Skip to main content

Analytics

Navigating from EBS Journal Lines to Originating Transactions – Part II

In our last post we discussed the linkages between Journal Entries and the source transactions in Accounts Payable, Accounts Receivable, and Fixed Assets.  This post continues the discussion with the linkages for the Purchasing, Project Accounting, and Cost Management applications.

As noted in the prior posting, since most organizations employ summary posting of Journal entries, the Subledger Accounting application groups source transactions before passing them to the General Ledger Application.  The following query expands on the query in the previous post to add the Journal Source and Journal Category from the associated Journal Header.

SELECT distinct xdl.application_id, fa.application_short_name, xdl.source_distribution_type,
xdl.accounting_line_code, xdl.event_class_code, xdl.event_type_code,
xdl.applied_to_entity_code, xdl.applied_to_distribution_type, gjh.je_category, gjh.je_source
FROM apps.xla_distribution_links xdl
LEFT OUTER JOIN apps.xla_ae_lines xal ON xal.ae_header_id = xdl.ae_header_id and xal.ae_line_num = xdl.ae_line_num
LEFT OUTER JOIN apps.gl_import_references gir ON gir.gl_sl_link_id = xal.gl_sl_link_id
LEFT OUTER JOIN apps.gl_je_headers gjh ON gjh.je_header_id = gir.je_header_id
LEFT OUTER JOIN apps.gl_je_lines gjl ON gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
LEFT OUTER JOIN applsys.fnd_application fa ON fa.application_id = xdl.application_id
ORDER BY xdl.application_id, xdl.source_distribution_type, xdl.accounting_line_code, xdl.event_class_code, xdl.event_type_code;

Oracle seeds dozens of accounting events and linkages with the E-Business Suite as will be shown from the results of this query, not all of which are necessarily used.  These can be investigated further in the Sub Ledger Accounting (Accounting Hub) documentation, but three facts are noteworthy:

  • The Source Distribution Type identifies the type of transaction , and the Source Distribution ID columns (Up to five numerics and/or up to five character columns) provide the columns used to identify the source transaction.
  • Typically, not all rows extracted by the above query will have Journal Sources and/or Categories. Primary focus should be on those accounting events that have an associated Journal Source/Category, since those are likely those actually used.
  • Journal Sources and Categories likely do not uniquely identify a distribution type. As a result, these cannot be used to drive identification of source transactions.

In addition, individual implementations may define additional linkages (e.g. loading GL data from a non-Oracle application).  These will normally have a distinct, custom application id, and can not be addressed here.

Purchasing

For Purchasing application linkages (Application id = 201), these are:

Purchasing Table 100

Unless you are using encumbrance accounting, there typically are not journals originating from the Purchasing application.  Receipt accruals are generated using the Costing application as noted below.

Project Accounting

For Project Accounting application linkages (Application id = 275), these are:

Project Accounting Table 100

Of these, perhaps the most significant is ‘R’, since it relates to recognition of Raw and Burdened project costs.  The Revenue related distribution types are typically used only by firms that perform projects on a fee basis, such as an engineering firm.

Cost Management

For Cost Management application linkages (Application id = 707), these are:

Cost Management Table 100

As can be seen above,  Cost management accounting linkages fall into three categories:

  • Accounting events that recognize changes in the value of inventory items, or changes to the accounting distribution of the inventory value (e.g. a movement from one cost center’s warehouse to another cost center’s warehouse).
  • Accounting events that recognize accrued liabilities based on purchase order receipts.
  • Accounting events that recognize cost accumulations during the manufacturing process (e.g. overhead absorption).

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Bruce Wroughton

Bruce Wroughton is a Business Intelligence Solutions Architect specializing in the design and development of Data Warehouses and Business Intelligence solutions, primarily based on Oracle’s Cloud applications.

More from this Author

Categories
Follow Us
TwitterLinkedinFacebookYoutubeInstagram