[Home] [Help]
168: --TruncateTable ('AMS_DM_PARTY_PROFILE_STG');
169: --TruncateTable ('AMS_DM_AGG_STG');
170: --TruncateTable ('AMS_DM_BIC_STG');
171:
172: TruncateTable ('AMS_DM_DRV_STG_GT');
173: TruncateTable ('AMS_DM_GEN_STG_GT');
174: TruncateTable ('AMS_DM_PERINT_STG_GT');
175: TruncateTable ('AMS_DM_FINNUM_STG_GT');
176: TruncateTable ('AMS_DM_PROFILE_STG_GT');
207: -- nyostos - Sept 11, 2003
208: -- changes for parallel mining processes using global temporary tables
209: --INSERT -- /*+ APPEND PARALLEL(AMS_DM_DRV_STG,DEFAULT,DEFAULT)*/
210: --INTO ams_dm_drv_stg (
211: INSERT -- /*+ APPEND PARALLEL(AMS_DM_DRV_STG_GT,DEFAULT,DEFAULT)*/
212: INTO ams_dm_drv_stg_gt (party_id)
213: SELECT ads.party_id party_id
214: FROM ams_dm_source ads
215: WHERE ads.used_for_object_id = p_object_id
208: -- changes for parallel mining processes using global temporary tables
209: --INSERT -- /*+ APPEND PARALLEL(AMS_DM_DRV_STG,DEFAULT,DEFAULT)*/
210: --INTO ams_dm_drv_stg (
211: INSERT -- /*+ APPEND PARALLEL(AMS_DM_DRV_STG_GT,DEFAULT,DEFAULT)*/
212: INTO ams_dm_drv_stg_gt (party_id)
213: SELECT ads.party_id party_id
214: FROM ams_dm_source ads
215: WHERE ads.used_for_object_id = p_object_id
216: AND ads.arc_used_for_object = p_object_type
262: --INTO ams_dm_drv_stg (
263: -- kbasavar 6/11/2004 Commented out the exists condition for bug 3278796
264: -- This could be a candidate for a performance issue in the future. As we will go against all parties in party details
265: -- The table could grow significantly in time
266: INSERT -- /*+ APPEND PARALLEL(AMS_DM_DRV_STG_GT,DEFAULT,DEFAULT)*/
267: INTO ams_dm_drv_stg_gt (
268: party_id)
269: SELECT x.party_id
270: FROM ams_dm_party_details x
263: -- kbasavar 6/11/2004 Commented out the exists condition for bug 3278796
264: -- This could be a candidate for a performance issue in the future. As we will go against all parties in party details
265: -- The table could grow significantly in time
266: INSERT -- /*+ APPEND PARALLEL(AMS_DM_DRV_STG_GT,DEFAULT,DEFAULT)*/
267: INTO ams_dm_drv_stg_gt (
268: party_id)
269: SELECT x.party_id
270: FROM ams_dm_party_details x
271: WHERE x.party_type = p_party_type
491: hzp.party_name,
492: hzp.city
493: FROM
494: -- ams_dm_drv_stg drv,
495: ams_dm_drv_stg_gt drv, -- nysotos - Sep 15, 2003 - Global Temp Table
496: hz_organization_profiles hop,
497: hz_parties hzp
498: WHERE
499: drv.party_id = hzp.party_id
684: hzp.party_name,
685: hzp.city
686: FROM
687: -- ams_dm_drv_stg drv,
688: ams_dm_drv_stg_gt drv, -- nysotos - Sep 15, 2003 - Global Temp Table
689: hz_person_profiles hpp,
690: hz_organization_profiles hop,
691: hz_org_contacts hoc,
692: hz_employment_history heh,
790: hzp.party_name,
791: hzp.city
792: FROM
793: -- ams_dm_drv_stg drv,
794: ams_dm_drv_stg_gt drv, -- nysotos - Sep 15, 2003 - Global Temp Table
795: hz_person_profiles hpp,
796: hz_employment_history heh,
797: hz_parties hzp
798: WHERE
877: 'TRAVEL',0,
878: 'DRINK',0,
879: 'SMOKE',0, 1)) interest_other_flag
880: -- FROM ams_dm_drv_stg drv,
881: FROM ams_dm_drv_stg_gt drv, -- nyostos - Sept 15, 2003 - Global Temp Table
882: hz_person_interest hpi,
883: hz_relationships hpr
884: WHERE drv.party_id = hpr.party_id --it's the party of type relationship
885: AND hpr.status = 'A'
926: 'TRAVEL',0,
927: 'DRINK',0,
928: 'SMOKE',0, 1)) interest_other_flag
929: -- FROM ams_dm_drv_stg drv,
930: FROM ams_dm_drv_stg_gt drv, -- nyostos - Sep 15, 2003 - Global Temp Table
931: hz_person_interest hpi
932: WHERE drv.party_id = hpi.party_id(+)
933: AND hpi.status(+) = 'A'
934: GROUP BY drv.party_id
969: SUM(DECODE (hzf.financial_number_name,'ACCOUNTS_RECEIVABLE',hzf.financial_number,0)) accounts_receivable ,
970: SUM(DECODE (hzf.financial_number_name,'RETAINED_EARNINGS',hzf.financial_number,0)) retained_earnings
971: -- nyostos - Sep 15, 2003 - Global Temp Table
972: -- FROM ams_dm_drv_stg drv, hz_financial_numbers hzf, hz_financial_reports hfr
973: FROM ams_dm_drv_stg_gt drv, hz_financial_numbers hzf, hz_financial_reports hfr
974: WHERE drv.party_id = hfr.party_id(+)
975: AND hfr.status(+) = 'A'
976: AND hfr.consolidated_ind(+) = 'C' -- wen only want consolidated reports
977: AND hfr.financial_report_id = hzf.financial_report_id(+)
1030: SUM(DECODE (hzf.financial_number_name,'ACCOUNTS_RECEIVABLE',hzf.financial_number,0)) accounts_receivable ,
1031: SUM(DECODE (hzf.financial_number_name,'RETAINED_EARNINGS',hzf.financial_number,0)) retained_earnings
1032: -- nyostos - Sep 15, 2003 - Global Temp Table
1033: -- FROM ams_dm_drv_stg drv, hz_financial_numbers hzf, hz_financial_reports hfr, hz_relationships hpr
1034: FROM ams_dm_drv_stg_gt drv, hz_financial_numbers hzf, hz_financial_reports hfr, hz_relationships hpr
1035: WHERE drv.party_id = hpr.party_id
1036: AND hpr.status = 'A'
1037: AND hpr.subject_table_name = 'HZ_PARTIES'
1038: AND hpr.object_table_name = 'HZ_PARTIES'
1082: MAX(hcp.interest_period_days) interest_period_days,
1083: MAX(hcp.payment_grace_days) payment_grace_days
1084: FROM
1085: -- ams_dm_drv_stg drv,
1086: ams_dm_drv_stg_gt drv, -- nyostos - Sep 15, 2003 - Global Temp Tables
1087: hz_cust_accounts hca,
1088: hz_customer_profiles hcp
1089: WHERE drv.party_id = hca.party_id(+)
1090: AND hca.status(+) = 'A'
1123: MAX(hcp.interest_period_days) interest_period_days,
1124: MAX(hcp.payment_grace_days) payment_grace_days
1125: FROM
1126: -- ams_dm_drv_stg drv,
1127: ams_dm_drv_stg_gt drv, -- nyostos - Sep 15, 2003 - Global Temp Tables
1128: hz_cust_accounts hca,
1129: hz_customer_profiles hcp,
1130: hz_relationships hpr
1131: WHERE drv.party_id = hpr.party_id
1173: MAX(hcp.interest_period_days) interest_period_days,
1174: MAX(hcp.payment_grace_days) payment_grace_days
1175: FROM
1176: -- ams_dm_drv_stg drv,
1177: ams_dm_drv_stg_gt drv, -- nyostos - Sep 15, 2003 - Global Temp Table
1178: hz_cust_accounts hca,
1179: hz_customer_profiles hcp
1180: WHERE hcp.cust_account_id(+) = hca.cust_account_id
1181: AND hcp.status(+) = 'A'
1238:
1239: -- age is in number of days
1240: CURSOR c_age IS
1241: SELECT SYSDATE - hpp.date_of_birth, drv.party_id
1242: FROM hz_person_profiles hpp, ams_dm_drv_stg_gt drv
1243: WHERE hpp.party_id = drv.party_id
1244: AND (SYSDATE BETWEEN hpp.effective_start_date AND NVL(hpp.effective_end_date,SYSDATE))
1245: GROUP BY drv.party_id,hpp.date_of_birth;
1246:
1245: GROUP BY drv.party_id,hpp.date_of_birth;
1246:
1247: CURSOR c_age_b2b IS
1248: SELECT SYSDATE - hpp.date_of_birth,drv.party_id
1249: FROM hz_person_profiles hpp,hz_relationships hpr,ams_dm_drv_stg_gt drv
1250: WHERE hpp.party_id = hpr.subject_id
1251: AND drv.party_id = hpr.party_id
1252: AND hpr.status = 'A'
1253: AND hpr.subject_table_name = 'HZ_PARTIES'
1259: GROUP BY drv.party_id,hpp.date_of_birth;
1260:
1261: CURSOR c_days_since_last_school IS
1262: SELECT SYSDATE - MAX(hze.last_date_attended), drv.party_id
1263: FROM hz_education hze, ams_dm_drv_stg_gt drv
1264: WHERE hze.party_id = drv.party_id
1265: AND hze.status = 'A'
1266: GROUP BY drv.party_id;
1267:
1266: GROUP BY drv.party_id;
1267:
1268: CURSOR c_days_since_last_school_b2b IS
1269: SELECT SYSDATE - MAX(hze.last_date_attended), drv.party_id
1270: FROM hz_education hze, ams_dm_drv_stg_gt drv, hz_relationships hpr
1271: WHERE hze.party_id = hpr.subject_id
1272: AND drv.party_id = hpr.party_id
1273: AND hpr.status = 'A'
1274: AND hpr.subject_table_name = 'HZ_PARTIES'
1280: GROUP BY drv.party_id;
1281:
1282: CURSOR c_days_since_last_event IS
1283: SELECT (SYSDATE - MAX(aeo.event_start_date)), drv.party_id
1284: FROM ams_event_offers_all_b aeo, ams_dm_drv_stg_gt drv
1285: WHERE aeo.event_offer_id IN (SELECT aer.event_offer_id
1286: FROM ams_event_registrations aer
1287: -- WHERE aer.event_offer_id = aeo.event_offer_id
1288: WHERE aer.attendant_party_id = drv.party_id)
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;
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)
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
1331: -- all references to party_id in ams_list_entries
1332: -- changed to list_entry_source_system_id
1333: CURSOR c_days_since_last_targeted IS
1334: SELECT (SYSDATE - MAX(aeo.event_start_date)), drv.party_id
1335: FROM ams_event_offers_all_b aeo, ams_dm_drv_stg_gt drv
1336: WHERE aeo.event_offer_id IN (SELECT aer.event_offer_id
1337: FROM ams_event_registrations aer
1338: -- WHERE aer.event_offer_id = aeo.event_offer_id
1339: WHERE aer.attendant_party_id = drv.party_id)
1340: GROUP BY drv.party_id;
1341:
1342: CURSOR c_avg_disc_offered IS
1343: SELECT AVG(aao.offer_amount), drv.party_id
1344: FROM ams_act_offers aao, ams_campaign_schedules acs, ams_dm_drv_stg_gt drv
1345: WHERE aao.arc_act_offer_used_by = 'CSCH'
1346: AND aao.activity_offer_id = acs.activity_offer_id
1347: AND acs.campaign_schedule_id IN (SELECT aal.list_used_by_id
1348: FROM ams_act_lists aal
1359: -- changed to list_entry_source_system_id
1360:
1361: CURSOR c_num_types_disc_offered IS
1362: SELECT COUNT(aao.offer_type), drv.party_id
1363: FROM ams_act_offers aao, ams_campaign_schedules acs, ams_dm_drv_stg_gt drv
1364: WHERE aao.arc_act_offer_used_by = 'CSCH'
1365: AND aao.activity_offer_id = acs.activity_offer_id
1366: AND acs.campaign_schedule_id IN (SELECT aal.list_used_by_id
1367: FROM ams_list_entries ale, ams_act_lists aal
1372: GROUP BY drv.party_id;
1373:
1374: CURSOR c_days_since_first_contact IS
1375: SELECT (SYSDATE - MIN(aeo.event_start_date)), drv.party_id
1376: FROM ams_event_offers_all_b aeo,ams_dm_drv_stg_gt drv
1377: WHERE aeo.event_offer_id IN (SELECT 1
1378: FROM ams_event_registrations aer
1379: -- WHERE aer.event_offer_id = aeo.event_offer_id
1380: WHERE aer.attendant_party_id = drv.party_id)
1385: 0,--SYSDATE - MAX(hca.account_termination_date),
1386: 0 --SYSDATE - MAX(hca.account_activation_date)
1387: , drv.party_id
1388: -- SYSDATE - MAX(hca.account_suspension_date)
1389: FROM hz_cust_accounts hca, ams_dm_drv_stg_gt drv
1390: WHERE hca.party_id = drv.party_id
1391: AND hca.status = 'A'
1392: GROUP BY drv.party_id;
1393:
1396: 0,--SYSDATE - MAX(hca.account_termination_date),
1397: 0 --SYSDATE - MAX(hca.account_activation_date)
1398: , drv.party_id
1399: -- SYSDATE - MAX(hca.account_suspension_date)
1400: FROM hz_cust_accounts hca, ams_dm_drv_stg_gt drv, hz_relationships hpr
1401: WHERE hca.party_id = hpr.object_id
1402: AND drv.party_id = hpr.party_id
1403: AND hpr.status = 'A'
1404: AND hpr.subject_table_name = 'HZ_PARTIES'
1421: FROM ams_list_entries ale,
1422: ams_act_lists aal,
1423: ams_campaign_schedules_b acs,
1424: --ams_media_b ame,
1425: ams_dm_drv_stg_gt drv
1426: WHERE aal.list_used_by = 'CSCH'
1427: AND aal.list_used_by_id = acs.schedule_id
1428: AND aal.list_act_type = 'TARGET'
1429: AND acs.activity_type_code = 'DIRECT_MARKETING'
1440: SUM(DECODE(UPPER(offer_type),'OFF_INVOICE',1,0)), drv.party_id
1441: FROM ams_list_entries ale,
1442: ams_act_lists aal,
1443: ams_campaign_schedules_b acs,
1444: ams_act_offers aao, ams_dm_drv_stg_gt drv
1445: WHERE aal.list_used_by = 'CSCH'
1446: AND aal.list_used_by_id = acs.schedule_id
1447: AND aal.list_act_type = 'TARGET'
1448: AND acs.end_date_time <= SYSDATE
1452: GROUP BY drv.party_id;
1453:
1454: -- nyostos - Sep 15, 2003 - Use Global Temporary Table
1455: --CURSOR cur_party IS SELECT party_id FROM ams_dm_drv_stg;
1456: CURSOR c_all_parties IS SELECT party_id FROM ams_dm_drv_stg_gt;
1457:
1458: c_party NUMBER;
1459: l_person_party_id NUMBER;
1460: l_org_party_id NUMBER;
1872: );
1873:
1874: CURSOR c_days_since_last_event IS
1875: SELECT (SYSDATE - MAX(aeo.event_start_date)), drv.party_id
1876: FROM ams_event_offers_all_b aeo, ams_dm_drv_stg_gt drv
1877: WHERE aeo.event_offer_id IN (SELECT aer.event_offer_id
1878: FROM ams_event_registrations aer
1879: WHERE aer.attendant_party_id in
1880: (select party_id
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
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)
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
1957: group by drv.party_id;
1958:
1959: CURSOR c_days_since_last_targeted IS
1960: SELECT (SYSDATE - MAX(aeo.event_start_date)), drv.party_id
1961: FROM ams_event_offers_all_b aeo, ams_dm_drv_stg_gt drv
1962: WHERE aeo.event_offer_id IN (SELECT aer.event_offer_id
1963: FROM ams_event_registrations aer
1964: WHERE aer.attendant_party_id in
1965: (select party_id
1976: group by drv.party_id;
1977:
1978: CURSOR c_avg_disc_offered IS
1979: SELECT AVG(aao.offer_amount),drv.party_id
1980: FROM ams_act_offers aao, ams_campaign_schedules acs, ams_dm_drv_stg_gt drv
1981: WHERE aao.arc_act_offer_used_by = 'CSCH'
1982: AND aao.activity_offer_id = acs.activity_offer_id
1983: AND acs.campaign_schedule_id IN (SELECT aal.list_used_by_id
1984: FROM ams_act_lists aal
2002:
2003:
2004: CURSOR c_num_types_disc_offered IS
2005: SELECT COUNT(aao.offer_type),drv.party_id
2006: FROM ams_act_offers aao, ams_campaign_schedules acs,ams_dm_drv_stg_gt drv
2007: WHERE aao.arc_act_offer_used_by = 'CSCH'
2008: AND aao.activity_offer_id = acs.activity_offer_id
2009: AND acs.campaign_schedule_id IN (SELECT aal.list_used_by_id
2010: FROM ams_list_entries ale, ams_act_lists aal
2026: group by drv.party_id;
2027:
2028: CURSOR c_days_since_first_contact IS
2029: SELECT (SYSDATE - MIN(aeo.event_start_date)),drv.party_id
2030: FROM ams_event_offers_all_b aeo, ams_dm_drv_stg_gt drv
2031: WHERE aeo.event_offer_id IN (SELECT 1
2032: FROM ams_event_registrations aer
2033: WHERE aer.attendant_party_id in
2034: (select party_id
2048: SELECT SYSDATE - MAX(hca.account_established_date),
2049: 0,--SYSDATE - MAX(hca.account_termination_date),
2050: 0 --SYSDATE - MAX(hca.account_activation_date)
2051: ,drv.party_id
2052: FROM hz_cust_accounts hca, ams_dm_drv_stg_gt drv
2053: WHERE hca.party_id = drv.party_id
2054: AND hca.status = 'A'
2055: group by drv.party_id;
2056:
2061: FROM ams_list_entries ale,
2062: ams_act_lists aal,
2063: ams_campaign_schedules_b acs,
2064: --ams_media_b ame,
2065: ams_dm_drv_stg_gt drv
2066: WHERE aal.list_used_by = 'CSCH'
2067: AND aal.list_used_by_id = acs.schedule_id
2068: AND aal.list_act_type = 'TARGET'
2069: AND acs.activity_type_code = 'DIRECT_MARKETING'
2090: SUM(DECODE(UPPER(offer_type),'OFF_INVOICE',1,0)), drv.party_id
2091: FROM ams_list_entries ale,
2092: ams_act_lists aal,
2093: ams_campaign_schedules_b acs,
2094: ams_act_offers aao, ams_dm_drv_stg_gt drv
2095: WHERE aal.list_used_by = 'CSCH'
2096: AND aal.list_used_by_id = acs.schedule_id
2097: AND aal.list_act_type = 'TARGET'
2098: AND acs.end_date_time <= SYSDATE
2111: )
2112: group by drv.party_id;
2113:
2114: -- nyostos - Sep 15, 2003 - Use Global Temporary Table
2115: -- CURSOR cur_party IS SELECT party_id FROM AMS_DM_DRV_stg_gt;
2116: CURSOR c_all_parties IS SELECT party_id FROM AMS_DM_DRV_stg_gt;
2117:
2118: -- choang - 05-aug-2004 - bug 3816612 - changing to use index by binary integer
2119: TYPE l_master_table_type IS
2112: group by drv.party_id;
2113:
2114: -- nyostos - Sep 15, 2003 - Use Global Temporary Table
2115: -- CURSOR cur_party IS SELECT party_id FROM AMS_DM_DRV_stg_gt;
2116: CURSOR c_all_parties IS SELECT party_id FROM AMS_DM_DRV_stg_gt;
2117:
2118: -- choang - 05-aug-2004 - bug 3816612 - changing to use index by binary integer
2119: TYPE l_master_table_type IS
2120: TABLE OF AMS_DM_AGG_STG_ORG_REC_TYPE INDEX BY BINARY_INTEGER;
2573: AVG(bps.call_length) call_length,
2574: AVG(bps.profitability) profitability
2575: FROM bic_party_summ bps,
2576: -- ams_dm_drv_stg drv
2577: ams_dm_drv_stg_gt drv -- nyostos - Sep 15, 2003 - Global Temp Table
2578: WHERE drv.party_id = bps.party_id(+)
2579: AND bps.period_start_date(+) > l_date - 365
2580: GROUP BY drv.party_id
2581: ;
2707: AVG(bps.call_length) call_length,
2708: AVG(bps.profitability) profitability
2709: FROM bic_party_summ bps,
2710: -- ams_dm_drv_stg drv,
2711: ams_dm_drv_stg_gt drv, -- nyostos - Sep 15, 2003 - Global Temp Table
2712: hz_relationships hpr
2713: WHERE drv.party_id = hpr.party_id
2714: AND hpr.status = 'A'
2715: AND hpr.subject_table_name = 'HZ_PARTIES'
2850: AVG(bps.call_length) call_length,
2851: AVG(bps.profitability) profitability
2852: FROM bic_party_summ bps,
2853: -- ams_dm_drv_stg drv
2854: ams_dm_drv_stg_gt drv -- nyostos - Sep 15, 2003 - Global Temp Table
2855: WHERE bps.period_start_date(+) > l_date - 365
2856: AND bps.party_id(+) = drv.party_id
2857: GROUP BY drv.party_id
2858: ;
3187: -- ams_dm_gen_stg gen,
3188: -- ams_dm_perint_stg int,
3189: -- ams_dm_finnum_stg fin,
3190: -- ams_dm_party_profile_stg ppf
3191: ams_dm_drv_stg_gt drv,
3192: ams_dm_gen_stg_gt gen,
3193: ams_dm_perint_stg_gt int,
3194: ams_dm_finnum_stg_gt fin,
3195: ams_dm_profile_stg_gt ppf
3430: -- nyostos - Sep 15, 2003 - Global Temp Tables
3431: -- ams_dm_drv_stg drv,
3432: -- ams_dm_agg_stg agg,
3433: -- ams_dm_bic_stg bic
3434: ams_dm_drv_stg_gt drv,
3435: ams_dm_agg_stg_gt agg,
3436: ams_dm_bic_stg_gt bic
3437: WHERE drv.party_id = agg.party_id (+)
3438: AND drv.party_id = bic.party_id (+)
3647: ppf.risk_code risk_code,
3648: ppf.interest_period_days interest_period_days,
3649: ppf.payment_grace_days payment_grace_days
3650: FROM
3651: ams_dm_drv_stg_gt drv,
3652: ams_dm_gen_stg_gt gen,
3653: ams_dm_perint_stg_gt int,
3654: ams_dm_finnum_stg_gt fin,
3655: ams_dm_profile_stg_gt ppf
3659: AND drv.party_id = int.party_id (+)
3660: AND drv.party_id = fin.party_id (+)
3661: AND drv.party_id = ppf.party_id (+)
3662: )
3663: WHERE pdt.party_id IN (SELECT /*+ INDEX_FFS(AMS_DM_DRV_STG_GT_U1)*/ party_id FROM ams_dm_drv_stg_gt)
3664: ;
3665:
3666: -------------------- finish --------------------------
3667: COMMIT;
3873: bic.tot_calls tot_calls,
3874: bic.call_length call_length,
3875: bic.profitability profitability
3876: FROM
3877: ams_dm_drv_stg_gt drv,
3878: ams_dm_agg_stg_gt agg,
3879: ams_dm_bic_stg_gt bic
3880: WHERE drv.party_id = pdtt.party_id
3881: AND drv.party_id = agg.party_id (+)
3880: WHERE drv.party_id = pdtt.party_id
3881: AND drv.party_id = agg.party_id (+)
3882: AND drv.party_id = bic.party_id (+)
3883: )
3884: WHERE pdtt.party_id IN (SELECT /*+ INDEX_FFS(AMS_DM_DRV_STG_GT_U1)*/ party_id FROM ams_dm_drv_stg_gt)
3885: ;
3886:
3887: -------------------- finish --------------------------
3888: COMMIT;
4139: -- load into targets
4140: UpdatePartyDetails;
4141: UpdatePartyDetailsTime;
4142:
4143: TruncateTable ('AMS_DM_DRV_STG_GT');
4144:
4145: END LOOP;
4146:
4147: END IF;