Why did I see "Period definition update skipped because the year end has already been rolled" during rollover / What does the stored procedure CHECK_END_OF_YEAR_ROLLED do?
Starting in 2016 releases, Exo business added a check for whether the end of year had already been rolled as part of year end rollover. This check is designed to prevent problems with the PERIOD_DEFN table and the PERIOD_STATUS table from customers accidentally running the end of year process repeatedly.
Since PERIOD_DEFN is shared for all Ledgers, if end of year is performed separately for different periods or just accidentally rerun we need to make sure the PERIOD_DEFN doesn’t go awry.
This is governed by a call to the stored procedure:
CHECK_END_OF_YEAR_ROLLED
The check boils down to:
If STOP_DATE in PERIOD_STATUS is > PERIOD_DEFN START_DATE then don’t update periods and report
”Period definition update skipped because the year end has already been rolled” to the UI.
A commented version of the basic procedure is below:
CREATE PROCEDURE dbo.CHECK_END_OF_YEAR_ROLLED
AS
BEGIN
/*From EXO DBUpdate ~2016
Process map by WillH, MYOB Support 2025-01-15
Logical walkthrough:
0. Declare variables
1. Set @Result=0
2. Check LEDGER_PERIODS and opens cursor
3. Fetches first record from cursor
4. START_DATE from PERIDOS_DEFN
--Note, this is done during the cursor, logically it can (and maybe should) be run before the cursor is opened.
5. Checks each Ledger from PERIOD_STATUS, specifically checking the PERIOD_SEQNO that corresponds to the start of the financial year (for any Financial Year).
6. Checks each PERIOD_STATUS record with PERIOD_SEQNO recorded in LEDGER_PERIODS
6. If the highest PERIOD_STATUS.STOPDATE < PERIODS_DEFN.START_DATE, Set @Result=1 (prevent periods being rolled.)
7. Fetch next from cursor
8. Close and deallocate cursor
9. If the above cursor failed, let the year end rollover happen. (Report @Result=0)
10. Return @Result
End result: if PERIOD_STATUS.STOPDATE for the first period of the fiancnial year was ever before than PERIODS_DEFN.START_DATE for the fiancnial year, then report @Result=1, else report @Result=0.
How Exo uses this:
A 1 indicates "Already rolled, DO NOT ROLL PERIOD DEFINITION and show warning to user",
A 0 indicates "let the rollover happen"
Note, logical construction here is that PERIODS_DEFN will not be changed if _any_ ledger has a stopdate earlier than the startdate of Periods_Defn
Supporting informantion:
LedgerPeriods map as follows:
1=D (Debtor's ledger)
2=C (Creditor's ledger)
3=S (Stock ledger)
4=G (General Ledger)
For calculation purposes, any SEQNOs other than 1 2 or 3 are assumed to be a General Ledger check. If customising the LEDGER_PERIODS table be aware of this. (Please do not cusotmise this table, it's a system control).
*/
DECLARE @LEDGER_SEQNO INT
DECLARE @LEDGER_PERIOD INT
DECLARE @LEDGER_STOPDATE DATETIME
DECLARE @YEAR_STARTDATE DATETIME
DECLARE @RESULT INT
SET @RESULT = 0
DECLARE LedgerPeriods CURSOR FOR (SELECT SEQNO, PERIOD_SEQNO FROM LEDGER_PERIODS)
OPEN LedgerPeriods
BEGIN TRY
FETCH NEXT FROM LedgerPeriods INTO @LEDGER_SEQNO, @LEDGER_PERIOD
SELECT @YEAR_STARTDATE = STARTDATE FROM PERIODS_DEFN WHERE SEQNO = 1
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@LEDGER_SEQNO = 1)
SELECT @LEDGER_STOPDATE = MAX(STOPDATE) FROM PERIOD_STATUS WHERE LEDGER = 'D' AND PERIOD_SEQNO = @LEDGER_PERIOD
ELSE IF (@LEDGER_SEQNO = 2)
SELECT @LEDGER_STOPDATE = MAX(STOPDATE) FROM PERIOD_STATUS WHERE LEDGER = 'C' AND PERIOD_SEQNO = @LEDGER_PERIOD
ELSE IF (@LEDGER_SEQNO = 3)
SELECT @LEDGER_STOPDATE = MAX(STOPDATE) FROM PERIOD_STATUS WHERE LEDGER = 'S' AND PERIOD_SEQNO = @LEDGER_PERIOD
ELSE
SELECT @LEDGER_STOPDATE = MAX(STOPDATE) FROM PERIOD_STATUS WHERE LEDGER = 'G' AND PERIOD_SEQNO = @LEDGER_PERIOD
IF @LEDGER_STOPDATE < @YEAR_STARTDATE
SET @RESULT = 1
FETCH NEXT FROM LedgerPeriods INTO @LEDGER_SEQNO, @LEDGER_PERIOD
END
END TRY
BEGIN CATCH
CLOSE LedgerPeriods
DEALLOCATE LedgerPeriods
RETURN 0
END CATCH
CLOSE LedgerPeriods
DEALLOCATE LedgerPeriods
RETURN @RESULT
END