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

What's Your Setup?

To the skaters out there you’ll recognise the familiar phrase “What’s Your Setup?”.

Your "Setup" is all of the pieces and parts that go into your skateboard. It's pretty rare to find two skaters with the same setup, and hearing about what other skaters use, and why, is a great way to learn about new options, and pick fights. [ref]

For developers using Visual Studio it is a similar story. I am a generalist developer, very lucky to have work types split over developing and supporting; embedded microcontroller programming, ERP Add-in development, Windows Forms, WPF, ASP.NET web applications and more. I also work 50-50, VB.NET-C#.

Visual Studio has embraced a pluggable architecture (MEF), allowing the base product to be changed with plug-in extensions. I thought it might be interesting to look at how I have set my Visual Studio up. I work over many machines between work and home and over virtual machines. My setup is consistent, helped by the user awareness that Visual Studio has these days. Some of these extensions I’ve picked up from co-workers, or going to developer talks, others from stumbling on them.

Colour Theme

Visual Studio with dark look

[More]

Currently running the “dark” colour theme, I resisted for a couple of years, convinced that I preferred the original light version but I’m a total convert now. Some of the windows clearly have not yet been updated to properly flow with the theme but I live with that. Dark theme makes the syntax highlighting punch out much more (for me) and is less tiring on the eyes (I think).

At work I use Team Foundation Server for source control and Application Lifecycle Management (ALM) , and Git extensions for source control on home projects.

Auto Shelve

TFS Auto Shelve for Visual Studio 2015

This every x mins shelves my current workspace into a shelf set, the solves the auditors worry over hardware failing and thus us loosing too many days or hours work since the last check in. It is amazing how often a quick update or change leads to a project being open and worked on for a couple of weeks, you never know when that SSD might go pop…

It also makes it easy for others in the team to pick my work in progress up, say after a huddle, with no effort from me.

Spell Checker

Visual Studio Spell Checker

I can’t spell, look at the comments and variable names in my circa 2003-7 code and you see why I need this feature. The spell checker is awesome in this add in, after remembering to switch it to EN-GB from EN-US. Just look at all the lovely settings, it is very code aware.

Spell Checker options windowsSpell Checker options windows

RegEx Tester

Regex Tester

RegEx comes into development a lot, I personally love creating regular expressions, this tool lets them be tested using the same regex engine as the code, in a dockable window right inside the IDE!

Regex Tester window

Productivity Power Tools

Productivity Power Tools 2015

This extension has biggest impact on my experience. Yes, when you first start using it, it adds lots of extra visual noise into the development environment, but once you get used to it, your productivity will increase and you’ll miss it if its not installed. I work with the tabs on the left, they are colour coded by project and sorted by project. The names on the tabs are much easier to see for the files and it just makes sense with a wide screen monitor and lots of files open to have them there.

Productivity Power Tools 2015 optionsProductivity Power Tools 2015 options

Productivity Power Tools 2015 optionsProductivity Power Tools 2015 options

Coloured tabs and left hand tabs well

Application Insights

Application Insights Tools for Visual Studio

Important for quickly diagnose any problems in your live application.

The following are other standard extensions that are important but next tier down in my frequency of use, i.e. I can work without them most of the time.

Power shell tools for Visual Studio 2015Web essentials 2015Visual  Studio installer projectsimageTypescript for Microsoft Visual Studio

For hardware projects

For work with Raspberry Pi, Arduino hardware and other maker activities I have these two

Arduino IDE

Windows IoT core TemplatesGIT

Templates

I also have templates installed for developing Microsoft Dynamics Plugins, see: Visual Studio 2015 Dynamics GP Addin Project Templates Install

Dynamics GP Project Templates for VS2015

General use

I’m loving the visual studio experience, being able to hover and see the out of view start of code blocks,

Code lenses

The new code lens type features and light bulb suggestions are great, although i have a gripe that it is far too slow compared to the old rename variable.

The built in diagnostics are really handy for looking at object disposal and performance tracing.

image

Extensions give so much more, as does delving into settings to see what is there.

I will add to this post as I think of other things I do with Visual Studio…

Generating *SEQ (LNITEMSEQ etc) column values in Dynamics GP

How does LNITMSEQ work?


There are sequence columns used to introduce order into rows within Dynamics GP. Examples are such as LNITMSEQ that identifies the line numbers of sales orders in SOP10200 and SOP30300. When new rows are appended to these tables, the sequence column value becomes 16384 * the line number. Basically 16384 is added to the last existing value for the sequence in the table. By having gaps in the numbering, this allows records to be inserted in between the existing records without renumbering the entire sequence, with the performance impact that would have had back in the era when GP was born. This is a similar technique that BASIC programmers used to use, to allow code to be inserted, lines would be incremented in tens.

Line number SEQ
1 16384
2 32768
3 49152

Note that although there is a mathematical relationship between the row number  and the sequence number to begin with, if new lines are inserted and/or deleted, that relationship is immediately destroyed. Finding line numbers is better done using SQL windowing functions like ROW_NUMBER() than trying to derive it from the SEQ.

Below is a demonstration, where a line has been inserted after the first line, the sequence value is half way between the adjacent values (24576).

Line number SEQ
1 16384
2 <inserted> 24576
3 32768

 

Applying that learning

If there is a requirement to “bulk” insert rows after the last record and the table has a sequence column, the next whole multiple of 16384 after the last sequence number is required.

To find the next number in the sequence I wrote the following SQL:

@tmpSEQNUMBER = CEILING(CAST(MAX(SEQNUMBR) AS DECIMAL(19, 5)) / 16384) + 1 
- CEILING((CAST(MAX(SEQNUMBR) AS DECIMAL(19, 5)) / 16384) % 1)
 
As a side note, for .NET try the following where MaxLineSequenceNumber is the max seq existing in the table:
C#
int NextLineSequenceNumber = 
Convert.ToInt32((Math.Ceiling(MaxLineSequenceNumber / 16384) + 1)
- Math.Ceiling(MaxLineSequenceNumber / 16384) % 1) * 16384;
 
VB.NET
Dim NextLineSequenceNumber As Integer =
CInt((Math.Ceiling(MaxLineSequenceNumber / 16384) + 1)
- Math.Ceiling(MaxLineSequenceNumber / 16384) Mod 1)
* 16384

This is calculating the next row number where the sequence left off.
This row number can then be used together with the ROW_NUMBER() SQL window function to create a calculated column that is then used to insert the values into the sequence column. Here is the SQL I wrote to do that bit, see below for these snippets in context of the full SQL script.
 
(ROW_NUMBER() OVER (ORDER BY SOPNUMBE) -- actual row number
+ @tmpSEQNUMBER -- row number offset
) * 16384 -- GP multiplier
 

Real world example

The following script demonstrates a practical application of these snippets where the need is to apply a BIN number allocations of stock against sales orders from a custom table generated by the picking list. The new stock is allocated from the BINS to the sales order using SOP10203 and the allocations are added as rows to the end of the existing sequence of allocations. This table has a SEQNUMBER field that is used to maintain the order of the records in the table and also takes part in the compound key for the record.
In this example parameters; @SOPNUMBE, @SOPTYPE, @LNITMSEQ, @CMPNTSEQ define the sales order line.
 
DECLARE @tmpSEQNUMBER INT

SELECT @tmpSEQNUMBER = CEILING(CAST(MAX(SEQNUMBR) AS DECIMAL(19, 5)) / 16384) + 1
- CEILING((CAST(MAX(SEQNUMBR) AS DECIMAL(19, 5)) / 16384) % 1)
FROM SOP10203
WHERE SOPNUMBE = @SOPNUMBE
AND SOPTYPE = @SOPTYPE
AND LNITMSEQ = @LNITMSEQ
AND CMPNTSEQ = @CMPNTSEQ
-- if no pre existing rows @tempSEQNUMBER will be null, so seed with zero
SELECT @tmpSEQNUMBER = ISNULL(@tmpSEQNUMBER, 0)

INSERT INTO [SOP10203] (
[SOPNUMBE]
,[SOPTYPE]
,[LNITMSEQ]
,[CMPNTSEQ]
,[SEQNUMBR]
,[ITEMNMBR]
,[LOCNCODE]
,[BIN]
,[QTYTYPE]
,[QUANTITY]
,[POSTED]
)
SELECT SOPNUMBE
,SOPTYPE
,LNITMSEQ
,CMPNTSEQ
,(
ROW_NUMBER() OVER (
ORDER BY SOPNUMBE --fake order
) + @tmpSEQNUMBER
) * 16384
,--generate seq
ITEMNMBR
,LOCNCODE
,BIN
,QTYTYPE
,QUANTITY
,0
FROM [CA_SOP_PicklistDetailBins] --our bin allocation to apply
WHERE SOPNUMBE = @SOPNUMBE
AND SOPTYPE = @SOPTYPE
AND LNITMSEQ = @LNITMSEQ
AND CMPNTSEQ = @CMPNTSEQ
AND DT_Pick_No = @PICKNUMBER
I hope this post may help someone with some ideas on how to tackle variants on this problem.