Lot Tracking
Track inventory by lot numbers for quality control, compliance, and traceability.
What is Lot Tracking?
LOT TRACKING CONCEPT
═══════════════════════════════════════════════════════════════════════════════
┌─────────────────────────────────────────────────────────────────────────────┐
│ LOT NUMBERED INVENTORY │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ Same Item, Different Lots: │
│ │
│ ┌─────────────────┐ ┌─────────────────┐ ┌─────────────────┐ │
│ │ Widget A │ │ Widget A │ │ Widget A │ │
│ │ │ │ │ │ │ │
│ │ Lot: LOT-2024-01│ │ Lot: LOT-2024-02│ │ Lot: LOT-2024-03│ │
│ │ Qty: 100 │ │ Qty: 150 │ │ Qty: 75 │ │
│ │ Mfg: 01/15/2024 │ │ Mfg: 02/01/2024 │ │ Mfg: 02/20/2024 │ │
│ │ Exp: 01/15/2025 │ │ Exp: 02/01/2025 │ │ Exp: 02/20/2025 │ │
│ └─────────────────┘ └─────────────────┘ └─────────────────┘ │
│ │
│ Each lot is tracked separately with its own: │
│ • Quantity │
│ • Manufacture date │
│ • Expiration date │
│ • Cost (if lot costing enabled) │
│ • Location/Bin │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
When to Use Lot Tracking
| Industry | Use Case |
|---|---|
| Food & Beverage | Expiration dates, FDA recalls, FIFO/FEFO |
| Pharmaceuticals | Batch tracking, expiry, regulatory compliance |
| Chemicals | Batch certification, hazmat tracking |
| Electronics | Date codes, component batches |
| Manufacturing | Raw material batch tracing, quality control |
| Medical Devices | Lot traceability, recall management |
LOT TRACKING BENEFITS
═══════════════════════════════════════════════════════════════════════════════
Traceability:
┌─────────────────────────────────────────────────────────────────────────────┐
│ • Track lot from receipt through sale to customer │
│ • Know which customers received specific lots │
│ • Trace quality issues to specific batches │
└─────────────────────────────────────────────────────────────────────────────┘
Compliance:
┌─────────────────────────────────────────────────────────────────────────────┐
│ • FDA, USDA, EPA requirements │
│ • GMP (Good Manufacturing Practice) compliance │
│ • ISO certification requirements │
└─────────────────────────────────────────────────────────────────────────────┘
Quality Control:
┌─────────────────────────────────────────────────────────────────────────────┐
│ • Isolate defective batches │
│ • Targeted recalls by lot number │
│ • Link quality test results to lots │
└─────────────────────────────────────────────────────────────────────────────┘
Inventory Management:
┌─────────────────────────────────────────────────────────────────────────────┐
│ • FEFO (First Expired, First Out) picking │
│ • Expiration date visibility │
│ • Lot-specific costing │
└─────────────────────────────────────────────────────────────────────────────┘
Setup Lot Tracked Items
Enable Lot Tracking
Navigation: Item Record → Inventory subtab
LOT ITEM SETUP
═══════════════════════════════════════════════════════════════════════════════
Item Record Configuration:
┌─────────────────────────────────────────────────────────────────────────────┐
│ Item ID: WIDGET-A │
│ Display Name: Widget Type A │
│ Item Type: Inventory Item │
│ │
│ Inventory Tab: │
│ ┌───────────────────────────────────────────────────────────────────────┐ │
│ │ ☑ Lot Numbered Item │ │
│ │ │ │
│ │ Lot Numbering: │ │
│ │ ○ On Receipt (assign lot when receiving) │ │
│ │ ● Auto-Generate (system generates lot numbers) │ │
│ │ │ │
│ │ ☑ Track Expiration Date │ │
│ │ ☐ Track Manufacturing Date │ │
│ │ │ │
│ │ Default Expiration Days: 365 │ │
│ └───────────────────────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────────────────────┘
⚠️ Note: Cannot change item from non-lot to lot-tracked if inventory exists
Lot Numbering Options
| Option | Description | Best For |
|---|---|---|
| On Receipt | Manually enter lot number during receiving | Supplier lot numbers |
| Auto-Generate | System creates lot number automatically | Internal manufacturing |
| On Order | Assign lot when order is created | Work order tracking |
Lot Transactions
Receiving with Lots
ITEM RECEIPT WITH LOT ASSIGNMENT
═══════════════════════════════════════════════════════════════════════════════
┌─────────────────────────────────────────────────────────────────────────────┐
│ Purchase Order: PO-1234 │
│ Item: Widget A (Lot Tracked) │
│ Quantity: 200 │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ Item Receipt - Inventory Detail: │
│ ┌───────────────────────────────────────────────────────────────────────┐ │
│ │ Lot Number │ Quantity │ Expiration Date │ Bin │ Status │ │
│ ├────────────────┼──────────┼─────────────────┼──────────┼──────────────┤ │
│ │ LOT-2024-0145 │ 100 │ 12/25/2025 │ A-01-02 │ Available │ │
│ │ LOT-2024-0146 │ 100 │ 12/25/2025 │ A-01-03 │ Available │ │
│ └────────────────┴──────────┴─────────────────┴──────────┴──────────────┘ │
│ │
│ Received from Vendor: 2 lots, same expiration (from same production batch) │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
Fulfillment with Lots
ITEM FULFILLMENT - LOT SELECTION
═══════════════════════════════════════════════════════════════════════════════
Sales Order: SO-5678
Item: Widget A
Quantity Ordered: 75
Available Lots:
┌─────────────────────────────────────────────────────────────────────────────┐
│ Lot Number │ Available │ Expiration │ FEFO Pick │
├────────────────┼───────────┼────────────┼──────────────────────────────────┤
│ LOT-2024-0130 │ 50 │ 11/15/2025 │ ← Pick first (expires soonest) │
│ LOT-2024-0145 │ 100 │ 12/25/2025 │ ← Pick second (need 25 more) │
│ LOT-2024-0146 │ 100 │ 12/25/2025 │ │
└────────────────┴───────────┴────────────┴──────────────────────────────────┘
Fulfillment Lot Assignment:
┌─────────────────────────────────────────────────────────────────────────────┐
│ Lot Number │ Quantity Fulfilled │ Remaining in Lot │
├────────────────┼────────────────────┼──────────────────────────────────────┤
│ LOT-2024-0130 │ 50 │ 0 (depleted) │
│ LOT-2024-0145 │ 25 │ 75 │
└────────────────┴────────────────────┴──────────────────────────────────────┘
Lot Inventory Adjustment
LOT ADJUSTMENT EXAMPLE
═══════════════════════════════════════════════════════════════════════════════
Scenario: Quality issue found in LOT-2024-0130
Adjustment Record:
┌─────────────────────────────────────────────────────────────────────────────┐
│ Type: Inventory Adjustment │
│ Item: Widget A │
│ Location: Main Warehouse │
│ │
│ Inventory Detail: │
│ ┌───────────────────────────────────────────────────────────────────────┐ │
│ │ Lot Number │ Current Qty │ Adjust Qty │ New Qty │ Reason │ │
│ ├────────────────┼─────────────┼────────────┼─────────┼─────────────────┤ │
│ │ LOT-2024-0130 │ 50 │ -50 │ 0 │ Quality hold │ │
│ └────────────────┴─────────────┴────────────┴─────────┴─────────────────┘ │
│ │
│ GL Impact: │
│ DR: Inventory Loss/Write-off $X,XXX │
│ CR: Inventory Asset $X,XXX │
└─────────────────────────────────────────────────────────────────────────────┘
Lot Traceability
Forward Tracing (Lot → Customers)
Find all customers who received a specific lot.
-- Lot Forward Trace: Who received this lot?
SELECT DISTINCT
t.tranid AS fulfillment,
t.trandate AS ship_date,
c.companyname AS customer,
c.email AS customer_email,
tl.quantity AS qty_shipped
FROM
transactionline tl
JOIN
transaction t ON tl.transaction = t.id
JOIN
customer c ON t.entity = c.id
JOIN
inventoryassignment ia ON tl.id = ia.transactionline
WHERE
t.type = 'ItemShip'
AND ia.receiptinventorynumber = 'LOT-2024-0130'
ORDER BY
t.trandate
Backward Tracing (Customer → Lots)
Find all lots shipped to a specific customer.
-- Lot Backward Trace: What lots did customer receive?
SELECT
t.tranid AS fulfillment,
t.trandate AS ship_date,
i.itemid AS item,
ia.receiptinventorynumber AS lot_number,
tl.quantity AS quantity
FROM
transactionline tl
JOIN
transaction t ON tl.transaction = t.id
JOIN
item i ON tl.item = i.id
JOIN
inventoryassignment ia ON tl.id = ia.transactionline
WHERE
t.type = 'ItemShip'
AND t.entity = 12345 -- Customer internal ID
ORDER BY
t.trandate DESC
Expiration Management
EXPIRATION DATE TRACKING
═══════════════════════════════════════════════════════════════════════════════
Expiration Dashboard:
┌─────────────────────────────────────────────────────────────────────────────┐
│ │
│ EXPIRED (Action Required!) │
│ ┌───────────────────────────────────────────────────────────────────────┐ │
│ │ Lot LOT-2024-0089 │ Widget A │ Expired: 12/01/2024 │ Qty: 25 │ │
│ └───────────────────────────────────────────────────────────────────────┘ │
│ │
│ EXPIRING WITHIN 30 DAYS │
│ ┌───────────────────────────────────────────────────────────────────────┐ │
│ │ Lot LOT-2024-0102 │ Widget B │ Expires: 01/15/2025 │ Qty: 150 │ │
│ │ Lot LOT-2024-0105 │ Widget C │ Expires: 01/20/2025 │ Qty: 75 │ │
│ └───────────────────────────────────────────────────────────────────────┘ │
│ │
│ EXPIRING WITHIN 90 DAYS │
│ ┌───────────────────────────────────────────────────────────────────────┐ │
│ │ Lot LOT-2024-0130 │ Widget A │ Expires: 02/28/2025 │ Qty: 200 │ │
│ │ Lot LOT-2024-0145 │ Widget A │ Expires: 03/15/2025 │ Qty: 100 │ │
│ └───────────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
Expiration Query
-- Lots expiring within N days
SELECT
inv.inventorynumber AS lot_number,
i.itemid AS item,
i.displayname AS item_name,
inv.expirationdate,
TRUNC(inv.expirationdate) - TRUNC(SYSDATE) AS days_until_expiry,
ib.quantityonhand AS quantity,
l.name AS location
FROM
inventorynumber inv
JOIN
item i ON inv.item = i.id
JOIN
inventorynumberlocations ib ON inv.id = ib.inventorynumber
JOIN
location l ON ib.location = l.id
WHERE
ib.quantityonhand > 0
AND inv.expirationdate <= SYSDATE + 30
ORDER BY
inv.expirationdate,
i.itemid
Best Practices
LOT TRACKING BEST PRACTICES
═══════════════════════════════════════════════════════════════════════════════
Lot Number Format:
┌─────────────────────────────────────────────────────────────────────────────┐
│ Include meaningful information in lot numbers: │
│ • Date component: YYYYMMDD or YYWW (year-week) │
│ • Sequence: 001, 002, etc. │
│ • Supplier code (if applicable) │
│ │
│ Examples: │
│ • 20241225-001 (Date-Sequence) │
│ • WK2452-ACME-01 (Week-Vendor-Sequence) │
│ • LOT-2024-0001 (Simple sequential) │
└─────────────────────────────────────────────────────────────────────────────┘
Receiving:
┌─────────────────────────────────────────────────────────────────────────────┐
│ ✓ Record supplier lot number on receipt │
│ ✓ Enter expiration date at time of receipt │
│ ✓ Verify lot quantity matches packing slip │
└─────────────────────────────────────────────────────────────────────────────┘
Fulfillment:
┌─────────────────────────────────────────────────────────────────────────────┐
│ ✓ Use FEFO for perishable items │
│ ✓ Never ship expired lots │
│ ✓ Document lot numbers on shipping documents │
└─────────────────────────────────────────────────────────────────────────────┘
Compliance:
┌─────────────────────────────────────────────────────────────────────────────┐
│ ✓ Retain lot records per regulatory requirements │
│ ✓ Test recall procedures periodically │
│ ✓ Link quality documents to lot numbers │
└─────────────────────────────────────────────────────────────────────────────┘
Next Steps
| Goal | Go To |
|---|---|
| Lot transaction details | Lot Transactions → |
| Serial number tracking | Serial Tracking → |
| Bin management | Bin Management → |