AiDOPWarehouse/doc/db/s4_global_uat_seed_20260608.sql

-- S4 global UAT seed for tenant 797403760988229.
-- Idempotent: uses fixed UAT document numbers and NOT EXISTS guards.

SET @tenant_id := 797403760988229;

-- 1) Existing SH-UAT shipments were imported without tenant_id.
UPDATE scm_shd
SET tenant_id = @tenant_id,
    sh_purchase_name = COALESCE(NULLIF(sh_purchase_name, ''), sh_purchase_num),
    state = 1,
    shzt = COALESCE(NULLIF(shzt, ''), '待收'),
    tjrxm = COALESCE(NULLIF(tjrxm, ''), 'UAT数据导入'),
    tjrq = COALESCE(NULLIF(tjrq, ''), '2026-06-04')
WHERE shddh LIKE 'SH-UAT-%'
  AND (tenant_id IS NULL OR tenant_id <> @tenant_id);

UPDATE scm_shdzb
SET tenant_id = @tenant_id
WHERE (
    po_bill IN ('PO-UAT-20260604-01', 'PO-UAT-20260604-02', 'PO-UAT-20260604-03', 'PO-UAT-20260604-04', 'PO-UAT-20260604-05')
    OR po_billno IN ('PO-UAT-20260604-01', 'PO-UAT-20260604-02', 'PO-UAT-20260604-03', 'PO-UAT-20260604-04', 'PO-UAT-20260604-05')
  )
  AND (tenant_id IS NULL OR tenant_id <> @tenant_id);

-- 2) Source receipt documents (rc) for return creation and traceability.
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
  60604000 + p.idx AS RecID,
  COALESCE(m.Domain, '100') AS Domain,
  'rc' AS RctType,
  p.rc_receiver AS Receiver,
  DATE_ADD('2026-06-04', INTERVAL p.idx - 1 DAY) AS RctDate,
  p.po AS OrdNbr,
  COALESCE(m.Site, 'AIDOP') AS Site,
  0 AS BusinessID,
  'S1S4_UAT_20260604_RQ_V1' AS CreateUser,
  'S1S4_UAT_20260604_RQ_V1' AS UpdateUser,
  NOW() AS CreateTime,
  NOW() AS UpdateTime,
  1 AS IsActive,
  0 AS IsConfirm,
  'C' AS Typed,
  COALESCE(m.Department, 'D-PROD') AS Department,
  COALESCE(m.Supp, 'VEN00060') AS Supp,
  0 AS Print,
  0 AS IsChanged,
  COALESCE(m.TaxClass, '') AS TaxClass,
  COALESCE(m.TaxIn, 0) AS TaxIn,
  CONCAT('S4 global UAT receipt for ', p.po) AS Remark,
  COALESCE(m.Curr, 'CNY') AS Curr,
  0 AS IsExport,
  COALESCE(m.CreditTerms, '') AS Terms,
  0 AS IsPlan,
  'S1S4_UAT' AS TermsofTrade,
  0 AS IsBackwash,
  @tenant_id AS tenant_id
FROM (
  SELECT 1 idx, 'PO-UAT-20260604-01' po, 'RC-UAT-20260604-01' rc_receiver UNION ALL
  SELECT 2, 'PO-UAT-20260604-02', 'RC-UAT-20260604-02' UNION ALL
  SELECT 3, 'PO-UAT-20260604-03', 'RC-UAT-20260604-03' UNION ALL
  SELECT 4, 'PO-UAT-20260604-04', 'RC-UAT-20260604-04' UNION ALL
  SELECT 5, 'PO-UAT-20260604-05', 'RC-UAT-20260604-05'
) p
LEFT JOIN PurOrdMaster m ON m.PurOrd = p.po AND m.tenant_id = @tenant_id
WHERE NOT EXISTS (
  SELECT 1 FROM PurOrdRctMaster x
  WHERE x.RctType = 'rc' AND x.Receiver = p.rc_receiver AND x.tenant_id = @tenant_id
);

-- 3) Purchase return documents (pt) visible in the S4 return page.
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
  60604050 + p.idx AS RecID,
  COALESCE(m.Domain, '100') AS Domain,
  'pt' AS RctType,
  p.pt_receiver AS Receiver,
  DATE_ADD('2026-06-09', INTERVAL p.idx - 1 DAY) AS RctDate,
  p.po AS OrdNbr,
  COALESCE(m.Site, 'AIDOP') AS Site,
  0 AS BusinessID,
  'S1S4_UAT_20260604_RQ_V1' AS CreateUser,
  'S1S4_UAT_20260604_RQ_V1' AS UpdateUser,
  NOW() AS CreateTime,
  NOW() AS UpdateTime,
  1 AS IsActive,
  0 AS IsConfirm,
  'C' AS Typed,
  COALESCE(m.Department, 'D-PROD') AS Department,
  COALESCE(m.Supp, 'VEN00060') AS Supp,
  0 AS Print,
  0 AS IsChanged,
  COALESCE(m.TaxClass, '') AS TaxClass,
  COALESCE(m.TaxIn, 0) AS TaxIn,
  CONCAT('S4 global UAT purchase return for ', p.po) AS Remark,
  COALESCE(m.Curr, 'CNY') AS Curr,
  0 AS IsExport,
  COALESCE(m.CreditTerms, '') AS Terms,
  1 AS IsPlan,
  'S1S4_UAT' AS TermsofTrade,
  0 AS IsBackwash,
  @tenant_id AS tenant_id
FROM (
  SELECT 1 idx, 'PO-UAT-20260604-01' po, 'PT-UAT-20260604-01' pt_receiver UNION ALL
  SELECT 2, 'PO-UAT-20260604-02', 'PT-UAT-20260604-02' UNION ALL
  SELECT 3, 'PO-UAT-20260604-03', 'PT-UAT-20260604-03' UNION ALL
  SELECT 4, 'PO-UAT-20260604-04', 'PT-UAT-20260604-04' UNION ALL
  SELECT 5, 'PO-UAT-20260604-05', 'PT-UAT-20260604-05'
) p
LEFT JOIN PurOrdMaster m ON m.PurOrd = p.po AND m.tenant_id = @tenant_id
WHERE NOT EXISTS (
  SELECT 1 FROM PurOrdRctMaster x
  WHERE x.RctType = 'pt' AND x.Receiver = p.pt_receiver AND x.tenant_id = @tenant_id
);

-- 4) Receipt details.
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
  606041000 + p.idx * 10 + d.Line AS RecID,
  COALESCE(m.Domain, '100') AS Domain,
  'rc' AS RctType,
  p.rc_receiver AS Receiver,
  d.Line AS Line,
  d.ItemNum,
  COALESCE(d.Dimension1, '') AS Dimension1,
  COALESCE(d.Dimension2, '') AS Dimension2,
  p.po AS OrdNbr,
  DATE_ADD('2026-06-04', INTERVAL p.idx - 1 DAY) AS RctDate,
  COALESCE(d.PurCost, 0) AS POCost,
  COALESCE(d.UM, 'EA') AS UM,
  COALESCE(d.QtyOrded, 0) AS QtyOrded,
  ROUND(COALESCE(d.QtyOrded, 0) * 0.90, 5) AS RctQty,
  COALESCE(m.Site, 'AIDOP') AS Site,
  COALESCE(m.Supp, 'VEN00060') AS Supp,
  COALESCE(d.Location, 'QA') AS Location,
  ROUND(COALESCE(d.QtyOrded, 0) * 0.90, 5) AS QtyReceived,
  COALESCE(d.Potype, 'PT') AS Potype,
  0 AS BusinessID,
  'S1S4_UAT_20260604_RQ_V1' AS CreateUser,
  'S1S4_UAT_20260604_RQ_V1' AS UpdateUser,
  NOW() AS CreateTime,
  NOW() AS UpdateTime,
  1 AS IsActive,
  0 AS IsConfirm,
  'C' AS Typed,
  0 AS IsChanged,
  COALESCE(m.TaxClass, '') AS TaxClass,
  COALESCE(m.TaxIn, 0) AS TaxIn,
  1 AS UMConversion,
  COALESCE(m.Curr, '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,
  60604000 + p.idx AS PurOrdRctRecID,
  ROUND(COALESCE(d.QtyOrded, 0) * 0.90, 5) AS QcQty,
  p.rc_receiver AS RctNbr,
  CONCAT('S4 global UAT receipt detail for ', p.po) AS Remark,
  @tenant_id AS tenant_id
FROM (
  SELECT 1 idx, 'PO-UAT-20260604-01' po, 'RC-UAT-20260604-01' rc_receiver UNION ALL
  SELECT 2, 'PO-UAT-20260604-02', 'RC-UAT-20260604-02' UNION ALL
  SELECT 3, 'PO-UAT-20260604-03', 'RC-UAT-20260604-03' UNION ALL
  SELECT 4, 'PO-UAT-20260604-04', 'RC-UAT-20260604-04' UNION ALL
  SELECT 5, 'PO-UAT-20260604-05', 'RC-UAT-20260604-05'
) p
LEFT JOIN PurOrdMaster m ON m.PurOrd = p.po AND m.tenant_id = @tenant_id
JOIN (
  SELECT PurOrd, Line, MIN(ItemNum) ItemNum, MIN(Dimension1) Dimension1, MIN(Dimension2) Dimension2,
         MAX(QtyOrded) QtyOrded, MIN(UM) UM, MIN(Location) Location, MAX(PurCost) PurCost, MIN(Potype) 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.po
WHERE NOT EXISTS (
  SELECT 1 FROM PurOrdRctDetail x
  WHERE x.RctType = 'rc' AND x.Receiver = p.rc_receiver AND x.Line = d.Line AND x.tenant_id = @tenant_id
);

-- 5) Return details: line 1 quality issue, line 2 quantity discrepancy.
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, QC, QCDescr, IsChanged, TaxClass, TaxIn, UMConversion,
  Curr, IsClosed, OrdLine, IsRounding, BlanketLine, ProjectLine,
  RctNbr, RctLine, QtyReturn, PurOrdRctRecID, QcQty, Remark, tenant_id
)
SELECT
  606042000 + p.idx * 10 + d.Line AS RecID,
  COALESCE(m.Domain, '100') AS Domain,
  'pt' AS RctType,
  p.pt_receiver AS Receiver,
  d.Line AS Line,
  d.ItemNum,
  COALESCE(d.Dimension1, '') AS Dimension1,
  COALESCE(d.Dimension2, '') AS Dimension2,
  p.po AS OrdNbr,
  DATE_ADD('2026-06-09', INTERVAL p.idx - 1 DAY) AS RctDate,
  COALESCE(d.PurCost, 0) AS POCost,
  COALESCE(d.UM, 'EA') AS UM,
  COALESCE(d.QtyOrded, 0) AS QtyOrded,
  GREATEST(1, ROUND(COALESCE(d.QtyOrded, 0) * CASE WHEN d.Line = 1 THEN 0.03 ELSE 0.02 END, 5)) AS RctQty,
  COALESCE(m.Site, 'AIDOP') AS Site,
  COALESCE(m.Supp, 'VEN00060') AS Supp,
  COALESCE(d.Location, 'QA') AS Location,
  0 AS QtyReceived,
  COALESCE(d.Potype, 'PT') AS Potype,
  0 AS BusinessID,
  'S1S4_UAT_20260604_RQ_V1' AS CreateUser,
  'S1S4_UAT_20260604_RQ_V1' AS UpdateUser,
  NOW() AS CreateTime,
  NOW() AS UpdateTime,
  1 AS IsActive,
  0 AS IsConfirm,
  'C' AS Typed,
  CASE WHEN d.Line = 1 THEN 'QUALITY' ELSE 'QTY' END AS QC,
  CASE WHEN d.Line = 1 THEN 'Quality nonconformance' ELSE 'Quantity discrepancy' END AS QCDescr,
  0 AS IsChanged,
  COALESCE(m.TaxClass, '') AS TaxClass,
  COALESCE(m.TaxIn, 0) AS TaxIn,
  1 AS UMConversion,
  COALESCE(m.Curr, 'CNY') AS Curr,
  0 AS IsClosed,
  d.Line AS OrdLine,
  0 AS IsRounding,
  0 AS BlanketLine,
  0 AS ProjectLine,
  p.rc_receiver AS RctNbr,
  d.Line AS RctLine,
  GREATEST(1, ROUND(COALESCE(d.QtyOrded, 0) * CASE WHEN d.Line = 1 THEN 0.03 ELSE 0.02 END, 5)) AS QtyReturn,
  60604050 + p.idx AS PurOrdRctRecID,
  GREATEST(1, ROUND(COALESCE(d.QtyOrded, 0) * CASE WHEN d.Line = 1 THEN 0.03 ELSE 0.02 END, 5)) AS QcQty,
  CONCAT(CASE WHEN d.Line = 1 THEN 'Quality nonconformance for ' ELSE 'Quantity discrepancy for ' END, p.po) AS Remark,
  @tenant_id AS tenant_id
FROM (
  SELECT 1 idx, 'PO-UAT-20260604-01' po, 'RC-UAT-20260604-01' rc_receiver, 'PT-UAT-20260604-01' pt_receiver UNION ALL
  SELECT 2, 'PO-UAT-20260604-02', 'RC-UAT-20260604-02', 'PT-UAT-20260604-02' UNION ALL
  SELECT 3, 'PO-UAT-20260604-03', 'RC-UAT-20260604-03', 'PT-UAT-20260604-03' UNION ALL
  SELECT 4, 'PO-UAT-20260604-04', 'RC-UAT-20260604-04', 'PT-UAT-20260604-04' UNION ALL
  SELECT 5, 'PO-UAT-20260604-05', 'RC-UAT-20260604-05', 'PT-UAT-20260604-05'
) p
LEFT JOIN PurOrdMaster m ON m.PurOrd = p.po AND m.tenant_id = @tenant_id
JOIN (
  SELECT PurOrd, Line, MIN(ItemNum) ItemNum, MIN(Dimension1) Dimension1, MIN(Dimension2) Dimension2,
         MAX(QtyOrded) QtyOrded, MIN(UM) UM, MIN(Location) Location, MAX(PurCost) PurCost, MIN(Potype) 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.po
WHERE NOT EXISTS (
  SELECT 1 FROM PurOrdRctDetail x
  WHERE x.RctType = 'pt' AND x.Receiver = p.pt_receiver AND x.Line = d.Line AND x.tenant_id = @tenant_id
);

-- 6) IQC return samples.
INSERT INTO qms_qcp_inspecapplyn
(
  id, FBILLNO, FBILLSTATUS, FCREATETIME, FMODIFYTIME, FAUDITDATE,
  FAPPLYUSER, FAPPLYTIME, FBILLTYPE, FCOMMENT, FINTERFACEID
)
SELECT
  910606040100 + p.idx AS id,
  p.iqc_bill AS FBILLNO,
  'C' AS FBILLSTATUS,
  NOW() AS FCREATETIME,
  NOW() AS FMODIFYTIME,
  NOW() AS FAUDITDATE,
  'S1S4_UAT_20260604_RQ_V1' AS FAPPLYUSER,
  DATE_ADD('2026-06-09 09:00:00', INTERVAL p.idx - 1 DAY) AS FAPPLYTIME,
  'IQC_RETURN' AS FBILLTYPE,
  CONCAT('S4 global UAT IQC return for ', p.po) AS FCOMMENT,
  'S1S4_UAT_20260604_RQ_V1' AS FINTERFACEID
FROM (
  SELECT 1 idx, 'PO-UAT-20260604-01' po, 'IQC-UAT-20260604-01' iqc_bill UNION ALL
  SELECT 2, 'PO-UAT-20260604-02', 'IQC-UAT-20260604-02' UNION ALL
  SELECT 3, 'PO-UAT-20260604-03', 'IQC-UAT-20260604-03' UNION ALL
  SELECT 4, 'PO-UAT-20260604-04', 'IQC-UAT-20260604-04' UNION ALL
  SELECT 5, 'PO-UAT-20260604-05', 'IQC-UAT-20260604-05'
) p
WHERE NOT EXISTS (SELECT 1 FROM qms_qcp_inspecapplyn x WHERE x.FBILLNO = p.iqc_bill);

INSERT INTO qms_qcp_insappnentry
(
  id, glid, FSEQ, FUNIT, FLOTNUMBER, FSRCORDERTYPE, FSRCORDERNUM,
  FAPPLYQTY, FJOINQTY, FINSPECTSTATUS, FBASEQTY, FMATERIALCFG,
  wlmc, ggxh, FSCSYSTEM, FORDERNUM, FSUPPLIER, FORDERTYPE,
  FRESULTSTATUS, shdh, gysbm, gysmc, wllb
)
SELECT
  910606040200 + p.idx AS id,
  910606040100 + p.idx AS glid,
  1 AS FSEQ,
  COALESCE(d.UM, 'EA') AS FUNIT,
  CONCAT('LOT-UAT-', LPAD(p.idx, 2, '0')) AS FLOTNUMBER,
  'PO' AS FSRCORDERTYPE,
  p.po AS FSRCORDERNUM,
  GREATEST(1, ROUND(COALESCE(d.QtyOrded, 0) * 0.90, 5)) AS FAPPLYQTY,
  GREATEST(1, ROUND(COALESCE(d.QtyOrded, 0) * 0.90, 5)) AS FJOINQTY,
  '退货' AS FINSPECTSTATUS,
  GREATEST(1, ROUND(COALESCE(d.QtyOrded, 0) * 0.90, 5)) AS FBASEQTY,
  d.ItemNum AS FMATERIALCFG,
  COALESCE(i.Descr, d.ItemNum) AS wlmc,
  COALESCE(i.Descr1, '') AS ggxh,
  'AIDOP' AS FSCSYSTEM,
  COALESCE(m.SalesOrd, '') AS FORDERNUM,
  COALESCE(m.Supp, 'VEN00060') AS FSUPPLIER,
  'PO' AS FORDERTYPE,
  'NG' AS FRESULTSTATUS,
  p.shddh AS shdh,
  COALESCE(m.Supp, 'VEN00060') AS gysbm,
  COALESCE(NULLIF(ds.supplier, ''), COALESCE(m.Supp, 'VEN00060')) AS gysmc,
  'UAT' AS wllb
FROM (
  SELECT 1 idx, 'PO-UAT-20260604-01' po, 'SH-UAT-20260604-01' shddh UNION ALL
  SELECT 2, 'PO-UAT-20260604-02', 'SH-UAT-20260604-02' UNION ALL
  SELECT 3, 'PO-UAT-20260604-03', 'SH-UAT-20260604-03' UNION ALL
  SELECT 4, 'PO-UAT-20260604-04', 'SH-UAT-20260604-04' UNION ALL
  SELECT 5, 'PO-UAT-20260604-05', 'SH-UAT-20260604-05'
) p
LEFT JOIN PurOrdMaster m ON m.PurOrd = p.po AND m.tenant_id = @tenant_id
JOIN (
  SELECT PurOrd, MIN(ItemNum) ItemNum, MAX(QtyOrded) QtyOrded, MIN(UM) UM
  FROM PurOrdDetail
  WHERE tenant_id = @tenant_id AND PurOrd LIKE 'PO-UAT-20260604-%' AND Line = 1
  GROUP BY PurOrd
) d ON d.PurOrd = p.po
LEFT JOIN (
  SELECT ItemNum, MIN(Descr) AS Descr, MIN(Descr1) AS Descr1
  FROM ItemMaster
  GROUP BY ItemNum
) i ON i.ItemNum = d.ItemNum
LEFT JOIN srm_polist_ds ds ON ds.ponumber = p.po AND ds.poline = 1 AND ds.tenant_id = @tenant_id
WHERE NOT EXISTS (
  SELECT 1 FROM qms_qcp_insappnentry x
  WHERE x.FSRCORDERNUM = p.po AND x.shdh = p.shddh
);

INSERT INTO qms_qcp_inspbill
(
  id, lydjbh, FBILLNO, FBILLSTATUS, FCREATETIME, FMODIFYTIME, FAUDITDATE,
  FINSPESTARTDATE, FINSPEENDDATE, FINSPECTORID, FBILLTYPE, FCOMMENT,
  FMATERIALCFG, wlmc, ggxh, FRINSQTY, dhsl, lysl, bhgsl,
  clfs, pch, gysmc, thyy, UnqualifiedDescription, SampleQty, TestQty
)
SELECT
  910606040300 + p.idx AS id,
  p.iqc_bill AS lydjbh,
  CONCAT('INSP-UAT-20260604-', LPAD(p.idx, 2, '0')) AS FBILLNO,
  'C' AS FBILLSTATUS,
  NOW() AS FCREATETIME,
  NOW() AS FMODIFYTIME,
  NOW() AS FAUDITDATE,
  DATE_ADD('2026-06-09 10:00:00', INTERVAL p.idx - 1 DAY) AS FINSPESTARTDATE,
  DATE_ADD('2026-06-09 11:00:00', INTERVAL p.idx - 1 DAY) AS FINSPEENDDATE,
  'AIDOP-UAT' AS FINSPECTORID,
  'IQC_RETURN' AS FBILLTYPE,
  CONCAT('S4 global UAT IQC result for ', p.po) AS FCOMMENT,
  d.ItemNum AS FMATERIALCFG,
  COALESCE(i.Descr, d.ItemNum) AS wlmc,
  COALESCE(i.Descr1, '') AS ggxh,
  GREATEST(1, ROUND(COALESCE(d.QtyOrded, 0) * 0.90, 5)) AS FRINSQTY,
  GREATEST(1, ROUND(COALESCE(d.QtyOrded, 0) * 0.90, 5)) AS dhsl,
  GREATEST(1, ROUND(COALESCE(d.QtyOrded, 0) * 0.90, 5)) AS lysl,
  GREATEST(1, ROUND(COALESCE(d.QtyOrded, 0) * CASE WHEN p.idx <= 3 THEN 0.03 ELSE 0.02 END, 5)) AS bhgsl,
  3 AS clfs,
  CONCAT('LOT-UAT-', LPAD(p.idx, 2, '0')) AS pch,
  COALESCE(NULLIF(ds.supplier, ''), COALESCE(m.Supp, 'VEN00060')) AS gysmc,
  CASE WHEN p.idx <= 3 THEN 'Quality nonconformance' ELSE 'Quantity discrepancy' END AS thyy,
  CASE WHEN p.idx <= 3 THEN 'Quality nonconformance' ELSE 'Quantity discrepancy' END AS UnqualifiedDescription,
  GREATEST(1, ROUND(COALESCE(d.QtyOrded, 0) * 0.10, 5)) AS SampleQty,
  GREATEST(1, ROUND(COALESCE(d.QtyOrded, 0) * 0.90, 5)) AS TestQty
FROM (
  SELECT 1 idx, 'PO-UAT-20260604-01' po, 'IQC-UAT-20260604-01' iqc_bill UNION ALL
  SELECT 2, 'PO-UAT-20260604-02', 'IQC-UAT-20260604-02' UNION ALL
  SELECT 3, 'PO-UAT-20260604-03', 'IQC-UAT-20260604-03' UNION ALL
  SELECT 4, 'PO-UAT-20260604-04', 'IQC-UAT-20260604-04' UNION ALL
  SELECT 5, 'PO-UAT-20260604-05', 'IQC-UAT-20260604-05'
) p
LEFT JOIN PurOrdMaster m ON m.PurOrd = p.po AND m.tenant_id = @tenant_id
JOIN (
  SELECT PurOrd, MIN(ItemNum) ItemNum, MAX(QtyOrded) QtyOrded
  FROM PurOrdDetail
  WHERE tenant_id = @tenant_id AND PurOrd LIKE 'PO-UAT-20260604-%' AND Line = 1
  GROUP BY PurOrd
) d ON d.PurOrd = p.po
LEFT JOIN (
  SELECT ItemNum, MIN(Descr) AS Descr, MIN(Descr1) AS Descr1
  FROM ItemMaster
  GROUP BY ItemNum
) i ON i.ItemNum = d.ItemNum
LEFT JOIN srm_polist_ds ds ON ds.ponumber = p.po AND ds.poline = 1 AND ds.tenant_id = @tenant_id
WHERE NOT EXISTS (SELECT 1 FROM qms_qcp_inspbill x WHERE x.lydjbh = p.iqc_bill);

-- 7) Supplier shortage kanban samples.
INSERT INTO WorkOrdDetailTotalKB
(
  RowNum, Descr, Descr1, ItemNum, supplier_number, supplier_name,
  D0, D1, D2, D3, D4, D5, D6, D7, D8, D9, D10, D11, D12, D13, D14
)
SELECT
  CAST(2026060400 + p.idx AS CHAR) AS RowNum,
  COALESCE(i.Descr, d.ItemNum) AS Descr,
  COALESCE(i.Descr1, CONCAT('WO ', COALESCE(m.WorkOrd, ''))) AS Descr1,
  d.ItemNum,
  COALESCE(m.Supp, 'VEN00060') AS supplier_number,
  COALESCE(NULLIF(ds.supplier, ''), COALESCE(m.Supp, 'VEN00060')) AS supplier_name,
  GREATEST(1, ROUND(COALESCE(d.QtyOrded, 0) * 0.10, 5)) AS D0,
  GREATEST(1, ROUND(COALESCE(d.QtyOrded, 0) * 0.08, 5)) AS D1,
  GREATEST(1, ROUND(COALESCE(d.QtyOrded, 0) * 0.06, 5)) AS D2,
  GREATEST(1, ROUND(COALESCE(d.QtyOrded, 0) * 0.04, 5)) AS D3,
  GREATEST(1, ROUND(COALESCE(d.QtyOrded, 0) * 0.02, 5)) AS D4,
  0, 0, 0, 0, 0, 0, 0, 0, 0, 0
FROM (
  SELECT 1 idx, 'PO-UAT-20260604-01' po UNION ALL
  SELECT 2, 'PO-UAT-20260604-02' UNION ALL
  SELECT 3, 'PO-UAT-20260604-03' UNION ALL
  SELECT 4, 'PO-UAT-20260604-04' UNION ALL
  SELECT 5, 'PO-UAT-20260604-05'
) p
LEFT JOIN PurOrdMaster m ON m.PurOrd = p.po AND m.tenant_id = @tenant_id
JOIN (
  SELECT PurOrd, MIN(ItemNum) ItemNum, MAX(QtyOrded) QtyOrded
  FROM PurOrdDetail
  WHERE tenant_id = @tenant_id AND PurOrd LIKE 'PO-UAT-20260604-%' AND Line = 1
  GROUP BY PurOrd
) d ON d.PurOrd = p.po
LEFT JOIN (
  SELECT ItemNum, MIN(Descr) AS Descr, MIN(Descr1) AS Descr1
  FROM ItemMaster
  GROUP BY ItemNum
) i ON i.ItemNum = d.ItemNum
LEFT JOIN srm_polist_ds ds ON ds.ponumber = p.po AND ds.poline = 1 AND ds.tenant_id = @tenant_id
WHERE NOT EXISTS (
  SELECT 1 FROM WorkOrdDetailTotalKB x WHERE x.RowNum = CAST(2026060400 + p.idx AS CHAR)
);