USP_BATCHNUMBERINGSCHEME_GETNEXTBATCHNUMBER_ATOMIC

Gets the next available batch number for a batch numbering scheme and increments the sequence.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@BATCHTEMPLATEID	uniqueidentifier	IN
@BATCHNUMBER	nvarchar(100)	INOUT
@CHANGEAGENTID	uniqueidentifier	IN

Definition

Copy



CREATE procedure dbo.USP_BATCHNUMBERINGSCHEME_GETNEXTBATCHNUMBER_ATOMIC(
    @ID uniqueidentifier,
    @BATCHTEMPLATEID uniqueidentifier,
    @BATCHNUMBER nvarchar(100) output,
    @CHANGEAGENTID uniqueidentifier = null
)
as
    set nocount on;

    declare @BATCHNUMBER_CHECK nvarchar(100);
    declare @IS_UNIQUE bit;
    set @IS_UNIQUE = 0;

    declare @CHANGEDATE datetime;
    set @CHANGEDATE = getdate();

    if @CHANGEAGENTID is null
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    begin transaction;

    exec sp_getapplock @Resource = 'USP_BATCHNUMBERINGSCHEME_GETNEXTBATCHNUMBER_ATOMIC', @LockMode = 'Exclusive';

    while @IS_UNIQUE = 0
    begin

        set @BATCHNUMBER_CHECK = dbo.UFN_BATCHNUMBERINGSCHEME_GETNEXTBATCHNUMBER(@ID);

        update
            dbo.BATCHNUMBERINGSCHEME
        set
            ACTUALSEQUENCE = ACTUALSEQUENCE + 1,
            DATECHANGED = @CHANGEDATE,
            CHANGEDBYID = @CHANGEAGENTID
        where
            ID = @ID;

        if not exists (select 
                          top 1 BATCH.ID  
                        from dbo.BATCH 
                        inner join dbo.BATCHTEMPLATE on BATCHTEMPLATE.ID = BATCH.BATCHTEMPLATEID
                        where BATCHNUMBER = @BATCHNUMBER_CHECK and BATCHNUMBERINGSCHEMEID = @ID)
            set @IS_UNIQUE = 1;

    end;

    set @BATCHNUMBER = @BATCHNUMBER_CHECK;

    exec sp_releaseapplock @Resource = 'USP_BATCHNUMBERINGSCHEME_GETNEXTBATCHNUMBER_ATOMIC';

    commit transaction;

    return 0;