USP_REVENUE_GETREVENUEFORPLEDGEPAYMENTRECEIPTUPDATE

create procedure dbo.USP_REVENUE_GETREVENUEFORPLEDGEPAYMENTRECEIPTUPDATE
(
    @REVENUESELECTIONID uniqueidentifier
)
as
begin
    select
        FINANCIALTRANSACTION.ID as FINANCIALTRANSACTIONID,
        FINANCIALTRANSACTIONLINEITEM.ID as FINANCIALTRANSACTIONLINEITEMID,
        INSTALLMENTPAYMENT.PLEDGEID,
        sum(INSTALLMENTPAYMENT.AMOUNT) as APPLIEDTOPLEDGE,
        coalesce(
        (
            select sum(TRANSACTIONAMOUNT)
            from dbo.FINANCIALTRANSACTIONLINEITEM
                inner join REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
            where
                FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID and
                REVENUESPLIT_EXT.APPLICATIONCODE <> 2
        ), 0) as APPLIEDTOOTHER,
        coalesce(sum(REVENUEBENEFIT.TRANSACTIONTOTALVALUE), 0) as BENEFITAMOUNT,
        REVENUE_EXT.RECEIPTAMOUNT
    from dbo.INSTALLMENTPAYMENT
        inner join dbo.FINANCIALTRANSACTIONLINEITEM on INSTALLMENTPAYMENT.PAYMENTID = FINANCIALTRANSACTIONLINEITEM.ID
        inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@REVENUESELECTIONID) as SELECTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = SELECTION.ID
        inner join dbo.FINANCIALTRANSACTION on SELECTION.ID = FINANCIALTRANSACTION.ID
        inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
        left join dbo.REVENUEBENEFIT on FINANCIALTRANSACTION.ID = REVENUEBENEFIT.REVENUEID
    where
        FINANCIALTRANSACTION.TYPECODE = 0 --Verify revenue is a donation.

        and REVENUE_EXT.DONOTRECEIPT = 0 --Verify payment is receiptable.

        and not exists
        (
            select FINANCIALTRANSACTIONLINEITEM.ID
            from dbo.FINANCIALTRANSACTIONLINEITEM
                inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
            where
                FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                and REVENUESPLIT_EXT.APPLICATIONCODE in (1, 12) -- Event registration, auction purchase

        )-- Exclude types of revenue that have their own receipt amount

    group by
        FINANCIALTRANSACTION.ID,
        FINANCIALTRANSACTIONLINEITEM.ID,
        INSTALLMENTPAYMENT.PLEDGEID,
        REVENUEBENEFIT.TRANSACTIONTOTALVALUE,
        REVENUE_EXT.RECEIPTAMOUNT
end