USP_KPI_CAMPAIGNGOAL_GOALAMOUNTTOTAL

Parameters

Parameter	Parameter Type	Mode
@VALUE	money	INOUT
@ASOFDATE	datetime	IN
@CAMPAIGNID	uniqueidentifier	IN
@CAMPAIGNHIERARCHYGOALID	uniqueidentifier	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@CURRENCYID	uniqueidentifier	IN
@CAMPAIGNGOALID	uniqueidentifier	IN
Definition

Copy


                    CREATE procedure dbo.USP_KPI_CAMPAIGNGOAL_GOALAMOUNTTOTAL (
                        @VALUE money output, 
                        @ASOFDATE datetime, 
                        @CAMPAIGNID uniqueidentifier,
                        @CAMPAIGNHIERARCHYGOALID uniqueidentifier,
                        @STARTDATE datetime = null,
                        @ENDDATE datetime = null,
                        @CURRENCYID uniqueidentifier = null,
            @CAMPAIGNGOALID uniqueidentifier = null
                    ) as begin

                        if @STARTDATE is null and @ENDDATE is null
                        begin
                            select
                                @STARTDATE = CAMPAIGNHIERARCHYGOAL.STARTDATE,
                                @ENDDATE = CAMPAIGNHIERARCHYGOAL.ENDDATE
                            from dbo.CAMPAIGNHIERARCHYGOAL
                                inner join dbo.CAMPAIGN on CAMPAIGN.ID = CAMPAIGNHIERARCHYGOAL.CAMPAIGNID
                            where CAMPAIGNHIERARCHYGOAL.ID = @CAMPAIGNHIERARCHYGOALID
                        end

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

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

                        declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                        declare @TOTALRECEIVED money;
                        select @TOTALRECEIVED = TOTALRECEIVED
                        from dbo.UFN_CAMPAIGN_GETTOTALRECEIVEDINCURRENCY(@HIERARCHYPATH, @STARTDATE, @ENDDATE, @CURRENCYID, @ORGANIZATIONCURRENCYID);

                        declare @PLEDGEBALANCE money;
                        select @PLEDGEBALANCE = PLEDGEBALANCE
                        from dbo.UFN_CAMPAIGN_GETPLEDGEBALANCEINCURRENCY(@HIERARCHYPATH, @STARTDATE, @ENDDATE, @CURRENCYID, @ORGANIZATIONCURRENCYID);

                        set @VALUE = coalesce(@TOTALRECEIVED, 0) + coalesce(@PLEDGEBALANCE, 0);

                    end