USP_DATAFORMTEMPLATE_EDITLOAD_MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE_3

The load procedure used by the edit dataform template "Marketing Acknowledgement Template Rule Edit Form 3"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@ACKNOWLEDGEMENTMAILINGTEMPLATEID	uniqueidentifier	INOUT	Marketing Acknowledgement Template ID
@SEGMENTID	uniqueidentifier	INOUT	Segment
@CODE	nvarchar(10)	INOUT	Code
@TESTSEGMENTCODE	nvarchar(10)	INOUT	Test segment
@PACKAGEID	uniqueidentifier	INOUT	Package
@PACKAGECODE	nvarchar(10)	INOUT	Package code
@RESPONSERATE	decimal(5, 2)	INOUT	Response rate
@GIFTAMOUNT	money	INOUT	Gift amount
@MAXDAYSCHECKED	bit	INOUT	Process transactions within
@MAXDAYS	int	INOUT	Maximum number of days
@MINQUANTITYCHECKED	bit	INOUT	Process when the number of transactions reaches
@MINQUANTITY	int	INOUT	Minimum quantity to process
@SEQUENCE	int	INOUT	Sequence
@ASKLADDERID	uniqueidentifier	INOUT	Ask ladder
@SOURCECODEID	uniqueidentifier	INOUT	Source code ID
@ACKNOWLEDGEMENTMAILINGTEMPLATESITEID	uniqueidentifier	INOUT	Marketing Acknowledgement Template site 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.
@ISBBEC	bit	INOUT	Is BBEC?
@OVERRIDEADDRESSPROCESSING	bit	INOUT	Override address processing / name format rules
@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
@CODEVALUEID	uniqueidentifier	INOUT	Code value ID
@PACKAGECODEVALUEID	uniqueidentifier	INOUT	Package code value ID
@TESTSEGMENTCODEVALUEID	uniqueidentifier	INOUT	Test segment code value ID
@CHANNELSOURCECODE	nvarchar(10)	INOUT	Channel source code
@CHANNELSOURCECODEVALUEID	uniqueidentifier	INOUT	Channel code value ID
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency ID
@PACKAGEADDDATAFORMCONTEXT	nvarchar(128)	INOUT	Package add data form context
@EXCLUDE	bit	INOUT	Exclude from effort but show counts
@RECORDSOURCEID	uniqueidentifier	INOUT
@LISTCODEVALUEID	uniqueidentifier	INOUT
@LISTCODE	nvarchar(10)	INOUT
@PACKAGECHANNELCODE	tinyint	INOUT
@ITEMLIST	xml	INOUT

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE_3]
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @ACKNOWLEDGEMENTMAILINGTEMPLATEID uniqueidentifier = null output,
  @SEGMENTID uniqueidentifier = null output,
  @CODE nvarchar(10) = null output,
  @TESTSEGMENTCODE nvarchar(10) = null output,
  @PACKAGEID uniqueidentifier = null output,
  @PACKAGECODE nvarchar(10) = null output,
  @RESPONSERATE decimal(5,2) = null output,
  @GIFTAMOUNT money = null output,
  @MAXDAYSCHECKED bit = null output,
  @MAXDAYS int = null output,
  @MINQUANTITYCHECKED bit = null output,
  @MINQUANTITY int = null output,
  @SEQUENCE int = null output,
  @ASKLADDERID uniqueidentifier = null output,
  @SOURCECODEID uniqueidentifier = null output,
  @ACKNOWLEDGEMENTMAILINGTEMPLATESITEID uniqueidentifier = null output,
  @TSLONG bigint = 0 output,
  @ISBBEC bit = null output,
  @OVERRIDEADDRESSPROCESSING bit = null output,
  @USEADDRESSPROCESSING bit = null output,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null output,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint = null output,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime = null output,
  @NAMEFORMATPARAMETERID uniqueidentifier = null output,
  @CODEVALUEID uniqueidentifier = null output,
  @PACKAGECODEVALUEID uniqueidentifier = null output,
  @TESTSEGMENTCODEVALUEID uniqueidentifier = null output,
  @CHANNELSOURCECODE nvarchar(10) = null output,
  @CHANNELSOURCECODEVALUEID uniqueidentifier = null output,
  @BASECURRENCYID uniqueidentifier = null output,
  @PACKAGEADDDATAFORMCONTEXT nvarchar(128) = null output,
  @EXCLUDE bit = null output,
  @RECORDSOURCEID uniqueidentifier = null output,
  @LISTCODEVALUEID uniqueidentifier = null output,
  @LISTCODE nvarchar(10) = null output,
  @PACKAGECHANNELCODE tinyint = null output,
  @ITEMLIST xml = null output
)
as
  set nocount on;

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

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

  select 
    @DATALOADED = 1,
    @ACKNOWLEDGEMENTMAILINGTEMPLATEID = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ACKNOWLEDGEMENTMAILINGTEMPLATEID],
    @ACKNOWLEDGEMENTMAILINGTEMPLATESITEID = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[SITEID],
    @SEQUENCE = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[SEQUENCE],
    @SEGMENTID = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[SEGMENTID],
    @RECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
    @EXCLUDE = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[EXCLUDE],
    @PACKAGEID = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[PACKAGEID],
    @PACKAGEADDDATAFORMCONTEXT = '1|' + convert(nvarchar(36), [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[BASECURRENCYID]),
    @PACKAGECHANNELCODE = [MKTPACKAGE].[CHANNELCODE],
    @ASKLADDERID = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ASKLADDERID],
    @MAXDAYSCHECKED = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[MAXDAYSCHECKED],
    @MAXDAYS = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[MAXDAYS],
    @MINQUANTITYCHECKED = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[MINQUANTITYCHECKED],
    @MINQUANTITY = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[MINQUANTITY],
    @RESPONSERATE = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[RESPONSERATE],
    @GIFTAMOUNT = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[GIFTAMOUNT],
    @SOURCECODEID = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[SOURCECODEID],
    @CODEVALUEID = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[PARTDEFINITIONVALUESID],
    @CODE = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[CODE],
    @LISTCODEVALUEID = [MKTRECORDSOURCE].[PARTDEFINITIONVALUESID],
    @LISTCODE = isnull([MKTRECORDSOURCE].[CODE], ''),
    @TESTSEGMENTCODEVALUEID = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[TESTPARTDEFINITIONVALUESID],
    @TESTSEGMENTCODE = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[TESTSEGMENTCODE],
  @PACKAGECODEVALUEID = [MKTPACKAGE].[PARTDEFINITIONVALUESID],
    @PACKAGECODE = [MKTPACKAGE].[CODE],
    @CHANNELSOURCECODEVALUEID = [MKTPACKAGE].[CHANNELPARTDEFINITIONVALUESID],
    @CHANNELSOURCECODE = [MKTPACKAGE].[CHANNELSOURCECODE],
    @ITEMLIST = dbo.[UFN_MKTACKNOWLEDGEMENTMAILINGTEMPLATESOURCECODEPART_GETITEMLIST2_TOITEMLISTXML]([MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ACKNOWLEDGEMENTMAILINGTEMPLATEID]),
    @OVERRIDEADDRESSPROCESSING = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[OVERRIDEADDRESSPROCESSING],
    @USEADDRESSPROCESSING = case when [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[USEADDRESSPROCESSING] else [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[USEADDRESSPROCESSING] end,
    @ADDRESSPROCESSINGOPTIONID = case when [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ADDRESSPROCESSINGOPTIONID] else [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[ADDRESSPROCESSINGOPTIONID] end,
    @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = case when [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE] else [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE] end,
    @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = case when [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE] else [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE] end,
    @NAMEFORMATPARAMETERID = case when [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[NAMEFORMATPARAMETERID] else [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[NAMEFORMATPARAMETERID] end,
    @TSLONG = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[TSLONG]
  from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE]
  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[SEGMENTID]
  inner join dbo.[MKTRECORDSOURCE] on [MKTRECORDSOURCE].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
  inner join dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATE] on [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ACKNOWLEDGEMENTMAILINGTEMPLATEID]
  left join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[PACKAGEID]
  where [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ID] = @ID;

  if @DATALOADED = 1
    begin
      select
        @BASECURRENCYID = [BASECURRENCYID]
      from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATE]
      where [ID] = @ACKNOWLEDGEMENTMAILINGTEMPLATEID;

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

  return 0;