/*
*    Don't use this stored proc directly but instead use spBATGenerateSecondCommGLNumber_Synonym.
*    spBATGenerateSecondCommGLNumber_Synonym will either point to this stored proc or to
*    spBATGenerateSecondCommGLNumber_Legacy if that is active.
*
*
*    Description: If parameters are valid, new GLNumbers is generated and error code 0 is returned.
*
*    Error Codes:
*    1611 if @theTransactionType is not A/R nor A/P.
*    1612 if @theAccountingAccountId is not setup in XLedgerGLARAP for the specified transaction type.
*/

CREATE PROCEDURE [dbo].[spBATGenerateSecondCommGLNumber_XLedger]
    @ICPriKey int,
    @BranchPriKey int,
    @DivisionPriKey int,
    @OrdPriKey int,
    @BATProcessPriKey int,
    @Source varchar(50),
    @SourcePriKey int
AS

set nocount on

declare @batErrorCode int = 0
declare @theAccountingAccountId int
declare @shPriKey int

-- Error codes we record.
declare @ERROR_TRANSACTION_TYPE_IS_INVALID int = 1611
declare @ERROR_TRANSACTION_TYPE_IS_NOT_IN_XLEDGERGLARAP int = 1612

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

-- We report many errors and the following variables will simplify the logic so that we do not need
-- to test over and over if this is statements else this is orders.
declare @theSourceIsStatements bit
declare @theErrorObjectTable varchar( 15 )
declare @theErrorObjectID int
declare @theErrorCodeID int

-- We use @theIsStatementItem to simplify some condition checks below.
if ( @Source = 'StatementDetail' )
begin
    set @theSourceIsStatements = 1
    set @shPriKey = ( select SHPriKey from StatementDetail where SDPriKey = @SourcePriKey )
end
else if ( @Source = 'StatementDetailDist' )
begin
    set @theSourceIsStatements = 1
    set @shPriKey =
        (
            select sd.SHPriKey
            from StatementDetail sd
            inner join StatementDetailDist sdd on ( sdd.SDPriKey = sd.SDPriKey )
            where sdd.SDDPriKey = @SourcePriKey
        )
end
else if ( @Source = 'StatementPayable' )
begin
    set @theSourceIsStatements = 1
    set @shPriKey =
        (
            select StatementDetail.SHPriKey
            from StatementDetail
            join StatementPayable on StatementPayable.StatementDetailFID = StatementDetail.SDPriKey
            where StatementPayable.StatementPayableID = @SourcePriKey
        )
end
else
begin
    set @theSourceIsStatements = 0
end

select
    @theErrorObjectTable =
        case @theSourceIsStatements
            when 1 then 'StatementHeader'
            else 'Order'
        end,
    @theErrorObjectID =
        case @theSourceIsStatements
            when 1 then @shPriKey
            else @OrdPriKey
        end

declare @theTransactionType varchar(30) = ( select TransactionType from BATProcess where BATProcessPriKey = @BATProcessPriKey )
declare @theArApType varchar(2) = case
    when @theTransactionType like '%A/R%' then 'AR'
    when @theTransactionType like '%A/P%' then 'AP'
    else null    --XLTD?????
end

if( @theArApType is null )
begin
    set @theErrorCodeID = @ERROR_TRANSACTION_TYPE_IS_INVALID

    exec @batErrorCode = spBATRecordError
        @theBlankSessionID,
        @Source,
        @SourcePriKey,
        @theErrorCodeID,
        @theErrorObjectTable,
        @theErrorObjectID,
        @theTransactionType
end

if( @batErrorCode = 0 )
begin
    select
        @theAccountingAccountId = XLedgerGLARAP.AccountingAccountFid
    from XLedgerGLARAP
    where TypeName = @theArApType

    if( @theAccountingAccountId is null )
    begin
        set @theErrorCodeID = case @theSourceIsStatements
            when 1 then @ERROR_TRANSACTION_TYPE_IS_NOT_IN_XLEDGERGLARAP
            else @ERROR_TRANSACTION_TYPE_IS_NOT_IN_XLEDGERGLARAP
        end

        exec @batErrorCode = spBATRecordError
            @theBlankSessionID,
            @Source,
            @SourcePriKey,
            @theErrorCodeID,
            @theErrorObjectTable,
            @theErrorObjectID
    end
end

if( @batErrorCode = 0 )
begin
    -- The @theAccountingAccountId is valid, so update OpposingGLNumber in BATProcess
    update BATProcess set
        OpposingGLNumber = AccountingAccount.Code,
        OpposingAccountingAccountFid = AccountingAccount.AccountingAccountId
    from BATProcess
    inner join AccountingAccount on AccountingAccount.AccountingAccountId = @theAccountingAccountId
    where BATProcessPriKey = @BATProcessPriKey
end
GO
GRANT EXECUTE ON  [dbo].[spBATGenerateSecondCommGLNumber_XLedger] TO [MssExec]
GO