SQL Scripts for Microsoft Dynamics GP: Set Next PM Voucher Number

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script was created for a client who was having problems with the Next Voucher Number in the PM Setup File (PM40100) table being set back to am old value and needed a way to set it to the correct value. It uses the uf_AZRCRV_GetAlpha and uf_AZRCRV_GetNumber functions while selecting the highest current voucher number to write away the next number after this.

The client had a prefix of PINV on later invoices so the select uses this to select the correct highest number; the script was run automatically on a schedule using SQL Server Agent.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
DECLARE @NTVCHNUM AS VARCHAR(20)

SELECT
	TOP 1
		@NTVCHNUM = dbo.uf_ISC_SW_GetAlpha(CNTRLNUM) + CAST(dbo.uf_ISC_SW_GetNumber(CNTRLNUM) + 1 AS VARCHAR(15))
	FROM
		PM00400
	WHERE
		CNTRLTYP = 0
	AND
		LEFT(CNTRLNUM, 2) = 'PINV'
	ORDER BY
		CNTRLNUM DESC

UPDATE
	PM40100
SET
	NTVCHNUM = @NTVCHNUM
GO