USP_MKTMEMBERSHIPMAILINGACTIVE_SAVE

Saves the activated membership renewal effort calculations.

Parameters

Parameter	Parameter Type	Mode
@SEGMENTATIONID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@RENEWALS	int	IN
@UPGRADES	int	IN
@DOWNGRADES	int	IN
@TOTALRENEWALAMOUNT	money	IN
@AVERAGERENEWALAMOUNT	money	IN
@COSTPERRENEWAL	money	IN
@RENEWALRATE	decimal(19, 4)	IN
@UPGRADERATE	decimal(19, 4)	IN
@DOWNGRADERATE	decimal(19, 4)	IN
@ORGANIZATIONTOTALRENEWALAMOUNT	money	IN
@ORGANIZATIONAVERAGERENEWALAMOUNT	money	IN
@ORGANIZATIONCOSTPERRENEWAL	money	IN

Definition

Copy



CREATE procedure dbo.[USP_MKTMEMBERSHIPMAILINGACTIVE_SAVE]
(
  @SEGMENTATIONID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @RENEWALS int,
  @UPGRADES int,
  @DOWNGRADES int,
  @TOTALRENEWALAMOUNT money,
  @AVERAGERENEWALAMOUNT money,
  @COSTPERRENEWAL money,
  @RENEWALRATE decimal(19, 4),
  @UPGRADERATE decimal(19, 4),
  @DOWNGRADERATE decimal(19, 4),
  @ORGANIZATIONTOTALRENEWALAMOUNT money = null,
  @ORGANIZATIONAVERAGERENEWALAMOUNT money = null,
  @ORGANIZATIONCOSTPERRENEWAL money = null
)
as
  set nocount on;

  declare @CURRENTDATE datetime;

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

    set @CURRENTDATE = getdate();

    if exists(select 1 from dbo.[MKTMEMBERSHIPMAILINGACTIVE] where [ID] = @SEGMENTATIONID)
      -- update the existing row in the membership mailing cache

      update dbo.[MKTMEMBERSHIPMAILINGACTIVE] set
        [RENEWALS] = @RENEWALS,
        [UPGRADES] = @UPGRADES,
        [DOWNGRADES] = @DOWNGRADES,
        [TOTALRENEWALAMOUNT] = @TOTALRENEWALAMOUNT,
        [AVERAGERENEWALAMOUNT] = @AVERAGERENEWALAMOUNT,
        [COSTPERRENEWAL] = @COSTPERRENEWAL,
        [RENEWALRATE] = @RENEWALRATE,
        [UPGRADERATE] = @UPGRADERATE,
        [DOWNGRADERATE] = @DOWNGRADERATE,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE,
        [ORGANIZATIONTOTALRENEWALAMOUNT] = isnull(@ORGANIZATIONTOTALRENEWALAMOUNT, @TOTALRENEWALAMOUNT),
        [ORGANIZATIONAVERAGERENEWALAMOUNT] = isnull(@ORGANIZATIONAVERAGERENEWALAMOUNT, @AVERAGERENEWALAMOUNT),
        [ORGANIZATIONCOSTPERRENEWAL] = isnull(@ORGANIZATIONCOSTPERRENEWAL, @COSTPERRENEWAL)
      where [ID] = @SEGMENTATIONID;
    else
    begin

      declare @BASECURRENCYID uniqueidentifier;
      select @BASECURRENCYID = [BASECURRENCYID] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID;

      -- insert a new row in the membership mailing cache

      insert into dbo.[MKTMEMBERSHIPMAILINGACTIVE] (
        [ID],
        [RENEWALS],
        [UPGRADES],
        [DOWNGRADES],
        [TOTALRENEWALAMOUNT],
        [AVERAGERENEWALAMOUNT],
        [COSTPERRENEWAL],
        [RENEWALRATE],
        [UPGRADERATE],
        [DOWNGRADERATE],
        [ADDEDBYID],
        [CHANGEDBYID],
        [DATEADDED],
        [DATECHANGED],
        [ORGANIZATIONTOTALRENEWALAMOUNT],
        [ORGANIZATIONAVERAGERENEWALAMOUNT],
        [ORGANIZATIONCOSTPERRENEWAL],
        [BASECURRENCYID]
      ) values (
        @SEGMENTATIONID,
        @RENEWALS,
        @UPGRADES,
        @DOWNGRADES,
        @TOTALRENEWALAMOUNT,
        @AVERAGERENEWALAMOUNT,
        @COSTPERRENEWAL,
        @RENEWALRATE,
        @UPGRADERATE,
        @DOWNGRADERATE,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE,
        isnull(@ORGANIZATIONTOTALRENEWALAMOUNT, @TOTALRENEWALAMOUNT),
        isnull(@ORGANIZATIONAVERAGERENEWALAMOUNT, @AVERAGERENEWALAMOUNT),
        isnull(@ORGANIZATIONCOSTPERRENEWAL, @COSTPERRENEWAL),
        @BASECURRENCYID
      );
    end
  end try

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

  return 0;