# 06 — Integrations & Consolidated Data Model (Technical Digest)

> Lossless English digest of `files/manufacturing_spec/markdown/06_integrations_and_data_model.md`.
> An implementer must not need the original file. Governing principles inherited from spec file `00`
> (genericity mandate, frozen snapshots, state machines, many-to-many demand↔supply, multi-level
> recursion, config-over-code, real-time GL postings, resource abstraction).

---

## 6.1 Integration Map — "Manufacturing is NOT an island"

The Manufacturing module integrates with modules that already exist in the host ERP. It must NOT
rebuild them. Out-of-scope (integrate, do not rebuild): General Ledger, Inventory/Warehouse core,
Procurement core, Sales orders, HR/payroll.

ASCII map:
```
                    ┌──────────────┐
       Sales Order →│              │→ Procurement (Planned POs from MRP)
                    │ MANUFACTURING │
        Inventory  ↔│   MODULE      │↔ General Ledger (all postings)
   (RM, WIP, FG)    │              │
                    └──────────────┘
                       ↕ HR (labor rates / piece-rate payroll)
```

### Integration points (by direction)

| External module | Direction | What flows |
|---|---|---|
| **Sales** | → Mfg | Customer orders trigger MTO/Toll production; promised dates returned to Sales |
| **Procurement** | ← Mfg | MRP emits Planned Purchase Orders; receipts update material availability |
| **Inventory** | ↔ Mfg | Material reservations; RM→WIP→FG movements; batch/lot; consignment (customer-owned) stock |
| **General Ledger** | ← Mfg | Every issue/confirmation/GR posts journal entries (WIP, applied labor/machine/OH, variances) |
| **HR** | → Mfg | Labor rates supplied to Mfg; piece-rate quantities feed back to payroll |

### Key integration rules (verbatim intent)

1. **Consignment (toll):** customer-owned material lives in a **separate inventory bucket**
   (`ownership = Customer`). It is **never valued as own asset**, and **never triggers a purchase**.
2. **GL postings are real-time** — woven into execution events, NOT a nightly batch.
3. **Reservations** in Inventory are created at order **Release**, and consumed at **Material Issue**.

---

## 6.2 Consolidated Relational Data Model

Every entity in the module with keys and relationships. Types are indicative; implement per host ERP
conventions. Grouped by layer. `PK` = primary key, `FK→X` = foreign key to entity X.

### MASTER DATA

**Item** *(host ERP, referenced — NOT owned by Mfg)*
- `item_id` PK, `name`, `uom`, `procurement_type`, `material_ownership`, `costing_method`,
  `lead_time_days`, `safety_stock`, … (extensible)

**BOM_Header**
- `bom_id` PK, `parent_item_id` FK→Item, `bom_type`, `version`, `base_quantity`, `uom`, `status`,
  `effective_from`, `effective_to`, `created_by`, `approved_by`

**BOM_Line** *(BOM_Header 1—* BOM_Line)*
- `bom_id` FK→BOM_Header, `line_no`, `component_id` FK→Item, `quantity`, `uom`,
  `scrap_percentage`, `issue_type`, `issue_level`, `operation_seq`, `substitute_items`

**Routing_Header**
- `routing_id` PK, `item_id` FK→Item, `routing_type`, `version`, `lot_size_from`, `lot_size_to`,
  `status`, `effective_from`, `effective_to`, `total_lead_time`

**Routing_Operation** *(Routing_Header 1—* Routing_Operation)*
- `routing_id` FK→Routing_Header, `operation_no`, `description`, `work_center_id` FK→Work_Center,
  `tool_id` FK→Tool, `setup_time`, `run_time_per_unit`, `cavity_count`, `cycle_time`,
  `queue_time`, `move_time`, `inspection_required`, `critical_operation`, `required_skill_code`

**Work_Center**
- `wc_id` PK, `description`, `category`, `plant_location`, `cost_center_id` FK→Cost_Center,
  `capacity_unit`, `daily_capacity`, `capacity_multiplier`, `efficiency_percent`,
  `utilization_percent`, `calendar_id`, `setup_cost_rate`, `labor_cost_rate`,
  `machine_cost_rate`, `overhead_rate`, `labor_calc`, `bottleneck_flag`

**Tool** *(a Resource with resource_type = Tool)*
- `tool_id` PK, `description`, `product_id` FK→Item, `cavity_count`, `setup_time`, `status`,
  `life_cycles`, `cycles_used`, `purchase_cost`

### PLANNING

**MPS_Header**
- `id` PK, `plan_name`, `plant_id`, `time_bucket`, `start_date`, `end_date`, `frozen_fence`,
  `slushy_fence`, `status`

**MPS_Line**
- `mps_id` FK→MPS_Header, `product_id` FK→Item, `period_date`, `forecast_qty`,
  `confirmed_orders_qty`, `planned_production`, `projected_balance`, `safety_stock_target`,
  `available_to_promise`

**Item_MRP_Settings**
- `item_id` FK→Item, `mrp_type`, `procurement_type`, `material_ownership`, `lot_sizing_rule`,
  `lot_size`, `min_lot`, `max_lot`, `safety_stock`, `lead_time_days`, `reorder_point`

**MRP_Planned_Order** *(transient — regenerated each MRP run)*
- `item_id` FK→Item, `order_type`, `quantity`, `required_date`, `release_date`,
  `source_demand_id`, `mrp_run_id`

**CRP_Load** *(output of capacity check)*
- `resource_id`, `resource_type`, `period`, `required_load`, `available_capacity`,
  `utilization_pct`, `status`

### EXECUTION

**Production_Order_Header**
- `order_no` PK, `order_type`, `product_id` FK→Item, `quantity`, `production_type`,
  `material_ownership`, `start_date`, `finish_date`, `status`, `bom_snapshot_id`,
  `routing_snapshot_id`, `tool_id` FK→Tool, `wip_account`

**Order_Component**
- `order_no` FK→PO_Header, `component_id` FK→Item, `required_quantity`, `issued_quantity`,
  `reserved_quantity`, `operation_seq`

**Order_Operation**
- `order_no` FK→PO_Header, `operation_no`, `work_center_id` FK→Work_Center, `tool_id` FK→Tool,
  `planned_setup_time`, `planned_run_time`, `actual_setup_time`, `actual_run_time`, `status`,
  `actual_start_time`, `actual_end_time`, `confirmed_by`, `confirmed_quantity`, `scrap_quantity`
- `status ∈ {Waiting, Ready, InProgress, Completed}` — driven by the SFC layer

**Material_Issue_Header**
- `issue_no` PK, `issue_date`, `issue_type`, `issue_level`, `order_no` FK→PO_Header,
  `operation_no`, `issuer_id`, `warehouse_from`, `status`

**Material_Issue_Line**
- `issue_no` FK→MI_Header, `material_code` FK→Item, `quantity`, `batch_no`, `cost_price`,
  `total_cost`, `bin_location`, `ownership`

**Confirmation_Header**
- `confirmation_no` PK, `order_no` FK→PO_Header, `operation_no`, `confirmation_type`,
  `operator_id`, `work_center_id`, `shift`, `timestamp`

**Confirmation_Yield** *(grade distribution — yield is a distribution, not a scalar)*
- `confirmation_no` FK→Conf_Header, `grade_code`, `quantity`, `unit_sale_price`

**Confirmation_Detail**
- `confirmation_no` FK→Conf_Header, `scrap_quantity`, `rework_quantity`, `setup_time_actual`,
  `run_time_actual`, `labor_hours`, `machine_hours`, `reason_code`

**Goods_Receipt_Header**
- `gr_no` PK, `gr_date`, `order_no` FK→PO_Header, `receipt_type`, `warehouse_to`,
  `receiver_id`, `status`

**Goods_Receipt_Line** *(per grade)*
- `gr_no` FK→GR_Header, `item_id` FK→Item, `received_quantity`, `grade_code`, `batch_no`,
  `bin_location`, `cost_per_unit`, `quality_status`

### COSTING

**Standard_Cost**
- `item_id` FK→Item, `std_material_cost`, `std_labor_cost`, `std_overhead_cost`,
  `std_total_cost`, `last_updated`, `update_frequency`

**Cost_Center**
- `cc_id` PK, `type`, `parent_cc` FK→Cost_Center *(recursive hierarchy)*, `manager_id`,
  `budget_amount`, `actual_amount`, `status`

**Variance_Record**
- `order_no` FK→PO_Header, `variance_type`, `amount`, `percentage`, `classification`,
  `owner`, `investigation_flag`

### CROSS-CUTTING

**Pegging** *(Many-to-Many demand↔supply)*
- `production_order_id` FK→PO_Header, `sales_order_id` FK→SalesOrder, `allocated_quantity`

**Delivery_Schedule** *(order splitting)*
- `schedule_id` PK, `parent_order_id` FK→SalesOrder, `quantity`, `scheduled_date`, `status`,
  `linked_production_order` FK→PO_Header

---

## 6.3 Relationship Summary (key cardinalities)

```
Item 1—* BOM_Header 1—* BOM_Line *—1 Item (component)   [recursive multi-level]
Item 1—* Routing_Header 1—* Routing_Operation *—1 Work_Center
Routing_Operation *—1 Tool
Work_Center *—1 Cost_Center
Sales_Order *—* Production_Order   (via Pegging)
Sales_Order 1—* Delivery_Schedule
Production_Order 1—* Order_Component / Order_Operation
Production_Order 1—* Material_Issue 1—* Material_Issue_Line
Production_Order 1—* Confirmation 1—* Confirmation_Yield   (grades)
Production_Order 1—* Goods_Receipt 1—* Goods_Receipt_Line  (per grade)
Production_Order 1—* Variance_Record
```

---

## 6.4 Build Order (recommended for the AI builder — each step depends on the prior)

1. Master Data entities + CRUD (BOM recursive, Routing, WC, Tool)
2. Item_MRP_Settings + Standard_Cost
3. MPS (forecast consumption, time fences, CTP)
4. MRP (recursive explosion, lot sizing, toll branch)
5. CRP (resource intersection, overload detection)
6. Production Order (snapshot, state machine, order network)
7. Material Issue (3 types, 3 levels, consignment)
8. Confirmation (grade distribution, piece-rate, postings)
9. Goods Receipt (multi-grade, variance gate, toll branch)
10. Costing (methods, elements, cost centers, 7 variances)
11. SFC (operation-status real-time, terminal UI, auto-advance, dashboard)
12. Integrations (Sales, Procurement, Inventory, GL, HR)

Honour the genericity mandate (file 00 §0.2) throughout.

---

## 6.5 Future Phases (identified, NOT specified in this build)

| Component | Interface to current build |
|---|---|
| APS (detailed scheduling) | consumes Planned Orders + Resource calendars → time-phased schedule for SFC |
| Quality Management | hooks Confirmation grades/reason_codes; inspection gates on operations |
| Tool/Mold Management | extends Tool entity: lifecycle, maintenance, cycle tracking, replacement alerts |
| Maintenance (PM) | reduces Work_Center capacity during downtime windows |
| OEE / Dashboards | consumes Order_Operation actual times + scrap |
| PLM / ECO | governs BOM/Routing change orders (formalizes the Snapshot concept) |

---

## Enums referenced by this data model (defined in spec file 00 §0.5)

```
production_type     ∈ { MakeToStock, MakeToOrder, AssembleToOrder, TollManufacturing }
material_ownership  ∈ { Own, Customer }
procurement_type    ∈ { Buy, Make }
costing_method      ∈ { Standard, Actual, Average, FIFO }
labor_calc          ∈ { Hourly, PieceRate }
issue_type          ∈ { Manual, Backflush, AutoIssue }
issue_level         ∈ { PerOrder, PerShift, PerOperation }
resource_type       ∈ { Machine, Tool, Labor }
lot_sizing_rule     ∈ { Exact, Fixed, MinMax, EOQ, PeriodOrder }
order_status        ∈ { Planned, Released, InProcess, Completed, Closed }
operation_status    ∈ { Waiting, Ready, InProgress, Completed }
```

---

## "Expects from ERP" — explicit contract this spec part demands from the host

| # | Host module | Contract the Manufacturing module requires |
|---|---|---|
| 1 | **Item master** (Core/Inventory) | `Item` with `uom`, `procurement_type`, `material_ownership`, `costing_method`, `lead_time_days`, `safety_stock`. Mfg references it; never owns it. |
| 2 | **Inventory** | A `Material reservation` API (create at Release, consume at Issue). |
| 3 | **Inventory** | A **separate ownership bucket** so `ownership = Customer` (consignment) stock is held but NOT valued and NOT purchasable. |
| 4 | **Inventory** | `batch_no` / lot and `bin_location` tracking on movements. |
| 5 | **Inventory** | RM→WIP→FG stock movements driven by Issue / Confirmation / GR. |
| 6 | **Procurement** | Ability to consume MRP `Planned Purchase Orders` and feed receipts back to availability. |
| 7 | **Sales** | Sales Orders as a demand source; ability to return promised/ATP dates; a `SalesOrder` entity for Pegging and Delivery_Schedule FKs. |
| 8 | **General Ledger** | **Real-time** journal posting on every issue/confirmation/GR: WIP debit/credit, applied labor / machine / overhead, and variance accounts. `wip_account` per order. |
| 9 | **HR / Payroll** | Supply labor cost rates to Mfg; receive piece-rate confirmed quantities back for payroll. |
| 10 | **Cost Center** master | A (recursive) `Cost_Center` master that Work_Centers map to (may be owned by GL/Accounting or by Mfg — spec leaves owner ambiguous). |
| 11 | **Calendar** | A `calendar_id` resource calendar driving Work_Center available capacity (consumed by CRP / future APS). |