AiDOPWarehouse/doc/db/mdp/04_dwd_s4_s8_foundation.sql

-- dop_bi 逻辑域：L2 主题宽表（方案 §6.2）
-- S4 采购执行：核心消费 dwd_po_trans；S8 异常监控：可联接 dwd_po_trans / dwd_qc_trans 等做根因与关联分析。
-- 与 S3 试点表 dwd_supplier_delivery 可并存，后续由 ETL 对齐或汇总。

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS dwd_wo_trans (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    wo_no VARCHAR(50),
    item_code VARCHAR(50),
    item_name VARCHAR(200),
    line_code VARCHAR(50),
    line_name VARCHAR(100),
    op_seq INT,
    workcenter_code VARCHAR(50),
    emp_code VARCHAR(50),
    shift_code VARCHAR(20),
    plan_qty DECIMAL(12,3),
    actual_qty DECIMAL(12,3),
    scrap_qty DECIMAL(12,3),
    work_minutes INT,
    setup_minutes INT,
    downtime_minutes INT,
    trans_date DATE,
    trans_week VARCHAR(10),
    trans_month VARCHAR(7),
    source_system VARCHAR(20),
    sync_time DATETIME,
    INDEX idx_tenant_date (tenant_id, trans_date),
    INDEX idx_tenant_wo (tenant_id, wo_no),
    INDEX idx_tenant_line_date (tenant_id, line_code, trans_date),
    INDEX idx_tenant_item_date (tenant_id, item_code, trans_date)
) COMMENT='工单执行明细宽表';

CREATE TABLE IF NOT EXISTS dwd_inv_trans (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    item_code VARCHAR(50),
    item_name VARCHAR(200),
    wh_code VARCHAR(50),
    location_code VARCHAR(50),
    trans_type VARCHAR(20),
    qty DECIMAL(12,3),
    trans_date DATE,
    trans_week VARCHAR(10),
    trans_month VARCHAR(7),
    source_system VARCHAR(20),
    sync_time DATETIME,
    INDEX idx_tenant_date (tenant_id, trans_date),
    INDEX idx_tenant_item (tenant_id, item_code),
    INDEX idx_tenant_wh (tenant_id, wh_code)
) COMMENT='库存交易明细宽表';

CREATE TABLE IF NOT EXISTS dwd_qc_trans (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    item_code VARCHAR(50),
    supplier_code VARCHAR(50),
    batch_no VARCHAR(50),
    sample_qty INT,
    defect_qty INT,
    result ENUM('PASS','FAIL','CONCESSION'),
    trans_date DATE,
    source_system VARCHAR(20),
    sync_time DATETIME,
    INDEX idx_tenant_date (tenant_id, trans_date),
    INDEX idx_tenant_item (tenant_id, item_code),
    INDEX idx_tenant_supplier (tenant_id, supplier_code)
) COMMENT='质检交易明细宽表';

CREATE TABLE IF NOT EXISTS dwd_po_trans (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    po_no VARCHAR(50),
    supplier_code VARCHAR(50),
    item_code VARCHAR(50),
    order_qty DECIMAL(12,3),
    received_qty DECIMAL(12,3),
    trans_date DATE,
    source_system VARCHAR(20),
    sync_time DATETIME,
    INDEX idx_tenant_date (tenant_id, trans_date),
    INDEX idx_tenant_supplier (tenant_id, supplier_code)
) COMMENT='采购交易明细宽表';

CREATE TABLE IF NOT EXISTS dwd_ship_trans (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    so_no VARCHAR(50),
    customer_code VARCHAR(50),
    item_code VARCHAR(50),
    ship_qty DECIMAL(12,3),
    ship_date DATE,
    source_system VARCHAR(20),
    sync_time DATETIME,
    INDEX idx_tenant_date (tenant_id, ship_date),
    INDEX idx_tenant_customer (tenant_id, customer_code)
) COMMENT='发货交易明细宽表';