UFN_CAMPAIGN_GETTOTALRECOGNITIONRECEIVEDINCURRENCY

Returns total recognition received for a campaign converting amounts to the specified currency.
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@HIERARCHYPATH	hierarchyid	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@RECOGNITIONTYPEID	uniqueidentifier	IN
@CURRENCYID	uniqueidentifier	IN
@ORGANIZATIONCURRENCYID	uniqueidentifier	IN
Definition

Copy


            CREATE function dbo.UFN_CAMPAIGN_GETTOTALRECOGNITIONRECEIVEDINCURRENCY (
                @HIERARCHYPATH hierarchyid,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @RECOGNITIONTYPEID uniqueidentifier,
                @CURRENCYID uniqueidentifier = null,
                @ORGANIZATIONCURRENCYID uniqueidentifier =null
            ) returns table as
            return
                select
          case when @CURRENCYID = coalesce(@ORGANIZATIONCURRENCYID,dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()) then
            coalesce(sum(RECOGNITIONS.ORGANIZATIONAMOUNT),0)
          when @CURRENCYID = RECOGNITIONS.BASECURRENCYID then
            coalesce(sum(RECOGNITIONS.AMOUNT),0)
          else
                      coalesce(sum(RECOGNITIONS.AMOUNTINCURRENCY), 0)
          end TOTALRECEIVED
                from
                    dbo.REVENUESPLIT
                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 REVENUESPLIT.ID = CAMPAIGNREVENUESPLIT.REVENUESPLITID    
                cross apply 
                    dbo.UFN_RECOGNITIONCREDIT_GETRECOGNITIONS_3(1, @RECOGNITIONTYPEID, @CURRENCYID, REVENUESPLIT.ID, @STARTDATE, @ENDDATE) RECOGNITIONS
                left outer join 
                    dbo.REVENUE on REVENUESPLIT.REVENUEID = REVENUE.ID
                where
                    ((REVENUE.TRANSACTIONTYPECODE = 0) or
            (REVENUE.TRANSACTIONTYPECODE = 8 and 
              exists (select 1 from DONORCHALLENGEENCUMBERED 
                      inner join dbo.REVENUESPLITCAMPAIGN on DONORCHALLENGEENCUMBERED.REVENUESPLITID = REVENUESPLITCAMPAIGN.REVENUESPLITID 
                      inner join dbo.CAMPAIGN on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGN.ID
                      where MATCHEDREVENUEID = REVENUE.ID and 
                        CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@HIERARCHYPATH) = 1 and
                        DONORCHALLENGEENCUMBERED.DESIGNATIONID = REVENUESPLIT.DESIGNATIONID)))
                and
                    REVENUESPLIT.APPLICATIONCODE in (0, 6, 7, 3, 10)
                and 
                    (REVENUESPLIT.APPLICATIONCODE <> 10 or REVENUESPLIT.TYPECODE = 0)
                and
                    (RECOGNITIONS.REVENUERECOGNITIONTYPECODEID = @RECOGNITIONTYPEID or @RECOGNITIONTYPEID is null)
        group by RECOGNITIONS.BASECURRENCYID