113 lines
6.8 KiB
Transact-SQL
113 lines
6.8 KiB
Transact-SQL
ALTER PROCEDURE dbo.proc_allot_space @v_tray_code varchar(80),@v_code varchar(80),@v_result int OUTPUT,@v_resultmsg varchar(255) OUTPUT AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
IF NOT EXISTS(SELECT 1 FROM mixed_pilot.control WHERE enabled=1) AND NOT EXISTS(SELECT 1 FROM mixed_pilot.lane WHERE protected=1)
|
|
BEGIN
|
|
EXEC mixed_pilot.original_allot_space @v_tray_code,@v_code,@v_result OUTPUT,@v_resultmsg OUTPUT;
|
|
RETURN;
|
|
END;
|
|
SET XACT_ABORT OFF;
|
|
DECLARE @own bit=CASE WHEN @@TRANCOUNT=0 THEN 1 ELSE 0 END,@lock int,@dummy int,@job varchar(255),@slot varchar(80);
|
|
IF @own=1 BEGIN TRANSACTION; ELSE SAVE TRANSACTION mp_inbound;
|
|
BEGIN TRY
|
|
EXEC @lock=sys.sp_getapplock @Resource='FT546.mixed_pilot.inbound',@LockMode='Exclusive',@LockOwner='Transaction',@LockTimeout=500;
|
|
IF @lock<0 THROW 51120,N'试点分配正在处理，请稍后重试。',1;
|
|
SELECT @dummy=COUNT(*) FROM dbo.bas_cargo_space s WITH(UPDLOCK,HOLDLOCK)
|
|
JOIN mixed_pilot.lane l ON l.warehouse_id=s.warehouse_id AND l.lane_code=s.group_coding_no WHERE l.enabled=1 OR l.protected=1;
|
|
SELECT @dummy=COUNT(*) FROM dbo.mydo_job WITH(UPDLOCK,HOLDLOCK) WHERE tray_code=@v_tray_code;
|
|
IF @dummy<>1 THROW 51121,N'托盘任务不存在或不唯一，请核对后重试。',1;
|
|
SELECT @job=job_id,@slot=NULLIF(cargo_space_id,'') FROM dbo.mydo_job WHERE tray_code=@v_tray_code AND in_or_out<20;
|
|
IF @job IS NULL THROW 51122,N'没有对应的入库任务。',1;
|
|
IF @slot IS NOT NULL
|
|
BEGIN
|
|
IF NOT EXISTS(SELECT 1 FROM dbo.bas_cargo_space WHERE cargo_space_id=@slot AND status='3')
|
|
THROW 51123,N'已有入库分配的货位状态不一致，请核对任务。',1;
|
|
SET @v_result=10; SET @v_resultmsg='成功';
|
|
END
|
|
ELSE EXEC mixed_pilot.allot_space_with_fallback @v_tray_code,@v_code,@v_result OUTPUT,@v_resultmsg OUTPUT;
|
|
IF @own=1 COMMIT TRANSACTION;
|
|
END TRY
|
|
BEGIN CATCH
|
|
IF @own=1 AND XACT_STATE()<>0 ROLLBACK TRANSACTION;
|
|
ELSE IF XACT_STATE()=1 ROLLBACK TRANSACTION mp_inbound;
|
|
ELSE IF XACT_STATE()=-1 THROW;
|
|
SET @v_result=2; SET @v_resultmsg=CONVERT(varchar(255),ERROR_MESSAGE());
|
|
END CATCH;
|
|
END;
|
|
GO
|
|
ALTER PROCEDURE dbo.proc_yk_job_add @v_cargo_space_id varchar(255),@v_tar_cargo_space_id varchar(255),@v_tray_code varchar(255) AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
IF NOT EXISTS(SELECT 1 FROM mixed_pilot.lane WHERE enabled=1 OR protected=1)
|
|
BEGIN
|
|
EXEC mixed_pilot.original_yk_job_add @v_cargo_space_id,@v_tar_cargo_space_id,@v_tray_code;
|
|
RETURN;
|
|
END;
|
|
DECLARE @own bit=CASE WHEN @@TRANCOUNT=0 THEN 1 ELSE 0 END,@job varchar(255),@dummy int;
|
|
IF @own=1 BEGIN TRANSACTION; ELSE SAVE TRANSACTION mp_move;
|
|
BEGIN TRY
|
|
SELECT @dummy=COUNT(*) FROM dbo.bas_cargo_space WITH(UPDLOCK,HOLDLOCK) WHERE cargo_space_id IN(@v_cargo_space_id,@v_tar_cargo_space_id);
|
|
IF NOT EXISTS(SELECT 1 FROM dbo.bas_cargo_space s JOIN mixed_pilot.lane l ON l.warehouse_id=s.warehouse_id AND l.lane_code=s.group_coding_no
|
|
WHERE s.cargo_space_id IN(@v_cargo_space_id,@v_tar_cargo_space_id) AND (l.protected=1 OR l.enabled=1))
|
|
AND NOT EXISTS(SELECT 1 FROM mixed_pilot.move_audit WHERE target_slot=@v_tar_cargo_space_id)
|
|
BEGIN
|
|
EXEC mixed_pilot.original_yk_job_add @v_cargo_space_id,@v_tar_cargo_space_id,@v_tray_code;
|
|
IF @own=1 COMMIT TRANSACTION;
|
|
RETURN;
|
|
END;
|
|
IF EXISTS(SELECT 1 FROM mixed_pilot.move_audit a JOIN dbo.mydo_job j ON j.job_id=a.job_id
|
|
WHERE a.source_slot=@v_cargo_space_id AND a.target_slot=@v_tar_cargo_space_id AND a.tray_code=@v_tray_code AND a.completed_at IS NULL
|
|
AND j.in_or_out=30 AND j.cargo_space_id=a.source_slot AND j.target_cargo_space_id=a.target_slot AND j.tray_code=a.tray_code)
|
|
BEGIN
|
|
IF @own=1 COMMIT TRANSACTION;
|
|
RETURN;
|
|
END;
|
|
EXEC mixed_pilot.check_move @v_cargo_space_id,@v_tar_cargo_space_id,@v_tray_code;
|
|
EXEC mixed_pilot.original_yk_job_add @v_cargo_space_id,@v_tar_cargo_space_id,@v_tray_code;
|
|
SELECT @job=job_id FROM dbo.mydo_job WHERE in_or_out=30 AND cargo_space_id=@v_cargo_space_id AND target_cargo_space_id=@v_tar_cargo_space_id AND tray_code=@v_tray_code;
|
|
IF @job IS NULL THROW 51124,N'未能建立可核验的移库任务。',1;
|
|
INSERT mixed_pilot.move_audit(job_id,source_slot,target_slot,tray_code) VALUES(@job,@v_cargo_space_id,@v_tar_cargo_space_id,@v_tray_code);
|
|
IF @own=1 COMMIT TRANSACTION;
|
|
END TRY
|
|
BEGIN CATCH
|
|
IF @own=1 AND XACT_STATE()<>0 ROLLBACK TRANSACTION;
|
|
ELSE IF XACT_STATE()=1 ROLLBACK TRANSACTION mp_move;
|
|
THROW;
|
|
END CATCH;
|
|
END;
|
|
GO
|
|
ALTER PROCEDURE dbo.proc_yk_complete @v_job_id varchar(255),@v_result int OUTPUT AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
IF NOT EXISTS(SELECT 1 FROM mixed_pilot.move_audit WHERE job_id=@v_job_id)
|
|
BEGIN
|
|
EXEC mixed_pilot.original_yk_complete @v_job_id,@v_result OUTPUT;
|
|
RETURN;
|
|
END;
|
|
DECLARE @own bit=CASE WHEN @@TRANCOUNT=0 THEN 1 ELSE 0 END,@src varchar(255),@dst varchar(255),@tray varchar(255),@done datetime2,@dummy int;
|
|
IF @own=1 BEGIN TRANSACTION; ELSE SAVE TRANSACTION mp_complete;
|
|
BEGIN TRY
|
|
SELECT @src=source_slot,@dst=target_slot,@tray=tray_code,@done=completed_at FROM mixed_pilot.move_audit WITH(UPDLOCK,HOLDLOCK) WHERE job_id=@v_job_id;
|
|
IF @done IS NULL
|
|
BEGIN
|
|
SELECT @dummy=COUNT(*) FROM dbo.bas_cargo_space WITH(UPDLOCK,HOLDLOCK) WHERE cargo_space_id IN(@src,@dst);
|
|
IF NOT EXISTS(SELECT 1 FROM dbo.mydo_job WITH(UPDLOCK,HOLDLOCK) WHERE job_id=@v_job_id AND in_or_out=30 AND tray_code=@tray AND cargo_space_id=@src AND target_cargo_space_id=@dst)
|
|
OR NOT EXISTS(SELECT 1 FROM dbo.bas_cargo_space WHERE cargo_space_id=@src AND status='4' AND tray_code=@tray)
|
|
OR NOT EXISTS(SELECT 1 FROM dbo.bas_cargo_space WHERE cargo_space_id=@dst AND status='3' AND NULLIF(tray_code,'') IS NULL)
|
|
OR EXISTS(SELECT 1 FROM dbo.bas_stock WHERE cargo_space_id=@dst)
|
|
OR NOT EXISTS(SELECT 1 FROM dbo.bas_stock WHERE cargo_space_id=@src AND tray_code=@tray)
|
|
OR EXISTS(SELECT 1 FROM dbo.bas_stock WHERE cargo_space_id=@src AND (tray_code<>@tray OR tray_code IS NULL))
|
|
THROW 51125,N'移库完成的起终点或库存不一致，未修改库存。',1;
|
|
EXEC mixed_pilot.original_yk_complete @v_job_id,@v_result OUTPUT;
|
|
UPDATE mixed_pilot.move_audit SET completed_at=SYSUTCDATETIME() WHERE job_id=@v_job_id;
|
|
END;
|
|
SET @v_result=10;
|
|
IF @own=1 COMMIT TRANSACTION;
|
|
END TRY
|
|
BEGIN CATCH
|
|
IF @own=1 AND XACT_STATE()<>0 ROLLBACK TRANSACTION;
|
|
ELSE IF XACT_STATE()=1 ROLLBACK TRANSACTION mp_complete;
|
|
THROW;
|
|
END CATCH;
|
|
END;
|