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