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.
, fav2.application_column_name Company
, fav3.application_column_name department
, fav1.application_column_name account_segment
from FND_SEGMENT_ATTRIBUTE_VALUES fav1,
In the below query, we are using the company segment and querying for â€œExpensesâ€?
,cck.SEGMENT1 “Company_code” /*segment_name*/
, SUM(bal.begin_balance_dr +
SUM( bal.period_net_dr) Debit_activity,
FROM gl_balances bal,
select flex_value, flex_value_id,description
where flex_value_set_id= 1002470/*flex_value_set_id*/
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
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