Accounting Reconciliation In Oracle Fusion Finance

If you ever work on any fusion finance project, it is a matter of time that you’ll be asked to provide accounting related data (most probably for reconciliation). For that you need to combine the data from various sources say AP Invoice, AR Invoice etc. To write such query, one needs to be familiar with the structure of the database tables. here is what I learnt:

1. first step in accounting reconciliation is to query the XLA_TRANSACTION_ENTITIES table. Don’t forget to filter the records on the basis of ENTITY_CODE. SOURCE_ID_INT_1 is the column which will have the primary key of the entity ( e.g. customer_trx_id in case of AR Invoice and invoice_id in case of AP invoice) and get the event_id.
2.  Query XLA_AE_HEADERS table based on the entity_id. this table will give you the accounting date.
3. Query XLA_AE_LINES table based on the ae_header_id which you got from XLA_AE_HEADERS table. This table will provide you the amount related information.

A sample query for AP invoice can be:

select inv.invoice_num, aeh.accounting_date acct_dt , ael.*
from ap_invoices_all inv,
xla_transaction_entities ent,
xla_ae_headers aeh,
xla_ae_lines ael
WHERE 1=1
and inv.invoice_id = ent.source_id_int_1
and ent.ENTITY_CODE = ‘AP_INVOICES’
and aeh.entity_id = ent.entity_id
and aeh.ae_header_id = ael.ae_header_id

By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...

A sample query for AR invoice can be:

select inv.trx_number invoice_num, aeh.accounting_date acct_dt , ael.*
from ra_customer_trx_all inv,
xla_transaction_entities ent,
xla_ae_headers aeh,
xla_ae_lines ael
WHERE 1=1
and inv.CUSTOMER_TRX_ID = ent.source_id_int_1
and ent.ENTITY_CODE = ‘TRANSACTIONS’
and aeh.entity_id = ent.entity_id
and aeh.ae_header_id = ael.ae_header_id

Related Posts
Fusion: Account Receivable Invoice Query
Invoices Table in Oracle Fusion – AP_INVOICES_ALL

Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.

Reference:
support.oracle.com