Generating *SEQ (LNITEMSEQ etc) column values in Dynamics GP

How does LNITMSEQ work?

There are sequence columns used to introduce order into rows within Dynamics GP. Examples are such as LNITMSEQ that identifies the line numbers of sales orders in SOP10200 and SOP30300. When new rows are appended to these tables, the sequence column value becomes 16384 * the line number. Basically 16384 is added to the last existing value for the sequence in the table. By having gaps in the numbering, this allows records to be inserted in between the existing records without renumbering the entire sequence, with the performance impact that would have had back in the era when GP was born. This is a similar technique that BASIC programmers used to use, to allow code to be inserted, lines would be incremented in tens.

Note that although there is a mathematical relationship between the row number  and the sequence number to begin with, if new lines are inserted and/or deleted, that relationship is immediately destroyed. Finding line numbers is better done using SQL windowing functions like ROW_NUMBER() than trying to derive it from the SEQ.

Below is a demonstration, where a line has been inserted after the first line, the sequence value is half way between the adjacent values (24576).

Applying that learning

If there is a requirement to “bulk” insert rows after the last record and the table has a sequence column, the next whole multiple of 16384 after the last sequence number is required.

To find the next number in the sequence I wrote the following SQL:

@tmpSEQNUMBER = CEILING(CAST(MAX(SEQNUMBR) AS DECIMAL(19, 5)) / 16384) + 1 
              - CEILING((CAST(MAX(SEQNUMBR) AS DECIMAL(19, 5)) / 16384) % 1)

int NextLineSequenceNumber = 
Convert.ToInt32((Math.Ceiling(MaxLineSequenceNumber / 16384) + 1) 
- Math.Ceiling(MaxLineSequenceNumber / 16384) % 1) * 16384;

Dim NextLineSequenceNumber As Integer =
 CInt((Math.Ceiling(MaxLineSequenceNumber / 16384) + 1) 
    - Math.Ceiling(MaxLineSequenceNumber / 16384) Mod 1) 
    * 16384

(ROW_NUMBER() OVER (ORDER BY SOPNUMBE) -- actual row number
+ @tmpSEQNUMBER                        -- row number offset 
) * 16384                               -- GP multiplier

DECLARE @tmpSEQNUMBER INT

SELECT @tmpSEQNUMBER = CEILING(CAST(MAX(SEQNUMBR) AS DECIMAL(19, 5)) / 16384) + 1 
                    - CEILING((CAST(MAX(SEQNUMBR) AS DECIMAL(19, 5)) / 16384) % 1)
FROM SOP10203
WHERE SOPNUMBE = @SOPNUMBE
    AND SOPTYPE = @SOPTYPE
    AND LNITMSEQ = @LNITMSEQ
    AND CMPNTSEQ = @CMPNTSEQ
-- if no pre existing rows @tempSEQNUMBER will be null, so seed with zero
SELECT @tmpSEQNUMBER = ISNULL(@tmpSEQNUMBER, 0)

INSERT INTO [SOP10203] (
    [SOPNUMBE]
    ,[SOPTYPE]
    ,[LNITMSEQ]
    ,[CMPNTSEQ]
    ,[SEQNUMBR]
    ,[ITEMNMBR]
    ,[LOCNCODE]
    ,[BIN]
    ,[QTYTYPE]
    ,[QUANTITY]
    ,[POSTED]
    )
SELECT SOPNUMBE
    ,SOPTYPE
    ,LNITMSEQ
    ,CMPNTSEQ
    ,(
        ROW_NUMBER() OVER (
            ORDER BY SOPNUMBE --fake order
            ) + @tmpSEQNUMBER
        ) * 16384
    ,--generate seq
    ITEMNMBR
    ,LOCNCODE
    ,BIN
    ,QTYTYPE
    ,QUANTITY
    ,0
FROM [CA_SOP_PicklistDetailBins] --our bin allocation to apply
WHERE SOPNUMBE = @SOPNUMBE
    AND SOPTYPE = @SOPTYPE
    AND LNITMSEQ = @LNITMSEQ
    AND CMPNTSEQ = @CMPNTSEQ
    AND DT_Pick_No = @PICKNUMBER