AR Billing Payment History Details (Oracle Fusion SQL)

Enterprise Resource Planning (ERP) Oracle Account Receivables (AR-Fusion-SQL's)
1

This AR Billing Payment History Details query generates the Billing Payment History Report to view payment and receipt information.

#sqlquery

SELECT
 -- SQL4Fusion (An Orbit Analytics Project) 
 -- Generate the Billing Payment History Report to view payment and receipt information.
  HAOU.NAME ORGANIZATION_NAME,
  APSA.TRX_NUMBER INVOICE_NUMBER,
  ACRA.RECEIPT_NUMBER,
  ACRA.CURRENCY_CODE RECEIPT_CURRENCY,
  NVL (
    ARAA.AMOUNT_APPLIED_FROM,
    ARAA.AMOUNT_APPLIED
  ) RECEIPT_AMOUNT,
  ARAA.AMOUNT_APPLIED,
  ARAA.APPLY_DATE APPLIED_DATE,
  -- APS.TRX_NUMBER INVOICE_NUMBER,
  ACRA.DOC_SEQUENCE_VALUE DOCUMENT_NUMBER,
 ACRA.REVERSAL_CATEGORY,
  ACRA.REVERSAL_REASON_CODE,
  ACRA.STATUS CASH_RECEIPT_STATUS,
  ARAA.STATUS RECEIVABLE_APPLICATION_STATUS,
  ARAA.RECEIVABLE_APPLICATION_ID,
  ARAA.APPLIED_PAYMENT_SCHEDULE_ID,
  ARAA.PAYMENT_SCHEDULE_ID,
  HAOU.ORGANIZATION_ID
FROM
  AR_CASH_RECEIPTS_ALL ACRA,
  AR_RECEIVABLE_APPLICATIONS_ALL ARAA,
  AR_PAYMENT_SCHEDULES_ALL APSA,
  HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
  1 = 1
  AND ACRA.CASH_RECEIPT_ID = ARAA.CASH_RECEIPT_ID
  AND HAOU.ORGANIZATION_ID = ACRA.ORG_ID
  AND NVL (ARAA.CONFIRMED_FLAG, 'Y') = 'Y'
  AND NVL (ACRA.CONFIRMED_FLAG, 'Y') = 'Y'
  AND ACRA.CASH_RECEIPT_ID(+) = APSA.CASH_RECEIPT_ID