[Home] [Help]
PACKAGE BODY: APPS.WSH_TRIP_STOPS_ACTIONS
Source
1 PACKAGE BODY WSH_TRIP_STOPS_ACTIONS as
2 /* $Header: WSHSTACB.pls 120.4.12010000.2 2009/12/03 13:05:13 mvudugul ship $ */
3
4
5 --
6 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_TRIP_STOPS_ACTIONS';
7 --
8
9
10 --OTM R12 Org-Specific. Declare of local procedure.
11 ---------------------------------------------------------------------------
12 -- PROCEDURE LAST_PICKUP_STOP_CLOSED
13 --
14 -- parameters: p_trip_id -> the trip id to check for
15 -- p_stop_id -> the stop id to check for to skip because
16 -- we will set this stop to close since this
17 -- is called only in setClose procedure
18 -- x_last_pickup_stop_closed -> Returns 'Y' if the stop
19 -- closed is the last pickup stop.
20 -- x_eligible_for_asr --> Returns 'Y' if the trip information
21 -- is eligible to be send to OTM.
22 -- x_return_status --> Returns status of this procedure call.
23 -- Assumption : p_trip_id and p_stop_id as passed by the calling API
24 ---------------------------------------------------------------------------
25
26 PROCEDURE last_pickup_stop_closed
27 (p_trip_id IN WSH_TRIP_STOPS.TRIP_ID%TYPE,
28 p_stop_id IN WSH_TRIP_STOPS.STOP_ID%TYPE,
29 x_last_pickup_stop_closed OUT NOCOPY VARCHAR2,
30 x_eligible_for_asr OUT NOCOPY VARCHAR2,
31 x_return_status OUT NOCOPY VARCHAR2);
32 --OTM R12 End
33
34 PROCEDURE Confirm_Stop (
35 p_stop_id IN NUMBER,
36 p_action_flag IN VARCHAR2,
37 p_intransit_flag IN VARCHAR2,
38 p_close_flag IN VARCHAR2,
39 p_stage_del_flag IN VARCHAR2,
40 p_report_set_id IN NUMBER,
41 p_ship_method IN VARCHAR2,
42 p_actual_dep_date IN DATE,
43 p_bol_flag IN VARCHAR2,
44 p_defer_interface_flag IN VARCHAR2,
45 x_return_status OUT NOCOPY VARCHAR2 ) IS
46
47
48 cursor get_pickup_del is
49 select dg.delivery_id, st.stop_sequence_number,st.trip_id
50 from wsh_trip_stops st, wsh_delivery_legs dg, wsh_new_deliveries dl
51 where st.stop_id = p_stop_id
52 and dg.delivery_id = dl.delivery_id
53 and st.stop_location_id = dl.initial_pickup_location_id
54 and st.stop_id = dg.pick_up_stop_id
55 AND nvl(dl.shipment_direction,'O') IN ('O','IO') -- J-IB-NPARIKH
56 AND dl.delivery_type = 'STANDARD' --sperera, MDC
57 and dl.status_code IN ( 'OP', 'PA', 'SA') ;
58
59 del_ids wsh_util_core.id_tab_type;
60 stop_ids wsh_util_core.id_tab_type;
61 dummy_ids wsh_util_core.id_tab_type;
62 l_trip_tab wsh_util_core.id_tab_type;
63
64 l_exceptions_tab wsh_xc_util.XC_TAB_TYPE;
65 l_exp_logged BOOLEAN := FALSE;
66 l_msg_count NUMBER;
67 l_msg_data VARCHAR2(4000);
68 l_exc_exist VARCHAR2(1);
69 l_del_name VARCHAR2(30);
70 l_num_warnings NUMBER;
71 l_num_errors NUMBER;
72
73 l_return_status VARCHAR2(1) := NULL;
74
75
76 --
77 l_debug_on BOOLEAN;
78 --
79 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CONFIRM_STOP';
80 --
81 BEGIN
82
83 --
84 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
85 --
86 IF l_debug_on IS NULL THEN
87 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
88 END IF;
89 --
90 IF l_debug_on THEN
91 WSH_DEBUG_SV.push(l_module_name);
92 WSH_DEBUG_SV.log(l_module_name,'Stop id'||p_stop_id);
93 END IF;
94 --
95 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
96
97 savepoint sp_confirm_stop ;
98 stop_ids.delete;
99 stop_ids(1) := p_stop_id;
100 -- Get all the Pickup Deliveries
101 del_ids.delete;
102 FOR del_rec IN get_pickup_del LOOP
103 del_ids(del_ids.COUNT + 1) := del_rec.delivery_id ;
104 l_trip_tab(l_trip_tab.count + 1) := del_rec.trip_id;
105 END LOOP;
106
107 IF del_ids.COUNT <> 0 THEN
108 -- Call Confirm_Delivery for del_ids
109 IF l_debug_on THEN
110 WSH_DEBUG_SV.log(l_module_name,'Calling WSH_NEW_DELIVERY_ACTIONS.Confirm_Delivery');
111 END IF;
112 -- We always set the close_trip/set_intransit flags to 'N'
113 -- since we manually close the stops if necessary after the
114 -- call to confirm_delivery.
115 WSH_NEW_DELIVERY_ACTIONS.Confirm_Delivery (
116 p_del_rows => del_ids,
117 p_action_flag => p_action_flag,
118 p_intransit_flag => 'N',
119 p_close_flag => 'N',
120 p_stage_del_flag => p_stage_del_flag,
121 p_report_set_id => p_report_set_id,
122 p_ship_method => p_ship_method,
123 p_actual_dep_date => p_actual_dep_date,
124 p_bol_flag => p_bol_flag,
125 p_mc_bol_flag => p_bol_flag,
126 p_defer_interface_flag => p_defer_interface_flag,
127 p_send_945_flag => NULL,
128 x_return_status => l_return_status);
129 IF l_debug_on THEN
130 WSH_DEBUG_SV.log(l_module_name,'Return status from WSHDEACB.Confirm_Delivery: ' || l_return_status);
131 END IF;
132
133 x_return_status := l_return_status ;
134
135 IF x_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
136 -- Bug#5864517: Needs to call Print_Label API irrespective of p_intransit_flag and p_close_flag
137 -- flag values.
138 -- Call Print Label for WMS
139 IF l_debug_on THEN
140 WSH_DEBUG_SV.log(l_module_name,'Calling WSH_UTIL_CORE.Print_Label');
141 END IF;
142 WSH_UTIL_CORE.Print_Label(p_stop_ids => stop_ids,
143 p_delivery_ids => dummy_ids,
144 x_return_status => l_return_status);
145 IF l_debug_on THEN
146 WSH_DEBUG_SV.log(l_module_name,'Return status from WSH_UTIL_CORE.Print_Label : ' || l_return_status);
147 END IF;
148 IF l_return_status NOT IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
149 --{
150 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING AND
151 x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
152 x_return_status := l_return_status ;
153 END IF;
154 ELSE
155 x_return_status := l_return_status ;
156 --}
157 END IF;
158
159 END IF;
160 -- Bug#5864517: End
161
162 -- Bug 2887720, move End-If back up to correct place
163 END IF;
164
165
166 IF x_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
167
168 IF l_debug_on THEN
169 WSH_DEBUG_SV.log(l_module_name,'In-transit flag'||p_intransit_flag);
170 WSH_DEBUG_SV.log(l_module_name,'Close flag'||p_close_flag);
171 END IF;
172 IF p_intransit_flag = 'Y' OR p_close_flag = 'Y' THEN
173 -- Call Change_Status for the Stop
174 IF l_debug_on THEN
175 WSH_DEBUG_SV.log(l_module_name,'IN IF CONDITION');
176 WSH_DEBUG_SV.log(l_module_name,'Stop id'||stop_ids(1));
177 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.Check_Exceptions',WSH_DEBUG_SV.C_PROC_LEVEL);
178 END IF;
179 --l_exceptions_tab.delete; //Commented for bugfix 4017720.
180 l_exp_logged := FALSE;
181 l_exc_exist := 'N';
182
183 FOR i in 1..del_ids.count LOOP
184
185 -- Check if at least one delivery on the stop has an ITM exception logged.
186 -- At this stage deliveries would have all their exceptions cleaned up
187 -- except for ITM exceptions. Do not close stop if an ITM exception
188 -- of severity 'ERROR' or 'WARNING' exists against the delivery.
189 l_exceptions_tab.delete; --Bugfix 4017720
190
191 WSH_XC_UTIL.Check_Exceptions (
192 p_api_version => 1.0,
193 x_return_status => l_return_status,
194 x_msg_count => l_msg_count,
195 x_msg_data => l_msg_data,
196 p_logging_entity_id => del_ids(i),
197 p_logging_entity_name => 'DELIVERY',
198 p_consider_content => 'N',
199 x_exceptions_tab => l_exceptions_tab
200 );
201 WSH_UTIL_CORE.api_post_call
202 (
203 p_return_status => l_return_status,
204 x_num_warnings => l_num_warnings,
205 x_num_errors => l_num_errors
206 );
207
208 FOR exp_cnt in 1..l_exceptions_tab.COUNT LOOP
209 IF l_exceptions_tab(exp_cnt).exception_behavior in ('ERROR', 'WARNING') THEN
210
211 l_exc_exist := 'Y';
212 l_del_name := WSH_NEW_DELIVERIES_PVT.Get_Name(del_ids(i));
213 EXIT;
214
215 END IF;
216 END LOOP;
217 IF l_exc_exist = 'Y' THEN
218
219 EXIT;
220
221 END IF;
222
223 END LOOP;
224
225 -- Pack J, ITM -- Attempt to close the stop only if there are no exceptions against it.
226
227 IF l_exc_exist = 'N' THEN
228
229
230 IF l_debug_on THEN
231 WSH_DEBUG_SV.log(l_module_name,'Calling WSHSTACB.change_status');
232 END IF;
233
234
235 Change_Status ( p_stop_rows => stop_ids,
236 p_action => 'CLOSE',
237 p_actual_date => p_actual_dep_date,
238 p_defer_interface_flag => p_defer_interface_flag,
239 x_return_status => l_return_status);
240 IF l_debug_on THEN
241 WSH_DEBUG_SV.log(l_module_name,'Return status from WSHSTACB.change_status : ' || l_return_status);
242 END IF;
243
244 IF l_return_status NOT IN
245 (WSH_UTIL_CORE.G_RET_STS_ERROR,
246 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
247 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING AND
248 x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
249 x_return_status := l_return_status ;
250 END IF;
251
252 ELSE
253 x_return_status := l_return_status ;
254 END IF;
255 ELSE -- If exceptions exist
256 -- Bug 3402366, inform user stop will not be closed due to ITM exception
257 -- on delivery.
258
259 FND_MESSAGE.SET_NAME('WSH','WSH_EXP_COMPL_SCRN_REQD');
260 FND_MESSAGE.SET_TOKEN('DEL_NAME',l_del_name);
261 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_SUCCESS);
262
263 END IF;
264 END IF;
265 ELSE
266 x_return_status := l_return_status ;
267 END IF;
268
269 IF x_return_status IN ( WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ) THEN
270 rollback to sp_confirm_stop;
271 END IF;
272
273 IF l_debug_on THEN
274 WSH_DEBUG_SV.log(l_module_name,'Return status from WSHSTACB.Confirm_Stop: '||x_return_status);
275 WSH_DEBUG_SV.pop(l_module_name);
276 END IF;
277
278 EXCEPTION
279 WHEN others THEN
280 wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.CONFIRM_STOP');
281 rollback to sp_confirm_stop;
282 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
283 IF l_debug_on THEN
284 WSH_DEBUG_SV.log(l_module_name,'Unexpected Error has Occured.Oracle error message is'||SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
285 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
286 END IF;
287
288 END;
289
290
291 PROCEDURE Check_Update_Stops (
292 p_stop_rows IN wsh_util_core.id_tab_type,
293 p_action IN VARCHAR2,
294 --tkt
295 p_caller IN VARCHAR2,
296 x_return_status OUT NOCOPY VARCHAR2) IS
297
298 CURSOR stop_info (l_stop_id NUMBER) IS
299 SELECT trip_id,
300 status_code
301 FROM wsh_trip_stops
302 WHERE stop_id = l_stop_id;
303
304 CURSOR stop_dropoffs (l_stop_id NUMBER) IS
305 SELECT dl.delivery_id
306 FROM wsh_delivery_legs dg,
307 wsh_new_deliveries dl
308 WHERE dg.delivery_id = dl.delivery_id AND
309 dg.drop_off_stop_id = l_stop_id AND
310 dl.status_code IN ('OP','PA', 'SA') -- sperera 940/945
311 AND nvl(dl.shipment_direction,'O') IN ('O','IO');
312
313 CURSOR stop_pickups (l_stop_id NUMBER) IS
314 SELECT 1 from dual
315 WHERE exists ( select 1
316 FROM wsh_delivery_legs dg,
317 wsh_new_deliveries dl
318 WHERE dg.delivery_id = dl.delivery_id
319 AND dg.pick_up_stop_id = l_stop_id
320 AND dl.status_code in ('OP','PA', 'SA') -- sperera 940/945
321 AND nvl(dl.shipment_direction,'O') IN ('O','IO'));
322
323 l_trip_id NUMBER;
324 l_status_code VARCHAR2(2);
325 l_old_trip_id NUMBER;
326 l_old_status_code VARCHAR2(2);
327 l_del_id NUMBER;
328 l_trip_stop_num NUMBER;
329 others EXCEPTION;
330
331 --
332 l_debug_on BOOLEAN;
333 --
334 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_UPDATE_STOPS';
335 --
336 BEGIN
337
338 --
339 -- Debug Statements
340 --
341 --
342 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
343 --
344 IF l_debug_on IS NULL
345 THEN
346 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
347 END IF;
348 --
349 IF l_debug_on THEN
350 WSH_DEBUG_SV.push(l_module_name);
351 END IF;
352 --
353 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
354
355 IF (p_stop_rows.count = 0) THEN
356 RAISE others;
357 END IF;
358
359 l_trip_id := NULL;
360
361 FOR i IN 1..p_stop_rows.count LOOP
362
363 l_trip_id := NULL;
364
365 OPEN stop_info(p_stop_rows(i));
366 FETCH stop_info INTO l_trip_id, l_status_code;
367 CLOSE stop_info;
368
369 IF (l_trip_id IS NULL) THEN
370 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
371 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
372 wsh_util_core.add_message(x_return_status);
373 --
374 -- Debug Statements
375 --
376 IF l_debug_on THEN
377 WSH_DEBUG_SV.pop(l_module_name);
378 END IF;
379 --
380 RETURN;
381 END IF;
382
383 IF (l_trip_id = l_old_trip_id) THEN
384 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_UPDATE_SAME_TRIP');
385 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
386 wsh_util_core.add_message(x_return_status);
387 --
388 -- Debug Statements
389 --
390 IF l_debug_on THEN
391 WSH_DEBUG_SV.pop(l_module_name);
392 END IF;
393 --
394 RETURN;
395 END IF;
396
397 IF (l_status_code = 'CL') THEN
398 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_INVALID_STATUS');
399 --
400 -- Debug Statements
401 --
402 IF l_debug_on THEN
403 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
404 END IF;
405 --
406 FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(p_stop_rows(i),p_caller));
407 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
408 wsh_util_core.add_message(x_return_status);
409 --
410 -- Debug Statements
411 --
412 IF l_debug_on THEN
413 WSH_DEBUG_SV.pop(l_module_name);
414 END IF;
415 --
416 RETURN;
417 END IF;
418
419 -- Check if there are any open deliveries dropped off at the stop. The stop
420 -- cannot be arrived or closed if this happens
421
422 l_del_id := NULL;
423
424 OPEN stop_dropoffs (p_stop_rows(i));
425 FETCH stop_dropoffs INTO l_del_id;
426 CLOSE stop_dropoffs;
427
428 IF (l_del_id IS NOT NULL ) THEN
429
430 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_PREV_NOT_CLOSED');
431 --
432 -- Debug Statements
433 --
434 IF l_debug_on THEN
435 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
436 END IF;
437 --
438 FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(p_stop_rows(i),p_caller));
439 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
440 wsh_util_core.add_message(x_return_status);
441 --
442 -- Debug Statements
443 --
444 IF l_debug_on THEN
445 WSH_DEBUG_SV.pop(l_module_name);
446 END IF;
447 --
448 RETURN;
449 END IF;
450 IF p_action <> 'ARRIVE' THEN
451 OPEN stop_pickups (p_stop_rows(i));
452 FETCH stop_pickups INTO l_trip_stop_num;
453
454 IF (stop_pickups%FOUND) THEN
455
456 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_CLOSE_OP_PA_ERROR');
457 --
458 -- Debug Statements
459 --
460 IF l_debug_on THEN
461 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
462 END IF;
463 --
464 FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(p_stop_rows(i),p_caller));
465 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
466 wsh_util_core.add_message(x_return_status);
467
468 END IF;
469 CLOSE stop_pickups;
470 END IF;
471 l_old_trip_id := l_trip_id;
472 l_old_status_code := l_status_code;
473
474 <<loop_end>>
475 NULL;
476
477 END LOOP;
478
479 --
480 -- Debug Statements
481 --
482 IF l_debug_on THEN
483 WSH_DEBUG_SV.pop(l_module_name);
484 END IF;
485 --
486 EXCEPTION
487 WHEN others THEN
488 wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.CHECK_UPDATE');
489 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
490
491 --
492 -- Debug Statements
493 --
494 IF l_debug_on THEN
495 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
496 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
497 END IF;
498 --
499 END Check_Update_Stops;
500
501 PROCEDURE Change_Status (
502 p_stop_rows IN wsh_util_core.id_tab_type,
503 p_action IN VARCHAR2,
504 p_actual_date IN DATE,
505 p_defer_interface_flag IN VARCHAR2, -- bug 1578251
506 x_return_status OUT NOCOPY VARCHAR2,
507 p_caller IN VARCHAR2) IS
508
509
510 l_return_status VARCHAR2(1);
511 others EXCEPTION;
512 --
513 --
514 l_in_rec WSH_TRIP_STOPS_VALIDATIONS.chkClose_in_rec_type;
515 l_out_rec WSH_TRIP_STOPS_VALIDATIONS.chkClose_out_rec_type;
516 --
517 l_stopsProcessed NUMBER;
518 l_stopsProcessedWarnings NUMBER;
519 l_stopsNotProcessedWarnings NUMBER;
520 --
521 l_num_warnings NUMBER;
522 l_num_errors NUMBER;
523 l_num_warnings_bak NUMBER;
524 --
525 i NUMBER;
526 --
527 l_linked_stop_id NUMBER; --wr
528 -- Exceptions Project
529 l_msg_count NUMBER;
530 l_msg_data VARCHAR2(4000);
531 --
532 l_debug_on BOOLEAN;
533 --
534 l_stop_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
535 l_dbi_rs VARCHAR2(1); -- DBI Project
536 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHANGE_STATUS';
537 l_wf_rs VARCHAR2(1); -- Workflow Project
538
539 --
540 BEGIN
541 --{
542 SAVEPOINT stop_chg_status_begin_sp;
543 --
544 -- Debug Statements
545 --
546 --
547 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
548 --
549 IF l_debug_on IS NULL
550 THEN
551 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
552 END IF;
553 --
554 IF l_debug_on THEN
555 WSH_DEBUG_SV.push(l_module_name);
556 --
557 WSH_DEBUG_SV.log(l_module_name,'P_ACTION',P_ACTION);
558 WSH_DEBUG_SV.log(l_module_name,'P_ACTUAL_DATE',P_ACTUAL_DATE);
559 WSH_DEBUG_SV.log(l_module_name,'P_DEFER_INTERFACE_FLAG',P_DEFER_INTERFACE_FLAG);
560 END IF;
561 --
562 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
563 l_num_warnings := 0;
564 l_num_errors := 0;
565 --
566 l_stopsProcessed := 0;
567 l_stopsProcessedWarnings := 0;
568 l_stopsNotProcessedWarnings := 0;
569 --
570 IF (p_stop_rows.count = 0)
571 THEN
572 RAISE others;
573 END IF;
574 --
575
576 -- We cannot assume that the table has a gapless index sequence.
577 i := p_stop_rows.FIRST;
578 WHILE i IS NOT NULL
579 LOOP
580 --{
581 BEGIN
582 --{
583 savepoint stop_chg_status_sp ;
584 --
585 l_num_warnings_bak := l_num_warnings;
586 --
587 IF l_debug_on THEN
588 WSH_DEBUG_SV.log(l_module_name,'l_num_warnings_bak',l_num_warnings_bak);
589 END IF;
590 --
591
592 IF (p_action = 'CLOSE')
593 THEN
594 --{
595 l_in_rec.stop_id := p_stop_rows(i);
596 l_in_rec.put_messages := TRUE;
597 l_in_rec.caller := p_caller;
598 l_in_rec.actual_date := p_actual_date;
599 --
600 --
601 IF l_debug_on THEN
602 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_VALIDATIONS.CHECK_STOP_CLOSE',WSH_DEBUG_SV.C_PROC_LEVEL);
603 END IF;
604 --
605 WSH_TRIP_STOPS_VALIDATIONS.check_stop_close
606 (
607 p_in_rec => l_in_rec,
608 x_out_rec => l_out_rec,
609 x_return_status => l_return_status
610 );
611 --
612 IF l_debug_on THEN
613 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
614 WSH_DEBUG_SV.log(l_module_name,'l_out_rec.close_Allowed',l_out_rec.close_Allowed);
615 END IF;
616 --
617 WSH_UTIL_CORE.api_post_call
618 (
619 p_return_status => l_return_status,
620 x_num_warnings => l_num_warnings,
621 x_num_errors => l_num_errors
622 );
623 --
624 IF l_out_rec.close_Allowed = 'Y'
625 THEN
626 NULL;
627 ELSIF l_out_rec.close_Allowed = 'YW'
628 THEN
629 l_num_warnings := l_num_warnings + 1;
630 ELSIF l_out_rec.close_Allowed = 'NW'
631 THEN
632 l_num_warnings := l_num_warnings + 1;
633 l_stopsNotProcessedWarnings := l_stopsNotProcessedWarnings + 1;
634 RAISE wsh_util_core.g_exc_warning;
635 ELSE
636 l_num_errors := l_num_errors + 1;
637 RAISE FND_API.G_EXC_ERROR;
638 END IF;
639 --
640 --
641 IF l_debug_on THEN
642 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_ACTIONS.SETCLOSE',WSH_DEBUG_SV.C_PROC_LEVEL);
643 END IF;
644 --
645 WSH_TRIP_STOPS_ACTIONS.setClose
646 (
647 p_in_rec => l_in_rec,
648 p_in_rec1 => l_out_rec,
649 p_defer_interface_flag => p_defer_interface_flag,
650 x_return_status => l_return_status
651 );
652 --
653 WSH_UTIL_CORE.api_post_call
654 (
655 p_return_status => l_return_status,
656 x_num_warnings => l_num_warnings,
657 x_num_errors => l_num_errors
658 );
659 --
660 IF l_debug_on THEN
661 WSH_DEBUG_SV.log(l_module_name,'After setclose-l_num_warnings',l_num_warnings);
662 END IF;
663 --
664 --
665 IF l_num_warnings > l_num_warnings_bak
666 THEN
667 l_stopsProcessedWarnings := l_stopsProcessedWarnings + 1;
668 ELSE
669 l_stopsProcessed := l_stopsProcessed + 1;
670 END IF;
671
672 -- Close Exceptions for the Stop and its contents
673 IF l_debug_on THEN
674 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.Close_Exceptions',WSH_DEBUG_SV.C_PROC_LEVEL);
675 END IF;
676 WSH_XC_UTIL.Close_Exceptions (
677 p_api_version => 1.0,
678 x_return_status => l_return_status,
679 x_msg_count => l_msg_count,
680 x_msg_data => l_msg_data,
681 p_logging_entity_id => p_stop_rows(i),
682 p_logging_entity_name => 'STOP',
683 p_consider_content => 'Y',
684 p_caller => p_caller
685 ) ;
686
687 WSH_UTIL_CORE.api_post_call
688 (
689 p_return_status => l_return_status,
690 x_num_warnings => l_num_warnings,
691 x_num_errors => l_num_errors
692 );
693 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
694 RAISE FND_API.G_EXC_ERROR;
695 END IF;
696
697 --}
698 ELSIF (p_action = 'ARRIVE')
699 THEN
700 --{
701 l_in_rec.stop_id := p_stop_rows(i);
702 l_in_rec.put_messages := TRUE;
703 l_in_rec.caller := p_caller;
704 l_in_rec.actual_date := p_actual_date;
705 --
706 --
707 IF l_debug_on THEN
708 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_VALIDATIONS.CHECK_STOP_ARRIVE',WSH_DEBUG_SV.C_PROC_LEVEL);
709 END IF;
710 --
711 WSH_TRIP_VALIDATIONS.check_stop_arrive
712 (
713 p_stop_id => p_stop_rows(i),
714 x_linked_stop_id => l_linked_stop_id, --wr if not null, must be open.
715 x_return_status => l_return_status
716 );
717 --
718 --
719 WSH_UTIL_CORE.api_post_call
720 (
721 p_return_status => l_return_status,
722 x_num_warnings => l_num_warnings,
723 x_num_errors => l_num_errors
724 );
725 --
726 UPDATE wsh_trip_stops
727 SET status_code = 'AR',
728 actual_arrival_date = nvl(p_actual_date, SYSDATE)
729 WHERE stop_id IN (p_stop_rows(i), l_linked_stop_id) --wr
730 RETURNING stop_id BULK COLLECT INTO l_stop_tab; -- Added for DBI Project;
731
732 --
733 IF (SQL%NOTFOUND) THEN
734 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
735 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
736 l_num_errors := l_num_errors + 1;
737 RAISE FND_API.G_EXC_ERROR;
738 END IF;
739 --
740
741 -- Workflow Project
742 -- Raise Stop Status change business event
743 IF l_debug_on THEN
744 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.RAISE_EVENT',WSH_DEBUG_SV.C_PROC_LEVEL);
745 END IF;
746 WSH_WF_STD.RAISE_EVENT( p_entity_type => 'STOP',
747 p_entity_id => p_stop_rows(i),
748 p_event => 'oracle.apps.wsh.stop.gen.arrived',
749 x_return_status => l_wf_rs
750 );
751 IF l_debug_on THEN
752 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_WF_STD.RAISE_EVENT => ',l_wf_rs);
753 END IF;
754
755 IF l_linked_stop_id IS NOT NULL THEN
756 IF l_debug_on THEN
757 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.RAISE_EVENT',WSH_DEBUG_SV.C_PROC_LEVEL);
758 END IF;
759 WSH_WF_STD.RAISE_EVENT( p_entity_type => 'STOP',
760 p_entity_id => l_linked_stop_id,
761 p_event => 'oracle.apps.wsh.stop.gen.arrived',
762 x_return_status => l_wf_rs
763 );
764 IF l_debug_on THEN
765 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_WF_STD.RAISE_EVENT => ',l_wf_rs);
766 END IF;
767 END IF;
768 -- End of code for Workflow project
769
770 --
771 -- DBI Project
772 -- Updating WSH_TRIP_STOPS.
773 -- Call DBI API after the Update.
774 -- This API will also check for DBI Installed or not
775 IF l_debug_on THEN
776 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Stop Count -',l_stop_tab.count);
777 END IF;
778 WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
779 (p_stop_id_tab => l_stop_tab,
780 p_dml_type => 'UPDATE',
781 x_return_status => l_dbi_rs);
782
783 IF l_debug_on THEN
784 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
785 END IF;
786 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
787 x_return_status := l_dbi_rs;
788 ROLLBACK TO stop_chg_status_sp;
789 -- just pass this return status to caller API
790 IF l_debug_on THEN
791 WSH_DEBUG_SV.log(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
792 WSH_DEBUG_SV.pop(l_module_name);
793 END IF;
794 return;
795 END IF;
796 -- End of Code for DBI Project
797 --
798 --
799 IF l_num_warnings > l_num_warnings_bak
800 THEN
801 l_stopsProcessedWarnings := l_stopsProcessedWarnings + 1;
802 ELSE
803 l_stopsProcessed := l_stopsProcessed + 1;
804 END IF;
805 --}
806 END IF;
807 --}
808 EXCEPTION
809 --{
810 WHEN FND_API.G_EXC_ERROR
811 THEN
812 --{
813 ROLLBACK to stop_chg_status_sp;
814 --
815 IF p_action = 'CLOSE'
816 THEN
817 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_CLOSE_ERROR');
818 FND_MESSAGE.SET_TOKEN('STOP_NAME', l_out_rec.stop_name);
819 ELSIF p_action = 'ARRIVE'
820 THEN
821 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_ARRIVE_ERROR');
822 --
823 IF l_debug_on THEN
824 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
825 END IF;
826 --
827 FND_MESSAGE.SET_TOKEN('stop_name',wsh_trip_stops_pvt.get_name(p_stop_rows(i),p_caller));
828 END IF;
829 --
830 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
831 --}
832 WHEN wsh_util_core.g_exc_warning
833 THEN
834 --{
835 ROLLBACK to stop_chg_status_sp;
836 --
837 IF p_action = 'CLOSE'
838 THEN
839 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_CLOSE_ERROR');
840 FND_MESSAGE.SET_TOKEN('STOP_NAME', l_out_rec.stop_name);
841 END IF;
842 --
843 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING,l_module_name);
844 --}
845 --}
846 END;
847 --}
848 i := p_stop_rows.next(i);
849 END LOOP;
850 --
851 --
852 IF l_debug_on THEN
853 wsh_debug_sv.log(l_module_name, 'l_stopsProcessed', l_stopsProcessed);
854 wsh_debug_sv.log(l_module_name, 'l_stopsProcessedWarnings', l_stopsProcessedWarnings);
855 wsh_debug_sv.log(l_module_name, 'l_num_errors', l_num_errors);
856 wsh_debug_sv.log(l_module_name, 'l_num_warnings', l_num_warnings);
857
858 END IF;
859
860 IF (l_stopsProcessed = 0 AND l_stopsProcessedWarnings = 0)
861 OR l_num_errors > 0
862 THEN
863 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
864 ELSIF l_stopsProcessedWarnings > 0
865 OR l_stopsNotProcessedWarnings > 0
866 OR l_num_warnings > 0
867 THEN
868 l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
869 ELSE
870 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
871 END IF;
872 --
873 x_return_status := l_return_status;
874 --
875 IF ( p_stop_rows.count > 1 AND l_stopsProcessed <> p_stop_rows.count) OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
876 THEN
877 --{
878 IF (p_action = 'CLOSE') THEN
879 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_CLOSE_SUMMARY');
880 ELSE
881 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_ARRIVE_SUMMARY');
882 END IF;
883 --
884 --
885 FND_MESSAGE.SET_TOKEN('NUM_ERROR',l_num_errors);
886 FND_MESSAGE.SET_TOKEN('NUM_SUCCESS',l_stopsProcessed);
887 --
888 --
889 IF (p_action = 'CLOSE') THEN
890 FND_MESSAGE.SET_TOKEN('NUMBER_WARN_ERROR',l_stopsNotProcessedWarnings);
891 FND_MESSAGE.SET_TOKEN('NUM_WARN',l_stopsProcessedWarnings);
892 ELSE
893 FND_MESSAGE.SET_TOKEN('NUM_WARN',l_stopsProcessedWarnings+l_stopsNotProcessedWarnings);
894 END IF;
895 --
896 wsh_util_core.add_message(l_return_status,l_module_name);
897 --}
898 END IF;
899 --
900 -- Debug Statements
901 --
902 IF l_debug_on THEN
903 WSH_DEBUG_SV.pop(l_module_name);
904 END IF;
905 --}
906 EXCEPTION
907 WHEN FND_API.G_EXC_ERROR THEN
908 ROLLBACK TO stop_chg_status_begin_sp;
909 --
910 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
911 --
912 IF l_debug_on THEN
913 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
914 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
915 END IF;
916 --
917 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
918
919 ROLLBACK TO stop_chg_status_begin_sp;
920 --
921 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
922 --
923 IF l_debug_on THEN
924 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
925 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
926 END IF;
927 --
928 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
929 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
930 --
931 IF l_debug_on THEN
932 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
933 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
934 END IF;
935 --
936 WHEN others THEN
937
938 wsh_util_core.default_handler('WSH_NEW_DELIVERY_ACTIONS.CHANGE_STATUS',l_module_name);
939 ROLLBACK TO stop_chg_status_begin_sp;
940 --
941 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
942
943 --
944 -- Debug Statements
945 --
946 IF l_debug_on THEN
947 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
948 END IF;
949 --
950 END Change_Status;
951
952 --wr removed the old I procedure that had been commented with
953 -- instructions to remove after ut.
954
955 PROCEDURE Plan (
956 p_stop_rows IN wsh_util_core.id_tab_type,
957 p_action IN VARCHAR2,
958 p_caller IN VARCHAR2,
959 x_return_status OUT NOCOPY VARCHAR2) IS
960
961 CURSOR stop_dels (l_stop_id NUMBER) IS
962 SELECT dg.delivery_id
963 FROM wsh_delivery_legs dg
964 WHERE dg.pick_up_stop_id = l_stop_id;
965
966 CURSOR get_status(l_stop_id NUMBER) IS
967 select st.status_code, tr.status_code ,
968 NVL(st.shipments_type_flag,'O') -- J-IB-NPARIKH
969 from wsh_trip_stops st, wsh_trips tr
970 where st.stop_id = l_stop_id
971 and st.trip_id = tr.trip_id;
972
973 others EXCEPTION;
974
975 j BINARY_INTEGER;
976 l_num_error BINARY_INTEGER := 0;
977 l_num_warn BINARY_INTEGER := 0;
978 l_del_rows wsh_util_core.id_tab_type;
979 l_stop_status VARCHAR2(2);
980 l_trip_status VARCHAR2(2);
981 l_stop_id NUMBER;
982
983 invalid_stop exception;
984
985 --
986 l_debug_on BOOLEAN;
987 --
988 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PLAN';
989 --
990 l_shipments_type_flag VARCHAR2(30);
991 BEGIN
992
993 --
994 -- Debug Statements
995 --
996 --
997 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
998 --
999 IF l_debug_on IS NULL
1000 THEN
1001 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1002 END IF;
1003 --
1004 IF l_debug_on THEN
1005 WSH_DEBUG_SV.push(l_module_name);
1006 --
1007 WSH_DEBUG_SV.log(l_module_name,'P_ACTION',P_ACTION);
1008 END IF;
1009 --
1010 IF (p_stop_rows.count = 0) THEN
1011 raise others;
1012 END IF;
1013
1014 FOR i IN 1..p_stop_rows.count LOOP
1015
1016 j := 0;
1017 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1018 --removed code checking for closed status as now firm/plan/unfirm can be done at any stage
1019
1020 FOR s IN stop_dels(p_stop_rows(i)) LOOP
1021
1022 j := j + 1;
1023 l_del_rows(j) := s.delivery_id;
1024
1025
1026 END LOOP;
1027
1028 IF (j > 0) THEN
1029 IF (p_action = 'PLAN') THEN
1030 --
1031 -- Debug Statements
1032 --
1033 IF l_debug_on THEN
1034 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERY_ACTIONS.PLAN',WSH_DEBUG_SV.C_PROC_LEVEL);
1035 END IF;
1036 --
1037 wsh_new_delivery_actions.plan(l_del_rows, x_return_status);
1038 ELSE
1039 --
1040 -- Debug Statements
1041 --
1042 IF l_debug_on THEN
1043 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERY_ACTIONS.UNPLAN',WSH_DEBUG_SV.C_PROC_LEVEL);
1044 END IF;
1045 --
1046 wsh_new_delivery_actions.unplan(l_del_rows, x_return_status);
1047 END IF;
1048 ELSE
1049 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1050 END IF;
1051
1052 IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1053
1054 IF (p_action = 'PLAN') THEN
1055 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_PLAN_ERROR');
1056 ELSE
1057 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_UNPLAN_ERROR');
1058 END IF;
1059
1060 --
1061 -- Debug Statements
1062 --
1063 IF l_debug_on THEN
1064 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1065 END IF;
1066 --
1067 FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(p_stop_rows(i),p_caller));
1068 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1069 wsh_util_core.add_message(x_return_status);
1070 l_num_error := l_num_error + 1;
1071
1072 ELSIF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1073 l_num_warn := l_num_warn + 1;
1074 END IF;
1075
1076 END LOOP;
1077
1078 IF (p_stop_rows.count > 1) THEN
1079
1080 IF (l_num_error > 0) OR (l_num_warn > 0) THEN
1081
1082 IF (p_action = 'PLAN') THEN
1083 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_PLAN_SUMMARY');
1084 ELSE
1085 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_UNPLAN_SUMMARY');
1086 END IF;
1087
1088 FND_MESSAGE.SET_TOKEN('NUM_ERROR',l_num_error);
1089 FND_MESSAGE.SET_TOKEN('NUM_WARN',l_num_warn);
1090 FND_MESSAGE.SET_TOKEN('NUM_SUCCESS',p_stop_rows.count - l_num_error - l_num_warn);
1091 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1092 /*
1093 IF (p_stop_rows.count = l_num_error) THEN
1094 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1095 ELSE
1096 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1097 END IF;
1098 */
1099 wsh_util_core.add_message(x_return_status);
1100 ELSE
1101 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1102 END IF;
1103
1104 END IF;
1105
1106 --
1107 -- Debug Statements
1108 --
1109 IF l_debug_on THEN
1110 WSH_DEBUG_SV.pop(l_module_name);
1111 END IF;
1112 --
1113 EXCEPTION
1114 WHEN invalid_stop then
1115 IF (p_action = 'PLAN') THEN
1116 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_PLAN_ERROR');
1117 ELSE
1118 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_UNPLAN_ERROR');
1119 END IF;
1120
1121 FND_MESSAGE.SET_TOKEN('STOP_NAME',l_stop_id);
1122 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1123 wsh_util_core.add_message(x_return_status);
1124 --
1125 -- Debug Statements
1126 --
1127 IF l_debug_on THEN
1128 WSH_DEBUG_SV.logmsg(l_module_name,'INVALID_STOP exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1129 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INVALID_STOP');
1130 END IF;
1131 --
1132 WHEN others THEN
1133 wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.CHANGE_STATUS');
1134 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1135
1136
1137 --
1138 -- Debug Statements
1139 --
1140 IF l_debug_on THEN
1141 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1142 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1143 END IF;
1144 --
1145 END Plan;
1146
1147
1148
1149
1150 -- Procedure to calculate the Stop departure weight, volume
1151 -- Input parameter stop id, gets trip id and calls the trip weight, volume
1152 -- calculation API (more efficient)
1153
1154 PROCEDURE calc_stop_weight_volume( p_stop_rows IN wsh_util_core.id_tab_type,
1155 p_override_flag IN VARCHAR2,
1156 p_calc_wv_if_frozen IN VARCHAR2,
1157 x_return_status OUT NOCOPY VARCHAR2,
1158 --tkt
1159 p_caller IN VARCHAR2) IS
1160 dummy_trip_ids wsh_util_core.id_tab_type;
1161 l_stop_name VARCHAR2(100);
1162 l_override_flag VARCHAR2(1);
1163 l_suppress_errors VARCHAR2(1);
1164
1165 --
1166 l_debug_on BOOLEAN;
1167 --
1168 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CALC_STOP_WEIGHT_VOLUME';
1169 --
1170 BEGIN
1171 --
1172 -- Debug Statements
1173 --
1174 --
1175 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1176 --
1177 IF l_debug_on IS NULL
1178 THEN
1179 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1180 END IF;
1181 --
1182 IF l_debug_on THEN
1183 WSH_DEBUG_SV.push(l_module_name);
1184 --
1185 WSH_DEBUG_SV.log(l_module_name,'P_OVERRIDE_FLAG',P_OVERRIDE_FLAG);
1186 WSH_DEBUG_SV.log(l_module_name,'P_CALC_WV_IF_FROZEN',P_CALC_WV_IF_FROZEN);
1187 END IF;
1188 --
1189 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1190
1191 -- bug 2366163: overload p_override_flag 'X' to suppress errors
1192 IF p_override_flag = 'X' THEN
1193 l_override_flag := 'N';
1194 l_suppress_errors := 'Y';
1195 ELSE
1196 l_override_flag := p_override_flag;
1197 l_suppress_errors := NULL;
1198 END IF;
1199
1200 IF (p_stop_rows.count = 0) THEN
1201 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1202 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
1203 wsh_util_core.add_message(x_return_status);
1204 ELSE
1205
1206 FOR i IN 1..p_stop_rows.count LOOP
1207
1208 select trip_id into dummy_trip_ids(i)
1209 from wsh_trip_stops where stop_id=p_stop_rows(i);
1210 END LOOP;
1211
1212 END IF;
1213
1214 --pass trip id for trip weight/vol. calc.
1215 --
1216 -- Debug Statements
1217 --
1218 IF l_debug_on THEN
1219 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_ACTIONS.TRIP_WEIGHT_VOLUME',WSH_DEBUG_SV.C_PROC_LEVEL);
1220 END IF;
1221 --
1222 wsh_trips_actions.trip_weight_volume(
1223 p_trip_rows => dummy_trip_ids,
1224 p_override_flag => l_override_flag,
1225 p_calc_wv_if_frozen => p_calc_wv_if_frozen,
1226 p_start_departure_date => NULL,
1227 p_suppress_errors => l_suppress_errors,
1228 x_return_status => x_return_status,
1229 p_caller => p_caller);
1230
1231 --
1232 -- Debug Statements
1233 --
1234 IF l_debug_on THEN
1235 WSH_DEBUG_SV.pop(l_module_name);
1236 END IF;
1237 --
1238 EXCEPTION
1239 WHEN others THEN
1240 wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.CALC_STOP_WEIGHT_VOLUME');
1241 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1242
1243 --
1244 -- Debug Statements
1245 --
1246 IF l_debug_on THEN
1247 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1248 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1249 END IF;
1250 --
1251 END calc_stop_weight_volume;
1252 --
1253 --
1254 -- J-IB-NPARIKH-{
1255 --
1256 --
1257 --========================================================================
1258 -- PROCEDURE : autoCloseOpen
1259 --
1260 -- PARAMETERS: p_in_rec Input Record (Refer to WSHSTVLS.pls for description)
1261 -- p_reopenStop TRUE (open stop)/FALSE (close stop)
1262 -- x_stop_processed Input Stop was processed i.e. opened/closed. (Y/N)
1263 -- x_return_status Return status of API
1264 --
1265 -- COMMENT : This API is used only by inbound logistics functionality during ASN/Receipt
1266 -- integration for automatically closing/opening trip stops.
1267 -- Parameter p_reopenStop will determine whether API will try to perform stop open or close
1268 -- operation.
1269 --
1270 -- It performs following steps:
1271 -- 01. Check if stop can be closed or not.
1272 -- 02. If yes(even with warning), close the stop if p_reopenStop=FALSE
1273 -- 02.01. Before calling stop close api, calculate stop close date.
1274 -- 03. If no(even with warning), open the stop if p_reopenStop=TRUE
1275 --========================================================================
1276 --
1277 PROCEDURE autoCloseOpen
1278 (
1279 p_in_rec IN WSH_TRIP_STOPS_VALIDATIONS.chkClose_in_rec_type,
1280 p_reopenStop IN BOOLEAN DEFAULT FALSE,
1281 x_stop_processed OUT NOCOPY VARCHAR2,
1282 x_return_status OUT NOCOPY VARCHAR2
1283 )
1284 IS
1285 --{
1286 --
1287 l_return_status VARCHAR2(1);
1288 l_num_warnings NUMBER;
1289 l_num_errors NUMBER;
1290 --
1291 l_actual_date DATE := NULL;
1292 l_in_rec WSH_TRIP_STOPS_VALIDATIONS.chkClose_in_rec_type;
1293 l_out_rec WSH_TRIP_STOPS_VALIDATIONS.chkClose_out_rec_type;
1294 --
1295 l_debug_on BOOLEAN;
1296 --
1297 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'autoCloseOpen';
1298 --
1299 --}
1300 BEGIN
1301 --{
1302 --
1303 -- Debug Statements
1304 --
1305 --
1306 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1307 --
1308 IF l_debug_on IS NULL
1309 THEN
1310 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1311 END IF;
1312 --
1313 IF l_debug_on THEN
1314 WSH_DEBUG_SV.push(l_module_name);
1315 --
1316 WSH_DEBUG_SV.log(l_module_name,'P_in_rec.STOP_ID',P_in_rec.STOP_ID);
1317 WSH_DEBUG_SV.log(l_module_name,'P_in_rec.put_messages',P_in_rec.put_messages);
1318 WSH_DEBUG_SV.log(l_module_name,'P_in_rec.caller',P_in_rec.caller);
1319 WSH_DEBUG_SV.log(l_module_name,'P_in_rec.actual_date',P_in_rec.actual_date);
1320 WSH_DEBUG_SV.log(l_module_name,'p_reopenStop',p_reopenStop);
1321 END IF;
1322 --
1323 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1324 x_stop_processed := 'N';
1325 --
1326 l_in_rec := p_in_rec;
1327 l_in_rec.put_messages := FALSE;
1328 -- Internal locations changes 10+ for Inbound
1329 l_in_rec.caller := 'WSH_IB_'||p_in_rec.caller;
1330 --
1331 --
1332 IF l_debug_on THEN
1333 WSH_DEBUG_SV.log(l_module_name,'l_in_rec.caller',l_in_rec.caller);
1334 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_VALIDATIONS.CHECK_STOP_CLOSE',WSH_DEBUG_SV.C_PROC_LEVEL);
1335 END IF;
1336 --
1337 -- Check if stop can be closed or not.
1338 --
1339 WSH_TRIP_STOPS_VALIDATIONS.check_stop_close
1340 (
1341 p_in_rec => l_in_rec,
1342 x_out_rec => l_out_rec,
1343 x_return_status => l_return_status
1344 );
1345 --
1346 WSH_UTIL_CORE.api_post_call
1347 (
1348 p_return_status => l_return_status,
1349 x_num_warnings => l_num_warnings,
1350 x_num_errors => l_num_errors
1351 );
1352 --
1353 IF l_out_rec.close_Allowed IN ('Y','YW')
1354 AND NOT(p_reopenStop)
1355 THEN
1356 --{
1357 IF l_debug_on THEN
1358 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_VALIDATIONS.get_stop_close_date',WSH_DEBUG_SV.C_PROC_LEVEL);
1359 END IF;
1360 --
1361 -- Stop can be closed and input parameter p_reopenStop is FALSE.
1362 -- First, determine the stop close date
1363 -- and then call stop close api
1364 --
1365 WSH_TRIP_STOPS_VALIDATIONS.get_stop_close_date
1366 (
1367 p_trip_id => l_out_rec.trip_id,
1368 p_stop_id => p_in_rec.stop_id,
1369 p_stop_sequence_number => l_out_rec.stop_sequence_number,
1370 x_stop_close_date => l_actual_date,
1371 x_return_status => l_return_status
1372 );
1373 --
1374 WSH_UTIL_CORE.api_post_call
1375 (
1376 p_return_status => l_return_status,
1377 x_num_warnings => l_num_warnings,
1378 x_num_errors => l_num_errors
1379 );
1380 --
1381 --
1382 l_in_rec.actual_date := l_actual_date;
1383 l_in_rec.put_messages := TRUE;
1384 --
1385 --
1386 IF l_debug_on THEN
1387 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_ACTIONS.setClose',WSH_DEBUG_SV.C_PROC_LEVEL);
1388 END IF;
1389 --
1390 WSH_TRIP_STOPS_ACTIONS.setClose
1391 (
1392 p_in_rec => l_in_rec,
1393 p_in_rec1 => l_out_rec,
1394 p_defer_interface_flag => 'Y',
1395 x_return_status => l_return_status
1396 );
1397 --
1398 WSH_UTIL_CORE.api_post_call
1399 (
1400 p_return_status => l_return_status,
1401 x_num_warnings => l_num_warnings,
1402 x_num_errors => l_num_errors
1403 );
1404 --
1405 x_stop_processed := 'Y';
1406 --}
1407 ELSIF p_reopenStop
1408 AND l_out_rec.close_Allowed IN ('N','NW')
1409 THEN
1410 --{
1411 --
1412 -- Stop can not remain closed and input parameter p_reopenStop is TRUE.
1413 -- Call stop open api
1414 --
1415 l_in_rec.put_messages := TRUE;
1416 --
1417 --
1418 IF l_debug_on THEN
1419 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_ACTIONS.setOpen',WSH_DEBUG_SV.C_PROC_LEVEL);
1420 END IF;
1421 --
1422 WSH_TRIP_STOPS_ACTIONS.setOpen
1423 (
1424 p_in_rec => l_in_rec,
1425 p_in_rec1 => l_out_rec,
1426 x_return_status => l_return_status
1427 );
1428 --
1429 WSH_UTIL_CORE.api_post_call
1430 (
1431 p_return_status => l_return_status,
1432 x_num_warnings => l_num_warnings,
1433 x_num_errors => l_num_errors
1434 );
1435 --
1436 x_stop_processed := 'Y';
1437 --}
1438 END IF;
1439 --
1440 --
1441 IF l_num_errors > 0
1442 THEN
1443 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1444 ELSIF l_num_warnings > 0
1445 THEN
1446 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1447 ELSE
1448 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1449 END IF;
1450 --
1451 --
1452 -- Debug Statements
1453 --
1454 IF l_debug_on THEN
1455 WSH_DEBUG_SV.pop(l_module_name);
1456 END IF;
1457 --}
1458 EXCEPTION
1459 --
1460 WHEN FND_API.G_EXC_ERROR THEN
1461
1462 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1463 --
1464 IF l_debug_on THEN
1465 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1466 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1467 END IF;
1468 --
1469 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1470
1471 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1472 --
1473 IF l_debug_on THEN
1474 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1475 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1476 END IF;
1477 --
1478 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1479 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1480 --
1481 IF l_debug_on THEN
1482 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1483 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1484 END IF;
1485 --
1486 WHEN OTHERS THEN
1487
1488 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1489 wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.autoCloseOpen', l_module_name);
1490 IF l_debug_on THEN
1491 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1492 END IF;
1493 --
1494 END autoCloseOpen;
1495
1496 --
1497 --
1498 --========================================================================
1499 -- PROCEDURE : setClose
1500 --
1501 -- PARAMETERS: p_in_rec Input Record (Refer to WSHSTVLS.pls for description)
1502 -- p_in_rec1 Input Record (Refer to WSHSTVLS.pls for description)
1503 -- p_defer_interface_Flag Defer INV/OM Interface (Y/N)
1504 -- x_return_status Return status of API
1505 --
1506 -- PRE-REQS : Caller should set all the attributes of input parameters p_in_rec and p_in_rec1.
1507 -- Typically, this procedure gets called after call to
1508 -- WSH_TRIP_STOPS_VALIDATIONS.check_stop_close, which in turn will set its out
1509 -- parameter x_out_rec which can be passed as input (p_in_rec1) to this API.
1510 --
1511 --
1512 -- COMMENT : This API performs the stop close operation
1513 --
1514 -- It performs following steps:
1515 -- 01. Set deliveries starting from this stop to in-transit.
1516 -- 02. Unassign deliveries (starting from this stop) which cannot be set to in-transit
1517 -- 02.01. Mark other delivery legs on this trip as reprice required.
1518 -- 03. Update stop status and actual departure date
1519 -- 04. Update pending interface flag to Y if it is initial pickup stop for any delivery.
1520 -- 05. If FTE is installed and none of the deliveries associated with stop are carrier
1521 -- manifest enabled, rate the trip (call FTE API)
1522 -- 06. If p_defere_interface_flag='N' then interface trip stop to OM and INV
1523 -- 07. Set deliveries ending at this stop to closed.
1524 -- 08. Change trip status to in-transit, if open. Change trip status to closed,
1525 -- if all stops closed.
1526 --========================================================================
1527 --
1528 PROCEDURE setClose
1529 (
1530 p_in_rec IN WSH_TRIP_STOPS_VALIDATIONS.chkClose_in_rec_type,
1531 p_in_rec1 IN WSH_TRIP_STOPS_VALIDATIONS.chkClose_out_rec_type,
1532 p_defer_interface_Flag IN VARCHAR2,
1533 x_return_status OUT NOCOPY VARCHAR2
1534 )
1535 IS
1536 --{
1537 -- Unused cursors are removed
1538 --
1539 l_return_status VARCHAR2(1);
1540 l_num_warnings NUMBER := 0;
1541 l_num_errors NUMBER := 0;
1542 l_cnt NUMBER := 0;
1543 l_err_cnt NUMBER := 0;
1544 l_index NUMBER;
1545 --
1546 l_trip_tab wsh_util_core.id_tab_type;
1547 l_err_dlvy_id_tbl wsh_util_core.id_tab_type;
1548 l_dlvy_id_tbl wsh_util_core.id_tab_type;
1549 l_dlvy_orgid_tbl wsh_util_core.id_tab_type;
1550 l_warehouse_type VARCHAR2(30);
1551 l_cms_flag VARCHAR2(1);
1552 l_in_rec WSH_DELIVERY_VALIDATIONS.ChgStatus_in_rec_type;
1553 l_trip_in_rec WSH_TRIP_VALIDATIONS.ChgStatus_in_rec_type;
1554 l_stop_in_rec WSH_TRIP_STOPS_VALIDATIONS.chkClose_in_rec_type;
1555 --
1556 --
1557 l_stop_closed VARCHAR2(10);
1558 l_stop_opened VARCHAR2(10);
1559 l_stop_id NUMBER;
1560 l_stop_locationId NUMBER;
1561 --
1562 l_debug_on BOOLEAN;
1563 --
1564 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'setClose';
1565
1566 l_action_params WSH_FTE_INTEGRATION.rating_action_param_rec;
1567 l_msg_count NUMBER;
1568 l_msg_data VARCHAR2(2000);
1569 l_stop_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
1570 l_dbi_rs VARCHAR2(1); -- DBI Project
1571 l_wf_rs VARCHAR2(1); -- Workflow Project
1572
1573 --OTM R12, glog proj
1574 l_last_pickup_stop_closed VARCHAR2(1);
1575 l_tms_interface_flag WSH_NEW_DELIVERIES.TMS_INTERFACE_FLAG%TYPE;
1576 l_gc3_is_installed VARCHAR2(1);
1577 l_loop_counter NUMBER;
1578 --
1579
1580 --OTM R12 Org-Specific start
1581 l_eligible_for_asr VARCHAR2(1);
1582 --
1583 --}
1584 BEGIN
1585 --{
1586 --SAVEPOINT close_stop_begin_sp;
1587 --
1588 -- Debug Statements
1589 --
1590 --
1591 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1592 --
1593 IF l_debug_on IS NULL
1594 THEN
1595 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1596 END IF;
1597 --
1598 IF l_debug_on THEN
1599 WSH_DEBUG_SV.push(l_module_name);
1600 --
1601 WSH_DEBUG_SV.log(l_module_name,'P_in_rec.STOP_ID',P_in_rec.STOP_ID);
1602 WSH_DEBUG_SV.log(l_module_name,'P_in_rec.put_messages',P_in_rec.put_messages);
1603 WSH_DEBUG_SV.log(l_module_name,'P_in_rec.caller',P_in_rec.caller);
1604 WSH_DEBUG_SV.log(l_module_name,'P_in_rec.actual_date',P_in_rec.actual_date);
1605 WSH_DEBUG_SV.log(l_module_name,'P_DEFER_INTERFACE_FLAG',P_DEFER_INTERFACE_FLAG);
1606 WSH_DEBUG_SV.log(l_module_name,'P_in_rec1.trip_id',P_in_rec1.trip_id);
1607 WSH_DEBUG_SV.log(l_module_name,'P_in_rec1.service_level',P_in_rec1.service_level);
1608 WSH_DEBUG_SV.log(l_module_name,'P_in_rec1.carrier_id',P_in_rec1.carrier_id);
1609 WSH_DEBUG_SV.log(l_module_name,'P_in_rec1.mode_of_transport',P_in_rec1.mode_of_transport);
1610
1611 END IF;
1612 --
1613 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1614
1615 --OTM R12, glog proj, use Global Variable
1616 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
1617
1618 -- If null, call the function
1619 IF l_gc3_is_installed IS NULL THEN
1620 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
1621 END IF;
1622 IF l_debug_on THEN
1623 WSH_DEBUG_SV.log(l_module_name,'l_gc3_is_installed ',l_gc3_is_installed);
1624 END IF;
1625 -- end of OTM R12, glog proj
1626
1627 --
1628 --
1629 l_cnt := 0;
1630 l_err_cnt := 0;
1631 l_num_warnings := 0;
1632 l_num_errors := 0;
1633 --
1634 -- In parameter will indicate deliveries starting from this stop
1635 -- loop thro' input delivery id table
1636 --
1637 l_index := p_in_rec1.initial_pu_dlvy_recTbl.id_tbl.FIRST;
1638 --
1639 WHILE l_index IS NOT NULL
1640 LOOP
1641 --{
1642 l_in_rec.delivery_id := p_in_rec1.initial_pu_dlvy_recTbl.id_tbl(l_index);
1643 l_in_rec.name := p_in_rec1.initial_pu_dlvy_recTbl.name_tbl(l_index);
1644 l_in_rec.status_code := p_in_rec1.initial_pu_dlvy_recTbl.statusCode_tbl(l_index);
1645 l_in_rec.put_messages := TRUE; --p_in_rec.put_messages;
1646 l_in_rec.actual_date := p_in_rec.actual_date;
1647 l_in_rec.manual_flag := 'N';
1648 l_in_rec.caller := p_in_rec.caller;
1649 l_in_rec.stop_id := p_in_Rec.stop_id;
1650 --
1651 IF l_debug_on THEN
1652 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit setInTransit',WSH_DEBUG_SV.C_PROC_LEVEL);
1653 END IF;
1654 --
1655 -- Set delivery to in-transit.
1656 --
1657 WSH_NEW_DELIVERY_ACTIONS.setInTransit
1658 (
1659 p_in_rec => l_in_rec,
1660 x_return_status => l_return_status
1661 );
1662 --
1663 IF l_debug_on THEN
1664 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1665 END IF;
1666 --
1667 --
1668 wsh_util_core.api_post_call
1669 (
1670 p_return_status => l_return_status,
1671 x_num_warnings => l_num_warnings,
1672 x_num_errors => l_num_errors,
1673 p_raise_error_flag => FALSE
1674 );
1675 --
1676 --
1677 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
1678 THEN
1679 --{
1680 l_err_cnt := l_err_cnt + 1;
1681 --
1682 l_err_dlvy_id_tbl(l_err_cnt) := l_in_rec.delivery_id;
1683 --}
1684 ELSE
1685 --{
1686 l_cnt := l_cnt + 1;
1687 --
1688 l_dlvy_id_tbl(l_cnt) := l_in_rec.delivery_id;
1689 l_dlvy_orgid_tbl(l_cnt) := p_in_rec1.initial_pu_dlvy_recTbl.orgId_tbl(l_index);
1690 --}
1691 END IF;
1692 --
1693 l_index := p_in_rec1.initial_pu_dlvy_recTbl.id_tbl.NEXT(l_index);
1694 --}
1695 END LOOP;
1696 --
1697 IF l_cnt = 0
1698 AND l_err_cnt > 0
1699 THEN
1700 --{
1701 -- None of the candidate deliveries could be set to in-transit. Raise Error
1702 --
1703 RAISE FND_API.G_EXC_ERROR;
1704 --}
1705 END IF;
1706 --
1707 --
1708 IF p_in_rec1.initial_pu_err_dlvy_id_tbl.COUNT > 0
1709 THEN
1710 --{
1711 IF l_debug_on THEN
1712 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_LEGS_ACTIONS.UNASSIGN_DELIVERIES',WSH_DEBUG_SV.C_PROC_LEVEL);
1713 END IF;
1714 --
1715 -- Unassign deliveries (which were idenified during check_stop_close routine)
1716 -- which cannot be set to in-transit, from the trip
1717 --
1718 wsh_delivery_legs_actions.unassign_deliveries
1719 (
1720 p_del_rows => p_in_rec1.initial_pu_err_dlvy_id_tbl,
1721 p_trip_id => p_in_rec1.trip_id,
1722 p_pickup_stop_id => NULL,
1723 p_dropoff_stop_id => NULL,
1724 x_return_status => l_return_status
1725 );
1726 --
1727 --
1728 wsh_util_core.api_post_call
1729 (
1730 p_return_status => l_return_status,
1731 x_num_warnings => l_num_warnings,
1732 x_num_errors => l_num_errors
1733 );
1734 --
1735 --}
1736 END IF;
1737 --
1738 --
1739 IF l_err_cnt > 0
1740 THEN
1741 --{
1742 IF l_debug_on THEN
1743 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_LEGS_ACTIONS.UNASSIGN_DELIVERIES',WSH_DEBUG_SV.C_PROC_LEVEL);
1744 END IF;
1745 --
1746 --
1747 -- Unassign deliveries which cannot be set to in-transit(due to errors
1748 -- (while setting delivery to in-transit), from the trip
1749 --
1750 wsh_delivery_legs_actions.unassign_deliveries
1751 (
1752 p_del_rows => l_err_dlvy_id_tbl,
1753 p_trip_id => p_in_rec1.trip_id,
1754 p_pickup_stop_id => NULL,
1755 p_dropoff_stop_id => NULL,
1756 x_return_status => l_return_status
1757 );
1758 --
1759 wsh_util_core.api_post_call
1760 (
1761 p_return_status => l_return_status,
1762 x_num_warnings => l_num_warnings,
1763 x_num_errors => l_num_errors
1764 );
1765 --
1766 --}
1767 END IF;
1768 --
1769 --
1770 IF l_err_cnt > 0
1771 OR p_in_rec1.initial_pu_err_dlvy_id_tbl.COUNT > 0
1772 THEN
1773 --{
1774 l_trip_tab.delete;
1775 l_trip_tab(1) := p_in_rec1.trip_id;
1776 --
1777 -- Debug Statements
1778 --
1779 IF l_debug_on THEN
1780 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_LEGS_ACTIONS.MARK_REPRICE_REQUIRED',WSH_DEBUG_SV.C_PROC_LEVEL);
1781 END IF;
1782 --
1783 -- Dellvieries which could not be set to in-transit, were unassigned from trip.
1784 -- This changed trip contents and hence need to mark other deliveries on trip
1785 -- as reprice required
1786 --
1787 WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required (
1788 p_entity_type => 'TRIP',
1789 p_entity_ids => l_trip_tab,
1790 x_return_status => l_return_status);
1791 --
1792 wsh_util_core.api_post_call
1793 (
1794 p_return_status => l_return_status,
1795 x_num_warnings => l_num_warnings,
1796 x_num_errors => l_num_errors
1797 );
1798 --
1799 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_CLOSE_DEL_SUMMARY');
1800 FND_MESSAGE.SET_TOKEN('STOP_NAME', p_in_rec1.stop_name);
1801 FND_MESSAGE.SET_TOKEN('NUM_SUCCESS', l_cnt);
1802 FND_MESSAGE.SET_TOKEN('NUM_ERROR', l_err_cnt+p_in_rec1.initial_pu_err_dlvy_id_tbl.COUNT);
1803 l_num_warnings := l_num_warnings + 1;
1804 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING);
1805 --}
1806 END IF;
1807 --
1808 IF l_cnt > 0
1809 THEN
1810 --{
1811 IF p_in_rec1.ship_method_code IS NOT NULL
1812 THEN
1813 --{
1814 -- Bug 1783103 and 2699764 :Ship Method and carrier at Ship Confirm is not reflected at Delivery
1815 -- To update deliveries with the ship method and carrier entered for the trips
1816 --
1817 FORALL i IN l_dlvy_id_tbl.FIRST..l_dlvy_id_tbl.LAST
1818 UPDATE wsh_new_deliveries
1819 SET ship_method_code = p_in_rec1.ship_method_code,
1820 carrier_id = p_in_rec1.carrier_id,
1821 service_level = p_in_rec1.service_level,
1822 mode_of_transport = p_in_rec1.mode_of_transport,
1823 last_update_date = SYSDATE,
1824 last_updated_by = FND_GLOBAL.USER_ID,
1825 last_update_login = FND_GLOBAL.LOGIN_ID
1826 WHERE delivery_id = l_dlvy_id_tbl(i)
1827 AND NVL(ship_method_code,' ') <> p_in_rec1.ship_method_code;
1828 --}
1829 END IF;
1830 --}
1831 END IF;
1832 --
1833 --
1834 -- Here we update the status for any stop and set the pending interface flag to 'Y' if
1835 -- it is the initial pickup stop of a delivery.
1836 --
1837
1838 -- OTM R12, glog proj
1839
1840 BEGIN--{
1841
1842 IF (l_gc3_is_installed = 'Y') THEN --{
1843 --OTM R12 Org-Specific start.
1844 last_pickup_stop_closed(
1845 p_trip_id => p_in_rec1.trip_id
1846 ,p_stop_id => p_in_rec.stop_id
1847 ,x_last_pickup_stop_closed => l_last_pickup_stop_closed
1848 ,x_eligible_for_asr => l_eligible_for_asr
1849 ,x_return_status => l_return_status );
1850 IF l_debug_on THEN
1851 WSH_DEBUG_SV.log(l_module_name,'x_return_status',l_return_status);
1852 WSH_DEBUG_SV.log(l_module_name,'l_last_pickup_stop_closed',l_last_pickup_stop_closed);
1853 WSH_DEBUG_SV.log(l_module_name,'l_eligible_for_asr',l_eligible_for_asr);
1854 END IF;
1855
1856 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1857 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1858
1859 IF l_debug_on THEN
1860 wsh_debug_sv.log(l_module_name,'last_pickup_stop_closed return error');
1861 END IF;
1862 wsh_util_core.add_message(l_return_status);
1863 RAISE FND_API.G_EXC_ERROR;
1864 END IF;
1865 IF l_last_pickup_stop_closed = 'Y' AND l_eligible_for_asr = 'Y' THEN --{
1866 l_tms_interface_flag := WSH_TRIP_STOPS_PVT.C_TMS_ACTUAL_REQUEST;
1867 ELSE
1868 l_tms_interface_flag := WSH_TRIP_STOPS_PVT.C_TMS_NOT_TO_BE_SENT;
1869 END IF; --}
1870 --OTM R12 End
1871 END IF; --} -- gc3 is installed
1872 --
1873
1874 --
1875 -- Bug 3901377, actual_arrival_date could have been populated if stop was marked as arrived
1876 -- before. To preserve that date use an extra NVL
1877 UPDATE wsh_trip_stops
1878 SET pending_interface_flag = DECODE(l_cnt,0,pending_interface_flag,'Y'),
1879 status_code = 'CL',
1880 actual_departure_date = NVL(p_in_rec.actual_date, SYSDATE),
1881 actual_arrival_date = NVL(actual_arrival_date,NVL(p_in_rec.actual_date,SYSDATE)), -- Bug 3901377
1882 departure_seal_code = NVL(departure_seal_code,p_in_rec1.trip_seal_code),
1883 tms_interface_flag = l_tms_interface_flag, --OTM R12 Org-Specific.
1884 last_update_date = sysdate,
1885 last_updated_by = FND_GLOBAL.USER_ID,
1886 last_update_login = FND_GLOBAL.LOGIN_ID
1887 WHERE stop_id IN (p_in_rec.stop_id,
1888 p_in_rec1.linked_stop_id) --wr
1889 RETURNING stop_id BULK COLLECT INTO l_stop_tab; -- Added for DBI Project
1890 --
1891
1892 IF (SQL%NOTFOUND)
1893 THEN
1894 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_EXIST');
1895 FND_MESSAGE.SET_TOKEN('STOP_ID',p_in_rec.stop_id);
1896 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
1897 RAISE FND_API.G_EXC_ERROR;
1898 END IF;
1899 --
1900 --
1901 EXCEPTION
1902 WHEN others THEN
1903 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1904 END;--}
1905 --
1906
1907 -- Workflow Project
1908 -- Raise Stop close business event
1909 IF l_debug_on THEN
1910 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.RAISE_EVENT',WSH_DEBUG_SV.C_PROC_LEVEL);
1911 END IF;
1912
1913 WSH_WF_STD.RAISE_EVENT( p_entity_type => 'STOP',
1914 p_entity_id => p_in_rec.stop_id,
1915 p_event => 'oracle.apps.wsh.stop.gen.closed',
1916 x_return_status => l_wf_rs
1917 );
1918 IF l_debug_on THEN
1919 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_WF_STD.RAISE_EVENT => ',l_wf_rs);
1920 END IF;
1921
1922 IF p_in_rec1.linked_stop_id IS NOT NULL THEN
1923 IF l_debug_on THEN
1924 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.RAISE_EVENT',WSH_DEBUG_SV.C_PROC_LEVEL);
1925 END IF;
1926 WSH_WF_STD.RAISE_EVENT( p_entity_type => 'STOP',
1927 p_entity_id => p_in_rec1.linked_stop_id,
1928 p_event => 'oracle.apps.wsh.stop.gen.closed',
1929 x_return_status => l_wf_rs
1930 );
1931 IF l_debug_on THEN
1932 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_WF_STD.RAISE_EVENT => ',l_wf_rs);
1933 END IF;
1934 END IF;
1935 -- End of code for Workflow project
1936 --
1937 -- DBI Project
1938 -- Updating WSH_TRIP_STOPS.
1939 -- Call DBI API after the Update.
1940 -- This API will also check for DBI Installed or not
1941 IF l_debug_on THEN
1942 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Stop Count -',l_stop_tab.count);
1943 END IF;
1944 WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
1945 (p_stop_id_tab => l_stop_tab,
1946 p_dml_type => 'UPDATE',
1947 x_return_status => l_dbi_rs);
1948
1949 IF l_debug_on THEN
1950 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
1951 END IF;
1952 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1953 x_return_status := l_dbi_rs;
1954 -- just pass this return status to caller API
1955 IF l_debug_on THEN
1956 WSH_DEBUG_SV.log(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
1957 WSH_DEBUG_SV.pop(l_module_name);
1958 END IF;
1959 return;
1960 END IF;
1961 -- End of Code for DBI Project
1962 --
1963
1964 --
1965 /* H integration: Pricing integration csun */
1966 IF WSH_UTIL_CORE.FTE_Is_Installed = 'Y'
1967 THEN
1968 --{
1969 -- Bug fix 2489164
1970 -- Find if the deliveries in the stop are carrier manifest enabled
1971 -- Even if one delivery is carrier manifest enabled, set the flag l_cms_tpw_flag to Y
1972 --
1973 l_cms_flag := 'N';
1974 --
1975 --
1976 --
1977 l_index := l_dlvy_id_tbl.FIRST;
1978 --
1979 WHILE l_index IS NOT NULL
1980 LOOP
1981 --{
1982 IF l_debug_on THEN
1983 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_EXTERNAL_INTERFACE_SV.GET_WAREHOUSE_TYPE',WSH_DEBUG_SV.C_PROC_LEVEL);
1984 END IF;
1985 --
1986 l_warehouse_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type
1987 (
1988 p_organization_id => l_dlvy_orgid_tbl(l_index),
1989 x_return_status => l_return_status,
1990 p_delivery_id => l_dlvy_id_tbl(l_index)
1991 );
1992 --
1993 IF l_debug_on THEN
1994 wsh_debug_sv.log(l_module_name, 'Get WH type ret sts', l_return_status);
1995 wsh_debug_sv.log(l_module_name, 'Warehouse Type', l_warehouse_type);
1996 END IF;
1997 --
1998 --
1999 wsh_util_core.api_post_call
2000 (
2001 p_return_status => l_return_status,
2002 x_num_warnings => l_num_warnings,
2003 x_num_errors => l_num_errors
2004 );
2005 --
2006 IF(nvl(l_warehouse_type, '!') = 'CMS') THEN
2007 l_cms_flag := 'Y';
2008 exit;
2009 END IF;
2010 --
2011 l_index := l_dlvy_id_tbl.NEXT(l_index);
2012 --}
2013 END LOOP;
2014 --
2015 --
2016 l_index := p_in_rec1.ultimate_do_dlvy_recTbl.id_tbl.FIRST;
2017 --
2018 WHILE l_index IS NOT NULL
2019 LOOP
2020 --{
2021 IF l_debug_on THEN
2022 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_EXTERNAL_INTERFACE_SV.GET_WAREHOUSE_TYPE',WSH_DEBUG_SV.C_PROC_LEVEL);
2023 END IF;
2024 --
2025 l_warehouse_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type
2026 (
2027 p_organization_id => p_in_rec1.ultimate_do_dlvy_recTbl.orgId_tbl(l_index),
2028 x_return_status => l_return_status,
2029 p_delivery_id => p_in_rec1.ultimate_do_dlvy_recTbl.id_tbl(l_index)
2030 );
2031 --
2032 IF l_debug_on THEN
2033 wsh_debug_sv.log(l_module_name, 'Get WH type ret sts', l_return_status);
2034 wsh_debug_sv.log(l_module_name, 'Warehouse Type', l_warehouse_type);
2035 END IF;
2036 --
2037 --
2038 wsh_util_core.api_post_call
2039 (
2040 p_return_status => l_return_status,
2041 x_num_warnings => l_num_warnings,
2042 x_num_errors => l_num_errors
2043 );
2044 --
2045 IF(nvl(l_warehouse_type, '!') = 'CMS') THEN
2046 l_cms_flag := 'Y';
2047 exit;
2048 END IF;
2049 --
2050 l_index := p_in_rec1.ultimate_do_dlvy_recTbl.id_tbl.NEXT(l_index);
2051 --}
2052 END LOOP;
2053 --
2054 -- Bug fix 2489164
2055 -- Call Shipment_Price_Consolidate API only for NON carrier manifest enabled cases
2056 IF l_debug_on THEN
2057 wsh_debug_sv.log(l_module_name, 'l_cms_flag', l_cms_flag);
2058 wsh_debug_sv.log(l_module_name, 'WSH_TRIPS_ACTIONS.g_rate_trip_id',WSH_TRIPS_ACTIONS.g_rate_trip_id);
2059 wsh_debug_sv.log(l_module_name, 'p_in_rec1.trip_id',p_in_rec1.trip_id);
2060 END IF;
2061 --
2062
2063 IF (nvl(WSH_TRIPS_ACTIONS.g_rate_trip_id,-99) <> p_in_rec1.trip_id) THEN --{
2064 IF ( nvl(l_cms_flag, 'N') = 'N')
2065 AND p_in_rec1.service_level is not null
2066 AND p_in_rec1.carrier_id is not null
2067 AND p_in_rec1.mode_of_transport is not null
2068 THEN
2069 -- Bug 3296121 call rate_trip only if all of these fileds are specified
2070 --{
2071 IF l_debug_on THEN
2072 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_FTE_INTEGRATION.Rate_Trip',WSH_DEBUG_SV.C_PROC_LEVEL);
2073 END IF;
2074 --
2075 WSH_TRIPS_ACTIONS.g_rate_trip_id := p_in_rec1.trip_id;
2076
2077 l_action_params.caller := 'WSH';
2078 l_action_params.event := 'SHIP-CONFIRM';
2079 l_action_params.action := 'RATE';
2080 l_action_params.trip_id_list(1) := p_in_rec1.trip_id;
2081 WSH_FTE_INTEGRATION.Rate_Trip (
2082 p_api_version => 1.0,
2083 p_init_msg_list => FND_API.G_FALSE,
2084 p_action_params => l_action_params,
2085 p_commit => FND_API.G_FALSE,
2086 x_return_status => l_return_status,
2087 x_msg_count => l_msg_count,
2088 x_msg_data => l_msg_data);
2089 --
2090 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
2091 THEN
2092 FND_MESSAGE.SET_NAME('WSH','WSH_PRICE_CNSLD_UNEXP_ERROR');
2093 FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_in_rec1.trip_id));
2094 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
2095 RAISE FND_API.G_EXC_ERROR;
2096 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
2097 -- Bug 3278059, treat warning from rate_trip as success
2098 THEN
2099 --{
2100 l_num_warnings := l_num_warnings + 1;
2101 --
2102 FND_MESSAGE.SET_NAME('WSH','WSH_PRICE_CNSLD_ERROR');
2103 FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_in_rec1.trip_id));
2104 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING,l_module_name);
2105 --}
2106 END IF;
2107 --}
2108 END IF;
2109 END IF; --}
2110 --}
2111 END IF;
2112 --
2113 -- bug 1578251: defer submission of concurrent program to allow batch-processing.
2114 -- Call INV/OM interface only if atleast one delivery is picked up
2115 -- and if p_defer_interface_flag = 'N' (bug 1578251)
2116 /*2848835*/
2117 --
2118 --
2119 IF l_cnt > 0 -- at least one delivery was set to in-transit
2120 AND NVL(p_defer_interface_flag, 'Y') = 'N'
2121 THEN
2122 --{
2123 IF l_debug_on THEN
2124 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIP_CONFIRM_ACTIONS.SHIP_CONFIRM_A_TRIP_STOP',WSH_DEBUG_SV.C_PROC_LEVEL);
2125 END IF;
2126 --
2127 wsh_ship_confirm_actions.ship_confirm_a_trip_stop
2128 (
2129 p_in_rec.stop_id,
2130 l_return_status
2131 );
2132 --
2133 IF l_debug_on THEN
2134 wsh_debug_sv.log(l_module_name, 'Return status ship_confirm_a_trip_stop', l_return_status);
2135 END IF;
2136 --
2137 --
2138 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
2139 THEN
2140 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2141 ELSIF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2142 THEN
2143 --{
2144 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING
2145 THEN
2146 l_num_warnings := l_num_warnings + 1;
2147 ELSE
2148 l_num_errors := l_num_errors + 1;
2149 END IF;
2150 --
2151 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_CONFIRM_ERROR');
2152 FND_MESSAGE.SET_TOKEN('STOP_NAME',p_in_rec1.stop_name);
2153 FND_MESSAGE.SET_TOKEN('TRIP_NAME',p_in_rec1.trip_name);
2154 wsh_util_core.add_message(l_return_status,l_module_name);
2155 --}
2156 END IF;
2157 --}
2158 END IF;
2159 --
2160 --
2161 -- In parameter (p_in_rec1.ultimate_do_dlvy_recTbl) will indicate deliveries ending at this stop
2162 -- loop thro' input delivery id table
2163 --
2164 --
2165 l_index := p_in_rec1.ultimate_do_dlvy_recTbl.id_tbl.FIRST;
2166 --
2167 WHILE l_index IS NOT NULL
2168 LOOP
2169 --{
2170 l_in_rec.delivery_id := p_in_rec1.ultimate_do_dlvy_recTbl.id_tbl(l_index);
2171 l_in_rec.name := p_in_rec1.ultimate_do_dlvy_recTbl.name_tbl(l_index);
2172 l_in_rec.status_code := p_in_rec1.ultimate_do_dlvy_recTbl.statusCode_tbl(l_index);
2173 l_in_rec.put_messages := TRUE; --p_in_rec.put_messages;
2174 l_in_rec.actual_date := p_in_rec.actual_date;
2175 l_in_rec.manual_flag := 'N';
2176 l_in_rec.caller := p_in_rec.caller;
2177 l_in_rec.stop_id := p_in_Rec.stop_id;
2178 --
2179 IF l_debug_on THEN
2180 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERY_ACTIONS.setClose',WSH_DEBUG_SV.C_PROC_LEVEL);
2181 END IF;
2182 --
2183 -- Set delivery to close
2184 --
2185 WSH_NEW_DELIVERY_ACTIONS.setClose
2186 (
2187 p_in_rec => l_in_rec,
2188 x_return_status => l_return_status
2189 );
2190 --
2191 IF l_debug_on THEN
2192 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2193 END IF;
2194 --
2195 --
2196 wsh_util_core.api_post_call
2197 (
2198 p_return_status => l_return_status,
2199 x_num_warnings => l_num_warnings,
2200 x_num_errors => l_num_errors
2201 );
2202 --
2203 --
2204 l_index := p_in_rec1.ultimate_do_dlvy_recTbl.id_tbl.NEXT(l_index);
2205 --}
2206 END LOOP;
2207 --
2208 --
2209 -- If trip's status has changed (set by caller, typically check_stop_Close)
2210 -- call API to update trip status
2211 --
2212 IF p_in_rec1.trip_status_code <> p_in_rec1.trip_new_status_code
2213 THEN
2214 --{
2215 l_trip_in_rec.trip_id := p_in_rec1.trip_id;
2216 l_trip_in_rec.name := p_in_rec1.trip_name;
2217 l_trip_in_rec.new_status_code := p_in_rec1.trip_new_status_code;
2218 l_trip_in_rec.put_messages := TRUE; --p_in_rec.put_messages;
2219 l_trip_in_rec.actual_date := p_in_rec.actual_date;
2220 l_trip_in_rec.manual_flag := 'N';
2221 l_trip_in_rec.caller := p_in_rec.caller;
2222 l_trip_in_rec.stop_id := p_in_Rec.stop_id;
2223 --
2224 --
2225 IF l_debug_on THEN
2226 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_ACTIONS.CHANGESTATUS',WSH_DEBUG_SV.C_PROC_LEVEL);
2227 END IF;
2228 --
2229 wsh_trips_actions.changeStatus
2230 (
2231 p_in_rec => l_trip_in_rec,
2232 x_return_status => l_return_status
2233 );
2234 --
2235 wsh_util_core.api_post_call
2236 (
2237 p_return_status => l_return_status,
2238 x_num_warnings => l_num_warnings,
2239 x_num_errors => l_num_errors
2240 );
2241 --}
2242 END IF;
2243 --
2244 l_stop_closed := 'N';
2245 --
2246 --
2247 --
2248 IF l_num_errors > 0
2249 THEN
2250 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2251 ELSIF l_num_warnings > 0
2252 THEN
2253 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2254 ELSE
2255 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2256 END IF;
2257 --
2258 --
2259 --
2260 -- Debug Statements
2261 --
2262 IF l_debug_on THEN
2263 WSH_DEBUG_SV.pop(l_module_name);
2264 END IF;
2265 --
2266 --}
2267 EXCEPTION
2268 --{
2269 --
2270 WHEN FND_API.G_EXC_ERROR THEN
2271 --ROLLBACK TO close_stop_begin_sp;
2272 --
2273 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2274 --
2275 IF l_debug_on THEN
2276 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2277 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2278 END IF;
2279 --
2280 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2281 --ROLLBACK TO close_stop_begin_sp;
2282 --
2283 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2284 --
2285 IF l_debug_on THEN
2286 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2287 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2288 END IF;
2289 --
2290 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
2291 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2292 --
2293 IF l_debug_on THEN
2294 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2295 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2296 END IF;
2297 WHEN others THEN
2298 wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.setClose',l_module_name);
2299 --
2300 --ROLLBACK TO close_stop_begin_sp;
2301 --
2302 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2303 --
2304 -- Debug Statements
2305 --
2306 IF l_debug_on THEN
2307 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2308 END IF;
2309 --
2310 --}
2311 END setClose;
2312
2313
2314 --
2315 --
2316 --========================================================================
2317 -- PROCEDURE : setOpen
2318 --
2319 -- PARAMETERS: p_in_rec Input Record (Refer to WSHSTVLS.pls for description)
2320 -- p_in_rec1 Input Record (Refer to WSHSTVLS.pls for description)
2321 -- x_return_status Return status of API
2322 --
2323 -- PRE-REQS : Caller should set all the attributes of input parameters p_in_rec and p_in_rec1.
2324 -- Typically, this procedure gets called after call to
2325 -- WSH_TRIP_STOPS_VALIDATIONS.check_stop_close, which in turn will set its out
2326 -- parameter x_out_rec which can be passed as input (p_in_rec1) to this API.
2327 --
2328 --
2329 -- COMMENT : This API performs the stop open operation. This is only required for
2330 -- inbound logistics functionality while reverting ASN/Receipt or cancelling ASN.
2331 --
2332 -- It performs following steps:
2333 -- 01. Update stop status to open and actual departure date to null
2334 -- 02. Check the trip status (given by input parameter p_in_rec1.trip_status_code)
2335 -- 02.01 If trip status is Closed, call WSH_TRIP_VALIDATIONS.check_close which
2336 -- will indicate if trip can be(or remain) closed or not. If not, set trip
2337 -- status to in-transit.
2338 -- 02.02 If trip status is in-transit, call WSH_TRIP_VALIDATIONS.check_intransit which
2339 -- will indicate if trip can be(or remain) in-transit or not. If not, set trip
2340 -- status to open.
2341 --========================================================================
2342 --
2343 PROCEDURE setOpen
2344 (
2345 p_in_rec IN WSH_TRIP_STOPS_VALIDATIONS.chkClose_in_rec_type,
2346 p_in_rec1 IN WSH_TRIP_STOPS_VALIDATIONS.chkClose_out_rec_type,
2347 x_return_status OUT NOCOPY VARCHAR2
2348 )
2349 IS
2350 --{
2351 -- Following cursor is not used
2352 --
2353 CURSOR next_ib_stops_cur (p_trip_id IN NUMBER, p_stop_sequence_number IN NUMBER)
2354 IS
2355 SELECT stop_id, stop_sequence_number,
2356 NVL(shipments_type_flag,'O') shipments_type_flag
2357 FROM wsh_trip_stops
2358 WHERE trip_id = p_trip_id
2359 AND stop_sequence_number > p_stop_sequence_number
2360 AND NVL(shipments_type_flag,'O') IN ( 'I','M')
2361 AND status_code = 'CL'
2362 ORDER BY stop_sequence_number;
2363 --
2364 --
2365 -- Following cursor is not used
2366 --
2367 CURSOR ib_dropoff_stop_csr(p_pu_stop_id IN NUMBER, p_do_stop_id IN NUMBER)
2368 IS
2369 SELECT 1
2370 FROM wsh_delivery_legs wdl,
2371 wsh_new_deliveries wnd
2372 WHERE wdl.pick_up_stop_id = p_pu_stop_id
2373 AND wdl.drop_off_stop_id = p_do_stop_id
2374 AND wdl.delivery_id = wnd.delivery_id
2375 AND NVL(wnd.shipment_direction,'O') NOT IN ('O','IO')
2376 AND rownum = 1;
2377
2378 -- Following cursor is not used
2379 --
2380 CURSOR ib_dlvy_csr(p_stop_id IN NUMBER)
2381 IS
2382 SELECT wnd.delivery_id, wts.stop_location_id, wnd.ultimate_dropoff_location_id
2383 FROM wsh_delivery_legs wdl,
2384 wsh_new_deliveries wnd,
2385 wsh_trip_stops wts
2386 WHERE wdl.drop_off_stop_id = p_stop_id
2387 AND wts.stop_id = p_stop_id
2388 AND wdl.delivery_id = wnd.delivery_id
2389 AND NVL(wnd.shipment_direction,'O') NOT IN ('O','IO');
2390
2391
2392 -- Following cursor is not used
2393 --
2394 CURSOR next_leg_csr (p_stop_id IN NUMBER, p_delivery_id IN NUMBER) IS
2395 SELECT next_leg_do_stop.status_code do_stop_statusCode,
2396 NVL(next_leg_do_stop.shipments_type_flag,'O') do_stop_shipTypeFlag,
2397 next_leg_do_stop.stop_location_id do_stop_locationId,
2398 next_leg_do_stop.stop_id do_stop_id,
2399 next_leg_pu_stop.status_code pu_stop_statusCode,
2400 NVL(next_leg_pu_stop.shipments_type_flag,'O') pu_stop_shipTypeFlag,
2401 next_leg_pu_stop.stop_location_id pu_stop_locationId,
2402 next_leg_pu_stop.stop_id pu_stop_id,
2403 NVL(wnd.shipment_direction,'O') shipment_direction,
2404 wnd.status_code dlvy_status_code,
2405 wnd.ultimatE_dropoff_location_id dlvy_ultimate_doLocationId
2406 FROM wsh_trip_stops next_leg_do_stop,
2407 wsh_trip_stops next_leg_pu_stop,
2408 wsh_trip_stops curr_leg_do_stop,
2409 wsh_delivery_legs next_leg,
2410 wsh_delivery_legs curr_leg,
2411 wsh_new_deliveries wnd
2412 WHERE next_leg.drop_off_stop_id = next_leg_do_stop.stop_id
2413 --AND st1.status_code = 'OP'
2414 AND next_leg.pick_up_stop_id = next_leg_pu_stop.stop_id
2415 AND next_leg_pu_stop.stop_location_id = curr_leg_do_stop.stop_location_id
2416 AND next_leg.delivery_id = curr_leg.delivery_id
2417 AND curr_leg_do_stop.stop_id = p_stop_id
2418 AND curr_leg.drop_off_stop_id = p_stop_id
2419 AND wnd.delivery_id = curr_leg.delivery_id
2420 AND wnd.delivery_id = p_delivery_id;
2421 --AND NVL(wnd.shipment_direction,'O') NOT IN ('O','IO')
2422
2423 --
2424 --
2425 l_return_status VARCHAR2(1);
2426 l_num_warnings NUMBER;
2427 l_num_errors NUMBER;
2428 --
2429 --
2430 l_trip_Status_code VARCHAR2(30);
2431 l_stop_opened VARCHAR2(10);
2432 l_allowed VARCHAR2(10);
2433 --
2434 l_trip_in_rec WSH_TRIP_VALIDATIONS.ChgStatus_in_rec_type;
2435 l_stop_in_rec WSH_TRIP_STOPS_VALIDATIONS.chkClose_in_rec_type;
2436 l_stop_id_tbl WSH_UTIL_CORE.id_tab_type;
2437 l_stop_cnt NUMBER := 0;
2438 l_index NUMBER;
2439 l_stop_id NUMBER;
2440 l_stop_locationId NUMBER;
2441 --
2442 l_debug_on BOOLEAN;
2443 --
2444 l_stop_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
2445 l_dbi_rs VARCHAR2(1); -- DBI Project
2446 l_wf_rs VARCHAR2(1); -- Workflow Project
2447 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'setOpen';
2448 --
2449 --}
2450 BEGIN
2451 --{
2452 --SAVEPOINT open_stop_begin_sp;
2453 --
2454 -- Debug Statements
2455 --
2456 --
2457 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2458 --
2459 IF l_debug_on IS NULL
2460 THEN
2461 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2462 END IF;
2463 --
2464 IF l_debug_on THEN
2465 WSH_DEBUG_SV.push(l_module_name);
2466 --
2467 WSH_DEBUG_SV.log(l_module_name,'P_in_rec.STOP_ID',P_in_rec.STOP_ID);
2468 WSH_DEBUG_SV.log(l_module_name,'P_in_rec.put_messages',P_in_rec.put_messages);
2469 WSH_DEBUG_SV.log(l_module_name,'P_in_rec.caller',P_in_rec.caller);
2470 WSH_DEBUG_SV.log(l_module_name,'P_in_rec.actual_date',P_in_rec.actual_date);
2471 WSH_DEBUG_SV.log(l_module_name,'P_in_rec1.trip_status_code',P_in_rec1.trip_status_code);
2472 END IF;
2473 --
2474 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2475 --
2476 --
2477 l_num_warnings := 0;
2478 l_num_errors := 0;
2479 --
2480 --
2481 UPDATE wsh_trip_stops
2482 SET status_code = 'OP',
2483 actual_departure_date = NULL,
2484 actual_arrival_date = NULL, -- Bug 3901377
2485 --actual_departure_date = nvl(p_in_rec.actual_date, SYSDATE),
2486 --departure_seal_code = NVL(p_in_rec1.trip_seal_code, departure_seal_code),
2487 last_update_date = sysdate,
2488 last_updated_by = FND_GLOBAL.USER_ID,
2489 last_update_login = FND_GLOBAL.LOGIN_ID
2490 WHERE stop_id = p_in_rec.stop_id
2491 RETURNING stop_id BULK COLLECT INTO l_stop_tab; -- Added for DBI Project;
2492
2493 --
2494 IF (SQL%NOTFOUND)
2495 THEN
2496 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_EXIST');
2497 FND_MESSAGE.SET_TOKEN('STOP_ID',p_in_rec.stop_id);
2498 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
2499 RAISE FND_API.G_EXC_ERROR;
2500 END IF;
2501 --
2502
2503 -- Workflow Project
2504 -- Raise Stop Open business event
2505 IF l_debug_on THEN
2506 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.RAISE_EVENT',WSH_DEBUG_SV.C_PROC_LEVEL);
2507 END IF;
2508
2509 WSH_WF_STD.RAISE_EVENT( p_entity_type => 'STOP',
2510 p_entity_id => p_in_rec.stop_id,
2511 p_event => 'oracle.apps.wsh.stop.gen.open',
2512 x_return_status => l_wf_rs
2513 );
2514 IF l_debug_on THEN
2515 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_WF_STD.RAISE_EVENT => ',l_wf_rs);
2516 END IF;
2517 -- End of code for Workflow project
2518 --
2519 -- DBI Project
2520 -- Updating WSH_TRIP_STOPS.
2521 -- Call DBI API after the Update.
2522 -- This API will also check for DBI Installed or not
2523 IF l_debug_on THEN
2524 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Stop Count -',l_stop_tab.count);
2525 END IF;
2526 WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
2527 (p_stop_id_tab => l_stop_tab,
2528 p_dml_type => 'UPDATE',
2529 x_return_status => l_dbi_rs);
2530
2531 IF l_debug_on THEN
2532 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
2533 END IF;
2534 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2535 x_return_status := l_dbi_rs;
2536 -- just pass this return status to caller API
2537 IF l_debug_on THEN
2538 WSH_DEBUG_SV.log(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
2539 WSH_DEBUG_SV.pop(l_module_name);
2540 END IF;
2541 return;
2542 END IF;
2543 -- End of Code for DBI Project
2544 --
2545
2546 --
2547 l_trip_status_code := p_in_rec1.trip_status_code;
2548 --
2549 --
2550 l_stop_cnt := l_stop_cnt + 1;
2551 l_stop_id_tbl(l_stop_cnt) := p_in_rec.stop_id;
2552 --
2553 --
2554 --
2555 l_stop_opened := 'N';
2556 --
2557 FOR next_ib_stops_rec IN next_ib_stops_cur
2558 (
2559 p_trip_id => p_in_rec1.trip_id,
2560 p_stop_sequence_number => p_in_rec1.stop_sequence_number
2561 )
2562 LOOP
2563 --{
2564 IF next_ib_stops_rec.shipments_type_flag = 'I'
2565 AND l_stop_opened = 'N'
2566 THEN
2567 --{
2568 l_stop_in_rec.stop_id := next_ib_stops_rec.stop_id;
2569 l_stop_in_rec.put_messages := FALSE;
2570 l_stop_in_rec.caller := p_in_rec.caller;
2571 l_stop_in_rec.actual_date := p_in_rec.actual_date;
2572 --
2573 --
2574 IF l_debug_on THEN
2575 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_ACTIONS.autoCLOSE',WSH_DEBUG_SV.C_PROC_LEVEL);
2576 END IF;
2577 --
2578 WSH_TRIP_STOPS_ACTIONS.autoCloseOpen
2579 (
2580 p_in_rec => l_stop_in_rec,
2581 p_reopenStop => TRUE,
2582 x_stop_processed => l_stop_opened,
2583 x_return_status => l_return_status
2584 );
2585 --
2586 WSH_UTIL_CORE.api_post_call
2587 (
2588 p_return_status => l_return_status,
2589 x_num_warnings => l_num_warnings,
2590 x_num_errors => l_num_errors
2591 );
2592 --}
2593 ELSE
2594 --{
2595 FOR ib_dropoff_stop_rec IN ib_dropoff_stop_csr
2596 (
2597 p_pu_stop_id => p_in_rec.stop_id,
2598 p_do_stop_id => next_ib_stops_rec.stop_id
2599 )
2600 LOOP
2601 --{
2602 l_stop_cnt := l_stop_cnt + 1;
2603 l_stop_id_tbl(l_stop_cnt) := next_ib_stops_rec.stop_id;
2604 --}
2605 END LOOP;
2606 --}
2607 END IF;
2608 --
2609 --
2610 IF l_stop_opened = 'Y'
2611 THEN
2612 --{
2613 EXIT;
2614 --}
2615 END IF;
2616 --}
2617 END LOOP;
2618 --
2619 --
2620 IF l_stop_opened = 'N'
2621 THEN
2622 --{
2623 IF l_trip_status_code = 'CL'
2624 THEN
2625 --{
2626 --
2627 -- Trip is Closed, call WSH_TRIP_VALIDATIONS.check_close which will indicate
2628 -- if trip can be(or remain) closed or not.
2629 --
2630 l_trip_in_rec.trip_id := p_in_rec1.trip_id;
2631 l_trip_in_rec.name := p_in_rec1.trip_name;
2632 l_trip_in_rec.new_status_code := l_trip_status_code; --p_in_rec1.trip_new_status_code;
2633 l_trip_in_rec.put_messages := FALSE; --p_in_rec.put_messages;
2634 l_trip_in_rec.actual_date := p_in_rec.actual_date;
2635 l_trip_in_rec.manual_flag := 'N';
2636 l_trip_in_rec.caller := p_in_rec.caller;
2637 l_trip_in_rec.stop_id := NULL; --p_in_Rec.stop_id;
2638 --
2639 --
2640 IF l_debug_on THEN
2641 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_VALIDATIONS.check_close',WSH_DEBUG_SV.C_PROC_LEVEL);
2642 END IF;
2643 --
2644 WSH_TRIP_VALIDATIONS.check_close
2645 (
2646 p_in_rec => l_trip_in_rec,
2647 x_return_status => l_return_status,
2648 x_allowed => l_Allowed
2649 );
2650 --
2651 IF l_debug_on THEN
2652 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2653 WSH_DEBUG_SV.log(l_module_name,'l_Allowed',l_Allowed);
2654 END IF;
2655 --
2656 wsh_util_core.api_post_call
2657 (
2658 p_return_status => l_return_status,
2659 x_num_warnings => l_num_warnings,
2660 x_num_errors => l_num_errors
2661 );
2662 --
2663 --IF l_allowed <> 'Y'
2664 IF l_allowed NOT IN ( 'Y','YW')
2665 THEN
2666 l_trip_status_code := 'IT';
2667 END IF;
2668 --}
2669 END IF;
2670 --
2671 --
2672 IF l_trip_status_code = 'IT'
2673 THEN
2674 --{
2675
2676 --
2677 -- Trip is in-transit, call WSH_TRIP_VALIDATIONS.check_inTransit which will indicate
2678 -- if trip can be(or remain) in-transit or not.
2679 --
2680 l_trip_in_rec.trip_id := p_in_rec1.trip_id;
2681 l_trip_in_rec.name := p_in_rec1.trip_name;
2682 l_trip_in_rec.new_status_code := l_trip_status_code; --p_in_rec1.trip_new_status_code;
2683 l_trip_in_rec.put_messages := FALSE; --p_in_rec.put_messages;
2684 l_trip_in_rec.actual_date := p_in_rec.actual_date;
2685 l_trip_in_rec.manual_flag := 'N';
2686 l_trip_in_rec.caller := p_in_rec.caller;
2687 l_trip_in_rec.stop_id := NULL; --p_in_Rec.stop_id;
2688 --
2689 --
2690 IF l_debug_on THEN
2691 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_VALIDATIONS.check_inTransit',WSH_DEBUG_SV.C_PROC_LEVEL);
2692 END IF;
2693 --
2694 WSH_TRIP_VALIDATIONS.check_inTransit
2695 (
2696 p_in_rec => l_trip_in_rec,
2697 x_return_status => l_return_status,
2698 x_allowed => l_Allowed
2699 );
2700 --
2701 IF l_debug_on THEN
2702 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2703 WSH_DEBUG_SV.log(l_module_name,'l_Allowed',l_Allowed);
2704 END IF;
2705 --
2706 wsh_util_core.api_post_call
2707 (
2708 p_return_status => l_return_status,
2709 x_num_warnings => l_num_warnings,
2710 x_num_errors => l_num_errors
2711 );
2712 --
2713 --IF l_allowed <> 'Y'
2714 IF l_allowed NOT IN ( 'Y','YW')
2715 THEN
2716 l_trip_status_code := 'OP';
2717 END IF;
2718 --}
2719 END IF;
2720 --
2721 IF l_debug_on THEN
2722 WSH_DEBUG_SV.log(l_module_name,'l_trip_status_code',l_trip_status_code);
2723 END IF;
2724 --
2725 --
2726 IF p_in_rec1.trip_status_code <> l_trip_status_code
2727 THEN
2728 --{
2729
2730 l_trip_in_rec.trip_id := p_in_rec1.trip_id;
2731 l_trip_in_rec.name := p_in_rec1.trip_name;
2732 l_trip_in_rec.new_status_code := l_trip_status_code;
2733 l_trip_in_rec.put_messages := TRUE; --p_in_rec.put_messages;
2734 l_trip_in_rec.actual_date := p_in_rec.actual_date;
2735 l_trip_in_rec.manual_flag := 'N';
2736 l_trip_in_rec.caller := p_in_rec.caller;
2737 l_trip_in_rec.stop_id := p_in_Rec.stop_id;
2738 --
2739 --
2740 IF l_debug_on THEN
2741 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_ACTIONS.CHANGESTATUS',WSH_DEBUG_SV.C_PROC_LEVEL);
2742 END IF;
2743 --
2744 wsh_trips_actions.changeStatus
2745 (
2746 p_in_rec => l_trip_in_rec,
2747 x_return_status => l_return_status
2748 );
2749 --
2750 wsh_util_core.api_post_call
2751 (
2752 p_return_status => l_return_status,
2753 x_num_warnings => l_num_warnings,
2754 x_num_errors => l_num_errors
2755 );
2756 --}
2757 END IF;
2758 --}
2759 END IF;
2760 --
2761 --
2762 --
2763
2764 l_index := l_stop_id_tbl.FIRST;
2765 l_stop_opened := 'N';
2766 --
2767 --
2768 WHILE l_index IS NOT NULL
2769 LOOP
2770 --{
2771 FOR ib_dlvy_rec IN ib_dlvy_csr(l_stop_id_tbl(l_index))
2772 LOOP
2773 --{
2774 l_stop_locationId := ib_dlvy_rec.stop_location_Id;
2775 l_stop_Id := l_stop_id_tbl(l_index);
2776 l_stop_opened := 'N';
2777 --
2778 WHILE l_stop_locationId <> ib_dlvy_rec.ultimate_dropoff_location_id
2779 AND l_stop_opened = 'N'
2780 LOOP
2781 --{
2782 FOR next_leg_rec IN next_leg_csr(l_stop_id, ib_dlvy_rec.delivery_id)
2783 LOOP
2784 --{
2785
2786 --
2787 IF next_leg_rec.pu_stop_shipTypeFlag = 'I'
2788 AND next_leg_rec.pu_stop_statusCode = 'CL'
2789 THEN
2790 --{
2791 l_stop_in_rec.stop_id := next_leg_rec.pu_stop_id;
2792 l_stop_in_rec.put_messages := FALSE;
2793 l_stop_in_rec.caller := p_in_rec.caller;
2794 l_stop_in_rec.actual_date := p_in_rec.actual_date;
2795 --
2796 --
2797 IF l_debug_on THEN
2798 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_ACTIONS.autoCLOSE',WSH_DEBUG_SV.C_PROC_LEVEL);
2799 END IF;
2800 --
2801 WSH_TRIP_STOPS_ACTIONS.autoCloseOpen
2802 (
2803 p_in_rec => l_stop_in_rec,
2804 p_reopenStop => TRUE,
2805 x_stop_processed => l_stop_opened,
2806 x_return_status => l_return_status
2807 );
2808 --
2809 WSH_UTIL_CORE.api_post_call
2810 (
2811 p_return_status => l_return_status,
2812 x_num_warnings => l_num_warnings,
2813 x_num_errors => l_num_errors
2814 );
2815 --}
2816 END IF;
2817 --
2818 IF l_stop_opened = 'N'
2819 AND next_leg_rec.do_stop_shipTypeFlag = 'I'
2820 AND next_leg_rec.do_stop_statusCode = 'CL'
2821 THEN
2822 --{
2823 l_stop_opened := 'N';
2824 --
2825 l_stop_in_rec.stop_id := next_leg_rec.do_stop_id;
2826 l_stop_in_rec.put_messages := FALSE;
2827 l_stop_in_rec.caller := p_in_rec.caller;
2828 l_stop_in_rec.actual_date := p_in_rec.actual_date;
2829 --
2830 --
2831 IF l_debug_on THEN
2832 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_ACTIONS.autoCLOSE',WSH_DEBUG_SV.C_PROC_LEVEL);
2833 END IF;
2834 --
2835 WSH_TRIP_STOPS_ACTIONS.autoCloseOpen
2836 (
2837 p_in_rec => l_stop_in_rec,
2838 p_reopenStop => TRUE,
2839 x_stop_processed => l_stop_opened,
2840 x_return_status => l_return_status
2841 );
2842 --
2843 WSH_UTIL_CORE.api_post_call
2844 (
2845 p_return_status => l_return_status,
2846 x_num_warnings => l_num_warnings,
2847 x_num_errors => l_num_errors
2848 );
2849 --}
2850 END IF;
2851 --
2852 --
2853 l_stop_locationId := next_leg_rec.do_stop_locationId;
2854 l_stop_Id := next_leg_rec.do_stop_id;
2855 --}
2856 END LOOP; -- next leg
2857 --}
2858 END LOOP;
2859 --}
2860 END LOOP; -- dlvy
2861 --
2862 l_index := l_stop_id_tbl.NEXT(l_index);
2863 --}
2864 END LOOP;
2865 --
2866 --
2867 --
2868 --
2869 IF l_num_errors > 0
2870 THEN
2871 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2872 ELSE
2873 --{
2874 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_OPEN_MESSAGE');
2875 FND_MESSAGE.SET_TOKEN('STOP_NAME',p_in_rec1.stop_name);
2876 FND_MESSAGE.SET_TOKEN('TRIP_NAME',p_in_rec1.trip_name);
2877 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_SUCCESS,l_module_name);
2878 --
2879 IF l_num_warnings > 0
2880 THEN
2881 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2882 ELSE
2883 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2884 END IF;
2885 --}
2886 END IF;
2887 --
2888 --
2889 --
2890 -- Debug Statements
2891 --
2892 IF l_debug_on THEN
2893 WSH_DEBUG_SV.pop(l_module_name);
2894 END IF;
2895 --
2896 --}
2897 EXCEPTION
2898 --{
2899 --
2900 WHEN FND_API.G_EXC_ERROR THEN
2901
2902 --ROLLBACK TO open_stop_begin_sp;
2903 --
2904 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2905 --
2906 IF l_debug_on THEN
2907 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2908 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2909 END IF;
2910 --
2911 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2912
2913 --ROLLBACK TO open_stop_begin_sp;
2914 --
2915 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2916 --
2917 IF l_debug_on THEN
2918 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2919 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2920 END IF;
2921 --
2922 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
2923 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2924 --
2925 IF l_debug_on THEN
2926 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2927 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2928 END IF;
2929 WHEN others THEN
2930 wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.setOpen',l_module_name);
2931 --
2932 --ROLLBACK TO open_stop_begin_sp;
2933 --
2934 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2935 --
2936 -- Debug Statements
2937 --
2938 IF l_debug_on THEN
2939 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2940 END IF;
2941 --
2942 --}
2943 END setOpen;
2944 --
2945 --
2946 -- J-IB-NPARIKH-}
2947 --
2948 --
2949
2950 PROCEDURE RESET_STOP_SEQ_NUMBERS
2951 ( p_stop_details_rec IN OUT NOCOPY WSH_TRIP_STOPS_VALIDATIONS.stop_details,
2952 x_return_status OUT NOCOPY VARCHAR2
2953 ) IS
2954
2955
2956 CURSOR get_max_closed_seq (c_trip_id number) is
2957 SELECT max(stop_sequence_number)
2958 FROM wsh_trip_stops
2959 WHERE trip_id = c_trip_id
2960 AND status_code in ('AR', 'CL');
2961 -- Bug 3814592
2962 -- look for open stops higher than closed/arrived stops
2963 -- Mixed trip can have open stops which have lower sequence number
2964 -- than closed/arrived stops
2965 CURSOR get_all_open_stops( c_trip_id number,
2966 c_sequence_number NUMBER) is
2967 SELECT planned_arrival_date,
2968 stop_id,
2969 physical_stop_id -- SSN change
2970 FROM wsh_trip_stops
2971 WHERE trip_id = c_trip_id
2972 and status_code = 'OP'
2973 and (stop_sequence_number > c_sequence_number -- Update existing stops
2974 OR stop_sequence_number < 1) -- Newly created stops
2975 FOR update nowait
2976 ORDER BY 1, 2;
2977
2978 l_stop_rec_tab WSH_TRIP_STOPS_VALIDATIONS.stop_details_tab;
2979 l_max_seq_number NUMBER;
2980 l_trip_id_tmp NUMBER;
2981 l_locking_stops VARCHAR2(1);
2982 i NUMBER;
2983 l_trip_id_tab wsh_util_core.id_tab_type;
2984
2985 --SSN Change, new variables/constants
2986 l_normal_seq_offset number := 10;
2987 l_seq_offset number;
2988 --
2989 l_debug_on BOOLEAN;
2990 --
2991 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'RESET_STOP_SEQ_NUMBERS';
2992 RECORD_LOCKED EXCEPTION;
2993 PRAGMA EXCEPTION_INIT(RECORD_LOCKED, -00054);
2994
2995 BEGIN
2996
2997 SAVEPOINT begin_reset_stop_seq;
2998 --
2999 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3000 --
3001 IF l_debug_on IS NULL THEN
3002 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3003 END IF;
3004 --
3005 IF l_debug_on THEN
3006 WSH_DEBUG_SV.push(l_module_name);
3007 WSH_DEBUG_SV.log(l_module_name,'Stop id ', p_stop_details_rec.stop_id);
3008 WSH_DEBUG_SV.log(l_module_name,'Trip id ', p_stop_details_rec.trip_id);
3009 WSH_DEBUG_SV.log(l_module_name,'Stop sequence number ', p_stop_details_rec.stop_sequence_number);
3010 END IF;
3011 --
3012 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3013 l_locking_stops := 'N';
3014
3015 -- lock trip p_trip_id;
3016 WSH_TRIPS_PVT.lock_trip_no_compare(p_stop_details_rec.trip_id);
3017
3018 l_locking_stops := 'Y';
3019
3020 open get_max_closed_seq( p_stop_details_rec.trip_id);
3021 fetch get_max_closed_seq into l_max_seq_number;
3022
3023 close get_max_closed_seq;
3024
3025 IF l_max_seq_number is NULL THEN
3026 l_max_seq_number := 0;
3027 END IF;
3028
3029 IF l_debug_on THEN
3030 WSH_DEBUG_SV.log(l_module_name,'maximum sequence number from cursor get_max_closed_seq',l_max_seq_number );
3031 END IF;
3032
3033 l_stop_rec_tab.delete;
3034
3035 -- If the trip stop were created by patchset 11.5.7 or older, the trip stop sequence number should
3036 -- always be -99 regardless of the planned_arrival_date and status_code.
3037 -- If the trip stops were created by patchset 11.5.8 or 11.5.9, the trip stop sequence numbers
3038 -- could be any number entered by users, but users must close the trip stops in order.
3039 -- The code is based on the assumption that the sequence number of a open stop is always geater
3040 -- than or equal to the sequence number of any closed or arrived trip stop.
3041
3042 -- Bug 3814592, add l_max_seq_number to the cursor where clause
3043 -- renumber only open stops which have higher sequence number than
3044 -- closed/arrived stops on trip
3045
3046 open get_all_open_stops(p_stop_details_rec.trip_id,l_max_seq_number);
3047 loop
3048 i := l_stop_rec_tab.count+1;
3049 -- SSN change, fetch physical_stop_id
3050 fetch get_all_open_stops
3051 into l_stop_rec_tab(i).planned_arrival_date,
3052 l_stop_rec_tab(i).stop_id,l_stop_rec_tab(i).physical_stop_id ;
3053 exit when get_all_open_stops%NOTFOUND;
3054 end loop;
3055
3056 -- SSN Change
3057 l_seq_offset := l_normal_seq_offset;
3058
3059 i := l_stop_rec_tab.first;
3060 WHILE i is not NULL LOOP
3061 IF l_stop_rec_tab(i).stop_id is not NULL THEN
3062 -- SSN change
3063 -- Logic to offset linked dummy stop by -1
3064 IF l_stop_rec_tab(i).physical_stop_id is NOT NULL THEN
3065 -- to retain -1 offset with next physical stop
3066 l_max_seq_number := l_max_seq_number + l_normal_seq_offset - 1;
3067 l_seq_offset := 1; -- next physical stop will be +1 after this linked dummy stop.
3068 ELSE
3069 l_max_seq_number := l_max_seq_number + l_seq_offset;
3070 l_seq_offset := l_normal_seq_offset; -- always reset for next normal stop.
3071 END IF;
3072
3073 update wsh_trip_stops set stop_sequence_number = l_max_seq_number
3074 where stop_id = l_stop_rec_tab(i).stop_id;
3075
3076 IF l_debug_on THEN
3077 WSH_DEBUG_SV.logmsg(l_module_name,'updated stop '||l_stop_rec_tab(i).stop_id||' with sequence number '||to_char(l_max_seq_number));
3078 END IF;
3079 IF p_stop_details_rec.stop_id = l_stop_rec_tab(i).stop_id THEN
3080 p_stop_details_rec.stop_sequence_number := l_max_seq_number;
3081 END IF;
3082 i := l_stop_rec_tab.next(i);
3083 ELSE
3084 exit;
3085 END IF;
3086 END LOOP;
3087
3088 IF get_all_open_stops%ISOPEN THEN
3089 close get_all_open_stops;
3090 END IF;
3091
3092 -- Need to compute all stops weight/volumes since the sequence would have changed
3093 IF l_debug_on THEN
3094 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_ACTIONS.trip_weight_volume',WSH_DEBUG_SV.C_PROC_LEVEL);
3095 END IF;
3096
3097 l_trip_id_tab(1) := p_stop_details_rec.trip_id;
3098 WSH_TRIPS_ACTIONS.trip_weight_volume(
3099 p_trip_rows => l_trip_id_tab,
3100 p_override_flag => 'Y',
3101 p_calc_wv_if_frozen => 'N',
3102 p_start_departure_date => to_date(NULL),
3103 p_calc_del_wv => 'N',
3104 x_return_status => x_return_status,
3105 p_suppress_errors => 'Y');
3106
3107 IF x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3108 IF l_debug_on THEN
3109 WSH_DEBUG_SV.logmsg(l_module_name,'Error calculating trip wt/vol');
3110 END IF;
3111 END IF;
3112
3113 IF l_debug_on THEN
3114 WSH_DEBUG_SV.pop(l_module_name);
3115 END IF;
3116
3117 EXCEPTION
3118
3119 WHEN RECORD_LOCKED
3120 OR app_exception.application_exception
3121 OR app_exception.record_lock_exception THEN
3122 ROLLBACK TO begin_reset_stop_seq;
3123 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3124 IF get_all_open_stops%ISOPEN THEN
3125 close get_all_open_stops;
3126 END IF;
3127
3128 IF l_locking_stops = 'N' THEN
3129 FND_MESSAGE.SET_NAME('WSH', 'WSH_TRIP_LOCK_FAILED');
3130 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',to_char(l_trip_id_tmp));
3131 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
3132 IF l_debug_on THEN
3133 WSH_DEBUG_SV.log(l_module_name, 'ERROR: Failed to lock trip ID '||to_char(l_trip_id_tmp));
3134 WSH_DEBUG_SV.POP(l_module_name, 'EXCEPTION:RECORD_LOCKED');
3135 END IF;
3136 ELSE
3137 FND_MESSAGE.SET_NAME('WSH', 'WSH_NO_LOCK');
3138 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
3139 IF l_debug_on THEN
3140 WSH_DEBUG_SV.log(l_module_name, 'ERROR: Failed to lock trip stops belong to trip l_trip_id_tmp' );
3141 WSH_DEBUG_SV.POP(l_module_name, 'EXCEPTION:RECORD_LOCKED');
3142 END IF;
3143 END IF;
3144
3145 WHEN OTHERS THEN
3146
3147 wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.reset_stop_sequence_numbers',l_module_name);
3148 IF get_all_open_stops%ISOPEN THEN
3149 close get_all_open_stops;
3150 END IF;
3151 ROLLBACK TO begin_reset_stop_seq;
3152 IF get_all_open_stops%ISOPEN THEN
3153 close get_all_open_stops;
3154 END IF;
3155
3156 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3157 --
3158 -- Debug Statements
3159 --
3160 IF l_debug_on THEN
3161 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3162 END IF;
3163
3164 END RESET_STOP_SEQ_NUMBERS;
3165
3166
3167 -- OTM R12, glog proj
3168 ---------------------------------------------------------------------------
3169 -- PROCEDURE LAST_PICKUP_STOP_CLOSED
3170 --
3171 -- parameters: p_trip_id -> the trip id to check for
3172 -- p_stop_id -> the stop id to check for to skip because
3173 -- we will set this stop to close since this
3174 -- is called only in setClose procedure
3175 -- x_last_pickup_stop_closed -> Returns 'Y' if its the stop
3176 -- closed is the last pickup stop.
3177 -- x_eligible_for_asr --> Returns 'Y' is the trip information
3178 -- is eligible to be send to OTM.
3179 -- x_return_status --> Returns status of this procedure call.
3180 -- Assumption : p_trip_id and p_stop_id as passed by the calling API
3181 ---------------------------------------------------------------------------
3182
3183 PROCEDURE LAST_PICKUP_STOP_CLOSED
3184 (p_trip_id IN WSH_TRIP_STOPS.TRIP_ID%TYPE,
3185 p_stop_id IN WSH_TRIP_STOPS.STOP_ID%TYPE,
3186 x_last_pickup_stop_closed OUT NOCOPY VARCHAR2,
3187 x_eligible_for_asr OUT NOCOPY VARCHAR2,
3188 x_return_status OUT NOCOPY VARCHAR2) IS
3189
3190 --OTM R12 Org-Specific. (Reviewed by perf team)
3191 CURSOR pickup_stop_csr IS
3192 SELECT stop_id
3193 FROM ( SELECT wts.stop_id
3194 FROM wsh_trip_stops wts
3195 WHERE wts.trip_id = p_trip_id
3196 AND wts.status_code IN ( 'OP', 'AR')
3197 AND EXISTS ( SELECT 'c'
3198 FROM wsh_delivery_legs wdl
3199 WHERE wdl.pick_up_stop_id = wts.stop_id)
3200 ORDER BY wts.stop_sequence_number DESC)
3201 WHERE ROWNUM = 1;
3202
3203 -- LSP PROJECT : checking OTM enabled flag on client parameters.
3204 --OTM R12 Org-Specific (Reviewed by perf team)
3205 CURSOR check_eligible_for_asr_csr IS
3206 SELECT 1
3207 FROM wsh_trip_stops wts
3208 ,wsh_delivery_legs wdl
3209 ,wsh_new_deliveries wnd
3210 WHERE wts.trip_id = p_trip_id
3211 AND wts.stop_id = wdl.pick_up_stop_id
3212 AND wdl.delivery_id = wnd.delivery_id
3213 AND EXISTS ( SELECT 1
3214 FROM wsh_shipping_parameters wsp
3215 WHERE wnd.organization_id = wsp.organization_id
3216 AND wsp.otm_enabled = 'Y' AND wnd.client_ID IS NULL
3217 UNION ALL
3218 SELECT 1
3219 FROM mtl_client_parameters_v mcp
3220 WHERE wnd.client_id = mcp.client_id
3221 AND mcp.otm_enabled = 'Y' AND wnd.client_ID IS NOT NULL )
3222 AND NOT EXISTS ( SELECT 1
3223 FROM wsh_trip_stops wts
3224 ,wsh_delivery_legs wdl
3225 ,wsh_new_deliveries wnd
3226 WHERE wts.trip_id = p_trip_id
3227 AND wts.stop_id = wdl.pick_up_stop_id
3228 AND wdl.delivery_id = wnd.delivery_id
3229 AND ( EXISTS ( SELECT 1
3230 FROM mtl_parameters mtlp
3231 WHERE mtlp.organization_id = wnd.organization_id
3232 AND mtlp.distributed_organization_flag = 'Y')
3233 OR wnd.delivery_type = 'CONSOLIDATION'))
3234 AND ROWNUM = 1;
3235 --OTM R12
3236
3237 --OTM R12 Org-Specific start.
3238 l_stop_id wsh_trip_stops.stop_id%TYPE;
3239 l_id NUMBER;
3240 --OTM R12 End ;
3241
3242 l_debug_on BOOLEAN;
3243 --
3244 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || g_pkg_name || '.' || 'LAST_PICKUP_STOP_CLOSED';
3245 --
3246 BEGIN
3247
3248 l_debug_on := wsh_debug_interface.g_debug;
3249 --
3250 IF l_debug_on IS NULL THEN
3251 l_debug_on := wsh_debug_sv.is_debug_enabled;
3252 END IF;
3253 --
3254 IF l_debug_on THEN
3255 wsh_debug_sv.push(l_module_name);
3256 wsh_debug_sv.LOG(l_module_name, 'p_trip_id', p_trip_id);
3257 wsh_debug_sv.LOG(l_module_name, 'p_stop_id', p_stop_id);
3258 END IF;
3259
3260 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3261 x_last_pickup_stop_closed := 'N';
3262
3263 -- This API is not exposed to end user, so no message needed here
3264 -- the developers need to know the mandatory parameters
3265 IF p_trip_id IS NULL OR p_stop_id IS NULL THEN
3266 IF l_debug_on THEN
3267 WSH_DEBUG_SV.logmsg(l_module_name,
3268 'Please specify p_trip_id and p_stop_id');
3269 wsh_debug_sv.pop(l_module_name, 'ERROR EXCEPTION');
3270 END IF;
3271 RAISE FND_API.G_EXC_ERROR;
3272 END IF;
3273
3274 --OTM R12 Org-Specific start.
3275 OPEN pickup_stop_csr;
3276 FETCH pickup_stop_csr INTO l_stop_id;
3277 CLOSE pickup_stop_csr;
3278
3279 IF l_debug_on THEN
3280 WSH_DEBUG_SV.log(l_module_name,'l_stop_id', l_stop_id );
3281 END IF;
3282
3283 IF l_stop_id = p_stop_id THEN --{
3284 x_last_pickup_stop_closed := 'Y';
3285 ELSE
3286 x_last_pickup_stop_closed := 'N';
3287 END IF; --}
3288
3289 IF l_debug_on THEN
3290 WSH_DEBUG_SV.log(l_module_name,'x_last_pickup_stop_closed',
3291 x_last_pickup_stop_closed );
3292 END IF;
3293
3294 --If last pickup stop closed then check whether any of the delivery orgs
3295 --in the trip is TPW. If any orgs are TPW then dont send trip info to OTM.
3296 --If the delivery is part of MDC then don't sent trip info to OTM.
3297 --If warehouse is not TPW and included delivery not part of MDC then
3298 --check whether any of the deliveries in the trip belong to OTM enabed
3299 --orgs. If so set the flag to send trip info to OTM.
3300 IF x_last_pickup_stop_closed = 'Y' THEN --{
3301 OPEN check_eligible_for_asr_csr;
3302 FETCH check_eligible_for_asr_csr INTO l_id ;
3303 IF check_eligible_for_asr_csr%FOUND THEN
3304 x_eligible_for_asr := 'Y';
3305 ELSE
3306 x_eligible_for_asr := 'N';
3307 END IF;
3308 CLOSE check_eligible_for_asr_csr;
3309 END IF; --}
3310 IF x_eligible_for_asr IS NULL THEN
3311 x_eligible_for_asr := 'N';
3312 END IF;
3313 --OTM R12 End
3314
3315 IF l_debug_on THEN
3316 wsh_debug_sv.LOG(l_module_name, 'x_eligible_for_asr', x_eligible_for_asr);
3317 wsh_debug_sv.LOG(l_module_name, 'x_last_pickup_stop_closed',
3318 x_last_pickup_stop_closed);
3319 wsh_debug_sv.LOG(l_module_name, 'x_return_status', x_return_status);
3320 wsh_debug_sv.pop(l_module_name);
3321 END IF;
3322
3323 EXCEPTION
3324 --OTM R12 Org-Specific start.
3325 WHEN FND_API.G_EXC_ERROR THEN
3326 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3327 IF pickup_stop_csr%ISOPEN THEN
3328 CLOSE pickup_stop_csr;
3329 END IF;
3330 IF check_eligible_for_asr_csr%ISOPEN THEN
3331 CLOSE check_eligible_for_asr_csr;
3332 END IF;
3333
3334 IF l_debug_on THEN
3335 WSH_DEBUG_SV.log(l_module_name,
3336 'Oracle error '||SQLERRM,
3337 WSH_DEBUG_SV.C_EXCEP_LEVEL);
3338 wsh_debug_sv.pop(l_module_name, 'FND_API.G_EXC_ERROR');
3339 END IF;
3340
3341 --OTM R12 End
3342 WHEN OTHERS THEN
3343 IF pickup_stop_csr%ISOPEN THEN
3344 CLOSE pickup_stop_csr;
3345 END IF;
3346 IF check_eligible_for_asr_csr%ISOPEN THEN
3347 CLOSE check_eligible_for_asr_csr;
3348 END IF;
3349
3350 wsh_util_core.default_handler('wsh_trip_stops_actions.last_pickup_stop_closed',
3351 l_module_name);
3352 --
3353 IF l_debug_on THEN
3354 WSH_DEBUG_SV.log(l_module_name,
3355 'Unexpected Error has Occured.Oracle error message is'||SQLERRM,
3356 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3357 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:OTHERS');
3358 END IF;
3359
3360 END LAST_PICKUP_STOP_CLOSED;
3361
3362 END WSH_TRIP_STOPS_ACTIONS;