[Home] [Help]
243: Job Master
244:
245: The computation can be broken down into the following stages:
246:
247: 1. Truncate the log and audit tables, OPI_DBI_RUN_LOG_CURR and
248: OPI_DBI_RUN_LOG_AUDIT respectively.
249: 2. Populate the initial data for all the different ETLs in the
250: current log table OPI_DBI_RUN_LOG_CURR.
251: 3. Compute the end bounds for all the rows in the current log table,
246:
247: 1. Truncate the log and audit tables, OPI_DBI_RUN_LOG_CURR and
248: OPI_DBI_RUN_LOG_AUDIT respectively.
249: 2. Populate the initial data for all the different ETLs in the
250: current log table OPI_DBI_RUN_LOG_CURR.
251: 3. Compute the end bounds for all the rows in the current log table,
252: OPI_DBI_RUN_LOG_CURR.
253:
254: Data is committed when all steps are successful.
248: OPI_DBI_RUN_LOG_AUDIT respectively.
249: 2. Populate the initial data for all the different ETLs in the
250: current log table OPI_DBI_RUN_LOG_CURR.
251: 3. Compute the end bounds for all the rows in the current log table,
252: OPI_DBI_RUN_LOG_CURR.
253:
254: Data is committed when all steps are successful.
255:
256: Parameters:
284: 'OPI_DBI_RUN_LOG_AUDIT');
285:
286: l_stmt_id := 20;
287: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || p_opi_schema || '.' ||
288: 'OPI_DBI_RUN_LOG_CURR');
289:
290: -- Create all the data for all the different MAnufacuturing Management
291: -- ETLs in OPI_DBI_RUN_LOG_CURR
292: l_stmt_id := 30;
287: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || p_opi_schema || '.' ||
288: 'OPI_DBI_RUN_LOG_CURR');
289:
290: -- Create all the data for all the different MAnufacuturing Management
291: -- ETLs in OPI_DBI_RUN_LOG_CURR
292: l_stmt_id := 30;
293: seed_run_log_initial (errbuf, retcode, p_global_start_date);
294:
295: -- success so far, then commit everything in one shot
321:
322: /* seed_run_log_initial
323:
324: The following ETL bounds need to be computed and seeded in the
325: OPI_DBI_RUN_LOG_CURR table:
326:
327: 1. Job Transaction ETL (WIP Completions, Actual Usage and Scrap)
328: OPI sourced rows --
329: One row per discrete org with the start_txn_id as the first
475: -- transactions, because the last transaction itself could have been
476: -- backdated.
477:
478: l_stmt_id := 50;
479: INSERT /*+ append parallel (opi_dbi_run_log_curr) */
480: INTO opi_dbi_run_log_curr (
481: organization_id,
482: source,
483: last_collection_date,
476: -- backdated.
477:
478: l_stmt_id := 50;
479: INSERT /*+ append parallel (opi_dbi_run_log_curr) */
480: INTO opi_dbi_run_log_curr (
481: organization_id,
482: source,
483: last_collection_date,
484: start_txn_id,
569: -- date as the date time snapshot taken above in
570: -- the local variable l_to_bound_date.
571: -- Organization id would be null for such OPM rows.
572: l_stmt_id := 60;
573: INSERT INTO opi_dbi_run_log_curr (
574: organization_id,
575: source,
576: last_collection_date,
577: start_txn_id,
638: -- for OPI with start_txn_id as the first transaction past global
639: -- start date in WT. There cannot be any uncosted resource transactions.
640:
641: l_stmt_id := 110;
642: INSERT INTO opi_dbi_run_log_curr (
643: organization_id,
644: source,
645: last_collection_date,
646: start_txn_id,
688: -- and to bound date for OPI also.
689:
690: l_stmt_id := 120;
691:
692: INSERT INTO opi_dbi_run_log_curr (
693: organization_id,
694: source,
695: last_collection_date,
696: start_txn_id,
804: SELECT 1
805: INTO l_exists
806: FROM dual
807: WHERE (EXISTS (SELECT source
808: FROM opi_dbi_run_log_curr
809: WHERE rownum = 1));
810:
811: EXCEPTION
812: WHEN NO_DATA_FOUND THEN
903: SELECT 1
904: INTO l_exists
905: FROM dual
906: WHERE (EXISTS (SELECT start_txn_id
907: FROM opi_dbi_run_log_curr
908: WHERE start_txn_id IS NULL
909: AND source = p_source
910: AND etl_id = p_etl_id));
911:
922: SELECT 1
923: INTO l_exists
924: FROM dual
925: WHERE (EXISTS (SELECT next_start_txn_id
926: FROM opi_dbi_run_log_curr
927: WHERE next_start_txn_id IS NULL
928: AND source = p_source
929: AND etl_id = p_etl_id));
930:
999: l_stmt_id := 10;
1000: BEGIN
1001: SELECT 1
1002: INTO l_exists
1003: FROM opi_dbi_run_log_curr
1004: WHERE last_collection_date IS NOT NULL
1005: AND source = p_source
1006: AND etl_id = p_etl_id
1007: AND rownum = 1;
1020: SELECT 1
1021: INTO l_exists
1022: FROM dual
1023: WHERE (EXISTS (SELECT from_bound_date
1024: FROM opi_dbi_run_log_curr
1025: WHERE from_bound_date IS NULL
1026: AND source = p_source
1027: AND etl_id = p_etl_id));
1028:
1039: SELECT 1
1040: INTO l_exists
1041: FROM dual
1042: WHERE (EXISTS (SELECT to_bound_date
1043: FROM opi_dbi_run_log_curr
1044: WHERE to_bound_date IS NULL
1045: AND source = p_source
1046: AND etl_id = p_etl_id));
1047:
1107: -- the initial load cannot be run.
1108: l_stmt_id := 10;
1109: SELECT sum (1)
1110: INTO l_num_non_init_rows
1111: FROM opi_dbi_run_log_curr
1112: WHERE source = p_source
1113: AND etl_id = p_etl_id
1114: AND last_collection_date IS NOT NULL;
1115:
1218: l_stmt_id := 10;
1219: BEGIN
1220: SELECT s_WARNING
1221: INTO l_warning
1222: FROM OPI_DBI_RUN_LOG_CURR
1223: WHERE stop_reason_code = STOP_UNCOSTED
1224: AND rownum = 1;
1225: EXCEPTION
1226: WHEN NO_DATA_FOUND THEN
1250: l_stmt_id NUMBER := 0;
1251:
1252: -- Cursor for all the org bounds
1253: CURSOR opi_org_bounds_csr IS
1254: SELECT /*+ index(log, OPI_DBI_RUN_LOG_CURR_N1) use_nl(log mp)*/
1255: mp.organization_code,
1256: log.next_start_txn_id,
1257: decode (log.stop_reason_code,
1258: STOP_ALL_COSTED, 'All Costed',
1258: STOP_ALL_COSTED, 'All Costed',
1259: STOP_UNCOSTED, 'Uncosted',
1260: 'Data Issue?') stop_reason,
1261: nvl (mmt.transaction_date, sysdate) data_until
1262: FROM opi_dbi_run_log_curr log,
1263: mtl_parameters mp,
1264: mtl_material_transactions mmt
1265: WHERE source = OPI_SOURCE
1266: AND etl_id = JOB_TXN_ETL -- any ETL is good enough