How is the ‘Net Available’ field – added to the Sales Transaction Entry window – calculated?

Article ID: KB0084

One of the twelve (12) features available in EthoTech's Customer Service Collection product is the ability to display additional Inventory fields for each line item on the Sales Transaction Entry window. One of these fields is 'Net Available'.

The logic for calculating the 'Net Available' is a combination of two (2) SQL views created by EthoTech along with data from Dynamics GP tables.

SQL View 1 - ETI_IVQtyUnallocLoc

This SQL View sums the unallocated quantities (calculated field) in base unit of measure from table SOP_LINE_WORK (SOP10200) by item number and location code (site id):

SELECT TOP 100 PERCENT
ITEMNMBR,
LOCNCODE,
SUM((QUANTITY - ATYALLOC - QTYONPO - QTYRECVD - QTYTBAOR - QTYPRBAC - QTYPRINV - QTYCANCE)*QTYBSUOM) AS QTYUNALLOC
FROM SOP10200
WHERE (SOPTYPE = 2 OR SOPTYPE = 3 OR SOPTYPE = 6) AND (DROPSHIP = 0)
GROUP BY ITEMNMBR, LOCNCODE
ORDER BY ITEMNMBR, LOCNCODE 

SQL View 2 - ETI_IVQtyUnallocated

This SQL View displays unallocated quantity (from view ETI_IVQtyUnallocLoc) for each record in table IV_Item_MSTR_QTYS:

SELECT TOP 100 PERCENT
a.ITEMNMBR,
a.LOCNCODE,
a.RCRDTYPE,
QTYUNALLOC =
CASE a.RCRDTYPE
WHEN 1 THEN (SELECT ISNULL(SUM(QTYUNALLOC), 0) FROM ETI_IVQtyUnallocLoc WHERE ITEMNMBR = a.ITEMNMBR)
ELSE (SELECT ISNULL(SUM(QTYUNALLOC), 0) FROM ETI_IVQtyUnallocLoc WHERE ITEMNMBR = a.ITEMNMBR AND LOCNCODE = a.LOCNCODE)
END
FROM IV00102 a
ORDER BY a.ITEMNMBR, a.LOCNCODE, a.RCRDTYPE 

Dynamics GP

Table IV_Item_MSTR_QTYS (IV00102) stores quantity information for each item – location code combination (record type = 2), plus an additional record per item that stores summary quantity information for all location code (location code = ‘’, record type = 1).

Sales Transaction Entry window

Qty on Hand (ASI_QTY_On_Hand) is set to ‘QTY On Hand' of table IV_Item_MSTR_QTYS for the item – location code combination.

ASI_QTY_Available (by default, not displayed) is set to ‘QTY On Hand' of table IV_Item_MSTR_QTYS - 'QTY Allocated' of table IV_Item_MSTR_QTYS for the item – location code combination.

'ASI QTY Unallocated' (by default, not displayed) is set to QTYUNALLOC from view ETI_IVQtyUnallocated for the item – location code combination.

Net Available ('ASI Net Qty Available') is set to ASI_QTY_Available - 'ASI QTY Unallocated'