uUMTeDQv

1,
2    @RACK_ADDRESS VARCHAR(MAX) = '',
3    @PART_NO VARCHAR(MAX) = '',
4    @USERNAME VARCHAR(MAX) = ''
5AS
6BEGIN
7    SET NOCOUNT ON;
8
9    DECLARE @MSG_TEXT VARCHAR(max) = ''
10
11    EXEC spex.usp_CutPartNo @PART_NO OUTPUT
12
13    Declare @SET_STATUS INT = 1
14    Declare @SET_QTY INT
15    Declare @SET_TRANSFER_NO varchar(MAX)
16    Declare @SET_WRONG_RACK_ADDRESS_CD varchar(MAX)
17    Declare @SET_PART_NO varchar(MAX)
18    Declare @SET_RACK_ADDRESS_CD varchar(MAX)
19
20    CREATE TABLE #TB_T_DATA (
21		Result VARCHAR(max),
22        Message VARCHAR(max)
23	)
24
25	EXEC spex.usp_CutPartNo @PART_NO OUTPUT
26	PRINT '1. Wrong Address: ' + CONVERT(VARCHAR(MAX), @PART_NO)
27
28    IF EXISTS (
29        SELECT
30               a.[PART_NO]
31        FROM [SPEX_DB].[spex].[TB_R_REPORT_PROBLEM] a
32            INNER JOIN [SPEX_DB].[dbo].[TB_M_SYSTEM] b on a.[TYPE_PROBLEM] = b.[SYSTEM_CD]
33            INNER JOIN [SPEX_DB].[spex].[TB_M_PART_STOCK] c ON a.[PART_NO] = c.[PART_NO]
34        WHERE b.[SYSTEM_TYPE] = 'PROBLEM_CD'
35            AND ISNULL(a.[STATUS], 0) = 0
36            AND LEFT(a.[RACK_ADDRESS_CD], 1) = @ZONE
37            AND a.[RACK_ADDRESS_CD] = @RACK_ADDRESS
38            AND a.[PART_NO] = @PART_NO
39    )
40    BEGIN
41		PRINT '2. Wrong Address: Ada'
42        SELECT
43                @SET_PART_NO = a.[PART_NO],
44                @SET_QTY = a.[QTY],
45                @SET_RACK_ADDRESS_CD = a.[RACK_ADDRESS_CD],
46                @SET_TRANSFER_NO = a.[TRANSFER_NO],
47                @SET_STATUS = ISNULL(a.[STATUS], 0)
48        FROM [SPEX_DB].[spex].[TB_R_REPORT_PROBLEM] a
49            INNER JOIN [SPEX_DB].[dbo].[TB_M_SYSTEM] b on a.[TYPE_PROBLEM] = b.[SYSTEM_CD]
50            INNER JOIN [SPEX_DB].[spex].[TB_M_PART_STOCK] c ON a.[PART_NO] = c.[PART_NO]
51        WHERE b.[SYSTEM_TYPE] = 'PROBLEM_CD'
52            AND ISNULL(a.[STATUS], 0) = 0
53            AND LEFT(a.[RACK_ADDRESS_CD], 1) = @ZONE
54            AND a.[RACK_ADDRESS_CD] = @RACK_ADDRESS
55            AND a.[PART_NO] = @PART_NO
56            
57        PRINT '2. Wrong Address: Ada' + CONVERT(VARCHAR(MAX), @SET_STATUS)
58
59        IF (@SET_STATUS = 0)
60        BEGIN
61			PRINT '3. Wrong Address: Update'
62            UPDATE [SPEX_DB].[spex].[TB_R_REPORT_PROBLEM]
63            SET
64                [STATUS] = '1'
65                ,CHANGED_BY = @USERNAME
66                ,CHANGED_DT = CURRENT_TIMESTAMP
67            WHERE [PART_NO] = @SET_PART_NO AND [RACK_ADDRESS_CD] = @SET_RACK_ADDRESS_CD;
68            INSERT INTO #TB_T_DATA VALUES ('Success' ,@MSG_TEXT)
69        END
70        ELSE
71        BEGIN
72			PRINT '3. Wrong Address: Has been moved'
73            SET @MSG_TEXT = 'Rack address ' + @RACK_ADDRESS + ' has been moved';
74            INSERT INTO #TB_T_DATA
75            VALUES ('Failed', @MSG_TEXT)
76        END
77    END
78    ELSE
79    BEGIN
80		PRINT '3. Wrong Address: Not found'
81        SET @MSG_TEXT = 'Rack address ' + @RACK_ADDRESS + ' not found';
82        INSERT INTO #TB_T_DATA
83        VALUES ('Failed', @MSG_TEXT)
84    END
85
86    SELECT Result, Message
87	FROM #TB_T_DATA
88
89END
90