I’ve previously posted SQL stored procedures to get the next GL Journal Number and the next PO Receipt number and today it is the turn of a stored procedure to get the next PM Voucher Number.
The stored procedure will call the eConnect stored procedure which gets the next number and increments the stored value. This code was written so I could easily call it from VBA in Integration Manager for an integration which needed to insert some data into a custom table. This allowed me to get the voucher number up front and use it in the VBA.
-- drop stored proc if it exists
IF OBJECT_ID(N'usp_AZRCRV_GetNextPMVoucherNumber', N'P') IS NOT NULL
DROP PROCEDURE usp_AZRCRV_GetNextPMVoucherNumber
GO
-- create stored proc
CREATE PROCEDURE usp_AZRCRV_GetNextPMVoucherNumber AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
BEGIN
DECLARE @return_value AS INT
DECLARE @O_vCNTRLNUM AS VARCHAR(17)
DECLARE @I_sCNTRLTYP AS TINYINT = 0
DECLARE @O_iErrorState AS INT
EXEC @return_value = taGetPMNextVoucherNumber
@I_sCNTRLTYP = @I_sCNTRLTYP
,@O_iErrorState = @O_iErrorState OUTPUT
,@O_vCNTRLNUM = @O_vCNTRLNUM OUTPUT
SELECT @O_vCNTRLNUM AS VCHRNMBR
END
GO
-- grant execute permission on stored proc to DYNGRP
GRANT EXECUTE ON usp_AZRCRV_GetNextPMVoucherNumber TO DYNGRP
GO
The stored procedure can be executed using this command:
-- execute stored proc
EXEC usp_AZRCRV_GetNextPMVoucherNumber
GO