Contact us
  1. Home
  2. DevOps
  3. SAP tables
  4. Table relationships

SAP table relationships: how the main tables join

Join keys and diagrams for the material master, business partners, procure-to-pay, order-to-cash, finance, production and the Brazilian nota fiscal, in SAP S/4HANA and with the SAP ECC tables they replaced.

  • Foreign keys
  • Join paths
  • ABAP SQL
  • SAP S/4HANA and ECC

By · Updated

How SAP tables are related

SAP describes relationships in the ABAP Dictionary: a field's check table and foreign key say which table's key it refers to. The database itself has no foreign key constraints; the application checks the values when the data is entered.

You do not need a diagram for every table. Most joins follow a handful of patterns, and once you recognize the pattern you can usually guess the join fields before you open SE11.

Keys on this page leave out the client (MANDT). ABAP SQL adds it to every join on its own; in native SQL and external tools you must add it yourself.

Seven patterns behind almost every join

  1. Header and item

    The item table repeats the document key and adds an item number.

    • BKPF → BSEG (+ BUZEI)
    • EKKO → EKPO (+ EBELP)
    • VBAK → VBAP (+ POSNR)

  2. Organizational levels

    Master data grows a key field at each level: client, company code, plant, storage location, sales area.

    • MARA → MARC (+ WERKS) → MARD (+ LGORT)
    • KNA1 → KNB1 (+ BUKRS)

  3. Text table

    Same key plus the language. Filter SPRAS, or you get one row per language.

    • MARA → MAKT
    • SKA1 → SKAT
    • CSKS → CSKT

  4. Validity period

    A valid-to date in the key and a valid-from date beside it. Pick the record valid on the date you care about.

    • CSKS, CEPC (DATAB … DATBI)
    • ANLZ (BDATU)

  5. Document flow

    A table that only links documents to their predecessors and successors.

    • VBFA in sales
    • EKBE in purchasing

  6. Reference key

    A type field plus a key field that holds another document's number, often two fields concatenated.

    • BKPF: AWTYP + AWKEY
    • ACDOCA: AWTYP + AWREF, AWORG
    • J_1BNFLIN: REFTYP + REFKEY

  7. Object number

    Orders, WBS elements and other objects carry an OBJNR that leads to statuses and controlling data.

    • AUFK-OBJNR → JEST
    • PRPS-OBJNR → JEST
    • ACDOCA-OBJNR

Material master

Everything hangs off MARA by the material number. Each view of MM03 adds the key of its organizational level.

MARAGeneral material dataMATNR
  • MAKT · DescriptionsMATNR, SPRAS
  • MARM · Units of measureMATNR, MEINH
    MEAN · EAN/UPC codesMATNR, MEINH, LFNUM
  • MARC · Plant dataMATNR, WERKS
    MARD · Storage location dataMATNR, WERKS, LGORT
  • MVKE · Sales dataMATNR, VKORG, VTWEG
  • MLAN · Tax classificationMATNR, ALAND
  • MBEW · ValuationMATNR, BWKEY, BWTAR
    T001K · Valuation areaBWKEY → BUKRS
TableKeyLevelNote
MARAMATNRClientBasic data; MATNR up to 40 characters in SAP S/4HANA
MAKTMATNR, SPRASLanguageFilter SPRAS = sy-langu
MARMMEANMATNR, MEINH (+ LFNUM)Unit of measureConversion factors and EAN codes per unit
MARCMATNR, WERKSPlantMRP, purchasing, work scheduling and costing data
MARDMATNR, WERKS, LGORTStorage locationS/4HANA: stock quantities calculated from MATDOC
MCHAMCH1MATNR, WERKS, CHARG / MATNR, CHARGBatchBatches per plant, or across plants
MBEWMATNR, BWKEY, BWTARValuation areaBWKEY is the plant when valuation is at plant level: join MARC-WERKS → T001W-BWKEY. Values from ACDOCA and the material ledger in S/4HANA
MVKEMATNR, VKORG, VTWEGSales organization, channelSales views
MLANMATNR, ALANDCountryTax classification for sales
MLGNMATNR, LGNUMWarehouse numberWarehouse management views

Business partner, customer and supplier

In SAP S/4HANA a customer or supplier starts life as a business partner. Customer-vendor integration (CVI) keeps the classic tables and links them by the partner's GUID.

BUT000

PARTNER, PARTNER_GUID

CVI_CUST_LINK

PARTNER_GUID → CUSTOMER

KNA1

KUNNR

KNB1 · KNVV

+ BUKRS · + VKORG, VTWEG, SPART

BUT000

PARTNER, PARTNER_GUID

CVI_VEND_LINK

PARTNER_GUID → VENDOR

LFA1

LIFNR

LFB1 · LFM1

+ BUKRS · + EKORG

  • Addresses: KNA1-ADRNR and LFA1-ADRNR point to ADRC-ADDRNUMBER. BUT020 lists the partner's addresses.

  • Roles: BUT100 shows which roles a partner has, such as customer, supplier or contact person.

  • Numbers: the partner number and the customer or supplier number can be the same or different; always go through the link table.

Procure to pay

The purchase order item is the hub: requisition before it, goods receipts and invoices after it, recorded in its history EKBE.

  1. EBAN
  2. EKKO · EKPO
  3. EKET · EKKN
  4. EKBE
  5. MATDOC
  6. RBKP · RSEG
  7. BKPF · BSEG · ACDOCA
  8. Payment (AUGBL)
From → ToJoin onNote
EKKO → EKPOEKPO-EBELN = EKKO-EBELNOne header, many items
EBAN → EKPOEKPO-BANFN = EBAN-BANFN AND EKPO-BNFPO = EBAN-BNFPOThe PO item keeps its requisition
EKPO → EKETEBELN, EBELPSchedule lines (delivery dates)
EKPO → EKKNEBELN, EBELPAccount assignments; ZEKKN numbers them
EKPO → EKBEEBELN, EBELPPO history: VGABE 1 goods receipt, 2 invoice receipt
EKBE → MATDOCMBLNR = EKBE-BELNR AND MJAHR = EKBE-GJAHR AND ZEILE = EKBE-BUZEI, with VGABE = '1'MSEG in SAP ECC. MATDOC-EBELN and EBELP also point straight to the PO item
EKBE → RBKPRBKP-BELNR = EKBE-BELNR AND RBKP-GJAHR = EKBE-GJAHR, with VGABE = '2'The supplier invoice header
RBKP → RSEGBELNR, GJAHRRSEG-EBELN and EBELP lead back to the PO item
RBKP → BKPFBKPF-AWTYP = 'RMRP' AND BKPF-AWKEY = RBKP-BELNR && RBKP-GJAHRA concatenated reference key
MATDOC → BKPFBKPF-AWTYP = 'MKPF' AND BKPF-AWKEY = MBLNR && MJAHRAWTYP stays MKPF in SAP S/4HANA
BSEG → paymentBKPF-BUKRS = BSEG-BUKRS AND BKPF-BELNR = BSEG-AUGBL AND BKPF-GJAHR = BSEG-AUGGJThe clearing document of a paid item
EKKO → LFA1LFA1-LIFNR = EKKO-LIFNRThe supplier

Order to cash

Each sales document item points to its predecessor in its reference fields, and VBFA stores the whole flow in one place.

  1. VBAK · VBAP · VBEP
  2. LIKP · LIPS
  3. MATDOC (goods issue)
  4. VBRK · VBRP
  5. BKPF · ACDOCA
  6. J_1BNFDOC (Brazil)
From → ToJoin onNote
VBAK → VBAPVBELNHeader and items
VBAP → VBEPVBELN, POSNRSchedule lines
VBAK → VBPAVBPA-VBELN = VBAK-VBELN AND VBPA-POSNR = '000000'Header partners: sold-to, ship-to, payer
VBAK → PRCD_ELEMENTSKNUMV = VBAK-KNUMV (items: KPOSN = VBAP-POSNR)Pricing conditions; KONV in SAP ECC
VBAP → LIPSLIPS-VGBEL = VBAP-VBELN AND LIPS-VGPOS = VBAP-POSNRThe delivery item's reference document
LIKP → LIPSVBELNDelivery header and items
LIPS → VBRPVBRP-VGBEL = LIPS-VBELN AND VBRP-VGPOS = LIPS-POSNRDelivery-related billing
VBAP → VBRPVBRP-AUBEL = VBAP-VBELN AND VBRP-AUPOS = VBAP-POSNRThe sales order behind any billing item
VBRK → BKPFBKPF-AWTYP = 'VBRK' AND BKPF-AWKEY = VBRK-VBELNThe accounting document of the invoice
Any → VBFAVBFA-VBELV, POSNV = predecessor; VBFA-VBELN, POSNN = successorVBTYP_N gives the successor's category: J delivery, M invoice, R goods movement
VBAK → KNA1KNA1-KUNNR = VBAK-KUNNRThe sold-to party

In SAP S/4HANA the status tables VBUK and VBUP are gone: read the status fields on VBAK, VBAP, LIKP, LIPS and VBRK.

Finance: the journal entry and everything around it

BKPF, BSEG and ACDOCA share the document key. From ACDOCA, every account assignment field leads to its master data table, and the reference fields lead back to the document that started it.

BKPF → BSEG

BUKRS, BELNR, GJAHR

BKPF → ACDOCA

RBUKRS = BUKRS, GJAHR, BELNR

BSEG → ACDOCA

RBUKRS = BUKRS, GJAHR, BELNR, BUZEI, with RLDNR = '0L'

  • RACCT→ SKB1 (RBUKRS, RACCT) and SKA1 (KTOPL from T001)
  • RCNTR→ CSKS (KOKRS, KOSTL, valid on BUDAT)
  • PRCTR→ CEPC (KOKRS, PRCTR, valid on BUDAT)
  • AUFNR→ AUFK
  • PS_PSP_PNR→ PRPS-PSPNR
  • KUNNR · LIFNR→ KNA1 · LFA1
  • MATNR · WERKS→ MARA · T001W
  • ANLN1, ANLN2→ ANLA (with RBUKRS)
  • SEGMENT→ FAGL_SEGM
  • RASSC→ T880 (trading partner)
  • RLDNR→ FINSC_LEDGER
  • KOKRS→ TKA01

Back to the source document

AWTYPSource documentBKPF-AWKEYACDOCA AWREF / AWORG
BKPFFI documentBELNR + BUKRS + GJAHRBELNR / BUKRS + GJAHR
RMRPSupplier invoice (RBKP)BELNR + GJAHRBELNR / GJAHR
MKPFMaterial document (MATDOC)MBLNR + MJAHRMBLNR / MJAHR
VBRKBilling documentVBELNVBELN / empty

AWKEY is AWREF followed by AWORG. More AWTYP values are listed on the ACDOCA page.

Production and projects

Master data (BOM and routing) comes in through link tables; the order header carries the numbers that lead to operations, components and statuses.

From → ToJoin onNote
MAST → STKOSTKO-STLTY = 'M' AND STKO-STLNR = MAST-STLNR AND STKO-STLAL = MAST-STLALMaterial BOM header
STKO → STPOSTLTY, STLNRBOM items; STAS assigns items to each alternative
MAPL → PLKOPLNTY, PLNNR, PLNALMaterial to routing; operations in PLPO
AUFK → AFKOAUFNROrder master to production order header
AFKO → AFPOAUFNROrder items: material, quantity, plant
AFKO → AFVCAFVC-AUFPL = AFKO-AUFPLOperations of the order
AFKO → RESBRESB-RSNUM = AFKO-RSNUMComponents (reservations)
AFKO → AFRUAFRU-AUFNR = AFKO-AUFNRConfirmations
AUFK → JESTJEST-OBJNR = AUFK-OBJNR AND JEST-INACT = ' 'Active statuses; texts in TJ02T (ISTAT, SPRAS)
PROJ → PRPSPRPS-PSPHI = PROJ-PSPNRWBS elements of a project
PRPS → ACDOCAACDOCA-PS_PSP_PNR = PRPS-PSPNRActual costs and revenues on the WBS element
AUFK → ACDOCAACDOCA-AUFNR = AUFK-AUFNRActual costs on the order

Brazil: nota fiscal tables

Every nota fiscal hangs off its document number DOCNUM. The line items point back to the document that created them.

  • J_1BNFDOCHeader · DOCNUM
  • J_1BNFLINItems · DOCNUM, ITMNUM
  • J_1BNFSTXTaxes per item · DOCNUM, ITMNUM, TAXTYP
  • J_1BNFNADPartners · DOCNUM, PARVW
  • J_1BNFE_ACTIVENF-e status and access key · DOCNUM
J_1BNFLIN back to the source document
REFTYPSourceREFKEYREFITM
BIBilling documentVBRK-VBELNVBRP-POSNR
LISupplier invoice (logistics invoice verification)RBKP-BELNR + GJAHRRSEG-BUZEI
MDMaterial documentMBLNR + MJAHRZEILE

Other useful links: J_1BNFDOC-BRANCH with BUKRS → J_1BBRANCH (business place), and J_1BNFLIN-CFOP for the operation code. Tax rules and codes are in Brazil taxation.

Join examples in ABAP SQL

Five joins you will write sooner or later. Pick one to see the statement and what to watch.

Goods receipts of a purchase order

SELECT FROM ekpo AS i
  INNER JOIN ekbe AS h
    ON  h~ebeln = i~ebeln
    AND h~ebelp = i~ebelp
  FIELDS i~ebeln, i~ebelp, i~matnr,
         h~belnr AS gr_doc, h~gjahr,
         h~menge, h~dmbtr, h~shkzg
  WHERE i~ebeln = @lv_ebeln
    AND h~vgabe = '1'
  INTO TABLE @DATA(receipts).

Tables

EKPO, EKBE

What you get

Every goods receipt per PO item, with quantity, local currency value and the material document number.

Watch out

VGABE 1 is goods receipt and 2 invoice receipt. Reversals are lines of their own with SHKZG = H, so net them before you report quantities.

Accounting document of a billing document

" AWKEY is longer than VBELN: convert first
DATA(lv_awkey) = CONV awkey( lv_vbeln ).

SELECT FROM bkpf
  FIELDS bukrs, belnr, gjahr, blart, budat
  WHERE awtyp = 'VBRK'
    AND awkey = @lv_awkey
  INTO TABLE @DATA(fi_docs).

" Supplier invoice instead: AWKEY = BELNR + GJAHR
" DATA(lv_awkey) = |{ lv_belnr }{ lv_gjahr }|.

Tables

VBRK (number only), BKPF

What you get

The FI document (or documents, one per company code) created by the billing document.

Watch out

Fill AWKEY exactly as stored: billing documents use VBELN with leading zeros; supplier invoices and material documents concatenate number and year.

Description and stock per storage location

SELECT FROM mara AS m
  INNER JOIN makt AS t
    ON t~matnr = m~matnr
  INNER JOIN mard AS d
    ON d~matnr = m~matnr
  FIELDS m~matnr, t~maktx,
         d~werks, d~lgort, d~labst
  WHERE m~mtart = 'FERT'
    AND t~spras = @sy-langu
  INTO TABLE @DATA(stock).

Tables

MARA, MAKT, MARD

What you get

Finished products with their description in the logon language and unrestricted stock per plant and storage location.

Watch out

In SAP S/4HANA, MARD stock fields are calculated from MATDOC through a compatibility view: fine for a few materials, slow for mass reads. Use the released stock CDS views for volume.

Customer behind a business partner

SELECT FROM but000 AS bp
  INNER JOIN cvi_cust_link AS l
    ON l~partner_guid = bp~partner_guid
  INNER JOIN kna1 AS c
    ON c~kunnr = l~customer
  FIELDS bp~partner, c~kunnr,
         c~name1, c~land1
  WHERE bp~partner = @lv_partner
  INTO TABLE @DATA(customers).

Tables

BUT000, CVI_CUST_LINK, KNA1

What you get

The customer number and its general data for one business partner.

Watch out

Swap CVI_CUST_LINK and KNA1 for CVI_VEND_LINK (field VENDOR) and LFA1 to get the supplier.

Cost centers valid today, with their names

SELECT FROM csks AS c
  INNER JOIN cskt AS t
    ON  t~kokrs = c~kokrs
    AND t~kostl = c~kostl
    AND t~datbi = c~datbi
  FIELDS c~kostl, t~ktext,
         c~prctr, c~bukrs
  WHERE c~kokrs = @lv_kokrs
    AND c~datab <= @sy-datum
    AND c~datbi >= @sy-datum
    AND t~spras = @sy-langu
  INTO TABLE @DATA(cost_centers).

Tables

CSKS, CSKT

What you get

One line per cost center: name, profit center and company code as valid today.

Watch out

The text table shares the validity key DATBI. Leave it out of the ON condition and each cost center gets the names of all its periods.

Find relationships yourself

  • SE11 graphic

    Open the table and choose Utilities › Graphic: the foreign key relationships appear as a diagram you can expand.

  • Check tables and foreign keys

    On the Entry help/check tab, the check table column and the foreign key button show each field's relationship and its field pairs.

  • DD08L and DD05S

    The relationship definitions and their field pairs. Search DD08L by CHECKTABLE = T001 to see every table that points to the company code.

  • ST05 trace

    Trace a standard transaction and read its SQL statements: they show the joins SAP itself uses.

  • CDS associations

    Released CDS views model relationships as associations. In ABAP Development Tools they document joins that SE11 never had.

  • Test on real data

    Try the join in SE16H or SQVI before you write code, and compare the row counts with what you expect.

Pitfalls

  • Leading zeros. KUNNR, LIFNR, AUFNR and MATNR are stored in internal format. Convert input with the ALPHA routine before you compare.
  • Validity periods. A cost center with two periods joins twice. Restrict DATAB and DATBI to your date.
  • Fan-out. Header amounts summed after a join to items are multiplied by the number of items. Add up at one level only.
  • Ledgers. ACDOCA has one line per ledger: fix RLDNR before joining it to BSEG or summing it.
  • Concatenated keys. AWKEY and REFKEY hold several fields in one. Build the full value; never compare on part of the field.
  • Compatibility views. In SAP S/4HANA, joins on MSEG, MARD, COEP or BSIS run through views. For volume, read MATDOC, ACDOCA or released CDS views.

Frequently asked questions

How are SAP tables related to each other?

Through foreign keys defined in the ABAP Dictionary: a field's check table names the table whose key it refers to. The database has no constraints; the application checks the values.

How do I see the relationships of a table in SE11?

Display the table and choose Utilities › Graphic for a diagram, or look at the check table and foreign key of each field on the Entry help/check tab. The definitions are stored in DD08L and DD05S.

What is the link between MARA and MARC?

The material number. MARC repeats MATNR and adds the plant (WERKS), so a material has one MARA record and one MARC record per plant.

How do I find the accounting document of a billing document?

Read BKPF with AWTYP = 'VBRK' and AWKEY = the billing document number (VBRK-VBELN). The document flow in VF03 shows the same link.

How do I link a purchase order to its goods receipt?

Through the PO history EKBE with VGABE = '1' on EBELN and EBELP, or directly through MATDOC-EBELN and EBELP (MSEG in SAP ECC).

How is a business partner linked to a customer or supplier?

Through CVI_CUST_LINK and CVI_VEND_LINK, which map BUT000-PARTNER_GUID to the customer number in KNA1 or the supplier number in LFA1.

Which table links a nota fiscal to its billing document?

J_1BNFLIN. For billing documents REFTYP is BI, REFKEY holds the billing document number and REFITM its item.

References