34 lines
3.5 KiB
SQL
34 lines
3.5 KiB
SQL
-- 退货托盘签收箱内清点；MySQL 8；仅在目标尾程库执行，可重复运行。
|
|
USE baiyue_lastleg;
|
|
SET @rc_count_ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_return_inventory_carton' AND column_name='count_status'),'SELECT 1','ALTER TABLE ll_return_inventory_carton ADD COLUMN count_status VARCHAR(16) NOT NULL DEFAULT ''CONFIRMED'' COMMENT ''箱内清点状态 PENDING待清点 CONFIRMED已确认''');
|
|
PREPARE rc_count_stmt FROM @rc_count_ddl; EXECUTE rc_count_stmt; DEALLOCATE PREPARE rc_count_stmt;
|
|
SET @rc_count_ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_return_inventory_carton' AND column_name='counted_at'),'SELECT 1','ALTER TABLE ll_return_inventory_carton ADD COLUMN counted_at DATETIME(3) NULL COMMENT ''箱内清点确认时间''');
|
|
PREPARE rc_count_stmt FROM @rc_count_ddl; EXECUTE rc_count_stmt; DEALLOCATE PREPARE rc_count_stmt;
|
|
SET @rc_count_ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_return_inventory_carton' AND column_name='counted_by'),'SELECT 1','ALTER TABLE ll_return_inventory_carton ADD COLUMN counted_by VARCHAR(128) NULL COMMENT ''箱内清点确认操作人''');
|
|
PREPARE rc_count_stmt FROM @rc_count_ddl; EXECUTE rc_count_stmt; DEALLOCATE PREPARE rc_count_stmt;
|
|
SET @rc_count_ddl = IF(EXISTS(SELECT 1 FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='ll_return_inventory_carton' AND index_name='idx_ll_return_count'),'SELECT 1','CREATE INDEX idx_ll_return_count ON ll_return_inventory_carton(overseas_warehouse_id,count_status,status,source_receipt_id)');
|
|
PREPARE rc_count_stmt FROM @rc_count_ddl; EXECUTE rc_count_stmt; DEALLOCATE PREPARE rc_count_stmt;
|
|
-- 历史箱：只迁移整批仍可用、没有换标/合箱占用、未确认清点的托盘签收箱。
|
|
-- 已进入履约或合箱的批次不回退；后续需要逐批人工核实。
|
|
UPDATE ll_return_inventory_carton c
|
|
JOIN (SELECT source_receipt_id FROM (SELECT a.source_receipt_id
|
|
FROM ll_return_inventory_carton a WHERE a.source_receipt_id IS NOT NULL AND a.source_unit_type='PALLET'
|
|
GROUP BY a.source_receipt_id
|
|
HAVING SUM(a.status<>'AVAILABLE' OR a.relabel_application_id IS NOT NULL OR a.relabel_no IS NOT NULL OR a.counted_at IS NOT NULL)=0
|
|
AND SUM(EXISTS(SELECT 1 FROM ll_return_inventory_carton_item i WHERE i.carton_id=a.id AND COALESCE(i.packing_reserved_quantity,0)>0))=0
|
|
AND SUM(EXISTS(SELECT 1 FROM ll_return_packing_trace t WHERE t.source_carton_mark=a.carton_mark OR t.target_carton_mark=a.carton_mark))=0
|
|
) pending) p ON p.source_receipt_id=c.source_receipt_id
|
|
SET c.count_status='PENDING' WHERE c.source_unit_type='PALLET' AND c.counted_at IS NULL;
|
|
|
|
-- 退货箱内清点作业会话：每位操作员只能认领一个批次，每个批次只能被一人认领。
|
|
CREATE TABLE IF NOT EXISTS ll_return_carton_count_session (
|
|
operator_id VARCHAR(128) NOT NULL COMMENT '平台PDA操作员标识',
|
|
warehouse_id VARCHAR(128) NOT NULL COMMENT '认领时绑定海外仓标识',
|
|
receipt_id BIGINT NULL COMMENT '正在清点的签收批次，完成后释放为NULL',
|
|
started_at DATETIME(3) NULL COMMENT '本次开始清点时间',
|
|
created_at DATETIME(3) NOT NULL COMMENT '会话创建时间',
|
|
updated_at DATETIME(3) NOT NULL COMMENT '会话更新时间',
|
|
PRIMARY KEY(operator_id),
|
|
UNIQUE KEY uk_ll_return_count_session_receipt(receipt_id)
|
|
) ENGINE=InnoDB COMMENT='退货箱内清点单人单任务持久会话';
|