2Xwd4Htv

1USE SPEX_DB;
2
3IF EXISTS (
4SELECT *
5    FROM INFORMATION_SCHEMA.ROUTINES
6WHERE SPECIFIC_SCHEMA = N'spex'
7    AND SPECIFIC_NAME = N'SP_AMANAH_RE_PRINT_5_LIST'
8    AND ROUTINE_TYPE = N'PROCEDURE'
9)
10DROP PROCEDURE spex.SP_AMANAH_RE_PRINT_5_LIST
11GO
12
13CREATE PROCEDURE spex.SP_AMANAH_RE_PRINT_5_LIST
14    @ZONE VARCHAR(MAX) = '',
15    @MANIFEST_NO VARCHAR(MAX)
16AS
17BEGIN
18
19    IF OBJECT_ID('tempdb..##TB_P_PICKING_LABEL') IS NOT NULL
20    DROP TABLE ##TB_P_PICKING_LABEL
21
22    BEGIN TRY
23        SELECT
24            ROW_NUMBER() OVER (
25                ORDER BY SUBSTRING(a.[PART_ADDRESS], 1, 1),
26                SUBSTRING(a.[PART_ADDRESS], 3, 3),
27                SUBSTRING(a.[PART_ADDRESS], 7, 1),
28                SUBSTRING(a.[PART_ADDRESS], 9, 1)
29            ) NOURUT,
30            b.TMMIN_ORDER_NO,
31            a.[PL_CODE],
32            a.[MANIFEST_ITEM_NO],
33            a.[MANIFEST_NO],
34            b.BUYER_PD,
35            b.[MANIFEST_TYPE],
36            CONVERT(DATE, b.CREATED_DT) AS ISSUE_DATE,
37            FORMAT(b.CREATED_DT, 'HH:mm') AS BATCH_TYPE,
38            a.PART_ADDRESS,
39            b.DEST_NAME,
40            a.IMPORTIR_INFO3 AS TRANS_CODE,
41            a.[PART_NO],
42            b.TOTAL_ITEM,
43            a.[REMAIN_PICKING_QTY] as QTY -- SEBAGAI TOTAL_QTY
44            -- TOTAL_COUNT = COUNT(*) OVER()
45            INTO ##TB_P_PICKING_LABEL
46                FROM [SPEX_DB].[spex].[TB_R_DAILY_ORDER_PART] a
47                    INNER JOIN
48                        [SPEX_DB].[spex].[TB_R_DAILY_ORDER_MANIFEST] b
49                            ON a.[MANIFEST_NO] = b.[MANIFEST_NO]
50						JOIN [SPEX_DB].[dbo].[TB_M_SYSTEM] c on b.[MANIFEST_TYPE] = c.[SYSTEM_VALUE]
51                        WHERE a.[MANIFEST_NO] = @MANIFEST_NO
52                            AND LEFT(a.[PART_ADDRESS], 1) = @ZONE
53                            AND c.[SYSTEM_TYPE] = 'PICKING_LIST_CODE'
54                            AND a.[REMAIN_PICKING_QTY] > 0
55                            AND a.[PL_CODE] IS NOT NULL
56        SELECT
57            *
58        FROM ##TB_P_PICKING_LABEL;
59    END TRY
60    BEGIN CATCH
61        PRINT 'FAILURE: Record was not inserted.';
62        PRINT 'Error ' + CONVERT(VARCHAR, ERROR_NUMBER(), 1) + ': '+ ERROR_MESSAGE()
63    END CATCH
64
65END
66GO
67EXECUTE spex.SP_AMANAH_RE_PRINT_5_LIST 'S', 'PH20B05S01001';