AiDOPWarehouse/doc/plan/sql/S1S4_UAT_P0_试导数据填充_20260604.sql

-- =============================================================================
-- S1-S4 P0 试导数据填充（dopdemorq 实抽 → tmp 表）
-- 批次：S1S4_UAT_20260604_RQ_V1 | 租户：797403760988229
-- 抽数记录：doc/plan/S1S4_UAT_P0_抽数记录_20260604.md
--
-- 用法：
--   1) 仅验证 tmp：执行 Section A（含末尾 ROLLBACK 的试跑块）
--   2) 正式写库：人工确认后执行 Section B（取消注释），勿执行 ROLLBACK
-- =============================================================================

-- -----------------------------------------------------------------------------
-- Section A：变量 + 映射 + tmp 实数据（可先单独执行到 SELECT 校验）
-- -----------------------------------------------------------------------------

SET @tenant_id     := 797403760988229;
SET @batch_no      := 'S1S4_UAT_20260604_RQ_V1';
SET @operator_id   := 1300000000111;
SET @operator_name := 'UAT数据导入';
SET @now           := NOW();
SET @factory_id    := 797403760988229;
SET @id_base       := 9106000400000001;

DROP TEMPORARY TABLE IF EXISTS tmp_s1s4_uat_id_map;
CREATE TEMPORARY TABLE tmp_s1s4_uat_id_map (
    batch_no     VARCHAR(64)  NOT NULL,
    entity_name  VARCHAR(64)  NOT NULL,
    source_key   VARCHAR(128) NOT NULL,
    target_id    BIGINT       NOT NULL,
    ref_key      VARCHAR(128) NULL,
    PRIMARY KEY (batch_no, entity_name, source_key)
);

-- 销售订单（5 单；第 5 单用 MPO482025011300004 替换不存在的 010800003）
INSERT INTO tmp_s1s4_uat_id_map (batch_no, entity_name, source_key, target_id, ref_key) VALUES
(@batch_no,'crm_seorder','MPO482024102300001',@id_base+1,'MPO482024102300001'),
(@batch_no,'crm_seorder','MPO482025010900002',@id_base+2,'MPO482025010900002'),
(@batch_no,'crm_seorder','MPO482024120200003',@id_base+3,'MPO482024120200003'),
(@batch_no,'crm_seorder','MPO482025012100001',@id_base+4,'MPO482025012100001'),
(@batch_no,'crm_seorder','MPO482025011300004',@id_base+5,'MPO482025011300004');

-- 订单明细（旧库 soentry Id → 新 Id）
INSERT INTO tmp_s1s4_uat_id_map VALUES
(@batch_no,'crm_seorderentry','604078133665861',@id_base+101,'MPO482024102300001'),
(@batch_no,'crm_seorderentry','604078133665862',@id_base+102,'MPO482024102300001'),
(@batch_no,'crm_seorderentry','631771616395333',@id_base+103,'MPO482025010900002'),
(@batch_no,'crm_seorderentry','631771616399429',@id_base+104,'MPO482025010900002'),
(@batch_no,'crm_seorderentry','618329124159557',@id_base+105,'MPO482024120200003'),
(@batch_no,'crm_seorderentry','636029208526917',@id_base+106,'MPO482025012100001'),
(@batch_no,'crm_seorderentry','636029208526918',@id_base+107,'MPO482025012100001'),
(@batch_no,'crm_seorderentry','633180644831301',@id_base+108,'MPO482025011300004'),
(@batch_no,'crm_seorderentry','633180644831302',@id_base+109,'MPO482025011300004');

-- 工单（保留旧库 morder_no 作 ref_key）
INSERT INTO tmp_s1s4_uat_id_map VALUES
(@batch_no,'mes_morder','M500029406',@id_base+201,'604078133665861'),
(@batch_no,'mes_morder','M500029407',@id_base+202,'604078133665862'),
(@batch_no,'mes_morder','M500067189',@id_base+203,'631771616395333'),
(@batch_no,'mes_morder','M500067190',@id_base+204,'631771616399429'),
(@batch_no,'mes_morder','M500053856',@id_base+205,'618329124159557'),
(@batch_no,'mes_morder','M500077552',@id_base+206,'636029208526917'),
(@batch_no,'mes_morder','M500077553',@id_base+207,'636029208526918'),
(@batch_no,'mes_morder','M500070779',@id_base+208,'633180644831301'),
(@batch_no,'mes_morder','M500070780',@id_base+209,'633180644831302');

-- 采购订单（合成 PO，按销售订单分组）
INSERT INTO tmp_s1s4_uat_id_map VALUES
(@batch_no,'PurOrdMaster','PO-UAT-20260604-01',@id_base+301,'MPO482024102300001'),
(@batch_no,'PurOrdMaster','PO-UAT-20260604-02',@id_base+302,'MPO482025010900002'),
(@batch_no,'PurOrdMaster','PO-UAT-20260604-03',@id_base+303,'MPO482024120200003'),
(@batch_no,'PurOrdMaster','PO-UAT-20260604-04',@id_base+304,'MPO482025012100001'),
(@batch_no,'PurOrdMaster','PO-UAT-20260604-05',@id_base+305,'MPO482025011300004');

-- S1 订单头
DROP TEMPORARY TABLE IF EXISTS tmp_s1_uat_so;
CREATE TEMPORARY TABLE tmp_s1_uat_so (
    bill_no       VARCHAR(64) NOT NULL PRIMARY KEY,
    target_id     BIGINT NOT NULL,
    custom_no     VARCHAR(64) NULL,
    custom_name   VARCHAR(128) NULL,
    order_date    DATETIME NULL,
    delivery_date DATETIME NULL,
    urgent        INT DEFAULT 0
);
INSERT INTO tmp_s1_uat_so VALUES
('MPO482024102300001',@id_base+1,'CUST0005','WKQXGFYXGE','2024-10-23','2024-10-23',1),
('MPO482025010900002',@id_base+2,'CUST0005','WKQXGFYXGE','2025-01-09','2025-01-09',1),
('MPO482024120200003',@id_base+3,'CUST0005','WKQXGFYXGE','2024-12-02','2024-12-02',1),
('MPO482025012100001',@id_base+4,'CUST0005','WKQXGFYXGE','2025-01-21','2025-01-21',1),
('MPO482025011300004',@id_base+5,'CUST0005','WKQXGFYXGE','2025-01-13','2025-01-13',1);

-- S1 订单明细（dopdemorq 实抽）
DROP TEMPORARY TABLE IF EXISTS tmp_s1_uat_so_line;
CREATE TEMPORARY TABLE tmp_s1_uat_so_line (
    target_id    BIGINT NOT NULL PRIMARY KEY,
    source_entry VARCHAR(32) NOT NULL,
    bill_no      VARCHAR(64) NOT NULL,
    entry_seq    INT NOT NULL,
    item_number  VARCHAR(64) NULL,
    qty          DECIMAL(18,4) NULL,
    plan_date    DATETIME NULL
);
INSERT INTO tmp_s1_uat_so_line VALUES
(@id_base+101,'604078133665861','MPO482024102300001',1,'3121C0035',1500,'2024-10-29 00:00:00'),
(@id_base+102,'604078133665862','MPO482024102300001',2,'1A0C885',3500,'2024-10-29 00:00:00'),
(@id_base+103,'631771616395333','MPO482025010900002',1,'3121C0035',850,'2025-01-18 00:00:00'),
(@id_base+104,'631771616399429','MPO482025010900002',2,'3124C0015',850,'2025-01-21 00:00:00'),
(@id_base+105,'618329124159557','MPO482024120200003',1,'1AB9275',800,'2024-12-03 00:00:00'),
(@id_base+106,'636029208526917','MPO482025012100001',1,'91C0D2C',600,'2025-02-13 00:00:00'),
(@id_base+107,'636029208526918','MPO482025012100001',2,'91CC0231',100,'2025-02-13 00:00:00'),
(@id_base+108,'633180644831301','MPO482025011300004',1,'322AD001',5500,'2025-02-11 00:00:00'),
(@id_base+109,'633180644831302','MPO482025011300004',2,'3221C0031',5500,'2025-02-18 00:00:00');

-- S2 工单（dopdemorq 实抽）
DROP TEMPORARY TABLE IF EXISTS tmp_s2_uat_morder;
CREATE TEMPORARY TABLE tmp_s2_uat_morder (
    target_mo_id  BIGINT NOT NULL PRIMARY KEY,
    target_me_id  BIGINT NOT NULL,
    source_entry  VARCHAR(32) NOT NULL,
    bill_no       VARCHAR(64) NOT NULL,
    morder_no     VARCHAR(128) NOT NULL,
    product_code  VARCHAR(128) NULL,
    need_number   DECIMAL(23,10) NULL
);
INSERT INTO tmp_s2_uat_morder VALUES
(@id_base+201,@id_base+401,'604078133665861','MPO482024102300001','M500029406','32100035',1500),
(@id_base+202,@id_base+402,'604078133665862','MPO482024102300001','M500029407','1000885',3500),
(@id_base+203,@id_base+403,'631771616395333','MPO482025010900002','M500067189','32100035',850),
(@id_base+204,@id_base+404,'631771616399429','MPO482025010900002','M500067190','32400015',850),
(@id_base+205,@id_base+405,'618329124159557','MPO482024120200003','M500053856','1009275',800),
(@id_base+206,@id_base+406,'636029208526917','MPO482025012100001','M500077552','93000230',600),
(@id_base+207,@id_base+407,'636029208526918','MPO482025012100001','M500077553','93000231',100),
(@id_base+208,@id_base+408,'633180644831301','MPO482025011300004','M500070779','32100001',5500),
(@id_base+209,@id_base+409,'633180644831302','MPO482025011300004','M500070780','32100031',5500);

-- S3 采购申请（每工单 1 条，物料编码取工单 product_code）
DROP TEMPORARY TABLE IF EXISTS tmp_s3_uat_pr;
CREATE TEMPORARY TABLE tmp_s3_uat_pr (
    voucher       BIGINT NOT NULL PRIMARY KEY,
    pr_billno     VARCHAR(64) NOT NULL,
    morder_no     VARCHAR(128) NOT NULL,
    bill_no       VARCHAR(64) NOT NULL,
    icitem_code   VARCHAR(64) NULL,
    pr_rqty       DECIMAL(18,4) NULL
);
INSERT INTO tmp_s3_uat_pr VALUES
(@id_base+501,'PR-UAT-20260604-01','M500029406','MPO482024102300001','32100035',1500),
(@id_base+502,'PR-UAT-20260604-02','M500029407','MPO482024102300001','1000885',3500),
(@id_base+503,'PR-UAT-20260604-03','M500067189','MPO482025010900002','32100035',850),
(@id_base+504,'PR-UAT-20260604-04','M500067190','MPO482025010900002','32400015',850),
(@id_base+505,'PR-UAT-20260604-05','M500053856','MPO482024120200003','1009275',800),
(@id_base+506,'PR-UAT-20260604-06','M500077552','MPO482025012100001','93000230',600),
(@id_base+507,'PR-UAT-20260604-07','M500077553','MPO482025012100001','93000231',100),
(@id_base+508,'PR-UAT-20260604-08','M500070779','MPO482025011300004','32100001',5500),
(@id_base+509,'PR-UAT-20260604-09','M500070780','MPO482025011300004','32100031',5500);

-- S3 采购订单行（每 PO 2 行：成品料号 + 原料占位）
DROP TEMPORARY TABLE IF EXISTS tmp_s3_uat_po_line;
CREATE TEMPORARY TABLE tmp_s3_uat_po_line (
    pur_ord    VARCHAR(48) NOT NULL,
    line_no    INT NOT NULL,
    sales_ord  VARCHAR(64) NOT NULL,
    work_ord   VARCHAR(128) NULL,
    item_num   VARCHAR(60) NOT NULL,
    qty        DECIMAL(15,5) NOT NULL,
    PRIMARY KEY (pur_ord, line_no)
);
INSERT INTO tmp_s3_uat_po_line VALUES
('PO-UAT-20260604-01',1,'MPO482024102300001','M500029406','3121C0035',1500),
('PO-UAT-20260604-01',2,'MPO482024102300001','M500029407','1A0C885',3500),
('PO-UAT-20260604-02',1,'MPO482025010900002','M500067189','3121C0035',850),
('PO-UAT-20260604-02',2,'MPO482025010900002','M500067190','3124C0015',850),
('PO-UAT-20260604-03',1,'MPO482024120200003','M500053856','1AB9275',800),
('PO-UAT-20260604-03',2,'MPO482024120200003','M500053856','RAW-SUB-01',200),
('PO-UAT-20260604-04',1,'MPO482025012100001','M500077552','91C0D2C',600),
('PO-UAT-20260604-04',2,'MPO482025012100001','M500077553','91CC0231',100),
('PO-UAT-20260604-05',1,'MPO482025011300004','M500070779','322AD001',5500),
('PO-UAT-20260604-05',2,'MPO482025011300004','M500070780','3221C0031',5500);

-- S4 发货（3 张，覆盖前 3 个 PO）
DROP TEMPORARY TABLE IF EXISTS tmp_s4_uat_ship;
CREATE TEMPORARY TABLE tmp_s4_uat_ship (
    ship_id     BIGINT NOT NULL PRIMARY KEY,
    po_billno   VARCHAR(48) NOT NULL,
    shddh       VARCHAR(64) NOT NULL,
    supp_code   VARCHAR(32) NOT NULL
);
INSERT INTO tmp_s4_uat_ship VALUES
(@id_base+601,'PO-UAT-20260604-01','SH-UAT-20260604-01','VEN00060'),
(@id_base+602,'PO-UAT-20260604-02','SH-UAT-20260604-02','VEN00060'),
(@id_base+603,'PO-UAT-20260604-03','SH-UAT-20260604-03','VEN00060');

-- A.1 tmp 链路自检（只读）
SELECT 'tmp_so' AS chk, COUNT(*) AS cnt FROM tmp_s1_uat_so;
SELECT 'tmp_line' AS chk, COUNT(*) AS cnt FROM tmp_s1_uat_so_line;
SELECT 'tmp_morder' AS chk, COUNT(*) AS cnt FROM tmp_s2_uat_morder;
SELECT l.bill_no, l.item_number, m.morder_no, m.need_number
FROM tmp_s1_uat_so_line l
JOIN tmp_s2_uat_morder m ON m.source_entry = l.source_entry
ORDER BY l.bill_no, l.entry_seq;


-- -----------------------------------------------------------------------------
-- Section B：正式写入业务表（默认注释 — 确认后取消整段注释）
-- -----------------------------------------------------------------------------

/*
START TRANSACTION;

INSERT INTO crm_seorder (
    Id, bill_no, order_type, custom_no, custom_name,
    date, rdate, urgent, bill_from, closed, IsDeleted,
    create_by_name, create_time, update_by_name, update_time,
    tenant_id, factory_id
)
SELECT target_id, bill_no, 1, custom_no, custom_name,
       order_date, delivery_date, urgent,
       CONCAT('UAT导入:', @batch_no), 0, 0,
       @operator_name, @now, @operator_name, @now,
       @tenant_id, @factory_id
FROM tmp_s1_uat_so;

INSERT INTO crm_seorderentry (
    Id, seorder_id, bill_no, entry_seq, item_number,
    qty, plan_date, date, IsDeleted,
    create_by_name, create_time, update_by_name, update_time,
    tenant_id, factory_id, seorderentry_id
)
SELECT l.target_id, s.target_id, l.bill_no, l.entry_seq, l.item_number,
       l.qty, l.plan_date, s.order_date, 0,
       @operator_name, @now, @operator_name, @now,
       @tenant_id, @factory_id, l.target_id
FROM tmp_s1_uat_so_line l
JOIN tmp_s1_uat_so s ON s.bill_no = l.bill_no;

INSERT INTO mes_morder (
    Id, morder_no, morder_date, morder_state, product_code, product_name,
    need_number, morder_production_number, IsDeleted,
    create_by_name, create_time, update_by_name, update_time,
    tenant_id, factory_id
)
SELECT target_mo_id, morder_no, @now, 'Released', product_code, product_code,
       need_number, need_number, 0,
       @operator_name, @now, @operator_name, @now,
       @tenant_id, @factory_id
FROM tmp_s2_uat_morder;

INSERT INTO mes_moentry (
    Id, moentry_moid, moentry_mono, soentry_id, fbill_no,
    need_number, morder_production_number, IsDeleted,
    create_by_name, create_time, tenant_id, factory_id
)
SELECT m.target_me_id, m.target_mo_id, m.morder_no, l.target_id, m.bill_no,
       m.need_number, m.need_number, 0,
       @operator_name, @now, @tenant_id, @factory_id
FROM tmp_s2_uat_morder m
JOIN tmp_s1_uat_so_line l ON l.source_entry = m.source_entry;

INSERT INTO WorkOrdMaster (
    Domain, WorkOrd, ItemNum, Status, QtyOrded, QtyCompleted, LbrVar,
    OrdDate, DueDate, SalesJob, tenant_id, CreateTime, UpdateTime
)
SELECT CAST(@factory_id AS CHAR), morder_no, product_code, 'R',
       need_number, 0, 0, @now, DATE_ADD(@now, INTERVAL 14 DAY), bill_no,
       @tenant_id, @now, @now
FROM tmp_s2_uat_morder;

-- PurOrdMaster / Detail / srm_pr_main / scm_shd 等见 S1S4_UAT_P0_试导SQL草案_20260604.sql Section 4-5
-- 执行前请核对 PurOrdDetail 必填默认值

COMMIT;
*/