SAP HANA Integration

The system integrates with SAP HANA database to validate item availability in real-time.

Overview

SAP HANA integration provides:

• ✅ Real-time item availability checking
• ✅ Inventory status validation
• ✅ Sales and purchasing flag verification
• ✅ Automated SKU verification during upload
• ✅ Missing item identification

Configuration

Prerequisites

1. SAP HANA server access credentials
2. Network connectivity to SAP HANA server
3. Python hdbcli driver installed
4. Database schema access permissions

Environment Setup

Add to .env file:

HANA_HOST=172.16.25.3
HANA_PORT=30015
HANA_USER=your_username
HANA_PASSWORD=your_password
HANA_SCHEMA=SGI_LIVE_HANA

Test Connection

cd /mnt/data/punch_quotation
source venv/bin/activate
python -c "
from hdbcli import dbapi
import os
from dotenv import load_dotenv

load_dotenv()

try:
    conn = dbapi.connect(
        address=os.getenv('HANA_HOST'),
        port=int(os.getenv('HANA_PORT', 30015)),
        user=os.getenv('HANA_USER'),
        password=os.getenv('HANA_PASSWORD')
    )
    print('✓ Successfully connected to SAP HANA')
    conn.close()
except Exception as e:
    print(f'✗ Connection failed: {e}')
"

Database Schema

OITM Table Structure

The system queries the OITM table (Item Master Data):

Query Template

SELECT
    T1."ItemCode",
    T1."ItemName",
    CASE WHEN T1."validFor" = 'Y' THEN 'Yes' ELSE 'No' END AS "Is_Active",
    CASE WHEN T1."InvntItem" = 'Y' THEN 'Yes' ELSE 'No' END AS "Inventory",
    CASE WHEN T1."SellItem" = 'Y' THEN 'Yes' ELSE 'No' END AS "Sales",
    CASE WHEN T1."PrchseItem" = 'Y' THEN 'Yes' ELSE 'No' END AS "Purchasing"
FROM
    SGI_LIVE_HANA."OITM" T1
WHERE
    T1."ItemCode" IN ('AAP-001', 'AAP-002', ...)

Automatic SKU Checking

When Does It Run?

SKU checking happens automatically during file upload:

1. File Upload - User uploads Excel file
2. Data Extraction - System extracts item codes
3. De-duplication - Remove duplicate item codes
4. SAP Query - Batch query to SAP HANA
5. Result Mapping - Map results to items
6. Display - Show in Item Check tab

Process Flow

Upload File
    ↓
Extract Items (Column C - KODE ITEM)
    ↓
Filter Non-Items (dates, notes, etc.)
    ↓
Collect Unique Item Codes
    ↓
Query SAP HANA (OITM table)
    ↓
Map Results to Items
    ↓
Display in Item Check Tab

Performance

• Small uploads (< 50 unique items): ~2-3 seconds
• Medium uploads (50-200 items): ~5-10 seconds
• Large uploads (200+ items): ~15-30 seconds

Query performance depends on:

• Network latency to SAP HANA
• Number of unique items
• SAP HANA server load

Item Check Results

Available Items (Green ✓)

Item found in SAP HANA with all flags:

✓ Item Code: AAP10-40-N12-A18
✓ Item Name: ANGULAR PIN
✓ Active: Yes
✓ Inventory: Yes
✓ Sales: Yes
✓ Purchasing: Yes

Missing Items (Red ✗)

Item not found in SAP HANA:

✗ Item Code: NEW-ITEM-001
- Item Name: Missing
- Active: -
- Inventory: -
- Sales: -
- Purchasing: -

Inactive Items (Yellow ⚠)

Item exists but marked inactive:

⚠ Item Code: OLD-ITEM-001
⚠ Item Name: OBSOLETE PART
⚠ Active: No
✓ Inventory: Yes
✓ Sales: No
✓ Purchasing: No

Data Filtering

System automatically excludes:

Non-Item Entries

• Dates: 2025-10-23, 23-10-2025
• Delivery notes: 11 HARI KERJA, 15 working days
• Text notes: CATATAN, NOTE, ESTIMASI PENGIRIMAN
• Totals: TOTAL, SUBTOTAL, TAX
• Company names: PT. PUNCH, PT. SOMAGEDE

Filter Logic

# Excluded patterns
exclude_patterns = [
    'note', 'catatan', 'estimasi', 'pengiriman',
    'nett price', 'masa berlaku', 'delivery',
    'hari kerja', 'working day', 'total', 'tax'
]

# Date pattern
date_pattern = r'^\d{4}-\d{2}-\d{2}$|^\d{2}-\d{2}-\d{4}$|^\d{1,3}\s*hari'

Error Handling

Connection Errors

Symptoms:

• "SAP Connection Error" message
• No item check data displayed
• Processing completes but no SAP info

Causes:

1. SAP HANA server unreachable
2. Invalid credentials
3. Network firewall blocking port 30015
4. Schema not accessible

Solutions:

1. Test connection manually
2. Verify credentials in .env
3. Check network connectivity
4. Contact SAP administrator

Timeout Errors

Symptoms:

• Processing hangs during SAP query
• "Timeout" error in logs

Causes:

• Too many items in single query
• SAP HANA server overloaded
• Network latency issues

Solutions:

1. Split large files into smaller batches
2. Retry during off-peak hours
3. Increase timeout in settings

Schema Errors

Symptoms:

• "Table or view not found"
• Permission denied errors

Causes:

• Wrong schema name
• User lacks read permissions
• OITM table not accessible

Solutions:

1. Verify schema name: HANA_SCHEMA=SGI_LIVE_HANA
2. Request read access from SAP admin
3. Test with different schema if available

Sync Inventory Feature

Manual Sync

After initial upload, you can re-sync:

1. Go to Session Detail page
2. Navigate to "Item Check" tab
3. Click "Resync Missing Items" button
4. System re-queries SAP HANA
5. Updates displayed immediately

Use Cases

• New items added to SAP HANA after upload
• Initial connection failed
• Item data updated in SAP
• Verify current inventory status

Logging

Log Locations

SAP queries logged in:

• logs/extraction.log - Detailed extraction logs
• logs/django.log - General application logs

Log Entries

INFO: Processing 145 unique descriptions for SAP lookup
INFO: SAP HANA connection established successfully
INFO: Executing SAP query for 145 items
INFO: ✓ SAP query returned 132 rows for 145 items
INFO: ✓ SAP lookup completed. Found 132 items in SAP, 13 missing

View Logs

# Real-time extraction logs
./view_logs.sh extraction

# All logs
./view_logs.sh all

Best Practices

Connection Management

1. Use connection pooling for high-volume uploads
2. Close connections after queries complete
3. Handle timeouts gracefully with retries
4. Cache results during session (already implemented)

Query Optimization

1. Batch queries instead of individual lookups
2. Use IN clause for multiple items
3. Limit result columns to necessary fields only
4. Index ItemCode column for faster lookups

Error Recovery

1. Log all errors with full context
2. Continue processing even if SAP unavailable
3. Allow manual resync after fixing issues
4. Display partial results when some queries fail

Security

Credentials

• Store in .env file (never commit to Git)
• Use read-only SAP user account
• Limit schema access to OITM table only
• Rotate passwords regularly

Network

• Use VPN for remote access
• Restrict port 30015 to application server
• Monitor failed connection attempts
• Log all SAP queries for audit

Troubleshooting

Item Not Found but Exists in SAP

Check:

1. Item code spelling matches exactly
2. No extra spaces in item code
3. Correct schema configured
4. Item in active status

All Items Showing Missing

Check:

1. SAP HANA connection successful
2. Schema name correct
3. User has read permissions
4. Network connectivity

Slow Query Performance

Solutions:

1. Reduce number of items per query
2. Check SAP HANA server load
3. Verify network latency
4. Consider caching strategy

Next Steps

• Item Availability Check
• Configuration Guide
• Troubleshooting