UFN_REVERSALPOSTSUMMARYTYPE

CREATE function dbo.UFN_REVERSALPOSTSUMMARYTYPE(@REVERSALGLTRANSACTIONID uniqueidentifier)
returns integer
with execute as caller
as begin
    declare @RETURNVALUE integer
    declare @TRANSACTIONTYPECODE tinyint,
                    @REVENUETRANSACTIONTYPECODE tinyint,
                    @APPLICATIONCODE int,
                    @PAYMENTMETHODCODE int

    select 
        @TRANSACTIONTYPECODE = REVERSED.TRANSACTIONTYPECODE,
        @REVENUETRANSACTIONTYPECODE = coalesce(MAP.REVENUETRANSACTIONTYPECODE, 255),
        @APPLICATIONCODE = coalesce(MAP.APPLICATIONCODE, 255),
        @PAYMENTMETHODCODE = coalesce(MAP.PAYMENTMETHODCODE, 255)
    from 
        dbo.JOURNALENTRY REVERSAL
        inner join dbo.JOURNALENTRY_EXT on REVERSAL.ID = JOURNALENTRY_EXT.ID
        inner join dbo.JOURNALENTRY REVERSED on JOURNALENTRY_EXT.REVERSEDGLTRANSACTIONID = REVERSED.ID
        --left join dbo.REVENUEGLDISTRIBUTION as t2 on t2.GLTRANSACTIONID = REVERSED.ID

        inner join dbo.JOURNALENTRY_EXT JE2 on REVERSED.ID = JE2.ID
        left join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as MAP on JE2.GLPAYMENTMETHODREVENUETYPEMAPPINGID = MAP.ID 
        --left join dbo.UNREALIZEDGAINLOSSGLDISTRIBUTION on UNREALIZEDGAINLOSSGLDISTRIBUTION.GLTRANSACTIONID = REVERSED.ID

    where 
        REVERSAL.ID = @REVERSALGLTRANSACTIONID
        --and (t2.ID is not null or UNREALIZEDGAINLOSSGLDISTRIBUTION.ID is not null)

        and (JE2.TABLENAMECODE = 1 or JE2.TABLENAMECODE = 14)

    select @RETURNVALUE = dbo.UFN_POSTSUMMARYTYPE(0, @TRANSACTIONTYPECODE, @REVENUETRANSACTIONTYPECODE, @APPLICATIONCODE, @PAYMENTMETHODCODE)
    return @RETURNVALUE

end