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

vamsi kasina's picture
Submitted by vamsi kasina on Sat, 2009-06-06 14:16
AttachmentSize
Image icon entity_id_mappings_invoices.JPG93.31 KB
Image icon entity_id_mappings_payments.JPG97.09 KB
articles: 
Applications

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

Submitted by davholla on Wed, 2011-03-02 05:05.

Very helpful, thanks a lot.

»

Submitted by xhyrruz on Fri, 2011-03-25 04:03.

Very helpful information. Thanks.

»

Submitted by parimalbakshi on Tue, 2011-12-06 04:09.

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

Submitted by vamsi kasina on Fri, 2011-12-23 11:56.

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.

»

Submitted by knkdad4ever on Fri, 2012-06-01 14:47.

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

Submitted by vamsi kasina on Fri, 2012-07-13 03:42.

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

»