USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATIONBASECURRENCY

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

    declare @HASPACKAGE bit;
    declare @HASAPPEAL bit;
    declare @ISCALCULATING bit;
    declare @ISACTIVATING bit;
    declare @ACTIVE bit;
    declare @HASASKLADDEROVERRIDES bit;
    declare @CREATEDFROMPLAN bit;

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

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

    begin try
      select
        @ACTIVE = isnull([MKTSEGMENTATION].[ACTIVE], 0),
        @ISCALCULATING = isnull(dbo.[UFN_MKTSEGMENTATION_ISCALCULATING](@ID), 0),
        @ISACTIVATING = isnull(dbo.[UFN_MKTSEGMENTATION_ISACTIVATING](@ID), 0),
        @HASPACKAGE = (case when exists (select top 1 1 from dbo.[MKTSEGMENTATIONPACKAGE] where [SEGMENTATIONID] = @ID) then 1 else 0 end),
        @HASAPPEAL = (case when exists(select top 1 1 from dbo.[MKTSEGMENTATIONACTIVATE] where [SEGMENTATIONID] = @ID) then 1 else 0 end),
        @HASASKLADDEROVERRIDES = (case when (select dbo.[UFN_MKTSEGMENTATIONASKLADDEROVERRIDE_GETITEMLIST_TOITEMLISTXML](@ID)) is not null then 1 else 0 end),
        @CREATEDFROMPLAN = (case when [MKTSEGMENTATION].[MARKETINGPLANITEMID] is not null then 1 else 0 end)
      from dbo.[MKTSEGMENTATION]
      where [MKTSEGMENTATION].[ID] = @ID;

      if @ACTIVE = 1
        raiserror('BBERR_MKTSEGMENTATIONBASECURRENCYEDIT_ACTIVE', 13, 1);

      if @ISCALCULATING = 1
        raiserror('BBERR_MKTSEGMENTATIONBASECURRENCYEDIT_CALCULATING', 13, 1);

      if @ISACTIVATING = 1
        raiserror('BBERR_MKTSEGMENTATIONBASECURRENCYEDIT_ACTIVATING', 13, 1);

      if @HASPACKAGE = 1
        raiserror('BBERR_MKTSEGMENTATIONBASECURRENCYEDIT_PACKAGE', 13, 1);

      if @HASAPPEAL = 1
        raiserror('BBERR_MKTSEGMENTATIONBASECURRENCYEDIT_APPEAL', 13, 1);

      if @HASASKLADDEROVERRIDES = 1
        raiserror('BBERR_MKTSEGMENTATIONBASECURRENCYEDIT_ASKLADDEROVERRIDE', 13, 1);

      if @CREATEDFROMPLAN = 1
        raiserror('BBERR_MKTSEGMENTATIONBASECURRENCYEDIT_MARKETINGPLAN', 13, 1);

      declare @OLDBASECURRENCYID uniqueidentifier;
      declare @ORGANIZATIONCURRENCYID uniqueidentifier;
      declare @ORGANIZATIONCURRENCYEXCHANGERATEID uniqueidentifier;
      declare @DATEADDED datetime;

      select 
        @OLDBASECURRENCYID = [BASECURRENCYID]
      from dbo.[MKTSEGMENTATION]
      where [ID] = @ID;

      -- if the user saves without changing the base currency, don't update organization amounts
      if @OLDBASECURRENCYID <> @BASECURRENCYID
        begin
          set @ORGANIZATIONCURRENCYID = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

          -- if the new base currency is the organization currency, the organization amount is the same as the amount, so don't bother getting
          -- an exchange rate or doing a conversion
          if @BASECURRENCYID = @ORGANIZATIONCURRENCYID
            begin
              update dbo.[MKTSEGMENTATION] set
                [BASECURRENCYID] = @BASECURRENCYID,
                [CHANGEDBYID] = @CHANGEAGENTID,
                [DATECHANGED] = @CURRENTDATE
              where [ID] = @ID;

              update dbo.[MKTSEGMENTATIONBUDGET] set
                [BASECURRENCYID] = @BASECURRENCYID,
                [ORGANIZATIONFIXEDCOST] = [FIXEDCOST],
                [ORGANIZATIONBUDGETAMOUNT] = [BUDGETAMOUNT],
                [CHANGEDBYID] = @CHANGEAGENTID,
                [DATECHANGED] = @CURRENTDATE
              where [ID] = @ID;
            end
          else -- base currency has changed and isn't the organization currency
            begin
              select
                @DATEADDED = [DATEADDED]
              from dbo.[MKTSEGMENTATION]
              where [ID] = @ID;

              set @ORGANIZATIONCURRENCYEXCHANGERATEID = dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @DATEADDED, 0, null);

              update dbo.[MKTSEGMENTATION] set
                [BASECURRENCYID] = @BASECURRENCYID,
                [CHANGEDBYID] = @CHANGEAGENTID,
                [DATECHANGED] = @CURRENTDATE
              where [ID] = @ID;

              update dbo.[MKTSEGMENTATIONBUDGET] set
                [BASECURRENCYID] = @BASECURRENCYID,
                [ORGANIZATIONFIXEDCOST] = dbo.[UFN_CURRENCY_CONVERT]([FIXEDCOST], @ORGANIZATIONCURRENCYEXCHANGERATEID),
                [ORGANIZATIONBUDGETAMOUNT] = dbo.[UFN_CURRENCY_CONVERT]([BUDGETAMOUNT], @ORGANIZATIONCURRENCYEXCHANGERATEID),
                [ORGANIZATIONCURRENCYEXCHANGERATEID] = @ORGANIZATIONCURRENCYEXCHANGERATEID,
                [CHANGEDBYID] = @CHANGEAGENTID,
                [DATECHANGED] = @CURRENTDATE
              where [ID] = @ID;
            end
        end
    end try

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

    return 0;
  end