1 PACKAGE BODY WSH_TRIP_VALIDATIONS as
2 /* $Header: WSHTRVLB.pls 120.10.12000000.6 2007/01/26 20:56:27 rvishnuv ship $ */
3
4 --
5 --3509004:public api changes
6 PROCEDURE user_non_updatable_columns
7 (p_user_in_rec IN WSH_TRIPS_PVT.trip_rec_type,
8 p_out_rec IN WSH_TRIPS_PVT.trip_rec_type,
9 p_in_rec IN WSH_TRIPS_GRP.TripInRecType,
10 x_return_status OUT NOCOPY VARCHAR2);
11
12
13 -----------------------------------------------------------------------------
14 --
15 -- Procedure: Check_Plan
16 -- Parameters: trip_id, x_return_status
17 -- Description: Checks for Plan action pre-requisites which are
18 -- - at least two stops are assigned
19 -- - Vehicle or Ship Method information is specified
20 -- - Stop sequences are valid
21 -- - If trip has vehicle information then vehicle is not over/under filled at any stop [warning]
22 -- - At least one delivery is assigned to trip [warning]
23 -- NOTE: Planning of a trip would automatically update weight/volume
24 -- information for stops and deliveries if they are not already specified.
25 --
26 -----------------------------------------------------------------------------
27
28 --
29 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_TRIP_VALIDATIONS';
30 --
31 PROCEDURE Check_Plan ( p_trip_id IN NUMBER,
32 x_return_status OUT NOCOPY VARCHAR2,
33 p_caller IN VARCHAR2) IS
34
35 CURSOR stops_exist IS
36 SELECT count(*)
37 FROM wsh_trip_stops
38 WHERE trip_id = p_trip_id;
39
40 CURSOR trip_info IS
41 SELECT vehicle_item_id,
42 ship_method_code,
43 mode_of_transport
44 FROM wsh_trips
45 WHERE trip_id = p_trip_id;
46
47 CURSOR deliveries_exist IS
48 SELECT dl.delivery_id
49 FROM wsh_trips t,
50 wsh_trip_stops st,
51 wsh_delivery_legs dg,
52 wsh_new_deliveries dl
53 WHERE t.trip_id = p_trip_id AND
54 st.trip_id = t.trip_id AND
55 dg.pick_up_stop_id = st.stop_id AND
56 dl.delivery_id = dg.delivery_id;
57
58 CURSOR stops_info IS
59 SELECT stop_id,
60 departure_fill_percent,
61 departure_gross_weight,
62 departure_volume
63 FROM wsh_trip_stops
64 WHERE trip_id = p_trip_id;
65
66 CURSOR vehicle_info IS
67 SELECT msi.minimum_fill_percent,
68 msi.maximum_load_weight,
69 msi.internal_volume
70 FROM wsh_trips t,
71 mtl_system_items msi
72 WHERE t.trip_id = p_trip_id AND
73 t.vehicle_item_id = msi.inventory_item_id AND
74 t.vehicle_organization_id = msi.organization_id;
75
76 l_numstops BINARY_INTEGER;
77 l_del_id BINARY_INTEGER := NULL;
78 l_vehicle NUMBER;
79 l_min_fill NUMBER;
80 l_max_wt NUMBER;
81 l_max_vol NUMBER;
82 l_ship_method VARCHAR2(30);
83 l_mode wsh_trips.mode_of_transport%TYPE;
84
85 --
86 l_debug_on BOOLEAN;
87 --
88 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_PLAN';
89 --
90 BEGIN
91
92 --
93 -- Debug Statements
94 --
95 --
96 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
97 --
98 IF l_debug_on IS NULL
99 THEN
100 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
101 END IF;
102 --
103 IF l_debug_on THEN
104 WSH_DEBUG_SV.push(l_module_name);
105 --
106 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
107 END IF;
108 --
109 OPEN stops_exist;
110 FETCH stops_exist INTO l_numstops;
111 CLOSE stops_exist;
112
113 IF ( nvl(l_numstops,0) < 2) THEN
114 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NO_STOPS');
115 --
116 -- Debug Statements
117 --
118 IF l_debug_on THEN
119 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
120 END IF;
121 --
122 FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_id));
123 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
124 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
125 --
126 -- Debug Statements
127 --
128 IF l_debug_on THEN
129 WSH_DEBUG_SV.pop(l_module_name);
130 END IF;
131 --
132 RETURN;
133 END IF;
134
135 OPEN trip_info;
136 FETCH trip_info INTO l_vehicle, l_ship_method, l_mode;
137
138 IF (trip_info%NOTFOUND) THEN
139 CLOSE trip_info;
140 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NOT_FOUND');
141 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
142 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
143 --
144 -- Debug Statements
145 --
146 IF l_debug_on THEN
147 WSH_DEBUG_SV.pop(l_module_name);
148 END IF;
149 --
150 RETURN;
151 END IF;
152
153 CLOSE trip_info;
154
155 --changes in 'firm' behavior - only ship method is reqd.
156 --if tp is installed and mode happens to be TL, vehicle is required
157 IF (l_ship_method IS NULL) THEN
158 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_VEHICLE_SH_M_REQ');
159 FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_id));
160 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
161 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
162 --
163 -- Debug Statements
164 --
165 IF l_debug_on THEN
166 WSH_DEBUG_SV.pop(l_module_name);
167 END IF;
168 --
169 RETURN;
170 END IF;
171
172 IF l_mode='TRUCK' and l_vehicle is NULL AND WSH_UTIL_CORE.TP_IS_INSTALLED='Y' THEN
173 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_MODE_TR_REQ_VEH');
174 IF l_debug_on THEN
175 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
176 END IF;
177 FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_id));
178 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
179 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
180 --
181 -- Debug Statements
182 --
183 IF l_debug_on THEN
184 WSH_DEBUG_SV.pop(l_module_name);
185 END IF;
186 --
187 RETURN;
188 END IF;
189
190 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
191
192 OPEN deliveries_exist;
193 FETCH deliveries_exist INTO l_del_id;
194 CLOSE deliveries_exist;
195
196 IF (l_del_id IS NULL) THEN
197 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NO_DELIVERIES');
198 --
199 -- Debug Statements
200 --
201 IF l_debug_on THEN
202 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
203 END IF;
204 --
205 FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_id));
206 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
207 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
208 END IF;
209
210 IF (l_vehicle IS NOT NULL) THEN
211 OPEN vehicle_info;
212 FETCH vehicle_info INTO l_min_fill, l_max_wt, l_max_vol;
213 CLOSE vehicle_info;
214
215 FOR st IN stops_info LOOP
216 IF ((st.departure_fill_percent IS NOT NULL) AND ( l_min_fill IS NOT NULL) AND (st.departure_fill_percent < l_min_fill)) THEN
217 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_FILL_PC_MIN');
218 --
219 -- Debug Statements
220 --
221 IF l_debug_on THEN
222 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
223 END IF;
224 --
225 -- Bug 3697947
226 FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(st.stop_id,p_caller));
227 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
228 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
229 ELSIF ((st.departure_fill_percent IS NOT NULL) AND (st.departure_fill_percent > 100)) THEN
230 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_FILL_PC_MAX');
231 --
232 -- Debug Statements
233 --
234 IF l_debug_on THEN
235 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
236 END IF;
237 --
238 -- Bug 3697947
239 FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(st.stop_id,p_caller));
240 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
241 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
242 END IF;
243 IF ((st.departure_gross_weight IS NOT NULL) AND ( l_max_wt IS NOT NULL) AND (st.departure_gross_weight > l_max_wt)) OR
244 ((st.departure_fill_percent IS NOT NULL) AND ( l_max_vol IS NOT NULL) AND (st.departure_volume > l_max_vol)) THEN
245 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_MAX_WT_VOL_EXCEEDED');
246 --
247 -- Debug Statements
248 --
249 IF l_debug_on THEN
250 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
251 END IF;
252 --
253 -- Bug 3697947
254 FND_MESSAGE.SET_TOKEN('STOP_NAME', wsh_trip_stops_pvt.get_name(st.stop_id, p_caller));
255 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
256 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
257 END IF;
258
259 END LOOP;
260
261 END IF;
262
263 --
264 -- Debug Statements
265 --
266 IF l_debug_on THEN
267 WSH_DEBUG_SV.pop(l_module_name);
268 END IF;
269 --
270 EXCEPTION
271 WHEN others THEN
272 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.CHECK_PLAN');
273 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
274 IF l_debug_on THEN
275 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
276 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
277 END IF;
278
279 END Check_Plan;
280
281
282
283
284 -----------------------------------------------------------------------------
285 --
286 -- Procedure: Check_Unplan
287 -- Parameters: trip_id, x_return_status
288 -- Description: Checks for Unplan action pre-requisites which are
289 -- - Trip status is not CLOSED
290 -- - Trip is planned
291 --
292 -----------------------------------------------------------------------------
293
294 PROCEDURE Check_Unplan ( p_trip_id IN NUMBER,
295 x_return_status OUT NOCOPY VARCHAR2) IS
296
297 CURSOR trip_info IS
298 SELECT status_code,
299 planned_flag,
300 NVL(shipments_type_flag,'O') shipments_type_flag -- J-IB-NPARIKH
301 FROM wsh_trips
302 WHERE trip_id = p_trip_id;
303
304 l_status_code VARCHAR2(2);
305 l_planned_flag VARCHAR2(1);
306
307 --
308 l_debug_on BOOLEAN;
309 --
310 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_UNPLAN';
311 --
312 l_shipments_type_flag VARCHAR2(30);
313 BEGIN
314
315 --
316 -- Debug Statements
317 --
318 --
319 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
320 --
321 IF l_debug_on IS NULL
322 THEN
323 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
324 END IF;
325 --
326 IF l_debug_on THEN
327 WSH_DEBUG_SV.push(l_module_name);
328 --
329 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
330 END IF;
331 --
332 OPEN trip_info;
333 FETCH trip_info INTO l_status_code, l_planned_flag,l_shipments_type_flag;
334
335 IF (trip_info%NOTFOUND) THEN
336 CLOSE trip_info;
337 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NOT_FOUND');
338 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
339 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
340 --
341 -- Debug Statements
342 --
343 IF l_debug_on THEN
344 WSH_DEBUG_SV.pop(l_module_name);
345 END IF;
346 --
347 RETURN;
348 END IF;
349
350 CLOSE trip_info;
351
352 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
353
354 --
355 -- Debug Statements
356 --
357 IF l_debug_on THEN
358 WSH_DEBUG_SV.pop(l_module_name);
359 END IF;
360 --
361 EXCEPTION
362 WHEN others THEN
363 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.CHECK_UNPLAN');
364 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
365
366 --
367 -- Debug Statements
368 --
369 IF l_debug_on THEN
370 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
371 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
372 END IF;
373 --
374 END Check_Unplan;
375
376 PROCEDURE Dropoff_Del_Intransit ( p_trip_id IN NUMBER,
377 p_stop_id IN NUMBER,
378 x_return_status OUT NOCOPY VARCHAR2,
379 --tkt
380 p_caller IN VARCHAR2 DEFAULT 'WSH') IS
381
382 CURSOR prev_stop_info IS
383 SELECT 1 from dual
384 WHERE exists ( select 1
385 FROM wsh_trip_stops ds,
386 wsh_delivery_legs dg,
387 wsh_new_deliveries dl
388 WHERE ds.trip_id = p_trip_id
389 AND dg.drop_off_stop_id = p_stop_id
390 AND dg.delivery_id = dl.delivery_id
391 AND dl.status_code IN ('CO','OP','PA'));
392
393 l_stop_id NUMBER;
394
395 --
396 l_debug_on BOOLEAN;
397 --
398 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DROPOFF_DEL_INTRANSIT';
399 --
400 BEGIN
401
402 --
403 -- Debug Statements
404 --
405 --
406 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
407 --
408 IF l_debug_on IS NULL
409 THEN
410 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
411 END IF;
412 --
413 IF l_debug_on THEN
414 WSH_DEBUG_SV.push(l_module_name);
415 --
416 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
417 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
418 END IF;
419 --
420 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
421
422 OPEN prev_stop_info;
423 FETCH prev_stop_info INTO l_stop_id;
424 IF (prev_stop_info%FOUND) THEN
425 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_PREV_NOT_CLOSED');
426 --
427 -- Debug Statements
428 --
429 IF l_debug_on THEN
430 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
431 END IF;
432 --
433 FND_MESSAGE.SET_TOKEN('STOP_NAME', wsh_trip_stops_pvt.get_name(p_stop_id, p_caller));
434 close prev_stop_info;
435 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
436 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
437 --
438 -- Debug Statements
439 --
440 IF l_debug_on THEN
441 WSH_DEBUG_SV.pop(l_module_name);
442 END IF;
443 --
444 RETURN;
445 END IF;
446 close prev_stop_info;
447
448 --
449 -- Debug Statements
450 --
451 IF l_debug_on THEN
452 WSH_DEBUG_SV.pop(l_module_name);
453 END IF;
454 --
455 EXCEPTION
456 WHEN others THEN
457 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.Dropoff_Del_Intransit');
458 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
459 IF prev_stop_info%ISOPEN THEN
460 CLOSE prev_stop_info;
461 END IF;
462
463 --
464 -- Debug Statements
465 --
466 IF l_debug_on THEN
467 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
468 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
469 END IF;
470 --
471 END Dropoff_Del_Intransit;
472
473 -- Private procedure called to check if previous stop is closed
474
475 PROCEDURE Check_Prev_Stop_Close ( p_trip_id IN NUMBER,
476 p_stop_id IN NUMBER, --wr
477 p_curr_stop_seq IN NUMBER,
478 p_curr_stop_type IN VARCHAR2,
479 x_linked_stop_id OUT NOCOPY NUMBER, --wr
480 x_return_status OUT NOCOPY VARCHAR2) IS
481
482 --wr
483 -- Check for linked internal or physical stop
484 -- so that it can be subsequently validated and updated.
485 -- 1. if the input stop is dummy, get its linked physical stop.
486 -- (If physical stop is Arrived, the dummy stop must be Arrived or Closed,
487 -- which will be caught earlier in the group API flow.)
488 -- 2. if the input stop is physical, get the dummy stop only if it is open.
489 -- if there is no link, do not select any record.
490 CURSOR c_linked_stop(p_trip_id IN NUMBER,
491 p_stop_id IN NUMBER) IS
492 SELECT physical_stop_id linked_stop_id,
493 1 link_type
494 FROM wsh_trip_stops
495 WHERE stop_id = p_stop_id
496 AND physical_stop_id IS NOT NULL
497 UNION
498 SELECT stop_id linked_stop_id,
499 2 link_type
500 FROM wsh_trip_stops
501 WHERE trip_id = p_trip_id
502 AND physical_stop_id = p_stop_id
503 AND status_code = 'OP';
504
505
506 l_linked_stop_rec c_linked_stop%ROWTYPE;
507 l_primary_stop_id NUMBER;
508 l_secondary_stop_id NUMBER;
509
510 -- Pack J IB: KVENKATE
511 -- Select shipments type flag , sequence number
512 -- ignore the linked internal stop.
513 CURSOR prev_stop_info(p_stop_id NUMBER) IS
514 SELECT stop_id,
515 status_code,
516 stop_sequence_number,
517 nvl(shipments_type_flag, 'O') shipments_type_flag
518 FROM wsh_trip_stops
519 WHERE trip_id = p_trip_id AND
520 stop_sequence_number < p_curr_stop_seq AND
521 NVL(physical_stop_id,-1) <> p_stop_id AND --wr
522 status_code IN ('OP', 'AR') --wr
523 ORDER BY stop_sequence_number desc;
524
525 l_stop_date DATE;
526 l_stop_id NUMBER;
527 l_stop_status VARCHAR2(2);
528 l_stop_seq_num NUMBER;
529 l_shipments_type_flag VARCHAR2(30);
530 l_num_warning NUMBER := 0;
531 --
532 l_debug_on BOOLEAN;
533 --
534 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_PREV_STOP_CLOSE';
535 --
536 BEGIN
537
538 --
539 -- Debug Statements
540 --
541 --
542 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
543 --
544 IF l_debug_on IS NULL
545 THEN
546 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
547 END IF;
548 --
549 IF l_debug_on THEN
550 WSH_DEBUG_SV.push(l_module_name);
551 --
552 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
553 -- Pack J IB: KVENKATE
554 WSH_DEBUG_SV.log(l_module_name, 'p_curr_stop_seq', p_curr_stop_seq);
555 WSH_DEBUG_SV.log(l_module_name, 'p_curr_stop_type', p_curr_stop_type);
556 END IF;
557 --
558 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
559
560 --wr
561 OPEN c_linked_stop(p_trip_id, p_stop_id);
562 FETCH c_linked_stop into l_linked_stop_rec;
563 IF c_linked_stop%FOUND THEN
564 x_linked_stop_id := l_linked_stop_rec.linked_stop_id;
565 IF l_linked_stop_rec.link_type = 1 THEN
566 l_primary_stop_id := l_linked_stop_rec.linked_stop_id;
567 l_secondary_stop_id := p_stop_id;
568 ELSE
569 l_primary_stop_id := p_stop_id;
570 l_secondary_stop_id := l_linked_stop_rec.linked_stop_id;
571 END IF;
572 ELSE
573 l_primary_stop_id := p_stop_id;
574 l_secondary_stop_id := NULL;
575 END IF;
576 CLOSE c_linked_stop;
577
578 IF l_debug_on THEN
579 WSH_DEBUG_SV.log(l_module_name,'l_linked_stop_rec.link_type',l_linked_stop_rec.link_type);
580 WSH_DEBUG_SV.log(l_module_name,'l_linked_stop_rec.linked_stop_id',l_linked_stop_rec.linked_stop_id);
581 WSH_DEBUG_SV.log(l_module_name,'l_primary_stop_id',l_primary_stop_id);
582 WSH_DEBUG_SV.log(l_module_name,'l_primary_stop_id',l_secondary_stop_id);
583 WSH_DEBUG_SV.log(l_module_name,'x_linked_stop_id',x_linked_stop_id);
584 END IF;
585
586
587 -- Pack J IB: KVENKATE
588 -- Need to look at all previous stops to see if any is closed and is not inbound
589 FOR prev_stop_rec IN prev_stop_info(l_primary_stop_id) LOOP
590 --{
591
592 IF l_debug_on THEN
593 wsh_debug_sv.log(l_module_name, 'stop_seq_num', prev_stop_rec.stop_sequence_number);
594 wsh_debug_sv.log(l_module_name, 'shipments_type_flag', prev_stop_rec.shipments_type_flag);
595 END IF;
596
597
598 IF (prev_stop_rec.status_code <> 'CL') THEN
599 --{
600 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_PREV_NOT_CLOSED');
601 --
602 -- Debug Statements
603 --
604 IF l_debug_on THEN
605 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
606 END IF;
607 --
608 FND_MESSAGE.SET_TOKEN('STOP_NAME', wsh_trip_stops_pvt.get_name(prev_stop_rec.stop_id));
609 -- Pack J IB: KVENKATE
610 -- Set the return status based on checks of shipment_type_flag
611 -- of current stop and the previous stop
612
613 IF p_curr_stop_type IN ('O', 'M') AND prev_stop_rec.shipments_type_flag = 'I' THEN
614 l_num_warning := l_num_warning + 1;
615 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING);
616 ELSE
617 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
618 RAISE FND_API.G_EXC_ERROR;
619 END IF;
620
621 --}
622 END IF;
623 --}
624 END LOOP;
625
626 -- Pack J IB: KVENKATE
627 IF l_num_warning > 0 THEN
628 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
629 END IF;
630
631 --
632 -- Debug Statements
633 --
634 IF l_debug_on THEN
635 WSH_DEBUG_SV.pop(l_module_name);
636 END IF;
637 --
638 EXCEPTION
639 -- Pack J IB: KVENKATE
640 WHEN FND_API.G_EXC_ERROR THEN
641 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
642 IF l_debug_on THEN
643 wsh_debug_sv.logmsg(l_module_name, 'FND_API.G_EXC_ERROR exception has occured.', wsh_debug_sv.c_excep_level);
644 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
645 END IF;
646 --
647
648 WHEN others THEN
649 IF c_linked_stop%ISOPEN THEN
650 CLOSE c_linked_stop;
651 END IF;
652 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.Check_Prev_Stop_Close');
653 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
654 IF prev_stop_info%ISOPEN THEN
655 CLOSE prev_stop_info;
656 END IF;
657
658 --
659 -- Debug Statements
660 --
661 IF l_debug_on THEN
662 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
663 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
664 END IF;
665 --
666 END Check_Prev_Stop_Close;
667
668
669 -----------------------------------------------------------------------------
670 --
671 -- Procedure: Check_Trip_Close
672 -- Parameters: trip_id, x_return_status
673 -- Description: Checks for Trip Close action pre-requisites which are
674 -- - Trip status is OPEN or IN-TRANSIT
675 -- - If trip status is IN-TRANSIT then last stop status is ARRIVED or CLOSED [warning]
676 --
677 -----------------------------------------------------------------------------
678
679 PROCEDURE Check_Trip_Close ( p_trip_id IN NUMBER,
680 x_return_status OUT NOCOPY VARCHAR2) IS
681
682 CURSOR open_stop(v_trip_id NUMBER) IS
683 SELECT 'OPEN STOP'
684 FROM wsh_trip_stops st
685 WHERE st.trip_id = v_trip_id
686 AND st.status_code <> 'CL';
687
688
689 l_max_date DATE;
690 l_stop_status VARCHAR2(2);
691 l_trip_status VARCHAR2(2);
692 l_dummy VARCHAR2(20);
693
694 --
695 l_debug_on BOOLEAN;
696 --
697 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_TRIP_CLOSE';
698 --
699 BEGIN
700
701 --
702 -- Debug Statements
703 --
704 --
705 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
706 --
707 IF l_debug_on IS NULL
708 THEN
709 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
710 END IF;
711 --
712 IF l_debug_on THEN
713 WSH_DEBUG_SV.push(l_module_name);
714 --
715 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
716 END IF;
717 --
718 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
719
720 OPEN open_stop(p_trip_id);
721 FETCH open_stop INTO l_dummy;
722
723 IF (open_stop%FOUND) THEN
724 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
725 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
726 END IF;
727
728 CLOSE open_stop;
729
730 --
731 -- Debug Statements
732 --
733 IF l_debug_on THEN
734 WSH_DEBUG_SV.pop(l_module_name);
735 END IF;
736 --
737 EXCEPTION
738 WHEN others THEN
739 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.CHECK_TRIP_CLOSE');
740 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
741 IF open_stop%ISOPEN THEN
742 CLOSE open_stop;
743 END IF;
744
745 --
746 -- Debug Statements
747 --
748 IF l_debug_on THEN
749 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
750 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
751 END IF;
752 --
753 END Check_Trip_Close;
754
755
756 -----------------------------------------------------------------------------
757 --
758 -- Procedure: Check_Trip_Delete
759 -- Parameters: trip_id, x_return_status
760 -- Description: Checks for Trip Delete action pre-requisites which are
761 -- - Trip status is OPEN
762 -- - Trip is not planned [warning]
763 -- - No deliveries are assigned to trip [warning]
764 -- - No freight costs are attached to trip [warning]
765 --
766 -----------------------------------------------------------------------------
767
768 PROCEDURE Check_Trip_Delete ( p_trip_id IN NUMBER,
769 x_return_status OUT NOCOPY VARCHAR2,
770 --tkt
771 p_caller IN VARCHAR2) IS
772
773 CURSOR trip_info IS
774 SELECT status_code,
775 planned_flag
776 FROM wsh_trips
777 WHERE trip_id = p_trip_id;
778
779 CURSOR stops_info IS
780 SELECT stop_id
781 FROM wsh_trip_stops
782 WHERE trip_id = p_trip_id;
783
784 CURSOR freight_costs_exist IS
785 SELECT freight_cost_id
786 FROM wsh_freight_costs
787 WHERE trip_id = p_trip_id
788 FOR UPDATE NOWAIT;
789
790 l_status_code VARCHAR2(2);
791 l_planned_flag VARCHAR2(1);
792 l_return_status VARCHAR2(1);
793
794 l_freight_costs_flag VARCHAR2(1) := 'N';
795
796 --
797 l_debug_on BOOLEAN;
798 --
799 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_TRIP_DELETE';
800 --
801 BEGIN
802
803 --
804 -- Debug Statements
805 --
806 --
807 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
808 --
809 IF l_debug_on IS NULL
810 THEN
811 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
812 END IF;
813 --
814 IF l_debug_on THEN
815 WSH_DEBUG_SV.push(l_module_name);
816 --
817 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
818 END IF;
819 --
820 OPEN trip_info;
821 FETCH trip_info INTO l_status_code, l_planned_flag;
822
823 IF (trip_info%NOTFOUND) THEN
824 CLOSE trip_info;
825 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NOT_FOUND');
826 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
827 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
828 --
829 -- Debug Statements
830 --
831 IF l_debug_on THEN
832 WSH_DEBUG_SV.pop(l_module_name);
833 END IF;
834 --
835 RETURN;
836 END IF;
837
838 CLOSE trip_info;
839
840 IF (l_status_code <> 'OP') THEN
841 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_INVALID_STATUS');
842 FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_id));
843 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
844 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
845 --
846 -- Debug Statements
847 --
848 IF l_debug_on THEN
849 WSH_DEBUG_SV.pop(l_module_name);
850 END IF;
851 --
852 RETURN;
853 END IF;
854
855 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
856
857 IF (l_planned_flag = 'Y') THEN
858 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_DELETE_PLAN_TRIP');
859 --
860 -- Debug Statements
861 --
862 IF l_debug_on THEN
863 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
864 END IF;
865 --
866 FND_MESSAGE.SET_TOKEN('TRIP_NAME', wsh_trips_pvt.get_name(p_trip_id));
867 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
868 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
869 ELSIF (l_planned_flag='F') THEN
870 FND_MESSAGE.SET_NAME('WSH','WSH_DELETE_FIRM_TRIP');
871 FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_id));
872 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
873 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
874 IF l_debug_on THEN
875 WSH_DEBUG_SV.pop(l_module_name);
876 END IF;
877 RETURN;
878 END IF;
879
880 -- Checks if deliveries exist for any stop and locks the stops, legs for each delivery
881 -- NOTE: p_trip_flag is set to 'Y' to prevent duplication of delivery warnings
882
883 FOR st IN stops_info LOOP
884
885 check_stop_delete( st.stop_id, l_return_status, 'Y', p_caller);
886
887 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
888
889 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
890 --
891 -- Debug Statements
892 --
893 IF l_debug_on THEN
894 WSH_DEBUG_SV.pop(l_module_name);
895 END IF;
896 --
897 RETURN;
898 ELSE
899 x_return_status := l_return_status;
900 END IF;
901
902 END IF;
903
904 END LOOP;
905
906 -- Checks if freight costs exist for the trip and locks them
907
908 FOR fc IN freight_costs_exist LOOP
909 l_freight_costs_flag := 'Y';
910 END LOOP;
911
912 IF (l_freight_costs_flag = 'Y') THEN
913 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_DELETE_WITH_FC');
914 --
915 -- Debug Statements
916 --
917 IF l_debug_on THEN
918 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
919 END IF;
920 --
921 FND_MESSAGE.SET_TOKEN('TRIP_NAME', wsh_trips_pvt.get_name(p_trip_id));
922 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
923 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
924 END IF;
925
926 --
927 -- Debug Statements
928 --
929 IF l_debug_on THEN
930 WSH_DEBUG_SV.pop(l_module_name);
931 END IF;
932 --
933 EXCEPTION
934 WHEN others THEN
935 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.CHECK_TRIP_DELETE');
936 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
937
938 --
939 -- Debug Statements
940 --
941 IF l_debug_on THEN
942 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
943 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
944 END IF;
945 --
946 END Check_Trip_Delete;
947
948
949 -----------------------------------------------------------------------------
950 --
951 -- Procedure: Check_Change_Carrier
952 -- Parameters: trip_id, x_return_status
953 -- Description: Checks for Change Carrier action pre-requisites which are
954 -- - Trip status is OPEN
955 -- - If GROUP_BY_CARRIER_FLAG is set for any delivery on trip then Ship Method for deliveries and delivery details on this trip is not specified
956 --
957 -----------------------------------------------------------------------------
958
959 PROCEDURE Check_Change_Carrier ( p_trip_id IN NUMBER,
960 x_return_status OUT NOCOPY VARCHAR2) IS
961
962 CURSOR trip_info IS
963 SELECT status_code,
964 planned_flag
965 FROM wsh_trips
966 WHERE trip_id = p_trip_id;
967
968 CURSOR group_by_carrier_set IS
969 SELECT count(*)
970 FROM wsh_trip_stops t,
971 wsh_delivery_legs dg,
972 wsh_new_deliveries dl,
973 wsh_shipping_parameters wsp
974 WHERE t.stop_id = p_trip_id AND
975 dg.pick_up_stop_id = t.stop_id AND
976 dl.delivery_id = dg.delivery_id AND
977 dl.organization_id = wsp.organization_id AND
978 dl.ship_method_code IS NOT NULL AND
979 wsp.group_by_ship_method_flag = 'Y';
980
981 -- NEED TO INCLUDE RULE FOR FREIGHT COSTS
982
983 l_status_code VARCHAR2(2);
984 l_planned_flag VARCHAR2(1);
985 l_cnt NUMBER := NULL;
986
987 --
988 l_debug_on BOOLEAN;
989 --
990 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_CHANGE_CARRIER';
991 --
992 BEGIN
993
994 --
995 -- Debug Statements
996 --
997 --
998 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
999 --
1000 IF l_debug_on IS NULL
1001 THEN
1002 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1003 END IF;
1004 --
1005 IF l_debug_on THEN
1006 WSH_DEBUG_SV.push(l_module_name);
1007 --
1008 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
1009 END IF;
1010 --
1011 OPEN trip_info;
1012 FETCH trip_info INTO l_status_code, l_planned_flag;
1013
1014 IF (trip_info%NOTFOUND) THEN
1015 CLOSE trip_info;
1016 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NOT_FOUND');
1017 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1018 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1019 --
1020 -- Debug Statements
1021 --
1022 IF l_debug_on THEN
1023 WSH_DEBUG_SV.pop(l_module_name);
1024 END IF;
1025 --
1026 RETURN;
1027 END IF;
1028
1029 CLOSE trip_info;
1030
1031 OPEN group_by_carrier_set;
1032 FETCH group_by_carrier_set INTO l_cnt;
1033 CLOSE group_by_carrier_set;
1034
1035 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1036
1037 IF (l_cnt IS NOT NULL) THEN
1038 FND_MESSAGE.SET_NAME('WSH','Group-by-carrier-set');
1039 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1040 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1041 END IF;
1042
1043 --
1044 -- Debug Statements
1045 --
1046 IF l_debug_on THEN
1047 WSH_DEBUG_SV.pop(l_module_name);
1048 END IF;
1049 --
1050 EXCEPTION
1051 WHEN others THEN
1052 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.CHECK_CHANGE_CARRIER');
1053 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1054
1055 --
1056 -- Debug Statements
1057 --
1058 IF l_debug_on THEN
1059 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1060 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1061 END IF;
1062 --
1063 END Check_Change_Carrier;
1064
1065
1066
1067
1068 -----------------------------------------------------------------------------
1069 --
1070 -- Procedure: Check_Stop_Arrive
1071 -- Parameters: p_stop_id input stop to validate
1072 -- x_linked_stop_id populated only if linked physical or dummy stop
1073 -- is open.
1074 -- x_return_status
1075 -- Description: Checks for Arrive action pre-requisites which are
1076 -- (These prerequisites need to be updated) --wr
1077 -- - Actual Arrival Date is specified ( if date is not specified
1078 -- then default the current date in Actual Arrival Date)
1079 -- - Not the first stop on a trip
1080 -- - Previous stop on this trip is CLOSED [warning]
1081 -- NOTE: this warning allows the user to Close All Previous Stops, Ignore or Cancel.
1082 --
1083 -----------------------------------------------------------------------------
1084
1085 PROCEDURE Check_Stop_Arrive ( p_stop_id IN NUMBER,
1086 x_linked_stop_id OUT NOCOPY NUMBER, --wr
1087 x_return_status OUT NOCOPY VARCHAR2) IS
1088
1089 -- Pack J IB: KVENKATE
1090 -- Select shipment type flag, sequence number
1091 CURSOR stop_info IS
1092 SELECT stop_sequence_number,
1093 status_code,
1094 trip_id,
1095 nvl(shipments_type_flag, 'O') shipments_type_flag
1096 FROM wsh_trip_stops
1097 WHERE stop_id = p_stop_id;
1098
1099 l_stop_date DATE;
1100 l_status_code VARCHAR2(2);
1101 l_first_date DATE;
1102 l_trip_id NUMBER;
1103
1104 -- Pack J IB: KVENKATE
1105 l_stop_seq_num NUMBER;
1106 l_shipments_type_flag VARCHAR2(30);
1107 -- Pack J IB: KVENKATE
1108
1109 --
1110 l_debug_on BOOLEAN;
1111 --
1112 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_STOP_ARRIVE';
1113 --
1114 BEGIN
1115
1116 --
1117 -- Debug Statements
1118 --
1119 --
1120 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1121 --
1122 IF l_debug_on IS NULL
1123 THEN
1124 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1125 END IF;
1126 --
1127 IF l_debug_on THEN
1128 WSH_DEBUG_SV.push(l_module_name);
1129 --
1130 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
1131 END IF;
1132 --
1133 OPEN stop_info;
1134 -- PACK J: KVENKATE
1135 FETCH stop_info INTO l_stop_seq_num, l_status_code, l_trip_id, l_shipments_type_flag;
1136
1137 IF (stop_info%NOTFOUND) THEN
1138 CLOSE stop_info;
1139 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
1140 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1141 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1142 --
1143 -- Debug Statements
1144 --
1145 IF l_debug_on THEN
1146 WSH_DEBUG_SV.pop(l_module_name);
1147 END IF;
1148 --
1149 RETURN;
1150 END IF;
1151
1152 CLOSE stop_info;
1153
1154 IF (l_status_code = 'CL') THEN
1155 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_INVALID_STATUS');
1156 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1157 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1158 --
1159 -- Debug Statements
1160 --
1161 IF l_debug_on THEN
1162 WSH_DEBUG_SV.pop(l_module_name);
1163 END IF;
1164 --
1165 RETURN;
1166 END IF;
1167
1168 -- Pack J IB: KVENKATE
1169 Check_Prev_Stop_Close(
1170 p_trip_id => l_trip_id,
1171 p_stop_id => p_stop_id, --wr
1172 p_curr_stop_seq => l_stop_seq_num,
1173 p_curr_stop_type => l_shipments_type_flag,
1174 x_linked_stop_id => x_linked_stop_id,
1175 x_return_status => x_return_status);
1176
1177 --
1178 -- Debug Statements
1179 --
1180 IF l_debug_on THEN
1181 WSH_DEBUG_SV.pop(l_module_name);
1182 END IF;
1183 --
1184 EXCEPTION
1185 WHEN others THEN
1186 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.CHECK_STOP_ARRIVE');
1187 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1188
1189 --
1190 -- Debug Statements
1191 --
1192 IF l_debug_on THEN
1193 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1194 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1195 END IF;
1196 --
1197 END Check_Stop_Arrive;
1198
1199
1200
1201
1202 -----------------------------------------------------------------------------
1203 --
1204 -- Procedure: Check_Stop_Close
1205 -- Parameters: stop_id, x_return_status
1206 -- Description: Checks for Stop Close action pre-requisites which are
1207 -- (These prerequisites need to be updated) --wr
1208 -- - Pick up deliveries are confirmed, in transit or closed. (bug 1550824)
1209 -- - Actual Arrival Date and Actual Departure Date are specified
1210 -- (if first or last trip stop on a trip then only one of the
1211 -- two dates need to be specified)
1212 -- - Previous stop on this trip is CLOSED (except for the first trip stop) [warning]
1213 --
1214 -----------------------------------------------------------------------------
1215
1216 PROCEDURE Check_Stop_Close ( p_stop_id IN NUMBER,
1217 x_return_status OUT NOCOPY VARCHAR2,
1218 --tkt
1219 p_caller IN VARCHAR2) IS
1220
1221 CURSOR stop_info IS
1222 SELECT planned_departure_date,
1223 status_code,
1224 trip_id
1225 FROM wsh_trip_stops
1226 WHERE stop_id = p_stop_id;
1227
1228 -- bug 1550824: find delivery not confirmed, in transit or closed.
1229 CURSOR deliveries_still_open(p_stop_id NUMBER) IS
1230 SELECT wnd.delivery_id
1231 FROM wsh_new_deliveries wnd,
1232 wsh_delivery_legs wdl
1233 WHERE wdl.pick_up_stop_id = p_stop_id
1234 AND wnd.delivery_id = wdl.delivery_id
1235 AND wnd.status_code IN ('OP', 'PA')
1236 AND rownum = 1;
1237
1238 l_stop_date DATE;
1239 l_status_code VARCHAR2(2);
1240 l_trip_id NUMBER;
1241 l_delivery_id NUMBER; -- bug 1550824;
1242
1243 --
1244 l_debug_on BOOLEAN;
1245 --
1246 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_STOP_CLOSE';
1247 --
1248 BEGIN
1249 --
1250 -- Debug Statements
1251 --
1252 --
1253 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1254 --
1255 IF l_debug_on IS NULL
1256 THEN
1257 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1258 END IF;
1259 --
1260 IF l_debug_on THEN
1261 WSH_DEBUG_SV.push(l_module_name);
1262 --
1263 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
1264 END IF;
1265 --
1266 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1267
1268 OPEN stop_info;
1269 FETCH stop_info INTO l_stop_date, l_status_code, l_trip_id;
1270
1271 IF (stop_info%NOTFOUND) THEN
1272 CLOSE stop_info;
1273 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
1274 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1275 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1276 --
1277 -- Debug Statements
1278 --
1279 IF l_debug_on THEN
1280 WSH_DEBUG_SV.pop(l_module_name);
1281 END IF;
1282 --
1283 RETURN;
1284 END IF;
1285
1286 CLOSE stop_info;
1287
1288 -- bug 1550824: check for deliveries not ready to pick up
1289 OPEN deliveries_still_open(p_stop_id);
1290 FETCH deliveries_still_open INTO l_delivery_id;
1291 IF deliveries_still_open%NOTFOUND THEN
1292 l_delivery_id := NULL;
1293 END IF;
1294 CLOSE deliveries_still_open;
1295
1296 IF l_delivery_id IS NOT NULL THEN
1297 FND_MESSAGE.SET_NAME('WSH', 'WSH_STOP_CLOSE_OP_PA_ERROR');
1298 --
1299 -- Debug Statements
1300 --
1301 IF l_debug_on THEN
1302 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1303 END IF;
1304 --
1305 FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(p_stop_id, p_caller));
1306 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1307 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1308 --
1309 -- Debug Statements
1310 --
1311 IF l_debug_on THEN
1312 WSH_DEBUG_SV.pop(l_module_name);
1313 END IF;
1314 --
1315 RETURN;
1316 END IF;
1317
1318 Dropoff_Del_Intransit(l_trip_id, p_stop_id, x_return_status, p_caller);
1319
1320 --
1321 -- Debug Statements
1322 --
1323 IF l_debug_on THEN
1324 WSH_DEBUG_SV.pop(l_module_name);
1325 END IF;
1326 --
1327 EXCEPTION
1328 WHEN others THEN
1329 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.CHECK_STOP_CLOSE');
1330 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1331
1332 --
1333 -- Debug Statements
1334 --
1335 IF l_debug_on THEN
1336 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1337 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1338 END IF;
1339 --
1340 END Check_Stop_Close;
1341
1342
1343
1344 -----------------------------------------------------------------------------
1345 --
1346 -- Procedure: Check_Assign_Trip
1347 -- Parameters: stop_id, x_return_status
1348 -- Description: Checks for Assign Trip action pre-requisites which are
1349 -- - Trip status is not CLOSED
1350 -- - Trip is not planned
1351 -- - If trip has Vehicle information then vehicle is not over/under filled at this and subsequent stops [warning]
1352 -- NOTE: The above three rules apply to both trip to be unassigned from and trip to be assigned to
1353 -- - Stop with the same location does not exist for the trip to be assigned to [warning]
1354 --
1355 -----------------------------------------------------------------------------
1356
1357 PROCEDURE Check_Assign_Trip ( p_stop_id IN NUMBER,
1358 p_trip_id IN NUMBER,
1359 x_return_status OUT NOCOPY VARCHAR2) IS
1360 --
1361 l_debug_on BOOLEAN;
1362 --
1363 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_ASSIGN_TRIP';
1364 --
1365 BEGIN
1366 --
1367 -- Debug Statements
1368 --
1369 --
1370 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1371 --
1372 IF l_debug_on IS NULL
1373 THEN
1374 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1375 END IF;
1376 --
1377 IF l_debug_on THEN
1378 WSH_DEBUG_SV.push(l_module_name);
1379 --
1380 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
1381 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
1382 END IF;
1383 --
1384 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1385 --
1386 -- Debug Statements
1387 --
1388 IF l_debug_on THEN
1389 WSH_DEBUG_SV.pop(l_module_name);
1390 END IF;
1391 --
1392 END Check_Assign_Trip;
1393
1394
1395
1396
1397 -----------------------------------------------------------------------------
1398 --
1399 -- Procedure: Check_Stop_Delete
1400 -- Parameters: stop_id, x_return_status
1401 -- Description: Checks for Delete Stop action pre-requisites which are
1402 -- - Stop status is OPEN
1403 -- - Trip status is not CLOSED
1404 -- - Trip is not planned
1405 -- - No deliveries are assigned to this stop [warning]
1406 --
1407 -----------------------------------------------------------------------------
1408
1409 PROCEDURE Check_Stop_Delete ( p_stop_id IN NUMBER,
1410 x_return_status OUT NOCOPY VARCHAR2,
1411 p_trip_flag IN VARCHAR2 DEFAULT 'N',
1412 --tkt
1413 p_caller IN VARCHAR2) IS
1414 CURSOR stop_info IS
1415 SELECT t.status_code,
1416 t.planned_flag,
1417 st.status_code,
1418 t.trip_id
1419 FROM wsh_trips t,
1420 wsh_trip_stops st
1421 WHERE st.stop_id = p_stop_id AND
1422 st.trip_id = t.trip_id;
1423
1424 CURSOR pickup_deliveries IS
1425 SELECT dg.delivery_id
1426 FROM wsh_trip_stops t,
1427 wsh_delivery_legs dg
1428 WHERE t.stop_id = p_stop_id AND
1429 dg.pick_up_stop_id = t.stop_id
1430 FOR UPDATE NOWAIT;
1431
1432 CURSOR dropoff_deliveries IS
1433 SELECT dg.delivery_id
1434 FROM wsh_trip_stops t,
1435 wsh_delivery_legs dg
1436 WHERE t.stop_id = p_stop_id AND
1437 dg.drop_off_stop_id = t.stop_id
1438 FOR UPDATE NOWAIT;
1439
1440 CURSOR freight_costs_exist IS
1441 SELECT freight_cost_id
1442 FROM wsh_freight_costs
1443 WHERE stop_id = p_stop_id
1444 FOR UPDATE NOWAIT;
1445
1446 l_trip_status_code VARCHAR2(2);
1447 l_planned_flag VARCHAR2(1);
1448 l_stop_status_code VARCHAR2(2);
1449 l_trip_id NUMBER;
1450
1451 l_del_flag VARCHAR2(1) := 'N';
1452 l_freight_costs_flag VARCHAR2(1) := 'N';
1453
1454 --
1455 l_debug_on BOOLEAN;
1456 --
1457 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_STOP_DELETE';
1458 --
1459 BEGIN
1460
1461 --
1462 -- Debug Statements
1463 --
1464 --
1465 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1466 --
1467 IF l_debug_on IS NULL
1468 THEN
1469 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1470 END IF;
1471 --
1472 IF l_debug_on THEN
1473 WSH_DEBUG_SV.push(l_module_name);
1474 --
1475 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
1476 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_FLAG',P_TRIP_FLAG);
1477 END IF;
1478 --
1479 OPEN stop_info;
1480 FETCH stop_info INTO l_trip_status_code, l_planned_flag, l_stop_status_code, l_trip_id;
1481
1482 IF (stop_info%NOTFOUND) THEN
1483 CLOSE stop_info;
1484 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
1485 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1486 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1487 --
1488 -- Debug Statements
1489 --
1490 IF l_debug_on THEN
1491 WSH_DEBUG_SV.pop(l_module_name);
1492 END IF;
1493 --
1494 RETURN;
1495 END IF;
1496
1497 CLOSE stop_info;
1498
1499 IF (l_stop_status_code <> 'OP') THEN
1500 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_INVALID_STATUS');
1501 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1502 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1503 --
1504 -- Debug Statements
1505 --
1506 IF l_debug_on THEN
1507 WSH_DEBUG_SV.pop(l_module_name);
1508 END IF;
1509 --
1510 RETURN;
1511 END IF;
1512
1513 IF (l_trip_status_code = 'CL') THEN
1514 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_INVALID_STATUS');
1515 FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(l_trip_id));
1516 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1517 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1518 --
1519 -- Debug Statements
1520 --
1521 IF l_debug_on THEN
1522 WSH_DEBUG_SV.pop(l_module_name);
1523 END IF;
1524 --
1525 RETURN;
1526 END IF;
1527
1528 IF (l_planned_flag='Y') THEN
1529 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_INVALID_TRIP_PLAN');
1530 --
1531 -- Debug Statements
1532 --
1533 IF l_debug_on THEN
1534 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1535 END IF;
1536 --
1537 FND_MESSAGE.SET_TOKEN('TRIP_NAME', wsh_trips_pvt.get_name(l_trip_id));
1538 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1539 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1540 --
1541 -- Debug Statements
1542 --
1543 IF l_debug_on THEN
1544 WSH_DEBUG_SV.pop(l_module_name);
1545 END IF;
1546 --
1547 RETURN;
1548 ELSIF (l_planned_flag ='F') THEN
1549 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_INVALID_TRIP_FIRM');
1550 --
1551 -- Debug Statements
1552 --
1553 IF l_debug_on THEN
1554 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1555 END IF;
1556 --
1557 FND_MESSAGE.SET_TOKEN('TRIP_NAME', wsh_trips_pvt.get_name(l_trip_id));
1558 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1559 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1560 --
1561 -- Debug Statements
1562 --
1563 IF l_debug_on THEN
1564 WSH_DEBUG_SV.pop(l_module_name);
1565 END IF;
1566 --
1567 RETURN;
1568 END IF;
1569
1570 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1571
1572 FOR pd IN pickup_deliveries LOOP
1573 l_del_flag := 'Y';
1574 END LOOP;
1575
1576 IF (l_del_flag <> 'Y') AND (p_trip_flag = 'N') THEN
1577
1578 FOR dd IN dropoff_deliveries LOOP
1579 l_del_flag := 'Y';
1580 END LOOP;
1581
1582 END IF;
1583
1584 IF (l_del_flag = 'Y') THEN
1585 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_DELETE_WITH_DELS');
1586 --
1587 -- Debug Statements
1588 --
1589 IF l_debug_on THEN
1590 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1591 END IF;
1592 --
1593 FND_MESSAGE.SET_TOKEN('STOP_NAME', wsh_trip_stops_pvt.get_name(p_stop_id, p_caller));
1594 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1595 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1596 END IF;
1597
1598 -- Checks if freight costs exist for the stop and locks them
1599
1600 FOR fc IN freight_costs_exist LOOP
1601 l_freight_costs_flag := 'Y';
1602 END LOOP;
1603
1604 IF (l_freight_costs_flag = 'Y') THEN
1605 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_DELETE_WITH_FC');
1606 --
1607 -- Debug Statements
1608 --
1609 IF l_debug_on THEN
1610 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1611 END IF;
1612 --
1613 FND_MESSAGE.SET_TOKEN('STOP_NAME', wsh_trip_stops_pvt.get_name(p_stop_id,p_caller));
1614 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1615 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1616 END IF;
1617
1618 --
1619 -- Debug Statements
1620 --
1621 IF l_debug_on THEN
1622 WSH_DEBUG_SV.pop(l_module_name);
1623 END IF;
1624 --
1625 EXCEPTION
1626 WHEN others THEN
1627 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.CHECK_STOP_DELETE');
1628 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1629
1630 --
1631 -- Debug Statements
1632 --
1633 IF l_debug_on THEN
1634 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1635 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1636 END IF;
1637 --
1638 END Check_Stop_Delete;
1639
1640
1641 -----------------------------------------------------------------------------
1642 --
1643 -- Procedure: Get_Disabled_List
1644 -- Parameters: stop_id, x_return_status, p_trip_flag
1645 -- Description: Get the disabled columns/fields in a trip
1646 --
1647 -----------------------------------------------------------------------------
1648
1649 PROCEDURE Get_Disabled_List
1650 (p_trip_id IN NUMBER,
1651 p_list_type IN VARCHAR2,
1652 x_return_status OUT NOCOPY VARCHAR2,
1653 x_disabled_list OUT NOCOPY WSH_UTIL_CORE.COLUMN_TAB_TYPE,
1654 x_msg_count OUT NOCOPY NUMBER,
1655 x_msg_data OUT NOCOPY VARCHAR2,
1656 p_caller IN VARCHAR2 -- DEFAULT NULL, --3509004:public api changes
1657 ) IS
1658
1659 CURSOR get_trip_status(p_trip_id NUMBER) IS
1660 SELECT status_code, planned_flag, SHIPMENTS_TYPE_FLAG, --3509004:public api changes
1661 vehicle_organization_id, -- Bug 3599626
1662 lane_id,
1663 tp_plan_name -- OTM R12, glog proj
1664 FROM wsh_trips
1665 WHERE trip_id = p_trip_id;
1666
1667 i NUMBER := 0;
1668 l_tp_plan_name WSH_TRIPS.TP_PLAN_NAME%TYPE; -- OTM R12, glog proj
1669 l_gc3_is_installed VARCHAR(1); --OTM R12, glog proj
1670 l_status_code VARCHAR2(10) := NULL;
1671 l_planned_flag VARCHAR2(10) := NULL;
1672 l_vehicle_org_id NUMBER; -- Bug 3599626
1673 l_msg_summary VARCHAR2(2000) := NULL;
1674 l_msg_details VARCHAR2(4000) := NULL;
1675 l_shipments_type_flag VARCHAR(30) := NULL; --3509004:public api changes
1676 l_lane_id NUMBER;
1677 e_all_disabled EXCEPTION ; --3509004:public api changes
1678
1679 WSH_DP_NO_ENTITY EXCEPTION;
1680 WSH_INV_LIST_TYPE EXCEPTION;
1681 --
1682 l_debug_on BOOLEAN;
1683 --
1684 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DISABLED_LIST';
1685 --
1686 BEGIN
1687 --
1688 -- Debug Statements
1689 --
1690 --
1691 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1692 --
1693 IF l_debug_on IS NULL
1694 THEN
1695 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1696 END IF;
1697 --
1698 IF l_debug_on THEN
1699 WSH_DEBUG_SV.push(l_module_name);
1700 --
1701 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
1702 WSH_DEBUG_SV.log(l_module_name,'P_LIST_TYPE',P_LIST_TYPE);
1703 END IF;
1704 --
1705 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1706 --OTM R12, glog proj, use Global Variable
1707 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
1708 IF l_gc3_is_installed IS NULL THEN-- call function
1709 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
1710 END IF;
1711
1712 -- clear the disabled list first
1713 x_disabled_list.delete;
1714
1715 -- open the trip
1716 OPEN get_trip_status(p_trip_id);
1717 FETCH get_trip_status
1718 INTO l_status_code,
1719 l_planned_flag,
1720 l_shipments_type_flag, --3509004:public api changes
1721 l_vehicle_org_id,
1722 l_lane_id,
1723 l_tp_plan_name; -- OTM R12, glog proj
1724
1725 IF get_trip_status%NOTFOUND then
1726 CLOSE get_trip_status;
1727 RAISE WSH_DP_NO_ENTITY;
1728 END IF;
1729 CLOSE get_trip_status;
1730
1731 IF (p_list_type <> 'FORM') THEN
1732 RAISE WSH_INV_LIST_TYPE;
1733 END IF;
1734
1735 IF ( l_status_code = 'CL') THEN--{
1736 i:=i+1; x_disabled_list(i) := 'FULL';
1737 i:=i+1; x_disabled_list(i) := 'DESC_FLEX';
1738 i:=i+1; x_disabled_list(i) := 'OPERATOR';
1739 ELSIF ( l_status_code = 'IT') THEN--} {
1740 i:=i+1; x_disabled_list(i) := 'FULL';
1741 i:=i+1; x_disabled_list(i) := 'DESC_FLEX';
1742 i:=i+1; x_disabled_list(i) := 'ROUTING_INSTRUCTIONS';
1743 i:=i+1; x_disabled_list(i) := 'OPERATOR';
1744 --Added for Bug 3309150
1745 IF NVL(p_caller,'''') LIKE 'FTE%' THEN --3509004:public api changes
1746 i:=i+1; x_disabled_list(i) := 'VEHICLE_ORGANIZATION_ID';
1747 ELSE
1748 i:=i+1; x_disabled_list(i) := 'VEHICLE_ORGANIZATION_CODE';
1749 END IF;
1750 -- BUG 3599626
1751 IF l_vehicle_org_id IS NOT NULL THEN
1752 IF NVL(p_caller,'''') LIKE 'FTE%' THEN
1753 i:=i+1; x_disabled_list(i) := 'VEHICLE_ITEM_ID';
1754 ELSE
1755 i:=i+1; x_disabled_list(i) := 'VEHICLE_ITEM_NAME';
1756 END IF;
1757 END IF;
1758 --
1759 ELSIF (l_status_code = 'OP') THEN--} {
1760 IF (l_planned_flag IN ('Y','F')) THEN--{
1761 i:=i+1; x_disabled_list(i) := 'FULL';
1762 i:=i+1; x_disabled_list(i) := 'DESC_FLEX';
1763 --bug 3257612 : load tender needs to update fields even if firm (any level)
1764 i:=i+1; x_disabled_list(i) := 'ROUTING_INSTRUCTIONS';
1765 i:=i+1; x_disabled_list(i) := 'LOAD_TENDER_STATUS';
1766 i:=i+1; x_disabled_list(i) := 'WF_NAME';
1767 i:=i+1; x_disabled_list(i) := 'WF_PROCESS_NAME';
1768 i:=i+1; x_disabled_list(i) := 'WF_ITEM_KEY';
1769 i:=i+1; x_disabled_list(i) := 'CARRIER_CONTACT_ID';
1770 i:=i+1; x_disabled_list(i) := 'SHIPPER_WAIT_TIME';
1771 i:=i+1; x_disabled_list(i) := 'WAIT_TIME_UOM';
1772 i:=i+1; x_disabled_list(i) := 'LOAD_TENDERED_TIME';
1773 i:=i+1; x_disabled_list(i) := 'CARRIER_RESPONSE';
1774 i:=i+1; x_disabled_list(i) := 'OPERATOR';
1775 -- Bug 3507047: Lane_id should be updatable on a firmed trip.
1776 i:=i+1; x_disabled_list(i) := 'LANE_ID';
1777 i:=i+1; x_disabled_list(i) := 'CARRIER_REFERENCE_NUMBER';
1778 i:=i+1; x_disabled_list(i) := 'CONSIGNEE_CARRIER_AC_NO';
1779 -- bug 4341253
1780 i:=i+1; x_disabled_list(i) := 'VEHICLE_NUMBER';
1781 END IF; --}-- end of l_planned_flag IN (Y/F)
1782
1783 --OTM R12, glog proj
1784 IF l_debug_on THEN
1785 WSH_DEBUG_SV.logmsg(l_module_name,'l_tp_plan_name :'||l_tp_plan_name);
1786 WSH_DEBUG_SV.logmsg(l_module_name,'l_gc3_is_installed :'||l_gc3_is_installed);
1787 WSH_DEBUG_SV.logmsg(l_module_name,'l_planned_flag :'||l_planned_flag);
1788 END IF;
1789 IF l_gc3_is_installed= 'Y' AND
1790 l_tp_plan_name IS NOT NULL THEN--{
1791 IF l_planned_flag = 'N' THEN
1792 i:=i+1; x_disabled_list(i) := 'FULL';
1793 i:=i+1; x_disabled_list(i) := 'DESC_FLEX';
1794 i:=i+1; x_disabled_list(i) := 'ROUTING_INSTRUCTIONS';
1795 i:=i+1; x_disabled_list(i) := 'OPERATOR';
1796 i:=i+1; x_disabled_list(i) := 'VEHICLE_NUMBER';
1797 END IF;
1798 IF l_debug_on THEN
1799 WSH_DEBUG_SV.logmsg(l_module_name, 'Caller :'||p_caller);
1800 WSH_DEBUG_SV.logmsg(l_module_name, 'Disabled List element 1:'||x_disabled_list(1));
1801 WSH_DEBUG_SV.logmsg(l_module_name, 'Before enabling others:');
1802 END IF;
1803 -- As planned_flag is not null field, x_disabled_list would
1804 -- have 1st element as FULL, so no need to check for <> FULL
1805 i:=i+1; x_disabled_list(i) := 'VEHICLE_NUM_PREFIX';
1806 i:=i+1; x_disabled_list(i) := 'VEHICLE_ITEM_NAME';
1807 i:=i+1; x_disabled_list(i) := 'VEHICLE_ORGANIZATION_CODE';
1808 i:=i+1; x_disabled_list(i) := 'SEAL_CODE';
1809 i:=i+1; x_disabled_list(i) := 'NAME';
1810 END IF;--} -- gc3 installed and tp_plan_name is NOT NULL
1811 --
1812 END IF; --} -- status_code = OP
1813
1814 --
1815 --
1816 -- J-IB-NPARIKH-{
1817 --
1818 --
1819 -- Update on inbound trips are allowed only if caller
1820 -- starts with one of the following:
1821 -- - FTE
1822 -- - WSH_IB
1823 -- - WSH_PUB
1824 -- - WSH_TP_RELEASE
1825 -- 3509004:public api changes
1826 IF NVL(l_shipments_type_flag,'O') = 'I'
1827 AND NVL(p_caller, '''') NOT LIKE 'FTE%'
1828 AND NVL(p_caller, '''') NOT LIKE 'WSH_PUB%'
1829 AND NVL(p_caller, '''') NOT LIKE 'WSH_IB%'
1830 AND NVL(p_caller, '''') NOT LIKE 'WSH_TP_RELEASE%'
1831 THEN
1832 RAISE e_all_disabled; --
1833 END IF;
1834 --
1835 --
1836 IF l_status_code IN ('IT', 'CL')
1837 AND (
1838 NVL(l_shipments_type_flag,'O') = 'I'
1839 OR (
1840 NVL(l_shipments_type_flag,'O') = 'M'
1841 AND ( NVL(p_caller, '''') LIKE 'FTE%'
1842 OR NVL(p_caller, '''') LIKE 'WSH_PUB%'
1843 OR NVL(p_caller, '''') LIKE 'WSH_IB%'
1844 )
1845 )
1846 )
1847 THEN
1848 --{
1849 --
1850 -- For inbound/mixed trips, following fields are updateable even if trip is closed.
1851 -- - carrier, mode of transport, service level, ship method,
1852 -- freight terms, carrier reference number,
1853 -- consignee carrier account number
1854 -- --- only if NULL
1855 -- - vehicle number and vehicle number prefix.
1856 --
1857 -- For mixed trips, update is allowed only if caller starts with FTE/WSH_PUB/WSH_IB
1858 --
1859 IF x_disabled_list(1) = 'FULL'
1860 THEN
1861 i := x_disabled_list.count;
1862 --
1863 IF NVL(p_caller,'''') LIKE 'FTE%' THEN --3509004:public api changes
1864 i:=i+1; x_disabled_list(i) := '+CARRIER_ID';
1865 i:=i+1; x_disabled_list(i) := '+SERVICE_LEVEL';
1866 i:=i+1; x_disabled_list(i) := '+MODE_OF_TRANSPORT';
1867 i:=i+1; x_disabled_list(i) := '+SHIP_METHOD_CODE';
1868 i:=i+1; x_disabled_list(i) := '+LANE_ID';
1869 i:=i+1; x_disabled_list(i) := '+FREIGHT_TERMS_CODE';
1870 ELSE
1871 i:=i+1; x_disabled_list(i) := '+FREIGHT_CODE';
1872 i:=i+1; x_disabled_list(i) := '+SERVICE_LEVEL_NAME';
1873 i:=i+1; x_disabled_list(i) := '+MODE_OF_TRANSPORT_NAME';
1874 i:=i+1; x_disabled_list(i) := '+SHIP_METHOD_NAME';
1875 i:=i+1; x_disabled_list(i) := '+FREIGHT_TERMS_NAME';
1876 -- Added the LANE_ID here because when FTE calls our Grp
1877 -- API, we are converting the caller to WSH_PUB.
1878 i:=i+1; x_disabled_list(i) := '+LANE_ID';
1879 END IF;
1880 IF l_lane_id IS NULL THEN
1881 -- Per FTE disable CARRIER_REFERENCE_NUMBER if lane_id is
1882 -- not null (enable if null).
1883 i:=i+1; x_disabled_list(i) := '+CARRIER_REFERENCE_NUMBER';
1884 END IF;
1885 i:=i+1; x_disabled_list(i) := '+CONSIGNEE_CARRIER_AC_NO';
1886 i:=i+1; x_disabled_list(i) := 'VEHICLE_NUMBER';
1887 i:=i+1; x_disabled_list(i) := 'VEHICLE_NUM_PREFIX';
1888 i:=i+1; x_disabled_list(i) := 'OPERATOR';
1889 END IF;
1890 --}
1891 END IF;
1892 --
1893 -- J-IB-NPARIKH-}
1894
1895 --
1896 -- Debug Statements
1897 --
1898 IF l_debug_on THEN
1899 WSH_DEBUG_SV.pop(l_module_name);
1900 END IF;
1901 --
1902 EXCEPTION
1903 WHEN e_all_disabled THEN --3509004:public api changes
1904 -- OTM R12, glog proj
1905 IF get_trip_status%ISOPEN THEN
1906 CLOSE get_trip_status;
1907 END IF;
1908 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1909 FND_MESSAGE.SET_NAME('WSH','WSH_ALL_COLS_DISABLED');
1910 FND_MESSAGE.Set_Token('ENTITY_ID',p_trip_id);
1911 wsh_util_core.add_message(x_return_status,l_module_name);
1912 IF l_debug_on THEN
1913 -- Nothing is updateable
1914 WSH_DEBUG_SV.pop(l_module_name,'e_all_disabled');
1915 END IF;
1916
1917
1918 WHEN WSH_DP_NO_ENTITY THEN
1919 -- OTM R12, glog proj
1920 IF get_trip_status%ISOPEN THEN
1921 CLOSE get_trip_status;
1922 END IF;
1923 FND_MESSAGE.SET_NAME('WSH', 'WSH_DP_NO_ENTITY');
1924 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR);
1925 x_return_status := FND_API.G_RET_STS_ERROR;
1926 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
1927 if x_msg_count > 1 then
1928 x_msg_data := l_msg_summary || l_msg_details;
1929 else
1930 x_msg_data := l_msg_summary;
1931 end if;
1932
1933 -- invalid list type
1934 --
1935 -- Debug Statements
1936 --
1937 IF l_debug_on THEN
1938 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_DP_NO_ENTITY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1939 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_DP_NO_ENTITY');
1940 END IF;
1941 --
1942 WHEN WSH_INV_LIST_TYPE THEN
1943 -- OTM R12, glog proj
1944 IF get_trip_status%ISOPEN THEN
1945 CLOSE get_trip_status;
1946 END IF;
1947 FND_MESSAGE.SET_NAME('WSH', 'WSH_INV_LIST_TYPE');
1948 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR);
1949 x_return_status := FND_API.G_RET_STS_ERROR;
1950 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
1951 if x_msg_count > 1 then
1952 x_msg_data := l_msg_summary || l_msg_details;
1953 else
1954 x_msg_data := l_msg_summary;
1955 end if;
1956
1957 --
1958 -- Debug Statements
1959 --
1960 IF l_debug_on THEN
1961 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INV_LIST_TYPE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1962 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INV_LIST_TYPE');
1963 END IF;
1964 --
1965 WHEN OTHERS THEN
1966 IF get_trip_status%ISOPEN THEN
1967 CLOSE get_trip_status;
1968 END IF;
1969
1970 FND_MESSAGE.Set_Name('WSH','WSH_UNEXPECTED_ERROR');
1971 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1972
1973
1974 --
1975 -- Debug Statements
1976 --
1977 IF l_debug_on THEN
1978 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1979 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1980 END IF;
1981 --
1982 END Get_Disabled_List ;
1983
1984 --Harmonizing Project
1985 /***************Validate_Planned_Trip***/
1986 PROCEDURE validate_planned_trip
1987 (p_stop_id IN NUMBER,
1988 p_stop_sequence_number IN NUMBER,
1989 x_return_status OUT NOCOPY VARCHAR2
1990 ) IS
1991
1992 CURSOR c_get_stop_seq IS
1993 SELECT stop_sequence_number,
1994 trip_id
1995 FROM wsh_trip_stops
1996 WHERE stop_id = p_stop_id;
1997
1998 -- kept it separte because this need not execute everytime
1999 CURSOR c_get_trip_planned(v_trip_id IN NUMBER) IS
2000 SELECT planned_flag
2001 FROM wsh_trips
2002 WHERE trip_id = v_trip_id;
2003
2004 l_stop_sequence_number NUMBER;
2005 l_trip_id NUMBER;
2006 l_trip_planned VARCHAR2(1) := 'N' ;
2007
2008 --
2009 l_debug_on BOOLEAN;
2010 --
2011 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_PLANNED_TRIP';
2012 --
2013 BEGIN
2014
2015 --
2016 -- Debug Statements
2017 --
2018 --
2019 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2020 --
2021 IF l_debug_on IS NULL
2022 THEN
2023 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2024 END IF;
2025 --
2026 IF l_debug_on THEN
2027 WSH_DEBUG_SV.push(l_module_name);
2028 --
2029 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
2030 WSH_DEBUG_SV.log(l_module_name,'P_STOP_SEQUENCE_NUMBER',P_STOP_SEQUENCE_NUMBER);
2031 END IF;
2032 --
2033 IF (p_stop_id IS NOT NULL) THEN
2034
2035 OPEN c_get_stop_seq;
2036 FETCH c_get_stop_seq
2037 INTO l_stop_sequence_number,
2038 l_trip_id;
2039 IF c_get_stop_seq%NOTFOUND THEN
2040 CLOSE c_get_stop_seq;
2041 ELSE
2042 CLOSE c_get_stop_seq;
2043 END IF;
2044
2045 IF p_stop_sequence_number <> l_stop_sequence_number THEN
2046 OPEN c_get_trip_planned(l_trip_id);
2047 FETCH c_get_trip_planned
2048 INTO l_trip_planned;
2049 IF c_get_trip_planned%NOTFOUND THEN
2050 CLOSE c_get_trip_planned;
2051 ELSE
2052 CLOSE c_get_trip_planned;
2053 END IF;
2054 -- new message
2055 -- Stop Sequence Number cannot be updated for a planned trip.
2056 IF l_trip_planned ='Y' THEN
2057 FND_MESSAGE.Set_Name('WSH','WSH_PLANNED_TRIP');
2058 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2059 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
2060 --
2061 -- Debug Statements
2062 --
2063 IF l_debug_on THEN
2064 WSH_DEBUG_SV.pop(l_module_name);
2065 END IF;
2066 --
2067 RETURN;
2068 ELSIF l_trip_planned = 'F' THEN
2069 FND_MESSAGE.Set_Name('WSH','WSH_FIRMED_TRIP');
2070 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2071 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
2072 --
2073 -- Debug Statements
2074 --
2075 IF l_debug_on THEN
2076 WSH_DEBUG_SV.pop(l_module_name);
2077 END IF;
2078 --
2079 RETURN;
2080 END IF;
2081
2082
2083 END IF; -- for stop sequence number match
2084 END IF;
2085
2086 /*
2087 ELSE -- fields are not null
2088 --invalid trip stop information
2089 FND_MESSAGE.Set_Name('WSH','WSH_STOP_NOT_FOUND');
2090 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2091 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
2092 --
2093 -- Debug Statements
2094 --
2095 IF l_debug_on THEN
2096 WSH_DEBUG_SV.pop(l_module_name);
2097 END IF;
2098 --
2099 RETURN;
2100
2101 END IF; -- fields are not null
2102
2103 */
2104 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2105 --
2106 -- Debug Statements
2107 --
2108 IF l_debug_on THEN
2109 WSH_DEBUG_SV.pop(l_module_name);
2110 END IF;
2111 --
2112 END validate_planned_trip;
2113
2114 --Harmonizing Project
2115 PROCEDURE Is_Action_Enabled(
2116 p_trip_rec_tab IN trip_rec_tab_type,
2117 p_action IN VARCHAR2,
2118 p_caller IN VARCHAR2,
2119 x_return_status OUT NOCOPY VARCHAR2,
2120 x_valid_ids OUT NOCOPY wsh_util_core.id_tab_type,
2121 x_error_ids OUT NOCOPY wsh_util_core.id_tab_type,
2122 x_valid_index_tab OUT NOCOPY wsh_util_core.id_tab_type
2123 ) IS
2124
2125 -- Added wsp.otm_enabled column for OTM R12 - org specific
2126 CURSOR trip_to_del_cur(p_trip_id IN NUMBER) is
2127 SELECT wnd.delivery_id,
2128 wnd.organization_id,
2129 wnd.status_code,
2130 wnd.planned_flag,
2131 /*J inbound logistics new column jckwok*/
2132 NVL(shipment_direction,'O') shipment_direction,
2133 wnd.delivery_type, --MDC
2134 NVL(wnd.ignore_for_planning, 'N') ignore_for_planning, -- OTM R12, glog proj
2135 NVL(wnd.tms_interface_flag,WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT) tms_interface_flag, -- OTM R12, glog proj
2136 wsp.otm_enabled -- OTM R12, glog proj
2137 FROM wsh_new_deliveries wnd,
2138 wsh_delivery_legs wdl,
2139 wsh_trip_stops wts1,
2140 wsh_trip_stops wts2,
2141 wsh_trips wt,
2142 wsh_shipping_parameters wsp -- OTM R12, glog proj
2143 WHERE wnd.delivery_id = wdl.delivery_id
2144 AND wts1.stop_id = wdl.PICK_UP_STOP_ID
2145 AND wts2.stop_id = wdl.DROP_OFF_STOP_ID
2146 AND wts1.trip_id = wt.trip_id
2147 AND wts2.trip_id = wt.trip_id
2148 AND wt.trip_id = p_trip_id
2149 and wsp.organization_id = wnd.organization_id; -- OTM R12, glog proj
2150
2151 l_trip_actions_tab TripActionsTabType;
2152 l_valid_ids wsh_util_core.id_tab_type;
2153 l_error_ids wsh_util_core.id_tab_type;
2154 l_valid_index_tab wsh_util_core.id_tab_type;
2155 l_dlvy_rec_tab WSH_DELIVERY_VALIDATIONS.dlvy_rec_tab_type;
2156 l_move_id NUMBER:= NULL;
2157
2158 l_pass_section_a VARCHAR2(1):='Y';
2159 l_tpw_temp VARCHAR2(1);
2160 l_status_code VARCHAR2(1);
2161 l_return_status VARCHAR2(1);
2162 error_in_init_actions EXCEPTION;
2163 e_set_messages EXCEPTION;
2164
2165 l_debug_on BOOLEAN;
2166 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'IS_ACTION_ENABLED';
2167 --
2168 l_caller VARCHAR2(100);
2169
2170 l_loop_counter NUMBER; --OTM R12, glog proj
2171
2172 BEGIN
2173 --
2174 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2175 --
2176 IF l_debug_on IS NULL
2177 THEN
2178 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2179 END IF;
2180 --
2181 IF l_debug_on THEN
2182 WSH_DEBUG_SV.push(l_module_name);
2183 --
2184 WSH_DEBUG_SV.log(l_module_name,'p_caller',p_caller);
2185 WSH_DEBUG_SV.log(l_module_name,'p_action',p_action);
2186 END IF;
2187
2188 -- call initialize API and get the pl/sql table
2189 Init_Trip_Actions_Tbl(
2190 p_action => p_action,
2191 x_trip_actions_tab => l_trip_actions_tab,
2192 x_return_status => x_return_status);
2193
2194 IF l_debug_on THEN
2195 WSH_DEBUG_SV.log(l_module_name,'Init_Detail_Actions_Tbl x_return_status',x_return_status);
2196 END IF;
2197
2198 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
2199 raise error_in_init_actions;
2200 END IF;
2201
2202
2203 FOR j IN p_trip_rec_tab.FIRST..p_trip_rec_tab.LAST LOOP
2204 BEGIN
2205
2206 -- J-IB-NPARIKH-{
2207 --
2208 l_caller := p_caller;
2209 --
2210 --
2211 IF l_debug_on THEN
2212 wsh_debug_sv.log(l_module_name, 'l_caller', l_caller);
2213 END IF;
2214 --
2215 --
2216 -- Actions on inbound trips are allowed only if caller
2217 -- starts with one of the following:
2218 -- - FTE
2219 -- - WSH_IB
2220 -- - WSH_PUB
2221 -- - WSH_TP_RELEASE
2222 -- For any other callers, set l_caller to WSH_FSTRX
2223 -- Since for caller, WSH_FSTRX, all actions are disabled
2224 -- on inbound trips
2225 --
2226 --
2227 --
2228 IF nvl(p_trip_rec_tab(j).shipments_type_flag,'O') = 'I'
2229 THEN
2230 --{
2231 IF l_caller LIKE 'FTE%'
2232 OR l_caller LIKE 'WSH_PUB%'
2233 OR l_caller LIKE 'WSH_IB%'
2234 OR l_caller LIKE 'WSH_TP_RELEASE%'
2235 THEN
2236 NULL;
2237 ELSE
2238 l_caller := 'WSH_FSTRX';
2239 END IF;
2240 --}
2241 END IF;
2242 --
2243 --
2244 IF l_debug_on THEN
2245 wsh_debug_sv.log(l_module_name, 'l_caller-modified', l_caller);
2246 END IF;
2247 -- J-IB-NPARIKH-}
2248 --
2249 -- section a
2250 IF ( l_trip_actions_tab.COUNT > 0 ) THEN
2251 l_loop_counter := l_trip_actions_tab.FIRST;
2252 LOOP -- OTM R12, loop modified per standard
2253 IF(NVL(l_trip_actions_tab(l_loop_counter).status_code,p_trip_rec_tab(j).status_code) = p_trip_rec_tab(j).status_code
2254 AND NVL(l_trip_actions_tab(l_loop_counter).planned_flag,NVL(p_trip_rec_tab(j).planned_flag,'N')) =
2255 NVL(p_trip_rec_tab(j).planned_flag,'N')
2256 AND NVL(l_trip_actions_tab(l_loop_counter).load_tender_status,NVL(p_trip_rec_tab(j).load_tender_status,'OPEN')) =
2257 NVL(p_trip_rec_tab(j).load_tender_status,'OPEN') -- 12 SELECT CARRIER
2258 AND NVL(l_trip_actions_tab(l_loop_counter).caller,l_caller) = l_caller -- J-IB-NPARIKH
2259 AND l_trip_actions_tab(l_loop_counter).action_not_allowed = p_action
2260 -- add check to compare shipments_type_flag jckwok
2261 AND NVL(l_trip_actions_tab(l_loop_counter).shipments_type_flag, NVL(p_trip_rec_tab(j).shipments_type_flag,'O')) = NVL(p_trip_rec_tab(j).shipments_type_flag,'O')
2262 --OTM R12, glog proj
2263 AND nvl(l_trip_actions_tab(l_loop_counter).ignore_for_planning, nvl(p_trip_rec_tab(j).ignore_for_planning, 'N')) = nvl(p_trip_rec_tab(j).ignore_for_planning, 'N')
2264 ) THEN
2265
2266 IF l_trip_actions_tab(l_loop_counter).message_name IS NOT NULL THEN
2267 IF l_debug_on THEN
2268 wsh_debug_sv.log(l_module_name, 'Message Name is', l_trip_actions_tab(l_loop_counter).message_name);
2269 END IF;
2270 FND_MESSAGE.SET_NAME('WSH',l_trip_actions_tab(l_loop_counter).message_name);
2271 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error);
2272 END IF;
2273 RAISE e_set_messages;
2274 END IF;
2275 EXIT WHEN l_loop_counter >= l_trip_actions_tab.LAST;
2276 l_loop_counter := l_trip_actions_tab.NEXT(l_loop_counter);
2277 END LOOP;
2278 END IF;
2279
2280 -- section b
2281 IF ( p_action = 'PICK-RELEASE')
2282 OR ( p_action = 'INCLUDE_PLAN')
2283 THEN
2284 FOR cur_rec IN trip_to_del_cur(p_trip_rec_tab(j).trip_id) LOOP
2285 l_dlvy_rec_tab(l_dlvy_rec_tab.count+1) := cur_rec;
2286 END LOOP;
2287
2288 IF (l_dlvy_rec_tab.COUNT>0 AND p_action='INCLUDE_PLAN') OR (p_action='PICK-RELEASE') THEN
2289 WSH_DELIVERY_VALIDATIONS.Is_Action_Enabled(
2290 p_dlvy_rec_tab => l_dlvy_rec_tab,
2291 p_action => p_action,
2292 p_caller => p_caller,
2293 x_return_status => l_return_status,
2294 x_valid_ids => l_valid_ids,
2295 x_error_ids => l_error_ids,
2296 x_valid_index_tab => l_valid_index_tab);
2297
2298 IF l_debug_on THEN
2299 WSH_DEBUG_SV.log(l_module_name,'WSH_DELIVERY_VALIDATIONS.Is_Action_Enabled l_return_status',l_return_status);
2300 END IF;
2301
2302 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS)
2303 AND (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)
2304 THEN
2305 RAISE e_set_messages;
2306 --J-IB-NPARIKH--{
2307 ELSIF p_action = 'INCLUDE_PLAN'
2308 AND l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING
2309 THEN
2310 --
2311 -- If some of the deliveries cannot be included for planning,
2312 -- then trip cannot be included for planning.
2313 --
2314 RAISE e_set_messages;
2315 --J-IB-NPARIKH--}
2316 ELSE
2317 --{
2318 x_valid_ids(x_valid_ids.COUNT + 1) := p_trip_rec_tab(j).trip_id;
2319 x_valid_index_tab(j) := j;
2320 --}
2321 END IF;
2322
2323 ELSE
2324 x_valid_ids(x_valid_ids.COUNT + 1) := p_trip_rec_tab(j).trip_id;
2325 x_valid_index_tab(j) := j;
2326 END IF;--l_dlvy_rec_tab.count>0
2327 ELSIF p_action = 'SELECT-CARRIER' THEN
2328
2329 IF p_trip_rec_tab(j).lane_id IS NOT NULL THEN
2330 IF l_debug_on THEN
2331 wsh_debug_sv.log(l_module_name, 'Trip has lane id : ', p_trip_rec_tab(j).lane_id);
2332 END IF;
2333 FND_MESSAGE.SET_NAME('WSH','WSH_FTE_SEL_TRIP_LANE');
2334 FND_MESSAGE.SET_TOKEN('TRIPID',p_trip_rec_tab(j).trip_id);
2335 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error);
2336 RAISE e_set_messages;
2337 END IF;
2338
2339 -- Perform continuous move check here
2340 l_move_id := WSH_FTE_INTEGRATION.GET_TRIP_MOVE(p_trip_rec_tab(j).trip_id);
2341
2342 --IF l_move_id IS NOT NULL THEN
2343 IF l_move_id <> -1 THEN
2344 IF l_debug_on THEN
2345 wsh_debug_sv.log(l_module_name, 'Trip has continuous move id id : ', l_move_id);
2346 END IF;
2347 FND_MESSAGE.SET_NAME('WSH','WSH_FTE_SEL_TRIP_MOVE');
2348 FND_MESSAGE.SET_TOKEN('TRIPID',p_trip_rec_tab(j).trip_id);
2349 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error);
2350 RAISE e_set_messages;
2351
2352 END IF;
2353 x_valid_ids(x_valid_ids.COUNT + 1) := p_trip_rec_tab(j).trip_id;
2354 x_valid_index_tab(j) := j;
2355
2356 ELSE
2357 x_valid_ids(x_valid_ids.COUNT + 1) := p_trip_rec_tab(j).trip_id;
2358 x_valid_index_tab(j) := j;
2359 END IF;
2360 EXCEPTION
2361 WHEN e_set_messages THEN
2362 x_error_ids(x_error_ids.COUNT + 1) := p_trip_rec_tab(j).trip_id;
2363 IF p_caller = 'WSH_PUB' or p_caller like 'FTE%' THEN
2364 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_ACTION_INELIGIBLE');
2365 FND_MESSAGE.Set_Token('ACTION',wsh_util_core.get_action_meaning('TRIP',p_action));
2366 FND_MESSAGE.Set_Token('TRIP_NAME',
2367 wsh_trips_pvt.get_name(x_error_ids(x_error_ids.COUNT)));
2368 wsh_util_core.add_message('E',l_module_name);
2369 END IF;
2370 END;
2371
2372 END LOOP; -- FOR j IN p_trip_rec_tab.FIRST
2373
2374 IF (x_valid_ids.COUNT = 0 ) THEN
2375 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2376 --
2377 IF NOT (l_caller = 'WSH_PUB' OR l_caller LIKE 'FTE%') THEN
2378 FND_MESSAGE.SET_NAME('WSH','WSH_ACTION_ENABLED');
2379 wsh_util_core.add_message(x_return_status,l_module_name);
2380 END IF;
2381 --
2382 ELSIF (x_valid_ids.COUNT = p_trip_rec_tab.COUNT) THEN
2383 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2384 ELSIF (x_valid_ids.COUNT < p_trip_rec_tab.COUNT ) THEN
2385 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2386 --
2387 IF NOT (l_caller = 'WSH_PUB' OR l_caller LIKE 'FTE%') THEN
2388 FND_MESSAGE.SET_NAME('WSH','WSH_ACTION_ENABLED_WARN');
2389 wsh_util_core.add_message(x_return_status,l_module_name);
2390 END IF;
2391 --
2392 ElSE
2393 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2394 --
2395 IF NOT (l_caller = 'WSH_PUB' OR l_caller LIKE 'FTE%') THEN
2396 FND_MESSAGE.SET_NAME('WSH','WSH_ACTION_ENABLED');
2397 wsh_util_core.add_message(x_return_status,l_module_name);
2398 END IF;
2399 --
2400 END IF;
2401
2402 IF l_debug_on THEN
2403 WSH_DEBUG_SV.pop(l_module_name);
2404 END IF;
2405
2406 EXCEPTION
2407 WHEN error_in_init_actions THEN
2408 IF l_debug_on THEN
2409 WSH_DEBUG_SV.logmsg(l_module_name,'error_in_init_actions exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2410 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:error_in_init_actions');
2411 END IF;
2412
2413 WHEN OTHERS THEN
2414 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.IS_ACTION_ENABLED');
2415 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2416 IF l_debug_on THEN
2417 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
2418 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2419 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2420 END IF;
2421 END Is_Action_Enabled;
2422
2423
2424 PROCEDURE Validate_Arrive_after_trip(
2425 p_trip_id IN NUMBER,
2426 p_arr_after_trip_id IN OUT NOCOPY NUMBER,
2427 p_arr_after_trip_name IN VARCHAR2,
2428 x_return_status OUT NOCOPY VARCHAR2) IS
2429
2430 CURSOR get_trip_id(cp_arr_after_trip_id NUMBER) IS
2431 SELECT 'X'
2432 FROM wsh_trips
2433 WHERE trip_id = cp_arr_after_trip_id
2434 AND status_code <> 'CL';
2435
2436 CURSOR get_trip_name(cp_arr_after_trip_name VARCHAR2) IS
2437 SELECT 'X',trip_id
2438 FROM wsh_trips
2439 WHERE name = cp_arr_after_trip_name
2440 AND status_code <> 'CL';
2441
2442 l_debug_on BOOLEAN;
2443 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_ARRIVE_AFTER_TRIP';
2444 l_status VARCHAR2(1);
2445 BEGIN
2446 --
2447 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2448 --
2449 IF l_debug_on IS NULL
2450 THEN
2451 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2452 END IF;
2453 --
2454 IF l_debug_on THEN
2455 WSH_DEBUG_SV.push(l_module_name);
2456 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
2457 WSH_DEBUG_SV.log(l_module_name,'p_arr_after_trip_id',p_arr_after_trip_id);
2458 WSH_DEBUG_SV.log(l_module_name,'p_arr_after_trip_name',p_arr_after_trip_name);
2459 END IF;
2460
2461 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2462
2463 IF ((p_trip_id IS NULL OR p_trip_id = fnd_api.g_miss_num)
2464 OR ((p_arr_after_trip_id IS NULL OR p_arr_after_trip_id=fnd_api.g_miss_num)
2465 and (p_arr_after_trip_name IS NULL or p_arr_after_trip_name=fnd_api.g_miss_char)
2466 )
2467 ) THEN
2468 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2469 IF l_debug_on THEN
2470 WSH_DEBUG_SV.pop(l_module_name);
2471 END IF;
2472 RETURN;
2473 END IF;
2474
2475 IF (p_trip_id = p_arr_after_trip_id ) THEN
2476 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2477 ELSE
2478 IF (p_arr_after_trip_id IS NOT NULL ) THEN
2479 OPEN get_trip_id(p_arr_after_trip_id);
2480 FETCH get_trip_id INTO l_status;
2481 CLOSE get_trip_id;
2482 ELSIF (p_arr_after_trip_name IS NOT NULL ) THEN
2483 OPEN get_trip_name(p_arr_after_trip_name);
2484 FETCH get_trip_name INTO l_status,p_arr_after_trip_id;
2485 CLOSE get_trip_name;
2486 ELSE
2487 l_status := FND_API.G_MISS_CHAR;
2488 END IF;
2489
2490 IF (l_status IS NULL OR p_trip_id = p_arr_after_trip_id) THEN
2491 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2492 END IF;
2493
2494 END IF;
2495
2496 IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
2497 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ARR_AFTER_TRIP');
2498 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
2499 END IF;
2500
2501 IF l_debug_on THEN
2502 WSH_DEBUG_SV.pop(l_module_name);
2503 END IF;
2504
2505 EXCEPTION
2506 WHEN OTHERS THEN
2507 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.VALIDATE_ARRIVE_AFTER_TRIP');
2508 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2509 IF l_debug_on THEN
2510 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
2511 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2512 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2513 END IF;
2514 END Validate_Arrive_after_trip;
2515
2516
2517 PROCEDURE Validate_Consol_Allowed(
2518 p_trip_info IN WSH_TRIPS_PVT.trip_rec_type,
2519 p_db_trip_info IN WSH_TRIPS_PVT.trip_rec_type,
2520 x_return_status OUT NOCOPY VARCHAR2) IS
2521
2522 l_vehicle_item_id wsh_trips.vehicle_item_id%type;
2523 l_vehicle_organization_id wsh_trips.vehicle_organization_id%type;
2524 l_ship_method_code wsh_trips.ship_method_code%type;
2525 l_carrier_id wsh_trips.carrier_id%type;
2526 l_service_level wsh_trips.service_level%type;
2527 l_mode_of_transport wsh_trips.mode_of_transport%type;
2528 l_lane_id wsh_trips.lane_id%type;
2529 l_c_truck CONSTANT VARCHAR2(10):='TRUCK';
2530
2531 l_debug_on BOOLEAN;
2532 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_CONSOL_ALLOWED';
2533 l_consolidation_allowed VARCHAR2(100);
2534 l_entity_ids WSH_UTIL_CORE.id_tab_type;
2535 BEGIN
2536 --
2537 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2538 --
2539 IF l_debug_on IS NULL
2540 THEN
2541 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2542 END IF;
2543 --
2544
2545
2546 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2547
2548 -- Bug fix: 3365750
2549 -- Do not give error if FTE is not installed.
2550 -- Proceed with code only if FTE is installed. Do nothing if NOT installed.
2551 IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y') THEN
2552 l_consolidation_allowed :=p_db_trip_info.consolidation_allowed;
2553 l_vehicle_item_id :=p_db_trip_info.vehicle_item_id;
2554 l_vehicle_organization_id :=p_db_trip_info.vehicle_organization_id;
2555 l_ship_method_code :=p_db_trip_info.ship_method_code;
2556 l_carrier_id :=p_db_trip_info.carrier_id;
2557 l_service_level :=p_db_trip_info.service_level;
2558 l_mode_of_transport :=p_db_trip_info.mode_of_transport;
2559 l_lane_id :=p_db_trip_info.lane_id;
2560
2561 IF l_debug_on THEN
2562 WSH_DEBUG_SV.push(l_module_name);
2563 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',p_trip_info.trip_id);
2564 WSH_DEBUG_SV.log(l_module_name,'p_consolidation_allowed',p_trip_info.consolidation_allowed);
2565 WSH_DEBUG_SV.log(l_module_name,'p_veh_item_id',p_trip_info.vehicle_item_id);
2566 WSH_DEBUG_SV.log(l_module_name,'p_veh_org_id',p_trip_info.vehicle_organization_id);
2567 WSH_DEBUG_SV.log(l_module_name,'p_ship_method_code',p_trip_info.ship_method_code);
2568 WSH_DEBUG_SV.log(l_module_name,'p_carrier_id',p_trip_info.carrier_id);
2569 WSH_DEBUG_SV.log(l_module_name,'p_service_level',p_trip_info.service_level);
2570 WSH_DEBUG_SV.log(l_module_name,'p_mode_of_transport',p_trip_info.mode_of_transport);
2571 WSH_DEBUG_SV.log(l_module_name,'p_lane_id',p_trip_info.lane_id);
2572
2573 WSH_DEBUG_SV.log(l_module_name,'l_consolidation_allowed',l_consolidation_allowed);
2574 WSH_DEBUG_SV.log(l_module_name,'l_veh_item_id',l_vehicle_item_id);
2575 WSH_DEBUG_SV.log(l_module_name,'l_veh_org_id',l_vehicle_organization_id);
2576 WSH_DEBUG_SV.log(l_module_name,'l_ship_method_code',l_ship_method_code);
2577 WSH_DEBUG_SV.log(l_module_name,'l_carrier_id',l_carrier_id);
2578 WSH_DEBUG_SV.log(l_module_name,'l_service_level',l_service_level);
2579 WSH_DEBUG_SV.log(l_module_name,'l_mode_of_transport',l_mode_of_transport);
2580 WSH_DEBUG_SV.log(l_module_name,'l_lane_id',l_lane_id);
2581 END IF;
2582
2583
2584 --if lane_id, carrier_id, ship_method_code, service_level, mode_of_transport are changed
2585 --or if mode is truck and vehicle item or vehicle org is changed, then mark leg for reprice
2586 IF (
2587 (nvl(p_trip_info.lane_id,-99) <> FND_API.G_MISS_NUM AND (nvl(p_trip_info.lane_id,-99) <> nvl(l_lane_id,-99)))
2588 OR (nvl(p_trip_info.carrier_id,-99) <> FND_API.G_MISS_NUM AND (nvl(p_trip_info.carrier_id,-99) <> nvl(l_carrier_id,-99)))
2589 OR (nvl(p_trip_info.ship_method_code,'-99') <> FND_API.G_MISS_CHAR AND (nvl(p_trip_info.ship_method_code,'-99') <> nvl(l_ship_method_code,'-99')))
2590 OR (nvl(p_trip_info.service_level,'-99') <> FND_API.G_MISS_CHAR AND (nvl(p_trip_info.service_level,'-99') <> nvl(l_service_level,'-99')))
2591 OR (nvl(p_trip_info.mode_of_transport,'-99') <> FND_API.G_MISS_CHAR AND (nvl(p_trip_info.mode_of_transport,'-99') <> nvl(l_mode_of_transport,'-99')))
2592 OR (
2593 (p_trip_info.mode_of_transport=l_c_truck OR l_mode_of_transport=l_c_truck)
2594 AND (
2595 (nvl(p_trip_info.vehicle_item_id,-99) <> FND_API.G_MISS_NUM AND (nvl(p_trip_info.vehicle_item_id,-99) <> nvl(l_vehicle_item_id,-99)))
2596 OR (nvl(p_trip_info.vehicle_organization_id,-99) <> FND_API.G_MISS_NUM AND (nvl(p_trip_info.vehicle_organization_id,-99) <> nvl(l_vehicle_organization_id,-99)))
2597 )
2598 )
2599 ) THEN
2600 l_entity_ids(1):=p_trip_info.trip_id;
2601 WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required(
2602 p_entity_type => 'TRIP',
2603 p_entity_ids => l_entity_ids,
2604 p_consolidation_change => p_trip_info.consolidation_allowed,
2605 x_return_status => x_return_status);
2606 END IF;
2607
2608 END IF;
2609
2610 IF l_debug_on THEN
2611 WSH_DEBUG_SV.pop(l_module_name);
2612 END IF;
2613
2614 EXCEPTION
2615 WHEN OTHERS THEN
2616 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.VALIDATE_CONSOL_ALLOWED');
2617 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2618 IF l_debug_on THEN
2619 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
2620 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2621 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2622 END IF;
2623 END Validate_Consol_Allowed;
2624
2625
2626 /*
2627 Procedure populate_external_edf is called from
2628 eliminate_displayonly_fields to populate the external value
2629 for a given internal field
2630 */
2631
2632 PROCEDURE populate_external_edf(
2633 p_internal IN NUMBER
2634 , p_external IN VARCHAR2
2635 , x_internal IN OUT NOCOPY NUMBER
2636 , x_external IN OUT NOCOPY VARCHAR2
2637 )
2638 IS
2639 BEGIN
2640
2641 IF p_internal <> FND_API.G_MISS_NUM OR p_internal IS NULL THEN
2642 x_internal := p_internal;
2643 IF p_internal IS NULL THEN
2644 x_external := NULL;
2645 ELSE
2646 x_external := p_external;
2647 END IF;
2648 ELSIF p_external <> FND_API.G_MISS_CHAR OR p_external IS NULL THEN
2649 x_external := p_external;
2650 IF x_external IS NULL THEN
2651 x_internal := NULL;
2652 ELSE
2653 x_internal := p_internal;
2654 END IF;
2655 END IF;
2656
2657 END populate_external_edf;
2658
2659
2660
2661 /*
2662 Procedure populate_external_edf is called from
2663 eliminate_displayonly_fields to populate the external value
2664 for a given internal field
2665 */
2666
2667 PROCEDURE populate_external_edf(
2668 p_internal IN VARCHAR2
2669 , p_external IN VARCHAR2
2670 , x_internal IN OUT NOCOPY VARCHAR2
2671 , x_external IN OUT NOCOPY VARCHAR2
2672 )
2673 IS
2674 BEGIN
2675
2676 IF p_internal <> FND_API.G_MISS_CHAR OR p_internal IS NULL THEN
2677 x_internal := p_internal;
2678 IF p_internal IS NULL THEN
2679 x_external := NULL;
2680 ELSE
2681 x_external := p_external;
2682 END IF;
2683 ELSIF p_external <> FND_API.G_MISS_CHAR OR p_external IS NULL THEN
2684 x_external := p_external;
2685 IF x_external IS NULL THEN
2686 x_internal := NULL;
2687 ELSE
2688 x_internal := p_internal;
2689 END IF;
2690 END IF;
2691
2692 END populate_external_edf;
2693
2694
2695
2696 PROCEDURE eliminate_displayonly_fields (
2697 p_trip_rec IN WSH_TRIPS_PVT.trip_rec_type
2698 , p_in_rec IN WSH_TRIPS_GRP.TripInRecType
2699 , x_trip_rec IN OUT NOCOPY WSH_TRIPS_PVT.trip_rec_type
2700 )
2701 IS
2702 BEGIN
2703
2704 /*
2705 Enable the x_delivery_detail_rec, with the columns that are not
2706 permanently disabled.
2707 */
2708 IF p_trip_rec.NAME <> FND_API.G_MISS_CHAR
2709 OR p_trip_rec.NAME IS NULL THEN
2710 x_trip_rec.NAME := p_trip_rec.NAME;
2711 END IF;
2712
2713 populate_external_edf(p_trip_rec.ship_method_code,
2714 p_trip_rec.ship_method_name,
2715 x_trip_rec.ship_method_code,
2716 x_trip_rec.ship_method_name);
2717
2718 IF p_trip_rec.CARRIER_ID <> FND_API.G_MISS_NUM
2719 OR p_trip_rec.CARRIER_ID IS NULL THEN
2720 x_trip_rec.CARRIER_ID := p_trip_rec.CARRIER_ID;
2721 END IF;
2722
2723 IF p_trip_rec.ROUTE_ID <> FND_API.G_MISS_NUM
2724 OR p_trip_rec.ROUTE_ID IS NULL THEN
2725 x_trip_rec.ROUTE_ID := p_trip_rec.ROUTE_ID;
2726 END IF;
2727 IF p_trip_rec.FREIGHT_TERMS_CODE <> FND_API.G_MISS_CHAR
2728 OR p_trip_rec.FREIGHT_TERMS_CODE IS NULL THEN
2729 x_trip_rec.FREIGHT_TERMS_CODE := p_trip_rec.FREIGHT_TERMS_CODE;
2730 END IF;
2731 IF p_trip_rec.LOAD_TENDER_STATUS <> FND_API.G_MISS_CHAR
2732 OR p_trip_rec.LOAD_TENDER_STATUS IS NULL THEN
2733 x_trip_rec.LOAD_TENDER_STATUS := p_trip_rec.LOAD_TENDER_STATUS;
2734 END IF;
2735 IF p_trip_rec.LOAD_TENDER_NUMBER <> FND_API.G_MISS_NUM
2736 OR p_trip_rec.LOAD_TENDER_NUMBER IS NULL THEN
2737 x_trip_rec.LOAD_TENDER_NUMBER := p_trip_rec.LOAD_TENDER_NUMBER;
2738 END IF;
2739 IF p_trip_rec.VESSEL <> FND_API.G_MISS_CHAR
2740 OR p_trip_rec.VESSEL IS NULL THEN
2741 x_trip_rec.VESSEL := p_trip_rec.VESSEL;
2742 END IF;
2743 IF p_trip_rec.VOYAGE_NUMBER <> FND_API.G_MISS_CHAR
2744 OR p_trip_rec.VOYAGE_NUMBER IS NULL THEN
2745 x_trip_rec.VOYAGE_NUMBER := p_trip_rec.VOYAGE_NUMBER;
2746 END IF;
2747 IF p_trip_rec.PORT_OF_LOADING <> FND_API.G_MISS_CHAR
2748 OR p_trip_rec.PORT_OF_LOADING IS NULL THEN
2749 x_trip_rec.PORT_OF_LOADING:= p_trip_rec.PORT_OF_LOADING;
2750 END IF;
2751 IF p_trip_rec.PORT_OF_DISCHARGE <> FND_API.G_MISS_CHAR
2752 OR p_trip_rec.PORT_OF_DISCHARGE IS NULL THEN
2753 x_trip_rec.PORT_OF_DISCHARGE := p_trip_rec.PORT_OF_DISCHARGE;
2754 END IF;
2755 IF p_trip_rec.WF_NAME <> FND_API.G_MISS_CHAR
2756 OR p_trip_rec.WF_NAME IS NULL THEN
2757 x_trip_rec.WF_NAME := p_trip_rec.WF_NAME;
2758 END IF;
2759 IF p_trip_rec.WF_PROCESS_NAME <> FND_API.G_MISS_CHAR
2760 OR p_trip_rec.WF_PROCESS_NAME IS NULL THEN
2761 x_trip_rec.WF_PROCESS_NAME := p_trip_rec.WF_PROCESS_NAME;
2762 END IF;
2763 IF p_trip_rec.WF_ITEM_KEY <> FND_API.G_MISS_CHAR
2764 OR p_trip_rec.WF_ITEM_KEY IS NULL THEN
2765 x_trip_rec.WF_ITEM_KEY := p_trip_rec.WF_ITEM_KEY;
2766 END IF;
2767 IF p_trip_rec.CARRIER_CONTACT_ID <> FND_API.G_MISS_NUM
2768 OR p_trip_rec.CARRIER_CONTACT_ID IS NULL THEN
2769 x_trip_rec.CARRIER_CONTACT_ID := p_trip_rec.CARRIER_CONTACT_ID;
2770 END IF;
2771 IF p_trip_rec.SHIPPER_WAIT_TIME <> FND_API.G_MISS_NUM
2772 OR p_trip_rec.SHIPPER_WAIT_TIME IS NULL THEN
2773 x_trip_rec.SHIPPER_WAIT_TIME := p_trip_rec.SHIPPER_WAIT_TIME;
2774 END IF;
2775 IF p_trip_rec.WAIT_TIME_UOM <> FND_API.G_MISS_CHAR
2776 OR p_trip_rec.WAIT_TIME_UOM IS NULL THEN
2777 x_trip_rec.WAIT_TIME_UOM := p_trip_rec.WAIT_TIME_UOM;
2778 END IF;
2779 IF p_trip_rec.LOAD_TENDERED_TIME <> FND_API.G_MISS_DATE
2780 OR p_trip_rec.LOAD_TENDERED_TIME IS NULL THEN
2781 x_trip_rec.LOAD_TENDERED_TIME := p_trip_rec.LOAD_TENDERED_TIME;
2782 END IF;
2783 IF p_trip_rec.CARRIER_RESPONSE <> FND_API.G_MISS_CHAR
2784 OR p_trip_rec.CARRIER_RESPONSE IS NULL THEN
2785 x_trip_rec.CARRIER_RESPONSE := p_trip_rec.CARRIER_RESPONSE;
2786 END IF;
2787
2788 IF p_trip_rec.ROUTE_LANE_ID <> FND_API.G_MISS_NUM
2789 OR p_trip_rec.ROUTE_LANE_ID IS NULL THEN
2790 x_trip_rec.ROUTE_LANE_ID := p_trip_rec.ROUTE_LANE_ID;
2791 END IF;
2792 IF p_trip_rec.LANE_ID <> FND_API.G_MISS_NUM
2793 OR p_trip_rec.LANE_ID IS NULL THEN
2794 x_trip_rec.LANE_ID := p_trip_rec.LANE_ID;
2795 END IF;
2796 IF p_trip_rec.SCHEDULE_ID <> FND_API.G_MISS_NUM
2797 OR p_trip_rec.SCHEDULE_ID IS NULL THEN
2798 x_trip_rec.SCHEDULE_ID := p_trip_rec.SCHEDULE_ID;
2799 END IF;
2800 IF p_trip_rec.BOOKING_NUMBER <> FND_API.G_MISS_CHAR
2801 OR p_trip_rec.BOOKING_NUMBER IS NULL THEN
2802 x_trip_rec.BOOKING_NUMBER := p_trip_rec.BOOKING_NUMBER;
2803 END IF;
2804
2805 IF p_trip_rec.SERVICE_LEVEL <> FND_API.G_MISS_CHAR
2806 OR p_trip_rec.SERVICE_LEVEL IS NULL THEN
2807 x_trip_rec.SERVICE_LEVEL := p_trip_rec.SERVICE_LEVEL;
2808 END IF;
2809 IF p_trip_rec.MODE_OF_TRANSPORT <> FND_API.G_MISS_CHAR
2810 OR p_trip_rec.MODE_OF_TRANSPORT IS NULL THEN
2811 x_trip_rec.MODE_OF_TRANSPORT := p_trip_rec.MODE_OF_TRANSPORT;
2812 END IF;
2813 IF p_trip_rec.IGNORE_FOR_PLANNING <> FND_API.G_MISS_CHAR
2814 AND (p_in_rec.CALLER LIKE 'FTE%' OR p_in_rec.CALLER LIKE 'WSH_CONSOL%')
2815 AND p_in_rec.ACTION_CODE = 'CREATE' THEN
2816 -- bug 3694794: FTE needs to create trip with ignore = Y
2817 x_trip_rec.IGNORE_FOR_PLANNING := p_trip_rec.IGNORE_FOR_PLANNING;
2818 END IF;
2819 populate_external_edf(p_trip_rec.VEHICLE_ORGANIZATION_ID,
2820 p_trip_rec.VEHICLE_ORGANIZATION_CODE,
2821 x_trip_rec.VEHICLE_ORGANIZATION_ID,
2822 x_trip_rec.VEHICLE_ORGANIZATION_CODE);
2823
2824 populate_external_edf(p_trip_rec.VEHICLE_ITEM_ID,
2825 p_trip_rec.VEHICLE_ITEM_DESC,
2826 x_trip_rec.VEHICLE_ITEM_ID,
2827 x_trip_rec.VEHICLE_ITEM_DESC);
2828
2829 IF p_trip_rec.VEHICLE_NUM_PREFIX <> FND_API.G_MISS_CHAR
2830 OR p_trip_rec.VEHICLE_NUM_PREFIX IS NULL THEN
2831 x_trip_rec.VEHICLE_NUM_PREFIX := p_trip_rec.VEHICLE_NUM_PREFIX;
2832 END IF;
2833 IF p_trip_rec.VEHICLE_NUMBER <> FND_API.G_MISS_CHAR
2834 OR p_trip_rec.VEHICLE_NUMBER IS NULL THEN
2835 x_trip_rec.VEHICLE_NUMBER := p_trip_rec.VEHICLE_NUMBER;
2836 END IF;
2837
2838 populate_external_edf(p_trip_rec.ARRIVE_AFTER_TRIP_ID,
2839 p_trip_rec.ARRIVE_AFTER_TRIP_NAME,
2840 x_trip_rec.ARRIVE_AFTER_TRIP_ID,
2841 x_trip_rec.ARRIVE_AFTER_TRIP_NAME);
2842
2843 IF p_trip_rec.ROUTING_INSTRUCTIONS <> FND_API.G_MISS_CHAR
2844 OR p_trip_rec.ROUTING_INSTRUCTIONS IS NULL THEN
2845 x_trip_rec.ROUTING_INSTRUCTIONS := p_trip_rec.ROUTING_INSTRUCTIONS;
2846 END IF;
2847 IF p_trip_rec.CONSOLIDATION_ALLOWED <> FND_API.G_MISS_CHAR
2848 OR p_trip_rec.CONSOLIDATION_ALLOWED IS NULL THEN
2849 x_trip_rec.CONSOLIDATION_ALLOWED := p_trip_rec.CONSOLIDATION_ALLOWED;
2850 END IF;
2851 IF p_trip_rec.OPERATOR <> FND_API.G_MISS_CHAR
2852 OR p_trip_rec.OPERATOR IS NULL THEN
2853 x_trip_rec.OPERATOR := p_trip_rec.OPERATOR;
2854 END IF;
2855
2856 IF p_trip_rec.attribute1 <> FND_API.G_MISS_CHAR
2857 OR p_trip_rec.attribute1 IS NULL THEN
2858 x_trip_rec.attribute1 := p_trip_rec.attribute1;
2859 END IF;
2860 IF p_trip_rec.attribute2 <> FND_API.G_MISS_CHAR
2861 OR p_trip_rec.attribute2 IS NULL THEN
2862 x_trip_rec.attribute2 := p_trip_rec.attribute2;
2863 END IF;
2864 IF p_trip_rec.attribute3 <> FND_API.G_MISS_CHAR
2865 OR p_trip_rec.attribute3 IS NULL THEN
2866 x_trip_rec.attribute3 := p_trip_rec.attribute3;
2867 END IF;
2868 IF p_trip_rec.attribute4 <> FND_API.G_MISS_CHAR
2869 OR p_trip_rec.attribute4 IS NULL THEN
2870 x_trip_rec.attribute4 := p_trip_rec.attribute4;
2871 END IF;
2872 IF p_trip_rec.attribute5 <> FND_API.G_MISS_CHAR
2873 OR p_trip_rec.attribute5 IS NULL THEN
2874 x_trip_rec.attribute5 := p_trip_rec.attribute5;
2875 END IF;
2876 IF p_trip_rec.attribute6 <> FND_API.G_MISS_CHAR
2877 OR p_trip_rec.attribute6 IS NULL THEN
2878 x_trip_rec.attribute6 := p_trip_rec.attribute6;
2879 END IF;
2880 IF p_trip_rec.attribute7 <> FND_API.G_MISS_CHAR
2881 OR p_trip_rec.attribute7 IS NULL THEN
2882 x_trip_rec.attribute7 := p_trip_rec.attribute7;
2883 END IF;
2884 IF p_trip_rec.attribute8 <> FND_API.G_MISS_CHAR
2885 OR p_trip_rec.attribute8 IS NULL THEN
2886 x_trip_rec.attribute8 := p_trip_rec.attribute8;
2887 END IF;
2888 IF p_trip_rec.attribute9 <> FND_API.G_MISS_CHAR
2889 OR p_trip_rec.attribute9 IS NULL THEN
2890 x_trip_rec.attribute9 := p_trip_rec.attribute9;
2891 END IF;
2892 IF p_trip_rec.attribute10 <> FND_API.G_MISS_CHAR
2893 OR p_trip_rec.attribute10 IS NULL THEN
2894 x_trip_rec.attribute10 := p_trip_rec.attribute10;
2895 END IF;
2896 IF p_trip_rec.attribute11 <> FND_API.G_MISS_CHAR
2897 OR p_trip_rec.attribute11 IS NULL THEN
2898 x_trip_rec.attribute11 := p_trip_rec.attribute11;
2899 END IF;
2900 IF p_trip_rec.attribute12 <> FND_API.G_MISS_CHAR
2901 OR p_trip_rec.attribute12 IS NULL THEN
2902 x_trip_rec.attribute12 := p_trip_rec.attribute12;
2903 END IF;
2904 IF p_trip_rec.attribute13 <> FND_API.G_MISS_CHAR
2905 OR p_trip_rec.attribute13 IS NULL THEN
2906 x_trip_rec.attribute13 := p_trip_rec.attribute13;
2907 END IF;
2908 IF p_trip_rec.attribute14 <> FND_API.G_MISS_CHAR
2909 OR p_trip_rec.attribute14 IS NULL THEN
2910 x_trip_rec.attribute14 := p_trip_rec.attribute14;
2911 END IF;
2912 IF p_trip_rec.attribute15 <> FND_API.G_MISS_CHAR
2913 OR p_trip_rec.attribute15 IS NULL THEN
2914 x_trip_rec.attribute15 := p_trip_rec.attribute15;
2915 END IF;
2916 IF p_trip_rec.ATTRIBUTE_CATEGORY <> FND_API.G_MISS_CHAR
2917 OR p_trip_rec.ATTRIBUTE_CATEGORY IS NULL THEN
2918 x_trip_rec.ATTRIBUTE_CATEGORY := p_trip_rec.ATTRIBUTE_CATEGORY;
2919 END IF;
2920 -- OTM R12, glog project, allow GC3 Inbound Message to update tp_plan_name
2921 IF p_in_rec.caller IN ('WSH_TP_RELEASE','FTE_TMS_INTEGRATION') THEN
2922 IF p_trip_rec.tp_plan_name <> FND_API.G_MISS_CHAR
2923 OR p_trip_rec.tp_plan_name IS NULL THEN
2924 x_trip_rec.tp_plan_name := p_trip_rec.tp_plan_name;
2925 END IF;
2926 IF p_trip_rec.tp_trip_number <> FND_API.G_MISS_NUM
2927 OR p_trip_rec.tp_trip_number IS NULL THEN
2928 x_trip_rec.tp_trip_number := p_trip_rec.tp_trip_number;
2929 END IF;
2930 END IF;
2931
2932 IF p_in_rec.caller IN ('FTE_ROUTING_GUIDE',
2933 'FTE_RATING',
2934 'FTE_LOAD_TENDER',
2935 'FTE_MLS_WRAPPER') THEN
2936
2937 IF p_trip_rec.rank_id <> FND_API.G_MISS_NUM
2938 OR p_trip_rec.rank_id IS NULL THEN
2939 x_trip_rec.rank_id := p_trip_rec.rank_id;
2940 END IF;
2941
2942 END IF;
2943
2944 IF p_in_rec.caller IN ('FTE_ROUTING_GUIDE',
2945 'FTE_RATING',
2946 'FTE_LOAD_TENDER') THEN
2947
2948 IF p_trip_rec.routing_rule_id <> FND_API.G_MISS_NUM
2949 OR p_trip_rec.routing_rule_id IS NULL THEN
2950 x_trip_rec.routing_rule_id := p_trip_rec.routing_rule_id;
2951 END IF;
2952
2953 IF p_trip_rec.append_flag <> FND_API.G_MISS_CHAR
2954 OR p_trip_rec.append_flag IS NULL THEN
2955 x_trip_rec.append_flag := p_trip_rec.append_flag;
2956 END IF;
2957
2958 END IF;
2959
2960 IF p_trip_rec.consignee_carrier_ac_no <> FND_API.G_MISS_CHAR
2961 OR p_trip_rec.consignee_carrier_ac_no IS NULL THEN
2962 x_trip_rec.consignee_carrier_ac_no := p_trip_rec.consignee_carrier_ac_no;
2963 END IF;
2964
2965 IF p_trip_rec.carrier_reference_number <> FND_API.G_MISS_CHAR
2966 OR p_trip_rec.carrier_reference_number IS NULL THEN
2967 x_trip_rec.carrier_reference_number := p_trip_rec.carrier_reference_number;
2968 END IF;
2969
2970 IF p_trip_rec.seal_code <> FND_API.G_MISS_CHAR
2971 OR p_trip_rec.seal_code IS NULL THEN
2972 x_trip_rec.seal_code := p_trip_rec.seal_code;
2973 END IF;
2974
2975 EXCEPTION
2976 -- OTM 12, glog proj, no debug or x_return_status variable here
2977 WHEN OTHERS THEN
2978 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.ELIMINATE_DISPLAYONLY_FIELDS');
2979
2980 END eliminate_displayonly_fields;
2981
2982 /*----------------------------------------------------------
2983 -- Procedure disable_from_list will update the record x_out_rec
2984 -- and disables the field contained in p_disabled_list.
2985 -----------------------------------------------------------*/
2986
2987 PROCEDURE disable_from_list(
2988 p_disabled_list IN WSH_UTIL_CORE.column_tab_type
2989 , p_in_rec IN WSH_TRIPS_PVT.trip_rec_type
2990 , x_out_rec IN OUT NOCOPY WSH_TRIPS_PVT.trip_rec_type
2991 , x_return_status OUT NOCOPY VARCHAR2
2992 , x_field_name OUT NOCOPY VARCHAR2
2993
2994 ) IS
2995 BEGIN
2996 FOR i IN 1..p_disabled_list.COUNT
2997 LOOP
2998 IF p_disabled_list(i) = 'ROUTING_INSTRUCTIONS' THEN
2999 x_out_rec.ROUTING_INSTRUCTIONS := p_in_rec.ROUTING_INSTRUCTIONS ;
3000 ELSIF p_disabled_list(i) = 'FREIGHT_CODE' THEN
3001 --x_out_rec.FREIGHT_TERMS_CODE := p_in_rec.FREIGHT_TERMS_CODE ;
3002 x_out_rec.carrier_id := p_in_rec.carrier_id; -- J-IB-NPARIKH--I-bug-fix
3003 ELSIF p_disabled_list(i) = 'SERVICE_LEVEL_NAME' THEN
3004 x_out_rec.SERVICE_LEVEL := p_in_rec.SERVICE_LEVEL ;
3005 ELSIF p_disabled_list(i) = 'MODE_OF_TRANSPORT_NAME' THEN
3006 x_out_rec.MODE_OF_TRANSPORT := p_in_rec.MODE_OF_TRANSPORT ;
3007 ELSIF p_disabled_list(i) = 'OPERATOR' THEN
3008 x_out_rec.OPERATOR := p_in_rec.OPERATOR ;
3009 ELSIF p_disabled_list(i) = 'DESC_FLEX' THEN
3010 x_out_rec.attribute1 := p_in_rec.attribute1 ;
3011 x_out_rec.attribute2 := p_in_rec.attribute2 ;
3012 x_out_rec.attribute3 := p_in_rec.attribute3 ;
3013 x_out_rec.attribute4 := p_in_rec.attribute4 ;
3014 x_out_rec.attribute5 := p_in_rec.attribute5 ;
3015 x_out_rec.attribute6 := p_in_rec.attribute6 ;
3016 x_out_rec.attribute7 := p_in_rec.attribute7 ;
3017 x_out_rec.attribute8 := p_in_rec.attribute8 ;
3018 x_out_rec.attribute9 := p_in_rec.attribute9 ;
3019 x_out_rec.attribute10 := p_in_rec.attribute10 ;
3020 x_out_rec.attribute11 := p_in_rec.attribute11 ;
3021 x_out_rec.attribute12 := p_in_rec.attribute12 ;
3022 x_out_rec.attribute13 := p_in_rec.attribute13 ;
3023 x_out_rec.attribute14 := p_in_rec.attribute14 ;
3024 x_out_rec.attribute15 := p_in_rec.attribute15 ;
3025 x_out_rec.attribute_category := p_in_rec.attribute_category ;
3026 ELSIF p_disabled_list(i) = 'CARRIER_REFERENCE_NUMBER' THEN
3027 x_out_rec.carrier_reference_number := p_in_rec.carrier_reference_number;
3028 ELSIF p_disabled_list(i) = 'CONSIGNEE_CARRIER_AC_NO' THEN
3029 x_out_rec.consignee_carrier_ac_no := p_in_rec.consignee_carrier_ac_no;
3030 ELSIF p_disabled_list(i) = 'FULL' THEN
3031 NULL;
3032 ELSE
3033 -- invalid name
3034 x_field_name := p_disabled_list(i);
3035 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3036 RETURN;
3037 --
3038 END IF;
3039 END LOOP;
3040 END disable_from_list;
3041 /*
3042 Procedure populate_external_efl is called from
3043 enable_from_list to populate the external value
3044 for a given internal field
3045 */
3046
3047 PROCEDURE populate_external_efl(
3048 p_internal IN VARCHAR2
3049 , p_external IN VARCHAR2
3050 , p_mode IN VARCHAR2
3051 , x_internal IN OUT NOCOPY VARCHAR2
3052 , x_external IN OUT NOCOPY VARCHAR2
3053 )
3054 IS
3055 BEGIN
3056
3057 IF p_mode = '+' THEN
3058 IF x_internal IS NULL THEN
3059 IF p_internal <> FND_API.G_MISS_CHAR OR p_internal IS NULL THEN
3060 x_internal := p_internal ;
3061 IF p_internal IS NULL THEN
3062 x_external := NULL;
3063 ELSE
3064 x_external := p_external;
3065 END IF;
3066 ELSIF p_external <> FND_API.G_MISS_CHAR OR p_external IS NULL THEN
3067 x_external := p_external;
3068 IF p_external IS NULL THEN
3069 x_internal := NULL;
3070 ELSE
3071 x_internal := p_internal;
3072 END IF;
3073 END IF;
3074 END IF;
3075 ELSE --p_mode <> +
3076 IF p_internal <> FND_API.G_MISS_CHAR OR p_internal IS NULL THEN
3077 x_internal := p_internal ;
3078 IF p_internal IS NULL THEN
3079 x_external := NULL;
3080 ELSE
3081 x_external := p_external;
3082 END IF;
3083 ELSIF p_external <> FND_API.G_MISS_CHAR OR p_external IS NULL THEN
3084 x_external := p_external;
3085 IF p_external IS NULL THEN
3086 x_internal := NULL;
3087 ELSE
3088 x_internal := p_internal;
3089 END IF;
3090 END IF;
3091 END IF;
3092
3093 END populate_external_efl;
3094
3095 /*
3096 Procedure populate_external_efl is called from
3097 enable_from_list to populate the external value
3098 for a given internal field
3099 */
3100
3101 PROCEDURE populate_external_efl(
3102 p_internal IN NUMBER
3103 , p_external IN VARCHAR2
3104 , p_mode IN VARCHAR2
3105 , x_internal IN OUT NOCOPY NUMBER
3106 , x_external IN OUT NOCOPY VARCHAR2
3107 )
3108 IS
3109 BEGIN
3110
3111 IF p_mode = '+' THEN
3112 IF x_internal IS NULL THEN
3113 IF p_internal <> FND_API.G_MISS_NUM OR p_internal IS NULL THEN
3114 x_internal := p_internal ;
3115 IF p_internal IS NULL THEN
3116 x_external := NULL;
3117 ELSE
3118 x_external := p_external;
3119 END IF;
3120 ELSIF p_external <> FND_API.G_MISS_CHAR OR p_external IS NULL THEN
3121 x_external := p_external;
3122 IF p_external IS NULL THEN
3123 x_internal := NULL;
3124 ELSE
3125 x_internal := p_internal;
3126 END IF;
3127 END IF;
3128 END IF;
3129 ELSE --p_mode <> +
3130 IF p_internal <> FND_API.G_MISS_NUM OR p_internal IS NULL THEN
3131 x_internal := p_internal ;
3132 IF p_internal IS NULL THEN
3133 x_external := NULL;
3134 ELSE
3135 x_external := p_external;
3136 END IF;
3137 ELSIF p_external <> FND_API.G_MISS_CHAR OR p_external IS NULL THEN
3138 x_external := p_external;
3139 IF p_external IS NULL THEN
3140 x_internal := NULL;
3141 ELSE
3142 x_internal := p_internal;
3143 END IF;
3144 END IF;
3145 END IF;
3146
3147 END populate_external_efl;
3148 /*----------------------------------------------------------
3149 -- Procedure enable_from_list will update the record x_out_rec for the fields
3150 -- included in p_disabled_list and will enable them
3151 -----------------------------------------------------------*/
3152
3153 PROCEDURE enable_from_list(
3154 p_disabled_list IN WSH_UTIL_CORE.column_tab_type
3155 , p_in_rec IN WSH_TRIPS_PVT.trip_rec_type
3156 , x_out_rec IN OUT NOCOPY WSH_TRIPS_PVT.trip_rec_type
3157 , x_return_status OUT NOCOPY VARCHAR2
3158 , x_field_name OUT NOCOPY VARCHAR2
3159
3160 ) IS
3161 BEGIN
3162 FOR i IN 2..p_disabled_list.COUNT
3163 LOOP
3164 IF p_disabled_list(i) = 'ROUTING_INSTRUCTIONS' THEN
3165 IF p_in_rec.ROUTING_INSTRUCTIONS <> FND_API.G_MISS_CHAR
3166 OR p_in_rec.ROUTING_INSTRUCTIONS IS NULL THEN
3167 x_out_rec.ROUTING_INSTRUCTIONS := p_in_rec.ROUTING_INSTRUCTIONS ;
3168 END IF;
3169 -- J-IB-NPARIKH-{ ---I-bug-fix
3170 ELSIF p_disabled_list(i) = 'FREIGHT_CODE' THEN
3171 IF p_in_rec.CARRIER_ID <> FND_API.G_MISS_NUM
3172 OR p_in_rec.CARRIER_ID IS NULL THEN
3173 x_out_rec.CARRIER_ID := p_in_rec.CARRIER_ID ;
3174 END IF;
3175 ELSIF p_disabled_list(i) = '+FREIGHT_CODE' THEN
3176 IF p_in_rec.CARRIER_ID <> FND_API.G_MISS_NUM
3177 OR p_in_rec.CARRIER_ID IS NULL THEN
3178 IF x_out_rec.CARRIER_ID IS NULL THEN
3179 x_out_rec.CARRIER_ID := p_in_rec.CARRIER_ID ;
3180 END IF;
3181 END IF;
3182 ELSIF p_disabled_list(i) = '+SHIP_METHOD_NAME' THEN
3183 populate_external_efl(p_in_rec.ship_method_code,
3184 p_in_rec.ship_method_name,
3185 '+',
3186 x_out_rec.ship_method_code,
3187 x_out_rec.ship_method_name);
3188 ELSIF p_disabled_list(i) = 'SHIP_METHOD_NAME' THEN
3189 populate_external_efl(p_in_rec.ship_method_code,
3190 p_in_rec.ship_method_name,
3191 NULL,
3192 x_out_rec.ship_method_code,
3193 x_out_rec.ship_method_name);
3194
3195 -- J-IB-NPARIKH-}
3196
3197 /*
3198 IF p_in_rec.FREIGHT_TERMS_CODE <> FND_API.G_MISS_CHAR
3199 OR p_in_rec.FREIGHT_TERMS_CODE IS NULL THEN
3200 x_out_rec.FREIGHT_TERMS_CODE := p_in_rec.FREIGHT_TERMS_CODE ;
3201 END IF;
3202 */
3203 ELSIF p_disabled_list(i) = 'SERVICE_LEVEL_NAME' THEN
3204 IF p_in_rec.SERVICE_LEVEL <> FND_API.G_MISS_CHAR
3205 OR p_in_rec.SERVICE_LEVEL IS NULL THEN
3206 x_out_rec.SERVICE_LEVEL := p_in_rec.SERVICE_LEVEL ;
3207 END IF;
3208 ELSIF p_disabled_list(i) = 'MODE_OF_TRANSPORT_NAME' THEN
3209 IF p_in_rec.MODE_OF_TRANSPORT <> FND_API.G_MISS_CHAR
3210 OR p_in_rec.MODE_OF_TRANSPORT IS NULL THEN
3211 x_out_rec.MODE_OF_TRANSPORT := p_in_rec.MODE_OF_TRANSPORT ;
3212 END IF;
3213 -- J-IB-NPARIKH-{
3214 ELSIF p_disabled_list(i) = '+SERVICE_LEVEL_NAME' THEN
3215 IF p_in_rec.SERVICE_LEVEL <> FND_API.G_MISS_CHAR
3216 OR p_in_rec.SERVICE_LEVEL IS NULL THEN
3217 IF x_out_rec.SERVICE_LEVEL IS NULL THEN
3218 x_out_rec.SERVICE_LEVEL := p_in_rec.SERVICE_LEVEL ;
3219 END IF;
3220 END IF;
3221 ELSIF p_disabled_list(i) = '+MODE_OF_TRANSPORT_NAME' THEN
3222 IF p_in_rec.MODE_OF_TRANSPORT <> FND_API.G_MISS_CHAR
3223 OR p_in_rec.MODE_OF_TRANSPORT IS NULL THEN
3224 IF x_out_rec.MODE_OF_TRANSPORT IS NULL THEN
3225 x_out_rec.MODE_OF_TRANSPORT := p_in_rec.MODE_OF_TRANSPORT ;
3226 END IF;
3227 END IF;
3228 ELSIF p_disabled_list(i) = '+LANE_ID' THEN
3229 IF p_in_rec.LANE_ID <> FND_API.G_MISS_NUM
3230 OR p_in_rec.LANE_ID IS NULL THEN
3231 IF x_out_rec.LANE_ID IS NULL THEN
3232 x_out_rec.LANE_ID := p_in_rec.LANE_ID ;
3233 END IF;
3234 END IF;
3235 ELSIF p_disabled_list(i) = 'VEHICLE_NUMBER' THEN
3236 IF p_in_rec.VEHICLE_NUMBER <> FND_API.G_MISS_CHAR
3237 OR p_in_rec.VEHICLE_NUMBER IS NULL THEN
3238 x_out_rec.VEHICLE_NUMBER := p_in_rec.VEHICLE_NUMBER ;
3239 END IF;
3240 ELSIF p_disabled_list(i) = 'VEHICLE_NUM_PREFIX' THEN
3241 IF p_in_rec.VEHICLE_NUM_PREFIX <> FND_API.G_MISS_CHAR
3242 OR p_in_rec.VEHICLE_NUM_PREFIX IS NULL THEN
3243 x_out_rec.VEHICLE_NUM_PREFIX := p_in_rec.VEHICLE_NUM_PREFIX ;
3244 END IF;
3245 -- OTM R12, glog project
3246 ELSIF p_disabled_list(i) = 'SEAL_CODE' THEN
3247 IF p_in_rec.SEAL_CODE <> FND_API.G_MISS_CHAR
3248 OR p_in_rec.SEAL_CODE IS NULL THEN
3249 x_out_rec.SEAL_CODE := p_in_rec.SEAL_CODE;
3250 END IF;
3251 ELSIF p_disabled_list(i) = 'NAME' THEN
3252 IF p_in_rec.NAME <> FND_API.G_MISS_CHAR
3253 OR p_in_rec.NAME IS NULL THEN
3254 x_out_rec.NAME := p_in_rec.NAME;
3255 END IF;
3256 -- OTM R12, end of glog project
3257 ELSIF p_disabled_list(i) = 'OPERATOR' THEN
3258 IF p_in_rec.OPERATOR <> FND_API.G_MISS_CHAR
3259 OR p_in_rec.OPERATOR IS NULL THEN
3260 x_out_rec.OPERATOR := p_in_rec.OPERATOR ;
3261 END IF;
3262 -- bug 3507047: Enable update of lane on firmed trip.
3263 ELSIF p_disabled_list(i) = 'LANE_ID' THEN
3264 IF p_in_rec.LANE_ID <> FND_API.G_MISS_NUM
3265 OR p_in_rec.LANE_ID IS NULL THEN
3266 x_out_rec.LANE_ID := p_in_rec.LANE_ID ;
3267 END IF;
3268 -- J-IB-NPARIKH-}
3269 ELSIF p_disabled_list(i) = 'DESC_FLEX' THEN
3270 IF p_in_rec.attribute1 <> FND_API.G_MISS_CHAR
3271 OR p_in_rec.attribute1 IS NULL THEN
3272 x_out_rec.attribute1 := p_in_rec.attribute1 ;
3273 END IF;
3274 IF p_in_rec.attribute2 <> FND_API.G_MISS_CHAR
3275 OR p_in_rec.attribute2 IS NULL THEN
3276 x_out_rec.attribute2 := p_in_rec.attribute2 ;
3277 END IF;
3278 IF p_in_rec.attribute3 <> FND_API.G_MISS_CHAR
3279 OR p_in_rec.attribute3 IS NULL THEN
3280 x_out_rec.attribute3 := p_in_rec.attribute3 ;
3281 END IF;
3282 IF p_in_rec.attribute4 <> FND_API.G_MISS_CHAR
3283 OR p_in_rec.attribute4 IS NULL THEN
3284 x_out_rec.attribute4 := p_in_rec.attribute4 ;
3285 END IF;
3286 IF p_in_rec.attribute5 <> FND_API.G_MISS_CHAR
3287 OR p_in_rec.attribute5 IS NULL THEN
3288 x_out_rec.attribute5 := p_in_rec.attribute5 ;
3289 END IF;
3290 IF p_in_rec.attribute6 <> FND_API.G_MISS_CHAR
3291 OR p_in_rec.attribute6 IS NULL THEN
3292 x_out_rec.attribute6 := p_in_rec.attribute6 ;
3293 END IF;
3294 IF p_in_rec.attribute7 <> FND_API.G_MISS_CHAR
3295 OR p_in_rec.attribute7 IS NULL THEN
3296 x_out_rec.attribute7 := p_in_rec.attribute7 ;
3297 END IF;
3298 IF p_in_rec.attribute8 <> FND_API.G_MISS_CHAR
3299 OR p_in_rec.attribute8 IS NULL THEN
3300 x_out_rec.attribute8 := p_in_rec.attribute8 ;
3301 END IF;
3302 IF p_in_rec.attribute9 <> FND_API.G_MISS_CHAR
3303 OR p_in_rec.attribute9 IS NULL THEN
3304 x_out_rec.attribute9 := p_in_rec.attribute9 ;
3305 END IF;
3306 IF p_in_rec.attribute10 <> FND_API.G_MISS_CHAR
3307 OR p_in_rec.attribute10 IS NULL THEN
3308 x_out_rec.attribute10 := p_in_rec.attribute10 ;
3309 END IF;
3310 IF p_in_rec.attribute11 <> FND_API.G_MISS_CHAR
3311 OR p_in_rec.attribute11 IS NULL THEN
3312 x_out_rec.attribute11 := p_in_rec.attribute11 ;
3313 END IF;
3314 IF p_in_rec.attribute12 <> FND_API.G_MISS_CHAR
3315 OR p_in_rec.attribute12 IS NULL THEN
3316 x_out_rec.attribute12 := p_in_rec.attribute12 ;
3317 END IF;
3318 IF p_in_rec.attribute13 <> FND_API.G_MISS_CHAR
3319 OR p_in_rec.attribute13 IS NULL THEN
3320 x_out_rec.attribute13 := p_in_rec.attribute13 ;
3321 END IF;
3322 IF p_in_rec.attribute14 <> FND_API.G_MISS_CHAR
3323 OR p_in_rec.attribute14 IS NULL THEN
3324 x_out_rec.attribute14 := p_in_rec.attribute14 ;
3325 END IF;
3326 IF p_in_rec.attribute15 <> FND_API.G_MISS_CHAR
3327 OR p_in_rec.attribute15 IS NULL THEN
3328 x_out_rec.attribute15 := p_in_rec.attribute15 ;
3329 END IF;
3330 IF p_in_rec.attribute_category <> FND_API.G_MISS_CHAR
3331 OR p_in_rec.attribute_category IS NULL THEN
3332 x_out_rec.attribute_category := p_in_rec.attribute_category ;
3333 END IF;
3334 --ELSIF p_disabled_list(i) = 'FULL' THEN
3335 --NULL;
3336 --bug 3257612 : load tender needs to update fields even if firm
3337 ELSIF p_disabled_list(i) = 'LOAD_TENDER_STATUS' THEN
3338 IF p_in_rec.LOAD_TENDER_STATUS <> FND_API.G_MISS_CHAR
3339 OR p_in_rec.LOAD_TENDER_STATUS IS NULL THEN
3340 x_out_rec.LOAD_TENDER_STATUS := p_in_rec.LOAD_TENDER_STATUS;
3341 END IF;
3342 ELSIF p_disabled_list(i) = 'WF_NAME' THEN
3343 IF p_in_rec.WF_NAME <> FND_API.G_MISS_CHAR
3344 OR p_in_rec.WF_NAME IS NULL THEN
3345 x_out_rec.WF_NAME := p_in_rec.WF_NAME;
3346 END IF;
3347 ELSIF p_disabled_list(i) = 'WF_PROCESS_NAME' THEN
3348 IF p_in_rec.WF_PROCESS_NAME <> FND_API.G_MISS_CHAR
3349 OR p_in_rec.WF_PROCESS_NAME IS NULL THEN
3350 x_out_rec.WF_PROCESS_NAME := p_in_rec.WF_PROCESS_NAME;
3351 END IF;
3352 ELSIF p_disabled_list(i) = 'WF_ITEM_KEY' THEN
3353 IF p_in_rec.WF_ITEM_KEY <> FND_API.G_MISS_CHAR
3354 OR p_in_rec.WF_ITEM_KEY IS NULL THEN
3355 x_out_rec.WF_ITEM_KEY := p_in_rec.WF_ITEM_KEY;
3356 END IF;
3357 ELSIF p_disabled_list(i) = 'CARRIER_CONTACT_ID' THEN
3358 IF p_in_rec.CARRIER_CONTACT_ID <> FND_API.G_MISS_NUM
3359 OR p_in_rec.CARRIER_CONTACT_ID IS NULL THEN
3360 x_out_rec.CARRIER_CONTACT_ID := p_in_rec.CARRIER_CONTACT_ID;
3361 END IF;
3362 ELSIF p_disabled_list(i) = 'SHIPPER_WAIT_TIME' THEN
3363 IF p_in_rec.SHIPPER_WAIT_TIME <> FND_API.G_MISS_NUM
3364 OR p_in_rec.SHIPPER_WAIT_TIME IS NULL THEN
3365 x_out_rec.SHIPPER_WAIT_TIME := p_in_rec.SHIPPER_WAIT_TIME ;
3366 END IF;
3367 ELSIF p_disabled_list(i) = 'WAIT_TIME_UOM' THEN
3368 IF p_in_rec.WAIT_TIME_UOM <> FND_API.G_MISS_CHAR
3369 OR p_in_rec.WAIT_TIME_UOM IS NULL THEN
3370 x_out_rec.WAIT_TIME_UOM := p_in_rec.WAIT_TIME_UOM ;
3371 END IF;
3372 ELSIF p_disabled_list(i) = 'LOAD_TENDERED_TIME' THEN
3373 IF p_in_rec.LOAD_TENDERED_TIME <> FND_API.G_MISS_DATE
3374 OR p_in_rec.LOAD_TENDERED_TIME IS NULL THEN
3375 x_out_rec.LOAD_TENDERED_TIME := p_in_rec.LOAD_TENDERED_TIME;
3376 END IF;
3377 ELSIF p_disabled_list(i) = 'CARRIER_RESPONSE' THEN
3378 IF p_in_rec.CARRIER_RESPONSE <> FND_API.G_MISS_CHAR
3379 OR p_in_rec.CARRIER_RESPONSE IS NULL THEN
3380 x_out_rec.CARRIER_RESPONSE := p_in_rec.CARRIER_RESPONSE;
3381 END IF;
3382 --Bug 3309150 {
3383 ELSIF p_disabled_list(i) = 'VEHICLE_ORGANIZATION_CODE' THEN
3384 populate_external_efl(p_in_rec.VEHICLE_ORGANIZATION_ID,
3385 p_in_rec.VEHICLE_ORGANIZATION_CODE,
3386 NULL,
3387 x_out_rec.VEHICLE_ORGANIZATION_ID,
3388 x_out_rec.VEHICLE_ORGANIZATION_CODE);
3389
3390
3391 --Bug 3599626: If veh. org is enabled, enable the veh. item as well {
3392 IF x_out_rec.VEHICLE_ORGANIZATION_ID IS NOT NULL THEN
3393 IF p_in_rec.VEHICLE_ITEM_ID <> FND_API.G_MISS_NUM
3394 OR p_in_rec.VEHICLE_ITEM_ID IS NULL THEN
3395 x_out_rec.VEHICLE_ITEM_ID := p_in_rec.VEHICLE_ITEM_ID;
3396 END IF;
3397 END IF;
3398
3399 --}
3400 --Bug 3599626 {
3401 ELSIF p_disabled_list(i) = 'VEHICLE_ITEM_NAME' THEN
3402 -- This is already handled when enabling 'VEHICLE_ORGANIZATION_CODE'
3403 -- but we do not want to raise the below error.
3404 NULL;
3405 --}
3406 ELSIF p_disabled_list(i) = 'CARRIER_REFERENCE_NUMBER' THEN
3407 IF p_in_rec.CARRIER_REFERENCE_NUMBER <> FND_API.G_MISS_CHAR
3408 OR p_in_rec.CARRIER_REFERENCE_NUMBER IS NULL THEN
3409 x_out_rec.CARRIER_REFERENCE_NUMBER := p_in_rec.CARRIER_REFERENCE_NUMBER;
3410 END IF;
3411 ELSIF p_disabled_list(i) = '+CARRIER_REFERENCE_NUMBER' THEN
3412 IF p_in_rec.CARRIER_REFERENCE_NUMBER <> FND_API.G_MISS_CHAR
3413 OR p_in_rec.CARRIER_REFERENCE_NUMBER IS NULL THEN
3414 IF x_out_rec.CARRIER_REFERENCE_NUMBER IS NULL THEN
3415 x_out_rec.CARRIER_REFERENCE_NUMBER := p_in_rec.CARRIER_REFERENCE_NUMBER;
3416 END IF;
3417 END IF;
3418 ELSIF p_disabled_list(i) = 'CONSIGNEE_CARRIER_AC_NO' THEN
3419 IF p_in_rec.CONSIGNEE_CARRIER_AC_NO <> FND_API.G_MISS_CHAR
3420 OR p_in_rec.CONSIGNEE_CARRIER_AC_NO IS NULL THEN
3421 x_out_rec.CONSIGNEE_CARRIER_AC_NO := p_in_rec.CONSIGNEE_CARRIER_AC_NO;
3422 END IF;
3423 ELSIF p_disabled_list(i) = '+CONSIGNEE_CARRIER_AC_NO' THEN
3424 IF p_in_rec.CONSIGNEE_CARRIER_AC_NO <> FND_API.G_MISS_CHAR
3425 OR p_in_rec.CONSIGNEE_CARRIER_AC_NO IS NULL THEN
3426 IF x_out_rec.CONSIGNEE_CARRIER_AC_NO IS NULL THEN
3427 x_out_rec.CONSIGNEE_CARRIER_AC_NO := p_in_rec.CONSIGNEE_CARRIER_AC_NO;
3428 END IF;
3429 END IF;
3430 ELSIF p_disabled_list(i) IN ('+FREIGHT_TERMS_CODE', '+FREIGHT_TERMS_NAME') THEN
3431 IF p_in_rec.FREIGHT_TERMS_CODE <> FND_API.G_MISS_CHAR
3432 OR p_in_rec.FREIGHT_TERMS_CODE IS NULL THEN
3433 IF x_out_rec.FREIGHT_TERMS_CODE IS NULL THEN
3434 x_out_rec.FREIGHT_TERMS_CODE := p_in_rec.FREIGHT_TERMS_CODE;
3435 END IF;
3436 END IF;
3437 ELSE
3438 -- invalid name
3439 x_field_name := p_disabled_list(i);
3440 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3441 RETURN;
3442 --
3443 END IF;
3444 END LOOP;
3445
3446 EXCEPTION
3447 -- OTM R12, glog proj, add when Others exception handler
3448 WHEN others THEN
3449 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.ENABLE_FROM_LIST');
3450 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3451
3452 END enable_from_list;
3453
3454 --
3455 -- Overloaded procedure
3456 -- Bug 2678363: Added new parameter p_in_rec, in place of p_action
3457 --
3458 PROCEDURE Get_Disabled_List (
3459 p_trip_rec IN WSH_TRIPS_PVT.trip_rec_type
3460 , p_in_rec IN WSH_TRIPS_GRP.TripInRecType
3461 , x_return_status OUT NOCOPY VARCHAR2
3462 , x_msg_count OUT NOCOPY NUMBER
3463 , x_msg_data OUT NOCOPY VARCHAR2
3464 , x_trip_rec OUT NOCOPY WSH_TRIPS_PVT.trip_rec_type
3465 )
3466 IS
3467 l_disabled_list WSH_UTIL_CORE.column_tab_type;
3468 l_db_col_rec WSH_TRIPS_PVT.trip_rec_type;
3469 l_return_status VARCHAR2(30);
3470 l_field_name VARCHAR2(100);
3471 l_debug_on BOOLEAN;
3472 l_module_name CONSTANT VARCHAR2(100) :=
3473 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DISABLED_LIST';
3474
3475
3476 e_dp_no_entity EXCEPTION;
3477 e_bad_field EXCEPTION;
3478 e_all_disabled EXCEPTION ;
3479
3480 l_caller VARCHAR2(32767);
3481 --
3482 i number;
3483
3484 BEGIN
3485 --
3486 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3487 --
3488 IF l_debug_on IS NULL
3489 THEN
3490 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3491 END IF;
3492 --
3493 IF l_debug_on THEN
3494 --
3495 WSH_DEBUG_SV.push(l_module_name);
3496 WSH_DEBUG_SV.log(l_module_name,'trip_id',p_trip_rec.trip_id);
3497 WSH_DEBUG_SV.log(l_module_name,'Action', p_in_rec.action_code);
3498 WSH_DEBUG_SV.log(l_module_name,'Caller', p_in_rec.caller);
3499 --
3500 END IF;
3501 --
3502 x_return_status := FND_API.G_RET_STS_SUCCESS;
3503 --
3504 IF p_in_rec.action_code = 'CREATE' THEN
3505 --
3506 IF l_debug_on THEN
3507 WSH_DEBUG_SV.log(l_module_name,'calling eliminate_displayonly_fields');
3508 END IF;
3509 --
3510 -- nothing else need to be disabled
3511 --
3512 eliminate_displayonly_fields (p_trip_rec,p_in_rec,x_trip_rec);
3513 --
3514 --3509004:public api changes
3515 /*
3516 IF l_debug_on THEN
3517 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
3518 WSH_DEBUG_SV.pop(l_module_name);
3519 END IF;
3520 -- RETURN;
3521 --
3522 */
3523 ELSIF p_in_rec.action_code = 'UPDATE' THEN
3524 --
3525 l_caller := p_in_rec.caller;
3526 IF (l_caller like 'FTE%') THEN
3527 l_caller := 'WSH_PUB';
3528 END IF;
3529 Get_Disabled_List( p_trip_rec.trip_id
3530 , 'FORM'
3531 , x_return_status
3532 , l_disabled_list
3533 , x_msg_count
3534 , x_msg_data
3535 , l_caller --3509004:public api changes
3536 );
3537 --
3538 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR OR
3539 x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
3540 THEN
3541 --
3542 IF l_debug_on THEN
3543 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
3544 WSH_DEBUG_SV.pop(l_module_name);
3545 END IF;
3546 RETURN;
3547 --
3548 END IF;
3549 --
3550 IF l_disabled_list.COUNT = 1 THEN
3551 IF l_disabled_list(1) = 'FULL' THEN
3552 RAISE e_all_disabled;
3553 --Everything is disabled
3554 END IF;
3555 END IF;
3556 --
3557
3558 WSH_TRIPS_PVT.populate_record(
3559 p_trip_id => p_trip_rec.trip_id,
3560 x_trip_info => x_trip_rec,
3561 x_return_status => x_return_status);
3562
3563 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
3564 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
3565 RAISE e_dp_no_entity;
3566 END IF;
3567
3568 --
3569 -- bug 3398603: TP Release needs to always update these fields
3570 -- even if the trip is firmed.
3571
3572 -- OTM R12, glog project, allow GC3 Inbound Message to update tp_plan_name
3573 -- no change required for CREATE, caller = FTE_TMS_INTEGRATION
3574 IF p_in_rec.caller IN ('WSH_TP_RELEASE','FTE_TMS_INTEGRATION') THEN--{
3575 IF p_trip_rec.tp_plan_name <> FND_API.G_MISS_CHAR
3576 OR p_trip_rec.tp_plan_name IS NULL THEN
3577 x_trip_rec.tp_plan_name := p_trip_rec.tp_plan_name;
3578 END IF;
3579 IF p_trip_rec.tp_trip_number <> FND_API.G_MISS_NUM
3580 OR p_trip_rec.tp_trip_number IS NULL THEN
3581 x_trip_rec.tp_trip_number := p_trip_rec.tp_trip_number;
3582 END IF;
3583 END IF;--}
3584 -- OTM R12, glog proj,GC3 inbound message can also update Mode, Service, Carrier,Freight
3585 -- Ship Method Code, Vehicle Item id + Organization
3586 IF p_in_rec.caller = 'FTE_TMS_INTEGRATION' THEN --{
3587 IF p_trip_rec.mode_of_transport <> FND_API.G_MISS_CHAR
3588 OR p_trip_rec.mode_of_transport IS NULL THEN
3589 x_trip_rec.mode_of_transport := p_trip_rec.mode_of_transport;
3590 END IF;
3591 IF p_trip_rec.service_level <> FND_API.G_MISS_CHAR
3592 OR p_trip_rec.service_level IS NULL THEN
3593 x_trip_rec.service_level := p_trip_rec.service_level;
3594 END IF;
3595 IF p_trip_rec.carrier_id <> FND_API.G_MISS_NUM
3596 OR p_trip_rec.carrier_id IS NULL THEN
3597 x_trip_rec.carrier_id := p_trip_rec.carrier_id;
3598 END IF;
3599 IF p_trip_rec.freight_terms_code <> FND_API.G_MISS_CHAR
3600 OR p_trip_rec.freight_terms_code IS NULL THEN
3601 x_trip_rec.freight_terms_code := p_trip_rec.freight_terms_code;
3602 END IF;
3603 IF p_trip_rec.ship_method_code <> FND_API.G_MISS_CHAR
3604 OR p_trip_rec.ship_method_code IS NULL THEN
3605 x_trip_rec.ship_method_code := p_trip_rec.ship_method_code;
3606 END IF;
3607 IF p_trip_rec.vehicle_item_id <> FND_API.G_MISS_NUM
3608 OR p_trip_rec.vehicle_item_id IS NULL THEN
3609 x_trip_rec.vehicle_item_id := p_trip_rec.vehicle_item_id;
3610 END IF;
3611 IF p_trip_rec.vehicle_organization_id <> FND_API.G_MISS_NUM
3612 OR p_trip_rec.vehicle_organization_id IS NULL THEN
3613 x_trip_rec.vehicle_organization_id := p_trip_rec.vehicle_organization_id;
3614 END IF;
3615 END IF;--}
3616
3617 -- End of code added for OTM R12, glog proj
3618 --
3619
3620 --
3621 --
3622 IF l_debug_on THEN
3623 WSH_DEBUG_SV.log(l_module_name,'list.COUNT',l_disabled_list.COUNT);
3624 END IF;
3625 --
3626 IF l_disabled_list.COUNT = 0 THEN
3627 --
3628 IF l_debug_on THEN
3629 WSH_DEBUG_SV.log(l_module_name,'calling eliminate_displayonly_fields');
3630 END IF;
3631 --
3632 -- nothing else need to be disabled
3633 --
3634 eliminate_displayonly_fields (p_trip_rec,p_in_rec,x_trip_rec);
3635
3636 ELSIF l_disabled_list(1) = 'FULL' THEN
3637 --
3638 IF l_disabled_list.COUNT > 1 THEN
3639 --
3640 IF l_debug_on THEN
3641 FOR i in 1..l_disabled_list.COUNT
3642 LOOP
3643 WSH_DEBUG_SV.log(l_module_name,'list values',l_disabled_list(i));
3644 END LOOP;
3645 WSH_DEBUG_SV.log(l_module_name,'calling enable_from_list');
3646 END IF;
3647 --enable the columns matching the l_disabled_list
3648 enable_from_list(l_disabled_list,
3649 p_trip_rec,
3650 x_trip_rec,
3651 l_return_status,
3652 l_field_name);
3653 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
3654 RAISE e_bad_field;
3655 END IF;
3656 --
3657 END IF;
3658 --
3659 ELSE -- list.count > 1 and list(1) <> 'FULL'
3660 --
3661 l_db_col_rec := x_trip_rec ;
3662 --
3663 IF l_debug_on THEN
3664 FOR i in 1..l_disabled_list.COUNT
3665 LOOP
3666 WSH_DEBUG_SV.log(l_module_name,'list values',l_disabled_list(i));
3667 END LOOP;
3668 WSH_DEBUG_SV.log(l_module_name,'First element is not FULL');
3669 WSH_DEBUG_SV.log(l_module_name,'calling eliminate_displayonly_fields');
3670 END IF;
3671 --
3672 eliminate_displayonly_fields (p_trip_rec,p_in_rec,x_trip_rec);
3673 --
3674 IF l_debug_on THEN
3675 WSH_DEBUG_SV.log(l_module_name,'calling disable_from_list');
3676 END IF;
3677 -- The fileds in the list are getting disabled
3678 disable_from_list(l_disabled_list,
3679 l_db_col_rec,
3680 x_trip_rec,
3681 l_return_status,
3682 l_field_name
3683 );
3684 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
3685 RAISE e_bad_field;
3686 END IF;
3687 --
3688 END IF;
3689 --
3690 END IF; /* if action = 'UPDATE' */
3691 --3509004:public api changes
3692 IF (nvl(p_in_rec.caller,'''') <> 'WSH_FSTRX' AND
3693 nvl(p_in_rec.caller,'''') NOT LIKE 'FTE%') THEN
3694 --
3695 user_non_updatable_columns
3696 (p_user_in_rec => p_trip_rec,
3697 p_out_rec => x_trip_rec,
3698 p_in_rec => p_in_rec,
3699 x_return_status => l_return_status);
3700 --
3701 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
3702 x_return_status := l_return_status;
3703 END IF;
3704 --
3705 END IF;
3706 --
3707 IF l_debug_on THEN
3708 WSH_DEBUG_SV.pop(l_module_name);
3709 END IF;
3710 --
3711 EXCEPTION
3712 WHEN e_all_disabled THEN
3713 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3714 FND_MESSAGE.SET_NAME('WSH','WSH_ALL_COLS_DISABLED');
3715 FND_MESSAGE.Set_Token('ENTITY_ID',p_trip_rec.trip_id);
3716 wsh_util_core.add_message(x_return_status,l_module_name);
3717 IF l_debug_on THEN
3718 -- Nothing is updateable
3719 WSH_DEBUG_SV.pop(l_module_name,'e_all_disabled');
3720 END IF;
3721 WHEN e_dp_no_entity THEN
3722 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3723 -- the message for this is set in original get_disabled_list
3724 IF l_debug_on THEN
3725 WSH_DEBUG_SV.pop(l_module_name,'e_dp_no_entity');
3726 END IF;
3727 WHEN e_bad_field THEN
3728 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3729 FND_MESSAGE.SET_NAME('WSH','WSH_BAD_FIELD_NAME');
3730 FND_MESSAGE.Set_Token('FIELD_NAME',l_field_name);
3731 wsh_util_core.add_message(x_return_status,l_module_name);
3732 IF l_debug_on THEN
3733 WSH_DEBUG_SV.log(l_module_name,'Bad field name passed to the list:'
3734 ,l_field_name);
3735 WSH_DEBUG_SV.pop(l_module_name,'e_bad_field');
3736 END IF;
3737
3738 WHEN OTHERS THEN
3739 wsh_util_core.default_handler('WSH_DETAILS_VALIDATIONS.get_disabled_list'
3740 ,l_module_name);
3741 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3742 IF l_debug_on THEN
3743 WSH_DEBUG_SV.log(l_module_name,'Error:',SUBSTR(SQLERRM,1,200));
3744 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3745 END IF;
3746 END Get_Disabled_List;
3747
3748
3749
3750 PROCEDURE Init_Trip_Actions_Tbl (
3751 p_action IN VARCHAR2
3752 , x_Trip_actions_tab OUT NOCOPY TripActionsTabType
3753 , x_return_status OUT NOCOPY VARCHAR2
3754 )
3755
3756 IS
3757 l_debug_on BOOLEAN;
3758 l_module_name CONSTANT VARCHAR2(100) :=
3759 'wsh.plsql.' || G_PKG_NAME || '.' || 'Init_Trip_Actions_Tbl';
3760 i NUMBER := 0;
3761
3762 l_gc3_is_installed VARCHAR(1); --OTM R12, glog proj
3763
3764 BEGIN
3765 --
3766 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3767 --
3768 IF l_debug_on IS NULL
3769 THEN
3770 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3771 END IF;
3772
3773 --OTM R12, glog proj, use Global Variable
3774 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
3775
3776 -- If null, call the function
3777 IF l_gc3_is_installed IS NULL THEN
3778 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
3779 END IF;
3780 -- end of OTM R12, glog proj
3781
3782
3783 --
3784 -- J-IB-NPARIKH-{
3785 --
3786 -- Disable all actions on inbound stops when called from transactions form
3787 --
3788 i := i+1;
3789 x_Trip_actions_tab(i).shipments_type_flag := 'I';
3790 x_Trip_actions_tab(i).caller := 'WSH_FSTRX';
3791 x_Trip_actions_tab(i).action_not_allowed := p_action;
3792 -- J-IB-NPARIKH-}
3793 --
3794 IF p_action = 'TRIP-CONFIRM' THEN
3795
3796 i := i + 1;
3797 x_Trip_actions_tab(i).action_not_allowed := 'TRIP-CONFIRM';
3798 x_Trip_actions_tab(i).status_code := 'IT';
3799 i := i + 1;
3800 x_Trip_actions_tab(i).action_not_allowed := 'TRIP-CONFIRM';
3801 x_Trip_actions_tab(i).status_code := 'CL';
3802 i := i + 1;
3803 x_Trip_actions_tab(i).action_not_allowed := 'TRIP-CONFIRM';
3804 x_Trip_actions_tab(i).shipments_type_flag := 'I';
3805 END IF;
3806
3807 IF p_action = 'WT-VOL' THEN
3808 --
3809 -- Calculate weight/volume action is
3810 -- - always allowed for inbound trip
3811 -- - not allowed for outbound trip, once closed.
3812 -- - not allowed for mixed closed trip, if called from transactions form
3813 --
3814 i := i + 1;
3815 x_Trip_actions_tab(i).action_not_allowed := 'WT-VOL';
3816 x_Trip_actions_tab(i).shipments_type_flag := 'O'; -- J-IB-NPARIKH
3817 x_Trip_actions_tab(i).status_code := 'CL';
3818 -- J-IB-NPARIKH-{
3819 i := i + 1;
3820 x_Trip_actions_tab(i).action_not_allowed := 'WT-VOL';
3821 x_Trip_actions_tab(i).shipments_type_flag := 'M';
3822 x_Trip_actions_tab(i).caller := 'WSH_FSTRX';
3823 x_Trip_actions_tab(i).status_code := 'CL';
3824 -- J-IB-NPARIKH-}
3825
3826 END IF;
3827
3828 IF p_action = 'PICK-RELEASE-UI' THEN
3829 i := i + 1;
3830 x_Trip_actions_tab(i).action_not_allowed := 'PICK-RELEASE-UI';
3831 x_Trip_actions_tab(i).status_code := 'CL';
3832 i := i + 1;
3833 x_Trip_actions_tab(i).action_not_allowed := 'PICK-RELEASE-UI';
3834 x_Trip_actions_tab(i).shipments_type_flag := 'I';
3835
3836 --HVOP heali
3837 i := i + 1;
3838 x_Trip_actions_tab(i).action_not_allowed := 'PICK-RELEASE-UI';
3839 x_Trip_actions_tab(i).status_code := 'IT';
3840 --HVOP heali
3841 END IF;
3842 IF p_action = 'PICK-RELEASE' THEN
3843 i := i + 1;
3844 x_Trip_actions_tab(i).action_not_allowed := 'PICK-RELEASE';
3845 x_Trip_actions_tab(i).shipments_type_flag := 'I';
3846 --HVOP heali
3847 i := i + 1;
3848 x_Trip_actions_tab(i).action_not_allowed := 'PICK-RELEASE';
3849 x_Trip_actions_tab(i).status_code := 'IT';
3850 --HVOP heali
3851 END IF;
3852 IF p_action = 'ASSIGN-FREIGHT-COSTS' THEN
3853 i := i + 1;
3854 x_Trip_actions_tab(i).action_not_allowed := 'ASSIGN-FREIGHT-COSTS';
3855 x_Trip_actions_tab(i).shipments_type_flag := 'I';
3856 END IF;
3857 IF p_action = 'PRINT-DOC-SET' THEN
3858 i := i + 1;
3859 x_Trip_actions_tab(i).action_not_allowed := 'PRINT-DOC-SET';
3860 x_Trip_actions_tab(i).shipments_type_flag := 'I';
3861 END IF;
3862
3863 IF p_action = 'SELECT-CARRIER' THEN
3864
3865 i := i + 1;
3866 x_Trip_actions_tab(i).action_not_allowed := 'SELECT-CARRIER';
3867 x_Trip_actions_tab(i).status_code := 'IT';
3868 x_Trip_actions_tab(i).message_name := 'WSH_FTE_SEL_TRIP_STATUS';
3869 i := i + 1;
3870 x_Trip_actions_tab(i).action_not_allowed := 'SELECT-CARRIER';
3871 x_Trip_actions_tab(i).status_code := 'CL';
3872 x_Trip_actions_tab(i).message_name := 'WSH_FTE_SEL_TRIP_STATUS';
3873 i := i + 1;
3874 x_Trip_actions_tab(i).action_not_allowed := 'SELECT-CARRIER';
3875 x_Trip_actions_tab(i).planned_flag := 'Y';
3876 x_Trip_actions_tab(i).message_name := 'WSH_FTE_SEL_TRIP_PLANNED';
3877 i := i + 1;
3878 x_Trip_actions_tab(i).action_not_allowed := 'SELECT-CARRIER';
3879 x_Trip_actions_tab(i).planned_flag := 'F';
3880 x_Trip_actions_tab(i).message_name := 'WSH_FTE_SEL_TRIP_PLANNED';
3881 i := i + 1;
3882 x_Trip_actions_tab(i).action_not_allowed := 'SELECT-CARRIER';
3883 x_Trip_actions_tab(i).load_tender_status := 'TENDERED';
3884 x_Trip_actions_tab(i).message_name := 'WSH_FTE_SEL_TRIP_LT_STATUS';
3885 i := i + 1;
3886 x_Trip_actions_tab(i).action_not_allowed := 'SELECT-CARRIER';
3887 x_Trip_actions_tab(i).load_tender_status := 'ACCEPTED';
3888 x_Trip_actions_tab(i).message_name := 'WSH_FTE_SEL_TRIP_LT_STATUS';
3889 i := i + 1;
3890 x_Trip_actions_tab(i).action_not_allowed := 'SELECT-CARRIER';
3891 x_Trip_actions_tab(i).load_tender_status := 'AUTO_ACCEPTED';
3892 x_Trip_actions_tab(i).message_name := 'WSH_FTE_SEL_TRIP_LT_STATUS';
3893
3894 END IF;
3895
3896 --OTM R12, glog proj
3897 IF (l_gc3_is_installed = 'Y') THEN
3898
3899 -- Disable Include/Ignore actions if OTM is installed
3900 IF p_action IN ('IGNORE_PLAN', 'INCLUDE_PLAN') THEN
3901 i := i + 1;
3902 x_trip_actions_tab(i).action_not_allowed := p_action;
3903 END IF;
3904
3905 -- Disable Routing Firm, Routing and Contents Firm, Unfirm for
3906 -- include for planning trips only
3907 IF p_action IN ('FIRM', 'PLAN', 'UNPLAN') THEN
3908 i := i + 1;
3909 x_trip_actions_tab(i).action_not_allowed := p_action;
3910 x_trip_actions_tab(i).ignore_for_planning := 'N';
3911 END IF;
3912
3913 END IF;
3914
3915 -- bug 5837425
3916 IF p_action IN ('IGNORE_PLAN', 'INCLUDE_PLAN') THEN
3917 i := i + 1;
3918 x_trip_actions_tab(i).status_code := 'IT';
3919 x_trip_actions_tab(i).action_not_allowed := p_action;
3920 i := i + 1;
3921 x_trip_actions_tab(i).status_code := 'CL';
3922 x_trip_actions_tab(i).action_not_allowed := p_action;
3923 END IF;
3924 -- bug 5837425
3925
3926 --
3927 IF l_debug_on THEN
3928 WSH_DEBUG_SV.push(l_module_name);
3929 --
3930 WSH_DEBUG_SV.log(l_module_name,'p_action', p_action);
3931 END IF;
3932 --
3933 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3934
3935 IF l_debug_on THEN
3936 WSH_DEBUG_SV.pop(l_module_name);
3937 END IF;
3938
3939 EXCEPTION
3940 -- OTM R12, glog proj
3941 WHEN OTHERS THEN
3942 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_TRIP_VALIDATIONS.init_trip_actions_tbl'
3943 ,l_module_name);
3944 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3945 IF l_debug_on THEN
3946 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3947 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3948 END IF;
3949
3950
3951 END Init_Trip_Actions_Tbl;
3952
3953 -- for Load Tender Project
3954 /*
3955 -----------------------------------------------------------------------------
3956 PROCEDURE : Get Trip Calc Wtvol
3957 PARAMETERS : p_tab_id - entity id
3958 p_entity - entity name -DELIVERY,TRIP,TRIP_STOP,DELIVERY_DETAIL
3959 p_action_code - action code for each action
3960 p_phase - 1 for Before the action is performed, 2 for after.
3961 x_trip_id_tab - Table of Trip ids
3962 x_return_status - Return Status
3963 DESCRIPTION : This procedure finds the trip for each entity on the basis
3964 of p_entity.After the trip is determined, calculate the
3965 weight/volume for the trip.
3966 ------------------------------------------------------------------------------
3967 */
3968 -- THIS PROCEDURE IS OBSOLETE
3969 PROCEDURE Get_Trip_Calc_Wtvol
3970 (p_tab_id IN wsh_util_core.id_tab_type,
3971 p_entity IN VARCHAR2,
3972 p_action_code IN VARCHAR2,
3973 p_phase IN NUMBER,
3974 x_trip_id_tab IN OUT NOCOPY wsh_util_core.id_tab_type,
3975 x_return_status OUT NOCOPY VARCHAR2
3976 ) IS
3977
3978 BEGIN
3979
3980 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3981
3982 END get_trip_calc_wtvol;
3983
3984 -- End for Load Tender Project
3985 --
3986 -- J-IB-NPARIKH-{
3987 --
3988 --========================================================================
3989 -- PROCEDURE : Check_close
3990 --
3991 -- PARAMETERS: p_in_rec Input Record (Refer to WSHTRVLS.pls for description)
3992 -- x_allowed Trip can be closed or not
3993 -- 'Y' : Allowed
3994 -- 'YW': Allowed with warnings
3995 -- 'N' : Not Allowed
3996 -- 'NW': Not Allowed with warnings
3997 -- x_return_status Return status of API
3998 --
3999 --
4000 -- COMMENT : This function checks if trip can be closed or not.
4001 --
4002 -- It performs following validations:
4003 -- 01. Check if trip has any stops which is not closed. IF so, trip close is not
4004 -- allowed else it is allowed.
4005 -- 02. Check for exceptions logged against trip and its contents
4006 --========================================================================
4007 --
4008 PROCEDURE check_Close
4009 (
4010 p_in_rec IN ChgStatus_in_rec_type,
4011 x_return_status OUT NOCOPY VARCHAR2,
4012 x_allowed OUT NOCOPY VARCHAR2
4013 )
4014 IS
4015 --{
4016 --
4017 -- Check if trip has any stop which is not closed yet.
4018 --
4019 -- When linked_stop_id is passed, that stop
4020 -- will also get closed.
4021 --
4022 CURSOR any_open_stop (p_trip_id NUMBER,
4023 p_stop_id NUMBER,
4024 p_linked_stop_id NUMBER) IS
4025 SELECT stop_id
4026 FROM wsh_trip_stops
4027 WHERE trip_id = p_trip_id
4028 AND stop_id <> NVL(p_stop_id,-9999)
4029 AND stop_id <> NVL(p_linked_stop_id,-9999)
4030 AND status_code <> 'CL';
4031 --
4032 l_num_warnings NUMBER;
4033 l_num_errors NUMBER;
4034 l_dummy NUMBER;
4035 --
4036 -- Exception variables
4037 l_exceptions_tab wsh_xc_util.XC_TAB_TYPE;
4038 l_exp_logged BOOLEAN := FALSE;
4039 l_exp_warning BOOLEAN := FALSE;
4040 l_msg_count NUMBER;
4041 l_msg_data VARCHAR2(4000);
4042 l_return_status VARCHAR2(1);
4043 -- Following three variables are added for BugFix #3947506
4044 l_out_entity_id VARCHAR2(100);
4045 l_out_entity_name VARCHAR2(100);
4046 l_out_status VARCHAR2(100);
4047 --
4048 l_debug_on BOOLEAN;
4049 --
4050 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || g_pkg_name || '.' || 'check_Close';
4051 --}
4052 BEGIN
4053 --{
4054 --
4055 l_debug_on := wsh_debug_interface.g_debug;
4056 --
4057 IF l_debug_on IS NULL THEN
4058 l_debug_on := wsh_debug_sv.is_debug_enabled;
4059 END IF;
4060 --
4061 IF l_debug_on THEN
4062 wsh_debug_sv.push(l_module_name);
4063 --
4064 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.trip_id ', p_in_rec.trip_id );
4065 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.name ', p_in_rec.name );
4066 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.put_messages', p_in_rec.put_messages);
4067 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.manual_flag ', p_in_rec.manual_flag );
4068 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.caller ', p_in_rec.caller );
4069 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.actual_date ', p_in_rec.actual_date );
4070 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.stop_id ', p_in_rec.stop_id );
4071 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.linked_stop_id ', p_in_rec.linked_stop_id );
4072 END IF;
4073 --
4074 --
4075 l_num_warnings := 0;
4076 l_num_errors := 0;
4077 --
4078 -- Check if trip has any stops which is not closed. IF so, trip close is not
4079 -- allowed else it is allowed.
4080 --
4081 OPEN any_open_stop(p_in_rec.trip_id, p_in_rec.stop_id, p_in_rec.linked_stop_id);
4082 FETCH any_open_stop INTO l_dummy;
4083 --
4084 IF any_open_stop%FOUND
4085 THEN
4086 CLOSE any_open_stop;
4087 --bug 3410681
4088 x_allowed := 'NT';
4089
4090 RAISE wsh_util_core.e_not_allowed;
4091 END IF;
4092 --
4093 CLOSE any_open_stop;
4094 --
4095
4096 -- Check for Exceptions against the Trip
4097 IF l_debug_on THEN
4098 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.Check_Exceptions',WSH_DEBUG_SV.C_PROC_LEVEL);
4099 END IF;
4100 l_exceptions_tab.delete;
4101 l_exp_logged := FALSE;
4102 l_exp_warning := FALSE;
4103 WSH_XC_UTIL.Check_Exceptions (
4104 p_api_version => 1.0,
4105 x_return_status => l_return_status,
4106 x_msg_count => l_msg_count,
4107 x_msg_data => l_msg_data,
4108 p_logging_entity_id => p_in_rec.trip_id,
4109 p_logging_entity_name => 'TRIP',
4110 p_consider_content => 'Y',
4111 x_exceptions_tab => l_exceptions_tab
4112 );
4113 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
4114 x_return_status := l_return_status;
4115 wsh_util_core.add_message(x_return_status);
4116 RAISE FND_API.G_EXC_ERROR;
4117 END IF;
4118 FOR exp_cnt in 1..l_exceptions_tab.COUNT LOOP
4119 IF l_exceptions_tab(exp_cnt).exception_behavior = 'ERROR' THEN
4120 IF l_exceptions_tab(exp_cnt).entity_name = 'TRIP' THEN
4121 FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_ENTITY');
4122 ELSE
4123 FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_CONTENTS');
4124 END IF;
4125
4126 -- BugFix #3947506
4127 WSH_UTIL_CORE.Get_Entity_Name
4128 ( l_exceptions_tab(exp_cnt).entity_id,
4129 l_exceptions_tab(exp_cnt).entity_name,
4130 l_out_entity_id,
4131 l_out_entity_name,
4132 l_out_status);
4133
4134 IF ( l_out_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
4135 wsh_util_core.add_message(l_out_status);
4136 RAISE FND_API.G_EXC_ERROR;
4137 END IF;
4138 -- End of code BugFix #3947506
4139
4140 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',l_out_entity_name); -- BugFix #3947506
4141 FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_out_entity_id); -- BugFix #3947506
4142 FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Error');
4143 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4144 wsh_util_core.add_message(x_return_status);
4145 l_num_warnings := l_num_warnings + 1;
4146
4147 --bug 3410681
4148 x_allowed := 'N';
4149 l_out_entity_id := ''; -- BugFix #3947506
4150 l_out_entity_name := ''; -- BugFix #3947506
4151
4152 RAISE wsh_util_core.e_not_allowed;
4153 ELSIF l_exceptions_tab(exp_cnt).exception_behavior = 'WARNING' THEN
4154 IF l_exceptions_tab(exp_cnt).entity_name = 'TRIP' THEN
4155 FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_ENTITY');
4156 FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
4157 FND_MESSAGE.SET_TOKEN('ENTITY_ID',wsh_trips_pvt.get_name(l_exceptions_tab(exp_cnt).entity_id));
4158 FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Warning');
4159 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4160 wsh_util_core.add_message(x_return_status);
4161 l_num_warnings := l_num_warnings + 1;
4162 l_exp_warning := TRUE;
4163 ELSIF NOT (l_exp_logged) THEN
4164 -- BugFix #3947506
4165 WSH_UTIL_CORE.Get_Entity_Name
4166 ( l_exceptions_tab(exp_cnt).entity_id,
4167 l_exceptions_tab(exp_cnt).entity_name,
4168 l_out_entity_id,
4169 l_out_entity_name,
4170 l_out_status);
4171
4172 IF ( l_out_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
4173 wsh_util_core.add_message(l_out_status);
4174 RAISE FND_API.G_EXC_ERROR;
4175 END IF;
4176 -- End of code BugFix #3947506
4177 FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_CONTENTS');
4178 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',l_out_entity_name); -- BugFix #3947506
4179 FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_out_entity_id); -- BugFix #3947506
4180 FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Warning');
4181 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4182 l_exp_logged := TRUE;
4183 wsh_util_core.add_message(x_return_status);
4184 l_num_warnings := l_num_warnings + 1;
4185 l_exp_warning := TRUE;
4186 l_out_entity_id := ''; -- BugFix #3947506
4187 l_out_entity_name := ''; -- BugFix #3947506
4188 END IF;
4189 END IF;
4190 END LOOP;
4191 --
4192
4193 IF l_num_errors > 0
4194 THEN
4195 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4196 x_allowed := 'N';
4197 ELSIF l_num_warnings > 0
4198 THEN
4199 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4200 ELSE
4201 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4202 END IF;
4203 --
4204 -- If Exceptions have warnings, then display warnings but allow to proceed
4205 IF l_exp_warning THEN
4206 x_allowed := 'YW';
4207 ELSE
4208 x_allowed := 'Y';
4209 END IF;
4210 --
4211 -- Debug Statements
4212 --
4213 IF l_debug_on THEN
4214 WSH_DEBUG_SV.pop(l_module_name);
4215 END IF;
4216 --
4217 --}
4218 EXCEPTION
4219 --{
4220 WHEN wsh_util_core.e_not_allowed THEN
4221 IF l_num_warnings > 0
4222 THEN
4223 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4224 ELSE
4225 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4226 END IF;
4227 --
4228 --
4229 --
4230 IF l_debug_on THEN
4231 WSH_DEBUG_SV.logmsg(l_module_name,'e_not_allowed exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4232 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:e_not_allowed');
4233 END IF;
4234 --
4235 WHEN wsh_util_core.e_not_allowed_warning THEN
4236 IF l_num_warnings > 0
4237 THEN
4238 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4239 ELSE
4240 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4241 END IF;
4242 --
4243 x_allowed := 'NW';
4244 --
4245 --
4246 IF l_debug_on THEN
4247 WSH_DEBUG_SV.logmsg(l_module_name,'e_not_allowed_warning exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4248 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:e_not_allowed_warning');
4249 END IF;
4250 --
4251 WHEN FND_API.G_EXC_ERROR THEN
4252
4253 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4254 --
4255 IF l_debug_on THEN
4256 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4257 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4258 END IF;
4259 --
4260 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4261
4262 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4263 --
4264 IF l_debug_on THEN
4265 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4266 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
4267 END IF;
4268 --
4269 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
4270 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4271 --
4272 IF l_debug_on THEN
4273 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4274 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
4275 END IF;
4276 --
4277 WHEN OTHERS THEN
4278
4279 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4280 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.check_Close',l_module_name);
4281 --
4282 IF l_debug_on THEN
4283 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4284 END IF;
4285 --
4286 --}
4287 END check_Close;
4288 --
4289 --
4290 --========================================================================
4291 -- PROCEDURE : check_inTransit
4292 --
4293 -- PARAMETERS: p_in_rec Input Record (Refer to WSHTRVLS.pls for description)
4294 -- x_allowed Trip can be closed or not
4295 -- 'Y' : Allowed
4296 -- 'YW': Allowed with warnings
4297 -- 'N' : Not Allowed
4298 -- 'NW': Not Allowed with warnings
4299 -- x_return_status Return status of API
4300 --
4301 --
4302 -- COMMENT : This function checks if trip can be set to in-transit or not.
4303 --
4304 -- It performs following validations:
4305 -- 01. Check if trip has any stops which is closed. IF so, trip can be
4306 -- set to in-transit else not. This check is done ONLY when
4307 -- it is being called with Stop_Id as NULL
4308 -- 02. Check for exceptions logged against trip and its contents
4309 --========================================================================
4310 --
4311 PROCEDURE check_inTransit
4312 (
4313 p_in_rec IN ChgStatus_in_rec_type,
4314 x_return_status OUT NOCOPY VARCHAR2,
4315 x_allowed OUT NOCOPY VARCHAR2
4316 )
4317 IS
4318 --{
4319 --
4320 -- Check if trip has any closed stop
4321 --
4322 CURSOR any_closed_stop (p_trip_id NUMBER) IS
4323 SELECT stop_id
4324 FROM wsh_trip_stops
4325 WHERE trip_id = p_trip_id
4326 AND status_code = 'CL';
4327 --
4328 l_num_warnings NUMBER;
4329 l_num_errors NUMBER;
4330 l_dummy NUMBER;
4331 --
4332 -- Exception variables
4333 l_exceptions_tab wsh_xc_util.XC_TAB_TYPE;
4334 l_exp_logged BOOLEAN := FALSE;
4335 l_exp_warning BOOLEAN := FALSE;
4336 l_msg_count NUMBER;
4337 l_msg_data VARCHAR2(4000);
4338 l_return_status VARCHAR2(1);
4339 -- Following three variables are added for BugFix #3947506
4340 l_out_entity_id VARCHAR2(100);
4341 l_out_entity_name VARCHAR2(100);
4342 l_out_status VARCHAR2(1);
4343 --
4344 l_debug_on BOOLEAN;
4345 --
4346 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || g_pkg_name || '.' || 'check_inTransit';
4347 --}
4348 BEGIN
4349 --{
4350 --
4351 l_debug_on := wsh_debug_interface.g_debug;
4352 --
4353 IF l_debug_on IS NULL THEN
4354 l_debug_on := wsh_debug_sv.is_debug_enabled;
4355 END IF;
4356 --
4357 IF l_debug_on THEN
4358 wsh_debug_sv.push(l_module_name);
4359 --
4360 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.trip_id ', p_in_rec.trip_id );
4361 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.name ', p_in_rec.name );
4362 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.put_messages', p_in_rec.put_messages);
4363 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.manual_flag ', p_in_rec.manual_flag );
4364 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.caller ', p_in_rec.caller );
4365 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.actual_date ', p_in_rec.actual_date );
4366 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.stop_id ', p_in_rec.stop_id );
4367 END IF;
4368 --
4369 --
4370 l_num_warnings := 0;
4371 l_num_errors := 0;
4372 --
4373 --
4374 -- Stop_id is not null only when called from WSH_TRIP_STOP_VALIDATIONS.Check_Stop_Close api
4375 -- Otherwise it should be null when called from other APIs
4376 -- This check is required due to inbound logistics changes, trip needs to be set in-transit
4377 -- when any stop of trip is closed
4378 IF p_in_rec.stop_id IS NULL THEN
4379 OPEN any_closed_stop(p_in_rec.trip_id);
4380 FETCH any_closed_stop INTO l_dummy;
4381 --
4382 -- Check if trip has any stops which is closed. IF so, trip can be
4383 -- set to in-transit else not.
4384 --
4385 IF any_closed_stop%NOTFOUND
4386 THEN
4387 CLOSE any_closed_stop;
4388 RAISE wsh_util_core.e_not_allowed;
4389 END IF;
4390 --
4391 CLOSE any_closed_stop;
4392 END IF;
4393 --
4394
4395 -- Check for Exceptions against the Trip
4396 IF l_debug_on THEN
4397 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.Check_Exceptions',WSH_DEBUG_SV.C_PROC_LEVEL);
4398 END IF;
4399 l_exceptions_tab.delete;
4400 l_exp_logged := FALSE;
4401 l_exp_warning := FALSE;
4402 WSH_XC_UTIL.Check_Exceptions (
4403 p_api_version => 1.0,
4404 x_return_status => l_return_status,
4405 x_msg_count => l_msg_count,
4406 x_msg_data => l_msg_data,
4407 p_logging_entity_id => p_in_rec.trip_id,
4408 p_logging_entity_name => 'TRIP',
4409 p_consider_content => 'Y',
4410 x_exceptions_tab => l_exceptions_tab
4411 );
4412 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
4413 x_return_status := l_return_status;
4414 wsh_util_core.add_message(x_return_status);
4415 RAISE FND_API.G_EXC_ERROR;
4416 END IF;
4417 FOR exp_cnt in 1..l_exceptions_tab.COUNT LOOP
4418 IF l_exceptions_tab(exp_cnt).exception_behavior = 'ERROR' THEN
4419 IF l_exceptions_tab(exp_cnt).entity_name = 'TRIP' THEN
4420 FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_ENTITY');
4421 ELSE
4422 FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_CONTENTS');
4423 END IF;
4424
4425 -- BugFix #3947506
4426 WSH_UTIL_CORE.Get_Entity_Name
4427 ( l_exceptions_tab(exp_cnt).entity_id,
4428 l_exceptions_tab(exp_cnt).entity_name,
4429 l_out_entity_id,
4430 l_out_entity_name,
4431 l_out_status);
4432
4433 IF ( l_out_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
4434 wsh_util_core.add_message(l_out_status);
4435 RAISE FND_API.G_EXC_ERROR;
4436 END IF;
4437 -- End of code BugFix #3947506
4438
4439 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',l_out_entity_name); -- BugFix #3947506
4440 FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_out_entity_id); -- BugFix #3947506
4441 FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Error');
4442 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4443 wsh_util_core.add_message(x_return_status);
4444 l_num_warnings := l_num_warnings + 1;
4445 RAISE wsh_util_core.e_not_allowed;
4446 ELSIF l_exceptions_tab(exp_cnt).exception_behavior = 'WARNING' THEN
4447 IF l_exceptions_tab(exp_cnt).entity_name = 'TRIP' THEN
4448 FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_ENTITY');
4449 FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
4450 FND_MESSAGE.SET_TOKEN('ENTITY_ID',wsh_trips_pvt.get_name(l_exceptions_tab(exp_cnt).entity_id));
4451 FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Warning');
4452 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4453 wsh_util_core.add_message(x_return_status);
4454 l_num_warnings := l_num_warnings + 1;
4455 l_exp_warning := TRUE;
4456 ELSIF NOT (l_exp_logged) THEN
4457 -- BugFix #3947506
4458 WSH_UTIL_CORE.Get_Entity_Name
4459 ( l_exceptions_tab(exp_cnt).entity_id,
4460 l_exceptions_tab(exp_cnt).entity_name,
4461 l_out_entity_id,
4462 l_out_entity_name,
4463 l_out_status);
4464
4465 IF ( l_out_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
4466 wsh_util_core.add_message(l_out_status);
4467 RAISE FND_API.G_EXC_ERROR;
4468 END IF;
4469 -- End of code BugFix #3947506
4470
4471 FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_CONTENTS');
4472 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',l_out_entity_name); -- BugFix #3947506
4473 FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_out_entity_id); -- BugFix #3947506
4474 FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Warning');
4475 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4476 l_exp_logged := TRUE;
4477 wsh_util_core.add_message(x_return_status);
4478 l_num_warnings := l_num_warnings + 1;
4479 l_exp_warning := TRUE;
4480 END IF;
4481 END IF;
4482 END LOOP;
4483 --
4484
4485 IF l_num_errors > 0
4486 THEN
4487 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4488 x_allowed := 'N';
4489 ELSIF l_num_warnings > 0
4490 THEN
4491 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4492 ELSE
4493 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4494 END IF;
4495 --
4496 --
4497 -- If Exceptions have warnings, then display warnings but allow to proceed
4498 IF l_exp_warning THEN
4499 x_allowed := 'YW';
4500 ELSE
4501 x_allowed := 'Y';
4502 END IF;
4503 --
4504 -- Debug Statements
4505 --
4506 IF l_debug_on THEN
4507 WSH_DEBUG_SV.pop(l_module_name);
4508 END IF;
4509 --
4510 --}
4511 EXCEPTION
4512 --{
4513 WHEN wsh_util_core.e_not_allowed THEN
4514 IF l_num_warnings > 0
4515 THEN
4516 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4517 ELSE
4518 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4519 END IF;
4520 --
4521 x_allowed := 'N';
4522 --
4523 --
4524 IF l_debug_on THEN
4525 WSH_DEBUG_SV.logmsg(l_module_name,'e_not_allowed exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4526 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:e_not_allowed');
4527 END IF;
4528 --
4529 WHEN wsh_util_core.e_not_allowed_warning THEN
4530 IF l_num_warnings > 0
4531 THEN
4532 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4533 ELSE
4534 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4535 END IF;
4536 --
4537 x_allowed := 'NW';
4538 --
4539 --
4540 IF l_debug_on THEN
4541 WSH_DEBUG_SV.logmsg(l_module_name,'e_not_allowed_warning exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4542 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:e_not_allowed_warning');
4543 END IF;
4544 --
4545 WHEN FND_API.G_EXC_ERROR THEN
4546
4547 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4548 --
4549 IF l_debug_on THEN
4550 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4551 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4552 END IF;
4553 --
4554 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4555
4556 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4557 --
4558 IF l_debug_on THEN
4559 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4560 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
4561 END IF;
4562 --
4563 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
4564 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4565 --
4566 IF l_debug_on THEN
4567 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4568 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
4569 END IF;
4570 --
4571 WHEN OTHERS THEN
4572
4573 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4574 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.check_inTransit',l_module_name);
4575 --
4576 IF l_debug_on THEN
4577 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4578 END IF;
4579 --
4580 --}
4581 END check_inTransit;
4582 --
4583 --
4584 --========================================================================
4585 -- PROCEDURE : Validate_Trip_status
4586 --
4587 -- PARAMETERS: p_trip_id Trip ID
4588 -- p_action Action (Not used)
4589 -- x_return_status Return status of API
4590 --
4591 --
4592 -- COMMENT : This API is called by delivery group API while assigning delivery to trip
4593 --
4594 -- It performs following validations:
4595 -- 01. IF trip is routing and content firm or trip is in-transit/closed, delivery
4596 -- cannot be assigned to the trip
4597 --========================================================================
4598 --
4599 --
4600 PROCEDURE Validate_Trip_status
4601 (
4602 p_trip_id IN NUMBER,
4603 p_action IN VARCHAR2,
4604 x_return_status OUT NOCOPY VARCHAR2
4605 )
4606 IS
4607 --{
4608 /* J TP Release : If assigning del to trip doesn't introduce new stops, ok to assign to planned trips */
4609
4610 CURSOR trip_csr (p_trip_id NUMBER)
4611 IS
4612 select status_code, name, nvl(planned_flag,'N') planned_flag
4613 from wsh_trips
4614 where trip_id = p_trip_id;
4615 --
4616 l_trip_rec trip_csr%ROWTYPE;
4617 --
4618 l_debug_on BOOLEAN;
4619 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_TRIP_STATUS';
4620 --}
4621 BEGIN
4622 --{
4623 --
4624 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4625 --
4626 IF l_debug_on IS NULL
4627 THEN
4628 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4629 END IF;
4630 --
4631 IF l_debug_on THEN
4632 WSH_DEBUG_SV.push(l_module_name);
4633 WSH_DEBUG_SV.log(l_module_name,'p_trip_id',p_trip_id);
4634 WSH_DEBUG_SV.log(l_module_name,'p_action',p_action);
4635 END IF;
4636 --
4637 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4638 --
4639 IF (p_trip_id IS NOT NULL)
4640 THEN
4641 --{
4642 OPEN trip_csr(p_trip_id);
4643 FETCH trip_csr INTO l_trip_rec;
4644 CLOSE trip_csr;
4645 --
4646 IF l_trip_rec.planned_flag NOT IN ('N','Y')
4647 THEN
4648 -- Trip is routing and content firm.
4649 RAISE wsh_util_core.e_not_allowed;
4650 END IF;
4651 --
4652 IF l_trip_rec.status_code IN ('IT','CL')
4653 THEN
4654 RAISE wsh_util_core.e_not_allowed;
4655 END IF;
4656 --
4657 --}
4658 END IF;
4659 --
4660 IF l_debug_on THEN
4661 WSH_DEBUG_SV.pop(l_module_name);
4662 END IF;
4663 --}
4664 EXCEPTION
4665 --{
4666 WHEN wsh_util_core.e_not_allowed THEN
4667 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4668 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_STATUS_NO_ACTION');
4669 FND_MESSAGE.SET_TOKEN('TRIP_NAME', l_trip_rec.name);
4670 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
4671 --
4672 IF l_debug_on THEN
4673 WSH_DEBUG_SV.logmsg(l_module_name,'e_not_allowed exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4674 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:e_not_allowed');
4675 END IF;
4676 --
4677 WHEN OTHERS THEN
4678 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4679 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.validate_trip_status', l_module_name);
4680 IF l_debug_on THEN
4681 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4682 END IF;
4683 --}
4684 END validate_trip_status;
4685 --
4686 --
4687 --
4688 --========================================================================
4689 -- FUNCTION : has_outbound_deliveries
4690 --
4691 -- PARAMETERS: p_trip_id Trip ID
4692 -- p_stop_id Stop ID (Optional)
4693 -- Returns 'Y' if trip/stop has outbound deliveries
4694 -- Returns 'N' if trip/stop does not have outbound deliveries
4695 --
4696 --
4697 -- COMMENT : This function determines if trip/stop has outbound (O/IO) deliveries
4698 -- associated with it.
4699 --
4700 -- If stop is passed, it checks if any outbound(O/IO) delivery is being
4701 -- picked up or dropped off at the stop. If so, it returns 'Y' else 'N'
4702 --
4703 -- If stop is not passed, it checks if any outbound(O/IO) delivery is being
4704 -- picked up or dropped off at any stop of the trip.
4705 -- If so, it returns 'Y' else 'N'
4706 --
4707 -- If trip id is not passed in, it returns 'N'
4708 -- If invalid trip/stop id is passed in, it returns 'N'
4709 --========================================================================
4710 --
4711 FUNCTION has_outbound_deliveries
4712 (
4713 p_trip_id IN NUMBER,
4714 p_stop_id IN NUMBER DEFAULT NULL
4715 )
4716 RETURN VARCHAR2
4717 IS
4718 --{
4719 --
4720 -- Look for outbound (O/IO) deliveries being picked up or dropped off at stop/trip.
4721 --
4722 CURSOR trip_csr (p_trip_id NUMBER, p_stop_id NUMBER)
4723 IS
4724 SELECT 1
4725 FROM wsh_delivery_legs wdl,
4726 wsh_new_deliveries wnd,
4727 wsh_trip_stops wts
4728 WHERE wts.trip_id = p_trip_id
4729 AND wdl.delivery_id = wnd.delivery_id
4730 AND NVL(wnd.shipment_direction,'O') IN ( 'O','IO' )
4731 AND ( p_stop_id is null or wts.stop_id = p_stop_id ) --Bugfix 3639920
4732 AND (
4733 wdl.pick_up_stop_id = wts.stop_id
4734 OR wdl.drop_off_stop_id = wts.stop_id
4735 );
4736 --
4737 l_cnt NUMBER := 0;
4738 --
4739 l_debug_on BOOLEAN;
4740 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'has_outbound_deliveries';
4741 --}
4742 BEGIN
4743 --{
4744 --
4745 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4746 --
4747 IF l_debug_on IS NULL
4748 THEN
4749 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4750 END IF;
4751 --
4752 IF l_debug_on THEN
4753 WSH_DEBUG_SV.push(l_module_name);
4754 WSH_DEBUG_SV.log(l_module_name,'p_trip_id',p_trip_id);
4755 WSH_DEBUG_SV.log(l_module_name,'p_stop_id',p_stop_id);
4756 END IF;
4757 --
4758 --
4759 l_cnt := 0;
4760 --
4761 IF (p_trip_id IS NOT NULL)
4762 THEN
4763 --{
4764 OPEN trip_csr(p_trip_id, p_stop_id);
4765 FETCH trip_csr INTO l_cnt;
4766 CLOSE trip_csr;
4767 --}
4768 END IF;
4769 --
4770 --
4771 IF l_debug_on THEN
4772 WSH_DEBUG_SV.log(l_module_name,'l_cnt',l_cnt);
4773 WSH_DEBUG_SV.pop(l_module_name);
4774 END IF;
4775 --
4776 IF l_cnt = 1
4777 THEN
4778 RETURN('Y');
4779 ELSE
4780 RETURN('N');
4781 END IF;
4782 --}
4783 EXCEPTION
4784 --{
4785 WHEN OTHERS THEN
4786 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.has_outbound_deliveries', l_module_name);
4787 --
4788 IF l_debug_on THEN
4789 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4790 END IF;
4791 --
4792 RAISE;
4793 --}
4794 END has_outbound_deliveries;
4795 --
4796 --
4797 --
4798 --========================================================================
4799 -- FUNCTION : has_inbound_deliveries
4800 --
4801 -- PARAMETERS: p_trip_id Trip ID
4802 -- p_stop_id Stop ID (Optional)
4803 -- Returns 'Y' if trip/stop has inbound deliveries
4804 -- Returns 'N' if trip/stop does not have inbound deliveries
4805 --
4806 --
4807 -- COMMENT : This function determines if trip/stop has inbound (not O/IO) deliveries
4808 -- associated with it.
4809 --
4810 -- If stop is passed, it checks if any inbound(not O/IO) delivery is being
4811 -- picked up or dropped off at the stop. If so, it returns 'Y' else 'N'
4812 --
4813 -- If stop is not passed, it checks if any inbound(not O/IO) delivery is being
4814 -- picked up or dropped off at any stop of the trip.
4815 -- If so, it returns 'Y' else 'N'
4816 --
4817 -- If trip id is not passed in, it returns 'N'
4818 -- If invalid trip/stop id is passed in, it returns 'N'
4819 --========================================================================
4820 --
4821 FUNCTION has_inbound_deliveries
4822 (
4823 p_trip_id IN NUMBER,
4824 p_stop_id IN NUMBER DEFAULT NULL
4825 )
4826 RETURN VARCHAR2
4827 IS
4828 --{
4829 --
4830 -- Look for inbound (not O/IO) deliveries being picked up or dropped off at stop/trip.
4831 --
4832 CURSOR trip_csr (p_trip_id NUMBER, p_stop_id NUMBER)
4833 IS
4834 SELECT 1
4835 FROM wsh_delivery_legs wdl,
4836 wsh_new_deliveries wnd,
4837 wsh_trip_stops wts
4838 WHERE wts.trip_id = p_trip_id
4839 AND wdl.delivery_id = wnd.delivery_id
4840 AND NVL(wnd.shipment_direction,'O') NOT IN ( 'O','IO' )
4841 AND ( p_stop_id is null or wts.stop_id = p_stop_id ) --Bugfix 3639920
4842 AND (
4843 wdl.pick_up_stop_id = wts.stop_id
4844 OR wdl.drop_off_stop_id = wts.stop_id
4845 );
4846 --
4847 l_cnt NUMBER := 0;
4848 --
4849 l_debug_on BOOLEAN;
4850 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'has_inbound_deliveries';
4851 --}
4852 BEGIN
4853 --{
4854 --
4855 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4856 --
4857 IF l_debug_on IS NULL
4858 THEN
4859 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4860 END IF;
4861 --
4862 IF l_debug_on THEN
4863 WSH_DEBUG_SV.push(l_module_name);
4864 WSH_DEBUG_SV.log(l_module_name,'p_trip_id',p_trip_id);
4865 WSH_DEBUG_SV.log(l_module_name,'p_stop_id',p_stop_id);
4866 END IF;
4867 --
4868 --
4869 l_cnt := 0;
4870 --
4871 IF (p_trip_id IS NOT NULL)
4872 THEN
4873 --{
4874 OPEN trip_csr(p_trip_id, p_stop_id);
4875 FETCH trip_csr INTO l_cnt;
4876 CLOSE trip_csr;
4877 --}
4878 END IF;
4879 --
4880 IF l_debug_on THEN
4881 WSH_DEBUG_SV.log(l_module_name,'l_cnt',l_cnt);
4882 WSH_DEBUG_SV.pop(l_module_name);
4883 END IF;
4884 --
4885 IF l_cnt = 1
4886 THEN
4887 RETURN('Y');
4888 ELSE
4889 RETURN('N');
4890 END IF;
4891 --
4892 --}
4893 EXCEPTION
4894 --{
4895 WHEN OTHERS THEN
4896 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.has_inbound_deliveries', l_module_name);
4897 --
4898 IF l_debug_on THEN
4899 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4900 END IF;
4901 --
4902 RAISE;
4903 --}
4904 END has_inbound_deliveries;
4905 --
4906 --
4907 --
4908 --========================================================================
4909 -- FUNCTION : has_mixed_deliveries
4910 --
4911 -- PARAMETERS: p_trip_id Trip ID
4912 -- p_stop_id Stop ID (Optional)
4913 -- Returns 'Y' if trip/stop has mixed deliveries
4914 -- Returns 'N' if trip/stop does not have mixed deliveries
4915 --
4916 --
4917 -- COMMENT : This function determines if trip/stop has mixed (both inbound and outbound)
4918 -- deliveries associated with it.
4919 --
4920 -- Following is the logic:
4921 -- 01. Call has_outbound_deliveries
4922 -- 02. Call has_inbound_deliveries
4923 -- 03. Set return values as follows, using outcome of steps above.
4924 --
4925 -- Has Outbound Has Inbound Return Value(Meaning)
4926 -- Y Y Y
4927 -- Y N NO (No,has only outbound)
4928 -- N Y NI (No,has only inbound)
4929 -- N N N (No,no deliveries)
4930 --
4931 -- If trip id is not passed in, it returns 'N'
4932 -- If invalid trip/stop id is passed in, it returns 'N'
4933 --========================================================================
4934 --
4935 FUNCTION has_mixed_deliveries
4936 (
4937 p_trip_id IN NUMBER,
4938 p_stop_id IN NUMBER DEFAULT NULL
4939 )
4940 RETURN VARCHAR2
4941 IS
4942 --{
4943 l_has_outbound_deliveries VARCHAR2(10);
4944 l_has_inbound_deliveries VARCHAR2(10);
4945 l_has_mixed_deliveries VARCHAR2(10);
4946 --
4947 l_debug_on BOOLEAN;
4948 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'has_mixed_deliveries';
4949 --}
4950 BEGIN
4951 --{
4952 --
4953 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4954 --
4955 IF l_debug_on IS NULL
4956 THEN
4957 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4958 END IF;
4959 --
4960 IF l_debug_on THEN
4961 WSH_DEBUG_SV.push(l_module_name);
4962 WSH_DEBUG_SV.log(l_module_name,'p_trip_id',p_trip_id);
4963 WSH_DEBUG_SV.log(l_module_name,'p_stop_id',p_stop_id);
4964 END IF;
4965 --
4966 --
4967 l_has_mixed_deliveries := 'N';
4968 --
4969 IF (p_trip_id IS NOT NULL)
4970 THEN
4971 --{
4972 IF l_debug_on THEN
4973 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_VALIDATIONS.has_outbound_deliveries',WSH_DEBUG_SV.C_PROC_LEVEL);
4974 END IF;
4975 --
4976 --
4977 l_has_outbound_deliveries := WSH_TRIP_VALIDATIONS.has_outbound_deliveries
4978 (
4979 p_trip_id => p_trip_id,
4980 p_stop_id => p_stop_id
4981 );
4982 --
4983 --
4984 IF l_debug_on THEN
4985 WSH_DEBUG_SV.log(l_module_name,'l_has_outbound_deliveries',l_has_outbound_deliveries);
4986 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_VALIDATIONS.has_inbound_deliveries',WSH_DEBUG_SV.C_PROC_LEVEL);
4987 END IF;
4988 --
4989 l_has_inbound_deliveries := WSH_TRIP_VALIDATIONS.has_inbound_deliveries
4990 (
4991 p_trip_id => p_trip_id,
4992 p_stop_id => p_stop_id
4993 );
4994 --
4995 IF l_has_outbound_deliveries = 'Y'
4996 AND l_has_inbound_deliveries = 'Y'
4997 THEN
4998 l_has_mixed_deliveries := 'Y';
4999 ELSIF l_has_outbound_deliveries = 'Y'
5000 AND l_has_inbound_deliveries = 'N'
5001 THEN
5002 l_has_mixed_deliveries := 'NO';
5003 ELSIF l_has_outbound_deliveries = 'N'
5004 AND l_has_inbound_deliveries = 'Y'
5005 THEN
5006 l_has_mixed_deliveries := 'NI';
5007 END IF;
5008 --}
5009 END IF;
5010 --
5011 --
5012 IF l_debug_on THEN
5013 WSH_DEBUG_SV.log(l_module_name,'l_has_inbound_deliveries',l_has_inbound_deliveries);
5014 WSH_DEBUG_SV.log(l_module_name,'l_has_mixed_deliveries',l_has_mixed_deliveries);
5015 WSH_DEBUG_SV.pop(l_module_name);
5016 END IF;
5017 --
5018 RETURN(l_has_mixed_deliveries);
5019 --}
5020 EXCEPTION
5021 --{
5022 WHEN OTHERS THEN
5023 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.has_mixed_deliveries', l_module_name);
5024 --
5025 IF l_debug_on THEN
5026 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5027 END IF;
5028 --
5029 RAISE;
5030 --}
5031 END has_mixed_deliveries;
5032 --
5033 -- J-IB-NPARIKH-}
5034 --
5035 --3509004:public api change
5036 PROCEDURE user_non_updatable_columns
5037 (p_user_in_rec IN WSH_TRIPS_PVT.trip_rec_type,
5038 p_out_rec IN WSH_TRIPS_PVT.trip_rec_type,
5039 p_in_rec IN WSH_TRIPS_GRP.TripInRecType,
5040 x_return_status OUT NOCOPY VARCHAR2)
5041
5042 IS
5043 l_attributes VARCHAR2(2500) ;
5044 k number;
5045 l_return_status VARCHAR2(1);
5046 l_debug_on BOOLEAN;
5047 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'user_non_updatable_columns';
5048
5049 BEGIN
5050
5051 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5052 --
5053 IF l_debug_on IS NULL
5054 THEN
5055 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5056 END IF;
5057 --
5058 IF l_debug_on THEN
5059 --
5060 WSH_DEBUG_SV.push(l_module_name);
5061 WSH_DEBUG_SV.log(l_module_name,'p_in_rec.caller',p_in_rec.caller);
5062 --
5063 END IF;
5064 --
5065 x_return_status := FND_API.G_RET_STS_SUCCESS;
5066 --
5067 IF p_user_in_rec.TRIP_ID <> FND_API.G_MISS_NUM
5068 AND NVL(p_user_in_rec.TRIP_ID,-99) <> NVL(p_out_rec.TRIP_ID,-99)
5069 THEN
5070 IF l_attributes IS NOT NULL THEN
5071 l_attributes := l_attributes || ', ';
5072 END IF;
5073 l_attributes := l_attributes || 'TRIP_ID';
5074 END IF;
5075
5076 IF p_user_in_rec.NAME <> FND_API.G_MISS_CHAR
5077 AND NVL(p_user_in_rec.NAME,'!!!') <> NVL(p_out_rec.NAME,'!!!')
5078 THEN
5079 IF l_attributes IS NOT NULL THEN
5080 l_attributes := l_attributes || ', ';
5081 END IF;
5082 l_attributes := l_attributes || 'NAME';
5083 END IF;
5084
5085 IF p_user_in_rec.PLANNED_FLAG <> FND_API.G_MISS_CHAR
5086 AND NVL(p_user_in_rec.PLANNED_FLAG,'!!!') <> NVL(p_out_rec.PLANNED_FLAG,'!!!')
5087 THEN
5088 IF l_attributes IS NOT NULL THEN
5089 l_attributes := l_attributes || ', ';
5090 END IF;
5091 l_attributes := l_attributes || 'PLANNED_FLAG';
5092 END IF;
5093
5094 IF p_user_in_rec.ARRIVE_AFTER_TRIP_ID <> FND_API.G_MISS_NUM
5095 AND NVL(p_user_in_rec.ARRIVE_AFTER_TRIP_ID,-99) <> NVL(p_out_rec.ARRIVE_AFTER_TRIP_ID,-99)
5096 THEN
5097 IF l_attributes IS NOT NULL THEN
5098 l_attributes := l_attributes || ', ';
5099 END IF;
5100 l_attributes := l_attributes || 'ARRIVE_AFTER_TRIP_ID';
5101 END IF;
5102
5103 IF p_user_in_rec.STATUS_CODE <> FND_API.G_MISS_CHAR
5104 AND NVL(p_user_in_rec.STATUS_CODE,'!!!') <> NVL(p_out_rec.STATUS_CODE,'!!!')
5105 THEN
5106 IF l_attributes IS NOT NULL THEN
5107 l_attributes := l_attributes || ', ';
5108 END IF;
5109 l_attributes := l_attributes || 'STATUS_CODE';
5110 END IF;
5111
5112 IF p_user_in_rec.VEHICLE_ITEM_ID <> FND_API.G_MISS_NUM
5113 AND NVL(p_user_in_rec.VEHICLE_ITEM_ID,-99) <> NVL(p_out_rec.VEHICLE_ITEM_ID,-99)
5114 THEN
5115 IF l_attributes IS NOT NULL THEN
5116 l_attributes := l_attributes || ', ';
5117 END IF;
5118 l_attributes := l_attributes || 'VEHICLE_ITEM_ID';
5119 END IF;
5120
5121 IF p_user_in_rec.VEHICLE_ORGANIZATION_ID <> FND_API.G_MISS_NUM
5122 AND NVL(p_user_in_rec.VEHICLE_ORGANIZATION_ID,-99) <> NVL(p_out_rec.VEHICLE_ORGANIZATION_ID,-99)
5123 THEN
5124 IF l_attributes IS NOT NULL THEN
5125 l_attributes := l_attributes || ', ';
5126 END IF;
5127 l_attributes := l_attributes || 'VEHICLE_ORGANIZATION_ID';
5128 END IF;
5129
5130 IF p_user_in_rec.VEHICLE_NUMBER <> FND_API.G_MISS_CHAR
5131 AND NVL(p_user_in_rec.VEHICLE_NUMBER,'!!!') <> NVL(p_out_rec.VEHICLE_NUMBER,'!!!')
5132 THEN
5133 IF l_attributes IS NOT NULL THEN
5134 l_attributes := l_attributes || ', ';
5135 END IF;
5136 l_attributes := l_attributes || 'VEHICLE_NUMBER';
5137 END IF;
5138
5139 IF p_user_in_rec.VEHICLE_NUM_PREFIX <> FND_API.G_MISS_CHAR
5140 AND NVL(p_user_in_rec.VEHICLE_NUM_PREFIX,'!!!') <> NVL(p_out_rec.VEHICLE_NUM_PREFIX,'!!!')
5141 THEN
5142 IF l_attributes IS NOT NULL THEN
5143 l_attributes := l_attributes || ', ';
5144 END IF;
5145 l_attributes := l_attributes || 'VEHICLE_NUM_PREFIX';
5146 END IF;
5147
5148 IF p_user_in_rec.CARRIER_ID <> FND_API.G_MISS_NUM
5149 AND NVL(p_user_in_rec.CARRIER_ID,-99) <> NVL(p_out_rec.CARRIER_ID,-99)
5150 THEN
5151 IF l_attributes IS NOT NULL THEN
5152 l_attributes := l_attributes || ', ';
5153 END IF;
5154 l_attributes := l_attributes || 'CARRIER_ID';
5155 END IF;
5156
5157 IF p_user_in_rec.SHIP_METHOD_CODE <> FND_API.G_MISS_CHAR
5158 AND NVL(p_user_in_rec.SHIP_METHOD_CODE,'!!!') <> NVL(p_out_rec.SHIP_METHOD_CODE,'!!!')
5159 THEN
5160 IF l_attributes IS NOT NULL THEN
5161 l_attributes := l_attributes || ', ';
5162 END IF;
5163 l_attributes := l_attributes || 'SHIP_METHOD_CODE';
5164 END IF;
5165
5166 IF p_user_in_rec.ROUTE_ID <> FND_API.G_MISS_NUM
5167 AND NVL(p_user_in_rec.ROUTE_ID,-99) <> NVL(p_out_rec.ROUTE_ID,-99)
5168 THEN
5169 IF l_attributes IS NOT NULL THEN
5170 l_attributes := l_attributes || ', ';
5171 END IF;
5172 l_attributes := l_attributes || 'ROUTE_ID';
5173 END IF;
5174
5175 IF p_user_in_rec.ROUTING_INSTRUCTIONS <> FND_API.G_MISS_CHAR
5176 AND NVL(p_user_in_rec.ROUTING_INSTRUCTIONS,'!!!') <> NVL(p_out_rec.ROUTING_INSTRUCTIONS,'!!!')
5177 THEN
5178 IF l_attributes IS NOT NULL THEN
5179 l_attributes := l_attributes || ', ';
5180 END IF;
5181 l_attributes := l_attributes || 'ROUTING_INSTRUCTIONS';
5182 END IF;
5183
5184 IF p_user_in_rec.ATTRIBUTE_CATEGORY <> FND_API.G_MISS_CHAR
5185 AND NVL(p_user_in_rec.ATTRIBUTE_CATEGORY,'!!!') <> NVL(p_out_rec.ATTRIBUTE_CATEGORY,'!!!')
5186 THEN
5187 IF l_attributes IS NOT NULL THEN
5188 l_attributes := l_attributes || ', ';
5189 END IF;
5190 l_attributes := l_attributes || 'ATTRIBUTE_CATEGORY';
5191 END IF;
5192
5193 IF p_user_in_rec.ATTRIBUTE1 <> FND_API.G_MISS_CHAR
5194 AND NVL(p_user_in_rec.ATTRIBUTE1,'!!!') <> NVL(p_out_rec.ATTRIBUTE1,'!!!')
5195 THEN
5196 IF l_attributes IS NOT NULL THEN
5197 l_attributes := l_attributes || ', ';
5198 END IF;
5199 l_attributes := l_attributes || 'ATTRIBUTE1';
5200 END IF;
5201
5202 IF p_user_in_rec.ATTRIBUTE2 <> FND_API.G_MISS_CHAR
5203 AND NVL(p_user_in_rec.ATTRIBUTE2,'!!!') <> NVL(p_out_rec.ATTRIBUTE2,'!!!')
5204 THEN
5205 IF l_attributes IS NOT NULL THEN
5206 l_attributes := l_attributes || ', ';
5207 END IF;
5208 l_attributes := l_attributes || 'ATTRIBUTE2';
5209 END IF;
5210
5211 IF p_user_in_rec.ATTRIBUTE3 <> FND_API.G_MISS_CHAR
5212 AND NVL(p_user_in_rec.ATTRIBUTE3,'!!!') <> NVL(p_out_rec.ATTRIBUTE3,'!!!')
5213 THEN
5214 IF l_attributes IS NOT NULL THEN
5215 l_attributes := l_attributes || ', ';
5216 END IF;
5217 l_attributes := l_attributes || 'ATTRIBUTE3';
5218 END IF;
5219
5220 IF p_user_in_rec.ATTRIBUTE4 <> FND_API.G_MISS_CHAR
5221 AND NVL(p_user_in_rec.ATTRIBUTE4,'!!!') <> NVL(p_out_rec.ATTRIBUTE4,'!!!')
5222 THEN
5223 IF l_attributes IS NOT NULL THEN
5224 l_attributes := l_attributes || ', ';
5225 END IF;
5226 l_attributes := l_attributes || 'ATTRIBUTE4';
5227 END IF;
5228
5229 IF p_user_in_rec.ATTRIBUTE5 <> FND_API.G_MISS_CHAR
5230 AND NVL(p_user_in_rec.ATTRIBUTE5,'!!!') <> NVL(p_out_rec.ATTRIBUTE5,'!!!')
5231 THEN
5232 IF l_attributes IS NOT NULL THEN
5233 l_attributes := l_attributes || ', ';
5234 END IF;
5235 l_attributes := l_attributes || 'ATTRIBUTE5';
5236 END IF;
5237
5238 IF p_user_in_rec.ATTRIBUTE6 <> FND_API.G_MISS_CHAR
5239 AND NVL(p_user_in_rec.ATTRIBUTE6,'!!!') <> NVL(p_out_rec.ATTRIBUTE6,'!!!')
5240 THEN
5241 IF l_attributes IS NOT NULL THEN
5242 l_attributes := l_attributes || ', ';
5243 END IF;
5244 l_attributes := l_attributes || 'ATTRIBUTE6';
5245 END IF;
5246
5247 IF p_user_in_rec.ATTRIBUTE7 <> FND_API.G_MISS_CHAR
5248 AND NVL(p_user_in_rec.ATTRIBUTE7,'!!!') <> NVL(p_out_rec.ATTRIBUTE7,'!!!')
5249 THEN
5250 IF l_attributes IS NOT NULL THEN
5251 l_attributes := l_attributes || ', ';
5252 END IF;
5253 l_attributes := l_attributes || 'ATTRIBUTE7';
5254 END IF;
5255
5256 IF p_user_in_rec.ATTRIBUTE8 <> FND_API.G_MISS_CHAR
5257 AND NVL(p_user_in_rec.ATTRIBUTE8,'!!!') <> NVL(p_out_rec.ATTRIBUTE8,'!!!')
5258 THEN
5259 IF l_attributes IS NOT NULL THEN
5260 l_attributes := l_attributes || ', ';
5261 END IF;
5262 l_attributes := l_attributes || 'ATTRIBUTE8';
5263 END IF;
5264
5265 IF p_user_in_rec.ATTRIBUTE9 <> FND_API.G_MISS_CHAR
5266 AND NVL(p_user_in_rec.ATTRIBUTE9,'!!!') <> NVL(p_out_rec.ATTRIBUTE9,'!!!')
5267 THEN
5268 IF l_attributes IS NOT NULL THEN
5269 l_attributes := l_attributes || ', ';
5270 END IF;
5271 l_attributes := l_attributes || 'ATTRIBUTE9';
5272 END IF;
5273
5274 IF p_user_in_rec.ATTRIBUTE10 <> FND_API.G_MISS_CHAR
5275 AND NVL(p_user_in_rec.ATTRIBUTE10,'!!!') <> NVL(p_out_rec.ATTRIBUTE10,'!!!')
5276 THEN
5277 IF l_attributes IS NOT NULL THEN
5278 l_attributes := l_attributes || ', ';
5279 END IF;
5280 l_attributes := l_attributes || 'ATTRIBUTE10';
5281 END IF;
5282
5283 IF p_user_in_rec.ATTRIBUTE11 <> FND_API.G_MISS_CHAR
5284 AND NVL(p_user_in_rec.ATTRIBUTE11,'!!!') <> NVL(p_out_rec.ATTRIBUTE11,'!!!')
5285 THEN
5286 IF l_attributes IS NOT NULL THEN
5287 l_attributes := l_attributes || ', ';
5288 END IF;
5289 l_attributes := l_attributes || 'ATTRIBUTE11';
5290 END IF;
5291
5292 IF p_user_in_rec.ATTRIBUTE12 <> FND_API.G_MISS_CHAR
5293 AND NVL(p_user_in_rec.ATTRIBUTE12,'!!!') <> NVL(p_out_rec.ATTRIBUTE12,'!!!')
5294 THEN
5295 IF l_attributes IS NOT NULL THEN
5296 l_attributes := l_attributes || ', ';
5297 END IF;
5298 l_attributes := l_attributes || 'ATTRIBUTE12';
5299 END IF;
5300
5301 IF p_user_in_rec.ATTRIBUTE13 <> FND_API.G_MISS_CHAR
5302 AND NVL(p_user_in_rec.ATTRIBUTE13,'!!!') <> NVL(p_out_rec.ATTRIBUTE13,'!!!')
5303 THEN
5304 IF l_attributes IS NOT NULL THEN
5305 l_attributes := l_attributes || ', ';
5306 END IF;
5307 l_attributes := l_attributes || 'ATTRIBUTE13';
5308 END IF;
5309
5310 IF p_user_in_rec.ATTRIBUTE14 <> FND_API.G_MISS_CHAR
5311 AND NVL(p_user_in_rec.ATTRIBUTE14,'!!!') <> NVL(p_out_rec.ATTRIBUTE14,'!!!')
5312 THEN
5313 IF l_attributes IS NOT NULL THEN
5314 l_attributes := l_attributes || ', ';
5315 END IF;
5316 l_attributes := l_attributes || 'ATTRIBUTE14';
5317 END IF;
5318
5319 IF p_user_in_rec.ATTRIBUTE15 <> FND_API.G_MISS_CHAR
5320 AND NVL(p_user_in_rec.ATTRIBUTE15,'!!!') <> NVL(p_out_rec.ATTRIBUTE15,'!!!')
5321 THEN
5322 IF l_attributes IS NOT NULL THEN
5323 l_attributes := l_attributes || ', ';
5324 END IF;
5325 l_attributes := l_attributes || 'ATTRIBUTE15';
5326 END IF;
5327
5328 /**
5329 -- bug 3613650
5330 -- Need not compare against standard WHO columns
5331 IF p_user_in_rec.CREATION_DATE <> FND_API.G_MISS_DATE
5332 AND NVL(p_user_in_rec.CREATION_DATE,TO_DATE('2','j')) <> NVL(p_out_rec.CREATION_DATE,TO_DATE('2','j'))
5333 THEN
5334 IF l_attributes IS NOT NULL THEN
5335 l_attributes := l_attributes || ', ';
5336 END IF;
5337 l_attributes := l_attributes || 'CREATION_DATE';
5338 END IF;
5339
5340 IF p_user_in_rec.CREATED_BY <> FND_API.G_MISS_NUM
5341 AND NVL(p_user_in_rec.CREATED_BY,-99) <> NVL(p_out_rec.CREATED_BY,-99)
5342 THEN
5343 IF l_attributes IS NOT NULL THEN
5344 l_attributes := l_attributes || ', ';
5345 END IF;
5346 l_attributes := l_attributes || 'CREATED_BY';
5347 END IF;
5348
5349 IF p_user_in_rec.LAST_UPDATE_DATE <> FND_API.G_MISS_DATE
5350 AND NVL(p_user_in_rec.LAST_UPDATE_DATE,TO_DATE('2','j')) <> NVL(p_out_rec.LAST_UPDATE_DATE,TO_DATE('2','j'))
5351 THEN
5352 IF l_attributes IS NOT NULL THEN
5353 l_attributes := l_attributes || ', ';
5354 END IF;
5355 l_attributes := l_attributes || 'LAST_UPDATE_DATE';
5356 END IF;
5357
5358 IF p_user_in_rec.LAST_UPDATED_BY <> FND_API.G_MISS_NUM
5359 AND NVL(p_user_in_rec.LAST_UPDATED_BY,-99) <> NVL(p_out_rec.LAST_UPDATED_BY,-99)
5360 THEN
5361 IF l_attributes IS NOT NULL THEN
5362 l_attributes := l_attributes || ', ';
5363 END IF;
5364 l_attributes := l_attributes || 'LAST_UPDATED_BY';
5365 END IF;
5366
5367 IF p_user_in_rec.LAST_UPDATE_LOGIN <> FND_API.G_MISS_NUM
5368 AND NVL(p_user_in_rec.LAST_UPDATE_LOGIN,-99) <> NVL(p_out_rec.LAST_UPDATE_LOGIN,-99)
5369 THEN
5370 IF l_attributes IS NOT NULL THEN
5371 l_attributes := l_attributes || ', ';
5372 END IF;
5373 l_attributes := l_attributes || 'LAST_UPDATE_LOGIN';
5374 END IF;
5375
5376 IF p_user_in_rec.PROGRAM_APPLICATION_ID <> FND_API.G_MISS_NUM
5377 AND NVL(p_user_in_rec.PROGRAM_APPLICATION_ID,-99) <> NVL(p_out_rec.PROGRAM_APPLICATION_ID,-99)
5378 THEN
5379 IF l_attributes IS NOT NULL THEN
5380 l_attributes := l_attributes || ', ';
5381 END IF;
5382 l_attributes := l_attributes || 'PROGRAM_APPLICATION_ID';
5383 END IF;
5384
5385 IF p_user_in_rec.PROGRAM_ID <> FND_API.G_MISS_NUM
5386 AND NVL(p_user_in_rec.PROGRAM_ID,-99) <> NVL(p_out_rec.PROGRAM_ID,-99)
5387 THEN
5388 IF l_attributes IS NOT NULL THEN
5389 l_attributes := l_attributes || ', ';
5390 END IF;
5391 l_attributes := l_attributes || 'PROGRAM_ID';
5392 END IF;
5393
5394 IF p_user_in_rec.PROGRAM_UPDATE_DATE <> FND_API.G_MISS_DATE
5395 AND NVL(p_user_in_rec.PROGRAM_UPDATE_DATE,TO_DATE('2','j')) <> NVL(p_out_rec.PROGRAM_UPDATE_DATE,TO_DATE('2','j'))
5396 THEN
5397 IF l_attributes IS NOT NULL THEN
5398 l_attributes := l_attributes || ', ';
5399 END IF;
5400 l_attributes := l_attributes || 'PROGRAM_UPDATE_DATE';
5401 END IF;
5402
5403 IF p_user_in_rec.REQUEST_ID <> FND_API.G_MISS_NUM
5404 AND NVL(p_user_in_rec.REQUEST_ID,-99) <> NVL(p_out_rec.REQUEST_ID,-99)
5405 THEN
5406 IF l_attributes IS NOT NULL THEN
5407 l_attributes := l_attributes || ', ';
5408 END IF;
5409 l_attributes := l_attributes || 'REQUEST_ID';
5410 END IF;
5411
5412 bug 3613650 */
5413
5414 IF p_user_in_rec.SERVICE_LEVEL <> FND_API.G_MISS_CHAR
5415 AND NVL(p_user_in_rec.SERVICE_LEVEL,'!!!') <> NVL(p_out_rec.SERVICE_LEVEL,'!!!')
5416 THEN
5417 IF l_attributes IS NOT NULL THEN
5418 l_attributes := l_attributes || ', ';
5419 END IF;
5420 l_attributes := l_attributes || 'SERVICE_LEVEL';
5421 END IF;
5422
5423 IF p_user_in_rec.MODE_OF_TRANSPORT <> FND_API.G_MISS_CHAR
5424 AND NVL(p_user_in_rec.MODE_OF_TRANSPORT,'!!!') <> NVL(p_out_rec.MODE_OF_TRANSPORT,'!!!')
5425 THEN
5426 IF l_attributes IS NOT NULL THEN
5427 l_attributes := l_attributes || ', ';
5428 END IF;
5429 l_attributes := l_attributes || 'MODE_OF_TRANSPORT';
5430 END IF;
5431
5432 IF p_user_in_rec.FREIGHT_TERMS_CODE <> FND_API.G_MISS_CHAR
5433 AND NVL(p_user_in_rec.FREIGHT_TERMS_CODE,'!!!') <> NVL(p_out_rec.FREIGHT_TERMS_CODE,'!!!')
5434 THEN
5435 IF l_attributes IS NOT NULL THEN
5436 l_attributes := l_attributes || ', ';
5437 END IF;
5438 l_attributes := l_attributes || 'FREIGHT_TERMS_CODE';
5439 END IF;
5440
5441 IF p_user_in_rec.CONSOLIDATION_ALLOWED <> FND_API.G_MISS_CHAR
5442 AND NVL(p_user_in_rec.CONSOLIDATION_ALLOWED,'!!!') <> NVL(p_out_rec.CONSOLIDATION_ALLOWED,'!!!')
5443 THEN
5444 IF l_attributes IS NOT NULL THEN
5445 l_attributes := l_attributes || ', ';
5446 END IF;
5447 l_attributes := l_attributes || 'CONSOLIDATION_ALLOWED';
5448 END IF;
5449
5450 IF p_user_in_rec.LOAD_TENDER_STATUS <> FND_API.G_MISS_CHAR
5451 AND NVL(p_user_in_rec.LOAD_TENDER_STATUS,'!!!') <> NVL(p_out_rec.LOAD_TENDER_STATUS,'!!!')
5452 THEN
5453 IF l_attributes IS NOT NULL THEN
5454 l_attributes := l_attributes || ', ';
5455 END IF;
5456 l_attributes := l_attributes || 'LOAD_TENDER_STATUS';
5457 END IF;
5458
5459 IF p_user_in_rec.ROUTE_LANE_ID <> FND_API.G_MISS_NUM
5460 AND NVL(p_user_in_rec.ROUTE_LANE_ID,-99) <> NVL(p_out_rec.ROUTE_LANE_ID,-99)
5461 THEN
5462 IF l_attributes IS NOT NULL THEN
5463 l_attributes := l_attributes || ', ';
5464 END IF;
5465 l_attributes := l_attributes || 'ROUTE_LANE_ID';
5466 END IF;
5467
5468 IF p_user_in_rec.LANE_ID <> FND_API.G_MISS_NUM
5469 AND NVL(p_user_in_rec.LANE_ID,-99) <> NVL(p_out_rec.LANE_ID,-99)
5470 THEN
5471 IF l_attributes IS NOT NULL THEN
5472 l_attributes := l_attributes || ', ';
5473 END IF;
5474 l_attributes := l_attributes || 'LANE_ID';
5475 END IF;
5476
5477 IF p_user_in_rec.SCHEDULE_ID <> FND_API.G_MISS_NUM
5478 AND NVL(p_user_in_rec.SCHEDULE_ID,-99) <> NVL(p_out_rec.SCHEDULE_ID,-99)
5479 THEN
5480 IF l_attributes IS NOT NULL THEN
5481 l_attributes := l_attributes || ', ';
5482 END IF;
5483 l_attributes := l_attributes || 'SCHEDULE_ID';
5484 END IF;
5485
5486 IF p_user_in_rec.BOOKING_NUMBER <> FND_API.G_MISS_CHAR
5487 AND NVL(p_user_in_rec.BOOKING_NUMBER,'!!!') <> NVL(p_out_rec.BOOKING_NUMBER,'!!!')
5488 THEN
5489 IF l_attributes IS NOT NULL THEN
5490 l_attributes := l_attributes || ', ';
5491 END IF;
5492 l_attributes := l_attributes || 'BOOKING_NUMBER';
5493 END IF;
5494
5495 IF p_user_in_rec.ROWID <> FND_API.G_MISS_CHAR
5496 AND NVL(p_user_in_rec.ROWID,'!!!') <> NVL(p_out_rec.ROWID,'!!!')
5497 THEN
5498 IF l_attributes IS NOT NULL THEN
5499 l_attributes := l_attributes || ', ';
5500 END IF;
5501 l_attributes := l_attributes || 'ROWID';
5502 END IF;
5503
5504 IF p_user_in_rec.ARRIVE_AFTER_TRIP_NAME <> FND_API.G_MISS_CHAR
5505 AND NVL(p_user_in_rec.ARRIVE_AFTER_TRIP_NAME,'!!!') <> NVL(p_out_rec.ARRIVE_AFTER_TRIP_NAME,'!!!')
5506 THEN
5507 IF l_attributes IS NOT NULL THEN
5508 l_attributes := l_attributes || ', ';
5509 END IF;
5510 l_attributes := l_attributes || 'ARRIVE_AFTER_TRIP_NAME';
5511 END IF;
5512
5513 IF p_user_in_rec.SHIP_METHOD_NAME <> FND_API.G_MISS_CHAR
5514 AND NVL(p_user_in_rec.SHIP_METHOD_NAME,'!!!') <> NVL(p_out_rec.SHIP_METHOD_NAME,'!!!')
5515 THEN
5516 IF l_attributes IS NOT NULL THEN
5517 l_attributes := l_attributes || ', ';
5518 END IF;
5519 l_attributes := l_attributes || 'SHIP_METHOD_NAME';
5520 END IF;
5521
5522 IF p_user_in_rec.VEHICLE_ITEM_DESC <> FND_API.G_MISS_CHAR
5523 AND NVL(p_user_in_rec.VEHICLE_ITEM_DESC,'!!!') <> NVL(p_out_rec.VEHICLE_ITEM_DESC,'!!!')
5524 THEN
5525 IF l_attributes IS NOT NULL THEN
5526 l_attributes := l_attributes || ', ';
5527 END IF;
5528 l_attributes := l_attributes || 'VEHICLE_ITEM_DESC';
5529 END IF;
5530
5531 IF p_user_in_rec.VEHICLE_ORGANIZATION_CODE <> FND_API.G_MISS_CHAR
5532 AND NVL(p_user_in_rec.VEHICLE_ORGANIZATION_CODE,'!!!') <> NVL(p_out_rec.VEHICLE_ORGANIZATION_CODE,'!!!')
5533 THEN
5534 IF l_attributes IS NOT NULL THEN
5535 l_attributes := l_attributes || ', ';
5536 END IF;
5537 l_attributes := l_attributes || 'VEHICLE_ORGANIZATION_CODE';
5538 END IF;
5539
5540 IF p_user_in_rec.LOAD_TENDER_NUMBER <> FND_API.G_MISS_NUM
5541 AND NVL(p_user_in_rec.LOAD_TENDER_NUMBER,-99) <> NVL(p_out_rec.LOAD_TENDER_NUMBER,-99)
5542 THEN
5543 IF l_attributes IS NOT NULL THEN
5544 l_attributes := l_attributes || ', ';
5545 END IF;
5546 l_attributes := l_attributes || 'LOAD_TENDER_NUMBER';
5547 END IF;
5548
5549 IF p_user_in_rec.VESSEL <> FND_API.G_MISS_CHAR
5550 AND NVL(p_user_in_rec.VESSEL,'!!!') <> NVL(p_out_rec.VESSEL,'!!!')
5551 THEN
5552 IF l_attributes IS NOT NULL THEN
5553 l_attributes := l_attributes || ', ';
5554 END IF;
5555 l_attributes := l_attributes || 'VESSEL';
5556 END IF;
5557
5558 IF p_user_in_rec.VOYAGE_NUMBER <> FND_API.G_MISS_CHAR
5559 AND NVL(p_user_in_rec.VOYAGE_NUMBER,'!!!') <> NVL(p_out_rec.VOYAGE_NUMBER,'!!!')
5560 THEN
5561 IF l_attributes IS NOT NULL THEN
5562 l_attributes := l_attributes || ', ';
5563 END IF;
5564 l_attributes := l_attributes || 'VOYAGE_NUMBER';
5565 END IF;
5566
5567 IF p_user_in_rec.PORT_OF_LOADING <> FND_API.G_MISS_CHAR
5568 AND NVL(p_user_in_rec.PORT_OF_LOADING,'!!!') <> NVL(p_out_rec.PORT_OF_LOADING,'!!!')
5569 THEN
5570 IF l_attributes IS NOT NULL THEN
5571 l_attributes := l_attributes || ', ';
5572 END IF;
5573 l_attributes := l_attributes || 'PORT_OF_LOADING';
5574 END IF;
5575
5576 IF p_user_in_rec.PORT_OF_DISCHARGE <> FND_API.G_MISS_CHAR
5577 AND NVL(p_user_in_rec.PORT_OF_DISCHARGE,'!!!') <> NVL(p_out_rec.PORT_OF_DISCHARGE,'!!!')
5578 THEN
5579 IF l_attributes IS NOT NULL THEN
5580 l_attributes := l_attributes || ', ';
5581 END IF;
5582 l_attributes := l_attributes || 'PORT_OF_DISCHARGE';
5583 END IF;
5584
5585 IF p_user_in_rec.WF_NAME <> FND_API.G_MISS_CHAR
5586 AND NVL(p_user_in_rec.WF_NAME,'!!!') <> NVL(p_out_rec.WF_NAME,'!!!')
5587 THEN
5588 IF l_attributes IS NOT NULL THEN
5589 l_attributes := l_attributes || ', ';
5590 END IF;
5591 l_attributes := l_attributes || 'WF_NAME';
5592 END IF;
5593
5594 IF p_user_in_rec.WF_PROCESS_NAME <> FND_API.G_MISS_CHAR
5595 AND NVL(p_user_in_rec.WF_PROCESS_NAME,'!!!') <> NVL(p_out_rec.WF_PROCESS_NAME,'!!!')
5596 THEN
5597 IF l_attributes IS NOT NULL THEN
5598 l_attributes := l_attributes || ', ';
5599 END IF;
5600 l_attributes := l_attributes || 'WF_PROCESS_NAME';
5601 END IF;
5602
5603 IF p_user_in_rec.WF_ITEM_KEY <> FND_API.G_MISS_CHAR
5604 AND NVL(p_user_in_rec.WF_ITEM_KEY,'!!!') <> NVL(p_out_rec.WF_ITEM_KEY,'!!!')
5605 THEN
5606 IF l_attributes IS NOT NULL THEN
5607 l_attributes := l_attributes || ', ';
5608 END IF;
5609 l_attributes := l_attributes || 'WF_ITEM_KEY';
5610 END IF;
5611
5612 IF p_user_in_rec.CARRIER_CONTACT_ID <> FND_API.G_MISS_NUM
5613 AND NVL(p_user_in_rec.CARRIER_CONTACT_ID,-99) <> NVL(p_out_rec.CARRIER_CONTACT_ID,-99)
5614 THEN
5615 IF l_attributes IS NOT NULL THEN
5616 l_attributes := l_attributes || ', ';
5617 END IF;
5618 l_attributes := l_attributes || 'CARRIER_CONTACT_ID';
5619 END IF;
5620
5621 IF p_user_in_rec.SHIPPER_WAIT_TIME <> FND_API.G_MISS_NUM
5622 AND NVL(p_user_in_rec.SHIPPER_WAIT_TIME,-99) <> NVL(p_out_rec.SHIPPER_WAIT_TIME,-99)
5623 THEN
5624 IF l_attributes IS NOT NULL THEN
5625 l_attributes := l_attributes || ', ';
5626 END IF;
5627 l_attributes := l_attributes || 'SHIPPER_WAIT_TIME';
5628 END IF;
5629
5630 IF p_user_in_rec.WAIT_TIME_UOM <> FND_API.G_MISS_CHAR
5631 AND NVL(p_user_in_rec.WAIT_TIME_UOM,'!!!') <> NVL(p_out_rec.WAIT_TIME_UOM,'!!!')
5632 THEN
5633 IF l_attributes IS NOT NULL THEN
5634 l_attributes := l_attributes || ', ';
5635 END IF;
5636 l_attributes := l_attributes || 'WAIT_TIME_UOM';
5637 END IF;
5638
5639 IF p_user_in_rec.LOAD_TENDERED_TIME <> FND_API.G_MISS_DATE
5640 AND NVL(p_user_in_rec.LOAD_TENDERED_TIME,TO_DATE('2','j')) <> NVL(p_out_rec.LOAD_TENDERED_TIME,TO_DATE('2','j'))
5641 THEN
5642 IF l_attributes IS NOT NULL THEN
5643 l_attributes := l_attributes || ', ';
5644 END IF;
5645 l_attributes := l_attributes || 'LOAD_TENDERED_TIME';
5646 END IF;
5647
5648 IF p_user_in_rec.CARRIER_RESPONSE <> FND_API.G_MISS_CHAR
5649 AND NVL(p_user_in_rec.CARRIER_RESPONSE,'!!!') <> NVL(p_out_rec.CARRIER_RESPONSE,'!!!')
5650 THEN
5651 IF l_attributes IS NOT NULL THEN
5652 l_attributes := l_attributes || ', ';
5653 END IF;
5654 l_attributes := l_attributes || 'CARRIER_RESPONSE';
5655 END IF;
5656
5657 IF p_user_in_rec.SHIPMENTS_TYPE_FLAG <> FND_API.G_MISS_CHAR
5658 AND NVL(p_user_in_rec.SHIPMENTS_TYPE_FLAG,'!!!') <> NVL(p_out_rec.SHIPMENTS_TYPE_FLAG,'!!!')
5659 THEN
5660 IF l_attributes IS NOT NULL THEN
5661 l_attributes := l_attributes || ', ';
5662 END IF;
5663 l_attributes := l_attributes || 'SHIPMENTS_TYPE_FLAG';
5664 END IF;
5665
5666 IF p_user_in_rec.IGNORE_FOR_PLANNING <> FND_API.G_MISS_CHAR
5667 AND NVL(p_user_in_rec.IGNORE_FOR_PLANNING,'!!!') <> NVL(p_out_rec.IGNORE_FOR_PLANNING,'!!!')
5668 THEN
5669 IF l_attributes IS NOT NULL THEN
5670 l_attributes := l_attributes || ', ';
5671 END IF;
5672 l_attributes := l_attributes || 'IGNORE_FOR_PLANNING';
5673 END IF;
5674
5675 IF p_user_in_rec.TP_PLAN_NAME <> FND_API.G_MISS_CHAR
5676 AND NVL(p_user_in_rec.TP_PLAN_NAME,'!!!') <> NVL(p_out_rec.TP_PLAN_NAME,'!!!')
5677 THEN
5678 IF l_attributes IS NOT NULL THEN
5679 l_attributes := l_attributes || ', ';
5680 END IF;
5681 l_attributes := l_attributes || 'TP_PLAN_NAME';
5682 END IF;
5683
5684 IF p_user_in_rec.TP_TRIP_NUMBER <> FND_API.G_MISS_NUM
5685 AND NVL(p_user_in_rec.TP_TRIP_NUMBER,-99) <> NVL(p_out_rec.TP_TRIP_NUMBER,-99)
5686 THEN
5687 IF l_attributes IS NOT NULL THEN
5688 l_attributes := l_attributes || ', ';
5689 END IF;
5690 l_attributes := l_attributes || 'TP_TRIP_NUMBER';
5691 END IF;
5692
5693 IF p_user_in_rec.SEAL_CODE <> FND_API.G_MISS_CHAR
5694 AND NVL(p_user_in_rec.SEAL_CODE,'!!!') <> NVL(p_out_rec.SEAL_CODE,'!!!')
5695 THEN
5696 IF l_attributes IS NOT NULL THEN
5697 l_attributes := l_attributes || ', ';
5698 END IF;
5699 l_attributes := l_attributes || 'SEAL_CODE';
5700 END IF;
5701
5702 IF p_user_in_rec.OPERATOR <> FND_API.G_MISS_CHAR
5703 AND NVL(p_user_in_rec.OPERATOR,'!!!') <> NVL(p_out_rec.OPERATOR,'!!!')
5704 THEN
5705 IF l_attributes IS NOT NULL THEN
5706 l_attributes := l_attributes || ', ';
5707 END IF;
5708 l_attributes := l_attributes || 'OPERATOR';
5709 END IF;
5710
5711
5712 IF p_user_in_rec.CONSIGNEE_CARRIER_AC_NO <> FND_API.G_MISS_CHAR
5713 AND NVL(p_user_in_rec.CONSIGNEE_CARRIER_AC_NO,'!!!') <> NVL(p_out_rec.CONSIGNEE_CARRIER_AC_NO,'!!!')
5714 THEN
5715 IF l_attributes IS NOT NULL THEN
5716 l_attributes := l_attributes || ', ';
5717 END IF;
5718 l_attributes := l_attributes || 'CONSIGNEE_CARRIER_AC_NO';
5719 END IF;
5720
5721 IF p_user_in_rec.CARRIER_REFERENCE_NUMBER <> FND_API.G_MISS_CHAR
5722 AND NVL(p_user_in_rec.CARRIER_REFERENCE_NUMBER,'!!!') <> NVL(p_out_rec.CARRIER_REFERENCE_NUMBER,'!!!')
5723 THEN
5724 IF l_attributes IS NOT NULL THEN
5725 l_attributes := l_attributes || ', ';
5726 END IF;
5727 l_attributes := l_attributes || 'CARRIER_REFERENCE_NUMBER';
5728 END IF;
5729
5730 IF p_user_in_rec.APPEND_FLAG <> FND_API.G_MISS_CHAR
5731 AND NVL(p_user_in_rec.APPEND_FLAG,'!!!') <> NVL(p_out_rec.APPEND_FLAG,'!!!')
5732 THEN
5733 IF l_attributes IS NOT NULL THEN
5734 l_attributes := l_attributes || ', ';
5735 END IF;
5736 l_attributes := l_attributes || 'APPEND_FLAG';
5737 END IF;
5738
5739
5740 IF p_user_in_rec.RANK_ID <> FND_API.G_MISS_NUM
5741 AND NVL(p_user_in_rec.RANK_ID, -99) <> NVL(p_out_rec.RANK_ID,-99)
5742 THEN
5743 IF l_attributes IS NOT NULL THEN
5744 l_attributes := l_attributes || ', ';
5745 END IF;
5746 l_attributes := l_attributes || 'RANK_ID';
5747 END IF;
5748
5749 IF p_user_in_rec.ROUTING_RULE_ID <> FND_API.G_MISS_NUM
5750 AND NVL(p_user_in_rec.ROUTING_RULE_ID, -99) <> NVL(p_out_rec.ROUTING_RULE_ID,-99)
5751 THEN
5752 IF l_attributes IS NOT NULL THEN
5753 l_attributes := l_attributes || ', ';
5754 END IF;
5755 l_attributes := l_attributes || 'ROUTING_RULE_ID';
5756 END IF;
5757
5758
5759 IF l_debug_on THEN
5760 WSH_DEBUG_SV.log(l_module_name,'l_attributes',l_attributes);
5761 WSH_DEBUG_SV.log(l_module_name,'length(l_attributes)',length(l_attributes));
5762 END IF;
5763
5764
5765 IF l_attributes IS NULL THEN
5766 --no message to be shown to the user
5767 IF l_debug_on THEN
5768 WSH_DEBUG_SV.pop(l_module_name);
5769 END IF;
5770 RETURN;
5771 --
5772 ELSE
5773 Wsh_Utilities.process_message(
5774 p_entity => 'TRIP',
5775 p_entity_name => NVL(p_out_rec.NAME,p_out_rec.TRIP_ID),
5776 p_attributes => l_attributes,
5777 x_return_status => l_return_status
5778 );
5779
5780 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
5781 THEN
5782 x_return_status := l_return_status;
5783 IF l_debug_on THEN
5784 wsh_debug_sv.logmsg(l_module_name,'Error returned by wsh_utilities.process_message',WSH_DEBUG_SV.C_PROC_LEVEL);
5785 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5786 wsh_debug_sv.pop(l_module_name);
5787 END IF;
5788 return;
5789 ELSE
5790 x_return_status := wsh_util_core.G_RET_STS_WARNING;
5791 END IF;
5792 END IF;
5793
5794
5795
5796 IF l_debug_on THEN
5797 WSH_DEBUG_SV.pop(l_module_name);
5798 END IF;
5799 --
5800
5801
5802 EXCEPTION
5803 WHEN OTHERS THEN
5804 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
5805 --
5806 IF l_debug_on THEN
5807 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5808 END IF;
5809 --
5810 END user_non_updatable_columns;
5811
5812 -- bug 3516052
5813 -- -----------------------------------------------------------------------
5814 -- Name
5815 -- PROCEDURE Validate_Stop_Dates
5816 -- Purpose
5817 -- This procedure is to validate the planned dates of the stops in the trip.
5818 -- It makes sure that the planned departure date of a stop precedes the
5819 -- planned arrival date of the next stop. It should be called after
5820 -- the date changes have been applied to database.
5821 -- Bug 3782135
5822 -- Planned Arrival Dates across stops should be in Order, in sync
5823 -- with Stop Sequence Number(Inbound - Mixed trips, Outbound)
5824 --
5825 -- Input Parameters:
5826 -- p_trip_id : The trip to be validated
5827 --
5828 --
5829 -- Output Parameters:
5830 -- x_return_status - Success, Warning, Error, Unexpected Error
5831 -- ----------------------------------------------------------------------
5832
5833 PROCEDURE Validate_Stop_Dates
5834 ( p_trip_id IN NUMBER,
5835 x_return_status OUT NOCOPY VARCHAR2,
5836 --tkt
5837 p_caller IN VARCHAR2
5838 )
5839 IS
5840
5841 -- Bug 4036204: cur.planned_departure_date =< nxt.planned_arrival_date is OK
5842 -- as long as cur.planned_arrival_date < nxt.planned_arrival_date
5843 CURSOR get_invalid_planned_dates IS
5844 SELECT cur.stop_location_id cur_location_id,
5845 cur.physical_stop_id,
5846 cur.planned_arrival_date cur_arr_date,
5847 cur.planned_departure_date cur_dep_date,
5848 nxt.stop_location_id nxt_location_id,
5849 nxt.planned_arrival_date nxt_arr_date
5850 FROM wsh_trip_stops cur, wsh_trip_stops nxt
5851 WHERE cur.trip_id = p_trip_id
5852 AND cur.trip_id = nxt.trip_id
5853 AND cur.stop_id <> nxt.stop_id
5854 AND (( cur.planned_departure_date > nxt.planned_arrival_date
5855 AND cur.planned_arrival_date < nxt.planned_arrival_date
5856 AND cur.status_code = 'OP'
5857 AND nxt.status_code = 'OP')
5858 OR ( cur.planned_arrival_date = nxt.planned_arrival_date
5859 AND cur.status_code = 'OP'
5860 AND nxt.status_code = 'OP')
5861 OR ( cur.physical_stop_id = nxt.stop_id
5862 AND cur.stop_sequence_number + 1 <> nxt.stop_sequence_number
5863 ))
5864 AND rownum = 1;
5865
5866 l_invalid_planned_date get_invalid_planned_dates%ROWTYPE;
5867
5868 -- Bug 3782135, try to find a stop with invalid planned arrival date
5869 -- This cursor validates the planned arrival dates across stops
5870 -- within a trip (valid for inbound mixed trips as well as outbound)
5871 -- Compare the open stops with closed/arrived stops
5872 -- on same trip
5873 -- where open stop's sequence number > close/arrived stop's seq. number
5874 -- and open stops's pl. arr. date <= closed/arrived stops' pl arr date
5875 -- Select statement below makes sure no open stop has a planned arrival
5876 -- date which is earlier than a closed/arrived stop's planned arrival date
5877 -- Check for upper limit is in WSHSTTHB.pls - get_disabled_list
5878 -- and WSHSTACB.pls - reset_stop_seq_numbers
5879 -- Outbound Scenario, select statement
5880 -- Stop id Sequence Status
5881 -- 10 1 CL
5882 -- 20 2 AR
5883 -- 30 3 OP
5884 -- Inbound, Mixed Trip scenario, Handled in WSHSTTHB.pls - get_disabled_list
5885 -- Stop id Sequence Status
5886 -- 10 1 CL
5887 -- 20 2 OP
5888 -- 30 3 CL
5889 -- 40 4 OP
5890 -- 50 5 OP
5891 -- 60 6 CL
5892 CURSOR get_invalid_plarrival_dates IS
5893 SELECT cur.planned_arrival_date cur_arr_date,
5894 cur.stop_location_id cur_location_id,
5895 cur.stop_id cur_stop_id,
5896 cur.stop_sequence_number cur_stop_seq_num,
5897 prv.planned_arrival_date prv_arr_date
5898 FROM wsh_trip_stops cur,
5899 wsh_trip_stops prv
5900 WHERE cur.trip_id = p_trip_id
5901 AND cur.trip_id = prv.trip_id
5902 AND cur.stop_id <> prv.stop_id
5903 AND cur.planned_arrival_date <= prv.planned_arrival_date
5904 AND cur.status_code = 'OP'
5905 AND prv.status_code IN ('AR','CL')
5906 AND cur.stop_sequence_number > prv.stop_sequence_number
5907 AND rownum = 1;
5908
5909 l_invalid_plarrival_date get_invalid_plarrival_dates%ROWTYPE;
5910 l_found BOOLEAN;
5911 -- End of Bug 3782135
5912
5913 CURSOR get_deliveries_to_unassign IS
5914 SELECT leg.delivery_id
5915 FROM wsh_delivery_legs leg,
5916 wsh_trip_stops pickup,
5917 wsh_trip_stops dropoff
5918 WHERE pickup.trip_id = p_trip_id
5919 AND pickup.trip_id = dropoff.trip_id
5920 AND pickup.stop_id <> dropoff.stop_id
5921 AND pickup.status_code = 'OP'
5922 AND dropoff.status_code = 'OP'
5923 AND leg.pick_up_stop_id = pickup.stop_id
5924 AND leg.drop_off_stop_id = dropoff.stop_id
5925 AND dropoff.planned_arrival_date <= pickup.planned_arrival_date;
5926
5927 l_num_warn NUMBER;
5928 l_num_warn_total NUMBER;
5929 l_debug_on BOOLEAN;
5930 l_del_to_unassign WSH_UTIL_CORE.Id_Tab_Type;
5931 l_return_status VARCHAR2(1);
5932 --
5933 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Validate_Stop_Dates';
5934 invalid_input EXCEPTION;
5935 invalid_stop_date EXCEPTION;
5936 invalid_arrival_date EXCEPTION;
5937 i NUMBER;
5938 l_batchsize NUMBER;
5939
5940 BEGIN
5941
5942 -- initialize variables
5943 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5944 l_del_to_unassign.delete;
5945 l_num_warn := 0;
5946 l_batchsize := 1000;
5947 l_num_warn_total := 0;
5948 --
5949 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5950 --
5951
5952 IF l_debug_on IS NULL THEN
5953 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5954 END IF;
5955
5956 --
5957 IF l_debug_on THEN
5958 WSH_DEBUG_SV.push(l_module_name);
5959 WSH_DEBUG_SV.log(l_module_name,'Trip ID ', p_trip_id);
5960 END IF;
5961
5962 -- SSN Change
5963 -- Date validations are applicable only for Profile option = PAD and not if it is set to SSN
5964 IF WSH_TRIPS_ACTIONS.GET_STOP_SEQ_MODE = WSH_INTERFACE_GRP.G_STOP_SEQ_MODE_SSN THEN
5965 IF l_debug_on THEN
5966 WSH_DEBUG_SV.logmsg(l_module_name,'Stop Sequence Mode is SSN');
5967 WSH_DEBUG_SV.pop(l_module_name);
5968 END IF;
5969 RETURN; -- these validations are not required for profile option of SSN
5970 END IF;
5971
5972 IF p_trip_id is NULL THEN
5973 raise invalid_input;
5974 END IF;
5975
5976
5977 OPEN get_invalid_planned_dates;
5978 FETCH get_invalid_planned_dates INTO l_invalid_planned_date;
5979
5980 IF get_invalid_planned_dates%FOUND THEN
5981 raise invalid_stop_date;
5982 END IF;
5983 CLOSE get_invalid_planned_dates;
5984
5985 -- Bug 3782135
5986 IF l_debug_on THEN
5987 WSH_DEBUG_SV.log(l_module_name,'Before Validating Arrival Dates',p_trip_id);
5988 END IF;
5989
5990 OPEN get_invalid_plarrival_dates;
5991 FETCH get_invalid_plarrival_dates
5992 INTO l_invalid_plarrival_date;
5993 l_found := get_invalid_plarrival_dates%FOUND;
5994 CLOSE get_invalid_plarrival_dates;
5995 IF l_debug_on THEN
5996 WSH_DEBUG_SV.log(l_module_name,'After Validating Arrival Dates',p_trip_id);
5997 END IF;
5998
5999 IF l_found THEN
6000 IF l_debug_on THEN
6001 WSH_DEBUG_SV.log(l_module_name,'Stop ID',l_invalid_plarrival_date.cur_stop_id);
6002 WSH_DEBUG_SV.log(l_module_name,'Seq',l_invalid_plarrival_date.cur_stop_seq_num);
6003 END IF;
6004 raise invalid_arrival_date;
6005 END IF;
6006 -- End of Bug 3782135
6007
6008 -- bulk collection
6009
6010 OPEN get_deliveries_to_unassign;
6011 LOOP
6012 l_num_warn := 0;
6013
6014 FETCH get_deliveries_to_unassign BULK COLLECT INTO l_del_to_unassign LIMIT l_batchsize ;
6015 IF l_del_to_unassign.count > 0 THEN
6016
6017 i := l_del_to_unassign.first;
6018 WHILE i is not NULL LOOP
6019
6020 IF l_debug_on THEN
6021 WSH_DEBUG_SV.logmsg(l_module_name,'unassign delivery '|| l_del_to_unassign(i));
6022 END IF;
6023
6024 FND_MESSAGE.SET_NAME('WSH','WSH_DEL_UNASSIGN_TRIP');
6025 FND_MESSAGE.SET_TOKEN('DELIVERY_NAME',
6026 WSH_NEW_DELIVERIES_PVT.get_name(l_del_to_unassign(i)));
6027 FND_MESSAGE.SET_TOKEN('TRIP_NAME',
6028 WSH_TRIPS_PVT.get_name(p_trip_id));
6029 l_num_warn := l_num_warn + 1;
6030 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING);
6031
6032 i := l_del_to_unassign.next(i);
6033 END LOOP;
6034
6035 IF l_debug_on THEN
6036 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_ACTIONS.UNASSIGN_TRIP',WSH_DEBUG_SV.C_PROC_LEVEL);
6037 END IF;
6038
6039 WSH_TRIPS_ACTIONS.unassign_trip
6040 (p_del_rows => l_del_to_unassign,
6041 p_trip_id => p_trip_id,
6042 x_return_status => l_return_status);
6043
6044 IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
6045 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6046 IF l_debug_on THEN
6047 WSH_DEBUG_SV.pop(l_module_name);
6048 END IF;
6049 return;
6050 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
6051 l_num_warn := l_num_warn + 1;
6052 END IF;
6053
6054 l_num_warn_total := l_num_warn_total + 1;
6055 l_del_to_unassign.delete;
6056
6057 END IF;
6058
6059 EXIT WHEN get_deliveries_to_unassign%NOTFOUND;
6060 END LOOP;
6061 CLOSE get_deliveries_to_unassign;
6062
6063 -- bulk collection
6064
6065 IF l_num_warn_total > 0 THEN
6066 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6067 END IF;
6068
6069 IF l_debug_on THEN
6070 WSH_DEBUG_SV.pop(l_module_name);
6071 END IF;
6072
6073 EXCEPTION
6074
6075 WHEN invalid_input THEN
6076 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6077 FND_MESSAGE.SET_NAME('WSH', 'WSH_TRIP_ID_REQUIRED');
6078 wsh_util_core.add_message(x_return_status, l_module_name);
6079 IF l_debug_on THEN
6080 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR: Trip ID is NULL');
6081 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_input');
6082 END IF;
6083
6084 WHEN invalid_stop_date THEN
6085 IF get_invalid_planned_dates%ISOPEN THEN
6086 close get_invalid_planned_dates;
6087 END IF;
6088 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6089 IF l_invalid_planned_date.physical_stop_id is NULL THEN
6090 FND_MESSAGE.SET_NAME('WSH', 'WSH_STOP_DATE_OVERLAP');
6091 FND_MESSAGE.SET_TOKEN('CURRENT_STOP_DATE', fnd_date.date_to_displaydt(l_invalid_planned_date.cur_dep_date));
6092 FND_MESSAGE.SET_TOKEN('CURRENT_LOCATION_DESP',
6093 WSH_UTIL_CORE.Get_Location_Description (l_invalid_planned_date.cur_location_id,'NEW UI CODE INFO'));
6094 FND_MESSAGE.SET_TOKEN('NEXT_STOP_DATE', fnd_date.date_to_displaydt(l_invalid_planned_date.nxt_arr_date));
6095 ELSE
6096 -- No stop is allowed between dummy stop and physical stop.
6097 FND_MESSAGE.SET_NAME('WSH', 'WSH_BETWEEN_LINKED_STOPS');
6098 FND_MESSAGE.SET_TOKEN('DUMMY_STOP_DATE', fnd_date.date_to_displaydt(l_invalid_planned_date.cur_arr_date));
6099 FND_MESSAGE.SET_TOKEN('DUMMY_LOCATION_DESP',
6100 WSH_UTIL_CORE.Get_Location_Description (l_invalid_planned_date.cur_location_id,'NEW UI CODE INFO'));
6101 FND_MESSAGE.SET_TOKEN('PHYSICAL_STOP_DATE', fnd_date.date_to_displaydt(l_invalid_planned_date.nxt_arr_date));
6102 FND_MESSAGE.SET_TOKEN('PHYSICAL_LOCATION_DESP',
6103 WSH_UTIL_CORE.Get_Location_Description (l_invalid_planned_date.nxt_location_id,'NEW UI CODE INFO'));
6104 END IF;
6105 wsh_util_core.add_message(x_return_status, l_module_name);
6106
6107 IF l_debug_on THEN
6108 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_stop_date');
6109 END IF;
6110
6111 -- Bug 3782135
6112 WHEN invalid_arrival_date THEN
6113 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6114
6115 FND_MESSAGE.SET_NAME('WSH', 'WSH_INVALID_STOP_DATE');
6116 FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(l_invalid_plarrival_date.cur_stop_id,p_caller));
6117 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
6118
6119 IF l_debug_on THEN
6120 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_planned_arrival_date');
6121 END IF;
6122
6123 -- End of Bug 3782135
6124 WHEN Others THEN
6125 IF get_invalid_planned_dates%ISOPEN THEN
6126 close get_invalid_planned_dates;
6127 END IF;
6128 -- Bug 3782135
6129 IF get_invalid_plarrival_dates%ISOPEN THEN
6130 close get_invalid_plarrival_dates;
6131 END IF;
6132 -- End of Bug 3782135
6133
6134 IF get_deliveries_to_unassign%ISOPEN THEN
6135 close get_deliveries_to_unassign;
6136 END IF;
6137 wsh_util_core.default_handler('WSH_TRIP_STOPS_VALIDATIONS.Validate_Stop_Dates',l_module_name);
6138 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6139 IF l_debug_on THEN
6140 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
6141 END IF;
6142
6143 END Validate_Stop_Dates;
6144
6145 END WSH_TRIP_VALIDATIONS;
[Home] [Help]