APIL PO RECEIPT REJECTION REPORT(PO-RCV-INV)_LINK

 SELECT   DISTINCT
           poh.segment1 po_number,
           pol.item_id,
           poh.creation_date po_date,
           aps.vendor_name,
           RSL.TO_ORGANIZATION_ID,
           pol.quantity po_quantity,
           RSH.SHIPMENT_HEADER_ID,
           rsh.receipt_num grn_no,
           rsl.quantity_received receipt_quantity,
           ( (SELECT   SUM (tax_amount)
                FROM   jai_rcv_line_taxes a
               WHERE   rsl.shipment_line_id = a.shipment_line_id(+))
            + (rsl.quantity_received * pol.unit_price))
              receipt_value,
           rsh.creation_date receipt_date,
           DECODE (RT.TRANSACTION_TYPE, 'REJECT', NVL (RT.QUANTITY, 0), 0)
              "REJECTED_QUN",
           rsl.quantity_received
           + DECODE (RT.TRANSACTION_TYPE, 'REJECT', NVL (RT.QUANTITY, 0), 0)
              "RECEIVE_QUN",
           (NVL (RT.QUANTITY, 0) * NVL (pol.unit_price, 0)) "REJECTED_VALUE",
           RT.TRANSACTION_DATE "Rejected_Date",
           rt.attribute2 lr_no,
           rt.attribute3 lr_date
    FROM   po_headers_all poh,
           po_lines_all pol,
           jai_po_taxes jpo,
           po_line_locations_all pll,
           po_distributions_all pda,
           rcv_shipment_headers rsh,
           rcv_shipment_lines rsl,
           rcv_transactions rt,
           ap_suppliers aps,
           hr_all_organization_units_tl hou,
           ap_invoice_lines_all ail,
           ap_payment_schedules_all apsa,
           ap_invoices_all aia
   WHERE       poh.po_header_id = pol.po_header_id
           -- AND jpo.po_line_id(+) = pol.po_line_id
           AND jpo.PO_HEADER_ID = POH.PO_HEADER_ID
           AND pol.po_line_id = pll.po_line_id
           AND pll.line_location_id = pda.line_location_id
           AND rsl.po_header_id = poh.po_header_id
           AND rsh.shipment_header_id = rsl.shipment_header_id
           AND rt.shipment_line_id = rsl.shipment_line_id
           AND ail.invoice_id = aia.invoice_id
           AND rt.transaction_type IN ('REJECT')
           AND poh.vendor_id = aps.vendor_id
           AND poh.org_id = hou.organization_id
           AND pda.po_distribution_id = ail.po_distribution_id
           AND ail.line_type_lookup_code = 'ITEM'
           AND apsa.invoice_id = aia.invoice_id
           AND  trunc(rsh.creation_date) BETWEEN NVL (:from_receipt_date,
                                                      rsh.creation_date)
                                             AND  NVL (:to_receipt_date,
                                                       rsh.creation_date)
           AND pol.item_id BETWEEN NVL (:from_material_code, inventory_item_id)
                               AND  NVL (:to_material_code, inventory_item_id)
           AND RSL.TO_ORGANIZATION_ID = :organization_id
           AND poh.vendor_id = NVl(:vendor_name ,poh.vendor_id)
GROUP BY   poh.segment1,
           pol.item_id,
           poh.creation_date,
           pol.unit_price,
           pol.quantity,
           aps.vendor_name,
           rsl.quantity_received,
           rsl.shipment_line_id,
           apsa.amount_remaining,
           rsh.receipt_num,
           rsl.quantity_received,
           rsl.quantity_shipped,
           pll.need_by_date,
           rsh.creation_date,
           aia.invoice_num,
           aia.invoice_date,
           rt.attribute2,
           rt.attribute3,
           RT.QUANTITY,
           TO_ORGANIZATION_ID,
           RT.TRANSACTION_TYPE,
           RT.TRANSACTION_DATE,
           RSH.SHIPMENT_HEADER_ID
ORDER BY   POH.SEGMENT1, RSH.RECEIPT_NUM