AKI-REFILL-5

USE SPEX_DB;

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

CREATE PROCEDURE spex.SP_AMANAH_RE_FILL_4_RESERVE_DETAIL
(
    @PART_NO NVARCHAR(15),
    @ZONE_CD VARCHAR(1)
)
AS
BEGIN

    SELECT TOP 1 * FROM (
        SELECT
        a.TRANSFER_NO
        , a.BT_NO
        , a.PART_NO
        , ISNULL(a.[QTY], 0) AS QTY
        , a.SOURCE
        , a.CREATED_BY
        , a.CREATED_DT
        , a.ID
        , a.START_DATE
        , a.END_DATE
        , a.RACK_ADDRESS
        , CASE
            WHEN ISNULL(a.[QTY], 0) > ISNULL(d.[REFILL_RESERVE_LOCATION_SET_MIN_QTY], 0)
                THEN 1
                ELSE 0
        END AS IS_ENABLE_INPUT_QTY
            FROM
                [spex].[TB_R_BINNING] a
                INNER JOIN  [SPEX_DB].[spex].[TB_R_PART_STOCK_INFO] b ON a.PART_NO = b.PART_NO
                LEFT JOIN [spex].[TB_R_BUCKET_TRANSFER_H] c ON a.TRANSFER_NO = c.TRANSFER_NO AND a.BT_NO = c.BT_NO
                CROSS APPLY (SELECT TOP 1 ISNULL([SYSTEM_VALUE], 0) AS REFILL_RESERVE_LOCATION_SET_MIN_QTY FROM [SPEX_DB].[dbo].[TB_M_SYSTEM] WHERE [SYSTEM_TYPE] = 'DEVICE' AND [SYSTEM_CD] = 'REFILL_RESERVE_LOCATION_SET_MIN_QTY') d
                    WHERE
                        a.[PART_NO] = @PART_NO
                        AND (a.[SOURCE] = 'NON-DIRECT')
                        AND ISNULL(b.SONDR, 0) > 0
                        AND (c.[CLOSE_BY] IS NOT NULL OR c.[CLOSE_DT] IS NOT NULL)
                        AND LEFT(a.[RACK_ADDRESS], 1) = @ZONE_CD
    ) x WHERE x.QTY > 0 ORDER BY x.SOURCE ASC, x.START_DATE ASC

END
GO

EXECUTE spex.SP_AMANAH_RE_FILL_4_RESERVE_DETAIL '851100D120', 'M';