SOP Master Numbers not being assigned properly

 

A unique document number could not be found. please check setup.

I been with a situation where my customer were getting the above messages, noticed that the system is updating the master number in SOP40100 to a smaller number which sometimes being exist in SOP10100 or SOP30200.

I workaround this by creating a trigger on SOP10100 and SOP30200 to update the SOP40100 once saving or updating transactions and set the next master number to be current maximum master number plus 1!

Below the scripts I used:

CREATE TRIGGER SOP30200UPDATEMASTER
ON SOP30200
AFTER INSERT, UPDATE
AS
BEGIN

DECLARE @MASTERNUMBER BIGINT
SELECT @MASTERNUMBER = ISNULL(MAX(MSTRNUMB), 0) FROM
(SELECT MAX(MSTRNUMB) AS MSTRNUMB FROM SOP10100
UNION ALL
SELECT MAX(MSTRNUMB) AS MSTRNUMB FROM SOP30200) AS MASTERS

UPDATE SOP40100 SET NXTMSTNO = @MASTERNUMBER
END

GO

CREATE TRIGGER SOP30200UPDATEMASTER
ON SOP30200
AFTER INSERT, UPDATE
AS
BEGIN

DECLARE @MASTERNUMBER BIGINT
SELECT @MASTERNUMBER = ISNULL(MAX(MSTRNUMB), 0) FROM
(SELECT MAX(MSTRNUMB) AS MSTRNUMB FROM SOP10100
UNION ALL
SELECT MAX(MSTRNUMB) AS MSTRNUMB FROM SOP30200) AS MASTERS

UPDATE SOP40100 SET NXTMSTNO = @MASTERNUMBER
END

GO

Enjoy!

UPDATE: Folks at Accolade Publications, Inc has publish an article that contains a modified script to fix this issue, below is the modified script:

/****** 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

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com