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.
Line number | SEQ |
1 | 16384 |
2 | 32768 |
3 | 49152 |
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).
Line number | SEQ |
1 | 16384 |
2 <inserted> | 24576 |
3 | 32768 |
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
This is calculating the next row number where the sequence left off.
(ROW_NUMBER() OVER (ORDER BY SOPNUMBE) -- actual row number
+ @tmpSEQNUMBER -- row number offset
) * 16384 -- GP multiplier
Real world example
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