Changing the accounting period date for the current fiscal year

Overview

The end date for the current fiscal year is incorrect. This article will describe the method to correct this issue.

Solution

Changing the end date for the fiscal end-year can only be done through the back-end. To do this, please run the queries below:

Identify the incorrect end date

Run the query below to identify the current end date:

-- Getting fiscal year and the period needed to be updated
select * from FISCAL_YR where fiscal_yr = '{current_year}';
select * from PERIOD where fiscal_yr = '{current_year}' and period = 12;

Note the end date obtained by running these queries.

Run the queries below to check for transactions that happened after the period identified earlier:

-- Checking for any transactions after the period identified earlier
-- Note: date is in mm/dd/yyyy format
select * from bch_head where tran_date >= '{current_end_date}'
select * from bch_tran where ENTRY_DATE >= '{current_end_date}'
select * from TRANSACT where ENTRY_DATE >=  '{current_end_date}'

Replace {current_end_date} with the value noted earlier.

If the queries above did not return any results, execute the query in the Correct the end date step below otherwise if they returned results, you will need to Move entries to the correct period.

Move entries to the correct period

Run the query below to extract the transactions that will need to be updated:

SELECT *
INTO   temptable_batch
FROM   batch
WHERE  fiscal_yr > '{current_year}' 

Update the transactions by running the query below:

UPDATE batch
SET    fiscal_yr = '{current_year}',
       period = 12
WHERE  fiscal_yr > '{current_year}'
       AND created_date IN (SELECT created_date
                            FROM   temptable_batch) 

After updating the table, run the update_coahist_activity_from_transact stored procedure execute to recalculate the journal balance:

1. In Object Explorer, expand Databases.

2. Expand the database that you want, expand Programmability, and then expand Stored Procedures.

3. Right-click the update_coahist_activity_from_transact procedure and click Execute Stored Procedure.

4. In the Execute Procedure dialog box, choose to Pass Null Value for both parameters:
   

5. Click OK to execute the stored procedure.

Correct the end date

-- Update the actual fiscal year and period
BEGIN TRANSACTION

-- Note: end_date is in yyyy-mm-dd format
UPDATE fiscal_yr
SET    end_date = '{correct_end_date}'
WHERE  fiscal_yr = '{current_year}'

UPDATE period
SET    end_date = '{correct_end_date}'
WHERE  fiscal_yr = '{current_year}'
       AND period = 12

COMMIT TRANSACTION 

Replace the {correct_end_date} and {current_year} with the appropriate values. Please note that the END_DATE is written in the yyyy-mm-dd format (such as 2021-12-31).

Testing

After running the above query, the end period will be updated this is reflected properly on the Period screen. Navigate to File > Setup > Accounting > Periods to confirm:

If the issue persists after executing the Solution steps, contact support for further troubleshooting.