Optimize Medical Stock: Min-Max Configuration & Auto Purchase Request for Operational Efficiency

In hospital and clinic operational environments, precise inventory management is not just a best practice, but a crucial necessity. A shortage of vital medications or medical equipment can have fatal consequences for patient care quality, while excess stock instead burdens the operational budget with storage costs, expiration risks, and potential losses. Imagine a clinic running out of flu vaccines at the peak of the season, or a hospital having to discard thousands of ampoules of medication because they have passed their expiration date. Situations like this are not only financially detrimental, but also damage reputation and public trust. The main challenge is balancing optimal availability with cost efficiency. How can you ensure the right items are always available in the right quantities, at the right time, without waste?

This article will thoroughly examine smart solutions through the configuration of a min-max stock system integrated with automated purchase requests (auto purchase request). We will discuss the basic concepts, implementation details using real examples from ERP platforms and custom development, present runnable code examples, and discuss best practices and handling of possible obstacles. The goal is to provide a practical and in-depth guide for operations managers, hospital IT managers, clinic owners, and decision makers to transform their inventory management into something more efficient, responsive, and cost-effective, ensuring smooth operations and excellent healthcare service quality.

Basic Concepts of Min-Max Stock and Automated Purchase Requests

The min-max stock system is an inventory control method that sets a minimum limit (reorder point) and a maximum for each stock item. When the stock quantity of an item reaches or falls below the minimum limit, the system will automatically trigger a purchase request to replenish stock until it reaches the predetermined maximum limit. This concept is very effective for managing thousands of items with varying consumption rates, ensuring availability without excessive accumulation.

For example, a hospital sets a minimum limit of 50 units for a certain antibiotic medication and a maximum limit of 200 units. If the stock of that antibiotic falls to 48 units, the system will generate an automatic purchase request for 152 units (200 - 48), or the quantity that has been set for purchase per order (for example, multiples of 50 or 100 units), so that stock returns to the optimal level. Determining the minimum and maximum values is not done arbitrarily. The Reorder Point (RP) or minimum limit is often calculated using the formula: RP = (Lead Time x Daily Usage) + Safety Stock. Lead Time is the time required by the supplier to deliver the goods. Daily Usage is the average daily consumption. Safety Stock is the buffer inventory to anticipate demand fluctuations or delivery delays. Meanwhile, the maximum limit usually takes into account storage capacity, holding costs, and the desired ordering frequency.

Automated purchase requests (auto purchase request) are an evolution of the min-max stock system. After the system detects stock below the minimum limit, instead of only giving a notification, it directly creates a draft purchase request or even a purchase order to a predetermined supplier. This reduces error-prone manual intervention, speeds up the procurement cycle, and ensures supply is not interrupted. Its main benefits include reducing operational costs due to minimal manual labor, preventing stock-outs that could harm service, optimizing working capital by avoiding excessive stock accumulation, and improving overall inventory data accuracy. Proper implementation can produce significant savings, with some institutions reporting reductions in holding costs of up to 15-20% and increases in stock availability of up to 99%.

Implementation Details in Information Systems

The implementation of min-max stock configuration with auto purchase request can be done through an existing ERP (Enterprise Resource Planning) system or with custom development. One popular and flexible ERP platform is Odoo. For the healthcare context, Odoo 16 provides powerful Inventory and Purchase modules, suitable for SIMRS or Clinic MIS needs. In Odoo 16, this feature is known as 'Reordering Rules'.

The configuration steps in Odoo 16 generally include:

1. Activate Advanced Inventory Features: Make sure you have enabled features such as 'Storage Locations' and 'Multi-Step Routes' in the Odoo Inventory module. This allows for more granular control over stock movements and locations.
2. Product Definition: Each medical or pharmaceutical item must be correctly defined in the system. Be sure to fill in important information such as the 'Lead Time' from the vendor (delivery time), 'Unit of Measure', and the relevant 'Vendor'. This 'Lead Time' data is crucial for accurate reorder point calculations.
3. Reordering Rules Configuration: Navigate to the Inventory > Configuration > Reordering Rules module. Here, you can create a new rule for each product. For each rule, you need to set:
   • Minimum Quantity: The minimum stock quantity that triggers a purchase request.
   • Maximum Quantity: The maximum stock quantity you want to achieve after purchasing.
   • Quantity to Order: The quantity to be ordered each time the rule is triggered (can be equal to Maximum Quantity - Current Stock, or a specific multiple).
   • Location: The storage location where this rule applies.
   • Preferred Route: The procurement route (for example, buy from vendor).
4. Schedule Run Scheduler: Odoo has a 'Scheduler' that runs periodically (for example, every hour or every night). This Scheduler is responsible for evaluating all 'Reordering Rules' and automatically creating a 'Purchase Order' or 'Purchase Request' if the conditions are met. You can configure the scheduler frequency in the Technical > Automation > Scheduled Actions menu. Make sure the 'Run Scheduler' for 'Reordering Rules' is enabled and set according to operational needs.

Odoo's integration with a PostgreSQL 16 database ensures fast performance and data reliability. For custom development, the system can be built using a framework such as Laravel 11.x with PHP 8.3, connected to a similar database. The custom approach provides full flexibility to tailor highly specific business logic or integrate with existing legacy systems such as SIMRS through an API.

Example Code for Implementing Min-Max Logic

In a custom development scenario, min-max stock logic can be implemented using a programming language such as PHP with the Laravel framework. The following is a simple example of how you could implement stock checking logic and automatic purchase request triggering.

First, we need to ensure the product or inventory table has columns to store the minimum and maximum stock limits. The following is an example database migration in Laravel 11.x:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class AddMinMaxStockToInventoryItemsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('inventory_items', function (Blueprint $table) {
            $table->integer('min_stock_level')->default(0)->after('current_stock');
            $table->integer('max_stock_level')->default(0)->after('min_stock_level');
            $table->integer('reorder_quantity')->default(0)->after('max_stock_level');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('inventory_items', function (Blueprint $table) {
            $table->dropColumn(['min_stock_level', 'max_stock_level', 'reorder_quantity']);
        });
    }
}

The migration above adds three new columns to the inventory_items table: min_stock_level (minimum limit), max_stock_level (maximum limit), and reorder_quantity (the quantity to be ordered when reordering). After the migration is run, you can fill in these values for each inventory item.

Next, we will create a class or method responsible for checking stock periodically and triggering a purchase request if needed. This can be run as a scheduled task in Laravel (using a Cron Job).

<?php

namespace App\Services;

use App\Models\InventoryItem;
use App\Models\PurchaseRequest;
use Illuminate\Support\Facades\Log;

class StockAutomationService
{
    public function checkAndCreatePurchaseRequests(): void
    {
        Log::info('Running stock automation service...');

        $itemsToReorder = InventoryItem::whereColumn('current_stock', '<=', 'min_stock_level')
                                       ->where('is_active', true) // Only active items
                                       ->get();

        foreach ($itemsToReorder as $item) {
            // Check whether a pending Purchase Request already exists for this item
            // within a certain period (e.g., the last 24 hours) to avoid duplication
            $existingPendingRequest = PurchaseRequest::where('inventory_item_id', $item->id)
                                                     ->where('status', 'pending')
                                                     ->where('created_at', '>=', now()->subHours(24))
                                                     ->first();

            if ($existingPendingRequest) {
                Log::info("Pending purchase request already exists for item {$item->name}. Skipping.");
                continue;
            }

            $quantityToOrder = $item->reorder_quantity > 0 ? $item->reorder_quantity : ($item->max_stock_level - $item->current_stock);

            if ($quantityToOrder <= 0) {
                Log::warning("Calculated quantity to order for {$item->name} is non-positive. Skipping.");
                continue;
            }

            try {
                PurchaseRequest::create([
                    'inventory_item_id' => $item->id,
                    'quantity' => $quantityToOrder,
                    'requested_by' => 'SYSTEM_AUTOMATION', // Or the system user ID
                    'status' => 'pending',
                    'notes' => "Automatic reorder triggered by min-max stock level. Current stock: {$item->current_stock}, Min stock: {$item->min_stock_level}."
                ]);
                Log::info("Created automatic purchase request for {$item->name}, quantity {$quantityToOrder}.");
            } catch (\Exception $e) {
                Log::error("Failed to create purchase request for {$item->name}: " . $e->getMessage());
            }
        }
        Log::info('Stock automation service finished.');
    }
}

The PHP 8.3 code above defines a service that will search for all inventory items whose stock is at or below their minimum level. For each item that meets the criteria, the system will check whether a pending purchase request already exists for that item within the last 24 hours to prevent duplication. If none exists, the system will calculate the quantity to order (using reorder_quantity if available, or the difference between max_stock_level and current_stock) and create a new entry in the purchase_requests table with 'pending' status. This process will then be logged for audit and monitoring purposes. This service can be called by the Laravel Scheduler, for example every 30 minutes or every hour.

Example Payload and Error Handling

In an integrated system architecture, automatic purchase requests are often sent via API to the purchasing module or even directly to a specific vendor's system. The following is an example of a realistic JSON payload for a purchase request generated automatically by the system:

{
  "request_id": "PR-AUTO-20240726-001",
  "item_details": [
    {
      "item_code": "ANTIBIO-001",
      "item_name": "Amoxicillin 500mg Kapsul",
      "quantity_requested": 150,
      "unit_of_measure": "Kapsul",
      "vendor_code": "PHARMA-SUPP-A",
      "vendor_name": "PT. Pharma Sejahtera"
    }
  ],
  "requested_by": "SYSTEM_AUTOMATION",
  "request_date": "2024-07-26T10:30:00Z",
  "reason": "Automatic reorder triggered by min-max stock level for item ANTIBIO-001. Current stock: 45, Min stock: 50.",
  "priority": "normal",
  "delivery_location": "Gudang Farmasi Utama"
}

The payload above includes essential information such as the request ID, details of the ordered item (code, name, quantity, unit, vendor), who requested it (in this case, the automated system), the request date, the trigger reason, priority, and delivery location. This payload will then be processed by the purchasing module's API, which can subsequently generate a Purchase Order (PO) sent to the vendor.

Although automation is designed for efficiency, unexpected error situations are always possible. One common error scenario is when the system fails to create a purchase request due to incomplete master data, or there is a connectivity issue with the vendor's API. An example error message that might appear:

{
  "status": "error",
  "code": "PR_001_INVALID_VENDOR",
  "message": "Purchase request failed: Vendor 'PHARMA-SUPP-A' is inactive or not found for item 'ANTIBIO-001'. Manual intervention required.",
  "timestamp": "2024-07-26T10:30:05Z",
  "details": "Check vendor master data and item-vendor linkage."
}

Effective error handling is crucial. When the automated system encounters an error like the one above, several steps must be taken:

1. Logging Detail: All errors must be logged in detail in the system log (for example, using Laravel's Monolog or integration with Sentry/ELK Stack). This log must include the time of occurrence, error message, related transaction ID, and the data context that caused the error.
2. Real-time Notification: The operations or purchasing team must receive real-time notifications (email, SMS, dashboard notifications) regarding this failure. Notifications must be clear and directly point to the required action.
3. Fallback System and Manual Override: There must be a mechanism to create purchase requests manually if automation fails. The purchasing team must be able to review the error, correct the master data (for example, activate the vendor), and then create a PR manually or try to re-trigger the automation.
4. Monitoring and Alarms: Implement a monitoring system (for example, Prometheus with Grafana) to monitor metrics such as the number of failed purchase requests or API response time. Set alarms for certain thresholds so the team can proactively respond to issues before they have a broad impact.
5. Root Cause Analysis: Recurring errors must be analyzed for their root cause. Is it because the master data is often invalid? Is there an API stability issue? Continuous improvement based on this analysis is very important.

Best Practices

1. Master Data Validation and Accuracy: Ensure that product master data (name, code, unit, lead time, vendor) and stock data (current stock) are always accurate and real-time. Invalid data is the main cause of automation failure. Conduct regular data audits, at least monthly, to ensure data integrity.
2. Periodic Parameter Review and Adjustment: Min-max stock values and reorder quantity are not static. Conduct reviews at least every quarter or semester, as well as when there are significant changes in demand patterns, vendor lead times, or procurement policies. Use historical sales or consumption data to optimize these parameters.
3. Strong End-to-End Integration: Ensure the inventory management system is integrated with the purchasing, sales/distribution, and accounting modules. Seamless integration (for example using RESTful APIs with the FHIR R4 standard for medical data if possible) will ensure consistent data flow and reduce manual entry, avoiding information silos.
4. User Training and System Adoption: Provide comprehensive training to operational and purchasing staff on how the automation system works, how to review created requests, and how to handle exceptions or errors. Good adoption by users is the key to success.
5. Implementation of Appropriate Safety Stock: In addition to min-max, consider adding 'safety stock' for critical items or those with high demand fluctuations. Safety stock acts as a buffer against supply uncertainty or unexpected demand spikes, calculated based on the standard deviation of demand and lead time.
6. Performance Monitoring and Routine Audits: Actively monitor the performance of the automation system, including response time, the number of successful/failed requests, and the impact on overall stock levels. Conduct routine internal audits to ensure compliance with procedures and the effectiveness of the configuration.
7. Vendor Performance Evaluation: Vendor performance (especially lead time and delivery reliability) directly affects the effectiveness of the min-max system. Conduct regular vendor evaluations to ensure they meet the Service Level Agreement (SLA) and adjust system parameters if there are significant changes in vendor performance.
8. System Scalability and Flexibility: Design the system so that it can scale according to operational growth and is flexible to accommodate changes in policies or future business needs. Use a modular architecture and open APIs.
9. Regulatory Compliance: Especially for the healthcare industry, ensure that all procurement and stock management processes and documentation comply with applicable regulations, such as the Minister of Health Regulation (PMK) regarding pharmaceuticals and medical devices, as well as hospital accreditation standards.

FAQ

1. What is the main difference between Min-Max Stock and just Reorder Point?

   Min-Max Stock is a more comprehensive strategy because it not only sets the point at which to order (minimum/reorder point) but also how much to order to reach the target stock level (maximum). Reorder Point (ROP) only focuses on the ordering trigger when stock reaches a certain level, without explicitly determining an upper limit. Min-Max helps control inventory so it is neither too little nor too much, while ROP focuses more on preventing stockouts.

2. How do you determine the optimal Min-Max values for each item?

   Determining optimal Min-Max values requires careful analysis of historical data. For Min, use the Reorder Point formula: (Average Daily Usage x Lead Time) + Safety Stock. For Max, consider storage costs, desired ordering frequency, quantity discounts from vendors, and warehouse capacity. Analytical tools such as descriptive statistics, demand forecasting, and simulation can be very helpful in optimizing these values.

3. Is the Min-Max Stock system suitable for all types of items in a hospital/clinic?

   The Min-Max Stock system is very suitable for items with relatively stable and predictable demand, such as routine medications, syringes, or hygiene supplies. For items with highly irregular demand, very expensive, or with very short expiration dates (for example, blood or radioactive isotopes), other inventory management methods such as Just-In-Time (JIT) or Made-to-Order may be necessary. A combination of several strategies is often the best approach.

4. What are the main challenges in implementing Purchase Request automation?

   The main challenges include the accuracy of master data which is often inconsistent, resistance from staff accustomed to manual processes, the complexity of integration with various systems (SIMRS, ERP, vendor systems), as well as the need to continuously monitor and adjust Min-Max parameters. Building trust in the automation system and ensuring clean data are key to overcoming these challenges.

5. What if there is a sudden change in demand (for example, a disease outbreak)?

   In the case of a sudden change in demand, the Min-Max automation system may need temporary manual intervention. It is important to have an emergency mechanism that allows operational staff to quickly adjust Min-Max parameters, place emergency orders, or override the system. Historical data from similar events (if any) can be used to create scenarios and adjust safety stock in the future. System flexibility is very important in facing uncertainty.

6. Is integration with external vendor systems necessary for PR automation?

   Direct integration with external vendor systems (for example via EDI or vendor API) can further improve efficiency by sending Purchase Orders automatically without manual intervention. However, this requires strong technical and business agreements with the vendor. If direct integration is not possible, automating internal purchase requests that are then reviewed and sent manually by the purchasing team still provides significant benefits in reducing workload and speeding up internal processes.

Implementing a min-max stock system with purchase request automation is a strategic investment that will bring significant efficiency to your healthcare service operations. By reducing stockouts, minimizing excess inventory, and speeding up the procurement process, you not only save costs but also ensure patient service quality remains optimal. The digital era demands every organization to embrace technology to achieve operational excellence. Don't let inventory management become an obstacle to your growth and efficiency.

If you need assistance in designing, developing, or implementing an inventory management solution that is fully integrated with SIMRS, SIM Klinik, or a custom ERP system, Nugroho Setiawan's team is ready to be your partner. With deep experience in various health information systems and operational management, we will help you create the right, efficient, and sustainable solution. Contact us for further consultation and start your journey towards smarter and more responsive operations.

This article is part of the knowledge base of Nugroho Setiawan, an IT consultant in Jogja (Yogyakarta). Related solutions: SIMRS (Hospital Information System) Clinic Management System SatuSehat Integration. Need a free consultation?