UFN_CONSTITUENT_GIVINGHISTORYINORGCURRENCY_BULK

create function [dbo].[UFN_CONSTITUENT_GIVINGHISTORYINORGCURRENCY_BULK]
(
    @CONSTITUENTID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier
)
returns table
as
return(
    with CONSTITUENTREVENUE_CTE as
    (
        select
            REVENUE.CONSTITUENTID,
            REVENUE.ID,
            REVENUE.TYPECODE [TRANSACTIONTYPECODE],
            REVENUE.TYPE [TRANSACTIONTYPE],
            REVENUE.ORGAMOUNT AMOUNT,
            REVENUE.CALCULATEDDATE [DATE],
            REVENUE.DATEADDED
        from dbo.FINANCIALTRANSACTION REVENUE
        inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
        where REVENUE.CONSTITUENTID = @CONSTITUENTID 
            and REVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15) 
            and REVENUE.DELETEDON is null
    ), REVHISTORY_CTE as
    (
        select
            R.CONSTITUENTID,
            R.ID REVENUEID,
            R.TRANSACTIONTYPECODE,
            R.TRANSACTIONTYPE,
            R.AMOUNT as REVENUEAMOUNT,
            R.DATE,
            R.DATEADDED,
            LI.ID SPLITID,
            RSE.APPLICATIONCODE,
            RSE.DESIGNATIONID,
            LI.ORGAMOUNT as SPLITAMOUNT,
            dbo.UFN_PLEDGESPLIT_GETNETAMOUNTINCURRENCY(RSE.ID, null) as SPLITNETAMOUNT
        from CONSTITUENTREVENUE_CTE R 
        inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on R.ID = LI.FINANCIALTRANSACTIONID
        inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = LI.ID
        where 
            LI.DELETEDON is null and LI.TYPECODE = 0 and 
            (
                R.TRANSACTIONTYPECODE in (1, 7, 8) or --Pledge, Auction donation, Donor challenge claim

                (R.TRANSACTIONTYPECODE = 4 and 
                    exists(select top 1 1 
                        from dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PGR 
                        inner join dbo.PLANNEDGIFT PG on PG.ID = PGR.PLANNEDGIFTID 
                        where PGR.REVENUEID = R.ID and
                        PG.VEHICLECODE in (0,1,2,5,6,7,8,9,11,12,13))) --Planned gifts other than lead trusts and bequests

            )

        union all

        select
            R.CONSTITUENTID,
            R.ID REVENUEID,
            R.TRANSACTIONTYPECODE,
            R.TRANSACTIONTYPE,
            R.AMOUNT as REVENUEAMOUNT,
            R.DATE,
            R.DATEADDED,
            LI.ID SPLITID,
            RSE.APPLICATIONCODE,
            RSE.DESIGNATIONID,
            LI.ORGAMOUNT as SPLITAMOUNT,
            dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RSE.ID, null) as SPLITNETAMOUNT
        from CONSTITUENTREVENUE_CTE R 
        inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on R.ID = LI.FINANCIALTRANSACTIONID
        inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = LI.ID
        left join dbo.INSTALLMENTSPLITPAYMENT ISP on ISP.PAYMENTID = RSE.ID
        left join dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PGR on ISP.PLEDGEID = PGR.REVENUEID
        left join dbo.PLANNEDGIFT PG on PG.ID = PGR.PLANNEDGIFTID
        where
            LI.DELETEDON is null and LI.TYPECODE = 0 and 
            (
                (R.TRANSACTIONTYPECODE = 5 and RSE.APPLICATIONCODE = 0) or -- Donations made on orders

                (
                    R.TRANSACTIONTYPECODE = 0 and -- payments made on...

                    (
                        RSE.APPLICATIONCODE in (0, 3, 7, 13, 12) or -- ...donations, recurring gifts, matching gifts, donor challenge, auction purchase...

                        (RSE.APPLICATIONCODE = 1 and RSE.TYPECODE = 0) or -- gift revenue applied to event registration

                        (RSE.APPLICATIONCODE = 6 and 
                            exists(select top 1 1
                                from dbo.INSTALLMENTSPLITPAYMENT ISP
                                inner join dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PGR on ISP.PLEDGEID = PGR.REVENUEID
                                inner join dbo.PLANNEDGIFT PG on PG.ID = PGR.PLANNEDGIFTID
                                where ISP.PAYMENTID = RSE.ID and PG.VEHICLECODE in (3,4,10)
                            )  -- ...lead trusts and bequests

                        )
                    )
                )
            )
    )
    select 
        REVENUEID,
        TRANSACTIONTYPECODE,
        TRANSACTIONTYPE,
        REVENUEAMOUNT,
        [DATE],
        DATEADDED,
        SPLITID,
        APPLICATIONCODE,
        DESIGNATIONID,
        SPLITAMOUNT,
        (SPLITAMOUNT - SPLITNETAMOUNT) as WRITEOFFAMOUNT,
        SPLITNETAMOUNT
    from REVHISTORY_CTE RH
)