[Home] [Help]
255: retcode := 0;
256:
257: -- big insert for OPI COGS
258:
259: INSERT /*+ append parallel(m) */ INTO opi_dbi_cogs_fstg m (
260: m.inventory_item_id,
261: m.organization_id,
262: m.order_line_id,
263: m.top_model_line_id,
450: To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
451:
452: retcode := 0;
453:
454: INSERT /*+ append */ INTO opi_dbi_cogs_fstg m (
455: m.inventory_item_id,
456: m.organization_id,
457: m.order_line_id,
458: m.top_model_line_id,
638: To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
639:
640: retcode := 0;
641:
642: INSERT /*+ append parallel(m) */ INTO opi_dbi_cogs_fstg m (
643: m.inventory_item_id,
644: m.organization_id,
645: m.order_line_id,
646: m.top_model_line_id,
964: bis_collection_utilities.put_line('Enter incremental_opm_cogs() ' ||
965: To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
966: retcode := 0;
967:
968: INSERT /*+ append */ INTO opi_dbi_cogs_fstg m (
969: m.inventory_item_id,
970: m.organization_id,
971: m.order_line_id,
972: m.top_model_line_id,
1375: conv.transaction_date) cogs_date,
1376: conv.f_currency_code func_currency_code
1377: FROM opi_dbi_cogsf_conv_rates conv,
1378: mtl_parameters mp,
1379: (SELECT /*+ parallel (opi_dbi_cogs_fstg) */
1380: DISTINCT organization_id, trunc (cogs_date) cogs_date
1381: FROM opi_dbi_cogs_fstg) to_conv
1382: WHERE nvl (conv.conversion_rate, -999) < 0 -- null is not fine
1383: AND mp.organization_id = to_conv.organization_id
1377: FROM opi_dbi_cogsf_conv_rates conv,
1378: mtl_parameters mp,
1379: (SELECT /*+ parallel (opi_dbi_cogs_fstg) */
1380: DISTINCT organization_id, trunc (cogs_date) cogs_date
1381: FROM opi_dbi_cogs_fstg) to_conv
1382: WHERE nvl (conv.conversion_rate, -999) < 0 -- null is not fine
1383: AND mp.organization_id = to_conv.organization_id
1384: AND conv.transaction_date (+) = to_conv.cogs_date
1385: AND conv.organization_id (+) = to_conv.organization_id
1397: conv.transaction_date) cogs_date,
1398: conv.f_currency_code func_currency_code
1399: FROM opi_dbi_cogsf_conv_rates conv,
1400: mtl_parameters mp,
1401: (SELECT /*+ parallel (opi_dbi_cogs_fstg) */
1402: DISTINCT organization_id, trunc (cogs_date) cogs_date
1403: FROM opi_dbi_cogs_fstg) to_conv
1404: WHERE nvl (conv.sec_conversion_rate, 999) < 0 -- null is fine
1405: AND mp.organization_id = to_conv.organization_id
1399: FROM opi_dbi_cogsf_conv_rates conv,
1400: mtl_parameters mp,
1401: (SELECT /*+ parallel (opi_dbi_cogs_fstg) */
1402: DISTINCT organization_id, trunc (cogs_date) cogs_date
1403: FROM opi_dbi_cogs_fstg) to_conv
1404: WHERE nvl (conv.sec_conversion_rate, 999) < 0 -- null is fine
1405: AND mp.organization_id = to_conv.organization_id
1406: AND conv.transaction_date (+) = to_conv.cogs_date
1407: AND conv.organization_id (+) = to_conv.organization_id)
1484: fii_currency.get_global_rate_secondary (
1485: curr_codes.currency_code,
1486: to_conv.cogs_date)))
1487: FROM
1488: (SELECT /*+ parallel (opi_dbi_cogs_fstg) */
1489: DISTINCT organization_id, trunc (cogs_date) cogs_date
1490: FROM opi_dbi_cogs_fstg) to_conv,
1491: (SELECT /*+ leading (hoi) full (hoi) use_hash (gsob)
1492: parallel (hoi) parallel (gsob)*/
1486: to_conv.cogs_date)))
1487: FROM
1488: (SELECT /*+ parallel (opi_dbi_cogs_fstg) */
1489: DISTINCT organization_id, trunc (cogs_date) cogs_date
1490: FROM opi_dbi_cogs_fstg) to_conv,
1491: (SELECT /*+ leading (hoi) full (hoi) use_hash (gsob)
1492: parallel (hoi) parallel (gsob)*/
1493: DISTINCT hoi.organization_id, gsob.currency_code
1494: FROM hr_organization_information hoi,
1632: conv.transaction_date) cogs_date,
1633: conv.f_currency_code func_currency_code
1634: FROM opi_dbi_cogsf_conv_rates conv,
1635: mtl_parameters mp,
1636: (SELECT /*+ parallel (opi_dbi_cogs_fstg) */
1637: DISTINCT organization_id, trunc (cogs_date) cogs_date
1638: FROM opi_dbi_cogs_fstg) to_conv
1639: WHERE nvl (conv.conversion_rate, -999) < 0 -- null is not fine
1640: AND mp.organization_id = to_conv.organization_id
1634: FROM opi_dbi_cogsf_conv_rates conv,
1635: mtl_parameters mp,
1636: (SELECT /*+ parallel (opi_dbi_cogs_fstg) */
1637: DISTINCT organization_id, trunc (cogs_date) cogs_date
1638: FROM opi_dbi_cogs_fstg) to_conv
1639: WHERE nvl (conv.conversion_rate, -999) < 0 -- null is not fine
1640: AND mp.organization_id = to_conv.organization_id
1641: AND conv.transaction_date (+) = to_conv.cogs_date
1642: AND conv.organization_id (+) = to_conv.organization_id
1653: conv.transaction_date) cogs_date,
1654: conv.f_currency_code func_currency_code
1655: FROM opi_dbi_cogsf_conv_rates conv,
1656: mtl_parameters mp,
1657: (SELECT /*+ parallel (opi_dbi_cogs_fstg) */
1658: DISTINCT organization_id, trunc (cogs_date) cogs_date
1659: FROM opi_dbi_cogs_fstg) to_conv
1660: WHERE nvl (conv.sec_conversion_rate, 999) < 0 -- null is fine
1661: AND mp.organization_id = to_conv.organization_id
1655: FROM opi_dbi_cogsf_conv_rates conv,
1656: mtl_parameters mp,
1657: (SELECT /*+ parallel (opi_dbi_cogs_fstg) */
1658: DISTINCT organization_id, trunc (cogs_date) cogs_date
1659: FROM opi_dbi_cogs_fstg) to_conv
1660: WHERE nvl (conv.sec_conversion_rate, 999) < 0 -- null is fine
1661: AND mp.organization_id = to_conv.organization_id
1662: AND conv.transaction_date (+) = to_conv.cogs_date
1663: AND conv.organization_id (+) = to_conv.organization_id)
1742: to_conv.cogs_date)))
1743: FROM
1744: (SELECT
1745: DISTINCT organization_id, trunc (cogs_date) cogs_date
1746: FROM opi_dbi_cogs_fstg) to_conv,
1747: (SELECT
1748: DISTINCT hoi.organization_id, gsob.currency_code
1749: FROM hr_organization_information hoi,
1750: gl_sets_of_books gsob
1862:
1863: bis_collection_utilities.put_line ('Initial Load COGS starts at '
1864: || To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
1865:
1866: IF bis_collection_utilities.setup('OPI_DBI_COGS_F' ) = false THEN
1867: --{
1868: RAISE_APPLICATION_ERROR(-20000, errbuf);
1869: --}
1870: END IF;
1896: execute immediate 'truncate table ' || l_opi_schema
1897: || '.opi_dbi_cogs_run_log ';
1898:
1899: execute immediate 'truncate table ' || l_opi_schema
1900: || '.opi_dbi_cogs_fstg ';
1901:
1902: -- bug 3863905- mv log is now dropped before initial load
1903: -- we shouldnt be truncating mv log anymore
1904:
1902: -- bug 3863905- mv log is now dropped before initial load
1903: -- we shouldnt be truncating mv log anymore
1904:
1905: -- execute immediate 'truncate table ' || l_opi_schema
1906: --|| '.mlog$_opi_dbi_cogs_f';
1907:
1908: execute immediate 'truncate table ' || l_opi_schema
1909: || '.opi_dbi_cogs_f PURGE MATERIALIZED VIEW LOG';
1910: --}
1905: -- execute immediate 'truncate table ' || l_opi_schema
1906: --|| '.mlog$_opi_dbi_cogs_f';
1907:
1908: execute immediate 'truncate table ' || l_opi_schema
1909: || '.opi_dbi_cogs_f PURGE MATERIALIZED VIEW LOG';
1910: --}
1911: ELSE
1912: --{
1913: RAISE schema_info_not_found;
1976:
1977: l_stmt_num := 80;
1978:
1979:
1980: gather_stats(p_table_name => 'OPI_DBI_COGS_FSTG');
1981:
1982: get_cogs_conversion_rate( errbuf => errbuf, retcode => retcode );
1983: commit;
1984:
1996: --{
1997: -- Load from staging table into fact table
1998: l_stmt_num := 90;
1999:
2000: INSERT /*+ append parallel(m) */ INTO opi_dbi_cogs_f m (
2001: m.inventory_item_id,
2002: m.organization_id,
2003: m.order_line_id,
2004: m.top_model_line_id,
2048: g_program_id,
2049: g_program_login_id,
2050: g_program_application_id,
2051: g_request_id
2052: FROM opi_dbi_cogs_fstg stg,
2053: opi_dbi_cogsf_conv_rates rate
2054: WHERE stg.organization_id = rate.organization_id
2055: AND trunc (stg.cogs_date) = rate.transaction_date
2056: GROUP BY
2067: stg.turns_cogs_flag,
2068: stg.internal_flag;
2069: l_row_count := sql%rowcount;
2070:
2071: bis_collection_utilities.put_line('Loaded ' || l_row_count || ' rows into opi_dbi_cogs_f.');
2072:
2073: -- Report etl success
2074: l_stmt_num := 100;
2075:
2094: execute immediate 'truncate table ' || l_opi_schema
2095: || '.opi_dbi_cogsf_conv_rates ';
2096:
2097: execute immediate 'truncate table ' || l_opi_schema
2098: || '.opi_dbi_cogs_fstg ';
2099:
2100: bis_collection_utilities.wrapup(p_status => TRUE,
2101: p_count => l_row_count,
2102: p_message => 'successful in initial_load_cogs.');
2166:
2167: bis_collection_utilities.put_line('Incrmental Load COGS starts at '
2168: || To_char(Sysdate, 'hh24:mi:ss dd-mon-yyyy'));
2169:
2170: IF bis_collection_utilities.setup( 'OPI_DBI_COGS_F' ) = false THEN
2171: --{
2172: RAISE_APPLICATION_ERROR(-20000, errbuf);
2173: --}
2174: END IF;
2195: execute immediate 'truncate table ' || l_opi_schema
2196: || '.opi_dbi_cogsf_conv_rates ';
2197:
2198: execute immediate 'truncate table ' || l_opi_schema
2199: || '.opi_dbi_cogs_fstg ';
2200: --}
2201: ELSE
2202: --{
2203: RAISE schema_info_not_found;
2263: -- For improve perf, need to commit in stg/conversion rate tables
2264: -- and gather statistics
2265:
2266: l_stmt_num := 80;
2267: gather_stats(p_table_name => 'OPI_DBI_COGS_FSTG');
2268:
2269: get_cogs_conversion_rate_incr( errbuf => errbuf, retcode => retcode );
2270:
2271: commit;
2284: l_stmt_num := 90;
2285:
2286: -- Load data from staging table into fact table
2287:
2288: MERGE /*+ index(m, OPI_DBI_COGS_F_N2) */ INTO opi_dbi_cogs_f m
2289: USING (
2290: SELECT
2291: stg.inventory_item_id,
2292: stg.organization_id,
2303: stg.turns_cogs_flag,
2304: stg.internal_flag,
2305: min(rate.conversion_rate) conversion_rate,
2306: min(rate.sec_conversion_rate) sec_conversion_rate
2307: FROM opi_dbi_cogs_fstg stg,
2308: opi_dbi_cogsf_conv_rates rate
2309: WHERE stg.organization_id = rate.organization_id
2310: AND trunc (stg.cogs_date) = rate.transaction_date
2311: GROUP BY
2388:
2389:
2390: l_row_count := sql%rowcount;
2391:
2392: bis_collection_utilities.put_line('Loaded ' || l_row_count || ' rows into opi_dbi_cogs_f.');
2393: -- Report etl success
2394: l_stmt_num := 100;
2395:
2396: opi_dbi_bounds_pkg.set_load_successful(p_etl_type => 'COGS', p_load_type => 'INCR');
2413: execute immediate 'truncate table ' || l_opi_schema
2414: || '.opi_dbi_cogsf_conv_rates ';
2415:
2416: execute immediate 'truncate table ' || l_opi_schema
2417: || '.opi_dbi_cogs_fstg ';
2418:
2419: bis_collection_utilities.wrapup( p_status => TRUE,
2420: p_count => l_row_count,
2421: p_message => 'successful in incremental_load_cogs.');