[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
INTO l_run_log_size
FROM opi_dbi_run_log_curr
WHERE rownum = 1;
'Manufacturing Page common module Initial load has not been run. Please run the initial load (Initial Load - Update Job Details Base Summary) first.');
'Manufacturing Page common module Initial load has not been run. Please run the initial load (Initial Load - Update Job Details Base Summary) first.';
SELECT nvl (max (transaction_id), -1) + 1
INTO l_max_mmt_plus_one
FROM mtl_material_transactions;
SELECT nvl (max (transaction_id), -1) + 1
INTO l_max_wt_plus_one
FROM wip_transactions;
SELECT sysdate
INTO l_to_bound_date
FROM DUAL;
SELECT min (start_txn_id)
INTO l_min_start_id_opi_orgs
FROM opi_dbi_run_log_curr
WHERE source = OPI_SOURCE
AND etl_id = JOB_TXN_ETL;
INSERT INTO opi_dbi_run_log_curr (
organization_id,
source,
last_collection_date,
start_txn_id,
next_start_txn_id,
from_bound_date,
to_bound_date,
etl_id,
last_update_date,
creation_date,
last_updated_by,
created_by,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id)
SELECT new_orgs.organization_id,
OPI_SOURCE,
l_global_start_date, -- never collected yet
l_min_start_id_opi_orgs, -- least collected transaction id
NULL, -- no next_start_txn_id yet
NULL,
NULL,
etls.etl_id, -- All material ETLs
sysdate,
sysdate,
s_user_id,
s_user_id,
s_login_id,
s_program_id,
s_program_login_id,
s_program_application_id,
s_request_id
FROM (SELECT JOB_TXN_ETL etl_id FROM dual
) etls,
(SELECT organization_id
FROM mtl_parameters
WHERE process_enabled_flag <> 'Y' -- not OPM org
MINUS
SELECT organization_id -- all distinct orgs
FROM opi_dbi_run_log_curr
WHERE etl_id = JOB_TXN_ETL
AND source = OPI_SOURCE) new_orgs;
INSERT /*+ append */
INTO opi_dbi_run_log_curr_tmp
(ORGANIZATION_ID,
ETL_ID, SOURCE,
NEXT_START_TXN_ID,
STOP_REASON_CODE,
LAST_TRANSACTION_DATE)
SELECT /*+ use_nl(curr mmt_bounds) */
curr_log.organization_id,
curr_log.etl_id,
curr_log.source,
nvl (mmt_bounds.next_start_txn_id,
l_max_mmt_plus_one) next_start_txn_id,
decode (mmt_bounds.next_start_txn_id,
NULL, STOP_ALL_COSTED,
STOP_UNCOSTED) stop_reason_code,
decode (mmt_bounds.next_start_txn_id,
NULL, sysdate,
mmt_bounds.last_transaction_date) last_transaction_date
FROM
(SELECT /*+ use_nl(uncosted mmt1)
index(mmt1, MTL_MATERIAL_TRANSACTIONS_U1) */
uncosted.organization_id,
uncosted.etl_id,
uncosted.source,
uncosted.uncosted_id next_start_txn_id,
max (mmt1.transaction_date) last_transaction_date
FROM (SELECT /*+ index(log, OPI_DBI_RUN_LOG_CURR_N1)
leading(log) use_nl(log mmt) */
min (mmt.transaction_id) uncosted_id,
log.organization_id,
log.etl_id,
log.source,
log.start_txn_id
FROM mtl_material_transactions mmt,
(SELECT organization_id,
etl_id,
source,
start_txn_id
FROM opi_dbi_run_log_curr
WHERE source = OPI_SOURCE
AND etl_id = JOB_TXN_ETL) log
WHERE mmt.costed_flag IN ('N', 'E')
AND mmt.transaction_id >= log.start_txn_id
AND mmt.organization_id = log.organization_id
GROUP BY
log.organization_id,
log.etl_id,
log.source,
log.start_txn_id) uncosted,
mtl_material_transactions mmt1
WHERE mmt1.organization_id+0 = uncosted.organization_id
AND mmt1.transaction_id BETWEEN uncosted.start_txn_id
AND uncosted.uncosted_id
GROUP BY
uncosted.organization_id,
uncosted.etl_id,
uncosted.source,
uncosted.uncosted_id) mmt_bounds,
(SELECT organization_id, etl_id, source, start_txn_id
FROM opi_dbi_run_log_curr
WHERE source = OPI_SOURCE
AND etl_id = JOB_TXN_ETL) curr_log
WHERE curr_log.organization_id = mmt_bounds.organization_id (+)
AND curr_log.etl_id = mmt_bounds.etl_id (+)
AND curr_log.source = mmt_bounds.source (+);
UPDATE /*+ index(opi_curr_log, opi_dbi_run_log_curr_n1) */
opi_dbi_run_log_curr opi_curr_log
SET last_update_date = sysdate,
(next_start_txn_id, stop_reason_code, last_transaction_date) =
(SELECT next_start_txn_id,
stop_reason_code,
last_transaction_date
FROM opi_dbi_run_log_curr_tmp bounds
WHERE bounds.organization_id = opi_curr_log.organization_id
AND bounds.etl_id = opi_curr_log.etl_id
AND bounds.source = opi_curr_log.source)
WHERE opi_curr_log.source = OPI_SOURCE
AND opi_curr_log.etl_id = JOB_TXN_ETL;
UPDATE opi_dbi_run_log_curr log
SET last_update_date = sysdate,
last_transaction_date = sysdate,
to_bound_date = l_to_bound_date
WHERE log.source = OPM_SOURCE;
UPDATE opi_dbi_run_log_curr log
SET last_update_date = sysdate,
last_transaction_date = sysdate,
next_start_txn_id = l_max_wt_plus_one
WHERE log.source = OPI_SOURCE
AND log.etl_id = ACTUAL_RES_ETL;
UPDATE opi_dbi_run_log_curr log
SET last_update_date = sysdate,
last_transaction_date = sysdate,
to_bound_date = l_to_bound_date
WHERE log.source = OPI_SOURCE
AND log.etl_id IN (RESOURCE_VAR_ETL, JOB_MASTER_ETL);
OPI_DBI_RUN_LOG_AUDIT and then updated so that they can be populated
with new bounds when the common module runs again.
There are 4 different ETLs that can call the API:
Job Transactions ETL - WIP Completions, Scrap and Material Usage
Actual Resource Usage
Resource Variance
Job Master
Each of the ETLs can have an OPI or OPM source. The behaviour of
the API depends on both the invoking ETL and the source.
The general behaviour is that all rows for the ETL-source pair
get a last_collection_date of when this API is invoked and are copied
to the audit table. Then for the ETLs that use transaction_id's the
start and next_start txn_id's are updated.
In particular, the following types of behaviours can occur:
1. txn_id_success - For transaction_id based highwatermark ETLs:
Job Transactions ETL and OPI Source,
Actual Resource Usage and OPI Source.
Sets the last_collection_date to when this API
is called and copies all rows for the etl-source
pair to the audit table. The start and next_start
txn_id columns are updated.
2. collect_date_success - For last_collection_date based highwatermark
ETLs: Resource Usage and Job Master and all OPM ETL's.
Sets the last_collection_date to when this API
is called and copies all rows for the
etl-source pair to the audit table.
Also sets the from and to bound dates.
DO NOT COMMIT ANY DATA IN THIS API. IT IS THE RESPONSIBILITY OF THE
MODULE INVOKING THIS API TO COMMIT!!!!
Parameters:
p_etl_id - ETL id of the ETL invoking API.
p_source - data source of ETL (1 = OPI, 2 = OPM).
Return Value:
l_retval - true if the function returns with no errors.
false otherwise.
Date Author Action
04/21/03 Dinkar Gupta Wrote Function
07/01/05 Sandeep Beri Modified the IF conditions to the
transaction id set uo check call as
in R12, no OPM ETL would have txn id bounds.
*/
FUNCTION etl_report_success (p_etl_id IN NUMBER, p_source IN NUMBER)
RETURN BOOLEAN
IS
l_proc_name VARCHAR2 (60) := 'etl_report_success';
INSERT INTO opi_dbi_run_log_audit (
organization_id,
source,
last_collection_date,
start_txn_id,
next_start_txn_id,
from_bound_date,
to_bound_date,
etl_id,
stop_reason_code,
last_transaction_date,
last_update_date,
creation_date,
last_updated_by,
created_by,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id
)
SELECT
organization_id,
p_source,
p_completion_date,
start_txn_id,
next_start_txn_id,
from_bound_date,
to_bound_date,
p_etl_id,
stop_reason_code,
last_transaction_date,
sysdate,
sysdate,
s_user_id,
s_user_id,
s_login_id,
s_program_id,
s_program_login_id,
s_program_application_id,
s_request_id
FROM opi_dbi_run_log_curr
WHERE etl_id = p_etl_id
AND source = p_source;
UPDATE opi_dbi_run_log_curr log
SET last_collection_date = p_completion_date,
start_txn_id = next_start_txn_id,
next_start_txn_id = NULL
WHERE log.source = p_source
AND log.etl_id = p_etl_id;
INSERT INTO opi_dbi_run_log_audit (
organization_id,
source,
last_collection_date,
start_txn_id,
next_start_txn_id,
from_bound_date,
to_bound_date,
etl_id,
stop_reason_code,
last_update_date,
creation_date,
last_updated_by,
created_by,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id
)
SELECT
organization_id,
p_source,
p_completion_date,
start_txn_id,
next_start_txn_id,
from_bound_date,
to_bound_date,
p_etl_id,
stop_reason_code,
sysdate,
sysdate,
s_user_id,
s_user_id,
s_login_id,
s_program_id,
s_program_login_id,
s_program_application_id,
s_request_id
FROM opi_dbi_run_log_curr
WHERE etl_id = p_etl_id
AND source = p_source;
UPDATE opi_dbi_run_log_curr log
SET last_collection_date = p_completion_date,
from_bound_date = to_bound_date,
to_bound_date = NULL,
stop_reason_code = NULL
WHERE log.source = p_source
AND log.etl_id = p_etl_id;
SELECT 1
INTO l_exists
FROM dual
WHERE (EXISTS (SELECT source
FROM opi_dbi_run_log_curr
WHERE rownum = 1));
SELECT 1
INTO l_exists
FROM dual
WHERE (EXISTS (SELECT start_txn_id
FROM opi_dbi_run_log_curr
WHERE start_txn_id IS NULL
AND source = p_source
AND etl_id = p_etl_id));
SELECT 1
INTO l_exists
FROM opi_dbi_run_log_curr
WHERE next_start_txn_id IS NULL
AND source = p_source
AND etl_id = p_etl_id
AND rownum = 1;
SELECT 1
INTO l_exists
FROM opi_dbi_run_log_curr
WHERE last_collection_date IS NULL
AND source = p_source
AND etl_id = p_etl_id
AND rownum = 1;
SELECT 1
INTO l_exists
FROM dual
WHERE (EXISTS (SELECT from_bound_date
FROM opi_dbi_run_log_curr
WHERE from_bound_date IS NULL
AND source = p_source
AND etl_id = p_etl_id));
SELECT 1
INTO l_exists
FROM dual
WHERE (EXISTS (SELECT to_bound_date
FROM opi_dbi_run_log_curr
WHERE to_bound_date IS NULL
AND source = p_source
AND etl_id = p_etl_id));
SELECT sum (1)
INTO l_num_non_incr_rows
FROM opi_dbi_run_log_curr
WHERE source = p_source
AND etl_id = p_etl_id
AND last_collection_date IS NULL;