USP_DATAFORMTEMPLATE_VIEW_MKTSEGMENTATIONEXPORTUNDEFINEDPACKAGES

The load procedure used by the view dataform template "Marketing Effort Export Undefined Package View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	nvarchar(38)	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.
@CHANNELCODE	tinyint	INOUT	Channel code
@UNDEFINEDPACKAGES	xml	INOUT	Packages

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_MKTSEGMENTATIONEXPORTUNDEFINEDPACKAGES]
(
  @ID nvarchar(38),
  @DATALOADED bit = 0 output,
  @CHANNELCODE tinyint = null output,
  @UNDEFINEDPACKAGES xml = null output
)
as
  declare @SEGMENTATIONID uniqueidentifier;

  set @SEGMENTATIONID = convert(uniqueidentifier, substring(@ID, 1, charindex('|', @ID, 1) - 1));
  set @CHANNELCODE = convert(tinyint, substring(@ID, charindex('|', @ID, 1) + 1, len(@ID)));

  declare @PACKAGES table ([PACKAGEID] uniqueidentifier);

  insert into @PACKAGES
  select distinct [TEMP].[PACKAGEID]
    from (select [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
            from dbo.[MKTSEGMENTATIONSEGMENT]
            inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
            left outer join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[SEGMENTID] = [MKTSEGMENT].[ID]
           where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
           and ([MKTSEGMENTLIST].[TYPECODE] is null or [MKTSEGMENTLIST].[TYPECODE] <> 1)
          union all
          select [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]
            from dbo.[MKTSEGMENTATIONTESTSEGMENT]
            inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
            inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
            left outer join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[SEGMENTID] = [MKTSEGMENT].[ID]
           where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
           and ([MKTSEGMENTLIST].[TYPECODE] is null or [MKTSEGMENTLIST].[TYPECODE] <> 1)) as [TEMP];

  select @UNDEFINEDPACKAGES = (
    select 
      [MKTPACKAGE].[ID],
      [MKTPACKAGE].[NAME]
    from @PACKAGES as [P]
    inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [P].[PACKAGEID]
    where [MKTPACKAGE].[CHANNELCODE] = @CHANNELCODE
      and ([MKTPACKAGE].[LETTERCODEID] is null and [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] = 0 and [MKTPACKAGE].[EXPORTDEFINITIONID] is null)
     for xml raw('ITEM'), type, elements, root('UNDEFINEDPACKAGES'), BINARY BASE64);

  set @DATALOADED = 1;

  return 0;