[Home] [Help]
136: Wrapper routine for OPI + OPM wip completion data extraction for
137: initial load.
138:
139: To begin with, this routine truncates the staging table,
140: OPI_DBI_WIP_COMP_STG and the fact table OPI_DBI_WIP_COMP_F.
141:
142: Both OPI and OPM ETLs can run independently upto the staging table level,
143: even if the other fails. That way, errors found in extracting OPI and OPM
144: data in the initial run can be reported simultaneously.
218: -- bug 3863905- mv log is now dropped before initial load
219: -- we shouldnt be truncating mv log anymore
220: --
221: -- EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
222: -- 'MLOG$_OPI_DBI_WIP_COMP_F');
223:
224: l_stmt_id := 25;
225: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
226: 'OPI_DBI_WIP_COMP_F PURGE MATERIALIZED VIEW LOG');
222: -- 'MLOG$_OPI_DBI_WIP_COMP_F');
223:
224: l_stmt_id := 25;
225: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
226: 'OPI_DBI_WIP_COMP_F PURGE MATERIALIZED VIEW LOG');
227:
228: l_stmt_id := 30;
229: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
230: 'OPI_DBI_WIP_COMP_STG');
702: -- Depending on how the staging table extraction SQL has been written
703: -- it might not be necessary to perform the group by operation here (at
704: -- least not for initial load) but it is being done for consistency.
705: l_stmt_id := 10;
706: INSERT /*+ append parallel(opi_dbi_wip_comp_f) */
707: INTO opi_dbi_wip_comp_f (
708: organization_id,
709: inventory_item_id,
710: transaction_date,
703: -- it might not be necessary to perform the group by operation here (at
704: -- least not for initial load) but it is being done for consistency.
705: l_stmt_id := 10;
706: INSERT /*+ append parallel(opi_dbi_wip_comp_f) */
707: INTO opi_dbi_wip_comp_f (
708: organization_id,
709: inventory_item_id,
710: transaction_date,
711: completion_quantity,