/**
* Don't use this stored proc directly but instead use spBATGenerateSecondRevGLNumber_Synonym.
* spBATGenerateSecondRevGLNumber_Synonym will either point to this stored proc or to
* spBATGenerateSecondRevGLNumber_Legacy if that is active.
*
*    
*    Description: If parameters are valid, new GLNumbers is generated and error code 0 is returned.
*
*    @param    @ICPriKey
*    @param    @BranchPriKey
*    @param    @DivisionPriKey
*    @param    @OrdPriKey
*    @param    @BATProcessPriKey
*    @param    @Source
*    @param    @SourcePriKey
*
*    Error Codes:
*    1501 if @theTransactionType is not A/R nor A/P.
*    1502 if @theAccountingAccountId is not setup in XLedgerGLARAP for the specified transaction type.
*/

CREATE PROCEDURE [dbo].[spBATGenerateSecondRevGLNumber_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 = 1501
declare @ERROR_TRANSACTION_TYPE_IS_NOT_IN_XLEDGERGLARAP int = 1502

-- 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(20)
declare @theErrorObjectID int
declare @theErrorCodeID int

if( @Source in ('StatementDetail', 'StatementDetailDist', 'StatementPayable') )
begin
    -- Get the StatementHeader SHPriKey
    set @shPriKey = case @Source
        when 'StatementDetail' then
        (
            select SHPriKey
            from StatementDetail
            where SDPriKey = @SourcePriKey
        )
        when 'StatementDetailDist' then
        (
            select StatementDetail.SHPriKey
            from StatementDetail
            inner join StatementDetailDist on ( StatementDetailDist.SDPriKey = StatementDetail.SDPriKey )
            where StatementDetailDist.SDDPriKey = @SourcePriKey
        )
        when 'StatementPayable' then
        (
            select StatementDetail.SHPriKey
            from StatementDetail
            join StatementPayable on StatementPayable.StatementDetailFID = StatementDetail.SDPriKey
            where StatementPayable.StatementPayableID = @SourcePriKey
        )
    end

    -- For StatementDetail, StatementDetailDist or StatementPayable errors, we will record errors with StatementHeader
    set @theSourceIsStatements = 1
    set @theErrorObjectTable = 'StatementHeader'
    set @theErrorObjectID = @shPriKey
end
else
begin
    -- Note: For orders, @BranchPriKey and @DivisionPriKey are the ARBranch and ARDivision.

    -- Otherwise we will record errors with Order
    set @theSourceIsStatements = 0
    set @theErrorObjectTable = 'Order'
    set @theErrorObjectID = @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].[spBATGenerateSecondRevGLNumber_XLedger] TO [MssExec]
GO