Drill from Gl general ledger to ledger

A) In R12, the link between GL and any subledger is via xla.
B) GL tables will no longer be storing any subledger specific information
Like invoice_id/date etc onto the gl_je_lines/gl_je_headers etc. Max
Information transfered between subledger to GL is the doc sequence number
(That also in specific cases only .)
C) Any reconciliation between XLA-GL is via gl_sl_link_id and
Gl_sl_link_table. These 2 columns together form a unique pair to join any row
Of GL to xla_ AE _lines.
Xla_ AE _lines.gl_sl_link_id = gl_import_references.gl_sl_link_id and
Xla_ AE _lines.gl_sl_link_table = gl_import_references.gl_sl_link_table
.
Now if the posting is in summary, the gl_sl_link_id is available in
Gl_import_references only. If its detailed mode transfer then its available
In both gl_import_references and gl_je_lines. In any case, if the data is
Coming via xla, then gl_import_references will always have these rows
Populated.
.
So all your reconciliation has to follow the following route:
.
1) gl_je_lines & gl_import_references
2) gl_import_references & xla_ AE _lines
3) xla_ AE _lines/xla_distribution_links & subledger distribution tables
Or
Xla_ AE _lines/xla_ AE _headers/xla_events & subledger events table.

 

Subledger distribution tables:

Xla_distribution_links ----

If you find a row with source_distribution_type as ap_inv_dist in the xla_distribution_links table then check the source_distribution_id_num_1 which wocould be
The invoice_distribution_id in the ap_invoice_distributions_all and form there
You can get the invoice_id.

Xla_distribution_links ----

If you find a source_distribution_type as ap_pmt_dist in the xla_distribution_links table then check
The source_distribution_id_num_1 which wocould be the payment_hist_dist_id in
Ap_payment_hist_dists and from there you can find out the invoice_payment_id and
Go to ap_invoice_payments_all and find the check_id.

For example

Select * from
Gl_je_headers gjh
Where gjh. je_header_id = 175961

Select * from
Gl_je_lines gjl
Where gjl. je_header_id = 175961

Select gir. gl_sl_link_id, gir. * from
Gl_import_references gir
Where gir. je_header_id = 175961

 

Select xal. AE _header_id, xal. * from
Xla_ AE _lines xal
Where xal. gl_sl_link_id = 508807

Select xdl. source_distribution_id_num_1, xdl. * from
Xla_distribution_links xdl
Where xdl. AE _header_id = 215002

Select Aida. invoice_id, Aida. * from
Ap_invoice_distributions_all Aida
Where aida. invoice_distribution_id = 292410

Select * from
Ap_invoices_all AIA
Where AIA. invoice_id = 39776