Optimize Stock: Configuring Min-Max Stock with Auto Purchase Requests in ERP

Inefficient inventory management is one of the most crucial problems faced by healthcare institutions such as hospitals and clinics, as well as other sectors such as retail and feed manufacturing. Imagine a scenario where a hospital runs out of vital drugs such as insulin or antibiotics due to a slow and error-prone manual ordering process. Or, conversely, accumulating too much expired stock, causing significant financial losses. A study from Supply Chain Quarterly (2022) shows that inventory handling costs can reach 20-40% of the inventory value itself. Without an automated system, the risk of out-of-stock that disrupts patient services or overstock that burdens working capital becomes very high. This article will guide you in depth on how to implement a min-max stock system with an auto purchase request feature, a smart solution that can eliminate these problems. We will discuss the basic concepts, technical implementation details using modern technology, runnable code examples, error handling, and best practices to ensure your system runs optimally and sustainably. The goal is to create stock management that is proactive, efficient, and free from unnecessary manual intervention.

Basic Concepts of Min-Max Stock and Purchase Request Automation

Min-max stock management is an inventory strategy that sets minimum (reorder point) and maximum limits for each item in the warehouse. When the stock quantity of an item reaches or falls below the minimum limit, the system automatically triggers a purchase request to replenish stock up to the maximum limit. This concept is vital for maintaining a balance between item availability and storage costs. In a SIMRS environment, for example, the stock of essential drugs such as Paracetamol 500mg can be set with a minimum limit of 100 boxes and a maximum of 300 boxes. If the stock drops to 99 boxes, the system will immediately create a purchase request for 201 boxes (300 - 99 = 201), ensuring availability without accumulating too much.

The main benefits of implementing this system are very clear. First, operational efficiency. With automation, staff no longer need to monitor stock manually, reducing workload and the potential for human error. Second, prevention of out-of-stock. Patients will not lack vital drugs, and the service process will not be disrupted. Third, cost control. By preventing overstock, storage costs, expiration risk, and working capital losses can be minimized. Fourth, better stock visibility. Operations managers can have an accurate and real-time view of inventory status.

As a concrete example in the Poultry/Layer ERP sector, chicken feed is the largest cost component. If critical feed stock runs out, chicken growth will be hampered and large losses are unavoidable. With min-max stock, the system can be set for a specific type of feed (e.g., Starter Feed) with a min of 500 sacks and a max of 1500 sacks. When stock reaches 499 sacks, the system automatically creates a PR for 1001 sacks. This also applies to vaccines and vitamins, which are very sensitive to timely availability. This automation applies not only to consumables, but also to important spare parts for medical equipment or production machines.

The work process involves several steps: (1) Determining min-max values for each SKU (Stock Keeping Unit), often based on historical demand data, supplier lead time, and storage capacity. (2) Continuous stock monitoring by the system. (3) When stock reaches the minimum limit, the system triggers the creation of a purchase request with a calculated quantity (max_stock - current_stock). (4) This purchase request is then forwarded to the purchasing module for approval and procurement. Integration with other modules such as sales, production, and finance is crucial to ensure consistent data and a smooth process.

Technical Implementation Details with Modern Technology

Implementing a min-max stock system with auto purchase request requires a solid technical foundation. We recommend using a modern stack for flexibility, scalability, and ease of development. For the backend, Laravel 11.x (with PHP 8.2+) is an excellent choice due to its rich ecosystem, powerful Eloquent ORM, and Artisan Command features that make task scheduling easy. As the database, PostgreSQL 16 offers performance, reliability, and advanced features suitable for complex inventory data.

The minimum required database structure includes the items table (for item master data), warehouses (storage locations), inventory_levels (actual stock in each warehouse), and purchase_requests (to store generated purchase requests). The items table will have columns such as id, name, sku, min_stock_level, max_stock_level, and uom (unit of measure). The inventory_levels table will store item_id, warehouse_id, and current_quantity. Meanwhile, the purchase_requests table will record id, item_id, warehouse_id, requested_quantity, status (e.g., PENDING, APPROVED, REJECTED), requested_by (SYSTEM/USER), and request_date.

The automation process can be implemented using a scheduled Laravel Artisan Command (for example, hourly or daily) via a Cron Job on the server. This command will scan all items in each warehouse, comparing current_quantity with min_stock_level. If the minimum condition is met, the system will calculate requested_quantity (max_stock_level - current_quantity) and create a new entry in the purchase_requests table. It is important to add validation logic to avoid duplicate requests or requests for items already in the procurement process.

Additionally, consider factors such as supplier lead time (time from ordering to receiving goods), safety stock (buffer stock to handle demand fluctuations or supplier delays), and demand forecasting (prediction of future demand). For example, if the lead time for a certain drug is 7 days and the average daily demand is 10 units, then the min_stock_level should consider at least 70 units plus safety stock. Integration with other systems, such as a vendor management module to obtain the latest lead time data or an accounting module to process payments, will improve the overall effectiveness of the system. Using a RESTful API for integration between modules or external systems is the best approach.

For SIMRS cases that may need to interact with health standards, although not directly relevant to min-max stock, an understanding of FHIR R4 or HL7 v2.5.1 is an added value. However, for internal inventory automation, focusing on a microservices or modular architecture with internal APIs will be more effective. The use of a queueing system such as Redis or RabbitMQ can also help process purchase requests asynchronously, especially if the volume of items and requests is very high, ensuring application performance remains optimal and responsive.

Example Code for Purchase Request Automation Implementation

The following is an example implementation using Laravel 11.x and PHP 8.2+. We will create an Artisan Command that will be run on a schedule to check stock and create purchase requests.

<?phpnamespace App"Console"Commands;use "Illuminate"Console"Command;use App"Models"Item;use App"Models"InventoryLevel;use App"Models"PurchaseRequest;use "Illuminate"Support"Facades"DB;class GeneratePurchaseRequests extends Command{    protected $signature = 'inventory:generate-prs';    protected $description = 'Checks inventory levels and generates purchase requests for items below min stock.';    public function handle()    {        $this->info('Starting inventory check for auto purchase requests...');        $items = Item::where('is_active', true)->get();        foreach ($items as $item) {            foreach ($item->inventoryLevels as $inventoryLevel) {                if ($inventoryLevel->current_quantity <= $item->min_stock_level) {                    // Check if there's an existing PENDING PR for this item in this warehouse                    $existingPr = PurchaseRequest::where('item_id', $item->id)                                                ->where('warehouse_id', $inventoryLevel->warehouse_id)                                                ->where('status', 'PENDING')                                                ->first();                    if (!$existingPr) {                        $requestedQuantity = $item->max_stock_level - $inventoryLevel->current_quantity;                        if ($requestedQuantity > 0) {                            DB::transaction(function () use ($item, $inventoryLevel, $requestedQuantity) {                                PurchaseRequest::create([                                    'item_id' => $item->id,                                    'warehouse_id' => $inventoryLevel->warehouse_id,                                    'requested_quantity' => $requestedQuantity,                                    'uom' => $item->uom,                                    'status' => 'PENDING',                                    'requested_by' => 'SYSTEM',                                    'request_date' => now(),                                    'notes' => 'Generated by min-max stock automation'                                ]);                                $this->info("Generated PR for {$item->name} in warehouse {$inventoryLevel->warehouse->name}. Quantity: {$requestedQuantity}");                            });                        }                    } else {                        $this->warn("Skipping {$item->name} in warehouse {$inventoryLevel->warehouse->name}: Pending PR already exists.");                    }                }            }        }        $this->info('Inventory check completed.');        return Command::SUCCESS;    }}

The code above is a Laravel Artisan Command. First, this command will retrieve all active items. Then, for each item, it will iterate through all inventory levels in each warehouse. If the current_quantity of an item in a particular warehouse falls below or equal to the specified min_stock_level, the system will check whether there is already a purchase request with 'PENDING' status for that item in the same warehouse. If none exists, the system will calculate requested_quantity (max_stock_level minus current_quantity) and create a new entry in the purchase_requests table. A database transaction is used to ensure data integrity. This prevents the creation of duplicate PRs for the same case.

// Example of scheduling the command in app/Console/Kernel.php within the schedule method$schedule->command('inventory:generate-prs')->hourly(); // Run every hour// Minimal Models for context (app/Models/Item.php, app/Models/InventoryLevel.php, app/Models/PurchaseRequest.php)<?phpnamespace App"Models";use "Illuminate"Database"Eloquent"Factories"HasFactory;use "Illuminate"Database"Eloquent"Model;class Item extends Model{    use HasFactory;    protected $fillable = ['name', 'sku', 'min_stock_level', 'max_stock_level', 'uom', 'is_active'];    public function inventoryLevels()    {        return $this->hasMany(InventoryLevel::class);    }}<?phpnamespace App"Models";use "Illuminate"Database"Eloquent"Factories"HasFactory;use "Illuminate"Database"Eloquent"Model;class InventoryLevel extends Model{    use HasFactory;    protected $fillable = ['item_id', 'warehouse_id', 'current_quantity'];    public function item()    {        return $this->belongsTo(Item::class);    }    public function warehouse()    {        return $this->belongsTo(Warehouse::class);    }}<?phpnamespace App"Models";use "Illuminate"Database"Eloquent"Factories"HasFactory;use "Illuminate"Database"Eloquent"Model;class PurchaseRequest extends Model{    use HasFactory;    protected $fillable = ['item_id', 'warehouse_id', 'requested_quantity', 'uom', 'status', 'requested_by', 'request_date', 'notes'];    public function item()    {        return $this->belongsTo(Item::class);    }    public function warehouse()    {        return $this->belongsTo(Warehouse::class);    }}

The second code shows how this command is scheduled in app/Console/Kernel.php to run every hour. This ensures that the system regularly checks inventory status and responds to stock changes quickly. In addition, minimal Eloquent model definitions for Item, InventoryLevel, and PurchaseRequest are also included, which are necessary for the command code above to work. These models define the relationships between tables and fillable attributes, making interaction with the database easier. It is important to ensure that the item->inventoryLevels relationship is correctly defined in the Item model and vice versa, and that the Warehouse model is also available.

Example Purchase Request Payload and Error Handling

When the min-max stock system automatically creates a purchase request, this data is usually represented in JSON or XML format for ease of transfer between modules or systems. The following is an example of a realistic JSON payload for a purchase request generated by the system:

{  "request_id": "PR-WH001-MED001-20240726-001",  "warehouse_id": "WH001",  "warehouse_name": "Gudang Farmasi Utama",  "item_id": "MED001",  "item_sku": "PARA-500MG-BOX",  "item_name": "Paracetamol 500mg Box",  "requested_quantity": 200,  "uom": "BOX",  "current_stock_before_pr": 100,  "min_stock_level": 100,  "max_stock_level": 300,  "status": "PENDING_APPROVAL",  "requested_by": "SYSTEM_AUTOMATION",  "request_date": "2024-07-26T10:30:00Z",  "expected_delivery_date": null,  "priority": "MEDIUM",  "notes": "Generated automatically by min-max stock system. Requires immediate review.",  "audit_trail": [    {      "timestamp": "2024-07-26T10:30:00Z",      "event": "PR_CREATED",      "actor": "SYSTEM_AUTOMATION",      "details": "Initial creation due to stock below minimum."    }  ]}

This payload includes complete details about the request, including unique identification, warehouse and item information, requested quantity, status, and audit trail. Information such as current_stock_before_pr and min_stock_level is very useful for context for the purchasing manager who will approve the request. This comprehensive payload design ensures that all relevant information is available for the next process, whether it is manual approval or integration with a vendor procurement system.

Example Error Message and How to Handle It:

Although automation systems are designed to work smoothly, problems can arise, especially when integrating with external systems or under inconsistent data conditions. One example of an error that often occurs is:

{  "error": {    "code": "VENDOR_API_UNAVAILABLE",    "message": "Failed to connect to Vendor API for supplier 'PT Farmasi Jaya'. Please check network connectivity or API status.",    "timestamp": "2024-07-26T10:45:12Z",    "details": "cURL error 7: Failed to connect to host (Connection refused)"  }}

The error above indicates that the system failed to connect to the vendor API when trying to send a purchase request. This can be caused by network issues, a down vendor server, or incorrect endpoint configuration. To overcome this, a robust error handling strategy is essential:

1. Logging Detail: Every error must be logged in detail, including the stack trace, time of occurrence, and the context of the data that caused the error. This helps in debugging and identifying the root cause.
2. Retry Mechanism: For transient errors such as network connection issues, the system must retry sending the request after a certain delay (for example, 3 attempts with exponential intervals: 1 minute, 5 minutes, 15 minutes).
3. Automatic Notification: After several failed attempts, the system must send a notification (email, SMS, or via an internal notification system) to the administrator or relevant operational team. The notification must contain error details and the affected items.
4. Manual Fallback: If automation persistently fails, the system must provide a fallback mechanism, such as marking the purchase request as 'FAILED_AUTOMATION' and triggering a manual task for staff to review and process the request manually.
5. Monitoring Dashboard: Provide a monitoring dashboard that displays the status of purchase requests, including those that failed to process. This allows the operational team to monitor system performance in real-time and take corrective action if needed.

The implementation of this error handling strategy ensures that even if problems occur, the procurement process can still run, and the risk of stock shortages can be minimized.

Best Practices for Implementing Automatic Min-Max Stock

1. Review and Adjust Min-Max Levels Periodically: Min-max levels are not static values. Market demand, seasonal trends, promotions, changes in supplier lead time, or internal policies (for example, PMK No. 73 of 2016 concerning Pharmaceutical Service Standards in Pharmacies which may affect drug availability) will affect the optimal values. Conduct a review at least every quarter or semester, and adjust based on historical data analysis and projections.
2. Full Integration with Related Modules: Ensure the min-max system is tightly integrated with other modules such as Sales/Dispensing, Purchasing, Goods Receipt, and Accounting. Consistent data and smooth information flow will prevent inconsistencies and errors, for example, ensuring that sold stock directly reduces inventory and triggers a PR if needed.
3. Accurately Account for Lead Time and Safety Stock: Ordering goods requires delivery time (lead time). The min-max values must account for this time so that goods arrive before stock runs out. Also add safety stock to anticipate unexpected demand fluctuations or delays from suppliers.
4. Implement a Robust Notification System: In addition to internal notifications for created PRs, ensure there are notifications for anomaly cases such as very low stock even though a PR has been created, integration failures with suppliers, or items approaching their expiration date. These notifications must be real-time and configurable.
5. Maintain Cleanliness and Accuracy of Master Data: Item master data (name, SKU, UOM, main supplier, standard price) must always be accurate and up-to-date. Errors in master data can cause incorrect min-max calculations and inappropriate purchase requests. Conduct regular data audits.
6. Conduct Continuous Testing and Validation: Before and after implementation, conduct extensive testing. Validate whether the generated PRs comply with the rules, the quantities are correct, and the integration process runs smoothly. Test boundary scenarios, such as zero stock or very high stock.
7. Provide a Comprehensive Audit Trail: Every change to min-max levels, every PR created, approved, or rejected, must be recorded in detail (who, when, what change). This audit trail is important for accountability, issue tracking, and regulatory compliance.
8. Train Users and Provide Documentation: End users, especially warehouse and purchasing staff, must understand how the system works and their role in the workflow. Provide clear and easily accessible documentation for operational guidance and basic troubleshooting.
9. Consider Seasonal and Promotional Factors: For items with seasonal demand or that are often part of promotions, min-max levels may need to be adjusted dynamically. A flexible system allows these adjustments without significant manual intervention.

FAQ: Common Questions About Min-Max Stock and Automation

1. What is the difference between Min-Max Stock and Reorder Point (ROP)?
Although both are used to trigger reordering, there is a fundamental difference. Reorder Point (ROP) is a single stock point at which reordering must be done to avoid stockouts, taking into account lead time and daily demand rate. Meanwhile, the Min-Max Stock system sets two limits: a minimum limit (often the same as ROP) that triggers ordering, and a maximum limit that determines how many goods should be ordered to replenish stock up to that level. The Min-Max system provides more granular control over the desired stock quantity after reordering.

2. How do you determine the optimal Min-Max value for each item?
Determining the optimal min-max values involves analyzing historical sales or usage data, supplier lead time, holding costs, and out-of-stock costs. For the minimum value, you can use the ROP formula: (Average Daily Demand x Lead Time) + Safety Stock. For the maximum value, it is usually calculated based on the target storage capacity or target inventory cycle, for example (Min Stock + Economic Order Quantity) or (Min Stock + Average Usage During a Certain Period). It is important to make adjustments periodically based on performance and market conditions.

3. Can this automation system be implemented in an existing SIMRS?
Very possible. Implementation can be done with two approaches: (1) API Integration: If your SIMRS has an adequate API, you can build a separate module for min-max stock and auto purchase request, then integrate it via the API. (2) Direct Customization: If the SIMRS is an in-house system or allows code customization, this feature can be added directly to the existing inventory module. This approach requires a deep understanding of the existing SIMRS architecture and may involve additional database development.

4. What are the main challenges in implementing an automatic Min-Max Stock system?
Some of the main challenges include: (1) Data Accuracy: Item master data, stock data, and historical usage data must be highly accurate. (2) Parameter Determination: Determining the right min-max values and lead time can be complex and requires continuous analysis. (3) Integration: Integrating a new system with existing modules or external systems is often a technical challenge. (4) Cultural Change: Staff may need to adapt to new, more automated processes. (5) Maintenance: The system requires continuous monitoring and adjustment to remain relevant and efficient.

5. What if there is a price change or discount from the supplier after the automatic PR is created?
The auto purchase request automation system ideally stops at the PR creation stage with the status 'PENDING_APPROVAL'. The purchasing manager will then review the PR. At this review stage, price changes, special discounts, or the availability of alternative suppliers can be considered. The system can be integrated with a vendor management module to automatically pull the latest price information, but final approval often still involves human intervention for strategic decisions and negotiation. This ensures flexibility even though the initial process is automated.

6. Is integration with a vendor system or e-procurement required for this system?
Although not absolutely necessary at the initial stage, integration with a vendor system or e-procurement platform is highly recommended for maximum efficiency. Without integration, automatically created PRs still have to be processed manually to the vendor (for example, via email or a vendor portal system). With integration, the PR can be sent directly to the vendor system or converted into a Purchase Order (PO) automatically after approval, speeding up the procurement cycle and reducing transcription errors. This is highly relevant for standards such as PEB (Electronic Procurement of Goods) in the public sector or B2B API integration in the private sector.

Implementing a min-max stock system with auto purchase request is no longer a luxury, but an essential need to maintain operational efficiency and business sustainability, especially in crucial sectors such as healthcare and agribusiness. With the right approach, the use of modern technologies such as Laravel 11.x and PostgreSQL 16, as well as a deep understanding of best practices, you can turn inventory management challenges into a competitive advantage. This solution not only reduces the risk of out-of-stock and overstock, but also frees up human resources to focus on more strategic tasks, increases customer satisfaction, and ultimately drives your business growth. If you are ready to take your inventory management to the next level and need a tailored technology solution, do not hesitate to contact Nugroho Setiawan. With extensive experience in SIMRS, ERP, and system integration, we are ready to help you design, develop, and implement the right solution for your organization's specific needs. Let's realize optimal operational efficiency together!

Related solutions by PT Pilar Inovasi Informatika (Pilar Inovasi IT products): SIMRS (Hospital Information System) Clinic Management System SatuSehat Integration. Need a free consultation?