[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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;
select simulation_set_id
into l_item_simulation_set_id
from msc_plans
where plan_id=p_plan_id;
select plan_id
into l_sim_plan_id
from msc_rp_simulation_sets
where simulation_set_id=l_item_simulation_set_id;
select msc_hub_query_s.nextval into l_qid_eo from dual;
insert into msc_hub_query(
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
date1, -- OBSOLESCENCE_DATE
number3, -- sr_instance_id
number4, -- organization_id
number5, -- inventory_item_id
number7, -- excess_horizon
number8 -- standard_cost
)
select
unique l_qid_eo,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
msi.obsolescence_date,
msi.sr_instance_id,
msi.organization_id,
msi.inventory_item_id,
msi.excess_horizon,
msi.standard_cost
from msc_system_items msi
where msi.plan_id=l_sim_plan_id
and nvl(msi.simulation_set_id, -23453)=l_item_simulation_set_id;
select fnd_profile.value('MSC_APCC_SNO_ITEM_SIMULATION_SET')
into l_item_simulation_set_id
from dual;
select item_simulation_set_id
into l_item_simulation_set_id
from msc_plans
where plan_id=p_plan_id;
select msc_hub_query_s.nextval into l_qid_eo from dual;
insert into msc_hub_query(
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
date1, -- OBSOLESCENCE_DATE
number3, -- sr_instance_id
number4, -- organization_id
number5, -- inventory_item_id
number7, -- excess_horizon
number8 -- standard_cost
)
select
unique l_qid_eo,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
mia.obsolescence_date,
mia.sr_instance_id,
mia.organization_id,
mia.inventory_item_id,
mia.excess_horizon,
nvl(mia.standard_cost, msi.standard_cost)
from msc_item_attributes mia, msc_system_items msi
where mia.plan_id=-1
and mia.simulation_set_id=l_item_simulation_set_id
and msi.plan_id=p_plan_id
and mia.sr_instance_id=msi.sr_instance_id
and mia.organization_id=msi.organization_id
and mia.inventory_item_id=msi.inventory_item_id;
insert /*+ append nologging */ into msc_st_items_f (
st_transaction_id,
error_code,
sr_instance_id,
organization_id,
owning_inst_id,
owning_org_id,
inventory_item_id,
dmd_within_obs_horizon,
dmd_within_excess_horizon,
excess_onhand,
excess_onorder,
obsolete_onhand,
obsolete_onorder,
total_excess,
total_obs,
excess_from_onhand_value,
excess_from_onorder_value,
obsolete_onhand_value,
obsolete_onorder_value,
total_excess_value,
total_obs_value,
excess_from_onhand_value2,
excess_from_onorder_value2,
obsolete_onhand_value2,
obsolete_onorder_value2,
total_excess_value2,
total_obs_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
l_transfer_id,
to_number(0),
sdt.sr_instance_id,
sdt.organization_id,
sdt.sr_instance_id owning_inst_id,
sdt.organization_id owning_org_id,
sdt.inventory_item_id,
sum(nvl(sdt.dmd_within_obs_horizon,0)) dmd_within_obs_horizon,
sum(nvl(sdt.dmd_within_excess_horizon,0)) dmd_within_excess_horizon,
greatest(sum(nvl(sdt.onhand_qty,0)-nvl(sdt.dmd_within_excess_horizon,0)), 0) excess_onhand,
greatest(sum(nvl(sdt.onorder_qty,0)-nvl(sdt.dmd_within_excess_horizon,0)), 0) excess_onorder,
greatest(sum(nvl(sdt.onhand_qty,0)-nvl(sdt.dmd_within_obs_horizon,0)), 0) obsolete_onhand,
greatest(sum(nvl(sdt.onorder_qty,0)-nvl(sdt.dmd_within_obs_horizon,0)), 0) obsolete_onorder,
greatest(sum(nvl(sdt.onorder_qty,0)-nvl(sdt.dmd_within_excess_horizon,0)), 0) +
greatest(sum(nvl(sdt.onhand_qty,0)-nvl(sdt.dmd_within_excess_horizon,0)), 0) total_excess,
greatest(sum(nvl(sdt.onorder_qty,0)-nvl(sdt.dmd_within_obs_horizon,0)), 0) +
greatest(sum(nvl(sdt.onhand_qty,0)-nvl(sdt.dmd_within_obs_horizon,0)), 0) total_obs,
greatest(sum(nvl(sdt.onhand_value,0)-nvl(sdt.dmd_within_excess_hor_value,0)), 0) excess_from_onhand_value,
greatest(sum(nvl(sdt.onorder_value,0)-nvl(sdt.dmd_within_excess_hor_value,0)), 0) excess_from_onorder_value,
greatest(sum(nvl(sdt.onhand_value,0)-nvl(sdt.dmd_within_obs_hor_value,0)), 0) obsolete_onhand_value,
greatest(sum(nvl(sdt.onorder_value,0)-nvl(sdt.dmd_within_obs_hor_value,0)), 0) obsolete_onorder_value,
greatest(sum(nvl(sdt.onhand_value,0)-nvl(sdt.dmd_within_excess_hor_value,0)), 0) +
greatest(sum(nvl(sdt.onorder_value,0)-nvl(sdt.dmd_within_excess_hor_value,0)), 0) total_excess_value,
greatest(sum(nvl(sdt.onhand_value,0)-nvl(sdt.dmd_within_obs_hor_value,0)), 0) +
greatest(sum(nvl(sdt.onorder_value,0)-nvl(sdt.dmd_within_obs_hor_value,0)), 0) total_obs_value,
greatest(sum((nvl(sdt.onhand_value,0)-nvl(sdt.dmd_within_excess_hor_value,0)) *
decode(sdt.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate,0))), 0) excess_from_onhand_value2,
greatest(sum((nvl(sdt.onorder_value,0)-nvl(sdt.dmd_within_excess_hor_value,0)) *
decode(sdt.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate,0))), 0) excess_from_onorder_value2,
greatest(sum((nvl(sdt.onhand_value,0)-nvl(sdt.dmd_within_obs_hor_value,0)) *
decode(sdt.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate,0))), 0) obsolete_onhand_value2,
greatest(sum((nvl(sdt.onorder_value,0)-nvl(sdt.dmd_within_obs_hor_value,0)) *
decode(sdt.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate,0))), 0) obsolete_onorder_value2,
greatest(sum((nvl(sdt.onhand_value,0)-nvl(sdt.dmd_within_excess_hor_value,0)) *
decode(sdt.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate,0))), 0) +
greatest(sum((nvl(sdt.onorder_value,0)-nvl(sdt.dmd_within_excess_hor_value,0)) *
decode(sdt.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate,0))), 0) total_excess_value2,
greatest(sum((nvl(sdt.onhand_value,0)-nvl(sdt.dmd_within_obs_hor_value,0)) *
decode(sdt.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate,0))), 0) +
greatest(sum((nvl(sdt.onorder_value,0)-nvl(sdt.dmd_within_obs_hor_value,0)) *
decode(sdt.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate,0))), 0) total_obs_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
msf.plan_id,
msf.plan_run_id,
msf.sr_instance_id,
msf.organization_id,
msf.inventory_item_id,
msf.supply_date detail_date,
decode(l_plan_type, 6, l_owning_currency_code, nvl(mtp.currency_code, l_owning_currency_code)) currency_code,
to_number(0) dmd_within_obs_horizon,
to_number(0) dmd_within_excess_horizon,
to_number(0) dmd_within_obs_hor_value,
to_number(0) dmd_within_excess_hor_value,
sum(decode(msf.supply_type,18, nvl(msf.supply_qty,0),0)) onhand_qty,
sum(case when msf.supply_type in (1,2,3,8,11,12,14,27,49,53,80)
then nvl(msf.supply_qty,0) else 0 end) onorder_qty,
sum(decode(msf.supply_type,18,nvl(msf.supply_qty,0),0)
*nvl(b.number8, nvl(i.standard_cost,0))) onhand_value,
sum((case when msf.supply_type in (1,2,3,8,11,12,14,27,49,53,80)
then nvl(msf.supply_qty,0) else 0 end)
*nvl(b.number8, nvl(i.standard_cost,0))) onorder_value
from msc_supplies_f msf,msc_trading_partners mtp, msc_hub_query b, msc_system_items i
where msf.plan_id=p_plan_id
and msf.plan_run_id=p_plan_run_id
and msf.aggr_type=0
and b.query_id(+)=l_qid_eo
and b.number5(+)=msf.inventory_item_id
and b.number3(+)=msf.sr_instance_id
and b.number4(+)=msf.organization_id
and msf.sr_instance_id(+)=mtp.sr_instance_id
and msf.organization_id(+)=mtp.sr_tp_id
and mtp.partner_type(+)=3
and msf.plan_id=i.plan_id(+)
and msf.sr_instance_id=i.sr_instance_id(+)
and msf.organization_id=i.organization_id(+)
and msf.inventory_item_id=i.inventory_item_id(+)
and msf.supply_type in (18,1,2,3,8,11,12,14,27,49,53,80)
group by
msf.plan_id,
msf.plan_run_id,
msf.sr_instance_id,
msf.organization_id,
msf.inventory_item_id,
msf.supply_date,
decode(l_plan_type, 6, l_owning_currency_code, nvl(mtp.currency_code, l_owning_currency_code))
union all
select
mdf.plan_id,
mdf.plan_run_id,
mdf.sr_instance_id,
mdf.organization_id,
mdf.inventory_item_id,
mdf.order_date detail_date,
decode(l_plan_type, 6, l_owning_currency_code, nvl(mtp.currency_code, l_owning_currency_code)) currency_code,
nvl((case when mdf.order_date between l_plan_start_date and nvl(b.date1, l_plan_cutoff_date)
then mdf.demand_qty else 0 end),0) dmd_within_obs_horizon,
nvl((case when mdf.order_date between l_plan_start_date and decode(b.number7, null, l_plan_cutoff_date, l_plan_start_date+b.number7)
then mdf.demand_qty else 0 end),0) dmd_within_excess_horizon,
nvl((case when mdf.order_date between l_plan_start_date and nvl(b.date1, l_plan_cutoff_date)
then mdf.demand_qty else 0 end),0)*nvl(b.number8,nvl(i.standard_cost,0)) dmd_within_obs_hor_value,
nvl((case when mdf.order_date between l_plan_start_date and decode(b.number7, null, l_plan_cutoff_date, l_plan_start_date+b.number7)
then mdf.demand_qty else 0 end),0)*nvl(b.number8,nvl(i.standard_cost,0)) dmd_within_excess_hor_value,
to_number(0) onhand_qty,
to_number(0) onorder_qty,
to_number(0) onhand_value,
to_number(0) onorder_value
from msc_demands_f mdf, msc_trading_partners mtp, msc_hub_query b, msc_system_items i
where mdf.plan_id=p_plan_id
and mdf.plan_run_id=p_plan_run_id
and mdf.aggr_type=0
and b.query_id(+)=l_qid_eo
and b.number5(+)=mdf.inventory_item_id
and b.number3(+)=mdf.sr_instance_id
and b.number4(+)=mdf.organization_id
and mdf.sr_instance_id=mtp.sr_instance_id(+)
and mdf.organization_id=mtp.sr_tp_id(+)
and mtp.partner_type(+)=3
and mdf.plan_id=i.plan_id(+)
and mdf.sr_instance_id=i.sr_instance_id(+)
and mdf.organization_id=i.organization_id(+)
and mdf.inventory_item_id=i.inventory_item_id(+)
) sdt,
msc_currency_conv_mv mcc
where sdt.plan_id=p_plan_id
and mcc.to_currency(+)=fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
and mcc.from_currency(+)=nvl(sdt.currency_code, l_owning_currency_code)
and mcc.calendar_date(+)=sdt.detail_date
group by
sdt.sr_instance_id,
sdt.organization_id,
sdt.inventory_item_id;
msc_phub_util.log('insert into msc_st_items_f:eo: '||sql%rowcount);
insert /*+ append nologging */ into msc_st_items_f (
st_transaction_id,
error_code,
sr_instance_id,
organization_id,
owning_inst_id,
owning_org_id,
inventory_item_id,
wh_available_capacity,
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),
po.sr_instance_id,
po.organization_id,
to_number(-23453) owning_inst_id,
to_number(-23453) owning_org_id,
to_number(-23453) inventory_item_id,
mtp.maximum_volume*nvl(uc.conversion_rate,1) wh_available_capacity,
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_trading_partners mtp,
msc_plan_organizations po,
msc_uom_conversions uc
where po.plan_id=p_plan_id
and po.sr_instance_id=mtp.sr_instance_id
and po.organization_id=mtp.sr_tp_id
and mtp.partner_type=3
and mtp.maximum_volume is not null
and mtp.volume_uom=uc.uom_code
and mtp.sr_instance_id=uc.sr_instance_id
and uc.inventory_item_id=0;
msc_phub_util.log('insert into msc_st_items_f:wh_available_capacity: '||sql%rowcount);
insert /*+ append nologging */ into msc_st_items_f (
st_transaction_id,
error_code,
sr_instance_id,
organization_id,
owning_inst_id,
owning_org_id,
inventory_item_id,
wh_available_capacity,
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),
mwc.sr_instance_id,
mwc.organization_id,
mwc.sr_instance_id owning_inst_id,
to_number(-23453) owning_org_id,
-mwc.sr_category_id inventory_item_id,
mwc.available_capacity*nvl(uc.conversion_rate,1) wh_available_capacity,
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_warehouse_capacities mwc,
msc_plan_organizations po,
msc_uom_conversions uc
where po.plan_id=p_plan_id
and po.sr_instance_id=mwc.sr_instance_id
and po.organization_id=mwc.organization_id
and mwc.capacity_uom=uc.uom_code(+)
and mwc.sr_instance_id=uc.sr_instance_id(+)
and uc.inventory_item_id(+)=0;
msc_phub_util.log('insert into msc_st_items_f:wh_available_capacity_cat: '||sql%rowcount);
insert into msc_items_f (
plan_id,
plan_run_id,
sr_instance_id,
organization_id,
owning_inst_id,
owning_org_id,
inventory_item_id,
aggr_type,
category_set_id,
sr_category_id,
dmd_within_obs_horizon,
dmd_within_excess_horizon,
excess_onorder,
excess_onhand,
obsolete_onorder,
obsolete_onhand,
total_excess,
total_obs,
excess_from_onhand_value,
excess_from_onorder_value,
obsolete_onhand_value,
obsolete_onorder_value,
total_excess_value,
total_obs_value,
excess_from_onhand_value2,
excess_from_onorder_value2,
obsolete_onhand_value2,
obsolete_onorder_value2,
total_excess_value2,
total_obs_value2,
wh_available_capacity,
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,
sr_instance_id,
organization_id,
owning_inst_id,
owning_org_id,
inventory_item_id,
to_number(0) aggr_type,
to_number(-23453) category_set_id,
to_number(-23453) sr_category_id,
sum(dmd_within_obs_horizon),
sum(dmd_within_excess_horizon),
sum(excess_onorder),
sum(excess_onhand),
sum(obsolete_onorder),
sum(obsolete_onhand),
sum(total_excess),
sum(total_obs),
sum(excess_from_onhand_value),
sum(excess_from_onorder_value),
sum(obsolete_onhand_value),
sum(obsolete_onorder_value),
sum(total_excess_value),
sum(total_obs_value),
sum(excess_from_onhand_value2),
sum(excess_from_onorder_value2),
sum(obsolete_onhand_value2),
sum(obsolete_onorder_value2),
sum(total_excess_value2),
sum(total_obs_value2),
sum(wh_available_capacity),
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_st_items_f f
where f.st_transaction_id=l_transfer_id
group by
sr_instance_id,
organization_id,
owning_inst_id,
owning_org_id,
inventory_item_id;
msc_phub_util.log('insert into msc_items_f:final: '||sql%rowcount);
delete from msc_items_f
where plan_id=p_plan_id and plan_run_id=p_plan_run_id and aggr_type>0;
msc_phub_util.log('msc_phub_excess_pkg.summarize_items_f, delete='||sql%rowcount);
insert into msc_items_f (
plan_id, plan_run_id,
sr_instance_id, organization_id,
owning_inst_id, owning_org_id, inventory_item_id,
aggr_type, category_set_id, sr_category_id,
dmd_within_obs_horizon,
dmd_within_excess_horizon,
excess_onorder,
excess_onhand,
obsolete_onorder,
obsolete_onhand,
total_excess,
excess_from_onhand_value,
excess_from_onorder_value,
total_excess_value,
excess_from_onhand_value2,
excess_from_onorder_value2,
total_excess_value2,
total_obs,
obsolete_onhand_value,
obsolete_onorder_value,
total_obs_value,
obsolete_onhand_value2,
obsolete_onorder_value2,
total_obs_value2,
wh_available_capacity,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
-- category (42, 43, 44)
select
f.plan_id, f.plan_run_id,
f.sr_instance_id, f.organization_id,
f.owning_inst_id, f.owning_org_id,
to_number(-23453) inventory_item_id,
to_number(42) aggr_type,
l_category_set_id1 category_set_id,
nvl(q.sr_category_id, -23453),
sum(dmd_within_obs_horizon),
sum(dmd_within_excess_horizon),
sum(excess_onorder),
sum(excess_onhand),
sum(obsolete_onorder),
sum(obsolete_onhand),
sum(total_excess),
sum(excess_from_onhand_value),
sum(excess_from_onorder_value),
sum(total_excess_value),
sum(excess_from_onhand_value2),
sum(excess_from_onorder_value2),
sum(total_excess_value2),
sum(total_obs),
sum(obsolete_onhand_value),
sum(obsolete_onorder_value),
sum(total_obs_value),
sum(obsolete_onhand_value2),
sum(obsolete_onorder_value2),
sum(total_obs_value2),
sum(wh_available_capacity),
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_items_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 q.inventory_item_id(+)=decode(sign(f.inventory_item_id), 1, f.inventory_item_id, -23453)
and q.sr_category_id(+)=decode(sign(f.inventory_item_id), 1, q.sr_category_id(+), -f.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.owning_inst_id, f.owning_org_id,
nvl(q.sr_category_id, -23453);
delete from msc_st_items_f where st_transaction_id=p_st_transaction_id;
' insert into msc_st_items_f('||
' st_transaction_id,'||
' error_code,'||
' sr_instance_id,'||
' organization_id,'||
' inventory_item_id,'||
' organization_code,'||
' item_name,'||
' 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,'||
' fnd_global.user_id, sysdate,'||
' fnd_global.user_id, sysdate, fnd_global.login_id'||
' from'||
' '||l_apps_schema||'.msc_item_orders_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'||
' group by'||
' f.sr_instance_id,'||
' f.organization_id,'||
' f.inventory_item_id,'||
' mtp.organization_code,'||
' mi.item_name';
' insert into msc_st_items_f('||
' st_transaction_id,'||
' error_code,'||
' sr_instance_id,'||
' organization_id,'||
' inventory_item_id,'||
' owning_inst_id,'||
' owning_org_id,'||
' organization_code,'||
' owning_org_code,'||
' item_name,'||
' category_instance_code,'||
' category_name,'||
' dmd_within_obs_horizon,'||
' dmd_within_excess_horizon,'||
' excess_onorder,'||
' excess_onhand,'||
' obsolete_onorder,'||
' obsolete_onhand,'||
' total_excess,'||
' excess_from_onhand_value,'||
' excess_from_onorder_value,'||
' total_excess_value,'||
' excess_from_onhand_value2,'||
' excess_from_onorder_value2,'||
' total_excess_value2,'||
' total_obs,'||
' obsolete_onhand_value,'||
' obsolete_onorder_value,'||
' total_obs_value,'||
' obsolete_onhand_value2,'||
' obsolete_onorder_value2,'||
' total_obs_value2,'||
' wh_available_capacity,'||
' 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.owning_inst_id,'||
' f.owning_org_id,'||
' f.inventory_item_id,'||
' mtp.organization_code,'||
' mtp2.organization_code,'||
' mi.item_name,'||
' mai.instance_code category_instance_code,'||
' (select category_name from msc_phub_categories_mv where sr_instance_id=f.owning_inst_id and sr_category_id=-f.inventory_item_id and rownum=1) category_name,'||
' f.dmd_within_obs_horizon,'||
' f.dmd_within_excess_horizon,'||
' f.excess_onorder,'||
' f.excess_onhand,'||
' f.obsolete_onorder,'||
' f.obsolete_onhand,'||
' f.total_excess,'||
' f.excess_from_onhand_value,'||
' f.excess_from_onorder_value,'||
' f.total_excess_value,'||
' f.excess_from_onhand_value2,'||
' f.excess_from_onorder_value2,'||
' f.total_excess_value2,'||
' f.total_obs,'||
' f.obsolete_onhand_value,'||
' f.obsolete_onorder_value,'||
' f.total_obs_value,'||
' f.obsolete_onhand_value2,'||
' f.obsolete_onorder_value2,'||
' f.total_obs_value2,'||
' f.wh_available_capacity,'||
' fnd_global.user_id, sysdate,'||
' fnd_global.user_id, sysdate, fnd_global.login_id'||
' from'||
' '||l_apps_schema||'.msc_items_f'||l_suffix||' f,'||
' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp,'||
' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp2,'||
' '||l_apps_schema||'.msc_items'||l_suffix||' mi,'||
' '||l_apps_schema||'.msc_apps_instances'||l_suffix||' mai'||
' 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'||
' and mtp2.partner_type(+)=3'||
' and mtp2.sr_instance_id(+)=f.owning_inst_id'||
' and mtp2.sr_tp_id(+)=f.owning_org_id'||
' and mai.instance_id(+)=f.owning_inst_id';
msc_phub_util.log('msc_phub_excess_pkg.export_items_f: inserted='||sql%rowcount);
msc_phub_util.log('msc_phub_excess_pkg.import_items_f: insert into msc_items_f');
insert into msc_items_f (
plan_id,
plan_run_id,
sr_instance_id,
organization_id,
owning_inst_id,
owning_org_id,
inventory_item_id,
dmd_within_obs_horizon,
dmd_within_excess_horizon,
excess_onorder,
excess_onhand,
obsolete_onorder,
obsolete_onhand,
total_excess,
excess_from_onhand_value,
excess_from_onorder_value,
total_excess_value,
excess_from_onhand_value2,
excess_from_onorder_value2,
total_excess_value2,
total_obs,
obsolete_onhand_value,
obsolete_onorder_value,
total_obs_value,
obsolete_onhand_value2,
obsolete_onorder_value2,
total_obs_value2,
wh_available_capacity,
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),
dmd_within_obs_horizon,
dmd_within_excess_horizon,
excess_onorder,
excess_onhand,
obsolete_onorder,
obsolete_onhand,
total_excess,
excess_from_onhand_value,
excess_from_onorder_value,
total_excess_value,
excess_from_onhand_value2,
excess_from_onorder_value2,
total_excess_value2,
total_obs,
obsolete_onhand_value,
obsolete_onorder_value,
total_obs_value,
obsolete_onhand_value2,
obsolete_onorder_value2,
total_obs_value2,
wh_available_capacity,
0, -23453, -23453,
fnd_global.user_id, sysdate,
fnd_global.user_id, sysdate, fnd_global.login_id
from msc_st_items_f
where st_transaction_id=p_st_transaction_id and error_code=0;
msc_phub_util.log('msc_phub_excess_pkg.import_items_f: inserted='||sql%rowcount);