USP_DATAFORMTEMPLATE_VIEW_MKTSEGMENTEXPRESSION

The load procedure used by the view dataform template "Segment Expression View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@NAME	nvarchar(100)	INOUT	Name
@SEGMENTTYPECODE	tinyint	INOUT	Segment type code
@SEGMENTLISTID	uniqueidentifier	INOUT	List segment ID
@TYPECODE	tinyint	INOUT	Type code
@STATUSCODE	tinyint	INOUT	Status code
@ISCONSOLIDATED	bit	INOUT	Is consolidated?
@SHOWINQUERYDESIGNER	bit	INOUT	Show in query designer?
@PARENTSEGMENTID	uniqueidentifier	INOUT	Parent segment ID
@PARENTSEGMENTNAME	nvarchar(100)	INOUT	Parent segment name
@DEDUPLISTS	bit	INOUT	Process list duplicates
@SEGMENTREFRESHPROCESSID	uniqueidentifier	INOUT	Segment refresh process ID
@SEGMENTLISTIMPORTPROCESSID	uniqueidentifier	INOUT	List segment import process ID
@SEGMENTLISTDEDUPEPROCESSID	uniqueidentifier	INOUT	List segment dedupe process ID
@RECORDTYPENAME	nvarchar(50)	INOUT	Record type name
@QUERYVIEWCATALOGID	uniqueidentifier	INOUT	Query view ID
@HASSELECTIONS	bit	INOUT	Has selections
@HASAVAILABLEKPIS	bit	INOUT	Has available KPIs
@ISHISTORICAL	bit	INOUT

Definition

Copy


CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_MKTSEGMENTEXPRESSION]
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @NAME nvarchar(100) = null output,
  @SEGMENTTYPECODE tinyint = null output,
  @SEGMENTLISTID uniqueidentifier = null output,
  @TYPECODE tinyint = null output,
  @STATUSCODE tinyint = null output,
  @ISCONSOLIDATED bit = null output,
  @SHOWINQUERYDESIGNER bit = null output,
  @PARENTSEGMENTID uniqueidentifier = null output,
  @PARENTSEGMENTNAME nvarchar(100) = null output,
  @DEDUPLISTS bit = null output,
  @SEGMENTREFRESHPROCESSID uniqueidentifier = null output,
  @SEGMENTLISTIMPORTPROCESSID uniqueidentifier = null output,
  @SEGMENTLISTDEDUPEPROCESSID uniqueidentifier = null output,
  @RECORDTYPENAME nvarchar(50) = null output,
  @QUERYVIEWCATALOGID uniqueidentifier = null output,
  @HASSELECTIONS bit = null output,
  @HASAVAILABLEKPIS bit = null output,
  @ISHISTORICAL bit = null output
)
as
  set nocount on;

  set @DATALOADED = 0;

  select
    @DATALOADED = 1,
    @NAME = [MKTSEGMENT].[NAME],
    @SEGMENTTYPECODE = [MKTSEGMENT].[SEGMENTTYPECODE],
    @SEGMENTLISTID = [MKTSEGMENTLIST].[ID],
    @TYPECODE = [MKTSEGMENTLIST].[TYPECODE],
    @STATUSCODE = [MKTSEGMENTLIST].[STATUSCODE],
    @ISCONSOLIDATED = (case when [MKTSEGMENTLIST].[CONSOLIDATEDQUERYVIEWID] is null then 0 else 1 end),
    @SHOWINQUERYDESIGNER = [QUERYVIEWCATALOG].[ROOTOBJECT],
    @PARENTSEGMENTID = [PARENTSEGMENT].[ID],
    @PARENTSEGMENTNAME = [PARENTSEGMENT].[NAME],
    @DEDUPLISTS = cast((case when [MKTSEGMENTLISTDEDUPEPROCESS].[ID] is null then 0 else (case when dbo.[UFN_MKTSEGMENTLIST_DUPLICATEFIELDSEXIST]([MKTSEGMENTLIST].[ID]) = 1 then 1 else 0 end) end) as bit),
    @SEGMENTREFRESHPROCESSID = [MKTSEGMENTREFRESHPROCESS].[ID],
    @SEGMENTLISTIMPORTPROCESSID = [MKTSEGMENTLISTIMPORTPROCESS].[ID],
    @SEGMENTLISTDEDUPEPROCESSID = [MKTSEGMENTLISTDEDUPEPROCESS].[ID],
    @RECORDTYPENAME = [RECORDTYPE].[NAME],
    @QUERYVIEWCATALOGID = [MKTSEGMENTLIST].[QUERYVIEWCATALOGID],
    @HASSELECTIONS = (case when exists(select 1 from dbo.[MKTSEGMENTSELECTION] where [SEGMENTID] = [MKTSEGMENT].[ID]) then 1 else 0 end),
    @ISHISTORICAL = [MKTSEGMENT].[ISHISTORICAL]
  from dbo.[MKTSEGMENT]
  left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
  left join dbo.[MKTSEGMENTREFRESHPROCESS] on [MKTSEGMENTREFRESHPROCESS].[SEGMENTID] = [MKTSEGMENT].[ID]
  left join dbo.[MKTSEGMENTLISTIMPORTPROCESS] on [MKTSEGMENTLISTIMPORTPROCESS].[SEGMENTLISTID] = [MKTSEGMENTLIST].[ID]
  left join dbo.[MKTSEGMENTLISTDEDUPEPROCESS] on [MKTSEGMENTLISTDEDUPEPROCESS].[SEGMENTLISTID] = [MKTSEGMENTLIST].[ID]
  left join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSEGMENTLIST].[QUERYVIEWCATALOGID]
  left join dbo.[MKTSEGMENT] as [PARENTSEGMENT] on [PARENTSEGMENT].[ID] = [MKTSEGMENTLIST].[PARENTSEGMENTID]
  left join dbo.[RECORDTYPE] on [RECORDTYPE].[ID] = [MKTSEGMENTLIST].[IDSETRECORDTYPEID]
  where [MKTSEGMENT].[ID] = @ID;

    -- Check to see whether any segment KPIs will be available with the currently installed products.
    declare @INSTALLED table (ID uniqueidentifier, NAME nvarchar(100));
    insert into @INSTALLED exec dbo.USP_INSTALLEDPRODUCTS_GETLIST;

    declare @SEGMENTRECORDID uniqueidentifier = (select ID from dbo.RECORDTYPE where NAME = 'Segment');

    if exists(
        select 1
        from dbo.KPICATALOG
        cross apply (
            select KpiSpec.InstalledProduct.value('(./@ID)', 'uniqueidentifier') ID
            from KPICATALOG.SPECXML.nodes('declare namespace d1p1="bb_appfx_commontypes"; declare default element namespace "bb_appfx_kpi"; /KpiSpec/d1p1:InstalledProductList/d1p1:InstalledProduct') KpiSpec (InstalledProduct)
        ) NEEDEDPRODUCTS
        inner join @INSTALLED INS on INS.ID = NEEDEDPRODUCTS.ID
        where KPICATALOG.CONTEXTRECORDTYPEID = @SEGMENTRECORDID
    )
        set @HASAVAILABLEKPIS = 1

  return 0;