[Home] [Help]
1: PACKAGE BODY BIM_I_LEAD_FACTS_PKG AS
2: /*$Header: bimildfb.pls 120.1 2005/10/11 05:38:48 sbassi noship $*/
3:
4: g_pkg_name CONSTANT VARCHAR2(20) := 'BIM_I_LEAD_FACTS_PKG';
5: g_file_name CONSTANT VARCHAR2(20) := 'bimildfb.pls';
1: PACKAGE BODY BIM_I_LEAD_FACTS_PKG AS
2: /*$Header: bimildfb.pls 120.1 2005/10/11 05:38:48 sbassi noship $*/
3:
4: g_pkg_name CONSTANT VARCHAR2(20) := 'BIM_I_LEAD_FACTS_PKG';
5: g_file_name CONSTANT VARCHAR2(20) := 'bimildfb.pls';
6: l_global_currency_code varchar2(20);
7:
8:
18: CURSOR C_missing_rates
19: IS
20: SELECT from_currency from_currency,
21: decode(conversion_rate,-3,to_date('01/01/1999','MM/DD/RRRR'),lead_creation_date) lead_creation_date
22: FROM bim_i_lead_facts_stg
23: WHERE (conversion_rate < 0
24: OR conversion_rate IS NULL)
25: AND from_currency is not null
26: AND lead_creation_date >= p_start_date
26: AND lead_creation_date >= p_start_date
27: ORDER BY from_currency;
28: BEGIN
29: l_msg_name := 'BIS_DBI_CURR_NO_LOAD';
30: SELECT COUNT(*) INTO l_cnt_miss_rate FROM bim_i_lead_facts_stg
31: WHERE
32: (conversion_rate < 0
33: OR conversion_rate IS NULL)
34: AND from_currency is not null
94: l_start_date DATE;
95: l_end_date DATE;
96: l_user_id NUMBER := FND_GLOBAL.USER_ID();
97: l_api_version_number CONSTANT NUMBER := 1.0;
98: l_api_name CONSTANT VARCHAR2(30) := 'BIM_I_LEAD_FACTS_PKG';
99: l_mesg_text VARCHAR2(100);
100: l_load_type VARCHAR2(100);
101: l_global_date DATE;
102: l_missing_date BOOLEAN := FALSE;
342:
343:
344:
345: /* Dropping INdexes */
346: BIM_UTL_PKG.DROP_INDEX('BIM_I_LEAD_FACTS');
347:
348: /* Truncate Staging table */
349: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.bim_i_lead_facts_stg';
350:
345: /* Dropping INdexes */
346: BIM_UTL_PKG.DROP_INDEX('BIM_I_LEAD_FACTS');
347:
348: /* Truncate Staging table */
349: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.bim_i_lead_facts_stg';
350:
351:
352: l_table_name := 'BIM_I_LEAD_FACTS';
353: bis_collection_utilities.log('Running Initial Load of Lead Facts');
348: /* Truncate Staging table */
349: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.bim_i_lead_facts_stg';
350:
351:
352: l_table_name := 'BIM_I_LEAD_FACTS';
353: bis_collection_utilities.log('Running Initial Load of Lead Facts');
354:
355: l_sysdate :=sysdate;
356:
354:
355: l_sysdate :=sysdate;
356:
357: INSERT /*+ append parallel */
358: INTO bim_i_lead_facts_stg LDF
359: (
360: lead_id
361: ,lead_line_id
362: ,group_id
447:
448: /*l_check_missing_rate := Check_Missing_Rates (p_start_date);
449: if (l_check_missing_rate = -1) then
450: BIS_COLLECTION_UTILITIES.debug('before truncating first time load' );
451: l_stmt := 'TRUNCATE table '||l_schema||'.bim_i_lead_facts_stg';
452: EXECUTE IMMEDIATE l_stmt;
453: commit;
454:
455: x_return_status := FND_API.G_RET_STS_ERROR;
458: */
459:
460: --update to get customer category
461:
462: /*update bim.bim_i_lead_facts_stg stg
463: set stg.cust_category
464: = (select b.class_code from hz_code_assignments b
465: where
466: stg.customer_id=b.OWNER_TABLE_ID
480: and b.CLASS_CATEGORY='CUSTOMER_CATEGORY'
481: and b.status='A');
482: */
483:
484: update bim_i_lead_facts_stg stg
485: set stg.cust_category
486: = (select b.class_code from hz_code_assignments b
487: where
488: stg.customer_id=b.OWNER_TABLE_ID
525:
526:
527: IF l_cert_level = 'YES' THEN
528:
529: UPDATE bim_i_lead_facts_stg stg SET CUSTOMER_FLAG='Y'
530: WHERE
531: EXISTS
532: (SELECT 1 from HZ_CUST_ACCOUNTS a,hz_parties b
533: WHERE a.party_id=stg.customer_id
536: AND b.certification_level is not null);
537:
538: ELSE
539:
540: UPDATE bim_i_lead_facts_stg stg set CUSTOMER_FLAG='Y'
541: WHERE
542: EXISTS
543: (SELECT 1 from HZ_CUST_ACCOUNTS a
544: WHERE a.party_id=stg.customer_id
548: END IF;
549:
550: BIS_COLLECTION_UTILITIES.log('Truncating Facts Table');
551:
552: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.bim_i_lead_facts';
553:
554: BIS_COLLECTION_UTILITIES.deleteLogForObject('BIM_LEADS');
555:
556: INSERT /*+ append parallel */
553:
554: BIS_COLLECTION_UTILITIES.deleteLogForObject('BIM_LEADS');
555:
556: INSERT /*+ append parallel */
557: INTO bim_i_lead_facts LDF
558: (
559: creation_date
560: ,last_update_date
561: ,created_by
656: ,nvl(x.product_category_id,-1) product_category_id
657: ,x.customer_flag customer_flag
658: ,x.lead_name Lead_name
659: FROM
660: bim_i_lead_facts_stg X
661: ,bim_i_source_codes A
662: ,bis_territory_hierarchies T
663: WHERE
664: X.source_code_id = A.source_code_id(+)
672:
673:
674:
675: --update date for converted leads
676: UPDATE bim_i_lead_facts facts
677: SET (facts.lead_converted_date, facts.lead_touched_date)
678: = (SELECT TRUNC(MIN(slo.creation_date)), TRUNC(MIN(slo.creation_date))
679: FROM
680: as_sales_lead_opportunity slo
696:
697:
698:
699: --update lead_closed_date for closed leads other than dead and converted
700: UPDATE bim_i_lead_facts facts
701: SET (facts.lead_closed_date, facts.lead_touched_date)
702: = (SELECT TRUNC(MIN(hist.creation_date)), TRUNC(MIN(hist.creation_date))
703: FROM
704: as_sales_leads_log hist
732:
733:
734:
735: --update touched_date for leads that does not have history
736: UPDATE bim_i_lead_facts facts
737: SET facts.lead_touched_date
738: =(CASE
739: WHEN lead_dead_date is not null THEN lead_dead_date
740: WHEN lead_converted_date is not null THEN lead_converted_date
748:
749:
750:
751:
752: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.bim_i_lead_facts_stg';
753:
754: --EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.mlog$_bim_i_lead_facts';
755:
756: --dbms_output.put_line(p_start_date);
750:
751:
752: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.bim_i_lead_facts_stg';
753:
754: --EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.mlog$_bim_i_lead_facts';
755:
756: --dbms_output.put_line(p_start_date);
757:
758: --dbms_output.put_line(p_start_date);
765:
766: /***************************************************************/
767:
768:
769: bis_collection_utilities.log('Before Analyze of the table BIM_I_LEAD_FACTS');
770:
771: --Analyze the facts table
772: DBMS_STATS.gather_table_stats('BIM','BIM_I_LEAD_FACTS', estimate_percent => 5,
773: degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
768:
769: bis_collection_utilities.log('Before Analyze of the table BIM_I_LEAD_FACTS');
770:
771: --Analyze the facts table
772: DBMS_STATS.gather_table_stats('BIM','BIM_I_LEAD_FACTS', estimate_percent => 5,
773: degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
774:
775: /* Recreating Indexes */
776: BIM_UTL_PKG.CREATE_INDEX('BIM_I_LEAD_FACTS');
772: DBMS_STATS.gather_table_stats('BIM','BIM_I_LEAD_FACTS', estimate_percent => 5,
773: degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
774:
775: /* Recreating Indexes */
776: BIM_UTL_PKG.CREATE_INDEX('BIM_I_LEAD_FACTS');
777:
778: bis_collection_utilities.log('Successful Completion of Leads Facts Program');
779:
780:
788: p_count => x_msg_count,
789: p_data => x_msg_data
790: );
791:
792: ams_utility_pvt.write_conc_log('BIM_I_LEAD_FACTS_PKG:FIRST_LOAD:IN EXPECTED EXCEPTION '||sqlerrm(sqlcode));
793:
794: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
795:
796: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
801: p_count => x_msg_count,
802: p_data => x_msg_data
803: );
804:
805: ams_utility_pvt.write_conc_log('BIM_I_LEAD_FACTS_PKG:FIRST_LOAD:IN UNEXPECTED EXCEPTION '||sqlerrm(sqlcode));
806:
807: WHEN OTHERS THEN
808:
809: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
819: p_count => x_msg_count,
820: p_data => x_msg_data
821: );
822:
823: ams_utility_pvt.write_conc_log('BIM_I_LEAD_FACTS_PKG:FIRST_LOAD:IN OTHERS EXCEPTION '||sqlerrm(sqlcode));
824:
825:
826: END FIRST_LOAD;
827:
898: --Find if the certification level is implemented or not
899: l_cert_level := nvl(FND_PROFILE.Value('HZ_DISPLAY_CERT_STATUS'),'NO');
900:
901:
902: l_table_name := 'BIM_I_LEAD_FACTS';
903: bis_collection_utilities.log('Running Incremental Load of Lead Facts');
904:
905: /* Truncate Staging table */
906: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.bim_i_lead_facts_stg';
902: l_table_name := 'BIM_I_LEAD_FACTS';
903: bis_collection_utilities.log('Running Incremental Load of Lead Facts');
904:
905: /* Truncate Staging table */
906: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.bim_i_lead_facts_stg';
907:
908: l_sysdate:=sysdate;
909:
910: INSERT /*+ append parallel */
907:
908: l_sysdate:=sysdate;
909:
910: INSERT /*+ append parallel */
911: INTO bim_i_lead_facts_stg LDF
912: (
913: lead_id
914: ,group_id
915: ,resource_id
978:
979: COMMIT;
980:
981: INSERT /*+ append parallel */
982: INTO bim_i_lead_facts_stg LDF
983: (
984: lead_id
985: ,lead_line_id
986: ,group_id
1068:
1069: /*l_check_missing_rate := Check_Missing_Rates (p_start_date);
1070: if (l_check_missing_rate = -1) then
1071: BIS_COLLECTION_UTILITIES.debug('before truncating first time load' );
1072: l_stmt := 'TRUNCATE table '||l_schema||'.bim_i_lead_facts_stg';
1073: EXECUTE IMMEDIATE l_stmt;
1074: commit;
1075: x_return_status := FND_API.G_RET_STS_ERROR;
1076: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1078: */
1079:
1080: --update to get customer category
1081:
1082: update bim_i_lead_facts_stg stg
1083: set stg.cust_category
1084: = (select b.class_code from hz_code_assignments b
1085: where
1086: stg.customer_id=b.OWNER_TABLE_ID
1122: --update customer flag
1123:
1124: IF l_cert_level = 'YES' THEN
1125:
1126: update bim_i_lead_facts_stg stg set CUSTOMER_FLAG='Y'
1127: where
1128: exists
1129: (select 1 from HZ_CUST_ACCOUNTS a,hz_parties b
1130: where a.party_id=stg.customer_id
1133: and b.certification_level is not null);
1134:
1135: else
1136:
1137: update bim_i_lead_facts_stg stg set CUSTOMER_FLAG='Y'
1138: where
1139: exists
1140: (select 1 from HZ_CUST_ACCOUNTS a
1141: where a.party_id=stg.customer_id
1144: end if;
1145:
1146: COMMIT;
1147:
1148: MERGE INTO bim_i_lead_facts facts
1149: USING (
1150: SELECT
1151: sysdate creation_date
1152: ,sysdate last_update_date
1197: ,nvl(x.product_category_id,-1) product_category_id
1198: ,x.customer_flag customer_flag
1199: ,x.lead_name lead_name
1200: FROM
1201: bim_i_lead_facts_stg X
1202: ,bim_i_source_codes A
1203: ,bis_territory_hierarchies T
1204: WHERE
1205: X.source_code_id = A.source_code_id(+)
1359: );
1360:
1361:
1362: --update date for dead leads
1363: UPDATE bim_i_lead_facts facts
1364: SET facts.lead_dead_date
1365: = (SELECT TRUNC(MIN(hist.creation_date))
1366: FROM
1367: as_sales_leads_log hist
1389: );
1390:
1391:
1392: --update date for converted leads
1393: UPDATE bim_i_lead_facts facts
1394: SET facts.lead_converted_date
1395: = (SELECT TRUNC(MIN(slo.creation_date))
1396: FROM
1397: as_sales_lead_opportunity slo
1415: );
1416:
1417:
1418: --update date for closed leads other than dead and converted
1419: UPDATE bim_i_lead_facts facts
1420: SET facts.lead_closed_date
1421: = (SELECT TRUNC(MIN(hist.creation_date))
1422: FROM
1423: as_sales_leads_log hist
1450: AND facts.lead_closed_date is null
1451: );
1452:
1453: --update touched_date for leads that comes in dead, conveted or closed
1454: UPDATE bim_i_lead_facts facts
1455: SET facts.lead_touched_date
1456: =(CASE
1457: WHEN lead_dead_date is not null THEN lead_dead_date
1458: WHEN lead_converted_date is not null THEN lead_converted_date
1464: and last_update_date between p_start_date and l_sysdate;
1465:
1466:
1467: DELETE
1468: FROM bim_i_lead_facts
1469: WHERE lead_line_id IS NULL
1470: AND lead_id in (SELECT
1471: lead_id
1472: FROM bim_i_lead_facts_stg
1468: FROM bim_i_lead_facts
1469: WHERE lead_line_id IS NULL
1470: AND lead_id in (SELECT
1471: lead_id
1472: FROM bim_i_lead_facts_stg
1473: WHERE lead_line_id is NOT NULL);
1474:
1475: COMMIT;
1476:
1473: WHERE lead_line_id is NOT NULL);
1474:
1475: COMMIT;
1476:
1477: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.bim_i_lead_facts_stg';
1478:
1479:
1480: --dbms_output.put_line(p_start_date);
1481: --dbms_output.put_line(p_end_date);
1487:
1488: /***************************************************************/
1489:
1490:
1491: bis_collection_utilities.log('Before Analyze of the table BIM_I_LEAD_FACTS');
1492:
1493: --Analyze the facts table
1494: DBMS_STATS.gather_table_stats('BIM','BIM_I_LEAD_FACTS', estimate_percent => 5,
1495: degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
1490:
1491: bis_collection_utilities.log('Before Analyze of the table BIM_I_LEAD_FACTS');
1492:
1493: --Analyze the facts table
1494: DBMS_STATS.gather_table_stats('BIM','BIM_I_LEAD_FACTS', estimate_percent => 5,
1495: degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
1496:
1497:
1498: bis_collection_utilities.log('Successful Completion of Leads Facts Program');
1508: p_count => x_msg_count,
1509: p_data => x_msg_data
1510: );
1511:
1512: ams_utility_pvt.write_conc_log('BIM_I_LEAD_FACTS_PKG:INCREMENTAL_LOAD:IN EXPECTED EXCEPTION '||sqlerrm(sqlcode));
1513:
1514: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1515:
1516: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1521: p_count => x_msg_count,
1522: p_data => x_msg_data
1523: );
1524:
1525: ams_utility_pvt.write_conc_log('BIM_I_LEAD_FACTS_PKG:INCREMENTAL_LOAD:IN UNEXPECTED EXCEPTION '||sqlerrm(sqlcode));
1526:
1527: WHEN OTHERS THEN
1528:
1529: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1539: p_count => x_msg_count,
1540: p_data => x_msg_data
1541: );
1542:
1543: ams_utility_pvt.write_conc_log('BIM_I_LEAD_FACTS_PKG:INCREMENTAL_LOAD:IN OTHERS EXCEPTION '||sqlerrm(sqlcode));
1544:
1545:
1546: END INCREMENTAL_LOAD;
1547:
1545:
1546: END INCREMENTAL_LOAD;
1547:
1548:
1549: END BIM_I_LEAD_FACTS_PKG;
1550: