AKI-REPRINT-4

USE SPEX_DB;

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

CREATE PROCEDURE spex.SP_AMANAH_RE_PRINT_5_LIST
    @ZONE VARCHAR(MAX) = '',
    @MANIFEST_NO VARCHAR(MAX)
AS
BEGIN

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

    BEGIN TRY
        SELECT
            ROW_NUMBER() OVER (
                ORDER BY SUBSTRING(a.[PART_ADDRESS], 1, 1),
                SUBSTRING(a.[PART_ADDRESS], 3, 3),
                SUBSTRING(a.[PART_ADDRESS], 7, 1),
                SUBSTRING(a.[PART_ADDRESS], 9, 1)
            ) NOURUT,
            b.TMMIN_ORDER_NO,
            a.[PL_CODE],
            a.[MANIFEST_ITEM_NO],
            a.[MANIFEST_NO],
            b.BUYER_PD,
            b.[MANIFEST_TYPE],
            CONVERT(DATE, b.CREATED_DT) AS ISSUE_DATE,
            FORMAT(b.CREATED_DT, 'HH:mm') AS BATCH_TYPE,
            a.PART_ADDRESS,
            b.DEST_NAME,
            a.IMPORTIR_INFO3 AS TRANS_CODE,
            a.[PART_NO],
            b.TOTAL_ITEM,
            a.[REMAIN_PICKING_QTY] AS QTY -- SEBAGAI TOTAL_QTY
            -- TOTAL_COUNT = COUNT(*) OVER()
            INTO ##TB_P_PICKING_LABEL
                FROM [SPEX_DB].[spex].[TB_R_DAILY_ORDER_PART] a
                    INNER JOIN
                        [SPEX_DB].[spex].[TB_R_DAILY_ORDER_MANIFEST] b
                            ON a.[MANIFEST_NO] = b.[MANIFEST_NO]
                        JOIN [SPEX_DB].[dbo].[TB_M_SYSTEM] c ON b.[MANIFEST_TYPE] = c.[SYSTEM_VALUE]
                        WHERE a.[MANIFEST_NO] = @MANIFEST_NO
                            AND LEFT(a.[PART_ADDRESS], 1) = @ZONE
                            AND c.[SYSTEM_TYPE] = 'PICKING_LIST_CODE'
                            AND a.[REMAIN_PICKING_QTY] > 0
                            AND a.[PL_CODE] IS NOT NULL
        SELECT
            *
        FROM ##TB_P_PICKING_LABEL;
    END TRY
    BEGIN CATCH
        PRINT 'FAILURE: Record was not inserted.';
        PRINT 'Error ' + CONVERT(VARCHAR, ERROR_NUMBER(), 1) + ': '+ ERROR_MESSAGE()
    END CATCH

END
GO
EXECUTE spex.SP_AMANAH_RE_PRINT_5_LIST 'S', 'PH20B05S01001';