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)
 
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.

Dynamics GP Drop Shipping Sales invoices before purchase invoice with Shipment Notification Tool (SNT)

Hey- I’m going to let you into a little secret. No one talks much about this tool and it is difficult to find information on about it, I read about it some time back and suddenly found a need to use it.

What is it?

The Shipment Notification tool can be an essential tool for Dynamics GP customers who drop ship goods to customers. Drop ship means that the supplier sends the goods directly to the customer, the goods never physically touch your premises. This is a very common situation, for companies using 3rd Party Logistics providers (3PL), those companies may never “touch” the goods they sell, instead the goods are  (mostly) sent direct from the fulfilment service provider to the customer. This is more popular in eCommerce situations due to the robust and easy to use API’s provided by companies such as Fulfilment by Amazon making it a simple way to do business.

The problems many companies face when trying to implement this in the real world using Dynamics GP ERP system, is the desire to invoice your customer immediately on receiving the ship notification or delivery notification from the 3PL provider. Most companies are keen to invoice customers as soon as possible for the best cash flow outcome.

Natively GP will require the purchase invoice from the supplier to be input before the goods can be invoiced using a Sales Order Processing (SOP) sales invoice. Suppliers may consolidate invoices, it is not uncommon for a supplier to perform a monthly billing cycle, consolidating invoices over the month. As sales invoices rely on purchase invoices, this will cause a significant lag in the generation of sales invoices and subsequent impact on cash flow and unintended consequence of extending the customers’ credit terms. Not only that, if the customer returns the goods before they are even invoiced, confusion ensues.

 

How to get it?

Shipment Notification Tool (SNT) is a FREE tool. There are rumours on the Internet that the SNT is distributed within the Professional Services Tool Library, however after going on that wild goose chase it turns out that the Shipment Notification Tool is only available from Microsoft via a support email. A Dynamics Partner, can raise this support case, this is certainly true of GP2013R2. If it is somewhere on Partner Source (the partner version of customer source), then it is well hidden, as no one can find it. The SNT will be emailed by Microsoft as a compressed archive, shown below.Shiment Notification package Note that Encoreblog had copies of the tool on their website, but I would not trust downloading from an unverified source. Careful consideration should be taken if downloading from the internet. The danger is installing a potentially compromised GP dictionary into your ERP system, that theoretically could have spy or malware in it, potentially transmitting your companies core data, or customer data, to a hacker to sell or use for corporate espionage. Is it really worth the time it saves to take the risk? No reflection of integrity of Encoreblog here, merely trying to raise awareness of this danger in general!

Contained inside the archive, there is a SQL script to create the database objects for the companies using the SNT and a cnk file that should be placed into the application directory of GP. The cnk creates the application enhancements to support the tool in the GP client software.

The behaviour of SNT can be changed using configuration switches in the client DEX.INI file. The following is lifted from the one page of instructions (Shipment Notification.doc).

taShipmentNotificationAlwaysTransfer = true
If this switch exists in the dex.ini and is set to true, orders will always be allowed to be transferred to invoices, even if the Shipped flag in the taShipmentNotification table is not marked.

taShipmentNotificationAlwaysPost = true
If this switch exists in the dex.ini and is set to true, invoices will always be allowed to post, even if the Shipped flag is not marked and the purchase invoice has not been processed. (see posting holds section below)

taShipmentNotificationAllowVoids = true
If this switch exists in the dex.ini and is set to true, invoices will be able to be voided, even if they have Purchase Order Commitments.

When installed, a new GP window becomes available. This form allows the drop ship purchase order items to be selected and marked as shipped. This may not be required depending on the settings of the configuration switches set above. Certainly on testing, after installing this tool and setting the DEX switches it is now possible to produce sales invoice before purchase invoices, that was not possible before.

Shipment Notification Window Dynamics GP

Partial Shipments

If the supplier partial ships the order, the Quantity To Invoice and Quantity to Back Order on the sales order can be manually adjusted before transferring to sales invoice. Do not attempt to use the Quantity Fulfilled as this has no meaning for an order that has no physical fulfilment. Note here that the quantity to invoice of the sales line is not tied to the quantity shipped in the shipment notification window.

It seems to be the shipped check box that unlocks the ability to invoice the document. This is annoying as with multiple shipments, after the first shipment the full quantity can be accidentally invoiced on the sales order. Indeed the presence of the POP-SOP link means the following dialog pops up preventing editing of the quantities, so with out breaking the SOP-POP commitment relationship, the whole quantity must be invoiced – argh, no good for partial shipments from suppliers.

Dialog saying "You can't change the Billed Quantity because quantities for this line item are committed to a purchase order"

Again there is no link or awareness of quantities on the shipping notification window from the sale order. Only awareness of the shipped check box. This means the person invoicing the sales orders must reapply the information that the person marking the purchase order as shipped entered. Duplication of effort and risk of errors in my opinion.

This may be an area that I will address with a modification on the shipping window to update the sales quantities in response to the notification quantities, but I don’t know where that leads me in regard to the commitment, do I simply un-commit the quantity shipped? More work required in this area!

image

Another limitation of the shipment notification window, in relation to supplier partial shipments, is the ship date can only be entered once. There is only one row in this “table” for the purchase order line, so as the various shipments are made, the same value is updated adding to it more quantity. As a new row cannot be added for each shipment notification, the detail of the dates the shipments were made is lot recordable. The more I dig into this the more I see the need for implementing a total rewrite of the Shipment Notification Tool. However I really don’t want to do that, yet we get many split despatches from suppliers.

Error This document contains one or more posting holds

When attempting to post a sales invoice in Dynamics GP, if the shipping window feature is in use and where a sales order is drop ship purchase order linked, then a dialog may appear. The following dialog box is not really a posting hold error as the text suggests. I suspect the Shipping Notification window modification is piggy backing on the posting holds function to prevent the posting of sales invoices before the purchase invoice has been posted.

ERROR This document contains one or more posting holds

The actual costs from the purchase invoice (what we have been charged by the supplier for the goods) cannot be used in the sales order once it has been posted.  This error box is preventing the sales invoice being posted until the purchase invoice (with the correct costs) has been processed. This thereby ensures that the drop ship account does not become out of balance due to differences in costs between those recorded in the sales invoice and those recorded in the purchase invoice. This behaviour can be changed with the ini setting mentioned before, taShipmentNotificationAlwaysPost = true. Once this is set the above message is not longer fired and the invoice will post. However more care over the balancing of the accounts is required in this case. This may be appropriate where electronic transactions with agreed pricing is in place and all costs are known up front.

Costs

A question on Dynamics GP Community was asking about how the costs are handled. Costs for the sale will derive from the sales order line as this is the only place it can be obtained until the purchase order invoice is later received and posted.

Limitations using Multiple Binning and a SQL script - Dynamics GP

Updated: 2017-11-27 by TW, to remove reference to schema in SQL script

It seems that the priorities for bins can be entered against an item in the Item Site Default Bins window. The documentation says this is so the bins can be prioritised, for the order they are consumed. It seems the GP application does not actually use this priority, other than in ordering a GUI pick list from which the user can manually choose when to take the stock from. To me this does not seem that well integrated, but to keep the application UI simple and flexible I can see why this may have been done.

The default position is to take the stock from the bin defined in the specific boxes above the priority scrolling window. For example the Sales Order Fulfilment bin will be used by default and the user prompted once supply from that bin is exhausted, it is at this point the user is given the priority list to help them choose when the remainder of stock should be taken from.

Item Site Default Bins Window Dynamics GP

Back in the day, the first thing I did with GP was to automate picking and fulfilment. Advanced distribution didn’t exist or we didn’t know about it back then. Picking and fulfilment in a distribution business with a large warehouses is important so a better solution was produced. When orders are sent to the warehouse by the SOP entry screen (a button alternative to save), the order is checked for credit, needs to be ship complete, and for many other checks. A windows service runs performing all these checks, finally if appropriate a pick list is printed to PDF for archive purposes and also to printer for warehouse picking, another copy is also sent to the large automated storage carousels to allow auto picking from these motorised vertical stores when the picker arrives at them.

The picker takes the next pick list from the printer and picks it, new orders get new picklists on the printer. All our documentation is printed using reporting services, a decision made in the betas of RS, we early adopted years before GP got native support for RS. Before that we used the free Crystal .NET version that shipped with first versions of .NET.  Basically GP report writer sucks, even more so when multilingual highly formatted, multi output format,  invoices/order confirmations/pick lists/despatch notes/receipts/quotations etc are needed.

A gripe I have with the native multiple binning is the way that the location of the goods to be picked can not be “allocated” to the order before picking. How can a picker be directed to the product if it is split over bins, if the bins and qtys are not tied to the order? Hence I have created a table to hold, this info against the order, that is what bins should be used for the pick, for that item. In GP it is only when an item on the order is fulfilled that the link is made in GP, allocating stock in the bin to the order. The goods have to be picked before they can be fulfilled, so this direct link between Qty Fulfilled and Qty allocated seems wrong to be for our use case.

I get off the point, which is that the fulfilment we use is also a bespoke replacement for the native GP system and is very wizard and barcode driven. I don’t want pickers choosing where to take stock from, I want to push them to the stock. Besides, if product variations are held in different locations sales users can then direct the picker to the correct variation (say split packs and sealed packs). 

The advantage of all this being bespoke is that the priorities of the bins can be used. I wrote the script below to act as a source of the data for bin qtys for an item at a location. These are polled on pick production, frozen as an allocation by convention, in a child table of the pick list table, this means the bin locations can be printed on the pick lists, sending the picker to all the locations where stock needs picking, not just the default.

I thought this might be handy for others if using excel or other techniques to pick items. Feel free to hack it to your purposes!

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Tim Wappat
-- Create date: 12th Jan 2016
-- Description: Fetch Bin Qtys for an item ordered by priority
-- Defaults to the SOP Fulfilment Bin if no Priorities
-- =============================================
CREATE PROCEDURE dbo.CA_SOP_BinQtysByItem (
@ITEMNMBR CHAR(31)
,@LOCNCODE CHAR(11)
,@QTYTYPE SMALLINT
)
AS
SET NOCOUNT ON;

-- Gets the bin qtys ordered by bin priority
SELECT IvBins.ITEMNMBR
,IvBins.LOCNCODE
,IvBins.BIN
,IvBins.QTYTYPE
,IvBins.QUANTITY
,IvBins.ATYALLOC
,ISNULL(SiteBinPriorities.[PRIORITY], CASE
WHEN IvSites.SOFULFILLMENTBIN = IvBins.BIN
THEN 0
ELSE 255
END) AS [PRIORITY]
FROM IV00112 IvBins
LEFT JOIN IV00117 SiteBinPriorities ON IvBins.ITEMNMBR = SiteBinPriorities.ITEMNMBR
AND IvBins.LOCNCODE = SiteBinPriorities.LOCNCODE
AND IvBins.BIN = SiteBinPriorities.BIN
LEFT JOIN IV00102 IvSites ON IvSites.ITEMNMBR = IvBins.ITEMNMBR
AND IvSites.LOCNCODE = IvBins.LOCNCODE
WHERE IvBins.ITEMNMBR = @ITEMNMBR
AND IvBins.QTYTYPE = @QTYTYPE
AND IvBins.LOCNCODE = @LOCNCODE
ORDER BY ISNULL(SiteBinPriorities.[PRIORITY], 0)
GO

GRANT EXECUTE
ON dbo.CA_SOP_BinQtysByItem
TO DYNGRP
GO

Microsoft Connect–stop whinging and contribute

“The software should just do that, or I wish it could do this..?”

The site https://connect.microsoft.com is a feedback tool that can be used to submit feedback on Microsoft products. I know after chatting with some of the developers from the MS Dynamics products, that they look at it regularly and take the content seriously.

Join

Go visit the site, login with the login you use to access Microsoft sites normally. Before you can interact with a product’s suggestions (or even visit a link to a suggestion from a social media post), you must link to that product. Connect covers many products so to keep the noise down it works on this subscribe kind of model. The home page lets you search and then click “join” to join that product.

image

Search

You can search for existing suggestions and also vote for them, items getting more votes get more attention from Microsoft, so the community influences the priority. Thus it can be good to whip up interest on Twitter or other social media for a particular feature, to get votes.

image

image

Contribute

It is also possible to create a new suggestion or comment against, or offer alternative solutions to existing suggestions. Connect indeed connects users and partners to Microsoft and others in the community, so get contributing!