/*
*    Description: Processes the revenue transactions for the specified order which are using the specified BATSession
*    identifier.  This procedure mainly summarizes the BATProcess tranactions by ExpenseType and then updates all
*    such BATProcess records of the same ExpenseType for this session with that Xgl identifier.  Any errors are
*    logged via spBATRecordError or spStorageRecordError if @inIsStorageProcessing = 1.
*
*    Input @params:
*    ===========================================================================================================================
*    @inOrderID: The primary key for the order of interest.
*    @inBatSessionID: If specified, this is a BatSession identifier used to locate our BATProcess records attached to the specified order.
*        This will be ignored if @inBatProcessPriKey is > 0.
*    @inErrorLoggingSessionId: A BAT or Storage session identifier used for any error logging.  This could be the blank session identifier.
*    @inBatProcessPriKey: If > 0, is the ONE BATProcess record to be processed and @inBatSessionID is ignored.
*    @inIsStorageProcessing: If 1, then any errors are reported via spStorageRecordError.  Otherwise, errors are reported
*        via spBATRecordError.
*    
*    Returned parameters:
*    ===========================================================================================================================
*    None.
*
*    Any detected errors are logged to the BATErrorLog via spBATRecordError or to the StorageErrorLog via spStorageRecordError
*    if @inIsStorageProcessing = 1.
*/
CREATE PROCEDURE [dbo].[spBATGetXLedgerXgls]
    @inOrderID int,
    @inBatSessionID varchar(50),
    @inErrorLoggingSessionId varchar(50),
    @inBatProcessPriKey int = null,
    @inIsStorageProcessing bit = null
as

set nocount on

-- initialize output parameters
declare @theHadError bit
declare @theXGlId int
declare @theExpenseTypeRowCount int
declare @theCounter int
declare @theExpenseTypeName varchar(16)

select
    @theHadError = 0,
    @theXGlId = null,
    @inBatProcessPriKey = case
        -- Make sure no one passes in 0 or a negative value for @inBatProcessPriKey!!
        when isnull( @inBatProcessPriKey, 0 ) > 0 then @inBatProcessPriKey
        -- Treat zero and negative values as 'not specified'.
        else null
    end

declare @theBATProcessInfo table
(
    BATProcessID int,
    TransactionType varchar(30),
    GlArAp varchar(2),
    Source varchar(50),
    SourceRecord int,
    ExpenseTypeName varchar(16),
    LaborTypeFid int
)

declare @theExpenseTypesInUse table
(
    ID int identity( 1, 1 ) not null,
    ExpenseTypeName varchar(16)
)

insert into @theBATProcessInfo
(
    BATProcessID,
    TransactionType,
    Source,
    SourceRecord

    -- LaborTypeFid int
    -- We'll populate LaborTypeFid from the Source and SourceRecord in
    -- subsequent passes for the various possible Source tables since
    -- each Source needs different joins to determine the LaborTypeFID.
)
select
    BATProcessID = BATProcess.BATProcessPriKey,
    TransactionType = BATProcess.TransactionType,
    Source = BATProcess.Source,
    SourceRecord = BATProcess.SourceRecord
from BATProcess
where @inBatProcessPriKey is null and
    BATProcess.OrdPriKey = @inOrderID and
    BATProcess.BATSessionID = @inBatSessionID
union all
select
    BATProcessID = BATProcess.BATProcessPriKey,
    TransactionType = BATProcess.TransactionType,
    Source = BATProcess.Source,
    SourceRecord = BATProcess.SourceRecord
from BATProcess
where @inBatProcessPriKey is not null and
    BATProcess.BATProcessPriKey = @inBatProcessPriKey

-- Update the GlArAp and ExpenseTypeName
update @theBATProcessInfo set
    GlArAp = case TransactionType
        when 'A/P Credit Memo' then 'AP'
        when 'A/P Invoice' then 'AP'
        when 'A/R Cash Receipt' then 'AR'
        when 'A/R Credit Memo' then 'AR'
        when 'A/R Debit Memo' then 'AR'
        when 'A/R Sales / Invoice' then 'AR'
        when 'Journal Entry' then 'GL'
        else '??'    -- We shouldn't get here
    end,
    ExpenseTypeName = case Source
        when 'ClaimItemDueFrom' then 'Claims'
        when 'ClaimHandlingRevenue' then 'Claims'
        when 'ClaimSettlement' then 'Claims'
        when 'ClaimLiabilityItem' then 'Claims'
        when 'ThirdParty' then 'ThirdParty'
        else ''
    end
from @theBATProcessInfo

-- Set Expense type for commissioned details based upon the labor type associated to the commission.
update @theBATProcessInfo set
    ExpenseTypeName = XLedgerExpenseType.[TypeName]
from @theBATProcessInfo as theBATProcessInfo
inner join CommissionedDetail on CommissionedDetail.CDPriKey = theBATProcessInfo.SourceRecord
inner join XLedgerLaborTypeToExpenseTypeMap on XLedgerLaborTypeToExpenseTypeMap.LaborTypeFID = CommissionedDetail.LaborTypeFID
inner join XLedgerExpenseType on XLedgerExpenseType.XLedgerExpenseTypeID = XLedgerLaborTypeToExpenseTypeMap.XLedgerExpenseTypeFID
where theBATProcessInfo.ExpenseTypeName = '' and
    theBATProcessInfo.Source = 'CommissionedDetail' and
    theBATProcessInfo.GlArAp = 'AP'

-- Set Expense type for split commissions details based upon the labor type associated to the split commission.
update @theBATProcessInfo set
    ExpenseTypeName = XLedgerExpenseType.[TypeName]
from @theBATProcessInfo as theBATProcessInfo
inner join SplitCommissions on SplitCommissions.SCPriKey = theBATProcessInfo.SourceRecord
inner join XLedgerLaborTypeToExpenseTypeMap on XLedgerLaborTypeToExpenseTypeMap.LaborTypeFID = SplitCommissions.LaborTypeFID
inner join XLedgerExpenseType on XLedgerExpenseType.XLedgerExpenseTypeID = XLedgerLaborTypeToExpenseTypeMap.XLedgerExpenseTypeFID
where theBATProcessInfo.ExpenseTypeName = '' and
    theBATProcessInfo.Source = 'SplitCommissions' and
    theBATProcessInfo.GlArAp = 'AP'

-- Set Expense type for advances details based upon the default labor type associated to the advancee.
update @theBATProcessInfo set
    ExpenseTypeName = XLedgerExpenseType.[TypeName]
from @theBATProcessInfo as theBATProcessInfo
inner join Advance on Advance.PriKey = theBATProcessInfo.SourceRecord
inner join SysUser on SysUser.SysUserID = Advance.AdvanceToSysUserFID
inner join SysUserLaborTypeMap on SysUserLaborTypeMap.SysUserFID = Sysuser.SysUserID and SysUserLaborTypeMap.[Default] = 1
inner join XLedgerLaborTypeToExpenseTypeMap on XLedgerLaborTypeToExpenseTypeMap.LaborTypeFID = SysUserLaborTypeMap.LaborTypeFID
inner join XLedgerExpenseType on XLedgerExpenseType.XLedgerExpenseTypeID = XLedgerLaborTypeToExpenseTypeMap.XLedgerExpenseTypeFID
where theBATProcessInfo.ExpenseTypeName = '' and
    theBATProcessInfo.Source = 'Advance'

-- XLTD: Might need more clarification/modifications once we get further into this, like for PM, etc.
-- Maybe we might need to log an error if there is some new Source type(s) we don't know about that we
-- haven't handled with the above code.

-- For StatementPayables, I don't see a way to link Vendors to a specific labor type.  The smae Vendor can
-- be assigned to multiple labor types for multiple SysUsers so there is no way to know which ONE is the
-- SysUser for a specific vendor on the StatementPayable.  If we are trying to make sure we handle all
-- Source types, we might need to do this:
--    update @theBATProcessInfo set
--        ExpenseTypeName = 'None'
--    from @theBATProcessInfo as theBATProcessInfo
--    where theBATProcessInfo.ExpenseTypeName = '' and
--        theBATProcessInfo.Source = 'StatementPayable'
--
-- and then, log an error for each Source that still has an ExpenseTypeName of ''.

-- For whatever is left, set any other remaining  to None.
update @theBATProcessInfo set
    ExpenseTypeName = 'None'
from @theBATProcessInfo as theBATProcessInfo
where theBATProcessInfo.ExpenseTypeName = ''

-- See what ExpenseTypeName(s) were actualy used by the complete list of transactions
-- on this order.  It's possible that it's only 'None' that we have to deal with.
insert into @theExpenseTypesInUse
(
    ExpenseTypeName
)
select distinct
    ExpenseTypeName
from @theBATProcessInfo

set @theExpenseTypeRowCount = @@rowcount
set @theCounter = 1

while( @theCounter <= @theExpenseTypeRowCount )
begin
    select
        @theExpenseTypeName = theExpenseTypesInUse.ExpenseTypeName
    from @theExpenseTypesInUse as theExpenseTypesInUse
    where theExpenseTypesInUse.ID = @theCounter

    exec spBATGetSingleXLedgerXglId
        @inOrderID = @inOrderID,
        @inSessionID = @inErrorLoggingSessionId,
        @inXLedgerExpenseType = @theExpenseTypeName,
        @inIsStorageProcessing = @inIsStorageProcessing,
        @outXGlId = @theXGlId out,
        @outHadError = @theHadError out

    if( @theHadError = 0 )
    begin
        update BATProcess set
            XglFid = @theXGlId
        from @theBATProcessInfo as theBATProcessInfo
        inner join BATProcess on BATProcess.BATProcessPriKey = theBATProcessInfo.BATProcessID
        where theBATProcessInfo.ExpenseTypeName = @theExpenseTypeName
    end
    
    set @theCounter = @theCounter + 1
end
GO
GRANT EXECUTE ON  [dbo].[spBATGetXLedgerXgls] TO [MssExec]
GO