USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONSEGMENT_PRELOAD

The load procedure used by the edit dataform template "Marketing Effort Segment 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
@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
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency ID
@PACKAGEADDDATAFORMCONTEXT	nvarchar(128)	INOUT	Package add data form context
@ISBBAC	bit	INOUT	Is BBAC?
@RECORDSOURCEID	uniqueidentifier	INOUT
@LISTCODEVALUEID	uniqueidentifier	INOUT
@LISTCODE	nvarchar(10)	INOUT
@PACKAGECHANNELCODE	tinyint	INOUT
@ITEMLIST	xml	INOUT
@EFFORTOVERRIDESBUSINESSUNITS	bit	INOUT
@PACKAGECODEVALUEID	uniqueidentifier	INOUT
@CHANNELSOURCECODE	nvarchar(10)	INOUT
@CHANNELSOURCECODEVALUEID	uniqueidentifier	INOUT
@CHANNELCODE	tinyint	INOUT
@ALLOWEFFORTBUSINESSUNITSOVERRIDE	bit	INOUT
@ALLOWEFFORTADDRESSPROCESSINGOVERRIDE	bit	INOUT

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONSEGMENT_PRELOAD]
(
  @SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE nvarchar(100),
  @CURRENTAPPUSERID uniqueidentifier,
  @SOURCECODEID uniqueidentifier = 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,
  @BASECURRENCYID uniqueidentifier = null output,
  @PACKAGEADDDATAFORMCONTEXT nvarchar(128) = null output,
  @ISBBAC bit = null output,
  @RECORDSOURCEID uniqueidentifier = null output,
  @LISTCODEVALUEID uniqueidentifier = null output,
  @LISTCODE nvarchar(10) = null output,
  @PACKAGECHANNELCODE tinyint = null output,
  @ITEMLIST xml = null output,
  @EFFORTOVERRIDESBUSINESSUNITS bit = null output,
  @PACKAGECODEVALUEID uniqueidentifier = null output,
  @CHANNELSOURCECODE nvarchar(10) = null output,
  @CHANNELSOURCECODEVALUEID uniqueidentifier = null output,
  @CHANNELCODE 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)

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

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

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

  select
    @SEGMENTATIONSITEID = [MKTSEGMENTATION].[SITEID],
    @BASECURRENCYID = [MKTSEGMENTATION].[BASECURRENCYID],
    @PACKAGEADDDATAFORMCONTEXT = '0|' + convert(nvarchar(36), [MKTSEGMENTATION].[BASECURRENCYID]),
    @SOURCECODEID = [MKTSEGMENTATION].[SOURCECODEID],
    @ITEMLIST = dbo.[UFN_MKTSOURCECODEPART_GETITEMLIST2_TOITEMLISTXML]([MKTSEGMENTATION].[ID]),
    @EFFORTOVERRIDESBUSINESSUNITS = [MKTSEGMENTATION].[OVERRIDEBUSINESSUNITS],
    @ALLOWEFFORTBUSINESSUNITSOVERRIDE = isnull([MKTCOMMUNICATIONTEMPLATE].[ALLOWEFFORTBUSINESSUNITSOVERRIDE], 1),
    @CHANNELCODE = 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 @ISBBEC = (case when dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0 then 1 else 0 end);
  set @ISBBAC = dbo.[UFN_MKTCOMMON_PRODUCTISALTRU]();

  if @ISBBEC = 1 or @ISBBAC = 1
    begin
      select
        @USEADDRESSPROCESSING = [USEADDRESSPROCESSING],
        @ADDRESSPROCESSINGOPTIONID = [ADDRESSPROCESSINGOPTIONID],
        @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = [ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
        @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = [ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
        @NAMEFORMATPARAMETERID = [NAMEFORMATPARAMETERID],
        @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 @ISBBEC = 1
        begin
          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
    end
  else
    begin
      set @USEADDRESSPROCESSING = 0;
      set @ADDRESSPROCESSINGOPTIONID = null;
      set @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = 0;
      set @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = null;
      set @NAMEFORMATPARAMETERID = null;
      set @OVERRIDEBUSINESSUNITS = 0;
    end

  return 0;