[Home] [Help]
PACKAGE BODY: APPS.FTE_TP_GRP
Source
4 G_TP_RELEASE_CODE CONSTANT VARCHAR2(30) := WSH_TP_RELEASE_GRP.G_TP_RELEASE_CODE;
1 PACKAGE BODY FTE_TP_GRP as
2 /* $Header: FTETPGPB.pls 115.10 2004/05/08 00:25:40 sperera noship $ */
3
5
6 --
7 G_PKG_NAME CONSTANT VARCHAR2(50) := 'FTE_TP_GRP';
8 --
9
10
11 CURSOR c_cm_info(x_trip_id NUMBER) IS
12 SELECT fm.move_id,
13 fm.cm_trip_number,
14 fm.planned_flag,
15 ftm.sequence_number
16 FROM FTE_MOVES fm,
17 FTE_TRIP_MOVES ftm
18 WHERE ftm.trip_id = x_trip_id
19 AND fm.move_id = ftm.move_id
20 AND fm.move_type_code = 'CONTINUOUS';
21
22
23 --
24 -- Procedure: int_lookup_cm_info
25 -- Parameters:
26 -- p_trip_id trip_id to look up its continuous move segment
27 -- x_int_cm_info_rec internal attributes of continuous move and segment
28 -- x_return_status return status
29 --
30 -- Description:
31 -- Internal API to look up continuous move information associated
32 -- with the trip for the callers.
33 --
34 --
35
36 PROCEDURE int_lookup_cm_info (
37 p_trip_id IN NUMBER,
38 x_int_cm_info_rec OUT NOCOPY c_cm_info%ROWTYPE,
39 x_return_status OUT NOCOPY VARCHAR2)
40 IS
41 --
42 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'int_lookup_cm_info';
43 --
44 l_debug_on BOOLEAN;
45 --
46
47 BEGIN
48 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
49 --
50 IF l_debug_on IS NULL THEN
51 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
52 END IF;
53
54 IF l_debug_on THEN
55 WSH_DEBUG_SV.push(l_module_name);
56 WSH_DEBUG_SV.log(l_module_name, 'p_trip_id', p_trip_id);
57 END IF;
58
59 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
60
61 --
62 --
63
64 OPEN c_cm_info(p_trip_id);
65 FETCH c_cm_info INTO x_int_cm_info_rec;
66 IF c_cm_info%NOTFOUND THEN
67 x_int_cm_info_rec.move_id := NULL;
68 x_int_cm_info_rec.cm_trip_number := NULL;
69 x_int_cm_info_rec.sequence_number := NULL;
73 --
70 END IF;
71 CLOSE c_cm_info;
72
74 --
75
76 IF l_debug_on THEN
77 WSH_DEBUG_SV.log(l_module_name, 'move_id', x_int_cm_info_rec.move_id);
78 WSH_DEBUG_SV.log(l_module_name, 'cm_trip_number', x_int_cm_info_rec.cm_trip_number);
79 WSH_DEBUG_SV.log(l_module_name, 'sequence_number', x_int_cm_info_rec.sequence_number);
80 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
81 WSH_DEBUG_SV.pop(l_module_name);
82 END IF;
83
84 EXCEPTION
85 WHEN OTHERS THEN
86 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
87 WSH_UTIL_CORE.DEFAULT_HANDLER(
88 'FTE_TP_GRP.int_lookup_cm_info',
89 l_module_name);
90 IF c_cm_info%ISOPEN THEN
91 CLOSE c_cm_info;
92 END IF;
93 IF l_debug_on THEN
94 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
95 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
96 END IF;
97
98 END int_lookup_cm_info;
99
100
101
102 --
103 -- Procedure: lookup_move
104 -- Parameters:
105 -- p_move_id move identifier
106 -- p_lock_flag 'Y' - lock move and its trip_moves; 'N' - do not lock
107 -- x_move_rec record from FTE_MOVES
108 -- x_locked 'Y' - locked by other session; 'N' - no exception raised
109 -- x_return_status return status
110 --
111 -- Description:
112 -- Internal API to lock and populate the move record.
113 --
114 --
115
116 PROCEDURE lookup_move (
117 p_move_id IN NUMBER,
118 p_lock_flag IN VARCHAR2,
119 x_move_rec OUT NOCOPY FTE_MOVES_PVT.move_rec_type,
120 x_locked OUT NOCOPY VARCHAR2,
121 x_return_status OUT NOCOPY VARCHAR2)
122 IS
123 --
124 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'lookup_move';
125 --
126 l_debug_on BOOLEAN;
127 --
128 CURSOR c_move(x_move_id NUMBER) IS
129 SELECT fm.move_id,
130 fm.move_type_code,
131 fm.lane_id,
132 fm.service_level,
133 fm.planned_flag,
134 fm.cm_trip_number,
135 fm.tp_plan_name,
136 fm.creation_date,
137 fm.created_by,
138 fm.last_update_date,
139 fm.last_updated_by,
140 fm.last_update_login,
141 fm.program_application_id,
142 fm.program_id,
143 fm.program_update_date,
144 fm.request_id,
145 fm.attribute_category,
146 fm.attribute1,
147 fm.attribute2,
148 fm.attribute3,
149 fm.attribute4,
150 fm.attribute5,
151 fm.attribute6,
152 fm.attribute7,
153 fm.attribute8,
154 fm.attribute9,
155 fm.attribute10,
156 fm.attribute11,
157 fm.attribute12,
158 fm.attribute13,
159 fm.attribute14,
160 fm.attribute15
161 FROM FTE_MOVES fm
162 WHERE fm.move_id = x_move_id;
163
164 CURSOR c_lock_move(x_move_id NUMBER) IS
165 SELECT fm.move_id,
166 fm.move_type_code,
167 fm.lane_id,
168 fm.service_level,
169 fm.planned_flag,
170 fm.cm_trip_number,
171 fm.tp_plan_name,
172 fm.creation_date,
173 fm.created_by,
174 fm.last_update_date,
175 fm.last_updated_by,
176 fm.last_update_login,
177 fm.program_application_id,
178 fm.program_id,
179 fm.program_update_date,
180 fm.request_id,
181 fm.attribute_category,
182 fm.attribute1,
183 fm.attribute2,
184 fm.attribute3,
185 fm.attribute4,
186 fm.attribute5,
187 fm.attribute6,
188 fm.attribute7,
189 fm.attribute8,
190 fm.attribute9,
191 fm.attribute10,
192 fm.attribute11,
193 fm.attribute12,
194 fm.attribute13,
195 fm.attribute14,
196 fm.attribute15
197 FROM FTE_MOVES fm
198 WHERE fm.move_id = x_move_id
199 FOR UPDATE NOWAIT;
200
201 CURSOR c_lock_trip_moves(x_move_id NUMBER) IS
202 SELECT ftm.trip_move_id
203 FROM FTE_TRIP_MOVES ftm
204 WHERE ftm.move_id = x_move_id
205 FOR UPDATE NOWAIT;
206
207 BEGIN
208 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
209 --
210 IF l_debug_on IS NULL THEN
211 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
212 END IF;
213
214 IF l_debug_on THEN
215 WSH_DEBUG_SV.push(l_module_name);
216 WSH_DEBUG_SV.log(l_module_name, 'p_move_id', p_move_id);
217 END IF;
218
219 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
220 x_locked := 'N';
221
222 --
223 --
224
225 IF p_lock_flag = 'Y' THEN
226 OPEN c_lock_move(p_move_id);
227 FETCH c_lock_move INTO x_move_rec;
228 IF c_lock_move%NOTFOUND THEN
229 FND_MESSAGE.SET_NAME('FTE','FTE_MOVE_NOT_FOUND');
230 FND_MESSAGE.SET_TOKEN('MOVE_ID', p_move_id);
231 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
232 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
236 -- need only to lock the trip moves
233 END IF;
234 CLOSE c_lock_move;
235
237 OPEN c_lock_trip_moves(p_move_id);
238 CLOSE c_lock_trip_moves;
239
240 ELSE
241 OPEN c_move(p_move_id);
242 FETCH c_move INTO x_move_rec;
243 IF c_move%NOTFOUND THEN
244 FND_MESSAGE.SET_NAME('FTE','FTE_MOVE_NOT_FOUND');
245 FND_MESSAGE.SET_TOKEN('MOVE_ID', p_move_id);
246 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
247 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
248 END IF;
249 CLOSE c_move;
250 END IF;
251
252 --
253 --
254
255 IF l_debug_on THEN
256 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
257 WSH_DEBUG_SV.pop(l_module_name);
258 END IF;
259
260 EXCEPTION
261 WHEN app_exception.application_exception or app_exception.record_lock_exception THEN
262 x_locked := 'Y';
263 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
264 FND_MESSAGE.SET_NAME('FTE', 'FTE_MOVE_LOCK');
265 FND_MESSAGE.SET_TOKEN('MOVE_ID', p_move_id);
266 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
267 IF l_debug_on THEN
268 WSH_DEBUG_SV.logmsg(l_module_name,'APP_EXCEPTION.APPLICATION_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
269 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:APP_EXCEPTION.APPLICATION_EXCEPTION');
270 END IF;
271
272 WHEN OTHERS THEN
273 IF c_move%ISOPEN THEN
274 CLOSE c_move;
275 END IF;
276 IF c_lock_move%ISOPEN THEN
277 CLOSE c_lock_move;
278 END IF;
279 IF c_lock_trip_moves%ISOPEN THEN
280 CLOSE c_lock_trip_moves;
281 END IF;
282 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
283 WSH_UTIL_CORE.DEFAULT_HANDLER(
284 'FTE_TP_GRP.lookup_move',
285 l_module_name);
286 IF c_cm_info%ISOPEN THEN
287 CLOSE c_cm_info;
288 END IF;
289 IF l_debug_on THEN
290 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
291 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
292 END IF;
293
294 END lookup_move;
295
296
297
298 --
299 -- Procedure: lookup_cm_info
300 -- Parameters:
301 -- p_trip_id trip_id to look up its continuous move segment
302 -- x_cm_info_rec attributes of continuous move and segment
303 -- x_return_status return status
304 --
305 -- Description:
306 -- Looks up continuous move information associated with the trip
307 -- to be displayed in shipping UIs.
308 --
309 --
310
311 PROCEDURE lookup_cm_info (
312 p_trip_id IN NUMBER,
313 x_cm_info_rec OUT NOCOPY WSH_FTE_TP_INTEGRATION.cm_info_rec_type,
314 x_return_status OUT NOCOPY VARCHAR2)
315 IS
316 --
317 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'lookup_cm_info';
318 --
319 l_debug_on BOOLEAN;
320 --
321 l_int_cm_info_rec c_cm_info%ROWTYPE;
322
323 BEGIN
324 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
325 --
326 IF l_debug_on IS NULL THEN
327 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
328 END IF;
329
330 IF l_debug_on THEN
331 WSH_DEBUG_SV.push(l_module_name);
332 END IF;
333
334 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
335
336 --
337 --
338
339 int_lookup_cm_info(
340 p_trip_id => p_trip_id,
341 x_int_cm_info_rec => l_int_cm_info_rec,
342 x_return_status => x_return_status);
343
344 IF x_return_status IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS,
345 WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
346 x_cm_info_rec.move_id := l_int_cm_info_rec.move_id;
347 x_cm_info_rec.cm_trip_number := l_int_cm_info_rec.cm_trip_number;
348 x_cm_info_rec.sequence_number := l_int_cm_info_rec.sequence_number;
349 ELSE
350 x_cm_info_rec.move_id := NULL;
351 x_cm_info_rec.cm_trip_number := NULL;
352 x_cm_info_rec.sequence_number := NULL;
353 END IF;
354
355 --
356 --
357
358 IF l_debug_on THEN
359 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
360 WSH_DEBUG_SV.pop(l_module_name);
361 END IF;
362
363 EXCEPTION
364 WHEN OTHERS THEN
365 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
366 WSH_UTIL_CORE.DEFAULT_HANDLER(
367 'FTE_TP_GRP.lookup_cm_info',
368 l_module_name);
369 IF c_cm_info%ISOPEN THEN
370 CLOSE c_cm_info;
371 END IF;
372 IF l_debug_on THEN
373 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
374 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
375 END IF;
376
377 END lookup_cm_info;
378
379
380
381 --
382 -- Procedure: trip_callback
383 -- Parameters:
384 -- p_api_version_number known api version (1.0)
385 -- p_init_msg_list FND_API.G_TRUE to reset list
386 -- x_return_status return status
387 -- x_msg_count number of messages in the list
388 -- x_msg_data text of messages
392 -- p_rec_attr_tab table of trip records to process
389 -- p_actions_prms action parameters record
390 -- used to identify the action triggering
391 -- the callback to FTE.
393 --
394 -- Description:
395 -- take care of continuous moves based on the action being
396 -- performed on the trips.
397 --
398
399 PROCEDURE trip_callback (
400 p_api_version_number IN NUMBER,
401 p_init_msg_list IN VARCHAR2,
402 x_return_status OUT NOCOPY VARCHAR2,
403 x_msg_count OUT NOCOPY NUMBER,
404 x_msg_data OUT NOCOPY VARCHAR2,
405 p_action_prms IN WSH_TRIPS_GRP.action_parameters_rectype,
406 p_rec_attr_tab IN WSH_TRIPS_PVT.Trip_Attr_Tbl_Type)
407 IS
408 --
409 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'trip_callback';
410 --
411 l_debug_on BOOLEAN;
412 --
413 i NUMBER;
414 l_return_status VARCHAR2(1);
415 l_int_cm_info_rec c_cm_info%ROWTYPE;
416 l_move_rec FTE_MOVES_PVT.move_rec_type;
417 l_locked VARCHAR2(1);
418 move_error EXCEPTION;
419 BEGIN
420 SAVEPOINT before_trip_callback;
421 --
422 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
423 --
424 IF l_debug_on IS NULL THEN
425 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
426 END IF;
427
428 IF l_debug_on THEN
429 WSH_DEBUG_SV.push(l_module_name);
430 WSH_DEBUG_SV.log(l_module_name, 'action_code', p_action_prms.action_code);
431 WSH_DEBUG_SV.log(l_module_name, 'count of trips', p_rec_attr_tab.COUNT);
432 END IF;
433
434 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
435
436 --
437 --
438
439 IF p_action_prms.action_code IN ('PLAN', 'UNPLAN', 'DELETE') THEN
440
441 i := p_rec_attr_tab.FIRST;
442 WHILE i IS NOT NULL LOOP
443
444 int_lookup_cm_info(
445 p_trip_id => p_rec_attr_tab(i).trip_id,
446 x_int_cm_info_rec => l_int_cm_info_rec,
447 x_return_status => l_return_status);
448
449 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
450 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
451 ROLLBACK TO before_trip_callback;
452 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
453 IF l_debug_on THEN
454 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
455 WSH_DEBUG_SV.pop(l_module_name);
456 END IF;
457 RETURN;
458 END IF;
459
460
461 IF l_int_cm_info_rec.move_id IS NOT NULL THEN
462
463 IF (p_action_prms.action_code IN ('UNPLAN'))
464 AND (l_int_cm_info_rec.planned_flag <> 'N') THEN
465 -- planning or unplanning a trip needs to downgrade its firmed move.
466
467 lookup_move(
468 p_move_id => l_int_cm_info_rec.move_id,
469 p_lock_flag => 'Y',
470 x_move_rec => l_move_rec,
471 x_locked => l_locked,
472 x_return_status => l_return_status);
473
474 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
475 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
476 RAISE move_error;
477 END IF;
478
479 -- unfirm continuous move
480 l_move_rec.planned_flag := 'N';
481 l_move_rec.last_updated_by := FND_GLOBAL.user_id;
482 l_move_rec.last_update_date := SYSDATE;
483
484 fte_moves_pvt.update_move(
485 p_move_info => l_move_rec,
486 x_return_status => l_return_status);
487
488 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
489 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
490 RAISE move_error;
491 END IF;
492
493 ELSIF p_action_prms.action_code = 'DELETE' THEN
494
495 lookup_move(
496 p_move_id => l_int_cm_info_rec.move_id,
497 p_lock_flag => 'Y',
498 x_move_rec => l_move_rec,
499 x_locked => l_locked,
500 x_return_status => l_return_status);
501
502 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
503 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
504 RAISE move_error;
505 END IF;
506
507 fte_moves_pvt.delete_move(
508 p_move_id => l_int_cm_info_rec.move_id,
509 p_validate_flag => 'Y',
510 x_return_status => l_return_status);
511 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
512 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
513 RAISE move_error;
514 END IF;
515
516 END IF;
517 END IF;
518
519 i := p_rec_attr_tab.NEXT(i);
520 END LOOP;
521
522 END IF;
523
524 --
525 --
526
527 IF l_debug_on THEN
528 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
529 WSH_DEBUG_SV.pop(l_module_name);
530 END IF;
531
532 EXCEPTION
533 WHEN move_error THEN
534 ROLLBACK TO before_trip_callback;
538 WSH_DEBUG_SV.pop(l_module_name);
535 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
536 IF l_debug_on THEN
537 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
539 END IF;
540
541 WHEN OTHERS THEN
542 ROLLBACK TO before_trip_callback;
543 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
544 WSH_UTIL_CORE.DEFAULT_HANDLER(
545 'FTE_TP_GRP.trip_callback',
546 l_module_name);
547 IF l_debug_on THEN
548 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
549 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
550 END IF;
551
552 END trip_callback;
553
554
555
556 --
557 -- Procedure: stop_callback
558 -- Parameters:
559 -- p_api_version_number known api version (1.0)
560 -- p_init_msg_list FND_API.G_TRUE to reset list
561 -- x_return_status return status
562 -- x_msg_count number of messages in the list
563 -- x_msg_data text of messages
564 -- p_actions_prms action parameters record
565 -- used to identify the action triggering
566 -- the callback to FTE.
567 -- p_rec_attr_tab table of stop records to process
568 --
569 -- Description:
570 -- take care of continuous moves based on the action being performed
571 -- on the stops.
572 --
573
574 PROCEDURE stop_callback (
575 p_api_version_number IN NUMBER,
576 p_init_msg_list IN VARCHAR2,
577 x_return_status OUT NOCOPY VARCHAR2,
578 x_msg_count OUT NOCOPY NUMBER,
579 x_msg_data OUT NOCOPY VARCHAR2,
580 p_action_prms IN WSH_TRIP_STOPS_GRP.action_parameters_rectype,
581 p_rec_attr_tab IN WSH_TRIP_STOPS_PVT.stop_attr_tbl_type)
582 IS
583 --
584 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'stop_callback';
585 --
586 l_debug_on BOOLEAN;
587 --
588 BEGIN
589 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
590 --
591 IF l_debug_on IS NULL THEN
592 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
593 END IF;
594
595 IF l_debug_on THEN
596 WSH_DEBUG_SV.push(l_module_name);
597 END IF;
598
599 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
600
601 --
602 --
603
604 NULL;
605
606 --
607 --
608
609 IF l_debug_on THEN
610 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
611 WSH_DEBUG_SV.pop(l_module_name);
612 END IF;
613
614 EXCEPTION
615 WHEN OTHERS THEN
616 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
617 WSH_UTIL_CORE.DEFAULT_HANDLER(
618 'FTE_TP_GRP.stop_callback',
619 l_module_name);
620 IF l_debug_on THEN
621 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
622 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
623 END IF;
624
625 END stop_callback;
626
627
628
629 --
630 -- Procedure: map_moves
631 -- Parameters:
632 -- x_context context in this session
633 -- x_plan_trips list of trips mapped to interface trips
634 -- x_plan_trip_moves list of continuous move segments
635 -- x_plan_moves list of continuous moves
636 -- x_obsoleted_trip_moves list of continous move segments that need to be deleted
637 -- x_errors_tab list of errors to insert into wsh_interface_errors at the end
638 -- x_return_status return status
639 --
640 -- Description:
641 -- Part of TP release process
642 -- Maps the plan's continuous moves: generate and lock candidates
643 -- x_obsoleted_trip_moves will have the obsoleted move segments.
644 --
645
646 PROCEDURE map_moves(
647 x_context IN OUT NOCOPY WSH_TP_RELEASE_INT.context_rec_type,
648 x_plan_trips IN OUT NOCOPY WSH_TP_RELEASE_INT.plan_trip_tab_type,
649 x_plan_trip_moves IN OUT NOCOPY WSH_FTE_TP_INTEGRATION.plan_trip_move_tab_type,
650 x_plan_moves IN OUT NOCOPY WSH_FTE_TP_INTEGRATION.plan_move_tab_type,
651 x_obsoleted_trip_moves IN OUT NOCOPY WSH_FTE_TP_INTEGRATION.obsoleted_trip_move_tab_type,
652 x_errors_tab IN OUT NOCOPY WSH_TP_RELEASE_INT.interface_errors_tab_type,
653 x_return_status OUT NOCOPY VARCHAR2
654 )
655 IS
656 --
657 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'map_moves';
658 --
659 l_debug_on BOOLEAN;
660 --
661 CURSOR c_tp_interface_moves(x_group_id NUMBER) IS
662 SELECT DISTINCT fmi.move_interface_id,
663 fmi.move_id,
664 NVL(fmi.move_type_code, 'CONTINUOUS') move_type_code,
665 fmi.lane_id,
666 fmi.service_level,
667 fmi.planned_flag,
668 fmi.tp_plan_name,
669 fmi.cm_trip_number
670 FROM fte_moves_interface fmi,
671 fte_trip_moves_interface ftmi,
672 wsh_trips_interface wti
673 WHERE wti.group_id = x_group_id
674 AND wti.interface_action_code = G_TP_RELEASE_CODE
678 AND fmi.interface_action_code = G_TP_RELEASE_CODE
675 AND ftmi.trip_interface_id = wti.trip_interface_id
676 AND ftmi.interface_action_code = G_TP_RELEASE_CODE
677 AND fmi.move_interface_id = ftmi.move_interface_id
679 ORDER BY fmi.move_interface_id;
680
681 CURSOR c_tp_interface_trip_moves(x_move_interface_id NUMBER) IS
682 SELECT ftmi.trip_move_interface_id,
683 ftmi.trip_move_id,
684 ftmi.move_interface_id,
685 ftmi.move_id,
686 ftmi.trip_interface_id,
687 ftmi.trip_id,
688 ftmi.sequence_number
689 FROM fte_trip_moves_interface ftmi
690 WHERE ftmi.move_interface_id = x_move_interface_id
691 AND ftmi.interface_action_code = G_TP_RELEASE_CODE
692 ORDER BY ftmi.sequence_number;
693
694 CURSOR c_trip_moves(x_move_id NUMBER) IS
695 SELECT ftm.move_id,
696 ftm.trip_move_id,
697 ftm.trip_id,
698 ftm.sequence_number
699 FROM fte_trip_moves ftm
700 WHERE ftm.move_id = x_move_id
701 ORDER BY ftm.sequence_number;
702
703
704 -- Looks for trips in obsoleted moves.
705 CURSOR c_obsoleted_move_trips(x_move_id IN NUMBER) IS
706 SELECT trip_id
707 FROM fte_trip_moves
708 WHERE move_id = x_move_id;
709
710
711
712 l_move_rec FTE_MOVES_PVT.move_rec_type;
713 l_cm_info c_cm_info%ROWTYPE;
714 l_locked VARCHAR2(1);
715 l_return_status VARCHAR2(1);
716
717 l_unmapped_trip_moves WSH_FTE_TP_INTEGRATION.obsoleted_trip_move_tab_type;
718
719 l_new_index NUMBER := 0;
720 l_tm_index NUMBER := 0;
721 l_trip_index NUMBER;
722 l_map_index NUMBER;
723 l_mapped BOOLEAN;
724
725
726
727 BEGIN
728 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
729 --
730 IF l_debug_on IS NULL THEN
731 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
732 END IF;
733
734 IF l_debug_on THEN
735 WSH_DEBUG_SV.push(l_module_name);
736 END IF;
737
738 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
739
740 --
741 --
742
743 l_new_index := 0;
744 FOR imove IN c_tp_interface_moves(x_group_id => x_context.group_id) LOOP --{
745
746 IF l_debug_on THEN
747 WSH_DEBUG_SV.log(l_module_name,
748 'imove looping: move_interface_id',
749 imove.move_interface_id);
750 END IF;
751
752 l_new_index := l_new_index + 1;
753 x_plan_moves(l_new_index).move_interface_id := imove.move_interface_id;
754 x_plan_moves(l_new_index).move_id := imove.move_id;
755 x_plan_moves(l_new_index).move_type_code := imove.move_type_code;
756 x_plan_moves(l_new_index).lane_id := imove.lane_id;
757 x_plan_moves(l_new_index).service_level := imove.service_level;
758 x_plan_moves(l_new_index).planned_flag := imove.planned_flag;
759 x_plan_moves(l_new_index).tp_plan_name := imove.tp_plan_name;
760 x_plan_moves(l_new_index).cm_trip_number := imove.cm_trip_number;
761 x_plan_moves(l_new_index).trip_move_base_index := NULL;
762
763 l_unmapped_trip_moves.DELETE;
764
765
766 IF l_debug_on THEN
767 WSH_DEBUG_SV.log(l_module_name,
768 'x_plan_moves(l_new_index).move_id',
769 x_plan_moves(l_new_index).move_id);
770 END IF;
771
772 IF x_plan_moves(l_new_index).move_id IS NOT NULL THEN
773 -- if continous move was snapshot, check whether we can lock and update it.
774 lookup_move(
775 p_move_id => x_plan_moves(l_new_index).move_id,
776 p_lock_flag => 'Y',
777 x_move_rec => l_move_rec,
778 x_locked => l_locked,
779 x_return_status => l_return_status);
780
781 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
782 AND l_locked = 'N' THEN
783 -- if move is not found, clear its ID.
784 x_plan_moves(l_new_index).move_id := NULL;
785 ELSIF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
786 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
787 -- move or one of its trip_moves cannot be locked or some other error happened.
788 WSH_TP_RELEASE_INT.stamp_interface_error(
789 p_group_id => x_context.group_id,
790 p_entity_table_name => 'FTE_MOVES_INTERFACE',
791 p_entity_interface_id => imove.move_interface_id,
792 p_message_name => 'WSH_TP_F_NO_LOCK_MOVE',
793 p_token_1_name => 'MOVE_ID',
794 p_token_1_value => x_plan_moves(l_new_index).move_id,
795 x_errors_tab => x_errors_tab,
796 x_return_status => l_return_status);
797 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
798 IF l_debug_on THEN
799 WSH_DEBUG_SV.pop(l_module_name);
800 END IF;
801 RETURN;
802 ELSE
803 -- note move's current firm flag
804 x_plan_moves(l_new_index).fte_planned_flag := l_move_rec.planned_flag;
805
806 -- look up its trip moves to map or obsolete.
807 l_map_index := 1;
808 FOR tm IN c_trip_moves(x_plan_moves(l_new_index).move_id) LOOP
809 l_unmapped_trip_moves(l_map_index).move_id := tm.move_id;
810 l_unmapped_trip_moves(l_map_index).trip_move_id := tm.trip_move_id;
811 l_unmapped_trip_moves(l_map_index).trip_id := tm.trip_id;
815 END IF;
812 l_unmapped_trip_moves(l_map_index).sequence_number := tm.sequence_number;
813 l_map_index := l_map_index + 1;
814 END LOOP;
816 END IF;
817
818 FOR itripmove IN c_tp_interface_trip_moves(x_move_interface_id => x_plan_moves(l_new_index).move_interface_id) LOOP --[
819
820 IF l_debug_on THEN
821 WSH_DEBUG_SV.log(l_module_name,
822 'itripmove looping: trip_move_interface_id',
823 itripmove.trip_move_interface_id);
824 END IF;
825
826 IF x_plan_moves(l_new_index).trip_move_base_index IS NULL THEN
827 IF x_plan_trip_moves.COUNT = 0 THEN
828 l_tm_index := 1;
829 ELSE
830 l_tm_index := x_plan_trip_moves.LAST + 1;
831 END IF;
832 x_plan_moves(l_new_index).trip_move_base_index := l_tm_index;
833 ELSE
834 l_tm_index := l_tm_index + 1;
835 END IF;
836
837 -- map trip_interface_id to its plan index
838 l_trip_index := x_plan_trips.FIRST;
839 WHILE l_trip_index IS NOT NULL LOOP
840 EXIT WHEN itripmove.trip_interface_id = x_plan_trips(l_trip_index).trip_interface_id;
841 l_trip_index := x_plan_trips.NEXT(l_trip_index);
842 END LOOP;
843
844 IF l_trip_index IS NULL THEN
845 WSH_TP_RELEASE_INT.stamp_interface_error(
846 p_group_id => x_context.group_id,
847 p_entity_table_name => 'FTE_TRIP_MOVES_INTERFACE',
848 p_entity_interface_id => itripmove.trip_move_interface_id,
849 p_message_name => 'WSH_TP_F_TRIP_NOT_MAPPED',
850 p_token_1_name => 'PLAN_CM_NUM',
851 p_token_1_value => x_plan_moves(l_new_index).cm_trip_number,
852 x_errors_tab => x_errors_tab,
853 x_return_status => l_return_status);
854 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
855 IF l_debug_on THEN
856 WSH_DEBUG_SV.pop(l_module_name);
857 END IF;
858 RETURN;
859 END IF;
860
861 x_plan_trip_moves(l_tm_index).trip_move_interface_id := itripmove.trip_move_interface_id;
862 x_plan_trip_moves(l_tm_index).trip_move_id := itripmove.trip_move_id;
863 x_plan_trip_moves(l_tm_index).move_interface_id := itripmove.move_interface_id;
864 x_plan_trip_moves(l_tm_index).move_id := itripmove.move_id;
865 x_plan_trip_moves(l_tm_index).move_index := l_new_index;
866 x_plan_trip_moves(l_tm_index).trip_interface_id := itripmove.trip_interface_id;
867 x_plan_trip_moves(l_tm_index).trip_id := itripmove.trip_id;
868 x_plan_trip_moves(l_tm_index).trip_index := l_trip_index;
869 x_plan_trip_moves(l_tm_index).sequence_number := itripmove.sequence_number;
870
871 IF l_debug_on THEN
872 WSH_DEBUG_SV.log(l_module_name,
873 'x_plan_trip_moves(l_tm_index).trip_move_id',
874 x_plan_trip_moves(l_tm_index).trip_move_id);
875 END IF;
876 IF x_plan_moves(l_new_index).move_id IS NULL THEN
877
878 -- if the move is not in FTE data, neither are its trip_move records.
879 x_plan_trip_moves(l_tm_index).trip_move_id := NULL;
880
881 ELSE
882
883 -- check if this record still exists
884 -- (if yes, it'll already be locked by looking the move up.)
885 l_map_index := NULL;
886 l_mapped := FALSE;
887
888 IF l_debug_on THEN
889 WSH_DEBUG_SV.log(l_module_name,
890 'l_unmapped_trip_moves.COUNT',
891 l_unmapped_trip_moves.COUNT);
892 END IF;
893
894 IF l_unmapped_trip_moves.COUNT > 0 THEN
895 l_map_index := l_unmapped_trip_moves.FIRST;
896 WHILE l_map_index IS NOT NULL LOOP
897 -- this is mapped only if key attributes match.
898 IF l_debug_on THEN
899 WSH_DEBUG_SV.log(l_module_name,
900 'l_unmapped_trip_moves(l_map_index).trip_move_id', l_unmapped_trip_moves(l_map_index).trip_move_id);
901 WSH_DEBUG_SV.log(l_module_name,
902 'x_plan_trip_moves(l_tm_index).trip_move_id', x_plan_trip_moves(l_tm_index).trip_move_id);
903 END IF;
904
905 IF l_debug_on THEN
906 WSH_DEBUG_SV.log(l_module_name,
907 'l_unmapped_trip_moves(l_map_index).trip_id', l_unmapped_trip_moves(l_map_index).trip_id);
908 WSH_DEBUG_SV.log(l_module_name,
909 'x_plan_trips(x_plan_trip_moves(l_tm_index).trip_index).trip_id', x_plan_trips(x_plan_trip_moves(l_tm_index).trip_index).trip_id);
910 WSH_DEBUG_SV.log(l_module_name,
911 'l_unmapped_trip_moves(l_map_index).sequence_number', l_unmapped_trip_moves(l_map_index).sequence_number);
912 WSH_DEBUG_SV.log(l_module_name,
913 'x_plan_trip_moves(l_tm_index).sequence_number', x_plan_trip_moves(l_tm_index).sequence_number);
914 END IF;
915 IF (l_unmapped_trip_moves(l_map_index).trip_id
916 = x_plan_trips(x_plan_trip_moves(l_tm_index).trip_index).trip_id)
917 AND (l_unmapped_trip_moves(l_map_index).sequence_number
918 = x_plan_trip_moves(l_tm_index).sequence_number) THEN
919 -- the record matches, so it is mapped
920 x_plan_trip_moves(l_tm_index).trip_move_id := l_unmapped_trip_moves(l_map_index).trip_move_id;
921 l_unmapped_trip_moves.DELETE(l_map_index);
922 EXIT;
923 END IF;
924
928
925 l_map_index := l_unmapped_trip_moves.NEXT(l_map_index);
926 END LOOP;
927 END IF;
929 IF l_map_index IS NULL THEN
930 -- being NULL means this record does not exist or is not mapped.
931 x_plan_trip_moves(l_tm_index).trip_move_id := NULL;
932 END IF;
933
934 END IF;
935
936 END LOOP; --] itripmove
937
938 -- transfer unmapped trip moves to obsoleted trip moves
939 IF l_unmapped_trip_moves.COUNT > 0 THEN
940
941 -- is this move currently firmed (so it should have all trip_moves mapped)?
942 IF x_plan_moves(l_new_index).fte_planned_flag <> 'N' THEN
943 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
944 WSH_TP_RELEASE_INT.stamp_interface_error(
945 p_group_id => x_context.group_id,
946 p_entity_table_name => 'FTE_MOVES_INTERFACE',
947 p_entity_interface_id => x_plan_moves(l_new_index).move_interface_id,
948 p_message_name => 'WSH_TP_F_FIRM_MOVE_DIFF',
949 p_token_1_name => 'PLAN_TRIP_NUM',
950 p_token_1_value => x_plan_trips(1).tp_trip_number,
951 p_token_2_name => 'MOVE_ID',
952 p_token_2_value => x_plan_moves(l_new_index).move_id,
953 x_errors_tab => x_errors_tab,
954 x_return_status => l_return_status);
955 IF l_debug_on THEN
956 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
957 WSH_DEBUG_SV.pop(l_module_name);
958 END IF;
959 RETURN;
960 END IF;
961
962
963 l_map_index := l_unmapped_trip_moves.FIRST;
964 IF x_obsoleted_trip_moves.COUNT = 0 THEN
965 l_tm_index := 1;
966 ELSE
967 l_tm_index := x_obsoleted_trip_moves.LAST + 1;
968 END IF;
969
970 WHILE l_map_index IS NOT NULL LOOP
971 x_obsoleted_trip_moves(l_tm_index) := l_unmapped_trip_moves(l_map_index);
972 l_tm_index := l_tm_index + 1;
973 l_map_index := l_unmapped_trip_moves.NEXT(l_map_index);
974 END LOOP;
975 END IF;
976
977 END LOOP; --} imove
978
979
980 IF l_debug_on THEN
981 WSH_DEBUG_SV.log(l_module_name, 'x_plan_trips.COUNT to remove unmapped moves', x_plan_trips.COUNT);
982 END IF;
983
984 l_map_index := 1;
985 l_trip_index := x_plan_trips.FIRST;
986 WHILE l_trip_index IS NOT NULL LOOP
987
988 IF x_plan_trips(l_trip_index).trip_id IS NOT NULL THEN
989
990 int_lookup_cm_info (
991 p_trip_id => x_plan_trips(l_trip_index).trip_id,
992 x_int_cm_info_rec => l_cm_info,
993 x_return_status => l_return_status);
994
995 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
996 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
997 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
998 WSH_TP_RELEASE_INT.stamp_interface_error(
999 p_group_id => x_context.group_id,
1000 p_entity_table_name => 'WSH_TRIPS_INTERFACE',
1001 p_entity_interface_id => x_plan_trips(l_trip_index).trip_interface_id,
1002 p_message_name => 'WSH_TP_F_MOVE_LOOKUP',
1003 p_token_1_name => 'PLAN_TRIP_NUM',
1004 p_token_1_value => x_plan_trips(l_trip_index).tp_trip_number,
1005 x_errors_tab => x_errors_tab,
1006 x_return_status => l_return_status);
1007 IF l_debug_on THEN
1008 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
1009 WSH_DEBUG_SV.pop(l_module_name);
1010 END IF;
1014 IF l_cm_info.move_id IS NOT NULL THEN
1011 RETURN;
1012 END IF;
1013
1015
1016 l_mapped := FALSE;
1017 -- check if this move has been added to obsoleted list (or has trip_move record).
1018 IF x_obsoleted_trip_moves.COUNT > 0 THEN
1019
1020 l_map_index := x_obsoleted_trip_moves.FIRST;
1021
1022 WHILE l_map_index IS NOT NULL LOOP--{
1023 IF x_obsoleted_trip_moves(l_map_index).move_id = l_cm_info.move_id THEN
1024 IF x_obsoleted_trip_moves(l_map_index).trip_move_id IS NOT NULL THEN
1025 -- if this is a trip_move to obsolete, it implies the move is mapped.
1026 l_map_index := NULL;
1027 l_mapped := TRUE;
1028 END IF;
1029 EXIT;
1030 END IF;
1031 l_map_index := x_obsoleted_trip_moves.NEXT(l_map_index);
1032 END LOOP; --}
1036
1033 ELSE
1034 l_map_index := NULL;
1035 END IF;
1037 -- if not seen before, determine whether this move should be obsoleted.
1038 IF l_map_index IS NULL AND NOT l_mapped
1039 AND x_plan_moves.COUNT > 0 THEN
1040 l_map_index := x_plan_moves.FIRST;
1041 WHILE l_map_index IS NOT NULL LOOP
1042 IF x_plan_moves(l_map_index).move_id = l_cm_info.move_id THEN
1043 EXIT;
1044 END IF;
1045 l_map_index := x_plan_moves.NEXT(l_map_index);
1046 END LOOP;
1047 END IF;
1048
1049 IF l_map_index IS NULL AND NOT l_mapped THEN
1050 -- lock this move, verify its planned_flag and then add it to the list of obsoleted moves.
1051 lookup_move(
1052 p_move_id => l_cm_info.move_id,
1053 p_lock_flag => 'Y',
1054 x_move_rec => l_move_rec,
1055 x_locked => l_locked,
1056 x_return_status => l_return_status);
1057
1058 IF (l_locked = 'Y')
1059 OR (l_return_status IN
1060 (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
1061 -- move or one of its trip_moves cannot be locked or some other error happened.
1062 WSH_TP_RELEASE_INT.stamp_interface_error(
1063 p_group_id => x_context.group_id,
1064 p_entity_table_name => 'WSH_TRIPS_INTERFACE',
1065 p_entity_interface_id => x_plan_trips(l_trip_index).trip_interface_id,
1066 p_message_name => 'WSH_TP_F_NO_LOCK_OBS_MOVE',
1067 p_token_1_name => 'MOVE_ID',
1068 p_token_1_value => l_cm_info.move_id,
1069 x_errors_tab => x_errors_tab,
1070 x_return_status => l_return_status);
1071 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1072 IF l_debug_on THEN
1073 WSH_DEBUG_SV.pop(l_module_name);
1074 END IF;
1075 RETURN;
1076 END IF;
1077
1078 -- we should not obsolete a firmed move.
1079 IF NVL(l_move_rec.planned_flag, 'N') <> 'N' THEN
1080 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1081 WSH_TP_RELEASE_INT.stamp_interface_error(
1082 p_group_id => x_context.group_id,
1083 p_entity_table_name => 'WSH_TRIPS_INTERFACE',
1084 p_entity_interface_id => x_plan_trips(l_trip_index).trip_interface_id,
1085 p_message_name => 'WSH_TP_F_FIRM_MOVE_NO_MATCH',
1086 p_token_1_name => 'PLAN_TRIP_NUM',
1087 p_token_1_value => x_plan_trips(l_trip_index).tp_trip_number,
1088 x_errors_tab => x_errors_tab,
1089 x_return_status => l_return_status);
1090 IF l_debug_on THEN
1091 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
1092 WSH_DEBUG_SV.pop(l_module_name);
1093 END IF;
1094 RETURN;
1095 END IF;
1096
1097 x_obsoleted_trip_moves(x_obsoleted_trip_moves.COUNT + 1).move_id := l_cm_info.move_id;
1098
1099 FOR move in c_obsoleted_move_trips(l_cm_info.move_id) LOOP
1100 -- Keep track of the trips that are in an obsoleted move.
1101 -- This is used to delete these trips if necessary later.
1102 -- Only the trip_id is populated in the record.
1103 -- Lock the trip that might get deleted.
1104 BEGIN
1105 WSH_TRIPS_PVT.lock_trip_no_compare(move.trip_id);
1106 x_obsoleted_trip_moves(x_obsoleted_trip_moves.COUNT + 1).trip_id := move.trip_id;
1107 EXCEPTION
1108 WHEN OTHERS THEN
1109 -- Do nothing, if it is locked by another process,
1110 -- Unlikely that it will need to be deleted.
1111 IF l_debug_on THEN
1112 WSH_DEBUG_SV.log(l_module_name, 'Trip could not be locked: ', move.trip_id);
1113 END IF;
1114 END;
1115
1116 END LOOP;
1117 END IF;
1118
1119 END IF;
1120
1121 END IF;
1122
1123 l_trip_index := x_plan_trips.NEXT(l_trip_index);
1124 END LOOP;
1125
1126 --
1127 --
1128
1129
1130 IF l_debug_on THEN
1131 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
1132 WSH_DEBUG_SV.pop(l_module_name);
1133 END IF;
1134
1135 EXCEPTION
1136 WHEN OTHERS THEN
1137 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1138
1139 IF c_tp_interface_moves%ISOPEN THEN
1140 CLOSE c_tp_interface_moves;
1141 END IF;
1142 IF c_tp_interface_trip_moves%ISOPEN THEN
1143 CLOSE c_tp_interface_trip_moves;
1144 END IF;
1145 IF c_trip_moves%ISOPEN THEN
1146 CLOSE c_trip_moves;
1147 END IF;
1148
1149 WSH_UTIL_CORE.DEFAULT_HANDLER(
1150 'FTE_TP_GRP.map_moves',
1151 l_module_name);
1152 IF l_debug_on THEN
1153 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1154 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1155 END IF;
1156
1157 END map_moves;
1158
1159
1160
1161 --
1162 -- Procedure: reconciliate_moves
1163 -- Parameters:
1167 -- x_plan_moves list of continuous moves
1164 -- x_context context in this session
1165 -- x_plan_trips list of trips mapped to interface trips
1166 -- x_plan_trip_moves list of continuous move segments
1168 -- x_obsoleted_trip_moves list of continous move segments that need to be deleted
1169 -- x_errors_tab list of errors to insert into wsh_interface_errors at the end
1170 -- x_return_status return status
1171 --
1172 -- Description:
1173 -- Part of TP release process
1174 -- Create or update continous moves and their segments, delete obsoleted segments.
1175 --
1176
1177 PROCEDURE reconciliate_moves(
1178 x_context IN OUT NOCOPY WSH_TP_RELEASE_INT.context_rec_type,
1179 x_plan_trips IN OUT NOCOPY WSH_TP_RELEASE_INT.plan_trip_tab_type,
1180 x_plan_trip_moves IN OUT NOCOPY WSH_FTE_TP_INTEGRATION.plan_trip_move_tab_type,
1181 x_plan_moves IN OUT NOCOPY WSH_FTE_TP_INTEGRATION.plan_move_tab_type,
1182 x_obsoleted_trip_moves IN OUT NOCOPY WSH_FTE_TP_INTEGRATION.obsoleted_trip_move_tab_type,
1183 x_errors_tab IN OUT NOCOPY WSH_TP_RELEASE_INT.interface_errors_tab_type,
1184 x_return_status OUT NOCOPY VARCHAR2
1185 )
1186 IS
1187 --
1188 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'reconciliate_moves';
1189 --
1190 l_debug_on BOOLEAN;
1191 --
1192
1193 l_m_index NUMBER;
1194 l_tm_index NUMBER;
1195 l_id NUMBER;
1196 l_return_status VARCHAR2(1);
1197 l_move_rec FTE_MOVES_PVT.move_rec_type;
1198 l_trip_move_rec FTE_TRIP_MOVES_PVT.trip_moves_rec_type;
1199
1200
1201 BEGIN
1202 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1203 --
1204 IF l_debug_on IS NULL THEN
1205 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1206 END IF;
1207
1208 IF l_debug_on THEN
1209 WSH_DEBUG_SV.push(l_module_name);
1210 END IF;
1211
1212 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1213
1214
1215 --
1216 --
1217
1218 IF l_debug_on THEN
1219 WSH_DEBUG_SV.log(l_module_name, 'x_obsoleted_trip_moves.COUNT', x_obsoleted_trip_moves.COUNT);
1220 END IF;
1221
1222 -- first, remove obsoleted trip moves in order to avoid duplicate data.
1223 IF x_obsoleted_trip_moves.COUNT > 0 THEN
1224
1225 l_tm_index := x_obsoleted_trip_moves.FIRST;
1226 WHILE l_tm_index IS NOT NULL LOOP
1227 -- x_obsoleted_trip_moves(l_tm_index).move_id would be null in the case when the record contains trips
1228 -- that belong to an obsoleted move.
1229 IF x_obsoleted_trip_moves(l_tm_index).trip_move_id IS NULL
1230 AND x_obsoleted_trip_moves(l_tm_index).move_id IS NOT NULL THEN
1231
1232 -- delete the move
1233 fte_moves_pvt.delete_move(
1234 p_move_id => x_obsoleted_trip_moves(l_tm_index).move_id,
1235 p_validate_flag => 'Y',
1236 x_return_status => l_return_status);
1237 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1238 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1239
1240 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1241 WSH_TP_RELEASE_INT.stamp_interface_error(
1242 p_group_id => x_context.group_id,
1243 p_entity_table_name => 'NONE',
1244 p_entity_interface_id => -1,
1245 p_message_name => 'WSH_TP_F_DELETE_OBS_MOVE',
1246 p_token_1_name => 'MOVE_ID',
1247 p_token_1_value => x_obsoleted_trip_moves(l_tm_index).move_id,
1248 x_errors_tab => x_errors_tab,
1249 x_return_status => l_return_status);
1250 IF l_debug_on THEN
1251 WSH_DEBUG_SV.pop(l_module_name);
1252 END IF;
1253 RETURN;
1254 END IF;
1255
1256 ELSIF x_obsoleted_trip_moves(l_tm_index).move_id IS NOT NULL THEN
1257
1258 -- delete the trip_move segment
1259 fte_trip_moves_pvt.delete_trip_moves(
1260 p_trip_move_id => x_obsoleted_trip_moves(l_tm_index).trip_move_id,
1261 x_return_status => l_return_status);
1262 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1263 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1264
1265 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1266 WSH_TP_RELEASE_INT.stamp_interface_error(
1267 p_group_id => x_context.group_id,
1268 p_entity_table_name => 'NONE',
1269 p_entity_interface_id => -1,
1270 p_message_name => 'WSH_TP_F_DELETE_OBS_MOVE',
1271 p_token_1_name => 'MOVE_ID',
1272 p_token_1_value => x_obsoleted_trip_moves(l_tm_index).move_id,
1273 x_errors_tab => x_errors_tab,
1274 x_return_status => l_return_status);
1275 IF l_debug_on THEN
1276 WSH_DEBUG_SV.pop(l_module_name);
1277 END IF;
1278 RETURN;
1279 END IF;
1280
1281 END IF;
1282
1283 l_tm_index := x_obsoleted_trip_moves.NEXT(l_tm_index);
1284 END LOOP;
1285
1286 END IF;
1287
1288
1289
1290 IF x_plan_moves.COUNT > 0 THEN
1291
1292 l_m_index := x_plan_moves.FIRST;
1293 WHILE l_m_index IS NOT NULL LOOP
1294
1295 l_move_rec.move_id := x_plan_moves(l_m_index).move_id;
1299 l_move_rec.planned_flag := 'N';
1296 l_move_rec.move_type_code := x_plan_moves(l_m_index).move_type_code;
1297 l_move_rec.lane_id := x_plan_moves(l_m_index).lane_id;
1298 l_move_rec.service_level := x_plan_moves(l_m_index).service_level;
1300 l_move_rec.cm_trip_number := x_plan_moves(l_m_index).cm_trip_number;
1301 l_move_rec.tp_plan_name := x_plan_moves(l_m_index).tp_plan_name;
1302
1303 IF x_plan_moves(l_m_index).move_id IS NULL THEN
1304 fte_moves_pvt.create_move(
1305 p_move_info => l_move_rec,
1306 x_move_id => l_id,
1307 x_return_status => l_return_status);
1308 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1309 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1310 WSH_TP_RELEASE_INT.stamp_interface_error(
1311 p_group_id => x_context.group_id,
1312 p_entity_table_name => 'FTE_MOVES_INTERFACE',
1316 x_return_status => l_return_status);
1313 p_entity_interface_id => x_plan_moves(l_m_index).move_interface_id,
1314 p_message_name => 'WSH_TP_F_CREATE_MOVE',
1315 x_errors_tab => x_errors_tab,
1317 IF l_debug_on THEN
1318 WSH_DEBUG_SV.pop(l_module_name);
1319 END IF;
1320 RETURN;
1321 END IF;
1322 x_plan_moves(l_m_index).move_id := l_id;
1323 ELSE
1324 fte_moves_pvt.update_move(
1325 p_move_info => l_move_rec,
1326 x_return_status => l_return_status);
1327 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1328 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1329 WSH_TP_RELEASE_INT.stamp_interface_error(
1330 p_group_id => x_context.group_id,
1331 p_entity_table_name => 'FTE_MOVES_INTERFACE',
1332 p_entity_interface_id => x_plan_moves(l_m_index).move_interface_id,
1333 p_message_name => 'WSH_TP_F_UPDATE_MOVE',
1334 p_token_1_name => 'MOVE_ID',
1335 p_token_1_value => x_plan_moves(l_m_index).move_id,
1336 x_errors_tab => x_errors_tab,
1337 x_return_status => l_return_status);
1338 IF l_debug_on THEN
1339 WSH_DEBUG_SV.pop(l_module_name);
1340 END IF;
1341 RETURN;
1342 END IF;
1343 END IF;
1344
1345 l_tm_index := x_plan_moves(l_m_index).trip_move_base_index;
1346 WHILE l_tm_index IS NOT NULL LOOP
1347
1348 l_trip_move_rec.move_id := x_plan_moves(x_plan_trip_moves(l_tm_index).move_index).move_id;
1349 l_trip_move_rec.trip_id := x_plan_trips(x_plan_trip_moves(l_tm_index).trip_index).trip_id;
1350 l_trip_move_rec.sequence_number := x_plan_trip_moves(l_tm_index).sequence_number;
1351 l_trip_move_rec.trip_move_id := x_plan_trip_moves(l_tm_index).trip_move_id;
1352
1353 IF x_plan_trip_moves(l_tm_index).trip_move_id IS NULL THEN
1354 fte_trip_moves_pvt.create_trip_moves(
1355 p_trip_moves_info => l_trip_move_rec,
1356 x_trip_move_id => l_id,
1357 x_return_status => l_return_status);
1358 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1359 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1360 WSH_TP_RELEASE_INT.stamp_interface_error(
1361 p_group_id => x_context.group_id,
1362 p_entity_table_name => 'FTE_TRIP_MOVES_INTERFACE',
1363 p_entity_interface_id => x_plan_trip_moves(l_tm_index).trip_move_interface_id,
1364 p_message_name => 'WSH_TP_F_CREATE_TRIP_MOVE',
1365 x_errors_tab => x_errors_tab,
1366 x_return_status => l_return_status);
1367 IF l_debug_on THEN
1368 WSH_DEBUG_SV.pop(l_module_name);
1369 END IF;
1370 RETURN;
1371 END IF;
1372 x_plan_trip_moves(l_tm_index).trip_move_id := l_id;
1373 ELSE
1374 fte_trip_moves_pvt.update_trip_moves(
1375 p_trip_moves_info => l_trip_move_rec,
1376 x_return_status => l_return_status);
1377 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1378 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1379 WSH_TP_RELEASE_INT.stamp_interface_error(
1380 p_group_id => x_context.group_id,
1381 p_entity_table_name => 'FTE_TRIP_MOVES_INTERFACE',
1385 x_return_status => l_return_status);
1382 p_entity_interface_id => x_plan_trip_moves(l_tm_index).trip_move_interface_id,
1383 p_message_name => 'WSH_TP_F_UPDATE_TRIP_MOVE',
1384 x_errors_tab => x_errors_tab,
1386 IF l_debug_on THEN
1387 WSH_DEBUG_SV.pop(l_module_name);
1388 END IF;
1389 RETURN;
1390 END IF;
1391 END IF;
1392
1393 l_tm_index := x_plan_trip_moves.NEXT(l_tm_index);
1394 IF l_tm_index IS NOT NULL THEN
1395 IF x_plan_trip_moves(l_tm_index).move_interface_id <> x_plan_moves(l_m_index).move_interface_id THEN
1396 -- finished looping through trip_moves for this move.
1397 l_tm_index := NULL;
1398 END IF;
1399 END IF;
1400 END LOOP;
1401
1402 l_m_index := x_plan_moves.NEXT(l_m_index);
1403 END LOOP;
1404
1405 END IF;
1406
1407 --
1408 --
1409
1410 IF l_debug_on THEN
1411 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
1412 WSH_DEBUG_SV.pop(l_module_name);
1413 END IF;
1414
1415 EXCEPTION
1416 WHEN OTHERS THEN
1417 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1418 WSH_UTIL_CORE.DEFAULT_HANDLER(
1419 'FTE_TP_GRP.reconciliate_moves',
1420 l_module_name);
1421 IF l_debug_on THEN
1422 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1423 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1424 END IF;
1425
1426 END reconciliate_moves;
1427
1428
1429 --
1430 -- Procedure: tp_firm_moves
1431 -- Parameters:
1432 -- x_context context in this session
1433 -- x_plan_trips list of trips mapped to interface trips
1434 -- x_plan_trip_moves list of continuous move segments
1435 -- x_plan_moves list of continuous moves
1436 -- x_obsoleted_trip_moves list of continous move segments that need to be deleted
1437 -- x_errors_tab list of errors to insert into wsh_interface_errors at the end
1438 -- x_return_status return status
1439 --
1440 -- Description:
1441 -- Part of TP release process
1442 -- Upgrade continuous moves' PLANNED_FLAG based on the plan
1443 --
1444
1445 PROCEDURE tp_firm_moves(
1446 x_context IN OUT NOCOPY WSH_TP_RELEASE_INT.context_rec_type,
1447 x_plan_moves IN OUT NOCOPY WSH_FTE_TP_INTEGRATION.plan_move_tab_type,
1448 x_errors_tab IN OUT NOCOPY WSH_TP_RELEASE_INT.interface_errors_tab_type,
1449 x_return_status OUT NOCOPY VARCHAR2
1450 )
1451 IS
1452 --
1453 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'tp_firm_moves';
1454 --
1455 l_debug_on BOOLEAN;
1456 --
1457 l_m_index NUMBER;
1458 l_return_status VARCHAR2(1);
1459 l_move_rec FTE_MOVES_PVT.move_rec_type;
1460
1461 BEGIN
1462 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1463 --
1464 IF l_debug_on IS NULL THEN
1465 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1466 END IF;
1467
1468 IF l_debug_on THEN
1469 WSH_DEBUG_SV.push(l_module_name);
1470 END IF;
1471
1472 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1473
1474
1475 --
1476 --
1477
1478 IF x_plan_moves.COUNT > 0 THEN
1479
1480 l_m_index := x_plan_moves.FIRST;
1481 WHILE l_m_index IS NOT NULL LOOP
1482
1483 IF ( (NVL(x_plan_moves(l_m_index).fte_planned_flag, 'N') = 'N')
1484 AND (x_plan_moves(l_m_index).planned_flag <> 'N'))
1485 OR
1486 ( (NVL(x_plan_moves(l_m_index).fte_planned_flag, 'N') = 'Y')
1487 AND (x_plan_moves(l_m_index).planned_flag = 'F')) THEN
1488 l_move_rec.move_id := x_plan_moves(l_m_index).move_id;
1489 l_move_rec.move_type_code := x_plan_moves(l_m_index).move_type_code;
1490 l_move_rec.lane_id := x_plan_moves(l_m_index).lane_id;
1491 l_move_rec.service_level := x_plan_moves(l_m_index).service_level;
1492 l_move_rec.planned_flag := x_plan_moves(l_m_index).planned_flag;
1493 l_move_rec.cm_trip_number := x_plan_moves(l_m_index).cm_trip_number;
1494 l_move_rec.tp_plan_name := x_plan_moves(l_m_index).tp_plan_name;
1495
1496 fte_moves_pvt.update_move(
1497 p_move_info => l_move_rec,
1498 x_return_status => l_return_status);
1499 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1500 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1501 WSH_TP_RELEASE_INT.stamp_interface_error(
1502 p_group_id => x_context.group_id,
1503 p_entity_table_name => 'FTE_MOVES_INTERFACE',
1504 p_entity_interface_id => x_plan_moves(l_m_index).move_interface_id,
1505 p_message_name => 'WSH_TP_F_FIRM_MOVE',
1506 p_token_1_name => 'MOVE_ID',
1507 p_token_1_value => x_plan_moves(l_m_index).move_id,
1508 x_errors_tab => x_errors_tab,
1509 x_return_status => l_return_status);
1510 IF l_debug_on THEN
1511 WSH_DEBUG_SV.pop(l_module_name);
1512 END IF;
1513 RETURN;
1514 END IF;
1515
1516 END IF;
1517
1518 l_m_index := x_plan_moves.NEXT(l_m_index);
1519 END LOOP;
1520
1524 --
1521 END IF;
1522
1523 --
1525
1526 IF l_debug_on THEN
1527 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
1528 WSH_DEBUG_SV.pop(l_module_name);
1529 END IF;
1530
1531 EXCEPTION
1532 WHEN OTHERS THEN
1533 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1534 WSH_UTIL_CORE.DEFAULT_HANDLER(
1535 'FTE_TP_GRP.tp_firm_moves',
1536 l_module_name);
1537 IF l_debug_on THEN
1538 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1539 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1540 END IF;
1541
1542 END tp_firm_moves;
1543
1544
1545
1546 --
1547 -- Procedure: purge_interface_tables
1548 -- Parameters:
1549 -- p_group_ids list of group_ids to purge
1550 -- FTE interface tables (based on WSH_TRIPS_INTERFACE.GROUP_ID)
1551 -- x_return_status return status
1552 --
1553 -- Description:
1554 -- Part of TP release process
1555 -- Delete the records from FTE interface tables:
1556 -- FTE_MOVES_INTERFACE
1557 -- FTE_TRIP_MOVES_INTERFACE
1558 --
1559 PROCEDURE purge_interface_tables(
1560 p_group_ids IN WSH_TP_RELEASE_GRP.ID_TAB_TYPE,
1561 x_return_status OUT NOCOPY VARCHAR2)
1562 IS
1563 --
1564 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'purge_interface_tables';
1565 --
1566 l_debug_on BOOLEAN;
1567 --
1568 i NUMBER;
1569 BEGIN
1570 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1571 --
1572 IF l_debug_on IS NULL THEN
1573 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1574 END IF;
1575
1576 IF l_debug_on THEN
1577 WSH_DEBUG_SV.push(l_module_name);
1578 END IF;
1579
1580 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1581
1582
1583 --
1584 --
1585
1586 -- The tables must be deleted in the right order so that their
1587 -- records can be identified correctly.
1588
1589 FORALL i in p_group_ids.FIRST ..p_group_ids.LAST
1590 delete from fte_moves_interface fmi
1591 where fmi.move_interface_id in (
1592 select distinct ftmi.move_interface_id
1593 from fte_trip_moves_interface ftmi,
1594 wsh_trips_interface wti
1595 where wti.group_id = p_group_ids(i)
1596 and wti.interface_action_code = G_TP_RELEASE_CODE
1597 and ftmi.trip_interface_id = wti.trip_interface_id
1598 and ftmi.interface_action_code = G_TP_RELEASE_CODE)
1599 and fmi.interface_action_code = G_TP_RELEASE_CODE;
1600
1601
1602 FORALL i in p_group_ids.FIRST ..p_group_ids.LAST
1603 delete from fte_trip_moves_interface ftmi
1604 where ftmi.trip_move_interface_id in (
1605 select ftmi2.trip_move_interface_id
1606 from fte_trip_moves_interface ftmi2,
1607 wsh_trips_interface wti
1608 where wti.group_id = p_group_ids(i)
1609 and wti.interface_action_code = G_TP_RELEASE_CODE
1610 and ftmi2.trip_interface_id = wti.trip_interface_id
1611 and ftmi2.interface_action_code = G_TP_RELEASE_CODE)
1612 and ftmi.interface_action_code = G_TP_RELEASE_CODE;
1613
1614 --
1615 --
1616
1617 IF l_debug_on THEN
1618 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
1619 WSH_DEBUG_SV.pop(l_module_name);
1620 END IF;
1621
1622 EXCEPTION
1623 WHEN OTHERS THEN
1624 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1625 WSH_UTIL_CORE.DEFAULT_HANDLER(
1626 'FTE_TP_GRP.purge_interface_tables',
1627 l_module_name);
1628 IF l_debug_on THEN
1632
1629 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1630 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1631 END IF;
1633 END purge_interface_tables;
1634
1635
1636
1637 END FTE_TP_GRP;