Stored Procedure To Get Next Purchase Receipt Number

This stored procedure can be executed to generate the next sequential purchase receipt number which can be used for both receivings transactions (Shipment and Shipment/Invoice) and invoices; the generated invoice was then added to the integration file which was then submitted to eConnect. I’ve written this stored procedure at least three times for different integrations, so thought it best to post it here so I don’t write it again.

-- drop stored proc if it exists
IF OBJECT_ID (N'usp_AZRCRV_GetNextPOPReceiptNumber', N'P') IS NOT NULL
    DROP PROCEDURE usp_AZRCRV_GetNextPOPReceiptNumber
GO
-- create stored proc
CREATE PROCEDURE usp_AZRCRV_GetNextPOPReceiptNumber 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 INT
	DECLARE @I_vInc_Dec TINYINT = 1
	DECLARE @O_vPOPRCTNM AS VARCHAR(17)
	DECLARE @O_iErrorState INT

	exec @return_value = taGetPurchReceiptNextNumber  @I_vInc_Dec, @O_vPOPRCTNM = @O_vPOPRCTNM OUTPUT,  @O_iErrorState = @O_iErrorState OUTPUT
	SELECT @O_vPOPRCTNM
END
GO

-- grant execute permission on stored proc to DYNGRP
GRANT EXECUTE ON usp_AZRCRV_GetNextPOPReceiptNumber TO DYNGRP
GO

-- execute stored proc
EXEC usp_AZRCRV_GetNextPOPReceiptNumber
GO

The stored proc calls a Microsoft Dynamics GP stored procedure which actually does the work, so we are still getting the receipt number using standard functionality.