mhamdani049

AKI-REFILL-7

Jun 29th, 2020
1,235
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE SPEX_DB;
  2.  
  3. IF EXISTS (
  4. SELECT *
  5.     FROM INFORMATION_SCHEMA.ROUTINES
  6. WHERE SPECIFIC_SCHEMA = N'spex'
  7.     AND SPECIFIC_NAME = N'SP_AMANAH_RE_FILL_5_SCAN_BT'
  8.     AND ROUTINE_TYPE = N'PROCEDURE'
  9. )
  10. DROP PROCEDURE spex.SP_AMANAH_RE_FILL_5_SCAN_BT
  11. GO
  12.  
  13. CREATE 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. )
  19. AS
  20. BEGIN
  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.  
  88. END
  89. GO
  90.  
  91.  
  92. -- EXECUTE spex.SP_AMANAH_RE_FILL_5_SCAN_BT 'Y-USUP-001', 'REBI-Y-200220-001', 'cahya';
RAW Paste Data