AiDOPWarehouse/doc/db/s4_purordrct_seed_20260617.sql

-- 采购收货单测试数据（PurOrdRctMaster + PurOrdRctDetail）
-- 幂等：使用固定编号 + NOT EXISTS 防重复插入
-- 执行前请确认租户ID，默认为 797403760988229

SET @tenant_id := 797403760988229;

-- ===========================================================================
-- 1) 收货单主表 PurOrdRctMaster（RctType='rc'，5 条）
-- ===========================================================================
INSERT INTO PurOrdRctMaster
(
  RecID, Domain, RctType, Receiver, RctDate, OrdNbr, Site, BusinessID,
  CreateUser, UpdateUser, CreateTime, UpdateTime,
  IsActive, IsConfirm, Typed, Department, Supp, `Print`, IsChanged,
  TaxClass, TaxIn, Remark, Curr, IsExport, Terms, IsPlan,
  TermsofTrade, IsBackwash, tenant_id
)
SELECT
  60617000 + p.idx AS RecID,
  '100' AS Domain,
  'rc' AS RctType,
  p.receiver AS Receiver,
  DATE_ADD('2026-06-17', INTERVAL p.idx - 1 DAY) AS RctDate,
  p.ordNbr AS OrdNbr,
  'AIDOP' AS Site,
  0 AS BusinessID,
  'seed-20260617' AS CreateUser,
  'seed-20260617' AS UpdateUser,
  NOW() AS CreateTime,
  NOW() AS UpdateTime,
  1 AS IsActive,
  0 AS IsConfirm,
  'C' AS Typed,
  'D-PROD' AS Department,
  p.supp AS Supp,
  0 AS `Print`,
  0 AS IsChanged,
  '' AS TaxClass,
  0 AS TaxIn,
  CONCAT('采购收货测试-', p.ordNbr) AS Remark,
  'CNY' AS Curr,
  0 AS IsExport,
  '' AS Terms,
  0 AS IsPlan,
  '' AS TermsofTrade,
  0 AS IsBackwash,
  @tenant_id AS tenant_id
FROM (
  SELECT 1 idx, 'RC-TEST-20260617-01' receiver, 'PO-UAT-20260604-01' ordNbr, 'VEN00060' supp UNION ALL
  SELECT 2,       'RC-TEST-20260617-02',       'PO-UAT-20260604-02',       'VEN00060' UNION ALL
  SELECT 3,       'RC-TEST-20260617-03',       'PO-UAT-20260604-03',       'VEN00060' UNION ALL
  SELECT 4,       'RC-TEST-20260617-04',       'PO-UAT-20260604-04',       'VEN00060' UNION ALL
  SELECT 5,       'RC-TEST-20260617-05',       'PO-UAT-20260604-05',       'VEN00060'
) p
WHERE NOT EXISTS (
  SELECT 1 FROM PurOrdRctMaster x
  WHERE x.Receiver = p.receiver AND x.tenant_id = @tenant_id
);

-- ===========================================================================
-- 2) 收货单明细 PurOrdRctDetail（每单 2 行，共 10 条）
-- ===========================================================================
INSERT INTO PurOrdRctDetail
(
  RecID, Domain, RctType, Receiver, Line, ItemNum, Dimension1, Dimension2,
  OrdNbr, RctDate, POCost, UM, QtyOrded, RctQty, Site, Supp, Location,
  QtyReceived, Potype, BusinessID, CreateUser, UpdateUser, CreateTime, UpdateTime,
  IsActive, IsConfirm, Typed, IsChanged, TaxClass, TaxIn, UMConversion,
  Curr, IsClosed, OrdLine, IsRounding, BlanketLine, ProjectLine,
  RctLine, QtyReturn, PurOrdRctRecID, QcQty, RctNbr, Remark, tenant_id
)
SELECT
  606170000 + p.idx * 10 + d.Line AS RecID,
  '100' AS Domain,
  'rc' AS RctType,
  p.receiver AS Receiver,
  d.Line AS Line,
  d.ItemNum,
  COALESCE(d.Dimension1, '') AS Dimension1,
  COALESCE(d.Dimension2, '') AS Dimension2,
  p.ordNbr AS OrdNbr,
  DATE_ADD('2026-06-17', INTERVAL p.idx - 1 DAY) AS RctDate,
  COALESCE(d.PurCost, 10.00) AS POCost,
  COALESCE(d.UM, 'EA') AS UM,
  COALESCE(d.QtyOrded, 100) AS QtyOrded,
  ROUND(COALESCE(d.QtyOrded, 100) * 0.85, 5) AS RctQty,
  'AIDOP' AS Site,
  p.supp AS Supp,
  COALESCE(d.Location, 'WH-01') AS Location,
  ROUND(COALESCE(d.QtyOrded, 100) * 0.85, 5) AS QtyReceived,
  COALESCE(d.Potype, 'PT') AS Potype,
  0 AS BusinessID,
  'seed-20260617' AS CreateUser,
  'seed-20260617' AS UpdateUser,
  NOW() AS CreateTime,
  NOW() AS UpdateTime,
  1 AS IsActive,
  0 AS IsConfirm,
  'C' AS Typed,
  0 AS IsChanged,
  '' AS TaxClass,
  0 AS TaxIn,
  1 AS UMConversion,
  'CNY' AS Curr,
  0 AS IsClosed,
  d.Line AS OrdLine,
  0 AS IsRounding,
  0 AS BlanketLine,
  0 AS ProjectLine,
  d.Line AS RctLine,
  0 AS QtyReturn,
  60617000 + p.idx AS PurOrdRctRecID,
  ROUND(COALESCE(d.QtyOrded, 100) * 0.85, 5) AS QcQty,
  p.receiver AS RctNbr,
  CONCAT('采购收货测试明细-', p.ordNbr, '-L', d.Line) AS Remark,
  @tenant_id AS tenant_id
FROM (
  SELECT 1 idx, 'RC-TEST-20260617-01' receiver, 'PO-UAT-20260604-01' ordNbr, 'VEN00060' supp UNION ALL
  SELECT 2,       'RC-TEST-20260617-02',       'PO-UAT-20260604-02',       'VEN00060' UNION ALL
  SELECT 3,       'RC-TEST-20260617-03',       'PO-UAT-20260604-03',       'VEN00060' UNION ALL
  SELECT 4,       'RC-TEST-20260617-04',       'PO-UAT-20260604-04',       'VEN00060' UNION ALL
  SELECT 5,       'RC-TEST-20260617-05',       'PO-UAT-20260604-05',       'VEN00060'
) p
LEFT JOIN (
  SELECT PurOrd, Line,
         MIN(ItemNum) AS ItemNum,
         MIN(Dimension1) AS Dimension1,
         MIN(Dimension2) AS Dimension2,
         MAX(QtyOrded) AS QtyOrded,
         MIN(UM) AS UM,
         MIN(Location) AS Location,
         MAX(PurCost) AS PurCost,
         MIN(Potype) AS Potype
  FROM PurOrdDetail
  WHERE tenant_id = @tenant_id
    AND PurOrd LIKE 'PO-UAT-20260604-%'
    AND Line IN (1, 2)
  GROUP BY PurOrd, Line
) d ON d.PurOrd = p.ordNbr
WHERE d.ItemNum IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM PurOrdRctDetail x
    WHERE x.RctType = 'rc' AND x.Receiver = p.receiver AND x.Line = d.Line AND x.tenant_id = @tenant_id
  );

-- ===========================================================================
-- 3) 验收：查看插入结果
-- ===========================================================================
SELECT '=== 收货单主表 ===' AS info;
SELECT RecID, Receiver, RctDate, OrdNbr, Supp, Remark, tenant_id
FROM PurOrdRctMaster
WHERE Receiver LIKE 'RC-TEST-20260617-%' AND tenant_id = @tenant_id
ORDER BY RecID;

SELECT '=== 收货单明细 ===' AS info;
SELECT RecID, Receiver, Line, ItemNum, OrdNbr, QtyOrded, RctQty, QtyReceived, QcQty, Remark
FROM PurOrdRctDetail
WHERE Receiver LIKE 'RC-TEST-20260617-%' AND tenant_id = @tenant_id
ORDER BY RecID;