AP Payment Distribution (Oracle Fusion SQL)

Enterprise Resource Planning (ERP) Oracle Account Payables (AP-Fusion-SQL's)
1

This AP Payment Distribution Report query retrieves detailed AP payment distribution information including payment number, date, amount, invoice details, supplier info, and the associated GL account.

#sqlquery

SELECT
  --SQL4Fusion (An Orbit Analytics Project) 
  --This query retrieves detailed payment distribution information
  ACA.CHECK_NUMBER PAYMENT_NUMBER,
  ACA.CHECK_DATE PAYMENT_DATE,
  ACA.CURRENCY_CODE,
  ACA.AMOUNT PAYMENT_AMOUNT,
  ACA.STATUS_LOOKUP_CODE PAYMENT_STATUS,
  AIPA.INVOICE_PAYMENT_TYPE,
  AIA.INVOICE_NUM INVOICE_NUMBER,
  AIA.INVOICE_DATE,
  AIA.INVOICE_AMOUNT,
  AIDA.DISTRIBUTION_LINE_NUMBER,
  AIDA.AMOUNT DISTRIBUTION_AMOUNT,
  AIDA.ACCOUNTING_DATE,
  PSV.VENDOR_NAME SUPPLIER_NAME,
  PSV.SEGMENT1 SUPPLIER_NUMBER
FROM
  AP_INVOICE_PAYMENTS_ALL AIPA,
  AP_CHECKS_ALL ACA,
  AP_INVOICES_ALL AIA,
  AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
  POZ_SUPPLIERS_V PSV,
  GL_CODE_COMBINATIONS GCC
WHERE
      AIPA.CHECK_ID = ACA.CHECK_ID
  AND AIPA.INVOICE_ID = AIA.INVOICE_ID
  AND AIA.INVOICE_ID = AIDA.INVOICE_ID
  AND AIA.VENDOR_ID = PSV.VENDOR_ID
  AND AIDA.DIST_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID 
  AND AIPA.AMOUNT != 0
ORDER BY
  ACA.CHECK_DATE DESC,
  AIA.INVOICE_NUM