AKI-REFILL-2

USE SPEX_DB;

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

CREATE PROCEDURE spex.SP_AMANAH_RE_FILL_3_SEARCH
(
  @PART_NO VARCHAR(15),
  @ZONE_CD VARCHAR(1)
)
AS
BEGIN
    DECLARE @MSG_TEXT VARCHAR(MAX) = ''
        ,@PARAM1 VARCHAR(MAX) = ''

    DECLARE @PART_LABEL_CHECK_DIGIT VARCHAR(1)

    IF OBJECT_ID('tempdb..#TB_T_DATA') IS NOT NULL
        DROP TABLE #TB_T_DATA

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

    EXEC spex.usp_CutPartNo @PART_NO OUTPUT

    BEGIN TRY
        BEGIN TRANSACTION

            IF EXISTS (
                SELECT
                    a.[PART_NO]
                    , a.[QTY] AS QTY
                        FROM [SPEX_DB].[spex].[TB_R_REPORT_PROBLEM] a
                        INNER JOIN [SPEX_DB].[spex].[TB_R_PART_STOCK_INFO] b ON a.[PART_NO] = b.[PART_NO]
                        INNER JOIN [SPEX_DB].[spex].[TB_M_PART_STOCK] c ON a.[PART_NO] = c.[PART_NO]
                            WHERE a.[TYPE_PROBLEM] = 2
                                AND a.[PART_NO] = @PART_NO
                                AND ISNULL(b.SOR, 0) < ISNULL(c.MAX_STOCK, 0)
                                AND (ISNULL(b.SONDR, 0) > 0)
                                AND LEFT(c.RACK_ADDRESS_CD, 1) = @ZONE_CD

            )
                BEGIN

                    SET @MSG_TEXT = '1'

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

                END
            ELSE
                BEGIN

                    IF EXISTS (
                        SELECT R_PART.PART_NO
                            FROM [SPEX_DB].[spex].[TB_R_PART_STOCK_INFO] AS R_PART
                            INNER JOIN [SPEX_DB].[spex].[TB_M_PART_STOCK] AS M_PART ON R_PART.PART_NO = M_PART.PART_NO
                            WHERE
                                ISNULL(R_PART.SOR, 0) < ISNULL(M_PART.MAX_STOCK, 0)
                                    AND (ISNULL(R_PART.SONDR, 0) > 0)
                                    AND R_PART.PART_NO = @PART_NO
                                    AND LEFT(M_PART.RACK_ADDRESS_CD, 1) = @ZONE_CD
                    )
                        BEGIN
                            SET @MSG_TEXT = '0'

                            INSERT INTO #TB_T_DATA
                            VALUES (
                                'Success'
                                ,@MSG_TEXT
                                )
                        END
                    ELSE
                        BEGIN
                            SET @MSG_TEXT = 'P/N ' + @PART_NO + ' no not found'

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

                END


        COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
        ROLLBACK TRANSACTION

        SET @MSG_TEXT = 'Part no not found'

        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_3_SEARCH '851100D120 F', 'Y';