I. Scenario Overview & Architectural Context

This document provides a comprehensive technical and functional evaluation of the HotWax Order Routing Engine (OrderRouting) against the operational specifications of a Premium Disposable Aluminum Products Business.

The business relies on a high-velocity, bulky-product supply chain utilizing two primary US warehouses:

1. East Coast Warehouse (NJ, serving primary East Zone density)
2. West Coast Warehouse (CA, serving primary West Zone density)

As online sales expand to retail customers, the company requires a routing framework that minimizes shipping distances, enforces single-facility fulfillment, gracefully handles inventory depletion fallbacks, scales automatically to new warehouses, and supports an intelligent Transfer Order Suggestion (TOS) model.

II. Action -> Reaction -> Workflow Sequence

This sequence outlines how an ingested customer order is processed, filtered, evaluated, and assigned to a fulfillment facility.

Action-Reaction Matrix​

🎮 Interactive Routing Simulator​

Try out real-time routing runs using our high-fidelity sandboxed simulator. Configure order payloads, toggle constraints, and analyze step-by-step Moqui engine execution logs.

Core Workflow Sequence Diagram​

III. State Transition Model

The state of an order during brokering is driven primarily by the facilityId field of its OrderItemShipGroup entity, while its administrative statuses remain ORDER_APPROVED and ITEM_APPROVED.

IV. Entity Relationship Diagram (ERD)

The order routing engine sits as a hybrid intelligence layer. Core transaction data rests in Apache OFBiz entities, while routing rules, configurations, and run statistics reside in Moqui entities.

V. Feasibility Analysis (Achievable vs. Gaps)

Based on an exhaustive review of the OrderRouting component's source code, SQL templates (InventorySourceSelector.sql.ftl, EligibleOrdersQuery.sql.ftl), and services (OrderRoutingServices.xml), we have analyzed the feasibility of your specific fulfillment rules.

1. What is Achievable (Out-of-the-Box)​

• Proximity-Based Routing (Rule 1 - Nearest Warehouse Serving Order):
  • Implementation: Fully supported. The SQL engine calculates geographical distance using:

    ST_Distance_Sphere(point(fpa.LONGITUDE, fpa.LATITUDE), point(opa.LONGITUDE, opa.LATITUDE)) * conversionFactor AS distance
    

    It compares the customer's delivery postal address contactMechId with the facility's primary location coordinates. If the rule's inventorySortByList contains distance, warehouses are automatically sorted by proximity.
• Single Facility Fulfillment / No Partial Shipments (Rule 2):
  • Implementation: Fully supported. By configuring the routing rule with assignmentEnumId = 'ORA_SINGLE', the query utilizes:

    ifnull((select distinct 'N' from order_item oi1 LEFT join product_facility pf1 ... where pf1.last_inventory_count < quantity ...),'Y') as rank_by_order_at_facility
    

    The routing engine filters out any warehouse that cannot fulfill 100% of the ship group items. No partial shipments are allowed; the order will either route in full to a single facility or fail brokering.
• Depletion Fallback (NY/East Coast to West Coast):
  • Implementation: Fully supported. Because the query sorts by distance ASC but filters by rank_by_order_at_facility = 'Y', if the closest warehouse (East Coast) is missing even one item, it is excluded. The fallback warehouse (West Coast) is automatically selected as it is next in proximity that satisfies 100% stock availability.
• Warehouse Scaling (Rule 4 - Adding Warehouses with Same Activities):
  • Implementation: Fully supported. The proximity calculation is dynamic and spatial. Adding an arbitrary number of warehouses requires zero code changes. You only need to create a Facility record, map it to a primary PostalAddress with latitude/longitude coordinates, and register it to the ProductStoreFacility association.

2. What is NOT Achievable (Gaps & Functional Debt)​

While the core brokering features exist, your specific constraints reveal two major functional gaps:

❌ Gap A: Rule 3 (Prioritizing LOWER Inventory Turnover Ratio)​

• The Conflict: The existing system contains a weekOfSupply sorting mechanism. However, it is hardcoded to sort in descending order:

  week_of_supply is null asc, week_of_supply desc
  

  This standard design prioritizes facilities with the maximum weeks of supply left (to prevent fast stockouts at high-demand facilities). Your rule explicitly asks for the opposite: prioritize warehouses with lower rollover days (fewer weeks of supply left) to clean out stock or balance inventory turnout.
• The Solution: This is a minor code customization gap. We must introduce a new sort parameter or modify the template to support weekOfSupplyAsc (or lowerInventoryTurnover).

                  pf.SALES_VELOCITY IS NULL ASC,pf.SALES_VELOCITY ASC
                <#elseif 'weekOfSupply' == inventorySortByList>
                  <#if weekOfSupply?has_content && weekOfSupply &gt; 0>
                    week_of_supply is null asc,week_of_supply desc
                  </#if>
+               <#elseif 'lowerInventoryTurnover' == inventorySortByList>
+                 <#if weekOfSupply?has_content && weekOfSupply &gt; 0>
+                   week_of_supply is null asc,week_of_supply asc
+                 </#if>
                <#else>
                  ${inventorySortBy!}

❌ Gap B: Weekly inter-facility Transfer Order Suggestion (TOS)​

• The Conflict: The OrderRouting component is strictly an Order Brokering Engine. It does not possess a predictive replenishment or transfer suggestion engine out of the box. It cannot natively analyze multi-item unfillable backlogs in the UNFILLABLE_QUEUE and calculate optimal SKU-level bulk transfer suggestions between warehouses.
• The Solution (Proposed Architecture): We must design a standalone weekly batch service (suggest#InterWarehouseTransfers) leveraging existing Apache OFBiz entities: Requirement (type INTERNAL_REQ), InventoryTransfer, and Shipment.

VI. Antigravity Challenge: Thinking Around Corners

5 Critical Critiques of the Proposed Fulfillment Strategy​

[!IMPORTANT] The requested design is functionally direct, but structurally flawed under real-world operating conditions. Below are 5 "around-the-corners" critiques challenging your core operational assumptions to protect margins and customer experience.

1. The Risk of the "LOWER Inventory Supply Sort"​

• The Fallacy: Prioritizing warehouses with lower weeks of supply (Rule 3) means you are deliberately routing orders to the facility that is fastest to deplete.
• The Trap: If both East and West coast warehouses are equal days away from a customer, but the East Coast warehouse only has 10 days of supply remaining while the West Coast has 50 days of supply, Rule 3 will force fulfillment from the East Coast. This will trigger premature stockouts on the East Coast for orders that cannot be served by the West Coast (due to proximity limits or delivery day SLAs), forcing costly cross-country shipments for future orders.
• Strategic Recommendation: Turn this rule upside down. Sort by week_of_supply DESC (standard HotWax behavior) to maintain balanced regional buffers, unless you are running a specific clearance sale or warehouse decommissioning event.

2. The Bulky-Margin Paradox of weekly Inter-Warehouse TOS​

• The Fallacy: Assuming that suggesting weekly SKU-level transfers between warehouses is an optimal way to fulfill unfillable multi-item orders.
• The Trap: Aluminum disposable products (trays, foil pans, containers) are low-margin, high-volume, and extremely bulky. Inter-warehouse freight is priced by volume (freight class / dimensional weight). If you transfer 2 pallets of aluminum lids from West Coast to East Coast just to resolve 15 unfillable orders that were stuck due to the "No Partial Shipments" rule, the freight and handling cost of the transfer will far exceed the profit margins of those orders.
• Strategic Recommendation: Rather than relying on constant, costly inter-facility transfers, utilize the TOS engine only for high-value items, or implement a regional demand-driven safety stock threshold directly in ProductFacility.minimumStock to prevent these imbalances from happening in the first place.

3. The Unfillable Queue Churn Trap​

• The Fallacy: Orders that cannot be fulfilled from a single warehouse are pushed to the UNFILLABLE_QUEUE until a weekly TOS is executed.
• The Trap: In modern e-commerce, a customer order sitting unfulfilled in a backlog for up to a week waiting for an inter-warehouse truck to arrive will result in massive customer churn and cancelled orders.
• Strategic Recommendation: Implement an SLA-based bypass. If an order sits in the UNFILLABLE_QUEUE for more than 48 hours, override the "No Partial Shipments" rule, split the order into two ship groups, ship them from both warehouses, and absorb the split shipping cost as a customer retention measure.

4. The Proximity vs. SLA Carrier Blindspot​

• The Fallacy: Proximity-based routing (ST_Distance_Sphere) is a direct proxy for shipping speed and cost.
• The Trap: Geographical distance does not equal shipping transit time or carrier zone costs. A warehouse that is geographically closer to a customer might not have a direct carrier route, resulting in 3 days transit, whereas a warehouse slightly further away sits next to an FedEx/UPS regional hub and can deliver in 1 day at a lower zone rate.
• Strategic Recommendation: Transition from static coordinate distance sorting to dynamic carrier API zone pricing and SLA transit-time lookup in your routing rule configurations.

5. Dual Database Synchronization Lag​

• The Fallacy: The system can seamlessly manage real-time inventory queries across Apache OFBiz and Moqui.
• The Trap: HotWax OMS utilizes a split architecture where core inventory transactions (shipments, receipts, adjustments) update the OFBiz database, but the Moqui Order Routing engine runs asynchronously on cached or synchronized views. If high-velocity online sales are running, a lag in inventory synchronization between OFBiz and Moqui can lead to double-brokering (allocating the same physical stock to different orders at both warehouses), leading to rejection loops and inventory variance write-offs.
• Strategic Recommendation: Enforce strict real-time transactional inventory checks (entity-find without cache) for high-velocity SKUs inside the InventorySourceSelector routing run.