SAP PP Production data model
Third-normal-form mirror of the SAP PP production order tables as they land from the source ERP. The order master (AUFK) and PP header (AFKO) anchor the order, with items (AFPO), operations (AFVC/AFVV), reservations (RESB) and confirmations (AFRU) hanging off by foreign key — preserving the exact SAP header/item/operation/confirmation hierarchy for shop-floor reconciliation.
Normalized replica of SAP ECC/S4HANA Production Planning order-processing source tables for a warehouse staging/replication layer: order master (AUFK), production order header (AFKO), item (AFPO) and operations (AFVC/AFVV); component reservations (RESB); confirmations (AFRU); and the material-to-BOM link (MAST) — keyed by the real SAP business keys (aufnr, posnr, aplzl, rsnum/rspos, rueck/rmzhl) and anchored to order-type, routing, plant and material (MARA) check/master tables. It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.
- 12
- tables
- 108
- columns
- 13
- relationships
- 2
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: PostgreSQL). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Order master plus PP header mirrors SAP's split
SAP carries every order in the cross-application order master (AUFK) and, for production orders, an additional PP header (AFKO); replicating both preserves the shared order-number key and the PP-specific scheduling data.
Header/item/operation/confirmation normalization
A production order decomposes into items (AFPO), operations (AFVC/AFVV) and per-operation confirmations (AFRU); modeling each as its own table matches the SAP structure and keeps confirmation history off the operation row.
Component reservations as a separate entity
Component demand is stored as reservations (RESB) keyed by rsnum/rspos and linked to the order item; a dedicated table preserves planned vs. withdrawn quantities and the material-component relationship.
Check tables modeled as lookups
Order type, routing (PLKO), work center (CRHD), plant (T001W) and material (MARA) are slow SAP check/master tables; modeling them as lookups without an audit trail keeps reference data clean and avoids re-keying every order row.
Tables in the SAP PP Production model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Tables (7)
order_master
Grain: one row per order master record (aufnr)
SAP AUFK — order master data.
| Column | Type | Keys |
|---|---|---|
| order_master_key | bigint | PK |
| order_type_key | bigint | |
| plant_key | bigint | |
| aufnr | varchar | BK |
| auart | varchar | |
| autyp | varchar | |
| werks | varchar | |
| bukrs | varchar | |
| ktext | varchar | |
| erdat | date | |
| ernam | varchar | PII |
| loekz | boolean |
order_header_pp
Grain: one row per production order header (aufnr)
SAP AFKO — order header data PP orders.
| Column | Type | Keys |
|---|---|---|
| order_header_pp_key | bigint | PK |
| order_master_key | bigint | |
| routing_key | bigint | |
| aufnr | varchar | BK |
| gamng | decimal | |
| gmein | varchar | |
| gstrp | date | |
| gltrp | date | |
| gstri | date | |
| gltri | date | |
| plnnr | varchar | |
| fevor | varchar |
order_item
Grain: one row per order item (aufnr, posnr)
SAP AFPO — order item.
| Column | Type | Keys |
|---|---|---|
| order_item_key | bigint | PK |
| order_header_pp_key | bigint | |
| material_key | bigint | |
| aufnr | varchar | BK |
| posnr | varchar | BK |
| matnr | varchar | |
| psmng | decimal | |
| wemng | decimal | |
| meins | varchar | |
| lgort | varchar | |
| kdauf | varchar |
order_operation
Grain: one row per order operation (aufpl, aplzl)
SAP AFVC (operation) + AFVV (quantities/dates) — order operations.
| Column | Type | Keys |
|---|---|---|
| order_operation_key | bigint | PK |
| order_header_pp_key | bigint | |
| work_center_key | bigint | |
| aufpl | varchar | BK |
| aplzl | varchar | BK |
| vornr | varchar | |
| ltxa1 | varchar | |
| arbid | varchar | |
| steus | varchar | |
| vgw01 | decimal | |
| vgw02 | decimal | |
| vgw03 | decimal | |
| vge01 | varchar |
component_reservation
Grain: one row per reservation item (rsnum, rspos)
SAP RESB — reservation/dependent requirements.
| Column | Type | Keys |
|---|---|---|
| component_reservation_key | bigint | PK |
| order_item_key | bigint | |
| material_key | bigint | |
| rsnum | varchar | BK |
| rspos | varchar | BK |
| matnr | varchar | |
| werks | varchar | |
| lgort | varchar | |
| bdmng | decimal | |
| enmng | decimal | |
| meins | varchar | |
| posnr | varchar |
confirmation
Grain: one row per order confirmation (rueck, rmzhl)
SAP AFRU — order completion confirmations.
| Column | Type | Keys |
|---|---|---|
| confirmation_key | bigint | PK |
| order_operation_key | bigint | |
| rueck | varchar | BK |
| rmzhl | varchar | BK |
| aufnr | varchar | |
| vornr | varchar | |
| lmnga | decimal | |
| xmnga | decimal | |
| ism01 | decimal | |
| budat | date | |
| ername | varchar | PII |
| stokz | boolean |
bom_link
Grain: one row per material-to-BOM allocation (matnr, werks, stlan, stlnr)
SAP MAST — material to BOM link.
| Column | Type | Keys |
|---|---|---|
| bom_link_key | bigint | PK |
| material_key | bigint | |
| plant_key | bigint | |
| matnr | varchar | BK |
| werks | varchar | BK |
| stlan | varchar | BK |
| stlnr | varchar | BK |
| stlal | varchar |
Lookup tables (5)
order_type
Grain: one row per order type
SAP T003O / T003P — order type check table.
| Column | Type | Keys |
|---|---|---|
| order_type_key | bigint | PK |
| auart | varchar | BK |
| txt | varchar | |
| autyp | varchar |
plant
Grain: one row per plant
SAP T001W — plant/branch check table.
| Column | Type | Keys |
|---|---|---|
| plant_key | bigint | PK |
| werks | varchar | BK |
| name1 | varchar | |
| land1 | varchar | |
| bukrs | varchar |
material
Grain: one row per material master general record
SAP MARA — material master (general), referenced as a lookup here.
| Column | Type | Keys |
|---|---|---|
| material_key | bigint | PK |
| matnr | varchar | BK |
| mtart | varchar | |
| matkl | varchar | |
| meins | varchar | |
| maktx | varchar |
routing
Grain: one row per task list / routing group (plnty, plnnr, plnal)
SAP PLKO — routing/task list header (reference).
| Column | Type | Keys |
|---|---|---|
| routing_key | bigint | PK |
| plnty | varchar | BK |
| plnnr | varchar | BK |
| plnal | varchar | BK |
| ktext | varchar | |
| werks | varchar | |
| statu | varchar |
work_center
Grain: one row per work center (objid)
SAP CRHD — work center header.
| Column | Type | Keys |
|---|---|---|
| work_center_key | bigint | PK |
| objid | varchar | BK |
| arbpl | varchar | |
| werks | varchar | |
| ktext | varchar | |
| verwe | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- order_master.order_type_key → order_type (many-to-one)
- order_master.plant_key → plant (many-to-one)
- order_header_pp.order_master_key → order_master (many-to-one)
- order_header_pp.routing_key → routing (many-to-one)
- order_item.order_header_pp_key → order_header_pp (many-to-one)
- order_item.material_key → material (many-to-one)
- order_operation.order_header_pp_key → order_header_pp (many-to-one)
- order_operation.work_center_key → work_center (many-to-one)
- component_reservation.order_item_key → order_item (many-to-one)
- component_reservation.material_key → material (many-to-one)
- confirmation.order_operation_key → order_operation (many-to-one)
- bom_link.material_key → material (many-to-one)
- bom_link.plant_key → plant (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.