SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/*
*    Don't use this stored proc directly but instead use spBATGenerateFirstThirdPartyGLNumber_Synonym.
*    spBATGenerateFirstThirdPartyGLNumber_Synonym will either point to this stored proc or to
*    spBATGenerateFirstThirdPartyGLNumber_XLedger if that is active.
*
*
*    Description: If parameters are valid, new GLNumbers is generated and error code 0 is returned. Error Codes:
*    204 for invalid @glcPriKey for @BranchPriKey,
*    205 if GLAccount is null for @glaPriKey
*    206 if length of GLAccount > length allowed for RevGLAccount when @glSource = 'Natural',
*    207 if GLSubAccount is null for @glaPriKey
*    208 if length of GLSubAccount > length allowed for RevGLAccount when @glSource = 'Sub'  
*    209 if CompanyGLCode in Branch is not valid for 'Company'
*    210 if length of CompanyGLCode > length allowed for CompanyGLCode when @glSource = 'Company'
*    211 if GLCode in Branch is not valid for 'Branch'
*    212 if length of GLCode > length allowed for GLCode when @glSource = 'Branch'
*    213 if GLCode in Division is not valid when @glSource = 'Division'
*    214 if length of GLCode > length allowed for GLCode when @glSource = 'Division'
*    215 if GLSource not in ("Natural", "Sub", "Company", Branch", "Division")        
*    262 if @DivisionPriKey is not valid when @glSource = 'Division'
*    714 if there is more than 1 record in GLSource with SourceName = 'Natural'
*    715 if there is more than 1 record in GLSource with GLSourceName = 'Sub'
*    716 if there is more than 1 record in GLSource with GLSourceName = 'Company'
*    717 if there is more than 1 record in GLSource with GLSourceName = 'Branch'
*    718 if there is more than 1 record in GLSource with GLSourceName = 'Division
*    719 if @BranchPriKey is not valid
*    720 if ManualGL = 1 and OverrideGLNumber is not valid
*    746 if @secondGLNumber is not in Great Plains
*/

CREATE PROCEDURE [dbo].[spBATGenerateSecondThirdPartyGLNumber_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 719
IF( not exists (SELECT * FROM Branch WHERE BranchPriKey = @BranchPriKey) )
BEGIN
    EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 719, '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 714
IF( (SELECT Count(*) FROM GLSource WHERE SourceName = 'Natural') > 1 )
BEGIN
    EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 714, 'Order', @OrdPriKey
    SET @secondGLNumber = null
END

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

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

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

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

-- If @glcPriKey is not vaild. return null for @secondGLNumber and error code 204
IF( @glcPriKey is null )
BEGIN
    EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 204, 'Order', @OrdPriKey
    SET @secondGLNumber = null
END

-- Otherwise, get 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 720
    IF @secondGLNumber is null
    BEGIN
        EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 720, 'Order', @OrdPriKey
        SET @secondGLNumber = null
    END
END -- End of ManualGL

-- Otherwise, generate the 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 gs.SourceName
            FROM GLSource gs
            Inner Join GLDetail gd on (gd.GLSPriKey = gs.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 205
            IF( @glNumber is null )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 205, 'Order', @OrdPriKey
                SET @secondGLNumber = null
            END

            -- Else if the length of GLAccount is greater than the @padLength return null @secondGLNumber and error code 206
            ELSE IF( Len(Rtrim(@glNumber)) > @padLength )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 206, '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 FirstGLNumber and error code 207
            IF( @glNumber is null )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 207, 'Order', @OrdPriKey
                SET @secondGLNumber = null
            END

            -- Else if the length of GLSubAccount is greater than the @padLength return null @secondGLNumber and error code 208
            ELSE IF Len(Rtrim(@glNumber)) > @padLength
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 208, '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 209
            IF( @glNumber is null )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 209, 'Order', @OrdPriKey
                SET @secondGLNumber = null
            END

            -- Else if the length of CompanyGLCode is greater than the @padLength return null @secondGLNumber and error code 210
            ELSE IF( Len(Rtrim(@glNumber)) > @padLength )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 210, '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 FirstGLNumber and error code 211
            IF( @glNumber is null )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 211, 'Order', @OrdPriKey
                SET @secondGLNumber = null
            END

            -- Else if the length of GLCode is greater than the @padLength return null @secondGLNumber and error code 212
            ELSE IF( Len(Rtrim(@glNumber)) > @padLength )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 212, '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 262
            IF (SELECT DivisionID FROM Division WHERE DivisionID = @DivisionPriKey) is null
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 262, '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 213
            IF( @glNumber is null )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 213, 'Order', @OrdPriKey
                SET @secondGLNumber = null
            END

            -- Else if the length of GLCode is greater than the @padLength return null @secondGLNumber and error code 214
            ELSE IF( Len(Rtrim(@glNumber)) > @padLength )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 214, '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 FirstGLNumber and error code 215 because @glSource not in (Natural, Sub, Company, Branch, Division)
        ELSE
        BEGIN
            EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 215, '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 746
    ELSE
    begin
        EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 746, 'Order', @OrdPriKey
    end
END
GO
GRANT EXECUTE ON  [dbo].[spBATGenerateSecondThirdPartyGLNumber_Legacy] TO [MssExec]
GO