/**
    * Don't use this stored proc directly but instead use spBATGenerateFirstCommGLNumber_Synonym.
    * spBATGenerateFirstCommGLNumber_Synonym will either point to this stored proc or to
    * spBATGenerateFirstCommGLNumber_XLedger if that is active.
*
*
* Description: If parameters are valid, new GLNumbers is generated and error code 0 is returned. Error Codes:  
* 162 if there is more than 1 record found in ICCommissions with sysuser,
* 292 if there is no record found in ICCommisisons with sysuser
* 295 if we do not retrieve the labor type on the commission (programming error if that is the case)
* 163 if CommGLAccount is null when GLCSource = Natural
* 164 if length of CommGLAccount > length allowed for CommGLAccount when @glcSource = 'Natural',
* 165 if CommGLSubAccount is null when GLCSource = Sub
* 180 if length of CommGLSubAccount > length allowed for CommGLSubAccount when @glcSource = 'Sub'  
* 216 if CompanyGLCode in Branch is not valid for 'Company'
* 217 if length of CompanyGLCode > length allowed for CompanyGLCode when @glSource = 'Company'
* 218 if GLCode in Branch is not valid for 'Branch'
* 219 if length of GLCode > length allowed for GLCode when @glcSource = 'Branch'
* 220 if GLCode in Division is not valid when @glSource = 'Division')  
* 221 if length of GLCode in Division > length allowed for GLCode when @glcSource = 'Division'     
* 222 if GLSource not in (Natural, Sub, Company, Branch, Depatment)
* 239 if @DivisionID is not valid when @glSource = 'Division'      
* 294 if @Branch.GLCPriKey is not valid
* 682 if there is more than 1 record in GLSource with GLSourceName = 'Natural'
* 683 if there is more than 1 record in GLSource with GLSourceName = 'Sub'
* 684 if there is more than 1 record in GLSource with GLSourceName = 'Company'
* 685 if there is more than 1 record in GLSource with GLSourceName = 'Branch'
* 686 if there is more than 1 record in GLSource with GLSourceName = 'Division'
* 702 if @BranchPriKey is not valid
* 743 if @firstGLNumber is not in Great Plains
*/

CREATE PROCEDURE [dbo].[spBATGenerateFirstCommGLNumber_Legacy]
    @ICPriKey int,
    @BranchPriKey int,
    @DivisionID int,
    @OrdPriKey int,
    @CommissionSysuser int, -- SysuserPriKey for person getting a commission.
    @BATProcessPriKey int,
    @Source varchar(50),
    @SourcePriKey int
as

set nocount on

declare @theLaborTypeID int
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
declare @theItemCode int
declare @theCommissionableSysuserName varchar( 70 )
declare @theLaborTypeDesc varchar(20)
declare @theCommodity varchar( 26 )
declare @theAuthority varchar( 30 )
declare @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 = ' '

-- If @BranchPriKey is not valid, return error code 702
if not exists
(
    select 1
    from Branch
    where BranchPriKey = @BranchPriKey
)
begin
    exec @batErrorCode = spBATRecordError
        @theBlankSessionID,
        @Source,
        @SourcePriKey,
        702,
        '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( @Source = 'CommissionedDetail' )
begin
    set @theLaborTypeID = ( select LaborTypeFID from CommissionedDetail where CDPriKey = @SourcePriKey )
end
else if( @Source = 'SplitCommissions' )
begin
    set @theLaborTypeID = ( select LaborTypeFID from SplitCommissions where SCPriKey = @SourcePriKey )
end

-- If @theLaborTypeID is not vaild. return null for FirstGLNumber and error code 295.  For this error,
-- it probably means that a different @Source was used to where we could not determine the LaborTypeFID
-- or that the LaborType was not set on the source record.
if( @theLaborTypeID is null )
begin
    exec @batErrorCode = spBATRecordError
        @theBlankSessionID,
        @Source,
        @SourcePriKey,
        295,
        'Order',
        @OrdPriKey
        
    set @firstGLNumber = null
end


-- If @glcPriKey is not vaild. return null for FirstGLNumber and error code 294
if @glcPriKey is null
begin
    exec @batErrorCode = spBATRecordError
        @theBlankSessionID,
        @Source,
        @SourcePriKey,
        294,
        'Order',
        @OrdPriKey
        
    set @firstGLNumber = null
end
else -- Otherwise get the ICDPriKey
begin
    exec @icdPriKey = spBATSelectionProcess
        @ICPriKey,
        @glcPriKey,
        @OrdPriKey,
        'ICDetail',
        @Source,
        @SourcePriKey,
        null
end

-- If there is more than 1 record in ICCommissions with @icdPriKey, return null for FirstGLNumber and error code 162
if(    select count(*)
    from ICCommissions ic
    WHERE ic.ICDPriKey = @icdPriKey and ic.LaborType = @theLaborTypeID) > 1
begin
    exec @batErrorCode = spBATRecordError
        @theBlankSessionID,
        @Source,
        @SourcePriKey,
        162,
        'Order',
        @OrdPriKey
        
    set @firstGLNumber = null
end
-- Else if there is not a record in ICCommissions with @icdPriKey, return null for FirstGLNumber and error code 292
else if not exists
(
    select 1
    from ICCommissions ic
    where
    (
        ic.ICDPriKey = @icdPriKey and
        ic.LaborType = @theLaborTypeID
    )
)
begin
    -- Get the Item Code and commisionable persion for error code 292
    set @theItemCode =
    (
        select rtrim( str( ItemCode.ItemCode ) )
        from ItemCode
        where ItemCode.ICPRiKey = @ICPriKey
    )
    set @theLaborTypeDesc = isnull(
    (
        select LaborType
        from LaborType
        where LaborType.PriKey = @theLaborTypeID
    ), 'Missing Labor Type' )
    set @theCommissionableSysuserName =
    (
        select dbo.FormatLastNameFirstName( Sysuser.LASTNAME, Sysuser.FIRSTNAME ) + ' (' + @theLaborTypeDesc + ')'
        from Sysuser
        where Sysuser.SysUserID = @CommissionSysuser
    )
        
    -- Get the MoveType, Commodity, and Authority of the order for error code 292
    exec GetOrderAuthorityCommodityMoveType
        @inOrderID = @OrdPriKey,
        @outAuthority = @theAuthority output,
        @outCommodity = @theCommodity output,
        @outMoveType = @theMoveType output

    exec @batErrorCode = spBATRecordError
        @theBlankSessionID,
        @Source,
        @SourcePriKey,
        292,
        'Order',
        @OrdPriKey,
        @theItemCode,
        @theCommissionableSysuserName,
        @theMoveType,
        @theCommodity,
        @theAuthority
        
    set @firstGLNumber = null
end

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

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

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

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

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

    set @firstGLNumber = null
end

-- If there are no errors, check the GLSourceName of each record
if @batErrorCode = 0
begin  
    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( ic.CommGLAccount ) )
                from ICCommissions ic
                where
                    ic.ICDPriKey = @icdPriKey and
                    ic.LaborType = @theLaborTypeID
            )
            
            -- If CommGLAccount is null, return null for FirstGLNumber and error code 163
            if @glNumber is null
            begin
                exec @batErrorCode = spBATRecordError
                    @theBlankSessionID,
                    @Source,
                    @SourcePriKey,
                    163,
                    'Order',
                    @OrdPriKey
                    
                set @firstGLNumber = null
            end
            -- Else if the length of CommGLAccount is greater than the @padLength return null FirstGLNumber and error code 164
            else if len( rtrim( @glNumber ) ) > @padLength
            begin
                exec @batErrorCode = spBATRecordError
                    @theBlankSessionID,
                    @Source,
                    @SourcePriKey,
                    164,
                    '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
                else -- Otherwise a dash (-) is not needed
                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( ic.CommGLSubAccount ) )
                from ICCommissions ic
                where
                    ic.ICDPriKey = @icdPriKey and
                    ic.LaborType = @theLaborTypeID
            )
            
            -- If CommGLSubAccount is null, return null FirstGLNumber and error code 165
            if @glNumber is null
            begin
                exec @batErrorCode = spBATRecordError
                    @theBlankSessionID,
                    @Source,
                    @SourcePriKey,
                    165,
                    'Order',
                    @OrdPriKey
                
                set @firstGLNumber = null
            end
            -- Else if the length of CommGLNumber is greater than the @padLength return null FirstGLNumber and error code 180
            else if len( rtrim( @glNumber ) ) > @padLength
            begin
                exec @batErrorCode = spBATRecordError
                    @theBlankSessionID,
                    @Source,
                    @SourcePriKey,
                    180,
                    '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
                else -- Otherwise a dash (-) is not 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 216
            if @glNumber is null
            begin
                exec @batErrorCode = spBATRecordError
                    @theBlankSessionID,
                    @Source,
                    @SourcePriKey,
                    216,
                    'Order',
                    @OrdPriKey
                    
                set @firstGLNumber = null
            end
            
            -- Else if the length of CompanyGLCode is greater than the @padLength return null FirstGLNumber and error code 217
            else if len( rtrim( @glNumber ) ) > @padLength
            begin
                exec @batErrorCode = spBATRecordError
                    @theBlankSessionID,
                    @Source,
                    @SourcePriKey,
                    217,
                    'Order',
                    @OrdPriKey
                    
                set @firstGLNumber = null
            end
            else -- Otherwise check if @firstGLNumber needs to insert a dash (-).
            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
                else -- Otherwise a dash (-) is not needed
                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 218
            if @glNumber is null
            begin
                exec @batErrorCode = spBATRecordError
                    @theBlankSessionID,
                    @Source,
                    @SourcePriKey,
                    218,
                    'Order',
                    @OrdPriKey
                    
                set @firstGLNumber = null
            end
            -- Else if the length of GLCode is greater than the @padLength return null FirstGLNumber and error code 219
            else if len(rtrim(@glNumber)) > @padLength
            begin
                exec @batErrorCode = spBATRecordError
                    @theBlankSessionID,
                    @Source,
                    @SourcePriKey,
                    219,
                    '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 a dash (-) is not 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 239
            if not exists
            (
                select DivisionID
                from Division
                where DivisionID = @DivisionID
            )
            begin
                exec @batErrorCode = spBATRecordError
                    @theBlankSessionID,
                    @Source,
                    @SourcePriKey,
                    239,
                    'Order',
                    @OrdPriKey
                    
                set @firstGLNumber = null
            end
            else -- Otherwise get the GLCode
            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 220
                if @glNumber is null
                begin
                    exec @batErrorCode = spBATRecordError
                        @theBlankSessionID,
                        @Source,
                        @SourcePriKey,
                        220,
                        'Order',
                        @OrdPriKey
                        
                    set @firstGLNumber = null
                end
                
                -- Else if the length of GLCode is greater than the @padLength return null FirstGLNumber and error code 221
                else if len( rtrim( @glNumber ) ) > @padLength
                begin
                    exec @batErrorCode = spBATRecordError
                        @theBlankSessionID,
                        @Source,
                        @SourcePriKey,
                        221,
                        'Order',
                        @OrdPriKey
                        
                    set @firstGLNumber = null
                end
                else -- Otherwise check if @firstGLNumber needs to insert a dash (-).
                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
                    else -- Otherwise a dash (-) is not needed
                    begin
                        set @firstGLNumber = ltrim( rtrim( @firstGLNumber ) ) + ltrim( rtrim( @glAccount ) )
                    end
                end -- End of padding number
            end -- End of valid @DivisionID
        end -- End of @glcSourceName = 'Division'
        -- Otherwise return null for FirstGLNumber and error code 222 because @glSource not in (Natural, Sub, Company, Branch, Division)
        else
        begin
            exec @batErrorCode = spBATRecordError
                @theBlankSessionID,
                @Source,
                @SourcePriKey,
                222,
                'Order',
                @OrdPriKey
                
            set @glsCounter = @countGLCSegmets + 1
            set @firstGLNumber = null
        end
        set @glsCounter = @glsCounter + 1
    end -- End of @glsCounter <= @countGLCSegmets
end

-- 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
    else -- Otherwise return error code 743
    begin
        exec @batErrorCode = spBATRecordError
            @theBlankSessionID,
            @Source,
            @SourcePriKey,
            743,
            'Order',
            @OrdPriKey
    end
end
GO
GRANT EXECUTE ON  [dbo].[spBATGenerateFirstCommGLNumber_Legacy] TO [MssExec]
GO