select pm_policy_no_0,
'VOL' as pm_lob,
pm_last_name_of_customer,
pm_first_name_of_customer,
(CASE
WHEN LENGTH(PM_ORIG_INCEPTION_DATE) = 3 THEN
'20000' || trim(PM_ORIG_INCEPTION_DATE)
WHEN LENGTH(PM_ORIG_INCEPTION_DATE) = 4 THEN
'2000' || trim(PM_ORIG_INCEPTION_DATE)
WHEN LENGTH(PM_ORIG_INCEPTION_DATE) = 5 THEN
'200' || trim(PM_ORIG_INCEPTION_DATE)
WHEN LENGTH(PM_ORIG_INCEPTION_DATE) = 6 THEN
'19' || trim(PM_ORIG_INCEPTION_DATE)
WHEN LENGTH(PM_ORIG_INCEPTION_DATE) = 8 THEN
trim(PM_ORIG_INCEPTION_DATE)
ELSE
trim(PM_ORIG_INCEPTION_DATE)
END) AS PM_ORIG_INCEPTION_DATE,
(CASE
WHEN LENGTH(PM_EFF_DATE) = 3 THEN
'20000' || trim(PM_EFF_DATE)
WHEN LENGTH(PM_EFF_DATE) = 4 THEN
'2000' || trim(PM_EFF_DATE)
WHEN LENGTH(PM_EFF_DATE) = 5 THEN
'200' || trim(PM_EFF_DATE)
WHEN LENGTH(PM_EFF_DATE) = 6 THEN
'19' || trim(PM_EFF_DATE)
WHEN LENGTH(PM_EFF_DATE) = 8 THEN
trim(PM_EFF_DATE)
ELSE
trim(PM_EFF_DATE)
END) AS PM_EFF_DATE,
(CASE
WHEN LENGTH(PM_EXP_DATE) = 3 THEN
'20000' || trim(PM_EXP_DATE)
WHEN LENGTH(PM_EXP_DATE) = 4 THEN
'2000' || trim(PM_EXP_DATE)
WHEN LENGTH(PM_EXP_DATE) = 5 THEN
'200' || trim(PM_EXP_DATE)
WHEN LENGTH(PM_EXP_DATE) = 6 THEN
'19' || trim(PM_EXP_DATE)
WHEN LENGTH(PM_EXP_DATE) = 8 THEN
trim(PM_EXP_DATE)
ELSE
trim(PM_EXP_DATE)
END) AS PM_EXP_DATE,
pm_address_city,
pm_address_state_code,
trim(TO_CHAR(pm_zip_code_1,'09999')),
trim(SUBSTR((TO_CHAR(pm_zip_code_2,'0999')),2,5)),
pm_no_of_vehicles,
pm_policy_trans_type
from tblPolicy
where (pm_amend_trans_type = '21' and
(pm_version_date > '070924' and
pm_version_date < '071023') and pm_orig_inception_date > pm_version_date);
