USP_DATAFORMTEMPLATE_ADD_OPPORTUNITYAMOUNTBRACKETSGROUP

The save procedure used by the add dataform template "Opportunity Amount Brackets Group Add Data Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@NAME	nvarchar(100)	IN	Name
@DESCRIPTION	nvarchar(255)	IN	Description
@OPPORTUNITYAMOUNTBRACKETS	xml	IN	Amount brackets
@SITES	xml	IN	Sites
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_OPPORTUNITYAMOUNTBRACKETSGROUP
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,
  @NAME nvarchar(100) = '',
  @DESCRIPTION nvarchar(255) = '',
  @OPPORTUNITYAMOUNTBRACKETS xml = null,
  @SITES xml = null,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

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

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

  declare @CURRENTDATE datetime
  set @CURRENTDATE = getdate() 

  -- 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);

  --check for default group

  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

    -- Process the brackets xml to calculate organization amounts.

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

    declare @BASECURRENCYID uniqueidentifier = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);
    declare @ORGANIZATIONCURRENCYID uniqueidentifier;    
    declare @BASETOORGANIZATIONEXCHANGERATEID uniqueidentifier;

    exec dbo.USP_CURRENCY_GETCURRENCYVALUES
        @AMOUNT=null,
        @DATE=@CURRENTDATE,
        @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); 

  -- insert values into the group table

  insert into dbo.OPPORTUNITYAMOUNTBRACKETGROUP
  (
    ID,
    NAME,
    DESCRIPTION,
    BASECURRENCYID,
    ADDEDBYID,
    CHANGEDBYID,
    DATEADDED,
    DATECHANGED
  )
  values
  (
    @ID,
    @NAME,
    @DESCRIPTION,
    @BASECURRENCYID,
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CURRENTDATE,
    @CURRENTDATE
  )

  -- insert the values from the amount ranges grid 

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

  -- insert sites                 

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

return 0