Need help with writing the three scripts

GL Open Bal :

GL_ACCOUNT
GL_ACCOUNT_TYPE
OPEN_BALANCE
PERIOD_NAME

(For table “GL Open Bal” Please provide a record count and total for field “Open_Balance”)

GL Close Bal :

GL_ACCOUNT
GL_ACCOUNT_TYPE
CLOSING_BALANCE
PERIOD_NAME

(For table “GL Close Bal” Please provide a record count and total for field “Closing_Balance”)

Solution:

For GL Open Bal:

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 = <'>
AND bal.set_of_books_id = <your set_of_books_id>
GROUP BY cc.concatenated_segments,
cc.gl_account_type,
bal.period_name
ORDER by cc.concatenated_segments

For GL Close Bal:

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 = <'>
AND bal.set_of_books_id = <your set_of_books_id>
GROUP BY cc.concatenated_segments,
cc.gl_account_type,
bal.period_name
ORDER by cc.concatenated_segments

What should you put in here:

AND bal.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.

AND bal.set_of_books_id = <your set_of_books_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.

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips