select
mai.instance_code planning_instance,
mp.compile_designator plan_name,
mpo.organization_code organization_code,
msi.planner_code,
msi.buyer_name,
msi.planning_exception_set,
msi.item_name item,
mcs.category_set_name,
mic.category_name,
msc_get_name.lookup_meaning@A2M_DBLINK('MTL_PLANNING_MAKE_BUY',msi.planning_make_buy_code) "Make/Buy",
msc_get_name.lookup_meaning@A2M_DBLINK('MRP_ORDER_TYPE',ms.order_type) supply_type,
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 ) order_number,
msc_get_name.action@A2M_DBLINK
('MSC_SUPPLIES', msi.bom_item_type, msi.base_item_id, msi.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, msi.release_time_fence_code, ms.reschedule_days, ms.firm_quantity
, ms.plan_id, msi.critical_component_flag, msi.mrp_planning_code, msi.lots_exist, ms.item_type_value, ms.transaction_id
) action,
mfp.supply_quantity supply_qty,
mfp.allocated_quantity allocated_qty,
round(mfp.allocated_quantity, 1) pegged_qty,
mfp.demand_quantity demand_qty,
msc_get_name.lookup_meaning@A2M_DBLINK('MSC_DEMAND_ORIGINATION',md.origination_type) peg_type,
case when mfp.demand_id < 0
then msc_get_name.lookup_meaning@A2M_DBLINK('MRP_FLP_SUPPLY_DEMAND_TYPE',mfp.demand_id)
else null
end peg_other,
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(msi.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)
)
) demand_order_number,
nvl(md.demand_priority,md2.demand_priority) demand_priority,
nvl(trunc(md.using_assembly_demand_date) ,mfp.demand_date) demand_date,
trunc(md.old_demand_date) old_demand_date,
trunc(ms.new_schedule_date) new_supply_schedule_date,
trunc(ms.firm_date) firm_supply_date,
ms.firm_quantity firm_supply_qty,
-- prev pegging
msi2.item_name using_assembly_item,
msc_get_name.lookup_meaning@A2M_DBLINK('MSC_DEMAND_ORIGINATION',md2.origination_type) assembly_peg_type,
nvl(md2.order_number,
decode(md2.origination_type
, 1, to_char(md2.disposition_id)
, 3, msc_get_name.job_name@A2M_DBLINK(md2.disposition_id, md2.plan_id, md2.sr_instance_id)
, 22, to_char(md2.disposition_id)
, 50, msc_get_name.maintenance_plan@A2M_DBLINK(md2.schedule_designator_id)
, 70, msc_get_name.maintenance_plan@A2M_DBLINK(md2.schedule_designator_id)
, 92, msc_get_name.maintenance_plan@A2M_DBLINK(md2.schedule_designator_id )
, 29,decode(md2.plan_id
, -11, msc_get_name.designator@A2M_DBLINK(md2.schedule_designator_id)
, decode(msi2.in_source_plan
,1,msc_get_name.designator@A2M_DBLINK(md2.schedule_designator_id, md2.forecast_set_id )
, msc_get_name.scenario_designator@A2M_DBLINK(md2.forecast_set_id, md2.plan_id, md2.organization_id, md2.sr_instance_id)
|| decode(msc_get_name.designator@A2M_DBLINK(md2.schedule_designator_id,md2.forecast_set_id )
, null, null
, '/'||msc_get_name.designator@A2M_DBLINK(md2.schedule_designator_id,md2.forecast_set_id )
)
)
)
, 78, to_char(md2.disposition_id)
, msc_get_name.designator@A2M_DBLINK(md2.schedule_designator_id)
)
) assembly_demand_order,
trunc(md2.using_assembly_demand_date) assembly_demand_date,
md2.request_ship_date assembly_req_ship_date,
round((md2.dmd_satisfied_date - md2.using_assembly_demand_date), 1) assembly_days_late,
md2.demand_priority assembly_demand_priority,
round(mfp2.demand_quantity, 1) assembly_demand_qty,
round(mfp2.allocated_quantity, 1) assembly_allocated_qty
from
msc_apps_instances@A2M_DBLINK mai,
msc_plans@A2M_DBLINK mp,
msc_plan_organizations@A2M_DBLINK mpo,
msc_system_items@A2M_DBLINK msi,
msc_item_categories@A2M_DBLINK mic,
msc_category_sets@A2M_DBLINK mcs,
msc_supplies@A2M_DBLINK ms,
msc_demands@A2M_DBLINK md,
msc_full_pegging@A2M_DBLINK mfp,
--
msc_full_pegging@A2M_DBLINK mfp2,
msc_demands@A2M_DBLINK md2,
msc_system_items@A2M_DBLINK msi2
--
where
mai.instance_id = mp.sr_instance_id
and mp.sr_instance_id = mpo.sr_instance_id
and mp.plan_id = mpo.plan_id
--
and mpo.sr_instance_id = msi.sr_instance_id
and mpo.plan_id = msi.plan_id
and mpo.organization_id = msi.organization_id
--
and msi.sr_instance_id = mic.sr_instance_id
and msi.organization_id = mic.organization_id
and msi.inventory_item_id = mic.inventory_item_id
and mic.sr_instance_id = mcs.sr_instance_id
and mic.category_set_id = mcs.category_set_id
--
and msi.sr_instance_id = ms.sr_instance_id
and msi.plan_id = ms.plan_id
and msi.organization_id = ms.organization_id
and msi.inventory_item_id = ms.inventory_item_id
--
and ms.sr_instance_id = mfp.sr_instance_id
and ms.plan_id = mfp.plan_id
and ms.organization_id = mfp.organization_id
and ms.transaction_id = mfp.transaction_id
--
and mfp.sr_instance_id = md.sr_instance_id (+)
and mfp.plan_id = md.plan_id (+)
and mfp.organization_id = md.organization_id (+)
and mfp.demand_id = md.demand_id (+)
--
and mfp.sr_instance_id = mfp2.sr_instance_id (+)
and mfp.plan_id = mfp2.plan_id (+)
and mfp.organization_id = mfp2.organization_id (+)
and mfp.prev_pegging_id = mfp2.pegging_id (+)
--
and mfp2.sr_instance_id = md2.sr_instance_id (+)
and mfp2.plan_id = md2.plan_id (+)
and mfp2.organization_id = md2.organization_id (+)
and mfp2.demand_id = md2.demand_id (+)
--
and mfp2.sr_instance_id = msi2.sr_instance_id (+)
and mfp2.plan_id = msi2.plan_id (+)
and mfp2.organization_id = msi2.organization_id (+)
and mfp2.inventory_item_id = msi2.inventory_item_id (+)
--
and mai.instance_code = :p_instance_code
and mp.compile_designator = :p_plan_name
and mcs.category_set_name = :p_cat_set_name
and 1=1
--
order by
mai.instance_code,
mp.compile_designator,
mpo.organization_code,
msi.item_name |
