5 Replies Latest reply: Apr 23, 2014 5:19 AM by 1498181 RSS

    query for net PO Receiving

    1498181

      Hi,

      I have completed a complete Procure to Pay cycle and making queries for this cycle. i also have few return to receiving transactions. How would i make a query to show only net quantity received against PO?

        • 1. Re: query for net PO Receiving
          AutoInstall

          The sum of po_line_locations_all.quantity_received / quantity_accepted. Not sure which one reflects Return to Receiving.

           

          Or try this API, AP_MATCHING_UTILS_PKG.GET_RECEIPT_QUANTITIES It is based on receiving transaction.

          • 2. Re: query for net PO Receiving
            PranitSaha

            Easiest way to refer to the QUANTITY_RECEIVED column in the PO_LINE_LOCATIONS_ALL table to get the actual received quantity.

            • 3. Re: query for net PO Receiving
              1498181

              i am picking up the records against DELIVER transactions from RCV_TRANSACTIONS table joined with RCV_SHIPMENT_HEADERS and RCV_SHIPMENT_LINES.

              if i pick RCV_TRANSACTIONS.QUANTITY, it does not exclude RETURN TO VENDOR / RETURN TO RECEIVING.

              and if i pick RCV_SHIPMENT_LINES.QUANTITY_RECEIVED, it shows duplicate quantity in case of partial receiving. e.g( RECEIVE 20 and DELIVER 10 first time. then DELIVER 10 another time). QUANTITY_RECEIVED will show 20 and then again 20.

               

              PO_LINE_LOCATIONS_ALL.QUANTITY_RECEIVED does not exclude RETURN_TO_RECEIVING. obviously it is receiving column. i think if i pick up PO_DISTRIBUTIONS_ALL.QUANTITY_DELIVERED, it shows the actual net quantity delivered excluding all returns.

              • 4. Re: query for net PO Receiving
                Hemant Garg

                for others reference can you please post the query as well .

                • 5. Re: query for net PO Receiving
                  1498181

                  i have finalized my query as below. my target was to get net delivered quantity. (DELIVER - RETURN TO RECEIVING)

                  If you find any problem in this query, plz let me know.

                   

                  select receipt_num

                         ,po_num

                         ,max(transaction_date) max_date

                         ,item_name

                         ,item_desc

                         ,(sum(line_qty) / count(line_qty)) po_line_qty

                         ,sum(net_qty_delvr) qty_deliver

                  from

                  (

                  select rsh.receipt_num

                         ,rsl.po_line_id

                         ,(select segment1 from po_headers_all where po_header_id = rsl.po_header_id) po_num

                         ,(select quantity from po_lines_all where po_header_id = rsl.po_header_id and po_line_id = rsl.po_line_id) line_qty

                         ,rt.transaction_date

                         ,msi.segment1 item_name

                         ,nvl((select nvl(mst.description,msi.description) from mtl_system_items_tl mst where mst.inventory_item_id = msi.inventory_item_id

                         and mst.organization_id = msi.organization_id and nvl(mst.language,'&LANG') = '&LANG'),rsl.item_description) item_desc

                         ,decode(rt.transaction_type,'DELIVER',rt.quantity,rt.quantity * -1) net_qty_delvr

                  from rcv_shipment_headers rsh  

                      ,rcv_shipment_lines rsl

                      ,rcv_transactions rt

                      ,mtl_system_items_b msi

                  where rsh.shipment_header_id = rsl.shipment_header_id

                  and rsl.shipment_line_id = rt.shipment_line_id

                  and rsh.shipment_header_id = rt.shipment_header_id

                  and rsh.ship_to_org_id = rt.organization_id

                  and rt.transaction_type in ('DELIVER','RETURN TO RECEIVING')

                  and rt.source_document_code = 'PO'

                  and rt.po_release_id is null

                  and rsl.item_id = msi.inventory_item_id(+)

                  and nvl(rsl.from_organization_id,7311) = msi.organization_id(+)

                  and rt.organization_id = 7311

                  )

                  group by receipt_num

                         ,po_num

                         ,item_name

                         ,item_desc

                         ,po_line_id