JD Edwards Table Reference: F4211, F4311, F0101, F4101, F4102
JD Edwards EnterpriseOne table names look cryptic until you learn the pattern: an F, the system code and a sequence, with every column prefixed by two letters from the table. This reference lists common tables such as F4211, F4311, F0101, F4101 and F4102 with their prefix and key fields so you can write joins faster.
| Table | Description | Module | Column prefix | Key fields |
|---|---|---|---|---|
F0101 | Address book master | Address Book (01) | AB | ABAN8 |
F0116 | Address by date | Address Book (01) | AL | ALAN8, ALEFTB |
F0111 | Address book who's who | Address Book (01) | WW | WWAN8, WWIDLN |
F0301 | Customer master | Accounts Receivable (03) | AI | AIAN8 |
F0401 | Supplier master | Accounts Payable (04) | A6 | A6AN8 |
F0005 | User defined code values | System (00) | DR | DRSY, DRRT, DRKY |
F0004 | User defined code types | System (00) | DT | DTSY, DTRT |
F0006 | Business unit master | General Accounting (00) | MC | MCMCU |
F0010 | Company constants | General Accounting (00) | CC | CCCO |
F0902 | Account balances | General Accounting (09) | GB | GBMCU, GBOBJ, GBSUB, GBLT, GBFY |
F0911 | Account ledger | General Accounting (09) | GL | GLKCO, GLDCT, GLDOC, GLDGJ, GLJELN |
F0411 | Accounts payable ledger | Accounts Payable (04) | RP | RPKCO, RPDCT, RPDOC, RPSFX |
F03B11 | Customer ledger | Accounts Receivable (03B) | RP | RPKCO, RPDCT, RPDOC, RPSFX |
F4101 | Item master | Inventory Management (41) | IM | IMITM (short item), IMLITM (2nd item) |
F4102 | Item branch | Inventory Management (41) | IB | IBITM, IBMCU |
F41021 | Item location | Inventory Management (41) | LI | LIMCU, LIITM, LILOCN, LILOTN |
F4111 | Item ledger (cardex) | Inventory Management (41) | CI | CIUKID (unique key ID) |
F4201 | Sales order header | Sales Order Management (42) | SH | SHDOCO, SHDCTO, SHKCOO |
F4211 | Sales order detail | Sales Order Management (42) | SD | SDDOCO, SDDCTO, SDKCOO, SDLNID |
F4301 | Purchase order header | Procurement (43) | PH | PHDOCO, PHDCTO, PHKCOO |
F4311 | Purchase order detail | Procurement (43) | PD | PDDOCO, PDDCTO, PDKCOO, PDLNID |
F43121 | Purchase order receiver | Procurement (43) | PR | PRMATC, PRDOC, PRDCT, PRLNID |
F3002 | Bill of material master | Product Data Management (30) | IV | IVKIT, IVMMCU, IVITM, IVCMCU |
F3003 | Routing master | Product Data Management (30) | IR | IRKIT, IRMMCU, IRSEQN |
F4801 | Work order master | Shop Floor Management (31) | WA | WADOCO |
F3111 | Work order parts list | Shop Floor Management (31) | WM | WMDOCO, WMCPIT or WMCPNT |
F3112 | Work order routing | Shop Floor Management (31) | WL | WLDOCO, WLOPSQ |
Column prefixes are two letters per table and they are stable, but individual column names, keys and indexes vary by release and by customer modification. Confirm field names in the data dictionary (P9210) or the table design in Object Management Workbench before writing production SQL.
JDE data map for your project
Optional: we can extend this reference with your custom tables and fields for a migration or reporting project.
Optional. The tool and every export work without this. No spam.
How to use the reference
Search by table number, a word like item or voucher, or a column prefix such as SD. Use the keys column to build joins and the prefix column to read unfamiliar SQL, since every column in a table starts with its prefix. Export the filtered list to Excel when you start a data extract, conversion or reporting project and need a shared data map.
Reading the table naming pattern
The first digits after F show the system: 01 address book, 03 and 04 receivables and payables, 09 general accounting, 30 and 31 manufacturing, 41 inventory, 42 sales and 43 procurement. A trailing letter or extra digits distinguish related tables, such as the sales header F4201 and detail F4211. Business data usually lives in a business data schema, while control tables such as user defined codes live in a separate control table schema.
- Item numbers: short item (ITM) is numeric, second item (LITM) is the readable part number
- Branch plant fields hold a right justified business unit string
- Dates are stored in Julian format (CYYDDD)
- Amounts and quantities carry implied decimals
Joining across tables
Sales lines join to the item master through the short item number and to the item branch through short item plus branch. Purchase detail joins the same way, and receipts connect back to orders by order number, type and company. For customer and supplier names, join to the address book on the address number. Always test counts after each join, because the item branch and location tables are one to many from the item master.
Common mistakes
Developers often join on the second item number, which is not unique across all setups, instead of the short item. Others forget Julian dates, or treat padded business unit strings as trimmed. Reading production data directly can also bypass JDE security, so make sure database access follows your policy and prefer read-only accounts.
Frequently Asked Questions
What is the difference between F4211 and F4201?
F4201 is the sales order header with one row per order, and F4211 is the sales order detail with one row per line. Most reporting about quantities, prices and statuses uses F4211, joined to F4201 for header level data.
What are the short item and second item numbers?
The short item number is the internal numeric key used in joins, while the second item number is the part number users usually see. A third item field also exists. Join on the short item to avoid ambiguity.
Why do table columns all start with two letters?
JDE prefixes every data item in a table with a two letter code unique to that table, such as SD for the sales order detail. It lets the same data dictionary item appear in many tables without name clashes.
Is this list complete?
No. It covers commonly used tables only. JDE has thousands of tables, so use the data dictionary and Object Management Workbench in your own environment for the full list and your custom tables.
If your team writes the same joins again and again, Netray can put a read-only question and answer layer over your JDE tables that shows the query it ran, with ERP roles respected.
Related Tools
JD Edwards SQL Query Generator (Open Sales Order Lines)
Generates a read-only SQL Server query for open JDE sales order lines, with Julian date conversion, implied decimal handling and branch filters.
OracleJD Edwards Document Types Reference (OP, SO, OV, PV)
Searchable lookup of common JD Edwards document types such as OP, SO, OV and PV, with the module and the table that normally holds them.
OracleJD Edwards Work Order Status Codes Template
Editable worksheet to document your JD Edwards work order status codes, what each one means, who owns it and where an order can move next.
OracleJD Edwards Security Workbench Worksheet
Editable worksheet to record JD Edwards role security records by type, with the actions allowed, risk notes and approver for each.
More Oracle tools
See all- JD Edwards UBE Job Scheduling Worksheet
- JD Edwards Tools Release Upgrade Checklist
- JD Edwards Orchestrator Design Template
- JD Edwards Environment Refresh Runbook Template
- JD Edwards UBE Runtime Log Worksheet
- JD Edwards Next Numbers Capacity Worksheet
- JD Edwards Object Promotion Request Template
- JD Edwards Data Dictionary Item Request Template
- JD Edwards User Defined Code Change Log Worksheet