USP_DATAFORMTEMPLATE_EDIT_MKTMARKETINGPLANBASECURRENCY

The save procedure used by the edit dataform template "Marketing Plan Base Currency 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.
@BASECURRENCYID	uniqueidentifier	IN	Base currency
Definition

Copy

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDIT_MKTMARKETINGPLANBASECURRENCY] (
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @BASECURRENCYID uniqueidentifier
)
as
  begin
    set nocount on;

    declare @CURRENTDATE datetime;
    declare @OLDBASECURRENCYID uniqueidentifier;
    declare @ORGANIZATIONCURRENCYID uniqueidentifier;

    if exists (select top 1 1 from dbo.[MKTMARKETINGPLANBRIEF] where [MARKETINGPLANID] = @ID and [PACKAGEID] is not null)
      raiserror('BBERR_PLANBASECURRENCYEDIT_HASPACKAGES', 13, 1);

    declare @USEDBYMAILING bit;

    set @USEDBYMAILING = case when exists (select * 
                                           from dbo.[MKTMARKETINGPLAN]
                                           inner join [MKTMARKETINGPLANITEM] on [MKTMARKETINGPLANITEM].[MARKETINGPLANID] = [MKTMARKETINGPLAN].[ID]
                                           left join [MKTSEGMENTATION] on [MKTSEGMENTATION].[MARKETINGPLANITEMID] = [MKTMARKETINGPLANITEM].[ID]
                                           where [MKTMARKETINGPLAN].[ID] = @ID and [MKTSEGMENTATION].[ID] is not null) then 1 else 0 end;
    if @USEDBYMAILING = 1
      raiserror('BBERR_PLANBASECURRENCYEDIT_HASMARKETINGEFFORT', 13, 1);

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

    set @CURRENTDATE = getdate();

    begin try
      select 
        @OLDBASECURRENCYID = [BASECURRENCYID]
      from dbo.[MKTMARKETINGPLAN]
      where [ID] = @ID;

      if @OLDBASECURRENCYID <> @BASECURRENCYID
        begin
          update dbo.[MKTMARKETINGPLAN] set
            [BASECURRENCYID] = @BASECURRENCYID,
            [CHANGEDBYID] = @CHANGEAGENTID,
            [DATECHANGED] = @CURRENTDATE
          where [ID] = @ID;            

          set @ORGANIZATIONCURRENCYID = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

          if @BASECURRENCYID = @ORGANIZATIONCURRENCYID
            begin
              update dbo.[MKTMARKETINGPLANITEM] set
                [BASECURRENCYID] = @BASECURRENCYID,
                [CURRENCYEXCHANGERATEID] = null,
                [ORGANIZATIONBUDGETAMOUNT] = [BUDGETAMOUNT],
                [ORGANIZATIONTOTALREVENUEGOAL] = [TOTALREVENUEGOAL],
                [CHANGEDBYID] = @CHANGEAGENTID,
                [DATECHANGED] = @CURRENTDATE
              where [MARKETINGPLANID] = @ID;

              update dbo.[MKTMARKETINGPLANITEMEXPENSE] set
                [BASECURRENCYID] = @BASECURRENCYID,
                [CURRENCYEXCHANGERATEID] = null,
                [ORGANIZATIONAMOUNT] = [AMOUNT],
                [ORGANIZATIONBUDGETAMOUNT] = [BUDGETAMOUNT],
                [CHANGEDBYID] = @CHANGEAGENTID,
                [DATECHANGED] = @CURRENTDATE
              where [MARKETINGPLANITEMID] in (select [ID] from dbo.[MKTMARKETINGPLANITEM] where [MARKETINGPLANID] = @ID);

              update dbo.[MKTMARKETINGPLANBRIEF] set
                [BASECURRENCYID] = @BASECURRENCYID,
                [CURRENCYEXCHANGERATEID] = null,
                [ORGANIZATIONUNITCOST] = [UNITCOST],
                [ORGANIZATIONBUDGETAMOUNT] = [BUDGETAMOUNT],
                [ORGANIZATIONTOTALREVENUEGOAL] = [TOTALREVENUEGOAL],
                [ORGANIZATIONAVERAGEREVENUEGOAL] = [AVERAGEREVENUEGOAL],
                [CHANGEDBYID] = @CHANGEAGENTID,
                [DATECHANGED] = @CURRENTDATE
              where [MARKETINGPLANID] = @ID;
            end
          else
            begin
              update dbo.[MKTMARKETINGPLANITEM] set
                [BASECURRENCYID] = @BASECURRENCYID,
                [CURRENCYEXCHANGERATEID] = dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, [DATEADDED], 0, null),
                [ORGANIZATIONBUDGETAMOUNT] = dbo.[UFN_CURRENCY_CONVERT]([BUDGETAMOUNT], dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, [DATEADDED], 0, null)),
                [ORGANIZATIONTOTALREVENUEGOAL] = dbo.[UFN_CURRENCY_CONVERT]([TOTALREVENUEGOAL], dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, [DATEADDED], 0, null)),
                [CHANGEDBYID] = @CHANGEAGENTID,
                [DATECHANGED] = @CURRENTDATE
              where [MARKETINGPLANID] = @ID;

              update dbo.[MKTMARKETINGPLANITEMEXPENSE] set
                [BASECURRENCYID] = @BASECURRENCYID,
                [CURRENCYEXCHANGERATEID] = dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, [DATEADDED], 0, null),
                [ORGANIZATIONAMOUNT] = dbo.[UFN_CURRENCY_CONVERT]([AMOUNT], dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, [DATEADDED], 0, null)),
                [ORGANIZATIONBUDGETAMOUNT] = dbo.[UFN_CURRENCY_CONVERT]([BUDGETAMOUNT], dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, [DATEADDED], 0, null)),
                [CHANGEDBYID] = @CHANGEAGENTID,
                [DATECHANGED] = @CURRENTDATE
              where [MARKETINGPLANITEMID] in (select [ID] from dbo.[MKTMARKETINGPLANITEM] where [MARKETINGPLANID] = @ID);

              update dbo.[MKTMARKETINGPLANBRIEF] set
                [BASECURRENCYID] = @BASECURRENCYID,
                [CURRENCYEXCHANGERATEID] = dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, [DATEADDED], 0, null),
                [ORGANIZATIONUNITCOST] = dbo.[UFN_CURRENCY_CONVERT]([UNITCOST], dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, [DATEADDED], 0, null)),
                [ORGANIZATIONBUDGETAMOUNT] = dbo.[UFN_CURRENCY_CONVERT]([BUDGETAMOUNT], dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, [DATEADDED], 0, null)),
                [ORGANIZATIONTOTALREVENUEGOAL] = dbo.[UFN_CURRENCY_CONVERT]([TOTALREVENUEGOAL], dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, [DATEADDED], 0, null)),
                [ORGANIZATIONAVERAGEREVENUEGOAL] = dbo.[UFN_CURRENCY_CONVERT]([AVERAGEREVENUEGOAL], dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, [DATEADDED], 0, null)),
                [CHANGEDBYID] = @CHANGEAGENTID,
                [DATECHANGED] = @CURRENTDATE
              where [MARKETINGPLANID] = @ID;
            end
        end
    end try

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

    return 0;
  end