OracleFree referencePDF, Excel, Word exportJD Edwards EnterpriseOneAll JD Edwards EnterpriseOne tools

JD Edwards Inventory Tables: F41021 Item Location, F4111 Item Ledger, F4105 Item Cost

A working map of the JD Edwards EnterpriseOne 9.2 inventory tables: item master, item branch, item location balances, the F4111 cardex, cost, lot, location and UOM tables. Each F41021 quantity column has its own row so you can see what moves it before you build an availability report.

Download:

Table	Table name / column	Key columns	Important columns or meaning	Maintained by	Joins to
F4101	Item Master	IMITM	IMLITM, IMAITM, IMDSC1, IMSTKT stocking type, IMLNTY line type, IMUOM1 primary UOM, IMGLPT G/L class, IMLOTS lot status	Item Master (P4101)	F4102, F41021, F4111 on ITM
F4102	Item Branch File	IBITM, IBMCU	IBSTKT, IBLNTY, IBGLPT, IBROPI reorder point, IBROQI reorder quantity, IBSAFE safety stock, IBBUYR buyer, IBVEND supplier	Item Branch/Plant (P41026)	F4101 on ITM; F41021 on ITM, MCU
F41021	Item Location File	LIITM, LIMCU, LILOCN, LILOTN	LIPBIN primary location P/S, LILOTS lot status, LIGLPT, LILRCJ last receipt date, quantity buckets below	Updated by inventory, sales, purchasing and manufacturing transactions; reviewed in Item Availability (P41202)	F4102 on ITM, MCU; F4108 on ITM, MCU, LOTN; F4100 on MCU, LOCN
F41021	LIPQOH		Quantity on Hand - Primary units. Increased by receipts and completions, reduced by issues and shipments when inventory is relieved.	P4112, P4113, P4114, P4312, P4205 or R42800
F41021	LIHCOM		Quantity - Hard Committed (sales lines committed to this location and lot).	Sales order commitment, pick slip and ship confirm
F41021	LIPCOM		Quantity Soft Committed (sales lines committed at item and branch level, no location yet). Usually carried on the primary location record.	Sales order entry
F41021	LIFCOM		Quantity on Future Commit (orders with a date beyond the commitment window).	Sales order entry; Inventory Commitment (R42997) moves them into soft or hard commit
F41021	LIFUN1		Quantity - Work Order Soft Commit (parts lists attached to work orders).	Work order parts list attachment
F41021	LIQOWO		Quantity - Work Order Hard Commit.	Work order hard commitment of components
F41021	LIPREQ		Quantity on Purchase Order - primary units (open PO quantity due in).	Purchase order entry and receipts
F41021	LIQWBO		Quantity on Work Order Receipt (open work order quantity expected).	Work order entry and completions
F41021	LIPBCK		Quantity on Backorder.	Sales order backorders
F41021	LIQTTR		Units - In Transit - Primary units.	Receipt routing and transfer processing
F41021	LIQTIN		Units - In Inspection - Primary units.	Receipt routing steps
F41021	LIQTO1 / LIQTO2		Units - In Operation 1 and 2 - Primary units (user defined receipt routing buckets).	Receipt routing steps
F41021	LIQONL		Quantity - On Loan to Manufacturing.	Manufacturing loan transactions
F41021	LIQTRI / LIQTRO		Quantity Inbound and Outbound - Warehouse (warehouse management putaway and pick requests).	Warehouse Management
F41021	LIOT1P / LIOT2P / LIOT1A		Quantity 1 and 2 - Other primary units; Quantity - Other Purchasing 1. Buckets that branch constants can add or subtract.	Configurable buckets; check your branch availability definition
F41021	Availability (P41202)		Available = on hand minus the commitments and adds you choose in the Item Availability Definition of Branch/Plant Constants (P41001), e.g. less soft, hard and future commits, plus PO receipts if selected.	Defined per branch/plant in P41001
F4111	Item Ledger File (cardex)	ILUKID	ILITM, ILMCU, ILLOCN, ILLOTN, ILDOC, ILDCT, ILKCO, ILDOCO/ILDCTO/ILKCOO order, ILTRDJ, ILDGL, ILTRQT quantity, ILUNCS, ILPAID extended cost, ILTREX, ILGLPT, ILICU batch	Written by every inventory transaction; viewed in Item Ledger Inquiry (CARDEX) (P4111)	F0911 on ILDOC, ILDCT, ILKCO, ILDGL, ILJELN; F4211 or F4311 on order keys
F4111	ILTRQT sign		Positive for quantity into the location (receipts, completions, transfers in), negative for quantity out (issues, shipments, transfers out).
F4105	Item Cost File	COITM, COMCU, COLOCN, COLOTN, COLEDG	COLEDG cost method, COUNCS unit cost (4 decimals), COCSIN costing selection - inventory, COCSPO costing selection - purchasing	Cost Revisions (P4105)	F4102 on ITM, MCU
F4105	COLEDG cost method codes (UDC 40/CM)		01 Last In, 02 Weighted Average, 07 Standard. Codes 01 to 19 are reserved by JD Edwards.	UDC 40/CM
F4108	Lot Master	IOLOTN, IOITM, IOMCU	IOLOTS lot status, IOMMEJ expiration date, IOVEND supplier, IORLOT supplier lot, IOLOTP potency, IOLOTG grade, IOBBDJ best before, IOSBDJ sell by	Lot Master (P4108)	F41021 on ITM, MCU, LOTN
F4100	Location Master	LMMCU, LMLOCN	LMAISL aisle, LMBIN bin, location level and warehouse attributes used by putaway and picking	Location Master (P4100)	F41021 on MCU, LOCN
F41001	Inventory Constants	CIMCU	Location format, symbols for item numbers, item availability definition, G/L interface flags per branch/plant	Branch/Plant Constants (P41001)	F4102 and F41021 on MCU
F41002	Item Units of Measure Conversion Factors	UMMCU, UMITM, UMUM, UMRUM	UMCONV conversion factor (7 decimals), UMCNV1 factor to primary, UMUSTR UOM structure	Item Units of Measure from Item Master or Item Branch	F4101 on ITM; UMMCU is blank unless UOM conversion by branch is on
F4104	Item Cross Reference File	IVITM, IVXRT, IVAN8, IVCITM, IVEXDJ, IVCIRV	IVXRT cross-reference type, IVCITM customer or supplier item number, IVEFTJ effective, IVEXDJ expired	Item Cross Reference (P4104)	F4101 on ITM; F0101 on AN8
F4115	Item History	IHITM, IHMCU, IHFY	Period sales quantities and amounts by fiscal year, used for sales history and forecasting	Updated by Sales Update (R42800)	F4102 on ITM, MCU

Quantities in F41021 and F4111 are stored with implied decimals set by each data item's display decimals in the data dictionary, so check the DD before dividing. Business unit columns (LIMCU, ILMCU, COMCU) are right justified 12 character strings. Program numbers shown are the standard 9.2 applications; your site may run custom versions.

Availability not matching P41202?

Send us your branch availability definition and a sample item and we will show which F41021 buckets explain the gap.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Search a column alias such as LIHCOM or a table such as F4105 and read across: key columns, the important fields, the program that maintains the data and the usual join. Use the F41021 rows when an availability number on a report does not match Item Availability (P41202). The usual cause is that the report subtracts a bucket that the branch's availability definition does not, or the other way round. Export the table to Excel and keep it next to your SQL while you write the joins.

Where availability comes from

F41021 holds one row per item, branch, location and lot, with separate quantity buckets: on hand, hard committed, soft committed, future committed, work order soft and hard commits, on PO, on work order receipt, in transit, in inspection and others. P41202 does not store availability. It calculates it from these buckets using the Item Availability Definition in Branch/Plant Constants (P41001), where you choose which quantities subtract (for example soft, hard and future commits) and which add (for example PO receipts). Two branches can legitimately show different availability for the same buckets.

Soft commits usually sit on the primary location row (LIPBIN = P)
Hard commits sit on the specific location and lot
Future commits move to soft or hard via R42997

Worked example

Item 1001 in branch M30, primary location, lot blank: LIPQOH 500, LIHCOM 120, LIPCOM 80, LIFCOM 40, LIPREQ 300. If P41001 subtracts hard, soft and future commits and does not add PO receipts, availability is 500 - 120 - 80 - 40 = 260. If the definition also adds quantity on PO, it becomes 560. To prove the on-hand figure, sum ILTRQT in F4111 for the same item, branch, location and lot; the total should equal LIPQOH unless history has been purged or a repost is needed.

Gotchas

Do not join on LITM: use the short item number ITM. Remember that F4105 holds several rows per item and branch, one per cost method, and COCSIN flags the one used for inventory and sales costing. Weighted average costs (02) change as transactions post, so recompute margins from F4111 rather than from today's F4105 cost. F41002 rows are only branch specific when UOM conversion by branch is switched on in the system constants.

Frequently Asked Questions

What is the F41021 table in JDE?

F41021 is the Item Location File. It holds one row per item, branch/plant, location and lot with on hand and all commitment and incoming quantity buckets such as LIPQOH, LIHCOM, LIPCOM, LIFCOM, LIPREQ and LIQWBO. Its primary key is LIITM, LIMCU, LILOCN and LILOTN.

What is the item branch table in JDE?

F4102 is the Item Branch File, keyed by IBITM and IBMCU. It holds branch-specific settings such as stocking type, G/L class, buyer, supplier, reorder point, reorder quantity and safety stock. It is maintained in Item Branch/Plant (P41026) and joins to F4101 on the short item number.

What is the JDE F4111 table?

F4111 is the Item Ledger File, also called the cardex. Every inventory transaction writes a row with item, branch, location, lot, document number and type, transaction date, GL date, quantity (ILTRQT) and extended cost (ILPAID). Its unique key is ILUKID. You review it in Item Ledger Inquiry (P4111).

How is item availability calculated in JDE?

Item Availability (P41202) starts with on hand from F41021 and subtracts or adds the buckets selected in the Item Availability Definition of Branch/Plant Constants (P41001), for example hard, soft and future commits subtracted and PO receipts optionally added. The setting is per branch/plant, so check it before comparing reports.

If you want buyers and planners to ask JD Edwards stock and availability questions in plain English, answered from F41021 and F4111 on your own servers, we can build that.

Talk to an Expert Explore More Free Tools