AKI-REFILL-7

USE SPEX_DB;

IF EXISTS (
SELECT *
    FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'spex'
    AND SPECIFIC_NAME = N'SP_AMANAH_RE_FILL_5_SCAN_BT'
    AND ROUTINE_TYPE = N'PROCEDURE'
)
DROP PROCEDURE spex.SP_AMANAH_RE_FILL_5_SCAN_BT
GO

CREATE PROCEDURE spex.SP_AMANAH_RE_FILL_5_SCAN_BT
(
    @RESERVE_LOC VARCHAR(MAX),
    @BT_NO VARCHAR(30),
    @USERNAME VARCHAR(20)
)
AS
BEGIN

    DECLARE @MSG_TEXT VARCHAR(MAX) = ''
    DECLARE @RES_TEXT VARCHAR(MAX) = ''

    SET @RES_TEXT = 'Failed'

    DECLARE @counterExist INT = 0
    DECLARE @counterInProcess INT = 0
    DECLARE @counterBtTransExist INT = 0
    DECLARE @transferNo VARCHAR(50)
    DECLARE @counterBtTempExist INT = 0
    DECLARE @existsUsername VARCHAR(MAX) = ''

    SET NOCOUNT ON;
    CREATE TABLE #TB_T_DATA (
        RESULT VARCHAR(MAX),
        Message VARCHAR(MAX)
    )

    SELECT @counterExist = COUNT(1)
    FROM   spex.TB_M_BUCKET_TRANSFER M
    WHERE  M.SOURCE = 'RESERVE'
    AND    M.TARGET = 'BINNING'
    AND    M.BT_NO = @BT_NO
    AND    M.[ZONE_CD] = LEFT(@RESERVE_LOC, 1)
    AND    ISNULL(M.STATUS, 0) IN (0, 3)

    IF (@counterExist > 0)
    BEGIN
        SELECT @counterInProcess = COUNT(1)
        FROM (
            SELECT BT_NO
            FROM   spex.TB_T_BUCKET_TRANSFER
            WHERE  BT_NO = @BT_NO
            AND    OPEN_BY <> @USERNAME
        ) X

        IF (@counterInProcess = 1)
        BEGIN

            SELECT @existsUsername = OPEN_BY
            FROM   spex.TB_T_BUCKET_TRANSFER
            WHERE  BT_NO = @BT_NO

            PRINT 'B/T ' + @BT_NO + ' has already been scan by ' + @existsUsername;
            SET @MSG_TEXT = 'B/T ' + @BT_NO + ' has already been scan by ' + @existsUsername;
            SET @RES_TEXT = 'Failed'
        END
        ELSE
        BEGIN
            SET @MSG_TEXT = '';
            SET @RES_TEXT = 'Success'
        END
    END
    ELSE
    BEGIN
        PRINT 'B/T ' + @BT_NO + ' not found.'
        SET @MSG_TEXT = 'B/T ' + @BT_NO + ' not found.';
        SET @RES_TEXT = 'Failed'
    END

    INSERT INTO #TB_T_DATA
    VALUES (@RES_TEXT, @MSG_TEXT)

    SELECT RESULT, Message
    FROM #TB_T_DATA

END
GO


-- EXECUTE spex.SP_AMANAH_RE_FILL_5_SCAN_BT 'Y-USUP-001', 'REBI-Y-200220-001', 'cahya';