AiDOPWarehouse/doc/plan/sql/S1S4_UAT_P0_补ASN_主样例_20260614.sql

-- P0 主样例销售订单 ASN 发货种子（订单发货页可按 MPO482024102300001 检索）
-- 租户：AIDOP 797403760988229
-- 执行前请确认未占用单号 SH-UAT-P0-20260614-01

SET @tenant_id := 797403760988229;
SET @order_no := 'MPO482024102300001';
SET @asn_id := 'SH-UAT-P0-20260614-01';
SET @operator := 'UAT数据导入';
SET @ship_date := '2024-10-29';
SET @now := NOW();

-- 主表（ShType=SH, Typed 非 S, IsActive=1）
INSERT INTO ASNBOLShipperMaster
    (Id, OrdNbr, SoldTo, ShipDate, Status, Remark, ShType, Typed, IsActive, IsConfirm, CreateUser, CreateTime2, tenant_id)
SELECT
    @asn_id, @order_no, 'CUST0005', @ship_date, '', 'UAT导入:P0主样例ASN', 'SH', '', 1, 0, @operator, @now, @tenant_id
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM ASNBOLShipperMaster
    WHERE tenant_id = @tenant_id AND Id = @asn_id
);

SET @master_rec_id := (
    SELECT RecID FROM ASNBOLShipperMaster
    WHERE tenant_id = @tenant_id AND Id = @asn_id
    LIMIT 1
);

-- 明细行 1：3121C0035 × 1500
INSERT INTO ASNBOLShipperDetail
    (Line, OrdNbr, OrdLine, ContainerItem, Descr, QtyToShip, ShType, Typed, IsActive, IsConfirm, ASNBOLShipperRecID, tenant_id)
SELECT 1, @order_no, 1, '3121C0035', 'P0主样例行1', 1500, 'SH', '', 1, 0, @master_rec_id, @tenant_id
FROM DUAL
WHERE @master_rec_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM ASNBOLShipperDetail
    WHERE tenant_id = @tenant_id AND ASNBOLShipperRecID = @master_rec_id AND Line = 1
);

-- 明细行 2：1A0C885 × 3500
INSERT INTO ASNBOLShipperDetail
    (Line, OrdNbr, OrdLine, ContainerItem, Descr, QtyToShip, ShType, Typed, IsActive, IsConfirm, ASNBOLShipperRecID, tenant_id)
SELECT 2, @order_no, 2, '1A0C885', 'P0主样例行2', 3500, 'SH', '', 1, 0, @master_rec_id, @tenant_id
FROM DUAL
WHERE @master_rec_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM ASNBOLShipperDetail
    WHERE tenant_id = @tenant_id AND ASNBOLShipperRecID = @master_rec_id AND Line = 2
);

SELECT m.RecID, m.Id, m.OrdNbr, d.Line, d.ContainerItem, d.QtyToShip
FROM ASNBOLShipperMaster m
LEFT JOIN ASNBOLShipperDetail d ON d.ASNBOLShipperRecID = m.RecID
WHERE m.tenant_id = @tenant_id AND m.OrdNbr = @order_no
ORDER BY d.Line;