Drill into subledgers BI Dashboard

In this article we will see how to drill from GL balances to Journal Details and into Sub-ledger transactions. We will also see how all these things fit together in an Operational Finance Dashboard
First section will cover the basics of drilling from GL to subledgers and the second section
will discuss the tools that can help you acheive this.
You can also go through some previous entries for background information

First Section
If you happen to speak to any user in Oracle Financials every body wants the ability to
see summary of gl balances. It doesn’t end here, the most important part is the ability to
drill into sub-ledgers. They love drilling, infact everybody needs the ability to drill from
higher level to the smallest details. This is the reason why tools like Discoverer are so popular.

The most sought after requirement is to see gl_balances at higher level and then ability to drill either into journal details or directly into subledger transactions.

Basics: GL-Balance is made of summary from the journal lines and journal lines is either one-to-one
mapping to subledger transactions or a summary of sl(sub-ledgers)

For e.g GL balance is nothing but balance amount for each code_combination_id

Code combination ID (..9390..) amount
123.345.9390.3930 4000.00
123.345.9390.3931 1000.00
123.345.9390.3932 2000.00
123.345.9390.3933 3000.00
Code combination ID (..9391..) amount
123.345.9391.3930 4000.00
123.345.9391.3931 1000.00
123.345.9391.3932 2000.00
123.345.9391.3933 3000.00
Code combination ID (..9392..) amount
123.345.9392.3930 4000.00
123.345.9392.3931 1000.00
123.345.9392.3932 2000.00
123.345.9392.3933 3000.00

The details in Journal lines would be something like below (this is not the exact technical depiction)

line id Code combination ID (..9390..) amount
1 123.345.9390.3930 2000.00
2 123.345.9390.3930 300.00
3 123.345.9390.3930 1400.00
4 123.345.9390.3930 300.00
4 123.345.9390.3931 …..

So as you see the sum of all the journal lines is updated to GL balances and this is known as GL-Posting.

Similarly all the balances are summarized by code_combination_id and other dimensions like period, balance_type etc
in gl balances.

So now the question is where are these Journal line transactions coming from?

They come from either direct manual journal entry in GL or from sub-ledgers like AR, AP, INV, PO, FA, WIP, BOM,
CST, OM etc

If you look into the Journal headers there is a journal source and journal category fields that indicate where these
transactions are coming from

If the Source =”Revenue” then it is coming from AR
source = “Payables” then it is coming from AP
source = “Purchasing” then it is coming from PO
source = “Manual” then the journals are manualy created in the journal entry screen of GL

Well, we took a lengthy tangent off our initial discussion on how do you drill?

Let us re-focus on our initial intent

There are various ways you can drill.

You can either drill from GL Balances to Sub-ledgers. This is possible but very resource intensive and for a
system where tons of transactions are posted to GL from Subledgers on a daily basis this is not the right approach.

Since a typical gl balance entry could be coming from multitude of sources you would have to build a materialized

view on top of each subledger transaction table and journal entries (to capture manual journals)

And this materialized view cannot provide real-time information from subledgers.

The other approach is to go down the same route i.e. from GL Balances to Journal lines and then into subledgers

Going from GL balance to journal lines is straight forward, as most of the parameters are available in the

gl_balance table like period, code_combination_id etc

Going from journal lines to subledgers is a little tricky but it is possible.

Each journal line has a journal source and category, so we can use this information to identify which subledger tables to drill into.
So if the source is “Revenue” then we know that we need to just query the AR tables.

Second Section
Here is a very powerful GL Dashboard that does exactly the same

Following is the list of things this dashboard can do

  • View Balance by any Set of Books and Chart of Account segments : Most companies who have implemented GL
    have multiple sets of books and multiple chart of account structure.When you have a different chart of account structure it means each structure has different number of segments(dimensions) and each segment could have different value set than the same segment in a different structure. with this GL Dashboard you can browse any set of books and any chart of accounts. You can dynamically change dimensions and the portlet will show you information for the new dimension in an instant.
  • Drill into Balance by Code combinations : From summary GL balances to detailed ccid balancesscreenshot for drill into balance by code_combination_id
  • Drill into Journal Details : From ccid balances to journal line detailsscreenshot for drill into journal details
  • Drill into Subledgers, e.g. sub-ledgers are AR, AP, PO, FA, INV, WIP, PA etc : This is the most powerful aspect
    of this dashboard. It can drill into any subledger. There is no setup required on the database side and noadditional objects. It uses the oracle apps views to drill into each different subledger.Some of the other features are as below
  • Drill into trending chart
  • View Revenue Balance by any of the account, company, cost center, project or product dimension
  • View Expenses by any of the account, company, cost-center, project or product dimension
  • View Assets by any of the account, company, cost center, project or product or country or region dimension
  • View Liability Balance by any of the account, company, cost center, project or product dimension
  • View Budget and Actual Balance by any of the account, company, cost center, project or product dimension
  • View PTD, QTD and YTD balance for any period
  • Define ad-hoc drills into Vendor, Items, customers or any other information you desire.

This dashboard is pure plug and play. Just download it and you are ready to go. Before you download the dashboard you need to download the main InfoCaptor setup file and install it.

InfoCaptor is a very flexible dashboard designer and viewer. You can download a trial version on your PC
and start creating dashboards. The website has some pre-built dashboards for GL , Discoverer, Sysadmin and DBA.
You can create your own dashboards and share it with others.

Enjoy and have a wonderful day!

Leave a Comment