select
mov.instance,
mov.plan,
mov.organization,
mov.subinventory,
mov.demand_class,
mov.project_number,
mov.task_number,
&lp_custom_attributes
mov.planner,
mov.buyer_name,
mov.make_buy,
mov.item,
mov.item_description,
mov.category_set,
mov.category,
regexp_substr(mov.category,'[^.]+',1,1) category1,
regexp_substr(mov.category,'[^.]+',1,2) category2,
mov.safety_stock,
--
mov.suggested_due_date,
mov.supply_demand,
mov.order_type,
mov.order_number,
--
mov.new_date,
mov.new_quantity,
--
mov.suggested_order_date,
mov.suggested_start_date,
mov.days_from_today,
mov.need_by_date,
mov.promise_date,
mov.old_due_date,
mov.old_need_by_date,
--
mov.qty,
mov.released_qty,
--
mov.implement_date,
mov.implement_quantity_rate,
mov.implement_as,
mov.implement_location,
--
mov.firm,
mov.firm_date,
mov.firm_quantity,
--
mov.source_organization,
mov.source_vendor_name,
mov.source_vendor_site_code,
mov.supplier,
mov.supplier_site,
--
mov.action_required,
mov.for_release,
mov.released_status,
mov.schedule_compression_days,
mov.reschedule_days,
--
mov.order_priority,
mov.using_assembly,
mov.alternate_bom_designator,
mov.alternate_routing_designator,
mov.line_code,
mov.schedule_group,
mov.build_sequence,
mov.planning_group,
--
mov.intransit_lt,
mov.planning_time_fence_date,
mov.release_time_fence_days,
mov.fixed_lt,
mov.variable_lt,
mov.pre_processing_lt,
mov.post_processing_lt,
mov.full_lt,
mov.abc_class,
-- item dff attributes
&lp_item_dff_cols
--
-- pegged to order
--
nvl(mfpd.allocated_quantity,mfps.allocated_quantity) peg_allocated_quantity,
case
when mfpd.pegging_id is not null then xxen_util.meaning('DEMAND','MSC_QUESTION_TYPE',3)
when mfps.pegging_id is not null then xxen_util.meaning('SUPPLY','MSC_QUESTION_TYPE',3)
else null
end peg_supply_demand,
--
case
when mfpd.pegging_id is not null
then
case when mfpd.demand_id < 0
then msc_get_name.lookup_meaning&a2m_dblink ('MRP_FLP_SUPPLY_DEMAND_TYPE',mfpd.demand_id)
else msc_get_name.lookup_meaning&a2m_dblink ('MSC_DEMAND_ORIGINATION',decode(md.origination_type, 70, 50, 92, 50, md.origination_type))
end
when mfps.pegging_id is not null
then
case
when mov.plan_type = 8
then case when ms.order_type = 1 then msc_get_name.lookup_meaning&a2m_dblink ('SRP_CHANGED_ORDER_TYPE', ms.order_type)
when ms.order_type = 2 and ms.source_organization_id is null then msc_get_name.lookup_meaning&a2m_dblink ('SRP_CHANGED_ORDER_TYPE', ms.order_type)
when ms.order_type = 2 and ms.source_organization_id is not null then msc_get_name.lookup_meaning&a2m_dblink ('MRP_ORDER_TYPE',53)
when ms.order_type = 53 then msc_get_name.lookup_meaning&a2m_dblink ('SRP_CHANGED_ORDER_TYPE', ms.order_type)
else msc_get_name.lookup_meaning&a2m_dblink ('MRP_ORDER_TYPE',case ms.order_type when 92 then 70 else ms.order_type end)
end
else msc_get_name.lookup_meaning&a2m_dblink ('MRP_ORDER_TYPE',case ms.order_type when 92 then 70 else ms.order_type end)
end
else
null
end peg_order_type,
case
when mfpd.pegging_id is not null
then
nvl(md.order_number
,decode(md.origination_type
, 1 , to_char(md.disposition_id)
, 3 , msc_get_name.job_name&a2m_dblink (md.disposition_id, md.plan_id, md.sr_instance_id)
, 22, to_char(md.disposition_id)
, 50, msc_get_name.maintenance_plan&a2m_dblink (md.schedule_designator_id)
, 70, msc_get_name.maintenance_plan&a2m_dblink (md.schedule_designator_id)
, 92, msc_get_name.maintenance_plan&a2m_dblink (md.schedule_designator_id )
, 29, decode(md.plan_id
,-11, msc_get_name.designator&a2m_dblink (md.schedule_designator_id)
, decode(mov.in_source_plan
,1,msc_get_name.designator&a2m_dblink (md.schedule_designator_id, md.forecast_set_id )
, msc_get_name.scenario_designator&a2m_dblink (md.forecast_set_id, md.plan_id, md.organization_id, md.sr_instance_id)
|| decode(msc_get_name.designator&a2m_dblink (md.schedule_designator_id,md.forecast_set_id )
, null, null
, '/'||msc_get_name.designator&a2m_dblink (md.schedule_designator_id,md.forecast_set_id )
)
)
)
, 78, to_char(md.disposition_id)
, msc_get_name.designator&a2m_dblink (md.schedule_designator_id)
)
)
when mfps.pegging_id is not null
then
msc_get_name.supply_order_number&a2m_dblink
(ms.order_type
,ms.order_number
,ms.plan_id
,ms.sr_instance_id
,ms.transaction_id
,ms.disposition_id
)
else
null
end peg_order_number,
nvl(-nvl(md.daily_demand_rate,md.using_requirement_quantity)
,nvl(ms.daily_rate,ms.new_order_quantity)
) peg_order_qty,
ms.implemented_quantity peg_released_qty,
trunc(nvl(md.using_assembly_demand_date,ms.new_schedule_date)) peg_suggested_due_date,
trunc(nvl(ms.new_order_placement_date,ms.firm_date)) peg_suggested_order_date,
trunc(ms.new_wip_start_date) peg_suggested_start_date,
case when mfpd.pegging_id is not null
then
round(greatest(0,md.using_assembly_demand_date - (trunc(sysdate))),2)
else
round(greatest(0,ms.new_order_placement_date - (trunc(sysdate))),2)
end peg_days_from_today,
trunc(ms.need_by_date) peg_need_by_date,
trunc(nvl(md.promise_date,ms.promised_date)) peg_promise_date,
trunc(nvl(md.old_demand_date,ms.old_schedule_date)) peg_old_due_date,
trunc(decode(ms.order_type,1,ms.old_dock_date,ms.old_need_by_date)) peg_old_need_by_date,
case
when mfpd.pegging_id is not null
then
decode(md.supply_id
, null, decode(md.origination_type
,6 , decode(msc_get_name.order_type&a2m_dblink (md.plan_id, md.disposition_id, md.sr_instance_id)
,2, null
, msc_get_name.org_code&a2m_dblink (md.source_organization_id,md.source_org_instance_id)
)
,30, decode(msc_get_name.order_type&a2m_dblink (md.plan_id, md.disposition_id, md.sr_instance_id), 2, null, msc_get_name.org_code&a2m_dblink (md.source_organization_id,md.source_org_instance_id))
,1 , decode(mov.plan_type, 5, null, msc_get_name.org_code&a2m_dblink (md.source_organization_id, md.source_org_instance_id))
, msc_get_name.org_code&a2m_dblink (md.source_organization_id, md.source_org_instance_id)
)
, null
)
when mfps.pegging_id is not null
then
msc_get_name.org_code&a2m_dblink (ms.source_organization_id,ms.source_sr_instance_id)
else
null
end peg_source_organization,
msc_get_name.supplier&a2m_dblink (ms.supplier_id) peg_supplier,
msc_get_name.supplier_site&a2m_dblink (ms.supplier_site_id) peg_supplier_site,
case
when mfpd.pegging_id is not null
then
msc_get_name.action&a2m_dblink
( 'MSC_DEMANDS'
, mov.plan_type
, decode(md.plan_id,-1,md.supply_id,md.disposition_id)
, decode(md.source_org_instance_id,null,md.sr_instance_id,-23453,md.sr_instance_id,md.source_org_instance_id)
, md.origination_type
, md.reschedule_flag
, md.demand_id
, null
, null
, md.sales_order_line_split
, md.fill_kill_flag
, null
, md.inventory_item_id
, md.prev_subst_item
, null
, md.plan_id
)
when mfps.pegging_id is not null
then
msc_get_name.action&a2m_dblink
( 'MSC_SUPPLIES'
, mov.bom_item_type
, mov.base_item_id
, mov.wip_supply_type
, ms.order_type
, ms.reschedule_flag
, ms.disposition_status_type
, ms.new_schedule_date
, ms.old_schedule_date
, ms.implemented_quantity
, ms.quantity_in_process
, ms.new_order_quantity
, mov.release_time_fence_code
, ms.reschedule_days
, ms.firm_quantity
, ms.plan_id
, mov.critical_component_flag
, mov.mrp_planning_code
, mov.lots_exist
, ms.item_type_value
, ms.transaction_id
)
else
null
end peg_order_action,
decode(nvl(md.release_status,ms.release_status)
, 2, null
, 1, xxen_util.meaning('Y','YES_NO',0)
, nvl(md.release_status,ms.release_status)
) peg_order_for_release,
decode(nvl(ms.implemented_quantity,-999),-999,null,'Released') peg_order_released_status,
--
mov.transaction_id,
mov.demand_id,
nvl(mfpd.pegging_id,mfps.pegging_id) pegging_id,
nvl(mfpd.end_pegging_id,mfps.end_pegging_id) end_pegging_id,
mfpd.demand_id peg_demand_id,
mfps.transaction_id peg_transaction_id,
mov.supply_qty,
mov.demand_qty,
mov.abs_qty,
mov.supply_cnt,
mov.demand_cnt
from
(select /*+ push_pred(mov)*/
nvl(:p_instance_code,mai.instance_code) instance,
nvl(:p_plan_name,mov.compile_designator) plan,
mov.organization_code organization,
mov.subinventory_code subinventory,
mov.demand_class demand_class,
mov.planner_code planner,
mpl.employee_id planner_emp_id,
mov.buyer_name buyer_name,
mov.project_number project_number,
mov.task_number task_number,
--
msc_get_name.lookup_meaning&a2m_dblink ('MTL_PLANNING_MAKE_BUY',mov.planning_make_buy_code)
make_buy,
mov.item_segments item,
mov.description item_description,
:p_category_set_name category_set,
mov.category_name category,
(select distinct
max(mss.safety_stock_quantity) keep (dense_rank last order by mss.period_start_date) over (partition by mss.organization_id,mss.inventory_item_id) safety_stock
from
msc_safety_stocks&a2m_dblink mss
where
mss.sr_instance_id = mov.sr_instance_id
and mss.plan_id = mov.plan_id
and mss.organization_id = mov.organization_id
and mss.inventory_item_id = mov.inventory_item_id
and mss.period_start_date <= sysdate
) safety_stock,
--
trunc(mov.new_due_date) suggested_due_date,
xxen_util.meaning(decode(mov.source_table,'MSC_SUPPLIES','SUPPLY','DEMAND'),'MSC_QUESTION_TYPE',3)
supply_demand,
mov.order_type_text order_type,
mov.order_type order_type_num,
mov.order_number order_number,
--
case when mov.firm_date is not null
then trunc(mov.firm_date)
else
case when
mov.firm_planned_type = 1 and
( (mov.source_table = 'MSC_SUPPLIES' and mov.order_type in (1,3,5,51,53,76,77,78,79,81))
or (mov.source_table = 'MSC_SUPPLIES' and mov.order_type = 2 and mov.source_organization_id != mov.organization_id)
or (mov.source_table = 'MSC_DEMANDS' and mov.order_type in (29,30,53,54))
)
then
case when (mov.source_table = 'MSC_DEMANDS' and mov.order_type in (53,54))
then nvl(mov.ship_date,mov.new_due_date)
else trunc(greatest(sysdate,mov.new_due_date))
end
else
to_date(null)
end
end new_date,
case when mov.firm_quantity is not null
then mov.firm_quantity
else
case when
mov.firm_planned_type = 1 and
( (mov.source_table = 'MSC_SUPPLIES' and mov.order_type in (1,3,5,51,53,76,77,78,79,81))
or (mov.source_table = 'MSC_SUPPLIES' and mov.order_type = 2 and mov.source_organization_id != mov.organization_id)
or (mov.source_table = 'MSC_DEMANDS' and mov.order_type in (29,30,53,54))
)
then abs(mov.quantity_rate)
else to_number(null)
end
end new_quantity,
--
trunc(mov.new_order_date) suggested_order_date,
trunc(mov.new_start_date) suggested_start_date,
mov.days_from_today days_from_today,
trunc(mov.need_by_date) need_by_date,
trunc(mov.promise_date) promise_date,
trunc(mov.old_due_date) old_due_date,
trunc(mov.old_need_by_date) old_need_by_date,
--
mov.quantity_rate qty,
mov.implemented_quantity released_qty,
--
mov.implement_date,
mov.implement_quantity_rate,
mov.implement_as_text implement_as,
coalesce(
(select
mtps.location
from
msc_trading_partner_sites&a2m_dblink mtps,
msc_trading_partners&a2m_dblink mtp
where
mtp.partner_id = mtps.partner_id and
mtp.sr_tp_id = case when mov.source_table = 'MSC_DEMANDS' and mov.order_type= 53 then mov.dest_org_id else mov.organization_id end and
mtp.sr_instance_id = mov.sr_instance_id and
mtps.sr_tp_site_id = mov.implement_location_id and
mtp.partner_type = 3 and
rownum=1
),
(select
mla.location_code
from
msc_location_associations&a2m_dblink mla,
msc_trading_partners&a2m_dblink mtp
where
mtp.sr_instance_id = mla.sr_instance_id and
mtp.partner_id = mla.partner_id and
mtp.sr_tp_id = mla.organization_id and
mtp.sr_tp_id = case when mov.source_table = 'MSC_DEMANDS' and mov.order_type= 53 then mov.dest_org_id else mov.organization_id end and
mtp.sr_instance_id = mov.sr_instance_id and
mla.location_id = mov.implement_location_id and
mtp.partner_type = 3 and
rownum=1
)
) implement_location,
--
decode(mov.firm_planned_type
, 2, null
, 1, 'Yes'
,mov.firm_planned_type) firm,
trunc(mov.firm_date) firm_date,
mov.firm_quantity firm_quantity,
--
mov.source_organization_code source_organization,
mov.source_vendor_name source_vendor_name,
mov.source_vendor_site_code source_vendor_site_code,
mov.supplier_name supplier,
mov.supplier_site_code supplier_site,
--
mov.action action_required,
decode(mov.release_status
, 2, null, 1, xxen_util.meaning('Y','YES_NO',0), mov.release_status)
for_release,
decode(nvl(mov.implemented_quantity, -999)
, -999, null, 'Released') released_status,
mov.schedule_compression_days schedule_compression_days,
mov.reschedule_days reschedule_days,
--
mov.demand_priority order_priority,
mov.using_assembly_segments using_assembly,
mov.alternate_bom_designator alternate_bom_designator,
mov.alternate_routing_designator alternate_routing_designator,
mov.line_code line_code,
mov.schedule_group_name schedule_group,
mov.build_sequence build_sequence,
mov.planning_group planning_group,
--
mov.intransit_lead_time intransit_lt,
trunc(msi.planning_time_fence_date) planning_time_fence_date,
msi.release_time_fence_days release_time_fence_days,
msi.fixed_lead_time fixed_lt,
msi.variable_lead_time variable_lt,
msi.preprocessing_lead_time pre_processing_lt,
msi.postprocessing_lead_time post_processing_lt,
msi.full_lead_time full_lt,
mov.abc_class abc_class,
decode(mov.source_table,'MSC_SUPPLIES','S','D') s_d_flag,
decode(mov.source_table,'MSC_SUPPLIES',mov.transaction_id,null)
transaction_id,
decode(mov.source_table,'MSC_DEMANDS',mov.transaction_id,null)
demand_id,
mov.sr_instance_id,
mov.plan_id,
mov.organization_id,
msi.inventory_item_id,
msi.sr_inventory_item_id,
mp.plan_type,
msi.in_source_plan,
msi.bom_item_type,
msi.base_item_id,
msi.wip_supply_type,
msi.release_time_fence_code,
msi.critical_component_flag,
msi.mrp_planning_code,
msi.lots_exist,
--
case when mov.source_table = 'MSC_SUPPLIES'
then mov.quantity_rate
else null
end supply_qty,
case when mov.source_table = 'MSC_DEMANDS'
then -mov.quantity_rate
else null
end demand_qty,
abs(mov.quantity_rate) abs_qty,
case when mov.source_table = 'MSC_SUPPLIES'
then 1
else null
end supply_cnt,
case when mov.source_table = 'MSC_DEMANDS'
then 1
else null
end demand_cnt
from
msc_orders_v&a2m_dblink mov,
msc_apps_instances&a2m_dblink mai,
msc_plans&a2m_dblink mp,
msc_system_items&a2m_dblink msi,
msc_planners&a2m_dblink mpl
where
mov.sr_instance_id = mai.instance_id
and mov.plan_id = mp.plan_id
and mov.category_set_id = nvl(:p_category_set_id,(select mcs.category_set_id from msc_category_sets&a2m_dblink mcs where mcs.default_flag = 1 and rownum=1))
and mov.sr_instance_id = msi.sr_instance_id
and mov.plan_id = msi.plan_id
and mov.organization_id = msi.organization_id
and mov.inventory_item_id = msi.inventory_item_id
and mov.organization_id = mpl.organization_id (+)
and mov.planner_code = mpl.planner_code (+)
and ( (mov.order_type NOT IN ( 18, 6, 7, 30, 31))
or (mov.order_type IN (18, 6, 7, 30) and mov.quantity_rate <> 0)
or (mov.order_type = 30 and (mov.fill_kill_flag = 1 or mov.so_line_split =1))
or (mov.order_type = 31 and mov.quantity <> 0)
)
and mov.order_type <> 60
and 1=1
) mov,
msc_full_pegging&a2m_dblink mfpd,
msc_demands&a2m_dblink md,
msc_full_pegging&a2m_dblink mfps,
msc_supplies&a2m_dblink ms
where
-- demand pegged to supply
mfpd.sr_instance_id (+) = nvl2(:p_show_pegging,mov.sr_instance_id,null)
and mfpd.plan_id (+) = nvl2(:p_show_pegging,mov.plan_id,null)
and mfpd.transaction_id (+) = nvl2(:p_show_pegging,mov.transaction_id,null)
and md.sr_instance_id (+) = mfpd.sr_instance_id
and md.plan_id (+) = mfpd.plan_id
and md.demand_id (+) = mfpd.demand_id
-- supply pegged to demand
and mfps.sr_instance_id (+) = nvl2(:p_show_pegging,mov.sr_instance_id,null)
and mfps.plan_id (+) = nvl2(:p_show_pegging,mov.plan_id,null)
and mfps.demand_id (+) = nvl2(:p_show_pegging,mov.demand_id,null)
and ms.sr_instance_id (+) = mfps.sr_instance_id
and ms.plan_id (+) = mfps.plan_id
and ms.transaction_id (+) = mfps.transaction_id
--
order by
instance,
plan,
organization,
planner,
item,
suggested_due_date,
supply_demand,
order_type,
order_number |
