-115 Removals
+30 Additions
1USE [AAD] 1USE [AAD]
2GO 2GO
3 3
4/****** Object: StoredProcedure [dbo].[usp_vo_get_destination_location] Script Date: 10/08/2013 14:42:08 ******/ 4/****** Object: StoredProcedure [dbo].[usp_vo_assign_user_trolley] Script Date: 10/08/2013 14:39:29 ******/
5SET ANSI_NULLS ON 5SET ANSI_NULLS ON
6GO 6GO
7 7
8SET QUOTED_IDENTIFIER ON 8SET QUOTED_IDENTIFIER ON
9GO 9GO
10 10
11 11
12/* 12/*
13 BTH 10-11-2013 13BTH
14 14Update the trolley's entry in t_hu_master by settings it's user_id to the current operator.
15 Get the destination put location on the operator's current carton.
16 If the location is in the operator's current zone, return check digits.
17 If not in the zone, check the lane. A lane may or may not encompass
18 multiple zones (this depends on the area of the warehouse). If the
19 location exists in the lane, return code to prompt user to pass carton
20 to next zone. If not in the lane, return data error.
21*/ 15*/
22CREATE PROCEDURE [dbo].[usp_vo_get_destination_location] 16CREATE PROCEDURE [dbo].[usp_vo_assign_user_trolley]
23@in_time_stamp DATETIME, 17@in_time_stamp DATETIME,
24@in_device_serial NVARCHAR(30), 18@in_device_serial NVARCHAR(30),
25@in_operator NVARCHAR(30), 19@in_operator NVARCHAR(30),
26@in_msg_id NVARCHAR(100), 20@in_msg_id NVARCHAR(100),
27@in_language_code NVARCHAR(5), 21@in_language_code NVARCHAR(5),
28@in_wh_id NVARCHAR(10), 22@in_wh_id NVARCHAR(10),
29@in_hu_id NVARCHAR(50), 23@in_trolley_hu_id NVARCHAR(50)
30@in_zone NVARCHAR(50),
31@in_lane NVARCHAR(20)
32AS 24AS
33 25
34 26
35/********************** 27/**********************
36Sample Call 28Sample Call
37 29
38************************/ 30************************/
39 31
40 32
41DECLARE @errorcode INT 33DECLARE @errorcode INT
42DECLARE @message NVARCHAR(250) 34DECLARE @message NVARCHAR(250)
43DECLARE @errorname NVARCHAR (30) 35DECLARE @errorname NVARCHAR (30)
44 36
45DECLARE @DEADLOCK NVARCHAR(20), 37DECLARE @DEADLOCK NVARCHAR(20),
46 @SQLERROR NVARCHAR(20), 38 @SQLERROR NVARCHAR(20),
47 @NOPUTLOCCART NVARCHAR(50),
48 @PASSTONEXTZONE NVARCHAR(50),
49 @PUTLOCNOTINLANE NVARCHAR(50),
50 --Error messages 39 --Error messages
51 @current_xml_response XML 40 @current_xml_response XML
52
53DECLARE @destination_location NVARCHAR(100),
54 @destination_zone NVARCHAR(50),
55 @destination_check_digits NVARCHAR(10)
56 41
57SELECT 42SELECT
58 @DEADLOCK = 'DEADLOCK', 43 @DEADLOCK = 'DEADLOCK',
59 @SQLERROR = 'SQL_ERROR', 44 @SQLERROR = 'SQL_ERROR'
60 @NOPUTLOCCART = 'NO_PUT_LOC_CART',
61 @PASSTONEXTZONE = 'PASS_TO_NEXT_ZONE',
62 @PUTLOCNOTINLANE = 'PUT_LOC_NOT_IN_LANE'
63 45
64SET @errorcode = 0 46SET @errorcode = 0
65SET @message = '' 47SET @message = ''
66 48
67DECLARE @process_name NVARCHAR(100) 49DECLARE @process_name NVARCHAR(100)
68SET @process_name = 'usp_vo_get_destination_location' 50SET @process_name = 'usp_vo_assign_user_trolley'
69 51
70--Standard Pre Execution Setup Steps Here 52--Standard Pre Execution Setup Steps Here
71DECLARE @device_number NVARCHAR(30) 53DECLARE @device_number NVARCHAR(30)
72EXEC usp_vo_get_device_number @in_device_serial, @device_number OUTPUT 54EXEC usp_vo_get_device_number @in_device_serial, @device_number OUTPUT
73--End Standard Setup Steps 55--End Standard Setup Steps
74 56
75DECLARE @numtries INT, @jumpto NVARCHAR(50) 57DECLARE @numtries INT, @jumpto NVARCHAR(50)
76SELECT @numtries = next_value, @jumpto = 'START' 58SELECT @numtries = next_value, @jumpto = 'START'
77 FROM t_control (NOLOCK) 59 FROM t_control (NOLOCK)
78 WHERE control_type = 'MAX_DEADLOCK_RETRY' 60 WHERE control_type = 'MAX_DEADLOCK_RETRY'
79 61
80START: 62START:
81BEGIN TRY 63BEGIN TRY
82 64
83 DECLARE @RC int, 65 DECLARE @RC INT,
84 @out_device_number NVARCHAR(10), 66 @out_device_number NVARCHAR(10),
85 @out_duplicate_flag BIT, 67 @out_duplicate_flag BIT,
86 @out_last_tran_datetime DATETIME, 68 @out_last_tran_datetime DATETIME,
87 @out_xml_response XML, 69 @out_xml_response XML,
88 @wh_id NVARCHAR (10), 70 @wh_id NVARCHAR (10),
89 @lane NVARCHAR (10) 71 @lane NVARCHAR (10)
90 72
91 73
92 EXECUTE @RC = usp_vo_core_pre_voice 74 EXECUTE @RC = usp_vo_core_pre_voice
93 @process_name 75 @process_name
94 ,@in_device_serial 76 ,@in_device_serial
95 ,@in_msg_id 77 ,@in_msg_id
96 ,@out_device_number OUTPUT 78 ,@out_device_number OUTPUT
97 ,@out_duplicate_flag OUTPUT 79 ,@out_duplicate_flag OUTPUT
98 ,@out_last_tran_datetime OUTPUT 80 ,@out_last_tran_datetime OUTPUT
99 ,@out_xml_response OUTPUT 81 ,@out_xml_response OUTPUT
100 82
101 --In Pallet Building, there will be a single destination put location associated with a carton. 83 UPDATE t_hu_master
102 SET @destination_location = ( SELECT TOP 1 pkd.planned_put_loc 84 SET user_id = @in_operator
103 FROM t_hu_master hum (NOLOCK) 85 WHERE hu_id = @in_trolley_hu_id
104 INNER JOIN t_pick_detail pkd (NOLOCK) 86 AND wh_id = @in_wh_id
105 ON pkd.container_id = hum.hu_id 87
106 AND pkd.wh_id = hum.wh_id
107 WHERE hum.hu_id = @in_hu_id
108 )
109
110 IF @destination_location IS NULL
111 BEGIN
112 --no put location for carton
113 SET @errorname = @NOPUTLOCCART
114 SET @errorcode = 2
115 GOTO ErrorHandler
116 END
117
118 --The destination location may be linked to multiple locations with a matching c1 field.
119 --Check if the destination location is in the same zone as the operator.
120 IF EXISTS( SELECT *
121 FROM t_zone_loca zl WITH (NOLOCK)
122 INNER JOIN t_location loc WITH (NOLOCK)
123 ON loc.location_id = zl.location_id
124 AND loc.wh_id = zl.wh_id
125 WHERE zl.zone= @in_zone
126 AND zl.wh_id = @in_wh_id
127 AND loc.c1 = @destination_location
128 )
129 BEGIN
130 --The prompting for check digits of a store will require the carton and operator be in
131 --the same zone.
132 SET @destination_check_digits = ( SELECT loc.check_digits
133 FROM t_zone_loca tzl (nolock)
134 inner join t_location loc (nolock)
135 ON loc.location_id = tzl.location_id
136 AND loc.wh_id = tzl.wh_id
137 WHERE tzl.zone = @in_zone
138 and loc.c1 = @destination_location
139 and tzl.wh_id = loc.wh_id
140 )
141 END
142
143 ELSE
144 --Destination location is in another zone. Check lane.
145 IF EXISTS ( SELECT *
146 FROM t_zone_loca zl WITH (NOLOCK)
147 INNER JOIN t_location loc WITH (NOLOCK)
148 ON loc.location_id = zl.location_id
149 AND loc.wh_id = zl.wh_id
150 WHERE lane = @in_lane
151 AND loc.c1 = @destination_location
152 AND zl.zone != 'ALL'
153 AND zl.wh_id = @in_wh_id
154 )
155 BEGIN
156 SET @errorname = @PASSTONEXTZONE
157 SET @errorcode = 3
158 GOTO ErrorHandler
159 END
160
161 ELSE
162 --ERROR: WRONG_SHIPPING_SPUR
163 SET @errorname = @PUTLOCNOTINLANE
164 SET @errorcode = 4
165 GOTO ErrorHandler
166
167 GOTO ExitLabel 88 GOTO ExitLabel
168END TRY 89END TRY
169BEGIN CATCH 90BEGIN CATCH
170 IF @@TRANCOUNT>0 91 IF @@TRANCOUNT>0
171 ROLLBACK TRAN 92 ROLLBACK TRAN
172 93
173 --Catch deadlock error and try again 94 --Catch deadlock error and try again
174 IF ERROR_NUMBER() = '1205' AND @numtries > 0 95 IF ERROR_NUMBER() = '1205' AND @numtries > 0
175 BEGIN 96 BEGIN
176 SET @numtries = @numtries - 1 97 SET @numtries = @numtries - 1
177 GOTO START 98 GOTO START
178 END 99 END
179 100
180 IF ERROR_NUMBER() = '1205' 101 IF ERROR_NUMBER() = '1205'
181 BEGIN 102 BEGIN
182 SET @errorname=@DEADLOCK 103 SET @errorname=@DEADLOCK
183 SET @errorcode = 1205 104 SET @errorcode = 1205
184 END 105 END
185 106
186 SET @errorname = ISNULL(@errorname,@SQLERROR) 107 SET @errorname = ISNULL(@errorname,@SQLERROR)
187 108
188 GOTO ErrorHandler 109 GOTO ErrorHandler
189END CATCH 110END CATCH
190 111
191ErrorHandler: 112ErrorHandler:
192 IF @errorcode = 0 113 IF @errorcode = 0
193 SET @errorcode = 1 114 SET @errorcode = 1
194 115
195 EXEC usp_vo_core_get_error_message_embedded 'ERROR',@errorname,'en_US',@message OUTPUT 116 EXEC usp_vo_core_get_error_message_embedded 'ERROR',@errorname,'en_US',@message OUTPUT
196 117
197 IF @@ERROR <> 0 118 IF @@ERROR <> 0
198 BEGIN 119 BEGIN
199 SET @message = 'unknown error' 120 SET @message = 'unknown error'
200 END 121 END
201 122
202 123
203ExitLabel: 124ExitLabel:
204 SELECT @current_xml_response = ( 125 SELECT @current_xml_response = (
205 SELECT 126 SELECT
206 @destination_location as destination_location,
207
208 --When a destination location is found in a different zone (but the same lane),
209 --the user is prompted to pass the carton to that zone.
210 @destination_zone as destination_zone,
211 @destination_check_digits as destination_check_digits,
212 @errorcode as error_code, 127 @errorcode as error_code,
213 @message as error_msg 128 @message as error_msg
214 FOR XML RAW 129 FOR XML RAW
215 ) 130 )
216 131
217 SELECT @destination_location, @destination_zone, @destination_check_digits, @errorcode, @message 132 SELECT @errorcode,@message
218 133
219 --Update Device Status 134 --Update Device Status
220 DECLARE @in_tran_date DATETIME, 135 DECLARE @in_tran_date datetime,
221 @in_break_flag NVARCHAR(10), 136 @in_break_flag nvarchar(10),
222 @in_wh_id_2 NVARCHAR(10), 137 @in_wh_id_2 nvarchar(10),
223 @in_vehicle_2 NVARCHAR(10) 138 @in_vehicle_2 nvarchar(10)
224 139
225 SELECT @in_tran_date = NULL, 140 SELECT @in_tran_date = NULL,
226 @in_break_flag = 0, 141 @in_break_flag = 0,
227 @in_wh_id_2 = NULL, 142 @in_wh_id_2 = NULL,
228 @in_vehicle_2 = NULL 143 @in_vehicle_2 = NULL
229 144
230 EXECUTE @RC = usp_vo_core_post_voice 145 EXECUTE @RC = usp_vo_core_post_voice
231 @process_name 146 @process_name
232 ,@out_device_number 147 ,@out_device_number
233 ,@in_msg_id 148 ,@in_msg_id
234 ,@current_xml_response 149 ,@current_xml_response
235 ,@in_tran_date 150 ,@in_tran_date
236 ,@in_operator 151 ,@in_operator
237 ,@in_break_flag 152 ,@in_break_flag
238 ,@in_wh_id_2 153 ,@in_wh_id_2
239 154
240RETURN 155RETURN
241 156
242 157
243 158
244 159
245 160
246 161
247 162
248GO 163GO
Editor
Original Text
Changed Text
Recommended videos