[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select sr_instance_id, organization_id
from msc_plan_organizations
where plan_id = p_plan_id;
select min(bkt_start_date), max(bkt_end_date)
into l_first_bkt_start_date, l_last_bkt_end_date
from msc_plan_buckets
where plan_id=p_plan_id;
select o.currency_code
into l_owning_currency_code
from msc_trading_partners o, msc_plans p
where o.sr_instance_id=p.sr_instance_id
and o.sr_tp_id=p.organization_id
and o.partner_type=3
and p.plan_id=p_plan_id;
select curr_cutoff_date - curr_start_date +1 into l_plan_days
from msc_plans where plan_id = p_plan_id;
select msc_hub_query_s.nextval into l_qid_vmi_item 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, -- inventory_item_id
number6 -- vmi flag
)
select
unique l_qid_vmi_item,l_sysdate,1,l_sysdate,1,1,
p_plan_id,
p_plan_run_id,
msi.sr_instance_id,
msi.organization_id,
msi.inventory_item_id,
nvl(mis.vmi_flag,0)
from msc_item_suppliers mis,
msc_system_items msi
where msi.plan_id = mis.plan_id
and msi.sr_instance_id = mis.sr_instance_id
and msi.organization_id = mis.organization_id
and msi.inventory_item_id = mis.inventory_item_id
and msi.plan_id = p_plan_id
and nvl(mis.vmi_flag,0)=1;
select msc_hub_query_s.nextval into l_qid_bucket 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
date1, -- bkt_start_date
date2, --- bkt_end_date
date3, --- last working day NOTE: for day bucket, this could be null
date4, -- safety_Stock period date in msc_safety_Stock table,
-- this may not at bucket_start date
date5, -- working day bkt start date
number10, -- bucket_type
number11 -- days in bucket
)
select
l_qid_bucket,l_sysdate,1,l_sysdate,1,1,
p_plan_id,
p_plan_run_id,
mpb.sr_instance_id,
mpb.organization_id,
mpb.bkt_start_date,
mpb.bkt_end_date,
decode(mpb.bucket_type,1,mpb.bkt_start_date,
msc_hub_calendar.last_work_date(p_plan_id,mpb.sr_instance_id,
mpb.bucket_type,mpb.bkt_start_date,
mpb.bkt_end_date) )last_work_date,
-- day bucket always has its self as last_work_date
-- no matter it is actually a working day or not
msc_hub_calendar.ss_date(p_plan_id,mpb.bkt_start_date,mpb.bkt_end_date) ss_date,
decode(mpb.bucket_type,1,
msc_hub_calendar.working_day_bkt_start_date(p_plan_id,
mpb.sr_instance_id,
mpb.bucket_type,
mpb.bkt_start_date,
mpb.bkt_end_date) ,
mpb.bkt_start_date) working_day_bkt_start_date,
mpb.bucket_type,
mpb.days_in_bkt
from msc_plan_buckets mpb,
msc_plans mp
where mpb.plan_id =p_plan_id
and mp.plan_id=mpb.plan_id
and mpb.sr_instance_id = mp.sr_instance_id
and mpb.organization_id = mp.organization_id
and mpb.curr_flag=1;
select msc_hub_query_s.nextval into l_qid_hub_week from dual;
insert into msc_hub_query (
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
date1 , -- hub week start date
date2 -- hub week end date
)
select unique l_qid_hub_week,
l_sysdate,1,l_sysdate,1,1,
trunc(mw.week_start_date),
trunc(mw.WEEK_END_DATE) -- need trunc since end_date is in time stamp 23:59:59
from msc_phub_mfg_cal_weeks_mv mw,
msc_plans mp
where mp.curr_start_date<=mw.week_end_date
and mp.curr_cutoff_date >=mw.week_start_date -- curr_cutoff_date is in 00:00:00,
-- cutoff date >=week_start_date instead of week_end date
-- this is important to include the last week where
--- week end date may > cutoff date
and mp.plan_id=p_plan_id;
select msc_hub_query_s.nextval into l_qid_bis_week from dual;
insert into msc_hub_query (
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
date1
)
select unique l_qid_bis_week,
l_sysdate,1,l_sysdate,1,1,
trunc(mbid.detail_date)
from msc_bis_inv_detail mbid, msc_plans mp
where mbid.plan_id=p_plan_id
and mbid.plan_id=mp.plan_id
and (nvl(mbid.detail_level,0)=1 or mp.plan_type=6)
and nvl(mbid.period_type,0)=1;
select msc_hub_query_s.nextval into l_qid_week_map from dual;
insert into msc_hub_query (
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
date1 , -- hub week start date
date2 , -- hub week end date
date3 -- bis week start date=msc_bis_inv_detail.detail_date
)
select unique l_qid_week_map,
l_sysdate,1,l_sysdate,1,1,
mfg.date1 hub_week_start_date,
mfg.date2 hub_week_end_date,
bis.date1
from msc_hub_query mfg,
msc_hub_query bis
where mfg.query_id=l_qid_hub_week
and bis.query_id=l_qid_bis_week
and bis.date1>=mfg.date1
and bis.date1<=mfg.date2;
select msc_hub_query_s.nextval into l_qid_last_date1 from dual;
insert into msc_hub_query (
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
date1
)
select unique l_qid_last_date1, l_sysdate, 1, l_sysdate, 1, 1, trunc(mcd.month_end_date)
from msc_calendar_dtl mcd
where mcd.month_end_date between l_first_bkt_start_date and l_last_bkt_end_date
union
select unique l_qid_last_date1, l_sysdate, 1, l_sysdate, 1, 1, trunc(mw.week_end_date)
from msc_phub_mfg_cal_weeks_mv mw
where mw.week_end_date between l_first_bkt_start_date and l_last_bkt_end_date
union
select unique l_qid_last_date1, l_sysdate, 1, l_sysdate, 1, 1, trunc(bp.end_date)
from msc_phub_fiscal_periods_mv bp
where bp.end_date between l_first_bkt_start_date and l_last_bkt_end_date
union
select l_qid_last_date1, l_sysdate, 1, l_sysdate, 1, 1, trunc(l_last_bkt_end_date)
from dual
order by 1;
select msc_hub_query_s.nextval into l_qid_last_date from dual;
insert into msc_hub_query (
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
date1, -- last_date
date2, -- bkt_start_date, for day bucket, it may not be a working day
date3 -- last_work_date,
)
select l_qid_last_date,
l_sysdate,1,l_sysdate,1,1,
f1.date1 last_date,
(select max(f2.date1) from msc_hub_query f2
where f2.date1<=f1.date1 and f2.query_id = l_qid_bucket) ,
(select max(f3.date3) from msc_hub_query f3
where f3.date3<=f1.date1 and f3.query_id =l_qid_bucket)
from msc_hub_query f1 where f1.query_id = l_qid_last_date1;
select msc_hub_query_s.nextval into l_qid_mil_item 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, -- org_id
number5, -- inventory_item_id
number6, -- vmi_flag
date1, -- bkt_start_date
date2, -- bkt end date,
date3, -- last work date
date4, -- ss date
date5, -- working day bkt start date
number10, -- bkt_type
number11 -- days in bucket
)
select
l_qid_mil_item, l_sysdate, 1, l_sysdate, 1, 1,
p_plan_id, p_plan_run_id,
t.sr_instance_id,
t.organization_id,
t.inventory_item_id,
t.vmi_flag,
f.date1, -- bkt_start_date,
f.date2, -- bkt_end_date
f.date3, -- last work date
f.date4,
f.date5,
f.number10, --bkt_type
f.number11 --days in bucket
from
(select distinct sr_instance_id, organization_id, inventory_item_id,
nvl(vmi_flag, 0) vmi_flag
from msc_demands_f
where plan_id=p_plan_id and plan_run_id=p_plan_run_id and aggr_type=0
and sr_instance_id=c.sr_instance_id and organization_id=c.organization_id
union
select distinct sr_instance_id, organization_id, inventory_item_id,
nvl(vmi_flag, 0) vmi_flag
from msc_supplies_f
where plan_id=p_plan_id and plan_run_id=p_plan_run_id and aggr_type=0
and sr_instance_id=c.sr_instance_id and organization_id=c.organization_id
union
select distinct mss.sr_instance_id, mss.organization_id, mss.inventory_item_id,
nvl(vmi.number6, 0) vmi_flag
from msc_safety_stocks mss, msc_hub_query vmi
where mss.plan_id=p_plan_id
and mss.plan_id=vmi.number1(+)
and mss.sr_instance_id=vmi.number3(+)
and mss.organization_id=vmi.number4(+)
and mss.inventory_item_id=vmi.number5(+)
and vmi.query_id(+)=l_qid_vmi_item
and mss.sr_instance_id=c.sr_instance_id
and mss.organization_id=c.organization_id) t,
(select distinct
t_bucket.date1, -- bkt_start_date,
t_bucket.date2, -- bkt_end_date
t_bucket.date3, -- last work date
t_bucket.date4,
t_bucket.date5,
t_bucket.number10, --bkt_type
t_bucket.number11 --days in bucket
from msc_hub_query t_bucket, msc_hub_query t_last_date
where t_bucket.query_id=l_qid_bucket
and t_last_date.query_id=l_qid_last_date
and t_bucket.date1=t_last_date.date2) f;
select msc_hub_query_s.nextval into l_qid_sd_item 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, -- org_id
number5, -- inventory_item_id
number6, -- vmi_flag
number7, -- owning_org_id
number8, -- owning_inst_id
date1, -- bkt_start_date
date2, -- bkt_end_date
date3 , -- activity date
number10, --bkt_type
number11 -- days_in_bkt
)
select
l_qid_sd_item,
l_sysdate,1,l_sysdate,1,1,
p_plan_id,
p_plan_run_id,
sd.sr_instance_id,
sd.organization_id,
sd.inventory_item_id,
sd.vmi_flag,
decode(sign(sd.organization_id), -1, msc_hub_calendar.get_item_org(p_plan_id, sd.inventory_item_id,
decode(sign(sd.sr_instance_id), -1, mp.sr_instance_id, sd.sr_instance_id)),
sd.organization_id),
decode(sign(sd.sr_instance_id), -1, mp.sr_instance_id, sd.sr_instance_id),
f.date1,
f.date2,
sd.activity_date,
f.number10,
f.number11
from msc_hub_query f,
(select unique
mdf.plan_id,
mdf.sr_instance_id ,
mdf.organization_id,
mdf.inventory_item_id,
nvl(mdf.vmi_flag,0) vmi_flag,
mdf.order_date activity_date
from msc_demands_f mdf
where mdf.plan_id= p_plan_id
and mdf.plan_run_id = p_plan_run_id
and mdf.aggr_type=0
union
select unique
msf.plan_id,
msf.sr_instance_id,
msf.organization_id,
msf.inventory_item_id,
nvl(msf.vmi_flag,0) vmi_flag,
msf.supply_date activity_date
from msc_supplies_f msf
where msf.plan_id = p_plan_id
and msf.plan_run_id = p_plan_run_id
and msf.aggr_type=0) sd,
msc_plans mp
where sd.activity_date = f.date3
and sd.plan_id = mp.plan_id
and f.query_id =l_qid_bucket;
select msc_hub_query_s.nextval into l_qid_pab_item 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, -- org_id
number5, -- inventory_item_id
number6, -- vmi_flag
number7, -- owning_org_id
number8, -- owning_inst_id
date3 -- activity_date
)
select
l_qid_pab_item,
l_sysdate,1,l_sysdate,1,1,
p_plan_id,
p_plan_run_id,
sd.sr_instance_id,
sd.organization_id,
sd.inventory_item_id,
sd.vmi_flag,
decode(sign(sd.organization_id), -1, msc_hub_calendar.get_item_org(p_plan_id, sd.inventory_item_id,
decode(sign(sd.sr_instance_id), -1, mp.sr_instance_id, sd.sr_instance_id)),
sd.organization_id),
decode(sign(sd.sr_instance_id), -1, mp.sr_instance_id, sd.sr_instance_id),
l.date3
from msc_hub_query l,
(select unique
mdf.plan_id,
mdf.sr_instance_id,
mdf.organization_id,
mdf.inventory_item_id,
nvl(mdf.vmi_flag,0) vmi_flag
from msc_demands_f mdf
where mdf.plan_id= p_plan_id
and mdf.plan_run_id = p_plan_run_id
and mdf.aggr_type=0
union
select unique
msf.plan_id,
msf.sr_instance_id,
msf.organization_id,
msf.inventory_item_id,
nvl(msf.vmi_flag,0) vmi_flag
from msc_supplies_f msf
where msf.plan_id = p_plan_id
and msf.plan_run_id = p_plan_run_id
and msf.aggr_type=0) sd,
msc_plans mp
where l.query_id = l_qid_last_date
and sd.plan_id = mp.plan_id
union
select l_qid_pab_item,
l_sysdate,1,l_sysdate,1,1,
p_plan_id,
p_plan_run_id,
f.number3,
f.number4,
f.number5,
f.number6,
f.number7,
f.number8,
f.date3 --- activity date
from msc_hub_query f where f.query_id=l_qid_sd_item;
select msc_hub_query_s.nextval into l_qid_pab 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, --- inventory_item_id
number6, -- vmi flag
date3 , -- last work date,
--------------------------------------------------
number10, -- pab
-----------------------------------------
number11, -- total demand
number12, -- total supply
number13, -- planned order qty
number14, -- indep_demand_qty
number15 , --- indep_demand_value
number16, -- total dep demand
number17, --sales order value
number18, -- return order value
number19, -- make order qty
number20, -- make order leadtime
number21, -- make order count
number23, -- item leadtime
number24, -- on hand
number25 , -- Scheduled_rept_qty
number22 , --- forecast qty,
number9 -- in drp, some supply (1,2,51) is also a demand
)
select
l_qid_pab,
l_sysdate,1,l_sysdate,1,1,
p_plan_id,
p_plan_run_id,
s.sr_instance_id,
s.organization_id,
s.inventory_item_id,
s.vmi_flag,
s.last_work_date,
------------------------------------------------
SUM(nvl(s.pab_supply ,0)- nvl(d.pab_demand ,0)-nvl(s.drp_supply_as_demand,0)) --- drp case
OVER (PARTITION BY s.plan_id,s.plan_run_id,
s.sr_instance_id,s.organization_id,s.inventory_item_id
ORDER by s.last_work_date) pab_qty ,
-------------------------------------------------------
d.total_demand,
s.total_supply,
s.planned_order_qty,
d.total_indep_Demand_qty,
---- make sure this indep_demand_value is qty * std_cost. this is used to calculate
---- cogs =item std cost x sum of Indep dem qty
---d.total_indep_demand_value, -- qty * std_Cost
d.total_indep_Demand_qty * msi.standard_cost,
d.total_dep_demand_qty,
d.sales_order_Qty * (nvl(msi.list_price,0) -(1-nvl(msi.AVERAGE_DISCOUNT,0)/100)) sales_order_value,
s.return_order_qty * (nvl(msi.list_price,0) -(1-nvl(msi.AVERAGE_DISCOUNT,0)/100)) return_order_value,
s.make_order_qty,
s.work_order_leadtime,
s.work_order_count,
msi.FIXED_LEAD_TIME ,
s.onhand_Qty,
s.Scheduled_rept_qty,
d.forecast_qty,
s.drp_supply_as_demand
from
(select mfq.number1 plan_id,
mfq.number2 plan_run_id,
mfq.number3 sr_instance_id,
mfq.number4 organization_id,
mfq.number5 inventory_item_id,
mfq.number6 vmi_flag , ---- nvl(msf.vmi_flag,0) vmi_flag,
mfq.number7 owning_org_id,
mfq.number8 owning_inst_id,
mfq.date3 last_work_date,
sum(decode(nvl(msf.supply_type,0),
4,0,
0,0,
nvl(msf.supply_qty,0))) pab_supply,
--- exclude onhand from total supply for drp
sum(decode(mps.plan_type,5,decode(msf.supply_type,18,0,nvl(msf.supply_qty,0)) ,
nvl(msf.supply_qty,0)) ) total_supply,
/* ms.source_organization_id <> ms.organization_id
and (ms.order_type <> PURCH_REQ or
(ms.order_type = PURCH_REQ and ms.supplier_id is not null))*/
sum(nvl(msf.drp_supply_as_demand,0)) drp_supply_as_demand,
sum(mfq.number11) days_in_bkt,
sum(decode(nvl(msf.supply_type,0),
5,nvl(msf.supply_qty,0),
0)) planned_order_qty,
sum(nvl(msf.work_order_Qty,0)) make_order_qty,
-- return order in srp is defined
-- as order_type in (1,2,18) and nvl(item_type_id,401) = 401 and nvl(item_type_value,1) = 2
sum(nvl(msf.return_order_qty,0)) return_order_qty,
sum(nvl(msf.work_order_leadtime,0)) work_order_leadtime,
sum(nvl(msf.work_order_count,0)) work_order_count,
sum(decode(nvl(msf.supply_type,0),
18, nvl(msf.supply_qty,0),
0)) onhand_qty,
sum(decode(nvl(msf.supply_type,0),
1,nvl(msf.supply_qty,0),
2,nvl(msf.supply_qty,0),
3,nvl(msf.supply_qty,0),
8,nvl(msf.supply_qty,0),
11,nvl(msf.supply_qty,0),
12,nvl(msf.supply_qty,0),
14,nvl(msf.supply_qty,0), --- bug 6797566 include work oder co/by product
0)) Scheduled_rept_qty
from msc_supplies_f msf,msc_hub_query mfq,msc_plans mps
where mfq.number1 = msf.plan_id(+)
and mfq.number2 = msf.plan_run_id(+)
and mfq.number3 = msf.sr_instance_id(+)
and mfq.number4 = msf.organization_id(+)
and mfq.number5 = msf.inventory_item_id(+)
and mfq.date3 = msf.supply_date(+)
and msf.aggr_type(+)=0
and mfq.query_id =l_qid_pab_item --- calculate at activity date and last work day
and mps.plan_id= mfq.number1
group by
mfq.number1,
mfq.number2,
mfq.number3,
mfq.number4,
mfq.number5,
mfq.number6,
mfq.number7,
mfq.number8,
mfq.date3
) s,
(select mfq1.number1 plan_id,
mfq1.number2 plan_run_id,
mfq1.number3 sr_instance_id,
mfq1.number4 organization_id,
mfq1.number5 inventory_item_id,
mfq1.number6 vmi_flag , ---- nvl(mdf.vmi_flag,0) vmi_flag,
mfq1.number7 owning_org_id,
mfq1.number8 owning_inst_id,
mfq1.date3 last_work_date,
sum( decode(mpd.plan_type,5,
decode(nvl(mdf.order_type,0),
0,0,
-1,0,
-29,decode(mfq1.number4,-23453,0,nvl(mdf.demand_qty,0)), -- exclude global f/c
-31,0,
nvl(mdf.demand_qty,0)),
decode(nvl(mdf.order_type,0),
0,0,
-5,0,
-22,0,
-29,decode(mfq1.number4,-23453,0,nvl(mdf.demand_qty,0)), -- exclude global f/c
-31,0, --exclude safety stock demand
nvl(mdf.demand_qty,0)))) pab_demand,
-- for drp plan
---
-- work order,INTER_ORG_DEMAND (based on order_date), exclude planned order
-- supply , exclude onhand(18)
--- supply type 1,2,51 also count as demand
-- decode(ms.order_type, PLANNED_ARRIVAL, PLANNED_SHIPMENT_OFF,
-- PURCHASE_ORDER,PLANNED_SHIPMENT_OFF,
-- PURCH_REQ,PLANNED_SHIPMENT_OFF)
--pab= total_suply+onhand-total_demand
sum(decode(mpd.plan_type,5,
decode(nvl(mdf.order_type,0),
0,0,
-1,0,
-29,decode(mfq1.number4,-23453,0,nvl(mdf.demand_qty,0)),
-31,0,
nvl(mdf.demand_qty,0)),
decode(nvl(mdf.order_type,0),
0,0,
-29,decode(mfq1.number4,-23453,0,nvl(mdf.demand_qty,0)),
-31,0,
nvl(mdf.demand_qty,0)))) total_demand,
sum(nvl(mdf.INDEP_DEMAND_QTY,0) ) total_indep_demand_qty,
---- make sure this indep_demand_value is qty * std_cost. this is used to calculate
---- cogs =item std cost x sum of Indep dem qty
--sum(nvl(INDEP_DEMAND_QTY,0) * msi.standard_cost) total_indep_demand_value,
sum(decode(nvl(mdf.order_type,0),
-1,decode(mpd.plan_type,5,0,nvl(mdf.demand_qty,0)), -- exclude drp planned demand from dep demand
-2,nvl(mdf.demand_qty,0),
-3,nvl(mdf.demand_qty,0),
-4,nvl(mdf.demand_qty,0),
-24,nvl(mdf.demand_qty,0),
-25,nvl(mdf.demand_qty,0),
0)) total_dep_demand_qty,
sum(decode(nvl(mdf.order_type,0),
-30,nvl(mdf.demand_qty,0),
0)) sales_order_Qty,
sum(decode(nvl(mdf.order_type,0),
-29,nvl(mdf.demand_qty,0),
0)) forecast_Qty
from msc_demands_f mdf,msc_hub_query mfq1,
msc_plans mpd
where mfq1.number1 = mdf.plan_id(+)
and mfq1.number2 = mdf.plan_run_id(+)
and mfq1.number3 = mdf.sr_instance_id(+)
and mfq1.number4 = mdf.organization_id(+)
and mfq1.number5 = mdf.inventory_item_id(+)
and mfq1.date3 = mdf.order_date(+)
and mdf.aggr_type(+)=0
and mfq1.query_id = l_qid_pab_item --- calculate at activity date and last work day
and mpd.plan_id=mfq1.number1 --- plan_id
group by
mfq1.number1,
mfq1.number2,
mfq1.number3,
mfq1.number4,
mfq1.number5,
mfq1.number6,
mfq1.number7,
mfq1.number8,
mfq1.date3
) d,
msc_system_items msi
where d.plan_id = s.plan_id
and d.plan_run_id = s.plan_run_id
and d.sr_instance_id = s.sr_instance_id
and d.organization_id = s.organization_id
and d.inventory_item_id = s.inventory_item_id
and d.last_work_date = s.last_work_date
and d.plan_id = msi.plan_id
and d.owning_inst_id = msi.sr_instance_id
and d.owning_org_id = msi.organization_id
and d.inventory_item_id = msi.inventory_item_id;
select msc_hub_query_s.nextval into l_qid_ss_item 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, -- org_id
number5, -- inventory_item_id
number6,
date1, ---- bkt start date,
date2, -- bkt end date,
date3, --- last work date,
date4, -- ss period
date5,
number10,
number11
)
select unique
l_qid_ss_item,
l_sysdate,1,l_sysdate,1,1,
p_plan_id,
p_plan_run_id,
mss.sr_instance_id,
mss.organization_id,
mss.inventory_item_id,
nvl(vmi.number6,0) vmi_flag,
f2.date1, -- bkt_start_date
f2.date2, -- bkt_end_date,
f2.date3,
f2.date4,
f2.date5,
f2.number10,
f2.number11
from msc_safety_stocks mss,
(select distinct
t_bucket.date1, -- bkt_start_date,
t_bucket.date2, -- bkt_end_date
t_bucket.date3, -- last work date
t_bucket.date4,
t_bucket.date5,
t_bucket.number10, --bkt_type
t_bucket.number11 --days in bucket
from msc_hub_query t_bucket, msc_hub_query t_last_date
where t_bucket.query_id=l_qid_bucket
and t_last_date.query_id=l_qid_last_date
and t_bucket.date1=t_last_date.date2) f2,
msc_hub_query vmi
where mss.plan_id =p_plan_id
and mss.plan_id = vmi.number1(+)
and mss.sr_instance_id = vmi.number3(+)
and mss.organization_id = vmi.number4(+)
and mss.inventory_item_id = vmi.number5(+)
and vmi.query_id (+) =l_qid_vmi_item;
select msc_hub_query_s.nextval into l_qid_ss 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, --- inventory_item_id
number6, -- vmi flag
date1,
date2,
date3,
number10, -- ss
number11 -- user enter ss
)
select
l_qid_ss,
l_sysdate,1,l_sysdate,1,1,
p_plan_id,
p_plan_run_id,
f.number3 sr_instance_id,
f.number4 organization_id,
f.number5 inventory_item_id,
f.number6 vmi_flag,
f.date1 bkt_start_date,
f.date2 bkt_end_date,
f.date3 last_work_date,
LAST_VALUE(mss.SAFETY_STOCK_QUANTITY ignore nulls)
OVER (PARTITION BY f.number1,f.number3,f.number4,f.number5
ORDER by f.date1) safety_stock_qty ,
LAST_VALUE(mss.user_defined_safety_stocks ignore nulls)
OVER (PARTITION BY f.number1,f.number3,f.number4,f.number5
ORDER by f.date1) user_defined_safety_stock_qty
from msc_safety_stocks mss,
msc_hub_query f
where f.query_id=l_qid_ss_item
and f.number1 = mss.plan_id(+)
and f.number3 = mss.sr_instance_id(+)
and f.number4 = mss.organization_id(+)
and f.number5 = mss.inventory_item_id(+)
and f.date4 = mss.period_start_date(+) ;
select msc_hub_query_s.nextval into l_qid_others 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, -- inventory_item_id
number10 --- average daily demand
)
select
l_qid_others,
l_sysdate,1,l_sysdate,1,1,
p_plan_id,
p_plan_run_id,
mdf.sr_instance_id,
mdf.organization_id,
mdf.inventory_item_id,
sum(nvl(mdf.demand_qty,0)) / l_plan_days
from msc_Demands_f mdf
where mdf.plan_id = p_plan_id
and mdf.plan_run_id = p_plan_run_id
group by
l_qid_others,
l_sysdate,1,l_sysdate,1,1,
p_plan_id,
p_plan_run_id,
mdf.sr_instance_id,
mdf.organization_id,
mdf.inventory_item_id;
insert into msc_item_inventory_f (
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
----------------------------
plan_id,
plan_run_id,
io_plan_flag,
sr_instance_id,
organization_id,
owning_inst_id,
owning_org_id,
inventory_item_id,
ship_method,
vmi_flag,
order_date,
aggr_type, category_set_id, sr_category_id,
pab_qty,
pab_value,
pab_value2,
safety_Stock_qty,
min_inventory_level,
max_inventory_level,
avg_daily_demand,
-----------------------------------
SUPPLY_CHAIN_COST ,
SUPPLY_CHAIN_COST2,
REVENUE ,
REVENUE2,
MANUFACTURING_COST ,
MANUFACTURING_COST2 ,
TRANSPORTATION_COST,
TRANSPORTATION_COST2 ,
purchasing_cost ,
purchasing_cost2,
carrying_cost,
carrying_cost2,
GROSS_MARGIN,
GROSS_MARGIN2,
inv_build_target)
select
l_user_id,
l_sysdate,
l_user_id,
l_sysdate,
l_user_login_id,
l_program_id,
l_cp_login_id,
l_appl_id,
l_request_id,
pab_tbl.plan_id,
pab_tbl.plan_run_id,
decode(mp_tbl.plan_type,4,1,9,1,0) io_plan_flag,
decode(pab_tbl.organization_id, -1, -23453, pab_tbl.sr_instance_id) sr_instance_id,
decode(pab_tbl.organization_id, -1, -23453, pab_tbl.organization_id) organization_id,
decode(sign(pab_tbl.sr_instance_id), -1, mp_tbl.sr_instance_id, pab_tbl.sr_instance_id) owing_inst_id,
decode(sign(pab_tbl.organization_id),
-1, msc_hub_calendar.get_item_org(p_plan_id, pab_tbl.inventory_item_id,
decode(sign(pab_tbl.sr_instance_id), -1, mp_tbl.sr_instance_id, pab_tbl.sr_instance_id)),
pab_tbl.organization_id) owing_inst_id,
pab_tbl.inventory_item_id,
pab_tbl.ship_method,
pab_tbl.vmi_flag,
pab_tbl.order_date,
to_number(0) aggr_type,
to_number(-23453) category_set_id,
to_number(-23453) sr_category_id,
sum(pab_tbl.pab_qty) pab_qty, --- sum(decode(sign(pab_tbl.pab_qty),-1,0,pab_tbl.pab_qty)),
sum(pab_tbl.pab_value) pab_value, -- sum(decode(sign(pab_tbl.pab_qty),-1,0,pab_tbl.pab_value)),
sum((pab_tbl.pab_value) *decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
nvl(mcc.CONV_RATE,0))) pab_value2,
sum(pab_tbl.safety_Stock_qty) safety_Stock_qty ,
sum(pab_tbl.min_inventory_level) min_inventory_level,
sum(pab_tbl.max_inventory_level) max_inventory_level,
sum(pab_tbl.avg_daily_demand) avg_daily_demand,
---------------------------------------------------------------------
sum(pab_tbl.total_cost),
sum(pab_tbl.total_cost * decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
nvl(mcc.CONV_RATE,0))),
sum(pab_tbl.revenue),
sum(pab_tbl.revenue * decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
nvl(mcc.CONV_RATE,0))),
sum(pab_tbl.mfg_cost),
sum(pab_tbl.mfg_cost* decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
nvl(mcc.CONV_RATE,0))),
sum(pab_tbl.tp_cost),
sum(pab_tbl.tp_cost * decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
nvl(mcc.CONV_RATE,0))),
sum(pab_tbl.po_cost) ,
sum(pab_tbl.po_cost * decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
nvl(mcc.CONV_RATE,0))),
sum(pab_tbl.carrying_cost),
sum(pab_tbl.carrying_cost * decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
nvl(mcc.CONV_RATE,0))),
sum(pab_tbl.revenue- pab_tbl.total_cost),
sum((pab_tbl.revenue- pab_tbl.total_cost) *decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
nvl(mcc.CONV_RATE,0))),
sum(pab_tbl.inv_build_target)
from
(select
pab.plan_id,
pab.plan_run_id,
pab.sr_instance_id,
pab.organization_id,
pab.inventory_item_id,
'-23453' ship_method,
pab.vmi_flag,
nvl(mtp.currency_code, l_owning_currency_code) currency_code,
pab.order_date,
--------------------------------------------------------------
pab.pab_qty,
pab.pab_qty*m1.standard_cost pab_value,
--------------------------------------------------------------
to_number(null) safety_stock_qty, -- ss
to_number(null) min_inventory_level, -- min level
to_number(null) max_inventory_level,
-------------------------------------------------------------
to_number(null) avg_daily_demand,
to_number(null) revenue,
to_number(null) mfg_cost,
to_number(null) po_cost,
to_number(null) tp_cost,
to_number(null) carrying_cost,
to_number(null) total_cost,
to_number(null) inv_build_target
from
(select
p.number1 plan_id,
p.number2 plan_run_id,
p.number3 sr_instance_id,
p.number4 organization_id,
p.number5 inventory_item_id,
p.number6 vmi_flag,
l.date1 order_date,
p.date3 pab_acvivity_date,
LAST_VALUE(p.number10 ignore nulls)
OVER (PARTITION BY p.number1,p.number2,p.number3,
p.number4,p.number5
ORDER by p.date3) pab_qty
from msc_hub_query l,msc_hub_query p
where l.query_id =l_qid_last_date and p.query_id=l_qid_pab
and l.date3 = p.date3) pab,
msc_system_items m1,
msc_trading_partners mtp,
msc_plans mp --- bug
where pab.plan_id = m1.plan_id(+)
and pab.sr_instance_id = m1.sr_instance_id(+)
and pab.organization_id = m1.organization_id(+)
and pab.inventory_item_id = m1.inventory_item_id(+)
and pab.sr_instance_id = mtp.sr_instance_id(+)
and pab.organization_id = mtp.sr_tp_id(+)
and mtp.partner_type(+) = 3
and pab.plan_id= mp.plan_id --- exclude sno plan since sno plan, pab is from msc
and mp.plan_type<>6 --- msc_bis_inv_detail.pab column
union all
select
ss.number1 plan_id,
ss.number2 lan_run_id,
ss.number3 sr_instance_id,
ss.number4 organization_id,
ss.number5 inventory_item_id,
'-23453' ship_method,
ss.number6 vmi_flag,
nvl(ss_mtp.currency_code, l_owning_currency_code) currency_code,
ss_last.date1 order_date,
----------------------------------------------------------------------------------------
to_number(null) pab_qty,
to_number(null) pab_value,
LAST_VALUE(ss.number10 ignore nulls)
OVER (PARTITION BY ss.number1,ss.number3,
ss.number4,ss.number5
ORDER by ss.date1) safety_stock_qty,
to_number(null) min_inventory_level,
to_number(null) max_inventory_level,
---------------------------------------------------------------------------------
to_number(null) avg_daily_demand,
to_number(null) revenue,
to_number(null) mfg_cost,
to_number(null) po_cost,
to_number(null) tp_cost,
to_number(null) carrying_cost,
to_number(null) total_cost,
to_number(null) inv_build_target
from msc_hub_query ss,
msc_hub_query ss_last,
msc_trading_partners ss_mtp
where ss.query_id = l_qid_ss
and ss_last.query_id =l_qid_last_date
and ss_last.date2 = ss.date1 -- for each last day,pick out its bkt_start_date
and ss.number3 = ss_mtp.sr_instance_id
and ss.number4 = ss_mtp.sr_tp_id
and ss_mtp.partner_type = 3
union all
------------------------------------------------------------------------------------
--- in msc_inventory_level, even if it is day bucket, if it is
--- not a working day, there is no row for it. in such case
--- we use the previous working day's value of the no-working day bucket inventory value
--- see bug 6706755
--- attention: with this, we will not pick up inventory value on not working day in
--- msc_inventory_level ???
/*
here is the very trick part. for min inventory level. in msc_inventory_level table
if it is not a working day, even if it is a day bucket, there is no value for the
day in msc_inventory_level. however, there could be safety stock value for the day
since msc_safety_stock.period_start_date is not aligned with bkt start date
so for min inventory level (day bucket, non working day), we first get min_quantity
from msc_inventory_level for the previous working day and if specified, use it
otherwise, get the ss qty for the bucket day
*/
---------------------------------------------------------------------------------------
select
mil1.plan_id plan_id,
mil1.plan_run_id plan_run_id,
mil1.sr_instance_id sr_instance_id,
mil1.organization_id organization_id,
mil1.inventory_item_id inventory_item_id,
'-23453' ship_method,
mil1.vmi_flag vmi_flag,
nvl(mil_mtp.currency_code, l_owning_currency_code) currency_code,
mil_last.date1 order_date,
----------------------------------------------------------------------------
to_number(null) pab_qty,
to_number(null) pab_value,
----------------------------------------------------------------------------
to_number(null) safety_stock_qty, -- ss
nvl(mil1.min_inventory_level,mil_ss.number10) min_inventory_level,
mil1.max_inventory_level max_inventory_level,
---------------------------------------------------------------------------------
to_number(null) avg_daily_demand,
to_number(null) revenue,
to_number(null) mfg_cost,
to_number(null) po_cost,
to_number(null) tp_cost,
to_number(null) carrying_cost,
to_number(null) total_cost,
to_number(null) inv_build_target
from msc_hub_query mil_ss,
msc_hub_query mil_last,
msc_trading_partners mil_mtp,
(select
item.number1 plan_id,
item.number2 plan_run_id,
item.number3 sr_instance_id,
item.number4 organization_id,
item.number5 inventory_item_id,
item.number6 vmi_flag,
item.date1 order_date, -- move to bkt start date,may not a working day
nvl(mil.MIN_QUANTITY,mil_msi.MIN_MINMAX_QUANTITY) min_inventory_level,
nvl(mil.max_quantity, mil_msi.MAX_MINMAX_QUANTITY) max_inventory_level
from msc_inventory_levels mil,
msc_system_items mil_msi,
msc_hub_query item
where item.query_id = l_qid_mil_item
and item.number1 = mil.plan_id(+)
and item.number3 = mil.sr_instance_id(+)
and item.number4 = mil.organization_id(+)
and item.number5 = mil.inventory_item_id(+)
and item.date5 = mil.inventory_date(+) -- if inventory_level is at 07/13,
--- move to 07/15(not working day) for day bucket only
and mil_msi.plan_id = item.number1
and mil_msi.sr_instance_id =item.number3
and mil_msi.organization_id = item.number4
and mil_msi.inventory_item_id = item.number5 ) mil1
where mil_ss.query_id (+)= l_qid_ss
and mil_ss.number1 (+)= mil1.plan_id
and mil_ss.number2 (+) = mil1.plan_run_id
and mil_ss.number3(+) = mil1.sr_instance_id
and mil_ss.number4(+) = mil1.organization_id
and mil_ss.number5(+) = mil1.inventory_item_id
and mil_ss.number6(+) = mil1.vmi_flag
and mil_ss.date1 (+) = mil1.order_date --- pick the ss value for the bkt start date
and mil_last.query_id = l_qid_last_date
and mil_last.date2 = mil1.order_date
and mil1.sr_instance_id = mil_mtp.sr_instance_id
and mil1.organization_id = mil_mtp.sr_tp_id
and mil_mtp.partner_type = 3
union all
select
others.number1 plan_id, -- plan_id
others.number2 plan_run_id, --- plan_run_id
others.number3 sr_instance_id, -- sr_instance_id
others.number4 organization_id, -- organization_id
others.number5 inventory_item_id, --- inventory_item_id
'-23453' ship_method,
nvl(others_vmi.number6,0) vmi_flag, -- vmi flag
nvl(mtp2.currency_code, l_owning_currency_code) currency_code,
last_date1.date1 order_date, -- end date
----------------------------------------------------------------------------
to_number(null) pab_qty,
to_number(null) pab_value,
----------------------------------------------------------------------------
to_number(null) safety_stock_qty, -- ss
to_number(null) min_inventory_level, -- min level
to_number(null) max_inventory_level,
----------------------------------------------------------------------------
others.number10 avg_daily_demand,
----------------------------------------------------------------------------
to_number(null) revenue,
to_number(null) mfg_cost,
to_number(null) po_cost,
to_number(null) tp_cost,
to_number(null) carrying_cost,
to_number(null) total_cost,
to_number(null) inv_build_target
from msc_hub_query others,
msc_hub_query others_vmi,
msc_hub_query last_date1,
msc_trading_partners mtp2
where last_date1.query_id =l_qid_last_date
and others_vmi.query_id(+) =l_qid_vmi_item
and others.query_id = l_qid_others
and others_vmi.number1(+) =others.number1
and others_vmi.number2(+) =others.number2
and others_vmi.number3(+) =others.number3
and others_vmi.number4(+) =others.number4
and others_vmi.number5(+) =others.number5
and others.number3 = mtp2.sr_instance_id
and others.number4 = mtp2.sr_tp_id
and mtp2.partner_type = 3
union all
select
p_plan_id plan_id,
p_plan_run_id plan_run_id,
mbid.sr_instance_id,
mbid.organization_id,
mbid.inventory_item_id,
nvl(mbid.ship_method, '-23453') ship_method,
nvl(vmi.number6,0) vmi_flag,
nvl(bis_mtp.currency_code, l_owning_currency_code) currency_code,
map.date2 order_date, --- hub week end date
----------------------------------------------------------------------------
to_number(null) pab_qty,
to_number(null) pab_value,
----------------------------------------------------------------------------
to_number(null) safety_stock_qty, -- ss
to_number(null) min_inventory_level, -- min level
to_number(null) max_inventory_level,
----------------------------------------------------------------------------
to_number(null) avg_daily_demand,
-----------------------------------------------------------------------------
decode(mp.plan_type,
6, decode(sign(mbid.mds_price), -1, -mbid.mds_price, 0),
mbid.mds_price) revenue, -- SNO mds_price negative means revenue, positive means demand cost
nvl(mbid.PRODUCTION_COST,0) + nvl(mbid.mds_cost,0) mfg_cost, -- bug 6784517
mbid.purchasing_cost po_cost,
mbid.TRANSPORTATION_COST tp_cost,
mbid.carrying_cost carrying_cost,
nvl(mbid.PRODUCTION_COST,0) + nvl(mbid.mds_cost,0)+ nvl(mbid.purchasing_cost,0) +
nvl(mbid.carrying_cost,0) + nvl(mbid.TRANSPORTATION_COST,0) total_cost,
to_number(null) inv_build_target
----------------------------------------------------------------------------
from msc_bis_inv_detail mbid,
msc_hub_query vmi,
msc_hub_query map,
msc_trading_partners bis_mtp,
msc_plans mp
where vmi.query_id(+) =l_qid_vmi_item
and map.query_id = l_qid_week_map
and map.date3 = trunc(mbid.detail_date)
and nvl(mbid.detail_level,0)=1 and mp.plan_type<>6
and nvl(mbid.period_type,0)=1
and mbid.plan_id=p_plan_id
and vmi.query_id(+) =l_qid_vmi_item
and vmi.number1(+) = mbid.plan_id
and vmi.number3(+) = mbid.sr_instance_id
and vmi.number4(+) = mbid.organization_id
and vmi.number5(+) = mbid.inventory_item_id
and mbid.sr_instance_id = bis_mtp.sr_instance_id(+)
and mbid.organization_id = bis_mtp.sr_tp_id(+)
and bis_mtp.partner_type(+) = 3
and mbid.plan_id=mp.plan_id
union all
select
p_plan_id plan_id,
p_plan_run_id plan_run_id,
mbid.sr_instance_id,
mbid.organization_id,
mbid.inventory_item_id,
nvl(mbid.ship_method, '-23453') ship_method,
nvl(msi_bis.vmi_flag ,0) vmi_flag,
nvl(bis_mtp.currency_code, l_owning_currency_code) currency_code,
map.date2 order_date, --- hub week end date
----------------------------------------------------------------------------
----- inventory build target from sno is not cum value yet.
----- msc_bis_inv_detail.pab is not a cumulative value. It is calculated for each bucket independently
last_value(mbid.pab ignore nulls) over (partition by mbid.plan_id, mbid.sr_instance_id,
mbid.organization_id, mbid.inventory_item_id,
mbid.ship_method
order by map.date2) pab_qty,
--- assume that msc_bis_inv_detail.pab is only
--- populated in sno plan
last_value(mbid.pab ignore nulls) over (partition by mbid.plan_id, mbid.sr_instance_id,
mbid.organization_id, mbid.inventory_item_id,
mbid.ship_method
order by map.date2) * msi_bis.standard_cost pab_value,
----------------------------------------------------------------------------
to_number(null) safety_stock_qty, -- ss
to_number(null) min_inventory_level, -- min level
to_number(null) max_inventory_level,
----------------------------------------------------------------------------
to_number(null) avg_daily_demand,
-----------------------------------------------------------------------------
decode(mp.plan_type,
6, decode(sign(mbid.mds_price), -1, -mbid.mds_price, 0),
mbid.mds_price) revenue, -- SNO mds_price negative means revenue, positive means demand cost
nvl(mbid.PRODUCTION_COST,0) + nvl(mbid.mds_cost,0) mfg_cost, -- bug 6784517
mbid.purchasing_cost po_cost,
mbid.TRANSPORTATION_COST tp_cost,
mbid.carrying_cost carrying_cost,
nvl(mbid.PRODUCTION_COST,0) + nvl(mbid.mds_cost,0)+ nvl(mbid.purchasing_cost,0) +
nvl(mbid.carrying_cost,0) + nvl(mbid.TRANSPORTATION_COST,0) total_cost,
----- inventory build target from sno is not cum value yet.
----- msc_bis_inv_detail.pab is not a cumulative value. It is calculated for each bucket independently
last_value(mbid.pab ignore nulls) over (partition by mbid.plan_id, mbid.sr_instance_id,
mbid.organization_id, mbid.inventory_item_id,
mbid.ship_method
order by map.date2) inv_build_target
----------------------------------------------------------------------------
from
(select p_plan_id plan_id,
mbid1.sr_instance_id,
mbid1.organization_id,
mbid1.inventory_item_id,
nvl(mbid1.ship_method, '-23453') ship_method,
mbid1.detail_date,
sum(nvl(mbid1.mds_price,0)) mds_price,
sum(nvl(mbid1.pab,0)) pab,
sum(nvl(mbid1.PRODUCTION_COST,0)) PRODUCTION_COST,
sum(nvl(mbid1.mds_cost,0) ) mds_cost,
sum(nvl(mbid1.purchasing_cost,0) ) purchasing_cost,
sum(nvl(mbid1.TRANSPORTATION_COST,0) ) TRANSPORTATION_COST,
sum(nvl(mbid1.carrying_cost,0)) carrying_cost
from msc_bis_inv_detail mbid1 where mbid1.plan_id=p_plan_id
group by
p_plan_id,
mbid1.sr_instance_id,
mbid1.organization_id,
mbid1.inventory_item_id,
nvl(mbid1.ship_method, '-23453'),
mbid1.detail_date) mbid,
(select msi_1.plan_id,
msi_1.sr_instance_id,
msi_1.organization_id,
msi_1.inventory_item_id,
msi_1.standard_cost,
nvl(vmi.number6,0) vmi_flag
from msc_system_items msi_1,
msc_hub_query vmi
where vmi.query_id(+) =l_qid_vmi_item
and vmi.number1(+) = msi_1.plan_id
and vmi.number3(+) = msi_1.sr_instance_id
and vmi.number4(+) = msi_1.organization_id
and vmi.number5(+) = msi_1.inventory_item_id) msi_bis ,
msc_hub_query map,
msc_trading_partners bis_mtp,
msc_plans mp
where
--vmi.query_id(+) =l_qid_vmi_item
map.query_id = l_qid_week_map
and map.date3 = trunc(mbid.detail_date)
and mp.plan_type=6
and mbid.plan_id=p_plan_id
and msi_bis.plan_id (+)= mbid.plan_id
and msi_bis.sr_instance_id (+)= mbid.sr_instance_id --- make sure it is out join for customer demand in sno
and msi_bis.organization_id(+) = mbid.organization_id
and msi_bis.inventory_item_id(+) = mbid.inventory_item_id
and mbid.sr_instance_id = bis_mtp.sr_instance_id(+)
and mbid.organization_id = bis_mtp.sr_tp_id(+)
and bis_mtp.partner_type(+) = 3
and mbid.plan_id=mp.plan_id
) pab_tbl,msc_plans mp_tbl,
MSC_CURRENCY_CONV_MV mcc
where mcc.FROM_CURRENCY(+) =pab_tbl.currency_code --- make sure 'xxx' is not a valid currency code
and mcc.TO_CURRENCY(+) = fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
and mcc.CALENDAR_DATE(+) = pab_tbl.order_date
and mp_tbl.plan_id = pab_tbl.plan_id ---
group by
pab_tbl.plan_id,
pab_tbl.plan_run_id,
decode(mp_tbl.plan_type,4,1,9,1,0),
decode(pab_tbl.organization_id, -1, -23453, pab_tbl.sr_instance_id),
decode(pab_tbl.organization_id, -1, -23453, pab_tbl.organization_id),
decode(sign(pab_tbl.sr_instance_id), -1, mp_tbl.sr_instance_id, pab_tbl.sr_instance_id),
decode(sign(pab_tbl.organization_id),
-1, msc_hub_calendar.get_item_org(p_plan_id, pab_tbl.inventory_item_id,
decode(sign(pab_tbl.sr_instance_id), -1, mp_tbl.sr_instance_id, pab_tbl.sr_instance_id)),
pab_tbl.organization_id),
pab_tbl.inventory_item_id,
pab_tbl.ship_method,
pab_tbl.vmi_flag,
pab_tbl.order_date;
insert into msc_item_orders_f (
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
------------------------------------------------------------------------
PLAN_ID,
PLAN_RUN_ID,
io_plan_flag,
SR_INSTANCE_ID,
ORGANIZATION_ID,
owning_inst_id,
owning_org_id,
INVENTORY_ITEM_ID,
vmi_flag,
ORDER_DATE,
aggr_type, category_set_id, sr_category_id,
------------------------------------------------------------------------
DEMAND_QTY,
SUPPLY_QTY,
PEGGED_TO_EXCESS_QTY ,
PLANNED_ORDER_QTY,
INDEP_DEMAND_QTY,
INDEP_DEMAND_VALUE,
DEP_DEMAND_QTY,
SALES_ORDER_VALUE,
RETURN_ORDER_VALUE,
MAKE_ORDER_QTY,
MAKE_ORDER_LEADTIME,
MAKE_ORDER_COUNT,
STOCK_OUTS_COUNT,
NO_ACTIVITY_ITEM_COUNT,
DAYS_IN_BKT,
item_leadtime,
avg_daily_demand,
onhand_qty,
Scheduled_rept_qty,
forecast_qty)
select
l_user_id,
l_sysdate,
l_user_id,
l_sysdate,
l_user_login_id,
l_program_id,
l_cp_login_id,
l_appl_id,
l_request_id,
---------------------------------------------------
p_plan_id,
p_plan_run_id,
decode(mp_tbl.plan_type,4,1,9,1,0) io_plan_flag,
order_tbl.sr_instance_id,
order_tbl.organization_id,
decode(order_tbl.sr_instance_id,-23453, mp_tbl.sr_instance_id,order_tbl.sr_instance_id) owing_inst_id,
decode(order_tbl.organization_id,
-23453,msc_hub_calendar.get_item_org(p_plan_id,order_tbl.inventory_item_id,
decode(order_tbl.sr_instance_id,-23453,mp_tbl.sr_instance_id,
order_tbl.sr_instance_id)),
order_tbl.organization_id) owning_org_id,
order_tbl.inventory_item_id,
order_tbl.vmi_flag,
order_tbl.order_date,
to_number(0) aggr_type,
to_number(-23453) category_set_id,
to_number(-23453) sr_category_id,
--------------------------------------------------
sum(order_tbl.demand_qty),
sum(order_tbl.supply_qty),
sum(order_tbl.qty_pegged_to_excess),
sum(order_tbl.planned_order_qty),
sum(order_tbl.indep_demand_qty),
sum(order_tbl.indep_demand_value),
sum(order_tbl.dep_demand_qty),
sum(order_tbl.sales_order_value),
sum(order_tbl.return_order_value),
sum(order_tbl.make_order_qty),
sum(order_tbl.make_order_leadtime),
sum(order_tbl.make_order_count),
sum(order_tbl.stock_outs_count),
sum(order_tbl.no_activity_item_count),
sum(order_tbl.days_in_bkt),
sum(order_tbl.item_leadtime),
sum(order_tbl.avg_daily_demand),
sum(order_tbl.onhand_Qty),
sum(order_tbl.Scheduled_rept_qty),
sum(order_tbl.forecast_qty)
from
(
select
sd.number1 plan_id, -- plan_id
sd.number2 plan_run_id, --- plan_run_id
sd.number3 sr_instance_id, -- sr_instance_id
sd.number4 organization_id, -- organization_id
sd.number5 inventory_item_id, --- inventory_item_id
sd.number6 vmi_flag, -- vmi flag
sd.date3 order_date,
-------------------------------------------------
sd.number11 + nvl(sd.number9,0) demand_qty, -- total demand -- in drp, supply(1,2,51) is demand
sd.number12 supply_qty, -- total supply
sd.number13 planned_order_qty, -- planned order qty
sd.number14 indep_demand_qty, -- indep_demand_qty
sd.number15 indep_demand_value, --- indep_demand_value
sd.number16 dep_demand_qty, -- total dep demand
sd.number17 sales_order_value, --sales order value
sd.number18 return_order_value, -- return order value
sd.number19 make_order_qty, -- make order qty
sd.number20 make_order_leadtime, -- make order leadtime
sd.number21 make_order_count, -- make order count
sd_item.number11 days_in_bkt , -- days in bucket
sd.number23 item_leadtime,
sd.number24 onhand_Qty,
sd.number25 Scheduled_rept_qty,
sd.number22 forecast_qty, --- forecast qty
----------------------------------------
nvl(others.number10,0) avg_daily_demand,
---------------------------------------------------------------
to_number(null) qty_pegged_to_excess,
--------------------------------------------------------------
to_number(null) no_activity_item_count,
to_number(null) stock_outs_count
from msc_hub_query sd,
msc_hub_query sd_item,
msc_hub_query others
where sd.query_id =l_qid_pab
and sd_item.query_id =l_qid_sd_item
and sd.number1 = sd_item.number1
and sd.number2 = sd_item.number2
and sd.number3 = sd_item.number3
and sd.number4 = sd_item.number4
and sd.number5 = sd_item.number5
and sd.date3 = sd_item.date3
and others.query_id(+) = l_qid_others
and others.number1(+)= sd.number1
and others.number2(+)= sd.number2
and others.number3 (+)=sd.number3
and others.number4(+)= sd.number4
and others.number5 (+)= sd.number5 --- note, need outer join since some item may do not have demand(only supply)
union all
select
mfp.plan_id plan_id,
p_plan_run_id plan_run_id,
mfp.sr_instance_id sr_instance_id,
mfp.organization_id organization_id,
mfp.inventory_item_id inventory_item_id,
nvl(peg_vmi.number6,0) vmi_flag,
trunc(nvl(mfp.supply_date,nvl(ms.firm_date,ms.new_schedule_date))) order_date,
-----------------------------------------------------------------------
to_number(null) demand_qty, -- total demand
to_number(null) supply_qty, -- total supply
to_number(null) planned_order_qty, -- planned order qty
to_number(null) indep_demand_qty, -- indep_demand_qty
to_number(null) indep_demand_value, --- indep_demand_value
to_number(null) dep_demand_qty, -- total dep demand
to_number(null) sales_order_value, --sales order value
to_number(null) return_order_value, -- return order value
to_number(null) make_order_qty, -- make order qty
to_number(null) make_order_leadtime, -- make order leadtime
to_number(null) make_order_count, -- make order count
to_number(null) days_in_bkt , -- days in bucket
to_number(null) item_leadtime,
to_number(null) onhand_Qty,
to_number(null) Scheduled_rept_qty,
to_number(null) forecast_qty,
to_number(null) avg_daily_demand,
------------------------------------------------------------------------------
sum(mfp.allocated_quantity) qty_pegged_to_excess,
------------------------------------------------------------------------------
to_number(null) no_activity_item_count ,
to_number(null) stock_outs_count
from msc_full_pegging mfp,
msc_hub_query peg_vmi,
msc_supplies ms
where ms.plan_id=mfp.plan_id
and ms.TRANSACTION_ID = mfp.TRANSACTION_ID
and ms.sr_instance_id = mfp.sr_instance_id
and mfp.plan_id =p_plan_id
and mfp.demand_id=-1
and mfp.plan_id = peg_vmi.number1(+)
and mfp.sr_instance_id = peg_vmi.number3(+)
and mfp.organization_id = peg_vmi.number4(+)
and mfp.inventory_item_id = peg_vmi.number5(+)
and peg_vmi.query_id (+) =l_qid_vmi_item
group by
mfp.plan_id ,
p_plan_run_id ,
mfp.sr_instance_id ,
mfp.organization_id ,
mfp.inventory_item_id ,
nvl(peg_vmi.number6,0) ,
trunc(nvl(mfp.supply_date,nvl(ms.firm_date,ms.new_schedule_date)))
union all
select
plan_id,
plan_run_id,
me.sr_instance_id,
me.ORGANIZATION_ID,
me.INVENTORY_ITEM_ID,
nvl(vmi1.number6,0) vmi_flag,
me.ANALYSIS_DATE order_date, --- bkt_start_date
-----------------------------------------------------------------------
to_number(null) demand_qty, -- total demand
to_number(null) supply_qty, -- total supply
to_number(null) planned_order_qty, -- planned order qty
to_number(null) indep_demand_qty, -- indep_demand_qty
to_number(null) indep_demand_value, --- indep_demand_value
to_number(null) dep_demand_qty, -- total dep demand
to_number(null) sales_order_value, --sales order value
to_number(null) return_order_value, -- return order value
to_number(null) make_order_qty, -- make order qty
to_number(null) make_order_leadtime, -- make order leadtime
to_number(null) make_order_count, -- make order count
to_number(null) days_in_bkt , -- days in bucket
to_number(null) item_leadtime,
to_number(null) onhand_Qty,
to_number(null) Scheduled_rept_qty,
to_number(null) forecast_qty,
to_number(null) avg_daily_demand,
------------------------------------------------------------------------------
to_number(null) qty_pegged_to_excess,
----------------------------------------------------------
sum(decode(EXCEPTION_TYPE,5,EXCEPTION_COUNT,0) )no_activity_item_count,
sum(decode(EXCEPTION_TYPE,2,exception_count,0)) stock_outs_count
from msc_exceptions_f me, msc_hub_query vmi1
where me.EXCEPTION_TYPE in (5,2)
and me.plan_id = p_plan_id
and me.plan_run_id = p_plan_run_id
and me.aggr_type=0
and vmi1.number1(+) = me.plan_id
and vmi1.number3(+) = me.sr_instance_id
and vmi1.number4(+) = me.organization_id
and vmi1.number5(+) = me.inventory_item_id
and vmi1.number2(+) = me.plan_run_id
and vmi1.query_id(+)=l_qid_vmi_item
group by
plan_id,
plan_run_id,
me.sr_instance_id,
me.ORGANIZATION_ID,
me.INVENTORY_ITEM_ID,
nvl(vmi1.number6,0),
me.ANALYSIS_DATE
) order_tbl,msc_plans mp_tbl
where mp_tbl.plan_id = p_plan_id
group by
l_user_id,
l_sysdate,
l_user_id,
l_sysdate,
l_user_login_id,
l_program_id,
l_cp_login_id,
l_appl_id,
l_request_id,
p_plan_id,
p_plan_run_id,
decode(mp_tbl.plan_type,4,1,9,1,0),
order_tbl.sr_instance_id,
order_tbl.organization_id,
decode(order_tbl.sr_instance_id,-23453, mp_tbl.sr_instance_id,order_tbl.sr_instance_id),
decode(order_tbl.organization_id,
-23453,msc_hub_calendar.get_item_org(p_plan_id,order_tbl.inventory_item_id,
decode(order_tbl.sr_instance_id,-23453,mp_tbl.sr_instance_id,
order_tbl.sr_instance_id)),
order_tbl.organization_id) ,
order_tbl.inventory_item_id,
order_tbl.vmi_flag,
order_tbl.order_date;
insert into msc_item_inventory_f (
plan_id, plan_run_id, io_plan_flag,
sr_instance_id, organization_id, owning_inst_id, owning_org_id,
inventory_item_id,
ship_method, vmi_flag, order_date,
aggr_type, category_set_id, sr_category_id,
pab_qty,
pab_value,
pab_value2,
safety_stock_qty,
min_inventory_level,
max_inventory_level,
avg_daily_demand,
supply_chain_cost,
supply_chain_cost2,
revenue,
revenue2,
manufacturing_cost,
manufacturing_cost2,
transportation_cost,
transportation_cost2,
purchasing_cost,
purchasing_cost2,
carrying_cost,
carrying_cost2,
gross_margin,
gross_margin2,
inv_build_target,
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.io_plan_flag,
f.sr_instance_id, f.organization_id, f.owning_inst_id, f.owning_org_id,
to_number(-23453) inventory_item_id,
f.ship_method, f.vmi_flag, f.order_date,
to_number(42) aggr_type,
l_category_set_id1 category_set_id,
nvl(q.sr_category_id, -23453),
sum(f.pab_qty),
sum(f.pab_value),
sum(f.pab_value2),
sum(f.safety_stock_qty),
sum(f.min_inventory_level),
sum(f.max_inventory_level),
sum(f.avg_daily_demand),
sum(f.supply_chain_cost),
sum(f.supply_chain_cost2),
sum(f.revenue),
sum(f.revenue2),
sum(f.manufacturing_cost),
sum(f.manufacturing_cost2),
sum(f.transportation_cost),
sum(f.transportation_cost2),
sum(f.purchasing_cost),
sum(f.purchasing_cost2),
sum(f.carrying_cost),
sum(f.carrying_cost2),
sum(f.gross_margin),
sum(f.gross_margin2),
sum(f.inv_build_target),
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_item_inventory_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.io_plan_flag,
f.sr_instance_id, f.organization_id, f.owning_inst_id, f.owning_org_id,
f.ship_method, f.vmi_flag, f.order_date,
nvl(q.sr_category_id, -23453);
insert into msc_item_orders_f (
plan_id, plan_run_id, io_plan_flag,
sr_instance_id, organization_id, owning_inst_id, owning_org_id,
inventory_item_id,
vmi_flag, order_date,
aggr_type, category_set_id, sr_category_id,
demand_qty,
supply_qty,
pegged_to_excess_qty ,
planned_order_qty,
indep_demand_qty,
indep_demand_value,
dep_demand_qty,
sales_order_value,
return_order_value,
make_order_qty,
make_order_leadtime,
make_order_count,
stock_outs_count,
no_activity_item_count,
days_in_bkt,
item_leadtime,
avg_daily_demand,
onhand_qty,
scheduled_rept_qty,
forecast_qty,
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.io_plan_flag,
f.sr_instance_id, f.organization_id, f.owning_inst_id, f.owning_org_id,
to_number(-23453) inventory_item_id,
f.vmi_flag, f.order_date,
to_number(42) aggr_type,
l_category_set_id1 category_set_id,
nvl(q.sr_category_id, -23453),
sum(f.demand_qty),
sum(f.supply_qty),
sum(f.pegged_to_excess_qty ),
sum(f.planned_order_qty),
sum(f.indep_demand_qty),
sum(f.indep_demand_value),
sum(f.dep_demand_qty),
sum(f.sales_order_value),
sum(f.return_order_value),
sum(f.make_order_qty),
sum(f.make_order_leadtime),
sum(f.make_order_count),
sum(f.stock_outs_count),
sum(f.no_activity_item_count),
sum(f.days_in_bkt),
sum(f.item_leadtime),
sum(f.avg_daily_demand),
sum(f.onhand_qty),
sum(f.scheduled_rept_qty),
sum(f.forecast_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
msc_item_orders_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.io_plan_flag,
f.sr_instance_id, f.organization_id, f.owning_inst_id, f.owning_org_id,
f.vmi_flag, f.order_date,
nvl(q.sr_category_id, -23453);
delete /*+ PARALLEL(mos) */ from msc_item_inventory_f
where rownum<=p_commit_size and plan_id=p_plan_id and plan_run_id=nvl(p_plan_run_id,plan_run_id);
select count(1) into l_num from msc_item_inventory_f
where plan_id=p_plan_id and plan_run_id=nvl(p_plan_run_id,plan_run_id);
delete /*+ PARALLEL(mos) */ from msc_item_inventory_f
where rownum<=p_commit_size and plan_id=p_plan_id and plan_run_id=nvl(p_plan_run_id,plan_run_id);
select count(1) into l_num from msc_item_inventory_f
where plan_id=p_plan_id and plan_run_id=nvl(p_plan_run_id,plan_run_id);
delete /*+ PARALLEL(mos) */ from msc_item_orders_f
where rownum<=p_commit_size and plan_id=p_plan_id and plan_run_id=nvl(p_plan_run_id,plan_run_id);
select count(1) into l_num from msc_item_orders_f
where plan_id=p_plan_id and plan_run_id=nvl(p_plan_run_id,plan_run_id);