How to Build a Complete Production Planning Tool in ERPNext

ERPNext provides powerful manufacturing features such as Bill of Materials (BOM), Work Orders, and Material Requests. However, many factories need an additional layer: a Production Planning Tool that can forecast materials, generate work orders automatically, schedule capacity, and track Work-In-Progress (WIP).

This technical guide explains how to build a complete Production Planning Tool inside ERPNext using custom apps, DocTypes, client and server scripts, workflows, SQL reports, and integration features. It is written for ERPNext developers, technical consultants, and advanced implementers.

1. Technical Architecture of a Production Planning Tool

The Production Planning Tool sits logically between Sales Orders and Work Orders. It reads confirmed demand, checks available stock, explodes BOMs, and prepares a feasible production schedule based on workstation capacity and material availability.

Core ERPNext manufacturing components it integrates with:

Component	Purpose
Bill of Materials (BOM)	Defines parent item structure and component items
Work Order	Execution document for manufacturing a given item and quantity
Operations	Defines routing steps such as Cutting, Welding, Painting
Workstation	Represents machines, production lines, or work centers with capacity
Stock Entry	Handles material issues, receipts, and transfers during production

High-level process flow:

2. Creating a Custom App for Planning

Start by creating a separate custom app for the Production Planning logic. This keeps customizations modular and version-controlled.

Steps

1. Initialize a new bench (if not already created).
2. Create a new site for development.
3. Create a custom app for production planning.
4. Install the app on the target site.

bench init mybench
cd mybench
bench new-site demo.local
bench new-app production_planner
bench --site demo.local install-app production_planner
  

In production, you would install the same app to your live site and keep the app in Git for CI/CD.

3. New DocType: Production Plan

The central entity of the tool is a custom DocType named Production Plan. It aggregates items that need to be produced, their quantities, material availability, and resulting work orders.

Key Fields

Field	Field Type	Purpose
plan_date	Date	Date for which planning is done
sales_order	Link (Sales Order)	Source of demand for the plan
items	Table (Production Plan Item)	Child table listing item-wise planning details
status	Select	Draft, In Review, In Production, Completed

Select “Is Submittable” if you want the Production Plan to support Draft → Submitted → Cancelled states in addition to your custom workflow.

4. Child Table: Planning Items

The child table Production Plan Item holds item-level details derived from Sales Orders and inventory status.

Field	Type	Description
item_code	Link (Item)	Item to be manufactured
required_qty	Float	Quantity required from confirmed demand
bom	Link (BOM)	Selected BOM for this item
available_qty	Float	Current available stock (from warehouses)
shortage_qty	Float	Required quantity minus available quantity

Additional fields like planned_start_date, planned_end_date, workstation, and routing can be added based on the complexity of your factory.

5. Auto Fetch Sales Orders (Client Script)

When a user selects a Sales Order in Production Plan, the system should automatically fetch its items and populate the child table. This can be done with a Client Script calling a whitelisted Python method.

frappe.ui.form.on("Production Plan", {
    sales_order: function(frm) {
        if (!frm.doc.sales_order) return;

        frappe.call({
            method: "production_planner.api.fetch_items_from_sales_order",
            args: {
                sales_order: frm.doc.sales_order
            },
            callback: function(r) {
                if (!r.message) return;

                frm.clear_table("items");
                r.message.forEach(function(row) {
                    let child = frm.add_child("items");
                    child.item_code = row.item_code;
                    child.required_qty = row.required_qty;
                    child.available_qty = row.available_qty;
                    child.shortage_qty = row.shortage_qty;
                    child.bom = row.bom;
                });
                frm.refresh_field("items");
            }
        });
    }
});
  

6. Server Script: Fetch Items from Sales Order

The server-side function reads Sales Order items, checks available inventory, and returns structured data to the client.

import frappe

def get_available_qty(item_code):
    bin_data = frappe.db.sql(
        """
        SELECT IFNULL(SUM(actual_qty), 0)
        FROM `tabBin`
        WHERE item_code = %s
        """,
        (item_code,)
    )
    return bin_data[0][0] if bin_data else 0

@frappe.whitelist()
def fetch_items_from_sales_order(sales_order):
    so = frappe.get_doc("Sales Order", sales_order)
    data = []

    for item in so.items:
        available = get_available_qty(item.item_code)
        shortage = max(item.qty - available, 0)
        data.append({
            "item_code": item.item_code,
            "required_qty": item.qty,
            "available_qty": available,
            "shortage_qty": shortage,
            "bom": item.bom or frappe.db.get_value("BOM",
                {"item": item.item_code, "is_default": 1}, "name")
        })

    return data
  

7. Table for Material Shortage Calculation

Once the Sales Order items are fetched and stock is checked, the planner can visually understand which items have shortages and require production or purchase.

Item	Required Qty	Available Qty	Shortage Qty
ITEM-001	50	20	30
ITEM-002	10	10	0
ITEM-003	100	40	60

Items with zero shortage may not require new Work Orders if enough stock is already available.

8. Automated BOM Explosion

To understand raw material requirements, you must explode each selected BOM and aggregate component quantities across all planned finished goods.

def get_bom_components(bom_name, qty):
    components = []
    bom_items = frappe.get_all(
        "BOM Item",
        filters={"parent": bom_name},
        fields=["item_code", "qty"]
    )

    for bi in bom_items:
        components.append({
            "item_code": bi.item_code,
            "required_qty": bi.qty * qty
        })

    return components
  

This logic can be extended to multi-level BOMs by recursively reading child BOMs and aggregating the results in a dictionary keyed by item_code.

9. Workflow for Production Plan

Define a custom workflow to move the Production Plan through stages of review and execution.

State	Allowed Role	Action	Next State
Draft	Planner	Submit for Review	Material Review
Material Review	Planning Manager	Approve Materials	Capacity Planning
Capacity Planning	Planner	Generate Work Orders	Work Order Created
Work Order Created	System	Start Production	In Production
In Production	Production Supervisor	Mark Completed	Completed

10. Auto Work Order Creation Script

A key automation is to create Work Orders directly from the Production Plan, based on planned items and their selected BOMs.

def create_work_orders(plan_name):
    plan = frappe.get_doc("Production Plan", plan_name)

    for row in plan.items:
        if row.shortage_qty <= 0:
            # no shortage, optional to skip auto WO
            continue

        wo = frappe.get_doc({
            "doctype": "Work Order",
            "production_item": row.item_code,
            "qty": row.required_qty,
            "bom_no": row.bom,
            "planned_start_date": plan.plan_date,
            "description": "Created from Production Plan {0}".format(plan.name)
        })
        wo.insert()
        wo.submit()
  

This function can be called on a workflow transition or via a custom button on the Production Plan form.

11. Capacity Planning Logic

Workstation capacity planning ensures that you do not overload machines beyond their daily or shift-based limits. A simple capacity model looks at operation time per part and total planned quantity.

Basic formula:

def calculate_capacity_utilization(workstation, required_time):
    ws = frappe.get_doc("Workstation", workstation)
    available_capacity = ws.hour_rate or 8  # assume 8 hours if not set
    utilization = (required_time / available_capacity) * 100
    return utilization
  

You can create a dedicated child table in Production Plan to record workstation-wise loads and utilization.

12. Workstation Capacity Table

Present capacity and load data clearly to planners so they can adjust schedules, split batches, or move work to alternate workstations.

Workstation	Capacity (hrs/day)	Required Time (hrs)	Load Status
CNC-1	8	12	Overloaded
CNC-2	8	6	OK
Lathe-1	8	8	Fully Loaded

13. Gantt Chart Scheduling Using API

A Gantt-style schedule can be built by mapping operations to time slots based on required time and workstation capacity. In ERPNext, you can generate start and end times and feed them into a Calendar or custom Gantt view.

from frappe.utils import now_datetime, add_to_date

def plan_operation(wo_name, hours_required):
    start = now_datetime()
    end = add_to_date(start, hours=hours_required)

    # example: store planned times on Work Order itself
    wo = frappe.get_doc("Work Order", wo_name)
    wo.planned_start_date = start
    wo.planned_end_date = end
    wo.save()
  

On the client side, you can build a custom page to render these planned time windows as bar segments.

14. WIP (Work In Progress) Tracking

WIP tracking measures how much of a Work Order is completed. You can calculate a simple progress percentage using produced quantity vs planned quantity.

def get_wip_progress(work_order):
    wo = frappe.get_doc("Work Order", work_order)
    if not wo.qty:
        return 0

    completed = wo.produced_qty or 0
    progress = (completed / wo.qty) * 100
    return round(progress, 2)
  

This progress can be shown in Work Order dashboards, Production Plan dashboards, or even as a percentage bar in list views.

15. Automatic Material Request for Shortage Items

Shortage items identified at the Production Plan stage can automatically create Material Requests, either for Purchase or for Material Transfer between warehouses.

def create_material_requests_from_plan(plan_name):
    plan = frappe.get_doc("Production Plan", plan_name)

    for row in plan.items:
        if row.shortage_qty <= 0:
            continue

        mr = frappe.get_doc({
            "doctype": "Material Request",
            "material_request_type": "Purchase",
            "schedule_date": plan.plan_date,
            "items": [{
                "item_code": row.item_code,
                "qty": row.shortage_qty,
                "schedule_date": plan.plan_date
            }]
        })
        mr.insert()
        mr.submit()
  

16. Production Calendar View (Custom Page)

A calendar-style view helps planners see which days are heavily loaded or free. ERPNext supports Calendar views that can be bound to any DocType with date fields.

frappe.views.calendar["Production Plan"] = {
    field_map: {
        start: "plan_date",
        end: "plan_date",
        id: "name",
        title: "name"
    },
    order_by: "plan_date",
    get_events_method: "production_planner.api.get_plans_for_calendar"
};
  

The server method get_plans_for_calendar should return a list of dicts with keys such as name, plan_date, and reference status.

17. Dashboard KPIs

A dedicated dashboard can show planners a summary of production performance. Common KPIs include on-time production, capacity utilization, and shortage rate.

KPI	Formula
On-time Production %	Completed on or before due date / Total Work Orders × 100
Capacity Utilization %	Total Required Time / Total Available Capacity × 100
Material Shortage %	Number of Shortage Items / Total Planned Items × 100

def get_production_kpis():
    total_wo = frappe.db.count("Work Order")
    on_time = frappe.db.count("Work Order", {
        "status": "Completed",
        "production_completion_date": ["<=", "planned_end_date"]
    })

    on_time_percent = (on_time / total_wo * 100) if total_wo else 0

    return {
        "total_work_orders": total_wo,
        "on_time_percent": round(on_time_percent, 2)
    }
  

18. SQL Report for Pending Work Orders

Build a custom Query Report to show Work Orders that are not started or are delayed against their planned dates.

SELECT
    name,
    production_item,
    qty,
    status,
    planned_start_date,
    planned_end_date
FROM
    `tabWork Order`
WHERE
    status IN ('Not Started', 'In Process')
ORDER BY
    planned_start_date;
  

Attach this Query Report to the Production Planning dashboard to give planners a quick view of open work.

19. Complete Workflow Summary

The final Production Planning process integrates multiple ERPNext documents and custom logic into a smooth end-to-end workflow.

20. Final Deployment Steps

Once your Production Planning Tool is tested in a development or staging site, you can deploy to production using standard bench and Git workflows.

1. Push the custom app to a Git repository.
2. On the production server, pull the latest version.
3. Run bench migrate to apply schema and patches.
4. Reload DocTypes, Client Scripts, Reports, and Workflows.
5. Test with sample Sales Orders and Work Orders.

# Example deployment sequence
git add .
git commit -m "Add production planning tool"
git push origin main

# On production bench
git pull origin main
bench migrate
bench reload-doc production_planner
bench restart
  

21. Dynamic Routing Selection Based on Item Attributes

In many factories, routing depends on item characteristics such as material type, thickness, grade, or surface finish. Instead of manually choosing routings during Work Order creation, you can select routing dynamically based on Item attributes.

Example logic:

• Steel items above 5mm thickness require a “Heavy Steel Route”.
• Plastic items require a “Plastic Molding Route”.
• Otherwise, a standard route is applied.

def get_dynamic_routing(item_code):
    item = frappe.get_doc("Item", item_code)

    # Example attributes: material_type and thickness are custom fields
    if item.material_type == "Steel" and (item.thickness or 0) > 5:
        return "Steel-Heavy-Route"

    if item.material_type == "Plastic":
        return "Plastic-Molding-Route"

    return "Standard-Route"
  

This function can be called from a Work Order hook to automatically set the routing field before saving or submitting the document.

22. Predictive Material Planning Using Historical Consumption Patterns

ERPNext’s reorder level logic is reactive. A more advanced approach is to use historical consumption to predict future material requirements and adjust plans accordingly.

1. Fetch 6–12 months of consumption data for key items from Stock Ledger Entries.
2. Compute an average or weighted moving average consumption.
3. Project required consumption for the next planning period.
4. Compare projection to available and incoming stock.
5. Automatically create Material Requests where projected demand exceeds supply.

SELECT
    item_code,
    SUM(actual_qty) AS net_qty
FROM
    `tabStock Ledger Entry`
WHERE
    posting_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
    AND actual_qty < 0
GROUP BY
    item_code;
  

The result can be processed in Python to compute average monthly usage and then multiplied by a forecast period (for example, next 2 months) to guide purchasing decisions.

23. Operation Parallelization for Multi-Machine Capability

Many factories have multiple identical machines that can run the same operation in parallel. Instead of scheduling all workload on a single workstation, you can automatically distribute operations across multiple machines.

Basic allocation logic:

def allocate_operation_to_machine(operation_name):
    operation = frappe.get_doc("Operation", operation_name)

    machines = frappe.get_all(
        "Workstation",
        filters={"is_group": 0, "disabled": 0},
        fields=["name"]
    )

    # Example: choose a machine randomly or based on your own load logic
    chosen_machine = machines[0]["name"] if machines else None

    if chosen_machine:
        operation.workstation = chosen_machine
        operation.save()
  

A more advanced implementation would maintain a custom table of machine loads and dynamically select the workstation with the lowest total assigned hours.

24. Real-Time Production Monitoring Using WebSockets

ERPNext supports realtime updates using WebSockets. You can push production status changes directly to planners’ browsers when Work Orders or operations are updated, providing a live production dashboard.

Server-Side Event Publish

import frappe

def publish_production_update(wo_name):
    wo = frappe.get_doc("Work Order", wo_name)
    progress = get_wip_progress(wo_name)

    frappe.publish_realtime(
        event="production_update",
        message={
            "work_order": wo.name,
            "item": wo.production_item,
            "progress": progress,
            "status": wo.status
        }
    )
  

Client-Side Listener

frappe.realtime.on("production_update", function(data) {
    // Update dashboard widgets or list views
    console.log("Live production update:", data);
});
  

This allows monitoring boards showing live status without page refreshes, especially useful in control rooms or production floor displays.

25. Subcontracting Integration With Production Planning Flow

Some operations, such as heat treatment, plating, or specialized machining, may be subcontracted. The Production Planning Tool can mark such operations and automatically create Subcontract Purchase Orders.

Extended flow:

def create_subcontract_po_for_operation(operation):
    if not operation.requires_subcontracting:
        return

    po = frappe.get_doc({
        "doctype": "Purchase Order",
        "supplier": operation.subcontractor,
        "is_subcontracted": 1,
        "items": [{
            "item_code": operation.subcontract_item,
            "qty": operation.qty,
            "schedule_date": operation.planned_start_date
        }]
    })
    po.insert()
    po.submit()
  

This ensures subcontracting is tightly integrated into the production schedule, rather than handled separately in purchasing.

26. Custom Alerts & SLA Monitoring for Production Delays

Production delays can be monitored with SLA logic. You can track if operations or Work Orders exceed planned times and send alerts or escalate to supervisors.

Example delay detection:

from frappe.utils import now_datetime

def check_for_delayed_operations():
    operations = frappe.get_all(
        "Operation",
        filters={"status": ["in", ["In Process", "Not Started"]]},
        fields=["name", "planned_end_time", "status"]
    )

    now = now_datetime()
    for op in operations:
        if op.planned_end_time and now > op.planned_end_time:
            # send alert
            frappe.sendmail(
                recipients=["prod.supervisor@example.com"],
                subject="Operation Delay: {0}".format(op.name),
                message="Operation {0} has exceeded its planned end time.".format(op.name)
            )
  

Metric	Definition
Delay Rate	Delayed Work Orders / Total Work Orders × 100
SLA Breach %	Number of operations exceeding planned time / Total operations × 100

27. Machine Maintenance Integration With Production Scheduling

Planned maintenance must be considered during scheduling. You can create a Machine Maintenance schedule and ensure that capacity calculations exclude maintenance periods.

Machine	Maintenance Date	Maintenance Type	Status
CNC-1	2025-12-15	Preventive	Planned
Lathe-3	2025-12-18	Inspection	Planned

def is_machine_available(machine, date):
    maintenance = frappe.get_all(
        "Machine Maintenance",
        filters={"machine": machine, "maintenance_date": date, "status": "Planned"}
    )
    return len(maintenance) == 0
  

When scheduling operations, call is_machine_available and if the result is false, move the operation to another day or workstation.

28. AI-Based Production Time Estimation (Optional Add-On)

For advanced setups, machine learning models can be used to estimate production time more accurately than static standard times. Features can include item complexity, material type, operation type, and operator performance.

The model can be trained outside ERPNext and exposed via an API. ERPNext calls the API to fetch predicted time for a given operation.

import requests

def get_ai_estimated_time(item_code, qty, workstation):
    payload = {
        "item_code": item_code,
        "qty": qty,
        "workstation": workstation
    }
    response = requests.post("https://your-ml-api/estimate_time", json=payload)
    if response.ok:
        return response.json().get("estimated_hours", 0)
    return 0
  

This estimate can then feed into capacity planning, scheduling, and WIP projections.

29. Automated Quality Inspection Workflow

Quality checks can be enforced as part of the Production Planning flow. Each Work Order can generate Quality Inspection templates based on item and operation types, and production should only proceed when QC passes.

QC Parameter	Min	Max	Observed	Result
Thickness (mm)	4.5	4.6	4.55	Pass
Hardness (HRC)	60	65	68	Fail

def handle_qc_result(qc_doc):
    if qc_doc.status == "Fail":
        wo = frappe.get_doc("Work Order", qc_doc.reference_name)
        wo.status = "On Hold"
        wo.save()
  

30. Production Analytics Dashboard With Custom SQL & Charts

A final layer of the Production Planning Tool is analytics. Combining SQL queries with ERPNext’s chart components, you can build dashboards for machine utilization, delay statistics, and material shortages.

SELECT
    workstation,
    SUM(actual_time) AS total_time,
    SUM(capacity) AS total_capacity,
    (SUM(actual_time) / SUM(capacity)) * 100 AS utilization
FROM
    `tabOperation Log`
GROUP BY
    workstation;
  

frappe.call({
    method: "production_planner.api.get_machine_utilization",
    callback: function(r) {
        new frappe.Chart("#machine-utilization-chart", {
            data: r.message,
            type: "bar"
        });
    }
});
  

This gives planners a clear, data-driven view of how effectively machines and operations are being used, and supports continuous improvement initiatives.