[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select msc_hub_query_s.nextval into l_qid_req from dual;
insert into msc_hub_query(
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
number1, -- plan_id
number2, -- plan_run_id
number3, -- sr_instance_id
number4, -- organization_id
char1, -- currency_code
number5, -- supplier_id
number6, -- supplier_site_id
number7, -- region_id
number8, -- supplier_site_id
date1, -- analysis_date
number10, -- required_qty
number11, -- po_reschedule_count
number12, -- po_count
number13, -- po_cancel_count
number14, -- buy_order_value
number15, -- buy_order_value2
number16 -- buy_order_count
)
select
l_qid_req, sysdate, 1, sysdate, 1, 1,
p_plan_id,
p_plan_run_id,
t.sr_instance_id,
t.organization_id,
t.currency_code,
t.supplier_id,
t.supplier_site_id,
mps.region_id,
t.inventory_item_id,
t.analysis_date,
sum(t.required_qty) required_qty,
sum(t.po_reschedule_count) po_reschedule_count,
sum(t.po_count) po_count,
sum(t.po_cancel_count) po_cancel_count,
sum(t.buy_order_value) buy_order_value,
sum(t.buy_order_value * decode(t.currency_code,
fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate,0))) buy_order_value2,
sum(buy_order_count) buy_order_count
from (
select
msr.sr_instance_id sr_instance_id,
msr.organization_id organization_id,
nvl(mtp.currency_code, 'XXX') currency_code,
msr.supplier_id supplier_id,
nvl(msr.supplier_site_id, -23453) supplier_site_id,
to_number(-23453) region_id,
msr.inventory_item_id inventory_item_id,
trunc(msr.consumption_date) analysis_date,
sum(msr.consumed_quantity+msr.overloaded_capacity) required_qty,
to_number(null) po_reschedule_count,
to_number(null) po_count,
to_number(null) po_cancel_count,
to_number(null) buy_order_value,
to_number(null) buy_order_count
from msc_supplier_requirements msr,
msc_trading_partners mtp
where msr.plan_id = p_plan_id
and l_plan_constrained = SYS_YES
and msr.sr_instance_id = mtp.sr_instance_id
and msr.organization_id = mtp.sr_tp_id
and mtp.partner_type = 3
group by
msr.sr_instance_id,
msr.organization_id,
nvl(mtp.currency_code, 'XXX'),
msr.supplier_id,
nvl(msr.supplier_site_id,-23453),
msr.inventory_item_id,
trunc(msr.consumption_date)
union all
select
ms.sr_instance_id sr_instance_id,
ms.organization_id organization_id,
nvl(mtp.currency_code, 'XXX') currency_code,
decode(ms.order_type, PLANNED_ORDER, ms.source_supplier_id,
PLANNED_ARRIVAL, ms.source_supplier_id,
ms.supplier_id) supplier_id,
nvl(decode(ms.order_type, PLANNED_ORDER, ms.source_supplier_site_id,
PLANNED_ARRIVAL, ms.source_supplier_site_id,
ms.supplier_site_id), -23453) supplier_site_id,
to_number(-23453) region_id,
ms.inventory_item_id inventory_item_id,
-- SNO populates new_schedule_date
decode(l_plan_type, 6, trunc(ms.new_schedule_date), ms.new_order_placement_date) analysis_date,
sum(decode(mp.plan_type,
5, decode(nvl(ms.disposition_status_type,1),
1, ms.new_order_quantity,
0),
4, decode(nvl(ms.disposition_status_type,1),
1, ms.new_order_quantity,
0),
decode(nvl(ms.disposition_status_type,1),1,
decode(l_plan_constrained,2,ms.new_order_quantity,0),0)))required_qty,
sum(msc_supplier_pkg.is_rescheduled_po(ms.order_type, ms.reschedule_flag,
ms.new_schedule_date, ms.old_schedule_date)) po_rescheduled_count,
sum(decode(ms.order_type, 1, 1, 0)) po_count,
sum(msc_supplier_pkg.is_cancelled_po(ms.order_type,
ms.disposition_status_type)) po_cancel_count,
sum(msc_supplier_pkg.supplier_spend_value (ms.new_order_quantity,
nvl(ms.DELIVERY_PRICE,msi.list_price), ms.order_type)) buy_order_value,
sum(msc_supplier_pkg.is_new_buy_order(ms.order_type, l_plan_type, msi.purchasing_enabled_flag)) buy_order_count
from
MSC_SUPPLIES ms,
MSC_SYSTEM_ITEMS msi,
msc_plans mp,
msc_trading_partners mtp
where
mp.plan_id = p_plan_id
and l_plan_constrained = SYS_NO -- are we double counting constrained plan with previous?
and mp.plan_id = ms.plan_id
and ms.supplier_id is not null
and ms.plan_id = msi.plan_id
and ms.sr_instance_id = msi.sr_instance_id
and ms.organization_id = msi.organization_id
and ms.inventory_item_id = msi.inventory_item_id
and ms.order_type in (PLANNED_ORDER,PURCHASE_ORDER,PURCHASE_REQ,PLANNED_ARRIVAL,NEW_BUY_POS)
and ms.organization_id = mtp.sr_tp_id
and ms.sr_instance_id = mtp.sr_instance_id
and mtp.partner_type = 3
group by
ms.sr_instance_id,
ms.organization_id,
nvl(mtp.currency_code, 'XXX'),
decode(ms.order_type, PLANNED_ORDER, ms.source_supplier_id,
PLANNED_ARRIVAL, ms.source_supplier_id,
ms.supplier_id),
nvl(decode(ms.order_type, PLANNED_ORDER, ms.source_supplier_site_id,
PLANNED_ARRIVAL, ms.source_supplier_site_id,
ms.supplier_site_id), -23453),
ms.inventory_item_id,
decode(l_plan_type, 6, trunc(ms.new_schedule_date), ms.new_order_placement_date)
union all
select
mbid.sr_instance_id,
mbid.organization_id,
nvl(mtp.currency_code, 'XXX') currency_code,
mbid.supplier_id,
nvl(mbid.supplier_site_id, -23453) supplier_site_id,
nvl(mbid.zone_id, -23453) region_id,
mbid.inventory_item_id,
trunc(mbid.detail_date) analysis_date,
mbid.supplier_usage required_qty,
to_number(null) po_reschedule_count,
to_number(null) po_count,
to_number(null) po_cancel_count,
to_number(null) buy_order_value,
to_number(null) buy_order_count
from
msc_bis_inv_detail mbid,
msc_trading_partners mtp
where mbid.plan_id = p_plan_id
and mbid.supplier_id is not null
and mbid.organization_id = mtp.sr_tp_id
and mbid.sr_instance_id = mtp.sr_instance_id
and mtp.partner_type = 3
and l_plan_type = 6) t,
msc_currency_conv_mv mcc,
msc_phub_suppliers_mv mps
where mcc.from_currency(+) = t.currency_code
and mcc.to_currency(+) = fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
and mcc.calendar_date(+) = t.analysis_date
and mps.supplier_id = nvl(t.supplier_id, -23453)
and mps.supplier_site_id = nvl(t.supplier_site_id, -23453)
and mps.region_id = decode(nvl(t.supplier_site_id, -23453),
-23453, nvl(t.region_id, -23453), mps.region_id)
group by
t.sr_instance_id,
t.organization_id,
t.currency_code,
t.supplier_id,
t.supplier_site_id,
mps.region_id,
t.inventory_item_id,
t.analysis_date;
select msc_hub_query_s.nextval into l_qid_avail_org from dual;
insert into msc_hub_query(
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
number1, -- plan_id
number2, -- plan_run_id
number3, -- sr_instance_id
number4, -- organization_id
number5, -- supplier_id
number6, -- supplier_site_id
number7, -- region_id
number8, -- supplier_site_id
date1, -- analysis_date
number20 -- avail_qty
)
select
l_qid_avail_org, sysdate, 1, sysdate, 1, 1,
p_plan_id,
p_plan_run_id,
t.sr_instance_id,
t.organization_id,
t.supplier_id,
t.supplier_site_id,
mps.region_id,
t.inventory_item_id,
t.analysis_date,
sum(t.avail_qty)
from
(select
mscp.sr_instance_id sr_instance_id,
mscp.organization_id organization_id,
mscp.supplier_id supplier_id,
nvl(mscp.supplier_site_id, -23453) supplier_site_id,
to_number(-23453) region_id,
mscp.inventory_item_id inventory_item_id,
trunc(mcd.calendar_date) analysis_date,
to_number(null) required_qty,
nvl(mscp.capacity, 1e20) avail_qty
from
msc_supplier_capacities mscp,
msc_calendar_dates mcd,
msc_trading_partners mtp,
msc_item_suppliers mis,
msc_plans mp
where mp.plan_id = mscp.plan_id
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,mp.cutoff_date))
and mcd.calendar_date between decode(mp.plan_type, 4, trunc(mp.curr_start_date),
nvl(trunc(mis.supplier_lead_time_date+1),trunc(mp.curr_start_date)))
and trunc(mp.curr_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 mp.plan_type <> 4))
or (mp.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 mp.plan_id=p_plan_id
union all
select
mbid.sr_instance_id,
mbid.organization_id,
mbid.supplier_id,
nvl(mbid.supplier_site_id, -23453) supplier_site_id,
nvl(mbid.zone_id, -23453) region_id,
mbid.inventory_item_id,
trunc(mbid.detail_date) analysis_date,
to_number(null) required_qty,
mbid.supplier_capacity avail_qty
from
msc_bis_inv_detail mbid,
msc_trading_partners mtp
where mbid.plan_id = p_plan_id
and mbid.supplier_id is not null
and mbid.organization_id = mtp.sr_tp_id
and mbid.sr_instance_id = mtp.sr_instance_id
and mtp.partner_type = 3
and l_plan_type = 6) t,
msc_phub_suppliers_mv mps
where mps.supplier_id = nvl(t.supplier_id, -23453)
and mps.supplier_site_id = nvl(t.supplier_site_id, -23453)
and mps.region_id = decode(nvl(t.supplier_site_id, -23453),
-23453, nvl(t.region_id, -23453), mps.region_id)
group by
t.sr_instance_id,
t.organization_id,
t.supplier_id,
t.supplier_site_id,
mps.region_id,
t.inventory_item_id,
t.analysis_date;
select msc_hub_query_s.nextval into l_qid_avail_req from dual;
insert into msc_hub_query(
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
number1, -- plan_id
number2, -- plan_run_id
number3, -- sr_instance_id
number5, -- supplier_id
number6, -- supplier_site_id
number7, -- region_id
number8, -- supplier_site_id
date1, -- analysis_date
number10, -- required_qty
number20, -- avail_qty
number21 -- net_avail_qty
)
select
l_qid_avail_req, sysdate, 1, sysdate, 1, 1,
p_plan_id,
p_plan_run_id,
t.sr_instance_id,
t.supplier_id,
t.supplier_site_id,
t.region_id,
t.inventory_item_id,
t.analysis_date,
sum(t.required_qty),
sum(t.avail_qty),
sum(t.avail_qty) - sum(t.required_qty)
from
(select
number3 sr_instance_id,
number5 supplier_id,
number6 supplier_site_id,
number7 region_id,
number8 inventory_item_id,
date1 analysis_date,
sum(number10) required_qty,
to_number(null) avail_qty
from msc_hub_query
where query_id=l_qid_req
group by number3, number5, number6, number7, number8, date1
union all
select distinct
number3 sr_instance_id,
number5 supplier_id,
number6 supplier_site_id,
number7 region_id,
number8 inventory_item_id,
date1 analysis_date,
to_number(null) required_qty,
number20 avail_qty
from msc_hub_query
where query_id=l_qid_avail_org) t
group by
t.sr_instance_id,
t.supplier_id,
t.supplier_site_id,
t.region_id,
t.inventory_item_id,
t.analysis_date;
select msc_hub_query_s.nextval into l_qid_avail_cum from dual;
insert into msc_hub_query(
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
number1, -- plan_id
number2, -- plan_run_id
number3, -- sr_instance_id
number5, -- supplier_id
number6, -- supplier_site_id
number7, -- region_id
number8, -- supplier_site_id
date1, -- analysis_date
number10, -- required_qty
number20, -- avail_qty
number21, -- net_avail_qty
number22 -- net_avail_qty_cum
)
select
l_qid_avail_cum, sysdate, 1, sysdate, 1, 1,
p_plan_id,
p_plan_run_id,
k.sr_instance_id,
k.supplier_id,
k.supplier_site_id,
k.region_id,
k.inventory_item_id,
k.analysis_date,
f2.required_qty,
f2.avail_qty,
f2.net_avail_qty,
sum(f2.net_avail_qty) over(
partition by k.sr_instance_id, k.supplier_id, k.supplier_site_id, k.region_id, k.inventory_item_id
order by k.analysis_date) net_avail_qty_cum
from
(select
number3 sr_instance_id,
number5 supplier_id,
number6 supplier_site_id,
number7 region_id,
number8 inventory_item_id,
date1 analysis_date
from
(select distinct number3, number5, number6, number7, number8
from msc_hub_query where query_id=l_qid_avail_req),
(select distinct date1 from msc_hub_query where query_id=l_qid_avail_req)
) k,
(select
number3 sr_instance_id,
number5 supplier_id,
number6 supplier_site_id,
number7 region_id,
number8 inventory_item_id,
date1 analysis_date,
number10 required_qty,
number20 avail_qty,
number21 net_avail_qty
from msc_hub_query
where query_id=l_qid_avail_req) f2
where k.sr_instance_id = f2.sr_instance_id(+)
and k.supplier_id = f2.supplier_id(+)
and k.supplier_site_id = f2.supplier_site_id(+)
and k.region_id = f2.region_id(+)
and k.inventory_item_id = f2.inventory_item_id(+)
and k.analysis_date = f2.analysis_date(+);
insert into msc_suppliers_f (
plan_id,
plan_run_id,
sr_instance_id,
organization_id,
supplier_id,
supplier_site_id,
region_id,
inventory_item_id,
analysis_date,
aggr_type, category_set_id, sr_category_id,
required_qty,
avail_qty,
net_avail_qty,
net_avail_qty_cum,
po_reschedule_count,
po_count,
po_cancel_count,
buy_order_value,
buy_order_value2,
buy_order_count,
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,
f2.sr_instance_id,
f2.organization_id,
f2.supplier_id,
f2.supplier_site_id,
f2.region_id,
f2.inventory_item_id,
f2.analysis_date,
to_number(0) aggr_type,
to_number(-23453) category_set_id,
to_number(-23453) sr_category_id,
f1.required_qty,
f2.avail_qty,
f2.net_avail_qty,
f2.net_avail_qty_cum,
f1.po_reschedule_count,
f1.po_count,
f1.po_cancel_count,
f1.buy_order_value,
f1.buy_order_value2,
f1.buy_order_count,
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
number3 sr_instance_id,
number4 organization_id,
number5 supplier_id,
number6 supplier_site_id,
number7 region_id,
number8 inventory_item_id,
date1 analysis_date,
number10 required_qty,
number11 po_reschedule_count,
number12 po_count,
number13 po_cancel_count,
number14 buy_order_value,
number15 buy_order_value2,
number16 buy_order_count
from msc_hub_query
where query_id=l_qid_req) f1,
(select
number3 sr_instance_id,
number4 organization_id,
number5 supplier_id,
number6 supplier_site_id,
number7 region_id,
number8 inventory_item_id,
date1 analysis_date,
number20 avail_qty,
number21 net_avail_qty,
number22 net_avail_qty_cum
from
(select distinct number3, number5, number6, number7, number8, date1, number20, number21, number22
from msc_hub_query where query_id=l_qid_avail_cum),
(select distinct number4 from msc_hub_query where query_id in (l_qid_req, l_qid_avail_org))
) f2
where f2.sr_instance_id = f1.sr_instance_id(+)
and f2.organization_id = f1.organization_id(+)
and f2.supplier_id = f1.supplier_id(+)
and f2.supplier_site_id = f1.supplier_site_id(+)
and f2.region_id = f1.region_id(+)
and f2.inventory_item_id = f1.inventory_item_id(+)
and f2.analysis_date = f1.analysis_date(+);
insert into msc_suppliers_f (
plan_id, plan_run_id,
sr_instance_id, organization_id,
supplier_id, supplier_site_id, region_id,
inventory_item_id,
analysis_date,
aggr_type, category_set_id, sr_category_id,
required_qty,
avail_qty,
net_avail_qty,
net_avail_qty_cum,
po_reschedule_count,
po_count,
po_cancel_count,
buy_order_value,
buy_order_value2,
buy_order_count,
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.supplier_id, f.supplier_site_id, f.region_id,
to_number(-23453) inventory_item_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),
sum(f.po_reschedule_count),
sum(f.po_count),
sum(f.po_cancel_count),
sum(f.buy_order_value),
sum(f.buy_order_value2),
sum(f.buy_order_count),
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.sr_instance_id=q.sr_instance_id(+)
and f.organization_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.supplier_id, f.supplier_site_id, f.region_id,
f.analysis_date,
nvl(q.sr_category_id, -23453);
insert into msc_suppliers_f (
plan_id, plan_run_id,
sr_instance_id, organization_id,
supplier_id, supplier_site_id, region_id,
inventory_item_id,
analysis_date,
aggr_type, category_set_id, sr_category_id,
required_qty,
avail_qty,
net_avail_qty,
net_avail_qty_cum,
po_reschedule_count,
po_count,
po_cancel_count,
buy_order_value,
buy_order_value2,
buy_order_count,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
-- category-fiscal_period (1019, 1020, 1021)
select
t.plan_id, t.plan_run_id,
t.sr_instance_id, t.organization_id,
t.supplier_id, t.supplier_site_id, t.region_id,
t.inventory_item_id,
t.analysis_date,
t.aggr_type,
t.category_set_id, t.sr_category_id,
t.required_qty,
t.avail_qty,
t.net_avail_qty,
sum(t.net_avail_qty) over(
partition by t.plan_id, t.plan_run_id,
t.sr_instance_id, t.organization_id,
t.supplier_id, t.supplier_site_id, t.region_id,
t.inventory_item_id, t.aggr_type,
t.category_set_id, t.sr_category_id
order by t.analysis_date) net_avail_qty_cum,
t.po_reschedule_count,
t.po_count,
t.po_cancel_count,
t.buy_order_value,
t.buy_order_value2,
t.buy_order_count,
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.plan_id, f.plan_run_id,
f.sr_instance_id, f.organization_id,
f.supplier_id, f.supplier_site_id, f.region_id,
f.inventory_item_id,
fp.start_date analysis_date,
decode(f.aggr_type, 42, 1019, 43, 1020, 1021) aggr_type,
f.category_set_id, f.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.po_reschedule_count) po_reschedule_count,
sum(f.po_count) po_count,
sum(f.po_cancel_count) po_cancel_count,
sum(f.buy_order_value) buy_order_value,
sum(f.buy_order_value2) buy_order_value2,
sum(f.buy_order_count) buy_order_count
from
msc_suppliers_f f,
msc_phub_fiscal_periods_mv fp
where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
and f.aggr_type between 42 and 44
and f.analysis_date between fp.start_date and fp.end_date
group by
f.plan_id, f.plan_run_id,
f.sr_instance_id, f.organization_id,
f.supplier_id, f.supplier_site_id, f.region_id,
f.inventory_item_id,
fp.start_date,
decode(f.aggr_type, 42, 1019, 43, 1020, 1021),
f.category_set_id, f.sr_category_id) t;
delete from msc_suppliers_f
where plan_id = p_plan_id
and plan_run_id = nvl(p_plan_run_id,plan_run_id);