USP_DATAFORMTEMPLATE_EDITLOAD_MKTSEGMENTATIONEXPORTPROCESS_4

The load procedure used by the edit dataform template "Marketing Effort Export Process Edit Form 4"

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.
@SEGMENTATIONID	uniqueidentifier	INOUT	Marketing effort
@DESCRIPTION	nvarchar(255)	INOUT	Description
@MAILINGTYPECODE	tinyint	INOUT	Type
@EXPORTDEFINITIONRECORDTYPE	nvarchar(50)	INOUT	Export definition record type
@EXPORTDEFINITIONRECORDTYPEID	uniqueidentifier	INOUT	Export definition record type ID
@EXPORTDEFINITIONQUERYVIEWID	uniqueidentifier	INOUT	Export definition query view ID
@MAILEXPORTDEFINITIONID	uniqueidentifier	INOUT	Mail export definition
@EMAILEXPORTDEFINITIONID	uniqueidentifier	INOUT	Email export definition
@PHONEEXPORTDEFINITIONID	uniqueidentifier	INOUT	Phone export definition
@HASMAILPACKAGES	bit	INOUT	Has mail packages?
@HASEMAILPACKAGES	bit	INOUT	Has email packages?
@HASPHONEPACKAGES	bit	INOUT	Has phone packages?
@HASUNDEFINEDMAILPACKAGES	bit	INOUT	Has undefined mail packages?
@HASUNDEFINEDEMAILPACKAGES	bit	INOUT	Has undefined email packages?
@HASUNDEFINEDPHONEPACKAGES	bit	INOUT	Has undefined phone packages?
@HASDEFINEDPACKAGES	bit	INOUT	Has defined packages?
@DEFINEDPACKAGES	xml	INOUT	Defined packages
@ORGANIZATIONCURRENCYID	uniqueidentifier	INOUT	Organization currency ID
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@EXPORTACTIVESEGMENTS	bit	INOUT	Export active segments
@EXPORTINLINEEXCLUSIONS	bit	INOUT	Export exclusion segments
@INLINEEXCLUSIONEXPORTDEFINITIONID	uniqueidentifier	INOUT	Export definition
@HASEXCLUDEDRECORDS	bit	INOUT	Has excluded records?
@EXPORTDESCRIPTIONLOCKED	bit	INOUT
@MAILEXPORTDEFINITIONIDLOCKED	bit	INOUT
@EMAILEXPORTDEFINITIONIDLOCKED	bit	INOUT
@PHONEEXPORTDEFINITIONIDLOCKED	bit	INOUT

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTSEGMENTATIONEXPORTPROCESS_4]
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @SEGMENTATIONID uniqueidentifier = null output,
  @DESCRIPTION nvarchar(255) = null output,
  @MAILINGTYPECODE tinyint = null output,
  @EXPORTDEFINITIONRECORDTYPE nvarchar(50) = null output,
  @EXPORTDEFINITIONRECORDTYPEID uniqueidentifier = null output,
  @EXPORTDEFINITIONQUERYVIEWID uniqueidentifier = null output,
  @MAILEXPORTDEFINITIONID uniqueidentifier = null output,
  @EMAILEXPORTDEFINITIONID uniqueidentifier = null output,
  @PHONEEXPORTDEFINITIONID uniqueidentifier = null output,
  @HASMAILPACKAGES bit = null output,
  @HASEMAILPACKAGES bit = null output,
  @HASPHONEPACKAGES bit = null output,
  @HASUNDEFINEDMAILPACKAGES bit = null output,
  @HASUNDEFINEDEMAILPACKAGES bit = null output,
  @HASUNDEFINEDPHONEPACKAGES bit = null output,
  @HASDEFINEDPACKAGES bit = null output,
  @DEFINEDPACKAGES xml = null output,
  @ORGANIZATIONCURRENCYID uniqueidentifier = null output,
  @TSLONG bigint = 0 output,
  @EXPORTACTIVESEGMENTS bit = null output,
  @EXPORTINLINEEXCLUSIONS bit = null output,
  @INLINEEXCLUSIONEXPORTDEFINITIONID uniqueidentifier = null output,
  @HASEXCLUDEDRECORDS bit = null output,
  @EXPORTDESCRIPTIONLOCKED bit = null output,
  @MAILEXPORTDEFINITIONIDLOCKED bit = null output,
  @EMAILEXPORTDEFINITIONIDLOCKED bit = null output,
  @PHONEEXPORTDEFINITIONIDLOCKED bit = null output
)
as
  set nocount on;

  set @DATALOADED = 0;
  set @TSLONG = 0;

  select 
    @DATALOADED = 1,
    @SEGMENTATIONID = [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID],
    @MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
    @DESCRIPTION = [MKTSEGMENTATIONEXPORTPROCESS].[DESCRIPTION],
    @EXPORTACTIVESEGMENTS = [MKTSEGMENTATIONEXPORTPROCESS].[EXPORTACTIVESEGMENTS],
    @MAILEXPORTDEFINITIONID = [MKTSEGMENTATIONEXPORTPROCESS].[MAILEXPORTDEFINITIONID],
    @EMAILEXPORTDEFINITIONID = [MKTSEGMENTATIONEXPORTPROCESS].[EMAILEXPORTDEFINITIONID],
    @PHONEEXPORTDEFINITIONID = [MKTSEGMENTATIONEXPORTPROCESS].[PHONEEXPORTDEFINITIONID],
    @EXPORTINLINEEXCLUSIONS = [MKTSEGMENTATIONEXPORTPROCESS].[EXPORTINLINEEXCLUSIONS],
    @INLINEEXCLUSIONEXPORTDEFINITIONID =[MKTSEGMENTATIONEXPORTPROCESS]. [INLINEEXCLUSIONEXPORTDEFINITIONID],
    @TSLONG = [MKTSEGMENTATIONEXPORTPROCESS].[TSLONG]
  from dbo.[MKTSEGMENTATIONEXPORTPROCESS]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID]
  where [MKTSEGMENTATIONEXPORTPROCESS].[ID] = @ID
  and [MKTSEGMENTATION].[ISHISTORICAL] = 0;

  set @EXPORTDEFINITIONRECORDTYPE = dbo.[UFN_MKTEXPORTDEFINITION_GETRECORDTYPEFROMMAILINGTYPECODE](@MAILINGTYPECODE);
  set @EXPORTDEFINITIONRECORDTYPEID = dbo.[UFN_MKTEXPORTDEFINITION_GETRECORDTYPEIDFROMMAILINGTYPECODE](@MAILINGTYPECODE);
  set @EXPORTDEFINITIONQUERYVIEWID = dbo.[UFN_MKTEXPORTDEFINITION_GETQUERYVIEWIDFROMMAILINGTYPECODE](@MAILINGTYPECODE);

  set @ORGANIZATIONCURRENCYID = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

  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];

  set @HASMAILPACKAGES = convert(bit, 
    case when exists (select top 1 1
                        from @PACKAGES as [P]
                        inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [P].[PACKAGEID]
                       where [MKTPACKAGE].[CHANNELCODE] = 0)
         then 1 else 0 end);

  set @HASEMAILPACKAGES = convert(bit, 
    case when exists (select top 1 1
                        from @PACKAGES as [P]
                        inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [P].[PACKAGEID]
                       where [MKTPACKAGE].[CHANNELCODE] = 1)
         then 1 else 0 end);

  set @HASPHONEPACKAGES = convert(bit, 
    case when exists (select top 1 1
                        from @PACKAGES as [P]
                        inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [P].[PACKAGEID]
                       where [MKTPACKAGE].[CHANNELCODE] = 2)
         then 1 else 0 end);

  set @HASUNDEFINEDMAILPACKAGES = convert(bit, 
    case when exists (select top 1 1
                        from @PACKAGES as [P]
                        inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [P].[PACKAGEID]
                       where [MKTPACKAGE].[CHANNELCODE] = 0 and [MKTPACKAGE].[LETTERCODEID] is null and [MKTPACKAGE].[EXPORTDEFINITIONID] is null)
         then 1 else 0 end);

  set @HASUNDEFINEDEMAILPACKAGES = convert(bit, 
    case when exists (select top 1 1
                        from @PACKAGES as [P]
                        inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [P].[PACKAGEID]
                       where [MKTPACKAGE].[CHANNELCODE] = 1 and [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] = 0 and [MKTPACKAGE].[EXPORTDEFINITIONID] is null)
         then 1 else 0 end);

  set @HASUNDEFINEDPHONEPACKAGES = convert(bit, 
    case when exists (select top 1 1
                        from @PACKAGES as [P]
                        inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [P].[PACKAGEID]
                       where [MKTPACKAGE].[CHANNELCODE] = 2 and [MKTPACKAGE].[EXPORTDEFINITIONID] is null)
         then 1 else 0 end);

  set @HASDEFINEDPACKAGES = convert(bit, 
    case when exists (select top 1 1
                        from @PACKAGES as [P]
                        inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [P].[PACKAGEID]
                       where (([MKTPACKAGE].[CHANNELCODE] = 0 and ([MKTPACKAGE].[LETTERCODEID] is not null or [MKTPACKAGE].[EXPORTDEFINITIONID] is not null)) or 
                              ([MKTPACKAGE].[CHANNELCODE] = 1 and ([MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] > 0 or [MKTPACKAGE].[EXPORTDEFINITIONID] is not null)) or
                              ([MKTPACKAGE].[CHANNELCODE] = 2 and [MKTPACKAGE].[EXPORTDEFINITIONID] is not null)))
         then 1 else 0 end);

  set @DEFINEDPACKAGES = (
    select distinct
      [ID],
      [EXPORTDEFINITION],
      [COUNT],
      [PACKAGES]
    from (
      select distinct 
          [EXPORTDEFINITION].[ID],
          [EXPORTDEFINITION].[NAME] as [EXPORTDEFINITION],
          (select count(distinct [MKTPACKAGE].[NAME])
             from @PACKAGES as [P]
             inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [P].[PACKAGEID]
             inner join dbo.[LETTERCODE] on [LETTERCODE].[ID] = [MKTPACKAGE].[LETTERCODEID]
            where [LETTERCODE].[EXPORTDEFINITIONID] = [EXPORTDEFINITION].[ID])
          as [COUNT],
          dbo.[UFN_MKTSEGMENTATIONEXPORTPROCESS_GETSEGMENTATIONPACKAGELISTFOREXPORTDEFINITION]([EXPORTDEFINITION].[ID], @SEGMENTATIONID, default) as [PACKAGES]
        from @PACKAGES as [P]
inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [P].[PACKAGEID]
        inner join dbo.[LETTERCODE] on [LETTERCODE].[ID] = [MKTPACKAGE].[LETTERCODEID]
        inner join dbo.[EXPORTDEFINITION] on [EXPORTDEFINITION].[ID] = [LETTERCODE].[EXPORTDEFINITIONID]
      union all
      select distinct 
          [EXPORTDEFINITION].[ID],
          [EXPORTDEFINITION].[NAME] as [EXPORTDEFINITION],
          (select count(distinct [MKTPACKAGE].[NAME])
             from @PACKAGES as [P]
             inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [P].[PACKAGEID]
             inner join dbo.[MKTEXPORTDEFINITION] on ([MKTEXPORTDEFINITION].[NETCOMMUNITYDATASOURCEID] = [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] and [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] > 0)
             inner join dbo.[EXPORTDEFINITION] as [EMAILEXPORTDEFINITION] on [EMAILEXPORTDEFINITION].[ID] = [MKTEXPORTDEFINITION].[EXPORTDEFINITIONID]
            where [EMAILEXPORTDEFINITION].[ID] = [EXPORTDEFINITION].[ID])
          as [COUNT],
          dbo.[UFN_MKTSEGMENTATIONEXPORTPROCESS_GETSEGMENTATIONPACKAGELISTFOREXPORTDEFINITION]([EXPORTDEFINITION].[ID], @SEGMENTATIONID, default) as [PACKAGES]
        from @PACKAGES as [P]
        inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [P].[PACKAGEID]
        inner join dbo.[MKTEXPORTDEFINITION] on ([MKTEXPORTDEFINITION].[NETCOMMUNITYDATASOURCEID] = [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] and [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] > 0)
        inner join dbo.[EXPORTDEFINITION] on [EXPORTDEFINITION].[ID] = [MKTEXPORTDEFINITION].[EXPORTDEFINITIONID]
      union all
      select distinct 
          [EXPORTDEFINITION].[ID],
          [EXPORTDEFINITION].[NAME] as [EXPORTDEFINITION],
          (select count(distinct [MKTPACKAGE].[NAME])
             from @PACKAGES as [P]
             inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [P].[PACKAGEID]
            where [MKTPACKAGE].[EXPORTDEFINITIONID] = [EXPORTDEFINITION].[ID])
          as [COUNT],
          dbo.[UFN_MKTSEGMENTATIONEXPORTPROCESS_GETSEGMENTATIONPACKAGELISTFOREXPORTDEFINITION]([EXPORTDEFINITION].[ID], @SEGMENTATIONID, default) as [PACKAGES]
        from @PACKAGES as [P]
        inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [P].[PACKAGEID]
        inner join dbo.[EXPORTDEFINITION] on [EXPORTDEFINITION].[ID] = [MKTPACKAGE].[EXPORTDEFINITIONID]) as [TEMP]
     for xml raw('ITEM'), type, elements, root('DEFINEDPACKAGES'), binary base64);

  set @HASEXCLUDEDRECORDS =
    case when exists (select top 1 1
                      from dbo.[MKTSEGMENTATIONSEGMENT]
                      where [SEGMENTATIONID] = @SEGMENTATIONID
                      and [EXCLUDE] = 1)
         then 1 else 0 end;

  /* If using a communication template, load the lock settings from the template */
  if exists(select top 1 1 from dbo.[MKTCOMMUNICATIONTEMPLATE] where [MKTSEGMENTATIONID] = @SEGMENTATIONID)
    begin

      select
        @EXPORTDESCRIPTIONLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXPORTDESCRIPTIONLOCKED],
        @MAILEXPORTDEFINITIONIDLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[MAILEXPORTDEFINITIONIDLOCKED],
        @EMAILEXPORTDEFINITIONIDLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EMAILEXPORTDEFINITIONIDLOCKED],
        @PHONEEXPORTDEFINITIONIDLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[PHONEEXPORTDEFINITIONIDLOCKED]
      from dbo.[MKTCOMMUNICATIONTEMPLATE]
      inner join dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULT] on [MKTCOMMUNICATIONTEMPLATEDEFAULT].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID]
      where [MKTSEGMENTATIONID] = @SEGMENTATIONID;

    end

  return 0;