Financial Intelligence (GL Balances)

Categories: Dashboard,ERP
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 from FND_SEGMENT_ATTRIBUTE_VALUES fav1, FND_SEGMENT_ATTRIBUTE_VALUES fav2, FND_SEGMENT_ATTRIBUTE_VALUES fav3, 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 ,bal.period_name
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) image008.png ii. Expense by Department image010.png iii. Expense by Account image012.png iv. Revenue by Account image014.png
« « Interviewing Techniques for gathering Business Requirements                        EPB: How to Validate the OGL EPF Actuals Balance » »

Comments are closed.