AKI-REPRINT-1

USE SPEX_DB;

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

CREATE PROCEDURE spex.SP_AMANAH_RE_PRINT_3_SELECT_ZONE
AS
BEGIN

    SET NOCOUNT ON;
    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_R_DAILY_ORDER_PART] b
                        ON a.[ZONE_CD] = LEFT(b.[PART_ADDRESS], 1)
                INNER JOIN
                    (
                        SELECT
                            MANIFEST_NO,
                            MANIFEST_TYPE,
                            PICKING_FLAG
                            FROM
                                [SPEX_DB].[spex].[TB_R_DAILY_ORDER_MANIFEST]
                                    WHERE
                                        [MANIFEST_TYPE] = 'PH' AND
                                        isnull([PICKING_FLAG], '0') <> '2'
                    ) AS c ON b.[MANIFEST_NO] = c.[MANIFEST_NO]
            WHERE b.[REMAIN_PICKING_QTY] > 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_R_DAILY_ORDER_PART] b
                        ON a.[ZONE_CD] = LEFT(b.[PART_ADDRESS], 1)
                INNER JOIN
                    (
                        SELECT
                            MANIFEST_NO,
                            MANIFEST_TYPE,
                            PICKING_FLAG
                            FROM
                                [SPEX_DB].[spex].[TB_R_DAILY_ORDER_MANIFEST]
                                    WHERE
                                        [MANIFEST_TYPE] = 'PH' AND
                                        isnull([PICKING_FLAG], '0') <> '2'
                    ) AS c ON b.[MANIFEST_NO] = c.[MANIFEST_NO]
        WHERE b.[REMAIN_PICKING_QTY] > 0
    ) x ORDER BY x.ZONE ASC

END
GO

EXECUTE spex.SP_AMANAH_RE_PRINT_3_SELECT_ZONE