Number sequence consumption monitoring

Once a company has been live for a while and they are doing Business As Usual, they often forget about maintaining one critical area. The system is using Number Sequences as identifiers, for which we have fixed, allocated range of values. Typical example is a general financial journal with a format like JNL18-###### with values between 000001 and 999999. Number Sequence consumption monitoring is essential!

If the sequence reaches the maximum value, you are no longer able to create new financial journals. Sometimes it is obvious when a number is about to run out, but in many cases AX has it hidden on a transactional level that is not necessarily visible for the users. When the sequence runs out, it can cause serious issues:

• database locking
• error messages
• rolled back transactions.

Proactive monitoring is key to a healthy ERP system on many levels. Number sequence consumption monitoring is no exception. We are running a Transact-SQL script that keeps tracking of the number sequence utilization, and sends out an e-mail with entries reaching a set threshold. We are running the job based on a weekly schedule. We include any sequences that have used up at least 70% of their available range.

/*
	ONLY NEED THIS IN AN ALWAYSON HIGH AVAILABILITY GROUP,
	TO EXECUTE JOB ON PRIMARY REPLICA EXCLUSIVELY
*/

IF sys.fn_hadr_is_primary_replica(DB_NAME()) <> 1   
BEGIN  
-- If this is not the primary replica, exit
	RETURN
END  
-- If this is the primary replica, continue

/*
	MAIN PART FOR SELECTING NUMBER SEQUENCE CONSUMPTION
*/

DECLARE @tableHTML NVARCHAR(MAX);

SET @tableHTML =
    N'<H1>Number sequences consumed above threshold 70%</H1>' +
    N'<table border="1">' +
    N'<tr><th>Sequence</th><th>Text</th>' +
    N'<th>Min</th><th>Max</th><th>Next</th>' +
    N'<th>Percentage</th></tr>' +
    CAST ( ( SELECT td = [NUMBERSEQUENCE],''
				  ,td = [TXT],''
				  ,td = [LOWEST],''
				  ,td = [HIGHEST],''
				  ,td = [NEXTREC],''
				  ,td = CONVERT(NUMERIC, (CONVERT(REAL, ([NEXTREC]-[LOWEST]) / CONVERT(REAL, [HIGHEST]-[LOWEST] ))*100))
			  FROM [dbo].[NUMBERSEQUENCETABLE]
			  WHERE (NEXTREC - LOWEST) >= (HIGHEST - LOWEST) * 0.7
			  ORDER BY (CONVERT(REAL, ([NEXTREC]-[LOWEST]) / CONVERT(REAL, [HIGHEST]-[LOWEST] ))*100) DESC
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

--select @tableHTML

EXEC msdb.dbo.sp_send_dbmail
    @recipients = 'YOUREMAILADDRESS',
    @subject = 'Number sequences consumed above threshold',
    @profile_name = 'YOURDATABASEMAILPROFILE',
    @attach_query_result_as_file=0,
    @execute_query_database = 'YOURAXDBNAME',
	@body_format = 'HTML',
	@body = @tableHTML;

Updating the sequence for journals, vouchers and transactional data multiple times a year is common. Our approach is to include 2 digits of the year, and alphanumeric character that can be increased with each reset of the sequence. For example: SO20A##### -> SO20B##### -> SO21A##### (next year). The following job could be used to set such a value:

static void WIK_updateNumSeqFormat(Args _args)
{
    // SET THESE PARAMETER VALUES -->
    #define.NumberSequenceToUpdate('SalesOrder')
    /*
        Alphanumeric format value   =   -2
        Constant format value       =   -1
        Company format value        =   0
    */
    container               newFormat =
        [
            [-2, '#####'],
            [-1, 'SO20A']
        ];
    // SET THESE PARAMETER VALUES <--

    #OCCRetryCount
    #define.SleepTime(100)
    container               currentFormat;
    NumberSequenceTable     numberSequenceTable;
    str                     annotatedFormat;
    str                     format;

    currentFormat = NumberSeq::parseAnnotatedFormat(numberSequenceTable.AnnotatedFormat);

    try
    {
        ttsBegin;

        numberSequenceTable = NumberSequenceTable::findByNaturalKey(
            #NumberSequenceToUpdate,
            NumberSeqScopeFactory::createDefaultScope().getId(),
            true
            );

        if (!numberSequenceTable)
        {
            throw error(Error::missingRecord(tableStr(NumberSequenceTable)));
        }

        annotatedFormat = NumberSeq::createAnnotatedFormatFromSegments(newFormat);
        format = NumberSeq::createAnnotatedFormatFromSegments(newFormat, false);

        numberSequenceTable.selectForUpdate(true);
        numberSequenceTable.AnnotatedFormat = annotatedFormat;
        numberSequenceTable.Format          = format;
        numberSequenceTable.NextRec         = 1;
        info(strFmt('Updating number sequence %1 format %2 -> format %3',
            numberSequenceTable.NumberSequence,
            numberSequenceTable.orig().Format,
            numberSequenceTable.Format
            ));
        numberSequenceTable.update();
        
        ttsCommit;
    }
    catch (Exception::Deadlock)
    {
        sleep(#SleepTime);
        retry;
    }
    catch (Exception::UpdateConflict)
    {
        if (appl.ttsLevel() == 0)
        {
            if (xSession::currentRetryCount() >= #RetryNum)
            {
                throw Exception::UpdateConflictNotRecovered;
            }
            else
            {
                retry;
            }
        }
        else
        {
            throw Exception::UpdateConflict;
        }
    }
    catch
    {
        exceptionTextFallThrough();
    }
}

 Source code for the SQL job and X++ are available on GitHub.