This is a quick note on how to find the AR Transaction Types and reference accounts information in Oracle Fusion or Oracle Cloud ERP.
In Fusion and Cloud ERP, we have to use the following two tables to get the Code Combination Ids:
- RA_CUST_TRX_TYPES_ALL
- AR_REF_ACCOUNTS_ALL
RA_CUST_TRX_TYPES_ALL
The table stores information about each transaction type used for invoices, bills receivable, and credit memos. the table has a TYPE column that has the following values
- INV for Invoice
- CM for Credit Memo
- DM for Debit Memo
The CUST_TRX_TYPE_SEQ_ID column can be used to join with RA_CUSTOMER_TRX_ALL table to get Transaction Type for an AR Transaction.
AR_REF_ACCOUNTS_ALL
This table stores the reference account information that is used as part of Auto Accounting to determine the default accounts. The table can have various entities like Transaction Types, Sales Persons etc. Keep a note of the following two columns:
- SOURCE_REF_TABLE column is used to identify the Source Table information of the record
- SOURCE_REF_ACCOUNT_ID column is used to identify the Id of the record that exists in the Source Table
Use the below SQL to get the Code Combination Ids:
[sql]
SELECT rctta.name
,araa.rev_ccid
,araa.rec_ccid
FROM ar_ref_accounts_all araa
,ra_cust_trx_types_all rctta
WHERE araa.source_ref_table = ‘RA_CUST_TRX_TYPES_ALL’
AND araa.source_ref_account_id = rctta.cust_trx_type_seq_id;
[/sql]
You can further refine the query by adding Ledger_id, Bu_id (business unit id) conditions in the SQL. The AR_REF_ACCOUNTS table has LEDGER_ID and BU_ID Columns.
Once you have the Code combination Ids, join the code_combination_id with GL_CODE_COMBINATIONS table to get the account details.
Hope this post helped. If you have questions, please free to post the questions in the comments section.