Handling SOP Document Number Chaos: A Dynamic SQL Solution

Today, I encountered an issue where the sales document next number sequence in Dynamics GP went haywire. In just 10 minutes, quotes were transferred, orders were manually entered, and eConnect imported eCommerce orders—all with incorrect SOP document numbers.

The Challenge

Twenty sales orders were affected before the problem was noticed. After fixing the next SOP number sequence in GP, I needed a script to correct the SOP document numbers. But with a heavily customized GP install, referencing SOP numbers and types across 120 tables (spread across different SQL schemas), doing this manually wasn’t an option.

Given the well-defined scope of the issue and my knowledge of the data, I figured I could use dynamic SQL to renumber the documents. The challenge was to find every table with references to SOPNUMBE and SOPTYPE, then update the values with the correct sequence.

Approach

First, I called the GP eConnect stored procedure to retrieve the next document number, passing it the existing document details. Then, I enhanced the SQL script to dynamically iterate through all relevant tables and update the SOP document numbers.

⚠️ Note: This solution worked because all the orders were "fresh," i.e., just entered. Once orders are transferred to other document types, it gets trickier. You need to ensure the original document number is also updated in the target document, and there could be side effects like incorrect references in reports or customer emails. Always assess the consequences if you're dealing with non-fresh orders.

Solution

Replace the OldSOPNUMBE variable with the incorrect document number. The script will automatically generate a new number and replace the old one across all tables.

Pro Tip: After running the script, check for any duplicate keys or other data issues by looking for errors in the output window of SSMS. Also, some tables might only reference SOPNUMBE without SOPTYPE, especially in custom solutions or certain GP modules like service.

Finding Tables Missing SOPTYPE

If you encounter tables that only have SOPNUMBE without SOPTYPE, deal with them manually. Here’s a script to help identify those tables:

 SELECT s.name AS SchemaName, t.name AS TableName
    FROM sys.tables t
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    JOIN sys.columns c1 ON t.object_id = c1.object_id AND c1.name = 'SOPNUMBE'
    LEFT JOIN sys.columns c2 ON t.object_id = c2.object_id AND c2.name = 'SOPTYPE'
	WHERE c2.column_id is null 

Renumbering Sales Documents in Dynamics GP

Below is the script I used to renumber the SOP documents dynamically across all tables:

---- check for errors after running

DECLARE @OldSOPNUMBE NVARCHAR(50) = '00426109'
DECLARE @OldSOPTYPE TINYINT = 2

--Use econnect stored proc to get next SOPNUMBER

DECLARE @return_value INT
DECLARE @I_cDOCID CHAR(15) = (SELECT DOCID FROM SOP10100 WHERE SOPNUMBE=@OldSOPNUMBE AND SOPTYPE=@OldSOPTYPE)
DECLARE @I_tInc_Dec TINYINT = 1
DECLARE @NewSOPNUMBE AS VARCHAR(21)
DECLARE @O_iErrorState INT
	
EXEC @return_value = taGetSopNumber  @I_tSOPType = @OldSOPTYPE, @I_cDOCID = @I_cDOCID, @I_tInc_Dec = @I_tInc_Dec, @O_vSopNumber = @NewSOPNUMBE  OUTPUT,  @O_iErrorState = @O_iErrorState OUTPUT
	
PRINT 'New assigned SOPNUMBE: ' + @NewSOPNUMBE
DECLARE @TableName NVARCHAR(255)
DECLARE @SchemaName NVARCHAR(255)
DECLARE @SQL NVARCHAR(MAX)

-- Cursor to go through each table that contains a column named SOPNUMBE
DECLARE cur CURSOR FOR
     SELECT s.name AS SchemaName, t.name AS TableName
    FROM sys.tables t
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    JOIN sys.columns c1 ON t.object_id = c1.object_id AND c1.name = 'SOPNUMBE'
    JOIN sys.columns c2 ON t.object_id = c2.object_id AND c2.name = 'SOPTYPE'

OPEN cur
FETCH NEXT FROM cur INTO @SchemaName, @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
print 'Update to: ' + @schemaname +'.'+ @tablename
    -- Build the dynamic SQL to update the SOPNUMBE field in the current table with the schema name for the document type in question
    SET @SQL = 'UPDATE [' + @SchemaName + '].[' + @TableName + '] ' + 
               'SET SOPNUMBE = @NewSOPNUMBE ' + 
               'WHERE SOPNUMBE = @OldSOPNUMBE AND SOPTYPE=' + @OldSOPTYPE 

    -- Execute the dynamic SQL
    EXEC sp_executesql @SQL, N'@OldSOPNUMBE NVARCHAR(50), @NewSOPNUMBE NVARCHAR(50)', 
                       @OldSOPNUMBE, @NewSOPNUMBE

    FETCH NEXT FROM cur INTO @SchemaName, @TableName
END

CLOSE cur
DEALLOCATE cur

This script saved me hours of manual updates. If you face a similar challenge with SOP document numbers, feel free to adapt this solution, but proceed carefully—there are always ripple effects when altering document numbers, especially in a live system! Always test in a test environment first.

This post walks you through my recent experience with SOP document renumbering using SQL in Dynamics GP. Let me know if you've faced similar issues and how you tackled them!