USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONSEGMENTLIST_PRELOAD

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

Parameters

Parameter	Parameter Type	Mode	Description
@SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE	nvarchar(100)	IN	Input parameter indicating the context ID for the record being added.
@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
@BUSINESSUNITS	xml	INOUT	Business units
@ISBBEC	bit	INOUT	Is BBEC?
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency ID
@PACKAGEADDDATAFORMCONTEXT	nvarchar(128)	INOUT	Package add data form context
@RECORDSOURCEID	uniqueidentifier	INOUT
@ITEMLIST	xml	INOUT
@PACKAGECHANNELCODE	tinyint	INOUT
@EFFORTOVERRIDESBUSINESSUNITS	bit	INOUT
@CHANNELCODE	tinyint	INOUT
@ALLOWEFFORTBUSINESSUNITSOVERRIDE	bit	INOUT

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONSEGMENTLIST_PRELOAD]
(
  @SEGMENTATIONIDMARKETINGPLANBRIEFIDSEQUENCE nvarchar(100),
  @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,
  @BUSINESSUNITS xml = null output,
  @ISBBEC bit = null output,
  @BASECURRENCYID uniqueidentifier = null output,
  @PACKAGEADDDATAFORMCONTEXT nvarchar(128) = null output,
  @RECORDSOURCEID uniqueidentifier = null output,
  @ITEMLIST xml = null output,
  @PACKAGECHANNELCODE tinyint = null output,
  @EFFORTOVERRIDESBUSINESSUNITS bit = null output,
  @CHANNELCODE tinyint = null output,
  @ALLOWEFFORTBUSINESSUNITSOVERRIDE 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],
    @PACKAGECODE = [MKTPACKAGE].[CODE],
    @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
    @SOURCECODEID = [MKTSEGMENTATION].[SOURCECODEID],
    @SEGMENTATIONSITEID = [MKTSEGMENTATION].[SITEID],
    @ACTIVE = [MKTSEGMENTATION].[ACTIVE],
    @BASECURRENCYID = [MKTSEGMENTATION].[BASECURRENCYID],
    @PACKAGEADDDATAFORMCONTEXT = '0|' + convert(nvarchar(36), [MKTSEGMENTATION].[BASECURRENCYID]),
    @ITEMLIST = dbo.[UFN_MKTSOURCECODEPART_GETITEMLIST2_TOITEMLISTXML]([MKTSEGMENTATION].[ID]),
    @EFFORTOVERRIDESBUSINESSUNITS = [MKTSEGMENTATION].[OVERRIDEBUSINESSUNITS],
    @ALLOWEFFORTBUSINESSUNITSOVERRIDE = isnull([MKTCOMMUNICATIONTEMPLATE].[ALLOWEFFORTBUSINESSUNITSOVERRIDE], 1),
    @CHANNELCODE = isnull([MKTSEGMENTATION].[CHANNELCODE], 255)
  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;

  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
  else
    set @OVERRIDEBUSINESSUNITS = 0;

  return 0;