[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select plan_type, sr_instance_id, organization_id, plan_start_date, plan_cutoff_date, temp_transfer_id
into l_plan_type, l_sr_instance_id, l_organization_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;
insert /*+ append nologging */ into msc_st_costs_f (
st_transaction_id,
error_code,
owning_inst_id,
owning_org_id,
sr_instance_id,
organization_id,
source_org_instance_id,
source_organization_id,
inventory_item_id,
customer_id,
customer_site_id,
customer_region_id,
supplier_id,
supplier_site_id,
ship_method,
detail_date,
revenue,
manufacturing_cost,
purchasing_cost,
transportation_cost,
carrying_cost,
supply_chain_cost,
item_travel_distance,
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),
decode(sign(mbid.sr_instance_id), -1,
l_sr_instance_id, mbid.sr_instance_id) owning_inst_id,
decode(sign(mbid.organization_id), -1,
msc_hub_calendar.get_item_org(p_plan_id,
mbid.inventory_item_id,
decode(sign(mbid.sr_instance_id), -1,
l_sr_instance_id, mbid.sr_instance_id)),
mbid.organization_id) owning_org_id,
decode(sign(nvl(mbid.organization_id, -23453)),
-1, -23453, nvl(mbid.sr_instance_id, -23453)) sr_instance_id,
nvl(mbid.organization_id, -23453) organization_id,
nvl(mbid.source_org_instance_id, -23453) source_org_instance_id,
nvl(mbid.source_organization_id, -23453) source_organization_id,
nvl(mbid.inventory_item_id, -23453) inventory_item_id,
decode(sign(mbid.customer_id), 1, mbid.customer_id, -23453) customer_id,
decode(sign(mbid.customer_site_id), 1, mbid.customer_site_id, -23453) customer_site_id,
decode(sign(mbid.zone_id), 1, mbid.zone_id, -23453) customer_region_id,
decode(sign(mbid.supplier_id), 1, mbid.supplier_id, -23453) supplier_id,
decode(sign(mbid.supplier_site_id), 1, mbid.supplier_site_id, -23453) supplier_site_id,
nvl(mbid.ship_method, '-23453') ship_method,
d.mfg_week_end_date detail_date,
-- bnaghi: DRP fact not available item_travel_distance , manufacturing_cost and transportation_cost=0
sum(decode(l_plan_type, 6,
decode(sign(mbid.mds_price), -1, -mbid.mds_price, 0), mbid.mds_price)) revenue,
decode(l_plan_type,5, to_number(null),sum(nvl(mbid.production_cost,0))) manufacturing_cost,
sum(nvl(mbid.purchasing_cost,0)) purchasing_cost,
decode(l_plan_type,5, 0,sum(nvl(mbid.transportation_cost,0))) transportation_cost,
sum(nvl(mbid.carrying_cost,0)) carrying_cost,
sum(nvl(mbid.production_cost,0) + nvl(mbid.purchasing_cost,0) +
nvl(mbid.carrying_cost,0) + decode(l_plan_type,5, 0,nvl(mbid.transportation_cost,0))) supply_chain_cost,
decode(l_plan_type,5, to_number(null),sum(nvl(mbid.item_travel_distance,0))) item_travel_distance,
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
where mbid.plan_id=p_plan_id
and l_plan_type not in (101,102,103,105)
and (mbid.detail_level=1 or l_plan_type=6)
and mbid.period_type=1
and trunc(mbid.detail_date)=d.calendar_date
group by
decode(sign(mbid.sr_instance_id), -1,
l_sr_instance_id, mbid.sr_instance_id),
decode(sign(mbid.organization_id), -1,
msc_hub_calendar.get_item_org(p_plan_id,
mbid.inventory_item_id,
decode(sign(mbid.sr_instance_id), -1,
l_sr_instance_id, mbid.sr_instance_id)),
mbid.organization_id),
decode(sign(nvl(mbid.organization_id, -23453)),
-1, -23453, nvl(mbid.sr_instance_id, -23453)),
nvl(mbid.organization_id, -23453),
nvl(mbid.source_org_instance_id, -23453),
nvl(mbid.source_organization_id, -23453),
nvl(mbid.inventory_item_id, -23453),
decode(sign(mbid.customer_id), 1, mbid.customer_id, -23453),
decode(sign(mbid.customer_site_id), 1, mbid.customer_site_id, -23453),
decode(sign(mbid.zone_id), 1, mbid.zone_id, -23453),
decode(sign(mbid.supplier_id), 1, mbid.supplier_id, -23453),
decode(sign(mbid.supplier_site_id), 1, mbid.supplier_site_id, -23453),
nvl(mbid.ship_method, '-23453'),
d.mfg_week_end_date;
msc_phub_util.log('insert into msc_st_costs_f:regular_costs: '||sql%rowcount);
insert /*+ append nologging */ into msc_st_costs_f (
st_transaction_id,
error_code,
owning_inst_id,
owning_org_id,
sr_instance_id,
organization_id,
source_org_instance_id,
source_organization_id,
inventory_item_id,
customer_id,
customer_site_id,
customer_region_id,
supplier_id,
supplier_site_id,
ship_method,
detail_date,
fixed_cost,
facility_cost,
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),
decode(sign(mbod.sr_instance_id), -1,
l_sr_instance_id, mbod.sr_instance_id) owning_inst_id,
decode(sign(mbod.organization_id), -1,
l_organization_id, mbod.organization_id) owning_org_id,
mbod.sr_instance_id,
mbod.organization_id,
to_number(-23453) source_org_instance_id,
to_number(-23453) source_organization_id,
to_number(-23453) inventory_item_id,
to_number(-23453) customer_id,
to_number(-23453) customer_site_id,
to_number(-23453) customer_region_id,
to_number(-23453) supplier_id,
to_number(-23453) supplier_site_id,
'-23453' ship_method,
d.mfg_week_end_date,
--bnaghi: these facts not available for DRP plans
decode(l_plan_type,5, to_number(null),sum(decode(mbod.facility_cost_type,
3, mbod.facility_cost, null))) fixed_cost,
decode(l_plan_type,5, to_number(null),sum(decode(mbod.facility_cost_type,
1, mbod.facility_cost, 2, mbod.facility_cost,
null))) facility_cost,
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_org_detail mbod, msc_phub_dates_mv d
where mbod.plan_id=p_plan_id
and trunc(mbod.detail_date)=d.calendar_date
group by
decode(sign(mbod.sr_instance_id), -1,
l_sr_instance_id, mbod.sr_instance_id),
decode(sign(mbod.organization_id), -1,
l_organization_id, mbod.organization_id),
mbod.sr_instance_id,
mbod.organization_id,
d.mfg_week_end_date;
msc_phub_util.log('insert into msc_st_costs_f:facility_cost: '||sql%rowcount);
insert /*+ append nologging */ into msc_st_costs_f (
st_transaction_id,
error_code,
owning_inst_id,
owning_org_id,
sr_instance_id,
organization_id,
source_org_instance_id,
source_organization_id,
inventory_item_id,
customer_id,
customer_site_id,
customer_region_id,
supplier_id,
supplier_site_id,
ship_method,
detail_date,
source_count,
risk_item_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),
decode(sign(f.sr_instance_id), -1,
l_sr_instance_id, f.sr_instance_id) owning_inst_id,
decode(sign(f.organization_id), -1,
msc_hub_calendar.get_item_org(p_plan_id,
f.inventory_item_id,
decode(sign(f.sr_instance_id), -1,
l_sr_instance_id, f.sr_instance_id)),
f.organization_id) owning_org_id,
decode(sign(nvl(f.organization_id, -23453)),
-1, -23453, nvl(f.sr_instance_id, -23453)) sr_instance_id,
nvl(f.organization_id, -23453) organization_id,
nvl(f.sr_instance_id2, -23453) source_org_instance_id,
nvl(f.source_organization_id, -23453) source_organization_id,
nvl(f.inventory_item_id, -23453) inventory_item_id,
nvl(f.customer_id, -23453) customer_id,
nvl(f.customer_site_id, -23453) customer_site_id,
nvl(f.zone_id, -23453) customer_region_id,
nvl(f.supplier_id, -23453) supplier_id,
nvl(f.supplier_site_id, -23453) supplier_site_id,
'-23453' ship_method,
d.calendar_date detail_date,
f.source_count,
f.risk_item_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
(select
plan_id, sr_instance_id, organization_id, inventory_item_id,
customer_id, customer_site_id, zone_id,
sr_instance_id2, source_organization_id, supplier_id, supplier_site_id,
effective_date, disable_date,
source_count,
decode(count(1) over(partition by
plan_id, sr_instance_id, organization_id, inventory_item_id,
customer_id, customer_site_id, zone_id,
effective_date, disable_date), 1, 1, 0) risk_item_count
from
(select
plan_id, sr_instance_id, organization_id, inventory_item_id,
customer_id, customer_site_id, zone_id,
sr_instance_id2, source_organization_id, supplier_id, supplier_site_id,
effective_date, disable_date,
to_number(1) source_count
from msc_item_sourcing
where plan_id=p_plan_id
group by
plan_id, sr_instance_id, organization_id, inventory_item_id,
customer_id, customer_site_id, zone_id,
sr_instance_id2, source_organization_id, supplier_id, supplier_site_id,
effective_date, disable_date
)
) f,
msc_phub_dates_mv d
where d.calendar_date between l_plan_start_date and l_plan_cutoff_date
and d.calendar_date in (d.mfg_week_end_date, l_plan_cutoff_date)
and d.calendar_date between nvl(f.effective_date, l_plan_start_date) and nvl(f.disable_date, l_plan_cutoff_date);
msc_phub_util.log('insert into msc_st_costs_f:source_count: '||sql%rowcount);
insert /*+ append nologging */ into msc_st_costs_f (
st_transaction_id,
error_code,
owning_inst_id,
owning_org_id,
sr_instance_id,
organization_id,
source_org_instance_id,
source_organization_id,
inventory_item_id,
customer_id,
customer_site_id,
customer_region_id,
supplier_id,
supplier_site_id,
ship_method,
detail_date,
revenue,
manufacturing_cost,
purchasing_cost,
supply_chain_cost,
ctb_make_order_cnt,
total_make_order_cnt,
avail_component_qty,
total_component_qty,
ready_to_build_qty,
total_build_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),
decode(sign(mrk.instance_id), -1,
l_sr_instance_id, mrk.instance_id) owning_inst_id,
decode(sign(mrk.org_id), -1,
msc_hub_calendar.get_item_org(p_plan_id,
mrk.item_id,
decode(sign(mrk.instance_id), -1,
l_sr_instance_id, mrk.instance_id)),
mrk.org_id) owning_org_id,
decode(sign(nvl(mrk.org_id, -23453)),
-1, -23453, nvl(mrk.instance_id, -23453)) sr_instance_id,
nvl(mrk.org_id, -23453) organization_id,
to_number(-23453) source_org_instance_id,
to_number(-23453) source_organization_id,
nvl(mrk.item_id, -23453) inventory_item_id,
nvl(mrk.customer_id, -23453) customer_id,
nvl(mrk.customer_site_id, -23453) customer_site_id,
to_number(-23453) customer_region_id,
nvl(mrk.supplier_id, -23453) supplier_id,
nvl(mrk.supplier_site_id, -23453) supplier_site_id,
'-23453' ship_method,
d.mfg_week_end_date detail_date,
sum(decode(mrk.kpi_type_id, 5, kpi_value, 0)) revenue,
sum(decode(mrk.kpi_type_id, 7, kpi_value, 0)) manufacturing_cost,
sum(decode(mrk.kpi_type_id, 8, kpi_value, 0)) purchasing_cost,
sum(case when mrk.kpi_type_id in (7,8) then kpi_value else 0 end) supply_chain_cost,
sum(decode(mrk.kpi_type_id, 36, kpi_value_num1, 0)) ctb_make_order_cnt,
sum(decode(mrk.kpi_type_id, 36, kpi_value_num2, 0)) total_make_order_cnt,
sum(decode(mrk.kpi_type_id, 37, kpi_value_num1, 0)) avail_component_qty,
sum(decode(mrk.kpi_type_id, 37, kpi_value_num2, 0)) total_component_qty,
sum(decode(mrk.kpi_type_id, 38, kpi_value_num1, 0)) ready_to_build_qty,
sum(decode(mrk.kpi_type_id, 38, kpi_value_num2, 0)) total_build_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_rp_kpi mrk,
msc_phub_dates_mv d
where mrk.plan_id=p_plan_id
and mrk.kpi_type_id in (5,7,8,36,37,38)
and l_plan_type in (101,102,103,105)
and trunc(mrk.kpi_time)=d.calendar_date
and mrk.item_id is not null
and mrk.kpi_time is not null
and mrk.org_id is not null
group by
decode(sign(mrk.instance_id), -1,
l_sr_instance_id, mrk.instance_id),
decode(sign(mrk.org_id), -1,
msc_hub_calendar.get_item_org(p_plan_id,
mrk.item_id,
decode(sign(mrk.instance_id), -1,
l_sr_instance_id, mrk.instance_id)),
mrk.org_id),
decode(sign(nvl(mrk.org_id, -23453)),
-1, -23453, nvl(mrk.instance_id, -23453)),
nvl(mrk.org_id, -23453),
nvl(mrk.item_id, -23453),
nvl(mrk.customer_id, -23453),
nvl(mrk.customer_site_id, -23453),
nvl(mrk.supplier_id, -23453),
nvl(mrk.supplier_site_id, -23453),
d.mfg_week_end_date;
msc_phub_util.log('insert into msc_st_costs_f:RP: '||sql%rowcount);
insert into msc_costs_f (
plan_id,
plan_run_id,
io_plan_flag,
owning_inst_id,
owning_org_id,
sr_instance_id,
organization_id,
source_org_instance_id,
source_organization_id,
inventory_item_id,
customer_id,
customer_site_id,
customer_region_id,
supplier_id,
supplier_site_id,
ship_method,
detail_date,
aggr_type,
category_set_id,
sr_category_id,
revenue,
revenue2,
manufacturing_cost,
manufacturing_cost2,
purchasing_cost,
purchasing_cost2,
transportation_cost,
transportation_cost2,
carrying_cost,
carrying_cost2,
supply_chain_cost,
supply_chain_cost2,
gross_margin,
gross_margin2,
fixed_cost,
fixed_cost2,
facility_cost,
facility_cost2,
item_travel_distance,
source_count,
risk_item_count,
ctb_make_order_cnt,
total_make_order_cnt,
avail_component_qty,
total_component_qty,
ready_to_build_qty,
total_build_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,
p_plan_run_id,
decode(l_plan_type,4,1,9,1,0) io_plan_flag,
f.owning_inst_id,
f.owning_org_id,
f.sr_instance_id,
f.organization_id,
f.source_org_instance_id,
f.source_organization_id,
f.inventory_item_id,
f.customer_id,
f.customer_site_id,
f.customer_region_id,
f.supplier_id,
f.supplier_site_id,
f.ship_method,
f.detail_date,
to_number(0) aggr_type,
to_number(-23453) category_set_id,
to_number(-23453) sr_category_id,
f.revenue,
f.revenue * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) revenue2,
f.manufacturing_cost,
f.manufacturing_cost * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) manufacturing_cost2,
f.purchasing_cost,
f.purchasing_cost * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) purchasing_cost2,
f.transportation_cost,
f.transportation_cost * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) transportation_cost2,
f.carrying_cost,
f.carrying_cost * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) carrying_cost2,
f.supply_chain_cost,
f.supply_chain_cost * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) supply_chain_cost2,
(nvl(f.revenue,0) - nvl(f.supply_chain_cost,0)) gross_margin,
(nvl(f.revenue,0) - nvl(f.supply_chain_cost,0)) * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) gross_margin2,
f.fixed_cost,
f.fixed_cost * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) fixed_cost2,
f.facility_cost,
f.facility_cost * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) facility_cost2,
f.item_travel_distance,
f.source_count,
f.risk_item_count,
f.ctb_make_order_cnt,
f.total_make_order_cnt,
f.avail_component_qty,
f.total_component_qty,
f.ready_to_build_qty,
f.total_build_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
f.owning_inst_id,
f.owning_org_id,
f.sr_instance_id,
f.organization_id,
f.source_org_instance_id,
f.source_organization_id,
f.inventory_item_id,
f.customer_id,
f.customer_site_id,
f.customer_region_id,
f.supplier_id,
f.supplier_site_id,
f.ship_method,
f.detail_date,
decode(l_plan_type, 6, l_owning_currency_code, nvl(mtp.currency_code, l_owning_currency_code)) currency_code,
sum(f.revenue) revenue,
sum(f.manufacturing_cost) manufacturing_cost,
sum(f.purchasing_cost) purchasing_cost,
sum(f.transportation_cost) transportation_cost,
sum(f.carrying_cost) carrying_cost,
sum(f.supply_chain_cost) supply_chain_cost,
sum(f.item_travel_distance) item_travel_distance,
sum(f.fixed_cost) fixed_cost,
sum(f.facility_cost) facility_cost,
sum(f.source_count) source_count,
sum(f.risk_item_count) risk_item_count,
sum(f.ctb_make_order_cnt) ctb_make_order_cnt,
sum(f.total_make_order_cnt) total_make_order_cnt,
sum(f.avail_component_qty) avail_component_qty,
sum(f.total_component_qty) total_component_qty,
sum(f.ready_to_build_qty) ready_to_build_qty,
sum(f.total_build_qty) total_build_qty
from
msc_st_costs_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.owning_inst_id,
f.owning_org_id,
f.sr_instance_id,
f.organization_id,
f.source_org_instance_id,
f.source_organization_id,
f.inventory_item_id,
f.customer_id,
f.customer_site_id,
f.customer_region_id,
f.supplier_id,
f.supplier_site_id,
f.ship_method,
f.detail_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.detail_date=mcc.calendar_date(+);
msc_phub_util.log('insert into msc_costs_f:final: '||sql%rowcount);
delete from msc_costs_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_cost_pkg.summarize_costs_f, delete='||sql%rowcount);
insert into msc_costs_f (
plan_id, plan_run_id,
owning_inst_id, owning_org_id,
sr_instance_id, organization_id,
source_org_instance_id, source_organization_id,
inventory_item_id,
customer_id, customer_site_id, customer_region_id,
supplier_id, supplier_site_id,
io_plan_flag, ship_method, detail_date,
aggr_type, category_set_id, sr_category_id,
revenue,
revenue2,
manufacturing_cost,
manufacturing_cost2,
purchasing_cost,
purchasing_cost2,
transportation_cost,
transportation_cost2,
carrying_cost,
carrying_cost2,
supply_chain_cost,
supply_chain_cost2,
gross_margin,
gross_margin2,
fixed_cost,
fixed_cost2,
facility_cost,
facility_cost2,
item_travel_distance,
source_count,
risk_item_count,
ctb_make_order_cnt,
total_make_order_cnt,
avail_component_qty,
total_component_qty,
ready_to_build_qty,
total_build_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.owning_inst_id, f.owning_org_id,
f.sr_instance_id, f.organization_id,
f.source_org_instance_id, f.source_organization_id,
to_number(-23453) inventory_item_id,
f.customer_id, f.customer_site_id, f.customer_region_id,
f.supplier_id, f.supplier_site_id,
f.io_plan_flag, f.ship_method, f.detail_date,
to_number(42) aggr_type,
l_category_set_id1 category_set_id,
nvl(q.sr_category_id, -23453),
sum(f.revenue),
sum(f.revenue2),
sum(f.manufacturing_cost),
sum(f.manufacturing_cost2),
sum(f.purchasing_cost),
sum(f.purchasing_cost2),
sum(f.transportation_cost),
sum(f.transportation_cost2),
sum(f.carrying_cost),
sum(f.carrying_cost2),
sum(f.supply_chain_cost),
sum(f.supply_chain_cost2),
sum(f.gross_margin),
sum(f.gross_margin2),
sum(f.fixed_cost),
sum(f.fixed_cost2),
sum(f.facility_cost),
sum(f.facility_cost2),
sum(f.item_travel_distance),
sum(f.source_count),
sum(f.risk_item_count),
sum(f.ctb_make_order_cnt),
sum(f.total_make_order_cnt),
sum(f.avail_component_qty),
sum(f.total_component_qty),
sum(f.ready_to_build_qty),
sum(f.total_build_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_costs_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.owning_inst_id, f.owning_org_id,
f.sr_instance_id, f.organization_id,
f.source_org_instance_id, f.source_organization_id,
f.customer_id, f.customer_site_id, f.customer_region_id,
f.supplier_id, f.supplier_site_id,
f.io_plan_flag, f.ship_method, f.detail_date,
nvl(q.sr_category_id, -23453);
delete from msc_st_costs_f where st_transaction_id=p_st_transaction_id;
' insert into msc_st_costs_f('||
' st_transaction_id,'||
' error_code,'||
' owning_inst_id,'||
' owning_org_id,'||
' sr_instance_id,'||
' organization_id,'||
' inventory_item_id,'||
' owning_org_code,'||
' organization_code,'||
' item_name,'||
' detail_date,'||
' revenue,'||
' revenue2,'||
' manufacturing_cost,'||
' manufacturing_cost2,'||
' purchasing_cost,'||
' purchasing_cost2,'||
' transportation_cost,'||
' transportation_cost2,'||
' carrying_cost,'||
' carrying_cost2,'||
' supply_chain_cost,'||
' supply_chain_cost2,'||
' gross_margin,'||
' gross_margin2,'||
' created_by, creation_date,'||
' last_updated_by, last_update_date, last_update_login'||
' )'||
' select'||
' :p_st_transaction_id,'||
' 0,'||
' f.owning_inst_id,'||
' f.owning_org_id,'||
' f.sr_instance_id,'||
' f.organization_id,'||
' f.inventory_item_id,'||
' mtp3.organization_code,'||
' mtp.organization_code,'||
' mi.item_name,'||
' f.order_date,'||
' f.revenue,'||
' f.revenue2,'||
' f.manufacturing_cost,'||
' f.manufacturing_cost2,'||
' f.purchasing_cost,'||
' f.purchasing_cost2,'||
' f.transportation_cost,'||
' f.transportation_cost2,'||
' f.carrying_cost,'||
' f.carrying_cost2,'||
' f.supply_chain_cost,'||
' f.supply_chain_cost2,'||
' f.gross_margin,'||
' f.gross_margin2,'||
' fnd_global.user_id, sysdate,'||
' fnd_global.user_id, sysdate, fnd_global.login_id'||
' from'||
' '||l_apps_schema||'.msc_item_inventory_f'||l_suffix||' f,'||
' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp,'||
' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp3,'||
' '||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 mtp3.partner_type(+)=3'||
' and mtp3.sr_instance_id(+)=f.owning_inst_id'||
' and mtp3.sr_tp_id(+)=f.owning_org_id'||
' and mi.inventory_item_id(+)=f.inventory_item_id';
' insert into msc_st_costs_f('||
' st_transaction_id,'||
' error_code,'||
' owning_inst_id,'||
' owning_org_id,'||
' sr_instance_id,'||
' organization_id,'||
' source_org_instance_id,'||
' source_organization_id,'||
' inventory_item_id,'||
' customer_id,'||
' customer_site_id,'||
' customer_region_id,'||
' supplier_id,'||
' supplier_site_id,'||
' owning_org_code,'||
' organization_code,'||
' source_org_code,'||
' item_name,'||
' customer_name,'||
' customer_site_code,'||
' customer_zone,'||
' supplier_name,'||
' supplier_site_code,'||
' ship_method,'||
' detail_date,'||
' revenue,'||
' revenue2,'||
' manufacturing_cost,'||
' manufacturing_cost2,'||
' purchasing_cost,'||
' purchasing_cost2,'||
' transportation_cost,'||
' transportation_cost2,'||
' carrying_cost,'||
' carrying_cost2,'||
' supply_chain_cost,'||
' supply_chain_cost2,'||
' gross_margin,'||
' gross_margin2,'||
' fixed_cost,'||
' fixed_cost2,'||
' facility_cost,'||
' facility_cost2,'||
' item_travel_distance,'||
' source_count,'||
' risk_item_count,'||
' ctb_make_order_cnt,'||
' total_make_order_cnt,'||
' avail_component_qty,'||
' total_component_qty,'||
' ready_to_build_qty,'||
' total_build_qty,'||
' created_by, creation_date,'||
' last_updated_by, last_update_date, last_update_login'||
' )'||
' select'||
' :p_st_transaction_id,'||
' 0,'||
' f.owning_inst_id,'||
' f.owning_org_id,'||
' f.sr_instance_id,'||
' f.organization_id,'||
' f.source_org_instance_id,'||
' f.source_organization_id,'||
' f.inventory_item_id,'||
' f.customer_id,'||
' f.customer_site_id,'||
' f.customer_region_id,'||
' f.supplier_id,'||
' f.supplier_site_id,'||
' mtp3.organization_code,'||
' mtp.organization_code,'||
' mtp2.organization_code,'||
' mi.item_name,'||
' decode(f.customer_id, -23453, null, cmv.customer_name),'||
' decode(f.customer_site_id, -23453, null, cmv.customer_site),'||
' decode(f.customer_region_id, -23453, null, cmv.zone),'||
' decode(f.supplier_id, -23453, null, smv.supplier_name),'||
' decode(f.supplier_site_id, -23453, null, smv.supplier_site_code),'||
' f.ship_method,'||
' f.detail_date,'||
' f.revenue,'||
' f.revenue2,'||
' f.manufacturing_cost,'||
' f.manufacturing_cost2,'||
' f.purchasing_cost,'||
' f.purchasing_cost2,'||
' f.transportation_cost,'||
' f.transportation_cost2,'||
' f.carrying_cost,'||
' f.carrying_cost2,'||
' f.supply_chain_cost,'||
' f.supply_chain_cost2,'||
' f.gross_margin,'||
' f.gross_margin2,'||
' f.fixed_cost,'||
' f.fixed_cost2,'||
' f.facility_cost,'||
' f.facility_cost2,'||
' f.item_travel_distance,'||
' f.source_count,'||
' f.risk_item_count,'||
' f.ctb_make_order_cnt,'||
' f.total_make_order_cnt,'||
' f.avail_component_qty,'||
' f.total_component_qty,'||
' f.ready_to_build_qty,'||
' f.total_build_qty,'||
' fnd_global.user_id, sysdate,'||
' fnd_global.user_id, sysdate, fnd_global.login_id'||
' from'||
' '||l_apps_schema||'.msc_costs_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_trading_partners'||l_suffix||' mtp3,'||
' '||l_apps_schema||'.msc_items'||l_suffix||' mi,'||
' '||l_apps_schema||'.msc_phub_customers_mv'||l_suffix||' cmv,'||
' '||l_apps_schema||'.msc_phub_suppliers_mv'||l_suffix||' smv'||
' 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 mtp2.partner_type(+)=3'||
' and mtp2.sr_instance_id(+)=f.source_org_instance_id'||
' and mtp2.sr_tp_id(+)=f.source_organization_id'||
' and mtp3.partner_type(+)=3'||
' and mtp3.sr_instance_id(+)=f.owning_inst_id'||
' and mtp3.sr_tp_id(+)=f.owning_org_id'||
' and mi.inventory_item_id(+)=f.inventory_item_id'||
' and cmv.customer_id(+)=f.customer_id'||
' and cmv.customer_site_id(+)=f.customer_site_id'||
' and cmv.region_id(+)=f.customer_region_id';
msc_phub_util.log('msc_phub_cost_pkg.import_costs_f: insert into msc_costs_f');
insert into msc_costs_f (
plan_id,
plan_run_id,
sr_instance_id,
organization_id,
owning_inst_id,
owning_org_id,
source_org_instance_id,
source_organization_id,
inventory_item_id,
customer_id,
customer_site_id,
customer_region_id,
supplier_id,
supplier_site_id,
io_plan_flag,
ship_method,
detail_date,
revenue,
revenue2,
manufacturing_cost,
manufacturing_cost2,
purchasing_cost,
purchasing_cost2,
transportation_cost,
transportation_cost2,
carrying_cost,
carrying_cost2,
supply_chain_cost,
supply_chain_cost2,
gross_margin,
gross_margin2,
fixed_cost,
fixed_cost2,
facility_cost,
facility_cost2,
item_travel_distance,
source_count,
risk_item_count,
ctb_make_order_cnt,
total_make_order_cnt,
avail_component_qty,
total_component_qty,
ready_to_build_qty,
total_build_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(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(customer_id, -23453),
nvl(customer_site_id, -23453),
nvl(customer_region_id, -23453),
nvl(supplier_id, -23453),
nvl(supplier_site_id, -23453),
decode(p_plan_type, 4, 1, 0) io_plan_flag,
ship_method,
detail_date,
revenue,
revenue2,
manufacturing_cost,
manufacturing_cost2,
purchasing_cost,
purchasing_cost2,
transportation_cost,
transportation_cost2,
carrying_cost,
carrying_cost2,
supply_chain_cost,
supply_chain_cost2,
gross_margin,
gross_margin2,
fixed_cost,
fixed_cost2,
facility_cost,
facility_cost2,
item_travel_distance,
source_count,
risk_item_count,
ctb_make_order_cnt,
total_make_order_cnt,
avail_component_qty,
total_component_qty,
ready_to_build_qty,
total_build_qty,
0, -23453, -23453,
fnd_global.user_id, sysdate,
fnd_global.user_id, sysdate, fnd_global.login_id
from msc_st_costs_f
where st_transaction_id=p_st_transaction_id and error_code=0;
msc_phub_util.log('msc_phub_cost_pkg.import_costs_f: inserted='||sql%rowcount);
select distinct plan_run_id
from msc_item_inventory_f f
where not exists (select 1 from msc_costs_f where plan_run_id=f.plan_run_id)
order by plan_run_id;
' select nvl(min(partition_id), -1) next_id'||
' from'||
' (select to_number(substr(partition_name, length(:table_name)-2)) partition_id'||
' from sys.all_tab_partitions'||
' where table_name=:table_name) t'||
' where partition_id>=:plan_run_id';
' insert into msc_costs_f ('||
' plan_id,'||
' plan_run_id,'||
' sr_instance_id,'||
' organization_id,'||
' inventory_item_id,'||
' owning_org_id,'||
' owning_inst_id,'||
' source_org_instance_id,'||
' source_organization_id,'||
' customer_id,'||
' customer_site_id,'||
' customer_region_id,'||
' supplier_id,'||
' supplier_site_id,'||
' ship_method,'||
' detail_date,'||
' io_plan_flag,'||
' aggr_type,'||
' category_set_id,'||
' sr_category_id,'||
' revenue,'||
' revenue2,'||
' manufacturing_cost,'||
' manufacturing_cost2,'||
' purchasing_cost,'||
' purchasing_cost2,'||
' transportation_cost,'||
' transportation_cost2,'||
' carrying_cost,'||
' carrying_cost2,'||
' supply_chain_cost,'||
' supply_chain_cost2,'||
' gross_margin,'||
' gross_margin2,'||
' created_by, creation_date,'||
' last_updated_by, last_update_date, last_update_login'||
' )'||
' select'||
' plan_id,'||
' plan_run_id,'||
' sr_instance_id,'||
' organization_id,'||
' inventory_item_id,'||
' owning_org_id,'||
' owning_inst_id,'||
' to_number(-23453) source_org_instance_id,'||
' to_number(-23453) source_organization_id,'||
' to_number(-23453) customer_id,'||
' to_number(-23453) customer_site_id,'||
' to_number(-23453) customer_region_id,'||
' to_number(-23453) supplier_id,'||
' to_number(-23453) supplier_site_id,'||
' ship_method,'||
' order_date detail_date,'||
' io_plan_flag,'||
' aggr_type,'||
' category_set_id,'||
' sr_category_id,'||
' revenue,'||
' revenue2,'||
' manufacturing_cost,'||
' manufacturing_cost2,'||
' purchasing_cost,'||
' purchasing_cost2,'||
' transportation_cost,'||
' transportation_cost2,'||
' carrying_cost,'||
' carrying_cost2,'||
' supply_chain_cost,'||
' supply_chain_cost2,'||
' gross_margin,'||
' gross_margin2,'||
' fnd_global.user_id, sysdate,'||
' fnd_global.user_id, sysdate, fnd_global.login_id'||
' from msc_item_inventory_f'||
' where plan_run_id=:p_plan_run_id';
l_sql := 'select count(*) from msc_item_inventory_f where rownum=1 and gross_margin2=0';
' select count(*)'||
' from sys.all_tab_partitions'||
' where table_name=:table_name'||
' and partition_name<>:base_partition_name'||
' and rownum=1';