purchase to invoice link

SELECT   poh.org_id "ORGANIZATION", poh.segment1 "PO NO",
         poh.creation_date "PO DATE", SUM (pol.quantity) "PO QUANTITY",
         rch.receipt_num "GRN NO", rct.transaction_date "GRN DATE",
         SUM (pll.quantity_received) "RECEIVED QTY",
         SUM (pll.quantity_accepted) "ACCEPTED QTY",
         SUM (ail.quantity_invoiced) "INVOICED QTY",
         aia.amount_paid "PAYMENT AMOUNT", apc.check_date "DATE OF PAYMENT",
         apt.NAME "PAYMENT_TERM",
         pol.ITEM_ID
FROM     po_headers_all poh,
         po_lines_all pol,
         po_line_locations_all pll,
         po_distributions_all pda,
         rcv_shipment_headers rch,
         rcv_shipment_lines rcl,
         rcv_transactions rct,
         ap_invoice_lines_all ail,
         ap_invoices_all aia,
         ap_invoice_distributions_all aid,
         ap_checks_all apc,
         ap_invoice_payments_all aip,
         ap_terms apt
WHERE    poh.po_header_id = pol.po_header_id
AND      pol.item_id IN (SELECT distinct inventory_item_id
                         FROM mtl_system_items_b
                         WHERE segment1 BETWEEN NVL (:from_item_code, segment1)
                         AND NVL (:to_item_code, segment1))
         AND pol.po_line_id = pll.po_line_id
         AND pll.line_location_id = pda.line_location_id
         AND pda.po_distribution_id = rcl.po_distribution_id
         AND rch.shipment_header_id = rcl.shipment_header_id
         AND rcl.shipment_line_id = rct.shipment_line_id
         AND ail.invoice_id = aia.invoice_id
         AND aia.invoice_id = aid.invoice_id
         AND rct.transaction_id = aid.rcv_transaction_id
         AND aip.invoice_id(+) = aia.invoice_id
         AND aip.check_id = apc.check_id(+)
         AND apt.term_id = aia.terms_id
         AND TRUNC (rct.transaction_date) BETWEEN NVL (:from_date,
                                                   rct.transaction_date
                                                  )
                                          AND NVL (:TO_DATE,
                                                   rct.transaction_date
                                                  )
         AND poh.segment1 = nvl(:po_num,poh.segment1)
         AND poh.org_id = NVL (:org_id, poh.org_id)
GROUP BY poh.org_id,
         poh.segment1,
         poh.creation_date,
         rch.receipt_num,
         aia.amount_paid,
         rct.transaction_date,
         apc.check_date,
         apt.NAME,
         pol.ITEM_ID

=================================================================

select prha.SEGMENT1 req_no,
       prha.CREATION_DATE req_date,
       pv.VENDOR_NAME,
       pha.SEGMENT1 po_no,
       pha.CREATION_DATE po_date,
       prla.ITEM_DESCRIPTION,
       sum(pla.QUANTITY) po_qty,
       pla.UNIT_PRICE,
       rsh.RECEIPT_NUM,
       rt.TRANSACTION_DATE rec_date,
       rt.TRANSACTION_TYPE,
       aia.INVOICE_NUM,
       aia.INVOICE_DATE,
       aia.INVOICE_AMOUNT,
       aipa.PAYMENT_BASE_AMOUNT,
       aipa.AMOUNT,
       apsa.PAYMENT_METHOD_CODE,
       aca.CHECK_NUMBER,
       aca.CHECK_DATE
from po_requisition_headers_all prha,
     po_requisition_lines_all prla,
     po_req_distributions_all prda,
     po_distributions_all pda,
     po_lines_all pla,
     po_headers_all pha,
     po_vendors pv,
     rcv_transactions rt,
     rcv_shipment_headers rsh,
     ap_invoice_distributions_all aida,
     ap_invoice_lines_all aila,
     ap_invoices_all aia,
     ap_invoice_payments_all aipa,
     ap_payment_schedules_all apsa,
     ap_checks_all aca
where prha.REQUISITION_HEADER_ID=prla.REQUISITION_HEADER_ID
and   prla.REQUISITION_LINE_ID=prda.REQUISITION_LINE_ID
and   pda.REQ_DISTRIBUTION_ID=prda.DISTRIBUTION_ID
and   pla.PO_LINE_ID=pda.PO_LINE_ID
and   pha.PO_HEADER_ID=pla.PO_HEADER_ID
and pv.VENDOR_ID=pha.VENDOR_ID
and pda.PO_DISTRIBUTION_ID=rt.PO_DISTRIBUTION_ID
and rsh.SHIPMENT_HEADER_ID=rt.SHIPMENT_HEADER_ID
and rt.TRANSACTION_ID=aida.RCV_TRANSACTION_ID
and aila.INVOICE_ID=aida.INVOICE_ID
and aia.INVOICE_ID=aila.INVOICE_ID
and aia.INVOICE_ID=aipa.INVOICE_ID
and aia.INVOICE_ID=apsa.INVOICE_ID
and aca.CHECK_ID=aipa.CHECK_ID
and pha.SEGMENT1='7162'--nvl(:p_po_no,pha.SEGMENT1)
group by prha.SEGMENT1,prha.CREATION_DATE,pv.VENDOR_NAME,
pha.SEGMENT1 ,pha.CREATION_DATE ,
prla.ITEM_DESCRIPTION,pla.UNIT_PRICE,
rsh.RECEIPT_NUM, rt.TRANSACTION_DATE ,rt.TRANSACTION_TYPE,
aia.INVOICE_NUM,aia.INVOICE_DATE,aia.INVOICE_AMOUNT,
aipa.PAYMENT_BASE_AMOUNT,aipa.AMOUNT,apsa.PAYMENT_METHOD_CODE,
aca.CHECK_NUMBER,aca.CHECK_DATE