FTC25009/sql/proc_bind_tray_msg_trace.sql

IF OBJECT_ID(N'[dbo].[in_tray_msg]', N'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[in_tray_msg] (
      [id] bigint IDENTITY(1,1) NOT NULL,
      [tray_code] varchar(80) COLLATE Chinese_PRC_CI_AS NOT NULL,
      [stockin_detail_id] varchar(80) COLLATE Chinese_PRC_CI_AS NOT NULL,
      [product_Id] varchar(80) COLLATE Chinese_PRC_CI_AS NULL,
      [quantity] decimal(18,4) NOT NULL,
      [warehouse_id] varchar(80) COLLATE Chinese_PRC_CI_AS NOT NULL,
      [user_id] bigint NULL,
      [dept_id] bigint NULL,
      [create_time] datetime2(7) NULL,
      CONSTRAINT [PK_in_tray_msg] PRIMARY KEY CLUSTERED ([id])
    );
END
GO

IF COL_LENGTH('dbo.in_tray_msg', 'warehouse_id') IS NULL
    ALTER TABLE dbo.in_tray_msg ADD warehouse_id varchar(80) COLLATE Chinese_PRC_CI_AS NULL;
GO
IF COL_LENGTH('dbo.in_tray_msg', 'user_id') IS NULL
    ALTER TABLE dbo.in_tray_msg ADD user_id bigint NULL;
GO
IF COL_LENGTH('dbo.in_tray_msg', 'dept_id') IS NULL
    ALTER TABLE dbo.in_tray_msg ADD dept_id bigint NULL;
GO
IF COL_LENGTH('dbo.in_tray_msg', 'product_Id') IS NULL AND COL_LENGTH('dbo.in_tray_msg', 'product_id') IS NULL
    ALTER TABLE dbo.in_tray_msg ADD product_Id varchar(80) COLLATE Chinese_PRC_CI_AS NULL;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_in_tray_msg_tray_warehouse' AND object_id = OBJECT_ID(N'[dbo].[in_tray_msg]'))
    CREATE INDEX [IX_in_tray_msg_tray_warehouse] ON [dbo].[in_tray_msg] ([tray_code], [warehouse_id]);
GO

IF OBJECT_ID(N'[dbo].[proc_bind_tray_msg]', N'P') IS NOT NULL
    DROP PROCEDURE [dbo].[proc_bind_tray_msg]
GO

CREATE PROCEDURE [dbo].[proc_bind_tray_msg]
    @v_stockin_detail_id varchar(80),
    @v_tray_code varchar(80),
    @v_warehouse_id varchar(80),
    @v_quantity decimal(18,4),
    @v_user_id bigint,
    @v_dept_id bigint,
    @v_result int OUTPUT,
    @v_resultmsg varchar(500) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE
        @v_product_id varchar(80),
        @v_bill_quantity decimal(18,4),
        @v_scan_num decimal(18,4);

    BEGIN TRY
        IF @v_stockin_detail_id IS NULL OR LTRIM(RTRIM(@v_stockin_detail_id)) = ''
        BEGIN
            SET @v_result = 0;
            SET @v_resultmsg = '入库子单ID不能为空';
            RETURN;
        END

        IF @v_tray_code IS NULL OR LTRIM(RTRIM(@v_tray_code)) = ''
        BEGIN
            SET @v_result = 0;
            SET @v_resultmsg = '托盘号不能为空';
            RETURN;
        END

        IF @v_warehouse_id IS NULL OR LTRIM(RTRIM(@v_warehouse_id)) = ''
        BEGIN
            SET @v_result = 0;
            SET @v_resultmsg = '仓库编码不能为空';
            RETURN;
        END

        IF ISNULL(@v_quantity, 0) <= 0
        BEGIN
            SET @v_result = 0;
            SET @v_resultmsg = '绑定数量必须大于0';
            RETURN;
        END

        SELECT TOP 1
            @v_product_id = product_id,
            @v_bill_quantity = ISNULL(quantity, 0),
            @v_scan_num = ISNULL(scan_num, 0)
        FROM dbo.in_stockin_detail WITH (UPDLOCK, HOLDLOCK)
        WHERE stockin_detail_id = @v_stockin_detail_id;

        IF @v_product_id IS NULL
        BEGIN
            SET @v_result = 0;
            SET @v_resultmsg = '入库子单不存在';
            RETURN;
        END

        IF @v_scan_num + @v_quantity > @v_bill_quantity
        BEGIN
            SET @v_result = 0;
            SET @v_resultmsg = '绑定数量超过子单待绑数量';
            RETURN;
        END

        BEGIN TRANSACTION;

        UPDATE dbo.in_stockin_detail
        SET scan_num = ISNULL(scan_num, 0) + @v_quantity
        WHERE stockin_detail_id = @v_stockin_detail_id
          AND ISNULL(scan_num, 0) + @v_quantity <= ISNULL(quantity, 0);

        IF @@ROWCOUNT = 0
        BEGIN
            SET @v_result = 0;
            SET @v_resultmsg = '绑定数量超过子单待绑数量';
            ROLLBACK TRANSACTION;
            RETURN;
        END

        IF EXISTS (
            SELECT 1 FROM dbo.in_tray_msg
            WHERE tray_code = @v_tray_code
              AND warehouse_id = @v_warehouse_id
              AND stockin_detail_id = @v_stockin_detail_id
        )
        BEGIN
            UPDATE dbo.in_tray_msg
            SET quantity = ISNULL(quantity, 0) + @v_quantity,
                product_Id = @v_product_id,
                user_id = @v_user_id,
                dept_id = @v_dept_id
            WHERE tray_code = @v_tray_code
              AND warehouse_id = @v_warehouse_id
              AND stockin_detail_id = @v_stockin_detail_id;
        END
        ELSE
        BEGIN
            INSERT INTO dbo.in_tray_msg (
                tray_code, stockin_detail_id, product_Id, quantity,
                warehouse_id, user_id, dept_id, create_time
            )
            VALUES (
                @v_tray_code, @v_stockin_detail_id, @v_product_id, @v_quantity,
                @v_warehouse_id, @v_user_id, @v_dept_id, GETDATE()
            );
        END

        COMMIT TRANSACTION;

        SET @v_result = 1;
        SET @v_resultmsg = '绑盘成功';
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        SET @v_result = 0;
        SET @v_resultmsg = ERROR_MESSAGE();
    END CATCH
END
GO