Drill Down JV - AP

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

Please Subscribe my blog Qamar Zahoor, YouTube Channel YouTube, Join the Facebook group

Facebook Group and do follow on Twitter Twitter to get knowledge of Oracle EBS, Database, Ecommerce, Amazon, Ebay and Digital Marketing. Keep learning.