SAP ERP Materials API

Build a fast, cost-effective materials inventory API from SAP ERP data. This example shows end-to-end configuration with caching, authentication, and MCP integration.

Overview

Use Case: Provide real-time material inventory data to web applications, mobile apps, and AI agents without overloading SAP.

Challenge:

• Direct SAP queries: 10-30 seconds
• High RFC load on production SAP
• Expensive ABAP custom development

Solution:

• flAPI with 1-hour cache refresh
• Millisecond API responses
• Zero SAP load during API serving

Project Structure

sap-materials-api/
├── flapi.yaml                 # Main configuration
├── sqls/
│   ├── materials.yaml         # Endpoint config
│   ├── materials.sql          # API query template
│   └── materials_cache.sql    # Cache materialization
└── .env                       # Environment variables (not committed)

Step 1: Configuration

# flapi.yaml
# ═══════════════════════════════════════════════════════════════
# SAP Materials Inventory API
# ═══════════════════════════════════════════════════════════════
project_name: sap-materials-api
project_description: Fast materials inventory API from SAP ERP

template:
  path: './sqls'
  environment-whitelist:
    - '^SAP_.*'
    - '^JWT_.*'

duckdb:
  db_path: ./sap_cache.db
  threads: 4
  max_memory: 4GB

connections:
  sap-prod:
    init: |
      INSTALL erpl;
      LOAD erpl;
    properties:
      ashost: ${SAP_HOST}
      sysnr: ${SAP_SYSNR}
      client: ${SAP_CLIENT}
      user: ${SAP_USER}
      passwd: ${SAP_PASSWORD}
      lang: EN

security:
  enforce-https: true
  jwt:
    secret: ${JWT_SECRET}
    algorithm: HS256
    expiration: 3600
  roles:
    inventory-user:
      permissions: [read]
    inventory-admin:
      permissions: [read, write]

server:
  host: 0.0.0.0
  port: 8080
  workers: 4

Step 2: Cache Template

-- sqls/materials_cache.sql
-- ═══════════════════════════════════════════════════════════════
-- Materialize SAP material master and inventory data
-- Runs every hour to keep cache fresh
-- ═══════════════════════════════════════════════════════════════

SELECT 
  -- Material master (MARA)
  m.MATNR as material_number,
  m.MTART as material_type,
  m.MATKL as material_group,
  m.MEINS as base_unit_of_measure,
  m.BRGEW as gross_weight,
  m.NTGEW as net_weight,
  m.GEWEI as weight_unit,
  
  -- Material description (MAKT)
  t.MAKTX as material_description,
  t.MAKTG as material_description_short,
  
  -- Plant data (MARC)
  p.WERKS as plant,
  p.DISPO as mrp_controller,
  p.DISMM as mrp_type,
  p.PLIFZ as planned_delivery_time,
  
  -- Storage location data (MARD)
  s.LGORT as storage_location,
  s.LABST as unrestricted_stock,
  s.INSME as quality_inspection_stock,
  s.SPEME as blocked_stock,
  s.UMLME as stock_in_transfer,
  
  -- Valuation (MBEW)
  v.STPRS as standard_price,
  v.PEINH as price_unit,
  v.BKLAS as valuation_class,
  v.SALK3 as total_stock_value,
  
  -- Calculated fields
  (s.LABST + s.INSME) as available_stock,
  CASE 
    WHEN s.LABST = 0 THEN 'OUT_OF_STOCK'
    WHEN s.LABST < 10 THEN 'LOW_STOCK'
    ELSE 'IN_STOCK'
  END as stock_status,
  
  CURRENT_TIMESTAMP as cache_updated_at

FROM {{conn.sap-prod.ashost}}.MARA m

-- Material descriptions (English only)
LEFT JOIN {{conn.sap-prod.ashost}}.MAKT t
  ON m.MATNR = t.MATNR 
  AND t.SPRAS = 'E'

-- Plant data
LEFT JOIN {{conn.sap-prod.ashost}}.MARC p
  ON m.MATNR = p.MATNR

-- Storage location stock
LEFT JOIN {{conn.sap-prod.ashost}}.MARD s
  ON m.MATNR = s.MATNR
  AND p.WERKS = s.WERKS

-- Valuation data
LEFT JOIN {{conn.sap-prod.ashost}}.MBEW v
  ON m.MATNR = v.MATNR
  AND p.WERKS = v.BWKEY

WHERE 
  -- Only active materials
  m.LVORM IS NULL
  -- Exclude deleted materials
  AND (m.LOEKZ IS NULL OR m.LOEKZ = '')
  -- Only materials with stock or recently active
  AND (s.LABST > 0 OR p.MMSTA = 'A')

ORDER BY m.MATNR, p.WERKS, s.LGORT

Step 3: Endpoint Configuration

# sqls/materials.yaml
# ═══════════════════════════════════════════════════════════════
# Materials Inventory API Endpoint
# GET /materials/?plant=1000&material_type=FERT
# ═══════════════════════════════════════════════════════════════

url-path: /materials/

description: |
  Get material inventory data from SAP ERP with millisecond response times.
  Data is refreshed hourly from SAP production system.

authentication:
  required: true
  methods: [jwt, api-key]
  roles: [inventory-user, inventory-admin]

# ───────────────────────────────────────────────────────────────
# Caching Configuration (Essential for SAP)
# ───────────────────────────────────────────────────────────────
cache:
  enabled: true
  table: sap_materials_inventory
  schema: cache
  schedule: 60m              # Refresh every hour
  on_startup: true           # Load cache on startup
  persist: true              # Persist cache to disk
  template_file: materials_cache.sql

# ───────────────────────────────────────────────────────────────
# Request Parameters
# ───────────────────────────────────────────────────────────────
request:
  - field-name: plant
    field-in: query
    description: Plant code (e.g., 1000, 2000)
    required: false
    validators:
      - type: string
        pattern: '^\d{4}$'
    example: "1000"
  
  - field-name: material_type
    field-in: query
    description: Material type (FERT=Finished goods, HALB=Semi-finished, ROH=Raw materials)
    required: false
    validators:
      - type: string
        enum: [FERT, HALB, ROH, HIBE]
    example: "FERT"
  
  - field-name: material_group
    field-in: query
    description: Material group code
    required: false
  
  - field-name: stock_status
    field-in: query
    description: Stock availability status
    required: false
    validators:
      - type: string
        enum: [IN_STOCK, LOW_STOCK, OUT_OF_STOCK]
  
  - field-name: search
    field-in: query
    description: Search in material number or description
    required: false
    validators:
      - type: string
        min_length: 3
        max_length: 100
  
  - field-name: limit
    field-in: query
    description: Maximum number of results
    required: false
    validators:
      - type: int
        min: 1
        max: 1000
    default: 100
  
  - field-name: offset
    field-in: query
    description: Number of results to skip (pagination)
    required: false
    validators:
      - type: int
        min: 0
    default: 0

# ───────────────────────────────────────────────────────────────
# SQL Template
# ───────────────────────────────────────────────────────────────
template-source: materials.sql
connection:
  - sap-prod

# ───────────────────────────────────────────────────────────────
# MCP Tool Configuration (for AI agents)
# ───────────────────────────────────────────────────────────────
mcp-tool:
  name: get_sap_materials
  description: |
    Query SAP ERP materials inventory data. Returns material master information,
    stock levels, pricing, and availability status.
    
    Use this tool when you need to:
    - Check material availability and stock levels
    - Get material descriptions and specifications
    - Find materials by plant or material type
    - Analyze inventory across locations
  
  examples:
    - description: Get all finished goods in plant 1000
      parameters:
        plant: "1000"
        material_type: FERT
        limit: 50
    
    - description: Find materials with low stock
      parameters:
        stock_status: LOW_STOCK
        limit: 20
    
    - description: Search for specific material
      parameters:
        search: "pump"
        limit: 10

Step 4: API Query Template

-- sqls/materials.sql
-- ═══════════════════════════════════════════════════════════════
-- Fast queries against materialized cache
-- ═══════════════════════════════════════════════════════════════

SELECT 
  material_number,
  material_description,
  material_type,
  material_group,
  plant,
  storage_location,
  unrestricted_stock,
  quality_inspection_stock,
  blocked_stock,
  available_stock,
  stock_status,
  base_unit_of_measure,
  standard_price,
  price_unit,
  total_stock_value,
  mrp_controller,
  planned_delivery_time,
  cache_updated_at
FROM sap_materials_inventory
WHERE 1=1

{{#params.plant}}
  AND plant = '{{params.plant}}'
{{/params.plant}}

{{#params.material_type}}
  AND material_type = '{{params.material_type}}'
{{/params.material_type}}

{{#params.material_group}}
  AND material_group = '{{params.material_group}}'
{{/params.material_group}}

{{#params.stock_status}}
  AND stock_status = '{{params.stock_status}}'
{{/params.stock_status}}

{{#params.search}}
  AND (
    material_number LIKE '%{{params.search}}%'
    OR LOWER(material_description) LIKE LOWER('%{{params.search}}%')
  )
{{/params.search}}

ORDER BY 
  plant, 
  material_number

LIMIT {{params.limit}}
OFFSET {{params.offset}}

Step 5: Environment Configuration

# .env (NEVER commit to git)
# ═══════════════════════════════════════════════════════════════
# SAP ERP Connection
# ═══════════════════════════════════════════════════════════════
export SAP_HOST="sap-prod.company.com"
export SAP_SYSNR="00"
export SAP_CLIENT="100"
export SAP_USER="RFC_API_USER"
export SAP_PASSWORD="secure-sap-password"

# ═══════════════════════════════════════════════════════════════
# Security
# ═══════════════════════════════════════════════════════════════
export JWT_SECRET="your-super-secret-jwt-key-min-32-chars"

# ═══════════════════════════════════════════════════════════════
# Load environment
# ═══════════════════════════════════════════════════════════════
source .env

Step 6: Run flAPI

# Install flAPI
curl -sSL https://get.flapi.io | sh

# Start server
flapii serve

# Output:
# [INFO] Loading configuration...
# [INFO] Connecting to SAP ERP...
# [INFO] Initializing cache: sap_materials_inventory
# [INFO] Cache refresh scheduled: every 60 minutes
# [INFO] Server running on http://0.0.0.0:8080

Step 7: Test the API

Basic Query

curl -H "Authorization: Bearer $TOKEN" \
  "http://localhost:8080/materials/?limit=5"

Response:

{
  "data": [
    {
      "material_number": "000000000010000001",
      "material_description": "Centrifugal Pump Model X100",
      "material_type": "FERT",
      "material_group": "PUMPS",
      "plant": "1000",
      "storage_location": "0001",
      "unrestricted_stock": 45.0,
      "quality_inspection_stock": 0.0,
      "blocked_stock": 2.0,
      "available_stock": 45.0,
      "stock_status": "IN_STOCK",
      "base_unit_of_measure": "EA",
      "standard_price": 1250.00,
      "price_unit": 1,
      "total_stock_value": 56250.00,
      "mrp_controller": "001",
      "planned_delivery_time": 14,
      "cache_updated_at": "2024-01-15T10:00:00Z"
    }
  ],
  "metadata": {
    "total": 2847,
    "limit": 5,
    "offset": 0
  }
}

Filter by Plant

curl -H "Authorization: Bearer $TOKEN" \
  "http://localhost:8080/materials/?plant=1000&material_type=FERT"

Search Materials

curl -H "Authorization: Bearer $TOKEN" \
  "http://localhost:8080/materials/?search=pump&limit=10"

Check Low Stock

curl -H "Authorization: Bearer $TOKEN" \
  "http://localhost:8080/materials/?stock_status=LOW_STOCK"

Performance Comparison

Method	Query Time	Concurrent Users	SAP Load
Direct SAP RFC	10-30 seconds	5-10	High
flAPI Cached	1-5ms	10,000+	Minimal (hourly refresh)

Cost Savings:

• SAP RFC load reduced by 99%
• Response time improved by 10,000x
• Can serve millions of API calls with single hourly SAP query

AI Agent Integration (MCP)

// Claude/GPT can now query SAP inventory
const response = await mcp.call("get_sap_materials", {
  plant: "1000",
  stock_status: "LOW_STOCK"
});

console.log(`Found ${response.data.length} low stock materials`);

Next Steps

• SAP ERP Connection Guide: Detailed SAP ERP setup
• SAP BW Connection: Connect to SAP BW/4HANA
• Caching Strategy: Understand optimization techniques
• Caching Setup: Configure caching for SAP
• Authentication: Secure production deployment
• BigQuery Example: Cloud warehouse alternative
• Deployment: Deploy to production