[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert into msc_exceptions_f (
plan_id,
plan_run_id,
organization_id,
sr_instance_id,
inventory_item_id,
department_id,
resource_id,
supplier_id,
supplier_site_id,
customer_id,
customer_site_id,
project_id,
task_id,
analysis_date,
exception_type,
exception_count,
exception_value,
exception_value2,
exception_days,
exception_quantity,
exception_ratio,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id)
select
exception_tbl.plan_id,
p_plan_run_id,
exception_tbl.organization_id,
exception_tbl.sr_instance_id,
nvl(exception_tbl.inventory_item_id, -23453),
exception_tbl.department_id,
exception_tbl.resource_id,
nvl(exception_tbl.supplier_id, -23453),
nvl(exception_tbl.supplier_site_id, -23453),
nvl(exception_tbl.customer_id, -23453),
nvl(exception_tbl.customer_site_id, -23453),
exception_tbl.project_id,
exception_tbl.task_id,
exception_tbl.analysis_date,
exception_tbl.exception_type,
exception_tbl.exception_count,
exception_tbl.exception_value,
exception_tbl.exception_value * decode(exception_tbl.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1, nvl(mcc.CONV_RATE,0)) exception_value2,
exception_tbl.exception_days,
exception_tbl.exception_quantity,
exception_tbl.exception_ratio,
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
t.plan_id,
decode(t.organization_id, -1, -23453, t.organization_id) organization_id,
decode(t.organization_id, -1, -23453, t.sr_instance_id) sr_instance_id, -- wei: sync sr_instance_id with organization_id
decode(t.inventory_item_id, -1, -23453, t.inventory_item_id) inventory_item_id,
decode(t.department_id, -1, -23453, t.department_id) department_id,
decode(t.resource_id, -1, -23453, t.resource_id) resource_id,
decode(t.supplier_id, -1, -23453, t.supplier_id) supplier_id,
decode(t.supplier_site_id, -1, -23453, t.supplier_site_id) supplier_site_id,
decode(t.customer_id, -1, -23453, t.customer_id) customer_id,
decode(t.customer_site_id, -1, -23453, t.customer_site_id) customer_site_id,
-23453 project_id, -- SNO does not write project_id, task_id
-23453 task_id,
mtp.currency_code currency_code,
t.date1 analysis_date,
t.exception_type, -- wei: SNO same
count(*) exception_count, -- wei: SNO same
sum(decode(t.exception_type,
150, abs(t.quantity) *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
151, t.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
152, t.quantity *nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)),
160, abs(t.quantity) *msi.standard_cost,
161, t.quantity *msi.standard_cost,
162, t.quantity *msi.standard_cost,
190, abs(t.quantity) *msi.standard_cost,
191, t.quantity *msi.standard_cost,
to_number(null)) )exception_value, -- wei: SNO
to_number(null) exception_days, -- wei: SNO always null
sum(decode( t.exception_type,
150, abs(t.quantity),
151, t.quantity,
152, t.quantity,
160, abs(t.quantity),
161, t.quantity,
162, t.quantity,
170, abs(t.quantity),
171, t.quantity,
172, abs(t.quantity),
173, t.quantity,
180, abs(t.quantity),
181, t.quantity,
190, abs(t.quantity),
191, t.quantity,
200, abs(t.quantity),
201, t.quantity,
to_number(null))) exception_quantity, -- wei: SNO
avg(t.number2) exception_ratio --wei: SNO
from
(select
med.plan_id,
med.organization_id,
med.sr_instance_id,
med.inventory_item_id,
med.department_id,
med.resource_id,
med.supplier_id,
med.supplier_site_id,
med.customer_id,
med.customer_site_id,
med.exception_type,
med.quantity,
med.date1,
med.number2,
decode(med.organization_id, -1, mp.organization_id, med.organization_id) eff_organization_id,
decode(med.organization_id, -1, mp.sr_instance_id, med.sr_instance_id) eff_sr_instance_id -- wei: sync sr_instance_id with organization_id
from
msc_exception_details med,
msc_plans mp
where mp.plan_id = med.plan_id
and mp.plan_type = 6
and mp.plan_id = p_plan_id) t,
msc_system_items msi,
msc_trading_partners mtp
where msi.plan_id(+) = t.plan_id
and msi.inventory_item_id(+) = t.inventory_item_id
and msi.organization_id(+) = t.eff_organization_id
and msi.sr_instance_id(+) = t.eff_sr_instance_id
and mtp.sr_instance_id(+) = t.sr_instance_id
and mtp.sr_tp_id(+) = t.organization_id
and mtp.partner_type(+) = 3
group by
t.plan_id,
t.organization_id,
t.sr_instance_id,
decode(t.inventory_item_id, -1, -23453, t.inventory_item_id),
decode(t.department_id, -1, -23453, t.department_id),
decode(t.resource_id, -1, -23453, t.resource_id),
decode(t.supplier_id, -1, -23453, t.supplier_id),
decode(t.supplier_site_id, -1, -23453, t.supplier_site_id),
decode(t.customer_id, -1, -23453, t.customer_id),
decode(t.customer_site_id, -1, -23453, t.customer_site_id),
mtp.currency_code,
t.date1,
t.exception_type
) exception_tbl,
msc_currency_conv_mv mcc
where mcc.FROM_CURRENCY(+) = nvl(exception_tbl.currency_code, 'XXX')
and mcc.TO_CURRENCY(+) = fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
and mcc.CALENDAR_DATE(+) = exception_tbl.analysis_date;
insert into msc_resources_f(
plan_id,
plan_run_id,
sr_instance_id,
organization_id,
department_id,
owning_department_id,
resource_id,
inventory_item_id,
analysis_date,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id,
required_hours,
available_hours,
setup_time_hrs,
order_quantity,
resource_hours,
no_of_orders,
resource_cost, --wei
resource_cost2 --wei
)
select
p_plan_id,
p_plan_run_id,
sr_instance_id,
organization_id,
department_id,
nvl(owning_department_id, -23453),
resource_id,
inventory_item_id,
analysis_date,
fnd_global.user_id created_by,
sysdate creation_date,
sysdate last_update_date,
fnd_global.user_id last_updated_by,
fnd_global.login_id last_update_login,
fnd_global.conc_program_id program_id,
fnd_global.conc_login_id program_login_id,
fnd_global.prog_appl_id program_application_id,
fnd_global.conc_request_id request_id,
sum(required_hours),
sum(available_hours),
sum(setup_time_hrs),
sum(order_quantity),
sum(resource_hours),
sum(no_of_orders),
sum(resource_cost) resource_cost, --wei
sum(resource_cost2) resource_cost2 --wei
from
(
select
t1.plan_id,
t1.sr_instance_id,
t1.organization_id,
t1.department_id,
t1.owning_department_id,
t1.resource_id,
-23453 inventory_item_id,
t1.resource_date analysis_date,
t1.required_hours,
t1.available_hours,
t1.setup_hours setup_time_hrs,
to_number(null) order_quantity,
to_number(null) resource_hours,
to_number(null) no_of_orders,
t1.resource_cost,
t1.resource_cost * decode(t1.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) resource_cost2
from
(select
mdrs.plan_id,
mdrs.sr_instance_id,
mdrs.organization_id,
mtp.currency_code,
mdrs.department_id,
mdr.owning_department_id,
mdrs.resource_id,
mdrs.resource_date,
mdrs.required_hours,
mdrs.available_hours,
mdrs.setup_hours,
mdrs.resource_cost
from
msc_bis_res_summary mdrs,
msc_department_resources mdr,
msc_trading_partners mtp
where mdrs.plan_id = p_plan_id
and nvl(mdrs.detail_level, 0) = 1
and nvl(mdrs.period_type, 0) = 1
and mdrs.sr_instance_id = mtp.sr_instance_id(+)
and mdrs.organization_id = mtp.sr_tp_id(+)
and mtp.partner_type(+) = 3
and mdr.plan_id = mdrs.plan_id
and mdr.sr_instance_id = mdrs.sr_instance_id
and mdr.organization_id = mdrs.organization_id
and mdr.department_id = mdrs.department_id
and mdr.resource_id = mdrs.resource_id) t1,
msc_currency_conv_mv mcc
where mcc.to_currency(+) = fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
and mcc.from_currency(+) = nvl(t1.currency_code, 'XXX')
and mcc.calendar_date(+) = t1.resource_date
)
group by
sr_instance_id,
organization_id,
department_id,
nvl(owning_department_id,-23453),
resource_id,
inventory_item_id,
analysis_date;
insert into msc_suppliers_f (
plan_id,
plan_run_id,
sr_instance_id,
organization_id,
supplier_id,
supplier_site_id,
inventory_item_id,
analysis_date,
required_qty,
avail_qty,
po_reschedule_count,
po_count,
po_cancel_count,
buy_order_value,
buy_order_value2,
buy_order_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
t1.plan_id,
p_plan_run_id,
t1.sr_instance_id,
t1.organization_id,
t1.supplier_id,
t1.supplier_site_id,
t1.inventory_item_id,
t1.analysis_date,
sum(t1.required_qty),
sum(t1.avail_qty),
sum(t1.po_reschedule_count),
sum(t1.po_count),
sum(t1.po_cancel_count),
sum(t1.buy_order_value),
sum(t1.buy_order_value * decode(t1.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,nvl(mcc.conv_rate,0))) buy_order_value2,
sum(t1.buy_order_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
mbid.plan_id plan_id,
p_plan_run_id plan_run_id,
mbid.sr_instance_id,
mbid.organization_id,
mbid.supplier_id,
nvl(mbid.supplier_site_id, -23453) supplier_site_id,
mbid.inventory_item_id,
mtp.currency_code,
trunc(mbid.detail_date) analysis_date,
sum(mbid.supplier_usage) required_qty,
sum(mbid.supplier_capacity) avail_qty,
to_number(null) po_reschedule_count,
to_number(null) po_count,
to_number(null) po_cancel_count,
to_number(null) buy_order_value,
to_number(null) buy_order_value2,
to_number(null) buy_order_count
from
msc_bis_inv_detail mbid,
msc_trading_partners mtp
where mbid.plan_id = p_plan_id
and mbid.supplier_id is not null
and mbid.organization_id = mtp.sr_tp_id
and mbid.sr_instance_id = mtp.sr_instance_id
and mtp.partner_type = 3
group by
mbid.plan_id,
mbid.sr_instance_id,
mbid.organization_id,
mtp.currency_code,
mbid.supplier_id,
nvl(mbid.supplier_site_id, -23453),
mbid.inventory_item_id,
trunc(mbid.detail_date)
union all
select
ms.plan_id plan_id,
p_plan_run_id plan_run_id,
ms.sr_instance_id,
ms.organization_id,
ms.supplier_id, -- supplier_id for SNO
nvl(ms.supplier_site_id, -23453) supplier_site_id, -- supplier_site_id for SNO
ms.inventory_item_id inventory_item_id,
mtp.currency_code,
trunc(ms.new_schedule_date) analysis_date, -- new_schedule_date for SNO
to_number(null) required_qty, -- msc_bis_inv_detail.supplier_usage
to_number(null) avail_qty, -- msc_bis_inv_detail.supplier_capacity
to_number(null) po_reschedule_count, -- 0 for SNO
sum(decode(ms.order_type, 1, 1, 0)) po_count,
to_number(null), -- 0 for SNO
--sum(msc_supplier_pkg.supplier_spend_value (ms.new_order_quantity, nvl(msi.list_price,0)*(1-(nvl(msi.average_discount,0)/100)), ms.order_type)) buy_order_value,
--sum(msc_supplier_pkg.is_new_buy_order(ms.order_type, l_plan_type, msi.purchasing_enabled_flag))) buy_order_count
to_number(null) buy_order_value, -- wei: temp, should use lines above
to_number(null) buy_order_value2, -- wei: temp, should use lines above
to_number(null) buy_order_count -- wei: temp, should use lines above
from
msc_supplies ms,
msc_system_items msi,
msc_plans mp,
msc_trading_partners mtp
where mp.plan_id = p_plan_id
and mp.plan_type = 6
and ms.plan_id = p_plan_id
and ms.supplier_id is not null -- wei: make/move orders don't go to supplier and are filtered out
and ms.plan_id = msi.plan_id
and ms.sr_instance_id = msi.sr_instance_id
and ms.organization_id = msi.organization_id
and ms.inventory_item_id = msi.inventory_item_id
and ms.order_type in (planned_order, purchase_order, purchase_req, planned_arrival, new_buy_pos)
and ms.organization_id = mtp.sr_tp_id
and ms.sr_instance_id = mtp.sr_instance_id
and mtp.partner_type = 3
group by
ms.plan_id,
ms.sr_instance_id,
ms.organization_id,
mtp.currency_code,
ms.supplier_id,
nvl(ms.supplier_site_id, -23453),
ms.inventory_item_id,
trunc(ms.new_schedule_date)
) t1,
msc_currency_conv_mv mcc
where mcc.to_currency(+) = FND_PROFILE.value('MSC_HUB_CUR_CODE_RPT')
and mcc.from_currency(+) = t1.currency_code
and mcc.calendar_date(+) = trunc(t1.analysis_date)
group by
t1.plan_id,
t1.sr_instance_id,
t1.organization_id,
t1.supplier_id,
t1.supplier_site_id,
t1.inventory_item_id,
t1.analysis_date;
insert into msc_demands_f (
plan_id,
plan_run_id,
sr_instance_id,
organization_id,
inventory_item_id,
project_id,
task_id,
customer_id,
customer_site_id,
demand_class,
order_date,
order_type,
demand_qty,
qty_by_due_date,
net_demand,
constrained_fcst,
constrained_fcst_value,
constrained_fcst_value2,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
select
t1.plan_id,
p_plan_run_id,
t1.sr_instance_id,
t1.organization_id,
t1.inventory_item_id,
t1.project_id,
t1.task_id,
t1.customer_id,
t1.customer_site_id,
t1.demand_class,
t1.order_date,
t1.order_type,
t1.demand_qty,
t1.qty_by_due_date,
t1.demand_qty net_demand, --wei: SNO new column
t1.qty_by_due_date constrained_fcst, --wei: SNO new column
t1.qty_by_due_date * nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)) constrained_fcst_value, --wei: SNO new column
t1.qty_by_due_date * nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100)) * decode(t1.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) constrained_fcst_value2, --wei: SNO new column
l_user_id created_by,
l_sysdate creation_date,
l_user_id last_updated_by,
l_sysdate last_update_date,
l_user_id last_update_login
from
(select
md.plan_id,
decode(md.organization_id, -1, -23453, md.sr_instance_id) sr_instance_id, -- wei: sync sr_instance_id with organization_id
decode(md.organization_id, -1, -23453, md.organization_id) organization_id,
mtp.currency_code,
md.inventory_item_id,
nvl(md.project_id, -23453) project_id,
nvl(md.task_id, -23453) task_id,
nvl(md.customer_id, -23453) customer_id,
nvl(md.customer_site_id, -23453) customer_site_id,
nvl(md.demand_class, '-23453') demand_class,
trunc(nvl(md.firm_date, md.using_assembly_demand_date)) order_date,
- md.origination_type order_type,
sum(using_requirement_quantity) demand_qty,
sum(quantity_by_due_date) qty_by_due_date
from
msc_demands md,
msc_trading_partners mtp
where md.plan_id = p_plan_id
and md.origination_type = 81 -- wei: SNO
and md.sr_instance_id = mtp.sr_instance_id(+)
and md.organization_id = mtp.sr_tp_id(+)
and mtp.partner_type(+) = 3
group by
md.plan_id,
md.sr_instance_id,
md.organization_id,
mtp.currency_code,
md.inventory_item_id,
nvl(md.project_id, -23453),
nvl(md.task_id, -23453),
nvl(md.customer_id, -23453),
nvl(md.customer_site_id, -23453),
nvl(md.demand_class, '-23453'),
trunc(nvl(md.firm_date, md.using_assembly_demand_date)),
md.origination_type) t1,
msc_system_items msi,
msc_plans mp,
msc_currency_conv_mv mcc
where msi.plan_id = t1.plan_id
and msi.sr_instance_id = decode(sign(t1.sr_instance_id), -1, mp.sr_instance_id, t1.sr_instance_id) --wei
and msi.organization_id = decode(sign(t1.organization_id), -1, mp.organization_id, t1.organization_id) --wei
and msi.inventory_item_id = t1.inventory_item_id
and t1.plan_id = mp.plan_id
and mcc.to_currency(+) = fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
and mcc.from_currency(+) = nvl(t1.currency_code, 'XXX')
and mcc.calendar_date(+) = t1.order_date;
insert into msc_demands_cum_f (
plan_id,
plan_run_id,
sr_instance_id,
organization_id,
inventory_item_id,
customer_id,
customer_site_id,
demand_class,
order_date,
cum_constrained_fcst,
cum_constrained_fcst_value,
cum_constrained_fcst_value2,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id
)
select
cum.plan_id,
cum.plan_run_id,
cum.sr_instance_id,
cum.organization_id,
cum.inventory_item_id,
cum.customer_id,
cum.customer_site_id,
cum.demand_class,
cum.end_date,
last_value(cum.cum_constrained_fcst ignore nulls) over (partition by
cum.plan_id, cum.plan_run_id,
cum.sr_instance_id, cum.organization_id,
cum.inventory_item_id, cum.demand_class,
cum.customer_id, cum.customer_site_id
order by cum.end_date) cum_constrained_fcst,
last_value(cum.cum_constrained_fcst_value ignore nulls) over (partition by
cum.plan_id, cum.plan_run_id,
cum.sr_instance_id, cum.organization_id,
cum.inventory_item_id, cum.demand_class,
cum.customer_id, cum.customer_site_id
order by cum.end_date) cum_constrained_fcst_value,
last_value(cum.cum_constrained_fcst_value2 ignore nulls) over (partition by
cum.plan_id, cum.plan_run_id,
cum.sr_instance_id, cum.organization_id,
cum.inventory_item_id, cum.demand_class,
cum.customer_id, cum.customer_site_id
order by cum.end_date) cum_constrained_fcst_value2,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate,
l_user_id,
l_program_id,
l_cp_login_id,
l_appl_id,
l_request_id
from (
select
k.plan_id,
k.plan_run_id,
k.sr_instance_id,
k.organization_id,
k.inventory_item_id,
k.customer_id,
k.customer_site_id,
k.demand_class,
k.end_date,
sum(f.constrained_fcst) over(partition by
f.plan_id, f.plan_run_id,
f.sr_instance_id, f.organization_id,
f.inventory_item_id, f.demand_class,
f.customer_id, f.customer_site_id
order by f.order_date) cum_constrained_fcst,
sum(f.constrained_fcst_value) over(partition by
f.plan_id, f.plan_run_id,
f.sr_instance_id, f.organization_id,
f.inventory_item_id, f.demand_class,
f.customer_id, f.customer_site_id
order by f.order_date) cum_constrained_fcst_value,
sum(f.constrained_fcst_value2) over(partition by
f.plan_id, f.plan_run_id,
f.sr_instance_id, f.organization_id,
f.inventory_item_id, f.demand_class,
f.customer_id, f.customer_site_id
order by f.order_date) cum_constrained_fcst_value2
from
(select distinct
k1.plan_id,
k1.plan_run_id,
k1.sr_instance_id,
k1.organization_id,
k1.inventory_item_id,
k1.customer_id,
k1.customer_site_id,
k1.demand_class,
k2.end_date
from msc_demands_f k1,
(select trunc(v.month_end_date) end_date
from msc_calendar_dtl v, msc_plans mp
where mp.plan_id=p_plan_id
and v.month_end_date between mp.curr_start_date and mp.curr_cutoff_date
union all
select trunc(v.week_end_date) end_date
from msc_phub_mfg_cal_weeks_mv v, msc_plans mp
where mp.plan_id=p_plan_id
and v.week_end_date between mp.curr_start_date and mp.curr_cutoff_date
union all
select trunc(v.end_date) end_date
from msc_phub_fiscal_periods_mv v, msc_plans mp
where mp.plan_id=p_plan_id
and v.end_date between mp.curr_start_date and mp.curr_cutoff_date
union all
select order_date from msc_demands_f f
where f.plan_id=p_plan_id and f.plan_run_id=p_plan_run_id
) k2
where k1.plan_id=p_plan_id and k1.plan_run_id=p_plan_run_id) k,
msc_demands_f f
where k.plan_id = f.plan_id(+)
and k.plan_run_id = f.plan_run_id(+)
and k.sr_instance_id = f.sr_instance_id(+)
and k.organization_id = f.organization_id(+)
and k.inventory_item_id = f.inventory_item_id(+)
and k.customer_id = f.customer_id(+)
and k.customer_site_id = f.customer_site_id(+)
and k.demand_class = f.demand_class(+)
and k.end_date = f.order_date(+)) cum;
insert into msc_item_inventory_f (
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id,
plan_id,
plan_run_id,
sr_instance_id,
organization_id,
inventory_item_id,
ship_method, --wei
vmi_flag,
order_date,
pab_qty,
pab_value,
pab_value2,
safety_stock_qty,
min_inventory_level,
max_inventory_level,
avg_daily_demand,
supply_chain_cost,
supply_chain_cost2,
revenue,
revenue2,
manufacturing_cost,
manufacturing_cost2,
transportation_cost,
transportation_cost2,
purchasing_cost,
purchasing_cost2,
carrying_cost,
carrying_cost2,
gross_margin,
gross_margin2,
inv_build_target) --wei
select
l_user_id,
l_sysdate,
l_user_id,
l_sysdate,
l_user_login_id,
l_program_id,
l_cp_login_id,
l_appl_id,
l_request_id,
pab_tbl.plan_id,
pab_tbl.plan_run_id,
pab_tbl.sr_instance_id,
pab_tbl.organization_id,
pab_tbl.inventory_item_id,
pab_tbl.ship_method, --wei: SNO dimension
pab_tbl.vmi_flag,
pab_tbl.order_date,
sum(pab_tbl.pab_qty) pab_qty,
sum(pab_tbl.pab_value) pab_value,
sum((pab_tbl.pab_value) *decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
nvl(mcc.CONV_RATE,0))) pab_value2,
sum(pab_tbl.safety_Stock_qty) safety_Stock_qty,
sum(pab_tbl.min_inventory_level) min_inventory_level,
sum(pab_tbl.max_inventory_level) max_inventory_level,
sum(pab_tbl.avg_daily_demand) avg_daily_demand,
sum(pab_tbl.total_cost),
sum(pab_tbl.total_cost * decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
nvl(mcc.CONV_RATE,0))),
sum(pab_tbl.revenue),
sum(pab_tbl.revenue * decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
nvl(mcc.CONV_RATE,0))),
sum(pab_tbl.mfg_cost),
sum(pab_tbl.mfg_cost* decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
nvl(mcc.CONV_RATE,0))),
sum(pab_tbl.tp_cost),
sum(pab_tbl.tp_cost * decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
nvl(mcc.CONV_RATE,0))),
sum(pab_tbl.po_cost),
sum(pab_tbl.po_cost * decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
nvl(mcc.CONV_RATE,0))),
sum(pab_tbl.carrying_cost),
sum(pab_tbl.carrying_cost * decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
nvl(mcc.CONV_RATE,0))),
sum(pab_tbl.revenue- pab_tbl.total_cost),
sum((pab_tbl.revenue- pab_tbl.total_cost) *decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
nvl(mcc.CONV_RATE,0))),
sum(pab_tbl.inv_build_target) --wei: SNO measure
from
(
select
p_plan_id plan_id,
p_plan_run_id plan_run_id,
mbid.sr_instance_id,
mbid.organization_id,
mbid.inventory_item_id,
mbid.ship_method, --wei: SNO dimension
to_number(null) vmi_flag,
nvl(bis_mtp.currency_code,'XXX') currency_code,
mbid.detail_date order_date, --- hub week end date
to_number(null) pab_qty,
to_number(null) pab_value,
to_number(null) safety_stock_qty, -- ss
to_number(null) min_inventory_level, -- min level
to_number(null) max_inventory_level,
to_number(null) avg_daily_demand,
mbid.mds_price revenue,
mbid.PRODUCTION_COST mfg_cost,
mbid.purchasing_cost po_cost,
mbid.TRANSPORTATION_COST tp_cost,
mbid.carrying_cost carrying_cost,
nvl(mbid.PRODUCTION_COST,0) + nvl(mbid.purchasing_cost,0) +
nvl(mbid.carrying_cost,0) + nvl(mbid.TRANSPORTATION_COST,0) total_cost,
last_value(mbid.pab ignore nulls) over (partition by mbid.plan_id, mbid.sr_instance_id,
mbid.organization_id, mbid.inventory_item_id, mbid.ship_method
order by mbid.detail_date) inv_build_target
-- wei: SNO meassure, to add include partition by vmi.number6 order by map.date2
from msc_bis_inv_detail mbid,
msc_trading_partners bis_mtp
where nvl(mbid.detail_level,0)=1
and nvl(mbid.period_type,0)=1
and mbid.plan_id=p_plan_id
and mbid.sr_instance_id = bis_mtp.sr_instance_id
and mbid.organization_id = bis_mtp.sr_tp_id
and bis_mtp.partner_type = 3
) pab_tbl,
msc_currency_conv_mv mcc
where mcc.from_currency(+) =pab_tbl.currency_code --- make sure 'xxx' is not a valid currency code
and mcc.to_currency(+) = fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
and mcc.calendar_date(+) = pab_tbl.order_date
group by
pab_tbl.plan_id,
pab_tbl.plan_run_id,
pab_tbl.sr_instance_id,
pab_tbl.organization_id,
pab_tbl.inventory_item_id,
pab_tbl.ship_method, --wei: SNO dimension
pab_tbl.vmi_flag,
pab_tbl.order_date;