Thanks for all the great questions - let me provide comprehensive details on our implementation covering the automated reorder logic, inventory threshold monitoring, and purchase order creation:
Automated Reorder Logic - The Complete Formula:
Our reorder calculation evolved significantly from the simplified version I initially shared. Here’s the full logic that addresses demand variability and supplier reliability:
function calculateReorderQuantity(item) {
var avgDailyUsage = getAverageDailyUsage(item.id, 90); // 90-day rolling average
var forecastedDemand = getProductionOrderDemand(item.id, item.leadtime);
var effectiveDemand = Math.max(avgDailyUsage * item.leadtime, forecastedDemand);
var safetyStockFactor = item.custitem_safety_factor || 1.5; // Item-specific factor
var safetyStock = effectiveDemand * (safetyStockFactor - 1);
var reorderQty = effectiveDemand + safetyStock - item.quantityonhand - item.quantityonorder;
return Math.max(0, Math.ceil(reorderQty / item.orderquantity) * item.orderquantity);
}
Key enhancements from the basic version:
-
Dynamic Demand Forecasting: We compare 90-day historical average against actual production order requirements for the lead time window. The script uses whichever is higher, preventing stockouts when production ramps up beyond historical patterns. This addresses Raj’s question about demand volatility.
-
Item-Specific Safety Factors: As Sarah noted, the 1.5 multiplier isn’t one-size-fits-all. We created a custom item field (custitem_safety_factor) where procurement sets factors between 1.2 and 2.5 based on:
- Supplier reliability (1.2 for domestic reliable suppliers, 2.0 for overseas or unreliable)
- Demand volatility (1.3 for stable items, 2.5 for highly variable)
- Criticality to production (higher factors for single-source or production-blocking items)
-
Order Quantity Rounding: The final calculation rounds up to the supplier’s minimum order quantity or standard pack size. This prevents ordering 47 units when the supplier only sells in packs of 50.
Inventory Threshold Monitoring - Real-Time vs Scheduled:
We use a hybrid monitoring approach:
Scheduled Script (Every 4 Hours):
Processes all 300 critical items systematically, checking:
- Current on-hand quantity vs reorder point
- Open purchase orders and expected receipt dates
- Production order requirements for next 60 days
- Vendor status (active, on-hold, etc.)
The script maintains a custom record type (customrecord_reorder_queue) that tracks evaluation history:
record.create({
type: 'customrecord_reorder_queue',
values: {
custrecord_item: itemId,
custrecord_evaluated_date: new Date(),
custrecord_onhand_qty: onHand,
custrecord_reorder_qty: reorderQty,
custrecord_action_taken: actionTaken
}
}).save();
User Event Script (Real-Time):
For the 50 most critical items, we also have an afterSubmit script on inventory transactions that triggers immediate reorder evaluation when quantity drops below a critical threshold (typically 25% of reorder point). This catches emergency situations between scheduled runs.
Purchase Order Creation - Approval Workflow:
To address Tom’s concern about PO autonomy, we implemented a tiered approval system:
Tier 1 - Auto-Approve (No Human Review):
- Order value < $5,000
- Reorder quantity within expected range (0.8x to 1.5x of typical order size)
- Vendor is approved and active
- Item has been reordered automatically at least 3 times successfully
These POs are created with status ‘Pending Receipt’ and submitted directly to the vendor via EDI.
Tier 2 - Pending Approval (Buyer Review Required):
- Order value $5,000 - $50,000
- Reorder quantity outside typical range
- First-time auto-reorder for this item
- Vendor has quality holds or recent delivery issues
These POs are created with status ‘Pending Approval’ and trigger email notification to the assigned buyer.
Tier 3 - Exception Handling (Manual Process):
- Order value > $50,000
- Vendor is inactive or has no pricing
- Item has no preferred vendor defined
- Reorder calculation produces unusual results
These scenarios are logged to a custom exception record and generate daily summary emails to procurement management.
Here’s the PO creation logic:
function createPurchaseOrder(item, quantity, vendor) {
var orderValue = quantity * item.cost;
var approvalRequired = (orderValue > 5000) || !item.custitem_auto_reorder_approved;
var po = record.create({type: record.Type.PURCHASE_ORDER});
po.setValue({fieldId: 'entity', value: vendor.id});
po.setValue({fieldId: 'custbody_auto_generated', value: true});
po.setValue({fieldId: 'approvalstatus', value: approvalRequired ? 1 : 2}); // 1=Pending, 2=Approved
po.setSublistValue({sublistId: 'item', fieldId: 'item', line: 0, value: item.id});
po.setSublistValue({sublistId: 'item', fieldId: 'quantity', line: 0, value: quantity});
try {
var poId = po.save();
log.audit('PO Created', 'Item: ' + item.name + ', PO#: ' + poId + ', Approval: ' + !approvalRequired);
return poId;
} catch(e) {
logException(item, vendor, e.message);
return null;
}
}
Error Handling and Exception Management:
Amanda’s question about error handling is crucial. We implemented comprehensive exception management:
- Vendor Data Validation:
Before attempting PO creation, the script validates:
- Vendor is active and approved for purchasing
- Current pricing exists (from purchase price level or last PO)
- Vendor has valid remit-to address
- Payment terms are defined
If any validation fails, the item is logged to the exception queue rather than creating an invalid PO.
- Exception Queue Processing:
We created a custom record type (customrecord_reorder_exception) that captures failed reorder attempts:
function logException(item, vendor, errorMessage) {
record.create({
type: 'customrecord_reorder_exception',
values: {
custrecord_exception_item: item.id,
custrecord_exception_vendor: vendor ? vendor.id : null,
custrecord_exception_date: new Date(),
custrecord_exception_message: errorMessage,
custrecord_exception_status: 'Open'
}
}).save();
}
Procurement receives a daily digest email listing all exceptions with actionable details. They can resolve the underlying issue (update vendor, fix pricing, adjust reorder points) and mark the exception as resolved.
- Monitoring Dashboard:
We built a custom SuiteScript portlet that displays:
- Items approaching reorder point (next 7 days)
- Open exceptions requiring attention
- Auto-generated POs pending approval
- Reorder success rate (successful auto-orders / total evaluations)
This dashboard gives procurement visibility into the automation’s performance and immediate awareness of items needing manual intervention.
Results and Continuous Improvement:
After 6 months, our metrics show:
- 78% reduction in stockouts (45/month to 10/month)
- 85% of reorders handled automatically without human intervention
- Procurement team time savings of 15 hours/week (redirected to supplier development)
- Inventory carrying costs decreased 12% (better ordering precision)
- The remaining 10 stockouts/month are primarily due to supplier delivery failures, not reordering failures
We continue to refine the safety stock factors based on actual stockout incidents. Items that experienced stockouts get their safety factors increased automatically, creating a self-learning system.
The key to success was starting with a small pilot (50 items), validating the logic thoroughly, then expanding gradually while monitoring results. The approval workflow gave our procurement team confidence to embrace automation rather than fear it.
For anyone implementing similar automation, I’d emphasize: comprehensive error handling is more important than perfect reorder logic. You’ll never eliminate all edge cases, but robust exception management ensures nothing falls through the cracks.