USP_DATAFORMTEMPLATE_EDIT_MKTMARKETINGPLANBRIEFASSUMPTIONS

The save procedure used by the edit dataform template "Marketing Plan Segment Summary Assumptions Edit Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@PACKAGEID	uniqueidentifier	IN	Package
@PACKAGECODE	nvarchar(10)	IN	Package code
@UNITCOST	money	IN	Cost
@BUDGETAMOUNT	money	IN	Budget amount
@TOTALREVENUEGOAL	money	IN	Revenue
@QUANTITY	int	IN	Quantity
@RESPONSERATEGOAL	decimal(20, 2)	IN	Response rate
@TOTALTRANSACTIONSGOAL	int	IN	Responses
@AVERAGEREVENUEGOAL	money	IN	Average amount

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDIT_MKTMARKETINGPLANBRIEFASSUMPTIONS]
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @PACKAGEID uniqueidentifier,
  @PACKAGECODE nvarchar(10),
  @UNITCOST money,
  @BUDGETAMOUNT money,
  @TOTALREVENUEGOAL money,
  @QUANTITY int ,
  @RESPONSERATEGOAL decimal(20, 2),
  @TOTALTRANSACTIONSGOAL int,
  @AVERAGEREVENUEGOAL money
)
as
  set nocount on;

  declare @CURRENTDATE datetime;
  declare @BASECURRENCYID uniqueidentifier;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;
  declare @CURRENCYEXCHANGERATEID uniqueidentifier;
  declare @ORGANIZATIONUNITCOST money;
  declare @ORGANIZATIONBUDGETAMOUNT money;
  declare @ORGANIZATIONTOTALREVENUEGOAL money;
  declare @ORGANIZATIONAVERAGEREVENUEGOAL money;
  declare @DATEADDED datetime;
  declare @OLDCODE nvarchar(10);

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

  set @CURRENTDATE = getdate();

  select @BASECURRENCYID = [BASECURRENCYID], @DATEADDED = [DATEASSUMPTIONSADDED] from dbo.[MKTMARKETINGPLANBRIEF] where [ID] = @ID;
  set @ORGANIZATIONCURRENCYID = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();
  if @DATEADDED is null set @DATEADDED = @CURRENTDATE;

  if @BASECURRENCYID = @ORGANIZATIONCURRENCYID
    begin
      set @CURRENCYEXCHANGERATEID = null;
      set @ORGANIZATIONUNITCOST = @UNITCOST;
      set @ORGANIZATIONBUDGETAMOUNT = @BUDGETAMOUNT;
      set @ORGANIZATIONTOTALREVENUEGOAL = @TOTALREVENUEGOAL;
      set @ORGANIZATIONAVERAGEREVENUEGOAL = @AVERAGEREVENUEGOAL;
    end
  else
    begin
      set @CURRENCYEXCHANGERATEID = dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @DATEADDED, 0, null);
      set @ORGANIZATIONUNITCOST = dbo.[UFN_CURRENCY_CONVERT](@UNITCOST, @CURRENCYEXCHANGERATEID);
      set @ORGANIZATIONBUDGETAMOUNT = dbo.[UFN_CURRENCY_CONVERT](@BUDGETAMOUNT, @CURRENCYEXCHANGERATEID);
      set @ORGANIZATIONTOTALREVENUEGOAL = dbo.[UFN_CURRENCY_CONVERT](@TOTALREVENUEGOAL, @CURRENCYEXCHANGERATEID);
      set @ORGANIZATIONAVERAGEREVENUEGOAL = dbo.[UFN_CURRENCY_CONVERT](@AVERAGEREVENUEGOAL, @CURRENCYEXCHANGERATEID);
    end

  begin try
    update dbo.[MKTMARKETINGPLANBRIEF] set
      [PACKAGEID] = @PACKAGEID,
      [QUANTITY] = @QUANTITY,
      [CURRENCYEXCHANGERATEID] = @CURRENCYEXCHANGERATEID,
      [UNITCOST] = @UNITCOST,
      [ORGANIZATIONUNITCOST] = @ORGANIZATIONUNITCOST,
      [BUDGETAMOUNT] = @BUDGETAMOUNT,
      [ORGANIZATIONBUDGETAMOUNT] = @ORGANIZATIONBUDGETAMOUNT,
      [RESPONSERATEGOAL] = @RESPONSERATEGOAL,
      [TOTALTRANSACTIONSGOAL] = @TOTALTRANSACTIONSGOAL,
      [TOTALREVENUEGOAL] = @TOTALREVENUEGOAL,
      [ORGANIZATIONTOTALREVENUEGOAL] = @ORGANIZATIONTOTALREVENUEGOAL,
      [AVERAGEREVENUEGOAL] = @AVERAGEREVENUEGOAL,
      [ORGANIZATIONAVERAGEREVENUEGOAL] = @ORGANIZATIONAVERAGEREVENUEGOAL,
      [DATEASSUMPTIONSADDED] = isnull([DATEASSUMPTIONSADDED], @CURRENTDATE),
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @ID;

  end try

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

  return 0;