USP_BUSINESSPROCESS_CREATEORUPDATE

      CREATE procedure [dbo].[USP_BUSINESSPROCESS_CREATEORUPDATE]
        @ID uniqueidentifier = null output,
        @NAME nvarchar(100),
        @DESCRIPTION nvarchar(1000),
        @BUSINESSPROCESSSPECXML xml,
        @RECORDTYPE nvarchar(100),
        @PARAMETERTABLENAME nvarchar(100),
        @PARAMETERLABELFIELD nvarchar(100),
        @ALLOWSIMULTANEOUSEXECUTION bit,
        @CHANGEAGENTID uniqueidentifier=null,
        @ALLOWENQUEUE bit = 0,
        @ENQUEUEGROUP nvarchar(255) = null
      as

      set nocount on;

      if @NAME is null or @NAME = ''
        begin
          raiserror ('Name was missing or blank', 16,1);
          return 5;
        end

      if @DESCRIPTION is null or @DESCRIPTION = ''
        begin
          raiserror ('Description was missing or blank', 16,1);
          return 5;
        end

      if @RECORDTYPE is null or @RECORDTYPE = ''
        begin
          raiserror ('RecordType was missing or blank', 16,1);
          return 5;
        end

      declare @ExistingID uniqueidentifier;

      if @ID is null
        begin
          select @ExistingID = ID from dbo.BUSINESSPROCESSCATALOG where NAME = @NAME;
          set @ID = @ExistingID;
        end
      else
        select @ExistingID = ID from dbo.BUSINESSPROCESSCATALOG where ID = @ID;

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

      declare @date datetime;
      set @date = getDate();

      --Add record type if it does not exist.        

      declare @RecordTypeID uniqueidentifier;
      exec dbo.USP_RECORDTYPE_GETID_OR_CREATE_FROMNAME @NAME=@RECORDTYPE,@CHANGEAGENTID=@CHANGEAGENTID,@RECORDTYPEID=@RecordTypeID output;

      declare @SECURITYUIFOLDER nvarchar(255);
      set @SECURITYUIFOLDER=
       coalesce(
          @BUSINESSPROCESSSPECXML.value(
                      '
                      declare namespace bbfa="bb_appfx_businessprocess";
                      declare namespace c="bb_appfx_commontypes";
                      /bbfa:BusinessProcessSpec[1]/@c:SecurityUIFolder
                      '
                      ,'nvarchar(255)')
          ,'');


      if @ExistingID is null 
        begin
          if @ID is null
            set @ID = NewID();

          insert into dbo.BUSINESSPROCESSCATALOG
            (ID, NAME, DESCRIPTION, BUSINESSPROCESSSPECXML, PARAMETERTABLENAME, PARAMETERLABELFIELD, RECORDTYPEID, ALLOWSIMULTANEOUSEXECUTION, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, SECURITYUIFOLDER, ALLOWENQUEUE, ENQUEUEGROUP)
          values 
            (@ID, @NAME, @DESCRIPTION, @BUSINESSPROCESSSPECXML, @PARAMETERTABLENAME, @PARAMETERLABELFIELD, @RECORDTYPEID, @ALLOWSIMULTANEOUSEXECUTION, @CHANGEAGENTID, @CHANGEAGENTID, @date, @date, @SECURITYUIFOLDER, @ALLOWENQUEUE, @ENQUEUEGROUP);
        end
      else
        begin
          update
            dbo.BUSINESSPROCESSCATALOG
          set 
            NAME = @NAME,
            DESCRIPTION = @DESCRIPTION,
            BUSINESSPROCESSSPECXML = @BUSINESSPROCESSSPECXML,
            RECORDTYPEID = @RECORDTYPEID,
            PARAMETERTABLENAME = @PARAMETERTABLENAME,
            PARAMETERLABELFIELD = @PARAMETERLABELFIELD,
            ALLOWSIMULTANEOUSEXECUTION = @ALLOWSIMULTANEOUSEXECUTION,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @date,
            SECURITYUIFOLDER = @SECURITYUIFOLDER,
            [ALLOWENQUEUE] = @ALLOWENQUEUE,
            [ENQUEUEGROUP] = @ENQUEUEGROUP
          where
            ID = @ID;
        end

      if @@ROWCOUNT=0
        return 3;

      return 0;