Skip to main content

Example 01: MRP Order Creation Primary Workflow

Overview​

The MRP order creation workflow is the core business process of this dashboard, implementing sophisticated material requirements planning calculations to determine optimal purchase order quantities based on safety stock levels, minimum quantities, and current demand.

Business Context​

Manufacturing and distribution companies need to maintain optimal inventory levels to meet customer demand while minimizing carrying costs. This dashboard automates the complex calculations required to determine when and how much to order from suppliers.

Workflow Implementation​

Step 1: MRP Calculation​

The system uses the custom CG_InventoryOrdering_View to calculate reorder requirements:

-- Safety stock based calculation
IIf(
    QtyOnHand - ([QtyInTransit] + [QtyAllocated] + [QtyAllocatedWip]) < SafetyStockQty,
    IIf(
        ReOrderQty > 0,
        ReOrderQty * CEILING((SafetyStockQty - (QtyOnHand - ([QtyInTransit] + [QtyAllocated] + [QtyAllocatedWip]))) / ReOrderQty), 
        SafetyStockQty - (QtyOnHand - ([QtyInTransit] + [QtyAllocated] + [QtyAllocatedWip]))
    ),
    0
) AS QtyToOrder

This calculation:

  1. Determines available quantity (on-hand minus allocations)
  2. Compares against safety stock level
  3. Calculates shortage if below safety stock
  4. Rounds up to reorder quantity multiple if specified
  5. Returns exact shortage if no reorder quantity defined

Step 2: User Selection and Filtering​

Users can filter and select items for ordering:

// WarehouseOrderingViewModel.cs
public IQueryable<WarehouseOrderingListItem> GetWarehouseQueryable(
    WarehouseSearchFilter searchFilter)
{
    var queryable = from x in _sysproDataContext.CG_InventoryOrdering_View
                    select new WarehouseOrderingListItem
                    {
                        Warehouse = x.Warehouse,
                        Supplier = x.Supplier,
                        StockCode = x.StockCode,
                        OrderQty = x.QtyToOrder ?? 0,
                        // ... additional mappings
                    };

    // Apply user filters
    if (!string.IsNullOrWhiteSpace(searchFilter.SelectedSupplier))
    {
        queryable = queryable.Where(x => 
            x.Supplier.Contains(searchFilter.SelectedSupplier));
    }

    return queryable;
}

Step 3: Order Consolidation​

Selected items are grouped into purchase orders:

// InvOrderingService.cs
public IEnumerable<SummaryOrder> CreateOrderSummary(
    IEnumerable<InvOrderingOrderSummaryListItem> items,
    string buyer,
    bool orderByWarehouse,
    bool orderByDueDate)
{
    // Dynamic grouping based on settings
    var supplierGroups = orderByWarehouse ?
        items.GroupBy(x => new { x.Supplier, x.Warehouse }) :
        items.GroupBy(x => x.Supplier);

    foreach (var group in supplierGroups)
    {
        var order = new SummaryOrder
        {
            Supplier = group.Key.Supplier,
            Warehouse = orderByWarehouse ? group.Key.Warehouse : null,
            OrderLines = group.Select(ConvertToOrderLine).ToList()
        };
        
        summary.Add(order);
    }
}

Step 4: SYSPRO Integration​

Orders are posted to SYSPRO through the PORTOI business object:

// SysproPostService.cs
public IEnumerable<SummaryOrder> CreateOrders(
    IEnumerable<SummaryOrder> orders)
{
    foreach (var order in orders)
    {
        // Generate XML for SYSPRO
        var inputXml = GetInputXml(order);
        var paramXml = GetParamXml();
        
        // Post to SYSPRO
        var outputXml = PerformBusinessObjectPost(
            inputXml, paramXml, "PORTOI");
            
        // Parse response
        order.OrderNumber = ParseXmlOutput(outputXml);
    }
    return orders;
}

Step 5: Result Presentation​

Successfully created orders are displayed with details:

// InvOrderingCompletionViewModel.cs
public void SetOrderResult(CreateOrdersResult ordersResult)
{
    OrderSummaries = ordersResult.Orders;
    Buyer = ordersResult.Buyer;
    
    // Display success/failure status for each order
    foreach (var order in OrderSummaries)
    {
        if (!order.PostFailed)
        {
            // Show order number and line count
            DisplaySuccessfulOrder(order);
        }
        else
        {
            // Show error details
            DisplayFailedOrder(order);
        }
    }
}

Validation and Business Rules​

Pre-Order Validation​

  1. Supplier must be active (not on hold)
  2. Stock items must not be on hold
  3. User must have purchase order creation permissions
  4. Buyer must be selected

Order Processing Rules​

  1. Respect minimum order quantities
  2. Apply reorder quantity multiples
  3. Consider lead times for due dates
  4. Group by warehouse if configured

Error Handling​

  • Supplier on hold: Skip order, notify user
  • Stock validation failures: Remove invalid lines
  • SYSPRO posting errors: Log and display to user
  • Partial success: Show successful and failed orders

Performance Optimizations​

Database Query Optimization​

  • Custom view pre-calculates MRP requirements
  • Indexed on frequently queried columns
  • Efficient joins between related tables

Batch Processing​

  • Orders grouped to minimize SYSPRO calls
  • Parallel processing where possible
  • Async operations for UI responsiveness

Caching Strategy​

  • Cache supplier and warehouse lists
  • Store user preferences
  • Reuse SYSPRO session

Configuration Options​

MepSettings.json​

{
  "SettingName": "OrderByWarehouse",
  "SettingValue": "true",
  "Description": "Group orders by warehouse"
},
{
  "SettingName": "OrderByDueDate", 
  "SettingValue": "false",
  "Description": "Group orders by due date"
}

User Preferences​

  • Default buyer selection
  • Filter preferences
  • Page size for grids
  • Export format preferences

Testing Considerations​

Unit Testing​

  • MRP calculation accuracy
  • Order grouping logic
  • Validation rules

Integration Testing​

  • SYSPRO posting success
  • Error handling scenarios
  • Session management

User Acceptance Testing​

  • Workflow completeness
  • Performance under load
  • Error message clarity

Lessons Learned​

What Works Well​

  • Pre-calculated MRP view improves performance
  • Settings-driven grouping provides flexibility
  • Comprehensive error handling improves reliability

Challenges Overcome​

  • SYSPRO session timeout handling
  • Large dataset performance
  • Complex grouping requirements

Future Enhancements​

  • Suggested order scheduling
  • Automatic order approval workflow
  • Integration with forecasting systems