USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONSEGMENTGROUP_PRELOAD

The load procedure used by the edit dataform template "Marketing Effort Segment Group Add Form"

Parameters

Parameter	Parameter Type	Mode	Description
@SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE	nvarchar(100)	IN	Input parameter indicating the context ID for the record being added.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SOURCECODEID	uniqueidentifier	INOUT	Source code ID
@ASKLADDERS	xml	INOUT	Ask ladders
@SEGMENTATIONSITEID	uniqueidentifier	INOUT	Marketing effort site ID
@PACKAGEID	uniqueidentifier	INOUT	Package
@PACKAGECODE	nvarchar(10)	INOUT	Package code
@RESPONSERATE	decimal(5, 2)	INOUT	Response rate
@GIFTAMOUNT	money	INOUT	Gift amount
@ISBBEC	bit	INOUT	Is BBEC?
@USEADDRESSPROCESSING	bit	INOUT	Use address processing?
@ADDRESSPROCESSINGOPTIONID	uniqueidentifier	INOUT	Address processing options
@ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE	tinyint	INOUT	Consider seasonal addresses as of
@ADDRESSPROCESSINGOPTIONSEASONALASOFDATE	datetime	INOUT	Consider seasonal addresses as of
@NAMEFORMATPARAMETERID	uniqueidentifier	INOUT	Name format options
@BUSINESSUNITS	xml	INOUT	Business units
@MAILINGTYPECODE	tinyint	INOUT	Mailing type code
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency ID
@PACKAGEADDDATAFORMCONTEXT	nvarchar(128)	INOUT	Package add data form context
@SEGMENTGROUPADDDATAFORMCONTEXT	nvarchar(38)	INOUT	Segment group add data form context
@PACKAGEPARTDEFINITIONID	uniqueidentifier	INOUT
@TESTSEGMENTPARTDEFINITIONID	uniqueidentifier	INOUT
@EFFORTOVERRIDESBUSINESSUNITS	bit	INOUT
@PACKAGECODEVALUEID	uniqueidentifier	INOUT
@CHANNELPARTDEFINITIONID	uniqueidentifier	INOUT
@CHANNELCODEVALUEID	uniqueidentifier	INOUT
@CHANNELCODE	nvarchar(10)	INOUT
@EFFORTCHANNELCODE	tinyint	INOUT
@ALLOWEFFORTBUSINESSUNITSOVERRIDE	bit	INOUT
@ALLOWEFFORTADDRESSPROCESSINGOVERRIDE	bit	INOUT

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONSEGMENTGROUP_PRELOAD]
(
  @SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE nvarchar(100),
  @CURRENTAPPUSERID uniqueidentifier,
  @SOURCECODEID uniqueidentifier = null output,
  @ASKLADDERS xml = null output,
  @SEGMENTATIONSITEID uniqueidentifier = null output,
  @PACKAGEID uniqueidentifier = null output,
  @PACKAGECODE nvarchar(10) = null output,
  @RESPONSERATE decimal(5,2) = null output,
  @GIFTAMOUNT money = null output,
  @ISBBEC bit = null output,
  @USEADDRESSPROCESSING bit = null output,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null output,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint = null output,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime = null output,
  @NAMEFORMATPARAMETERID uniqueidentifier = null output,
  @BUSINESSUNITS xml = null output,
  @MAILINGTYPECODE tinyint = null output,
  @BASECURRENCYID uniqueidentifier = null output,
  @PACKAGEADDDATAFORMCONTEXT nvarchar(128) = null output,
  @SEGMENTGROUPADDDATAFORMCONTEXT nvarchar(38) = null output,
  @PACKAGEPARTDEFINITIONID uniqueidentifier = null output,
  @TESTSEGMENTPARTDEFINITIONID uniqueidentifier = null output,
  @EFFORTOVERRIDESBUSINESSUNITS bit = null output,
  @PACKAGECODEVALUEID uniqueidentifier = null output,
  @CHANNELPARTDEFINITIONID uniqueidentifier = null output,
  @CHANNELCODEVALUEID uniqueidentifier = null output,
  @CHANNELCODE nvarchar(10) = null output,
  @EFFORTCHANNELCODE tinyint = null output,
  @ALLOWEFFORTBUSINESSUNITSOVERRIDE bit = null output,
  @ALLOWEFFORTADDRESSPROCESSINGOVERRIDE bit = null output
)
as
  set nocount on;

  declare @SEGMENTATIONID uniqueidentifier;
  declare @MARKETINGPLANBRIEFID uniqueidentifier;
  declare @OVERRIDEBUSINESSUNITS bit;
  declare @ACTIVE bit;

  if charindex('|', @SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE, 1) > 0
    begin
      set @SEGMENTATIONID = convert(uniqueidentifier, substring(@SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE, 1, charindex('|', @SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE, 1) - 1));

      if charindex('|', @SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE, charindex('|', @SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE, 1)) > 0
        if len(substring(@SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE, charindex('|', @SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE, 1) + 1, charindex('|', @SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE, charindex('|', @SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE, 1)) - 1)) = 36
          set @MARKETINGPLANBRIEFID = convert(uniqueidentifier, substring(@SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE, charindex('|', @SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE, 1) + 1, charindex('|', @SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE, charindex('|', @SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE, 1)) - 1));
    end
  else
    set @SEGMENTATIONID = convert(uniqueidentifier, @SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE);

  select
    @PACKAGEID = [MKTMARKETINGPLANBRIEF].[PACKAGEID],
    @PACKAGECODEVALUEID = [MKTPACKAGE].[PARTDEFINITIONVALUESID],
    @PACKAGECODE = [MKTPACKAGE].[CODE],
    @CHANNELCODEVALUEID = [MKTPACKAGE].[CHANNELPARTDEFINITIONVALUESID],
    @CHANNELCODE = [MKTPACKAGE].[CHANNELSOURCECODE],
    @RESPONSERATE = [MKTMARKETINGPLANBRIEF].[RESPONSERATEGOAL],
    @GIFTAMOUNT = [MKTMARKETINGPLANBRIEF].[AVERAGEREVENUEGOAL]
  from dbo.[MKTMARKETINGPLANBRIEF]
  left outer join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTMARKETINGPLANBRIEF].[PACKAGEID]
  where [MKTMARKETINGPLANBRIEF].[ID] = @MARKETINGPLANBRIEFID;

  -- check if the mailing is currently being activated...

  declare @R int;
  exec @R = dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @SEGMENTATIONID;
  if @R <> 0
    return 1;

  select
    @MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
    @SOURCECODEID = [MKTSEGMENTATION].[SOURCECODEID],
    @SEGMENTATIONSITEID = [MKTSEGMENTATION].[SITEID],
    @BASECURRENCYID = [MKTSEGMENTATION].[BASECURRENCYID],
    @PACKAGEADDDATAFORMCONTEXT = convert(nvarchar(3), [MKTSEGMENTATION].[MAILINGTYPECODE]) + '|' + convert(nvarchar(36), [MKTSEGMENTATION].[BASECURRENCYID]),
    @SEGMENTGROUPADDDATAFORMCONTEXT = '1|' + convert(nvarchar(36), [MKTSEGMENTATION].[BASECURRENCYID]),
    @EFFORTOVERRIDESBUSINESSUNITS = [MKTSEGMENTATION].[OVERRIDEBUSINESSUNITS],
    @ALLOWEFFORTBUSINESSUNITSOVERRIDE = isnull([MKTCOMMUNICATIONTEMPLATE].[ALLOWEFFORTBUSINESSUNITSOVERRIDE], 1),
    @EFFORTCHANNELCODE = isnull([MKTSEGMENTATION].[CHANNELCODE], 255),
    @ALLOWEFFORTADDRESSPROCESSINGOVERRIDE = isnull([MKTCOMMUNICATIONTEMPLATE].[ALLOWEFFORTADDRESSPROCESSINGOVERRIDE], 1)
  from dbo.[MKTSEGMENTATION]
  left outer join dbo.[MKTCOMMUNICATIONTEMPLATE] on [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  where [MKTSEGMENTATION].[ID] = @SEGMENTATIONID;

  set @ASKLADDERS = (select [QUERYVIEWCATALOG].[ID] as [RECORDSOURCEID], [QUERYVIEWCATALOG].[DISPLAYNAME] as [RECORDSOURCENAME]
                     from dbo.[MKTRECORDSOURCE]
                     inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTRECORDSOURCE].[ID]
                     where dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTRECORDSOURCE].[ID]) = 1
                     for xml raw('ITEM'), type, elements, root('ASKLADDERS'), binary base64);

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

  if @ISBBEC = 1
    begin
      select 
        @USEADDRESSPROCESSING = [USEADDRESSPROCESSING],
        @ADDRESSPROCESSINGOPTIONID = [ADDRESSPROCESSINGOPTIONID],
        @NAMEFORMATPARAMETERID = [NAMEFORMATPARAMETERID],
        @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = [ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
        @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = [ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
        @OVERRIDEBUSINESSUNITS = [OVERRIDEBUSINESSUNITS],
        @ACTIVE = [MKTSEGMENTATION].[ACTIVE]
      from dbo.[MKTSEGMENTATION]
      where [ID] = @SEGMENTATIONID;

      if @ADDRESSPROCESSINGOPTIONID is null
        select top 1 @ADDRESSPROCESSINGOPTIONID = [ID] from dbo.[ADDRESSPROCESSINGOPTION] where [ISDEFAULT] = 1 and dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [SITEID]) = 1;
      if @NAMEFORMATPARAMETERID is null
        select top 1 @NAMEFORMATPARAMETERID = [ID] from dbo.[NAMEFORMATPARAMETER] where [ISDEFAULT] = 1 and dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [SITEID]) = 1;

      if @EFFORTOVERRIDESBUSINESSUNITS = 1
        set @BUSINESSUNITS =  dbo.[UFN_MKTSEGMENTATIONBUSINESSUNIT_GETBUSINESSUNITS_TOITEMLISTXML](@SEGMENTATIONID) 
      else
        begin
          declare @APPEALSYSTEMID uniqueidentifier;

          select 
            @APPEALSYSTEMID = convert(uniqueidentifier, [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID])
          from dbo.[MKTAPPEALRECORDSOURCE] 
          inner join [QUERYVIEWCATALOG] on [MKTAPPEALRECORDSOURCE].[ID] = [QUERYVIEWCATALOG].[ID]
          left join [MKTSEGMENTATIONACTIVATE] on ([MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = [MKTAPPEALRECORDSOURCE].[ID] and [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = @SEGMENTATIONID)
          where (@ACTIVE = 0 or (@ACTIVE = 1 and [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] <> ''))
          and (dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([QUERYVIEWCATALOG].[ID]) = 1);

          if @APPEALSYSTEMID is not null
            set @BUSINESSUNITS = dbo.[UFN_APPEALBUSINESSUNIT_GETBUSINESSUNITS_TOITEMLISTXML](@APPEALSYSTEMID);
        end
    end
  else
    begin
      set @USEADDRESSPROCESSING = 0;
      set @ADDRESSPROCESSINGOPTIONID = null;
      set @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = 0;
      set @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = null;
      set @NAMEFORMATPARAMETERID = null;
      set @OVERRIDEBUSINESSUNITS = 0;
    end

  select @PACKAGEPARTDEFINITIONID = [ID] from dbo.[MKTSOURCECODEPARTDEFINITION] where [ITEMTYPECODE] = 2;
  select @CHANNELPARTDEFINITIONID = [ID] from dbo.[MKTSOURCECODEPARTDEFINITION] where [ITEMTYPECODE] = 3;
  select @TESTSEGMENTPARTDEFINITIONID = [ID] from dbo.[MKTSOURCECODEPARTDEFINITION] where [ITEMTYPECODE] = 7;

  return 0;