AiDOPWarehouse/server/Admin.NET.Web.Entry/UpdateScripts/1.0.564.verify.sql

-- 1.0.564.verify.sql — 每条 SELECT 必须返回真值。

-- 库位角色表已建出
SELECT COUNT(*) = 1
  FROM information_schema.tables
 WHERE table_schema = DATABASE()
   AND table_name = 'mdp_location_role';

-- 隔离表已补原因列
SELECT COUNT(*) = 1
  FROM information_schema.columns
 WHERE table_schema = DATABASE()
   AND table_name = 'mdp_std_inv_trans_unmapped'
   AND column_name = 'unmapped_reason';

-- 角色取值只允许中立枚举
SELECT COUNT(*) = 0
  FROM mdp_location_role
 WHERE location_role NOT IN ('INSPECT','MATERIAL','SEMI','LINE','FG','SCRAP','OTHER','UNKNOWN');

-- 待检仓口径到位（无 1000 库位时也算通过）
SELECT (SELECT COUNT(*) FROM mdp_location_role WHERE location = '1000') = 0
    OR (SELECT COUNT(*) FROM mdp_location_role WHERE location = '1000' AND location_role <> 'INSPECT' AND role_source <> 'MANUAL') = 0;

-- trans_type 只允许 12 个中立阶段码或 NULL
SELECT COUNT(*) = 0
  FROM mdp_std_inv_trans
 WHERE trans_type IS NOT NULL
   AND trans_type NOT IN ('MAT_RECEIPT','MAT_IQC_RELEASE','MAT_PUTAWAY','MAT_PICK','MAT_ISSUE','MAT_LINE','FG_RECEIPT','FG_PUTAWAY','FG_PICK','FG_SHIP','FG_PROD_RECEIPT','FG_FQC_RELEASE');

-- 非阶段码不得残留在隔离表
SELECT COUNT(*) = 0
  FROM mdp_std_inv_trans_unmapped
 WHERE src_trans_type_raw IN ('inv-frz','inv-unfrz');

-- 隔离原因只允许三类
SELECT COUNT(*) = 0
  FROM mdp_std_inv_trans_unmapped
 WHERE unmapped_reason NOT IN ('PENDING_VERIFY','LOCATION_ROLE_UNCONFIGURED','UNKNOWN_CODE');

-- 已映射成功的行不得留在隔离表
SELECT COUNT(*) = 0
  FROM mdp_std_inv_trans_unmapped u
  JOIN mdp_std_inv_trans t
    ON t.tenant_id = u.tenant_id AND t.source_system = u.source_system AND t.src_rec_id = u.src_rec_id
 WHERE t.trans_type IS NOT NULL;