UFN_CAMPAIGNSUBPRIORITY_GETTOTALBALANCEINCURRENCY

Returns the total balance for a given campaign sub priority converting amounts to the specified currency.
Return

Return Type
money
Parameters

Parameter	Parameter Type	Mode
@CAMPAIGNSUBPRIORITYID	uniqueidentifier	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@CURRENCYID	uniqueidentifier	IN
Definition

Copy


            create function dbo.UFN_CAMPAIGNSUBPRIORITY_GETTOTALBALANCEINCURRENCY
            (
                @CAMPAIGNSUBPRIORITYID uniqueidentifier,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @CURRENCYID uniqueidentifier = null
            )
            returns money
            as
            begin
                declare @RESULT money;

                with CAMPAIGNSUBPRIORITY_CTE as (
                    select distinct
                        REVENUESPLIT.REVENUEID
                    from
                        dbo.REVENUESPLIT
                    inner join dbo.REVENUE with (nolock) on REVENUE.ID = REVENUESPLIT.REVENUEID 
                    inner join dbo.REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID = REVENUESPLIT.ID
                    where
                        REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID = @CAMPAIGNSUBPRIORITYID
                        and REVENUE.TRANSACTIONTYPECODE in (1, 6)
                        and (REVENUE.DATE >= @STARTDATE or @STARTDATE is null)
                        and (REVENUE.DATE <= @ENDDATE or @ENDDATE is null))
                select
                    @RESULT = coalesce(sum(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID, @CURRENCYID)), 0)
                    -
                    (
                        coalesce(( --Subtract Writeoffs of the above pledges

                                    select
                                        SUM(dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY(WRITEOFFSPLIT.ID, @CURRENCYID))
                                    from
                                        dbo.WRITEOFFSPLIT
                                    inner join dbo.WRITEOFF on WRITEOFF.ID = WRITEOFFSPLIT.WRITEOFFID
                                    inner join dbo.REVENUE with (nolock) on REVENUE.ID = WRITEOFF.REVENUEID 
                                    inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
                                    inner join CAMPAIGNSUBPRIORITY_CTE on CAMPAIGNSUBPRIORITY_CTE.REVENUEID = REVENUESPLIT.REVENUEID
                        ), 0)
                    )
                from
                    dbo.REVENUESPLIT
                inner join CAMPAIGNSUBPRIORITY_CTE on CAMPAIGNSUBPRIORITY_CTE.REVENUEID = REVENUESPLIT.REVENUEID;

                return @RESULT;
            end