ERPstuff - Oracle EBS Community

I have two basic questions regarding gl_balance tables :-
1. Is everything which goes to table "gl_balance" must come via "gl_je_lines" ?
Or
there is some extra insertion of records directly to table "gl_balance" which is not through "gl_je_lines"

I am asking this question because when I tried to write a query by totaling
"gl_je_lines" and comparing with "gl_balance"
(in VISION demo database for criteria i.e
set_of_books_id =1 , period_set_name='Accounting' and actual_flag='A' ) .

They were not same records .


2. what is problem in the query mentioned below ?
why these queries do not bring same number of records/total debit/credit balance ?
Results are 79135 records (in balance query)
Results are 28398 records (in gl_je_lines query)

gl_je_lines query
=================
select l.code_combination_id , h.period_name,
sum(nvl (l.accounted_dr, 0)) tot_dr ,sum(nvl (l.accounted_cr, 0)) tot_cr
from GL_JE_LINES l,GL_JE_HEADERS h, gl_periods p
where h.set_of_books_id =1
and h.STATUS='P'
and h.JE_HEADER_ID =l.JE_HEADER_ID
and h.set_of_books_id=l.set_of_books_id
and h.period_name=p.period_name
and p.period_set_name='Accounting'
group by l.code_combination_id , h.period_name

balance query
=============
select bal.CODE_COMBINATION_ID,bal.period_name,
PERIOD_NET_DR tot_dr ,PERIOD_NET_CR tot_cr
from gl_balances bal ,gl_periods p ,GL_CODE_COMBINATIONS cc
where set_of_books_id =1
and bal.period_name=p.period_name
and p.period_set_name='Accounting'
and actual_flag='A'
and (PERIOD_NET_DR <>0 or PERIOD_NET_CR <>0)
and bal.CODE_COMBINATION_ID=cc.CODE_COMBINATION_ID

I am at a very basic learning stage of Oracle financial (GL Module) .
So, I shall be very thankful if anybody can explain me clearly.

Hi,

The reason why amounts are looking different is that you are not looking at the currency aspect.

While looking at GL Balances you have used period_net_dr/cr which will store balances in transaction currency, however while using gl_je_lines you have used rightly accounted_dr/cr.

Now 2 ways of getting this right
1. get currency as well in your query and modify the query from gl_je_lines to entered_dr/cr
2. Use PERIOD_NET_DR_BEQ and PERIOD_NET_CR_BEQ in your gl_Balances query

Hope this helps.

Regards
Dinesh