UFN_REVENUE_GETRECOGNITIONS_2

CREATE function [dbo].[UFN_REVENUE_GETRECOGNITIONS_2]
(
@REVENUESPLITID uniqueidentifier
)
returns table
as
return 
(
with PLEDGENETAMOUNTCTE as
    (select FINANCIALTRANSACTIONLINEITEM.ID,
    FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT - 
        coalesce((select
        sum(INSTALLMENTSPLITWRITEOFF.AMOUNT) as AMOUNT
        from dbo.INSTALLMENTSPLIT
        inner join dbo.INSTALLMENTSPLITWRITEOFF on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID 
        where 
        INSTALLMENTSPLIT.PLEDGEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID and
        INSTALLMENTSPLIT.DESIGNATIONID = REVENUESPLIT_EXT.DESIGNATIONID), 0) as NETAMOUNT
    from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
    )

select REVENUERECOGNITION.[ID],
REVENUERECOGNITION.[CONSTITUENTID],
CONSTITUENT.NAME,
REVENUERECOGNITION.[AMOUNT],
-- If this is a recurring gift, just use the revenue date as the effective date.  If the actual

-- recognition effective date were used then the sorting could be off since the effective date isn't displayed

-- for that revenue type.

case when FINANCIALTRANSACTION.TYPECODE <> 2 then [EFFECTIVEDATE] else FINANCIALTRANSACTION.DATE end 'EFFECTIVEDATE',
[REVENUERECOGNITIONTYPECODEID],
REVENUERECOGNITIONTYPECODE.DESCRIPTION as RECOGNITIONTYPE,
case FINANCIALTRANSACTION.TYPECODE 
    when 1 then case 
        when REVENUERECOGNITION.AMOUNT > FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT or (select NETAMOUNT from PLEDGENETAMOUNTCTE where ID = FINANCIALTRANSACTIONLINEITEM.ID) = 0
            then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNT(FINANCIALTRANSACTIONLINEITEM.ID) - FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT + REVENUERECOGNITION.AMOUNT 
        -- Remove write-offs from the split amount since write-offs affect the gift aid amount for pledges

        else REVENUERECOGNITION.AMOUNT/(select NETAMOUNT from PLEDGENETAMOUNTCTE where ID = FINANCIALTRANSACTIONLINEITEM.ID) * (dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNT(FINANCIALTRANSACTIONLINEITEM.ID) - FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT) + REVENUERECOGNITION.AMOUNT 
        end
    when 2 then case 
        when REVENUERECOGNITION.AMOUNT > FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT or FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT = 0
            then dbo.UFN_GIFTAIDREVENUESPLIT_GETSPLITTAXCLAIMAMOUNT(FINANCIALTRANSACTIONLINEITEM.ID, 1) + REVENUERECOGNITION.AMOUNT 
        -- No need to remove write-offs from split amount since write-offs don't affect the gift aid amount for recurring gifts

        else REVENUERECOGNITION.AMOUNT/FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT * dbo.UFN_GIFTAIDREVENUESPLIT_GETSPLITTAXCLAIMAMOUNT(FINANCIALTRANSACTIONLINEITEM.ID, 1) + REVENUERECOGNITION.AMOUNT
        end
    else REVENUERECOGNITION.AMOUNT 
    end as GROSSAMOUNT,
REVENUERECOGNITION.BASECURRENCYID,
REVENUERECOGNITION.ORGANIZATIONAMOUNT,
REVENUERECOGNITION.ORGANIZATIONEXCHANGERATEID
from 
dbo.REVENUERECOGNITION
inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUERECOGNITION.CONSTITUENTID
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUERECOGNITION.REVENUESPLITID
inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
left join dbo.REVENUERECOGNITIONTYPECODE on REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
where 
REVENUERECOGNITION.REVENUESPLITID = @REVENUESPLITID
)