MRP Pegging
Description
Categories: Enginatics
Repository: Github
Repository: Github
Detail report for MRP planning pegging showing the hierarchy from demand, including forecast, sales orders, work order to supply (WIP jobs, purchase orders and on hand stock)
select z.organization_code, z.origination_type, z.demand_number, z.order_line, z.ship_set, z.line_status, z.demand_description, z.created_by, z.project, z.task, z.level_, z.item, z.item_description, z.item_type, z.pegging, z.planner_code, z.planner, z.buyer, z.end_demand_pegged_qty, z.demand_quantity, z.pegged_quantity, z.uom, z.plan_demand_date, z.plan_start_date, z.plan_supply_date, z.plan_delay, z.make_or_buy, z.supply_quantity, z.supply_type, z.supply_number, z.supply_line, z.demand_date, z.supply_date, z.demand_date-z.supply_date delay, xxen_util.client_time(z.need_by_date) need_by_date, xxen_util.client_time(z.promised_date) promised_date, xxen_util.client_time(z.receipt_date) receipt_date, z.department_code, z.operation_sequence, z.operation_code, z.operation_description, z.resource_code, z.resource_description, xxen_util.client_time(z.date_last_moved) date_last_moved, z.in_queue, z.running, z.to_move, z.rejected, z.scrapped, z.completed, z.action, z.reschedule_date, z.exception, z.supplier_name, z.frozen_cost, z.pending_cost, z.on_hand, z.safety_stock, z.min_minmax_quantity, z.forecast, z.preprocessing_lead_time, z.cum_manufacturing_lead_time, z.cumulative_total_lead_time, z.postprocessing_lead_time, xxen_util.client_time(z.schedule_ship_date) schedule_ship_date, z.item_, z.supply_type_, z.item_path, z.item_description_path, nvl(z.demand_date,z.plan_demand_date) nvl_demand_date, nvl(z.supply_date,z.plan_supply_date) nvl_supply_date, z.plan, z.end_pegging_id, z.pegging_id, z.prev_pegging_id, z.inventory_item_id, z.order_line_id, z.vendor_id, z.supply_line_id from ( select y.organization_code, max(nvl2(y.prev_pegging_id,null,y.origination_type)) over (partition by y.end_pegging_id) origination_type, max(nvl2(y.prev_pegging_id,null,y.demand_number)) over (partition by y.end_pegging_id) demand_number, max(nvl2(y.prev_pegging_id,null,y.order_line)) over (partition by y.end_pegging_id) order_line, max(nvl2(y.prev_pegging_id,null,y.ship_set)) over (partition by y.end_pegging_id) ship_set, max(nvl2(y.prev_pegging_id,null,y.line_status)) over (partition by y.end_pegging_id) line_status, max(nvl2(y.prev_pegging_id,null,y.demand_description)) over (partition by y.end_pegging_id) demand_description, max(nvl2(y.prev_pegging_id,null,y.created_by)) over (partition by y.end_pegging_id) created_by, max(nvl2(y.prev_pegging_id,null,y.project)) over (partition by y.end_pegging_id) project, max(nvl2(y.prev_pegging_id,null,y.task)) over (partition by y.end_pegging_id) task, y.level_, y.item, y.item_description, y.item_type, y.pegging, y.planner_code, y.planner, y.buyer, y.end_demand_pegged_qty, y.demand_quantity, y.pegged_quantity, y.uom, y.plan_demand_date, y.plan_start_date, y.plan_supply_date, y.plan_delay, y.make_or_buy, y.supply_quantity, y.supply_type, y.supply_number, nvl(pla.line_num,prla.line_num) supply_line, trunc(xxen_util.client_time(y.demand_date)) demand_date, ( select bcd1.calendar_date from bom_calendar_dates bcd, bom_calendar_dates bcd1 where trunc(xxen_util.client_time(coalesce(y.scheduled_completion_date,plla.promised_date,plla.need_by_date,prla.need_by_date)))=bcd.calendar_date and bcd.next_seq_num+nvl(y.postprocessing_lead_time,0)=bcd1.seq_num and y.calendar_code=bcd.calendar_code and y.calendar_code=bcd1.calendar_code and bcd.exception_set_id=-1 and bcd1.exception_set_id=-1 ) supply_date, nvl(plla.need_by_date,prla.need_by_date) need_by_date, plla.promised_date, (select max(rt.transaction_date) from rcv_transactions rt where plla.line_location_id=rt.po_line_location_id and rt.transaction_type='RECEIVE') receipt_date, y.department_code, y.operation_sequence, y.operation_code, y.operation_description, y.resource_code, y.resource_description, y.date_last_moved, y.in_queue, y.running, y.to_move, y.rejected, y.scrapped, y.completed, xxen_util.meaning(y.action_id,'MRP_ACTIONS',700) action, y.reschedule_date, y.exception, aps.vendor_name supplier_name, y.frozen_cost, y.pending_cost, y.on_hand, y.safety_stock, y.min_minmax_quantity, y.forecast, y.preprocessing_lead_time, y.cum_manufacturing_lead_time, y.cumulative_total_lead_time, y.postprocessing_lead_time, y.schedule_ship_date, y.item_, y.supply_type_, y.item_path, y.item_description_path, y.plan, y.end_pegging_id, y.pegging_id, y.prev_pegging_id, y.inventory_item_id, y.order_line_id, y.vendor_id, y.supply_line_id from ( select nvl2(x.prev_pegging_id,null,xxen_util.meaning( case when x.demand_id<0 then x.demand_id else mgr.origination_type end, case when x.demand_id<0 then 'MRP_FLP_SUPPLY_DEMAND_TYPE' else 'MRP_DEMAND_ORIGINATION' end,700)|| nvl2(flv.meaning,'/'||flv.meaning,null)) origination_type, coalesce(we0.wip_entity_name,mipo0.po_number,to_char(ooha.order_number)) demand_number, rtrim(oola.line_number||'.'||oola.shipment_number||'.'||oola.option_number||'.'||oola.component_number||'.'||oola.service_number,'.') order_line, (select os.set_name from oe_sets os where oola.ship_set_id=os.set_id) ship_set, xxen_util.meaning(oola.flow_status_code,'LINE_FLOW_STATUS',660) line_status, nvl(ottt.name,msiv0.concatenated_segments||' '||msiv0.description) demand_description, xxen_util.user_name(nvl(we0.created_by,ooha.created_by)) created_by, nvl2(x.prev_pegging_id,null,case when x.demand_id in (-1,-3) then mrp_get_project.project(x.project_id) else mrp_get_project.project(mgr.project_id) end) project, nvl2(x.prev_pegging_id,null,case when x.demand_id in (-1,-3) then mrp_get_project.task(x.task_id) else mrp_get_project.task(mgr.task_id) end) task, lpad(' ',2*(x.level__-1))||(x.level__-1) level_, lpad(' ',2*(x.level__-1))||x.item item, x.item_description, xxen_util.meaning(x.item_type,'ITEM_TYPE',3) item_type, xxen_util.meaning(x.end_assembly_pegging_flag,'ASSEMBLY_PEGGING_CODE',0) pegging, x.planner_code, mpl.description planner, (select ppx.full_name from per_people_x ppx where x.buyer_id=ppx.person_id and rownum=1) buyer, round(nvl(x.allocated_quantity/xxen_util.zero_to_null(x.end_item_usage),0),4) end_demand_pegged_qty, round(x.demand_quantity,4) demand_quantity, round(x.allocated_quantity,4) pegged_quantity, muot.unit_of_measure_tl uom, x.demand_date plan_demand_date, mr.new_wip_start_date plan_start_date, x.supply_date plan_supply_date, x.demand_date-x.supply_date plan_delay, xxen_util.meaning(x.planning_make_buy_code,'MTL_PLANNING_MAKE_BUY',700) make_or_buy, round(x.supply_quantity,4) supply_quantity, lpad(' ',2*(x.level__-1))||xxen_util.meaning(x.supply_type,'MRP_ORDER_TYPE',700) supply_type, nvl(we.wip_entity_name,mipo.po_number) supply_number, wdj0.scheduled_start_date demand_date, wdj.scheduled_completion_date, bd.department_code, wo.operation_seq_num operation_sequence, bso.operation_code, wo.description operation_description, br.resource_code, br.description resource_description, wo.date_last_moved, wo.in_queue, wo.running, wo.to_move, wo.rejected, wo.scrapped, wo.completed, case when x.bom_item_type in (1,2,3,5) or x.base_item_id is not null and mwdo.orders_release_configs='N' or x.wip_supply_type=6 and mwdo.orders_release_phantoms='N' or mr.order_type in (14,15,16,17,18,19) or mr.rescheduled_flag=1 and nvl(mr.release_status,2)=2 then null --None when mr.disposition_status_type=2 then 1 --Cancel when mr.new_schedule_date>mr.old_schedule_date then 3 --Reschedule Out when mr.new_schedule_date<mr.old_schedule_date then 2 --Reschedule In when mr.order_type=5 then --Planned order case when nvl(mr.implemented_quantity,0)+nvl(mr.quantity_in_process,0)>=nvl(mr.firm_quantity,mr.new_order_quantity) and nvl(mr.release_status,2)=2 then null --None, planned order has been released else 4 --Release end else null --None end action_id, case when mr.new_schedule_date<>mr.old_schedule_date then decode(x.planning_make_buy_code,1,mr.new_wip_start_date,mr.new_dock_date) end reschedule_date, ( select distinct listagg(med.exception_type_meaning,', ') within group (order by med.exception_type_meaning) over () from ( select distinct med.number1, med.inventory_item_id, med.compile_designator, med.organization_id, xxen_util.meaning(med.exception_type,'MRP_EXCEPTION_CODE_TYPE',700) exception_type_meaning from mrp_exception_details med where med.exception_type in (1,2,3,4,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29) ) med where x.transaction_id=med.number1 and x.inventory_item_id=med.inventory_item_id and x.compile_designator=med.compile_designator and x.organization_id=med.organization_id ) exception, cic1.item_cost frozen_cost, cic3.item_cost pending_cost, moqd.on_hand, (select distinct max(mss.safety_stock_quantity) keep (dense_rank last order by mss.effectivity_date) over (partition by mss.organization_id,mss.inventory_item_id) safety_stock from mtl_safety_stocks mss where x.organization_id=mss.organization_id and x.inventory_item_id=mss.inventory_item_id and mss.effectivity_date<=sysdate) safety_stock, x.min_minmax_quantity, ( select sum(mfd.current_forecast_quantity) from mrp_forecast_designators mfds, mrp_forecast_dates mfd where x.organization_id=mfds.organization_id and mfds.forecast_designator=mfd.forecast_designator and x.organization_id=mfd.organization_id and x.inventory_item_id=mfd.inventory_item_id ) forecast, x.preprocessing_lead_time, x.cum_manufacturing_lead_time, x.cumulative_total_lead_time, x.postprocessing_lead_time, oola.schedule_ship_date, x.item item_, xxen_util.meaning(x.supply_type,'MRP_ORDER_TYPE',700) supply_type_, x.item_path, x.item_description_path, x.compile_designator plan, mp.organization_code, mp.calendar_code, x.end_pegging_id, x.pegging_id, x.prev_pegging_id, x.inventory_item_id, decode(mgr.origination_type,6,mgr.reservation_id) order_line_id, x.supply_type supply_type_id, nvl(mipo.vendor_id,msa.vendor_id) vendor_id, case when mr.order_type in (1,8) then ( --try to find matching po line_location_id as MRP unfortunately only tracks the line_id, but not line_location_id select plla.line_location_id from (select count(*) over (partition |