AI Data Catalog / DeliveryOperationAnalyticsView
route

DeliveryOperationAnalyticsView

AI-ready delivery lifecycle view that combines planning, execution, and logistics capacity analysis for distribution operations including orders, volume, weight, and vehicle utilization.
delivery logistics distribution route-optimization
Purpose

This view provides a complete lifecycle of delivery operations including planning, execution, and completion phases. It combines order-level details with vehicle capacity and delivery performance metrics for AI-driven logistics optimization.

Business Rules
  • Each record represents a delivery operation with header + detail structure.
  • Multiple orders (SalesDocID) can exist within a single delivery operation (SPID).
  • OrderAmount represents invoice value per order.
  • TahvilAmount represents delivered amount per order.
  • SumOrderAmount is aggregated total order value per delivery.
  • Vol and Weight represent total volume (m³) and weight (kg) of delivery load.
  • DistTime is estimated delivery duration in HH:MM format.
  • Truck capacity is defined by MaxWeight and MaxVolume.
Relationships

This view represents the logistics execution layer of the system. It connects delivery operations with orders, customers, financial summaries, inventory and returns to enable full distribution lifecycle analysis and route optimization.

Related View Join Keys Description
SalesInvoiceView SalesDocID = OrderID Links delivery execution with invoice lines to analyze fulfillment accuracy, dispatch behavior and product-level delivery performance.
OrderFinancialSummaryView SalesDocID = OrderID Connects delivery execution with financial realization including revenue, discounts, tax and outstanding balance.
OrderReturnSummaryView SalesDocID = OrderID, MPID = VisitorMPID Evaluates return patterns based on delivered orders and identifies operational or logistics-related return issues.
OrderCollectionAnalyticsView SalesDocID = OrderID Analyzes how delivery completion impacts payment speed, receivable aging and collection performance.
InventoryView ProductId, BranchID Links delivered products with stock reduction and supports inventory replenishment and supply planning.
CustomerVisitLogsView CustID Connects actual customer visits with delivery execution to measure engagement and fulfillment success correlation.
SellerVisitPathCustomersView CustID, SellerID Compares planned customer visit routes with actual delivery execution coverage.
CustAssignToSellerView CustID, SellerID Ensures delivery operations are aligned with assigned sales ownership and territory structure.
DeliveryOperationAnalyticsView (Current) SPID (Delivery Batch), SalesDocID = OrderID, MPID Core logistics execution dataset combining route planning, load optimization, delivery status and execution performance.
Primary Logistics Join Strategy
  • SPID → Delivery batch / route identifier (grouping multiple orders)
  • SalesDocID = OrderID → Core lifecycle link between delivery and order systems
  • MPID → Delivery agent performance and workload distribution
  • CustID → Customer-level delivery, visit and financial correlation
  • ProductId → Inventory consumption and product movement tracking
  • SPState → Delivery lifecycle state (planning → execution → completion)
Delivery States (SPState)
  • 0 → Planning phase (in preparation)
  • 1 → Ready for dispatch
  • 2 → Completed delivery
  • >2 → Post-processing (returns, settlement, finalization)
  • 10 → Canceled delivery plan
cancel
Cancellation Note

State 10 indicates a fully canceled delivery plan and should be excluded from performance KPIs unless analyzing failure rates.

Key Definitions
  • SPID: Delivery operation ID
  • SPDate: Delivery schedule date
  • MPID / MPName: Delivery agent
  • SalesDocID: Invoice or order number
  • OrderAmount: Order value per invoice
  • TahvilAmount: Delivered amount per invoice
  • OrderCount: Number of orders in delivery run
  • SumOrderAmount: Total value of all orders in run
  • Vol: Total volume (m³)
  • Weight: Total weight (kg)
  • DistTime: Planned delivery duration (HH:MM)
  • TruckID / TruckDesc: Vehicle information
  • MaxWeight / MaxVolume: Vehicle capacity limits
Format Notes
schedule
Time Format

DistTime is stored as STRING in format HH:MM. It represents planned duration of delivery operations. All date fields in this view are stored as STRING in YYYY/MM/DD format (Gregorian calendar). They must be converted before performing any date-based calculations or filtering.

Usage
  • Delivery route optimization
  • Vehicle capacity utilization analysis
  • Logistics performance monitoring
  • Sales distribution efficiency tracking
  • AI-based dispatch planning
  • Delivery delay prediction
AI Insight
auto_awesome
Logistics Intelligence Layer

This view acts as a core dataset for AI-driven logistics optimization including route efficiency, load balancing, vehicle utilization, and delivery performance scoring.