USP_KPI_CAMPAIGNSUBPRIORITY_AMOUNTTOTAL

Parameters

Parameter	Parameter Type	Mode
@ASOFDATE	datetime	IN
@CAMPAIGNID	uniqueidentifier	IN
@PRIORITYID	uniqueidentifier	IN
@SUBPRIORITYID	uniqueidentifier	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@CURRENCYID	uniqueidentifier	IN
@VALUE	money	INOUT

Definition

Copy



        CREATE procedure [dbo].[USP_KPI_CAMPAIGNSUBPRIORITY_AMOUNTTOTAL] (
              @ASOFDATE datetime,
              @CAMPAIGNID uniqueidentifier,
              @PRIORITYID uniqueidentifier,
              @SUBPRIORITYID uniqueidentifier,
              @STARTDATE datetime = null,
              @ENDDATE datetime = null,
              @CURRENCYID uniqueidentifier = null,
              @VALUE money output
          ) as           
            begin
                declare @HIERARCHYPATH hierarchyid;

                select @HIERARCHYPATH = HIERARCHYPATH
                from dbo.CAMPAIGN
                where ID = @CAMPAIGNID;

                if @ENDDATE is null or DATEDIFF(day, @ASOFDATE, @ENDDATE) > 0
                begin
                    set @ENDDATE = @ASOFDATE
                end

                declare @TOTALRECEIVED money;
                declare @PLEDGEBALANCE money;

                select @TOTALRECEIVED = TOTALRECEIVED
                from  dbo.UFN_CAMPAIGNSUBPRIORITY_GETTOTALRECEIVEDINCURRENCY_FORCAMPAIGN (@HIERARCHYPATH, @SUBPRIORITYID, @STARTDATE, @ENDDATE, @CURRENCYID)

                select @PLEDGEBALANCE = PLEDGEBALANCE
                from dbo.UFN_CAMPAIGNSUBPRIORITY_GETPLEDGEBALANCEINCURRENCY (@HIERARCHYPATH, @SUBPRIORITYID, @STARTDATE, @ENDDATE, @CURRENCYID)

                set @VALUE = @TOTALRECEIVED + @PLEDGEBALANCE;
            end