Batch Management in Discrete & Process Manufacturing: Lot Traceability, Potency & Shelf-Life
Complete masterclass on tracking batches from production to dispatch across SAP PP, Oracle Process Manufacturing, Odoo, and custom SQL Server ERPs. Includes Bangladeshi pharmaceutical & food industry scenarios, Taka cost impacts, DBA performance tricks, and AI-driven future trends.
1. What is Batch Management in Manufacturing?
Batch management is the practice of grouping produced items into discrete lots, each assigned a unique identifier (batch number), and tracking the characteristics, quantities, and movements of these lots throughout the supply chain. In discrete manufacturing (electronics, machinery), batches often refer to production runs of identical items. In process manufacturing (pharmaceuticals, food, chemicals), batches represent a specific quantity of product made in one production cycle, with inherent variability in quality attributes like potency, moisture, or purity.
1.1 The Three Pillars of Batch Traceability
- Genealogy – The parent-child relationships between raw material batches, intermediate batches, and finished product batches. If a customer reports a defect, you must trace back to the exact raw material lot.
- Characteristics – Quantitative or qualitative attributes (potency 98.5%, moisture 2.1%, assay value, pH level) that determine whether a batch meets specifications.
- Shelf-Life & Expiry – Each batch has a production date and expiry date. Managing FIFO/FEFO (First-Expired-First-Out) is critical to avoid waste and regulatory penalties.
1.2 Discrete vs. Process Manufacturing – Key Differences in Batch Tracking
| Aspect | Discrete Manufacturing | Process Manufacturing |
|---|---|---|
| Product Nature | Countable items (e.g., mobile phones, garments) | Bulk, liquid, powder (e.g., syrup, spice mix) |
| Batch Definition | Production order lot | Process order lot with variable yield |
| Potency/Variability | Minimal variation; items are identical | Significant batch-to-batch variation; potency adjustment |
| Traceability Focus | Serial numbers, component genealogy | Batch characteristics, by-products, rework |
| BOM Structure | Fixed BOM, exact quantities | Formula-based, scaling, potency-adjusted |
| ERP Examples | SAP PP, Oracle Discrete Mfg, D365 | SAP PP-PI, Oracle Process Mfg, Odoo MRP |
Bangladeshi Context: The garment industry (discrete) tracks batches by cutting order and production line. The pharmaceutical sector (process) tracks active ingredient potency from raw API batches to final syrup or tablet batches. A single batch of paracetamol tablets may use API from two different supplier lots, requiring precise genealogy.
2. Why Batch Traceability is Critical in Bangladesh – Regulatory & Financial Impact
Bangladesh's manufacturing sector, especially pharmaceuticals and food processing, operates under strict regulations from the Directorate General of Drug Administration (DGDA) and the Bangladesh Standards and Testing Institution (BSTI). Batch traceability is not just a best practice; it's a legal requirement for export and domestic sales.
2.1 Regulatory Requirements in Bangladesh
- DGDA mandates full batch records for every pharmaceutical product, including raw material batch numbers, equipment used, personnel involved, and QC test results. Failure to produce batch genealogy on demand can lead to license suspension.
- BSTI requires food manufacturers to implement traceability systems under the Food Safety Act. Recalls must be traceable within hours, not days.
- Export markets (EU, US FDA, WHO-GMP) demand electronic batch records and complete lot traceability. Bangladeshi manufacturers exporting to these markets face audits where batch mismanagement is a major non-conformance.
2.2 The Cost of Poor Batch Management – A Bangladeshi Example
Consider Dhaka Agro Foods Ltd., a leading spice and snack manufacturer in Tongi. They produce 50+ SKUs including turmeric powder, chili powder, and chanachur. Without proper batch tracking, a routine quality check revealed that a batch of turmeric powder (Batch #TF-2026-08-15) had moisture content above acceptable limits. The company could only identify that the batch was produced in the previous week, but couldn't trace which raw turmeric lot was used or which other products might be contaminated. The recall took 4 days and cost over ৳2.5 crore in lost inventory and customer compensation. With proper ERP batch management, the recall would have taken less than 2 hours, affecting only the specific batch.
2.3 Common Grey Areas Where Batch Tracking Fails
3. SAP PP: Batch Characteristics, Genealogy & Shelf-Life Management
SAP ECC and S/4HANA offer comprehensive batch management through the Classification System and Batch Management module. For process industries, SAP PP-PI (Production Planning for Process Industries) adds formula management and potency calculation.
3.1 SAP Batch Characteristics and Class Types
In SAP, each batch is assigned to a class (e.g., class BATCH_STD) with characteristics that define quality parameters. Common characteristics for Bangladeshi pharma:
POTENCY– Active ingredient concentration (e.g., 98.0% to 102.0%)ASSAY– Chemical purity test resultMOISTURE– Moisture content percentageEXPIRY_DATE– Shelf-life expiryMANUFACTURING_DATEQC_STATUS– Released, Blocked, Under TestRELEASE_DATESUPPLIER_LOT– Original vendor batch for traceability
-- SAP Table: MCH1 (Batch Master) and AUSP (Characteristic Values)
SELECT mchb.matnr, mchb.charg, mchb.werks, mchb.lgort,
ausp.atinn, ausp.atwrt, ausp.atflv
FROM mchb
JOIN ausp ON mchb.charg = ausp.objek
WHERE mchb.matnr = 'PARACETAMOL_500'
AND ausp.klart = '023' " class type 023 = batch
ORDER BY mchb.charg;
3.2 Batch Genealogy in SAP
SAP automatically records the parent-child relationship between batches when materials are consumed in production orders. The Batch Where-Used List (transaction MB56) shows all parent batches that consumed a specific batch. This is critical for recalls.
3.3 Potency Adjustment in SAP PP-PI
Process manufacturing often requires potency adjustment – if a raw material batch has higher than standard potency, the quantity used must be adjusted proportionally. SAP handles this through:
- Material Quantity Calculation (MQC) – Custom formula functions that adjust input quantities based on characteristic values.
- Batch-specific unit of measure – Using a "theoretical quantity" based on assay.
- Recipe with active ingredient quantity – The formula specifies the required active ingredient amount, not total weight; SAP calculates the actual weight based on potency.
4. Oracle Process Manufacturing: Lot Traceability & Formula Management
Oracle Process Manufacturing (OPM) is part of Oracle E-Business Suite and Oracle Cloud SCM. It is designed specifically for process industries with features like formula management, lot genealogy, and quality integration.
4.1 Oracle OPM Lot and Batch Control
OPM uses Lots (equivalent to batches) and tracks them through the entire production process. Each lot has a unique number and can be assigned multiple attributes.
| Oracle OPM Feature | Description | Bangladesh Use Case |
|---|---|---|
| Formula | Recipe with ingredients, quantities, and process instructions | Paracetamol syrup formula with API, sugar, preservatives |
| Batch/Lot | Specific production run with unique ID | Batch number like PS-2609-001 |
| Lot Attributes | User-defined characteristics (potency, moisture, etc.) | Assay value 99.2%, moisture 1.8% |
| Genealogy | Parent-child links between input and output lots | Finished batch links to raw API lot |
| Expiry Date | Shelf-life tracking with FEFO logic | Syrup expiry 24 months from manufacturing |
4.2 Lot Genealogy in Oracle OPM
OPM automatically creates genealogy records when a batch is produced. The Lot Genealogy Inquiry shows all parent lots consumed and child lots produced. This is essential for regulatory compliance.
-- Oracle SQL: Query lot genealogy for a specific batch
SELECT lg.lot_number AS parent_lot,
lg.item_name AS input_item,
lg.qty AS input_qty,
l2.lot_number AS output_lot,
l2.item_name AS output_item
FROM gme_lot_genealogy lg
JOIN gme_batch_header bh ON lg.batch_id = bh.batch_id
JOIN gme_lot_numbers l2 ON lg.output_lot_id = l2.lot_id
WHERE l2.lot_number = 'PS-2609-001';
4.3 Potency and Formula Scaling in OPM
Oracle OPM supports Formula Scaling and Potency Adjustment through Process Parameter Calculations. You can define formulas that calculate ingredient quantities based on the active ingredient potency of input lots. For example, if a raw material lot has 95% potency instead of 100%, the formula calculates the extra quantity needed.
5. Odoo: Batch Routes, Lot Tracking & Expiry Management
Odoo, a popular open-source ERP, provides solid batch management features through its Inventory and Manufacturing modules. While not as complex as SAP or Oracle, Odoo is widely used by small to mid-sized Bangladeshi manufacturers due to its affordability and flexibility.
5.1 Odoo Lot and Serial Number Tracking
Odoo allows you to assign lot numbers to products. For process manufacturing, you can create Manufacturing Orders that consume raw materials by lot and produce finished goods by lot. Key features:
- Lot Properties – Custom fields like potency, expiry date, QC status.
- Expiry Dates – Odoo supports FEFO (First Expiry First Out) in warehouse operations.
- Parent-Child Traceability – Odoo's "Traceability" report shows upstream and downstream movements of a lot.
- Quality Alerts – Create quality checks linked to lots, with automatic blocking of non-conforming batches.
5.2 Odoo Consignment & Batch Routes
For a Bangladeshi food processor, Odoo's Routes can be configured to handle batch-specific workflows. For example:
- Quality Control Route – Finished goods must pass QC before being moved to saleable stock.
- Rework Route – Batches that fail QC but can be reprocessed are sent to a rework location.
- Expiry Route – Batches nearing expiry can trigger automatic alerts or price reductions.
5.3 Odoo Code Example: Auto-Assign Expiry Date
# Odoo Python: Automatically set expiry date based on product category
from odoo import models, fields, api
class StockLot(models.Model):
_inherit = 'stock.lot'
@api.model
def create(self, vals):
if 'product_id' in vals and 'expiration_date' not in vals:
product = self.env['product.product'].browse(vals['product_id'])
if product.categ_id.name == 'Pharmaceuticals':
vals['expiration_date'] = fields.Date.today() + timedelta(days=product.shelf_life_days)
return super().create(vals)
6. Custom SQL Server: Handling Batch Splitting & Merging – A DBA's Performance Nightmare
Many Bangladeshi manufacturers run custom in-house ERP systems built on SQL Server or Oracle databases. While these offer complete flexibility, implementing batch splitting and merging under high concurrency poses significant database challenges.
6.1 Database Schema for Batch Tracking
-- ============================================
-- BATCH MANAGEMENT SCHEMA (SQL Server)
-- ============================================
CREATE TABLE dbo.BatchHeader (
BatchID BIGINT IDENTITY(1,1) PRIMARY KEY,
BatchNumber NVARCHAR(50) NOT NULL UNIQUE,
ItemCode NVARCHAR(50) NOT NULL,
ProductionDate DATE NOT NULL,
ExpiryDate DATE NULL,
Status NVARCHAR(20) NOT NULL DEFAULT 'CREATED', -- 'CREATED', 'QC_PENDING', 'RELEASED', 'BLOCKED', 'EXPIRED'
CreatedBy NVARCHAR(50) NOT NULL DEFAULT SYSTEM_USER,
CreatedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
CONSTRAINT FK_BatchHeader_Item FOREIGN KEY (ItemCode) REFERENCES dbo.ItemMaster(ItemCode)
);
CREATE TABLE dbo.BatchCharacteristic (
BatchCharacteristicID BIGINT IDENTITY(1,1) PRIMARY KEY,
BatchID BIGINT NOT NULL,
CharacteristicName NVARCHAR(50) NOT NULL,
CharacteristicValue NVARCHAR(200) NOT NULL,
UOM NVARCHAR(20) NULL,
CONSTRAINT FK_BatchChar_Batch FOREIGN KEY (BatchID) REFERENCES dbo.BatchHeader(BatchID),
CONSTRAINT UQ_BatchChar UNIQUE (BatchID, CharacteristicName)
);
CREATE TABLE dbo.BatchGenealogy (
GenealogyID BIGINT IDENTITY(1,1) PRIMARY KEY,
ParentBatchID BIGINT NOT NULL,
ChildBatchID BIGINT NOT NULL,
QuantityUsed DECIMAL(18,3) NOT NULL,
QuantityProduced DECIMAL(18,3) NOT NULL,
TransactionDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
CONSTRAINT FK_BatchGenealogy_Parent FOREIGN KEY (ParentBatchID) REFERENCES dbo.BatchHeader(BatchID),
CONSTRAINT FK_BatchGenealogy_Child FOREIGN KEY (ChildBatchID) REFERENCES dbo.BatchHeader(BatchID),
CONSTRAINT UQ_ParentChild UNIQUE (ParentBatchID, ChildBatchID)
);
6.2 Batch Splitting – Implementation with Trigger
Splitting a batch occurs when you need to divide a parent batch into multiple smaller batches, often for different customers or storage conditions. The challenge is maintaining genealogy and characteristics.
-- ============================================
-- TRIGGER: Prevent batch split if insufficient quantity
-- ============================================
CREATE TRIGGER trg_BatchSplit_Validation
ON dbo.BatchGenealogy
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (
SELECT 1
FROM inserted i
JOIN dbo.BatchHeader parent ON i.ParentBatchID = parent.BatchID
JOIN dbo.BatchHeader child ON i.ChildBatchID = child.BatchID
WHERE parent.Status = 'BLOCKED'
OR child.Status = 'BLOCKED'
)
BEGIN
THROW 50001, 'Cannot split blocked batch', 1;
END;
-- Optionally check quantity balance here
END;
6.3 Batch Merging – Combining Batches with Different Potency
Merging batches is common when you need to combine leftovers or reprocess. If two batches have different potency, the merged batch's potency must be calculated as a weighted average. This requires careful SQL logic.
-- ============================================
-- STORED PROCEDURE: Merge two batches with potency calculation
-- ============================================
CREATE PROCEDURE dbo.sp_MergeBatches
@ParentBatch1 BIGINT,
@ParentBatch2 BIGINT,
@NewBatchNumber NVARCHAR(50),
@QuantityFrom1 DECIMAL(18,3),
@QuantityFrom2 DECIMAL(18,3)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
-- Get potency values from characteristics
DECLARE @Potency1 DECIMAL(18,4), @Potency2 DECIMAL(18,4);
SELECT @Potency1 = CAST(CharacteristicValue AS DECIMAL(18,4))
FROM dbo.BatchCharacteristic
WHERE BatchID = @ParentBatch1 AND CharacteristicName = 'POTENCY';
SELECT @Potency2 = CAST(CharacteristicValue AS DECIMAL(18,4))
FROM dbo.BatchCharacteristic
WHERE BatchID = @ParentBatch2 AND CharacteristicName = 'POTENCY';
-- Calculate weighted average potency
DECLARE @TotalQty DECIMAL(18,3) = @QuantityFrom1 + @QuantityFrom2;
DECLARE @MergedPotency DECIMAL(18,4) = (@Potency1 * @QuantityFrom1 + @Potency2 * @QuantityFrom2) / @TotalQty;
-- Insert new merged batch
INSERT INTO dbo.BatchHeader (BatchNumber, ItemCode, ProductionDate, ExpiryDate, Status)
SELECT @NewBatchNumber, ItemCode, MIN(ProductionDate), MIN(ExpiryDate), 'CREATED'
FROM dbo.BatchHeader
WHERE BatchID IN (@ParentBatch1, @ParentBatch2)
GROUP BY ItemCode;
DECLARE @NewBatchID BIGINT = SCOPE_IDENTITY();
-- Insert characteristics for merged batch
INSERT INTO dbo.BatchCharacteristic (BatchID, CharacteristicName, CharacteristicValue)
VALUES (@NewBatchID, 'POTENCY', CAST(@MergedPotency AS NVARCHAR(50)));
-- Insert genealogy links
INSERT INTO dbo.BatchGenealogy (ParentBatchID, ChildBatchID, QuantityUsed, QuantityProduced)
VALUES
(@ParentBatch1, @NewBatchID, @QuantityFrom1, @TotalQty),
(@ParentBatch2, @NewBatchID, @QuantityFrom2, @TotalQty);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
THROW;
END CATCH
END;
6.4 DBA Performance Nightmares & Solutions
| DBA Issue | Impact | Solution |
|---|---|---|
| Deadlocks during concurrent batch splits | Transaction failures, user frustration | Use row-level locking, minimize transaction duration, implement retry logic |
| Genealogy query performance | Slow traceability reports | Index BatchGenealogy on ParentBatchID and ChildBatchID; use recursive CTEs optimized with indexes |
| Index fragmentation on BatchHeader | Slow batch lookups | Schedule index maintenance; use filtered indexes for active batches |
| Large transaction log growth | Storage issues, slow recovery | Batch split/merge operations should be in minimal logging mode; consider partitioning |
| Concurrency control for batch numbers | Duplicate batch numbers | Use sequence or identity; enforce UNIQUE constraint |
BEGIN TRANSACTION and handle rollback carefully.
7. Common Operational Issues by Role: Developer, Architect, DBA, MIS, Designer
7.1 ERP Developers
- Missing batch characteristics inheritance – When a BOM component is consumed, the characteristic values (like supplier lot) may not propagate to the parent batch automatically. Developers must write logic to copy relevant characteristics.
- Potency adjustment bugs – Incorrect rounding or unit conversions in potency calculations can lead to off-spec products.
- UI for batch selection – Warehouse staff struggle to pick the correct batch from a long list without proper search/filter features.
7.2 ERP Architects
- Multi-level BOM genealogy complexity – For a Bangladeshi pharma company with 6-level BOMs (raw API → intermediate → bulk → final), the genealogy tree can become huge. Architects must design efficient data models.
- Integration with external systems – LIMS (Laboratory Information Management System) must feed QC results back to ERP batch records. Architects need to define API contracts.
- Regulatory compliance – Electronic batch records must be audit-ready. Architects must implement audit trails and electronic signatures.
7.3 DBAs
| DBA Issue | Impact | Solution |
|---|---|---|
| Slow genealogy recursive queries | Timeout in recall reports | Create indexed views or precomputed closure tables for genealogy |
| High write concurrency on batch tables | Lock contention | Use partitioning by ItemCode or date; implement optimistic concurrency |
| Data purging of expired batches | Table bloat | Archive old batches to separate tables while preserving genealogy |
7.4 MIS / Reporting Teams
- Conflicting batch reports – One report shows batch status as "Released", another shows "Under QC". Need a single source of truth.
- Expiry tracking dashboards – Need real-time visibility into batches nearing expiry across multiple warehouses.
- Regulatory submission reports – Must generate batch records in DGDA-required format with all genealogy details.
7.5 UI/UX Designers
- Visual distinction of batch status – Color-coding for Released (green), Under Test (yellow), Blocked (red), Expired (grey).
- Mobile batch scanning – Barcode/QR code scanning for warehouse operators; minimize manual entry.
- Alerts for expiry – Dashboard cards showing batches expiring in 30, 60, 90 days.
8. Real-World Bangladeshi Business Scenarios
8.1 Scenario 1: Pharmaceutical Company – Potency Adjustment Failure
Company: Medinova Pharmaceuticals, a leading manufacturer in Gazipur, producing antibiotic suspensions.
Situation: A batch of amoxicillin API (Batch #AMX-2026-078) had potency of 105% instead of the standard 100%. The production order for suspension required 10 kg of API at 100% potency. If the system used 10 kg of the 105% potency API, the final product would have 5% excess active ingredient, potentially harmful.
What should happen: The ERP should adjust the quantity to 9.52 kg (10 kg * 100/105) to achieve the correct active ingredient content.
The Chaos: Due to a configuration error in the custom SQL Server ERP, the potency adjustment formula was not triggered. The full 10 kg was used. The final batch had potency 105.2%, exceeding the allowed limit (95-105%). The entire batch of 5,000 bottles worth ৳12 lakh had to be destroyed.
Resolution: Implemented a stored procedure that validates potency before BOM consumption; if potency is outside tolerance, it automatically calculates adjusted quantity and blocks the production order until QA approval.
8.2 Scenario 2: Food Processing – Batch Splitting for Export and Local Market
Company: Golden Harvest Foods, producing packaged spices and snacks in Dhaka.
Situation: A batch of 1,000 kg of turmeric powder (Batch #TF-2609-012) needed to be split: 600 kg for export to the Middle East (requires higher quality certification) and 400 kg for local market. The ERP (Odoo) had to split the batch while maintaining traceability.
Odoo Implementation:
- Created two new lots:
TF-2609-012-EXPandTF-2609-012-LOC. - Used Odoo's "Split" function in the lot form, which automatically creates child lots and records the parent-child relationship.
- Assigned different quality certificates to the export lot.
Result: Full traceability maintained. When a quality issue arose with the export lot, the company could trace back to the original parent lot and identify the root cause in the drying process.
8.3 Scenario 3: Custom SQL Server – Merging Returned Batches
Company: A textile dye manufacturer in Narayanganj using a custom ERP on SQL Server.
Situation: Two partially used batches of reactive dye (Batch #RD-101 and #RD-102) were returned from customers. Both had similar color strength but different moisture content. The warehouse wanted to merge them into a single batch to save storage space.
Custom SQL Implementation:
- Used the
sp_MergeBatchesprocedure shown in Section 6. - Calculated weighted average moisture content and color strength.
- Created a new batch #RD-103 with combined quantity and updated genealogy.
Challenge: During the merge, a concurrent process tried to allocate batch #RD-101 to a sales order, causing a deadlock. The DBA implemented row-level locking and retry logic to resolve it.
9. Workflow Diagrams & Best Practices
9.1 Batch Production & QC Release Workflow
9.2 Batch Recall Workflow (Reverse Traceability)
9.3 Best Practices Checklist for Bangladeshi Manufacturers
- Enable batch tracking at the item master level for all regulated materials.
- Define batch characteristics early and consistently (potency, assay, moisture, expiry).
- Automate potency adjustment in BOM consumption – never rely on manual calculation.
- Use FEFO logic for all shelf-life items in warehouse operations.
- Implement QC integration – batch status must update automatically when LIMS results are uploaded.
- Maintain genealogy closure table for fast recursive queries (SQL Server) or enable SAP where-used list.
- Train warehouse staff on batch scanning and lot selection – avoid manual entry errors.
- Regularly test recall readiness – conduct mock recalls to ensure traceability works.
- Secure batch records with audit trails and electronic signatures for DGDA compliance.
- Monitor batch performance – track expiry rates, rework rates, and QC rejections by batch.
10. AI & Future Trends in Batch Management
The next frontier in batch management is the integration of artificial intelligence and machine learning to predict quality issues, optimize batch scheduling, and automate traceability.
10.1 AI Applications in Batch Tracking
| AI Capability | Batch Management Application | Business Impact (Bangladesh) |
|---|---|---|
| Predictive Quality Analytics | ML models predict batch quality based on raw material characteristics, equipment parameters, and environmental data | Reduces QC rejections by 30% in pharma |
| Potency Optimization | AI suggests optimal blend of raw material lots to achieve target potency | Saves raw material costs by 5-8% |
| Expiry Prediction | Models forecast actual shelf-life under storage conditions, enabling dynamic expiry dates | Reduces waste from premature expiry by 20% |
| Automated Batch Classification | NLP and image recognition classify batch records from handwritten logs | Digitizes legacy records for DGDA compliance |
| Recall Simulation | AI simulates recall scenarios to identify weak points in genealogy data | Improves recall readiness |
10.2 Machine Learning for Batch Traceability Enhancement
One innovative application is using ML to automatically detect missing genealogy links. By analyzing transaction patterns and material flows, AI can flag production orders where batch consumption was not properly recorded, prompting correction before a recall occurs.
-- Feature extraction for ML model to detect missing genealogy links
SELECT
po.production_order_id,
COUNT(DISTINCT input.batch_id) AS input_batch_count,
COUNT(DISTINCT output.batch_id) AS output_batch_count,
SUM(CASE WHEN input.batch_id IS NULL THEN 1 ELSE 0 END) AS missing_input_links,
SUM(CASE WHEN output.batch_id IS NULL THEN 1 ELSE 0 END) AS missing_output_links
FROM production_order po
LEFT JOIN material_transaction input ON po.order_id = input.order_id AND input.direction = 'IN'
LEFT JOIN material_transaction output ON po.order_id = output.order_id AND output.direction = 'OUT'
GROUP BY po.production_order_id;
11. Frequently Asked Questions & Expert Answers
These questions are sourced from real-world ERP implementations in Bangladesh, covering common challenges in batch management.
0 Comments
thanks for your comments!