# Infor System21 DB2 for i SQL Query Generator

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

## Short answer

Fill in your System21 libraries and field names and get read-only SQL for order header to line joins, orders with customer names, stock by stockroom, item lookups and locked records.

## Category

developer

## Erp

Infor System21

## Audience

- IBM i developer
- Report writer
- ERP administrator

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Distribution file library

**Key:** lib

**Type:** text

**Default:** OSLD1F3

#### Sales Ledger file library

**Key:** arlib

**Type:** text

**Default:** OSLSLF3

#### Company code value

**Key:** company

**Type:** text

**Default:** 01

#### Company field in OEP40

**Key:** cono40

**Type:** text

**Default:** CONO40

**Help:** Convention-based; confirm with DSPFFD

#### Order number field in OEP40

**Key:** ordn40

**Type:** text

**Default:** ORDN40

#### Customer field in OEP40

**Key:** cusn40

**Type:** text

**Default:** CUSN40

**Help:** Convention-based; confirm with DSPFFD

#### Company field in OEP55

**Key:** cono55

**Type:** text

**Default:** CONO55

**Help:** Convention-based; confirm with DSPFFD

#### Order number field in OEP55

**Key:** ordn55

**Type:** text

**Default:** ORDN55

**Help:** Convention-based; confirm with DSPFFD

#### Item field in OEP55

**Key:** catn55

**Type:** text

**Default:** CATN55

**Help:** OE uses CATN for item number; confirm with DSPFFD

#### Company field in SLP05

**Key:** cono05

**Type:** text

**Default:** CONO05

**Help:** Convention-based; confirm with DSPFFD

#### Customer field in SLP05

**Key:** cusn05

**Type:** text

**Default:** CUSN05

**Help:** Convention-based; confirm with DSPFFD

#### Item field in INP35

**Key:** pnum35

**Type:** text

**Default:** PNUM35

**Help:** IN uses PNUM for item number; confirm with DSPFFD

#### Item field in INP60

**Key:** pnum60

**Type:** text

**Default:** PNUM60

**Help:** Convention-based; confirm with DSPFFD

#### Item number to look up

**Key:** item

**Type:** text

**Default:** ITEM-1001

#### Row limit

**Key:** rows

**Type:** number

**Default:** 100

**Blocks:**

#### 0. Inspect the files first (CL commands)

**Body:** -- Run these on a command line before trusting any field name below
-- IBM i commands:
--   DSPFFD FILE({{lib}}/OEP40)
--   DSPFFD FILE({{lib}}/OEP55)
--   DSPFFD FILE({{arlib}}/SLP05)
--   DSPDBR FILE({{lib}}/OEP40)
-- System21 commands in OSLSYS (prompt with F4):
--   DSPFLD   display a file's layout
--   DSPKEY   display a file's access path
--   DSPDEP   display all logical files of a physical file

#### 1. Order header to lines

**Body:** -- Sales order header (OEP40) joined to order lines (OEP55)
SELECT h.*, l.*
  FROM {{lib}}.OEP40 h
  JOIN {{lib}}.OEP55 l
    ON l.{{cono55}} = h.{{cono40}}
   AND l.{{ordn55}} = h.{{ordn40}}
 WHERE h.{{cono40}} = '{{company}}'
 ORDER BY h.{{ordn40}}
 FETCH FIRST {{rows}} ROWS ONLY;

#### 2. Orders with customer master

**Body:** -- Order headers with the customer record from the Sales Ledger (SLP05)
SELECT h.{{ordn40}}, h.{{cusn40}}, c.*
  FROM {{lib}}.OEP40 h
  LEFT JOIN {{arlib}}.SLP05 c
    ON c.{{cono05}} = h.{{cono40}}
   AND c.{{cusn05}} = h.{{cusn40}}
 WHERE h.{{cono40}} = '{{company}}'
 ORDER BY h.{{ordn40}}
 FETCH FIRST {{rows}} ROWS ONLY;

#### 3. Stock by stockroom for one item

**Body:** -- Item stockroom balance profile (INP60) for one item
SELECT *
  FROM {{lib}}.INP60
 WHERE {{pnum60}} = '{{item}}'
 FETCH FIRST {{rows}} ROWS ONLY;

#### 4. Item master lookup

**Body:** -- Inventory item master (INP35)
SELECT *
  FROM {{lib}}.INP35
 WHERE {{pnum35}} = '{{item}}';

-- Order lines for the same item (OE references the item as CATN)
SELECT l.*
  FROM {{lib}}.OEP55 l
 WHERE l.{{cono55}} = '{{company}}'
   AND l.{{catn55}} = '{{item}}'
 FETCH FIRST {{rows}} ROWS ONLY;

#### 5. Records currently locked by System21

**Body:** -- Sales orders held by a user or a failed program (ACTF40 not blank)
SELECT {{ordn40}}, ACTF40
  FROM {{lib}}.OEP40
 WHERE ACTF40 <> ''
 FETCH FIRST {{rows}} ROWS ONLY;

-- Items held in maintenance (ACTF35 not blank)
SELECT {{pnum35}}, ACTF35
  FROM {{lib}}.INP35
 WHERE ACTF35 <> ''
 FETCH FIRST {{rows}} ROWS ONLY;

-- Most applications also log locks in control files, for example INP99
SELECT *
  FROM {{lib}}.INP99
 FETCH FIRST {{rows}} ROWS ONLY;

**Notes:** Verified names from IBM Redbook SG24-5967 chapter 3: files OEP40, OEP55, INP35, INP60, INP99 (OSLD1F3) and SLP05 (OSLSLF3), fields ORDN40, ACTF40, ACTF35, and the convention that OE uses CATN and IN uses PNUM for item number. All other field defaults (CONO40, CUSN40, ORDN55, CONO55, CATN55, CONO05, CUSN05, PNUM35, PNUM60) are placeholders built from the naming convention and must be confirmed with DSPFFD on your system. These queries are read-only. Never update System21 files directly: lock fields and control files would be left inconsistent.

## Content

**Intro:** This generator writes read-only Db2 for i SQL for Infor System21: sales order header to line joins, orders with the customer master, stock by stockroom, item lookups and records currently locked. Every field name is an editable input so you can match your own file layouts after checking them with DSPFFD.

**Sections:**

#### How to use it

**Body:** Start with block 0 and run DSPFFD against OEP40, OEP55 and SLP05 in your libraries. Copy the real company, order, customer and item field names into the inputs; the defaults follow the System21 convention of field reference code plus two-digit file number, but only ORDN40 and the lock fields ACTF40 and ACTF35 are confirmed in the source. Set the distribution file library (normally OSLD1F3) and the Sales Ledger file library (normally OSLSLF3), the company code value and a row limit. Then paste the SQL into Run SQL Scripts in IBM i Access Client Solutions or any ODBC tool connected with a read-only profile.

#### Field naming convention behind the defaults

**Body:** System21 field names are a short field reference code followed by the last two digits of the physical file number. The order number in the sales order header OEP40 is therefore ORDN40, and the same reference code ORDN appears in other files with their own suffix. The inputs use this rule to suggest CONO40, CUSN40, ORDN55 and so on. Two exceptions catch people out: the OE application refers to the item number as CATN while the IN application uses PNUM, and many General Ledger fields have no suffix at all, such as CONO, TXSRCE, TT and DOCREF that link FLP007 and FLP008.

**Bullets:**

- OEP40 header, OEP55 lines, OEP65 and OEP70 invoices
- INP35 item master, INP60 stockroom balance
- SLP05 customer master in OSLSLF3

#### Worked example: finding a stuck order

**Body:** A user reports that order 123456 cannot be amended. Run block 5: if the order appears with ACTF40 set, System21 still thinks someone is maintaining it, often after a session ended abnormally. Check active jobs for the user first. If no job holds it, the Redbook approach is to clear the lock through a controlled database utility under your support team, and to clear the Application Manager lock with Verify Allocations or the Active Jobs enquiry. Do not clear locks from an SQL script you share with users; record who cleared what and why.

#### Version notes and gotchas

**Body:** The source Redbook describes System21 3.5-era layouts; later releases and Aurora can change field lengths or add files, so test each query on a copy first. Company and code fields are usually fixed-length character, so compare with quoted values and watch for leading zeros. If your site has several environments, the libraries may have different names (a test environment might use TSTD1F3 instead of OSLD1F3), so set the libraries for the environment you mean. The Redbook also warns that some fields may be obsolete or reused, so verify any field you report on against real data.

**Cta:** If you want planners and customer service to ask System21 these questions in plain English, with read-only SQL on your own IBM i, we can set that up.

**Faq:**

#### How do I join the System21 order header to order lines?

**Answer:** Join OEP40 (header) to OEP55 (lines) in OSLD1F3 on company and order number. The header order number is ORDN40; by the naming convention the line file uses the same reference code with suffix 55. Confirm both field names with DSPFFD, then use block 1 of this generator.

#### Why does the item number field differ between OE and IN files?

**Answer:** System21 applications have their own field reference files, and they do not always use the same reference code for the same thing. The OE application references item number as CATN, while the IN application uses PNUM. So OEP55 carries a CATN field and INP35 a PNUM field for the same item.

#### Can I update System21 files with SQL?

**Answer:** Not safely. System21 uses soft locks, lock fields such as ACTF40 and control files such as INP99, and its programs maintain related files together. A direct SQL update skips all of that and can leave balances and locks inconsistent. Keep SQL read-only and make changes through System21 functions.

#### Which tool should I use to run these queries?

**Answer:** Run SQL Scripts in IBM i Access Client Solutions is the usual choice, and any ODBC or JDBC client works with the IBM i host servers. Use a profile with read authority only to the OSL file libraries you need, and keep the row limit while testing.

## Search terms

infor system 21, infor system 21 erp, infor system21 aurora erp, what is infor system 21, system21 sql query, oep40 oep55 join, system 21 db2 query, system21 inp60 stock query

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