1 PACKAGE BODY WSH_FTE_TP_INTEGRATION as
2 /* $Header: WSHFTPIB.pls 115.3 2003/09/05 19:01:02 wrudge noship $ */
3
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_FTE_TP_INTEGRATION';
6 --
7
8 --
9 -- Procedure: lookup_cm_info
10 -- Parameters:
11 -- p_trip_id trip_id to look up its continuous move segment
12 -- x_cm_info_rec attributes of continuous move and segment
13 -- x_return_status return status
14 --
15 -- Description:
16 -- Wrapper for FTE api:
17 -- Looks up continuous move information associated with the trip
18 -- to be displayed in shipping UIs.
19 --
20 --
21
22 PROCEDURE lookup_cm_info (
23 p_trip_id IN NUMBER,
24 x_cm_info_rec OUT NOCOPY cm_info_rec_type,
25 x_return_status OUT NOCOPY VARCHAR2)
26 IS
27 --
28 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'lookup_cm_info';
29 --
30 l_debug_on BOOLEAN;
31 --
32 BEGIN
33 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
34 --
35 IF l_debug_on IS NULL THEN
36 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
37 END IF;
38
39 IF l_debug_on THEN
40 WSH_DEBUG_SV.push(l_module_name);
41 END IF;
42
43 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
44
45 IF WSH_UTIL_CORE.TP_IS_INSTALLED = 'Y' THEN
46 FTE_TP_GRP.lookup_cm_info (
47 p_trip_id => p_trip_id,
48 x_cm_info_rec => x_cm_info_rec,
49 x_return_status => x_return_status);
50 END IF;
51
52 IF l_debug_on THEN
53 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
54 WSH_DEBUG_SV.pop(l_module_name);
55 END IF;
56
57 EXCEPTION
58 WHEN OTHERS THEN
59 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
60 WSH_UTIL_CORE.DEFAULT_HANDLER(
61 'WSH_FTE_TP_INTEGRATION.lookup_cm_info',
62 l_module_name);
63 IF l_debug_on THEN
64 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
65 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
66 END IF;
67
68 END lookup_cm_info;
69
70
71
72 --
73 -- Procedure: trip_callback
74 -- Parameters:
75 -- p_api_version_number known api version (1.0)
76 -- p_init_msg_list FND_API.G_TRUE to reset list
77 -- x_return_status return status
78 -- x_msg_count number of messages in the list
79 -- x_msg_data text of messages
80 -- p_actions_prms action parameters record
81 -- used to identify the action triggering
82 -- the callback to FTE.
83 -- p_rec_attr_tab table of trip records to process
84 --
85 -- Description:
86 -- Wrapper for FTE api:
87 -- take care of continuous moves based on the action being
88 -- performed on the trips.
89 --
90
91 PROCEDURE trip_callback (
92 p_api_version_number IN NUMBER,
93 p_init_msg_list IN VARCHAR2,
94 x_return_status OUT NOCOPY VARCHAR2,
95 x_msg_count OUT NOCOPY NUMBER,
96 x_msg_data OUT NOCOPY VARCHAR2,
97 p_action_prms IN WSH_TRIPS_GRP.action_parameters_rectype,
98 p_rec_attr_tab IN WSH_TRIPS_PVT.Trip_Attr_Tbl_Type)
99 IS
100 --
101 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'trip_callback';
102 --
103 l_debug_on BOOLEAN;
104 --
105 BEGIN
106 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
107 --
108 IF l_debug_on IS NULL THEN
109 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
110 END IF;
111
112 IF l_debug_on THEN
113 WSH_DEBUG_SV.push(l_module_name);
114 END IF;
115
116 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
117
118 IF WSH_UTIL_CORE.TP_IS_INSTALLED = 'Y' THEN
119 FTE_TP_GRP.trip_callback(
120 p_api_version_number => p_api_version_number,
121 p_init_msg_list => p_init_msg_list,
122 x_return_status => x_return_status,
123 x_msg_count => x_msg_count,
124 x_msg_data => x_msg_data,
125 p_action_prms => p_action_prms,
126 p_rec_attr_tab => p_rec_attr_tab);
127 END IF;
128
129 IF l_debug_on THEN
130 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
131 WSH_DEBUG_SV.pop(l_module_name);
132 END IF;
133
134 EXCEPTION
135 WHEN OTHERS THEN
136 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
137 WSH_UTIL_CORE.DEFAULT_HANDLER(
138 'WSH_FTE_TP_INTEGRATION.trip_callback',
139 l_module_name);
140 IF l_debug_on THEN
141 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
142 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
143 END IF;
144
145 END trip_callback;
146
147
148
149 --
150 -- Procedure: stop_callback
151 -- Parameters:
152 -- p_api_version_number known api version (1.0)
153 -- p_init_msg_list FND_API.G_TRUE to reset list
154 -- x_return_status return status
155 -- x_msg_count number of messages in the list
156 -- x_msg_data text of messages
157 -- p_actions_prms action parameters record
158 -- used to identify the action triggering
159 -- the callback to FTE.
160 -- p_rec_attr_tab table of stop records to process
161 --
162 -- Description:
163 -- Wrapper for FTE api:
164 -- take care of continuous moves based on the action being performed
165 -- on the stops.
166 --
167
168 PROCEDURE stop_callback (
169 p_api_version_number IN NUMBER,
170 p_init_msg_list IN VARCHAR2,
171 x_return_status OUT NOCOPY VARCHAR2,
172 x_msg_count OUT NOCOPY NUMBER,
173 x_msg_data OUT NOCOPY VARCHAR2,
174 p_action_prms IN WSH_TRIP_STOPS_GRP.action_parameters_rectype,
175 p_rec_attr_tab IN WSH_TRIP_STOPS_PVT.stop_attr_tbl_type)
176 IS
177 --
178 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'stop_callback';
179 --
180 l_debug_on BOOLEAN;
181 --
182 BEGIN
183 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
184 --
185 IF l_debug_on IS NULL THEN
186 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
187 END IF;
188
189 IF l_debug_on THEN
190 WSH_DEBUG_SV.push(l_module_name);
191 END IF;
192
193 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
194
195 IF WSH_UTIL_CORE.TP_IS_INSTALLED = 'Y' THEN
196 FTE_TP_GRP.stop_callback(
197 p_api_version_number => p_api_version_number,
198 p_init_msg_list => p_init_msg_list,
199 x_return_status => x_return_status,
200 x_msg_count => x_msg_count,
201 x_msg_data => x_msg_data,
202 p_action_prms => p_action_prms,
203 p_rec_attr_tab => p_rec_attr_tab);
204 END IF;
205
206 IF l_debug_on THEN
207 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
208 WSH_DEBUG_SV.pop(l_module_name);
209 END IF;
210
211 EXCEPTION
212 WHEN OTHERS THEN
213 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
214 WSH_UTIL_CORE.DEFAULT_HANDLER(
215 'WSH_FTE_TP_INTEGRATION.stop_callback',
216 l_module_name);
217 IF l_debug_on THEN
218 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
219 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
220 END IF;
221
222 END stop_callback;
223
224
225
226 --
227 -- Procedure: map_moves
228 -- Parameters:
229 -- x_context context in this session
230 -- x_plan_trips list of trips mapped to interface trips
231 -- x_plan_trip_moves list of continuous move segments
232 -- x_plan_moves list of continuous moves
233 -- x_obsoleted_trip_moves list of continous move segments that need to be deleted
234 -- x_errors_tab list of errors to insert into wsh_interface_errors at the end
235 -- x_return_status return status
236 --
237 -- Description:
238 -- Wrapper for FTE api:
239 -- Part of TP release process
240 -- Maps the plan's continuous moves: generate and lock candidates
241 -- x_obsoleted_trip_moves will have the obsoleted move segments.
242 --
243
244 PROCEDURE map_moves(
245 x_context IN OUT NOCOPY WSH_TP_RELEASE_INT.context_rec_type,
246 x_plan_trips IN OUT NOCOPY WSH_TP_RELEASE_INT.plan_trip_tab_type,
247 x_plan_trip_moves IN OUT NOCOPY plan_trip_move_tab_type,
248 x_plan_moves IN OUT NOCOPY plan_move_tab_type,
249 x_obsoleted_trip_moves IN OUT NOCOPY obsoleted_trip_move_tab_type,
250 x_errors_tab IN OUT NOCOPY WSH_TP_RELEASE_INT.interface_errors_tab_type,
251 x_return_status OUT NOCOPY VARCHAR2
252 )
253 IS
254 --
255 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'map_moves';
256 --
257 l_debug_on BOOLEAN;
258 --
259 BEGIN
260 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
261 --
262 IF l_debug_on IS NULL THEN
263 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
264 END IF;
265
266 IF l_debug_on THEN
267 WSH_DEBUG_SV.push(l_module_name);
268 END IF;
269
270 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
271
272 IF WSH_UTIL_CORE.TP_IS_INSTALLED = 'Y' THEN
273 FTE_TP_GRP.map_moves(
274 x_context => x_context,
275 x_plan_trips => x_plan_trips,
276 x_plan_trip_moves => x_plan_trip_moves,
277 x_plan_moves => x_plan_moves,
278 x_obsoleted_trip_moves => x_obsoleted_trip_moves,
279 x_errors_tab => x_errors_tab,
280 x_return_status => x_return_status
281 );
282 END IF;
283
284 IF l_debug_on THEN
285 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
286 WSH_DEBUG_SV.pop(l_module_name);
287 END IF;
288
289 EXCEPTION
290 WHEN OTHERS THEN
291 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
292 WSH_UTIL_CORE.DEFAULT_HANDLER(
293 'WSH_FTE_TP_INTEGRATION.map_moves',
294 l_module_name);
295 IF l_debug_on THEN
296 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
297 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
298 END IF;
299
300 END map_moves;
301
302
303
304 --
305 -- Procedure: reconciliate_moves
306 -- Parameters:
307 -- x_context context in this session
308 -- x_plan_trips list of trips mapped to interface trips
309 -- x_plan_trip_moves list of continuous move segments
310 -- x_plan_moves list of continuous moves
311 -- x_obsoleted_trip_moves list of continous move segments that need to be deleted
312 -- x_errors_tab list of errors to insert into wsh_interface_errors at the end
313 -- x_return_status return status
314 --
315 -- Description:
316 -- Wrapper for FTE api:
317 -- Part of TP release process
318 -- Create or update continous moves and their segments, delete obsoleted segments.
319 --
320
321 PROCEDURE reconciliate_moves(
322 x_context IN OUT NOCOPY WSH_TP_RELEASE_INT.context_rec_type,
323 x_plan_trips IN OUT NOCOPY WSH_TP_RELEASE_INT.plan_trip_tab_type,
324 x_plan_trip_moves IN OUT NOCOPY plan_trip_move_tab_type,
325 x_plan_moves IN OUT NOCOPY plan_move_tab_type,
326 x_obsoleted_trip_moves IN OUT NOCOPY obsoleted_trip_move_tab_type,
327 x_errors_tab IN OUT NOCOPY WSH_TP_RELEASE_INT.interface_errors_tab_type,
328 x_return_status OUT NOCOPY VARCHAR2
329 )
330 IS
331 --
332 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'reconciliate_moves';
333 --
334 l_debug_on BOOLEAN;
335 --
336 BEGIN
337 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
338 --
339 IF l_debug_on IS NULL THEN
340 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
341 END IF;
342
343 IF l_debug_on THEN
344 WSH_DEBUG_SV.push(l_module_name);
345 END IF;
346
347 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
348
349 IF WSH_UTIL_CORE.TP_IS_INSTALLED = 'Y' THEN
350 FTE_TP_GRP.reconciliate_moves(
351 x_context => x_context,
352 x_plan_trips => x_plan_trips,
353 x_plan_trip_moves => x_plan_trip_moves,
354 x_plan_moves => x_plan_moves,
355 x_obsoleted_trip_moves => x_obsoleted_trip_moves,
356 x_errors_tab => x_errors_tab,
357 x_return_status => x_return_status
358 );
359 END IF;
360
361 IF l_debug_on THEN
362 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
363 WSH_DEBUG_SV.pop(l_module_name);
364 END IF;
365
366 EXCEPTION
367 WHEN OTHERS THEN
368 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
369 WSH_UTIL_CORE.DEFAULT_HANDLER(
370 'WSH_FTE_TP_INTEGRATION.reconciliate_moves',
371 l_module_name);
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 reconciliate_moves;
378
379
380 --
381 -- Procedure: tp_firm_moves
382 -- Parameters:
383 -- x_context context in this session
384 -- x_plan_trips list of trips mapped to interface trips
385 -- x_plan_trip_moves list of continuous move segments
386 -- x_plan_moves list of continuous moves
387 -- x_obsoleted_trip_moves list of continous move segments that need to be deleted
388 -- x_errors_tab list of errors to insert into wsh_interface_errors at the end
389 -- x_return_status return status
390 --
391 -- Description:
392 -- Wrapper for FTE api:
393 -- Part of TP release process
394 -- Upgrade continuous moves' PLANNED_FLAG based on the plan
395 --
396
397 PROCEDURE tp_firm_moves(
398 x_context IN OUT NOCOPY WSH_TP_RELEASE_INT.context_rec_type,
399 x_plan_moves IN OUT NOCOPY plan_move_tab_type,
400 x_errors_tab IN OUT NOCOPY WSH_TP_RELEASE_INT.interface_errors_tab_type,
401 x_return_status OUT NOCOPY VARCHAR2
402 )
403 IS
404 --
405 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'tp_firm_moves';
406 --
407 l_debug_on BOOLEAN;
408 --
409 BEGIN
410 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
411 --
412 IF l_debug_on IS NULL THEN
413 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
414 END IF;
415
416 IF l_debug_on THEN
417 WSH_DEBUG_SV.push(l_module_name);
418 END IF;
419
420 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
421
422 IF WSH_UTIL_CORE.TP_IS_INSTALLED = 'Y' THEN
423 FTE_TP_GRP.tp_firm_moves(
424 x_context => x_context,
425 x_plan_moves => x_plan_moves,
426 x_errors_tab => x_errors_tab,
427 x_return_status => x_return_status
428 );
429 END IF;
430
431 IF l_debug_on THEN
432 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
433 WSH_DEBUG_SV.pop(l_module_name);
434 END IF;
435
436 EXCEPTION
437 WHEN OTHERS THEN
438 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
439 WSH_UTIL_CORE.DEFAULT_HANDLER(
440 'WSH_FTE_TP_INTEGRATION.tp_firm_moves',
441 l_module_name);
442 IF l_debug_on THEN
443 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
444 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
445 END IF;
446
447 END tp_firm_moves;
448
449
450
451 --
452 -- Procedure: purge_interface_tables
453 -- Parameters:
454 -- p_group_ids list of group_ids to purge
455 -- FTE interface tables (based on WSH_TRIPS_INTERFACE.GROUP_ID)
456 -- x_return_status return status
457 --
458 -- Description:
459 -- Wrapper for FTE api:
460 -- Part of TP release process
461 -- Delete the records from FTE interface tables:
462 -- FTE_MOVES_INTERFACE
463 -- FTE_TRIP_MOVES_INTERFACE
464 --
465 PROCEDURE purge_interface_tables(
466 p_group_ids IN WSH_TP_RELEASE_GRP.ID_TAB_TYPE,
467 x_return_status OUT NOCOPY VARCHAR2)
468 IS
469 --
470 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'purge_interface_tables';
471 --
472 l_debug_on BOOLEAN;
473 --
474 BEGIN
475 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
476 --
477 IF l_debug_on IS NULL THEN
478 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
479 END IF;
480
481 IF l_debug_on THEN
482 WSH_DEBUG_SV.push(l_module_name);
483 END IF;
484
485 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
486
487 IF WSH_UTIL_CORE.TP_IS_INSTALLED = 'Y' THEN
488 FTE_TP_GRP.purge_interface_tables(
489 p_group_ids => p_group_ids,
490 x_return_status => x_return_status
491 );
492 END IF;
493
494 IF l_debug_on THEN
495 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
496 WSH_DEBUG_SV.pop(l_module_name);
497 END IF;
498
499 EXCEPTION
500 WHEN OTHERS THEN
501 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
502 WSH_UTIL_CORE.DEFAULT_HANDLER(
503 'WSH_FTE_TP_INTEGRATION.purge_interface_tables',
504 l_module_name);
505 IF l_debug_on THEN
506 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
507 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
508 END IF;
509
510 END purge_interface_tables;
511
512
513
514
515 END WSH_FTE_TP_INTEGRATION;