AP Invoice Cash Requirements Report (Oracle Fusion SQL)

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

AP Invoice Cash Requirements Report Displays invoice-level supplier payment data, including check run details, payment document information, gross and discounted amounts, payment currency, and associated bank account details.

#sqlquery

SELECT
  -- SQL4Fusion (An Orbit Analytics Project)
  -- This query provides detailed payment information
  HAOU.NAME ORGANIZATION_NAME,
  AIA.INVOICE_NUM INVOICE_NUMBER,
  AIA.INVOICE_DATE,
  AIA.INVOICE_CURRENCY_CODE INVOICE_CURRENCY,
  AIA.INVOICE_AMOUNT,
  AIA.DESCRIPTION INVOICE_DESCRIPTION,
  PSV.VENDOR_NAME SUPPLIER_NAME,
  ACA.CHECK_NUMBER,
  ACA.CHECKRUN_NAME,
  ACA.CHECK_DATE,
  ACA.AMOUNT CHECK_AMOUNT,
  ACA.BANK_ACCOUNT_NAME,
  NVL (AIPA.AMOUNT, 0) + NVL (AIPA.DISCOUNT_TAKEN, 0) GROSS_AMOUNT,
  NVL (AIPA.DISCOUNT_TAKEN, 0) DISCOUNT_AMOUNT_TAKEN,
  NVL (AIPA.AMOUNT, 0) PAYMENT_AMOUNT,
  AIPA.PAYMENT_NUM PAYMENT_NUMBER,
  DECODE (ACA.VOID_DATE, '', 'No', 'Yes') IS_VOIDED
FROM
  AP_INVOICES_ALL AIA,
  POZ_SUPPLIERS_V PSV,
  AP_INVOICE_PAYMENTS_ALL AIPA,
  AP_CHECKS_ALL ACA,
  CE_BANK_ACCT_USES_ALL CBAUA,
  CE_BANK_ACCOUNTS CBA,
  AP_SYSTEM_PARAMETERS_ALL ASPA,
  CE_PAYMENT_DOCUMENTS CPD,
  HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
  1 = 1
  AND AIA.VENDOR_ID = PSV.VENDOR_ID
  AND AIA.INVOICE_ID = AIPA.INVOICE_ID
  AND AIPA.CHECK_ID = ACA.CHECK_ID
  AND ACA.CE_BANK_ACCT_USE_ID = CBAUA.BANK_ACCT_USE_ID(+)
  AND CBAUA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID(+)
  AND CBAUA.ORG_ID = ASPA.ORG_ID
  AND ACA.PAYMENT_DOCUMENT_ID = CPD.PAYMENT_DOCUMENT_ID(+)
  AND AIA.ORG_ID = HAOU.ORGANIZATION_ID
  AND SIGN (AIPA.AMOUNT) != (-1 * SIGN (AIA.INVOICE_AMOUNT))