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;