/**
    * Don't use this stored proc directly but instead use spBATGenerateFirstRevGLNumber_Synonym.
    * spBATGenerateFirstRevGLNumber_Synonym will either point to this stored proc or to
    * spBATGenerateFirstRevGLNumber_XLedger if that is active.
    *
    *
    * Description: If parameters are valid, new GLNumbers is generated and error code 0 is returned. Error Codes:
    *
    * 110 if RevGLAccount is null for @icdPriKey
    * 111 if length of RevGLAccount > length allowed for RevGLAccount when @glSource = 'Natural',
    * 112 if RevGLSubAccount is null for @icdPriKey
    * 113 if length of RevGLAccount > length allowed for RevGLAccount when @glSource = 'Sub'  
    * 114 if CompanyGLCode in Branch is not valid for 'Company'
    * 115 if length of CompanyGLCode > length allowed for CompanyGLCode when @glSource = 'Company'
    * 116 if GLCode in Branch is not valid for 'Branch'
    * 117 if length of GLCode > length allowed for GLCode when @glSource = 'Branch'
    * 118 if GLCode in Division is not valid when @glSource = 'Division'
    * 119 if length of GLCode > length allowed for GLCode when @glSource = 'Division'
    * 120 if GLSource not in ("Natural", "Sub", "Company", Branch", "Division")
    * 121 if GLCPriKey is invalid for @BranchPriKey
    * 154 if @DivisionID is not valid when @glSource = 'Division'
    * 687 if there is more than 1 record in GLSource with SourceName = 'Natural'
    * 688 if there is more than 1 record in GLSource with GLSourceName = 'Sub'
    * 689 if there is more than 1 record in GLSource with GLSourceName = 'Company'
    * 690 if there is more than 1 record in GLSource with GLSourceName = 'Branch'
    * 691 if there is more than 1 record in GLSource with GLSourceName = 'Division
    * 703 if @BranchPriKey is invalid
    * 741 if @firstGLNumber is not valid in Great Plains
    */

CREATE PROCEDURE [dbo].[spBATGenerateFirstRevGLNumber_Legacy]
    @inSessionID varchar( 50 ),
    @ICPriKey int,
    @BranchPriKey int,
    @DivisionID int,
    @OrdPriKey int,
    @BATProcessPriKey int,
    @Source varchar(50),
    @SourcePriKey int
as

set nocount on

declare @glNumber varchar( 50 )
declare @glcPriKey int
declare @countGLCSegmets int
declare @glcSourceName varchar( 10 )
declare @glsCounter int
declare @padLength int
declare @glAccount varchar( 100 )
declare @firstGLNumber varchar( 66 )
declare @batErrorCode int
declare @icdPriKey int

set @batErrorCode = 0
set @glsCounter = 1
set @firstGLNumber = ' '

-- If @BranchPrikey is not valid, return error code 703
if not exists
(
    select 1
    from Branch
    where BranchPriKey = @BranchPriKey
)
begin
    exec @batErrorCode = spBATRecordError
        @inSessionID,
        @Source,
        @SourcePriKey,
        703,
        'Order',
        @OrdPriKey

    set @firstGLNumber = null
end
else -- Otherwise get the GLCPriKey of the Branch
begin
    set @glcPriKey =
    (
        select GLCPriKey
        from Branch
        where BranchPriKey =  @BranchPriKey
    )
    set @countGLCSegmets =
    (
        select count(*)
        from GLDetail
        where GLCPriKey = @glcPriKey
    )
end

-- If @glcPriKey is not vaild. return null for FirstGLNumber and error code 121
if @glcPriKey is null
begin
    exec @batErrorCode = spBATRecordError
        @inSessionID,
        @Source,
        @SourcePriKey,
        121,
        'Order',
        @OrdPriKey

    set @firstGLNumber = null
end
else -- Otherwise get ICDPriKey for the record that matches the selection process
begin
    exec @icdPriKey = spBATSelectionProcess
        @ICPriKey,
        @glcPriKey,
        @OrdPriKey,
        'ICDetail',
        @Source,
        @SourcePriKey,
        null
end

-- If there is more than 1 GLSourceName of "Natural" in GLSource, return error code 687
if( select count(*)
    from GLSource
    where SourceName = 'Natural' ) > 1
begin
    exec @batErrorCode = spBATRecordError
        @inSessionID,
        @Source,
        @SourcePriKey,
        687,
        'Order',
        @OrdPriKey

    set @firstGLNumber = null
end

-- If there is more than 1 GLSourceName of "Sub" in GLSource, return error code 688
if( select count(*)
    from GLSource
    where SourceName = 'Sub' ) > 1
begin
    exec @batErrorCode = spBATRecordError
        @inSessionID,
        @Source,
        @SourcePriKey,
        688,
        'Order',
        @OrdPriKey

    set @firstGLNumber = null
end

-- If there is more than 1 GLSourceName of "Company" in GLSource, return error code 689
if( select count(*)
    from GLSource
    where SourceName = 'Company' ) > 1
begin
    exec @batErrorCode = spBATRecordError
        @inSessionID,
        @Source,
        @SourcePriKey,
        689,
        'Order',
        @OrdPriKey

    set @firstGLNumber = null
end

-- If there is more than 1 GLSourceName of "Branch" in GLSource, return error code 690
if( select count(*)
    from GLSource
    where SourceName = 'Branch' ) > 1
begin
    exec @batErrorCode = spBATRecordError
        @inSessionID,
        @Source,
        @SourcePriKey,
        690,
        'Order',
        @OrdPriKey

    set @firstGLNumber = null
end

-- If there is more than 1 GLSourceName of "Division" in GLSource, return error code 691
if( select count(*)
    from GLSource
    where SourceName = 'Division' ) > 1
begin
    exec @batErrorCode = spBATRecordError
        @inSessionID,
        @Source,
        @SourcePriKey,
        691,
        'Order',
        @OrdPriKey

    set @firstGLNumber = null
end

-- If there were no errors, build the GL Number
if @batErrorCode = 0
begin -- Start of @icdPriKey not null
    
    -- For each record in GLDetail with GLCPriKey = @glcPriKey and SegmentNumber = @glsCounter, check the source name
    while @glsCounter <= @countGLCSegmets
    begin -- Begin of @glsCounter <= @countGLCSegmets
        set @glcSourceName =
        (
            select gls.SourceName
            from GLSource gls
            inner join GLDetail gld on gld.GLSPriKey = gls.GLSPriKey
            where
                gld.GLCPriKey = @glcPriKey and
                gld.SegmentNumber = @glsCounter
        )
        set @padLength =
        (
            select SegmentLength
            from GLDetail
            where
                SegmentNumber = @glsCounter and
                GLCPriKey = @glcPriKey
        )
        if @glcSourceName = 'Natural'
        begin -- Start of @glcSourceName = Natural
            set @glNumber =
            (
                select ltrim( rtrim( RevGLAccount ) )
                from ICDetail
                where ICDPriKey = @icdPriKey
            )

            -- If there is no RevGLAccount but the mapping for authority, commodity, and movetype exist (the ICDPriKey exists),
            -- return null FirstGLNumber and error code 110.  If the ICDPriKey does not exist an error (143) has already been
            -- thrown in spBATSelectionProcess
            if @glNumber is null and @icdPriKey != 0
            begin
                exec @batErrorCode = spBATRecordError
                    @inSessionID,
                    @Source,
                    @SourcePriKey,
                    110,
                    'Order',
                    @OrdPriKey,
                    @icdPriKey
                    
                set @firstGLNumber = null
            end

            -- Else if the length of RevGLAccount is greater than the @padLength return null FIrstGLNumber and error code 111
            else if len( rtrim( @glNumber ) ) > @padLength
            begin
                exec @batErrorCode = spBATRecordError
                    @inSessionID,
                    @Source,
                    @SourcePriKey,
                    111,
                    'Order',
                    @OrdPriKey

                set @firstGLNumber = null
            end
            else -- Otherwise check if @firstGLNumber needs to insert a dash (-).
            begin

                -- Pad the number with zeros
                exec @glAccount = udfLeftPadding
                    @padLength,
                    @glNumber

                -- If @firstGLNumber is not empty and it is not the last iteration of the loop, insert a dash (-) between numbers
                if @glsCounter > 1 and @firstGLNumber <> ' '
                begin
                    set @firstGLNumber = ltrim( rtrim( @firstGLNumber ) ) + '-' + ltrim( rtrim( @glAccount ) )
                end
                else -- Otherwise no dash is needed
                begin
                    set @firstGLNumber = ltrim( rtrim( @firstGLNumber ) ) + ltrim( rtrim( @glAccount ) )
                end
            end
        end -- End of @glcSourceName = 'Natural'
        else if @glcSourceName = 'Sub'
        begin -- Start of @glcSourceName = Sub
            set @glNumber =
            (
                select ltrim( rtrim( RevGLSubAccount ) )
                from ICDetail
                where ICDPriKey = @icdPriKey
            )

            -- If RevGLSubAccount is not valid but the mapping for authority, commodity, and movetype exist (the ICDPriKey exists),
            -- return null FirstGLNumber and error code 112.  If the ICDPriKey does not exist an error (143) has already been
            -- thrown in spBATSelectionProcess
            if @glNumber is null and @icdPriKey != 0
            begin
                exec @batErrorCode = spBATRecordError
                    @inSessionID,
                    @Source,
                    @SourcePriKey,
                    112,
                    'Order',
                    @OrdPriKey,
                    @icdPriKey
                    
                set @firstGLNumber = null
            end

            -- Else if the length of RevGLSubAccount is greater than @padLength return null FirstGLNumber and error code 113
            else if len( rtrim( @glNumber ) ) > @padLength
            begin
                exec @batErrorCode = spBATRecordError
                    @inSessionID,
                    @Source,
                    @SourcePriKey,
                    113,
                    'Order',
                    @OrdPriKey
                    
                set @firstGLNumber = null
            end
            else -- Otherwise check if @firstGLNumber needs to insert a dash (-).
            begin

                -- Pad the number with zeros
                exec @glAccount = udfLeftPadding
                    @padLength,
                    @glNumber

                -- If @firstGLNumber is not empty and it is not the last iteration of the loop, insert a dash (-) between numbers
                if @glsCounter > 1 and @firstGLNumber <> ' '
                begin
                    set @firstGLNumber = ltrim( rtrim( @firstGLNumber ) ) + '-' + ltrim( rtrim( @glAccount ) )
                end
                else -- Otherwise no dash is needed
                begin
                    set @firstGLNumber = ltrim( rtrim( @firstGLNumber ) ) + ltrim( rtrim( @glAccount ) )
                end
            end
        end -- End of @glcSourceName = Sub

        -- Else if @glcSourceName = "Company, begin selection process.
        else if @glcSourceName = 'Company'
        begin -- Start of @glcSourceName = 'Company'
            set @glNumber =
            (
                select ltrim( rtrim( CompanyGLCode ) )
                from Branch
                where BranchPriKey = @BranchPriKey
            )

            -- If CompanyGLCode is not valid, return null FirstGLNumber and error code 114
            if @glNumber is null
            begin
                exec @batErrorCode = spBATRecordError
                    @inSessionID,
                    @Source,
                    @SourcePriKey,
                    114,
                    'Order',
                    @OrdPriKey

                set @firstGLNumber = null
            end

            -- Else if the length of CompanyGLCode is greater than the @padLength return null FirstGLNumber and error code 115
            else if len( rtrim( @glNumber ) ) > @padLength
            begin
                exec @batErrorCode = spBATRecordError
                    @inSessionID,
                    @Source,
                    @SourcePriKey,
                    115,
                    'Order',
                    @OrdPriKey

                set @firstGLNumber = null
            end
            else -- Otherwise, pad CompanyGLCode and append it to @firstGlNumber
            begin
                exec @glAccount = udfLeftPadding
                    @padLength,
                    @glNumber

                -- If @firstGLNumber is not empty and it is not the last iteration of the loop, insert a dash (-) between numbers
                if @glsCounter > 1 and @firstGLNumber <> ' '
                begin
                    set @firstGLNumber = ltrim( rtrim( @firstGLNumber ) )  + '-' + ltrim( rtrim( @glAccount ) )
                end
                else -- Otherwise no dash is needed
                begin
                    set @firstGLNumber = ltrim( rtrim( @firstGLNumber ) ) + ltrim( rtrim( @glAccount ) )
                end
            end
        end -- End of @glcSourceName = 'Company'

        -- Else if @glcSourceName = "Branch, begin selection process.
        else if @glcSourceName = 'Branch'
        begin -- Start of @glcSourceName = 'Branch'
            set @glNumber =
            (
                select ltrim( rtrim( GLCode ) )
                from Branch
                where BranchPriKey = @BranchPriKey
            )

            -- If GLCode is not valid, return null FirstGLNumber and error code 116
            if @glNumber is null
            begin
                exec @batErrorCode = spBATRecordError
                    @inSessionID,
                    @Source,
                    @SourcePriKey,
                    116,
                    'Order',
                    @OrdPriKey
                    
                set @firstGLNumber = null
            end

            -- Else if the length of GLCode is greater than the @padLength return null FirstGLNumber and error code 117
            else if len( rtrim( @glNumber ) ) > @padLength
            begin
                exec @batErrorCode = spBATRecordError
                    @inSessionID,
                    @Source,
                    @SourcePriKey,
                    117,
                    'Order',
                    @OrdPriKey
                    
                set @firstGLNumber = null
            end
            else -- Otherwise pad GLCode with zeros and append it to @firstGLNumber
            begin       
                exec @glAccount = udfLeftPadding
                    @padLength,
                    @glNumber

                -- If @firstGLNumber is not empty and it is not the last iteration of the loop, insert a dash (-) between numbers
                if @glsCounter > 1 and @firstGLNumber <> ' '
                begin
                    set @firstGLNumber = ltrim( rtrim( @firstGLNumber ) )  + '-' + ltrim( rtrim( @glAccount ) )
                end
                else -- Otherwise, no dash (-) is needed.
                begin
                    set @firstGLNumber = ltrim( rtrim( @firstGLNumber ) ) + ltrim( rtrim( @glAccount ) )
                end
            end
        end -- End of @glcSourceName = 'Branch'

        -- Else if @glcSourceName = "Division, begin selection process.
        else if @glcSourceName = 'Division'
        begin -- Start of @glcSourceName = 'Division'

            -- If @DivisionID in not valid return error code 154
            if not exists
            (
                select DivisionID
                from Division
                where DivisionID = @DivisionID
            )
            begin
                exec @batErrorCode = spBATRecordError
                    @inSessionID,
                    @Source,
                    @SourcePriKey,
                    154,
                    'Order',
                    @OrdPriKey

                set @firstGLNumber = null
            end
            else -- Otherwise get the GLCode from the Division
            begin -- Start of valid @DivisionID
                set @glNumber =
                (
                    select ltrim( rtrim( GLCode ) )
                    from Division
                    where DivisionID = @DivisionID
                )

                -- If GLCode is not valid, return null FirstGLNumber and error code 118
                if @glNumber is null
                begin
                    exec @batErrorCode = spBATRecordError
                        @inSessionID,
                        @Source,
                        @SourcePriKey,
                        118,
                        'Order',
                        @OrdPriKey
                        
                    set @firstGLNumber = null
                end

                -- Else if the length of GLCode is greater than the @padLength return null FirstGLNumber and error code 119
                else if len( rtrim( @glNumber ) ) > @padLength
                begin
                    exec @batErrorCode = spBATRecordError
                        @inSessionID,
                        @Source,
                        @SourcePriKey,
                        119,
                        'Order',
                        @OrdPriKey
                        
                    set @firstGLNumber = null
                end
                else -- Otherwise, pad GLCode with zeros and append it to @firstGLNumber
                begin
                    exec @glAccount = udfLeftPadding
                        @padLength,
                        @glNumber

                    -- If @firstGLNumber is not empty and it is not the last iteration of the loop, insert a dash (-) between numbers
                    if @glsCounter > 1 and @firstGLNumber <> ' '
                    begin
                        set @firstGLNumber = ltrim( rtrim( @firstGLNumber ) )  + '-' + ltrim( rtrim( @glAccount ) )
                    end
                    else -- Otherwise no dash is needed
                    begin
                        set @firstGLNumber = ltrim( rtrim( @firstGLNumber ) ) + ltrim( rtrim( @glAccount ) )
                    end
                end
            end -- End of valid @DivisionID
        end -- End of @glcSourceName = 'Division'
        -- Otherwise return null for FirstGLNumber and error code 120 because @glSource not in (Natural, Sub, Company, Branch, or Division)
        else
        begin
            exec @batErrorCode = spBATRecordError
                @inSessionID,
                @Source,
                @SourcePriKey,
                120,
                'Order',
                @OrdPriKey

            set @glsCounter = @countGLCSegmets + 1
            set @firstGLNumber = null
        end
        set @glsCounter = @glsCounter + 1
    end -- End of @glsCounter <= @countGLCSegmets
end -- End of @icdPriKey not null

-- Only if @firstGLNumber is not null update the record in BATProcess
if @firstGLNumber is not null
begin
    -- Validate @firstGLNumber
    exec @batErrorCode = spBATCheckGLNumber
        @inSessionID,
        @firstGLNumber,
        @branchPriKey,
        @Source,
        @SourcePriKey,
        'Order',
        @OrdPriKey

    -- If @firstGLNumber is valid, update GLNumber in BATProcess, otherwise return error code 741
    if @batErrorCode = 0
    begin
        update BATProcess
        set GLNumber = @firstGLNumber
        where BATProcessPriKey = @BATProcessPriKey
    end
    else
    begin
        exec @batErrorCode = spBATRecordError
            @inSessionID,
            @Source,
            @SourcePriKey,
            741,
            'Order',
            @OrdPriKey,
            @firstGLNumber
    end
end
GO
GRANT EXECUTE ON  [dbo].[spBATGenerateFirstRevGLNumber_Legacy] TO [MssExec]
GO