[Home] [Help]
176: l_stmt_id := 5;
177: BEGIN
178: SELECT 1
179: INTO l_run_log_size
180: FROM opi_dbi_run_log_curr
181: WHERE rownum = 1;
182: EXCEPTION
183: WHEN NO_DATA_FOUND THEN
184: RAISE run_common_mod_init;
322: Material Usage Variance
323:
324:
325: Compute the end bounds for all the rows corresponding to these ETLs
326: in the current log table, OPI_DBI_RUN_LOG_CURR
327:
328: Data is committed at the end of this procedure but not in any
329: of it's helper routines.
330:
455:
456: /* Truncate temp table */
457: l_stmt_id := 5;
458: EXECUTE IMMEDIATE ('TRUNCATE TABLE ' ||
459: s_opi_schema || '.opi_dbi_run_log_curr_tmp');
460:
461: l_stmt_id := 10;
462: SELECT nvl (max (transaction_id), -1) + 1
463: INTO l_max_mmt_plus_one
476: FROM DUAL;
477:
478: -- It is possible that a new discrete org was defined in
479: -- MTL_PARAMETERS since the last incremental run. This org must
480: -- get a record in current log table, OPI_DBI_RUN_LOG_CURR so that
481: -- it can be picked up in subsequent extractions. This need only be
482: -- done for OPI orgs.
483:
484: -- The new org can start with least of the starting id's because
488: -- incremental load.
489: l_stmt_id := 48;
490: SELECT min (start_txn_id)
491: INTO l_min_start_id_opi_orgs
492: FROM opi_dbi_run_log_curr
493: WHERE source = OPI_SOURCE
494: AND etl_id = JOB_TXN_ETL;
495:
496: -- Get the global start date which will be used as the last collection
503: RAISE global_start_date_null;
504: END IF;
505:
506:
507: -- Note that all distinct orgs in OPI_DBI_RUN_LOG_CURR can be queried
508: -- by looking at all the orgs for Material ETL with OPI source
509: -- (we look at the JOBH_TXN_ETL below). This is because
510: -- we have exactly one row per discrete org for the Job Txn ETL
511: -- Note that we are making the last_collection_date for new orgs
511: -- Note that we are making the last_collection_date for new orgs
512: -- to be the global start date so that the run_incr_bounds API
513: -- performs correctly.
514: l_stmt_id := 50;
515: INSERT INTO opi_dbi_run_log_curr (
516: organization_id,
517: source,
518: last_collection_date,
519: start_txn_id,
553: FROM mtl_parameters
554: WHERE process_enabled_flag <> 'Y' -- not OPM org
555: MINUS
556: SELECT organization_id -- all distinct orgs
557: FROM opi_dbi_run_log_curr
558: WHERE etl_id = JOB_TXN_ETL
559: AND source = OPI_SOURCE) new_orgs;
560:
561: -- For Job Transactions ETL the OPI sourced
571: -- update it for the OPI sourced MMT ETLs
572:
573: l_stmt_id := 60;
574: INSERT /*+ append */
575: INTO opi_dbi_run_log_curr_tmp
576: (ORGANIZATION_ID,
577: ETL_ID, SOURCE,
578: NEXT_START_TXN_ID,
579: STOP_REASON_CODE,
597: uncosted.etl_id,
598: uncosted.source,
599: uncosted.uncosted_id next_start_txn_id,
600: max (mmt1.transaction_date) last_transaction_date
601: FROM (SELECT /*+ index(log, OPI_DBI_RUN_LOG_CURR_N1)
602: leading(log) use_nl(log mmt) */
603: min (mmt.transaction_id) uncosted_id,
604: log.organization_id,
605: log.etl_id,
609: (SELECT organization_id,
610: etl_id,
611: source,
612: start_txn_id
613: FROM opi_dbi_run_log_curr
614: WHERE source = OPI_SOURCE
615: AND etl_id = JOB_TXN_ETL) log
616: WHERE mmt.costed_flag IN ('N', 'E')
617: AND mmt.transaction_id >= log.start_txn_id
630: uncosted.etl_id,
631: uncosted.source,
632: uncosted.uncosted_id) mmt_bounds,
633: (SELECT organization_id, etl_id, source, start_txn_id
634: FROM opi_dbi_run_log_curr
635: WHERE source = OPI_SOURCE
636: AND etl_id = JOB_TXN_ETL) curr_log
637: WHERE curr_log.organization_id = mmt_bounds.organization_id (+)
638: AND curr_log.etl_id = mmt_bounds.etl_id (+)
643: commit;
644:
645: l_stmt_id := 65;
646:
647: UPDATE /*+ index(opi_curr_log, opi_dbi_run_log_curr_n1) */
648: opi_dbi_run_log_curr opi_curr_log
649: SET last_update_date = sysdate,
650: (next_start_txn_id, stop_reason_code, last_transaction_date) =
651: (SELECT next_start_txn_id,
644:
645: l_stmt_id := 65;
646:
647: UPDATE /*+ index(opi_curr_log, opi_dbi_run_log_curr_n1) */
648: opi_dbi_run_log_curr opi_curr_log
649: SET last_update_date = sysdate,
650: (next_start_txn_id, stop_reason_code, last_transaction_date) =
651: (SELECT next_start_txn_id,
652: stop_reason_code,
650: (next_start_txn_id, stop_reason_code, last_transaction_date) =
651: (SELECT next_start_txn_id,
652: stop_reason_code,
653: last_transaction_date
654: FROM opi_dbi_run_log_curr_tmp bounds
655: WHERE bounds.organization_id = opi_curr_log.organization_id
656: AND bounds.etl_id = opi_curr_log.etl_id
657: AND bounds.source = opi_curr_log.source)
658: WHERE opi_curr_log.source = OPI_SOURCE
661:
662: -- For all OPM ETL's, we would have to set the to_bound_date as the sysdate.
663: -- By default, everything is costed
664: l_stmt_id := 70;
665: UPDATE opi_dbi_run_log_curr log
666: SET last_update_date = sysdate,
667: last_transaction_date = sysdate,
668: to_bound_date = l_to_bound_date
669: WHERE log.source = OPM_SOURCE;
671: -- For Actual Resource Usage the next_start_txn_id for:
672: -- 1. OPI sourced row needs max + 1 from WT.
673: -- By default, everything is costed.
674: l_stmt_id := 80;
675: UPDATE opi_dbi_run_log_curr log
676: SET last_update_date = sysdate,
677: last_transaction_date = sysdate,
678: next_start_txn_id = l_max_wt_plus_one
679: WHERE log.source = OPI_SOURCE
680: AND log.etl_id = ACTUAL_RES_ETL;
681:
682: -- For OPI Job Master and Resource Availibility ETL's, we would have to set the to_bound_date as the sysdate.
683: l_stmt_id := 90;
684: UPDATE opi_dbi_run_log_curr log
685: SET last_update_date = sysdate,
686: last_transaction_date = sysdate,
687: to_bound_date = l_to_bound_date
688: WHERE log.source = OPI_SOURCE
733:
734: /* etl_report_success
735:
736: Interface API for all ETLs that have collected data for the bounds
737: stored in the log table OPI_DBI_RUN_LOG_CURR.
738:
739: This API is an indication from the ETL that its current rows in the
740: OPI_DBI_RUN_LOG_CURR should be copied into the history table,
741: OPI_DBI_RUN_LOG_AUDIT and then updated so that they can be populated
736: Interface API for all ETLs that have collected data for the bounds
737: stored in the log table OPI_DBI_RUN_LOG_CURR.
738:
739: This API is an indication from the ETL that its current rows in the
740: OPI_DBI_RUN_LOG_CURR should be copied into the history table,
741: OPI_DBI_RUN_LOG_AUDIT and then updated so that they can be populated
742: with new bounds when the common module runs again.
743:
744: There are 4 different ETLs that can call the API:
842:
843: Log success for an ETL that uses transaction_id's as the high watermark.
844:
845: The behaviour is simple. Copy out all rows in the current log table,
846: OPI_DBI_RUN_LOG_CURR with the same ETL_id-source values,
847: and copy them to the audit table with the
848: last_collection_date set to the completion date passed in.
849:
850: Then for all existing rows in OPI_DBI_RUN_LOG_CURR with the same ETL_id-
846: OPI_DBI_RUN_LOG_CURR with the same ETL_id-source values,
847: and copy them to the audit table with the
848: last_collection_date set to the completion date passed in.
849:
850: Then for all existing rows in OPI_DBI_RUN_LOG_CURR with the same ETL_id-
851: source pairs as passed as arguments:
852: Set the start_txn_id to the next_start_txn_id because for the next
853: run we need to start at where we stopped this time.
854: Set the next_start_txn_id to NULL since it will have to be recomputed.
925: s_program_id,
926: s_program_login_id,
927: s_program_application_id,
928: s_request_id
929: FROM opi_dbi_run_log_curr
930: WHERE etl_id = p_etl_id
931: AND source = p_source;
932:
933:
932:
933:
934: -- update the start_txn_id to the next_start_txn_id and
935: -- the last_collection_date to the completion date in the
936: -- OPI_DBI_RUN_LOG_CURR.
937: -- update the next_start_txn_id
938: -- since they must be recomputed when the common module runs
939: -- again.
940: -- Do not change the stop reason code since PTP will need it later.
940: -- Do not change the stop reason code since PTP will need it later.
941: -- Performance change (09/09/2003): Merged previous two updates into one.
942:
943: l_stmt_id := 20;
944: UPDATE opi_dbi_run_log_curr log
945: SET last_collection_date = p_completion_date,
946: start_txn_id = next_start_txn_id,
947: next_start_txn_id = NULL
948: WHERE log.source = p_source
976:
977: Log success for an ETL that uses from and to bound dates as the high watermark.
978:
979: The behaviour is simple. Copy out all rows in the current log table,
980: OPI_DBI_RUN_LOG_CURR with the same ETL_id-source values,
981: and copy them to the audit table with the
982: last_collection_date set to the completion date passed in.
983:
984: Then for all existing rows in OPI_DBI_RUN_LOG_CURR with the same ETL_id-
980: OPI_DBI_RUN_LOG_CURR with the same ETL_id-source values,
981: and copy them to the audit table with the
982: last_collection_date set to the completion date passed in.
983:
984: Then for all existing rows in OPI_DBI_RUN_LOG_CURR with the same ETL_id-
985: source pairs as passed as arguments:
986: Set the last_collection_date to the completion date passed in.
987:
988: DO NOT COMMIT ANYTHING HERE. THAT WILL BE THE RESPONSIBILITY OF THE
1057: s_program_id,
1058: s_program_login_id,
1059: s_program_application_id,
1060: s_request_id
1061: FROM opi_dbi_run_log_curr
1062: WHERE etl_id = p_etl_id
1063: AND source = p_source;
1064:
1065: -- update the last_collection_date to the completion date in the
1062: WHERE etl_id = p_etl_id
1063: AND source = p_source;
1064:
1065: -- update the last_collection_date to the completion date in the
1066: -- OPI_DBI_RUN_LOG_CURR.
1067: -- Also set the stop_reason_code to NULL for consistency.
1068: -- Also set the from_bound_date of the to_bound_date of this run
1069: -- and set the to_bound_date to NULL as it would be computed in the
1070: -- next run.
1068: -- Also set the from_bound_date of the to_bound_date of this run
1069: -- and set the to_bound_date to NULL as it would be computed in the
1070: -- next run.
1071: l_stmt_id := 20;
1072: UPDATE opi_dbi_run_log_curr log
1073: SET last_collection_date = p_completion_date,
1074: from_bound_date = to_bound_date,
1075: to_bound_date = NULL,
1076: stop_reason_code = NULL
1141: SELECT 1
1142: INTO l_exists
1143: FROM dual
1144: WHERE (EXISTS (SELECT source
1145: FROM opi_dbi_run_log_curr
1146: WHERE rownum = 1));
1147:
1148: EXCEPTION
1149: WHEN NO_DATA_FOUND THEN
1235: SELECT 1
1236: INTO l_exists
1237: FROM dual
1238: WHERE (EXISTS (SELECT start_txn_id
1239: FROM opi_dbi_run_log_curr
1240: WHERE start_txn_id IS NULL
1241: AND source = p_source
1242: AND etl_id = p_etl_id));
1243:
1252: l_stmt_id := 20;
1253: BEGIN
1254: SELECT 1
1255: INTO l_exists
1256: FROM opi_dbi_run_log_curr
1257: WHERE next_start_txn_id IS NULL
1258: AND source = p_source
1259: AND etl_id = p_etl_id
1260: AND rownum = 1;
1331: l_stmt_id := 10;
1332: BEGIN
1333: SELECT 1
1334: INTO l_exists
1335: FROM opi_dbi_run_log_curr
1336: WHERE last_collection_date IS NULL
1337: AND source = p_source
1338: AND etl_id = p_etl_id
1339: AND rownum = 1;
1351: SELECT 1
1352: INTO l_exists
1353: FROM dual
1354: WHERE (EXISTS (SELECT from_bound_date
1355: FROM opi_dbi_run_log_curr
1356: WHERE from_bound_date IS NULL
1357: AND source = p_source
1358: AND etl_id = p_etl_id));
1359:
1370: SELECT 1
1371: INTO l_exists
1372: FROM dual
1373: WHERE (EXISTS (SELECT to_bound_date
1374: FROM opi_dbi_run_log_curr
1375: WHERE to_bound_date IS NULL
1376: AND source = p_source
1377: AND etl_id = p_etl_id));
1378:
1438: -- then the incremental load cannot be run.
1439: l_stmt_id := 10;
1440: SELECT sum (1)
1441: INTO l_num_non_incr_rows
1442: FROM opi_dbi_run_log_curr
1443: WHERE source = p_source
1444: AND etl_id = p_etl_id
1445: AND last_collection_date IS NULL;
1446: