USP_BATCH_GETEXCEPTIONBATCHNUMBER_ATOMIC

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

    declare @BATCHTEMPLATEID uniqueidentifier;
    declare @USERDEFINEDNUMBER nvarchar(60);
    declare @EXCEPTIONBATCHNUMBERCODE tinyint;
    declare @INCLUDEEXCEPTIONPREFIX bit;
    declare @EXCEPTIONPREFIX nvarchar(8);
    declare @NUMBERINGSCHEMEID uniqueidentifier;

    begin transaction;

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

    select
        @USERDEFINEDNUMBER = BATCH.EXCEPTIONBATCHNAME,
        @EXCEPTIONBATCHNUMBERCODE = BATCHNUMBERINGSCHEME.EXCEPTIONBATCHNUMBERCODE,
        @INCLUDEEXCEPTIONPREFIX = BATCHNUMBERINGSCHEME.INCLUDEEXCEPTIONPREFIX,
        @EXCEPTIONPREFIX = BATCHNUMBERINGSCHEME.EXCEPTIONPREFIX,
        @NUMBERINGSCHEMEID = BATCHNUMBERINGSCHEME.ID,
        @BATCHTEMPLATEID = BATCH.BATCHTEMPLATEID
    from dbo.BATCH
        inner join dbo.BATCHTEMPLATE on BATCH.BATCHTEMPLATEID = BATCHTEMPLATE.ID
        inner join dbo.BATCHNUMBERINGSCHEME on BATCHTEMPLATE.BATCHNUMBERINGSCHEMEID = BATCHNUMBERINGSCHEME.ID
    where 
        BATCH.ID = @ID;

    if len(@USERDEFINEDNUMBER) > 0
        set @BATCHNUMBER = @USERDEFINEDNUMBER;
    else
    begin
        if @EXCEPTIONBATCHNUMBERCODE = 0    -- Assign new number

            exec dbo.USP_BATCHNUMBERINGSCHEME_GETNEXTBATCHNUMBER_ATOMIC @NUMBERINGSCHEMEID, @BATCHTEMPLATEID, @BATCHNUMBER output, @CHANGEAGENTID;
        else    -- Use current number plus sequence

        begin
            set @BATCHNUMBER = '';

            declare @LEVEL int, @ORIGINALBATCHNUMBER nvarchar(100), @SEPARATOR nvarchar(9);
            exec dbo.USP_BATCH_GETORIGINALNUMBERANDLEVEL @ID, @ORIGINALBATCHNUMBER output, @LEVEL output

            declare @APPENDTEXT nvarchar(35)
            set @APPENDTEXT = ''

            -- Adding the Separator here, after the original batch number


            select top 1 @SEPARATOR = SEPARATOR
            from
            (select 
                SEQUENCEPLACECODE PLACECODE_1, SEQUENCESEPARATOR SEPARATOR_1, CONVERT(bit, 1) INCLUDE_1,
                LABELPLACECODE PLACECODE_2, LABELSEPARATOR SEPARATOR_2, INCLUDELABEL INCLUDE_2,
                DATEFORMATPLACECODE PLACECODE_3, DATESEPARATOR SEPARATOR_3, INCLUDEDATEFORMAT INCLUDE_3
             from 
                dbo.BATCHNUMBERINGSCHEME
             where ID = @NUMBERINGSCHEMEID) P
            UNPIVOT
                (PLACECODE FOR ID1 IN (PLACECODE_1, PLACECODE_2, PLACECODE_3)) AS UNPVT
            UNPIVOT
                (SEPARATOR for ID2 in (SEPARATOR_1, SEPARATOR_2, SEPARATOR_3)) as UNPVT2
            UNPIVOT
                (INCLUDE for ID3 in (INCLUDE_1, INCLUDE_2, INCLUDE_3)) as UNPVT3  
            where RIGHT(ID1, 1) = RIGHT(ID2, 1) and
                  RIGHT(ID1, 1) = RIGHT(ID3, 1) and
                  INCLUDE = 1
            order by PLACECODE desc;
            -- 2 special cases have string stored in the db

            if @SEPARATOR = '< Space >'
                set @SEPARATOR = ' ';

            if @SEPARATOR = '< None >'
                set @SEPARATOR = '';

            set @APPENDTEXT = @SEPARATOR

            if  (@EXCEPTIONPREFIX is not null) and (len(@EXCEPTIONPREFIX) > 0)
            begin
                set @APPENDTEXT = @APPENDTEXT + @EXCEPTIONPREFIX 
            end

            set @APPENDTEXT = @APPENDTEXT + cast(@LEVEL + 1 as nvarchar)

            set @BATCHNUMBER = substring(@ORIGINALBATCHNUMBER, 1, 100 - len(@APPENDTEXT)) + @APPENDTEXT

            if exists (select top 1 ID from dbo.BATCH where (BATCHTEMPLATEID = @BATCHTEMPLATEID) and (BATCHNUMBER = @BATCHNUMBER))
            begin
                set @APPENDTEXT = @APPENDTEXT + ' ' + convert(nvarchar(20), getdate(), 120)
                set @BATCHNUMBER = substring(@ORIGINALBATCHNUMBER, 1, 100 - len(@APPENDTEXT)) + @APPENDTEXT
            end
        end
    end;

    exec sp_releaseapplock @Resource = 'USP_BATCH_GETEXCEPTIONBATCHNUMBER';

    commit transaction;

    return 0;