AP Payment Invoice Register (Oracle Fusion SQL)

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

This AP Payment Invoice Register query provides a comprehensive view of invoice payments and check details, including payment status, approval, bank info, supplier details, and accounting data for auditing and reconciliation.

#sqlquery

SELECT
  --SQL4Fusion (An Orbit Analytics Project) 
  --This query provides a comprehensive view of invoice payments and check details
  HAOU.NAME ORGANIZATION_NAME,
  AC.CHECK_NUMBER PAYMENT_NUMBER,
  AC.CHECK_DATE PAYMENT_DATE,
  AC.CHECKRUN_NAME,
  AC.AMOUNT CHECK_AMOUNT,
  AC.CHECK_VOUCHER_NUM CHECK_VOUCHER_NUMBER,
  AC.CLEARED_AMOUNT,
  AC.CLEARED_DATE,
  AC.VOID_DATE,
  AC.FUTURE_PAY_DUE_DATE,
  AC.POSITIVE_PAY_STATUS_CODE,
  AC.STOPPED_DATE,
  AC.RELEASED_DATE,
  AIA.INVOICE_NUM INVOICE_NUMBER,
  AIA.INVOICE_DATE,
  AIA.SOURCE INVOICE_SOURCE,
  AIA.INVOICE_AMOUNT,
  AIA.APPROVAL_READY_FLAG,
  AIA.APPROVAL_STATUS,
  AIA.APPROVED_AMOUNT,
  AIA.AMOUNT_APPLICABLE_TO_DISCOUNT,
  AIA.PAYMENT_STATUS_FLAG,
  AIA.FREIGHT_AMOUNT,
  AIA.GOODS_RECEIVED_DATE,
  AIA.INVOICE_RECEIVED_DATE,
  AIA.DESCRIPTION INVOICE_DESCRIPTION,
  AIP.ACCOUNTING_DATE,
  AIP.AMOUNT PAYMENT_AMOUNT,
  AIP.PERIOD_NAME,
  AIP.POSTED_FLAG,
  AIP.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUMBER,
  AIP.BANK_NUM BANK_NUMBER,
  AIP.DISCOUNT_LOST,
  AIP.DISCOUNT_TAKEN,
  AIP.INVOICE_BASE_AMOUNT,
  AIP.PAYMENT_BASE_AMOUNT,
  AIP.ASSETS_ADDITION_FLAG,
  AIP.REVERSAL_FLAG,
  AIA.ATTRIBUTE_CATEGORY,
  AC.VENDOR_NAME SUPPLIER_NAME,
  AC.BANK_ACCOUNT_NAME,
  AC.ADDRESS_LINE1,
  AC.ADDRESS_LINE2,
  AC.ADDRESS_LINE3,
  AC.ADDRESS_LINE4,
  AC.CITY,
  AC.COUNTY,
  AC.STATE,
  AC.COUNTRY,
  AC.ZIP,
  AIA.INVOICE_CURRENCY_CODE INVOICE_CURRENCY,
  AIA.PAYMENT_CURRENCY_CODE PAYMENT_CURRENCY,
  AIA.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE,
  AIA.PAY_GROUP_LOOKUP_CODE PAYMENT_GROUP,
  AIP.INVOICE_PAYMENT_TYPE,
  AC.VENDOR_SITE_CODE SUPPLIER_SITE,
  AC.PAYMENT_METHOD_LOOKUP_CODE PAYMENT_METHOD,
  AIP.REVERSAL_INV_PMT_ID REVERSAL_INVOICE_PAYMENT_ID,
  AIP.SET_OF_BOOKS_ID SOB,
  AC.VENDOR_ID VENDOR_ID,
  AIA.INVOICE_ID INVOICE_ID,
  AC.CHECK_ID CHECK_ID,
  HAOU.ORGANIZATION_ID,  
  AIP.INVOICE_PAYMENT_ID
FROM
  AP_INVOICE_PAYMENTS_ALL AIP,
  AP_CHECKS_ALL AC,
  AP_INVOICES_ALL AIA,
  HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
  1 = 1
  AND AIP.CHECK_ID = AC.CHECK_ID(+)
  AND AIP.INVOICE_ID(+) = AIA.INVOICE_ID
  AND AIA.ORG_ID = HAOU.ORGANIZATION_ID