· 5 years ago · Jun 30, 2020, 03:32 AM
1
2USE SPEX_DB;
3
4IF EXISTS (
5SELECT *
6 FROM INFORMATION_SCHEMA.ROUTINES
7WHERE SPECIFIC_SCHEMA = N'spex'
8 AND SPECIFIC_NAME = N'SP_AMANAH_RE_FILL_5_DO_RE_FILL'
9 AND ROUTINE_TYPE = N'PROCEDURE'
10)
11DROP PROCEDURE spex.SP_AMANAH_RE_FILL_5_DO_RE_FILL
12GO
13
14CREATE PROCEDURE spex.SP_AMANAH_RE_FILL_5_DO_RE_FILL
15(
16 @PART_NO NVARCHAR(MAX),
17 @PRIMARY_LOC NVARCHAR(MAX),
18 @RESERVE_LOC NVARCHAR(MAX),
19 @ADD_QTY INT,
20 @SOR INT,
21 @MAX_STOCK INT,
22 @STOCK_REQ INT,
23 @RESERVE_CURRENT_QTY INT,
24 @SOURCE VARCHAR(MAX),
25 @BT_NO VARCHAR(MAX),
26 @IS_PROBLEM_SHORTAGE INT = 1,
27 @ZONE_CD VARCHAR(1),
28 @USERNAME VARCHAR(MAX) = ''
29)
30AS
31BEGIN
32
33 DECLARE @MSG_TEXT VARCHAR(MAX) = ''
34
35 CREATE TABLE #TB_T_DATA (
36 Result VARCHAR(max),
37 Message VARCHAR(max)
38 )
39
40 DECLARE @@temp_AMANAH_PART_STOCK TABLE (
41 [PART_NO] varchar(15) NOT NULL,
42 [PART_NAME] varchar(100) NOT NULL,
43 [RACK_ADDRESS_CD] varchar(12) NOT NULL,
44 [DOCK_CD] varchar(2) NULL,
45 [DOCK_PRD] varchar(2) NULL,
46 [DAD] decimal(6,2) NULL,
47 [MAD] int NOT NULL,
48 [ORDER_CYCLE] decimal(6,2) NOT NULL,
49 [PROCUREMENT_LT] decimal(6,2) NOT NULL,
50 [RECEIVING_LT] decimal(6,2) NOT NULL,
51 [ALFA] decimal(6,2) NOT NULL,
52 [ROP] int NOT NULL,
53 [MIN_STOCK] int NOT NULL,
54 [MAX_STOCK] int NOT NULL,
55 [KANBAN_QTY] decimal(6) NOT NULL,
56 [PART_DIMENSION] varchar(2) NULL)
57
58 DECLARE @MaxQtyPart int, @QtyRack int, @RACK_ADDRESS_CD VARCHAR(22)
59 DECLARE @ERR_MSG VARCHAR(MAX), @isValid bit = 1
60
61 DECLARE @transferNo varchar(50)
62 DECLARE @counterBtTransExist int = 0
63 DECLARE @counterBtTempExist int = 0
64 DECLARE @rowCount int = 0
65 DECLARE @rowCountRefill int = 0
66 DECLARE @transferItemNo int
67 DECLARE @successQtyRefill int = 0
68
69 DECLARE @SET_RESERVE_LOC_CURRENT varchar(MAX) = '1'
70 DECLARE @SET_RESERVE_LOC_SCAN varchar(MAX) = '0'
71
72 SELECT @SET_RESERVE_LOC_CURRENT = value FROM STRING_SPLIT(@RESERVE_LOC, ',') WHERE RTRIM(value) <> '' ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;
73 SELECT @SET_RESERVE_LOC_SCAN = value FROM STRING_SPLIT(@RESERVE_LOC, ',') WHERE RTRIM(value) <> '' ORDER BY (SELECT 0) OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;
74 PRINT '@SET_RESERVE_LOC_CURRENT, @SET_RESERVE_LOC_SCAN:' + CONVERT(VARCHAR(MAX), @SET_RESERVE_LOC_CURRENT) + ', ' + CONVERT(VARCHAR(MAX), @SET_RESERVE_LOC_SCAN)
75
76 IF (@SET_RESERVE_LOC_CURRENT = @SET_RESERVE_LOC_SCAN)
77 BEGIN
78
79 EXEC spex.usp_CutPartNo @PART_NO OUTPUT
80
81 -- [START] backup at 16 april 2020 by yusup
82 -- IF EXISTS (SELECT TOP 1 ID FROM spex.TB_R_BINNING WHERE PART_NO = @PART_NO AND (SOURCE = 'NON-DIRECT') AND LEFT([RACK_ADDRESS], 1) = @ZONE_CD AND ISNULL(QTY, 0) > 0)
83 -- [END]
84 IF EXISTS (SELECT TOP 1 ID FROM spex.TB_R_BINNING WHERE PART_NO = @PART_NO AND RACK_ADDRESS = @SET_RESERVE_LOC_CURRENT AND ISNULL(QTY, 0) > 0)
85 BEGIN
86
87 -- 1. Cek jika add qty lebih dari reserve qty maka error.
88 IF (@ADD_QTY > @RESERVE_CURRENT_QTY)
89 BEGIN
90
91 SET @MSG_TEXT = 'Gagal, kamu menambahkan qty melebihi kapasitas qty yang tersedia di reserve lokasi. Saran coba ' + CONVERT(varchar(MAX), @RESERVE_CURRENT_QTY) + 'qty.'
92 INSERT INTO #TB_T_DATA
93 VALUES (
94 'Failed'
95 ,@MSG_TEXT
96 )
97 END
98 ELSE
99 BEGIN
100
101 -- 2. Cek jika add qty lebih dari stock req untuk primary loc
102 IF (@ADD_QTY > @STOCK_REQ)
103 BEGIN
104
105 SET @MSG_TEXT = 'Gagal, kamu menambahkan qty melebihi maximal stock yang bisa disimpan di primary lokasi. Saran coba ' + CONVERT(varchar(MAX), @STOCK_REQ) +' qty.' -- ID: Mohon maaf qty yang di scan melebihi stok yang bisa disimpan di primary location. Butuh 2000 qty.
106 INSERT INTO #TB_T_DATA
107 VALUES (
108 'Failed'
109 ,@MSG_TEXT
110 )
111 END
112 ELSE
113 BEGIN
114
115 SELECT @counterBtTransExist = COUNT(1)
116 FROM spex.TB_R_BUCKET_TRANSFER_H BTH
117 WHERE BTH.BT_NO = @BT_NO
118 AND CLOSE_BY is null
119 AND CLOSE_DT is null
120
121 IF (@counterBtTransExist = 0)
122 BEGIN
123 SELECT @transferNo = 'TR' + (CONVERT(VARCHAR, YEAR(CURRENT_TIMESTAMP)) +
124 CONVERT(VARCHAR, RIGHT('0' + RTRIM(MONTH(CURRENT_TIMESTAMP)), 2)) +
125 CONVERT(VARCHAR, RIGHT('0' + RTRIM(DAY(CURRENT_TIMESTAMP)), 2)) +
126 RIGHT('0000' + CONVERT(VARCHAR, RTRIM(ISNULL(MAX(SUBSTRING(TRANSFER_NO, 10, 13)), 0) + 1)), 5))
127 FROM spex.TB_R_BUCKET_TRANSFER_H
128 WHERE DAY(CREATED_DT) = DAY(CURRENT_TIMESTAMP)
129 AND MONTH(CREATED_DT) = MONTH(CURRENT_TIMESTAMP)
130 AND YEAR(CREATED_DT) = YEAR(CURRENT_TIMESTAMP)
131 END
132 ELSE
133 BEGIN
134 SELECT @transferNo = TRANSFER_NO
135 FROM spex.TB_R_BUCKET_TRANSFER_H BTH
136 WHERE BTH.BT_NO = @BT_NO
137 AND CLOSE_BY is null
138 AND CLOSE_DT is null
139 END
140
141 SELECT @counterBtTempExist = COUNT(1)
142 FROM spex.TB_T_BUCKET_TRANSFER TBT
143 WHERE TBT.BT_NO = @BT_NO
144
145 IF (@counterBtTransExist = 0)
146 BEGIN
147 INSERT INTO spex.TB_R_BUCKET_TRANSFER_H
148 (TRANSFER_NO, BT_NO, OPEN_BY, OPEN_DT, CLOSE_BY, CLOSE_DT, CREATED_BY, CREATED_DT)
149 VALUES
150 (@transferNo, @BT_NO, @USERNAME, CURRENT_TIMESTAMP, null, null, @USERNAME, CURRENT_TIMESTAMP)
151 END
152
153 IF (@counterBtTempExist = 0)
154 BEGIN
155 INSERT INTO spex.TB_T_BUCKET_TRANSFER
156 (TRANSFER_NO, BT_NO, OPEN_BY, OPEN_DT, CREATED_BY, CREATED_DT)
157 VALUES
158 (@transferNo, @BT_NO, @USERNAME, CURRENT_TIMESTAMP, @USERNAME, CURRENT_TIMESTAMP)
159 END
160
161 PRINT 'IS_PROBLEM_SHORTAGE = ' + CONVERT(VARCHAR(MAX), @IS_PROBLEM_SHORTAGE)
162 IF (@IS_PROBLEM_SHORTAGE = 1)
163 BEGIN
164
165 IF OBJECT_ID('tempdb..#tmpReportProblem') IS NOT NULL
166 DROP TABLE #tmpReportProblem
167
168 CREATE TABLE #tmpReportProblem
169 (
170 ID INT IDENTITY(1,1) PRIMARY KEY,
171 PROBLEM_NO VARCHAR(MAX),
172 TRANSFER_NO VARCHAR(MAX),
173 MANIFEST_NO VARCHAR(MAX),
174 BT_NO VARCHAR(MAX)
175 )
176
177 INSERT INTO #tmpReportProblem
178 SELECT
179 PROBLEM_NO
180 , TRANSFER_NO
181 , MANIFEST_NO
182 , BT_NO
183 FROM [SPEX_DB].[spex].[TB_R_REPORT_PROBLEM]
184 WHERE PART_NO = @PART_NO
185 AND TYPE_PROBLEM = 2
186 -- [START] backup at 17 april 2020 by yusup
187 -- AND MANIFEST_NO IS NOT NULL
188 -- GROUP BY TRANSFER_NO, MANIFEST_NO, BT_NO
189 -- [END]
190
191 DECLARE @LoopCenter INT, @problemNoTmpReportProblem VARCHAR(MAX), @MaxTmpReportProblem INT, @transferNoTmpReportProblem varchar(MAX), @manifestNoTmpReportProblem varchar(MAX), @btNoTmpReportProblem varchar(MAX), @qtyTmpReportProblem INT = 0, @refillQtyTmpReportProblem INT = 0, @needQtyTmpReportProblem INT = 0, @isQtyTmpReportProblem INT = 0
192 SELECT @LoopCenter = MIN(ID), @MaxTmpReportProblem = MAX(ID) FROM #tmpReportProblem
193
194 PRINT 'Check ADD_QTY, LoopCenter, MaxTmpReportProblem: ' + CONVERT(VARCHAR(MAX), @ADD_QTY) + ', ' + CONVERT(VARCHAR(MAX), @LoopCenter) + ', ' + CONVERT(VARCHAR(MAX), @MaxTmpReportProblem)
195 WHILE(@ADD_QTY > 0 AND @LoopCenter IS NOT NULL AND @LoopCenter <= @MaxTmpReportProblem)
196 BEGIN
197
198 SET @MSG_TEXT = 'Update Problem Shortage \n '
199
200 SELECT
201 @problemNoTmpReportProblem = PROBLEM_NO
202 , @transferNoTmpReportProblem = TRANSFER_NO
203 , @manifestNoTmpReportProblem = MANIFEST_NO
204 , @btNoTmpReportProblem = BT_NO
205 FROM #tmpReportProblem
206 WHERE ID = @LoopCenter
207 PRINT '@transferNoTmpReportProblem, @manifestNoTmpReportProblem, @btNoTmpReportProblem, @problemNoTmpReportProblem :' + CONVERT(VARCHAR(MAX), @transferNoTmpReportProblem) + ', ' + CONVERT(VARCHAR(MAX), @manifestNoTmpReportProblem) + ', ' + CONVERT(VARCHAR(MAX), @btNoTmpReportProblem) + ', ' + CONVERT(VARCHAR(MAX), @problemNoTmpReportProblem)
208
209 DECLARE @foundRowReportProblem INT = 0
210 SELECT
211 @foundRowReportProblem = 1
212 , @qtyTmpReportProblem = ISNULL(QTY, 0)
213 , @refillQtyTmpReportProblem = ISNULL(REFILL_QTY, 0)
214 FROM spex.TB_R_REPORT_PROBLEM
215 WHERE TYPE_PROBLEM = 2
216 AND TRANSFER_NO = @transferNoTmpReportProblem
217 AND MANIFEST_NO = @manifestNoTmpReportProblem
218 AND BT_NO = @btNoTmpReportProblem
219 AND PART_NO = @PART_NO
220 AND PROBLEM_NO = @problemNoTmpReportProblem
221 AND ISNULL(REFILL_QTY, 0) < ISNULL(QTY, 0)
222 ORDER BY REFILL_QTY ASC
223 PRINT '@foundRowReportProblem : ' + CONVERT(VARCHAR(MAX), @foundRowReportProblem)
224 PRINT '@qtyTmpReportProblem, @refillQtyTmpReportProblem :' + CONVERT(VARCHAR(MAX), @qtyTmpReportProblem) + ', ' + CONVERT(VARCHAR(MAX), @refillQtyTmpReportProblem)
225
226 IF (@foundRowReportProblem > 0)
227 BEGIN
228
229 SET @needQtyTmpReportProblem = @qtyTmpReportProblem - @refillQtyTmpReportProblem
230 PRINT '@needQtyTmpReportProblem : ' + CONVERT(VARCHAR(MAX), @needQtyTmpReportProblem)
231
232 SET @isQtyTmpReportProblem = @ADD_QTY
233 IF (@ADD_QTY >= @needQtyTmpReportProblem)
234 BEGIN
235 SET @isQtyTmpReportProblem = @needQtyTmpReportProblem
236 END
237 PRINT '@isQtyTmpReportProblem : ' + CONVERT(VARCHAR(MAX), @isQtyTmpReportProblem)
238
239 SET NOCOUNT ON;
240 BEGIN TRY
241 BEGIN TRANSACTION
242 UPDATE spex.TB_R_REPORT_PROBLEM SET REFILL_QTY = ISNULL(REFILL_QTY, 0) + @isQtyTmpReportProblem, CHANGED_BY = @USERNAME, CHANGED_DT = CURRENT_TIMESTAMP WHERE TYPE_PROBLEM = 2 AND TRANSFER_NO = @transferNoTmpReportProblem AND MANIFEST_NO = @manifestNoTmpReportProblem AND BT_NO = @btNoTmpReportProblem AND PART_NO = @PART_NO AND PROBLEM_NO = @problemNoTmpReportProblem
243 --SELECT * FROM spex.TB_R_REPORT_PROBLEM WHERE TYPE_PROBLEM = 2 AND TRANSFER_NO = @transferNoTmpReportProblem AND MANIFEST_NO = @manifestNoTmpReportProblem AND BT_NO = @btNoTmpReportProblem AND PART_NO = @PART_NO
244 PRINT 'Update REFILL_QTY In TB_R_REPORT_PROBLEM Success'
245
246 SET @MSG_TEXT = @MSG_TEXT + 'TRANSFER_NO@' + CONVERT(VARCHAR(MAX), @transferNoTmpReportProblem) + ' MANIFEST_NO@' + CONVERT(VARCHAR(MAX), @manifestNoTmpReportProblem) + ' BT_NO@' + CONVERT(VARCHAR(MAX), @btNoTmpReportProblem) + ' PART_NO@' + CONVERT(VARCHAR(MAX), @PART_NO) + ' REFILLQTY@' + CONVERT(VARCHAR(MAX), @isQtyTmpReportProblem) + ' \n '
247
248 SET @successQtyRefill = @successQtyRefill + @isQtyTmpReportProblem
249
250 COMMIT TRANSACTION
251 END TRY
252 BEGIN CATCH
253 PRINT 'Update REFILL_QTY In TB_R_REPORT_PROBLEM Error'
254 END CATCH
255
256
257 SET @ADD_QTY = @ADD_QTY - @isQtyTmpReportProblem
258 PRINT '@ADD_QTY, @LoopCenter ' + CONVERT(VARCHAR(MAX), @ADD_QTY) + ', ' + CONVERT(VARCHAR(MAX), @LoopCenter)
259
260 END
261
262 SET @LoopCenter = @LoopCenter + 1
263 END
264 PRINT '@successQtyRefill ' + CONVERT(VARCHAR(MAX), @successQtyRefill)
265
266 END
267
268 DECLARE @addQtyRefill INT = 0
269
270 IF (@IS_PROBLEM_SHORTAGE = 1)
271 BEGIN
272 SET @addQtyRefill = @successQtyRefill + ISNULL(@ADD_QTY, 0)
273 END
274 ELSE
275 BEGIN
276 SET @addQtyRefill = @ADD_QTY
277 END
278 PRINT '@addQtyRefill ' + CONVERT(VARCHAR(MAX), @addQtyRefill)
279
280 DECLARE @getCurrentQtyNonDirect INT = 0, @setRefillQtyNonDirect INT = @addQtyRefill, @getCurrentQtyDirect INT = 0, @setRefillQtyDirect INT = @addQtyRefill, @setLeftQtyRefill INT = 0
281 IF (EXISTS(SELECT PART_NO FROM [spex].[TB_R_PART_STOCK_INFO] WHERE PART_NO = @PART_NO AND SONDR > 0))
282 BEGIN
283
284 SELECT @getCurrentQtyNonDirect = SONDR FROM [spex].[TB_R_PART_STOCK_INFO] WHERE PART_NO = @PART_NO
285 IF (@SOURCE = 'NON-DIRECT')
286 BEGIN
287 if (@addQtyRefill >= @getCurrentQtyNonDirect)
288 begin
289 set @setRefillQtyNonDirect = @getCurrentQtyNonDirect
290 set @setLeftQtyRefill = @addQtyRefill - @getCurrentQtyNonDirect
291 end
292 else
293 begin
294 set @setRefillQtyNonDirect = @addQtyRefill
295 if (@addQtyRefill >= @getCurrentQtyNonDirect)
296 begin
297 set @setRefillQtyNonDirect = @getCurrentQtyNonDirect
298 set @setLeftQtyRefill = @addQtyRefill - @getCurrentQtyNonDirect
299 end
300 end
301 UPDATE [spex].[TB_R_PART_STOCK_INFO] SET SONDR = SONDR - @setRefillQtyNonDirect
302 ,CHANGED_BY = @USERNAME
303 ,CHANGED_DT = CURRENT_TIMESTAMP
304 WHERE PART_NO = @PART_NO
305 PRINT 'Update NON-DIRECT ' + CONVERT(VARCHAR(MAX), @getCurrentQtyNonDirect) + ', Refill Qty = ' + CONVERT(VARCHAR(MAX), @setRefillQtyNonDirect) + ', Left Qty = ' + CONVERT(VARCHAR(MAX), @setLeftQtyRefill)
306
307 SELECT @transferNo = TRANSFER_NO
308 FROM spex.TB_R_BUCKET_TRANSFER_H BTH
309 WHERE BTH.BT_NO = @BT_NO
310 AND CLOSE_BY is null
311 AND CLOSE_DT is null
312
313 SET @rowCount = 0
314
315 SELECT @rowCount = COUNT(1)
316 FROM spex.TB_R_BUCKET_TRANSFER_D
317 WHERE TRANSFER_NO = @transferNo
318 AND PART_NO = @PART_NO
319
320 IF (@rowCount > 0)
321 BEGIN
322 UPDATE spex.TB_R_BUCKET_TRANSFER_D
323 SET KANBAN_QTY = isnull(KANBAN_QTY, 0) + @setRefillQtyNonDirect,
324 CHANGED_BY = @USERNAME,
325 CHANGED_DT = CURRENT_TIMESTAMP
326 WHERE TRANSFER_NO = @transferNo
327 AND PART_NO = @PART_NO
328 PRINT 'Update KANBAN_QTY In TB_R_BUCKET_TRANSFER_D'
329 END
330 ELSE
331 BEGIN
332 SELECT @transferItemNo = ISNULL(MAX(TRANSFER_ITEM_NO), 0) + 1
333 FROM spex.TB_R_BUCKET_TRANSFER_D
334 WHERE TRANSFER_NO = @transferNo
335
336 INSERT INTO spex.TB_R_BUCKET_TRANSFER_D
337 (
338 TRANSFER_NO
339 , TRANSFER_ITEM_NO
340 , PART_NO
341 , KANBAN_QTY
342 , DELIVERED_QTY
343 , PROBLEM_FLAG
344 , CREATED_BY
345 , CREATED_DT
346 )
347 SELECT
348 @transferNo
349 , @transferItemNo
350 , R_PART_STOCK_INFO.PART_NO
351 , @setRefillQtyNonDirect
352 , 0
353 , NULL
354 , @USERNAME
355 , CURRENT_TIMESTAMP
356 FROM [SPEX_DB].[spex].[TB_R_PART_STOCK_INFO] R_PART_STOCK_INFO
357 INNER JOIN [SPEX_DB].[spex].[TB_M_PART_STOCK] as M_PART_STOCK
358 ON R_PART_STOCK_INFO.PART_NO = M_PART_STOCK.PART_NO
359 WHERE ISNULL(R_PART_STOCK_INFO.SOR, 0) < ISNULL(M_PART_STOCK.MAX_STOCK, 0)
360 AND (ISNULL(R_PART_STOCK_INFO.SODR, 0) > 0 OR ISNULL(R_PART_STOCK_INFO.SONDR, 0) > 0)
361 AND R_PART_STOCK_INFO.PART_NO = @PART_NO
362 PRINT 'Insert New KANBAN_QTY In TB_R_BUCKET_TRANSFER_D'
363 END
364
365 SET @rowCountRefill = 0
366 SELECT @rowCountRefill = COUNT(1) FROM dbo.TB_R_REFILL WHERE TRANSFER_NO = @transferNo AND PART_NO = @PART_NO AND SOURCE = @SOURCE AND STATUS <> 3
367
368 IF(@rowCountRefill > 0)
369 BEGIN
370 UPDATE dbo.TB_R_REFILL SET
371 QTY = ISNULL(QTY, 0) + @addQtyRefill
372 , CHANGED_BY = @USERNAME
373 , CHANGED_DT = CURRENT_TIMESTAMP
374 WHERE TRANSFER_NO = @transferNo AND PART_NO = @PART_NO
375 PRINT 'Update QTY In TB_R_REFILL ' + CONVERT(VARCHAR(MAX), @addQtyRefill)
376 END
377 ELSE
378 BEGIN
379 INSERT INTO dbo.TB_R_REFILL
380 (TRANSFER_NO, BT_NO, PART_NO, QTY, CREATED_BY, CREATED_DT, SOURCE, STATUS)
381 VALUES
382 (@transferNo, @BT_NO, @PART_NO, @addQtyRefill, @USERNAME, CURRENT_TIMESTAMP, @SOURCE, 0)
383 PRINT 'Insert New QTY In TB_R_REFILL ' + CONVERT(VARCHAR(MAX), @addQtyRefill)
384 END
385
386 PRINT '@transferNo:' + CONVERT(VARCHAR(MAX), @transferNo)
387
388 -- [START] backup at 16 april 2020 by yusup
389 -- UPDATE spex.TB_R_BINNING SET QTY = QTY - @addQtyRefill, CHANGED_BY = @USERNAME, CHANGED_DT = CURRENT_TIMESTAMP WHERE ID IN (SELECT TOP 1 ID FROM spex.TB_R_BINNING WHERE PART_NO = @PART_NO AND SOURCE = 'NON-DIRECT' AND LEFT([RACK_ADDRESS], 1) = @ZONE_CD AND ISNULL(QTY, 0) > 0 ORDER BY SOURCE ASC, START_DATE ASC)
390 -- [END]
391 UPDATE spex.TB_R_BINNING SET QTY = QTY - @addQtyRefill, CHANGED_BY = @USERNAME, CHANGED_DT = CURRENT_TIMESTAMP WHERE ID IN (SELECT TOP 1 ID FROM spex.TB_R_BINNING WHERE PART_NO = @PART_NO AND RACK_ADDRESS = @SET_RESERVE_LOC_CURRENT AND LEFT([RACK_ADDRESS], 1) = @ZONE_CD AND ISNULL(QTY, 0) > 0)
392 IF NOT EXISTS(SELECT 1 FROM spex.TB_R_STOCK_CARD WHERE PART_NO = @PART_NO AND CONVERT ( DATE, [DATE] ) = CONVERT ( DATE, CURRENT_TIMESTAMP ) AND FLAG = '0' )
393 BEGIN
394 INSERT INTO spex.TB_R_STOCK_CARD (
395 [DATE],
396 [FLAG],
397 [SEQ],
398 [PART_NO],
399 [PART_NAME],
400 [SUPPLIER_CD],
401 [SUB_SUPPLIER_CD],
402 [SUPPLIER_PLANT],
403 [SUB_SUPPLIER_PLANT],
404 [QTY],
405 [BEGIN_STOCK],
406 [END_STOCK],
407 [REF_FILE],
408 [CREATED_BY],
409 [CREATED_DT],
410 [CHANGED_BY],
411 [CHANGED_DT]
412 ) SELECT
413 CURRENT_TIMESTAMP AS [DATE],
414 0,
415 ( SELECT ISNULL( MAX ( SEQ ), 0 ) + 1 AS SEQ FROM spex.TB_R_STOCK_CARD A WHERE CONVERT ( DATE, A.DATE ) = CONVERT ( DATE, CURRENT_TIMESTAMP ) ),
416 @PART_NO,
417 P.PART_NAME,
418 P.SUPPLIER_CD,
419 P.SUB_SUPPLIER_CD,
420 P.SUPPLIER_PLANT,
421 P.SUB_SUPPLIER_PLANT,
422 @addQtyRefill,
423 SOH,
424 SOH + @addQtyRefill,
425 NULL,
426 @USERNAME,
427 CURRENT_TIMESTAMP,
428 @addQtyRefill,
429 CURRENT_TIMESTAMP
430 FROM spex.TB_M_PACKING_PART P
431 JOIN spex.TB_R_PART_STOCK_INFO S ON P.PART_NO = S.PART_NO
432 WHERE P.PART_NO = @PART_NO
433 END
434 ELSE
435 BEGIN
436 UPDATE A
437 SET A.QTY = A.QTY + @addQtyRefill,
438 END_STOCK = B.SOH + @addQtyRefill,
439 CHANGED_BY = @USERNAME,
440 CHANGED_DT = CURRENT_TIMESTAMP
441 FROM spex.TB_R_STOCK_CARD A
442 JOIN spex.TB_R_PART_STOCK_INFO B ON A.PART_NO = B.PART_NO
443 WHERE A.PART_NO = @PART_NO
444 AND CONVERT ( DATE, [DATE] ) = CONVERT ( DATE, CURRENT_TIMESTAMP )
445 AND FLAG = 0
446 END
447
448 SET @MSG_TEXT = @MSG_TEXT + ' | Remaining qty refilled @' + CONVERT(VARCHAR(MAX), @ADD_QTY)
449 INSERT INTO #TB_T_DATA
450 VALUES ('Success', @MSG_TEXT)
451
452 END
453 END
454
455 END
456
457 END
458
459 END
460 ELSE
461 BEGIN
462 SET @MSG_TEXT = 'Part No ' + @PART_NO + ' on Reserve Location ' + @SET_RESERVE_LOC_CURRENT + ' not found or All done'
463 INSERT INTO #TB_T_DATA
464 VALUES ('Failed', @MSG_TEXT)
465 END
466
467 END
468 ELSE
469 BEGIN
470 SET @MSG_TEXT = 'INVALID RESERVE ADDRESS!'
471 INSERT INTO #TB_T_DATA
472 VALUES ('Failed', @MSG_TEXT)
473 END
474
475 SELECT Result
476 ,Message
477 FROM #TB_T_DATA
478
479END
480GO
481
482
483-- EXECUTE spex.SP_AMANAH_RE_FILL_5_DO_RE_FILL '851430D030', 'S0104-3-B', 'S-UPARMAN-001,S-UPARMAN-001', 2, 1006, 2020, 994, 3, 'NON-DIRECT', 'REBI-S-240220-001', 1, 'S', 'AMANAH.Yusup';