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:
- 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.
- Query XLA_AE_HEADERS table based on the entity_id. this table will give you the accounting date.
- 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
Want to give back to the society? (Do you have something which you think can be used by folks working on Oracle world wide?) Then Click here, share your email id and a brief description of the topic. And keep your photo and the content ready.
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports. 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