UFN_CORPORATION_GETCONSTITUENTGIFTSINCURRENCY

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

                select
                    CSS.SELECTEDID as CONSTITUENTID,
                    R.ID as REVENUEID,
                    RS.ID as REVENUESPLITID,
                    cast(sum(cast(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RS.ID,@CURRENCYID) as decimal(20,5)) - cast(coalesce(WO.AMOUNT, 0) as decimal(20,5))) as money) as AMOUNT,
                    R.TRANSACTIONTYPECODE as TRANSACTIONTYPECODE,
                    RS.APPLICATIONCODE as APPLICATIONCODE,
                    RS.TYPECODE as TYPECODE,
                    RS.DESIGNATIONID as DESIGNATIONID
                from
                    dbo.CORPORATESTRUCTURESELECTION CSS
                    inner join dbo.REVENUE 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.REVENUESPLIT RS on RS.REVENUEID = R.ID
                    left join
                        (select
                            INSTALLMENTSPLIT.PLEDGEID,
                            INSTALLMENTSPLIT.DESIGNATIONID,
                            sum(coalesce(dbo.UFN_INSTALLMENTSPLITWRITEOFF_GETAMOUNTINCURRENCY(INSTALLMENTSPLITWRITEOFF.ID,@CURRENCYID),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 = RS.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 >= R.DATE)) and
                    CSS.APPUSERID = @CURRENTAPPUSERID and
                    CSS.ROOTID = @ROOTID
            group by CSS.SELECTEDID, R.ID, RS.ID, R.TRANSACTIONTYPECODE, RS.APPLICATIONCODE, RS.TYPECODE, RS.DESIGNATIONID;