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 **|
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 **|
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:
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.
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.