Dynamics GP– Process to invoice only fulfilled and shipped items for scheduled, drop-ship or call off sales orders

Photo of Cable Drums

What are call off orders and scheduled orders?

If a customer wants to order 12km of cable supplying for a long running project, but only wants it supplied in 1km despatches over the next twelve weeks, perhaps to different sites. Then this is a call off or a scheduled order. Here we will talk about cable as an example, but it could be applied to most products.

If it is a call off order, then the customer will call/email/electronically transact to request (call off the order) another x km of cable for date x on an ad-hoc basis, often depending on the pace of the project they are working on.

If the customer project/requirement is more strongly project managed and the exact about of cable required at each delivery location is known ahead of time, then this is a scheduled order.

In both cases, usually the customer would want the whole project to be referenced by the one sales order and one sales order to match their own entire purchase order.

To make this work in GP I did the following:

Call off orders

For call off orders, the entire stock the customer is reserving is placed on one SOP sales line, assuming the contract stipulates the entire stock to be held (allocated to them) and available at all times. If not it becomes like scheduled order as described later, the stock purchased based on the forward ship dates of the sales lines.

To hold the line from being despatched immediately, the sales line is held with a forward ship date many years in the future. This is an exact date, set by convention so it can used to identify call off order lines in reporting. The call off order is then put in a pending despatch, hold batch, out the way. The physical stock may or may not be held in a physical location too to the order, if using multiple bins for GP, this becomes easy.

When the customer calls to get some more stock, a new line is added to the order for the quantity they require, with a forward ship date of when they now want it. Often this is for immediate despatch. That quantity is subtracted off the master line that is holding the stock (the first line we put on the order), thus the stock allocation moves from the first stock hold line to the new (to be despatched now) line.

A SQL job picks up that the order now has something to be despatched and the order is pushed into our custom despatch process pipeline. The order is picked and despatched. The outcome is an order with that new line fulfilled in the pending sales invoice batch. We will pick up on the invoicing again later in this post.

Showing a calendar

Scheduled orders

Scheduled orders are placed with one line per delivery address and shipping date. For our example there would be twelve lines on the sales order, each with a forward date of a different week and each with the appropriate site address as the ship to address. As with the call off orders, the order is placed into the pending despatch hold batch. The SQL job that monitors this batch sees when a forward despatch date is reached for a particular order line and then releases that line to the warehouse for picking and despatch.

The pick list we use (SQL server reporting services) will only print picks for the lines that at or past forward ship dates and not yet invoiced, the result being those picked lines are fulfilled and placed into a batch pending sales invoicing.

Partial sales order line Invoicing

I have a number of processes running against orders before they are invoiced, one of them is designed to help with partial invoicing like that caused by drop-ship orders, scheduled orders and call off orders. If the sales orders from our example were to be transferred to invoiced immediately, items not yet fulfilled would be invoiced. This is because the “quantity to invoice” on the sales order would be equal to whatever is allocated to the order and not what has actually been fulfilled. The SQL script below skims off any quantities to invoice above the fulfilled quantities, essentially setting the Quantity To Invoice = Quantity Fulfilled. This is only true for inventory items, service items do not have the concept of being fulfilled in the same sense.

What about drop-ship orders?

Drop shipping of orders from our suppliers to customer also occurs and for those orders the GP shipping window is used to flag the purchase orders as shipped from the supplier. This makes the sales order available for invoicing before the purchase invoice from the supplier has reached us. There is a flaw in GP that if a partially shipped line were to be transferred to invoice, the whole order quantity ends up going to invoice, even though only some of the sales order line has been shipped by the supplier to the customer. The same SQL script used for skimming the quantities on the scheduled orders off, also looks into the shipping notification window table and ensures that the “quantity to invoice” of the sales line is set to the quantity shipped of that table. Thus only the correct invoice quantities are transferred.

Logging what happens is a good idea

For logging purposes the SQL script makes a record of any “adjustments” it makes to quantities for later diagnosis if there should be any issues (never had to use these records yet). When originally designed, this table was also going to be used to restore the quantities back onto the sales order that it had removed before transfer to invoice. Happily it turns out that GP recalculates the quantity to invoice field value of the sales order, after transfer to invoice, thus restoring the order to its nominal state, so no further intervention is required.

Closing the cycle

The final step to close the process is that the orders are returned to the pending despatch batch by a post transfer SQL script. That is unless no further order lines have remaining un-invoiced quantities. In that case the order will, as with standard GP behaviour end up in history.

-- ==========================================================================================
-- Author: Tim Wappat
-- Create date: 2012/08/11
-- Description:
-- Procedure ran by the invoicing application
-- before transferring sales orders to Invoices
-- Ensures that only the qty fulfilled gets invoiced.
-- (or qty shipped for drop ship orders)
-- ToInvoice value = Qty Fulfilled so only despatched items are invoiced
--
-- Update the QtyToINV of SOP10200 (sales lines) = QtyFULFI (Qty Fulfilled)
-- squirt the original values into a table as a log record of activity
-- Logs_SOP_OrderToInvQtyAdjustments
--
-- Updates:
-- 2016/09/29 Added awareness of shipping notification table and drop ships
-- ==========================================================================================
ALTER PROCEDURE [canford].[Jobs_SopSalesOrderQtyToInvoiceAdjustment] (
@SOPTYPE SMALLINT = 0
,@SOPNUMBE AS CHAR(21) = NULL
,@SOPBACHNUMB AS CHAR(15) = NULL
)
AS
BEGIN
DECLARE @AdjustmentID AS INT;

-- Get the next adjustment ID
-- Note: this is not locked, so don't run this proc twice at same time
SELECT @AdjustmentID = ISNULL(MAX(AdjustmentID) + 1, 1)
FROM Logs_SOP_OrderToInvQtyAdjustments;

IF (
@SOPBACHNUMB IS NULL
OR LEN(RTRIM(@SOPBACHNUMB)) = 0
)
BEGIN
-- Process by Document
BEGIN TRANSACTION;

-- Update the sales lines NON-DROPSHIP
UPDATE SalesLines
SET QTYTOINV = QTYFULFI
OUTPUT DELETED.SOPTYPE
,DELETED.SOPNUMBE
,DELETED.LNITMSEQ
,DELETED.CMPNTSEQ
,DELETED.QUANTITY
,DELETED.QTYFULFI
,DELETED.QTYTOINV
,DELETED.QTYTOINV - INSERTED.QTYTOINV
,getdate()
,@AdjustmentID
,0
INTO Logs_SOP_OrderToInvQtyAdjustments
FROM SOP10200 AS SalesLines
INNER JOIN IV00101 AS InventoryItem ON SalesLines.ITEMNMBR = InventoryItem.ITEMNMBR
WHERE SOPTYPE = @SOPTYPE
AND SOPNUMBE = @SOPNUMBE
AND SalesLines.QTYTOINV > 0
AND SalesLines.DropShip = 0
AND SalesLines.NONINVEN = 0
AND (
InventoryItem.ITEMTYPE = 1
OR InventoryItem.ITEMTYPE = 2
)
AND SalesLines.QTYTOINV != SalesLines.QTYFULFI;

-- Update the sales lines for DROPSHIP lines
-- Qty to go to invoice on drop ship lines should be the qty shipped from ship notification table
UPDATE SalesLines
SET QTYTOINV = ShipmentNotification.QTYSHPPD - SalesLines.QTYPRINV
OUTPUT DELETED.SOPTYPE
,DELETED.SOPNUMBE
,DELETED.LNITMSEQ
,DELETED.CMPNTSEQ
,DELETED.QUANTITY
,DELETED.QTYFULFI
,DELETED.QTYTOINV
,DELETED.QTYTOINV - INSERTED.QTYTOINV
,getdate()
,@AdjustmentID
,0
INTO Logs_SOP_OrderToInvQtyAdjustments
FROM SOP10200 AS SalesLines
INNER JOIN IV00101 AS InventoryItem ON SalesLines.ITEMNMBR = InventoryItem.ITEMNMBR
JOIN SOP60100 Link ON Link.SOPTYPE = SalesLines.SOPTYPE
AND Link.SOPNUMBE = SalesLines.SOPNUMBE
AND Link.LNITMSEQ = SalesLines.LNITMSEQ
AND Link.CMPNTSEQ = SalesLines.CMPNTSEQ
JOIN taShipmentNotification ShipmentNotification ON Link.PONUMBER COLLATE DATABASE_DEFAULT = ShipmentNotification.PONUMBER COLLATE DATABASE_DEFAULT
AND Link.ORD = ShipmentNotification.ORD
WHERE SalesLines.SOPTYPE = @SOPTYPE
AND SalesLines.SOPNUMBE = @SOPNUMBE
AND SalesLines.QTYTOINV > 0
AND SalesLines.DropShip = 1
AND SalesLines.NONINVEN = 0
AND (
InventoryItem.ITEMTYPE = 1
OR InventoryItem.ITEMTYPE = 2
)
AND SalesLines.QTYTOINV != ShipmentNotification.QTYSHPPD - SalesLines.QTYPRINV
AND SalesLines.DROPSHIP = 1;

COMMIT TRANSACTION;

RETURN 0
END
ELSE
BEGIN
BEGIN TRANSACTION;

-- Update the sales lines by batch for NON-DROP SHIP
UPDATE SalesLines
SET QTYTOINV = QTYFULFI
OUTPUT DELETED.SOPTYPE
,DELETED.SOPNUMBE
,DELETED.LNITMSEQ
,DELETED.CMPNTSEQ
,DELETED.QUANTITY
,DELETED.QTYFULFI
,DELETED.QTYTOINV
,DELETED.QTYTOINV - INSERTED.QTYTOINV
,getdate()
,@AdjustmentID
,0
INTO Logs_SOP_OrderToInvQtyAdjustments
FROM SOP10200 AS SalesLines
INNER JOIN IV00101 AS InventoryItem ON SalesLines.ITEMNMBR = InventoryItem.ITEMNMBR
INNER JOIN SOP10100 AS SalesHeader ON SalesHeader.SOPTYPE = SalesLines.SOPTYPE
AND SalesHeader.SOPNUMBE = SalesLines.SOPNUMBE
WHERE SalesHeader.BACHNUMB = @SOPBACHNUMB
AND SalesLines.QTYTOINV > 0
AND SalesLines.DropShip = 0
AND SalesLines.NONINVEN = 0
AND (
InventoryItem.ITEMTYPE = 1
OR InventoryItem.ITEMTYPE = 2
)
AND SalesLines.QTYTOINV != SalesLines.QTYFULFI
AND SalesLines.SOPTYPE = 2
AND SalesLines.DROPSHIP = 0;

-- For DROP-SHIP items
UPDATE SalesLines
SET QTYTOINV = ShipmentNotification.QTYSHPPD - SalesLines.QTYPRINV
OUTPUT DELETED.SOPTYPE
,DELETED.SOPNUMBE
,DELETED.LNITMSEQ
,DELETED.CMPNTSEQ
,DELETED.QUANTITY
,DELETED.QTYFULFI
,DELETED.QTYTOINV
,DELETED.QTYTOINV - INSERTED.QTYTOINV
,getdate()
,@AdjustmentID
,0
INTO Logs_SOP_OrderToInvQtyAdjustments
FROM SOP10200 AS SalesLines
INNER JOIN IV00101 AS InventoryItem ON SalesLines.ITEMNMBR = InventoryItem.ITEMNMBR
INNER JOIN SOP10100 AS SalesHeader ON SalesHeader.SOPTYPE = SalesLines.SOPTYPE
AND SalesHeader.SOPNUMBE = SalesLines.SOPNUMBE
JOIN SOP60100 Link ON Link.SOPTYPE = SalesLines.SOPTYPE
AND Link.SOPNUMBE = SalesLines.SOPNUMBE
AND Link.LNITMSEQ = SalesLines.LNITMSEQ
AND Link.CMPNTSEQ = SalesLines.CMPNTSEQ
JOIN taShipmentNotification ShipmentNotification ON Link.PONUMBER COLLATE DATABASE_DEFAULT = ShipmentNotification.PONUMBER COLLATE DATABASE_DEFAULT
AND Link.ORD = ShipmentNotification.ORD
WHERE SalesHeader.BACHNUMB = @SOPBACHNUMB
AND SalesLines.QTYTOINV > 0
AND SalesLines.DropShip = 1
AND SalesLines.NONINVEN = 0
AND (
InventoryItem.ITEMTYPE = 1
OR InventoryItem.ITEMTYPE = 2
)
AND SalesLines.QTYTOINV != ShipmentNotification.QTYSHPPD - SalesLines.QTYPRINV
AND SalesLines.SOPTYPE = 2;

COMMIT TRANSACTION;

RETURN 0
END
END

The scripts work with our particular configuration of GP but I can not emphasize enough that full testing and validation of the scripts for your own application and circumstances is essential.

UK visitor to USA for conference, GPUG summit

If you are visiting the USA for a conference there are a few things to remember:

Passport

UK Passport

Obvious? – well amazing how many people realise at the last moment that the passport is expired. Check it early and get it renewed if its nearly at end of life.

Visa

esta logo

Once you have a modern electronic passport with chip, to visit the USA as a UK citizen, you must apply for an “ESTA”, to give you access to the USA. Go to the website fill in your details, it will cost you $14, at time of writing. This must be done at least 72 hours before you travel, but I would recommend doing so long before you need it, just in case there is a problem. If you are denied you will have to seek a full visa, a much more involved process. You can stay for ninety days under the ESTA and its valid for two years, handy for next year’s conference too!

Money & Currency Cards

VISA Mastercard logos

The US use different tokens to buy things with, called dollars, they are green and all look identical to each other, so when you get some beware handing over valued notes and getting incorrect change. It is essential to get some cash, preferably a good number of one dollar bills. The US service sector factors in tipping to the employee wages so tipping is an important part of the culture, people handling your bags, waiters, room service, cleaning maids, anyone who provides you a service should be offered a tip. You will find this eats up lots of one dollar bills over the duration of the trip. UK people tend to find this very difficult to get used to, don’t resent it you are not at home, the economy works differently.

If you are not on company expenses for the trip, then get a US currency card. currency cards allow you to get and lock the exchange rate at which cash is placed on the card. Cards, such as the FairFx card can be loaded with cash, usually any amount and topped up via a phone app or website with more while you are out there. The cards are “normal” VISA or Mastercard cards and work in the same way at point of sale. The cash sits on the card as US dollars, conversion usually at time of putting the credit on the card, unlike credit cards that take the exchange rate at time of purchase (that is less unpredictable). These cards are US dollar cards so do not experience a fixed transaction fee each time that you use them, unlike most UK sterling debit or credit cards would. Do this with two weeks to spare as it can take time for the card to arrive through the post. Keep an eye on exchange rates and hedge when you see a good rate by putting some cash on the card. I find this useful for micro transactions, such as automated vending, such as hotel clothes washers, or vending machines or some parking that only can take card payment. For these small payments the flat transaction fee on your standard bank card would make its use unattractive.

Documents

printer icon

It is wise to remember to bring your accommodation and other booking information to conference, what if there turns out to be two similar named hotels, paperwork helps when the taxi driver challenges you. Immigration officers can vary a lot in the scary factor. Like in most work places, some can be very officious and awkward, others can be lovely. I can not recommend highly enough that you have all your hotel booking information, conference booking information and especially hotel addresses and return travel documentation with you and easily at hand when entering the USA. Be very careful if you are being paid to present at the conference, this can lead you down a difficult path with immigration. It can be easier to ask for conference discounts or free accommodation in lieu of payment, to avoid this surfacing. Don’t lie about anything, as in worst cases, if they inspect your laptop and find the power points and arrangements on emails, your are on a slippery slope if you were glossing over details.

When you land in the US you will have to go through immigration before connecting to internal flights. Airports like Miami and Orlando MCO have the electronic fingerprinting and face recognition systems, for entry to the USA you have to click on one of two buttons, business or pleasure, you can imagine for some conferences this can be a grey area, there are plenty of forums with people debating the correct response, go check them out. If your company are paying for the flight, then its business IMO. This can be one of the questions asked. Having the correct paper work backing up any answers will almost always allow you to be waved though, but you will be so glad you had it all at hand if you are being singled out for extra attention.

Remember don’t bring any non-processed food into the US, like fruit or meats.

The above sounds worrying, but 99.9% of the time, getting in should not be a problem!

Power

USA UK adapter with four way and multi-USB supply

The USA have a different mains power system to the UK, you need an adapter to plug UK plugs into the mains and you MUST check on the label for the equipment you are plugging in, that it will work on 110VAC. Most plug top power supplies these days work on both European and US voltages and so laptop and phone chargers are usually fine. I always travel with a four way mains extension cable bought from a discount store, it is cheap so extremely light and has a foot of cable on it. For hotels I find this essential. Sometimes hotels have too few sockets, or mains sockets that are so loose that plugging an US-UK adapter then your plug top power supply in to the adapter is heavy enough to just fall out the socket, refusing to stay put. The four way extension instead allows a number of devices to be plugged in, placed on the table top thus taking the strain off the socket. Only the four way plug is in the wall.

Using a slim dedicated UK-USA adapter is much better than the bulky universal adapters for the same reason, the larger ones tend to just fall out the USA sockets. I would also recommend getting a multiple output USB power supply as shown in the photo, check it has high current output as cheap ones will not charge all the devices if plugged in at one. This allows for some chargers to be left at home and releases more mains sockets for laptop charging.

A portable lithium batter USB charger or two can also be helpful as if you are a twitter addict or just busy on the conference apps, the mobile phone can struggle to make it to the end of the after conference party, having a portable charger just gives that confidence that every last tweet can be read, or at least the number for a taxi home can be found after the party!

Mobile phone and data

Check your mobile phone or computer dongle contract for what charges you will incur in the USA, these are usually eye watering. I would recommend getting a Three Pay as you Go SIM, activate it and top it up the week before you go and get your phone unlocked so it can accept the SIM (if not already on the Three network). The data rates are cheap in the us and texts and calls are received at UK rates rather than at roaming rates. Free WIFI in the USA is easy to find, but where it matters in the conference, things can be different with many thousands of users trying to access one WIFI network. Mobile data gives another route to the internet, essential if twitter or checking facts during sessions is important to you.

Sun protection

We in the UK forget how strong the sun can be, Florida can be viscous and inflict real harm to the skins of UK visitors, bring sun protection, hats, sprays, cover ups. Check for the time of year what normal UV and heat is like. Florida can be very hot and humid, you were warned.

Accommodation

Booking as early as possible for your hotel, lets you get the fun of being closer to the action (after conference socials at hotel bars etc) and also keeps the taxi bills for transfer to the conference venue lower.

Eating

If you are not on company expenses then going to conference very expensive on the personal pocket, so look out for events that will give you a free feeding on evenings. No doubt this will be companies trying to court you, but will keep the family budget from being hit too hard.