Cart empty
Your Dynamics

Education Experts

SOP Entry Window Cannot Find Unique Number

When you have a large number of orders in the system (in work and history) your master numbers will start to have holes in the sequence when orders are deleted.  The system tries to fill these holes.  Problem is, the next number is then set to something that exists.  When you try to add the next order, it sees the next number is used, steps forward 1, and checks again.  It repeats this test, one step at a time ONLY 100 times and then gives the error message above.

Here is a script that replaces the sopGetMasterNumber script with one that will skip down to the next highest number.  It was provided by GP.  Make sure to backup your system before running this as it replaces a stored procedure in the database.

 

/****** Object:  Stored Procedure dbo.sopGetMasterNumber     ******/
if exists (select * from sysobjects where id = object_id('dbo.sopGetMasterNumber') and sysstat & 0xf = 4)
 drop procedure dbo.sopGetMasterNumber
GO

create procedure dbo.sopGetMasterNumber
        @O_iOUTMasterNumber                   int             = NULL  output,
        @O_iErrorState                        int             = NULL  output
as     


 /*
 **********************************************************************************************************
 * (c) 1994 Great Plains Software, Inc.
 **********************************************************************************************************
 *
 * PROCEDURE NAME:      sopGetMasterNumber
 *
 * SANSCRIPT NAME:      Get_Master_Number of form SOP_Entry
 *
 * PARAMETERS:
 * @O_iOUTMasterNumber Retreived Master Number
 * @O_iErrorState  contains any errors that occur in this procedure
 *
 * DESCRIPTION:
 *   Returns the next number field from the given SOP_SETP record and increments
 * the next number.
 *
 * Customization was made to look at SOP40500 to verify the NXTMSTNO is larger than existing values.
 *
 * TABLES:
 *              Table Name                      Access
 *              =========                       =====
 *  SOP40100   Read/Write
 *
 * DATABASE:   
 *              Company
 *
 *
 * RETURN VALUE:
 *
 *      0 = Successful
 *     non-0 = Not successful
 *
 * REVISION HISTORY:
 *
 *      Date           Who             Comments
 *      -------------  --------        -------------------------------------------------
 * 24Jun98  msluke  Initial Creation
 *****************************************************************************
 */

declare  @tTransaction           tinyint,
  @iError   int,
  @MaxMSTRNUMB  int

/*
 * Initialize variables and Output Parameters.
 */
select  @O_iOUTMasterNumber = 0,
 @O_iErrorState  = 0


/*
* Start a transaction if the trancount is 0.
*/
if @@trancount = 0
begin
        select @tTransaction = 1
        begin transaction
end

/*
 * Read record from SOP_SETP table within an update statement so a lock is held
 * on the record until the number is updated. This will ensure that only a single
 * user is reading this record at any given time.
 */
update
 SOP40100  WITH (TABLOCKX,HOLDLOCK)
set
 @O_iOUTMasterNumber = NXTMSTNO,
 NXTMSTNO= NXTMSTNO + 1

if ( @@rowcount <> 1)
 begin
 /* Failed writing to SOP40100. */
 select          @O_iErrorState                  = 21035 /* Failed writing to SOP40100 */
 end
/*
 * Do an additional read from SOP40500 to attempt to recover from the situation where the NXTMSTNO
 * is less than or equal to the max value in SOP40500. 
 */
select @MaxMSTRNUMB = max(MSTRNUMB) from SOP40500 (nolock)
if (@MaxMSTRNUMB >= @O_iOUTMasterNumber)
 begin
 update
  SOP40100
 set
  @O_iOUTMasterNumber = @MaxMSTRNUMB + 1,
  NXTMSTNO= @MaxMSTRNUMB + 2
  
 if ( @@rowcount <> 1)
  begin
  /* Failed writing to SOP40100. */
  select          @O_iErrorState                  = 21035 /* Failed writing to SOP40100 */
  end
 end

/*
 *  Reset next master number to 2, if master number has reached max value
 *  or it is zero.
 */
if (( @O_iOUTMasterNumber = 99999999) or ( @O_iOUTMasterNumber = 0)) and @O_iErrorState = 0
 begin
 select   @O_iOUTMasterNumber = 1
   
 update
  SOP40100
 set
  NXTMSTNO = 2

 if ( @@rowcount <> 1)
  begin
  /* Failed writing to SOP40100. */
  select          @O_iErrorState                  = 21035 /* Failed writing to SOP40100 */
  end
 end


/*
 * Determine if a rollback or commit should be executed.
 */
if @O_iErrorState <> 0
 begin
        select           @O_iOUTMasterNumber    = 0
        /*
         * Rollback the transaction if this procedure started it.
         */
        if @tTransaction = 1
                rollback transaction
 end
else
 begin
        /*
         *  Commit the transaction if this procedure started it.
         */

         if @tTransaction = 1
                commit transaction
 end

return

GO

GRANT  EXECUTE  ON dbo.sopGetMasterNumber  TO DYNGRP
GO