AKI-WRONGADDRESS-5

,
    @RACK_ADDRESS VARCHAR(MAX) = '',
    @PART_NO VARCHAR(MAX) = '',
    @USERNAME VARCHAR(MAX) = ''
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @MSG_TEXT VARCHAR(MAX) = ''

    EXEC spex.usp_CutPartNo @PART_NO OUTPUT

    DECLARE @SET_STATUS INT = 1
    DECLARE @SET_QTY INT
    DECLARE @SET_TRANSFER_NO VARCHAR(MAX)
    DECLARE @SET_WRONG_RACK_ADDRESS_CD VARCHAR(MAX)
    DECLARE @SET_PART_NO VARCHAR(MAX)
    DECLARE @SET_RACK_ADDRESS_CD VARCHAR(MAX)

    CREATE TABLE #TB_T_DATA (
        RESULT VARCHAR(MAX),
        Message VARCHAR(MAX)
    )

    EXEC spex.usp_CutPartNo @PART_NO OUTPUT
    PRINT '1. Wrong Address: ' + CONVERT(VARCHAR(MAX), @PART_NO)

    IF EXISTS (
        SELECT
               a.[PART_NO]
        FROM [SPEX_DB].[spex].[TB_R_REPORT_PROBLEM] a
            INNER JOIN [SPEX_DB].[dbo].[TB_M_SYSTEM] b ON a.[TYPE_PROBLEM] = b.[SYSTEM_CD]
            INNER JOIN [SPEX_DB].[spex].[TB_M_PART_STOCK] c ON a.[PART_NO] = c.[PART_NO]
        WHERE b.[SYSTEM_TYPE] = 'PROBLEM_CD'
            AND ISNULL(a.[STATUS], 0) = 0
            AND LEFT(a.[RACK_ADDRESS_CD], 1) = @ZONE
            AND a.[RACK_ADDRESS_CD] = @RACK_ADDRESS
            AND a.[PART_NO] = @PART_NO
    )
    BEGIN
        PRINT '2. Wrong Address: Ada'
        SELECT
                @SET_PART_NO = a.[PART_NO],
                @SET_QTY = a.[QTY],
                @SET_RACK_ADDRESS_CD = a.[RACK_ADDRESS_CD],
                @SET_TRANSFER_NO = a.[TRANSFER_NO],
                @SET_STATUS = ISNULL(a.[STATUS], 0)
        FROM [SPEX_DB].[spex].[TB_R_REPORT_PROBLEM] a
            INNER JOIN [SPEX_DB].[dbo].[TB_M_SYSTEM] b ON a.[TYPE_PROBLEM] = b.[SYSTEM_CD]
            INNER JOIN [SPEX_DB].[spex].[TB_M_PART_STOCK] c ON a.[PART_NO] = c.[PART_NO]
        WHERE b.[SYSTEM_TYPE] = 'PROBLEM_CD'
            AND ISNULL(a.[STATUS], 0) = 0
            AND LEFT(a.[RACK_ADDRESS_CD], 1) = @ZONE
            AND a.[RACK_ADDRESS_CD] = @RACK_ADDRESS
            AND a.[PART_NO] = @PART_NO

        PRINT '2. Wrong Address: Ada' + CONVERT(VARCHAR(MAX), @SET_STATUS)

        IF (@SET_STATUS = 0)
        BEGIN
            PRINT '3. Wrong Address: Update'
            UPDATE [SPEX_DB].[spex].[TB_R_REPORT_PROBLEM]
            SET
                [STATUS] = '1'
                ,CHANGED_BY = @USERNAME
                ,CHANGED_DT = CURRENT_TIMESTAMP
            WHERE [PART_NO] = @SET_PART_NO AND [RACK_ADDRESS_CD] = @SET_RACK_ADDRESS_CD;
            INSERT INTO #TB_T_DATA VALUES ('Success' ,@MSG_TEXT)
        END
        ELSE
        BEGIN
            PRINT '3. Wrong Address: Has been moved'
            SET @MSG_TEXT = 'Rack address ' + @RACK_ADDRESS + ' has been moved';
            INSERT INTO #TB_T_DATA
            VALUES ('Failed', @MSG_TEXT)
        END
    END
    ELSE
    BEGIN
        PRINT '3. Wrong Address: Not found'
        SET @MSG_TEXT = 'Rack address ' + @RACK_ADDRESS + ' not found';
        INSERT INTO #TB_T_DATA
        VALUES ('Failed', @MSG_TEXT)
    END

    SELECT RESULT, Message
    FROM #TB_T_DATA

END