AR: Basiv(technical foundation)

This post is more on basic table structure of Oracle Account Receivable.

1.Table that holds AR Invoice data
The following tables can give most of the invoice information.

• RA_CUSTOMER_TRX_ALL stores invoice header information. RA_CUSTOMER_TRX_LINES_ALL stores information about invoice, debit memo, credit memo, bills receivable, and commitment lines.
• The AR_PAYMENT_SCHEDULES_ALL table stores all transactions except adjustments and miscellaneous cash receipts.
• RA_CUST_TRX_LINE_GL_DIST_ALL stores accounting distribution records for all transaction lines except bills receivable.The RA_CUST_TRX_LINE_GL_DIST_ALL table stores the accounting records for revenue, unearned revenue, and unbilled receivables for each invoice or credit memo line.
• RA_CUSTOMERS - Customer information
• RA_CUST_TRX_TYPES_ALL - Customer Transaction Type
• AR_PAYMENT_SCHEDULES_ALL
• RA_CUSTOMER_TRX_LINES_ALL - Transaction Line information
• MTL_SYSTEM_ITEMS - Base table for item

The ER Diagram for a customer Transaction can be easily understood as:

2.Customer Payment
These are the main tables which holds Customer Payment information

• AR_CASH_RECEIPTS_ALL stores one record for each receipt entry.
• AR_CASH_RECEIPT_HISTORY_ALL stores all of the activity that is contained for the life cycle of a receipt.
• Each row represents one step., Possible statuses are Approved, Cleared,Confirmed, Remitted, and Reversed.
• AR_MISC_CASH_DISTRIBUTIONS_ALL stores all accounting entries for miscellaneous cash applications.
• AR_DISTRIBUTIONS_ALL stores the accounting distributions for cash receipts, miscellaneous receipts, adjustments,credit memo applications, cash receipt applications, and bills receivable transactions.
• AR_RECEIVABLE_APPLICATIONS_ALL stores all accounting entries for cash and credit memo applications.
• Each row includes the amount applied, status, and accounting flexfield information.

The ER Diagram for a customer Payment can be easily understood as:

3. Accounting Link between the tables

• For Invoice:

ra_customers (This is for capturing customers information)
ra_customer_trx_all,
ra_cust_trx_types_all,
ar_payment_schedules_all,
ra_customer_trx_lines_all
and joins are customer_trx_id for ar_payment_schedules_all & ra_customer_trx_all & ra_customer_trx_lines_all .
ra_cust_trx_types_all.type IN ('INV')

• For the Receipts:

ar_receivable_applications_all,
ra_customer_trx_all,
ra_customer_trx_lines_all,
ar_cash_receipts_all
ar_receivable_applications_all.application_type = 'CASH',
ar_receivable_applications_all.cash_receipt_id = ar_cash_receipts_all.cash_receipt_id,
ar_receivable_applications_all.applied_customer_trx_id = ra_customer_trx_all.customer_trx_id