USP_DATAFORMTEMPLATE_EDIT_OPPORTUNITYAMOUNTBRACKETSGROUP

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_OPPORTUNITYAMOUNTBRACKETSGROUP (
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @NAME nvarchar(100),
  @DESCRIPTION nvarchar(255),
  @OPPORTUNITYAMOUNTBRACKETS xml,
  @SITES xml
)
as

  set nocount on;

  if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

  declare @CURRENTDATE datetime
  set @CURRENTDATE = getdate()

  begin try        
    -- check if there's a default site

    declare @ISDEFAULTGROUP smallint;
    declare @SITECOUNT smallint;

    select @ISDEFAULTGROUP = COUNT(ID)
    from OPPORTUNITYAMOUNTBRACKETGROUP
    where ID not in (select distinct OPPORTUNITYAMOUNTBRACKETGROUPID from dbo.OPPORTUNITYAMOUNTBRACKETSITES)
    and ID <> @ID;

    declare @TempTbl table (
      SITEID uniqueidentifier
    );
    insert into @TempTbl
      select
        SITEID
      from 
        dbo.UFN_OPPORTUNITYAMOUNTBRACKETSITES_FROMITEMLISTXML(@SITES);
    select @SITECOUNT = count(SITEID) from @TempTbl

    if @ISDEFAULTGROUP > 0 and @SITECOUNT = 0 
    begin
       raiserror('DEFAULTGROUP_EXISTS', 13, 1);
    end

    --Check for duplicate lower limits  

    declare @TempTbl2 table (
      LOWERLIMIT money
    );
    insert into @TempTbl2
      select
        LOWERLIMIT
      from 
        dbo.UFN_OPPORTUNITYAMOUNTBRACKETS_FROMITEMLISTXML(@OPPORTUNITYAMOUNTBRACKETS);
    if exists(select LOWERLIMIT from @TempTbl2 group by LOWERLIMIT having COUNT(*) > 1)
      begin
         raiserror('LOWERLIMIT_DUPLICATES', 13, 1);
      end      

    update 
      dbo.OPPORTUNITYAMOUNTBRACKETGROUP
    set 
      NAME= @NAME,
      DESCRIPTION = @DESCRIPTION,        
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE
    where 
      ID = @ID    

    -- Process the brackets xml to calculate organization amounts.

    --Multicurrency - SlyyMu 8/19/10 - Get the updated exchange rate.    

    declare @BASECURRENCYID uniqueidentifier;
    declare @ORGANIZATIONCURRENCYID uniqueidentifier;    
    declare @BASETOORGANIZATIONEXCHANGERATEID uniqueidentifier;
    declare @DATE datetime;

    select
      @BASECURRENCYID = BASECURRENCYID,  
      @DATE = DATEADDED      
    from
      dbo.OPPORTUNITYAMOUNTBRACKETGROUP
    where
      ID = @ID;

    exec dbo.USP_CURRENCY_GETCURRENCYVALUES
        @AMOUNT=null,
        @DATE=@DATE,
        @BASECURRENCYID=@BASECURRENCYID,
        @BASEEXCHANGERATEID=null,
        @TRANSACTIONCURRENCYID=null,
        @BASEAMOUNT=null,
        @ORGANIZATIONCURRENCYID=@ORGANIZATIONCURRENCYID,
        @ORGANIZATIONAMOUNT=null,
        @ORGANIZATIONEXCHANGERATEID=null,
        @LOOKUPORGANIZATIONEXCHANGERATE=0,
        @BASETOORGANIZATIONEXCHANGERATEID=@BASETOORGANIZATIONEXCHANGERATEID output;

    set @OPPORTUNITYAMOUNTBRACKETS = dbo.UFN_OPPORTUNITYAMOUNTBRACKET_CONVERTAMOUNTSINXML(@OPPORTUNITYAMOUNTBRACKETS, @BASECURRENCYID, @BASETOORGANIZATIONEXCHANGERATEID); 

    -- update the values from the amount ranges grid 

    exec dbo.USP_OPPORTUNITYAMOUNTBRACKET_GETBRACKETS_2_UPDATEFROMXML @ID, @OPPORTUNITYAMOUNTBRACKETS, @CHANGEAGENTID; 

    -- update sites                 

    exec dbo.USP_OPPORTUNITYAMOUNTBRACKETSITE_GETSITES_UPDATEFROMXML @ID, @SITES, @CHANGEAGENTID;    

  end try

  begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
  end catch

return 0;