UFN_CORPORATION_GETCONSTITUENTGIFTS

            CREATE function [dbo].[UFN_CORPORATION_GETCONSTITUENTGIFTS](@CURRENTAPPUSERID uniqueidentifier, @ROOTID uniqueidentifier)
            returns table
            as return

            select
                CSS.SELECTEDID as CONSTITUENTID,
                R.ID as REVENUEID,
                RS.ID as REVENUESPLITID,
                cast(sum(cast(RS.BASEAMOUNT as decimal(20,5)) - cast(coalesce(WO.AMOUNT, 0) as decimal(20,5))) as money) as AMOUNT,
                R.TYPECODE as TRANSACTIONTYPECODE,
                RSE.APPLICATIONCODE as APPLICATIONCODE,
                RSE.TYPECODE as TYPECODE,
                RSE.DESIGNATIONID as DESIGNATIONID                
            from
                dbo.CORPORATESTRUCTURESELECTION CSS
                inner join dbo.FINANCIALTRANSACTION R on CSS.SELECTEDID = R.CONSTITUENTID
                inner join (select distinct REVENUEID from dbo.UFN_SITEACCESSABLE_REVENUESPLITID_FORUSER(@CURRENTAPPUSERID)) SITEFILTER on SITEFILTER.REVENUEID = R.ID
                inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on RS.FINANCIALTRANSACTIONID = R.ID
                inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = RS.ID
                left join
                    (select
                        INSTALLMENTSPLIT.PLEDGEID,
                        INSTALLMENTSPLIT.DESIGNATIONID,
                        sum(coalesce(INSTALLMENTSPLITWRITEOFF.AMOUNT,0)) AMOUNT
                    from
                        dbo.INSTALLMENTSPLITWRITEOFF
                    inner join
                        dbo.INSTALLMENTSPLIT
                    on
                        INSTALLMENTSPLIT.ID = INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID
                    group by
                        INSTALLMENTSPLIT.PLEDGEID, INSTALLMENTSPLIT.DESIGNATIONID) WO
                on
                    WO.PLEDGEID = R.ID and WO.DESIGNATIONID = RSE.DESIGNATIONID

                left join
                    (select
                        INSTALLMENTSPLIT.PLEDGEID,
                        INSTALLMENTSPLIT.DESIGNATIONID,
                        sum(coalesce(INSTALLMENTSPLITPAYMENT.AMOUNT,0)) AMOUNT    
                    from
                        dbo.INSTALLMENTSPLITPAYMENT
                    inner join
                        dbo.INSTALLMENTSPLIT
                    on
                        INSTALLMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID
                    group by
                        INSTALLMENTSPLIT.PLEDGEID, INSTALLMENTSPLIT.DESIGNATIONID) PAYMENT
                on
                    PAYMENT.PLEDGEID = R.ID and PAYMENT.DESIGNATIONID = RSE.DESIGNATIONID
                left join
                    dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PGR on PGR.REVENUEID = R.ID
                left join
                    dbo.PLANNEDGIFT PG on PG.ID = PGR.PLANNEDGIFTID
                cross apply dbo.UFN_CORPORATION_GETFILTERDATES(@CURRENTAPPUSERID) DF
            where
                ((DF.STARTDATE is null or DF.STARTDATE <= R.DATE)
                and
                (DF.ENDDATE is null or DF.ENDDATE >= cast(R.DATE as datetime))) and
                CSS.APPUSERID = @CURRENTAPPUSERID and
                CSS.ROOTID = @ROOTID and
                R.DELETEDON is null and
                RS.DELETEDON is null and
                RS.TYPECODE <> 1
            group by CSS.SELECTEDID, R.ID, RS.ID, R.TYPECODE, RSE.APPLICATIONCODE, RSE.TYPECODE, RSE.DESIGNATIONID