Closing stock, standard cost and actual cost by company code: one HANA SQL query in DB02

Give it a company code and get the closing stock quantity, the standard cost value and the actual cost value for every material — per period, in one result set.

Runs in DB02 → Diagnostics → SQL Editor as plain HANA SQL, not an ABAP report. Amounts come from the Material Ledger period tables. Verified on an S/4HANA sandbox: client 100, company code CC10, fiscal year 2026 period 007.

0. Before you start — valuation area wiring, and whether the physical table is still readable

CaseCompany code ↔ valuation area ↔ plant — T001K / T001W
What it doesThe starting point for pulling material data by company code. T001K links valuation area (BWKEY) to company code (BUKRS); T001W links plant (WERKS) to valuation area. MLBWA='X' means the Material Ledger is active for that valuation area.
Watch outAt plant-level valuation BWKEY = WERKS, so the join is 1:1. At company-code-level valuation one valuation area carries several plants and the join multiplies rows.
Result1 row (PT10)
SELECT k.BUKRS, k.BWKEY, w.WERKS, w.NAME1, k.MLBWA
  FROM T001K k
  LEFT JOIN T001W w
         ON w.MANDT = k.MANDT
        AND w.BWKEY = k.BWKEY
 WHERE k.MANDT = '100'
   AND k.BUKRS = 'CC10';
Result — 1 row
BUKRS | BWKEY | WERKS | NAME1         | MLBWA
------+-------+-------+---------------+------
CC10  | PT10  | PT10  | Plant 10 #JNC | X
CaseIs the physical table still the real data? — DD25L
What it doesIn S/4HANA the stock tables (MARD, MARC, MCHB, MSKA and friends) were replaced by NSDM_V_* compatibility views, and the actual data lives in MATDOC. Querying the physical table in HANA SQL returns empty or stale values, so check for a compatibility view first.
Watch outTables with no compatibility view — MBEW, CKMLPP, CKMLCR — are still safe to read directly. That is why this query uses them.
Watch outThe sandbox returns more than a hundred NSDM_V_* views; the stock-relevant ones are shown below.
Result7 stock-relevant views
SELECT VIEWNAME, VIEWCLASS
  FROM DD25L
 WHERE VIEWNAME LIKE 'NSDM/_V/_%' ESCAPE '/'
   AND AS4LOCAL = 'A'
 ORDER BY VIEWNAME;
Result — stock-relevant NSDM_V_* views
VIEWNAME     | VIEWCLASS
-------------+----------
NSDM_V_MARD  | D
NSDM_V_MCHB  | D
NSDM_V_MSKA  | D
NSDM_V_MSLB  | D
NSDM_V_MKOL  | D
NSDM_V_MSEG  | D
NSDM_V_MARCH | D

1. The query — closing stock + standard cost + actual cost by period

CaseMaterial Ledger based — CKMLHD / CKMLPP / CKMLCR
What it doesCKMLHD links KALNR (the ledger object) to material and valuation area. CKMLPP-LBKUM is the closing stock quantity for that period. CKMLCR-SALK3 is the value at standard price and SALKV the value at actual price. CURTP='10' is company code currency; UNTPER='000' is the period total line, not a sub-period.
Watch outBDATJ and POPER are DDIC NUMC, which means strings in HANA — write '2026' and '007' with quotes and the full three digits. Writing 7 returns an empty result.
Watch outSALK3 and SALKV will not equal quantity times unit price. They carry the price changes and variance settlements posted during the period, the same figures CKM3 shows.
Watch outBefore the ML period close (CKMLCP) STATUS is below 70 and PVPRS is still the opening price, so read actual cost from SALKV rather than from the unit price.
Result17 rows
SELECT
    k.BUKRS                          AS COMPANY_CODE,
    h.BWKEY                          AS PLANT,
    w.NAME1                          AS PLANT_NAME,
    h.MATNR                          AS MATERIAL,
    t.MAKTX                          AS MATERIAL_TEXT,
    h.BWTAR                          AS VAL_TYPE,
    p.BDATJ                          AS FISCAL_YEAR,
    p.POPER                          AS PERIOD,
    p.STATUS                         AS ML_STATUS,
    p.LBKUM                          AS CLOSING_QTY,
    p.MEINS                          AS UOM,
    c.STPRS / NULLIF(c.PEINH, 0)     AS STD_UNIT_PRICE,
    c.SALK3                          AS STD_VALUE,
    c.PVPRS / NULLIF(c.PEINH, 0)     AS ACT_UNIT_PRICE,
    c.SALKV                          AS ACT_VALUE,
    c.SALKV - c.SALK3                AS PRICE_DIFF,
    c.WAERS                          AS CURRENCY
FROM        CKMLHD h
INNER JOIN  T001K  k ON  k.MANDT = h.MANDT
                     AND k.BWKEY = h.BWKEY
LEFT  JOIN  T001W  w ON  w.MANDT = h.MANDT
                     AND w.WERKS = h.BWKEY
INNER JOIN  CKMLPP p ON  p.MANDT  = h.MANDT
                     AND p.KALNR  = h.KALNR
                     AND p.BDATJ  = '2026'
                     AND p.POPER  = '007'
                     AND p.UNTPER = '000'
INNER JOIN  CKMLCR c ON  c.MANDT  = p.MANDT
                     AND c.KALNR  = p.KALNR
                     AND c.BDATJ  = p.BDATJ
                     AND c.POPER  = p.POPER
                     AND c.UNTPER = p.UNTPER
                     AND c.CURTP  = '10'
LEFT  JOIN  MAKT   t ON  t.MANDT = h.MANDT
                     AND t.MATNR = h.MATNR
                     AND t.SPRAS = 'E'
WHERE h.MANDT = '100'
  AND k.BUKRS = 'CC10'
--AND p.LBKUM <> 0
ORDER BY h.BWKEY, h.MATNR, h.BWTAR;
Result — 17 rows (CC10, 2026/007)
COMPANY_CODE | PLANT | PLANT_NAME    | MATERIAL | MATERIAL_TEXT     | VAL_TYPE | FISCAL_YEAR | PERIOD | ML_STATUS | CLOSING_QTY | UOM | STD_UNIT_PRICE | STD_VALUE | ACT_UNIT_PRICE | ACT_VALUE  | PRICE_DIFF | CURRENCY
-------------+-------+---------------+----------+-------------------+----------+-------------+--------+-----------+-------------+-----+----------------+-----------+----------------+------------+------------+---------
CC10         | PT10  | Plant 10 #JNC | ML1001   | [JNC] 고기만두_완제품    |          | 2026        | 007    | 10        | 978.000     | EA  | 0.19           | 4,913.02  | 0.33           | 16,827.32  | 11,914.30  | KRW
CC10         | PT10  | Plant 10 #JNC | ML1002   | [JNC] 김치만두_완제품    |          | 2026        | 007    | 05        | 0.000       | EA  | 0.17           | 0.00      | 0.17           | 4,250.00   | 4,250.00   | KRW
CC10         | PT10  | Plant 10 #JNC | ML1003   | [JNC] 새우만두_완제품    |          | 2026        | 007    | 05        | 0.000       | EA  | 0.19           | 0.00      | 3.16           | 3,163.16   | 3,163.16   | KRW
CC10         | PT10  | Plant 10 #JNC | ML4001   | [JNC] 고기성형만두_반제품  |          | 2026        | 007    | 01        | 0.000       | EA  | 0.16           | 0.00      | 13.61          | 340,107.78 | 340,107.78 | KRW
CC10         | PT10  | Plant 10 #JNC | ML4002   | [JNC] 김치성형만두_반제품  |          | 2026        | 007    | 01        | 0.000       | EA  | 0.14           | 0.00      | 1.86           | 5,637.20   | 5,637.20   | KRW
CC10         | PT10  | Plant 10 #JNC | ML4003   | [JNC] 새우성형만두_반제품  |          | 2026        | 007    | 01        | 0.000       | EA  | 0.16           | 0.00      | 0.16           | 3,839.84   | 3,839.84   | KRW
CC10         | PT10  | Plant 10 #JNC | ML5001   | [JNC] 고기          |          | 2026        | 007    | 10        | 2,000.000   | KG  | 0.10           | 200.00    | 0.10           | 0.00       | -200.00    | KRW
CC10         | PT10  | Plant 10 #JNC | ML5002   | [JNC] 고기          |          | 2026        | 007    | 05        | 0.000       | KG  | 0.10           | 0.00      | 0.10           | 0.00       | 0.00       | KRW
CC10         | PT10  | Plant 10 #JNC | ML5003   | [JNC] 부추          |          | 2026        | 007    | 05        | 0.000       | KG  | 0.10           | 0.00      | 10.00          | 0.00       | 0.00       | KRW
CC10         | PT10  | Plant 10 #JNC | ML5004   | [JNC] 당면          |          | 2026        | 007    | 05        | 0.000       | KG  | 0.10           | 0.00      | 20.00          | 0.00       | 0.00       | KRW
CC10         | PT10  | Plant 10 #JNC | ML5005   | [JNC] 소금          |          | 2026        | 007    | 05        | 0.000       | KG  | 0.10           | 0.00      | 30.00          | 0.00       | 0.00       | KRW
CC10         | PT10  | Plant 10 #JNC | ML5006   | [JNC] 밀가루         |          | 2026        | 007    | 05        | 0.000       | KG  | 0.10           | 0.00      | 50.00          | 0.00       | 0.00       | KRW
CC10         | PT10  | Plant 10 #JNC | ML5007   | [JNC] 새우          |          | 2026        | 007    | 05        | 0.000       | KG  | 0.10           | 0.00      | 11.34          | 0.00       | 0.00       | KRW
CC10         | PT10  | Plant 10 #JNC | ML5008   | [JNC] 김치          |          | 2026        | 007    | 05        | 0.000       | KG  | 0.10           | 0.00      | 100.00         | 0.00       | 0.00       | KRW
CC10         | PT10  | Plant 10 #JNC | ML6004   | [JNC] 고기만두 포장지 V2 |          | 2026        | 007    | 05        | 0.000       | EA  | 0.00           | 0.00      | 0.00           | 0.00       | 0.00       | KRW
CC10         | PT10  | Plant 10 #JNC | ML6005   | [JNC] 김치만두 포장지 V2 |          | 2026        | 007    | 05        | 0.000       | EA  | 0.00           | 0.00      | 0.00           | 0.00       | 0.00       | KRW
CC10         | PT10  | Plant 10 #JNC | ML6006   | [JNC] 새우만두 포장지 V2 |          | 2026        | 007    | 05        | 0.000       | EA  | 0.00           | 0.00      | 0.00           | 0.00       | 0.00       | KRW
CaseTotals by company code and plant
What it doesWrap the query above as a subquery and aggregate. Keep CURRENCY in the group key so currencies never get added together.
Result1 row
SELECT COMPANY_CODE, PLANT, CURRENCY,
       SUM(CLOSING_QTY) AS QTY,
       SUM(STD_VALUE)   AS STD_VALUE,
       SUM(ACT_VALUE)   AS ACT_VALUE,
       SUM(ACT_VALUE) - SUM(STD_VALUE) AS PRICE_DIFF
  FROM ( /* the whole query above goes here */ )
 GROUP BY COMPANY_CODE, PLANT, CURRENCY
 ORDER BY COMPANY_CODE, PLANT;
Result — 1 row
COMPANY_CODE | PLANT | CURRENCY | QTY       | STD_VALUE | ACT_VALUE  | PRICE_DIFF
-------------+-------+----------+-----------+-----------+------------+-----------
CC10         | PT10  | KRW      | 2,978.000 | 5,113.02  | 373,825.30 | 368,712.28

2. Variant — when you want right now, not a period

CaseValuation master based — MBEW
What it doesIf you need the current state rather than a period end, read MBEW instead of the Material Ledger. VPRSV='S' means standard price control (valued at STPRS), 'V' means moving average (valued at VERPR). SALK3 is the value at standard price, SALKV the value at moving average or actual price.
Watch outAmount fields must be divided by PEINH (price unit) to become a unit price.
Watch outThis is the value as of the moment you query, not the closing value of any particular period.
Result20 rows
SELECT k.BUKRS, b.BWKEY AS PLANT, b.MATNR, t.MAKTX, b.BWTAR,
       b.VPRSV                       AS PRICE_CTRL,
       b.LBKUM                       AS STOCK_QTY,
       b.STPRS / NULLIF(b.PEINH, 0)  AS STD_UNIT_PRICE,
       b.SALK3                       AS STD_VALUE,
       b.VERPR / NULLIF(b.PEINH, 0)  AS MAP_UNIT_PRICE,
       b.SALKV                       AS VALUE_AT_MAP
  FROM MBEW b
  INNER JOIN T001K k ON k.MANDT = b.MANDT
                    AND k.BWKEY = b.BWKEY
  LEFT  JOIN MAKT  t ON t.MANDT = b.MANDT
                    AND t.MATNR = b.MATNR
                    AND t.SPRAS = 'E'
 WHERE b.MANDT = '100'
   AND k.BUKRS = 'CC10'
   AND b.LVORM = ''
 ORDER BY b.BWKEY, b.MATNR;
Result — 20 rows
BUKRS | PLANT | MATNR  | MAKTX             | BWTAR | PRICE_CTRL | STOCK_QTY   | STD_UNIT_PRICE | STD_VALUE    | MAP_UNIT_PRICE | VALUE_AT_MAP
------+-------+--------+-------------------+-------+------------+-------------+----------------+--------------+----------------+-------------
CC10  | PT10  | ML1001 | [JNC] 고기만두_완제품    |       | S          | 50,855.000  | 0.19           | 9,662.45     | 0.33           | 16,827.32
CC10  | PT10  | ML1002 | [JNC] 김치만두_완제품    |       | S          | 25,000.000  | 0.17           | 4,250.00     | 0.17           | 4,250.00
CC10  | PT10  | ML1003 | [JNC] 새우만두_완제품    |       | S          | 1,001.000   | 0.19           | 190.19       | 3.16           | 3,163.16
CC10  | PT10  | ML4001 | [JNC] 고기성형만두_반제품  |       | S          | 24,998.000  | 0.16           | 3,999.68     | 13.61          | 340,107.78
CC10  | PT10  | ML4002 | [JNC] 김치성형만두_반제품  |       | S          | 3,030.000   | 0.14           | 424.20       | 1.86           | 5,637.20
CC10  | PT10  | ML4003 | [JNC] 새우성형만두_반제품  |       | S          | 23,999.000  | 0.16           | 3,839.84     | 0.16           | 3,839.84
CC10  | PT10  | ML5001 | [JNC] 고기          |       | V          | 0.000       | 0.10           | 0.00         | 0.10           | 0.00
CC10  | PT10  | ML5002 | [JNC] 고기          |       | V          | 37,500.000  | 0.10           | 3,750.00     | 0.10           | 0.00
CC10  | PT10  | ML5003 | [JNC] 부추          |       | V          | 20,000.000  | 0.10           | 200,000.00   | 10.00          | 0.00
CC10  | PT10  | ML5004 | [JNC] 당면          |       | V          | 20,000.000  | 0.10           | 400,000.00   | 20.00          | 0.00
CC10  | PT10  | ML5005 | [JNC] 소금          |       | V          | 13,648.500  | 0.10           | 409,455.00   | 30.00          | 0.00
CC10  | PT10  | ML5006 | [JNC] 밀가루         |       | V          | 112,836.500 | 0.10           | 5,641,825.00 | 50.00          | 0.00
CC10  | PT10  | ML5007 | [JNC] 새우          |       | V          | 44,040.000  | 0.10           | 499,201.72   | 11.34          | 0.00
CC10  | PT10  | ML5008 | [JNC] 김치          |       | V          | 48,985.000  | 0.10           | 4,898,500.00 | 100.00         | 0.00
CC10  | PT10  | ML6001 | [JNC] 고기만두 포장지    |       | V          | 0.000       | 0.10           | 0.00         | 0.00           | 0.00
CC10  | PT10  | ML6002 | [JNC] 고기만두 포장지    |       | V          | 0.000       | 0.10           | 0.00         | 0.00           | 0.00
CC10  | PT10  | ML6003 | [JNC] 고기만두 포장지    |       | V          | 0.000       | 0.00           | 0.00         | 0.00           | 0.00
CC10  | PT10  | ML6004 | [JNC] 고기만두 포장지 V2 |       | V          | 0.000       | 0.00           | 0.00         | 0.00           | 0.00
CC10  | PT10  | ML6005 | [JNC] 김치만두 포장지 V2 |       | V          | 0.000       | 0.00           | 0.00         | 0.00           | 0.00
CC10  | PT10  | ML6006 | [JNC] 새우만두 포장지 V2 |       | V          | 24.000      | 0.00           | 0.00         | 0.00           | 0.00

Comments

Post a Comment

Popular posts from this blog

SAP S/4HANA Table Reference — Tables I Actually Query, by Topic

SAP tables I actually queried while building custom reports and running end-to-end tests on an S/4HANA sandbox, grouped by topic. Updated continuously as development goes on. Each table: Table / Description / Key fields / T-codes / Used in our custom reports. Last updated: 2026-08-13 1. CO — Allocation Cycles (Assessment / Distribution) Table Description Key fields T-codes Used in our reports T811C Allocation cycle header ※ Distribution = ALART 'V', assessment = ALART 'U'. Classic CO-OM uses TAB='CCSS' TAB / CYCLE / SDATE KSU3, KSV3 ZCO0040 (allocation cycle report), ZCO0041 (distribution cycle detail) T811S Cycle segments ※ SRULE (sender rule) / RRULE (receiver rule) / SPERCENT / RCDATA (variable portion type) / NEGTEST (negative tracing factors — stored value differs from the screen code) TAB / CYCLE / SDATE / SEQNR KSU3, KSV3 ZCO0040, ZCO0041 T811K Selection criteria per segment ※ SETKIND: 0=cycle, 1=sender control, 2=sender, 3=rece...
Read more

Production Order Variance Calculation KKS1 — Fixing KV031 and KV011, and What Lands in COSB

In the last post on cost center variance calculation (KSS1) I noted that the similarly-named KKS1 is a different animal. This is that post: production order variance calculation. I hit two errors on the way in, so we start with their causes and fixes, then verify — by comparing the database before and after — exactly what gets stored where. Environment: controlling area JNC2, plant JNC2, period 2026/008, KRW, three production orders. ※ Screens, menus, and settings may differ depending on your SAP release and system configuration. Two errors before it would even run KV031 ("no target cost version") — version 0 itself existed. What was empty was the variance variant for orders (AWVAO) in OKV6: only the cost-center one (AWVAS) was maintained, which is why KSS1 worked fine while KKS1 refused. Same OKV6 screen, different column. KV011 is an order status problem. Order 1004916 got its goods receipt and final delivery on August 2 — run KKS1 for period 007 and, as of that pe...
Read more

Modern ABAP Syntax Notes — Patterns and Pitfalls from Real Development

ABAP syntax actually used while developing our custom Z reports, organized by topic. Examples come from working sources, together with the pitfalls we actually hit. Structure: syntax item → explanation → example code. Last updated: 2026-08-13 1. Data Declarations and Types ▷ Inline declarations DATA(...) / @DATA(...) — Declare and assign in one statement. SELECT result tables land directly in @DATA(lt_...) — the structure is derived from the SELECT list SELECT matnr, maktx FROM makt INTO TABLE @DATA(lt_makt) WHERE spras = @sy-langu. DATA(lv_qty) = ls_stpo-menge * iv_losgr / lv_bmeng. LOOP AT lt_mat INTO DATA(ls_mat). " loop variable inline too ENDLOOP. ▷ FIELD-SYMBOLS / ASSIGNING — Modify internal table rows in place without copying. ASSIGN COMPONENT lets you pick the column name at runtime (used for dynamic logic such as auto-hiding empty columns) LOOP AT gt_list ASSIGNING FIELD-SYMBOL(<ls_list>) WHERE typps = 'M'. <ls_list...
Read more