[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select refresh_mode into l_refresh_mode
from msc_plan_runs
where plan_run_id = p_plan_run_id;
delete from msc_supplies_f
where plan_id = p_plan_id
and plan_run_id = p_plan_run_id
and (p_plan_id, sr_instance_id, organization_id, inventory_item_id) in
(select number1, number2, number3, number4 from msc_hub_query q where q.query_id = l_item_rn_qid);
msc_phub_util.log('msc_supplies_f, delete='||sql%rowcount||', l_rowcount1='||l_rowcount1);
delete from msc_item_wips_f
where plan_id = p_plan_id
and plan_run_id = p_plan_run_id
and (p_plan_id, sr_instance_id, organization_id, inventory_item_id) in
(select number1, number2, number3, number4 from msc_hub_query q where q.query_id = l_item_rn_qid);
msc_phub_util.log('msc_item_wips_f, delete='||sql%rowcount||', l_rowcount2='||l_rowcount2);
select plan_type, sr_instance_id
into l_plan_type, l_sr_instance_id
from msc_plan_runs
where plan_id=p_plan_id
and plan_run_id=p_plan_run_id;
select trunc(plan_start_date), trunc(plan_cutoff_date)
into l_plan_start_date, l_plan_cutoff_date
from msc_plan_runs
where plan_run_id = p_plan_run_id;
insert into msc_supplies_f (
plan_id, -- plan_id
plan_run_id,
io_plan_flag, --- this flag indidate whether it is an io plan
sr_instance_id,
organization_id,
subinventory_code,
owning_inst_id,
owning_org_id,
source_org_instance_id,
source_organization_id,
inventory_item_id,
end_item_inst_id,
end_item_org_id,
end_item_id,
parent_model_item_id,
project_id,
task_id,
supplier_id,
supplier_site_id,
customer_region_id,
ship_method,
part_condition,
supply_date,
aggr_type, category_set_id, sr_category_id,
end_item_cat_id,
parent_model_cat_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,
supply_volume,
po_reschedule_count,
po_count,
po_cancel_count,
buy_order_value,
buy_order_value2,
buy_order_count,
drp_supply_as_demand,
return_order_qty,
return_fcst,
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, -- plan_id
p_plan_run_id, -- plan_run_id,
decode(l_plan_type,4,1,9,1,0) io_plan_flag,
supply_tbl.sr_instance_id,
supply_tbl.organization_id,
supply_tbl.subinventory_code,
supply_tbl.owning_inst_id,
supply_tbl.owning_org_id,
supply_tbl.source_org_instance_id,
supply_tbl.source_organization_id,
supply_tbl.inventory_item_id,
supply_tbl.end_item_inst_id,
supply_tbl.end_item_org_id,
supply_tbl.end_item_id,
supply_tbl.parent_model_item_id,
supply_tbl.project_id,
supply_tbl.task_id,
supply_tbl.supplier_id,
supply_tbl.supplier_site_id,
supply_tbl.customer_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,
supply_tbl.part_condition,
decode(sign(to_number(supply_tbl.supply_date-l_plan_start_date)),
-1, msc_hub_calendar.last_work_date(p_plan_id,l_plan_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_date,
to_number(0) aggr_type,
to_number(-23453) category_set_id,
to_number(-23453) sr_category_id,
to_number(-23453) end_item_cat_id,
to_number(-23453) parent_model_cat_id,
supply_tbl.supply_type,
supply_tbl.vmi_flag,
sum(supply_tbl.supply_qty) supply_qty,
sum(case when l_plan_type <>5 then supply_tbl.planned_order_count else to_number(null) end ) planned_order_count,
sum(case when l_plan_type <>5 then supply_tbl.work_order_leadtime else to_number(null) end ) work_order_leadtime,
sum(case when l_plan_type <>5 then supply_tbl.work_order_count else to_number(null) end ) work_order_count,
sum(case when l_plan_type <>5 then supply_tbl.work_order_qty else to_number(null) end ) work_order_qty,
sum(case when l_plan_type <>5 then supply_tbl.supply_volume else to_number(null) end ) supply_volume,
sum(supply_tbl.po_reschedule_count) po_reschedule_count,
sum(supply_tbl.po_count) po_count,
sum(supply_tbl.po_cancel_count) po_cancel_count,
sum(supply_tbl.buy_order_value) buy_order_value,
sum(supply_tbl.buy_order_value * decode(supply_tbl.currency_code,
fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate,0))) buy_order_value2,
sum(supply_tbl.buy_order_count) buy_order_count,
sum(case when l_plan_type <>5 then supply_tbl.drp_supply_as_demand else to_number(null) end ) drp_supply_as_demand,
sum(case when l_plan_type <>5 then supply_tbl.return_order_qty else to_number(null) end ) return_order_qty,
sum(case when l_plan_type <>5 then supply_tbl.return_fcst else to_number(null) end ) return_fcst,
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 /*+ ordered */
decode(sign(ms.organization_id), -1, -23453, ms.sr_instance_id) sr_instance_id,
decode(sign(ms.organization_id), -1, -23453, ms.organization_id) organization_id,
nvl(mtp.currency_code, l_owning_currency_code) currency_code,
decode(ms.order_type, 18, nvl(ms.subinventory_code, '-23453'), '-23453') subinventory_code,
decode(sign(ms.sr_instance_id), -1, l_sr_instance_id, ms.sr_instance_id) owning_inst_id,
decode(sign(ms.organization_id), -1, msc_hub_calendar.get_item_org(p_plan_id, ms.inventory_item_id,
decode(sign(ms.sr_instance_id), -1, l_sr_instance_id, ms.sr_instance_id)),
ms.organization_id) owning_org_id,
decode(ms.source_sr_instance_id,null,-23453,0,-23453,ms.source_sr_instance_id) source_org_instance_id,
decode(ms.source_organization_id,null,-23453,0,-23453,ms.source_organization_id) source_organization_id,
ms.inventory_item_id,
nvl(pg.end_item_inst_id, -23453) end_item_inst_id,
nvl(pg.end_item_org_id, -23453) end_item_org_id,
nvl(pg.end_item_id, -23453) end_item_id,
nvl(pg.parent_model_item_id, -23453) parent_model_item_id,
nvl(ms.project_id,-23453) project_id,
nvl(ms.task_id,-23453) task_id,
nvl(case when ms.order_type in (5,51) then ms.source_supplier_id else ms.supplier_id end, -23453) supplier_id,
nvl(case when ms.order_type in (5,51) then ms.source_supplier_site_id else ms.supplier_site_id end, -23453) supplier_site_id,
decode(l_plan_type, 8, nvl(ms.zone_id,-23453), -23453) customer_region_id,
nvl(ms.ship_method, '-23453') ship_method,
nvl(ms.item_type_value,1) part_condition,
trunc(nvl(ms.firm_date,ms.new_schedule_date)) supply_date,
ms.order_type supply_type,
nvl(msi.vmi_flag,0) vmi_flag,
sum(nvl(pg.supply_qty,
decode(msi.base_item_id,null,
decode(ms.disposition_status_type,2, 0,
decode(ms.last_unit_completion_date, null, nvl(ms.firm_quantity, ms.new_order_quantity), ms.daily_rate)),
decode(ms.last_unit_completion_date, null, nvl(ms.firm_quantity, 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(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(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(case when ms.order_type in (3,4,7,13,14,15,16,17,27,28,30,88)
and (msi.base_item_id is not null or ms.disposition_status_type<>2)
then decode(ms.last_unit_completion_date, null, ms.new_order_quantity, ms.daily_rate)
when ms.order_type in (5)
and 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) in (3,4)
and (msi.base_item_id is not null or ms.disposition_status_type<>2)
then decode(ms.last_unit_completion_date, null, ms.new_order_quantity, ms.daily_rate)
else null end) work_order_qty,
--- order_type in (1,2,18) and nvl(ms.item_type_id,401) = 401 and nvl(ms.item_type_value,1) = 2
sum(case when l_plan_type in (8,9) and ms.order_type in (1,2,18)
and nvl(ms.item_type_id,401)=401 and nvl(ms.item_type_value,1)=2
and (msi.base_item_id is not null or ms.disposition_status_type<>2)
then decode(ms.last_unit_completion_date, null, ms.new_order_quantity, ms.daily_rate)
else null end) return_order_qty,
sum(decode(ms.order_type,81,ms.new_order_quantity,0)) return_fcst,
sum(case when l_plan_type=5
and (ms.order_type in (1,51) or (ms.order_type in (2) and ms.supplier_id is not null))
and ms.organization_id<>ms.source_organization_id
and (msi.base_item_id is not null or ms.disposition_status_type<>2)
then decode(ms.last_unit_completion_date,null,ms.new_order_quantity,ms.daily_rate)
end) drp_supply_as_demand,
sum(case when ms.order_type=5
and nvl(ms.source_organization_id,-23453)<>ms.organization_id then nvl(ms.firm_quantity, ms.new_order_quantity)
when ms.order_type in (1,2,8,51,53,76,80,87) then ms.new_order_quantity else null end) supply_volume,
sum(case when ms.order_type in (1) and ms.reschedule_flag is not null
and ms.new_schedule_date<>ms.old_schedule_date
then 1 else 0 end) po_reschedule_count,
sum(case when ms.order_type in (1) then 1 else 0 end) po_count,
sum(case when ms.order_type in (1) and ms.disposition_status_type=2
then 1 else 0 end) po_cancel_count,
sum(case when (ms.order_type in (1,2,76) or
(ms.order_type in (5) and msi.purchasing_enabled_flag=1))
then ms.new_order_quantity * nvl(ms.delivery_price, msi.standard_cost)
else null end) buy_order_value,
sum(case when (ms.order_type in (1,2,76) or
(ms.order_type in (5) and msi.purchasing_enabled_flag=1))
then 1 else 0 end) buy_order_count
from
msc_supplies ms,
(select
mfp.transaction_id,
mfp.sr_instance_id,
mfp2.sr_instance_id end_item_inst_id,
mfp2.organization_id end_item_org_id,
mfp2.inventory_item_id end_item_id,
nvl(decode(l_pegging_granularity, 1, md.parent_model_item_id, md.top_model_item_id), -23453) parent_model_item_id,
sum(mfp.allocated_quantity) supply_qty
from msc_full_pegging mfp,
msc_full_pegging mfp2,
msc_demands md,
msc_apcc_item_d msi
where l_pegging_granularity in (0,1)
and mfp.plan_id=p_plan_id
and mfp.plan_id=mfp2.plan_id
and mfp.end_pegging_id=mfp2.end_pegging_id
and mfp2.prev_pegging_id is null
and mfp2.plan_id=md.plan_id(+)
and mfp2.demand_id=md.demand_id(+)
and mfp.plan_id=msi.plan_id
and mfp.sr_instance_id=msi.sr_instance_id
and mfp.organization_id=msi.organization_id
and mfp.inventory_item_id=msi.inventory_item_id
and msi.pegging_sr_category_id>0
group by
mfp.transaction_id,
mfp.sr_instance_id,
mfp2.sr_instance_id,
mfp2.organization_id,
mfp2.inventory_item_id,
nvl(decode(l_pegging_granularity, 1, md.parent_model_item_id, md.top_model_item_id), -23453)
) pg,
msc_apcc_item_d msi,
msc_trading_partners mtp
where ms.plan_id = msi.plan_id
and decode(sign(ms.sr_instance_id), -1, l_sr_instance_id, ms.sr_instance_id) = msi.sr_instance_id
and decode(sign(ms.organization_id), -1, msc_hub_calendar.get_item_org(p_plan_id, ms.inventory_item_id,
decode(sign(ms.sr_instance_id), -1, l_sr_instance_id, ms.sr_instance_id)),
ms.organization_id) = msi.organization_id
and ms.inventory_item_id = msi.inventory_item_id
and ms.plan_id=p_plan_id
and not (l_plan_type=8 and ms.order_type in (3)) -- bug 9123354, 10044668
and (p_plan_id <> -1
or ( p_plan_id = -1
and ms.sr_instance_id = l_sr_instance_id
and (l_refresh_mode = 1
or (l_refresh_mode = 2 and (p_plan_id, ms.sr_instance_id, ms.organization_id, ms.inventory_item_id) in
(select number1, number2, number3, number4 from msc_hub_query q where q.query_id = l_item_rn_qid) ) )
and trunc(nvl(ms.firm_date,ms.new_schedule_date)) between l_plan_start_date and l_plan_cutoff_date
)
)
and ms.transaction_id=pg.transaction_id(+)
and ms.sr_instance_id=pg.sr_instance_id(+)
and ms.sr_instance_id=mtp.sr_instance_id
and ms.organization_id=mtp.sr_tp_id
and mtp.partner_type=3
group by
decode(sign(ms.organization_id), -1, -23453, ms.sr_instance_id),
decode(sign(ms.organization_id), -1, -23453, ms.organization_id),
nvl(mtp.currency_code, l_owning_currency_code),
decode(ms.order_type, 18, nvl(ms.subinventory_code, '-23453'), '-23453'),
decode(sign(ms.sr_instance_id), -1, l_sr_instance_id, ms.sr_instance_id),
decode(sign(ms.organization_id), -1, msc_hub_calendar.get_item_org(p_plan_id, ms.inventory_item_id,
decode(sign(ms.sr_instance_id), -1, l_sr_instance_id, ms.sr_instance_id)),
ms.organization_id),
decode(ms.source_sr_instance_id,null,-23453,0,-23453,ms.source_sr_instance_id),
decode(ms.source_organization_id,null,-23453,0,-23453,ms.source_organization_id),
ms.inventory_item_id,
nvl(pg.end_item_inst_id, -23453),
nvl(pg.end_item_org_id, -23453),
nvl(pg.end_item_id, -23453),
nvl(pg.parent_model_item_id, -23453),
nvl(ms.project_id,-23453),
nvl(ms.task_id,-23453),
nvl(case when ms.order_type in (5,51) then ms.source_supplier_id else ms.supplier_id end, -23453),
nvl(case when ms.order_type in (5,51) then ms.source_supplier_site_id else ms.supplier_site_id end, -23453),
decode(l_plan_type, 8, nvl(ms.zone_id,-23453), -23453),
nvl(ms.ship_method, '-23453'),
nvl(ms.item_type_value,1),
trunc(nvl(ms.firm_date,ms.new_schedule_date)),
ms.order_type,
nvl(msi.vmi_flag,0)
) supply_tbl,
msc_currency_conv_mv mcc
where mcc.from_currency(+)=supply_tbl.currency_code
and mcc.to_currency(+)=fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
and mcc.calendar_date(+)=supply_tbl.supply_date
group by
decode(l_plan_type,4,1,9,1,0),
supply_tbl.sr_instance_id,
supply_tbl.organization_id,
supply_tbl.subinventory_code,
supply_tbl.owning_inst_id,
supply_tbl.owning_org_id,
supply_tbl.source_org_instance_id,
supply_tbl.source_organization_id,
supply_tbl.inventory_item_id,
supply_tbl.end_item_inst_id,
supply_tbl.end_item_org_id,
supply_tbl.end_item_id,
supply_tbl.parent_model_item_id,
supply_tbl.project_id,
supply_tbl.task_id,
supply_tbl.supplier_id,
supply_tbl.supplier_site_id,
supply_tbl.customer_region_id,
supply_tbl.ship_method,
supply_tbl.part_condition,
decode(sign(to_number(supply_tbl.supply_date-l_plan_start_date)),
-1, msc_hub_calendar.last_work_date(p_plan_id,l_plan_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;
msc_phub_util.log('msc_supplies_f, insert='||sql%rowcount||', l_rowcount1='||l_rowcount1);
insert into msc_item_wips_f (
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,
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, -- 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_plan_start_date)),-1,l_plan_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),
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
ms.sr_instance_id,
ms.organization_id,
ms.inventory_item_id,
nvl(msi.vmi_flag, 0) vmi_flag,
trunc(nvl(nvl(ms.new_wip_start_date,ms.first_unit_start_date),l_plan_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(case when ms.order_type in (3,4,7,13,14,15,16,27,28,30,88)
and (msi.base_item_id is not null or ms.disposition_status_type<>2)
then decode(ms.last_unit_completion_date, null, ms.new_order_quantity, ms.daily_rate)
when ms.order_type in (5,17)
and 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) in (3,4)
and (msi.base_item_id is not null or ms.disposition_status_type<>2)
then decode(ms.last_unit_completion_date, null, ms.new_order_quantity, ms.daily_rate)
else null end) wip_qty
from
msc_supplies ms,
msc_apcc_item_d msi
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 l_plan_type not in (4,9) --- exclude io plan
and (p_plan_id <> -1
or ( p_plan_id = -1
and ms.sr_instance_id = l_sr_instance_id
and (l_refresh_mode = 1
or (l_refresh_mode = 2 and (p_plan_id, ms.sr_instance_id, ms.organization_id, ms.inventory_item_id) in
(select number1, number2, number3, number4 from msc_hub_query q where q.query_id = l_item_rn_qid) ) )
and trunc(nvl(nvl(ms.new_wip_start_date,ms.first_unit_start_date),l_plan_start_date)) between l_plan_start_date and l_plan_cutoff_date
)
)
group by
ms.sr_instance_id,
ms.organization_id,
ms.inventory_item_id,
nvl(msi.vmi_flag, 0),
trunc(nvl(nvl(ms.new_wip_start_date,ms.first_unit_start_date),l_plan_start_date)) ) wip_tbl
-- where l_plan_type <> 6
group by
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_plan_start_date)),-1,l_plan_start_date,wip_tbl.wip_start_date);
msc_phub_util.log('msc_item_wips_f, insert='||sql%rowcount||', l_rowcount2='||l_rowcount2);
delete from msc_supplies_f
where plan_id=p_plan_id and plan_run_id=p_plan_run_id and aggr_type>0;
msc_phub_util.log('msc_supply_pkg.summarize_supplies_f, delete='||sql%rowcount);
insert into msc_supplies_f (
plan_id, plan_run_id, io_plan_flag,
sr_instance_id, organization_id, subinventory_code,
owning_inst_id, owning_org_id, inventory_item_id,
source_org_instance_id, source_organization_id,
end_item_inst_id,
end_item_org_id,
end_item_id,
parent_model_item_id,
project_id, task_id,
supplier_id, supplier_site_id,
ship_method, customer_region_id,
part_condition,
supply_date,
aggr_type, category_set_id, sr_category_id,
end_item_cat_id,
parent_model_cat_id,
supply_type, vmi_flag,
supply_qty,
planned_order_count,
work_order_leadtime,
work_order_count,
work_order_qty,
supply_volume,
po_reschedule_count,
po_count,
po_cancel_count,
buy_order_value,
buy_order_value2,
buy_order_count,
drp_supply_as_demand,
return_order_qty,
return_fcst,
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.subinventory_code,
f.owning_inst_id, to_number(-23453) owning_org_id,
to_number(-23453) inventory_item_id,
f.source_org_instance_id, f.source_organization_id,
f.end_item_inst_id,
to_number(-23453) end_item_org_id,
to_number(-23453) end_item_id,
to_number(-23453) parent_model_item_id,
f.project_id, f.task_id,
f.supplier_id, f.supplier_site_id,
f.ship_method, f.customer_region_id,
f.part_condition,
f.supply_date,
to_number(42) aggr_type,
l_category_set_id1 category_set_id,
nvl(ic1.sr_category_id, -23453),
nvl(ic2.sr_category_id, -23453),
nvl(ic3.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.supply_volume),
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),
sum(f.drp_supply_as_demand),
sum(f.return_order_qty),
sum(f.return_fcst),
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 ic1,
msc_phub_item_categories_mv ic2,
msc_phub_item_categories_mv ic3
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=ic1.sr_instance_id(+)
and f.owning_org_id=ic1.organization_id(+)
and f.inventory_item_id=ic1.inventory_item_id(+)
and ic1.category_set_id(+)=l_category_set_id1
and f.end_item_inst_id=ic2.sr_instance_id(+)
and f.end_item_org_id=ic2.organization_id(+)
and f.end_item_id=ic2.inventory_item_id(+)
and ic2.category_set_id(+)=l_category_set_id1
and f.end_item_inst_id=ic3.sr_instance_id(+)
and f.end_item_org_id=ic3.organization_id(+)
and f.parent_model_item_id=ic3.inventory_item_id(+)
and ic3.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.subinventory_code,
f.owning_inst_id,
f.source_org_instance_id, f.source_organization_id,
f.end_item_inst_id,
f.project_id, f.task_id,
f.supplier_id, f.supplier_site_id,
f.ship_method, f.customer_region_id,
f.part_condition,
f.supply_date,
nvl(ic1.sr_category_id, -23453),
nvl(ic2.sr_category_id, -23453),
nvl(ic3.sr_category_id, -23453),
f.supply_type, f.vmi_flag;
insert into msc_supplies_f (
plan_id, plan_run_id, io_plan_flag,
sr_instance_id, organization_id, subinventory_code,
owning_inst_id, owning_org_id, inventory_item_id,
source_org_instance_id, source_organization_id,
end_item_inst_id,
end_item_org_id,
end_item_id,
parent_model_item_id,
project_id, task_id,
supplier_id, supplier_site_id,
ship_method, customer_region_id,
part_condition,
supply_date,
aggr_type, category_set_id, sr_category_id,
end_item_cat_id,
parent_model_cat_id,
supply_type, vmi_flag,
supply_qty,
planned_order_count,
work_order_leadtime,
work_order_count,
work_order_qty,
supply_volume,
po_reschedule_count,
po_count,
po_cancel_count,
buy_order_value,
buy_order_value2,
buy_order_count,
drp_supply_as_demand,
return_order_qty,
return_fcst,
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.subinventory_code,
f.owning_inst_id, f.owning_org_id, f.inventory_item_id,
f.source_org_instance_id, f.source_organization_id,
f.end_item_inst_id,
f.end_item_org_id,
f.end_item_id,
f.parent_model_item_id,
f.project_id, f.task_id,
f.supplier_id, f.supplier_site_id,
f.ship_method, f.customer_region_id,
f.part_condition,
d.mfg_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.end_item_cat_id,
f.parent_model_cat_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.supply_volume),
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),
sum(f.drp_supply_as_demand),
sum(f.return_order_qty),
sum(f.return_fcst),
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_dates_mv d
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 = d.calendar_date
and d.mfg_period_start_date is not null
group by
f.plan_id, f.plan_run_id, f.io_plan_flag,
f.sr_instance_id, f.organization_id, f.subinventory_code,
f.owning_inst_id, f.owning_org_id, f.inventory_item_id,
f.source_org_instance_id, f.source_organization_id,
f.end_item_inst_id,
f.end_item_org_id,
f.end_item_id,
f.parent_model_item_id,
f.project_id, f.task_id,
f.supplier_id, f.supplier_site_id,
f.ship_method, f.customer_region_id,
f.part_condition,
d.mfg_period_start_date,
decode(f.aggr_type, 42, 1016, 43, 1017, 1018),
f.category_set_id, f.sr_category_id,
f.end_item_cat_id,
f.parent_model_cat_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.subinventory_code,
f.owning_inst_id, f.owning_org_id, f.inventory_item_id,
f.source_org_instance_id, f.source_organization_id,
f.end_item_inst_id,
f.end_item_org_id,
f.end_item_id,
f.parent_model_item_id,
f.project_id, f.task_id,
f.supplier_id, f.supplier_site_id,
f.ship_method, f.customer_region_id,
f.part_condition,
d.fis_period_start_date supply_date,
decode(f.aggr_type, 42, 1019, 43, 1020, 1021) aggr_type,
f.category_set_id, f.sr_category_id,
f.end_item_cat_id,
f.parent_model_cat_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.supply_volume),
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),
sum(f.drp_supply_as_demand),
sum(f.return_order_qty),
sum(f.return_fcst),
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_dates_mv d
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 = d.calendar_date
and d.fis_period_start_date is not null
group by
f.plan_id, f.plan_run_id, f.io_plan_flag,
f.sr_instance_id, f.organization_id, f.subinventory_code,
f.owning_inst_id, f.owning_org_id, f.inventory_item_id,
f.source_org_instance_id, f.source_organization_id,
f.end_item_inst_id,
f.end_item_org_id,
f.end_item_id,
f.parent_model_item_id,
f.project_id, f.task_id,
f.supplier_id, f.supplier_site_id,
f.ship_method, f.customer_region_id,
f.part_condition,
d.fis_period_start_date,
decode(f.aggr_type, 42, 1019, 43, 1020, 1021),
f.category_set_id, f.sr_category_id,
f.end_item_cat_id,
f.parent_model_cat_id,
f.supply_type, f.vmi_flag;
delete from msc_item_wips_f
where plan_id=p_plan_id and plan_run_id=p_plan_run_id and aggr_type>0;
msc_phub_util.log('msc_supply_pkg.summarize_item_wips_f, delete='||sql%rowcount);
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_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,
d.mfg_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_dates_mv d
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 = d.calendar_date
and d.mfg_period_start_date is not null
group by
f.plan_id, f.plan_run_id,
f.sr_instance_id, f.organization_id, f.inventory_item_id,
f.vmi_flag,
d.mfg_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,
d.fis_period_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_dates_mv d
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 = d.calendar_date
and d.fis_period_start_date is not null
group by
f.plan_id, f.plan_run_id,
f.sr_instance_id, f.organization_id, f.inventory_item_id,
f.vmi_flag,
d.fis_period_start_date,
decode(f.aggr_type, 42, 1019, 43, 1020, 1021),
f.category_set_id, f.sr_category_id;
delete from msc_st_supplies_f where st_transaction_id=p_st_transaction_id;
' insert into msc_st_supplies_f('||
' st_transaction_id,'||
' error_code,'||
' sr_instance_id,'||
' organization_id,'||
' inventory_item_id,'||
' supplier_id,'||
' supplier_site_id,'||
' project_id,'||
' task_id,'||
' organization_code,'||
' item_name,'||
' supplier_name,'||
' supplier_site_code,'||
' project_number,'||
' task_number,'||
' ship_method,'||
' supply_type,'||
' owning_org_code,'||
' owning_inst_id,'||
' owning_org_id,';
' last_updated_by, last_update_date, last_update_login'||
' )'||
' select'||
' :p_st_transaction_id,'||
' 0,'||
' f.sr_instance_id,'||
' f.organization_id,'||
' f.inventory_item_id,'||
' f.supplier_id,'||
' f.supplier_site_id,'||
' f.project_id,'||
' f.task_id,'||
' mtp.organization_code,'||
' mi.item_name,'||
' decode(f.supplier_id, -23453, null, smv.supplier_name),'||
' decode(f.supplier_site_id, -23453, null, smv.supplier_site_code),'||
' proj.project_number,'||
' proj.task_number,'||
' f.ship_method,'||
' f.supply_type,';
' (select p.sr_instance_id, p.organization_id,'||
' p.project_id, t.task_id, p.project_number, t.task_number'||
' from '||l_apps_schema||'.msc_projects'||l_suffix||' p, '||l_apps_schema||'.msc_project_tasks'||l_suffix||' t'||
' where p.project_id=t.project_id'||
' and p.plan_id=t.plan_id'||
' and p.sr_instance_id=t.sr_instance_id'||
' and p.organization_id=t.organization_id'||
' and p.plan_id=-1) proj';
msc_phub_util.log('msc_supply_pkg.export_supplies_f: inserted='||sql%rowcount);
delete from msc_st_item_wips_f where st_transaction_id=p_st_transaction_id;
' insert into msc_st_item_wips_f('||
' st_transaction_id,'||
' error_code,'||
' sr_instance_id,'||
' organization_id,'||
' inventory_item_id,'||
' organization_code,'||
' item_name,'||
' vmi_flag,'||
' wip_start_date,'||
' wip_qty,'||
' created_by, creation_date,'||
' last_updated_by, last_update_date, last_update_login'||
' )'||
' select'||
' :p_st_transaction_id,'||
' 0,'||
' f.sr_instance_id,'||
' f.organization_id,'||
' f.inventory_item_id,'||
' mtp.organization_code,'||
' mi.item_name,'||
' f.vmi_flag,'||
' f.wip_start_date,'||
' f.wip_qty,'||
' fnd_global.user_id, sysdate,'||
' fnd_global.user_id, sysdate, fnd_global.login_id'||
' from'||
' '||l_apps_schema||'.msc_item_wips_f'||l_suffix||' f,'||
' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp,'||
' '||l_apps_schema||'.msc_items'||l_suffix||' mi'||
' where f.plan_run_id=:p_plan_run_id'||
' and f.aggr_type=0'||
' and mtp.partner_type(+)=3'||
' and mtp.sr_instance_id(+)=f.sr_instance_id'||
' and mtp.sr_tp_id(+)=f.organization_id'||
' and mi.inventory_item_id(+)=f.inventory_item_id';
msc_phub_util.log('msc_supply_pkg.export_item_wips_f: inserted='||sql%rowcount);
msc_phub_util.log('msc_supply_pkg.import_supplies_f: insert into msc_supplies_f');
insert into msc_supplies_f (
plan_id,
plan_run_id,
sr_instance_id,
organization_id,
subinventory_code,
owning_inst_id,
owning_org_id,
source_org_instance_id,
source_organization_id,
inventory_item_id,
supplier_id,
supplier_site_id,
customer_region_id,
end_item_inst_id,
end_item_org_id,
end_item_id,
parent_model_item_id,
project_id,
task_id,
ship_method,
supply_type,
part_condition,
io_plan_flag,
vmi_flag,
supply_date,
supply_qty,
planned_order_count,
planned_order_itf_count,
planned_order_gmod_count,
planned_order_bwo_count,
work_order_leadtime,
work_order_count,
qty_pegged_to_excess,
work_order_qty,
drp_supply_as_demand,
return_order_qty,
return_fcst,
supply_volume,
po_reschedule_count,
po_count,
po_cancel_count,
buy_order_value,
buy_order_value2,
buy_order_count,
aggr_type, category_set_id, sr_category_id,
created_by, creation_date,
last_updated_by, last_update_date, last_update_login
)
select
p_plan_id,
p_plan_run_id,
nvl(sr_instance_id, -23453),
nvl(organization_id, -23453),
nvl(subinventory_code, '-23453'),
nvl(owning_inst_id, -23453),
nvl(owning_org_id, -23453),
nvl(source_org_instance_id, -23453),
nvl(source_organization_id, -23453),
nvl(inventory_item_id, -23453),
nvl(supplier_id, -23453),
nvl(supplier_site_id, -23453),
nvl(customer_region_id, -23453),
nvl(end_item_inst_id, -23453),
nvl(end_item_org_id, -23453),
nvl(end_item_id, -23453),
nvl(parent_model_item_id, -23453),
nvl(project_id, -23453),
nvl(task_id, -23453),
ship_method,
supply_type,
part_condition,
decode(p_plan_type, 4, 1, 0) io_plan_flag,
vmi_flag,
supply_date,
supply_qty,
planned_order_count,
planned_order_itf_count,
planned_order_gmod_count,
planned_order_bwo_count,
work_order_leadtime,
work_order_count,
qty_pegged_to_excess,
work_order_qty,
drp_supply_as_demand,
return_order_qty,
return_fcst,
supply_volume,
po_reschedule_count,
po_count,
po_cancel_count,
buy_order_value,
buy_order_value2,
buy_order_count,
0, -23453, -23453,
fnd_global.user_id, sysdate,
fnd_global.user_id, sysdate, fnd_global.login_id
from msc_st_supplies_f
where st_transaction_id=p_st_transaction_id and error_code=0;
msc_phub_util.log('msc_supply_pkg.import_supplies_f: inserted='||sql%rowcount);
msc_phub_util.log('msc_supply_pkg.import_item_wips_f: insert into msc_item_wips_f');
insert into msc_item_wips_f (
plan_id,
plan_run_id,
sr_instance_id,
organization_id,
inventory_item_id,
vmi_flag,
wip_start_date,
wip_qty,
aggr_type, category_set_id, sr_category_id,
created_by, creation_date,
last_updated_by, last_update_date, last_update_login
)
select
p_plan_id,
p_plan_run_id,
nvl(sr_instance_id, -23453),
nvl(organization_id, -23453),
nvl(inventory_item_id, -23453),
vmi_flag,
wip_start_date,
wip_qty,
0, -23453, -23453,
fnd_global.user_id, sysdate,
fnd_global.user_id, sysdate, fnd_global.login_id
from msc_st_item_wips_f
where st_transaction_id=p_st_transaction_id and error_code=0;
msc_phub_util.log('msc_supply_pkg.import_item_wips_f: inserted='||sql%rowcount);