UFN_BATCHNUMBERINGSCHEME_GETNEXTBATCHNUMBER

CREATE function dbo.UFN_BATCHNUMBERINGSCHEME_GETNEXTBATCHNUMBER(
    @ID uniqueidentifier
)
returns nvarchar(100)
as
    begin

    declare @USERDEFINEDLABEL nvarchar(50);
    declare @LABELPLACECODE tinyint;
    declare @SEQUENCEPLACECODE tinyint;
    declare @DATEFORMAT nvarchar(10);
    declare @DATEFORMATPLACECODE tinyint;
    declare @PART1 nvarchar(100);
    declare @SEP1 nvarchar(1);
    declare @PART2 nvarchar(100);
    declare @SEP2 nvarchar(1);
    declare @PART3 nvarchar(100);
    declare @LEADINGZEROES int;
    declare @FORMATTEDNUMBER nvarchar(20);
    declare @NEXTBATCHNUMBER nvarchar(100);

    select
        @USERDEFINEDLABEL = USERDEFINEDLABEL,
        @LABELPLACECODE = (case INCLUDELABEL when 1 then LABELPLACECODE else 0 end),
        @FORMATTEDNUMBER = cast(ACTUALSEQUENCE as nvarchar),
        @SEQUENCEPLACECODE = SEQUENCEPLACECODE,
        @DATEFORMAT = DATEFORMAT,
        @DATEFORMATPLACECODE = (case INCLUDEDATEFORMAT when 1 then DATEFORMATPLACECODE else 0 end),
        @LEADINGZEROES = NUMBERFORMATCODE,
        @SEP1 = case when (INCLUDELABEL=1) or (INCLUDEDATEFORMAT=1) then (case (case 1 when SEQUENCEPLACECODE then SEQUENCESEPARATORCODE when DATEFORMATPLACECODE then DATESEPARATORCODE when LABELPLACECODE then LABELSEPARATORCODE else 0 end) when (1) then N' ' when (2) then N'-' when (3) then N'/' when (4) then N'.' when (5) then N',' when (6) then N'_' when (7) then N'*' when (8) then N':' else '' end) else '' end,
        @SEP2 = case when (INCLUDELABEL=1) AND (INCLUDEDATEFORMAT=1) then (case (case 2 when SEQUENCEPLACECODE then SEQUENCESEPARATORCODE when DATEFORMATPLACECODE then DATESEPARATORCODE when LABELPLACECODE then LABELSEPARATORCODE else 0 end) when (1) then N' ' when (2) then N'-' when (3) then N'/' when (4) then N'.' when (5) then N',' when (6) then N'_' when (7) then N'*' when (8) then N':' else '' end) else '' end
    from
        dbo.BATCHNUMBERINGSCHEME
    where
        ID = @ID;

    if @LEADINGZEROES >= len(@FORMATTEDNUMBER) set @FORMATTEDNUMBER = replicate('0',1+(@LEADINGZEROES-len(@FORMATTEDNUMBER)))+@FORMATTEDNUMBER;

    if @DATEFORMATPLACECODE > 0
    begin
        declare @today datetime;
        set @today = getdate();

        if charindex('YYYY', @DATEFORMAT) > 0
            set @DATEFORMAT = replace(@DATEFORMAT, 'YYYY', right('0000'+cast(year(@today) as nvarchar), 4));
        else if charindex('YY', @DATEFORMAT) > 0
            set @DATEFORMAT = replace(@DATEFORMAT, 'YY', right('0000'+cast(year(@today) as nvarchar), 2));

        if charindex('MM', @DATEFORMAT) > 0
            set @DATEFORMAT = replace(@DATEFORMAT, 'MM', right('0' + cast(month(@today) as nvarchar), 2));

        if charindex('DD', @DATEFORMAT) > 0
            set @DATEFORMAT = replace(@DATEFORMAT, 'DD', right('0' + cast(day(@today) as nvarchar), 2));

    end;   

    SELECT @PART1=case 1 when @SEQUENCEPLACECODE then @FORMATTEDNUMBER when @DATEFORMATPLACECODE then @DATEFORMAT when @LABELPLACECODE then @USERDEFINEDLABEL else '' end;
    SELECT @PART2=case 2 when @SEQUENCEPLACECODE then @FORMATTEDNUMBER when @DATEFORMATPLACECODE then @DATEFORMAT when @LABELPLACECODE then @USERDEFINEDLABEL else '' end;
    SELECT @PART3=case 3 when @SEQUENCEPLACECODE then @FORMATTEDNUMBER when @DATEFORMATPLACECODE then @DATEFORMAT when @LABELPLACECODE then @USERDEFINEDLABEL else '' end;

    set @NEXTBATCHNUMBER=left(@PART1+@SEP1+@PART2+@SEP2+@PART3,100);

    return @NEXTBATCHNUMBER;

    end