3 Replies Latest reply on Oct 5, 2011 2:40 PM by Sandeep Gandhi, Consultant

    PO's pending to receive

    makdutakdu
      hi all

      i need to obtain the po's which are pending to receive ie those PO's which have pending qty

      i tried this but with this query i happen to get those PO's which have no pending qty too
      SELECT pha.segment1,pla.line_num,rsl.item_description,pla.quantity,rsh.receipt_num,rsl.quantity_received,(pla.quantity-rsl.quantity_received)pending_qty
      FROM 
      po_headers_all pha,
      po_lines_all pla ,
      rcv_shipment_headers rsh,
      rcv_shipment_lines rsl
      WHERE pha.po_header_id=pla.po_header_id
      AND rsl.po_header_id=pha.po_header_id
      AND rsl.po_line_id=pla.po_line_id
      AND rsl.item_id = pla.item_id
      AND rsh.shipment_header_id=rsl.shipment_header_id
      GROUP BY pha.segment1,pla.line_num,rsl.item_description,pla.quantity,rsh.receipt_num,rsl.quantity_received
      kindly guide

      thanking in advance
        • 1. Re: PO's pending to receive
          Sandeep Gandhi, Consultant
          I am not sure what you are looking for.

          If you are looking for POs that have not been received at the dock, simply query from po_line_locations_inq_v and use the quantity_received and quantity columns.
          Make sure you skip closed or cancelled POs.

          If you are looking for those POs that been received but not delivered, you need to use rcv_transactions.

          If you want to continue using your current query, try adding this line at the end of your query
          HAVING (pla.quantity - rsl.quantity_received) != 0

          Hope this helps,
          Sandeep Gandhi
          • 2. Re: PO's pending to receive
            makdutakdu
            hi

            thanks for ur guidance

            based on ur advice

            i ve modified the query to include quantity_received inorder to get the PO'S that are pending to receive
            SELECT DISTINCT pha.segment1 po_num,pla.line_num,
            plla.quantity qty_ord ,plla.quantity_received,plla.quantity_accepted,(plla.quantity-plla.quantity_received)pending_qty,
            pha.approved_date,TO_CHAR(pha.approved_date,'YYYY')YEARS,
            msi.segment1,msi.description
            FROM po_headers_all pha ,po_lines_all pla
            ,po_line_locations_all plla
            ,mtl_system_items msi
            WHERE pha.po_header_id =pla.po_header_id
            AND pha.po_header_id=plla.po_header_id
            AND pla.po_line_id=plla.po_line_id
            AND pha.type_lookup_code='STANDARD'
            AND PHA.CLOSED_CODE='OPEN'
            AND plla.quantity > plla.quantity_received
            and plla.quantity_cancelled=0
            and plla.quantity_rejected=0
            AND msi.inventory_item_id=pla.item_id
            is it right to include closed code=OPEN
            should any other changes be done
            kindly guide

            thanking in advance

            Edited by: makdutakdu on Oct 5, 2011 7:26 AM
            • 3. Re: PO's pending to receive
              Sandeep Gandhi, Consultant
              I assume that you are looking for open POs because closed /canceled POs cannot be received.
              Make sure you put NVL around quantity_received, quantity_cancelled, quantity_rejected.

              Hope this helps,
              Sandeep Gandhi
              1 person found this helpful