Procurement Analytics & Automation

Overview

• What you’ll learn:
  • The iDempiere replenishment framework: MReplenish rules, ReplenishReport process, and output modes (PO, Requisition, Movement, Distribution)
  • The MStorageOnHand API for querying inventory levels across warehouses, locators, and attribute set instances
  • How to build custom procurement processes using the SvrProcess pattern with prepare()/doIt() lifecycle
  • Procurement reporting SQL using actual iDempiere table and column names
  • Automation patterns: scheduled processes, ModelValidator triggers, and event-driven procurement workflows
• Prerequisites: Lesson 1 — Procurement Overview & Requisitions, Lesson 2 — Purchase Orders
• Estimated reading time: 25 minutes

1. Introduction: Automating Procurement

Manual procurement — checking stock levels, identifying shortages, creating purchase orders — is time-consuming and error-prone. Missed reorder points lead to stockouts; over-ordering ties up capital in excess inventory. iDempiere provides a comprehensive replenishment framework that automates the entire cycle: monitor inventory levels, calculate required quantities, and generate purchase orders or requisitions automatically.

The procurement automation framework consists of three interconnected layers:

2. MReplenish: Replenishment Rules

The MReplenish class (source file: org.compiere.model.MReplenish) extends X_M_Replenish and defines the replenishment policy for each product in each warehouse. Each record in the M_Replenish table represents one rule: “For product X in warehouse Y, use replenishment type Z with these parameters.”

2.1 ReplenishType Constants

The replenishment type determines when and how the system suggests reordering. These constants are defined in X_M_Replenish:

2.2 Key Fields

2.3 The getForProduct() Method

The MReplenish class provides a static method to retrieve the replenishment rule for a specific product and warehouse, with an organization-specific override pattern:

public static MReplenish getForProduct(Properties ctx, int M_Product_ID, 
        int M_Warehouse_ID, String trxName) {
    // First try org-specific rule
    MReplenish retValue = new Query(ctx, Table_Name, 
            "M_Product_ID=? AND M_Warehouse_ID=?", trxName)
        .setParameters(M_Product_ID, M_Warehouse_ID)
        .setOnlyActiveRecords(true)
        .first();
    return retValue;
}

3. MStorageOnHand: Inventory Queries

The MStorageOnHand class (source file: org.compiere.model.MStorageOnHand) extends X_M_StorageOnHand and manages the on-hand inventory records. Each record in M_StorageOnHand tracks the quantity of a specific product at a specific locator with a specific attribute set instance and material policy date (for FIFO/LIFO).

3.1 The add() Method Chain

The add() method is the primary entry point for updating inventory. It provides three overloads with increasing specificity:

// Overload 1: Full parameters with date material policy
public static BigDecimal add(Properties ctx,
    int M_Warehouse_ID, int M_Locator_ID,
    int M_Product_ID, int M_AttributeSetInstance_ID,
    BigDecimal diffQtyOnHand,
    Timestamp dateMPolicy, String trxName) {
    // ...
    MStorageOnHand storage = getOrCreate(ctx, M_Locator_ID, 
        M_Product_ID, M_AttributeSetInstance_ID, dateMPolicy, trxName);
    BigDecimal oldQty = storage.getQtyOnHand();
    BigDecimal newQty = oldQty.add(diffQtyOnHand);
    storage.setQtyOnHand(newQty);
    storage.checkNegativeOnHand(diffQtyOnHand); // Throws if disallowed
    storage.saveEx(trxName);
    return newQty;
}

The getOrCreate() pattern is critical: if a matching storage record exists, it is retrieved; otherwise, a new one is created with QtyOnHand = 0. This ensures every inventory transaction has a target record.

3.2 Static Query Methods

MStorageOnHand provides a comprehensive set of static methods for querying inventory levels at different granularities:

3.3 The getWarehouse() Method

The getWarehouse() method is particularly important for allocation and picking processes:

public static MStorageOnHand[] getWarehouse(Properties ctx,
    int M_Warehouse_ID, int M_Product_ID, int M_AttributeSetInstance_ID,
    boolean allAttributeInstances, Timestamp minGuaranteeDate,
    boolean FiFo, String trxName) {
    
    StringBuilder sql = new StringBuilder(
        "SELECT s.M_Product_ID, s.M_Locator_ID, s.M_AttributeSetInstance_ID,")
        .append(" s.DateMaterialPolicy, SUM(s.QtyOnHand) as QtyOnHand")
        .append(" FROM M_StorageOnHand s")
        .append(" JOIN M_Locator l ON (s.M_Locator_ID = l.M_Locator_ID)")
        .append(" WHERE l.M_Warehouse_ID=?")
        .append(" AND s.M_Product_ID=?")
        .append(" AND s.QtyOnHand <> 0");
    
    // ... optional ASI filter, guarantee date filter ...
    
    // FIFO vs LIFO ordering
    sql.append(" ORDER BY s.DateMaterialPolicy");
    if (!FiFo)
        sql.append(" DESC");
    sql.append(", s.M_AttributeSetInstance_ID");
    if (!FiFo)
        sql.append(" DESC");
    
    // ... execute and return ...
}

The DateMaterialPolicy column is the key to FIFO/LIFO: it records when each lot entered inventory. FIFO ordering (ASC) picks oldest stock first; LIFO (DESC) picks newest.

3.4 The beforeSave() Negative Check

The MStorageOnHand.beforeSave() method enforces the warehouse-level IsDisallowNegativeInv flag:

@Override
protected boolean beforeSave(boolean newRecord) {
    if (newRecord || is_ValueChanged("QtyOnHand")) {
        MWarehouse wh = new MWarehouse(getCtx(), getM_Warehouse_ID(), 
            get_TrxName());
        if (wh.isDisallowNegativeInv()) {
            // Check total on-hand across all ASI at this locator
            String sql = "SELECT SUM(QtyOnHand) FROM M_StorageOnHand s"
                + " INNER JOIN M_Locator l ON (s.M_Locator_ID=l.M_Locator_ID)"
                + " WHERE s.M_Product_ID=?"
                + " AND l.M_Warehouse_ID=?"
                + " AND l.M_Locator_ID=?"
                + " AND s.M_AttributeSetInstance_ID<>?";
            BigDecimal QtyOnHand = DB.getSQLValueBDEx(get_TrxName(), sql,
                new Object[]{getM_Product_ID(), getM_Warehouse_ID(), 
                    getM_Locator_ID(), getM_AttributeSetInstance_ID()});
            if (QtyOnHand == null)
                QtyOnHand = Env.ZERO;
            QtyOnHand = QtyOnHand.add(getQtyOnHand());
            
            if (getQtyOnHand().compareTo(BigDecimal.ZERO) < 0 ||
                    QtyOnHand.compareTo(Env.ZERO) < 0) {
                log.saveError("Error", 
                    new NegativeInventoryDisallowedException(...));
                return false;
            }
        }
    }
    return true;
}

This check prevents inventory from going negative at the warehouse level (summing across all ASIs at the same locator). It is a critical safeguard for warehouse management integrity.

4. ReplenishReport: The Replenishment Engine

The ReplenishReport class (source file: org.compiere.process.ReplenishReport) extends SvrProcess and is the main engine that drives automated procurement. It reads replenishment rules, queries current inventory levels, calculates required quantities, and generates output documents.

4.1 Process Parameters

4.2 The doIt() Flow

The main process logic follows this sequence:

1. fillTable() — Populates the temporary T_Replenish table with replenishment data
2. Calculate quantities — For each product, compute: QtyOnHand + QtyOrdered - QtyReserved vs. Level_Min/Level_Max
3. Apply constraints — Enforce Order_Min and Order_Pack from M_Product_PO
4. Generate output — Create POs, Requisitions, Movements, or Distribution Orders

4.3 The fillTable() SQL

The core replenishment calculation uses a complex SQL query that joins M_Replenish with inventory data:

// Simplified from ReplenishReport.fillTable():
StringBuilder sql = new StringBuilder(
    "INSERT INTO T_Replenish "
    + "(AD_PInstance_ID, M_Warehouse_ID, M_Product_ID, AD_Client_ID, AD_Org_ID, "
    + " ReplenishType, Level_Min, Level_Max, "
    + " C_BPartner_ID, Order_Min, Order_Pack, QtyToOrder, QtyOnHand, "
    + " M_WarehouseSource_ID, DatePromised) "
    + "SELECT ").append(getAD_PInstance_ID())
    .append(", r.M_Warehouse_ID, r.M_Product_ID, r.AD_Client_ID, r.AD_Org_ID, ")
    .append(" r.ReplenishType, r.Level_Min, r.Level_Max, ")
    .append(" po.C_BPartner_ID, po.Order_Min, po.Order_Pack, 0, ")
    .append(" bomQtyOnHand(r.M_Product_ID, r.M_Warehouse_ID, 0) as QtyOnHand, ")
    .append(" r.M_WarehouseSource_ID, null ")
    .append("FROM M_Replenish r ")
    .append(" INNER JOIN M_Product_PO po ON (r.M_Product_ID=po.M_Product_ID) ")
    .append("WHERE r.ReplenishType <> '0' ")       // Exclude Manual
    .append(" AND po.IsCurrentVendor='Y' ")
    .append(" AND r.IsActive='Y' AND po.IsActive='Y' ")
    .append(" AND r.M_Warehouse_ID=?");
);

Key observations:

• Uses bomQtyOnHand() database function to include BOM component quantities
• Joins with M_Product_PO to get the current vendor (IsCurrentVendor = 'Y')
• Excludes manual replenishment type (ReplenishType <> '0')
• Picks up Order_Min and Order_Pack constraints from the vendor-product record

4.4 Quantity Calculation

After populating the temporary table, the process calculates the required order quantity:

// For REPLENISHTYPE_ReorderBelowMinimumLevel ("1"):
// Available = QtyOnHand + QtyOrdered - QtyReserved
// If Available < Level_Min:
//   QtyToOrder = Level_Max - Available

// For REPLENISHTYPE_MaintainMaximumLevel ("2"):
// Available = QtyOnHand + QtyOrdered - QtyReserved
// If Available < Level_Max:
//   QtyToOrder = Level_Max - Available

// The key difference: "Reorder Below Min" only triggers when stock falls
// below Level_Min, while "Maintain Max" always orders to reach Level_Max.

4.5 Order_Min and Order_Pack Constraints

After calculating the raw QtyToOrder, the process applies vendor constraints from M_Product_PO:

// Order_Min: enforce minimum order quantity
BigDecimal QtyToOrder = ...;  // calculated above
BigDecimal orderMin = replenish.getOrder_Min();
if (orderMin.signum() > 0 && QtyToOrder.compareTo(orderMin) < 0) {
    QtyToOrder = orderMin;  // Round up to minimum
}

// Order_Pack: round up to next pack multiple
BigDecimal orderPack = replenish.getOrder_Pack();
if (orderPack.signum() > 0) {
    // Calculate how many packs are needed
    BigDecimal packs = QtyToOrder.divide(orderPack, 0, RoundingMode.CEILING);
    QtyToOrder = packs.multiply(orderPack);
}
// Example: If QtyToOrder=73, Order_Pack=25
// packs = ceil(73/25) = 3
// QtyToOrder = 3 * 25 = 75

4.6 Output Generation

Depending on the ReplenishmentCreate parameter, the process generates different document types:

4.7 Custom Replenishment via ReplenishInterface

When ReplenishType = "9" (Custom), the process delegates to a custom Java class that implements the ReplenishInterface. This allows organizations to implement sophisticated algorithms such as seasonal demand forecasting, economic order quantity (EOQ) calculations, or safety stock formulas:

// Custom replenishment class must implement:
public interface ReplenishInterface {
    /**
     * Calculate the quantity to order
     * @param replenish the replenishment rule
     * @param product the product
     * @param warehouse the warehouse
     * @return quantity to order, or zero if no replenishment needed
     */
    public BigDecimal getQtyToOrder(MReplenish replenish, 
        MProduct product, MWarehouse warehouse);
}

5. ReplenishReportProduction

The ReplenishReportProduction class extends the standard ReplenishReport with manufacturing-specific capabilities. In addition to creating purchase orders and requisitions, it can generate manufacturing orders for products that are produced internally rather than purchased.

Key differences from ReplenishReport:

• Supports BOM (Bill of Materials) explosion for manufactured products
• Can generate manufacturing orders instead of purchase orders
• Uses QtyBatchSize from M_Replenish for production batch sizing
• Considers production lead times in addition to purchase lead times

6. SvrProcess: Building Custom Procurement Processes

The SvrProcess class (source file: org.compiere.process.SvrProcess) is the base class for all server-side processes in iDempiere. Custom procurement analytics, batch operations, and reporting processes extend this class.

6.1 The Process Lifecycle

// SvrProcess lifecycle:
// 1. autoFillParameters() - auto-fills annotated fields from AD_PInstance_Para
// 2. prepare()            - parse parameters (called by framework)
// 3. doIt()               - main business logic (implement this)
// 4. postProcess(success)  - cleanup / notification

6.2 Parameter Access Methods

6.3 Logging and User Feedback

// Simple log entry
addLog("Processing vendor: " + vendorName);

// Structured log with record reference
addBufferLog(0, null, amount, 
    "@C_Order_ID@ " + order.getDocumentNo(),
    MOrder.Table_ID, order.getC_Order_ID());
// This creates a clickable link in the process result

6.4 Complete Custom Process Example

/**
 * Custom procurement analytics process that generates a 
 * spend analysis report by vendor and product category.
 */
public class ProcurementSpendAnalysis extends SvrProcess {

    private int p_C_BPartner_ID = 0;
    private Timestamp p_DateFrom = null;
    private Timestamp p_DateTo = null;
    private int p_M_Product_Category_ID = 0;

    @Override
    protected void prepare() {
        ProcessInfoParameter[] params = getParameter();
        for (ProcessInfoParameter para : params) {
            String name = para.getParameterName();
            if ("C_BPartner_ID".equals(name))
                p_C_BPartner_ID = para.getParameterAsInt();
            else if ("DateOrdered".equals(name)) {
                p_DateFrom = (Timestamp) para.getParameter();
                p_DateTo = (Timestamp) para.getParameter_To();
            }
            else if ("M_Product_Category_ID".equals(name))
                p_M_Product_Category_ID = para.getParameterAsInt();
        }
    }

    @Override
    protected String doIt() throws Exception {
        StringBuilder sql = new StringBuilder(
            "SELECT bp.Name as VendorName, ")
            .append("pc.Name as Category, ")
            .append("SUM(ol.LineNetAmt) as TotalSpend, ")
            .append("COUNT(DISTINCT o.C_Order_ID) as OrderCount, ")
            .append("SUM(ol.QtyOrdered) as TotalQty ")
            .append("FROM C_Order o ")
            .append("JOIN C_OrderLine ol ON o.C_Order_ID = ol.C_Order_ID ")
            .append("JOIN C_BPartner bp ON o.C_BPartner_ID = bp.C_BPartner_ID ")
            .append("JOIN M_Product p ON ol.M_Product_ID = p.M_Product_ID ")
            .append("JOIN M_Product_Category pc ON p.M_Product_Category_ID = pc.M_Product_Category_ID ")
            .append("WHERE o.IsSOTrx = 'N' ")  // Purchase orders only
            .append("AND o.DocStatus IN ('CO','CL') ");

        ArrayList<Object> params = new ArrayList<>();

        if (p_C_BPartner_ID > 0) {
            sql.append("AND o.C_BPartner_ID = ? ");
            params.add(p_C_BPartner_ID);
        }
        if (p_DateFrom != null) {
            sql.append("AND o.DateOrdered >= ? ");
            params.add(p_DateFrom);
        }
        if (p_DateTo != null) {
            sql.append("AND o.DateOrdered <= ? ");
            params.add(p_DateTo);
        }
        if (p_M_Product_Category_ID > 0) {
            sql.append("AND p.M_Product_Category_ID = ? ");
            params.add(p_M_Product_Category_ID);
        }

        sql.append("GROUP BY bp.Name, pc.Name ")
           .append("ORDER BY TotalSpend DESC");

        int count = 0;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
            DB.setParameters(pstmt, params);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String vendor = rs.getString("VendorName");
                String category = rs.getString("Category");
                BigDecimal spend = rs.getBigDecimal("TotalSpend");
                int orders = rs.getInt("OrderCount");
                
                addLog(String.format("%s | %s | Spend: %s | Orders: %d",
                    vendor, category, spend, orders));
                count++;
            }
        } finally {
            DB.close(rs, pstmt);
        }

        return "@Processed@ " + count + " vendor-category combinations";
    }
}

7. Procurement Reporting SQL

The following SQL queries use actual iDempiere table and column names and can be adapted for custom reports, dashboard views, or external BI tools.

7.1 Purchase Order Status Dashboard

// Open PO Aging Report
String sql = 
    "SELECT bp.Name as Vendor, " +
    "  o.DocumentNo, o.DateOrdered, o.DatePromised, " +
    "  o.GrandTotal, o.DocStatus, " +
    "  CURRENT_DATE - o.DateOrdered as DaysOpen, " +
    "  CASE WHEN o.DatePromised < CURRENT_DATE THEN 'OVERDUE' " +
    "       ELSE 'ON TIME' END as Status " +
    "FROM C_Order o " +
    "JOIN C_BPartner bp ON o.C_BPartner_ID = bp.C_BPartner_ID " +
    "WHERE o.IsSOTrx = 'N' " +
    "  AND o.DocStatus = 'CO' " +            // Completed but not closed
    "  AND EXISTS (SELECT 1 FROM C_OrderLine ol " +
    "    WHERE ol.C_Order_ID = o.C_Order_ID " +
    "    AND ol.QtyOrdered > ol.QtyDelivered) " +  // Has undelivered lines
    "ORDER BY o.DatePromised ASC";

7.2 Vendor Spend Analysis

// Top vendors by spend in a date range
String sql = 
    "SELECT bp.Name, bp.Value as VendorCode, " +
    "  COUNT(DISTINCT o.C_Order_ID) as POCount, " +
    "  SUM(ol.LineNetAmt) as TotalSpend, " +
    "  AVG(ol.PriceActual) as AvgPrice, " +
    "  COUNT(DISTINCT ol.M_Product_ID) as ProductCount " +
    "FROM C_OrderLine ol " +
    "JOIN C_Order o ON ol.C_Order_ID = o.C_Order_ID " +
    "JOIN C_BPartner bp ON o.C_BPartner_ID = bp.C_BPartner_ID " +
    "WHERE o.IsSOTrx = 'N' " +
    "  AND o.DocStatus IN ('CO','CL') " +
    "  AND o.DateOrdered BETWEEN ? AND ? " +
    "GROUP BY bp.Name, bp.Value " +
    "ORDER BY TotalSpend DESC";

7.3 Stock Level Monitoring

// Products below reorder point
String sql = 
    "SELECT p.Value as ProductCode, p.Name as ProductName, " +
    "  r.Level_Min, r.Level_Max, r.ReplenishType, " +
    "  COALESCE(bomQtyOnHand(p.M_Product_ID, r.M_Warehouse_ID, 0), 0) as QtyOnHand, " +
    "  COALESCE((SELECT SUM(ol.QtyOrdered - ol.QtyDelivered) " +
    "    FROM C_OrderLine ol JOIN C_Order o ON ol.C_Order_ID = o.C_Order_ID " +
    "    WHERE ol.M_Product_ID = p.M_Product_ID " +
    "    AND o.IsSOTrx = 'N' AND o.DocStatus = 'CO'), 0) as QtyOnOrder, " +
    "  r.Level_Min - COALESCE(bomQtyOnHand(p.M_Product_ID, r.M_Warehouse_ID, 0), 0) as Shortage " +
    "FROM M_Replenish r " +
    "JOIN M_Product p ON r.M_Product_ID = p.M_Product_ID " +
    "WHERE r.ReplenishType <> '0' " +
    "  AND r.IsActive = 'Y' " +
    "  AND r.M_Warehouse_ID = ? " +
    "  AND COALESCE(bomQtyOnHand(p.M_Product_ID, r.M_Warehouse_ID, 0), 0) < r.Level_Min " +
    "ORDER BY Shortage DESC";

7.4 Receipt Performance

// Receipt turnaround time (PO Date to Receipt Date)
String sql = 
    "SELECT bp.Name as Vendor, " +
    "  o.DocumentNo as PO_Number, " +
    "  o.DateOrdered, o.DatePromised, " +
    "  io.MovementDate as ReceiptDate, " +
    "  io.MovementDate - o.DateOrdered as TotalLeadDays, " +
    "  io.MovementDate - o.DatePromised as DaysLateEarly " +
    "FROM M_InOut io " +
    "JOIN C_Order o ON io.C_Order_ID = o.C_Order_ID " +
    "JOIN C_BPartner bp ON o.C_BPartner_ID = bp.C_BPartner_ID " +
    "WHERE io.MovementType = 'V+' " +    // Vendor Receipts
    "  AND io.DocStatus IN ('CO','CL') " +
    "  AND io.MovementDate BETWEEN ? AND ? " +
    "ORDER BY bp.Name, io.MovementDate";

8. Automation Patterns

8.1 Scheduled Process Execution

iDempiere supports automated process execution through the AD_Scheduler table. The replenishment report can be scheduled to run daily or weekly:

// Scheduler configuration (via Application Dictionary):
// AD_Scheduler record:
//   AD_Process_ID → ReplenishReport process
//   ScheduleType → "F" (Frequency) or "C" (Cron)
//   FrequencyType → "D" (Daily), "W" (Weekly), "M" (Monthly)
//   Frequency → 1 (every 1 day/week/month)
//   AD_Schedule_ID → links to parameter values

// Parameters stored in AD_Scheduler_Para:
//   M_Warehouse_ID → target warehouse
//   ReplenishmentCreate → "POO" (auto-create POs)
//   C_DocType_ID → PO document type

8.2 ModelValidator for Event-Driven Procurement

A ModelValidator can trigger procurement actions automatically in response to business events:

public class AutoProcurementValidator implements ModelValidator {
    private int m_AD_Client_ID = -1;

    @Override
    public void initialize(ModelValidationEngine engine, MClient client) {
        if (client != null)
            m_AD_Client_ID = client.getAD_Client_ID();
        // Listen for inventory changes
        engine.addDocValidate(MInOut.Table_Name, this);
    }

    @Override
    public int getAD_Client_ID() {
        return m_AD_Client_ID;
    }

    @Override
    public String docValidate(PO po, int timing) {
        if (timing == TIMING_AFTER_COMPLETE && po instanceof MInOut) {
            MInOut inout = (MInOut) po;
            // Only for customer shipments (reduces stock)
            if (inout.isSOTrx() && 
                "C-".equals(inout.getMovementType())) {
                checkReplenishment(inout);
            }
        }
        return null;
    }

    private void checkReplenishment(MInOut shipment) {
        for (MInOutLine line : shipment.getLines()) {
            int productId = line.getM_Product_ID();
            int warehouseId = shipment.getM_Warehouse_ID();
            
            // Check if stock fell below reorder point
            MReplenish rule = MReplenish.getForProduct(
                shipment.getCtx(), productId, warehouseId, 
                shipment.get_TrxName());
            
            if (rule != null && 
                !X_M_Replenish.REPLENISHTYPE_Manual.equals(
                    rule.getReplenishType())) {
                BigDecimal qtyOnHand = MStorageOnHand.getQtyOnHand(
                    productId, warehouseId, 0, 
                    shipment.get_TrxName());
                
                if (qtyOnHand.compareTo(rule.getLevel_Min()) < 0) {
                    // Stock below minimum — log alert
                    MNote note = new MNote(shipment.getCtx(), 
                        "Reorder Alert", 0, shipment.get_TrxName());
                    note.setTextMsg("Product " + productId + 
                        " below reorder point. QtyOnHand: " + qtyOnHand +
                        ", Level_Min: " + rule.getLevel_Min());
                    note.saveEx();
                }
            }
        }
    }

    @Override
    public String modelChange(PO po, int type) { return null; }

    @Override
    public String login(int AD_Org_ID, int AD_Role_ID, int AD_User_ID) { 
        return null; 
    }
}

9. Complete Code Examples

9.1 Stock Level Monitoring Process

/**
 * Process to check stock levels and generate alerts for 
 * products below their reorder points.
 */
public class StockLevelMonitor extends SvrProcess {

    private int p_M_Warehouse_ID = 0;

    @Override
    protected void prepare() {
        ProcessInfoParameter[] params = getParameter();
        for (ProcessInfoParameter para : params) {
            if ("M_Warehouse_ID".equals(para.getParameterName()))
                p_M_Warehouse_ID = para.getParameterAsInt();
        }
        if (p_M_Warehouse_ID == 0)
            p_M_Warehouse_ID = getParameterAsInt("M_Warehouse_ID");
    }

    @Override
    protected String doIt() throws Exception {
        if (p_M_Warehouse_ID == 0)
            throw new AdempiereException("@M_Warehouse_ID@ @NotFound@");

        String sql = 
            "SELECT r.M_Product_ID, r.Level_Min, r.Level_Max, " +
            "  r.ReplenishType, r.C_BPartner_ID " +
            "FROM M_Replenish r " +
            "WHERE r.M_Warehouse_ID = ? " +
            "  AND r.ReplenishType <> '0' " +
            "  AND r.IsActive = 'Y'";

        int alertCount = 0;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = DB.prepareStatement(sql, get_TrxName());
            pstmt.setInt(1, p_M_Warehouse_ID);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                int productId = rs.getInt("M_Product_ID");
                BigDecimal levelMin = rs.getBigDecimal("Level_Min");

                BigDecimal qtyOnHand = MStorageOnHand.getQtyOnHand(
                    productId, p_M_Warehouse_ID, 0, get_TrxName());
                if (qtyOnHand == null)
                    qtyOnHand = Env.ZERO;

                if (qtyOnHand.compareTo(levelMin) < 0) {
                    MProduct product = MProduct.get(getCtx(), productId);
                    BigDecimal shortage = levelMin.subtract(qtyOnHand);
                    addLog(String.format(
                        "ALERT: %s [%s] - OnHand: %s, Min: %s, Short: %s",
                        product.getName(), product.getValue(),
                        qtyOnHand, levelMin, shortage));
                    alertCount++;
                }
            }
        } finally {
            DB.close(rs, pstmt);
        }

        return alertCount > 0 
            ? "@Alert@ " + alertCount + " products below reorder point"
            : "All stock levels OK";
    }
}

9.2 Automated PO Creation from Replenishment

// Programmatic execution of ReplenishReport to create POs:
MPInstance instance = new MPInstance(ctx, processId, 0);
instance.saveEx();

// Set parameters
MPInstancePara para1 = new MPInstancePara(instance, 10);
para1.setParameterName("M_Warehouse_ID");
para1.setP_Number(warehouseId);
para1.saveEx();

MPInstancePara para2 = new MPInstancePara(instance, 20);
para2.setParameterName("ReplenishmentCreate");
para2.setP_String("POO");  // Create Purchase Orders
para2.saveEx();

// Execute
ProcessInfo pi = new ProcessInfo("Replenishment", processId);
pi.setAD_PInstance_ID(instance.getAD_PInstance_ID());
pi.setAD_Client_ID(Env.getAD_Client_ID(ctx));

ServerProcessCtl.process(pi, null);

if (pi.isError()) {
    log.severe("Replenishment failed: " + pi.getSummary());
} else {
    log.info("Replenishment completed: " + pi.getSummary());
}

10. Common Pitfalls and Best Practices

11. Summary

• MReplenish defines per-product, per-warehouse rules with four types: Manual (0), Reorder Below Min (1), Maintain Max (2), Custom (9). Key fields: Level_Min, Level_Max, M_WarehouseSource_ID, C_BPartner_ID.
• MStorageOnHand tracks on-hand inventory at the product/locator/ASI/date level. The add() method chain creates or updates records via getOrCreate(). Query methods support warehouse, locator, reservation, and shipping granularities.
• The beforeSave() negative inventory check on MStorageOnHand enforces the warehouse-level IsDisallowNegativeInv flag, preventing inventory from going below zero.
• ReplenishReport is the main engine: populates T_Replenish, calculates quantities, applies Order_Min/Order_Pack constraints, and generates POs (POO), Requisitions (POR), Movements (MMM), or Distribution Orders (DOO).
• ReplenishReportProduction extends the standard report with manufacturing order generation and BOM-based replenishment.
• The SvrProcess pattern (prepare()/doIt()) is the foundation for all custom processes. Use getParameterAsInt(), addLog(), and addBufferLog() for parameter access and user feedback.
• Procurement SQL queries use standard iDempiere tables: C_Order/C_OrderLine for POs, M_InOut/M_InOutLine for receipts, M_Product_PO for vendor products, M_Replenish/M_StorageOnHand for inventory.
• AD_Scheduler enables automated ReplenishReport execution on a cron/frequency basis for hands-off procurement.
• ModelValidator approach enables event-driven procurement: trigger reorder alerts or automatic PO creation when stock falls below minimums after customer shipments.
• Always set IsCurrentVendor = 'Y' on at least one M_Product_PO record per product — the ReplenishReport requires this to generate orders.