过站存储过程

QS_ASSEMBLY

ALTER PROCEDURE [dbo].[SUGA_CHECK_SERIAL_ASYNC]
    @lotId nvarchar(50),
    @stationId nvarchar(50),
    @routeStepId nvarchar(50),
    @userId nvarchar(50),
    @serialNumber nvarchar(50)
AS
BEGIN
    -- 获取工单信息
    declare @productId nvarchar(50)
    select @productId = product_id from QS_LOTS(NOLOCK) where lot_id = @lotId
    if @productId is null or @productId = ''
    begin
        select 1 errorcode,'工单不存在'
        return;
    end

    --获取产品信息
    declare @productName nvarchar(50)
    select @productName = name from QS_PRODUCTS(NOLOCK) where product_id = @productId
    if @productName is null or @productName = ''
    begin
        select 1 errorcode,'工单所属产品已被删除'
        return;
    end

    --判断东莞是否存在
    declare @isExists int
    select @isExists = count(1) from QS_SERIAL_NUMBERS(NOLOCK) where SERIAL_NUMBER = @serialNumber
    if @isExists > 0
    begin
        select 0 errorcode
        return;
    end

    --判断越南是否存在
    declare @productName_VN nvarchar(50)
    declare @serialState_VN nvarchar(50)
    select @productName_VN = model_no,@serialState_VN = SERIAL_NUMBER_STATE from 
    [VN_10.10.22.2].[Udit.SFC].[dbo].QS_SERIAL_NUMBERS
    where serial_number = @serialNumber
    if @productName_VN is null or @productName_VN = ''
    begin
        select 1 errorcode,'条码' + @serialNumber + '未在越南注册'
        return;
    end

    --判断条码是否完工
    if @serialState_VN <> 1
    begin
        select 1 errorcode,'条码' + @serialNumber + '未在越南完工'
        return;
    end

    --判断产品是否一致
    if @productName <> @productName_VN
    begin
        select 1 errorcode,'条码' + @serialNumber + '所属产品' + @productName_VN + '与本产品不一致'
        return;
    end

    --验证通过
    select 0 errorcode
END