AKI-REFILL-1

USE SPEX_DB;

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

CREATE PROCEDURE spex.SP_AMANAH_RE_FILL_3_LIST
    @ZONE_CD VARCHAR(1)
AS
BEGIN
    SELECT DISTINCT z.*
        FROM
            (
                SELECT DISTINCT  y.* FROM (
                    SELECT
                        a.[PART_NO]
                        , c.RACK_ADDRESS_CD AS PRIMARY_LOC
                        , ISNULL(b.[SOH], 0) AS SOH
                        , ISNULL(b.[SOR], 0) AS SOR
                        , ISNULL(c.[MAX_STOCK], 0) AS MAX_STOCK
                        , ISNULL(b.[SODR], 0) AS SODR
                        , ISNULL(b.[SONDR], 0) AS SONDR
                        , 1 AS IS_PROBLEM_SHORTAGE
                        , (
                            SELECT TOP 1 z.RACK_ADDRESS
                                FROM [spex].[TB_R_BINNING] z
                                INNER JOIN [SPEX_DB].[spex].[TB_R_PART_STOCK_INFO] y
                                    ON z.PART_NO = y.PART_NO
                                WHERE z.PART_NO = b.PART_NO
                                    AND ( z.SOURCE = 'NON-DIRECT')
                                    AND ISNULL(y.SONDR, 0) > 0
                                    AND ISNULL(z.QTY, 0) > 0
                          ) AS RESERVE_LOC_TOP
                        , (
                            SELECT COUNT(*)
                                FROM [spex].[TB_R_BINNING] z
                                INNER JOIN [SPEX_DB].[spex].[TB_R_PART_STOCK_INFO] y
                                    ON z.PART_NO = y.PART_NO
                                WHERE z.PART_NO = b.PART_NO
                                    AND ( z.SOURCE = 'NON-DIRECT')
                                    AND ISNULL(y.SONDR, 0) > 0
                                    AND ISNULL(z.QTY, 0) > 0
                          ) AS RESERVE_LOC_COUNT
                            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 ISNULL(b.SOR, 0) < ISNULL(c.MAX_STOCK, 0)
                                    AND (ISNULL(b.SONDR, 0) > 0)
                                    AND LEFT(c.RACK_ADDRESS_CD, 1) = @ZONE_CD
                                    AND ISNULL(a.REFILL_QTY, 0) < ISNULL(a.QTY, 0)
                                    --AND a.MANIFEST_NO IS NOT NULL
                ) y
                UNION
                SELECT
                    b.[PART_NO]
                    , b.RACK_ADDRESS_CD AS PRIMARY_LOC
                    , ISNULL(b.[SOH], 0) AS SOH
                    , ISNULL(b.[SOR], 0) AS SOR
                    , ISNULL(b.[MAX_STOCK], 0) AS MAX_STOCK
                    , ISNULL(b.[SODR], 0) AS SODR
                    , ISNULL(b.[SONDR], 0) AS SONDR
                    , 0 AS IS_PROBLEM_SHORTAGE
                    , (
                        SELECT TOP 1 z.RACK_ADDRESS
                            FROM [spex].[TB_R_BINNING] z
                            INNER JOIN [SPEX_DB].[spex].[TB_R_PART_STOCK_INFO] y
                                ON z.PART_NO = y.PART_NO
                            WHERE z.PART_NO = b.PART_NO
                                AND ( z.SOURCE = 'NON-DIRECT')
                                AND ISNULL(y.SONDR, 0) > 0
                                AND ISNULL(z.QTY, 0) > 0
                      ) AS RESERVE_LOC_TOP
                    , (
                        SELECT COUNT(*)
                            FROM [spex].[TB_R_BINNING] z
                            INNER JOIN [SPEX_DB].[spex].[TB_R_PART_STOCK_INFO] y
                                ON z.PART_NO = y.PART_NO
                            WHERE z.PART_NO = b.PART_NO
                                AND ( z.SOURCE = 'NON-DIRECT')
                                AND ISNULL(y.SONDR, 0) > 0
                                AND ISNULL(z.QTY, 0) > 0
                      ) AS RESERVE_LOC_COUNT
                        FROM (
                            SELECT
                                R_PART_STOCK_INFO.[PART_NO]
                                , M_PART_STOCK.RACK_ADDRESS_CD
                                , R_PART_STOCK_INFO.[SOH]
                                , R_PART_STOCK_INFO.[SOR]
                                , M_PART_STOCK.[MAX_STOCK]
                                , R_PART_STOCK_INFO.[SODR]
                                , R_PART_STOCK_INFO.[SONDR]
                                    FROM [SPEX_DB].[spex].[TB_R_PART_STOCK_INFO] R_PART_STOCK_INFO
                                    INNER JOIN [SPEX_DB].[spex].[TB_M_PART_STOCK] AS M_PART_STOCK
                                        ON R_PART_STOCK_INFO.PART_NO = M_PART_STOCK.PART_NO
                            WHERE ISNULL(R_PART_STOCK_INFO.SOR, 0) < ISNULL(M_PART_STOCK.MAX_STOCK, 0)
                            AND (ISNULL(R_PART_STOCK_INFO.SONDR, 0) > 0)
                            AND LEFT(M_PART_STOCK.RACK_ADDRESS_CD, 1) = @ZONE_CD
                            AND R_PART_STOCK_INFO.PART_NO NOT IN (
                                SELECT
                                    a.[PART_NO]
                                    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 ISNULL(b.SOR, 0) < ISNULL(c.MAX_STOCK, 0)
                                            AND (ISNULL(b.SONDR, 0) > 0)
                                            AND LEFT(c.RACK_ADDRESS_CD, 1) = @ZONE_CD
                                            AND ISNULL(a.REFILL_QTY, 0) < ISNULL(a.QTY, 0)
                            )
                    ) b
                --WHERE NOT EXISTS (SELECT 1 FROM [SPEX_DB].[spex].[TB_R_REPORT_PROBLEM] w where w.PART_NO = b.PART_NO)
            ) AS z
            WHERE ISNULL(z.RESERVE_LOC_COUNT, 0) > 0
            ORDER BY z.[IS_PROBLEM_SHORTAGE] DESC, z.RESERVE_LOC_COUNT DESC

END
GO

EXECUTE spex.SP_AMANAH_RE_FILL_3_LIST 'M'