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!

Find carriage return (cr) and line feed (lf) in NOTES fields of Dynamics GP

This is a Epson FX80 dot matrix printer. For those of us who remember RS232 driving these things using DOS or Turbo Pascal then you know that to make it move down a line, then a line feed ASCII character would need sending in the text stream, a char(10).

linefeedprinter

To make the printer move its print head to the start of the line (left had column) a carriage return would have to be issued, a char(13). This heralded from the mechanical typewriter origins of printers where on the typewriter, you’d have to move a line down and push the carriage to the start of the line again (return the carriage). – Yes I still own a mechanical typewriter…

Although the behaviour could be configured, often with DIP switches, electrical config switches hidden in the printer or using config ESC codes, it was common in computing to have to have a char(10)+char(13) sequence at the end of lines. However it was not uncommon (UNIX) to find just a char(10) alone used.

if it has not be sanitised before injection into the GP database, Dynamics GP notes fields, can have both or one of these characters used. However only char(13) should be used as char(10) gives  an ugly block character in the text when displayed in the user interface, for some versions of GP. We use Profad Enhanced notes in place of the default notes window, where the type of line feed character no longer seems to matter.

To see what line endings are present, the output saved from SQL can be viewed in a text (HEX enabled) editor allowing the hex of the file to be viewed, or it can be easier to translate the line feeds and carriage returns into tokens using SQL script like this and view in normal SQL server Management Studio:

SELECT 
REPLACE(
REPLACE(CAST(TXTFIELD AS VARCHAR(max)), CHAR(10), '[lf]'),
CHAR(13), '[cr]'
)
FROM sy03900
WHERE NOTEINDX=0000000

Where the zeros of NOTEINDX are replaced with the note id of interest and every carriage return will show as [cr] and every line feed as [lf].

Here is an example of the output of this SQL, a bad example with [cr][lf] sequence in the notes

image

If the fields need cleaning because of blocky characters, then I wrote about this a while back Formatting notes using GP econnect

This wiki has much more info and more detailed information for those who are interested https://en.wikipedia.org/wiki/Newline