Among the frequent requests when building a Financial Data Warehouse based on Oracle EBS is the desire to drill from a General Ledger period balance through the related Journal Entry lines to the underlying transaction in the source application through the Subledger Accounting linkages. Complicating this challenge is the fact that some of the EBS applications consolidate a variety of transactions into a single table that is interfaced to Subledger Accounting. This post will provide a summary of these linkages for the Accounts Payable, Accounts Receivable, and Fixed Assets applications. For Accounts Receivable, this will include resolution of the consolidations identified by the Sub Ledger Accounting record to the detail EBS transactions. Future posts will expand this discussion to other application areas. (Refer to this blog for linkages related to Purchasing, Project Accounting, and Cost Management applications.)
First, a quick overview of the basic linkages between Journal Entries and Subledger Accounting. Because most organizations employ summary posting of Journal entries, the Subledger Accounting application groups source transactions before passing them to the General Ledger Application. This linkage requires the following SQL joins to obtain the linkage information from the XLA DISTRIBUTION LINKS table, which contains the first level of linkages to the source transactions.
Beginning with the Journal Entry Line (GL JE LINES – GJL):
LEFT OUTER JOIN GL IMPORT REFERENCES gir ON gjl.je header id = gir.je header id
AND gjl.je line num = gir.je line num
LEFT OUTER JOIN XLA AE LINES xal ON xal.gl sl link id = gir.gl sl link id
AND xal.gl sl link table = gir.gl sl link table
LEFT OUTER JOIN XLA DISTRIBUTION LINKS xdl ON xdl.ae header id = xal.ae header id
AND xdl.ae line num = xal.ae line num
Linking to the source transactions requires evaluation of several columns in the XLA DISTRIBUTION LINKS table driven by the value of the SOURCE DISTRIBUTION TYPE column, sometimes in conjunction with the APPLICATION ID.
Accounts Payable
For Accounts Payable application linkages (Application id = 200), these are
Accounts Receivable
For Accounts Receivable application linkages (Application id = 222), these are
*SOURCE_DISTRIBUTION_ID_NUM1 is not equal to TAX_LINE_REF_ID
The AR Distributions All table further identifies the actual source transactions
Fixed Assets
For Fixed Assets application linkages (Application id = 140), these are
Follow this link for Part II of this blog post.
