/**
* Don't use this stored proc directly but instead use spStorageGenerateGLNumbers_Synonym.
* spStorageGenerateGLNumbers_Synonym will either point to this stored proc or to
* spStorageGenerateGLNumbers_Legacy if that is active.
*
*
*  Updates a BATProcess record with the correct GLNumber and OpposingGLNumber for storage records.
*
*    Errors:
*    491 If @theOrderId 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_XLedger]
    @StorageType varchar(20),
    @BATProcessPriKey int,
    @SessionID varchar(50),
    @inAuthorityID int,
    @inStorageItemsID int,
    @ErrorCode int output
as
set nocount on

declare
    --@branchPriKey int,
    --@DivisionPriKey int,
    @theOrderId int,
    @theSource varchar(50),
    @theSourcePriKey int,
    --@firstGLNumber varchar(66),
    --@countGLCSegmets int,
    --@glsCounter int,
    --@padLength int,
    --@glaPriKey int,
    --@icDetailPriKey int,
    @theItemCodePriKey int,
    --@commissionSysuser int,
    --@transType varchar(2),
    @theIsDeferred smallint,
    --@commodityPriKey int,
    --@mtGroupPriKey int,
    --@dtPriKey int,
    --@authority varchar(30),
    @theErrorFlag bit,
    @theMappingType varchar(16),
    @theGLARAPType varchar(2),
    @theRevenueExpenseGLNumberAccountingAccountId int,
    @theARAPSDGLNumberAccountingAccountId int,
    @theFirstGLNumberAccountingAccountId int,
    @theSecondGLNumberAccountingAccountId int,
    @DEFERRED_STORAGE_TYPE varchar(10),
    @REVENUE_STORAGE_TYPE varchar(10),
    @COMMISSION_STORAGE_TYPE varchar(10),
    @theItemCodeStr varchar(10),
    @theIsInboundDeferral bit

select
    @theErrorFlag = 0,
    @ErrorCode = 0,
    @theIsDeferred = 0,
    @DEFERRED_STORAGE_TYPE = 'Deferred',
    @REVENUE_STORAGE_TYPE = 'Revenue',
    @COMMISSION_STORAGE_TYPE = 'Commission'

select
    @theOrderId = OrdPriKey,
    @theSource = [Source],
    @theSourcePriKey = SourceRecord
from BATProcess
where BATProcessPriKey = @BATProcessPriKey

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

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

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

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

set @theMappingType =
    case @theSource
        when 'BillingMinorItem' then 'Revenue'
        when 'CommissionedDetail' then 'Commissions'
        when 'StorageDeferral' then 'Revenue'
    end

select
    @theRevenueExpenseGLNumberAccountingAccountId = XLedgerItemCodeMap.AccountingAccountFid
from XLedgerItemCodeMapAccountType
left outer join XLedgerItemCodeMap on XLedgerItemCodeMap.ItemCodeFid = @theItemCodePriKey and
    XLedgerItemCodeMap.XLedgerItemCodeMapAccountTypeFid = XLedgerItemCodeMapAccountType.XLedgerItemCodeMapAccountTypeId
where XLedgerItemCodeMapAccountType.MappingType = @theMappingType

if( @theRevenueExpenseGLNumberAccountingAccountId is null )
begin
    set @theItemCodeStr = ( select convert( varchar(10), ItemCode ) from itemCode where ICPriKey = @theItemCodePriKey )
    exec spStorageRecordError
        @inSessionID = @SessionID,
        @inOrderID = @theOrderId,
        @inErrorCodeID = 1520,
        @inParam1 = @theItemCodeStr,
        @inParam2 = @theMappingType

    set @ErrorCode = 1
    return
end

-- Get the AR, AP or Storage Deferral (SD) GL account
set @theGLARAPType =
    case @theSource
        when 'BillingMinorItem' then 'AR'
        when 'CommissionedDetail' then 'AP'
        when 'StorageDeferral' then 'SD'
    end

select
    @theARAPSDGLNumberAccountingAccountId = XLedgerGLARAP.AccountingAccountFid
from XLedgerGLARAP
where XLedgerGLARAP.TypeName = @theGLARAPType

if( @theARAPSDGLNumberAccountingAccountId is null )
begin
    exec spStorageRecordError
        @inSessionID = @SessionID,
        @inOrderID = @theOrderId,
        @inErrorCodeID = 1521,
        @inParam1 = @theGLARAPType

    set @ErrorCode = 1
    return
end

-- @DEFERRED_STORAGE_TYPE 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_STORAGE_TYPE )
begin -- Start of @StorageType = @DEFERRED_STORAGE_TYPE
    set @theIsInboundDeferral =
    (
        select
        case
            when Processed is null then 1
            else 0
        end
        from StorageDeferral
        where DeferralPriKey = @theSourcePriKey
    )

    set @theFirstGLNumberAccountingAccountId =
        case @theIsInboundDeferral
            -- Going in the revenue account is the GLNumber
            when 1 then @theRevenueExpenseGLNumberAccountingAccountId
            -- Coming out the deferred account is the GLNumber
            else @theARAPSDGLNumberAccountingAccountId
        end

    set @theSecondGLNumberAccountingAccountId =
        case @theIsInboundDeferral
            -- Going in the deferred account is the OpposingGLNumber
            when 1 then @theARAPSDGLNumberAccountingAccountId
            -- Coming out the revenue account is the OpposingGLNumber
            else @theRevenueExpenseGLNumberAccountingAccountId
        end

end -- End of @StorageType = @DEFERRED_STORAGE_TYPE

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Else if @StorageType = @REVENUE_STORAGE_TYPE
-- For BillingMinorItems the GLNumber is built with the revenue account and the opposing GLNumber is the storage account.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
else if @StorageType = @REVENUE_STORAGE_TYPE
begin -- Start of @StorageType = @REVENUE_STORAGE_TYPE

    -- Update GLNumber with the revenue account
    set @theFirstGLNumberAccountingAccountId = @theRevenueExpenseGLNumberAccountingAccountId
    -- And set OpposingGLNumber to the AR account
    set @theSecondGLNumberAccountingAccountId = @theARAPSDGLNumberAccountingAccountId

end -- End of @StorageType = @REVENUE_STORAGE_TYPE

-- Else if @StorageType = @COMMISSION_STORAGE_TYPE, generate the GLNumbers
else if @StorageType = @COMMISSION_STORAGE_TYPE
begin -- Start of @StorageType = @COMMISSION_STORAGE_TYPE

    -- Update GLNumber with the expense account
    set @theFirstGLNumberAccountingAccountId = @theRevenueExpenseGLNumberAccountingAccountId
    -- And set OpposingGLNumber to the AP account
    set @theSecondGLNumberAccountingAccountId = @theARAPSDGLNumberAccountingAccountId

end -- End of @StorageType = @COMMISSION_STORAGE_TYPE

-- Now, use the first and second AccountingAccount to update BATProcess:
update BATProcess set
    GLNumber = isnull( FirstAccountingAccount.Code, '' ),
    NominalAccountingAccountFid = @theFirstGLNumberAccountingAccountId,
    OpposingGLNumber = isnull( SecondAccountingAccount.Code, '' ),
    OpposingAccountingAccountFid = @theSecondGLNumberAccountingAccountId
from BATProcess
left outer join AccountingAccount as FirstAccountingAccount on FirstAccountingAccount.AccountingAccountId = @theFirstGLNumberAccountingAccountId
left outer join AccountingAccount as SecondAccountingAccount on SecondAccountingAccount.AccountingAccountId = @theSecondGLNumberAccountingAccountId
where BATProcess.BATProcessPriKey = @BATProcessPriKey
GO
GRANT EXECUTE ON  [dbo].[spStorageGenerateGLNumbers_XLedger] TO [MssExec]
GO