Other ERPsFree referencePDF, Excel, Word exportInfor LX / BPCSAll Infor LX and BPCS tools

BPCS and Infor LX Table List: Core Database Files

This is a searchable list of the core BPCS and Infor LX physical files that report writers and developers query on IBM i. Each row gives the module, what one record represents, the files it joins to and the warnings worth knowing before you run a query.

Download:

File	Area	Description	One record per	Related files	Notes
IIM	Inventory	Item master	Item	IWI, ILI, ECL, HPO, MBM	Start point for almost every report; joins on the item number
IWM	Inventory	Warehouse master	Warehouse	IWI, ILM	Warehouse code, name and settings; join to it on the warehouse field
IWI	Inventory	Item-warehouse record	Item and warehouse	IIM, IWM, ILI	Warehouse-level planning data and balances; compare to the sum of ILI rows when reconciling
ILM	Inventory	Location master	Warehouse and location	IWM, ILI	Defines the stocking locations in each warehouse; holds no item balances
ILI	Inventory	Location inventory	Item, warehouse, location and lot	ILM, IWI, IIM	Location-level balances; the usual source for on-hand by location
ITH	Inventory	Inventory transaction history	Inventory transaction	IIM, ILI	Very large on mature systems; always filter by item and date and limit rows
ECH	Order entry	Customer order header	Customer order	ECL, RCM	Header only; quantities live on the lines
ECL	Order entry	Customer order lines	Order line	ECH, IIM	Item, ordered and shipped quantities per line
RCM	Receivables	Customer master	Customer	ECH	Customer name, address and credit data
AVM	Payables	Vendor master	Vendor	HPH	Vendor name and terms
HPH	Purchasing	Purchase order header	Purchase order	HPO, AVM	Header only; item and quantity data are on HPO
HPO	Purchasing	Purchase order lines	PO line	HPH, IIM	Open PO quantities for buyer and MRP checks
FSO	Shop floor	Shop order summary	Shop order	IIM, MBM, FRT	Order status and quantities at order level
MBM	Bill of material	Bill of material	Parent and component relationship	IIM, FSO	One row per component under a parent; recursive queries walk levels
FRT	Shop floor	Routings	Routing operation	IIM, FSO	Standard operations used to build shop order operations

File names follow MidrangeWiki BPCS Files and BPCS-L usage for BPCS 6.x and Infor LX 8.x. Libraries, logical files and some descriptions vary by release and by site modifications. List the fields of any file on your system with DSPFFD before writing a query.

Get this list mapped to your own BPCS libraries

Send us your release and library names, and we will return the file list with your actual field names and logical files.

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

Was this tool useful?

How BPCS file names work

BPCS file names are three characters, and the first letter usually tells you the module. That is why the item master is IIM, order entry files start with E and purchasing files start with H. Once you know the letter, an unfamiliar file name in a job log or a query is easy to place. Infor LX kept most of the BPCS file names, so a report written against BPCS 6.x often runs on LX 8.x with only field-level changes. Most master files also carry a record ID field that separates active records from deleted ones. Check the values your site uses before you trust a count.

I: inventory (IIM, IWM, IWI, ILM, ILI, ITH)
E: order entry (ECH, ECL)
H: purchasing (HPH, HPO)
F: shop floor (FSO, FRT)
M: bill of material (MBM)
R: receivables (RCM), A: payables (AVM)

Inventory balances: ILI, IWI and IIM

Inventory is held at three levels. ILI holds balances by item, warehouse, location and lot. IWI holds them by item and warehouse. IIM carries item-level totals. In many releases period-end processing rolls location balances forward and resets the period movement fields (confirm on your release), so the same item can show different numbers depending on which file you read and when. For on-hand by location, read ILI and add its opening balance and period movement fields. For reconciliation, sum ILI by item and warehouse and compare the result to IWI. Any gap normally means a failed or partial period-end run, or a transaction posted while the close was running.

How to list fields and find the right one

Field names are not shown here because they change between BPCS 4.05, 6.x and LX 8.x, and sites add their own. On the IBM i command line, DSPFFD FILE(BPCSF/ECL) lists every field with its text description, and adding OUTPUT(*OUTFILE) writes the list to a file you can query. With SQL you can read QSYS2.SYSCOLUMNS for the same library and table. Use DSPDBR to see the logical files built over a physical file. Before you query ITH or another large file, check that a logical file or index exists over the columns you filter on.

Common mistakes

The most common reporting error is summing ECL without checking the line status your site uses for cancelled or closed lines. The next is using IIM totals as if they were live location balances. Unfiltered queries against ITH can run for a very long time and slow interactive users, so always add an item or date filter and a row limit, and run big extracts against a copy or off-peak. Also confirm the library list. Many sites keep several BPCSF-style libraries for test and production, and a query against the wrong one returns believable but wrong numbers.

Frequently Asked Questions

What is the BPCS order header file?

The customer order header file is ECH. It holds one record per customer order. Line-level data, including item and ordered and shipped quantities, is in ECL. Join ECH to ECL on the order number field, after confirming its name with DSPFFD on your release, and check whether your site uses any additional line files.

Which BPCS table holds on-hand inventory?

Location and lot balances are in ILI, warehouse balances are in IWI, and item-level totals are on IIM. For a by-location on-hand report, read ILI and combine its opening balance and period movement fields. Sum ILI by item and warehouse and compare it to IWI to reconcile.

Did Infor LX change the BPCS table names?

Mostly no. Infor LX kept the main BPCS physical file names such as IIM, ITH, ECH and ECL, so the file list carries over. Field lengths, added fields and library names can differ by release, and custom fields are common, so check each file with DSPFFD before you reuse an old query.

Why is my ITH query so slow?

ITH stores every inventory transaction, and on a mature system it can hold many millions of rows. Filter on item and date, select only the columns you need, and add FETCH FIRST n ROWS ONLY. Check DSPDBR for a logical file keyed on item and date, and run large extracts off-peak or against a copy.

If you want planners and buyers to ask BPCS or LX questions in plain English on your own servers, with the query shown for every answer, we can set that up.

Talk to an Expert Explore More Free Tools