[Home] [Help]
PACKAGE BODY: APPS.WSH_TRIPS_PVT
Source
1 PACKAGE BODY WSH_TRIPS_PVT AS
2 /* $Header: WSHTRTHB.pls 120.3 2007/01/05 00:26:34 anxsharm ship $ */
3
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_TRIPS_PVT';
6 --
7 PROCEDURE Create_Trip(
8 p_trip_info IN trip_rec_type,
9 x_rowid OUT NOCOPY varchar2,
10 x_trip_id OUT NOCOPY NUMBER,
11 x_name OUT NOCOPY VARCHAR2,
12 x_return_status OUT NOCOPY VARCHAR2
13 )
14 IS
15
16 CURSOR get_next_trip IS
17 SELECT wsh_trips_s.nextval
18 FROM sys.dual;
19
20 -- Remove cursor get_row_id for Bug 3821688
21
22 CURSOR check_trip_names (v_trip_name VARCHAR2) IS
23 SELECT trip_id FROM wsh_trips
24 WHERE name = v_trip_name;
25
26 CURSOR check_trip_ids (v_trip_id NUMBER) IS
27 SELECT trip_id FROM wsh_trips
28 WHERE trip_id = v_trip_id;
29
30 l_name WSH_TRIPS.NAME%TYPE;
31 l_row_check NUMBER;
32 l_temp_id NUMBER;
33
34 l_tmp_count NUMBER := 0;
35
36 wsh_duplicate_name EXCEPTION;
37 others EXCEPTION;
38
39 l_ignore_for_planning WSH_TRIPS.IGNORE_FOR_PLANNING%TYPE;
40 l_gc3_is_installed VARCHAR2(1); -- OTM R12, glog proj
41 --
42 --/== Workflow Changes
43 l_process_started VARCHAR2(1);
44 l_return_status VARCHAR2(1);
45 l_wf_rs VARCHAR2(1);
46 --==/
47
48 l_debug_on BOOLEAN;
49 --
50 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_TRIP';
51 --
52 BEGIN
53 --
54 -- Debug Statements
55 --
56 --
57 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
58 --
59 IF l_debug_on IS NULL
60 THEN
61 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
62 END IF;
63 --
64 IF l_debug_on THEN
65 WSH_DEBUG_SV.push(l_module_name);
66 END IF;
67 --
68
69 x_trip_id := p_trip_info.trip_id;
70 x_name := p_trip_info.name;
71
72 --OTM R12, glog proj, use Global Variable
73 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
74
75 -- If null, call the function
76 IF l_gc3_is_installed IS NULL THEN
77 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
78 END IF;
79 IF l_gc3_is_installed = 'Y' THEN
80 l_ignore_for_planning := 'Y';
81 ELSE
82 l_ignore_for_planning := 'N';
83 END IF;
84 -- end of OTM R12, glog proj
85
86
87
88 IF (x_trip_id IS NULL) THEN
89
90 LOOP
91
92 OPEN get_next_trip;
93 FETCH get_next_trip INTO x_trip_id;
94 CLOSE get_next_trip;
95
96 IF (x_trip_id IS NULL) THEN
97 raise others;
98 END IF;
99
100 l_row_check := NULL;
101
102 OPEN check_trip_ids(x_trip_id);
103 FETCH check_trip_ids INTO l_row_check;
104
105 IF (check_trip_ids%NOTFOUND) THEN
106 CLOSE check_trip_ids;
107 EXIT;
108 END IF;
109
110 CLOSE check_trip_ids;
111
112 END LOOP;
113
114
115 END IF;
116
117 IF (x_name IS NULL) THEN
118
119 --
120 -- Debug Statements
121 --
122 IF l_debug_on THEN
123 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CUSTOM_PUB.TRIP_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
124 END IF;
125 --
126 l_name := wsh_custom_pub.trip_name(x_trip_id, p_trip_info);
127
128 -- shipping default make sure the delivery name is not duplicate
129 IF ( l_name = to_char(x_trip_id) ) THEN
130
131 l_temp_id := x_trip_id;
132
133 --l_name := to_char(l_temp_id);
134
135 LOOP
136 l_name := to_char(l_temp_id);
137
138 OPEN check_trip_names( l_name);
139 FETCH check_trip_names INTO l_row_check;
140
141 IF (check_trip_names%NOTFOUND) THEN
142 CLOSE check_trip_names;
143 EXIT;
144 END IF;
145
146 CLOSE check_trip_names;
147
148 OPEN get_next_trip;
149 FETCH get_next_trip INTO l_temp_id;
150 CLOSE get_next_trip;
151
152 IF (l_temp_id IS NULL) THEN
153 raise others;
154 END IF;
155
156 END LOOP;
157
158 x_trip_id := l_temp_id;
159
160 END IF;
161
162 x_name := l_name;
163
164 ELSE
165 OPEN check_trip_names( x_name);
166 FETCH check_trip_names INTO l_row_check;
167
168 IF (check_trip_names%FOUND) THEN
169 CLOSE check_trip_names;
170 RAISE wsh_duplicate_name;
171 END IF;
172
173 CLOSE check_trip_names;
174
175 END IF;
176
177
178 INSERT INTO wsh_trips(
179 trip_id,
180 name,
181 planned_flag,
182 arrive_after_trip_id,
183 status_code,
184 vehicle_item_id,
185 vehicle_organization_id,
186 vehicle_number,
187 vehicle_num_prefix,
188 carrier_id,
189 ship_method_code,
190 route_id,
191 routing_instructions,
192 attribute_category,
193 attribute1,
194 attribute2,
195 attribute3,
196 attribute4,
197 attribute5,
198 attribute6,
199 attribute7,
200 attribute8,
201 attribute9,
202 attribute10,
203 attribute11,
204 attribute12,
205 attribute13,
206 attribute14,
207 attribute15,
208 creation_date,
209 created_by,
210 last_update_date,
211 last_updated_by,
212 last_update_login,
213 program_application_id,
214 program_id,
215 program_update_date,
216 request_id,
217 /* H Integration: datamodel changes wrudge */
218 service_level,
219 mode_of_transport,
220 freight_terms_code,
221 consolidation_allowed,
222 load_tender_status,
223 load_tender_number,
224 wf_name,
225 wf_process_name,
226 wf_item_key,
227 carrier_contact_id,
228 shipper_wait_time,
229 wait_time_uom,
230 load_tendered_time,
231 vessel,
232 voyage_number,
233 port_of_loading,
234 port_of_discharge,
235 carrier_response,
236 route_lane_id,
237 lane_id,
238 schedule_id,
239 booking_number,
240 shipments_type_flag,
241 /* J TP Release : ttrichy */
242 ignore_for_planning,
243 tp_plan_name,
244 tp_trip_number,
245 seal_code,
246 operator,
247 /* R12 attributes */
248 carrier_reference_number,
249 rank_id,
250 consignee_carrier_ac_no,
251 routing_rule_id,
252 append_flag
253 ) VALUES(
254 x_trip_id,
255 x_name,
256 nvl(p_trip_info.planned_flag, 'N'),
257 p_trip_info.arrive_after_trip_id,
258 nvl(p_trip_info.status_code,'OP'),
259 p_trip_info.vehicle_item_id,
260 p_trip_info.vehicle_organization_id,
261 p_trip_info.vehicle_number,
262 p_trip_info.vehicle_num_prefix,
263 p_trip_info.carrier_id,
264 p_trip_info.ship_method_code,
265 p_trip_info.route_id,
266 p_trip_info.routing_instructions,
267 p_trip_info.attribute_category,
268 p_trip_info.attribute1,
269 p_trip_info.attribute2,
270 p_trip_info.attribute3,
271 p_trip_info.attribute4,
272 p_trip_info.attribute5,
273 p_trip_info.attribute6,
274 p_trip_info.attribute7,
275 p_trip_info.attribute8,
276 p_trip_info.attribute9,
277 p_trip_info.attribute10,
278 p_trip_info.attribute11,
279 p_trip_info.attribute12,
280 p_trip_info.attribute13,
281 p_trip_info.attribute14,
282 p_trip_info.attribute15,
283 nvl(p_trip_info.creation_date, SYSDATE),
284 nvl(p_trip_info.created_by, FND_GLOBAL.USER_ID),
285 nvl(p_trip_info.last_update_date, SYSDATE),
286 nvl(p_trip_info.last_updated_by, FND_GLOBAL.USER_ID),
287 nvl(p_trip_info.last_update_login, FND_GLOBAL.LOGIN_ID),
288 p_trip_info.program_application_id,
289 p_trip_info.program_id,
290 p_trip_info.program_update_date,
291 p_trip_info.request_id,
292 /* H Integration: datamodel changes wrudge */
293 p_trip_info.service_level,
294 p_trip_info.mode_of_transport,
295 p_trip_info.freight_terms_code,
296 p_trip_info.consolidation_allowed,
297 p_trip_info.load_tender_status,
298 p_trip_info.load_tender_number,
299 p_trip_info.wf_name,
300 p_trip_info.wf_process_name,
301 p_trip_info.wf_item_key,
302 p_trip_info.carrier_contact_id,
303 p_trip_info.shipper_wait_time,
304 p_trip_info.wait_time_uom,
305 p_trip_info.load_tendered_time,
306 p_trip_info.vessel,
307 p_trip_info.voyage_number,
308 p_trip_info.port_of_loading,
309 p_trip_info.port_of_discharge,
310 p_trip_info.carrier_response,
311 p_trip_info.route_lane_id,
312 p_trip_info.lane_id,
313 p_trip_info.schedule_id,
314 p_trip_info.booking_number,
315 nvl(p_trip_info.shipments_type_flag, 'O'),
316 /* J TP Release : ttrichy */
317 --OTM R12, glog proj
318 NVL(p_trip_info.ignore_for_planning, l_ignore_for_planning),
319 p_trip_info.tp_plan_name,
320 p_trip_info.tp_trip_number,
321 p_trip_info.seal_code,
322 p_trip_info.operator,
323 p_trip_info.carrier_reference_number,
324 p_trip_info.rank_id,
325 p_trip_info.consignee_carrier_ac_no,
326 p_trip_info.routing_rule_id,
327 p_trip_info.append_flag
328 )RETURNING rowid -- Bug 3821688
329 INTO x_rowid;
330
331 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
332
333 --/== Workflow Changes
334 IF l_debug_on THEN
335 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.START_WF_PROCESS',WSH_DEBUG_SV.C_PROC_LEVEL);
336 END IF;
337 WSH_WF_STD.start_wf_process(p_entity_type => 'TRIP',
338 p_entity_id => x_trip_id,
339 p_organization_id => NULL,
340 x_process_started => l_process_started,
341 x_return_status => l_return_status);
342 IF l_debug_on THEN
343 WSH_DEBUG_SV.log(l_module_name,'L_PROCESS_STARTED',l_process_started);
344 WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
345 END IF;
346 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
347 NULL;
348 -- x_return_status := l_return_status;
349 -- Log an exception
350 END IF;
351
352
353 IF l_debug_on THEN
354 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.RAISE_EVENT',WSH_DEBUG_SV.C_PROC_LEVEL);
355 END IF;
356
357 WSH_WF_STD.RAISE_EVENT(p_entity_type => 'TRIP',
358 p_entity_id => x_trip_id,
359 p_event => 'oracle.apps.wsh.trip.gen.create',
360 x_return_status => l_wf_rs
361 );
362
363 IF l_debug_on THEN
364 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_WF_STD.RAISE_EVENT => ',l_wf_rs);
365 END IF;
366 -- Workflow Changes ==/
367
368
369 --
370 -- Debug Statements
371 --
372 IF l_debug_on THEN
373 WSH_DEBUG_SV.pop(l_module_name);
374 END IF;
375 --
376 EXCEPTION
377 WHEN wsh_duplicate_name THEN
378 -- OTM R12,glog proj, close any open cursor
379 IF get_next_trip%ISOPEN THEN
380 CLOSE get_next_trip;
381 END IF;
382 IF check_trip_names%ISOPEN THEN
383 CLOSE check_trip_names;
384 END IF;
385 IF check_trip_ids%ISOPEN THEN
386 CLOSE check_trip_ids;
387 END IF;
388 FND_MESSAGE.Set_Name('FND', 'FORM_DUPLICATE_KEY_IN_INDEX');
389 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
390 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
391 --
392 -- Debug Statements
393 --
394 IF l_debug_on THEN
395 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_DUPLICATE_NAME exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
396 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_DUPLICATE_NAME');
397 END IF;
398 --
399 WHEN others THEN
400 -- OTM R12,glog proj, close any open cursor
401 IF get_next_trip%ISOPEN THEN
402 CLOSE get_next_trip;
403 END IF;
404 IF check_trip_names%ISOPEN THEN
405 CLOSE check_trip_names;
406 END IF;
407 IF check_trip_ids%ISOPEN THEN
408 CLOSE check_trip_ids;
409 END IF;
410 wsh_util_core.default_handler('WSH_TRIPS_PVT.CREATE_TRIP');
411 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
412 --
413 -- Debug Statements
414 --
415 IF l_debug_on THEN
416 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
417 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
418 END IF;
419 --
420 END Create_Trip;
421
422 PROCEDURE Delete_Trip(
423 p_rowid IN VARCHAR2,
424 p_trip_id IN NUMBER,
425 x_return_status OUT NOCOPY VARCHAR2,
426 p_validate_flag IN VARCHAR2,
427 --tkt
428 p_caller IN VARCHAR2) IS
429
430 CURSOR get_trip_id_rowid (v_rowid VARCHAR2) IS
431 SELECT trip_id
432 FROM wsh_trips
433 WHERE rowid = v_rowid;
434
435 CURSOR trip_deliveries (l_trip_id IN NUMBER) IS
436 SELECT delivery_id
437 FROM wsh_delivery_legs wdl,
438 wsh_trip_stops wts
439 WHERE wts.trip_id = l_trip_id
440 AND wdl.pick_up_stop_id = wts.stop_id;
441
442 CURSOR get_trip_stops ( c_trip_id IN NUMBER) IS
443 SELECT stop_id FROM wsh_trip_stops WHERE trip_id = c_trip_id;
444
445 --OTM R12, glog proj
446 CURSOR c_get_trip_status (p_trip_id IN NUMBER) IS
447 SELECT NVL(ignore_for_planning, 'N'),
448 tp_plan_name
449 FROM WSH_TRIPS
450 WHERE trip_id = p_trip_id;
451
452 l_ignore WSH_TRIPS.IGNORE_FOR_PLANNING%TYPE;
453 l_tp_plan_name WSH_TRIPS.TP_PLAN_NAME%TYPE;
454 l_gc3_is_installed VARCHAR2(1);
455 e_gc3_trip EXCEPTION;
456 --
457
458
459 l_stop_id NUMBER;
460 l_trip_id NUMBER;
461 l_del_ids WSH_UTIL_CORE.ID_TAB_TYPE;
462 l_return_status VARCHAR2(1);
463
464 cannot_delete_trip EXCEPTION;
465 others EXCEPTION;
466
467 l_stop_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
468 l_dbi_rs VARCHAR2(1); -- DBI Project
469 l_debug_on BOOLEAN;
470
471 --
472 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_TRIP';
473 --
474 BEGIN
475
476 --
477 -- Debug Statements
478 --
479 --
480 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
481 --
482 IF l_debug_on IS NULL
483 THEN
484 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
485 END IF;
486 --
487 IF l_debug_on THEN
488 WSH_DEBUG_SV.push(l_module_name);
489 --
490 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
491 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
492 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATE_FLAG',P_VALIDATE_FLAG);
493 END IF;
494 --
495 x_return_Status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
496
497 l_trip_id := p_trip_id;
498
499 IF p_rowid IS NOT NULL THEN
500 OPEN get_trip_id_rowid(p_rowid);
501 FETCH get_trip_id_rowid INTO l_trip_id;
502 CLOSE get_trip_id_rowid;
503 END IF;
504
505 --OTM R12, glog proj, use Global Variable
506 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
507
508 -- If null, call the function
509 IF l_gc3_is_installed IS NULL THEN
510 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
511 END IF;
512
513 IF l_gc3_is_installed = 'Y' THEN
514 OPEN c_get_trip_status(l_trip_id);
515 FETCH c_get_trip_status INTO l_ignore, l_tp_plan_name;
516 IF c_get_trip_status%NOTFOUND THEN
517 CLOSE c_get_trip_status;
518 RAISE no_data_found;
519 END IF;
520 CLOSE c_get_trip_status;
521
522 IF l_debug_on THEN
523 WSH_DEBUG_SV.log(l_module_name,'Ignore for Planning',l_ignore);
524 WSH_DEBUG_SV.log(l_module_name,'Tp Plan Name',l_tp_plan_name);
525 WSH_DEBUG_SV.log(l_module_name,'GC3 is installed',l_gc3_is_installed);
526 END IF;
527
528 -- Do not allow user to delete a trip if it is include for Planning and created by OTM
529 IF (l_ignore = 'N' AND l_tp_plan_name IS NOT NULL) THEN
530 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
531 RAISE e_gc3_trip;
532 END IF;
533 END IF;
534 -- end of OTM R12, glog proj
535
536 IF (p_validate_flag = 'Y') THEN
537 --
538 -- Debug Statements
539 --
540 IF l_debug_on THEN
541 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_VALIDATIONS.CHECK_TRIP_DELETE',WSH_DEBUG_SV.C_PROC_LEVEL);
542 END IF;
543 --
544 wsh_trip_validations.check_trip_delete(l_trip_id, l_return_status, p_caller);
545
546 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
547 RAISE cannot_delete_trip;
548 ELSE
549 x_return_status := l_return_status;
550 END IF;
551 END IF;
552
553 IF l_trip_id IS NOT NULL THEN
554
555 FOR del IN trip_deliveries(l_trip_id) LOOP
556 l_del_ids(l_del_ids.count + 1) := del.delivery_id;
557 END LOOP;
558
559 IF (l_del_ids.count > 0) THEN
560
561 SAVEPOINT unassign_del;
562
563 --
564 -- Debug Statements
565 --
566 IF l_debug_on THEN
567 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_LEGS_ACTIONS.UNASSIGN_DELIVERIES',WSH_DEBUG_SV.C_PROC_LEVEL);
568 END IF;
569 --
570 wsh_delivery_legs_actions.unassign_deliveries(l_del_ids, l_trip_id, NULL, NULL, l_return_status);
571
572 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
573
574 ROLLBACK TO SAVEPOINT unassign_del;
575 RAISE cannot_delete_trip;
576
577 END IF;
578
579 END IF;
580
581 DELETE FROM wsh_freight_costs
582 WHERE trip_id = l_trip_id;
583
584 open get_trip_stops(l_trip_id);
585 loop
586 fetch get_trip_stops into l_stop_id;
587 l_stop_tab(l_stop_tab.count+1) := l_stop_id; -- DBI project
588 exit when get_trip_stops%NOTFOUND ;
589 DELETE FROM wsh_freight_costs WHERE stop_id = l_stop_id;
590 end loop;
591 close get_trip_stops;
592
593 DELETE FROM wsh_trip_stops
594 WHERE trip_id = l_trip_id;
595
596 --
597 -- DBI Project
598 -- DELETE from WSH_TRIP_STOPS.
599 -- Call DBI API after the DELETE.
600 -- This API will also check for DBI Installed or not
601 IF l_debug_on THEN
602 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail Count -',l_stop_tab.count);
603 END IF;
604 WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
605 (p_stop_id_tab => l_stop_tab,
606 p_dml_type => 'DELETE',
607 x_return_status => l_dbi_rs);
608
609 IF l_debug_on THEN
610 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
611 END IF;
612 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
613 x_return_status := l_dbi_rs;
614 rollback;
615 -- just pass this return status to caller API
616 IF l_debug_on THEN
617 WSH_DEBUG_SV.log(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
618 WSH_DEBUG_SV.pop(l_module_name);
619 END IF;
620 return;
621 END IF;
622 -- End of Code for DBI Project
623 --
624
625 DELETE FROM wsh_trips
626 WHERE trip_id = l_trip_id;
627
628 IF (SQL%NOTFOUND) THEN
629 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NOT_FOUND');
630 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
631 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
632 END IF;
633 ELSE
634 raise others;
635 END IF;
636
637 --
638 -- Debug Statements
639 --
640 IF l_debug_on THEN
641 WSH_DEBUG_SV.pop(l_module_name);
642 END IF;
643 --
644 EXCEPTION
645 --OTM R12, glog proj
646 WHEN e_gc3_trip THEN
647 IF c_get_trip_status%ISOPEN THEN
648 CLOSE c_get_trip_status;
649 END IF;
650 FND_MESSAGE.SET_NAME('WSH','WSH_OTM_TRIP_DELETE_ERROR');
651 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
652 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
653 --
654 -- Debug Statements
655 --
656 IF l_debug_on THEN
657 WSH_DEBUG_SV.logmsg(l_module_name,'E_GC3_TRIP exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
658 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:E_GC3_TRIP');
659 END IF;
660 --
661
662 --OTM R12, glog proj
663 WHEN no_data_found THEN
664 IF c_get_trip_status%ISOPEN THEN
665 CLOSE c_get_trip_status;
666 END IF;
667 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NOT_FOUND');
668 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
669 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
670 --
671 -- Debug Statements
672 --
673 IF l_debug_on THEN
674 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
675 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
676 END IF;
677 --
678
679
680 WHEN cannot_delete_trip THEN
681 IF c_get_trip_status%ISOPEN THEN
682 CLOSE c_get_trip_status;
683 END IF;
684 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_DELETE_ERROR');
685 FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_id));
686 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
687 wsh_util_core.add_message(x_return_status);
688 --
689 -- Debug Statements
690 --
691 IF l_debug_on THEN
692 WSH_DEBUG_SV.logmsg(l_module_name,'CANNOT_DELETE_TRIP exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
693 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:CANNOT_DELETE_TRIP');
694 END IF;
695 --
696 WHEN others THEN
697 -- OTM R12, glog proj
698 IF c_get_trip_status%ISOPEN THEN
699 CLOSE c_get_trip_status;
700 END IF;
701 wsh_util_core.default_handler('WSH_TRIPS_PVT.DELETE_TRIP');
702 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
703 --
704 -- Debug Statements
705 --
706 IF l_debug_on THEN
707 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
708 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
709 END IF;
710 --
711 END Delete_Trip;
712
713 PROCEDURE Update_Trip(
714 p_rowid IN VARCHAR2,
715 p_trip_info IN trip_rec_type,
716 x_return_status OUT NOCOPY VARCHAR2) IS
717
718 CURSOR get_row_id IS
719 SELECT rowid
720 FROM wsh_trips
721 WHERE trip_id = p_trip_info.trip_id;
722
723 l_rowid VARCHAR2(30);
724
725 CURSOR c_iscarriersmcchanged IS
726 SELECT wnd.organization_id, wnd.name
727 FROM wsh_new_deliveries wnd, wsh_trip_stops wts, wsh_delivery_legs wdl
728 WHERE wnd.delivery_id=wdl.delivery_id
729 and wdl.pick_up_stop_id=wts.stop_id
730 and wts.trip_id = p_trip_info.trip_id
731 and (carrier_id <> p_trip_info.carrier_id
732 OR ship_method_code <> p_trip_info.ship_method_code);
733
734 l_return_status VARCHAR2 (1);
735 l_wh_type VARCHAR2(3);
736
737 --WF: CMR
738 l_wf_rs VARCHAR2(1);
739 l_del_ids WSH_UTIL_CORE.ID_TAB_TYPE;
740 l_del_old_carrier_ids WSH_UTIL_CORE.ID_TAB_TYPE;
741
742 --
743 l_debug_on BOOLEAN;
744 --
745 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_TRIP';
746 --
747 BEGIN
748
749 --
750 -- Debug Statements
751 --
752 --
753 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
754 --
755 IF l_debug_on IS NULL
756 THEN
757 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
758 END IF;
759 --
760 IF l_debug_on THEN
761 WSH_DEBUG_SV.push(l_module_name);
762 --
763 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
764 WSH_DEBUG_SV.log(l_module_name,'trip_id', p_trip_info.trip_id);
765 WSH_DEBUG_SV.log(l_module_name,'name', p_trip_info.name);
766 WSH_DEBUG_SV.log(l_module_name,'planned_flag', p_trip_info.planned_flag);
767 WSH_DEBUG_SV.log(l_module_name,'arrive_after_trip_id', p_trip_info.arrive_after_trip_id);
768 WSH_DEBUG_SV.log(l_module_name,'status_code', p_trip_info.status_code);
769 WSH_DEBUG_SV.log(l_module_name,'vehicle_item_id', p_trip_info.vehicle_item_id);
770 WSH_DEBUG_SV.log(l_module_name,'vehicle_organization_id', p_trip_info.vehicle_organization_id);
771 WSH_DEBUG_SV.log(l_module_name,'vehicle_number', p_trip_info.vehicle_number);
772 WSH_DEBUG_SV.log(l_module_name,'vehicle_num_prefix', p_trip_info.vehicle_num_prefix);
773 WSH_DEBUG_SV.log(l_module_name,'carrier_id', p_trip_info.carrier_id);
774 WSH_DEBUG_SV.log(l_module_name,'ship_method_code', p_trip_info.ship_method_code);
775 WSH_DEBUG_SV.log(l_module_name,'route_id', p_trip_info.route_id);
776 WSH_DEBUG_SV.log(l_module_name,'routing_instructions', p_trip_info.routing_instructions);
777 WSH_DEBUG_SV.log(l_module_name,'service_level', p_trip_info.service_level);
778 WSH_DEBUG_SV.log(l_module_name,'mode_of_transport', p_trip_info.mode_of_transport);
779 WSH_DEBUG_SV.log(l_module_name,'freight_terms_code', p_trip_info.freight_terms_code);
780 WSH_DEBUG_SV.log(l_module_name,'consolidation_allowed', p_trip_info.consolidation_allowed);
781 WSH_DEBUG_SV.log(l_module_name,'load_tender_status', p_trip_info.load_tender_status);
782 WSH_DEBUG_SV.log(l_module_name,'load_tender_number', p_trip_info.load_tender_number);
783 WSH_DEBUG_SV.log(l_module_name,'wf_name', p_trip_info.wf_name);
784 WSH_DEBUG_SV.log(l_module_name,'wf_process_name', p_trip_info.wf_process_name);
785 WSH_DEBUG_SV.log(l_module_name,'wf_item_key', p_trip_info.wf_item_key);
786 WSH_DEBUG_SV.log(l_module_name,'carrier_contact_id', p_trip_info.carrier_contact_id);
787 WSH_DEBUG_SV.log(l_module_name,'shipper_wait_time', p_trip_info.shipper_wait_time);
788 WSH_DEBUG_SV.log(l_module_name,'wait_time_uom', p_trip_info.wait_time_uom);
789 WSH_DEBUG_SV.log(l_module_name,'vessel', p_trip_info.vessel);
790 WSH_DEBUG_SV.log(l_module_name,'voyage_number', p_trip_info.voyage_number);
791 WSH_DEBUG_SV.log(l_module_name,'port_of_loading', p_trip_info.port_of_loading);
792 WSH_DEBUG_SV.log(l_module_name,'port_of_discharge', p_trip_info.port_of_discharge);
793 WSH_DEBUG_SV.log(l_module_name,'carrier_response', p_trip_info.carrier_response);
794 WSH_DEBUG_SV.log(l_module_name,'route_lane_id', p_trip_info.route_lane_id);
795 WSH_DEBUG_SV.log(l_module_name,'lane_id', p_trip_info.lane_id);
796 WSH_DEBUG_SV.log(l_module_name,'schedule_id', p_trip_info.schedule_id);
797 WSH_DEBUG_SV.log(l_module_name,'booking_number', p_trip_info.booking_number);
798
799 WSH_DEBUG_SV.log(l_module_name,'carrier_reference_number', p_trip_info.carrier_reference_number);
800 WSH_DEBUG_SV.log(l_module_name,'rank_id', p_trip_info.rank_id);
801 WSH_DEBUG_SV.log(l_module_name,'consignee_carrier_ac_no', p_trip_info.consignee_carrier_ac_no);
802 WSH_DEBUG_SV.log(l_module_name,'routing_rule_id', p_trip_info.routing_rule_id);
803 WSH_DEBUG_SV.log(l_module_name,'append_flag', p_trip_info.append_flag);
804 END IF;
805 --
806 IF (p_rowid IS NULL) THEN
807 OPEN get_row_id;
808 FETCH get_row_id INTO l_rowid;
809
810 IF (get_row_id%NOTFOUND) THEN
811 CLOSE get_row_id;
812 RAISE no_data_found;
813 END IF;
814
815 CLOSE get_row_id;
816 ELSE
817 l_rowid := p_rowid;
818 END IF;
819
820 /*CURRENTLY NOT IN USE
821 --WF: CMR
822 WSH_WF_STD.Get_Deliveries(p_trip_id => p_trip_info.trip_id,
823 x_del_ids => l_del_ids,
824 x_return_status => l_wf_rs);
825 IF l_del_ids.COUNT > 0 THEN
826 WSH_WF_STD.Get_Carrier(p_del_ids => l_del_ids,
827 x_del_old_carrier_ids => l_del_old_carrier_ids,
828 x_return_status => l_wf_rs);
829 END IF;
830 */
831
832 UPDATE wsh_trips SET
833 trip_id = p_trip_info.trip_id,
834 name = p_trip_info.name,
835 planned_flag = p_trip_info.planned_flag,
836 arrive_after_trip_id = p_trip_info.arrive_after_trip_id,
837 status_code = p_trip_info.status_code,
838 vehicle_item_id = p_trip_info.vehicle_item_id,
839 vehicle_organization_id = p_trip_info.vehicle_organization_id,
840 vehicle_number = p_trip_info.vehicle_number,
841 vehicle_num_prefix = p_trip_info.vehicle_num_prefix,
842 carrier_id = p_trip_info.carrier_id,
843 ship_method_code = p_trip_info.ship_method_code,
844 route_id = p_trip_info.route_id,
845 routing_instructions = p_trip_info.routing_instructions,
846 attribute_category = p_trip_info.attribute_category,
847 attribute1 = p_trip_info.attribute1,
848 attribute2 = p_trip_info.attribute2,
849 attribute3 = p_trip_info.attribute3,
850 attribute4 = p_trip_info.attribute4,
851 attribute5 = p_trip_info.attribute5,
852 attribute6 = p_trip_info.attribute6,
853 attribute7 = p_trip_info.attribute7,
854 attribute8 = p_trip_info.attribute8,
855 attribute9 = p_trip_info.attribute9,
856 attribute10 = p_trip_info.attribute10,
857 attribute11 = p_trip_info.attribute11,
858 attribute12 = p_trip_info.attribute12,
859 attribute13 = p_trip_info.attribute13,
860 attribute14 = p_trip_info.attribute14,
861 attribute15 = p_trip_info.attribute15,
862 last_update_date = p_trip_info.last_update_date,
863 last_updated_by = p_trip_info.last_updated_by,
864 last_update_login = p_trip_info.last_update_login,
865 program_application_id = p_trip_info.program_application_id,
866 program_id = p_trip_info.program_id,
867 program_update_date = p_trip_info.program_update_date,
868 request_id = p_trip_info.request_id,
869 /* H Integration: datamodel changes wrudge */
870 service_level = p_trip_info.service_level,
871 mode_of_transport = p_trip_info.mode_of_transport,
872 freight_terms_code = p_trip_info.freight_terms_code,
873 consolidation_allowed = p_trip_info.consolidation_allowed,
874 load_tender_status = p_trip_info.load_tender_status,
875 load_tender_number = p_trip_info.load_tender_number,
876 wf_name = p_trip_info.wf_name,
877 wf_process_name = p_trip_info.wf_process_name,
878 wf_item_key = p_trip_info.wf_item_key,
879 carrier_contact_id = p_trip_info.carrier_contact_id,
880 shipper_wait_time = p_trip_info.shipper_wait_time,
881 wait_time_uom = p_trip_info.wait_time_uom,
882 load_tendered_time = p_trip_info.load_tendered_time,
883 vessel = p_trip_info.vessel,
884 voyage_number = p_trip_info.voyage_number,
885 port_of_loading = p_trip_info.port_of_loading,
886 port_of_discharge = p_trip_info.port_of_discharge,
887 carrier_response = p_trip_info.carrier_response,
888 route_lane_id = p_trip_info.route_lane_id,
889 lane_id = p_trip_info.lane_id,
890 schedule_id = p_trip_info.schedule_id,
891 booking_number = p_trip_info.booking_number,
892 /* J Inbound Logistics: new columns jckwok */
893 shipments_type_flag = nvl(p_trip_info.shipments_type_flag, 'O'),
894 /* J TP Release : ttrichy */
895 IGNORE_FOR_PLANNING = nvl(p_trip_info.IGNORE_FOR_PLANNING, 'N'),
896 TP_PLAN_NAME = p_trip_info.TP_PLAN_NAME,
897 TP_TRIP_NUMBER = p_trip_info.TP_TRIP_NUMBER,
898 seal_code = p_trip_info.seal_code,
899 operator = p_trip_info.operator,
900 carrier_reference_number = p_trip_info.carrier_reference_number,
901 rank_id = p_trip_info.rank_id,
902 consignee_carrier_ac_no = p_trip_info.consignee_carrier_ac_no,
903 routing_rule_id = p_trip_info.routing_rule_id,
904 append_flag = p_trip_info.append_flag
905 WHERE rowid = l_rowid;
906
907 IF (SQL%NOTFOUND) THEN
908 RAISE no_data_found;
909 END IF;
910
911 /*CURRENTLY NOT IN USE
912 --WF: CMR
913 IF l_del_ids.COUNT > 0 THEN
914 WSH_WF_STD.Handle_Trip_Carriers(p_trip_id => p_trip_info.trip_id,
915 p_del_ids => l_del_ids,
916 p_del_old_carrier_ids => l_del_old_carrier_ids,
917 x_return_status => l_wf_rs);
918 END IF;
919 */
920 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
921
922 --
923 -- Debug Statements
924 --
925 IF l_debug_on THEN
926 WSH_DEBUG_SV.pop(l_module_name);
927 END IF;
928 --
929 EXCEPTION
930 WHEN no_data_found THEN
931 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NOT_FOUND');
932 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
933 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
934 --
935 -- Debug Statements
936 --
937 IF l_debug_on THEN
938 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
939 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
940 END IF;
941 --
942 --Bug # 3268641
943 WHEN DUP_VAL_ON_INDEX THEN
944 FND_MESSAGE.SET_NAME('WSH','WSH_ASSIGN_NEW_TRIP');
945 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
946 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
947
948 IF l_debug_on THEN
949 WSH_DEBUG_SV.logmsg(l_module_name,'DUP_VAL_ON_INDEX exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
950 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:DUP_VAL_ON_INDEX');
951 END IF;
952 --
953
954
955 WHEN others THEN
956 wsh_util_core.default_handler('WSH_TRIPS_PVT.UPDATE_TRIP');
957 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
958
959 --
960 -- Debug Statements
961 --
962 IF l_debug_on THEN
963 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
964 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
965 END IF;
966 --
967 END Update_Trip;
968
969
970 PROCEDURE Lock_Trip (
971 p_rowid IN VARCHAR2,
972 p_trip_info IN trip_rec_type
973 ) IS
974
975 CURSOR lock_row IS
976 SELECT
977 TRIP_ID,
978 NAME,
979 PLANNED_FLAG,
980 ARRIVE_AFTER_TRIP_ID,
981 STATUS_CODE,
982 VEHICLE_ITEM_ID,
983 VEHICLE_ORGANIZATION_ID,
984 VEHICLE_NUMBER,
985 VEHICLE_NUM_PREFIX,
986 CARRIER_ID,
987 SHIP_METHOD_CODE,
988 ROUTE_ID,
989 ROUTING_INSTRUCTIONS,
990 ATTRIBUTE_CATEGORY,
991 ATTRIBUTE1,
992 ATTRIBUTE2,
993 ATTRIBUTE3,
994 ATTRIBUTE4,
995 ATTRIBUTE5,
996 ATTRIBUTE6,
997 ATTRIBUTE7,
998 ATTRIBUTE8,
999 ATTRIBUTE9,
1000 ATTRIBUTE10,
1001 ATTRIBUTE11,
1002 ATTRIBUTE12,
1003 ATTRIBUTE13,
1004 ATTRIBUTE14,
1005 ATTRIBUTE15,
1006 CREATION_DATE,
1007 CREATED_BY,
1008 LAST_UPDATE_DATE,
1009 LAST_UPDATED_BY,
1010 LAST_UPDATE_LOGIN,
1011 PROGRAM_APPLICATION_ID,
1012 PROGRAM_ID,
1013 PROGRAM_UPDATE_DATE,
1014 REQUEST_ID,
1015 /* H Integration: datamodel changes wrudge */
1016 SERVICE_LEVEL,
1017 MODE_OF_TRANSPORT,
1018 FREIGHT_TERMS_CODE,
1019 CONSOLIDATION_ALLOWED,
1020 LOAD_TENDER_STATUS,
1021 ROUTE_LANE_ID,
1022 LANE_ID,
1023 SCHEDULE_ID,
1024 BOOKING_NUMBER,
1025 /* I WSH-FTE Load Tender Integration */
1026 LOAD_TENDER_NUMBER,
1027 VESSEL,
1028 VOYAGE_NUMBER,
1029 PORT_OF_LOADING,
1030 PORT_OF_DISCHARGE,
1031 WF_NAME,
1032 WF_PROCESS_NAME,
1033 WF_ITEM_KEY,
1034 CARRIER_CONTACT_ID,
1035 SHIPPER_WAIT_TIME,
1036 WAIT_TIME_UOM,
1037 LOAD_TENDERED_TIME,
1038 CARRIER_RESPONSE,
1039 /* J Inbound Logistics: new columns */
1040 SHIPMENTS_TYPE_FLAG,
1041 /* J TP Release : ttrichy */
1042 IGNORE_FOR_PLANNING,
1043 TP_PLAN_NAME,
1044 TP_TRIP_NUMBER,
1045 SEAL_CODE,
1046 OPERATOR,
1047 /* R12 attributes */
1048 CARRIER_REFERENCE_NUMBER,
1049 RANK_ID,
1050 CONSIGNEE_CARRIER_AC_NO,
1051 ROUTING_RULE_ID,
1052 APPEND_FLAG
1053 FROM wsh_trips
1054 WHERE rowid = p_rowid
1055 FOR UPDATE OF trip_id NOWAIT;
1056
1057 Recinfo lock_row%ROWTYPE;
1058
1059 l_gc3_is_installed VARCHAR2(1); -- OTM R12, glog proj
1060 l_ignore_flag WSH_TRIPS.IGNORE_FOR_PLANNING%TYPE;--OTM R12, glog proj
1061
1062 --
1063 l_debug_on BOOLEAN;
1064 --
1065 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_TRIP';
1066 --
1067 BEGIN
1068
1069 --
1070 -- Debug Statements
1071 --
1072 --
1073 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1074 --
1075 IF l_debug_on IS NULL
1076 THEN
1077 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1078 END IF;
1079 --
1080 IF l_debug_on THEN
1081 WSH_DEBUG_SV.push(l_module_name);
1082 --
1083 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
1084 WSH_DEBUG_SV.log(l_module_name,'trip_id', p_trip_info.trip_id);
1085 WSH_DEBUG_SV.log(l_module_name,'name', p_trip_info.name);
1086 WSH_DEBUG_SV.log(l_module_name,'planned_flag', p_trip_info.planned_flag);
1087 WSH_DEBUG_SV.log(l_module_name,'arrive_after_trip_id', p_trip_info.arrive_after_trip_id);
1088 WSH_DEBUG_SV.log(l_module_name,'status_code', p_trip_info.status_code);
1089 WSH_DEBUG_SV.log(l_module_name,'vehicle_item_id', p_trip_info.vehicle_item_id);
1090 WSH_DEBUG_SV.log(l_module_name,'vehicle_organization_id', p_trip_info.vehicle_organization_id);
1091 WSH_DEBUG_SV.log(l_module_name,'vehicle_number', p_trip_info.vehicle_number);
1092 WSH_DEBUG_SV.log(l_module_name,'vehicle_num_prefix', p_trip_info.vehicle_num_prefix);
1093 WSH_DEBUG_SV.log(l_module_name,'carrier_id', p_trip_info.carrier_id);
1094 WSH_DEBUG_SV.log(l_module_name,'ship_method_code', p_trip_info.ship_method_code);
1095 WSH_DEBUG_SV.log(l_module_name,'route_id', p_trip_info.route_id);
1096 WSH_DEBUG_SV.log(l_module_name,'routing_instructions', p_trip_info.routing_instructions);
1097 WSH_DEBUG_SV.log(l_module_name,'service_level', p_trip_info.service_level);
1098 WSH_DEBUG_SV.log(l_module_name,'mode_of_transport', p_trip_info.mode_of_transport);
1099 WSH_DEBUG_SV.log(l_module_name,'freight_terms_code', p_trip_info.freight_terms_code);
1100 WSH_DEBUG_SV.log(l_module_name,'consolidation_allowed', p_trip_info.consolidation_allowed);
1101 WSH_DEBUG_SV.log(l_module_name,'load_tender_status', p_trip_info.load_tender_status);
1102 WSH_DEBUG_SV.log(l_module_name,'route_lane_id', p_trip_info.route_lane_id);
1103 WSH_DEBUG_SV.log(l_module_name,'lane_id', p_trip_info.lane_id);
1104 WSH_DEBUG_SV.log(l_module_name,'schedule_id', p_trip_info.schedule_id);
1105 WSH_DEBUG_SV.log(l_module_name,'booking_number', p_trip_info.booking_number);
1106
1107 WSH_DEBUG_SV.log(l_module_name,'carrier_reference_number', p_trip_info.carrier_reference_number);
1108 WSH_DEBUG_SV.log(l_module_name,'consignee_carrier_ac_no', p_trip_info.consignee_carrier_ac_no);
1109 WSH_DEBUG_SV.log(l_module_name,'rank_id', p_trip_info.rank_id);
1110 WSH_DEBUG_SV.log(l_module_name,'routing_rule_id', p_trip_info.routing_rule_id);
1111 WSH_DEBUG_SV.log(l_module_name,'append_flag', p_trip_info.append_flag);
1112 END IF;
1113 --
1114
1115 --OTM R12, glog proj, use Global Variable
1116 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
1117
1118 -- If null, call the function
1119 IF l_gc3_is_installed IS NULL THEN
1120 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
1121 END IF;
1122
1123 IF l_gc3_is_installed = 'Y' THEN
1124 l_ignore_flag := 'Y';
1125 ELSE
1126 l_ignore_flag := 'N';
1127 END IF;
1128 -- end of OTM R12, glog proj
1129
1130
1131 OPEN lock_row;
1132 FETCH lock_row INTO Recinfo;
1133
1134 IF (lock_row%NOTFOUND) THEN
1135 CLOSE lock_row;
1136 FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
1137 IF l_debug_on THEN
1138 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1139 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:app_exception');
1140 END IF;
1141 app_exception.raise_exception;
1142 END IF;
1143
1144 CLOSE lock_row;
1145
1146 IF (
1147 (Recinfo.Trip_Id = p_trip_info.Trip_Id)
1148 AND (Recinfo.Name = p_trip_info.Name)
1149 AND (Recinfo.Planned_Flag = p_trip_info.Planned_Flag)
1150 AND (Recinfo.Status_Code = p_trip_info.Status_Code)
1151 AND ( (Recinfo.Arrive_After_Trip_Id = p_trip_info.Arrive_After_Trip_Id)
1152 OR ( (Recinfo.Arrive_After_Trip_Id IS NULL)
1153 AND (p_trip_info.Arrive_After_Trip_Id IS NULL)))
1154 AND ( (Recinfo.Vehicle_Item_Id = p_trip_info.Vehicle_Item_Id)
1155 OR ( (Recinfo.Vehicle_Item_Id IS NULL)
1156 AND (p_trip_info.Vehicle_Item_Id IS NULL)))
1157 AND ( (Recinfo.Vehicle_Organization_Id = p_trip_info.Vehicle_Organization_Id)
1158 OR ( (Recinfo.Vehicle_Organization_Id IS NULL)
1159 AND (p_trip_info.Vehicle_Organization_Id IS NULL)))
1160 AND ( (Recinfo.Vehicle_Number = p_trip_info.Vehicle_Number)
1161 OR ( (Recinfo.Vehicle_Number IS NULL)
1162 AND (p_trip_info.Vehicle_Number IS NULL)))
1163 AND ( (Recinfo.Vehicle_Num_Prefix = p_trip_info.Vehicle_Num_Prefix)
1164 OR ( (Recinfo.Vehicle_Num_Prefix IS NULL)
1165 AND (p_trip_info.Vehicle_Num_Prefix IS NULL)))
1166 AND ( (Recinfo.Carrier_Id = p_trip_info.Carrier_Id)
1167 OR ( (Recinfo.Carrier_Id IS NULL)
1168 AND (p_trip_info.Carrier_Id IS NULL)))
1169 AND ( (Recinfo.Ship_Method_Code = p_trip_info.Ship_Method_Code)
1170 OR ( (Recinfo.Ship_Method_Code IS NULL)
1171 AND (p_trip_info.Ship_Method_Code IS NULL)))
1172 AND ( (Recinfo.Route_Id = p_trip_info.Route_Id)
1173 OR ( (Recinfo.Route_Id IS NULL)
1174 AND (p_trip_info.Route_Id IS NULL)))
1175 AND ( (Recinfo.Routing_Instructions = p_trip_info.Routing_Instructions)
1176 OR ( (Recinfo.Routing_Instructions IS NULL)
1177 AND (p_trip_info.Routing_Instructions IS NULL)))
1178 AND ( (Recinfo.Creation_Date = p_trip_info.Creation_Date)
1179 OR ( (Recinfo.Creation_Date IS NULL)
1180 AND (p_trip_info.Creation_Date IS NULL)))
1181 AND ( (Recinfo.Created_By = p_trip_info.Created_By)
1182 OR ( (Recinfo.Created_By IS NULL)
1183 AND (p_trip_info.Created_By IS NULL)))
1184 AND ( (Recinfo.Last_Update_Date = p_trip_info.Last_Update_Date)
1185 OR ( (Recinfo.Last_Update_Date IS NULL)
1186 AND (p_trip_info.Last_Update_Date IS NULL)))
1187 AND ( (Recinfo.Last_Updated_By = p_trip_info.Last_Updated_By)
1188 OR ( (Recinfo.Last_Updated_By IS NULL)
1189 AND (p_trip_info.Last_Updated_By IS NULL)))
1190 AND ( (Recinfo.Last_Update_Login = p_trip_info.Last_Update_Login)
1191 OR ( (Recinfo.Last_Update_Login IS NULL)
1192 AND (p_trip_info.Last_Update_Login IS NULL)))
1193 AND ( (Recinfo.Program_Application_Id = p_trip_info.Program_Application_Id)
1194 OR ( (Recinfo.Program_Application_Id IS NULL)
1195 AND (p_trip_info.Program_Application_Id IS NULL)))
1196 AND ( (Recinfo.Program_Id = p_trip_info.Program_Id)
1197 OR ( (Recinfo.Program_Id IS NULL)
1198 AND (p_trip_info.Program_Id IS NULL)))
1199 AND ( (Recinfo.Program_Update_Date = p_trip_info.Program_Update_Date)
1200 OR ( (Recinfo.Program_Update_Date IS NULL)
1201 AND (p_trip_info.Program_Update_Date IS NULL)))
1202 AND ( (Recinfo.Request_Id = p_trip_info.Request_Id)
1203 OR ( (Recinfo.Request_Id IS NULL)
1204 AND (p_trip_info.Request_Id IS NULL)))
1205 AND ( (Recinfo.Attribute_Category = p_trip_info.Attribute_Category)
1206 OR ( (Recinfo.Attribute_Category IS NULL)
1207 AND (p_trip_info.Attribute_Category IS NULL)))
1208 AND ( (Recinfo.Attribute1 = p_trip_info.Attribute1)
1209 OR ( (Recinfo.Attribute1 IS NULL)
1210 AND (p_trip_info.Attribute1 IS NULL)))
1211 AND ( (Recinfo.Attribute2 = p_trip_info.Attribute2)
1212 OR ( (Recinfo.Attribute2 IS NULL)
1213 AND (p_trip_info.Attribute2 IS NULL)))
1214 AND ( (Recinfo.Attribute3 = p_trip_info.Attribute3)
1215 OR ( (Recinfo.Attribute3 IS NULL)
1216 AND (p_trip_info.Attribute3 IS NULL)))
1217 AND ( (Recinfo.Attribute4 = p_trip_info.Attribute4)
1218 OR ( (Recinfo.Attribute4 IS NULL)
1219 AND (p_trip_info.Attribute4 IS NULL)))
1220 AND ( (Recinfo.Attribute5 = p_trip_info.Attribute5)
1221 OR ( (Recinfo.Attribute5 IS NULL)
1222 AND (p_trip_info.Attribute5 IS NULL)))
1223 AND ( (Recinfo.Attribute6 = p_trip_info.Attribute6)
1224 OR ( (Recinfo.Attribute6 IS NULL)
1225 AND (p_trip_info.Attribute6 IS NULL)))
1226 AND ( (Recinfo.Attribute7 = p_trip_info.Attribute7)
1227 OR ( (Recinfo.Attribute7 IS NULL)
1228 AND (p_trip_info.Attribute7 IS NULL)))
1229 AND ( (Recinfo.Attribute8 = p_trip_info.Attribute8)
1230 OR ( (Recinfo.Attribute8 IS NULL)
1231 AND (p_trip_info.Attribute8 IS NULL)))
1232 AND ( (Recinfo.Attribute9 = p_trip_info.Attribute9)
1233 OR ( (Recinfo.Attribute9 IS NULL)
1234 AND (p_trip_info.Attribute9 IS NULL)))
1235 AND ( (Recinfo.Attribute10 = p_trip_info.Attribute10)
1236 OR ( (Recinfo.Attribute10 IS NULL)
1237 AND (p_trip_info.Attribute10 IS NULL)))
1238 AND ( (Recinfo.Attribute11 = p_trip_info.Attribute11)
1239 OR ( (Recinfo.Attribute11 IS NULL)
1240 AND (p_trip_info.Attribute11 IS NULL)))
1241 AND ( (Recinfo.Attribute12 = p_trip_info.Attribute12)
1242 OR ( (Recinfo.Attribute12 IS NULL)
1243 AND (p_trip_info.Attribute12 IS NULL)))
1244 AND ( (Recinfo.Attribute13 = p_trip_info.Attribute13)
1245 OR ( (Recinfo.Attribute13 IS NULL)
1246 AND (p_trip_info.Attribute13 IS NULL)))
1247 AND ( (Recinfo.Attribute14 = p_trip_info.Attribute14)
1248 OR ( (Recinfo.Attribute14 IS NULL)
1249 AND (p_trip_info.Attribute14 IS NULL)))
1250 AND ( (Recinfo.Attribute15 = p_trip_info.Attribute15)
1251 OR ( (Recinfo.Attribute15 IS NULL)
1252 AND (p_trip_info.Attribute15 IS NULL)))
1253 /* H Integration: datamodel changes wrudge */
1254 AND ( (Recinfo.service_level = p_trip_info.service_level)
1255 OR ( (Recinfo.service_level IS NULL)
1256 AND (p_trip_info.service_level IS NULL)))
1257 AND ( (Recinfo.mode_of_transport = p_trip_info.mode_of_transport)
1258 OR ( (Recinfo.mode_of_transport IS NULL)
1259 AND (p_trip_info.mode_of_transport IS NULL)))
1260 AND ( (Recinfo.freight_terms_code = p_trip_info.freight_terms_code)
1261 OR ( (Recinfo.freight_terms_code IS NULL)
1262 AND (p_trip_info.freight_terms_code IS NULL)))
1263 AND ( (Recinfo.consolidation_allowed = p_trip_info.consolidation_allowed)
1264 OR ( (Recinfo.consolidation_allowed IS NULL)
1265 AND (p_trip_info.consolidation_allowed IS NULL)))
1266 AND ( (Recinfo.load_tender_status = p_trip_info.load_tender_status)
1267 OR ( (Recinfo.load_tender_status IS NULL)
1268 AND (p_trip_info.load_tender_status IS NULL)))
1269 AND ( (Recinfo.route_lane_id = p_trip_info.route_lane_id)
1270 OR ( (Recinfo.route_lane_id IS NULL)
1271 AND (p_trip_info.route_lane_id IS NULL)))
1272 AND ( (Recinfo.lane_id = p_trip_info.lane_id)
1273 OR ( (Recinfo.lane_id IS NULL)
1274 AND (p_trip_info.lane_id IS NULL)))
1275 AND ( (Recinfo.schedule_id = p_trip_info.schedule_id)
1276 OR ( (Recinfo.schedule_id IS NULL)
1277 AND (p_trip_info.schedule_id IS NULL)))
1278 AND ( (Recinfo.booking_number = p_trip_info.booking_number)
1279 OR ( (Recinfo.booking_number IS NULL)
1280 AND (p_trip_info.booking_number IS NULL)))
1281 AND ( (nvl(Recinfo.shipments_type_flag, 'O') = nvl(p_trip_info.shipments_type_flag,'O') )
1282 OR ( (Recinfo.shipments_type_flag IS NULL)
1283 AND (p_trip_info.shipments_type_flag IS NULL)))
1284 /* J TP Release : ttrichy */
1285 -- OTM R12, glog project, for l_ignore_flag
1286 AND (nvl(Recinfo.IGNORE_FOR_PLANNING, l_ignore_flag) = nvl(p_trip_info.ignore_for_planning,l_ignore_flag))
1287 AND ( (Recinfo.TP_PLAN_NAME = p_trip_info.TP_PLAN_NAME)
1288 OR ( (Recinfo.TP_PLAN_NAME IS NULL)
1289 AND (p_trip_info.TP_PLAN_NAME IS NULL)))
1290 AND ( (Recinfo.TP_TRIP_NUMBER = p_trip_info.TP_TRIP_NUMBER )
1291 OR ( (Recinfo.TP_TRIP_NUMBER IS NULL)
1292 AND (p_trip_info.TP_TRIP_NUMBER IS NULL)))
1293 AND ( (Recinfo.seal_code = p_trip_info.seal_code )
1294 OR ( (Recinfo.seal_code IS NULL)
1295 AND (p_trip_info.seal_code IS NULL)))
1296 AND ( (Recinfo.operator = p_trip_info.operator )
1297 OR ( (Recinfo.operator IS NULL)
1298 AND (p_trip_info.operator IS NULL)))
1299 AND ( (Recinfo.carrier_reference_number = p_trip_info.carrier_reference_number )
1300 OR ( (Recinfo.carrier_reference_number IS NULL)
1301 AND (p_trip_info.carrier_reference_number IS NULL)))
1302 AND ( (Recinfo.consignee_carrier_ac_no = p_trip_info.consignee_carrier_ac_no )
1303 OR ( (Recinfo.consignee_carrier_ac_no IS NULL)
1304 AND (p_trip_info.consignee_carrier_ac_no IS NULL)))
1305 -- do not check these columns because they are not in WSH UI
1306 -- ROUTING_RULE_ID
1307 -- APPEND_FLAG
1308 -- RANK_ID
1309 ) THEN
1310 --
1311 -- Debug Statements
1312 --
1313 IF l_debug_on THEN
1314 WSH_DEBUG_SV.pop(l_module_name);
1315 END IF;
1316 --
1317 return;
1318 ELSE
1319 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1320 IF l_debug_on THEN
1321 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1322 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:app_exception');
1323 END IF;
1324 app_exception.raise_exception;
1325 END IF;
1326
1327 --
1328 -- Debug Statements
1329 --
1330 IF l_debug_on THEN
1331 WSH_DEBUG_SV.pop(l_module_name);
1332 END IF;
1333 --
1334 EXCEPTION
1335 WHEN app_exception.application_exception or app_exception.record_lock_exception THEN
1336 if (lock_row%ISOPEN) then
1337 close lock_row;
1338 end if;
1339 --
1340 IF l_debug_on THEN
1341 WSH_DEBUG_SV.logmsg(l_module_name,'APP_EXCEPTION.APPLICATION_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1342 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:APP_EXCEPTION.APPLICATION_EXCEPTION');
1343 END IF;
1344 --
1345 RAISE;
1346 --
1347 WHEN others THEN
1348 --
1349 if (lock_row%ISOPEN) then
1350 close lock_row;
1351 end if;
1352 --
1353 IF l_debug_on THEN
1354 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1355 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1356 END IF;
1357 --
1358 raise;
1359 --
1360 END Lock_Trip;
1361
1362 Procedure Populate_Record(
1363 p_trip_id IN NUMBER,
1364 x_trip_info OUT NOCOPY trip_rec_type,
1365 x_return_status OUT NOCOPY VARCHAR2) IS
1366
1367 CURSOR trip_record(p_ignore_flag IN VARCHAR2) IS
1368 SELECT
1369 TRIP_ID,
1370 NAME,
1371 PLANNED_FLAG,
1372 ARRIVE_AFTER_TRIP_ID,
1373 STATUS_CODE,
1374 VEHICLE_ITEM_ID,
1375 VEHICLE_ORGANIZATION_ID,
1376 VEHICLE_NUMBER,
1377 VEHICLE_NUM_PREFIX,
1378 CARRIER_ID,
1379 SHIP_METHOD_CODE,
1380 ROUTE_ID,
1381 ROUTING_INSTRUCTIONS,
1382 ATTRIBUTE_CATEGORY,
1383 ATTRIBUTE1,
1384 ATTRIBUTE2,
1385 ATTRIBUTE3,
1386 ATTRIBUTE4,
1387 ATTRIBUTE5,
1388 ATTRIBUTE6,
1389 ATTRIBUTE7,
1390 ATTRIBUTE8,
1391 ATTRIBUTE9,
1392 ATTRIBUTE10,
1393 ATTRIBUTE11,
1394 ATTRIBUTE12,
1395 ATTRIBUTE13,
1396 ATTRIBUTE14,
1397 ATTRIBUTE15,
1398 CREATION_DATE,
1399 CREATED_BY,
1400 LAST_UPDATE_DATE,
1401 LAST_UPDATED_BY,
1402 LAST_UPDATE_LOGIN,
1403 PROGRAM_APPLICATION_ID,
1404 PROGRAM_ID,
1405 PROGRAM_UPDATE_DATE,
1406 REQUEST_ID,
1407 /* H Integration: datamodel changes wrudge */
1408 SERVICE_LEVEL,
1409 MODE_OF_TRANSPORT,
1410 FREIGHT_TERMS_CODE,
1411 CONSOLIDATION_ALLOWED,
1412 LOAD_TENDER_STATUS,
1413 ROUTE_LANE_ID,
1414 LANE_ID,
1415 SCHEDULE_ID,
1416 BOOKING_NUMBER,
1417 /* I Harmonization: Nondatabase Columns Added rvishnuv */
1418 ROWID,
1419 NULL,
1420 NULL,
1421 NULL,
1422 NULL,
1423 /* I WSH-FTE Load Tender Integration */
1424 LOAD_TENDER_NUMBER,
1425 VESSEL,
1426 VOYAGE_NUMBER,
1427 PORT_OF_LOADING,
1428 PORT_OF_DISCHARGE,
1429 WF_NAME,
1430 WF_PROCESS_NAME,
1431 WF_ITEM_KEY,
1432 CARRIER_CONTACT_ID,
1433 SHIPPER_WAIT_TIME,
1434 WAIT_TIME_UOM,
1435 LOAD_TENDERED_TIME,
1436 CARRIER_RESPONSE,
1437 /* J Inbound Logistics: new columns */
1438 SHIPMENTS_TYPE_FLAG,
1439 /* J TP Release : ttrichy */
1440 --OTM R12,glog proj
1441 NVL(ignore_for_planning, p_ignore_flag),
1442 TP_PLAN_NAME,
1443 TP_TRIP_NUMBER,
1444 SEAL_CODE,
1445 OPERATOR,
1446 /* R12 attributes */
1447 CARRIER_REFERENCE_NUMBER,
1448 RANK_ID,
1449 CONSIGNEE_CARRIER_AC_NO,
1450 ROUTING_RULE_ID,
1451 APPEND_FLAG
1452 FROM wsh_trips
1453 WHERE trip_id = p_trip_id;
1454
1455 others EXCEPTION;
1456
1457 l_ignore_for_planning WSH_TRIPS.IGNORE_FOR_PLANNING%TYPE;
1458 l_gc3_is_installed VARCHAR2(1); -- OTM R12, glog proj
1459
1460 --
1461 l_debug_on BOOLEAN;
1462 --
1463 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'POPULATE_RECORD';
1464 --
1465 BEGIN
1466
1467 --
1468 -- Debug Statements
1469 --
1470 --
1471 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1472 --
1473 IF l_debug_on IS NULL
1474 THEN
1475 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1476 END IF;
1477 --
1478 IF l_debug_on THEN
1479 WSH_DEBUG_SV.push(l_module_name);
1480 --
1481 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
1482 END IF;
1483 --
1484 IF (p_trip_id IS NULL) THEN
1485 raise others;
1486 END IF;
1487
1488 --OTM R12, glog proj, use Global Variable
1489 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
1490
1491 -- If null, call the function
1492 IF l_gc3_is_installed IS NULL THEN
1493 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
1494 END IF;
1495 IF l_gc3_is_installed = 'Y' THEN
1496 l_ignore_for_planning := 'Y';
1497 ELSE
1498 l_ignore_for_planning := 'N';
1499 END IF;
1500 -- end of OTM R12, glog proj
1501
1502 OPEN trip_record(l_ignore_for_planning);
1503 FETCH trip_record INTO x_trip_info;
1504
1505 IF (trip_record%NOTFOUND) THEN
1506 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NOT_FOUND');
1507 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1508 wsh_util_core.add_message(x_return_status);
1509 ELSE
1510 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1511 END IF;
1512
1513 CLOSE trip_record;
1514
1515 --
1516 -- Debug Statements
1517 --
1518 IF l_debug_on THEN
1519 WSH_DEBUG_SV.pop(l_module_name);
1520 END IF;
1521 --
1522 EXCEPTION
1523 WHEN others THEN
1524 wsh_util_core.default_handler('WSH_TRIPS_PVT.POPULATE_RECORD');
1525 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1526
1527 --
1528 -- Debug Statements
1529 --
1530 IF l_debug_on THEN
1531 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1532 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1533 END IF;
1534 --
1535 END Populate_Record;
1536
1537
1538
1539 FUNCTION Get_Name
1540 (p_trip_id IN NUMBER
1541 ) RETURN VARCHAR2 IS
1542
1543 CURSOR get_name IS
1544 SELECT name
1545 FROM wsh_trips
1546 WHERE trip_id = p_trip_id;
1547
1548 x_name VARCHAR2(30);
1549
1550 others EXCEPTION;
1551
1552 --
1553 l_debug_on BOOLEAN;
1554 --
1555 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_NAME';
1556 --
1557 BEGIN
1558
1559 --
1560 -- Debug Statements
1561 --
1562 --
1563 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1564 --
1565 IF l_debug_on IS NULL
1566 THEN
1567 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1568 END IF;
1569 --
1570 IF l_debug_on THEN
1571 WSH_DEBUG_SV.push(l_module_name);
1572 --
1573 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
1574 END IF;
1575 --
1576 IF (p_trip_id IS NULL) THEN
1577 raise others;
1578 END IF;
1579
1580 OPEN get_name;
1581 FETCH get_name INTO x_name;
1582 CLOSE get_name;
1583
1584 IF (x_name IS NULL) THEN
1585 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NOT_FOUND');
1586 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
1587 --
1588 -- Debug Statements
1589 --
1590 IF l_debug_on THEN
1591 WSH_DEBUG_SV.pop(l_module_name);
1592 END IF;
1593 --
1594 RETURN null;
1595 END IF;
1596
1597 --
1598 -- Debug Statements
1599 --
1600 IF l_debug_on THEN
1601 WSH_DEBUG_SV.pop(l_module_name);
1602 END IF;
1603 --
1604 RETURN x_name;
1605
1606 EXCEPTION
1607
1608 WHEN others THEN
1609 wsh_util_core.default_handler('WSH_TRIPS_PVT.GET_NAME');
1610 --
1611 -- Debug Statements
1612 --
1613 IF l_debug_on THEN
1614 WSH_DEBUG_SV.pop(l_module_name);
1615 END IF;
1616 --
1617 RETURN null;
1618
1619 --
1620 -- Debug Statements
1621 --
1622 IF l_debug_on THEN
1623 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1624 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1625 END IF;
1626 --
1627 END Get_Name;
1628
1629 procedure Lock_Trip(
1630 p_rec_attr_tab IN Trip_Attr_Tbl_Type,
1631 p_caller IN VARCHAR2,
1632 p_valid_index_tab IN wsh_util_core.id_tab_type,
1633 x_valid_ids_tab OUT NOCOPY wsh_util_core.id_tab_type,
1634 x_return_status OUT NOCOPY VARCHAR2
1635 )
1636 IS
1637 --
1638 --
1639 l_index NUMBER := 0;
1640 l_num_errors NUMBER := 0;
1641 --
1642 l_debug_on BOOLEAN;
1643 --
1644 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_TRIP_WRAPPER';
1645 --
1646 BEGIN
1647 --
1648 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1649 --
1650 IF l_debug_on IS NULL
1651 THEN
1652 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1653 END IF;
1654 --
1655 IF l_debug_on THEN
1656 WSH_DEBUG_SV.push(l_module_name);
1657 WSH_DEBUG_SV.log(l_module_name,'p_caller',p_caller);
1658 WSH_DEBUG_SV.log(l_module_name,'Total Number of Trip Records being locked',p_valid_index_tab.COUNT);
1659 END IF;
1660 --
1661 --
1662 l_index := p_valid_index_tab.FIRST;
1663 --
1664 while l_index is not null loop
1665 begin
1666 --
1667 savepoint lock_trip_loop;
1668 --
1669 IF p_caller = 'WSH_FSTRX' THEN
1670 lock_trip(p_rowid => p_rec_attr_tab(l_index).rowid,
1671 p_trip_info => p_rec_attr_tab(l_index)
1672 );
1673 ELSE
1674 lock_trip_no_compare(p_rec_attr_tab(l_index).trip_id);
1675 END IF;
1676
1677 IF nvl(p_caller,FND_API.G_MISS_CHAR) <> 'WSH_FSTRX' THEN
1678 x_valid_ids_tab(x_valid_ids_tab.COUNT + 1) := p_rec_attr_tab(l_index).trip_id;
1679 ELSE
1680 x_valid_ids_tab(x_valid_ids_tab.COUNT + 1) := l_index;
1681 END IF;
1682 --
1683 exception
1684 --
1685 WHEN app_exception.application_exception or app_exception.record_lock_exception THEN
1686 rollback to lock_trip_loop;
1687 IF nvl(p_caller,FND_API.G_MISS_CHAR) = 'WSH_PUB'
1688 OR nvl(p_caller, '!') like 'FTE%' THEN
1689 FND_MESSAGE.SET_NAME('WSH', 'WSH_TRIP_LOCK_FAILED');
1690 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',p_rec_attr_tab(l_index).name);
1691 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
1692 END IF;
1693
1694 l_num_errors := l_num_errors + 1;
1695 --
1696 IF l_debug_on THEN
1697 WSH_DEBUG_SV.log(l_module_name,'Unable to obtain lock on the Trip Id',p_rec_attr_tab(l_index).trip_id);
1698 END IF;
1699 --
1700 WHEN others THEN
1701 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1702 end;
1703 --
1704 l_index := p_valid_index_tab.NEXT(l_index);
1705 --
1706 end loop;
1707
1708 IF p_valid_index_tab.COUNT = 0 THEN
1709 x_return_status := wsh_util_core.g_ret_sts_success;
1710 ELSIF l_num_errors = p_valid_index_tab.COUNT THEN
1711 FND_MESSAGE.SET_NAME('WSH', 'WSH_UI_NOT_PERFORMED');
1712 x_return_status := wsh_util_core.g_ret_sts_error;
1713 wsh_util_core.add_message(x_return_status,l_module_name);
1714 IF l_debug_on THEN
1715 wsh_debug_sv.logmsg(l_module_name, 'WSH_UI_NOT_PERFORMED');
1716 END IF;
1717 RAISE FND_API.G_EXC_ERROR;
1718 ELSIF l_num_errors > 0 THEN
1719 FND_MESSAGE.SET_NAME('WSH', 'WSH_UI_NOT_PROCESSED');
1720 x_return_status := wsh_util_core.g_ret_sts_warning;
1721 wsh_util_core.add_message(x_return_status,l_module_name);
1722 IF l_debug_on THEN
1723 wsh_debug_sv.logmsg(l_module_name, 'WSH_UI_NOT_PROCESSED');
1724 END IF;
1725 raise wsh_util_core.g_exc_warning;
1726 ELSE
1727 x_return_status := wsh_util_core.g_ret_sts_success;
1728 END IF;
1729
1730 --
1731 IF l_debug_on THEN
1732 WSH_DEBUG_SV.pop(l_module_name);
1733 END IF;
1734 --
1735 EXCEPTION
1736 --
1737 --
1738 WHEN FND_API.G_EXC_ERROR THEN
1739 --
1740 x_return_status := wsh_util_core.g_ret_sts_error;
1741 --
1742 IF l_debug_on THEN
1743 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1744 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1745 END IF;
1746 --
1747 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1748 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
1749 --
1750 IF l_debug_on THEN
1751 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1752 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1753 END IF;
1754 --
1755 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1756 x_return_status := wsh_util_core.g_ret_sts_warning;
1757 --
1758 IF l_debug_on THEN
1759 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1760 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1761 END IF;
1762 --
1763 WHEN OTHERS THEN
1764 --
1765 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
1766 --
1767 wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.LOCK_TRIP_WRAPPER',l_module_name);
1768 --
1769 IF l_debug_on THEN
1770 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1771 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1772 END IF;
1773 --
1774 END Lock_Trip;
1775
1776
1777 PROCEDURE lock_trip_no_compare (p_trip_id IN NUMBER)
1778 IS
1779 l_trip_id NUMBER;
1780 l_debug_on BOOLEAN;
1781 --
1782 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.'
1783 || 'lock_trip_no_compare';
1784 CURSOR c_lock_trip IS
1785 SELECT trip_id
1786 FROM wsh_trips
1787 WHERE trip_id = p_trip_id
1788 FOR UPDATE NOWAIT;
1789
1790 BEGIN
1791 --
1792 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1793 --
1794 IF l_debug_on IS NULL
1795 THEN
1796 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1797 END IF;
1798 --
1799 IF l_debug_on THEN
1800 WSH_DEBUG_SV.push(l_module_name);
1801 WSH_DEBUG_SV.log(l_module_name,'p_trip_id',p_trip_id);
1802 END IF;
1803
1804 OPEN c_lock_trip;
1805 FETCH c_lock_trip INTO l_trip_id;
1806 CLOSE c_lock_trip;
1807
1808 IF l_debug_on THEN
1809 WSH_DEBUG_SV.log(l_module_name,'trip id is locked',l_trip_id);
1810 END IF;
1811
1812 IF l_debug_on THEN
1813 WSH_DEBUG_SV.pop(l_module_name);
1814 END IF;
1815
1816 EXCEPTION
1817 WHEN app_exception.application_exception
1818 OR app_exception.record_lock_exception THEN
1819 IF l_debug_on THEN
1820 wsh_debug_sv.log(l_module_name, 'Could not lock trip', p_trip_id);
1821 WSH_DEBUG_SV.pop(l_module_name, 'EXCEPTION:APPLICTION_EXCEPTION');
1822 END IF;
1823 --
1824 RAISE;
1825
1826 WHEN OTHERS THEN
1827 --
1828 wsh_util_core.default_handler('WSH_TRIPS_PVT.lock_trip_no_compare',l_module_name);
1829 --
1830 IF l_debug_on THEN
1831 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1832 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1833 END IF;
1834 --
1835 RAISE;
1836 END lock_trip_no_compare;
1837
1838 END WSH_TRIPS_PVT;