dodil/erp-brick-manufacturing
DODIL ERP brick: manufacturing
src/brick_manufacturing/migrations/00002_slice_b/up.sql
-- 00002_slice_b: work centres and routings, confirmations and scrap, MRP-lite, cost roll-ups, the AI audit, the Odoo slice;
-- and four columns on the order (routing, plan reference, stale note). Additive only.
CREATE TABLE IF NOT EXISTS mfg_ai_finding (
production_order_id VARCHAR NOT NULL,
finding_no INTEGER NOT NULL,
step_key VARCHAR,
model VARCHAR,
inputs_digest VARCHAR,
finding_json VARCHAR,
decision VARCHAR,
requested_by VARCHAR,
requested_sub VARCHAR,
decided_by VARCHAR,
decided_sub VARCHAR,
decided_at TIMESTAMP,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (production_order_id, finding_no)
);
CREATE TABLE IF NOT EXISTS mfg_confirmation (
production_order_id VARCHAR NOT NULL,
op_no INTEGER NOT NULL,
confirmation_no INTEGER NOT NULL,
work_centre_id VARCHAR,
machine_minutes NUMERIC(19,4),
labour_minutes NUMERIC(19,4),
is_final BOOLEAN,
cost NUMERIC(18,2),
journal_id VARCHAR,
reverses_confirmation_no INTEGER,
recorded_by VARCHAR,
recorded_sub VARCHAR,
business_date DATE,
status VARCHAR,
outbox_ref VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (production_order_id, op_no, confirmation_no)
);
CREATE TABLE IF NOT EXISTS mfg_cost_rollup (
rollup_id VARCHAR NOT NULL,
company_code VARCHAR,
effective_on DATE,
status VARCHAR,
ran_by VARCHAR,
ran_sub VARCHAR,
approved_by VARCHAR,
approved_sub VARCHAR,
released_on DATE,
stale_cause VARCHAR,
used_json VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (rollup_id)
);
CREATE TABLE IF NOT EXISTS mfg_cost_rollup_line (
rollup_id VARCHAR NOT NULL,
product_id VARCHAR NOT NULL,
bom_id VARCHAR,
bom_version INTEGER,
base_unit_code VARCHAR,
material NUMERIC(19,4),
conversion NUMERIC(19,4),
by_product_credit NUMERIC(19,4),
total NUMERIC(19,4),
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (rollup_id, product_id)
);
CREATE TABLE IF NOT EXISTS mfg_dependent_requirement (
warehouse_id VARCHAR NOT NULL,
product_id VARCHAR NOT NULL,
need_date DATE NOT NULL,
source_ref VARCHAR NOT NULL,
quantity NUMERIC(19,4),
unit_code VARCHAR,
source_kind VARCHAR,
run_no INTEGER,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (warehouse_id, product_id, need_date, source_ref)
);
CREATE TABLE IF NOT EXISTS mfg_forecast (
warehouse_id VARCHAR NOT NULL,
product_id VARCHAR NOT NULL,
week_start DATE NOT NULL,
quantity NUMERIC(19,4),
unit_code VARCHAR,
entered_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (warehouse_id, product_id, week_start)
);
CREATE TABLE IF NOT EXISTS mfg_migration_map (
odoo_db VARCHAR NOT NULL,
odoo_model VARCHAR NOT NULL,
odoo_id VARCHAR NOT NULL,
our_key VARCHAR,
run_id VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (odoo_db, odoo_model, odoo_id)
);
CREATE TABLE IF NOT EXISTS mfg_migration_run (
run_id VARCHAR NOT NULL,
odoo_db VARCHAR,
company_code VARCHAR,
cutover_date DATE,
status VARCHAR,
doors_json VARCHAR,
checks_json VARCHAR,
summary_json VARCHAR,
loaded_by VARCHAR,
signed_by VARCHAR,
signed_sub VARCHAR,
signed_on DATE,
discard_reason VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (run_id)
);
CREATE TABLE IF NOT EXISTS mfg_mrp_run (
run_no INTEGER NOT NULL,
warehouse_id VARCHAR,
from_date DATE,
horizon_weeks INTEGER,
planned_count INTEGER,
stale BOOLEAN,
stale_cause VARCHAR,
started_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (run_no)
);
CREATE TABLE IF NOT EXISTS mfg_planned_order (
warehouse_id VARCHAR NOT NULL,
product_id VARCHAR NOT NULL,
need_date DATE NOT NULL,
run_no INTEGER NOT NULL,
quantity NUMERIC(19,4),
unit_code VARCHAR,
planned_order_ref VARCHAR,
status VARCHAR,
cause_json VARCHAR,
production_order_id VARCHAR,
firmed_by VARCHAR,
firmed_sub VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (warehouse_id, product_id, need_date, run_no)
);
ALTER TABLE mfg_production_order ADD COLUMN routing_version INTEGER;
ALTER TABLE mfg_production_order ADD COLUMN frozen_routing_json VARCHAR;
ALTER TABLE mfg_production_order ADD COLUMN planned_order_ref VARCHAR;
ALTER TABLE mfg_production_order ADD COLUMN stale_note VARCHAR;
CREATE TABLE IF NOT EXISTS mfg_routing (
product_id VARCHAR NOT NULL,
routing_version INTEGER NOT NULL,
valid_from DATE,
operations_json VARCHAR,
written_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (product_id, routing_version)
);
CREATE TABLE IF NOT EXISTS mfg_scrap (
production_order_id VARCHAR NOT NULL,
scrap_no INTEGER NOT NULL,
product_id VARCHAR,
quantity NUMERIC(19,4),
unit_code VARCHAR,
quantity_base NUMERIC(19,4),
reason_code VARCHAR,
standard_value NUMERIC(18,2),
status VARCHAR,
recorded_by VARCHAR,
recorded_sub VARCHAR,
approved_by VARCHAR,
approved_sub VARCHAR,
business_date DATE,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (production_order_id, scrap_no)
);
CREATE TABLE IF NOT EXISTS mfg_scrap_reason (
reason_code VARCHAR NOT NULL,
description VARCHAR,
abnormal BOOLEAN,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (reason_code)
);
CREATE TABLE IF NOT EXISTS mfg_work_centre (
work_centre_id VARCHAR NOT NULL,
company_code VARCHAR,
warehouse_id VARCHAR,
cost_centre VARCHAR,
batch_capacity NUMERIC(19,4),
capacity_unit_code VARCHAR,
hours_per_day NUMERIC(19,4),
status VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (work_centre_id)
);
CREATE TABLE IF NOT EXISTS mfg_work_centre_rate (
work_centre_id VARCHAR NOT NULL,
rate_kind VARCHAR NOT NULL,
valid_from DATE NOT NULL,
rate_amount NUMERIC(19,4),
currency VARCHAR,
set_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (work_centre_id, rate_kind, valid_from)
);