Invoices, Purchase Orders, Suppliers and Payments - Single Query

Hi Viewers,

There is a need to know the PO Matched Invoices and Supplier and Payment details along with Invoice totals and PO totals in a single query for audit purpose.

The below query will give you the details .

 Please replace ORG_ID with Operating unit ID to set the Org context
EXEC mo_global.set_policy_context('S','ORG_ID');

SELECT details.org_id,
       details.vendor_id,
       details.vendor_number,
       details.vendor_name,
       details.vendor_site_id,
       details.vendor_site_code,
       details.tolerance_name,
       details.addressee,
       details.invoice_id,
       details.invoice_num,
       details.inv_creation_date,
       details.payment_method_code,
       details.invoice_amount inv_total_amount,
       details.inv_item_total,
       details.inv_tax_total,
       details.inv_freight_total,
       details.check_number,
       details.payment_date,
       details.po_header_id,
       details.po_creation_date,
       details.po_number,
       (NVL (details.po_amount, 0) + NVL (details.po_tax_amount, 0) + NVL (details.po_freight_amount, 0)) po_total_amount,
       details.po_amount po_items_total,
       details.po_tax_amount po_tax_total,
       details.po_freight_amount po_freight_total,
       details.Matched_amount_to_invoice      
  FROM (  SELECT aia.org_id,
                 asu.vendor_id,
                 asu.segment1 vendor_number,
                 asu.vendor_name,
                 assa.vendor_site_id,
                 assa.vendor_site_code,
                 att.tolerance_name,
                 hps.addressee,
                 aia.INVOICE_ID,
                 aia.INVOICE_NUM,
                 aia.creation_date inv_creation_date,
                 aia.invoice_amount,
                 AP_INVOICES_UTILITY_PKG.get_item_total ( aia.invoice_id, aia.org_id) inv_item_total,
                 (SELECT SUM (NVL (amount, 0)) - SUM (NVL (included_tax_amount, 0))
                    FROM ap_invoice_lines_all aila
                   WHERE aila.invoice_id = aia.invoice_id
                         AND line_type_lookup_code IN ('TAX'))
                    inv_tax_total,
                 AP_INVOICES_UTILITY_PKG.get_freight_total ( aia.invoice_id, aia.org_id) inv_freight_total,
                 aca.CHECK_NUMBER "CHECK_NUMBER",
                 TO_CHAR (TRUNC (aca.CHECK_DATE)) "PAYMENT_DATE",
                 poh.po_header_id,
                 poh.creation_date po_creation_date,
                 AP_INVOICES_UTILITY_PKG.get_po_number (aia.invoice_id) Po_Number,
                 (SELECT SUM (pll.unit_price * pll.quantity)
                    FROM po_lines_all pll, po_line_types ptt
                   WHERE     pll.po_header_id = poh.po_header_id
                         AND pll.line_type_id = ptt.line_type_id
                         AND ptt.line_type <> 'Freight')
                    po_amount,
                 (SELECT SUM (aida.amount)
                    FROM ap_invoice_distributions_all aida, po_distributions_all pda
                   WHERE     1 = 1
                         AND invoice_id = aia.invoice_id
                         AND aida.po_distribution_id = pda.po_distribution_id
                         AND pda.po_header_id = poh.po_header_id                        
                         AND NVL (tax_recoverable_flag, 'Y') <> 'N')
                    Matched_amount_to_invoice,
                 (SELECT SUM (NVL (pda.recoverable_tax, 0) + NVL (pda.nonrecoverable_tax, 0))
                    FROM po_distributions_all pda, po_lines_all pla
                   WHERE     pda.po_header_id = poh.po_header_id
                         AND pla.po_header_id = poh.po_header_id
                         AND pda.po_line_id = pla.po_line_id
                         AND NVL (pla.cancel_flag, 'N') <> 'Y')
                    po_tax_amount,
                 (SELECT SUM (pll.unit_price * pll.quantity)
                    FROM po_lines_all pll, po_line_types ptt
                   WHERE     po_header_id = poh.po_header_id
                         AND pll.line_type_id = ptt.line_type_id
                         AND NVL (pll.cancel_flag, 'N') <> 'Y'
                         AND ptt.line_type = 'Freight')
                    po_freight_amount,
                 aia.payment_method_code
            FROM AP_INVOICES_ALL aia,
                 ap_suppliers asu,
                 ap_supplier_sites_all assa,
                 hz_party_sites hps,
                 po_headers_all poh,
                 AP_TOLERANCE_TEMPLATES ATT,
                 AP_INVOICE_PAYMENTS_ALL aipa,
                 AP_CHECKS_ALL aca
           WHERE     AP_INVOICES_UTILITY_PKG.get_po_number (aia.invoice_id) <> 'UNMATCHED'
                 AND aia.vendor_id = asu.vendor_id
                 AND aia.vendor_site_id = assa.vendor_site_id
                 AND asu.vendor_id = assa.vendor_id
                 AND assa.party_site_id = hps.party_site_id
                 AND assa.tolerance_id = att.tolerance_id
                 AND AP_INVOICES_UTILITY_PKG.get_po_number (aia.invoice_id) = poh.segment1
                 AND aia.INVOICE_ID = aipa.INVOICE_ID(+)
                 AND aipa.CHECK_ID = aca.CHECK_ID(+)
                 AND NVL (aipa.reversal_flag, 'N') <> 'Y'
        ORDER BY asu.segment1,
                 hps.addressee,
                 poh.segment1,
                 aia.invoice_num,
                 aia.creation_date) details
 WHERE 1 = 1