EPB: How to Validate the OGL EPF Actuals Balance

Categories: Dashboard,ERP
Once the EPF OGL link is setup, all the COA dimensions are mapped, hierarchy rules and dimension members loaded, the next step is to bring in the actual balances from Oracle General Ledger. Once the actual balances is brought into EPF the next step is to validate this data and the below steps demonstrates this validation process. 1. query the global value set id as below
select * from fem_global_vs_combos_tl;
global_value_set_id.png epf_balance_by_account.png 2. Determine which period you need to validate the balances for e.g 'JAN-05' NOTE: Verify in your GL_Periods table the right format for the period definition For e.g. is it JAN-05 or Jan-05 or Jan-2005 etc   3. Query the calendar period id from EPF dimension
select p.cal_period_name, to_char(b.cal_period_id,'99999999999999999999999999999999') as "Cal Period ID", c.calendar_name, count(*) from fem_balances b, fem_cal_periods_tl p, fem_calendars_tl c where b.cal_period_id = p.cal_period_id and p.calendar_id = c.calendar_id group by b.cal_period_id, c.calendar_name, p.cal_period_name;
4. Use the cal_period_id from step 3 and use it in the below query..
select f.extended_account_type, n.natural_account_display_code, sum(b.xtd_balance_e), sum(b.xtd_balance_f), count(*) from fem_balances b, fem_intg_ogl_ccid_map f, fem_nat_accts_b n where f.company_cost_center_org_id = b.company_cost_center_org_id and f.natural_account_id = b.natural_account_id and f.line_item_id = b.line_item_id and f.product_id = b.product_id and f.channel_id = b.channel_id and f.project_id = b.project_id and f.customer_id = b.customer_id and f.entity_id = b.entity_id and f.intercompany_id = b.intercompany_id and f.user_dim1_id = b.user_dim1_id and f.user_dim2_id = b.user_dim2_id and f.user_dim3_id = b.user_dim3_id and f.user_dim4_id = b.user_dim4_id and f.user_dim5_id = b.user_dim5_id and f.user_dim6_id = b.user_dim6_id and f.user_dim7_id = b.user_dim7_id and f.user_dim8_id = b.user_dim8_id and f.user_dim9_id = b.user_dim9_id and f.user_dim10_id = b.user_dim10_id and f.user_dim2_id = b.user_dim2_id and f.task_id = b.task_id and b.natural_account_id = n.natural_account_id and cal_period_id = 24533950000000000000061002010000 and financial_elem_id in (100,455,457) group by n.natural_account_display_code, f.extended_account_type;
5. Run the below query against GL_balances
SELECT cck.SEGMENT1 Account/*segment_name*/ /*application_column_name*/ ,bal.currency_code Curr ,bal.period_name Period , glkp.description Acc_type , decode( cck.gl_account_type, 'A', sum( nvl(bal.begin_balance_dr,0)+nvl(bal.period_net_dr,0)-nvl(bal.begin_balance_cr,0)-nvl(bal.period_net_cr,0) ), 'L', sum( nvl(bal.begin_balance_cr,0)+nvl(bal.period_net_cr,0)-nvl(bal.begin_balance_dr,0)-nvl(bal.period_net_dr,0) ), 'O', sum( nvl(bal.begin_balance_cr,0)+nvl(bal.period_net_cr,0)-nvl(bal.begin_balance_dr,0)-nvl(bal.period_net_dr,0) ), 'R', sum( nvl(bal.period_net_cr,0)-nvl(bal.period_net_dr,0) ), sum( nvl(bal.period_net_dr,0)-nvl(bal.period_net_cr,0) ) ) balance FROM gl_balances bal, gl_code_combinations_kfv cck , gl_lookups glkp 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 = 'ADJ-01' /*period_name*/ AND bal.currency_code = 'USD'/*distinctcurrency_code*/ and cck.SEGMENT1 like '%'/*flex_value*/ and glkp.lookup_code=cck.gl_account_type and glkp.lookup_type='ACCOUNT TYPE' group by cck.SEGMENT1 /*application_column_name*/ , bal.currency_code ,bal.period_name , glkp.description ,cck.gl_account_type
gl_balance_by_account.png Using this Dashboard tool, I can easily create cascading parameters and have two portlets placed adjacent to each other, one for GL balance and the other for EPF balances and I get the visual presentation as below. epb_parameters.png gl_and_epf_balance_by_account.png I can do similar comparison for any period by selecting the value from the period drop down list. I can also define drills so that I can directly jump from EPF balance to journal details and hence subledger transactions. I will cover the drilling to subledger and journal details in a later post.
« « Financial Intelligence (GL Balances)                        Oracle Accounts Payables (AP) – Key Tables 1 » »

Comments are closed.