The SAP Data Quirks Guide

-- MANDT is the first key field on client-dependent application tables.
-- Filter it at the read boundary AND carry it through every join.
SELECT
  h.MANDT,
  h.EBELN,
  i.EBELP,
  i.MATNR,
  i.MENGE,
  i.MEINS
FROM <catalog>.<schema>.ekko h
JOIN <catalog>.<schema>.ekpo i
  ON  i.MANDT = h.MANDT
  AND i.EBELN = h.EBELN
WHERE h.MANDT = '<client>';

-- Wrong: ON i.EBELN = h.EBELN
-- It looks fine in a one-client sandbox and fans out as soon as the landed
-- schema contains the same purchasing document number in another client.

-- Keep SAP identifiers as strings in bronze and silver.
-- Apply display-to-internal formatting to the INPUT, never by casting the
-- stored column to a number and back.
SELECT
  v.VBELN,
  p.POSNR,
  p.MATNR
FROM <catalog>.<schema>.vbak v
JOIN <catalog>.<schema>.vbap p
  ON  p.MANDT = v.MANDT
  AND p.VBELN = v.VBELN
WHERE v.MANDT = '<client>'
  AND v.VBELN = lpad('<sales_document>', 10, '0');

-- Material number width is system/version dependent (18 in classic ECC;
-- S/4HANA supports 40). Read the DDIC length for your source before using:
--   lpad('<external_material>', <ddic_length>, '0')
-- Never CAST POSNR, GJAHR, BUZEI, BELNR, VBELN, EBELN, or MATNR to BIGINT.

-- DATS and TIMS are fixed-width character semantics in raw extracts.
-- Convert the zero-filled INITIAL date to NULL before parsing.
SELECT
  b.BUKRS,
  b.BELNR,
  b.GJAHR,
  to_date(nullif(b.BLDAT, '00000000'), 'yyyyMMdd') AS document_date,
  to_date(nullif(b.BUDAT, '00000000'), 'yyyyMMdd') AS posting_date
FROM <catalog>.<schema>.bkpf b
WHERE b.MANDT = '<client>';

-- When a table carries a DATS/TIMS pair, keep the date guard and preserve
-- 000000 as a valid midnight time once the date itself is present.
CASE
  WHEN t.ERDAT = '00000000' THEN NULL
  ELSE to_timestamp(concat(t.ERDAT, lpad(t.ERZET, 6, '0')), 'yyyyMMddHHmmss')
END AS created_at_source_time

-- Do not append a timezone unless that field's documentation states one.
-- Many classic application stamps reflect the SAP application server time.

-- An amount without its currency and a quantity without its unit are
-- incomplete facts. Keep the reference fields in the same row and group.
SELECT
  k.WAERS                              AS document_currency,
  sum(s.WRBTR)                         AS amount_document_currency
FROM <catalog>.<schema>.bkpf k
JOIN <catalog>.<schema>.bseg s
  ON  s.MANDT = k.MANDT
  AND s.BUKRS = k.BUKRS
  AND s.BELNR = k.BELNR
  AND s.GJAHR = k.GJAHR
WHERE k.MANDT = '<client>'
GROUP BY k.WAERS;

SELECT
  e.MEINS                              AS order_unit,
  sum(e.MENGE)                         AS ordered_quantity
FROM <catalog>.<schema>.ekpo e
WHERE e.MANDT = '<client>'
GROUP BY e.MEINS;

-- Convert currencies or units only after preserving this grain. Never SUM
-- mixed WAERS or MEINS values into one unlabeled number.

-- MAKT is keyed by client + material + language. Choose one language
-- before joining descriptions onto the material master.
SELECT
  m.MATNR,
  t.MAKTX
FROM <catalog>.<schema>.mara m
LEFT JOIN <catalog>.<schema>.makt t
  ON  t.MANDT = m.MANDT
  AND t.MATNR = m.MATNR
  AND t.SPRAS = '<language_key>'
WHERE m.MANDT = '<client>';

-- A safer multilingual model keeps the text table at its native grain:
SELECT MANDT, MATNR, SPRAS, MAKTX
FROM <catalog>.<schema>.makt
WHERE MANDT = '<client>';

-- Join that dimension only after the consuming model has chosen a language.
-- The internal SAP language key is not necessarily the two-letter code your
-- presentation layer uses, so map it explicitly rather than guessing.

-- Accounting document numbers repeat by company code and fiscal year.
-- The complete BKPF -> BSEG join also carries client and line-item grain.
SELECT
  k.BUKRS,
  k.BELNR,
  k.GJAHR,
  s.BUZEI,
  s.HKONT,
  s.WRBTR,
  k.WAERS
FROM <catalog>.<schema>.bkpf k
JOIN <catalog>.<schema>.bseg s
  ON  s.MANDT = k.MANDT
  AND s.BUKRS = k.BUKRS
  AND s.BELNR = k.BELNR
  AND s.GJAHR = k.GJAHR
WHERE k.MANDT = '<client>';

-- Material-document grain is also composite:
--   MANDT + MBLNR + MJAHR + ZEILE
-- Treat the key flags in DDIC/catalog metadata as the contract. The visible
-- document number is a label until every key column is present.

-- Normalize SAP INITIAL values only where the field semantics justify it.
SELECT
  r.BANFN,
  r.BNFPO,
  nullif(trim(r.LOEKZ), '')                    AS deletion_indicator,
  to_date(nullif(r.ERDAT, '00000000'), 'yyyyMMdd') AS requisition_date,
  nullif(r.MATNR, '')                          AS material_number,
  r.MENGE,                                     -- zero can be a real quantity
  r.MEINS
FROM <catalog>.<schema>.eban r
WHERE r.MANDT = '<client>';

-- Do not run blanket NULLIF(column, 0) across numeric fields. Zero is a valid
-- amount, quantity, status, or counter in many domains. Likewise, a blank
-- indicator can mean the active/default state rather than missing data.

-- S/4HANA can preserve a familiar classic name as a compatibility or proxy
-- view while the physical write moves elsewhere. Point CDC at persistence.
SELECT
  d.MANDT,
  d.MBLNR,
  d.MJAHR,
  d.ZEILE,
  d.MATNR,
  d.WERKS,
  d.LGORT,
  d.BWART,
  d.MENGE,
  d.MEINS
FROM <catalog>.<schema>.matdoc d
WHERE d.MANDT = '<client>';

-- Do not UNION MATDOC with MSEG in S/4HANA: the classic MSEG read is a
-- compatibility projection over the new material-document model.
--
-- Finance is nuanced rather than universal:
--   ACDOCA = Universal Journal / G/L view and most actual CO reporting
--   BKPF   = accounting document header; it remains
--   BSEG   = entry view and open-item/source-document detail; it remains
-- Choose the physical or released CDS source that matches the business view,
-- and verify the release state for your S/4HANA edition before production.