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
