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 PaymentThese 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_alland 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_allar_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
