6 Replies Latest reply: Sep 17, 2012 7:38 AM by 962542 RSS

    Link Sales Order and Purchase Order - MRP Pegging - Give Feedback

    user220421
      Oracle Apps Version - 11.5.10.2
      Mfg - Discreate manufacturing module along with P2P and OTC..No Projects Module.
      Soft Pegging enabled on Items..

      Hi Guru,

      we have created a SQL to link SO and PO using MRP_Full_Pegging Table,what are issues you see with this logic..would it always give me correct mapping between SO and PO

      SELECT
      mgr.inventory_item_id Item_ID,
      mtl.segment1 Item,
      ohll.order_number SO_Number,
      --mr.disposition_id,
      mr.compile_designator MRP_Plan,
      --mipo.line_id,
      --poll.line_location_id,
      pohll.segment1 P0_Number,
      mtl.description Item_Description
      FROM
      mrp_gross_requirements mgr,
      MRP_SCHEDULE_DATES msd,
      mtl_sales_orders mso,
      oe_order_headers_all ohll,
      mrp_recommendations mr,
      mrp_item_purchase_orders mipo,
      po_line_locations_all poll,
      PO_HEADERS_ALL pohll,
      mtl_system_items_b mtl
      WHERE
      msd.mps_transaction_id = mgr.DISPOSITION_ID
      and msd.inventory_item_id = mgr.inventory_item_id
      and mtl.inventory_item_id = mgr.inventory_item_id
      and msd.source_sales_order_Id = mso.sales_order_id
      and mso.segment1=ohll.order_number
      and msd.organization_id = XXX -- pl use you inv org id
      and mr.disposition_id=mipo.transaction_id
      And mipo.line_id=poll.po_line_id
      and poll.po_header_id = pohll.po_header_id
      and mr.inventory_item_id = mtl.inventory_item_id
      and mr.compile_designator like '%XXX%' -- pl use MRP plan name
      and rownum <100 -- just to limit result set
      Order By ohll.order_number desc;

      Appreciate your feedback and inputs to make necessary corrections to make it accurate and complete.

      thank you.