USP_MKTSEGMENTATIONACTIVE_SAVE

Saves the activated marketing effort calculations.

Parameters

Parameter Parameter Type Mode Description
@SEGMENTATIONID uniqueidentifier IN
@QUANTITY int IN
@RESPONDERS int IN
@RESPONSES int IN
@VARIABLECOST money IN
@TOTALCOST money IN
@COSTPERDOLLARRAISED money IN
@TOTALGIFTAMOUNT money IN
@AVERAGEGIFTAMOUNT money IN
@RESPONSERATE decimal(19, 4) IN
@ROIAMOUNT money IN
@ROIPERCENT decimal(19, 4) IN
@EXPECTEDCOSTPERDOLLARRAISED money IN
@EXPECTEDRESPONDERS int IN
@EXPECTEDTOTALGIFTAMOUNT money IN
@EXPECTEDAVERAGEGIFTAMOUNT money IN
@EXPECTEDRESPONSERATE decimal(19, 4) IN
@EXPECTEDROIAMOUNT money IN
@EXPECTEDROIPERCENT decimal(19, 4) IN
@INDIRECTRESPONDERS int IN
@INDIRECTRESPONSES int IN
@INDIRECTTOTALGIFTAMOUNT money IN
@INDIRECTAVERAGEGIFTAMOUNT money IN
@UNRESOLVEDRESPONDERS int IN
@UNRESOLVEDRESPONSES int IN
@UNRESOLVEDTOTALGIFTAMOUNT money IN
@UNRESOLVEDAVERAGEGIFTAMOUNT money IN
@CHANGEAGENTID uniqueidentifier IN
@ORGANIZATIONVARIABLECOST money IN
@ORGANIZATIONTOTALCOST money IN
@ORGANIZATIONCOSTPERDOLLARRAISED money IN
@ORGANIZATIONTOTALGIFTAMOUNT money IN
@ORGANIZATIONAVERAGEGIFTAMOUNT money IN
@ORGANIZATIONROIAMOUNT money IN
@ORGANIZATIONEXPECTEDCOSTPERDOLLARRAISED money IN
@ORGANIZATIONEXPECTEDTOTALGIFTAMOUNT money IN
@ORGANIZATIONEXPECTEDAVERAGEGIFTAMOUNT money IN
@ORGANIZATIONEXPECTEDROIAMOUNT money IN
@ORGANIZATIONINDIRECTTOTALGIFTAMOUNT money IN
@ORGANIZATIONINDIRECTAVERAGEGIFTAMOUNT money IN
@ORGANIZATIONUNRESOLVEDTOTALGIFTAMOUNT money IN
@ORGANIZATIONUNRESOLVEDAVERAGEGIFTAMOUNT money IN
@COSTTOACQUIRE money IN
@COSTPERTHOUSAND decimal(22, 4) IN
@NETPERTHOUSAND decimal(22, 4) IN
@GROSSPERTHOUSAND decimal(22, 4) IN
@ORGANIZATIONCOSTTOACQUIRE money IN
@ORGANIZATIONCOSTPERTHOUSAND decimal(22, 4) IN
@ORGANIZATIONNETPERTHOUSAND decimal(22, 4) IN
@ORGANIZATIONGROSSPERTHOUSAND decimal(22, 4) IN
@FIRSTRESPONSEDATE date IN

Definition

Copy 


CREATE procedure dbo.[USP_MKTSEGMENTATIONACTIVE_SAVE]
(
  @SEGMENTATIONID uniqueidentifier,
  @QUANTITY int,
  @RESPONDERS int,
  @RESPONSES int,
  @VARIABLECOST money,
  @TOTALCOST money,
  @COSTPERDOLLARRAISED money,
  @TOTALGIFTAMOUNT money,
  @AVERAGEGIFTAMOUNT money,
  @RESPONSERATE decimal(19,4),
  @ROIAMOUNT money,
  @ROIPERCENT decimal(19,4),
  @EXPECTEDCOSTPERDOLLARRAISED money,
  @EXPECTEDRESPONDERS int,
  @EXPECTEDTOTALGIFTAMOUNT money,
  @EXPECTEDAVERAGEGIFTAMOUNT money,
  @EXPECTEDRESPONSERATE decimal(19,4),
  @EXPECTEDROIAMOUNT money,
  @EXPECTEDROIPERCENT decimal(19,4),
  @INDIRECTRESPONDERS int,
  @INDIRECTRESPONSES int,
  @INDIRECTTOTALGIFTAMOUNT money,
  @INDIRECTAVERAGEGIFTAMOUNT money,
  @UNRESOLVEDRESPONDERS int,
  @UNRESOLVEDRESPONSES int,
  @UNRESOLVEDTOTALGIFTAMOUNT money,
  @UNRESOLVEDAVERAGEGIFTAMOUNT money,
  @CHANGEAGENTID uniqueidentifier = null,
  @ORGANIZATIONVARIABLECOST money = null,
  @ORGANIZATIONTOTALCOST money = null,
  @ORGANIZATIONCOSTPERDOLLARRAISED money = null,
  @ORGANIZATIONTOTALGIFTAMOUNT money = null,
  @ORGANIZATIONAVERAGEGIFTAMOUNT money = null,
  @ORGANIZATIONROIAMOUNT money = null,
  @ORGANIZATIONEXPECTEDCOSTPERDOLLARRAISED money = null,
  @ORGANIZATIONEXPECTEDTOTALGIFTAMOUNT money = null,
  @ORGANIZATIONEXPECTEDAVERAGEGIFTAMOUNT money = null,
  @ORGANIZATIONEXPECTEDROIAMOUNT money = null,
  @ORGANIZATIONINDIRECTTOTALGIFTAMOUNT money = null,
  @ORGANIZATIONINDIRECTAVERAGEGIFTAMOUNT money = null,
  @ORGANIZATIONUNRESOLVEDTOTALGIFTAMOUNT money = null,
  @ORGANIZATIONUNRESOLVEDAVERAGEGIFTAMOUNT money = null,
  @COSTTOACQUIRE money = null,
  @COSTPERTHOUSAND decimal(22,4) = null,
  @NETPERTHOUSAND decimal(22,4) = null,
  @GROSSPERTHOUSAND decimal(22,4) = null,
  @ORGANIZATIONCOSTTOACQUIRE money = null,
  @ORGANIZATIONCOSTPERTHOUSAND decimal(22,4) = null,
  @ORGANIZATIONNETPERTHOUSAND decimal(22,4) = null,
  @ORGANIZATIONGROSSPERTHOUSAND decimal(22,4) = null,
  @FIRSTRESPONSEDATE date = 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.[MKTSEGMENTATIONACTIVE] where [ID] = @SEGMENTATIONID)
      /* Update the existing row of mailing cache */
      update dbo.[MKTSEGMENTATIONACTIVE] set
        [QUANTITY] = @QUANTITY,
        [RESPONDERS] = @RESPONDERS,
        [RESPONSES] = @RESPONSES,
        [VARIABLECOST] = @VARIABLECOST,
        [TOTALCOST] = @TOTALCOST,
        [COSTPERDOLLARRAISED] = @COSTPERDOLLARRAISED,
        [TOTALGIFTAMOUNT] = @TOTALGIFTAMOUNT,
        [AVERAGEGIFTAMOUNT] = @AVERAGEGIFTAMOUNT,
        [RESPONSERATE] = @RESPONSERATE,
        [ROIAMOUNT] = @ROIAMOUNT,
        [ROIPERCENT] = @ROIPERCENT,
        [EXPECTEDCOSTPERDOLLARRAISED] = @EXPECTEDCOSTPERDOLLARRAISED,
        [EXPECTEDRESPONDERS] = @EXPECTEDRESPONDERS,
        [EXPECTEDTOTALGIFTAMOUNT] = @EXPECTEDTOTALGIFTAMOUNT,
        [EXPECTEDAVERAGEGIFTAMOUNT] = @EXPECTEDAVERAGEGIFTAMOUNT,
        [EXPECTEDRESPONSERATE] = @EXPECTEDRESPONSERATE,
        [EXPECTEDROIAMOUNT] = @EXPECTEDROIAMOUNT,
        [EXPECTEDROIPERCENT] = @EXPECTEDROIPERCENT,
        [INDIRECTRESPONDERS] = @INDIRECTRESPONDERS,
        [INDIRECTRESPONSES] = @INDIRECTRESPONSES,
        [INDIRECTTOTALGIFTAMOUNT] = @INDIRECTTOTALGIFTAMOUNT,
        [INDIRECTAVERAGEGIFTAMOUNT] = @INDIRECTAVERAGEGIFTAMOUNT,
        [UNRESOLVEDRESPONDERS] = @UNRESOLVEDRESPONDERS,
        [UNRESOLVEDRESPONSES] = @UNRESOLVEDRESPONSES,
        [UNRESOLVEDTOTALGIFTAMOUNT] = @UNRESOLVEDTOTALGIFTAMOUNT,
        [UNRESOLVEDAVERAGEGIFTAMOUNT] = @UNRESOLVEDAVERAGEGIFTAMOUNT,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE,
        [ORGANIZATIONVARIABLECOST] = isnull(@ORGANIZATIONVARIABLECOST, @VARIABLECOST),
        [ORGANIZATIONTOTALCOST] = isnull(@ORGANIZATIONTOTALCOST, @TOTALCOST),
        [ORGANIZATIONCOSTPERDOLLARRAISED] = isnull(@ORGANIZATIONCOSTPERDOLLARRAISED, @COSTPERDOLLARRAISED),
        [ORGANIZATIONTOTALGIFTAMOUNT] = isnull(@ORGANIZATIONTOTALGIFTAMOUNT, @TOTALGIFTAMOUNT),
        [ORGANIZATIONAVERAGEGIFTAMOUNT] = isnull(@ORGANIZATIONAVERAGEGIFTAMOUNT, @AVERAGEGIFTAMOUNT),
        [ORGANIZATIONROIAMOUNT] = isnull(@ORGANIZATIONROIAMOUNT, @ROIAMOUNT),
        [ORGANIZATIONEXPECTEDCOSTPERDOLLARRAISED] = isnull(@ORGANIZATIONEXPECTEDCOSTPERDOLLARRAISED, @EXPECTEDCOSTPERDOLLARRAISED),
        [ORGANIZATIONEXPECTEDTOTALGIFTAMOUNT] = isnull(@ORGANIZATIONEXPECTEDTOTALGIFTAMOUNT, @EXPECTEDTOTALGIFTAMOUNT),
        [ORGANIZATIONEXPECTEDAVERAGEGIFTAMOUNT] = isnull(@ORGANIZATIONEXPECTEDAVERAGEGIFTAMOUNT, @EXPECTEDAVERAGEGIFTAMOUNT),
        [ORGANIZATIONEXPECTEDROIAMOUNT] = isnull(@ORGANIZATIONEXPECTEDROIAMOUNT, @EXPECTEDROIAMOUNT),
        [ORGANIZATIONINDIRECTTOTALGIFTAMOUNT] = isnull(@ORGANIZATIONINDIRECTTOTALGIFTAMOUNT, @INDIRECTTOTALGIFTAMOUNT),
        [ORGANIZATIONINDIRECTAVERAGEGIFTAMOUNT] = isnull(@ORGANIZATIONINDIRECTAVERAGEGIFTAMOUNT, @INDIRECTAVERAGEGIFTAMOUNT),
        [ORGANIZATIONUNRESOLVEDTOTALGIFTAMOUNT] = isnull(@ORGANIZATIONUNRESOLVEDTOTALGIFTAMOUNT, @UNRESOLVEDTOTALGIFTAMOUNT),
        [ORGANIZATIONUNRESOLVEDAVERAGEGIFTAMOUNT] = isnull(@ORGANIZATIONUNRESOLVEDAVERAGEGIFTAMOUNT, @UNRESOLVEDAVERAGEGIFTAMOUNT),
        [COSTTOACQUIRE] = isnull(@COSTTOACQUIRE, 0),
        [COSTPERTHOUSAND] = isnull(@COSTPERTHOUSAND, 0),
        [NETPERTHOUSAND] = isnull(@NETPERTHOUSAND, 0),
        [GROSSPERTHOUSAND] = isnull(@GROSSPERTHOUSAND, 0),
        [ORGANIZATIONCOSTTOACQUIRE] = coalesce(@ORGANIZATIONCOSTTOACQUIRE, @COSTTOACQUIRE, 0),
        [ORGANIZATIONCOSTPERTHOUSAND] = coalesce(@ORGANIZATIONCOSTPERTHOUSAND, @COSTPERTHOUSAND, 0),
        [ORGANIZATIONNETPERTHOUSAND] = coalesce(@ORGANIZATIONNETPERTHOUSAND, @NETPERTHOUSAND, 0),
        [ORGANIZATIONGROSSPERTHOUSAND] = coalesce(@ORGANIZATIONGROSSPERTHOUSAND, @GROSSPERTHOUSAND, 0),
        [FIRSTRESPONSEDATE] = @FIRSTRESPONSEDATE
      where [ID] = @SEGMENTATIONID;
    else
    begin
      declare @BASECURRENCYID uniqueidentifier;
      select @BASECURRENCYID = [BASECURRENCYID] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID;

      /* Insert a new row for the mailing cache */
      insert into dbo.[MKTSEGMENTATIONACTIVE] (
        [ID],
        [QUANTITY],
        [RESPONDERS],
        [RESPONSES],
        [VARIABLECOST],
        [TOTALCOST],
        [COSTPERDOLLARRAISED],
        [TOTALGIFTAMOUNT],
        [AVERAGEGIFTAMOUNT],
        [RESPONSERATE],
        [ROIAMOUNT],
        [ROIPERCENT],
        [EXPECTEDCOSTPERDOLLARRAISED],
        [EXPECTEDRESPONDERS],
        [EXPECTEDTOTALGIFTAMOUNT],
        [EXPECTEDAVERAGEGIFTAMOUNT],
        [EXPECTEDRESPONSERATE],
        [EXPECTEDROIAMOUNT],
        [EXPECTEDROIPERCENT],
        [INDIRECTRESPONDERS],
        [INDIRECTRESPONSES],
        [INDIRECTTOTALGIFTAMOUNT],
        [INDIRECTAVERAGEGIFTAMOUNT],
        [UNRESOLVEDRESPONDERS],
        [UNRESOLVEDRESPONSES],
        [UNRESOLVEDTOTALGIFTAMOUNT],
        [UNRESOLVEDAVERAGEGIFTAMOUNT],
        [ADDEDBYID],
        [CHANGEDBYID],
        [DATEADDED],
        [DATECHANGED],
        [ORGANIZATIONVARIABLECOST],
        [ORGANIZATIONTOTALCOST],
        [ORGANIZATIONCOSTPERDOLLARRAISED],
        [ORGANIZATIONTOTALGIFTAMOUNT],
        [ORGANIZATIONAVERAGEGIFTAMOUNT],
        [ORGANIZATIONROIAMOUNT],
        [ORGANIZATIONEXPECTEDCOSTPERDOLLARRAISED],
        [ORGANIZATIONEXPECTEDTOTALGIFTAMOUNT],
        [ORGANIZATIONEXPECTEDAVERAGEGIFTAMOUNT],
        [ORGANIZATIONEXPECTEDROIAMOUNT],
        [ORGANIZATIONINDIRECTTOTALGIFTAMOUNT],
        [ORGANIZATIONINDIRECTAVERAGEGIFTAMOUNT],
        [ORGANIZATIONUNRESOLVEDTOTALGIFTAMOUNT],
        [ORGANIZATIONUNRESOLVEDAVERAGEGIFTAMOUNT],
        [BASECURRENCYID],
        [COSTTOACQUIRE],
        [COSTPERTHOUSAND],
        [NETPERTHOUSAND],
        [GROSSPERTHOUSAND],
        [ORGANIZATIONCOSTTOACQUIRE],
        [ORGANIZATIONCOSTPERTHOUSAND],
        [ORGANIZATIONNETPERTHOUSAND],
        [ORGANIZATIONGROSSPERTHOUSAND],
        [FIRSTRESPONSEDATE]
      ) values (
        @SEGMENTATIONID,
        @QUANTITY,
        @RESPONDERS,
        @RESPONSES,
        @VARIABLECOST,
        @TOTALCOST,
        @COSTPERDOLLARRAISED,
        @TOTALGIFTAMOUNT,
        @AVERAGEGIFTAMOUNT,
        @RESPONSERATE,
        @ROIAMOUNT,
        @ROIPERCENT,
        @EXPECTEDCOSTPERDOLLARRAISED,
        @EXPECTEDRESPONDERS,
        @EXPECTEDTOTALGIFTAMOUNT,
        @EXPECTEDAVERAGEGIFTAMOUNT,
        @EXPECTEDRESPONSERATE,
        @EXPECTEDROIAMOUNT,
        @EXPECTEDROIPERCENT,
        @INDIRECTRESPONDERS,
        @INDIRECTRESPONSES,
        @INDIRECTTOTALGIFTAMOUNT,
        @INDIRECTAVERAGEGIFTAMOUNT,
        @UNRESOLVEDRESPONDERS,
        @UNRESOLVEDRESPONSES,
        @UNRESOLVEDTOTALGIFTAMOUNT,
        @UNRESOLVEDAVERAGEGIFTAMOUNT,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE,
        isnull(@ORGANIZATIONVARIABLECOST, @VARIABLECOST),
        isnull(@ORGANIZATIONTOTALCOST, @TOTALCOST),
        isnull(@ORGANIZATIONCOSTPERDOLLARRAISED, @COSTPERDOLLARRAISED),
        isnull(@ORGANIZATIONTOTALGIFTAMOUNT, @TOTALGIFTAMOUNT),
        isnull(@ORGANIZATIONAVERAGEGIFTAMOUNT, @AVERAGEGIFTAMOUNT),
        isnull(@ORGANIZATIONROIAMOUNT, @ROIAMOUNT),
        isnull(@ORGANIZATIONEXPECTEDCOSTPERDOLLARRAISED, @EXPECTEDCOSTPERDOLLARRAISED),
        isnull(@ORGANIZATIONEXPECTEDTOTALGIFTAMOUNT, @EXPECTEDTOTALGIFTAMOUNT),
        isnull(@ORGANIZATIONEXPECTEDAVERAGEGIFTAMOUNT, @EXPECTEDAVERAGEGIFTAMOUNT),
        isnull(@ORGANIZATIONEXPECTEDROIAMOUNT, @EXPECTEDROIAMOUNT),
        isnull(@ORGANIZATIONINDIRECTTOTALGIFTAMOUNT, @INDIRECTTOTALGIFTAMOUNT),
        isnull(@ORGANIZATIONINDIRECTAVERAGEGIFTAMOUNT, @INDIRECTAVERAGEGIFTAMOUNT),
        isnull(@ORGANIZATIONUNRESOLVEDTOTALGIFTAMOUNT, @UNRESOLVEDTOTALGIFTAMOUNT),
        isnull(@ORGANIZATIONUNRESOLVEDAVERAGEGIFTAMOUNT, @UNRESOLVEDAVERAGEGIFTAMOUNT),
        @BASECURRENCYID,
        isnull(@COSTTOACQUIRE, 0),
        isnull(@COSTPERTHOUSAND, 0),
        isnull(@NETPERTHOUSAND, 0),
        isnull(@GROSSPERTHOUSAND, 0),
        coalesce(@ORGANIZATIONCOSTTOACQUIRE, @COSTTOACQUIRE, 0),
        coalesce(@ORGANIZATIONCOSTPERTHOUSAND, @COSTPERTHOUSAND, 0),
        coalesce(@ORGANIZATIONNETPERTHOUSAND, @NETPERTHOUSAND, 0),
        coalesce(@ORGANIZATIONGROSSPERTHOUSAND, @GROSSPERTHOUSAND, 0),
        @FIRSTRESPONSEDATE
      );
    end
  end try

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

  return 0;