ERP Table and Field Explorer

Infor LN and Baan Database Tables and Field Explorer

Search the core Infor LN tables by table code, field or description, see each table's key, important fields and joins, then pick two to four tables to get a JOIN skeleton for SQL Server or Oracle. LN table codes are package, module and number (tdsls400 is package td, module sls, table 400), every company has its own copy of each table identified by the company number, and shared tables live in one physical company.

74 tables, 666 documented fields and 152 join paths across 9 modules. Free, runs in your browser, no login needed.

1Find a table by name, description or field

Naming: LN table codes are a 2 letter package, a 3 letter module and a 3 digit number (tdsls400 = td sls 400), and fields are written table.field (tdsls400.orno). Tables exist per company number; company 000 holds the Tools tables. In the database the company number is appended to the table name, and the database driver adds its own prefixes to tables and columns (commonly ttdsls400100 with column t_orno on SQL Server or t$orno on Oracle). Confirm the physical names in your database before running generated SQL.

Tables by module

2Pick tables

74 of 74

Items

tcibd001

Items - General: one row per item with item type, item group, inventory unit, lot and serial control and the default supply source.

Also known as: Items (tcibd0501m000), item master, general item data

Key:item
FieldMeaning
itemItem code.
dscaItem description.
kitmItem type: Product, Purchased, Manufactured, Generic, Cost, Service, Subcontracted service, List, Tool, Equipment, Engineering module (enumerated).
citgItem group (tcmcs023).
cuniInventory unit (tcmcs001).
srceDefault supply source: Shop Floor, Repetitive, Assembly, Purchase, Subcontract, Distribution.
ctypProduct type.
cpclProduct class.
cplnProduct line.
ltctLot controlled (yes/no).
seriSerialized (yes/no).
csigItem signal, for example to block or flag an item.

Joins

  • tcibd001.citg = tcmcs023.citg
  • tcibd001.cuni = tcmcs001.cuni
  • tcibd001.item = tcibd200.item

    Item ordering data.

  • tcibd001.item = tdipu001.item

    Item purchase data.

  • tcibd001.item = tiipd001.item

    Item production data.

  • tcibd001.item = whwmd400.item

    Item warehousing data.

  • tcibd001.item = ticpr007.item

    Item costing data.

  • tcibd001.item = tdpcg031.item
  • tcibd001.item = tibom010.mitm
  • tcibd001.item = tirou101.mitm
  • tcibd001.item = tisfc001.mitm
  • tcibd001.item = ticst001.sitm
  • tcibd001.item = tdsls401.item
  • tcibd001.item = cisli310.item
  • tcibd001.item = tdpur401.item
  • tcibd001.item = whinh210.item
  • tcibd001.item = whinh220.item
  • tcibd001.item = whinh431.item
  • tcibd001.item = whinr110.item
  • tcibd001.item = whwmd215.item
  • tcibd001.item = whwmd210.item
  • tcibd001.item = whltc100.item
  • tcibd001.item = whltc500.item

Other documented columns: wght and cwun (weight), cean (EAN code), cmnf (manufacturer), eitm (revision controlled), cnfg (configurable), seak and seab (search keys), lmdt (last modification date). Check the length and segments of the item domain in your data dictionary: project items carry a project segment in the item code.

3Generate a JOIN skeleton (2 to 4 tables)

Starters:
Output
-- Query skeleton generated by netray.co/tools/table-explorer
-- Dialect: SQL Server (T-SQL). Review keys, filters and indexes before running on production.
-- Logical LN names with the company number appended. Apply your database's physical prefixes before running: commonly t + table + company (ttdsls400100) and t_ + field on SQL Server or t$ + field on Oracle. Verify in your database.
-- Status, type and yes/no fields are enumerated domains stored as numbers, not labels: look up the values in the data dictionary before filtering.
-- Read only. Create and change data through LN sessions, DAL logic or BODs, not direct UPDATE statements.

SELECT TOP 100
    tdsl.orno,
    tdsl.sotp,
    tdsl.ofbp,
    tdsl.stbp,
    tdsl.itbp,
    tdsl2.pono,
    tdsl2.sqnb,
    tdsl2.item,
    tdsl2.qoor,
    tdsl2.cuqs,
    tcco.cadr,
    tcco.cofc,
    tcco.crep,
    tcco.cpls,
    tcco.cdec,
    tcib.dsca,
    tcib.kitm,
    tcib.citg,
    tcib.cuni,
    tcib.srce
FROM dbo.tdsls400100 AS tdsl
LEFT JOIN dbo.tdsls401100 AS tdsl2
    ON tdsl.orno = tdsl2.orno
LEFT JOIN dbo.tccom110100 AS tcco
    ON tdsl.ofbp = tcco.ofbp
LEFT JOIN dbo.tcibd001100 AS tcib
    ON tdsl2.item = tcib.item
ORDER BY tdsl.orno;
Free, optional. Reopen it on any device.
Download:

How to use it

  1. 1

    Search

    Type a table code such as tdsls401, a field such as qoor, a session code such as tisfc0501m000, or a business word such as shipment. Field matches are highlighted.

  2. 2

    Filter and inspect

    Narrow by module, open a table to see its key, the documented fields and the tables it joins to. Click a join to jump to that table.

  3. 3

    Set the company

    Enter your LN company number. It is appended to every table name in the generated SQL, because each LN company has its own set of tables.

  4. 4

    Build the join

    Add two to four tables or a starter. Then apply your database's physical prefixes (see the notes in the SQL) and check enumerated fields against your data dictionary before running it.

  5. 5

    Keep it

    Star tables as favorites, export to PDF, Excel, Word or CSV, and optionally save the setup to your Netray account.

Questions about Infor LN (and Baan) tables

How are Infor LN and Baan table names built?

A table code is a 2 letter package, a 3 letter module and a 3 digit table number, so tdsls400 is package td (distribution), module sls (sales), table 400, and tcibd001 is package tc (common), module ibd (item base data), table 001. Infor's Table Fields help states that a table is identified by package, module and table code and that fields are referenced as table.field, for example tdsls400.orno. Customer defined fields get a cdf_ prefix, for example tdsls400.cdf_fld1.

Why does every table have a company number such as 100?

Each LN company has its own set of tables, identified by the company number defined in Companies (ttaad1100m000); company 000 is reserved for Tools tables. In the database the company number is part of the physical table name, so the same query runs against another company by changing the number. When tables are shared through logical table linking, the physical table exists in one company only and the other companies read it there.

What are the physical table and column names in SQL Server or Oracle?

The database driver adds prefixes to the logical names. The commonly seen form is a leading t on the table plus the company number (ttdsls400100) and a t_ prefix on SQL Server columns (t_orno) or t$ on Oracle (t$orno). This tool shows logical names with the company number appended; check the actual names in your database or with your DBA before running the generated SQL.

Which tables hold sales orders, deliveries and invoices?

tdsls400 is the sales order header (sold-to ofbp, ship-to stbp, invoice-to itbp, status hdst) and tdsls401 holds the lines keyed by orno, pono and sqnb with ordered quantity qoor, delivered quantity qidl and price pric. Actual deliveries are in tdsls406, which also carries the invoice company, transaction type and number. The invoice itself is in Invoicing: cisli305 (header) and cisli310 (lines), keyed by sfcp, tran and idoc.

Where are the bill of material and routing stored?

The production BOM is in tibom010 (parent mitm, position pono, sequence seqn, component sitm, net quantity qana). Routings per item are in tirou101 and their operations in tirou102 (mitm, opro, opno) with task, work center, machine, setup and cycle time. A released production order copies them into ticst001 (estimated materials) and tisfc010 (order operations) under the order number pdno in tisfc001.

Which table gives stock on hand?

whwmd215 holds inventory on hand (qhnd), blocked (qblk), on order (qord) and allocated (qall) per warehouse and item. whinr140 breaks stock down by warehouse, location, item, lot and inventory date. Inventory movements with the order that caused them are in whinr110, with the order type in koor and inventory after the transaction in qhnd.

Why do my status filters return nothing?

Fields such as tdsls400.hdst, tdpur400.hdst, tisfc001.osta and tcibd001.kitm are enumerated domains. The application shows labels like Approved or Released, but the database stores a number. Look up the domain values in the data dictionary for your LN version and filter on the numbers.

Do these tables apply to Baan IV and Baan 5?

The package and module structure comes from Baan, and Baan 5 is close to LN for many tables, but table numbers and fields changed between Baan IV, Baan 5 and LN releases (Baan IV, for example, used different sales and item tables). The fields here were checked against the LN 10.7 online help; check the Table Definitions session (ttadv4526m000) and Table Fields (ttadv4522m000) in your own version before relying on them.

Sources

Table names and columns were checked against these public references. Tables whose key or columns we could not confirm say so in their notes. Column sets change between releases, so confirm against the data dictionary of your own version before you build on it.