ERP Table and Field Explorer

SAP S/4HANA Tables and Field Explorer

Search the core SAP S/4HANA tables by table name, field or description, see the key, the columns that matter and the join fields, then pick two to four tables to get a HANA SQL JOIN skeleton. It covers what changed from ECC: journal entries in ACDOCA, goods movements in MATDOC, document status on VBAK and VBAP, pricing in PRCD_ELEMENTS, and customers and suppliers behind the business partner (BUT000).

90 tables, 729 documented fields and 147 join paths across 9 modules. Free, runs in your browser, no login needed.

1Find a table by name, description or field

Naming: S/4HANA tables are client dependent: MANDT is the first key field of most tables (RCLNT in ACDOCA, CLIENT in BUT000, BUT020, ADRC, PRCD_ELEMENTS and the CVI link tables). Organizational units scope the data instead of table names: company code (BUKRS), plant (WERKS), sales organization (VKORG), purchasing organization (EKORG) and controlling area (KOKRS). Customer objects use the Y or Z namespace, for example ZTABLE, or a registered /NAMESPACE/ prefix.

Tables by module

2Pick tables

90 of 90

Material master

MARA

General material data: one row per material, valid for all plants and sales organizations.

Also known as: material master, MM01, MM03, product master

Key:MANDTMATNR
FieldMeaning
MATNRMaterial number. CHAR 40 in S/4HANA; numeric numbers are stored with leading zeros.
MTARTMaterial type, for example FERT finished, HALB semi-finished, ROH raw material (T134).
MATKLMaterial group (T023).
MEINSBase unit of measure.
MBRSHIndustry sector.
ERSDACreated on.
LVORMDeletion flag at client level.
MSTAECross-plant material status.
BRGEWGross weight.
NTGEWNet weight.
GEWEIWeight unit.
SPARTDivision.

Joins

  • MARA.MATNR = MAKT.MATNR

    Filter MAKT.SPRAS for one language.

  • MARA.MATNR = MARC.MATNR
  • MARA.MATNR = MARM.MATNR
  • MARA.MATNR = MVKE.MATNR
  • MARA.MTART = T134.MTART
  • MARA.MATNR = MBEW.MATNR
  • MARA.MATNR = MCH1.MATNR
  • MARA.MATNR = STPO.IDNRK
  • MARA.MATNR = AFKO.PLNBEZ
  • MARA.MATNR = VBAP.MATNR
  • MARA.MATNR = LIPS.MATNR
  • MARA.MATNR = EINA.MATNR
  • MARA.MATNR = MATDOC.MATBF
  • MARA.MATNR = MSEG.MATNR
  • MARA.MANDT = ACDOCA.RCLNT AND MARA.MATNR = ACDOCA.MATNR

MATNR is 40 characters long in S/4HANA (18 in ECC). Read the plant view from MARC, valuation from MBEW and sales data from MVKE.

3Generate a JOIN skeleton (2 to 4 tables)

Starters:
Output
-- Query skeleton generated by netray.co/tools/table-explorer
-- Dialect: SAP HANA SQL. Review keys, filters and indexes before running on production.
-- Native HANA SQL: tables live in the schema of the ABAP database user. Enter it in the schema box, or leave it empty when that schema is your default.
-- Native SQL does not filter the client: keep the MANDT condition. ABAP Open SQL adds the client automatically, so drop MANDT there.
-- Key fields are CHAR with leading zeros (MATNR, AUFNR, VBELN, KUNNR): compare with the padded internal value, for example '000000000000001234'.
-- Read only. Post through BAPIs, OData APIs or transactions, never with direct UPDATE statements.

SELECT TOP 100
    vbak.VBELN,
    vbak.AUART,
    vbak.VBTYP,
    vbak.AUDAT,
    vbak.VKORG,
    vbap.POSNR,
    vbap.MATNR,
    vbap.ARKTX,
    vbap.KWMENG,
    vbap.VRKME,
    vbep.ETENR,
    vbep.EDATU,
    vbep.WMENG,
    vbep.BMENG,
    vbep.ETTYP,
    kna1.KUNNR,
    kna1.NAME1,
    kna1.LAND1,
    kna1.ORT01,
    kna1.PSTLZ
FROM VBAK AS vbak
LEFT JOIN VBAP AS vbap
    ON vbak.VBELN = vbap.VBELN
   AND vbak.MANDT = vbap.MANDT
LEFT JOIN VBEP AS vbep
    ON vbap.VBELN = vbep.VBELN
   AND vbap.POSNR = vbep.POSNR
   AND vbap.MANDT = vbep.MANDT
LEFT JOIN KNA1 AS kna1
    ON vbak.KUNNR = kna1.KUNNR
   AND vbak.MANDT = kna1.MANDT
WHERE vbak.MANDT = '100'  -- replace with your client
ORDER BY vbak.VBELN;
Free, optional. Reopen it on any device.
Download:

How to use it

  1. 1

    Search

    Type a table name such as VBAP, a field such as KWMENG or MATNR, a transaction such as MIGO, or a business word such as invoice. Field matches are highlighted under each table.

  2. 2

    Filter and inspect

    Narrow by module, then open a table to see its key, the columns that matter and the tables it joins to. Click a join to jump to that table.

  3. 3

    Build the join

    Add two to four tables with the plus button or a starter. The SQL skeleton includes the documented join fields plus MANDT (or RCLNT and CLIENT where SAP names the client column differently).

  4. 4

    Keep it

    Star tables as favorites, export to PDF, Excel, Word or CSV, and optionally save the setup to your Netray account to reopen it later.

Questions about SAP S/4HANA (on-premise and private cloud) tables

Where are material documents stored in S/4HANA?

In MATDOC. Goods movements write one MATDOC row per document line with the header and item fields together (MBLNR, MJAHR, ZEILE, BWART, BUDAT, MATNR, WERKS, LGORT, CHARG and so on) plus stock identifier fields with the _SID suffix and a STOCK_QTY stock quantity. The physical key is a split GUID (KEY1 to KEY6), not MBLNR. MKPF and MSEG still exist for compatibility, but new SQL should read MATDOC, always filtered by posting date, plant and material because the table grows fast.

What is ACDOCA and does it replace BSEG?

ACDOCA is the Universal Journal: one line item table for the general ledger, asset accounting, controlling, profitability analysis and the material ledger, keyed by RCLNT, RLDNR, RBUKRS, GJAHR, BELNR and DOCLN. It takes over the line items that ECC kept in tables such as FAGLFLEXA, COEP, ANEP and MLCD. BKPF and BSEG are still there: BKPF is the header, and BSEG is the entry view with open item and clearing fields. For balances and reporting, read ACDOCA filtered by ledger (RLDNR, for example 0L).

How do I find the customer number of a business partner?

In S/4HANA every customer and supplier is a business partner. BUT000 holds the partner (PARTNER, PARTNER_GUID). CVI_CUST_LINK maps PARTNER_GUID to the customer number (CUSTOMER = KNA1.KUNNR) and CVI_VEND_LINK maps it to the supplier number (VENDOR = LFA1.LIFNR). KNA1, KNB1, KNVV, LFA1, LFB1 and LFM1 are still the tables that sales, purchasing and finance documents point to. Note that the client column is CLIENT in BUT000 and the CVI tables, not MANDT.

Do I still need VBUK and VBUP in S/4HANA?

No. S/4HANA keeps the sales document status fields on the document tables themselves. Header status such as GBSTK (overall status) is on VBAK, LIKP and VBRK, and item status such as GBSTA and LFSTA is on VBAP and LIPS. VBUK still exists in the dictionary for compatibility but is not filled for new documents, so ECC queries that joined VBAK to VBUK should read the status from VBAK.

Where are the pricing conditions of a sales order?

In PRCD_ELEMENTS. Take VBAK.KNUMV (or VBRK.KNUMV for an invoice, EKKO.KNUMV for a purchase order) and join PRCD_ELEMENTS on KNUMV, with KPOSN equal to the item number. KSCHL is the condition type, KBETR the rate and KWERT the value. PRCD_ELEMENTS takes over from KONV, and its client column is CLIENT, so join it with VBAK.MANDT = PRCD_ELEMENTS.CLIENT.

Why does my WHERE MATNR = '1234' return nothing?

Numeric material numbers are stored with leading zeros up to the field length, and MATNR is 40 characters in S/4HANA. Pass the internal value (the conversion exit MATN1 does this in ABAP) or, in native SQL, compare with LPAD('1234', 18, '0') or the length your system uses for numeric material numbers. The same applies to VBELN, AUFNR, KUNNR and LIFNR (10 or 12 characters, ALPHA conversion).

Should I query the tables or the CDS views?

For quick analysis and data checks, the tables work, with MANDT in every join. For anything that lives on, build on the CDS views of the virtual data model (the I_ views, for example I_JournalEntryItem on top of ACDOCA) or on a CDS view of your own. Views shield you from table design changes, carry associations and texts, and respect authorizations. In private cloud, direct database access depends on your contract and landscape, so check what your operations model allows.

Is MANDT needed in every join?

In native HANA SQL, yes: client-dependent tables hold every client's data, and a join without MANDT mixes clients. Some tables name the client column differently (RCLNT in ACDOCA, CLIENT in BUT000, ADRC, PRCD_ELEMENTS and the CVI tables), and a few configuration tables such as TJ02T are client-independent. In ABAP Open SQL the client is added for you, so you leave MANDT out.

Sources

Table names and columns were checked against these public references. Tables whose key or columns we could not confirm say so in their notes. Column sets change between releases, so confirm against the data dictionary of your own version before you build on it.