Oracle Fusion GL Balances Table Data Visualization

 

We often get requirements to build a report of PTD, QTD and YTD balances.

It makes our work easier if we know how the data rsides in GL_BALANCES table.

     We know that there is no primary key in that table.

Let us see how we can analyze the data in that table and build our report easily.

 

Ledger Currency: USD 

Data in GL_BALANCES  Table
-> For 1 period, to get all Activity in Func Currency there are  2 options             1    Example: select PERIOD_NET_DR from gl_balances where CURRENCY_CODE='USD'       and translated_flag=null  and period_name='Dec-18' 2    Example: select sum(PERIOD_NET_DR_BEQ) from gl_balances  where period_name='Dec-18'               -> For 1 period, to get only Activity done in Func Currency               Example: select PERIOD_NET_DR_BEQ from gl_balances where   CURRENCY_CODE='USD' and translated_flag=null and period_name='Dec-18'                  -> For 1 period, to get only Activity done in other currency                Example: select PERIOD_NET_DR from gl_balances where translated_flag='R'  and period_name='Dec-18'             ->Same rules for begin balances

Note:- In the above example the code combination and ledger is same for all rows