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
New to JD Edwards EnterpriseOne? Start with the JD Edwards EnterpriseOne guide and toolkit, or plan with Training Path Builder, Upgrade Path Planner, ERP Table and Field Explorer.
Keep exploring
Free tools
- JD Edwards EnterpriseOne Core Table ReferenceSearchable list of the JD Edwards EnterpriseOne tables you query most often, with column prefix and the key fields to filter on.
- JD Edwards World Key File Reference TableA searchable lookup of widely used JD Edwards World files with the module, purpose and key field prefix, useful for queries, reports and migration mapping.
- JD Edwards System Codes ReferenceLook up what each JD Edwards system code stands for and how it shows up in table, application and UDC names.
ERP fixes and how-tos
- How to Add a Private AI Layer to JD Edwards EnterpriseOne On-PremAdd a private AI layer to JD Edwards EnterpriseOne: AIS data service, Orchestrator, Security Workbench mapping, a local model and human approval.
- How to Add a Private AI Layer to JD Edwards World on IBM iAdd AI to JD Edwards World without moving data: DB2 for i access, journal-based replication, rebuilt row security, local GPUs and approved batch write-back.
- JD Edwards table conversion: how it works and how to run oneWhat a JD Edwards table conversion does to a JDE table's structure and data, when it runs in a package build, and how to run or design one manually.
Runs with your ERP
- Vertex Tax Connectors with JD Edwards EnterpriseOneHow Vertex calculates sales, use and VAT tax for JD Edwards EnterpriseOne and World, plus Oracle EBS and Cloud ERP: integration model, flows, setup, pitfalls.
- Oracle CPQ and JD Edwards EnterpriseOne: quote to sales orderHow Oracle CPQ integrates with JD Edwards EnterpriseOne: customer data into quotes, sales orders created from quotes, and status back, with setup and pitfalls.
- Cleo Integration Cloud with JD Edwards: EDI Connector and Migration PathCleo Integration Cloud has a prebuilt JD Edwards EnterpriseOne connector for EDI. See data flows, setup steps, pitfalls and Oracle Fusion migration notes.
AI on your ERP
- AI for JD Edwards EnterpriseOne, Built on Orchestrator and AISAdd AI to JD Edwards E1 9.2 using Orchestrator, AIS, and BSSV rather than screen-scraping. Ground a private LLM on F4211, F4111, and F0411 data.
- AI for JD Edwards EnterpriseOne Procurement, From Requisition to VoucherPrivate AI for JD Edwards EnterpriseOne procurement: F4301 and F4311 purchase orders, receipts and voucher match, via AIS and Orchestrator, on-prem ready.
Integrations
- How to add AI agents to JD Edwards EnterpriseOneHow to add AI agents to JD Edwards EnterpriseOne: Orchestrator and AIS as the tool layer, read-only first, human approval on writes, on-prem models.
- How to use ChatGPT with JD Edwards EnterpriseOneHow to use ChatGPT with JD Edwards EnterpriseOne: custom GPT actions over AIS, exports versus live access, enterprise data controls, and private alternatives.