BPCS and Infor LX SQL Query Generator for Reports on AS400

-- Open customer order lines with item description.
SELECT H.HORD AS ORDER_NO,
       L.LLINE AS LINE_NO,
       L.LPROD AS ITEM,
       I.IDESC AS ITEM_DESC,
       L.LQORD AS QTY_ORDERED,
       L.LQSHP AS QTY_SHIPPED,
       L.LQORD - L.LQSHP AS QTY_OPEN
FROM BPCSF.ECH H
JOIN BPCSF.ECL L
  ON L.LORD = H.HORD
LEFT JOIN BPCSF.IIM I
  ON I.IPROD = L.LPROD
WHERE L.LQORD - L.LQSHP > 0
ORDER BY H.HORD, L.LLINE
FETCH FIRST 100 ROWS ONLY;

-- 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.LPROD AS ITEM,
       B.LWHS AS WAREHOUSE,
       B.LLOC AS LOCATION,
       B.LOPB + B.LRCT + B.LADJ - B.LISS AS ON_HAND
FROM BPCSF.ILI B
WHERE TRIM(B.LWHS) = '1'
  AND (TRIM('[Item (blank for all)]') = '' OR B.LPROD = '[Item (blank for all)]')
ORDER BY B.LPROD, B.LLOC
FETCH FIRST 100 ROWS ONLY;

-- Movement summary by transaction type for one item (or all items if blank).
SELECT T.TTYPE AS TRAN_TYPE,
       COUNT(*) AS TRAN_COUNT,
       SUM(T.TQTY) AS TOTAL_QTY
FROM BPCSF.ITH T
WHERE T.TTDTE >= 20250101
  AND TRIM(T.TWHS) = '1'
  AND (TRIM('[Item (blank for all)]') = '' OR T.TPROD = '[Item (blank for all)]')
GROUP BY T.TTYPE
ORDER BY T.TTYPE;

-- 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.TTDTE / 10000) AS TRAN_YEAR,
       COUNT(*) AS ROW_COUNT
FROM BPCSF.ITH T
GROUP BY INTEGER(T.TTDTE / 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(BPCSF/ITH))
-- and ask your administrator before creating a new index on a production library.

-- CL command (IBM i command line): list fields of ECL to an outfile
DSPFFD FILE(BPCSF/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 = 'BPCSF'
  AND TABLE_NAME IN ('ECH', 'ECL', 'IIM', 'ILI', 'ITH')
ORDER BY TABLE_NAME, ORDINAL_POSITION;