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)

As a side note, for .NET try the following where MaxLineSequenceNumber is the max seq existing in the table:

C#

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

VB.NET

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

This is calculating the next row number where the sequence left off.

This row number can then be used together with the ROW_NUMBER() SQL window function to create a calculated column that is then used to insert the values into the sequence column. Here is the SQL I wrote to do that bit, see below for these snippets in context of the full SQL script.

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

Real world example

The following script demonstrates a practical application of these snippets where the need is to apply a BIN number allocations of stock against sales orders from a custom table generated by the picking list. The new stock is allocated from the BINS to the sales order using SOP10203 and the allocations are added as rows to the end of the existing sequence of allocations. This table has a SEQNUMBER field that is used to maintain the order of the records in the table and also takes part in the compound key for the record.

In this example parameters; @SOPNUMBE, @SOPTYPE, @LNITMSEQ, @CMPNTSEQ define the sales order line.

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

I hope this post may help someone with some ideas on how to tackle variants on this problem.