UFN_CAMPAIGN_GETTOTALRECEIVEDINCURRENCY

Returns total revenue 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
@CURRENCYID	uniqueidentifier	IN
@ORGANIZATIONCURRENCYID	uniqueidentifier	IN
Definition

Copy


            CREATE function dbo.UFN_CAMPAIGN_GETTOTALRECEIVEDINCURRENCY (
                @HIERARCHYPATH hierarchyid,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @CURRENCYID uniqueidentifier = null,
                @ORGANIZATIONCURRENCYID uniqueidentifier =null 
            ) 
            returns table as
                return
                    with REVENUE_CTE(TOTALRECEIVED) as (
                        select 
                            case 
                                when @CURRENCYID = coalesce(@ORGANIZATIONCURRENCYID,dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()) then
                                    REVENUESPLIT.ORGANIZATIONAMOUNT
                                when @CURRENCYID = REVENUESPLIT.BASECURRENCYID then
                                    REVENUESPLIT.AMOUNT
                                else 
                                    dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID, @CURRENCYID)
                            end as 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    
                            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 not in (10, 1) or REVENUESPLIT.TYPECODE = 0)
                    )

                    select sum(TOTALRECEIVED) as TOTALRECEIVED from REVENUE_CTE;