[Home] [Help]
PACKAGE BODY: APPS.WSH_SHIPPING_INFO
Source
1 PACKAGE BODY WSH_SHIPPING_INFO as
2 /* $Header: WSHSHINB.pls 120.2.12010000.2 2009/07/30 11:18:56 ueshanka ship $ */
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 status <> 'CANCELLED' --Bug 8597679 :Added the condition to filter out cancelld BOl
191 AND document_type= 'BOL';
192
193 CURSOR get_trip_delivery_info (v_delivery_name VARCHAR2) IS
194 SELECT wl.meaning status,
195 t.name,
196 WSH_UTIL_CORE.get_location_description(ts1.stop_location_id,'CSZ') pickup_loc,
197 ts1.actual_arrival_date pu_arrival_date,
198 ts1.actual_departure_date pu_departure_date,
199 WSH_UTIL_CORE.get_location_description(ts2.stop_location_id,'CSZC') dropoff_loc,
200 ts2.actual_arrival_date do_arrival_date,
201 ts2.actual_departure_date do_departure_date,
202 t.ship_method_code,
203 hp.party_name carrier_name, -- Bug 5697730
204 dg.delivery_leg_id
205 FROM wsh_lookups wl,
206 wsh_trips t,
207 wsh_trip_stops ts1,
208 wsh_trip_stops ts2,
209 -- wsh_document_instances di, -- Bug 3146273
210 wsh_delivery_legs dg,
211 wsh_new_deliveries dl,
212 hz_parties hp, hz_party_usg_assignments hpu -- Bug 5697730
213 WHERE dl.name = v_delivery_name
214 AND hp.party_id(+) = t.carrier_id -- Bug 5697730
215 AND hp.party_id = hpu.party_id(+) -- Bug 5697730
216 AND hpu.party_usage_code(+) = 'TRANSPORTATION_PROVIDER' -- Bug 5697730
217 AND dg.delivery_id = dl.delivery_id
218 AND dg.pick_up_stop_id = ts1.stop_id
219 -- AND ts1.status_code = 'CL'
220 AND dg.drop_off_stop_id = ts2.stop_id
221 AND ts1.trip_id = t.trip_id
222 --AND dg.delivery_leg_id = di.entity_id(+) -- Bug 3146273
223 AND wl.lookup_type = 'DELIVERY_STATUS'
224 AND wl.lookup_code = dl.status_code
225 AND nvl(dl.SHIPMENT_DIRECTION , 'O') IN ('O', 'IO') -- J inbound logistics jckwok
226 ORDER BY ts1.planned_arrival_date asc;
227
228 l_trip_del get_trip_delivery_info%ROWTYPE;
229
230 l_tmpTrackLine Tracking_Info_Rec_Typ;
231 l_bill_of_lading wsh_document_instances.sequence_number%TYPE:= null;
232
233 i NUMBER;
234
235 --
236 l_debug_on BOOLEAN;
237 --
238 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'TRACK_DELIVERY';
239 --
240 BEGIN
241
242 -- First, get Unshipped Delivery Information
243 --
244 -- Debug Statements
245 --
246 --
247 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
248 --
249 IF l_debug_on IS NULL
250 THEN
251 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
252 END IF;
253 --
254 IF l_debug_on THEN
255 WSH_DEBUG_SV.push(l_module_name);
256 --
257 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_NAME',P_DELIVERY_NAME);
258 WSH_DEBUG_SV.log(l_module_name,'P_MODE',P_MODE);
259 END IF;
260 --
261 --Get Delivery With Trip Details
262 OPEN get_trip_delivery_info(p_delivery_name);
263 i := 0;
264 LOOP
265 FETCH get_trip_delivery_info
266 INTO l_trip_del;
267 EXIT WHEN get_trip_delivery_info%NOTFOUND;
268 -- BUG 3146273: jckwok: open get_bill_of_lading with new leg id
269 OPEN get_bill_of_lading (l_trip_del.delivery_leg_id);
270 l_bill_of_lading := null;
271 FETCH get_bill_of_lading
272 INTO l_bill_of_lading;
273 CLOSE get_bill_of_lading;
274 -- BUG 3146273: jckwok
275
276 -- Update Current record information, arrival date is
277 -- previous entries arrival date at location
278 i := i + 1;
279 Fill_Track_Record (
280 p_record_number => i,
281 p_delivery_status => l_trip_del.status,
282 p_trip_name => l_trip_del.name,
283 p_location_name => l_trip_del.pickup_loc,
284 p_actual_arrival_date => FND_API.G_MISS_DATE,
285 p_actual_departure_date => l_trip_del.pu_departure_date,
286 p_ship_method_code => l_trip_del.ship_method_code,
287 p_bill_of_lading => l_bill_of_lading,
288 p_carrier_name => l_trip_del.carrier_name, -- Bug 5697730
289 x_tracking_details => x_tracking_details);
290 i := i + 1;
291 Fill_Track_Record (
292 p_record_number => i,
293 p_delivery_status => l_trip_del.status,
294 p_trip_name => l_trip_del.name,
295 p_location_name => l_trip_del.dropoff_loc,
296 p_actual_arrival_date => l_trip_del.do_arrival_date,
297 p_actual_departure_date => NULL,
298 p_ship_method_code => NULL,
299 p_bill_of_lading => NULL,
300 p_carrier_name => NULL, -- Bug 5697730
301 x_tracking_details => x_tracking_details);
302 -- Avoid infinite loop problem by terminating on
303 -- large number
304 IF i = 100 THEN
305 FND_MESSAGE.Set_Name('WSH','WSH_UNEXP_ERROR');
306 RAISE wsh_unexpected_error;
307 END IF;
308 END LOOP;
309 CLOSE get_trip_delivery_info;
310
311 IF i=0 THEN --Get Delivery Without Trip
312 OPEN get_delivery_info(p_delivery_name);
313 FETCH get_delivery_info INTO l_del;
314 IF get_delivery_info%FOUND THEN
315 i := i + 1;
316 Fill_Track_Record (
317 p_record_number => i,
318 p_delivery_status => l_del.status,
319 p_trip_name => l_del.name,
320 p_location_name => l_del.pickup_loc,
321 p_actual_arrival_date => NULL,
322 p_actual_departure_date => l_del.pu_departure_date,
323 p_ship_method_code => l_del.ship_method_code,
324 p_bill_of_lading => l_del.bill_of_lading,
325 p_carrier_name => l_del.carrier_name, -- Bug 5697730
326 x_tracking_details => x_tracking_details);
327 END IF;
328 CLOSE get_delivery_info;
329 ELSE
330 IF p_mode = 'CURRENT' THEN
331 l_tmpTrackLine := x_tracking_details(x_tracking_details.LAST);
332 -- Bug: 1114924 since the last record in the table will always have NULL BOL and should be same as the previous one.
333 l_tmpTrackLine.bill_of_lading := x_tracking_details(x_tracking_details.count-1).bill_of_lading;
334 -- Bug: 1570332 since the last record in the table will always have NULL ship method code and should be same as the previous one.
335 l_tmpTrackLine.ship_method_code := x_tracking_details(x_tracking_details.count-1).ship_method_code;
336 l_tmpTrackLine.carrier_name := x_tracking_details(x_tracking_details.count-1).carrier_name; -- Bug 5697730
337 l_tmpTrackLine.actual_departure_date := x_tracking_details(x_tracking_details.count-1).actual_departure_date;
338 x_tracking_details.DELETE;
339 x_tracking_details(1) := l_tmpTrackLine;
340 END IF;
341 END IF;
342 IF(i=0) THEN
343 FND_MESSAGE.Set_Name('WSH','WSH_UNEXP_ERROR');
344 RAISE wsh_unexpected_error;
345 END IF;
346 --
347 -- Debug Statements
348 --
349 IF l_debug_on THEN
350 WSH_DEBUG_SV.pop(l_module_name);
351 END IF;
352 --
353 EXCEPTION
354 WHEN OTHERS THEN
355 IF get_trip_delivery_info%ISOPEN THEN
356 CLOSE get_trip_delivery_info;
357 END IF;
358 IF get_delivery_info%ISOPEN THEN
359 CLOSE get_delivery_info;
360 END IF;
361 IF get_bill_of_lading%ISOPEN THEN
362 CLOSE get_bill_of_lading;
363 END IF;
364 --
365 -- Debug Statements
366 --
367 IF l_debug_on THEN
368 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
369 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
370 END IF;
371 --
372 RAISE;
373
374 END Track_Delivery;
375
376 --
377 -- Procedure: Track_Shipment
378 -- Parameters: p_delivery_name - Name of Delivery to track
379 -- p_tracking_number_dd - Tracking Number of Delivery
380 -- Line
381 -- p_mode - 'FULL' or 'CURRENT'
382 -- 'FULL' Gives complete tracking information
383 -- 'CURRENT' Provides simple tracking information
384 -- a) If the delivery is not shipped, initial
385 -- trip/location information is provided
386 -- b) If the delivery is shipped, it provides
387 -- the current shipment information
388 -- c) If the delivery has been delivered, it
389 -- provides the final trip/location
390 -- information when delivered to the
391 -- customer
392 -- x_tracking_details - Record of all the tracking
393 -- details for a shipment
394 -- x_return_status - Status of procedure call
395 -- - FND_API.G_RET_STS_SUCCESS
396 -- - FND_API.G_RET_STS_ERROR
397 -- Description: This procedure will provide tracking information
398 -- for a shipment
399 --
400
401 PROCEDURE Track_Shipment
402 (p_delivery_name IN VARCHAR2 DEFAULT NULL,
403 p_tracking_number_dd IN VARCHAR2 DEFAULT NULL,
404 p_mode IN VARCHAR2,
405 x_tracking_details OUT NOCOPY Tracking_Info_Tab_Typ,
406 x_return_status OUT NOCOPY VARCHAR2
407 ) IS
408
409 l_delivery_name VARCHAR2(30);
410 --
411 l_debug_on BOOLEAN;
412 --
413 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'TRACK_SHIPMENT';
414 --
415 BEGIN
416 -- Setup parameters
417 --
418 -- Debug Statements
419 --
420 --
421 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
422 --
423 IF l_debug_on IS NULL
424 THEN
425 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
426 END IF;
427 --
428 IF l_debug_on THEN
429 WSH_DEBUG_SV.push(l_module_name);
430 --
431 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_NAME',P_DELIVERY_NAME);
432 WSH_DEBUG_SV.log(l_module_name,'P_TRACKING_NUMBER_DD',P_TRACKING_NUMBER_DD);
433 WSH_DEBUG_SV.log(l_module_name,'P_MODE',P_MODE);
434 END IF;
435 --
436 x_tracking_details.delete;
437 x_return_status := FND_API.G_RET_STS_SUCCESS;
438
439 -- Validate parameters
440 IF p_delivery_name IS NULL THEN
441 FND_MESSAGE.Set_Name('WSH','WSH_NO_TRACKING_INFO_SPECIFIED');
442 RAISE wsh_tracking_exception;
443 END IF;
444
445 IF p_mode NOT IN ('FULL','CURRENT') THEN
446 FND_MESSAGE.Set_Name('WSH','WSH_INVALID_TRACKING_MODE');
447 RAISE wsh_tracking_exception;
448 END IF;
449
450 --Bug 3639940
451 IF p_delivery_name IS NOT NULL THEN
452 Track_Delivery(p_delivery_name, p_mode, x_tracking_details);
453 END IF;
454
455 --
456 -- Debug Statements
457 --
458 IF l_debug_on THEN
459 WSH_DEBUG_SV.pop(l_module_name);
460 END IF;
461 --
462 EXCEPTION
463
464 WHEN wsh_tracking_exception THEN
465
466 x_return_status := FND_API.G_RET_STS_ERROR;
467 --
468 -- Debug Statements
469 --
470 IF l_debug_on THEN
471 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_TRACKING_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
472 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_TRACKING_EXCEPTION');
473 END IF;
474 --
475
476 WHEN wsh_unexpected_error THEN
477
478 x_return_status := FND_API.G_RET_STS_ERROR;
479 --
480 -- Debug Statements
481 --
482 IF l_debug_on THEN
483 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
484 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UNEXPECTED_ERROR');
485 END IF;
486 --
487 WHEN OTHERS THEN
488
489 x_return_status := FND_API.G_RET_STS_ERROR;
490
491 --
492 -- Debug Statements
493 --
494
495 IF l_debug_on THEN
496 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
497 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
498 END IF;
499 --
500 END;
501 END WSH_SHIPPING_INFO;