AP to GL Drilldown

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

xla_entity_id_mappings : This is most important table for SLA as it includes all the join condition between xla tables and base table. It will gives you column name mapping between xla table with base table. You just need to pass entity code and you get all details.
e.g. Entity code for Account Payable Invoice is 'AP_INVOICES'. Please refer below given query.

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 gir.je_batch_id=gir.je_batch_id

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    ;