USP_DATAFORMTEMPLATE_ADD_MKTMARKETINGPLANITEMEXPENSE

The save procedure used by the add dataform template "Marketing Plan Item Expense Add Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@MARKETINGPLANITEMID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@DESCRIPTION	nvarchar(max)	IN	Description
@EXPENSECATEGORYCODEID	uniqueidentifier	IN	Category
@EXPENSETYPECODE	tinyint	IN	Type
@COUNT	int	IN	Count
@AMOUNT	money	IN	Cost
@BUDGETAMOUNT	money	IN	Budget amount

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_ADD_MKTMARKETINGPLANITEMEXPENSE]
(
  @ID uniqueidentifier = null output,
  @CURRENTAPPUSERID uniqueidentifier,
  @MARKETINGPLANITEMID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @DESCRIPTION nvarchar(max) = null, 
  @EXPENSECATEGORYCODEID uniqueidentifier = null,
  @EXPENSETYPECODE tinyint = 0,
  @COUNT integer = 1 ,
  @AMOUNT money = 0, 
  @BUDGETAMOUNT money = 0
)
as
  set nocount on;

  declare @CURRENTDATE datetime;
  declare @SEQUENCE integer;
  declare @BASECURRENCYID uniqueidentifier;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;
  declare @CURRENCYEXCHANGERATEID uniqueidentifier;
  declare @ORGANIZATIONAMOUNT money;
  declare @ORGANIZATIONBUDGETAMOUNT money;

  if @ID is null set @ID = newid();

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

  set @CURRENTDATE = getdate();

  select @SEQUENCE = isnull(max([SEQUENCE]), 0) + 1 from [MKTMARKETINGPLANITEMEXPENSE] where [MARKETINGPLANITEMID] = @MARKETINGPLANITEMID;

  select @BASECURRENCYID = [BASECURRENCYID] from dbo.[MKTMARKETINGPLANITEM] where [ID] = @MARKETINGPLANITEMID;
  set @ORGANIZATIONCURRENCYID = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

  begin try
    if @BASECURRENCYID = @ORGANIZATIONCURRENCYID
      begin
        set @CURRENCYEXCHANGERATEID = null;
        set @ORGANIZATIONAMOUNT = @AMOUNT;
        set @ORGANIZATIONBUDGETAMOUNT = @BUDGETAMOUNT;
      end
    else
      begin
        set @CURRENCYEXCHANGERATEID = dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null);
        set @ORGANIZATIONAMOUNT = dbo.[UFN_CURRENCY_CONVERT](@AMOUNT, @CURRENCYEXCHANGERATEID);
        set @ORGANIZATIONBUDGETAMOUNT = dbo.[UFN_CURRENCY_CONVERT](@BUDGETAMOUNT, @CURRENCYEXCHANGERATEID);
      end

    insert into dbo.[MKTMARKETINGPLANITEMEXPENSE] (
      [ID],
      [DESCRIPTION],
      [MARKETINGPLANITEMID],
      [EXPENSECATEGORYCODEID],
      [EXPENSETYPECODE],
      [COUNT],
      [BASECURRENCYID],
      [CURRENCYEXCHANGERATEID],
      [AMOUNT],
      [ORGANIZATIONAMOUNT],
      [BUDGETAMOUNT],
      [ORGANIZATIONBUDGETAMOUNT],
      [SEQUENCE],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    ) values (
      @ID,
      @DESCRIPTION,
      @MARKETINGPLANITEMID,
      @EXPENSECATEGORYCODEID,
      @EXPENSETYPECODE,
      @COUNT,
      @BASECURRENCYID,
      @CURRENCYEXCHANGERATEID,
      @AMOUNT,
      @ORGANIZATIONAMOUNT,
      @BUDGETAMOUNT,
      @ORGANIZATIONBUDGETAMOUNT,
      @SEQUENCE,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );
  end try

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

  return 0;