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.

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