[Home] [Help]
PACKAGE BODY: APPS.WSH_SHIPPING_INFO
Source
1 PACKAGE BODY WSH_SHIPPING_INFO as
2 /* $Header: WSHSHINB.pls 120.2 2006/12/09 00:14:49 rlanka noship $ */
3
4 /*
5 -- FILENAME
6 --
7 -- WSHSHINB.pls
8 --
9 -- DESCRIPTION
10 --
11 -- Body of package WSH_SHIPPING_INFO
12 --
13 -- NOTES
14 --
15 -- HISTORY
16 --
17 -- Aug 15, 2001 Raju Varghese Bug#1924574 for hr_locations
18 -- : Removed Calls to hr_locations
19 -- : Using API WSH_UTIL_CORE.get_location_desriptions instead for HR changes
20 -- and performance Reasons.
21 --
22 */
23
24 --
25 -- Package exceptions
26 --
27
28 wsh_tracking_exception EXCEPTION;
29
30 wsh_unexpected_error EXCEPTION;
31
32 --
33 -- Procedure: Fill_Track_Record
34 -- Parameters: p_record_number - Tracking record number
35 -- p_delivery_status - Delivery status
36 -- p_trip_name - Trip name
37 -- p_location_name - Location name
38 -- p_actual_arrival_date - Actual arrival date
39 -- p_actual_departure_date - Actual departure date
40 -- p_ship_method_code - Ship method code
41 -- p_carrier_name - Carrier Name (For Bug 5697730)
42 -- p_bill_of_lading - Bill of Lading
43 -- x_tracking_details - Tracking Record
44 -- Description: This procedure will populates the Tracking
45 -- record table with information based on the
46 -- record number
47 --
48
49 --
50 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_SHIPPING_INFO';
51 --
52 PROCEDURE Fill_Track_Record
53 (p_record_number IN VARCHAR2,
54 p_delivery_status IN VARCHAR2 ,
55 p_trip_name IN VARCHAR2 ,
56 p_location_name IN VARCHAR2 ,
57 p_actual_arrival_date IN DATE ,
58 p_actual_departure_date IN DATE ,
59 p_ship_method_code IN VARCHAR2 ,
60 p_bill_of_lading IN VARCHAR2 ,
61 p_carrier_name IN VARCHAR2, -- Bug 5697730
62 x_tracking_details IN OUT NOCOPY Tracking_Info_Tab_Typ
63 ) IS
64
65 --
66 l_debug_on BOOLEAN;
67 --
68 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'FILL_TRACK_RECORD';
69 --
70 BEGIN
71
72 --
73 -- Debug Statements
74 --
75 --
76 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
77 --
78 IF l_debug_on IS NULL
79 THEN
80 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
81 END IF;
82 --
83 IF l_debug_on THEN
84 WSH_DEBUG_SV.push(l_module_name);
85 --
86 WSH_DEBUG_SV.log(l_module_name,'P_RECORD_NUMBER',P_RECORD_NUMBER);
87 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_STATUS',P_DELIVERY_STATUS);
88 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_NAME',P_TRIP_NAME);
89 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_NAME',P_LOCATION_NAME);
90 WSH_DEBUG_SV.log(l_module_name,'P_ACTUAL_ARRIVAL_DATE',P_ACTUAL_ARRIVAL_DATE);
91 WSH_DEBUG_SV.log(l_module_name,'P_ACTUAL_DEPARTURE_DATE',P_ACTUAL_DEPARTURE_DATE);
92 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_METHOD_CODE',P_SHIP_METHOD_CODE);
93 WSH_DEBUG_SV.log(l_module_name,'P_BILL_OF_LADING',P_BILL_OF_LADING);
94 WSH_DEBUG_SV.log(l_module_name,'P_CARRIER_NAME',P_CARRIER_NAME);
95 END IF;
96 --
97 IF p_record_number IS NULL THEN
98 FND_MESSAGE.Set_Name('WSH','WSH_UNEXPECTED_ERROR');
99 Raise Wsh_Tracking_Exception;
100 END IF;
101
102 x_tracking_details(p_record_number).delivery_status := p_delivery_status;
103
104 x_tracking_details(p_record_number).trip_name := p_trip_name;
105
106 x_tracking_details(p_record_number).location_name := p_location_name;
107
108 x_tracking_details(p_record_number).actual_arrival_date
109 := p_actual_arrival_date;
110
111 x_tracking_details(p_record_number).actual_departure_date
112 := p_actual_departure_date;
113
114 x_tracking_details(p_record_number).ship_method_code
115 := p_ship_method_code;
116
117 x_tracking_details(p_record_number).bill_of_lading
118 := p_bill_of_lading;
119 --
120 -- Bug 5697730
121 x_tracking_details(p_record_number).carrier_name := p_carrier_name;
122 --
123
124 --
125 -- Debug Statements
126 --
127 IF l_debug_on THEN
128 WSH_DEBUG_SV.pop(l_module_name);
129 END IF;
130 --
131 END Fill_Track_Record;
132
133
134 --
135 -- Procedure: Track_Delivery
136 -- Parameters: p_delivery_name - Name of Delivery to track
137 -- p_mode - 'FULL' or 'CURRENT'
138 -- x_tracking_details - Record of all the tracking
139 -- details for a shipment
140 -- Description: This procedure will provide tracking information
141 -- for a delivery
142 --
143
144 PROCEDURE Track_Delivery
145 (p_delivery_name IN VARCHAR2,
146 p_mode IN VARCHAR2,
147 x_tracking_details OUT NOCOPY Tracking_Info_Tab_Typ
148 ) IS
149 -- 1924574 Changes: Removed the hr_locations join and passing dl.initial_pickup_location_id
150 -- to WSH_UTIL_CORE.get_location_description
151 CURSOR get_delivery_info (v_delivery_name VARCHAR2) IS
152 SELECT wl.meaning status,
153 to_char(NULL) name,
154 WSH_UTIL_CORE.get_location_description(dl.initial_pickup_location_id,'CSZ') pickup_loc,
155 to_date(NULL) pu_arrival_date,
156 to_date(NULL) pu_departure_date,
157 to_char(NULL) dropoff_loc,
158 to_date(NULL) do_arrival_date,
159 to_date(NULL) do_departure_date,
160 dl.ship_method_code,
161 hp.party_name carrier_name, -- Bug 5697730
162 to_char(NULL) bill_of_lading
163 FROM wsh_lookups wl,
164 wsh_new_deliveries dl,
165 hz_parties hp, hz_party_usg_assignments hpu
166 WHERE dl.name = v_delivery_name
167 AND hp.party_id(+) = dl.carrier_id -- Bug 5697730
168 AND hp.party_id = hpu.party_id(+) -- Bug 5697730
169 AND hpu.party_usage_code(+) = 'TRANSPORTATION_PROVIDER' -- Bug 5697730
170 AND wl.lookup_type = 'DELIVERY_STATUS'
171 AND wl.lookup_code = dl.status_code
172 AND nvl(dl.SHIPMENT_DIRECTION , 'O') IN ('O', 'IO') -- J inbound logistics jckwok
173 AND dl.status_code not in ('CL','IT'); -- sperera 940/945 -- sperera 940/945
174
175 l_del get_delivery_info%ROWTYPE;
176
177 -- 1924574 Changes: Removed the joins with hr_locations join and passing ts1,ts2.stop_location_id
178 -- to WSH_UTIL_CORE.get_location_description
179
180 -- Bug 3146273 : Removed the wsh_document_instances table from the Cursor
181 -- "get_shipped_delivery_info"
182 -- and added new cursor "get_bill_of_lading" to get the sequence_number from the
183 -- wsh_document_instances table
184
185 CURSOR get_bill_of_lading(v_delivery_leg_id VARCHAR2) IS
186 SELECT sequence_number bill_of_lading
187 FROM wsh_document_instances
188 WHERE entity_id = v_delivery_leg_id
189 AND entity_name = 'WSH_DELIVERY_LEGS'
190 AND document_type= 'BOL';
191
192 CURSOR get_trip_delivery_info (v_delivery_name VARCHAR2) IS
193 SELECT wl.meaning status,
194 t.name,
195 WSH_UTIL_CORE.get_location_description(ts1.stop_location_id,'CSZ') pickup_loc,
196 ts1.actual_arrival_date pu_arrival_date,
197 ts1.actual_departure_date pu_departure_date,
198 WSH_UTIL_CORE.get_location_description(ts2.stop_location_id,'CSZC') dropoff_loc,
199 ts2.actual_arrival_date do_arrival_date,
200 ts2.actual_departure_date do_departure_date,
201 t.ship_method_code,
202 hp.party_name carrier_name, -- Bug 5697730
203 dg.delivery_leg_id
204 FROM wsh_lookups wl,
205 wsh_trips t,
206 wsh_trip_stops ts1,
207 wsh_trip_stops ts2,
208 -- wsh_document_instances di, -- Bug 3146273
209 wsh_delivery_legs dg,
210 wsh_new_deliveries dl,
211 hz_parties hp, hz_party_usg_assignments hpu -- Bug 5697730
212 WHERE dl.name = v_delivery_name
213 AND hp.party_id(+) = t.carrier_id -- Bug 5697730
214 AND hp.party_id = hpu.party_id(+) -- Bug 5697730
215 AND hpu.party_usage_code(+) = 'TRANSPORTATION_PROVIDER' -- Bug 5697730
216 AND dg.delivery_id = dl.delivery_id
217 AND dg.pick_up_stop_id = ts1.stop_id
218 -- AND ts1.status_code = 'CL'
219 AND dg.drop_off_stop_id = ts2.stop_id
220 AND ts1.trip_id = t.trip_id
221 --AND dg.delivery_leg_id = di.entity_id(+) -- Bug 3146273
222 AND wl.lookup_type = 'DELIVERY_STATUS'
223 AND wl.lookup_code = dl.status_code
224 AND nvl(dl.SHIPMENT_DIRECTION , 'O') IN ('O', 'IO') -- J inbound logistics jckwok
225 ORDER BY ts1.planned_arrival_date asc;
226
227 l_trip_del get_trip_delivery_info%ROWTYPE;
228
229 l_tmpTrackLine Tracking_Info_Rec_Typ;
230 l_bill_of_lading wsh_document_instances.sequence_number%TYPE:= null;
231
232 i NUMBER;
233
234 --
235 l_debug_on BOOLEAN;
236 --
237 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'TRACK_DELIVERY';
238 --
239 BEGIN
240
241 -- First, get Unshipped Delivery Information
242 --
243 -- Debug Statements
244 --
245 --
246 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
247 --
248 IF l_debug_on IS NULL
249 THEN
250 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
251 END IF;
252 --
253 IF l_debug_on THEN
254 WSH_DEBUG_SV.push(l_module_name);
255 --
256 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_NAME',P_DELIVERY_NAME);
257 WSH_DEBUG_SV.log(l_module_name,'P_MODE',P_MODE);
258 END IF;
259 --
260 --Get Delivery With Trip Details
261 OPEN get_trip_delivery_info(p_delivery_name);
262 i := 0;
263 LOOP
264 FETCH get_trip_delivery_info
265 INTO l_trip_del;
266 EXIT WHEN get_trip_delivery_info%NOTFOUND;
267 -- BUG 3146273: jckwok: open get_bill_of_lading with new leg id
268 OPEN get_bill_of_lading (l_trip_del.delivery_leg_id);
269 l_bill_of_lading := null;
270 FETCH get_bill_of_lading
271 INTO l_bill_of_lading;
272 CLOSE get_bill_of_lading;
273 -- BUG 3146273: jckwok
274
275 -- Update Current record information, arrival date is
276 -- previous entries arrival date at location
277 i := i + 1;
278 Fill_Track_Record (
279 p_record_number => i,
280 p_delivery_status => l_trip_del.status,
281 p_trip_name => l_trip_del.name,
282 p_location_name => l_trip_del.pickup_loc,
283 p_actual_arrival_date => FND_API.G_MISS_DATE,
284 p_actual_departure_date => l_trip_del.pu_departure_date,
285 p_ship_method_code => l_trip_del.ship_method_code,
286 p_bill_of_lading => l_bill_of_lading,
287 p_carrier_name => l_trip_del.carrier_name, -- Bug 5697730
288 x_tracking_details => x_tracking_details);
289 i := i + 1;
290 Fill_Track_Record (
291 p_record_number => i,
292 p_delivery_status => l_trip_del.status,
293 p_trip_name => l_trip_del.name,
294 p_location_name => l_trip_del.dropoff_loc,
295 p_actual_arrival_date => l_trip_del.do_arrival_date,
296 p_actual_departure_date => NULL,
297 p_ship_method_code => NULL,
298 p_bill_of_lading => NULL,
299 p_carrier_name => NULL, -- Bug 5697730
300 x_tracking_details => x_tracking_details);
301 -- Avoid infinite loop problem by terminating on
302 -- large number
303 IF i = 100 THEN
304 FND_MESSAGE.Set_Name('WSH','WSH_UNEXP_ERROR');
305 RAISE wsh_unexpected_error;
306 END IF;
307 END LOOP;
308 CLOSE get_trip_delivery_info;
309
310 IF i=0 THEN --Get Delivery Without Trip
311 OPEN get_delivery_info(p_delivery_name);
312 FETCH get_delivery_info INTO l_del;
313 IF get_delivery_info%FOUND THEN
314 i := i + 1;
315 Fill_Track_Record (
316 p_record_number => i,
317 p_delivery_status => l_del.status,
318 p_trip_name => l_del.name,
319 p_location_name => l_del.pickup_loc,
320 p_actual_arrival_date => NULL,
321 p_actual_departure_date => l_del.pu_departure_date,
322 p_ship_method_code => l_del.ship_method_code,
323 p_bill_of_lading => l_del.bill_of_lading,
324 p_carrier_name => l_del.carrier_name, -- Bug 5697730
325 x_tracking_details => x_tracking_details);
326 END IF;
327 CLOSE get_delivery_info;
328 ELSE
329 IF p_mode = 'CURRENT' THEN
330 l_tmpTrackLine := x_tracking_details(x_tracking_details.LAST);
331 -- Bug: 1114924 since the last record in the table will always have NULL BOL and should be same as the previous one.
332 l_tmpTrackLine.bill_of_lading := x_tracking_details(x_tracking_details.count-1).bill_of_lading;
333 -- Bug: 1570332 since the last record in the table will always have NULL ship method code and should be same as the previous one.
334 l_tmpTrackLine.ship_method_code := x_tracking_details(x_tracking_details.count-1).ship_method_code;
335 l_tmpTrackLine.carrier_name := x_tracking_details(x_tracking_details.count-1).carrier_name; -- Bug 5697730
336 l_tmpTrackLine.actual_departure_date := x_tracking_details(x_tracking_details.count-1).actual_departure_date;
337 x_tracking_details.DELETE;
338 x_tracking_details(1) := l_tmpTrackLine;
339 END IF;
340 END IF;
341 IF(i=0) THEN
342 FND_MESSAGE.Set_Name('WSH','WSH_UNEXP_ERROR');
343 RAISE wsh_unexpected_error;
344 END IF;
345 --
346 -- Debug Statements
347 --
348 IF l_debug_on THEN
349 WSH_DEBUG_SV.pop(l_module_name);
350 END IF;
351 --
352 EXCEPTION
353 WHEN OTHERS THEN
354 IF get_trip_delivery_info%ISOPEN THEN
355 CLOSE get_trip_delivery_info;
356 END IF;
357 IF get_delivery_info%ISOPEN THEN
358 CLOSE get_delivery_info;
359 END IF;
360 IF get_bill_of_lading%ISOPEN THEN
361 CLOSE get_bill_of_lading;
362 END IF;
363 --
364 -- Debug Statements
365 --
366 IF l_debug_on THEN
367 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
368 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
369 END IF;
370 --
371 RAISE;
372
373 END Track_Delivery;
374
375 --
376 -- Procedure: Track_Shipment
377 -- Parameters: p_delivery_name - Name of Delivery to track
378 -- p_tracking_number_dd - Tracking Number of Delivery
379 -- Line
380 -- p_mode - 'FULL' or 'CURRENT'
381 -- 'FULL' Gives complete tracking information
382 -- 'CURRENT' Provides simple tracking information
383 -- a) If the delivery is not shipped, initial
384 -- trip/location information is provided
385 -- b) If the delivery is shipped, it provides
386 -- the current shipment information
387 -- c) If the delivery has been delivered, it
388 -- provides the final trip/location
389 -- information when delivered to the
390 -- customer
391 -- x_tracking_details - Record of all the tracking
392 -- details for a shipment
393 -- x_return_status - Status of procedure call
394 -- - FND_API.G_RET_STS_SUCCESS
395 -- - FND_API.G_RET_STS_ERROR
396 -- Description: This procedure will provide tracking information
397 -- for a shipment
398 --
399
400 PROCEDURE Track_Shipment
401 (p_delivery_name IN VARCHAR2 DEFAULT NULL,
402 p_tracking_number_dd IN VARCHAR2 DEFAULT NULL,
403 p_mode IN VARCHAR2,
404 x_tracking_details OUT NOCOPY Tracking_Info_Tab_Typ,
405 x_return_status OUT NOCOPY VARCHAR2
406 ) IS
407
408 l_delivery_name VARCHAR2(30);
409 --
410 l_debug_on BOOLEAN;
411 --
412 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'TRACK_SHIPMENT';
413 --
414 BEGIN
415 -- Setup parameters
416 --
417 -- Debug Statements
418 --
419 --
420 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
421 --
422 IF l_debug_on IS NULL
423 THEN
424 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
425 END IF;
426 --
427 IF l_debug_on THEN
428 WSH_DEBUG_SV.push(l_module_name);
429 --
430 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_NAME',P_DELIVERY_NAME);
431 WSH_DEBUG_SV.log(l_module_name,'P_TRACKING_NUMBER_DD',P_TRACKING_NUMBER_DD);
432 WSH_DEBUG_SV.log(l_module_name,'P_MODE',P_MODE);
433 END IF;
434 --
435 x_tracking_details.delete;
436 x_return_status := FND_API.G_RET_STS_SUCCESS;
437
438 -- Validate parameters
439 IF p_delivery_name IS NULL THEN
440 FND_MESSAGE.Set_Name('WSH','WSH_NO_TRACKING_INFO_SPECIFIED');
441 RAISE wsh_tracking_exception;
442 END IF;
443
444 IF p_mode NOT IN ('FULL','CURRENT') THEN
445 FND_MESSAGE.Set_Name('WSH','WSH_INVALID_TRACKING_MODE');
446 RAISE wsh_tracking_exception;
447 END IF;
448
449 --Bug 3639940
450 IF p_delivery_name IS NOT NULL THEN
451 Track_Delivery(p_delivery_name, p_mode, x_tracking_details);
452 END IF;
453
454 --
455 -- Debug Statements
456 --
457 IF l_debug_on THEN
458 WSH_DEBUG_SV.pop(l_module_name);
459 END IF;
460 --
461 EXCEPTION
462
463 WHEN wsh_tracking_exception THEN
464
465 x_return_status := FND_API.G_RET_STS_ERROR;
466 --
467 -- Debug Statements
468 --
469 IF l_debug_on THEN
470 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_TRACKING_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
471 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_TRACKING_EXCEPTION');
472 END IF;
473 --
474
475 WHEN wsh_unexpected_error THEN
476
477 x_return_status := FND_API.G_RET_STS_ERROR;
478 --
479 -- Debug Statements
480 --
481 IF l_debug_on THEN
482 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
483 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UNEXPECTED_ERROR');
484 END IF;
485 --
486 WHEN OTHERS THEN
487
488 x_return_status := FND_API.G_RET_STS_ERROR;
489
490 --
491 -- Debug Statements
492 --
493
494 IF l_debug_on THEN
495 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
496 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
497 END IF;
498 --
499 END;
500 END WSH_SHIPPING_INFO;