1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
| Select propdata.apv_prop_reference "Renewal Proposal Number",
targetset.apv_ch_prev_con_num "Previous Policy Number",
targetset.apv_created_date "Renewal Date",
DECODE (propdata.apv_crc_code,
'99', 'Policy',
'94', 'Cancelled',
(Select tvs_description
From table_values
Where tvs_tad_code = 'PROP_STATUS'
And tvs_code = propdata.apv_file_status)
) "Status"
From (Select rnw.apv_prop_reference,
rnw.apv_created_date,
rnw.apv_ch_prev_con_num,
(Select Max (apv_version_number)
From ad_proposal_versions
Where apv_prop_reference = rnw.apv_prop_reference)
maximum_version
From ad_proposal_versions rnw
Where 1 = 1
And rnw.apv_origin_type = 'AUTO_R'
And rnw.apv_version_number = 1
And rnw.apv_created_date < to_date('20150327','YYYYMMDD')
And rnw.apv_created_date >= to_date('20150326','YYYYMMDD')
Order By rnw.apv_created_date
) targetset,
ad_proposal_versions propdata
Where targetset.maximum_version = propdata.apv_version_number
And targetset.apv_prop_reference = propdata.apv_prop_reference; |
Partager