· 5 years ago · Jun 30, 2020, 03:30 AM
1USE SPEX_DB;
2
3IF EXISTS (
4SELECT *
5 FROM INFORMATION_SCHEMA.ROUTINES
6WHERE SPECIFIC_SCHEMA = N'spex'
7 AND SPECIFIC_NAME = N'SP_AMANAH_RE_FILL_5_SCAN_BT'
8 AND ROUTINE_TYPE = N'PROCEDURE'
9)
10DROP PROCEDURE spex.SP_AMANAH_RE_FILL_5_SCAN_BT
11GO
12
13CREATE PROCEDURE spex.SP_AMANAH_RE_FILL_5_SCAN_BT
14(
15 @RESERVE_LOC VARCHAR(MAX),
16 @BT_NO VARCHAR(30),
17 @USERNAME VARCHAR(20)
18)
19AS
20BEGIN
21
22 DECLARE @MSG_TEXT VARCHAR(max) = ''
23 DECLARE @RES_TEXT VARCHAR(max) = ''
24
25 SET @RES_TEXT = 'Failed'
26
27 DECLARE @counterExist int = 0
28 DECLARE @counterInProcess int = 0
29 DECLARE @counterBtTransExist int = 0
30 DECLARE @transferNo varchar(50)
31 DECLARE @counterBtTempExist int = 0
32 DECLARE @existsUsername varchar(max) = ''
33
34 SET NOCOUNT ON;
35 CREATE TABLE #TB_T_DATA (
36 Result VARCHAR(max),
37 Message VARCHAR(max)
38 )
39
40 SELECT @counterExist = COUNT(1)
41 FROM spex.TB_M_BUCKET_TRANSFER M
42 WHERE M.SOURCE = 'RESERVE'
43 AND M.TARGET = 'BINNING'
44 AND M.BT_NO = @BT_NO
45 AND M.[ZONE_CD] = LEFT(@RESERVE_LOC, 1)
46 AND ISNULL(M.STATUS, 0) IN (0, 3)
47
48 IF (@counterExist > 0)
49 BEGIN
50 SELECT @counterInProcess = COUNT(1)
51 FROM (
52 SELECT BT_NO
53 FROM spex.TB_T_BUCKET_TRANSFER
54 WHERE BT_NO = @BT_NO
55 AND OPEN_BY <> @USERNAME
56 ) X
57
58 IF (@counterInProcess = 1)
59 BEGIN
60
61 SELECT @existsUsername = OPEN_BY
62 FROM spex.TB_T_BUCKET_TRANSFER
63 WHERE BT_NO = @BT_NO
64
65 PRINT 'B/T ' + @BT_NO + ' has already been scan by ' + @existsUsername;
66 SET @MSG_TEXT = 'B/T ' + @BT_NO + ' has already been scan by ' + @existsUsername;
67 SET @RES_TEXT = 'Failed'
68 END
69 ELSE
70 BEGIN
71 SET @MSG_TEXT = '';
72 SET @RES_TEXT = 'Success'
73 END
74 END
75 ELSE
76 BEGIN
77 PRINT 'B/T ' + @BT_NO + ' not found.'
78 SET @MSG_TEXT = 'B/T ' + @BT_NO + ' not found.';
79 SET @RES_TEXT = 'Failed'
80 END
81
82 INSERT INTO #TB_T_DATA
83 VALUES (@RES_TEXT, @MSG_TEXT)
84
85 SELECT Result, Message
86 FROM #TB_T_DATA
87
88END
89GO
90
91
92-- EXECUTE spex.SP_AMANAH_RE_FILL_5_SCAN_BT 'Y-USUP-001', 'REBI-Y-200220-001', 'cahya';