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 user to get the next Temporary Creditor ID (making sure the selected number hasn;t been used already); it uses the uf_AZRCRV_GetAlpha and uf_AZRCRV_GetNumber functions posted over the last couple of articles in this series.
There may be an “official” way of getting the next Temporary Creditor ID, but I wasn’t able to determine what that was, so this script was created.
-- drop stored proc if it exists
IF OBJECT_ID (N'usp_ISC_SW_GetNextTemporaryVendorID', N'P') IS NOT NULL
DROP PROCEDURE usp_ISC_SW_GetNextTemporaryVendorID
GO
-- create stored proc
CREATE PROCEDURE [dbo].[usp_ISC_SW_GetNextTemporaryVendorID]
/*
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).
*/
AS
SET NOCOUNT ON
BEGIN TRAN
-- select next number from table into variable
DECLARE @NXTVNDID VARCHAR(15)
DECLARE @AlphaPart VARCHAR(15)
DECLARE @NumberPart VARCHAR(15)
DECLARE @NewNXTVNDID AS VARCHAR(15)
DECLARE @LOOP AS INTEGER = 1
WHILE (@LOOP >= 1)
BEGIN
SET @NXTVNDID = (SELECT NXTVNDID FROM PM40100) --PM Setup File (PM40100)
SELECT @AlphaPart = dbo.uf_AZRCRV_GetAlpha(@NXTVNDID)
SELECT @NumberPart = dbo.uf_AZRCRV_GetNumber(@NXTVNDID)
SELECT @LOOP = COUNT(0) FROM PM00200 WHERE VENDORID = @NXTVNDID --PM Vendor Master File (PM00200)
SET @NumberPart = @NumberPart + 1
SET @NewNXTVNDID = @AlphaPart + REPLICATE('0', LEN(@NXTVNDID) - LEN(@AlphaPart) - LEN(@NumberPart)) + CONVERT(VARCHAR(20), @NumberPart)
UPDATE
PM40100
SET
NXTVNDID = @NewNXTVNDID
END
SELECT @NXTVNDID AS NXTVNDID
COMMIT TRAN
-- return variable containing next number
RETURN
GO
GRANT EXECUTE ON usp_ISC_SW_GetNextTemporaryVendorID TO DYNGRP
GO
