0 Replies Latest reply on Mar 6, 2015 5:09 AM by User910243567

    Need help in getting all Open PO (Standard & BPA)

    User910243567

      Hi All,

       

      I have requirement to fetch all the Open PO (Standard & BPA) for given specific item and fall under given specified date. I was able to develop below query, need your help in correcting or modifying it.

       

      Query

      ----------

      SELECT POH.segment1 into l_order_number
      FROM PO_LINE_LOCATIONS_ALL POLL1,
      PO_LINES_ALL POL,
      PO_HEADERS_ALL POH
      WHEREPOL.PO_LINE_ID = POLL1.PO_LINE_ID
      AND NVL (POLL1.promised_date, POLL1.need_by_date) IN
      (SELECT MIN (
      NVL (pll.promised_date,
      pll.need_by_date))
      FROM po_lines_all pol,
      po_releases_all pra,
      po_line_locations_all pll
      WHEREpol.po_line_id = pll.po_line_id
      AND NVL (pra.po_release_id, 1) =
      NVL (pll.po_release_id, 1)
      AND pol.item_id = p_component_item_id
      AND pra.authorization_status = 'APPROVED'
      AND (  pll.quantity
      - (  pll.QUANTITY_RECEIVED
      + pll.QUANTITY_CANCELLED)) > 0
      AND NVL (TRUNC (pll.promised_date),
      TRUNC (pll.need_by_date)) <=
      TRUNC (ld_date))
      AND pol.item_id = p_component_item_id
      AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
      AND POH.authorization_status = 'APPROVED'
      AND (  POLL1.quantity
      - (POLL1.QUANTITY_RECEIVED + POLL1.QUANTITY_CANCELLED)) >
      0;

       

       

      Thanks