USP_DATAFORMTEMPLATE_VIEW_MKTSEGMENTPASSIVE

The load procedure used by the view dataform template "Public Media Segment 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.
@SEGMENTTYPECODE	tinyint	INOUT	Segment type code
@SEGMENTTYPE	nvarchar(50)	INOUT	Segment type
@NAME	nvarchar(100)	INOUT	Name
@DESCRIPTION	nvarchar(255)	INOUT	Description
@SEGMENTCATEGORY	nvarchar(100)	INOUT	Category
@CODE	nvarchar(10)	INOUT	Code
@VENDOR	nvarchar(154)	INOUT	Vendor
@VENDORID	uniqueidentifier	INOUT	Vendor ID
@PARENTMEDIAOUTLETSEGMENT	nvarchar(100)	INOUT	Media outlet
@PARENTMEDIAOUTLETSEGMENTID	uniqueidentifier	INOUT	Media outlet ID
@SCHEDULESTARTTIME	time	INOUT	Start time
@SCHEDULEENDTIME	time	INOUT	End time
@SCHEDULEDURATION	nvarchar(100)	INOUT	Duration
@LOCATIONADDRESS	nvarchar(300)	INOUT	Address
@IMPRESSIONS	int	INOUT	Impressions
@IMPRESSIONCALCULATIONMETHOD	nvarchar(20)	INOUT	Impression calculation method
@GROUPS	nvarchar(255)	INOUT	Groups
@ISINUSE	bit	INOUT	Is in use
@SITEID	uniqueidentifier	INOUT	Site ID
@SITE	nvarchar(100)	INOUT	Site
@INACTIVEPUBLICMEDIAEFFORTSEXIST	bit	INOUT	Inactive public media efforts exist

Definition

Copy


CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_MKTSEGMENTPASSIVE]
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @SEGMENTTYPECODE tinyint = null output,
  @SEGMENTTYPE nvarchar(50) = null output,
  @NAME nvarchar(100) = null output,
  @DESCRIPTION nvarchar(255) = null output,
  @SEGMENTCATEGORY nvarchar(100) = null output,
  @CODE nvarchar(10) = null output,
  @VENDOR nvarchar(154) = null output,
  @VENDORID uniqueidentifier = null output,
  @PARENTMEDIAOUTLETSEGMENT nvarchar(100) = null output,
  @PARENTMEDIAOUTLETSEGMENTID uniqueidentifier = null output,
  @SCHEDULESTARTTIME time(0) = null output,
  @SCHEDULEENDTIME time(0) = null output,
  @SCHEDULEDURATION nvarchar(100) = null output,
  @LOCATIONADDRESS nvarchar(300) = null output,
  @IMPRESSIONS integer = null output,
  @IMPRESSIONCALCULATIONMETHOD nvarchar(20) = null output,
  @GROUPS nvarchar(255) = null output,
  @ISINUSE bit = null output,
  @SITEID uniqueidentifier = null output,
  @SITE nvarchar(100) = null output,
  @INACTIVEPUBLICMEDIAEFFORTSEXIST bit = null output
)
as
  set nocount on;

  set @DATALOADED = 0;
  set @INACTIVEPUBLICMEDIAEFFORTSEXIST = 0;

  select 
    @DATALOADED = 1,
    @SEGMENTTYPECODE = [MKTSEGMENT].[SEGMENTTYPECODE],
    @SEGMENTTYPE = [MKTSEGMENT].[SEGMENTTYPE],
    @NAME = [MKTSEGMENT].[NAME],
    @DESCRIPTION = [MKTSEGMENT].[DESCRIPTION],
    @SEGMENTCATEGORY = [MKTSEGMENTCATEGORYCODE].[DESCRIPTION],
    @CODE = [MKTSEGMENT].[CODE],
    @ISINUSE = dbo.[UFN_MKTSEGMENT_ISINUSE]([MKTSEGMENT].[ID]),
    @VENDOR = [VENDOR].[NAME],
    @VENDORID = [MKTSEGMENTPASSIVE].[VENDORID],
    @PARENTMEDIAOUTLETSEGMENT = [PARENTMEDIAOUTLETSEGMENT].[NAME],
    @PARENTMEDIAOUTLETSEGMENTID = [MKTSEGMENTPASSIVE].[PARENTMEDIAOUTLETSEGMENTID],
    @SCHEDULESTARTTIME = [MKTSEGMENTPASSIVE].[SCHEDULESTARTTIME],
    @SCHEDULEENDTIME = [MKTSEGMENTPASSIVE].[SCHEDULEENDTIME],
    @SCHEDULEDURATION = dbo.[UFN_MKTSEGMENTPASSIVE_GETDURATION]([MKTSEGMENTPASSIVE].[SCHEDULEDURATION]),
    @LOCATIONADDRESS = dbo.[UFN_BUILDFULLADDRESS](null, [MKTSEGMENTPASSIVE].[LOCATIONADDRESSBLOCK], [MKTSEGMENTPASSIVE].[LOCATIONCITY], [MKTSEGMENTPASSIVE].[LOCATIONSTATEID], [MKTSEGMENTPASSIVE].[LOCATIONPOSTCODE], [MKTSEGMENTPASSIVE].[LOCATIONCOUNTRYID]),
    @IMPRESSIONS = [MKTSEGMENTPASSIVE].[IMPRESSIONS],
    @IMPRESSIONCALCULATIONMETHOD = [MKTSEGMENTPASSIVE].[IMPRESSIONCALCULATIONMETHOD],
    @GROUPS = (select stuff((select ', ' + [MKTSEGMENTGROUP].[NAME]
                             from dbo.[MKTSEGMENTGROUP]
                             inner join dbo.[MKTGROUPSEGMENTS] on [MKTGROUPSEGMENTS].[SEGMENTGROUPID] = [MKTSEGMENTGROUP].[ID]
                             where [MKTGROUPSEGMENTS].[SEGMENTID] = [MKTSEGMENT].[ID]
                             order by [MKTSEGMENTGROUP].[NAME]
                             for xml path(''), type
                             ).value('.', 'varchar(max)')
                             , 1, 2, '')),
    @SITEID = [MKTSEGMENT].[SITEID],
    @SITE = [SITE].[NAME]
  from dbo.[MKTSEGMENT]
  inner join dbo.[MKTSEGMENTPASSIVE] on [MKTSEGMENTPASSIVE].[ID] = [MKTSEGMENT].[ID]
  left outer join dbo.[MKTSEGMENTCATEGORYCODE] on [MKTSEGMENTCATEGORYCODE].[ID] = [MKTSEGMENT].[SEGMENTCATEGORYCODEID]
  left outer join dbo.[CONSTITUENT] as [VENDOR] on [VENDOR].[ID] = [MKTSEGMENTPASSIVE].[VENDORID]
  left outer join dbo.[MKTSEGMENT] as [PARENTMEDIAOUTLETSEGMENT] on [PARENTMEDIAOUTLETSEGMENT].[ID] = [MKTSEGMENTPASSIVE].[PARENTMEDIAOUTLETSEGMENTID]
  left outer join dbo.[SITE] on [SITE].[ID] = [MKTSEGMENT].[SITEID]
  where [MKTSEGMENT].[ID] = @ID;

  if @DATALOADED = 1
    select
      @INACTIVEPUBLICMEDIAEFFORTSEXIST = 1
    from dbo.[MKTSEGMENTATION]
    where [MAILINGTYPECODE] = 4 and [ACTIVE] = 0;

  return 0;