AiDOPWarehouse/doc/plan/sql/S1S4_UAT_P0_execute_write.sql

-- S1-S4 P0 UAT write (AIDOP tenant 797403760988229)
-- Batch: S1S4_UAT_20260604_RQ_V1

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_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);

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'),
(@id_base+102,'604078133665862','MPO482024102300001',2,'1A0C885',3500,'2024-10-29'),
(@id_base+103,'631771616395333','MPO482025010900002',1,'3121C0035',850,'2025-01-18'),
(@id_base+104,'631771616399429','MPO482025010900002',2,'3124C0015',850,'2025-01-21'),
(@id_base+105,'618329124159557','MPO482024120200003',1,'1AB9275',800,'2024-12-03'),
(@id_base+106,'636029208526917','MPO482025012100001',1,'91C0D2C',600,'2025-02-13'),
(@id_base+107,'636029208526918','MPO482025012100001',2,'91CC0231',100,'2025-02-13'),
(@id_base+108,'633180644831301','MPO482025011300004',1,'322AD001',5500,'2025-02-11'),
(@id_base+109,'633180644831302','MPO482025011300004',2,'3221C0031',5500,'2025-02-18');

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);

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,
    mo_id BIGINT NOT NULL
);
INSERT INTO tmp_s3_uat_pr VALUES
(@id_base+501,'PR-UAT-20260604-01','M500029406','MPO482024102300001','32100035',1500,@id_base+201),
(@id_base+502,'PR-UAT-20260604-02','M500029407','MPO482024102300001','1000885',3500,@id_base+202),
(@id_base+503,'PR-UAT-20260604-03','M500067189','MPO482025010900002','32100035',850,@id_base+203),
(@id_base+504,'PR-UAT-20260604-04','M500067190','MPO482025010900002','32400015',850,@id_base+204),
(@id_base+505,'PR-UAT-20260604-05','M500053856','MPO482024120200003','1009275',800,@id_base+205),
(@id_base+506,'PR-UAT-20260604-06','M500077552','MPO482025012100001','93000230',600,@id_base+206),
(@id_base+507,'PR-UAT-20260604-07','M500077553','MPO482025012100001','93000231',100,@id_base+207),
(@id_base+508,'PR-UAT-20260604-08','M500070779','MPO482025011300004','32100001',5500,@id_base+208),
(@id_base+509,'PR-UAT-20260604-09','M500070780','MPO482025011300004','32100031',5500,@id_base+209);

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);

DROP TEMPORARY TABLE IF EXISTS tmp_s3_uat_po_hdr;
CREATE TEMPORARY TABLE tmp_s3_uat_po_hdr (
    pur_ord VARCHAR(48) NOT NULL PRIMARY KEY,
    sales_ord VARCHAR(64) NOT NULL,
    work_ord VARCHAR(128) NULL
);
INSERT INTO tmp_s3_uat_po_hdr
SELECT pur_ord, MIN(sales_ord), MIN(work_ord) FROM tmp_s3_uat_po_line GROUP BY pur_ord;

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
);
INSERT INTO tmp_s4_uat_ship VALUES
(@id_base+601,'PO-UAT-20260604-01','SH-UAT-20260604-01'),
(@id_base+602,'PO-UAT-20260604-02','SH-UAT-20260604-02'),
(@id_base+603,'PO-UAT-20260604-03','SH-UAT-20260604-03');

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, BusinessID, IsActive, IsConfirm, Priority, Urgent,
    tenant_id, CreateTime, UpdateTime
)
SELECT '8010', morder_no, product_code, 'R',
       need_number, 0, 0, @now, DATE_ADD(@now, INTERVAL 14 DAY), bill_no,
       0, 1, 1, 0, 0, @tenant_id, @now, @now
FROM tmp_s2_uat_morder;

INSERT INTO srm_pr_main (
    Id, pr_billno, pr_purchaseid, pr_purchasenumber, pr_purchasename,
    pr_rqty, pr_aqty, pr_sqty, icitem_id, icitem_name,
    state, pr_type, currencytype,
    create_by, create_by_name, create_time, update_by, update_by_name, update_time,
    tenant_id, factory_id, IsDeleted, IsRequireGoods
)
SELECT p.voucher, p.pr_billno, p.mo_id, p.morder_no, p.morder_no,
       p.pr_rqty, 0, 0, 0, p.icitem_code,
       1, 1, 0,
       @operator_id, @operator_name, @now, @operator_id, @operator_name, @now,
       @tenant_id, @factory_id, 0, 0
FROM tmp_s3_uat_pr p;

INSERT INTO PurOrdMaster (
    OwnerApplication, APAcct, CostCtr, APSubAcct, Bank, BillTo, BlanketOrd, Buyer, CloseDate,
    CommentIndex, Confirming, Consignment, Contact, Contract, CreditTermsInt, CreditTerms, Curr,
    CycleCode, DeliverTo, Disc, Domain, DueDate, DutyType, BlanketStart, BlanketEnd, ExchRate,
    ERSOption, EstVal, Sequence, ExchRate1, ExchRate2, RateType, FixedPrice, FixedRate, FOB, Frt,
    FSMType, FSTID, InvoicesVia, EMTPO, Language, MaxAgingDays, PurOrd, OrdDate, PartialOK,
    AmtPrepaid, LastPriceDt, PricingDate, PrintPO, Project, DiscTbl, PriceTbl, ERSPriceListOption,
    PST, PSTID, Recurr, `Release`, Rel, ReqBy, Revision, Remark, Scheduled, SchedulesVia,
    ServiceCharge, ShipTo, ShipVia, Site, SecondarySOCrHold, PrimarySO, SpecialCharge, Status,
    Taxable, TaxClass, TaxDate, TaxEnvironment, Tax1, Tax2, Tax3, TaxUsage, TermsofTrade,
    TransportDays, Typed, Ufld1, Ufld2, EnteredBy, Supp, Transm, BusinessID, CreateUser,
    UpdateUser, CreateTime, UpdateTime, EffTime, IsActive, IsConfirm, Potype, Department,
    IsChanged, TaxIn, Amt, `Usage`, CustAddress, CustContact, CustPhone, SalesOrd, CustPO,
    IsPriceChanged, CustName, Rev, RoleNo, InvoiceType, PricingMethod, ERPfld1, ERPWorkOrd,
    WorkOrd, tenant_id
)
SELECT
    m.OwnerApplication, m.APAcct, m.CostCtr, m.APSubAcct, m.Bank, m.BillTo, m.BlanketOrd, @operator_name, m.CloseDate,
    m.CommentIndex, m.Confirming, m.Consignment, m.Contact, m.Contract, m.CreditTermsInt, m.CreditTerms, m.Curr,
    m.CycleCode, m.DeliverTo, m.Disc, m.Domain, m.DueDate, m.DutyType, m.BlanketStart, m.BlanketEnd, m.ExchRate,
    m.ERSOption, m.EstVal, m.Sequence, m.ExchRate1, m.ExchRate2, m.RateType, m.FixedPrice, m.FixedRate, m.FOB, m.Frt,
    m.FSMType, m.FSTID, m.InvoicesVia, m.EMTPO, m.Language, m.MaxAgingDays, h.pur_ord, @now, m.PartialOK,
    m.AmtPrepaid, m.LastPriceDt, m.PricingDate, m.PrintPO, m.Project, m.DiscTbl, m.PriceTbl, m.ERSPriceListOption,
    m.PST, m.PSTID, m.Recurr, m.`Release`, m.Rel, m.ReqBy, m.Revision, CONCAT('UAT:', @batch_no), m.Scheduled, m.SchedulesVia,
    m.ServiceCharge, m.ShipTo, m.ShipVia, m.Site, m.SecondarySOCrHold, m.PrimarySO, m.SpecialCharge, m.Status,
    m.Taxable, m.TaxClass, m.TaxDate, m.TaxEnvironment, m.Tax1, m.Tax2, m.Tax3, m.TaxUsage, m.TermsofTrade,
    m.TransportDays, m.Typed, m.Ufld1, m.Ufld2, m.EnteredBy, 'VEN00060', m.Transm, m.BusinessID, @operator_name,
    @operator_name, @now, @now, m.EffTime, m.IsActive, m.IsConfirm, 'PT', m.Department,
    m.IsChanged, m.TaxIn, m.Amt, m.`Usage`, m.CustAddress, m.CustContact, m.CustPhone, h.sales_ord, m.CustPO,
    m.IsPriceChanged, m.CustName, m.Rev, m.RoleNo, m.InvoiceType, m.PricingMethod, m.ERPfld1, m.ERPWorkOrd,
    h.work_ord, @tenant_id
FROM tmp_s3_uat_po_hdr h
CROSS JOIN (SELECT * FROM PurOrdMaster WHERE PurOrd='PW202605100002' AND tenant_id=@tenant_id LIMIT 1) m;

INSERT INTO PurOrdDetail (
    QtyBO, RctCost, CreditTermsInt, UpdateCurrentCost, CumReceived1, CumReceived2, CumReceived3, CumReceived4,
    Disc, FixedPrice, InspectReq, SingleLot, SupplyPer, PurOrd, PST, PackingSlipQty, PayUMConv, PurCost,
    RctQty, QtyOrded, QtyReceived, QtyReturned, Active, QtyReleased, RctUMConversion, Scheduled, ScheduledChanged,
    SchedMRPReq, SafetyDays, SafetyHours, StdCost, Taxable, TaxIn, MaxTaxableAmt, TransportHours, UMConversion, VAT,
    IsActive, IsConfirm, Potype, IsChanged, TaxRate, IsRounding, ReceiptQty, BarCodeQty, IsClosed, QtyReturnedRefund, CumQtyBO,
    Line, ItemNum, UM, DueDate, LotSerial, PurOrdRecID, Status, WorkOrd,
    CreateUser, CreateTime, UpdateUser, UpdateTime, tenant_id
)
SELECT
    0,0,0,0,0,0,0,0,0,0,0,0,0,pl.pur_ord,0,0,1,0,0,pl.qty,0,0,1,0,1,0,0,0,0,0,0,1,1,0,0,1,0,
    1,1,'PT',0,0,0,0,0,0,0,0,
    pl.line_no, pl.item_num, 'EA', DATE_ADD(@now, INTERVAL 14 DAY), '', 0, 'O', pl.work_ord,
    @operator_name, @now, @operator_name, @now, @tenant_id
FROM tmp_s3_uat_po_line pl;

SET @ds_seq := 800;
INSERT INTO srm_polist_ds (
    Id, domain, icdsid, dsnum, status, itemnum, um, purgroup,
    suppliercode, supplier, ponumber, poline, schedqty, needdate, remarks, tenant_id
)
SELECT
    @id_base+(@ds_seq:=@ds_seq+1),
    '8010', @id_base+800, CONCAT('DS-', pl.pur_ord, '-', pl.line_no),
    'P', pl.item_num, 'EA', 'PG01', 'VEN00060', 'VEN00060',
    pl.pur_ord, pl.line_no, pl.qty, DATE_ADD(@now, INTERVAL 14 DAY), '', @tenant_id
FROM tmp_s3_uat_po_line pl
CROSS JOIN (SELECT @ds_seq:=800) init;

INSERT INTO scm_shd (id, po_billno, shddh, sh_purchase_num, estimated_delivery_date, tjrxm, tjrq, state)
SELECT ship_id, po_billno, shddh, 'VEN00060', DATE_ADD(@now, INTERVAL 7 DAY), @operator_name, DATE_FORMAT(@now,'%Y-%m-%d'), 0
FROM tmp_s4_uat_ship;

SET @zb_seq := 700;
INSERT INTO scm_shdzb (id, glid, sh_material_code, sh_delivery_quantity, po_bill, po_billline)
SELECT @id_base+(@zb_seq:=@zb_seq+1), CAST(s.ship_id AS CHAR), pl.item_num, pl.qty, pl.pur_ord, CAST(pl.line_no AS CHAR)
FROM tmp_s3_uat_po_line pl
JOIN tmp_s4_uat_ship s ON s.po_billno = pl.pur_ord
CROSS JOIN (SELECT @zb_seq:=700) init;

COMMIT;

SELECT 'crm_seorder' AS tbl, COUNT(*) cnt FROM crm_seorder WHERE tenant_id=@tenant_id AND bill_from LIKE CONCAT('UAT导入:',@batch_no,'%');
SELECT 'crm_seorderentry' AS tbl, COUNT(*) cnt FROM crm_seorderentry WHERE tenant_id=@tenant_id AND bill_no IN (SELECT bill_no FROM tmp_s1_uat_so);
SELECT 'mes_morder' AS tbl, COUNT(*) cnt FROM mes_morder WHERE tenant_id=@tenant_id AND morder_no LIKE 'M5000%';
SELECT 'srm_pr_main' AS tbl, COUNT(*) cnt FROM srm_pr_main WHERE tenant_id=@tenant_id AND pr_billno LIKE 'PR-UAT-20260604-%';
SELECT 'PurOrdMaster' AS tbl, COUNT(*) cnt FROM PurOrdMaster WHERE tenant_id=@tenant_id AND Remark LIKE CONCAT('%',@batch_no,'%');
SELECT 'scm_shd' AS tbl, COUNT(*) cnt FROM scm_shd WHERE shddh LIKE 'SH-UAT-20260604-%';

SELECT so.bill_no, COUNT(DISTINCT se.Id) line_cnt, COUNT(DISTINCT mo.Id) morder_cnt, COUNT(DISTINCT pom.PurOrd) po_cnt
FROM crm_seorder so
LEFT JOIN crm_seorderentry se ON se.seorder_id=so.Id AND se.tenant_id=@tenant_id
LEFT JOIN mes_moentry me ON me.soentry_id=se.Id AND me.tenant_id=@tenant_id
LEFT JOIN mes_morder mo ON mo.Id=me.moentry_moid AND mo.tenant_id=@tenant_id
LEFT JOIN PurOrdMaster pom ON pom.SalesOrd=so.bill_no AND pom.tenant_id=@tenant_id
LEFT JOIN PurOrdDetail pd ON pd.PurOrd=pom.PurOrd AND pd.tenant_id=@tenant_id
WHERE so.tenant_id=@tenant_id AND so.bill_from LIKE CONCAT('UAT导入:',@batch_no,'%')
GROUP BY so.bill_no;