USP_DATAFORMTEMPLATE_EDITLOAD_MKTMEMBERSHIPMAILINGTEMPLATERULE_2

The load procedure used by the edit dataform template "Membership Renewal Effort Template Rule Edit Form 2"

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.
@MEMBERSHIPMAILINGTEMPLATEID	uniqueidentifier	INOUT	Membership renewal effort 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
@SEQUENCE	int	INOUT	Sequence
@ASKLADDERID	uniqueidentifier	INOUT	Ask ladder
@SOURCECODEID	uniqueidentifier	INOUT	Source code ID
@MEMBERSHIPMAILINGTEMPLATESITEID	uniqueidentifier	INOUT	Membership renewal effort 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.
@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
@ISBBEC	bit	INOUT
@ISBBAC	bit	INOUT
@RECORDSOURCEID	uniqueidentifier	INOUT
@LISTCODEVALUEID	uniqueidentifier	INOUT
@LISTCODE	nvarchar(10)	INOUT
@PACKAGECHANNELCODE	tinyint	INOUT
@ITEMLIST	xml	INOUT

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTMEMBERSHIPMAILINGTEMPLATERULE_2]
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @MEMBERSHIPMAILINGTEMPLATEID 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,
  @SEQUENCE int = null output,
  @ASKLADDERID uniqueidentifier = null output,
  @SOURCECODEID uniqueidentifier = null output,
  @MEMBERSHIPMAILINGTEMPLATESITEID uniqueidentifier = null output,
  @TSLONG bigint = 0 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,
  @ISBBEC bit = 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
)
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);
  set @ISBBAC = dbo.[UFN_MKTCOMMON_PRODUCTISALTRU]();

  /* load the segment */
  select 
    @DATALOADED = 1,
    @MEMBERSHIPMAILINGTEMPLATEID = [MKTMEMBERSHIPMAILINGTEMPLATERULE].[MEMBERSHIPMAILINGTEMPLATEID],
    @MEMBERSHIPMAILINGTEMPLATESITEID = [MKTMEMBERSHIPMAILINGTEMPLATE].[SITEID],
    @BASECURRENCYID = [MKTMEMBERSHIPMAILINGTEMPLATERULE].[BASECURRENCYID],
    @SEQUENCE = [MKTMEMBERSHIPMAILINGTEMPLATERULE].[SEQUENCE],
    @SEGMENTID = [MKTMEMBERSHIPMAILINGTEMPLATERULE].[SEGMENTID],
    @RECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
    @EXCLUDE = [MKTMEMBERSHIPMAILINGTEMPLATERULE].[EXCLUDE],
    @PACKAGEID = [MKTMEMBERSHIPMAILINGTEMPLATERULE].[PACKAGEID],
    @PACKAGEADDDATAFORMCONTEXT = '2|' + convert(nvarchar(36), [MKTMEMBERSHIPMAILINGTEMPLATERULE].[BASECURRENCYID]),
    @ASKLADDERID = [MKTMEMBERSHIPMAILINGTEMPLATERULE].[ASKLADDERID],
    @RESPONSERATE = [MKTMEMBERSHIPMAILINGTEMPLATERULE].[RESPONSERATE],
    @GIFTAMOUNT = [MKTMEMBERSHIPMAILINGTEMPLATERULE].[GIFTAMOUNT],
    @SOURCECODEID = [MKTMEMBERSHIPMAILINGTEMPLATE].[SOURCECODEID],
    @CODEVALUEID = [MKTMEMBERSHIPMAILINGTEMPLATERULE].[PARTDEFINITIONVALUESID],
    @CODE = [MKTMEMBERSHIPMAILINGTEMPLATERULE].[CODE],
    @LISTCODEVALUEID = [MKTRECORDSOURCE].[PARTDEFINITIONVALUESID],
    @LISTCODE = isnull([MKTRECORDSOURCE].[CODE], ''),
    @TESTSEGMENTCODEVALUEID = [MKTMEMBERSHIPMAILINGTEMPLATERULE].[TESTPARTDEFINITIONVALUESID],
    @TESTSEGMENTCODE = [MKTMEMBERSHIPMAILINGTEMPLATERULE].[TESTSEGMENTCODE],
    @PACKAGECODEVALUEID = [MKTPACKAGE].[PARTDEFINITIONVALUESID],
    @PACKAGECODE = [MKTPACKAGE].[CODE],
    @CHANNELSOURCECODEVALUEID = [MKTPACKAGE].[CHANNELPARTDEFINITIONVALUESID],
    @CHANNELSOURCECODE = [MKTPACKAGE].[CHANNELSOURCECODE],
    @ITEMLIST = dbo.[UFN_MKTMEMBERSHIPMAILINGTEMPLATESOURCECODEPART_GETITEMLIST2_TOITEMLISTXML]([MKTMEMBERSHIPMAILINGTEMPLATERULE].[MEMBERSHIPMAILINGTEMPLATEID]),
    @OVERRIDEADDRESSPROCESSING = [MKTMEMBERSHIPMAILINGTEMPLATERULE].[OVERRIDEADDRESSPROCESSING],
    @USEADDRESSPROCESSING = case when [MKTMEMBERSHIPMAILINGTEMPLATERULE].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTMEMBERSHIPMAILINGTEMPLATERULE].[USEADDRESSPROCESSING] else [MKTMEMBERSHIPMAILINGTEMPLATE].[USEADDRESSPROCESSING] end,
    @ADDRESSPROCESSINGOPTIONID = case when [MKTMEMBERSHIPMAILINGTEMPLATERULE].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTMEMBERSHIPMAILINGTEMPLATERULE].[ADDRESSPROCESSINGOPTIONID] else [MKTMEMBERSHIPMAILINGTEMPLATE].[ADDRESSPROCESSINGOPTIONID] end,
    @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = case when [MKTMEMBERSHIPMAILINGTEMPLATERULE].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTMEMBERSHIPMAILINGTEMPLATERULE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE] else [MKTMEMBERSHIPMAILINGTEMPLATE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE] end,
    @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = case when [MKTMEMBERSHIPMAILINGTEMPLATERULE].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTMEMBERSHIPMAILINGTEMPLATERULE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE] else [MKTMEMBERSHIPMAILINGTEMPLATE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE] end,
    @NAMEFORMATPARAMETERID = case when [MKTMEMBERSHIPMAILINGTEMPLATERULE].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTMEMBERSHIPMAILINGTEMPLATERULE].[NAMEFORMATPARAMETERID] else [MKTMEMBERSHIPMAILINGTEMPLATE].[NAMEFORMATPARAMETERID] end,
    @TSLONG = [MKTMEMBERSHIPMAILINGTEMPLATERULE].[TSLONG]
  from dbo.[MKTMEMBERSHIPMAILINGTEMPLATERULE]
  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTMEMBERSHIPMAILINGTEMPLATERULE].[SEGMENTID]
  inner join dbo.[MKTRECORDSOURCE] on [MKTRECORDSOURCE].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
  left outer join dbo.[MKTMEMBERSHIPMAILINGTEMPLATE] on [MKTMEMBERSHIPMAILINGTEMPLATE].[ID] = [MKTMEMBERSHIPMAILINGTEMPLATERULE].[MEMBERSHIPMAILINGTEMPLATEID]
  left outer join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTMEMBERSHIPMAILINGTEMPLATERULE].[PACKAGEID]
  where [MKTMEMBERSHIPMAILINGTEMPLATERULE].[ID] = @ID;

  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;

  return 0;