Production and Traceability

Views for production orders, materials, SKUs, material types, and traceability checks.

These views cover production planning and traceability in Maecos. Production orders track planned and actual production runs. Materials and material types define what is being produced. Traceability checks verify that the correct materials are staged and used on the right equipment.

---

reporting.production_orders

Production order records with planned vs. actual quantities and timing.

Field	Type	Description
production_order_number	text	Production order identifier (e.g., SAP order number)
production_line	text	Production line name (from labels)
planned_start	timestamp	Planned start time
planned_end	timestamp	Planned end time
actual_start	timestamp	Actual start time
actual_end	timestamp	Actual end time
status	text	Order status
target_quantity	numeric	Planned production quantity
actual_quantity	numeric	Actual production quantity
sku	text	Stock Keeping Unit code
sku_name	text	SKU description
ean	text	European Article Number (barcode)
dlc	text	Date Limit of Consumption (shelf life date)
created_at	timestamp	When the order was created
created_by	uuid	User who created the order
updated_at	timestamp	Last modification timestamp
updated_by	uuid	User who last modified the order

Joins: Join with production_order_items on production_order_number for the bill of materials. Join with traca on production_order_number for traceability checks.

---

reporting.production_order_items

Bill of materials for production orders — which items are needed for each order.

Field	Type	Description
production_order_number	text	The production order
item_id	text	Material/item identifier
item_name	text	Material/item name
quantity	numeric	Required quantity

Joins: Join with production_orders on production_order_number. Join with materials on item_id = sap_code.

---

reporting.materials

Material master data (BOM items).

Field	Type	Description
sap_code	text	Material identifier (item ID)
name	text	Material name

Joins: Join with materials_material_types on sap_code = material_id for type classification.

---

reporting.materials_material_types

Maps materials to their material types.

Field	Type	Description
material_id	text	The material (SAP code)
material_type_id	uuid	The material type

Joins: Join with material_types on material_type_id.

---

reporting.material_types

Material type definitions used for classification and equipment compatibility.

Field	Type	Description
material_type_id	uuid	Unique identifier
name	text	Material type name
created_at	timestamp	When the record was created
created_by	uuid	User who created the record
updated_at	timestamp	Last modification timestamp
updated_by	uuid	User who last modified the record
deleted_at	timestamp	Soft delete timestamp
deleted_by	uuid	User who deleted the record

Joins: Join with equipment_material_types on material_type_id to find compatible equipment.

---

reporting.skus

SKU master data.

Field	Type	Description
sku_uuid	uuid	Unique identifier
sku	text	SKU code

---

reporting.traca

Traceability checks — verifications that the correct materials are staged and used at the right equipment slot during production.

Field	Type	Description
traca_id	uuid	Unique identifier (from the check)
equipment_id	uuid	Equipment where the check was performed
equipment_slot_number	integer	Slot number on the equipment
staged_at	timestamp	When the material was staged
active_from	timestamp	When the material started being used
material_code	text	Material identifier
production_order_number	text	Associated production order
conform	boolean	Whether the traceability check passed
ocr	text	OCR scan result (if scanned)
updated_at	timestamp	Last modification timestamp
deleted_at	timestamp	Soft delete timestamp

This view already filters out deleted records.

Non-conformances (conform = false) indicate a wrong material was staged or used. These are critical quality events and should be monitored closely.

Joins: Join with equipment on equipment_id. Join with production_orders on production_order_number. Join with materials on material_code = sap_code.

---

Example queries

Production order performance — planned vs. actual

SELECT
    production_order_number,
    sku_name,
    production_line,
    target_quantity,
    actual_quantity,
    ROUND(100.0 * actual_quantity / NULLIF(target_quantity, 0), 1) AS yield_pct,
    actual_end - actual_start AS actual_duration
FROM reporting.production_orders
WHERE actual_end IS NOT NULL
ORDER BY actual_end DESC
LIMIT 50;

Traceability non-conformances by equipment

SELECT
    e.name AS equipment,
    COUNT(*) AS total_checks,
    COUNT(*) FILTER (WHERE t.conform = false) AS non_conform,
    ROUND(100.0 * COUNT(*) FILTER (WHERE t.conform = false) / COUNT(*), 1) AS non_conform_pct
FROM reporting.traca t
JOIN reporting.equipment e USING (equipment_id)
WHERE t.staged_at >= NOW() - INTERVAL '30 days'
GROUP BY e.name
ORDER BY non_conform_pct DESC;