USP_DATAFORMTEMPLATE_ADD_MKTMEMBERSHIPMAILINGTEMPLATERULE

The save procedure used by the add dataform template "Membership Renewal Effort Template Rule Add Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@MEMBERSHIPMAILINGTEMPLATEID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@SEGMENTID	uniqueidentifier	IN	Segment
@CODE	nvarchar(10)	IN	Code
@TESTSEGMENTCODE	nvarchar(10)	IN	Test segment
@PACKAGEID	uniqueidentifier	IN	Package
@PACKAGECODE	nvarchar(10)	IN	Package code
@RESPONSERATE	decimal(5, 2)	IN	Response rate
@GIFTAMOUNT	money	IN	Gift amount
@ASKLADDERID	uniqueidentifier	IN	Ask ladder
@OVERRIDEADDRESSPROCESSING	bit	IN	Override address processing / name format rules
@USEADDRESSPROCESSING	bit	IN	Use address processing?
@ADDRESSPROCESSINGOPTIONID	uniqueidentifier	IN	Address processing options
@ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE	tinyint	IN	Consider seasonal addresses as of
@ADDRESSPROCESSINGOPTIONSEASONALASOFDATE	datetime	IN	Consider seasonal addresses as of
@NAMEFORMATPARAMETERID	uniqueidentifier	IN	Name format options
@CODEVALUEID	uniqueidentifier	IN	Code value ID
@PACKAGECODEVALUEID	uniqueidentifier	IN	Package code value ID
@TESTSEGMENTCODEVALUEID	uniqueidentifier	IN	Test segment code value ID
@CHANNELSOURCECODE	nvarchar(10)	IN	Channel source code
@CHANNELSOURCECODEVALUEID	uniqueidentifier	IN	Channel code value ID
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@EXCLUDE	bit	IN	Exclude from effort but show counts

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_ADD_MKTMEMBERSHIPMAILINGTEMPLATERULE]
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,    
  @MEMBERSHIPMAILINGTEMPLATEID uniqueidentifier,
  @SEGMENTID uniqueidentifier,
  @CODE nvarchar(10) = '',
  @TESTSEGMENTCODE nvarchar(10) = '',
  @PACKAGEID uniqueidentifier = null,
  @PACKAGECODE nvarchar(10) = '',
  @RESPONSERATE decimal(5,2) = 5,
  @GIFTAMOUNT money,
  @ASKLADDERID uniqueidentifier = null,
  @OVERRIDEADDRESSPROCESSING bit = 0,
  @USEADDRESSPROCESSING bit = 0,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint = 0,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime = null,
  @NAMEFORMATPARAMETERID uniqueidentifier = null,
  @CODEVALUEID uniqueidentifier = null,
  @PACKAGECODEVALUEID uniqueidentifier = null,
  @TESTSEGMENTCODEVALUEID uniqueidentifier = null,
  @CHANNELSOURCECODE nvarchar(10) = '',
  @CHANNELSOURCECODEVALUEID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @EXCLUDE bit = 0
)
as
  set nocount on;

  declare @CURRENTDATE datetime;
  declare @OLDCODE nvarchar(10);
  declare @SEQUENCE int;
  declare @BASECURRENCYID uniqueidentifier;
  declare @ORGANIZATIONCURRENCYEXCHANGERATEID uniqueidentifier;
  declare @ORGANIZATIONGIFTAMOUNT money;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;

  begin try
    if @ID is null set @ID = newid();

    if @CHANGEAGENTID is null exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

    set @CURRENTDATE = getdate();

    if @EXCLUDE = 1
      begin
        set @PACKAGEID = null;
        set @ASKLADDERID = null;
        set @RESPONSERATE = 0;
        set @GIFTAMOUNT = 0;
        set @OVERRIDEADDRESSPROCESSING = 0;
        set @CODE = '';
        set @CODEVALUEID = null;
        set @PACKAGECODE = '';
        set @PACKAGECODEVALUEID = null;
        set @CHANNELSOURCECODE = '';
        set @CHANNELSOURCECODEVALUEID = null;
        set @TESTSEGMENTCODE = '';
        set @TESTSEGMENTCODEVALUEID = null;
      end

    select
      @SEQUENCE = isnull(max([SEQUENCE]), 0) + 1
    from dbo.[MKTMEMBERSHIPMAILINGTEMPLATERULE]
    where [MEMBERSHIPMAILINGTEMPLATEID] = @MEMBERSHIPMAILINGTEMPLATEID;

    select
      @BASECURRENCYID = [BASECURRENCYID]
    from dbo.[MKTMEMBERSHIPMAILINGTEMPLATE]
    where [ID] = @MEMBERSHIPMAILINGTEMPLATEID;

    set @ORGANIZATIONCURRENCYID = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

    if @ORGANIZATIONCURRENCYID = @BASECURRENCYID
      set @ORGANIZATIONGIFTAMOUNT = @GIFTAMOUNT;
    else
      begin
        set @ORGANIZATIONCURRENCYEXCHANGERATEID = dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null);
        set @ORGANIZATIONGIFTAMOUNT = dbo.[UFN_CURRENCY_CONVERT](@GIFTAMOUNT, @ORGANIZATIONCURRENCYEXCHANGERATEID);
      end

    set @EXCLUDE = isnull(@EXCLUDE, 0);

    /* save the rule */
    insert into dbo.[MKTMEMBERSHIPMAILINGTEMPLATERULE] (
      [ID],
      [MEMBERSHIPMAILINGTEMPLATEID],
      [SEQUENCE],
      [SEGMENTID],
      [EXCLUDE],
      [PACKAGEID],
      [ASKLADDERID],
      [RESPONSERATE],
      [GIFTAMOUNT],
      [PARTDEFINITIONVALUESID],
      [CODE],
      [TESTPARTDEFINITIONVALUESID],
      [TESTSEGMENTCODE],
      [OVERRIDEADDRESSPROCESSING],
      [USEADDRESSPROCESSING],
      [ADDRESSPROCESSINGOPTIONID],
      [ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
      [ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
      [NAMEFORMATPARAMETERID],
      [BASECURRENCYID],
      [ORGANIZATIONGIFTAMOUNT],
      [CURRENCYEXCHANGERATEID],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    ) values (
      @ID,
      @MEMBERSHIPMAILINGTEMPLATEID,
      @SEQUENCE,
      @SEGMENTID,
      @EXCLUDE,
      @PACKAGEID,
      @ASKLADDERID,
      @RESPONSERATE,
      @GIFTAMOUNT,
      @CODEVALUEID,
      @CODE,
      @TESTSEGMENTCODEVALUEID,
      @TESTSEGMENTCODE,
      @OVERRIDEADDRESSPROCESSING,
      case when @OVERRIDEADDRESSPROCESSING = 1 then @USEADDRESSPROCESSING else 0 end,
      case when @OVERRIDEADDRESSPROCESSING = 1 and @USEADDRESSPROCESSING = 1 then @ADDRESSPROCESSINGOPTIONID else null end,
      case when @OVERRIDEADDRESSPROCESSING = 1 and @USEADDRESSPROCESSING = 1 then @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE else 0 end,
      case when @OVERRIDEADDRESSPROCESSING = 1 and @USEADDRESSPROCESSING = 1 then @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE else null end,
      case when @OVERRIDEADDRESSPROCESSING = 1 and @USEADDRESSPROCESSING = 1 then @NAMEFORMATPARAMETERID else null end,
      @BASECURRENCYID,
      @ORGANIZATIONGIFTAMOUNT,
      @ORGANIZATIONCURRENCYEXCHANGERATEID,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );

    if @EXCLUDE = 0
      begin
        /* Update the package with the selected code */
        exec dbo.[USP_MKTPACKAGE_UPDATECODE] @PACKAGEID, @PACKAGECODE, @PACKAGECODEVALUEID, @CHANNELSOURCECODE, @CHANNELSOURCECODEVALUEID, @CHANGEAGENTID, @CURRENTAPPUSERID;

        /* update the segment with the selected code */
        if @CODEVALUEID is not null
          exec dbo.[USP_MKTSEGMENT_UPDATECODE] @SEGMENTID, @CODE, @CODEVALUEID, @CHANGEAGENTID, 0, @CURRENTAPPUSERID;
      end

  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;