[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select trunc(mp.curr_start_date),trunc(mp.curr_cutoff_date)
into l_curr_start_date,l_curr_cutoff_date
from msc_plans mp where mp.plan_id=p_plan_id;
select msc_hub_query_s.nextval into l_qid_vmi 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
number3, -- sr_instance_id
number4, -- organization_id
number5, -- inventory_item_id
number10 -- vmi flag
)
select
unique l_qid_vmi,l_sysdate,1,l_sysdate,1,1,
p_plan_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;
Insert into msc_supplies_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_id
plan_run_id,
io_plan_flag, --- this flag indidate whether it is an io plan
sr_instance_id,
organization_id,
inventory_item_id,
project_id,
task_id,
supplier_id,
supplier_site_id,
region_id,
ship_method,
supply_date,
aggr_type, category_set_id, sr_category_id,
supply_type,
vmi_flag,
supply_qty,
Planned_order_count,
work_order_leadtime , --- for work order (work order, planned work order)
work_order_count,
work_order_qty,
stockout_days , -- this is for vmi measure 'stockout day'
drp_supply_as_demand,
return_order_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, -- plan_id
p_plan_run_id, -- plan_run_id,
decode(mp.plan_type,4,1,9,1,0) io_plan_flag,
supply_tbl.sr_instance_id,
supply_tbl.organization_id,
supply_tbl.inventory_item_id,
supply_tbl.project_id,
supply_tbl.task_id,
supply_tbl.supplier_id,
supply_tbl.supplier_site_id,
mps.region_id,
--- if supply_date l_curr_cutoff_date, supply_date=l_curr_cutoff_date+1
--- else supply_date
--- we can not simply put it at plan start date,
--- should be at the last working day of the bucket where plan start date is
supply_tbl.ship_method,
decode(sign(to_number(supply_tbl.supply_date-l_curr_start_date)),
-1, msc_hub_calendar.last_work_date(p_plan_id,l_curr_start_date),
decode(supply_tbl.supply_type,
18, msc_hub_calendar.last_work_date(p_plan_id,supply_tbl.supply_date),
supply_tbl.supply_date)),
to_number(0) aggr_type,
to_number(-23453) category_set_id,
to_number(-23453) sr_category_id,
supply_tbl.supply_type,
supply_tbl.vmi_flag,
sum(supply_tbl.supply_qty),
sum(supply_tbl.Planned_order_count),
sum(supply_tbl.work_order_leadtime),
sum(supply_tbl.work_order_count),
sum(supply_tbl.work_order_qty),
sum(supply_tbl.stockout_days),
sum(supply_tbl.drp_supply_as_demand) ,
sum(supply_tbl.return_order_qty)
from
(select
ms.sr_instance_id,
ms.organization_id,
ms.inventory_item_id,
nvl(ms.project_id,-23453) project_id,
nvl(ms.task_id,-23453) task_id,
nvl(ms.supplier_id,-23453) supplier_id,
nvl(ms.supplier_site_id,-23453) supplier_site_id,
nvl(ms.zone_id,-23453) region_id,
nvl(ms.ship_method, '-23453') ship_method,
trunc(nvl(ms.firm_date,ms.new_schedule_date)) supply_date,
ms.order_type supply_type,
msi.vmi_flag,
sum(decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) )) supply_qty,
sum(decode(ms.order_type,5,
decode(msi.base_item_id,null,
decode(ms.disposition_status_type, 2, 0,1),1),to_number(null))) Planned_order_count,
---------------------------------------------------------------------------
--- ??? exclude if new_schedule_date is null
--- decode(nvl(ms.source_organization_id, ms.organization_id),
-- ms.organization_id,
-- PLANNED_MAKE_OFF,
-- PLANNED_BUY_OFF)
-- make order 3,7,14,15,27,28,
-- 4,13 ?? do we need to include Repetitive schdule as make order??
-- make planned order
-- 3,4,5,7,13,14,15,16,17,27,28,30
---------------------------------------------------------------------------
sum(decode(ms.order_type,3, nvl(ms.NEW_SCHEDULE_DATE,null)-nvl(ms.new_wip_start_date,null),
5,decode(msc_supply_pkg.implement_code(ms.source_organization_id,ms.organization_id,
msi.repetitive_type,ms.source_supplier_id,
msi.planning_make_buy_code,msi.build_in_wip_flag),
3,nvl(ms.NEW_SCHEDULE_DATE,null)-nvl(ms.new_wip_start_date,null),
4,nvl(ms.NEW_SCHEDULE_DATE,null)- nvl(ms.FIRST_UNIT_START_DATE,null),
0),
7,nvl(ms.NEW_SCHEDULE_DATE,null)-nvl(ms.new_wip_start_date,null),
14,nvl(ms.NEW_SCHEDULE_DATE,null)-nvl(ms.new_wip_start_date,null),
15,nvl(ms.NEW_SCHEDULE_DATE,null)-nvl(ms.new_wip_start_date,null),
27,nvl(ms.NEW_SCHEDULE_DATE,null)-nvl(ms.new_wip_start_date,null),
28,nvl(ms.NEW_SCHEDULE_DATE,null)-nvl(ms.new_wip_start_date,null),
4,nvl(ms.NEW_SCHEDULE_DATE,null)- nvl(ms.FIRST_UNIT_START_DATE,null),
13,nvl(ms.NEW_SCHEDULE_DATE,null)- nvl(ms.FIRST_UNIT_START_DATE,null),
16,nvl(ms.NEW_SCHEDULE_DATE,null)- nvl(ms.FIRST_UNIT_START_DATE,null),
17,nvl(ms.NEW_SCHEDULE_DATE,null)-nvl(ms.new_wip_start_date,null),
30,nvl(ms.NEW_SCHEDULE_DATE,null)- nvl(ms.FIRST_UNIT_START_DATE,null),
88,nvl(ms.NEW_SCHEDULE_DATE,null)- nvl(ms.FIRST_UNIT_START_DATE,null),
to_number(null))) work_order_leadtime,
sum(decode(ms.order_type,3,1,
5,decode(msc_supply_pkg.implement_code(ms.source_organization_id,ms.organization_id,
msi.repetitive_type,ms.source_supplier_id,
msi.planning_make_buy_code,msi.build_in_wip_flag),
3,1,0),
7,1,
14,1,
15,1,
27,1,
28,1,
4,1,
13,1,
16,1,
17,1,
30,1,
88,1,
to_number(null))) work_order_count,
sum(decode(ms.order_type,
3, decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
5, decode(msc_supply_pkg.implement_code(ms.source_organization_id,ms.organization_id,
msi.repetitive_type,ms.source_supplier_id,
msi.planning_make_buy_code,msi.build_in_wip_flag),
3,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
4,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
0),
7,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
14,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
15,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
27,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
28,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
4,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
13,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
16,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
17,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
30,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
88,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
to_number(null))) work_order_qty,
--- order_type in (1,2,18) and nvl(item_type_id,401) = 401 and nvl(item_type_value,1) = 2
sum(decode(mp.plan_type,8,
decode(ms.order_type,1,decode(nvl(item_type_id,401),401,
decode(nvl(item_type_value,1),
2,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
0),
0),
2,decode(nvl(item_type_id,401),401,
decode(nvl(item_type_value,1),
2,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
0),
0),
18,decode(nvl(item_type_id,401),401,
decode(nvl(item_type_value,1),
2,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
0),
0),
0),
9,
decode(ms.order_type,1,decode(nvl(item_type_id,401),401,
decode(nvl(item_type_value,1),
2,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
0),
0),
2,decode(nvl(item_type_id,401),401,
decode(nvl(item_type_value,1),
2,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
0),
0),
18,decode(nvl(item_type_id,401),401,
decode(nvl(item_type_value,1),
2,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
0),
0),
0),
0)) return_order_qty,
to_number(null) drp_supply_as_demand,
to_number(null) stockout_days
from
msc_supplies ms,
( select msi_1.plan_id,
msi_1.sr_instance_id,
msi_1.organization_id,
msi_1.inventory_item_id,
msi_1.base_item_id,
msi_1.repetitive_type,
msi_1.planning_make_buy_code,
msi_1.build_in_wip_flag,
nvl(f_1.number10,0) vmi_flag
from msc_system_items msi_1,
msc_hub_query f_1
where f_1.query_id(+) = l_qid_vmi
and f_1.number1(+) = msi_1.plan_id
and f_1.number3(+) = msi_1.sr_instance_id
and f_1.number4(+) = msi_1.organization_id
and f_1.number5(+) = msi_1.inventory_item_id) msi, msc_plans mp
where 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.plan_id=p_plan_id
and ms.plan_id=mp.plan_id
group by
ms.sr_instance_id,
ms.organization_id,
ms.inventory_item_id,
nvl(ms.project_id,-23453),
nvl(ms.task_id,-23453),
nvl(ms.supplier_id,-23453),
nvl(ms.supplier_site_id,-23453),
nvl(ms.zone_id,-23453),
nvl(ms.ship_method, '-23453'),
trunc(nvl(ms.firm_date,ms.new_schedule_date)),
ms.order_type,
msi.vmi_flag
union all
select
med.sr_instance_id,
med.organization_id,
med.inventory_item_id,
-23453 project_id,
-23453 task_id,
nvl(med.supplier_id, -23453) supplier_id,
nvl(med.supplier_site_id, -23453) supplier_id,
nvl(med.zone_id, -23453) region_id,
'-23453' ship_method,
trunc(med.date1) supply_date, -- exception date
-23453 supply_type,
nvl(f_2.number10,0) vmi_flag,
-----------------------------------------------------------------------
to_number(null) supply_qty,
to_number(null) Planned_order_count,
to_number(null) work_order_leadtime,
to_number(null) work_order_count,
to_number(null) work_order_qty,
to_number(null) return_order_qty,
to_number(null) drp_supply_as_demand,
sum(MED.DATE2 - MED.DATE1) stockout_days --- should be to_date - from_date
-- may get from msc_exception_f if this has performance issue
-- table from_date - to_date
from
msc_exception_details med,
msc_hub_query f_2
where f_2.query_id(+) = l_qid_vmi
and f_2.number1(+) = med.plan_id
and f_2.number3(+) = med.sr_instance_id
and f_2.number4(+) = med.organization_id
and f_2.number5(+) = med.inventory_item_id
and med.exception_type =2
and med.plan_id = p_plan_id
group by
med.sr_instance_id,
med.organization_id,
med.inventory_item_id,
-23453,
-23453,
nvl(med.supplier_id, -23453),
nvl(med.supplier_site_id, -23453),
nvl(med.zone_id, -23453),
trunc(med.date1),
-23453,
nvl(f_2.number10,0)
union all
select
ms2.sr_instance_id,
ms2.source_organization_id organization_id,
ms2.inventory_item_id,
nvl(ms2.project_id,-23453) project_id,
nvl(ms2.task_id,-23453) task_id,
nvl(ms2.supplier_id,-23453) supplier_id,
nvl(ms2.supplier_site_id,-23453) supplier_site_id,
nvl(ms2.zone_id,-23453) supplier_site_id,
nvl(ms2.ship_method, '-23453') ship_method,
trunc(nvl(ms2.firm_date,ms2.new_schedule_date)) supply_date,
ms2.order_type supply_type,
msi2.vmi_flag,
-----------------------------------------------------------------------
to_number(null) supply_qty,
to_number(null) Planned_order_count,
to_number(null) work_order_leadtime,
to_number(null) work_order_count,
to_number(null) work_order_qty,
to_number(null) return_order_qty,
/* 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(decode(mp.plan_type,5,decode(ms2.order_type,1,
decode(ms2.organization_id,ms2.source_organization_id,0,
decode(msi2.base_item_id,null,
decode(ms2.disposition_status_type,2, 0,
decode(ms2.last_unit_completion_date,null, ms2.new_order_quantity,ms2.daily_rate) ),
decode(ms2.last_unit_completion_date,null, ms2.new_order_quantity,ms2.daily_rate) )),
51,decode(ms2.organization_id, ms2.source_organization_id,0,
decode(msi2.base_item_id,null,
decode(ms2.disposition_status_type,2, 0,
decode(ms2.last_unit_completion_date,null, ms2.new_order_quantity,ms2.daily_rate) ),
decode(ms2.last_unit_completion_date,null, ms2.new_order_quantity,ms2.daily_rate) )),
2,decode(ms2.supplier_id,null,0,
decode(ms2.organization_id, ms2.source_organization_id,0,
decode(msi2.base_item_id,null,
decode(ms2.disposition_status_type,2, 0,
decode(ms2.last_unit_completion_date,null, ms2.new_order_quantity,ms2.daily_rate) ),
decode(ms2.last_unit_completion_date,null, ms2.new_order_quantity,ms2.daily_rate) ))),
0),0)) drp_supply_as_demand,
to_number(null) stockout_days
from msc_supplies ms2,msc_plans mp,
( select msi_2.plan_id,
msi_2.sr_instance_id,
msi_2.organization_id,
msi_2.inventory_item_id,
msi_2.base_item_id,
msi_2.planning_make_buy_code,
msi_2.build_in_wip_flag,
nvl(f_3.number10,0) vmi_flag
from msc_system_items msi_2,
msc_hub_query f_3
where f_3.query_id(+) = l_qid_vmi
and f_3.number1(+) = msi_2.plan_id
and f_3.number3(+) = msi_2.sr_instance_id
and f_3.number4(+) = msi_2.organization_id
and f_3.number5(+) = msi_2.inventory_item_id) msi2
where ms2.plan_id = msi2.plan_id
and ms2.sr_instance_id = msi2.sr_instance_id
and ms2.source_organization_id =msi2.organization_id
and ms2.inventory_item_id = msi2.inventory_item_id
and ms2.plan_id=p_plan_id
and ms2.order_type in (1,2,51)
and ms2.plan_id=mp.plan_id
group by
ms2.sr_instance_id,
ms2.source_organization_id,
ms2.inventory_item_id,
nvl(ms2.project_id,-23453),
nvl(ms2.task_id,-23453),
nvl(ms2.supplier_id,-23453),
nvl(ms2.supplier_site_id,-23453),
nvl(ms2.zone_id,-23453),
nvl(ms2.ship_method, '-23453'),
trunc(nvl(ms2.firm_date,ms2.new_schedule_date)),
ms2.order_type,
msi2.vmi_flag
order by 1,2,3,4,5,6,7,8,9,10) supply_tbl,
msc_plans mp,
msc_phub_suppliers_mv mps
where mp.plan_id=p_plan_id --- we need this to separate io measure from other measures
and mps.supplier_id = nvl(supply_tbl.supplier_id, -23453)
and mps.supplier_site_id = nvl(supply_tbl.supplier_site_id, -23453)
and mps.region_id = decode(nvl(supply_tbl.supplier_id, -23453),
-23453, nvl(supply_tbl.region_id, -23453), mps.region_id)
group by
decode(mp.plan_type,4,1,9,1,0),
supply_tbl.sr_instance_id,
supply_tbl.organization_id,
supply_tbl.inventory_item_id,
supply_tbl.project_id,
supply_tbl.task_id,
supply_tbl.supplier_id,
supply_tbl.supplier_site_id,
mps.region_id,
supply_tbl.ship_method,
decode(sign(to_number(supply_tbl.supply_date-l_curr_start_date)),
-1, msc_hub_calendar.last_work_date(p_plan_id,l_curr_start_date),
decode(supply_tbl.supply_type,
18, msc_hub_calendar.last_work_date(p_plan_id,supply_tbl.supply_date),
supply_tbl.supply_date)),
supply_tbl.supply_type,
supply_tbl.vmi_flag;
Insert into msc_item_wips_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_id
plan_run_id,
sr_instance_id,
organization_id,
inventory_item_id,
vmi_flag,
wip_start_date,
aggr_type, category_set_id, sr_category_id,
wip_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, -- plan_id
p_plan_run_id, -- plan_run_id,
wip_tbl.sr_instance_id,
wip_tbl.organization_id,
wip_tbl.inventory_item_id,
wip_tbl.vmi_flag,
decode(sign(to_number(wip_tbl.wip_start_date-l_curr_start_date)),-1,l_curr_start_date,wip_tbl.wip_start_date),
to_number(0) aggr_type,
to_number(-23453) category_set_id,
to_number(-23453) sr_category_id,
sum(wip_tbl.wip_qty)
from
(select
ms.sr_instance_id,
ms.organization_id,
ms.inventory_item_id,
msi.vmi_flag,
trunc(nvl(nvl(ms.new_wip_start_date,ms.FIRST_UNIT_START_DATE),l_curr_start_date)) wip_start_date,
-- make order 3,7,14,15,27,28,
-- 4,13 ?? do we need to include Repetitive schdule as make order??
-- make planned order
---------------------------------------------------------------------------
sum(decode(ms.order_type,
3, decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
5, decode(msc_supply_pkg.implement_code(ms.source_organization_id,ms.organization_id,
msi.repetitive_type,ms.source_supplier_id,
msi.planning_make_buy_code,msi.build_in_wip_flag),
3,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
4,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
0),
7,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
14,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
15,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
27,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
28,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
4,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
13,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
16,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
17,decode(msc_supply_pkg.implement_code(ms.source_organization_id,ms.organization_id,
msi.repetitive_type,ms.source_supplier_id,
msi.planning_make_buy_code,msi.build_in_wip_flag),
3,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
4,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
0),
30,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
88,decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
decode(ms.last_unit_completion_date,null, ms.new_order_quantity,ms.daily_rate) ),
to_number(null))) wip_qty
from
msc_supplies ms,
( select msi_1.plan_id,
msi_1.sr_instance_id,
msi_1.organization_id,
msi_1.inventory_item_id,
msi_1.base_item_id,
msi_1.repetitive_type,
msi_1.planning_make_buy_code,
msi_1.build_in_wip_flag,
nvl(f_1.number10,0) vmi_flag
from msc_system_items msi_1,
msc_hub_query f_1
where f_1.query_id(+) = l_qid_vmi
and f_1.number1(+) = msi_1.plan_id
and f_1.number3(+) = msi_1.sr_instance_id
and f_1.number4(+) = msi_1.organization_id
and f_1.number5(+) = msi_1.inventory_item_id) msi,
msc_plans mp
where 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.plan_id=p_plan_id
and mp.plan_id=ms.plan_id
and mp.plan_type not in (4,9) --- exclude io plan
group by
ms.sr_instance_id,
ms.organization_id,
ms.inventory_item_id,
msi.vmi_flag,
trunc(nvl(nvl(ms.new_wip_start_date,ms.FIRST_UNIT_START_DATE),l_curr_start_date)) ) wip_tbl
-- where l_plan_type <> 6
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, -- plan_id
p_plan_run_id, -- plan_run_id,
wip_tbl.sr_instance_id,
wip_tbl.organization_id,
wip_tbl.inventory_item_id,
wip_tbl.vmi_flag,
decode(sign(to_number(wip_tbl.wip_start_date-l_curr_start_date)),-1,l_curr_start_date,wip_tbl.wip_start_date);
insert into msc_supplies_f (
plan_id, plan_run_id, io_plan_flag,
sr_instance_id, organization_id, inventory_item_id,
project_id, task_id,
supplier_id, supplier_site_id, region_id,
ship_method,
supply_date,
aggr_type, category_set_id, sr_category_id,
supply_type, vmi_flag,
supply_qty,
planned_order_count,
work_order_leadtime,
work_order_count,
work_order_qty,
stockout_days,
drp_supply_as_demand,
return_order_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,
to_number(-23453) inventory_item_id,
f.project_id, f.task_id,
f.supplier_id, f.supplier_site_id, f.region_id,
f.ship_method,
f.supply_date,
to_number(42) aggr_type,
l_category_set_id1 category_set_id,
nvl(q.sr_category_id, -23453),
f.supply_type, f.vmi_flag,
sum(f.supply_qty),
sum(f.planned_order_count),
sum(f.work_order_leadtime),
sum(f.work_order_count),
sum(f.work_order_qty),
sum(f.stockout_days),
sum(f.drp_supply_as_demand),
sum(f.return_order_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_supplies_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.io_plan_flag,
f.sr_instance_id, f.organization_id,
f.project_id, f.task_id,
f.supplier_id, f.supplier_site_id, f.region_id,
f.ship_method,
f.supply_date,
nvl(q.sr_category_id, -23453),
f.supply_type, f.vmi_flag;
insert into msc_item_wips_f (
plan_id, plan_run_id,
sr_instance_id, organization_id, inventory_item_id,
vmi_flag, wip_start_date,
aggr_type, category_set_id, sr_category_id,
wip_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.sr_instance_id, f.organization_id,
to_number(-23453) inventory_item_id,
f.vmi_flag, f.wip_start_date,
to_number(42) aggr_type,
l_category_set_id1 category_set_id,
nvl(q.sr_category_id, -23453),
sum(f.wip_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_wips_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.vmi_flag, f.wip_start_date,
nvl(q.sr_category_id, -23453);
insert into msc_supplies_f (
plan_id, plan_run_id, io_plan_flag,
sr_instance_id, organization_id, inventory_item_id,
project_id, task_id,
supplier_id, supplier_site_id, region_id,
ship_method,
supply_date,
aggr_type, category_set_id, sr_category_id,
supply_type, vmi_flag,
supply_qty,
planned_order_count,
work_order_leadtime,
work_order_count,
work_order_qty,
stockout_days,
drp_supply_as_demand,
return_order_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-mfg_period (1016, 1017, 1018)
select
f.plan_id, f.plan_run_id, f.io_plan_flag,
f.sr_instance_id, f.organization_id, f.inventory_item_id,
f.project_id, f.task_id,
f.supplier_id, f.supplier_site_id, f.region_id,
f.ship_method,
mp.period_start_date supply_date,
decode(f.aggr_type, 42, 1016, 43, 1017, 1018) aggr_type,
f.category_set_id, f.sr_category_id,
f.supply_type, f.vmi_flag,
sum(f.supply_qty),
sum(f.planned_order_count),
sum(f.work_order_leadtime),
sum(f.work_order_count),
sum(f.work_order_qty),
sum(f.stockout_days),
sum(f.drp_supply_as_demand),
sum(f.return_order_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_supplies_f f,
msc_phub_mfg_cal_periods_mv mp
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.supply_date between mp.period_start_date and mp.period_end_date
group by
f.plan_id, f.plan_run_id, f.io_plan_flag,
f.sr_instance_id, f.organization_id, f.inventory_item_id,
f.project_id, f.task_id,
f.supplier_id, f.supplier_site_id, f.region_id,
f.ship_method,
mp.period_start_date,
decode(f.aggr_type, 42, 1016, 43, 1017, 1018),
f.category_set_id, f.sr_category_id,
f.supply_type, f.vmi_flag
union all
-- category-fiscal_period (1019, 1020, 1021)
select
f.plan_id, f.plan_run_id, f.io_plan_flag,
f.sr_instance_id, f.organization_id, f.inventory_item_id,
f.project_id, f.task_id,
f.supplier_id, f.supplier_site_id, f.region_id,
f.ship_method,
fp.start_date supply_date,
decode(f.aggr_type, 42, 1019, 43, 1020, 1021) aggr_type,
f.category_set_id, f.sr_category_id,
f.supply_type, f.vmi_flag,
sum(f.supply_qty),
sum(f.planned_order_count),
sum(f.work_order_leadtime),
sum(f.work_order_count),
sum(f.work_order_qty),
sum(f.stockout_days),
sum(f.drp_supply_as_demand),
sum(f.return_order_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_supplies_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.supply_date between fp.start_date and fp.end_date
group by
f.plan_id, f.plan_run_id, f.io_plan_flag,
f.sr_instance_id, f.organization_id, f.inventory_item_id,
f.project_id, f.task_id,
f.supplier_id, f.supplier_site_id, f.region_id,
f.ship_method,
fp.start_date,
decode(f.aggr_type, 42, 1019, 43, 1020, 1021),
f.category_set_id, f.sr_category_id,
f.supply_type, f.vmi_flag;
insert into msc_item_wips_f (
plan_id, plan_run_id,
sr_instance_id, organization_id, inventory_item_id,
vmi_flag, wip_start_date,
aggr_type, category_set_id, sr_category_id,
wip_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-mfg_period (1016, 1017, 1018)
select
f.plan_id, f.plan_run_id,
f.sr_instance_id, f.organization_id, f.inventory_item_id,
f.vmi_flag,
mp.period_start_date wip_start_date,
decode(f.aggr_type, 42, 1016, 43, 1017, 1018) aggr_type,
f.category_set_id, f.sr_category_id,
sum(f.wip_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_wips_f f,
msc_phub_mfg_cal_periods_mv mp
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.wip_start_date between mp.period_start_date and mp.period_end_date
group by
f.plan_id, f.plan_run_id,
f.sr_instance_id, f.organization_id, f.inventory_item_id,
f.vmi_flag,
mp.period_start_date,
decode(f.aggr_type, 42, 1016, 43, 1017, 1018),
f.category_set_id, f.sr_category_id
union all
-- category-fiscal_period (1019, 1020, 1021)
select
f.plan_id, f.plan_run_id,
f.sr_instance_id, f.organization_id, f.inventory_item_id,
f.vmi_flag,
fp.start_date wip_start_date,
decode(f.aggr_type, 42, 1019, 43, 1020, 1021) aggr_type,
f.category_set_id, f.sr_category_id,
sum(f.wip_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_wips_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.wip_start_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.inventory_item_id,
f.vmi_flag,
fp.start_date,
decode(f.aggr_type, 42, 1019, 43, 1020, 1021),
f.category_set_id, f.sr_category_id;
delete /*+ PARALLEL(mos) */ from msc_supplies_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_supplies_f
where plan_id=p_plan_id and plan_run_id=nvl(p_plan_run_id,plan_run_id);