USP_DATAFORMTEMPLATE_EDITLOAD_MKTSPONSORSHIPMAILINGTEMPLATERULE_2

The load procedure used by the edit dataform template "Sponsorship Effort Template Rule Edit Form"

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.
@SPONSORSHIPMAILINGTEMPLATEID	uniqueidentifier	INOUT	Sponsorship 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
@SPONSORSHIPMAILINGTEMPLATESITEID	uniqueidentifier	INOUT	Sponsorship 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
@RECORDSOURCEID	uniqueidentifier	INOUT
@LISTCODEVALUEID	uniqueidentifier	INOUT
@LISTCODE	nvarchar(10)	INOUT
@PACKAGECHANNELCODE	tinyint	INOUT
@ITEMLIST	xml	INOUT

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTSPONSORSHIPMAILINGTEMPLATERULE_2]
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @SPONSORSHIPMAILINGTEMPLATEID 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,
  @SPONSORSHIPMAILINGTEMPLATESITEID 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,
  @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 = 1 -- BBEC-only form; not necessary -> (case when dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0 then 1 else 0 end);


  /* load the segment */
  select 
    @DATALOADED = 1,
    @SPONSORSHIPMAILINGTEMPLATEID = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[SPONSORSHIPMAILINGTEMPLATEID],
    @SPONSORSHIPMAILINGTEMPLATESITEID = [MKTSPONSORSHIPMAILINGTEMPLATE].[SITEID],
    @BASECURRENCYID = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[BASECURRENCYID],
    @SEQUENCE = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[SEQUENCE],
    @SEGMENTID = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[SEGMENTID],
    @RECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
    @EXCLUDE = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[EXCLUDE],
    @PACKAGEID = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[PACKAGEID],
    @PACKAGEADDDATAFORMCONTEXT = '3|' + convert(nvarchar(36), [MKTSPONSORSHIPMAILINGTEMPLATERULE].[BASECURRENCYID]),
    @ASKLADDERID = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[ASKLADDERID],
    @RESPONSERATE = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[RESPONSERATE],
    @GIFTAMOUNT = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[GIFTAMOUNT],
    @SOURCECODEID = [MKTSPONSORSHIPMAILINGTEMPLATE].[SOURCECODEID],
    @CODEVALUEID = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[PARTDEFINITIONVALUESID],
    @CODE = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[CODE],
    @LISTCODEVALUEID = [MKTRECORDSOURCE].[PARTDEFINITIONVALUESID],
    @LISTCODE = isnull([MKTRECORDSOURCE].[CODE], ''),
    @TESTSEGMENTCODEVALUEID = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[TESTPARTDEFINITIONVALUESID],
    @TESTSEGMENTCODE = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[TESTSEGMENTCODE],
    @PACKAGECHANNELCODE = [MKTPACKAGE].[CHANNELCODE],
    @PACKAGECODEVALUEID = [MKTPACKAGE].[PARTDEFINITIONVALUESID],
    @PACKAGECODE = [MKTPACKAGE].[CODE],
    @CHANNELSOURCECODEVALUEID = [MKTPACKAGE].[CHANNELPARTDEFINITIONVALUESID],
    @CHANNELSOURCECODE = [MKTPACKAGE].[CHANNELSOURCECODE],
    @ITEMLIST = dbo.[UFN_MKTSPONSORSHIPMAILINGTEMPLATESOURCECODEPART_GETITEMLIST2_TOITEMLISTXML](@SPONSORSHIPMAILINGTEMPLATEID),
    @OVERRIDEADDRESSPROCESSING = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[OVERRIDEADDRESSPROCESSING],
    @USEADDRESSPROCESSING = case when [MKTSPONSORSHIPMAILINGTEMPLATERULE].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTSPONSORSHIPMAILINGTEMPLATERULE].[USEADDRESSPROCESSING] else [MKTSPONSORSHIPMAILINGTEMPLATE].[USEADDRESSPROCESSING] end,
    @ADDRESSPROCESSINGOPTIONID = case when [MKTSPONSORSHIPMAILINGTEMPLATERULE].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTSPONSORSHIPMAILINGTEMPLATERULE].[ADDRESSPROCESSINGOPTIONID] else [MKTSPONSORSHIPMAILINGTEMPLATE].[ADDRESSPROCESSINGOPTIONID] end,
    @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = case when [MKTSPONSORSHIPMAILINGTEMPLATERULE].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTSPONSORSHIPMAILINGTEMPLATERULE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE] else [MKTSPONSORSHIPMAILINGTEMPLATE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE] end,
    @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = case when [MKTSPONSORSHIPMAILINGTEMPLATERULE].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTSPONSORSHIPMAILINGTEMPLATERULE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE] else [MKTSPONSORSHIPMAILINGTEMPLATE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE] end,
    @NAMEFORMATPARAMETERID = case when [MKTSPONSORSHIPMAILINGTEMPLATERULE].[OVERRIDEADDRESSPROCESSING] = 1 then [MKTSPONSORSHIPMAILINGTEMPLATERULE].[NAMEFORMATPARAMETERID] else [MKTSPONSORSHIPMAILINGTEMPLATE].[NAMEFORMATPARAMETERID] end,
    @TSLONG = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[TSLONG]
  from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE]
  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[SEGMENTID]
  inner join dbo.[MKTRECORDSOURCE] on [MKTRECORDSOURCE].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
  left outer join dbo.[MKTSPONSORSHIPMAILINGTEMPLATE] on [MKTSPONSORSHIPMAILINGTEMPLATE].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[SPONSORSHIPMAILINGTEMPLATEID]
  left outer join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[PACKAGEID]
  where [MKTSPONSORSHIPMAILINGTEMPLATERULE].[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;