AKI-REFILL-6

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_M_RESERVE_LOCATION'
    AND ROUTINE_TYPE = N'PROCEDURE'
)
DROP PROCEDURE spex.SP_AMANAH_RE_FILL_5_SCAN_M_RESERVE_LOCATION
GO

CREATE PROCEDURE spex.SP_AMANAH_RE_FILL_5_SCAN_M_RESERVE_LOCATION
(
    @PART_NO NVARCHAR(MAX),
    @RESERVE_LOC NVARCHAR(MAX)
)
AS
BEGIN

    DECLARE @MSG_TEXT VARCHAR(MAX) = ''
        ,@PARAM1 VARCHAR(MAX) = ''
        ,@DATEPARAM VARCHAR(MAX)

    SET @DATEPARAM = CONVERT(VARCHAR, GETDATE(), 112)

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

    BEGIN TRY
        BEGIN TRANSACTION

            IF EXISTS (
                SELECT TOP 1 a.[PART_NO]
                        ,a.[RACK_CD]
                    FROM [SPEX_DB].[spex].[TB_M_RESERVE_LOCATION] a
                    LEFT JOIN [SPEX_DB].[spex].[TB_R_BINNING] b ON a.RACK_CD = b.[RACK_ADDRESS]
                    WHERE a.[RACK_CD] = @RESERVE_LOC AND a.[PART_NO] = @PART_NO
            )
                BEGIN

                    INSERT INTO #TB_T_DATA
                    VALUES ('Success', @MSG_TEXT)

                END
            ELSE
                BEGIN
                    SET @MSG_TEXT = ERROR_MESSAGE()

                    INSERT INTO #TB_T_DATA
                    VALUES ('Failed' ,@MSG_TEXT)
                END

        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION

        SET @MSG_TEXT = ERROR_MESSAGE()

        INSERT INTO #TB_T_DATA
        VALUES (
            'Failed'
            ,@MSG_TEXT
            )
    END CATCH

    SELECT RESULT
        ,Message
    FROM #TB_T_DATA

END
GO

EXECUTE spex.SP_AMANAH_RE_FILL_5_SCAN_M_RESERVE_LOCATION '851100D120', 'RA-002';