The JDE Extraction Guide

Start with the defining fact of a JDE extract: EnterpriseOne's data model lives entirely in a relational back end — Oracle Database or SQL Server, with a smaller population still running on IBM i / DB2 (acknowledged here, not covered by this guide). There is no publishing framework sitting on top — no delta queue, no managed export service. That makes extraction database-level work by default, which is a simplification once you accept it, not a handicap: every mature CDC and replication tool in the market already knows how to read Oracle and SQL Server.

Where the data lives matters before how you extract it. Business data — the F-tables this reference catalogs — sits in business data libraries (conventionally PRODDTA in production), with related control tables such as UDCs and next-numbers in control libraries (PRODCTL). Separately, central objects and specification libraries hold application definitions — the compiled logic behind programs and forms — and should never be replicated for analytics; system and data-dictionary libraries round out the landscape. Table naming follows a system-code convention — F plus a two-digit system code plus a table number, so F42xx is Sales Order Management — which is exactly how the module pages in this reference are organized.

Which one fits your estate comes down to four questions: which back end you're on, how much delete tolerance your analytics can live with, how fresh the data needs to be, and what your DBA and your database license actually allow you to attach.

On SQL Server, native SQL Server Change Data Capture on JDE tables is a real, supported pattern, not a workaround — Oracle's own knowledge base documents enabling CDC on tables like F0911 and F0411 (Doc ID 2737374.1, behind a My Oracle Support sign-in), and the JDELIST community thread on SQL Server CDC covers practitioner experience choosing which tables to enable it on. The edition floor matters: Microsoft's own enable/disable CDC documentation requires Enterprise, Developer, or Standard edition from SQL Server 2016 SP1 onward.

On Oracle, the equivalent is reading the redo/archive logs directly: GoldenGate (separately licensed — flagged again in the licensing section below) or LogMiner-based tooling, either of which requires supplemental logging to be enabled on the tables you care about.

The first-party Databricks path is Lakeflow Connect. The SQL Server connector reached general availability on September 25, 2025 with built-in CDC and Change Tracking support. The Oracle connector is in Public Preview as of August 2026, LogMiner-based, covering Oracle 12c through 26ai (12c, 18c, 19c, 21c, 23ai, 26ai). Neither connector is JDE-specific — to Lakeflow Connect, JDE is just a database, which is exactly why they work. See the Lakeflow Connect documentation and the Oracle connector FAQ.

Third-party tools treat JDE the same way. Fivetran's HVR-based connectors have a documented JDE-on-SQL-Server deployment — Toll Brothers replicated 55 JDE tables, one at 80 million rows, from proof-of-concept to production in under a quarter (see Fivetran's case study) — worth naming honestly: their destination was Snowflake, not Databricks, but the JDE-side mechanics — reading SQL Server CDC — are identical for a Databricks target. Qlik Replicate, AWS DMS, and Striim belong in the same sentence: all three treat JDE as an ordinary Oracle or SQL Server source, and none of them ships a “JDE connector.”

The vendor-neutral rule for any of these: evaluate on initial-load handling, delete capture, and numeric/decimal fidelity — not logos. A tool that “helpfully” rescales a decimal amount on the way through breaks the implied-decimal contract every generated SQL snippet in this reference depends on (see the quirks guide).

When log-based CDC isn't on the table, most JDE tables carry enough of an audit trail to fake an incremental pull with nothing but SQL: UPMJ (update date, Julian) plus a time column, and JOBN / USER / PID under table-specific prefixes. WHERE UPMJ >= <watermark> is the whole pattern — see the audit-columns and Julian-dates sections of the quirks guide for the exact conversion.

Be honest with yourself about what this buys you, though. Not every program or custom writer updates audit columns reliably — rows can change without the watermark moving. There is no delete capture, period — that alone is the subject of the next section. Sub-day watermarking has its own quirk: UPMJ is date-grain only, and the intra-day time lives in a separate column whose name depends on the table — TDAY on distribution and manufacturing tables, UPMT on financial and foundation tables (the quirks guide documents the split) — so a sub-day pull combines two differently named columns per table, not one universal pair. Pure constants tables such as F0002 and F40205 have no audit columns at all. And batch jobs that physically relocate rows — sales update (R42800) moving lines from F4211 to F42119— look like a delete in one table and an insert in the other, which an audit-column pull on either table alone won't reconcile.

The verdict: acceptable for append-mostly tables and daily batch cadences. Pair it with periodic reconciliation, or don't trust the counts.

The JDE reality: there is generally no soft-delete flag. Physical deletes are the norm — canceled order lines, voided documents, purge jobs, and the F4211 → F42119 move at sales update all remove rows outright. A bronze layer fed only by audit-column pulls keeps counting documents JDE no longer has, and every downstream sum inherits the drift.

Three strategies, ranked. First, log-based CDC — the only complete answer, covered above. Second, periodic full-key reconciliation — extract only the primary-key columns (cheap, even at scale), anti-join against bronze, and delete the orphans. Third, scheduled full reloads for small masters and control tables — anything at the scale of F0002 or F0006.

-- Periodic key-reconciliation delete. Run on a slower cadence than your
-- incremental load: extract only the primary-key columns from the source
-- (cheap, even for a large table), stage them, then anti-join against bronze
-- and delete whatever's left. Lines that left F4211 at sales update (R42800)
-- are correctly deleted here as long as F42119 is loaded too — neither table
-- alone is "all sales" (see the quirks guide's sales-history section).
DELETE FROM <catalog>.<schema>.F4211 AS tgt
WHERE NOT EXISTS (
  SELECT 1 FROM <catalog>.<staging_schema>.F4211_KEYS AS src
  WHERE src.SDKCOO = tgt.SDKCOO AND src.SDDOCO = tgt.SDDOCO
    AND src.SDDCTO = tgt.SDDCTO AND src.SDLNID = tgt.SDLNID
)

One caveat worth internalizing before you run this: lines that left F4211 at sales update are correctly deleted by this pattern only if F42119is loaded too — neither table alone is “all sales,” the same rule the quirks guide states for reporting (see sales history).

Whichever extraction path you use, the landing convention is the same: point a Unity Catalog external location at the container your extracts arrive in, register the landed data, and expose it as a bronze schema — the raw, source-aligned layer you build silver and gold on. The <catalog>.<schema> placeholders in every boilerplate snippet on this site are meant to point at exactly that bronze layer.

Keep bronze byte-faithful — raw CYYDDD Julian dates, unscaled implied-decimal amounts, padded fixed-width strings — and do the Julian and implied-decimal conversions in silver, not bronze. Two reasons: it preserves replay and audit fidelity back to the source encoding, and — the reason specific to this reference — every generated boilerplate query on this site assumes raw JDE encodings; converting in bronze would break the site's own SQL. See Julian dates and implied decimals in the quirks guide for the exact expressions.

The safe incremental load is the same two steps as any CDC pipeline: dedup each incoming batch to one row per key, keeping the latest change by your feed's watermark, then MERGE into bronze, turning the delete signal into a physical DELETE. What that delete-signal column is called depends on the path: SQL Server CDC carries __$operation, most third-party tools write their own _operation / _extracted_at columns, and audit-column pulls have no delete signal at all — see the delete-capture section above for that gap. The skeleton below targets F4211 keyed on SDKCOO, SDDOCO, SDDCTO, SDLNID.

-- Incremental upsert from a staged extraction batch into a bronze Delta table.
-- The watermark and delete-signal columns depend on your extraction path:
-- SQL Server CDC carries __$operation, most third-party tools write their own
-- _operation / _extracted_at columns, and audit-column pulls have no delete
-- signal at all (see the delete-capture section) — substitute yours for the
-- placeholders below. Dedup on the order-line key keeping the latest row,
-- then MERGE — turning the delete signal into a physical DELETE so removed
-- lines actually leave bronze.
MERGE INTO <catalog>.<schema>.F4211 AS tgt
USING (
  SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (
      PARTITION BY SDKCOO, SDDOCO, SDDCTO, SDLNID
      ORDER BY <watermark_column> DESC
    ) AS rn
    FROM <catalog>.<staging_schema>.F4211
  ) WHERE rn = 1
) AS src
ON  tgt.SDKCOO = src.SDKCOO
AND tgt.SDDOCO = src.SDDOCO
AND tgt.SDDCTO = src.SDDCTO
AND tgt.SDLNID = src.SDLNID
WHEN MATCHED AND src.<delete_flag> = true  THEN DELETE
WHEN MATCHED AND src.<delete_flag> = false THEN UPDATE SET *
WHEN NOT MATCHED AND src.<delete_flag> = false THEN INSERT *

Per-table traps worth knowing before your first load: capture both F4211 and F42119 together — the R42800 sales-update move arrives as a delete in one and an insert in the other, so a pipeline watching only one table silently loses shipped lines. F0911, the general ledger detail, is append-heavy and high-volume — size your watermarking for it accordingly. F0411 (accounts payable) and F4111 (the item ledger / Cardex, also high-volume) follow the same incremental pattern. F4101 and F0101 are masters — small enough that a scheduled full reload is often simpler than incremental CDC. And the display-decimals table, F9210, isn't in this reference's browsable table catalog — treat it as a lookup you query directly against source, not a table you'll find a page for here.

One JDE instance hosts multiple environments — PD (production), PY (prototype), DV (development), and others — each pairing a path code with its own data sources. Production business data typically lives in PRODDTA / PRODCTL; PY points at its own libraries entirely. What you want to replicate is production's libraries, not the environment name you happen to log into — the two aren't the same thing, and assuming they are is a common way to point an extraction pipeline at test data.

Object Configuration Manager (OCM)is the mapping layer that decides which data source an environment actually reads a given table from. Before you point CDC or any replication tool at a schema, verify through OCM — or your CNC administrator — where the table physically resides. This guide doesn't cover OCM internals; it's enough to know the mapping exists and that skipping the check is how pipelines end up reading the wrong library.

Three things to flag — flagged, not advised. First, many JDE estates run the underlying database under an application-specific or restricted-use license— Oracle Application Specific Full Use (ASFU) or a SQL Server runtime edition — that permits only the JDE application itself to use that database. Attaching third-party replication or CDC tooling is a license question before it's a technical one. Second, GoldenGate is separately licensed, and SQL Server CDC has its own edition requirements (Enterprise, Developer, or Standard 2016 SP1+, as noted above) — don't assume either is bundled. Third, database-level CDC needs DBA cooperation and change control — log retention, supplemental logging, and CDC cleanup jobs are ongoing operational commitments, not one-time setup.

The rule this guide actually endorses: involve whoever owns your JDE and database contracts before standing up a new extraction path. This page documents mechanics; it is not legal or licensing advice.