AKI-REFILL-0

USE SPEX_DB;

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

CREATE PROCEDURE spex.SP_AMANAH_RE_FILL_3_ZONE
AS
BEGIN

    SELECT x.* FROM (
        SELECT a.ZONE_CD AS ZONE, 0 AS STATUS FROM [SPEX_DB].[spex].[TB_M_ZONE] a WHERE a.ZONE_CD NOT IN (
            SELECT DISTINCT a.ZONE_CD AS ZONE FROM [SPEX_DB].[spex].[TB_M_ZONE] a
            LEFT JOIN [SPEX_DB].[spex].[TB_M_PART_STOCK] b ON a.ZONE_CD = LEFT(b.RACK_ADDRESS_CD, 1)
            LEFT JOIN [SPEX_DB].[spex].[TB_R_PART_STOCK_INFO] c ON b.PART_NO = c.PART_NO
            WHERE ISNULL(c.SOR, 0) < ISNULL(b.MAX_STOCK, 0)
            AND (ISNULL(c.SONDR, 0) > 0)
        )
        UNION
        SELECT DISTINCT a.ZONE_CD AS ZONE, 1 AS STATUS FROM [SPEX_DB].[spex].[TB_M_ZONE] a
        LEFT JOIN [SPEX_DB].[spex].[TB_M_PART_STOCK] b ON a.ZONE_CD = LEFT(b.RACK_ADDRESS_CD, 1)
        LEFT JOIN [SPEX_DB].[spex].[TB_R_PART_STOCK_INFO] c ON b.PART_NO = c.PART_NO
        WHERE ISNULL(c.SOR, 0) < ISNULL(b.MAX_STOCK, 0)
        AND (ISNULL(c.SONDR, 0) > 0)
    ) x ORDER BY x.ZONE ASC

END
GO

EXECUTE spex.SP_AMANAH_RE_FILL_3_ZONE