[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_last_update_login NUMBER;
g_last_updated_by NUMBER;
update_log_exception EXCEPTION;
PRAGMA EXCEPTION_INIT (update_log_exception, -20904);
select org1.organization_id, nvl(org2.existing_flag, 0), org1.from_date
bulk collect into l_org_list, l_existing_list, l_from_dates
from (select /*+ no_merge use_hash(sched,setup,plan,org) */
distinct org.organization_id,
sched.from_date
from isc_dbi_plan_organizations org,
isc_dbi_plans plan,
opi_dbi_baseline_schedules sched,
opi_dbi_baseline_plans setup
where sched.next_collection_date <= g_sysdate
and sched.schedule_type = 1
and sched.baseline_id = setup.baseline_id
and setup.plan_name = plan.compile_designator
and setup.owning_org_id = plan.organization_id
and org.plan_id = plan.plan_id
) org1,
(select distinct organization_id,
1 existing_flag
from opi_dbi_ptp_conv
) org2
where org1.organization_id = org2.organization_id (+)
;
l_stmt := ' insert into OPI_DBI_PTP_COST
(
FROZEN_FLAG,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
UNIT_COST,
FROM_DATE,
TO_DATE,
SOURCE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
select /*+ use_hash(msi,mp) index_ffs(msi,MTL_SYSTEM_ITEMS_B_U1) parallel_index(msi,MTL_SYSTEM_ITEMS_B_U1) */
null l_frozen_flag,
msi.organization_id,
msi.inventory_item_id,
0 item_cost,
:g_global_start_date FROM_DATE,
org_dates.from_date TO_DATE,
:l_source,
:g_sysdate,
:g_last_updated_by,
:g_sysdate,
:g_last_updated_by,
:g_last_update_login
from
mtl_parameters mp,
mtl_system_Items_b msi,
(select /*+ no_merge use_hash(sched,setup,plan,orgs) */
orgs.organization_id,
sched.from_date
from opi_dbi_baseline_schedules sched,
opi_dbi_baseline_plans setup,
isc_dbi_plans plan,
isc_dbi_plan_organizations orgs
where sched.next_collection_date <= :g_sysdate
and sched.schedule_type = 1
and sched.baseline_id = setup.baseline_id
and setup.plan_name = plan.compile_designator
and setup.owning_org_id = plan.organization_id
and plan.plan_id = orgs.plan_id
) org_dates
where mp.organization_id in ' || l_new_orgs ||
' AND mp.organization_id = org_dates.organization_id
AND mp.process_enabled_flag <> ''Y''
AND mp.organization_id = msi.organization_id
union all
select /*+ use_hash(cic) index(cic CST_ITEM_COSTS_U1) parallel(io) parallel(cic) */
:l_frozen_flag,
io.organization_id,
io.inventory_item_id,
nvl(cic.item_cost, 0) item_cost,
org_dates.from_date FROM_DATE,
null TO_DATE,
:l_source,
:g_sysdate,
:g_last_updated_by,
:g_sysdate,
:g_last_updated_by,
:g_last_update_login
from
cst_item_costs cic,
(select /*+ no_merge use_hash(msi) parallel(mp) index_ffs(msi,MTL_SYSTEM_ITEMS_B_U1) parallel_index(msi,MTL_SYSTEM_ITEMS_B_U1) */
msi.organization_id,
msi.inventory_item_id,
decode (mp.primary_cost_method, 1, 1, 2) cost_type_id
from
mtl_system_items_b msi,
mtl_parameters mp
where mp.organization_id in ' || l_new_orgs || '
AND mp.process_enabled_flag <> ''Y''
AND mp.organization_id = msi.organization_id
) io,
(select /*+ no_merge use_hash(sched,setup,plan,orgs) */
orgs.organization_id,
sched.from_date
from opi_dbi_baseline_schedules sched,
opi_dbi_baseline_plans setup,
isc_dbi_plans plan,
isc_dbi_plan_organizations orgs
where sched.next_collection_date <= :g_sysdate
and sched.schedule_type = 1
and sched.baseline_id = setup.baseline_id
and setup.plan_name = plan.compile_designator
and setup.owning_org_id = plan.organization_id
and plan.plan_id = orgs.plan_id
) org_dates
where io.organization_id = org_dates.organization_id
AND io.organization_id = cic.organization_id (+)
AND io.inventory_item_id = cic.inventory_item_id (+)
AND io.cost_type_id = cic.cost_type_id (+)
'
;
EXECUTE IMMEDIATE l_stmt USING g_global_start_date, 1, g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by, g_last_update_login, g_sysdate, -1, 1, g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by, g_last_update_login, g_sysdate;
SELECT gsob.currency_code
INTO l_currency_code
FROM hr_organization_information hoi,
gl_sets_of_books gsob
WHERE
hoi.ORG_INFORMATION_CONTEXT = 'Accounting Information'
AND hoi.org_information1 = to_char(gsob.set_of_books_id)
AND hoi.organization_id = l_org_list(i)
AND rownum < 2;
SELECT fii_currency.get_global_rate_primary(l_currency_code, g_sysdate)
INTO l_rate
FROM dual;
insert into OPI_DBI_PTP_CONV
(
FROZEN_FLAG,
ORGANIZATION_ID,
CONVERSION_RATE,
SEC_CONVERSION_RATE,
CURRENCY_CODE,
FROM_DATE,
TO_DATE,
SOURCE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES (
null,
:l_org_id,
:l_rate,
:l_secondary_rate,
:l_currency_code,
:g_global_start_date,
:l_from_date,
:l_source,
:g_sysdate,
:g_last_upated_by,
:g_sysdate,
:g_last_updated_by,
:g_last_update_login
)
'
;
EXECUTE IMMEDIATE l_stmt USING l_org_list(i), 0, 0, l_currency_code, g_global_start_date, l_from_dates(i), 1, g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by, g_last_update_login;
insert into OPI_DBI_PTP_CONV
(
FROZEN_FLAG,
ORGANIZATION_ID,
CONVERSION_RATE,
SEC_CONVERSION_RATE,
CURRENCY_CODE,
FROM_DATE,
TO_DATE,
SOURCE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES (
:l_frozen_flag,
:l_org_id,
:l_rate,
:l_secondary_rate,
:l_currency_code,
:l_from_date,
null,
:l_source,
:g_sysdate,
:g_last_upated_by,
:g_sysdate,
:g_last_updated_by,
:g_last_update_login
)
'
;
EXECUTE IMMEDIATE l_stmt USING -1, l_org_list(i), l_rate, l_secondary_rate, l_currency_code, l_from_dates(i), 1, g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by, g_last_update_login;
update OPI_DBI_PTP_COST
set frozen_flag = null,
to_date = :g_sysdate,
last_update_date = :g_sysdate,
last_updated_by = :g_last_updated_by,
last_update_login = :g_last_update_login,
source = -1
where frozen_flag = -1
and source = 1
and organization_id in ' || l_existing_orgs
;
EXECUTE IMMEDIATE l_stmt USING g_sysdate, g_sysdate, g_last_updated_by, g_last_update_login;
select /*+ use_hash(cic) parallel(io) parallel(cic) */
:l_frozen_flag frozen_flag,
io.organization_id,
io.inventory_item_id,
nvl(cic.item_cost, 0) item_cost,
:g_sysdate from_date,
null to_date,
:l_source source
from
cst_item_costs cic,
(select /*+ no_merge use_hash(msi) parallel(mp) index_ffs(msi,MTL_SYSTEM_ITEMS_B_U1) parallel_index(msi,MTL_SYSTEM_ITEMS_B_U1) */
msi.organization_id,
msi.inventory_item_id,
decode (mp.primary_cost_method, 1, 1, 2) cost_type_id
from
mtl_system_items_b msi,
mtl_parameters mp
where mp.organization_id in ' || l_existing_orgs || '
AND mp.process_enabled_flag <> ''Y''
AND mp.organization_id = msi.organization_id
) io
where io.organization_id = cic.organization_id (+)
AND io.inventory_item_id = cic.inventory_item_id (+)
AND io.cost_type_id = cic.cost_type_id (+)
) new_cost
on
( cost.organization_id = new_cost.organization_id
and cost.inventory_item_id = new_cost.inventory_item_id
and cost.unit_cost = new_cost.item_cost
and cost.source = -1
)
when matched then
update set
cost.frozen_flag = -1,
cost.to_date = null
when not matched then
insert
(
FROZEN_FLAG,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
UNIT_COST,
FROM_DATE,
TO_DATE,
SOURCE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
values
(
new_cost.frozen_flag,
new_cost.organization_id,
new_cost.inventory_item_id,
new_cost.item_cost,
new_cost.from_date,
new_cost.to_date,
new_cost.source,
:g_sysdate,
:g_last_updated_by,
:g_sysdate,
:g_last_updated_by,
:g_last_update_login
)
';
EXECUTE IMMEDIATE l_stmt USING -1, g_sysdate, 1, g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by, g_last_update_login;
update OPI_DBI_PTP_COST
set from_date = :g_global_start_date
where (organization_id, inventory_item_id) in
(select organization_id, inventory_item_id from OPI_DBI_PTP_COST
where source = 1 and frozen_flag = -1 and creation_date = :g_sysdate
minus
select organization_id, inventory_item_id from OPI_DBI_PTP_COST
where source = -1
)
';
update opi_dbi_ptp_cost
set source = 1
where source = -1
;
update OPI_DBI_PTP_CONV
set frozen_flag = null,
to_date = :g_sysdate,
last_update_date = :g_sysdate,
last_updated_by = :g_last_updated_by,
last_update_login = :g_last_update_login,
source = -1
where organization_id in ' || l_existing_orgs ||
' and frozen_flag = -1
and source = 1
';
EXECUTE IMMEDIATE l_stmt USING g_sysdate, g_sysdate, g_last_updated_by, g_last_update_login;
SELECT gsob.currency_code
INTO l_currency_code
FROM hr_organization_information hoi,
gl_sets_of_books gsob
WHERE
hoi.ORG_INFORMATION_CONTEXT = 'Accounting Information'
AND hoi.org_information1 = to_char(gsob.set_of_books_id)
AND hoi.organization_id = l_org_list(i)
AND rownum < 2;
SELECT fii_currency.get_global_rate_primary(l_currency_code, g_sysdate)
INTO l_rate
FROM dual;
select
:l_org_id organization_id,
:l_rate conversion_rate,
:l_secondary_rate sec_conversion_rate,
:g_sysdate from_date,
null to_date
from dual
) new_conv
on
( conv.organization_id = new_conv.organization_id
and conv.conversion_rate = new_conv.conversion_rate
and nvl (conv.sec_conversion_rate, -9999) = nvl (new_conv.sec_conversion_rate, -9999)
and conv.source = -1
)
when matched then
update set
conv.frozen_flag = -1,
conv.to_date = null
when not matched then
insert (
FROZEN_FLAG,
ORGANIZATION_ID,
CONVERSION_RATE,
SEC_CONVERSION_RATE,
CURRENCY_CODE,
FROM_DATE,
TO_DATE,
SOURCE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
values
(
:l_frozen_flag,
new_conv.organization_id,
new_conv.conversion_rate,
new_conv.sec_conversion_rate,
:l_currency_code,
new_conv.from_date,
new_conv.to_date,
:l_source,
:g_sysdate,
:g_last_upated_by,
:g_sysdate,
:g_last_updated_by,
:g_last_update_login
)
';
EXECUTE IMMEDIATE l_stmt USING l_org_list(i), l_rate, l_secondary_rate, g_sysdate, -1, l_currency_code, 1, g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by, g_last_update_login;
update OPI_DBI_PTP_CONV
set source = 1
where source = -1
;
select org1.organization_id, nvl(org2.existing_flag, 0), org1.from_date
bulk collect into l_org_list, l_existing_list, l_from_dates
from (select /*+ no_merge use_hash(sched,setup,plan,org) */
distinct org.organization_id,
sched.from_date
from isc_dbi_plan_organizations org,
isc_dbi_plans plan,
opi_dbi_baseline_schedules sched,
opi_dbi_baseline_plans setup
where sched.next_collection_date <= g_sysdate
and sched.schedule_type = 1
and sched.baseline_id = setup.baseline_id
and setup.plan_name = plan.compile_designator
and setup.owning_org_id = plan.organization_id
and org.plan_id = plan.plan_id
) org1,
(select distinct organization_id,
1 existing_flag
from opi_dbi_ptp_conv
) org2,
mtl_parameters mp
where org1.organization_id = org2.organization_id (+)
and org1.organization_id = mp.organization_id
and mp.process_enabled_flag = 'Y';
l_stmt := 'INSERT INTO opi_pmi_cost_param_gtmp
(
item_id,
whse_code,
orgn_code,
trans_date
)
SELECT
i.item_id,
w.whse_code,
w.orgn_code,
SYSDATE trans_date
FROM
ic_item_mst_b i,
ic_whse_mst w
WHERE
w.mtl_organization_id IN ' || l_new_orgs;
l_stmt := 'INSERT INTO opi_pmi_cost_param_gtmp
(
item_id,
whse_code,
orgn_code,
trans_date
)
SELECT
i.item_id,
w.whse_code,
w.orgn_code,
SYSDATE trans_date
FROM
ic_item_mst_b i,
ic_whse_mst w
WHERE
w.mtl_organization_id IN ' || l_existing_orgs;
l_stmt := ' insert into OPI_DBI_PTP_COST
(
FROZEN_FLAG,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
UNIT_COST,
FROM_DATE,
TO_DATE,
SOURCE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
select
null l_frozen_flag,
msi.organization_id,
msi.inventory_item_id,
0 item_cost,
:g_global_start_date FROM_DATE,
org_dates.from_date TO_DATE,
:l_source,
:g_sysdate,
:g_last_updated_by,
:g_sysdate,
:g_last_updated_by,
:g_last_update_login
from
mtl_parameters mp,
mtl_system_Items_b msi,
(select orgs.organization_id,
sched.from_date
from opi_dbi_baseline_schedules sched,
opi_dbi_baseline_plans setup,
isc_dbi_plans plan,
isc_dbi_plan_organizations orgs
where sched.next_collection_date <= :g_sysdate
and sched.schedule_type = 1
and sched.baseline_id = setup.baseline_id
and setup.plan_name = plan.compile_designator
and setup.owning_org_id = plan.organization_id
and plan.plan_id = orgs.plan_id
) org_dates
where mp.organization_id in ' || l_new_orgs ||
' AND mp.organization_id = org_dates.organization_id
AND mp.process_enabled_flag = ''Y''
AND mp.organization_id = msi.organization_id
union all
select
:l_frozen_flag,
msi.organization_id,
msi.inventory_item_id,
nvl(cst.total_cost, 0) item_cost,
org_dates.from_date FROM_DATE,
null TO_DATE,
:l_source,
:g_sysdate,
:g_last_updated_by,
:g_sysdate,
:g_last_updated_by,
:g_last_update_login
from
(select orgs.organization_id,
sched.from_date
from opi_dbi_baseline_schedules sched,
opi_dbi_baseline_plans setup,
isc_dbi_plans plan,
isc_dbi_plan_organizations orgs
where sched.next_collection_date <= :g_sysdate
and sched.schedule_type = 1
and sched.baseline_id = setup.baseline_id
and setup.plan_name = plan.compile_designator
and setup.owning_org_id = plan.organization_id
and plan.plan_id = orgs.plan_id
) org_dates,
ic_whse_mst w,
mtl_system_items_b msi,
ic_item_mst_b i,
opi_pmi_cost_result_gtmp cst
where
w.mtl_organization_id = org_dates.organization_id
AND msi.organization_id = w.mtl_organization_id
AND i.item_no = msi.segment1
AND i.item_id = cst.item_id
AND w.whse_code = cst.whse_code
'
;
EXECUTE IMMEDIATE l_stmt USING g_global_start_date, 2, g_sysdate, g_last_updated_by, g_sysdate,
g_last_updated_by, g_last_update_login, g_sysdate, -1, 2,
g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by,
g_last_update_login, g_sysdate;
update OPI_DBI_PTP_COST
set frozen_flag = null,
to_date = :g_sysdate,
last_update_date = :g_sysdate,
last_updated_by = :g_last_updated_by,
last_update_login = :g_last_update_login,
source = -2
where frozen_flag = -1
and source = 2
and organization_id in ' || l_existing_orgs
;
EXECUTE IMMEDIATE l_stmt USING g_sysdate, g_sysdate, g_last_updated_by, g_last_update_login;
select
:l_frozen_flag frozen_flag,
msi.organization_id,
msi.inventory_item_id,
nvl(cst.total_cost, 0) item_cost,
:g_sysdate from_date,
null to_date,
:l_source source
from
opi_pmi_cost_result_gtmp cst,
ic_whse_mst w,
ic_item_mst_b i,
mtl_system_items_b msi
where
w.mtl_organization_id IN ' || l_existing_orgs || '
AND w.whse_code = cst.whse_code
AND i.item_id = cst.item_id
AND msi.segment1 = i.item_no
AND msi.organization_id = w.mtl_organization_id
) new_cost
on
( cost.organization_id = new_cost.organization_id
and cost.inventory_item_id = new_cost.inventory_item_id
and cost.unit_cost = new_cost.item_cost
and cost.source = -1
)
when matched then
update set
cost.frozen_flag = -1,
cost.to_date = null
when not matched then
insert
(
FROZEN_FLAG,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
UNIT_COST,
FROM_DATE,
TO_DATE,
SOURCE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
values
(
new_cost.frozen_flag,
new_cost.organization_id,
new_cost.inventory_item_id,
new_cost.item_cost,
new_cost.from_date,
new_cost.to_date,
new_cost.source,
:g_sysdate,
:g_last_updated_by,
:g_sysdate,
:g_last_updated_by,
:g_last_update_login
)
';
EXECUTE IMMEDIATE l_stmt USING -1, g_sysdate, 2, g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by, g_last_update_login;
update OPI_DBI_PTP_COST
set from_date = :g_global_start_date
where (organization_id, inventory_item_id) in
(select organization_id, inventory_item_id from OPI_DBI_PTP_COST
where source = 2 and frozen_flag = -1 and creation_date = :g_sysdate
minus
select organization_id, inventory_item_id from OPI_DBI_PTP_COST
where source = -2
)
';
update opi_dbi_ptp_cost
set source = 2
where source = -2
;
l_insert_tables STRINGLIST := STRINGLIST();
l_delete NUMBER;
select def.baseline_id,
def.data_start_date,
sched.from_date
from OPI_DBI_BASELINE_DEFINITIONS def,
OPI_DBI_BASELINE_SCHEDULES sched
where def.baseline_id = sched.baseline_id
and sched.next_collection_date <= trunc(sysdate)
and sched.schedule_type = 1
;
g_last_update_login := nvl(fnd_global.login_id, -1);
g_last_updated_by := nvl(fnd_global.user_id, -1);
SELECT BIS_COMMON_PARAMETERS.GET_GLOBAL_START_DATE INTO g_global_start_date FROM DUAL;
SELECT bis_common_parameters.get_currency_code INTO g_global_currency_code FROM dual;
select stop_reason_code into l_refresh from opi_dbi_run_log_curr where etl_id = 7 and source = 1;
insert into opi_dbi_run_log_curr
(organization_id,
source,
last_collection_date,
start_txn_id,
next_start_txn_id,
etl_id,
stop_reason_code,
last_transaction_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
values
(null, 1, null, null, null, 7, 2, null, g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by, g_last_update_login);
select min(from_date)
into l_from_date
from
(
select sched.baseline_id,
sched.from_date
from OPI_DBI_BASELINE_SCHEDULES sched,
OPI_DBI_BASELINE_PLANS setup
where sched.next_collection_date <= g_sysdate
and sched.schedule_type = 1
and sched.baseline_id = setup.baseline_id
) boundary
;
select count(*)
into l_count
from
(
select orgs.organization_id, count(orgs.plan_id) num_of_plans
from OPI_DBI_BASELINE_SCHEDULES sched,
OPI_DBI_BASELINE_PLANS setup,
ISC_DBI_PLANS plan,
ISC_DBI_PLAN_ORGANIZATIONS orgs
where sched.next_collection_date <= g_sysdate
and sched.schedule_type = 1
and sched.baseline_id = setup.baseline_id
and setup.plan_name = plan.compile_designator
and setup.owning_org_id = plan.organization_id
and plan.plan_id = orgs.plan_id
group by orgs.organization_id
) org_plan
where num_of_plans > 1
;
select count(*)
into l_count
from OPI_DBI_BASELINE_SCHEDULES sched,
OPI_DBI_BASELINE_PLANS setup,
ISC_DBI_PLANS plan
where sched.baseline_id = setup.baseline_id
and setup.plan_name = plan.compile_designator
and setup.owning_org_id = plan.organization_id
and sched.from_date < trunc(plan.data_start_date)
and sched.next_collection_date <= g_sysdate
and sched.schedule_type = 1
;
select RPAD(def.baseline_name, 20, ' ') || ' ' ||
RPAD(setup.plan_name, 20, ' ') || ' ' ||
RPAD(TO_CHAR(sched.from_date, 'DD-MON-YYYY'), 20, ' ') || ' ' ||
RPAD(TO_CHAR(plan.data_start_date, 'DD-MON-YYYY'), 20, ' ')
bulk collect into l_strings
from OPI_DBI_BASELINE_DEFINITIONS def,
OPI_DBI_BASELINE_SCHEDULES sched,
OPI_DBI_BASELINE_PLANS setup,
ISC_DBI_PLANS plan
where def.baseline_id = sched.baseline_id
and sched.baseline_id = setup.baseline_id
and setup.plan_name = plan.compile_designator
and setup.owning_org_id = plan.organization_id
and sched.next_collection_date <= g_sysdate
and sched.schedule_type = 1
;
select def.baseline_name, sched.baseline_id, def.last_collected_date
bulk collect into l_strings, l_baseline_ids, l_collected_dates
from OPI_DBI_BASELINE_SCHEDULES sched,
OPI_DBI_BASELINE_DEFINITIONS def
where sched.next_collection_date <= g_sysdate
and sched.schedule_type = 1
and def.baseline_id = sched.baseline_id
;
l_insert_tables.extend(l_baseline_ids.count);
l_insert_tables(i) := 'WHEN BASELINE_ID = ''' || l_baseline_ids(i) || ''' THEN INTO OPI_DBI_PTP_TMP_' || l_baseline_ids(i);
'LAST_UPDATE_DATE DATE NOT NULL, ' ||
'LAST_UPDATED_BY NUMBER NOT NULL, ' ||
'LAST_UPDATE_LOGIN NUMBER)';
BIS_COLLECTION_UTILITIES.put_line('Insert new data into temporary regular tables.');
l_stmt := 'INSERT /*+ append ';
IF l_insert_tables.count <> 0 THEN
FOR i IN l_create_tables.FIRST..l_create_tables.LAST LOOP
l_stmt := l_stmt || l_insert_tables(i)
|| ' VALUES (' ||
'ORGANIZATION_ID, ' ||
'BASELINE_ID, ' ||
'PLAN_NAME, ' ||
'OWNING_ORG_ID, ' ||
'INVENTORY_ITEM_ID, ' ||
'TRANSACTION_DATE, ' ||
'PLANNED_QUANTITY, ' ||
'UOM_CODE, ' ||
'CREATION_DATE, ' ||
'CREATED_BY, ' ||
'LAST_UPDATE_DATE, ' ||
'LAST_UPDATED_BY, ' ||
'LAST_UPDATE_LOGIN)';
'select /*+ ordered use_nl(time) use_hash(bucket, supply) parallel(supply) */
supply.organization_id ORGANIZATION_ID,
bl.baseline_id BASELINE_ID,
bl.compile_designator PLAN_NAME,
bl.organization_id OWNING_ORG_ID,
supply.sr_inventory_item_id INVENTORY_ITEM_ID,
trunc(time.report_date) TRANSACTION_DATE,
sum(nvl(supply.new_order_quantity, 0)/bucket.days_in_bkt) PLANNED_QUANTITY,
supply.uom_code UOM_CODE,
:g_sysdate CREATION_DATE,
:g_last_updated_by CREATED_BY,
:g_sysdate LAST_UPDATE_DATE,
:g_last_updated_by LAST_UPDATED_BY,
:g_last_update_login LAST_UPDATE_LOGIN
from
(select /*+ no_merge use_hash(sched,setup,plan) */
sched.baseline_id,
sched.from_date,
sched.to_date,
plan.plan_id,
plan.compile_designator,
plan.organization_id,
plan.cutoff_date
from
opi_dbi_baseline_schedules sched,
opi_dbi_baseline_plans setup,
isc_dbi_plans plan
where sched.next_collection_date <= :g_sysdate
and sched.schedule_type = 1
and sched.baseline_id = setup.baseline_id
and setup.plan_name = plan.compile_designator
and setup.owning_org_id = plan.organization_id
) bl,
isc_dbi_plan_buckets bucket,
fii_time_day_all_v time,
isc_dbi_supplies_f supply
where bucket.plan_id = bl.plan_id
and bucket.organization_id = bl.organization_id
and bucket.plan_id = supply.plan_id
and supply.new_schedule_date + nvl(supply.new_processing_days, 0) between bucket.bkt_start_date and bucket.bkt_end_date
AND nvl(supply.disposition_status_type, 0) <> 2
and supply.in_source_plan = 2
and nvl(supply.bom_item_type, 0) <> 3
--and nvl(supply.r_cfm_routing_flag, 0) <> 3
and (supply.order_type in (3, 14, 16, 27, 28, 30)
OR
(supply.order_type in (5, 17)
AND supply.source_sr_instance_id = supply.sr_instance_id
AND supply.source_organization_id = supply.organization_id
)
OR
(supply.order_type in (5, 17)
AND supply.source_sr_instance_id is null
AND supply.source_supplier_id is null
AND supply.planning_make_buy_code = 1
)
)
and time.report_date between bucket.bkt_start_date and bucket.bkt_end_date
and time.report_date between bl.from_date and nvl(bl.to_date, bl.cutoff_date)
group by
supply.organization_id,
bl.baseline_id,
bl.compile_designator,
bl.organization_id,
supply.sr_inventory_item_id,
trunc(time.report_date),
supply.uom_code
';
EXECUTE IMMEDIATE l_stmt USING g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by, g_last_update_login, g_sysdate;
BIS_COLLECTION_UTILITIES.put_line('Fail to insert new data into temporary regular table.');
select SUM(decode(sign(sched.from_date - def.last_from_date), -1, 1, 0)) del_cnt,
SUM(decode(sign(sched.from_date - def.last_from_date), 1, 1, 0)) arv_cnt
into l_delete, l_archive
from OPI_DBI_BASELINE_DEFINITIONS def,
OPI_DBI_BASELINE_SCHEDULES sched
where sched.next_collection_date <= g_sysdate
and sched.schedule_type = 1
and sched.baseline_id = def.baseline_id
;
IF l_delete > 0 THEN
BIS_COLLECTION_UTILITIES.put_line('Clean up data in baseline fact table.');
delete from OPI_DBI_PTP_PLAN_F
where
rowid in
(select f.rowid
from OPI_DBI_PTP_PLAN_F f,
OPI_DBI_BASELINE_SCHEDULES sched
where sched.next_collection_date <= g_sysdate
and sched.schedule_type = 1
and f.baseline_id = sched.baseline_id
and f.transaction_date >= sched.from_date
);
BIS_COLLECTION_UTILITIES.put_line('Fail to delete old data from baseline fact table.');
insert /*+ append parallel(OPI_DBI_PTP_PLAN_F) */
into OPI_DBI_PTP_PLAN_F
(
ORGANIZATION_ID,
BASELINE_ID,
PLAN_NAME,
OWNING_ORG_ID,
INVENTORY_ITEM_ID,
TRANSACTION_DATE,
PLANNED_QUANTITY,
UOM_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
select /*+ use_hash(day,stg) parallel(stg) parallel(day) */
stg.ORGANIZATION_ID,
stg.BASELINE_ID,
stg.PLAN_NAME,
stg.OWNING_ORG_ID,
stg.INVENTORY_ITEM_ID,
day.report_date,
stg.PLANNED_QUANTITY,
stg.UOM_CODE,
g_sysdate,
g_last_updated_by,
g_sysdate,
g_last_updated_by,
g_last_update_login
from OPI_PTP_SUM_STG_MV stg,
OPI_DBI_BASELINE_SCHEDULES sched,
fii_time_day day
where sched.next_collection_date <= g_sysdate
and sched.schedule_type = 1
and stg.baseline_id = sched.baseline_id
and stg.item_cat_flag = 0
and stg.period_type_id = 1
and stg.day_id = day.report_date_julian
and day.report_date < sched.from_date
;
BIS_COLLECTION_UTILITIES.put_line('Archive collection history into log table, update baseline setup tables.');
insert into OPI_DBI_PTP_LOG
(
BASELINE_ID,
BASELINE_NAME,
PLAN_NAME,
OWNING_ORG_ID,
ORGANIZATION_ID,
FROM_DATE,
TO_DATE,
COLLECTED_DATE,
PLAN_RUN_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
select def.BASELINE_ID,
def.BASELINE_NAME,
setup.PLAN_NAME,
setup.OWNING_ORG_ID,
orgs.ORGANIZATION_ID,
sched.FROM_DATE,
nvl(sched.TO_DATE, plan.cutoff_date),
g_sysdate COLLECTED_DATE,
plan.data_start_date PLAN_RUN_DATE,
g_sysdate,
g_last_updated_by,
g_sysdate,
g_last_updated_by,
g_last_update_login
from OPI_DBI_BASELINE_DEFINITIONS def,
OPI_DBI_BASELINE_SCHEDULES sched,
OPI_DBI_BASELINE_PLANS setup,
ISC_DBI_PLANS plan,
ISC_DBI_PLAN_ORGANIZATIONS orgs
where def.baseline_id = sched.baseline_id
and sched.next_collection_date <= g_sysdate
and sched.schedule_type = 1
and sched.baseline_id = setup.baseline_id
and setup.plan_name = plan.compile_designator
and setup.owning_org_id = plan.organization_id
and plan.plan_id = orgs.plan_id
;
update OPI_DBI_BASELINE_DEFINITIONS def
set (data_start_date, last_collected_date, last_from_date, last_to_date, last_update_date, last_updated_by, last_update_login)
=
(select nvl(def.data_start_date, sched.FROM_DATE),
g_sysdate,
sched.FROM_DATE,
min(nvl(sched.TO_DATE, plan.cutoff_date)),
g_sysdate,
g_last_updated_by,
g_last_update_login
from OPI_DBI_BASELINE_SCHEDULES sched,
OPI_DBI_BASELINE_PLANS setup,
ISC_DBI_PLANS plan
where def.baseline_id = sched.baseline_id
and sched.baseline_id = setup.baseline_id
and setup.plan_name = plan.compile_designator
and setup.owning_org_id = plan.organization_id
group by def.data_start_date, sched.FROM_DATE
)
where def.baseline_id in
(select baseline_id from opi_dbi_baseline_schedules
where next_collection_date <= g_sysdate
and schedule_type = 1
)
;
update OPI_DBI_PTP_COST cost
set to_date = l_baseline_record.from_date
where cost.organization_id in
(select orgs.organization_id
from OPI_DBI_BASELINE_PLANS setup,
ISC_DBI_PLANS plan,
ISC_DBI_PLAN_ORGANIZATIONS orgs
where setup.baseline_id = l_baseline_record.baseline_id
and setup.plan_name = plan.compile_designator
and setup.owning_org_id = plan.organization_id
and plan.plan_id = orgs.plan_id
)
and cost.to_date = l_baseline_record.data_start_date
;
update OPI_DBI_PTP_COST cost
set from_date = l_baseline_record.from_date
where cost.organization_id in
(select orgs.organization_id
from OPI_DBI_BASELINE_PLANS setup,
ISC_DBI_PLANS plan,
ISC_DBI_PLAN_ORGANIZATIONS orgs
where setup.baseline_id = l_baseline_record.baseline_id
and setup.plan_name = plan.compile_designator
and setup.owning_org_id = plan.organization_id
and plan.plan_id = orgs.plan_id
)
and cost.from_date = l_baseline_record.data_start_date
;
update OPI_DBI_PTP_CONV conv
set to_date = l_baseline_record.from_date
where conv.organization_id in
(select orgs.organization_id
from OPI_DBI_BASELINE_PLANS setup,
ISC_DBI_PLANS plan,
ISC_DBI_PLAN_ORGANIZATIONS orgs
where setup.baseline_id = l_baseline_record.baseline_id
and setup.plan_name = plan.compile_designator
and setup.owning_org_id = plan.organization_id
and plan.plan_id = orgs.plan_id
)
and conv.to_date = l_baseline_record.data_start_date
;
update OPI_DBI_PTP_CONV conv
set from_date = l_baseline_record.from_date
where conv.organization_id in
(select orgs.organization_id
from OPI_DBI_BASELINE_PLANS setup,
ISC_DBI_PLANS plan,
ISC_DBI_PLAN_ORGANIZATIONS orgs
where setup.baseline_id = l_baseline_record.baseline_id
and setup.plan_name = plan.compile_designator
and setup.owning_org_id = plan.organization_id
and plan.plan_id = orgs.plan_id
)
and conv.from_date = l_baseline_record.data_start_date
;
update OPI_DBI_BASELINE_DEFINITIONS def
set data_start_date = last_from_date
where baseline_id = l_baseline_record.baseline_id
;
update OPI_DBI_BASELINE_SCHEDULES
set from_date = null,
to_date = null,
schedule_type = null,
next_collection_date = null
where baseline_id = l_baseline_record.baseline_id
;
RAISE update_log_exception;
update opi_dbi_run_log_curr
set stop_reason_code = 2
where etl_id = 7 and source = 1;
update opi_dbi_run_log_curr
set stop_reason_code = 1
where etl_id = 7 and source = 1;
WHEN load_exception or archive_cleanup_exception or update_log_exception or cost_conversion_rate_exception THEN
ROLLBACK;