/**
* Don't use this stored proc directly but instead use spBATGenerateSecondRevGLNumber_Synonym.
* spBATGenerateSecondRevGLNumber_Synonym will either point to this stored proc or to
* spBATGenerateSecondRevGLNumber_XLedger if that is active.
*
*    
*    Description: If parameters are valid, new GLNumbers is generated and error code 0 is returned.
*
*    @param    @ICPriKey
*    @param    @BranchPriKey
*    @param    @DivisionPriKey
*    @param    @OrdPriKey
*    @param    @BATProcessPriKey
*    @param    @Source
*    @param    @SourcePriKey
*
*    Error Codes:
*    122 if GLCPriKey for @BranchPriKey is invalid
*    123 if GLAccount is null for @glaPriKey
*    124 if length of GLAccount > length allowed for RevGLAccount when @glSource = 'Natural',
*    125 if GLSubAccount is null for @glaPriKey
*    126 if length of GLSubAccount > length allowed for RevGLAccount when @glSource = 'Sub'
*    127 if CompanyGLCode in Branch is null when @glSource = 'Company'
*    128 if length of CompanyGLCode > length allowed for CompanyGLCode when @glSource = 'Company'
*    129 if GLCode in Branch is null when @glSource = 'Branch'
*    130 if length of GLCode > length allowed for GLCode when @glSource = 'Branch'
*    131 if GLCode in Division is null when @glSource = 'Division'
*    132 if length of GLCode > length allowed for GLCode when @glSource = 'Division'
*    133 if GLSource not in ("Natural", "Sub", "Company", Branch", "Division")
*    233 if @DivisionPriKey is not valid when @glSource = 'Division'
*    349 if @BranchPriKey.GLCPriKey is invalid when @Source = StatementDetail or StatementDetailDist
*    350 if @glaPriKey.GLAccount is null when @Source = StatementDetail, StatmentDetailDist or StatementPayable
*    351 if length of GLAccount > length allowed for RevGLAccount when @glSource = 'Natural' when @Source = StatementDetail, StatmentDetailDist or StatementPayable,
*    352 if @glaPriKey.GLSubAccount is null when @Source = StatementDetail, StatmentDetailDist or StatementPayable
*    353 if length of GLSubAccount > length allowed for RevGLAccount when @glSource = 'Sub' and @Source = StatementDetail, StatmentDetailDist or StatementPayable
*    354 if CompanyGLCode in Branch is null when @glSource = 'Company' and @Source = StatementDetail, StatmentDetailDist or StatementPayable
*    355 if length of CompanyGLCode > length allowed for CompanyGLCode when @glSource = 'Company' and @Source = StatementDetail, StatmentDetailDist or StatementPayable
*    356 if GLCode in Branch is null when @glSource = 'Branch' and @Source = StatementDetail, StatmentDetailDist or StatementPayable
*    357 if length of GLCode > length allowed for GLCode when @glSource = 'Branch' and @Source = StatementDetail, StatmentDetailDist or StatementPayable
*    358 if @DivisionPriKey is not valid when @glSource = 'Division' and @Source = StatementDetail, StatmentDetailDist or StatementPayable
*    359 if GLCode in Division is null when @glSource = 'Division' and @Source = StatementDetail, StatmentDetailDist or StatementPayable
*    360 if length of GLCode > length allowed for GLCode when @glSource = 'Division' and @Source = StatementDetail, StatmentDetailDist or StatementPayable
*    361 if GLSource not in ("Natural", "Sub", "Company", Branch", "Division") when @Source = StatementDetail, StatmentDetailDist or StatementPayable
*    707 if there is more than 1 record in GLSource with SourceName = 'Natural'
*    708 if there is more than 1 record in GLSource with GLSourceName = 'Sub'
*    709 if there is more than 1 record in GLSource with GLSourceName = 'Company'
*    710 if there is more than 1 record in GLSource with GLSourceName = 'Branch'
*    711 if there is more than 1 record in GLSource with GLSourceName = 'Division
*    712 if @BranchPriKey is not valid
*    713 if ManualGL = 1 and OverrideGLNumber is not valid
*    742 if @secondGLNumber is not in Great Plains
*/

CREATE PROCEDURE [dbo].[spBATGenerateSecondRevGLNumber_Legacy]
    @ICPriKey int,
    @BranchPriKey int,
    @DivisionPriKey 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 @secondGLNumber varchar(66)
declare @batErrorCode int
declare @glaPriKey int
declare @shPriKey int

-- Error constants
declare @ERROR_GLCPRIKEY_IS_INVALID_FOR_BRANCH int
set @ERROR_GLCPRIKEY_IS_INVALID_FOR_BRANCH = 122

declare @ERROR_GL_ACCOUNT_IS_NULL int
set @ERROR_GL_ACCOUNT_IS_NULL = 123

declare @ERROR_GL_ACCOUNT_TOO_LONG int
set @ERROR_GL_ACCOUNT_TOO_LONG = 124

declare @ERROR_GL_SUBACCOUNT_IS_NULL int
set @ERROR_GL_SUBACCOUNT_IS_NULL = 125

declare @ERROR_SUBACCOUNT_TOO_LONG int
set @ERROR_SUBACCOUNT_TOO_LONG = 126

declare @ERROR_COMPANY_GLCODE_IS_NULL int
set @ERROR_COMPANY_GLCODE_IS_NULL = 127

declare @ERROR_COMPANY_GLCODE_IS_TOO_LONG int
set @ERROR_COMPANY_GLCODE_IS_TOO_LONG = 128

declare @ERROR_GLCODE_IN_BRANCH_IS_NULL int
set @ERROR_GLCODE_IN_BRANCH_IS_NULL = 129

declare @ERROR_GLCODE_IN_BRANCH_IS_TOO_LONG int
set @ERROR_GLCODE_IN_BRANCH_IS_TOO_LONG = 130

declare @ERROR_GLCODE_IN_DIVISION_IS_NULL int
set @ERROR_GLCODE_IN_DIVISION_IS_NULL = 131

declare @ERROR_GLCODE_IN_DIVISION_IS_TOO_LONG int
set @ERROR_GLCODE_IN_DIVISION_IS_TOO_LONG = 132

declare @ERROR_GLSOURCE_IS_INVALID int
set @ERROR_GLSOURCE_IS_INVALID = 133

declare @ERROR_DIVISION_IS_INVALID int
set @ERROR_DIVISION_IS_INVALID = 233

declare @ERROR_GLCPRIKEY_IS_INVALID_FOR_BRANCH_FOR_STMTS int
set @ERROR_GLCPRIKEY_IS_INVALID_FOR_BRANCH_FOR_STMTS = 349

declare @ERROR_GL_ACCOUNT_IS_NULL_FOR_STMTS int
set @ERROR_GL_ACCOUNT_IS_NULL_FOR_STMTS = 350

declare @ERROR_GL_ACCOUNT_TOO_LONG_FOR_STMTS int
set @ERROR_GL_ACCOUNT_TOO_LONG_FOR_STMTS = 351

declare @ERROR_GL_SUBACCOUNT_IS_NULL_FOR_STMTS int
set @ERROR_GL_SUBACCOUNT_IS_NULL_FOR_STMTS = 352

declare @ERROR_SUBACCOUNT_TOO_LONG_FOR_STMTS int
set @ERROR_SUBACCOUNT_TOO_LONG_FOR_STMTS = 353

declare @ERROR_COMPANY_GLCODE_IS_NULL_FOR_STMTS int
set @ERROR_COMPANY_GLCODE_IS_NULL_FOR_STMTS = 354

declare @ERROR_COMPANY_GLCODE_IS_TOO_LONG_FOR_STMTS int
set @ERROR_COMPANY_GLCODE_IS_TOO_LONG_FOR_STMTS = 355

declare @ERROR_GLCODE_IN_BRANCH_IS_NULL_FOR_STMTS int
set @ERROR_GLCODE_IN_BRANCH_IS_NULL_FOR_STMTS = 356

declare @ERROR_GLCODE_IN_BRANCH_IS_TOO_LONG_FOR_STMTS int
set @ERROR_GLCODE_IN_BRANCH_IS_TOO_LONG_FOR_STMTS = 357

declare @ERROR_DIVISION_IS_INVALID_FOR_STMTS int
set @ERROR_DIVISION_IS_INVALID_FOR_STMTS = 358

declare @ERROR_GLCODE_IN_DIVISION_IS_NULL_FOR_STMTS int
set @ERROR_GLCODE_IN_DIVISION_IS_NULL_FOR_STMTS = 359

declare @ERROR_GLCODE_IN_DIVISION_IS_TOO_LONG_FOR_STMTS int
set @ERROR_GLCODE_IN_DIVISION_IS_TOO_LONG_FOR_STMTS = 360

declare @ERROR_GLSOURCE_IS_INVALID_FOR_STMTS int
set @ERROR_GLSOURCE_IS_INVALID_FOR_STMTS = 361

declare @ERROR_MORE_THAN_ONE_RECORD_FOR_NATURAL int
set @ERROR_MORE_THAN_ONE_RECORD_FOR_NATURAL = 707

declare @ERROR_MORE_THAN_ONE_RECORD_FOR_SUB int
set @ERROR_MORE_THAN_ONE_RECORD_FOR_SUB = 708

declare @ERROR_MORE_THAN_ONE_RECORD_FOR_COMPANY int
set @ERROR_MORE_THAN_ONE_RECORD_FOR_COMPANY = 709

declare @ERROR_MORE_THAN_ONE_RECORD_FOR_BRANCH int
set @ERROR_MORE_THAN_ONE_RECORD_FOR_BRANCH = 710

declare @ERROR_MORE_THAN_ONE_RECORD_FOR_DIVISION int
set @ERROR_MORE_THAN_ONE_RECORD_FOR_DIVISION = 711

declare @ERROR_INVALID_BRANCH_PRIKEY int
set @ERROR_INVALID_BRANCH_PRIKEY = 712

declare @ERROR_MANUAL_GL_AND_OVERRIDE_NOT_ALLOWED int
set @ERROR_MANUAL_GL_AND_OVERRIDE_NOT_ALLOWED = 713

declare @ERROR_SECOND_GL_NOT_IN_GREAT_PLAINS int
set @ERROR_SECOND_GL_NOT_IN_GREAT_PLAINS = 742


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

-- create a blank Session ID.  Claims will be the only one using an actual Session ID.
declare @theBlankSessionID varchar( 50 )
set @theBlankSessionID = ''

-- We report many errors and the following variables will simplify the logic so that we do not need
-- to test over and over if this is statements else this is orders
declare @theSourceIsStatements bit
declare @theErrorObjectTable varchar(20)
declare @theErrorObjectID int
declare @theErrorCodeID int

if( @Source in ('StatementDetail', 'StatementDetailDist', 'StatementPayable') )
begin
    -- Get the StatementHeader SHPriKey
    set @shPriKey = case @Source
        when 'StatementDetail' then ( select SHPriKey from StatementDetail where SDPriKey = @SourcePriKey )
        when 'StatementDetailDist' then
        (
            select StatementDetail.SHPriKey
            from StatementDetail
            inner join StatementDetailDist on ( StatementDetailDist.SDPriKey = StatementDetail.SDPriKey )
            where StatementDetailDist.SDDPriKey = @SourcePriKey
        )
        when 'StatementPayable' then
        (
            select StatementDetail.SHPriKey
            from StatementDetail
            join StatementPayable on StatementPayable.StatementDetailFID = StatementDetail.SDPriKey
            where StatementPayable.StatementPayableID = @SourcePriKey
        )
    end

    -- For StatementDetail, StatementDetailDist or StatementPayable errors, we will record errors with StatementHeader
    set @theSourceIsStatements = 1
    set @theErrorObjectTable = 'StatementHeader'
    set @theErrorObjectID = @shPriKey
end
else
begin
    -- Note: For orders, @BranchPriKey and @DivisionPriKey are the ARBranch and ARDivision.

    -- Otherwise we will record errors with Order
    set @theSourceIsStatements = 0
    set @theErrorObjectTable = 'Order'
    set @theErrorObjectID = @OrdPriKey
end

-- If @BranchPrikey is not valid, return error code 712
if not exists ( select 1 from Branch where BranchPriKey = @BranchPriKey )
begin
    exec @batErrorCode = spBATRecordError
        @theBlankSessionID,
        @Source,
        @SourcePriKey,
        @ERROR_INVALID_BRANCH_PRIKEY,
        @theErrorObjectTable,
        @theErrorObjectID

    set @secondGLNumber = null
end

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

-- If there is more than 1 GLSourceName of "Natural" in GLSource, return error code 707
if ( select count(*) from GLSource where SourceName = 'Natural' ) > 1
begin
    exec @batErrorCode = spBATRecordError
        @theBlankSessionID,
        @Source,
        @SourcePriKey,
        @ERROR_MORE_THAN_ONE_RECORD_FOR_NATURAL,
        @theErrorObjectTable,
        @theErrorObjectID
    
    set @secondGLNumber = null
end

-- If there is more than 1 GLSourceName of "Sub" in GLSource, return error code 708
if (select count(*) from GLSource where SourceName = 'Sub') > 1
begin
    exec @batErrorCode = spBATRecordError
        @theBlankSessionID,
        @Source,
        @SourcePriKey,
        @ERROR_MORE_THAN_ONE_RECORD_FOR_SUB,
        @theErrorObjectTable,
        @theErrorObjectID
    
    set @secondGLNumber = null
end

-- If there is more than 1 GLSourceName of "Company" in GLSource, return error code 709
if (select count(*) from GLSource where SourceName = 'Company') > 1
begin
    exec @batErrorCode = spBATRecordError
        @theBlankSessionID,
        @Source,
        @SourcePriKey,
        @ERROR_MORE_THAN_ONE_RECORD_FOR_COMPANY,
        @theErrorObjectTable,
        @theErrorObjectID
    
    set @secondGLNumber = null
end

-- If there is more than 1 GLSourceName of "Branch" in GLSource, return error code 710
if (select count(*) from GLSource where SourceName = 'Branch') > 1
begin
    exec @batErrorCode = spBATRecordError
        @theBlankSessionID,
        @Source,
        @SourcePriKey,
        @ERROR_MORE_THAN_ONE_RECORD_FOR_BRANCH,
        @theErrorObjectTable,
        @theErrorObjectID
    
    set @secondGLNumber = null
end

-- If there is more than 1 GLSourceName of "Division" in GLSource, return error code 711
if (select count(*) from GLSource where SourceName = 'Division') > 1
begin
    exec @batErrorCode = spBATRecordError
        @theBlankSessionID,
        @Source,
        @SourcePriKey,
        @ERROR_MORE_THAN_ONE_RECORD_FOR_DIVISION,
        @theErrorObjectTable,
        @theErrorObjectID
    
    set @secondGLNumber = null
end

-- If @glcPriKey is not vaild. check @Source
if( @glcPriKey is null )
begin
    set @theErrorCodeID = case @theSourceIsStatements
        when 1 then @ERROR_GLCPRIKEY_IS_INVALID_FOR_BRANCH_FOR_STMTS
        else @ERROR_GLCPRIKEY_IS_INVALID_FOR_BRANCH
    end
    exec @batErrorCode = spBATRecordError
        @theBlankSessionID,
        @Source,
        @SourcePriKey,
        @theErrorCodeID,
        @theErrorObjectTable,
        @theErrorObjectID
    
    -- Set @secondGLNumber = null
    set @secondGLNumber = null
end

-- Otherwise, get GLAPriKey for the record that matches the selection process
else
begin
    
    -- If @Source = 'StatementDetail', 'StatementDetailDist' or 'StatementPayable', the selection process can use AR or AP transaction type
    if( 1 = @theSourceIsStatements )
    begin  -- Start of StatementDetail, StatementDetailDist or StatementPayable
        
        -- If the payment StatementDetail, check if the transaction is for UnappliedAmount
        if( @Source = 'StatementDetail' )
        begin -- Start of StatementDetail
            
            -- If the transaction is for UnappliedAmount, find the GLARAP using TransactionType AR or for a Positive p
            if exists
            (
                select 1
                from StatementDetail
                inner join BATProcess on ( BATProcess.SourceRecord = StatementDetail.SDPriKey )
                where BATProcess.BATProcessPriKey = @BATProcessPriKey and
                    BATProcess.TransactionAmount = abs( StatementDetail.UnappliedAmount ) and
                    StatementDetail.UnappliedCash = 1
            )
            begin
                exec @glaPriKey = spBATGetUnappliedCashGLARAP
                    @glcPriKey,
                    @OrdPriKey,
                    @Source,
                    @SourcePriKey,
                    @shPriKey
            end
            -- Else if the transaction type for the BATProcess record is AR, find the GLARAP using the AR transaction type
            else if exists ( select 1 from BATProcess where BATProcessPriKey = @BATProcessPriKey and TransactionType like '%A/R%' )
            begin
                exec @glaPriKey = spBATSelectionProcess
                    @ICPriKey,
                    @glcPriKey,
                    @OrdPriKey,
                    'GLARAP',
                    @Source,
                    @SourcePriKey,
                    'AR'
            end
            -- Otherwise find the GLARAP using the AP transaction type
            else
            begin
                exec @glaPriKey = spBATSelectionProcess
                    @ICPriKey,
                    @glcPriKey,
                    @OrdPriKey,
                    'GLARAP',
                    @Source,
                    @SourcePriKey,
                    'AP'
            end
        end -- End of StatementDetail
        
        -- Else, if the payment is StatementDetailDist, check if the transaction is for UnappliedAmount
        else if( @Source = 'StatementDetailDist' )
        begin -- Start of StatementDetailDist
            
            -- If the transaction is for UnappliedAmount, find the GLARAP using TransactionType AR
            if exists
            (
                select 1
                from StatementDetailDist
                inner join BATProcess on ( BATProcess.SourceRecord = StatementDetailDist.SDDPriKey )
                where BATProcess.BATProcessPriKey = @BATProcessPriKey and
                    BATProcess.TransactionAmount = abs( StatementDetailDist.UnappliedAmount ) and
                    StatementDetailDist.UnappliedCash = 1
            )
            begin
                exec @glaPriKey = spBATSelectionProcess
                    @ICPriKey,
                    @glcPriKey,
                    @OrdPriKey,
                    'GLARAP',
                    @Source,
                    @SourcePriKey,
                    'AR'
            end
            -- Else if the transaction is a positive p, find the GLARAP using the AR transaction type
            else if exists
            (
                select 1
                from StatementDetailDist
                inner join BATProcess on BATProcess.SourceRecord = StatementDetailDist.SDDPriKey
                left outer join StatementPayable on StatementPayable.StatementDetailDistFID = StatementDetailDist.SDDPriKey
                where BATProcess.BATProcessPriKey = @BATProcessPriKey and
                    BATProcess.TransactionType like '%A/R%' and
                    StatementDetailDist.Amount > 0 and
                    StatementPayable.StatementPayableID is null
            )
            begin
                exec @glaPriKey = spBATSelectionProcess
                    @ICPriKey,
                    @glcPriKey,
                    @OrdPriKey,
                    'GLARAP',
                    @Source,
                    @SourcePriKey,
                    'AR'
            end
            -- Otherwise find the GLARAP using the AP transaction type
            else
            begin
                exec @glaPriKey = spBATSelectionProcess
                    @ICPriKey,
                    @glcPriKey,
                    @OrdPriKey,
                    'GLARAP',
                    @Source,
                    @SourcePriKey,
                    'AP'
            end
        end -- End of StatementDetailDist
        -- Otherwise, the payment is a StatementPayable, which can only be an AP transaction.
        else
        begin -- Start of StatementPayable
            exec @glaPriKey = spBATSelectionProcess
                @ICPriKey,
                @glcPriKey,
                @OrdPriKey,
                'GLARAP',
                @Source,
                @SourcePriKey,
                'AP'
        end -- End of StatementPayable
    end  -- End of StatementDetail, StatementDetailDist or StatementPayable
    
    -- Otherwise the selection process should use AR transaction type
    else
    begin
        exec @glaPriKey = spBATSelectionProcess
            @ICPriKey,
            @glcPriKey,
            @OrdPriKey,
            'GLARAP',
            @Source,
            @SourcePriKey,
            'AR'
    end
end

-- If ManualGL = 1,  return the OverrideGLNumber
if exists ( select 1 from GLARAP where GLAPriKey = @glaPriKey and GLCPriKey = @glcPriKey and ManualGL = 1 )
begin -- Start of ManualGL
    set @secondGLNumber = ( select OverrideGLNumber from GLARAP where GLAPriKey = @glaPriKey and ManualGL = 1 )
    
    -- If the OverrideGLNumber is not valid, return error code 713
    if( @secondGLNumber is null )
    begin
        exec @batErrorCode = spBATRecordError
            @theBlankSessionID,
            @Source,
            @SourcePriKey,
            @ERROR_MANUAL_GL_AND_OVERRIDE_NOT_ALLOWED,
            @theErrorObjectTable,
            @theErrorObjectID
        
        -- Set the @secondGLNumber = null
        set @secondGLNumber = null
    end
end -- End of ManualGL

-- Otherwise @glaPriKey is valid, generate GL Number
else if exists ( select 1 from GLARAP where GLAPriKey = @glaPriKey and GLCPriKey = @glcPriKey )
begin -- Start of vaild @glaPriKey

    -- For each record in GLDetail with GLCPriKey = @glcPriKey and SegmentNumber = @glsCounter, check the source name
    while( @glsCounter <= @countGLCSegmets )
    begin -- Begin of @glsCounter <= @countGLCSegmets
        set @padLength = ( select SegmentLength from GLDetail where SegmentNumber = @glsCounter and GLCPriKey = @glcPriKey )
        set @glcSourceName =
        (
            select GLSource.SourceName
            from GLSource
            inner join GLDetail on ( GLDetail.GLSPriKey = GLSource.GLSPriKey )
            where GLDetail.GLCPriKey = @glcPriKey and
                GLDetail.SegmentNumber = @glsCounter
        )
        if( @glcSourceName = 'Natural' )
        begin -- Start of @glcSourceName = Natural
            set @glNumber = ( select ltrim( rtrim( GLAccount ) ) from GLARAP where GLCPriKey = @glcPriKey and GLAPriKey = @glaPriKey )
            
            -- If GLAccount is not valid, check @Source
            if( @glNumber is null )
            begin
                set @theErrorCodeID = case @theSourceIsStatements
                    when 1 then @ERROR_GL_ACCOUNT_IS_NULL_FOR_STMTS
                    else @ERROR_GL_ACCOUNT_IS_NULL
                end
                exec @batErrorCode = spBATRecordError
                    @theBlankSessionID,
                    @Source,
                    @SourcePriKey,
                    @theErrorCodeID,
                    @theErrorObjectTable,
                    @theErrorObjectID
                
                set @secondGLNumber = null
            end
            
            -- Else if the length of GLAccount is greater than the @padLength, check @Source
            else if( len( rtrim( @glNumber ) ) > @padLength )
            begin
                set @theErrorCodeID = case @theSourceIsStatements
                    when 1 then @ERROR_GL_ACCOUNT_TOO_LONG_FOR_STMTS
                    else @ERROR_GL_ACCOUNT_TOO_LONG
                end
                exec @batErrorCode = spBATRecordError
                    @theBlankSessionID,
                    @Source,
                    @SourcePriKey,
                    @theErrorCodeID,
                    @theErrorObjectTable,
                    @theErrorObjectID
                
                set @secondGLNumber = null
            end
            else
            begin
                
                -- Pad the number with zeros
                exec @glAccount = udfLeftPadding @padLength, @glNumber
                
                -- If @secondGLNumber is not empty and it is not the last iteration of the loop, insert a dash (-) between numbers
                if( @glsCounter > 1 and @secondGLNumber <> ' ' )
                begin
                    set @secondGLNumber = ltrim( rtrim( @secondGLNumber ) ) + '-' + ltrim( rtrim( @glAccount ) )
                end
                -- Otherwise do not insert a dash (-) between numbers because it is either the last iteration or it is empty
                else
                begin
                    set @secondGLNumber = ltrim( rtrim( @secondGLNumber ) ) + ltrim( rtrim( @glAccount ) )
                end
            end
        end -- End of @glcSourceName = 'Natural'

        else if( @glcSourceName = 'Sub' )
        begin -- Start of @glcSourceName = Sub
            set @glNumber = ( select ltrim( rtrim( GLSubAccount ) ) from GLARAP where GLCPriKey = @glcPriKey and GLAPriKey = @glaPriKey )
            
            -- If there is no RevGLSubAccount, check @Source
            if( @glNumber is null )
            begin
                set @theErrorCodeID = case @theSourceIsStatements
                    when 1 then @ERROR_GL_SUBACCOUNT_IS_NULL_FOR_STMTS
                    else @ERROR_GL_SUBACCOUNT_IS_NULL
                end
                exec @batErrorCode = spBATRecordError
                    @theBlankSessionID,
                    @Source,
                    @SourcePriKey,
                    @theErrorCodeID,
                    @theErrorObjectTable,
                    @theErrorObjectID
                
                set @secondGLNumber = null
            end
            
            -- Else if the length of RevGLSubAccount is greater than @padLength, check @Source
            else if( len( rtrim( @glNumber ) ) > @padLength )
            begin
                set @theErrorCodeID = case @theSourceIsStatements
                    when 1 then @ERROR_SUBACCOUNT_TOO_LONG_FOR_STMTS
                    else @ERROR_SUBACCOUNT_TOO_LONG
                end
                exec @batErrorCode = spBATRecordError
                    @theBlankSessionID,
                    @Source,
                    @SourcePriKey,
                    @theErrorCodeID,
                    @theErrorObjectTable,
                    @theErrorObjectID

                set @secondGLNumber = null
            end
            else
            begin
                -- Pad the number with zeros
                exec @glAccount = udfLeftPadding @padLength, @glNumber
                
                -- If @secondGLNumber is not empty and it is not the last iteration of the loop, insert a dash (-) between numbers
                if( @glsCounter > 1 and @secondGLNumber <> ' ' )
                begin
                    set @secondGLNumber = ltrim( rtrim( @secondGLNumber ) ) + '-' + ltrim( rtrim( @glAccount ) )
                end
                -- Otherwise do not insert a dash (-) between numbers because it is either the last iteration or it is empty
                else
                begin
                    set @secondGLNumber = ltrim( rtrim( @secondGLNumber ) ) + 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, check @Source
            if( @glNumber is null )
            begin
                set @theErrorCodeID = case @theSourceIsStatements
                    when 1 then @ERROR_COMPANY_GLCODE_IS_NULL_FOR_STMTS
                    else @ERROR_COMPANY_GLCODE_IS_NULL
                end
                exec @batErrorCode = spBATRecordError
                    @theBlankSessionID,
                    @Source,
                    @SourcePriKey,
                    @theErrorCodeID,
                    @theErrorObjectTable,
                    @theErrorObjectID
                
                set @secondGLNumber = null
            end
            
            -- Else if the length of CompanyGLCode is greater than the @padLength, check @Source
            else if( len( rtrim( @glNumber ) ) > @padLength )
            begin
                set @theErrorCodeID = case @theSourceIsStatements
                    when 1 then @ERROR_COMPANY_GLCODE_IS_TOO_LONG_FOR_STMTS
                    else @ERROR_COMPANY_GLCODE_IS_TOO_LONG
                end
                exec @batErrorCode = spBATRecordError
                    @theBlankSessionID,
                    @Source,
                    @SourcePriKey,
                    @theErrorCodeID,
                    @theErrorObjectTable,
                    @theErrorObjectID
                
                set @secondGLNumber = null
            end
            
            -- Otherwise, pad @glCode and append it to @secondGlNumber
            else
            begin
                exec @glAccount = udfLeftPadding @padLength, @glNumber
                
                -- If @secondGLNumber is not empty and it is not the last iteration of the loop, insert a dash (-) between numbers
                if( @glsCounter > 1 and @secondGLNumber <> ' ' )
                begin
                    set @secondGLNumber = ltrim( rtrim( @secondGLNumber ) ) + '-' + ltrim( rtrim( @glAccount ) )
                end
                -- Otherwise do not insert a dash (-) between numbers because it is either the last iteration or it is empty
                else
                begin
                    set @secondGLNumber = ltrim( rtrim( @secondGLNumber ) ) + 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, check @Source
            if( @glNumber is null )
            begin
                set @theErrorCodeID = case @theSourceIsStatements
                    when 1 then @ERROR_GLCODE_IN_BRANCH_IS_NULL_FOR_STMTS
                    else @ERROR_GLCODE_IN_BRANCH_IS_NULL
                end
                exec @batErrorCode = spBATRecordError
                    @theBlankSessionID,
                    @Source,
                    @SourcePriKey,
                    @theErrorCodeID,
                    @theErrorObjectTable,
                    @theErrorObjectID
                
                set @secondGLNumber = null
            end
            
            -- Else if the length of GLCode is greater than the @padLength, check @Source
            else if( len( rtrim( @glNumber ) ) > @padLength )
            begin
                set @theErrorCodeID = case @theSourceIsStatements
                    when 1 then @ERROR_GLCODE_IN_BRANCH_IS_TOO_LONG_FOR_STMTS
                    else @ERROR_GLCODE_IN_BRANCH_IS_TOO_LONG
                end
                exec @batErrorCode = spBATRecordError
                    @theBlankSessionID,
                    @Source,
                    @SourcePriKey,
                    @theErrorCodeID,
                    @theErrorObjectTable,
                    @theErrorObjectID
                
                set @secondGLNumber = null
            end
            
            -- Otherwise pad GLCode with zeros and append it to @secondGLNumber
            else
            begin
                exec @glAccount = udfLeftPadding @padLength, @glNumber
                
                -- If @secondGLNumber is not empty and it is not the last iteration of the loop, insert a dash (-) between numbers
                if( @glsCounter > 1 and @secondGLNumber <> ' ' )
                begin
                    set @secondGLNumber = ltrim( rtrim( @secondGLNumber ) ) + '-' + ltrim( rtrim( @glAccount ) )
                end
                -- Otherwise do not insert a dash (-) between numbers because it is either the last iteration or it is empty
                else
                begin
                    set @secondGLNumber = ltrim( rtrim( @secondGLNumber ) ) + 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 @DivisionPriKey is not valid, check @Source
            if not exists ( select 1 from Division where DivisionID = @DivisionPriKey )
            begin
                set @theErrorCodeID = case @theSourceIsStatements
                    when 1 then @ERROR_DIVISION_IS_INVALID_FOR_STMTS
                    else @ERROR_DIVISION_IS_INVALID
                end
                exec @batErrorCode = spBATRecordError
                    @theBlankSessionID,
                    @Source,
                    @SourcePriKey,
                    @theErrorCodeID,
                    @theErrorObjectTable,
                    @theErrorObjectID
                
                set @secondGLNumber = null
            end
            set @glNumber = ( select ltrim( rtrim( GLCode ) ) from Division where DivisionID = @DivisionPriKey )
            
            -- If GLCode is not valid, check @Source
            if( @glNumber is null )
            begin
                set @theErrorCodeID = case @theSourceIsStatements
                    when 1 then @ERROR_GLCODE_IN_DIVISION_IS_NULL_FOR_STMTS
                    else @ERROR_GLCODE_IN_DIVISION_IS_NULL
                end
                exec @batErrorCode = spBATRecordError
                    @theBlankSessionID,
                    @Source,
                    @SourcePriKey,
                    @theErrorCodeID,
                    @theErrorObjectTable,
                    @theErrorObjectID
                
                set @secondGLNumber = null
            end
            
            -- Else if the length of GLCode is greater than the @padLength, check @Source
            else if( len( rtrim( @glNumber ) ) > @padLength )
            begin
                set @theErrorCodeID = case @theSourceIsStatements
                    when 1 then @ERROR_GLCODE_IN_DIVISION_IS_TOO_LONG_FOR_STMTS
                    else @ERROR_GLCODE_IN_DIVISION_IS_TOO_LONG
                end
                exec @batErrorCode = spBATRecordError
                    @theBlankSessionID,
                    @Source,
                    @SourcePriKey,
                    @theErrorCodeID,
                    @theErrorObjectTable,
                    @theErrorObjectID
                
                set @secondGLNumber = null
            end
            
            -- Otherwise, pad GLCode with zeros and append it to @secondGLNumber
            else
            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 @secondGLNumber <> ' ' )
                begin
                    set @secondGLNumber = ltrim( rtrim( @secondGLNumber ) ) + '-' + ltrim( rtrim( @glAccount ) )
                end
                -- Otherwise do not insert a dash (-) between numbers because it is either the last iteration or it is empty
                else
                begin
                    set @secondGLNumber = ltrim( rtrim( @secondGLNumber ) ) + ltrim( rtrim( @glAccount ) )
                end
            end
        end -- End of @glcSourceName = 'Division'
        
        -- Otherwise glSource not in (Natural, Sub, Company, Branch, or Division), check @Source
        else
        begin
            set @theErrorCodeID = case @theSourceIsStatements
                when 1 then @ERROR_GLSOURCE_IS_INVALID_FOR_STMTS
                else @ERROR_GLSOURCE_IS_INVALID
            end
            exec @batErrorCode = spBATRecordError
                @theBlankSessionID,
                @Source,
                @SourcePriKey,
                @theErrorCodeID,
                @theErrorObjectTable,
                @theErrorObjectID
            
            set @glsCounter = @countGLCSegmets + 1
            set @secondGLNumber = null
        end
        set @glsCounter = @glsCounter + 1


    end -- End of @glsCounter <= @countGLCSegmets
end -- End of vaild @glaPriKey

-- Otherwise @glaPriKey is not valid so set @secondGLNumber = null
else
begin
    set @secondGLNumber = null
end
-- Only if @secondGLNumber is not null update the record in BATProcess
if( @secondGLNumber is not null )
begin
    -- Validate @secondGLNumber
    exec @batErrorCode = spBATCheckGLNumber
            @theBlankSessionID,
            @secondGLNumber,
            @branchPriKey,
            @Source,
            @SourcePriKey,
            @theErrorObjectTable,
            @theErrorObjectID
    
    -- If @secondGLNumber is valid, update GLNumber in BATProcess
    if( @batErrorCode = 0 )
    begin
        update BATProcess set
            OpposingGLNumber = @secondGLNumber
        where BATProcessPriKey = @BATProcessPriKey
    end
    
    -- Otherwise return error code 742
    else
    begin
        exec @batErrorCode = spBATRecordError
            @theBlankSessionID,
            @Source,
            @SourcePriKey,
            @ERROR_SECOND_GL_NOT_IN_GREAT_PLAINS,
            @theErrorObjectTable,
            @theErrorObjectID
    end
end
GO
GRANT EXECUTE ON  [dbo].[spBATGenerateSecondRevGLNumber_Legacy] TO [MssExec]
GO