Developing against Dynamics GP Multiple Bins, tables used

Quick guide to data flow when working with Dynamics GP and multiple bins.

Inventory levels

As is normally the case, stock levels are maintained by IV00102, in fields QTYONHND (quantity on hand) and ATYALLOC (quantity allocated).

When multiple bins is enabled (see post: TSQL determine inventory MultiBinning is enabled in Dynamics GP), then a layer is added on top of the standard stock levels with an additional table IV00112. This table holds the bin detail break down of the stock levels held in the IV00102 table. This bin detail table is keyed by item, location and quantity type where quantity type is “on hand”, “in service”, “in Use”, “returned”, “Damaged”. It adds a break down of the quantities of stock held at each Bin in the field IV00112.QUANTITY.

Bin Allocations vs. Site Allocations

It is important to understand that multiple binning also introduces another tier of stock allocations, at a bin level. Transactions such as SOP documents can have stock allocated to them at a site level (shown in the quantity allocated in item enquiry windows by site) but now with multiple binning enabled, they must also be allocated at the bin level of detail. The quantity of stock in bin detail that is allocated to sales, manufacturing or inventory transactions is held in the field IV00112.ATYALLOC. The value in ATYALLOC should be the total of all the different allocations over all the different types of transaction that can demand stock for that item.

When the transaction document is posted (or invoice posted in case of sales documents), the value for both ATYALLOC and QUANITTY of table IV00112 will be decreased by the quantity posted. There is an example later on in this document.

To put this another way, the total of the QUANTITY field in IV00112 for a item and location over all the bins should equal the QTYONHND in IV00102 for that item location (site).


Stock Level and Bin Stock Level Tables

Note That the quantity changes in IV00112.ATYALLOC in real time as quantities are selected in transactions documents, even before the lines are “saved” with the save button.

Default bins for transaction types

Default bins (some of which are highlighted by coloured box in screen shot) can be specified to be used for fulfilment , returns, receipts etc. These default bins are held against each item site combination by the IV00102. If defaults are specified, the application will not prompt the user for bin choices in these types of transactions, for that site and item. Instead the default bin will be automatically selected however should insufficient stock be available from that bin, the window will open prompting for the user to select where the stock should come from.

Default bins and priorites by item site

When this prompt window opens, the order of the bins shown in the lookup can be forced, by specifying a priority order, by item, in the table IV00117.  In native GP, this priority is merely used for the order in the lookup window. Some ISVs offer products that will allow the bins specified in the priority table to be used also until all stock is exhausted – this is how I’ve programmed our implementation too, I think this should be an option check box to make GP behave this way.

Bin Priority Table

In the next screen shot it can be seen how the priority is provided as a sort column in selecting bins. Further options are made available by clicking on the blue bar.

Bin Priority shown in Bin selection lookup

Serial Number Selection window

Table IV40700, site definition table holds default bins by site – shown by box below. This operates at the site level in the same way as the item default bins, allowing for items, for example to be placed into a put away floor grid by default when GRN is complete.

image

 

Sales document bin allocations

In the same way that stock is allocated from inventory at both site and bin levels, so too sales orders can be allocated.

Stock allocations from bins to sales order lines is recorded against a sales order line using two tables, depending upon if the item is a serial numbered item or a standard inventory item;

  • Non serial numbered/lot items Table: SOP10203
  • Serial/lot items Table: SOP10201

When viewing a sales order line in the detail window, either the serial/lot button will be enabled or the bins button depending on the type of item, this also indicates the table responsible for recording that bin allocation.

The total quantity allocated from a bin to documents is an aggregate sum of the values in SOP10203.QUANITTY and SOP10201.QUANTITY fields for all sales order lines and the equivalent fields for other transaction types that consume stock, for example inventory transactions.

Sales Order bin allocation tables

As bins are selected or serial numbers are selected for the sales order line, the field QTYSLCTD (Qty Selected) is increased in SOP10200. So the QTYSLCTD field should always represent the sum of the quantity of bin items selected or sum of the number of serial numbers selected (which in turn is associated with a bin in the serial record). Note that for serial numbers there is also EXTQTYSEL that is used to keep track of the number of serial numbers selected too. As a serial record contains both bin and serial information, the EXTQTYSEL and QTYSLCTD are in sync for serial items.

To show this in SQL speak, for non-serial items the following SQL would correct the value where SOPTYPE is not 4 (return), this should be added to the where clause. Returns do not cause an allocation just in the same way as negative adjustments do not. The statement below is an over simplification for illustrative purposes, it has not been validated for completeness do not run against production database.

;
WITH CTE_SumBinSelectedQty
AS (
SELECT SOPTYPE
,SOPNUMBE
,LNITMSEQ
,CMPNTSEQ
,SUM(QUANTITY) SumQtySelected
FROM SOP10203
WHERE SOPNUMBE = @SOPNUMBE
AND SOPTYPE = @SOPTYPE
AND LNITMSEQ = @LNITMSEQ
AND CMPNTSEQ = @CMPNTSEQ
AND POSTED = 0
GROUP BY SOPTYPE
,SOPNUMBE
,LNITMSEQ
,CMPNTSEQ
)
UPDATE SOP10200
SET QTYSLCTD = CTE_SumBinSelectedQty.SumQtySelected
FROM SOP10200
JOIN CTE_SumBinSelectedQty ON SOP10200.SOPTYPE = CTE_SumBinSelectedQty.SOPTYPE
AND SOP10200.SOPNUMBE = CTE_SumBinSelectedQty.SOPNUMBE
AND SOP10200.LNITMSEQ = CTE_SumBinSelectedQty.LNITMSEQ
AND SOP10200.CMPNTSEQ = CTE_SumBinSelectedQty.CMPNTSEQ

While for correcting the selected quantity for serial items this following would be true. The statement below is an over simplification for illustrative purposes, it has not been validated for completeness do not run against production database.

;
WITH CTE_SerialTotals
AS (
SELECT SOPTYPE
,SOPNUMBE
,LNITMSEQ
,CMPNTSEQ
,SUM(SERLTQTY) AS SerialTotal
FROM SOP10201
WHERE SOPTYPE = @SOPTYPE
AND SOPNUMBE = @SOPNUMBE
AND POSTED = 0
GROUP BY SOPTYPE
,SOPNUMBE
,LNITMSEQ
,CMPNTSEQ
)
UPDATE SOP10200
SET EXTQTYSEL = SerialTotal
,QTYSLCTD = SerialTotal
FROM SOP10200 sl
JOIN CTE_SerialTotals tot ON sl.SOPTYPE = tot.SOPTYPE
AND sl.SOPNUMBE = tot.SOPNUMBE
AND sl.LNITMSEQ = tot.LNITMSEQ
AND sl.CMPNTSEQ = tot.CMPNTSEQ

What happens at sales invoicing?

When the order is transferred to invoice, the SOPTYPE and SOPNUMBE are updated in SOP10201/SOP10203 to those of the newly created invoice, thus transferring the bin allocation records from the order to invoice. On posting that invoice, the value for both ATYALLOC and QUANITTY of table IV00112 will be decreased by the quantity posted, as the stock is removed from inventory. It is at this point that the rows corresponding to the sale are marked as posted in the order tables SOP10201/SOP10203, using  POSTED=1 in the POSTED field. Thus records with POSTED=1 should not be treated as live bin allocations.

Sales order example:

When the sales order is entered and a quantity entered for the order, then assuming stock availability, the order line will cause a site allocation of the stock. The IV00112 and SOP10201 tables will not show any ATYALLOC value for the order at this point. The Bin button or the Serial/Lot button can be pressed from within the sales order line detail window. This lets the user see what bins the stock resides in and allows the user to select which bins they want to fulfil the order from. Note that immediately the user selects the bin or serial number and quantity, the stock becomes allocated by a value in IV00112.ATYALLOC and associated quantity in SOP10203.QUANTITY or SOP10201.SERLTQTY. Note that the order line also then becomes fulfilled by the bin quantity allocated! With standard GP, it is not possible to direct pickers to stock by selecting bins within the order line as this will result in premature fulfilment of the sales order quantities for that line! To work around this in my solution we introduced another table to hold our own bin allocations for serial and non-serial items during picking, the values in this table are transferred to the order at fulfilment of the order lines allowing the pickers to be directed to bins holding the stock for the order.

As noted earlier, once the order is transferred to invoice the order allocations will be marked POSTED and the inventory values decremented by the value posted.

Inventory transactions

Other transaction types can hold bin allocations, such as inventory and manufacturing orders.
IV10003 (Inventory Transaction Bin Quantities WORK) holds the bin details for inventory transactions and IV10004 (Inventory Bin Quantity Transfer).

Note: Negative adjustments do not cause allocations for bins.

imageBin work

Useful script

The following is a script that is a work in progress that can be used to check the health of bin allocations.

-- =============================================
-- Author: Tim Wappat
-- Create date: 28th July 2016
-- Description: BIN allocations checker
-- Checks to see where bin allocations have become corrupted
-- =============================================
-- Find items with BIN allocations in inventory that do not have BIN allocations
-- in any outstanding transactions in Inventory or Sales Docs
-- This is a starting point for a more comprehensive script, if system is
-- running other modules like manufacturing, then this would need taking into account

-- Summary of bin stock levels by itemnumber, qty type and bin
;WITH CTE_InventoryBinSummary
AS (
SELECT ITEMNMBR,
LOCNCODE,
BIN,
QTYTYPE,
SUM(QUANTITY) as SumQty,
SUM(ATYALLOC) as SumAllocated
FROM IV00112
GROUP BY ITEMNMBR, LOCNCODE, QTYTYPE,BIN
),
-- Summary of BIN allocations from open Sales orders (non-Serial numbered)
CTE_SOP10203Sum AS(
SELECT
CASE WHEN QTYTYPE=4 THEN 0 ELSE SUM(QUANTITY) END as SUMQTY,
ITEMNMBR,
BIN,
LOCNCODE,
QTYTYPE
FROM SOP10203
WHERE POSTED=0
GROUP BY ITEMNMBR, BIN, LOCNCODE, QTYTYPE
),
-- Summary of BIN allocations from open Sales orders (serial numbered)
SERIAL AS
(SELECT ITEMNMBR, QTYTYPE, BIN, SUM(SERLTQTY) SumCount FROM SOP10201 WHERE POSTED=0 GROUP BY ITEMNMBR, QTYTYPE, BIN)
,
-- Summary of BIN allocations from open Inventory transactinos (adjustments)
IVTX AS
(SELECT IV10003.ITEMNMBR,LOCNCODE,QTYTYPE, CASE WHEN TRXQTY<0 THEN -1 * SUM(QUANTITY) ELSE SUM(QUANTITY) END as SumQty, BIN FROM IV10003
JOIN IV10001 ON IV10003.IVDOCNBR=IV10001.IVDOCNBR AND IV10003.IVDOCTYP=IV10001.IVDOCTYP AND IV10003.LNSEQNBR=IV10001.LNSEQNBR
gROUP BY IV10003.ITEMNMBR, LOCNCODE, QTYTYPE,TRXQTY ,BIN)
-- Use the above CTE's to check where the totals don't add up
SELECT * ,ISNULL(CTE_SOP10203Sum.SUMQTY,0) + ISNULL(SERIAL.SumCount,0) + CASE WHEN ISNULL(ivtx.SumQty,0) <0 THEN ISNULL(ivtx.SumQty,0) ELSE 0 END as calc
FROM
CTE_InventoryBinSummary
LEFT JOIN
CTE_SOP10203Sum
ON CTE_InventoryBinSummary.ITEMNMBR=CTE_SOP10203Sum.ITEMNMBR
AND CTE_InventoryBinSummary.LOCNCODE=CTE_SOP10203Sum.LOCNCODE
AND CTE_InventoryBinSummary.BIN=CTE_SOP10203Sum.BIN
AND CTE_InventoryBinSummary.QTYTYPE=CTE_SOP10203Sum.QTYTYPE
LEFT JOIN
SERIAL
ON CTE_InventoryBinSummary.ITEMNMBR=SERIAL.ITEMNMBR
AND CTE_InventoryBinSummary.BIN=SERIAL.BIN
AND CTE_InventoryBinSummary.QTYTYPE=SERIAL.QTYTYPE
LEFT JOIN
IVTX
ON CTE_InventoryBinSummary.ITEMNMBR=IVTX.ITEMNMBR
AND CTE_InventoryBinSummary.LOCNCODE=IVTX.LOCNCODE
AND CTE_InventoryBinSummary.BIN=IVTX.BIN
AND CTE_InventoryBinSummary.QTYTYPE=IVTX.QTYTYPE
WHERE CTE_InventoryBinSummary.SumAllocated != ISNULL(CTE_SOP10203Sum.SUMQTY,0)
+ ISNULL(SERIAL.SumCount,0)
+ CASE WHEN ISNULL(ivtx.SumQty,0) <0
THEN -1*ISNULL(ivtx.SumQty,0)
ELSE 0
END --inventory postive adjust does not hit stock
ORDER BY CTE_InventoryBinSummary.ITEMNMBR

Limitations using Multiple Binning and a SQL script - Dynamics GP

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 canford.CA_SOP_BinQtysByItem (
@ITEMNMBR CHAR(31)
,@LOCNCODE CHAR(11)
,@QTYTYPE SMALLINT
)
AS
SET NOCOUNT ON;

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

GRANT EXECUTE
ON canford.CA_SOP_BinQtysByItem
TO DYNGRP
GO