SAP S/4HANA Production Order Tables: AUFK, AFKO, AFPO, AFVC, RESB and Join SQL

-- AUFK : order master (all order categories). Key AUFNR. AUTYP = '10' is a production order.
-- AFKO : order header for PP (dates GSTRP/GLTRP, order quantity GAMNG, AUFPL routing link, RSNUM reservation).
-- AFPO : order item (MATNR, PSMNG planned qty, WEMNG delivered qty, MEINS, DWERK).
-- AFVC : operations (key AUFPL + APLZL). AFVV holds operation quantities and dates, same key.
-- RESB : components / reservation items (RSNUM + RSPOS). RESB-AUFNR also holds the order number.
-- JEST : status per object. AUFK-OBJNR links to JEST-OBJNR. Active status has INACT = ' '.

SELECT k.AUFNR, k.AUART, k.WERKS, p.MATNR, p.PSMNG, p.WEMNG, p.MEINS,
       o.GSTRP AS BASIC_START, o.GLTRP AS BASIC_FINISH, k.OBJNR
FROM AUFK k
JOIN AFKO o ON o.MANDT = k.MANDT AND o.AUFNR = k.AUFNR
JOIN AFPO p ON p.MANDT = o.MANDT AND p.AUFNR = o.AUFNR
WHERE k.MANDT = '100'
  AND k.AUTYP = '10'
  AND k.WERKS = '1000'
  AND k.AUART = 'PP01'
  AND o.GSTRP BETWEEN '20260101' AND '20261231'
  -- exclude deletion-flagged orders (I0013) and closed orders (I0046)
  AND NOT EXISTS (
    SELECT 1 FROM JEST j
    WHERE j.MANDT = k.MANDT AND j.OBJNR = k.OBJNR
      AND j.STAT IN ('I0013', 'I0046') AND j.INACT = ' ')
ORDER BY o.GSTRP, k.AUFNR;

SELECT k.AUFNR, v.VORNR AS OPERATION, v.ARBID, v.STEUS AS CONTROL_KEY,
       v.LTXA1 AS OP_TEXT, w.MGVRG AS OP_QTY, w.VGW01 AS STANDARD_VALUE_1
FROM AUFK k
JOIN AFKO o ON o.MANDT = k.MANDT AND o.AUFNR = k.AUFNR
JOIN AFVC v ON v.MANDT = o.MANDT AND v.AUFPL = o.AUFPL
JOIN AFVV w ON w.MANDT = v.MANDT AND w.AUFPL = v.AUFPL AND w.APLZL = v.APLZL
WHERE k.MANDT = '100'
  AND k.AUTYP = '10'
  AND k.WERKS = '1000'
  AND k.AUART = 'PP01'
  AND o.GSTRP BETWEEN '20260101' AND '20261231'
ORDER BY k.AUFNR, v.VORNR;

SELECT k.AUFNR, r.RSPOS, r.MATNR AS COMPONENT, r.BDMNG AS REQ_QTY,
       r.ENMNG AS WITHDRAWN_QTY, r.MEINS, r.LGORT, r.VORNR AS OPERATION
FROM AUFK k
JOIN AFKO o ON o.MANDT = k.MANDT AND o.AUFNR = k.AUFNR
JOIN RESB r ON r.MANDT = o.MANDT AND r.RSNUM = o.RSNUM
WHERE k.MANDT = '100'
  AND k.AUTYP = '10'
  AND k.WERKS = '1000'
  AND r.XLOEK = ' '
  AND r.MATNR = 'FG-10045'
ORDER BY k.AUFNR, r.RSPOS;