[Home] [Help]
PACKAGE BODY: APPS.WSH_TRIPS_GRP
Source
1 PACKAGE BODY WSH_TRIPS_GRP as
2 /* $Header: WSHTRGPB.pls 120.7 2007/01/05 19:23:14 parkhj noship $ */
3
4
5 --===================
6 -- CONSTANTS
7 --===================
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_TRIPS_GRP';
9 -- add your constants here if any
10
11 --===================
12 -- PROCEDURES
13 --===================
14
15 --========================================================================
16 -- PROCEDURE : Trip_Action PUBLIC
17 --
18 -- PARAMETERS: p_api_version_number known api version error number
19 -- p_init_msg_list FND_API.G_TRUE to reset list
20 -- x_return_status return status
21 -- x_msg_count number of messages in the list
22 -- x_msg_data text of messages
23 -- p_trip_info Attributes for the trip entity
24 -- p_trip_IN_rec Input Attributes for the trip entity
25 -- p_trip_OUT_rec Output Attributes for the trip entity
26 -- p_action_code Trip action code. Valid action codes are
27 -- 'PLAN','UNPLAN',
28 -- 'WT-VOL'
29 -- 'PICK-RELEASE'
30 -- 'DELETE'
31 -- VERSION : current version 1.0
32 -- initial version 1.0
33 -- COMMENT : This procedure is used to perform an action specified in p_action_code
34 -- on an existing trip identified by p_trip_id or trip_name
35 --
36 --========================================================================
37 PROCEDURE Trip_Action_New
38 ( p_api_version_number IN NUMBER,
39 p_init_msg_list IN VARCHAR2,
40 x_return_status OUT NOCOPY VARCHAR2,
41 x_msg_count OUT NOCOPY NUMBER,
42 x_msg_data OUT NOCOPY VARCHAR2,
43 p_trip_info IN OUT NOCOPY Trip_Pub_Rec_Type,
44 p_trip_IN_rec IN tripActionInRecType,
45 p_trip_OUT_rec OUT NOCOPY tripActionOutRecType) IS
46
47
48 l_api_version_number CONSTANT NUMBER := 1.0;
49 l_api_name CONSTANT VARCHAR2(30):= 'Trip_Action';
50
51 l_action_prms WSH_TRIPS_GRP.action_parameters_rectype;
52 l_entity_id_tab wsh_util_core.id_tab_type;
53 -- <insert here your local variables declaration>
54 trip_action_error EXCEPTION;
55
56 l_trip_id NUMBER := p_trip_info.trip_id;
57
58 l_return_status VARCHAR2(30);
59
60 --
61 l_debug_on BOOLEAN;
62 --
63 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'TRIP_ACTION_NEW';
64 --
65 BEGIN
66 -- Standard call to check for call compatibility
67 --
68 -- Debug Statements
69 --
70 --
71 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
72 --
73 IF l_debug_on IS NULL
74 THEN
75 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
76 END IF;
77 --
78 IF l_debug_on THEN
79 WSH_DEBUG_SV.push(l_module_name);
80 --
81 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION_NUMBER',P_API_VERSION_NUMBER);
82 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
83 WSH_DEBUG_SV.log(l_module_name,'TRIP_ID',p_trip_info.trip_id);
84 WSH_DEBUG_SV.log(l_module_name,'NAME',p_trip_info.name);
85 WSH_DEBUG_SV.log(l_module_name,'VEHICLE_ITEM_ID',p_trip_info.vehicle_item_id);
86 WSH_DEBUG_SV.log(l_module_name,'VEHICLE_ORGANIZATION_ID',p_trip_info.vehicle_organization_id);
87 WSH_DEBUG_SV.log(l_module_name,'CARRIER_ID',p_trip_info.carrier_id);
88 WSH_DEBUG_SV.log(l_module_name,'SHIP_METHOD_CODE',p_trip_info.ship_method_code);
89 WSH_DEBUG_SV.log(l_module_name,'SERVICE_LEVEL',p_trip_info.service_level);
90 WSH_DEBUG_SV.log(l_module_name,'MODE_OF_TRANSPORT',p_trip_info.mode_of_transport);
91 WSH_DEBUG_SV.log(l_module_name,'CONSOLIDATION_ALLOWED',p_trip_info.consolidation_allowed);
92 WSH_DEBUG_SV.log(l_module_name,'PLANNED_FLAG',p_trip_info.planned_flag);
93 WSH_DEBUG_SV.log(l_module_name,'STATUS_CODE',p_trip_info.status_code);
94 WSH_DEBUG_SV.log(l_module_name,'FREIGHT_TERMS_CODE',p_trip_info.freight_terms_code);
95 WSH_DEBUG_SV.log(l_module_name,'LANE_ID',p_trip_info.lane_id);
96 END IF;
97 --
98 IF NOT FND_API.Compatible_API_Call
99 ( l_api_version_number
100 , p_api_version_number
101 , l_api_name
102 , G_PKG_NAME
103 )
104 THEN
105 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
106 END IF;
107
108 -- Initialize message stack if required
109 IF FND_API.to_Boolean(p_init_msg_list) THEN
110 FND_MSG_PUB.initialize;
111 END IF;
112
113 wsh_util_validate.validate_trip_name( l_trip_id,
114 p_trip_info.name,
115 x_return_status);
116
117 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
118 raise trip_action_error;
119 END IF;
120
121 l_action_prms.caller := 'WSH_API';
122 l_action_prms.action_code := p_trip_IN_rec.action_code;
123 l_action_prms.organization_id := p_trip_info.VEHICLE_ORGANIZATION_ID;
124 --l_action_prms.report_set_id ??
125 l_action_prms.override_flag := p_trip_IN_rec.wv_override_flag;
126 --bms
127 l_entity_id_tab(1) := l_trip_id;
128
129 WSH_INTERFACE_GRP.Trip_Action
130 ( p_api_version_number => p_api_version_number,
131 p_init_msg_list => FND_API.G_FALSE,
132 p_commit => FND_API.G_TRUE,
133 p_entity_id_tab => l_entity_id_tab,
134 p_action_prms => l_action_prms,
135 x_trip_out_rec => p_trip_OUT_rec,
136 x_return_status => x_return_status,
137 x_msg_count => x_msg_count,
138 x_msg_data => x_msg_data);
139
140
141 FND_MSG_PUB.Count_And_Get
142 ( p_count => x_msg_count
143 , p_data => x_msg_data
144 ,p_encoded => FND_API.G_FALSE
145 );
146 --
147 IF l_debug_on THEN
148 WSH_DEBUG_SV.pop(l_module_name);
149 END IF;
150 --
151 EXCEPTION
152
153 WHEN trip_action_error THEN
154 FND_MESSAGE.SET_NAME('WSH','WSH_OI_TRIP_ACTION_ERROR');
155 --
156 IF l_debug_on THEN
157 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
158 END IF;
159 --
160 FND_MESSAGE.SET_TOKEN('TRIP_NAME', wsh_trips_pvt.get_name(l_trip_id));
161 FND_MESSAGE.SET_TOKEN('ACTION', wsh_util_core.get_action_meaning('TRIP',p_trip_IN_rec.action_code));
162 wsh_util_core.add_message(x_return_status);
163
164 IF l_debug_on THEN
165 WSH_DEBUG_SV.logmsg(l_module_name,'TRIP_ACTION_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
166 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:TRIP_ACTION_ERROR');
167 END IF;
168 --
169 WHEN FND_API.G_EXC_ERROR THEN
170 x_return_status := FND_API.G_RET_STS_ERROR;
171 -- Get message count and data
172 FND_MSG_PUB.Count_And_Get
173 ( p_count => x_msg_count
174 , p_data => x_msg_data
175 );
176
177 IF l_debug_on THEN
178 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
179 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
180 END IF;
181 --
182 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
183 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
184 -- Get message count and data
185 FND_MSG_PUB.Count_And_Get
186 ( p_count => x_msg_count
187 , p_data => x_msg_data
188 );
189 IF l_debug_on THEN
190 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
191 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
192 END IF;
193 --
194 WHEN OTHERS THEN
195 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
196 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
197 THEN
198 FND_MSG_PUB.Add_Exc_Msg
199 ( G_PKG_NAME
200 , '_x_'
201 );
202 END IF;
203 -- Get message count and data
204 FND_MSG_PUB.Count_And_Get
205 ( p_count => x_msg_count
206 , p_data => x_msg_data
207 );
208
209 IF l_debug_on THEN
210 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
211 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
212 END IF;
213 --
214 END Trip_Action_New;
215 /*** OLD VERSIONS **/
216
217
218 --========================================================================
219 -- PROCEDURE : Trip_Action PUBLIC
220 --
221 -- PARAMETERS: p_api_version_number known api version error number
222 -- p_init_msg_list FND_API.G_TRUE to reset list
223 -- x_return_status return status
224 -- x_msg_count number of messages in the list
225 -- x_msg_data text of messages
226 -- p_action_code Trip action code. Valid action codes are
227 -- 'PLAN','UNPLAN',
228 -- 'WT-VOL'
229 -- 'PICK-RELEASE'
230 -- 'DELETE'
231 -- p_trip_id Trip identifier
232 -- p_trip_name Trip name
233 -- p_wv_override_flag Override flag for weight/volume calc
234 -- VERSION : current version 1.0
235 -- initial version 1.0
236 -- COMMENT : This procedure is used to perform an action specified in p_action_code
237 -- on an existing trip identified by p_trip_id or trip_name
238 --
239 --========================================================================
240
241 PROCEDURE Trip_Action
242 ( p_api_version_number IN NUMBER,
243 p_init_msg_list IN VARCHAR2,
244 x_return_status OUT NOCOPY VARCHAR2,
245 x_msg_count OUT NOCOPY NUMBER,
246 x_msg_data OUT NOCOPY VARCHAR2,
247 p_action_code IN VARCHAR2,
248 p_trip_id IN NUMBER DEFAULT NULL,
249 p_trip_name IN VARCHAR2 DEFAULT NULL,
250 p_wv_override_flag IN VARCHAR2 DEFAULT 'N') IS
251
252 l_api_version_number CONSTANT NUMBER := 1.0;
253 l_api_name CONSTANT VARCHAR2(30):= 'Trip_Action';
254
255 -- <insert here your local variables declaration>
256 trip_action_error EXCEPTION;
257
258 l_trip_rows wsh_util_core.id_tab_type;
259 l_action_prms WSH_TRIPS_GRP.action_parameters_rectype;
260 l_trip_out_rec WSH_TRIPS_GRP.tripActionOutRecType;
261
262
263 l_trip_id NUMBER := p_trip_id;
264 --
265 l_debug_on BOOLEAN;
266 --
267 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'TRIP_ACTION';
268 --
269 BEGIN
270 -- Standard call to check for call compatibility
271 --
272 -- Debug Statements
273 --
274 --
275 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
276 --
277 IF l_debug_on IS NULL
278 THEN
279 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
280 END IF;
281 --
282 IF l_debug_on THEN
283 WSH_DEBUG_SV.push(l_module_name);
284 --
285 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION_NUMBER',P_API_VERSION_NUMBER);
286 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
287 WSH_DEBUG_SV.log(l_module_name,'P_ACTION_CODE',P_ACTION_CODE);
288 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
289 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_NAME',P_TRIP_NAME);
290 WSH_DEBUG_SV.log(l_module_name,'P_WV_OVERRIDE_FLAG',P_WV_OVERRIDE_FLAG);
291 END IF;
292 --
293 IF NOT FND_API.Compatible_API_Call
294 ( l_api_version_number
295 , p_api_version_number
296 , l_api_name
297 , G_PKG_NAME
298 )
299 THEN
300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
301 END IF;
302
303 -- Initialize message stack if required
304 IF FND_API.to_Boolean(p_init_msg_list) THEN
305 FND_MSG_PUB.initialize;
306 END IF;
307
308 IF l_debug_on THEN
309 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_VALIDATE.VALIDATE_TRIP_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
310 END IF;
311 --
312 wsh_util_validate.validate_trip_name( l_trip_id, p_trip_name, x_return_status);
313
314 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
315 raise trip_action_error;
316 END IF;
317
318 l_trip_rows(1) := l_trip_id;
319 l_action_prms.caller := 'WSH_API';
320 l_action_prms.action_code := p_action_code;
321 l_action_prms.override_flag := p_wv_override_flag;
322
323 WSH_INTERFACE_GRP.Trip_Action
324 ( p_api_version_number => p_api_version_number,
325 p_init_msg_list => FND_API.G_FALSE,
326 p_commit => FND_API.G_TRUE,
327 p_entity_id_tab => l_trip_rows,
328 p_action_prms => l_action_prms,
329 x_trip_out_rec => l_trip_out_rec,
330 x_return_status => x_return_status,
331 x_msg_count => x_msg_count,
332 x_msg_data => x_msg_data);
333
334 FND_MSG_PUB.Count_And_Get
335 ( p_count => x_msg_count
336 , p_data => x_msg_data
337 ,p_encoded => FND_API.G_FALSE
338 );
339 --
340 IF l_debug_on THEN
341 WSH_DEBUG_SV.pop(l_module_name);
342 END IF;
343 --
344 EXCEPTION
345
346 WHEN trip_action_error THEN
347 FND_MESSAGE.SET_NAME('WSH','WSH_OI_TRIP_ACTION_ERROR');
348 --
349 -- Debug Statements
350 --
351 IF l_debug_on THEN
352 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
353 END IF;
354 --
355 FND_MESSAGE.SET_TOKEN('TRIP_NAME', wsh_trips_pvt.get_name(l_trip_id));
356 FND_MESSAGE.SET_TOKEN('ACTION', wsh_util_core.get_action_meaning('TRIP',p_action_code));
357 wsh_util_core.add_message(x_return_status);
358
359 --
360 -- Debug Statements
361 --
362 IF l_debug_on THEN
363 WSH_DEBUG_SV.logmsg(l_module_name,'TRIP_ACTION_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
364 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:TRIP_ACTION_ERROR');
365 END IF;
366 --
367 WHEN FND_API.G_EXC_ERROR THEN
368 x_return_status := FND_API.G_RET_STS_ERROR;
369 -- Get message count and data
370 FND_MSG_PUB.Count_And_Get
371 ( p_count => x_msg_count
372 , p_data => x_msg_data
373 ,p_encoded => FND_API.G_FALSE
374 );
375
376 --
377 -- Debug Statements
378 --
379 IF l_debug_on THEN
380 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
381 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
382 END IF;
383 --
384 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
385 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
386 -- Get message count and data
387 FND_MSG_PUB.Count_And_Get
388 ( p_count => x_msg_count
389 , p_data => x_msg_data
390 ,p_encoded => FND_API.G_FALSE
391 );
392
393 --
394 -- Debug Statements
395 --
396 IF l_debug_on THEN
397 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
398 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
399 END IF;
400 --
401 WHEN OTHERS THEN
402 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
403 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
404 THEN
405 FND_MSG_PUB.Add_Exc_Msg
406 ( G_PKG_NAME
407 , '_x_'
408 );
409 END IF;
410 -- Get message count and data
411 FND_MSG_PUB.Count_And_Get
412 ( p_count => x_msg_count
413 , p_data => x_msg_data
414 ,p_encoded => FND_API.G_FALSE
415 );
416
417 --
418 -- Debug Statements
419 --
420 IF l_debug_on THEN
421 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
422 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
423 END IF;
424 --
425 END Trip_Action;
426
427
428 PROCEDURE Trip_Action
429 ( p_api_version_number IN NUMBER,
430 p_init_msg_list IN VARCHAR2,
431 p_commit IN VARCHAR2,
432 p_action_prms IN action_parameters_rectype,
433 p_rec_attr_tab IN WSH_TRIPS_PVT.Trip_Attr_Tbl_Type,
434 x_trip_out_rec OUT NOCOPY tripActionOutRecType,
435 x_def_rec OUT NOCOPY default_parameters_rectype,
436 x_return_status OUT NOCOPY VARCHAR2,
437 x_msg_count OUT NOCOPY NUMBER,
438 x_msg_data OUT NOCOPY VARCHAR2)
439
440 IS
441 l_trip_id_tab wsh_util_core.id_tab_type;
442 l_trip_rec_tab WSH_TRIP_VALIDATIONS.trip_rec_tab_type;
443 l_valid_id_tab wsh_util_core.id_tab_type;
444 l_dummy_ids wsh_util_core.id_tab_type;
445 l_valid_index_tab wsh_util_core.id_tab_type;
446 l_error_ids wsh_util_core.id_tab_type;
447 l_trip_rec WSH_TRIPS_PVT.trip_rec_type;
448 l_stop_rec WSH_TRIP_STOPS_PVT.trip_stop_rec_type;
449 l_dummy_doc_param WSH_DOCUMENT_SETS.DOCUMENT_SET_TAB_TYPE;
450 l_api_version_number CONSTANT NUMBER := 1.0;
451 l_api_name CONSTANT VARCHAR2(30):= 'Trip_Action';
452 l_first NUMBER;
453 l_index NUMBER;
454 l_last NUMBER;
455 l_counter NUMBER;
456 l_num_warning NUMBER := 0;
457 l_num_errors NUMBER := 0;
458 l_trip_id NUMBER;
459 l_num NUMBER;
460 l_return_status VARCHAR2(500);
461 l_report_set_id NUMBER := p_action_prms.report_set_id;
462 l_trip_org NUMBER;
463 l_temp_trip_id_tab wsh_util_core.id_tab_type;
464 l_mbol_error_count number;
465 l_unassign_all VARCHAR2(1);
466
467 --Compatibility Changes
468 l_cc_validate_result VARCHAR2(1);
469 l_cc_failed_records WSH_FTE_COMP_CONSTRAINT_PKG.failed_line_tab_type;
470 l_cc_group_info WSH_FTE_COMP_CONSTRAINT_PKG.cc_group_tab_type;
471 l_cc_line_groups WSH_FTE_COMP_CONSTRAINT_PKG.line_group_tab_type;
472 l_msg_count NUMBER;
473 l_msg_data VARCHAR2(2000);
474
475 l_trip_info_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
476 l_trip_id_tab_temp wsh_util_core.id_tab_type;
477 l_cc_count_success NUMBER;
478 b_cc_linefailed BOOLEAN;
479
480 --dummy tables for calling validate_constraint_wrapper
481 l_cc_del_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
482 l_cc_det_attr_tab WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type;
483 l_cc_trip_attr_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
484 l_cc_stop_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
485 l_cc_in_ids wsh_util_core.id_tab_type;
486 l_cc_fail_ids wsh_util_core.id_tab_type;
487
488 --Compatibility Changes
489
490 l_debug_on BOOLEAN;
491 --
492 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.'
493 || 'TRIP_ACTION';
494 e_req_field EXCEPTION;
495 --
496 --
497 -- J-IB-NPARIKH-{
498 CURSOR dlvy_csr(p_trip_id NUMBER)
499 IS
500 SELECT wdl.delivery_id, wt.name
501 FROM wsh_trip_stops wts,
502 wsh_Delivery_legs wdl,
503 wsh_new_deliveries wnd,
504 wsh_trips wt
505 WHERE wt.trip_id = p_trip_id
506 AND wts.trip_id = p_trip_id
507 AND wdl.pick_up_stop_id = wts.stop_id
508 AND wnd.delivery_id = wdl.delivery_id
509 AND wnd.initial_pickup_location_id = wts.stop_location_id;
510 --
511
512 -- Cursor c_mbol_doc_set added for bug 4493263
513 CURSOR c_mbol_doc_set IS
514 SELECT WRS.Report_Set_Id
515 FROM Wsh_Report_Sets Wrs,
516 Wsh_Report_Set_Lines Wrsl
517 WHERE Wrsl.Report_Set_Id = Wrs.Report_Set_Id
518 AND Wrs.Name = 'Master Bill of Lading';
519
520 -- Added for bug 4493263
521 l_dummy_doc_set_params wsh_document_sets.document_set_tab_type;
522 l_dummy_id_tab wsh_util_core.id_tab_type;
523 l_pmbol_trip_id_tab wsh_util_core.id_tab_type;
524 l_doc_set_id NUMBER;
525
526
527 -- J - MBOL
528 l_document_number VARCHAR2(50);
529 --
530 l_action_prms wsh_deliveries_grp.action_parameters_rectype;
531 l_del_action_out_rec wsh_deliveries_grp.Delivery_Action_Out_Rec_Type;
532 l_delivery_id_tab wsh_util_core.id_tab_type;
533 l_trip_name VARCHAR2(30);
534 l_cnt NUMBER;
535 e_end_of_api EXCEPTION;
536 -- J-IB-NPARIKH-}
537
538 -- Bug 3877951
539 l_intransit_flag VARCHAR2(1);
540 --Bugfix 4070732
541 l_api_session_name CONSTANT VARCHAR2(150) := G_PKG_NAME ||'.' || l_module_name;
542 l_reset_flags BOOLEAN;
543
544 -- K LPN CONV. rv
545 l_lpn_in_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_in_rec_type;
546 l_lpn_out_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_out_rec_type;
547 -- K LPN CONV. rv
548
549 -- OTM R12 : packing ECO
550 l_gc3_is_installed VARCHAR2(1);
551 -- End of OTM R12 : packing ECO
552
553 BEGIN
554 -- Bugfix 4070732
555 IF WSH_UTIL_CORE.G_START_OF_SESSION_API is null
556 THEN
557 WSH_UTIL_CORE.G_START_OF_SESSION_API := l_api_session_name;
558 WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API := FALSE;
559 END IF;
560 -- End of Code Bugfix 4070732
561 --
562 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
563 --
564 IF l_debug_on IS NULL
565 THEN
566 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
567 END IF;
568 --
569 SAVEPOINT s_trip_action_grp;
570
571 IF l_debug_on THEN
572 wsh_debug_sv.push (l_module_name);
573 wsh_debug_sv.log (l_module_name,'p_api_version_number',
574 p_api_version_number);
575 wsh_debug_sv.log (l_module_name,'p_init_msg_list',p_init_msg_list);
576 wsh_debug_sv.log (l_module_name,'p_commit',p_commit);
577 wsh_debug_sv.log (l_module_name,'action_code',p_action_prms.action_code);
578 wsh_debug_sv.log (l_module_name,'caller',p_action_prms.caller);
579 wsh_debug_sv.log (l_module_name,'COUNT',p_rec_attr_tab.COUNT);
580 END IF;
581
582 IF FND_API.to_Boolean(p_init_msg_list) THEN
583 FND_MSG_PUB.initialize;
584 END IF;
585
586 IF NOT FND_API.Compatible_API_Call
587 ( l_api_version_number
588 , p_api_version_number
589 , l_api_name
590 , G_PKG_NAME
591 )
592 THEN
593 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
594 END IF;
595
596
597 IF p_action_prms.action_code IS NULL THEN
598 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
599 FND_MESSAGE.SET_TOKEN('FIELD_NAME','p_action_prms.action_code');
600 RAISE e_req_field;
601 ELSIF p_action_prms.caller IS NULL THEN
602 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
603 FND_MESSAGE.SET_TOKEN('FIELD_NAME','p_action_prms.caller');
604 RAISE e_req_field;
605 ELSIF p_rec_attr_tab.COUNT = 0 THEN
606 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
607 FND_MESSAGE.SET_TOKEN('FIELD_NAME','p_rec_attr_tab.COUNT');
608 RAISE e_req_field;
609 END IF;
610 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
611
612 -- J-IB-NPARIKH-{
613 --
614 --
615 IF p_action_prms.action_code = 'GENERATE-ROUTING-RESPONSE'
616 THEN
617 --{
618 IF l_debug_on THEN
619 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_ACTIONS.generateRoutingResponse',WSH_DEBUG_SV.C_PROC_LEVEL);
620 END IF;
621 --
622 WSH_TRIPS_ACTIONS.generateRoutingResponse
623 (
624 p_action_prms => p_action_prms,
625 p_rec_attr_tab => p_rec_attr_tab,
626 x_return_status => l_return_status
627 );
628 --
629 --
630 IF l_debug_on THEN
631 wsh_debug_sv.log(l_module_name,'Return Status After Calling generate_routing_response',l_return_status);
632 END IF;
633 --
634 wsh_util_core.api_post_call(
635 p_return_status => l_return_status,
636 x_num_warnings => l_num_warning,
637 x_num_errors => l_num_errors);
638 --
639 RAISE e_end_of_Api;
640 --}
641 END IF;
642 --
643 --
644 -- J-IB-NPARIKH-}
645
646 l_index := p_rec_attr_tab.FIRST;
647
648 WHILE l_index IS NOT NULL LOOP
649 l_trip_id_tab(l_index) := p_rec_attr_tab(l_index).trip_id;
650 l_trip_rec_tab(l_index).trip_id := p_rec_attr_tab(l_index).trip_id;
651 l_trip_rec_tab(l_index).status_code := p_rec_attr_tab(l_index).status_code;
652 -- J inbound logistics. populate new column shipments_type_flag jckwok
653 l_trip_rec_tab(l_index).shipments_type_flag := p_rec_attr_tab(l_index).shipments_type_flag;
654 -- R12 Select Carrier
655 l_trip_rec_tab(l_index).planned_flag := p_rec_attr_tab(l_index).planned_flag;
656 l_trip_rec_tab(l_index).load_tender_status := p_rec_attr_tab(l_index).load_tender_status;
657 l_trip_rec_tab(l_index).lane_id := p_rec_attr_tab(l_index).lane_id;
658
659 --OTM R12, glog proj
660 l_trip_rec_tab(l_index).ignore_for_planning := p_rec_attr_tab(l_index).ignore_for_planning;
661 IF l_debug_on THEN
662 wsh_debug_sv.log (l_module_name,'trip_id',
663 p_rec_attr_tab(l_index).trip_id);
664 wsh_debug_sv.log (l_module_name,'status_code',
665 p_rec_attr_tab(l_index).status_code);
666 END IF;
667 l_index := p_rec_attr_tab.NEXT(l_index);
668 END LOOP;
669
670 WSH_ACTIONS_LEVELS.set_validation_level (
671 p_entity => 'TRIP',
672 p_caller => p_action_prms.caller,
673 p_phase => p_action_prms.phase,
674 p_action =>p_action_prms.action_code ,
675 x_return_status => l_return_status);
676
677
678
679 wsh_util_core.api_post_call(p_return_status =>l_return_status,
680 x_num_warnings =>l_num_warning,
681 x_num_errors =>l_num_errors);
682
683 IF WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_ACTION_ENABLED_LVL) = 1 THEN
684 -- R12 Select Carrier
685 -- Changes inside Is_Action_Enabled
686
687 WSH_TRIP_VALIDATIONS.Is_Action_Enabled(
688 p_trip_rec_tab => l_trip_rec_tab,
689 p_action => p_action_prms.action_code,
690 p_caller => p_action_prms.caller,
691 x_return_status => l_return_status,
692 x_valid_ids => l_valid_id_tab ,
693 x_error_ids => l_error_ids ,
694 x_valid_index_tab => l_valid_index_tab);
695
696 wsh_util_core.api_post_call(p_return_status =>l_return_status,
697 x_num_warnings =>l_num_warning,
698 x_num_errors =>l_num_errors,
699 p_msg_data => NULL,
700 p_raise_error_flag => FALSE);
701 END IF;
702
703
704 IF WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_LOCK_RECORDS_LVL) = 1 THEN
705 IF NOT ( p_action_prms.caller = 'WSH_FSTRX'
706 AND p_action_prms.action_code = 'DELETE'
707 ) THEN --BUG 4354579
708 WSH_TRIPS_PVT.lock_trip(p_rec_attr_tab => p_rec_attr_tab,
709 p_caller => p_action_prms.caller,
710 p_valid_index_tab => l_valid_index_tab,
711 x_valid_ids_tab => x_trip_out_rec.valid_ids_tab,
712 x_return_status => l_return_status);
713
714 wsh_util_core.api_post_call(p_return_status =>l_return_status,
715 x_num_warnings =>l_num_warning,
716 x_num_errors =>l_num_errors,
717 p_msg_data => NULL,
718 p_raise_error_flag => FALSE);
719
720 END IF;
721 END IF;
722
723 IF WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_TRIP_CONFIRM_DEFAULT_LVL) = 1 THEN
724
725 WSH_TRIPS_ACTIONS.Get_Trip_Defaults(p_trip_id => l_trip_id_tab(1),
726 p_trip_name => p_action_prms.trip_name,
727 x_def_rec => x_def_rec,
728 x_return_status => l_return_status);
729
730 wsh_util_core.api_post_call(p_return_status =>l_return_status,
731 x_num_warnings =>l_num_warning,
732 x_num_errors =>l_num_errors,
733 p_msg_data => NULL,
734 p_raise_error_flag => FALSE);
735
736
737 END IF;
738
739
740 IF(l_num_errors >0 ) THEN
741 --
742 x_return_status := wsh_util_core.g_ret_sts_error;
743 --
744 IF (p_action_prms.caller = 'WSH_FSTRX') THEN
745 FND_MESSAGE.SET_NAME('WSH', 'WSH_DISABLE_ACTION');
746 wsh_util_core.add_message(x_return_status,l_module_name);
747 END IF;
748 --
749 IF l_debug_on THEN
750 wsh_debug_sv.logmsg(l_module_name, 'WSH_DISABLE_ACTION');
751 END IF;
752 --
753 RAISE FND_API.G_EXC_ERROR;
754 --
755 END IF;
756
757 IF l_debug_on THEN
758 wsh_debug_sv.log (l_module_name,'l_num_warning',l_num_warning);
759 END IF;
760
761
762 IF l_num_warning > 0 AND p_action_prms.caller = 'WSH_FSTRX' THEN
763 x_trip_out_rec.selection_issue_flag := 'Y';
764 END IF;
765
766 IF l_num_warning > 0 THEN
767 x_return_status := wsh_util_core.g_ret_sts_warning;
768 --
769 FND_MESSAGE.SET_NAME('WSH', 'WSH_DISABLE_ACTION_WARN');
770 wsh_util_core.add_message(x_return_status,l_module_name);
771 --
772 IF l_debug_on THEN
773 wsh_debug_sv.logmsg(l_module_name, 'WSH_DISABLE_ACTION_WARN');
774 END IF;
775 --
776 IF p_action_prms.caller = 'WSH_FSTRX' THEN
777 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
778 ELSE
779 RAISE FND_API.G_EXC_ERROR;
780 END IF;
781 --
782 END IF;
783
784 l_trip_id := p_rec_attr_tab(p_rec_attr_tab.FIRST).trip_id;
785
786 IF l_debug_on THEN
787 wsh_debug_sv.log (l_module_name,'l_trip_id', l_trip_id);
788 END IF;
789
790 IF p_action_prms.action_code in ('PRINT-DOC-SETS', 'TRIP-CONFIRM')
791 AND nvl(p_action_prms.phase,1) = 1
792 AND p_action_prms.caller = 'WSH_FSTRX'
793 THEN
794
795 x_return_status := wsh_util_core.g_ret_sts_success;
796 --
797 -- Start code for Bugfix 4070732
798 --
799 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
800 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
801 IF l_debug_on THEN
802 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
803 END IF;
804
805
806 WSH_UTIL_CORE.reset_stops_for_load_tender(
807 p_reset_flags => TRUE,
808 x_return_status => l_return_status);
809 IF l_debug_on THEN
810 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
811 END IF;
812 -- x_return_status is set to Success already, so
813 -- it will get the value for l_return_status
814 x_return_status := l_return_status;
815 END IF;
816 END IF;
817 -- End of Code Bugfix 4070732
818 --
819 RETURN; -- Non-Generic Actions.
820 END IF;
821
822
823
824 IF l_debug_on THEN
825 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));
826 END IF;
827
828 IF (WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_TRIP_STOP_VALIDATION_LVL) = 1 )
829 AND ( p_action_prms.action_code IN ( 'FIRM', 'PLAN' , 'UNPLAN'))
830 AND (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y')
831 THEN
832 FOR i IN l_trip_id_tab.FIRST..l_trip_id_tab.last LOOP
833 BEGIN
834
835 SAVEPOINT s_clean_loop_grp;
836
837 WSH_TRIPS_GRP.get_trip_details_pvt(
838 p_trip_id => l_trip_id_tab(i),
839 x_trip_rec => l_trip_rec,
840 x_return_status => l_return_status
841 );
842 wsh_util_core.api_post_call(p_return_status =>l_return_status,
843 x_num_warnings =>l_num_warning,
844 x_num_errors =>l_num_errors);
845
846
847 WSH_FTE_INTEGRATION.trip_stop_validations(
848 p_stop_rec => l_stop_rec,
849 p_trip_rec => l_trip_rec,
850 p_action => 'UPDATE',
851 x_return_status => l_return_status
852 );
853 wsh_util_core.api_post_call(p_return_status =>l_return_status,
854 x_num_warnings =>l_num_warning,
855 x_num_errors =>l_num_errors);
856 EXCEPTION
857 WHEN FND_API.G_EXC_ERROR THEN
858 ROLLBACK TO s_clean_loop_grp;
859 IF l_debug_on THEN
860 wsh_debug_sv.log (l_module_name,'G_EXC_ERROR in the loop');
861 END IF;
862 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
863 ROLLBACK TO s_clean_loop_grp;
864 IF l_debug_on THEN
865 wsh_debug_sv.log (l_module_name,
866 'G_EXC_UNEXPECTED_ERROR in the loop');
867 END IF;
868 END;
869 END LOOP;
870
871 IF l_num_errors >= l_trip_id_tab.COUNT THEN
872 IF l_debug_on THEN
873 wsh_debug_sv.log (l_module_name,'All failed in the loop');
874 END IF;
875 RAISE FND_API.G_EXC_ERROR;
876 END IF;
877
878 IF l_num_errors >0 THEN
879 IF l_debug_on THEN
880 wsh_debug_sv.log (l_module_name,'l_num_errors',l_num_errors);
881
882 END IF;
883 l_num_warning := l_num_warning + l_num_errors;
884 l_num_errors := 0;
885 END IF;
886 --
887 END IF;
888 --
889
890
891 IF p_action_prms.action_code IN ('FIRM','PLAN','UNPLAN') THEN
892
893 WSH_TRIPS_ACTIONS.plan (
894 p_trip_rows => l_trip_id_tab,
895 p_action => p_action_prms.action_code,
896 x_return_status => l_return_status
897 );
898
899 wsh_util_core.api_post_call(p_return_status =>l_return_status,
900 x_num_warnings =>l_num_warning,
901 x_num_errors =>l_num_errors);
902
903 /* J TP Release */
904 ELSIF p_action_prms.action_code in ('IGNORE_PLAN', 'INCLUDE_PLAN') then
905 Wsh_tp_release.change_ignoreplan_status
906 (p_entity =>'TRIP',
907 p_in_ids => l_trip_id_tab,
908 p_action_code => p_action_prms.action_code,
909 x_return_status => l_return_status);
910 --
911 IF l_debug_on THEN
912 wsh_debug_sv.log(l_module_name,'Return Status After Calling change_ignoreplan_status ',l_return_status);
913 END IF;
914 --
915 wsh_util_core.api_post_call(
916 p_return_status => l_return_status,
917 x_num_warnings => l_num_warning,
918 x_num_errors => l_num_errors);
919 --
920
921 ELSIF p_action_prms.action_code = 'PICK-RELEASE' THEN
922
923 WSH_PICK_LIST.Launch_Pick_Release(
924 p_trip_ids => l_trip_id_tab,
925 p_stop_ids => l_dummy_ids,
926 p_delivery_ids => l_dummy_ids,
927 p_detail_ids => l_dummy_ids,
928 x_request_ids => x_trip_out_rec.result_id_tab,
929 x_return_status => l_return_status
930 );
931 wsh_util_core.api_post_call(p_return_status =>l_return_status,
932 x_num_warnings =>l_num_warning,
933 x_num_errors =>l_num_errors);
934
935
936 ELSIF p_action_prms.action_code = 'TRIP-CONFIRM' THEN
937
938 -- Bug 3877951
939 IF p_action_prms.autoclose_flag = 'Y' THEN
940 l_intransit_flag := 'Y';
941 ELSE
942 l_intransit_flag := p_action_prms.autointransit_flag;
943 END IF;
944
945 WSH_TRIPS_ACTIONS.Confirm_Trip(
946 p_trip_id => l_trip_id_tab(1),
947 p_action_flag => p_action_prms.action_flag,
948 p_intransit_flag => p_action_prms.autointransit_flag,
949 p_close_flag => p_action_prms.autoclose_flag,
950 p_stage_del_flag => p_action_prms.stage_del_flag,
951 p_report_set_id => p_action_prms.report_set_id,
952 p_ship_method => p_action_prms.ship_method,
953 p_actual_dep_date => p_action_prms.actual_departure_date,
954 p_bol_flag => p_action_prms.bill_of_lading_flag,
955 p_defer_interface_flag => p_action_prms.defer_interface_flag,
956 p_mbol_flag => p_action_prms.mbol_flag, -- Added for MBOL
957 x_return_status => l_return_status);
958
959 wsh_util_core.api_post_call(p_return_status =>l_return_status,
960 x_num_warnings =>l_num_warning,
961 x_num_errors =>l_num_errors);
962
963 ELSIF p_action_prms.action_code = 'PRINT-DOC-SETS' THEN
964 IF WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_DOCUMENT_SETS_LVL)=1 THEN
965
966 IF l_report_set_id IS NULL THEN
967 FND_MESSAGE.SET_NAME('WSH','WSH_DOC_MISSING');
968 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
969 IF l_debug_on THEN
970 wsh_debug_sv.log (l_module_name,'WSH_DOC_MISSING');
971 END IF;
972 RAISE FND_API.G_EXC_ERROR;
973 END IF;
974 WSH_UTIL_VALIDATE.validate_report_set(
975 p_report_set_id => l_report_set_id,
976 p_report_set_name => NULL,
977 x_return_status => l_return_status);
978 wsh_util_core.api_post_call(p_return_status =>l_return_status,
979 x_num_warnings =>l_num_warning,
980 x_num_errors =>l_num_errors);
981 END IF;
982
983 WSH_DOCUMENT_SETS.print_document_sets(
984 p_report_set_id => p_action_prms.report_set_id,
985 p_organization_id => p_action_prms.organization_id,
986 p_trip_ids => l_trip_id_tab,
987 p_stop_ids => l_dummy_ids,
988 p_delivery_ids => l_dummy_ids,
989 p_document_param_info => l_dummy_doc_param,
990 x_return_status => l_return_status
991 );
992 wsh_util_core.api_post_call(p_return_status =>l_return_status,
993 x_num_warnings =>l_num_warning,
994 x_num_errors =>l_num_errors);
995
996 ELSIF p_action_prms.action_code = 'WT-VOL' THEN
997
998 -- OTM R12 : packing ECO
999 -- This change was introduced to mark the G_RESET_WV flag
1000 -- before calling trip_weight_volume so the procedure will know
1001 -- to invoke update tms_interface_flag process.
1002
1003 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
1004 IF l_gc3_is_installed IS NULL THEN
1005 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
1006 END IF;
1007
1008 IF l_gc3_is_installed = 'Y' THEN
1009 WSH_WV_UTILS.G_RESET_WV := 'Y'; -- set to Y to enable the update
1010 END IF;
1011 -- End of OTM R12 : packing ECO
1012
1013 WSH_TRIPS_ACTIONS.Trip_weight_volume(
1014 p_trip_rows => l_trip_id_tab,
1015 p_override_flag => p_action_prms.override_flag,
1016 p_start_departure_date => p_action_prms.actual_date,
1017 x_return_status => l_return_status,
1018 --tkt
1019 p_caller => p_action_prms.caller
1020 );
1021 wsh_util_core.api_post_call(p_return_status =>l_return_status,
1022 x_num_warnings =>l_num_warning,
1023 x_num_errors =>l_num_errors);
1024
1025 -- OTM R12 : packing ECO
1026 IF l_gc3_is_installed = 'Y' THEN
1027 WSH_WV_UTILS.G_RESET_WV := 'N'; -- after call, set it back to 'N'
1028 END IF;
1029 -- End of OTM R12 : packing ECO
1030
1031 ELSIF p_action_prms.action_code = 'DELETE' THEN
1032 WSH_UTIL_CORE.delete(
1033 p_type => 'TRIP',
1034 p_rows => l_trip_id_tab,
1035 x_return_status => l_return_status
1036 );
1037 wsh_util_core.api_post_call(p_return_status =>l_return_status,
1038 x_num_warnings =>l_num_warning,
1039 x_num_errors =>l_num_errors);
1040
1041 ELSIF p_action_prms.action_code IN ('PICK-RELEASE-UI' ,'RESOLVE-EXCEPTIONS-UI','FREIGHT-COSTS-UI') THEN
1042 IF p_rec_attr_tab.COUNT > 1 THEN
1043 --bms set the message given in DLD
1044 FND_MESSAGE.SET_NAME('WSH','WSH_UI_MULTI_SELECTION');
1045 FND_MSG_PUB.ADD;
1046 IF l_debug_on THEN
1047 wsh_debug_sv.log (l_module_name,'WSH_UI_MULTI_SELECTION');
1048 END IF;
1049 RAISE FND_API.G_EXC_ERROR;
1050 ELSIF p_rec_attr_tab.COUNT = 1
1051 AND p_action_prms.caller <> 'WSH_FSTRX' THEN
1052 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_ACTION_CODE');
1053 FND_MESSAGE.SET_TOKEN('ACT_CODE',p_action_prms.action_code );
1054 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
1055 IF l_debug_on THEN
1056 wsh_debug_sv.log (l_module_name,'WSH_INVALID_ACTION_CODE COUNT');
1057 END IF;
1058 RAISE FND_API.G_EXC_ERROR;
1059 END IF;
1060 -- Modified by 4493263
1061 ELSIF p_action_prms.action_code in ( 'GENERATE-PACK-SLIP', 'PRINT-PACK-SLIP' ) THEN
1062
1063 l_cnt := 0;
1064 --
1065 FOR dlvy_rec IN dlvy_csr(p_rec_attr_tab(p_rec_attr_tab.FIRST).trip_id)
1066 LOOP
1067 --{
1068 l_trip_name := dlvy_rec.name;
1069 l_cnt := l_cnt + 1;
1070 --
1071 l_delivery_id_tab(l_cnt) := dlvy_rec.delivery_id;
1072 --}
1073 END LOOP;
1074 --
1075 IF l_cnt = 0
1076 THEN
1077 --{
1078 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NO_PICKUP_ERROR');
1079 FND_MESSAGE.SET_TOKEN('TRIP_NAME',l_trip_name);
1080 x_return_status := wsh_util_core.g_ret_sts_error;
1081 wsh_util_core.add_message(x_return_status,l_module_name);
1082 RAISE FND_API.G_EXC_ERROR;
1083 --}
1084 ELSE
1085 l_action_prms.caller := 'WSH_GRP';
1086 l_action_prms.phase := p_action_prms.phase;
1087 l_action_prms.action_code := p_action_prms.action_code;
1088 --
1089 wsh_interface_grp.Delivery_Action(
1090 p_api_version_number => p_api_version_number,
1091 p_init_msg_list => FND_API.G_FALSE,
1092 p_commit => FND_API.G_FALSE,
1093 p_action_prms => l_action_prms,
1094 p_delivery_id_tab => l_delivery_id_tab,
1095 x_delivery_out_rec => l_del_action_out_rec,
1096 x_return_status => l_return_status,
1097 x_msg_count => l_msg_count,
1098 x_msg_data => l_msg_data);
1099 --
1100 --
1101 IF l_debug_on THEN
1102 wsh_debug_sv.log(l_module_name,'Return Status After Calling delivery_action',l_return_status);
1103 END IF;
1104
1105 wsh_util_core.api_post_call(p_return_status =>l_return_status,
1106 x_num_warnings =>l_num_warning,
1107 x_num_errors =>l_num_errors);
1108 END IF;
1109 -- Modified by 4493263
1110 ELSIF p_action_prms.action_code in ( 'GENERATE-BOL', 'PRINT-BOL' ) THEN
1111
1112 l_cnt := 0;
1113 --
1114 FOR dlvy_rec IN dlvy_csr(p_rec_attr_tab(p_rec_attr_tab.FIRST).trip_id)
1115 LOOP
1116 --{
1117 l_trip_name := dlvy_rec.name;
1118 l_cnt := l_cnt + 1;
1119 --
1120 l_delivery_id_tab(l_cnt) := dlvy_rec.delivery_id;
1121 --}
1122 END LOOP;
1123 --
1124 IF l_cnt = 0
1125 THEN
1126 --{
1127 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NO_PICKUP_ERROR');
1128 FND_MESSAGE.SET_TOKEN('TRIP_NAME',l_trip_name);
1129 x_return_status := wsh_util_core.g_ret_sts_error;
1130 wsh_util_core.add_message(x_return_status,l_module_name);
1131 RAISE FND_API.G_EXC_ERROR;
1132 --}
1133 ELSE
1134 l_action_prms.caller := 'WSH_GRP';
1135 l_action_prms.phase := p_action_prms.phase;
1136 l_action_prms.action_code := p_action_prms.action_code;
1137 --
1138 wsh_interface_grp.Delivery_Action(
1139 p_api_version_number => p_api_version_number,
1140 p_init_msg_list => FND_API.G_FALSE,
1141 p_commit => FND_API.G_FALSE,
1142 p_action_prms => l_action_prms,
1143 p_delivery_id_tab => l_delivery_id_tab,
1144 x_delivery_out_rec => l_del_action_out_rec,
1145 x_return_status => l_return_status,
1146 x_msg_count => l_msg_count,
1147 x_msg_data => l_msg_data);
1148 --
1149 --
1150 IF l_debug_on THEN
1151 wsh_debug_sv.log(l_module_name,'Return Status After Calling delivery_action',l_return_status);
1152 END IF;
1153
1154
1155 wsh_util_core.api_post_call(p_return_status =>l_return_status,
1156 x_num_warnings =>l_num_warning,
1157 x_num_errors =>l_num_errors);
1158 END IF;
1159
1160 ELSIF p_action_prms.action_code = 'PRINT-MBOL' THEN
1161 --Bug 4493263
1162 --Instead of calling the PRINT_MBOL, we need to now trigger the document set.
1163
1164 l_mbol_error_count := 0 ;
1165 --4493263 : Get the document set id for MBOL
1166
1167 OPEN c_mbol_doc_set;
1168 FETCH c_mbol_doc_set into l_doc_set_id;
1169 CLOSE c_mbol_doc_set;
1170
1171 IF l_debug_on THEN
1172 wsh_debug_sv.log (l_module_name,'l_doc_set_id is', l_doc_set_id);
1173 END IF;
1174
1175 FOR i IN l_trip_id_tab.FIRST..l_trip_id_tab.last LOOP
1176
1177 WSH_MBOLS_PVT.Generate_MBOL(
1178 p_trip_id => l_trip_id_tab(i),
1179 x_sequence_number => l_document_number,
1180 x_return_status => l_return_status );
1181
1182 -- 4493263
1183 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1184 l_mbol_error_count := l_mbol_error_count + 1;
1185 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1186 -- Need to add another trip id tab which will have the trip ids for which generate mbol
1187 -- was successful.And then use this for print Document sets.
1188 l_pmbol_trip_id_tab(l_pmbol_trip_id_tab.COUNT +1) := l_trip_id_tab(i);
1189 END IF;
1190
1191 END LOOP;
1192
1193 IF l_debug_on THEN
1194 wsh_debug_sv.log (l_module_name,'l_pmbol_trip_id_tab.COUNT is', l_pmbol_trip_id_tab.COUNT );
1195 END IF;
1196 --Bug 4494236 : call Print doc set only if l_pmbol_trip_id_tab.count is > 0
1197
1198 IF ( l_doc_set_id IS NOT NULL AND l_pmbol_trip_id_tab.COUNT > 0 ) THEN
1199 -- l_delivery_id_tab(1) := x_action_out_rec.x_delivery_id;
1200
1201 WSH_DOCUMENT_SETS.Print_Document_Sets(
1202 p_report_set_id => l_doc_set_id,
1203 p_organization_id => p_action_prms.organization_id,
1204 p_trip_ids => l_pmbol_trip_id_tab,
1205 p_stop_ids => l_dummy_id_tab,
1206 p_delivery_ids => l_dummy_id_tab,
1207 p_document_param_info => l_dummy_doc_set_params,
1208 x_return_status => l_return_status);
1209 END IF;
1210
1211 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1212 l_mbol_error_count := l_mbol_error_count + 1;
1213 END IF;
1214
1215 WSH_UTIL_CORE.api_post_call(
1216 p_return_status => l_return_status,
1217 x_num_warnings => l_num_warning,
1218 x_num_errors => l_num_errors,
1219 p_raise_error_flag => FALSE );
1220
1221 IF l_mbol_error_count > 0 THEN
1222 FND_MESSAGE.SET_NAME('WSH', 'WSH_PRINT_MBOL_ERROR');
1223 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
1224 ELSIF l_mbol_error_count = 0 THEN
1225 FND_MESSAGE.SET_NAME('WSH', 'WSH_PRINT_MBOL_SUCCESS');
1226 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
1227 END IF;
1228
1229 /*
1230 -- Hiding project
1231 -- R12 Select Carrier
1232
1233 ELSIF p_action_prms.action_code = 'SELECT-CARRIER' THEN
1234 IF wsh_util_core.fte_is_installed = 'Y' THEN
1235 IF p_rec_attr_tab.COUNT > 1 AND p_action_prms.caller = 'WSH_FSTRX' THEN
1236 FND_MESSAGE.SET_NAME('WSH','WSH_UI_MULTI_SELECTION');
1237 x_return_status := wsh_util_core.g_ret_sts_error;
1238 wsh_util_core.add_message(x_return_status,l_module_name);
1239 --FND_MSG_PUB.ADD;
1240 IF l_debug_on THEN
1241 wsh_debug_sv.log (l_module_name,'WSH_UI_MULTI_SELECTION');
1242 END IF;
1243 RAISE FND_API.G_EXC_ERROR;
1244 END IF;
1245
1246 WSH_TRIPS_ACTIONS.PROCESS_CARRIER_SELECTION(
1247 p_init_msg_list => FND_API.G_FALSE,
1248 p_trip_id_tab => l_trip_id_tab,
1249 p_caller => p_action_prms.caller, -- WSH_FSTRX / WSH_PUB / WSH_GROUP/ FTE
1250 x_msg_count => l_msg_count,
1251 x_msg_data => l_msg_data,
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
1258 ELSE
1259 FND_MESSAGE.SET_NAME('WSH', 'FTE_NOT_INSTALLED');
1260 x_return_status := wsh_util_core.g_ret_sts_error;
1261 wsh_util_core.add_message(x_return_status,l_module_name);
1262 IF l_debug_on THEN
1263 wsh_debug_sv.logmsg(l_module_name,'FTE_NOT_INSTALLED');
1264 END IF;
1265 RAISE FND_API.G_EXC_ERROR;
1266 END IF;
1267
1268 */ -- Hiding project
1269 ELSIF p_action_prms.action_code IN ('REMOVE-CONSOL', 'UNASSIGN-ALL') THEN
1270
1271 IF p_action_prms.action_code = 'UNASSIGN-ALL' THEN
1272
1273 l_unassign_all := 'Y';
1274
1275 ELSE
1276
1277 l_unassign_all := 'N';
1278
1279 END IF;
1280
1281
1282 WSH_TRIPS_ACTIONS.Remove_Consolidation(
1283 p_trip_id_tab => l_trip_id_tab,
1284 p_unassign_all => l_unassign_all,
1285 p_caller => p_action_prms.caller,
1286 x_return_status => l_return_status);
1287
1288 IF l_debug_on THEN
1289 wsh_debug_sv.log(l_module_name,'Return Status After Calling Remove_Consolidation',l_return_status);
1290 END IF;
1291 wsh_util_core.api_post_call(p_return_status =>l_return_status,
1292 x_num_warnings =>l_num_warning,
1293 x_num_errors =>l_num_errors);
1294
1295
1296 ELSE
1297 -- give message for invalid action
1298 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_ACTION_CODE');
1299 FND_MESSAGE.SET_TOKEN('ACT_CODE',p_action_prms.action_code );
1300 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
1301 IF l_debug_on THEN
1302 wsh_debug_sv.log (l_module_name,'WSH_INVALID_ACTION_CODE');
1303 END IF;
1304 RAISE FND_API.G_EXC_ERROR;
1305 END IF;
1306
1307 -- TP call back to unfirm continuous move or delete continuous move or
1308 -- any other action that will be done in the future based on the action performed
1309 IF WSH_UTIL_CORE.TP_IS_INSTALLED='Y' THEN
1310 WSH_FTE_TP_INTEGRATION.trip_callback (
1311 p_api_version_number => 1.0,
1312 p_init_msg_list => FND_API.G_TRUE,
1313 x_return_status => l_return_status,
1314 x_msg_count => l_msg_count,
1315 x_msg_data => l_msg_data,
1316 p_action_prms => p_action_prms,
1317 p_rec_attr_tab => p_rec_attr_tab);
1318
1319 IF l_debug_on THEN
1320 WSH_DEBUG_SV.log(l_module_name,'after calling trip_callback l_return_status',l_return_status);
1321 END IF;
1322
1323 wsh_util_core.api_post_call(
1324 p_return_status => l_return_status,
1325 x_num_warnings => l_num_warning,
1326 x_num_errors => l_num_errors
1327 );
1328 END IF;--tp_is_installed
1329
1330
1331 --
1332 RAISE e_end_of_api;
1333
1334 EXCEPTION
1335 -- J-IB-NPARIKH-{
1336 WHEN e_end_of_api THEN
1337 IF l_num_warning > 0 THEN
1338 --RAISE WSH_UTIL_CORE.G_EXC_WARNING;
1339 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1340 ELSE
1341 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1342 END IF;
1343
1344 --
1345 -- K LPN CONV. rv
1346 --
1347 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
1348 THEN
1349 --{
1350 IF l_debug_on THEN
1351 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);
1352 END IF;
1353
1354 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
1355 (
1356 p_in_rec => l_lpn_in_sync_comm_rec,
1357 x_return_status => l_return_status,
1358 x_out_rec => l_lpn_out_sync_comm_rec
1359 );
1360 --
1361 --
1362 IF l_debug_on THEN
1363 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
1364 END IF;
1365 --
1366 --
1367 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1368 x_return_status := l_return_status;
1369 END IF;
1370
1371 --}
1372 END IF;
1373 --
1374 --
1375 -- K LPN CONV. rv
1376
1377 IF p_commit = FND_API.G_TRUE THEN
1378 --
1379 -- Start code for Bugfix 4070732 for commit
1380 --
1381 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1382 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1383
1384 IF l_debug_on THEN
1385 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1386 END IF;
1387
1388 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => FALSE,
1389 x_return_status => l_return_status);
1390
1391 IF l_debug_on THEN
1392 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1393 END IF;
1394 -- The x_return_status at this point is either success or warning
1395 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1396 x_return_status := l_return_status;
1397 END IF;
1398
1399 END IF;
1400
1401 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
1402 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1403 COMMIT;
1404 ELSE
1405 ROLLBACK TO s_trip_action_grp;
1406 END IF;
1407 --
1408 -- End of Code Bugfix 4070732
1409 --
1410 END IF;
1411
1412 --
1413 --Bugfix 4070732 { logical end of the API
1414 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name)
1415 THEN --{
1416 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1417
1418 IF p_commit = FND_API.G_TRUE THEN
1419
1420 IF l_debug_on THEN
1421 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1422 END IF;
1423 WSH_UTIL_CORE.reset_stops_for_load_tender(
1424 p_reset_flags => TRUE,
1425 x_return_status => l_return_status);
1426 ELSE
1427
1428 IF l_debug_on THEN
1429 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1430 END IF;
1431 WSH_UTIL_CORE.Process_stops_for_load_tender(
1432 p_reset_flags => TRUE,
1433 x_return_status => l_return_status);
1434 END IF;
1435
1436 IF l_debug_on THEN
1437 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1438 END IF;
1439
1440 --
1441 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1442 OR (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1443 THEN --{
1444 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1445 ELSIF x_return_status <> WSH_UTIL_CORE.G_RET_STS_ERROR
1446 THEN
1447 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
1448 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1449 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING
1450 THEN
1451 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1452 END IF;
1453 END IF; --}
1454
1455 IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1456 OR x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
1457 THEN
1458 IF p_commit = FND_API.G_TRUE THEN
1459 null;
1460 ELSE
1461 ROLLBACK TO s_trip_action_grp;
1462 END IF;
1463
1464 END IF;
1465 --
1466 END IF;
1467 END IF; --}
1468
1469 --}
1470 --End of bug 4070732
1471 --
1472 FND_MSG_PUB.Count_And_Get
1473 ( p_count => x_msg_count
1474 , p_data => x_msg_data
1475 ,p_encoded => FND_API.G_FALSE
1476 );
1477
1478 IF l_debug_on THEN
1479 WSH_DEBUG_SV.pop(l_module_name);
1480 END IF;
1481 -- J-IB-NPARIKH-}
1482
1483 WHEN e_req_field THEN
1484 ROLLBACK TO s_trip_action_grp;
1485 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1486 wsh_util_core.add_message(x_return_status,l_module_name);
1487
1488 --
1489 -- Start code for Bugfix 4070732
1490 --
1491 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1492 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1493 IF l_debug_on THEN
1494 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1495 END IF;
1496
1497 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1498 x_return_status => l_return_status);
1499
1500
1501 IF l_debug_on THEN
1502 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1503 END IF;
1504 IF l_return_status =
1505 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1506 x_return_status := l_return_status;
1507 END IF;
1508 END IF;
1509 END IF;
1510 --
1511 -- End of Code Bugfix 4070732
1512 --
1513
1514 FND_MSG_PUB.Count_And_Get
1515 ( p_count => x_msg_count
1516 , p_data => x_msg_data
1517 ,p_encoded => FND_API.G_FALSE
1518 );
1519 IF l_debug_on THEN
1520 wsh_debug_sv.log (l_module_name,'G_EXC_ERROR');
1521 WSH_DEBUG_SV.pop(l_module_name);
1522 END IF;
1523
1524
1525 WHEN FND_API.G_EXC_ERROR THEN
1526 ROLLBACK TO s_trip_action_grp;
1527 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1528
1529 --
1530 -- Start code for Bugfix 4070732
1531 --
1532 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1533 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1534 IF l_debug_on THEN
1535 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1536 END IF;
1537
1538 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1539 x_return_status => l_return_status);
1540
1541
1542 IF l_debug_on THEN
1543 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1544 END IF;
1545
1546 IF l_return_status =
1547 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1548 x_return_status := l_return_status;
1549 END IF;
1550 END IF;
1551 END IF;
1552 --
1553 -- End of Code Bugfix 4070732
1554 --
1555 FND_MSG_PUB.Count_And_Get
1556 ( p_count => x_msg_count
1557 , p_data => x_msg_data
1558 ,p_encoded => FND_API.G_FALSE
1559 );
1560
1561 IF l_debug_on THEN
1562 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
1563 wsh_debug_sv.log (l_module_name,'G_EXC_ERROR');
1564 WSH_DEBUG_SV.pop(l_module_name);
1565 END IF;
1566 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1567 ROLLBACK TO s_trip_action_grp;
1568 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1569
1570 --
1571 -- Start code for Bugfix 4070732
1572 --
1573 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1574 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1575 IF l_debug_on THEN
1576 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1577 END IF;
1578
1579 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1580 x_return_status => l_return_status);
1581
1582
1583 IF l_debug_on THEN
1584 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1585 END IF;
1586
1587 END IF;
1588 END IF;
1589
1590 --
1591 -- End of Code Bugfix 4070732
1592 --
1593 FND_MSG_PUB.Count_And_Get
1594 ( p_count => x_msg_count
1595 , p_data => x_msg_data
1596 ,p_encoded => FND_API.G_FALSE
1597 );
1598
1599 IF l_debug_on THEN
1600 wsh_debug_sv.log (l_module_name,'G_EXC_UNEXPECTED_ERROR');
1601 WSH_DEBUG_SV.pop(l_module_name);
1602 END IF;
1603 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1604 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1605 --
1606 -- K LPN CONV. rv
1607 --
1608 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
1609 THEN
1610 --{
1611 IF l_debug_on THEN
1612 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);
1613 END IF;
1614
1615 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
1616 (
1617 p_in_rec => l_lpn_in_sync_comm_rec,
1618 x_return_status => l_return_status,
1619 x_out_rec => l_lpn_out_sync_comm_rec
1620 );
1621 --
1622 --
1623 IF l_debug_on THEN
1624 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
1625 END IF;
1626 --
1627 --
1628 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
1629 x_return_status := l_return_status;
1630 END IF;
1631 --
1632 --}
1633 END IF;
1634 --
1635 -- K LPN CONV. rv
1636 --
1637
1638 --
1639 -- Start code for Bugfix 4070732
1640 --
1641 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1642 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1643 IF l_debug_on THEN
1644 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1645 END IF;
1646
1647 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => TRUE,
1648 x_return_status => l_return_status);
1649
1650 IF l_debug_on THEN
1651 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1652 END IF;
1653 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
1654 THEN
1655 x_return_status := l_return_status;
1656 END IF;
1657 END IF;
1658 END IF;
1659
1660 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1661 OR l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
1662 THEN
1663 ROLLBACK TO s_trip_action_grp;
1664 END IF;
1665 --
1666 -- End of Code Bugfix 4070732
1667 --
1668 FND_MSG_PUB.Count_And_Get
1669 ( p_count => x_msg_count
1670 , p_data => x_msg_data
1671 ,p_encoded => FND_API.G_FALSE
1672 );
1673
1674 IF l_debug_on THEN
1675 wsh_debug_sv.log (l_module_name,'G_EXC_WARNING');
1676 WSH_DEBUG_SV.pop(l_module_name);
1677 END IF;
1678 WHEN OTHERS THEN
1679 ROLLBACK TO s_trip_action_grp;
1680 wsh_util_core.default_handler('WSH_TRIPS_GRP.TRIP_ACTION',
1681 l_module_name);
1682
1683
1684 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1685
1686 --
1687 -- Start code for Bugfix 4070732
1688 --
1689 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1690 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1691 IF l_debug_on THEN
1692 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1693 END IF;
1694
1695 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1696 x_return_status => l_return_status);
1697
1698
1699 IF l_debug_on THEN
1700 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1701 END IF;
1702
1703 END IF;
1704 END IF;
1705
1706 --
1707 -- End of Code Bugfix 4070732
1708 --
1709
1710 FND_MSG_PUB.Count_And_Get
1711 ( p_count => x_msg_count
1712 , p_data => x_msg_data
1713 ,p_encoded => FND_API.G_FALSE
1714 );
1715 IF l_debug_on THEN
1716 wsh_debug_sv.log (l_module_name,'Others',substr(sqlerrm,1,200));
1717 WSH_DEBUG_SV.pop(l_module_name);
1718 END IF;
1719
1720 END Trip_Action;
1721
1722
1723 --Harmonization Project **heali
1724 PROCEDURE map_tripgrp_to_pvt(
1725 p_grp_trip_rec IN TRIP_PUB_REC_TYPE,
1726 x_pvt_trip_rec OUT NOCOPY WSH_TRIPS_PVT.TRIP_REC_TYPE,
1727 x_return_status OUT NOCOPY VARCHAR2) IS
1728
1729 l_debug_on BOOLEAN;
1730 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'MAP_TRIPPUB_TO_GRP';
1731 BEGIN
1732 --
1733 --
1734 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1735 --
1736 IF l_debug_on IS NULL
1737 THEN
1738 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1739 END IF;
1740 --
1741 IF l_debug_on THEN
1742 WSH_DEBUG_SV.push(l_module_name);
1743 WSH_DEBUG_SV.log(l_module_name,'p_grp_trip_rec.TRIP_ID',p_grp_trip_rec.TRIP_ID);
1744 WSH_DEBUG_SV.log(l_module_name,'p_grp_trip_rec.NAME',p_grp_trip_rec.NAME);
1745 END IF;
1746 --
1747 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1748
1749 x_pvt_trip_rec.TRIP_ID := p_grp_trip_rec.TRIP_ID;
1750 x_pvt_trip_rec.NAME := p_grp_trip_rec.NAME;
1751 x_pvt_trip_rec.PLANNED_FLAG := p_grp_trip_rec.PLANNED_FLAG;
1752 x_pvt_trip_rec.ARRIVE_AFTER_TRIP_ID := p_grp_trip_rec.ARRIVE_AFTER_TRIP_ID;
1753 x_pvt_trip_rec.STATUS_CODE := p_grp_trip_rec.STATUS_CODE;
1754 x_pvt_trip_rec.VEHICLE_ITEM_ID := p_grp_trip_rec.VEHICLE_ITEM_ID;
1755 x_pvt_trip_rec.VEHICLE_ORGANIZATION_ID := p_grp_trip_rec.VEHICLE_ORGANIZATION_ID;
1756 x_pvt_trip_rec.VEHICLE_NUMBER := p_grp_trip_rec.VEHICLE_NUMBER;
1757 x_pvt_trip_rec.VEHICLE_NUM_PREFIX := p_grp_trip_rec.VEHICLE_NUM_PREFIX;
1758 x_pvt_trip_rec.CARRIER_ID := p_grp_trip_rec.CARRIER_ID;
1759 x_pvt_trip_rec.SHIP_METHOD_CODE := p_grp_trip_rec.SHIP_METHOD_CODE;
1760 x_pvt_trip_rec.ROUTE_ID := p_grp_trip_rec.ROUTE_ID;
1761 x_pvt_trip_rec.ROUTING_INSTRUCTIONS := p_grp_trip_rec.ROUTING_INSTRUCTIONS;
1762 x_pvt_trip_rec.ATTRIBUTE_CATEGORY := p_grp_trip_rec.ATTRIBUTE_CATEGORY;
1763 x_pvt_trip_rec.ATTRIBUTE1 := p_grp_trip_rec.ATTRIBUTE1;
1764 x_pvt_trip_rec.ATTRIBUTE2 := p_grp_trip_rec.ATTRIBUTE2;
1765 x_pvt_trip_rec.ATTRIBUTE3 := p_grp_trip_rec.ATTRIBUTE3;
1766 x_pvt_trip_rec.ATTRIBUTE4 := p_grp_trip_rec.ATTRIBUTE4;
1767 x_pvt_trip_rec.ATTRIBUTE5 := p_grp_trip_rec.ATTRIBUTE5;
1768 x_pvt_trip_rec.ATTRIBUTE6 := p_grp_trip_rec.ATTRIBUTE6;
1769 x_pvt_trip_rec.ATTRIBUTE7 := p_grp_trip_rec.ATTRIBUTE7;
1770 x_pvt_trip_rec.ATTRIBUTE8 := p_grp_trip_rec.ATTRIBUTE8;
1771 x_pvt_trip_rec.ATTRIBUTE9 := p_grp_trip_rec.ATTRIBUTE9;
1772 x_pvt_trip_rec.ATTRIBUTE10 := p_grp_trip_rec.ATTRIBUTE10;
1773 x_pvt_trip_rec.ATTRIBUTE11 := p_grp_trip_rec.ATTRIBUTE11;
1774 x_pvt_trip_rec.ATTRIBUTE12 := p_grp_trip_rec.ATTRIBUTE12;
1775 x_pvt_trip_rec.ATTRIBUTE13 := p_grp_trip_rec.ATTRIBUTE13;
1776 x_pvt_trip_rec.ATTRIBUTE14 := p_grp_trip_rec.ATTRIBUTE14;
1777 x_pvt_trip_rec.ATTRIBUTE15 := p_grp_trip_rec.ATTRIBUTE15;
1778 x_pvt_trip_rec.CREATION_DATE := p_grp_trip_rec.CREATION_DATE;
1779 x_pvt_trip_rec.CREATED_BY := p_grp_trip_rec.CREATED_BY;
1780 x_pvt_trip_rec.LAST_UPDATE_DATE := p_grp_trip_rec.LAST_UPDATE_DATE;
1781 x_pvt_trip_rec.LAST_UPDATED_BY := p_grp_trip_rec.LAST_UPDATED_BY;
1782 x_pvt_trip_rec.LAST_UPDATE_LOGIN := p_grp_trip_rec.LAST_UPDATE_LOGIN;
1783 x_pvt_trip_rec.PROGRAM_APPLICATION_ID := p_grp_trip_rec.PROGRAM_APPLICATION_ID;
1784 x_pvt_trip_rec.PROGRAM_ID := p_grp_trip_rec.PROGRAM_ID;
1785 x_pvt_trip_rec.PROGRAM_UPDATE_DATE := p_grp_trip_rec.PROGRAM_UPDATE_DATE;
1786 x_pvt_trip_rec.REQUEST_ID := p_grp_trip_rec.REQUEST_ID;
1787 x_pvt_trip_rec.SERVICE_LEVEL := p_grp_trip_rec.SERVICE_LEVEL;
1788 x_pvt_trip_rec.MODE_OF_TRANSPORT := p_grp_trip_rec.MODE_OF_TRANSPORT;
1789 x_pvt_trip_rec.FREIGHT_TERMS_CODE := p_grp_trip_rec.FREIGHT_TERMS_CODE;
1790 x_pvt_trip_rec.CONSOLIDATION_ALLOWED := p_grp_trip_rec.CONSOLIDATION_ALLOWED;
1791 x_pvt_trip_rec.LOAD_TENDER_STATUS := p_grp_trip_rec.LOAD_TENDER_STATUS;
1792 x_pvt_trip_rec.ROUTE_LANE_ID := p_grp_trip_rec.ROUTE_LANE_ID;
1793 x_pvt_trip_rec.LANE_ID := p_grp_trip_rec.LANE_ID;
1794 x_pvt_trip_rec.SCHEDULE_ID := p_grp_trip_rec.SCHEDULE_ID;
1795 x_pvt_trip_rec.BOOKING_NUMBER := p_grp_trip_rec.BOOKING_NUMBER;
1796 x_pvt_trip_rec.ARRIVE_AFTER_TRIP_NAME := p_grp_trip_rec.ARRIVE_AFTER_TRIP_NAME;
1797 x_pvt_trip_rec.SHIP_METHOD_NAME := p_grp_trip_rec.SHIP_METHOD_NAME;
1798 x_pvt_trip_rec.VEHICLE_ITEM_DESC := p_grp_trip_rec.VEHICLE_ITEM_DESC;
1799 x_pvt_trip_rec.VEHICLE_ORGANIZATION_CODE := p_grp_trip_rec.VEHICLE_ORGANIZATION_CODE;
1800 x_pvt_trip_rec.CARRIER_REFERENCE_NUMBER := p_grp_trip_rec.CARRIER_REFERENCE_NUMBER;
1801 x_pvt_trip_rec.CONSIGNEE_CARRIER_AC_NO := p_grp_trip_rec.CONSIGNEE_CARRIER_AC_NO;
1802
1803 IF l_debug_on THEN
1804 WSH_DEBUG_SV.pop(l_module_name);
1805 END IF;
1806 --
1807 EXCEPTION
1808 WHEN OTHERS THEN
1809 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_FTE_INTEGRATION.map_tripgrp_to_pvt',l_module_name);
1810 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1811 --
1812 IF l_debug_on THEN
1813 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1814 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1815 END IF;
1816 --
1817 END map_tripgrp_to_pvt;
1818
1819 PROCEDURE Validate_Trip
1820 (p_trip_info_tab IN OUT NOCOPY WSH_TRIPS_PVT.Trip_Attr_Tbl_Type,
1821 p_action_code IN VARCHAR2,
1822 x_valid_index_tab OUT NOCOPY wsh_util_core.id_tab_type,
1823 x_return_status OUT NOCOPY VARCHAR2,
1824 p_caller IN VARCHAR2 DEFAULT NULL) IS
1825
1826 l_debug_on BOOLEAN;
1827 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_TRIP';
1828
1829 CURSOR get_trip_info(p_trip_id NUMBER) IS
1830 SELECT consolidation_allowed, vehicle_item_id, vehicle_organization_id, ship_method_code, carrier_id, service_level, mode_of_transport, lane_id
1831 FROM wsh_trips
1832 WHERE trip_id= p_trip_id;
1833
1834 l_db_trip_info WSH_TRIPS_PVT.trip_rec_type;
1835
1836 l_ship_method_code varchar2(32764);
1837 l_num_errors NUMBER := 0;
1838 l_num_warnings NUMBER := 0;
1839 l_action VARCHAR2(100);
1840 l_return_status VARCHAR2(1);
1841 x_msg_count NUMBER;
1842 l_index NUMBER;
1843 x_msg_data varchar2(32764);
1844 l_stop_rec WSH_TRIP_STOPS_PVT.trip_stop_rec_type;
1845 l_dummy VARCHAR2(3000);
1846 l_dummy_master_org_id NUMBER;
1847 l_seg_array FND_FLEX_EXT.SegmentArray;
1848 l_vehicle_org_id NUMBER;
1849
1850 e_mixed_trip_error EXCEPTION; -- J-IB-NPARIKH
1851
1852 l_vehicle_name VARCHAR2(2000); --Bug# 3565374
1853 l_vehicle_org_name VARCHAR2(240);
1854 l_vehicle_type NUMBER := 0;
1855
1856 --OTM R12, glog proj
1857 l_gc3_is_installed VARCHAR2(1);
1858
1859
1860 BEGIN
1861 --
1862 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1863 --
1864 IF l_debug_on IS NULL
1865 THEN
1866 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1867 END IF;
1868 --
1869
1870 --OTM R12, glog proj
1871 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1872 --OTM R12, glog proj, use Global Variable
1873 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
1874
1875 -- If null, call the function
1876 IF l_gc3_is_installed IS NULL THEN
1877 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
1878 END IF;
1879 -- end of OTM R12, glog proj
1880
1881 SAVEPOINT validate_trip_grp;
1882 IF l_debug_on THEN
1883 wsh_debug_sv.push(l_module_name);
1884 WSH_DEBUG_SV.log(l_module_name,'p_action_code',p_action_code );
1885 END IF;
1886
1887
1888 l_index := p_trip_info_tab.FIRST;
1889 WHILE l_index IS NOT NULL LOOP
1890 BEGIN
1891 IF l_debug_on THEN
1892 WSH_DEBUG_SV.log(l_module_name,'p_caller',p_caller);
1893 WSH_DEBUG_SV.log(l_module_name,'trip_id',p_trip_info_tab(l_index).trip_id);
1894 WSH_DEBUG_SV.log(l_module_name,'name',p_trip_info_tab(l_index).name);
1895 WSH_DEBUG_SV.log(l_module_name,'arrive_after_trip_id',p_trip_info_tab(l_index).arrive_after_trip_id);
1896 WSH_DEBUG_SV.log(l_module_name,'arrive_after_trip_name',p_trip_info_tab(l_index).arrive_after_trip_name);
1897 WSH_DEBUG_SV.log(l_module_name,'ship_method_name',p_trip_info_tab(l_index).ship_method_name);
1898 WSH_DEBUG_SV.log(l_module_name,'ship_method_code',p_trip_info_tab(l_index).ship_method_code);
1899 WSH_DEBUG_SV.log(l_module_name,'carrier_id',p_trip_info_tab(l_index).carrier_id);
1900 WSH_DEBUG_SV.log(l_module_name,'mode_of_transport',p_trip_info_tab(l_index).mode_of_transport);
1901 WSH_DEBUG_SV.log(l_module_name,'vehicle_organization_id',p_trip_info_tab(l_index).vehicle_organization_id);
1902 WSH_DEBUG_SV.log(l_module_name,'vehicle_organization_code',p_trip_info_tab(l_index).vehicle_organization_code);
1903 WSH_DEBUG_SV.log(l_module_name,'vehicle_item_id',p_trip_info_tab(l_index).vehicle_item_id);
1904 WSH_DEBUG_SV.log(l_module_name,'consolidation_allowed',p_trip_info_tab(l_index).consolidation_allowed);
1905 WSH_DEBUG_SV.log(l_module_name,'freight_terms_code',p_trip_info_tab(l_index).freight_Terms_code);
1906 END IF;
1907
1908 SAVEPOINT validate_trip_loop_grp;
1909
1910 IF p_action_code = 'UPDATE' THEN
1911 l_action := 'UPDATE';
1912 ELSE
1913 l_action := 'ADD';
1914 END IF;
1915
1916 -- J-IB-NPARIKH-{
1917 IF ( WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_CREATE_MIXED_TRIP_LVL) = 1 )
1918 THEN
1919 --{
1920 IF p_trip_info_tab(l_index).shipments_type_flag = 'M'
1921 THEN
1922 -- You cannot create mixed trips through API
1923 --
1924 RAISE e_mixed_trip_error;
1925 END IF;
1926 --}
1927 END IF;
1928 --
1929 -- J-IB-NPARIKH-}
1930
1931 --get the trip info from db for these specific validations
1932 IF ( WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_FREIGHT_CARRIER_LVL) = 1 )
1933 OR ( WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_VEH_ORG_LVL) = 1
1934 AND p_trip_info_tab(l_index).vehicle_organization_id = fnd_api.G_MISS_NUM )
1935 OR ( WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_CONSOL_ALLW_LVL) = 1 ) THEN
1936
1937 OPEN get_trip_info(p_trip_info_tab(l_index).trip_id);
1938 FETCH get_trip_info INTO
1939 l_db_trip_info.consolidation_allowed,
1940 l_db_trip_info.vehicle_item_id,
1941 l_db_trip_info.vehicle_organization_id,
1942 l_db_trip_info.ship_method_code,
1943 l_db_trip_info.carrier_id,
1944 l_db_trip_info.service_level,
1945 l_db_trip_info.mode_of_transport,
1946 l_db_trip_info.lane_id ;
1947 CLOSE get_trip_info;
1948 END IF;
1949
1950 IF ( WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_TRIP_NAME_LVL) = 1 ) THEN
1951 IF (p_trip_info_tab(l_index).trip_id IS NOT NULL ) THEN
1952 l_dummy := NULL;
1953 ELSE
1954 l_dummy := p_trip_info_tab(l_index).name;
1955 END IF;
1956
1957 WSH_UTIL_VALIDATE.Validate_Trip_Name (
1958 p_trip_id => p_trip_info_tab(l_index).trip_id,
1959 p_trip_name => l_dummy,
1960 x_return_status => l_return_status);
1961 IF l_debug_on THEN
1962 WSH_DEBUG_SV.log(l_module_name,'WSH_UTIL_VALIDATE.Validate_Trip_Name l_return_status',l_return_status);
1963 END IF;
1964 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
1965 x_num_warnings =>l_num_warnings,
1966 x_num_errors =>l_num_errors);
1967 END IF;
1968
1969 IF ( WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_ARR_AFTER_TRIP_LVL) = 1 ) THEN
1970 WSH_TRIP_VALIDATIONS.Validate_Arrive_after_trip (
1971 p_trip_id => p_trip_info_tab(l_index).trip_id,
1972 p_arr_after_trip_id => p_trip_info_tab(l_index).arrive_after_trip_id,
1973 p_arr_after_trip_name => p_trip_info_tab(l_index).arrive_after_trip_name,
1974 x_return_status => l_return_status);
1975 IF l_debug_on THEN
1976 WSH_DEBUG_SV.log(l_module_name,'WSH_TRIP_VALIDATIONS.Validate_Arrive_after_trip l_return_status',
1977 l_return_status);
1978 END IF;
1979 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
1980 x_num_warnings =>l_num_warnings,
1981 x_num_errors =>l_num_errors);
1982 END IF;
1983
1984 IF ( WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_FREIGHT_CARRIER_LVL) = 1 ) THEN
1985 WSH_UTIL_VALIDATE.validate_freight_carrier(
1986 p_ship_method_name => p_trip_info_tab(l_index).ship_method_name,
1987 x_ship_method_code => p_trip_info_tab(l_index).ship_method_code,
1988 p_carrier_name => NULL,
1989 x_carrier_id => p_trip_info_tab(l_index).carrier_id,
1990 x_service_level => p_trip_info_tab(l_index).service_level,
1991 x_mode_of_transport => p_trip_info_tab(l_index).mode_of_transport,
1992 p_entity_type => 'TRIP',
1993 p_entity_id => p_trip_info_tab(l_index).trip_id,
1994 p_organization_id => NULL,
1995 x_return_status => l_return_status,
1996 p_caller => p_caller);
1997
1998 IF l_debug_on THEN
1999 WSH_DEBUG_SV.log(l_module_name,'WSH_UTIL_VALIDATE.Validate_Freight_Carrier l_return_status',l_return_status);
2000 END IF;
2001
2002 -- OTM R12, glog project
2003 -- To handle the warning about Invalid Ship Method when the
2004 -- components are valid - Mode,Service and Carrier
2005 -- In 11.5.10, the Input parameter is p_in_rec, here it is p_caller
2006 -- Warning is converted to error when caller is FTE_TMS_INTEGRATION
2007 -- For Inbound messages, there is no way to warn user.
2008 IF (l_gc3_is_installed = 'Y' AND
2009 p_caller = 'FTE_TMS_INTEGRATION' AND
2010 l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2011 IF l_debug_on THEN
2012 WSH_DEBUG_SV.log(l_module_name,'Convert l_return_status',l_return_status);
2013 END IF;
2014 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2015 END IF;
2016 -- OTM R12, end of glog project
2017
2018 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2019 x_num_warnings =>l_num_warnings,
2020 x_num_errors =>l_num_errors);
2021
2022
2023 IF (nvl(l_db_trip_info.ship_method_code,FND_API.G_MISS_CHAR) <>
2024 nvl( p_trip_info_tab(l_index).ship_method_code,FND_API.G_MISS_CHAR) ) THEN
2025 WSH_BOLS_PVT.cancel_bol
2026 ( p_trip_id => p_trip_info_tab(l_index).trip_id
2027 ,p_old_ship_method_code => l_db_trip_info.ship_method_code
2028 ,p_new_ship_method_code => p_trip_info_tab(l_index).ship_method_code
2029 ,x_return_status => l_return_status );
2030
2031 IF l_debug_on THEN
2032 WSH_DEBUG_SV.log(l_module_name,'WSH_BOLS_PVT.cancel_bol l_return_status',l_return_status);
2033 END IF;
2034 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2035 x_num_warnings =>l_num_warnings,
2036 x_num_errors =>l_num_errors);
2037 WSH_MBOLS_PVT.cancel_mbol
2038 ( p_trip_id => p_trip_info_tab(l_index).trip_id
2039 ,x_return_status => l_return_status );
2040
2041 IF l_debug_on THEN
2042 WSH_DEBUG_SV.log(l_module_name,'WSH_MBOLS_PVT.cancel_mbol l_return_status',l_return_status);
2043 END IF;
2044 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2045 x_num_warnings =>l_num_warnings,
2046 x_num_errors =>l_num_errors);
2047 END IF;
2048 END IF;
2049
2050 IF WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_FREIGHT_TERMS_LVL) = 1 THEN
2051 --
2052 wsh_util_validate.validate_freight_terms(
2053 p_freight_terms_code => p_trip_info_tab(l_index).freight_terms_code,
2054 p_freight_terms_name => NULL,
2055 x_return_status => l_return_status);
2056 --
2057 IF l_debug_on THEN
2058 wsh_debug_sv.log(l_module_name,'Return Status After Calling validate_freight_terms',l_return_status);
2059 END IF;
2060 --
2061 WSH_UTIL_CORE.api_post_call(
2062 p_return_status => l_return_status,
2063 x_num_warnings => l_num_warnings,
2064 x_num_errors => l_num_errors);
2065 --
2066 END IF;
2067
2068 IF ( WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_VEH_ITEM_LVL) = 1 ) THEN
2069 IF (p_trip_info_tab(l_index).vehicle_item_id IS NOT NULL ) THEN
2070 l_dummy := NULL;
2071 ELSE
2072 l_dummy := p_trip_info_tab(l_index).vehicle_item_desc;
2073 END IF;
2074
2075 IF (p_trip_info_tab(l_index).vehicle_organization_id IS NULL
2076 AND p_trip_info_tab(l_index).vehicle_item_id IS NOT NULL) THEN
2077 --removed get_vehicle_item_id cursor as this was not being used
2078
2079 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
2080 FND_MESSAGE.SET_TOKEN('FIELD_NAME','vehicle_organization_id');
2081 wsh_util_core.add_message(l_return_status,l_module_name);
2082 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2083 ELSE
2084 WSH_UTIL_VALIDATE.validate_item(
2085 p_inventory_item_id => p_trip_info_tab(l_index).vehicle_item_id,
2086 p_inventory_item => p_trip_info_tab(l_index).vehicle_item_desc,
2087 p_organization_id => p_trip_info_tab(l_index).vehicle_organization_id,
2088 p_seg_array => l_seg_array,
2089 p_item_type => 'VEH_ITEM' ,
2090 x_return_status => l_return_status);
2091 --Bug# 3565374 - Start
2092 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2093 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_VEHICLE');
2094 wsh_util_core.add_message(l_return_status,l_module_name);
2095 END IF;
2096 --Bug# 3565374 - End
2097
2098 END IF;
2099
2100 IF l_debug_on THEN
2101 WSH_DEBUG_SV.log(l_module_name,'WSH_UTIL_VALIDATE.validate_item -vehicle l_return_status',l_return_status);
2102 END IF;
2103 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2104 x_num_warnings =>l_num_warnings,
2105 x_num_errors =>l_num_errors);
2106
2107 END IF;
2108
2109 IF ( WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_VEH_ORG_LVL) = 1 ) THEN
2110
2111 IF (p_trip_info_tab(l_index).vehicle_organization_id = fnd_api.G_MISS_NUM ) THEN --Bug 3534623
2112 p_trip_info_tab(l_index).vehicle_organization_id:=l_db_trip_info.vehicle_organization_id;
2113 END IF;
2114
2115 IF (p_trip_info_tab(l_index).vehicle_organization_id IS NOT NULL ) THEN
2116 l_dummy := NULL;
2117 ELSE
2118 l_dummy := p_trip_info_tab(l_index).vehicle_organization_code;
2119 END IF;
2120 WSH_UTIL_VALIDATE.validate_org(
2121 p_org_id => p_trip_info_tab(l_index).vehicle_organization_id,
2122 p_org_code => p_trip_info_tab(l_index).vehicle_organization_code,
2123 x_return_status => l_return_status);
2124 IF l_debug_on THEN
2125 WSH_DEBUG_SV.log(l_module_name,'WSH_UTIL_VALIDATE.validate_org l_return_status',l_return_status);
2126 END IF;
2127 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2128 x_num_warnings =>l_num_warnings,
2129 x_num_errors =>l_num_errors);
2130
2131
2132 -- Pack J, if FTE is installed, make sure this is a master organization.
2133 IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y') AND (p_trip_info_tab(l_index).vehicle_organization_id IS NOT NULL) THEN
2134 WSH_UTIL_CORE.get_master_from_org(
2135 p_org_id => p_trip_info_tab(l_index).vehicle_organization_id,
2136 x_master_org_id => l_dummy_master_org_id,
2137 x_return_status => l_return_status);
2138
2139 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2140 x_num_warnings =>l_num_warnings,
2141 x_num_errors =>l_num_errors);
2142
2143
2144 IF (p_trip_info_tab(l_index).vehicle_item_id IS NOT NULL) THEN
2145 IF l_debug_on THEN
2146 wsh_debug_sv.logmsg(l_module_name, 'calling get_vehicle_org_id');
2147 END IF;
2148
2149 WSH_FTE_INTEGRATION.GET_VEHICLE_ORG_ID
2150 (p_inventory_item_id => p_trip_info_tab(l_index).vehicle_item_id,
2151 x_vehicle_org_id => l_vehicle_org_id,
2152 x_return_status => l_return_status);
2153
2154 --Bug# 3565374 - Start
2155 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2156 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2157 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2158 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2159 END IF;
2160 l_vehicle_name := WSH_UTIL_CORE.get_item_name (p_item_id => p_trip_info_tab(l_index).VEHICLE_ITEM_ID,
2161 p_organization_id => p_trip_info_tab(l_index).VEHICLE_ORGANIZATION_ID);
2162 l_vehicle_org_name := WSH_UTIL_CORE.get_org_name (p_organization_id => p_trip_info_tab(l_index).VEHICLE_ORGANIZATION_ID);
2163 FND_MESSAGE.SET_NAME('WSH','WSH_VEHICLE_TYPE_UNDEFINED');
2164 FND_MESSAGE.SET_TOKEN('ITEM',l_vehicle_name);
2165 FND_MESSAGE.SET_TOKEN('ORGANIZATION',l_vehicle_org_name);
2166 FND_MSG_PUB.ADD;
2167 END IF;
2168 END IF;
2169 --Bug# 3565374 - End
2170 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2171 x_num_warnings =>l_num_warnings,
2172 x_num_errors =>l_num_errors);
2173
2174 ELSE
2175 --populate with master org
2176 l_vehicle_org_id:=l_dummy_master_org_id;
2177 END IF;
2178
2179 IF l_debug_on THEN
2180 WSH_DEBUG_SV.log(l_module_name,'vehicle org passed in: '|| p_trip_info_tab(l_index).vehicle_organization_id||' master org: '|| l_dummy_master_org_id||' vehicle org found: '||l_vehicle_org_id);
2181 END IF;
2182
2183 --bug 3437995 - if master org is not passed, do not error out, get
2184 --the master org and populate vehicle org with that
2185 p_trip_info_tab(l_index).vehicle_organization_id:=l_vehicle_org_id;
2186 END IF;
2187
2188 END IF;
2189
2190 IF ( WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_TRIP_SMC_LVL) = 1 ) THEN
2191 IF (p_trip_info_tab(l_index).ship_method_code IS NOT NULL ) THEN
2192 l_dummy := NULL;
2193 ELSE
2194 l_dummy := p_trip_info_tab(l_index).ship_method_name;
2195 END IF;
2196 WSH_UTIL_VALIDATE.validate_ship_method(
2197 p_ship_method_code => p_trip_info_tab(l_index).ship_method_code,
2198 p_ship_method_name => l_dummy,
2199 x_return_status => l_return_status);
2200 IF l_debug_on THEN
2201 WSH_DEBUG_SV.log(l_module_name,'WSH_UTIL_VALIDATE.validate_ship_method l_return_status',l_return_status);
2202 END IF;
2203 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2204 x_num_warnings =>l_num_warnings,
2205 x_num_errors =>l_num_errors);
2206 END IF;
2207
2208 IF ( WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_CONSOL_ALLW_LVL) = 1 ) THEN
2209 WSH_TRIP_VALIDATIONS.Validate_Consol_Allowed(
2210 p_trip_info => p_trip_info_tab(l_index),
2211 p_db_trip_info => l_db_trip_info,
2212 x_return_status => l_return_status);
2213 IF l_debug_on THEN
2214 WSH_DEBUG_SV.log(l_module_name,'WSH_TRIP_VALIDATIONS.Validate_Consol_Allowed l_return_status',l_return_status);
2215 END IF;
2216 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2217 x_num_warnings =>l_num_warnings,
2218 x_num_errors =>l_num_errors);
2219 END IF;
2220
2221 IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y') THEN
2222 IF ( WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_TRIP_STOP_VALIDATION_LVL) = 1 ) THEN
2223
2224 -- 4106444 -skattama
2225 -- After Trip is modified
2226 -- If mode is other than TRUCK, the stops should not be greater than 2
2227 WSH_UTIL_VALIDATE.Validate_Trip_MultiStops (
2228 p_trip_id => p_trip_info_tab(l_index).trip_id,
2229 p_mode_of_transport => p_trip_info_tab(l_index).mode_of_transport,
2230 x_return_status => l_return_status);
2231 IF l_debug_on THEN
2232 WSH_DEBUG_SV.log(l_module_name,'WSH_UTIL_VALIDATE.Validate_MultiStops l_return_status',l_return_status);
2233 END IF;
2234 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2235 x_num_warnings =>l_num_warnings,
2236 x_num_errors =>l_num_errors);
2237
2238 -- end 4106444
2239
2240 WSH_FTE_INTEGRATION.trip_stop_validations
2241 (p_stop_rec => l_stop_rec,
2242 p_trip_rec => p_trip_info_tab(l_index),
2243 p_action => l_action,
2244 x_return_status => l_return_status);
2245 IF l_debug_on THEN
2246 WSH_DEBUG_SV.log(l_module_name,'WSH_FTE_INTEGRATION.trip_stop_validations l_return_status',l_return_status);
2247 END IF;
2248 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2249 x_num_warnings =>l_num_warnings,
2250 x_num_errors =>l_num_errors);
2251 END IF;
2252 END IF;
2253
2254 x_valid_index_tab(x_valid_index_tab.COUNT + 1) := l_index;
2255
2256 EXCEPTION
2257 -- J-IB-NPARIKH-{
2258 WHEN e_mixed_trip_error THEN
2259 ROLLBACK TO validate_trip_loop_grp;
2260 l_num_errors := l_num_errors + 1;
2261 FND_MESSAGE.SET_NAME('WSH', 'WSH_MIXED_TRIP_ERROR');
2262 WSH_UTIL_CORE.ADD_MESSAGE(wsh_util_core.g_ret_sts_error, l_module_name);
2263 IF l_debug_on THEN
2264 WSH_DEBUG_SV.logmsg(l_module_name,'e_mixed_trip_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2265 END IF;
2266 -- J-IB-NPARIKH-}
2267
2268 WHEN fnd_api.g_exc_error THEN
2269 Rollback to validate_trip_loop_grp;
2270
2271 WHEN fnd_api.g_exc_unexpected_error THEN
2272 Rollback to validate_trip_loop_grp;
2273
2274 WHEN others THEN
2275 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2276 ROLLBACK TO SAVEPOINT validate_trip_loop_grp;
2277 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2278
2279 END;
2280 l_index := p_trip_info_tab.NEXT(l_index);
2281 END LOOP;
2282
2283 IF (l_num_errors = p_trip_info_tab.count ) THEN
2284 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2285 ELSIF (l_num_errors > 0 ) THEN
2286 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2287 ELSIF (l_num_warnings > 0 ) THEN
2288 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2289 ELSE
2290 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2291 END IF;
2292
2293 IF l_debug_on THEN
2294 WSH_DEBUG_SV.pop(l_module_name);
2295 END IF;
2296 EXCEPTION
2297 WHEN FND_API.G_EXC_ERROR THEN
2298 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
2299 FND_MSG_PUB.Count_And_Get (
2300 p_count => x_msg_count,
2301 p_data => x_msg_data,
2302 p_encoded => FND_API.G_FALSE);
2303 IF l_debug_on THEN
2304 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',
2305 WSH_DEBUG_SV.C_EXCEP_LEVEL);
2306 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2307 END IF;
2308 ROLLBACK TO SAVEPOINT validate_trip_grp;
2309
2310 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2311 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2312 FND_MSG_PUB.Count_And_Get (
2313 p_count => x_msg_count,
2314 p_data => x_msg_data,
2315 p_encoded => FND_API.G_FALSE);
2316 IF l_debug_on THEN
2317 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',
2318 WSH_DEBUG_SV.C_EXCEP_LEVEL);
2319 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2320 END IF;
2321 ROLLBACK TO SAVEPOINT validate_trip_grp;
2322
2323 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
2324 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2325 FND_MSG_PUB.Count_And_Get (
2326 p_count => x_msg_count,
2327 p_data => x_msg_data,
2328 p_encoded => FND_API.G_FALSE);
2329 IF l_debug_on THEN
2330 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',
2331 WSH_DEBUG_SV.C_EXCEP_LEVEL);
2332 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2333 END IF;
2334 -- Bug 2741482
2335 --ROLLBACK TO SAVEPOINT validate_trip_grp;
2336
2337 WHEN OTHERS THEN
2338 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2339 wsh_util_core.default_handler ('WSH_TRIP_STOPS_GRP.Validate_Trip');
2340 FND_MSG_PUB.Count_And_Get (
2341 p_count => x_msg_count,
2342 p_data => x_msg_data,
2343 p_encoded => FND_API.G_FALSE);
2344 IF l_debug_on THEN
2345 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
2346 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2347 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2348 END IF;
2349 ROLLBACK TO SAVEPOINT validate_trip_grp;
2350
2351 END Validate_Trip;
2352
2353
2354 --========================================================================
2355 -- PROCEDURE : Create_Update_Trip CORE API
2356 --
2357 -- PARAMETERS: p_api_version_number known api versionerror buffer
2358 -- p_init_msg_list FND_API.G_TRUE to reset list
2359 -- x_return_status return status
2360 -- x_msg_count number of messages in the list
2361 -- x_msg_data text of messages
2362 -- p_trip_info_tab Table of Attributes for the trip entity
2363 -- p_IN_rec Input Attributes for the trip entity
2364 -- p_OUT_rec Table of output Attributes for the trip entity
2365 -- VERSION : current version 1.0
2366 -- initial version 1.0
2367 -- COMMENT : Core procedure to perform Create and Update action on Trip. This is called by all Wrapper Procedures
2368 --========================================================================
2369 PROCEDURE Create_Update_Trip(
2370 p_api_version_number IN NUMBER,
2371 p_init_msg_list IN VARCHAR2,
2372 p_commit IN VARCHAR2,
2373 p_trip_info_tab IN WSH_TRIPS_PVT.Trip_Attr_Tbl_Type,
2374 p_In_rec IN tripInRecType,
2375 x_Out_Tab OUT NOCOPY trip_Out_Tab_Type,
2376 x_return_status OUT NOCOPY VARCHAR2,
2377 x_msg_count OUT NOCOPY NUMBER,
2378 x_msg_data OUT NOCOPY VARCHAR2) IS
2379
2380 l_api_version_number CONSTANT NUMBER := 1.0;
2381 l_api_name CONSTANT VARCHAR2(30) := 'Create_Update_Trip';
2382 l_debug_on BOOLEAN;
2383 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_TRIP';
2384
2385 RECORD_LOCKED EXCEPTION;
2386 PRAGMA EXCEPTION_INIT(RECORD_LOCKED, -54);
2387
2388 l_num_errors NUMBER :=0;
2389 l_num_warnings NUMBER :=0;
2390 l_index NUMBER;
2391 l_sp_disabled_list VARCHAR2(30) := 'get_disabled_list';
2392 l_return_status VARCHAR2(1);
2393
2394 x_valid_index_tab wsh_util_core.id_tab_type;
2395 l_p_trip_info_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
2396 l_trip_info_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
2397 l_pvt_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
2398
2399 --Compatibility Changes
2400 l_cc_validate_result VARCHAR2(1);
2401 l_cc_failed_records WSH_FTE_COMP_CONSTRAINT_PKG.failed_line_tab_type;
2402 l_cc_group_info WSH_FTE_COMP_CONSTRAINT_PKG.cc_group_tab_type;
2403 l_cc_line_groups WSH_FTE_COMP_CONSTRAINT_PKG.line_group_tab_type;
2404 l_msg_count NUMBER;
2405 l_msg_data VARCHAR2(2000);
2406
2407 l_trip_info_tab_temp WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
2408 l_cc_count_success NUMBER;
2409 b_cc_linefailed BOOLEAN;
2410
2411 --dummy tables for calling validate_constraint_wrapper
2412 l_cc_del_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
2413 l_cc_det_attr_tab WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type;
2414 l_cc_trip_attr_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
2415 l_cc_stop_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
2416 l_cc_in_ids wsh_util_core.id_tab_type;
2417 l_cc_fail_ids wsh_util_core.id_tab_type;
2418
2419 --Compatibility Changes
2420 CURSOR c_getorgcarriersmc (p_tripid NUMBER) IS
2421 SELECT wnd.organization_id, wnd.name, wt.ship_method_code, wt.carrier_id
2422 FROM wsh_new_deliveries wnd, wsh_trip_stops wts, wsh_delivery_legs wdl, wsh_trips wt
2423 WHERE wnd.delivery_id=wdl.delivery_id
2424 and wdl.pick_up_stop_id=wts.stop_id
2425 and wt.trip_id = p_tripid
2426 and wt.trip_id=wts.trip_id
2427 and wt.ignore_for_planning<>'Y'
2428 and rownum=1;
2429
2430 l_wh_type VARCHAR2(3);
2431 l_organization_id wsh_new_deliveries.organization_id%TYPE;
2432 l_smc wsh_trips.ship_method_code%TYPE;
2433 l_carrier_id wsh_trips.carrier_id%TYPE;
2434 l_param_info WSH_SHIPPING_PARAMS_PVT.Global_Parameters_Rec_Typ;
2435 l_autofirm_loadtender_trip Varchar2(10) := null;
2436
2437 l_action_prms WSH_TRIPS_GRP.action_parameters_rectype;
2438 l_action VARCHAR2(20);
2439 l_trip_ids wsh_util_core.id_tab_type;
2440
2441 BEGIN
2442 --
2443 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2444 --
2445 IF l_debug_on IS NULL
2446 THEN
2447 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2448 END IF;
2449 --
2450 SAVEPOINT create_update_trip_grp;
2451 IF l_debug_on THEN
2452 wsh_debug_sv.push (l_module_name, 'Create_Update_Trip');
2453 wsh_debug_sv.log (l_module_name,'p_in_rec.action_code',p_in_rec.action_code);
2454 END IF;
2455
2456 IF l_debug_on THEN
2457 FOR i in 1..p_trip_info_tab.count
2458 LOOP
2459 wsh_debug_sv.log (l_module_name,'p_shipmethod_code',p_trip_info_tab(i).ship_method_code);
2460 END LOOP;
2461 END IF;
2462
2463 IF NOT FND_API.Compatible_API_Call(l_api_version_number, p_api_version_number,l_api_name,G_PKG_NAME) THEN
2464 IF l_debug_on THEN
2465 WSH_DEBUG_SV.logmsg(l_module_name,'Not compatible');
2466 END IF;
2467 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2468 END IF;
2469
2470 IF FND_API.to_Boolean(p_init_msg_list) THEN
2471 FND_MSG_PUB.initialize;
2472 END IF;
2473
2474 IF (p_in_rec.caller IS NULL) THEN
2475 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
2476 FND_MESSAGE.SET_TOKEN('FIELD_NAME','p_in_rec.caller');
2477 wsh_util_core.add_message(x_return_status,l_module_name);
2478 raise fnd_api.g_exc_error;
2479 END IF;
2480
2481 -- for Load Tender
2482 IF (p_in_rec.action_code IS NULL OR p_in_rec.action_code NOT IN ('CREATE','UPDATE','FTE_LOAD_TENDER') ) THEN
2483 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
2484 FND_MESSAGE.SET_TOKEN('FIELD_NAME','p_in_rec.action_code');
2485 wsh_util_core.add_message(x_return_status,l_module_name);
2486 raise fnd_api.g_exc_error;
2487 END IF;
2488
2489 IF (nvl(p_in_rec.phase,1) < 1) THEN
2490 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
2491 FND_MESSAGE.SET_TOKEN('FIELD_NAME','p_in_rec.phase');
2492 wsh_util_core.add_message(x_return_status,l_module_name);
2493 raise fnd_api.g_exc_error;
2494 END IF;
2495
2496 IF (p_trip_info_tab.count = 0 ) THEN
2497 l_p_trip_info_tab(1):=l_pvt_trip_rec;
2498 ELSE
2499 l_p_trip_info_tab:=p_trip_info_tab;
2500 END IF;
2501
2502
2503 WSH_ACTIONS_LEVELS.set_validation_level (
2504 p_entity => 'TRIP',
2505 p_caller => p_in_rec.caller,
2506 p_phase => p_in_rec.phase,
2507 p_action => p_in_rec.action_code,
2508 x_return_status => l_return_status);
2509
2510 IF l_debug_on THEN
2511 WSH_DEBUG_SV.log(l_module_name,'WSH_ACTIONS_LEVELS.set_validation_level l_return_status',l_return_status);
2512 END IF;
2513
2514 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2515 x_num_warnings =>l_num_warnings,
2516 x_num_errors =>l_num_errors);
2517
2518 IF ( WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_DISABLED_LIST_LVL) = 1 ) THEN
2519 l_index := l_p_trip_info_tab.FIRST;
2520 WHILE l_index IS NOT NULL LOOP
2521 BEGIN
2522 SAVEPOINT l_sp_disabled_list;
2523 WSH_TRIP_VALIDATIONS.get_disabled_list(
2524 p_trip_rec => l_p_trip_info_tab(l_index),
2525 p_in_rec => p_in_rec,
2526 x_return_status => l_return_status,
2527 x_msg_count => x_msg_count,
2528 x_msg_data => x_msg_data,
2529 x_trip_rec => l_trip_info_tab(l_index));
2530
2531 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2532 x_num_warnings =>l_num_warnings,
2533 x_num_errors =>l_num_errors);
2534 EXCEPTION
2535 WHEN fnd_api.g_exc_error THEN
2536 Rollback to l_sp_disabled_list;
2537
2538 WHEN fnd_api.g_exc_unexpected_error THEN
2539 Rollback to l_sp_disabled_list;
2540
2541 WHEN others THEN
2542 Rollback to l_sp_disabled_list;
2543 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2544 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2545 END;
2546 l_index := l_p_trip_info_tab.NEXT(l_index);
2547 END LOOP;
2548
2549 IF (l_num_errors = l_p_trip_info_tab.COUNT) THEN
2550 raise fnd_api.g_exc_error;
2551 END IF;
2552 ELSE
2553 l_trip_info_tab := l_p_trip_info_tab;
2554 END IF;
2555
2556 IF l_debug_on THEN
2557 WSH_DEBUG_SV.log(l_module_name,'count, l_num_errors',l_trip_info_tab.COUNT ||','||l_num_errors);
2558 END IF;
2559
2560 IF WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_VALIDATE_CONSTRAINTS_LVL) = 1 THEN --{
2561 --Compatiblity Changes
2562 IF (wsh_util_core.fte_is_installed = 'Y') THEN
2563
2564 WSH_FTE_COMP_CONSTRAINT_PKG.validate_constraint_main(
2565 p_api_version_number => p_api_version_number,
2566 p_init_msg_list => p_init_msg_list,
2567 p_entity_type => 'T',
2568 p_target_id => null,
2569 p_action_code => p_in_rec.action_code,
2570 p_del_attr_tab => l_cc_del_attr_tab,
2571 p_det_attr_tab => l_cc_det_attr_tab,
2572 p_trip_attr_tab => l_trip_info_tab,
2573 p_stop_attr_tab => l_cc_stop_attr_tab,
2574 p_in_ids => l_cc_in_ids,
2575 x_fail_ids => l_cc_fail_ids,
2576 x_validate_result => l_cc_validate_result,
2577 x_failed_lines => l_cc_failed_records,
2578 x_line_groups => l_cc_line_groups,
2579 x_group_info => l_cc_group_info,
2580 x_msg_count => l_msg_count,
2581 x_msg_data => l_msg_data,
2582 x_return_status => l_return_status);
2583
2584
2585 IF l_debug_on THEN
2586 wsh_debug_sv.log(l_module_name,'Return Status After Calling validate_constraint_wrap',l_return_status);
2587 wsh_debug_sv.log(l_module_name,'validate_result After Calling validate_constraint_wrap',l_cc_validate_result);
2588 wsh_debug_sv.log(l_module_name,'msg_count After Calling validate_constraint_wrap',l_msg_count);
2589 wsh_debug_sv.log(l_module_name,'msg_data After Calling validate_constraint_wrap',l_msg_data);
2590 wsh_debug_sv.log(l_module_name,'fail_ids count After Calling validate_constraint_wrap',l_cc_failed_records.COUNT);
2591 wsh_debug_sv.log(l_module_name,'l_cc_line_groups.count count After Calling validate_constraint_wrap',l_cc_line_groups.COUNT);
2592 wsh_debug_sv.log(l_module_name,'group_info count After Calling validate_constraint_wrap',l_cc_group_info.COUNT);
2593 END IF;
2594 --
2595
2596 IF l_return_status=wsh_util_core.g_ret_sts_error THEN
2597 --fix p_rec_attr_tab to have only successful records
2598 l_cc_count_success:=1;
2599
2600 IF l_debug_on THEN
2601 wsh_debug_sv.log(l_module_name,'trip_info_tab count before removing failed lines',l_trip_info_tab.COUNT);
2602 END IF;
2603
2604 IF l_cc_fail_ids.COUNT>0 AND l_trip_info_tab.COUNT>0 THEN
2605 FOR i in l_trip_info_tab.FIRST..l_trip_info_tab.LAST LOOP
2606 b_cc_linefailed:=FALSE;
2607 FOR j in l_cc_fail_ids.FIRST..l_cc_fail_ids.LAST LOOP
2608 IF (l_trip_info_tab(i).trip_id=l_cc_fail_ids(j)) THEN
2609 b_cc_linefailed:=TRUE;
2610 END IF;
2611 END LOOP;
2612 IF (NOT(b_cc_linefailed)) THEN
2613 l_trip_info_tab_temp(l_cc_count_success):=l_trip_info_tab(i);
2614 l_cc_count_success:=l_cc_count_success+1;
2615 END IF;
2616 END LOOP;
2617 END IF;
2618
2619 IF l_trip_info_tab_temp.COUNT>0 THEN
2620 l_trip_info_tab:=l_trip_info_tab_temp;
2621 END IF;
2622
2623 IF l_debug_on THEN
2624 wsh_debug_sv.log(l_module_name,'trip_info_tab count after removing failed lines',l_trip_info_tab.COUNT);
2625 END IF;
2626
2627 END IF;
2628
2629
2630 IF l_return_status=wsh_util_core.g_ret_sts_error and l_cc_fail_ids.COUNT<>l_trip_info_tab.COUNT THEN
2631 l_return_status:=wsh_util_core.g_ret_sts_warning;
2632 END IF;
2633
2634 wsh_util_core.api_post_call(
2635 p_return_status => l_return_status,
2636 x_num_warnings => l_num_warnings,
2637 x_num_errors => l_num_errors,
2638 p_msg_data => l_msg_data);
2639
2640
2641 END IF;
2642 --Compatiblity Changes
2643 END IF;--}
2644
2645 l_num_errors:= 0;
2646
2647 IF l_debug_on THEN
2648 wsh_debug_sv.log(l_module_name,'p_in_rec.caller',p_in_rec.caller);
2649 END IF;
2650
2651 Validate_Trip(
2652 p_trip_info_tab => l_trip_info_tab,
2653 p_action_code => p_in_rec.action_code,
2654 x_valid_index_tab => x_valid_index_tab,
2655 x_return_status => l_return_status,
2656 p_caller => p_in_rec.caller);
2657
2658 IF l_debug_on THEN
2659 WSH_DEBUG_SV.log(l_module_name,'Validate_trip l_return_status',l_return_status);
2660 END IF;
2661
2662 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2663 x_num_warnings =>l_num_warnings,
2664 x_num_errors =>l_num_errors);
2665
2666
2667 l_index := x_valid_index_tab.FIRST;
2668 WHILE l_index IS NOT NULL LOOP
2669 BEGIN
2670 SAVEPOINT l_trip;
2671 IF (p_in_rec.action_code = 'CREATE' ) THEN
2672 WSH_TRIPS_PVT.CREATE_TRIP (
2673 p_trip_info => l_trip_info_tab(x_valid_index_tab(l_index)),
2674 x_rowid => x_out_Tab(l_index).rowid,
2675 x_trip_id => x_out_Tab(l_index).trip_id,
2676 x_name => x_out_Tab(l_index).trip_name,
2677 x_return_status => l_return_status);
2678
2679 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2680 x_num_warnings =>l_num_warnings,
2681 x_num_errors =>l_num_errors);
2682 ELSIF (p_in_rec.action_code = 'UPDATE' ) THEN
2683
2684 IF p_in_rec.caller = 'WSH_FSTRX' THEN
2685 -- fill in the columns not queried into STF or QSUI
2686 SELECT
2687 ROUTING_RULE_ID,
2688 APPEND_FLAG,
2689 RANK_ID
2690 INTO
2691 l_trip_info_tab(x_valid_index_tab(l_index)).ROUTING_RULE_ID,
2692 l_trip_info_tab(x_valid_index_tab(l_index)).APPEND_FLAG,
2693 l_trip_info_tab(x_valid_index_tab(l_index)).RANK_ID
2694 FROM WSH_TRIPS
2695 WHERE ROWID = l_trip_info_tab(x_valid_index_tab(l_index)).rowid;
2696 END IF;
2697
2698 WSH_TRIPS_PVT.UPDATE_TRIP(
2699 p_rowid => l_trip_info_tab(x_valid_index_tab(l_index)).rowid,
2700 p_trip_info => l_trip_info_tab(x_valid_index_tab(l_index)),
2701 x_return_status => l_return_status);
2702
2703 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2704 x_num_warnings =>l_num_warnings,
2705 x_num_errors =>l_num_errors);
2706
2707 --J TP Release : firm/plan/unplan trip based on parameter for load tender update
2708 -- moved update statement from previous release to table handler
2709 IF l_debug_on THEN
2710 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2711 END IF;
2712
2713 WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters(
2714 x_Param_Info => l_param_info,
2715 x_return_status => l_return_status);
2716
2717 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2718 x_num_warnings =>l_num_warnings,
2719 x_num_errors =>l_num_errors);
2720
2721 l_autofirm_loadtender_trip:=l_param_info.AUTOFIRM_LOAD_TENDERED_TRIPS;
2722
2723
2724 IF l_debug_on THEN
2725 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2726 WSH_DEBUG_SV.log(l_module_name,'load_tender_status',l_trip_info_tab(x_valid_index_tab(l_index)).load_tender_status);
2727 WSH_DEBUG_SV.log(l_module_name,'l_autofirm_loadtender_trip',l_autofirm_loadtender_trip);
2728 END IF;
2729 IF l_trip_info_tab(x_valid_index_tab(l_index)).load_tender_status is not null AND l_autofirm_loadtender_trip IN ('Y','F') THEN
2730 IF l_trip_info_tab(x_valid_index_tab(l_index)).load_tender_status IN ('TENDERED','ACCEPTED','RETENDERED', 'AUTO_ACCEPTED') THEN
2731 IF l_autofirm_loadtender_trip='Y' THEN
2732 l_action:='PLAN';
2733 ELSE
2734 l_action:='FIRM';
2735 END IF;
2736 ELSIF l_trip_info_tab(x_valid_index_tab(l_index)).load_tender_status IN ('REJECTED','SHIPPER_CANCELLED') THEN
2737 l_action:='UNPLAN';
2738 END IF;
2739 IF l_action is not null THEN
2740 l_trip_ids(1):=l_trip_info_tab(x_valid_index_tab(l_index)).trip_id;
2741 wsh_trips_actions.Plan(p_trip_rows => l_trip_ids,
2742 p_action => l_action,
2743 x_return_status => l_return_status);
2744
2745 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
2746 x_num_warnings =>l_num_warnings,
2747 x_num_errors =>l_num_errors);
2748 END IF;
2749 END IF;--call is for load tender update
2750
2751 /***TP Release**/
2752 --TP Release : if carrier or smc is changed, if they are CMS/TPW, call change_ignoreplan_status
2753 l_smc:=l_trip_info_tab(x_valid_index_tab(l_index)).ship_method_code;
2754 l_carrier_id:=l_trip_info_tab(x_valid_index_tab(l_index)).carrier_id;
2755 IF l_debug_on THEN
2756 WSH_DEBUG_SV.log(l_module_name,'smc',l_smc);
2757 WSH_DEBUG_SV.log(l_module_name,'carrier_id',l_carrier_id);
2758 END IF;
2759 IF (l_carrier_id is not null OR l_smc is not null)
2760 AND WSH_UTIL_CORE.TP_Is_Installed = 'Y' THEN
2761
2762 FOR cur in c_getorgcarriersmc (l_trip_info_tab(x_valid_index_tab(l_index)).trip_id) LOOP
2763 l_organization_id:=cur.organization_id;
2764 IF l_smc is null THEN
2765 l_smc:=cur.ship_method_code;
2766 END IF;
2767 IF l_carrier_id is null THEN
2768 l_carrier_id:=cur.carrier_id;
2769 END IF;
2770 l_wh_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type
2771 (p_organization_id => l_organization_id,
2772 x_return_status => l_return_status,
2773 p_carrier_id => l_carrier_id,
2774 p_ship_method_code=> l_smc,
2775 p_msg_display => 'N');
2776
2777 IF l_debug_on THEN
2778 WSH_DEBUG_SV.log(l_module_name,'Get_Warehouse_Type organization_id,l_wh_type,l_return_status',l_organization_id||l_wh_type||l_return_status);
2779 WSH_DEBUG_SV.log(l_module_name,'carrier_id',l_carrier_id);
2780 WSH_DEBUG_SV.log(l_module_name,'smc',l_smc);
2781 END IF;
2782
2783 WSH_UTIL_CORE.api_post_call(
2784 p_return_status => l_return_status,
2785 x_num_warnings => l_num_warnings,
2786 x_num_errors => l_num_errors);
2787
2788 --if org is a tpw/cms and current ignore plan is 'N', change ignore plan by
2789 --calling api.
2790
2791 IF nvl(l_wh_type, FND_API.G_MISS_CHAR) IN ('TPW','CMS') THEN
2792 l_trip_ids(1):=l_trip_info_tab(x_valid_index_tab(l_index)).trip_id;
2793 wsh_tp_release.change_ignoreplan_status
2794 (p_entity => 'TRIP',
2795 p_in_ids => l_trip_ids,
2796 p_action_code => 'IGNORE_PLAN',
2797 x_return_status => l_return_status);
2798 IF l_debug_on THEN
2799 wsh_debug_sv.log(l_module_name,'Return Status After Calling change_ignoreplan_sttatus',l_return_status);
2800 END IF;
2801 WSH_UTIL_CORE.api_post_call(
2802 p_return_status => l_return_status,
2803 x_num_warnings => l_num_warnings,
2804 x_num_errors => l_num_errors);
2805 END IF;
2806 END LOOP;
2807
2808 -- TP call back to unfirm continuous move or delete continuous move or
2809 -- any other action that will be done in the future based on the action performed
2810 IF WSH_UTIL_CORE.TP_IS_INSTALLED='Y' THEN
2811 l_action_prms.action_code:=p_in_rec.action_code;
2812 l_action_prms.caller:=p_in_rec.caller;
2813 WSH_FTE_TP_INTEGRATION.trip_callback (
2814 p_api_version_number => 1.0,
2815 p_init_msg_list => FND_API.G_TRUE,
2816 x_return_status => l_return_status,
2817 x_msg_count => l_msg_count,
2818 x_msg_data => l_msg_data,
2819 p_action_prms => l_action_prms,
2820 p_rec_attr_tab => l_trip_info_tab);
2821
2822 IF l_debug_on THEN
2823 WSH_DEBUG_SV.log(l_module_name,'after calling trip_callback l_return_status',l_return_status);
2824 END IF;
2825
2826 wsh_util_core.api_post_call(
2827 p_return_status => l_return_status,
2828 x_num_warnings => l_num_warnings,
2829 x_num_errors => l_num_errors
2830 );
2831 END IF;--tp_is_installed
2832
2833 END IF;
2834 /***TP Release**/
2835
2836 END IF;--create or update
2837
2838 EXCEPTION
2839 WHEN fnd_api.g_exc_error THEN
2840 Rollback to l_trip;
2841
2842 WHEN fnd_api.g_exc_unexpected_error THEN
2843 Rollback to l_trip;
2844
2845 WHEN others THEN
2846 Rollback to l_trip;
2847 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2848 END;
2849 l_index := x_valid_index_tab.NEXT(l_index);
2850 END LOOP;
2851
2852 IF (l_num_errors = l_trip_info_tab.count ) THEN
2853 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2854 ELSIF (l_num_errors > 0 ) THEN
2855 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2856 ELSIF (l_num_warnings > 0 ) THEN
2857 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2858 ELSE
2859 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2860 END IF;
2861
2862 IF FND_API.To_Boolean( p_commit ) THEN
2863 COMMIT WORK;
2864 END IF;
2865
2866 FND_MSG_PUB.Count_And_Get
2867 ( p_count => x_msg_count,
2868 p_data => x_msg_data,
2869 p_encoded => FND_API.G_FALSE );
2870
2871 IF l_debug_on THEN
2872 WSH_DEBUG_SV.pop(l_module_name);
2873 END IF;
2874 EXCEPTION
2875 WHEN RECORD_LOCKED THEN
2876 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2877 FND_MESSAGE.Set_Name('WSH', 'WSH_NO_LOCK');
2878 wsh_util_core.add_message(x_return_status,l_module_name);
2879 FND_MSG_PUB.Count_And_Get (
2880 p_count => x_msg_count,
2881 p_data => x_msg_data,
2882 p_encoded => FND_API.G_FALSE);
2883 IF l_debug_on THEN
2884 WSH_DEBUG_SV.logmsg(l_module_name,'RECORD_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2885 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:RECORD_LOCKED');
2886 END IF;
2887 ROLLBACK TO create_update_trip_grp;
2888
2889 WHEN FND_API.G_EXC_ERROR THEN
2890 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
2891 FND_MSG_PUB.Count_And_Get (
2892 p_count => x_msg_count,
2893 p_data => x_msg_data,
2894 p_encoded => FND_API.G_FALSE);
2895 IF l_debug_on THEN
2896 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2897 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2898 END IF;
2899 ROLLBACK TO create_update_trip_grp;
2900
2901 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2902 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2903 FND_MSG_PUB.Count_And_Get (
2904 p_count => x_msg_count,
2905 p_data => x_msg_data,
2906 p_encoded => FND_API.G_FALSE);
2907 IF l_debug_on THEN
2908 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2909 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2910 END IF;
2911 ROLLBACK TO create_update_trip_grp;
2912
2913 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
2914 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2915 FND_MSG_PUB.Count_And_Get (
2916 p_count => x_msg_count,
2917 p_data => x_msg_data,
2918 p_encoded => FND_API.G_FALSE);
2919 IF l_debug_on THEN
2920 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2921 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2922 END IF;
2923 -- Bug 2741482
2924 --ROLLBACK TO create_update_trip_grp;
2925
2926 WHEN OTHERS THEN
2927 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2928 wsh_util_core.default_handler ('WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP');
2929 FND_MSG_PUB.Count_And_Get (
2930 p_count => x_msg_count,
2931 p_data => x_msg_data,
2932 p_encoded => FND_API.G_FALSE);
2933 IF l_debug_on THEN
2934 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
2935 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2936 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2937 END IF;
2938 ROLLBACK TO create_update_trip_grp;
2939
2940 END CREATE_UPDATE_TRIP;
2941
2942 --========================================================================
2943 -- PROCEDURE : Create_Update_Trip Wrapper
2944 --
2945 -- PARAMETERS: p_api_version_number known api versionerror buffer
2946 -- p_init_msg_list FND_API.G_TRUE to reset list
2947 -- x_return_status return status
2948 -- x_msg_count number of messages in the list
2949 -- x_msg_data text of messages
2950 -- p_trip_info Attributes for the trip entity
2951 -- p_trip_IN_rec Input Attributes for the trip entity
2952 -- p_trip_OUT_rec Output Attributes for the trip entity
2953 -- VERSION : current version 1.0
2954 -- initial version 1.0
2955 -- COMMENT : Creates or updates a record in wsh_trips table with information
2956 -- specified in p_trip_info
2957 --========================================================================
2958
2959 PROCEDURE Create_Update_Trip_New
2960 ( p_api_version_number IN NUMBER,
2961 p_init_msg_list IN VARCHAR2,
2962 x_return_status OUT NOCOPY VARCHAR2,
2963 x_msg_count OUT NOCOPY NUMBER,
2964 x_msg_data OUT NOCOPY VARCHAR2,
2965 p_trip_info IN OUT NOCOPY Trip_Pub_Rec_Type,
2966 p_trip_IN_rec IN tripInRecType,
2967 p_trip_OUT_rec OUT NOCOPY tripOutRecType) IS
2968
2969 l_api_version_number CONSTANT NUMBER := 1.0;
2970 l_api_name CONSTANT VARCHAR2(30):= 'Create_Update_Trip';
2971 l_debug_on BOOLEAN;
2972 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_TRIP_NEW';
2973
2974 l_num_errors NUMBER;
2975 l_num_warnings NUMBER;
2976 l_pvt_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
2977 l_trip_info_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
2978 l_out_tab trip_out_tab_type;
2979 l_commit VARCHAR2(1):='F';
2980 BEGIN
2981 -- Standard call to check for call compatibility
2982 --
2983 -- Debug Statements
2984 --
2985 --
2986 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2987 --
2988 IF l_debug_on IS NULL
2989 THEN
2990 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2991 END IF;
2992 --
2993 IF l_debug_on THEN
2994 WSH_DEBUG_SV.push(l_module_name);
2995 --
2996 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION_NUMBER',P_API_VERSION_NUMBER);
2997 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
2998 WSH_DEBUG_SV.log(l_module_name,'TRIP_ID',p_trip_info.trip_id);
2999 WSH_DEBUG_SV.log(l_module_name,'NAME',p_trip_info.name);
3000 WSH_DEBUG_SV.log(l_module_name,'VEHICLE_ITEM_ID',p_trip_info.vehicle_item_id);
3001 WSH_DEBUG_SV.log(l_module_name,'VEHICLE_ORGANIZATION_ID',p_trip_info.vehicle_organization_id);
3002 WSH_DEBUG_SV.log(l_module_name,'CARRIER_ID',p_trip_info.carrier_id);
3003 WSH_DEBUG_SV.log(l_module_name,'SHIP_METHOD_CODE',p_trip_info.ship_method_code);
3004 WSH_DEBUG_SV.log(l_module_name,'SERVICE_LEVEL',p_trip_info.service_level);
3005 WSH_DEBUG_SV.log(l_module_name,'MODE_OF_TRANSPORT',p_trip_info.mode_of_transport);
3006 WSH_DEBUG_SV.log(l_module_name,'CONSOLIDATION_ALLOWED',p_trip_info.consolidation_allowed);
3007 WSH_DEBUG_SV.log(l_module_name,'PLANNED_FLAG',p_trip_info.planned_flag);
3008 WSH_DEBUG_SV.log(l_module_name,'STATUS_CODE',p_trip_info.status_code);
3009 WSH_DEBUG_SV.log(l_module_name,'FREIGHT_TERMS_CODE',p_trip_info.freight_terms_code);
3010 WSH_DEBUG_SV.log(l_module_name,'LANE_ID',p_trip_info.lane_id);
3011 END IF;
3012 --
3013
3014 IF NOT FND_API.Compatible_API_Call (l_api_version_number,p_api_version_number ,l_api_name ,G_PKG_NAME) THEN
3015 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3016 END IF;
3017
3018 IF FND_API.to_Boolean(p_init_msg_list) THEN
3019 FND_MSG_PUB.initialize;
3020 END IF;
3021
3022 map_tripgrp_to_pvt (
3023 p_grp_trip_rec => p_trip_info,
3024 x_pvt_trip_rec => l_pvt_trip_rec,
3025 x_return_status => x_return_status);
3026 IF l_debug_on THEN
3027 wsh_debug_sv.log (l_module_name, 'map_tripgrp_to_pvt x_return_status',x_return_status);
3028 END IF;
3029 IF ( x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
3030 raise FND_API.G_EXC_ERROR;
3031 END IF;
3032
3033 l_trip_info_tab(1):= l_pvt_trip_rec;
3034
3035 WSH_INTERFACE_GRP.Create_Update_Trip(
3036 p_api_version_number => p_api_version_number,
3037 p_init_msg_list => p_init_msg_list,
3038 p_commit => l_commit,
3039 x_return_status => x_return_status,
3040 x_msg_count => x_msg_count,
3041 x_msg_data => x_msg_data,
3042 p_trip_info_tab => l_trip_info_tab,
3043 p_In_rec => p_trip_In_rec,
3044 x_Out_tab => l_Out_Tab);
3045
3046 IF l_debug_on THEN
3047 wsh_debug_sv.log (l_module_name,'In Wrapper Create_Update_Trip x_return_status',x_return_status);
3048 END IF;
3049
3050 wsh_util_core.api_post_call(
3051 p_return_status => x_return_status,
3052 x_num_errors => l_num_errors,
3053 x_num_warnings => l_num_warnings,
3054 p_msg_data => x_msg_data);
3055
3056 IF l_Out_Tab.COUNT <> 0 THEN
3057 p_trip_out_rec := l_out_tab(l_out_tab.FIRST);
3058 END IF;
3059 --
3060 IF l_num_warnings > 0 THEN
3061 x_return_status := wsh_util_core.g_ret_sts_warning;
3062 ELSE
3063 x_return_status := wsh_util_core.g_ret_sts_success;
3064 END IF;
3065
3066 FND_MSG_PUB.Count_And_Get (
3067 p_count => x_msg_count,
3068 p_data => x_msg_data);
3069
3070 IF l_debug_on THEN
3071 WSH_DEBUG_SV.pop(l_module_name);
3072 END IF;
3073 EXCEPTION
3074 WHEN FND_API.G_EXC_ERROR THEN
3075 IF l_debug_on THEN
3076 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3077 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3078 END IF;
3079
3080 WHEN OTHERS THEN
3081 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3082 wsh_util_core.default_handler ('WSH_TRIP_STOPS_GRP.CREATE_UPDATE_TRIP_NEW');
3083 IF l_debug_on THEN
3084 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
3085 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3086 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3087 END IF;
3088 END Create_Update_Trip_New;
3089
3090
3091 --========================================================================
3092 -- PROCEDURE : Create_Update_Trip Wrapper
3093 --
3094 -- PARAMETERS: p_api_version_number known api versionerror buffer
3095 -- p_init_msg_list FND_API.G_TRUE to reset list
3096 -- x_return_status return status
3097 -- x_msg_count number of messages in the list
3098 -- x_msg_data text of messages
3099 -- p_trip_info Attributes for the trip entity
3100 -- p_trip_name Trip name for update
3101 -- x_trip_id Trip id of new trip
3102 -- x_trip_name Trip name of new trip
3103 -- VERSION : current version 1.0
3104 -- initial version 1.0
3105 -- COMMENT : Creates or updates a record in wsh_trips table with information
3106 -- specified in p_trip_info
3107 --========================================================================
3108
3109 PROCEDURE Create_Update_Trip
3110 ( p_api_version_number IN NUMBER,
3111 p_init_msg_list IN VARCHAR2,
3112 x_return_status OUT NOCOPY VARCHAR2,
3113 x_msg_count OUT NOCOPY NUMBER,
3114 x_msg_data OUT NOCOPY VARCHAR2,
3115 p_action_code IN VARCHAR2,
3116 p_trip_info IN OUT NOCOPY Trip_Pub_Rec_Type,
3117 p_trip_name IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
3118 x_trip_id OUT NOCOPY NUMBER,
3119 x_trip_name OUT NOCOPY VARCHAR2) IS
3120
3121 l_api_version_number CONSTANT NUMBER := 1.0;
3122 l_api_name CONSTANT VARCHAR2(30):= 'Create_Update_Trip';
3123 l_debug_on BOOLEAN;
3124 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_TRIP';
3125
3126 l_num_errors NUMBER;
3127 l_num_warnings NUMBER;
3128 l_pvt_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
3129 l_trip_info_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
3130 l_out_tab trip_out_tab_type;
3131 l_in_rec TripInRecType;
3132 l_commit VARCHAR2(1):='F';
3133 BEGIN
3134 --
3135 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3136 --
3137 IF l_debug_on IS NULL
3138 THEN
3139 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3140 END IF;
3141 --
3142 IF l_debug_on THEN
3143 WSH_DEBUG_SV.push(l_module_name);
3144 --
3145 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION_NUMBER',P_API_VERSION_NUMBER);
3146 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
3147 WSH_DEBUG_SV.log(l_module_name,'P_ACTION_CODE',P_ACTION_CODE);
3148 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_NAME',P_TRIP_NAME);
3149 WSH_DEBUG_SV.log(l_module_name,'TRIP_ID',p_trip_info.trip_id);
3150 WSH_DEBUG_SV.log(l_module_name,'NAME',p_trip_info.name);
3151 WSH_DEBUG_SV.log(l_module_name,'VEHICLE_ITEM_ID',p_trip_info.vehicle_item_id);
3152 WSH_DEBUG_SV.log(l_module_name,'VEHICLE_ORGANIZATION_ID',p_trip_info.vehicle_organization_id);
3153 WSH_DEBUG_SV.log(l_module_name,'CARRIER_ID',p_trip_info.carrier_id);
3154 WSH_DEBUG_SV.log(l_module_name,'SHIP_METHOD_CODE',p_trip_info.ship_method_code);
3155 WSH_DEBUG_SV.log(l_module_name,'SERVICE_LEVEL',p_trip_info.service_level);
3156 WSH_DEBUG_SV.log(l_module_name,'MODE_OF_TRANSPORT',p_trip_info.mode_of_transport);
3157 WSH_DEBUG_SV.log(l_module_name,'CONSOLIDATION_ALLOWED',p_trip_info.consolidation_allowed);
3158 WSH_DEBUG_SV.log(l_module_name,'PLANNED_FLAG',p_trip_info.planned_flag);
3159 WSH_DEBUG_SV.log(l_module_name,'STATUS_CODE',p_trip_info.status_code);
3160 WSH_DEBUG_SV.log(l_module_name,'FREIGHT_TERMS_CODE',p_trip_info.freight_terms_code);
3161 WSH_DEBUG_SV.log(l_module_name,'LANE_ID',p_trip_info.lane_id);
3162 END IF;
3163
3164 IF NOT FND_API.Compatible_API_Call (l_api_version_number,p_api_version_number ,l_api_name ,G_PKG_NAME) THEN
3165 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3166 END IF;
3167
3168 IF FND_API.to_Boolean(p_init_msg_list) THEN
3169 FND_MSG_PUB.initialize;
3170 END IF;
3171
3172 map_tripgrp_to_pvt (
3173 p_grp_trip_rec => p_trip_info,
3174 x_pvt_trip_rec => l_pvt_trip_rec,
3175 x_return_status => x_return_status);
3176 IF l_debug_on THEN
3177 wsh_debug_sv.log (l_module_name, 'map_tripgrp_to_pvt x_return_status',x_return_status);
3178 END IF;
3179 IF ( x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
3180 raise FND_API.G_EXC_ERROR;
3181 END IF;
3182
3183 IF (p_trip_name IS NOT NULL) AND (p_trip_name <> FND_API.G_MISS_CHAR) THEN
3184 l_pvt_trip_rec.name := p_trip_name;
3185 END IF;
3186
3187 l_in_rec.caller:='WSH_GRP';
3188 l_in_rec.phase:= 1;
3189 l_in_rec.action_code:= p_action_code;
3190
3191 l_trip_info_tab(1):= l_pvt_trip_rec;
3192
3193 WSH_INTERFACE_GRP.Create_Update_Trip(
3194 p_api_version_number => p_api_version_number,
3195 p_init_msg_list => p_init_msg_list,
3196 p_commit => l_commit,
3197 x_return_status => x_return_status,
3198 x_msg_count => x_msg_count,
3199 x_msg_data => x_msg_data,
3200 p_trip_info_tab => l_trip_info_tab,
3201 p_In_rec => l_In_rec,
3202 x_Out_tab => l_Out_Tab);
3203
3204 IF l_debug_on THEN
3205 wsh_debug_sv.log (l_module_name,'In Wrapper Create_Update_Trip x_return_status',x_return_status);
3206 END IF;
3207 wsh_util_core.api_post_call(
3208 p_return_status => x_return_status,
3209 x_num_errors => l_num_errors,
3210 x_num_warnings => l_num_warnings,
3211 p_msg_data => x_msg_data);
3212
3213 IF l_Out_Tab.COUNT <> 0 THEN
3214 x_trip_id := l_out_tab(l_out_tab.FIRST).trip_id;
3215 x_trip_name := l_out_tab(l_out_tab.FIRST).trip_name;
3216 END IF;
3217 --
3218 IF l_num_warnings > 0 THEN
3219 x_return_status := wsh_util_core.g_ret_sts_warning;
3220 ELSE
3221 x_return_status := wsh_util_core.g_ret_sts_success;
3222 END IF;
3223
3224 FND_MSG_PUB.Count_And_Get (
3225 p_count => x_msg_count,
3226 p_data => x_msg_data);
3227
3228 IF l_debug_on THEN
3229 WSH_DEBUG_SV.pop(l_module_name);
3230 END IF;
3231 EXCEPTION
3232 WHEN FND_API.G_EXC_ERROR THEN
3233 IF l_debug_on THEN
3234 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3235 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3236 END IF;
3237
3238 WHEN OTHERS THEN
3239 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3240 wsh_util_core.default_handler ('WSH_TRIP_STOPS_GRP.CREATE_UPDATE_TRIP_NEW');
3241 IF l_debug_on THEN
3242 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
3243 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3244 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3245 END IF;
3246 END Create_Update_Trip;
3247 --Harmonization Project **heali
3248
3249 -- API to get Trip Details
3250 PROCEDURE get_trip_details_pvt
3251 (p_trip_id IN NUMBER,
3252 x_trip_rec OUT NOCOPY WSH_TRIPS_PVT.TRIP_REC_TYPE,
3253 x_return_status OUT NOCOPY VARCHAR2) IS
3254
3255 CURSOR c_old_trip_details(v_trip_id IN NUMBER) IS
3256 SELECT TRIP_ID,
3257 NAME,
3258 ARRIVE_AFTER_TRIP_ID,
3259 --FND_API.G_MISS_CHAR, -- ARRIVE_AFTER_TRIP_NAME
3260 VEHICLE_ITEM_ID,
3261 --FND_API.G_MISS_CHAR, -- VEHICLE_ITEM_DESC
3262 VEHICLE_ORGANIZATION_ID,
3263 --FND_API.G_MISS_CHAR, -- VEHICLE_ORGANIZATION_CODE
3264 VEHICLE_NUMBER,
3265 VEHICLE_NUM_PREFIX,
3266 CARRIER_ID,
3267 SHIP_METHOD_CODE,
3268 --FND_API.G_MISS_CHAR, -- SHIP_METHOD_NAME
3269 ROUTE_ID,
3270 ROUTING_INSTRUCTIONS,
3271 ATTRIBUTE_CATEGORY,
3272 ATTRIBUTE1,
3273 ATTRIBUTE2,
3274 ATTRIBUTE3,
3275 ATTRIBUTE4,
3276 ATTRIBUTE5,
3277 ATTRIBUTE6,
3278 ATTRIBUTE7,
3279 ATTRIBUTE8,
3280 ATTRIBUTE9,
3281 ATTRIBUTE10,
3282 ATTRIBUTE11,
3283 ATTRIBUTE12,
3284 ATTRIBUTE13,
3285 ATTRIBUTE14,
3286 ATTRIBUTE15,
3287 SERVICE_LEVEL,
3288 MODE_OF_TRANSPORT,
3289 CONSOLIDATION_ALLOWED, --H integration
3290 PLANNED_FLAG,
3291 STATUS_CODE,
3292 FREIGHT_TERMS_CODE,
3293 LOAD_TENDER_STATUS,
3294 ROUTE_LANE_ID,
3295 LANE_ID,
3296 SCHEDULE_ID,
3297 BOOKING_NUMBER,
3298 CREATION_DATE,
3299 CREATED_BY,
3300 LAST_UPDATE_DATE,
3301 LAST_UPDATED_BY,
3302 LAST_UPDATE_LOGIN,
3303 PROGRAM_APPLICATION_ID,
3304 PROGRAM_ID,
3305 PROGRAM_UPDATE_DATE,
3306 REQUEST_ID,
3307 nvl(SHIPMENTS_TYPE_FLAG, 'O'), -- J inbound logistics jckwok
3308 OPERATOR
3309 FROM wsh_trips
3310 WHERE trip_id = v_trip_id;
3311
3312 l_stops_trip_rec WSH_TRIPS_PVT.trip_rec_type;
3313
3314 --
3315 l_debug_on BOOLEAN;
3316 --
3317 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_TRIP_DETAILS_PVT';
3318 --
3319 BEGIN
3320 --
3321 --
3322 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3323 --
3324 IF l_debug_on IS NULL
3325 THEN
3326 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3327 END IF;
3328 --
3329 IF l_debug_on THEN
3330 WSH_DEBUG_SV.push(l_module_name);
3331 --
3332 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
3333 END IF;
3334 --
3335 OPEN c_old_trip_details(p_trip_id);
3336 FETCH c_old_trip_details
3337 INTO
3338 l_stops_trip_rec.TRIP_ID,
3339 l_stops_trip_rec.NAME,
3340 l_stops_trip_rec.ARRIVE_AFTER_TRIP_ID,
3341 --l_stops_trip_rec.ARRIVE_AFTER_TRIP_NAME,
3342 l_stops_trip_rec.VEHICLE_ITEM_ID,
3343 --l_stops_trip_rec.VEHICLE_ITEM_DESC,
3344 l_stops_trip_rec.VEHICLE_ORGANIZATION_ID,
3345 --l_stops_trip_rec.VEHICLE_ORGANIZATION_CODE,
3346 l_stops_trip_rec.VEHICLE_NUMBER,
3347 l_stops_trip_rec.VEHICLE_NUM_PREFIX,
3348 l_stops_trip_rec.CARRIER_ID,
3349 l_stops_trip_rec.SHIP_METHOD_CODE,
3350 --l_stops_trip_rec.SHIP_METHOD_NAME,
3351 l_stops_trip_rec.ROUTE_ID,
3352 l_stops_trip_rec.ROUTING_INSTRUCTIONS,
3353 l_stops_trip_rec.ATTRIBUTE_CATEGORY,
3354 l_stops_trip_rec.ATTRIBUTE1,
3355 l_stops_trip_rec.ATTRIBUTE2,
3356 l_stops_trip_rec.ATTRIBUTE3,
3357 l_stops_trip_rec.ATTRIBUTE4,
3358 l_stops_trip_rec.ATTRIBUTE5,
3359 l_stops_trip_rec.ATTRIBUTE6,
3360 l_stops_trip_rec.ATTRIBUTE7,
3361 l_stops_trip_rec.ATTRIBUTE8,
3362 l_stops_trip_rec.ATTRIBUTE9,
3363 l_stops_trip_rec.ATTRIBUTE10,
3364 l_stops_trip_rec.ATTRIBUTE11,
3365 l_stops_trip_rec.ATTRIBUTE12,
3366 l_stops_trip_rec.ATTRIBUTE13,
3367 l_stops_trip_rec.ATTRIBUTE14,
3368 l_stops_trip_rec.ATTRIBUTE15,
3369 l_stops_trip_rec.SERVICE_LEVEL,
3370 l_stops_trip_rec.MODE_OF_TRANSPORT,
3371 l_stops_trip_rec.CONSOLIDATION_ALLOWED, --H integration
3372 l_stops_trip_rec.PLANNED_FLAG,
3373 l_stops_trip_rec.STATUS_CODE,
3374 l_stops_trip_rec.FREIGHT_TERMS_CODE,
3375 l_stops_trip_rec.LOAD_TENDER_STATUS,
3376 l_stops_trip_rec.ROUTE_LANE_ID,
3377 l_stops_trip_rec.LANE_ID,
3378 l_stops_trip_rec.SCHEDULE_ID,
3379 l_stops_trip_rec.BOOKING_NUMBER,
3380 l_stops_trip_rec.CREATION_DATE,
3381 l_stops_trip_rec.CREATED_BY,
3382 l_stops_trip_rec.LAST_UPDATE_DATE,
3383 l_stops_trip_rec.LAST_UPDATED_BY,
3384 l_stops_trip_rec.LAST_UPDATE_LOGIN,
3385 l_stops_trip_rec.PROGRAM_APPLICATION_ID,
3386 l_stops_trip_rec.PROGRAM_ID,
3387 l_stops_trip_rec.PROGRAM_UPDATE_DATE,
3388 l_stops_trip_rec.REQUEST_ID,
3389 l_stops_trip_rec.SHIPMENTS_TYPE_FLAG, -- J Inbound Logistics jckwok
3390 l_stops_trip_rec.OPERATOR;
3391 CLOSE c_old_trip_details;
3392
3393 x_trip_rec := l_stops_trip_rec;
3394 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3395 --
3396 IF l_debug_on THEN
3397 WSH_DEBUG_SV.log(l_module_name,'l_stops_trip_rec.TRIP_ID',l_stops_trip_rec.TRIP_ID);
3398 WSH_DEBUG_SV.log(l_module_name,'l_stops_trip_rec.NAME',l_stops_trip_rec.NAME);
3399 WSH_DEBUG_SV.pop(l_module_name);
3400 END IF;
3401 --
3402 EXCEPTION
3403 WHEN OTHERS THEN
3404 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_TRIPS_GRP.get_trip_details_pvt',l_module_name);
3405 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3406 --
3407 IF l_debug_on THEN
3408 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3409 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3410 END IF;
3411 --
3412 END;
3413
3414 END WSH_TRIPS_GRP;