1 PACKAGE BODY WSH_TRIP_STOPS_VALIDATIONS as
2 /* $Header: WSHSTVLB.pls 120.8.12010000.2 2009/12/03 13:06:24 mvudugul ship $ */
3
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_TRIP_STOPS_VALIDATIONS';
6
7 --3509004 :public api changes
8 PROCEDURE user_non_updatable_columns
9 (p_user_in_rec IN WSH_TRIP_STOPS_PVT.trip_stop_rec_type,
10 p_out_rec IN WSH_TRIP_STOPS_PVT.trip_stop_rec_type,
11 p_in_rec IN WSH_TRIP_STOPS_GRP.stopInRecType,
12 x_return_status OUT NOCOPY VARCHAR2);
13
14
15 /*
16 For every New stop entered or Updated
17 1. Check if stop sequence number is positive integer
18 2. Check if stop status is OPEN for update,OPEN for insert as well
19 3. Check if the new planned arrival date is greater than the planned arrival date of
20 arrived or closed stop
21 FP Bug 425334,per bug 4245339, validation of uniqueness is deferred to handle_internal_stops in WSHTRACB.pls.
22 */
23 -- Stop id is there as of now,but no validation,in case required later
24 PROCEDURE validate_sequence_number
25 (p_stop_id IN NUMBER,
26 p_stop_sequence_number IN NUMBER,
27 p_trip_id IN NUMBER,
28 p_status_code IN VARCHAR2,
29 x_return_status OUT NOCOPY VARCHAR2
30 )
31 IS
32
33 CURSOR c_lock_trip IS
34 SELECT trip_id
35 FROM wsh_trips
36 WHERE trip_id = p_trip_id
37 FOR UPDATE NOWAIT;
38
39
40 l_return_status VARCHAR2(30);
41 l_trip_id NUMBER;
42 l_del_to_unassign WSH_UTIL_CORE.id_tab_type;
43
44 RECORD_LOCKED EXCEPTION;
45 PRAGMA EXCEPTION_INIT(RECORD_LOCKED, -54);
46
47
48 --
49 l_debug_on BOOLEAN;
50 --
51 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_SEQUENCE_NUMBER';
52 --
53 BEGIN
54 --
55 -- Debug Statements
56 --
57 --
58 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
59 --
60 IF l_debug_on IS NULL
61 THEN
62 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
63 END IF;
64 --
65 IF l_debug_on THEN
66 WSH_DEBUG_SV.push(l_module_name);
67 --
68 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
69 WSH_DEBUG_SV.log(l_module_name,'P_STOP_SEQUENCE_NUMBER',P_STOP_SEQUENCE_NUMBER);
70 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
71 WSH_DEBUG_SV.log(l_module_name,'P_STATUS_CODE',P_STATUS_CODE);
72 END IF;
73 --
74 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
75
76 OPEN c_lock_trip;
77 FETCH c_lock_trip
78 INTO l_trip_id;
79 CLOSE c_lock_trip;
80
81 IF p_stop_sequence_number IS NULL THEN
82 -- Harmonization Project I heali
83 --
84 -- Debug Statements
85 --
86 IF l_debug_on THEN
87 WSH_DEBUG_SV.pop(l_module_name);
88 END IF;
89 --
90 RETURN;
91 END IF;
92
93 -- Check if sequence number is positive
94 check_for_negative_number(
95 p_stop_sequence_number => p_stop_sequence_number,
96 x_return_status => l_return_status);
97 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
98 x_return_status := l_return_status;
99 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_STOP_SEQUENCE');
100 wsh_util_core.add_message(x_return_status);
101 --
102 -- Debug Statements
103 --
104 IF l_debug_on THEN
105 WSH_DEBUG_SV.pop(l_module_name);
106 END IF;
107 --
108 RETURN;
109 END IF;
110
111 -- Check if stop status is Open
112 -- For new records pass this as OP as well
113 validate_stop_status(
114 p_stop_status => p_status_code,
115 x_return_status => l_return_status);
116 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
117 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_INVALID_STATUS');
118 x_return_status := l_return_status;
119 wsh_util_core.add_message(x_return_status);
120 --
121 -- Debug Statements
122 --
123 IF l_debug_on THEN
124 WSH_DEBUG_SV.pop(l_module_name);
125 END IF;
126 --
127 RETURN;
128 END IF;
129
130 -- validate for sequence number of closed stop,the new sequencenumber should
131 -- be greater than stop sequence number of closed stop
132 validate_closed_stop_seq
133 (p_trip_id => p_trip_id,
134 p_stop_sequence_number => p_stop_sequence_number,
135 x_return_status => l_return_status);
136 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
137 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_SEQUENCE_LOWER');
138 x_return_status := l_return_status;
139 wsh_util_core.add_message(x_return_status);
140 --
141 -- Debug Statements
142 --
143 IF l_debug_on THEN
144 WSH_DEBUG_SV.pop(l_module_name);
145 END IF;
146 --
147 RETURN;
148 END IF;
149 --
150 -- Check if update of Sequence Number is going to unassign a delivery from
151 -- the trip
152 -- Delivery D1 - Stop S1(10 as pickup) and Stop S2(25 as dropoff) on Trip T1
153 -- Example, updating of stop sequence from 10 to 50 would invalidate this
154 -- delivery on the trip.
155
156 -- This check is only for update
157 IF p_stop_id IS NOT NULL THEN
158 valid_delivery_on_trip
159 (p_stop_id => p_stop_id,
160 p_trip_id => p_trip_id,
161 p_stop_sequence_number => p_stop_sequence_number,
162 x_del_to_unassign => l_del_to_unassign,
163 x_return_status => l_return_status);
164 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
165 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
166 x_return_status := l_return_status;
167 ELSE
168 x_return_status := l_return_status;
169 --
170 -- Debug Statements
171 --
172 IF l_debug_on THEN
173 WSH_DEBUG_SV.pop(l_module_name);
174 END IF;
175 --
176 RETURN;
177 END IF;
178 END IF; --If return status is not success from API
179 END IF; -- if p_stop_id is not null
180
181 --
182 -- Debug Statements
183 --
184 IF l_debug_on THEN
185 WSH_DEBUG_SV.pop(l_module_name);
186 END IF;
187 --
188 EXCEPTION
189 WHEN RECORD_LOCKED THEN
190 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
191 FND_MESSAGE.Set_Name('WSH', 'WSH_NO_LOCK');
192 WSH_UTIL_CORE.add_message (x_return_status);
193 --
194 -- Debug Statements
195 --
196 IF l_debug_on THEN
197 WSH_DEBUG_SV.pop(l_module_name);
198 END IF;
199 --
200 RETURN;
201
202 --
203 -- Debug Statements
204 --
205 IF l_debug_on THEN
206 WSH_DEBUG_SV.logmsg(l_module_name,'RECORD_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
207 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:RECORD_LOCKED');
208 END IF;
209 --
210 WHEN OTHERS THEN
211 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_TRIP_STOP_VALIDATIONS.validate_sequence_number');
212 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
213 --
214 -- Debug Statements
215 --
216 IF l_debug_on THEN
217 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
218 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
219 END IF;
220 --
221 END validate_sequence_number;
222
223
224 PROCEDURE validate_closed_stop_seq
225 (p_trip_id IN NUMBER,
226 p_stop_sequence_number IN NUMBER,
227 x_return_status OUT NOCOPY VARCHAR2) IS
228
229 -- nvl is if no records exist
230 CURSOR c_check_max IS
231 SELECT nvl(max(stop_sequence_number),0) stop_sequence_number
232 FROM wsh_trip_stops
233 WHERE trip_id = p_trip_id
234 AND status_code IN ('AR','CL');
235
236 l_max_sequence NUMBER;
237
238 --
239 l_debug_on BOOLEAN;
240 --
241 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_CLOSED_STOP_SEQ';
242 --
243 BEGIN
244
245 --
246 -- Debug Statements
247 --
248 --
249 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
250 --
251 IF l_debug_on IS NULL
252 THEN
253 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
254 END IF;
255 --
256 IF l_debug_on THEN
257 WSH_DEBUG_SV.push(l_module_name);
258 --
259 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
260 WSH_DEBUG_SV.log(l_module_name,'P_STOP_SEQUENCE_NUMBER',P_STOP_SEQUENCE_NUMBER);
261 END IF;
262 --
263 OPEN c_check_max;
264 FETCH c_check_max
265 INTO l_max_sequence;
266
267 IF (c_check_max%NOTFOUND or l_max_sequence = 0) THEN
268 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
269 ELSIF (l_max_sequence > p_stop_sequence_number) THEN
270 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
271 END IF;
272
273 CLOSE c_check_max;
274 --
275 -- Debug Statements
276 --
277 IF l_debug_on THEN
278 WSH_DEBUG_SV.pop(l_module_name);
279 END IF;
280 --
281 EXCEPTION
282 WHEN OTHERS THEN
283 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_TRIP_STOP_VALIDATIONS.validate_closed_stop_seq');
284 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
285 --
286 -- Debug Statements
287 --
288 IF l_debug_on THEN
289 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
290 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
291 END IF;
292 --
293 END;
294 /* p_stop id will be null for new records
295 for update it will be populated
296 */
297 PROCEDURE validate_unique_sequence
298 (p_trip_id IN NUMBER,
299 p_stop_id IN NUMBER,
300 p_stop_sequence_number IN NUMBER,
301 x_return_status OUT NOCOPY VARCHAR2) IS
302
303 CURSOR c_check_unique IS
304 SELECT stop_id
305 FROM wsh_trip_stops
306 WHERE trip_id = p_trip_id
307 AND stop_sequence_number = p_stop_sequence_number
308 AND rownum = 1 ;
309
310 l_check_flag VARCHAR2(1) := 'N';
311 l_stop_id NUMBER;
312
313 --
314 l_debug_on BOOLEAN;
315 --
316 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_UNIQUE_SEQUENCE';
317 --
318 BEGIN
319
320 --
321 -- Debug Statements
322 --
323 --
324 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
325 --
326 IF l_debug_on IS NULL
327 THEN
328 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
329 END IF;
330 --
331 IF l_debug_on THEN
332 WSH_DEBUG_SV.push(l_module_name);
333 --
334 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
335 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
336 WSH_DEBUG_SV.log(l_module_name,'P_STOP_SEQUENCE_NUMBER',P_STOP_SEQUENCE_NUMBER);
337 END IF;
338 --
339 OPEN c_check_unique;
340 FETCH c_check_unique
341 INTO l_stop_id;
342
343 /* for update check if the new sequence number is same as existing value
344 then success */
345 IF (
346 (p_stop_id IS NOT NULL
347 AND l_stop_id = p_stop_id
348 ) OR
349 (c_check_unique%NOTFOUND) OR
350 (l_stop_id IS NULL)
351 )THEN
352 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
353 ELSE
354 -- l_stop_id is not null and <> p_stop_id
355 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
356 END IF;
357 /*
358 -- no records exist
359 IF (l_check_flag = 'N' OR c_check_unique%NOTFOUND) THEN
360 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
361 ELSIF l_check_flag = 'Y' THEN
362 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
363 END IF;
364 */
365
366 CLOSE c_check_unique;
367
368 --
369 -- Debug Statements
370 --
371 IF l_debug_on THEN
372 WSH_DEBUG_SV.pop(l_module_name);
373 END IF;
374 --
375 EXCEPTION
376 WHEN OTHERS THEN
377 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_TRIP_STOP_VALIDATIONS.validate_unique_sequence');
378 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
379 --
380 -- Debug Statements
381 --
382 IF l_debug_on THEN
383 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
384 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
385 END IF;
386 --
387 END;
388
389 PROCEDURE check_for_negative_number
390 (p_stop_sequence_number IN NUMBER,
391 x_return_status OUT NOCOPY VARCHAR2) IS
392 --
393 l_debug_on BOOLEAN;
394 --
395 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_FOR_NEGATIVE_NUMBER';
396 --
397 BEGIN
398 --
399 -- Debug Statements
400 --
401 --
402 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
403 --
404 IF l_debug_on IS NULL
405 THEN
406 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
407 END IF;
408 --
409 IF l_debug_on THEN
410 WSH_DEBUG_SV.push(l_module_name);
411 --
412 WSH_DEBUG_SV.log(l_module_name,'P_STOP_SEQUENCE_NUMBER',P_STOP_SEQUENCE_NUMBER);
413 END IF;
414 --
415 IF p_stop_sequence_number > 0 THEN
416 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
417 ELSE
418 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
419 END IF;
420
421 --
422 -- Debug Statements
423 --
424 IF l_debug_on THEN
425 WSH_DEBUG_SV.pop(l_module_name);
426 END IF;
427 --
428 EXCEPTION
429 WHEN OTHERS THEN
430 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_TRIP_STOP_VALIDATIONS.check_for_negative_number');
431 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
432 --
433 -- Debug Statements
434 --
435 IF l_debug_on THEN
436 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
437 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
438 END IF;
439 --
440 END ;
441
442 PROCEDURE validate_stop_status
443 (p_stop_status IN VARCHAR2,
444 x_return_status OUT NOCOPY VARCHAR2) IS
445 --
446 l_debug_on BOOLEAN;
447 --
448 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_STOP_STATUS';
449 --
450 BEGIN
451
452 --
453 -- Debug Statements
454 --
455 --
456 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
457 --
458 IF l_debug_on IS NULL
459 THEN
460 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
461 END IF;
462 --
463 IF l_debug_on THEN
464 WSH_DEBUG_SV.push(l_module_name);
465 --
466 WSH_DEBUG_SV.log(l_module_name,'P_STOP_STATUS',P_STOP_STATUS);
467 END IF;
468 --
469 IF p_stop_status = 'OP' THEN
470 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
471 ELSE
472 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
473 END IF;
474
475 --
476 -- Debug Statements
477 --
478 IF l_debug_on THEN
479 WSH_DEBUG_SV.pop(l_module_name);
480 END IF;
481 --
482 EXCEPTION
483 WHEN OTHERS THEN
484 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_TRIP_STOP_VALIDATIONS.validate_stop_status');
485 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
486 --
487 -- Debug Statements
488 --
489 IF l_debug_on THEN
490 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
491 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
492 END IF;
493 --
494 END;
495 /** Logic used here
496 For this trip_id and stop_id, find all the deliveries associated
497 which means find deliveries which will be OPEN and have this stop location
498 as either their pickup location or drop off location.
499
500 If yes then
501 evaluate with the new stop sequence to see if the original pickup or dropoff
502 plans for delivery is not altered by updating this stop sequence.
503 End if;
504
505 x_del_to_unassign returns the list of deliveries which have to be unassigned
506
507 **/
508 PROCEDURE valid_delivery_on_trip
509 (p_stop_id IN NUMBER,
510 p_trip_id IN NUMBER,
511 p_stop_sequence_number IN NUMBER,
512 x_del_to_unassign OUT NOCOPY WSH_UTIL_CORE.ID_TAB_TYPE,
513 x_return_status OUT NOCOPY VARCHAR2) IS
514
515 CURSOR c_stop_details IS
516 SELECT stop_sequence_number,
517 stop_id
518 FROM wsh_trip_stops
519 WHERE trip_id = p_trip_id
520 AND status_code = 'OP' -- this is a case only for Open stops
521 AND stop_id <> p_stop_id ;
522
523 l_rec_stop WSH_TRIP_STOPS_VALIDATIONS.stop_details_tab;
524
525 -- Question , Can there be multiple record with same stop id as pickup
526 CURSOR c_wdl_details1 IS
527 SELECT pick_up_stop_id,
528 drop_off_stop_id,
529 wdl.delivery_id
530 FROM wsh_delivery_legs wdl
531 WHERE (pick_up_stop_id = p_stop_id
532 OR drop_off_stop_id = p_stop_id);
533
534 CURSOR c_wdl_details2 IS
535 SELECT pick_up_stop_id,
536 drop_off_stop_id,
537 delivery_id
538 FROM wsh_delivery_legs
539 WHERE drop_off_stop_id = p_stop_id;
540
541 l_rec_wdl1 WSH_TRIP_STOPS_VALIDATIONS.dleg_details_tab;
542 --l_rec_wdl2 c_wdl_details%TYPE;
543
544 stop_count NUMBER := 0;
545 dleg_count NUMBER := 0;
546 l_num_warn NUMBER := 0;
547
548 --this will be used to unassign deliveries from trip
549 l_del_to_unassign WSH_UTIL_CORE.id_tab_type;
550 l_return_status VARCHAR2(30) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
551
552
553 --
554 l_debug_on BOOLEAN;
555 --
556 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALID_DELIVERY_ON_TRIP';
557 --
558 BEGIN
559
560 --
561 -- Debug Statements
562 --
563 --
564 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
565 --
566 IF l_debug_on IS NULL
567 THEN
568 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
569 END IF;
570 --
571 IF l_debug_on THEN
572 WSH_DEBUG_SV.push(l_module_name);
573 --
574 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
575 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
576 WSH_DEBUG_SV.log(l_module_name,'P_STOP_SEQUENCE_NUMBER',P_STOP_SEQUENCE_NUMBER);
577 END IF;
578 --
579 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
580
581 FOR rec in c_stop_details
582 LOOP
583 stop_count := stop_count + 1;
584 l_rec_stop(stop_count).stop_sequence_number := rec.stop_sequence_number;
585 l_rec_stop(stop_count).stop_id := rec.stop_id;
586
587 END LOOP;
588
589
590 -- If no records are found means,there is no delivery associated
591 -- with the stops and the sequence numbers can be updated
592
593 -- for the stop ,find delivery legs where this stop is pickup or dropoff
594
595 FOR rec IN c_wdl_details1
596 LOOP
597 dleg_count := dleg_count + 1;
598 l_rec_wdl1(dleg_count).pick_up_stop_id := rec.pick_up_stop_id;
599 l_rec_wdl1(dleg_count).drop_off_stop_id := rec.drop_off_stop_id;
600 l_rec_wdl1(dleg_count).delivery_id := rec.delivery_id;
601
602 END LOOP;
603
604
605 /*
606 -- for the stop ,find delivery legs where this stop is drop off
607
608 OPEN c_wdl_details2;
609 FETCH c_wdl_details2
610 INTO l_rec_wdl2;
611 CLOSE c_wdl_details2;
612 */
613
614 -- Warning will be that some of the deliveries will be unassigned
615 -- from the trip
616
617 FOR i in 1..l_rec_stop.count
618 LOOP
619 FOR j in 1..l_rec_wdl1.count
620 LOOP
621 IF l_rec_wdl1(j).pick_up_stop_id = l_rec_stop(i).stop_id THEN
622 --compare with new stop sequence number
623 -- If new stop is earlier than the pick up - then message
624 IF p_stop_sequence_number < l_rec_stop(i).stop_sequence_number THEN
625 l_del_to_unassign(l_del_to_unassign.count + 1) :=
626 l_rec_wdl1(j).delivery_id;
627 FND_MESSAGE.SET_NAME('WSH','WSH_DEL_UNASSIGN_TRIP');
628 --
629 -- Debug Statements
630 --
631 IF l_debug_on THEN
632 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
633 END IF;
634 --
635 FND_MESSAGE.SET_TOKEN('DELIVERY_NAME',
636 WSH_NEW_DELIVERIES_PVT.get_name(l_rec_wdl1(j).delivery_id));
637 --
638 -- Debug Statements
639 --
640 IF l_debug_on THEN
641 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
642 END IF;
643 --
644 FND_MESSAGE.SET_TOKEN('TRIP_NAME',
645 WSH_TRIPS_PVT.get_name(p_trip_id));
646 l_num_warn := l_num_warn + 1;
647 l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
648 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING);
649
650 END IF;
651
652 -- Both cases for pickup and drop off cannot be true separately at the same time
653 -- It can be either pickup or drop off ,as we match with the stop id when
654 -- creating l_rec_wdl1 and there is no existing record in l_rec_stop for p_stop_id
655
656 ELSIF l_rec_wdl1(j).drop_off_stop_id = l_rec_stop(i).stop_id THEN
657 --compare with new stop sequence number
658 -- If new stop is later than the dropoff - then message
659 IF p_stop_sequence_number > l_rec_stop(i).stop_sequence_number THEN
660 l_del_to_unassign(l_del_to_unassign.count + 1) :=
661 l_rec_wdl1(j).delivery_id;
662 FND_MESSAGE.SET_NAME('WSH','WSH_DEL_UNASSIGN_TRIP');
663 --
664 -- Debug Statements
665 --
666 IF l_debug_on THEN
667 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
668 END IF;
669 --
670 FND_MESSAGE.SET_TOKEN('DELIVERY_NAME',
671 WSH_NEW_DELIVERIES_PVT.get_name(l_rec_wdl1(j).delivery_id));
672 --
673 -- Debug Statements
674 --
675 IF l_debug_on THEN
676 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
677 END IF;
678 --
679 FND_MESSAGE.SET_TOKEN('TRIP_NAME',
680 WSH_TRIPS_PVT.get_name(p_trip_id));
681 l_num_warn := l_num_warn + 1;
682 l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
683 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING);
684
685 END IF;
686
687 END IF;
688 END LOOP;
689 END LOOP;
690 -- as of now ,keep error
691 IF (l_num_warn > 0 AND l_del_to_unassign.count > 0) THEN
692 --
693 -- Debug Statements
694 --
695 IF l_debug_on THEN
696 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_ACTIONS.UNASSIGN_TRIP',WSH_DEBUG_SV.C_PROC_LEVEL);
697 END IF;
698 --
699 WSH_TRIPS_ACTIONS.unassign_trip
700 (p_del_rows => l_del_to_unassign,
701 p_trip_id => p_trip_id,
702 x_return_status => l_return_status);
703 END IF;
704
705
706 IF l_num_warn > 0 THEN
707 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
708 ELSIF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
709 x_return_status := l_return_status;
710 END IF;
711
712
713 --
714 -- Debug Statements
715 --
716 IF l_debug_on THEN
717 WSH_DEBUG_SV.pop(l_module_name);
718 END IF;
719 --
720 EXCEPTION
721 WHEN OTHERS THEN
722 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_TRIP_STOP_VALIDATIONS.valid_delivery_on_trip');
723 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
724 --
725 -- Debug Statements
726 --
727 IF l_debug_on THEN
728 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
729 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
730 END IF;
731 --
732 END;
733 /*
734 This is called from Assign to Trip
735 Case1 - Use pickup and dropoff locations on delivery to create new stops.
736 In this case, stop sequence number has to be generated and then
737 validated
738 Case2 - Use new pickup and dropoff locations specified.In this case just
739 validate the new pickup and stop sequence number.
740 Case3 - Use new pickup and the drop off on the delivery.In this case, validate
741 the new pickup stop sequence and generate and validate for
742 dropoff (as per delivery).
743 Test the case, where there are multiple deliveries.
744 In that case,since new Pickup or Dropoff takes precedence, so
745 in this case the dropoff locations on all the deliveries will have
746 new stops created but only 1 pickup will be created.
747
748 Case4 - Use pickup on the delivery and new drop off location.In this case,
749 validate the new dropoff stop sequence number and generate
750 and validate for the pickup exisitng on the delivery.
751
752 */
753 PROCEDURE get_new_sequence_number
754 (x_stop_sequence_number IN OUT NOCOPY NUMBER,
755 p_trip_id IN NUMBER,
756 p_status_code IN VARCHAR2,
757 p_stop_id IN NUMBER,
758 p_new_flag IN VARCHAR2,
759 x_return_status OUT NOCOPY VARCHAR2
760 ) IS
761
762 CURSOR c_get_max_sequence IS
763 SELECT nvl(max(stop_sequence_number),0) stop_sequence_number
764 FROM wsh_trip_stops
765 WHERE trip_id = p_trip_id;
766
767 l_stop_sequence_number NUMBER;
768
769 --
770 l_debug_on BOOLEAN;
771 --
772 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_NEW_SEQUENCE_NUMBER';
773 --
774 BEGIN
775 -- The processing is for each stop and not the combination
776 -- It can be a new Pickup
777 -- or New Dropoff
778 -- or as per Delivery
779
780 --
781 -- Debug Statements
782 --
783 --
784 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
785 --
786 IF l_debug_on IS NULL
787 THEN
788 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
789 END IF;
790 --
791 IF l_debug_on THEN
792 WSH_DEBUG_SV.push(l_module_name);
793 --
794 WSH_DEBUG_SV.log(l_module_name,'X_STOP_SEQUENCE_NUMBER',X_STOP_SEQUENCE_NUMBER);
795 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
796 WSH_DEBUG_SV.log(l_module_name,'P_STATUS_CODE',P_STATUS_CODE);
797 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
798 WSH_DEBUG_SV.log(l_module_name,'P_NEW_FLAG',P_NEW_FLAG);
799 END IF;
800 --
801 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
802 l_stop_sequence_number := 0;
803
804 -- new pickup or dropoff
805 IF (p_new_flag IN ('PICKUP','DROPOFF')) THEN /* pickup or dropoff */
806
807 validate_sequence_number
808 (p_stop_id => p_stop_id,
809 p_stop_sequence_number => x_stop_sequence_number,
810 p_trip_id => p_trip_id,
811 p_status_code => p_status_code,
812 x_return_status => x_return_status);
813
814 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
815 --
816 -- Debug Statements
817 --
818 IF l_debug_on THEN
819 WSH_DEBUG_SV.pop(l_module_name);
820 END IF;
821 --
822 RETURN;
823 END IF;
824
825 -- no new pickup or dropoff
826 -- use delivery level information
827 ELSIF p_new_flag = 'DELIVERY' THEN /* delivery */
828 /* For an existing delivery and matching stops ,if any ,will have a stop sequence
829 number - in that case the flow will not come to this place.
830 For this code ,So p_stop_sequence_number cannot be populated for an existing delivery.
831 */
832 IF (nvl(x_stop_sequence_number,0) = 0 )THEN
833 OPEN c_get_max_sequence;
834 FETCH c_get_max_sequence
835 INTO l_stop_sequence_number;
836 CLOSE c_get_max_sequence;
837 END IF;
838 -- First time x_stop_sequence will be null then use l_stop_sequence
839 -- Else use x_stop_sequence when this API is called by itself
840 -- SSN change
841 -- Add 10 to derive next SSN
842 l_stop_sequence_number := nvl(x_stop_sequence_number,l_stop_sequence_number) + 10;
843
844 -- Generate Logic here
845 validate_sequence_number
846 (p_stop_id => p_stop_id,
847 p_stop_sequence_number => l_stop_sequence_number,
848 p_trip_id => p_trip_id,
849 p_status_code => p_status_code,
850 x_return_status => x_return_status);
851
852 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
853 -- try another number,append by 10 more
854 get_new_sequence_number
855 (x_stop_sequence_number => l_stop_sequence_number,
856 p_trip_id => p_trip_id,
857 p_status_code => p_status_code,
858 p_stop_id => p_stop_id,
859 p_new_flag => p_new_flag,
860 x_return_status => x_return_status);
861
862 END IF; /* not success */
863
864 x_stop_sequence_number := l_stop_sequence_number;
865
866 END IF; /* End of flag */
867 --
868 -- Debug Statements
869 --
870 IF l_debug_on THEN
871 WSH_DEBUG_SV.pop(l_module_name);
872 END IF;
873 --
874 EXCEPTION
875 WHEN OTHERS THEN
876 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_TRIP_STOP_VALIDATIONS.get_new_sequence_number');
877 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
878 --
879 -- Debug Statements
880 --
881 IF l_debug_on THEN
882 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
883 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
884 END IF;
885 --
886 END;
887
888
889 PROCEDURE Is_Action_Enabled(
890 p_stop_rec_tab IN stop_rec_tab_type,
891 p_action IN VARCHAR2,
892 p_caller IN VARCHAR2,
893 x_return_status OUT NOCOPY VARCHAR2,
894 x_valid_ids OUT NOCOPY wsh_util_core.id_tab_type,
895 x_error_ids OUT NOCOPY wsh_util_core.id_tab_type,
896 x_valid_index_tab OUT NOCOPY wsh_util_core.id_tab_type
897 ) IS
898
899
900 cursor stop_to_del_cur( p_stop_id IN NUMBER ) is
901 select wnd.delivery_id,
902 wnd.organization_id,
903 wnd.status_code,
904 wnd.planned_flag,
905 /*J inbound logistics new column jckwok*/
906 NVL(wnd.shipment_direction, 'O') shipment_direction,
907 wnd.delivery_type, --MDC
908 NVL(wnd.ignore_for_planning, 'N') ignore_for_planning, --OTM R12, glog proj
909 NVL(wnd.tms_interface_flag,WSH_TRIP_STOPS_PVT.C_TMS_NOT_TO_BE_SENT) tms_interface_flag, -- OTM R12, glog proj
910 NVL(mcp.otm_enabled,wsp.otm_enabled) otm_enabled, -- LSP PROJECT : checking OTM enabled flag on client parameters.
911 wnd.client_id -- LSP PROJECT
912 from wsh_new_deliveries wnd,
913 wsh_delivery_legs wdl,
914 wsh_shipping_parameters wsp,
915 mtl_client_parameters_v mcp
916 where wnd.delivery_id = wdl.delivery_id
917 and (wdl.pick_up_stop_id = p_stop_id
918 OR wdl.drop_off_stop_id = p_stop_id )
919 and wnd.organization_id = wsp.organization_id
920 and wnd.client_id = mcp.client_id (+);
921
922 cursor stop_to_det_cur( p_stop_id IN NUMBER ) is
923 select distinct 'X'
924 from wsh_delivery_details wdd,
925 wsh_new_deliveries wnd,
926 wsh_delivery_assignments_v wda,
927 wsh_delivery_legs wdl
928 where (wdl.pick_up_stop_id = p_stop_id OR wdl.drop_off_stop_id = p_stop_id)
929 and wnd.delivery_id = wdl.delivery_id
930 and wda.delivery_id = wnd.delivery_id
931 and wdd.delivery_detail_id = wda.delivery_detail_id
932 and wdd.source_code = 'WSH'
933 and wdd.container_flag = 'N';
934
935 --/== Workflow Changes
936 cursor stop_del_cur_wf( p_stop_id IN NUMBER ) is
937 select wnd.delivery_id,
938 wnd.delivery_scpod_wf_process,
939 wnd.del_wf_intransit_attr,
940 wnd.del_wf_close_attr,
941 decode(p_stop_id,wdl.drop_off_stop_id,'D',wdl.pick_up_stop_id,'P','X') stop_type
942 from wsh_new_deliveries wnd,
943 wsh_delivery_legs wdl
944 where wnd.delivery_id = wdl.delivery_id
945 and (wdl.pick_up_stop_id = p_stop_id OR
946 wdl.drop_off_stop_id = p_stop_id );
947
948 l_override_wf VARCHAR2(1);
949 l_del_entity_ids WSH_UTIL_CORE.column_tab_type;
950 l_purged_count NUMBER;
951 l_wf_rs VARCHAR2(1);
952 e_scpod_wf_inprogress EXCEPTION;
953 --==/
954
955 l_stop_actions_tab StopActionsTabType;
956 l_valid_ids wsh_util_core.id_tab_type;
957 l_error_ids wsh_util_core.id_tab_type;
958 l_valid_index_tab wsh_util_core.id_tab_type;
959 l_dlvy_rec_tab WSH_DELIVERY_VALIDATIONS.dlvy_rec_tab_type;
960
961 l_tpw_temp VARCHAR2(1);
962 l_return_status VARCHAR2(1);
963 error_in_init_actions EXCEPTION;
964 e_set_messages EXCEPTION;
965
966 l_debug_on BOOLEAN;
967 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'IS_ACTION_ENABLED';
968 --
969 l_caller VARCHAR2(100);
970
971 BEGIN
972 --
973 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
974 --
975 IF l_debug_on IS NULL
976 THEN
977 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
978 END IF;
979 --
980 IF l_debug_on THEN
981 WSH_DEBUG_SV.push(l_module_name);
982 --
983 WSH_DEBUG_SV.log(l_module_name,'p_caller',p_caller);
984 WSH_DEBUG_SV.log(l_module_name,'p_action',p_action);
985 END IF;
986
987 Init_Stop_Actions_Tbl(
988 p_action => p_action,
989 x_stop_actions_tab => l_stop_actions_tab,
990 x_return_status => x_return_status);
991
992 IF l_debug_on THEN
993 WSH_DEBUG_SV.log(l_module_name,'Init_Detail_Actions_Tbl x_return_status',x_return_status);
994 END IF;
995
996 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
997 raise error_in_init_actions;
998 END IF;
999
1000 FOR j IN p_stop_rec_tab.FIRST..p_stop_rec_tab.LAST LOOP
1001 BEGIN
1002
1003 -- J-IB-NPARIKH-{
1004 --
1005 l_caller := p_caller;
1006 --
1007 --
1008 IF l_debug_on THEN
1009 wsh_debug_sv.log(l_module_name, 'l_caller', l_caller);
1010 END IF;
1011 --
1012 --
1013 -- Actions on inbound trip-stops are allowed only if caller
1014 -- starts with one of the following:
1015 -- - FTE
1016 -- - WSH_IB
1017 -- - WSH_PUB
1018 -- - WSH_TP_RELEASE
1019 -- For any other callers, set l_caller to WSH_FSTRX
1020 -- Since for caller, WSH_FSTRX, all actions are disabled
1021 -- on inbound trip stops
1022 --
1023 --
1024 --
1025 IF nvl(p_stop_rec_tab(j).shipments_type_flag,'O') = 'I'
1026 THEN
1027 --{
1028 IF l_caller LIKE 'FTE%'
1029 OR l_caller LIKE 'WSH_PUB%'
1030 OR l_caller LIKE 'WSH_IB%'
1031 OR l_caller LIKE 'WSH_TP_RELEASE%'
1032 THEN
1033 NULL;
1034 ELSE
1035 l_caller := 'WSH_FSTRX';
1036 END IF;
1037 --}
1038 END IF;
1039 --
1040 --
1041 IF l_debug_on THEN
1042 wsh_debug_sv.log(l_module_name, 'l_caller-modified', l_caller);
1043 END IF;
1044 -- J-IB-NPARIKH-}
1045 --
1046
1047 IF (l_stop_actions_tab.COUNT > 0) THEN
1048 For k in l_stop_actions_tab.FIRST..l_stop_actions_tab.LAST LOOP
1049 IF(nvl(l_stop_actions_tab(k).status_code,p_stop_rec_tab(j).status_code) = p_stop_rec_tab(j).status_code
1050 AND nvl(l_stop_actions_tab(k).caller,p_caller) = p_caller
1051 AND l_stop_actions_tab(k).action_not_allowed = p_action
1052 -- add check to compare shipments_type_flag jckwok
1053 AND nvl(l_stop_actions_tab(k).shipments_type_flag, nvl(p_stop_rec_tab(j).shipments_type_flag,'O')) = nvl(p_stop_rec_tab(j).shipments_type_flag,'O')) THEN
1054 RAISE e_set_messages;
1055 END IF;
1056 END LOOP;
1057 END IF;
1058
1059 IF ( p_action ='PICK-RELEASE') THEN
1060 FOR cur_rec IN stop_to_del_cur(p_stop_rec_tab(j).stop_id) LOOP
1061 l_dlvy_rec_tab(l_dlvy_rec_tab.count+1) := cur_rec;
1062 END LOOP;
1063
1064 WSH_DELIVERY_VALIDATIONS.Is_Action_Enabled(
1065 p_dlvy_rec_tab => l_dlvy_rec_tab,
1066 p_action => p_action,
1067 p_caller => p_caller,
1068 x_return_status => l_return_status,
1069 x_valid_ids => l_valid_ids,
1070 x_error_ids => l_error_ids,
1071 x_valid_index_tab => l_valid_index_tab);
1072
1073 IF l_debug_on THEN
1074 WSH_DEBUG_SV.log(l_module_name,'WSH_DELIVERY_VALIDATIONS.Is_Action_Enabled l_return_status',l_return_status);
1075 END IF;
1076
1077 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS)
1078 AND l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1079 RAISE e_set_messages;
1080 ELSE
1081 x_valid_ids(x_valid_ids.COUNT + 1) := p_stop_rec_tab(j).stop_id;
1082 x_valid_index_tab(j) := j;
1083 END IF;
1084
1085 ELSIF ( p_action IN ('PLAN', 'UNPLAN' )) THEN
1086 open stop_to_det_cur( p_stop_rec_tab(j).stop_id);
1087 Fetch stop_to_det_cur into l_tpw_temp;
1088 close stop_to_det_cur;
1089
1090 IF ( l_tpw_temp is not null ) THEN
1091 x_valid_ids(x_valid_ids.COUNT + 1) := p_stop_rec_tab(j).stop_id;
1092 x_valid_index_tab(j) := j;
1093 ELSE
1094 FOR cur_rec IN stop_to_del_cur(p_stop_rec_tab(j).stop_id) LOOP
1095 l_dlvy_rec_tab(l_dlvy_rec_tab.count+1) := cur_rec;
1096 END LOOP;
1097
1098 WSH_DELIVERY_VALIDATIONS.Is_Action_Enabled(
1099 p_dlvy_rec_tab => l_dlvy_rec_tab,
1100 p_action => p_action,
1101 p_caller => p_caller,
1102 x_return_status => l_return_status,
1103 x_valid_ids => l_valid_ids,
1104 x_error_ids => l_error_ids,
1105 x_valid_index_tab => l_valid_index_tab);
1106
1107 IF l_debug_on THEN
1108 WSH_DEBUG_SV.log(l_module_name,'WSH_DELIVERY_VALIDATIONS.Is_Action_Enabled l_return_status',
1109 l_return_status);
1110 END IF;
1111
1112 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS)
1113 AND l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1114 RAISE e_set_messages;
1115 ELSE
1116 x_valid_ids(x_valid_ids.COUNT + 1) := p_stop_rec_tab(j).stop_id;
1117 x_valid_index_tab(j) := j;
1118 END IF;
1119 END IF;
1120 --/== Workflow Changes
1121 ELSIF (p_action IN('UPDATE-STATUS')) THEN
1122 l_override_wf:= fnd_profile.value('WSH_OVERRIDE_SCPOD_WF');
1123 IF (nvl(l_override_wf,'N') = 'N') THEN
1124 FOR cur_rec IN stop_del_cur_wf(p_stop_rec_tab(j).stop_id) LOOP
1125 IF (cur_rec.delivery_scpod_wf_process is not null and
1126 ( ( cur_rec.stop_type='P' and cur_rec.del_wf_intransit_attr = 'I')
1127 or ( cur_rec.stop_type='D' and cur_rec.del_wf_close_attr = 'I') ) )THEN
1128 RAISE e_scpod_wf_inprogress;
1129 END IF;
1130 END LOOP;
1131 END IF;
1132 x_valid_ids(x_valid_ids.COUNT + 1) := p_stop_rec_tab(j).stop_id;
1133 x_valid_index_tab(j) := j; -- Workflow Changes ==/
1134 ELSE
1135 x_valid_ids(x_valid_ids.COUNT + 1) := p_stop_rec_tab(j).stop_id;
1136 x_valid_index_tab(j) := j;
1137 END IF;
1138 EXCEPTION
1139 WHEN e_scpod_wf_inprogress THEN --/== Workflow Changes
1140 x_error_ids(x_error_ids.count +1) := p_stop_rec_tab(j).stop_id;
1141 FND_MESSAGE.SET_NAME('WSH','WSH_WF_STOP_ACTION_INELIGIBLE');
1142 FND_MESSAGE.Set_Token('STOP_ID',x_error_ids(x_error_ids.count));
1143 FND_MESSAGE.Set_Token('ACTION',wsh_util_core.get_action_meaning('STOP', p_action));
1144 wsh_util_core.add_message('E',l_module_name); --==/
1145 WHEN e_set_messages THEN
1146 x_error_ids(x_error_ids.count +1) := p_stop_rec_tab(j).stop_id;
1147 IF p_caller = 'WSH_PUB'
1148 OR p_caller like 'FTE%' THEN
1149 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_ACTION_INELIGIBLE');
1150 FND_MESSAGE.Set_Token('STOP_ID',x_error_ids(x_error_ids.count));
1151 FND_MESSAGE.Set_Token('ACTION',wsh_util_core.get_action_meaning('STOP', p_action));
1152 wsh_util_core.add_message('E',l_module_name);
1153 END IF;
1154 END;
1155 END LOOP;
1156
1157 IF (x_valid_ids.COUNT = 0 ) THEN
1158 --{
1159 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1160 --
1161 IF NOT (l_caller = 'WSH_PUB' OR l_caller LIKE 'FTE%') THEN
1162 FND_MESSAGE.SET_NAME('WSH','WSH_ACTION_ENABLED');
1163 wsh_util_core.add_message(x_return_status,l_module_name);
1164 END IF;
1165 --}
1166 ELSIF (x_valid_ids.COUNT = p_stop_rec_tab.COUNT) THEN
1167 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1168 ELSIF (x_valid_ids.COUNT < p_stop_rec_tab.COUNT ) THEN
1169 --{
1170 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1171 --
1172 IF NOT (l_caller = 'WSH_PUB' OR l_caller LIKE 'FTE%') THEN
1173 FND_MESSAGE.SET_NAME('WSH','WSH_ACTION_ENABLED_WARN');
1174 wsh_util_core.add_message(x_return_status,l_module_name);
1175 END IF;
1176 --}
1177 ELSE
1178 --{
1179 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1180 --
1181 IF NOT (l_caller = 'WSH_PUB' OR l_caller LIKE 'FTE%') THEN
1182 FND_MESSAGE.SET_NAME('WSH','WSH_ACTION_ENABLED');
1183 wsh_util_core.add_message(x_return_status,l_module_name);
1184 END IF;
1185 --}
1186 END IF;
1187
1188 IF l_debug_on THEN
1189 WSH_DEBUG_SV.pop(l_module_name);
1190 END IF;
1191
1192 EXCEPTION
1193 WHEN error_in_init_actions THEN
1194 -- OTM R12, glog proj, other cursors are not using OPEN/FETCH
1195 IF stop_to_det_cur%ISOPEN THEN
1196 CLOSE stop_to_det_cur;
1197 END IF;
1198 IF l_debug_on THEN
1199 WSH_DEBUG_SV.logmsg(l_module_name,'error_in_init_actions exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1200 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:error_in_init_actions');
1201 END IF;
1202
1203 WHEN OTHERS THEN
1204 -- OTM R12, glog proj, other cursors are not using OPEN/FETCH
1205 IF stop_to_det_cur%ISOPEN THEN
1206 CLOSE stop_to_det_cur;
1207 END IF;
1208 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1209 IF l_debug_on THEN
1210 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1211 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1212 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1213 END IF;
1214 END Is_Action_Enabled;
1215
1216
1217 /*
1218 Procedure populate_external_edf is called from
1219 eliminate_displayonly_fields to populate the external value
1220 for a given internal field
1221 */
1222
1223 PROCEDURE populate_external_edf(
1224 p_internal IN NUMBER
1225 , p_external IN VARCHAR2
1226 , x_internal IN OUT NOCOPY NUMBER
1227 , x_external IN OUT NOCOPY VARCHAR2
1228 )
1229 IS
1230 BEGIN
1231
1232 IF p_internal <> FND_API.G_MISS_NUM OR p_internal IS NULL THEN
1233 x_internal := p_internal;
1234 IF p_internal IS NULL THEN
1235 x_external := NULL;
1236 ELSE
1237 x_external := p_external;
1238 END IF;
1239 ELSIF p_external <> FND_API.G_MISS_CHAR OR p_external IS NULL THEN
1240 x_external := p_external;
1241 IF x_external IS NULL THEN
1242 x_internal := NULL;
1243 ELSE
1244 x_internal := p_internal;
1245 END IF;
1246 END IF;
1247
1248 END populate_external_edf;
1249
1250
1251 /*
1252 Procedure populate_external_edf is called from
1253 eliminate_displayonly_fields to populate the external value
1254 for a given internal field
1255 */
1256
1257 PROCEDURE populate_external_edf(
1258 p_internal IN VARCHAR2
1259 , p_external IN VARCHAR2
1260 , x_internal IN OUT NOCOPY VARCHAR2
1261 , x_external IN OUT NOCOPY VARCHAR2
1262 )
1263 IS
1264 BEGIN
1265
1266 IF p_internal <> FND_API.G_MISS_CHAR OR p_internal IS NULL THEN
1267 x_internal := p_internal;
1268 IF p_internal IS NULL THEN
1269 x_external := NULL;
1270 ELSE
1271 x_external := p_external;
1272 END IF;
1273 ELSIF p_external <> FND_API.G_MISS_CHAR OR p_external IS NULL THEN
1274 x_external := p_external;
1275 IF x_external IS NULL THEN
1276 x_internal := NULL;
1277 ELSE
1278 x_internal := p_internal;
1279 END IF;
1280 END IF;
1281
1282 END populate_external_edf;
1283
1284
1285
1286 PROCEDURE eliminate_displayonly_fields (
1287 p_stop_rec IN WSH_TRIP_STOPS_PVT.trip_stop_rec_type
1288 , p_in_rec IN WSH_TRIP_STOPS_GRP.stopInRecType
1289 , x_stop_rec IN OUT NOCOPY WSH_TRIP_STOPS_PVT.trip_stop_rec_type
1290 )
1291 IS
1292 BEGIN
1293
1294 /*
1295 Enable the x_delivery_detail_rec, with the columns that are not
1296 permanently disabled.
1297 */
1298 populate_external_edf(p_stop_rec.STOP_LOCATION_ID,
1299 p_stop_rec.stop_location_code,
1300 x_stop_rec.STOP_LOCATION_ID,
1301 x_stop_rec.stop_location_code);
1302
1303 /*
1304 populate_external_edf(p_stop_rec.trip_id,
1305 p_stop_rec.trip_name,
1306 x_stop_rec.trip_id,
1307 x_stop_rec.trip_name);
1308
1309 */
1310 IF p_stop_rec.DEPARTURE_SEAL_CODE <> FND_API.G_MISS_CHAR
1311 OR p_stop_rec.DEPARTURE_SEAL_CODE IS NULL THEN
1312 x_stop_rec.DEPARTURE_SEAL_CODE :=
1313 p_stop_rec.DEPARTURE_SEAL_CODE;
1314 END IF;
1315
1316 -- SSN change
1317 -- For mode=PAD, Stop_sequence_number would be null when user tries to create a stop
1318 IF ((p_in_rec.action_code = 'CREATE')
1319 AND
1320 (WSH_TRIPS_ACTIONS.GET_STOP_SEQ_MODE = WSH_INTERFACE_GRP.G_STOP_SEQ_MODE_PAD))
1321 THEN
1322 -- csun stop sequence enhancement for 11.5.10, the stop sequence number for a
1323 -- trip stop is set to -99 initially since it is a required filed in the table,
1324 -- it will be re-sequenced in WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP right after
1325 -- it is created.
1326 x_stop_rec.STOP_SEQUENCE_NUMBER := -99;
1327 -- but for mode = SSN, stop_sequence_number field would always have to be specified
1328 -- when creating a stop
1329 ELSIF p_stop_rec.STOP_SEQUENCE_NUMBER <> FND_API.G_MISS_NUM
1330 OR p_stop_rec.STOP_SEQUENCE_NUMBER IS NULL THEN
1331 x_stop_rec.STOP_SEQUENCE_NUMBER :=
1332 p_stop_rec.STOP_SEQUENCE_NUMBER;
1333 END IF;
1334 -- end of SSN change
1335
1336 IF p_stop_rec.PLANNED_ARRIVAL_DATE <> FND_API.G_MISS_DATE
1337 OR p_stop_rec.PLANNED_ARRIVAL_DATE IS NULL THEN
1338 x_stop_rec.PLANNED_ARRIVAL_DATE :=
1339 p_stop_rec.PLANNED_ARRIVAL_DATE;
1340 END IF;
1341 IF p_stop_rec.PLANNED_DEPARTURE_DATE <> FND_API.G_MISS_DATE
1342 OR p_stop_rec.PLANNED_DEPARTURE_DATE IS NULL THEN
1343 x_stop_rec.PLANNED_DEPARTURE_DATE :=
1344 p_stop_rec.PLANNED_DEPARTURE_DATE;
1345 END IF;
1346 IF p_stop_rec.DEPARTURE_GROSS_WEIGHT <> FND_API.G_MISS_NUM
1347 OR p_stop_rec.DEPARTURE_GROSS_WEIGHT IS NULL THEN
1348 x_stop_rec.DEPARTURE_GROSS_WEIGHT :=
1349 p_stop_rec.DEPARTURE_GROSS_WEIGHT;
1350 END IF;
1351 IF p_stop_rec.DEPARTURE_NET_WEIGHT <> FND_API.G_MISS_NUM
1352 OR p_stop_rec.DEPARTURE_NET_WEIGHT IS NULL THEN
1353 x_stop_rec.DEPARTURE_NET_WEIGHT :=
1354 p_stop_rec.DEPARTURE_NET_WEIGHT;
1355 END IF;
1356
1357 populate_external_edf(p_stop_rec.WEIGHT_UOM_CODE,
1358 p_stop_rec.WEIGHT_UOM_DESC,
1359 x_stop_rec.WEIGHT_UOM_CODE,
1360 x_stop_rec.WEIGHT_UOM_DESC);
1361
1362 IF p_stop_rec.DEPARTURE_VOLUME <> FND_API.G_MISS_NUM
1363 OR p_stop_rec.DEPARTURE_VOLUME IS NULL THEN
1364 x_stop_rec.DEPARTURE_VOLUME :=
1365 p_stop_rec.DEPARTURE_VOLUME;
1366 END IF;
1367
1368 populate_external_edf(p_stop_rec.VOLUME_UOM_CODE,
1369 p_stop_rec.VOLUME_UOM_DESC,
1370 x_stop_rec.VOLUME_UOM_CODE,
1371 x_stop_rec.VOLUME_UOM_DESC);
1372
1373 -- bug 3666967 - need wv_frozen_flag when creating new stop through public api.
1374 IF p_stop_rec.wv_frozen_flag <> FND_API.G_MISS_CHAR THEN
1375 x_stop_rec.wv_frozen_flag :=
1376 p_stop_rec.wv_frozen_flag;
1377 END IF;
1378 -- end bug 3666967
1379
1380 IF p_stop_rec.DEPARTURE_FILL_PERCENT <> FND_API.G_MISS_NUM
1381 OR p_stop_rec.DEPARTURE_FILL_PERCENT IS NULL THEN
1382 x_stop_rec.DEPARTURE_FILL_PERCENT :=
1383 p_stop_rec.DEPARTURE_FILL_PERCENT;
1384 END IF;
1385 IF p_stop_rec.attribute1 <> FND_API.G_MISS_CHAR
1386 OR p_stop_rec.attribute1 IS NULL THEN
1387 x_stop_rec.attribute1 :=
1388 p_stop_rec.attribute1;
1389 END IF;
1390 IF p_stop_rec.attribute2 <> FND_API.G_MISS_CHAR
1391 OR p_stop_rec.attribute2 IS NULL THEN
1392 x_stop_rec.attribute2 :=
1393 p_stop_rec.attribute2;
1394 END IF;
1395 IF p_stop_rec.attribute3 <> FND_API.G_MISS_CHAR
1396 OR p_stop_rec.attribute3 IS NULL THEN
1397 x_stop_rec.attribute3 :=
1398 p_stop_rec.attribute3;
1399 END IF;
1400 IF p_stop_rec.attribute4 <> FND_API.G_MISS_CHAR
1401 OR p_stop_rec.attribute4 IS NULL THEN
1402 x_stop_rec.attribute4 :=
1403 p_stop_rec.attribute4;
1404 END IF;
1405 IF p_stop_rec.attribute5 <> FND_API.G_MISS_CHAR
1406 OR p_stop_rec.attribute5 IS NULL THEN
1407 x_stop_rec.attribute5 :=
1408 p_stop_rec.attribute5;
1409 END IF;
1410 IF p_stop_rec.attribute6 <> FND_API.G_MISS_CHAR
1411 OR p_stop_rec.attribute6 IS NULL THEN
1412 x_stop_rec.attribute6 :=
1413 p_stop_rec.attribute6;
1414 END IF;
1415 IF p_stop_rec.attribute7 <> FND_API.G_MISS_CHAR
1416 OR p_stop_rec.attribute7 IS NULL THEN
1417 x_stop_rec.attribute7 :=
1418 p_stop_rec.attribute7;
1419 END IF;
1420 IF p_stop_rec.attribute8 <> FND_API.G_MISS_CHAR
1421 OR p_stop_rec.attribute8 IS NULL THEN
1422 x_stop_rec.attribute8 :=
1423 p_stop_rec.attribute8;
1424 END IF;
1425 IF p_stop_rec.attribute9 <> FND_API.G_MISS_CHAR
1426 OR p_stop_rec.attribute9 IS NULL THEN
1427 x_stop_rec.attribute9 :=
1428 p_stop_rec.attribute9;
1429 END IF;
1430 IF p_stop_rec.attribute10 <> FND_API.G_MISS_CHAR
1431 OR p_stop_rec.attribute10 IS NULL THEN
1432 x_stop_rec.attribute10 :=
1433 p_stop_rec.attribute10;
1434 END IF;
1435 IF p_stop_rec.attribute11 <> FND_API.G_MISS_CHAR
1436 OR p_stop_rec.attribute11 IS NULL THEN
1437 x_stop_rec.attribute11 :=
1438 p_stop_rec.attribute11;
1439 END IF;
1440 IF p_stop_rec.attribute12 <> FND_API.G_MISS_CHAR
1441 OR p_stop_rec.attribute12 IS NULL THEN
1442 x_stop_rec.attribute12 :=
1443 p_stop_rec.attribute12;
1444 END IF;
1445 IF p_stop_rec.attribute13 <> FND_API.G_MISS_CHAR
1446 OR p_stop_rec.attribute13 IS NULL THEN
1447 x_stop_rec.attribute13 :=
1448 p_stop_rec.attribute13;
1449 END IF;
1450 IF p_stop_rec.attribute14 <> FND_API.G_MISS_CHAR
1451 OR p_stop_rec.attribute14 IS NULL THEN
1452 x_stop_rec.attribute14 :=
1453 p_stop_rec.attribute14;
1454 END IF;
1455 IF p_stop_rec.attribute15 <> FND_API.G_MISS_CHAR
1456 OR p_stop_rec.attribute15 IS NULL THEN
1457 x_stop_rec.attribute15 :=
1458 p_stop_rec.attribute15;
1459 END IF;
1460 IF p_stop_rec.ATTRIBUTE_CATEGORY <> FND_API.G_MISS_CHAR
1461 OR p_stop_rec.ATTRIBUTE_CATEGORY IS NULL THEN
1462 x_stop_rec.ATTRIBUTE_CATEGORY :=
1463 p_stop_rec.ATTRIBUTE_CATEGORY;
1464 END IF;
1465 IF p_stop_rec.tp_attribute1 <> FND_API.G_MISS_CHAR
1466 OR p_stop_rec.tp_attribute1 IS NULL THEN
1467 x_stop_rec.tp_attribute1 :=
1468 p_stop_rec.tp_attribute1;
1469 END IF;
1470 IF p_stop_rec.tp_attribute2 <> FND_API.G_MISS_CHAR
1471 OR p_stop_rec.tp_attribute2 IS NULL THEN
1472 x_stop_rec.tp_attribute2 :=
1473 p_stop_rec.tp_attribute2;
1474 END IF;
1475 IF p_stop_rec.tp_attribute3 <> FND_API.G_MISS_CHAR
1476 OR p_stop_rec.tp_attribute3 IS NULL THEN
1477 x_stop_rec.tp_attribute3 :=
1478 p_stop_rec.tp_attribute3;
1479 END IF;
1480 IF p_stop_rec.tp_attribute4 <> FND_API.G_MISS_CHAR
1481 OR p_stop_rec.tp_attribute4 IS NULL THEN
1482 x_stop_rec.tp_attribute4 :=
1483 p_stop_rec.tp_attribute4;
1484 END IF;
1485 IF p_stop_rec.tp_attribute5 <> FND_API.G_MISS_CHAR
1486 OR p_stop_rec.tp_attribute5 IS NULL THEN
1487 x_stop_rec.tp_attribute5 :=
1488 p_stop_rec.tp_attribute5;
1489 END IF;
1490 IF p_stop_rec.tp_attribute6 <> FND_API.G_MISS_CHAR
1491 OR p_stop_rec.tp_attribute6 IS NULL THEN
1492 x_stop_rec.tp_attribute6 :=
1493 p_stop_rec.tp_attribute6;
1494 END IF;
1495 IF p_stop_rec.tp_attribute7 <> FND_API.G_MISS_CHAR
1496 OR p_stop_rec.tp_attribute7 IS NULL THEN
1497 x_stop_rec.tp_attribute7 :=
1498 p_stop_rec.tp_attribute7;
1499 END IF;
1500 IF p_stop_rec.tp_attribute8 <> FND_API.G_MISS_CHAR
1501 OR p_stop_rec.tp_attribute8 IS NULL THEN
1502 x_stop_rec.tp_attribute8 :=
1503 p_stop_rec.tp_attribute8;
1504 END IF;
1505 IF p_stop_rec.tp_attribute9 <> FND_API.G_MISS_CHAR
1506 OR p_stop_rec.tp_attribute9 IS NULL THEN
1507 x_stop_rec.tp_attribute9 :=
1508 p_stop_rec.tp_attribute9;
1509 END IF;
1510 IF p_stop_rec.tp_attribute10 <> FND_API.G_MISS_CHAR
1511 OR p_stop_rec.tp_attribute10 IS NULL THEN
1512 x_stop_rec.tp_attribute10 :=
1513 p_stop_rec.tp_attribute10;
1514 END IF;
1515 IF p_stop_rec.tp_attribute11 <> FND_API.G_MISS_CHAR
1516 OR p_stop_rec.tp_attribute11 IS NULL THEN
1517 x_stop_rec.tp_attribute11 :=
1518 p_stop_rec.tp_attribute11;
1519 END IF;
1520 IF p_stop_rec.tp_attribute12 <> FND_API.G_MISS_CHAR
1521 OR p_stop_rec.tp_attribute12 IS NULL THEN
1522 x_stop_rec.tp_attribute12 :=
1523 p_stop_rec.tp_attribute12;
1524 END IF;
1525 IF p_stop_rec.tp_attribute13 <> FND_API.G_MISS_CHAR
1526 OR p_stop_rec.tp_attribute13 IS NULL THEN
1527 x_stop_rec.tp_attribute13 :=
1528 p_stop_rec.tp_attribute13;
1529 END IF;
1530 IF p_stop_rec.tp_attribute14 <> FND_API.G_MISS_CHAR
1531 OR p_stop_rec.tp_attribute14 IS NULL THEN
1532 x_stop_rec.tp_attribute14 :=
1533 p_stop_rec.tp_attribute14;
1534 END IF;
1535 IF p_stop_rec.tp_attribute15 <> FND_API.G_MISS_CHAR
1536 OR p_stop_rec.tp_attribute15 IS NULL THEN
1537 x_stop_rec.tp_attribute15 :=
1538 p_stop_rec.tp_attribute15;
1539 END IF;
1540 IF p_stop_rec.TP_ATTRIBUTE_CATEGORY <> FND_API.G_MISS_CHAR
1541 OR p_stop_rec.TP_ATTRIBUTE_CATEGORY IS NULL THEN
1542 x_stop_rec.TP_ATTRIBUTE_CATEGORY :=
1543 p_stop_rec.TP_ATTRIBUTE_CATEGORY;
1544 END IF;
1545
1546 IF p_in_rec.caller IN ('FTEMLWRB','WSH_TP_RELEASE') THEN
1547 IF p_stop_rec.wkday_layover_stops <> FND_API.G_MISS_NUM
1548 OR p_stop_rec.wkday_layover_stops IS NULL THEN
1549 x_stop_rec.wkday_layover_stops :=
1550 p_stop_rec.wkday_layover_stops;
1551 END IF;
1552 IF p_stop_rec.wkend_layover_stops <> FND_API.G_MISS_NUM
1553 OR p_stop_rec.wkend_layover_stops IS NULL THEN
1554 x_stop_rec.wkend_layover_stops :=
1555 p_stop_rec.wkend_layover_stops;
1556 END IF;
1557 END IF;
1558 IF p_in_rec.caller='WSH_TP_RELEASE' THEN
1559 IF p_stop_rec.tp_stop_id <> FND_API.G_MISS_NUM
1560 OR p_stop_rec.tp_stop_id IS NULL THEN
1561 x_stop_rec.tp_stop_id :=
1562 p_stop_rec.tp_stop_id;
1563 END IF;
1564 END IF;
1565 --Bug 3282166
1566 --
1567 IF p_in_rec.caller like 'FTE%' THEN
1568 IF p_stop_rec.CARRIER_EST_DEPARTURE_DATE <> FND_API.G_MISS_DATE
1569 OR p_stop_rec.CARRIER_EST_DEPARTURE_DATE IS NULL THEN
1570 x_stop_rec.CARRIER_EST_DEPARTURE_DATE := p_stop_rec.CARRIER_EST_DEPARTURE_DATE;
1571
1572 END IF;
1573 IF p_stop_rec.CARRIER_EST_ARRIVAL_DATE <> FND_API.G_MISS_DATE
1574 OR p_stop_rec.CARRIER_EST_ARRIVAL_DATE IS NULL THEN
1575 x_stop_rec.CARRIER_EST_ARRIVAL_DATE := p_stop_rec.CARRIER_EST_ARRIVAL_DATE;
1576
1577 END IF;
1578 END IF;
1579 --
1580
1581
1582 END eliminate_displayonly_fields;
1583
1584 /*----------------------------------------------------------
1585 -- Procedure disable_from_list will update the record x_out_rec
1586 -- and disables the field contained in p_disabled_list.
1587 -----------------------------------------------------------*/
1588
1589 PROCEDURE disable_from_list(
1590 p_disabled_list IN WSH_UTIL_CORE.column_tab_type
1591 , p_in_rec IN WSH_TRIP_STOPS_PVT.trip_stop_rec_type
1592 , x_out_rec IN OUT NOCOPY WSH_TRIP_STOPS_PVT.trip_stop_rec_type
1593 , x_return_status OUT NOCOPY VARCHAR2
1594 , x_field_name OUT NOCOPY VARCHAR2
1595
1596 ) IS
1597 BEGIN
1598 FOR i IN 1..p_disabled_list.COUNT
1599 LOOP
1600 IF p_disabled_list(i) = 'STOP_LOCATION_CODE' THEN
1601 x_out_rec.STOP_LOCATION_ID := p_in_rec.STOP_LOCATION_ID ;
1602 x_out_rec.STOP_LOCATION_CODE := FND_API.G_MISS_CHAR ;
1603 ELSIF p_disabled_list(i) = 'STOP_SEQUENCE_NUMBER' THEN
1604 x_out_rec.STOP_SEQUENCE_NUMBER := p_in_rec.STOP_SEQUENCE_NUMBER;
1605 ELSIF p_disabled_list(i) = 'PLANNED_ARRIVAL_DATE' THEN
1606 x_out_rec.PLANNED_ARRIVAL_DATE := p_in_rec.PLANNED_ARRIVAL_DATE ;
1607 ELSIF p_disabled_list(i) = 'PLANNED_DEPARTURE_DATE' THEN
1608 x_out_rec.PLANNED_DEPARTURE_DATE := p_in_rec.PLANNED_DEPARTURE_DATE ;
1609
1610 --Bug 3282166
1611 --
1612 ELSIF p_disabled_list(i) = 'CARRIER_EST_DEPARTURE_DATE' THEN
1613 x_out_rec.CARRIER_EST_DEPARTURE_DATE := p_in_rec.CARRIER_EST_DEPARTURE_DATE ;
1614 ELSIF p_disabled_list(i) = 'CARRIER_EST_ARRIVAL_DATE' THEN
1615 x_out_rec.CARRIER_EST_ARRIVAL_DATE := p_in_rec.CARRIER_EST_ARRIVAL_DATE ;
1616 --
1617 ELSIF p_disabled_list(i) = 'DESC_FLEX' THEN
1618 x_out_rec.attribute1 := p_in_rec.attribute1 ;
1619 x_out_rec.attribute2 := p_in_rec.attribute2 ;
1620 x_out_rec.attribute3 := p_in_rec.attribute3 ;
1621 x_out_rec.attribute4 := p_in_rec.attribute4 ;
1622 x_out_rec.attribute5 := p_in_rec.attribute5 ;
1623 x_out_rec.attribute6 := p_in_rec.attribute6 ;
1624 x_out_rec.attribute7 := p_in_rec.attribute7 ;
1625 x_out_rec.attribute8 := p_in_rec.attribute8 ;
1626 x_out_rec.attribute9 := p_in_rec.attribute9 ;
1627 x_out_rec.attribute10 := p_in_rec.attribute10 ;
1628 x_out_rec.attribute11 := p_in_rec.attribute11 ;
1629 x_out_rec.attribute12 := p_in_rec.attribute12 ;
1630 x_out_rec.attribute13 := p_in_rec.attribute13 ;
1631 x_out_rec.attribute14 := p_in_rec.attribute14 ;
1632 x_out_rec.attribute15 := p_in_rec.attribute15 ;
1633 x_out_rec.attribute_category := p_in_rec.attribute_category ;
1634 ELSIF p_disabled_list(i) = 'TP_FLEXFIELD' THEN
1635 x_out_rec.tp_attribute1 := p_in_rec.tp_attribute1 ;
1636 x_out_rec.tp_attribute2 := p_in_rec.tp_attribute2 ;
1637 x_out_rec.tp_attribute3 := p_in_rec.tp_attribute3 ;
1638 x_out_rec.tp_attribute4 := p_in_rec.tp_attribute4 ;
1639 x_out_rec.tp_attribute5 := p_in_rec.tp_attribute5 ;
1640 x_out_rec.tp_attribute6 := p_in_rec.tp_attribute6 ;
1641 x_out_rec.tp_attribute7 := p_in_rec.tp_attribute7 ;
1642 x_out_rec.tp_attribute8 := p_in_rec.tp_attribute8 ;
1643 x_out_rec.tp_attribute9 := p_in_rec.tp_attribute9 ;
1644 x_out_rec.tp_attribute10 := p_in_rec.tp_attribute10 ;
1645 x_out_rec.tp_attribute11 := p_in_rec.tp_attribute11 ;
1646 x_out_rec.tp_attribute12 := p_in_rec.tp_attribute12 ;
1647 x_out_rec.tp_attribute13 := p_in_rec.tp_attribute13 ;
1648 x_out_rec.tp_attribute14 := p_in_rec.tp_attribute14 ;
1649 x_out_rec.tp_attribute15 := p_in_rec.tp_attribute15 ;
1650 x_out_rec.tp_attribute_category := p_in_rec.tp_attribute_category ;
1651 ELSIF p_disabled_list(i) = 'FULL' THEN
1652 NULL;
1653 ELSE
1654 -- invalid name
1655 x_field_name := p_disabled_list(i);
1656 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1657 RETURN;
1658 --
1659 END IF;
1660 END LOOP;
1661 END disable_from_list;
1662
1663
1664 /*
1665 Procedure populate_external_efl is called from
1666 enable_from_list to populate the external value
1667 for a given internal field
1668 */
1669
1670 PROCEDURE populate_external_efl(
1671 p_internal IN NUMBER
1672 , p_external IN VARCHAR2
1673 , x_internal IN OUT NOCOPY NUMBER
1674 , x_external IN OUT NOCOPY VARCHAR2
1675 )
1676 IS
1677 BEGIN
1678
1679 IF x_internal IS NULL THEN
1680 IF p_internal <> FND_API.G_MISS_NUM OR p_internal IS NULL THEN
1681 x_internal := p_internal ;
1682 IF p_internal IS NULL THEN
1683 x_external := NULL;
1684 ELSE
1685 x_external := p_external;
1686 END IF;
1687 ELSIF p_external <> FND_API.G_MISS_CHAR OR p_external IS NULL THEN
1688 x_external := p_external;
1689 IF p_external IS NULL THEN
1690 x_internal := NULL;
1691 ELSE
1692 x_internal := p_internal;
1693 END IF;
1694 END IF;
1695 END IF;
1696 IF p_internal <> FND_API.G_MISS_NUM OR p_internal IS NULL THEN
1697 x_internal := p_internal ;
1698 IF p_internal IS NULL THEN
1699 x_external := NULL;
1700 ELSE
1701 x_external := p_external;
1702 END IF;
1703 ELSIF p_external <> FND_API.G_MISS_CHAR OR p_external IS NULL THEN
1704 x_external := p_external;
1705 IF p_external IS NULL THEN
1706 x_internal := NULL;
1707 ELSE
1708 x_internal := p_internal;
1709 END IF;
1710 END IF;
1711
1712 END populate_external_efl;
1713
1714 /*
1715 Procedure populate_external_efl is called from
1716 enable_from_list to populate the external value
1717 for a given internal field
1718 */
1719
1720 PROCEDURE populate_external_efl(
1721 p_internal IN VARCHAR2
1722 , p_external IN VARCHAR2
1723 , p_mode IN VARCHAR2
1724 , x_internal IN OUT NOCOPY VARCHAR2
1725 , x_external IN OUT NOCOPY VARCHAR2
1726 )
1727 IS
1728 BEGIN
1729
1730 IF p_mode = '+' THEN
1731 IF x_internal IS NULL THEN
1732 IF p_internal <> FND_API.G_MISS_CHAR OR p_internal IS NULL THEN
1733 x_internal := p_internal ;
1734 IF p_internal IS NULL THEN
1735 x_external := NULL;
1736 ELSE
1737 x_external := p_external;
1738 END IF;
1739 ELSIF p_external <> FND_API.G_MISS_CHAR OR p_external IS NULL THEN
1740 x_external := p_external;
1741 IF p_external IS NULL THEN
1742 x_internal := NULL;
1743 ELSE
1744 x_internal := p_internal;
1745 END IF;
1746 END IF;
1747 END IF;
1748 ELSE --p_mode <> +
1749 IF p_internal <> FND_API.G_MISS_CHAR OR p_internal IS NULL THEN
1750 x_internal := p_internal ;
1751 IF p_internal IS NULL THEN
1752 x_external := NULL;
1753 ELSE
1754 x_external := p_external;
1755 END IF;
1756 ELSIF p_external <> FND_API.G_MISS_CHAR OR p_external IS NULL THEN
1757 x_external := p_external;
1758 IF p_external IS NULL THEN
1759 x_internal := NULL;
1760 ELSE
1761 x_internal := p_internal;
1762 END IF;
1763 END IF;
1764 END IF;
1765
1766 END populate_external_efl;
1767
1768
1769
1770 /*----------------------------------------------------------
1771 -- Procedure enable_from_list will update the record x_out_rec for the fields
1772 -- included in p_disabled_list and will enable them
1773 -----------------------------------------------------------*/
1774
1775 PROCEDURE enable_from_list(
1776 p_disabled_list IN WSH_UTIL_CORE.column_tab_type
1777 , p_in_rec IN WSH_TRIP_STOPS_PVT.trip_stop_rec_type
1778 , x_out_rec IN OUT NOCOPY WSH_TRIP_STOPS_PVT.trip_stop_rec_type
1779 , x_return_status OUT NOCOPY VARCHAR2
1780 , x_field_name OUT NOCOPY VARCHAR2
1781
1782 ) IS
1783 BEGIN
1784 FOR i IN 2..p_disabled_list.COUNT
1785 LOOP
1786 IF p_disabled_list(i) = 'STOP_LOCATION_CODE' THEN
1787
1788 populate_external_efl(p_in_rec.STOP_LOCATION_ID,
1789 p_in_rec.stop_location_code,
1790 x_out_rec.STOP_LOCATION_ID,
1791 x_out_rec.stop_location_code);
1792
1793 ELSIF p_disabled_list(i) = 'STOP_SEQUENCE_NUMBER' THEN
1794 IF p_in_rec.STOP_SEQUENCE_NUMBER <> FND_API.G_MISS_NUM
1795 OR p_in_rec.STOP_SEQUENCE_NUMBER IS NULL THEN
1796 x_out_rec.STOP_SEQUENCE_NUMBER := p_in_rec.STOP_SEQUENCE_NUMBER;
1797 END IF;
1798 ELSIF p_disabled_list(i) = 'PLANNED_ARRIVAL_DATE' THEN
1799 IF p_in_rec.PLANNED_ARRIVAL_DATE <> FND_API.G_MISS_DATE
1800 OR p_in_rec.PLANNED_ARRIVAL_DATE IS NULL THEN
1801 x_out_rec.PLANNED_ARRIVAL_DATE := p_in_rec.PLANNED_ARRIVAL_DATE ;
1802 END IF;
1803 ELSIF p_disabled_list(i) = 'PLANNED_DEPARTURE_DATE' THEN
1804 IF p_in_rec.PLANNED_DEPARTURE_DATE <> FND_API.G_MISS_DATE
1805 OR p_in_rec.PLANNED_DEPARTURE_DATE IS NULL THEN
1806 x_out_rec.PLANNED_DEPARTURE_DATE := p_in_rec.PLANNED_DEPARTURE_DATE ;
1807 END IF;
1808 -- J-IB-NPARIKH-{
1809 ELSIF p_disabled_list(i) = 'DEPARTURE_GROSS_WEIGHT' THEN
1810 IF p_in_rec.DEPARTURE_GROSS_WEIGHT <> FND_API.G_MISS_NUM
1811 OR p_in_rec.DEPARTURE_GROSS_WEIGHT IS NULL THEN
1812 x_out_rec.DEPARTURE_GROSS_WEIGHT := p_in_rec.DEPARTURE_GROSS_WEIGHT ;
1813 END IF;
1814 ELSIF p_disabled_list(i) = 'DEPARTURE_NET_WEIGHT' THEN
1815 IF p_in_rec.DEPARTURE_NET_WEIGHT <> FND_API.G_MISS_NUM
1816 OR p_in_rec.DEPARTURE_NET_WEIGHT IS NULL THEN
1817 x_out_rec.DEPARTURE_NET_WEIGHT := p_in_rec.DEPARTURE_NET_WEIGHT ;
1818 END IF;
1819 ELSIF p_disabled_list(i) = 'WEIGHT_UOM_CODE' THEN
1820 populate_external_efl(p_in_rec.WEIGHT_UOM_CODE,
1821 p_in_rec.WEIGHT_UOM_DESC,
1822 NULL,
1823 x_out_rec.WEIGHT_UOM_CODE,
1824 x_out_rec.WEIGHT_UOM_DESC);
1825 ELSIF p_disabled_list(i) = 'DEPARTURE_VOLUME' THEN
1826 IF p_in_rec.DEPARTURE_VOLUME <> FND_API.G_MISS_NUM
1827 OR p_in_rec.DEPARTURE_VOLUME IS NULL THEN
1828 x_out_rec.DEPARTURE_VOLUME := p_in_rec.DEPARTURE_VOLUME ;
1829 END IF;
1830 ELSIF p_disabled_list(i) = 'VOLUME_UOM_CODE' THEN
1831 populate_external_efl(p_in_rec.VOLUME_UOM_CODE,
1832 p_in_rec.VOLUME_UOM_DESC,
1833 NULL,
1834 x_out_rec.VOLUME_UOM_CODE,
1835 x_out_rec.VOLUME_UOM_DESC);
1836 -- J-IB-NPARIKH-}
1837
1838 ELSIF p_disabled_list(i) = 'DESC_FLEX' THEN
1839 IF p_in_rec.attribute1 <> FND_API.G_MISS_CHAR
1840 OR p_in_rec.attribute1 IS NULL THEN
1841 x_out_rec.attribute1 := p_in_rec.attribute1 ;
1842 END IF;
1843 IF p_in_rec.attribute2 <> FND_API.G_MISS_CHAR
1844 OR p_in_rec.attribute2 IS NULL THEN
1845 x_out_rec.attribute2 := p_in_rec.attribute2 ;
1846 END IF;
1847 IF p_in_rec.attribute3 <> FND_API.G_MISS_CHAR
1848 OR p_in_rec.attribute3 IS NULL THEN
1849 x_out_rec.attribute3 := p_in_rec.attribute3 ;
1850 END IF;
1851 IF p_in_rec.attribute4 <> FND_API.G_MISS_CHAR
1852 OR p_in_rec.attribute4 IS NULL THEN
1853 x_out_rec.attribute4 := p_in_rec.attribute4 ;
1854 END IF;
1855 IF p_in_rec.attribute5 <> FND_API.G_MISS_CHAR
1856 OR p_in_rec.attribute5 IS NULL THEN
1857 x_out_rec.attribute5 := p_in_rec.attribute5 ;
1858 END IF;
1859 IF p_in_rec.attribute6 <> FND_API.G_MISS_CHAR
1860 OR p_in_rec.attribute6 IS NULL THEN
1861 x_out_rec.attribute6 := p_in_rec.attribute6 ;
1862 END IF;
1863 IF p_in_rec.attribute7 <> FND_API.G_MISS_CHAR
1864 OR p_in_rec.attribute7 IS NULL THEN
1865 x_out_rec.attribute7 := p_in_rec.attribute7 ;
1866 END IF;
1867 IF p_in_rec.attribute8 <> FND_API.G_MISS_CHAR
1868 OR p_in_rec.attribute8 IS NULL THEN
1869 x_out_rec.attribute8 := p_in_rec.attribute8 ;
1870 END IF;
1871 IF p_in_rec.attribute9 <> FND_API.G_MISS_CHAR
1872 OR p_in_rec.attribute9 IS NULL THEN
1873 x_out_rec.attribute9 := p_in_rec.attribute9 ;
1874 END IF;
1875 IF p_in_rec.attribute10 <> FND_API.G_MISS_CHAR
1876 OR p_in_rec.attribute10 IS NULL THEN
1877 x_out_rec.attribute10 := p_in_rec.attribute10 ;
1878 END IF;
1879 IF p_in_rec.attribute11 <> FND_API.G_MISS_CHAR
1880 OR p_in_rec.attribute11 IS NULL THEN
1881 x_out_rec.attribute11 := p_in_rec.attribute11 ;
1882 END IF;
1883 IF p_in_rec.attribute12 <> FND_API.G_MISS_CHAR
1884 OR p_in_rec.attribute12 IS NULL THEN
1885 x_out_rec.attribute12 := p_in_rec.attribute12 ;
1886 END IF;
1887 IF p_in_rec.attribute13 <> FND_API.G_MISS_CHAR
1888 OR p_in_rec.attribute13 IS NULL THEN
1889 x_out_rec.attribute13 := p_in_rec.attribute13 ;
1890 END IF;
1891 IF p_in_rec.attribute14 <> FND_API.G_MISS_CHAR
1892 OR p_in_rec.attribute14 IS NULL THEN
1893 x_out_rec.attribute14 := p_in_rec.attribute14 ;
1894 END IF;
1895 IF p_in_rec.attribute15 <> FND_API.G_MISS_CHAR
1896 OR p_in_rec.attribute15 IS NULL THEN
1897 x_out_rec.attribute15 := p_in_rec.attribute15 ;
1898 END IF;
1899 IF p_in_rec.attribute_category <> FND_API.G_MISS_CHAR
1900 OR p_in_rec.attribute_category IS NULL THEN
1901 x_out_rec.attribute_category := p_in_rec.attribute_category ;
1902 END IF;
1903 ELSIF p_disabled_list(i) = 'TP_FLEXFIELD' THEN
1904 IF p_in_rec.tp_attribute1 <> FND_API.G_MISS_CHAR
1905 OR p_in_rec.tp_attribute1 IS NULL THEN
1906 x_out_rec.tp_attribute1 := p_in_rec.tp_attribute1 ;
1907 END IF;
1908 IF p_in_rec.tp_attribute2 <> FND_API.G_MISS_CHAR
1909 OR p_in_rec.tp_attribute2 IS NULL THEN
1910 x_out_rec.tp_attribute2 := p_in_rec.tp_attribute2 ;
1911 END IF;
1912 IF p_in_rec.tp_attribute3 <> FND_API.G_MISS_CHAR
1913 OR p_in_rec.tp_attribute3 IS NULL THEN
1914 x_out_rec.tp_attribute3 := p_in_rec.tp_attribute3 ;
1915 END IF;
1916 IF p_in_rec.tp_attribute4 <> FND_API.G_MISS_CHAR
1917 OR p_in_rec.tp_attribute4 IS NULL THEN
1918 x_out_rec.tp_attribute4 := p_in_rec.tp_attribute4 ;
1919 END IF;
1920 IF p_in_rec.tp_attribute5 <> FND_API.G_MISS_CHAR
1921 OR p_in_rec.tp_attribute5 IS NULL THEN
1922 x_out_rec.tp_attribute5 := p_in_rec.tp_attribute5 ;
1923 END IF;
1924 IF p_in_rec.tp_attribute6 <> FND_API.G_MISS_CHAR
1925 OR p_in_rec.tp_attribute6 IS NULL THEN
1926 x_out_rec.tp_attribute6 := p_in_rec.tp_attribute6 ;
1927 END IF;
1928 IF p_in_rec.tp_attribute7 <> FND_API.G_MISS_CHAR
1929 OR p_in_rec.tp_attribute7 IS NULL THEN
1930 x_out_rec.tp_attribute7 := p_in_rec.tp_attribute7 ;
1931 END IF;
1932 IF p_in_rec.tp_attribute8 <> FND_API.G_MISS_CHAR
1933 OR p_in_rec.tp_attribute8 IS NULL THEN
1934 x_out_rec.tp_attribute8 := p_in_rec.tp_attribute8 ;
1935 END IF;
1936 IF p_in_rec.tp_attribute9 <> FND_API.G_MISS_CHAR
1937 OR p_in_rec.tp_attribute9 IS NULL THEN
1938 x_out_rec.tp_attribute9 := p_in_rec.tp_attribute9 ;
1939 END IF;
1940 IF p_in_rec.tp_attribute10 <> FND_API.G_MISS_CHAR
1941 OR p_in_rec.tp_attribute10 IS NULL THEN
1942 x_out_rec.tp_attribute10 := p_in_rec.tp_attribute10 ;
1943 END IF;
1944 IF p_in_rec.tp_attribute11 <> FND_API.G_MISS_CHAR
1945 OR p_in_rec.tp_attribute11 IS NULL THEN
1946 x_out_rec.tp_attribute11 := p_in_rec.tp_attribute11 ;
1947 END IF;
1948 IF p_in_rec.tp_attribute12 <> FND_API.G_MISS_CHAR
1949 OR p_in_rec.tp_attribute12 IS NULL THEN
1950 x_out_rec.tp_attribute12 := p_in_rec.tp_attribute12 ;
1951 END IF;
1952 IF p_in_rec.tp_attribute13 <> FND_API.G_MISS_CHAR
1953 OR p_in_rec.tp_attribute13 IS NULL THEN
1954 x_out_rec.tp_attribute13 := p_in_rec.tp_attribute13 ;
1955 END IF;
1956 IF p_in_rec.tp_attribute14 <> FND_API.G_MISS_CHAR
1957 OR p_in_rec.tp_attribute14 IS NULL THEN
1958 x_out_rec.tp_attribute14 := p_in_rec.tp_attribute14 ;
1959 END IF;
1960 IF p_in_rec.tp_attribute15 <> FND_API.G_MISS_CHAR
1961 OR p_in_rec.tp_attribute15 IS NULL THEN
1962 x_out_rec.tp_attribute15 := p_in_rec.tp_attribute15 ;
1963 END IF;
1964 IF p_in_rec.tp_attribute_category <> FND_API.G_MISS_CHAR
1965 OR p_in_rec.tp_attribute_category IS NULL THEN
1966 x_out_rec.tp_attribute_category := p_in_rec.tp_attribute_category ;
1967 END IF;
1968 --ELSIF p_disabled_list(i) = 'FULL' THEN
1969 --NULL;
1970 ELSE
1971 -- invalid name
1972 x_field_name := p_disabled_list(i);
1973 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1974 RETURN;
1975 --
1976 END IF;
1977 END LOOP;
1978 END enable_from_list;
1979
1980 --
1981 -- Bug 2678363 - Added p_in_rec as a parameter instead of p_action
1982 --
1983 PROCEDURE Get_Disabled_List (
1984 p_stop_rec IN WSH_TRIP_STOPS_PVT.trip_stop_rec_type
1985 , p_parent_entity_id IN NUMBER
1986 , p_in_rec IN WSH_TRIP_STOPS_GRP.stopInRecType
1987 , x_return_status OUT NOCOPY VARCHAR2
1988 , x_msg_count OUT NOCOPY NUMBER
1989 , x_msg_data OUT NOCOPY VARCHAR2
1990 , x_stop_rec OUT NOCOPY WSH_TRIP_STOPS_PVT.trip_stop_rec_type
1991 )
1992 IS
1993 l_disabled_list WSH_UTIL_CORE.column_tab_type;
1994 l_db_col_rec WSH_TRIP_STOPS_PVT.trip_stop_rec_type;
1995 l_return_status VARCHAR2(30);
1996 l_field_name VARCHAR2(100);
1997 l_parent_entity_id NUMBER;
1998 l_debug_on BOOLEAN;
1999 l_module_name CONSTANT VARCHAR2(100) :=
2000 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DISABLED_LIST';
2001
2002 CURSOR get_trip_id(p_stop_id NUMBER) IS
2003 SELECT trip_id
2004 FROM wsh_trip_stops
2005 WHERE stop_id = p_stop_rec.STOP_ID;
2006
2007 CURSOR c_tbl_rec IS
2008 SELECT STOP_ID
2009 ,TRIP_ID
2010 ,STOP_LOCATION_ID
2011 ,STATUS_CODE
2012 ,STOP_SEQUENCE_NUMBER
2013 ,PLANNED_ARRIVAL_DATE
2014 ,PLANNED_DEPARTURE_DATE
2015 ,ACTUAL_ARRIVAL_DATE
2016 ,ACTUAL_DEPARTURE_DATE
2017 ,DEPARTURE_GROSS_WEIGHT
2018 ,DEPARTURE_NET_WEIGHT
2019 ,WEIGHT_UOM_CODE
2020 ,DEPARTURE_VOLUME
2021 ,VOLUME_UOM_CODE
2022 ,DEPARTURE_SEAL_CODE
2023 ,DEPARTURE_FILL_PERCENT
2024 ,TP_ATTRIBUTE_CATEGORY
2025 ,TP_ATTRIBUTE1
2026 ,TP_ATTRIBUTE2
2027 ,TP_ATTRIBUTE3
2028 ,TP_ATTRIBUTE4
2029 ,TP_ATTRIBUTE5
2030 ,TP_ATTRIBUTE6
2031 ,TP_ATTRIBUTE7
2032 ,TP_ATTRIBUTE8
2033 ,TP_ATTRIBUTE9
2034 ,TP_ATTRIBUTE10
2035 ,TP_ATTRIBUTE11
2036 ,TP_ATTRIBUTE12
2037 ,TP_ATTRIBUTE13
2038 ,TP_ATTRIBUTE14
2039 ,TP_ATTRIBUTE15
2040 ,ATTRIBUTE_CATEGORY
2041 ,ATTRIBUTE1
2042 ,ATTRIBUTE2
2043 ,ATTRIBUTE3
2044 ,ATTRIBUTE4
2045 ,ATTRIBUTE5
2046 ,ATTRIBUTE6
2047 ,ATTRIBUTE7
2048 ,ATTRIBUTE8
2049 ,ATTRIBUTE9
2050 ,ATTRIBUTE10
2051 ,ATTRIBUTE11
2052 ,ATTRIBUTE12
2053 ,ATTRIBUTE13
2054 ,ATTRIBUTE14
2055 ,ATTRIBUTE15
2056 ,CREATION_DATE
2057 ,CREATED_BY
2058 ,sysdate
2059 ,FND_GLOBAL.USER_ID
2060 ,FND_GLOBAL.LOGIN_ID
2061 ,PROGRAM_APPLICATION_ID
2062 ,PROGRAM_ID
2063 ,PROGRAM_UPDATE_DATE
2064 ,REQUEST_ID
2065 ,WSH_LOCATION_ID
2066 ,TRACKING_DRILLDOWN_FLAG
2067 ,TRACKING_REMARKS
2068 ,CARRIER_EST_DEPARTURE_DATE
2069 ,CARRIER_EST_ARRIVAL_DATE
2070 ,LOADING_START_DATETIME
2071 ,LOADING_END_DATETIME
2072 ,UNLOADING_START_DATETIME
2073 ,UNLOADING_END_DATETIME
2074 ,p_stop_rec.ROWID
2075 ,p_stop_rec.TRIP_NAME
2076 ,p_stop_rec.STOP_LOCATION_CODE
2077 ,p_stop_rec.WEIGHT_UOM_DESC
2078 ,p_stop_rec.VOLUME_UOM_DESC
2079 ,p_stop_rec.LOCK_STOP_ID
2080 ,p_stop_rec.PENDING_INTERFACE_FLAG
2081 ,p_stop_rec.TRANSACTION_HEADER_ID
2082 /*J inbound logistics jckwok */
2083 ,nvl(SHIPMENTS_TYPE_FLAG, 'O') SHIPMENTS_TYPE_FLAG
2084 -- J: W/V Changes
2085 ,WV_FROZEN_FLAG
2086 /* J TL/TP ttrichy */
2087 , wkend_layover_stops
2088 , wkday_layover_stops
2089 , tp_stop_id
2090 , physical_stop_id
2091 , physical_location_id
2092 , TMS_INTERFACE_FLAG -- OTM R12, glog proj
2093 FROM wsh_trip_stops
2094 WHERE stop_id = p_stop_rec.STOP_ID;
2095
2096 e_dp_no_entity EXCEPTION;
2097 e_bad_field EXCEPTION;
2098 e_all_disabled EXCEPTION ;
2099 --
2100 l_caller VARCHAR2(32767);
2101 --
2102 i NUMBER;
2103
2104 BEGIN
2105 --
2106 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2107 --
2108 IF l_debug_on IS NULL
2109 THEN
2110 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2111 END IF;
2112 --
2113 IF l_debug_on THEN
2114 --
2115 WSH_DEBUG_SV.push(l_module_name);
2116 WSH_DEBUG_SV.log(l_module_name,'stop_id', p_stop_rec.stop_id);
2117 WSH_DEBUG_SV.log(l_module_name,'p_parent_entity_id', p_parent_entity_id);
2118 WSH_DEBUG_SV.log(l_module_name, 'Action', p_in_rec.action_code);
2119 WSH_DEBUG_SV.log(l_module_name, 'Caller', p_in_rec.caller);
2120 --
2121 END IF;
2122 --
2123 x_return_status := FND_API.G_RET_STS_SUCCESS;
2124 --
2125 IF p_in_rec.action_code = 'CREATE' THEN
2126 --
2127 IF l_debug_on THEN
2128 WSH_DEBUG_SV.log(l_module_name,'calling eliminate_displayonly_fields');
2129 END IF;
2130 --
2131 -- nothing else need to be disabled
2132 --
2133 eliminate_displayonly_fields (p_stop_rec,p_in_rec, x_stop_rec);
2134 --
2135 IF (p_stop_rec.trip_name <> FND_API.G_MISS_CHAR) THEN
2136 x_stop_rec.trip_name := p_stop_rec.trip_name;
2137 END IF;
2138 --
2139 IF (p_parent_entity_id <> FND_API.G_MISS_NUM) THEN
2140 x_stop_rec.trip_id := p_parent_entity_id;
2141 END IF;
2142 --
2143 --3509004: public api changes, commented the following code.
2144 /* IF l_debug_on THEN
2145 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
2146 WSH_DEBUG_SV.pop(l_module_name);
2147 END IF;
2148 --
2149 RETURN;
2150 -- */
2151 ELSIF p_in_rec.action_code = 'UPDATE' THEN
2152 --
2153 l_parent_entity_id := p_parent_entity_id;
2154 --
2155 IF (l_parent_entity_id IS NULL ) OR l_parent_entity_id = FND_API.G_MISS_NUM THEN
2156 --
2157 OPEN get_trip_id(p_stop_rec.stop_id);
2158 FETCH get_trip_id INTO l_parent_entity_id;
2159 --
2160 IF l_debug_on THEN
2161 WSH_DEBUG_SV.log(l_module_name,'trip_id',l_parent_entity_id);
2162 END IF;
2163 --
2164 IF (get_trip_id%NOTFOUND OR l_parent_entity_id IS NULL) THEN
2165 CLOSE get_trip_id;
2166 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2167 END IF;
2168 --
2169 CLOSE get_trip_id;
2170 --
2171 END IF;
2172 --
2173 l_caller := p_in_rec.caller;
2174 IF (l_caller like 'FTE%' AND l_caller <> 'FTE_TMS_INTEGRATION') THEN
2175 l_caller := 'WSH_PUB';
2176 END IF;
2177 WSH_TRIP_STOPS_PVT.Get_Disabled_List( p_stop_rec.stop_id
2178 ,l_parent_entity_id
2179 , 'FORM'
2180 , x_return_status
2181 , l_disabled_list
2182 , x_msg_count
2183 , x_msg_data
2184 , l_caller --3509004:public api changes
2185 );
2186 --
2187 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR OR
2188 x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
2189 THEN
2190 --
2191 IF l_debug_on THEN
2192 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
2193 WSH_DEBUG_SV.pop(l_module_name);
2194 END IF;
2195 --
2196 RETURN;
2197 --
2198 END IF;
2199 --
2200 IF l_disabled_list.COUNT = 1 THEN
2201 IF l_disabled_list(1) = 'FULL' THEN
2202 RAISE e_all_disabled;
2203 --Everything is disabled
2204 END IF;
2205 END IF;
2206 --
2207 OPEN c_tbl_rec;
2208 FETCH c_tbl_rec INTO x_stop_rec;
2209 IF c_tbl_rec%NOTFOUND THEN
2210 --
2211 CLOSE c_tbl_rec;
2212 RAISE e_dp_no_entity;
2213 --
2214 END IF;
2215 CLOSE c_tbl_rec;
2216
2217 --
2218 IF l_debug_on THEN
2219 WSH_DEBUG_SV.log(l_module_name,'list.COUNT',l_disabled_list.COUNT);
2220 END IF;
2221 --
2222 IF l_disabled_list.COUNT = 0 THEN
2223 IF l_debug_on THEN
2224 WSH_DEBUG_SV.log(l_module_name,'calling eliminate_displayonly_fields');
2225 END IF;
2226 --
2227 -- nothing else need to be disabled
2228 --
2229 eliminate_displayonly_fields (p_stop_rec,p_in_rec,x_stop_rec);
2230
2231 ELSIF l_disabled_list(1) = 'FULL' THEN
2232 --
2233 IF l_disabled_list.COUNT > 1 THEN
2234 --
2235 IF l_debug_on THEN
2236 FOR i in 1..l_disabled_list.COUNT
2237 LOOP
2238 WSH_DEBUG_SV.log(l_module_name,'list values',l_disabled_list(i));
2239 END LOOP;
2240 WSH_DEBUG_SV.log(l_module_name,'calling enable_from_list');
2241 END IF;
2242 --enable the columns matching the l_disabled_list
2243 enable_from_list(l_disabled_list,
2244 p_stop_rec,
2245 x_stop_rec,
2246 l_return_status,
2247 l_field_name);
2248 --
2249 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
2250 RAISE e_bad_field;
2251 END IF;
2252 --
2253 END IF;
2254 --
2255 ELSE -- list.count > 1 and list(1) <> 'FULL'
2256 --
2257 l_db_col_rec := x_stop_rec ;
2258 --
2259 IF l_debug_on THEN
2260 FOR i in 1..l_disabled_list.COUNT
2261 LOOP
2262 WSH_DEBUG_SV.log(l_module_name,'list values',l_disabled_list(i));
2263 END LOOP;
2264 WSH_DEBUG_SV.log(l_module_name,'First element is not FULL');
2265 WSH_DEBUG_SV.log(l_module_name,'calling eliminate_displayonly_fields');
2266 END IF;
2267 --
2268 eliminate_displayonly_fields (p_stop_rec,p_in_rec,x_stop_rec);
2269 --
2270 IF l_debug_on THEN
2271 WSH_DEBUG_SV.log(l_module_name,'calling disable_from_list');
2272 END IF;
2273 -- The fileds in the list are getting disabled
2274 disable_from_list(l_disabled_list,
2275 l_db_col_rec,
2276 x_stop_rec,
2277 l_return_status,
2278 l_field_name
2279 );
2280 --
2281 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
2282 RAISE e_bad_field;
2283 END IF;
2284 --
2285 END IF;
2286 --
2287 END IF; /* if action = 'UPDATE' */
2288 --
2289 --3509004:public api changes
2290 --bug 3613650
2291 IF (nvl(p_in_rec.caller,'''') <> 'WSH_FSTRX' AND
2292 nvl(p_in_rec.caller,'''') NOT LIKE 'FTE%') THEN
2293 --
2294 user_non_updatable_columns
2295 (p_user_in_rec => p_stop_rec,
2296 p_out_rec => x_stop_rec,
2297 p_in_rec => p_in_rec,
2298 x_return_status => l_return_status);
2299 --
2300 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
2301 x_return_status := l_return_status;
2302 END IF;
2303 --
2304 END IF;
2305
2306 IF l_debug_on THEN
2307 WSH_DEBUG_SV.pop(l_module_name);
2308 END IF;
2309
2310 EXCEPTION
2311 WHEN e_all_disabled THEN
2312 -- OTM R12, glog proj, close open cursors
2313 IF get_trip_id%ISOPEN THEN
2314 CLOSE get_trip_id;
2315 END IF;
2316 IF c_tbl_rec%ISOPEN THEN
2317 CLOSE c_tbl_rec;
2318 END IF;
2319 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2320 FND_MESSAGE.SET_NAME('WSH','WSH_ALL_COLS_DISABLED');
2321 FND_MESSAGE.Set_Token('ENTITY_ID',p_stop_rec.stop_id);
2322 wsh_util_core.add_message(x_return_status,l_module_name);
2323 IF l_debug_on THEN
2324 -- Nothing is updateable
2325 WSH_DEBUG_SV.pop(l_module_name,'e_all_disabled');
2326 END IF;
2327 WHEN e_dp_no_entity THEN
2328 -- OTM R12, glog proj, close open cursors
2329 IF get_trip_id%ISOPEN THEN
2330 CLOSE get_trip_id;
2331 END IF;
2332 IF c_tbl_rec%ISOPEN THEN
2333 CLOSE c_tbl_rec;
2334 END IF;
2335 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2336 -- the message for this is set in original get_disabled_list
2337 IF l_debug_on THEN
2338 WSH_DEBUG_SV.pop(l_module_name,'e_dp_no_entity');
2339 END IF;
2340 WHEN e_bad_field THEN
2341 -- OTM R12, glog proj, close open cursors
2342 IF get_trip_id%ISOPEN THEN
2343 CLOSE get_trip_id;
2344 END IF;
2345 IF c_tbl_rec%ISOPEN THEN
2346 CLOSE c_tbl_rec;
2347 END IF;
2348 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2349 FND_MESSAGE.SET_NAME('WSH','WSH_BAD_FIELD_NAME');
2350 FND_MESSAGE.Set_Token('FIELD_NAME',l_field_name);
2351 wsh_util_core.add_message(x_return_status,l_module_name);
2352 IF l_debug_on THEN
2353 WSH_DEBUG_SV.log(l_module_name,'Bad field name passed to the list:'
2354 ,l_field_name);
2355 WSH_DEBUG_SV.pop(l_module_name,'e_bad_field');
2356 END IF;
2357
2358 WHEN OTHERS THEN
2359 -- OTM R12, glog proj, close open cursors
2360 IF get_trip_id%ISOPEN THEN
2361 CLOSE get_trip_id;
2362 END IF;
2363 IF c_tbl_rec%ISOPEN THEN
2364 CLOSE c_tbl_rec;
2365 END IF;
2366 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2367 wsh_util_core.default_handler('WSH_TRIP_STOPS_VALIDATIONS.get_disabled_list', l_module_name);
2368 IF l_debug_on THEN
2369 WSH_DEBUG_SV.log(l_module_name,'Error:',SUBSTR(SQLERRM,1,200));
2370 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2371 END IF;
2372 END Get_Disabled_List;
2373
2374
2375
2376
2377 PROCEDURE Init_Stop_Actions_Tbl (
2378 p_action IN VARCHAR2
2379 , x_stop_actions_tab OUT NOCOPY StopActionsTabType
2380 , x_return_status OUT NOCOPY VARCHAR2
2381 )
2382
2383 IS
2384 l_debug_on BOOLEAN;
2385 l_module_name CONSTANT VARCHAR2(100) :=
2386 'wsh.plsql.' || G_PKG_NAME || '.' || 'Init_Stop_Actions_Tbl';
2387 i number := 0;
2388 BEGIN
2389 --
2390 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2391 --
2392 IF l_debug_on IS NULL
2393 THEN
2394 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2395 END IF;
2396 --
2397 IF l_debug_on THEN
2398 WSH_DEBUG_SV.push(l_module_name);
2399 --
2400 WSH_DEBUG_SV.log(l_module_name,'p_action', p_action);
2401 END IF;
2402 --
2403 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2404
2405 --
2406 -- J-IB-NPARIKH-{
2407 --
2408 -- Disable all actions on inbound stops when called from transactions form
2409 --
2410 i := i+1;
2411 x_stop_actions_tab(i).shipments_type_flag := 'I';
2412 x_stop_actions_tab(i).caller := 'WSH_FSTRX';
2413 x_stop_actions_tab(i).action_not_allowed := p_action;
2414 -- J-IB-NPARIKH-}
2415 --
2416
2417 --Replaced 'ARRIVE' and 'CLOSED' with 'UPDATE-STATUS' for bug 2748983
2418
2419 IF p_action IN ('PLAN','UNPLAN','UPDATE-STATUS','PICK-RELEASE',
2420 'PICK-RELEASE-UI','DELETE')
2421 THEN
2422 i := i+1;
2423 x_stop_actions_tab(i).status_code := 'CL';
2424 x_stop_actions_tab(i).action_not_allowed := p_action;
2425 /* J inbound logistics jckwok */
2426 IF p_action IN ('PICK-RELEASE',
2427 'PICK-RELEASE-UI')
2428 --removed 'UPDATE-STATUS' for -- J-IB-NPARIKH
2429 THEN
2430 i := i + 1;
2431 x_stop_actions_tab(i).shipments_type_flag := 'I';
2432 x_stop_actions_tab(i).action_not_allowed := p_action;
2433 END IF;
2434
2435 --HVOP heali
2436 IF p_action IN ('PICK-RELEASE','PICK-RELEASE-UI') THEN
2437 i := i + 1;
2438 x_stop_actions_tab(i).status_code := 'AR';
2439 x_stop_actions_tab(i).action_not_allowed := p_action;
2440 END IF;
2441 --HVOP heali
2442 --J-IB-JCKWOK
2443 ELSIF p_action IN ('ASSIGN-FREIGHT-COSTS','PRINT-DOC-SETS','RESOLVE-EXCEPTIONS-UI') THEN
2444 i := i + 1;
2445 x_stop_actions_tab(i).action_not_allowed := p_action;
2446 x_stop_actions_tab(i).shipments_type_flag := 'I';
2447 --J-IB-JCKWOK
2448 -- J-IB-NPARIKH-{
2449 ELSIF p_action = 'WT-VOL'
2450 THEN
2451 --
2452 -- Calculate weight/volume action is
2453 -- - always allowed for inbound stop
2454 -- - not allowed for outbound stop, once closed.
2455 -- - not allowed for mixed closed stop, if called from transactions form
2456 --
2457 i := i + 1;
2458 x_stop_actions_tab(i).action_not_allowed := p_action;
2459 x_stop_actions_tab(i).shipments_type_flag := 'O'; -- J-IB-NPARIKH
2460 x_stop_actions_tab(i).status_code := 'CL';
2461 i := i + 1;
2462 x_stop_actions_tab(i).action_not_allowed := p_action;
2463 x_stop_actions_tab(i).shipments_type_flag := 'M';
2464 x_stop_actions_tab(i).caller := 'WSH_FSTRX';
2465 x_stop_actions_tab(i).status_code := 'CL';
2466 END IF;
2467 -- J-IB-NPARIKH-}
2468
2469 IF l_debug_on THEN
2470 WSH_DEBUG_SV.pop(l_module_name);
2471 END IF;
2472 EXCEPTION
2473 WHEN OTHERS THEN
2474 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2475 wsh_util_core.default_handler('WSH_TRIP_STOPS_VALIDATIONS.get_disabled_list', l_module_name);
2476 IF l_debug_on THEN
2477 WSH_DEBUG_SV.log(l_module_name,'Error:',SUBSTR(SQLERRM,1,200));
2478 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2479 END IF;
2480
2481 END Init_Stop_Actions_Tbl;
2482
2483 -- J-IB-NPARIKH-{
2484 --
2485 --========================================================================
2486 -- PROCEDURE : refreshShipmentsTypeFlag
2487 --
2488 -- PARAMETERS: p_trip_id Trip ID (Required)
2489 -- p_stop_id Stop ID (Required)
2490 -- p_action ASSIGN/UNASSIGN
2491 -- p_shipment_direction Direction of delivery being assigned/unassigned
2492 -- x_shipments_type_flag Shipments type flag for stop
2493 -- x_return_status Return status of API
2494 --
2495 -- ASSUMPTION: Caller passes x_shipments_type_flag with database(current) value
2496 -- of the flag for trip stop.
2497 --
2498 -- PRE-REQ : This procedure should be called BEFORE performing the ASSIGNment operation.
2499 -- This procedure should be called AFTER performing the UNASSIGNment operation.
2500 --
2501 -- COMMENT : This function re-calculates value of shipments type flag for the
2502 -- trip stop as a result of a delivery being assigned/unassigned.
2503 --
2504 -- For action ASSIGN
2505 -- - If current value of shipments type flag is I
2506 -- - If delivery being assigned is outbound (O/IO)
2507 -- - Check if stop has any inbound delivery
2508 -- - If yes, set x_shipments_type_flag to M
2509 -- - If no, set x_shipments_type_flag to O
2510 -- - If delivery being assigned is inbound (not O/IO)
2511 -- - re-calculation is not required. Return
2512 --
2513 -- - If current value of shipments type flag is O
2514 -- - If delivery being assigned is inbound (not O/IO)
2515 -- - Check if stop has any outbound delivery
2516 -- - If yes, set x_shipments_type_flag to M
2517 -- - If no, set x_shipments_type_flag to I
2518 -- - If delivery being assigned is outbound (O/IO)
2519 -- - re-calculation is not required. Return
2520 --
2521 -- - If current value of shipments type flag is M
2522 -- - re-calculation is not required. Return
2523 --
2524 -- For action UNASSIGN
2525 -- - If current value of shipments type flag is M
2526 -- - If delivery being unassigned is outbound (O/IO)
2527 -- - Check if stop has any outbound delivery
2528 -- - If no, set x_shipments_type_flag to I
2529 -- - If yes, re-calculation is not required. Return
2530 -- - If delivery being assigned is inbound (not O/IO)
2531 -- - Check if stop has any inbound delivery
2532 -- - If no, set x_shipments_type_flag to O
2533 -- - If yes, re-calculation is not required. Return
2534 --
2535 -- - If current value of shipments type flag is O/I
2536 -- - re-calculation is not required. Return
2537 --
2538 --========================================================================
2539 --
2540 PROCEDURE refreshShipmentsTypeFlag
2541 (
2542 p_trip_id IN NUMBER,
2543 p_stop_id IN NUMBER,
2544 p_action IN VARCHAR2 DEFAULT 'ASSIGN',
2545 p_shipment_direction IN VARCHAR2 DEFAULT 'O' ,
2546 x_shipments_type_flag IN OUT NOCOPY VARCHAR2,
2547 x_return_status OUT NOCOPY VARCHAR2
2548 )
2549 IS
2550 --{
2551 --
2552 l_debug_on BOOLEAN;
2553 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'refreshShipmentsTypeFlag';
2554 --}
2555 BEGIN
2556 --{
2557 --
2558 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2559 --
2560 IF l_debug_on IS NULL
2561 THEN
2562 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2563 END IF;
2564 --
2565 IF l_debug_on THEN
2566 WSH_DEBUG_SV.push(l_module_name);
2567 WSH_DEBUG_SV.log(l_module_name,'p_trip_id',p_trip_id);
2568 WSH_DEBUG_SV.log(l_module_name,'p_stop_id',p_stop_id);
2569 WSH_DEBUG_SV.log(l_module_name,'p_action',p_action);
2570 WSH_DEBUG_SV.log(l_module_name,'p_shipment_direction',p_shipment_direction);
2571 WSH_DEBUG_SV.log(l_module_name,'x_shipments_type_flag',x_shipments_type_flag);
2572 END IF;
2573 --
2574 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2575 --
2576 IF p_trip_id IS NULL
2577 THEN
2578 --{
2579 --
2580 -- Trip ID is a required parameter. Raise error.
2581 --
2582 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
2583 FND_MESSAGE.SET_TOKEN('FIELD_NAME','p_trip_id');
2584 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
2585 RAISE FND_API.G_EXC_ERROR;
2586 --}
2587 END IF;
2588 --
2589 IF p_stop_id IS NULL
2590 THEN
2591 --{
2592 --
2593 -- Stop ID is a required parameter. Raise error.
2594 --
2595 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
2596 FND_MESSAGE.SET_TOKEN('FIELD_NAME','p_stop_id');
2597 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
2598 RAISE FND_API.G_EXC_ERROR;
2599 --}
2600 END IF;
2601 --
2602 --
2603 IF p_action = 'ASSIGN'
2604 THEN
2605 --{
2606 IF NVL(x_shipments_type_flag,'O') = 'I'
2607 THEN
2608 --{
2609 IF NVL(p_shipment_direction,'O') IN ('O','IO')
2610 THEN
2611 --{
2612 IF l_debug_on THEN
2613 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_VALIDATIONS.has_inbound_deliveries',WSH_DEBUG_SV.C_PROC_LEVEL);
2614 END IF;
2615 --
2616 IF
2617 WSH_TRIP_VALIDATIONS.has_inbound_deliveries
2618 (
2619 p_trip_id => p_trip_id,
2620 p_stop_id => p_stop_id
2621 )
2622 = 'Y'
2623 THEN
2624 x_shipments_type_flag := 'M';
2625 ELSE
2626 x_shipments_type_flag := 'O';
2627 END IF;
2628 --}
2629 END IF;
2630 --}
2631 ELSIF NVL(x_shipments_type_flag,'O') = 'O'
2632 THEN
2633 --{
2634 IF NVL(p_shipment_direction,'O') NOT IN ('O','IO')
2635 THEN
2636 --{
2637 IF l_debug_on THEN
2638 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_VALIDATIONS.has_outbound_deliveries',WSH_DEBUG_SV.C_PROC_LEVEL);
2639 END IF;
2640 --
2641 IF
2642 WSH_TRIP_VALIDATIONS.has_outbound_deliveries
2643 (
2644 p_trip_id => p_trip_id,
2645 p_stop_id => p_stop_id
2646 )
2647 = 'Y'
2648 THEN
2649 x_shipments_type_flag := 'M';
2650 ELSE
2651 x_shipments_type_flag := 'I';
2652 END IF;
2653 --}
2654 END IF;
2655 --}
2656 END IF;
2657 --}
2658 ELSIF p_action = 'UNASSIGN'
2659 THEN
2660 --{
2661 IF NVL(x_shipments_type_flag,'O') = 'M'
2662 THEN
2663 --{
2664 IF NVL(p_shipment_direction,'O') IN ('O','IO')
2665 THEN
2666 --{
2667 IF l_debug_on THEN
2668 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_VALIDATIONS.has_outbound_deliveries',WSH_DEBUG_SV.C_PROC_LEVEL);
2669 END IF;
2670 --
2671 IF
2672 WSH_TRIP_VALIDATIONS.has_outbound_deliveries
2673 (
2674 p_trip_id => p_trip_id,
2675 p_stop_id => p_stop_id
2676 )
2677 = 'Y'
2678 THEN
2679 x_shipments_type_flag := 'M';
2680 ELSE
2681 x_shipments_type_flag := 'I';
2682 END IF;
2683 --}
2684 ELSE
2685 --{
2686 IF l_debug_on THEN
2687 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_VALIDATIONS.has_inbound_deliveries',WSH_DEBUG_SV.C_PROC_LEVEL);
2688 END IF;
2689 --
2690 IF
2691 WSH_TRIP_VALIDATIONS.has_inbound_deliveries
2692 (
2693 p_trip_id => p_trip_id,
2694 p_stop_id => p_stop_id
2695 )
2696 = 'Y'
2697 THEN
2698 x_shipments_type_flag := 'M';
2699 ELSE
2700 x_shipments_type_flag := 'O';
2701 END IF;
2702 --}
2703 END IF;
2704 --}
2705 END IF;
2706 --}
2707 END IF;
2708 --
2709 --
2710 IF l_debug_on THEN
2711 WSH_DEBUG_SV.log(l_module_name,'x_shipments_type_flag',x_shipments_type_flag);
2712 WSH_DEBUG_SV.pop(l_module_name);
2713 END IF;
2714 --}
2715 EXCEPTION
2716 --{
2717 --
2718 WHEN FND_API.G_EXC_ERROR THEN
2719
2720 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2721 --
2722 IF l_debug_on THEN
2723 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2724 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2725 END IF;
2726 --
2727 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2728
2729 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2730 --
2731 IF l_debug_on THEN
2732 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2733 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2734 END IF;
2735 --
2736 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
2737 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2738 --
2739 IF l_debug_on THEN
2740 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2741 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2742 END IF;
2743 WHEN OTHERS THEN
2744 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2745 wsh_util_core.default_handler('WSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag', l_module_name);
2746 IF l_debug_on THEN
2747 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2748 END IF;
2749 --}
2750 END refreshShipmentsTypeFlag;
2751 --
2752 --
2753 --
2754 --========================================================================
2755 -- PROCEDURE : Check_Stop_Close
2756 --
2757 -- PARAMETERS: p_in_rec Input Record (Refer to WSHSTVLS.pls for description)
2758 -- p_out_rec Output record (Refer to WSHSTVLS.pls for description)
2759 -- x_return_status Return status of API
2760 --
2761 --
2762 -- COMMENT : This function checks if stop can be closed or not.
2763 -- Result is indicated via out parameter p_out_rec.close_allowed
2764 --
2765 -- When FTE or Inbound caller wants to close the physical stop, we will
2766 -- perform validation on both linked dummy and physical stops, in which
2767 -- case, both stops are treated as one virtual stop.
2768 --
2769 -- In below steps, linked internal stops are included(+).
2770 --
2771 -- It performs following steps:
2772 -- 01. To determine, if stop can be closed,
2773 -- 01.01. It checks status of all prior stops on same trip.(+)
2774 -- 01.02. It checks status of deliveries being picked up/droppped of at the stop.(+)
2775 -- 01.03. It checks status of all prior legs for the deliveries.(+)
2776 -- 02. Validate stop close date as per shipping parameter "Allow future date"
2777 -- 03. Validate stop close date against inventory open periods
2778 -- 04. Check if deliveries (Starting from this stop) can be set to in-transit.
2779 -- (Internal stops cannot be used as pick up.)
2780 -- 05. Check if deliveries (Ending at this stop) can be closed.(+)
2781 -- 06. Check if trip can be set to in-transit or closed.
2782 --
2783 --========================================================================
2784 --
2785 PROCEDURE Check_Stop_Close -----trvlb
2786 (
2787 p_in_rec IN chkClose_in_rec_type,
2788 x_out_rec OUT NOCOPY chkClose_out_rec_type,
2789 x_return_status OUT NOCOPY VARCHAR2
2790 )
2791 IS
2792 --{
2793
2794 l_dummy NUMBER;
2795 --
2796 -- Get trip and stop information
2797 --
2798 CURSOR stop_info_csr (p_stop_id IN NUMBER) IS
2799 SELECT stop_sequence_number,
2800 wts.status_code,
2801 wts.trip_id,
2802 nvl(wts.shipments_type_flag,'O') shipments_Type_flag,
2803 stop_location_id,
2804 wt.ship_method_code,
2805 wt.carrier_id,
2806 wt.mode_of_transport,
2807 wt.service_level,
2808 wt.status_code trip_status_code,
2809 wt.seal_code trip_seal_Code,
2810 wt.name trip_name,
2811 -- J: W/V Changes
2812 wts.departure_gross_weight,
2813 wts.departure_volume,
2814 wts.physical_location_id
2815 FROM wsh_trip_stops wts,
2816 wsh_trips wt
2817 WHERE stop_id = p_stop_id
2818 AND wts.trip_id = wt.trip_id;
2819 --
2820 --
2821 l_stop_info_rec stop_info_csr%ROWTYPE;
2822 --
2823 --
2824 -- Get linked internal stop information
2825 -- If the linked stop is already closed,
2826 -- we do not need to validate it anymore.
2827 -- If the caller passes the dummy stop, we will not use the physical
2828 -- stop.
2829 -- Linked internal stop can be closed separately;
2830 -- if it is already closed, we do not need to validate it.
2831 --
2832 -- FTE is expected to pass only the physical stop,
2833 -- and Inbound can pass either dummy stop (corner-case) or physical stop (typical).
2834 --
2835 CURSOR linked_stop_info_csr (p_stop_id IN NUMBER,
2836 p_trip_id IN NUMBER) IS
2837 SELECT wts.stop_id,
2838 wts.stop_sequence_number,
2839 wts.status_code,
2840 nvl(wts.shipments_type_flag,'O') shipments_Type_flag,
2841 wts.stop_location_id,
2842 wts.departure_gross_weight,
2843 wts.departure_volume
2844 FROM wsh_trip_stops wts
2845 WHERE wts.physical_stop_id = p_stop_id
2846 AND wts.trip_id = p_trip_id
2847 AND wts.status_code IN ('OP', 'AR');
2848 --
2849 --
2850 -- Get linked internal inbound stop for normal WSH callers
2851 -- because the linked dummy stop is not shown in
2852 -- FTE workbenches and the inbound stop is not visible
2853 -- to WSH.
2854 -- This resolves an issue with the case of
2855 -- drop-ship to internal location, along with
2856 -- inbound and outbound deliveries to the same physical
2857 -- location.
2858 -- We need to ensure that if the physical stop
2859 -- gets closed from WSH side, the linked inbound stop
2860 -- will get Closed.
2861 CURSOR wsh_linked_stop_csr (p_stop_id IN NUMBER,
2862 p_trip_id IN NUMBER) IS
2863 SELECT wts.stop_id,
2864 wts.stop_sequence_number,
2865 wts.status_code,
2866 wts.shipments_type_flag shipments_Type_flag,
2867 wts.stop_location_id,
2868 wts.departure_gross_weight,
2869 wts.departure_volume
2870 FROM wsh_trip_stops wts
2871 WHERE wts.physical_stop_id = p_stop_id
2872 AND wts.trip_id = p_trip_id
2873 AND wts.status_code IN ('OP', 'AR')
2874 AND wts.shipments_type_flag = 'I';
2875 --
2876 --
2877 l_linked_stop_info_rec linked_stop_info_csr%ROWTYPE;
2878 --
2879 -- in case of -99 it will be equal
2880 --
2881 --
2882 -- Get all previous stop on the same trip which are not closed.
2883 -- Ignore the linked internal stop if specified.
2884 -- If not specified, its value will be -1 which should not be matched.
2885 --
2886 CURSOR prev_stop_csr
2887 (
2888 p_trip_id IN NUMBER,
2889 p_linked_stop_id IN NUMBER,
2890 p_stop_sequence IN NUMBER
2891 )
2892 IS
2893 SELECT nvl(shipments_type_flag,'O') shipments_Type_flag,
2894 stop_location_id
2895 FROM wsh_trip_stops
2896 WHERE trip_id = p_trip_id
2897 AND status_code IN ('OP','AR')
2898 AND stop_sequence_number < p_stop_sequence
2899 AND stop_id <> NVL(p_linked_stop_id, -1)
2900 order by stop_sequence_number;
2901 --AND rownum = 1;
2902 --
2903 --
2904 -- Get all open deliveries being picked up at the current stop,
2905 -- sorted by outbound first then inbound.
2906 -- linked internal stop is not included because it cannot be
2907 -- used as pick up.
2908 --
2909 CURSOR open_pickup_dlvy_csr (p_stop_id NUMBER) IS
2910 SELECT nvl(shipment_direction,'O') shipment_direction
2911 FROM wsh_new_deliveries wnd,
2912 wsh_delivery_legs wdl
2913 WHERE wdl.pick_up_stop_id = p_stop_id
2914 AND wnd.delivery_id = wdl.delivery_id
2915 AND wnd.status_code IN ('OP', 'PA')
2916 ORDER BY DECODE( NVL(wnd.shipment_direction,'O'), 'O', 1, 'IO', 1, 'I', 2, 'D', 2, 2 );
2917 --AND rownum = 1;
2918 --
2919 --
2920 --
2921 -- Get deliveries being dropped off at the current stop
2922 -- and its linked dummy stop,
2923 -- sorted by outbound first then inbound.
2924 -- based on shipment direction, their status must be:
2925 -- * outbound/internal outbound: open or confirmed.
2926 -- * inbound, etc.: open or in-transit
2927 --
2928 CURSOR open_dropoff_dlvy_csr (p_stop_id NUMBER, p_stop_location_id NUMBER,
2929 p_dummy_stop_id NUMBER, p_dummy_location_id NUMBER) IS
2930 SELECT NVL(wnd.shipment_direction,'O') shipment_direction,
2931 wnd.ultimate_dropoff_location_id,
2932 wnd.status_code,
2933 DECODE(wnd.ultimate_dropoff_location_id,p_stop_location_id,1,
2934 (DECODE(wnd.ultimate_dropoff_location_id,p_dummy_location_id,1, 2))
2935 ) last_stop,
2936 wdl.drop_off_stop_id drop_off_stop_id
2937 FROM wsh_new_deliveries wnd,
2938 wsh_delivery_legs wdl
2939 WHERE wdl.drop_off_stop_id IN (p_stop_id, p_dummy_stop_id)
2940 AND wnd.delivery_id = wdl.delivery_id
2941 AND (
2942 (
2943 NVL(wnd.shipment_direction,'O') IN ('O','IO')
2944 AND wnd.status_code IN ('OP', 'PA', 'CO' )
2945 )
2946 OR
2947 (
2948 NVL(wnd.shipment_direction,'O') NOT IN ('O','IO')
2949 AND wnd.status_code IN ('OP', 'IT' )
2950 )
2951 )
2952 ORDER BY DECODE( NVL(wnd.shipment_direction,'O'), 'O', 1, 'IO', 1, 'I', 2, 'D', 2, 2 ) ASC, wnd.status_code DESC, last_stop ASC;
2953 --AND rownum = 1;
2954 --
2955 --
2956 -- Get previous leg for delivery (or all deliveries if p_delivery_id is null)
2957 -- being picked up at the current stop (indicated by p_stop_id)
2958 --
2959 -- Linked internal stop is not included because it cannot have pick-ups.
2960 --
2961 -- results sorted by outbound first then inbound.
2962 --
2963 CURSOR prev_leg_csr (p_stop_id IN NUMBER, p_delivery_id IN NUMBER) IS
2964 SELECT prev_leg_do_stop.status_code do_stop_status_code,
2965 NVL(prev_leg_do_stop.shipments_type_flag,'O') do_stop_shipments_type_flag,
2966 prev_leg_do_stop.stop_location_id do_stop_locationId,
2967 prev_leg_do_stop.stop_id do_stop_id,
2968 prev_leg_do_stop.stop_sequence_number do_stop_sequence_number,
2969 prev_leg_pu_stop.status_code pu_stop_status_code,
2970 NVL(prev_leg_pu_stop.shipments_type_flag,'O') pu_stop_shipments_type_flag,
2971 prev_leg_pu_stop.stop_location_id pu_stop_locationId,
2972 prev_leg_pu_stop.stop_id pu_stop_id,
2973 NVL(wnd.shipment_direction,'O') shipment_direction,
2974 wnd.status_code dlvy_status_code,
2975 wnd.delivery_id delivery_id,
2976 wnd.initial_pickup_location_id dlvy_initialPULocationId,
2977 wnd.name dlvy_name,
2978 DECODE(prev_leg_do_stop.status_code,'OP','OP','XX') do_stop_status_code_ord,
2979 DECODE(prev_leg_pu_stop.status_code,'OP','OP','XX') pu_stop_status_code_ord,
2980 prev_leg_do_stop.trip_id prev_leg_trip_id,
2981 wt.name prev_leg_trip_name
2982 FROM wsh_trip_stops prev_leg_do_stop,
2983 wsh_trip_stops prev_leg_pu_stop,
2984 wsh_trip_stops curr_leg_pu_stop,
2985 wsh_delivery_legs prev_leg,
2986 wsh_delivery_legs curr_leg,
2987 wsh_new_deliveries wnd,
2988 wsh_trips wt
2989 WHERE prev_leg.drop_off_stop_id = prev_leg_do_stop.stop_id
2990 --AND st1.status_code = 'OP'
2991 AND prev_leg.pick_up_stop_id = prev_leg_pu_stop.stop_id
2992 AND prev_leg_do_stop.stop_location_id = curr_leg_pu_stop.stop_location_id
2993 AND prev_leg_do_stop.trip_id = wt.trip_id
2994 AND prev_leg.delivery_id = curr_leg.delivery_id
2995 AND curr_leg_pu_stop.stop_id = p_stop_id
2996 AND curr_leg.pick_up_stop_id = p_stop_id
2997 AND wnd.delivery_id = curr_leg.delivery_id
2998 AND (
2999 p_delivery_id IS NULL
3000 OR
3001 wnd.delivery_id = p_delivery_id
3002 )
3003 ORDER BY DECODE( NVL(wnd.shipment_direction,'O'), 'O', 1, 'IO', 1, 'I', 2, 'D', 2, 2 ), do_stop_status_code_ord, pu_stop_status_code_ord, wnd.delivery_id;
3004 --AND rownum = 1;
3005 --
3006 --
3007 --
3008 -- Get previous leg for delivery (or all deliveries if p_delivery_id is null)
3009 -- being picked up at the current stop (indicated by p_stop_id)
3010 --
3011 -- Linked internal stop is not included because it cannot have pick-ups.
3012 --
3013 -- results sorted by outbound first then inbound.
3014 --
3015 -- This is same as prev_leg_csr but copied once again
3016 -- as we need to open this cursor in nested fashion.
3017 -- (I know this is not very good coding practice
3018 -- but it is a compromise considering the time constraints
3019 -- need to fix this in next release)
3020 --
3021 CURSOR prev_leg_csr1 (p_stop_id IN NUMBER, p_delivery_id IN NUMBER) IS
3022 SELECT prev_leg_do_stop.status_code do_stop_status_code,
3023 NVL(prev_leg_do_stop.shipments_type_flag,'O') do_stop_shipments_type_flag,
3024 prev_leg_do_stop.stop_location_id do_stop_locationId,
3025 prev_leg_do_stop.stop_id do_stop_id,
3026 prev_leg_do_stop.stop_sequence_number do_stop_sequence_number,
3027 prev_leg_pu_stop.status_code pu_stop_status_code,
3028 NVL(prev_leg_pu_stop.shipments_type_flag,'O') pu_stop_shipments_type_flag,
3029 prev_leg_pu_stop.stop_location_id pu_stop_locationId,
3030 prev_leg_pu_stop.stop_id pu_stop_id,
3031 NVL(wnd.shipment_direction,'O') shipment_direction,
3032 wnd.status_code dlvy_status_code,
3033 wnd.delivery_id delivery_id,
3034 wnd.initial_pickup_location_id dlvy_initialPULocationId,
3035 wnd.name dlvy_name,
3036 DECODE(prev_leg_do_stop.status_code,'OP','OP','XX') do_stop_status_code_ord,
3037 DECODE(prev_leg_pu_stop.status_code,'OP','OP','XX') pu_stop_status_code_ord,
3038 prev_leg_do_stop.trip_id prev_leg_trip_id,
3039 wt.name prev_leg_trip_name
3040 FROM wsh_trip_stops prev_leg_do_stop,
3041 wsh_trip_stops prev_leg_pu_stop,
3042 wsh_trip_stops curr_leg_pu_stop,
3043 wsh_delivery_legs prev_leg,
3044 wsh_delivery_legs curr_leg,
3045 wsh_new_deliveries wnd,
3046 wsh_trips wt
3047 WHERE prev_leg.drop_off_stop_id = prev_leg_do_stop.stop_id
3048 --AND st1.status_code = 'OP'
3049 AND prev_leg.pick_up_stop_id = prev_leg_pu_stop.stop_id
3050 AND prev_leg_do_stop.stop_location_id = curr_leg_pu_stop.stop_location_id
3051 AND prev_leg_do_stop.trip_id = wt.trip_id
3052 AND prev_leg.delivery_id = curr_leg.delivery_id
3053 AND curr_leg_pu_stop.stop_id = p_stop_id
3054 AND curr_leg.pick_up_stop_id = p_stop_id
3055 AND wnd.delivery_id = curr_leg.delivery_id
3056 AND (
3057 p_delivery_id IS NULL
3058 OR
3059 wnd.delivery_id = p_delivery_id
3060 )
3061 ORDER BY DECODE( NVL(wnd.shipment_direction,'O'), 'O', 1, 'IO', 1, 'I', 2, 'D', 2, 2 ), do_stop_status_code_ord, pu_stop_status_code_ord, wnd.delivery_id;
3062 --AND rownum = 1;
3063 --
3064 --
3065 --
3066 -- Get pickup stop information for an inbound delivery, given its drop-off stop.
3067 -- drop-off stop can be physical or linked internal.
3068 -- Pick up stops must be closed.
3069 --
3070 CURSOR ib_pickup_csr (p_stop_id IN NUMBER,
3071 p_linked_stop_id IN NUMBER) IS
3072 SELECT NVL(st1.shipments_type_flag,'O') pu_shipments_type_flag,
3073 st1.status_code pu_stop_statusCode,
3074 st1.stop_location_id pu_stop_locationId,
3075 st1.stop_id pu_stop_id,
3076 wnd.delivery_id delivery_id,
3077 wnd.status_code delivery_statusCode,
3078 wnd.initial_pickup_location_id dlvy_initialPULocationId
3079 FROM wsh_trip_stops st1,
3080 wsh_delivery_legs dl2,
3081 wsh_new_deliveries wnd
3082 WHERE dl2.pick_up_stop_id = st1.stop_id
3083 AND st1.status_code = 'CL'
3084 AND dl2.drop_off_stop_id IN (p_stop_id, p_linked_stop_id)
3085 AND wnd.delivery_id = dl2.delivery_id
3086 AND nvl(wnd.shipment_direction,'O') NOT IN ('O','IO');
3087 --
3088 --
3089 -- Get all deliveries which start(initial pickup location) at current stop,
3090 -- sorted by organization id
3091 -- linked internal stop is not included because it cannot have pick-ups.
3092 --
3093 CURSOR initial_pu_dlvy_csr (p_stop_id NUMBER) IS
3094 SELECT dl.delivery_id,
3095 dl.organization_id,
3096 DECODE(NVL(dl.shipment_direction,'O'), 'IO', dl.ultimate_dropoff_location_id, NULL) io_location_id,
3097 dl.name,
3098 dl.status_code
3099 FROM wsh_new_deliveries dl,
3100 wsh_delivery_legs dg,
3101 wsh_trip_stops st
3102 WHERE dg.delivery_id = dl.delivery_id
3103 AND st.stop_location_id = dl.initial_pickup_location_id
3104 AND st.stop_id = dg.pick_up_stop_id
3105 AND st.stop_id = p_stop_id
3106 AND NVL(dl.shipment_direction,'O') IN ('O','IO')
3107 ORDER BY organization_id;
3108 --AND rownum = 1;
3109 --
3110 --
3111 -- Get all deliveries which end(ultimate dropoff location) at current stop
3112 -- or its linked internal stop,
3113 -- sorted by organization id
3114 --
3115 CURSOR ultimate_do_dlvy_csr (p_stop_id NUMBER,
3116 p_dummy_stop_id NUMBER) IS
3117 SELECT dl.delivery_id,
3118 dl.organization_id,
3119 dl.name,
3120 dl.status_code,
3121 dg.drop_off_stop_id
3122 FROM wsh_new_deliveries dl,
3123 wsh_delivery_legs dg,
3124 wsh_trip_stops st
3125 WHERE dg.delivery_id = dl.delivery_id
3126 AND st.stop_location_id = dl.ultimate_dropoff_location_id
3127 AND st.stop_id = dg.drop_off_stop_id
3128 AND st.stop_id IN (p_stop_id, p_dummy_stop_id)
3129 AND NVL(dl.shipment_direction,'O') IN ('O','IO')
3130 AND dl.status_code = 'IT'
3131 ORDER BY organization_id;
3132 --AND rownum = 1;
3133 --
3134 --
3135 -- Need to get receiving organization_id and destination type code for location
3136 CURSOR get_org_id (c_delivery_id NUMBER) IS
3137 SELECT pl.destination_organization_id, pl.destination_type_code
3138 FROM wsh_delivery_assignments wda, wsh_delivery_details wdd,
3139 oe_order_lines_all oel, po_requisition_lines_all pl
3140 WHERE wda.delivery_id = c_delivery_id
3141 AND wda.delivery_detail_id = wdd.delivery_detail_id
3142 AND wdd.source_document_type_id = 10
3143 AND wdd.source_line_id = oel.line_id
3144 AND wdd.source_code = 'OE'
3145 AND pl.requisition_line_id = oel.source_document_line_id
3146 AND pl.destination_organization_id <> pl.source_organization_id;
3147
3148 -- Need to get intransit type from Shipping Networks
3149 CURSOR c_mtl_interorg_parameters (c_from_organization_id NUMBER, c_to_organization_id NUMBER) IS
3150 SELECT intransit_type
3151 FROM mtl_interorg_parameters
3152 WHERE from_organization_id = c_from_organization_id
3153 AND to_organization_id = c_to_organization_id;
3154
3155 l_prev_dropoff_location_id NUMBER;
3156 l_rec_organization_id NUMBER;
3157 l_dest_type_code VARCHAR2(30);
3158 l_intransit_type NUMBER;
3159 l_validate_rec_org BOOLEAN;
3160 l_org_name VARCHAR2(240);
3161 l_err_dlvy_cnt NUMBER;
3162 l_dlvy_cnt NUMBER;
3163 l_prev_org_dlvy_cnt NUMBER;
3164 l_prev_org_id NUMBER;
3165 l_stop_warnings NUMBER;
3166 l_num_warnings NUMBER;
3167 l_num_errors NUMBER;
3168 l_return_status VARCHAR2(30);
3169 l_stop_name VARCHAR2(60);
3170 l_prev_stop_name VARCHAR2(60);
3171 l_message_name VARCHAR2(100);
3172 l_allowed VARCHAR2(10);
3173 --
3174
3175 l_param_info WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ;
3176 -- Bug 3346237 : Parameters Enforce Ship Method and Allow future ship date
3177 -- should use values present in Global Parameters table.
3178 l_global_info WSH_SHIPPING_PARAMS_PVT.Global_Parameters_Rec_Typ;
3179
3180 l_itm_mark_dels wsh_util_core.id_tab_type;
3181 l_exceptions_tab wsh_delivery_validations.exception_rec_tab_type;
3182 l_exceptions_exist VARCHAR2(1);
3183 l_period_id NUMBER;
3184 l_open_past_period BOOLEAN;
3185 l_stop_Id NUMBER;
3186 l_stop_locationId NUMBER;
3187 l_dlvy_initialPULocationId NUMBER;
3188 --
3189 l_in_rec WSH_DELIVERY_VALIDATIONS.ChgStatus_in_rec_type;
3190 l_trip_in_rec WSH_TRIP_VALIDATIONS.ChgStatus_in_rec_type;
3191 l_wv_check_done BOOLEAN;
3192
3193 -- Exception variables
3194 l_exceptions_tbl wsh_xc_util.XC_TAB_TYPE;
3195 l_exp_logged BOOLEAN := FALSE;
3196 l_msg_count NUMBER;
3197 l_msg_data VARCHAR2(4000);
3198 --
3199 l_location_id NUMBER;
3200
3201 --
3202 l_virtual_shipments_type_flag WSH_TRIP_STOPS.SHIPMENTS_TYPE_FLAG%TYPE;
3203
3204 -- Following three variables are added for BufFix #3947506
3205 l_out_entity_id VARCHAR2(100);
3206 l_out_entity_name VARCHAR2(100);
3207 l_out_status VARCHAR2(1);
3208
3209 --
3210 l_debug_on BOOLEAN;
3211 --
3212 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_STOP_CLOSE';
3213
3214 --}
3215 BEGIN
3216 --{
3217 --
3218 -- Debug Statements
3219 --
3220 --
3221 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3222 --
3223 IF l_debug_on IS NULL
3224 THEN
3225 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3226 END IF;
3227 --
3228 IF l_debug_on THEN
3229 WSH_DEBUG_SV.push(l_module_name);
3230 --
3231 WSH_DEBUG_SV.log(l_module_name,'P_in_rec.STOP_ID',P_in_rec.STOP_ID);
3232 WSH_DEBUG_SV.log(l_module_name,'P_in_rec.put_messages',P_in_rec.put_messages);
3233 WSH_DEBUG_SV.log(l_module_name,'P_in_rec.caller',P_in_rec.caller);
3234 WSH_DEBUG_SV.log(l_module_name,'P_in_rec.actual_date',P_in_rec.actual_date);
3235 END IF;
3236 --
3237 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3238 l_stop_warnings := 0;
3239 l_num_warnings := 0;
3240 l_num_errors := 0;
3241 --
3242 x_out_rec.initial_pu_dlvy_recTbl.id_tbl.DELETE;
3243 x_out_rec.initial_pu_dlvy_recTbl.name_tbl.DELETE;
3244 x_out_rec.initial_pu_dlvy_recTbl.statusCode_tbl.DELETE;
3245 x_out_rec.initial_pu_dlvy_recTbl.orgId_tbl.DELETE;
3246 --
3247 x_out_rec.ultimate_do_dlvy_recTbl.id_tbl.DELETE;
3248 x_out_rec.ultimate_do_dlvy_recTbl.name_tbl.DELETE;
3249 x_out_rec.ultimate_do_dlvy_recTbl.statusCode_tbl.DELETE;
3250 x_out_rec.ultimate_do_dlvy_recTbl.orgId_tbl.DELETE;
3251 --
3252 x_out_rec.initial_pu_err_dlvy_id_tbl.DELETE;
3253 --
3254 --
3255 -- Get Stop Info
3256 --
3257 OPEN stop_info_csr (p_in_rec.stop_id);
3258 FETCH stop_info_csr INTO l_stop_info_rec;
3259 --
3260 IF stop_info_csr%NOTFOUND
3261 THEN
3262 --{
3263 --CLOSE stop_info_csr;
3264 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_EXIST');
3265 FND_MESSAGE.SET_TOKEN('STOP_ID',p_in_rec.stop_id);
3266 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
3267 RAISE FND_API.G_EXC_ERROR;
3268 --}
3269 END IF;
3270 --
3271 CLOSE stop_info_csr;
3272 --
3273 --
3274 l_virtual_shipments_type_flag := l_stop_info_rec.shipments_type_flag;
3275 --
3276 --
3277 -- we check for FTE because only FTE user can close both linked stops.
3278 -- we check for INBOUND because only Inbound user can close both linked stops.
3279 -- WSH users must close one stop at a time in the proper sequence
3280 -- on the same trip.
3281 --
3282 -- If caller passes the dummy stop, we will not look up the physical stop.
3283 --
3284 IF (p_in_rec.caller LIKE 'FTE%'
3285 OR p_in_rec.caller LIKE 'WSH_IB%' -- Inbound Changes 10+ Internal Locations
3286 ) THEN
3287 OPEN linked_stop_info_csr(p_in_rec.stop_id,
3288 l_stop_info_rec.trip_id);
3289 FETCH linked_stop_info_csr INTO l_linked_stop_info_rec;
3290 IF linked_stop_info_csr%NOTFOUND THEN
3291 -- use -1 or 'x' instead of NULL to avoid need for NVL in SQLs and conditions.
3292 l_linked_stop_info_rec.stop_id := -1;
3293 l_linked_stop_info_rec.stop_location_id := -1;
3294 l_linked_stop_info_rec.shipments_type_flag := 'x';
3295 x_out_rec.linked_stop_id := NULL;
3296 ELSE
3297 x_out_rec.linked_stop_id := l_linked_stop_info_rec.stop_id;
3298
3299 --
3300 -- In this API, if the stops are linked and both are not closed, they
3301 -- represent a virtual stop and we need to determine its shipments type.
3302 --
3303 -- linked dummy stop (OP, AR) physical stop virtual stop
3304 -- shipments type flag shipments type flag shipments type flag
3305 -- ------------------------- -------------------- -------------------
3306 -- O O O
3307 -- I I I
3308 -- O I M
3309 -- I O M
3310 -- M * M
3311 -- * M M
3312 --
3313 -- If there is no non-closed linked dummy stop, the virtual flag will have the
3314 -- main stop's flag value.
3315
3316 IF l_virtual_shipments_type_flag <> 'M'
3317 AND l_stop_info_rec.shipments_type_flag <> l_linked_stop_info_rec.shipments_type_flag THEN
3318 l_virtual_shipments_type_flag := 'M';
3319 END IF;
3320
3321 END IF;
3322 CLOSE linked_stop_info_csr;
3323 ELSE
3324 -- normal WSH cases need to close linked inbound internal stop;
3325 -- if the linked stop is outbound or mixed, it is visible to WSH
3326 -- and should normally be closed first.
3327 OPEN wsh_linked_stop_csr(p_in_rec.stop_id,
3328 l_stop_info_rec.trip_id);
3329 FETCH wsh_linked_stop_csr INTO l_linked_stop_info_rec;
3330 IF wsh_linked_stop_csr%NOTFOUND THEN
3331 -- use -1 or 'x' instead of NULL to avoid need for NVL in SQLs and conditions.
3332 l_linked_stop_info_rec.stop_id := -1;
3333 l_linked_stop_info_rec.stop_location_id := -1;
3334 x_out_rec.linked_stop_id := NULL;
3335 ELSE
3336 -- this case is always Mixed.
3337 x_out_rec.linked_stop_id := l_linked_stop_info_rec.stop_id;
3338 l_virtual_shipments_type_flag := 'M';
3339 END IF;
3340 CLOSE wsh_linked_stop_csr;
3341 END IF;
3342 --
3343 --
3344 IF l_debug_on THEN
3345 WSH_DEBUG_SV.log(l_module_name,'l_linked_stop_info_rec.stop_id',l_linked_stop_info_rec.stop_id);
3346 WSH_DEBUG_SV.log(l_module_name,'l_linked_stop_info_rec.stop_location_id',l_linked_stop_info_rec.stop_location_id);
3347 END IF;
3348
3349 IF (p_in_rec.caller LIKE 'FTE%'
3350 OR p_in_rec.caller LIKE 'WSH_IB%')
3351 AND l_stop_info_rec.physical_location_id IS NOT NULL THEN
3352 l_location_id := l_stop_info_rec.physical_location_id;
3353 ELSE
3354 l_location_id := l_stop_info_rec.stop_location_id;
3355 END IF;
3356
3357 l_stop_name := SUBSTRB(
3358 WSH_UTIL_CORE.get_location_description
3359 (
3360 l_location_id,
3361 'NEW UI CODE'
3362 ),
3363 1,
3364 60
3365 );
3366 --
3367 x_out_rec.stop_name := l_stop_name;
3368 x_out_rec.stop_Sequence_number := l_stop_info_rec.stop_Sequence_number;
3369 x_out_rec.trip_id := l_stop_info_rec.trip_id;
3370 x_out_rec.ship_method_code := l_stop_info_rec.ship_method_code;
3371 x_out_rec.carrier_id := l_stop_info_rec.carrier_id;
3372 x_out_rec.mode_of_transport := l_stop_info_rec.mode_of_transport;
3373 x_out_rec.service_level := l_stop_info_rec.service_level;
3374 x_out_rec.trip_Status_code := l_stop_info_Rec.trip_Status_Code;
3375 x_out_rec.trip_new_Status_code := l_stop_info_Rec.trip_Status_Code;
3376 x_out_rec.trip_seal_code := l_stop_info_rec.trip_seal_code;
3377 x_out_rec.trip_name := l_stop_info_rec.trip_name;
3378 --
3379 IF l_debug_on THEN
3380 WSH_DEBUG_SV.log(l_module_name,'l_stop_info_rec.stop_Sequence_number',l_stop_info_rec.stop_Sequence_number);
3381 WSH_DEBUG_SV.log(l_module_name,'l_stop_info_rec.trip_id',l_stop_info_rec.trip_id);
3382 WSH_DEBUG_SV.log(l_module_name,'l_stop_info_Rec.trip_Status_Code',l_stop_info_Rec.trip_Status_Code);
3383 WSH_DEBUG_SV.log(l_module_name,'l_stop_info_Rec.shipments_Type_flag',l_stop_info_Rec.shipments_Type_flag);
3384 WSH_DEBUG_SV.log(l_module_name,'l_linked_stop_info_Rec.shipments_Type_flag',l_linked_stop_info_Rec.shipments_Type_flag);
3385 WSH_DEBUG_SV.log(l_module_name,'l_virtual_shipments_Type_flag',l_virtual_shipments_Type_flag);
3386 END IF;
3387 --
3388
3389 -- Check for Exceptions against Trip Stop and its Contents
3390 IF l_debug_on THEN
3391 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.Check_Exceptions',WSH_DEBUG_SV.C_PROC_LEVEL);
3392 END IF;
3393 l_exceptions_tbl.delete;
3394 l_exp_logged := FALSE;
3395 WSH_XC_UTIL.Check_Exceptions (
3396 p_api_version => 1.0,
3397 x_return_status => l_return_status,
3398 x_msg_count => l_msg_count,
3399 x_msg_data => l_msg_data,
3400 p_logging_entity_id => p_in_rec.stop_id,
3401 p_logging_entity_name => 'STOP',
3402 p_consider_content => 'Y',
3403 x_exceptions_tab => l_exceptions_tbl,
3404 --tkt
3405 p_caller => p_in_rec.caller
3406 );
3407 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
3408 x_return_status := l_return_status;
3409 wsh_util_core.add_message(x_return_status);
3410 RAISE FND_API.G_EXC_ERROR;
3411 END IF;
3412 FOR exp_cnt in 1..l_exceptions_tbl.COUNT LOOP
3413 IF l_exceptions_tbl(exp_cnt).exception_behavior = 'ERROR' THEN
3414 IF l_exceptions_tbl(exp_cnt).entity_name = 'STOP' THEN
3415 FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_ENTITY');
3416 ELSE
3417 FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_CONTENTS');
3418 END IF;
3419
3420 -- BugFix #3947506
3421 WSH_UTIL_CORE.Get_Entity_Name
3422 ( l_exceptions_tbl(exp_cnt).entity_id,
3423 l_exceptions_tbl(exp_cnt).entity_name,
3424 l_out_entity_id,
3425 l_out_entity_name,
3426 l_out_status);
3427
3428 IF ( l_out_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
3429 wsh_util_core.add_message(l_out_status);
3430 RAISE FND_API.G_EXC_ERROR;
3431 END IF;
3432
3433 -- End of code BugFix #3947506
3434
3435 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',l_out_entity_name); -- BugFix #3947506
3436 FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_out_entity_id); -- BugFix #3947506
3437 FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Error');
3438 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3439 wsh_util_core.add_message(x_return_status);
3440 l_stop_warnings := l_stop_warnings + 1;
3441 RAISE wsh_util_core.e_not_allowed;
3442 ELSIF l_exceptions_tbl(exp_cnt).exception_behavior = 'WARNING' THEN
3443 IF l_exceptions_tbl(exp_cnt).entity_name = 'STOP' THEN
3444 FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_ENTITY');
3445 FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Stop');
3446 FND_MESSAGE.SET_TOKEN('ENTITY_ID',wsh_trip_stops_pvt.get_name(l_exceptions_tbl(exp_cnt).entity_id)); --BugFix #3925590
3447 FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Warning');
3448 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3449 wsh_util_core.add_message(x_return_status);
3450 l_stop_warnings := l_stop_warnings + 1;
3451 ELSIF NOT (l_exp_logged) THEN
3452 -- BugFix #3947506
3453 WSH_UTIL_CORE.Get_Entity_Name
3454 ( l_exceptions_tbl(exp_cnt).entity_id,
3455 l_exceptions_tbl(exp_cnt).entity_name,
3456 l_out_entity_id,
3457 l_out_entity_name,
3458 l_out_status);
3459
3460 IF ( l_out_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
3461 wsh_util_core.add_message(l_out_status);
3462 RAISE FND_API.G_EXC_ERROR;
3463 END IF;
3464 -- End of code BugFix #3947506
3465 FND_MESSAGE.SET_NAME('WSH','WSH_XC_EXIST_CONTENTS');
3466 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',l_out_entity_name); -- BugFix #3947506
3467 FND_MESSAGE.SET_TOKEN('ENTITY_ID',l_out_entity_id); -- BugFix #3947506
3468 FND_MESSAGE.SET_TOKEN('EXCEPTION_BEHAVIOR','Warning');
3469 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3470 l_exp_logged := TRUE;
3471 wsh_util_core.add_message(x_return_status);
3472 l_stop_warnings := l_stop_warnings + 1;
3473 END IF;
3474 END IF;
3475 END LOOP;
3476
3477 --
3478 --
3479 -- Get all previous stop on the same trip which are not closed.
3480 -- All prior outbound/mixed stops must be closed.
3481 --
3482 -- If current stop is inbound, then all prior inbound stops must be closed as well.
3483 --
3484 -- If current stop is not inbound, then give a warning if any prior inbound stop is open.
3485 --
3486 -- Following loop for cursor prev_stop_csr validates the above.
3487 --
3488 FOR prev_stop_rec IN prev_stop_csr
3489 (
3490 p_trip_id => l_stop_info_rec.trip_id,
3491 p_linked_stop_id => l_linked_stop_info_rec.stop_id,
3492 p_stop_sequence => l_stop_info_rec.stop_sequence_number
3493 )
3494 LOOP
3495 --{
3496 IF p_in_rec.put_messages
3497 THEN
3498 --{
3499 FND_MESSAGE.SET_NAME('WSH','WSH_PREV_STOP_NOT_CLOSED');
3500 FND_MESSAGE.SET_TOKEN('STOP_NAME', l_stop_name );
3501 --
3502 l_prev_stop_name := SUBSTRB(
3503 WSH_UTIL_CORE.get_location_description
3504 (
3505 prev_stop_rec.stop_location_id,
3506 'NEW UI CODE'
3507 ),
3508 1,
3509 60
3510 );
3511 --
3512 FND_MESSAGE.SET_TOKEN('PREV_STOP_NAME',l_prev_stop_name);
3513 FND_MESSAGE.SET_TOKEN('TRIP_NAME',l_stop_info_rec.trip_name);
3514 FND_MESSAGE.SET_TOKEN('PREV_TRIP_NAME',l_stop_info_rec.trip_name);
3515 --}
3516 END IF;
3517 --
3518 IF l_debug_on THEN
3519 WSH_DEBUG_SV.log(l_module_name,'prev_stop_rec.shipments_type_flag',prev_stop_rec.shipments_type_flag);
3520 WSH_DEBUG_SV.log(l_module_name,'prev_stop_rec.stop_location_id',prev_stop_rec.stop_location_id);
3521 END IF;
3522 --
3523 --
3524 IF l_virtual_shipments_type_flag IN ( 'M' , 'O' )
3525 AND prev_stop_rec.shipments_type_flag = 'I'
3526 THEN
3527 --{
3528 IF p_in_rec.put_messages
3529 THEN
3530 --{
3531 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING,l_module_name);
3532 --}
3533 END IF;
3534 --
3535 l_stop_warnings := l_stop_warnings + 1;
3536 --}
3537 ELSE
3538 --{
3539 IF p_in_rec.put_messages
3540 THEN
3541 --{
3542 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
3543 --}
3544 END IF;
3545 --
3546 --CLOSE prev_stop_csr;
3547 --
3548 RAISE wsh_util_core.e_not_allowed;
3549 --}
3550 END IF;
3551 --}
3552 END LOOP;
3553 --
3554 --
3555 -- bug 1550824: check for deliveries not ready to pick up
3556 --
3557 --
3558 -- Check if there are any open deliveries to be picked up at the stop.
3559 -- All outbound (O/IO) deliveries must be at least ship-confirmed.
3560 --
3561 -- If current stop is inbound, then all inbound deliveries (not O/IO) must be in-transit.
3562 --
3563 -- If current stop is mixed one, then give a warning if any open inbound delivery (not O/IO)
3564 --
3565 -- Following loop for cursor open_pickup_dlvy_csr validates the above.
3566 --
3567 FOR open_pickup_dlvy_rec IN open_pickup_dlvy_csr (p_in_rec.stop_id)
3568 LOOP
3569 --{
3570 IF p_in_rec.put_messages
3571 THEN
3572 --{
3573 IF open_pickup_dlvy_rec.shipment_direction IN ( 'O', 'IO' )
3574 THEN
3575 l_message_name := 'WSH_STOP_CLOSE_OP_PA_ERROR';
3576 ELSE
3577 l_message_name := 'WSH_STOP_CLOSE_OP_IT_ERROR';
3578 END IF;
3579 --
3580 FND_MESSAGE.SET_NAME('WSH', l_message_name);
3581 FND_MESSAGE.SET_TOKEN('STOP_NAME', l_stop_name );
3582 --}
3583 END IF;
3584 --
3585 IF l_debug_on THEN
3586 WSH_DEBUG_SV.log(l_module_name,'open_pickup_dlvy_rec.shipment_direction',open_pickup_dlvy_rec.shipment_direction);
3587 END IF;
3588 --
3589 --
3590 IF open_pickup_dlvy_rec.shipment_direction IN ( 'O', 'IO' )
3591 OR l_virtual_shipments_Type_flag = 'I'
3592 THEN
3593 --{
3594 IF p_in_rec.put_messages
3595 THEN
3596 --{
3597 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
3598 --}
3599 END IF;
3600 --
3601 --CLOSE open_pickup_dlvy_csr;
3602 --
3603 RAISE wsh_util_core.e_not_allowed;
3604 --}
3605 ELSE
3606 --{
3607 IF p_in_rec.put_messages
3608 THEN
3609 --{
3610 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING,l_module_name);
3611 --}
3612 END IF;
3613 --
3614 --CLOSE open_pickup_dlvy_csr;
3615 --
3616 l_stop_warnings := l_stop_warnings + 1;
3617 --
3618 EXIT;
3619 --}
3620 END IF;
3621 --CLOSE open_pickup_dlvy_csr;
3622 --}
3623 END LOOP;
3624 --
3625 --
3626 -- Check if there are any OP/CO/IT deliveries being dropped off at the stop.
3627 -- All outbound (O/IO) deliveries must be at least set to in-transit.
3628 -- All inbound (not O/IO) deliveries must be at least set to in-transit.
3629 --
3630 -- If current stop is inbound and it is the last stop for delivery, then delivery
3631 -- must be closed.
3632 --
3633 -- If current stop is mixed one, then give a warning
3634 -- - if any inbound delivery (not O/IO) which is not in-transit
3635 -- - if any inbound delivery (not O/IO) which is not closed and this is the
3636 -- last stop for the delivery
3637 --
3638 -- Following loop for cursor open_dropoff_dlvy_csr validates the above.
3639 --
3640 FOR open_dropoff_dlvy_rec IN open_dropoff_dlvy_csr (p_in_rec.stop_id,l_stop_info_rec.stop_location_id,
3641 l_linked_stop_info_rec.stop_id, l_linked_stop_info_rec.stop_location_id)
3642 LOOP
3643 --{
3644 IF p_in_rec.put_messages
3645 THEN
3646 --{
3647 IF open_dropoff_dlvy_rec.shipment_direction IN ( 'O', 'IO' )
3648 OR open_dropoff_dlvy_rec.status_Code IN ( 'OP', 'PA','CO' )
3649 THEN
3650 l_message_name := 'WSH_DO_STOP_CLOSE_ERROR';
3651 ELSE
3652 l_message_name := 'WSH_DO_IB_STOP_CLOSE_ERROR';
3653 END IF;
3654 --
3655 FND_MESSAGE.SET_NAME('WSH', l_message_name);
3656 FND_MESSAGE.SET_TOKEN('STOP_NAME', l_stop_name );
3657 --}
3658 END IF;
3659 --
3660 IF l_debug_on THEN
3661 WSH_DEBUG_SV.log(l_module_name,'open_dropoff_dlvy_rec.shipment_direction',open_dropoff_dlvy_rec.shipment_direction);
3662 WSH_DEBUG_SV.log(l_module_name,'open_dropoff_dlvy_rec.status_Code',open_dropoff_dlvy_rec.status_Code);
3663 WSH_DEBUG_SV.log(l_module_name,'open_dropoff_dlvy_rec.ultimate_Dropoff_location_id',open_dropoff_dlvy_rec.ultimate_Dropoff_location_id);
3664 END IF;
3665 --
3666 --
3667 -- note that open_dropoff_dlvy_csr filters the deliveries by status,
3668 -- so that this code will see
3669 -- * outbound and outbound internal deliveries
3670 -- in status Open or Confirmed
3671 -- * inbound deliveries in status Open or In-Transit.
3672 --
3673 -- validate that the outbound or internal outbound deliveries being dropped off
3674 -- are not open or confirmed.
3675 --
3676 -- validate that the inbound deliveries are:
3677 -- * neither open
3678 -- * nor in-transit if its main/internal stop is inbound only
3679 -- and at its ultimate drop off location.
3680 --
3681 -- give warning in the event that the drop-off stop has mixed shipments
3682 -- if either condition is met:
3683 -- * the inbound delivery is open
3684 -- * the inbound delivery is in transit
3685 --
3686 -- In other words:
3687 --
3688 -- 1. Outbound deliveries must be in transit or closed
3689 -- for both cases of outbound and mixed stops
3690 -- (i.e., neither open nor confirmed).
3691 --
3692 -- 2. If the stop is Inbound only, inbound deliveries must be:
3693 -- * in-transit if not ultimate drop-off
3694 -- * or closed if ultimate drop-off.
3695 --
3696 -- 3. If the stop is mixed, give warning when inbound deliveries
3697 -- are open or in-transit.
3698 --
3699 IF
3700 -- outbound [open/confirmed] delivery is dropped off at either main or linked stop
3701 open_dropoff_dlvy_rec.shipment_direction IN ( 'O', 'IO' )
3702 OR ( -- inbound delivery at either main or linked stop
3703 l_virtual_shipments_Type_flag = 'I'
3704 AND (
3705 open_dropoff_dlvy_rec.status_Code = 'OP'
3706 OR (
3707 open_dropoff_dlvy_rec.status_Code = 'IT'
3708 AND open_dropoff_dlvy_rec.last_stop = 1
3709 )
3710 )
3711 )
3712 THEN
3713 --{
3714 IF p_in_rec.put_messages
3715 THEN
3716 --{
3717 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
3718 --}
3719 END IF;
3720 --
3721 --CLOSE open_dropoff_dlvy_csr;
3722 --
3723 RAISE wsh_util_core.e_not_allowed;
3724 --}
3725 --ELSE
3726 ELSIF
3727 -- stop's shipments type flag is mixed; checking status of inbound deliveries
3728 (
3729 open_dropoff_dlvy_rec.status_Code = 'OP'
3730 OR ( -- at the main stop
3731 open_dropoff_dlvy_rec.status_Code = 'IT'
3732 AND open_dropoff_dlvy_rec.last_stop = 1
3733 )
3734 )
3735 THEN
3736 --{
3737 IF p_in_rec.put_messages
3738 THEN
3739 --{
3740 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING,l_module_name);
3741 --}
3742 END IF;
3743 --
3744 --CLOSE open_dropoff_dlvy_csr;
3745 --
3746 l_stop_warnings := l_stop_warnings + 1;
3747 --
3748 EXIT;
3749 --}
3750 END IF;
3751 --}
3752 END LOOP;
3753 --
3754 --
3755 -- If any delivery is being picked up at current stop, then check previous leg
3756 -- for each such delivery.
3757 --
3758 -- (Linked internal stop is not checked because it cannot be used as pick up.)
3759 --
3760 -- If drop-off stop of previous leg is open and delivery involved is outbound (O/IO),
3761 -- it is an error.
3762 --
3763 -- If drop-off stop of previous leg is open and delivery involved is inbound (not O/IO),
3764 -- it is an error if current stop is also inbound else it is warning.
3765 --
3766 -- If drop-off stop of previous leg is not open and delivery involved is inbound (not O/IO),
3767 -- it is an error, if delivery is not closed.
3768 --
3769 -- Following loop for cursor prev_leg_csr validates the above.
3770 --
3771 FOR prev_leg_rec IN prev_leg_csr
3772 (
3773 p_stop_id => p_in_rec.stop_id,
3774 p_delivery_id => NULL
3775 )
3776 LOOP
3777 --{
3778 IF l_debug_on THEN
3779 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec.do_stop_shipments_type_flag',prev_leg_rec.do_stop_shipments_type_flag);
3780 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec.do_stop_status_code',prev_leg_rec.do_stop_status_code);
3781 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec.do_stop_locationId',prev_leg_rec.do_stop_locationId);
3782 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec.do_stop_id',prev_leg_rec.do_stop_id);
3783 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec.pu_stop_shipments_type_flag',prev_leg_rec.pu_stop_shipments_type_flag);
3784 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec.pu_stop_status_code',prev_leg_rec.pu_stop_status_code);
3785 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec.pu_stop_locationId',prev_leg_rec.pu_stop_locationId);
3786 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec.pu_stop_id',prev_leg_rec.pu_stop_id);
3787 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec.shipment_direction',prev_leg_rec.shipment_direction);
3788 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec.dlvy_status_code',prev_leg_rec.dlvy_status_code);
3789 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec.delivery_id',prev_leg_rec.delivery_id);
3790 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec.dlvy_initialPULocationId',prev_leg_rec.dlvy_initialPULocationId);
3791 END IF;
3792 --
3793 IF prev_leg_rec.do_stop_status_code = 'OP'
3794 THEN
3795 --{
3796 IF p_in_rec.put_messages
3797 THEN
3798 --{
3799 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_CLOSE_DLEG_ERROR');
3800 FND_MESSAGE.SET_TOKEN('STOP_NAME', l_stop_name );
3801 --}
3802 END IF;
3803 --
3804 IF l_virtual_shipments_Type_flag <> 'M'
3805 OR prev_leg_rec.do_stop_shipments_type_flag = 'O'
3806 OR prev_leg_rec.shipment_direction IN ( 'O', 'IO' )
3807 THEN
3808 --{
3809 IF p_in_rec.put_messages
3810 THEN
3811 --{
3812 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
3813 --}
3814 END IF;
3815 --
3816 --CLOSE prev_leg_csr;
3817 --
3818 RAISE wsh_util_core.e_not_allowed;
3819 --}
3820 ELSE
3821 --{
3822 IF p_in_rec.put_messages
3823 THEN
3824 --{
3825 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING,l_module_name);
3826 --}
3827 END IF;
3828 --
3829 --CLOSE prev_leg_csr;
3830 --
3831 l_stop_warnings := l_stop_warnings + 1;
3832 --
3833 EXIT;
3834 --}
3835 END IF;
3836 --}
3837 ELSIF prev_leg_rec.do_stop_status_code IN ('AR','CL')
3838 AND prev_leg_rec.shipment_direction NOT IN ( 'O', 'IO' )
3839 AND prev_leg_rec.dlvy_status_code <> 'OP'
3840 --AND prev_leg_rec.dlvy_status_code <> 'CL'
3841 THEN
3842 --{
3843 FOR prev_stop_rec IN prev_stop_csr
3844 (
3845 p_trip_id => prev_leg_rec.prev_leg_trip_id,
3846 p_linked_stop_id => l_linked_stop_info_rec.stop_id,
3847 p_stop_sequence => prev_leg_rec.do_stop_sequence_number
3848 )
3849 LOOP
3850 --{
3851 IF p_in_rec.put_messages
3852 THEN
3853 --{
3854 FND_MESSAGE.SET_NAME('WSH','WSH_PREV_STOP_NOT_CLOSED');
3855 FND_MESSAGE.SET_TOKEN('STOP_NAME', l_stop_name );
3856 --
3857 l_prev_stop_name := SUBSTRB(
3858 WSH_UTIL_CORE.get_location_description
3859 (
3860 prev_stop_rec.stop_location_id,
3861 'NEW UI CODE'
3862 ),
3863 1,
3864 60
3865 );
3866 --
3867 FND_MESSAGE.SET_TOKEN('PREV_STOP_NAME',l_prev_stop_name);
3868 FND_MESSAGE.SET_TOKEN('TRIP_NAME',l_stop_info_rec.trip_name);
3869 FND_MESSAGE.SET_TOKEN('PREV_TRIP_NAME',prev_leg_rec.prev_leg_trip_name);
3870 --}
3871 END IF;
3872 --
3873 IF l_debug_on THEN
3874 WSH_DEBUG_SV.log(l_module_name,'prev_stop_rec.shipments_type_flag',prev_stop_rec.shipments_type_flag);
3875 WSH_DEBUG_SV.log(l_module_name,'prev_stop_rec.stop_location_id',prev_stop_rec.stop_location_id);
3876 END IF;
3877 --
3878 --
3879 IF l_virtual_shipments_Type_flag = 'I'
3880 THEN
3881 --{
3882 IF p_in_rec.put_messages
3883 THEN
3884 --{
3885 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
3886 --}
3887 END IF;
3888 --
3889 --CLOSE prev_leg_csr;
3890 --
3891 RAISE wsh_util_core.e_not_allowed;
3892 --}
3893 ELSE
3894 --{
3895 IF p_in_rec.put_messages
3896 THEN
3897 --{
3898 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING,l_module_name);
3899 --}
3900 END IF;
3901 --
3902 --CLOSE prev_leg_csr;
3903 --
3904 l_stop_warnings := l_stop_warnings + 1;
3905 --
3906 EXIT;
3907 --}
3908 END IF;
3909 --}
3910 END LOOP;
3911 --
3912 IF prev_leg_rec.pu_stop_status_code = 'OP'
3913 THEN
3914 --{
3915 null;
3916 --}
3917 ELSE
3918 --{
3919 l_stop_locationId := prev_leg_rec.pu_stop_locationId;
3920 l_stop_Id := prev_leg_rec.pu_stop_Id;
3921 l_dlvy_initialPULocationId := prev_leg_rec.dlvy_initialPULocationId;
3922 --
3923 IF l_debug_on THEN
3924 WSH_DEBUG_SV.logmsg(l_module_name,'Checking all prior legs-1');
3925 END IF;
3926 --
3927 WHILE l_stop_locationId <> l_dlvy_initialPULocationId
3928 LOOP
3929 --{
3930 FOR prev_leg_rec1 IN prev_leg_csr1
3931 (
3932 p_stop_id => l_stop_id,
3933 p_delivery_id => prev_leg_Rec.delivery_id
3934 )
3935 LOOP
3936 --{
3937
3938 IF l_debug_on THEN
3939 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec1.do_stop_shipments_type_flag',prev_leg_rec1.do_stop_shipments_type_flag);
3940 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec1.do_stop_status_code',prev_leg_rec1.do_stop_status_code);
3941 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec1.do_stop_locationId',prev_leg_rec1.do_stop_locationId);
3942 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec1.do_stop_id',prev_leg_rec1.do_stop_id);
3943 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec1.pu_stop_shipments_type_flag',prev_leg_rec1.pu_stop_shipments_type_flag);
3944 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec1.pu_stop_status_code',prev_leg_rec1.pu_stop_status_code);
3945 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec1.pu_stop_locationId',prev_leg_rec1.pu_stop_locationId);
3946 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec1.pu_stop_id',prev_leg_rec1.pu_stop_id);
3947 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec1.shipment_direction',prev_leg_rec1.shipment_direction);
3948 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec1.dlvy_status_code',prev_leg_rec1.dlvy_status_code);
3949 END IF;
3950 --
3951 IF prev_leg_rec1.do_stop_status_code = 'OP'
3952 THEN
3953 --{
3954 IF p_in_rec.put_messages
3955 THEN
3956 --{
3957 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_CLOSE_DLEG_ERROR');
3958 FND_MESSAGE.SET_TOKEN('STOP_NAME', l_stop_name );
3959 --wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
3960 --}
3961 END IF;
3962 --
3963 IF l_virtual_shipments_Type_flag = 'I'
3964 THEN
3965 --{
3966 IF p_in_rec.put_messages
3967 THEN
3968 --{
3969 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
3970 --}
3971 END IF;
3972 --
3973 --CLOSE prev_leg_csr;
3974 --
3975 RAISE wsh_util_core.e_not_allowed;
3976 --}
3977 ELSE
3978 --{
3979 IF p_in_rec.put_messages
3980 THEN
3981 --{
3982 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING,l_module_name);
3983 --}
3984 END IF;
3985 --
3986 --CLOSE prev_leg_csr;
3987 --
3988 l_stop_warnings := l_stop_warnings + 1;
3989 --
3990 --EXIT;
3991 --}
3992 END IF;
3993 --}
3994 END IF;
3995 --
3996 --
3997 FOR prev_stop_rec IN prev_stop_csr
3998 (
3999 p_trip_id => prev_leg_rec1.prev_leg_trip_id,
4000 p_linked_stop_id => l_linked_stop_info_rec.stop_id,
4001
4002 p_stop_sequence => prev_leg_rec1.do_stop_sequence_number
4003 )
4004 LOOP
4005 --{
4006 IF p_in_rec.put_messages
4007 THEN
4008 --{
4009 FND_MESSAGE.SET_NAME('WSH','WSH_PREV_STOP_NOT_CLOSED');
4010 FND_MESSAGE.SET_TOKEN('STOP_NAME', l_stop_name );
4011 --
4012 l_prev_stop_name := SUBSTRB(
4013 WSH_UTIL_CORE.get_location_description
4014 (
4015 prev_stop_rec.stop_location_id,
4016 'NEW UI CODE'
4017 ),
4018 1,
4019 60
4020 );
4021 --
4022 FND_MESSAGE.SET_TOKEN('PREV_STOP_NAME',l_prev_stop_name);
4023 FND_MESSAGE.SET_TOKEN('TRIP_NAME',l_stop_info_rec.trip_name);
4024 FND_MESSAGE.SET_TOKEN('PREV_TRIP_NAME',prev_leg_rec1.prev_leg_trip_name);
4025 --}
4026 END IF;
4027 --
4028 IF l_debug_on THEN
4029 WSH_DEBUG_SV.log(l_module_name,'prev_stop_rec.shipments_type_flag',prev_stop_rec.shipments_type_flag);
4030 WSH_DEBUG_SV.log(l_module_name,'prev_stop_rec.stop_location_id',prev_stop_rec.stop_location_id);
4031 END IF;
4032 --
4033 --
4034 IF l_virtual_shipments_Type_flag = 'I'
4035 THEN
4036 --{
4037 IF p_in_rec.put_messages
4038 THEN
4039 --{
4040 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
4041 --}
4042 END IF;
4043 --
4044 --CLOSE prev_leg_csr;
4045 --
4046 RAISE wsh_util_core.e_not_allowed;
4047 --}
4048 ELSE
4049 --{
4050 IF p_in_rec.put_messages
4051 THEN
4052 --{
4053 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING,l_module_name);
4054 --}
4055 END IF;
4056 --
4057 --CLOSE prev_leg_csr;
4058 --
4059 l_stop_warnings := l_stop_warnings + 1;
4060 --
4061 EXIT;
4062 --}
4063 END IF;
4064 --}
4065 END LOOP;
4066 --
4067 --
4068 --
4069 l_stop_locationId := prev_leg_rec1.pu_stop_locationId;
4070 l_stop_Id := prev_leg_rec1.pu_stop_Id;
4071 --}
4072 END LOOP;
4073 --}
4074 END LOOP;
4075 --}
4076 END IF;
4077 --}
4078 END IF;
4079 --}
4080 END LOOP;
4081 --
4082 --
4083 -- If current stop is drop-off for any inbound (not O/IO) delivery,
4084 -- check all its prior stops.
4085 --
4086 -- If any prior stop is open then
4087 -- if current stop is inbound or prior stop is mixed
4088 -- then error
4089 -- else warning.
4090 --
4091 -- Following loop for cursor ib_pickup_csr validates the above.
4092 -- It first finds pickup stop for the delivery on current trip.
4093 -- Then it recursively traverses prior legs for the delivery (By passing pickup stop found above
4094 -- to prev_leg_csr), until it reaches starting(initial)
4095 -- pickup stop for the delivery.
4096 --
4097 FOR ib_pickup_rec IN ib_pickup_csr (p_in_rec.stop_id,
4098 l_linked_stop_info_rec.stop_id)
4099 LOOP
4100 --{
4101 IF l_debug_on THEN
4102 WSH_DEBUG_SV.log(l_module_name,'ib_pickup_rec.pu_stop_statusCode',ib_pickup_rec.pu_stop_statusCode);
4103 WSH_DEBUG_SV.log(l_module_name,'ib_pickup_rec.pu_shipments_type_flag',ib_pickup_rec.pu_shipments_type_flag);
4104 WSH_DEBUG_SV.log(l_module_name,'ib_pickup_rec.delivery_statusCode',ib_pickup_rec.delivery_statusCode);
4105 WSH_DEBUG_SV.log(l_module_name,'ib_pickup_rec.pu_stop_locationId',ib_pickup_rec.pu_stop_locationId);
4106 WSH_DEBUG_SV.log(l_module_name,'ib_pickup_rec.pu_stop_id',ib_pickup_rec.pu_stop_id);
4107 WSH_DEBUG_SV.log(l_module_name,'ib_pickup_rec.delivery_id',ib_pickup_rec.delivery_id);
4108 WSH_DEBUG_SV.log(l_module_name,'ib_pickup_rec.dlvy_initialPULocationId',ib_pickup_rec.dlvy_initialPULocationId);
4109 END IF;
4110 --
4111 IF ib_pickup_rec.pu_stop_statusCode in ('OP','AR')
4112 THEN
4113 --{
4114 null;
4115 --}
4116 ELSE --- stop is closed, check all prev. legs
4117 --{
4118 --IF ib_pickup_rec.delivery_statusCode = 'CL'
4119 IF ib_pickup_rec.delivery_statusCode in ('IT', 'CL')
4120 THEN
4121 --{
4122 l_stop_locationId := ib_pickup_rec.pu_stop_locationId;
4123 l_stop_Id := ib_pickup_rec.pu_stop_Id;
4124 --
4125 IF l_debug_on THEN
4126 WSH_DEBUG_SV.logmsg(l_module_name,'Checking all prior legs');
4127 END IF;
4128 --
4129 WHILE l_stop_locationId <> ib_pickup_rec.dlvy_initialPULocationId
4130 LOOP
4131 --{
4132 FOR prev_leg_rec IN prev_leg_csr
4133 (
4134 p_stop_id => l_stop_id,
4135 p_delivery_id => ib_pickup_rec.delivery_id
4136 )
4137 LOOP
4138 --{
4139
4140 IF l_debug_on THEN
4141 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec.do_stop_shipments_type_flag',prev_leg_rec.do_stop_shipments_type_flag);
4142 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec.do_stop_status_code',prev_leg_rec.do_stop_status_code);
4143 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec.do_stop_locationId',prev_leg_rec.do_stop_locationId);
4144 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec.do_stop_id',prev_leg_rec.do_stop_id);
4145 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec.pu_stop_shipments_type_flag',prev_leg_rec.pu_stop_shipments_type_flag);
4146 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec.pu_stop_status_code',prev_leg_rec.pu_stop_status_code);
4147 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec.pu_stop_locationId',prev_leg_rec.pu_stop_locationId);
4148 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec.pu_stop_id',prev_leg_rec.pu_stop_id);
4149 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec.shipment_direction',prev_leg_rec.shipment_direction);
4150 WSH_DEBUG_SV.log(l_module_name,'prev_leg_rec.dlvy_status_code',prev_leg_rec.dlvy_status_code);
4151 END IF;
4152 --
4153 --IF prev_leg_rec.do_stop_status_code in ('OP','AR')
4154 IF prev_leg_rec.do_stop_status_code in ('OP')
4155 THEN
4156 --{
4157 IF p_in_rec.put_messages
4158 THEN
4159 --{
4160 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_CLOSE_DO_DO_ERROR');
4161 FND_MESSAGE.SET_TOKEN('STOP_NAME', l_stop_name );
4162 --wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
4163 --}
4164 END IF;
4165 --
4166 IF l_virtual_shipments_Type_flag = 'I'
4167 THEN
4168 --{
4169 IF p_in_rec.put_messages
4170 THEN
4171 --{
4172 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
4173 --}
4174 END IF;
4175 --
4176 --CLOSE prev_leg_csr;
4177 --
4178 RAISE wsh_util_core.e_not_allowed;
4179 --}
4180 ELSE
4181 --{
4182 IF p_in_rec.put_messages
4183 THEN
4184 --{
4185 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING,l_module_name);
4186 --}
4187 END IF;
4188 --
4189 --CLOSE prev_leg_csr;
4190 --
4191 l_stop_warnings := l_stop_warnings + 1;
4192 --
4193 --EXIT;
4194 --}
4195 END IF;
4196 --}
4197 END IF;
4198 --
4199 --
4200 FOR prev_stop_rec IN prev_stop_csr
4201 (
4202 p_trip_id => prev_leg_rec.prev_leg_trip_id,
4203 p_linked_stop_id => l_linked_stop_info_rec.stop_id,
4204
4205 p_stop_sequence => prev_leg_rec.do_stop_sequence_number
4206 )
4207 LOOP
4208 --{
4209 IF p_in_rec.put_messages
4210 THEN
4211 --{
4212 FND_MESSAGE.SET_NAME('WSH','WSH_PREV_STOP_NOT_CLOSED');
4213 FND_MESSAGE.SET_TOKEN('STOP_NAME', l_stop_name );
4214 --
4215 l_prev_stop_name := SUBSTRB(
4216 WSH_UTIL_CORE.get_location_description
4217 (
4218 prev_stop_rec.stop_location_id,
4219 'NEW UI CODE'
4220 ),
4221 1,
4222 60
4223 );
4224 --
4225 FND_MESSAGE.SET_TOKEN('PREV_STOP_NAME',l_prev_stop_name);
4226 FND_MESSAGE.SET_TOKEN('TRIP_NAME',l_stop_info_rec.trip_name);
4227 FND_MESSAGE.SET_TOKEN('PREV_TRIP_NAME',prev_leg_rec.prev_leg_trip_name);
4228 --}
4229 END IF;
4230 --
4231 IF l_debug_on THEN
4232 WSH_DEBUG_SV.log(l_module_name,'prev_stop_rec.shipments_type_flag',prev_stop_rec.shipments_type_flag);
4233 WSH_DEBUG_SV.log(l_module_name,'prev_stop_rec.stop_location_id',prev_stop_rec.stop_location_id);
4234 END IF;
4235 --
4236 --
4237 IF l_virtual_shipments_Type_flag = 'I'
4238 THEN
4239 --{
4240 IF p_in_rec.put_messages
4241 THEN
4242 --{
4243 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
4244 --}
4245 END IF;
4246 --
4247 --CLOSE prev_leg_csr;
4248 --
4249 RAISE wsh_util_core.e_not_allowed;
4250 --}
4251 ELSE
4252 --{
4253 IF p_in_rec.put_messages
4254 THEN
4255 --{
4256 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING,l_module_name);
4257 --}
4258 END IF;
4259 --
4260 --CLOSE prev_leg_csr;
4261 --
4262 l_stop_warnings := l_stop_warnings + 1;
4263 --
4264 EXIT;
4265 --}
4266 END IF;
4267 --}
4268 END LOOP;
4269 --
4270 --
4271 l_stop_locationId := prev_leg_rec.pu_stop_locationId;
4272 l_stop_Id := prev_leg_rec.pu_stop_Id;
4273 --}
4274 END LOOP;
4275 --}
4276 END LOOP;
4277 --}
4278 END IF;
4279 --}
4280 END IF;
4281 --
4282 --CLOSE ib_pickup_csr;
4283 --}
4284 END LOOP;
4285 --
4286 --
4287 -- Initialize loop variables
4288 --
4289 l_prev_org_id := -999;
4290 l_err_dlvy_cnt := 0;
4291 l_dlvy_cnt := 0;
4292 -- J: W/V Changes
4293 l_wv_check_done := FALSE;
4294 --
4295 -- Get all deliveries which start(initial pickup location) at current stop,
4296 -- sorted by organization id
4297 --
4298 FOR l_initial_pu_dlvy_rec IN initial_pu_dlvy_csr(p_in_rec.stop_id)
4299 LOOP
4300 --{
4301 IF l_debug_on THEN
4302 WSH_DEBUG_SV.log(l_module_name,'l_initial_pu_dlvy_rec.organization_id',l_initial_pu_dlvy_rec.organization_id);
4303 WSH_DEBUG_SV.log(l_module_name,'l_prev_org_id',l_prev_org_id);
4304 END IF;
4305 --
4306 -- Since deliveries are sorted by organization_id,
4307 -- peform organization specific checks only when organization id changes
4308 --
4309 IF l_initial_pu_dlvy_rec.organization_id <> l_prev_org_id
4310 THEN
4311 --{
4312 l_prev_org_dlvy_cnt := l_dlvy_cnt;
4313 --
4314 IF l_debug_on THEN
4315 WSH_DEBUG_SV.log(l_module_name,'l_prev_org_dlvy_cnt',l_prev_org_dlvy_cnt);
4316 WSH_DEBUG_SV.log(l_module_name,'l_dlvy_cnt',l_dlvy_cnt);
4317 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.Get',WSH_DEBUG_SV.C_PROC_LEVEL);
4318 END IF;
4319 --
4320 WSH_SHIPPING_PARAMS_PVT.Get
4321 (
4322 p_organization_id => l_initial_pu_dlvy_rec.organization_id,
4323 x_param_info => l_param_info,
4324 x_return_status => l_return_status
4325 );
4326 --
4327 IF l_debug_on THEN
4328 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
4329 END IF;
4330 --
4331 WSH_UTIL_CORE.api_post_call
4332 (
4333 p_return_status => l_return_status,
4334 x_num_warnings => l_num_warnings,
4335 x_num_errors => l_num_errors
4336 );
4337
4338 IF l_debug_on THEN
4339 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters',WSH_DEBUG_SV.C_PROC_LEVEL);
4340 END IF;
4341
4342 --Bug 3346237: Allow future date and enforce ship method should take values from
4343 -- Global parameters table.
4344
4345 WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters
4346 (
4347 x_param_info => l_global_info,
4348 x_return_status => l_return_status
4349 );
4350 --
4351
4352 IF l_debug_on THEN
4353 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
4354 END IF;
4355 --
4356
4357 WSH_UTIL_CORE.api_post_call
4358 (
4359 p_return_status => l_return_status,
4360 x_num_warnings => l_num_warnings,
4361 x_num_errors => l_num_errors
4362 );
4363
4364 --
4365 --
4366 -- J: W/V Changes
4367 IF NOT l_wv_check_done THEN
4368 l_wv_check_done := TRUE;
4369
4370 IF (l_param_info.percent_fill_basis_flag = 'W' and l_stop_info_rec.departure_gross_weight is NULL) THEN
4371 FND_MESSAGE.SET_NAME('WSH','WSH_NULL_WV');
4372 IF l_debug_on THEN
4373 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
4374 END IF;
4375 --
4376 FND_MESSAGE.SET_TOKEN('ENTITY_TYPE','Stop');
4377 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',wsh_trip_stops_pvt.get_name(p_in_rec.stop_id,p_in_rec.caller));
4378 FND_MESSAGE.SET_TOKEN('WV','Weight');
4379 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4380 wsh_util_core.add_message(x_return_status);
4381 l_stop_warnings := l_stop_warnings + 1;
4382 END IF;
4383
4384 IF (l_param_info.percent_fill_basis_flag = 'V' and l_stop_info_rec.departure_volume is NULL) THEN
4385 FND_MESSAGE.SET_NAME('WSH','WSH_NULL_WV');
4386 IF l_debug_on THEN
4387 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
4388 END IF;
4389 --
4390 FND_MESSAGE.SET_TOKEN('ENTITY_TYPE','Stop');
4391 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',wsh_trip_stops_pvt.get_name(p_in_rec.stop_id,p_in_rec.caller));
4392 FND_MESSAGE.SET_TOKEN('WV','Volume');
4393 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4394 wsh_util_core.add_message(x_return_status);
4395 l_stop_warnings := l_stop_warnings + 1;
4396 END IF;
4397 END IF;
4398 --
4399 --
4400 -- Validate stop close date against today's date.
4401 -- Generate error/warning depending on shipping parameter "Allow future date"
4402 --
4403 IF p_in_rec.actual_date > sysdate THEN
4404 --{
4405 IF p_in_rec.put_messages
4406 THEN
4407 FND_MESSAGE.SET_NAME('WSH','WSH_ADEP_DATE_FUTURE');
4408 END IF;
4409 --
4410 IF (NVL(l_global_info.allow_future_ship_date, 'N') = 'Y')
4411 THEN
4412 --{
4413 IF p_in_rec.put_messages
4414 THEN
4415 --{
4416 WSH_UTIL_CORE.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING,l_module_name);
4417 --}
4418 END IF;
4419 --
4420 l_stop_warnings := l_stop_warnings + 1;
4421 --}
4422 ELSE
4423 --{
4424 IF p_in_rec.put_messages
4425 THEN
4426 --{
4427 WSH_UTIL_CORE.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
4428 --}
4429 END IF;
4430 --
4431 --CLOSE initial_pu_dlvy_csr;
4432 RAISE wsh_util_core.e_not_allowed;
4433 --}
4434 END IF;
4435 --}
4436 END IF;
4437 --
4438 --
4439 -- Check for open inventory period
4440 -- Error, if inventory period (corresponding to stop close date) is not open
4441 --
4442 -- Debug Statements
4443 --
4444 IF l_debug_on THEN
4445 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INVTTMTX.TDATECHK',WSH_DEBUG_SV.C_PROC_LEVEL);
4446 END IF;
4447 --
4448 invttmtx.tdatechk(l_initial_pu_dlvy_rec.organization_id, p_in_rec.actual_date, l_period_id, l_open_past_period);
4449
4450 IF (l_period_id <= 0)
4451 THEN
4452 --{
4453 IF p_in_rec.put_messages
4454 THEN
4455 --{
4456 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_DATE_UNOPEN_PERIOD');
4457 FND_MESSAGE.SET_TOKEN('STOP_NAME', l_stop_name );
4458 l_org_name := WSH_UTIL_CORE.Get_Org_Name(l_initial_pu_dlvy_rec.organization_id);
4459 FND_MESSAGE.SET_TOKEN('ORG_NAME', l_org_name );
4460 WSH_UTIL_CORE.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
4461 --}
4462 END IF;
4463 --
4464 --CLOSE initial_pu_dlvy_csr;
4465 RAISE wsh_util_core.e_not_allowed;
4466 --}
4467 END IF;
4468 --}
4469 END IF;
4470
4471 -- Added check for ultimate dropoff location for internal orders, if it changes
4472 IF NVL(l_initial_pu_dlvy_rec.io_location_id, NVL(l_prev_dropoff_location_id, -99))
4473 <> NVL(l_prev_dropoff_location_id, -99) THEN --{
4474 l_prev_dropoff_location_id := l_initial_pu_dlvy_rec.io_location_id;
4475
4476 OPEN get_org_id(l_initial_pu_dlvy_rec.delivery_id);
4477 FETCH get_org_id INTO l_rec_organization_id, l_dest_type_code;
4478 IF get_org_id%NOTFOUND THEN
4479 l_rec_organization_id := NULL;
4480 l_dest_type_code := NULL;
4481 END IF;
4482 CLOSE get_org_id;
4483
4484 IF l_rec_organization_id IS NOT NULL THEN --{
4485 IF l_debug_on THEN
4486 WSH_DEBUG_SV.logmsg(l_module_name,'Receiving Organization_id : '|| l_rec_organization_id ||
4487 ' , Destination Source Code : '|| l_dest_type_code);
4488 END IF;
4489 -- Validate Accounting Period Open only if its a Direct Transfer or InTransit Shipment to Expense Destination
4490 l_intransit_type := NULL;
4491 IF l_dest_type_code = 'EXPENSE' THEN
4492 l_validate_rec_org := TRUE;
4493 ELSE --{
4494 l_validate_rec_org := FALSE;
4495 -- Check Shipping Networks to find the Transit Type
4496 OPEN c_mtl_interorg_parameters( l_initial_pu_dlvy_rec.organization_id, l_rec_organization_id);
4497 FETCH c_mtl_interorg_parameters INTO l_intransit_type;
4498 IF c_mtl_interorg_parameters%FOUND THEN
4499 IF l_debug_on THEN
4500 WSH_DEBUG_SV.log(l_module_name,'Intransit Type', l_intransit_type);
4501 END IF;
4502 IF l_intransit_type = 1 THEN
4503 l_validate_rec_org := TRUE;
4504 END IF;
4505 END IF;
4506 CLOSE c_mtl_interorg_parameters; --}
4507 END IF;
4508
4509 IF l_validate_rec_org THEN --{
4510 -- Check for open inventory period
4511 IF l_debug_on THEN
4512 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INVTTMTX.TDATECHK',WSH_DEBUG_SV.C_PROC_LEVEL);
4513 END IF;
4514
4515 invttmtx.tdatechk(l_rec_organization_id, p_in_rec.actual_date, l_period_id, l_open_past_period);
4516
4517 IF (l_period_id <= 0) THEN
4518 IF p_in_rec.put_messages THEN
4519 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_DATE_UNOPEN_PERIOD');
4520 FND_MESSAGE.SET_TOKEN('STOP_NAME', l_stop_name );
4521 l_org_name := WSH_UTIL_CORE.Get_Org_Name(l_rec_organization_id);
4522 FND_MESSAGE.SET_TOKEN('ORG_NAME', l_org_name );
4523 WSH_UTIL_CORE.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
4524 END IF;
4525 RAISE wsh_util_core.e_not_allowed;
4526 END IF;
4527 END IF; --}
4528 END IF; --}
4529 END IF; --}
4530 --
4531 --
4532 -- Check if export compliance check is reqd. for this delivery org.
4533 --
4534 IF l_param_info.export_screening_flag in ('C', 'S', 'A')
4535 THEN
4536 --{
4537 l_itm_mark_dels(1) := l_initial_pu_dlvy_rec.delivery_id;
4538 --
4539 IF l_debug_on THEN
4540 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit wsh_delivery_validations.check_exception',WSH_DEBUG_SV.C_PROC_LEVEL);
4541 END IF;
4542 --
4543 wsh_delivery_validations.check_exception(
4544 p_deliveries_tab => l_itm_mark_dels,
4545 x_exceptions_exist => l_exceptions_exist,
4546 x_exceptions_tab => l_exceptions_tab,
4547 x_return_status => l_return_status);
4548 --
4549 IF l_debug_on THEN
4550 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
4551 END IF;
4552 --
4553 WSH_UTIL_CORE.api_post_call
4554 (
4555 p_return_status => l_return_status,
4556 x_num_warnings => l_num_warnings,
4557 x_num_errors => l_num_errors
4558 );
4559 --
4560 IF l_exceptions_exist = 'Y'
4561 AND l_exceptions_tab(1).severity in ('HIGH', 'MEDIUM')
4562 THEN
4563 --{
4564 IF p_in_rec.caller = 'SHIP_CONFIRM'
4565 THEN
4566 --{
4567 IF p_in_rec.put_messages
4568 THEN
4569 --{
4570 FND_MESSAGE.SET_NAME('WSH','WSH_ITM_COMPLIANCE_WARN');
4571 FND_MESSAGE.SET_TOKEN('DEL_NAME', l_initial_pu_dlvy_rec.name);
4572 WSH_UTIL_CORE.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING,l_module_name);
4573 --}
4574 END IF;
4575 --
4576 --CLOSE initial_pu_dlvy_csr;
4577 RAISE wsh_util_core.e_not_allowed_warning;
4578 --}
4579 ELSE
4580 --{
4581 IF p_in_rec.put_messages
4582 THEN
4583 --{
4584 FND_MESSAGE.SET_NAME('WSH','WSH_ITM_ERROR_STOP');
4585 FND_MESSAGE.SET_TOKEN('DEL_NAME', l_initial_pu_dlvy_rec.name);
4586 WSH_UTIL_CORE.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
4587 --}
4588 END IF;
4589 --
4590 --CLOSE initial_pu_dlvy_csr;
4591 RAISE wsh_util_core.e_not_allowed;
4592 --}
4593 END IF;
4594 --}
4595 END IF;
4596 --}
4597 END IF;
4598 --
4599 --
4600 l_in_rec.delivery_id := l_initial_pu_dlvy_rec.delivery_id;
4601 l_in_rec.name := l_initial_pu_dlvy_rec.name;
4602 l_in_rec.status_code := l_initial_pu_dlvy_rec.status_code;
4603 l_in_rec.put_messages := p_in_rec.put_messages;
4604 l_in_rec.actual_date := p_in_rec.actual_date;
4605 l_in_rec.manual_flag := 'N';
4606 l_in_rec.caller := p_in_rec.caller;
4607 l_in_rec.stop_id := p_in_Rec.stop_id;
4608 --
4609 --
4610 -- Debug Statements
4611 --
4612 IF l_debug_on THEN
4613 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_VALIDATIONS.check_inTransit',WSH_DEBUG_SV.C_PROC_LEVEL);
4614 END IF;
4615 --
4616 --
4617 -- Check if deliveries can be set to in-transit.
4618 -- If allowed, add delivery to output parameter x_out_rec.initial_pu_dlvy_recTbl
4619 -- If not allowed, add delivery to output parameter x_out_rec.initial_pu_err_dlvy_id_tbl
4620 --
4621 WSH_DELIVERY_VALIDATIONS.check_inTransit
4622 (
4623 p_in_rec => l_in_rec,
4624 x_return_status => l_return_status,
4625 x_allowed => l_Allowed
4626 );
4627 --
4628 IF l_debug_on THEN
4629 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
4630 WSH_DEBUG_SV.log(l_module_name,'l_Allowed',l_Allowed);
4631 END IF;
4632 --
4633 --
4634 WSH_UTIL_CORE.api_post_call
4635 (
4636 p_return_status => l_return_status,
4637 x_num_warnings => l_num_warnings,
4638 x_num_errors => l_num_errors
4639 );
4640 --
4641 IF l_Allowed IN ( 'YW', 'NW' )
4642 THEN
4643 l_num_warnings := l_num_warnings + 1;
4644 END IF;
4645 --
4646 IF l_allowed NOT IN ('Y', 'YW')
4647 THEN
4648 --{
4649 -- If not allowed, add delivery to output parameter x_out_rec.initial_pu_err_dlvy_id_tbl
4650 --
4651 l_err_dlvy_cnt := l_err_dlvy_cnt + 1;
4652 x_out_rec.initial_pu_err_dlvy_id_tbl(l_err_dlvy_cnt) := l_initial_pu_dlvy_rec.delivery_id;
4653 --}
4654 ELSE
4655 --{
4656 -- If allowed, add delivery to output parameter x_out_rec.initial_pu_dlvy_recTbl
4657 --
4658 l_dlvy_cnt := l_dlvy_cnt + 1;
4659 --
4660 x_out_rec.initial_pu_dlvy_recTbl.id_tbl(l_dlvy_cnt) := l_initial_pu_dlvy_rec.delivery_id;
4661 x_out_rec.initial_pu_dlvy_recTbl.name_tbl(l_dlvy_cnt) := l_initial_pu_dlvy_rec.name;
4662 x_out_rec.initial_pu_dlvy_recTbl.orgId_tbl(l_dlvy_cnt) := l_initial_pu_dlvy_rec.organization_id;
4663 x_out_rec.initial_pu_dlvy_recTbl.statusCode_tbl(l_dlvy_cnt) := l_initial_pu_dlvy_rec.status_code;
4664 --}
4665 END IF;
4666 --
4667 --
4668 --IF l_dlvy_cnt > l_prev_org_dlvy_cnt
4669 --
4670 -- Condition below indicates first delivery being processed for an organization
4671 --
4672 IF l_dlvy_cnt = l_prev_org_dlvy_cnt+1
4673 THEN
4674 --{
4675 --
4676 -- If shipping parameter "Enforce ship method" is true
4677 -- and ship method is null on the trip, stop close cannot be allowed.
4678 --
4679 IF l_global_info.enforce_ship_method = 'Y'
4680 AND l_stop_info_rec.ship_method_code IS NULL
4681 THEN
4682 --{
4683 IF p_in_rec.put_messages
4684 THEN
4685 --{
4686 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_SM_NOT_FOUND');
4687 WSH_UTIL_CORE.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
4688 --}
4689 END IF;
4690 --
4691 --CLOSE initial_pu_dlvy_csr;
4692 RAISE wsh_util_core.e_not_allowed;
4693 --}
4694 END IF;
4695 --}
4696 END IF;
4697 --
4698 --
4699 l_prev_org_id := l_initial_pu_dlvy_rec.organization_id;
4700 --}
4701 END LOOP;
4702 --
4703 IF l_debug_on THEN
4704 WSH_DEBUG_SV.log(l_module_name,'l_dlvy_cnt',l_dlvy_cnt);
4705 WSH_DEBUG_SV.log(l_module_name,'l_err_dlvy_cnt',l_err_dlvy_cnt);
4706 END IF;
4707 --
4708 -- IF all deliveries errored out, stop close cannot be allowed.
4709 --
4710 IF l_dlvy_cnt = 0 AND l_err_dlvy_cnt > 0
4711 THEN
4712 --{
4713 RAISE wsh_util_core.e_not_allowed;
4714 --}
4715 END IF;
4716 --
4717 --
4718 l_dlvy_cnt := 0;
4719 --
4720 -- Get all deliveries which end(ultimate dropoff location) at current stop,
4721 -- sorted by organization id
4722 --
4723 FOR l_ultimate_do_dlvy_rec IN ultimate_do_dlvy_csr(p_in_rec.stop_id,
4724 l_linked_stop_info_rec.stop_id)
4725 LOOP
4726 --{
4727 --
4728 l_in_rec.delivery_id := l_ultimate_do_dlvy_rec.delivery_id;
4729 l_in_rec.name := l_ultimate_do_dlvy_rec.name;
4730 l_in_rec.status_code := l_ultimate_do_dlvy_rec.status_code;
4731 l_in_rec.put_messages := p_in_rec.put_messages;
4732 l_in_rec.actual_date := p_in_rec.actual_date;
4733 l_in_rec.manual_flag := 'N';
4734 l_in_rec.caller := p_in_rec.caller;
4735 l_in_rec.stop_id := l_ultimate_do_dlvy_rec.drop_off_stop_id;
4736 --
4737 --
4738 -- Debug Statements
4739 --
4740 IF l_debug_on THEN
4741 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_VALIDATIONS.check_close',WSH_DEBUG_SV.C_PROC_LEVEL);
4742 END IF;
4743 --
4744 -- Check if delivery can be closed.
4745 -- If so, add it to out parameter x_out_rec.ultimate_do_dlvy_recTbl
4746 --
4747 WSH_DELIVERY_VALIDATIONS.check_close
4748 (
4749 p_in_rec => l_in_rec,
4750 x_return_status => l_return_status,
4751 x_allowed => l_Allowed
4752 );
4753 --
4754 IF l_debug_on THEN
4755 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
4756 WSH_DEBUG_SV.log(l_module_name,'l_Allowed',l_Allowed);
4757 END IF;
4758 --
4759 --
4760 WSH_UTIL_CORE.api_post_call
4761 (
4762 p_return_status => l_return_status,
4763 x_num_warnings => l_num_warnings,
4764 x_num_errors => l_num_errors
4765 );
4766 --
4767 IF l_Allowed IN ( 'YW', 'NW' )
4768 THEN
4769 l_num_warnings := l_num_warnings + 1;
4770 END IF;
4771 --
4772 IF l_allowed NOT IN ('Y', 'YW')
4773 THEN
4774 --{
4775 NULL;
4776 --}
4777 ELSE
4778 --{
4779 l_dlvy_cnt := l_dlvy_cnt + 1;
4780 --
4781 x_out_rec.ultimate_do_dlvy_recTbl.id_tbl(l_dlvy_cnt) := l_ultimate_do_dlvy_rec.delivery_id;
4782 x_out_rec.ultimate_do_dlvy_recTbl.name_tbl(l_dlvy_cnt) := l_ultimate_do_dlvy_rec.name;
4783 x_out_rec.ultimate_do_dlvy_recTbl.orgId_tbl(l_dlvy_cnt) := l_ultimate_do_dlvy_rec.organization_id;
4784 x_out_rec.ultimate_do_dlvy_recTbl.statusCode_tbl(l_dlvy_cnt) := l_ultimate_do_dlvy_rec.status_code;
4785 --}
4786 END IF;
4787 --}
4788 END LOOP;
4789 --
4790 --
4791 l_trip_in_rec.trip_id := l_stop_info_rec.trip_id;
4792 l_trip_in_rec.put_messages := p_in_rec.put_messages;
4793 l_trip_in_rec.actual_date := p_in_rec.actual_date;
4794 l_trip_in_rec.manual_flag := 'N';
4795 l_trip_in_rec.caller := p_in_rec.caller;
4796 l_trip_in_rec.stop_id := p_in_Rec.stop_id;
4797 l_trip_in_rec.name := l_stop_info_rec.trip_name;
4798 l_trip_in_rec.linked_stop_id := l_linked_stop_info_rec.stop_id;
4799 --
4800 IF l_stop_info_rec.trip_status_code = 'OP'
4801 THEN
4802 --{
4803 -- Trip is set to in-transit, whenever any stop closes.
4804 --
4805 -- Debug Statements
4806 --
4807 IF l_debug_on THEN
4808 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_VALIDATIONS.check_inTransit',WSH_DEBUG_SV.C_PROC_LEVEL);
4809 END IF;
4810 --
4811 -- Check if trip can be set to In-Transit.
4812 --
4813 WSH_TRIP_VALIDATIONS.check_inTransit
4814 (
4815 p_in_rec => l_trip_in_rec,
4816 x_return_status => l_return_status,
4817 x_allowed => l_Allowed
4818 );
4819 --
4820 IF l_debug_on THEN
4821 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
4822 WSH_DEBUG_SV.log(l_module_name,'l_Allowed',l_Allowed);
4823 END IF;
4824 --
4825 --
4826 WSH_UTIL_CORE.api_post_call
4827 (
4828 p_return_status => l_return_status,
4829 x_num_warnings => l_num_warnings,
4830 x_num_errors => l_num_errors
4831 );
4832 --
4833 IF l_Allowed = 'N'
4834 THEN
4835 -- If trip cannot be set to In-transit, the stop cannot be closed
4836 l_num_warnings := l_num_warnings + 1;
4837 RAISE wsh_util_core.e_not_allowed;
4838 END IF;
4839 --
4840 IF l_Allowed IN ( 'YW', 'NW' )
4841 THEN
4842 l_num_warnings := l_num_warnings + 1;
4843 END IF;
4844 --
4845 IF l_allowed IN ('Y', 'YW')
4846 THEN
4847 --{
4848 -- If trip can be set to In-transit, set out parameter x_out_rec.trip_new_status_code to 'IT'
4849 x_out_rec.trip_new_status_code := 'IT';
4850 --}
4851 END IF;
4852 --}
4853 ELSIF l_stop_info_rec.trip_status_code = 'IT'
4854 THEN
4855 --{
4856 l_trip_in_rec.new_Status_code := 'CL';
4857 --
4858 -- Debug Statements
4859 --
4860 IF l_debug_on THEN
4861 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_VALIDATIONS.check_close',WSH_DEBUG_SV.C_PROC_LEVEL);
4862 END IF;
4863 --
4864 -- Check if trip can be closed.
4865 --
4866 WSH_TRIP_VALIDATIONS.check_close
4867 (
4868 p_in_rec => l_trip_in_rec,
4869 x_return_status => l_return_status,
4870 x_allowed => l_Allowed
4871 );
4872 --
4873 IF l_debug_on THEN
4874 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
4875 WSH_DEBUG_SV.log(l_module_name,'l_Allowed',l_Allowed);
4876 END IF;
4877 --
4878 --
4879 WSH_UTIL_CORE.api_post_call
4880 (
4881 p_return_status => l_return_status,
4882 x_num_warnings => l_num_warnings,
4883 x_num_errors => l_num_errors
4884 );
4885 --
4886 IF l_Allowed = 'N'
4887 THEN
4888 -- If trip cannot be closed, the stop cannot be closed
4889 l_num_warnings := l_num_warnings + 1;
4890 RAISE wsh_util_core.e_not_allowed;
4891 END IF;
4892 --
4893 IF l_Allowed IN ( 'YW', 'NW' )
4894 THEN
4895 l_num_warnings := l_num_warnings + 1;
4896 END IF;
4897 --
4898 IF l_allowed IN ('Y', 'YW')
4899 THEN
4900 --{
4901 -- If trip can be closed, set out parameter x_out_rec.trip_new_status_code to 'CL'
4902 --
4903 x_out_rec.trip_new_status_code := l_trip_in_rec.new_Status_code;
4904 --}
4905 END IF;
4906 --}
4907 END IF;
4908 --
4909 --
4910 IF l_num_errors > 0
4911 THEN
4912 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4913 x_out_rec.close_allowed := 'N';
4914 ELSIF l_num_warnings > 0
4915 THEN
4916 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4917 ELSE
4918 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4919 END IF;
4920 --
4921 --
4922 IF l_stop_warnings > 0
4923 THEN
4924 x_out_rec.close_allowed := 'YW';
4925 ELSE
4926 x_out_rec.close_allowed := 'Y';
4927 END IF;
4928 --
4929 -- Debug Statements
4930 --
4931 IF l_debug_on THEN
4932 WSH_DEBUG_SV.pop(l_module_name);
4933 END IF;
4934 --
4935 --}
4936 EXCEPTION
4937 --{
4938 WHEN wsh_util_core.e_not_allowed THEN
4939 IF l_num_warnings > 0
4940 THEN
4941 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4942 ELSE
4943 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4944 END IF;
4945 --
4946 x_out_rec.close_allowed := 'N';
4947 --
4948 --
4949 IF l_debug_on THEN
4950 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_util_core.e_not_allowed exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4951 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_util_core.e_not_allowed');
4952 END IF;
4953 --
4954 WHEN wsh_util_core.e_not_allowed_warning THEN
4955 IF l_num_warnings > 0
4956 THEN
4957 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4958 ELSE
4959 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4960 END IF;
4961 --
4962 x_out_Rec.close_allowed := 'NW';
4963 --
4964 --
4965 IF l_debug_on THEN
4966 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4967 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4968 END IF;
4969 --
4970 WHEN FND_API.G_EXC_ERROR THEN
4971
4972 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4973 --
4974 IF l_debug_on THEN
4975 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4976 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4977 END IF;
4978 --
4979 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4980
4981 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4982 --
4983 IF l_debug_on THEN
4984 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4985 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
4986 END IF;
4987 --
4988 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
4989 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4990 --
4991 IF l_debug_on THEN
4992 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4993 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
4994 END IF;
4995 --
4996 WHEN OTHERS THEN
4997 IF linked_stop_info_csr%ISOPEN THEN
4998 CLOSE linked_stop_info_csr;
4999 END IF;
5000 IF wsh_linked_stop_csr%ISOPEN THEN
5001 CLOSE wsh_linked_stop_csr;
5002 END IF;
5003 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
5004 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.CHECK_STOP_CLOSE',l_module_name);
5005 --
5006 IF l_debug_on THEN
5007 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5008 END IF;
5009 --
5010 --}
5011 END Check_Stop_Close;
5012 --
5013 --
5014 --
5015 --========================================================================
5016 -- PROCEDURE : get_stop_close_date
5017 --
5018 -- PARAMETERS: p_in_rec Input Record (Refer to WSHSTVLS.pls for description)
5019 -- p_out_rec Output record (Refer to WSHSTVLS.pls for description)
5020 -- x_return_status Return status of API
5021 --
5022 --
5023 -- COMMENT : This procedure calculate stop close date.
5024 -- This is to be used only for inbound logistics project.
5025 -- This is called when automatically closing stops while processing ASN/Receipt.
5026 --
5027 -- The calculation is as follows:
5028 -- 01. Find all deliveries associated with the input stop.
5029 -- - Deliveries are sorted and grouped as per following order
5030 -- 1. Deliveries starting from this stop. (consider initial pickup date)
5031 -- 2. Deliveries ending at this stop. (consider ultimate dropoff date)
5032 -- 3. Deliveries being picked up at this stop. (consider initial pickup date)
5033 -- 4. Deliveries being dropped of at this stop. (consider ultimate dropoff date)
5034 -- 02. API tries to find maximum date within a group.
5035 -- 03. Once a date is found, it skips remaining groups.
5036 -- 04. If calculated date is less than close date of previous stop,
5037 -- set it to close date of previous stop.
5038 -- 05. If calculated date is greater than close date of next stop,
5039 -- set it to close date of next stop.
5040 --========================================================================
5041 --
5042 PROCEDURE get_stop_close_date
5043 (
5044 p_trip_id IN NUMBER,
5045 p_stop_id IN NUMBER,
5046 p_stop_sequence_number IN NUMBER,
5047 x_stop_close_date OUT NOCOPY DATE,
5048 x_return_status OUT NOCOPY VARCHAR2
5049 )
5050 IS
5051 --{
5052 -- Find all deliveries associated with the input stop.
5053 -- - Deliveries are sorted and grouped as per following order
5054 -- 1. Deliveries starting from this stop. (consider initial pickup date)
5055 -- 2. Deliveries ending at this stop. (consider ultimate dropoff date)
5056 -- 3. Deliveries being picked up at this stop. (consider initial pickup date)
5057 -- 4. Deliveries being dropped of at this stop. (consider ultimate dropoff date)
5058 --
5059 CURSOR ib_dlvy_cur (p_stop_id IN NUMBER)
5060 IS
5061 SELECT wnd.initial_pickup_date delivery_Date,
5062 DECODE(wts.stop_location_id,wnd.initial_pickup_location_id,1,3) order_seq
5063 FROM wsh_new_deliveries wnd,
5064 wsh_delivery_legs wdl,
5065 wsh_trip_stops wts
5066 WHERE wts.stop_id = p_stop_id
5067 AND wdl.pick_up_stop_id = wts.stop_id
5068 AND wdl.delivery_id = wnd.delivery_id
5069 UNION
5070 SELECT wnd.ultimate_dropoff_date delivery_Date,
5071 DECODE(wts.stop_location_id,wnd.ultimate_dropoff_location_id,2,4) order_seq
5072 FROM wsh_new_deliveries wnd,
5073 wsh_delivery_legs wdl,
5074 wsh_trip_stops wts
5075 WHERE wts.stop_id = p_stop_id
5076 AND wdl.drop_off_stop_id = wts.stop_id
5077 AND wdl.delivery_id = wnd.delivery_id
5078 ORDER BY order_seq ASC;
5079 --
5080 CURSOR prev_stop_cur (p_trip_id IN NUMBER, p_stop_sequence_number IN NUMBER)
5081 IS
5082 SELECT actual_departure_date
5083 FROM wsh_trip_stops
5084 WHERE trip_id = p_trip_id
5085 AND stop_sequence_number < p_stop_sequence_number
5086 ORDER BY stop_sequence_number DESC;
5087 --
5088 CURSOR next_stop_cur (p_trip_id IN NUMBER, p_stop_sequence_number IN NUMBER)
5089 IS
5090 SELECT actual_departure_date
5091 FROM wsh_trip_stops
5092 WHERE trip_id = p_trip_id
5093 AND stop_sequence_number > p_stop_sequence_number
5094 ORDER BY stop_sequence_number ASC;
5095 --
5096 l_return_status VARCHAR2(1);
5097 l_num_warnings NUMBER;
5098 l_num_errors NUMBER;
5099 --
5100 l_actual_date DATE := NULL;
5101 l_prev_order_seq NUMBER := 0;
5102 --
5103 l_prevStop_departure_date DATE;
5104 l_nextStop_departure_date DATE;
5105 --
5106 l_debug_on BOOLEAN;
5107 --
5108 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_stop_close_date';
5109 --
5110 --}
5111 BEGIN
5112 --{
5113 --
5114 -- Debug Statements
5115 --
5116 --
5117 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5118 --
5119 IF l_debug_on IS NULL
5120 THEN
5121 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5122 END IF;
5123 --
5124 IF l_debug_on THEN
5125 WSH_DEBUG_SV.push(l_module_name);
5126 --
5127 WSH_DEBUG_SV.log(l_module_name,'p_trip_id',p_trip_id);
5128 WSH_DEBUG_SV.log(l_module_name,'p_stop_id',p_stop_id);
5129 WSH_DEBUG_SV.log(l_module_name,'p_stop_sequence_number',p_stop_sequence_number);
5130 END IF;
5131 --
5132 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5133 --
5134 FOR ib_dlvy_rec IN ib_dlvy_cur(p_stop_id)
5135 LOOP
5136 --{
5137 IF l_prev_order_seq <> ib_dlvy_rec.order_seq
5138 AND l_actual_date IS NOT NULL
5139 THEN
5140 EXIT;
5141 END IF;
5142 --
5143 IF l_actual_date IS NULL
5144 OR l_actual_date < ib_dlvy_rec.delivery_date
5145 THEN
5146 l_actual_date := ib_dlvy_rec.delivery_date;
5147 END IF ;
5148 --
5149 l_prev_order_seq := ib_dlvy_rec.order_seq;
5150 --}
5151 END LOOP;
5152 --
5153 --
5154 l_actual_date := NVL(l_actual_date,SYSDATE);
5155 --
5156 l_prevStop_departure_date := NULL;
5157 l_nextStop_departure_date := NULL;
5158 --
5159 OPEN prev_stop_cur
5160 (
5161 p_trip_id => p_trip_id,
5162 p_stop_sequence_number => p_stop_sequence_number
5163 );
5164 --
5165 FETCH prev_stop_cur INTO l_prevStop_departure_date;
5166 CLOSE prev_stop_cur;
5167 --
5168 IF l_actual_date < l_prevStop_departure_date
5169 THEN
5170 l_actual_date := l_prevStop_departure_date;
5171 END IF;
5172 --
5173 OPEN next_stop_cur
5174 (
5175 p_trip_id => p_trip_id,
5176 p_stop_sequence_number => p_stop_sequence_number
5177 );
5178 --
5179 FETCH next_stop_cur INTO l_nextStop_departure_date;
5180 CLOSE next_stop_cur;
5181 --
5182 IF l_actual_date > l_nextStop_departure_date
5183 THEN
5184 l_actual_date := l_nextStop_departure_date;
5185 END IF;
5186 --
5187 x_stop_close_date := l_actual_date;
5188 --
5189 -- Debug Statements
5190 --
5191 IF l_debug_on THEN
5192 WSH_DEBUG_SV.log(l_module_name,'l_actual_date',l_actual_date);
5193 WSH_DEBUG_SV.pop(l_module_name);
5194 END IF;
5195 --}
5196 EXCEPTION
5197 --
5198 WHEN FND_API.G_EXC_ERROR THEN
5199
5200 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5201 --
5202 IF l_debug_on THEN
5203 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5204 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
5205 END IF;
5206 --
5207 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5208
5209 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
5210 --
5211 IF l_debug_on THEN
5212 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5213 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
5214 END IF;
5215 --
5216 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
5217 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
5218 --
5219 IF l_debug_on THEN
5220 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5221 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
5222 END IF;
5223 --
5224 WHEN OTHERS THEN
5225
5226 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
5227 wsh_util_core.default_handler('WSH_TRIP_STOPS_VALIDATIONS.get_stop_close_date', l_module_name);
5228 IF l_debug_on THEN
5229 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5230 END IF;
5231 --
5232 END get_stop_close_date;
5233
5234
5235
5236 -- J-IB-NPARIKH-}
5237
5238
5239 --3509004:public api change
5240 PROCEDURE user_non_updatable_columns
5241 (p_user_in_rec IN WSH_TRIP_STOPS_PVT.trip_stop_rec_type,
5242 p_out_rec IN WSH_TRIP_STOPS_PVT.trip_stop_rec_type,
5243 p_in_rec IN WSH_TRIP_STOPS_GRP.stopInRecType,
5244 x_return_status OUT NOCOPY VARCHAR2)
5245
5246 IS
5247 l_attributes VARCHAR2(2500) ;
5248 k number;
5249 l_return_status VARCHAR2(1);
5250 l_debug_on BOOLEAN;
5251 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'user_non_updatable_columns';
5252
5253 BEGIN
5254
5255 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5256 --
5257 IF l_debug_on IS NULL
5258 THEN
5259 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5260 END IF;
5261 --
5262 IF l_debug_on THEN
5263 --
5264 WSH_DEBUG_SV.push(l_module_name);
5265 WSH_DEBUG_SV.log(l_module_name,'p_in_rec.caller',p_in_rec.caller);
5266 --
5267 END IF;
5268 --
5269 x_return_status := FND_API.G_RET_STS_SUCCESS;
5270 --
5271 IF p_user_in_rec.STOP_ID <> FND_API.G_MISS_NUM
5272 AND NVL(p_user_in_rec.STOP_ID,-99) <> NVL(p_out_rec.STOP_ID,-99)
5273 THEN
5274 l_attributes := l_attributes || 'STOP_ID, ';
5275 END IF;
5276
5277 IF p_user_in_rec.TRIP_ID <> FND_API.G_MISS_NUM
5278 AND NVL(p_user_in_rec.TRIP_ID,-99) <> NVL(p_out_rec.TRIP_ID,-99)
5279 THEN
5280 l_attributes := l_attributes || 'TRIP_ID, ';
5281 END IF;
5282
5283 IF p_user_in_rec.STOP_LOCATION_ID <> FND_API.G_MISS_NUM
5284 AND NVL(p_user_in_rec.STOP_LOCATION_ID,-99) <> NVL(p_out_rec.STOP_LOCATION_ID,-99)
5285 THEN
5286 l_attributes := l_attributes || 'STOP_LOCATION_ID, ';
5287 END IF;
5288
5289 IF p_user_in_rec.STATUS_CODE <> FND_API.G_MISS_CHAR
5290 AND NVL(p_user_in_rec.STATUS_CODE,'!!!') <> NVL(p_out_rec.STATUS_CODE,'!!!')
5291 THEN
5292 l_attributes := l_attributes || 'STATUS_CODE, ';
5293 END IF;
5294
5295 IF p_user_in_rec.STOP_SEQUENCE_NUMBER <> FND_API.G_MISS_NUM
5296 AND NVL(p_user_in_rec.STOP_SEQUENCE_NUMBER,-99) <> NVL(p_out_rec.STOP_SEQUENCE_NUMBER,-99)
5297 THEN
5298 l_attributes := l_attributes || 'STOP_SEQUENCE_NUMBER, ';
5299 END IF;
5300
5301 IF p_user_in_rec.PLANNED_ARRIVAL_DATE <> FND_API.G_MISS_DATE
5302 AND NVL(p_user_in_rec.PLANNED_ARRIVAL_DATE,TO_DATE('2','j')) <> NVL(p_out_rec.PLANNED_ARRIVAL_DATE,TO_DATE('2','j'))
5303 THEN
5304 l_attributes := l_attributes || 'PLANNED_ARRIVAL_DATE, ';
5305 END IF;
5306
5307 IF p_user_in_rec.PLANNED_DEPARTURE_DATE <> FND_API.G_MISS_DATE
5308 AND NVL(p_user_in_rec.PLANNED_DEPARTURE_DATE,TO_DATE('2','j')) <> NVL(p_out_rec.PLANNED_DEPARTURE_DATE,TO_DATE('2','j'))
5309 THEN
5310 l_attributes := l_attributes || 'PLANNED_DEPARTURE_DATE, ';
5311 END IF;
5312
5313 IF p_user_in_rec.ACTUAL_ARRIVAL_DATE <> FND_API.G_MISS_DATE
5314 AND NVL(p_user_in_rec.ACTUAL_ARRIVAL_DATE,TO_DATE('2','j')) <> NVL(p_out_rec.ACTUAL_ARRIVAL_DATE,TO_DATE('2','j'))
5315 THEN
5316 l_attributes := l_attributes || 'ACTUAL_ARRIVAL_DATE, ';
5317 END IF;
5318
5319 IF p_user_in_rec.ACTUAL_DEPARTURE_DATE <> FND_API.G_MISS_DATE
5320 AND NVL(p_user_in_rec.ACTUAL_DEPARTURE_DATE,TO_DATE('2','j')) <> NVL(p_out_rec.ACTUAL_DEPARTURE_DATE,TO_DATE('2','j'))
5321 THEN
5322 l_attributes := l_attributes || 'ACTUAL_DEPARTURE_DATE, ';
5323 END IF;
5324
5325 IF p_user_in_rec.DEPARTURE_GROSS_WEIGHT <> FND_API.G_MISS_NUM
5326 AND NVL(p_user_in_rec.DEPARTURE_GROSS_WEIGHT,-99) <> NVL(p_out_rec.DEPARTURE_GROSS_WEIGHT,-99)
5327 THEN
5328 l_attributes := l_attributes || 'DEPARTURE_GROSS_WEIGHT, ';
5329 END IF;
5330
5331 IF p_user_in_rec.DEPARTURE_NET_WEIGHT <> FND_API.G_MISS_NUM
5332 AND NVL(p_user_in_rec.DEPARTURE_NET_WEIGHT,-99) <> NVL(p_out_rec.DEPARTURE_NET_WEIGHT,-99)
5333 THEN
5334 l_attributes := l_attributes || 'DEPARTURE_NET_WEIGHT, ';
5335 END IF;
5336
5337 IF p_user_in_rec.WEIGHT_UOM_CODE <> FND_API.G_MISS_CHAR
5338 AND NVL(p_user_in_rec.WEIGHT_UOM_CODE,'!!!') <> NVL(p_out_rec.WEIGHT_UOM_CODE,'!!!')
5339 THEN
5340 l_attributes := l_attributes || 'WEIGHT_UOM_CODE, ';
5341 END IF;
5342
5343 IF p_user_in_rec.DEPARTURE_VOLUME <> FND_API.G_MISS_NUM
5344 AND NVL(p_user_in_rec.DEPARTURE_VOLUME,-99) <> NVL(p_out_rec.DEPARTURE_VOLUME,-99)
5345 THEN
5346 l_attributes := l_attributes || 'DEPARTURE_VOLUME, ';
5347 END IF;
5348
5349 IF p_user_in_rec.VOLUME_UOM_CODE <> FND_API.G_MISS_CHAR
5350 AND NVL(p_user_in_rec.VOLUME_UOM_CODE,'!!!') <> NVL(p_out_rec.VOLUME_UOM_CODE,'!!!')
5351 THEN
5352 l_attributes := l_attributes || 'VOLUME_UOM_CODE, ';
5353 END IF;
5354
5355 IF p_user_in_rec.DEPARTURE_SEAL_CODE <> FND_API.G_MISS_CHAR
5356 AND NVL(p_user_in_rec.DEPARTURE_SEAL_CODE,'!!!') <> NVL(p_out_rec.DEPARTURE_SEAL_CODE,'!!!')
5357 THEN
5358 l_attributes := l_attributes || 'DEPARTURE_SEAL_CODE, ';
5359 END IF;
5360
5361 IF p_user_in_rec.DEPARTURE_FILL_PERCENT <> FND_API.G_MISS_NUM
5362 AND NVL(p_user_in_rec.DEPARTURE_FILL_PERCENT,-99) <> NVL(p_out_rec.DEPARTURE_FILL_PERCENT,-99)
5363 THEN
5364 l_attributes := l_attributes || 'DEPARTURE_FILL_PERCENT, ';
5365 END IF;
5366
5367 IF p_user_in_rec.TP_ATTRIBUTE_CATEGORY <> FND_API.G_MISS_CHAR
5368 AND NVL(p_user_in_rec.TP_ATTRIBUTE_CATEGORY,'!!!') <> NVL(p_out_rec.TP_ATTRIBUTE_CATEGORY,'!!!')
5369 THEN
5370 l_attributes := l_attributes || 'TP_ATTRIBUTE_CATEGORY, ';
5371 END IF;
5372
5373 IF p_user_in_rec.TP_ATTRIBUTE1 <> FND_API.G_MISS_CHAR
5374 AND NVL(p_user_in_rec.TP_ATTRIBUTE1,'!!!') <> NVL(p_out_rec.TP_ATTRIBUTE1,'!!!')
5375 THEN
5376 l_attributes := l_attributes || 'TP_ATTRIBUTE1, ';
5377 END IF;
5378
5379 IF p_user_in_rec.TP_ATTRIBUTE2 <> FND_API.G_MISS_CHAR
5380 AND NVL(p_user_in_rec.TP_ATTRIBUTE2,'!!!') <> NVL(p_out_rec.TP_ATTRIBUTE2,'!!!')
5381 THEN
5382 l_attributes := l_attributes || 'TP_ATTRIBUTE2, ';
5383 END IF;
5384
5385 IF p_user_in_rec.TP_ATTRIBUTE3 <> FND_API.G_MISS_CHAR
5386 AND NVL(p_user_in_rec.TP_ATTRIBUTE3,'!!!') <> NVL(p_out_rec.TP_ATTRIBUTE3,'!!!')
5387 THEN
5388 l_attributes := l_attributes || 'TP_ATTRIBUTE3, ';
5389 END IF;
5390
5391 IF p_user_in_rec.TP_ATTRIBUTE4 <> FND_API.G_MISS_CHAR
5392 AND NVL(p_user_in_rec.TP_ATTRIBUTE4,'!!!') <> NVL(p_out_rec.TP_ATTRIBUTE4,'!!!')
5393 THEN
5394 l_attributes := l_attributes || 'TP_ATTRIBUTE4, ';
5395 END IF;
5396
5397 IF p_user_in_rec.TP_ATTRIBUTE5 <> FND_API.G_MISS_CHAR
5398 AND NVL(p_user_in_rec.TP_ATTRIBUTE5,'!!!') <> NVL(p_out_rec.TP_ATTRIBUTE5,'!!!')
5399 THEN
5400 l_attributes := l_attributes || 'TP_ATTRIBUTE5, ';
5401 END IF;
5402
5403 IF p_user_in_rec.TP_ATTRIBUTE6 <> FND_API.G_MISS_CHAR
5404 AND NVL(p_user_in_rec.TP_ATTRIBUTE6,'!!!') <> NVL(p_out_rec.TP_ATTRIBUTE6,'!!!')
5405 THEN
5406 l_attributes := l_attributes || 'TP_ATTRIBUTE6, ';
5407 END IF;
5408
5409 IF p_user_in_rec.TP_ATTRIBUTE7 <> FND_API.G_MISS_CHAR
5410 AND NVL(p_user_in_rec.TP_ATTRIBUTE7,'!!!') <> NVL(p_out_rec.TP_ATTRIBUTE7,'!!!')
5411 THEN
5412 l_attributes := l_attributes || 'TP_ATTRIBUTE7, ';
5413 END IF;
5414
5415 IF p_user_in_rec.TP_ATTRIBUTE8 <> FND_API.G_MISS_CHAR
5416 AND NVL(p_user_in_rec.TP_ATTRIBUTE8,'!!!') <> NVL(p_out_rec.TP_ATTRIBUTE8,'!!!')
5417 THEN
5418 l_attributes := l_attributes || 'TP_ATTRIBUTE8, ';
5419 END IF;
5420
5421 IF p_user_in_rec.TP_ATTRIBUTE9 <> FND_API.G_MISS_CHAR
5422 AND NVL(p_user_in_rec.TP_ATTRIBUTE9,'!!!') <> NVL(p_out_rec.TP_ATTRIBUTE9,'!!!')
5423 THEN
5424 l_attributes := l_attributes || 'TP_ATTRIBUTE9, ';
5425 END IF;
5426
5427 IF p_user_in_rec.TP_ATTRIBUTE10 <> FND_API.G_MISS_CHAR
5428 AND NVL(p_user_in_rec.TP_ATTRIBUTE10,'!!!') <> NVL(p_out_rec.TP_ATTRIBUTE10,'!!!')
5429 THEN
5430 l_attributes := l_attributes || 'TP_ATTRIBUTE10, ';
5431 END IF;
5432
5433 IF p_user_in_rec.TP_ATTRIBUTE11 <> FND_API.G_MISS_CHAR
5434 AND NVL(p_user_in_rec.TP_ATTRIBUTE11,'!!!') <> NVL(p_out_rec.TP_ATTRIBUTE11,'!!!')
5435 THEN
5436 l_attributes := l_attributes || 'TP_ATTRIBUTE11, ';
5437 END IF;
5438
5439 IF p_user_in_rec.TP_ATTRIBUTE12 <> FND_API.G_MISS_CHAR
5440 AND NVL(p_user_in_rec.TP_ATTRIBUTE12,'!!!') <> NVL(p_out_rec.TP_ATTRIBUTE12,'!!!')
5441 THEN
5442 l_attributes := l_attributes || 'TP_ATTRIBUTE12, ';
5443 END IF;
5444
5445 IF p_user_in_rec.TP_ATTRIBUTE13 <> FND_API.G_MISS_CHAR
5446 AND NVL(p_user_in_rec.TP_ATTRIBUTE13,'!!!') <> NVL(p_out_rec.TP_ATTRIBUTE13,'!!!')
5447 THEN
5448 l_attributes := l_attributes || 'TP_ATTRIBUTE13, ';
5449 END IF;
5450
5451 IF p_user_in_rec.TP_ATTRIBUTE14 <> FND_API.G_MISS_CHAR
5452 AND NVL(p_user_in_rec.TP_ATTRIBUTE14,'!!!') <> NVL(p_out_rec.TP_ATTRIBUTE14,'!!!')
5453 THEN
5454 l_attributes := l_attributes || 'TP_ATTRIBUTE14, ';
5455 END IF;
5456
5457 IF p_user_in_rec.TP_ATTRIBUTE15 <> FND_API.G_MISS_CHAR
5458 AND NVL(p_user_in_rec.TP_ATTRIBUTE15,'!!!') <> NVL(p_out_rec.TP_ATTRIBUTE15,'!!!')
5459 THEN
5460 l_attributes := l_attributes || 'TP_ATTRIBUTE15, ';
5461 END IF;
5462
5463 IF p_user_in_rec.ATTRIBUTE_CATEGORY <> FND_API.G_MISS_CHAR
5464 AND NVL(p_user_in_rec.ATTRIBUTE_CATEGORY,'!!!') <> NVL(p_out_rec.ATTRIBUTE_CATEGORY,'!!!')
5465 THEN
5466 l_attributes := l_attributes || 'ATTRIBUTE_CATEGORY, ';
5467 END IF;
5468
5469 IF p_user_in_rec.ATTRIBUTE1 <> FND_API.G_MISS_CHAR
5470 AND NVL(p_user_in_rec.ATTRIBUTE1,'!!!') <> NVL(p_out_rec.ATTRIBUTE1,'!!!')
5471 THEN
5472 l_attributes := l_attributes || 'ATTRIBUTE1, ';
5473 END IF;
5474
5475 IF p_user_in_rec.ATTRIBUTE2 <> FND_API.G_MISS_CHAR
5476 AND NVL(p_user_in_rec.ATTRIBUTE2,'!!!') <> NVL(p_out_rec.ATTRIBUTE2,'!!!')
5477 THEN
5478 l_attributes := l_attributes || 'ATTRIBUTE2, ';
5479 END IF;
5480
5481 IF p_user_in_rec.ATTRIBUTE3 <> FND_API.G_MISS_CHAR
5482 AND NVL(p_user_in_rec.ATTRIBUTE3,'!!!') <> NVL(p_out_rec.ATTRIBUTE3,'!!!')
5483 THEN
5484 l_attributes := l_attributes || 'ATTRIBUTE3, ';
5485 END IF;
5486
5487 IF p_user_in_rec.ATTRIBUTE4 <> FND_API.G_MISS_CHAR
5488 AND NVL(p_user_in_rec.ATTRIBUTE4,'!!!') <> NVL(p_out_rec.ATTRIBUTE4,'!!!')
5489 THEN
5490 l_attributes := l_attributes || 'ATTRIBUTE4, ';
5491 END IF;
5492
5493 IF p_user_in_rec.ATTRIBUTE5 <> FND_API.G_MISS_CHAR
5494 AND NVL(p_user_in_rec.ATTRIBUTE5,'!!!') <> NVL(p_out_rec.ATTRIBUTE5,'!!!')
5495 THEN
5496 l_attributes := l_attributes || 'ATTRIBUTE5, ';
5497 END IF;
5498
5499 IF p_user_in_rec.ATTRIBUTE6 <> FND_API.G_MISS_CHAR
5500 AND NVL(p_user_in_rec.ATTRIBUTE6,'!!!') <> NVL(p_out_rec.ATTRIBUTE6,'!!!')
5501 THEN
5502 l_attributes := l_attributes || 'ATTRIBUTE6, ';
5503 END IF;
5504
5505 IF p_user_in_rec.ATTRIBUTE7 <> FND_API.G_MISS_CHAR
5506 AND NVL(p_user_in_rec.ATTRIBUTE7,'!!!') <> NVL(p_out_rec.ATTRIBUTE7,'!!!')
5507 THEN
5508 l_attributes := l_attributes || 'ATTRIBUTE7, ';
5509 END IF;
5510
5511 IF p_user_in_rec.ATTRIBUTE8 <> FND_API.G_MISS_CHAR
5512 AND NVL(p_user_in_rec.ATTRIBUTE8,'!!!') <> NVL(p_out_rec.ATTRIBUTE8,'!!!')
5513 THEN
5514 l_attributes := l_attributes || 'ATTRIBUTE8, ';
5515 END IF;
5516
5517 IF p_user_in_rec.ATTRIBUTE9 <> FND_API.G_MISS_CHAR
5518 AND NVL(p_user_in_rec.ATTRIBUTE9,'!!!') <> NVL(p_out_rec.ATTRIBUTE9,'!!!')
5519 THEN
5520 l_attributes := l_attributes || 'ATTRIBUTE9, ';
5521 END IF;
5522
5523 IF p_user_in_rec.ATTRIBUTE10 <> FND_API.G_MISS_CHAR
5524 AND NVL(p_user_in_rec.ATTRIBUTE10,'!!!') <> NVL(p_out_rec.ATTRIBUTE10,'!!!')
5525 THEN
5526 l_attributes := l_attributes || 'ATTRIBUTE10, ';
5527 END IF;
5528
5529 IF p_user_in_rec.ATTRIBUTE11 <> FND_API.G_MISS_CHAR
5530 AND NVL(p_user_in_rec.ATTRIBUTE11,'!!!') <> NVL(p_out_rec.ATTRIBUTE11,'!!!')
5531 THEN
5532 l_attributes := l_attributes || 'ATTRIBUTE11, ';
5533 END IF;
5534
5535 IF p_user_in_rec.ATTRIBUTE12 <> FND_API.G_MISS_CHAR
5536 AND NVL(p_user_in_rec.ATTRIBUTE12,'!!!') <> NVL(p_out_rec.ATTRIBUTE12,'!!!')
5537 THEN
5538 l_attributes := l_attributes || 'ATTRIBUTE12, ';
5539 END IF;
5540
5541 IF p_user_in_rec.ATTRIBUTE13 <> FND_API.G_MISS_CHAR
5542 AND NVL(p_user_in_rec.ATTRIBUTE13,'!!!') <> NVL(p_out_rec.ATTRIBUTE13,'!!!')
5543 THEN
5544 l_attributes := l_attributes || 'ATTRIBUTE13, ';
5545 END IF;
5546
5547 IF p_user_in_rec.ATTRIBUTE14 <> FND_API.G_MISS_CHAR
5548 AND NVL(p_user_in_rec.ATTRIBUTE14,'!!!') <> NVL(p_out_rec.ATTRIBUTE14,'!!!')
5549 THEN
5550 l_attributes := l_attributes || 'ATTRIBUTE14, ';
5551 END IF;
5552
5553 IF p_user_in_rec.ATTRIBUTE15 <> FND_API.G_MISS_CHAR
5554 AND NVL(p_user_in_rec.ATTRIBUTE15,'!!!') <> NVL(p_out_rec.ATTRIBUTE15,'!!!')
5555 THEN
5556 l_attributes := l_attributes || 'ATTRIBUTE15, ';
5557 END IF;
5558
5559 /**
5560 -- Bug 3613650
5561 -- Need not compare against WHO columns
5562 IF p_user_in_rec.CREATION_DATE <> FND_API.G_MISS_DATE
5563 AND NVL(p_user_in_rec.CREATION_DATE,TO_DATE('2','j')) <> NVL(p_out_rec.CREATION_DATE,TO_DATE('2','j'))
5564 THEN
5565 l_attributes := l_attributes || 'CREATION_DATE, ';
5566 END IF;
5567
5568 IF p_user_in_rec.CREATED_BY <> FND_API.G_MISS_NUM
5569 AND NVL(p_user_in_rec.CREATED_BY,-99) <> NVL(p_out_rec.CREATED_BY,-99)
5570 THEN
5571 l_attributes := l_attributes || 'CREATED_BY, ';
5572 END IF;
5573
5574 IF p_user_in_rec.LAST_UPDATE_DATE <> FND_API.G_MISS_DATE
5575 AND NVL(p_user_in_rec.LAST_UPDATE_DATE,TO_DATE('2','j')) <> NVL(p_out_rec.LAST_UPDATE_DATE,TO_DATE('2','j'))
5576 THEN
5577 l_attributes := l_attributes || 'LAST_UPDATE_DATE, ';
5578 END IF;
5579
5580 IF p_user_in_rec.LAST_UPDATED_BY <> FND_API.G_MISS_NUM
5581 AND NVL(p_user_in_rec.LAST_UPDATED_BY,-99) <> NVL(p_out_rec.LAST_UPDATED_BY,-99)
5582 THEN
5583 l_attributes := l_attributes || 'LAST_UPDATED_BY, ';
5584 END IF;
5585
5586 IF p_user_in_rec.LAST_UPDATE_LOGIN <> FND_API.G_MISS_NUM
5587 AND NVL(p_user_in_rec.LAST_UPDATE_LOGIN,-99) <> NVL(p_out_rec.LAST_UPDATE_LOGIN,-99)
5588 THEN
5589 l_attributes := l_attributes || 'LAST_UPDATE_LOGIN, ';
5590 END IF;
5591
5592 IF p_user_in_rec.PROGRAM_APPLICATION_ID <> FND_API.G_MISS_NUM
5593 AND NVL(p_user_in_rec.PROGRAM_APPLICATION_ID,-99) <> NVL(p_out_rec.PROGRAM_APPLICATION_ID,-99)
5594 THEN
5595 l_attributes := l_attributes || 'PROGRAM_APPLICATION_ID, ';
5596 END IF;
5597
5598 IF p_user_in_rec.PROGRAM_ID <> FND_API.G_MISS_NUM
5599 AND NVL(p_user_in_rec.PROGRAM_ID,-99) <> NVL(p_out_rec.PROGRAM_ID,-99)
5600 THEN
5601 l_attributes := l_attributes || 'PROGRAM_ID, ';
5602 END IF;
5603
5604 IF p_user_in_rec.PROGRAM_UPDATE_DATE <> FND_API.G_MISS_DATE
5605 AND NVL(p_user_in_rec.PROGRAM_UPDATE_DATE,TO_DATE('2','j')) <> NVL(p_out_rec.PROGRAM_UPDATE_DATE,TO_DATE('2','j'))
5606 THEN
5607 l_attributes := l_attributes || 'PROGRAM_UPDATE_DATE, ';
5608 END IF;
5609
5610 IF p_user_in_rec.REQUEST_ID <> FND_API.G_MISS_NUM
5611 AND NVL(p_user_in_rec.REQUEST_ID,-99) <> NVL(p_out_rec.REQUEST_ID,-99)
5612 THEN
5613 l_attributes := l_attributes || 'REQUEST_ID, ';
5614 END IF;
5615
5616 bug 3613650 */
5617
5618 IF p_user_in_rec.WSH_LOCATION_ID <> FND_API.G_MISS_NUM
5619 AND NVL(p_user_in_rec.WSH_LOCATION_ID,-99) <> NVL(p_out_rec.WSH_LOCATION_ID,-99)
5620 THEN
5621 l_attributes := l_attributes || 'WSH_LOCATION_ID, ';
5622 END IF;
5623
5624 IF p_user_in_rec.TRACKING_DRILLDOWN_FLAG <> FND_API.G_MISS_CHAR
5625 AND NVL(p_user_in_rec.TRACKING_DRILLDOWN_FLAG,'!!!') <> NVL(p_out_rec.TRACKING_DRILLDOWN_FLAG,'!!!')
5626 THEN
5627 l_attributes := l_attributes || 'TRACKING_DRILLDOWN_FLAG, ';
5628 END IF;
5629
5630 IF p_user_in_rec.TRACKING_REMARKS <> FND_API.G_MISS_CHAR
5631 AND NVL(p_user_in_rec.TRACKING_REMARKS,'!!!') <> NVL(p_out_rec.TRACKING_REMARKS,'!!!')
5632 THEN
5633 l_attributes := l_attributes || 'TRACKING_REMARKS, ';
5634 END IF;
5635
5636 IF p_user_in_rec.CARRIER_EST_DEPARTURE_DATE <> FND_API.G_MISS_DATE
5637 AND NVL(p_user_in_rec.CARRIER_EST_DEPARTURE_DATE,TO_DATE('2','j')) <> NVL(p_out_rec.CARRIER_EST_DEPARTURE_DATE,TO_DATE('2','j'))
5638 THEN
5639 l_attributes := l_attributes || 'CARRIER_EST_DEPARTURE_DATE, ';
5640 END IF;
5641
5642 IF p_user_in_rec.CARRIER_EST_ARRIVAL_DATE <> FND_API.G_MISS_DATE
5643 AND NVL(p_user_in_rec.CARRIER_EST_ARRIVAL_DATE,TO_DATE('2','j')) <> NVL(p_out_rec.CARRIER_EST_ARRIVAL_DATE,TO_DATE('2','j'))
5644 THEN
5645 l_attributes := l_attributes || 'CARRIER_EST_ARRIVAL_DATE, ';
5646 END IF;
5647
5648 IF p_user_in_rec.LOADING_START_DATETIME <> FND_API.G_MISS_DATE
5649 AND NVL(p_user_in_rec.LOADING_START_DATETIME,TO_DATE('2','j')) <> NVL(p_out_rec.LOADING_START_DATETIME,TO_DATE('2','j'))
5650 THEN
5651 l_attributes := l_attributes || 'LOADING_START_DATETIME, ';
5652 END IF;
5653
5654 IF p_user_in_rec.LOADING_END_DATETIME <> FND_API.G_MISS_DATE
5655 AND NVL(p_user_in_rec.LOADING_END_DATETIME,TO_DATE('2','j')) <> NVL(p_out_rec.LOADING_END_DATETIME,TO_DATE('2','j'))
5656 THEN
5657 l_attributes := l_attributes || 'LOADING_END_DATETIME, ';
5658 END IF;
5659
5660 IF p_user_in_rec.UNLOADING_START_DATETIME <> FND_API.G_MISS_DATE
5661 AND NVL(p_user_in_rec.UNLOADING_START_DATETIME,TO_DATE('2','j')) <> NVL(p_out_rec.UNLOADING_START_DATETIME,TO_DATE('2','j'))
5662 THEN
5663 l_attributes := l_attributes || 'UNLOADING_START_DATETIME, ';
5664 END IF;
5665
5666 IF p_user_in_rec.UNLOADING_END_DATETIME <> FND_API.G_MISS_DATE
5667 AND NVL(p_user_in_rec.UNLOADING_END_DATETIME,TO_DATE('2','j')) <> NVL(p_out_rec.UNLOADING_END_DATETIME,TO_DATE('2','j'))
5668 THEN
5669 l_attributes := l_attributes || 'UNLOADING_END_DATETIME, ';
5670 END IF;
5671
5672 IF p_user_in_rec.ROWID <> FND_API.G_MISS_CHAR
5673 AND NVL(p_user_in_rec.ROWID,'!!!') <> NVL(p_out_rec.ROWID,'!!!')
5674 THEN
5675 l_attributes := l_attributes || 'ROWID, ';
5676 END IF;
5677
5678 IF p_user_in_rec.TRIP_NAME <> FND_API.G_MISS_CHAR
5679 AND NVL(p_user_in_rec.TRIP_NAME,'!!!') <> NVL(p_out_rec.TRIP_NAME,'!!!')
5680 THEN
5681 l_attributes := l_attributes || 'TRIP_NAME, ';
5682 END IF;
5683
5684 IF p_user_in_rec.STOP_LOCATION_CODE <> FND_API.G_MISS_CHAR
5685 AND NVL(p_user_in_rec.STOP_LOCATION_CODE,'!!!') <> NVL(p_out_rec.STOP_LOCATION_CODE,'!!!')
5686 THEN
5687 l_attributes := l_attributes || 'STOP_LOCATION_CODE, ';
5688 END IF;
5689
5690 IF p_user_in_rec.WEIGHT_UOM_DESC <> FND_API.G_MISS_CHAR
5691 AND NVL(p_user_in_rec.WEIGHT_UOM_DESC,'!!!') <> NVL(p_out_rec.WEIGHT_UOM_DESC,'!!!')
5692 THEN
5693 l_attributes := l_attributes || 'WEIGHT_UOM_DESC, ';
5694 END IF;
5695
5696 IF p_user_in_rec.VOLUME_UOM_DESC <> FND_API.G_MISS_CHAR
5697 AND NVL(p_user_in_rec.VOLUME_UOM_DESC,'!!!') <> NVL(p_out_rec.VOLUME_UOM_DESC,'!!!')
5698 THEN
5699 l_attributes := l_attributes || 'VOLUME_UOM_DESC, ';
5700 END IF;
5701
5702 IF p_user_in_rec.LOCK_STOP_ID <> FND_API.G_MISS_NUM
5703 AND NVL(p_user_in_rec.LOCK_STOP_ID,-99) <> NVL(p_out_rec.LOCK_STOP_ID,-99)
5704 THEN
5705 l_attributes := l_attributes || 'LOCK_STOP_ID, ';
5706 END IF;
5707
5708 IF p_user_in_rec.PENDING_INTERFACE_FLAG <> FND_API.G_MISS_CHAR
5709 AND NVL(p_user_in_rec.PENDING_INTERFACE_FLAG,'!!!') <> NVL(p_out_rec.PENDING_INTERFACE_FLAG,'!!!')
5710 THEN
5711 l_attributes := l_attributes || 'PENDING_INTERFACE_FLAG, ';
5712 END IF;
5713
5714 IF p_user_in_rec.TRANSACTION_HEADER_ID <> FND_API.G_MISS_NUM
5715 AND NVL(p_user_in_rec.TRANSACTION_HEADER_ID,-99) <> NVL(p_out_rec.TRANSACTION_HEADER_ID,-99)
5716 THEN
5717 l_attributes := l_attributes || 'TRANSACTION_HEADER_ID, ';
5718 END IF;
5719
5720 IF p_user_in_rec.SHIPMENTS_TYPE_FLAG <> FND_API.G_MISS_CHAR
5721 AND NVL(p_user_in_rec.SHIPMENTS_TYPE_FLAG,'!!!') <> NVL(p_out_rec.SHIPMENTS_TYPE_FLAG,'!!!')
5722 THEN
5723 l_attributes := l_attributes || 'SHIPMENTS_TYPE_FLAG, ';
5724 END IF;
5725
5726 IF p_user_in_rec.WV_FROZEN_FLAG <> FND_API.G_MISS_CHAR
5727 AND NVL(p_user_in_rec.WV_FROZEN_FLAG,'!!!') <> NVL(p_out_rec.WV_FROZEN_FLAG,'!!!')
5728 THEN
5729 l_attributes := l_attributes || 'WV_FROZEN_FLAG, ';
5730 END IF;
5731
5732 IF p_user_in_rec.WKEND_LAYOVER_STOPS <> FND_API.G_MISS_NUM
5733 AND NVL(p_user_in_rec.WKEND_LAYOVER_STOPS,-99) <> NVL(p_out_rec.WKEND_LAYOVER_STOPS,-99)
5734 THEN
5735 l_attributes := l_attributes || 'WKEND_LAYOVER_STOPS, ';
5736 END IF;
5737
5738 IF p_user_in_rec.WKDAY_LAYOVER_STOPS <> FND_API.G_MISS_NUM
5739 AND NVL(p_user_in_rec.WKDAY_LAYOVER_STOPS,-99) <> NVL(p_out_rec.WKDAY_LAYOVER_STOPS,-99)
5740 THEN
5741 l_attributes := l_attributes || 'WKDAY_LAYOVER_STOPS, ';
5742 END IF;
5743
5744 IF p_user_in_rec.TP_STOP_ID <> FND_API.G_MISS_NUM
5745 AND NVL(p_user_in_rec.TP_STOP_ID,-99) <> NVL(p_out_rec.TP_STOP_ID,-99)
5746 THEN
5747 l_attributes := l_attributes || 'TP_STOP_ID, ';
5748 END IF;
5749
5750 IF l_debug_on THEN
5751 WSH_DEBUG_SV.log(l_module_name,'l_attributes',l_attributes);
5752 WSH_DEBUG_SV.log(l_module_name,'length(l_attributes)',length(l_attributes));
5753 END IF;
5754
5755
5756 IF l_attributes IS NULL THEN
5757 --no message to be shown to the user
5758 IF l_debug_on THEN
5759 WSH_DEBUG_SV.pop(l_module_name);
5760 END IF;
5761 RETURN;
5762 ELSE
5763 Wsh_Utilities.process_message(
5764 p_entity => 'STOP',
5765 p_entity_name => p_out_rec.STOP_ID,
5766 p_attributes => l_attributes,
5767 x_return_status => l_return_status
5768 );
5769
5770 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
5771 THEN
5772 x_return_status := l_return_status;
5773 IF l_debug_on THEN
5774 wsh_debug_sv.logmsg(l_module_name,'Error returned by wsh_utilities.process_message',WSH_DEBUG_SV.C_PROC_LEVEL);
5775 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5776 wsh_debug_sv.pop(l_module_name);
5777 END IF;
5778 return;
5779 ELSE
5780 x_return_status := wsh_util_core.G_RET_STS_WARNING;
5781 END IF;
5782 END IF;
5783
5784 IF l_debug_on THEN
5785 WSH_DEBUG_SV.pop(l_module_name);
5786 END IF;
5787 --
5788
5789
5790 EXCEPTION
5791 WHEN OTHERS THEN
5792 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
5793 --
5794 IF l_debug_on THEN
5795 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5796 END IF;
5797 --
5798 END user_non_updatable_columns;
5799
5800
5801 END WSH_TRIP_STOPS_VALIDATIONS;
[Home] [Help]