USP_KPI_CAMPAIGNHIERARCHYGOAL_GOALAMOUNTTOTAL

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

                        declare @GOALSTARTDATE datetime, @GOALENDDATE datetime, @HIERARCHYPATH hierarchyid 
                        select
                            @GOALSTARTDATE = CAMPAIGNHIERARCHYGOAL.STARTDATE,
                            @GOALENDDATE = CAMPAIGNHIERARCHYGOAL.ENDDATE,
                            @HIERARCHYPATH = CAMPAIGN.HIERARCHYPATH
                        from dbo.CAMPAIGNHIERARCHYGOAL
                            inner join dbo.CAMPAIGN on CAMPAIGN.ID = CAMPAIGNHIERARCHYGOAL.CAMPAIGNID
                        where CAMPAIGNHIERARCHYGOAL.ID = @CAMPAIGNHIERARCHYGOALID

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

                        declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

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

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

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

                    end