SQL Queries Tables in Oracle Fusion Accounts Receivables

In Oracle Fusion Applications, Accounts Receivables (AR) involves several key tables that store various aspects of financial transactions, customer details, invoices, payments, and related information. Here’s a list of some important tables related to Accounts Receivables in Oracle Fusion:

Key Tables in Oracle Fusion Accounts Receivables

1. AR_INVOICE_HEADERS_ALL

   • Contains header information for invoices.
   • Key columns: INVOICE_ID, INVOICE_NUMBER, CUSTOMER_ID, STATUS, INVOICE_DATE, etc.

2. AR_INVOICE_LINES_ALL

   • Stores detailed line information for invoices.
   • Key columns: INVOICE_LINE_ID, INVOICE_ID, LINE_ID, LINE_AMOUNT, PRODUCT_ID, etc.

3. AR_CASH_RECEIPTS_ALL

   • Contains information about cash receipts.
   • Key columns: CASH_RECEIPT_ID, RECEIPT_NUMBER, CUSTOMER_ID, RECEIPT_DATE, AMOUNT_RECEIVED, etc.

4. AR_PAYMENT_SCHEDULES_ALL

   • Details payment schedules associated with invoices.
   • Key columns: PAYMENT_SCHEDULE_ID, INVOICE_ID, DUE_DATE, AMOUNT_DUE, etc.

5. AR_RECEIPT_APPLICATIONS_ALL

   • Manages the application of receipts to invoices.
   • Key columns: RECEIPT_APPLICATION_ID, RECEIPT_ID, INVOICE_ID, APPLIED_AMOUNT, etc.

6. AR_CUSTOMERS

   • Stores basic information about customers.
   • Key columns: CUSTOMER_ID, CUSTOMER_NAME, ACCOUNT_NUMBER, STATUS, etc.

7. AR_CUSTOMER_ACCOUNTS

   • Contains information about customer accounts.
   • Key columns: CUSTOMER_ACCOUNT_ID, CUSTOMER_ID, ACCOUNT_NUMBER, STATUS, etc.

8. AR_DISTRIBUTIONS_ALL

   • Includes details of the distribution of invoices and receipts.
   • Key columns: DISTRIBUTION_ID, INVOICE_ID, RECEIPT_ID, ACCOUNT_ID, AMOUNT, etc.

9. AR_ADJUSTMENTS_ALL

   • Manages adjustments made to invoices and receipts.
   • Key columns: ADJUSTMENT_ID, INVOICE_ID, RECEIPT_ID, ADJUSTMENT_AMOUNT, ADJUSTMENT_TYPE, etc.

10. AR_COLLECTIONS_ALL

    • Contains information about collections and collection activities.
    • Key columns: COLLECTION_ID, CUSTOMER_ID, COLLECTION_DATE, AMOUNT_COLLECTED, etc.

11. AR_TAX_LINES_ALL

    • Stores tax details associated with invoices.
    • Key columns: TAX_LINE_ID, INVOICE_ID, TAX_AMOUNT, TAX_CODE, etc.

12. AR_INVOICE_ACCOUNTS

    • Holds information on the accounting aspects of invoices.
    • Key columns: INVOICE_ACCOUNT_ID, INVOICE_ID, ACCOUNTING_DATE, ACCOUNTING_AMOUNT, etc.

Sample SQL Query

Here’s a sample SQL query that joins some of these tables to retrieve basic invoice and payment information:

SELECT
    i.INVOICE_ID,
    i.INVOICE_NUMBER,
    i.INVOICE_DATE,
    i.CUSTOMER_ID,
    c.CUSTOMER_NAME,
    l.LINE_ID,
    l.LINE_AMOUNT,
    r.RECEIPT_ID,
    r.AMOUNT_RECEIVED
FROM
    AR_INVOICE_HEADERS_ALL i
    JOIN AR_INVOICE_LINES_ALL l ON i.INVOICE_ID = l.INVOICE_ID
    LEFT JOIN AR_CASH_RECEIPTS_ALL r ON i.INVOICE_ID = r.INVOICE_ID
    JOIN AR_CUSTOMERS c ON i.CUSTOMER_ID = c.CUSTOMER_ID
WHERE
    i.INVOICE_DATE BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD') AND TO_DATE('2024-12-31', 'YYYY-MM-DD')
ORDER BY
    i.INVOICE_DATE DESC;

Notes

• The actual table names and structures might vary depending on your Oracle Fusion implementation and customizations. It’s important to consult your specific Fusion schema or documentation.
• Ensure you have the necessary permissions to access these tables and views.
• When querying large datasets, consider performance optimization techniques such as indexing and filtering.

Using these tables, you can manage and analyze various aspects of Accounts Receivables within Oracle Fusion, helping to streamline financial processes and improve reporting accuracy.