R12 - GL / XLA / FAH - How to link GL data to the subledger data or vice versa

vamsi kasina's picture
articles: 

gl_je_lines (je_header_id, je_line_num)                -> gl_import_references (je_header_id, je_line_num)

gl_import_references (gl_sl_link_table, gl_sl_link_id) -> xla_ae_lines (gl_sl_link_table, gl_sl_link_id)

xla_ae_lines (applicaiton_id, ae_header_id)            -> xla_ae_headers (application_id, ae_header_id)

xla_ae_headers (application_id, event_id)              -> xla_events (application_id, event_id)

xla_events (application_id, entity_id)                 -> xla.xla_transaction_entities (application_id, entity_id)


xla.xla_transaction_entities (source_id_int_1, etc) after filtering by application_id, entity_code and ledger_id     -> subledger's table(its key columns mentioned in xla_entity_id_mappings) for that ledger_id

For Ex:
xla.xla_transaction_entities (source_id_int_1) filtered by application_id 200, entity_code AP_INVOICES and ledger_id -> ap_invoices_all (invoice_id) for that set_of_books_id.
xla.xla_transaction_entities (source_id_int_1) filtered by application_id 200, entity_code AP_PAYMENTS and ledger_id -> ap_checks_all (check_id) for that set_of_books_id.

xla.xla_transaction_entities (source_id_int_1) filtered by application_id 222, entity_code TRANSACTIONS and ledger_id -> ra_customer_trx_all (customer_trx_id) for that set_of_books_id.


Note:
a) There is an index on xla.xla_transaction_entities on the following columns.
application_id
entity_code
ledger_id
nvl(source_id_int_1,-99)
nvl(source_id_int_2,-99)
nvl(source_id_int_3,-99)
nvl(source_id_int_4,-99)
nvl(source_id_char_1,' ')
...
nvl(source_id_char_4,' ')

b) Use application_id filter wherever it is possible, as above mentioned XLA tables are partitioned by that.

Comments

Very helpful, thanks a lot.

Very helpful information. Thanks.

accounting_error_code, tax_code, gl_transfer_error_code columns were there in ap_ae_lines_all in 11i, but these columns are not found in XLA_ae_lines table. Please help me to find these columns. I'm new to R12 and finance modules.

vamsi kasina's picture

Accounting errors can be found in xla_accounting_errors table by filtering on event_id.
Tax Code you may find in ZX tables. The link is in xla_distribution_links.
GL Transfer Error Code is not stored in normal XLA tables.
xla_ae_headers.gl_transfer_status_code just tells you about the status of the GL Transfer of that Journal.
The table xla_glt_, a dynamically generated table for each Journal Import have the error code related to GL Transfer.
Replace with xla_ae_headers.group_id to know the exact table name.

How does one drill down to find tax information off an invoice in the XLA tables?
I can find invoice tax from Zx_Rec_Nrec_dist but this might not be what when to the GL.
Thanks

vamsi kasina's picture

As said before the link is in xla_distribution_links.
xla_distribution_links.tax_line_ref_id is tax_line_id in ZX tables.