Complete Process Flow

The LPO processing follows a 9-step workflow:

Process Steps

1. Document Ingest

User Upload: User uploads PDF/Image via frontend interface
Email Integration: System receives documents via email attachments
WhatsApp Integration: Documents received through WhatsApp channels
Storage: Files uploaded to CDN with organized path structure
Database: LPO record created with PENDING status

2. Validation

Frontend Validation: File type, size, and page count validation during upload
Backend Validation: Comprehensive validation for all document sources (frontend, email, WhatsApp)
File Types: PDF, JPEG, PNG, JPG
File Size: Maximum 10MB
Page Count: Maximum 35 pages for PDFs
Credit Check: Validate available page credits

3. OCR Processing

Service: External Gemini (LLM) + OCR service for intelligent data extraction
Status Update: Document marked as PROCESSING
Credit Hold: Credits held during processing
AI Enhancement: LLM improves extraction accuracy and understanding

4. Data Callback

Webhook: OCR service returns extracted data via callback
Status Update: Document marked as PROCESSED or FAILED
Credit Hold: Credits consumed after processing
Parsing: Extract customer info, product codes, quantities, prices

5. Product Matching

5-Tier Matching Hierarchy: Apply matching in strict order for accuracy
Direct Product ID: Use provided product_id directly if available
Product Code: Match item_code/bar_code against product_code/product_valcode
Customer Account Mapping: Match against customer-specific product code mappings
MySQL Full-text Search: Search product descriptions using MySQL FULLTEXT
MeiliSearch Fallback: Intelligent fuzzy matching with confidence scores
Unmatched Handling: Store items without product mapping, provide suggestions for manual review

6. Price Mapping

Customer/Outlet Pricing: Apply appropriate pricing based on customer and outlet
LPO Price Default: Option to default to prices specified in the LPO document
Hierarchy: Manual (when set) → LPO → Customer → Default pricing
Override Support: Manual price adjustments when needed

7. UOM Conversion

Configured Units: Store each LPO line item with configured Unit of Measure
Outlet Configuration: Apply outlet-specific UOM preferences
Conversion Logic: Convert to largest/smallest/custom UOMs as needed
Quantity Adjustment: Adjust quantities and prices based on UOM conversion

8. Review & Edit

User Interface: User reviews extracted data for accuracy
Manual Editing: Corrections and adjustments where necessary
Validation: Ensure all required fields are completed
Change Tracking: Log all user modifications for audit trail

9. Order Conversion

Sales Order Creation: Convert LPO to actual sales order
Delivery Date: Set delivery date where necessary
Final Validation: Ensure all data is complete before conversion
Order Linking: Link LPO to created sales order
Status Update: Mark as CONVERTED (immutable)

Process Flow Diagram

graph TD
    A1[Frontend Upload] --> B[Validation]
    A2[Email Attachment] --> B[Validation]
    A3[WhatsApp Document] --> B[Validation]
    B --> C[OCR Processing - Gemini + OCR]
    C --> D[Data Callback]
    D --> E[Product Matching]
    E --> F[Price Mapping]
    F --> G[UOM Conversion]
    G --> H[Review & Edit]
    H --> I[Order Conversion]
    
    B --> J[Failed - Validation]
    C --> K[Failed - OCR]
    D --> L[Failed - Callback]
    E --> M[Failed - Matching]
    F --> N[Failed - Pricing]
    G --> O[Failed - UOM]
    H --> P[Failed - Review]
    I --> Q[Success - Converted]

Document Status Lifecycle

Documents progress through a strict one-way status flow:

Status Flow

PENDING → PROCESSING → PROCESSED → CONVERTED
    ↓         ↓           ↓           ↓
  FAILED ← FAILED ← FAILED       ARCHIVED

Status Definitions

PENDING

Description: Document uploaded, awaiting OCR processing
Key Columns: sat_reference_id, attachment_link, status, created_by, source
File Storage: Files stored in CDN (local_purchase_orders/{client_code}/)
Business Logic: Initial validation completed, ready for OCR processing
Required Configs: CLIENTCODE for storage path organization

PROCESSING

Description: Currently being processed by OCR service
Key Columns: status, page_count, email_address, entry_time
Business Logic: Document submitted to external OCR service
Callback Handling: OCR service posts results to callback endpoint
Timeout: 5-minute automatic timeout detection

PROCESSED

Description: OCR completed successfully, data extracted
Key Columns: status, prefer_lpo_prices, converted_uoms, customer_set_id, outlet_id
Business Logic:
- Parse extracted data and store line items in eva_ocr_lpo_items
- Apply product matching hierarchy
- Apply UOM conversion and price mapping
- Log all changes in eva_ocr_lpo_item_histories
User Actions: Review, edit, and prepare for conversion

CONVERTED

Description: Successfully converted to sales order
Key Columns: order_id links to created sales order
Business Logic:
- Validate all items matched and quantities/prices confirmed
- Create sales order and update order_id
- Status becomes immutable
Final State: Cannot be changed once converted

FAILED

Description: Processing failed at any stage
Key Columns: status, status_reason
Business Logic:
- Can occur due to validation errors, OCR failures, timeouts
- Status reason logged for traceability
- Manual retry available
Recovery: Resync functionality available

ARCHIVED

Description: Old documents moved to archive
Business Logic:
- Documents in PENDING status for extended periods
- Automated monthly archive process
- Archive notification sent to users
Retention: Maintains data for audit purposes

Status Transition Rules

Allowed Transitions

PENDING → PROCESSING (OCR submission)
PROCESSING → PROCESSED (successful OCR)
PROCESSED → CONVERTED (order creation)
Any status → FAILED (error occurrence)
PENDING → ARCHIVED (timeout/cleanup)

Forbidden Transitions

No backward movement (e.g., PROCESSED cannot return to PENDING)
CONVERTED status is immutable
ARCHIVED documents should not be reactivated

Business Logic for Item Storage

Item Creation Process

When transitioning from PROCESSING to PROCESSED:

Parse OCR Data: Extract line items from callback data
Create Items: Store in eva_ocr_lpo_items table
Product Matching: Apply 5-tier matching hierarchy
UOM Assignment: Set based on outlet configuration
Price Mapping: Apply outlet-specific or default prices
History Logging: Record all changes for audit trail

Product Matching Hierarchy

Direct Product ID: Use provided product_id directly
Product Code: Match item_code/bar_code against product_code/product_valcode
Description Match: Fuzzy matching using cleaned product names
Customer Mapping: Search EvaDocsProductCode for customer-specific codes
AI Fallback: MeiliSearch for intelligent suggestions with confidence scores