6 Core AR Invoice Tables in Oracle Fusion Cloud
While developing BIP reports or querying invoice data we need to be aware of the common AR invoice tables in oracle fusion cloud applications. many of these tables are in sync with AR invoice tables of oracle apps r12 EBS.
The above table describes what kind of receivables transactions are stored and in which table
Table of Contents
Common AR invoice tables in Oracle Fusion
The most common AR invoice tables in oracle fusion are
Here below are some of the important tables about AR Invoice.
RA_CUSTOMER_TRX_ALL
RA_CUSTOMER_TRX_LINES_ALL
RA_CUST_TRX_LINE_GL_DIST_ALL
RA_CUST_TRX_LINE_SALESREPS_ALL
AR_PAYMENT_SCHEDULES
AR_RECEIVABLE_APPLICATIONS
Basic SQL query for AR receivables invoice header , lines and distributions
,RA_CUST_TRX_LINE_GL_DIST_ALL radist
where ral.customer_trx_id = rac.customer_trx_id and
ral.CUSTOMER_TRX_LINE_ID=radist.CUSTOMER_TRX_LINE_ID and
ral.org_id = rac.org_id and
ral.line_type=’LINE’ and
rac.trx_number=’C100619′
RA_CUSTOMER_TRX_ALL
This is the main header table for receivables transactions invoice, debit memo, and credit memo header information. The important key field is ORG_ID and TRX_NUMBER which is used to link to lines table.
Other fields are Transaction date , Invoice Date, Customer, Customer Site, Payment Terms, Invoice Source, Invoice Transaction Type, Currency and other DFF fields.
other Important columns in the RA_CUSTOMER_TRX table include:
- CUSTOMER_TRX_ID column
- TRX_NUMBER column
- BILL_TO_CUSTOMER_ID column
- TRX_DATE column
RA_CUSTOMER_TRX_LINES_ALL
The RA_CUSTOMER_TRX_LINES table stores invoice, debit memo, and credit memo line level information. Each transaction line is stored as a unique record, based on the primary key customer_trx_line_id column. The customer_trx_id column is a foreign key to the RA_CUSTOMER_TRX table.
We can get the Invoice Line No, Item, Description, Qty, Unit Price, Accounted price. Here in this Table, We will get the separate Lines for Each TAX line. If any invoice has 1 Line and two taxes applied to this Line. Then we will get three lines for this Invoice. One for Line and Two for Taxes.
RA_CUST_TRX_LINE_SALESREPS_ALL
This tables helps to store the Sales representative information’s for the AR Invoice in Oracle Fusion. The RA_CUST_TRX_LINE_SALESREPS table stores sales credit assignments for invoice lines. Each assignment is stored as a unique record, based on the primary key cust_trx_line_salesrep_id. The sales_rep_id column identifies the salesperson receiving the credit for this transaction. The customer_trx_line_id column is a foreign key to the RA_CUSTOMER_TRX_LINES table.
RA_CUST_TRX_LINE_GL_DIST_ALL:-
This table store the AR Invoice accounting information’s for each line of the AR Invoice. Here will get all the Accounting Information’s.
AR_PAYMENT_SCHEDULES
The AR_PAYMENT_SCHEDULES table stores customer balance information at the transaction level. Each transaction balance is stored as a unique record, based on the primary key payment_schedule_id.
Receivables Applications
The Receivables tables that manage data for receipt and credit memo applications are:
- AR_RECEIVABLE_APPLICATIONS (For Receipts transactions)
- AR_CREDIT_MEMO_AMOUNTS
- AR_CASH_RECEIPTS
- AR_CASH_RECEIPT_HISTORY
- AR_MISC_CASH_DISTRIBUTIONS
Important columns are RECEIVABLE_APPLICATION_ID , AMOUNT_APPLIED , STATUS PAYMENT_SCHEDULE_ID, CODE_COMBINATION_ID, CASH_RECEIPT_ID, APPLIED_PAYMENT_SCHEDULE_ID and APPLIED_CUSTOMER_TRX_ID
For more /cloud receivables, tables refer to this doc
These were the ar invoice tables in oracle cloud receivables. Similarly we have for EBS
AR Invoice tables in Oracle Apps r12 EBS
The core AR receivables tables in oracle apps r12 business suite are as below for reference
- RA_CUSTOMER_TRX_ALL
- RA_CUSTOMER_TRX_LINES_ALL
- RA_CUST_TRX_LINE_GL_DIST_ALL
- AR_CASH_RECEIPTS_ALL
- RA_CUST_TRX_LINE_SALESREPS_ALL
- AR_RECEIVABLE_APPLICATIONS_ALL
- AR_PAYMENT_SCHEDULES_ALL
- AR_CASH_RECEIPT_HISTORY_ALL
FAQ on AR invoice tables in oracle fusion
In which tables invoices are stored?
RA_CUSTOMER_TRX_ALL
In which table stores AR documents
RA_CUSTOMER_TRX_LINES_ALL
Are EBS and fusion receivables same?
Yes almost. many tables are common between Fusion and Oracle EBS
Related posts
