AP to GL Link using XLA Tables

The table is called GL_IMPORT_REFERENCES .

It is linked with GL_JE_BATCHES by JE_BATCH_ID and linked with GL_JE_HEADERS, GL_JE_LINES by JE_HEADER_ID

This table is linked with XLA related table by reference column.
REFERENCE_5 is ENTITY_ID in XLA table.
REFERENCE_6 is ACCOUNTING_EVENT_ID in XLA table.
REFERENCE_7 is AE_HEADER_ID in XLA table.
The most important column in GL_IMPORT_REFERENCES is GL_SL_LINK_ID, you also can find this column in table XLA_AE_LINES

Lets see how we can Drilldown from Account Payables Invoice details to GL Journal Detail.

select * from xla_entity_id_mappings

where entity_code like 'AP_INVOICES';

1. When we create invoices it will hit below given table in account Payables.

AP_INVOICES_ALL

AP_INVOICE_LINES_ALL

AP_INVOICE_DISTRIBUTIONS_ALL

e.g. :

Invoice Header:

select * from ap_invoices_all

where invoice_num='IN0097158'; -- Copy the value for Invoice_id for future rerfrence 16211228

Invoice Line : 

select * from ap_invoice_lines_all

where invoice_id=16211228;

Invoice Distributions :

select * from ap_invoice_distributions_all

where invoice_id=16211228;

SLA Tables :

select * from XLA.xla_transaction_entities

where source_id_int_1=16211228

and transaction_number='IN0097158'; -- From this table copy value for Entity id 580427218

--  

SELECT * FROM XLA_AE_HEADERS

WHERE ENTITY_ID=580427218;  --- From this table copy value Event id 582978337 and ae_header_id value 126276903

-- 

select * from xla_events

WHERE EVENT_ID=582978337;

-- 

select * from xla_ae_lines

WHERE AE_HEADER_ID=126276903;

--

SELECT *

from gl_import_references gir

where gir.gl_sl_link_id in

(select gl_sl_link_id from xla_ae_lines

WHERE AE_HEADER_ID=126276903); -- from this table you will get detail for je_header_id, je_line_num, je_batch_id

--

select * from gl_je_batches

where je_batch_id= 5709025;

-- 

select * from gl_je_headers

where je_header_id=6780748;

--

select * from gl_je_lines

where je_header_id=6780748

and je_line_num in (9619,6734);

--

  

select

gjh.name Journal_name,

gjb.name Batch_name,

gjl.description Line_desc,

gjc.je_category_name,

gjs.je_source_name,

xl.entered_dr,

xl.entered_cr,

xl.accounted_dr,

xl.accounted_cr

from gl_import_references gir,

gl_je_headers gjh,

gl_je_batches gjb,

gl_je_lines gjl,

xla_ae_lines XL,

gl_je_categories gjc,

gl_je_sources gjs

where gir.gl_sl_link_id in

(select gl_sl_link_id from xla_ae_lines

WHERE AE_HEADER_ID=126276903)

and gjh.je_header_id=gir.je_header_id

and gjl.je_line_num=gir.je_line_num

and gjl.je_header_id=gjh.je_header_id

and gjh.je_batch_id=gjb.je_batch_id

and gjh.actual_flag='A'

AND gjh.status='P'

and xl.gl_sl_link_id=gir.gl_sl_link_id

and gjh.je_category=gjc.je_category_name

and gjs.je_source_name=gjh.je_source    ;