SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/*
*    Don't use this stored proc directly but instead use spBATGenerateSecondCommGLNumber_Synonym.
*    spBATGenerateSecondCommGLNumber_Synonym will either point to this stored proc or to
*    spBATGenerateSecondCommGLNumber_XLedger if that is active.
*
*
*    Description: If parameters are valid, new GLNumbers is generated and error code 0 is returned.
*
*    Error Codes:
*    181 if GLAccount is null when GLCSource = Natural
*    182 if length of GLAccount > length allowed for GLAccount when @glcSource = 'Natural',
*    183 if GLSubAccount is null when GLCSource = Sub
*    184 if length of GLSubAccount > length allowed for GLSubAccount when @glcSource = 'Sub'  
*    185 if CompanyGLCode in Branch is not valid for 'Company'
*    186 if length of CompanyGLCode > length allowed for CompanyGLCode when @glSource = 'Company'
*    187 if GLCode in Branch is not valid for 'Branch'
*    188 if length of GLCode > length allowed for GLCode when @glcSource = 'Branch'
*    189 if GLCode in Division is not valid when @glSource = 'Division')  
*    190 if length of GLCode in Division > length allowed for GLCode when @glcSource = 'Division'   
*    191 if GLCSource not in (Natural, Sub, Company, Branch, Division.)
*    240 if @DivisionPriKey is not valid when @glSource = Division
*    293 if @Branch.GLCPrikey is not valid
*    697 if there is more than 1 record in GLSource with SourceName = 'Natural'
*    698 if there is more than 1 record in GLSource with GLSourceName = 'Sub'
*    699 if there is more than 1 record in GLSource with GLSourceName = 'Company'
*    700 if there is more than 1 record in GLSource with GLSourceName = 'Branch'
*    701 if there is more than 1 record in GLSource with GLSourceName = 'Division
*    705 if @BranchPriKey is not valid
*    706 if ManualGL = 1 and OverrideGLNumber is not valid
*    744 if @secondGLNumber is not in Great Plains
*/

CREATE PROCEDURE [dbo].[spBATGenerateSecondCommGLNumber_Legacy]
    @ICPriKey int,
    @BranchPriKey int,
    @DivisionPriKey int,
    @OrdPriKey int,
    @BATProcessPriKey int,
    @Source varchar(50),
    @SourcePriKey int
AS

SET NOCOUNT ON

DECLARE
    @glNumber varchar(50),
    @glcPriKey int,
    @countGLCSegmets int,
    @glcSourceName varchar(10),
    @glsCounter int,
    @padLength int,
    @glAccount varchar(100),
    @secondGLNumber varchar(66),
    @batErrorCode int,
    @glaPriKey int

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 = ''

-- If @BranchPrikey is not valid, return error code 705
IF( not exists (SELECT * FROM Branch WHERE BranchPriKey = @BranchPriKey) )
BEGIN
    EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 705, 'Order', @OrdPriKey
    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 697
IF( (SELECT Count(*) FROM GLSource WHERE SourceName = 'Natural') > 1 )
BEGIN
    EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 697, 'Order', @OrdPriKey
    SET @secondGLNumber = null
END

-- If there is more than 1 GLSourceName of "Sub" in GLSource, return error code 698
IF( (SELECT Count(*) FROM GLSource WHERE SourceName = 'Sub') > 1 )
BEGIN
    EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 698, 'Order', @OrdPriKey
    SET @secondGLNumber = null
END

-- If there is more than 1 GLSourceName of "Company" in GLSource, return error code 699
IF( (SELECT Count(*) FROM GLSource WHERE SourceName = 'Company') > 1 )
BEGIN
    EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 699, 'Order', @OrdPriKey
    SET @secondGLNumber = null
END

-- If there is more than 1 GLSourceName of "Branch" in GLSource, return error code 700
IF( (SELECT Count(*) FROM GLSource WHERE SourceName = 'Branch') > 1 )
BEGIN
    EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 700, 'Order', @OrdPriKey
    SET @secondGLNumber = null
END

-- If there is more than 1 GLSourceName of "Division" in GLSource, return error code 701
IF( (SELECT Count(*) FROM GLSource WHERE SourceName = 'Division') > 1 )
BEGIN
    EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 701, 'Order', @OrdPriKey
    SET @secondGLNumber = null
END

-- If @glcPriKey is not vaild. return null for FirstGLNumber and error code 293
IF( @glcPriKey is null )
BEGIN
    EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 293, 'Order', @OrdPriKey
    SET @secondGLNumber = null
END
-- Otherwise get the GLAPriKey for the record that matches the selection process
ELSE
BEGIN
    EXEC @glaPriKey = spBATSelectionProcess @ICPriKey, @glcPriKey, @OrdPriKey, 'GLARAP', @Source, @SourcePriKey, 'AP'
end

-- If ManualGL = 1, return the OverrideGLNumber
IF( (SELECT IsNull(ManualGL, 0) FROM GLARAP WHERE GLAPriKey = @glaPriKey) = 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 706
    IF( @secondGLNumber is null )
    BEGIN
        EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 706, 'Order', @OrdPriKey
        SET @secondGLNumber = null
    END
END -- End of ManualGL

-- Otherwise @glaPriKey is valid so generate the second Comm GL Number
ELSE
BEGIN
    WHILE( @glsCounter <= @countGLCSegmets )
    BEGIN -- Begin of @glsCounter <= @countGLCSegmets
        SET @padLength = (SELECT SegmentLength FROM GLDetail WHERE SegmentNumber = @glsCounter and GLCPriKey = @glcPriKey)
        SET @glcSourceName = (
            SELECT g.SourceName
            FROM GLSource g
            Inner Join GLDetail gd on (gd.GLSPriKey = g.GLSPriKey)
            WHERE gd.GLCPriKey = @glcPriKey and gd.SegmentNumber = @glsCounter )
        IF( @glcSourceName = 'Natural' )
        BEGIN -- Start of @glcSourceName = 'Natural'
            SET @glNumber = (SELECT Ltrim(Rtrim(GLAccount)) FROM GLARAP WHERE GLAPriKey = @glaPriKey)
            
            -- If GLAccount is not valid, return null for @secondGLNumber and error code 181
            IF( @glNumber is null )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 181, 'Order', @OrdPriKey
                SET @secondGLNumber = null
            END

            -- Else if the length of GLAccount is greater than the @padLength return null @secondGLNumber and error code 182
            ELSE IF( Len(Rtrim(@glNumber)) > @padLength )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 182, 'Order', @OrdPriKey
                SET @secondGLNumber = null
            END
            
            -- Otherwise check if @secondGLNumber needs to insert a dash (-).
            ELSE
            BEGIN -- Start of padding number
                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 of padding number
        END -- End of @glcSourceName = Natural

        ELSE IF( @glcSourceName = 'Sub' )
        BEGIN -- Start of @glcSourceName = Sub
            SET @glNumber = (SELECT Ltrim(Rtrim(GLSubAccount)) FROM GLARAP WHERE GLAPriKey = @glaPriKey)
            
            -- If GLSubAccount is not valid, return null @secondGLNumber and error code 183
            IF( @glNumber is null )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 183, 'Order', @OrdPriKey
                SET @secondGLNumber = null
            END
            
            -- Else if the length of GLSubAccount is greater than the @padLength return null @secondGLNumber and error code 184
            ELSE IF( Len(Rtrim(@glNumber)) > @padLength )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 184, 'Order', @OrdPriKey
                SET @secondGLNumber = null
            END
            
            -- Otherwise check if @secondGLNumber needs to insert a dash (-).
            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 = 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 @secondGLNumber and error code 185
            IF( @glNumber is null )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 185, 'Order', @OrdPriKey
                SET @secondGLNumber = null
            END
            
            -- Else if the length of CompanyGLCode is greater than the @padLength return null @secondGLNumber and error code 186
            ELSE IF( Len(Rtrim(@glNumber)) > @padLength )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 186, 'Order', @OrdPriKey
                SET @secondGLNumber = null
            END
            
            -- Otherwise check if @secondGLNumber needs to insert a dash (-).
            ELSE
            BEGIN -- Start of padding number
                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 of padding number
        END -- @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 @secondGLNumber and error code 187
            IF( @glNumber is null )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 187, 'Order', @OrdPriKey
                SET @secondGLNumber = null
            END
            
            -- Else if the length of GLCode is greater than the @padLength return null @secondGLNumber and error code 188
            ELSE IF( Len(Rtrim(@glNumber)) > @padLength )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 188, 'Order', @OrdPriKey
                SET @secondGLNumber = null
            END
            
            -- Otherwise check if @secondGLNumber needs to insert a dash (-).
            ELSE
            BEGIN -- Start of padding number
                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 of padding number
        END -- End of @glcSourceName = 'Branch'

        -- Else if @glcSourceName = "Division, begin selection process.
        ELSE IF( @glcSourceName = 'Division' )
        BEGIN -- Start of @glcSourceName = 'Division'
            
            -- If @DivisionPriKey in not valid return error code 240
            IF( (SELECT DivisionID FROM Division WHERE DivisionID = @DivisionPriKey) is null )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 240, 'Order', @OrdPriKey
                SET @secondGLNumber = null
            END
            SET @glNumber = (SELECT Ltrim(Rtrim(GLCode)) FROM Division WHERE DivisionID = @DivisionPriKey)
            
            -- If GLCode is not valid, return null @secondGLNumber and error code 189
            IF( @glNumber is null )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 189, 'Order', @OrdPriKey
                SET @secondGLNumber = null
            END
            
            -- Else if the length of GLCode is greater than the @padLength return null @secondGLNumber and error code 190
            ELSE IF( Len(Rtrim(@glNumber)) > @padLength )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 190, 'Order', @OrdPriKey
                SET @secondGLNumber = null
            END
            
            -- Otherwise check if @secondGLNumber needs to insert a dash (-).
            ELSE
            BEGIN -- Start of padding number
                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 of padding number
        END -- End of @glcSourceName = 'Division'

        -- Otherwise return null for @secondGLNumber and error code 191 because @glSource not in (Natural, Sub, Company, Branch, Division)
        ELSE
        BEGIN
            EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 191, 'Order', @OrdPriKey
            SET @glsCounter = @countGLCSegmets + 1
            SET @secondGLNumber = null
        END
        SET @glsCounter = @glsCounter + 1
    END -- End of @glsCounter <= @countGLCSegmets
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,
            'Order',
            @OrdPriKey

    -- 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 744
    ELSE
    begin
        EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 744, 'Order', @OrdPriKey
    end
END
GO
GRANT EXECUTE ON  [dbo].[spBATGenerateSecondCommGLNumber_Legacy] TO [MssExec]
GO