What do I need to do to change the financial year e.g. July June to a January December to year

Where financial data already exists in a database, the reporting requirements of a business may change and this requires changes being made to the table GL_Config - UPDATED for 7.00.157 and 7.00.175

Step-by-step guide

Advanced SQL skills are required

Advanced SQL programming skills are required to complete this task. Make sure this is tested out thoroughly on a copy of the database first.

Every financial year requires 12 periods.  To change the financial structure in an existing database from a July/June reporting year to a January/December year the start periods of at least 6 of the periods will need to be changed, depending what period is the current posting period.

In this example, the current posting period is November 2016 and the new year end is to be 31 December 2016.  We need to spread the last 7 months of the 2016/2017 year over the December 2016 month by editing the table GL_Config.  Please ensure you test this out in a copy of the database before implementing in the live database.  Follow these steps for databases 7.01.00 and higher.

  1.  

    • edit the table GL_Config editing the record where field YearStartingDate = 2016-07-01 

  1.  

    • update the fields in this record by changing the relevant PeriodStart dates e.g. PeriodStart7 = 04/12/2016, PeriodStart8 = 9/12/2016, PeriodStart9 = 13/12/2016, PeriodStart10 = 17/12/2016, PeriodStart11= 23/12/2016, PeriodStart12 = 27/12/2016 (these dates are just suggestions - discuss what works for the client)

    • edit table GL_Config editing the record where YearStartingDate = 2017-07-01

    • change the fields in this record by changing the relevant PeriodStart dates e.g. PeriodStart1 = 01/01/2017, PeriodStart2 = 01/02/2017

    • then run usp_Jiwa_GL_Repost and usp_Jiwa_GL_UpdateParents to update the movement table with the new reporting periods

UPDATE for 7.00.157 and 7.00.175 databases  

7.00.157 databases need to be upgraded to 7.00.175 SR 4 with the attached patch applied.  7.00.175 databases need Service Release 4 installed and the attached patch applied.  Service Release 5 for 7.00.175 will include this fix.  

Steps to follow for 7.00.175 database:

  • complete all steps above - DO NOT RUN usp_Jiwa_GL_Repost and usp_Jiwa_GL_UpdateParents

  • apply Service Release 4 as required

  • apply the patch from JiwaApplication.RAR

  • run the script ChangeYearFix.sql

  • run usp_Jiwa_GL_Repost and usp_Jiwa_GL_UpdateParents to update the movement table with the new reporting periods

Please log a Service Request or call the office on 02-9409 0700 if you have any further questions.

 

Related articles