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

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

Select rac.CT_reference,ral.* from rac, RA_CUSTOMER_TRX_LINES_ALL ral,
,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

PO Requisitions tables

We will be happy to hear your thoughts

      Leave a reply

      Cloud Technologies Blog
      Logo
      Enable registration in settings - general
      Compare items
      • Total (0)
      Compare
      0
      Shopping cart