Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 8 April 2013

Step by Step Oracle Financial Year Closing

Steps:
1. Prepare the Test environment with latest data.
2. Start the Closing with Fixed Assets Branch by Branch.
a). If do not found any error to close fixed assets of any branch.
b). If found error to close fixed assets of any Branch.
3. Proceed to close this year account.
4. Proceed to Carry Forward Balance for Next year.

1. Export original schema ORAFIN and use import to re-create the ITGFIN schema (Test Env) to update with current data. For step by step follow the link: Restore or Refresh Schema using Import/Export with Toad
Expected Time: 90 Minutes
Note: Open the restored ITGFIN (Test Environment) schema and before starting the actual process you must put ‘Test environment’ to avoid any confusion between original and test environment from application side. For that modify the table “Application” and “Company profile” due to recreation of schema from original environment there is original environment.
2. Open the application to perform the actual closing work start with “Fixed Assets”
a). If do not found any error to close Fixed assets of any branch:
Expected Time: 30 minutes
Open the application and go to Fixed Assets –> Management Function –> Year Closing
Put the Branch# and the Year for closing and click on “Close Year for Branch”. Repeat this process for entire branch one by one.
Note: last date depr will be the last date of the closing year which will be automatically appear when you put the year for closing.
3. Then proceed to close this year from the application open GL –> Management Function –> Year Closing
Provide the closing year and click on the RUN and wait for some times approximately 45 minute (it depends on the size of data)
It calls a database procedure called GL_CLOSE which will work on the account type Sales, Cost of sales, Revenues, Expense only which are commonly used to find the company profit or loss at the end of the year. At the end, this procedure will build voucher type 40 (Year Closing) in GL_DAILY table.
Thus after completion of year closing you can check it with this query
Select * from Gl_Daily
where D_VOUTYPE=40 and D_VOUREMARK = 'Closing year 2011' order by D_BRANCH, D_DEPT;
4. Finally after closing the current year, the process that follows is carry forward balances to then next year, open the GL –> Management Function –> Carry forward Balance

Provide the carry forward year and click on the Run (carry forward year should be same as that of closing year).
This calls the procedure BAL_ACCT_CYCLE which will insert new record for each carry forward account balance in the next year.
To check the Account is successfully done and carry is forward, use the below query if they have value in ABAL_DB_BAL0,  ABAL_CR_BAL0 column of next year then the carry forward is successful.
SELECT ABAL_DB_BAL0,  ABAL_CR_BAL0 FROM GL_ACCTBAL
WHERE ABAL_YYYY = 2012;
b). If found error to close the fixed assets of any branch:
        Check the assets calculation if there is any calculation not done yet inform the finance department to compute that month depreciation.
        Check any unbalance assets calculations (due to fractional part) try to balance them.
The below query will results any unbalance assets either not computed or due to fractional part differences.
Expected Time: 60 minutes
    SELECT * FROM ASSETS_ALL
WHERE AST_YEAR = :DUM_YEAR
AND AST_ASST  > 0 AND AST_START+AST_ANUUAL != AST_ASST
AND AST_DEPT NOT IN (15,16) AND AST_PERC != 0
AND TO_CHAR(AST_DATE,'YYYYMMDD') != TO_CHAR(:DUM_YEAR)||'1231'
AND (TO_CHAR(AST_UPDT,'YYYYMMDD') < TO_CHAR(:DUM_YEAR)||'1231'
OR AST_UPDT IS NULL);
After finding the month whose depreciation is not calculated open the software start calculating depreciation from pending month one by one. Here in the below example month July calculation has been depicted.
Note: Due date should be the last date of the month to whom depreciation is computing.
Thus depreciation not calculated issue is fixed again run the same above query, now you are having only unbalance assets calculations due to fractional part issue. To fix this issue run the following update query:
UPDATE ASSETS_ALL
   SET AST_START=AST_ASST- AST_ANUUAL
   WHERE AST_YEAR = :DUM_YEAR
   AND AST_ASST > 0 AND AST_START+AST_ANUUAL != AST_ASST
   AND AST_DEPT NOT IN (15,16) AND AST_PERC != 0
   AND TO_CHAR(AST_DATE,'YYYYMMDD')  != TO_CHAR(:DUM_YEAR)||'1231'
   AND (TO_CHAR(AST_UPDT,'YYYYMMDD') < TO_CHAR(:DUM_YEAR)||'1231'
   OR  AST_UPDT IS NULL);   
The logic behind this update is to deduct AST_START = AST_ASST – AST_ANNUAL
So that AST_START+AST_ANUUAL == AST_ASST
For example, if we accumulate for the range: 01/06/2011 to 31/07/2011 then EXT_CR_VAL will have the balance up to 01/06/2011 (starting) and EXT_DB_VAL and EXT_CR_VAL will have the balance for the period between 01/06/2011 to 31/07/2011. Check the details with below query: Select * from glextmf order by EXT_date;
Once the assets issue is clear you can proceed to close this year step already described above.
Caution: It is preferred or recommended to do this process on the Test Environment first and support Finance department with the output reports like Trial balance to confirm before doing actual closing on live environment.

1 comments:

  1. QUANTUM BINARY SIGNALS

    Get professional trading signals delivered to your mobile phone daily.

    Start following our signals right now and earn up to 270% per day.

    ReplyDelete