UFN_CORPORATION_GETCONSTITUENTGIFTSINCURRENCY_NOSITE

create function [dbo].[UFN_CORPORATION_GETCONSTITUENTGIFTSINCURRENCY_NOSITE]
                (  
                    @CURRENTAPPUSERID uniqueidentifier,   
                    @ROOTID uniqueidentifier,   
                    @CURRENCYID uniqueidentifier,  
                    @ORGANIZATIONCURRENCYID uniqueidentifier,  
                    @DECIMALDIGITS tinyint,  
                    @ROUNDINGTYPECODE tinyint,
                    @SECURITYFEATUREID uniqueidentifier,
                    @SECURITYFEATURETYPE tinyint
                )                 
                returns table
                as return

                select
                    CSS.SELECTEDID as CONSTITUENTID,
                    RS.REVENUEID as REVENUEID,
                    RS.ID as REVENUESPLITID,
                    cast(sum(cast(RS.AMOUNTINCURRENCY as decimal(20,5)) - cast(coalesce(WO.AMOUNT, 0) as decimal(20,5))) as money) as AMOUNT,
                    RS.TRANSACTIONTYPECODE as TRANSACTIONTYPECODE,
                    RS.APPLICATIONCODE as APPLICATIONCODE,
                    RS.TYPECODE as TYPECODE,
                    RS.DESIGNATIONID as DESIGNATIONID
                from
                    dbo.CORPORATESTRUCTURESELECTION CSS
                    inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RS on RS.CONSTITUENTID = CSS.SELECTEDID  
                    left join
                        (select
                            INSTALLMENTSPLIT.PLEDGEID,
                            INSTALLMENTSPLIT.DESIGNATIONID,
                            case when @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() then
                                sum(isnull(INSTALLMENTSPLITWRITEOFF.ORGANIZATIONAMOUNT,0))
                            else sum(coalesce(dbo.UFN_INSTALLMENTSPLITWRITEOFF_GETAMOUNTINCURRENCY(INSTALLMENTSPLITWRITEOFF.ID,@CURRENCYID),0)) end AMOUNT
                        from
                            dbo.INSTALLMENTSPLITWRITEOFF
                        inner join
                            dbo.INSTALLMENTSPLIT
                        on
                            INSTALLMENTSPLIT.ID = INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID
                        group by
                            INSTALLMENTSPLIT.PLEDGEID, INSTALLMENTSPLIT.DESIGNATIONID) WO
                    on
                        WO.PLEDGEID = RS.REVENUEID and WO.DESIGNATIONID = RS.DESIGNATIONID
                    cross apply dbo.UFN_CORPORATION_GETFILTERDATES(@CURRENTAPPUSERID) DF
                where
                    ((DF.STARTDATE is null or DF.STARTDATE <= RS.DATE)
                    and
                    (DF.ENDDATE is null or DF.ENDDATE >= RS.DATE)) and
                    CSS.APPUSERID = @CURRENTAPPUSERID and
                    CSS.ROOTID = @ROOTID
            group by CSS.SELECTEDID, RS.REVENUEID, RS.ID, RS.TRANSACTIONTYPECODE, RS.APPLICATIONCODE, RS.TYPECODE, RS.DESIGNATIONID;