USP_MKTMEMBERSHIPMAILINGSEGMENTACTIVE_SAVE

CREATE procedure dbo.[USP_MKTMEMBERSHIPMAILINGSEGMENTACTIVE_SAVE]
(
  @SEGMENTID uniqueidentifier,
  @TESTSEGMENTID uniqueidentifier = null,
  @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,
  @BASECURRENCYID uniqueidentifier = null
)
as
  set nocount on;

  declare @ID uniqueidentifier;
  declare @CURRENTDATE datetime;

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

    set @CURRENTDATE = getdate();

    select @ID = [ID]
    from dbo.[MKTSEGMENTATIONSEGMENTACTIVE]
    where [SEGMENTID] = @SEGMENTID 
    and ((@TESTSEGMENTID is null and [TESTSEGMENTID] is null) or (@TESTSEGMENTID is not null and [TESTSEGMENTID] = @TESTSEGMENTID))

    if exists (select top 1 1 from dbo.[MKTMEMBERSHIPMAILINGSEGMENTACTIVE] where [ID] = @ID)
      -- update the existing row in the segment cache

      update dbo.[MKTMEMBERSHIPMAILINGSEGMENTACTIVE] 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] = @ID;
    else
    begin

      if @BASECURRENCYID is null
        set @BASECURRENCYID = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

      -- insert a new row in the segment cache

      insert into dbo.[MKTMEMBERSHIPMAILINGSEGMENTACTIVE] (
        [ID],
        [RENEWALS],
        [UPGRADES],
        [DOWNGRADES],
        [TOTALRENEWALAMOUNT],
        [AVERAGERENEWALAMOUNT],
        [COSTPERRENEWAL],
        [RENEWALRATE],
        [UPGRADERATE],
        [DOWNGRADERATE],
        [ADDEDBYID],
        [CHANGEDBYID],
        [DATEADDED],
        [DATECHANGED],
        [ORGANIZATIONTOTALRENEWALAMOUNT],
        [ORGANIZATIONAVERAGERENEWALAMOUNT],
        [ORGANIZATIONCOSTPERRENEWAL],
        [BASECURRENCYID]
      ) values (
        @ID,
        @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;