USP_KPI_CAMPAIGNGOAL_GOALVALUE

Parameters

Parameter	Parameter Type	Mode
@VALUE	money	INOUT
@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_GOALVALUE (
                        @VALUE money output,
                        @CAMPAIGNID uniqueidentifier,
                        @CAMPAIGNHIERARCHYGOALID uniqueidentifier,
                        @STARTDATE datetime = null,
                        @ENDDATE datetime = null,
                        @CURRENCYID uniqueidentifier = null,
            @CAMPAIGNGOALID uniqueidentifier = null
                    ) as begin

                        set @VALUE = 0;

                        --Because we are forcing the currency to be the campaign's base, we can just get the 

                        --    AMOUNT off the goal tables without having to worry about any conversions.


                        if exists(select CAMPAIGN.ID from dbo.CAMPAIGN where CAMPAIGN.ID = @CAMPAIGNID and CAMPAIGN.HIERARCHYPATH.GetAncestor(1) = hierarchyid::GetRoot())
                            select
                                @VALUE = CAMPAIGNHIERARCHYGOAL.AMOUNT
                            from
                                dbo.CAMPAIGNHIERARCHYGOAL
                            where
                                CAMPAIGNHIERARCHYGOAL.ID = @CAMPAIGNHIERARCHYGOALID
                        else
                            select
                                @VALUE = CAMPAIGNGOAL.AMOUNT
                            from
                                dbo.CAMPAIGNGOAL
                            where
                                CAMPAIGNGOAL.CAMPAIGNID = @CAMPAIGNID
                            and
                                CAMPAIGNGOAL.CAMPAIGNHIERARCHYGOALID = @CAMPAIGNHIERARCHYGOALID

                    end