The JDE Quirks Guide

JDE stores dates as a six-digit number, CYYDDD: C is the century offset from 1900, YY is the year within that century, and DDD is the day of the year. So 126203 is 1900 + 126 = 2026, day 203 — 2026-07-22. A raw 0means “no date” and must become NULL, not 1899-12-31.

Convert on the way out with the expression below. For filtering, don't convert every row — turn your date bounds into CYYDDD once and compare against the raw column, which keeps the predicate index-friendly.

Numeric amount and quantity columns store no decimal point. The display-decimals setting in F9210 tells you where it belongs, and it varies by column: unit prices commonly carry four implied decimals, extended amounts two, whole-unit quantities none.

The trap: 100000 in a two-decimal amount column is 1,000.00, not one hundred thousand. Scale by POWER(10, n), and always verify n for a column in F9210 before you trust a raw figure — a wrong assumption is off by orders of magnitude.

JDE predates widespread nullable columns, so it doesn't use NULL. Character fields hold blanks; numeric fields hold zeros. Both usually mean “nothing here,” but they aggregate differently than NULL — a blank still counts, a zero still averages in.

Normalize at read time with NULLIF: collapse blank strings and sentinel zeros to NULL so counts, averages, and distinct-value logic behave the way analysts expect.

The business unit / branch plant field MCU is a twelve-character field, right-justifiedand space-padded. So business unit “30” is stored as ten spaces followed by “30”.

This is the single most common silent join-killer in JDE analytics: join MCU to MCU without trimming and the padding mismatch drops rows with no error. TRIM both sides of every business-unit join, and expect the same padding on other right-justified keys.

Every item has three numbers. ITM is the short, numeric internal key that transaction tables join on. LITM is the long, user-facing item code people actually recognize. AITM is the third number — a catalog or cross-reference code.

The gotcha is joining on the wrong one. Transaction tables like F4211 and the item ledger carry all three, but the reliable join to the item master F4101 is on the short number ITM. Display LITM; join on ITM.

User Defined Codes are JDE's lookup mechanism. A coded column — order type, line type, a category code — stores only the code; its meaning lives in F0005, keyed by a composite of system (DRSY), type (DRRT), and the code value itself (DRKY). F0004 defines each code type; F0005 holds the values you join to.

Two catches: DRKY is right-justified like other JDE keys, so TRIM it before comparing; and the system/type pair is specific to each field. Every column flagged UDC in this reference carries its own system and type — drop them into the two constants in the join and the decode is done.

Most JDE tables end with the same audit trail, under a table-specific prefix but stable data-dictionary aliases: USER (who), PID (which program), JOBN (which job), UPMJ (update date, in Julian), and an update-time column that is TDAY on distribution and manufacturing tables and UPMT across the financial and foundation tables. A few tables — pure constants files like F0002 and F40205 — carry no audit columns at all, so check the column list before you filter on them. The generated boilerplate SQL in this reference omits these columns by default to keep SELECTs readable, and the curated field lists leave them out too unless one is part of the key — so RPUPMT, for instance, won't show up on the F0411 field table even though the column is there in your system.

They're more than metadata, though. PIDis a free lineage marker: rows written by a given program carry that program's code, so filtering on PID isolates exactly what a batch job produced — invaluable for tracing where a number came from.

F4211 is current sales detail and can retain completed lines. Processing options on R42800 control whether eligible detail is copied to F42119 and purged; a separate R42996 schedule can purge eligible status-999 detail. The history file shares the F4211 layout column-for-column.

The consequence for analytics: neither table alone is guaranteed to be complete. Query F4211 UNION ALL F42119, reconcile the full company/type/order/line key, and check the site's processing options before interpreting either side as open or closed.

DOCO comes from a per-system next-number counter (see the next-numbers quirk above), and those counters are shared across companies and document types. The result: the same DOCO value shows up on a sales order, a purchase order, and a voucher — sometimes more than once. The real key of a JDE document is the trio KCOO (order key company) + DCTO (order type) + DOCO, plus LNID (line number) at detail grain, and SFXO (order suffix) on procurement documents split across partial receipts.

Joining on DOCO alone fans out silently — no error, just duplicated or cross-matched rows. Financial tables carry the same trio under shorter aliases: KCO / DCT / DOC, plus the pay item SFX. The relationship clauses rendered elsewhere in this reference join on the DOCO leg only — for production SQL, add the company and type legs yourself.

Interface staging tables come in two flavors. Most carry a trailing “Z” suffix — the Z-file pattern — such as F4211Z1, F4101Z1, or F0411Z1. The system-47 EDI files (F47011, F47012, and the rest) are the same idea under a different naming convention. Both stage rows moving in or out of JDE: inbound documents awaiting processing, outbound documents awaiting transmission.

These are not the system of record. Their rows are duplicated, partial, or transient, and they clear as processing completes. Treating a Z file as a fact source double-counts or invents transactions — read the base table instead.

If a table name in your extract starts F55, F56, F57, F58, or F59, it is not a JD Edwards table and you will not find it in any reference — including this one. Oracle reserves product codes 55–59 for clients (opens in new tab) and 60–69 for custom development, so those tables were built by whoever implemented your system, for your installation alone.

You can read the ownership straight off the name: a JDE table name is F + the system code + a group digit + a version digit, so the characters after the F are the system code — usually two digits, but some codes run three or four (F03B11 is system 03B, Enhanced Accounts Receivable). F4211is system 42, Sales Order Management — Oracle's. F5501 is system 55 — yours. The same reading applies to programs and reports: an R55 batch job or a P56 application is site-built too.

Two custom tables with the same name at two different companies have nothing in common, so their structure, contents, and joins can't be documented generically. What can be documented is your own: the object librarian tables F9860 (object master) and F9861 (object detail) hold the description and deployment of every object in your environment, custom ones included. Extract them alongside the business data and you have a catalog of the custom estate that no vendor could have written for you.

Document numbers (DOCO and friends) come from the next-number counters in F0002, kept per system code. When a transaction starts, it reserves the next number and increments the counter.

Because reserved numbers aren't always committed — an order entry is abandoned, a batch rolls back — gaps in a document-number sequence are normal. Never assume DOCO values are contiguous, and never infer a transaction count from the span between the smallest and largest number.