Have you ever wanted to know what FIFO layer in your on-hand inventory is going to be used next, its unit cost and how much of it remains on hand?

When an item is managed by FIFO (First in First Out), layers are created in the system as stock receipts are posted in the OITM or OITW tables. Information relating to the subsequent issuances of these FIFO layers is also stored in OINM. Likewise, an A/P Invoice created for this item will create new layers in OINM. These records however, do not carry information as to the open quantities and the current costs per open layer. In order to determine the next available FIFO layer, its remaining quantity and cost, the user needs to run a report on OINM using this query.


/* Unlike items managed by Moving Average and Standard valuation methods, the item cost of items managed by FIFO are only accessible through OINM. This query will enable the user to find the next item cost for a given item numbermanaged by FIFO. */

Select Top 1 T0.HTransSeq, /* The highest transaction number represent the highest revaluation line for a given layer. Selecting the top 1 when order desc will enable to select the last revaluation of the layer */

T0.Transtype, /*Provide the information on the origin of the layer*/

T0.Base_REF, /* Provide the document number when exists */

T0.ItemCode, /* Item number */

T0.calcprice, /* Cost price */

T0.openqty, /* available qty for the layer */

T0.BaseCurr, /* currency of the transaction */

T0.TransSeq, /* Layer number */

T0.HTransSeq /* Highest revaluation of a given layer */



Inner Join

OITM T1 on

T0.Itemcode = T1.Itemcode

Where T1.Evalsystem =’F’ and /* check only the item manage by FIFO */

T0.openqty > 0 and /* exclude the layers where the open quantity is null */

T0.itemCode = ‘FIFO3’ and /* Select Specific item number */

T0.Warehouse = ‘[%0]’

Order by

T0.HTransSeq desc /*Order desc enable to select the last layer revaluated */