Adding process hold to a sales order processing document (SOP10104)

Sales order holds table

This table holds the data relating to process holds on sales orders, the table is shared between work and historical transactions.

Historical transactions will have a posting audit value in the TRXSORCE field, such as :
SLSVT00054292
SLSTE00001743

Work transactions (live orders) will have blank TRXSORCE

[more]Holds that are applied (active holds) have a value in the DELETE1 field of 0, holds that have been applied but are now removed have a DELETE1 value of 1. The DELETE1 field name is a common convention in GP for fields that share a name with that of SQL keywords, the 1 although technically unnecessary from a SQL perspective, is used to make it not be confused with the keyword.

The user ID that interacted with the hold goes in the user id field.

Hold date and time are obvious, but strip off the date from the time and the time from the date, see my stored procedure example below.

DEX_ROW_ID is merely an identity field in the database, it will be generated for you.

PRCHLDID must exist in the hold definition table,

    select * from SOP00100

Example of how to create a process hold stored procedure

-- ===========================================================================
-- Author:        Tim Wappat
-- Create date: 17th Aug 2007
-- Description:    Allows a sales hold to be added or changed on a SOP document
--                
-- ===========================================================================
ALTER PROCEDURE [myschema].[SOP_AddHoldToSalesDoc] @SOPNUMBE CHAR(21)
    ,@SOPTYPE SMALLINT
    ,@PRCHLDID CHAR(15)
    ,@USERID CHAR(15)
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE SOP10104
    SET DELETE1 = 0
        ,USERID = @USERID
        ,HOLDDATE = CONVERT(VARCHAR(255), GETDATE(), 102)
        ,TIME1 = CONVERT(CHAR(8), GETDATE(), 108)
    WHERE SOPNUMBE = @SOPNUMBE
        AND SOPTYPE = @SOPTYPE
        AND PRCHLDID = @PRCHLDID
        AND TRXSORCE=''

    IF @@ROWCOUNT = 0
    BEGIN
        INSERT INTO SOP10104 (
            SOPTYPE
            ,SOPNUMBE
            ,PRCHLDID
            ,DELETE1
            ,USERID
            ,HOLDDATE
            ,TIME1
            ,TRXSORCE
            )
        VALUES (
            @SOPTYPE
            ,@SOPNUMBE
            ,@PRCHLDID
            ,0
            ,@USERID
            ,CONVERT(VARCHAR(255), GETDATE(), 102)
            ,CONVERT(CHAR(8), GETDATE(), 108)
            ,''
            )
    END
END

Important note

The GP maintenance utility checklinks will remove (delete) sales order holds that have incorrect values in the fields of SOP10104. I’ve seen hundreds of records removed as the TRXSORCE had a value yet the order was not in history, breaking the rule.

The user id can be made up is a system process is creating the hold, this can help identify it was the system, although I tend to use sa now to do this or create a dummy user, just in case it is checked for in future versions that the user exists.