R12分类帐：从AP到GL

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 would 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 would be the PAYMENT_HIST_DIST_ID in the 
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

 

AR与子模块的关系：

  参考：metalink文章 ID 879072.1 

1. you need to know the distribution id of your payment.
2. use following sql to get sub ledger id.
select * from xla_distribution_links t where t.source_distribution_type = 'AP_PMT_DIST' and source_distribution_id_num_1 = 'Distribution Id from 1';
3. use following sql to get sub ledger info.
select * from xla_ae_headers where ae_header_id = 'sub ledger id from 2';
select * from xla_ae_lines where ae_header_id = 'sub ledger id from 2';