USP_DATAFORMTEMPLATE_VIEW_MKTSOURCECODEMAP

The load procedure used by the view dataform template "Source Code Map View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	nvarchar(50)	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.
@SOURCECODEMAPID	uniqueidentifier	INOUT	Source code map ID
@SEGMENTATIONID	uniqueidentifier	INOUT	Marketing effort ID
@SEGMENTATIONNAME	nvarchar(100)	INOUT	Marketing effort
@SEGMENTNAME	nvarchar(203)	INOUT	Segment name
@PACKAGENAME	nvarchar(100)	INOUT	Package name
@APPEALID	uniqueidentifier	INOUT	Appeal ID
@APPEALNAME	nvarchar(100)	INOUT	Appeal

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_MKTSOURCECODEMAP]
(
  @ID nvarchar(50),
  @DATALOADED bit = 0 output,
  @SOURCECODEMAPID uniqueidentifier = null output,
  @SEGMENTATIONID uniqueidentifier = null output,
  @SEGMENTATIONNAME nvarchar(100) = null output,
  @SEGMENTNAME nvarchar(203) = null output,
  @PACKAGENAME nvarchar(100) = null output,
  @APPEALID uniqueidentifier = null output,
  @APPEALNAME nvarchar(100) = null output
)
as
  set nocount on;

  set @DATALOADED = 0;

  select
    @DATALOADED = 1,
    @SOURCECODEMAPID = [MKTSOURCECODEMAP].[ID],
    @SEGMENTATIONID = [MKTSEGMENTATION].[ID],
    @SEGMENTATIONNAME = [MKTSEGMENTATION].[NAME],
    @SEGMENTNAME = (case when [MKTSEGMENTATIONTESTSEGMENT].[ID] is not null then dbo.[UFN_MKTSEGMENTATIONTESTSEGMENT_GETNAME]([MKTSEGMENTATIONTESTSEGMENT].[ID]) else [MKTSEGMENT].[NAME] end),
    @PACKAGENAME = isnull([TESTPACKAGE].[NAME], [SEGMENTPACKAGE].[NAME]),
    @APPEALID = [APPEAL].[ID],
    @APPEALNAME = [APPEAL].[NAME]
  from dbo.[MKTSOURCECODEMAP]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSOURCECODEMAP].[SEGMENTATIONID]
  inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSOURCECODEMAP].[SEGMENTATIONSEGMENTID]
  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
  inner join dbo.[MKTPACKAGE] as [SEGMENTPACKAGE] on [SEGMENTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
  left join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[ID] = [MKTSOURCECODEMAP].[SEGMENTATIONTESTSEGMENTID]
  left join dbo.[MKTPACKAGE] as [TESTPACKAGE] on [TESTPACKAGE].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]
  inner join dbo.[MKTSEGMENTATIONACTIVATE] on ([MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = [MKTSEGMENT].[QUERYVIEWCATALOGID])
  inner join dbo.[APPEAL] on [APPEAL].[ID] = [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID]
  where [MKTSOURCECODEMAP].[SOURCECODE] = @ID
  and [MKTSEGMENTATION].[ACTIVE] = 1;

  if @DATALOADED = 0
    select
      @DATALOADED = 1,
      @SOURCECODEMAPID = [MKTSOURCECODEMAP].[ID],
      @SEGMENTATIONID = [MKTSEGMENTATION].[ID],
      @SEGMENTATIONNAME = [MKTSEGMENTATION].[NAME],
      @SEGMENTNAME = [MKTSEGMENT].[NAME],
      @PACKAGENAME = '',
      @APPEALID = null,
      @APPEALNAME = ''
    from dbo.[MKTSOURCECODEMAP]
    inner join dbo.[MKTSEGMENTWHITEMAIL] on [MKTSEGMENTWHITEMAIL].[ID] = [MKTSOURCECODEMAP].[WHITEMAILSEGMENTID]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTWHITEMAIL].[ID]
    left outer join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTWHITEMAIL].[SEGMENTATIONID]
    where [MKTSOURCECODEMAP].[SOURCECODE] = @ID
    and isnull([MKTSEGMENTATION].[ACTIVE], 1) = 1;

  return 0;