SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/**
* Don't use this stored proc directly but instead use spStorageGenerateGLNumbers_Synonym.
* spStorageGenerateGLNumbers_Synonym will either point to this stored proc or to
* spStorageGenerateGLNumbers_XLedger if that is active.
*
*
*  Updates a BATProcess record with the correct GLNumber and OpposingGLNumber for storage records.
*
*    Errors:
*    491 If @OrdPriKey is not valid
*    492 If @BranchPriKey is not valid
*    942 The Division primary key is not valid
*    954 If the deferred account from AcctTransactions for a storage deferral record is not valid, enhancement for generic error code
*    1008 If @StorageType is not "Deferred", "Revenue", or "Commission"
*    1020 if the revenue account for a deferred storage record is invalid
*    1031 Invalid revenue account gl number
*    1032 Invalid deferred account gl number
*    1033 Invalid AR account gl number
*    1035 Invalid expense account for a commission
*/

CREATE PROCEDURE [dbo].[spStorageGenerateGLNumbers_Legacy]
    @StorageType varchar(20),
    @BATProcessPriKey int,
    @SessionID varchar(50),
    @inAuthorityID int,
    @inStorageItemsID int,
    @ErrorCode int output
as
set nocount on

declare
    @branchPriKey int,
    @DivisionPriKey int,
    @ordPriKey int,
    @source varchar(50),
    @sourcePriKey int,
    @firstGLNumber varchar(66),
    @countGLCSegmets int,
    @glsCounter int,
    @padLength int,
    @glaPriKey int,
    @icDetailPriKey int,
    @icPriKey int,
    @commissionSysuser int,
    @transType varchar(2),
    @isDeferred smallint,
    @commodityPriKey int,
    @mtGroupPriKey int,
    @dtPriKey int,
    @authority varchar(30),
    @theErrorFlag bit

set @theErrorFlag = 0
set @ErrorCode = 0
set @isDeferred = 0
set @ordPriKey = ( select OrdPriKey from BATProcess where BATProcessPriKey = @BATProcessPriKey )
set @commodityPriKey = ( select Commodity from Orders where PriKey = @ordPriKey )
set @mtGroupPriKey =
(
    select mt.MTGroupPriKey
    from MoveType mt
    inner join Orders o on (o.MoveType = mt.PriKey)
    where o.PriKey = @ordPriKey
)
set @dtPriKey =
(
    select s.DTPriKey
    from Sysuser s
    inner join Trips t on (t.DriverEmpNo = s.SysuserID)
    inner join TripOrd tor on (tor.TripPriKey = t.PriKey)
    where tor.OrdPriKey = @ordPriKey
)

-- The Branch primary key here comes from the Warehouse selected for the Billing Item
set @branchPriKey =
(
    select Branch.BranchPriKey
    from Branch
    inner join BATProcess on BATProcess.BranchID = Branch.BranchID
    where BATProcess.BATProcessPriKey = @BATProcessPriKey
)

set @source = ( select Source from BATProcess where BATProcessPriKey = @BATProcessPriKey)
set @sourcePriKey = (select SourceRecord from BATProcess where BATProcessPriKey = @BATProcessPriKey )

-- Get the Division primary key from the appropriate source
set @DivisionPriKey =
    case @source
        when 'BillingMinorItem' then ( select DivisionFID from BillingMinorItem where BMinPriKey = @sourcePriKey )
        when 'CommissionedDetail' then
        (
            select BillingMinorItem.DivisionFID
            from BillingMinorItem
            inner join CommissionedDetail on CommissionedDetail.BMinPriKey = BillingMinorItem.BMinPriKey
            where CommissionedDetail.CDPriKey = @sourcePriKey
        )
        else
        (
            select BillingMinorItem.DivisionFID
            from BillingMinorItem
            inner join StorageDeferral on StorageDeferral.BMinPriKey = BillingMinorItem.BMinPriKey
            inner join BATProcess on BATProcess.SourceRecord = StorageDeferral.DeferralPriKey
            where BATProcessPriKey = @BATProcessPriKey
        )
    end

-- Get the Authority description
set @authority =
(
    select Description
    from AuthorityTypes
    where AuthPriKey = @inAuthorityID
)

-- If the order is not valid, return error code 491
if not exists ( select * from Orders where PriKey = @ordPriKey )
begin
    exec spStorageRecordError @SessionID, @OrdPriKey, 491
    set @ErrorCode = 1
    return
end

-- If the branch is not valid, return error code 492
if not exists( select * from Branch where BranchPriKey = @branchPriKey )
begin
    exec spStorageRecordError @SessionID, @OrdPriKey, 492
    set @ErrorCode = 1
    return
end

-- If @StorageType is not "Deferred", "Revenue", or "Commission", return error code 1008
if @StorageType not in ('Deferred', 'Revenue', 'Commission')
begin
    exec spStorageRecordError @SessionID, @OrdPriKey, 1008
    set @ErrorCode = 1
    return
end

set @icPriKey =
    case @source
        when 'BillingMinorItem' then ( select ICPriKey from BillingMinorItem where BMinPriKey = @sourcePriKey )
        when 'CommissionedDetail' then
        (
            select b.ICPriKey
            from BillingMinorItem b
            inner join CommissionedDetail c on ( c.BMinPriKey = b.BMinPriKey )
            where c.CDPriKey = @sourcePriKey
        )
        when 'StorageDeferral' then
        (
            select b.ICPriKey
            from BillingMinorItem b
            inner join StorageDeferral s on ( s.BMinPriKey = b.BMinPriKey )
            where s.DeferralPriKey = @sourcePriKey
        )
    end

-- 'Deferred' is used for both inbound and outbound Deferred, differenciated based on the Processed date (null means inbound).
-- If the storage type is inbound, set GLNumber to the storage account, and the OpposingGLNumber to the deferred account.
-- If the storage type is outbound, set GLNumber to the deferred account, and the OpposingGLNumber to the storage account.
if @StorageType = 'Deferred'
begin -- Start of @StorageType = 'Deferred'
    declare @theIsInboundDeferral bit
    set @theIsInboundDeferral =
    (
        select
        case
            when Processed is null then 1
            else 0
        end
        from StorageDeferral
        where DeferralPriKey = @sourcePriKey
    )

    -- Calculate the deferred GL account
    exec RecurringBillingBuildGLNumber
        @SessionID,
        @ordPriKey,
        @branchPriKey,
        'Deferred',
        @DivisionPriKey,
        @icPriKey,
        null,
        @inAuthorityID,
        @commodityPriKey,
        @mtGroupPriKey,
        @dtPriKey,
        @sourcePriKey,
        @firstGLNumber output,
        @theErrorFlag output

    if( @theErrorFlag = 1 )
    begin
        set @ErrorCode = 1
        return
    end

    -- Validate the deferred account
    exec RecurringBillingValidateGLNumber
        @firstGLNumber,
        @BranchPriKey,
        @ErrorCode output

    -- If the deferred account is not valid, record the error and error code 954
    if( @ErrorCode != 0 )
    begin
        exec spStorageRecordError @SessionID, @ordPriKey, @ErrorCode
        exec spStorageRecordError @SessionID, @ordPriKey, 954
        set @ErrorCode = 1
        return
    end

    -- Otherwise set GLNumber to the deferred account and build OpposingGLNumber with the revenue account
    else
    begin -- Start of valid deferred account
    if( @theIsInboundDeferral = 1 )
    begin
        -- Going in the deferred account is the OpposingGLNumber
        update BATProcess
        set OpposingGLNumber = @firstGLNumber
        where BATProcessPriKey = @BATProcessPriKey
    end
    else
    begin
        -- Coming out the deferred account is the GLNumber
        update BATProcess
        set GLNumber = @firstGLNumber
        where BATProcessPriKey = @BATProcessPriKey
    end

    exec RecurringBillingBuildGLNumber
        @SessionID,
        @ordPriKey,
        @branchPriKey,
        'Storage',
        @DivisionPriKey,
        @icPriKey,
        null,
        @inAuthorityID,
        @commodityPriKey,
        @mtGroupPriKey,
        @dtPriKey,
        @sourcePriKey,
        @firstGLNumber output,
        @theErrorFlag output

    if( @theErrorFlag = 1 )
    begin
        set @ErrorCode = 1
        return
    end

    -- Validate the revenue account
    exec RecurringBillingValidateGLNumber
        @firstGLNumber,
        @BranchPriKey,
        @ErrorCode output

    -- If the revenue account is not valid return the error code and error code 1020
    if @ErrorCode <> 0
    begin
        exec spStorageRecordError @SessionID, @OrdPriKey, @ErrorCode
        exec spStorageRecordError @SessionID, @OrdPriKey, 1020
        set @ErrorCode = 1
        return
    end

    -- Otherwise, set OpposingGLNumber to the the revenue account
    else
    if( @theIsInboundDeferral = 1 )
    begin
        -- Going in the revenue account is the GLNumber
        update BATProcess
        set GLNumber = @firstGLNumber
        where BATProcessPriKey = @BATProcessPriKey
    end
    else
    begin
        -- Coming out the revenue account is the OpposingGLNumber
        update BATProcess
        set OpposingGLNumber = @firstGLNumber
        where BATProcessPriKey = @BATProcessPriKey
    end
    end -- End of valid deferred account
end -- End of @StorageType = 'Deferred'

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Else if @StorageType = 'Revenue'
-- For BillingMinorItems the GLNumber is built with the revenue account and the opposing GLNumber is the storage account.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
else if @StorageType = 'Revenue'
begin -- Start of @StorageType = 'Revenue'
    exec RecurringBillingBuildGLNumber
        @SessionID,
        @ordPriKey,
        @branchPriKey,
        'Revenue',
        @DivisionPriKey,
        @icPriKey,
        null,
        @inAuthorityID,
        @commodityPriKey,
        @mtGroupPriKey,
        @dtPriKey,
        @sourcePriKey,
        @firstGLNumber output,
        @theErrorFlag output

    if( @theErrorFlag = 1 )
    begin
        set @ErrorCode = 1
        return
    end

    -- Validate the GLNumber
    exec RecurringBillingValidateGLNumber
        @firstGLNumber,
        @BranchPriKey,
        @ErrorCode output

    -- If GLNumber is not valid, log the error and error code 1031 or 1032
    if @ErrorCode <> 0
    begin
        exec spStorageRecordError @SessionID, @OrdPriKey, @ErrorCode
        if @isDeferred = 0
        begin
            exec spStorageRecordError @SessionID, @OrdPriKey, 1031
        end
        else
        begin
            exec spStorageRecordError @SessionID, @OrdPriKey, 1032
        end
        set @ErrorCode = 1
        return
    end

    -- Otherwise, update GLNumber and build OpposingGLNumber with the AR account in GLARAP
    else
    begin -- Start of build OpposingGLNumber
        update BATProcess
        set GLNumber = @firstGLNumber
        where BATProcessPriKey = @BATProcessPriKey

        set @branchPriKey = null
        select
            @branchPriKey = StorageItems.ARBranchFID,
            @DivisionPriKey = StorageItems.ARDivisionFID
        from StorageItems
        where StorageItems.StorageItemsPriKey = @inStorageItemsID

        -- If the branch is not valid, return error code 1106  (previously, this set error code 1186, which was
        -- also used in another sp for a completely different error message)
        if not exists (select * from Branch where BranchPriKey = @branchPriKey)
        begin
            set @ErrorCode = 1
            exec spStorageRecordError @SessionID, @OrdPriKey, 1106
            return
        end

        set @transType = 'AR'

        exec RecurringBillingBuildGLNumber
            @SessionID,
            @ordPriKey,
            @branchPriKey,
            'GLARAP',
            @DivisionPriKey,
            null,
            @transType,
            @inAuthorityID,
            @commodityPriKey,
            @mtGroupPriKey,
            @dtPriKey,
            @sourcePriKey,
            @firstGLNumber output,
            @theErrorFlag output

        -- Validate the AR account
        exec RecurringBillingValidateGLNumber
            @firstGLNumber,
            @BranchPriKey,
            @ErrorCode output

        -- If the AR account is not valid, log the error and error code 1033
        if @ErrorCode <> 0
        begin
            exec spStorageRecordError @SessionID, @OrdPriKey, @ErrorCode
            exec spStorageRecordError @SessionID, @OrdPriKey, 1033
            set @ErrorCode = 1
            return
        end

        -- Otherwise set OpposingGLNumber to the AR account
        else
        begin
            update BATProcess set
            OpposingGLNumber = @firstGLNumber
            where BATProcessPriKey = @BATProcessPriKey
        end
        return
    end -- End of build OpposingGLNumber
end -- End of @StorageType = 'Revenue'

-- Else if @StorageType = 'Commission', generate the GLNumbers
else if @StorageType = 'Commission'
begin -- Start of @StorageType = 'Commission'
    set @transType = 'AP'

    exec RecurringBillingBuildGLNumber
        @SessionID,
        @ordPriKey,
        @branchPriKey,
        'Commission',
        @DivisionPriKey,
        @icPriKey,
        null,
        @inAuthorityID,
        @commodityPriKey,
        @mtGroupPriKey,
        @dtPriKey,
        @sourcePriKey,
        @firstGLNumber output,
        @theErrorFlag output

    if( @theErrorFlag = 1 )
    begin
        set @ErrorCode = 1
        return
    end

    -- Validate the GLNumber
    exec RecurringBillingValidateGLNumber
        @firstGLNumber,
        @BranchPriKey,
        @ErrorCode output

    -- If GLNumber is not valid, log the error and error code 1035
    if @ErrorCode <> 0
    begin
        exec spStorageRecordError @SessionID, @OrdPriKey, @ErrorCode
        exec spStorageRecordError @SessionID, @OrdPriKey, 1035
        set @ErrorCode = 1
        return
    end

    -- Otherwise update GLNumber and build OpposingGLNumber
    else
    begin -- Start of build OpposingGLNumber
        update BATProcess
        set GLNumber = @firstGLNumber
        where BATProcessPriKey = @BATProcessPriKey

        -- Generate OpposingGLNumber for commission
        set @glsCounter = 1
        set @firstGLNumber = ' '

        -- Check the authority of the storage, this is for MSS04295 when the authority is Van Line, the opposing gl number
        -- Should be built with the branch of the warehouse.
        -- NOTE: It appears to me that this code is setting the branch to the same value that it was set to above.  But I'm
        -- too chicken to remove it (rand).
        set @branchPriKey = Case @authority
            when 'Van Line' then
            (
                select w.BranchPriKey
                from Warehouse w
                inner join StorageItems si on (si.WarehousePriKey = w.WarehousePriKey)
                inner join StorageProcessing sp on (sp.StorageItemsPrikey = si.StorageItemsPriKey)
                inner join BATProcess bp on (bp.BATSessionID = sp.SessionID)
                where bp.BATProcessPriKey = @BATProcessPriKey and
                    bp.Source = sp.Source and
                    bp.SourceRecord = sp.SourcePriKey
            )
            else
            (
                select BillingMinorItem.BranchFID
                from BillingMinorItem
                inner join CommissionedDetail on CommissionedDetail.BMinPriKey = BillingMinorItem.BMinPriKey
                where CommissionedDetail.CDPriKey = @SourcePriKey
            )
        end

        -- NOTE: Continued from above, @BranchPriKey will never be null by this point because the Agent of the Commission's Revenue Item
        -- is always set to the Warehouse of the storage item.  But I'm too chicken to remove it (rand).
        if @branchPriKey is null
        begin
            set @branchPriKey =
            (
                select o.BranchPriKey
                from Orders o
                inner join BATProcess b on (b.OrdPriKey = o.PriKey)
                where b.BATProcessPriKey = @BATProcessPriKey
            )
        end

        -- If the branch is not valid, return error code 1107 (previously, this set error code 1186, which was
        -- also used in another sp for a completely different error message)
        if not exists ( select * from Branch where BranchPriKey = @branchPriKey )
        begin
            set @ErrorCode = 1
            exec spStorageRecordError @SessionID, @OrdPriKey, 1107
            return
        end

        exec RecurringBillingBuildGLNumber
            @SessionID,
            @ordPriKey,
            @branchPriKey,
            'GLARAP',
            @DivisionPriKey,
            null,
            @transType,
            @inAuthorityID,
            @commodityPriKey,
            @mtGroupPriKey,
            @dtPriKey,
            @sourcePriKey,
            @firstGLNumber output,
            @theErrorFlag output

        -- Validate OpposingGLNumber
        exec RecurringBillingValidateGLNumber
            @firstGLNumber,
            @BranchPriKey,
            @ErrorCode output

        -- If OpposingGLNumber is not valid, log the error and return 1
        if @ErrorCode <> 0
        begin
            exec spStorageRecordError @SessionID, @OrdPriKey, @ErrorCode
            set @ErrorCode = 1
            return
        end

        -- Otherwise update the OpposingGLNumber in BATProcess
        else
        begin
            update BATProcess
            set OpposingGLNumber = @firstGLNumber
            where BATProcessPriKey = @BATProcessPriKey
        end
    end
end -- End of @StorageType = 'Commission'
GO
GRANT EXECUTE ON  [dbo].[spStorageGenerateGLNumbers_Legacy] TO [MssExec]
GO