eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OPI_COLLECTION_HOOK_P dependencies on OPI_EDW_MARGIN_F

Line 583: IF p_base_fact_name <> 'OPI_EDW_MARGIN_F' THEN

579: -- First add all the changed rows from margin to period summary
580: -- then subtract old rows backed up by following insert statement.
581: --
582: --
583: IF p_base_fact_name <> 'OPI_EDW_MARGIN_F' THEN
584: EXECUTE IMMEDIATE ' insert into OPI_EDW_MARGIN_PERD_FDLG (SOB_FK_KEY
585: ,USER_MEASURE5
586: ,USER_MEASURE4
587: ,USER_MEASURE3

Line 701: FROM MLOG$_OPI_EDW_MARGIN_F a

697: ,SHIP_LOCATION_FK_KEY
698: ,UNIT_SELLING_PRICE
699: ,LAST_UPDATE_DATE
700: ,MIN(LAST_UPDATE_DATE) OVER (PARTITION BY M_ROW$$) FIRST_UPDATE_DATE
701: FROM MLOG$_OPI_EDW_MARGIN_F a
702: WHERE a.DMLTYPE$$ IN (''D'',''U'')
703: AND a.GL_PERIOD_FK_KEY IS NOT NULL
704: AND a.GL_PERIOD_FK_KEY <> ''0'' )
705: WHERE

Line 746: OPI_COLLECTION_HOOK_P.GATHER_STATS('MLOG$_OPI_EDW_MARGIN_F');

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

Line 754: insert into OPI_EDW_MARGIN_PERD_ILOG(ROW_ID,STATUS) (select /+ ALL_ROWS / distinct m_row$$ ,0 from MLOG$_OPI_EDW_MARGIN_F);

750: -- Take the backup of changed rows
751: --
752: --
753:
754: insert into OPI_EDW_MARGIN_PERD_ILOG(ROW_ID,STATUS) (select /*+ ALL_ROWS */ distinct m_row$$ ,0 from MLOG$_OPI_EDW_MARGIN_F);
755:
756: EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows to be processed to build margin period summary'||SQL%ROWCOUNT);
757: EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
758:

Line 918: SELECT /*+ INDEX(MRG, OPI_EDW_MARGIN_F_U)

914: (MAX_GL_POSTED_DATE,
915: MAX_UNIT_SELLING_PRICE,
916: GL_DATE_FK_KEY,
917: ROW_ID)
918: SELECT /*+ INDEX(MRG, OPI_EDW_MARGIN_F_U)
919: INDEX(LOG, OPI_EDW_MARGIN_PERD_ILOG_U1) */
920: MAXVALS.MAX_GL_POSTED_DATE,
921: MAXVALS.MAX_UNIT_SELLING_PRICE,
922: MAXVALS.GL_DATE_FK_KEY,

Line 926: OPI_EDW_MARGIN_F MRG,

922: MAXVALS.GL_DATE_FK_KEY,
923: MRG.ROWID
924: FROM
925: OPI_EDW_MARGIN_PERD_ILOG LOG,
926: OPI_EDW_MARGIN_F MRG,
927: OPI_EDW_MRG_MAX_VALUES MAXVALS
928: WHERE
929: MAXVALS.ORDER_LINE_ID = MRG.ORDER_LINE_ID
930: AND MAXVALS.UOM_FK_KEY = MRG.UOM_FK_KEY

Line 947: UPDATE OPI_EDW_MARGIN_F MRG

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
950: ,UNIT_SELLING_PRICE)=(SELECT distinct MAXVALS.MAX_GL_POSTED_DATE
951: ,MAXVALS.GL_DATE_FK_KEY

Line 957: EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows udated OPI_EDW_MARGIN_F from revenue fact'||SQL%ROWCOUNT);

953: where EXISTS (SELECT 1
954: FROM OPI_EDW_MRGIN_REV_POSTCOLL_FUR MAXVALS
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

Line 1121: SELECT /+ INDEX(MRG, OPI_EDW_MARGIN_F_U) / MAXVALS.MAX_ORDER_DATE,

1117: COGS_DATE_FK_KEY,
1118: MAX_ORDER_NUMBER,
1119: MAX_COGS_DATE,
1120: ROW_ID)
1121: SELECT /*+ INDEX(MRG, OPI_EDW_MARGIN_F_U) */ MAXVALS.MAX_ORDER_DATE,
1122: MAXVALS.SHIP_INV_LOCATOR_FK_KEY,
1123: MAXVALS.COGS_DATE_FK_KEY,
1124: MAXVALS.MAX_ORDER_NUMBER,
1125: MAXVALS.MAX_COGS_DATE,

Line 1129: OPI_EDW_MARGIN_F MRG,

1125: MAXVALS.MAX_COGS_DATE,
1126: MRG.ROWID
1127: FROM
1128: OPI_EDW_MARGIN_PERD_ILOG LOG,
1129: OPI_EDW_MARGIN_F MRG,
1130: OPI_EDW_MRG_MAX_VALUES MAXVALS
1131: WHERE
1132: MAXVALS.ORDER_LINE_ID = MRG.ORDER_LINE_ID
1133: AND MAXVALS.UOM_FK_KEY = MRG.UOM_FK_KEY

Line 1150: UPDATE OPI_EDW_MARGIN_F MRG

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
1153: ,SHIP_DATE
1154: ,SHIP_LOCATION_FK_KEY

Line 1166: EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows udated OPI_EDW_MARGIN_F from revenue fact'||SQL%ROWCOUNT);

1162: where EXISTS (SELECT 1
1163: FROM OPI_EDW_MRGIN_COGSPOSTCOLL_FUR MAXVALS
1164: WHERE MRG.rowid = MAXVALS.ROW_ID);
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:

Line 1178: ELSIF p_base_fact_name = 'OPI_EDW_MARGIN_F' /* if it is a full Refesh */

1174: END LOOP;
1175: UPDATE OPI_EDW_MARGIN_PERD_ILOG
1176: SET STATUS =0;
1177: COMMIT;
1178: ELSIF p_base_fact_name = 'OPI_EDW_MARGIN_F' /* if it is a full Refesh */
1179: THEN
1180: INSERT INTO OPI_EDW_MRG_KEYS (ORDER_LINE_ID,
1181: UOM_FK_KEY,
1182: PROJECT_FK_KEY,

Line 1329: OPI_EDW_MARGIN_F MRG,

1325: MAXVALS.GL_DATE_FK_KEY,
1326: MRG.ROWID
1327: FROM
1328: OPI_EDW_MARGIN_PERD_ILOG LOG,
1329: OPI_EDW_MARGIN_F MRG,
1330: OPI_EDW_MRG_MAX_VALUES MAXVALS
1331: WHERE
1332: MAXVALS.ORDER_LINE_ID = MRG.ORDER_LINE_ID
1333: AND MAXVALS.UOM_FK_KEY = MRG.UOM_FK_KEY

Line 1350: UPDATE OPI_EDW_MARGIN_F MRG

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
1353: ,UNIT_SELLING_PRICE)=(SELECT distinct MAXVALS.MAX_GL_POSTED_DATE
1354: ,MAXVALS.GL_DATE_FK_KEY

Line 1362: EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows udated OPI_EDW_MARGIN_F from revenue fact'||SQL%ROWCOUNT);

1358: where EXISTS (SELECT 1
1359: FROM OPI_EDW_MRGIN_REV_POSTCOLL_FUR MAXVALS
1360: WHERE MRG.rowid = MAXVALS.ROW_ID);
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:

Line 1540: OPI_EDW_MARGIN_F MRG,

1536: MAXVALS.MAX_COGS_DATE,
1537: MRG.ROWID
1538: FROM
1539: OPI_EDW_MARGIN_PERD_ILOG LOG,
1540: OPI_EDW_MARGIN_F MRG,
1541: OPI_EDW_MRG_MAX_VALUES MAXVALS
1542: WHERE
1543: MAXVALS.ORDER_LINE_ID = MRG.ORDER_LINE_ID
1544: AND MAXVALS.UOM_FK_KEY = MRG.UOM_FK_KEY

Line 1561: UPDATE OPI_EDW_MARGIN_F MRG

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
1564: ,SHIP_DATE
1565: ,SHIP_LOCATION_FK_KEY

Line 1577: EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows udated OPI_EDW_MARGIN_F from revenue fact'||SQL%ROWCOUNT);

1573: where EXISTS (SELECT 1
1574: FROM OPI_EDW_MRGIN_COGSPOSTCOLL_FUR MAXVALS
1575: WHERE MRG.rowid = MAXVALS.ROW_ID);
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:

Line 1590: EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows udated OPI_EDW_MARGIN_F from cogs fact'||SQL%ROWCOUNT);

1586: UPDATE OPI_EDW_MARGIN_PERD_ILOG
1587: SET STATUS =0;
1588: COMMIT;
1589:
1590: EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows udated OPI_EDW_MARGIN_F from cogs fact'||SQL%ROWCOUNT);
1591: EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1592:
1593: END IF;
1594: /****************************************************************************************+

Line 1612: UPDATE OPI_EDW_MARGIN_F MRG

1608: *** Update Margin Date FK Key with COGS Date FK Key if Ship date is **
1609: *** greater than or equal to Invoice Date **
1610: ***********************************************************************/
1611:
1612: UPDATE OPI_EDW_MARGIN_F MRG
1613: SET MARGIN_DATE_FK_KEY = MRG.COGS_DATE_FK_KEY,
1614: GL_PERIOD_FK_KEY =
1615: (SELECT TIM2.CDAY_CAL_DAY_PK_KEY
1616: FROM EDW_TIME_M TIM1,

Line 1636: UPDATE OPI_EDW_MARGIN_F MRG

1632: *** Update Margin Date FK Key with Revenue Date FK Key if Ship date is **
1633: *** Less than Invoice Date **
1634: **************************************************************************/
1635: --3836905 -- Added this for RMA transcations
1636: UPDATE OPI_EDW_MARGIN_F MRG
1637: SET MARGIN_DATE_FK_KEY = MRG.COGS_DATE_FK_KEY,
1638: invoice_date= MRG.ship_date ,
1639: GL_PERIOD_FK_KEY =
1640: (SELECT TIM2.CDAY_CAL_DAY_PK_KEY

Line 1656: UPDATE OPI_EDW_MARGIN_F MRG

1652: FROM OPI_EDW_MARGIN_PERD_ILOG
1653: WHERE MRG.ROWID = ROW_ID
1654: AND STATUS = 1);
1655: -- End of addition --
1656: UPDATE OPI_EDW_MARGIN_F MRG
1657: SET MARGIN_DATE_FK_KEY = MRG.REVENUE_DATE_FK_KEY,
1658: GL_PERIOD_FK_KEY =
1659: (SELECT TIM2.CDAY_CAL_DAY_PK_KEY
1660: FROM EDW_TIME_M TIM1,

Line 1684: EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows udated OPI_EDW_MARGIN_F generated period FK KEY'||SQL%ROWCOUNT);

1680: UPDATE OPI_EDW_MARGIN_PERD_ILOG
1681: SET STATUS =0;
1682: COMMIT;
1683:
1684: EDW_OWB_COLLECTION_UTIL.write_to_log_file('Number of rows udated OPI_EDW_MARGIN_F generated period FK KEY'||SQL%ROWCOUNT);
1685: EDW_OWB_COLLECTION_UTIL.write_to_log_file(to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1686: /***************************************************************************************+
1687: ***** Delete Snapshot log of margin. Since this on derived fact collection engine *****
1688: ***** doesn't delete it. *****

Line 1692: -- delete MLOG$_OPI_EDW_MARGIN_F;

1688: ***** doesn't delete it. *****
1689: +****************************************************************************************/
1690:
1691:
1692: -- delete MLOG$_OPI_EDW_MARGIN_F;
1693: LOOP
1694: UPDATE OPI_EDW_MARGIN_PERD_ILOG
1695: SET STATUS =1
1696: WHERE STATUS = 0 AND

Line 1772: FROM OPI_EDW_MARGIN_F, OPI_EDW_MARGIN_PERD_ILOG

1768: ,SUM(USER_MEASURE4)
1769: ,SUM(USER_MEASURE3)
1770: ,SUM(USER_MEASURE2)
1771: ,SUM(USER_MEASURE1)
1772: FROM OPI_EDW_MARGIN_F, OPI_EDW_MARGIN_PERD_ILOG
1773: WHERE OPI_EDW_MARGIN_PERD_ILOG.ROW_ID = OPI_EDW_MARGIN_F.ROWID
1774: AND OPI_EDW_MARGIN_PERD_ILOG.STATUS = 1
1775: AND GL_PERIOD_FK_KEY IS NOT NULL
1776: AND GL_PERIOD_FK_KEY <> '0'

Line 1773: WHERE OPI_EDW_MARGIN_PERD_ILOG.ROW_ID = OPI_EDW_MARGIN_F.ROWID

1769: ,SUM(USER_MEASURE3)
1770: ,SUM(USER_MEASURE2)
1771: ,SUM(USER_MEASURE1)
1772: FROM OPI_EDW_MARGIN_F, OPI_EDW_MARGIN_PERD_ILOG
1773: WHERE OPI_EDW_MARGIN_PERD_ILOG.ROW_ID = OPI_EDW_MARGIN_F.ROWID
1774: AND OPI_EDW_MARGIN_PERD_ILOG.STATUS = 1
1775: AND GL_PERIOD_FK_KEY IS NOT NULL
1776: AND GL_PERIOD_FK_KEY <> '0'
1777: GROUP BY

Line 2196: OPI_COLLECTION_HOOK_P.TURNC_TAB('MLOG$_OPI_EDW_MARGIN_F');

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');

Line 2215: execute immediate 'truncate table ' || g_opi_schema || '.MLOG$_OPI_EDW_MARGIN_F';

2211: g_industry, g_opi_schema)) THEN
2212: RAISE_APPLICATION_ERROR (-20000, 'Unable to get session information.');
2213: END IF;
2214:
2215: execute immediate 'truncate table ' || g_opi_schema || '.MLOG$_OPI_EDW_MARGIN_F';
2216:
2217: COMMIT;
2218:
2219: END PRE_MARGIN_COLL;

Line 2229: execute immediate 'truncate table ' || g_opi_schema || '.MLOG$_OPI_EDW_MARGIN_F';

2225: g_industry, g_opi_schema)) THEN
2226: RAISE_APPLICATION_ERROR (-20000, 'Unable to get session information.');
2227: END IF;
2228:
2229: execute immediate 'truncate table ' || g_opi_schema || '.MLOG$_OPI_EDW_MARGIN_F';
2230: execute immediate 'truncate table ' || g_opi_schema || '.OPI_EDW_REV_LOG';
2231:
2232: COMMIT;
2233:

Line 2246: execute immediate 'truncate table ' || g_opi_schema || '.MLOG$_OPI_EDW_MARGIN_F';

2242: g_industry, g_opi_schema)) THEN
2243: RAISE_APPLICATION_ERROR (-20000, 'Unable to get session information.');
2244: END IF;
2245:
2246: execute immediate 'truncate table ' || g_opi_schema || '.MLOG$_OPI_EDW_MARGIN_F';
2247: execute immediate 'truncate table ' || g_opi_schema || '.OPI_EDW_COGS_LOG';
2248:
2249: COMMIT;
2250: