1094 lines
64 KiB
SQL
1094 lines
64 KiB
SQL
CREATE DATABASE IF NOT EXISTS jiaheng_erp
|
||
DEFAULT CHARACTER SET utf8mb4
|
||
DEFAULT COLLATE utf8mb4_unicode_ci;
|
||
|
||
USE jiaheng_erp;
|
||
|
||
SET NAMES utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS sys_department (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
dept_code VARCHAR(50) NOT NULL UNIQUE COMMENT '部门编码',
|
||
dept_name VARCHAR(100) NOT NULL COMMENT '部门名称',
|
||
parent_id BIGINT NULL COMMENT '上级ID',
|
||
org_node_type VARCHAR(32) NOT NULL DEFAULT 'DEPARTMENT' COMMENT '组织节点类型：COMPANY/BRANCH/DEPARTMENT/GROUP',
|
||
dept_type VARCHAR(32) NOT NULL COMMENT '部门类型：ADMIN/SALES/PURCHASE/WAREHOUSE/PRODUCTION/FINANCE/EQUIPMENT',
|
||
manager_name VARCHAR(100) NULL COMMENT '负责人姓名',
|
||
manager_employee_id BIGINT NULL COMMENT '负责人员工ID',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
|
||
sort_no INT NOT NULL DEFAULT 0 COMMENT '排序号',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
KEY idx_department_parent_sort (parent_id, sort_no, id),
|
||
FOREIGN KEY (parent_id) REFERENCES sys_department(id)
|
||
) ENGINE=InnoDB COMMENT='部门表';
|
||
|
||
CREATE TABLE IF NOT EXISTS hr_employee (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
employee_code VARCHAR(50) NOT NULL UNIQUE COMMENT '员工编码',
|
||
employee_name VARCHAR(100) NOT NULL COMMENT '员工姓名',
|
||
dept_id BIGINT NOT NULL COMMENT '部门ID',
|
||
mobile VARCHAR(30) NULL COMMENT '手机号',
|
||
gender VARCHAR(16) NULL COMMENT '性别',
|
||
hire_date DATE NULL COMMENT '入职日期',
|
||
job_title VARCHAR(100) NULL COMMENT '岗位名称',
|
||
shift_code VARCHAR(32) NULL COMMENT '班次编码',
|
||
manager_employee_id BIGINT NULL COMMENT '上级员工ID',
|
||
is_operator TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否操作工',
|
||
is_workshop_staff TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否车间人员',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (dept_id) REFERENCES sys_department(id),
|
||
FOREIGN KEY (manager_employee_id) REFERENCES hr_employee(id)
|
||
) ENGINE=InnoDB COMMENT='员工表';
|
||
|
||
CREATE TABLE IF NOT EXISTS sys_role (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
role_code VARCHAR(50) NOT NULL UNIQUE COMMENT '角色编码',
|
||
role_name VARCHAR(100) NOT NULL COMMENT '角色名称',
|
||
role_scope VARCHAR(32) NOT NULL DEFAULT 'SYSTEM' COMMENT '角色范围',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
|
||
) ENGINE=InnoDB COMMENT='角色表';
|
||
|
||
CREATE TABLE IF NOT EXISTS sys_permission (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
permission_code VARCHAR(100) NOT NULL UNIQUE COMMENT '权限编码',
|
||
permission_name VARCHAR(100) NOT NULL COMMENT '权限名称',
|
||
module_code VARCHAR(50) NOT NULL COMMENT '模块编码',
|
||
action_code VARCHAR(50) NOT NULL COMMENT '操作编码',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
|
||
) ENGINE=InnoDB COMMENT='权限点表';
|
||
|
||
CREATE TABLE IF NOT EXISTS sys_role_permission (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
role_id BIGINT NOT NULL COMMENT '角色ID',
|
||
permission_id BIGINT NOT NULL COMMENT '权限ID',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
UNIQUE KEY uk_role_permission (role_id, permission_id),
|
||
FOREIGN KEY (role_id) REFERENCES sys_role(id),
|
||
FOREIGN KEY (permission_id) REFERENCES sys_permission(id)
|
||
) ENGINE=InnoDB COMMENT='角色权限关系表';
|
||
|
||
CREATE TABLE IF NOT EXISTS sys_user (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
username VARCHAR(50) NOT NULL UNIQUE COMMENT '登录账号',
|
||
password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希',
|
||
employee_id BIGINT NOT NULL COMMENT '员工ID',
|
||
dept_id BIGINT NOT NULL COMMENT '部门ID',
|
||
nickname VARCHAR(100) NULL COMMENT '昵称',
|
||
email VARCHAR(100) NULL COMMENT '邮箱',
|
||
is_super_admin TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否超级管理员',
|
||
last_login_at DATETIME NULL COMMENT '最后登录时间',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (employee_id) REFERENCES hr_employee(id),
|
||
FOREIGN KEY (dept_id) REFERENCES sys_department(id)
|
||
) ENGINE=InnoDB COMMENT='系统用户表';
|
||
|
||
CREATE TABLE IF NOT EXISTS sys_user_role (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
user_id BIGINT NOT NULL COMMENT '用户ID',
|
||
role_id BIGINT NOT NULL COMMENT '角色ID',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
UNIQUE KEY uk_user_role (user_id, role_id),
|
||
FOREIGN KEY (user_id) REFERENCES sys_user(id),
|
||
FOREIGN KEY (role_id) REFERENCES sys_role(id)
|
||
) ENGINE=InnoDB COMMENT='用户角色关系表';
|
||
|
||
CREATE TABLE IF NOT EXISTS sys_org_employee_binding (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
dept_id BIGINT NOT NULL COMMENT '组织脑图节点ID',
|
||
employee_id BIGINT NOT NULL COMMENT '人员ID',
|
||
sort_no INT NOT NULL DEFAULT 0 COMMENT '排序号',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
UNIQUE KEY uk_org_employee_binding (dept_id, employee_id),
|
||
KEY idx_org_employee_binding_dept (dept_id, sort_no, id),
|
||
KEY idx_org_employee_binding_employee (employee_id),
|
||
FOREIGN KEY (dept_id) REFERENCES sys_department(id),
|
||
FOREIGN KEY (employee_id) REFERENCES hr_employee(id)
|
||
) ENGINE=InnoDB COMMENT='系统权限组织脑图人员绑定表';
|
||
|
||
CREATE TABLE IF NOT EXISTS sys_org_manager_binding (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
dept_id BIGINT NOT NULL COMMENT '组织脑图节点ID',
|
||
employee_id BIGINT NOT NULL COMMENT '主管人员ID',
|
||
sort_no INT NOT NULL DEFAULT 0 COMMENT '排序号',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
UNIQUE KEY uk_org_manager_binding (dept_id, employee_id),
|
||
KEY idx_org_manager_binding_dept (dept_id, sort_no, id),
|
||
KEY idx_org_manager_binding_employee (employee_id),
|
||
FOREIGN KEY (dept_id) REFERENCES sys_department(id),
|
||
FOREIGN KEY (employee_id) REFERENCES hr_employee(id)
|
||
) ENGINE=InnoDB COMMENT='系统权限组织脑图主管绑定表';
|
||
|
||
CREATE TABLE IF NOT EXISTS sys_broadcast_message (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
title VARCHAR(100) NOT NULL COMMENT '广播标题',
|
||
content VARCHAR(500) NOT NULL COMMENT '广播内容',
|
||
tone VARCHAR(32) NOT NULL DEFAULT 'INFO' COMMENT '提示类型：INFO/SUCCESS/WARNING/DANGER',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态：ACTIVE/PAUSED',
|
||
starts_at DATETIME NULL COMMENT '开始展示时间',
|
||
ends_at DATETIME NULL COMMENT '结束展示时间',
|
||
sort_no INT NOT NULL DEFAULT 0 COMMENT '排序号，越大越优先',
|
||
created_by BIGINT NULL COMMENT '创建用户ID',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
KEY idx_broadcast_status_time (status, starts_at, ends_at),
|
||
FOREIGN KEY (created_by) REFERENCES sys_user(id)
|
||
) ENGINE=InnoDB COMMENT='系统广播消息';
|
||
|
||
CREATE TABLE IF NOT EXISTS sys_ai_assistant_config (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
assistant_name VARCHAR(100) NOT NULL DEFAULT '嘉恒工艺助手' COMMENT '助手名称',
|
||
provider_name VARCHAR(100) NULL COMMENT 'LLM供应商名称',
|
||
api_base_url VARCHAR(255) NULL COMMENT 'LLM API基础地址',
|
||
api_key VARCHAR(512) NULL COMMENT 'LLM API密钥',
|
||
model_name VARCHAR(100) NULL COMMENT '模型名称',
|
||
temperature DECIMAL(6,3) NOT NULL DEFAULT 0.300 COMMENT '温度参数',
|
||
system_prompt TEXT NULL COMMENT '新建对话系统提示词',
|
||
enabled TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否启用真实LLM',
|
||
created_by BIGINT NULL COMMENT '创建用户ID',
|
||
updated_by BIGINT NULL COMMENT '更新用户ID',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (created_by) REFERENCES sys_user(id),
|
||
FOREIGN KEY (updated_by) REFERENCES sys_user(id)
|
||
) ENGINE=InnoDB COMMENT='AI助手配置';
|
||
|
||
CREATE TABLE IF NOT EXISTS md_unit (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
unit_code VARCHAR(50) NOT NULL UNIQUE COMMENT '单位编码',
|
||
unit_name VARCHAR(50) NOT NULL COMMENT '单位名称',
|
||
precision_digits INT NOT NULL DEFAULT 3 COMMENT '小数精度位数',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
|
||
) ENGINE=InnoDB COMMENT='计量单位';
|
||
|
||
CREATE TABLE IF NOT EXISTS md_customer (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
customer_code VARCHAR(50) NOT NULL UNIQUE COMMENT '客户编码',
|
||
customer_name VARCHAR(200) NOT NULL COMMENT '客户名称',
|
||
short_name VARCHAR(100) NULL COMMENT '简称',
|
||
contact_name VARCHAR(100) NULL COMMENT '联系人',
|
||
contact_phone VARCHAR(50) NULL COMMENT '联系电话',
|
||
address VARCHAR(255) NULL COMMENT '地址',
|
||
tax_no VARCHAR(100) NULL COMMENT '税号',
|
||
credit_days INT NOT NULL DEFAULT 0 COMMENT '账期天数',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
|
||
) ENGINE=InnoDB COMMENT='客户主档';
|
||
|
||
CREATE TABLE IF NOT EXISTS md_supplier (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
supplier_code VARCHAR(50) NOT NULL UNIQUE COMMENT '供应商编码',
|
||
supplier_name VARCHAR(200) NOT NULL COMMENT '供应商名称',
|
||
short_name VARCHAR(100) NULL COMMENT '简称',
|
||
contact_name VARCHAR(100) NULL COMMENT '联系人',
|
||
contact_phone VARCHAR(50) NULL COMMENT '联系电话',
|
||
address VARCHAR(255) NULL COMMENT '地址',
|
||
lead_time_days INT NOT NULL DEFAULT 0 COMMENT '采购提前期天数',
|
||
default_tax_rate DECIMAL(8,4) NOT NULL DEFAULT 0 COMMENT '默认税率',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
|
||
) ENGINE=InnoDB COMMENT='供应商主档';
|
||
|
||
CREATE TABLE IF NOT EXISTS md_item_category (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
category_code VARCHAR(50) NOT NULL UNIQUE COMMENT '分类编码',
|
||
category_name VARCHAR(100) NOT NULL COMMENT '分类名称',
|
||
parent_id BIGINT NULL COMMENT '上级ID',
|
||
item_type VARCHAR(32) NOT NULL COMMENT '物料类型',
|
||
sort_no INT NOT NULL DEFAULT 0 COMMENT '排序号',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (parent_id) REFERENCES md_item_category(id)
|
||
) ENGINE=InnoDB COMMENT='物料分类';
|
||
|
||
CREATE TABLE IF NOT EXISTS md_item (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
item_code VARCHAR(50) NOT NULL COMMENT '物料编码',
|
||
item_name VARCHAR(200) NOT NULL COMMENT '物料名称',
|
||
item_type VARCHAR(32) NOT NULL COMMENT '物料类型：RAW_MATERIAL/FINISHED_GOOD/SEMI_FINISHED/SCRAP/SPARE_PART',
|
||
category_id BIGINT NULL COMMENT '分类ID',
|
||
specification VARCHAR(200) NULL COMMENT '规格',
|
||
model_no VARCHAR(100) NULL COMMENT '型号',
|
||
material_type VARCHAR(100) NULL COMMENT '材料类型',
|
||
material_grade VARCHAR(100) NULL COMMENT '材质牌号',
|
||
length_mm DECIMAL(18,3) NOT NULL DEFAULT 0 COMMENT '长度(mm)',
|
||
width_mm DECIMAL(18,3) NOT NULL DEFAULT 0 COMMENT '宽度(mm)',
|
||
thickness_mm DECIMAL(18,3) NOT NULL DEFAULT 0 COMMENT '厚度(mm)',
|
||
density_kg_m3 DECIMAL(18,3) NOT NULL DEFAULT 0 COMMENT '密度(kg/m3)',
|
||
theoretical_weight_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '理论重量(kg)',
|
||
unit_weight_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '实际管理用单件或单批重量',
|
||
sales_unit_id BIGINT NULL COMMENT '销售单位ID',
|
||
stock_unit_id BIGINT NULL COMMENT '库存单位ID',
|
||
purchase_unit_id BIGINT NULL COMMENT '采购单位ID',
|
||
safety_stock_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '安全库存数量',
|
||
safety_stock_weight_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '安全库存重量(kg)',
|
||
scrap_sale_price DECIMAL(18,4) NOT NULL DEFAULT 0 COMMENT '废料销售单价',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (category_id) REFERENCES md_item_category(id),
|
||
FOREIGN KEY (sales_unit_id) REFERENCES md_unit(id),
|
||
FOREIGN KEY (stock_unit_id) REFERENCES md_unit(id),
|
||
FOREIGN KEY (purchase_unit_id) REFERENCES md_unit(id),
|
||
INDEX idx_md_item_code (item_code),
|
||
INDEX idx_md_item_type (item_type),
|
||
INDEX idx_md_item_category (category_id)
|
||
) ENGINE=InnoDB COMMENT='统一物料主档';
|
||
|
||
CREATE TABLE IF NOT EXISTS md_material (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
item_id BIGINT NOT NULL UNIQUE COMMENT '物料ID',
|
||
default_supplier_id BIGINT NULL COMMENT '默认供应商ID',
|
||
purchase_calc_mode VARCHAR(32) NOT NULL DEFAULT 'BY_WEIGHT' COMMENT '采购计算方式',
|
||
min_purchase_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '最小采购数量',
|
||
purchase_multiple_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '采购倍数数量',
|
||
purchase_multiple_weight_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '采购倍数重量(kg)',
|
||
lead_time_days INT NOT NULL DEFAULT 0 COMMENT '采购提前期天数',
|
||
quality_rule VARCHAR(255) NULL COMMENT '质检规则',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (item_id) REFERENCES md_item(id),
|
||
FOREIGN KEY (default_supplier_id) REFERENCES md_supplier(id)
|
||
) ENGINE=InnoDB COMMENT='原材料扩展信息';
|
||
|
||
CREATE TABLE IF NOT EXISTS md_product (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
item_id BIGINT NOT NULL UNIQUE COMMENT '物料ID',
|
||
drawing_no VARCHAR(100) NULL COMMENT '图号',
|
||
product_family VARCHAR(100) NULL COMMENT '产品系列',
|
||
version_no VARCHAR(50) NOT NULL DEFAULT 'V1.0' COMMENT '版本号',
|
||
standard_output_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '标准产出数量',
|
||
allowed_scrap_rate DECIMAL(8,4) NOT NULL DEFAULT 0 COMMENT '允许报废率',
|
||
quality_standard VARCHAR(255) NULL COMMENT '质量标准',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (item_id) REFERENCES md_item(id)
|
||
) ENGINE=InnoDB COMMENT='产品扩展信息';
|
||
|
||
CREATE TABLE IF NOT EXISTS md_process (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
process_code VARCHAR(50) NOT NULL UNIQUE COMMENT '工序编码',
|
||
process_name VARCHAR(100) NOT NULL COMMENT '工序名称',
|
||
process_type VARCHAR(50) NOT NULL COMMENT '工序类型',
|
||
is_report_required TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否必须报工',
|
||
is_quality_gate TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否质量关卡',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
|
||
) ENGINE=InnoDB COMMENT='标准工序';
|
||
|
||
CREATE TABLE IF NOT EXISTS md_work_center (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
center_code VARCHAR(50) NOT NULL UNIQUE COMMENT '工作中心编码',
|
||
center_name VARCHAR(100) NOT NULL COMMENT '工作中心名称',
|
||
dept_id BIGINT NOT NULL COMMENT '部门ID',
|
||
center_type VARCHAR(50) NOT NULL COMMENT '工作中心类型',
|
||
shift_pattern VARCHAR(50) NULL COMMENT '班制',
|
||
capacity_hours_per_day DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '日产能工时',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (dept_id) REFERENCES sys_department(id)
|
||
) ENGINE=InnoDB COMMENT='工作中心';
|
||
|
||
CREATE TABLE IF NOT EXISTS md_process_route (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
route_code VARCHAR(50) NOT NULL COMMENT '工艺路线编码',
|
||
route_name VARCHAR(100) NOT NULL COMMENT '工艺路线名称',
|
||
product_item_id BIGINT NOT NULL COMMENT '产品物料ID',
|
||
version_no VARCHAR(50) NOT NULL DEFAULT 'V1.0' COMMENT '版本号',
|
||
is_default TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否默认',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
|
||
effective_date DATE NULL COMMENT '生效日期',
|
||
expire_date DATE NULL COMMENT '失效日期',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
UNIQUE KEY uk_route_code_version (route_code, version_no),
|
||
FOREIGN KEY (product_item_id) REFERENCES md_item(id),
|
||
INDEX idx_route_product (product_item_id)
|
||
) ENGINE=InnoDB COMMENT='产品工艺路线';
|
||
|
||
CREATE TABLE IF NOT EXISTS md_process_route_operation (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
route_id BIGINT NOT NULL COMMENT '工艺路线ID',
|
||
seq_no INT NOT NULL COMMENT '序号',
|
||
process_id BIGINT NOT NULL COMMENT '工序ID',
|
||
work_center_id BIGINT NOT NULL COMMENT '工作中心ID',
|
||
operation_name VARCHAR(100) NOT NULL COMMENT '工序名称',
|
||
std_setup_minutes DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '标准准备时长(分钟)',
|
||
std_cycle_seconds DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '标准节拍(秒/件)',
|
||
std_labor_cost DECIMAL(18,4) NOT NULL DEFAULT 0 COMMENT '标准人工成本',
|
||
std_machine_cost DECIMAL(18,4) NOT NULL DEFAULT 0 COMMENT '标准设备成本',
|
||
std_outsource_cost DECIMAL(18,4) NOT NULL DEFAULT 0 COMMENT '标准委外成本',
|
||
allowed_scrap_rate DECIMAL(8,4) NOT NULL DEFAULT 0 COMMENT '允许报废率',
|
||
report_required TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否要求报工',
|
||
is_key_operation TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否关键工序',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
UNIQUE KEY uk_route_seq (route_id, seq_no),
|
||
FOREIGN KEY (route_id) REFERENCES md_process_route(id),
|
||
FOREIGN KEY (process_id) REFERENCES md_process(id),
|
||
FOREIGN KEY (work_center_id) REFERENCES md_work_center(id)
|
||
) ENGINE=InnoDB COMMENT='工艺路线工序明细';
|
||
|
||
CREATE TABLE IF NOT EXISTS md_bom (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
bom_code VARCHAR(50) NOT NULL COMMENT 'BOM编码',
|
||
product_item_id BIGINT NOT NULL COMMENT '产品物料ID',
|
||
version_no VARCHAR(50) NOT NULL DEFAULT 'V1.0' COMMENT '版本号',
|
||
is_default TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否默认',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
|
||
effective_date DATE NULL COMMENT '生效日期',
|
||
expire_date DATE NULL COMMENT '失效日期',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
UNIQUE KEY uk_bom_code_version (bom_code, version_no),
|
||
FOREIGN KEY (product_item_id) REFERENCES md_item(id),
|
||
INDEX idx_bom_product (product_item_id)
|
||
) ENGINE=InnoDB COMMENT='BOM 主表';
|
||
|
||
CREATE TABLE IF NOT EXISTS md_bom_item (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
bom_id BIGINT NOT NULL COMMENT 'BOM ID',
|
||
seq_no INT NOT NULL COMMENT '序号',
|
||
material_item_id BIGINT NOT NULL COMMENT '原材料物料ID',
|
||
usage_type VARCHAR(32) NOT NULL DEFAULT 'WEIGHT' COMMENT '用量类型：WEIGHT/FIXED_QTY',
|
||
qty_per_piece DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '单件用量',
|
||
net_weight_per_piece_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '单件净重(kg)',
|
||
gross_weight_per_piece_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '单件毛重(kg)',
|
||
loss_rate DECIMAL(8,4) NOT NULL DEFAULT 0 COMMENT '损耗率',
|
||
issue_over_tolerance_rate DECIMAL(8,4) NOT NULL DEFAULT 0 COMMENT '领料超发容差率',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
UNIQUE KEY uk_bom_seq (bom_id, seq_no),
|
||
FOREIGN KEY (bom_id) REFERENCES md_bom(id),
|
||
FOREIGN KEY (material_item_id) REFERENCES md_item(id)
|
||
) ENGINE=InnoDB COMMENT='BOM 明细';
|
||
|
||
CREATE TABLE IF NOT EXISTS em_equipment (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
equipment_code VARCHAR(50) NOT NULL UNIQUE COMMENT '设备编码',
|
||
equipment_name VARCHAR(100) NOT NULL COMMENT '设备名称',
|
||
category_name VARCHAR(100) NULL COMMENT '分类名称',
|
||
work_center_id BIGINT NOT NULL COMMENT '工作中心ID',
|
||
manufacturer VARCHAR(100) NULL COMMENT '制造商',
|
||
model_no VARCHAR(100) NULL COMMENT '型号',
|
||
install_date DATE NULL COMMENT '安装日期',
|
||
rated_power_kw DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '额定功率(kW)',
|
||
occupied_area_sqm DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '占用场地面积(平方米)',
|
||
maintenance_cycle_hours DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '保养周期(小时)',
|
||
last_maintenance_at DATETIME NULL COMMENT '上次保养时间',
|
||
next_maintenance_at DATETIME NULL COMMENT '下次保养时间',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'IDLE' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (work_center_id) REFERENCES md_work_center(id)
|
||
) ENGINE=InnoDB COMMENT='设备台账';
|
||
|
||
CREATE TABLE IF NOT EXISTS em_maintenance_plan (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
plan_code VARCHAR(50) NOT NULL UNIQUE COMMENT '计划编码',
|
||
equipment_id BIGINT NOT NULL COMMENT '设备ID',
|
||
plan_name VARCHAR(100) NOT NULL COMMENT '计划名称',
|
||
maintenance_type VARCHAR(32) NOT NULL COMMENT '保养类型',
|
||
cycle_hours DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '周期小时数',
|
||
cycle_days INT NOT NULL DEFAULT 0 COMMENT '周期天数',
|
||
last_execute_at DATETIME NULL COMMENT '上次执行时间',
|
||
next_execute_at DATETIME NULL COMMENT '下次执行时间',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (equipment_id) REFERENCES em_equipment(id)
|
||
) ENGINE=InnoDB COMMENT='设备保养计划';
|
||
|
||
CREATE TABLE IF NOT EXISTS em_maintenance_order (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
order_no VARCHAR(50) NOT NULL UNIQUE COMMENT '单据号',
|
||
equipment_id BIGINT NOT NULL COMMENT '设备ID',
|
||
plan_id BIGINT NULL COMMENT '计划ID',
|
||
maintenance_type VARCHAR(32) NOT NULL COMMENT '保养类型',
|
||
start_time DATETIME NULL COMMENT '开始时间',
|
||
end_time DATETIME NULL COMMENT '结束时间',
|
||
downtime_minutes DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '停机时长(分钟)',
|
||
owner_employee_id BIGINT NULL COMMENT '负责人员工ID',
|
||
result_summary VARCHAR(255) NULL COMMENT '结果摘要',
|
||
cost_amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '成本金额',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'CREATED' COMMENT '状态',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (equipment_id) REFERENCES em_equipment(id),
|
||
FOREIGN KEY (plan_id) REFERENCES em_maintenance_plan(id),
|
||
FOREIGN KEY (owner_employee_id) REFERENCES hr_employee(id)
|
||
) ENGINE=InnoDB COMMENT='设备保养维修工单';
|
||
|
||
CREATE TABLE IF NOT EXISTS so_sales_order (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
order_no VARCHAR(50) NOT NULL UNIQUE COMMENT '单据号',
|
||
customer_id BIGINT NOT NULL COMMENT '客户ID',
|
||
order_date DATE NOT NULL COMMENT '订单日期',
|
||
promised_date DATE NULL COMMENT '承诺交期',
|
||
sales_employee_id BIGINT NULL COMMENT '销售员员工ID',
|
||
delivery_address VARCHAR(255) NULL COMMENT '送货地址',
|
||
tax_rate DECIMAL(8,4) NOT NULL DEFAULT 0 COMMENT '税率',
|
||
total_amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '总金额',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'DRAFT' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (customer_id) REFERENCES md_customer(id),
|
||
FOREIGN KEY (sales_employee_id) REFERENCES hr_employee(id),
|
||
INDEX idx_so_customer (customer_id),
|
||
INDEX idx_so_order_date (order_date)
|
||
) ENGINE=InnoDB COMMENT='订单头';
|
||
|
||
CREATE TABLE IF NOT EXISTS so_sales_order_item (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
sales_order_id BIGINT NOT NULL COMMENT '订单ID',
|
||
line_no INT NOT NULL COMMENT '行号',
|
||
product_item_id BIGINT NOT NULL COMMENT '产品物料ID',
|
||
customer_part_no VARCHAR(100) NULL COMMENT '客户料号',
|
||
order_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '订单数量',
|
||
delivered_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '已发货数量',
|
||
unit_price DECIMAL(18,4) NOT NULL DEFAULT 0 COMMENT '单价',
|
||
line_amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '行金额',
|
||
promised_date DATE NULL COMMENT '承诺交期',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'OPEN' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
UNIQUE KEY uk_so_line (sales_order_id, line_no),
|
||
FOREIGN KEY (sales_order_id) REFERENCES so_sales_order(id),
|
||
FOREIGN KEY (product_item_id) REFERENCES md_item(id),
|
||
INDEX idx_so_item_product (product_item_id)
|
||
) ENGINE=InnoDB COMMENT='订单明细';
|
||
|
||
CREATE TABLE IF NOT EXISTS mrp_material_demand (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
demand_no VARCHAR(50) NOT NULL UNIQUE COMMENT '需求单号',
|
||
sales_order_item_id BIGINT NOT NULL COMMENT '订单明细ID',
|
||
product_item_id BIGINT NOT NULL COMMENT '产品物料ID',
|
||
material_item_id BIGINT NOT NULL COMMENT '原材料物料ID',
|
||
bom_item_id BIGINT NOT NULL COMMENT 'BOM明细ID',
|
||
order_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '订单数量',
|
||
theoretical_weight_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '理论重量(kg)',
|
||
planned_weight_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '计划重量(kg)',
|
||
available_stock_weight_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '可用库存重量(kg)',
|
||
shortage_weight_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '缺口重量(kg)',
|
||
suggested_purchase_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '建议采购数量',
|
||
suggested_purchase_weight_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '建议采购重量(kg)',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'NEW' COMMENT '状态',
|
||
generated_at DATETIME NULL COMMENT '生成时间',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (sales_order_item_id) REFERENCES so_sales_order_item(id),
|
||
FOREIGN KEY (product_item_id) REFERENCES md_item(id),
|
||
FOREIGN KEY (material_item_id) REFERENCES md_item(id),
|
||
FOREIGN KEY (bom_item_id) REFERENCES md_bom_item(id),
|
||
INDEX idx_mrp_material (material_item_id),
|
||
INDEX idx_mrp_status (status)
|
||
) ENGINE=InnoDB COMMENT='物料需求计划';
|
||
|
||
CREATE TABLE IF NOT EXISTS po_purchase_order (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
po_no VARCHAR(50) NOT NULL UNIQUE COMMENT '采购订单号',
|
||
supplier_id BIGINT NOT NULL COMMENT 'SupplierID',
|
||
order_date DATE NOT NULL COMMENT '订单日期',
|
||
expected_date DATE NULL COMMENT '预计到货日期',
|
||
purchaser_employee_id BIGINT NULL COMMENT '采购员员工ID',
|
||
target_warehouse_type VARCHAR(32) NOT NULL DEFAULT 'RAW' COMMENT '目标仓库类型：RAW原材料库/AUX辅料库',
|
||
tax_rate DECIMAL(8,4) NOT NULL DEFAULT 0 COMMENT '税率',
|
||
total_amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '总金额',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'DRAFT' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (supplier_id) REFERENCES md_supplier(id),
|
||
FOREIGN KEY (purchaser_employee_id) REFERENCES hr_employee(id),
|
||
INDEX idx_po_supplier (supplier_id)
|
||
) ENGINE=InnoDB COMMENT='采购订单头';
|
||
|
||
CREATE TABLE IF NOT EXISTS po_purchase_order_item (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
purchase_order_id BIGINT NOT NULL COMMENT '采购订单ID',
|
||
line_no INT NOT NULL COMMENT '行号',
|
||
material_item_id BIGINT NOT NULL COMMENT '原材料物料ID',
|
||
source_demand_id BIGINT NULL COMMENT '来源需求ID',
|
||
order_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '订单数量',
|
||
order_weight_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '订单重量(kg)',
|
||
received_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '已收货数量',
|
||
received_weight_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '已收货重量(kg)',
|
||
unit_price DECIMAL(18,4) NOT NULL DEFAULT 0 COMMENT '单价',
|
||
line_amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '行金额',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'OPEN' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
UNIQUE KEY uk_po_line (purchase_order_id, line_no),
|
||
FOREIGN KEY (purchase_order_id) REFERENCES po_purchase_order(id),
|
||
FOREIGN KEY (material_item_id) REFERENCES md_item(id),
|
||
FOREIGN KEY (source_demand_id) REFERENCES mrp_material_demand(id),
|
||
INDEX idx_po_item_material (material_item_id)
|
||
) ENGINE=InnoDB COMMENT='采购订单明细';
|
||
|
||
CREATE TABLE IF NOT EXISTS wh_warehouse (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
warehouse_code VARCHAR(50) NOT NULL UNIQUE COMMENT '仓库编码',
|
||
warehouse_name VARCHAR(100) NOT NULL COMMENT '仓库名称',
|
||
warehouse_type VARCHAR(32) NOT NULL COMMENT '仓库类型：RAW/SEMI/FINISHED/AUX/RETURN/SCRAP',
|
||
manager_employee_id BIGINT NULL COMMENT '上级员工ID',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (manager_employee_id) REFERENCES hr_employee(id)
|
||
) ENGINE=InnoDB COMMENT='仓库主档';
|
||
|
||
CREATE TABLE IF NOT EXISTS wh_location (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
warehouse_id BIGINT NOT NULL COMMENT '仓库ID',
|
||
location_code VARCHAR(50) NOT NULL COMMENT '库位编码',
|
||
location_name VARCHAR(100) NOT NULL COMMENT '库位名称',
|
||
zone_name VARCHAR(100) NULL COMMENT '库区名称',
|
||
is_default TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否默认',
|
||
is_locked TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否锁定',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
UNIQUE KEY uk_warehouse_location (warehouse_id, location_code),
|
||
FOREIGN KEY (warehouse_id) REFERENCES wh_warehouse(id)
|
||
) ENGINE=InnoDB COMMENT='库位表';
|
||
|
||
CREATE TABLE IF NOT EXISTS po_receipt (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
receipt_no VARCHAR(50) NOT NULL UNIQUE COMMENT '入库单号',
|
||
purchase_order_id BIGINT NOT NULL COMMENT '采购订单ID',
|
||
warehouse_id BIGINT NOT NULL COMMENT '仓库ID',
|
||
receipt_date DATETIME NOT NULL COMMENT '收货日期',
|
||
receiver_employee_id BIGINT NULL COMMENT '接收人员工ID',
|
||
supplier_delivery_no VARCHAR(100) NULL COMMENT '供应商送货单号',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'DRAFT' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (purchase_order_id) REFERENCES po_purchase_order(id),
|
||
FOREIGN KEY (warehouse_id) REFERENCES wh_warehouse(id),
|
||
FOREIGN KEY (receiver_employee_id) REFERENCES hr_employee(id)
|
||
) ENGINE=InnoDB COMMENT='采购收货单头';
|
||
|
||
CREATE TABLE IF NOT EXISTS po_receipt_item (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
receipt_id BIGINT NOT NULL COMMENT '单据ID',
|
||
line_no INT NOT NULL COMMENT '行号',
|
||
purchase_order_item_id BIGINT NOT NULL COMMENT '采购订单明细ID',
|
||
material_item_id BIGINT NOT NULL COMMENT '原材料物料ID',
|
||
location_id BIGINT NULL COMMENT '库位ID',
|
||
lot_no VARCHAR(50) NOT NULL COMMENT '批次号',
|
||
received_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '已收货数量',
|
||
received_weight_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '已收货重量(kg)',
|
||
accepted_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '合格数量',
|
||
accepted_weight_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '合格重量(kg)',
|
||
unit_cost DECIMAL(18,4) NOT NULL DEFAULT 0 COMMENT '单位成本',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'PENDING_QC' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
UNIQUE KEY uk_receipt_line (receipt_id, line_no),
|
||
FOREIGN KEY (receipt_id) REFERENCES po_receipt(id),
|
||
FOREIGN KEY (purchase_order_item_id) REFERENCES po_purchase_order_item(id),
|
||
FOREIGN KEY (material_item_id) REFERENCES md_item(id),
|
||
FOREIGN KEY (location_id) REFERENCES wh_location(id),
|
||
INDEX idx_receipt_item_lot (lot_no)
|
||
) ENGINE=InnoDB COMMENT='采购收货单明细';
|
||
|
||
CREATE TABLE IF NOT EXISTS wh_stock_lot (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
lot_no VARCHAR(50) NOT NULL UNIQUE COMMENT '批次号',
|
||
item_id BIGINT NOT NULL COMMENT '物料ID',
|
||
warehouse_id BIGINT NOT NULL COMMENT '仓库ID',
|
||
location_id BIGINT NULL COMMENT '库位ID',
|
||
source_doc_type VARCHAR(32) NOT NULL COMMENT '来源单据类型',
|
||
source_doc_id BIGINT NOT NULL COMMENT '来源单据ID',
|
||
source_line_id BIGINT NULL COMMENT '来源单据行ID',
|
||
logistics_waybill_no VARCHAR(100) NULL COMMENT '运单号',
|
||
logistics_freight_amount DECIMAL(18,2) NULL COMMENT '运费',
|
||
logistics_photo_url VARCHAR(255) NULL COMMENT '订单照片路径',
|
||
inbound_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '入库数量',
|
||
inbound_weight_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '入库重量(kg)',
|
||
remaining_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '剩余数量',
|
||
remaining_weight_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '剩余重量(kg)',
|
||
locked_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '锁定数量',
|
||
locked_weight_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '锁定重量(kg)',
|
||
unit_cost DECIMAL(18,4) NOT NULL DEFAULT 0 COMMENT '单位成本',
|
||
production_date DATE NULL COMMENT '生产日期',
|
||
expire_date DATE NULL COMMENT '失效日期',
|
||
quality_status VARCHAR(32) NOT NULL DEFAULT 'PASS' COMMENT '质量状态',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'AVAILABLE' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (item_id) REFERENCES md_item(id),
|
||
FOREIGN KEY (warehouse_id) REFERENCES wh_warehouse(id),
|
||
FOREIGN KEY (location_id) REFERENCES wh_location(id),
|
||
INDEX idx_stock_lot_item (item_id),
|
||
INDEX idx_stock_lot_wh (warehouse_id)
|
||
) ENGINE=InnoDB COMMENT='库存批次';
|
||
|
||
CREATE TABLE IF NOT EXISTS wh_stock_balance (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
item_id BIGINT NOT NULL COMMENT '物料ID',
|
||
warehouse_id BIGINT NOT NULL COMMENT '仓库ID',
|
||
location_id BIGINT NULL COMMENT '库位ID',
|
||
qty_on_hand DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '现存数量',
|
||
weight_on_hand_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '现存重量(kg)',
|
||
qty_available DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '可用数量',
|
||
weight_available_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '可用重量(kg)',
|
||
qty_allocated DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '已分配数量',
|
||
weight_allocated_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '已分配重量(kg)',
|
||
avg_unit_cost DECIMAL(18,4) NOT NULL DEFAULT 0 COMMENT '平均单位成本',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
UNIQUE KEY uk_stock_balance (item_id, warehouse_id, location_id),
|
||
FOREIGN KEY (item_id) REFERENCES md_item(id),
|
||
FOREIGN KEY (warehouse_id) REFERENCES wh_warehouse(id),
|
||
FOREIGN KEY (location_id) REFERENCES wh_location(id)
|
||
) ENGINE=InnoDB COMMENT='库存余额表';
|
||
|
||
CREATE TABLE IF NOT EXISTS wh_inventory_txn (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
txn_no VARCHAR(50) NOT NULL UNIQUE COMMENT '流水号',
|
||
txn_type VARCHAR(32) NOT NULL COMMENT '流水类型：PURCHASE_IN/MATERIAL_ISSUE/FG_IN/SALES_OUT/RETURN_IN/RETURN_OUT/SCRAP_OUT/PRODUCTION_SCRAP_IN/OUTSOURCING_SCRAP_IN/SCRAP_SALE_OUT/ADJUST',
|
||
item_id BIGINT NOT NULL COMMENT '物料ID',
|
||
warehouse_id BIGINT NOT NULL COMMENT '仓库ID',
|
||
location_id BIGINT NULL COMMENT '库位ID',
|
||
lot_id BIGINT NULL COMMENT '批次ID',
|
||
qty_change DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '数量变动',
|
||
weight_change_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '重量变动(kg)',
|
||
unit_cost DECIMAL(18,4) NOT NULL DEFAULT 0 COMMENT '单位成本',
|
||
amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '金额',
|
||
source_doc_type VARCHAR(32) NOT NULL COMMENT '来源单据类型',
|
||
source_doc_id BIGINT NOT NULL COMMENT '来源单据ID',
|
||
source_line_id BIGINT NULL COMMENT '来源单据行ID',
|
||
logistics_waybill_no VARCHAR(100) NULL COMMENT '运单号',
|
||
logistics_freight_amount DECIMAL(18,2) NULL COMMENT '运费',
|
||
logistics_photo_url VARCHAR(255) NULL COMMENT '订单照片路径',
|
||
biz_time DATETIME NOT NULL COMMENT '业务时间',
|
||
operator_user_id BIGINT NULL COMMENT '操作人用户ID',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (item_id) REFERENCES md_item(id),
|
||
FOREIGN KEY (warehouse_id) REFERENCES wh_warehouse(id),
|
||
FOREIGN KEY (location_id) REFERENCES wh_location(id),
|
||
FOREIGN KEY (lot_id) REFERENCES wh_stock_lot(id),
|
||
FOREIGN KEY (operator_user_id) REFERENCES sys_user(id),
|
||
INDEX idx_inventory_txn_item (item_id),
|
||
INDEX idx_inventory_txn_time (biz_time)
|
||
) ENGINE=InnoDB COMMENT='库存流水';
|
||
|
||
CREATE TABLE IF NOT EXISTS pp_work_order (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
work_order_no VARCHAR(50) NOT NULL UNIQUE COMMENT '工单号',
|
||
work_order_type VARCHAR(32) NOT NULL DEFAULT 'NORMAL' COMMENT '工单类型：NORMAL/REWORK',
|
||
source_sales_order_item_id BIGINT NULL COMMENT '来源订单明细ID',
|
||
product_item_id BIGINT NOT NULL COMMENT '产品物料ID',
|
||
route_id BIGINT NOT NULL COMMENT '工艺路线ID',
|
||
bom_id BIGINT NOT NULL COMMENT 'BOM ID',
|
||
planned_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '计划数量',
|
||
released_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '下达数量',
|
||
finished_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '完工数量',
|
||
scrap_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '报废数量',
|
||
planned_start_time DATETIME NULL COMMENT '计划开始时间',
|
||
planned_end_time DATETIME NULL COMMENT '计划结束时间',
|
||
actual_start_time DATETIME NULL COMMENT '实际开始时间',
|
||
actual_end_time DATETIME NULL COMMENT '实际结束时间',
|
||
priority_level INT NOT NULL DEFAULT 3 COMMENT '优先级',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'CREATED' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (source_sales_order_item_id) REFERENCES so_sales_order_item(id),
|
||
FOREIGN KEY (product_item_id) REFERENCES md_item(id),
|
||
FOREIGN KEY (route_id) REFERENCES md_process_route(id),
|
||
FOREIGN KEY (bom_id) REFERENCES md_bom(id),
|
||
INDEX idx_work_order_status (status)
|
||
) ENGINE=InnoDB COMMENT='生产工单';
|
||
|
||
CREATE TABLE IF NOT EXISTS pp_work_order_material (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
work_order_id BIGINT NOT NULL COMMENT '工单ID',
|
||
bom_item_id BIGINT NOT NULL COMMENT 'BOM明细ID',
|
||
material_item_id BIGINT NOT NULL COMMENT '原材料物料ID',
|
||
required_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '需求数量',
|
||
required_weight_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '需求重量(kg)',
|
||
issued_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '已领料数量',
|
||
issued_weight_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '已领料重量(kg)',
|
||
returned_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '已退料数量',
|
||
returned_weight_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '已退料重量(kg)',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'OPEN' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
UNIQUE KEY uk_work_order_material (work_order_id, bom_item_id),
|
||
FOREIGN KEY (work_order_id) REFERENCES pp_work_order(id),
|
||
FOREIGN KEY (bom_item_id) REFERENCES md_bom_item(id),
|
||
FOREIGN KEY (material_item_id) REFERENCES md_item(id)
|
||
) ENGINE=InnoDB COMMENT='工单用料清单';
|
||
|
||
CREATE TABLE IF NOT EXISTS pp_work_order_operation (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
work_order_id BIGINT NOT NULL COMMENT '工单ID',
|
||
route_operation_id BIGINT NOT NULL COMMENT '工艺路线工序ID',
|
||
seq_no INT NOT NULL COMMENT '序号',
|
||
operation_name VARCHAR(100) NOT NULL COMMENT '工序名称',
|
||
work_center_id BIGINT NOT NULL COMMENT '工作中心ID',
|
||
planned_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '计划数量',
|
||
report_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '报工数量',
|
||
good_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '合格数量',
|
||
scrap_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '报废数量',
|
||
rework_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '返工数量',
|
||
std_labor_cost DECIMAL(18,4) NOT NULL DEFAULT 0 COMMENT '标准人工成本',
|
||
std_machine_cost DECIMAL(18,4) NOT NULL DEFAULT 0 COMMENT '标准设备成本',
|
||
std_outsource_cost DECIMAL(18,4) NOT NULL DEFAULT 0 COMMENT '标准委外成本',
|
||
allowed_scrap_rate DECIMAL(8,4) NOT NULL DEFAULT 0 COMMENT '允许报废率',
|
||
is_abnormal TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否异常',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'PENDING' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
UNIQUE KEY uk_work_order_operation (work_order_id, seq_no),
|
||
FOREIGN KEY (work_order_id) REFERENCES pp_work_order(id),
|
||
FOREIGN KEY (route_operation_id) REFERENCES md_process_route_operation(id),
|
||
FOREIGN KEY (work_center_id) REFERENCES md_work_center(id)
|
||
) ENGINE=InnoDB COMMENT='工单工序清单';
|
||
|
||
CREATE TABLE IF NOT EXISTS pp_material_issue (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
issue_no VARCHAR(50) NOT NULL UNIQUE COMMENT '领料单号',
|
||
work_order_id BIGINT NOT NULL COMMENT '工单ID',
|
||
warehouse_id BIGINT NOT NULL COMMENT '仓库ID',
|
||
issue_time DATETIME NOT NULL COMMENT '领料时间',
|
||
issuer_employee_id BIGINT NULL COMMENT '发料人员工ID',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'CREATED' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (work_order_id) REFERENCES pp_work_order(id),
|
||
FOREIGN KEY (warehouse_id) REFERENCES wh_warehouse(id),
|
||
FOREIGN KEY (issuer_employee_id) REFERENCES hr_employee(id)
|
||
) ENGINE=InnoDB COMMENT='生产领料单头';
|
||
|
||
CREATE TABLE IF NOT EXISTS pp_material_issue_item (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
material_issue_id BIGINT NOT NULL COMMENT '领料单ID',
|
||
work_order_material_id BIGINT NOT NULL COMMENT '工单用料ID',
|
||
material_item_id BIGINT NOT NULL COMMENT '原材料物料ID',
|
||
lot_id BIGINT NOT NULL COMMENT '批次ID',
|
||
location_id BIGINT NULL COMMENT '库位ID',
|
||
issue_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT 'Issue数量',
|
||
issue_weight_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT 'issue_weight_kg',
|
||
unit_cost DECIMAL(18,4) NOT NULL DEFAULT 0 COMMENT '单位成本',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'POSTED' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (material_issue_id) REFERENCES pp_material_issue(id),
|
||
FOREIGN KEY (work_order_material_id) REFERENCES pp_work_order_material(id),
|
||
FOREIGN KEY (material_item_id) REFERENCES md_item(id),
|
||
FOREIGN KEY (lot_id) REFERENCES wh_stock_lot(id),
|
||
FOREIGN KEY (location_id) REFERENCES wh_location(id)
|
||
) ENGINE=InnoDB COMMENT='生产领料单明细';
|
||
|
||
CREATE TABLE IF NOT EXISTS pp_operation_report (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
report_no VARCHAR(50) NOT NULL UNIQUE COMMENT '报工单号',
|
||
work_order_id BIGINT NOT NULL COMMENT '工单ID',
|
||
work_order_operation_id BIGINT NOT NULL COMMENT '工单工序ID',
|
||
employee_id BIGINT NOT NULL COMMENT '员工ID',
|
||
equipment_id BIGINT NULL COMMENT '设备ID',
|
||
report_source VARCHAR(32) NOT NULL DEFAULT 'MANUAL' COMMENT '报工来源：MANUAL/MINI_APP/IMPORT',
|
||
shift_code VARCHAR(32) NULL COMMENT '班次编码',
|
||
start_time DATETIME NOT NULL COMMENT '开始时间',
|
||
end_time DATETIME NOT NULL COMMENT '结束时间',
|
||
report_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '报工数量',
|
||
good_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '合格数量',
|
||
scrap_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '报废数量',
|
||
rework_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '返工数量',
|
||
scrap_reason VARCHAR(255) NULL COMMENT '报废原因',
|
||
downtime_minutes DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '停机时长(分钟)',
|
||
extra_cost_amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '额外成本金额',
|
||
is_abnormal TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否异常',
|
||
approved_by BIGINT NULL COMMENT '审批人用户ID',
|
||
approved_at DATETIME NULL COMMENT '审批时间',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (work_order_id) REFERENCES pp_work_order(id),
|
||
FOREIGN KEY (work_order_operation_id) REFERENCES pp_work_order_operation(id),
|
||
FOREIGN KEY (employee_id) REFERENCES hr_employee(id),
|
||
FOREIGN KEY (equipment_id) REFERENCES em_equipment(id),
|
||
FOREIGN KEY (approved_by) REFERENCES sys_user(id),
|
||
INDEX idx_operation_report_operation (work_order_operation_id),
|
||
INDEX idx_operation_report_employee (employee_id)
|
||
) ENGINE=InnoDB COMMENT='工序报工记录';
|
||
|
||
CREATE TABLE IF NOT EXISTS pp_scrap_record (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
scrap_no VARCHAR(50) NOT NULL UNIQUE COMMENT '报废单号',
|
||
report_id BIGINT NOT NULL COMMENT '报工记录ID',
|
||
work_order_id BIGINT NOT NULL COMMENT '工单ID',
|
||
work_order_operation_id BIGINT NOT NULL COMMENT '工单工序ID',
|
||
item_id BIGINT NOT NULL COMMENT '物料ID',
|
||
scrap_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '报废数量',
|
||
scrap_weight_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '报废重量(kg)',
|
||
scrap_reason VARCHAR(255) NULL COMMENT '报废原因',
|
||
disposal_type VARCHAR(32) NOT NULL DEFAULT 'SCRAP' COMMENT '处置类型：SCRAP/REWORK',
|
||
saleable_scrap_weight_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '可售废料重量(kg)',
|
||
scrap_cost_amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '报废成本金额',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'CONFIRMED' COMMENT '状态',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (report_id) REFERENCES pp_operation_report(id),
|
||
FOREIGN KEY (work_order_id) REFERENCES pp_work_order(id),
|
||
FOREIGN KEY (work_order_operation_id) REFERENCES pp_work_order_operation(id),
|
||
FOREIGN KEY (item_id) REFERENCES md_item(id)
|
||
) ENGINE=InnoDB COMMENT='报废与返工记录';
|
||
|
||
CREATE TABLE IF NOT EXISTS pp_completion_receipt (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
receipt_no VARCHAR(50) NOT NULL UNIQUE COMMENT '入库单号',
|
||
work_order_id BIGINT NOT NULL COMMENT '工单ID',
|
||
warehouse_id BIGINT NOT NULL COMMENT '仓库ID',
|
||
receipt_time DATETIME NOT NULL COMMENT '入库时间',
|
||
receiver_employee_id BIGINT NULL COMMENT '接收人员工ID',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'CREATED' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (work_order_id) REFERENCES pp_work_order(id),
|
||
FOREIGN KEY (warehouse_id) REFERENCES wh_warehouse(id),
|
||
FOREIGN KEY (receiver_employee_id) REFERENCES hr_employee(id)
|
||
) ENGINE=InnoDB COMMENT='完工入库单头';
|
||
|
||
CREATE TABLE IF NOT EXISTS pp_completion_receipt_item (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
completion_receipt_id BIGINT NOT NULL COMMENT '完工入库单ID',
|
||
line_no INT NOT NULL COMMENT '行号',
|
||
product_item_id BIGINT NOT NULL COMMENT '产品物料ID',
|
||
location_id BIGINT NULL COMMENT '库位ID',
|
||
lot_no VARCHAR(50) NOT NULL COMMENT '批次号',
|
||
receipt_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT 'Receipt数量',
|
||
receipt_weight_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT 'receipt_weight_kg',
|
||
unit_cost DECIMAL(18,4) NOT NULL DEFAULT 0 COMMENT '单位成本',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'POSTED' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
UNIQUE KEY uk_completion_line (completion_receipt_id, line_no),
|
||
FOREIGN KEY (completion_receipt_id) REFERENCES pp_completion_receipt(id),
|
||
FOREIGN KEY (product_item_id) REFERENCES md_item(id),
|
||
FOREIGN KEY (location_id) REFERENCES wh_location(id),
|
||
INDEX idx_completion_lot (lot_no)
|
||
) ENGINE=InnoDB COMMENT='完工入库单明细';
|
||
|
||
CREATE TABLE IF NOT EXISTS so_delivery (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
delivery_no VARCHAR(50) NOT NULL UNIQUE COMMENT '发货单号',
|
||
sales_order_id BIGINT NOT NULL COMMENT '订单ID',
|
||
warehouse_id BIGINT NOT NULL COMMENT '仓库ID',
|
||
delivery_date DATETIME NOT NULL COMMENT '发货时间',
|
||
shipper_employee_id BIGINT NULL COMMENT '发货人员工ID',
|
||
consignee_name VARCHAR(100) NULL COMMENT '收货人姓名',
|
||
consignee_phone VARCHAR(50) NULL COMMENT '收货人电话',
|
||
delivery_address VARCHAR(255) NULL COMMENT '送货地址',
|
||
logistics_waybill_no VARCHAR(100) NULL COMMENT '运单号',
|
||
logistics_freight_amount DECIMAL(18,2) NULL COMMENT '运费',
|
||
logistics_photo_url VARCHAR(255) NULL COMMENT '订单照片路径',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'CREATED' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (sales_order_id) REFERENCES so_sales_order(id),
|
||
FOREIGN KEY (warehouse_id) REFERENCES wh_warehouse(id),
|
||
FOREIGN KEY (shipper_employee_id) REFERENCES hr_employee(id)
|
||
) ENGINE=InnoDB COMMENT='发货单头';
|
||
|
||
CREATE TABLE IF NOT EXISTS so_delivery_item (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
delivery_id BIGINT NOT NULL COMMENT '发货单ID',
|
||
line_no INT NOT NULL COMMENT '行号',
|
||
sales_order_item_id BIGINT NOT NULL COMMENT '订单明细ID',
|
||
product_item_id BIGINT NOT NULL COMMENT '产品物料ID',
|
||
lot_id BIGINT NULL COMMENT '批次ID',
|
||
delivery_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '发货数量',
|
||
delivery_weight_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '发货重量(kg)',
|
||
unit_price DECIMAL(18,4) NOT NULL DEFAULT 0 COMMENT '单价',
|
||
line_amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '行金额',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'POSTED' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
UNIQUE KEY uk_delivery_line (delivery_id, line_no),
|
||
FOREIGN KEY (delivery_id) REFERENCES so_delivery(id),
|
||
FOREIGN KEY (sales_order_item_id) REFERENCES so_sales_order_item(id),
|
||
FOREIGN KEY (product_item_id) REFERENCES md_item(id),
|
||
FOREIGN KEY (lot_id) REFERENCES wh_stock_lot(id)
|
||
) ENGINE=InnoDB COMMENT='发货单明细';
|
||
|
||
CREATE TABLE IF NOT EXISTS rt_return_order (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
return_no VARCHAR(50) NOT NULL UNIQUE COMMENT '退货单号',
|
||
customer_id BIGINT NOT NULL COMMENT '客户ID',
|
||
sales_order_id BIGINT NULL COMMENT '订单ID',
|
||
delivery_id BIGINT NULL COMMENT '发货单ID',
|
||
return_date DATETIME NOT NULL COMMENT '退货时间',
|
||
return_reason VARCHAR(255) NULL COMMENT '退货原因',
|
||
handler_employee_id BIGINT NULL COMMENT '处理人员工ID',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'CREATED' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (customer_id) REFERENCES md_customer(id),
|
||
FOREIGN KEY (sales_order_id) REFERENCES so_sales_order(id),
|
||
FOREIGN KEY (delivery_id) REFERENCES so_delivery(id),
|
||
FOREIGN KEY (handler_employee_id) REFERENCES hr_employee(id)
|
||
) ENGINE=InnoDB COMMENT='退货单头';
|
||
|
||
CREATE TABLE IF NOT EXISTS rt_return_item (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
return_order_id BIGINT NOT NULL COMMENT '退货单ID',
|
||
line_no INT NOT NULL COMMENT '行号',
|
||
sales_order_item_id BIGINT NULL COMMENT '订单明细ID',
|
||
delivery_item_id BIGINT NULL COMMENT '发货明细ID',
|
||
product_item_id BIGINT NOT NULL COMMENT '产品物料ID',
|
||
source_lot_id BIGINT NULL COMMENT '来源批次ID',
|
||
return_qty DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '退货数量',
|
||
return_weight_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '退货重量(kg)',
|
||
disposition_status VARCHAR(32) NOT NULL DEFAULT 'PENDING' COMMENT '处置状态',
|
||
responsibility_type VARCHAR(32) NULL COMMENT '责任类型',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
UNIQUE KEY uk_return_line (return_order_id, line_no),
|
||
INDEX idx_rt_return_item_delivery_item (delivery_item_id),
|
||
FOREIGN KEY (return_order_id) REFERENCES rt_return_order(id),
|
||
FOREIGN KEY (sales_order_item_id) REFERENCES so_sales_order_item(id),
|
||
FOREIGN KEY (delivery_item_id) REFERENCES so_delivery_item(id),
|
||
FOREIGN KEY (product_item_id) REFERENCES md_item(id),
|
||
FOREIGN KEY (source_lot_id) REFERENCES wh_stock_lot(id)
|
||
) ENGINE=InnoDB COMMENT='退货单明细';
|
||
|
||
CREATE TABLE IF NOT EXISTS rt_return_disposition (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
disposition_no VARCHAR(50) NOT NULL UNIQUE COMMENT '处置单号',
|
||
return_item_id BIGINT NOT NULL COMMENT '退货明细ID',
|
||
disposition_type VARCHAR(32) NOT NULL COMMENT '处置方式：REWORK/SCRAP',
|
||
rework_work_order_id BIGINT NULL COMMENT '返工工单ID',
|
||
scrap_weight_kg DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '报废重量(kg)',
|
||
extra_cost_amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '额外成本金额',
|
||
scrap_sale_amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '废料销售金额',
|
||
closed_at DATETIME NULL COMMENT '关闭时间',
|
||
status VARCHAR(32) NOT NULL DEFAULT 'OPEN' COMMENT '状态',
|
||
remark VARCHAR(255) NULL COMMENT '备注',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (return_item_id) REFERENCES rt_return_item(id),
|
||
FOREIGN KEY (rework_work_order_id) REFERENCES pp_work_order(id)
|
||
) ENGINE=InnoDB COMMENT='退货处置记录';
|
||
|
||
CREATE TABLE IF NOT EXISTS fi_accounting_period (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
period_code VARCHAR(20) NOT NULL UNIQUE COMMENT '期间编码',
|
||
year_no INT NOT NULL COMMENT '年度',
|
||
month_no INT NOT NULL COMMENT '月份',
|
||
start_date DATE NOT NULL COMMENT '开始日期',
|
||
end_date DATE NOT NULL COMMENT '结束日期',
|
||
close_status VARCHAR(32) NOT NULL DEFAULT 'OPEN' COMMENT '关账状态',
|
||
closed_at DATETIME NULL COMMENT '关闭时间',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
|
||
) ENGINE=InnoDB COMMENT='财务期间';
|
||
|
||
CREATE TABLE IF NOT EXISTS fi_overhead_entry (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
period_id BIGINT NOT NULL COMMENT '期间ID',
|
||
overhead_type VARCHAR(32) NOT NULL COMMENT '制造费用类型：RENT/UTILITY/LOGISTICS/ADMIN/DEPRECIATION/OTHER',
|
||
dept_id BIGINT NULL COMMENT '部门ID',
|
||
amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '金额',
|
||
allocation_basis VARCHAR(32) NOT NULL DEFAULT 'WORK_HOURS' COMMENT '分摊依据：WORK_HOURS/MACHINE_HOURS/QTY/OCCUPIED_AREA',
|
||
description VARCHAR(255) NULL COMMENT '说明',
|
||
created_by BIGINT NULL COMMENT '创建人用户ID',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (period_id) REFERENCES fi_accounting_period(id),
|
||
FOREIGN KEY (dept_id) REFERENCES sys_department(id),
|
||
FOREIGN KEY (created_by) REFERENCES sys_user(id)
|
||
) ENGINE=InnoDB COMMENT='制造费用录入';
|
||
|
||
CREATE TABLE IF NOT EXISTS fi_cost_allocation (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
period_id BIGINT NOT NULL COMMENT '期间ID',
|
||
work_order_id BIGINT NOT NULL COMMENT '工单ID',
|
||
product_item_id BIGINT NOT NULL COMMENT '产品物料ID',
|
||
allocation_basis_value DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '分摊依据值',
|
||
overhead_amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '制造费用金额',
|
||
material_amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '材料金额',
|
||
labor_amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '人工金额',
|
||
machine_amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '设备金额',
|
||
abnormal_loss_amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '异常损失金额',
|
||
total_cost_amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '总成本金额',
|
||
unit_cost DECIMAL(18,6) NOT NULL DEFAULT 0 COMMENT '单位成本',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
UNIQUE KEY uk_period_work_order (period_id, work_order_id),
|
||
FOREIGN KEY (period_id) REFERENCES fi_accounting_period(id),
|
||
FOREIGN KEY (work_order_id) REFERENCES pp_work_order(id),
|
||
FOREIGN KEY (product_item_id) REFERENCES md_item(id)
|
||
) ENGINE=InnoDB COMMENT='工单成本分摊结果';
|
||
|
||
CREATE TABLE IF NOT EXISTS fi_statement_snapshot (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
|
||
snapshot_no VARCHAR(50) NOT NULL UNIQUE COMMENT '快照编号',
|
||
period_id BIGINT NOT NULL COMMENT '期间ID',
|
||
statement_type VARCHAR(32) NOT NULL COMMENT '报表类型：WEEKLY/MONTHLY/INVENTORY/COSTING',
|
||
report_date DATE NOT NULL COMMENT '报表日期',
|
||
summary_json JSON NOT NULL COMMENT '汇总数据(JSON)',
|
||
created_by BIGINT NULL COMMENT '创建人用户ID',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
FOREIGN KEY (period_id) REFERENCES fi_accounting_period(id),
|
||
FOREIGN KEY (created_by) REFERENCES sys_user(id)
|
||
) ENGINE=InnoDB COMMENT='财务报表快照';
|