Returned items up against the purchase order

Find out items returned up against the purchase order to supplier.
  SELECT APS.VENDOR_NAME "SUPPLIER",
         ph.segment1 "PO NUM",
         MSI.DESCRIPTION,
         TO_NUMBER (QUANTITY_RECEIVED) " NET RECIEVED",
         (QUANTITY_SHIPPED - QUANTITY_RECEIVED) "RETURNED",
         rct.transaction_type,
         TRUNC (RCT.TRANSACTION_DATE) TRANSACTION_DATE
    FROM RCV_SHIPMENT_HEADERS RSH,
         RCV_SHIPMENT_LINES RSL,
         RCV_TRANSACTIONS RCT,
         PO_LINES_ALL PL,
         PO_HEADERS_ALL PH,
         PO_LINE_TYPES PLT,
         RCV_LOT_TRANSACTIONS LOT,
         MTL_SYSTEM_ITEMS_B MSI,
         AP_SUPPLIERS APS
   WHERE     RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
         AND RCT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
         AND PL.PO_LINE_ID = RCT.PO_LINE_ID
         AND PH.PO_HEADER_ID = PL.PO_HEADER_ID
         AND PLT.LINE_TYPE_ID = PL.LINE_TYPE_ID
         AND lot.TRANSACTION_ID(+) = RCT.TRANSACTION_ID
         AND MSI.INVENTORY_ITEM_ID = PL.ITEM_ID
         AND RCT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
         AND APS.VENDOR_ID = RCT.VENDOR_ID
         --   and ph.segment1='&PO_NUM'
         AND rct.transaction_type = 'RETURN TO VENDOR'
         AND NVL (TO_NUMBER (rct.quantity_billed), 0) = '0'
         AND TRUNC (RCT.TRANSACTION_DATE) BETWEEN TO_DATE ('01-05-2020',
                                                           'dd-mm-yyyy')
                                              AND  TO_DATE ('30-06-2020',
                                                            'dd-mm-yyyy')
ORDER BY APS.VENDOR_NAME DESC
Returned items up against the purchase order

Tags
