[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select trunc(plan_cutoff_date) into l_plan_cutoff_date
from msc_plan_runs
where plan_run_id=p_plan_run_id;
select refresh_mode into l_refresh_mode
from msc_plan_runs
where plan_run_id=p_plan_run_id;
delete from msc_item_inventory_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_inventory_f, delete='||sql%rowcount||', l_rowcount1='||l_rowcount1);
delete from msc_item_orders_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_orders_f, delete='||sql%rowcount||', l_rowcount2='||l_rowcount2);
select plan_type, sr_instance_id, plan_start_date, plan_cutoff_date, temp_transfer_id
into l_plan_type, l_sr_instance_id, l_plan_start_date, l_plan_cutoff_date, l_transfer_id
from msc_plan_runs
where plan_id=p_plan_id
and plan_run_id=p_plan_run_id;
select nvl(enforce_wrh_cpty,2)
into l_enforce_wh_cpty
from msc_plans
where plan_id=p_plan_id;
insert /*+ append nologging */ into msc_st_item_orders_f (
st_transaction_id,
error_code,
sr_instance_id,
organization_id,
subinventory_code,
owning_inst_id,
owning_org_id,
inventory_item_id,
order_date,
demand_qty,
indep_demand_qty,
indep_demand_value,
dep_demand_qty,
sales_order_value,
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)
select
l_transfer_id,
to_number(0),
f.sr_instance_id,
f.organization_id,
'-23453',
f.owning_inst_id,
f.owning_org_id,
f.inventory_item_id,
d.date2 order_date,
-- 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(case when l_plan_type=5 then
decode(nvl(f.order_type,0), 0,0, -1,0,
-29,decode(f.organization_id,-23453,0,nvl(f.demand_qty,0)),
-31,0,
nvl(f.demand_qty,0))
--- exclude defective demand from total demand qty for spp
when l_plan_type=8 and nvl(f.part_condition,1)=2
then 0
else
decode(nvl(f.order_type,0), 0,0, -5,0, -22,0,
-29,decode(f.organization_id,-23453,0,nvl(f.demand_qty,0)),
-31,0,
nvl(f.demand_qty,0))
end) demand_qty,
sum(nvl(f.indep_demand_qty,0)) indep_demand_qty,
sum(nvl(f.indep_demand_qty,0) * nvl(i.standard_cost,0)) indep_demand_value,
---- 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) * i.standard_cost) indep_demand_value,
--bnaghi -dep_demand_qty not calculated for DRP plan
decode(l_plan_type,5,to_number(null), sum(decode(nvl(f.order_type,0),
-1,decode(l_plan_type,5,0,nvl(f.demand_qty,0)), -- exclude drp planned demand from dep demand
-2,nvl(f.demand_qty,0),
-3,nvl(f.demand_qty,0),
-4,nvl(f.demand_qty,0),
-24,nvl(f.demand_qty,0),
-25,nvl(f.demand_qty,0),
0)) ) dep_demand_qty,
--bnaghi -sales_order_value and forecast_qty not calculated for DRP plan
decode(l_plan_type,5,to_number(null),sum(nvl(f.sales_order_qty,0) * nvl(i.list_price,0)*(1-nvl(i.average_discount,0)/100))) sales_order_value,
decode(l_plan_type,5,to_number(null),sum(decode(nvl(f.order_type,0), -29,nvl(f.demand_qty,0), 0))) 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_demands_f f,
msc_hub_query d,
msc_apcc_item_d i
where f.plan_run_id=p_plan_run_id
and f.aggr_type=0
and d.query_id=l_qid_last_date1
and f.order_date between d.date1 and d.date2
and f.plan_id=i.plan_id
and f.sr_instance_id=i.sr_instance_id
and f.organization_id=i.organization_id
and f.inventory_item_id=i.inventory_item_id
and (p_plan_id <> -1
or (p_plan_id=-1
and f.sr_instance_id=l_sr_instance_id
and (l_refresh_mode=1
or (l_refresh_mode=2 and (f.plan_id, f.sr_instance_id, f.organization_id, f.inventory_item_id) in
(select number1, number2, number3, number4 from msc_hub_query q where q.query_id=l_item_rn_qid)))))
group by
f.sr_instance_id,
f.organization_id,
f.owning_inst_id,
f.owning_org_id,
f.inventory_item_id,
d.date2;
msc_phub_util.log('insert into msc_st_item_orders_f:demands: '||sql%rowcount);
insert /*+ append nologging */ into msc_st_item_orders_f (
st_transaction_id,
error_code,
sr_instance_id,
organization_id,
subinventory_code,
owning_inst_id,
owning_org_id,
inventory_item_id,
order_date,
supply_qty,
demand_qty,
planned_order_qty,
return_order_value,
make_order_qty,
make_order_leadtime,
make_order_count,
item_leadtime,
onhand_qty,
onhand_value,
onhand_usable,
onhand_defective,
intransit_usable,
intransit_defective,
plnd_xfer_usable,
plnd_xfer_defective,
supply_qty_usable,
supply_qty_defective,
scheduled_rept_qty,
scheduled_rept_value,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
select
l_transfer_id,
to_number(0),
f.sr_instance_id,
f.organization_id,
f.subinventory_code,
f.owning_inst_id,
f.owning_org_id,
f.inventory_item_id,
d.date2 order_date,
--- exclude onhand from total supply for drp
sum(case when l_plan_type=5 and f.supply_type in (18)
then null
when f.supply_type in (4,0)
then 0
--- exclude defective supply from total supply qty for spp
when l_plan_type=8 and nvl(f.part_condition,1)=2
then 0
else nvl(f.supply_qty,0) end) supply_qty,
--- in drp, supply(1,2,51) is demand
sum(nvl(f.drp_supply_as_demand,0)) demand_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))*/
-- bnaghi : not available for DRP plan
decode(l_plan_type,5,to_number(null), sum(case when f.supply_type in (5,76,77,78,79) then f.supply_qty else 0 end)) planned_order_qty,
decode(l_plan_type,5,to_number(null),sum(nvl(f.return_order_qty,0) * nvl(i.list_price,0)*(1-nvl(i.average_discount,0)/100))) return_order_value,
decode(l_plan_type,5,to_number(null),sum(nvl(f.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
decode(l_plan_type,5,to_number(null),sum(nvl(f.work_order_leadtime,0))) make_order_leadtime,
decode(l_plan_type,5,to_number(null),sum(nvl(f.work_order_count,0))) make_order_count,
decode(l_plan_type,5,to_number(null),avg(i.fixed_lead_time)) item_leadtime,
sum(decode(nvl(f.supply_type,0), 18, nvl(f.supply_qty,0), 0)) onhand_qty,
sum(decode(nvl(f.supply_type,0), 18, nvl(f.supply_qty,0), 0) * nvl(i.standard_cost,0)) onhand_value,
sum(case when l_plan_type=8
and nvl(f.part_condition,1)=1
and f.supply_type=18
then f.supply_qty else 0 end) onhand_usable,
sum(case when l_plan_type=8
and nvl(f.part_condition,1)=2
and f.supply_type=18
then f.supply_qty else 0 end) onhand_defective,
sum(case when l_plan_type=8
and nvl(f.part_condition,1)=1
and f.supply_type in (8,11,12)
then f.supply_qty else 0 end) intransit_usable,
sum(case when l_plan_type=8
and nvl(f.part_condition,1)=2
and f.supply_type in (8,11,12)
then f.supply_qty else 0 end) intransit_defective,
sum(case when l_plan_type=8
and nvl(f.part_condition,1)=1
and f.supply_type=51
then f.supply_qty else 0 end) plnd_xfer_usable,
sum(case when l_plan_type=8
and nvl(f.part_condition,1)=2
and f.supply_type=51
then f.supply_qty else 0 end) plnd_xfer_defective,
sum(case when l_plan_type=8
and nvl(f.part_condition,1)=1
then f.supply_qty else 0 end) supply_qty_usable,
sum(case when l_plan_type=8
and nvl(f.part_condition,1)=2
then f.supply_qty else 0 end) supply_qty_defective,
sum(case when f.supply_type in (1,2,3,8,11,12,14,27,49,53,80)
then nvl(f.supply_qty, 0) else 0 end) scheduled_rept_qty, -- bug 6797566, 9376354
sum((case when f.supply_type in (1,2,3,8,11,12,14,27,49,53,80)
then nvl(f.supply_qty, 0) else 0 end) * nvl(i.standard_cost,0)) scheduled_rept_value,
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_hub_query d,
msc_apcc_item_d i
where f.plan_run_id=p_plan_run_id
and f.aggr_type=0
and d.query_id=l_qid_last_date1
and f.supply_date between d.date1 and d.date2
and f.plan_id=i.plan_id
and f.sr_instance_id=i.sr_instance_id
and f.organization_id=i.organization_id
and f.inventory_item_id=i.inventory_item_id
and (p_plan_id <> -1
or (p_plan_id=-1
and f.sr_instance_id=l_sr_instance_id
and (l_refresh_mode=1
or (l_refresh_mode=2 and (f.plan_id, f.sr_instance_id, f.organization_id, f.inventory_item_id) in
(select number1, number2, number3, number4 from msc_hub_query q where q.query_id=l_item_rn_qid)))))
group by
f.sr_instance_id,
f.organization_id,
f.subinventory_code,
f.owning_inst_id,
f.owning_org_id,
f.inventory_item_id,
d.date2;
msc_phub_util.log('insert into msc_st_item_orders_f:supplies: '||sql%rowcount);
insert /*+ append nologging */ into msc_st_item_orders_f (
st_transaction_id,
error_code,
sr_instance_id,
organization_id,
subinventory_code,
owning_inst_id,
owning_org_id,
inventory_item_id,
order_date,
sup_end_pgd_to_fcst,
sup_end_pgd_to_so,
sup_end_pgd_to_ss,
sup_end_pgd_to_excess,
sup_end_pgd_to_fcst_value,
sup_end_pgd_to_so_value,
sup_end_pgd_to_ss_value,
sup_end_pgd_to_excess_value,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
select /*+ ordered */
l_transfer_id,
to_number(0),
mfp.sr_instance_id,
mfp.organization_id,
'-23453',
mfp.sr_instance_id,
mfp.organization_id,
mfp.inventory_item_id,
trunc(nvl(ms.firm_date, ms.new_schedule_date)) order_date,
sum(decode(mfp2.end_origination_type, 29, mfp.allocated_quantity, 0)) sup_end_pgd_to_fcst,
sum(decode(mfp2.end_origination_type, 30, mfp.allocated_quantity, 0)) sup_end_pgd_to_so,
sum(decode(mfp2.demand_id, -2, mfp.allocated_quantity, 0)) sup_end_pgd_to_ss,
sum(decode(mfp2.demand_id, -1, mfp.allocated_quantity, 0)) sup_end_pgd_to_excess,
sum(decode(mfp2.end_origination_type, 29, mfp.allocated_quantity,0) *
nvl(i.standard_cost,0)) sup_end_pgd_to_fcst_value,
sum(decode(mfp2.end_origination_type, 30, mfp.allocated_quantity, 0) *
nvl(i.standard_cost,0)) sup_end_pgd_to_so_value,
sum(decode(mfp2.demand_id, -2, mfp.allocated_quantity, 0) *
nvl(i.standard_cost,0)) sup_end_pgd_to_ss_value,
sum(decode(mfp2.demand_id, -1, mfp.allocated_quantity, 0) *
nvl(i.standard_cost,0)) sup_end_pgd_to_excess_value,
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_full_pegging mfp,
msc_supplies ms,
msc_apcc_item_d i,
msc_full_pegging mfp2
where 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=p_plan_id
and mfp.plan_id=i.plan_id
and mfp.sr_instance_id=i.sr_instance_id
and mfp.organization_id=i.organization_id
and mfp.inventory_item_id=i.inventory_item_id
and mfp.plan_id=ms.plan_id
and mfp.transaction_id=ms.transaction_id
and (mfp2.demand_id in (-1, -2) or mfp2.end_origination_type in (29, 30))
group by
mfp.sr_instance_id,
mfp.organization_id,
mfp.inventory_item_id,
trunc(nvl(ms.firm_date, ms.new_schedule_date));
msc_phub_util.log('insert into msc_st_item_orders_f:supply end-pegged: '||sql%rowcount);
insert /*+ append nologging */ into msc_st_item_orders_f (
st_transaction_id,
error_code,
sr_instance_id,
organization_id,
subinventory_code,
owning_inst_id,
owning_org_id,
inventory_item_id,
order_date,
dmd_pgd_to_schd_recp,
dmd_pgd_to_plnd_order,
dmd_pgd_to_onhand,
dmd_pgd_to_schd_recp_value,
dmd_pgd_to_plnd_order_value,
dmd_pgd_to_onhand_value,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
select
l_transfer_id,
to_number(0),
mfp.sr_instance_id,
mfp.organization_id,
'-23453',
mfp.sr_instance_id,
mfp.organization_id,
mfp.inventory_item_id,
trunc(mfp.demand_date) order_date,
sum(case when mfp.supply_type in (1,2,3,11,12) then mfp.allocated_quantity else 0 end) dmd_pgd_to_schd_recp,
sum(case when mfp.supply_type in (5,76,77,78,79) then mfp.allocated_quantity else 0 end) dmd_pgd_to_plnd_order,
sum(case when mfp.supply_type in (18) then mfp.allocated_quantity else 0 end) dmd_pgd_to_onhand,
sum((case when mfp.supply_type in (1,2,3,11,12) then mfp.allocated_quantity else 0 end) *
nvl(i.list_price,0)*(1-nvl(i.average_discount,0)/100)) dmd_pgd_to_schd_recp_value,
sum((case when mfp.supply_type in (5,76,77,78,79) then mfp.allocated_quantity else 0 end) *
nvl(i.list_price,0)*(1-nvl(i.average_discount,0)/100)) dmd_pgd_to_plnd_order_value,
sum((case when mfp.supply_type in (18) then mfp.allocated_quantity else 0 end) *
nvl(i.list_price,0)*(1-nvl(i.average_discount,0)/100)) dmd_pgd_to_onhand_value,
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_full_pegging mfp,
msc_apcc_item_d i,
msc_demands md
where mfp.plan_id=md.plan_id
and mfp.demand_id=md.demand_id
and md.origination_type in (5,6,7,8,9,10,11,12,15,22,24,27,29,30)
and mfp.plan_id=p_plan_id
and mfp.plan_id=i.plan_id
and mfp.sr_instance_id=i.sr_instance_id
and mfp.organization_id=i.organization_id
and mfp.inventory_item_id=i.inventory_item_id
and p_plan_id <> -1
and l_plan_type not in (5)
group by
mfp.sr_instance_id,
mfp.organization_id,
mfp.inventory_item_id,
trunc(mfp.demand_date);
msc_phub_util.log('insert into msc_st_item_orders_f:demand pegged: '||sql%rowcount);
insert /*+ append nologging */ into msc_st_item_orders_f (
st_transaction_id,
error_code,
sr_instance_id,
organization_id,
subinventory_code,
owning_inst_id,
owning_org_id,
inventory_item_id,
order_date,
no_activity_item_count,
stock_outs_count,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
select
l_transfer_id,
to_number(0),
me.sr_instance_id,
me.organization_id,
'-23453',
me.owning_inst_id,
me.owning_org_id,
me.inventory_item_id,
me.analysis_date order_date, --- bkt_start_date
sum(decode(exception_type,5,exception_count,0)) no_activity_item_count,
sum(decode(exception_type,2,exception_count,0)) stock_outs_count,
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id,
fnd_global.conc_program_id, fnd_global.conc_login_id,
fnd_global.prog_appl_id, fnd_global.conc_request_id
from
msc_exceptions_f me
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 p_plan_id <> -1
and l_plan_type not in (5)
group by
me.sr_instance_id,
me.organization_id,
me.owning_inst_id,
me.owning_org_id,
me.inventory_item_id,
me.analysis_date;
msc_phub_util.log('insert into msc_st_item_orders_f:exception: '||sql%rowcount);
insert into msc_item_orders_f (
plan_id,
plan_run_id,
sr_instance_id,
organization_id,
subinventory_code,
owning_inst_id,
owning_org_id,
inventory_item_id,
order_date,
io_plan_flag,
aggr_type,
category_set_id,
sr_category_id,
demand_qty,
pegged_to_excess_qty,
supply_qty,
planned_order_qty,
indep_demand_qty,
dep_demand_qty,
sales_order_value,
sales_order_value2,
return_order_value,
make_order_qty,
make_order_leadtime,
make_order_count,
stock_outs_count,
no_activity_item_count,
item_leadtime,
indep_demand_value,
scheduled_rept_qty,
onhand_qty,
forecast_qty,
onhand_usable,
intransit_usable,
plnd_xfer_usable,
onhand_defective,
intransit_defective,
plnd_xfer_defective,
dmd_pgd_to_schd_recp,
dmd_pgd_to_plnd_order,
dmd_pgd_to_onhand,
sup_end_pgd_to_fcst,
sup_end_pgd_to_so,
sup_end_pgd_to_ss,
sup_end_pgd_to_excess,
dmd_pgd_to_schd_recp_value,
dmd_pgd_to_plnd_order_value,
dmd_pgd_to_onhand_value,
sup_end_pgd_to_fcst_value,
sup_end_pgd_to_so_value,
sup_end_pgd_to_ss_value,
sup_end_pgd_to_excess_value,
dmd_pgd_to_schd_recp_value2,
dmd_pgd_to_plnd_order_value2,
dmd_pgd_to_onhand_value2,
sup_end_pgd_to_fcst_value2,
sup_end_pgd_to_so_value2,
sup_end_pgd_to_ss_value2,
sup_end_pgd_to_excess_value2,
supply_qty_usable,
supply_qty_defective,
onhand_value,
onhand_value2,
scheduled_rept_value,
scheduled_rept_value2,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
select
p_plan_id,
p_plan_run_id,
f.sr_instance_id,
f.organization_id,
f.subinventory_code,
f.owning_inst_id,
f.owning_org_id,
f.inventory_item_id,
f.order_date,
decode(l_plan_type, 4, 1, 0) io_plan_flag,
to_number(0) aggr_type,
to_number(-23453) category_set_id,
to_number(-23453) sr_category_id,
f.demand_qty,
f.sup_end_pgd_to_excess pegged_to_excess_qty,
f.supply_qty,
f.planned_order_qty,
f.indep_demand_qty,
f.dep_demand_qty,
f.sales_order_value,
f.sales_order_value *
decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) sales_order_value2,
f.return_order_value,
f.make_order_qty,
f.make_order_leadtime,
f.make_order_count,
f.stock_outs_count,
f.no_activity_item_count,
f.item_leadtime,
f.indep_demand_value,
f.scheduled_rept_qty,
f.onhand_qty,
f.forecast_qty,
f.onhand_usable,
f.intransit_usable,
f.plnd_xfer_usable,
f.onhand_defective,
f.intransit_defective,
f.plnd_xfer_defective,
f.dmd_pgd_to_schd_recp,
f.dmd_pgd_to_plnd_order,
f.dmd_pgd_to_onhand,
f.sup_end_pgd_to_fcst,
f.sup_end_pgd_to_so,
f.sup_end_pgd_to_ss,
f.sup_end_pgd_to_excess,
f.dmd_pgd_to_schd_recp_value,
f.dmd_pgd_to_plnd_order_value,
f.dmd_pgd_to_onhand_value,
f.sup_end_pgd_to_fcst_value,
f.sup_end_pgd_to_so_value,
f.sup_end_pgd_to_ss_value,
f.sup_end_pgd_to_excess_value,
f.dmd_pgd_to_schd_recp_value *
decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) dmd_pgd_to_schd_recp_value2,
f.dmd_pgd_to_plnd_order_value *
decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) dmd_pgd_to_plnd_order_value2,
f.dmd_pgd_to_onhand_value *
decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) dmd_pgd_to_onhand_value2,
f.sup_end_pgd_to_fcst_value *
decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) sup_end_pgd_to_fcst_value2,
f.sup_end_pgd_to_so_value *
decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) sup_end_pgd_to_so_value2,
f.sup_end_pgd_to_ss_value *
decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) sup_end_pgd_to_ss_value2,
f.sup_end_pgd_to_excess_value *
decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) sup_end_pgd_to_excess_value2,
f.supply_qty_usable,
f.supply_qty_defective,
f.onhand_value,
f.onhand_value *
decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) onhand_value2,
f.scheduled_rept_value,
f.scheduled_rept_value *
decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) scheduled_rept_value2,
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id,
fnd_global.conc_program_id, fnd_global.conc_login_id,
fnd_global.prog_appl_id, fnd_global.conc_request_id
from
(select
f.sr_instance_id,
f.organization_id,
f.subinventory_code,
f.owning_inst_id,
f.owning_org_id,
f.inventory_item_id,
f.order_date,
decode(l_plan_type, 6, l_owning_currency_code, nvl(mtp.currency_code, l_owning_currency_code)) currency_code,
sum(f.demand_qty) demand_qty,
sum(f.supply_qty) supply_qty,
sum(f.planned_order_qty) planned_order_qty,
sum(f.indep_demand_qty) indep_demand_qty,
sum(f.dep_demand_qty) dep_demand_qty,
sum(f.sales_order_value) sales_order_value,
sum(f.return_order_value) return_order_value,
sum(f.make_order_qty) make_order_qty,
sum(f.make_order_leadtime) make_order_leadtime,
sum(f.make_order_count) make_order_count,
sum(f.stock_outs_count) stock_outs_count,
sum(f.no_activity_item_count) no_activity_item_count,
sum(f.item_leadtime) item_leadtime,
sum(f.indep_demand_value) indep_demand_value,
sum(f.scheduled_rept_qty) scheduled_rept_qty,
sum(f.onhand_qty) onhand_qty,
sum(f.forecast_qty) forecast_qty,
sum(f.onhand_usable) onhand_usable,
sum(f.intransit_usable) intransit_usable,
sum(f.plnd_xfer_usable) plnd_xfer_usable,
sum(f.onhand_defective) onhand_defective,
sum(f.intransit_defective) intransit_defective,
sum(f.plnd_xfer_defective) plnd_xfer_defective,
sum(f.dmd_pgd_to_schd_recp) dmd_pgd_to_schd_recp,
sum(f.dmd_pgd_to_plnd_order) dmd_pgd_to_plnd_order,
sum(f.dmd_pgd_to_onhand) dmd_pgd_to_onhand,
sum(f.sup_end_pgd_to_fcst) sup_end_pgd_to_fcst,
sum(f.sup_end_pgd_to_so) sup_end_pgd_to_so,
sum(f.sup_end_pgd_to_ss) sup_end_pgd_to_ss,
sum(f.sup_end_pgd_to_excess) sup_end_pgd_to_excess,
sum(f.dmd_pgd_to_schd_recp_value) dmd_pgd_to_schd_recp_value,
sum(f.dmd_pgd_to_plnd_order_value) dmd_pgd_to_plnd_order_value,
sum(f.dmd_pgd_to_onhand_value) dmd_pgd_to_onhand_value,
sum(f.sup_end_pgd_to_fcst_value) sup_end_pgd_to_fcst_value,
sum(f.sup_end_pgd_to_so_value) sup_end_pgd_to_so_value,
sum(f.sup_end_pgd_to_ss_value) sup_end_pgd_to_ss_value,
sum(f.sup_end_pgd_to_excess_value) sup_end_pgd_to_excess_value,
sum(f.supply_qty_usable) supply_qty_usable,
sum(f.supply_qty_defective) supply_qty_defective,
sum(f.onhand_value) onhand_value,
sum(f.scheduled_rept_value) scheduled_rept_value
from
msc_st_item_orders_f f,
msc_trading_partners mtp
where f.st_transaction_id=l_transfer_id
and mtp.partner_type(+)=3
and f.owning_inst_id=mtp.sr_instance_id(+)
and f.owning_org_id=mtp.sr_tp_id(+)
group by
f.sr_instance_id,
f.organization_id,
f.subinventory_code,
f.owning_inst_id,
f.owning_org_id,
f.inventory_item_id,
f.order_date,
decode(l_plan_type, 6, l_owning_currency_code, nvl(mtp.currency_code, l_owning_currency_code))
) f,
msc_currency_conv_mv mcc
where f.currency_code=mcc.from_currency(+)
and fnd_profile.value('MSC_HUB_CUR_CODE_RPT')=mcc.to_currency(+)
and f.order_date=mcc.calendar_date(+);
msc_phub_util.log('insert into msc_item_orders_f:final: '||sql%rowcount);
insert /*+ append nologging */ into msc_st_item_inventory_f (
st_transaction_id,
error_code,
sr_instance_id,
organization_id,
owning_inst_id,
owning_org_id,
inventory_item_id,
vmi_flag,
order_date,
pab_qty,
pab_value,
pab_volume,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
select
l_transfer_id,
to_number(0),
f.sr_instance_id,
f.organization_id,
f.owning_inst_id,
f.owning_org_id,
f.inventory_item_id,
nvl(i.vmi_flag,0) vmi_flag,
d.date2 order_date,
sum(nvl(f.supply_qty,0) - nvl(f.demand_qty,0)) pab_qty,
sum((nvl(f.supply_qty,0) - nvl(f.demand_qty,0)) * nvl(i.standard_cost,0)) pab_value,
sum((nvl(f.supply_qty,0) - nvl(f.demand_qty,0)) * nvl(i.unit_volume,1)) pab_volume,
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_apcc_item_d i,
msc_hub_query d
where f.plan_run_id=p_plan_run_id
and f.aggr_type=0
and f.plan_id=i.plan_id
and f.owning_inst_id=i.sr_instance_id
and f.owning_org_id=i.organization_id
and f.inventory_item_id=i.inventory_item_id
and d.query_id=l_qid_last_date1
and f.order_date <= d.date2
and (f.supply_qty is not null or f.demand_qty is not null)
group by
f.sr_instance_id,
f.organization_id,
f.owning_inst_id,
f.owning_org_id,
f.inventory_item_id,
nvl(i.vmi_flag,0),
d.date2;
msc_phub_util.log('insert into msc_st_item_inventory_f:pab: '||sql%rowcount);
insert /*+ append nologging */ into msc_st_item_inventory_f (
st_transaction_id,
error_code,
sr_instance_id,
organization_id,
owning_inst_id,
owning_org_id,
inventory_item_id,
vmi_flag,
order_date,
pab_qty,
pab_value,
pab_volume,
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)
select
l_transfer_id,
to_number(0),
t.sr_instance_id,
t.organization_id,
t.sr_instance_id,
t.organization_id,
t.inventory_item_id,
nvl(i.vmi_flag,0) vmi_flag,
t.order_date,
t.pab_qty,
t.pab_qty * nvl(i.standard_cost, 0) pab_value,
t.pab_qty * nvl(i.unit_volume, 1) pab_volume,
t.pab_qty 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
(select
mbid.plan_id,
mbid.sr_instance_id,
mbid.organization_id,
mbid.inventory_item_id,
d.date2 order_date,
mbid.pab pab_qty,
rank() over (partition by mbid.plan_id,
mbid.sr_instance_id, mbid.organization_id, mbid.inventory_item_id,
d.date2 order by mbid.detail_date desc, nvl(mbid.period_type,0) desc) rn
from msc_bis_inv_detail mbid, msc_hub_query d
where mbid.plan_id=p_plan_id
and l_plan_type=6
and d.query_id=l_qid_last_date1
and mbid.detail_date <= d.date2
and ((nvl(mbid.detail_level,0)=1 and nvl(mbid.period_type,0)=1)
or (nvl(mbid.detail_level,0)=0 and nvl(mbid.period_type,0)=0))
) t,
msc_apcc_item_d i
where t.rn=1
and t.plan_id=i.plan_id
and t.sr_instance_id=i.sr_instance_id
and t.organization_id=i.organization_id
and t.inventory_item_id=i.inventory_item_id;
msc_phub_util.log('insert into msc_st_item_inventory_f:sno pab: '||sql%rowcount);
insert /*+ append nologging */ into msc_st_item_inventory_f (
st_transaction_id,
error_code,
sr_instance_id,
organization_id,
owning_inst_id,
owning_org_id,
inventory_item_id,
vmi_flag,
order_date,
safety_stock_qty,
safety_stock_value,
safety_stock_volume,
safety_stock_days,
demand_var_ss_qty,
sup_ltvar_ss_qty,
transit_ltvar_ss_qty,
mfg_ltvar_ss_qty,
total_unpooled_safety_stock,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
select
l_transfer_id,
to_number(0),
t.sr_instance_id,
t.organization_id,
t.sr_instance_id,
t.organization_id,
t.inventory_item_id,
nvl(i.vmi_flag,0) vmi_flag,
t.order_date,
t.safety_stock_quantity safety_stock_qty,
t.safety_stock_quantity * nvl(i.standard_cost,0) safety_stock_value,
t.safety_stock_quantity * nvl(i.unit_volume,1) safety_stock_volume,
(case when l_plan_type in (4, 9) then t.achieved_days_of_supply else null end) safety_stock_days,
t.demand_var_ss_percent*t.total_unpooled_safety_stock/100 demand_var_ss_qty,
t.sup_ltvar_ss_percent*t.total_unpooled_safety_stock/100 sup_ltvar_ss_qty,
t.transit_ltvar_ss_percent*t.total_unpooled_safety_stock/100 transit_ltvar_ss_qty,
t.mfg_ltvar_ss_percent*t.total_unpooled_safety_stock/100 mfg_ltvar_ss_qty,
t.total_unpooled_safety_stock,
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id,
fnd_global.conc_program_id, fnd_global.conc_login_id,
fnd_global.prog_appl_id, fnd_global.conc_request_id
from
(select
f.plan_id,
f.sr_instance_id,
f.organization_id,
f.inventory_item_id,
d.date2 order_date,
f.safety_stock_quantity,
f.achieved_days_of_supply,
f.demand_var_ss_percent,
f.sup_ltvar_ss_percent,
f.transit_ltvar_ss_percent,
f.mfg_ltvar_ss_percent,
f.total_unpooled_safety_stock,
rank() over (partition by f.plan_id,
f.sr_instance_id, f.organization_id, f.inventory_item_id,
d.date2 order by f.period_start_date desc) rn
from msc_safety_stocks f, msc_hub_query d
where f.plan_id=p_plan_id
and p_plan_id <> -1
and d.query_id=l_qid_last_date1
and f.period_start_date <= d.date2
) t,
msc_apcc_item_d i,
msc_trading_partners mtp
where t.rn=1
and t.plan_id=i.plan_id
and t.sr_instance_id=i.sr_instance_id
and t.organization_id=i.organization_id
and t.inventory_item_id=i.inventory_item_id
and t.sr_instance_id=mtp.sr_instance_id(+)
and t.organization_id=mtp.sr_tp_id(+)
and mtp.partner_type(+)=3
and l_plan_type not in(5);
msc_phub_util.log('insert into msc_st_item_inventory_f:safety_stock_qty: '||sql%rowcount);
insert /*+ append nologging */ into msc_st_item_inventory_f (
st_transaction_id,
error_code,
sr_instance_id,
organization_id,
owning_inst_id,
owning_org_id,
inventory_item_id,
vmi_flag,
order_date,
min_inventory_level,
max_inventory_level, -- measures populated for DRP
max_inventory_level_dos, -- new measures populated for DRP
target_inventory_level, -- new measures populated for DRP
target_inventory_level_dos, -- new measures populated for DRP
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
select
l_transfer_id,
to_number(0),
t.sr_instance_id,
t.organization_id,
t.sr_instance_id,
t.organization_id,
t.inventory_item_id,
nvl(i.vmi_flag,0) vmi_flag,
t.order_date,
decode(i.safety_stock_code, 2, nvl(t.min_quantity, i.min_minmax_quantity), null) min_inventory_level, --Bug 9858214
nvl(t.max_quantity, i.max_minmax_quantity) max_inventory_level,
nvl(t.max_quantity_dos, 0) max_inventory_level_dos,
nvl(t.target_quantity, 0) target_inventory_level,
nvl(t.target_quantity_dos, 0) target_inventory_level_dos,
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id,
fnd_global.conc_program_id, fnd_global.conc_login_id,
fnd_global.prog_appl_id, fnd_global.conc_request_id
from
(select
f.plan_id,
f.sr_instance_id,
f.organization_id,
f.inventory_item_id,
d.date2 order_date,
f.min_quantity,
f.max_quantity,
f.max_quantity_dos,
f.target_quantity,
f.target_quantity_dos,
rank() over (partition by f.plan_id,
f.sr_instance_id, f.organization_id, f.inventory_item_id,
d.date2 order by f.inventory_date desc) rn
from msc_inventory_levels f, msc_hub_query d
where f.plan_id=p_plan_id
and p_plan_id <> -1
and d.query_id=l_qid_last_date1
and f.inventory_date <= d.date2
) t,
msc_apcc_item_d i,
msc_trading_partners mtp
where t.rn=1
and t.plan_id=i.plan_id
and t.sr_instance_id=i.sr_instance_id
and t.organization_id=i.organization_id
and t.inventory_item_id=i.inventory_item_id
and t.sr_instance_id=mtp.sr_instance_id(+)
and t.organization_id=mtp.sr_tp_id(+)
and mtp.partner_type(+)=3;
msc_phub_util.log('insert into msc_st_item_inventory_f:min_inventory_level: '||sql%rowcount);
insert /*+ append nologging */ into msc_st_item_inventory_f (
st_transaction_id,
error_code,
sr_instance_id,
organization_id,
owning_inst_id,
owning_org_id,
inventory_item_id,
vmi_flag,
order_date,
inventory_cost_post,
inventory_cost_no_post,
inventory_value_post,
inventory_value_no_post,
inventory_value,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
select
l_transfer_id,
to_number(0),
mbid.sr_instance_id,
mbid.organization_id,
mbid.sr_instance_id,
mbid.organization_id,
mbid.inventory_item_id,
nvl(i.vmi_flag,0) vmi_flag,
d.mfg_week_end_date order_date,
sum(inventory_cost_post) inventory_cost_post,
sum(inventory_cost_no_post) inventory_cost_no_post,
avg(inventory_value_post) inventory_value_post,
avg(inventory_value_no_post) inventory_value_no_post,
avg(inventory_value) inventory_value,
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_bis_inv_detail mbid,
msc_phub_dates_mv d,
msc_apcc_item_d i
where trunc(mbid.detail_date)=d.calendar_date
and mbid.plan_id=p_plan_id
and mbid.plan_id=i.plan_id
and mbid.sr_instance_id=i.sr_instance_id
and mbid.organization_id=i.organization_id
and mbid.inventory_item_id=i.inventory_item_id
and nvl(mbid.detail_level,0)=1
and nvl(mbid.period_type,0)=1
and l_plan_type=4
group by
mbid.sr_instance_id,
mbid.organization_id,
mbid.inventory_item_id,
nvl(i.vmi_flag,0),
d.mfg_week_end_date;
msc_phub_util.log('insert into msc_st_item_inventory_f:inventory_value: '||sql%rowcount);
insert /*+ append nologging */ into msc_st_item_inventory_f (
st_transaction_id,
error_code,
sr_instance_id,
organization_id,
owning_inst_id,
owning_org_id,
inventory_item_id,
vmi_flag,
order_date,
wh_required_capacity,
avg_cycle_stock,
avg_cycle_stock_volume,
iqi_value,
iqi_volume,
avg_daily_demand,
avg_daily_demand_volume,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
select
l_transfer_id,
to_number(0),
mwr.sr_instance_id,
mwr.organization_id,
mwr.sr_instance_id,
mwr.organization_id,
mwr.inventory_item_id,
nvl(i.vmi_flag,0) vmi_flag,
d.mfg_week_end_date order_date,
sum(decode(l_enforce_wh_cpty, 1, mwr.required_capacity, null)) wh_required_capacity,
sum(mwr.avg_cycle_stock) avg_cycle_stock,
sum(mwr.avg_cycle_stock * nvl(i.unit_volume,1)) avg_cycle_stock_volume,
sum(decode(l_enforce_wh_cpty, 1, mwr.iqi_value, null)) iqi_value,
sum(decode(l_enforce_wh_cpty, 1, mwr.iqi_value, null) *
nvl(i.unit_volume,1)) iqi_volume,
sum(mwr.avg_daily_demand) avg_daily_demand,
sum(mwr.avg_daily_demand * nvl(i.unit_volume,1)) avg_daily_demand_volume,
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_whse_requirements mwr,
msc_phub_dates_mv d,
msc_apcc_item_d i
where trunc(mwr.stock_date)=d.calendar_date
and mwr.plan_id=p_plan_id
and mwr.plan_id=i.plan_id
and mwr.sr_instance_id=i.sr_instance_id
and mwr.organization_id=i.organization_id
and mwr.inventory_item_id=i.inventory_item_id
and l_plan_type=4
group by
mwr.sr_instance_id,
mwr.organization_id,
mwr.inventory_item_id,
nvl(i.vmi_flag,0),
d.mfg_week_end_date;
msc_phub_util.log('insert into msc_st_item_inventory_f:warehouse_req: '||sql%rowcount);
insert /*+ append nologging */ into msc_st_item_inventory_f (
st_transaction_id,
error_code,
sr_instance_id,
organization_id,
owning_inst_id,
owning_org_id,
inventory_item_id,
vmi_flag,
order_date,
avg_daily_demand,
avg_daily_demand_volume,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
select
l_transfer_id,
to_number(0),
f.sr_instance_id,
f.organization_id,
f.sr_instance_id,
f.organization_id,
f.inventory_item_id,
nvl(i.vmi_flag,0) vmi_flag,
d.mfg_week_end_date order_date,
sum(nvl(f.demand_qty,0)) / l_plan_days avg_daily_demand,
sum(nvl(f.demand_qty,0) * nvl(i.unit_volume,1)) / l_plan_days avg_daily_demand,
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_demands_f f,
msc_phub_dates_mv d,
msc_apcc_item_d i
where trunc(f.order_date)=d.calendar_date
and f.plan_run_id=p_plan_run_id
and f.aggr_type=0
and f.plan_id=i.plan_id
and f.sr_instance_id=i.sr_instance_id
and f.organization_id=i.organization_id
and f.inventory_item_id=i.inventory_item_id
group by
f.sr_instance_id,
f.organization_id,
f.inventory_item_id,
nvl(i.vmi_flag,0),
d.mfg_week_end_date;
msc_phub_util.log('insert into msc_st_item_inventory_f:avg_daily_demand: '||sql%rowcount);
insert into msc_item_inventory_f (
plan_id,
plan_run_id,
sr_instance_id,
organization_id,
owning_inst_id,
owning_org_id,
inventory_item_id,
vmi_flag,
order_date,
io_plan_flag,
aggr_type,
category_set_id,
sr_category_id,
pab_qty,
pab_value,
pab_value2,
pab_volume,
safety_stock_qty,
min_inventory_level,
max_inventory_level,
max_inventory_level_dos, -- new measures populated for DRP
target_inventory_level, -- new measures populated for DRP
target_inventory_level_dos, -- new measures populated for DRP
inv_build_target,
safety_stock_value,
safety_stock_value2,
safety_stock_volume,
safety_stock_days,
inventory_cost_post,
inventory_cost_post2,
inventory_cost_no_post,
inventory_cost_no_post2,
inventory_value_post,
inventory_value_post2,
inventory_value_no_post,
inventory_value_no_post2,
demand_var_ss_qty,
sup_ltvar_ss_qty,
transit_ltvar_ss_qty,
mfg_ltvar_ss_qty,
total_unpooled_safety_stock,
inventory_value,
inventory_value2,
wh_required_capacity,
avg_cycle_stock,
avg_cycle_stock_volume,
iqi_value,
iqi_volume,
avg_daily_demand,
avg_daily_demand_volume,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
select
p_plan_id,
p_plan_run_id,
f.sr_instance_id,
f.organization_id,
f.owning_inst_id,
f.owning_org_id,
f.inventory_item_id,
f.vmi_flag,
f.order_date,
decode(l_plan_type, 4, 1, 0) io_plan_flag,
to_number(0) aggr_type,
to_number(-23453) category_set_id,
to_number(-23453) sr_category_id,
f.pab_qty,
f.pab_value,
f.pab_value * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) pab_value2,
f.pab_volume,
f.safety_stock_qty,
f.min_inventory_level,
f.max_inventory_level,
f.max_inventory_level_dos, -- new measures populated for DRP
f.target_inventory_level, -- new measures populated for DRP
f.target_inventory_level_dos, -- new measures populated for DRP
f.inv_build_target,
f.safety_stock_value,
f.safety_stock_value * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) safety_stock_value2,
f.safety_stock_volume,
f.safety_stock_days,
f.inventory_cost_post,
f.inventory_cost_post * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) inventory_cost_post2,
f.inventory_cost_no_post,
f.inventory_cost_no_post * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) inventory_cost_no_post2,
f.inventory_value_post,
f.inventory_value_post * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) inventory_value_post2,
f.inventory_value_no_post,
f.inventory_value_no_post * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) inventory_value_no_post2,
f.demand_var_ss_qty,
f.sup_ltvar_ss_qty,
f.transit_ltvar_ss_qty,
f.mfg_ltvar_ss_qty,
f.total_unpooled_safety_stock,
f.inventory_value,
f.inventory_value * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) inventory_value2,
f.wh_required_capacity,
f.avg_cycle_stock,
f.avg_cycle_stock_volume,
f.iqi_value,
f.iqi_volume,
f.avg_daily_demand,
f.avg_daily_demand_volume,
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id,
fnd_global.conc_program_id, fnd_global.conc_login_id,
fnd_global.prog_appl_id, fnd_global.conc_request_id
from
(select
f.sr_instance_id,
f.organization_id,
f.owning_inst_id,
f.owning_org_id,
f.inventory_item_id,
f.vmi_flag,
i.safety_stock_code,
f.order_date,
decode(l_plan_type, 6, l_owning_currency_code, nvl(mtp.currency_code, l_owning_currency_code)) currency_code,
sum(nvl(f.pab_qty,0)) pab_qty,
sum(nvl(f.pab_value,0)) pab_value,
sum(nvl(f.pab_volume,0)) pab_volume,
sum(nvl(f.safety_stock_qty,0)) safety_stock_qty,
sum(nvl(f.safety_stock_volume,0)) safety_stock_volume,
decode(i.safety_stock_code, 2,
nvl(sum(f.min_inventory_level), sum(f.safety_stock_qty)), null) min_inventory_level, --Bug 9858214
sum(f.max_inventory_level) max_inventory_level,
sum(f.max_inventory_level_dos) max_inventory_level_dos, -- new measures populated for DRP
sum(f.target_inventory_level) target_inventory_level, -- new measures populated for DRP
sum(f.target_inventory_level_dos) target_inventory_level_dos, -- new measures populated for DRP
sum(f.inv_build_target) inv_build_target,
sum(nvl(f.safety_stock_value,0)) safety_stock_value,
(case when l_plan_type in (4, 9) then sum(f.safety_stock_days) else null end) safety_stock_days,
sum(f.inventory_cost_post) inventory_cost_post,
sum(f.inventory_cost_no_post) inventory_cost_no_post,
avg(f.inventory_value_post) inventory_value_post,
avg(f.inventory_value_no_post) inventory_value_no_post,
sum(f.demand_var_ss_qty) demand_var_ss_qty,
sum(f.sup_ltvar_ss_qty) sup_ltvar_ss_qty,
sum(f.transit_ltvar_ss_qty) transit_ltvar_ss_qty,
sum(f.mfg_ltvar_ss_qty) mfg_ltvar_ss_qty,
sum(f.total_unpooled_safety_stock) total_unpooled_safety_stock,
avg(f.inventory_value) inventory_value,
sum(f.wh_required_capacity) wh_required_capacity,
sum(f.avg_cycle_stock) avg_cycle_stock,
sum(f.avg_cycle_stock_volume) avg_cycle_stock_volume,
sum(f.iqi_value) iqi_value,
sum(f.iqi_volume) iqi_volume,
sum(f.avg_daily_demand) avg_daily_demand,
sum(f.avg_daily_demand_volume) avg_daily_demand_volume
from
msc_st_item_inventory_f f,
msc_trading_partners mtp,
msc_apcc_item_d i
where f.st_transaction_id=l_transfer_id
and mtp.partner_type(+)=3
and f.owning_inst_id=mtp.sr_instance_id(+)
and f.owning_org_id=mtp.sr_tp_id(+)
and i.plan_id=p_plan_id
and f.owning_inst_id=i.sr_instance_id
and f.owning_org_id=i.organization_id
and f.inventory_item_id=i.inventory_item_id
group by
f.sr_instance_id,
f.organization_id,
f.owning_inst_id,
f.owning_org_id,
f.inventory_item_id,
f.vmi_flag,
i.safety_stock_code,
f.order_date,
decode(l_plan_type, 6, l_owning_currency_code, nvl(mtp.currency_code, l_owning_currency_code))
) f,
msc_currency_conv_mv mcc
where f.currency_code=mcc.from_currency(+)
and fnd_profile.value('MSC_HUB_CUR_CODE_RPT')=mcc.to_currency(+)
and f.order_date=mcc.calendar_date(+);
msc_phub_util.log('insert into msc_item_inventory_f:final: '||sql%rowcount);
delete from msc_item_inventory_f
where plan_id=p_plan_id and plan_run_id=p_plan_run_id and aggr_type>0;
msc_phub_util.log('msc_item_pkg.summarize_item_inventory_f, delete='||sql%rowcount);
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,
vmi_flag, order_date,
aggr_type, category_set_id, sr_category_id,
pab_qty,
pab_value,
pab_value2,
pab_volume,
safety_stock_qty,
safety_stock_value,
safety_stock_value2,
safety_stock_volume,
safety_stock_days,
demand_var_ss_qty,
sup_ltvar_ss_qty,
transit_ltvar_ss_qty,
mfg_ltvar_ss_qty,
total_unpooled_safety_stock,
min_inventory_level,
max_inventory_level,
max_inventory_level_dos, -- new measures populated for DRP
target_inventory_level, -- new measures populated for DRP
target_inventory_level_dos, -- new measures populated for DRP
inv_build_target,
inventory_cost_post,
inventory_cost_no_post,
inventory_value_post,
inventory_value_no_post,
inventory_value,
inventory_cost_post2,
inventory_cost_no_post2,
inventory_value_post2,
inventory_value_no_post2,
inventory_value2,
wh_required_capacity,
avg_cycle_stock,
avg_cycle_stock_volume,
iqi_value,
iqi_volume,
avg_daily_demand,
avg_daily_demand_volume,
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.pab_qty),
sum(f.pab_value),
sum(f.pab_value2),
sum(f.pab_volume),
sum(f.safety_stock_qty),
sum(f.safety_stock_value),
sum(f.safety_stock_value2),
sum(f.safety_stock_volume),
sum(f.safety_stock_days),
sum(f.demand_var_ss_qty),
sum(f.sup_ltvar_ss_qty),
sum(f.transit_ltvar_ss_qty),
sum(f.mfg_ltvar_ss_qty),
sum(f.total_unpooled_safety_stock),
sum(f.min_inventory_level),
sum(f.max_inventory_level),
sum(f.max_inventory_level_dos), -- new measures populated for DRP
sum(f.target_inventory_level), -- new measures populated for DRP
sum(f.target_inventory_level_dos), -- new measures populated for DRP
sum(f.inv_build_target),
sum(f.inventory_cost_post),
sum(f.inventory_cost_no_post),
sum(f.inventory_value_post),
sum(f.inventory_value_no_post),
sum(f.inventory_value),
sum(f.inventory_cost_post2),
sum(f.inventory_cost_no_post2),
sum(f.inventory_value_post2),
sum(f.inventory_value_no_post2),
sum(f.inventory_value2),
sum(f.wh_required_capacity),
sum(f.avg_cycle_stock),
sum(f.avg_cycle_stock_volume),
sum(f.iqi_value),
sum(f.iqi_volume),
sum(f.avg_daily_demand),
sum(f.avg_daily_demand_volume),
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.vmi_flag, f.order_date,
nvl(q.sr_category_id, -23453);
delete from msc_item_orders_f
where plan_id=p_plan_id and plan_run_id=p_plan_run_id and aggr_type>0;
msc_phub_util.log('msc_item_pkg.summarize_item_orders_f, delete='||sql%rowcount);
insert into msc_item_orders_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,
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,
sales_order_value2,
return_order_value,
make_order_qty,
make_order_leadtime,
make_order_count,
stock_outs_count,
no_activity_item_count,
item_leadtime,
onhand_qty,
onhand_value,
onhand_value2,
onhand_usable,
intransit_usable,
plnd_xfer_usable,
onhand_defective,
intransit_defective,
plnd_xfer_defective,
supply_qty_usable,
supply_qty_defective,
scheduled_rept_qty,
scheduled_rept_value,
scheduled_rept_value2,
forecast_qty,
sup_end_pgd_to_fcst,
sup_end_pgd_to_so,
sup_end_pgd_to_ss,
sup_end_pgd_to_excess,
dmd_pgd_to_schd_recp,
dmd_pgd_to_plnd_order,
dmd_pgd_to_onhand,
sup_end_pgd_to_fcst_value,
sup_end_pgd_to_so_value,
sup_end_pgd_to_ss_value,
sup_end_pgd_to_excess_value,
dmd_pgd_to_schd_recp_value,
dmd_pgd_to_plnd_order_value,
dmd_pgd_to_onhand_value,
sup_end_pgd_to_fcst_value2,
sup_end_pgd_to_so_value2,
sup_end_pgd_to_ss_value2,
sup_end_pgd_to_excess_value2,
dmd_pgd_to_schd_recp_value2,
dmd_pgd_to_plnd_order_value2,
dmd_pgd_to_onhand_value2,
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, f.owning_org_id,
to_number(-23453) inventory_item_id,
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.sales_order_value2),
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.item_leadtime),
sum(f.onhand_qty),
sum(f.onhand_value),
sum(f.onhand_value2),
sum(f.onhand_usable),
sum(f.intransit_usable),
sum(f.plnd_xfer_usable),
sum(f.onhand_defective),
sum(f.intransit_defective),
sum(f.plnd_xfer_defective),
sum(f.supply_qty_usable),
sum(f.supply_qty_defective),
sum(f.scheduled_rept_qty),
sum(f.scheduled_rept_value),
sum(f.scheduled_rept_value2),
sum(f.forecast_qty),
sum(f.sup_end_pgd_to_fcst),
sum(f.sup_end_pgd_to_so),
sum(f.sup_end_pgd_to_ss),
sum(f.sup_end_pgd_to_excess),
sum(f.dmd_pgd_to_schd_recp),
sum(f.dmd_pgd_to_plnd_order),
sum(f.dmd_pgd_to_onhand),
sum(f.sup_end_pgd_to_fcst_value),
sum(f.sup_end_pgd_to_so_value),
sum(f.sup_end_pgd_to_ss_value),
sum(f.sup_end_pgd_to_excess_value),
sum(f.dmd_pgd_to_schd_recp_value),
sum(f.dmd_pgd_to_plnd_order_value),
sum(f.dmd_pgd_to_onhand_value),
sum(f.sup_end_pgd_to_fcst_value2),
sum(f.sup_end_pgd_to_so_value2),
sum(f.sup_end_pgd_to_ss_value2),
sum(f.sup_end_pgd_to_excess_value2),
sum(f.dmd_pgd_to_schd_recp_value2),
sum(f.dmd_pgd_to_plnd_order_value2),
sum(f.dmd_pgd_to_onhand_value2),
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.subinventory_code,
f.owning_inst_id, f.owning_org_id,
f.order_date,
nvl(q.sr_category_id, -23453);
delete from msc_st_item_inventory_f where st_transaction_id=p_st_transaction_id;
' insert into msc_st_item_inventory_f('||
' st_transaction_id,'||
' error_code,'||
' sr_instance_id,'||
' organization_id,'||
' owning_inst_id,'||
' owning_org_id,'||
' inventory_item_id,'||
' organization_code,'||
' owning_org_code,'||
' item_name,'||
' vmi_flag,'||
' order_date,'||
' pab_qty,'||
' pab_value,'||
' pab_value2,'||
' safety_stock_qty,'||
' min_inventory_level,'||
' max_inventory_level,'||
' inv_build_target,';
' last_updated_by, last_update_date, last_update_login'||
' )'||
' select'||
' :p_st_transaction_id,'||
' 0,'||
' f.sr_instance_id,'||
' f.organization_id,'||
' f.owning_inst_id,'||
' f.owning_org_id,'||
' f.inventory_item_id,'||
' mtp.organization_code,'||
' mtp2.organization_code,'||
' mi.item_name,'||
' f.vmi_flag,'||
' f.order_date,'||
' f.pab_qty,'||
' f.pab_value,'||
' f.pab_value2,'||
' f.safety_stock_qty,'||
' f.min_inventory_level,'||
' f.max_inventory_level,'||
' f.inv_build_target,';
msc_phub_util.log('msc_item_pkg.export_item_inventory_f: inserted='||sql%rowcount);
delete from msc_st_item_orders_f where st_transaction_id=p_st_transaction_id;
' insert into msc_st_item_orders_f('||
' st_transaction_id,'||
' error_code,'||
' sr_instance_id,'||
' organization_id,'||
' owning_inst_id,'||
' owning_org_id,'||
' inventory_item_id,'||
' organization_code,'||
' owning_org_code,'||
' item_name,';
' last_updated_by, last_update_date, last_update_login'||
' )'||
' select'||
' :p_st_transaction_id,'||
' 0,'||
' f.sr_instance_id,'||
' f.organization_id,'||
' f.owning_inst_id,'||
' f.owning_org_id,'||
' f.inventory_item_id,'||
' mtp.organization_code,'||
' mtp2.organization_code,'||
' mi.item_name,';
msc_phub_util.log('msc_item_pkg.export_item_orders_f: inserted='||sql%rowcount);
msc_phub_util.log('msc_item_pkg.import_item_inventory_f: insert into msc_item_inventory_f');
insert into msc_item_inventory_f (
plan_id,
plan_run_id,
sr_instance_id,
organization_id,
owning_inst_id,
owning_org_id,
inventory_item_id,
io_plan_flag,
vmi_flag,
order_date,
pab_qty,
pab_value,
pab_value2,
pab_volume,
safety_stock_qty,
min_inventory_level,
max_inventory_level,
max_inventory_level_dos, -- new measures populated for DRP
target_inventory_level, -- new measures populated for DRP
target_inventory_level_dos, -- new measures populated for DRP
inv_build_target,
safety_stock_value,
safety_stock_value2,
safety_stock_volume,
safety_stock_days,
demand_var_ss_qty,
sup_ltvar_ss_qty,
transit_ltvar_ss_qty,
mfg_ltvar_ss_qty,
total_unpooled_safety_stock,
inventory_cost_post,
inventory_cost_no_post,
inventory_value_post,
inventory_value_no_post,
inventory_value,
inventory_cost_post2,
inventory_cost_no_post2,
inventory_value_post2,
inventory_value_no_post2,
inventory_value2,
wh_required_capacity,
avg_cycle_stock,
avg_cycle_stock_volume,
iqi_value,
iqi_volume,
avg_daily_demand,
avg_daily_demand_volume,
aggr_type, category_set_id, sr_category_id,
created_by, creation_date,
last_updated_by, last_update_date, last_update_login
)
select
p_plan_id,
p_plan_run_id,
nvl(sr_instance_id, -23453),
nvl(organization_id, -23453),
nvl(owning_inst_id, -23453),
nvl(owning_org_id, -23453),
nvl(inventory_item_id, -23453),
decode(p_plan_type, 4, 1, 0) io_plan_flag,
vmi_flag,
order_date,
pab_qty,
pab_value,
pab_value2,
pab_volume,
safety_stock_qty,
min_inventory_level,
max_inventory_level,
max_inventory_level_dos, -- new measures populated for DRP
target_inventory_level, -- new measures populated for DRP
target_inventory_level_dos, -- new measures populated for DRP
inv_build_target,
safety_stock_value,
safety_stock_value2,
safety_stock_volume,
safety_stock_days,
demand_var_ss_qty,
sup_ltvar_ss_qty,
transit_ltvar_ss_qty,
mfg_ltvar_ss_qty,
total_unpooled_safety_stock,
inventory_cost_post,
inventory_cost_no_post,
inventory_value_post,
inventory_value_no_post,
inventory_value,
inventory_cost_post2,
inventory_cost_no_post2,
inventory_value_post2,
inventory_value_no_post2,
inventory_value2,
wh_required_capacity,
avg_cycle_stock,
avg_cycle_stock_volume,
iqi_value,
iqi_volume,
avg_daily_demand,
avg_daily_demand_volume,
0, -23453, -23453,
fnd_global.user_id, sysdate,
fnd_global.user_id, sysdate, fnd_global.login_id
from msc_st_item_inventory_f
where st_transaction_id=p_st_transaction_id and error_code=0;
msc_phub_util.log('msc_item_pkg.import_item_inventory_f: inserted='||sql%rowcount);
msc_phub_util.log('msc_item_pkg.import_item_orders_f: insert into msc_st_item_orders_f');
insert into msc_item_orders_f (
plan_id,
plan_run_id,
sr_instance_id,
organization_id,
subinventory_code,
owning_inst_id,
owning_org_id,
inventory_item_id,
io_plan_flag,
order_date,
demand_qty,
pegged_to_excess_qty,
supply_qty,
planned_order_qty,
indep_demand_qty,
dep_demand_qty,
sales_order_value,
sales_order_value2,
return_order_value,
make_order_qty,
make_order_leadtime,
make_order_count,
stock_outs_count,
no_activity_item_count,
item_leadtime,
indep_demand_value,
scheduled_rept_qty,
onhand_qty,
forecast_qty,
onhand_usable,
intransit_usable,
plnd_xfer_usable,
onhand_defective,
intransit_defective,
plnd_xfer_defective,
sup_end_pgd_to_fcst,
sup_end_pgd_to_so,
sup_end_pgd_to_ss,
sup_end_pgd_to_excess,
dmd_pgd_to_schd_recp,
dmd_pgd_to_plnd_order,
dmd_pgd_to_onhand,
sup_end_pgd_to_fcst_value,
sup_end_pgd_to_so_value,
sup_end_pgd_to_ss_value,
sup_end_pgd_to_excess_value,
dmd_pgd_to_schd_recp_value,
dmd_pgd_to_plnd_order_value,
dmd_pgd_to_onhand_value,
sup_end_pgd_to_fcst_value2,
sup_end_pgd_to_so_value2,
sup_end_pgd_to_ss_value2,
sup_end_pgd_to_excess_value2,
dmd_pgd_to_schd_recp_value2,
dmd_pgd_to_plnd_order_value2,
dmd_pgd_to_onhand_value2,
supply_qty_usable,
supply_qty_defective,
onhand_value,
onhand_value2,
scheduled_rept_value,
scheduled_rept_value2,
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(inventory_item_id, -23453),
decode(p_plan_type, 4, 1, 0) io_plan_flag,
order_date,
demand_qty,
pegged_to_excess_qty,
supply_qty,
planned_order_qty,
indep_demand_qty,
dep_demand_qty,
sales_order_value,
sales_order_value2,
return_order_value,
make_order_qty,
make_order_leadtime,
make_order_count,
stock_outs_count,
no_activity_item_count,
item_leadtime,
indep_demand_value,
scheduled_rept_qty,
onhand_qty,
forecast_qty,
onhand_usable,
intransit_usable,
plnd_xfer_usable,
onhand_defective,
intransit_defective,
plnd_xfer_defective,
sup_end_pgd_to_fcst,
sup_end_pgd_to_so,
sup_end_pgd_to_ss,
sup_end_pgd_to_excess,
dmd_pgd_to_schd_recp,
dmd_pgd_to_plnd_order,
dmd_pgd_to_onhand,
sup_end_pgd_to_fcst_value,
sup_end_pgd_to_so_value,
sup_end_pgd_to_ss_value,
sup_end_pgd_to_excess_value,
dmd_pgd_to_schd_recp_value,
dmd_pgd_to_plnd_order_value,
dmd_pgd_to_onhand_value,
sup_end_pgd_to_fcst_value2,
sup_end_pgd_to_so_value2,
sup_end_pgd_to_ss_value2,
sup_end_pgd_to_excess_value2,
dmd_pgd_to_schd_recp_value2,
dmd_pgd_to_plnd_order_value2,
dmd_pgd_to_onhand_value2,
supply_qty_usable,
supply_qty_defective,
onhand_value,
onhand_value2,
scheduled_rept_value,
scheduled_rept_value2,
0, -23453, -23453,
fnd_global.user_id, sysdate,
fnd_global.user_id, sysdate, fnd_global.login_id
from msc_st_item_orders_f
where st_transaction_id=p_st_transaction_id and error_code=0;
msc_phub_util.log('msc_item_pkg.import_item_orders_f: inserted='||sql%rowcount);