UFN_CAMPAIGNSUBPRIORITY_GETTOTALRECEIVEDINCURRENCY_FORCAMPAIGN

Returns total revenue received for a campaign sub priority converting amounts to the specified currency.

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode
@HIERARCHYPATH	hierarchyid	IN
@SUBPRIORITYID	uniqueidentifier	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@CURRENCYID	uniqueidentifier	IN

Definition

Copy



    create function [dbo].[UFN_CAMPAIGNSUBPRIORITY_GETTOTALRECEIVEDINCURRENCY_FORCAMPAIGN] (
        @HIERARCHYPATH hierarchyid,
        @SUBPRIORITYID uniqueidentifier,
        @STARTDATE datetime = null,
        @ENDDATE datetime = null,
        @CURRENCYID uniqueidentifier = null
    ) returns table as
    return
        select
            coalesce(sum(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID, @CURRENCYID)), 0) 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
            and
                REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID = @SUBPRIORITYID

        ) as CAMPAIGNREVENUESPLIT on REVENUESPLIT.ID = CAMPAIGNREVENUESPLIT.REVENUESPLITID    

        left outer join
            dbo.REVENUE on REVENUESPLIT.REVENUEID = REVENUE.ID
        where
            (@STARTDATE <= REVENUE.DATE or @STARTDATE is null)
        and
            (REVENUE.DATE <= @ENDDATE or @ENDDATE is null)
        and
            REVENUE.TRANSACTIONTYPECODE = 0
        and
            REVENUESPLIT.APPLICATIONCODE in (0, 1, 5, 6, 7, 3, 10, 13)
        and 
            (REVENUESPLIT.APPLICATIONCODE <> 10 or REVENUESPLIT.TYPECODE = 0)