ERP Table and Field Explorer

JD Edwards EnterpriseOne Table and Field Explorer

Search the core JD Edwards EnterpriseOne tables by table number, column or description, see each table's key, important columns and joins, then pick two to four tables to get an Oracle or SQL Server JOIN skeleton. Every JDE column is a two-letter table prefix plus a data dictionary alias, so the order number is SDDOCO in F4211, PDDOCO in F4311 and WADOCO in F4801.

62 tables, 490 documented fields and 110 join paths across 11 modules. Free, runs in your browser, no login needed.

1Find a table by name, description or field

Naming: Tables are named F plus the system code and a number (F42 sales, F43 purchasing, F09 general ledger). Columns are a two-letter table prefix plus the data dictionary alias: DOCO is SDDOCO in F4211 and PDDOCO in F4311. Business data sits in the environment schema (PRODDTA, CRPDTA, TESTDTA), UDCs in the control tables schema (PRODCTL) and user profiles in the system schema. There is no company column on every table: company is held in KCOO, KCO or CO columns and in the business unit (MCU).

Tables by module

2Pick tables

62 of 62

Address book, customers, suppliers

F0101

Address Book Master: one row per address number for every customer, supplier, employee, company and branch.

Also known as: Address Book, P01012, address book master

Key:ABAN8
FieldMeaning
ABAN8Address number, the key used as customer, supplier, ship-to, employee and payee number across the system.
ABALKYLong address number (alternate key).
ABTAXTax ID.
ABALPHAlpha name (the name shown on forms and reports).
ABDCDescription compressed, used for name searches.
ABMCUBusiness unit the address is assigned to.
ABAT1Search type (UDC 01/ST), for example C customer, V supplier, E employee.
ABAN81Related address 1 (one of several related address fields ABAN81 to ABAN86).
ABAC01Category code 01 (ABAC01 to ABAC30 are user defined).
ABEFTBEffective date (Julian CYYDDD).
ABUPMJDate updated (Julian CYYDDD).

Joins

  • F0101.ABAN8 = F0116.ALAN8

    Address by date: take the row with the latest ALEFTB on or before today.

  • F0101.ABAN8 = F0111.WWAN8

    Who's Who contacts; WWIDLN 0 is the main entry.

  • F0101.ABAN8 = F03012.AIAN8

    Customer master by line of business.

  • F0101.ABAN8 = F0401.A6AN8

    Supplier master.

  • F0101.ABMCU = F0006.MCMCU
  • F0101.ABAN8 = F0150.MAPA8
  • F0101.ABAN8 = F0150.MAAN8
  • F0101.ABAN8 = F4104.IVAN8
  • F0101.ABAN8 = F4201.SHAN8
  • F0101.ABAN8 = F4211.SDAN8
  • F0101.ABAN8 = F4215.XHSHAN
  • F0101.ABAN8 = F4301.PHAN8
  • F0101.ABAN8 = F4311.PDAN8
  • F0101.ABAN8 = F0411.RPAN8
  • F0101.ABAN8 = F03B11.RPAN8
  • F0101.ABAN8 = F0092.ULAN8

Name and type live here, street address lives in F0116, phones in F0115 and email in F01151. All dates in JDE are Julian CYYDDD numbers.

3Generate a JOIN skeleton (2 to 4 tables)

Starters:
Output
-- Query skeleton generated by netray.co/tools/table-explorer
-- Dialect: Oracle SQL. Review keys, filters and indexes before running on production.
-- Dates are Julian CYYDDD numbers. Oracle: TO_DATE(TO_CHAR(col + 1900000), 'YYYYDDD'). SQL Server: DATEADD(day, col % 1000 - 1, DATEADD(year, col / 1000, '1900-01-01')).
-- Amounts and quantities are stored without the decimal point. Divide by the data dictionary display decimals, or by the currency decimals for currency amounts.
-- Business unit (MCU) columns are CHAR(12) right justified: compare with LTRIM or pad the literal to 12 characters.
-- Read only. Write through business functions, Z files or Orchestrator, not with direct UPDATE statements.

SELECT
    f420.SHKCOO,
    f420.SHDOCO,
    f420.SHDCTO,
    f420.SHAN8,
    f420.SHSHAN,
    f421.SDKCOO,
    f421.SDDOCO,
    f421.SDDCTO,
    f421.SDLNID,
    f421.SDAN8,
    f010.ABAN8,
    f010.ABALKY,
    f010.ABTAX,
    f010.ABALPH,
    f010.ABDC,
    f410.IMITM,
    f410.IMLITM,
    f410.IMAITM,
    f410.IMDSC1,
    f410.IMDSC2
FROM PRODDTA.F4201 f420
LEFT JOIN PRODDTA.F4211 f421
    ON f420.SHKCOO = f421.SDKCOO
   AND f420.SHDOCO = f421.SDDOCO
   AND f420.SHDCTO = f421.SDDCTO
-- F4201 to F0101: Sold-to.
LEFT JOIN PRODDTA.F0101 f010
    ON f420.SHAN8 = f010.ABAN8
LEFT JOIN PRODDTA.F4101 f410
    ON f421.SDITM = f410.IMITM
ORDER BY f420.SHKCOO, f420.SHDOCO, f420.SHDCTO
FETCH FIRST 100 ROWS ONLY;
Free, optional. Reopen it on any device.
Download:

How to use it

  1. 1

    Search

    Type a table number such as F4211, a column such as SDDOCO, a data dictionary alias such as DOCO, or a business word such as voucher. Field matches are highlighted under each table.

  2. 2

    Filter and inspect

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

  3. 3

    Build the join

    Add two to four tables with the plus button or a starter. Pick Oracle or SQL Server and set the schema (PRODDTA, CRPDTA, TESTDTA) to match the environment you query.

  4. 4

    Keep it

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

Questions about JD Edwards EnterpriseOne tables

How are JD Edwards column names built?

Every column is the table's two-letter prefix plus a data dictionary alias of up to eight characters. The alias DOCO (order number) becomes SDDOCO in F4211, SHDOCO in F4201, PDDOCO in F4311 and WADOCO in F4801. Search the explorer by the alias alone, for example AN8 or ITM, to see every table that carries it. A few prefixes are reused: F0411 and F03B11 both use RP, and F0116 and F4074 both use AL, so always qualify columns with a table alias.

How do I convert JDE Julian dates in SQL?

JDE stores dates as a number in CYYDDD form, where C is centuries since 1900, YY the year and DDD the day of the year: 126295 is 22 October 2026. In Oracle use TO_DATE(TO_CHAR(SDTRDJ + 1900000), 'YYYYDDD'). In SQL Server use DATEADD(day, SDTRDJ % 1000 - 1, DATEADD(year, SDTRDJ / 1000, '1900-01-01')). A value of 0 means no date, so wrap the conversion in a CASE.

Why are amounts in F0911 or F4211 a hundred times too big?

JDE stores numbers without the decimal point and applies the decimals at display time. Currency amounts such as GLAA or SDAEXP use the decimals of the transaction currency (2 for USD), and other numbers use the display decimals set for the alias in the data dictionary, for example unit prices often carry 4. Divide in your query, for example GLAA / 100 for a USD ledger.

Which tables hold sales orders and invoices?

F4201 is the order header and F4211 the order detail, keyed by order company, order number and order type (plus line number in F4211). Closed lines are moved to F42119 and headers to F42019 when you purge, so sales history queries need both. After Sales Update (R42800), the invoice is in F03B11 and the order line carries it in SDDOC, SDDCT and SDKCO.

Where is on hand inventory stored?

F41021 (Item Location) holds on hand, hard committed, soft committed, on PO and backorder quantities by item, branch, location and lot. F4102 holds the branch settings, F4105 the unit costs by cost method, and F4111 every movement that changed the balance. Join on the short item number (ITM), not the second item number (LITM).

Why does WHERE SDMCU = '30' return nothing?

Business unit columns (MCU, MMCU, EMCU) are 12 characters, right justified with leading spaces, so the value is stored as ten spaces followed by 30. Compare with LTRIM(SDMCU) = '30' or pad the literal to 12 characters. Padding keeps the index usable; LTRIM on the column does not. UDC values in F0005.DRKY are right justified in the same way.

Which schema should I query?

Each environment has its own business data schema: PRODDTA for production, CRPDTA for the conference room pilot and TESTDTA for test in a standard install. UDCs (F0004, F0005) sit in the control tables schema such as PRODCTL, and user profiles (F0092) in the system schema. On IBM i the schemas are libraries. Change the schema field in the query builder to match.

Is it safe to update JD Edwards tables with SQL?

Read with SQL, write through the application. Inserts and updates through business functions, Z file batch import programs or Orchestrator keep next numbers, ledgers, commitments and audit columns consistent, which direct UPDATE statements skip. Integrity reports such as the F03B11 to F0911 checks exist because ledgers drift when tables are changed outside the application.

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.