[Home] [Help]
425: -- Truncate the staging table, commit the data to the fact table
426: -- and update the CCA related bounds in one database transaction
427: -- for consistency.
428: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
429: 'opi_dbi_inv_cca_stg');
430:
431: l_stmt_id := 170;
432: -- Truncate the MMT staging table, commit the data to the fact table
433: -- and update the CCA related bounds in one database transaction
672:
673: l_stmt_id := 120;
674: -- Staging table
675: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
676: 'opi_dbi_inv_cca_stg');
677:
678: l_stmt_id := 130;
679: -- Staging table
680: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
1006: --
1007: -- to MSI - on org and item. This should allow use of the U1 index.
1008: --
1009: -- to SUBS - on org and subinventory code
1010: INSERT /*+ append parallel (opi_dbi_inv_cca_stg) */
1011: INTO opi_dbi_inv_cca_stg (
1012: organization_id,
1013: inventory_item_id,
1014: cycle_count_header_id,
1007: -- to MSI - on org and item. This should allow use of the U1 index.
1008: --
1009: -- to SUBS - on org and subinventory code
1010: INSERT /*+ append parallel (opi_dbi_inv_cca_stg) */
1011: INTO opi_dbi_inv_cca_stg (
1012: organization_id,
1013: inventory_item_id,
1014: cycle_count_header_id,
1015: abc_class_id,
1313: --
1314: -- to SUBS - on org and subinventory code
1315:
1316:
1317: INSERT /*+ append parallel (opi_dbi_inv_cca_stg) */
1318: INTO opi_dbi_inv_cca_stg (
1319: organization_id,
1320: inventory_item_id,
1321: cycle_count_header_id,
1314: -- to SUBS - on org and subinventory code
1315:
1316:
1317: INSERT /*+ append parallel (opi_dbi_inv_cca_stg) */
1318: INTO opi_dbi_inv_cca_stg (
1319: organization_id,
1320: inventory_item_id,
1321: cycle_count_header_id,
1322: abc_class_id,
1601: -- .
1602:
1603:
1604:
1605: INSERT /*+ append parallel (opi_dbi_inv_cca_stg) */
1606: INTO opi_dbi_inv_cca_stg (
1607: organization_id,
1608: inventory_item_id,
1609: cycle_count_header_id,
1602:
1603:
1604:
1605: INSERT /*+ append parallel (opi_dbi_inv_cca_stg) */
1606: INTO opi_dbi_inv_cca_stg (
1607: organization_id,
1608: inventory_item_id,
1609: cycle_count_header_id,
1610: abc_class_id,
1711: The conversion rate work table was truncated during
1712: the initialization phase.
1713:
1714: Get the currency conversion rates based on the data in
1715: OPI_DBI_INV_CCA_STG using the fii_currency.get_global_rate_primary
1716: API for the primary global currency and
1717: fii_currency.get_global_rate_secondary for the secondary global currency.
1718: The primary currency API:
1719: 1. finds the conversion rate if one exists.
1805: conv.approval_date) approval_date,
1806: conv.func_currency_code
1807: FROM opi_dbi_inv_cca_conv conv,
1808: mtl_parameters mp,
1809: (SELECT /*+ parallel (opi_dbi_inv_cca_stg) */
1810: DISTINCT organization_id, approval_date
1811: FROM opi_dbi_inv_cca_stg) to_conv
1812: WHERE nvl (conv.conversion_rate, -999) < 0 -- null is not fine
1813: AND mp.organization_id = to_conv.organization_id
1807: FROM opi_dbi_inv_cca_conv conv,
1808: mtl_parameters mp,
1809: (SELECT /*+ parallel (opi_dbi_inv_cca_stg) */
1810: DISTINCT organization_id, approval_date
1811: FROM opi_dbi_inv_cca_stg) to_conv
1812: WHERE nvl (conv.conversion_rate, -999) < 0 -- null is not fine
1813: AND mp.organization_id = to_conv.organization_id
1814: AND conv.approval_date (+) = to_conv.approval_date
1815: AND conv.organization_id (+) = to_conv.organization_id
1827: conv.approval_date) approval_date,
1828: conv.func_currency_code
1829: FROM opi_dbi_inv_cca_conv conv,
1830: mtl_parameters mp,
1831: (SELECT /*+ parallel (opi_dbi_inv_cca_stg) */
1832: DISTINCT organization_id, approval_date
1833: FROM opi_dbi_inv_cca_stg) to_conv
1834: WHERE nvl (conv.sec_conversion_rate, 999) < 0 -- null is fine
1835: AND mp.organization_id = to_conv.organization_id
1829: FROM opi_dbi_inv_cca_conv conv,
1830: mtl_parameters mp,
1831: (SELECT /*+ parallel (opi_dbi_inv_cca_stg) */
1832: DISTINCT organization_id, approval_date
1833: FROM opi_dbi_inv_cca_stg) to_conv
1834: WHERE nvl (conv.sec_conversion_rate, 999) < 0 -- null is fine
1835: AND mp.organization_id = to_conv.organization_id
1836: AND conv.approval_date (+) = to_conv.approval_date
1837: AND conv.organization_id (+) = to_conv.organization_id)
1913: fii_currency.get_global_rate_secondary (
1914: curr_codes.currency_code,
1915: to_conv.approval_date)))
1916: FROM
1917: (SELECT /*+ parallel (opi_dbi_inv_cca_stg) */
1918: DISTINCT organization_id, approval_date
1919: FROM opi_dbi_inv_cca_stg) to_conv,
1920: (SELECT /*+ leading (hoi) full (hoi) use_hash (gsob)
1921: parallel (hoi) parallel (gsob)*/
1915: to_conv.approval_date)))
1916: FROM
1917: (SELECT /*+ parallel (opi_dbi_inv_cca_stg) */
1918: DISTINCT organization_id, approval_date
1919: FROM opi_dbi_inv_cca_stg) to_conv,
1920: (SELECT /*+ leading (hoi) full (hoi) use_hash (gsob)
1921: parallel (hoi) parallel (gsob)*/
1922: DISTINCT hoi.organization_id, gsob.currency_code
1923: FROM hr_organization_information hoi,
2188: s_request_id,
2189: s_program_application_id,
2190: s_program_id,
2191: sysdate
2192: FROM opi_dbi_inv_cca_stg stg,
2193: opi_dbi_inv_cca_conv conv
2194: WHERE stg.organization_id = conv.organization_id
2195: AND stg.approval_date = conv.approval_date;
2196:
2331: -- Truncate the staging table, commit the data to the fact table
2332: -- and update the bounds related to cycle counts in one database
2333: -- transaction for consistency.
2334: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
2335: 'opi_dbi_inv_cca_stg');
2336:
2337:
2338: -- return successfully
2339: retcode := C_SUCCESS;
2393: -- Truncate the staging table because extraction failed midway.
2394: -- In the next run, all previously extracted and
2395: -- adjustment entry data will be re-extracted.
2396: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
2397: 'opi_dbi_inv_cca_stg');
2398:
2399: -- report the error
2400: retcode := C_ERROR;
2401: errbuf := 'Inventory Cycle Count Accuracy ETL incremental load terminated with errors. Please check the concurrent program log file for errors.';
2412: -- Truncate the staging table because extraction failed midway.
2413: -- In the next run, all previously extracted exact match and
2414: -- adjustment entry data will be re-extracted.
2415: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
2416: 'opi_dbi_inv_cca_stg');
2417:
2418: -- report the error
2419: retcode := C_ERROR;
2420: errbuf := 'Inventory Cycle Count Accuracy ETL incremental load terminated with errors. Please check the concurrent program log file for errors.';
2585: -- the fact is complete and are being committed using the truncate
2586: -- on the staging table, just ensure that the staging table is
2587: -- truncated.
2588: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
2589: 'opi_dbi_inv_cca_stg');
2590:
2591:
2592: -- Truncate the following tables (in case of exceptions, nothing
2593: -- special to do here because it is a database error):
2807: -- to MSI - on org and item. This should allow use of the U1 index.
2808: --
2809: -- to SUBS - on org and subinventory code
2810: INSERT
2811: INTO opi_dbi_inv_cca_stg (
2812: organization_id,
2813: inventory_item_id,
2814: cycle_count_header_id,
2815: abc_class_id,
3099: -- to SUBS - on org and subinventory code
3100:
3101:
3102: INSERT
3103: INTO opi_dbi_inv_cca_stg (
3104: organization_id,
3105: inventory_item_id,
3106: cycle_count_header_id,
3107: abc_class_id,
3368: -- .
3369:
3370:
3371: INSERT
3372: INTO opi_dbi_inv_cca_stg (
3373: organization_id,
3374: inventory_item_id,
3375: cycle_count_header_id,
3376: abc_class_id,
3466: The conversion rate work table was truncated during
3467: the initialization phase.
3468:
3469: Get the currency conversion rates based on the data in
3470: OPI_DBI_INV_CCA_STG using the fii_currency.get_global_rate_primary
3471: API for the primary global currency and
3472: fii_currency.get_global_rate_secondary for the secondary global currency.
3473: The primary currency API:
3474: 1. finds the conversion rate if one exists.
3559: FROM opi_dbi_inv_cca_conv conv,
3560: mtl_parameters mp,
3561: (SELECT
3562: DISTINCT organization_id, approval_date
3563: FROM opi_dbi_inv_cca_stg) to_conv
3564: WHERE nvl (conv.conversion_rate, -999) < 0 -- null is not fine
3565: AND mp.organization_id = to_conv.organization_id
3566: AND conv.approval_date (+) = to_conv.approval_date
3567: AND conv.organization_id (+) = to_conv.organization_id
3580: FROM opi_dbi_inv_cca_conv conv,
3581: mtl_parameters mp,
3582: (SELECT
3583: DISTINCT organization_id, approval_date
3584: FROM opi_dbi_inv_cca_stg) to_conv
3585: WHERE nvl (conv.sec_conversion_rate, 999) < 0 -- null is fine
3586: AND mp.organization_id = to_conv.organization_id
3587: AND conv.approval_date (+) = to_conv.approval_date
3588: AND conv.organization_id (+) = to_conv.organization_id)
3665: to_conv.approval_date)))
3666: FROM
3667: (SELECT
3668: DISTINCT organization_id, approval_date
3669: FROM opi_dbi_inv_cca_stg) to_conv,
3670: (SELECT
3671: DISTINCT hoi.organization_id, gsob.currency_code
3672: FROM hr_organization_information hoi,
3673: gl_sets_of_books gsob
3919: s_request_id request_id,
3920: s_program_application_id program_application_id,
3921: s_program_id program_id,
3922: sysdate program_update_date
3923: FROM opi_dbi_inv_cca_stg stg,
3924: opi_dbi_inv_cca_conv conv
3925: WHERE stg.organization_id = conv.organization_id
3926: AND stg.approval_date = conv.approval_date) new
3927: ON (