[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE FROM OPI_DBI_CONC_PROG_RUN_LOG
WHERE etl_type = p_etl_type;
l_debug_msg := 'Deleted '||to_char(sql%rowcount)||' rows from OPI_DBI_CONC_PROG_RUN_LOG' ;
SELECT 1,nvl(completion_status_code,'N') INTO l_count,l_completion_status
FROM OPI_DBI_CONC_PROG_RUN_LOG
WHERE etl_type = p_etl_type
AND load_type = p_load_type
AND rownum <= 1;
/* As completion_status_code is updated based on etl_type and load_type success
of one record implies success of all the records for that etl_type and load_type */
SELECT 1,nvl(completion_status_code,'N') into l_init_count,l_completion_status
FROM OPI_DBI_CONC_PROG_RUN_LOG
WHERE etl_type = p_etl_type
AND load_type = 'INIT'
AND rownum <= 1;
INSERT INTO OPI_DBI_CONC_PROG_RUN_LOG(
driving_table_code ,
etl_type ,
load_type ,
bound_type ,
bound_level_entity_code ,
bound_level_entity_id ,
from_bound_date ,
from_bound_id ,
to_bound_date ,
to_bound_id ,
completion_status_code ,
stop_reason_code ,
created_by ,
creation_date ,
last_run_date ,
last_update_date ,
last_updated_by ,
last_update_login ,
program_id ,
program_login_id ,
program_application_id ,
request_id
)
SELECT
driving_table_code ,
etl_type ,
'INCR' ,
bound_type ,
bound_level_entity_code ,
bound_level_entity_id ,
to_bound_date ,
to_bound_id ,
null ,
null ,
null ,
null ,
l_user_id ,
sysdate ,
sysdate ,
sysdate ,
l_user_id ,
l_login_id ,
l_program_id ,
l_program_login_id ,
l_program_application_id ,
l_request_id
FROM OPI_DBI_CONC_PROG_RUN_LOG
WHERE etl_type = p_etl_type
AND load_type = 'INIT';
l_debug_msg := 'Inserted '||to_char(sql%rowcount)||' rows into OPI_DBI_CONC_PROG_RUN_LOG' ;
/* update from_bound_id and from_bound_date as previous to_bound_id and
to_bound_date */
l_stmt_no :=40;
UPDATE OPI_DBI_CONC_PROG_RUN_LOG prlout
SET ( from_bound_id ,
from_bound_date ,
to_bound_date ,
to_bound_id ,
completion_status_code,
stop_reason_code ,
last_run_date ,
last_update_date ,
last_updated_by ,
last_update_login ,
program_id ,
program_login_id ,
program_application_id,
request_id
) =
(SELECT
to_bound_id ,
to_bound_date ,
null ,
null ,
null ,
null ,
sysdate ,
sysdate ,
l_user_id ,
l_login_id ,
l_program_id ,
l_program_login_id ,
l_program_application_id,
l_request_id
FROM OPI_DBI_CONC_PROG_RUN_LOG prlin
WHERE prlin.etl_type = prlout.etl_type
AND prlin.load_type = prlout.load_type
AND prlin.driving_table_code = prlout.driving_table_code
AND nvl(prlin.bound_level_entity_id,-1) = nvl(prlout.bound_level_entity_id,-1))
WHERE prlout.etl_type = p_etl_type
AND prlout.load_type = p_load_type;
l_debug_msg := 'Updated '||to_char(sql%rowcount)||' rows in OPI_DBI_CONC_PROG_RUN_LOG' ;
UPDATE OPI_DBI_CONC_PROG_RUN_LOG
SET to_bound_id = null,
to_bound_date = null,
completion_status_code = null,
stop_reason_code = null,
last_run_date = null , -- last run date should be null at this point
last_update_date = sysdate ,
last_updated_by = l_user_id ,
last_update_login = l_login_id,
program_id = l_program_id ,
program_login_id = l_program_login_id ,
program_application_id = l_program_application_id,
request_id = l_request_id
WHERE etl_type = p_etl_type
AND load_type = p_load_type;
l_debug_msg := 'Updated '||to_char(sql%rowcount)||' rows in OPI_DBI_CONC_PROG_RUN_LOG' ;
select BIS_COMMON_PARAMETERS.GET_GLOBAL_START_DATE into l_global_start_date from DUAL;
SELECT count(1) INTO l_inv_count FROM OPI_DBI_CONC_PROG_RUN_LOG
WHERE etl_type = 'INVENTORY'
AND load_type = 'INIT'
AND rownum <=1 ;
INSERT INTO OPI_DBI_CONC_PROG_RUN_LOG(
driving_table_code ,
etl_type ,
load_type ,
bound_type ,
bound_level_entity_code ,
bound_level_entity_id ,
from_bound_date ,
from_bound_id ,
to_bound_date ,
to_bound_id ,
completion_status_code ,
stop_reason_code ,
created_by ,
creation_date ,
last_run_date ,
last_update_date ,
last_updated_by ,
last_update_login ,
program_id ,
program_login_id ,
program_application_id ,
request_id)
SELECT
'MMT' ,
p_etl_type ,
'INIT' ,
bound_type ,
bound_level_entity_code ,
bound_level_entity_id , /* org frm INV INIT record */
null ,
from_bound_id , /* from_bound_id from INV record */
null ,
to_bound_id , /* to_bound_id from INV record */
null ,
stop_reason_code , /* stop_reason_code copied from INVENTORY record */
l_user_id ,
sysdate ,
sysdate ,
sysdate ,
l_user_id ,
l_login_id ,
l_program_id ,
l_program_login_id ,
l_program_application_id,
l_request_id
FROM OPI_DBI_CONC_PROG_RUN_LOG
WHERE etl_type = 'INVENTORY'
AND driving_table_code = 'MMT'
AND load_type = 'INIT';
l_debug_msg := 'Inserted '||to_char(sql%rowcount)||' rows into OPI_DBI_CONC_PROG_RUN_LOG' ;
INSERT INTO OPI_DBI_CONC_PROG_RUN_LOG(
driving_table_code ,
etl_type ,
load_type ,
bound_type ,
bound_level_entity_code ,
bound_level_entity_id ,
from_bound_date ,
from_bound_id ,
to_bound_date ,
to_bound_id ,
completion_status_code ,
stop_reason_code ,
created_by ,
creation_date ,
last_run_date ,
last_update_date ,
last_updated_by ,
last_update_login ,
program_id ,
program_login_id ,
program_application_id ,
request_id
)
SELECT
'MMT' ,
p_etl_type ,
'INIT' ,
bound_type ,
bound_level_entity_code ,
bound_level_entity_id , /* org frm INV INIT record */
null ,
from_bound_id , /* min (from_bound_id) from all INV records */
null ,
null , /* set to null for now updated in set_mmt_new_bounds */
null ,
stop_reason_code ,
l_user_id ,
sysdate ,
sysdate ,
sysdate ,
l_user_id ,
l_login_id ,
l_program_id ,
l_program_login_id ,
l_program_application_id,
l_request_id
FROM OPI_DBI_CONC_PROG_RUN_LOG
WHERE etl_type = 'INVENTORY'
AND driving_table_code = 'MMT'
AND load_type = 'INIT';
l_debug_msg := 'Inserted '||to_char(sql%rowcount)||' rows into OPI_DBI_CONC_PROG_RUN_LOG' ;
SELECT max(transaction_id)+1 INTO l_max_trx_id
FROM mtl_material_transactions mmt;
INSERT INTO OPI_DBI_CONC_PROG_RUN_LOG(
driving_table_code ,
etl_type ,
load_type ,
bound_type ,
bound_level_entity_code ,
bound_level_entity_id ,
from_bound_date ,
from_bound_id ,
to_bound_date ,
to_bound_id ,
completion_status_code ,
stop_reason_code ,
created_by ,
creation_date ,
last_run_date ,
last_update_date ,
last_updated_by ,
last_update_login ,
program_id ,
program_login_id ,
program_application_id ,
request_id)
SELECT
'MMT' ,
p_etl_type ,
'INIT' ,
'ID' ,
'ORGANIZATION' ,
mp.organization_id ,
null ,
/* FIRST TXN ID FOR THE ORGANIZATION AFTER GSD .IF There are no records for
the org after GSD then incr record wouldn't be created*/
min_trx.transaction_id ,
null ,
/* FIRST UNCOSTED TXN ID FOR THE ORGANIZATION. MAX TRANSACTION OF MMT
IN CASE THERE NO UNCOSTED TXN. */
nvl(uncosted_trx.transaction_id,l_max_trx_id) ,
null ,
/* stop reason code */
decode (uncosted_trx.transaction_id,NULL, 'STOP_ALL_COSTED','STOP_UNCOSTED'),
l_user_id ,
sysdate ,
sysdate ,
sysdate ,
l_user_id ,
l_login_id ,
l_program_id ,
l_program_login_id ,
l_program_application_id,
l_request_id
FROM mtl_parameters mp ,
(
SELECT /*+ no_merge parallel(mmt) */ organization_id,min(transaction_id) transaction_id
FROM mtl_material_transactions mmt
WHERE transaction_date >= l_global_start_date
GROUP BY organization_id
)min_trx ,
(
SELECT /*+ no_merge parallel(mmt) */ organization_id,min(transaction_id) transaction_id
FROM mtl_material_transactions mmt
WHERE costed_flag in('N','E')
AND transaction_date >= l_global_start_date --Bug 5096963
GROUP BY organization_id
)uncosted_trx
WHERE mp.organization_id = min_trx.organization_id
AND min_trx.organization_id = uncosted_trx.organization_id(+)
AND mp.process_enabled_flag <> 'Y';
l_debug_msg := 'Inserted '||to_char(sql%rowcount)||' rows into OPI_DBI_CONC_PROG_RUN_LOG' ;
SELECT DISTINCT organization_id
FROM mtl_parameters
WHERE process_enabled_flag <> 'Y'
MINUS
SELECT DISTINCT bound_level_entity_id
FROM OPI_DBI_CONC_PROG_RUN_LOG
WHERE etl_type = p_etl_type
AND driving_table_code = 'MMT';
/* copy from bound as max of existing to bound for the new organizations and insert records */
/* this code inserts all new organizations from MMT without checking the existence of a
transaction for them after global_start_date */
/* find new organizations only on INCR load */
if (p_load_type = 'INCR') then
l_stmt_no := 10;
INSERT INTO OPI_DBI_CONC_PROG_RUN_LOG(
driving_table_code ,
etl_type ,
load_type ,
bound_type ,
bound_level_entity_code ,
bound_level_entity_id ,
from_bound_date ,
from_bound_id ,
to_bound_date ,
to_bound_id ,
completion_status_code ,
stop_reason_code ,
created_by ,
creation_date ,
last_run_date ,
last_update_date ,
last_updated_by ,
last_update_login ,
program_id ,
program_login_id ,
program_application_id ,
request_id
)
SELECT
'MMT' ,
p_etl_type ,
'INCR' ,
'ID' ,
'ORGANIZATION' ,
c_new_org.organization_id ,
null ,
max(to_bound_id) ,
null ,
null ,
null ,
null ,
l_user_id ,
sysdate ,
sysdate ,
sysdate ,
l_user_id ,
l_login_id ,
l_program_id ,
l_program_login_id ,
l_program_application_id,
l_request_id
FROM OPI_DBI_CONC_PROG_RUN_LOG
WHERE etl_type = p_etl_type
AND driving_table_code = 'MMT';
l_debug_msg := 'Inserted '||to_char(sql%rowcount)||' rows into OPI_DBI_CONC_PROG_RUN_LOG' ;
end if; /* end insert new org */
SELECT max(transaction_id)+1 INTO l_max_trx_id
FROM mtl_material_transactions mmt;
/* update to bounds for all records as first uncosted transaction */
UPDATE OPI_DBI_CONC_PROG_RUN_LOG prlout
SET ( to_bound_id ,
stop_reason_code ,
completion_status_code ,
last_run_date ,
last_update_date ,
last_updated_by ,
last_update_login ,
program_id ,
program_login_id ,
program_application_id ,
request_id
) =
(select
/* FIRST UNCOSTED TXN ID FOR THE ORGANIZATION.
MAX TRANSACTION OF MMT IN CASE THERE NO UNCOSTED TXN. */
nvl(uncosted_trx.transaction_id,l_max_trx_id),
/* stop reason code */
decode(uncosted_trx.transaction_id,NULL,'STOP_ALL_COSTED','STOP_UNCOSTED'),
null ,
sysdate ,
sysdate ,
l_user_id ,
l_login_id ,
l_program_id ,
l_program_login_id ,
l_program_application_id,
l_request_id
from
(SELECT /*+ no_merge parallel(mmt) */ organization_id,min(transaction_id) transaction_id
FROM mtl_material_transactions mmt
WHERE costed_flag in('N','E')
AND transaction_id >= (SELECT from_bound_id FROM opi_dbi_conc_prog_run_log plog
WHERE plog.etl_type = p_etl_type
AND plog.load_type = p_load_type
AND plog.driving_table_code = 'MMT'
AND plog.bound_level_entity_code = 'ORGANIZATION'
AND mmt.organization_id = plog.bound_level_entity_id) --Bug 5096963
GROUP BY organization_id
) uncosted_trx
, mtl_parameters mp
where prlout.bound_level_entity_id = mp.organization_id
and mp.organization_id = uncosted_trx.organization_id(+))
WHERE prlout.driving_table_code = 'MMT'
AND prlout.etl_type = p_etl_type
AND prlout.load_type = p_load_type
AND prlout.bound_level_entity_code = 'ORGANIZATION';
l_debug_msg := 'Updated '||to_char(sql%rowcount)||' rows in OPI_DBI_CONC_PROG_RUN_LOG' ;
select BIS_COMMON_PARAMETERS.GET_GLOBAL_START_DATE into l_global_start_date from DUAL;
/* insert records with from_bound_id as first transaction after GSD and to_bound_id as max of
transaction_id as of setting bounds from WTA */
l_stmt_no := 20;
INSERT into OPI_DBI_CONC_PROG_RUN_LOG(
driving_table_code ,
etl_type ,
load_type ,
bound_type ,
bound_level_entity_code ,
bound_level_entity_id ,
from_bound_date ,
from_bound_id ,
to_bound_date ,
to_bound_id ,
completion_status_code ,
stop_reason_code ,
created_by ,
creation_date ,
last_run_date ,
last_update_date ,
last_updated_by ,
last_update_login ,
program_id ,
program_login_id ,
program_application_id ,
request_id
)
SELECT
'WTA' ,
'INVENTORY' ,
'INIT' ,
'ID' ,
null ,
null ,
null ,
min(transaction_id) ,
null ,
max(transaction_id)+1 ,
null ,
null ,
l_user_id ,
sysdate ,
sysdate ,
sysdate ,
l_user_id ,
l_login_id ,
l_program_id ,
l_program_login_id ,
l_program_application_id,
l_request_id
FROM wip_transaction_accounts
WHERE transaction_date >= l_global_start_date;
l_debug_msg := 'Inserted '||to_char(sql%rowcount)||' rows into OPI_DBI_CONC_PROG_RUN_LOG' ;
/* Update to_bound as max of transaction_id as of setting the bounds */
l_stmt_no := 30;
UPDATE OPI_DBI_CONC_PROG_RUN_LOG
SET (to_bound_id ,
completion_status_code ,
last_update_date ,
last_updated_by ,
last_update_login ,
program_id ,
program_login_id ,
program_application_id ,
request_id ) =
(SELECT max(transaction_id)+1 ,
null ,
sysdate ,
l_user_id ,
l_login_id ,
l_program_id ,
l_program_login_id ,
l_program_application_id,
l_request_id
FROM wip_transaction_accounts)
WHERE driving_table_code = 'WTA'
AND etl_type = 'INVENTORY'
AND load_type = p_load_type;
l_debug_msg := 'Updated '||to_char(sql%rowcount)||' rows in OPI_DBI_CONC_PROG_RUN_LOG' ;
select BIS_COMMON_PARAMETERS.GET_GLOBAL_START_DATE into l_global_start_date from DUAL;
INSERT into OPI_DBI_CONC_PROG_RUN_LOG(
driving_table_code ,
etl_type ,
load_type ,
bound_type ,
bound_level_entity_code ,
bound_level_entity_id ,
from_bound_date ,
from_bound_id ,
to_bound_date ,
to_bound_id ,
completion_status_code ,
stop_reason_code ,
created_by ,
creation_date ,
last_run_date ,
last_update_date ,
last_updated_by ,
last_update_login ,
program_id ,
program_login_id ,
program_application_id ,
request_id )
SELECT
p_driving_table_code ,
p_etl_type ,
'INIT' ,
'DATE' ,
null ,
null ,
l_global_start_date ,
null ,
sysdate ,
null ,
null ,
null ,
l_user_id ,
sysdate ,
sysdate ,
sysdate ,
l_user_id ,
l_login_id ,
l_program_id ,
l_program_login_id ,
l_program_application_id,
l_request_id
FROM DUAL ;
l_debug_msg := 'Inserted '||to_char(sql%rowcount)||' rows into OPI_DBI_CONC_PROG_RUN_LOG' ;
UPDATE OPI_DBI_CONC_PROG_RUN_LOG
SET TO_BOUND_DATE = sysdate ,
completion_status_code = null ,
LAST_RUN_DATE = sysdate ,
LAST_UPDATE_DATE = sysdate ,
LAST_UPDATED_BY = l_user_id ,
LAST_UPDATE_LOGIN = l_login_id ,
PROGRAM_ID = l_program_id ,
PROGRAM_LOGIN_ID = l_program_login_id ,
PROGRAM_APPLICATION_ID = l_program_application_id ,
REQUEST_ID = l_request_id
WHERE DRIVING_TABLE_CODE = p_driving_table_code
AND ETL_TYPE = p_etl_type
AND LOAD_TYPE = 'INCR';
l_debug_msg := 'Updated '||to_char(sql%rowcount)||' rows in OPI_DBI_CONC_PROG_RUN_LOG' ;
UPDATE OPI_DBI_CONC_PROG_RUN_LOG
SET completion_status_code = 'S'
WHERE etl_type = p_etl_type
AND load_type = p_load_type; /*update log table with status success */
l_debug_msg := 'Updated status to success for '||to_char(sql%rowcount)||' rows';
insert into mano_log (pkg, proc_name, stmt_no, msg )
select p_pkg_name, p_proc_name, p_stmt_no, p_debug_msg from dual;
SELECT mp.organization_code,
log.to_bound_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_conc_prog_run_log log,
mtl_parameters mp,
mtl_material_transactions mmt
WHERE log.driving_table_code = 'MMT'
AND log.to_bound_id = mmt.transaction_id (+)
AND log.bound_level_entity_id = mp.organization_id
AND log.etl_type = p_etl_type
AND log.load_type = p_load_type;
SELECT g_warning
INTO l_warning
FROM OPI_DBI_CONC_PROG_RUN_LOG
WHERE stop_reason_code = STOP_UNCOSTED
AND etl_type = p_etl_type
AND load_type = p_load_type
AND rownum = 1;
DELETE FROM OPI_DBI_ORG_LE_TEMP;
l_debug_msg := 'Deleted '||to_char(sql%rowcount)||' rows from OPI_DBI_ORG_LE_TEMP' ;
-- Inserting rows into the temp table from org_organization_definitions and
-- gmf_fiscal_policies
l_stmt_no := 20;
INSERT INTO OPI_DBI_ORG_LE_TEMP
(
organization_id ,
ledger_id ,
legal_entity_id ,
valuation_cost_type_id
)
SELECT ood.organization_id ,
gfp.ledger_id ,
gfp.legal_entity_id ,
gfp.cost_type_id
FROM ORG_ORGANIZATION_DEFINITIONS ood,
GMF_FISCAL_POLICIES gfp,
MTL_PARAMETERS mp
WHERE mp.process_enabled_flag = 'Y' --for OPM orgs only
AND mp.organization_id = ood.organization_id
AND ood.legal_entity = gfp.legal_entity_id ;
l_debug_msg := 'Inserted '||to_char(sql%rowcount)||' rows from OPI_DBI_ORG_LE_TEMP' ;
l_debug_msg := 'Error in deleting/inserting OPM org ledger data into OPI_DBI_ORG_LE_TEMP' ;