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