ERP Table and Field Explorer

SAP ECC Tables and Field Explorer

Search the core SAP ECC 6.0 tables by table name, field or description, see each table's key, important fields and joins, then pick two to four tables to get a JOIN skeleton for SAP HANA (Suite on HANA) or Oracle. Every application table starts with the client field MANDT, and ECC still has the VBUK/VBUP status tables, MKPF/MSEG material documents and the BSEG and KONV cluster tables that S/4HANA replaced.

95 tables, 1197 documented fields and 161 join paths across 10 modules. Free, runs in your browser, no login needed.

1Find a table by name, description or field

Naming: Table and field names are the ABAP Dictionary names (SE11), upper case. Every client-dependent table has MANDT as its first key field, so one database holds several clients; FI-SL tables call it RCLNT, ADRC calls it CLIENT and change documents call it MANDANT. Organizational units (company code BUKRS, plant WERKS, sales organization VKORG, purchasing organization EKORG, controlling area KOKRS) are key or filter fields, not table suffixes. The database schema owner is set at installation, commonly SAPSR3 on Oracle and SAP<SID> or SAPABAP1 on HANA: confirm with your Basis team.

Tables by module

2Pick tables

95 of 95

Material master

MARA

General material data: one row per material, valid across all plants and sales areas.

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

Key:MANDTMATNR
FieldMeaning
MANDTClient. Every application table starts with it; Open SQL fills it automatically.
MATNRMaterial number, CHAR 18 in ECC, stored with leading zeros when numeric.
MTARTMaterial type (T134), for example FERT finished, HALB semi-finished, ROH raw material.
MBRSHIndustry sector (T137).
MATKLMaterial group (T023).
MEINSBase unit of measure (T006).
BRGEWGross weight.
NTGEWNet weight.
GEWEIWeight unit.
SPARTDivision.
PRDHAProduct hierarchy (T179).
MSTAECross-plant material status (T141).
LVORMDeletion flag at client level, X when flagged.

Joins

  • MARA.MATNR = MAKT.MATNR

    Filter MAKT.SPRAS for one language.

  • MARA.MATNR = MARC.MATNR
  • MARA.MATNR = MVKE.MATNR
  • MARA.MATNR = MARM.MATNR
  • MARA.MATNR = MBEW.MATNR
  • MARA.MATNR = MCH1.MATNR
  • MARA.MATNR = STPO.IDNRK
  • MARA.MATNR = AFPO.MATNR
  • MARA.MATNR = RESB.MATNR
  • MARA.MATNR = VBAP.MATNR
  • MARA.MATNR = EKPO.MATNR
  • MARA.MATNR = MSEG.MATNR

Also ERSDA (created on), ERNAM (created by), LAEDA (last change) and BISMT (old material number). MATNR is extended to 40 characters in S/4HANA.

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 SQL skeleton. In ABAP Open SQL drop the schema and MANDT conditions: the client is added automatically.
-- BSEG, KONV and CDPOS are cluster tables on ECC with Oracle, SQL Server or DB2 and cannot be read with native SQL; use BSIS/BSAS/BSID/BSAD/BSIK/BSAK or FAGLFLEXA. They are transparent on Suite on HANA.
-- Numeric keys (MATNR, KUNNR, LIFNR, VBELN, EBELN, AUFNR) are stored with leading zeros: compare with '0000012345', not '12345'. Dates are CHAR 8 YYYYMMDD.
-- Read only. Change data through transactions, BAPIs or IDocs, never with direct UPDATE statements.

SELECT TOP 100
    vbak.VBELN,
    vbak.AUART,
    vbak.VBTYP,
    vbak.AUDAT,
    vbak.ERDAT,
    vbap.POSNR,
    vbap.MATNR,
    vbap.ARKTX,
    vbap.PSTYV,
    vbap.KWMENG,
    vbup.GBSTA,
    vbup.LFSTA,
    vbup.LFGSA,
    vbup.WBSTA,
    vbup.FKSTA,
    kna1.KUNNR,
    kna1.NAME1,
    kna1.NAME2,
    kna1.ORT01,
    kna1.PSTLZ
FROM SAPSR3.VBAK AS vbak
LEFT JOIN SAPSR3.VBAP AS vbap
    ON vbak.VBELN = vbap.VBELN
   AND vbak.MANDT = vbap.MANDT
LEFT JOIN SAPSR3.VBUP AS vbup
    ON vbap.VBELN = vbup.VBELN
   AND vbap.POSNR = vbup.POSNR
   AND vbap.MANDT = vbup.MANDT
-- VBAK to KNA1: Sold-to party.
LEFT JOIN SAPSR3.KNA1 AS kna1
    ON vbak.KUNNR = kna1.KUNNR
   AND vbak.MANDT = kna1.MANDT
WHERE vbak.MANDT = '100'  -- replace with your production 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 a business word such as invoice or BOM. Field matches are highlighted under each table.

  2. 2

    Filter and inspect

    Narrow by module, then open a table to see its key, the fields that matter and the tables it joins to. Notes flag cluster tables and ECC-only tables.

  3. 3

    Build the join

    Add two to four tables with the plus button or a starter. The skeleton includes the documented join fields plus MANDT; switch between HANA and Oracle and set your schema owner.

  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 later.

Questions about SAP ECC (ERP Central Component) tables

Why can't I join BSEG in SQL on SAP ECC?

On ECC running on Oracle, SQL Server or DB2, BSEG is a cluster table: its rows are stored compressed in the physical cluster RFBLG together with other document tables, so native SQL cannot read it and ABAP Open SQL cannot use it in a JOIN. Use the secondary index tables instead: BSIS and BSAS for GL accounts, BSID and BSAD for customers, BSIK and BSAK for vendors (open and cleared items), or FAGLFLEXA when New GL is active. On Suite on HANA the cluster tables are declustered and BSEG can be joined like any other table.

Where are sales order statuses stored in ECC?

In VBUK (header) and VBUP (item), not in VBAK and VBAP. For example VBUK.GBSTK is the overall processing status (A not processed, B partly, C complete) and VBUP.LFSTA the delivery status of an item. The same tables hold the status of deliveries and billing documents. S/4HANA removed VBUK and VBUP and moved the fields onto VBAK, VBAP, LIKP, LIPS and VBRK, so ECC reports that read VBUK need changes during a conversion.

What is MANDT and do I need it in joins?

MANDT is the client, the first key field of every client-dependent table. One ECC database can hold several clients (for example 100 production and 200 test in a QA system), so native SQL must join and filter on MANDT or rows from different clients mix. ABAP Open SQL adds the client automatically. A few tables use another name: RCLNT in FI-SL tables such as GLT0 and FAGLFLEXA, CLIENT in ADRC and MANDANT in CDHDR and CDPOS.

Why does WHERE KUNNR = '12345' return nothing?

Numeric-looking keys are stored with leading zeros up to the field length: a customer 12345 is stored as '0000012345' in KNA1.KUNNR, and a material 4711 as '000000000000004711' in MARA.MATNR (CHAR 18 in ECC). The SAP GUI hides the zeros with the ALPHA conversion exit. Pad the value in SQL, or use CONVERSION_EXIT_ALPHA_INPUT in ABAP. Alphanumeric keys are stored as typed.

How is a bill of material stored in SAP ECC?

MAST links the header material, plant and BOM usage to an internal BOM number (STLNR) and alternative (STLAL). STKO is the BOM header with the base quantity. Items are in STPO, stored per BOM number rather than per alternative, and STAS selects which item nodes (STLKN) belong to each alternative. So a single-level material BOM is MAST to STAS to STPO with STLTY = 'M', and STPO.IDNRK is the component material. Routings follow the same pattern with MAPL, PLKO, PLAS and PLPO.

Where are the prices of a sales order or invoice?

In the pricing conditions table KONV, linked through VBAK.KNUMV or VBRK.KNUMV, with KPOSN matching the item number. KSCHL is the condition type, KBETR the rate and KWERT the value. Like BSEG, KONV is a cluster table on ECC anyDB (cluster KOCLU), so read it by key in ABAP. Purchase order conditions use EKKO.KNUMV. S/4HANA replaced KONV with PRCD_ELEMENTS.

Which ECC tables change in S/4HANA?

The main ones: ACDOCA (the universal journal) holds FI and CO line items and makes the separate totals and index tables unnecessary, MATDOC replaces MKPF and MSEG, VBUK and VBUP are gone, KONV becomes PRCD_ELEMENTS, MATNR grows from 18 to 40 characters, and customers and vendors are managed as business partners. SAP's mainstream maintenance for ECC 6.0 (EhP6 to EhP8) runs to the end of 2027, with optional extended maintenance to the end of 2030.

Is it safe to update SAP ECC tables with SQL?

No. Read with SQL, write through the application. Transactions, BAPIs and IDocs run the update task, number ranges, change documents (CDHDR and CDPOS), locks and the postings to dependent tables such as BSIS or MARD. A direct UPDATE skips all of that and leaves inconsistent data that SAP support will not cover.

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.