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.