# BPCS and Infor LX SQL Query Generator for Reports on AS400

> Source: https://netray.co/tools/infor-lx-bpcs-sql-query-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Fill in your library, warehouse, item and field names to get ready-to-run Db2 for i SQL for the BPCS reports people ask for most.

## Category

developer

## Erp

Infor LX / BPCS

## Audience

- IBM i developer
- Report writer
- ERP administrator

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Data library

**Key:** lib

**Type:** text

**Default:** BPCSF

**Help:** Library holding the BPCS or LX data files

#### Warehouse

**Key:** whs

**Type:** text

**Default:** 1

#### Item (blank for all)

**Key:** item

**Type:** text

#### From date (numeric, as stored)

**Key:** from_date

**Type:** text

**Default:** 20250101

**Help:** YYYYMMDD or CYYMMDD depending on release

#### Row limit

**Key:** limit

**Type:** number

**Default:** 100

#### ECH order number field

**Key:** ech_ord

**Type:** text

**Default:** HORD

#### ECL order number field

**Key:** ecl_ord

**Type:** text

**Default:** LORD

#### ECL line number field

**Key:** ecl_line

**Type:** text

**Default:** LLINE

#### ECL item field

**Key:** ecl_item

**Type:** text

**Default:** LPROD

#### ECL quantity ordered field

**Key:** ecl_qord

**Type:** text

**Default:** LQORD

#### ECL quantity shipped field

**Key:** ecl_qshp

**Type:** text

**Default:** LQSHP

#### IIM item field

**Key:** iim_item

**Type:** text

**Default:** IPROD

#### IIM description field

**Key:** iim_desc

**Type:** text

**Default:** IDESC

#### ILI item field

**Key:** ili_item

**Type:** text

**Default:** LPROD

#### ILI warehouse field

**Key:** ili_whs

**Type:** text

**Default:** LWHS

#### ILI location field

**Key:** ili_loc

**Type:** text

**Default:** LLOC

#### ILI opening balance field

**Key:** ili_opb

**Type:** text

**Default:** LOPB

#### ILI receipts field

**Key:** ili_rct

**Type:** text

**Default:** LRCT

#### ILI adjustments field

**Key:** ili_adj

**Type:** text

**Default:** LADJ

#### ILI issues field

**Key:** ili_iss

**Type:** text

**Default:** LISS

#### ITH item field

**Key:** ith_item

**Type:** text

**Default:** TPROD

#### ITH transaction type field

**Key:** ith_type

**Type:** text

**Default:** TTYPE

#### ITH quantity field

**Key:** ith_qty

**Type:** text

**Default:** TQTY

#### ITH transaction date field

**Key:** ith_date

**Type:** text

**Default:** TTDTE

#### ITH warehouse field

**Key:** ith_whs

**Type:** text

**Default:** TWHS

**Blocks:**

#### 1. Open customer order lines (ECH, ECL, IIM)

**Body:** -- Open customer order lines with item description.
SELECT H.{{ech_ord}} AS ORDER_NO,
       L.{{ecl_line}} AS LINE_NO,
       L.{{ecl_item}} AS ITEM,
       I.{{iim_desc}} AS ITEM_DESC,
       L.{{ecl_qord}} AS QTY_ORDERED,
       L.{{ecl_qshp}} AS QTY_SHIPPED,
       L.{{ecl_qord}} - L.{{ecl_qshp}} AS QTY_OPEN
FROM {{lib}}.ECH H
JOIN {{lib}}.ECL L
  ON L.{{ecl_ord}} = H.{{ech_ord}}
LEFT JOIN {{lib}}.IIM I
  ON I.{{iim_item}} = L.{{ecl_item}}
WHERE L.{{ecl_qord}} - L.{{ecl_qshp}} > 0
ORDER BY H.{{ech_ord}}, L.{{ecl_line}}
FETCH FIRST {{limit}} ROWS ONLY;

#### 2. On-hand by location from ILI (verify formula)

**Body:** -- VERIFY FORMULA: opening balance + receipts + adjustments - issues.
-- Confirm the balance fields and their sign convention on your release before using this for counts.
SELECT B.{{ili_item}} AS ITEM,
       B.{{ili_whs}} AS WAREHOUSE,
       B.{{ili_loc}} AS LOCATION,
       B.{{ili_opb}} + B.{{ili_rct}} + B.{{ili_adj}} - B.{{ili_iss}} AS ON_HAND
FROM {{lib}}.ILI B
WHERE TRIM(B.{{ili_whs}}) = '{{whs}}'
  AND (TRIM('{{item}}') = '' OR B.{{ili_item}} = '{{item}}')
ORDER BY B.{{ili_item}}, B.{{ili_loc}}
FETCH FIRST {{limit}} ROWS ONLY;

#### 3. ITH transactions since a date, by transaction type

**Body:** -- Movement summary by transaction type for one item (or all items if blank).
SELECT T.{{ith_type}} AS TRAN_TYPE,
       COUNT(*) AS TRAN_COUNT,
       SUM(T.{{ith_qty}}) AS TOTAL_QTY
FROM {{lib}}.ITH T
WHERE T.{{ith_date}} >= {{from_date}}
  AND TRIM(T.{{ith_whs}}) = '{{whs}}'
  AND (TRIM('{{item}}') = '' OR T.{{ith_item}} = '{{item}}')
GROUP BY T.{{ith_type}}
ORDER BY T.{{ith_type}};

#### 4. ITH volume check by year (run before any big extract)

**Body:** -- ITH is usually the largest file in BPCS. Check volume first.
-- For YYYYMMDD dates this returns the year; for CYYMMDD it returns CYY (e.g. 125 = 2025).
SELECT INTEGER(T.{{ith_date}} / 10000) AS TRAN_YEAR,
       COUNT(*) AS ROW_COUNT
FROM {{lib}}.ITH T
GROUP BY INTEGER(T.{{ith_date}} / 10000)
ORDER BY TRAN_YEAR DESC
FETCH FIRST 30 ROWS ONLY;

-- If this is slow, check for an existing logical file keyed on item and date (DSPDBR FILE({{lib}}/ITH))
-- and ask your administrator before creating a new index on a production library.

#### 5. Confirm field names before you run anything

**Body:** -- CL command (IBM i command line): list fields of ECL to an outfile
DSPFFD FILE({{lib}}/ECL) OUTPUT(*OUTFILE) OUTFILE(QTEMP/ECLFLD)

-- SQL alternative using the catalog
SELECT COLUMN_NAME, COLUMN_TEXT, DATA_TYPE, LENGTH, NUMERIC_SCALE
FROM QSYS2.SYSCOLUMNS
WHERE TABLE_SCHEMA = '{{lib}}'
  AND TABLE_NAME IN ('ECH', 'ECL', 'IIM', 'ILI', 'ITH')
ORDER BY TABLE_NAME, ORDINAL_POSITION;

**Notes:** File names (ECH, ECL, IIM, ILI, ITH) are standard BPCS and LX names. The default field names are those commonly used on BPCS 6.x but could not be confirmed against a published reference for every release, so treat them as placeholders until you have checked each one with block 5. Field names differ between BPCS 4.05, 6.x and LX 8.x, and date fields are numeric YYYYMMDD or CYYMMDD depending on release. Warehouse and item are compared as text after TRIM. Run against a copy or off-peak, never in a tight loop on production.

## Content

**Intro:** This generator writes Db2 for i SQL for the BPCS and Infor LX reports asked for most often: open order lines, on-hand by location, inventory history by transaction type and a volume check on ITH. Every field name is an input, because names vary by release and site, and the last block shows you how to confirm them.

**Sections:**

#### How to use it

**Body:** First run block 5 against your data library. It lists the real columns of ECH, ECL, IIM, ILI and ITH with their text descriptions. Compare them to the defaults and correct any field that differs, for example if your release stores the order number under another name. Then set the warehouse, an optional item and the from-date in the format your release stores. Copy a block into ACS Run SQL Scripts, STRSQL or your query tool and run it. Each block uses SQL naming (LIBRARY.FILE). If your tool uses system naming, change the period to a slash.

**Bullets:**

- Block 1: open order lines from ECH and ECL with item description
- Block 2: on-hand by location from ILI
- Block 3: ITH movement summary by transaction type
- Block 4: ITH row count by year before a large extract
- Block 5: field list via DSPFFD or QSYS2.SYSCOLUMNS

#### The logic behind each query

**Body:** An open order line is one where ordered quantity minus shipped quantity is greater than zero. Block 1 joins header to lines on the order number, and IIM is a left join so lines are still listed when an item record is missing. Block 2 assumes ILI keeps an opening balance plus period receipts, adjustments and issues, which gives on-hand as opening + receipts + adjustments - issues. That is why it is labelled verify formula: confirm the fields and signs on your release, then check a few items against the on-screen inquiry. Block 3 groups ITH by transaction type so you can see receipts, issues and adjustments at a glance.

#### Worked example

**Body:** A planner wants open orders and the on-hand position for one item in warehouse 1. Set item to the part number, leave the from-date at 20250101 and the limit at 100. Block 1 returns each open line with its open quantity. Block 2 returns one row per location and lot with a computed on-hand figure. Block 3 then shows what moved since January: if issues are far higher than receipts while open orders are growing, the item needs a buyer's attention. Before you share the numbers, compare the block 2 total for the item with the warehouse balance on the standard inventory inquiry screen.

#### Version differences and gotchas

**Body:** BPCS 4.05, 6.x and LX 8.x share file names but not always field names or lengths. Many sites also add custom fields or run modified programs. Dates are numeric. Older releases often use CYYMMDD, where 1250101 means 1 January 2025, so a YYYYMMDD from-date will silently return nothing or everything. Warehouse fields are usually short character fields, so compare them as text. Block 1 does not filter on line status, so add your site's status field if cancelled lines carry open quantities. Finally, never run block 3 or 4 without filters at month-end, when ITH is busiest.

**Cta:** If you would rather your team asked LX or BPCS these questions in plain English, running on your own IBM i data with every query shown, we can set that up.

**Faq:**

#### How do I write a BPCS open order report in SQL?

**Answer:** Join ECH to ECL on the order number field and keep lines where ordered quantity minus shipped quantity is greater than zero. Left join IIM for the item description, and filter on line status if your site keeps cancelled lines. Confirm every field name with DSPFFD or QSYS2.SYSCOLUMNS before running, because names vary by release.

#### Can I query BPCS on AS400 with normal SQL?

**Answer:** Yes. BPCS and Infor LX files are Db2 for i physical files, so you can read them with SQL from ACS Run SQL Scripts, STRSQL, ODBC or JDBC clients. Use read-only access, the correct library, and row limits on large files such as ITH.

#### What date format do BPCS files use?

**Answer:** Dates are stored as numbers, not SQL DATE values. Depending on release and file, the format is YYYYMMDD or CYYMMDD, where C is the century digit. Check a few sample rows before filtering, and convert explicitly if you need real dates in a report.

#### Is the ILI on-hand formula official?

**Answer:** No. Opening balance plus receipts plus adjustments minus issues is the common practitioner approach to reading ILI. It is labelled verify formula because field names and signs can differ. Check the result against the standard inventory inquiry for several items before relying on it.

## Search terms

bpcs reports, bpcs report, infor lx as400, bpcs as400, bpcs order header file, bpcs sql query, bpcs open order report sql, infor lx sql

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json