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:
*  ============================================================================================
*    192 if GLCPriKey is not valid for @BranchPriKey
*    193 if PayGLAccount is null for @icdPriKey
*    194 if length of PayGLAccount > length allowed for PayGLAccount when @glSource = 'Natural'
*    195 if PayGLSubAccount is null for @icdPriKey
*    196 if length of PayGLSubAccount > length allowed for PayGLSubAccount when @glSource = 'Sub'
*    197 if CompanyGLCode in Branch is not valid for 'Company'
*    198 if length of CompanyGLCode > length allowed for CompanyGLCode when @glSource = 'Company'
*    199 if GLCode in Branch is not valid for 'Branch'
*    200 if length of GLCode > length allowed for GLCode when @glSource = 'Branch'
*    201 if GLCode in Division is not valid when @glSource = 'Division'
*    202 if length of GLCode > length allowed for GLCode when @glSource = 'Division'
*    203 if GLSource not in ("Natural", "Sub", "Company", Branch", "Division")
*    241 if @DivisionID is not valid when @glSource = 'Division'
*    692 if there is more than 1 record in GLSource with SourceName = 'Natural'
*    693 if there is more than 1 record in GLSource with GLSourceName = 'Sub'
*    694 if there is more than 1 record in GLSource with GLSourceName = 'Company'
*    695 if there is more than 1 record in GLSource with GLSourceName = 'Branch'
*    696 if there is more than 1 record in GLSource with GLSourceName = 'Division
*    704 if @BranchPriKey is not valid
*    745 if @firstGLNumber is not in Great Plains
*/

CREATE PROCEDURE [dbo].[spBATGenerateFirstThirdPartyGLNumber_Legacy]
    @ICPriKey int,
    @BranchPriKey int,
    @DivisionID 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),
    @firstGLNumber varchar(66),
    @batErrorCode int,
    @icdPriKey int,
    @theItemCode int,
    @theCommodity varchar( 26 ),
    @theAuthority varchar( 30 ),
    @theMoveType varchar( 15 )

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

SET @batErrorCode = 0
SET @glsCounter = 1
SET @firstGLNumber = ' '
set @theItemCode = ( select rtrim( str( ItemCode.ItemCode ) ) from ItemCode where ItemCode.ICPRiKey = @ICPriKey )

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

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

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

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

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

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

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

-- If there were no errors, build the GL Number
IF( @batErrorCode = 0 )
BEGIN -- Start of @icdPriKey <> 0
    WHILE( @glsCounter <= @countGLCSegmets )
    BEGIN -- Begin of @glsCounter <= @countGLCSegmets
        SET @padLength = (SELECT SegmentLength FROM GLDetail WHERE SegmentNumber = @glsCounter and GLCPriKey = @glcPriKey)
        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
        )
        IF( @glcSourceName = 'Natural' )
        BEGIN -- Start of @glcSourceName = 'Natural'

            SET @glNumber = (SELECT Ltrim(Rtrim(PayGLAccount)) FROM ICDetail WHERE ICDPriKey = @icdPriKey)

            -- If PayGLAccount is not valid, return null for FirstGLNumber and error code 193
            IF( @glNumber is null )
            BEGIN
                -- Get the Item Code being checked and the MoveType, Commodity, and Authority of the order for error code 143
                exec GetOrderAuthorityCommodityMoveType
                    @inOrderID = @OrdPriKey,
                    @outAuthority = @theAuthority output,
                    @outCommodity = @theCommodity output,
                    @outMoveType = @theMoveType output

                exec @batErrorCode =
                    spBATRecordError
                    @theBlankSessionID,
                    @Source,
                    @SourcePriKey,
                    193,
                    'Order',
                    @OrdPriKey,
                    @theItemCode,
                    @theMoveType,
                    @theCommodity,
                    @theAuthority
                    
                SET @firstGLNumber = null
            END

            -- Else if the length of PayGLAccount is greater than the @padLength return null FirstGLNumber and error code 194
            ELSE IF( Len(Rtrim(@glNumber)) > @padLength )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 194, 'Order', @OrdPriKey
                SET @firstGLNumber = null
            END

            -- Otherwise check if @firstGLNumber needs to insert a dash (-).
            ELSE
            BEGIN -- Start of padding number
                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

                -- Otherwise no dash is needed
                ELSE
                begin
                    SET @firstGLNumber = Ltrim(Rtrim(@firstGLNumber)) + 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(PayGLSubAccount)) FROM ICDetail WHERE ICDPriKey = @icdPriKey)

            -- If PayGLSubAccount is not valid, return null FirstGLNumber and error code 195
            IF( @glNumber is null )
            BEGIN
                -- Get the Item Code being checked and the MoveType, Commodity, and Authority of the order for error code 143
                exec GetOrderAuthorityCommodityMoveType
                    @inOrderID = @OrdPriKey,
                    @outAuthority = @theAuthority output,
                    @outCommodity = @theCommodity output,
                    @outMoveType = @theMoveType output
                    
                EXEC @batErrorCode =
                    spBATRecordError
                    @theBlankSessionID,
                    @Source,
                    @SourcePriKey,
                    195,
                    'Order',
                    @OrdPriKey,
                    @theItemCode,
                    @theMoveType,
                    @theCommodity,
                    @theAuthority

                SET @firstGLNumber = null
            END

            -- Else if the length of PayGLSubAccount is greater than the @padLength return null FirstGLNumber and error code 196
            ELSE IF( Len(Rtrim(@glNumber)) > @padLength )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 196, 'Order', @OrdPriKey
                SET @firstGLNumber = null
            END

            -- Otherwise check if @firstGLNumber needs to insert a dash (-).
            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 @firstGLNumber <> ' ' )
                begin
                    SET @firstGLNumber = Ltrim(Rtrim(@firstGLNumber))  + '-' + Ltrim(Rtrim(@glAccount))
                end

                -- Otherwise no dash is needed
                ELSE
                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 CompanyGLCode FROM Branch WHERE BranchPriKey = @BranchPriKey)

            -- If CompanyGLCode is not valid, return null FirstGLNumber and error code 197
            IF( @glNumber is null )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 197, 'Order', @OrdPriKey
                SET @firstGLNumber = null
            END

            -- Else if the length of CompanyGLCode is greater than the @padLength return null FirstGLNumber and error code 198
            ELSE IF( Len(Rtrim(@glNumber)) > @padLength )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 198, 'Order', @OrdPriKey
                SET @firstGLNumber = null
            END

            -- Otherwise check if @firstGLNumber needs to insert a dash (-).
            ELSE
            BEGIN -- Start of padding number
                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

                -- Otherwise no dash is needed
                ELSE
                begin
                    SET @firstGLNumber = Ltrim(Rtrim(@firstGLNumber)) + 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 199
            IF( @glNumber is null )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 199, 'Order', @OrdPriKey
                SET @firstGLNumber = null
            END

            -- Else if the length of GLCode is greater than the @padLength return null FirstGLNumber and error code 200
            ELSE IF( Len(Rtrim(@glNumber)) > @padLength )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 200, 'Order', @OrdPriKey
                SET @firstGLNumber = null
            END

            -- Otherwise check if @firstGLNumber needs to insert a dash (-).
            ELSE
            BEGIN -- Start of padding number
                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

                -- Otherwise no dash is needed
                ELSE
                begin
                    SET @firstGLNumber = Ltrim(Rtrim(@firstGLNumber)) + 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 @DivisionID in not valid return error code 241
            IF( not exists (SELECT * FROM Division WHERE DivisionID = @DivisionID) )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 241, 'Order', @OrdPriKey
                SET @firstGLNumber = null
            END
            SET @glNumber = (SELECT Ltrim(Rtrim(GLCode)) FROM Division WHERE DivisionID = @DivisionID)

            -- If GLCode is not valid, return null FirstGLNumber and error code 201
            IF( @glNumber is null )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 201, 'Order', @OrdPriKey
                SET @firstGLNumber = null
            END

            -- Else if the length of GLCode is greater than the @padLength return null FirstGLNumber and error code 202
            ELSE IF( Len(Rtrim(@glNumber)) > @padLength )
            BEGIN
                EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 202, 'Order', @OrdPriKey
                SET @firstGLNumber = null
            END

            -- Otherwise check if @firstGLNumber needs to insert a dash (-).
            ELSE
            BEGIN -- Start of padding number
                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

                -- Otherwise no dash is needed
                ELSE
                begin
                    SET @firstGLNumber = Ltrim(Rtrim(@firstGLNumber)) + Ltrim(Rtrim(@glAccount))
                end
            END -- End of padding number
        END -- End of @glcSourceName = 'Division'

        -- Otherwise return null for FirstGLNumber and error code 203 because @glSource not in (Natural, Sub, Company, Branch, Division)
        ELSE
        BEGIN
            EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 203, 'Order', @OrdPriKey
            SET @glsCounter = @countGLCSegmets + 1
            SET @firstGLNumber = null
        END
        SET @glsCounter = @glsCounter + 1
    END -- End of @glsCounter <= @countGLCSegmets
END -- Start of @icdPriKey <> 0

-- Only if @firstGLNumber is not null update the record in BATProcess
IF( @firstGLNumber is not null )
BEGIN

    -- Validate @firstGLNumber
    EXEC @batErrorCode =
        spBATCheckGLNumber
            @theBlankSessionID,
            @firstGLNumber,
            @branchPriKey,
            @Source,
            @SourcePriKey,
            'Order',
            @OrdPriKey

    -- If @firstGLNumber is valid, update GLNumber in BATProcess
    IF( @batErrorCode = 0 )
    begin
        UPDATE BATProcess set
            GLNumber = @firstGLNumber
        WHERE BATProcessPriKey = @BATProcessPriKey
    end
    -- Otherwise return error code 745
    ELSE
    begin
        EXEC @batErrorCode = spBATRecordError @theBlankSessionID, @Source, @SourcePriKey, 745, 'Order', @OrdPriKey, @firstGLNumber, @theItemCode
    end
  END
GO
GRANT EXECUTE ON  [dbo].[spBATGenerateFirstThirdPartyGLNumber_Legacy] TO [MssExec]
GO