Quick guide to data flow when working with Dynamics GP and multiple bins.
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” etc. 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.
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.
Other transaction types can hold bin allocations, such as inventory and manufacturing orders.
IV10004 holds the bin details for inventory transactions.