1 PACKAGE BODY WSH_TRIP_VALIDATIONS as
2 /* $Header: WSHTRVLB.pls 120.18 2011/02/21 09:35:54 brana 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 NUMBER := NULL; --Bug 9308056 Changed data type to number from binary integer.
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 NVL(mcp.otm_enabled,wsp.otm_enabled) otm_enabled, -- OTM R12, glog proj-- LSP PROJECT : checking OTM enabled flag on client parameters.
2137 mcp.client_id -- LSP PROJECT
2138 FROM wsh_new_deliveries wnd,
2139 wsh_delivery_legs wdl,
2140 wsh_trip_stops wts1,
2141 wsh_trip_stops wts2,
2142 wsh_trips wt,
2143 wsh_shipping_parameters wsp, -- OTM R12, glog proj
2144 mtl_client_parameters_v mcp -- LSP PROJECT
2145 WHERE wnd.delivery_id = wdl.delivery_id
2146 AND wts1.stop_id = wdl.PICK_UP_STOP_ID
2147 AND wts2.stop_id = wdl.DROP_OFF_STOP_ID
2148 AND wts1.trip_id = wt.trip_id
2149 AND wts2.trip_id = wt.trip_id
2150 AND wt.trip_id = p_trip_id
2151 AND wsp.organization_id = wnd.organization_id -- OTM R12, glog proj
2152 AND wnd.client_id = mcp.client_id (+); -- LSP PROJECT
2153
2154 l_trip_actions_tab TripActionsTabType;
2155 l_valid_ids wsh_util_core.id_tab_type;
2156 l_error_ids wsh_util_core.id_tab_type;
2157 l_valid_index_tab wsh_util_core.id_tab_type;
2158 l_dlvy_rec_tab WSH_DELIVERY_VALIDATIONS.dlvy_rec_tab_type;
2159 l_move_id NUMBER:= NULL;
2160
2161 l_pass_section_a VARCHAR2(1):='Y';
2162 l_tpw_temp VARCHAR2(1);
2163 l_status_code VARCHAR2(1);
2164 l_return_status VARCHAR2(1);
2165 error_in_init_actions EXCEPTION;
2166 e_set_messages EXCEPTION;
2167
2168 l_debug_on BOOLEAN;
2169 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'IS_ACTION_ENABLED';
2170 --
2171 l_caller VARCHAR2(100);
2172
2173 l_loop_counter NUMBER; --OTM R12, glog proj
2174
2175 BEGIN
2176 --
2177 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2178 --
2179 IF l_debug_on IS NULL
2180 THEN
2181 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2182 END IF;
2183 --
2184 IF l_debug_on THEN
2185 WSH_DEBUG_SV.push(l_module_name);
2186 --
2187 WSH_DEBUG_SV.log(l_module_name,'p_caller',p_caller);
2188 WSH_DEBUG_SV.log(l_module_name,'p_action',p_action);
2189 END IF;
2190
2191 -- call initialize API and get the pl/sql table
2192 Init_Trip_Actions_Tbl(
2193 p_action => p_action,
2194 x_trip_actions_tab => l_trip_actions_tab,
2195 x_return_status => x_return_status);
2196
2197 IF l_debug_on THEN
2198 WSH_DEBUG_SV.log(l_module_name,'Init_Detail_Actions_Tbl x_return_status',x_return_status);
2199 END IF;
2200
2201 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
2202 raise error_in_init_actions;
2203 END IF;
2204
2205
2206 FOR j IN p_trip_rec_tab.FIRST..p_trip_rec_tab.LAST LOOP
2207 BEGIN
2208
2209 -- J-IB-NPARIKH-{
2210 --
2211 l_caller := p_caller;
2212 --
2213 --
2214 IF l_debug_on THEN
2215 wsh_debug_sv.log(l_module_name, 'l_caller', l_caller);
2216 END IF;
2217 --
2218 --
2219 -- Actions on inbound trips are allowed only if caller
2220 -- starts with one of the following:
2221 -- - FTE
2222 -- - WSH_IB
2223 -- - WSH_PUB
2224 -- - WSH_TP_RELEASE
2225 -- For any other callers, set l_caller to WSH_FSTRX
2226 -- Since for caller, WSH_FSTRX, all actions are disabled
2227 -- on inbound trips
2228 --
2229 --
2230 --
2231 IF nvl(p_trip_rec_tab(j).shipments_type_flag,'O') = 'I'
2232 THEN
2233 --{
2234 IF l_caller LIKE 'FTE%'
2235 OR l_caller LIKE 'WSH_PUB%'
2236 OR l_caller LIKE 'WSH_IB%'
2237 OR l_caller LIKE 'WSH_TP_RELEASE%'
2238 THEN
2239 NULL;
2240 ELSE
2241 l_caller := 'WSH_FSTRX';
2242 END IF;
2243 --}
2244 END IF;
2245 --
2246 --
2247 IF l_debug_on THEN
2248 wsh_debug_sv.log(l_module_name, 'l_caller-modified', l_caller);
2249 END IF;
2250 -- J-IB-NPARIKH-}
2251 --
2252 -- section a
2253 IF ( l_trip_actions_tab.COUNT > 0 ) THEN
2254 l_loop_counter := l_trip_actions_tab.FIRST;
2255 LOOP -- OTM R12, loop modified per standard
2256 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
2257 AND NVL(l_trip_actions_tab(l_loop_counter).planned_flag,NVL(p_trip_rec_tab(j).planned_flag,'N')) =
2258 NVL(p_trip_rec_tab(j).planned_flag,'N')
2259 AND NVL(l_trip_actions_tab(l_loop_counter).load_tender_status,NVL(p_trip_rec_tab(j).load_tender_status,'OPEN')) =
2260 NVL(p_trip_rec_tab(j).load_tender_status,'OPEN') -- 12 SELECT CARRIER
2261 AND NVL(l_trip_actions_tab(l_loop_counter).caller,l_caller) = l_caller -- J-IB-NPARIKH
2262 AND l_trip_actions_tab(l_loop_counter).action_not_allowed = p_action
2263 -- add check to compare shipments_type_flag jckwok
2264 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')
2265 --OTM R12, glog proj
2266 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')
2267 ) THEN
2268
2269 IF l_trip_actions_tab(l_loop_counter).message_name IS NOT NULL THEN
2270 IF l_debug_on THEN
2271 wsh_debug_sv.log(l_module_name, 'Message Name is', l_trip_actions_tab(l_loop_counter).message_name);
2272 END IF;
2273 FND_MESSAGE.SET_NAME('WSH',l_trip_actions_tab(l_loop_counter).message_name);
2274 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error);
2275 END IF;
2276 RAISE e_set_messages;
2277 END IF;
2278 EXIT WHEN l_loop_counter >= l_trip_actions_tab.LAST;
2279 l_loop_counter := l_trip_actions_tab.NEXT(l_loop_counter);
2280 END LOOP;
2281 END IF;
2282
2283 -- section b
2284 IF ( p_action = 'PICK-RELEASE')
2285 OR ( p_action = 'INCLUDE_PLAN')
2286 THEN
2287 FOR cur_rec IN trip_to_del_cur(p_trip_rec_tab(j).trip_id) LOOP
2288 l_dlvy_rec_tab(l_dlvy_rec_tab.count+1) := cur_rec;
2289 END LOOP;
2290
2291 IF (l_dlvy_rec_tab.COUNT>0 AND p_action='INCLUDE_PLAN') OR (p_action='PICK-RELEASE') THEN
2292 WSH_DELIVERY_VALIDATIONS.Is_Action_Enabled(
2293 p_dlvy_rec_tab => l_dlvy_rec_tab,
2294 p_action => p_action,
2295 p_caller => p_caller,
2296 x_return_status => l_return_status,
2297 x_valid_ids => l_valid_ids,
2298 x_error_ids => l_error_ids,
2299 x_valid_index_tab => l_valid_index_tab);
2300
2301 IF l_debug_on THEN
2302 WSH_DEBUG_SV.log(l_module_name,'WSH_DELIVERY_VALIDATIONS.Is_Action_Enabled l_return_status',l_return_status);
2303 END IF;
2304
2305 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS)
2306 AND (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)
2307 THEN
2308 RAISE e_set_messages;
2309 --J-IB-NPARIKH--{
2310 ELSIF p_action = 'INCLUDE_PLAN'
2311 AND l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING
2312 THEN
2313 --
2314 -- If some of the deliveries cannot be included for planning,
2315 -- then trip cannot be included for planning.
2316 --
2317 RAISE e_set_messages;
2318 --J-IB-NPARIKH--}
2319 ELSE
2320 --{
2321 x_valid_ids(x_valid_ids.COUNT + 1) := p_trip_rec_tab(j).trip_id;
2322 x_valid_index_tab(j) := j;
2323 --}
2324 END IF;
2325
2326 ELSE
2327 x_valid_ids(x_valid_ids.COUNT + 1) := p_trip_rec_tab(j).trip_id;
2328 x_valid_index_tab(j) := j;
2329 END IF;--l_dlvy_rec_tab.count>0
2330 ELSIF p_action = 'SELECT-CARRIER' THEN
2331
2332 IF p_trip_rec_tab(j).lane_id IS NOT NULL THEN
2333 IF l_debug_on THEN
2334 wsh_debug_sv.log(l_module_name, 'Trip has lane id : ', p_trip_rec_tab(j).lane_id);
2335 END IF;
2336 FND_MESSAGE.SET_NAME('WSH','WSH_FTE_SEL_TRIP_LANE');
2337 FND_MESSAGE.SET_TOKEN('TRIPID',p_trip_rec_tab(j).trip_id);
2338 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error);
2339 RAISE e_set_messages;
2340 END IF;
2341
2342 -- Perform continuous move check here
2343 l_move_id := WSH_FTE_INTEGRATION.GET_TRIP_MOVE(p_trip_rec_tab(j).trip_id);
2344
2345 --IF l_move_id IS NOT NULL THEN
2346 IF l_move_id <> -1 THEN
2347 IF l_debug_on THEN
2348 wsh_debug_sv.log(l_module_name, 'Trip has continuous move id id : ', l_move_id);
2349 END IF;
2350 FND_MESSAGE.SET_NAME('WSH','WSH_FTE_SEL_TRIP_MOVE');
2351 FND_MESSAGE.SET_TOKEN('TRIPID',p_trip_rec_tab(j).trip_id);
2352 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error);
2353 RAISE e_set_messages;
2354
2355 END IF;
2356 x_valid_ids(x_valid_ids.COUNT + 1) := p_trip_rec_tab(j).trip_id;
2357 x_valid_index_tab(j) := j;
2358
2359 ELSE
2360 x_valid_ids(x_valid_ids.COUNT + 1) := p_trip_rec_tab(j).trip_id;
2361 x_valid_index_tab(j) := j;
2362 END IF;
2363 EXCEPTION
2364 WHEN e_set_messages THEN
2365 x_error_ids(x_error_ids.COUNT + 1) := p_trip_rec_tab(j).trip_id;
2366 IF p_caller = 'WSH_PUB' or p_caller like 'FTE%' THEN
2367 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_ACTION_INELIGIBLE');
2368 FND_MESSAGE.Set_Token('ACTION',wsh_util_core.get_action_meaning('TRIP',p_action));
2369 FND_MESSAGE.Set_Token('TRIP_NAME',
2370 wsh_trips_pvt.get_name(x_error_ids(x_error_ids.COUNT)));
2371 wsh_util_core.add_message('E',l_module_name);
2372 END IF;
2373 END;
2374
2375 END LOOP; -- FOR j IN p_trip_rec_tab.FIRST
2376
2377 IF (x_valid_ids.COUNT = 0 ) THEN
2378 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2379 --
2380 IF NOT (l_caller = 'WSH_PUB' OR l_caller LIKE 'FTE%') THEN
2381 FND_MESSAGE.SET_NAME('WSH','WSH_ACTION_ENABLED');
2382 wsh_util_core.add_message(x_return_status,l_module_name);
2383 END IF;
2384 --
2385 ELSIF (x_valid_ids.COUNT = p_trip_rec_tab.COUNT) THEN
2386 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2387 ELSIF (x_valid_ids.COUNT < p_trip_rec_tab.COUNT ) THEN
2388 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2389 --
2390 IF NOT (l_caller = 'WSH_PUB' OR l_caller LIKE 'FTE%') THEN
2391 FND_MESSAGE.SET_NAME('WSH','WSH_ACTION_ENABLED_WARN');
2392 wsh_util_core.add_message(x_return_status,l_module_name);
2393 END IF;
2394 --
2395 ElSE
2396 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2397 --
2398 IF NOT (l_caller = 'WSH_PUB' OR l_caller LIKE 'FTE%') THEN
2399 FND_MESSAGE.SET_NAME('WSH','WSH_ACTION_ENABLED');
2400 wsh_util_core.add_message(x_return_status,l_module_name);
2401 END IF;
2402 --
2403 END IF;
2404
2405 IF l_debug_on THEN
2406 WSH_DEBUG_SV.pop(l_module_name);
2407 END IF;
2408
2409 EXCEPTION
2410 WHEN error_in_init_actions THEN
2411 IF l_debug_on THEN
2412 WSH_DEBUG_SV.logmsg(l_module_name,'error_in_init_actions exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2413 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:error_in_init_actions');
2414 END IF;
2415
2416 WHEN OTHERS THEN
2417 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.IS_ACTION_ENABLED');
2418 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2419 IF l_debug_on THEN
2420 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
2421 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2422 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2423 END IF;
2424 END Is_Action_Enabled;
2425
2426
2427 PROCEDURE Validate_Arrive_after_trip(
2428 p_trip_id IN NUMBER,
2429 p_arr_after_trip_id IN OUT NOCOPY NUMBER,
2430 p_arr_after_trip_name IN VARCHAR2,
2431 x_return_status OUT NOCOPY VARCHAR2) IS
2432
2433 CURSOR get_trip_id(cp_arr_after_trip_id NUMBER) IS
2434 SELECT 'X'
2435 FROM wsh_trips
2436 WHERE trip_id = cp_arr_after_trip_id
2437 AND status_code <> 'CL';
2438
2439 CURSOR get_trip_name(cp_arr_after_trip_name VARCHAR2) IS
2440 SELECT 'X',trip_id
2441 FROM wsh_trips
2442 WHERE name = cp_arr_after_trip_name
2443 AND status_code <> 'CL';
2444
2445 l_debug_on BOOLEAN;
2446 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_ARRIVE_AFTER_TRIP';
2447 l_status VARCHAR2(1);
2448 BEGIN
2449 --
2450 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2451 --
2452 IF l_debug_on IS NULL
2453 THEN
2454 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2455 END IF;
2456 --
2457 IF l_debug_on THEN
2458 WSH_DEBUG_SV.push(l_module_name);
2459 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
2460 WSH_DEBUG_SV.log(l_module_name,'p_arr_after_trip_id',p_arr_after_trip_id);
2461 WSH_DEBUG_SV.log(l_module_name,'p_arr_after_trip_name',p_arr_after_trip_name);
2462 END IF;
2463
2464 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2465
2466 IF ((p_trip_id IS NULL OR p_trip_id = fnd_api.g_miss_num)
2467 OR ((p_arr_after_trip_id IS NULL OR p_arr_after_trip_id=fnd_api.g_miss_num)
2468 and (p_arr_after_trip_name IS NULL or p_arr_after_trip_name=fnd_api.g_miss_char)
2469 )
2470 ) THEN
2471 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2472 IF l_debug_on THEN
2473 WSH_DEBUG_SV.pop(l_module_name);
2474 END IF;
2475 RETURN;
2476 END IF;
2477
2478 IF (p_trip_id = p_arr_after_trip_id ) THEN
2479 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2480 ELSE
2481 IF (p_arr_after_trip_id IS NOT NULL ) THEN
2482 OPEN get_trip_id(p_arr_after_trip_id);
2483 FETCH get_trip_id INTO l_status;
2484 CLOSE get_trip_id;
2485 ELSIF (p_arr_after_trip_name IS NOT NULL ) THEN
2486 OPEN get_trip_name(p_arr_after_trip_name);
2487 FETCH get_trip_name INTO l_status,p_arr_after_trip_id;
2488 CLOSE get_trip_name;
2489 ELSE
2490 l_status := FND_API.G_MISS_CHAR;
2491 END IF;
2492
2493 IF (l_status IS NULL OR p_trip_id = p_arr_after_trip_id) THEN
2494 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2495 END IF;
2496
2497 END IF;
2498
2499 IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
2500 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ARR_AFTER_TRIP');
2501 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
2502 END IF;
2503
2504 IF l_debug_on THEN
2505 WSH_DEBUG_SV.pop(l_module_name);
2506 END IF;
2507
2508 EXCEPTION
2509 WHEN OTHERS THEN
2510 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.VALIDATE_ARRIVE_AFTER_TRIP');
2511 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2512 IF l_debug_on THEN
2513 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
2514 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2515 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2516 END IF;
2517 END Validate_Arrive_after_trip;
2518
2519
2520 PROCEDURE Validate_Consol_Allowed(
2521 p_trip_info IN WSH_TRIPS_PVT.trip_rec_type,
2522 p_db_trip_info IN WSH_TRIPS_PVT.trip_rec_type,
2523 x_return_status OUT NOCOPY VARCHAR2) IS
2524
2525 l_vehicle_item_id wsh_trips.vehicle_item_id%type;
2526 l_vehicle_organization_id wsh_trips.vehicle_organization_id%type;
2527 l_ship_method_code wsh_trips.ship_method_code%type;
2528 l_carrier_id wsh_trips.carrier_id%type;
2529 l_service_level wsh_trips.service_level%type;
2530 l_mode_of_transport wsh_trips.mode_of_transport%type;
2531 l_lane_id wsh_trips.lane_id%type;
2532 l_c_truck CONSTANT VARCHAR2(10):='TRUCK';
2533
2534 l_debug_on BOOLEAN;
2535 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_CONSOL_ALLOWED';
2536 l_consolidation_allowed VARCHAR2(100);
2537 l_entity_ids WSH_UTIL_CORE.id_tab_type;
2538 BEGIN
2539 --
2540 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2541 --
2542 IF l_debug_on IS NULL
2543 THEN
2544 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2545 END IF;
2546 --
2547
2548
2549 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2550
2551 -- Bug fix: 3365750
2552 -- Do not give error if FTE is not installed.
2553 -- Proceed with code only if FTE is installed. Do nothing if NOT installed.
2554 IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y') THEN
2555 l_consolidation_allowed :=p_db_trip_info.consolidation_allowed;
2556 l_vehicle_item_id :=p_db_trip_info.vehicle_item_id;
2557 l_vehicle_organization_id :=p_db_trip_info.vehicle_organization_id;
2558 l_ship_method_code :=p_db_trip_info.ship_method_code;
2559 l_carrier_id :=p_db_trip_info.carrier_id;
2560 l_service_level :=p_db_trip_info.service_level;
2561 l_mode_of_transport :=p_db_trip_info.mode_of_transport;
2562 l_lane_id :=p_db_trip_info.lane_id;
2563
2564 IF l_debug_on THEN
2565 WSH_DEBUG_SV.push(l_module_name);
2566 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',p_trip_info.trip_id);
2567 WSH_DEBUG_SV.log(l_module_name,'p_consolidation_allowed',p_trip_info.consolidation_allowed);
2568 WSH_DEBUG_SV.log(l_module_name,'p_veh_item_id',p_trip_info.vehicle_item_id);
2569 WSH_DEBUG_SV.log(l_module_name,'p_veh_org_id',p_trip_info.vehicle_organization_id);
2570 WSH_DEBUG_SV.log(l_module_name,'p_ship_method_code',p_trip_info.ship_method_code);
2571 WSH_DEBUG_SV.log(l_module_name,'p_carrier_id',p_trip_info.carrier_id);
2572 WSH_DEBUG_SV.log(l_module_name,'p_service_level',p_trip_info.service_level);
2573 WSH_DEBUG_SV.log(l_module_name,'p_mode_of_transport',p_trip_info.mode_of_transport);
2574 WSH_DEBUG_SV.log(l_module_name,'p_lane_id',p_trip_info.lane_id);
2575
2576 WSH_DEBUG_SV.log(l_module_name,'l_consolidation_allowed',l_consolidation_allowed);
2577 WSH_DEBUG_SV.log(l_module_name,'l_veh_item_id',l_vehicle_item_id);
2578 WSH_DEBUG_SV.log(l_module_name,'l_veh_org_id',l_vehicle_organization_id);
2579 WSH_DEBUG_SV.log(l_module_name,'l_ship_method_code',l_ship_method_code);
2580 WSH_DEBUG_SV.log(l_module_name,'l_carrier_id',l_carrier_id);
2581 WSH_DEBUG_SV.log(l_module_name,'l_service_level',l_service_level);
2582 WSH_DEBUG_SV.log(l_module_name,'l_mode_of_transport',l_mode_of_transport);
2583 WSH_DEBUG_SV.log(l_module_name,'l_lane_id',l_lane_id);
2584 END IF;
2585
2586
2587 --if lane_id, carrier_id, ship_method_code, service_level, mode_of_transport are changed
2588 --or if mode is truck and vehicle item or vehicle org is changed, then mark leg for reprice
2589 IF (
2590 (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)))
2591 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)))
2592 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')))
2593 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')))
2594 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')))
2595 OR (
2596 (p_trip_info.mode_of_transport=l_c_truck OR l_mode_of_transport=l_c_truck)
2597 AND (
2598 (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)))
2599 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)))
2600 )
2601 )
2602 ) THEN
2603 l_entity_ids(1):=p_trip_info.trip_id;
2604 WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required(
2605 p_entity_type => 'TRIP',
2606 p_entity_ids => l_entity_ids,
2607 p_consolidation_change => p_trip_info.consolidation_allowed,
2608 x_return_status => x_return_status);
2609 END IF;
2610
2611 END IF;
2612
2613 IF l_debug_on THEN
2614 WSH_DEBUG_SV.pop(l_module_name);
2615 END IF;
2616
2617 EXCEPTION
2618 WHEN OTHERS THEN
2619 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.VALIDATE_CONSOL_ALLOWED');
2620 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2621 IF l_debug_on THEN
2622 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
2623 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2624 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2625 END IF;
2626 END Validate_Consol_Allowed;
2627
2628
2629 /*
2630 Procedure populate_external_edf is called from
2631 eliminate_displayonly_fields to populate the external value
2632 for a given internal field
2633 */
2634
2635 PROCEDURE populate_external_edf(
2636 p_internal IN NUMBER
2637 , p_external IN VARCHAR2
2638 , x_internal IN OUT NOCOPY NUMBER
2639 , x_external IN OUT NOCOPY VARCHAR2
2640 )
2641 IS
2642 BEGIN
2643
2644 IF p_internal <> FND_API.G_MISS_NUM OR p_internal IS NULL THEN
2645 x_internal := p_internal;
2646 IF p_internal IS NULL THEN
2647 x_external := NULL;
2648 ELSE
2649 x_external := p_external;
2650 END IF;
2651 ELSIF p_external <> FND_API.G_MISS_CHAR OR p_external IS NULL THEN
2652 x_external := p_external;
2653 IF x_external IS NULL THEN
2654 x_internal := NULL;
2655 ELSE
2656 x_internal := p_internal;
2657 END IF;
2658 END IF;
2659
2660 END populate_external_edf;
2661
2662
2663
2664 /*
2665 Procedure populate_external_edf is called from
2666 eliminate_displayonly_fields to populate the external value
2667 for a given internal field
2668 */
2669
2670 PROCEDURE populate_external_edf(
2671 p_internal IN VARCHAR2
2672 , p_external IN VARCHAR2
2673 , x_internal IN OUT NOCOPY VARCHAR2
2674 , x_external IN OUT NOCOPY VARCHAR2
2675 )
2676 IS
2677 BEGIN
2678
2679 IF p_internal <> FND_API.G_MISS_CHAR OR p_internal IS NULL THEN
2680 x_internal := p_internal;
2681 IF p_internal IS NULL THEN
2682 x_external := NULL;
2683 ELSE
2684 x_external := p_external;
2685 END IF;
2686 ELSIF p_external <> FND_API.G_MISS_CHAR OR p_external IS NULL THEN
2687 x_external := p_external;
2688 IF x_external IS NULL THEN
2689 x_internal := NULL;
2690 ELSE
2691 x_internal := p_internal;
2692 END IF;
2693 END IF;
2694
2695 END populate_external_edf;
2696
2697
2698
2699 PROCEDURE eliminate_displayonly_fields (
2700 p_trip_rec IN WSH_TRIPS_PVT.trip_rec_type
2701 , p_in_rec IN WSH_TRIPS_GRP.TripInRecType
2702 , x_trip_rec IN OUT NOCOPY WSH_TRIPS_PVT.trip_rec_type
2703 )
2704 IS
2705 BEGIN
2706
2707 /*
2708 Enable the x_delivery_detail_rec, with the columns that are not
2709 permanently disabled.
2710 */
2711 IF p_trip_rec.NAME <> FND_API.G_MISS_CHAR
2712 OR p_trip_rec.NAME IS NULL THEN
2713 x_trip_rec.NAME := p_trip_rec.NAME;
2714 END IF;
2715
2716 populate_external_edf(p_trip_rec.ship_method_code,
2717 p_trip_rec.ship_method_name,
2718 x_trip_rec.ship_method_code,
2719 x_trip_rec.ship_method_name);
2720
2721 IF p_trip_rec.CARRIER_ID <> FND_API.G_MISS_NUM
2722 OR p_trip_rec.CARRIER_ID IS NULL THEN
2723 x_trip_rec.CARRIER_ID := p_trip_rec.CARRIER_ID;
2724 END IF;
2725
2726 IF p_trip_rec.ROUTE_ID <> FND_API.G_MISS_NUM
2727 OR p_trip_rec.ROUTE_ID IS NULL THEN
2728 x_trip_rec.ROUTE_ID := p_trip_rec.ROUTE_ID;
2729 END IF;
2730 IF p_trip_rec.FREIGHT_TERMS_CODE <> FND_API.G_MISS_CHAR
2731 OR p_trip_rec.FREIGHT_TERMS_CODE IS NULL THEN
2732 x_trip_rec.FREIGHT_TERMS_CODE := p_trip_rec.FREIGHT_TERMS_CODE;
2733 END IF;
2734 IF p_trip_rec.LOAD_TENDER_STATUS <> FND_API.G_MISS_CHAR
2735 OR p_trip_rec.LOAD_TENDER_STATUS IS NULL THEN
2736 x_trip_rec.LOAD_TENDER_STATUS := p_trip_rec.LOAD_TENDER_STATUS;
2737 END IF;
2738 IF p_trip_rec.LOAD_TENDER_NUMBER <> FND_API.G_MISS_NUM
2739 OR p_trip_rec.LOAD_TENDER_NUMBER IS NULL THEN
2740 x_trip_rec.LOAD_TENDER_NUMBER := p_trip_rec.LOAD_TENDER_NUMBER;
2741 END IF;
2742 IF p_trip_rec.VESSEL <> FND_API.G_MISS_CHAR
2743 OR p_trip_rec.VESSEL IS NULL THEN
2744 x_trip_rec.VESSEL := p_trip_rec.VESSEL;
2745 END IF;
2746 IF p_trip_rec.VOYAGE_NUMBER <> FND_API.G_MISS_CHAR
2747 OR p_trip_rec.VOYAGE_NUMBER IS NULL THEN
2748 x_trip_rec.VOYAGE_NUMBER := p_trip_rec.VOYAGE_NUMBER;
2749 END IF;
2750 IF p_trip_rec.PORT_OF_LOADING <> FND_API.G_MISS_CHAR
2751 OR p_trip_rec.PORT_OF_LOADING IS NULL THEN
2752 x_trip_rec.PORT_OF_LOADING:= p_trip_rec.PORT_OF_LOADING;
2753 END IF;
2754 IF p_trip_rec.PORT_OF_DISCHARGE <> FND_API.G_MISS_CHAR
2755 OR p_trip_rec.PORT_OF_DISCHARGE IS NULL THEN
2756 x_trip_rec.PORT_OF_DISCHARGE := p_trip_rec.PORT_OF_DISCHARGE;
2757 END IF;
2758 IF p_trip_rec.WF_NAME <> FND_API.G_MISS_CHAR
2759 OR p_trip_rec.WF_NAME IS NULL THEN
2760 x_trip_rec.WF_NAME := p_trip_rec.WF_NAME;
2761 END IF;
2762 IF p_trip_rec.WF_PROCESS_NAME <> FND_API.G_MISS_CHAR
2763 OR p_trip_rec.WF_PROCESS_NAME IS NULL THEN
2764 x_trip_rec.WF_PROCESS_NAME := p_trip_rec.WF_PROCESS_NAME;
2765 END IF;
2766 IF p_trip_rec.WF_ITEM_KEY <> FND_API.G_MISS_CHAR
2767 OR p_trip_rec.WF_ITEM_KEY IS NULL THEN
2768 x_trip_rec.WF_ITEM_KEY := p_trip_rec.WF_ITEM_KEY;
2769 END IF;
2770 IF p_trip_rec.CARRIER_CONTACT_ID <> FND_API.G_MISS_NUM
2771 OR p_trip_rec.CARRIER_CONTACT_ID IS NULL THEN
2772 x_trip_rec.CARRIER_CONTACT_ID := p_trip_rec.CARRIER_CONTACT_ID;
2773 END IF;
2774 IF p_trip_rec.SHIPPER_WAIT_TIME <> FND_API.G_MISS_NUM
2775 OR p_trip_rec.SHIPPER_WAIT_TIME IS NULL THEN
2776 x_trip_rec.SHIPPER_WAIT_TIME := p_trip_rec.SHIPPER_WAIT_TIME;
2777 END IF;
2778 IF p_trip_rec.WAIT_TIME_UOM <> FND_API.G_MISS_CHAR
2779 OR p_trip_rec.WAIT_TIME_UOM IS NULL THEN
2780 x_trip_rec.WAIT_TIME_UOM := p_trip_rec.WAIT_TIME_UOM;
2781 END IF;
2782 IF p_trip_rec.LOAD_TENDERED_TIME <> FND_API.G_MISS_DATE
2783 OR p_trip_rec.LOAD_TENDERED_TIME IS NULL THEN
2784 x_trip_rec.LOAD_TENDERED_TIME := p_trip_rec.LOAD_TENDERED_TIME;
2785 END IF;
2786 IF p_trip_rec.CARRIER_RESPONSE <> FND_API.G_MISS_CHAR
2787 OR p_trip_rec.CARRIER_RESPONSE IS NULL THEN
2788 x_trip_rec.CARRIER_RESPONSE := p_trip_rec.CARRIER_RESPONSE;
2789 END IF;
2790
2791 IF p_trip_rec.ROUTE_LANE_ID <> FND_API.G_MISS_NUM
2792 OR p_trip_rec.ROUTE_LANE_ID IS NULL THEN
2793 x_trip_rec.ROUTE_LANE_ID := p_trip_rec.ROUTE_LANE_ID;
2794 END IF;
2795 IF p_trip_rec.LANE_ID <> FND_API.G_MISS_NUM
2796 OR p_trip_rec.LANE_ID IS NULL THEN
2797 x_trip_rec.LANE_ID := p_trip_rec.LANE_ID;
2798 END IF;
2799 IF p_trip_rec.SCHEDULE_ID <> FND_API.G_MISS_NUM
2800 OR p_trip_rec.SCHEDULE_ID IS NULL THEN
2801 x_trip_rec.SCHEDULE_ID := p_trip_rec.SCHEDULE_ID;
2802 END IF;
2803 IF p_trip_rec.BOOKING_NUMBER <> FND_API.G_MISS_CHAR
2804 OR p_trip_rec.BOOKING_NUMBER IS NULL THEN
2805 x_trip_rec.BOOKING_NUMBER := p_trip_rec.BOOKING_NUMBER;
2806 END IF;
2807
2808 IF p_trip_rec.SERVICE_LEVEL <> FND_API.G_MISS_CHAR
2809 OR p_trip_rec.SERVICE_LEVEL IS NULL THEN
2810 x_trip_rec.SERVICE_LEVEL := p_trip_rec.SERVICE_LEVEL;
2811 END IF;
2812 IF p_trip_rec.MODE_OF_TRANSPORT <> FND_API.G_MISS_CHAR
2813 OR p_trip_rec.MODE_OF_TRANSPORT IS NULL THEN
2814 x_trip_rec.MODE_OF_TRANSPORT := p_trip_rec.MODE_OF_TRANSPORT;
2815 END IF;
2816 IF p_trip_rec.IGNORE_FOR_PLANNING <> FND_API.G_MISS_CHAR
2817 AND (p_in_rec.CALLER LIKE 'FTE%' OR p_in_rec.CALLER LIKE 'WSH_CONSOL%')
2818 AND p_in_rec.ACTION_CODE = 'CREATE' THEN
2819 -- bug 3694794: FTE needs to create trip with ignore = Y
2820 x_trip_rec.IGNORE_FOR_PLANNING := p_trip_rec.IGNORE_FOR_PLANNING;
2821 END IF;
2822 populate_external_edf(p_trip_rec.VEHICLE_ORGANIZATION_ID,
2823 p_trip_rec.VEHICLE_ORGANIZATION_CODE,
2824 x_trip_rec.VEHICLE_ORGANIZATION_ID,
2825 x_trip_rec.VEHICLE_ORGANIZATION_CODE);
2826
2827 populate_external_edf(p_trip_rec.VEHICLE_ITEM_ID,
2828 p_trip_rec.VEHICLE_ITEM_DESC,
2829 x_trip_rec.VEHICLE_ITEM_ID,
2830 x_trip_rec.VEHICLE_ITEM_DESC);
2831
2832 IF p_trip_rec.VEHICLE_NUM_PREFIX <> FND_API.G_MISS_CHAR
2833 OR p_trip_rec.VEHICLE_NUM_PREFIX IS NULL THEN
2834 x_trip_rec.VEHICLE_NUM_PREFIX := p_trip_rec.VEHICLE_NUM_PREFIX;
2835 END IF;
2836 IF p_trip_rec.VEHICLE_NUMBER <> FND_API.G_MISS_CHAR
2837 OR p_trip_rec.VEHICLE_NUMBER IS NULL THEN
2838 x_trip_rec.VEHICLE_NUMBER := p_trip_rec.VEHICLE_NUMBER;
2839 END IF;
2840
2841 populate_external_edf(p_trip_rec.ARRIVE_AFTER_TRIP_ID,
2842 p_trip_rec.ARRIVE_AFTER_TRIP_NAME,
2843 x_trip_rec.ARRIVE_AFTER_TRIP_ID,
2844 x_trip_rec.ARRIVE_AFTER_TRIP_NAME);
2845
2846 IF p_trip_rec.ROUTING_INSTRUCTIONS <> FND_API.G_MISS_CHAR
2847 OR p_trip_rec.ROUTING_INSTRUCTIONS IS NULL THEN
2848 x_trip_rec.ROUTING_INSTRUCTIONS := p_trip_rec.ROUTING_INSTRUCTIONS;
2849 END IF;
2850 IF p_trip_rec.CONSOLIDATION_ALLOWED <> FND_API.G_MISS_CHAR
2851 OR p_trip_rec.CONSOLIDATION_ALLOWED IS NULL THEN
2852 x_trip_rec.CONSOLIDATION_ALLOWED := p_trip_rec.CONSOLIDATION_ALLOWED;
2853 END IF;
2854 IF p_trip_rec.OPERATOR <> FND_API.G_MISS_CHAR
2855 OR p_trip_rec.OPERATOR IS NULL THEN
2856 x_trip_rec.OPERATOR := p_trip_rec.OPERATOR;
2857 END IF;
2858
2859 IF p_trip_rec.attribute1 <> FND_API.G_MISS_CHAR
2860 OR p_trip_rec.attribute1 IS NULL THEN
2861 x_trip_rec.attribute1 := p_trip_rec.attribute1;
2862 END IF;
2863 IF p_trip_rec.attribute2 <> FND_API.G_MISS_CHAR
2864 OR p_trip_rec.attribute2 IS NULL THEN
2865 x_trip_rec.attribute2 := p_trip_rec.attribute2;
2866 END IF;
2867 IF p_trip_rec.attribute3 <> FND_API.G_MISS_CHAR
2868 OR p_trip_rec.attribute3 IS NULL THEN
2869 x_trip_rec.attribute3 := p_trip_rec.attribute3;
2870 END IF;
2871 IF p_trip_rec.attribute4 <> FND_API.G_MISS_CHAR
2872 OR p_trip_rec.attribute4 IS NULL THEN
2873 x_trip_rec.attribute4 := p_trip_rec.attribute4;
2874 END IF;
2875 IF p_trip_rec.attribute5 <> FND_API.G_MISS_CHAR
2876 OR p_trip_rec.attribute5 IS NULL THEN
2877 x_trip_rec.attribute5 := p_trip_rec.attribute5;
2878 END IF;
2879 IF p_trip_rec.attribute6 <> FND_API.G_MISS_CHAR
2880 OR p_trip_rec.attribute6 IS NULL THEN
2881 x_trip_rec.attribute6 := p_trip_rec.attribute6;
2882 END IF;
2883 IF p_trip_rec.attribute7 <> FND_API.G_MISS_CHAR
2884 OR p_trip_rec.attribute7 IS NULL THEN
2885 x_trip_rec.attribute7 := p_trip_rec.attribute7;
2886 END IF;
2887 IF p_trip_rec.attribute8 <> FND_API.G_MISS_CHAR
2888 OR p_trip_rec.attribute8 IS NULL THEN
2889 x_trip_rec.attribute8 := p_trip_rec.attribute8;
2890 END IF;
2891 IF p_trip_rec.attribute9 <> FND_API.G_MISS_CHAR
2892 OR p_trip_rec.attribute9 IS NULL THEN
2893 x_trip_rec.attribute9 := p_trip_rec.attribute9;
2894 END IF;
2895 IF p_trip_rec.attribute10 <> FND_API.G_MISS_CHAR
2896 OR p_trip_rec.attribute10 IS NULL THEN
2897 x_trip_rec.attribute10 := p_trip_rec.attribute10;
2898 END IF;
2899 IF p_trip_rec.attribute11 <> FND_API.G_MISS_CHAR
2900 OR p_trip_rec.attribute11 IS NULL THEN
2901 x_trip_rec.attribute11 := p_trip_rec.attribute11;
2902 END IF;
2903 IF p_trip_rec.attribute12 <> FND_API.G_MISS_CHAR
2904 OR p_trip_rec.attribute12 IS NULL THEN
2905 x_trip_rec.attribute12 := p_trip_rec.attribute12;
2906 END IF;
2907 IF p_trip_rec.attribute13 <> FND_API.G_MISS_CHAR
2908 OR p_trip_rec.attribute13 IS NULL THEN
2909 x_trip_rec.attribute13 := p_trip_rec.attribute13;
2910 END IF;
2911 IF p_trip_rec.attribute14 <> FND_API.G_MISS_CHAR
2912 OR p_trip_rec.attribute14 IS NULL THEN
2913 x_trip_rec.attribute14 := p_trip_rec.attribute14;
2914 END IF;
2915 IF p_trip_rec.attribute15 <> FND_API.G_MISS_CHAR
2916 OR p_trip_rec.attribute15 IS NULL THEN
2917 x_trip_rec.attribute15 := p_trip_rec.attribute15;
2918 END IF;
2919 IF p_trip_rec.ATTRIBUTE_CATEGORY <> FND_API.G_MISS_CHAR
2920 OR p_trip_rec.ATTRIBUTE_CATEGORY IS NULL THEN
2921 x_trip_rec.ATTRIBUTE_CATEGORY := p_trip_rec.ATTRIBUTE_CATEGORY;
2922 END IF;
2923 -- OTM R12, glog project, allow GC3 Inbound Message to update tp_plan_name
2924 IF p_in_rec.caller IN ('WSH_TP_RELEASE','FTE_TMS_INTEGRATION') THEN
2925 IF p_trip_rec.tp_plan_name <> FND_API.G_MISS_CHAR
2926 OR p_trip_rec.tp_plan_name IS NULL THEN
2927 x_trip_rec.tp_plan_name := p_trip_rec.tp_plan_name;
2928 END IF;
2929 IF p_trip_rec.tp_trip_number <> FND_API.G_MISS_NUM
2930 OR p_trip_rec.tp_trip_number IS NULL THEN
2931 x_trip_rec.tp_trip_number := p_trip_rec.tp_trip_number;
2932 END IF;
2933 END IF;
2934
2935 IF p_in_rec.caller IN ('FTE_ROUTING_GUIDE',
2936 'FTE_RATING',
2937 'FTE_LOAD_TENDER',
2938 'FTE_MLS_WRAPPER') THEN
2939
2940 IF p_trip_rec.rank_id <> FND_API.G_MISS_NUM
2941 OR p_trip_rec.rank_id IS NULL THEN
2942 x_trip_rec.rank_id := p_trip_rec.rank_id;
2943 END IF;
2944
2945 END IF;
2946
2947 IF p_in_rec.caller IN ('FTE_ROUTING_GUIDE',
2948 'FTE_RATING',
2949 'FTE_LOAD_TENDER') THEN
2950
2951 IF p_trip_rec.routing_rule_id <> FND_API.G_MISS_NUM
2952 OR p_trip_rec.routing_rule_id IS NULL THEN
2953 x_trip_rec.routing_rule_id := p_trip_rec.routing_rule_id;
2954 END IF;
2955
2956 IF p_trip_rec.append_flag <> FND_API.G_MISS_CHAR
2957 OR p_trip_rec.append_flag IS NULL THEN
2958 x_trip_rec.append_flag := p_trip_rec.append_flag;
2959 END IF;
2960
2961 END IF;
2962
2963 IF p_trip_rec.consignee_carrier_ac_no <> FND_API.G_MISS_CHAR
2964 OR p_trip_rec.consignee_carrier_ac_no IS NULL THEN
2965 x_trip_rec.consignee_carrier_ac_no := p_trip_rec.consignee_carrier_ac_no;
2966 END IF;
2967
2968 IF p_trip_rec.carrier_reference_number <> FND_API.G_MISS_CHAR
2969 OR p_trip_rec.carrier_reference_number IS NULL THEN
2970 x_trip_rec.carrier_reference_number := p_trip_rec.carrier_reference_number;
2971 END IF;
2972
2973 IF p_trip_rec.seal_code <> FND_API.G_MISS_CHAR
2974 OR p_trip_rec.seal_code IS NULL THEN
2975 x_trip_rec.seal_code := p_trip_rec.seal_code;
2976 END IF;
2977
2978 EXCEPTION
2979 -- OTM 12, glog proj, no debug or x_return_status variable here
2980 WHEN OTHERS THEN
2981 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.ELIMINATE_DISPLAYONLY_FIELDS');
2982
2983 END eliminate_displayonly_fields;
2984
2985 /*----------------------------------------------------------
2986 -- Procedure disable_from_list will update the record x_out_rec
2987 -- and disables the field contained in p_disabled_list.
2988 -----------------------------------------------------------*/
2989
2990 PROCEDURE disable_from_list(
2991 p_disabled_list IN WSH_UTIL_CORE.column_tab_type
2992 , p_in_rec IN WSH_TRIPS_PVT.trip_rec_type
2993 , x_out_rec IN OUT NOCOPY WSH_TRIPS_PVT.trip_rec_type
2994 , x_return_status OUT NOCOPY VARCHAR2
2995 , x_field_name OUT NOCOPY VARCHAR2
2996
2997 ) IS
2998 BEGIN
2999 FOR i IN 1..p_disabled_list.COUNT
3000 LOOP
3001 IF p_disabled_list(i) = 'ROUTING_INSTRUCTIONS' THEN
3002 x_out_rec.ROUTING_INSTRUCTIONS := p_in_rec.ROUTING_INSTRUCTIONS ;
3003 ELSIF p_disabled_list(i) = 'FREIGHT_CODE' THEN
3004 --x_out_rec.FREIGHT_TERMS_CODE := p_in_rec.FREIGHT_TERMS_CODE ;
3005 x_out_rec.carrier_id := p_in_rec.carrier_id; -- J-IB-NPARIKH--I-bug-fix
3006 ELSIF p_disabled_list(i) = 'SERVICE_LEVEL_NAME' THEN
3007 x_out_rec.SERVICE_LEVEL := p_in_rec.SERVICE_LEVEL ;
3008 ELSIF p_disabled_list(i) = 'MODE_OF_TRANSPORT_NAME' THEN
3009 x_out_rec.MODE_OF_TRANSPORT := p_in_rec.MODE_OF_TRANSPORT ;
3010 ELSIF p_disabled_list(i) = 'OPERATOR' THEN
3011 x_out_rec.OPERATOR := p_in_rec.OPERATOR ;
3012 ELSIF p_disabled_list(i) = 'DESC_FLEX' THEN
3013 x_out_rec.attribute1 := p_in_rec.attribute1 ;
3014 x_out_rec.attribute2 := p_in_rec.attribute2 ;
3015 x_out_rec.attribute3 := p_in_rec.attribute3 ;
3016 x_out_rec.attribute4 := p_in_rec.attribute4 ;
3017 x_out_rec.attribute5 := p_in_rec.attribute5 ;
3018 x_out_rec.attribute6 := p_in_rec.attribute6 ;
3019 x_out_rec.attribute7 := p_in_rec.attribute7 ;
3020 x_out_rec.attribute8 := p_in_rec.attribute8 ;
3021 x_out_rec.attribute9 := p_in_rec.attribute9 ;
3022 x_out_rec.attribute10 := p_in_rec.attribute10 ;
3023 x_out_rec.attribute11 := p_in_rec.attribute11 ;
3024 x_out_rec.attribute12 := p_in_rec.attribute12 ;
3025 x_out_rec.attribute13 := p_in_rec.attribute13 ;
3026 x_out_rec.attribute14 := p_in_rec.attribute14 ;
3027 x_out_rec.attribute15 := p_in_rec.attribute15 ;
3028 x_out_rec.attribute_category := p_in_rec.attribute_category ;
3029 ELSIF p_disabled_list(i) = 'CARRIER_REFERENCE_NUMBER' THEN
3030 x_out_rec.carrier_reference_number := p_in_rec.carrier_reference_number;
3031 ELSIF p_disabled_list(i) = 'CONSIGNEE_CARRIER_AC_NO' THEN
3032 x_out_rec.consignee_carrier_ac_no := p_in_rec.consignee_carrier_ac_no;
3033 ELSIF p_disabled_list(i) = 'FULL' THEN
3034 NULL;
3035 ELSE
3036 -- invalid name
3037 x_field_name := p_disabled_list(i);
3038 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3039 RETURN;
3040 --
3041 END IF;
3042 END LOOP;
3043 END disable_from_list;
3044 /*
3045 Procedure populate_external_efl is called from
3046 enable_from_list to populate the external value
3047 for a given internal field
3048 */
3049
3050 PROCEDURE populate_external_efl(
3051 p_internal IN VARCHAR2
3052 , p_external IN VARCHAR2
3053 , p_mode IN VARCHAR2
3054 , x_internal IN OUT NOCOPY VARCHAR2
3055 , x_external IN OUT NOCOPY VARCHAR2
3056 )
3057 IS
3058 BEGIN
3059
3060 IF p_mode = '+' THEN
3061 IF x_internal IS NULL THEN
3062 IF p_internal <> FND_API.G_MISS_CHAR OR p_internal IS NULL THEN
3063 x_internal := p_internal ;
3064 IF p_internal IS NULL THEN
3065 x_external := NULL;
3066 ELSE
3067 x_external := p_external;
3068 END IF;
3069 ELSIF p_external <> FND_API.G_MISS_CHAR OR p_external IS NULL THEN
3070 x_external := p_external;
3071 IF p_external IS NULL THEN
3072 x_internal := NULL;
3073 ELSE
3074 x_internal := p_internal;
3075 END IF;
3076 END IF;
3077 END IF;
3078 ELSE --p_mode <> +
3079 IF p_internal <> FND_API.G_MISS_CHAR OR p_internal IS NULL THEN
3080 x_internal := p_internal ;
3081 IF p_internal IS NULL THEN
3082 x_external := NULL;
3083 ELSE
3084 x_external := p_external;
3085 END IF;
3086 ELSIF p_external <> FND_API.G_MISS_CHAR OR p_external IS NULL THEN
3087 x_external := p_external;
3088 IF p_external IS NULL THEN
3089 x_internal := NULL;
3090 ELSE
3091 x_internal := p_internal;
3092 END IF;
3093 END IF;
3094 END IF;
3095
3096 END populate_external_efl;
3097
3098 /*
3099 Procedure populate_external_efl is called from
3100 enable_from_list to populate the external value
3101 for a given internal field
3102 */
3103
3104 PROCEDURE populate_external_efl(
3105 p_internal IN NUMBER
3106 , p_external IN VARCHAR2
3107 , p_mode IN VARCHAR2
3108 , x_internal IN OUT NOCOPY NUMBER
3109 , x_external IN OUT NOCOPY VARCHAR2
3110 )
3111 IS
3112 BEGIN
3113
3114 IF p_mode = '+' THEN
3115 IF x_internal IS NULL THEN
3116 IF p_internal <> FND_API.G_MISS_NUM OR p_internal IS NULL THEN
3117 x_internal := p_internal ;
3118 IF p_internal IS NULL THEN
3119 x_external := NULL;
3120 ELSE
3121 x_external := p_external;
3122 END IF;
3123 ELSIF p_external <> FND_API.G_MISS_CHAR OR p_external IS NULL THEN
3124 x_external := p_external;
3125 IF p_external IS NULL THEN
3126 x_internal := NULL;
3127 ELSE
3128 x_internal := p_internal;
3129 END IF;
3130 END IF;
3131 END IF;
3132 ELSE --p_mode <> +
3133 IF p_internal <> FND_API.G_MISS_NUM OR p_internal IS NULL THEN
3134 x_internal := p_internal ;
3135 IF p_internal IS NULL THEN
3136 x_external := NULL;
3137 ELSE
3138 x_external := p_external;
3139 END IF;
3140 ELSIF p_external <> FND_API.G_MISS_CHAR OR p_external IS NULL THEN
3141 x_external := p_external;
3142 IF p_external IS NULL THEN
3143 x_internal := NULL;
3144 ELSE
3145 x_internal := p_internal;
3146 END IF;
3147 END IF;
3148 END IF;
3149
3150 END populate_external_efl;
3151 /*----------------------------------------------------------
3152 -- Procedure enable_from_list will update the record x_out_rec for the fields
3153 -- included in p_disabled_list and will enable them
3154 -----------------------------------------------------------*/
3155
3156 PROCEDURE enable_from_list(
3157 p_disabled_list IN WSH_UTIL_CORE.column_tab_type
3158 , p_in_rec IN WSH_TRIPS_PVT.trip_rec_type
3159 , x_out_rec IN OUT NOCOPY WSH_TRIPS_PVT.trip_rec_type
3160 , x_return_status OUT NOCOPY VARCHAR2
3161 , x_field_name OUT NOCOPY VARCHAR2
3162
3163 ) IS
3164 BEGIN
3165 FOR i IN 2..p_disabled_list.COUNT
3166 LOOP
3167 IF p_disabled_list(i) = 'ROUTING_INSTRUCTIONS' THEN
3168 IF p_in_rec.ROUTING_INSTRUCTIONS <> FND_API.G_MISS_CHAR
3169 OR p_in_rec.ROUTING_INSTRUCTIONS IS NULL THEN
3170 x_out_rec.ROUTING_INSTRUCTIONS := p_in_rec.ROUTING_INSTRUCTIONS ;
3171 END IF;
3172 -- J-IB-NPARIKH-{ ---I-bug-fix
3173 ELSIF p_disabled_list(i) = 'FREIGHT_CODE' THEN
3174 IF p_in_rec.CARRIER_ID <> FND_API.G_MISS_NUM
3175 OR p_in_rec.CARRIER_ID IS NULL THEN
3176 x_out_rec.CARRIER_ID := p_in_rec.CARRIER_ID ;
3177 END IF;
3178 ELSIF p_disabled_list(i) = '+FREIGHT_CODE' THEN
3179 IF p_in_rec.CARRIER_ID <> FND_API.G_MISS_NUM
3180 OR p_in_rec.CARRIER_ID IS NULL THEN
3181 IF x_out_rec.CARRIER_ID IS NULL THEN
3182 x_out_rec.CARRIER_ID := p_in_rec.CARRIER_ID ;
3183 END IF;
3184 END IF;
3185 ELSIF p_disabled_list(i) = '+SHIP_METHOD_NAME' THEN
3186 populate_external_efl(p_in_rec.ship_method_code,
3187 p_in_rec.ship_method_name,
3188 '+',
3189 x_out_rec.ship_method_code,
3190 x_out_rec.ship_method_name);
3191 ELSIF p_disabled_list(i) = 'SHIP_METHOD_NAME' THEN
3192 populate_external_efl(p_in_rec.ship_method_code,
3193 p_in_rec.ship_method_name,
3194 NULL,
3195 x_out_rec.ship_method_code,
3196 x_out_rec.ship_method_name);
3197
3198 -- J-IB-NPARIKH-}
3199
3200 /*
3201 IF p_in_rec.FREIGHT_TERMS_CODE <> FND_API.G_MISS_CHAR
3202 OR p_in_rec.FREIGHT_TERMS_CODE IS NULL THEN
3203 x_out_rec.FREIGHT_TERMS_CODE := p_in_rec.FREIGHT_TERMS_CODE ;
3204 END IF;
3205 */
3206 ELSIF p_disabled_list(i) = 'SERVICE_LEVEL_NAME' THEN
3207 IF p_in_rec.SERVICE_LEVEL <> FND_API.G_MISS_CHAR
3208 OR p_in_rec.SERVICE_LEVEL IS NULL THEN
3209 x_out_rec.SERVICE_LEVEL := p_in_rec.SERVICE_LEVEL ;
3210 END IF;
3211 ELSIF p_disabled_list(i) = 'MODE_OF_TRANSPORT_NAME' THEN
3212 IF p_in_rec.MODE_OF_TRANSPORT <> FND_API.G_MISS_CHAR
3213 OR p_in_rec.MODE_OF_TRANSPORT IS NULL THEN
3214 x_out_rec.MODE_OF_TRANSPORT := p_in_rec.MODE_OF_TRANSPORT ;
3215 END IF;
3216 -- J-IB-NPARIKH-{
3217 ELSIF p_disabled_list(i) = '+SERVICE_LEVEL_NAME' THEN
3218 IF p_in_rec.SERVICE_LEVEL <> FND_API.G_MISS_CHAR
3219 OR p_in_rec.SERVICE_LEVEL IS NULL THEN
3220 IF x_out_rec.SERVICE_LEVEL IS NULL THEN
3221 x_out_rec.SERVICE_LEVEL := p_in_rec.SERVICE_LEVEL ;
3222 END IF;
3223 END IF;
3224 ELSIF p_disabled_list(i) = '+MODE_OF_TRANSPORT_NAME' THEN
3225 IF p_in_rec.MODE_OF_TRANSPORT <> FND_API.G_MISS_CHAR
3226 OR p_in_rec.MODE_OF_TRANSPORT IS NULL THEN
3227 IF x_out_rec.MODE_OF_TRANSPORT IS NULL THEN
3228 x_out_rec.MODE_OF_TRANSPORT := p_in_rec.MODE_OF_TRANSPORT ;
3229 END IF;
3230 END IF;
3231 ELSIF p_disabled_list(i) = '+LANE_ID' THEN
3232 IF p_in_rec.LANE_ID <> FND_API.G_MISS_NUM
3233 OR p_in_rec.LANE_ID IS NULL THEN
3234 IF x_out_rec.LANE_ID IS NULL THEN
3235 x_out_rec.LANE_ID := p_in_rec.LANE_ID ;
3236 END IF;
3237 END IF;
3238 ELSIF p_disabled_list(i) = 'VEHICLE_NUMBER' THEN
3239 IF p_in_rec.VEHICLE_NUMBER <> FND_API.G_MISS_CHAR
3240 OR p_in_rec.VEHICLE_NUMBER IS NULL THEN
3241 x_out_rec.VEHICLE_NUMBER := p_in_rec.VEHICLE_NUMBER ;
3242 END IF;
3243 ELSIF p_disabled_list(i) = 'VEHICLE_NUM_PREFIX' THEN
3244 IF p_in_rec.VEHICLE_NUM_PREFIX <> FND_API.G_MISS_CHAR
3245 OR p_in_rec.VEHICLE_NUM_PREFIX IS NULL THEN
3246 x_out_rec.VEHICLE_NUM_PREFIX := p_in_rec.VEHICLE_NUM_PREFIX ;
3247 END IF;
3248 -- OTM R12, glog project
3249 ELSIF p_disabled_list(i) = 'SEAL_CODE' THEN
3250 IF p_in_rec.SEAL_CODE <> FND_API.G_MISS_CHAR
3251 OR p_in_rec.SEAL_CODE IS NULL THEN
3252 x_out_rec.SEAL_CODE := p_in_rec.SEAL_CODE;
3253 END IF;
3254 ELSIF p_disabled_list(i) = 'NAME' THEN
3255 IF p_in_rec.NAME <> FND_API.G_MISS_CHAR
3256 OR p_in_rec.NAME IS NULL THEN
3257 x_out_rec.NAME := p_in_rec.NAME;
3258 END IF;
3259 -- OTM R12, end of glog project
3260 ELSIF p_disabled_list(i) = 'OPERATOR' THEN
3261 IF p_in_rec.OPERATOR <> FND_API.G_MISS_CHAR
3262 OR p_in_rec.OPERATOR IS NULL THEN
3263 x_out_rec.OPERATOR := p_in_rec.OPERATOR ;
3264 END IF;
3265 -- bug 3507047: Enable update of lane on firmed trip.
3266 ELSIF p_disabled_list(i) = 'LANE_ID' THEN
3267 IF p_in_rec.LANE_ID <> FND_API.G_MISS_NUM
3268 OR p_in_rec.LANE_ID IS NULL THEN
3269 x_out_rec.LANE_ID := p_in_rec.LANE_ID ;
3270 END IF;
3271 -- J-IB-NPARIKH-}
3272 ELSIF p_disabled_list(i) = 'DESC_FLEX' THEN
3273 IF p_in_rec.attribute1 <> FND_API.G_MISS_CHAR
3274 OR p_in_rec.attribute1 IS NULL THEN
3275 x_out_rec.attribute1 := p_in_rec.attribute1 ;
3276 END IF;
3277 IF p_in_rec.attribute2 <> FND_API.G_MISS_CHAR
3278 OR p_in_rec.attribute2 IS NULL THEN
3279 x_out_rec.attribute2 := p_in_rec.attribute2 ;
3280 END IF;
3281 IF p_in_rec.attribute3 <> FND_API.G_MISS_CHAR
3282 OR p_in_rec.attribute3 IS NULL THEN
3283 x_out_rec.attribute3 := p_in_rec.attribute3 ;
3284 END IF;
3285 IF p_in_rec.attribute4 <> FND_API.G_MISS_CHAR
3286 OR p_in_rec.attribute4 IS NULL THEN
3287 x_out_rec.attribute4 := p_in_rec.attribute4 ;
3288 END IF;
3289 IF p_in_rec.attribute5 <> FND_API.G_MISS_CHAR
3290 OR p_in_rec.attribute5 IS NULL THEN
3291 x_out_rec.attribute5 := p_in_rec.attribute5 ;
3292 END IF;
3293 IF p_in_rec.attribute6 <> FND_API.G_MISS_CHAR
3294 OR p_in_rec.attribute6 IS NULL THEN
3295 x_out_rec.attribute6 := p_in_rec.attribute6 ;
3296 END IF;
3297 IF p_in_rec.attribute7 <> FND_API.G_MISS_CHAR
3298 OR p_in_rec.attribute7 IS NULL THEN
3299 x_out_rec.attribute7 := p_in_rec.attribute7 ;
3300 END IF;
3301 IF p_in_rec.attribute8 <> FND_API.G_MISS_CHAR
3302 OR p_in_rec.attribute8 IS NULL THEN
3303 x_out_rec.attribute8 := p_in_rec.attribute8 ;
3304 END IF;
3305 IF p_in_rec.attribute9 <> FND_API.G_MISS_CHAR
3306 OR p_in_rec.attribute9 IS NULL THEN
3307 x_out_rec.attribute9 := p_in_rec.attribute9 ;
3308 END IF;
3309 IF p_in_rec.attribute10 <> FND_API.G_MISS_CHAR
3310 OR p_in_rec.attribute10 IS NULL THEN
3311 x_out_rec.attribute10 := p_in_rec.attribute10 ;
3312 END IF;
3313 IF p_in_rec.attribute11 <> FND_API.G_MISS_CHAR
3314 OR p_in_rec.attribute11 IS NULL THEN
3315 x_out_rec.attribute11 := p_in_rec.attribute11 ;
3316 END IF;
3317 IF p_in_rec.attribute12 <> FND_API.G_MISS_CHAR
3318 OR p_in_rec.attribute12 IS NULL THEN
3319 x_out_rec.attribute12 := p_in_rec.attribute12 ;
3320 END IF;
3321 IF p_in_rec.attribute13 <> FND_API.G_MISS_CHAR
3322 OR p_in_rec.attribute13 IS NULL THEN
3323 x_out_rec.attribute13 := p_in_rec.attribute13 ;
3324 END IF;
3325 IF p_in_rec.attribute14 <> FND_API.G_MISS_CHAR
3326 OR p_in_rec.attribute14 IS NULL THEN
3327 x_out_rec.attribute14 := p_in_rec.attribute14 ;
3328 END IF;
3329 IF p_in_rec.attribute15 <> FND_API.G_MISS_CHAR
3330 OR p_in_rec.attribute15 IS NULL THEN
3331 x_out_rec.attribute15 := p_in_rec.attribute15 ;
3332 END IF;
3333 IF p_in_rec.attribute_category <> FND_API.G_MISS_CHAR
3334 OR p_in_rec.attribute_category IS NULL THEN
3335 x_out_rec.attribute_category := p_in_rec.attribute_category ;
3336 END IF;
3337 --ELSIF p_disabled_list(i) = 'FULL' THEN
3338 --NULL;
3339 --bug 3257612 : load tender needs to update fields even if firm
3340 ELSIF p_disabled_list(i) = 'LOAD_TENDER_STATUS' THEN
3341 IF p_in_rec.LOAD_TENDER_STATUS <> FND_API.G_MISS_CHAR
3342 OR p_in_rec.LOAD_TENDER_STATUS IS NULL THEN
3343 x_out_rec.LOAD_TENDER_STATUS := p_in_rec.LOAD_TENDER_STATUS;
3344 END IF;
3345 ELSIF p_disabled_list(i) = 'WF_NAME' THEN
3346 IF p_in_rec.WF_NAME <> FND_API.G_MISS_CHAR
3347 OR p_in_rec.WF_NAME IS NULL THEN
3348 x_out_rec.WF_NAME := p_in_rec.WF_NAME;
3349 END IF;
3350 ELSIF p_disabled_list(i) = 'WF_PROCESS_NAME' THEN
3351 IF p_in_rec.WF_PROCESS_NAME <> FND_API.G_MISS_CHAR
3352 OR p_in_rec.WF_PROCESS_NAME IS NULL THEN
3353 x_out_rec.WF_PROCESS_NAME := p_in_rec.WF_PROCESS_NAME;
3354 END IF;
3355 ELSIF p_disabled_list(i) = 'WF_ITEM_KEY' THEN
3356 IF p_in_rec.WF_ITEM_KEY <> FND_API.G_MISS_CHAR
3357 OR p_in_rec.WF_ITEM_KEY IS NULL THEN
3358 x_out_rec.WF_ITEM_KEY := p_in_rec.WF_ITEM_KEY;
3359 END IF;
3360 ELSIF p_disabled_list(i) = 'CARRIER_CONTACT_ID' THEN
3361 IF p_in_rec.CARRIER_CONTACT_ID <> FND_API.G_MISS_NUM
3362 OR p_in_rec.CARRIER_CONTACT_ID IS NULL THEN
3363 x_out_rec.CARRIER_CONTACT_ID := p_in_rec.CARRIER_CONTACT_ID;
3364 END IF;
3365 ELSIF p_disabled_list(i) = 'SHIPPER_WAIT_TIME' THEN
3366 IF p_in_rec.SHIPPER_WAIT_TIME <> FND_API.G_MISS_NUM
3367 OR p_in_rec.SHIPPER_WAIT_TIME IS NULL THEN
3368 x_out_rec.SHIPPER_WAIT_TIME := p_in_rec.SHIPPER_WAIT_TIME ;
3369 END IF;
3370 ELSIF p_disabled_list(i) = 'WAIT_TIME_UOM' THEN
3371 IF p_in_rec.WAIT_TIME_UOM <> FND_API.G_MISS_CHAR
3372 OR p_in_rec.WAIT_TIME_UOM IS NULL THEN
3373 x_out_rec.WAIT_TIME_UOM := p_in_rec.WAIT_TIME_UOM ;
3374 END IF;
3375 ELSIF p_disabled_list(i) = 'LOAD_TENDERED_TIME' THEN
3376 IF p_in_rec.LOAD_TENDERED_TIME <> FND_API.G_MISS_DATE
3377 OR p_in_rec.LOAD_TENDERED_TIME IS NULL THEN
3378 x_out_rec.LOAD_TENDERED_TIME := p_in_rec.LOAD_TENDERED_TIME;
3379 END IF;
3380 ELSIF p_disabled_list(i) = 'CARRIER_RESPONSE' THEN
3381 IF p_in_rec.CARRIER_RESPONSE <> FND_API.G_MISS_CHAR
3382 OR p_in_rec.CARRIER_RESPONSE IS NULL THEN
3383 x_out_rec.CARRIER_RESPONSE := p_in_rec.CARRIER_RESPONSE;
3384 END IF;
3385 --Bug 3309150 {
3386 ELSIF p_disabled_list(i) = 'VEHICLE_ORGANIZATION_CODE' THEN
3387 populate_external_efl(p_in_rec.VEHICLE_ORGANIZATION_ID,
3388 p_in_rec.VEHICLE_ORGANIZATION_CODE,
3389 NULL,
3390 x_out_rec.VEHICLE_ORGANIZATION_ID,
3391 x_out_rec.VEHICLE_ORGANIZATION_CODE);
3392
3393
3394 --Bug 3599626: If veh. org is enabled, enable the veh. item as well {
3395 IF x_out_rec.VEHICLE_ORGANIZATION_ID IS NOT NULL THEN
3396 IF p_in_rec.VEHICLE_ITEM_ID <> FND_API.G_MISS_NUM
3397 OR p_in_rec.VEHICLE_ITEM_ID IS NULL THEN
3398 x_out_rec.VEHICLE_ITEM_ID := p_in_rec.VEHICLE_ITEM_ID;
3399 END IF;
3400 END IF;
3401
3402 --}
3403 --Bug 3599626 {
3404 ELSIF p_disabled_list(i) = 'VEHICLE_ITEM_NAME' THEN
3405 -- This is already handled when enabling 'VEHICLE_ORGANIZATION_CODE'
3406 -- but we do not want to raise the below error.
3407 NULL;
3408 --}
3409 ELSIF p_disabled_list(i) = 'CARRIER_REFERENCE_NUMBER' THEN
3410 IF p_in_rec.CARRIER_REFERENCE_NUMBER <> FND_API.G_MISS_CHAR
3411 OR p_in_rec.CARRIER_REFERENCE_NUMBER IS NULL THEN
3412 x_out_rec.CARRIER_REFERENCE_NUMBER := p_in_rec.CARRIER_REFERENCE_NUMBER;
3413 END IF;
3414 ELSIF p_disabled_list(i) = '+CARRIER_REFERENCE_NUMBER' THEN
3415 IF p_in_rec.CARRIER_REFERENCE_NUMBER <> FND_API.G_MISS_CHAR
3416 OR p_in_rec.CARRIER_REFERENCE_NUMBER IS NULL THEN
3417 IF x_out_rec.CARRIER_REFERENCE_NUMBER IS NULL THEN
3418 x_out_rec.CARRIER_REFERENCE_NUMBER := p_in_rec.CARRIER_REFERENCE_NUMBER;
3419 END IF;
3420 END IF;
3421 ELSIF p_disabled_list(i) = 'CONSIGNEE_CARRIER_AC_NO' THEN
3422 IF p_in_rec.CONSIGNEE_CARRIER_AC_NO <> FND_API.G_MISS_CHAR
3423 OR p_in_rec.CONSIGNEE_CARRIER_AC_NO IS NULL THEN
3424 x_out_rec.CONSIGNEE_CARRIER_AC_NO := p_in_rec.CONSIGNEE_CARRIER_AC_NO;
3425 END IF;
3426 ELSIF p_disabled_list(i) = '+CONSIGNEE_CARRIER_AC_NO' THEN
3427 IF p_in_rec.CONSIGNEE_CARRIER_AC_NO <> FND_API.G_MISS_CHAR
3428 OR p_in_rec.CONSIGNEE_CARRIER_AC_NO IS NULL THEN
3429 IF x_out_rec.CONSIGNEE_CARRIER_AC_NO IS NULL THEN
3430 x_out_rec.CONSIGNEE_CARRIER_AC_NO := p_in_rec.CONSIGNEE_CARRIER_AC_NO;
3431 END IF;
3432 END IF;
3433 ELSIF p_disabled_list(i) IN ('+FREIGHT_TERMS_CODE', '+FREIGHT_TERMS_NAME') THEN
3434 IF p_in_rec.FREIGHT_TERMS_CODE <> FND_API.G_MISS_CHAR
3435 OR p_in_rec.FREIGHT_TERMS_CODE IS NULL THEN
3436 IF x_out_rec.FREIGHT_TERMS_CODE IS NULL THEN
3437 x_out_rec.FREIGHT_TERMS_CODE := p_in_rec.FREIGHT_TERMS_CODE;
3438 END IF;
3439 END IF;
3440 ELSE
3441 -- invalid name
3442 x_field_name := p_disabled_list(i);
3443 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3444 RETURN;
3445 --
3446 END IF;
3447 END LOOP;
3448
3449 EXCEPTION
3450 -- OTM R12, glog proj, add when Others exception handler
3451 WHEN others THEN
3452 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.ENABLE_FROM_LIST');
3453 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3454
3455 END enable_from_list;
3456
3457 --
3458 -- Overloaded procedure
3459 -- Bug 2678363: Added new parameter p_in_rec, in place of p_action
3460 --
3461 PROCEDURE Get_Disabled_List (
3462 p_trip_rec IN WSH_TRIPS_PVT.trip_rec_type
3463 , p_in_rec IN WSH_TRIPS_GRP.TripInRecType
3464 , x_return_status OUT NOCOPY VARCHAR2
3465 , x_msg_count OUT NOCOPY NUMBER
3466 , x_msg_data OUT NOCOPY VARCHAR2
3467 , x_trip_rec OUT NOCOPY WSH_TRIPS_PVT.trip_rec_type
3468 )
3469 IS
3470 l_disabled_list WSH_UTIL_CORE.column_tab_type;
3471 l_db_col_rec WSH_TRIPS_PVT.trip_rec_type;
3472 l_return_status VARCHAR2(30);
3473 l_field_name VARCHAR2(100);
3474 l_debug_on BOOLEAN;
3475 l_module_name CONSTANT VARCHAR2(100) :=
3476 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DISABLED_LIST';
3477
3478
3479 e_dp_no_entity EXCEPTION;
3480 e_bad_field EXCEPTION;
3481 e_all_disabled EXCEPTION ;
3482
3483 l_caller VARCHAR2(32767);
3484 --
3485 i number;
3486
3487 BEGIN
3488 --
3489 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3490 --
3491 IF l_debug_on IS NULL
3492 THEN
3493 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3494 END IF;
3495 --
3496 IF l_debug_on THEN
3497 --
3498 WSH_DEBUG_SV.push(l_module_name);
3499 WSH_DEBUG_SV.log(l_module_name,'trip_id',p_trip_rec.trip_id);
3500 WSH_DEBUG_SV.log(l_module_name,'Action', p_in_rec.action_code);
3501 WSH_DEBUG_SV.log(l_module_name,'Caller', p_in_rec.caller);
3502 --
3503 END IF;
3504 --
3505 x_return_status := FND_API.G_RET_STS_SUCCESS;
3506 --
3507 IF p_in_rec.action_code = 'CREATE' THEN
3508 --
3509 IF l_debug_on THEN
3510 WSH_DEBUG_SV.log(l_module_name,'calling eliminate_displayonly_fields');
3511 END IF;
3512 --
3513 -- nothing else need to be disabled
3514 --
3515 eliminate_displayonly_fields (p_trip_rec,p_in_rec,x_trip_rec);
3516 --
3517 --3509004:public api changes
3518 /*
3519 IF l_debug_on THEN
3520 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
3521 WSH_DEBUG_SV.pop(l_module_name);
3522 END IF;
3523 -- RETURN;
3524 --
3525 */
3526 ELSIF p_in_rec.action_code = 'UPDATE' THEN
3527 --
3528 l_caller := p_in_rec.caller;
3529 IF (l_caller like 'FTE%') THEN
3530 l_caller := 'WSH_PUB';
3531 END IF;
3532 Get_Disabled_List( p_trip_rec.trip_id
3533 , 'FORM'
3534 , x_return_status
3535 , l_disabled_list
3536 , x_msg_count
3537 , x_msg_data
3538 , l_caller --3509004:public api changes
3539 );
3540 --
3541 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR OR
3542 x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
3543 THEN
3544 --
3545 IF l_debug_on THEN
3546 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
3547 WSH_DEBUG_SV.pop(l_module_name);
3548 END IF;
3549 RETURN;
3550 --
3551 END IF;
3552 --
3553 IF l_disabled_list.COUNT = 1 THEN
3554 IF l_disabled_list(1) = 'FULL' THEN
3555 RAISE e_all_disabled;
3556 --Everything is disabled
3557 END IF;
3558 END IF;
3559 --
3560
3561 WSH_TRIPS_PVT.populate_record(
3562 p_trip_id => p_trip_rec.trip_id,
3563 x_trip_info => x_trip_rec,
3564 x_return_status => x_return_status);
3565
3566 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
3567 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
3568 RAISE e_dp_no_entity;
3569 END IF;
3570
3571 --
3572 -- bug 3398603: TP Release needs to always update these fields
3573 -- even if the trip is firmed.
3574
3575 -- OTM R12, glog project, allow GC3 Inbound Message to update tp_plan_name
3576 -- no change required for CREATE, caller = FTE_TMS_INTEGRATION
3577 IF p_in_rec.caller IN ('WSH_TP_RELEASE','FTE_TMS_INTEGRATION') THEN--{
3578 IF p_trip_rec.tp_plan_name <> FND_API.G_MISS_CHAR
3579 OR p_trip_rec.tp_plan_name IS NULL THEN
3580 x_trip_rec.tp_plan_name := p_trip_rec.tp_plan_name;
3581 END IF;
3582 IF p_trip_rec.tp_trip_number <> FND_API.G_MISS_NUM
3583 OR p_trip_rec.tp_trip_number IS NULL THEN
3584 x_trip_rec.tp_trip_number := p_trip_rec.tp_trip_number;
3585 END IF;
3586 END IF;--}
3587 -- OTM R12, glog proj,GC3 inbound message can also update Mode, Service, Carrier,Freight
3588 -- Ship Method Code, Vehicle Item id + Organization
3589 IF p_in_rec.caller = 'FTE_TMS_INTEGRATION' THEN --{
3590 IF p_trip_rec.mode_of_transport <> FND_API.G_MISS_CHAR
3591 OR p_trip_rec.mode_of_transport IS NULL THEN
3592 x_trip_rec.mode_of_transport := p_trip_rec.mode_of_transport;
3593 END IF;
3594 IF p_trip_rec.service_level <> FND_API.G_MISS_CHAR
3595 OR p_trip_rec.service_level IS NULL THEN
3596 x_trip_rec.service_level := p_trip_rec.service_level;
3597 END IF;
3598 IF p_trip_rec.carrier_id <> FND_API.G_MISS_NUM
3599 OR p_trip_rec.carrier_id IS NULL THEN
3600 x_trip_rec.carrier_id := p_trip_rec.carrier_id;
3601 END IF;
3602 IF p_trip_rec.freight_terms_code <> FND_API.G_MISS_CHAR
3603 OR p_trip_rec.freight_terms_code IS NULL THEN
3604 x_trip_rec.freight_terms_code := p_trip_rec.freight_terms_code;
3605 END IF;
3606 IF p_trip_rec.ship_method_code <> FND_API.G_MISS_CHAR
3607 OR p_trip_rec.ship_method_code IS NULL THEN
3608 x_trip_rec.ship_method_code := p_trip_rec.ship_method_code;
3609 END IF;
3610 IF p_trip_rec.vehicle_item_id <> FND_API.G_MISS_NUM
3611 OR p_trip_rec.vehicle_item_id IS NULL THEN
3612 x_trip_rec.vehicle_item_id := p_trip_rec.vehicle_item_id;
3613 END IF;
3614 IF p_trip_rec.vehicle_organization_id <> FND_API.G_MISS_NUM
3615 OR p_trip_rec.vehicle_organization_id IS NULL THEN
3616 x_trip_rec.vehicle_organization_id := p_trip_rec.vehicle_organization_id;
3617 END IF;
3618 END IF;--}
3619
3620 -- End of code added for OTM R12, glog proj
3621 --
3622
3623 --
3624 --
3625 IF l_debug_on THEN
3626 WSH_DEBUG_SV.log(l_module_name,'list.COUNT',l_disabled_list.COUNT);
3627 END IF;
3628 --
3629 IF l_disabled_list.COUNT = 0 THEN
3630 --
3631 IF l_debug_on THEN
3632 WSH_DEBUG_SV.log(l_module_name,'calling eliminate_displayonly_fields');
3633 END IF;
3634 --
3635 -- nothing else need to be disabled
3636 --
3637 eliminate_displayonly_fields (p_trip_rec,p_in_rec,x_trip_rec);
3638
3639 ELSIF l_disabled_list(1) = 'FULL' THEN
3640 --
3641 IF l_disabled_list.COUNT > 1 THEN
3642 --
3643 IF l_debug_on THEN
3644 FOR i in 1..l_disabled_list.COUNT
3645 LOOP
3646 WSH_DEBUG_SV.log(l_module_name,'list values',l_disabled_list(i));
3647 END LOOP;
3648 WSH_DEBUG_SV.log(l_module_name,'calling enable_from_list');
3649 END IF;
3650 --enable the columns matching the l_disabled_list
3651 enable_from_list(l_disabled_list,
3652 p_trip_rec,
3653 x_trip_rec,
3654 l_return_status,
3655 l_field_name);
3656 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
3657 RAISE e_bad_field;
3658 END IF;
3659 --
3660 END IF;
3661 --
3662 ELSE -- list.count > 1 and list(1) <> 'FULL'
3663 --
3664 l_db_col_rec := x_trip_rec ;
3665 --
3666 IF l_debug_on THEN
3667 FOR i in 1..l_disabled_list.COUNT
3668 LOOP
3669 WSH_DEBUG_SV.log(l_module_name,'list values',l_disabled_list(i));
3670 END LOOP;
3671 WSH_DEBUG_SV.log(l_module_name,'First element is not FULL');
3672 WSH_DEBUG_SV.log(l_module_name,'calling eliminate_displayonly_fields');
3673 END IF;
3674 --
3675 eliminate_displayonly_fields (p_trip_rec,p_in_rec,x_trip_rec);
3676 --
3677 IF l_debug_on THEN
3678 WSH_DEBUG_SV.log(l_module_name,'calling disable_from_list');
3679 END IF;
3680 -- The fileds in the list are getting disabled
3681 disable_from_list(l_disabled_list,
3682 l_db_col_rec,
3683 x_trip_rec,
3684 l_return_status,
3685 l_field_name
3686 );
3687 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
3688 RAISE e_bad_field;
3689 END IF;
3690 --
3691 END IF;
3692 --
3693 END IF; /* if action = 'UPDATE' */
3694 --3509004:public api changes
3695 IF (nvl(p_in_rec.caller,'''') <> 'WSH_FSTRX' AND
3696 nvl(p_in_rec.caller,'''') NOT LIKE 'FTE%') THEN
3697 --
3698 user_non_updatable_columns
3699 (p_user_in_rec => p_trip_rec,
3700 p_out_rec => x_trip_rec,
3701 p_in_rec => p_in_rec,
3702 x_return_status => l_return_status);
3703 --
3704 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
3705 x_return_status := l_return_status;
3706 END IF;
3707 --
3708 END IF;
3709 --
3710 IF l_debug_on THEN
3711 WSH_DEBUG_SV.pop(l_module_name);
3712 END IF;
3713 --
3714 EXCEPTION
3715 WHEN e_all_disabled THEN
3716 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3717 FND_MESSAGE.SET_NAME('WSH','WSH_ALL_COLS_DISABLED');
3718 FND_MESSAGE.Set_Token('ENTITY_ID',p_trip_rec.trip_id);
3719 wsh_util_core.add_message(x_return_status,l_module_name);
3720 IF l_debug_on THEN
3721 -- Nothing is updateable
3722 WSH_DEBUG_SV.pop(l_module_name,'e_all_disabled');
3723 END IF;
3724 WHEN e_dp_no_entity THEN
3725 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3726 -- the message for this is set in original get_disabled_list
3727 IF l_debug_on THEN
3728 WSH_DEBUG_SV.pop(l_module_name,'e_dp_no_entity');
3729 END IF;
3730 WHEN e_bad_field THEN
3731 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3732 FND_MESSAGE.SET_NAME('WSH','WSH_BAD_FIELD_NAME');
3733 FND_MESSAGE.Set_Token('FIELD_NAME',l_field_name);
3734 wsh_util_core.add_message(x_return_status,l_module_name);
3735 IF l_debug_on THEN
3736 WSH_DEBUG_SV.log(l_module_name,'Bad field name passed to the list:'
3737 ,l_field_name);
3738 WSH_DEBUG_SV.pop(l_module_name,'e_bad_field');
3739 END IF;
3740
3741 WHEN OTHERS THEN
3742 wsh_util_core.default_handler('WSH_DETAILS_VALIDATIONS.get_disabled_list'
3743 ,l_module_name);
3744 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3745 IF l_debug_on THEN
3746 WSH_DEBUG_SV.log(l_module_name,'Error:',SUBSTR(SQLERRM,1,200));
3747 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3748 END IF;
3749 END Get_Disabled_List;
3750
3751
3752
3753 PROCEDURE Init_Trip_Actions_Tbl (
3754 p_action IN VARCHAR2
3755 , x_Trip_actions_tab OUT NOCOPY TripActionsTabType
3756 , x_return_status OUT NOCOPY VARCHAR2
3757 )
3758
3759 IS
3760 l_debug_on BOOLEAN;
3761 l_module_name CONSTANT VARCHAR2(100) :=
3762 'wsh.plsql.' || G_PKG_NAME || '.' || 'Init_Trip_Actions_Tbl';
3763 i NUMBER := 0;
3764
3765 l_gc3_is_installed VARCHAR(1); --OTM R12, glog proj
3766
3767 BEGIN
3768 --
3769 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3770 --
3771 IF l_debug_on IS NULL
3772 THEN
3773 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3774 END IF;
3775
3776 --OTM R12, glog proj, use Global Variable
3777 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
3778
3779 -- If null, call the function
3780 IF l_gc3_is_installed IS NULL THEN
3781 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
3782 END IF;
3783 -- end of OTM R12, glog proj
3784
3785
3786 --
3787 -- J-IB-NPARIKH-{
3788 --
3789 -- Disable all actions on inbound stops when called from transactions form
3790 --
3791 i := i+1;
3792 x_Trip_actions_tab(i).shipments_type_flag := 'I';
3793 x_Trip_actions_tab(i).caller := 'WSH_FSTRX';
3794 x_Trip_actions_tab(i).action_not_allowed := p_action;
3795 -- J-IB-NPARIKH-}
3796 --
3797 IF p_action = 'TRIP-CONFIRM' THEN
3798
3799 i := i + 1;
3800 x_Trip_actions_tab(i).action_not_allowed := 'TRIP-CONFIRM';
3801 x_Trip_actions_tab(i).status_code := 'IT';
3802 i := i + 1;
3803 x_Trip_actions_tab(i).action_not_allowed := 'TRIP-CONFIRM';
3804 x_Trip_actions_tab(i).status_code := 'CL';
3805 i := i + 1;
3806 x_Trip_actions_tab(i).action_not_allowed := 'TRIP-CONFIRM';
3807 x_Trip_actions_tab(i).shipments_type_flag := 'I';
3808 END IF;
3809
3810 IF p_action = 'WT-VOL' THEN
3811 --
3812 -- Calculate weight/volume action is
3813 -- - always allowed for inbound trip
3814 -- - not allowed for outbound trip, once closed.
3815 -- - not allowed for mixed closed trip, if called from transactions form
3816 --
3817 i := i + 1;
3818 x_Trip_actions_tab(i).action_not_allowed := 'WT-VOL';
3819 x_Trip_actions_tab(i).shipments_type_flag := 'O'; -- J-IB-NPARIKH
3820 x_Trip_actions_tab(i).status_code := 'CL';
3821 -- J-IB-NPARIKH-{
3822 i := i + 1;
3823 x_Trip_actions_tab(i).action_not_allowed := 'WT-VOL';
3824 x_Trip_actions_tab(i).shipments_type_flag := 'M';
3825 x_Trip_actions_tab(i).caller := 'WSH_FSTRX';
3826 x_Trip_actions_tab(i).status_code := 'CL';
3827 -- J-IB-NPARIKH-}
3828
3829 END IF;
3830
3831 IF p_action = 'PICK-RELEASE-UI' THEN
3832 i := i + 1;
3833 x_Trip_actions_tab(i).action_not_allowed := 'PICK-RELEASE-UI';
3834 x_Trip_actions_tab(i).status_code := 'CL';
3835 i := i + 1;
3836 x_Trip_actions_tab(i).action_not_allowed := 'PICK-RELEASE-UI';
3837 x_Trip_actions_tab(i).shipments_type_flag := 'I';
3838
3839 --HVOP heali
3840 i := i + 1;
3841 x_Trip_actions_tab(i).action_not_allowed := 'PICK-RELEASE-UI';
3842 x_Trip_actions_tab(i).status_code := 'IT';
3843 --HVOP heali
3844 END IF;
3845 IF p_action = 'PICK-RELEASE' THEN
3846 i := i + 1;
3847 x_Trip_actions_tab(i).action_not_allowed := 'PICK-RELEASE';
3848 x_Trip_actions_tab(i).shipments_type_flag := 'I';
3849 --HVOP heali
3850 i := i + 1;
3851 x_Trip_actions_tab(i).action_not_allowed := 'PICK-RELEASE';
3852 x_Trip_actions_tab(i).status_code := 'IT';
3853 --HVOP heali
3854 END IF;
3855 IF p_action = 'ASSIGN-FREIGHT-COSTS' THEN
3856 i := i + 1;
3857 x_Trip_actions_tab(i).action_not_allowed := 'ASSIGN-FREIGHT-COSTS';
3858 x_Trip_actions_tab(i).shipments_type_flag := 'I';
3859 END IF;
3860 IF p_action = 'PRINT-DOC-SET' THEN
3861 i := i + 1;
3862 x_Trip_actions_tab(i).action_not_allowed := 'PRINT-DOC-SET';
3863 x_Trip_actions_tab(i).shipments_type_flag := 'I';
3864 END IF;
3865
3866 IF p_action = 'SELECT-CARRIER' THEN
3867
3868 i := i + 1;
3869 x_Trip_actions_tab(i).action_not_allowed := 'SELECT-CARRIER';
3870 x_Trip_actions_tab(i).status_code := 'IT';
3871 x_Trip_actions_tab(i).message_name := 'WSH_FTE_SEL_TRIP_STATUS';
3872 i := i + 1;
3873 x_Trip_actions_tab(i).action_not_allowed := 'SELECT-CARRIER';
3874 x_Trip_actions_tab(i).status_code := 'CL';
3875 x_Trip_actions_tab(i).message_name := 'WSH_FTE_SEL_TRIP_STATUS';
3876 i := i + 1;
3877 x_Trip_actions_tab(i).action_not_allowed := 'SELECT-CARRIER';
3878 x_Trip_actions_tab(i).planned_flag := 'Y';
3879 x_Trip_actions_tab(i).message_name := 'WSH_FTE_SEL_TRIP_PLANNED';
3880 i := i + 1;
3881 x_Trip_actions_tab(i).action_not_allowed := 'SELECT-CARRIER';
3882 x_Trip_actions_tab(i).planned_flag := 'F';
3883 x_Trip_actions_tab(i).message_name := 'WSH_FTE_SEL_TRIP_PLANNED';
3884 i := i + 1;
3885 x_Trip_actions_tab(i).action_not_allowed := 'SELECT-CARRIER';
3886 x_Trip_actions_tab(i).load_tender_status := 'TENDERED';
3887 x_Trip_actions_tab(i).message_name := 'WSH_FTE_SEL_TRIP_LT_STATUS';
3888 i := i + 1;
3889 x_Trip_actions_tab(i).action_not_allowed := 'SELECT-CARRIER';
3890 x_Trip_actions_tab(i).load_tender_status := 'ACCEPTED';
3891 x_Trip_actions_tab(i).message_name := 'WSH_FTE_SEL_TRIP_LT_STATUS';
3892 i := i + 1;
3893 x_Trip_actions_tab(i).action_not_allowed := 'SELECT-CARRIER';
3894 x_Trip_actions_tab(i).load_tender_status := 'AUTO_ACCEPTED';
3895 x_Trip_actions_tab(i).message_name := 'WSH_FTE_SEL_TRIP_LT_STATUS';
3896
3897 END IF;
3898
3899 --OTM R12, glog proj
3900 IF (l_gc3_is_installed = 'Y') THEN
3901
3902 -- Disable Include/Ignore actions if OTM is installed
3903 IF p_action IN ('IGNORE_PLAN', 'INCLUDE_PLAN') THEN
3904 i := i + 1;
3905 x_trip_actions_tab(i).action_not_allowed := p_action;
3906 END IF;
3907
3908 -- Disable Routing Firm, Routing and Contents Firm, Unfirm for
3909 -- include for planning trips only
3910 IF p_action IN ('FIRM', 'PLAN', 'UNPLAN') THEN
3911 i := i + 1;
3912 x_trip_actions_tab(i).action_not_allowed := p_action;
3913 x_trip_actions_tab(i).ignore_for_planning := 'N';
3914 END IF;
3915
3916 END IF;
3917
3918 -- bug 5837425
3919 IF p_action IN ('IGNORE_PLAN', 'INCLUDE_PLAN') THEN
3920 i := i + 1;
3921 x_trip_actions_tab(i).status_code := 'IT';
3922 x_trip_actions_tab(i).action_not_allowed := p_action;
3923 i := i + 1;
3924 x_trip_actions_tab(i).status_code := 'CL';
3925 x_trip_actions_tab(i).action_not_allowed := p_action;
3926 END IF;
3927 -- bug 5837425
3928
3929 --
3930 IF l_debug_on THEN
3931 WSH_DEBUG_SV.push(l_module_name);
3932 --
3933 WSH_DEBUG_SV.log(l_module_name,'p_action', p_action);
3934 END IF;
3935 --
3936 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3937
3938 IF l_debug_on THEN
3939 WSH_DEBUG_SV.pop(l_module_name);
3940 END IF;
3941
3942 EXCEPTION
3943 -- OTM R12, glog proj
3944 WHEN OTHERS THEN
3945 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_TRIP_VALIDATIONS.init_trip_actions_tbl'
3946 ,l_module_name);
3947 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3948 IF l_debug_on THEN
3949 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3950 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3951 END IF;
3952
3953
3954 END Init_Trip_Actions_Tbl;
3955
3956 -- for Load Tender Project
3957 /*
3958 -----------------------------------------------------------------------------
3959 PROCEDURE : Get Trip Calc Wtvol
3960 PARAMETERS : p_tab_id - entity id
3961 p_entity - entity name -DELIVERY,TRIP,TRIP_STOP,DELIVERY_DETAIL
3962 p_action_code - action code for each action
3963 p_phase - 1 for Before the action is performed, 2 for after.
3964 x_trip_id_tab - Table of Trip ids
3965 x_return_status - Return Status
3966 DESCRIPTION : This procedure finds the trip for each entity on the basis
3967 of p_entity.After the trip is determined, calculate the
3968 weight/volume for the trip.
3969 ------------------------------------------------------------------------------
3970 */
3971 -- THIS PROCEDURE IS OBSOLETE
3972 PROCEDURE Get_Trip_Calc_Wtvol
3973 (p_tab_id IN wsh_util_core.id_tab_type,
3974 p_entity IN VARCHAR2,
3975 p_action_code IN VARCHAR2,
3976 p_phase IN NUMBER,
3977 x_trip_id_tab IN OUT NOCOPY wsh_util_core.id_tab_type,
3978 x_return_status OUT NOCOPY VARCHAR2
3979 ) IS
3980
3981 BEGIN
3982
3983 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3984
3985 END get_trip_calc_wtvol;
3986
3987 -- End for Load Tender Project
3988 --
3989 -- J-IB-NPARIKH-{
3990 --
3991 --========================================================================
3992 -- PROCEDURE : Check_close
3993 --
3994 -- PARAMETERS: p_in_rec Input Record (Refer to WSHTRVLS.pls for description)
3995 -- x_allowed Trip can be closed or not
3996 -- 'Y' : Allowed
3997 -- 'YW': Allowed with warnings
3998 -- 'N' : Not Allowed
3999 -- 'NW': Not Allowed with warnings
4000 -- x_return_status Return status of API
4001 --
4002 --
4003 -- COMMENT : This function checks if trip can be closed or not.
4004 --
4005 -- It performs following validations:
4006 -- 01. Check if trip has any stops which is not closed. IF so, trip close is not
4007 -- allowed else it is allowed.
4008 -- 02. Check for exceptions logged against trip and its contents
4009 --========================================================================
4010 --
4011 PROCEDURE check_Close
4012 (
4013 p_in_rec IN ChgStatus_in_rec_type,
4014 x_return_status OUT NOCOPY VARCHAR2,
4015 x_allowed OUT NOCOPY VARCHAR2
4016 )
4017 IS
4018 --{
4019 --
4020 -- Check if trip has any stop which is not closed yet.
4021 --
4022 -- When linked_stop_id is passed, that stop
4023 -- will also get closed.
4024 --
4025 CURSOR any_open_stop (p_trip_id NUMBER,
4026 p_stop_id NUMBER,
4027 p_linked_stop_id NUMBER) IS
4028 SELECT stop_id
4029 FROM wsh_trip_stops
4030 WHERE trip_id = p_trip_id
4031 AND stop_id <> NVL(p_stop_id,-9999)
4032 AND stop_id <> NVL(p_linked_stop_id,-9999)
4033 AND status_code <> 'CL';
4034 --
4035 l_num_warnings NUMBER;
4036 l_num_errors NUMBER;
4037 l_dummy NUMBER;
4038 --
4039 -- Exception variables
4040 l_exceptions_tab wsh_xc_util.XC_TAB_TYPE;
4041 l_exp_logged BOOLEAN := FALSE;
4042 l_exp_warning BOOLEAN := FALSE;
4043 l_msg_count NUMBER;
4044 l_msg_data VARCHAR2(4000);
4045 l_return_status VARCHAR2(1);
4046 -- Following three variables are added for BugFix #3947506
4047 l_out_entity_id VARCHAR2(100);
4048 l_out_entity_name VARCHAR2(100);
4049 l_out_status VARCHAR2(100);
4050 --
4051 l_debug_on BOOLEAN;
4052 --
4053 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || g_pkg_name || '.' || 'check_Close';
4054 --}
4055 BEGIN
4056 --{
4057 --
4058 l_debug_on := wsh_debug_interface.g_debug;
4059 --
4060 IF l_debug_on IS NULL THEN
4061 l_debug_on := wsh_debug_sv.is_debug_enabled;
4062 END IF;
4063 --
4064 IF l_debug_on THEN
4065 wsh_debug_sv.push(l_module_name);
4066 --
4067 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.trip_id ', p_in_rec.trip_id );
4068 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.name ', p_in_rec.name );
4069 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.put_messages', p_in_rec.put_messages);
4070 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.manual_flag ', p_in_rec.manual_flag );
4071 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.caller ', p_in_rec.caller );
4072 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.actual_date ', p_in_rec.actual_date );
4073 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.stop_id ', p_in_rec.stop_id );
4074 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.linked_stop_id ', p_in_rec.linked_stop_id );
4075 END IF;
4076 --
4077 --
4078 l_num_warnings := 0;
4079 l_num_errors := 0;
4080 --
4081 -- Check if trip has any stops which is not closed. IF so, trip close is not
4082 -- allowed else it is allowed.
4083 --
4084 OPEN any_open_stop(p_in_rec.trip_id, p_in_rec.stop_id, p_in_rec.linked_stop_id);
4085 FETCH any_open_stop INTO l_dummy;
4086 --
4087 IF any_open_stop%FOUND
4088 THEN
4089 CLOSE any_open_stop;
4090 --bug 3410681
4091 x_allowed := 'NT';
4092
4093 RAISE wsh_util_core.e_not_allowed;
4094 END IF;
4095 --
4096 CLOSE any_open_stop;
4097 --
4098
4099 -- Check for Exceptions against the Trip
4100 IF l_debug_on THEN
4101 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.Check_Exceptions',WSH_DEBUG_SV.C_PROC_LEVEL);
4102 END IF;
4103 l_exceptions_tab.delete;
4104 l_exp_logged := FALSE;
4105 l_exp_warning := FALSE;
4106 WSH_XC_UTIL.Check_Exceptions (
4107 p_api_version => 1.0,
4108 x_return_status => l_return_status,
4109 x_msg_count => l_msg_count,
4110 x_msg_data => l_msg_data,
4111 p_logging_entity_id => p_in_rec.trip_id,
4112 p_logging_entity_name => 'TRIP',
4113 p_consider_content => 'Y',
4114 x_exceptions_tab => l_exceptions_tab
4115 );
4116 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
4117 x_return_status := l_return_status;
4118 wsh_util_core.add_message(x_return_status);
4119 RAISE FND_API.G_EXC_ERROR;
4120 END IF;
4121 FOR exp_cnt in 1..l_exceptions_tab.COUNT LOOP
4122 IF l_exceptions_tab(exp_cnt).exception_behavior = 'ERROR' THEN
4123 IF l_exceptions_tab(exp_cnt).entity_name = 'TRIP' THEN
4124 FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_ENTITY');
4125 ELSE
4126 FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_CONTENTS');
4127 END IF;
4128
4129 -- BugFix #3947506
4130 WSH_UTIL_CORE.Get_Entity_Name
4131 ( l_exceptions_tab(exp_cnt).entity_id,
4132 l_exceptions_tab(exp_cnt).entity_name,
4133 l_out_entity_id,
4134 l_out_entity_name,
4135 l_out_status);
4136
4137 IF ( l_out_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
4138 wsh_util_core.add_message(l_out_status);
4139 RAISE FND_API.G_EXC_ERROR;
4140 END IF;
4141 -- End of code BugFix #3947506
4142
4143 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',l_out_entity_name); -- BugFix #3947506
4144 FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_out_entity_id); -- BugFix #3947506
4145 FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Error');
4146 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4147 wsh_util_core.add_message(x_return_status);
4148 l_num_warnings := l_num_warnings + 1;
4149
4150 --bug 3410681
4151 x_allowed := 'N';
4152 l_out_entity_id := ''; -- BugFix #3947506
4153 l_out_entity_name := ''; -- BugFix #3947506
4154
4155 RAISE wsh_util_core.e_not_allowed;
4156 ELSIF l_exceptions_tab(exp_cnt).exception_behavior = 'WARNING' THEN
4157 IF l_exceptions_tab(exp_cnt).entity_name = 'TRIP' THEN
4158 FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_ENTITY');
4159 FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
4160 FND_MESSAGE.SET_TOKEN('ENTITY_ID',wsh_trips_pvt.get_name(l_exceptions_tab(exp_cnt).entity_id));
4161 FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Warning');
4162 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4163 wsh_util_core.add_message(x_return_status);
4164 l_num_warnings := l_num_warnings + 1;
4165 l_exp_warning := TRUE;
4166 ELSIF NOT (l_exp_logged) THEN
4167 -- BugFix #3947506
4168 WSH_UTIL_CORE.Get_Entity_Name
4169 ( l_exceptions_tab(exp_cnt).entity_id,
4170 l_exceptions_tab(exp_cnt).entity_name,
4171 l_out_entity_id,
4172 l_out_entity_name,
4173 l_out_status);
4174
4175 IF ( l_out_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
4176 wsh_util_core.add_message(l_out_status);
4177 RAISE FND_API.G_EXC_ERROR;
4178 END IF;
4179 -- End of code BugFix #3947506
4180 FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_CONTENTS');
4181 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',l_out_entity_name); -- BugFix #3947506
4182 FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_out_entity_id); -- BugFix #3947506
4183 FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Warning');
4184 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4185 l_exp_logged := TRUE;
4186 wsh_util_core.add_message(x_return_status);
4187 l_num_warnings := l_num_warnings + 1;
4188 l_exp_warning := TRUE;
4189 l_out_entity_id := ''; -- BugFix #3947506
4190 l_out_entity_name := ''; -- BugFix #3947506
4191 END IF;
4192 END IF;
4193 END LOOP;
4194 --
4195
4196 IF l_num_errors > 0
4197 THEN
4198 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4199 x_allowed := 'N';
4200 ELSIF l_num_warnings > 0
4201 THEN
4202 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4203 ELSE
4204 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4205 END IF;
4206 --
4207 -- If Exceptions have warnings, then display warnings but allow to proceed
4208 IF l_exp_warning THEN
4209 x_allowed := 'YW';
4210 ELSE
4211 x_allowed := 'Y';
4212 END IF;
4213 --
4214 -- Debug Statements
4215 --
4216 IF l_debug_on THEN
4217 WSH_DEBUG_SV.pop(l_module_name);
4218 END IF;
4219 --
4220 --}
4221 EXCEPTION
4222 --{
4223 WHEN wsh_util_core.e_not_allowed THEN
4224 IF l_num_warnings > 0
4225 THEN
4226 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4227 ELSE
4228 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4229 END IF;
4230 --
4231 --
4232 --
4233 IF l_debug_on THEN
4234 WSH_DEBUG_SV.logmsg(l_module_name,'e_not_allowed exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4235 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:e_not_allowed');
4236 END IF;
4237 --
4238 WHEN wsh_util_core.e_not_allowed_warning THEN
4239 IF l_num_warnings > 0
4240 THEN
4241 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4242 ELSE
4243 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4244 END IF;
4245 --
4246 x_allowed := 'NW';
4247 --
4248 --
4249 IF l_debug_on THEN
4250 WSH_DEBUG_SV.logmsg(l_module_name,'e_not_allowed_warning exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4251 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:e_not_allowed_warning');
4252 END IF;
4253 --
4254 WHEN FND_API.G_EXC_ERROR THEN
4255
4256 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4257 --
4258 IF l_debug_on THEN
4259 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4260 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4261 END IF;
4262 --
4263 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4264
4265 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4266 --
4267 IF l_debug_on THEN
4268 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4269 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
4270 END IF;
4271 --
4272 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
4273 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4274 --
4275 IF l_debug_on THEN
4276 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4277 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
4278 END IF;
4279 --
4280 WHEN OTHERS THEN
4281
4282 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4283 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.check_Close',l_module_name);
4284 --
4285 IF l_debug_on THEN
4286 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4287 END IF;
4288 --
4289 --}
4290 END check_Close;
4291 --
4292 --
4293 --========================================================================
4294 -- PROCEDURE : check_inTransit
4295 --
4296 -- PARAMETERS: p_in_rec Input Record (Refer to WSHTRVLS.pls for description)
4297 -- x_allowed Trip can be closed or not
4298 -- 'Y' : Allowed
4299 -- 'YW': Allowed with warnings
4300 -- 'N' : Not Allowed
4301 -- 'NW': Not Allowed with warnings
4302 -- x_return_status Return status of API
4303 --
4304 --
4305 -- COMMENT : This function checks if trip can be set to in-transit or not.
4306 --
4307 -- It performs following validations:
4308 -- 01. Check if trip has any stops which is closed. IF so, trip can be
4309 -- set to in-transit else not. This check is done ONLY when
4310 -- it is being called with Stop_Id as NULL
4311 -- 02. Check for exceptions logged against trip and its contents
4312 --========================================================================
4313 --
4314 PROCEDURE check_inTransit
4315 (
4316 p_in_rec IN ChgStatus_in_rec_type,
4317 x_return_status OUT NOCOPY VARCHAR2,
4318 x_allowed OUT NOCOPY VARCHAR2
4319 )
4320 IS
4321 --{
4322 --
4323 -- Check if trip has any closed stop
4324 --
4325 CURSOR any_closed_stop (p_trip_id NUMBER) IS
4326 SELECT stop_id
4327 FROM wsh_trip_stops
4328 WHERE trip_id = p_trip_id
4329 AND status_code = 'CL';
4330 --
4331 l_num_warnings NUMBER;
4332 l_num_errors NUMBER;
4333 l_dummy NUMBER;
4334 --
4335 -- Exception variables
4336 l_exceptions_tab wsh_xc_util.XC_TAB_TYPE;
4337 l_exp_logged BOOLEAN := FALSE;
4338 l_exp_warning BOOLEAN := FALSE;
4339 l_msg_count NUMBER;
4340 l_msg_data VARCHAR2(4000);
4341 l_return_status VARCHAR2(1);
4342 -- Following three variables are added for BugFix #3947506
4343 l_out_entity_id VARCHAR2(100);
4344 l_out_entity_name VARCHAR2(100);
4345 l_out_status VARCHAR2(1);
4346 --
4347 l_debug_on BOOLEAN;
4348 --
4349 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || g_pkg_name || '.' || 'check_inTransit';
4350 --}
4351 BEGIN
4352 --{
4353 --
4354 l_debug_on := wsh_debug_interface.g_debug;
4355 --
4356 IF l_debug_on IS NULL THEN
4357 l_debug_on := wsh_debug_sv.is_debug_enabled;
4358 END IF;
4359 --
4360 IF l_debug_on THEN
4361 wsh_debug_sv.push(l_module_name);
4362 --
4363 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.trip_id ', p_in_rec.trip_id );
4364 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.name ', p_in_rec.name );
4365 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.put_messages', p_in_rec.put_messages);
4366 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.manual_flag ', p_in_rec.manual_flag );
4367 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.caller ', p_in_rec.caller );
4368 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.actual_date ', p_in_rec.actual_date );
4369 wsh_debug_sv.LOG(l_module_name, 'p_in_rec.stop_id ', p_in_rec.stop_id );
4370 END IF;
4371 --
4372 --
4373 l_num_warnings := 0;
4374 l_num_errors := 0;
4375 --
4376 --
4377 -- Stop_id is not null only when called from WSH_TRIP_STOP_VALIDATIONS.Check_Stop_Close api
4378 -- Otherwise it should be null when called from other APIs
4379 -- This check is required due to inbound logistics changes, trip needs to be set in-transit
4380 -- when any stop of trip is closed
4381 IF p_in_rec.stop_id IS NULL THEN
4382 OPEN any_closed_stop(p_in_rec.trip_id);
4383 FETCH any_closed_stop INTO l_dummy;
4384 --
4385 -- Check if trip has any stops which is closed. IF so, trip can be
4386 -- set to in-transit else not.
4387 --
4388 IF any_closed_stop%NOTFOUND
4389 THEN
4390 CLOSE any_closed_stop;
4391 RAISE wsh_util_core.e_not_allowed;
4392 END IF;
4393 --
4394 CLOSE any_closed_stop;
4395 END IF;
4396 --
4397
4398 -- Check for Exceptions against the Trip
4399 IF l_debug_on THEN
4400 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.Check_Exceptions',WSH_DEBUG_SV.C_PROC_LEVEL);
4401 END IF;
4402 l_exceptions_tab.delete;
4403 l_exp_logged := FALSE;
4404 l_exp_warning := FALSE;
4405 WSH_XC_UTIL.Check_Exceptions (
4406 p_api_version => 1.0,
4407 x_return_status => l_return_status,
4408 x_msg_count => l_msg_count,
4409 x_msg_data => l_msg_data,
4410 p_logging_entity_id => p_in_rec.trip_id,
4411 p_logging_entity_name => 'TRIP',
4412 p_consider_content => 'Y',
4413 x_exceptions_tab => l_exceptions_tab
4414 );
4415 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
4416 x_return_status := l_return_status;
4417 wsh_util_core.add_message(x_return_status);
4418 RAISE FND_API.G_EXC_ERROR;
4419 END IF;
4420 FOR exp_cnt in 1..l_exceptions_tab.COUNT LOOP
4421 IF l_exceptions_tab(exp_cnt).exception_behavior = 'ERROR' THEN
4422 IF l_exceptions_tab(exp_cnt).entity_name = 'TRIP' THEN
4423 FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_ENTITY');
4424 ELSE
4425 FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_CONTENTS');
4426 END IF;
4427
4428 -- BugFix #3947506
4429 WSH_UTIL_CORE.Get_Entity_Name
4430 ( l_exceptions_tab(exp_cnt).entity_id,
4431 l_exceptions_tab(exp_cnt).entity_name,
4432 l_out_entity_id,
4433 l_out_entity_name,
4434 l_out_status);
4435
4436 IF ( l_out_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
4437 wsh_util_core.add_message(l_out_status);
4438 RAISE FND_API.G_EXC_ERROR;
4439 END IF;
4440 -- End of code BugFix #3947506
4441
4442 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',l_out_entity_name); -- BugFix #3947506
4443 FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_out_entity_id); -- BugFix #3947506
4444 FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Error');
4445 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4446 wsh_util_core.add_message(x_return_status);
4447 l_num_warnings := l_num_warnings + 1;
4448 RAISE wsh_util_core.e_not_allowed;
4449 ELSIF l_exceptions_tab(exp_cnt).exception_behavior = 'WARNING' THEN
4450 IF l_exceptions_tab(exp_cnt).entity_name = 'TRIP' THEN
4451 FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_ENTITY');
4452 FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Trip');
4453 FND_MESSAGE.SET_TOKEN('ENTITY_ID',wsh_trips_pvt.get_name(l_exceptions_tab(exp_cnt).entity_id));
4454 FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Warning');
4455 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4456 wsh_util_core.add_message(x_return_status);
4457 l_num_warnings := l_num_warnings + 1;
4458 l_exp_warning := TRUE;
4459 ELSIF NOT (l_exp_logged) THEN
4460 -- BugFix #3947506
4461 WSH_UTIL_CORE.Get_Entity_Name
4462 ( l_exceptions_tab(exp_cnt).entity_id,
4463 l_exceptions_tab(exp_cnt).entity_name,
4464 l_out_entity_id,
4465 l_out_entity_name,
4466 l_out_status);
4467
4468 IF ( l_out_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
4469 wsh_util_core.add_message(l_out_status);
4470 RAISE FND_API.G_EXC_ERROR;
4471 END IF;
4472 -- End of code BugFix #3947506
4473
4474 FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_CONTENTS');
4475 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',l_out_entity_name); -- BugFix #3947506
4476 FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_out_entity_id); -- BugFix #3947506
4477 FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Warning');
4478 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4479 l_exp_logged := TRUE;
4480 wsh_util_core.add_message(x_return_status);
4481 l_num_warnings := l_num_warnings + 1;
4482 l_exp_warning := TRUE;
4483 END IF;
4484 END IF;
4485 END LOOP;
4486 --
4487
4488 IF l_num_errors > 0
4489 THEN
4490 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4491 x_allowed := 'N';
4492 ELSIF l_num_warnings > 0
4493 THEN
4494 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4495 ELSE
4496 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4497 END IF;
4498 --
4499 --
4500 -- If Exceptions have warnings, then display warnings but allow to proceed
4501 IF l_exp_warning THEN
4502 x_allowed := 'YW';
4503 ELSE
4504 x_allowed := 'Y';
4505 END IF;
4506 --
4507 -- Debug Statements
4508 --
4509 IF l_debug_on THEN
4510 WSH_DEBUG_SV.pop(l_module_name);
4511 END IF;
4512 --
4513 --}
4514 EXCEPTION
4515 --{
4516 WHEN wsh_util_core.e_not_allowed THEN
4517 IF l_num_warnings > 0
4518 THEN
4519 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4520 ELSE
4521 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4522 END IF;
4523 --
4524 x_allowed := 'N';
4525 --
4526 --
4527 IF l_debug_on THEN
4528 WSH_DEBUG_SV.logmsg(l_module_name,'e_not_allowed exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4529 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:e_not_allowed');
4530 END IF;
4531 --
4532 WHEN wsh_util_core.e_not_allowed_warning THEN
4533 IF l_num_warnings > 0
4534 THEN
4535 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4536 ELSE
4537 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4538 END IF;
4539 --
4540 x_allowed := 'NW';
4541 --
4542 --
4543 IF l_debug_on THEN
4544 WSH_DEBUG_SV.logmsg(l_module_name,'e_not_allowed_warning exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4545 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:e_not_allowed_warning');
4546 END IF;
4547 --
4548 WHEN FND_API.G_EXC_ERROR THEN
4549
4550 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4551 --
4552 IF l_debug_on THEN
4553 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4554 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4555 END IF;
4556 --
4557 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4558
4559 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4560 --
4561 IF l_debug_on THEN
4562 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4563 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
4564 END IF;
4565 --
4566 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
4567 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4568 --
4569 IF l_debug_on THEN
4570 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4571 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
4572 END IF;
4573 --
4574 WHEN OTHERS THEN
4575
4576 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4577 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.check_inTransit',l_module_name);
4578 --
4579 IF l_debug_on THEN
4580 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4581 END IF;
4582 --
4583 --}
4584 END check_inTransit;
4585 --
4586 --
4587 --========================================================================
4588 -- PROCEDURE : Validate_Trip_status
4589 --
4590 -- PARAMETERS: p_trip_id Trip ID
4591 -- p_action Action (Not used)
4592 -- x_return_status Return status of API
4593 --
4594 --
4595 -- COMMENT : This API is called by delivery group API while assigning delivery to trip
4596 --
4597 -- It performs following validations:
4598 -- 01. IF trip is routing and content firm or trip is in-transit/closed, delivery
4599 -- cannot be assigned to the trip
4600 --========================================================================
4601 --
4602 --
4603 PROCEDURE Validate_Trip_status
4604 (
4605 p_trip_id IN NUMBER,
4606 p_action IN VARCHAR2,
4607 x_return_status OUT NOCOPY VARCHAR2
4608 )
4609 IS
4610 --{
4611 /* J TP Release : If assigning del to trip doesn't introduce new stops, ok to assign to planned trips */
4612
4613 CURSOR trip_csr (p_trip_id NUMBER)
4614 IS
4615 select status_code, name, nvl(planned_flag,'N') planned_flag
4616 from wsh_trips
4617 where trip_id = p_trip_id;
4618 --
4619 l_trip_rec trip_csr%ROWTYPE;
4620 --
4621 l_debug_on BOOLEAN;
4622 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_TRIP_STATUS';
4623 --}
4624 BEGIN
4625 --{
4626 --
4627 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4628 --
4629 IF l_debug_on IS NULL
4630 THEN
4631 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4632 END IF;
4633 --
4634 IF l_debug_on THEN
4635 WSH_DEBUG_SV.push(l_module_name);
4636 WSH_DEBUG_SV.log(l_module_name,'p_trip_id',p_trip_id);
4637 WSH_DEBUG_SV.log(l_module_name,'p_action',p_action);
4638 END IF;
4639 --
4640 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4641 --
4642 IF (p_trip_id IS NOT NULL)
4643 THEN
4644 --{
4645 OPEN trip_csr(p_trip_id);
4646 FETCH trip_csr INTO l_trip_rec;
4647 CLOSE trip_csr;
4648 --
4649 IF l_trip_rec.planned_flag NOT IN ('N','Y')
4650 THEN
4651 -- Trip is routing and content firm.
4652 RAISE wsh_util_core.e_not_allowed;
4653 END IF;
4654 --
4655 IF l_trip_rec.status_code IN ('IT','CL')
4656 THEN
4657 RAISE wsh_util_core.e_not_allowed;
4658 END IF;
4659 --
4660 --}
4661 END IF;
4662 --
4663 IF l_debug_on THEN
4664 WSH_DEBUG_SV.pop(l_module_name);
4665 END IF;
4666 --}
4667 EXCEPTION
4668 --{
4669 WHEN wsh_util_core.e_not_allowed THEN
4670 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4671 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_STATUS_NO_ACTION');
4672 FND_MESSAGE.SET_TOKEN('TRIP_NAME', l_trip_rec.name);
4673 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
4674 --
4675 IF l_debug_on THEN
4676 WSH_DEBUG_SV.logmsg(l_module_name,'e_not_allowed exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4677 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:e_not_allowed');
4678 END IF;
4679 --
4680 WHEN OTHERS THEN
4681 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4682 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.validate_trip_status', l_module_name);
4683 IF l_debug_on THEN
4684 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4685 END IF;
4686 --}
4687 END validate_trip_status;
4688 --
4689 --
4690 --
4691 --========================================================================
4692 -- FUNCTION : has_outbound_deliveries
4693 --
4694 -- PARAMETERS: p_trip_id Trip ID
4695 -- p_stop_id Stop ID (Optional)
4696 -- Returns 'Y' if trip/stop has outbound deliveries
4697 -- Returns 'N' if trip/stop does not have outbound deliveries
4698 --
4699 --
4700 -- COMMENT : This function determines if trip/stop has outbound (O/IO) deliveries
4701 -- associated with it.
4702 --
4703 -- If stop is passed, it checks if any outbound(O/IO) delivery is being
4704 -- picked up or dropped off at the stop. If so, it returns 'Y' else 'N'
4705 --
4706 -- If stop is not passed, it checks if any outbound(O/IO) delivery is being
4707 -- picked up or dropped off at any stop of the trip.
4708 -- If so, it returns 'Y' else 'N'
4709 --
4710 -- If trip id is not passed in, it returns 'N'
4711 -- If invalid trip/stop id is passed in, it returns 'N'
4712 --========================================================================
4713 --
4714 FUNCTION has_outbound_deliveries
4715 (
4716 p_trip_id IN NUMBER,
4717 p_stop_id IN NUMBER DEFAULT NULL
4718 )
4719 RETURN VARCHAR2
4720 IS
4721 --{
4722 --
4723 -- Look for outbound (O/IO) deliveries being picked up or dropped off at stop/trip.
4724 --
4725 CURSOR trip_csr (p_trip_id NUMBER, p_stop_id NUMBER)
4726 IS
4727 SELECT 1
4728 FROM wsh_delivery_legs wdl,
4729 wsh_new_deliveries wnd,
4730 wsh_trip_stops wts
4731 WHERE wts.trip_id = p_trip_id
4732 AND wdl.delivery_id = wnd.delivery_id
4733 AND NVL(wnd.shipment_direction,'O') IN ( 'O','IO' )
4734 AND ( p_stop_id is null or wts.stop_id = p_stop_id ) --Bugfix 3639920
4735 AND (
4736 wdl.pick_up_stop_id = wts.stop_id
4737 OR wdl.drop_off_stop_id = wts.stop_id
4738 );
4739 --
4740 l_cnt NUMBER := 0;
4741 --
4742 l_debug_on BOOLEAN;
4743 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'has_outbound_deliveries';
4744 --}
4745 BEGIN
4746 --{
4747 --
4748 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4749 --
4750 IF l_debug_on IS NULL
4751 THEN
4752 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4753 END IF;
4754 --
4755 IF l_debug_on THEN
4756 WSH_DEBUG_SV.push(l_module_name);
4757 WSH_DEBUG_SV.log(l_module_name,'p_trip_id',p_trip_id);
4758 WSH_DEBUG_SV.log(l_module_name,'p_stop_id',p_stop_id);
4759 END IF;
4760 --
4761 --
4762 l_cnt := 0;
4763 --
4764 IF (p_trip_id IS NOT NULL)
4765 THEN
4766 --{
4767 OPEN trip_csr(p_trip_id, p_stop_id);
4768 FETCH trip_csr INTO l_cnt;
4769 CLOSE trip_csr;
4770 --}
4771 END IF;
4772 --
4773 --
4774 IF l_debug_on THEN
4775 WSH_DEBUG_SV.log(l_module_name,'l_cnt',l_cnt);
4776 WSH_DEBUG_SV.pop(l_module_name);
4777 END IF;
4778 --
4779 IF l_cnt = 1
4780 THEN
4781 RETURN('Y');
4782 ELSE
4783 RETURN('N');
4784 END IF;
4785 --}
4786 EXCEPTION
4787 --{
4788 WHEN OTHERS THEN
4789 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.has_outbound_deliveries', l_module_name);
4790 --
4791 IF l_debug_on THEN
4792 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4793 END IF;
4794 --
4795 RAISE;
4796 --}
4797 END has_outbound_deliveries;
4798 --
4799 --
4800 --
4801 --========================================================================
4802 -- FUNCTION : has_inbound_deliveries
4803 --
4804 -- PARAMETERS: p_trip_id Trip ID
4805 -- p_stop_id Stop ID (Optional)
4806 -- Returns 'Y' if trip/stop has inbound deliveries
4807 -- Returns 'N' if trip/stop does not have inbound deliveries
4808 --
4809 --
4810 -- COMMENT : This function determines if trip/stop has inbound (not O/IO) deliveries
4811 -- associated with it.
4812 --
4813 -- If stop is passed, it checks if any inbound(not O/IO) delivery is being
4814 -- picked up or dropped off at the stop. If so, it returns 'Y' else 'N'
4815 --
4816 -- If stop is not passed, it checks if any inbound(not O/IO) delivery is being
4817 -- picked up or dropped off at any stop of the trip.
4818 -- If so, it returns 'Y' else 'N'
4819 --
4820 -- If trip id is not passed in, it returns 'N'
4821 -- If invalid trip/stop id is passed in, it returns 'N'
4822 --========================================================================
4823 --
4824 FUNCTION has_inbound_deliveries
4825 (
4826 p_trip_id IN NUMBER,
4827 p_stop_id IN NUMBER DEFAULT NULL
4828 )
4829 RETURN VARCHAR2
4830 IS
4831 --{
4832 --
4833 -- Look for inbound (not O/IO) deliveries being picked up or dropped off at stop/trip.
4834 --
4835 CURSOR trip_csr (p_trip_id NUMBER, p_stop_id NUMBER)
4836 IS
4837 SELECT 1
4838 FROM wsh_delivery_legs wdl,
4839 wsh_new_deliveries wnd,
4840 wsh_trip_stops wts
4841 WHERE wts.trip_id = p_trip_id
4842 AND wdl.delivery_id = wnd.delivery_id
4843 AND NVL(wnd.shipment_direction,'O') NOT IN ( 'O','IO' )
4844 AND ( p_stop_id is null or wts.stop_id = p_stop_id ) --Bugfix 3639920
4845 AND (
4846 wdl.pick_up_stop_id = wts.stop_id
4847 OR wdl.drop_off_stop_id = wts.stop_id
4848 );
4849 --
4850 l_cnt NUMBER := 0;
4851 --
4852 l_debug_on BOOLEAN;
4853 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'has_inbound_deliveries';
4854 --}
4855 BEGIN
4856 --{
4857 --
4858 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4859 --
4860 IF l_debug_on IS NULL
4861 THEN
4862 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4863 END IF;
4864 --
4865 IF l_debug_on THEN
4866 WSH_DEBUG_SV.push(l_module_name);
4867 WSH_DEBUG_SV.log(l_module_name,'p_trip_id',p_trip_id);
4868 WSH_DEBUG_SV.log(l_module_name,'p_stop_id',p_stop_id);
4869 END IF;
4870 --
4871 --
4872 l_cnt := 0;
4873 --
4874 IF (p_trip_id IS NOT NULL)
4875 THEN
4876 --{
4877 OPEN trip_csr(p_trip_id, p_stop_id);
4878 FETCH trip_csr INTO l_cnt;
4879 CLOSE trip_csr;
4880 --}
4881 END IF;
4882 --
4883 IF l_debug_on THEN
4884 WSH_DEBUG_SV.log(l_module_name,'l_cnt',l_cnt);
4885 WSH_DEBUG_SV.pop(l_module_name);
4886 END IF;
4887 --
4888 IF l_cnt = 1
4889 THEN
4890 RETURN('Y');
4891 ELSE
4892 RETURN('N');
4893 END IF;
4894 --
4895 --}
4896 EXCEPTION
4897 --{
4898 WHEN OTHERS THEN
4899 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.has_inbound_deliveries', l_module_name);
4900 --
4901 IF l_debug_on THEN
4902 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4903 END IF;
4904 --
4905 RAISE;
4906 --}
4907 END has_inbound_deliveries;
4908 --
4909 --
4910 --
4911 --========================================================================
4912 -- FUNCTION : has_mixed_deliveries
4913 --
4914 -- PARAMETERS: p_trip_id Trip ID
4915 -- p_stop_id Stop ID (Optional)
4916 -- Returns 'Y' if trip/stop has mixed deliveries
4917 -- Returns 'N' if trip/stop does not have mixed deliveries
4918 --
4919 --
4920 -- COMMENT : This function determines if trip/stop has mixed (both inbound and outbound)
4921 -- deliveries associated with it.
4922 --
4923 -- Following is the logic:
4924 -- 01. Call has_outbound_deliveries
4925 -- 02. Call has_inbound_deliveries
4926 -- 03. Set return values as follows, using outcome of steps above.
4927 --
4928 -- Has Outbound Has Inbound Return Value(Meaning)
4929 -- Y Y Y
4930 -- Y N NO (No,has only outbound)
4931 -- N Y NI (No,has only inbound)
4932 -- N N N (No,no deliveries)
4933 --
4934 -- If trip id is not passed in, it returns 'N'
4935 -- If invalid trip/stop id is passed in, it returns 'N'
4936 --========================================================================
4937 --
4938 FUNCTION has_mixed_deliveries
4939 (
4940 p_trip_id IN NUMBER,
4941 p_stop_id IN NUMBER DEFAULT NULL
4942 )
4943 RETURN VARCHAR2
4944 IS
4945 --{
4946 l_has_outbound_deliveries VARCHAR2(10);
4947 l_has_inbound_deliveries VARCHAR2(10);
4948 l_has_mixed_deliveries VARCHAR2(10);
4949 --
4950 l_debug_on BOOLEAN;
4951 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'has_mixed_deliveries';
4952 --}
4953 BEGIN
4954 --{
4955 --
4956 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4957 --
4958 IF l_debug_on IS NULL
4959 THEN
4960 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4961 END IF;
4962 --
4963 IF l_debug_on THEN
4964 WSH_DEBUG_SV.push(l_module_name);
4965 WSH_DEBUG_SV.log(l_module_name,'p_trip_id',p_trip_id);
4966 WSH_DEBUG_SV.log(l_module_name,'p_stop_id',p_stop_id);
4967 END IF;
4968 --
4969 --
4970 l_has_mixed_deliveries := 'N';
4971 --
4972 IF (p_trip_id IS NOT NULL)
4973 THEN
4974 --{
4975 IF l_debug_on THEN
4976 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_VALIDATIONS.has_outbound_deliveries',WSH_DEBUG_SV.C_PROC_LEVEL);
4977 END IF;
4978 --
4979 --
4980 l_has_outbound_deliveries := WSH_TRIP_VALIDATIONS.has_outbound_deliveries
4981 (
4982 p_trip_id => p_trip_id,
4983 p_stop_id => p_stop_id
4984 );
4985 --
4986 --
4987 IF l_debug_on THEN
4988 WSH_DEBUG_SV.log(l_module_name,'l_has_outbound_deliveries',l_has_outbound_deliveries);
4989 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_VALIDATIONS.has_inbound_deliveries',WSH_DEBUG_SV.C_PROC_LEVEL);
4990 END IF;
4991 --
4992 l_has_inbound_deliveries := WSH_TRIP_VALIDATIONS.has_inbound_deliveries
4993 (
4994 p_trip_id => p_trip_id,
4995 p_stop_id => p_stop_id
4996 );
4997 --
4998 IF l_has_outbound_deliveries = 'Y'
4999 AND l_has_inbound_deliveries = 'Y'
5000 THEN
5001 l_has_mixed_deliveries := 'Y';
5002 ELSIF l_has_outbound_deliveries = 'Y'
5003 AND l_has_inbound_deliveries = 'N'
5004 THEN
5005 l_has_mixed_deliveries := 'NO';
5006 ELSIF l_has_outbound_deliveries = 'N'
5007 AND l_has_inbound_deliveries = 'Y'
5008 THEN
5009 l_has_mixed_deliveries := 'NI';
5010 END IF;
5011 --}
5012 END IF;
5013 --
5014 --
5015 IF l_debug_on THEN
5016 WSH_DEBUG_SV.log(l_module_name,'l_has_inbound_deliveries',l_has_inbound_deliveries);
5017 WSH_DEBUG_SV.log(l_module_name,'l_has_mixed_deliveries',l_has_mixed_deliveries);
5018 WSH_DEBUG_SV.pop(l_module_name);
5019 END IF;
5020 --
5021 RETURN(l_has_mixed_deliveries);
5022 --}
5023 EXCEPTION
5024 --{
5025 WHEN OTHERS THEN
5026 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.has_mixed_deliveries', l_module_name);
5027 --
5028 IF l_debug_on THEN
5029 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5030 END IF;
5031 --
5032 RAISE;
5033 --}
5034 END has_mixed_deliveries;
5035 --
5036 -- J-IB-NPARIKH-}
5037 --
5038 --3509004:public api change
5039 PROCEDURE user_non_updatable_columns
5040 (p_user_in_rec IN WSH_TRIPS_PVT.trip_rec_type,
5041 p_out_rec IN WSH_TRIPS_PVT.trip_rec_type,
5042 p_in_rec IN WSH_TRIPS_GRP.TripInRecType,
5043 x_return_status OUT NOCOPY VARCHAR2)
5044
5045 IS
5046 l_attributes VARCHAR2(2500) ;
5047 k number;
5048 l_return_status VARCHAR2(1);
5049 l_debug_on BOOLEAN;
5050 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'user_non_updatable_columns';
5051
5052 BEGIN
5053
5054 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5055 --
5056 IF l_debug_on IS NULL
5057 THEN
5058 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5059 END IF;
5060 --
5061 IF l_debug_on THEN
5062 --
5063 WSH_DEBUG_SV.push(l_module_name);
5064 WSH_DEBUG_SV.log(l_module_name,'p_in_rec.caller',p_in_rec.caller);
5065 --
5066 END IF;
5067 --
5068 x_return_status := FND_API.G_RET_STS_SUCCESS;
5069 --
5070 IF p_user_in_rec.TRIP_ID <> FND_API.G_MISS_NUM
5071 AND NVL(p_user_in_rec.TRIP_ID,-99) <> NVL(p_out_rec.TRIP_ID,-99)
5072 THEN
5073 IF l_attributes IS NOT NULL THEN
5074 l_attributes := l_attributes || ', ';
5075 END IF;
5076 l_attributes := l_attributes || 'TRIP_ID';
5077 END IF;
5078
5079 IF p_user_in_rec.NAME <> FND_API.G_MISS_CHAR
5080 AND NVL(p_user_in_rec.NAME,'!!!') <> NVL(p_out_rec.NAME,'!!!')
5081 THEN
5082 IF l_attributes IS NOT NULL THEN
5083 l_attributes := l_attributes || ', ';
5084 END IF;
5085 l_attributes := l_attributes || 'NAME';
5086 END IF;
5087
5088 IF p_user_in_rec.PLANNED_FLAG <> FND_API.G_MISS_CHAR
5089 AND NVL(p_user_in_rec.PLANNED_FLAG,'!!!') <> NVL(p_out_rec.PLANNED_FLAG,'!!!')
5090 THEN
5091 IF l_attributes IS NOT NULL THEN
5092 l_attributes := l_attributes || ', ';
5093 END IF;
5094 l_attributes := l_attributes || 'PLANNED_FLAG';
5095 END IF;
5096
5097 IF p_user_in_rec.ARRIVE_AFTER_TRIP_ID <> FND_API.G_MISS_NUM
5098 AND NVL(p_user_in_rec.ARRIVE_AFTER_TRIP_ID,-99) <> NVL(p_out_rec.ARRIVE_AFTER_TRIP_ID,-99)
5099 THEN
5100 IF l_attributes IS NOT NULL THEN
5101 l_attributes := l_attributes || ', ';
5102 END IF;
5103 l_attributes := l_attributes || 'ARRIVE_AFTER_TRIP_ID';
5104 END IF;
5105
5106 IF p_user_in_rec.STATUS_CODE <> FND_API.G_MISS_CHAR
5107 AND NVL(p_user_in_rec.STATUS_CODE,'!!!') <> NVL(p_out_rec.STATUS_CODE,'!!!')
5108 THEN
5109 IF l_attributes IS NOT NULL THEN
5110 l_attributes := l_attributes || ', ';
5111 END IF;
5112 l_attributes := l_attributes || 'STATUS_CODE';
5113 END IF;
5114
5115 IF p_user_in_rec.VEHICLE_ITEM_ID <> FND_API.G_MISS_NUM
5116 AND NVL(p_user_in_rec.VEHICLE_ITEM_ID,-99) <> NVL(p_out_rec.VEHICLE_ITEM_ID,-99)
5117 THEN
5118 IF l_attributes IS NOT NULL THEN
5119 l_attributes := l_attributes || ', ';
5120 END IF;
5121 l_attributes := l_attributes || 'VEHICLE_ITEM_ID';
5122 END IF;
5123
5124 IF p_user_in_rec.VEHICLE_ORGANIZATION_ID <> FND_API.G_MISS_NUM
5125 AND NVL(p_user_in_rec.VEHICLE_ORGANIZATION_ID,-99) <> NVL(p_out_rec.VEHICLE_ORGANIZATION_ID,-99)
5126 THEN
5127 IF l_attributes IS NOT NULL THEN
5128 l_attributes := l_attributes || ', ';
5129 END IF;
5130 l_attributes := l_attributes || 'VEHICLE_ORGANIZATION_ID';
5131 END IF;
5132
5133 IF p_user_in_rec.VEHICLE_NUMBER <> FND_API.G_MISS_CHAR
5134 AND NVL(p_user_in_rec.VEHICLE_NUMBER,'!!!') <> NVL(p_out_rec.VEHICLE_NUMBER,'!!!')
5135 THEN
5136 IF l_attributes IS NOT NULL THEN
5137 l_attributes := l_attributes || ', ';
5138 END IF;
5139 l_attributes := l_attributes || 'VEHICLE_NUMBER';
5140 END IF;
5141
5142 IF p_user_in_rec.VEHICLE_NUM_PREFIX <> FND_API.G_MISS_CHAR
5143 AND NVL(p_user_in_rec.VEHICLE_NUM_PREFIX,'!!!') <> NVL(p_out_rec.VEHICLE_NUM_PREFIX,'!!!')
5144 THEN
5145 IF l_attributes IS NOT NULL THEN
5146 l_attributes := l_attributes || ', ';
5147 END IF;
5148 l_attributes := l_attributes || 'VEHICLE_NUM_PREFIX';
5149 END IF;
5150
5151 IF p_user_in_rec.CARRIER_ID <> FND_API.G_MISS_NUM
5152 AND NVL(p_user_in_rec.CARRIER_ID,-99) <> NVL(p_out_rec.CARRIER_ID,-99)
5153 THEN
5154 IF l_attributes IS NOT NULL THEN
5155 l_attributes := l_attributes || ', ';
5156 END IF;
5157 l_attributes := l_attributes || 'CARRIER_ID';
5158 END IF;
5159
5160 IF p_user_in_rec.SHIP_METHOD_CODE <> FND_API.G_MISS_CHAR
5161 AND NVL(p_user_in_rec.SHIP_METHOD_CODE,'!!!') <> NVL(p_out_rec.SHIP_METHOD_CODE,'!!!')
5162 THEN
5163 IF l_attributes IS NOT NULL THEN
5164 l_attributes := l_attributes || ', ';
5165 END IF;
5166 l_attributes := l_attributes || 'SHIP_METHOD_CODE';
5167 END IF;
5168
5169 IF p_user_in_rec.ROUTE_ID <> FND_API.G_MISS_NUM
5170 AND NVL(p_user_in_rec.ROUTE_ID,-99) <> NVL(p_out_rec.ROUTE_ID,-99)
5171 THEN
5172 IF l_attributes IS NOT NULL THEN
5173 l_attributes := l_attributes || ', ';
5174 END IF;
5175 l_attributes := l_attributes || 'ROUTE_ID';
5176 END IF;
5177
5178 IF p_user_in_rec.ROUTING_INSTRUCTIONS <> FND_API.G_MISS_CHAR
5179 AND NVL(p_user_in_rec.ROUTING_INSTRUCTIONS,'!!!') <> NVL(p_out_rec.ROUTING_INSTRUCTIONS,'!!!')
5180 THEN
5181 IF l_attributes IS NOT NULL THEN
5182 l_attributes := l_attributes || ', ';
5183 END IF;
5184 l_attributes := l_attributes || 'ROUTING_INSTRUCTIONS';
5185 END IF;
5186
5187 IF p_user_in_rec.ATTRIBUTE_CATEGORY <> FND_API.G_MISS_CHAR
5188 AND NVL(p_user_in_rec.ATTRIBUTE_CATEGORY,'!!!') <> NVL(p_out_rec.ATTRIBUTE_CATEGORY,'!!!')
5189 THEN
5190 IF l_attributes IS NOT NULL THEN
5191 l_attributes := l_attributes || ', ';
5192 END IF;
5193 l_attributes := l_attributes || 'ATTRIBUTE_CATEGORY';
5194 END IF;
5195
5196 IF p_user_in_rec.ATTRIBUTE1 <> FND_API.G_MISS_CHAR
5197 AND NVL(p_user_in_rec.ATTRIBUTE1,'!!!') <> NVL(p_out_rec.ATTRIBUTE1,'!!!')
5198 THEN
5199 IF l_attributes IS NOT NULL THEN
5200 l_attributes := l_attributes || ', ';
5201 END IF;
5202 l_attributes := l_attributes || 'ATTRIBUTE1';
5203 END IF;
5204
5205 IF p_user_in_rec.ATTRIBUTE2 <> FND_API.G_MISS_CHAR
5206 AND NVL(p_user_in_rec.ATTRIBUTE2,'!!!') <> NVL(p_out_rec.ATTRIBUTE2,'!!!')
5207 THEN
5208 IF l_attributes IS NOT NULL THEN
5209 l_attributes := l_attributes || ', ';
5210 END IF;
5211 l_attributes := l_attributes || 'ATTRIBUTE2';
5212 END IF;
5213
5214 IF p_user_in_rec.ATTRIBUTE3 <> FND_API.G_MISS_CHAR
5215 AND NVL(p_user_in_rec.ATTRIBUTE3,'!!!') <> NVL(p_out_rec.ATTRIBUTE3,'!!!')
5216 THEN
5217 IF l_attributes IS NOT NULL THEN
5218 l_attributes := l_attributes || ', ';
5219 END IF;
5220 l_attributes := l_attributes || 'ATTRIBUTE3';
5221 END IF;
5222
5223 IF p_user_in_rec.ATTRIBUTE4 <> FND_API.G_MISS_CHAR
5224 AND NVL(p_user_in_rec.ATTRIBUTE4,'!!!') <> NVL(p_out_rec.ATTRIBUTE4,'!!!')
5225 THEN
5226 IF l_attributes IS NOT NULL THEN
5227 l_attributes := l_attributes || ', ';
5228 END IF;
5229 l_attributes := l_attributes || 'ATTRIBUTE4';
5230 END IF;
5231
5232 IF p_user_in_rec.ATTRIBUTE5 <> FND_API.G_MISS_CHAR
5233 AND NVL(p_user_in_rec.ATTRIBUTE5,'!!!') <> NVL(p_out_rec.ATTRIBUTE5,'!!!')
5234 THEN
5235 IF l_attributes IS NOT NULL THEN
5236 l_attributes := l_attributes || ', ';
5237 END IF;
5238 l_attributes := l_attributes || 'ATTRIBUTE5';
5239 END IF;
5240
5241 IF p_user_in_rec.ATTRIBUTE6 <> FND_API.G_MISS_CHAR
5242 AND NVL(p_user_in_rec.ATTRIBUTE6,'!!!') <> NVL(p_out_rec.ATTRIBUTE6,'!!!')
5243 THEN
5244 IF l_attributes IS NOT NULL THEN
5245 l_attributes := l_attributes || ', ';
5246 END IF;
5247 l_attributes := l_attributes || 'ATTRIBUTE6';
5248 END IF;
5249
5250 IF p_user_in_rec.ATTRIBUTE7 <> FND_API.G_MISS_CHAR
5251 AND NVL(p_user_in_rec.ATTRIBUTE7,'!!!') <> NVL(p_out_rec.ATTRIBUTE7,'!!!')
5252 THEN
5253 IF l_attributes IS NOT NULL THEN
5254 l_attributes := l_attributes || ', ';
5255 END IF;
5256 l_attributes := l_attributes || 'ATTRIBUTE7';
5257 END IF;
5258
5259 IF p_user_in_rec.ATTRIBUTE8 <> FND_API.G_MISS_CHAR
5260 AND NVL(p_user_in_rec.ATTRIBUTE8,'!!!') <> NVL(p_out_rec.ATTRIBUTE8,'!!!')
5261 THEN
5262 IF l_attributes IS NOT NULL THEN
5263 l_attributes := l_attributes || ', ';
5264 END IF;
5265 l_attributes := l_attributes || 'ATTRIBUTE8';
5266 END IF;
5267
5268 IF p_user_in_rec.ATTRIBUTE9 <> FND_API.G_MISS_CHAR
5269 AND NVL(p_user_in_rec.ATTRIBUTE9,'!!!') <> NVL(p_out_rec.ATTRIBUTE9,'!!!')
5270 THEN
5271 IF l_attributes IS NOT NULL THEN
5272 l_attributes := l_attributes || ', ';
5273 END IF;
5274 l_attributes := l_attributes || 'ATTRIBUTE9';
5275 END IF;
5276
5277 IF p_user_in_rec.ATTRIBUTE10 <> FND_API.G_MISS_CHAR
5278 AND NVL(p_user_in_rec.ATTRIBUTE10,'!!!') <> NVL(p_out_rec.ATTRIBUTE10,'!!!')
5279 THEN
5280 IF l_attributes IS NOT NULL THEN
5281 l_attributes := l_attributes || ', ';
5282 END IF;
5283 l_attributes := l_attributes || 'ATTRIBUTE10';
5284 END IF;
5285
5286 IF p_user_in_rec.ATTRIBUTE11 <> FND_API.G_MISS_CHAR
5287 AND NVL(p_user_in_rec.ATTRIBUTE11,'!!!') <> NVL(p_out_rec.ATTRIBUTE11,'!!!')
5288 THEN
5289 IF l_attributes IS NOT NULL THEN
5290 l_attributes := l_attributes || ', ';
5291 END IF;
5292 l_attributes := l_attributes || 'ATTRIBUTE11';
5293 END IF;
5294
5295 IF p_user_in_rec.ATTRIBUTE12 <> FND_API.G_MISS_CHAR
5296 AND NVL(p_user_in_rec.ATTRIBUTE12,'!!!') <> NVL(p_out_rec.ATTRIBUTE12,'!!!')
5297 THEN
5298 IF l_attributes IS NOT NULL THEN
5299 l_attributes := l_attributes || ', ';
5300 END IF;
5301 l_attributes := l_attributes || 'ATTRIBUTE12';
5302 END IF;
5303
5304 IF p_user_in_rec.ATTRIBUTE13 <> FND_API.G_MISS_CHAR
5305 AND NVL(p_user_in_rec.ATTRIBUTE13,'!!!') <> NVL(p_out_rec.ATTRIBUTE13,'!!!')
5306 THEN
5307 IF l_attributes IS NOT NULL THEN
5308 l_attributes := l_attributes || ', ';
5309 END IF;
5310 l_attributes := l_attributes || 'ATTRIBUTE13';
5311 END IF;
5312
5313 IF p_user_in_rec.ATTRIBUTE14 <> FND_API.G_MISS_CHAR
5314 AND NVL(p_user_in_rec.ATTRIBUTE14,'!!!') <> NVL(p_out_rec.ATTRIBUTE14,'!!!')
5315 THEN
5316 IF l_attributes IS NOT NULL THEN
5317 l_attributes := l_attributes || ', ';
5318 END IF;
5319 l_attributes := l_attributes || 'ATTRIBUTE14';
5320 END IF;
5321
5322 IF p_user_in_rec.ATTRIBUTE15 <> FND_API.G_MISS_CHAR
5323 AND NVL(p_user_in_rec.ATTRIBUTE15,'!!!') <> NVL(p_out_rec.ATTRIBUTE15,'!!!')
5324 THEN
5325 IF l_attributes IS NOT NULL THEN
5326 l_attributes := l_attributes || ', ';
5327 END IF;
5328 l_attributes := l_attributes || 'ATTRIBUTE15';
5329 END IF;
5330
5331 /**
5332 -- bug 3613650
5333 -- Need not compare against standard WHO columns
5334 IF p_user_in_rec.CREATION_DATE <> FND_API.G_MISS_DATE
5335 AND NVL(p_user_in_rec.CREATION_DATE,TO_DATE('2','j')) <> NVL(p_out_rec.CREATION_DATE,TO_DATE('2','j'))
5336 THEN
5337 IF l_attributes IS NOT NULL THEN
5338 l_attributes := l_attributes || ', ';
5339 END IF;
5340 l_attributes := l_attributes || 'CREATION_DATE';
5341 END IF;
5342
5343 IF p_user_in_rec.CREATED_BY <> FND_API.G_MISS_NUM
5344 AND NVL(p_user_in_rec.CREATED_BY,-99) <> NVL(p_out_rec.CREATED_BY,-99)
5345 THEN
5346 IF l_attributes IS NOT NULL THEN
5347 l_attributes := l_attributes || ', ';
5348 END IF;
5349 l_attributes := l_attributes || 'CREATED_BY';
5350 END IF;
5351
5352 IF p_user_in_rec.LAST_UPDATE_DATE <> FND_API.G_MISS_DATE
5353 AND NVL(p_user_in_rec.LAST_UPDATE_DATE,TO_DATE('2','j')) <> NVL(p_out_rec.LAST_UPDATE_DATE,TO_DATE('2','j'))
5354 THEN
5355 IF l_attributes IS NOT NULL THEN
5356 l_attributes := l_attributes || ', ';
5357 END IF;
5358 l_attributes := l_attributes || 'LAST_UPDATE_DATE';
5359 END IF;
5360
5361 IF p_user_in_rec.LAST_UPDATED_BY <> FND_API.G_MISS_NUM
5362 AND NVL(p_user_in_rec.LAST_UPDATED_BY,-99) <> NVL(p_out_rec.LAST_UPDATED_BY,-99)
5363 THEN
5364 IF l_attributes IS NOT NULL THEN
5365 l_attributes := l_attributes || ', ';
5366 END IF;
5367 l_attributes := l_attributes || 'LAST_UPDATED_BY';
5368 END IF;
5369
5370 IF p_user_in_rec.LAST_UPDATE_LOGIN <> FND_API.G_MISS_NUM
5371 AND NVL(p_user_in_rec.LAST_UPDATE_LOGIN,-99) <> NVL(p_out_rec.LAST_UPDATE_LOGIN,-99)
5372 THEN
5373 IF l_attributes IS NOT NULL THEN
5374 l_attributes := l_attributes || ', ';
5375 END IF;
5376 l_attributes := l_attributes || 'LAST_UPDATE_LOGIN';
5377 END IF;
5378
5379 IF p_user_in_rec.PROGRAM_APPLICATION_ID <> FND_API.G_MISS_NUM
5380 AND NVL(p_user_in_rec.PROGRAM_APPLICATION_ID,-99) <> NVL(p_out_rec.PROGRAM_APPLICATION_ID,-99)
5381 THEN
5382 IF l_attributes IS NOT NULL THEN
5383 l_attributes := l_attributes || ', ';
5384 END IF;
5385 l_attributes := l_attributes || 'PROGRAM_APPLICATION_ID';
5386 END IF;
5387
5388 IF p_user_in_rec.PROGRAM_ID <> FND_API.G_MISS_NUM
5389 AND NVL(p_user_in_rec.PROGRAM_ID,-99) <> NVL(p_out_rec.PROGRAM_ID,-99)
5390 THEN
5391 IF l_attributes IS NOT NULL THEN
5392 l_attributes := l_attributes || ', ';
5393 END IF;
5394 l_attributes := l_attributes || 'PROGRAM_ID';
5395 END IF;
5396
5397 IF p_user_in_rec.PROGRAM_UPDATE_DATE <> FND_API.G_MISS_DATE
5398 AND NVL(p_user_in_rec.PROGRAM_UPDATE_DATE,TO_DATE('2','j')) <> NVL(p_out_rec.PROGRAM_UPDATE_DATE,TO_DATE('2','j'))
5399 THEN
5400 IF l_attributes IS NOT NULL THEN
5401 l_attributes := l_attributes || ', ';
5402 END IF;
5403 l_attributes := l_attributes || 'PROGRAM_UPDATE_DATE';
5404 END IF;
5405
5406 IF p_user_in_rec.REQUEST_ID <> FND_API.G_MISS_NUM
5407 AND NVL(p_user_in_rec.REQUEST_ID,-99) <> NVL(p_out_rec.REQUEST_ID,-99)
5408 THEN
5409 IF l_attributes IS NOT NULL THEN
5410 l_attributes := l_attributes || ', ';
5411 END IF;
5412 l_attributes := l_attributes || 'REQUEST_ID';
5413 END IF;
5414
5415 bug 3613650 */
5416
5417 IF p_user_in_rec.SERVICE_LEVEL <> FND_API.G_MISS_CHAR
5418 AND NVL(p_user_in_rec.SERVICE_LEVEL,'!!!') <> NVL(p_out_rec.SERVICE_LEVEL,'!!!')
5419 THEN
5420 IF l_attributes IS NOT NULL THEN
5421 l_attributes := l_attributes || ', ';
5422 END IF;
5423 l_attributes := l_attributes || 'SERVICE_LEVEL';
5424 END IF;
5425
5426 IF p_user_in_rec.MODE_OF_TRANSPORT <> FND_API.G_MISS_CHAR
5427 AND NVL(p_user_in_rec.MODE_OF_TRANSPORT,'!!!') <> NVL(p_out_rec.MODE_OF_TRANSPORT,'!!!')
5428 THEN
5429 IF l_attributes IS NOT NULL THEN
5430 l_attributes := l_attributes || ', ';
5431 END IF;
5432 l_attributes := l_attributes || 'MODE_OF_TRANSPORT';
5433 END IF;
5434
5435 IF p_user_in_rec.FREIGHT_TERMS_CODE <> FND_API.G_MISS_CHAR
5436 AND NVL(p_user_in_rec.FREIGHT_TERMS_CODE,'!!!') <> NVL(p_out_rec.FREIGHT_TERMS_CODE,'!!!')
5437 THEN
5438 IF l_attributes IS NOT NULL THEN
5439 l_attributes := l_attributes || ', ';
5440 END IF;
5441 l_attributes := l_attributes || 'FREIGHT_TERMS_CODE';
5442 END IF;
5443
5444 IF p_user_in_rec.CONSOLIDATION_ALLOWED <> FND_API.G_MISS_CHAR
5445 AND NVL(p_user_in_rec.CONSOLIDATION_ALLOWED,'!!!') <> NVL(p_out_rec.CONSOLIDATION_ALLOWED,'!!!')
5446 THEN
5447 IF l_attributes IS NOT NULL THEN
5448 l_attributes := l_attributes || ', ';
5449 END IF;
5450 l_attributes := l_attributes || 'CONSOLIDATION_ALLOWED';
5451 END IF;
5452
5453 IF p_user_in_rec.LOAD_TENDER_STATUS <> FND_API.G_MISS_CHAR
5454 AND NVL(p_user_in_rec.LOAD_TENDER_STATUS,'!!!') <> NVL(p_out_rec.LOAD_TENDER_STATUS,'!!!')
5455 THEN
5456 IF l_attributes IS NOT NULL THEN
5457 l_attributes := l_attributes || ', ';
5458 END IF;
5459 l_attributes := l_attributes || 'LOAD_TENDER_STATUS';
5460 END IF;
5461
5462 IF p_user_in_rec.ROUTE_LANE_ID <> FND_API.G_MISS_NUM
5463 AND NVL(p_user_in_rec.ROUTE_LANE_ID,-99) <> NVL(p_out_rec.ROUTE_LANE_ID,-99)
5464 THEN
5465 IF l_attributes IS NOT NULL THEN
5466 l_attributes := l_attributes || ', ';
5467 END IF;
5468 l_attributes := l_attributes || 'ROUTE_LANE_ID';
5469 END IF;
5470
5471 IF p_user_in_rec.LANE_ID <> FND_API.G_MISS_NUM
5472 AND NVL(p_user_in_rec.LANE_ID,-99) <> NVL(p_out_rec.LANE_ID,-99)
5473 THEN
5474 IF l_attributes IS NOT NULL THEN
5475 l_attributes := l_attributes || ', ';
5476 END IF;
5477 l_attributes := l_attributes || 'LANE_ID';
5478 END IF;
5479
5480 IF p_user_in_rec.SCHEDULE_ID <> FND_API.G_MISS_NUM
5481 AND NVL(p_user_in_rec.SCHEDULE_ID,-99) <> NVL(p_out_rec.SCHEDULE_ID,-99)
5482 THEN
5483 IF l_attributes IS NOT NULL THEN
5484 l_attributes := l_attributes || ', ';
5485 END IF;
5486 l_attributes := l_attributes || 'SCHEDULE_ID';
5487 END IF;
5488
5489 IF p_user_in_rec.BOOKING_NUMBER <> FND_API.G_MISS_CHAR
5490 AND NVL(p_user_in_rec.BOOKING_NUMBER,'!!!') <> NVL(p_out_rec.BOOKING_NUMBER,'!!!')
5491 THEN
5492 IF l_attributes IS NOT NULL THEN
5493 l_attributes := l_attributes || ', ';
5494 END IF;
5495 l_attributes := l_attributes || 'BOOKING_NUMBER';
5496 END IF;
5497
5498 IF p_user_in_rec.ROWID <> FND_API.G_MISS_CHAR
5499 AND NVL(p_user_in_rec.ROWID,'!!!') <> NVL(p_out_rec.ROWID,'!!!')
5500 THEN
5501 IF l_attributes IS NOT NULL THEN
5502 l_attributes := l_attributes || ', ';
5503 END IF;
5504 l_attributes := l_attributes || 'ROWID';
5505 END IF;
5506
5507 IF p_user_in_rec.ARRIVE_AFTER_TRIP_NAME <> FND_API.G_MISS_CHAR
5508 AND NVL(p_user_in_rec.ARRIVE_AFTER_TRIP_NAME,'!!!') <> NVL(p_out_rec.ARRIVE_AFTER_TRIP_NAME,'!!!')
5509 THEN
5510 IF l_attributes IS NOT NULL THEN
5511 l_attributes := l_attributes || ', ';
5512 END IF;
5513 l_attributes := l_attributes || 'ARRIVE_AFTER_TRIP_NAME';
5514 END IF;
5515
5516 IF p_user_in_rec.SHIP_METHOD_NAME <> FND_API.G_MISS_CHAR
5517 AND NVL(p_user_in_rec.SHIP_METHOD_NAME,'!!!') <> NVL(p_out_rec.SHIP_METHOD_NAME,'!!!')
5518 THEN
5519 IF l_attributes IS NOT NULL THEN
5520 l_attributes := l_attributes || ', ';
5521 END IF;
5522 l_attributes := l_attributes || 'SHIP_METHOD_NAME';
5523 END IF;
5524
5525 IF p_user_in_rec.VEHICLE_ITEM_DESC <> FND_API.G_MISS_CHAR
5526 AND NVL(p_user_in_rec.VEHICLE_ITEM_DESC,'!!!') <> NVL(p_out_rec.VEHICLE_ITEM_DESC,'!!!')
5527 THEN
5528 IF l_attributes IS NOT NULL THEN
5529 l_attributes := l_attributes || ', ';
5530 END IF;
5531 l_attributes := l_attributes || 'VEHICLE_ITEM_DESC';
5532 END IF;
5533
5534 IF p_user_in_rec.VEHICLE_ORGANIZATION_CODE <> FND_API.G_MISS_CHAR
5535 AND NVL(p_user_in_rec.VEHICLE_ORGANIZATION_CODE,'!!!') <> NVL(p_out_rec.VEHICLE_ORGANIZATION_CODE,'!!!')
5536 THEN
5537 IF l_attributes IS NOT NULL THEN
5538 l_attributes := l_attributes || ', ';
5539 END IF;
5540 l_attributes := l_attributes || 'VEHICLE_ORGANIZATION_CODE';
5541 END IF;
5542
5543 IF p_user_in_rec.LOAD_TENDER_NUMBER <> FND_API.G_MISS_NUM
5544 AND NVL(p_user_in_rec.LOAD_TENDER_NUMBER,-99) <> NVL(p_out_rec.LOAD_TENDER_NUMBER,-99)
5545 THEN
5546 IF l_attributes IS NOT NULL THEN
5547 l_attributes := l_attributes || ', ';
5548 END IF;
5549 l_attributes := l_attributes || 'LOAD_TENDER_NUMBER';
5550 END IF;
5551
5552 IF p_user_in_rec.VESSEL <> FND_API.G_MISS_CHAR
5553 AND NVL(p_user_in_rec.VESSEL,'!!!') <> NVL(p_out_rec.VESSEL,'!!!')
5554 THEN
5555 IF l_attributes IS NOT NULL THEN
5556 l_attributes := l_attributes || ', ';
5557 END IF;
5558 l_attributes := l_attributes || 'VESSEL';
5559 END IF;
5560
5561 IF p_user_in_rec.VOYAGE_NUMBER <> FND_API.G_MISS_CHAR
5562 AND NVL(p_user_in_rec.VOYAGE_NUMBER,'!!!') <> NVL(p_out_rec.VOYAGE_NUMBER,'!!!')
5563 THEN
5564 IF l_attributes IS NOT NULL THEN
5565 l_attributes := l_attributes || ', ';
5566 END IF;
5567 l_attributes := l_attributes || 'VOYAGE_NUMBER';
5568 END IF;
5569
5570 IF p_user_in_rec.PORT_OF_LOADING <> FND_API.G_MISS_CHAR
5571 AND NVL(p_user_in_rec.PORT_OF_LOADING,'!!!') <> NVL(p_out_rec.PORT_OF_LOADING,'!!!')
5572 THEN
5573 IF l_attributes IS NOT NULL THEN
5574 l_attributes := l_attributes || ', ';
5575 END IF;
5576 l_attributes := l_attributes || 'PORT_OF_LOADING';
5577 END IF;
5578
5579 IF p_user_in_rec.PORT_OF_DISCHARGE <> FND_API.G_MISS_CHAR
5580 AND NVL(p_user_in_rec.PORT_OF_DISCHARGE,'!!!') <> NVL(p_out_rec.PORT_OF_DISCHARGE,'!!!')
5581 THEN
5582 IF l_attributes IS NOT NULL THEN
5583 l_attributes := l_attributes || ', ';
5584 END IF;
5585 l_attributes := l_attributes || 'PORT_OF_DISCHARGE';
5586 END IF;
5587
5588 IF p_user_in_rec.WF_NAME <> FND_API.G_MISS_CHAR
5589 AND NVL(p_user_in_rec.WF_NAME,'!!!') <> NVL(p_out_rec.WF_NAME,'!!!')
5590 THEN
5591 IF l_attributes IS NOT NULL THEN
5592 l_attributes := l_attributes || ', ';
5593 END IF;
5594 l_attributes := l_attributes || 'WF_NAME';
5595 END IF;
5596
5597 IF p_user_in_rec.WF_PROCESS_NAME <> FND_API.G_MISS_CHAR
5598 AND NVL(p_user_in_rec.WF_PROCESS_NAME,'!!!') <> NVL(p_out_rec.WF_PROCESS_NAME,'!!!')
5599 THEN
5600 IF l_attributes IS NOT NULL THEN
5601 l_attributes := l_attributes || ', ';
5602 END IF;
5603 l_attributes := l_attributes || 'WF_PROCESS_NAME';
5604 END IF;
5605
5606 IF p_user_in_rec.WF_ITEM_KEY <> FND_API.G_MISS_CHAR
5607 AND NVL(p_user_in_rec.WF_ITEM_KEY,'!!!') <> NVL(p_out_rec.WF_ITEM_KEY,'!!!')
5608 THEN
5609 IF l_attributes IS NOT NULL THEN
5610 l_attributes := l_attributes || ', ';
5611 END IF;
5612 l_attributes := l_attributes || 'WF_ITEM_KEY';
5613 END IF;
5614
5615 IF p_user_in_rec.CARRIER_CONTACT_ID <> FND_API.G_MISS_NUM
5616 AND NVL(p_user_in_rec.CARRIER_CONTACT_ID,-99) <> NVL(p_out_rec.CARRIER_CONTACT_ID,-99)
5617 THEN
5618 IF l_attributes IS NOT NULL THEN
5619 l_attributes := l_attributes || ', ';
5620 END IF;
5621 l_attributes := l_attributes || 'CARRIER_CONTACT_ID';
5622 END IF;
5623
5624 IF p_user_in_rec.SHIPPER_WAIT_TIME <> FND_API.G_MISS_NUM
5625 AND NVL(p_user_in_rec.SHIPPER_WAIT_TIME,-99) <> NVL(p_out_rec.SHIPPER_WAIT_TIME,-99)
5626 THEN
5627 IF l_attributes IS NOT NULL THEN
5628 l_attributes := l_attributes || ', ';
5629 END IF;
5630 l_attributes := l_attributes || 'SHIPPER_WAIT_TIME';
5631 END IF;
5632
5633 IF p_user_in_rec.WAIT_TIME_UOM <> FND_API.G_MISS_CHAR
5634 AND NVL(p_user_in_rec.WAIT_TIME_UOM,'!!!') <> NVL(p_out_rec.WAIT_TIME_UOM,'!!!')
5635 THEN
5636 IF l_attributes IS NOT NULL THEN
5637 l_attributes := l_attributes || ', ';
5638 END IF;
5639 l_attributes := l_attributes || 'WAIT_TIME_UOM';
5640 END IF;
5641
5642 IF p_user_in_rec.LOAD_TENDERED_TIME <> FND_API.G_MISS_DATE
5643 AND NVL(p_user_in_rec.LOAD_TENDERED_TIME,TO_DATE('2','j')) <> NVL(p_out_rec.LOAD_TENDERED_TIME,TO_DATE('2','j'))
5644 THEN
5645 IF l_attributes IS NOT NULL THEN
5646 l_attributes := l_attributes || ', ';
5647 END IF;
5648 l_attributes := l_attributes || 'LOAD_TENDERED_TIME';
5649 END IF;
5650
5651 IF p_user_in_rec.CARRIER_RESPONSE <> FND_API.G_MISS_CHAR
5652 AND NVL(p_user_in_rec.CARRIER_RESPONSE,'!!!') <> NVL(p_out_rec.CARRIER_RESPONSE,'!!!')
5653 THEN
5654 IF l_attributes IS NOT NULL THEN
5655 l_attributes := l_attributes || ', ';
5656 END IF;
5657 l_attributes := l_attributes || 'CARRIER_RESPONSE';
5658 END IF;
5659
5660 IF p_user_in_rec.SHIPMENTS_TYPE_FLAG <> FND_API.G_MISS_CHAR
5661 AND NVL(p_user_in_rec.SHIPMENTS_TYPE_FLAG,'!!!') <> NVL(p_out_rec.SHIPMENTS_TYPE_FLAG,'!!!')
5662 THEN
5663 IF l_attributes IS NOT NULL THEN
5664 l_attributes := l_attributes || ', ';
5665 END IF;
5666 l_attributes := l_attributes || 'SHIPMENTS_TYPE_FLAG';
5667 END IF;
5668
5669 IF p_user_in_rec.IGNORE_FOR_PLANNING <> FND_API.G_MISS_CHAR
5670 AND NVL(p_user_in_rec.IGNORE_FOR_PLANNING,'!!!') <> NVL(p_out_rec.IGNORE_FOR_PLANNING,'!!!')
5671 THEN
5672 IF l_attributes IS NOT NULL THEN
5673 l_attributes := l_attributes || ', ';
5674 END IF;
5675 l_attributes := l_attributes || 'IGNORE_FOR_PLANNING';
5676 END IF;
5677
5678 IF p_user_in_rec.TP_PLAN_NAME <> FND_API.G_MISS_CHAR
5679 AND NVL(p_user_in_rec.TP_PLAN_NAME,'!!!') <> NVL(p_out_rec.TP_PLAN_NAME,'!!!')
5680 THEN
5681 IF l_attributes IS NOT NULL THEN
5682 l_attributes := l_attributes || ', ';
5683 END IF;
5684 l_attributes := l_attributes || 'TP_PLAN_NAME';
5685 END IF;
5686
5687 IF p_user_in_rec.TP_TRIP_NUMBER <> FND_API.G_MISS_NUM
5688 AND NVL(p_user_in_rec.TP_TRIP_NUMBER,-99) <> NVL(p_out_rec.TP_TRIP_NUMBER,-99)
5689 THEN
5690 IF l_attributes IS NOT NULL THEN
5691 l_attributes := l_attributes || ', ';
5692 END IF;
5693 l_attributes := l_attributes || 'TP_TRIP_NUMBER';
5694 END IF;
5695
5696 IF p_user_in_rec.SEAL_CODE <> FND_API.G_MISS_CHAR
5697 AND NVL(p_user_in_rec.SEAL_CODE,'!!!') <> NVL(p_out_rec.SEAL_CODE,'!!!')
5698 THEN
5699 IF l_attributes IS NOT NULL THEN
5700 l_attributes := l_attributes || ', ';
5701 END IF;
5702 l_attributes := l_attributes || 'SEAL_CODE';
5703 END IF;
5704
5705 IF p_user_in_rec.OPERATOR <> FND_API.G_MISS_CHAR
5706 AND NVL(p_user_in_rec.OPERATOR,'!!!') <> NVL(p_out_rec.OPERATOR,'!!!')
5707 THEN
5708 IF l_attributes IS NOT NULL THEN
5709 l_attributes := l_attributes || ', ';
5710 END IF;
5711 l_attributes := l_attributes || 'OPERATOR';
5712 END IF;
5713
5714
5715 IF p_user_in_rec.CONSIGNEE_CARRIER_AC_NO <> FND_API.G_MISS_CHAR
5716 AND NVL(p_user_in_rec.CONSIGNEE_CARRIER_AC_NO,'!!!') <> NVL(p_out_rec.CONSIGNEE_CARRIER_AC_NO,'!!!')
5717 THEN
5718 IF l_attributes IS NOT NULL THEN
5719 l_attributes := l_attributes || ', ';
5720 END IF;
5721 l_attributes := l_attributes || 'CONSIGNEE_CARRIER_AC_NO';
5722 END IF;
5723
5724 IF p_user_in_rec.CARRIER_REFERENCE_NUMBER <> FND_API.G_MISS_CHAR
5725 AND NVL(p_user_in_rec.CARRIER_REFERENCE_NUMBER,'!!!') <> NVL(p_out_rec.CARRIER_REFERENCE_NUMBER,'!!!')
5726 THEN
5727 IF l_attributes IS NOT NULL THEN
5728 l_attributes := l_attributes || ', ';
5729 END IF;
5730 l_attributes := l_attributes || 'CARRIER_REFERENCE_NUMBER';
5731 END IF;
5732
5733 IF p_user_in_rec.APPEND_FLAG <> FND_API.G_MISS_CHAR
5734 AND NVL(p_user_in_rec.APPEND_FLAG,'!!!') <> NVL(p_out_rec.APPEND_FLAG,'!!!')
5735 THEN
5736 IF l_attributes IS NOT NULL THEN
5737 l_attributes := l_attributes || ', ';
5738 END IF;
5739 l_attributes := l_attributes || 'APPEND_FLAG';
5740 END IF;
5741
5742
5743 IF p_user_in_rec.RANK_ID <> FND_API.G_MISS_NUM
5744 AND NVL(p_user_in_rec.RANK_ID, -99) <> NVL(p_out_rec.RANK_ID,-99)
5745 THEN
5746 IF l_attributes IS NOT NULL THEN
5747 l_attributes := l_attributes || ', ';
5748 END IF;
5749 l_attributes := l_attributes || 'RANK_ID';
5750 END IF;
5751
5752 IF p_user_in_rec.ROUTING_RULE_ID <> FND_API.G_MISS_NUM
5753 AND NVL(p_user_in_rec.ROUTING_RULE_ID, -99) <> NVL(p_out_rec.ROUTING_RULE_ID,-99)
5754 THEN
5755 IF l_attributes IS NOT NULL THEN
5756 l_attributes := l_attributes || ', ';
5757 END IF;
5758 l_attributes := l_attributes || 'ROUTING_RULE_ID';
5759 END IF;
5760
5761
5762 IF l_debug_on THEN
5763 WSH_DEBUG_SV.log(l_module_name,'l_attributes',l_attributes);
5764 WSH_DEBUG_SV.log(l_module_name,'length(l_attributes)',length(l_attributes));
5765 END IF;
5766
5767
5768 IF l_attributes IS NULL THEN
5769 --no message to be shown to the user
5770 IF l_debug_on THEN
5771 WSH_DEBUG_SV.pop(l_module_name);
5772 END IF;
5773 RETURN;
5774 --
5775 ELSE
5776 Wsh_Utilities.process_message(
5777 p_entity => 'TRIP',
5778 p_entity_name => NVL(p_out_rec.NAME,p_out_rec.TRIP_ID),
5779 p_attributes => l_attributes,
5780 x_return_status => l_return_status
5781 );
5782
5783 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
5784 THEN
5785 x_return_status := l_return_status;
5786 IF l_debug_on THEN
5787 wsh_debug_sv.logmsg(l_module_name,'Error returned by wsh_utilities.process_message',WSH_DEBUG_SV.C_PROC_LEVEL);
5788 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5789 wsh_debug_sv.pop(l_module_name);
5790 END IF;
5791 return;
5792 ELSE
5793 x_return_status := wsh_util_core.G_RET_STS_WARNING;
5794 END IF;
5795 END IF;
5796
5797
5798
5799 IF l_debug_on THEN
5800 WSH_DEBUG_SV.pop(l_module_name);
5801 END IF;
5802 --
5803
5804
5805 EXCEPTION
5806 WHEN OTHERS THEN
5807 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
5808 --
5809 IF l_debug_on THEN
5810 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5811 END IF;
5812 --
5813 END user_non_updatable_columns;
5814
5815 -- bug 3516052
5816 -- -----------------------------------------------------------------------
5817 -- Name
5818 -- PROCEDURE Validate_Stop_Dates
5819 -- Purpose
5820 -- This procedure is to validate the planned dates of the stops in the trip.
5821 -- It makes sure that the planned departure date of a stop precedes the
5822 -- planned arrival date of the next stop. It should be called after
5823 -- the date changes have been applied to database.
5824 -- Bug 3782135
5825 -- Planned Arrival Dates across stops should be in Order, in sync
5826 -- with Stop Sequence Number(Inbound - Mixed trips, Outbound)
5827 --
5828 -- Input Parameters:
5829 -- p_trip_id : The trip to be validated
5830 --
5831 --
5832 -- Output Parameters:
5833 -- x_return_status - Success, Warning, Error, Unexpected Error
5834 -- ----------------------------------------------------------------------
5835
5836 PROCEDURE Validate_Stop_Dates
5837 ( p_trip_id IN NUMBER,
5838 x_return_status OUT NOCOPY VARCHAR2,
5839 --tkt
5840 p_caller IN VARCHAR2
5841 )
5842 IS
5843
5844 -- Bug 4036204: cur.planned_departure_date =< nxt.planned_arrival_date is OK
5845 -- as long as cur.planned_arrival_date < nxt.planned_arrival_date
5846 CURSOR get_invalid_planned_dates IS
5847 SELECT cur.stop_location_id cur_location_id,
5848 cur.physical_stop_id,
5849 cur.planned_arrival_date cur_arr_date,
5850 cur.planned_departure_date cur_dep_date,
5851 nxt.stop_location_id nxt_location_id,
5852 nxt.planned_arrival_date nxt_arr_date
5853 FROM wsh_trip_stops cur, wsh_trip_stops nxt
5854 WHERE cur.trip_id = p_trip_id
5855 AND cur.trip_id = nxt.trip_id
5856 AND cur.stop_id <> nxt.stop_id
5857 AND (( cur.planned_departure_date > nxt.planned_arrival_date
5858 AND cur.planned_arrival_date < nxt.planned_arrival_date
5859 AND cur.status_code = 'OP'
5860 AND nxt.status_code = 'OP')
5861 OR ( cur.planned_arrival_date = nxt.planned_arrival_date
5862 AND cur.status_code = 'OP'
5863 AND nxt.status_code = 'OP')
5864 OR ( cur.physical_stop_id = nxt.stop_id
5865 AND cur.stop_sequence_number + 1 <> nxt.stop_sequence_number
5866 ))
5867 AND rownum = 1;
5868
5869 l_invalid_planned_date get_invalid_planned_dates%ROWTYPE;
5870
5871 -- Bug 3782135, try to find a stop with invalid planned arrival date
5872 -- This cursor validates the planned arrival dates across stops
5873 -- within a trip (valid for inbound mixed trips as well as outbound)
5874 -- Compare the open stops with closed/arrived stops
5875 -- on same trip
5876 -- where open stop's sequence number > close/arrived stop's seq. number
5877 -- and open stops's pl. arr. date <= closed/arrived stops' pl arr date
5878 -- Select statement below makes sure no open stop has a planned arrival
5879 -- date which is earlier than a closed/arrived stop's planned arrival date
5880 -- Check for upper limit is in WSHSTTHB.pls - get_disabled_list
5881 -- and WSHSTACB.pls - reset_stop_seq_numbers
5882 -- Outbound Scenario, select statement
5883 -- Stop id Sequence Status
5884 -- 10 1 CL
5885 -- 20 2 AR
5886 -- 30 3 OP
5887 -- Inbound, Mixed Trip scenario, Handled in WSHSTTHB.pls - get_disabled_list
5888 -- Stop id Sequence Status
5889 -- 10 1 CL
5890 -- 20 2 OP
5891 -- 30 3 CL
5892 -- 40 4 OP
5893 -- 50 5 OP
5894 -- 60 6 CL
5895 CURSOR get_invalid_plarrival_dates IS
5896 SELECT cur.planned_arrival_date cur_arr_date,
5897 cur.stop_location_id cur_location_id,
5898 cur.stop_id cur_stop_id,
5899 cur.stop_sequence_number cur_stop_seq_num,
5900 prv.planned_arrival_date prv_arr_date
5901 FROM wsh_trip_stops cur,
5902 wsh_trip_stops prv
5903 WHERE cur.trip_id = p_trip_id
5904 AND cur.trip_id = prv.trip_id
5905 AND cur.stop_id <> prv.stop_id
5906 AND cur.planned_arrival_date <= prv.planned_arrival_date
5907 AND cur.status_code = 'OP'
5908 AND prv.status_code IN ('AR','CL')
5909 AND cur.stop_sequence_number > prv.stop_sequence_number
5910 AND rownum = 1;
5911
5912 l_invalid_plarrival_date get_invalid_plarrival_dates%ROWTYPE;
5913 l_found BOOLEAN;
5914 -- End of Bug 3782135
5915
5916 CURSOR get_deliveries_to_unassign IS
5917 SELECT leg.delivery_id
5918 FROM wsh_delivery_legs leg,
5919 wsh_trip_stops pickup,
5920 wsh_trip_stops dropoff
5921 WHERE pickup.trip_id = p_trip_id
5922 AND pickup.trip_id = dropoff.trip_id
5923 AND pickup.stop_id <> dropoff.stop_id
5924 AND pickup.status_code = 'OP'
5925 AND dropoff.status_code = 'OP'
5926 AND leg.pick_up_stop_id = pickup.stop_id
5927 AND leg.drop_off_stop_id = dropoff.stop_id
5928 AND dropoff.planned_arrival_date <= pickup.planned_arrival_date;
5929
5930 l_num_warn NUMBER;
5931 l_num_warn_total NUMBER;
5932 l_debug_on BOOLEAN;
5933 l_del_to_unassign WSH_UTIL_CORE.Id_Tab_Type;
5934 l_return_status VARCHAR2(1);
5935 --
5936 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Validate_Stop_Dates';
5937 invalid_input EXCEPTION;
5938 invalid_stop_date EXCEPTION;
5939 invalid_arrival_date EXCEPTION;
5940 i NUMBER;
5941 l_batchsize NUMBER;
5942
5943 BEGIN
5944
5945 -- initialize variables
5946 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5947 l_del_to_unassign.delete;
5948 l_num_warn := 0;
5949 l_batchsize := 1000;
5950 l_num_warn_total := 0;
5951 --
5952 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5953 --
5954
5955 IF l_debug_on IS NULL THEN
5956 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5957 END IF;
5958
5959 --
5960 IF l_debug_on THEN
5961 WSH_DEBUG_SV.push(l_module_name);
5962 WSH_DEBUG_SV.log(l_module_name,'Trip ID ', p_trip_id);
5963 END IF;
5964
5965 -- SSN Change
5966 -- Date validations are applicable only for Profile option = PAD and not if it is set to SSN
5967 IF WSH_TRIPS_ACTIONS.GET_STOP_SEQ_MODE = WSH_INTERFACE_GRP.G_STOP_SEQ_MODE_SSN THEN
5968 IF l_debug_on THEN
5969 WSH_DEBUG_SV.logmsg(l_module_name,'Stop Sequence Mode is SSN');
5970 WSH_DEBUG_SV.pop(l_module_name);
5971 END IF;
5972 RETURN; -- these validations are not required for profile option of SSN
5973 END IF;
5974
5975 IF p_trip_id is NULL THEN
5976 raise invalid_input;
5977 END IF;
5978
5979
5980 OPEN get_invalid_planned_dates;
5981 FETCH get_invalid_planned_dates INTO l_invalid_planned_date;
5982
5983 IF get_invalid_planned_dates%FOUND THEN
5984 raise invalid_stop_date;
5985 END IF;
5986 CLOSE get_invalid_planned_dates;
5987
5988 -- Bug 3782135
5989 IF l_debug_on THEN
5990 WSH_DEBUG_SV.log(l_module_name,'Before Validating Arrival Dates',p_trip_id);
5991 END IF;
5992
5993 OPEN get_invalid_plarrival_dates;
5994 FETCH get_invalid_plarrival_dates
5995 INTO l_invalid_plarrival_date;
5996 l_found := get_invalid_plarrival_dates%FOUND;
5997 CLOSE get_invalid_plarrival_dates;
5998 IF l_debug_on THEN
5999 WSH_DEBUG_SV.log(l_module_name,'After Validating Arrival Dates',p_trip_id);
6000 END IF;
6001
6002 IF l_found THEN
6003 IF l_debug_on THEN
6004 WSH_DEBUG_SV.log(l_module_name,'Stop ID',l_invalid_plarrival_date.cur_stop_id);
6005 WSH_DEBUG_SV.log(l_module_name,'Seq',l_invalid_plarrival_date.cur_stop_seq_num);
6006 END IF;
6007 raise invalid_arrival_date;
6008 END IF;
6009 -- End of Bug 3782135
6010
6011 -- bulk collection
6012
6013 OPEN get_deliveries_to_unassign;
6014 LOOP
6015 l_num_warn := 0;
6016
6017 FETCH get_deliveries_to_unassign BULK COLLECT INTO l_del_to_unassign LIMIT l_batchsize ;
6018 IF l_del_to_unassign.count > 0 THEN
6019
6020 i := l_del_to_unassign.first;
6021 WHILE i is not NULL LOOP
6022
6023 IF l_debug_on THEN
6024 WSH_DEBUG_SV.logmsg(l_module_name,'unassign delivery '|| l_del_to_unassign(i));
6025 END IF;
6026
6027 FND_MESSAGE.SET_NAME('WSH','WSH_DEL_UNASSIGN_TRIP');
6028 FND_MESSAGE.SET_TOKEN('DELIVERY_NAME',
6029 WSH_NEW_DELIVERIES_PVT.get_name(l_del_to_unassign(i)));
6030 FND_MESSAGE.SET_TOKEN('TRIP_NAME',
6031 WSH_TRIPS_PVT.get_name(p_trip_id));
6032 l_num_warn := l_num_warn + 1;
6033 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING);
6034
6035 i := l_del_to_unassign.next(i);
6036 END LOOP;
6037
6038 IF l_debug_on THEN
6039 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_ACTIONS.UNASSIGN_TRIP',WSH_DEBUG_SV.C_PROC_LEVEL);
6040 END IF;
6041
6042 WSH_TRIPS_ACTIONS.unassign_trip
6043 (p_del_rows => l_del_to_unassign,
6044 p_trip_id => p_trip_id,
6045 x_return_status => l_return_status);
6046
6047 IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
6048 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6049 IF l_debug_on THEN
6050 WSH_DEBUG_SV.pop(l_module_name);
6051 END IF;
6052 return;
6053 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
6054 l_num_warn := l_num_warn + 1;
6055 END IF;
6056
6057 l_num_warn_total := l_num_warn_total + 1;
6058 l_del_to_unassign.delete;
6059
6060 END IF;
6061
6062 EXIT WHEN get_deliveries_to_unassign%NOTFOUND;
6063 END LOOP;
6064 CLOSE get_deliveries_to_unassign;
6065
6066 -- bulk collection
6067
6068 IF l_num_warn_total > 0 THEN
6069 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
6070 END IF;
6071
6072 IF l_debug_on THEN
6073 WSH_DEBUG_SV.pop(l_module_name);
6074 END IF;
6075
6076 EXCEPTION
6077
6078 WHEN invalid_input THEN
6079 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6080 FND_MESSAGE.SET_NAME('WSH', 'WSH_TRIP_ID_REQUIRED');
6081 wsh_util_core.add_message(x_return_status, l_module_name);
6082 IF l_debug_on THEN
6083 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR: Trip ID is NULL');
6084 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_input');
6085 END IF;
6086
6087 WHEN invalid_stop_date THEN
6088 IF get_invalid_planned_dates%ISOPEN THEN
6089 close get_invalid_planned_dates;
6090 END IF;
6091 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6092 IF l_invalid_planned_date.physical_stop_id is NULL THEN
6093 FND_MESSAGE.SET_NAME('WSH', 'WSH_STOP_DATE_OVERLAP');
6094 FND_MESSAGE.SET_TOKEN('CURRENT_STOP_DATE', fnd_date.date_to_displaydt(l_invalid_planned_date.cur_dep_date,calendar_aware=>FND_DATE.calendar_aware_alt));
6095 FND_MESSAGE.SET_TOKEN('CURRENT_LOCATION_DESP',
6096 WSH_UTIL_CORE.Get_Location_Description (l_invalid_planned_date.cur_location_id,'NEW UI CODE INFO'));
6097 FND_MESSAGE.SET_TOKEN('NEXT_STOP_DATE', fnd_date.date_to_displaydt(l_invalid_planned_date.nxt_arr_date,calendar_aware=>FND_DATE.calendar_aware_alt));
6098 ELSE
6099 -- No stop is allowed between dummy stop and physical stop.
6100 FND_MESSAGE.SET_NAME('WSH', 'WSH_BETWEEN_LINKED_STOPS');
6101 FND_MESSAGE.SET_TOKEN('DUMMY_STOP_DATE', fnd_date.date_to_displaydt(l_invalid_planned_date.cur_arr_date,calendar_aware=>FND_DATE.calendar_aware_alt));
6102 FND_MESSAGE.SET_TOKEN('DUMMY_LOCATION_DESP',
6103 WSH_UTIL_CORE.Get_Location_Description (l_invalid_planned_date.cur_location_id,'NEW UI CODE INFO'));
6104 FND_MESSAGE.SET_TOKEN('PHYSICAL_STOP_DATE', fnd_date.date_to_displaydt(l_invalid_planned_date.nxt_arr_date,calendar_aware=>FND_DATE.calendar_aware_alt));
6105 FND_MESSAGE.SET_TOKEN('PHYSICAL_LOCATION_DESP',
6106 WSH_UTIL_CORE.Get_Location_Description (l_invalid_planned_date.nxt_location_id,'NEW UI CODE INFO'));
6107 END IF;
6108 wsh_util_core.add_message(x_return_status, l_module_name);
6109
6110 IF l_debug_on THEN
6111 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_stop_date');
6112 END IF;
6113
6114 -- Bug 3782135
6115 WHEN invalid_arrival_date THEN
6116 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6117
6118 FND_MESSAGE.SET_NAME('WSH', 'WSH_INVALID_STOP_DATE');
6119 FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(l_invalid_plarrival_date.cur_stop_id,p_caller));
6120 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
6121
6122 IF l_debug_on THEN
6123 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_planned_arrival_date');
6124 END IF;
6125
6126 -- End of Bug 3782135
6127 WHEN Others THEN
6128 IF get_invalid_planned_dates%ISOPEN THEN
6129 close get_invalid_planned_dates;
6130 END IF;
6131 -- Bug 3782135
6132 IF get_invalid_plarrival_dates%ISOPEN THEN
6133 close get_invalid_plarrival_dates;
6134 END IF;
6135 -- End of Bug 3782135
6136
6137 IF get_deliveries_to_unassign%ISOPEN THEN
6138 close get_deliveries_to_unassign;
6139 END IF;
6140 wsh_util_core.default_handler('WSH_TRIP_STOPS_VALIDATIONS.Validate_Stop_Dates',l_module_name);
6141 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6142 IF l_debug_on THEN
6143 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
6144 END IF;
6145
6146 END Validate_Stop_Dates;
6147
6148 END WSH_TRIP_VALIDATIONS;
[Home] [Help]