23 lines
2.4 KiB
SQL
23 lines
2.4 KiB
SQL
-- 退货单 SKU 按件数出库快照。目标库：baiyue_lastleg。可重复执行。
|
|
USE baiyue_lastleg;
|
|
SET @return_sku_quantity_ddl = IF(
|
|
EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE()
|
|
AND table_name='ll_outbound_pallet_item' AND column_name='return_sku_quantity'),
|
|
'SELECT 1',
|
|
'ALTER TABLE ll_outbound_pallet_item ADD COLUMN return_sku_quantity BIGINT NULL COMMENT ''退货单SKU本来源计划出库件数快照，NULL为历史整箱出库'' AFTER sku_code'
|
|
);
|
|
PREPARE return_sku_quantity_stmt FROM @return_sku_quantity_ddl;
|
|
EXECUTE return_sku_quantity_stmt;
|
|
DEALLOCATE PREPARE return_sku_quantity_stmt;
|
|
|
|
-- 部分出库仅扣件数；整箱扣一箱，保留守恒和非负约束。
|
|
SET @return_sku_check_ddl = IF(EXISTS(SELECT 1 FROM information_schema.table_constraints WHERE constraint_schema=DATABASE() AND table_name='ll_return_inventory_outbound_movement' AND constraint_name='chk_ll_return_outbound_movement_delta'), 'ALTER TABLE ll_return_inventory_outbound_movement DROP CHECK chk_ll_return_outbound_movement_delta, ADD CONSTRAINT chk_ll_return_outbound_movement_delta CHECK(quantity>0 AND total_after>=0 AND available_after>=0 AND total_before-total_after=quantity AND available_before-available_after=quantity AND cartons_after>=0 AND cartons_before-cartons_after IN (0,1) AND total_after>=available_after)', 'ALTER TABLE ll_return_inventory_outbound_movement ADD CONSTRAINT chk_ll_return_outbound_movement_delta CHECK(quantity>0 AND total_after>=0 AND available_after>=0 AND total_before-total_after=quantity AND available_before-available_after=quantity AND cartons_after>=0 AND cartons_before-cartons_after IN (0,1) AND total_after>=available_after)');
|
|
PREPARE return_sku_check_stmt FROM @return_sku_check_ddl;
|
|
EXECUTE return_sku_check_stmt;
|
|
DEALLOCATE PREPARE return_sku_check_stmt;
|
|
-- 同箱余数允许再次出库，防重仍由拣货ID+SKU唯一索引保证。
|
|
SET @return_sku_index_ddl = IF(EXISTS(SELECT 1 FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='ll_return_inventory_outbound_movement' AND index_name='uk_ll_return_outbound_carton_sku'), 'ALTER TABLE ll_return_inventory_outbound_movement DROP INDEX uk_ll_return_outbound_carton_sku, ADD INDEX idx_ll_return_outbound_carton_sku(customer_id,carton_id,sku_code)', 'SELECT 1');
|
|
PREPARE return_sku_index_stmt FROM @return_sku_index_ddl;
|
|
EXECUTE return_sku_index_stmt;
|
|
DEALLOCATE PREPARE return_sku_index_stmt;
|