SAP production order data in the lakehouse: AFKO, AFRU, and the operation key

Table set. Order statuses do not live on the order tables. They live in JEST — one row per status ever set on an object, keyed by object number (OBJNR) and status code (STAT), with INACT flagging rows that no longer apply. The OBJNR comes from AUFK (the order master): for production orders it is the constant OR followed by the order number — for every AUFK order category, not just production; restrict via AFKO before counting. Status texts decode via TJ02T (system statuses), which is not in this reference.

The trap. JEST is a history, not a state: released, confirmed, technically-completed, closed all coexist as rows, and inactive rows stay in the table with INACT set. Joining JEST without filtering INACT and without picking specific status codes multiplies your order rows by the number of statuses each order ever had. Filter INACT <> 'X' and select the statuses you actually ask about.

Grain.One row per order per active status — an order legitimately has several at once. "Orders by status" is only well-defined for a chosen status set: the query below reads "open" as released and not technically completed, which still counts orders carrying the deletion-flag and closed statuses — two more JEST codes to exclude if your definition of open excludes them.

-- Open production orders: released but not technically completed
-- Status codes: I0002 = released (REL), I0045 = technically completed (TECO) — confirm against your status profile.
WITH order_status AS (
  SELECT
    a.aufnr,
    a.werks,
    max(CASE WHEN j.stat = 'I0002' THEN 1 ELSE 0 END) AS is_released,
    max(CASE WHEN j.stat = 'I0045' THEN 1 ELSE 0 END) AS is_teco
  FROM bronze_sap.aufk AS a
  JOIN bronze_sap.afko AS k
    ON  k.mandt = a.mandt
    AND k.aufnr = a.aufnr   -- AFKO restricts to production/process orders; AUFK alone holds every order type (internal, maintenance, ...)
  JOIN bronze_sap.jest AS j
    ON  j.mandt = a.mandt
    AND j.objnr = a.objnr
    AND coalesce(j.inact, '') <> 'X'
  WHERE a.mandt = '100'
  GROUP BY a.aufnr, a.werks
)
SELECT
  o.werks                      AS plant,
  count(*)                     AS open_orders
FROM order_status AS o
WHERE o.is_released = 1
  AND o.is_teco     = 0
GROUP BY o.werks
ORDER BY plant;

Table pages: AUFK, JEST, JSTO →

Table set. AFPO, the order item: PSMNG is the order item quantity (the plan), WEMNG the delivered quantity (what goods receipts actually posted to stock). AFKO, the header, adds the schedule (GSTRP/GLTRP scheduled start/finish) and IGMNG, the confirmed yield. Delivered and confirmed are different facts: IGMNG is what operators reported at confirmation, WEMNGis what reached stock — backflush timing, rework, and scrap decisions make them diverge, and which one is "production output" is a metric definition your gold layer must pick deliberately.

The trap. An order is not one row: co-products and by-products give one order several AFPO items. Completion analysis at AUFNR grain silently keeps whichever item your join found first; the grain is order item (AUFNR + POSNR).

-- Order completion: planned vs delivered, one row per order item
SELECT
  p.aufnr                                       AS order_number,
  p.posnr                                       AS order_item,
  p.matnr                                       AS material,
  k.gstrp                                       AS scheduled_start_raw, -- DATS string; '00000000' until scheduled — cast in silver
  p.psmng                                       AS planned_qty,
  p.wemng                                       AS delivered_qty,
  round(100.0 * p.wemng / p.psmng, 1)           AS completion_pct,
  CASE
    WHEN p.wemng >= p.psmng THEN 'complete or over'
    WHEN p.wemng > 0        THEN 'partial'
    ELSE                         'nothing delivered'
  END                                           AS delivery_state
FROM bronze_sap.afpo AS p
JOIN bronze_sap.afko AS k
  ON  k.mandt = p.mandt
  AND k.aufnr = p.aufnr
WHERE p.mandt  = '100'
  AND p.psmng  > 0             -- guards the division; also drops zero-plan items
ORDER BY order_number, order_item;

Table pages: AFKO, AFPO→

Table set — and the key that is not AUFNR. Operations live in AFVC, keyed by AUFPL (the order's routing number) and APLZL (the operation counter) — AFVC has no order-number key. The path is AFKO.AUFPL → AFVC.AUFPL. Confirmations live in AFRU, keyed by RUECK + RMZHL (confirmation number + counter), carrying AUFNR, AUFPL, and APLZL as links, with LMNGA (yield) and XMNGA (scrap) as the quantities. Joining AFVC straight to AFPO or AFRU on order number alone either fails or fans out — route through AUFPL/APLZL.

The trap. Confirmations get cancelled, and the cancellation is a new AFRU row, not an update: the original gets STOKZ (reversed indicator) set, and the cancelling record carries STZHL pointing at the counter it cancels. Sum LMNGA without excluding both sides and every corrected confirmation counts its yield twice (or three times, after a re-confirmation). Filter both the reversed original and the reversal record.

Grain. One row per confirmation posting. Net to operation grain (AUFPL + APLZL) before computing yield or scrap rates.

-- Net confirmed yield and scrap per order operation
-- Excludes reversed confirmations (STOKZ) and the reversal records (STZHL).
SELECT
  r.aufnr                                   AS order_number,
  r.aufpl                                   AS routing_number,
  r.aplzl                                   AS operation_counter,
  sum(r.lmnga)                              AS net_yield_qty,
  sum(r.xmnga)                              AS net_scrap_qty,
  round(100.0 * sum(r.xmnga) / nullif(sum(r.lmnga) + sum(r.xmnga), 0), 1)
                                            AS scrap_pct
FROM bronze_sap.afru AS r
WHERE r.mandt  = '100'
  AND coalesce(r.stokz, '') <> 'X'          -- not a reversed original
  AND coalesce(try_cast(r.stzhl AS BIGINT), 0) = 0  -- robust across extracts that land NUMC as string, int, or trimmed
GROUP BY r.aufnr, r.aufpl, r.aplzl
ORDER BY order_number, operation_counter;

Table pages: AFVC, AFVV, AFRU →

Table set. Order-related movements are ordinary material-document lines: MSEG (ECC) / MATDOC (S/4HANA) rows carrying AUFNR. Receipts from the order and component issues to the order are both there — separate them by direction (SHKZG) and by whether the material is the order's output (AFPO.MATNR) or a component (reservations in RESB). You do NOT need AUFM: it holds the same order movements but is only populated when the corresponding customizing flags are set, so a pipeline built on it works on one system and returns nothing on the next. A single confirmation's material document is also reachable directly: AFRU.WABLNR carries the material document number the confirmation posted.

Grain. Material-document line. The useful gold shapes are receipts-per-order-item (ties to WEMNG) and component-consumption-per-order (ties to RESB requirements).

Movement mechanics and reversal netting in bulk: the inventory guide →

Table pages: MSEG, MATDOC, RESB→

• AFKO, AFPO, AFVC, and AFRU are transparent tables, replicable as-is. No MATDOC-style replacement is documented for them in S/4HANA. No simplification-list entry restructuring these four tables shows up in published material — an absence, not an SAP statement; verify against the simplification list for your release.
• Order-related goods movements land in MATDOC on S/4HANA (the MSEG path is a compatibility view — same behavior as documented in the inventory guide).
• JEST is tall and narrow: statuses accumulate forever and rows flip INACT in place — a status change is an UPDATE, not an insert, so snapshot-diff loads miss status transitions and a delta feed must carry updates.
• AFRU is append-plus-reversal (like material movements): friendly to incremental loads, but reversals arrive as new rows against old work — recompute a trailing window.
• Confirmations are entered through CO11N; the reporting transaction COOIS reads the same tables this guide models.
• The ODP-RFC restriction (SAP Note 3255746, enforced June 2026) and SAP BDC Connect's curated-data-products model are covered in the deliveries guide's extraction section — the same constraints apply here.

• Bronze: the four order tables + JEST + movements, raw, CDC flags kept.
• Silver: one client. production_order (header+item conformed, AUFNR + POSNR grain), order_operation (AUFPL + APLZL grain, order number resolved via AFKO), order_confirmation (net-valid AFRU rows only — the reversal filter applied exactly once, here), order_status_current (JEST pivoted to one row per order with the status flags the business asks about).
• Gold: fct_production_order at order-item grain (planned, delivered, confirmed quantities; schedule dates as role-playing date keys) and fct_order_confirmation at operation-confirmation grain (net yield/scrap). Dimensions: material, plant, work center (CRHD), date roles. These serve schedule adherence, completion rate, scrap rate, and confirmed-vs-delivered reconciliation without re-deriving the reversal logic per dashboard.