AR Applied Receipt Details

This query extracts applied receipt details from Oracle Fusion Receivables. It combines customer, invoice, receipt, batch, receipt accounting date, applied amount, discounts, and invoice batch source details for receipt application analysis and reconciliation. Oracle’s Applied Receipts Register is specifically intended to review receipt applications by customer and receipt batch, which aligns closely with this query’s business purpose.
categories  Finance Account Receivables (AR)
back Back to Vault
xls Download Sample Report for AR Applied Receipt Details
SQL Download SQL Query for AR Applied Receipt Details

CSV SQL Query for AR Applied Receipt Details

Copy 
SELECT
    HCA.ACCOUNT_NUMBER            AS Customer_Account_Number,
    HP.PARTY_NAME                 AS Customer_Name,
    ABA.NAME                      AS Batch_Name,
    TO_CHAR(ACRA.DEPOSIT_DATE, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN')
                                   AS Deposit_Date,
    RCTA.TRX_NUMBER               AS Applied_Invoice_Number,
    TO_CHAR(RCTA.TRX_DATE, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN')
                                   AS Invoice_Date,
    APSA.STATUS                   AS Invoice_Status,
    (
        SELECT
            DHA.CUSTOMER_PO_NUMBER
        FROM
            DOO_HEADERS_ALL       DHA
        WHERE
                DHA.ORDER_NUMBER  = RCTA.INTERFACE_HEADER_ATTRIBUTE1
            AND DHA.SUBMITTED_FLAG = 'Y'
    )                             AS Customer_PO_Number,
    ACRA.RECEIPT_NUMBER           AS Receipt_Number,
    TO_CHAR(ACRA.RECEIPT_DATE, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN')
                                   AS Receipt_Date,
    TO_CHAR(ACRHA.GL_DATE, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN')
                                   AS Receipt_GL_Date,
    ACRA.AMOUNT                   AS Receipt_Amount,
    ACRA.CURRENCY_CODE            AS Transaction_Currency,
    ARAA.AMOUNT_APPLIED           AS Applied_Amount,
    ARAA.AMOUNT_APPLIED           AS Allocated_Receipt_Amount,
    ARAA.EARNED_DISCOUNT_TAKEN    AS Earned_Discount_Taken,
    ARAA.AMOUNT_APPLIED           AS Total_Amount_In_Functional_Currency,
    RBSA.NAME                     AS Invoice_Batch_Source
FROM
    HZ_PARTIES                    HP,
    HZ_CUST_ACCOUNTS              HCA,
    AR_PAYMENT_SCHEDULES_ALL      APSA,
    RA_CUSTOMER_TRX_ALL           RCTA,
    AR_RECEIVABLE_APPLICATIONS_ALL ARAA,
    AR_CASH_RECEIPTS_ALL          ACRA,
    AR_CASH_RECEIPT_HISTORY_ALL   ACRHA,
    AR_BATCHES_ALL                ABA,
    RA_BATCH_SOURCES_ALL          RBSA
WHERE
        HP.PARTY_ID (+)           = HCA.PARTY_ID
    AND HCA.CUST_ACCOUNT_ID (+)   = RCTA.BILL_TO_CUSTOMER_ID
    AND RCTA.CUSTOMER_TRX_ID      = APSA.CUSTOMER_TRX_ID (+)
    AND RCTA.CUSTOMER_TRX_ID      = ARAA.APPLIED_CUSTOMER_TRX_ID
    AND ARAA.CASH_RECEIPT_ID (+)  = ACRA.CASH_RECEIPT_ID
    AND ACRA.CASH_RECEIPT_ID      = ACRHA.CASH_RECEIPT_ID (+)
    AND ACRHA.BATCH_ID            = ABA.BATCH_ID (+)
    AND RCTA.BATCH_SOURCE_SEQ_ID  = RBSA.BATCH_SOURCE_SEQ_ID (+)
    AND ACRHA.CURRENT_RECORD_FLAG (+) = 'Y'

AR Applied Receipt Details in BI Connector

Try BI Connector  back
back Back to Vault

Can’t find the report you need?

Want to tweak one to fit your business logic?

Contact Sales
Banner