UFN_CORPORATION_GETALUMNIGIVINGTOTALINCURRENCY_BULK

                CREATE function [dbo].[UFN_CORPORATION_GETALUMNIGIVINGTOTALINCURRENCY_BULK]
                (
                    @CURRENTAPPUSERID uniqueidentifier,
                    @CURRENCYID uniqueidentifier,
                    @ORGANIZATIONCURRENCYID uniqueidentifier,
                    @DECIMALDIGITS tinyint,
                    @ROUNDINGTYPECODE tinyint
                )
        returns table
        as
        return(
          select
            RELATIONSHIP.RECIPROCALCONSTITUENTID as CONSTITUENTID,
            case
                when (@CURRENCYID is null) or (@CURRENCYID = @ORGANIZATIONCURRENCYID)
                    then coalesce(sum(REVENUESPLIT.ORGAMOUNT), 0)
                    else
                        coalesce(sum(RS.AMOUNTINCURRENCY), 0)
                    end [AMOUNTINCURRENCY],
            cast(REVENUE.DATE as datetime) DATE
          from
                        dbo.RELATIONSHIP 
                        inner join dbo.EMPLOYEECORPORATERELATIONSHIPTYPE on RELATIONSHIP.RELATIONSHIPTYPECODEID = EMPLOYEECORPORATERELATIONSHIPTYPE.RELATIONSHIPTYPECODEID
                        inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.CONSTITUENTID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
                        inner join (select distinct REVENUEID from dbo.UFN_SITEACCESSABLE_REVENUESPLITID_FORUSER(@CURRENTAPPUSERID)) SITEFILTER on SITEFILTER.REVENUEID = REVENUE.ID
                        inner join dbo.PDACCOUNTSYSTEM on REVENUE.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                        inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID                        
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.FINANCIALTRANSACTIONID = REVENUE.ID
                        inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID
                        left join dbo.INSTALLMENTSPLITPAYMENT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                        left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID,dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),@DECIMALDIGITS,@ROUNDINGTYPECODE) as RS on RS.ID = REVENUESPLIT.ID
        where
                    ((@CURRENCYID is null) 
                    or (@CURRENCYID = @ORGANIZATIONCURRENCYID)
                    or @CURRENCYID = CURRENCYSET.BASECURRENCYID)
                    and REVENUE.DELETEDON is null
                    and REVENUESPLIT.DELETEDON is null
                    and REVENUESPLIT.TYPECODE <> 1
                    and
                            (REVENUE.TYPECODE in (1,3) or --Pledge or MG claim

                                (REVENUE.TYPECODE = 0 and 
                                    (REVENUESPLIT_EXT.APPLICATIONCODE in (0,1,3,4,5,6,18,19) or 
                                    (REVENUESPLIT_EXT.APPLICATIONCODE = 7 and INSTALLMENTSPLITPAYMENT.ID is null) -- unapplied MG claim payments

                                    )
                                )
                            ) --Payments

                    and dbo.UFN_CONSTITUENT_ISALUMNUS(RELATIONSHIP.RELATIONSHIPCONSTITUENTID)=1
          group by RELATIONSHIP.RECIPROCALCONSTITUENTID,REVENUE.DATE
          union all
          select
            RELATIONSHIP.RECIPROCALCONSTITUENTID as CONSTITUENTID,
            case
                        when [LATESTORGANIZATIONEXCHANGERATE].RATE is not null
                            then sum(dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUESPLIT.ORGAMOUNT, [LATESTORGANIZATIONEXCHANGERATE].RATE), @DECIMALDIGITS, @ROUNDINGTYPECODE))
                        when [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE is not null
                            then sum(dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUESPLIT.ORGAMOUNT, cast((1 / [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE))
                        else
                            0
                      end [AMOUNTINCURRENCY],
            cast(REVENUE.DATE as datetime) DATE
          from
                        dbo.RELATIONSHIP 
                        inner join dbo.EMPLOYEECORPORATERELATIONSHIPTYPE on RELATIONSHIP.RELATIONSHIPTYPECODEID = EMPLOYEECORPORATERELATIONSHIPTYPE.RELATIONSHIPTYPECODEID
                        inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.CONSTITUENTID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
                        inner join (select distinct REVENUEID from dbo.UFN_SITEACCESSABLE_REVENUESPLITID_FORUSER(@CURRENTAPPUSERID)) SITEFILTER on SITEFILTER.REVENUEID = REVENUE.ID
                        inner join dbo.PDACCOUNTSYSTEM on REVENUE.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                        inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID                         
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.FINANCIALTRANSACTIONID = REVENUE.ID
                        inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID
                        left join dbo.INSTALLMENTSPLITPAYMENT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
            left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID,dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),@DECIMALDIGITS,@ROUNDINGTYPECODE) as RS on RS.ID = REVENUESPLIT.ID
          outer apply
                    (
                        select
                            RATE
                        from
                            dbo.CURRENCYEXCHANGERATE
                        where
                            @ORGANIZATIONCURRENCYID = CURRENCYEXCHANGERATE.FROMCURRENCYID
                            and @CURRENCYID = CURRENCYEXCHANGERATE.TOCURRENCYID
                            and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
                            and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
                            and dateadd(ms, 86399996,cast(REVENUE.DATE as datetime)) >= CURRENCYEXCHANGERATE.ASOFDATESDTZ
                            and dateadd(ms, 86399996,cast(REVENUE.DATE as datetime)) <= CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
                    ) LATESTORGANIZATIONEXCHANGERATE
                    outer apply
                    (
                        select
                            RATE
                        from
                            dbo.CURRENCYEXCHANGERATE
                        where
                            @CURRENCYID = CURRENCYEXCHANGERATE.FROMCURRENCYID
                            and @ORGANIZATIONCURRENCYID= CURRENCYEXCHANGERATE.TOCURRENCYID
                            and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
                            and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
                            and dateadd(ms, 86399996,cast(REVENUE.DATE as datetime)) >= CURRENCYEXCHANGERATE.ASOFDATESDTZ
                            and dateadd(ms, 86399996,cast(REVENUE.DATE as datetime)) <= CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
                    ) LATESTINVERSEORGANIZATIONEXCHANGERATE
                    where ((@CURRENCYID is not null) 
                        and (@CURRENCYID <> @ORGANIZATIONCURRENCYID)
                        and @CURRENCYID <> CURRENCYSET.BASECURRENCYID)
            and
                              (REVENUE.TYPECODE in (1,3) or --Pledge or MG claim

                                  (REVENUE.TYPECODE = 0 and 
                                      (REVENUESPLIT_EXT.APPLICATIONCODE in (0,1,3,4,5,6,18,19) or 
                                      (REVENUESPLIT_EXT.APPLICATIONCODE = 7 and INSTALLMENTSPLITPAYMENT.ID is null) -- unapplied MG claim payments

                                      )
                                  )
                              ) --Payments

                        and dbo.UFN_CONSTITUENT_ISALUMNUS(RELATIONSHIP.RELATIONSHIPCONSTITUENTID)=1
          group by RELATIONSHIP.RECIPROCALCONSTITUENTID,LATESTORGANIZATIONEXCHANGERATE.RATE,LATESTINVERSEORGANIZATIONEXCHANGERATE.RATE,REVENUE.DATE
        );