QUERY GL Opening and Closing Balance

on Thursday, August 12, 2010

OPENING BALANCE

SELECT     
      cc.concatenated_segments   "GL Account"
     ,cc.gl_account_type         "Account Type"
     ,nvl(sum(bal.begin_balance_dr - bal.begin_balance_cr),0)   "Begin Balance"
     ,bal.period_name            "Period Name"
FROM  gl_code_combinations_kfv cc
     ,gl_balances bal
WHERE cc.code_combination_id = bal.code_combination_id
AND   bal.period_name        = [Period Name]
AND   bal.set_of_books_id    = [Set of Book ID]
GROUP BY
      cc.concatenated_segments
     ,cc.gl_account_type
     ,bal.period_name
ORDER by
      cc.concatenated_segments


CLOSING BALANCE

SELECT
      cc.concatenated_segments   "GL Account"

     ,cc.gl_account_type         "Account Type"
     ,nvl(sum(bal.begin_balance_dr - bal.begin_balance_cr +
          bal.period_net_dr - bal.period_net_cr),0)  "Closing Balance"
     ,bal.period_name            "Period Name"
FROM  gl_code_combinations_kfv cc
     ,gl_balances bal
WHERE cc.code_combination_id = bal.code_combination_id
AND   bal.period_name        = [Period Name]
AND   bal.set_of_books_id    = [Set of Book ID]
GROUP BY
      cc.concatenated_segments

     ,cc.gl_account_type
     ,bal.period_name
ORDER BY
      cc.concatenated_segments

bal.period_name = [Period Name]  Here you should enter the period name you want to see. You can check the table GL_PERIOD_STATUSES to see which period you want. If you do not define a period the query will return a bunch of records without any sense.
bal.set_of_books_id =[Set of Book ID] Here you should enter your set of books id (each company has a set of books id), You can check table GL_SETS_OF_BOOKS to check which company you want to see. Otherwise the query will return all companies data mixed.

1 comment:

kashif said...

Thanks for posting opening and closing balance query.

Regards,

Kashif M
DW/BI Consultant - TransSys Solutions
www.mkashu.blogspot.com