USP_DATALIST_CAMPAIGNTEAM

This datalist returns all of the organizational teams assigned to 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_CAMPAIGNTEAM (
                    @CAMPAIGNID uniqueidentifier
                ) as begin
                    set nocount on;            

                    with XMLNAMESPACES ('bb_appfx_dataforms' as DFI)
                    select
                        CAMPAIGNTEAM.ID,
                        CAMPAIGNTEAM.ORGTEAMIDSET,
                        dbo.UFN_IDSET_GETNAME(CAMPAIGNTEAM.ORGTEAMIDSET) as NAME,
                        (
                            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(CAMPAIGNTEAM.CAMPAIGNID as varchar(36))
                            and
                                KPIINSTANCE.PARAMETERSXML.value('data(/DFI:DataFormItem/DFI:Values/DFI:fv[@ID="ORGTEAMIDSET"]/DFI:Value)[1]','varchar(36)') = cast(CAMPAIGNTEAM.ORGTEAMIDSET as varchar(36))
                            and
                                KPIINSTANCE.KPICATALOGID = 'c34812ae-1e1f-4b17-8c27-a42e6e4338b0'
                        ) as KPIINSTANCEID
                    from
                        dbo.CAMPAIGNTEAM
                    where
                        CAMPAIGNTEAM.CAMPAIGNID = @CAMPAIGNID
                    order by
                        NAME;

                end