VrGLdB1H

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_4_PH_SEARCH'
8    AND ROUTINE_TYPE = N'PROCEDURE'
9)
10DROP PROCEDURE spex.SP_AMANAH_RE_PRINT_4_PH_SEARCH
11GO
12
13CREATE PROCEDURE spex.SP_AMANAH_RE_PRINT_4_PH_SEARCH
14    @ZONE VARCHAR(MAX) = '',
15    @MANIFEST_NO VARCHAR(MAX) = ''
16AS
17BEGIN
18    DECLARE @MSG_TEXT VARCHAR(max) = ''
19		,@PARAM1 VARCHAR(max) = ''
20		,@PARAM2 VARCHAR(max) = ''
21		,@PARAM3 VARCHAR(max) = ''
22		,@N_ERR INT = 0
23		,@IS_ERR CHAR(1) = 'N'
24		,@DATEPARAM VARCHAR(MAX)
25
26	SET @DATEPARAM = CONVERT(VARCHAR, GETDATE(), 112)
27
28	IF OBJECT_ID('tempdb..#TB_T_DATA') IS NOT NULL
29		DROP TABLE #TB_T_DATA
30
31	CREATE TABLE #TB_T_DATA (
32		Result VARCHAR(max),
33		Message VARCHAR(max)
34	)
35
36    BEGIN TRY
37		BEGIN TRANSACTION
38
39            IF EXISTS (
40                SELECT
41                    a.[MANIFEST_NO],
42                    a.[PRINT_FLAG],
43                    TOTAL_COUNT = COUNT(*) OVER()
44                        FROM [SPEX_DB].[spex].[TB_R_DAILY_ORDER_MANIFEST] a
45                            INNER JOIN [SPEX_DB].[spex].[TB_R_DAILY_ORDER_PART] b
46                                ON b.[MANIFEST_NO] = a.[MANIFEST_NO]
47                        WHERE
48                            a.[MANIFEST_TYPE] = 'PH' AND
49                            ISNULL(a.[PICKING_FLAG], '0') <> '2' AND
50                            LEFT(b.[PART_ADDRESS], 1) = @ZONE AND
51                            a.[MANIFEST_NO] = @MANIFEST_NO AND
52                            b.[REMAIN_PICKING_QTY] > 0
53            )
54                BEGIN
55                    SET @PARAM1 = ERROR_MESSAGE()
56
57                    INSERT INTO #TB_T_DATA
58                    VALUES (
59                        'Success'
60                        ,@MSG_TEXT
61                        )
62                END
63            ELSE
64                BEGIN
65                    SET @PARAM1 = ERROR_MESSAGE()
66
67                    INSERT INTO #TB_T_DATA
68                    VALUES (
69                        'Failed'
70                        ,@MSG_TEXT
71                        )
72                END
73        COMMIT TRANSACTION
74	END TRY
75
76    BEGIN CATCH
77		ROLLBACK TRANSACTION
78
79		SET @PARAM1 = ERROR_MESSAGE()
80
81		INSERT INTO #TB_T_DATA
82		VALUES (
83			'Failed'
84			,@MSG_TEXT
85			)
86	END CATCH
87
88    SELECT Result
89		,Message
90	FROM #TB_T_DATA
91END
92GO
93
94EXECUTE spex.SP_AMANAH_RE_PRINT_4_PH_SEARCH 'M', 'PH20B05M01002';