USP_DATAFORMTEMPLATE_EDIT_MKTMARKETINGPLANITEMASSUMPTIONS

The save procedure used by the edit dataform template "Marketing Plan Item 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.
@BUDGETAMOUNT	money	IN	Budget amount
@TOTALREVENUEGOAL	money	IN	Revenue
@QUANTITY	int	IN	Quantity
@RESPONSERATEGOAL	decimal(20, 2)	IN	Response rate
@TOTALTRANSACTIONSGOAL	int	IN	Responses
@ASSUMPTIONSLIST	xml	IN	Child assumptions

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDIT_MKTMARKETINGPLANITEMASSUMPTIONS]
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @BUDGETAMOUNT money,
  @TOTALREVENUEGOAL money,
  @QUANTITY int,
  @RESPONSERATEGOAL decimal(20,2),
  @TOTALTRANSACTIONSGOAL int,
  @ASSUMPTIONSLIST xml
)
as
  set nocount on;

  declare @CURRENTDATE datetime;
  declare @BASECURRENCYID uniqueidentifier;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;
  declare @CURRENCYEXCHANGERATEID uniqueidentifier;
  declare @ORGANIZATIONBUDGETAMOUNT money;
  declare @ORGANIZATIONTOTALREVENUEGOAL money;
  declare @DATEADDED datetime;

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

  set @CURRENTDATE = getdate();

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

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

  begin try
    -- save the template items

    update dbo.[MKTMARKETINGPLANITEM] set
      [CURRENCYEXCHANGERATEID] = @CURRENCYEXCHANGERATEID,
      [BUDGETAMOUNT] = @BUDGETAMOUNT,
      [ORGANIZATIONBUDGETAMOUNT] = @ORGANIZATIONBUDGETAMOUNT,
      [TOTALREVENUEGOAL] = @TOTALREVENUEGOAL,
      [ORGANIZATIONTOTALREVENUEGOAL] = @ORGANIZATIONTOTALREVENUEGOAL,
      [QUANTITY] = @QUANTITY,
      [RESPONSERATEGOAL] = @RESPONSERATEGOAL,
      [TOTALTRANSACTIONSGOAL] = @TOTALTRANSACTIONSGOAL,
      [DATEASSUMPTIONSADDED] = isnull([DATEASSUMPTIONSADDED], @CURRENTDATE),
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @ID;

    if @ASSUMPTIONSLIST is null
      delete from dbo.[MKTMARKETINGPLANITEM] where [PARENTMARKETINGPLANITEMID] = @ID;
    else
      begin
        exec dbo.[USP_MKTMARKETINGPLANITEM_GETCHILDASSUMPTIONSLIST_UPDATEFROMXML] @ID, @ASSUMPTIONSLIST, @CHANGEAGENTID, @CURRENTDATE;

        if @BASECURRENCYID = @ORGANIZATIONCURRENCYID
          update dbo.[MKTMARKETINGPLANITEM] set
            [CURRENCYEXCHANGERATEID] = null,
            [ORGANIZATIONBUDGETAMOUNT] = [BUDGETAMOUNT],
            [ORGANIZATIONTOTALREVENUEGOAL] = [TOTALREVENUEGOAL],
            [DATEASSUMPTIONSADDED] = isnull([DATEASSUMPTIONSADDED], @CURRENTDATE),
            [DATECHANGED] = @CURRENTDATE
          where [PARENTMARKETINGPLANITEMID] = @ID;
        else
          update dbo.[MKTMARKETINGPLANITEM] set
            [CURRENCYEXCHANGERATEID] = dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, isnull([DATEASSUMPTIONSADDED], @CURRENTDATE), 0, null),
            [ORGANIZATIONBUDGETAMOUNT] = dbo.[UFN_CURRENCY_CONVERT]([BUDGETAMOUNT], dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, isnull([DATEASSUMPTIONSADDED], @CURRENTDATE), 0, null)),
            [ORGANIZATIONTOTALREVENUEGOAL] = dbo.[UFN_CURRENCY_CONVERT]([TOTALREVENUEGOAL], dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, isnull([DATEASSUMPTIONSADDED], @CURRENTDATE), 0, null)),
            [DATEASSUMPTIONSADDED] = isnull([DATEASSUMPTIONSADDED], @CURRENTDATE),
            [DATECHANGED] = @CURRENTDATE
          where [PARENTMARKETINGPLANITEMID] = @ID;
     end
  end try

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

  return 0;