[Home] [Help]
1: Package body OPI_COLLECTION_HOOK_P AS
2: /*$Header: OPICOLLB.pls 120.0 2005/05/24 17:56:39 appldev noship $ */
3:
4: /* Constants for session - including schema name for truncating and
5: collecting stats */
22:
23: COMMIT;
24:
25: -- Gather stats on the daily status fact to optimize joins to it.
26: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_INV_DAILY_STAT_F');
27:
28:
29: EDW_OWB_COLLECTION_UTIL.write_to_log_file('get distinct row ids from IDS Snapshot Log log table');
30: EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
43:
44: EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows changed in IDS'||SQL%ROWCOUNT);
45: EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
46:
47: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_IDS_OPICOLLB_LOG');
48:
49: /* Move Non Attribute Columns into Tep Table 1 */
50:
51: EDW_OWB_COLLECTION_UTIL.write_to_log_file('Populate Non key attributes into OPI_EDW_IDS_NET_CHG Table');
92:
93: EDW_OWB_COLLECTION_UTIL.write_to_log_file('Populated Non key attributes into OPI_EDW_IDS_NET_CHG Table'||SQL%ROWCOUNT);
94: EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
95:
96: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_IDS_NET_CHG');
97:
98: /* Move net change data from IPS and Temp OPI_EDW_IPS_NET_CHG */
99:
100: EDW_OWB_COLLECTION_UTIL.write_to_log_file('Move net change data from IPS and Temp OPI_EDW_IPS_NET_CHG ');
312:
313: EDW_OWB_COLLECTION_UTIL.write_to_log_file('Moved net change data from IPS and Temp OPI_EDW_IPS_NET_CHG ' ||SQL%ROWCOUNT);
314: EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
315:
316: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_IPS_NET_CHG');
317:
318: /* Delete IPS table for Changed rows */
319:
320: EDW_OWB_COLLECTION_UTIL.write_to_log_file('Deleting Changed rows from IPS ');
533:
534: EDW_OWB_COLLECTION_UTIL.write_to_log_file('Moved Data from Work table to IPS' ||SQL%ROWCOUNT);
535: EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
536:
537: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_INV_PERD_STAT_F');
538:
539: execute immediate 'truncate table ' || g_opi_schema || '.OPI_EDW_IDS_OPICOLLB_LOG';
540: execute immediate 'truncate table ' || g_opi_schema || '.OPI_EDW_IDS_NET_CHG';
541: execute immediate 'truncate table ' || g_opi_schema || '.OPI_EDW_IPS_NET_CHG';
555: --
556: -- Clean up Temporary work tables data. if previous run failed these tables will have data.
557: --
558: --
559: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_ILOG');
560: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_MAX_VALUES');
561: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_KEYS');
562: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FT');
563: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FUR');
556: -- Clean up Temporary work tables data. if previous run failed these tables will have data.
557: --
558: --
559: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_ILOG');
560: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_MAX_VALUES');
561: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_KEYS');
562: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FT');
563: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FUR');
564: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FIR');
557: --
558: --
559: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_ILOG');
560: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_MAX_VALUES');
561: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_KEYS');
562: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FT');
563: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FUR');
564: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FIR');
565: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FDLG');
558: --
559: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_ILOG');
560: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_MAX_VALUES');
561: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_KEYS');
562: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FT');
563: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FUR');
564: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FIR');
565: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FDLG');
566: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FD');
559: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_ILOG');
560: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_MAX_VALUES');
561: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_KEYS');
562: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FT');
563: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FUR');
564: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FIR');
565: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FDLG');
566: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FD');
567: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_COGSPOSTCOLL_FUR');
560: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_MAX_VALUES');
561: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_KEYS');
562: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FT');
563: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FUR');
564: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FIR');
565: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FDLG');
566: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FD');
567: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_COGSPOSTCOLL_FUR');
568: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_REV_POSTCOLL_FUR');
561: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_KEYS');
562: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FT');
563: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FUR');
564: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FIR');
565: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FDLG');
566: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FD');
567: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_COGSPOSTCOLL_FUR');
568: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_REV_POSTCOLL_FUR');
569: --
562: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FT');
563: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FUR');
564: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FIR');
565: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FDLG');
566: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FD');
567: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_COGSPOSTCOLL_FUR');
568: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_REV_POSTCOLL_FUR');
569: --
570: --
563: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FUR');
564: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FIR');
565: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FDLG');
566: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FD');
567: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_COGSPOSTCOLL_FUR');
568: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_REV_POSTCOLL_FUR');
569: --
570: --
571: -- Clean up section is over
564: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FIR');
565: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FDLG');
566: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FD');
567: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_COGSPOSTCOLL_FUR');
568: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_REV_POSTCOLL_FUR');
569: --
570: --
571: -- Clean up section is over
572: --
713: -- Gather table statistics these stats will be used by CBO for query optimisation.
714: --
715: --
716:
717: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MARGIN_PERD_FDLG');
718:
719: insert into OPI_EDW_MARGIN_PERD_FD (select rowid ,0 from OPI_EDW_MARGIN_PERD_FDLG);
720:
721: EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows inserted into OPI_EDW_MARGIN_PERD_FD '||SQL%ROWCOUNT);
727: -- Gather table statistics these stats will be used by CBO for query optimisation.
728: --
729: --
730:
731: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MARGIN_PERD_FD');
732:
733: ELSE
734: /***********************************************************************************************************
735: *** Trancate the Period Margin Fact because margin fact is rebuilt so we need to rebuild period summary ****
733: ELSE
734: /***********************************************************************************************************
735: *** Trancate the Period Margin Fact because margin fact is rebuilt so we need to rebuild period summary ****
736: ************************************************************************************************************/
737: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_F');
738: END IF;
739:
740: --
741: --
742: -- Gather table statistics these stats will be used by CBO for query optimisation.
743: --
744: --
745:
746: OPI_COLLECTION_HOOK_P.GATHER_STATS('MLOG$_OPI_EDW_MARGIN_F');
747:
748: --
749: --
750: -- Take the backup of changed rows
761: -- Gather table statistics these stats will be used by CBO for query optimisation.
762: --
763: --
764:
765: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MARGIN_PERD_ILOG');
766:
767: commit;
768:
769:
802: FROM FII_AR_TRX_DIST_F,OPI_EDW_REV_LOG
803: WHERE GL_DATE_FK_KEY <> 0
804: AND FII_AR_TRX_DIST_F.ROWID = OPI_EDW_REV_LOG.row_id;
805:
806: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MRG_KEYS');
807: INSERT INTO OPI_EDW_MRG_MAX_VALUES
808: (ORDER_LINE_ID,
809: UOM_FK_KEY,
810: PROJECT_FK_KEY,
899: AND MAXVALS.FUNCTIONAL_CURRENCY_FK_KEY = REV.FUNCTIONAL_CURRENCY_FK_KEY
900: AND MAXVALS.SET_OF_BOOKS_FK_KEY = REV.SET_OF_BOOKS_FK_KEY
901: AND REV.GL_DATE_FK_KEY <> 0;
902:
903: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MRG_MAX_VALUES');
904:
905: LOOP
906: UPDATE OPI_EDW_MARGIN_PERD_ILOG
907: SET STATUS =1
941: AND MAXVALS.SOB_FK_KEY = MRG.SOB_FK_KEY
942: AND LOG.ROW_ID = MRG.ROWID
943: AND LOG.STATUS = 1;
944:
945: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MRGIN_REV_POSTCOLL_FUR');
946:
947: UPDATE OPI_EDW_MARGIN_F MRG
948: SET (INVOICE_DATE
949: ,REVENUE_DATE_FK_KEY
955: WHERE MRG.rowid = MAXVALS.ROW_ID);
956:
957: EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows udated OPI_EDW_MARGIN_F from revenue fact'||SQL%ROWCOUNT);
958: EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
959: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_REV_POSTCOLL_FUR');
960: UPDATE OPI_EDW_MARGIN_PERD_ILOG
961: SET STATUS =2
962: WHERE STATUS = 1;
963: END LOOP;
995: FROM OPI_EDW_COGS_F,
996: OPI_EDW_COGS_LOG
997: WHERE OPI_EDW_COGS_F.ROWID = OPI_EDW_COGS_LOG.row_id;
998:
999: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MRG_KEYS');
1000:
1001: INSERT INTO OPI_EDW_MRG_MAX_VALUES
1002: (ORDER_LINE_ID,
1003: UOM_FK_KEY,
1098: AND MAXVALS.ITEM_ORG_FK_KEY = COGS.top_model_item_fk_key
1099: AND MAXVALS.BASE_CURRENCY_FK_KEY = COGS.BASE_CURRENCY_FK_KEY
1100: AND MAXVALS.GL_SET_OF_BOOKS_FK_KEY= COGS.GL_SET_OF_BOOKS_FK_KEY;
1101:
1102: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MRG_MAX_VALUES');
1103:
1104:
1105:
1106: LOOP
1144: AND MAXVALS.SOB_FK_KEY = MRG.SOB_FK_KEY
1145: AND LOG.ROW_ID = MRG.ROWID
1146: AND LOG.STATUS = 1;
1147:
1148: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MRGIN_COGSPOSTCOLL_FUR');
1149:
1150: UPDATE OPI_EDW_MARGIN_F MRG
1151: SET (ORDER_DATE
1152: ,ORDER_NO
1165:
1166: EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows udated OPI_EDW_MARGIN_F from revenue fact'||SQL%ROWCOUNT);
1167: EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1168:
1169: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_COGSPOSTCOLL_FUR');
1170:
1171: UPDATE OPI_EDW_MARGIN_PERD_ILOG
1172: SET STATUS =2
1173: WHERE STATUS = 1;
1205: SET_OF_BOOKS_FK_KEY
1206: FROM FII_AR_TRX_DIST_F
1207: WHERE GL_DATE_FK_KEY <> 0;
1208:
1209: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MRG_KEYS');
1210:
1211:
1212: INSERT INTO OPI_EDW_MRG_MAX_VALUES
1213: (ORDER_LINE_ID,
1304: AND MAXVALS.FUNCTIONAL_CURRENCY_FK_KEY = REV.FUNCTIONAL_CURRENCY_FK_KEY
1305: AND MAXVALS.SET_OF_BOOKS_FK_KEY = REV.SET_OF_BOOKS_FK_KEY
1306: AND REV.GL_DATE_FK_KEY <> 0;
1307:
1308: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MRG_MAX_VALUES');
1309:
1310: LOOP
1311: UPDATE OPI_EDW_MARGIN_PERD_ILOG
1312: SET STATUS =1
1344: AND MAXVALS.SOB_FK_KEY = MRG.SOB_FK_KEY
1345: AND LOG.ROW_ID = MRG.ROWID
1346: AND LOG.STATUS = 1;
1347:
1348: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MRGIN_REV_POSTCOLL_FUR');
1349:
1350: UPDATE OPI_EDW_MARGIN_F MRG
1351: SET (INVOICE_DATE
1352: ,REVENUE_DATE_FK_KEY
1361:
1362: EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows udated OPI_EDW_MARGIN_F from revenue fact'||SQL%ROWCOUNT);
1363: EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1364:
1365: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_REV_POSTCOLL_FUR');
1366:
1367: UPDATE OPI_EDW_MARGIN_PERD_ILOG
1368: SET STATUS =2
1369: WHERE STATUS = 1;
1371: UPDATE OPI_EDW_MARGIN_PERD_ILOG
1372: SET STATUS =0;
1373: COMMIT;
1374:
1375: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_MAX_VALUES');
1376: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_KEYS');
1377:
1378: /*********************************************************************************************
1379: *** Populate COGS data for full refresh ****
1372: SET STATUS =0;
1373: COMMIT;
1374:
1375: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_MAX_VALUES');
1376: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_KEYS');
1377:
1378: /*********************************************************************************************
1379: *** Populate COGS data for full refresh ****
1380: **********************************************************************************************/
1406: BASE_CURRENCY_FK_KEY,
1407: GL_SET_OF_BOOKS_FK_KEY
1408: FROM OPI_EDW_COGS_F;
1409: EDW_OWB_COLLECTION_UTIL.write_to_log_file('1 sqlcount ddd ' || SQL%rowcount);
1410: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MRG_KEYS');
1411:
1412: INSERT INTO OPI_EDW_MRG_MAX_VALUES
1413: (ORDER_LINE_ID,
1414: UOM_FK_KEY,
1509: AND MAXVALS.ITEM_ORG_FK_KEY = COGS.top_model_item_fk_key
1510: AND MAXVALS.BASE_CURRENCY_FK_KEY = COGS.BASE_CURRENCY_FK_KEY
1511: AND MAXVALS.GL_SET_OF_BOOKS_FK_KEY= COGS.GL_SET_OF_BOOKS_FK_KEY;
1512: EDW_OWB_COLLECTION_UTIL.write_to_log_file('2 sqlcount ddd ' || SQL%rowcount);
1513: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MRG_MAX_VALUES');
1514:
1515:
1516:
1517: LOOP
1555: AND MAXVALS.SOB_FK_KEY = MRG.SOB_FK_KEY
1556: AND LOG.ROW_ID = MRG.ROWID
1557: AND LOG.STATUS = 1;
1558: EDW_OWB_COLLECTION_UTIL.write_to_log_file('3 sqlcount ddd ' || SQL%rowcount);
1559: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MRGIN_COGSPOSTCOLL_FUR');
1560:
1561: UPDATE OPI_EDW_MARGIN_F MRG
1562: SET (ORDER_DATE
1563: ,ORDER_NO
1576:
1577: EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows udated OPI_EDW_MARGIN_F from revenue fact'||SQL%ROWCOUNT);
1578: EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1579:
1580: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_COGSPOSTCOLL_FUR');
1581:
1582: UPDATE OPI_EDW_MARGIN_PERD_ILOG
1583: SET STATUS =2
1584: WHERE STATUS = 1;
1796: ,USER_FK1_KEY);
1797:
1798: EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows moved to OPI_EDW_PERD_MARGIN_FT '||SQL%ROWCOUNT);
1799: EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1800: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_PERD_MARGIN_FT');
1801:
1802:
1803: INSERT INTO OPI_EDW_PERD_MARGIN_FUR(
1804: COGS_G
1856: AND NVL(MRGF.USER_FK1_KEY,0)=NVL(MRGFT.USER_FK1_KEY,0));
1857:
1858: EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows moved to OPI_EDW_PERD_MARGIN_FUR '||SQL%ROWCOUNT);
1859: EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1860: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_PERD_MARGIN_FUR');
1861:
1862:
1863: COMMIT;
1864:
1871:
1872: l_insert := SQL%ROWCOUNT;
1873:
1874: EDW_OWB_COLLECTION_UTIL.write_to_log_file('end Time '||to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1875: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_MARGIN_PERD_FIR');
1876:
1877:
1878: IF l_insert <> 0 THEN
1879: INSERT INTO OPI_EDW_PERD_MARGIN_F
1955: EDW_OWB_COLLECTION_UTIL.write_to_log_file('end Time '||to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1956: END IF;
1957: COMMIT;
1958:
1959: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_PERD_MARGIN_F');
1960:
1961: UPDATE OPI_EDW_PERD_MARGIN_F MRGF
1962: SET (COGS_G
1963: ,COGS_B
1997: COMMIT;
1998: UPDATE OPI_EDW_MARGIN_PERD_ILOG
1999: SET STATUS =2
2000: WHERE STATUS = 1;
2001: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FT');
2002: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FUR');
2003: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FIR');
2004: END LOOP;
2005: INSERT INTO OPI_EDW_PERD_MARGIN_FT(
1998: UPDATE OPI_EDW_MARGIN_PERD_ILOG
1999: SET STATUS =2
2000: WHERE STATUS = 1;
2001: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FT');
2002: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FUR');
2003: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FIR');
2004: END LOOP;
2005: INSERT INTO OPI_EDW_PERD_MARGIN_FT(
2006: ITEM_ORG_FK_KEY
1999: SET STATUS =2
2000: WHERE STATUS = 1;
2001: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FT');
2002: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FUR');
2003: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FIR');
2004: END LOOP;
2005: INSERT INTO OPI_EDW_PERD_MARGIN_FT(
2006: ITEM_ORG_FK_KEY
2007: ,OPERATING_UNIT_FK_KEY
2092: ,USER_FK2_KEY
2093: ,USER_FK1_KEY);
2094: COMMIT;
2095:
2096: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_PERD_MARGIN_FT');
2097:
2098:
2099: INSERT INTO OPI_EDW_PERD_MARGIN_FUR(
2100: COGS_G
2151: AND NVL(MRGF.USER_FK2_KEY,0)=NVL(MRGFT.USER_FK2_KEY,0)
2152: AND NVL(MRGF.USER_FK1_KEY,0)=NVL(MRGFT.USER_FK1_KEY,0));
2153: COMMIT;
2154:
2155: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_PERD_MARGIN_FUR');
2156:
2157:
2158: UPDATE OPI_EDW_PERD_MARGIN_F MRGF
2159: SET (COGS_G
2189: WHERE EXISTS (SELECT 1
2190: FROM OPI_EDW_PERD_MARGIN_FUR MRGFU
2191: WHERE MRGF.ROWID = MRGFU.ROW_ID1);
2192: COMMIT;
2193: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FT');
2194: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FUR');
2195: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FIR');
2196: OPI_COLLECTION_HOOK_P.TURNC_TAB('MLOG$_OPI_EDW_MARGIN_F');
2197: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FDLG');
2190: FROM OPI_EDW_PERD_MARGIN_FUR MRGFU
2191: WHERE MRGF.ROWID = MRGFU.ROW_ID1);
2192: COMMIT;
2193: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FT');
2194: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FUR');
2195: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FIR');
2196: OPI_COLLECTION_HOOK_P.TURNC_TAB('MLOG$_OPI_EDW_MARGIN_F');
2197: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FDLG');
2198: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FD');
2191: WHERE MRGF.ROWID = MRGFU.ROW_ID1);
2192: COMMIT;
2193: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FT');
2194: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FUR');
2195: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FIR');
2196: OPI_COLLECTION_HOOK_P.TURNC_TAB('MLOG$_OPI_EDW_MARGIN_F');
2197: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FDLG');
2198: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FD');
2199: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_ILOG');
2192: COMMIT;
2193: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FT');
2194: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FUR');
2195: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FIR');
2196: OPI_COLLECTION_HOOK_P.TURNC_TAB('MLOG$_OPI_EDW_MARGIN_F');
2197: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FDLG');
2198: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FD');
2199: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_ILOG');
2200: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_REV_LOG');
2193: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FT');
2194: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FUR');
2195: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FIR');
2196: OPI_COLLECTION_HOOK_P.TURNC_TAB('MLOG$_OPI_EDW_MARGIN_F');
2197: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FDLG');
2198: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FD');
2199: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_ILOG');
2200: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_REV_LOG');
2201: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_MAX_VALUES');
2194: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_PERD_MARGIN_FUR');
2195: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FIR');
2196: OPI_COLLECTION_HOOK_P.TURNC_TAB('MLOG$_OPI_EDW_MARGIN_F');
2197: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FDLG');
2198: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FD');
2199: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_ILOG');
2200: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_REV_LOG');
2201: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_MAX_VALUES');
2202: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_KEYS');
2195: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FIR');
2196: OPI_COLLECTION_HOOK_P.TURNC_TAB('MLOG$_OPI_EDW_MARGIN_F');
2197: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FDLG');
2198: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FD');
2199: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_ILOG');
2200: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_REV_LOG');
2201: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_MAX_VALUES');
2202: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_KEYS');
2203: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_COGS_LOG');
2196: OPI_COLLECTION_HOOK_P.TURNC_TAB('MLOG$_OPI_EDW_MARGIN_F');
2197: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FDLG');
2198: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FD');
2199: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_ILOG');
2200: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_REV_LOG');
2201: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_MAX_VALUES');
2202: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_KEYS');
2203: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_COGS_LOG');
2204: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_COGSPOSTCOLL_FUR');
2197: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FDLG');
2198: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FD');
2199: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_ILOG');
2200: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_REV_LOG');
2201: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_MAX_VALUES');
2202: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_KEYS');
2203: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_COGS_LOG');
2204: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_COGSPOSTCOLL_FUR');
2205: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_REV_POSTCOLL_FUR');
2198: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_FD');
2199: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_ILOG');
2200: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_REV_LOG');
2201: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_MAX_VALUES');
2202: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_KEYS');
2203: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_COGS_LOG');
2204: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_COGSPOSTCOLL_FUR');
2205: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_REV_POSTCOLL_FUR');
2206: END POST_MARGIN_COLL;
2199: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MARGIN_PERD_ILOG');
2200: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_REV_LOG');
2201: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_MAX_VALUES');
2202: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_KEYS');
2203: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_COGS_LOG');
2204: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_COGSPOSTCOLL_FUR');
2205: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_REV_POSTCOLL_FUR');
2206: END POST_MARGIN_COLL;
2207: PROCEDURE PRE_MARGIN_COLL IS
2200: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_REV_LOG');
2201: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_MAX_VALUES');
2202: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_KEYS');
2203: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_COGS_LOG');
2204: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_COGSPOSTCOLL_FUR');
2205: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_REV_POSTCOLL_FUR');
2206: END POST_MARGIN_COLL;
2207: PROCEDURE PRE_MARGIN_COLL IS
2208: BEGIN
2201: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_MAX_VALUES');
2202: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRG_KEYS');
2203: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_COGS_LOG');
2204: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_COGSPOSTCOLL_FUR');
2205: OPI_COLLECTION_HOOK_P.TURNC_TAB('OPI_EDW_MRGIN_REV_POSTCOLL_FUR');
2206: END POST_MARGIN_COLL;
2207: PROCEDURE PRE_MARGIN_COLL IS
2208: BEGIN
2209: /* get global session parameters */
2231:
2232: COMMIT;
2233:
2234: INSERT INTO OPI_EDW_REV_LOG (select /*+ ALL_ROWS */ distinct m_row$$,0 from MLOG$_FII_AR_TRX_DIST_F);
2235: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_REV_LOG');
2236: END;
2237: PROCEDURE POST_COGS_COLL IS
2238: BEGIN
2239:
2248:
2249: COMMIT;
2250:
2251: INSERT INTO OPI_EDW_COGS_LOG (select /*+ ALL_ROWS */ distinct m_row$$,0 from MLOG$_OPI_EDW_COGS_F);
2252: OPI_COLLECTION_HOOK_P.GATHER_STATS('OPI_EDW_COGS_LOG');
2253: END;
2254: PROCEDURE GATHER_STATS(P_TABLE_NAME VARCHAR2) IS
2255: l_TABLE_OWNER VARCHAR2(32);
2256: BEGIN
2299: NULL;
2300: END TURNC_TAB;
2301:
2302:
2303: End OPI_COLLECTION_HOOK_P;