Oracle EBS R12 Receivables Module – Practical SQL Queries

 

Overview:

An Oracle ERP finance module is a core component of an ERP system that manages and automates a company’s financial functions and performance.

The Oracle E-Business Suite (EBS) R12 Receivables (AR) module is  organizes and automates invoicing and payment processing. This can help you improve cash management while increasing your finance team’s productivity.

With the Accounts Receivable system, you can streamline the day-to-day functions of your entire Accounts Receivable department. You can simplify and accelerate the process of applying receipts, and have up-to-date information that improves communication among your billing and credit and collections departments.

Oracle Receivables integrates with modules such as:

·        Oracle General Ledger (GL)

·        Oracle Order Management (OM)

·        Oracle Cash Management (CE)

Below are useful SQL queries commonly used in Oracle ERP R12 Receivables (AR).

Note:

Invoice : A bill sent to the customer.

Receipt : A payment received from the customer. 

Apply Receipt:  The action of linking the customer payment to a specific invoice.

 1) Customer Transaction type

Purpose: This query retrieves Receivables transaction types such as Invoice, Debit Memo, and Credit Memo.

SELECT
    CTT.NAME           AS TRANSACTION_TYPE,
    CTT.TYPE,
    CTT.DESCRIPTION
FROM
    RA_CUST_TRX_TYPES_ALL CTT
ORDER BY
    CTT.NAME;

 

2) Receipt Method Detail

Purpose: This query retrieves receipt methods configured in Oracle ERP Receivables.

SELECT

    RECEIPT_METHOD_ID,

    NAME AS RECEIPT_METHOD_NAME,

    RECEIPT_CLASS_ID,

    START_DATE,

    END_DATE

FROM

    AR_RECEIPT_METHODS

ORDER BY   NAME;

 
3)  Customer Invoice Details

Purpose: This query retrieves customer invoice details along with customer name, invoice amount, invoice date, and transaction status.

SELECT
    RCTA.TRX_NUMBER              AS INVOICE_NUMBER,
    RCTA.TRX_DATE                AS INVOICE_DATE,
    HCA.ACCOUNT_NAME             AS CUSTOMER_NAME,
    HCA.ACCOUNT_NUMBER           AS CUSTOMER_NUMBER,
    RCTA.CUSTOMER_TRX_ID,
    RCTA.INVOICE_CURRENCY_CODE   AS CURRENCY,
    RCTA.COMPLETE_FLAG           AS STATUS,
    APS.AMOUNT_DUE_ORIGINAL      AS ORIGINAL_AMOUNT,
    APS.AMOUNT_DUE_REMAINING     AS BALANCE_AMOUNT
FROM
    RA_CUSTOMER_TRX_ALL RCTA,
    HZ_CUST_ACCOUNTS HCA,
    AR_PAYMENT_SCHEDULES_ALL APS
WHERE
    RCTA.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
    AND RCTA.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
ORDER BY
    RCTA.TRX_DATE DESC;

 

4) Customer Outstanding Balance Query

Purpose: This query shows unpaid customer balances and remaining outstanding amounts.

SELECT
    HCA.ACCOUNT_NUMBER,
    HCA.ACCOUNT_NAME,
    APS.TRX_NUMBER,
    APS.DUE_DATE,
    APS.AMOUNT_DUE_ORIGINAL,
    APS.AMOUNT_DUE_REMAINING
FROM
    AR_PAYMENT_SCHEDULES_ALL APS,
    HZ_CUST_ACCOUNTS HCA
WHERE
    APS.CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
    AND APS.AMOUNT_DUE_REMAINING > 0
ORDER BY
    APS.DUE_DATE;

 
5) Customer Receipt Details

Purpose: This query retrieves customer receipt information including receipt number, amount,receipt status and currency code.

SELECT
    ACR.RECEIPT_NUMBER,
    ACR.RECEIPT_DATE,
    HCA.ACCOUNT_NAME      AS CUSTOMER_NAME,
    ACR.AMOUNT,
    ACR.STATUS,
    ACR.CURRENCY_CODE
FROM
    AR_CASH_RECEIPTS_ALL ACR,
    HZ_CUST_ACCOUNTS HCA
WHERE
    ACR.PAY_FROM_CUSTOMER = HCA.CUST_ACCOUNT_ID
ORDER BY
    ACR.RECEIPT_DATE DESC;

 
6) Receipt Invoice details

Purpose: This query shows which receipts are applied against customer invoices.

Note: Applied receipt means Payment allocated to an invoice.

SELECT
    ACR.RECEIPT_NUMBER,
    RCTA.TRX_NUMBER        AS INVOICE_NUMBER,
    ARA.AMOUNT_APPLIED,
    ARA.APPLY_DATE
FROM
    AR_RECEIVABLE_APPLICATIONS_ALL ARA,
    AR_CASH_RECEIPTS_ALL ACR,
    RA_CUSTOMER_TRX_ALL RCTA
WHERE
    ARA.CASH_RECEIPT_ID = ACR.CASH_RECEIPT_ID
    AND ARA.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
    AND ARA.STATUS = 'APP';

 

7) AR Invoice Accounting distribution

Purpose: This query retrieves accounting distribution details for customer invoices.

SELECT
    RCTA.TRX_NUMBER,
    RCTLGD.ACCOUNT_CLASS,
    GCC.CONCATENATED_SEGMENTS AS ACCOUNT,
    RCTLGD.AMOUNT
FROM
    RA_CUSTOMER_TRX_ALL RCTA,
    RA_CUST_TRX_LINE_GL_DIST_ALL RCTLGD,
    GL_CODE_COMBINATIONS_KFV GCC
WHERE
    RCTA.CUSTOMER_TRX_ID = RCTLGD.CUSTOMER_TRX_ID
    AND RCTLGD.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
ORDER BY
    RCTA.TRX_NUMBER;

 

Conclusion

Oracle E-Business Suite R12 Receivables (AR) provides powerful functionality to manage day-to-day Accounts Receivable operations such as invoicing, receipt processing, customer balance tracking, and accounting.
The practical SQL queries shared in this article help users efficiently monitor customer transactions, analyze outstanding balances, track receipts, and review accounting information.