USP_DATALIST_CAMPAIGNGOAL

This datalist returns all campaign goals for a campaign.
Parameters

Parameter	Parameter Type	Mode	Description
@CAMPAIGNID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_CAMPAIGNGOAL(
                    @CAMPAIGNID uniqueidentifier
                )
                as
                    set nocount on;

                    declare @BASECURRENCYID uniqueidentifier;
                    select
                        @BASECURRENCYID = CAMPAIGN.BASECURRENCYID
                    from
                        dbo.CAMPAIGN
                    where
                        CAMPAIGN.ID = @CAMPAIGNID;

                    with XMLNAMESPACES ('bb_appfx_dataforms' as DFI)
                    select
                        CAMPAIGNGOAL.ID,
                        CAMPAIGNHIERARCHYGOAL.NAME,
                        CAMPAIGNGOAL.AMOUNT,
                        CAMPAIGNHIERARCHYGOAL.STARTDATE,
                        CAMPAIGNHIERARCHYGOAL.ENDDATE,
                        (
                            select top 1 
                                KPIINSTANCE.ID
                            from 
                                dbo.KPIINSTANCE 
                            where 
                                KPIINSTANCE.PARAMETERSXML.value('data(/DFI:DataFormItem/DFI:Values/DFI:fv[@ID="CAMPAIGNID"]/DFI:Value)[1]','varchar(36)') = cast(CAMPAIGNGOAL.CAMPAIGNID as varchar(36))
                            and
                                KPIINSTANCE.PARAMETERSXML.value('data(/DFI:DataFormItem/DFI:Values/DFI:fv[@ID="CAMPAIGNHIERARCHYGOALID"]/DFI:Value)[1]','varchar(36)') = cast(CAMPAIGNGOAL.CAMPAIGNHIERARCHYGOALID as varchar(36))
                        ) as KPIINSTANCEID,
                        CAMPAIGNGOAL.ID as KPICONTEXTID,
                        @BASECURRENCYID as BASECURRENCYID
                    from
                        dbo.CAMPAIGNGOAL
                    left outer join
                        dbo.CAMPAIGNHIERARCHYGOAL on CAMPAIGNGOAL.CAMPAIGNHIERARCHYGOALID = CAMPAIGNHIERARCHYGOAL.ID
                    where 
                        CAMPAIGNGOAL.CAMPAIGNID = @CAMPAIGNID
                    order by
                        --CAMPAIGNHIERARCHYGOAL.NAME Bug 18435

                         CAMPAIGNHIERARCHYGOAL.STARTDATE,
                        CAMPAIGNHIERARCHYGOAL.ENDDATE