UFN_CAMPAIGN_GETTOTALPLEDGERECOGNITIONINCURRENCY

            CREATE function dbo.UFN_CAMPAIGN_GETTOTALPLEDGERECOGNITIONINCURRENCY (
                @HIERARCHYPATH hierarchyid,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @RECOGNITIONTYPEID uniqueidentifier,
                @CURRENCYID uniqueidentifier = null,
        @ORGANIZATIONCURRENCYID uniqueidentifier= null
            ) returns table as
            return
                select 
          case when @CURRENCYID = @ORGANIZATIONCURRENCYID then
            coalesce(sum(DATA.ORGANIZATIONAMOUNT),0)
          when @CURRENCYID = DATA.BASECURRENCYID then
            coalesce(sum(DATA.AMOUNT),0)
          else
            coalesce(sum(DATA.AMOUNTINCURRENCY), 0)
          end as TOTALRECEIVED
                from (
                    select distinct
                        coalesce(PLEDGERECOGNITION.ID, PAYMENTRECOGNITION.ID) ID,
            case when PLEDGERECOGNITION.ID is not null then
              PLEDGERECOGNITION.ORGANIZATIONAMOUNT
            else
              PAYMENTRECOGNITION.ORGANIZATIONAMOUNT
            end as ORGANIZATIONAMOUNT,
            case when PLEDGERECOGNITION.ID is not null then
              PLEDGERECOGNITION.BASECURRENCYID
            else
              PAYMENTRECOGNITION.BASECURRENCYID
            end as BASECURRENCYID,
            case when PLEDGERECOGNITION.ID is not null then
              PLEDGERECOGNITION.AMOUNT
            else
              PAYMENTRECOGNITION.AMOUNT
            end as AMOUNT,
            case when PLEDGERECOGNITION.ID is not null then            
                        dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY(PLEDGERECOGNITION.ID, @CURRENCYID)
            else
              PAYMENTRECOGNITION.AMOUNTINCURRENCY
            end as AMOUNTINCURRENCY
                    from
                        dbo.REVENUESPLIT PLEDGESPLIT
                    inner join ( 
                        select 
                            distinct REVENUESPLITCAMPAIGN.REVENUESPLITID
                        from
                            dbo.REVENUESPLITCAMPAIGN
                        inner join
                            dbo.CAMPAIGN on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGN.ID
                        where
                            CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@HIERARCHYPATH) = 1
                    ) as CAMPAIGNREVENUESPLIT on PLEDGESPLIT.ID = CAMPAIGNREVENUESPLIT.REVENUESPLITID    
                    left join 
                        dbo.REVENUERECOGNITION PLEDGERECOGNITION on PLEDGERECOGNITION.REVENUESPLITID = PLEDGESPLIT.ID
                    left outer join 
                        dbo.REVENUE PLEDGE on PLEDGESPLIT.REVENUEID = PLEDGE.ID
                    left join (
                        select
                            INSTALLMENTSPLITPAYMENT.PLEDGEID as PLEDGEID,
                            INSTALLMENTSPLITPAYMENT.PAYMENTID as PAYMENTID,
                            INSTALLMENTSPLIT.DESIGNATIONID as DESIGNATIONID
                        from dbo.INSTALLMENTSPLITPAYMENT
                            inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID
                        ) INSTALLMENTS on INSTALLMENTS.PLEDGEID = PLEDGE.ID and INSTALLMENTS.DESIGNATIONID = PLEDGESPLIT.DESIGNATIONID
                    left join 
                        dbo.REVENUESPLIT PAYMENTSPLIT on PAYMENTSPLIT.ID = INSTALLMENTS.PAYMENTID
                    outer apply
                        dbo.UFN_RECOGNITIONCREDIT_GETRECOGNITIONS_3(1, @RECOGNITIONTYPEID, @CURRENCYID, PAYMENTSPLIT.ID, null, null) PAYMENTRECOGNITION
                    where
                        (@STARTDATE <= coalesce(PLEDGERECOGNITION.EFFECTIVEDATE,PAYMENTRECOGNITION.EFFECTIVEDATE) or @STARTDATE is null)
                    and
                        (coalesce(PLEDGERECOGNITION.EFFECTIVEDATE,PAYMENTRECOGNITION.EFFECTIVEDATE) <= @ENDDATE or @ENDDATE is null)
                    and
                        PLEDGE.TRANSACTIONTYPECODE in (1,3,4,6)
                    and
                        (coalesce(PLEDGERECOGNITION.REVENUERECOGNITIONTYPECODEID, PAYMENTRECOGNITION.REVENUERECOGNITIONTYPECODEID) = @RECOGNITIONTYPEID or @RECOGNITIONTYPEID is null)
                    ) as DATA
        group by DATA.BASECURRENCYID