[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
perform only inserts for the initial load.
All updates removed on recommendation
of performance team.
*/
PROCEDURE compute_initial_etl_bounds (errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_global_start_date IN DATE,
p_opi_schema IN VARCHAR2)
IS
l_proc_name VARCHAR2 (60) := 'compute_initial_etl_bounds';
SELECT nvl (max (transaction_id), -1) + 1
INTO l_max_mmt_plus_one
FROM mtl_material_transactions;
SELECT sysdate
INTO l_to_bound_date
FROM DUAL;
SELECT /*+ parallel(mtl_material_transactions) */
nvl (min (transaction_id), l_max_mmt_plus_one)
INTO l_mmt_start_txn_id
FROM mtl_material_transactions
WHERE transaction_date >= p_global_start_date;
INSERT /*+ append parallel (opi_dbi_run_log_curr) */
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,
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 /*+ parallel (bounds) parallel (etls) */
bounds.organization_id,
OPI_SOURCE, -- OPI rows
NULL,
bounds.start_txn_id,
bounds.next_start_txn_id,
NULL,
NULL,
JOB_TXN_ETL,
bounds.stop_reason_code,
decode (bounds.stop_reason_code,
STOP_UNCOSTED, bounds.last_transaction_date,
sysdate),
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 /*+ parallel (mmt_bounds) parallel (mmt) */
mmt_bounds.organization_id,
mmt_bounds.start_txn_id,
mmt_bounds.next_start_txn_id,
trunc (max (mmt.transaction_date))
last_transaction_date,
mmt_bounds.stop_reason_code
FROM
(SELECT /* parallel (uncosted) parallel (orgs) */
orgs.organization_id,
l_mmt_start_txn_id start_txn_id,
nvl (uncosted.uncosted_id, l_max_mmt_plus_one)
next_start_txn_id,
decode (uncosted.uncosted_id,
NULL, STOP_ALL_COSTED,
STOP_UNCOSTED) stop_reason_code
FROM (SELECT /*+ PARALLEL (mtl_material_transactions) */
min (transaction_id) uncosted_id,
organization_id
FROM mtl_material_transactions
WHERE costed_flag IN ('N', 'E')
AND transaction_id > l_mmt_start_txn_id
GROUP BY organization_id) uncosted,
(SELECT /*+ parallel (mtl_parameters) */
organization_id
FROM mtl_parameters
WHERE process_enabled_flag <> 'Y') orgs
WHERE orgs.organization_id = uncosted.organization_id (+))
mmt_bounds,
mtl_material_transactions mmt
WHERE mmt_bounds.organization_id = mmt.organization_id (+)
AND (mmt.transaction_id BETWEEN mmt_bounds.start_txn_id AND
mmt_bounds.next_start_txn_id
OR mmt.transaction_id IS NULL)
GROUP BY
mmt_bounds.organization_id,
mmt_bounds.start_txn_id,
mmt_bounds.next_start_txn_id,
mmt_bounds.stop_reason_code) bounds;
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,
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
NULL,
OPM_SOURCE, -- OPM rows
NULL,
NULL,
NULL,
p_global_start_date,
l_to_bound_date,
etls.etl_id,
NULL,
sysdate,
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
UNION ALL
SELECT ACTUAL_RES_ETL FROM dual
UNION ALL
SELECT RESOURCE_VAR_ETL FROM dual
UNION ALL
SELECT JOB_MASTER_ETL FROM dual) etls;
SELECT nvl (max (transaction_id), -1) + 1
INTO l_max_wt_plus_one
FROM wip_transactions;
SELECT /*+ index_ffs(wip_transactions) parallel_index(wip_transactions) */
nvl (min (transaction_id), l_max_wt_plus_one)
INTO l_wt_start_txn_id
FROM wip_transactions
WHERE transaction_date >= p_global_start_date;
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,
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
NULL,
src.source_type,
NULL,
l_wt_start_txn_id,
l_max_wt_plus_one,
NULL,
NULL,
ACTUAL_RES_ETL,
NULL,
sysdate,
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 OPI_SOURCE source_type FROM dual
) src;
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,
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
NULL,
OPI_SOURCE, -- OPI rows
NULL,
NULL,
NULL,
p_global_start_date,
l_to_bound_date,
etls.etl_id,
NULL,
sysdate,
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 RESOURCE_VAR_ETL etl_id FROM dual
UNION ALL
SELECT JOB_MASTER_ETL FROM dual) etls;
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 dual
WHERE (EXISTS (SELECT next_start_txn_id
FROM opi_dbi_run_log_curr
WHERE next_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 last_collection_date IS NOT 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_init_rows
FROM opi_dbi_run_log_curr
WHERE source = p_source
AND etl_id = p_etl_id
AND last_collection_date IS NOT NULL;
SELECT s_WARNING
INTO l_warning
FROM OPI_DBI_RUN_LOG_CURR
WHERE stop_reason_code = STOP_UNCOSTED
AND rownum = 1;
SELECT /*+ index(log, OPI_DBI_RUN_LOG_CURR_N1) use_nl(log mp)*/
mp.organization_code,
log.next_start_txn_id,
decode (log.stop_reason_code,
STOP_ALL_COSTED, 'All Costed',
STOP_UNCOSTED, 'Uncosted',
'Data Issue?') stop_reason,
nvl (mmt.transaction_date, sysdate) data_until
FROM opi_dbi_run_log_curr log,
mtl_parameters mp,
mtl_material_transactions mmt
WHERE source = OPI_SOURCE
AND etl_id = JOB_TXN_ETL -- any ETL is good enough
AND log.next_start_txn_id = mmt.transaction_id (+)
AND log.organization_id = mp.organization_id;