EPF , EPB and GL Validations and Troubleshooting

Categories: Other

Here are few scripts that I use to validate actual balances. In the below script, you can add or remove dimensions based on the actuals report you have created in EPB.

Let’s say you have a actuals report that has line, cost center and period then you can include those dimensions in the below query (in between select and sum and also include them in the group by clause). Also restrict the below query by cal_period_id
select cctr,line_desc,
sum(xtd_balance_e),
sum(xtd_balance_f),
sum(qtd_balance_e),
sum(qtd_balance_f),
sum(ytd_balance_e),
sum(ytd_balance_f),
sum(ptd_debit_balance_e),
sum(ptd_credit_balance_e),
sum(ytd_debit_balance_e),
sum(ytd_credit_balance_e)
from
(
SELECT
b.ledger_name ledger,
c.cal_period_name cal_per,
d.company_cost_center_org_name cctr,
d.description cctr_desc,
e.financial_elem_name fin_elem,
g.line_item_name line,
g.description line_desc,
j.user_dim2_name,
j.description dim2_desc,
xtd_balance_e,
xtd_balance_f,
qtd_balance_e,
qtd_balance_f,
ytd_balance_e,
ytd_balance_f,
ptd_debit_balance_e,
ptd_credit_balance_e,
ytd_debit_balance_e,
ytd_credit_balance_e
FROM fem_balances a,
fem_ledgers_tl b,
fem_cal_periods_tl c,
fem_cctr_orgs_tl d,
fem_fin_elems_tl e,
fem_ln_items_tl g,
fem_user_dim2_tl j
WHERE a.ledger_id = b.ledger_id
AND a.cal_period_id = c.cal_period_id
AND a.company_cost_center_org_id = d.company_cost_center_org_id
AND a.financial_elem_id = e.financial_elem_id
AND a.line_item_id = g.line_item_id
AND a.user_dim2_id = j.user_dim2_id
and a.ledger_id in and c.cal_period_id =

)
group by line_desc

Similarly you can construct a query against GL Balances as below

select
cctr_org, line_item
,sum(XTD_BALANCE_E)
,sum(XTD_BALANCE_E) XTD_BALANCE_F
,sum(YTD_BALANCE_E)
,sum(YTD_BALANCE_E) YTD_BALANCE_F
,sum(PTD_DEBIT_BALANCE_E)
,sum(PTD_CREDIT_BALANCE_E)
,sum(YTD_DEBIT_BALANCE_E)
,sum(YTD_CREDIT_BALANCE_E)
from
(
select
(
case when gcc.account_type in (‘R’,’E’)
then bal.Period_Net_Dr – bal.Period_Net_Cr
else ((bal.period_net_dr + bal.begin_balance_dr) – (bal.period_net_cr + bal.begin_balance_cr))
end
)
XTD_BALANCE_E
, ((bal.period_net_dr + bal.begin_balance_dr) – (bal.period_net_cr + bal.begin_balance_cr)) YTD_BALANCE_E
,bal.period_net_dr PTD_DEBIT_BALANCE_E
,bal.period_net_cr PTD_CREDIT_BALANCE_E
,bal.period_net_dr + bal.begin_balance_dr YTD_DEBIT_BALANCE_E
,bal.period_net_cr + bal.begin_balance_cr YTD_CREDIT_BALANCE_E
, (
case when gcc.account_type = ‘R’ then ‘455’
when gcc.account_type = ‘E’ then ‘457’
else ‘100’
end
) FINANCIAL_ELEM_DISPLAY_CODE
,gcc.segment3 CCTR_ORG
,gcc.segment1 NATURAL_ACCOUNT
,gcc.segment5 CHANNEL
,gcc.segment1 LINE_ITEM
,gcc.segment7 PROJECT
,gcc.segment6 CUSTOMER
,gcc.segment2 region
,gcc.segment4 legal_entity
from gl_balances bal
, gl_sets_of_books sob
, gl_code_combinations gcc
, gl_periods gp
where bal.actual_flag=’A’
and bal.set_of_books_id in < list of ids ie. value of set_of_books_id from gl_sets_of_books>
and bal.period_name=gp.period_name
and bal.currency_code=’USD’
and bal.set_of_books_id = sob.set_of_books_id
and bal.code_combination_id = gcc.code_combination_id
and gp.period_set_name =
and gp.period_name=
)
where
(xtd_balance_e<>0
or ytd_balance_e<>0
or  ptd_debit_balance_e <> 0
or ptd_credit_balance_e <> 0
or ytd_debit_balance_e <> 0
or ytd_credit_balance_e <> 0
)
group by
cctr_org,line_item

The angular brackets <> need to be replaced with real values applicable for your situation.

Also, change the segment mapping, in the above query segment3 is cctr_org but in your case it could be totally different segment.

Query the below table

select * from fnd_id_flex_segments where id_flex_code=’GL#’ order by id_flex_num and it would give you the mapping that is used in your chart of accounts structure.

Happy Troubleshooting 😉

« « Oracle Inventory (INV) ABC classification                        Drill into subledgers BI Dashboard » »

Comments are closed.