[Home] [Help]
PACKAGE BODY: APPS.WSH_TRIP_STOPS_GRP
Source
1 PACKAGE BODY WSH_TRIP_STOPS_GRP as
2 /* $Header: WSHSTGPB.pls 120.6 2007/01/05 19:24:49 parkhj noship $ */
3
4 --===================
5 -- CONSTANTS
6 --===================
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_TRIP_STOPS_GRP';
8 -- add your constants here if any
9
10 -- Forward declaration
11 PROCEDURE Lock_Related_Entity
12 (
13 p_action_prms IN action_parameters_rectype,
14 p_stop_attr_tab IN WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type,
15 x_valid_ids_tab OUT NOCOPY WSH_UTIL_CORE.id_Tab_Type,
16 x_return_status OUT NOCOPY VARCHAR2
17 );
18
19 PROCEDURE Add_to_Delete_List(
20 p_stop_tab IN WSH_TRIP_STOPS_VALIDATIONS.stop_details_tab,
21 p_caller IN VARCHAR2,
22 x_stop_delete_tab OUT NOCOPY wsh_util_core.id_tab_type,
23 x_trip_affected_tab OUT NOCOPY wsh_util_core.id_tab_type,
24 x_return_status OUT NOCOPY VARCHAR2) ;
25 --===================
26 -- PROCEDURES
27 --===================
28
29 --========================================================================
30 -- THIS PROCEDURE WOULD BE OBSOLETE SOON
31 --
32 -- PROCEDURE : Stop_Action_new PUBLIC
33 --
34 -- PARAMETERS: p_api_version_number known api version error number
35 -- p_init_msg_list FND_API.G_TRUE to reset list
36 -- x_return_status return status
37 -- x_msg_count number of messages in the list
38 -- x_msg_data text of messages
39 -- p_stop_info Attributes for the stop entity
40 -- p_stop_IN_rec Input Attributes for the stop entity
41 -- p_stop_OUT_rec Output Attributes for the stop entity
42 -- p_action_code Stop action code. Valid action codes are
43 -- 'PLAN','UNPLAN',
44 -- 'ARRIVE','CLOSE'
45 -- 'PICK-RELEASE'
46 -- 'DELETE'
47 -- VERSION : current version 1.0
48 -- initial version 1.0
49 -- COMMENT : This procedure is used to perform an action specified in p_action_code
50 -- on an existing stop identified by p_stop_id or a unique combination of
51 -- trip_id/trip_name, stop_location_id/stop_location_code or planned_departure_date.These are part of p_stop_info
52 --
53 --========================================================================
54
55 PROCEDURE Stop_Action_New
56 ( p_api_version_number IN NUMBER,
57 p_init_msg_list IN VARCHAR2,
58 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
59 x_return_status OUT NOCOPY VARCHAR2,
60 x_msg_count OUT NOCOPY NUMBER,
61 x_msg_data OUT NOCOPY VARCHAR2,
62 p_stop_info IN OUT NOCOPY WSH_TRIP_STOPS_GRP.Trip_Stop_Pub_Rec_Type,
63 p_stop_IN_rec IN stopActionInRecType,
64 x_stop_OUT_rec OUT NOCOPY stopActionOutRecType) IS
65
66
67 l_api_version_number CONSTANT NUMBER := 1.0;
68 l_api_name CONSTANT VARCHAR2(30):= 'Stop_Action';
69 l_stop_id NUMBER := p_stop_info.stop_id;
70 l_trip_id NUMBER := p_stop_info.trip_id;
71 l_stop_location_id NUMBER := p_stop_info.stop_location_id;
72 l_return_status varchar2(1);
73 l_num_errors NUMBER;
74 l_num_warning NUMBER;
75
76
77 -- <insert here your local variables declaration>
78
79 l_stop_rows wsh_util_core.id_tab_type;
80 l_action_prms WSH_TRIP_STOPS_GRP.action_parameters_rectype;
81
82 l_encoded VARCHAR2(1) := 'F';
83
84 --
85 l_debug_on BOOLEAN;
86 --
87 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'STOP_ACTION_NEW';
88 --
89 BEGIN
90
91 --
92 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
93 --
94 IF l_debug_on IS NULL
95 THEN
96 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
97 END IF;
98 --
99 IF l_debug_on THEN
100 wsh_debug_sv.push(l_module_name);
101 WSH_DEBUG_SV.log(l_module_name,'action_code',p_stop_IN_rec.action_code);
102 WSH_DEBUG_SV.log(l_module_name,'p_init_msg_list',p_init_msg_list );
103 WSH_DEBUG_SV.log(l_module_name,'p_commit',p_commit);
104 WSH_DEBUG_SV.log(l_module_name,'trip_id', p_stop_info.trip_id);
105 WSH_DEBUG_SV.log(l_module_name,'trip_id', p_stop_info.trip_id);
106 WSH_DEBUG_SV.log(l_module_name,'stop_location_id', p_stop_info.stop_location_id);
107 WSH_DEBUG_SV.log(l_module_name,'planned_arrival_date', p_stop_info.planned_arrival_date);
108 WSH_DEBUG_SV.log(l_module_name,'planned_departure_date', p_stop_info.planned_departure_date);
109 END IF;
110
111 -- Standard call to check for call compatibility
112 IF NOT FND_API.Compatible_API_Call
113 ( l_api_version_number
114 , p_api_version_number
115 , l_api_name
116 , G_PKG_NAME
117 )
118 THEN
119 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
120 END IF;
121
122 -- Initialize message stack if required
123 IF FND_API.to_Boolean(p_init_msg_list) THEN
124 FND_MSG_PUB.initialize;
125 END IF;
126
127 wsh_util_validate.validate_trip_name(
128 l_trip_id,
129 p_stop_info.trip_name,
130 l_return_status);
131
132 wsh_util_core.api_post_call(p_return_status =>l_return_status,
133 x_num_warnings =>l_num_warning,
134 x_num_errors =>l_num_errors);
135
136 -- Stop location id can be -1 for inbound stops
137 -- in such case, we do not need to call validate location, as it wil fail
138 -- in turn preventing any action on stops
139 --
140 IF l_stop_location_id <> WSH_UTIL_CORE.C_NULL_SF_LOCN_ID -- J-IB-NPARIKH
141 OR l_stop_location_id IS NULL
142 THEN
143 wsh_util_validate.validate_location(
144 l_stop_location_id,
145 p_stop_info.stop_location_code,
146 l_return_status);
147
148 wsh_util_core.api_post_call(p_return_status =>l_return_status,
149 x_num_warnings =>l_num_warning,
150 x_num_errors =>l_num_errors);
151 END IF;
152
153 wsh_util_validate.validate_stop_name(
154 l_stop_id,
155 l_trip_id,
156 l_stop_location_id,
157 p_stop_info.planned_departure_date,
158 l_return_status);
159 IF l_debug_on THEN
160 WSH_DEBUG_SV.log(l_module_name,'l_stop_id', l_stop_id);
161 END IF;
162
163 wsh_util_core.api_post_call(p_return_status =>l_return_status,
164 x_num_warnings =>l_num_warning,
165 x_num_errors =>l_num_errors);
166
167 IF (l_stop_id IS NULL) THEN
168 raise FND_API.G_EXC_ERROR;
169 END IF;
170
171 l_stop_rows(1) := p_stop_info.stop_id;
172 l_action_prms.caller := 'WSH_API';
173 l_action_prms.action_code := p_stop_IN_rec.action_code;
174 --l_action_prms.status_code := p_stop_info.STATUS_CODE;
175 l_action_prms.actual_date := p_stop_IN_rec.actual_date;
176 l_action_prms.defer_interface_flag := p_stop_IN_rec.defer_interface_flag;
177
178 wsh_interface_grp.stop_action(
179 p_api_version_number => p_api_version_number,
180 p_init_msg_list => FND_API.G_FALSE,
181 p_commit => p_commit,
182 p_entity_id_tab => l_stop_rows,
183 p_action_prms => l_action_prms,
184 x_stop_out_rec => x_stop_OUT_rec,
185 x_return_status => x_return_status,
186 x_msg_count => x_msg_count,
187 x_msg_data => x_msg_data);
188
189 FND_MSG_PUB.Count_And_Get
190 ( p_encoded => l_encoded
191 , p_count => x_msg_count
192 , p_data => x_msg_data
193 );
194 IF l_debug_on THEN
195 WSH_DEBUG_SV.pop(l_module_name);
196 END IF;
197 EXCEPTION
198
199 WHEN FND_API.G_EXC_ERROR THEN
200 x_return_status := FND_API.G_RET_STS_ERROR;
201 FND_MESSAGE.SET_NAME('WSH','WSH_OI_STOP_ACTION_ERROR');
202 FND_MESSAGE.SET_TOKEN('STOP_NAME', wsh_trip_stops_pvt.get_name(l_stop_id));
203 FND_MESSAGE.SET_TOKEN('ACTION', wsh_util_core.get_action_meaning('STOP',p_stop_IN_rec.action_code));
204 wsh_util_core.add_message(x_return_status,l_module_name);
205 FND_MSG_PUB.Count_And_Get
206 ( p_encoded => l_encoded
207 , p_count => x_msg_count
208 , p_data => x_msg_data
209 );
210 IF l_debug_on THEN
211 WSH_DEBUG_SV.log(l_module_name,'l_stop_id', l_stop_id);
212 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:G_EXC_ERROR ');
213 END IF;
214
215
216 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
217 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
218 FND_MESSAGE.SET_NAME('WSH','WSH_OI_STOP_ACTION_ERROR');
219 FND_MESSAGE.SET_TOKEN('STOP_NAME', wsh_trip_stops_pvt.get_name(l_stop_id));
220 FND_MESSAGE.SET_TOKEN('ACTION', wsh_util_core.get_action_meaning('STOP',p_stop_IN_rec.action_code));
221 wsh_util_core.add_message(x_return_status,l_module_name);
222 -- Get message count and data
223 FND_MSG_PUB.Count_And_Get
224 ( p_encoded => l_encoded
225 , p_count => x_msg_count
226 , p_data => x_msg_data
227 );
228 IF l_debug_on THEN
229 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
230 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
231 END IF;
232
233 WHEN OTHERS THEN
234 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
235 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
236 THEN
237 FND_MSG_PUB.Add_Exc_Msg
238 ( G_PKG_NAME
239 , '_x_'
240 );
241 END IF;
242 -- Get message count and data
243 FND_MSG_PUB.Count_And_Get
244 ( p_encoded => l_encoded
245 , p_count => x_msg_count
246 , p_data => x_msg_data
247 );
248 IF l_debug_on THEN
249 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
250 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
251 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
252 END IF;
253 END Stop_Action_New;
254
255
256 --========================================================================
257 -- PROCEDURE : Stop_Action PUBLIC
258 -- THIS PROCEDURE WOULD BE OBSOLETE SOON
259 --
260 -- PARAMETERS: p_api_version_number known api version error number
261 -- p_init_msg_list FND_API.G_TRUE to reset list
262 -- x_return_status return status
263 -- x_msg_count number of messages in the list
264 -- x_msg_data text of messages
265 -- p_action_code Stop action code. Valid action codes are
266 -- 'PLAN','UNPLAN',
267 -- 'ARRIVE','CLOSE'
268 -- 'PICK-RELEASE'
269 -- 'DELETE'
270 -- p_stop_id Stop identifier
271 -- p_trip_id Stop identifier - trip id it belongs to
272 -- p_trip_name Stop identifier - trip name it belongs to
273 -- p_stop_location_id Stop identifier - stop location id
274 -- p_stop_location_code Stop identifier - stop location code
275 -- p_planned_dep_date Stop identifier - stop planned dep date
276 -- p_actual_date Actual arrival/departure date of the stop
277 -- VERSION : current version 1.0
278 -- initial version 1.0
279 -- COMMENT : This procedure is used to perform an action specified in p_action_code
280 -- on an existing stop identified by p_stop_id or a unique combination of
281 -- trip_id/trip_name, stop_location_id/stop_location_code or planned_departure_date.
282 --
283 --========================================================================
284
285 PROCEDURE Stop_Action
286 ( p_api_version_number IN NUMBER,
287 p_init_msg_list IN VARCHAR2,
288 x_return_status OUT NOCOPY VARCHAR2,
289 x_msg_count OUT NOCOPY NUMBER,
290 x_msg_data OUT NOCOPY VARCHAR2,
291 p_action_code IN VARCHAR2,
292 p_stop_id IN NUMBER DEFAULT NULL,
293 p_trip_id IN NUMBER DEFAULT NULL,
294 p_trip_name IN VARCHAR2 DEFAULT NULL,
295 p_stop_location_id IN NUMBER DEFAULT NULL,
296 p_stop_location_code IN VARCHAR2 DEFAULT NULL,
297 p_planned_dep_date IN DATE DEFAULT NULL,
298 p_actual_date IN DATE DEFAULT NULL,
299 p_defer_interface_flag IN VARCHAR2 DEFAULT 'Y') IS
300
301 l_api_version_number CONSTANT NUMBER := 1.0;
302 l_api_name CONSTANT VARCHAR2(30):= 'Stop_Action';
303
304 -- <insert here your local variables declaration>
305
306 l_stop_rows wsh_util_core.id_tab_type;
307
308
309 l_stop_id NUMBER := p_stop_id;
310 l_trip_id NUMBER := p_trip_id;
311 l_stop_location_id NUMBER := p_stop_location_id;
312
313 l_return_status VARCHAR2(1);
314 l_num_errors NUMBER;
315 l_num_warning NUMBER;
316
317 l_stop_OUT_rec stopActionOutRecType;
318 l_action_prms WSH_TRIP_STOPS_GRP.action_parameters_rectype;
319
320 l_encoded VARCHAR2(1) := 'F';
321
322 --
323 l_debug_on BOOLEAN;
324 --
325 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'STOP_ACTION';
326 --
327 BEGIN
328 --
329 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
330 --
331 IF l_debug_on IS NULL
332 THEN
333 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
334 END IF;
335 --
336 IF l_debug_on THEN
337 wsh_debug_sv.push (l_module_name);
338 wsh_debug_sv.log (l_module_name,'p_action_code',p_action_code);
339 wsh_debug_sv.log (l_module_name,'p_stop_id',p_stop_id);
340 wsh_debug_sv.log (l_module_name,'p_trip_id',p_trip_id);
341 wsh_debug_sv.log (l_module_name,'p_trip_name',p_trip_name);
342 wsh_debug_sv.log (l_module_name,'p_stop_location_id',p_stop_location_id);
343 wsh_debug_sv.log (l_module_name,'p_stop_location_code',p_stop_location_code);
344 wsh_debug_sv.log (l_module_name,'p_planned_dep_date',p_planned_dep_date);
345 wsh_debug_sv.log (l_module_name,'p_actual_date',p_actual_date);
346 wsh_debug_sv.log (l_module_name,'p_defer_interface_flag',p_defer_interface_flag);
347 END IF;
348
349 -- Standard call to check for call compatibility
350 IF NOT FND_API.Compatible_API_Call
351 ( l_api_version_number
352 , p_api_version_number
353 , l_api_name
354 , G_PKG_NAME
355 )
356 THEN
357 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
358 END IF;
359
360 -- Initialize message stack if required
361 IF FND_API.to_Boolean(p_init_msg_list) THEN
362 FND_MSG_PUB.initialize;
363 END IF;
364
365 wsh_util_validate.validate_trip_name(
366 l_trip_id,
367 p_trip_name,
368 l_return_status);
369
370 wsh_util_core.api_post_call(p_return_status =>l_return_status,
371 x_num_warnings =>l_num_warning,
372 x_num_errors =>l_num_errors);
373
374 -- Stop location id can be -1 for inbound stops
375 -- in such case, we do not need to call validate location, as it wil fail
376 -- in turn preventing any action on stops
377 --
378 IF l_stop_location_id <> WSH_UTIL_CORE.C_NULL_SF_LOCN_ID -- J-IB-NPARIKH
379 OR l_stop_location_id IS NULL
380 THEN
381 wsh_util_validate.validate_location(
382 l_stop_location_id,
383 p_stop_location_code,
384 l_return_status);
385
386 wsh_util_core.api_post_call(p_return_status =>l_return_status,
387 x_num_warnings =>l_num_warning,
388 x_num_errors =>l_num_errors);
389 END IF;
390
391 wsh_util_validate.validate_stop_name(
392 l_stop_id,
393 l_trip_id,
394 l_stop_location_id,
395 p_planned_dep_date,
396 l_return_status);
397 IF l_debug_on THEN
398 WSH_DEBUG_SV.log(l_module_name,'l_stop_id', l_stop_id);
399 END IF;
400
401 wsh_util_core.api_post_call(p_return_status =>l_return_status,
402 x_num_warnings =>l_num_warning,
403 x_num_errors =>l_num_errors);
404
405 IF (l_stop_id IS NULL) THEN
406 raise FND_API.G_EXC_ERROR;
407 END IF;
408 l_stop_rows(1) := p_stop_id;
409 l_action_prms.caller := 'WSH_API';
410 l_action_prms.action_code := p_action_code;
411 l_action_prms.actual_date := p_actual_date;
412 l_action_prms.defer_interface_flag := p_defer_interface_flag;
413
414 wsh_interface_grp.stop_action(
415 p_api_version_number => p_api_version_number,
416 p_init_msg_list => FND_API.G_FALSE,
417 p_commit => FND_API.G_TRUE,
418 p_entity_id_tab => l_stop_rows,
419 p_action_prms => l_action_prms,
420 x_stop_out_rec => l_stop_OUT_rec,
421 x_return_status => x_return_status,
422 x_msg_count => x_msg_count,
423 x_msg_data => x_msg_data);
424
425
426 FND_MSG_PUB.Count_And_Get
427 ( p_encoded => l_encoded
428 , p_count => x_msg_count
429 , p_data => x_msg_data
430 );
431 IF l_debug_on THEN
432 wsh_debug_sv.pop (l_module_name);
433 END IF;
434 EXCEPTION
435
436 WHEN FND_API.G_EXC_ERROR THEN
437 x_return_status := FND_API.G_RET_STS_ERROR;
438 -- Get message count and data
439 FND_MESSAGE.SET_NAME('WSH','WSH_OI_STOP_ACTION_ERROR');
440 FND_MESSAGE.SET_TOKEN('STOP_NAME', wsh_trip_stops_pvt.get_name(l_stop_id));
441 FND_MESSAGE.SET_TOKEN('ACTION', wsh_util_core.get_action_meaning('STOP',p_action_code));
442 wsh_util_core.add_message(x_return_status,l_module_name);
443 FND_MSG_PUB.Count_And_Get
444 ( p_encoded => l_encoded
445 , p_count => x_msg_count
446 , p_data => x_msg_data
447 );
448 IF l_debug_on THEN
449 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
450 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
451 END IF;
452
453 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
454 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
455 FND_MESSAGE.SET_NAME('WSH','WSH_OI_STOP_ACTION_ERROR');
456 FND_MESSAGE.SET_TOKEN('STOP_NAME', wsh_trip_stops_pvt.get_name(l_stop_id));
457 FND_MESSAGE.SET_TOKEN('ACTION', wsh_util_core.get_action_meaning('STOP',p_action_code));
458 wsh_util_core.add_message(x_return_status,l_module_name);
459 -- Get message count and data
460 FND_MSG_PUB.Count_And_Get
461 ( p_encoded => l_encoded
462 , p_count => x_msg_count
463 , p_data => x_msg_data
464 );
465 IF l_debug_on THEN
466 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
467 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
468 END IF;
469
470 WHEN OTHERS THEN
471 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
472 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
473 THEN
474 FND_MSG_PUB.Add_Exc_Msg
475 ( G_PKG_NAME
476 , '_x_'
477 );
478 END IF;
479 -- Get message count and data
480 FND_MSG_PUB.Count_And_Get
481 ( p_encoded => l_encoded
482 , p_count => x_msg_count
483 , p_data => x_msg_data
484 );
485 IF l_debug_on THEN
486 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,
487 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
488 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
489 END IF;
490
491 END Stop_Action;
492
493 /*-----------------------------------------------
494
495 PROCEDURE Stop_Action
496 This is the internal group API
497
498 -----------------------------------------------*/
499
500 PROCEDURE Stop_Action
501 ( p_api_version_number IN NUMBER,
502 p_init_msg_list IN VARCHAR2,
503 p_commit IN VARCHAR2,
504 p_action_prms IN action_parameters_rectype,
505 p_rec_attr_tab IN WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type,
506 x_stop_out_rec OUT NOCOPY stopActionOutRecType,
507 x_def_rec OUT NOCOPY default_parameters_rectype,
508 x_return_status OUT NOCOPY VARCHAR2,
509 x_msg_count OUT NOCOPY NUMBER,
510 x_msg_data OUT NOCOPY VARCHAR2)
511
512 IS
513 l_stop_id_tab wsh_util_core.id_tab_type;
514 -- for load tender add l_trip_id_tab
515 l_trip_id_tab wsh_util_core.id_tab_type;
516 l_error_ids wsh_util_core.id_tab_type;
517 l_dummy_ids wsh_util_core.id_tab_type;
518 l_dummy_doc_param WSH_DOCUMENT_SETS.DOCUMENT_SET_TAB_TYPE;
519 l_num NUMBER;
520 l_stop_rec_tab wsh_trip_stops_validations.stop_rec_tab_type;
521 l_first NUMBER;
522 l_next NUMBER;
523 l_last NUMBER;
524 l_stop_id NUMBER;
525 l_is_installed VARCHAR2(10);
526 l_valid_index_tab wsh_util_core.id_tab_type;
527 l_valid_id_tab wsh_util_core.id_tab_type;
528 l_valid_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
529 l_num_warning NUMBER := 0;
530 l_num_errors NUMBER := 0;
531 l_return_status VARCHAR2(500);
532 l_trip_rec WSH_TRIPS_PVT.trip_rec_type;
533 l_stop_rec WSH_TRIP_STOPS_PVT.trip_stop_rec_type;
534 l_api_version_number CONSTANT NUMBER := 1.0;
535 l_api_name CONSTANT VARCHAR2(30):= 'Stop_Action';
536 l_counter NUMBER;
537 l_report_set_id NUMBER := p_action_prms.report_set_id;
538 l_status_code VARCHAR2(50) := 'OP' ;
539
540 l_debug_on BOOLEAN;
541 --
542 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.'
543 || 'STOP_ACTION';
544 l_isWshLocation BOOLEAN DEFAULT FALSE;
545 e_req_field EXCEPTION;
546 --Bugfix 4070732
547 l_api_session_name CONSTANT VARCHAR2(150) := G_PKG_NAME ||'.' || l_module_name;
548 l_reset_flags BOOLEAN;
549
550
551 --Compatibility Changes
552 l_cc_validate_result VARCHAR2(1);
553 l_cc_failed_records WSH_FTE_COMP_CONSTRAINT_PKG.failed_line_tab_type;
554 l_cc_line_groups WSH_FTE_COMP_CONSTRAINT_PKG.line_group_tab_type;
555 l_cc_group_info WSH_FTE_COMP_CONSTRAINT_PKG.cc_group_tab_type;
556 b_cc_linefailed boolean;
557 l_stop_id_tab_t wsh_util_core.id_tab_type;
558 l_cc_stop_id_tab wsh_util_core.id_tab_type;
559 l_cc_count_success NUMBER;
560 l_cc_del_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
561 l_cc_det_attr_tab wsh_glbl_var_strct_grp.Delivery_Details_Attr_Tbl_Type;
562 l_cc_trip_attr_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
563 l_cc_stop_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
564 l_cc_in_ids wsh_util_core.id_tab_type;
565 l_cc_fail_ids wsh_util_core.id_tab_type;
566 l_msg_count NUMBER;
567 l_msg_data VARCHAR2(32767);
568 --Compatibility Changes
569 l_stop_delete_tab wsh_util_core.id_tab_type;
570 l_trip_affected_tab wsh_util_core.id_tab_type;
571 l_success_trip_tab wsh_util_core.id_tab_type;
572 l_delete_tmp_tab WSH_TRIP_STOPS_VALIDATIONS.stop_details_tab;
573 l_delete_t WSH_TRIP_STOPS_VALIDATIONS.stop_details_tab;
574
575 CURSOR c_check_trip_close (p_trip_id IN NUMBER) IS
576 SELECT trip_id
577 FROM WSH_TRIPS
578 WHERE trip_id=p_trip_id
579 AND status_code='IT'
580 AND NOT EXISTS (select 'x'
581 from wsh_trip_stops
582 where trip_id=p_trip_id
583 and status_code IN ('OP', 'AR')
584 and rownum=1);
585 l_trip_in_rec WSH_TRIP_VALIDATIONS.ChgStatus_in_rec_type;
586
587 -- K LPN CONV. rv
588 l_lpn_in_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_in_rec_type;
589 l_lpn_out_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_out_rec_type;
590 -- K LPN CONV. rv
591
592 l_actual_date Date;
593
594 -- OTM R12 : packing ECO
595 l_gc3_is_installed VARCHAR2(1);
596 -- End of OTM R12 : packing ECO
597
598
599 BEGIN
600 IF WSH_UTIL_CORE.G_START_OF_SESSION_API is null THEN --Bugfix 4070732
601 WSH_UTIL_CORE.G_START_OF_SESSION_API := l_api_session_name;
602 WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API := FALSE;
603 END IF;
604 --
605 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
606 --
607 IF l_debug_on IS NULL
608 THEN
609 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
610 END IF;
611 --
612 SAVEPOINT s_stop_action_grp;
613 IF l_debug_on THEN
614 wsh_debug_sv.push (l_module_name);
615 wsh_debug_sv.log (l_module_name,'p_api_version_number',
616 p_api_version_number);
617 wsh_debug_sv.log (l_module_name,'p_init_msg_list',p_init_msg_list);
618 wsh_debug_sv.log (l_module_name,'p_commit',p_commit);
619 wsh_debug_sv.log (l_module_name,'action_code',p_action_prms.action_code);
620 wsh_debug_sv.log (l_module_name,'caller',p_action_prms.caller);
621 wsh_debug_sv.log (l_module_name,'COUNT',p_rec_attr_tab.COUNT);
622 END IF;
623
624 IF FND_API.to_Boolean(p_init_msg_list) THEN
625 FND_MSG_PUB.initialize;
626 END IF;
627
628 IF NOT FND_API.Compatible_API_Call
629 ( l_api_version_number
630 , p_api_version_number
631 , l_api_name
632 , G_PKG_NAME
633 )
634 THEN
635 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
636 END IF;
637
638
639 IF p_action_prms.action_code IS NULL THEN
640 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
641 FND_MESSAGE.SET_TOKEN('FIELD_NAME','p_action_prms.action_code');
642 RAISE e_req_field;
643 ELSIF p_action_prms.caller IS NULL THEN
644 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
645 FND_MESSAGE.SET_TOKEN('FIELD_NAME','p_action_prms.caller');
646 RAISE e_req_field;
647 ELSIF p_rec_attr_tab.COUNT = 0 THEN
648 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
649 FND_MESSAGE.SET_TOKEN('FIELD_NAME','p_rec_attr_tab.COUNT');
650 RAISE e_req_field;
651 END IF;
652 --
653 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
654
655 IF p_action_prms.caller IN ( 'WSH_FSTRX', 'WSH_TPW_INBOUND')
656 OR p_action_prms.caller LIKE 'FTE%' THEN
657 l_isWshLocation := TRUE;
658 END IF;
659
660 l_next := p_rec_attr_tab.FIRST;
661
662 WHILE l_next IS NOT NULL LOOP
663 l_stop_id_tab(l_next) := p_rec_attr_tab(l_next).stop_id;
664 l_stop_rec_tab(l_next).stop_id := p_rec_attr_tab(l_next).stop_id;
665 l_stop_rec_tab(l_next).status_code := p_rec_attr_tab(l_next).status_code;
666 -- J inbound logistics. populate new column shipments_type_flag jckwok
667 l_stop_rec_tab(l_next).shipments_type_flag := p_rec_attr_tab(l_next).shipments_type_flag;
668 -- csun 10+ internal location change
669 l_delete_tmp_tab(l_next).stop_id := p_rec_attr_tab(l_next).stop_id;
670 l_delete_tmp_tab(l_next).trip_id := p_rec_attr_tab(l_next).trip_id;
671
672 /* If one of the lines has status code of 'AR' then this is going to be
673 used for defaulting, else if there are lines with status code of 'OP' Then
674 Use thar for defaulting
675 bsadri
676 */
677 IF l_status_code <> 'AR' THEN
678 --
679 IF p_rec_attr_tab(l_next).status_code = 'AR' OR
680 p_rec_attr_tab(l_next).status_code = 'OP' THEN
681 --
682 l_status_code := p_rec_attr_tab(l_next).status_code;
683 --
684 END IF;
685 --
686 END IF;
687
688 IF l_debug_on THEN
689 wsh_debug_sv.log (l_module_name,'stop_id',
690 p_rec_attr_tab(l_next).stop_id);
691 wsh_debug_sv.log (l_module_name,'status_code',
692 p_rec_attr_tab(l_next).status_code);
693 END IF;
694 l_next := p_rec_attr_tab.NEXT(l_next);
695 END LOOP;
696
697
698 WSH_ACTIONS_LEVELS.set_validation_level (
699 p_entity => 'STOP',
700 p_caller => p_action_prms.caller,
701 p_phase => p_action_prms.phase,
702 p_action =>p_action_prms.action_code ,
703 x_return_status => l_return_status);
704
705
706 wsh_util_core.api_post_call(p_return_status =>l_return_status,
707 x_num_warnings =>l_num_warning,
708 x_num_errors =>l_num_errors);
709
710 IF WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_STOP_NAME_LVL) = 1 THEN
711 -- validate the stops are eligible for action.
712 l_next := p_rec_attr_tab.FIRST;
713 --
714 WHILE l_next IS NOT NULL LOOP
715 l_stop_id := p_rec_attr_tab(l_next).stop_id;
716 WSH_UTIL_VALIDATE.Validate_Stop_Name (
717 p_stop_id => l_stop_id,
718 p_trip_id => p_rec_attr_tab(l_next).trip_id,
719 p_stop_location_id => NULL, -- not needed
720 p_planned_dep_date => NULL, -- not needed
721 x_return_status => l_return_status);
722 IF l_debug_on THEN
723 WSH_DEBUG_SV.log(l_module_name,'WSH_UTIL_VALIDATE.Validate_Stop_Name l_return_status',l_return_status);
724 END IF;
725 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
726 x_num_warnings =>l_num_warning,
727 x_num_errors =>l_num_errors);
728
729 l_next := p_rec_attr_tab.NEXT(l_next);
730 END LOOP;
731 --
732 END IF;
733
734 IF WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_ACTION_ENABLED_LVL) = 1 THEN
735 WSH_TRIP_STOPS_validations.Is_Action_Enabled(
736 p_stop_rec_tab => l_stop_rec_tab,
737 p_action => p_action_prms.action_code,
738 p_caller => p_action_prms.caller,
739 x_return_status => l_return_status,
740 x_valid_ids => l_valid_id_tab ,
741 x_error_ids => l_error_ids ,
742 x_valid_index_tab => l_valid_index_tab);
743 wsh_util_core.api_post_call(p_return_status =>l_return_status,
744 x_num_warnings =>l_num_warning,
745 x_num_errors =>l_num_errors,
746 p_msg_data => NULL,
747 p_raise_error_flag => FALSE);
748
749 END IF;
750 IF WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_LOCK_RECORDS_LVL) = 1 THEN
751 IF NOT ( p_action_prms.caller = 'WSH_FSTRX'
752 AND p_action_prms.action_code = 'DELETE'
753 ) THEN --BUG 4354579
754 WSH_TRIP_STOPS_PVT.Lock_Trip_Stop(
755 p_rec_attr_tab=>p_rec_attr_tab,
756 p_caller=>p_action_prms.caller,
757 p_valid_index_tab =>l_valid_index_tab,
758 x_valid_ids_tab =>x_stop_out_rec.valid_ids_tab,
759 x_return_status=>l_return_status);
760
761 wsh_util_core.api_post_call(p_return_status =>l_return_status,
762 x_num_warnings =>l_num_warning,
763 x_num_errors =>l_num_errors,
764 p_msg_data => NULL,
765 p_raise_error_flag => FALSE);
766 END IF;
767 END IF;
768
769
770
771 IF(l_num_errors >0 ) THEN
772 --{
773 x_return_status := wsh_util_core.g_ret_sts_error;
774 --
775 IF (p_action_prms.caller = 'WSH_FSTRX') THEN
776 FND_MESSAGE.SET_NAME('WSH', 'WSH_DISABLE_ACTION');
777 wsh_util_core.add_message(x_return_status,l_module_name);
778 END IF;
779 --
780 IF l_debug_on THEN
781 wsh_debug_sv.logmsg(l_module_name, 'WSH_DISABLE_ACTION');
782 END IF;
783 --
784 RAISE FND_API.G_EXC_ERROR;
785 --}
786 END IF;
787
788 IF l_debug_on THEN
789 wsh_debug_sv.log (l_module_name,'l_num_warning',l_num_warning);
790 END IF;
791
792
793 IF l_num_warning > 0 AND p_action_prms.caller = 'WSH_FSTRX' THEN
794 x_stop_out_rec.selection_issue_flag := 'Y';
795 END IF;
796
797 IF l_debug_on THEN
798 wsh_debug_sv.log (l_module_name,'C_STOP_DEFAULTS_LVL', WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_STOP_DEFAULTS_LVL));
799 END IF;
800
801 IF WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_STOP_DEFAULTS_LVL)=1 THEN
802 IF l_status_code = 'OP' THEN
803 x_def_rec.date_field := 'STOP.ACTUAL_ARRIVAL_DATE';
804 x_def_rec.status_code := 'AR';
805 x_def_rec.status_name := 'Arrived';
806 x_def_rec.stop_action := 'ARRIVE';
807 IF l_num_warning > 0 THEN
808 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
809 ELSE
810 --Bugfix 4070732 { Phase 1 just call the rest API
811 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
812 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
813 IF l_debug_on THEN
814 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
815 END IF;
816
817 WSH_UTIL_CORE.reset_stops_for_load_tender(
818 p_reset_flags => TRUE,
819 x_return_status => x_return_status);
820
821 IF l_debug_on THEN
822 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
823 END IF;
824 END IF;
825 END IF;
826 --}
827 IF l_debug_on THEN
828 WSH_DEBUG_SV.pop(l_module_name);
829 END IF;
830 RETURN;
831 END IF;
832 ELSIF l_status_code = 'AR' THEN
833 x_def_rec.date_field := 'STOP.ACTUAL_DEPARTURE_DATE';
834 x_def_rec.status_code := 'CL';
835 x_def_rec.status_name := 'Closed';
836 x_def_rec.stop_action := 'CLOSE';
837 IF l_num_warning > 0 THEN
838 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
839 ELSE
840 --Bugfix 4070732 { Phase one just call the reset API
841 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
842 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
843 IF l_debug_on THEN
844 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
845 END IF;
846 WSH_UTIL_CORE.reset_stops_for_load_tender(
847 p_reset_flags => TRUE,
848 x_return_status => x_return_status);
849
850 IF l_debug_on THEN
851 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
852 END IF;
853
854 END IF;
855 END IF;
856 --}
857 IF l_debug_on THEN
858 WSH_DEBUG_SV.pop(l_module_name);
859 END IF;
860 RETURN;
861 END IF;
862 END IF;
863 END IF;
864
865 IF l_num_warning > 0 THEN
866 --{
867 x_return_status := wsh_util_core.g_ret_sts_warning;
868 --
869 FND_MESSAGE.SET_NAME('WSH', 'WSH_DISABLE_ACTION_WARN');
870 wsh_util_core.add_message(x_return_status,l_module_name);
871 --
872 IF l_debug_on THEN
873 wsh_debug_sv.logmsg(l_module_name, 'WSH_DISABLE_ACTION_WARN');
874 END IF;
875 --
876 IF p_action_prms.caller = 'WSH_FSTRX' THEN
877 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
878 ELSE
879 RAISE FND_API.G_EXC_ERROR;
880 END IF;
881 --}
882 END IF;
883
884 l_stop_id := p_rec_attr_tab(p_rec_attr_tab.first).stop_id;
885
886 IF p_action_prms.action_code IN ('UPDATE-STATUS', 'PRINT-DOC-SETS')
887 AND nvl(p_action_prms.phase,1) = 1
888 AND p_action_prms.caller = 'WSH_FSTRX'
889 THEN
890 --Bugfix 4070732 { Phase 1 need to call the reset API only
891 l_return_status := NULL;
892 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
893 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
894 IF l_debug_on THEN
895 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
896 END IF;
897 WSH_UTIL_CORE.reset_stops_for_load_tender(
898 p_reset_flags => TRUE,
899 x_return_status => x_return_status);
900
901 IF l_debug_on THEN
902 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
903 END IF;
904
905 END IF;
906 END IF;
907 --}
908 x_return_status := NVL(l_return_status, WSH_UTIL_CORE.G_RET_STS_SUCCESS);
909 RETURN; -- Non-Generic Actions.
910 END IF;
911
912
913 IF WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_VALIDATE_CONSTRAINTS_LVL) = 1 THEN --{
914 --Compatiblity Changes
915 --for autocreatetrip or if assign trip and caller is STF, phase=2
916
917
918
919 IF wsh_util_core.fte_is_installed='Y' THEN
920
921 WSH_FTE_COMP_CONSTRAINT_PKG.validate_constraint_main(
922 p_api_version_number => p_api_version_number,
923 p_init_msg_list => FND_API.G_FALSE,
924 p_entity_type => 'S',
925 p_target_id => null,
926 p_action_code => p_action_prms.action_code,
927 p_del_attr_tab => l_cc_del_attr_tab,
928 p_det_attr_tab => l_cc_det_attr_tab,
929 p_trip_attr_tab => l_cc_trip_attr_tab,
930 p_stop_attr_tab => p_rec_attr_tab,
931 p_in_ids => l_cc_stop_id_tab,
932 x_fail_ids => l_cc_fail_ids,
933 x_validate_result => l_cc_validate_result,
934 x_failed_lines => l_cc_failed_records,
935 x_line_groups => l_cc_line_groups,
936 x_group_info => l_cc_group_info,
937 x_msg_count => l_msg_count,
938 x_msg_data => l_msg_data,
939 x_return_status => l_return_status);
940
941
942 IF l_debug_on THEN
943 wsh_debug_sv.log(l_module_name,'Return Status After Calling validate_constraint_main',l_return_status);
944 wsh_debug_sv.log(l_module_name,'validate_result After Calling validate_constraint_main',l_cc_validate_result);
945 wsh_debug_sv.log(l_module_name,'msg_count After Calling validate_constraint_main',l_msg_count);
946 wsh_debug_sv.log(l_module_name,'msg_data After Calling validate_constraint_main',l_msg_data);
947 wsh_debug_sv.log(l_module_name,'fail_ids count After Calling validate_constraint_main',l_cc_fail_ids.COUNT);
948 END IF;
949 --
950
951 IF l_return_status=wsh_util_core.g_ret_sts_error THEN
952 --fix l_rec_attr_tab to have only successful records
953 l_cc_count_success:=1;
954
955 IF l_debug_on THEN
956 wsh_debug_sv.log(l_module_name,'l_stop_id_tab.count before removing failed lines',l_stop_id_tab.COUNT);
957 END IF;
958
959 IF l_cc_fail_ids.COUNT>0 AND l_stop_id_tab.COUNT>0 THEN
960
961 --set return_status as warning
962 IF l_cc_fail_ids.COUNT<>l_stop_id_tab.COUNT THEN
963 l_return_status:=wsh_util_core.g_ret_sts_warning;
964 END IF;
965
966 FOR i in l_stop_id_tab.FIRST..l_stop_id_tab.LAST LOOP
967 b_cc_linefailed:=FALSE;
968
969 FOR j in l_cc_fail_ids.FIRST..l_cc_fail_ids.LAST LOOP
970 IF (l_stop_id_tab(i)=l_cc_fail_ids(j)) THEN
971 b_cc_linefailed:=TRUE;
972 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_DELETE_ERROR');
973 --
974 -- Debug Statements
975 --
976 IF l_debug_on THEN
977 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
978 END IF;
979 --tkt
980 FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(l_cc_fail_ids(j),p_action_prms.caller));
981 wsh_util_core.add_message(l_return_status);
982 END IF;
983 END LOOP;
984
985 IF (NOT(b_cc_linefailed)) THEN
986 l_stop_id_tab_t(l_cc_count_success):=l_stop_id_tab(i);
987 l_cc_count_success:=l_cc_count_success+1;
988
989 -- csun 10+ internal location change
990 l_delete_t(l_cc_count_success).stop_id := l_delete_tmp_tab(i).stop_id;
991 l_delete_t(l_cc_count_success).trip_id := l_delete_tmp_tab(i).trip_id;
992 END IF;
993 END LOOP;
994
995 IF l_stop_id_tab_t.COUNT>0 THEN
996 l_stop_id_tab:=l_stop_id_tab_t;
997 -- csun 10+ internal location change
998 l_delete_tmp_tab := l_delete_t;
999 ELSE
1000 IF l_debug_on THEN
1001 wsh_debug_sv.logmsg(l_module_name,'all lines errored in compatibility check');
1002 END IF;
1003 wsh_util_core.api_post_call(
1004 p_return_status => l_return_status,
1005 x_num_warnings => l_num_warning,
1006 x_num_errors => l_num_errors,
1007 p_msg_data => l_msg_data);
1008
1009 END IF;
1010 END IF;
1011
1012 ELSIF l_return_status=wsh_util_core.g_ret_sts_unexp_error THEN
1013 wsh_util_core.api_post_call(
1014 p_return_status => l_return_status,
1015 x_num_warnings => l_num_warning,
1016 x_num_errors => l_num_errors,
1017 p_msg_data => l_msg_data);
1018 END IF;
1019
1020 wsh_util_core.api_post_call(
1021 p_return_status => l_return_status,
1022 x_num_warnings => l_num_warning,
1023 x_num_errors => l_num_errors,
1024 p_msg_data => l_msg_data,
1025 p_raise_error_flag => FALSE);
1026 END IF;
1027 --Compatiblity Changes
1028 END IF ; --}
1029
1030 -- jckwok: Bug 2684692 added the below case
1031 IF WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_LOCK_RELATED_ENTITIES_LVL) = 1
1032 AND p_action_prms.stop_action = 'CLOSE'
1033 THEN
1034
1035 Lock_Related_Entity(
1036 p_action_prms => p_action_prms,
1037 p_stop_attr_tab => p_rec_attr_tab,
1038 x_valid_ids_tab => l_stop_id_tab,
1039 x_return_status => l_return_status);
1040
1041 if l_debug_on then
1042 wsh_debug_sv.log(l_module_name, 'l_stop_id_tab count', l_stop_id_tab.count);
1043 end if;
1044
1045 wsh_util_core.api_post_call( p_return_status =>l_return_status,
1046 x_num_warnings =>l_num_warning,
1047 x_num_errors =>l_num_errors);
1048
1049 END IF;
1050
1051 IF p_action_prms.action_code = 'PLAN' THEN
1052 WSH_TRIP_STOPS_ACTIONS.plan (
1053 p_stop_rows => l_stop_id_tab,
1054 p_action => 'PLAN',
1055 --tkt
1056 p_caller => p_action_prms.caller,
1057 x_return_status => l_return_status
1058 );
1059 wsh_util_core.api_post_call(p_return_status =>l_return_status,
1060 x_num_warnings =>l_num_warning,
1061 x_num_errors =>l_num_errors);
1062 ELSIF p_action_prms.action_code = 'UNPLAN' THEN
1063 WSH_TRIP_STOPS_ACTIONS.plan (
1064 p_stop_rows => l_stop_id_tab,
1065 p_action => 'UNPLAN',
1066 --tkt
1067 p_caller => p_action_prms.caller,
1068 x_return_status => l_return_status
1069 );
1070 wsh_util_core.api_post_call(p_return_status =>l_return_status,
1071 x_num_warnings =>l_num_warning,
1072 x_num_errors =>l_num_errors);
1073
1074 ELSIF p_action_prms.action_code = 'PICK-RELEASE' THEN
1075 WSH_PICK_LIST.Launch_Pick_Release(
1076 p_trip_ids => l_dummy_ids,
1077 p_stop_ids => l_stop_id_tab,
1078 p_delivery_ids => l_dummy_ids,
1079 p_detail_ids => l_dummy_ids,
1080 x_request_ids => x_stop_out_rec.result_id_tab,
1081 x_return_status => l_return_status
1082 );
1083 wsh_util_core.api_post_call(p_return_status =>l_return_status,
1084 x_num_warnings =>l_num_warning,
1085 x_num_errors =>l_num_errors);
1086
1087 ELSIF p_action_prms.action_code = 'PRINT-DOC-SETS' THEN
1088 IF WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_DOCUMENT_SETS_LVL)=1 THEN
1089 IF l_report_set_id IS NULL THEN
1090 FND_MESSAGE.SET_NAME('WSH','WSH_DOC_MISSING');
1091 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
1092 IF l_debug_on THEN
1093 wsh_debug_sv.log (l_module_name,'WSH_DOC_MISSING');
1094 END IF;
1095 RAISE FND_API.G_EXC_ERROR;
1096 END IF;
1097 WSH_UTIL_VALIDATE.validate_report_set(
1098 p_report_set_id => l_report_set_id,
1099 p_report_set_name => NULL,
1100 x_return_status => l_return_status);
1101 wsh_util_core.api_post_call(p_return_status =>l_return_status,
1102 x_num_warnings =>l_num_warning,
1103 x_num_errors =>l_num_errors);
1104 END IF;
1105 WSH_DOCUMENT_SETS.print_document_sets(
1106 p_report_set_id => p_action_prms.report_set_id,
1107 p_organization_id => p_action_prms.organization_id,
1108 p_trip_ids => l_dummy_ids,
1109 p_stop_ids => l_stop_id_tab,
1110 p_delivery_ids => l_dummy_ids,
1111 p_document_param_info => l_dummy_doc_param,
1112 x_return_status => l_return_status
1113 );
1114 wsh_util_core.api_post_call(p_return_status =>l_return_status,
1115 x_num_warnings =>l_num_warning,
1116 x_num_errors =>l_num_errors);
1117 ELSIF p_action_prms.action_code = 'WT-VOL' THEN
1118
1119 -- OTM R12 : packing ECO
1120 -- This change was introduced to mark the G_RESET_WV flag
1121 -- before calling calc_stop_weight_volume so the procedure will know
1122 -- to invoke update tms_interface_flag process.
1123
1124 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
1125 IF l_gc3_is_installed IS NULL THEN
1126 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
1127 END IF;
1128
1129 IF l_gc3_is_installed = 'Y' THEN
1130 WSH_WV_UTILS.G_RESET_WV := 'Y'; -- set to Y to enable the update
1131 END IF;
1132 -- End of OTM R12 : packing ECO
1133
1134 WSH_TRIP_STOPS_ACTIONS.calc_stop_weight_volume(
1135 p_stop_rows => l_stop_id_tab,
1136 p_override_flag => p_action_prms.override_flag,
1137 x_return_status => l_return_status,
1138 --tkt
1139 p_caller => p_action_prms.caller
1140 );
1141 wsh_util_core.api_post_call(p_return_status =>l_return_status,
1142 x_num_warnings =>l_num_warning,
1143 x_num_errors =>l_num_errors);
1144
1145 -- OTM R12 : packing ECO
1146 IF l_gc3_is_installed = 'Y' THEN
1147 WSH_WV_UTILS.G_RESET_WV := 'N'; -- after call, set it back to 'N'
1148 END IF;
1149 -- End of OTM R12 : packing ECO
1150
1151 -- for Load Tender
1152 -- above call calls FTE
1153 -- for any update to a stop
1154 -- end of Load Tender
1155
1156 ELSIF p_action_prms.action_code = 'UPDATE-STATUS' THEN
1157
1158 IF l_debug_on THEN
1159 wsh_debug_sv.log (l_module_name,'stop_action',
1160 p_action_prms.stop_action);
1161 wsh_debug_sv.log (l_module_name,'actual_date',
1162 p_action_prms.actual_date);
1163 wsh_debug_sv.log (l_module_name,'defer_interface_flag',
1164 p_action_prms.defer_interface_flag);
1165 wsh_debug_sv.log (l_module_name,'C_TRIP_STOP_VALIDATION_LVL',WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_TRIP_STOP_VALIDATION_LVL));
1166 END IF;
1167
1168 -- BUG 4247388 - FP:11I10-12.0: if actual date is NULL, always default to SYSDATE here
1169 -- (whether updating status to Arrived or Closed) to ensure that
1170 -- the default date will be passed internally.
1171 -- For example, deliveries' initial pick up date must be in sync
1172 -- with their initial pick up stops' actual departure dates.
1173 l_actual_date := NVL(p_action_prms.actual_date, SYSDATE);
1174
1175 IF l_debug_on THEN
1176 wsh_debug_sv.log (l_module_name,'l_actual_date', l_actual_date);
1177 END IF;
1178
1179
1180 IF WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_TRIP_STOP_VALIDATION_LVL) = 1
1181 THEN
1182 --
1183 l_is_installed := WSH_UTIL_CORE.FTE_Is_Installed ;
1184 IF l_is_installed = 'Y' THEN
1185 --
1186 FOR i IN l_stop_id_tab.FIRST..l_stop_id_tab.last LOOP
1187 BEGIN
1188
1189 SAVEPOINT s_clean_loop_grp;
1190
1191 WSH_TRIP_STOPS_GRP.get_stop_details_pvt(
1192 p_stop_id => l_stop_id_tab(i),
1193 x_stop_rec => l_stop_rec,
1194 x_return_status => l_return_status
1195 );
1196 wsh_util_core.api_post_call(p_return_status =>l_return_status,
1197 x_num_warnings =>l_num_warning,
1198 x_num_errors =>l_num_errors);
1199
1200
1201 WSH_FTE_INTEGRATION.trip_stop_validations(
1202 p_stop_rec => l_stop_rec,
1203 p_trip_rec => l_trip_rec,
1204 p_action => 'UPDATE',
1205 x_return_status => l_return_status
1206 );
1207 wsh_util_core.api_post_call(p_return_status =>l_return_status,
1208 x_num_warnings =>l_num_warning,
1209 x_num_errors =>l_num_errors);
1210 EXCEPTION
1211 WHEN FND_API.G_EXC_ERROR THEN
1212 ROLLBACK TO s_clean_loop_grp;
1213 IF l_debug_on THEN
1214 wsh_debug_sv.log (l_module_name,'G_EXC_ERROR in the loop');
1215 END IF;
1216 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1217 ROLLBACK TO s_clean_loop_grp;
1218 IF l_debug_on THEN
1219 wsh_debug_sv.log (l_module_name,
1220 'G_EXC_UNEXPECTED_ERROR in the loop');
1221 END IF;
1222 END;
1223 END LOOP;
1224
1225 IF l_num_errors >= l_stop_id_tab.COUNT THEN
1226 IF l_debug_on THEN
1227 wsh_debug_sv.log (l_module_name,'All failed in the loop');
1228 END IF;
1229 RAISE FND_API.G_EXC_ERROR;
1230 END IF;
1231
1232 IF l_num_errors >0 THEN
1233 IF l_debug_on THEN
1234 wsh_debug_sv.log (l_module_name,'l_num_errors',l_num_errors);
1235 END IF;
1236 l_num_warning := l_num_warning + l_num_errors;
1237 l_num_errors := 0;
1238 END IF;
1239 --
1240 END IF;
1241 --
1242 END IF;
1243 IF WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_CHK_UPDATE_STATUS_LVL) = 1 THEN
1244 if l_debug_on then
1245 wsh_debug_sv.log(l_module_name, 'l_stop_id_tab count', l_stop_id_tab.count);
1246 end if;
1247 WSH_TRIP_STOPS_ACTIONS.Check_Update_Stops(
1248 p_stop_rows => l_stop_id_tab,
1249 p_action => p_action_prms.stop_action,
1250 --tkt
1251 p_caller => p_action_prms.caller,
1252 x_return_status => l_return_status
1253 );
1254 wsh_util_core.api_post_call(p_return_status =>l_return_status,
1255 x_num_warnings =>l_num_warning,
1256 x_num_errors =>l_num_errors);
1257 END IF;
1258
1259 WSH_TRIP_STOPS_ACTIONS.Change_Status(
1260 p_stop_rows => l_stop_id_tab,
1261 p_action => p_action_prms.stop_action,
1262 p_actual_date => l_actual_date,
1263 p_defer_interface_flag => p_action_prms.defer_interface_flag,
1264 x_return_status => l_return_status,
1265 p_caller => p_action_prms.caller
1266 );
1267 wsh_util_core.api_post_call(p_return_status =>l_return_status,
1268 x_num_warnings =>l_num_warning,
1269 x_num_errors =>l_num_errors);
1270
1271 ELSIF p_action_prms.action_code = 'DELETE' THEN
1272
1273 --TL Rating
1274 IF( WSH_UTIL_CORE.FTE_Is_Installed = 'Y') THEN
1275 IF l_stop_id_tab.count > 0 THEN
1276 --
1277 IF l_debug_on THEN
1278 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_LEGS_ACTIONS.MARK_REPRICE_REQUIRED',WSH_DEBUG_SV.C_PROC_LEVEL);
1279 END IF;
1280 --
1281 WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required(
1282 p_entity_type => 'STOP',
1283 p_entity_ids => l_stop_id_tab,
1284 x_return_status => l_return_status
1285 );
1286
1287 --
1288 wsh_util_core.api_post_call(
1289 p_return_status => l_return_status,
1290 x_num_warnings => l_num_warning,
1291 x_num_errors => l_num_errors
1292 );
1293
1294 END IF;
1295 END IF;
1296 --TL Rating
1297 --
1298
1299 Add_to_Delete_List(
1300 p_stop_tab => l_delete_tmp_tab,
1301 p_caller => p_action_prms.caller,
1302 x_stop_delete_tab => l_stop_delete_tab,
1303 x_trip_affected_tab => l_trip_affected_tab,
1304 x_return_status => l_return_status);
1305
1306
1307 WSH_UTIL_CORE.delete(
1308 p_type => 'STOP',
1309 p_rows => l_stop_delete_tab,
1310 x_return_status => l_return_status,
1311 --tkt
1312 p_caller => p_action_prms.caller
1313 );
1314 wsh_util_core.api_post_call(p_return_status =>l_return_status,
1315 x_num_warnings =>l_num_warning,
1316 x_num_errors =>l_num_errors);
1317
1318
1319 WSH_TRIPS_ACTIONS.Handle_Internal_Stops(
1320 p_trip_ids => l_trip_affected_tab,
1321 p_caller => p_action_prms.caller,
1322 x_success_trip_ids => l_success_trip_tab,
1323 x_return_status => l_return_status);
1324
1325 wsh_util_core.api_post_call(p_return_status =>l_return_status,
1326 x_num_warnings =>l_num_warning,
1327 x_num_errors =>l_num_errors);
1328
1329 --update status of trip to close if already in-transit
1330 --and all other stops are closed
1331 IF l_trip_affected_tab IS NOT NULL AND l_trip_affected_tab.COUNT>0 THEN
1332 FOR i IN l_trip_affected_tab.FIRST..l_trip_affected_tab.LAST LOOP
1333 l_trip_in_rec.new_status_code := 'CL';
1334 l_trip_in_rec.put_messages := TRUE; --p_in_rec.put_messages;
1335 l_trip_in_rec.manual_flag := 'N';
1336 l_trip_in_rec.caller := p_action_prms.caller;
1337 OPEN c_check_trip_close(l_trip_affected_tab(i));
1338 FETCH c_check_trip_close INTO l_trip_in_rec.trip_id;
1339 IF c_check_trip_close%FOUND THEN
1340 IF l_debug_on THEN
1341 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_ACTIONS.CHANGESTATUS',WSH_DEBUG_SV.C_PROC_LEVEL);
1342 WSH_DEBUG_SV.log(l_module_name,'Trip',l_trip_in_rec.trip_id);
1343 END IF;
1344
1345 wsh_trips_actions.changeStatus
1346 (
1347 p_in_rec => l_trip_in_rec,
1348 x_return_status => l_return_status
1349 );
1350
1351 wsh_util_core.api_post_call
1352 (
1353 p_return_status => l_return_status,
1354 x_num_warnings => l_num_warning,
1355 x_num_errors => l_num_errors
1356 );
1357 END IF;
1358 CLOSE c_check_trip_close;
1359 END LOOP;
1360 END IF;
1361
1362 ELSIF p_action_prms.action_code IN ( 'PICK-RELEASE-UI', 'RESOLVE-EXCEPTIONS-UI', 'FREIGHT-COSTS-UI') THEN
1363 IF p_rec_attr_tab.COUNT > 1 THEN
1364 FND_MESSAGE.SET_NAME('WSH','WSH_UI_MULTI_SELECTION');
1365 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
1366 IF l_debug_on THEN
1367 wsh_debug_sv.log (l_module_name,'WSH_UI_MULTI_SELECTION');
1368 END IF;
1369 RAISE FND_API.G_EXC_ERROR;
1370 ELSIF p_rec_attr_tab.COUNT = 1
1371 AND p_action_prms.caller <> 'WSH_FSTRX' THEN
1372 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_ACTION_CODE');
1373 FND_MESSAGE.SET_TOKEN('ACT_CODE',p_action_prms.action_code );
1374 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
1375 IF l_debug_on THEN
1376 wsh_debug_sv.log (l_module_name,'WSH_INVALID_ACTION_CODE COUNT');
1377 END IF;
1378 RAISE FND_API.G_EXC_ERROR;
1379 END IF;
1380
1381 ELSE
1382 -- give message for invalid action
1383 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_ACTION_CODE');
1384 FND_MESSAGE.SET_TOKEN('ACT_CODE',p_action_prms.action_code );
1385 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
1386 IF l_debug_on THEN
1387 wsh_debug_sv.log (l_module_name,'WSH_INVALID_ACTION_CODE');
1388 END IF;
1389 RAISE FND_API.G_EXC_ERROR;
1390 END IF;
1391
1392 -- TP call back to unfirm continuous move or delete continuous move or
1393 -- any other action that will be done in the future based on the action performed
1394 IF WSH_UTIL_CORE.TP_IS_INSTALLED='Y' THEN
1395 WSH_FTE_TP_INTEGRATION.stop_callback (
1396 p_api_version_number => 1.0,
1397 p_init_msg_list => FND_API.G_TRUE,
1398 x_return_status => l_return_status,
1399 x_msg_count => l_msg_count,
1400 x_msg_data => l_msg_data,
1401 p_action_prms => p_action_prms,
1402 p_rec_attr_tab => p_rec_attr_tab);
1403
1404 IF l_debug_on THEN
1405 WSH_DEBUG_SV.log(l_module_name,'after calling stop_callback l_return_status',l_return_status);
1406 END IF;
1407
1408 wsh_util_core.api_post_call(
1409 p_return_status => l_return_status,
1410 x_num_warnings => l_num_warning,
1411 x_num_errors => l_num_errors
1412 );
1413 END IF;--tp_is_installed
1414
1415 --
1416 -- K LPN CONV. rv
1417 --
1418 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
1419 THEN
1420 --{
1421 IF l_debug_on THEN
1422 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
1423 END IF;
1424
1425 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
1426 (
1427 p_in_rec => l_lpn_in_sync_comm_rec,
1428 x_return_status => l_return_status,
1429 x_out_rec => l_lpn_out_sync_comm_rec
1430 );
1431 --
1432 --
1433 IF l_debug_on THEN
1434 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
1435 END IF;
1436 --
1437 --
1438 WSH_UTIL_CORE.API_POST_CALL
1439 (
1440 p_return_status => l_return_status,
1441 x_num_warnings => l_num_warning,
1442 x_num_errors => l_num_errors
1443 );
1444 --}
1445 END IF;
1446 --
1447 -- K LPN CONV. rv
1448
1449
1450 IF l_num_warning > 0 THEN
1451 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
1452 ELSE
1453 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1454 END IF;
1455
1456 IF p_commit = FND_API.G_TRUE THEN
1457 --Bugfix 4070732 {
1458 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1459 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1460
1461 IF l_debug_on THEN
1462 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1463 END IF;
1464
1465 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => FALSE,
1466 x_return_status => l_return_status);
1467
1468 IF l_debug_on THEN
1469 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1470 END IF;
1471
1472 END IF;
1473
1474 --}
1475 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
1476 OR (l_return_status = wsh_util_core.g_ret_sts_warning) THEN
1477 COMMIT;
1478 END IF;
1479 --if return status is not success rollback in the exception
1480 -- handling block
1481 wsh_util_core.api_post_call
1482 (
1483 p_return_status => l_return_status,
1484 x_num_warnings => l_num_warning,
1485 x_num_errors => l_num_errors
1486 );
1487 x_return_status := l_return_status ; --x_return_status was success g
1488 -- set it to l_return_status for
1489 -- when l_return_status = warning
1490 END IF;
1491 --
1492 --Bugfix 4070732 { logical end of the API
1493 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN --{
1494 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1495
1496 IF p_commit = FND_API.G_TRUE THEN
1497
1498 IF l_debug_on THEN
1499 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1500 END IF;
1501 WSH_UTIL_CORE.reset_stops_for_load_tender(
1502 p_reset_flags => TRUE,
1503 x_return_status => l_return_status);
1504 ELSE
1505
1506 IF l_debug_on THEN
1507 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1508 END IF;
1509 WSH_UTIL_CORE.Process_stops_for_load_tender(
1510 p_reset_flags => TRUE,
1511 x_return_status => l_return_status);
1512 END IF;
1513
1514 IF l_debug_on THEN
1515 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1516 END IF;
1517
1518 --
1519 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1520 OR (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1521 THEN --{
1522 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1523 ELSIF x_return_status <> WSH_UTIL_CORE.G_RET_STS_ERROR
1524 THEN
1525 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
1526 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1527 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING
1528 THEN
1529 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1530 END IF;
1531 END IF; --}
1532 IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1533 OR x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
1534 THEN
1535 IF p_commit = FND_API.G_TRUE THEN
1536 null;
1537 ELSE
1538 ROLLBACK TO s_stop_action_grp;
1539 END IF;
1540
1541 END IF;
1542 --
1543 END IF;
1544 END IF; --}
1545
1546 --}
1547 --End of bug 4070732
1548 --
1549 --
1550 FND_MSG_PUB.Count_And_Get
1551 ( p_count => x_msg_count
1552 , p_data => x_msg_data
1553 , p_encoded => FND_API.G_FALSE
1554 );
1555
1556 IF l_debug_on THEN
1557 WSH_DEBUG_SV.pop(l_module_name);
1558 END IF;
1559
1560 EXCEPTION
1561 WHEN FND_API.G_EXC_ERROR THEN
1562 ROLLBACK TO s_stop_action_grp;
1563 IF c_check_trip_close%ISOPEN THEN
1564 CLOSE c_check_trip_close;
1565 END IF;
1566 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1567 --Bugfix 4070732 {
1568 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1569 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1570 IF l_debug_on THEN
1571 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1572 END IF;
1573
1574 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1575 x_return_status => l_return_status);
1576
1577
1578 IF l_debug_on THEN
1579 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1580 END IF;
1581 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1582 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1583 END IF;
1584
1585 END IF;
1586 END IF;
1587 --}
1588 FND_MSG_PUB.Count_And_Get
1589 ( p_count => x_msg_count
1590 , p_data => x_msg_data
1591 , p_encoded => FND_API.G_FALSE
1592 );
1593 IF l_debug_on THEN
1594 wsh_debug_sv.log (l_module_name,'G_EXC_ERROR');
1595 WSH_DEBUG_SV.pop(l_module_name);
1596 END IF;
1597 WHEN e_req_field THEN
1598 ROLLBACK TO s_stop_action_grp;
1599 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1600 wsh_util_core.add_message(x_return_status,l_module_name);
1601 --Bugfix 4070732 {
1602 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1603 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1604 IF l_debug_on THEN
1605 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1606 END IF;
1607
1608 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1609 x_return_status => l_return_status);
1610
1611
1612 IF l_debug_on THEN
1613 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1614 END IF;
1615
1616 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1617 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1618 END IF;
1619 END IF;
1620 END IF;
1621 --}
1622 FND_MSG_PUB.Count_And_Get
1623 ( p_count => x_msg_count
1624 , p_data => x_msg_data
1625 , p_encoded => FND_API.G_FALSE
1626 );
1627 IF l_debug_on THEN
1628 wsh_debug_sv.log (l_module_name,'G_EXC_ERROR');
1629 WSH_DEBUG_SV.pop(l_module_name);
1630 END IF;
1631 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1632 ROLLBACK TO s_stop_action_grp;
1633 IF c_check_trip_close%ISOPEN THEN
1634 CLOSE c_check_trip_close;
1635 END IF;
1636 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1637 --Bugfix 4070732 {
1638 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1639 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1640 IF l_debug_on THEN
1641 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1642 END IF;
1643
1644 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1645 x_return_status => l_return_status);
1646
1647
1648 IF l_debug_on THEN
1649 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1650 END IF;
1651
1652 END IF;
1653 END IF;
1654 --}
1655
1656 FND_MSG_PUB.Count_And_Get
1657 ( p_count => x_msg_count
1658 , p_data => x_msg_data
1659 , p_encoded => FND_API.G_FALSE
1660 );
1661 IF l_debug_on THEN
1662 wsh_debug_sv.log (l_module_name,'G_EXC_UNEXPECTED_ERROR');
1663 WSH_DEBUG_SV.pop(l_module_name);
1664 END IF;
1665 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1666 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1667 --Bugfix 4070732 {
1668 --
1669 -- K LPN CONV. rv
1670 --
1671 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
1672 THEN
1673 --{
1674 IF l_debug_on THEN
1675 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
1676 END IF;
1677
1678 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
1679 (
1680 p_in_rec => l_lpn_in_sync_comm_rec,
1681 x_return_status => l_return_status,
1682 x_out_rec => l_lpn_out_sync_comm_rec
1683 );
1684 --
1685 --
1686 IF l_debug_on THEN
1687 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
1688 END IF;
1689 --
1690 --
1691 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
1692 x_return_status := l_return_status;
1693 END IF;
1694 --
1695 --}
1696 END IF;
1697 --
1698 -- K LPN CONV. rv
1699 --
1700 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1701 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1702 IF l_debug_on THEN
1703 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1704 END IF;
1705
1706 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => TRUE,
1707 x_return_status => l_return_status);
1708
1709 IF l_debug_on THEN
1710 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1711 END IF;
1712
1713 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1714 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
1715 ROLLBACK TO s_stop_action_grp;
1716 x_return_status := l_return_status;
1717 END IF;
1718
1719 END IF;
1720 END IF;
1721 --}
1722 FND_MSG_PUB.Count_And_Get
1723 ( p_count => x_msg_count
1724 , p_data => x_msg_data
1725 , p_encoded => FND_API.G_FALSE
1726 );
1727 IF l_debug_on THEN
1728 wsh_debug_sv.log (l_module_name,'G_EXC_WARNING');
1729 WSH_DEBUG_SV.pop(l_module_name);
1730 END IF;
1731 WHEN OTHERS THEN
1732 ROLLBACK TO s_stop_action_grp;
1733 IF c_check_trip_close%ISOPEN THEN
1734 CLOSE c_check_trip_close;
1735 END IF;
1736 wsh_util_core.default_handler('WSH_TRIP_STOPS_GRP.STOP_ACTION',
1737 l_module_name);
1738
1739
1740 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1741 --Bugfix 4070732 {
1742 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1743 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1744 IF l_debug_on THEN
1745 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1746 END IF;
1747
1748 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1749 x_return_status => l_return_status);
1750
1751
1752 IF l_debug_on THEN
1753 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1754 END IF;
1755
1756 END IF;
1757 END IF;
1758 --}
1759 FND_MSG_PUB.Count_And_Get
1760 ( p_count => x_msg_count
1761 , p_data => x_msg_data
1762 , p_encoded => FND_API.G_FALSE
1763 );
1764 IF l_debug_on THEN
1765 wsh_debug_sv.log (l_module_name,'Others',substr(sqlerrm,1,200));
1766 WSH_DEBUG_SV.pop(l_module_name);
1767 END IF;
1768
1769 END Stop_Action;
1770
1771
1772 --Harmonizing Project **heali
1773 --========================================================================
1774 -- PROCEDURE : map_stopgrp_to_pvt PRIVATE
1775 --
1776 -- PARAMETERS: p_grp_stop_rec IN WSH_TRIP_STOPS_GRP.TRIP_STOP_PUB_REC_TYPE
1777 -- x_pvt_stop_rec OUT WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE
1778 -- x_return_status return status
1779 -- COMMENT : This procedure maps Group API record type to Private API record type for Stop.
1780 --========================================================================
1781 PROCEDURE map_stopgrp_to_pvt(
1782 p_grp_stop_rec IN WSH_TRIP_STOPS_GRP.TRIP_STOP_PUB_REC_TYPE,
1783 x_pvt_stop_rec OUT NOCOPY WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE,
1784 x_return_status OUT NOCOPY VARCHAR2) IS
1785
1786 l_debug_on BOOLEAN;
1787 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'MAP_STOPGRP_TO_PVT';
1788 BEGIN
1789 --
1790 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1791 --
1792 IF l_debug_on IS NULL
1793 THEN
1794 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1795 END IF;
1796 --
1797 IF l_debug_on THEN
1798 WSH_DEBUG_SV.push(l_module_name);
1799 WSH_DEBUG_SV.log(l_module_name,'p_grp_stop_rec.STOP_ID',p_grp_stop_rec.STOP_ID);
1800 WSH_DEBUG_SV.log(l_module_name,'p_grp_stop_rec.TRIP_ID',p_grp_stop_rec.TRIP_ID);
1801 WSH_DEBUG_SV.log(l_module_name,'p_grp_stop_rec.TRIP_NAME',p_grp_stop_rec.TRIP_NAME);
1802 END IF;
1803
1804 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1805
1806 x_pvt_stop_rec.STOP_ID := p_grp_stop_rec.STOP_ID;
1807 x_pvt_stop_rec.TRIP_ID := p_grp_stop_rec.TRIP_ID;
1808 x_pvt_stop_rec.STOP_LOCATION_ID := p_grp_stop_rec.STOP_LOCATION_ID;
1809 x_pvt_stop_rec.STATUS_CODE := p_grp_stop_rec.STATUS_CODE;
1810 x_pvt_stop_rec.STOP_SEQUENCE_NUMBER := p_grp_stop_rec.STOP_SEQUENCE_NUMBER;
1811 x_pvt_stop_rec.PLANNED_ARRIVAL_DATE := p_grp_stop_rec.PLANNED_ARRIVAL_DATE;
1812 x_pvt_stop_rec.PLANNED_DEPARTURE_DATE := p_grp_stop_rec.PLANNED_DEPARTURE_DATE;
1813 x_pvt_stop_rec.ACTUAL_ARRIVAL_DATE := p_grp_stop_rec.ACTUAL_ARRIVAL_DATE;
1814 x_pvt_stop_rec.ACTUAL_DEPARTURE_DATE := p_grp_stop_rec.ACTUAL_DEPARTURE_DATE;
1815 x_pvt_stop_rec.DEPARTURE_GROSS_WEIGHT := p_grp_stop_rec.DEPARTURE_GROSS_WEIGHT;
1816 x_pvt_stop_rec.DEPARTURE_NET_WEIGHT := p_grp_stop_rec.DEPARTURE_NET_WEIGHT;
1817 x_pvt_stop_rec.WEIGHT_UOM_CODE := p_grp_stop_rec.WEIGHT_UOM_CODE;
1818 x_pvt_stop_rec.DEPARTURE_VOLUME := p_grp_stop_rec.DEPARTURE_VOLUME;
1819 x_pvt_stop_rec.VOLUME_UOM_CODE := p_grp_stop_rec.VOLUME_UOM_CODE;
1820 x_pvt_stop_rec.DEPARTURE_SEAL_CODE := p_grp_stop_rec.DEPARTURE_SEAL_CODE;
1821 x_pvt_stop_rec.DEPARTURE_FILL_PERCENT := p_grp_stop_rec.DEPARTURE_FILL_PERCENT;
1822 x_pvt_stop_rec.TP_ATTRIBUTE_CATEGORY := p_grp_stop_rec.TP_ATTRIBUTE_CATEGORY;
1823 x_pvt_stop_rec.TP_ATTRIBUTE1 := p_grp_stop_rec.TP_ATTRIBUTE1;
1824 x_pvt_stop_rec.TP_ATTRIBUTE2 := p_grp_stop_rec.TP_ATTRIBUTE2;
1825 x_pvt_stop_rec.TP_ATTRIBUTE3 := p_grp_stop_rec.TP_ATTRIBUTE3;
1826 x_pvt_stop_rec.TP_ATTRIBUTE4 := p_grp_stop_rec.TP_ATTRIBUTE4;
1827 x_pvt_stop_rec.TP_ATTRIBUTE5 := p_grp_stop_rec.TP_ATTRIBUTE5;
1828 x_pvt_stop_rec.TP_ATTRIBUTE6 := p_grp_stop_rec.TP_ATTRIBUTE6;
1829 x_pvt_stop_rec.TP_ATTRIBUTE7 := p_grp_stop_rec.TP_ATTRIBUTE7;
1830 x_pvt_stop_rec.TP_ATTRIBUTE8 := p_grp_stop_rec.TP_ATTRIBUTE8;
1831 x_pvt_stop_rec.TP_ATTRIBUTE9 := p_grp_stop_rec.TP_ATTRIBUTE9;
1832 x_pvt_stop_rec.TP_ATTRIBUTE10 := p_grp_stop_rec.TP_ATTRIBUTE10;
1833 x_pvt_stop_rec.TP_ATTRIBUTE11 := p_grp_stop_rec.TP_ATTRIBUTE11;
1834 x_pvt_stop_rec.TP_ATTRIBUTE12 := p_grp_stop_rec.TP_ATTRIBUTE12;
1835 x_pvt_stop_rec.TP_ATTRIBUTE13 := p_grp_stop_rec.TP_ATTRIBUTE13;
1836 x_pvt_stop_rec.TP_ATTRIBUTE14 := p_grp_stop_rec.TP_ATTRIBUTE14;
1837 x_pvt_stop_rec.TP_ATTRIBUTE15 := p_grp_stop_rec.TP_ATTRIBUTE15;
1838 x_pvt_stop_rec.ATTRIBUTE_CATEGORY := p_grp_stop_rec.ATTRIBUTE_CATEGORY;
1839 x_pvt_stop_rec.ATTRIBUTE1 := p_grp_stop_rec.ATTRIBUTE1;
1840 x_pvt_stop_rec.ATTRIBUTE2 := p_grp_stop_rec.ATTRIBUTE2;
1841 x_pvt_stop_rec.ATTRIBUTE3 := p_grp_stop_rec.ATTRIBUTE3;
1842 x_pvt_stop_rec.ATTRIBUTE4 := p_grp_stop_rec.ATTRIBUTE4;
1843 x_pvt_stop_rec.ATTRIBUTE5 := p_grp_stop_rec.ATTRIBUTE5;
1844 x_pvt_stop_rec.ATTRIBUTE6 := p_grp_stop_rec.ATTRIBUTE6;
1845 x_pvt_stop_rec.ATTRIBUTE7 := p_grp_stop_rec.ATTRIBUTE7;
1846 x_pvt_stop_rec.ATTRIBUTE8 := p_grp_stop_rec.ATTRIBUTE8;
1847 x_pvt_stop_rec.ATTRIBUTE9 := p_grp_stop_rec.ATTRIBUTE9;
1848 x_pvt_stop_rec.ATTRIBUTE10 := p_grp_stop_rec.ATTRIBUTE10;
1849 x_pvt_stop_rec.ATTRIBUTE11 := p_grp_stop_rec.ATTRIBUTE11;
1850 x_pvt_stop_rec.ATTRIBUTE12 := p_grp_stop_rec.ATTRIBUTE12;
1851 x_pvt_stop_rec.ATTRIBUTE13 := p_grp_stop_rec.ATTRIBUTE13;
1852 x_pvt_stop_rec.ATTRIBUTE14 := p_grp_stop_rec.ATTRIBUTE14;
1853 x_pvt_stop_rec.ATTRIBUTE15 := p_grp_stop_rec.ATTRIBUTE15;
1854 x_pvt_stop_rec.CREATION_DATE := p_grp_stop_rec.CREATION_DATE;
1855 x_pvt_stop_rec.CREATED_BY := p_grp_stop_rec.CREATED_BY;
1856 x_pvt_stop_rec.LAST_UPDATE_DATE := p_grp_stop_rec.LAST_UPDATE_DATE;
1857 x_pvt_stop_rec.LAST_UPDATED_BY := p_grp_stop_rec.LAST_UPDATED_BY;
1858 x_pvt_stop_rec.LAST_UPDATE_LOGIN := p_grp_stop_rec.LAST_UPDATE_LOGIN;
1859 x_pvt_stop_rec.PROGRAM_APPLICATION_ID := p_grp_stop_rec.PROGRAM_APPLICATION_ID;
1860 x_pvt_stop_rec.PROGRAM_ID := p_grp_stop_rec.PROGRAM_ID;
1861 x_pvt_stop_rec.PROGRAM_UPDATE_DATE := p_grp_stop_rec.PROGRAM_UPDATE_DATE;
1862 x_pvt_stop_rec.REQUEST_ID := p_grp_stop_rec.REQUEST_ID;
1863 x_pvt_stop_rec.WSH_LOCATION_ID := p_grp_stop_rec.WSH_LOCATION_ID;
1864 x_pvt_stop_rec.TRACKING_DRILLDOWN_FLAG := p_grp_stop_rec.TRACKING_DRILLDOWN_FLAG;
1865 x_pvt_stop_rec.TRACKING_REMARKS := p_grp_stop_rec.TRACKING_REMARKS;
1866 x_pvt_stop_rec.CARRIER_EST_DEPARTURE_DATE := p_grp_stop_rec.CARRIER_EST_DEPARTURE_DATE;
1867 x_pvt_stop_rec.CARRIER_EST_ARRIVAL_DATE := p_grp_stop_rec.CARRIER_EST_ARRIVAL_DATE;
1868 x_pvt_stop_rec.LOADING_START_DATETIME := p_grp_stop_rec.LOADING_START_DATETIME;
1869 x_pvt_stop_rec.LOADING_END_DATETIME := p_grp_stop_rec.LOADING_END_DATETIME;
1870 x_pvt_stop_rec.UNLOADING_START_DATETIME := p_grp_stop_rec.UNLOADING_START_DATETIME;
1871 x_pvt_stop_rec.UNLOADING_END_DATETIME := p_grp_stop_rec.UNLOADING_END_DATETIME;
1872 x_pvt_stop_rec.TRIP_NAME := p_grp_stop_rec.TRIP_NAME;
1873 x_pvt_stop_rec.STOP_LOCATION_CODE := p_grp_stop_rec.STOP_LOCATION_CODE;
1874 x_pvt_stop_rec.WEIGHT_UOM_DESC := p_grp_stop_rec.WEIGHT_UOM_DESC;
1875 x_pvt_stop_rec.VOLUME_UOM_DESC := p_grp_stop_rec.VOLUME_UOM_DESC;
1876 x_pvt_stop_rec.LOCK_STOP_ID := p_grp_stop_rec.LOCK_STOP_ID;
1877 x_pvt_stop_rec.PENDING_INTERFACE_FLAG := p_grp_stop_rec.PENDING_INTERFACE_FLAG;
1878 x_pvt_stop_rec.TRANSACTION_HEADER_ID := p_grp_stop_rec.TRANSACTION_HEADER_ID;
1879
1880 IF l_debug_on THEN
1881 WSH_DEBUG_SV.pop(l_module_name);
1882 END IF;
1883 EXCEPTION
1884 WHEN OTHERS THEN
1885 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_FTE_INTEGRATION.map_stopgrp_to_pvt',l_module_name);
1886 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1887 --
1888 IF l_debug_on THEN
1889 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1890 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1891 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1892 END IF;
1893 --
1894 END map_stopgrp_to_pvt;
1895
1896 --========================================================================
1897 -- PROCEDURE : Validate_Stop PRIVATE
1898 --
1899 -- PARAMETERS: p_rec_attr_tab IN OUT stop_attr_tab_type
1900 -- p_action_code IN 'CREATE', 'UPDATE'
1901 -- x_valid_id_index_tab OUT wsh_util_core.id_tab_type
1902 -- x_return_status return status
1903 -- COMMENT : This procedure takes tabe of Stops and validate them and return the validate stops in x_valid_id_index_tab.
1904 --========================================================================
1905 PROCEDURE Validate_Stop
1906 (p_rec_attr_tab IN OUT NOCOPY WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type,
1907 p_action_code IN VARCHAR2,
1908 p_caller IN VARCHAR2,
1909 x_valid_id_index_tab OUT NOCOPY wsh_util_core.id_tab_type,
1910 x_return_status OUT NOCOPY VARCHAR2) IS
1911
1912 l_debug_on BOOLEAN;
1913 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_STOP';
1914
1915 l_num_errors NUMBER := 0;
1916 l_num_warnings NUMBER := 0;
1917 l_index NUMBER;
1918 l_status_code VARCHAR2(100);
1919 l_action VARCHAR2(100);
1920 l_return_status VARCHAR2(1);
1921 x_msg_count NUMBER;
1922 x_msg_data varchar2(3000);
1923 l_trip_rec WSH_TRIPS_PVT.trip_rec_type;
1924 l_isWshLocation BOOLEAN DEFAULT FALSE;
1925
1926 WSH_STOP_VALIDATION EXCEPTION;
1927 e_mixed_stop_error EXCEPTION;
1928
1929 -- J-Stop Sequence Change-CSUN
1930 l_stop_details_rec WSH_TRIP_STOPS_VALIDATIONS.stop_details;
1931
1932 l_stop_id NUMBER;
1933
1934 BEGIN
1935 --
1936 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1937 --
1938 IF l_debug_on IS NULL
1939 THEN
1940 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1941 END IF;
1942 --
1943 SAVEPOINT validate_stop_grp;
1944 IF l_debug_on THEN
1945 wsh_debug_sv.push(l_module_name);
1946 WSH_DEBUG_SV.log(l_module_name,'p_action_code',p_action_code );
1947 END IF;
1948
1949 IF p_caller IN ( 'WSH_FSTRX','WSH_TPW_INBOUND')
1950 OR p_caller LIKE 'FTE%' THEN
1951 l_isWshLocation := TRUE;
1952 END IF;
1953 l_index := p_rec_attr_tab.FIRST;
1954 WHILE l_index IS NOT NULL LOOP
1955 BEGIN
1956 SAVEPOINT validate_stop_loop_grp;
1957
1958 IF l_debug_on THEN
1959 WSH_DEBUG_SV.log(l_module_name,'status_code',p_rec_attr_tab(l_index).status_code);
1960 WSH_DEBUG_SV.log(l_module_name,'stop_id',p_rec_attr_tab(l_index).stop_id);
1961 WSH_DEBUG_SV.log(l_module_name,'trip_id',p_rec_attr_tab(l_index).trip_id);
1962 WSH_DEBUG_SV.log(l_module_name,'trip_name',p_rec_attr_tab(l_index).trip_name);
1963 WSH_DEBUG_SV.log(l_module_name,'stop_location_id',p_rec_attr_tab(l_index).stop_location_id);
1964 WSH_DEBUG_SV.log(l_module_name,'stop_location_code',p_rec_attr_tab(l_index).stop_location_code);
1965 WSH_DEBUG_SV.log(l_module_name,'stop_sequence_number',p_rec_attr_tab(l_index).stop_sequence_number);
1966 WSH_DEBUG_SV.log(l_module_name,'weight_uom_code',p_rec_attr_tab(l_index).weight_uom_code);
1967 WSH_DEBUG_SV.log(l_module_name,'volume_uom_code',p_rec_attr_tab(l_index).volume_uom_code);
1968 WSH_DEBUG_SV.log(l_module_name,'planned_arrival_date',p_rec_attr_tab(l_index).planned_arrival_date);
1969 WSH_DEBUG_SV.log(l_module_name,'planned_departure_date',p_rec_attr_tab(l_index).planned_departure_date);
1970 END IF;
1971
1972 IF p_action_code = 'UPDATE' THEN
1973 l_status_code := p_rec_attr_tab(l_index).status_code;
1974 l_action := 'UPDATE';
1975 ELSE
1976 l_status_code := 'OP';
1977 l_action := 'ADD';
1978 END IF;
1979
1980 IF ( WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_STOP_NAME_LVL) = 1) THEN
1981 l_stop_id := p_rec_attr_tab(l_index).stop_id;
1982 WSH_UTIL_VALIDATE.Validate_Stop_Name (
1983 p_stop_id => l_stop_id,
1984 p_trip_id => p_rec_attr_tab(l_index).trip_id,
1985 p_stop_location_id => NULL, -- not needed
1986 p_planned_dep_date => NULL, -- not needed
1987 x_return_status => l_return_status);
1988 IF l_debug_on THEN
1989 WSH_DEBUG_SV.log(l_module_name,'WSH_UTIL_VALIDATE.Validate_Stop_Name l_return_status',l_return_status);
1990 END IF;
1991 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
1992 x_num_warnings =>l_num_warnings,
1993 x_num_errors =>l_num_errors);
1994
1995 END IF;
1996
1997 -- J-IB-NPARIKH-{
1998 IF ( WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_CREATE_MIXED_STOP_LVL) = 1 )
1999 THEN
2000 --{
2001 IF p_rec_attr_tab(l_index).shipments_type_flag = 'M'
2002 THEN
2003 -- You cannot create mixed stops through API
2004 --
2005 RAISE e_mixed_stop_error;
2006 END IF;
2007 --}
2008 END IF;
2009 --
2010 -- J-IB-NPARIKH-}
2011
2012 IF ( WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_TRIP_NAME_LVL) = 1 ) THEN
2013 WSH_UTIL_VALIDATE.Validate_Trip_Name (
2014 p_trip_id => p_rec_attr_tab(l_index).trip_id,
2015 p_trip_name => p_rec_attr_tab(l_index).trip_name,
2016 x_return_status => l_return_status);
2017 IF l_debug_on THEN
2018 WSH_DEBUG_SV.log(l_module_name,'WSH_UTIL_VALIDATE.Validate_Trip_Name l_return_status',l_return_status);
2019 END IF;
2020 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2021 x_num_warnings =>l_num_warnings,
2022 x_num_errors =>l_num_errors);
2023 END IF;
2024
2025 IF ( WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_TRIP_STATUS_LVL) = 1 ) THEN
2026 WSH_UTIL_VALIDATE.Validate_Trip_status (
2027 p_trip_id => p_rec_attr_tab(l_index).trip_id,
2028 p_action => p_action_code,
2029 x_return_status => l_return_status);
2030 IF l_debug_on THEN
2031 WSH_DEBUG_SV.log(l_module_name,'WSH_UTIL_VALIDATE.Validate_Trip_status l_return_status',l_return_status);
2032 END IF;
2033 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2034 x_num_warnings =>l_num_warnings,
2035 x_num_errors =>l_num_errors);
2036 END IF;
2037
2038 IF ( WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_LOCATION_LVL) = 1 ) THEN
2039
2040 -- Stop location id can be -1 for inbound stops
2041 -- in such case, we do not need to call validate location, as it wil fail
2042 -- in turn preventing any update of stop attributes
2043 --
2044 IF p_rec_attr_tab(l_index).stop_location_id <> WSH_UTIL_CORE.C_NULL_SF_LOCN_ID -- J-IB-NPARIKH
2045 OR p_rec_attr_tab(l_index).stop_location_id IS NULL
2046 THEN
2047 WSH_UTIL_VALIDATE.Validate_Location (
2048 p_location_id => p_rec_attr_tab(l_index).stop_location_id,
2049 p_location_code => p_rec_attr_tab(l_index).stop_location_code,
2050 x_return_status => l_return_status,
2051 p_isWshLocation => l_isWshLocation);
2052 IF l_debug_on THEN
2053 WSH_DEBUG_SV.log(l_module_name,'WSH_UTIL_VALIDATE.Validate_Location l_return_status',l_return_status);
2054 END IF;
2055 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2056 x_num_warnings =>l_num_warnings,
2057 x_num_errors =>l_num_errors);
2058 END IF;
2059 END IF;
2060
2061
2062 IF ( WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_PLANNED_TRIP_LVL) = 1 ) THEN
2063 wsh_trip_validations.validate_planned_trip
2064 (p_stop_id => p_rec_attr_tab(l_index).stop_id,
2065 p_stop_sequence_number => p_rec_attr_tab(l_index).stop_sequence_number,
2066 x_return_status => l_return_status);
2067
2068 IF l_debug_on THEN
2069 WSH_DEBUG_SV.log(l_module_name,'WSH_UTIL_VALIDATE.validate_planned_trip l_return_status',l_return_status);
2070 END IF;
2071 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2072 x_num_warnings =>l_num_warnings,
2073 x_num_errors =>l_num_errors);
2074 END IF;
2075
2076 IF ( WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_WEIGHT_UOM_LVL) = 1 ) THEN
2077 wsh_util_validate.validate_uom (
2078 'WEIGHT',
2079 NULL,
2080 p_rec_attr_tab(l_index).weight_uom_code,
2081 p_rec_attr_tab(l_index).weight_uom_desc,
2082 l_return_status);
2083 IF l_debug_on THEN
2084 WSH_DEBUG_SV.log(l_module_name,'WSH_UTIL_VALIDATE.validate_uom-WEIGHT l_return_status',l_return_status);
2085 END IF;
2086 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2087 x_num_warnings =>l_num_warnings,
2088 x_num_errors =>l_num_errors);
2089 END IF;
2090
2091 IF ( WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_VOLUME_UOM_LVL) = 1 ) THEN
2092 wsh_util_validate.validate_uom (
2093 'VOLUME',
2094 NULL,
2095 p_rec_attr_tab(l_index).volume_uom_code,
2096 p_rec_attr_tab(l_index).volume_uom_desc,
2097 l_return_status);
2098 IF l_debug_on THEN
2099 WSH_DEBUG_SV.log(l_module_name,'WSH_UTIL_VALIDATE.validate_uom-VOLUME l_return_status',l_return_status);
2100 END IF;
2101 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2102 x_num_warnings =>l_num_warnings,
2103 x_num_errors =>l_num_errors);
2104 END IF;
2105
2106
2107 IF ( WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_ARR_DEP_DATES_LVL) = 1 ) THEN
2108 WSH_UTIL_VALIDATE.validate_from_to_dates (
2109 p_from_date => p_rec_attr_tab(l_index).planned_arrival_date,
2110 p_to_date =>p_rec_attr_tab(l_index).planned_departure_date,
2111 x_return_status => l_return_status);
2112 IF l_debug_on THEN
2113 WSH_DEBUG_SV.log(l_module_name,'WSH_UTIL_VALIDATE.validate_from_to_dates l_return_status',l_return_status);
2114 END IF;
2115 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2116 x_num_warnings =>l_num_warnings,
2117 x_num_errors =>l_num_errors);
2118 END IF;
2119
2120 -- SSN change
2121 -- Add conditional validation for stop sequence number
2122 IF (WSH_TRIPS_ACTIONS.GET_STOP_SEQ_MODE = WSH_INTERFACE_GRP.G_STOP_SEQ_MODE_SSN) AND
2123 (WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_SEQ_NUM_LVL) = 1 ) THEN
2124
2125 IF (l_status_code = 'OP') THEN
2126 WSH_TRIP_STOPS_validations.validate_sequence_number (
2127 p_stop_id => p_rec_attr_tab(l_index).stop_id,
2128 p_stop_sequence_number => p_rec_attr_tab(l_index).stop_sequence_number,
2129 p_trip_id => p_rec_attr_tab(l_index).trip_id,
2130 p_status_code => l_status_code,
2131 x_return_status => l_return_status);
2132
2133 IF l_debug_on THEN
2134 WSH_DEBUG_SV.log(l_module_name,'WSH_UTIL_VALIDATE.Validate_SSN l_return_status',l_return_status);
2135 END IF;
2136 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2137 x_num_warnings =>l_num_warnings,
2138 x_num_errors =>l_num_errors);
2139 END IF;
2140 END IF;
2141
2142 IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y') THEN
2143 IF ( WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_TRIP_STOP_VALIDATION_LVL) = 1 ) THEN
2144 WSH_FTE_INTEGRATION.trip_stop_validations
2145 (p_stop_rec => p_rec_attr_tab(l_index),
2146 p_trip_rec => l_trip_rec,
2147 p_action => l_action,
2148 x_return_status => l_return_status);
2149 IF l_debug_on THEN
2150 WSH_DEBUG_SV.log(l_module_name,'WSH_UTIL_VALIDATE.Validate_Trip_Name l_return_status',l_return_status);
2151 END IF;
2152 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2153 x_num_warnings =>l_num_warnings,
2154 x_num_errors =>l_num_errors);
2155 END IF;
2156 END IF;
2157 --Bug 4140359
2158 IF NVL(p_caller, '-1') <> 'WSH_FSTRX' THEN --{
2159 IF nvl(p_rec_attr_tab(l_index).DEPARTURE_GROSS_WEIGHT,0) > 0
2160 OR
2161 nvl(p_rec_attr_tab(l_index).DEPARTURE_NET_WEIGHT,0) > 0 THEN
2162 IF p_rec_attr_tab(l_index). WEIGHT_UOM_CODE IS NULL THEN
2163 FND_MESSAGE.SET_NAME('WSH', 'WSH_WTVOL_NULL');
2164 WSH_UTIL_CORE.ADD_MESSAGE(wsh_util_core.g_ret_sts_error, l_module_name);
2165 RAISE fnd_api.g_exc_error;
2166 END IF;
2167 END IF;
2168 --Bug 4140359
2169 IF nvl(p_rec_attr_tab(l_index).DEPARTURE_VOLUME,0) > 0
2170 AND p_rec_attr_tab(l_index).VOLUME_UOM_CODE IS NULL THEN
2171 FND_MESSAGE.SET_NAME('WSH', 'WSH_WTVOL_NULL');
2172 WSH_UTIL_CORE.ADD_MESSAGE(wsh_util_core.g_ret_sts_error, l_module_name);
2173 RAISE fnd_api.g_exc_error;
2174 END IF;
2175 END IF; --}
2176 x_valid_id_index_tab(x_valid_id_index_tab.COUNT + 1) := l_index;
2177
2178 IF l_debug_on THEN
2179 WSH_DEBUG_SV.log(l_module_name,'l_num_errors',l_num_errors);
2180 WSH_DEBUG_SV.log(l_module_name,'l_index',l_index);
2181 END IF;
2182 EXCEPTION
2183 -- J-IB-NPARIKH-{
2184 WHEN e_mixed_Stop_error THEN
2185 ROLLBACK TO validate_stop_loop_grp;
2186 l_num_errors := l_num_errors + 1;
2187 FND_MESSAGE.SET_NAME('WSH', 'WSH_MIXED_STOP_ERROR');
2188 WSH_UTIL_CORE.ADD_MESSAGE(wsh_util_core.g_ret_sts_error, l_module_name);
2189 IF l_debug_on THEN
2190 WSH_DEBUG_SV.logmsg(l_module_name,'e_mixed_Stop_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2191 END IF;
2192 -- J-IB-NPARIKH-}
2193
2194 WHEN fnd_api.g_exc_error THEN
2195 Rollback to validate_stop_loop_grp;
2196
2197 WHEN fnd_api.g_exc_unexpected_error THEN
2198 Rollback to validate_stop_loop_grp;
2199
2200 WHEN others THEN
2201 ROLLBACK TO SAVEPOINT validate_stop_loop_grp;
2202 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2203
2204 END;
2205 l_index := p_rec_attr_tab.NEXT(l_index);
2206 END LOOP;
2207
2208 IF (l_num_errors = p_rec_attr_tab.count ) THEN
2209 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2210 ELSIF (l_num_errors > 0 ) THEN
2211 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2212 ELSIF (l_num_warnings > 0 ) THEN
2213 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2214 ELSE
2215 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2216 END IF;
2217
2218
2219 IF l_debug_on THEN
2220 WSH_DEBUG_SV.pop(l_module_name);
2221 END IF;
2222 EXCEPTION
2223 WHEN FND_API.G_EXC_ERROR THEN
2224 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
2225 FND_MSG_PUB.Count_And_Get (
2226 p_count => x_msg_count,
2227 p_data => x_msg_data,
2228 p_encoded => FND_API.G_FALSE);
2229 IF l_debug_on THEN
2230 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2231 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2232 END IF;
2233 ROLLBACK TO SAVEPOINT validate_stop_grp;
2234
2235 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2236 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2237 FND_MSG_PUB.Count_And_Get (
2238 p_count => x_msg_count,
2239 p_data => x_msg_data,
2240 p_encoded => FND_API.G_FALSE);
2241 IF l_debug_on THEN
2242 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2243 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2244 END IF;
2245 ROLLBACK TO SAVEPOINT validate_stop_grp;
2246
2247 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
2248 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2249 FND_MSG_PUB.Count_And_Get (
2250 p_count => x_msg_count,
2251 p_data => x_msg_data,
2252 p_encoded => FND_API.G_FALSE);
2253 IF l_debug_on THEN
2254 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2255 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2256 END IF;
2257
2258 WHEN OTHERS THEN
2259 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2260 wsh_util_core.default_handler ('WSH_TRIP_STOPS_GRP.Validate_Stop');
2261 FND_MSG_PUB.Count_And_Get (
2262 p_count => x_msg_count,
2263 p_data => x_msg_data,
2264 p_encoded => FND_API.G_FALSE);
2265 IF l_debug_on THEN
2266 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
2267 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2268 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2269 END IF;
2270 ROLLBACK TO SAVEPOINT validate_stop_grp;
2271 END Validate_Stop;
2272
2273 --========================================================================
2274 -- PROCEDURE : Create_Update_Stop CORE API
2275 --
2276 -- PARAMETERS: p_api_version_number known api versionerror buffer
2277 -- p_init_msg_list FND_API.G_TRUE to reset list
2278 -- p_commit 'T'/'F'
2279 -- p_in_rec stopInRecType
2280 -- p_rec_attr_tab Table of Attributes for the stop entity
2281 -- p_stop_OUT_tab Table of Output Attributes for the stop entity
2282 -- x_return_status return status
2283 -- x_msg_count number of messages in the list
2284 -- x_msg_data text of messages
2285 -- VERSION : current version 1.0
2286 -- initial version 1.0
2287 -- COMMENT : This is the core CREATE_UPDATE_STOP procedure introduce as a part of Harmonizing Project in patchset I.
2288 -- All other CREATE_UPDATE_STOP procedures will call this one only.
2289 --========================================================================
2290 PROCEDURE CREATE_UPDATE_STOP(
2291 p_api_version_number IN NUMBER,
2292 p_init_msg_list IN VARCHAR2,
2293 p_commit IN VARCHAR2,
2294 p_in_rec IN stopInRecType,
2295 p_rec_attr_tab IN WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type,
2296 x_stop_out_tab OUT NOCOPY stop_out_tab_type,
2297 x_return_status OUT NOCOPY VARCHAR2,
2298 x_msg_count OUT NOCOPY NUMBER,
2299 x_msg_data OUT NOCOPY VARCHAR2,
2300 x_stop_wt_vol_out_tab OUT NOCOPY Stop_Wt_Vol_tab_type --bug 2796095
2301 ) IS
2302
2303 l_api_version_number CONSTANT NUMBER := 1.0;
2304 l_api_name CONSTANT VARCHAR2(30) := 'Create_Update_Stop';
2305 l_debug_on BOOLEAN;
2306 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_STOP';
2307 RECORD_LOCKED EXCEPTION;
2308 PRAGMA EXCEPTION_INIT(RECORD_LOCKED, -54);
2309
2310 l_trip_id NUMBER;
2311 i NUMBER;
2312 l_num_errors NUMBER := 0;
2313 l_num_warnings NUMBER := 0;
2314 l_return_status VARCHAR2(1);
2315 l_index NUMBER;
2316 l_rec_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
2317 x_index_id_tab wsh_util_core.id_tab_type;
2318
2319 --Compatibility Changes
2320 l_cc_validate_result VARCHAR2(1);
2321 l_cc_failed_records WSH_FTE_COMP_CONSTRAINT_PKG.failed_line_tab_type;
2322 l_cc_line_groups WSH_FTE_COMP_CONSTRAINT_PKG.line_group_tab_type;
2323 l_cc_group_info WSH_FTE_COMP_CONSTRAINT_PKG.cc_group_tab_type;
2324
2325 l_rec_attr_tab_temp WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
2326 l_cc_count_success NUMBER;
2327 b_cc_linefailed BOOLEAN;
2328 l_msg_count NUMBER;
2329 l_msg_data VARCHAR2(2000);
2330
2331 --dummy tables for calling validate_constraint_mainper
2332 l_cc_del_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
2333 l_cc_det_attr_tab wsh_glbl_var_strct_grp.Delivery_Details_Attr_Tbl_Type;
2334 l_cc_trip_attr_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
2335 l_cc_stop_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
2336 l_cc_in_ids wsh_util_core.id_tab_type;
2337 l_cc_fail_ids wsh_util_core.id_tab_type;
2338
2339 --Compatibility Changes
2340 -- csun 10+
2341 l_trip_id_tab wsh_util_core.id_tab_type;
2342 l_success_trip_ids wsh_util_core.id_tab_type;
2343 l_trips wsh_util_core.id_tab_type;
2344 l_found BOOLEAN;
2345
2346 --TL Rating
2347 l_details_marked WSH_UTIL_CORE.Id_Tab_Type;
2348 --TL Rating
2349
2350 l_action_prms action_parameters_rectype;
2351 l_stop_rec WSH_TRIP_STOPS_PVT.trip_stop_rec_type;
2352 l_internal_stop_rec WSH_TRIP_STOPS_PVT.trip_stop_rec_type;
2353
2354 --bug 2796095
2355 CURSOR c_get_all_trip_stops (p_trip_id IN NUMBER) IS
2356 SELECT stop_id,
2357 departure_gross_weight,
2358 departure_net_weight,
2359 departure_volume,
2360 departure_fill_percent
2361 FROM wsh_trip_stops
2362 WHERE trip_id = p_trip_id ;
2363
2364 -- bug 3848771
2365 CURSOR c_trip_info (p_trip_id in number) is
2366 SELECT mode_of_transport,
2367 NVL(ignore_for_planning,'N'), -- OTM R12,glog project
2368 tp_plan_name
2369 FROM wsh_trips
2370 WHERE trip_id = p_trip_id;
2371
2372 --OTM R12, glog proj
2373 l_ignore WSH_TRIPS.IGNORE_FOR_PLANNING%TYPE;
2374 l_tp_plan_name WSH_TRIPS.TP_PLAN_NAME%TYPE;
2375 l_gc3_is_installed VARCHAR2(1);
2376 e_gc3_trip_exception EXCEPTION;
2377 --OTM R12, end of glog proj
2378
2379
2380 l_stop_id NUMBER;
2381 l_stop_index NUMBER :=0;
2382 --bug 2796095
2383 l_status_code VARCHAR2(100);
2384 l_stop_details_rec WSH_TRIP_STOPS_VALIDATIONS.stop_details;
2385 l_handle_internal_stops BOOLEAN;
2386 l_reset_stop_sequence BOOLEAN;
2387
2388 get_physical_loc_err EXCEPTION;
2389
2390 -- bug 3848771
2391 l_mode_of_transport WSH_TRIPS.MODE_OF_TRANSPORT%TYPE;
2392 --Bugfix 4070732
2393 l_api_session_name CONSTANT VARCHAR2(150) := G_PKG_NAME ||'.' || l_module_name;
2394 l_reset_flags BOOLEAN;
2395
2396 l_stop_seq_mode NUMBER; -- SSN change
2397
2398 -- K LPN CONV. rv
2399 l_lpn_in_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_in_rec_type;
2400 l_lpn_out_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_out_rec_type;
2401 -- K LPN CONV. rv
2402
2403 BEGIN
2404 IF WSH_UTIL_CORE.G_START_OF_SESSION_API is null THEN --Bugfix 4070732
2405 WSH_UTIL_CORE.G_START_OF_SESSION_API := l_api_session_name;
2406 WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API := FALSE;
2407 END IF;
2408 --
2409 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2410 --
2411 IF l_debug_on IS NULL
2412 THEN
2413 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2414 END IF;
2415 --
2416 l_stop_seq_mode := WSH_TRIPS_ACTIONS.GET_STOP_SEQ_MODE; -- SSN change
2417
2418 SAVEPOINT create_update_stop_grp;
2419
2420 l_trips.delete;
2421
2422 IF l_debug_on THEN
2423 wsh_debug_sv.push (l_module_name, 'Create_Update_Stop');
2424 wsh_debug_sv.log (l_module_name,'p_in_rec.action_code',p_in_rec.action_code);
2425 wsh_debug_sv.log (l_module_name,'p_in_rec.caller',p_in_rec.caller);
2426 wsh_debug_sv.log (l_module_name,'p_in_rec.phase',p_in_rec.phase);
2427 wsh_debug_sv.log (l_module_name,'(p_rec_attr_tab.count',p_rec_attr_tab.count);
2428 END IF;
2429
2430 IF NOT FND_API.Compatible_API_Call(l_api_version_number, p_api_version_number,l_api_name,G_PKG_NAME) THEN
2431 IF l_debug_on THEN
2432 WSH_DEBUG_SV.logmsg(l_module_name,'Not compatible');
2433 END IF;
2434 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2435 END IF;
2436
2437 IF FND_API.to_Boolean(p_init_msg_list) THEN
2438 FND_MSG_PUB.initialize;
2439 END IF;
2440
2441 -- Initialize the Variables
2442 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2443
2444 --OTM R12, glog proj, use Global Variable
2445 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
2446
2447 -- If null, call the function
2448 IF l_gc3_is_installed IS NULL THEN
2449 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
2450 END IF;
2451 -- end of OTM R12, glog proj
2452
2453
2454
2455 IF (p_in_rec.caller IS NULL) THEN
2456 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
2457 FND_MESSAGE.SET_TOKEN('FIELD_NAME','p_in_rec.caller');
2458 wsh_util_core.add_message(x_return_status,l_module_name);
2459 raise fnd_api.g_exc_error;
2460 END IF;
2461 IF (p_in_rec.action_code IS NULL OR p_in_rec.action_code NOT IN ('CREATE','UPDATE') ) THEN
2462 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
2463 FND_MESSAGE.SET_TOKEN('FIELD_NAME','p_in_rec.action_code');
2464 wsh_util_core.add_message(x_return_status,l_module_name);
2465 raise fnd_api.g_exc_error;
2466 END IF;
2467
2468 IF (nvl(p_in_rec.phase,1) < 1) THEN
2469 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
2470 FND_MESSAGE.SET_TOKEN('FIELD_NAME','p_in_rec.phase');
2471 wsh_util_core.add_message(x_return_status,l_module_name);
2472 raise fnd_api.g_exc_error;
2473 END IF;
2474
2475 IF (p_rec_attr_tab.count < 0 ) THEN
2476 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
2477 FND_MESSAGE.SET_TOKEN('FIELD_NAME','p_rec_attr_tab.count');
2478 wsh_util_core.add_message(x_return_status,l_module_name);
2479 raise fnd_api.g_exc_error;
2480 END IF;
2481
2482 WSH_ACTIONS_LEVELS.set_validation_level (
2483 p_entity => 'STOP',
2484 p_caller => p_in_rec.caller,
2485 p_phase => p_in_rec.phase,
2486 p_action => p_in_rec.action_code,
2487 x_return_status => l_return_status);
2488
2489 IF l_debug_on THEN
2490 WSH_DEBUG_SV.log(l_module_name,'WSH_ACTIONS_LEVELS.set_validation_level l_return_status',l_return_status);
2491 END IF;
2492
2493 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2494 x_num_warnings =>l_num_warnings,
2495 x_num_errors =>l_num_errors);
2496
2497
2498 IF ( WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_DISABLED_LIST_LVL) = 1 ) THEN
2499 l_index := p_rec_attr_tab.FIRST;
2500 WHILE l_index IS NOT NULL LOOP
2501 BEGIN
2502 SAVEPOINT s_stop_disabled_list_grp;
2503 IF l_debug_on THEN
2504 WSH_DEBUG_SV.log(l_module_name,'stop_id,trip_id',p_rec_attr_tab(l_index).stop_id||','||
2505 p_rec_attr_tab(l_index).trip_id);
2506 END IF;
2507
2508 WSH_TRIP_STOPS_validations.get_disabled_list(
2509 p_stop_rec => p_rec_attr_tab(l_index),
2510 p_parent_entity_id => p_rec_attr_tab(l_index).trip_id,
2511 p_in_rec => p_in_rec,
2512 x_return_status => l_return_status,
2513 x_msg_count => x_msg_count,
2514 x_msg_data => x_msg_data,
2515 x_stop_rec => l_rec_attr_tab(l_index));
2516
2517 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2518 x_num_warnings =>l_num_warnings,
2519 x_num_errors =>l_num_errors);
2520
2521 EXCEPTION
2522 WHEN fnd_api.g_exc_error THEN
2523 Rollback to s_stop_disabled_list_grp;
2524
2525 WHEN fnd_api.g_exc_unexpected_error THEN
2526 Rollback to s_stop_disabled_list_grp;
2527
2528 WHEN others THEN
2529 Rollback to s_stop_disabled_list_grp;
2530 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2531 END;
2532
2533 l_index := p_rec_attr_tab.NEXT(l_index);
2534 END LOOP;
2535
2536 IF (l_num_errors = p_rec_attr_tab.count ) THEN
2537 raise fnd_api.g_exc_error;
2538 END IF;
2539
2540 ELSE
2541 l_rec_attr_tab := p_rec_attr_tab;
2542 END IF;
2543
2544 IF l_debug_on THEN
2545 WSH_DEBUG_SV.log(l_module_name,'count, l_num_errors',l_rec_attr_tab.COUNT ||','||l_num_errors);
2546 END IF;
2547
2548 IF WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_VALIDATE_CONSTRAINTS_LVL) = 1 THEN --{
2549 --Compatiblity Changes
2550 IF (wsh_util_core.fte_is_installed = 'Y') THEN
2551
2552 -- populate physical_location_id before validate_constraint_main
2553 i := l_rec_attr_tab.first;
2554 WHILE i is NOT NULL LOOP
2555
2556 WSH_LOCATIONS_PKG.Convert_internal_cust_location(
2557 p_internal_cust_location_id => l_rec_attr_tab(i).stop_location_id,
2558 x_internal_org_location_id => l_rec_attr_tab(i).physical_location_id,
2559 x_return_status => l_return_status);
2560
2561 IF l_return_status in (FND_API.G_RET_STS_UNEXP_ERROR, FND_API.G_RET_STS_ERROR) THEN
2562 RAISE get_physical_loc_err;
2563 END IF;
2564 i := l_rec_attr_tab.next(i);
2565 END LOOP;
2566
2567
2568 WSH_FTE_COMP_CONSTRAINT_PKG.validate_constraint_main(
2569 p_api_version_number => p_api_version_number,
2570 p_init_msg_list => FND_API.G_FALSE,
2571 p_entity_type => 'S',
2572 p_target_id => null,
2573 p_action_code => p_in_rec.action_code,
2574 p_del_attr_tab => l_cc_del_attr_tab,
2575 p_det_attr_tab => l_cc_det_attr_tab,
2576 p_trip_attr_tab => l_cc_trip_attr_tab,
2577 p_stop_attr_tab => l_rec_attr_tab,
2578 p_in_ids => l_cc_in_ids,
2579 x_fail_ids => l_cc_fail_ids,
2580 x_validate_result => l_cc_validate_result,
2581 x_failed_lines => l_cc_failed_records,
2582 x_line_groups => l_cc_line_groups,
2583 x_group_info => l_cc_group_info,
2584 x_msg_count => l_msg_count,
2585 x_msg_data => l_msg_data,
2586 x_return_status => l_return_status);
2587
2588 IF l_debug_on THEN
2589 wsh_debug_sv.log(l_module_name,'Return Status After Calling validate_constraint_main',l_return_status);
2590 wsh_debug_sv.log(l_module_name,'validate_result After Calling validate_constraint_main',l_cc_validate_result);
2591 wsh_debug_sv.log(l_module_name,'msg_count After Calling validate_constraint_main',l_msg_count);
2592 wsh_debug_sv.log(l_module_name,'msg_data After Calling validate_constraint_main',l_msg_data);
2593 wsh_debug_sv.log(l_module_name,'fail_ids count After Calling validate_constraint_main',l_cc_fail_ids.COUNT);
2594 wsh_debug_sv.log(l_module_name,'l_cc_line_groups.count count After Calling validate_constraint_main',l_cc_line_groups.COUNT);
2595 wsh_debug_sv.log(l_module_name,'group_info count After Calling validate_constraint_main',l_cc_group_info.COUNT);
2596 END IF;
2597 --
2598
2599 --fix p_rec_attr_tab to have only successful records if there are some failed lines
2600 IF l_return_status=wsh_util_core.g_ret_sts_error THEN
2601 l_cc_count_success:=1;
2602
2603 IF l_debug_on THEN
2604 wsh_debug_sv.log(l_module_name,'l_rec_attr_tab count before removing failed lines',l_rec_attr_tab.COUNT);
2605 END IF;
2606
2607 IF l_cc_fail_ids.COUNT>0 AND l_rec_attr_tab.COUNT>0 THEN
2608 IF l_cc_fail_ids.COUNT=l_rec_attr_tab.COUNT THEN
2609 IF l_debug_on THEN
2610 wsh_debug_sv.logmsg(l_module_name,'all stops failed compatibility check');
2611 END IF;
2612 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_COMP_FAILED');
2613 wsh_util_core.api_post_call(
2614 p_return_status => l_return_status,
2615 x_num_warnings => l_num_warnings,
2616 x_num_errors => l_num_errors,
2617 p_msg_data => l_msg_data);
2618 ELSE
2619 l_return_status:=wsh_util_core.g_ret_sts_warning;
2620 END IF;
2621
2622 FOR i in l_rec_attr_tab.FIRST..l_rec_attr_tab.LAST LOOP
2623 b_cc_linefailed:=FALSE;
2624
2625 FOR j in l_cc_fail_ids.FIRST..l_cc_fail_ids.LAST LOOP
2626 --for create, in WSHFTCCB, dummy stop_id (index of l_rec_attr_tab)
2627 --is passed so use that to remove rec
2628 IF (p_in_rec.action_code='CREATE' AND l_rec_attr_tab(i).stop_id is null
2629 AND i=l_cc_fail_ids(j)) THEN
2630 b_cc_linefailed:=TRUE;
2631 IF l_debug_on THEN
2632 wsh_debug_sv.logmsg(l_module_name,'compatibility check failed for stop create');
2633 END IF;
2634 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_COMP_FAILED');
2635 ELSIF (l_rec_attr_tab(i).stop_id=l_cc_fail_ids(j)) THEN
2636 b_cc_linefailed:=TRUE;
2637 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_COMP_FAILED');
2638 FND_MESSAGE.SET_TOKEN('STOP_ID',l_cc_fail_ids(j));
2639 END IF;
2640 END LOOP;--fail_ids
2641
2642 IF (NOT(b_cc_linefailed)) THEN
2643 l_rec_attr_tab_temp(l_cc_count_success):=l_rec_attr_tab(i);
2644 l_cc_count_success:=l_cc_count_success+1;
2645 END IF;
2646 END LOOP;--l_rec_attr_tab
2647
2648 IF l_rec_attr_tab_temp.COUNT>0 THEN
2649 l_rec_attr_tab:=l_rec_attr_tab_temp;
2650 END IF;
2651
2652 ELSE
2653 l_return_status:=wsh_util_core.g_ret_sts_warning;
2654 END IF; --fail_ids count>0
2655
2656
2657 IF l_debug_on THEN
2658 wsh_debug_sv.log(l_module_name,'l_rec_attr_tab count after removing failed lines',l_rec_attr_tab.COUNT);
2659 END IF;
2660
2661 END IF;--error
2662
2663 wsh_util_core.api_post_call(
2664 p_return_status => l_return_status,
2665 x_num_warnings => l_num_warnings,
2666 x_num_errors => l_num_errors,
2667 p_msg_data => l_msg_data);
2668
2669 END IF;
2670 --Compatiblity Changes
2671 END IF; --}
2672
2673 l_num_errors :=0;
2674
2675 Validate_Stop(
2676 p_rec_attr_tab => l_rec_attr_tab,
2677 p_action_code => p_in_rec.action_code,
2678 p_caller => p_in_rec.caller,
2679 x_valid_id_index_tab => x_index_id_tab,
2680 x_return_status => l_return_status);
2681
2682 IF l_debug_on THEN
2683 WSH_DEBUG_SV.log(l_module_name,'Validate_stop l_return_status',l_return_status);
2684 END IF;
2685
2686 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2687 x_num_warnings =>l_num_warnings,
2688 x_num_errors =>l_num_errors);
2689
2690
2691 l_index := x_index_id_tab.FIRST;
2692
2693 WHILE l_index IS NOT NULL LOOP
2694 BEGIN--{
2695 SAVEPOINT s_trip_stop_grp;
2696
2697 OPEN c_trip_info(l_rec_attr_tab(x_index_id_tab(l_index)).trip_id);
2698 -- OTM R12, glog proj
2699 FETCH c_trip_info INTO l_mode_of_transport,l_ignore,l_tp_plan_name;
2700 IF c_trip_info%NOTFOUND THEN
2701 CLOSE c_trip_info;
2702 RAISE no_data_found;
2703 END IF;
2704 CLOSE c_trip_info;
2705 IF (p_in_rec.action_code = 'CREATE' ) THEN
2706
2707 -- OTM R12, glog project
2708 -- Do not allow creation of Trip Stop for GC3 created trips
2709 -- from UI or Public API, only allowed from Inbound Message
2710 -- received from GC3
2711 IF l_debug_on THEN
2712 WSH_DEBUG_SV.log(l_module_name,'l_ignore',l_ignore);
2713 WSH_DEBUG_SV.log(l_module_name,'l_tp_plan_name',l_tp_plan_name);
2714 WSH_DEBUG_SV.log(l_module_name,'l_gc3_is_installed',l_gc3_is_installed);
2715 END IF;
2716
2717 IF (l_ignore = 'N' AND
2718 l_tp_plan_name IS NOT NULL AND
2719 l_gc3_is_installed = 'Y' AND
2720 p_in_rec.caller <> 'FTE_TMS_INTEGRATION') THEN
2721 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2722 RAISE e_gc3_trip_exception;
2723 END IF;
2724 --
2725
2726 WSH_TRIP_STOPS_PVT.create_trip_stop (
2727 p_trip_stop_info => l_rec_attr_tab(x_index_id_tab(l_index)),
2728 x_rowid => x_stop_out_tab(l_index).rowid,
2729 x_stop_id => x_stop_out_tab(l_index).stop_id,
2730 x_return_status => l_return_status);
2731
2732 wsh_util_core.api_post_call(p_return_status =>l_return_status,
2733 x_num_warnings =>l_num_warnings,
2734 x_num_errors =>l_num_errors);
2735
2736
2737 IF l_rec_attr_tab(x_index_id_tab(l_index)).trip_id is not NULL THEN
2738 l_trips(1) := l_rec_attr_tab(x_index_id_tab(l_index)).trip_id;
2739 WSH_TRIPS_ACTIONS.Handle_Internal_Stops(
2740 p_trip_ids => l_trips,
2741 p_caller => 'WSH_CREATE_TRIP_STOP',
2742 x_success_trip_ids => l_success_trip_ids,
2743 x_return_status => l_return_status);
2744
2745 wsh_util_core.api_post_call(
2746 p_return_status => l_return_status,
2747 x_num_warnings => l_num_warnings,
2748 x_num_errors => l_num_errors);
2749
2750 -- 4106444 -skattama
2751 -- After Trip is created and internal location is linked
2752 -- If mode is other than TRUCK, the stops should not be greater than 2
2753 IF( WSH_UTIL_CORE.FTE_Is_Installed = 'Y') THEN
2754 WSH_UTIL_VALIDATE.Validate_Trip_MultiStops (
2755 p_trip_id => l_rec_attr_tab(x_index_id_tab(l_index)).trip_id,
2756 p_mode_of_transport => l_mode_of_transport,
2757 x_return_status => l_return_status);
2758 IF l_debug_on THEN
2759 WSH_DEBUG_SV.log(l_module_name,'WSH_UTIL_VALIDATE.Validate_MultiStops l_return_status',l_return_status);
2760 END IF;
2761 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2762 x_num_warnings =>l_num_warnings,
2763 x_num_errors =>l_num_errors);
2764 END IF;
2765 -- end 4106444
2766 END IF;
2767
2768 ELSIF (p_in_rec.action_code = 'UPDATE' ) THEN
2769 -- csun start of stop sequence change
2770 l_handle_internal_stops := FALSE;
2771 l_reset_stop_sequence := FALSE;
2772 get_stop_details_pvt
2773 (p_stop_id => l_rec_attr_tab(x_index_id_tab(l_index)).stop_id,
2774 x_stop_rec => l_stop_rec,
2775 x_return_status => l_return_status);
2776
2777 wsh_util_core.api_post_call(p_return_status =>l_return_status,
2778 x_num_warnings =>l_num_warnings,
2779 x_num_errors =>l_num_errors);
2780
2781 -- begin csun 10+ internal location
2782 IF l_stop_rec.stop_location_id <> l_rec_attr_tab(x_index_id_tab(l_index)).stop_location_id THEN
2783 IF l_debug_on THEN
2784 wsh_debug_sv.logmsg(l_module_name,'stop location id has been changed');
2785 END IF;
2786 WSH_LOCATIONS_PKG.Convert_internal_cust_location(
2787 p_internal_cust_location_id => l_rec_attr_tab(x_index_id_tab(l_index)).stop_location_id,
2788 x_internal_org_location_id => l_rec_attr_tab(x_index_id_tab(l_index)).physical_location_id,
2789 x_return_status => l_return_status);
2790
2791 IF l_return_status in (FND_API.G_RET_STS_UNEXP_ERROR, FND_API.G_RET_STS_ERROR) THEN
2792 RAISE get_physical_loc_err;
2793 END IF;
2794 IF l_debug_on THEN
2795 wsh_debug_sv.log(l_module_name,'l_stop_rec.physical_location_id' , l_stop_rec.physical_location_id);
2796 wsh_debug_sv.log(l_module_name,'l_rec_attr_tab(x_index_id_tab(l_index)).physical_location_id' ,l_rec_attr_tab(x_index_id_tab(l_index)).physical_location_id);
2797 END IF;
2798 -- delink the physical stop id
2799 IF nvl(l_stop_rec.physical_location_id, -99) <> nvl(l_rec_attr_tab(x_index_id_tab(l_index)).physical_location_id, -99) THEN
2800 l_stop_rec.physical_stop_id := NULL;
2801 l_rec_attr_tab(x_index_id_tab(l_index)).physical_stop_id := NULL;
2802 l_stop_rec.physical_location_id := l_rec_attr_tab(x_index_id_tab(l_index)).physical_location_id;
2803 END IF;
2804 l_handle_internal_stops := TRUE;
2805 END IF;
2806
2807 -- end csun 10+ internal location
2808
2809 WSH_TRIP_STOPS_PVT.UPDATE_TRIP_STOP (
2810 p_rowid => l_rec_attr_tab(x_index_id_tab(l_index)).rowid,
2811 p_stop_info => l_rec_attr_tab(x_index_id_tab(l_index)),
2812 x_return_status => l_return_status);
2813
2814 wsh_util_core.api_post_call(p_return_status =>l_return_status,
2815 x_num_warnings =>l_num_warnings,
2816 x_num_errors =>l_num_errors);
2817
2818 -- bug 4253803: change in planned departure date needs to be validated.
2819 IF (l_stop_rec.planned_arrival_date <> l_rec_attr_tab(x_index_id_tab(l_index)).planned_arrival_date)
2820 OR (l_stop_rec.planned_departure_date <> l_rec_attr_tab(x_index_id_tab(l_index)).planned_departure_date) THEN
2821
2822 -- if mode is PAD, changing PAD may resequence.
2823 -- if mode is SSN, linked dummy stop's planned dates have
2824 -- to be synchronized (which will also take care of SSN changes).
2825
2826 IF l_debug_on THEN
2827 wsh_debug_sv.logmsg(l_module_name,'PAD or PDD has been changed');
2828 wsh_debug_sv.logmsg(l_module_name,'Physical Loc'||l_rec_attr_tab(x_index_id_tab(l_index)).physical_location_id);
2829 END IF;
2830
2831 -- begin csun 10+ internal location
2832 -- if the dates of physical stop is changed, change the dates of
2833 -- corresponding internal stop
2834 -- Always set handle_internal_stops to TRUE
2835 -- this calls reset/validate APIs as well
2836 -- Try updating planned arrival dates for both physical/dummy
2837 -- stop while testing this piece of code
2838 l_handle_internal_stops := TRUE;
2839 -- end csun 10+ internal location
2840 ELSIF (l_stop_seq_mode = WSH_INTERFACE_GRP.G_STOP_SEQ_MODE_SSN
2841 AND l_stop_rec.stop_sequence_number <> l_rec_attr_tab(x_index_id_tab(l_index)).stop_sequence_number) THEN
2842 -- if mode is SSN and SSN alone is changed,
2843 -- need to synchronize linked dummy stops.
2844
2845 IF l_debug_on THEN
2846 wsh_debug_sv.logmsg(l_module_name,'SSN has been changed');
2847 wsh_debug_sv.logmsg(l_module_name,'Physical Loc'||l_rec_attr_tab(x_index_id_tab(l_index)).physical_location_id);
2848 END IF;
2849
2850 -- if the SSN of physical stop is changed, change the SSN of
2851 -- corresponding internal stop
2852 -- Always set handle_internal_stops to TRUE
2853 -- Try updating SSN for both physical/dummy
2854 -- stop while testing this piece of code
2855 l_handle_internal_stops := TRUE;
2856 END IF;
2857
2858 IF l_handle_internal_stops THEN
2859 IF l_debug_on THEN
2860 wsh_debug_sv.logmsg(l_module_name,'handle internal stop TRUE');
2861 END IF;
2862 l_trips(1) := l_rec_attr_tab(x_index_id_tab(l_index)).trip_id;
2863
2864 WSH_TRIPS_ACTIONS.Handle_Internal_Stops(
2865 p_trip_ids => l_trips,
2866 p_caller => p_in_rec.caller,
2867 x_success_trip_ids => l_success_trip_ids,
2868 x_return_status => l_return_status);
2869
2870 wsh_util_core.api_post_call(
2871 p_return_status => l_return_status,
2872 x_num_warnings => l_num_warnings,
2873 x_num_errors => l_num_errors);
2874
2875 END IF;
2876 -- csun end of stop sequence change
2877 -- 4106444 - skattama
2878 -- After Trip is updated with stop and internal location is linked/delinked
2879 -- If mode is other than TRUCK, the stops of the trip should not be greater than 2
2880 IF( WSH_UTIL_CORE.FTE_Is_Installed = 'Y') THEN
2881 WSH_UTIL_VALIDATE.Validate_Trip_MultiStops (
2882 p_trip_id => l_rec_attr_tab(x_index_id_tab(l_index)).trip_id,
2883 p_mode_of_transport => l_mode_of_transport,
2884 x_return_status => l_return_status);
2885 IF l_debug_on THEN
2886 WSH_DEBUG_SV.log(l_module_name,'WSH_UTIL_VALIDATE.Validate_MultiStops l_return_status',l_return_status);
2887 END IF;
2888 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2889 x_num_warnings =>l_num_warnings,
2890 x_num_errors =>l_num_errors);
2891 END IF;
2892 -- end 4106444
2893 END IF;
2894
2895
2896 /* moved validate_squence_number here */
2897 IF p_in_rec.action_code = 'UPDATE' THEN
2898 l_status_code := l_rec_attr_tab(l_index).status_code;
2899 ELSE
2900 l_status_code := 'OP';
2901 END IF;
2902
2903 IF l_debug_on THEN
2904 wsh_debug_sv.logmsg(l_module_name,p_in_rec.action_code);
2905 wsh_debug_sv.log(l_module_name,'WSH_UTIL_CORE.FTE_Is_Installed', WSH_UTIL_CORE.FTE_Is_Installed);
2906 END IF;
2907
2908 --TL Rating
2909 IF (p_in_rec.action_code = 'CREATE' ) THEN
2910 l_details_marked(l_details_marked.COUNT+1):=x_stop_out_tab(l_index).stop_id;
2911 ELSIF (p_in_rec.action_code = 'UPDATE' ) THEN
2912
2913 IF( WSH_UTIL_CORE.FTE_Is_Installed = 'Y') THEN
2914 -- Bug 3848771
2915 -- Mark as reprice required only when certain conditions are met
2916 -- during update.
2917
2918 IF l_debug_on THEN
2919 wsh_debug_sv.log(l_module_name,'l_rec_attr_tab(x_index_id_tab(l_index)).planned_arrival_date', l_rec_attr_tab(x_index_id_tab(l_index)).planned_arrival_date);
2920 wsh_debug_sv.log(l_module_name,'l_rec_attr_tab(x_index_id_tab(l_index)).planned_departure_date', l_rec_attr_tab(x_index_id_tab(l_index)).planned_departure_date);
2921 wsh_debug_sv.log(l_module_name,'l_stop_rec.planned_arrival_date', l_stop_rec.planned_arrival_date);
2922 wsh_debug_sv.log(l_module_name,'l_stop_rec.planned_departure_date', l_stop_rec.planned_departure_date);
2923 END IF;
2924
2925
2926 IF (NVL(l_rec_attr_tab(x_index_id_tab(l_index)).planned_arrival_date, FND_API.G_MISS_DATE) <>
2927 NVL(l_stop_rec.planned_arrival_date, FND_API.G_MISS_DATE))
2928 OR (NVL(l_rec_attr_tab(x_index_id_tab(l_index)).planned_departure_date, FND_API.G_MISS_DATE) <>
2929 NVL(l_stop_rec.planned_departure_date, FND_API.G_MISS_DATE))
2930 OR (NVL(l_rec_attr_tab(x_index_id_tab(l_index)).stop_location_id, FND_API.G_MISS_NUM) <>
2931 NVL(l_stop_rec.stop_location_id, FND_API.G_MISS_NUM)) THEN
2932
2933 IF l_debug_on THEN
2934 wsh_debug_sv.log(l_module_name,'details marked count', l_details_marked.COUNT);
2935 END IF;
2936
2937 l_details_marked(l_details_marked.COUNT+1):=l_rec_attr_tab(x_index_id_tab(l_index)).stop_id;
2938
2939 IF l_debug_on THEN
2940 wsh_debug_sv.log(l_module_name,'details marked count', l_details_marked.COUNT);
2941 END IF;
2942
2943 ELSIF NVL(l_rec_attr_tab(x_index_id_tab(l_index)).WKEND_LAYOVER_STOPS, FND_API.G_MISS_NUM) <>
2944 NVL(l_stop_rec.WKEND_LAYOVER_STOPS, FND_API.G_MISS_NUM)
2945 OR NVL(l_rec_attr_tab(x_index_id_tab(l_index)).WKDAY_LAYOVER_STOPS, FND_API.G_MISS_NUM) <>
2946 NVL(l_stop_rec.WKDAY_LAYOVER_STOPS, FND_API.G_MISS_NUM) THEN
2947
2948 IF l_mode_of_transport = 'TRUCK' THEN
2949
2950 l_details_marked(l_details_marked.COUNT+1):=l_rec_attr_tab(x_index_id_tab(l_index)).stop_id;
2951
2952 END IF;
2953
2954 END IF;
2955
2956 END IF;
2957
2958 END IF;
2959 --TL Rating
2960
2961
2962
2963 EXCEPTION
2964 WHEN get_physical_loc_err THEN
2965 Rollback to s_trip_stop_grp;
2966 --OTM R12, glog proj
2967 IF c_trip_info%ISOPEN THEN
2968 CLOSE c_trip_info;
2969 END IF;
2970 l_num_errors := l_num_errors + 1;
2971 fnd_message.set_name('WSH', 'WSH_LOCATION_CONVERT_ERR');
2972 fnd_message.set_token('LOCATION_NAME',
2973 SUBSTRB(WSH_UTIL_CORE.get_location_description(l_rec_attr_tab(x_index_id_tab(l_index)).stop_location_id,'NEW UI CODE'), 1, 60));
2974 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING,l_module_name);
2975
2976 -- OTM R12, glog proj
2977 WHEN e_gc3_trip_exception THEN
2978 Rollback to s_trip_stop_grp;
2979 --OTM R12, glog proj
2980 IF c_trip_info%ISOPEN THEN
2981 CLOSE c_trip_info;
2982 END IF;
2983 l_num_errors := l_num_errors + 1;
2984 FND_MESSAGE.SET_NAME('WSH','WSH_OTM_TRIP_STOP_CR_ERROR');
2985 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR, l_module_name);
2986 --
2987 -- Debug Statements
2988 --
2989 IF l_debug_on THEN
2990 WSH_DEBUG_SV.logmsg(l_module_name,'GC3_TRIP exception has occured.');
2991 END IF;
2992 --
2993
2994 --OTM R12, glog proj, other cursors are closed in OUTER exception
2995 WHEN no_data_found THEN
2996 Rollback to s_trip_stop_grp;
2997 -- Cursor is already closed, before raising this exception
2998 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NOT_FOUND');
2999 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3000 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
3001 --
3002 -- Debug Statements
3003 --
3004 IF l_debug_on THEN
3005 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3006 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
3007 END IF;
3008 --
3009
3010
3011 WHEN fnd_api.g_exc_error THEN
3012 Rollback to s_trip_stop_grp;
3013 --OTM R12, glog proj
3014 IF c_trip_info%ISOPEN THEN
3015 CLOSE c_trip_info;
3016 END IF;
3017
3018 WHEN fnd_api.g_exc_unexpected_error THEN
3019 Rollback to s_trip_stop_grp;
3020 --OTM R12, glog proj
3021 IF c_trip_info%ISOPEN THEN
3022 CLOSE c_trip_info;
3023 END IF;
3024
3025 WHEN others THEN
3026 Rollback to s_trip_stop_grp;
3027 --OTM R12, glog proj
3028 IF c_trip_info%ISOPEN THEN
3029 CLOSE c_trip_info;
3030 END IF;
3031 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3032 END;
3033 l_index := x_index_id_tab.NEXT(l_index);
3034 END LOOP;
3035
3036
3037 -- TP call back to unfirm continuous move or delete continuous move or
3038 -- any other action that will be done in the future based on the action performed
3039 IF WSH_UTIL_CORE.TP_IS_INSTALLED='Y' THEN
3040 l_action_prms.action_code:=p_in_rec.action_code;
3041 l_action_prms.caller:=p_in_rec.caller;
3042 WSH_FTE_TP_INTEGRATION.stop_callback (
3043 p_api_version_number => 1.0,
3044 p_init_msg_list => FND_API.G_TRUE,
3045 x_return_status => l_return_status,
3046 x_msg_count => l_msg_count,
3047 x_msg_data => l_msg_data,
3048 p_action_prms => l_action_prms,
3049 p_rec_attr_tab => l_rec_attr_tab);
3050
3051 IF l_debug_on THEN
3052 WSH_DEBUG_SV.log(l_module_name,'after calling stop_callback l_return_status',l_return_status);
3053 END IF;
3054
3055 wsh_util_core.api_post_call(
3056 p_return_status => l_return_status,
3057 x_num_warnings => l_num_warnings,
3058 x_num_errors => l_num_errors
3059 );
3060 END IF;--tp_is_installed
3061
3062 --TL Rating mark trip as needing reprice
3063 IF( WSH_UTIL_CORE.FTE_Is_Installed = 'Y') THEN
3064 IF l_details_marked.count > 0 THEN
3065 --
3066 IF l_debug_on THEN
3067 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_LEGS_ACTIONS.MARK_REPRICE_REQUIRED',WSH_DEBUG_SV.C_PROC_LEVEL);
3068 END IF;
3069 --
3070 WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required(
3071 p_entity_type => 'STOP',
3072 p_entity_ids => l_details_marked,
3073 x_return_status => l_return_status);
3074
3075 --
3076 wsh_util_core.api_post_call(
3077 p_return_status => l_return_status,
3078 x_num_warnings => l_num_warnings,
3079 x_num_errors => l_num_errors
3080 );
3081
3082 END IF;
3083 END IF;
3084 --TL Rating
3085
3086
3087 --bug 2796095
3088 IF l_debug_on THEN
3089 WSH_DEBUG_SV.log(l_module_name,'l_trip_id_tab.count',l_trip_id_tab.count);
3090 END IF;
3091 IF ( l_trip_id_tab.count > 0 ) THEN
3092
3093 l_index := l_trip_id_tab.FIRST;
3094 WHILE l_index IS NOT NULL LOOP
3095 FOR stop_rec IN c_get_all_trip_stops(l_trip_id_tab(l_index)) LOOP
3096 IF l_debug_on THEN
3097 WSH_DEBUG_SV.log(l_module_name,'Stop_id',stop_rec.stop_id);
3098 END IF;
3099
3100 x_stop_wt_vol_out_tab(l_stop_index).stop_id := stop_rec.stop_id;
3101 x_stop_wt_vol_out_tab(l_stop_index).departure_gross_weight :=stop_rec.departure_gross_weight;
3102 x_stop_wt_vol_out_tab(l_stop_index).departure_net_weight :=stop_rec.departure_net_weight;
3103 x_stop_wt_vol_out_tab(l_stop_index).departure_volume :=stop_rec.departure_volume;
3104 x_stop_wt_vol_out_tab(l_stop_index).departure_fill_percent :=stop_rec.departure_fill_percent;
3105
3106 l_stop_index := l_stop_index +1;
3107 END LOOP;
3108
3109 l_index := l_trip_id_tab.NEXT(l_index);
3110 END LOOP;
3111 END IF;
3112 IF l_debug_on THEN
3113 WSH_DEBUG_SV.log(l_module_name,'x_stop_wt_vol_out_tab.count',x_stop_wt_vol_out_tab.count);
3114 END IF;
3115 --bug 2796095
3116 --
3117 -- K LPN CONV. rv
3118 --
3119 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
3120 THEN
3121 --{
3122 IF l_debug_on THEN
3123 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
3124 END IF;
3125
3126
3127 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
3128 (
3129 p_in_rec => l_lpn_in_sync_comm_rec,
3130 x_return_status => l_return_status,
3131 x_out_rec => l_lpn_out_sync_comm_rec
3132 );
3133 --
3134 --
3135 IF l_debug_on THEN
3136 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
3137 END IF;
3138 --
3139 --
3140 WSH_UTIL_CORE.API_POST_CALL
3141 (
3142 p_return_status => l_return_status,
3143 x_num_warnings => l_num_warnings,
3144 x_num_errors => l_num_errors
3145 );
3146 --}
3147 END IF;
3148 --
3149 -- K LPN CONV. rv
3150
3151
3152 IF (l_num_errors = l_rec_attr_tab.count ) THEN
3153 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3154 ELSIF (l_num_errors > 0 ) THEN
3155 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3156 ELSIF (l_num_warnings > 0 ) THEN
3157 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3158 ELSE
3159 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3160 END IF;
3161
3162
3163 IF FND_API.To_Boolean( p_commit ) THEN
3164 --Bugfix 4070732 {
3165 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3166 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
3167
3168 IF l_debug_on THEN
3169 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
3170 END IF;
3171
3172 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => FALSE,
3173 x_return_status => l_return_status);
3174
3175 IF l_debug_on THEN
3176 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
3177 END IF;
3178 END IF;
3179 --}
3180 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR
3181 (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3182 ROLLBACK to create_update_stop_grp;
3183 ELSE
3184 COMMIT WORK;
3185 END IF;
3186 wsh_util_core.api_post_call
3187 (
3188 p_return_status => l_return_status,
3189 x_num_warnings => l_num_warnings,
3190 x_num_errors => l_num_errors
3191 );
3192 --if l_return_status = warning
3193 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3194 x_return_status := l_return_status;
3195 END IF;
3196 END IF;
3197
3198 --Bugfix 4070732 {
3199 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN --{
3200 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
3201
3202 IF FND_API.TO_BOOLEAN(p_commit) THEN
3203
3204 IF l_debug_on THEN
3205 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
3206 END IF;
3207 WSH_UTIL_CORE.reset_stops_for_load_tender(
3208 p_reset_flags => TRUE,
3209 x_return_status => l_return_status);
3210 ELSE
3211
3212 IF l_debug_on THEN
3213 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
3214 END IF;
3215 WSH_UTIL_CORE.Process_stops_for_load_tender(
3216 p_reset_flags => TRUE,
3217 x_return_status => l_return_status);
3218 END IF;
3219
3220 IF l_debug_on THEN
3221 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
3222 END IF;
3223
3224 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
3225 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3226 IF NOT(FND_API.TO_BOOLEAN(p_commit)) THEN
3227 rollback to create_update_stop_grp;
3228 end if;
3229 END IF;
3230
3231 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
3232 OR (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
3233 THEN --{
3234 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3235 ELSIF x_return_status <> WSH_UTIL_CORE.G_RET_STS_ERROR
3236 THEN
3237 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
3238 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3239 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING
3240 THEN
3241 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3242 END IF;
3243 END IF; --}
3244
3245 END IF;
3246 END IF; --}
3247 --}
3248 --End of bug 4070732
3249
3250 FND_MSG_PUB.Count_And_Get
3251 ( p_count => x_msg_count,
3252 p_data => x_msg_data,
3253 p_encoded => FND_API.G_FALSE );
3254
3255
3256 IF l_debug_on THEN
3257 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
3258 WSH_DEBUG_SV.pop(l_module_name);
3259 END IF;
3260
3261
3262 EXCEPTION
3263 WHEN RECORD_LOCKED THEN
3264 -- OTM R12, glog proj, close cursors
3265 IF c_get_all_trip_stops%ISOPEN THEN
3266 CLOSE c_get_all_trip_stops;
3267 END IF;
3268 IF c_trip_info%ISOPEN THEN
3269 CLOSE c_trip_info;
3270 END IF;
3271 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3272 FND_MESSAGE.Set_Name('WSH', 'WSH_NO_LOCK');
3273 wsh_util_core.add_message(x_return_status,l_module_name);
3274 --Bugfix 4070732 {
3275 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
3276 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
3277 IF l_debug_on THEN
3278 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
3279 END IF;
3280
3281 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
3282 x_return_status => l_return_status);
3283
3284
3285 IF l_debug_on THEN
3286 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
3287 END IF;
3288
3289 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
3290 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3291 END IF;
3292 END IF;
3293 END IF;
3294 --}
3295 FND_MSG_PUB.Count_And_Get (
3296 p_count => x_msg_count,
3297 p_data => x_msg_data,
3298 p_encoded => FND_API.G_FALSE);
3299 IF l_debug_on THEN
3300 WSH_DEBUG_SV.logmsg(l_module_name,'RECORD_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3301 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:RECORD_LOCKED');
3302 END IF;
3303 ROLLBACK TO create_update_stop_grp;
3304
3305 WHEN FND_API.G_EXC_ERROR THEN
3306 -- OTM R12, glog proj, close cursors
3307 IF c_get_all_trip_stops%ISOPEN THEN
3308 CLOSE c_get_all_trip_stops;
3309 END IF;
3310 IF c_trip_info%ISOPEN THEN
3311 CLOSE c_trip_info;
3312 END IF;
3313 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
3314 --Bugfix 4070732 {
3315 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
3316 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
3317 IF l_debug_on THEN
3318 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
3319 END IF;
3320
3321 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
3322 x_return_status => l_return_status);
3323
3324
3325 IF l_debug_on THEN
3326 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
3327 END IF;
3328
3329 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
3330 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3331 END IF;
3332
3333 END IF;
3334 END IF;
3335 --}
3336 FND_MSG_PUB.Count_And_Get (
3337 p_count => x_msg_count,
3338 p_data => x_msg_data,
3339 p_encoded => FND_API.G_FALSE);
3340 IF l_debug_on THEN
3341 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3342 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3343 END IF;
3344
3345 ROLLBACK TO create_update_stop_grp;
3346
3347
3348 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3349 -- OTM R12, glog proj, close cursors
3350 IF c_get_all_trip_stops%ISOPEN THEN
3351 CLOSE c_get_all_trip_stops;
3352 END IF;
3353 IF c_trip_info%ISOPEN THEN
3354 CLOSE c_trip_info;
3355 END IF;
3356 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3357 --Bugfix 4070732 {
3358 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
3359 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
3360 IF l_debug_on THEN
3361 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
3362 END IF;
3363
3364 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
3365 x_return_status => l_return_status);
3366
3367
3368 IF l_debug_on THEN
3369 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
3370 END IF;
3371
3372 END IF;
3373 END IF;
3374 --}
3375 FND_MSG_PUB.Count_And_Get (
3376 p_count => x_msg_count,
3377 p_data => x_msg_data,
3378 p_encoded => FND_API.G_FALSE);
3379 IF l_debug_on THEN
3380 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3381 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3382 END IF;
3383 ROLLBACK TO create_update_stop_grp;
3384
3385 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
3386 -- OTM R12, glog proj, close cursors
3387 IF c_get_all_trip_stops%ISOPEN THEN
3388 CLOSE c_get_all_trip_stops;
3389 END IF;
3390 IF c_trip_info%ISOPEN THEN
3391 CLOSE c_trip_info;
3392 END IF;
3393 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3394 --
3395 -- K LPN CONV. rv
3396 --
3397 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
3398 THEN
3399 --{
3400 IF l_debug_on THEN
3401 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
3402 END IF;
3403
3404 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
3405 (
3406 p_in_rec => l_lpn_in_sync_comm_rec,
3407 x_return_status => l_return_status,
3408 x_out_rec => l_lpn_out_sync_comm_rec
3409 );
3410 --
3411 --
3412 IF l_debug_on THEN
3413 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
3414 END IF;
3415 --
3416 --
3417 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
3418 x_return_status := l_return_status;
3419 END IF;
3420 --
3421 --}
3422 END IF;
3423 --
3424 -- K LPN CONV. rv
3425 --
3426 --Bugfix 4070732 {
3427 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
3428 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
3429 IF l_debug_on THEN
3430 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
3431 END IF;
3432
3433 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => TRUE,
3434 x_return_status => l_return_status);
3435
3436 IF l_debug_on THEN
3437 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
3438 END IF;
3439
3440 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
3441 OR l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
3442 rollback to create_update_stop_grp;
3443 X_return_status := l_return_status;
3444 END IF;
3445 END IF;
3446 END IF;
3447 --}
3448 FND_MSG_PUB.Count_And_Get (
3449 p_count => x_msg_count,
3450 p_data => x_msg_data,
3451 p_encoded => FND_API.G_FALSE);
3452 IF l_debug_on THEN
3453 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3454 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
3455 END IF;
3456
3457 WHEN OTHERS THEN
3458 -- OTM R12, glog proj, close cursors
3459 IF c_get_all_trip_stops%ISOPEN THEN
3460 CLOSE c_get_all_trip_stops;
3461 END IF;
3462 IF c_trip_info%ISOPEN THEN
3463 CLOSE c_trip_info;
3464 END IF;
3465 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3466 wsh_util_core.default_handler ('WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP');
3467 --Bugfix 4070732 {
3468 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
3469 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
3470 IF l_debug_on THEN
3471 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
3472 END IF;
3473
3474 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
3475 x_return_status => l_return_status);
3476
3477
3478 IF l_debug_on THEN
3479 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
3480 END IF;
3481
3482 END IF;
3483 END IF;
3484 --}
3485 FND_MSG_PUB.Count_And_Get (
3486 p_count => x_msg_count,
3487 p_data => x_msg_data,
3488 p_encoded => FND_API.G_FALSE);
3489 IF l_debug_on THEN
3490 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
3491 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3492 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3493 END IF;
3494 ROLLBACK TO create_update_stop_grp;
3495
3496 END CREATE_UPDATE_STOP;
3497
3498 --========================================================================
3499 -- PROCEDURE : Create_Update_Stop Wrapper API
3500 --
3501 -- PARAMETERS: p_api_version_number known api versionerror buffer
3502 -- p_init_msg_list FND_API.G_TRUE to reset list
3503 -- x_return_status return status
3504 -- x_msg_count number of messages in the list
3505 -- x_msg_data text of messages
3506 -- p_stop_info Attributes for the stop entity
3507 -- p_trip_id Trip id for update
3508 -- p_trip_name Trip name for update
3509 -- p_stop_location_id Stop location id for update
3510 -- p_stop_location_code Stop location code for update
3511 -- p_planned_dep_date Planned departure date for update
3512 -- x_stop_id - stop id of new stop
3513 -- VERSION : current version 1.0
3514 -- initial version 1.0
3515 -- COMMENT : Creates or updates a record in wsh_trip_stops table with information
3516 -- specified in p_stop_info. Use p_trip_id, p_trip_name, p_stop_location_id,
3517 -- p_stop_location_code or p_planned_dep_date to update these values
3518 -- on an existing stop.
3519 --========================================================================
3520 PROCEDURE Create_Update_Stop
3521 ( p_api_version_number IN NUMBER,
3522 p_init_msg_list IN VARCHAR2,
3523 x_return_status OUT NOCOPY VARCHAR2,
3524 x_msg_count OUT NOCOPY NUMBER,
3525 x_msg_data OUT NOCOPY VARCHAR2,
3526 p_action_code IN VARCHAR2,
3527 p_stop_info IN OUT NOCOPY WSH_TRIP_STOPS_GRP.Trip_Stop_Pub_Rec_Type,
3528 p_trip_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
3529 p_trip_name IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
3530 p_stop_location_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
3531 p_stop_location_code IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
3532 p_planned_dep_date IN DATE DEFAULT FND_API.G_MISS_DATE,
3533 x_stop_id OUT NOCOPY NUMBER) IS
3534
3535 l_api_version_number CONSTANT NUMBER := 1.0;
3536 l_api_name CONSTANT VARCHAR2(30):= 'Create_Update_Stop';
3537 l_debug_on BOOLEAN;
3538 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_STOP';
3539
3540 l_in_rec stopInRecType;
3541 l_pvt_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
3542
3543 l_rec_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
3544 l_stop_out_tab stop_out_tab_type;
3545
3546 l_commit VARCHAR2(1):='F';
3547
3548 BEGIN
3549 --
3550 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3551 --
3552 IF l_debug_on IS NULL
3553 THEN
3554 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3555 END IF;
3556 --
3557 IF l_debug_on THEN
3558 wsh_debug_sv.push (l_module_name, 'Create_Update_Stop');
3559 wsh_debug_sv.log (l_module_name,'p_action_code',p_action_code);
3560 wsh_debug_sv.log (l_module_name,'p_stop_info.trip_id',p_stop_info.trip_id);
3561 wsh_debug_sv.log (l_module_name,'p_stop_info.trip_name',p_trip_name);
3562 wsh_debug_sv.log (l_module_name,'p_stop_info.stop_id',p_stop_info.stop_id);
3563 wsh_debug_sv.log (l_module_name,'p_stop_info.stop_location_id',p_stop_info.stop_location_id);
3564 wsh_debug_sv.log (l_module_name,'p_stop_info.stop_location_code',p_stop_info.stop_location_code);
3565 wsh_debug_sv.log (l_module_name,'p_stop_location_code',p_stop_location_code);
3566 wsh_debug_sv.log (l_module_name,'p_stop_info.stop_sequence_number',p_stop_info.stop_sequence_number);
3567 END IF;
3568
3569 IF l_debug_on THEN
3570 wsh_debug_sv.push (l_module_name, 'Create_Update_Stop');
3571 END IF;
3572
3573 IF NOT FND_API.Compatible_API_Call (l_api_version_number,p_api_version_number ,l_api_name ,G_PKG_NAME) THEN
3574 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3575 END IF;
3576
3577 IF FND_API.to_Boolean(p_init_msg_list) THEN
3578 FND_MSG_PUB.initialize;
3579 END IF;
3580
3581 map_stopgrp_to_pvt (
3582 p_grp_stop_rec => p_stop_info,
3583 x_pvt_stop_rec => l_pvt_stop_rec,
3584 x_return_status => x_return_status);
3585 IF l_debug_on THEN
3586 wsh_debug_sv.log (l_module_name, 'map_stoppub_to_pvt x_return_status',x_return_status);
3587 END IF;
3588 IF ( x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
3589 raise FND_API.G_EXC_ERROR;
3590 END IF;
3591
3592 IF (p_trip_id IS NOT NULL) AND (p_trip_id <> FND_API.G_MISS_NUM) THEN
3593 l_pvt_stop_rec.trip_id := p_trip_id;
3594 END IF;
3595
3596 IF (p_trip_name IS NOT NULL) AND (p_trip_name <> FND_API.G_MISS_CHAR) THEN
3597 l_pvt_stop_rec.trip_name := p_trip_name;
3598 END IF;
3599 IF (p_stop_location_id IS NOT NULL) AND (p_stop_location_id <> FND_API.G_MISS_NUM) THEN
3600 l_pvt_stop_rec.stop_location_id := p_stop_location_id;
3601 END IF;
3602
3603 IF (p_stop_location_code IS NOT NULL) AND (p_stop_location_code <> FND_API.G_MISS_CHAR) THEN
3604 l_pvt_stop_rec.stop_location_code := p_stop_location_code;
3605 END IF;
3606
3607 IF (p_planned_dep_date IS NOT NULL) AND (p_planned_dep_date <> FND_API.G_MISS_DATE)THEN
3608 l_pvt_stop_rec.planned_departure_date := p_planned_dep_date;
3609 END IF;
3610
3611 l_in_rec.caller :='WSH_GRP';
3612 l_in_rec.phase := 1;
3613 l_in_rec.action_code := p_action_code;
3614
3615 l_rec_attr_tab(1):= l_pvt_stop_rec;
3616
3617 WSH_INTERFACE_GRP.CREATE_UPDATE_STOP(
3618 p_api_version_number => p_api_version_number,
3619 p_init_msg_list => p_init_msg_list,
3620 p_commit => l_commit,
3621 p_in_rec => l_in_rec,
3622 p_rec_attr_tab => l_rec_attr_tab,
3623 x_stop_out_tab => l_stop_out_tab,
3624 x_return_status => x_return_status,
3625 x_msg_count => x_msg_count,
3626 x_msg_data => x_msg_data);
3627
3628 IF l_debug_on THEN
3629 WSH_DEBUG_SV.log(l_module_name,'WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP x_return_status',x_return_status);
3630 END IF;
3631
3632 IF ( x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS AND l_stop_out_tab.count > 0) THEN
3633 x_stop_id := l_stop_out_tab(l_stop_out_tab.FIRST).stop_id;
3634 END IF;
3635
3636 FND_MSG_PUB.Count_And_Get (
3637 p_count => x_msg_count,
3638 p_data => x_msg_data
3639 , p_encoded => FND_API.G_FALSE);
3640
3641 IF l_debug_on THEN
3642 WSH_DEBUG_SV.pop(l_module_name);
3643 END IF;
3644
3645 EXCEPTION
3646 WHEN OTHERS THEN
3647 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3648 wsh_util_core.default_handler ('WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP');
3649 IF l_debug_on THEN
3650 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
3651 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3652 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3653 END IF;
3654
3655 END CREATE_UPDATE_STOP;
3656
3657
3658 --========================================================================
3659 -- PROCEDURE : Create_Update_Stop_New Wrapper API PUBLIC
3660 --
3661 -- PARAMETERS: p_api_version_number known api versionerror buffer
3662 -- p_init_msg_list FND_API.G_TRUE to reset list
3663 -- x_return_status return status
3664 -- x_msg_count number of messages in the list
3665 -- x_msg_data text of messages
3666 -- p_stop_info Attributes for the stop entity
3667 -- p_stop_IN_rec Input Attributes for the stop entity
3668 -- p_stop_OUT_rec Output Attributes for the stop entity
3669 -- VERSION : current version 1.0
3670 -- initial version 1.0
3671 -- COMMENT : Creates or updates a record in wsh_trip_stops table with information
3672 -- specified in p_stop_info. Use p_trip_id, p_trip_name, p_stop_location_id,
3673 -- p_stop_location_code or p_planned_dep_date to update these values
3674 -- on an existing stop.
3675 --========================================================================
3676 PROCEDURE Create_Update_Stop_New
3677 ( p_api_version_number IN NUMBER,
3678 p_init_msg_list IN VARCHAR2,
3679 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3680 x_return_status OUT NOCOPY VARCHAR2,
3681 x_msg_count OUT NOCOPY NUMBER,
3682 x_msg_data OUT NOCOPY VARCHAR2,
3683 p_stop_info IN OUT NOCOPY WSH_TRIP_STOPS_GRP.Trip_Stop_Pub_Rec_Type,
3684 p_stop_IN_rec IN stopInRecType,
3685 x_stop_OUT_rec OUT NOCOPY stopOutRecType) IS
3686
3687 l_api_version_number CONSTANT NUMBER := 1.0;
3688 l_api_name CONSTANT VARCHAR2(30):= 'Create_Update_Stop';
3689 l_debug_on BOOLEAN;
3690 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_STOP_NEW';
3691
3692 l_in_rec stopInRecType;
3693 l_pvt_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
3694
3695 l_rec_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
3696 l_stop_out_tab stop_out_tab_type;
3697
3698 l_commit VARCHAR2(1):='F';
3699 BEGIN
3700 --
3701 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3702 --
3703 IF l_debug_on IS NULL
3704 THEN
3705 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3706 END IF;
3707 --
3708 IF l_debug_on THEN
3709 wsh_debug_sv.push(l_module_name);
3710 WSH_DEBUG_SV.log(l_module_name,'action_code',p_stop_IN_rec.action_code);
3711 WSH_DEBUG_SV.log(l_module_name,'p_init_msg_list',p_init_msg_list );
3712 WSH_DEBUG_SV.log(l_module_name,'p_commit',p_commit);
3713 WSH_DEBUG_SV.log(l_module_name,'trip_id', p_stop_info.trip_id);
3714 WSH_DEBUG_SV.log(l_module_name,'stop_location_id', p_stop_info.stop_location_id);
3715 WSH_DEBUG_SV.log(l_module_name,'stop_sequence_number', p_stop_info.stop_sequence_number);
3716 WSH_DEBUG_SV.log(l_module_name,'planned_arrival_date', p_stop_info.planned_arrival_date);
3717 WSH_DEBUG_SV.log(l_module_name,'planned_departure_date', p_stop_info.planned_departure_date);
3718 WSH_DEBUG_SV.log(l_module_name,'actual_arrival_date', p_stop_info.actual_arrival_date);
3719 WSH_DEBUG_SV.log(l_module_name,'actual_departure_date', p_stop_info.actual_departure_date);
3720 WSH_DEBUG_SV.log(l_module_name,'departure_gross_weight', p_stop_info.departure_gross_weight);
3721 WSH_DEBUG_SV.log(l_module_name,'departure_net_weight', p_stop_info.departure_net_weight);
3722 WSH_DEBUG_SV.log(l_module_name,'weight_uom_code', p_stop_info.weight_uom_code);
3723 WSH_DEBUG_SV.log(l_module_name,'departure_volume', p_stop_info.departure_volume);
3724 WSH_DEBUG_SV.log(l_module_name,'volume_uom_code', p_stop_info.volume_uom_code);
3725 WSH_DEBUG_SV.log(l_module_name,'departure_seal_code', p_stop_info.departure_seal_code);
3726 WSH_DEBUG_SV.log(l_module_name,'departure_fill_percent', p_stop_info.departure_fill_percent);
3727 WSH_DEBUG_SV.log(l_module_name,'lock_stop_id', p_stop_info.departure_fill_percent);
3728 WSH_DEBUG_SV.log(l_module_name,'pending_interface_flag', p_stop_info.pending_interface_flag);
3729 WSH_DEBUG_SV.log(l_module_name,'transaction_header_id', p_stop_info.transaction_header_id);
3730 WSH_DEBUG_SV.log(l_module_name,'wsh_location_id', p_stop_info.wsh_location_id);
3731 WSH_DEBUG_SV.log(l_module_name,'tracking_drilldown_flag', p_stop_info.tracking_drilldown_flag);
3732 WSH_DEBUG_SV.log(l_module_name,'tracking_remarks', p_stop_info.tracking_remarks);
3733 WSH_DEBUG_SV.log(l_module_name,'carrier_est_departure_date', p_stop_info.carrier_est_departure_date);
3734 WSH_DEBUG_SV.log(l_module_name,'carrier_est_arrival_date', p_stop_info.carrier_est_arrival_date);
3735 WSH_DEBUG_SV.log(l_module_name,'loading_start_datetime', p_stop_info.loading_start_datetime);
3736 WSH_DEBUG_SV.log(l_module_name,'loading_end_datetime', p_stop_info.loading_end_datetime);
3737 WSH_DEBUG_SV.log(l_module_name,'unloading_start_datetime', p_stop_info.unloading_start_datetime);
3738 WSH_DEBUG_SV.log(l_module_name,'unloading_end_datetime', p_stop_info.unloading_end_datetime);
3739 END IF;
3740
3741 IF NOT FND_API.Compatible_API_Call (l_api_version_number,p_api_version_number ,l_api_name ,G_PKG_NAME) THEN
3742 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3743 END IF;
3744
3745 IF FND_API.to_Boolean(p_init_msg_list) THEN
3746 FND_MSG_PUB.initialize;
3747 END IF;
3748
3749 map_stopgrp_to_pvt (
3750 p_grp_stop_rec => p_stop_info,
3751 x_pvt_stop_rec => l_pvt_stop_rec,
3752 x_return_status => x_return_status);
3753 IF l_debug_on THEN
3754 wsh_debug_sv.log (l_module_name, 'map_stoppub_to_pvt x_return_status',x_return_status);
3755 END IF;
3756 IF ( x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
3757 raise FND_API.G_EXC_ERROR;
3758 END IF;
3759
3760 l_rec_attr_tab(1):= l_pvt_stop_rec;
3761
3762 WSH_INTERFACE_GRP.CREATE_UPDATE_STOP(
3763 p_api_version_number => p_api_version_number,
3764 p_init_msg_list => p_init_msg_list,
3765 p_commit => l_commit,
3766 p_in_rec => p_stop_in_rec,
3767 p_rec_attr_tab => l_rec_attr_tab,
3768 x_stop_out_tab => l_stop_out_tab,
3769 x_return_status => x_return_status,
3770 x_msg_count => x_msg_count,
3771 x_msg_data => x_msg_data);
3772
3773 IF l_debug_on THEN
3774 WSH_DEBUG_SV.log(l_module_name,'WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP x_return_status',x_return_status);
3775 END IF;
3776
3777 IF ( x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS AND l_stop_out_tab.count > 0) THEN
3778 x_stop_out_rec := l_stop_out_tab(l_stop_out_tab.FIRST);
3779 END IF;
3780
3781 FND_MSG_PUB.Count_And_Get (
3782 p_count => x_msg_count,
3783 p_data => x_msg_data
3784 , p_encoded => FND_API.G_FALSE);
3785
3786 IF l_debug_on THEN
3787 WSH_DEBUG_SV.pop(l_module_name);
3788 END IF;
3789 EXCEPTION
3790 WHEN FND_API.G_EXC_ERROR THEN
3791 IF l_debug_on THEN
3792 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3793 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3794 END IF;
3795
3796 WHEN OTHERS THEN
3797 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3798 wsh_util_core.default_handler ('WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP');
3799 IF l_debug_on THEN
3800 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
3801 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3802 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3803 END IF;
3804
3805 END CREATE_UPDATE_STOP_NEW;
3806 --Harmonizing Project **heali
3807
3808 -- API to get Stop Details
3809 -- OTM R12, glog proj, removed the cursor to call populate_record API from WSHSTTHS/B
3810 PROCEDURE get_stop_details_pvt
3811 (p_stop_id IN NUMBER,
3812 x_stop_rec OUT NOCOPY WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE,
3813 x_return_status OUT NOCOPY VARCHAR2) IS
3814
3815 --
3816 l_debug_on BOOLEAN;
3817 --
3818 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_STOP_DETAILS_PVT';
3819 --
3820 BEGIN
3821 --
3822 --
3823 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3824 --
3825 IF l_debug_on IS NULL
3826 THEN
3827 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3828 END IF;
3829 --
3830 IF l_debug_on THEN
3831 WSH_DEBUG_SV.push(l_module_name);
3832 --
3833 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
3834 END IF;
3835 --
3836 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3837
3838 WSH_TRIP_STOPS_PVT.Populate_Record(
3839 p_stop_id => p_stop_id,
3840 x_stop_info => x_stop_rec,
3841 x_return_status => x_return_status);
3842
3843 --
3844 IF l_debug_on THEN
3845 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
3846 WSH_DEBUG_SV.pop(l_module_name);
3847 END IF;
3848 --
3849 EXCEPTION
3850 WHEN OTHERS THEN
3851 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_TRIP_STOPS_GRP.get_stop_details_pvt',l_module_name);
3852 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3853 --
3854 IF l_debug_on THEN
3855 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3856 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3857 END IF;
3858 --
3859 END get_stop_details_pvt;
3860
3861 --========================================================================
3862 -- PROCEDURE : Lock_Related_Entity
3863 --
3864 --
3865 -- PARAMETERS:
3866 -- p_stop_attr_tab
3867 -- x_return_status return status
3868 -- x_eligible_stop_id_tab
3869 -- p_action_prms Record of caller, phase, action_code and other
3870 -- parameters specific to the actions.
3871 --
3872 --
3873 -- VERSION : current version 1.0
3874 -- initial version 1.0
3875 -- COMMENT : Procedure introduced through bug fix : BUG 2684692 by jckwok. code reviewed:
3876 --========================================================================
3877
3878 PROCEDURE Lock_Related_Entity
3879 (
3880 p_action_prms IN action_parameters_rectype,
3881 p_stop_attr_tab IN WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type,
3882 x_valid_ids_tab OUT NOCOPY WSH_UTIL_CORE.id_Tab_Type,
3883 x_return_status OUT NOCOPY VARCHAR2
3884 )
3885 IS
3886 --
3887 CURSOR c_pickup_deliveries(p_stop_id NUMBER, p_stop_loc_id NUMBER) IS
3888 SELECT wnd.delivery_id
3889 FROM wsh_new_deliveries wnd, wsh_delivery_legs wdl
3890 WHERE wnd.delivery_id = wdl.delivery_id
3891 AND wdl.pick_up_stop_id = p_stop_id
3892 AND wnd.initial_pickup_location_id = p_stop_loc_id;
3893 --
3894 CURSOR c_dropoff_deliveries(p_stop_id NUMBER, p_stop_loc_id NUMBER) IS
3895 SELECT wnd.delivery_id
3896 FROM wsh_new_deliveries wnd, wsh_delivery_legs wdl
3897 WHERE wnd.delivery_id = wdl.delivery_id
3898 AND wdl.drop_off_stop_id = p_stop_id
3899 AND wnd.ultimate_dropoff_location_id = p_stop_loc_id;
3900 --
3901 CURSOR c_stop_trip_info(p_stop_id NUMBER) IS
3902 SELECT wts.trip_id, wts.stop_location_id, wt.status_code
3903 FROM wsh_trip_stops wts, wsh_trips wt
3904 WHERE wts.stop_id = p_stop_id
3905 AND wts.trip_id = wt.trip_id;
3906 --
3907 CURSOR c_unclosed_other_stops(p_trip_id NUMBER, p_stop_id NUMBER) IS
3908 SELECT wts.stop_id
3909 FROM wsh_trip_stops wts
3910 WHERE wts.trip_id = p_trip_id
3911 AND wts.status_code <> 'CL'
3912 AND wts.stop_id <> p_stop_id;
3913
3914 l_debug_on BOOLEAN;
3915 --
3916 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_RELATED_ENTITY';
3917 --
3918 l_count NUMBER := 1;
3919 l_index NUMBER;
3920 l_trip_id NUMBER;
3921 l_stop_location_id NUMBER;
3922 l_trip_status VARCHAR2(30);
3923 l_dummy_stop_id NUMBER;
3924
3925 BEGIN
3926
3927 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3928 --
3929 IF l_debug_on IS NULL
3930 THEN
3931 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3932 END IF;
3933 --
3934 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3935
3936 IF l_debug_on THEN
3937 WSH_DEBUG_SV.push(l_module_name);
3938 WSH_DEBUG_SV.log(l_module_name, 'Action_Code', p_action_prms.action_code);
3939 WSH_DEBUG_SV.log(l_module_name, 'Stop Action', p_action_prms.stop_action);
3940 WSH_DEBUG_SV.log(l_module_name, 'Phase ', p_action_prms.phase);
3941 END IF;
3942
3943 IF p_action_prms.action_code = 'UPDATE-STATUS'
3944 AND p_action_prms.stop_action = 'CLOSE'
3945 THEN
3946 --{
3947 --need to loop through each record in p_stop_attr_tab
3948 l_index := p_stop_attr_tab.FIRST;
3949 WHILE l_index IS NOT NULL LOOP
3950 BEGIN
3951 SAVEPOINT LOCK_RELATED_ENTITY_GRP_LOOP;
3952 --
3953 OPEN c_stop_trip_info(p_stop_attr_tab(l_index).stop_id);
3954 FETCH c_stop_trip_info
3955 INTO l_trip_id, l_stop_location_id, l_trip_status;
3956 CLOSE c_stop_trip_info;
3957
3958 IF l_debug_on THEN
3959 wsh_debug_sv.log(l_module_name, 'Trip Id', l_trip_id);
3960 wsh_debug_sv.log(l_module_name, 'Trip Status', l_trip_status);
3961 END IF;
3962
3963 /* With inbound logistics changes, New logic to lock trip is as follows:
3964 If trip is open, closing any stop requires trip status to be updated and hence trip's lock is needed.
3965 If trip is In-Transit, then lock the trip only if all other stops have been closed already.
3966 */
3967
3968 IF l_trip_status = 'OP'
3969 THEN
3970 -- {
3971 WSH_TRIPS_PVT.lock_trip_no_compare(
3972 p_trip_id => l_trip_id
3973 );
3974
3975 ELSIF l_trip_status = 'IT'
3976 THEN
3977 OPEN c_unclosed_other_stops(l_trip_id, p_stop_attr_tab(l_index).stop_id);
3978 FETCH c_unclosed_other_stops INTO l_dummy_stop_id;
3979 IF c_unclosed_other_stops%NOTFOUND
3980 THEN
3981 CLOSE c_unclosed_other_stops;
3982
3983 WSH_TRIPS_PVT.lock_trip_no_compare(
3984 p_trip_id => l_trip_id
3985 );
3986 ELSE
3987 IF l_debug_on THEN
3988 wsh_debug_sv.log(l_module_name, 'Atleast one Other stop not closed', l_dummy_stop_id);
3989 END IF;
3990 CLOSE c_unclosed_other_stops;
3991 END IF;
3992 -- }
3993 END IF;
3994
3995 FOR c_pickup_del_rec IN c_pickup_deliveries(p_stop_attr_tab(l_index).stop_id, l_stop_location_id)
3996 LOOP
3997 -- {
3998 -- lock delivery
3999 WSH_NEW_DELIVERIES_PVT.lock_dlvy_no_compare(
4000 p_delivery_id => c_pickup_del_rec.delivery_id
4001 );
4002 -- }
4003 END LOOP;
4004
4005 FOR c_dropoff_del_rec IN c_dropoff_deliveries(p_stop_attr_tab(l_index).stop_id, l_stop_location_id)
4006 LOOP
4007 -- {
4008 -- lock delivery
4009 WSH_NEW_DELIVERIES_PVT.lock_dlvy_no_compare(
4010 p_delivery_id => c_dropoff_del_rec.delivery_id
4011 );
4012
4013 -- }
4014 END LOOP;
4015
4016 x_valid_ids_tab(l_count) := p_stop_attr_tab(l_index).stop_id;
4017
4018 l_count := l_count + 1;
4019
4020 EXCEPTION
4021 --
4022 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION OR APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
4023 ROLLBACK TO LOCK_RELATED_ENTITY_GRP_LOOP;
4024 FND_MESSAGE.SET_NAME('WSH', 'WSH_STOP_DLVY_TRIP_LOCK');
4025 -- set token
4026 FND_MESSAGE.SET_TOKEN('STOP_ID', p_stop_attr_tab(l_index).stop_id);
4027 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
4028 WHEN FND_API.G_EXC_ERROR THEN
4029 ROLLBACK TO LOCK_RELATED_ENTITY_GRP_LOOP;
4030 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4031 ROLLBACK TO LOCK_RELATED_ENTITY_GRP_LOOP;
4032 WHEN OTHERS THEN
4033 ROLLBACK TO LOCK_RELATED_ENTITY_GRP_LOOP;
4034 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4035 --
4036 END;
4037 --
4038 l_index := p_stop_attr_tab.NEXT(l_index);
4039 END LOOP;
4040
4041 IF l_debug_on THEN
4042 WSH_DEBUG_SV.log(l_module_name, 'Valid ids count', x_valid_ids_tab.count);
4043 END IF;
4044
4045 -- Check for valid_ids_tab count.
4046 -- If valid ids count > 0 ,but less than the input table count, then return warning
4047 -- If valid ids count = 0, then return error
4048 IF p_stop_attr_tab.count > 0
4049 THEN
4050 -- {
4051 IF x_valid_ids_tab.count = 0
4052 THEN
4053 -- {
4054 RAISE FND_API.G_EXC_ERROR;
4055 ELSIF x_valid_ids_tab.count >0
4056 AND x_valid_ids_tab.count < p_stop_attr_tab.count
4057 THEN
4058 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
4059 -- }
4060 END IF;
4061 -- }
4062 END IF;
4063
4064 --}
4065 END IF;
4066
4067
4068
4069 IF l_debug_on THEN
4070 WSH_DEBUG_SV.pop(l_module_name);
4071 END IF;
4072
4073 EXCEPTION
4074 WHEN FND_API.G_EXC_ERROR THEN
4075 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4076 --
4077 IF l_debug_on THEN
4078 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4079 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4080 END IF;
4081 --
4082 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4083 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4084 --
4085 IF l_debug_on THEN
4086 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4087 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
4088 END IF;
4089 --
4090 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
4091 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4092 --
4093 IF l_debug_on THEN
4094 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4095 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
4096 END IF;
4097 --
4098 WHEN OTHERS THEN
4099 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
4100 wsh_util_core.default_handler('WSH_DELIVERIES_GRP.LOCK_RELATED_ENTITY');
4101
4102 --
4103 IF l_debug_on THEN
4104 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4105 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
4106 END IF;
4107 --
4108
4109 END Lock_Related_Entity;
4110
4111
4112
4113 PROCEDURE Add_to_Delete_List(
4114 p_stop_tab IN WSH_TRIP_STOPS_VALIDATIONS.stop_details_tab,
4115 p_caller IN VARCHAR2,
4116 x_stop_delete_tab OUT NOCOPY wsh_util_core.id_tab_type,
4117 x_trip_affected_tab OUT NOCOPY wsh_util_core.id_tab_type,
4118 x_return_status OUT NOCOPY VARCHAR2)
4119
4120 IS
4121
4122 CURSOR c_linked_stop(c_stop_id IN NUMBER, c_trip_id NUMBER) IS
4123 SELECT physical_stop_id linked_stop_id,
4124 1 link_type ,
4125 trip_id
4126 FROM wsh_trip_stops
4127 WHERE stop_id = c_stop_id
4128 AND physical_stop_id IS NOT NULL
4129 AND trip_id = c_trip_id
4130 UNION
4131 SELECT stop_id linked_stop_id,
4132 2 link_type ,
4133 trip_id
4134 FROM wsh_trip_stops
4135 WHERE physical_stop_id = c_stop_id
4136 AND status_code = 'OP'
4137 AND trip_id = c_trip_id;
4138
4139 i NUMBER;
4140 j NUMBER;
4141 k NUMBER;
4142 l_stop_delete_tab wsh_util_core.id_tab_type;
4143
4144 l_tmp NUMBER;
4145
4146 l_stop_exist BOOLEAN;
4147 l_trip_exist BOOLEAN;
4148 l_debug_on BOOLEAN;
4149 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Add_to_Delete_List';
4150
4151 BEGIN
4152 --
4153 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4154 --
4155 IF l_debug_on IS NULL
4156 THEN
4157 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4158 END IF;
4159 --
4160 IF l_debug_on THEN
4161 wsh_debug_sv.push(l_module_name);
4162 WSH_DEBUG_SV.log(l_module_name,'p_stop_tab.count', p_stop_tab.count);
4163 WSH_DEBUG_SV.log(l_module_name,'p_caller', p_caller );
4164 END IF;
4165
4166 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4167 x_stop_delete_tab.delete;
4168 l_stop_delete_tab.delete;
4169 x_trip_affected_tab.delete;
4170
4171
4172 IF p_caller like 'FTE%' THEN
4173 i := p_stop_tab.first;
4174
4175 WHILE i is not NULL LOOP
4176
4177 l_stop_exist := FALSE;
4178 j := l_stop_delete_tab.first;
4179 WHILE j is not NULL LOOP
4180 IF l_stop_delete_tab(j) = p_stop_tab(i).stop_id THEN
4181 l_stop_exist := TRUE;
4182 EXIT;
4183 END IF;
4184 j := l_stop_delete_tab.next(j);
4185 END LOOP;
4186
4187 IF not l_stop_exist THEN
4188 l_stop_delete_tab(l_stop_delete_tab.count+1) := p_stop_tab(i).stop_id;
4189 l_trip_exist := FALSE;
4190 k := x_trip_affected_tab.first;
4191 WHILE k is not NULL LOOP
4192 IF x_trip_affected_tab(k) = p_stop_tab(i).trip_id THEN
4193 l_trip_exist := TRUE;
4194 EXIT;
4195 END IF;
4196 k := x_trip_affected_tab.next(k);
4197 END LOOP;
4198 IF NOT l_trip_exist THEN
4199 x_trip_affected_tab(x_trip_affected_tab.count+1) := p_stop_tab(i).trip_id;
4200 END IF;
4201
4202 END IF;
4203
4204 FOR l_linked_stop IN c_linked_stop(p_stop_tab(i).stop_id, p_stop_tab(i).trip_id) LOOP
4205 l_stop_exist := FALSE;
4206 j := l_stop_delete_tab.first;
4207 WHILE j is not NULL LOOP
4208 IF l_stop_delete_tab(j) = l_linked_stop.linked_stop_id THEN
4209 l_stop_exist := TRUE;
4210 EXIT;
4211 END IF;
4212 j := l_stop_delete_tab.next(j);
4213 END LOOP;
4214
4215 IF not l_stop_exist THEN
4216 l_stop_delete_tab(l_stop_delete_tab.count+1) := l_linked_stop.linked_stop_id;
4217 END IF;
4218 END LOOP;
4219 i := p_stop_tab.next(i);
4220 END LOOP;
4221 ELSE
4222
4223 i := p_stop_tab.first;
4224 WHILE i is not NULL LOOP
4225
4226 l_stop_delete_tab(l_stop_delete_tab.count+1) := p_stop_tab(i).stop_id;
4227 l_trip_exist := FALSE;
4228 k := x_trip_affected_tab.first;
4229 WHILE k is not NULL LOOP
4230 IF x_trip_affected_tab(k) = p_stop_tab(i).trip_id THEN
4231 l_trip_exist := TRUE;
4232 EXIT;
4233 END IF;
4234 k := x_trip_affected_tab.next(k);
4235 END LOOP;
4236 IF NOT l_trip_exist THEN
4237 x_trip_affected_tab(x_trip_affected_tab.count+1) := p_stop_tab(i).trip_id;
4238 END IF;
4239 i := p_stop_tab.next(i);
4240 END LOOP;
4241 END IF;
4242
4243 x_stop_delete_tab := l_stop_delete_tab;
4244
4245
4246 IF l_debug_on THEN
4247 WSH_DEBUG_SV.pop(l_module_name);
4248 END IF;
4249
4250 EXCEPTION
4251
4252 WHEN Others THEN
4253 IF c_linked_stop%ISOPEN THEN
4254 close c_linked_stop;
4255 END IF;
4256
4257 wsh_util_core.default_handler('WSH_TRIP_STOPS_VALIDATIONS.Add_to_Delete_List',l_module_name);
4258 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4259 IF l_debug_on THEN
4260 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4261 END IF;
4262
4263 END Add_to_Delete_List;
4264
4265 -- end csun 10+ internal location
4266
4267 END WSH_TRIP_STOPS_GRP;