APPS.AMS_DMEXTRACT_PVT dependencies on AMS_LIST_HEADERS_ALL

Line 1293: FROM ams_list_entries ale, ams_dm_drv_stg_gt drv,ams_list_headers_all hdr

1289: GROUP BY drv.party_id;
1290:
1291: CURSOR c_num_times_targeted IS
1292: SELECT COUNT(DISTINCT ale.list_header_id), drv.party_id
1293: FROM ams_list_entries ale, ams_dm_drv_stg_gt drv,ams_list_headers_all hdr
1294: WHERE ale.party_id = drv.party_id
1295: AND hdr.list_header_id = ale.list_header_id
1296: AND hdr.list_type = 'TARGET'
1297: GROUP BY drv.party_id;

Line 1302: FROM ams_act_lists aal, ams_list_headers_all alh, ams_dm_drv_stg_gt drv

1298:
1299: -- change to use ams_act_lists
1300: CURSOR c_last_targeted_channel_code IS
1301: SELECT aal.list_used_by_id, drv.party_id
1302: FROM ams_act_lists aal, ams_list_headers_all alh, ams_dm_drv_stg_gt drv
1303: WHERE aal.list_used_by = 'CSCH'
1304: AND aal.list_act_type = 'TARGET'
1305: AND alh.list_header_id = aal.list_header_id
1306: AND alh.sent_out_date = (SELECT MAX(l.sent_out_date)

Line 1307: FROM ams_list_headers_all l

1303: WHERE aal.list_used_by = 'CSCH'
1304: AND aal.list_act_type = 'TARGET'
1305: AND alh.list_header_id = aal.list_header_id
1306: AND alh.sent_out_date = (SELECT MAX(l.sent_out_date)
1307: FROM ams_list_headers_all l
1308: WHERE l.list_header_id IN (SELECT ale.list_header_id
1309: FROM ams_list_entries ale
1310: WHERE ale.party_id = drv.party_id))
1311: GROUP BY drv.party_id, aal.list_used_by_id ;

Line 1321: FROM ams_list_headers_all alh, ams_dm_drv_stg_gt drv

1317: SELECT SUM(DECODE(SIGN(MONTHS_BETWEEN(SYSDATE, alh.sent_out_date) -1),1,0,1)),
1318: SUM(DECODE(SIGN(MONTHS_BETWEEN(SYSDATE, alh.sent_out_date) -3),1,0,1)),
1319: SUM(DECODE(SIGN(MONTHS_BETWEEN(SYSDATE, alh.sent_out_date) -9),1,0,1)),
1320: SUM(DECODE(SIGN(MONTHS_BETWEEN(SYSDATE, alh.sent_out_date) -12),1,0,1)), drv.party_id
1321: FROM ams_list_headers_all alh, ams_dm_drv_stg_gt drv
1322: WHERE alh.sent_out_date IS NOT NULL
1323: AND MONTHS_BETWEEN(sysdate, alh.sent_out_date) <= 12
1324: AND alh.list_header_id IN (SELECT ale.list_header_id
1325: FROM ams_list_entries ale

Line 1895: FROM ams_list_entries ale, ams_dm_drv_stg_gt drv,ams_list_headers_all hdr

1891: group by drv.party_id;
1892:
1893: CURSOR c_num_times_targeted IS
1894: SELECT COUNT(DISTINCT ale.list_header_id),drv.party_id
1895: FROM ams_list_entries ale, ams_dm_drv_stg_gt drv,ams_list_headers_all hdr
1896: WHERE ale.party_id in
1897: (select party_id
1898: from hz_relationships hpr
1899: where object_id=drv.party_id

Line 1913: FROM ams_act_lists aal, ams_list_headers_all alh, ams_dm_drv_stg_gt drv

1909: GROUP BY drv.party_id;
1910:
1911: CURSOR c_last_targeted_channel_code IS
1912: SELECT aal.list_used_by_id, drv.party_id
1913: FROM ams_act_lists aal, ams_list_headers_all alh, ams_dm_drv_stg_gt drv
1914: WHERE aal.list_used_by = 'CSCH'
1915: AND aal.list_act_type = 'TARGET'
1916: AND alh.list_header_id = aal.list_header_id
1917: AND alh.sent_out_date = (SELECT MAX(l.sent_out_date)

Line 1918: FROM ams_list_headers_all l

1914: WHERE aal.list_used_by = 'CSCH'
1915: AND aal.list_act_type = 'TARGET'
1916: AND alh.list_header_id = aal.list_header_id
1917: AND alh.sent_out_date = (SELECT MAX(l.sent_out_date)
1918: FROM ams_list_headers_all l
1919: WHERE l.list_header_id IN
1920: (SELECT ale.list_header_id
1921: FROM ams_list_entries ale
1922: WHERE ale.party_id in

Line 1941: FROM ams_list_headers_all alh,ams_dm_drv_stg_gt drv

1937: SELECT SUM(DECODE(SIGN(MONTHS_BETWEEN(SYSDATE, alh.sent_out_date) -1),1,0,1)),
1938: SUM(DECODE(SIGN(MONTHS_BETWEEN(SYSDATE, alh.sent_out_date) -3),1,0,1)),
1939: SUM(DECODE(SIGN(MONTHS_BETWEEN(SYSDATE, alh.sent_out_date) -9),1,0,1)),
1940: SUM(DECODE(SIGN(MONTHS_BETWEEN(SYSDATE, alh.sent_out_date) -12),1,0,1)), drv.party_id
1941: FROM ams_list_headers_all alh,ams_dm_drv_stg_gt drv
1942: WHERE alh.sent_out_date IS NOT NULL
1943: AND MONTHS_BETWEEN(sysdate, alh.sent_out_date) <= 12
1944: AND alh.list_header_id IN (SELECT ale.list_header_id
1945: FROM ams_list_entries ale