USP_DATAFORMTEMPLATE_VIEW_MKTSEGMENTATIONACTIVATE

The load procedure used by the view dataform template "Marketing Effort Activate 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.
@MAILINGNAME	nvarchar(100)	INOUT	Effort
@MAILINGDESCRIPTION	nvarchar(255)	INOUT	Description
@ACTIVATIONSOURCES	xml	INOUT	Activation sources
@MAILINGID	uniqueidentifier	INOUT	Effort ID
@MAILINGTYPECODE	tinyint	INOUT	Marketing effort type code
@APPEALLOCKED	bit	INOUT
@ALLACTIVATIONOPTIONSLOCKED	bit	INOUT

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_MKTSEGMENTATIONACTIVATE]
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @MAILINGNAME nvarchar(100) = null output,
  @MAILINGDESCRIPTION nvarchar(255) = null output,
  @ACTIVATIONSOURCES xml = null output,
  @MAILINGID uniqueidentifier = null output,
  @MAILINGTYPECODE tinyint = null output,
  @APPEALLOCKED bit = null output,
  @ALLACTIVATIONOPTIONSLOCKED bit = null output
)
as
  set nocount on;

  declare @ISBBEC bit = (case when dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0 then 1 else 0 end);

  set @DATALOADED = 0;

  select
    @DATALOADED = 1,
    @MAILINGID = [MKTSEGMENTATION].[ID],
    @MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
    @MAILINGNAME = [MKTSEGMENTATION].[NAME],
    @MAILINGDESCRIPTION = [MKTSEGMENTATION].[DESCRIPTION],
    @ACTIVATIONSOURCES = (select
                            [QUERYVIEWCATALOG].[DISPLAYNAME] as [RECORDSOURCE],
                            (case when len(isnull([MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID],'')) = 0 then 1 else 0 end) as [NEWAPPEAL],
                            (case when @ISBBEC = 1 and [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] <> '' and dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID]) = 1 then
                              (select [NAME] from dbo.[APPEAL] where [ID] = cast([MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] as uniqueidentifier))
                             else
                              [MKTSEGMENTATIONACTIVATE].[APPEALID]
                             end) as [APPEALID],
                            (case when @ISBBEC = 1 and [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] <> '' and dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID]) = 1 then
                              (select [DESCRIPTION] from dbo.[APPEAL] where [ID] = cast([MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] as uniqueidentifier))
                             else
                              [MKTSEGMENTATIONACTIVATE].[APPEALDESCRIPTION]
                             end) as [APPEALDESCRIPTION]
                          from dbo.[MKTSEGMENTATIONACTIVATE]
                          inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID]
                          where [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
                          order by [QUERYVIEWCATALOG].[DISPLAYNAME]
                          for xml raw('ITEM'),type,elements,root('ACTIVATIONSOURCES'),BINARY BASE64),
    @APPEALLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[APPEALLOCKED],
    @ALLACTIVATIONOPTIONSLOCKED = 
      case when 
        [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ACTIVATEOPTIONSLOCKED] = 1
        and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXPORTAFTERACTIVATELOCKED] = 1
        and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CACHESOURCEANALYSISRULEDATAACTIVATELOCKED] = 1
      then 1 else 0 end
  from dbo.[MKTSEGMENTATION]
  inner join dbo.[MKTSEGMENTATIONACTIVATEPROCESS] on [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left join dbo.[MKTCOMMUNICATIONTEMPLATE] on [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left join dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULT] on [MKTCOMMUNICATIONTEMPLATEDEFAULT].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID]
  where [MKTSEGMENTATIONACTIVATEPROCESS].[ID] = @ID;

  return 0;