Purchase order (PO) with invoice payment detail

/* Formatted on 4/27/2016 9:38:24 AM (QP5 v5.114.809.3010) */
SELECT   a.org_id "ORG ID",
         E.SEGMENT1 "VENDOR NUM",
         e.vendor_name "SUPPLIER NAME",
         UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
         f.vendor_site_code "VENDOR SITE CODE",
         f.ADDRESS_LINE1 "ADDRESS",
         f.city "CITY",
         f.country "COUNTRY",
         TO_CHAR (TRUNC (d.CREATION_DATE)) "PO Date",
         d.segment1 "PO NUM",
         d.type_lookup_code "PO Type",
         c.quantity_ordered "QTY ORDERED",
         c.quantity_cancelled "QTY CANCELLED",
         g.item_id "ITEM ID",
         g.item_description "ITEM DESCRIPTION",
         g.unit_price "UNIT PRICE",
         (NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0))
         * NVL (g.unit_price, 0)
            "PO Line Amount",
         (SELECT   DECODE (ph.approved_FLAG, 'Y', 'Approved')
            FROM   po.po_headers_all ph
           WHERE   ph.po_header_ID = d.po_header_id)
            "PO Approved?",
         a.invoice_type_lookup_code "INVOICE TYPE",
         a.invoice_amount "INVOICE AMOUNT",
         TO_CHAR (TRUNC (a.INVOICE_DATE)) "INVOICE DATE",
         a.invoice_num "INVOICE NUMBER",
         (SELECT   DECODE (x.MATCH_STATUS_FLAG, 'A', 'Approved')
            FROM   ap.ap_invoice_distributions_all x
           WHERE   x.INVOICE_DISTRIBUTION_ID = b.invoice_distribution_id)
            "Invoice Approved?",
         a.amount_paid,
         h.amount,
         h.check_id,
         h.invoice_payment_id "Payment Id",
         i.check_number "Cheque Number",
         TO_CHAR (TRUNC (i.check_DATE)) "PAYMENT DATE"
  FROM   AP.AP_INVOICES_ALL A,
         AP.AP_INVOICE_DISTRIBUTIONS_ALL B,
         PO.PO_DISTRIBUTIONS_ALL C,
         PO.PO_HEADERS_ALL D,
         AP_SUPPLIERS E,
         AP_SUPPLIER_SITES_ALL F,
         PO.PO_LINES_ALL G,
         AP.AP_INVOICE_PAYMENTS_ALL H,
         AP.AP_CHECKS_ALL I
 WHERE       a.invoice_id = b.invoice_id
         AND b.po_distribution_id = c.po_distribution_id(+)
         AND c.po_header_id = d.po_header_id(+)
         AND e.vendor_id(+) = d.VENDOR_ID
         AND f.vendor_site_id(+) = d.vendor_site_id
         AND d.po_header_id = g.po_header_id
         AND c.po_line_id = g.po_line_id
         AND a.invoice_id = h.invoice_id
         AND h.check_id = i.check_id
         AND f.vendor_site_id = i.vendor_site_id
         AND c.PO_HEADER_ID IS NOT NULL
         AND a.payment_status_flag = 'Y'
         AND d.type_lookup_code != 'BLANKET'
        
You need to know the link to GL_JE_LINES table for purchasing accrual and budgetary control actions..

The budgetary (encumbrance) and accrual actions in the purchasing module generate records that will be imported into GL for the corresponding accrual and budgetary journals.

The following reference fields are used to capture and keep PO information in the GL_JE_LINES table.

These reference fields are populated when the Journal source (JE_SOURCE in GL_JE_HEADERS) is Purchasing.

Budgetary Records from PO (These include reservations, reversals and cancellations):

    REFERENCE_1    - Source (PO or REQ)
    REFERENCE_2    - PO Header ID or Requisition Header ID (from po_headers_all.po_header_id    or  po_requisition_headers_all.requisition_header_id)
    REFERENCE_3    - Distribution ID (from po_distributions_all.po_distribution_id or
    po_req_distributions_all.distribution_id)
    REFERENCE_4    - Purchase Order or Requisition number (from po_headers_all.segment1 or
    po_requisition_headers_all.segment1)
    REFERENCE_5   - (Autocreated Purchase Orders only) Backing requisition number (from po_requisition_headers_all.segment1)

Accrual Records from PO:

    REFERENCE_1   - Source (PO)
    REFERENCE_2   - PO Header ID (from po_headers_all.po_header_id)
    REFERENCE_3   - Distribution ID (from po_distributions_all.po_distribution_id
    REFERENCE_4   - Purchase Order number (from po_headers_all.segment1)
    REFERENCE_5   - (ON LINE ACCRUALS ONLY) Receiving Transaction ID (from rcv_receiving_sub_ledger.rcv_transaction_id)

Take a note for Period end accruals, the REFERENCE_5 column is not used.