Financial Intelligence (GL Balances)

GL Balances in Oracle General Ledger is the summary of your entire ERP and hence is the first level of aggregation available directly within the applications. There is some really good intelligent information stored in this table. We will see how to extract some of the vital information as below.

· What is expense by Account?
· What is expense by Cost Center?
· What is revenue by Product? (Provided you have product as one of your account dimensions)
· What is revenue by Account and Country?
· What is expense and Revenue by Project? (Project needs to be an account dimension)
· If you set up a dashboard with a list of account dimensions and account types as shown below then you get the ability to dynamically change the Account dimensions and get ad-hoc analysis of Expenses, Revenue, Liability, Assets and other Account types.

Also, Financial module of DBI uses similar logic to determine Profit and loss metrics but in DBI the information is static and refreshed at a certain frequency. If you need real-time analysis then a dynamic dashboard using the below mentioned logic can serve the purpose.
In order to proceed, we need to first extract some information about the accounting flexfield structure and its segments.
1. First Select the Account FlexField meta data, i.e. find out which is the Balancing segment, Cost Center, and Account segment.

Select name
, gsob.set_of_books_id
, gsob.chart_of_accounts_id
, fav2.application_column_name Company
, fav3.application_column_name department
, fav1.application_column_name account_segment
gl_sets_of_books gsob
where 1=1
and fav1.id_flex_code=’GL#’
and fav1.id_flex_num=gsob.chart_of_accounts_id
and fav1.attribute_value=’Y’
and fav1.segment_attribute_type=’GL_ACCOUNT’
and fav2.id_flex_code=’GL#’
and fav2.id_flex_num=gsob.chart_of_accounts_id
and fav2.attribute_value=’Y’
and fav2.segment_attribute_type=’GL_BALANCING’
and fav3.id_flex_code=’GL#’
and fav3.id_flex_num=gsob.chart_of_accounts_id
and fav3.attribute_value=’Y’
and fav3.segment_attribute_type=’FA_COST_CTR’

GL Account Structure (Balancing, Cost Center and account segment)2.
What are my company wide expenses for period=Dec-03 ?

In the below query, we are using the company segment and querying for “Expenses�

SELECT value_desc.description “Company”
,cck.SEGMENT1 “Company_code” /*segment_name*/
,bal.currency_code Curr
,bal.period_name Period
, SUM(bal.begin_balance_dr +
bal.period_net_dr) debit_bal,
SUM(bal.begin_balance_cr +
bal.period_net_cr) credit_bal,
SUM( bal.period_net_dr) Debit_activity,
SUM(bal.period_net_cr) Credit_activity
FROM gl_balances bal,
gl_code_combinations_kfv cck,
select flex_value, flex_value_id,description
from fnd_flex_values_vl
where flex_value_set_id= 1002470/*flex_value_set_id*/
) value_desc
WHERE bal.code_combination_id = cck.code_combination_id
AND bal.actual_flag = ‘A’
AND bal.set_of_books_id = 1/*gsob.set_of_books_id*/
AND bal.period_name = ‘Dec-03’ /*period_name*/
AND bal.template_id IS NULL
AND NVL(bal.translated_flag, ‘R’) = ‘R’
AND bal.currency_code = ‘USD’/*distinctcurrency_code*/
and cck.SEGMENT1 like ‘%’/*flex_value*/
and cck.gl_account_type = ‘E’/*glkp.lookup_code*/
and cck.SEGMENT1 = value_desc.flex_value
group by value_desc.description,cck.SEGMENT1
, bal.currency_code

Note : You can get the value set id by querying the fnd_id_flex_segments table.
Expense by Company

Here are some visual examples of the above scenarios

i. Revenue by Product (product is one of the Account Dimensions)


ii. Expense by Department


iii. Expense by Account


iv. Revenue by Account


Leave a Comment