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). AIS data requests (opens in new tab) can return filtered queries, while the Orchestrator service endpoints (opens in new tab) execute named orchestrations. File outputs can use CSV, JSON, or XML, including larger outputs through disk caching (opens in new tab). Those are useful application-level exports, but they are not a managed, delete-aware delta feed. Replicated lakehouse ingestion therefore remains database-level work by default: mature CDC and replication tools already know 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 system code (usually two digits, though some run three or four: F03B11 is system 03B) 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 (opens in new tab), behind a My Oracle Support sign-in), and the JDELIST community thread on SQL Server CDC (opens in new tab) covers practitioner experience choosing which tables to enable it on. The edition floor matters: Microsoft’s own SQL Server 2016 SP1 release notes (opens in new tab) document CDC in Standard starting with SQL Server 2016 SP1; Enterprise and Developer are also supported, while Express is not.

On Oracle, the equivalent is reading the redo/archive logs directly: GoldenGate (opens in new tab) (separately licensed — flagged again in the licensing section below). Some ingestion vendors instead use LogMiner (opens in new tab). Check the connector’s support terms alongside Oracle’s stated diagnostic-use limits; GoldenGate has its own product and support path. Both approaches require the applicable supplemental logging for the tables you care about.

The first-party Databricks path is Lakeflow Connect. The SQL Server connector reached general availability on September 25, 2025 (opens in new tab) with built-in CDC and Change Tracking support. The Oracle connector is in Beta as of September 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 (opens in new tab), the Oracle connector FAQ (opens in new tab), and the Oracle connector setup guide (opens in new tab).

Lakeflow also offers a query-based connector (opens in new tab) for scheduled reads from Oracle or SQL Server without CDC setup. It requires exactly one monotonically increasing cursor column; a composite cursor is not supported. That is a material JDE limit: common audit timing is split between Julian UPMJ and time UPMT, so neither column alone is a safe total order for same-day updates. Use this path only where a reliable single cursor exists, engineer one at the source, or accept full loads. The API supports a soft-delete condition, and hard-delete key scans are Beta as of September 2026; configure primary keys and validate those behaviors before treating the path as delete-complete.

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 (opens in new tab)) — 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. Configured sales-update/history purging can copy eligible rows from F4211 to F42119 and remove them from current detail. That looks 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, including configured F4211 → F42119 history processing, 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 most direct transaction-grain answer when your database, license, and connector support it. 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: eligible lines removed from F4211 by configured history processing are preserved only if F42119 is loaded too. F4211 can also retain completed detail, so reconcile both tables by the full order-line key (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. Configured R42800 processing or the R42996 purge can move eligible detail to history, while completed detail can remain in F4211 when purging is disabled. Reconcile both on the full order-line key. 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 (including Standard from SQL Server 2016 SP1 onward, but not Express, 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.