The SAP EWM Quirks Guide

-- EWM's stock, handling-unit and delivery layers key on GUIDs stored as
-- RAW(16). A raw column prints as unreadable bytes, so SELECT the hex
-- rendering ALONGSIDE it - never instead of it.
SELECT
  t.GUID_STOCK,
  lower(hex(t.GUID_STOCK))  AS guid_stock_hex,   -- display only
  t.GUID_PARENT,
  t.LGNUM,
  t.LGPLA,
  t.MATID,
  t.QUAN,
  t.UNIT
FROM <catalog>.<schema>.scwm_aqua t
WHERE t.MANDT = '<client>'
  AND t.LGNUM = '<LGNUM>';

-- Joins run on the RAW columns. Comparing hex strings works only if BOTH
-- sides were rendered the same way, and it costs you the index every time.
SELECT
  a.LGPLA,
  a.QUAN,
  q.WDATU AS goods_receipt_date
FROM <catalog>.<schema>.scwm_aqua a
JOIN <catalog>.<schema>.scwm_quan q
  ON  q.MANDT       = a.MANDT
  AND q.GUID_PARENT = a.GUID_PARENT         -- both tables key it; 1:1
  AND q.GUID_STOCK  = a.GUID_STOCK          -- raw = raw
WHERE a.MANDT = '<client>';

-- Filtering by a GUID you have as text: convert the text, don't render the
-- column.
SELECT *
FROM <catalog>.<schema>.scwm_huhdr t
WHERE t.MANDT   = '<client>'
  AND t.GUID_HU = unhex('<GUID_HU>');

-- THE ENCODING TRAP. /SCWM tables carry the product GUID as RAW(16); the
-- product master keys it as CHAR(22) - the same value in its compressed form.
-- MATID = MATID is NOT true across that boundary:
--   /SCWM/ORDIM_O.MATID   RAW(16)
--   /SAPAPO/MATKEY.MATID  CHAR(22)
-- Convert once, in silver, and join on the conformed column. /SCMB/TOENTITY
-- is the public evidence for the pairing: it carries SCUGUID (RAW16) and
-- SCUGUID22 (CHAR22) on the same row, for the same supply chain unit.

-- Confirming a warehouse task removes the row from the open table and writes
-- it to the confirmed table. Neither table is the history: a query against
-- open tasks alone reports a backlog, a query against confirmed tasks alone
-- misses everything still to be done.
SELECT
  o.LGNUM,
  o.TANUM,
  CAST(NULL AS STRING) AS TAPOS,      -- the open table has no task-item key
  o.WHO,
  o.PROCTY,
  o.QUEUE,
  o.VLPLA,
  o.NLPLA,
  o.VSOLM               AS qty,        -- target quantity
  o.MEINS,
  o.CREATED_AT,
  CAST(NULL AS DECIMAL(15,0)) AS CONFIRMED_AT,
  'OPEN'                AS task_state
FROM <catalog>.<schema>.scwm_ordim_o o
WHERE o.MANDT = '<client>'

UNION ALL

SELECT
  c.LGNUM,
  c.TANUM,
  c.TAPOS,
  c.WHO,
  c.PROCTY,
  c.QUEUE,
  c.VLPLA,
  c.NLPLA,
  c.NISTM               AS qty,        -- actual quantity
  c.MEINS,
  c.CREATED_AT,
  c.CONFIRMED_AT,
  'CONFIRMED'           AS task_state
FROM <catalog>.<schema>.scwm_ordim_c c
WHERE c.MANDT = '<client>';

-- Two things the union alone still misses:
--   1. The confirmed table has ONE MORE key column than the open table
--      (TAPOS, the task item) - the NULL above is deliberate, not laziness.
--   2. Tasks that were CANCELLED never reach the confirmed table at all.
--      They land in the task log (/SCWM/ORDIM_L) with a status saying how
--      they ended - count created-vs-confirmed without it and cancellations
--      quietly become "still open".
--
-- And the consequence for extraction: a confirmed row leaves the open table,
-- so a log-based CDC feed sees a HARD DELETE on it. A watermark pull on
-- CREATED_AT will never learn that the row went.

-- EWM splits one stock item across three tables, and they answer three
-- different questions:
--   /LIME/NQUAN  quantities, hanging on a node of the location/HU tree
--   /SCWM/QUAN   attributes of the stock item (dates, weights, origin)
--   /SCWM/AQUA   available quants, with the identity columns resolved onto
--                one row - product, stock type, batch, owner, entitled, bin
--
-- Start here for almost every analytic question:
SELECT
  a.LGNUM,
  a.LGTYP,
  a.LGPLA,
  a.HUIDENT,
  a.MATID,
  a.CAT           AS stock_type,
  a.CHARG         AS batch,
  a.OWNER,
  a.ENTITLED,
  sum(a.QUAN)     AS qty,
  a.UNIT
FROM <catalog>.<schema>.scwm_aqua a
WHERE a.MANDT = '<client>'
  AND a.LGNUM = '<LGNUM>'
GROUP BY a.LGNUM, a.LGTYP, a.LGPLA, a.HUIDENT, a.MATID,
         a.CAT, a.CHARG, a.OWNER, a.ENTITLED, a.UNIT;

-- The LIME engine's own quantity table is the physical truth, but its rows
-- hang on TREE NODES: a stock item inside a handling unit inside a bin
-- appears at more than one level. GUID_PARENT is the node the row sits on
-- (the tree itself lives in /LIME/NTREE, cataloged here too), and the
-- unit of measure is part of the key, so the same stock can carry rows in
-- several units.
SELECT
  n.GUID_STOCK,
  n.GUID_PARENT,
  n.QUAN,
  n.UNIT
FROM <catalog>.<schema>.lime_nquan n
WHERE n.MANDT = '<client>';
-- SUM(QUAN) over that without resolving the tree double-counts nested HUs.

-- And AQUA is AVAILABLE, not on-hand: stock already committed to open
-- warehouse tasks is excluded, so it will not tie out to the LIME
-- quantities row for row. Say which one your number is before you publish it.

-- The /SCDL layer is shared. Inbound and outbound, request documents and
-- processing documents all live in the same physical tables, discriminated
-- by DOCCAT:
--   IDR  inbound delivery request (the notification)
--   ODR  outbound delivery request
--   PDI  inbound delivery
--   PDO  outbound delivery order   <- what EWM picks against
--   FDO  final outbound delivery   (after goods issue)
--
-- The status table is the clearest case - it serves EVERY category AND both
-- grains, so it needs three filters, not one:
SELECT
  s.DOCID,
  s.ITEMID,
  s.STATUS_TYPE,
  s.STATUS_VALUE
FROM <catalog>.<schema>.scdl_db_status s
WHERE s.MANDT       = '<client>'
  AND s.DOCCAT      = 'PDO'          -- one category
  AND s.STATUS_TYPE = '<STATUS_TYPE>' -- one status type, or it fans out
  AND s.ITEMID     <> unhex(repeat('0', 32));
  -- item-level rows only. An initial RAW16 GUID is sixteen ZERO BYTES, not
  -- NULL and not an empty string, so <> '' filters nothing out.

-- Reference documents behave the same way: one EWM document can carry an ERP
-- delivery, a purchase order, a sales order and an ASN, so REFDOCCAT decides
-- which row you meant.
SELECT
  r.DOCID,
  r.REFDOCCAT,
  r.REFBSKEY   AS source_system,
  r.REFDOCNO,
  r.REFITEMNO
FROM <catalog>.<schema>.scdl_db_refdoc r
WHERE r.MANDT     = '<client>'
  AND r.REFDOCCAT = '<REFDOCCAT>';

-- The same discriminator routes the warehouse-task join. RDOCCAT on a task
-- says whether RDOCID/RITMID point at the inbound delivery item table or the
-- outbound one - this reference draws BOTH edges rather than picking a side:
SELECT
  o.TANUM,
  o.VLPLA,
  o.NLPLA,
  i.DOCNO   AS delivery_no,
  i.ITEMNO  AS delivery_item
FROM <catalog>.<schema>.scwm_ordim_o o
JOIN <catalog>.<schema>.scdl_db_proci_o i
  ON  i.MANDT  = o.MANDT
  AND i.DOCID  = o.RDOCID
  AND i.ITEMID = o.RITMID
WHERE o.MANDT   = '<client>'
  AND o.RDOCCAT = 'PDO';   -- without this, inbound tasks join to nothing
                           -- and you silently lose half the rows

-- EWM timestamps are not DATE or TIMESTAMP columns. They are DEC(15)
-- numbers shaped yyyymmddhhmmss, and they are stored in UTC.
SELECT
  c.LGNUM,
  c.TANUM,
  c.CREATED_AT,                                   -- e.g. 20260823141503
  to_timestamp(CAST(c.CREATED_AT AS STRING), 'yyyyMMddHHmmss')   AS created_utc,
  to_timestamp(CAST(c.CONFIRMED_AT AS STRING), 'yyyyMMddHHmmss') AS confirmed_utc
FROM <catalog>.<schema>.scwm_ordim_c c
WHERE c.MANDT = '<client>'
  AND c.CONFIRMED_AT BETWEEN <ts_from> AND <ts_to>;  -- compare NUMBERS, not
                                                     -- date literals

-- Because the value is a number, a range filter needs no conversion at all -
-- and converting the column instead of the literal throws away partition
-- pruning on a table with hundreds of millions of rows.

-- Localizing: a warehouse day does not start at 00:00 UTC. The site's time
-- zone lives on the supply chain unit (/SCMB/TOENTITY.TZONE), which is what
-- makes a shift or a same-day service measure defensible:
SELECT
  from_utc_timestamp(
    to_timestamp(CAST(c.CONFIRMED_AT AS STRING), 'yyyyMMddHHmmss'),
    e.TZONE
  ) AS confirmed_local
FROM <catalog>.<schema>.scwm_ordim_c c
JOIN <catalog>.<schema>.scwm_t300_md m
  ON  m.MANDT = c.MANDT
  AND m.LGNUM = c.LGNUM
JOIN <catalog>.<schema>.scmb_toentity e
  ON  e.MANDT   = m.MANDT
  AND e.SCUGUID = m.SCUGUID
WHERE c.MANDT = '<client>';

-- Watch for the warehouse-local TWINS: several /SCDL columns end _WH and are
-- already local time. They look identical to their UTC siblings and are not
-- interchangeable - this reference flags only the UTC columns.

-- AND THE WIDER RULE: DEC(15) does not MEAN UTC. The yard's transportation
-- unit / delivery assignment table (/SCWM/TU_DLV) carries CREATED and CHANGED
-- as DEC(15) with the same yyyymmddhhmmss shape - and their data-dictionary
-- domain is the WAREHOUSE-LOCAL timestamp domain, with no flag to say so.
-- Check the domain, not the type; this reference leaves those two columns
-- unflagged rather than emitting a "-- UTC" comment that would be wrong.
SELECT
  d.TU_NUM,
  d.CREATED,   -- warehouse-local, NOT UTC
  d.CHANGED    -- warehouse-local, NOT UTC
FROM <catalog>.<schema>.scwm_tu_dlv d
WHERE d.MANDT = '<client>';

-- The other exception runs the other way: the LIME layer keeps its own
-- long-form DEC(21) stamps, which are a different shape entirely. The
-- yyyymmddhhmmss convention above does not parse them, so none of the
-- conversions on this page apply to them.

-- Two key models coexist, and which one a table uses decides how you join it.
--
-- SEMANTIC - configuration and execution. Every non-client key column is
-- readable, and the warehouse number is part of it:
SELECT b.LGNUM, b.LGPLA, b.LGTYP, b.LGBER, b.LPTYP
FROM <catalog>.<schema>.scwm_lagp b
WHERE b.MANDT = '<client>' AND b.LGNUM = '<LGNUM>';

-- GUID - stock, handling units, deliveries. Nothing in the key is readable,
-- and readable attributes arrive by joining out:
SELECT h.GUID_HU, h.HUIDENT, h.LGNUM, h.LETYP
FROM <catalog>.<schema>.scwm_huhdr h
WHERE h.MANDT = '<client>';

-- MIXED - both, in either direction. The fixed-bin table has a product GUID
-- INSIDE an otherwise readable key:
SELECT f.LGNUM, f.LGPLA, f.ENTITLED, f.MATID, f.MATNR, f.MINQTY, f.MAXQTY
FROM <catalog>.<schema>.scwm_binmat f
WHERE f.MANDT = '<client>';
-- ...and the delivery status table has a readable status type inside an
-- otherwise GUID key. Both are badged "Mixed key" on their table pages.

-- The practical rule: on a semantic-key table, join the columns you can read
-- (and always include LGNUM). On a GUID-key table, join the GUID and pull
-- readable columns from the master it points at. On a mixed table, check
-- which side of the key each join column comes from before you trust it.

-- EWM table names are namespaced: /SCWM/LAGP, /SCDL/DB_PROCH_I,
-- /LIME/NQUAN, /SAPAPO/MATKEY, /SCMB/TOENTITY. A slash is not legal in an
-- unquoted Databricks identifier, so replication targets conventionally land
-- them with the leading slash stripped and the rest swapped for underscores:
--   /SCWM/LAGP        ->  scwm_lagp
--   /SCDL/DB_PROCH_I  ->  scdl_db_proch_i
--   /LIME/NQUAN       ->  lime_nquan
--   /SAPAPO/MATKEY    ->  sapapo_matkey
--   /SCMB/TOENTITY    ->  scmb_toentity
-- Every snippet on this site uses that form. If your landing convention
-- differs, rename once at the bronze boundary rather than per query.
SELECT b.LGNUM, b.LGPLA
FROM <catalog>.<schema>.scwm_lagp b
WHERE b.MANDT = '<client>';

-- A handful of COLUMNS carry a namespace too - the warehouse number appended
-- to the delivery item tables is the one you will meet first. Backtick-quote
-- it, and check what your replication tool renamed it to:
SELECT
  i.DOCID,
  i.ITEMID,
  i.PRODUCTNO,
  i.`/SCWM/WHNO` AS warehouse_no
FROM <catalog>.<schema>.scdl_db_proci_o i
WHERE i.MANDT = '<client>';

-- One more naming consequence: sorting a table list alphabetically sorts by
-- NAMESPACE first, which scatters related tables. Browse this reference by
-- module rather than A-Z when you are exploring rather than looking up.

-- Two name traps, both cheap to avoid and expensive to discover.
--
-- 1. LGNUM is FOUR characters in EWM. Classic WM's warehouse number is three.
--    If both systems land in one lakehouse, a fixed-width parse or a padded
--    comparison written for one silently mismatches the other - and LGNUM
--    belongs in the join, not just the WHERE clause, on every semantic-key
--    table:
SELECT
  s.LGPLA,
  s.ACT_TYPE,
  s.SRT_NR      AS pick_sequence,
  b.LGTYP
FROM <catalog>.<schema>.scwm_lagps s
JOIN <catalog>.<schema>.scwm_lagp b
  ON  b.MANDT = s.MANDT
  AND b.LGNUM = s.LGNUM     -- without this the same bin code in two
  AND b.LGPLA = s.LGPLA     -- warehouses cross-joins
WHERE s.MANDT = '<client>'
  AND s.LGNUM = '<LGNUM>'
  AND s.ACT_TYPE = '<ACT_TYPE>';   -- one bin per activity, or bins multiply

-- 2. WHO is a table AND a column. /SCWM/WHO is the warehouse-order header,
--    and its order-number column is also called WHO. An unqualified WHO in a
--    multi-table query reads as either one - alias everything:
SELECT
  w.WHO,
  w.STATUS,
  w.AREAWHO     AS activity_area,   -- NOT "AAREA" on this table
  w.QUEUE,
  w.RSRC        AS resource,
  count(*)      AS tasks
FROM <catalog>.<schema>.scwm_who w
JOIN <catalog>.<schema>.scwm_ordim_c c
  ON  c.MANDT = w.MANDT
  AND c.LGNUM = w.LGNUM
  AND c.WHO   = w.WHO
WHERE w.MANDT = '<client>'
GROUP BY w.WHO, w.STATUS, w.AREAWHO, w.QUEUE, w.RSRC;
-- The warehouse order also carries a RAW16 WHOID. It is informational: the
-- join rides LGNUM + WHO.

-- EWM separates two parties that other systems collapse into one:
--   OWNER     - who owns the stock financially
--   ENTITLED  - who is entitled to dispose of it (whose books it moves on)
-- On third-party logistics and intercompany stock they differ, and available
-- quants carry both:
SELECT
  a.LGNUM,
  a.MATID,
  a.OWNER,
  a.ENTITLED,
  sum(a.QUAN) AS qty,
  a.UNIT
FROM <catalog>.<schema>.scwm_aqua a
WHERE a.MANDT = '<client>'
  AND a.LGNUM = '<LGNUM>'
GROUP BY a.LGNUM, a.MATID, a.OWNER, a.ENTITLED, a.UNIT;

-- The entitled party is not just an attribute - it is part of the KEY on the
-- tables that parameterize stock. Fixed bins are per entitled party:
SELECT f.LGNUM, f.LGPLA, f.MATNR, f.ENTITLED, f.MINQTY, f.MAXQTY
FROM <catalog>.<schema>.scwm_binmat f
WHERE f.MANDT = '<client>'
  AND f.LGNUM = '<LGNUM>';
-- ...and so are the warehouse-product settings, which key on the product
-- GUID + supply chain unit + entitled party.

-- The encoding differs by layer, which is the trap: the /SCWM tables carry
-- ENTITLED as a readable party code, while the /SAPAPO warehouse-product
-- tables carry ENTITLED_ID as a GUID. Conform them before you join or group,
-- or one business partner appears twice and every balance splits.

-- The queue master is /SCWM/T346, keyed MANDT + LGNUM + QUEUE, with
-- /SCWM/T346T carrying the per-language description. The name you will find
-- circulating in table dumps and forum posts - /SCWM/TQUEUE - resolves on the
-- public DDIC mirrors and renders no fields at all. It is a ghost.
SELECT
  r.LGNUM,
  r.RSRC        AS resource,
  r.QUEUE,
  qt.TEXT       AS queue_text,
  q.QTYPE       AS queue_type,
  q.RFRSRC      AS operating_environment
FROM <catalog>.<schema>.scwm_rsrc r
JOIN <catalog>.<schema>.scwm_t346 q
  ON  q.MANDT = r.MANDT
  AND q.LGNUM = r.LGNUM      -- the queue code is unique PER WAREHOUSE
  AND q.QUEUE = r.QUEUE
LEFT JOIN <catalog>.<schema>.scwm_t346t qt
  ON  qt.MANDT = q.MANDT
  AND qt.LGNUM = q.LGNUM
  AND qt.QUEUE = q.QUEUE
  AND qt.SPRAS = '<language>'  -- one language, or every queue multiplies
WHERE r.MANDT = '<client>'
  AND r.LGNUM = '<LGNUM>';

-- The general test, and it costs one page load: a mirror that returns a page
-- for a table name but shows NO field dump has not confirmed the table. It
-- has confirmed that something once answered to that string. Ask for the
-- columns before you write the name into a pipeline.

-- Packaging specifications - the recipe that says how many eaches go in a
-- case and how many cases on a pallet - do not live in a "packspec" table.
-- They persist as iPPE (integrated Product and Process Engineering) objects:
--   /SCWM/PNPAKH   header, an iPPE NODE keyed by node GUID + change counter
--   /SCWM/PVPAKL   the level: what is packed in, and how much it holds
--   /SCWM/PVPAKC   the content: what is packed, and how much of it
--
-- The two variant tables are the pair you can actually join, on the variant
-- GUID AND its change counter together:
SELECT
  lower(hex(c.PVGUID)) AS variant_hex,   -- display only
  c.QUAN               AS content_qty,
  c.UNIT               AS content_unit,
  c.MATID              AS packed_product,
  l.QUAN               AS level_qty,
  l.UNIT               AS level_unit,
  l.MATID              AS packaging_material,
  l.HURELEVANT
FROM <catalog>.<schema>.scwm_pvpakc c
JOIN <catalog>.<schema>.scwm_pvpakl l
  ON  l.MANDT  = c.MANDT
  AND l.PVGUID = c.PVGUID
  AND l.PVCNT  = c.PVCNT      -- without the counter, change states cross
WHERE c.MANDT = '<client>';

-- The header stands apart, and this reference draws no edge from it. Filter
-- its status first: one specification carries several node rows and only the
-- active one describes current behavior.
SELECT
  lower(hex(h.PNGUID)) AS node_hex,
  h.PNCNT,
  h.STATUS,
  h.PS_GROUP,
  h.LEVEL_SET,
  h.ACTIVATE_TIME
FROM <catalog>.<schema>.scwm_pnpakh h
WHERE h.MANDT = '<client>';

-- Two things not to chase:
--   1. The "packspec" table names that circulate in community dumps are
--      ABAP STRUCTURES, not tables. Nothing is stored in them.
--   2. The separately browsable legacy PS_* family is marked in DDIC as do
--      not use, SCM 4.1 only. It is not the current persistence and is
--      deliberately absent from this catalog.
-- And one open lead: the hook from the packaging specification ID an end user
-- reads off a screen to the node GUID above runs through the generic iPPE
-- node-identification table, outside this reference's namespaces. The mapping
-- is community-sourced only - verify it in your own system before relying
-- on it.