[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select plan_type, sr_instance_id, plan_start_date, plan_cutoff_date, temp_transfer_id
into l_plan_type, l_sr_instance_id, l_plan_start_date, l_plan_cutoff_date, l_transfer_id
from msc_plan_runs
where plan_id=p_plan_id
and plan_run_id=p_plan_run_id;
insert /*+ append nologging */ into msc_st_suppliers_f (
st_transaction_id,
error_code,
sr_instance_id,
organization_id,
inventory_item_id,
supplier_id,
supplier_site_id,
analysis_date,
required_qty,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
select
l_transfer_id,
to_number(1),
f.sr_instance_id,
f.organization_id,
f.inventory_item_id,
f.supplier_id,
f.supplier_site_id,
d.date2 analysis_date,
sum(f.required_qty) required_qty,
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id,
fnd_global.conc_program_id, fnd_global.conc_login_id,
fnd_global.prog_appl_id, fnd_global.conc_request_id
from
(select
msr.sr_instance_id,
msr.organization_id,
msr.inventory_item_id,
msr.supplier_id,
nvl(msr.supplier_site_id, -23453) supplier_site_id,
trunc(msr.consumption_date) analysis_date,
msr.consumed_quantity required_qty -- ignore overloaded_capacity
from msc_supplier_requirements msr
where l_plan_type not in (6)
and msr.plan_id=p_plan_id
and l_plan_constrained=SYS_YES
union all
select
mbid.sr_instance_id,
mbid.organization_id,
mbid.inventory_item_id,
mbid.supplier_id,
nvl(mbid.supplier_site_id, -23453) supplier_site_id,
trunc(mbid.detail_date) analysis_date,
mbid.supplier_usage required_qty
from msc_bis_inv_detail mbid
where l_plan_type in (6)
and mbid.plan_id=p_plan_id
and mbid.supplier_id is not null
union all
select
ms.sr_instance_id,
ms.organization_id,
ms.inventory_item_id,
nvl(case when ms.order_type in (5,51) then ms.source_supplier_id
else ms.supplier_id end, -23453) supplier_id,
nvl(case when ms.order_type in (5,51) then ms.source_supplier_site_id
else ms.supplier_site_id end, -23453) supplier_site_id,
trunc(case when l_plan_type in (101) then ms.new_schedule_date
else nvl(ms.new_order_placement_date, nvl(ms.firm_date,ms.new_schedule_date)) end)
analysis_date,
sum(ms.new_order_quantity) required_qty
from msc_supplies ms
where l_plan_type not in (6)
and ms.plan_id = p_plan_id
and nvl(ms.disposition_status_type,1)=1
and (l_plan_type in (4,5) or l_plan_constrained=2)
and ms.supplier_id is not null
and ms.order_type in (1,2,5,51,76)
group by
ms.sr_instance_id,
ms.organization_id,
ms.inventory_item_id,
nvl(case when ms.order_type in (5,51) then ms.source_supplier_id
else ms.supplier_id end, -23453),
nvl(case when ms.order_type in (5,51) then ms.source_supplier_site_id
else ms.supplier_site_id end, -23453),
trunc(case when l_plan_type in (101) then ms.new_schedule_date
else nvl(ms.new_order_placement_date, nvl(ms.firm_date,ms.new_schedule_date)) end)
) f,
msc_hub_query d
where d.query_id=l_qid_last_date
and f.analysis_date between d.date1 and d.date2
group by
f.sr_instance_id,
f.organization_id,
f.inventory_item_id,
f.supplier_id,
f.supplier_site_id,
d.date2;
msc_phub_util.log('insert into msc_st_suppliers_f:required: '||sql%rowcount);
select msc_hub_query_s.nextval into l_qid_orgs from dual;
insert /*+ append nologging */ into msc_hub_query (
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
number3, -- sr_instance_id
number4 -- organization_id
)
select distinct l_qid_orgs,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
f.sr_instance_id,
f.organization_id
from msc_st_suppliers_f f
where f.st_transaction_id=l_transfer_id
and f.error_code in (1);
insert /*+ append nologging */ into msc_hub_query (
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
number3, -- sr_instance_id
number4 -- organization_id
)
select distinct l_qid_orgs,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_sr_instance_id,
-23453
from dual;
insert /*+ append nologging */ into msc_st_suppliers_f (
st_transaction_id,
error_code,
inventory_item_id,
supplier_id,
supplier_site_id,
analysis_date,
avail_qty,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
select
l_transfer_id,
to_number(2),
f.inventory_item_id,
f.supplier_id,
f.supplier_site_id,
d.date2 analysis_date,
sum(f.avail_qty) avail_qty,
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id,
fnd_global.conc_program_id, fnd_global.conc_login_id,
fnd_global.prog_appl_id, fnd_global.conc_request_id
from
(select distinct
mscp.inventory_item_id,
mscp.supplier_id,
nvl(mscp.supplier_site_id, -23453) supplier_site_id,
trunc(mcd.calendar_date) analysis_date,
nvl(mscp.capacity, 1e20) avail_qty
from
msc_supplier_capacities mscp,
msc_calendar_dates mcd,
msc_trading_partners mtp,
msc_item_suppliers mis
where l_plan_type not in (6)
and mscp.capacity > 0
and mis.plan_id=mscp.plan_id
and mis.supplier_id=mscp.supplier_id
and mis.supplier_site_id=mscp.supplier_site_id
and mis.organization_id=mscp.organization_id
and mis.inventory_item_id=mscp.inventory_item_id
and mis.sr_instance_id=mscp.sr_instance_id
and mtp.sr_tp_id=mscp.organization_id
and mtp.sr_instance_id=mscp.sr_instance_id
and mtp.partner_type=3
and mcd.calendar_date between trunc(mscp.from_date) and trunc(nvl(mscp.to_date,l_plan_cutoff_date))
and mcd.calendar_date between decode(l_plan_type, 4, trunc(l_plan_start_date),
nvl(trunc(mis.supplier_lead_time_date+1),trunc(l_plan_start_date)))
and trunc(l_plan_cutoff_date)
and (((mis.delivery_calendar_code is not null and mcd.seq_num is not null)
or (mis.delivery_calendar_code is null and l_plan_type <> 4))
or (l_plan_type=4 and mcd.seq_num is not null))
and mcd.calendar_code=nvl(mis.delivery_calendar_code,mtp.calendar_code)
and mcd.exception_set_id=mtp.calendar_exception_set_id
and mcd.sr_instance_id=mtp.sr_instance_id
and mscp.plan_id=p_plan_id
union all
select
mbid.inventory_item_id,
mbid.supplier_id,
nvl(mbid.supplier_site_id, -23453) supplier_site_id,
trunc(mbid.detail_date) analysis_date,
first_value(mbid.supplier_capacity) over(
partition by mbid.inventory_item_id, mbid.supplier_id,
nvl(mbid.supplier_site_id, -23453),
trunc(mbid.detail_date)) avail_qty
from msc_bis_inv_detail mbid
where l_plan_type in (6)
and mbid.plan_id=p_plan_id
and mbid.supplier_id is not null
) f,
msc_hub_query d
where d.query_id=l_qid_last_date
and f.analysis_date between d.date1 and d.date2
group by
f.inventory_item_id,
f.supplier_id,
f.supplier_site_id,
d.date2;
msc_phub_util.log('insert into msc_st_suppliers_f:available: '||sql%rowcount);
insert /*+ append nologging */ into msc_st_suppliers_f (
st_transaction_id,
error_code,
inventory_item_id,
supplier_id,
supplier_site_id,
analysis_date,
avail_qty,
net_avail_qty,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
select
l_transfer_id,
to_number(3),
f.inventory_item_id,
f.supplier_id,
f.supplier_site_id,
f.analysis_date,
sum(avail_qty) avail_qty,
sum(nvl(f.avail_qty,0) - nvl(f.required_qty,0)) net_avail_qty,
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id,
fnd_global.conc_program_id, fnd_global.conc_login_id,
fnd_global.prog_appl_id, fnd_global.conc_request_id
from
(select
f.inventory_item_id,
f.supplier_id,
f.supplier_site_id,
f.analysis_date,
f.avail_qty,
f.required_qty
from msc_st_suppliers_f f
where f.st_transaction_id=l_transfer_id
and f.error_code in (1)
and exists (
select 1
from msc_st_suppliers_f f2
where f2.error_code in (2)
and f.inventory_item_id=f2.inventory_item_id
and f.supplier_id=f2.supplier_id
and f.supplier_site_id=f2.supplier_site_id)
union all
select
f.inventory_item_id,
f.supplier_id,
f.supplier_site_id,
f.analysis_date,
f.avail_qty,
f.required_qty
from msc_st_suppliers_f f
where f.st_transaction_id=l_transfer_id
and f.error_code in (2)
) f
group by
f.inventory_item_id,
f.supplier_id,
f.supplier_site_id,
f.analysis_date;
msc_phub_util.log('insert into msc_st_suppliers_f:net: '||sql%rowcount);
insert /*+ append nologging */ into msc_st_suppliers_f (
st_transaction_id,
error_code,
sr_instance_id,
organization_id,
inventory_item_id,
supplier_id,
supplier_site_id,
analysis_date,
required_qty,
avail_qty,
net_avail_qty,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
select
l_transfer_id,
to_number(4),
f.sr_instance_id,
f.organization_id,
f.inventory_item_id,
f.supplier_id,
f.supplier_site_id,
f.analysis_date,
sum(f.required_qty) required_qty,
sum(f.avail_qty) avail_qty,
sum(f.net_avail_qty) net_avail_qty,
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id,
fnd_global.conc_program_id, fnd_global.conc_login_id,
fnd_global.prog_appl_id, fnd_global.conc_request_id
from
(select
f.sr_instance_id,
f.organization_id,
f.inventory_item_id,
f.supplier_id,
f.supplier_site_id,
f.analysis_date,
f.required_qty,
to_number(null) avail_qty,
to_number(null) net_avail_qty
from msc_st_suppliers_f f
where f.st_transaction_id=l_transfer_id
and f.error_code in (1)
union all
select
o.number3 sr_instance_id,
o.number4 organization_id,
f.inventory_item_id,
f.supplier_id,
f.supplier_site_id,
f.analysis_date,
to_number(null) required_qty,
f.avail_qty avail_qty,
(case when nvl(f.net_avail_qty,0) < 0 then 0 else f.net_avail_qty end) net_avail_qty
from
msc_st_suppliers_f f,
msc_hub_query o
where f.st_transaction_id=l_transfer_id
and f.error_code in (3)
and o.query_id=l_qid_orgs
) f
group by
f.sr_instance_id,
f.organization_id,
f.inventory_item_id,
f.supplier_id,
f.supplier_site_id,
f.analysis_date;
msc_phub_util.log('insert into msc_st_suppliers_f:distributed: '||sql%rowcount);
insert /*+ append nologging */ into msc_st_suppliers_f (
st_transaction_id,
error_code,
sr_instance_id,
organization_id,
inventory_item_id,
supplier_id,
supplier_site_id,
analysis_date,
net_avail_qty_cum,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
select
l_transfer_id,
to_number(5),
f.sr_instance_id,
f.organization_id,
f.inventory_item_id,
f.supplier_id,
f.supplier_site_id,
d.date2 analysis_date,
sum(f.net_avail_qty) net_avail_qty_cum,
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id,
fnd_global.conc_program_id, fnd_global.conc_login_id,
fnd_global.prog_appl_id, fnd_global.conc_request_id
from
msc_st_suppliers_f f,
msc_hub_query d
where f.st_transaction_id=l_transfer_id
and f.error_code in (4)
and d.query_id=l_qid_last_date
and f.analysis_date<=d.date2
group by
f.sr_instance_id,
f.organization_id,
f.inventory_item_id,
f.supplier_id,
f.supplier_site_id,
d.date2;
msc_phub_util.log('insert into msc_st_suppliers_f:cum: '||sql%rowcount);
insert into msc_suppliers_f (
plan_id,
plan_run_id,
sr_instance_id,
organization_id,
owning_inst_id,
owning_org_id,
inventory_item_id,
supplier_id,
supplier_site_id,
analysis_date,
aggr_type,
category_set_id,
sr_category_id,
required_qty,
avail_qty,
net_avail_qty,
net_avail_qty_cum,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
select
p_plan_id,
p_plan_run_id,
f.sr_instance_id,
f.organization_id,
f.sr_instance_id owning_inst_id,
msc_hub_calendar.get_item_org(p_plan_id,
f.inventory_item_id, f.sr_instance_id, f.organization_id) owning_org_id,
f.inventory_item_id,
f.supplier_id,
f.supplier_site_id,
f.analysis_date,
to_number(0) aggr_type,
to_number(-23453) category_set_id,
to_number(-23453) sr_category_id,
sum(f.required_qty) required_qty,
sum(f.avail_qty) avail_qty,
sum(f.net_avail_qty) net_avail_qty,
sum(f.net_avail_qty_cum) net_avail_qty_cum,
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id,
fnd_global.conc_program_id, fnd_global.conc_login_id,
fnd_global.prog_appl_id, fnd_global.conc_request_id
from msc_st_suppliers_f f
where f.st_transaction_id=l_transfer_id
and f.error_code in (4,5)
group by
f.sr_instance_id,
f.organization_id,
f.inventory_item_id,
f.supplier_id,
f.supplier_site_id,
f.analysis_date;
msc_phub_util.log('insert into msc_suppliers_f:final: '||sql%rowcount);
delete from msc_suppliers_f
where plan_id=p_plan_id and plan_run_id=p_plan_run_id and aggr_type>0;
msc_phub_util.log('msc_supplier_pkg.summarize_suppliers_f, delete='||sql%rowcount);
insert into msc_suppliers_f (
plan_id, plan_run_id,
sr_instance_id, organization_id,
owning_inst_id, owning_org_id, inventory_item_id,
supplier_id, supplier_site_id,
analysis_date,
aggr_type, category_set_id, sr_category_id,
required_qty,
avail_qty,
net_avail_qty,
net_avail_qty_cum,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
-- category (42, 43, 44)
select
f.plan_id, f.plan_run_id,
f.sr_instance_id, f.organization_id,
f.owning_inst_id, f.owning_org_id, to_number(-23453) inventory_item_id,
f.supplier_id, f.supplier_site_id,
f.analysis_date,
to_number(42) aggr_type,
l_category_set_id1 category_set_id,
nvl(q.sr_category_id, -23453),
sum(f.required_qty),
sum(f.avail_qty),
sum(f.net_avail_qty),
sum(f.net_avail_qty_cum),
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id,
fnd_global.conc_program_id, fnd_global.conc_login_id,
fnd_global.prog_appl_id, fnd_global.conc_request_id
from
msc_suppliers_f f,
msc_phub_item_categories_mv q
where f.plan_id=p_plan_id and f.plan_run_id=p_plan_run_id
and f.aggr_type=0
and f.owning_inst_id=q.sr_instance_id(+)
and f.owning_org_id=q.organization_id(+)
and f.inventory_item_id=q.inventory_item_id(+)
and q.category_set_id(+)=l_category_set_id1
group by
f.plan_id, f.plan_run_id,
f.sr_instance_id, f.organization_id,
f.owning_inst_id, f.owning_org_id,
f.supplier_id, f.supplier_site_id,
f.analysis_date,
nvl(q.sr_category_id, -23453);
delete from msc_st_suppliers_f where st_transaction_id=p_st_transaction_id;
' insert into msc_st_suppliers_f('||
' st_transaction_id,'||
' error_code,'||
' sr_instance_id,'||
' organization_id,'||
' owning_inst_id,'||
' owning_org_id,'||
' inventory_item_id,'||
' supplier_id,'||
' supplier_site_id,'||
' organization_code,'||
' owning_org_code,'||
' item_name,'||
' supplier_name,'||
' supplier_site_code,'||
' analysis_date,'||
' required_qty,'||
' avail_qty,'||
' net_avail_qty,'||
' net_avail_qty_cum,'||
' created_by, creation_date,'||
' last_updated_by, last_update_date, last_update_login'||
' )'||
' select'||
' :p_st_transaction_id,'||
' 0,'||
' f.sr_instance_id,'||
' f.organization_id,'||
' f.owning_inst_id,'||
' f.owning_org_id,'||
' f.inventory_item_id,'||
' f.supplier_id,'||
' f.supplier_site_id,'||
' mtp.organization_code,'||
' mtp2.organization_code,'||
' mi.item_name,'||
' decode(f.supplier_id, -23453, null, smv.supplier_name),'||
' decode(f.supplier_site_id, -23453, null, smv.supplier_site_code),'||
' f.analysis_date,'||
' f.required_qty,'||
' f.avail_qty,'||
' f.net_avail_qty,'||
' f.net_avail_qty_cum,'||
' fnd_global.user_id, sysdate,'||
' fnd_global.user_id, sysdate, fnd_global.login_id'||
' from'||
' '||l_apps_schema||'.msc_suppliers_f'||l_suffix||' f,'||
' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp,'||
' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp2,'||
' '||l_apps_schema||'.msc_items'||l_suffix||' mi,'||
' '||l_apps_schema||'.msc_phub_suppliers_mv'||l_suffix||' smv'||
' where f.plan_run_id=:p_plan_run_id'||
' and f.aggr_type=0'||
' and mtp.partner_type(+)=3'||
' and mtp.sr_instance_id(+)=f.sr_instance_id'||
' and mtp.sr_tp_id(+)=f.organization_id'||
' and mtp2.partner_type(+)=3'||
' and mtp2.sr_instance_id(+)=f.owning_inst_id'||
' and mtp2.sr_tp_id(+)=f.owning_org_id'||
' and mi.inventory_item_id(+)=f.inventory_item_id'||
' and smv.supplier_id(+)=f.supplier_id'||
' and smv.supplier_site_id(+)=f.supplier_site_id';
msc_phub_util.log('msc_supplier_pkg.import_suppliers_f: insert into msc_suppliers_f');
insert into msc_suppliers_f (
plan_id,
plan_run_id,
sr_instance_id,
organization_id,
owning_inst_id,
owning_org_id,
inventory_item_id,
supplier_id,
supplier_site_id,
analysis_date,
required_qty,
avail_qty,
net_avail_qty,
net_avail_qty_cum,
aggr_type, category_set_id, sr_category_id,
created_by, creation_date,
last_updated_by, last_update_date, last_update_login
)
select
p_plan_id,
p_plan_run_id,
nvl(sr_instance_id, -23453),
nvl(organization_id, -23453),
nvl(owning_inst_id, -23453),
nvl(owning_org_id, -23453),
nvl(inventory_item_id, -23453),
nvl(supplier_id, -23453),
nvl(supplier_site_id, -23453),
analysis_date,
required_qty,
avail_qty,
net_avail_qty,
net_avail_qty_cum,
0, -23453, -23453,
fnd_global.user_id, sysdate,
fnd_global.user_id, sysdate, fnd_global.login_id
from msc_st_suppliers_f
where st_transaction_id=p_st_transaction_id and error_code=0;
msc_phub_util.log('msc_supplier_pkg.import_suppliers_f: inserted='||sql%rowcount);