USP_MKTSEGMENTATIONSEGMENTACTIVE_SAVE

Saves the activated marketing effort segment or test segment calculations.

Parameters

Parameter	Parameter Type	Mode
@SEGMENTID	uniqueidentifier	IN
@TESTSEGMENTID	uniqueidentifier	IN
@IMPRESSIONS	int	IN
@IMPRESSIONCALCULATIONMETHODCODE	tinyint	IN
@QUANTITY	int	IN
@RESPONDERS	int	IN
@RESPONSES	int	IN
@VARIABLECOST	money	IN
@FIXEDCOST	money	IN
@TOTALCOST	money	IN
@COSTPERDOLLARRAISED	money	IN
@TOTALGIFTAMOUNT	money	IN
@AVERAGEGIFTAMOUNT	money	IN
@RESPONSERATE	decimal(19, 4)	IN
@LIFT	decimal(19, 4)	IN
@ROIAMOUNT	money	IN
@ROIPERCENT	decimal(19, 4)	IN
@EXPECTEDCOSTPERDOLLARRAISED	money	IN
@EXPECTEDRESPONDERS	int	IN
@EXPECTEDTOTALGIFTAMOUNT	money	IN
@EXPECTEDROIAMOUNT	money	IN
@EXPECTEDROIPERCENT	decimal(19, 4)	IN
@CHANGEAGENTID	uniqueidentifier	IN
@ORGANIZATIONVARIABLECOST	money	IN
@ORGANIZATIONFIXEDCOST	money	IN
@ORGANIZATIONTOTALCOST	money	IN
@ORGANIZATIONCOSTPERDOLLARRAISED	money	IN
@ORGANIZATIONTOTALGIFTAMOUNT	money	IN
@ORGANIZATIONAVERAGEGIFTAMOUNT	money	IN
@ORGANIZATIONROIAMOUNT	money	IN
@ORGANIZATIONEXPECTEDCOSTPERDOLLARRAISED	money	IN
@ORGANIZATIONEXPECTEDTOTALGIFTAMOUNT	money	IN
@ORGANIZATIONEXPECTEDROIAMOUNT	money	IN
@BASECURRENCYID	uniqueidentifier	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	money	IN

Definition

Copy



CREATE procedure dbo.[USP_MKTSEGMENTATIONSEGMENTACTIVE_SAVE]
(
  @SEGMENTID uniqueidentifier,
  @TESTSEGMENTID uniqueidentifier = null,
  @IMPRESSIONS integer,
  @IMPRESSIONCALCULATIONMETHODCODE tinyint,
  @QUANTITY integer,
  @RESPONDERS integer,
  @RESPONSES integer,
  @VARIABLECOST money,
  @FIXEDCOST money,
  @TOTALCOST money,
  @COSTPERDOLLARRAISED money,
  @TOTALGIFTAMOUNT money,
  @AVERAGEGIFTAMOUNT money,
  @RESPONSERATE decimal(19,4),
  @LIFT decimal(19,4),
  @ROIAMOUNT money,
  @ROIPERCENT decimal(19,4),
  @EXPECTEDCOSTPERDOLLARRAISED money,
  @EXPECTEDRESPONDERS integer,
  @EXPECTEDTOTALGIFTAMOUNT money,
  @EXPECTEDROIAMOUNT money,
  @EXPECTEDROIPERCENT decimal(19,4),
  @CHANGEAGENTID uniqueidentifier = null,
  @ORGANIZATIONVARIABLECOST money = null,
  @ORGANIZATIONFIXEDCOST money = null,
  @ORGANIZATIONTOTALCOST money = null,
  @ORGANIZATIONCOSTPERDOLLARRAISED money = null,
  @ORGANIZATIONTOTALGIFTAMOUNT money = null,
  @ORGANIZATIONAVERAGEGIFTAMOUNT money = null,
  @ORGANIZATIONROIAMOUNT money = null,
  @ORGANIZATIONEXPECTEDCOSTPERDOLLARRAISED money = null,
  @ORGANIZATIONEXPECTEDTOTALGIFTAMOUNT money = null,
  @ORGANIZATIONEXPECTEDROIAMOUNT money = null,  
  @BASECURRENCYID uniqueidentifier = 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 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.[MKTSEGMENTATIONSEGMENTACTIVE] where [SEGMENTID] = @SEGMENTID and ((@TESTSEGMENTID is null and [TESTSEGMENTID] is null) or (@TESTSEGMENTID is not null and [TESTSEGMENTID] = @TESTSEGMENTID)))
      /* Update the existing row of segment cache */
      update dbo.[MKTSEGMENTATIONSEGMENTACTIVE] set
        [IMPRESSIONS] = @IMPRESSIONS,
        [IMPRESSIONCALCULATIONMETHODCODE] = @IMPRESSIONCALCULATIONMETHODCODE,
        [QUANTITY] = @QUANTITY,
        [RESPONDERS] = @RESPONDERS,
        [RESPONSES] = @RESPONSES,
        [VARIABLECOST] = @VARIABLECOST,
        [FIXEDCOST] = @FIXEDCOST,
        [TOTALCOST] = @TOTALCOST,
        [COSTPERDOLLARRAISED] = @COSTPERDOLLARRAISED,
        [TOTALGIFTAMOUNT] = @TOTALGIFTAMOUNT,
        [AVERAGEGIFTAMOUNT] = @AVERAGEGIFTAMOUNT,
        [RESPONSERATE] = @RESPONSERATE,
        [LIFT] = @LIFT,
        [ROIAMOUNT] = @ROIAMOUNT,
        [ROIPERCENT] = @ROIPERCENT,
        [EXPECTEDCOSTPERDOLLARRAISED] = @EXPECTEDCOSTPERDOLLARRAISED,
        [EXPECTEDRESPONDERS] = @EXPECTEDRESPONDERS,
        [EXPECTEDTOTALGIFTAMOUNT] = @EXPECTEDTOTALGIFTAMOUNT,
        [EXPECTEDROIAMOUNT] = @EXPECTEDROIAMOUNT,
        [EXPECTEDROIPERCENT] = @EXPECTEDROIPERCENT,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE,
        [ORGANIZATIONVARIABLECOST] = isnull(@ORGANIZATIONVARIABLECOST, @VARIABLECOST),
        [ORGANIZATIONFIXEDCOST] = isnull(@ORGANIZATIONFIXEDCOST, @FIXEDCOST),
        [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),
        [ORGANIZATIONEXPECTEDROIAMOUNT] = isnull(@ORGANIZATIONEXPECTEDROIAMOUNT, @EXPECTEDROIAMOUNT),
        [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)
      where [SEGMENTID] = @SEGMENTID
      and ((@TESTSEGMENTID is null and [TESTSEGMENTID] is null) or (@TESTSEGMENTID is not null and [TESTSEGMENTID] = @TESTSEGMENTID));
    else
    begin

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

      /* Insert a new row for the segment cache */
      insert into dbo.[MKTSEGMENTATIONSEGMENTACTIVE] (
        [ID],
        [SEGMENTID],
        [TESTSEGMENTID],
        [IMPRESSIONS],
        [IMPRESSIONCALCULATIONMETHODCODE],
        [QUANTITY],
        [RESPONDERS],
        [RESPONSES],
        [VARIABLECOST],
        [FIXEDCOST],
        [TOTALCOST],
        [COSTPERDOLLARRAISED],
        [TOTALGIFTAMOUNT],
        [AVERAGEGIFTAMOUNT],
        [RESPONSERATE],
        [LIFT],
        [ROIAMOUNT],
        [ROIPERCENT],
        [EXPECTEDCOSTPERDOLLARRAISED],
        [EXPECTEDRESPONDERS],
        [EXPECTEDTOTALGIFTAMOUNT],
        [EXPECTEDROIAMOUNT],
        [EXPECTEDROIPERCENT],
        [ADDEDBYID],
        [CHANGEDBYID],
        [DATEADDED],
        [DATECHANGED],
        [ORGANIZATIONVARIABLECOST],
        [ORGANIZATIONFIXEDCOST],
        [ORGANIZATIONTOTALCOST],
        [ORGANIZATIONCOSTPERDOLLARRAISED],
        [ORGANIZATIONTOTALGIFTAMOUNT],
        [ORGANIZATIONAVERAGEGIFTAMOUNT],
        [ORGANIZATIONROIAMOUNT],
        [ORGANIZATIONEXPECTEDCOSTPERDOLLARRAISED],
        [ORGANIZATIONEXPECTEDTOTALGIFTAMOUNT],
        [ORGANIZATIONEXPECTEDROIAMOUNT],
        [BASECURRENCYID],
        [COSTTOACQUIRE],
        [COSTPERTHOUSAND],
        [NETPERTHOUSAND],
        [GROSSPERTHOUSAND],
        [ORGANIZATIONCOSTTOACQUIRE],
        [ORGANIZATIONCOSTPERTHOUSAND],
        [ORGANIZATIONNETPERTHOUSAND],
        [ORGANIZATIONGROSSPERTHOUSAND]
      ) values (
        NewID(),
        @SEGMENTID,
        @TESTSEGMENTID,
        @IMPRESSIONS,
        @IMPRESSIONCALCULATIONMETHODCODE,
        @QUANTITY,
        @RESPONDERS,
        @RESPONSES,
        @VARIABLECOST,
        @FIXEDCOST,
        @TOTALCOST,
        @COSTPERDOLLARRAISED,
        @TOTALGIFTAMOUNT,
        @AVERAGEGIFTAMOUNT,
        @RESPONSERATE,
        @LIFT,
        @ROIAMOUNT,
        @ROIPERCENT,
        @EXPECTEDCOSTPERDOLLARRAISED,
        @EXPECTEDRESPONDERS,
        @EXPECTEDTOTALGIFTAMOUNT,
        @EXPECTEDROIAMOUNT,
        @EXPECTEDROIPERCENT,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE,
        isnull(@ORGANIZATIONVARIABLECOST, @VARIABLECOST),
        isnull(@ORGANIZATIONFIXEDCOST, @FIXEDCOST),
        isnull(@ORGANIZATIONTOTALCOST, @TOTALCOST),
        isnull(@ORGANIZATIONCOSTPERDOLLARRAISED, @COSTPERDOLLARRAISED),
        isnull(@ORGANIZATIONTOTALGIFTAMOUNT, @TOTALGIFTAMOUNT),
        isnull(@ORGANIZATIONAVERAGEGIFTAMOUNT, @AVERAGEGIFTAMOUNT),
        isnull(@ORGANIZATIONROIAMOUNT, @ROIAMOUNT),
        isnull(@ORGANIZATIONEXPECTEDCOSTPERDOLLARRAISED, @EXPECTEDCOSTPERDOLLARRAISED),
        isnull(@ORGANIZATIONEXPECTEDTOTALGIFTAMOUNT, @EXPECTEDTOTALGIFTAMOUNT),
        isnull(@ORGANIZATIONEXPECTEDROIAMOUNT, @EXPECTEDROIAMOUNT),
        @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)
      );
    end
  end try

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

  return 0;