WMS/sql/20260718_cancel_xb_inbound_task_verify.sql
2026-07-18 13:41:54 +08:00

74 lines
2.8 KiB
SQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

-- 线边库零库存占用库位:取消入库任务
-- 本事项没有数据库结构或初始化数据变更。
-- 本脚本仅用于上线前后只读核查,不会修改任何数据。
-- 请在线边库 default 数据库执行。
-- 1. 统计允许进入取消校验的候选任务。
SELECT
COUNT(*) AS TaskCount,
SUM(CASE WHEN `Status` = 0 THEN 1 ELSE 0 END) AS NewCount,
SUM(CASE WHEN `Status` = 1 THEN 1 ELSE 0 END) AS PendingCount,
SUM(CASE WHEN `Status` = 5 THEN 1 ELSE 0 END) AS AssignedCount
FROM `TaskHeader`
WHERE `ZoneId` = 1027
AND `RoadWay` = 1
AND `TaskType` = 1
AND `Status` IN (0, 1, 5);
-- 2. 列出状态不符合自动取消条件的任务,结果应为空。
SELECT
t.`ID`,
t.`TaskNo`,
t.`Status`,
t.`ContainerCode`,
t.`ToLocation`,
(SELECT COUNT(*) FROM `TaskDetail` d WHERE d.`HeaderId` = t.`ID`) AS DetailCount,
(SELECT COUNT(*) FROM `Inventory` i WHERE i.`ContainerCode` = t.`ContainerCode`) AS InventoryCount,
c.`IsLock` AS ContainerLock,
c.`Status` AS ContainerStatus,
c.`LocationCode` AS ContainerLocation,
l.`Status` AS LocationLock,
l.`ContainerStatus` AS LocationContainerStatus,
l.`ContainerCode` AS LocationContainer
FROM `TaskHeader` t
LEFT JOIN `Container` c ON c.`Code` = t.`ContainerCode`
LEFT JOIN `Location` l ON l.`Code` = t.`ToLocation`
WHERE t.`ZoneId` = 1027
AND t.`RoadWay` = 1
AND t.`TaskType` = 1
AND t.`Status` IN (0, 1, 5)
AND (
(SELECT COUNT(*) FROM `TaskDetail` d WHERE d.`HeaderId` = t.`ID`) <> 0
OR EXISTS (
SELECT 1
FROM `Inventory` i
WHERE i.`ContainerCode` = t.`ContainerCode`
AND (
i.`ZoneId` <> t.`ZoneId`
OR UPPER(COALESCE(i.`LocationCode`, '')) <> UPPER(COALESCE(t.`ToLocation`, ''))
OR i.`TaskStatus` <> 1
OR COALESCE(i.`Qty`, 0) <> 0
)
)
OR c.`IsLock` <> 1
OR UPPER(COALESCE(c.`LocationCode`, '')) <> UPPER(COALESCE(t.`ToLocation`, ''))
OR c.`Status` <> CASE
WHEN EXISTS (SELECT 1 FROM `Inventory` i WHERE i.`ContainerCode` = t.`ContainerCode`)
THEN 'some' ELSE 'empty' END
OR l.`Status` <> 1
OR UPPER(COALESCE(l.`ContainerCode`, '')) <> UPPER(COALESCE(t.`ContainerCode`, ''))
OR l.`ContainerStatus` <> CASE
WHEN EXISTS (SELECT 1 FROM `Inventory` i WHERE i.`ContainerCode` = t.`ContainerCode`)
THEN 'some' ELSE 'empty' END
)
ORDER BY t.`ID`;
-- 3. 取消单条任务后核查。执行前替换任务号。
SET @TaskNo = '请替换为实际任务号';
SELECT COUNT(*) AS RemainingTaskCount
FROM `TaskHeader`
WHERE `TaskNo` = @TaskNo;
-- 成功取消后应同时人工核对:原目标库位未锁且无托盘号,原托盘未锁且LocationCode为空。