[Home] [Help]
PACKAGE BODY: APPS.WSH_TRIP_STOPS_PVT
Source
1 PACKAGE BODY WSH_TRIP_STOPS_PVT AS
2 /* $Header: WSHSTTHB.pls 120.3.12010000.2 2008/08/21 06:04:26 sankarun ship $ */
3
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_TRIP_STOPS_PVT';
6 --
7 PROCEDURE Create_Trip_Stop(
8 p_trip_stop_info IN trip_stop_rec_type,
9 x_rowid OUT NOCOPY VARCHAR2,
10 x_stop_id OUT NOCOPY NUMBER,
11 x_return_status OUT NOCOPY VARCHAR2
12 )
13 IS
14
15 CURSOR get_next_stop IS
16 SELECT wsh_trip_stops_s.nextval
17 FROM sys.dual;
18
19 CURSOR check_stop IS
20 SELECT rowid
21 FROM wsh_trip_stops
22 WHERE stop_id = x_stop_id;
23
24 l_row_count NUMBER;
25 l_temp_id NUMBER;
26 l_physical_loc_id NUMBER;
27 l_return_status VARCHAR2(1);
28 l_trips wsh_util_core.id_tab_type;
29 l_success_trip_ids wsh_util_core.id_tab_type;
30 others EXCEPTION;
31 get_physical_loc_err EXCEPTION;
32
33 cursor l_trip_shipments_type_csr(p_trip_id IN NUMBER) is
34 select decode(shipments_type_flag, 'M', 'O', shipments_type_flag)
35 from wsh_trips
36 where trip_id = p_trip_id;
37
38 l_shipments_type_flag VARCHAR2(100);
39
40 l_stop_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
41 l_dbi_rs VARCHAR2(1); -- DBI Project
42
43 l_wf_rs VARCHAR2(1); -- Workflow Project
44 --
45 l_debug_on BOOLEAN;
46 --
47 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_TRIP_STOP';
48 --
49 BEGIN
50 --
51 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
52 --
53 IF l_debug_on IS NULL
54 THEN
55 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
56 END IF;
57 --
58 IF l_debug_on THEN
59 WSH_DEBUG_SV.push(l_module_name);
60 WSH_DEBUG_SV.log(l_module_name,'STOP_ID',p_trip_stop_info.STOP_ID);
61 WSH_DEBUG_SV.log(l_module_name,'TRIP_ID',p_trip_stop_info.TRIP_ID);
62 WSH_DEBUG_SV.log(l_module_name,'STOP_LOCATION_ID',p_trip_stop_info.STOP_LOCATION_ID);
63 WSH_DEBUG_SV.log(l_module_name,'STATUS_CODE',p_trip_stop_info.STATUS_CODE);
64 WSH_DEBUG_SV.log(l_module_name,'STOP_SEQUENCE_NUMBER',p_trip_stop_info.STOP_SEQUENCE_NUMBER);
65 WSH_DEBUG_SV.log(l_module_name,'PLANNED_ARRIVAL_DATE',p_trip_stop_info.PLANNED_ARRIVAL_DATE);
66 WSH_DEBUG_SV.log(l_module_name,'PLANNED_DEPARTURE_DATE',p_trip_stop_info.PLANNED_DEPARTURE_DATE);
67 WSH_DEBUG_SV.log(l_module_name,'ACTUAL_ARRIVAL_DATE',p_trip_stop_info.ACTUAL_ARRIVAL_DATE);
68 WSH_DEBUG_SV.log(l_module_name,'ACTUAL_DEPARTURE_DATE',p_trip_stop_info.ACTUAL_DEPARTURE_DATE);
69 WSH_DEBUG_SV.log(l_module_name,'DEPARTURE_GROSS_WEIGHT',p_trip_stop_info.DEPARTURE_GROSS_WEIGHT);
70 WSH_DEBUG_SV.log(l_module_name,'DEPARTURE_NET_WEIGHT',p_trip_stop_info.DEPARTURE_NET_WEIGHT);
71 WSH_DEBUG_SV.log(l_module_name,'WEIGHT_UOM_CODE',p_trip_stop_info.WEIGHT_UOM_CODE);
72 WSH_DEBUG_SV.log(l_module_name,'DEPARTURE_VOLUME',p_trip_stop_info.DEPARTURE_VOLUME);
73 WSH_DEBUG_SV.log(l_module_name,'VOLUME_UOM_CODE',p_trip_stop_info.VOLUME_UOM_CODE);
74 WSH_DEBUG_SV.log(l_module_name,'DEPARTURE_SEAL_CODE',p_trip_stop_info.DEPARTURE_SEAL_CODE);
75 WSH_DEBUG_SV.log(l_module_name,'DEPARTURE_FILL_PERCENT',p_trip_stop_info.DEPARTURE_FILL_PERCENT);
76 WSH_DEBUG_SV.log(l_module_name,'WSH_LOCATION_ID',p_trip_stop_info.WSH_LOCATION_ID);
77 WSH_DEBUG_SV.log(l_module_name,'TRACKING_DRILLDOWN_FLAG',p_trip_stop_info.TRACKING_DRILLDOWN_FLAG);
78 WSH_DEBUG_SV.log(l_module_name,'TRACKING_REMARKS',p_trip_stop_info.TRACKING_REMARKS);
79 WSH_DEBUG_SV.log(l_module_name,'CARRIER_EST_DEPARTURE_DATE',p_trip_stop_info.CARRIER_EST_DEPARTURE_DATE);
80 WSH_DEBUG_SV.log(l_module_name,'CARRIER_EST_ARRIVAL_DATE',p_trip_stop_info.CARRIER_EST_ARRIVAL_DATE);
81 WSH_DEBUG_SV.log(l_module_name,'LOADING_START_DATETIME',p_trip_stop_info.LOADING_START_DATETIME);
82 WSH_DEBUG_SV.log(l_module_name,'LOADING_END_DATETIME',p_trip_stop_info.LOADING_END_DATETIME);
83 WSH_DEBUG_SV.log(l_module_name,'UNLOADING_START_DATETIME',p_trip_stop_info.UNLOADING_START_DATETIME);
84 WSH_DEBUG_SV.log(l_module_name,'UNLOADING_END_DATETIME',p_trip_stop_info.UNLOADING_END_DATETIME);
85 WSH_DEBUG_SV.log(l_module_name,'SHIPMENTS_TYPE_FLAG',p_trip_stop_info.SHIPMENTS_TYPE_FLAG);
86 END IF;
87 --
88 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
89 l_trips.DELETE;
90 x_stop_id := p_trip_stop_info.stop_id;
91
92 l_shipments_type_flag := p_trip_stop_info.shipments_type_flag;
93
94 IF (l_shipments_type_flag is null) THEN
95 open l_trip_shipments_type_csr(p_trip_stop_info.trip_id);
96 fetch l_trip_shipments_type_csr into l_shipments_type_flag;
97 close l_trip_shipments_type_csr;
98 END IF;
99
100
101 IF (x_stop_id IS NULL) THEN
102
103 LOOP
104
105 OPEN get_next_stop;
106 FETCH get_next_stop INTO x_stop_id;
107 CLOSE get_next_stop;
108
109 IF (x_stop_id IS NOT NULL) THEN
110 x_rowid := NULL;
111
112 OPEN check_stop;
113 FETCH check_stop INTO x_rowid;
114 CLOSE check_stop;
115
116 IF ( x_rowid IS NULL ) THEN
117 EXIT;
118 END IF;
119 ELSE
120 EXIT;
121 END IF;
122
123 END LOOP;
124
125 END IF;
126
127
128 WSH_LOCATIONS_PKG.Convert_internal_cust_location(
129 p_internal_cust_location_id => p_trip_stop_info.stop_location_id,
130 x_internal_org_location_id => l_physical_loc_id,
131 x_return_status => l_return_status );
132
133 IF l_return_status in ( WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
134 raise get_physical_loc_err;
135 END IF;
136
137 IF l_debug_on THEN
138 WSH_DEBUG_SV.log(l_module_name,'l_physical_loc_id',l_physical_loc_id);
139 END IF;
140
141 INSERT INTO wsh_trip_stops(
142 stop_id,
143 trip_id,
144 stop_location_id,
145 status_code,
146 stop_sequence_number,
147 planned_arrival_date,
148 planned_departure_date,
149 actual_arrival_date,
150 actual_departure_date,
151 departure_gross_weight,
152 departure_net_weight,
153 weight_uom_code,
154 departure_volume,
155 volume_uom_code,
156 departure_seal_code,
157 departure_fill_percent,
158 tp_attribute_category,
159 tp_attribute1,
160 tp_attribute2,
161 tp_attribute3,
162 tp_attribute4,
163 tp_attribute5,
164 tp_attribute6,
165 tp_attribute7,
166 tp_attribute8,
167 tp_attribute9,
168 tp_attribute10,
169 tp_attribute11,
170 tp_attribute12,
171 tp_attribute13,
172 tp_attribute14,
173 tp_attribute15,
174 attribute_category,
175 attribute1,
176 attribute2,
177 attribute3,
178 attribute4,
179 attribute5,
180 attribute6,
181 attribute7,
182 attribute8,
183 attribute9,
184 attribute10,
185 attribute11,
186 attribute12,
187 attribute13,
188 attribute14,
189 attribute15,
190 creation_date,
191 created_by,
192 last_update_date,
193 last_updated_by,
194 last_update_login,
195 program_application_id,
196 program_id,
197 program_update_date,
198 request_id,
199 wsh_location_id,
200 tracking_drilldown_flag,
201 tracking_remarks,
202 carrier_est_departure_date,
203 carrier_est_arrival_date,
204 loading_start_datetime,
205 loading_end_datetime,
206 unloading_start_datetime,
207 unloading_end_datetime,
208 shipments_type_flag,
209 -- J: W/V Changes
210 wv_frozen_flag,
211 wkend_layover_stops,
212 wkday_layover_stops,
213 tp_stop_id,
214 physical_stop_id,
215 physical_location_id,
216 tms_interface_flag -- OTM R12, glog proj
217 ) values(
218
219 x_stop_id,
220 p_trip_stop_info.trip_id,
221 p_trip_stop_info.stop_location_id,
222 nvl(p_trip_stop_info.status_code,'OP'),
223 nvl(p_trip_stop_info.stop_sequence_number,0),
224 nvl(p_trip_stop_info.planned_arrival_date,SYSDATE),
225 nvl(p_trip_stop_info.planned_departure_date,SYSDATE),
226 p_trip_stop_info.actual_arrival_date,
227 p_trip_stop_info.actual_departure_date,
228 p_trip_stop_info.departure_gross_weight,
229 p_trip_stop_info.departure_net_weight,
230 p_trip_stop_info.weight_uom_code,
231 p_trip_stop_info.departure_volume,
232 p_trip_stop_info.volume_uom_code,
233 p_trip_stop_info.departure_seal_code,
234 p_trip_stop_info.departure_fill_percent,
235 p_trip_stop_info.tp_attribute_category,
236 p_trip_stop_info.tp_attribute1,
237 p_trip_stop_info.tp_attribute2,
238 p_trip_stop_info.tp_attribute3,
239 p_trip_stop_info.tp_attribute4,
240 p_trip_stop_info.tp_attribute5,
241 p_trip_stop_info.tp_attribute6,
242 p_trip_stop_info.tp_attribute7,
243 p_trip_stop_info.tp_attribute8,
244 p_trip_stop_info.tp_attribute9,
245 p_trip_stop_info.tp_attribute10,
246 p_trip_stop_info.tp_attribute11,
247 p_trip_stop_info.tp_attribute12,
248 p_trip_stop_info.tp_attribute13,
249 p_trip_stop_info.tp_attribute14,
250 p_trip_stop_info.tp_attribute15,
251 p_trip_stop_info.attribute_category,
252 p_trip_stop_info.attribute1,
253 p_trip_stop_info.attribute2,
254 p_trip_stop_info.attribute3,
255 p_trip_stop_info.attribute4,
256 p_trip_stop_info.attribute5,
257 p_trip_stop_info.attribute6,
258 p_trip_stop_info.attribute7,
259 p_trip_stop_info.attribute8,
260 p_trip_stop_info.attribute9,
261 p_trip_stop_info.attribute10,
262 p_trip_stop_info.attribute11,
263 p_trip_stop_info.attribute12,
264 p_trip_stop_info.attribute13,
265 p_trip_stop_info.attribute14,
266 p_trip_stop_info.attribute15,
267 nvl(p_trip_stop_info.creation_date, SYSDATE),
268 nvl(p_trip_stop_info.created_by, FND_GLOBAL.USER_ID),
269 nvl(p_trip_stop_info.last_update_date, SYSDATE),
270 nvl(p_trip_stop_info.last_updated_by, FND_GLOBAL.USER_ID),
271 nvl(p_trip_stop_info.last_update_login, FND_GLOBAL.LOGIN_ID),
272 p_trip_stop_info.program_application_id,
273 p_trip_stop_info.program_id,
274 p_trip_stop_info.program_update_date,
275 p_trip_stop_info.request_id,
276 p_trip_stop_info.wsh_location_id,
277 p_trip_stop_info.tracking_drilldown_flag,
278 p_trip_stop_info.tracking_remarks,
279 p_trip_stop_info.carrier_est_departure_date,
280 p_trip_stop_info.carrier_est_arrival_date,
281 p_trip_stop_info.loading_start_datetime,
282 p_trip_stop_info.loading_end_datetime,
283 p_trip_stop_info.unloading_start_datetime,
284 p_trip_stop_info.unloading_end_datetime,
285 nvl(l_shipments_type_flag, 'O'),
286 -- J: W/V Changes
287 nvl(p_trip_stop_info.wv_frozen_flag, 'N'),
288 p_trip_stop_info.wkend_layover_stops,
289 p_trip_stop_info.wkday_layover_stops,
290 p_trip_stop_info.tp_stop_id,
291 p_trip_stop_info.physical_stop_id,
292 nvl(p_trip_stop_info.physical_location_id, l_physical_loc_id),
293 NULL --OTM R12, glog proj , create stops with null value for tms_interface_flag
294 );
295
296 --
297 -- Workflow Project
298 -- Raise Trip Stop Creation business event
299 IF l_debug_on THEN
300 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.RAISE_EVENT',WSH_DEBUG_SV.C_PROC_LEVEL);
301 END IF;
302
303 WSH_WF_STD.RAISE_EVENT( p_entity_type => 'STOP',
304 p_entity_id => x_stop_id,
305 p_event => 'oracle.apps.wsh.stop.gen.create',
306 x_return_status => l_wf_rs
307 );
308
309 IF l_debug_on THEN
310 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_WF_STD.RAISE_EVENT => ',l_wf_rs);
311 END IF;
312 -- End of code for Workflow project
313
314 -- DBI Project
315 -- Insert into WSH_TRIP_STOPS.
316 -- Call DBI API after the INSERT.
317 -- This API will also check for DBI Installed or not
318 IF l_debug_on THEN
319 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Stop id-',x_stop_id);
320 END IF;
321 l_stop_tab(1) := x_stop_id;
322 WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
323 (p_stop_id_tab => l_stop_tab,
324 p_dml_type => 'INSERT',
325 x_return_status => l_dbi_rs);
326
327 IF l_debug_on THEN
328 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
329 END IF;
330 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
331 x_return_status := l_dbi_rs;
332 -- just pass this return status to caller API
333 IF l_debug_on THEN
334 WSH_DEBUG_SV.log(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
335 WSH_DEBUG_SV.pop(l_module_name);
336 END IF;
337 return;
338 END IF;
339 -- End of Code for DBI Project
340 --
341
342
343 OPEN check_stop;
344 FETCH check_stop INTO x_rowid;
345
346 IF (check_stop%NOTFOUND) THEN
347 CLOSE check_stop;
348 RAISE others;
349 END IF;
350
351 CLOSE check_stop;
352
353
354 --
355 -- Debug Statements
356 --
357 IF l_debug_on THEN
358 WSH_DEBUG_SV.pop(l_module_name);
359 END IF;
360 --
361 EXCEPTION
362
363 WHEN get_physical_loc_err THEN
364 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
365 fnd_message.set_name('WSH', 'WSH_LOCATION_CONVERT_ERR');
366 fnd_message.set_token('LOCATION_NAME',
367 SUBSTRB(WSH_UTIL_CORE.get_location_description(p_trip_stop_info.stop_location_id,'NEW UI CODE'), 1, 60));
368 wsh_util_core.add_message(x_return_status,l_module_name);
369
370 WHEN others THEN
371 wsh_util_core.default_handler('WSH_TRIP_STOPS_PVT.CREATE_TRIP_STOP',l_module_name);
372 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
373
374 --
375 -- Debug Statements
376 --
377 IF l_debug_on THEN
378 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
379 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
380 END IF;
381 --
382 END Create_Trip_Stop;
383
384
385 PROCEDURE Delete_Trip_Stop(
386 p_rowid IN VARCHAR2,
387 p_stop_id IN NUMBER,
388 x_return_status OUT NOCOPY VARCHAR2,
389 p_validate_flag IN VARCHAR2 DEFAULT 'Y',
390 --tkt
391 p_caller IN VARCHAR2
392 --tkt
393 ) IS
394
395 CURSOR get_stop_id_rowid (v_rowid VARCHAR2) IS
396 SELECT stop_id, trip_id
397 FROM wsh_trip_stops
398 WHERE rowid = v_rowid;
399
400 CURSOR get_trip_id (v_stop_id NUMBER) IS
401 SELECT trip_id
402 FROM wsh_trip_stops
403 WHERE stop_id = v_stop_id;
404
405 CURSOR get_del_leg_id (cp_stop_id NUMBER) IS
406 SELECT delivery_leg_id
407 FROM wsh_delivery_legs
408 WHERE pick_up_stop_id = cp_stop_id OR
409 drop_off_stop_id = cp_stop_id;
410
411 --OTM R12, glog proj
412 CURSOR c_get_trip_status (p_trip_id IN NUMBER) IS
413 SELECT NVL(ignore_for_planning, 'N'),
414 tp_plan_name
415 FROM WSH_TRIPS
416 WHERE trip_id = p_trip_id;
417 --
418
419
420 l_stop_id NUMBER;
421 l_trip_id NUMBER;
422 l_trip_id_tab wsh_util_core.id_tab_type;
423 others EXCEPTION;
424
425 l_return_status VARCHAR2(1);
426 l_warn_num NUMBER := 0;
427
428 l_stop_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
429 l_dbi_rs VARCHAR2(1); -- DBI Project
430
431 --OTM R12, glog proj
432 l_ignore WSH_TRIPS.IGNORE_FOR_PLANNING%TYPE;
433 l_tp_plan_name WSH_TRIPS.TP_PLAN_NAME%TYPE;
434 e_gc3_trip EXCEPTION;
435 l_gc3_is_installed VARCHAR2(1);
436 --
437
438 --
439 l_debug_on BOOLEAN;
440 --
441 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_TRIP_STOP';
442 --
443 BEGIN
444 --
445 -- Debug Statements
446 --
447 --
448 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
449 --
450 IF l_debug_on IS NULL
451 THEN
452 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
453 END IF;
454 --
455 IF l_debug_on THEN
456 WSH_DEBUG_SV.push(l_module_name);
457 --
458 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
459 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
460 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATE_FLAG',P_VALIDATE_FLAG);
461 WSH_DEBUG_SV.log(l_module_name,'P_CALLER',P_CALLER);
462 END IF;
463 --
464 l_stop_id := p_stop_id;
465
466 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
467
468 --OTM R12, glog proj, use Global Variable
469 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
470
471 -- If null, call the function
472 IF l_gc3_is_installed IS NULL THEN
473 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
474 END IF;
475 -- end of OTM R12, glog proj
476
477
478
479 IF p_rowid IS NOT NULL THEN
480 OPEN get_stop_id_rowid(p_rowid);
481 FETCH get_stop_id_rowid INTO l_stop_id,l_trip_id;
482 CLOSE get_stop_id_rowid;
483 ELSIF p_stop_id IS NOT NULL THEN
484 OPEN get_trip_id(p_stop_id);
485 FETCH get_trip_id INTO l_trip_id;
486 CLOSE get_trip_id;
487 END IF;
488
489
490 -- OTM R12, glog proj
491 -- Only allow stops to be deleted from the Gc3 Inbound Message
492 -- the caller for Inbound Message=FTE_TMS_INTEGRATION
493 -- Not allowed from Form or UI
494 IF l_gc3_is_installed = 'Y' AND nvl(p_caller,'@@@') <> 'FTE_TMS_INTEGRATION' THEN
495 l_ignore := 'N';
496 l_tp_plan_name := NULL;
497
498 OPEN c_get_trip_status(l_trip_id);
499 FETCH c_get_trip_status INTO l_ignore, l_tp_plan_name;
500 IF c_get_trip_status%NOTFOUND THEN
501 CLOSE c_get_trip_status;
502 RAISE no_data_found;
503 END IF;
504 CLOSE c_get_trip_status;
505 IF l_debug_on THEN
506 WSH_DEBUG_SV.logmsg(l_module_name,'Ignore:'||l_ignore||' Tp Plan:'||l_tp_plan_name);
507 END IF;
508 IF (l_ignore = 'N' AND l_tp_plan_name IS NOT NULL) THEN
509 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
510 RAISE e_gc3_trip;
511 END IF;
512 END IF;
513 --
514
515
516 IF (p_validate_flag = 'Y') THEN
517 --
518 -- Debug Statements
519 --
520 IF l_debug_on THEN
521 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_VALIDATIONS.CHECK_STOP_DELETE',WSH_DEBUG_SV.C_PROC_LEVEL);
522 END IF;
523 --
524 WSH_TRIP_VALIDATIONS.check_stop_delete(
525 p_stop_id => l_stop_id,
526 x_return_status => l_return_status,
527 p_caller => p_caller);
528 IF l_debug_on THEN
529 WSH_DEBUG_SV.log(l_module_name,'CHECK_STOP_DELETE x_return_status',x_return_status);
530 END IF;
531
532 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
533 x_return_status := l_return_status;
534 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_DELETE_ERROR');
535 --
536 -- Debug Statements
537 --
538 IF l_debug_on THEN
539 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
540 END IF;
541 --
542 FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(l_stop_id, p_caller));
543 wsh_util_core.add_message(x_return_status,l_module_name);
544 --
545 -- Debug Statements
546 --
547 IF l_debug_on THEN
548 WSH_DEBUG_SV.pop(l_module_name);
549 END IF;
550 --
551 RETURN;
552 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
553 l_warn_num := l_warn_num + 1;
554
555 END IF;
556 END IF;
557
558
559 IF l_stop_id IS NOT NULL THEN
560 DELETE FROM wsh_freight_costs
561 WHERE stop_id = l_stop_id;
562
563
564 FOR rec IN get_del_leg_id(l_stop_id) LOOP
565
566 IF l_debug_on THEN
567 WSH_DEBUG_SV.log(l_module_name,'Calling Delete_Delivery_Leg delivery_leg_id',rec.delivery_leg_id);
568 END IF;
569
570 WSH_DELIVERY_LEGS_PVT.Delete_Delivery_Leg (
571 p_delivery_leg_id => rec.delivery_leg_id,
572 x_return_status => l_return_status);
573
574 IF l_debug_on THEN
575 WSH_DEBUG_SV.log(l_module_name,'After Calling Delete_Delivery_Leg x_return_status',x_return_status);
576 END IF;
577
578 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
579 IF l_debug_on THEN
580 WSH_DEBUG_SV.pop(l_module_name);
581 END IF;
582 x_return_status := l_return_status;
583 RETURN;
584 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
585 l_warn_num := l_warn_num + 1;
586 END IF;
587
588 END LOOP;
589
590
591 DELETE FROM wsh_trip_stops
592 WHERE stop_id = l_stop_id;
593
594 --
595 -- DBI Project
596 -- DELETE from WSH_TRIP_STOPS.
597 -- Call DBI API after the DELETE.
598 -- This API will also check for DBI Installed or not
599 IF l_debug_on THEN
600 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Stop id-',l_stop_id);
601 END IF;
602 l_stop_tab(1) := l_stop_id;
603 WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
604 (p_stop_id_tab => l_stop_tab,
605 p_dml_type => 'DELETE',
606 x_return_status => l_dbi_rs);
607
608 IF l_debug_on THEN
609 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
610 END IF;
611 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
612 x_return_status := l_dbi_rs;
613 -- just pass this return status to caller API
614 IF l_debug_on THEN
615 WSH_DEBUG_SV.log(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
616 WSH_DEBUG_SV.pop(l_module_name);
617 END IF;
618 return;
619 END IF;
620 -- End of Code for DBI Project
621 --
622
623
624 IF l_trip_id IS NOT NULL THEN
625
626 -- Need to compute all stops weight/volumes
627 IF l_debug_on THEN
628 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_ACTIONS.trip_weight_volume',WSH_DEBUG_SV.C_PROC_LEVEL);
629 END IF;
630
631 l_trip_id_tab(1) := l_trip_id;
632 WSH_TRIPS_ACTIONS.trip_weight_volume(
633 p_trip_rows => l_trip_id_tab,
634 p_override_flag => 'Y',
635 p_calc_wv_if_frozen => 'N',
636 p_start_departure_date => to_date(NULL),
637 p_calc_del_wv => 'N',
638 x_return_status => x_return_status,
639 p_suppress_errors => 'Y');
640
641 IF x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
642 IF l_debug_on THEN
643 WSH_DEBUG_SV.logmsg(l_module_name,'Error calculating trip wt/vol');
644 END IF;
645 END IF;
646
647 END IF;
648
649 ELSE
650 raise others;
651 END IF;
652
653 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS and l_warn_num > 0 THEN
654 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
655 END IF;
656
657 --
658 -- Debug Statements
659 --
660 IF l_debug_on THEN
661 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
662 WSH_DEBUG_SV.pop(l_module_name);
663 END IF;
664 --
665 EXCEPTION
666 --OTM R12, glog proj
667 WHEN no_data_found THEN
668 IF c_get_trip_status%ISOPEN THEN
669 CLOSE c_get_trip_status;
670 END IF;
671 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NOT_FOUND');
672 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
673 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
674 --
675 -- Debug Statements
676 --
677 IF l_debug_on THEN
678 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
679 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
680 END IF;
681 --
682
683 --OTM R12, glog proj
684 WHEN e_gc3_trip THEN
685 IF c_get_trip_status%ISOPEN THEN
686 CLOSE c_get_trip_status;
687 END IF;
688 FND_MESSAGE.SET_NAME('WSH','WSH_OTM_TRIP_STOP_DEL_ERROR');
689 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
690 wsh_util_core.add_message(x_return_status);
691 --
692 -- Debug Statements
693 --
694 IF l_debug_on THEN
695 WSH_DEBUG_SV.logmsg(l_module_name,'E_GC3_TRIP exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
696 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:E_GC3_TRIP');
697 END IF;
698 --
699
700 WHEN others THEN
701 IF c_get_trip_status%ISOPEN THEN
702 CLOSE c_get_trip_status;
703 END IF;
704 wsh_util_core.default_handler('WSH_TRIP_STOPS_PVT.DELETE_TRIP_STOP',l_module_name);
705 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
706
707 --
708 -- Debug Statements
709 --
710 IF l_debug_on THEN
711 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
712 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
713 END IF;
714 --
715 END Delete_Trip_Stop;
716
717 procedure Update_Trip_Stop(
718 p_rowid IN VARCHAR2,
719 p_stop_info IN trip_stop_rec_type,
720 x_return_status OUT NOCOPY VARCHAR2) IS
721
722 -- J: W/V Changes
723 CURSOR get_stop_info IS
724 SELECT rowid,
725 departure_gross_weight,
726 departure_net_weight,
727 departure_volume,
728 weight_uom_code,
729 volume_uom_code,
730 NVL(wv_frozen_flag,'Y')
731 FROM wsh_trip_stops
732 WHERE stop_id = p_stop_info.stop_id;
733
734 -- J: W/V Changes
735 l_gross_wt NUMBER;
736 l_net_wt NUMBER;
737 l_volume NUMBER;
738 l_weight_uom_code VARCHAR2(3);
739 l_volume_uom_code VARCHAR2(3);
740 l_frozen_flag VARCHAR2(1);
741 l_return_status VARCHAR2(1);
742
743 l_rowid VARCHAR2(30);
744
745 l_stop_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
746 l_dbi_rs VARCHAR2(1); -- DBI Project
747
748 --
749 l_debug_on BOOLEAN;
750 --
751 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_TRIP_STOP';
752 --
753 BEGIN
754 --
755 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
756 --
757 IF l_debug_on IS NULL
758 THEN
759 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
760 END IF;
761 --
762 IF l_debug_on THEN
763 WSH_DEBUG_SV.push(l_module_name);
764 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
765 WSH_DEBUG_SV.log(l_module_name,'STOP_ID',p_stop_info.STOP_ID);
766 WSH_DEBUG_SV.log(l_module_name,'TRIP_ID',p_stop_info.TRIP_ID);
767 WSH_DEBUG_SV.log(l_module_name,'STOP_LOCATION_ID',p_stop_info.STOP_LOCATION_ID);
768 WSH_DEBUG_SV.log(l_module_name,'STATUS_CODE',p_stop_info.STATUS_CODE);
769 WSH_DEBUG_SV.log(l_module_name,'STOP_SEQUENCE_NUMBER',p_stop_info.STOP_SEQUENCE_NUMBER);
770 WSH_DEBUG_SV.log(l_module_name,'PLANNED_ARRIVAL_DATE',p_stop_info.PLANNED_ARRIVAL_DATE);
771 WSH_DEBUG_SV.log(l_module_name,'PLANNED_DEPARTURE_DATE',p_stop_info.PLANNED_DEPARTURE_DATE);
772 WSH_DEBUG_SV.log(l_module_name,'ACTUAL_ARRIVAL_DATE',p_stop_info.ACTUAL_ARRIVAL_DATE);
773 WSH_DEBUG_SV.log(l_module_name,'ACTUAL_DEPARTURE_DATE',p_stop_info.ACTUAL_DEPARTURE_DATE);
774 WSH_DEBUG_SV.log(l_module_name,'DEPARTURE_GROSS_WEIGHT',p_stop_info.DEPARTURE_GROSS_WEIGHT);
775 WSH_DEBUG_SV.log(l_module_name,'DEPARTURE_NET_WEIGHT',p_stop_info.DEPARTURE_NET_WEIGHT);
776 WSH_DEBUG_SV.log(l_module_name,'WEIGHT_UOM_CODE',p_stop_info.WEIGHT_UOM_CODE);
777 WSH_DEBUG_SV.log(l_module_name,'DEPARTURE_VOLUME',p_stop_info.DEPARTURE_VOLUME);
778 WSH_DEBUG_SV.log(l_module_name,'VOLUME_UOM_CODE',p_stop_info.VOLUME_UOM_CODE);
779 WSH_DEBUG_SV.log(l_module_name,'DEPARTURE_SEAL_CODE',p_stop_info.DEPARTURE_SEAL_CODE);
780 WSH_DEBUG_SV.log(l_module_name,'DEPARTURE_FILL_PERCENT',p_stop_info.DEPARTURE_FILL_PERCENT);
781 WSH_DEBUG_SV.log(l_module_name,'WSH_LOCATION_ID',p_stop_info.WSH_LOCATION_ID);
782 WSH_DEBUG_SV.log(l_module_name,'TRACKING_DRILLDOWN_FLAG',p_stop_info.TRACKING_DRILLDOWN_FLAG);
783 WSH_DEBUG_SV.log(l_module_name,'TRACKING_REMARKS',p_stop_info.TRACKING_REMARKS);
784 WSH_DEBUG_SV.log(l_module_name,'CARRIER_EST_DEPARTURE_DATE',p_stop_info.CARRIER_EST_DEPARTURE_DATE);
785 WSH_DEBUG_SV.log(l_module_name,'CARRIER_EST_ARRIVAL_DATE',p_stop_info.CARRIER_EST_ARRIVAL_DATE);
786 WSH_DEBUG_SV.log(l_module_name,'LOADING_START_DATETIME',p_stop_info.LOADING_START_DATETIME);
787 WSH_DEBUG_SV.log(l_module_name,'LOADING_END_DATETIME',p_stop_info.LOADING_END_DATETIME);
788 WSH_DEBUG_SV.log(l_module_name,'UNLOADING_START_DATETIME',p_stop_info.UNLOADING_START_DATETIME);
789 WSH_DEBUG_SV.log(l_module_name,'UNLOADING_END_DATETIME',p_stop_info.UNLOADING_END_DATETIME);
790 WSH_DEBUG_SV.log(l_module_name,'PHYSICAL_STOP_ID',p_stop_info.PHYSICAL_STOP_ID);
791 WSH_DEBUG_SV.log(l_module_name,'PHYSICAL_LOCATION_ID',p_stop_info.PHYSICAL_LOCATION_ID);
792 WSH_DEBUG_SV.log(l_module_name,'TMS_INTERFACE_FLAG',p_stop_info.TMS_INTERFACE_FLAG); --OTM R12,glog proj
793 END IF;
794 --
795
796 -- J: W/V Changes
797 OPEN get_stop_info;
798 FETCH get_stop_info INTO l_rowid, l_gross_wt, l_net_wt, l_volume, l_weight_uom_code, l_volume_uom_code, l_frozen_flag;
799 IF get_stop_info%NOTFOUND THEN
800 CLOSE get_stop_info;
801 RAISE no_data_found;
802 END IF;
803 CLOSE get_stop_info;
804 IF p_rowid IS NOT NULL THEN
805 l_rowid := p_rowid;
806 END IF;
807
808 IF l_debug_on THEN
809 WSH_DEBUG_SV.log(l_module_name,'l_rowid'||l_rowid||' Org Gross '||l_gross_wt||' Org Net '||l_net_wt||' Org Vol '||l_volume||' W Uom '||l_weight_uom_code||' V Uom '||l_volume_uom_code||' frozen '||l_frozen_flag);
810 END IF;
811
812 IF l_weight_uom_code <> p_stop_info.weight_uom_code THEN
813
814 l_gross_wt := WSH_WV_UTILS.convert_uom(
815 from_uom => l_weight_uom_code,
816 to_uom => p_stop_info.weight_uom_code,
817 quantity => l_gross_wt);
818
819 l_net_wt := WSH_WV_UTILS.convert_uom(
820 from_uom => l_weight_uom_code,
821 to_uom => p_stop_info.weight_uom_code,
822 quantity => l_net_wt);
823
824 END IF;
825
826 IF l_volume_uom_code <> p_stop_info.volume_uom_code THEN
827
828
829 l_volume := WSH_WV_UTILS.Convert_Uom(
830 from_uom => l_volume_uom_code,
831 to_uom => p_stop_info.volume_uom_code,
832 quantity => l_volume);
833
834 END IF;
835
836 -- Set wv_frozen_flag to Y if W/V info changes
837 IF (NVL(l_gross_wt,-99) <> NVL(p_stop_info.departure_gross_weight,-99)) OR
838 (NVL(l_net_wt,-99) <> NVL(p_stop_info.departure_net_weight,-99)) OR
839 (NVL(l_volume,-99) <> NVL(p_stop_info.departure_volume,-99)) THEN
840 l_frozen_flag := 'Y';
841 END IF;
842
843
844 UPDATE wsh_trip_stops SET
845 stop_id = p_stop_info.stop_id,
846 trip_id = p_stop_info.trip_id,
847 stop_location_id = p_stop_info.stop_location_id,
848 status_code = p_stop_info.status_code,
849 stop_sequence_number = p_stop_info.stop_sequence_number,
850 planned_arrival_date = p_stop_info.planned_arrival_date,
851 planned_departure_date = p_stop_info.planned_departure_date,
852 actual_arrival_date = p_stop_info.actual_arrival_date,
853 actual_departure_date = p_stop_info.actual_departure_date,
854 departure_gross_weight = p_stop_info.departure_gross_weight,
855 departure_net_weight = p_stop_info.departure_net_weight,
856 weight_uom_code = p_stop_info.weight_uom_code,
857 departure_volume = p_stop_info.departure_volume,
858 volume_uom_code = p_stop_info.volume_uom_code,
859 departure_seal_code = p_stop_info.departure_seal_code,
860 departure_fill_percent = p_stop_info.departure_fill_percent,
861 tp_attribute_category = p_stop_info.tp_attribute_category,
862 tp_attribute1 = p_stop_info.tp_attribute1,
863 tp_attribute2 = p_stop_info.tp_attribute2,
864 tp_attribute3 = p_stop_info.tp_attribute3,
865 tp_attribute4 = p_stop_info.tp_attribute4,
866 tp_attribute5 = p_stop_info.tp_attribute5,
867 tp_attribute6 = p_stop_info.tp_attribute6,
868 tp_attribute7 = p_stop_info.tp_attribute7,
869 tp_attribute8 = p_stop_info.tp_attribute8,
870 tp_attribute9 = p_stop_info.tp_attribute9,
871 tp_attribute10 = p_stop_info.tp_attribute10,
872 tp_attribute11 = p_stop_info.tp_attribute11,
873 tp_attribute12 = p_stop_info.tp_attribute12,
874 tp_attribute13 = p_stop_info.tp_attribute13,
875 tp_attribute14 = p_stop_info.tp_attribute14,
876 tp_attribute15 = p_stop_info.tp_attribute15,
877 attribute_category = p_stop_info.attribute_category,
878 attribute1 = p_stop_info.attribute1,
879 attribute2 = p_stop_info.attribute2,
880 attribute3 = p_stop_info.attribute3,
881 attribute4 = p_stop_info.attribute4,
882 attribute5 = p_stop_info.attribute5,
883 attribute6 = p_stop_info.attribute6,
884 attribute7 = p_stop_info.attribute7,
885 attribute8 = p_stop_info.attribute8,
886 attribute9 = p_stop_info.attribute9,
887 attribute10 = p_stop_info.attribute10,
888 attribute11 = p_stop_info.attribute11,
889 attribute12 = p_stop_info.attribute12,
890 attribute13 = p_stop_info.attribute13,
891 attribute14 = p_stop_info.attribute14,
892 attribute15 = p_stop_info.attribute15,
893 last_update_date = p_stop_info.last_update_date,
894 last_updated_by = p_stop_info.last_updated_by,
895 last_update_login = p_stop_info.last_update_login,
896 program_application_id = p_stop_info.program_application_id,
897 program_id = p_stop_info.program_id,
898 program_update_date = p_stop_info.program_update_date,
899 request_id = p_stop_info.request_id,
900 wsh_location_id = p_stop_info.wsh_location_id,
901 tracking_drilldown_flag = p_stop_info.tracking_drilldown_flag,
902 tracking_remarks = p_stop_info.tracking_remarks,
903 carrier_est_departure_date = p_stop_info.carrier_est_departure_date,
904 carrier_est_arrival_date = p_stop_info.carrier_est_arrival_date,
905 loading_start_datetime = p_stop_info.loading_start_datetime,
906 loading_end_datetime = p_stop_info.loading_end_datetime,
907 unloading_start_datetime = p_stop_info.unloading_start_datetime,
908 unloading_end_datetime = p_stop_info.unloading_end_datetime,
909 shipments_type_flag = nvl(p_stop_info.shipments_type_flag, 'O'),
910 -- J: W/V Changes
911 wv_frozen_flag = l_frozen_flag,
912 wkend_layover_stops = p_stop_info.wkend_layover_stops,
913 wkday_layover_stops = p_stop_info.wkday_layover_stops,
914 tp_stop_id = p_stop_info.tp_stop_id,
915 physical_stop_id = p_stop_info.physical_stop_id,
916 physical_location_id = p_stop_info.physical_location_id,
917 TMS_INTERFACE_FLAG = p_stop_info.tms_interface_flag --OTM R12,glog proj
918 WHERE rowid = l_rowid;
919
920 IF (SQL%NOTFOUND) THEN
921 raise no_data_found;
922 END IF;
923
924 --
925 -- DBI Project
926 -- Update WSH_TRIP_STOPS.
927 -- Call DBI API after the UPDATE.
928 -- This API will also check for DBI Installed or not
929 IF l_debug_on THEN
930 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Stop id-',p_stop_info.stop_id);
931 END IF;
932 l_stop_tab(1) := p_stop_info.stop_id;
933 WSH_INTEGRATION.dbi_update_trip_stop_log
934 (p_stop_id_tab => l_stop_tab,
935 p_dml_type => 'UPDATE',
936 x_return_status => l_dbi_rs);
937
938 IF l_debug_on THEN
939 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
940 END IF;
941 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
942 x_return_status := l_dbi_rs;
943 -- just pass this return status to caller API
944 IF l_debug_on THEN
945 WSH_DEBUG_SV.log(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
946 WSH_DEBUG_SV.pop(l_module_name);
947 END IF;
948 return;
949 END IF;
950 -- End of Code for DBI Project
951 --
952
953 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
954
955 --
956 -- Debug Statements
957 --
958 IF l_debug_on THEN
959 WSH_DEBUG_SV.pop(l_module_name);
960 END IF;
961 --
962 EXCEPTION
963 WHEN no_data_found THEN
964 FND_MESSAGE.Set_Name('WSH','WSH_STOP_NOT_FOUND');
965 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
966 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
967 --
968 -- Debug Statements
969 --
970 IF l_debug_on THEN
971 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
972 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
973 END IF;
974 --
975 WHEN others THEN
976 wsh_util_core.default_handler('WSH_TRIP_STOPS_PVT.UPDATE_TRIP_STOP',l_module_name);
977 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
978
979 --
980 -- Debug Statements
981 --
982 IF l_debug_on THEN
983 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
984 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
985 END IF;
986 --
987 END Update_Trip_Stop;
988
989
990 PROCEDURE Lock_Trip_Stop (
991 p_rowid IN VARCHAR2,
992 p_stop_info IN trip_stop_rec_type
993 ) IS
994
995 CURSOR lock_row IS
996 SELECT *
997 FROM wsh_trip_stops
998 WHERE rowid = p_rowid
999 FOR UPDATE OF trip_id NOWAIT;
1000
1001 Recinfo lock_row%ROWTYPE;
1002
1003 --
1004 l_debug_on BOOLEAN;
1005 --
1006 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_TRIP_STOP';
1007 --
1008 BEGIN
1009 --
1010 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1011 --
1012 IF l_debug_on IS NULL
1013 THEN
1014 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1015 END IF;
1016 --
1017 IF l_debug_on THEN
1018 WSH_DEBUG_SV.push(l_module_name);
1019 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
1020 WSH_DEBUG_SV.log(l_module_name,'STOP_ID',p_stop_info.STOP_ID);
1021 WSH_DEBUG_SV.log(l_module_name,'TRIP_ID',p_stop_info.TRIP_ID);
1022 WSH_DEBUG_SV.log(l_module_name,'STOP_LOCATION_ID',p_stop_info.STOP_LOCATION_ID);
1023 WSH_DEBUG_SV.log(l_module_name,'STATUS_CODE',p_stop_info.STATUS_CODE);
1024 WSH_DEBUG_SV.log(l_module_name,'STOP_SEQUENCE_NUMBER',p_stop_info.STOP_SEQUENCE_NUMBER);
1025 WSH_DEBUG_SV.log(l_module_name,'PLANNED_ARRIVAL_DATE',p_stop_info.PLANNED_ARRIVAL_DATE);
1026 WSH_DEBUG_SV.log(l_module_name,'PLANNED_DEPARTURE_DATE',p_stop_info.PLANNED_DEPARTURE_DATE);
1027 WSH_DEBUG_SV.log(l_module_name,'ACTUAL_ARRIVAL_DATE',p_stop_info.ACTUAL_ARRIVAL_DATE);
1028 WSH_DEBUG_SV.log(l_module_name,'ACTUAL_DEPARTURE_DATE',p_stop_info.ACTUAL_DEPARTURE_DATE);
1029 WSH_DEBUG_SV.log(l_module_name,'DEPARTURE_GROSS_WEIGHT',p_stop_info.DEPARTURE_GROSS_WEIGHT);
1030 WSH_DEBUG_SV.log(l_module_name,'DEPARTURE_NET_WEIGHT',p_stop_info.DEPARTURE_NET_WEIGHT);
1031 WSH_DEBUG_SV.log(l_module_name,'WEIGHT_UOM_CODE',p_stop_info.WEIGHT_UOM_CODE);
1032 WSH_DEBUG_SV.log(l_module_name,'DEPARTURE_VOLUME',p_stop_info.DEPARTURE_VOLUME);
1033 WSH_DEBUG_SV.log(l_module_name,'VOLUME_UOM_CODE',p_stop_info.VOLUME_UOM_CODE);
1034 WSH_DEBUG_SV.log(l_module_name,'DEPARTURE_SEAL_CODE',p_stop_info.DEPARTURE_SEAL_CODE);
1035 WSH_DEBUG_SV.log(l_module_name,'DEPARTURE_FILL_PERCENT',p_stop_info.DEPARTURE_FILL_PERCENT);
1036 WSH_DEBUG_SV.log(l_module_name,'WSH_LOCATION_ID',p_stop_info.WSH_LOCATION_ID);
1037 WSH_DEBUG_SV.log(l_module_name,'TRACKING_DRILLDOWN_FLAG',p_stop_info.TRACKING_DRILLDOWN_FLAG);
1038 WSH_DEBUG_SV.log(l_module_name,'TRACKING_REMARKS',p_stop_info.TRACKING_REMARKS);
1039 WSH_DEBUG_SV.log(l_module_name,'CARRIER_EST_DEPARTURE_DATE',p_stop_info.CARRIER_EST_DEPARTURE_DATE);
1040 WSH_DEBUG_SV.log(l_module_name,'CARRIER_EST_ARRIVAL_DATE',p_stop_info.CARRIER_EST_ARRIVAL_DATE);
1041 WSH_DEBUG_SV.log(l_module_name,'LOADING_START_DATETIME',p_stop_info.LOADING_START_DATETIME);
1042 WSH_DEBUG_SV.log(l_module_name,'LOADING_END_DATETIME',p_stop_info.LOADING_END_DATETIME);
1043 WSH_DEBUG_SV.log(l_module_name,'UNLOADING_START_DATETIME',p_stop_info.UNLOADING_START_DATETIME);
1044 WSH_DEBUG_SV.log(l_module_name,'UNLOADING_END_DATETIME',p_stop_info.UNLOADING_END_DATETIME);
1045 WSH_DEBUG_SV.log(l_module_name,'TMS_INTERFACE_FLAG',p_stop_info.TMS_INTERFACE_FLAG); --OTM R12,glog proj
1046 END IF;
1047
1048 OPEN lock_row;
1049 FETCH lock_row INTO Recinfo;
1050
1051 IF (lock_row%NOTFOUND) THEN
1052 CLOSE lock_row;
1053 FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
1054 app_exception.raise_exception;
1055 END IF;
1056
1057 CLOSE lock_row;
1058
1059 IF (
1060 (Recinfo.Stop_Id = p_stop_info.Stop_Id)
1061 AND (Recinfo.Trip_Id = p_stop_info.Trip_Id)
1062 AND (Recinfo.Stop_Location_Id = p_stop_info.Stop_Location_Id)
1063 AND (Recinfo.Status_Code = p_stop_info.Status_Code)
1064 AND (Recinfo.Stop_Sequence_Number = p_stop_info.Stop_Sequence_Number)
1065 AND ( (Recinfo.Planned_Arrival_Date = p_stop_info.Planned_Arrival_Date)
1066 OR ( (Recinfo.Planned_Arrival_Date IS NULL)
1067 AND (p_stop_info.Planned_Arrival_Date IS NULL)))
1068 AND ( (Recinfo.Planned_Departure_Date = p_stop_info.Planned_Departure_Date)
1069 OR ( (Recinfo.Planned_Departure_Date IS NULL)
1070 AND (p_stop_info.Planned_Departure_Date IS NULL)))
1071 AND ( (Recinfo.Actual_Arrival_Date = p_stop_info.Actual_Arrival_Date)
1072 OR ( (Recinfo.Actual_Arrival_Date IS NULL)
1073 AND (p_stop_info.Actual_Arrival_Date IS NULL)))
1074 AND ( (Recinfo.Actual_Departure_Date = p_stop_info.Actual_Departure_Date)
1075 OR ( (Recinfo.Actual_Departure_Date IS NULL)
1076 AND (p_stop_info.Actual_Departure_Date IS NULL)))
1077 AND ( (Recinfo.Departure_Gross_Weight = p_stop_info.Departure_Gross_Weight)
1078 OR ( (Recinfo.Departure_Gross_Weight IS NULL)
1079 AND (p_stop_info.Departure_Gross_Weight IS NULL)))
1080 AND ( (Recinfo.Departure_Net_Weight = p_stop_info.Departure_Net_Weight)
1081 OR ( (Recinfo.Departure_Net_Weight IS NULL)
1082 AND (p_stop_info.Departure_Net_Weight IS NULL)))
1083 AND ( (Recinfo.Weight_Uom_Code = p_stop_info.Weight_Uom_Code)
1084 OR ( (Recinfo.Weight_Uom_Code IS NULL)
1085 AND (p_stop_info.Weight_Uom_Code IS NULL)))
1086 AND ( (Recinfo.Departure_Volume = p_stop_info.Departure_Volume)
1087 OR ( (Recinfo.Departure_Volume IS NULL)
1088 AND (p_stop_info.Departure_Volume IS NULL)))
1089 AND ( (Recinfo.Volume_Uom_Code = p_stop_info.Volume_Uom_Code)
1090 OR ( (Recinfo.Volume_Uom_Code IS NULL)
1091 AND (p_stop_info.Volume_Uom_Code IS NULL)))
1092 AND ( (Recinfo.Departure_Seal_Code = p_stop_info.Departure_Seal_Code)
1093 OR ( (Recinfo.Departure_Seal_Code IS NULL)
1094 AND (p_stop_info.Departure_Seal_Code IS NULL)))
1095 AND ( (Recinfo.Departure_Fill_Percent = p_stop_info.Departure_Fill_Percent)
1096 OR ( (Recinfo.Departure_Fill_Percent IS NULL)
1097 AND (p_stop_info.Departure_Fill_Percent IS NULL)))
1098 AND ( (Recinfo.Creation_Date = p_stop_info.Creation_Date)
1099 OR ( (Recinfo.Creation_Date IS NULL)
1100 AND (p_stop_info.Creation_Date IS NULL)))
1101 AND ( (Recinfo.Created_By = p_stop_info.Created_By)
1102 OR ( (Recinfo.Created_By IS NULL)
1103 AND (p_stop_info.Created_By IS NULL)))
1104 AND ( (Recinfo.Last_Update_Date = p_stop_info.Last_Update_Date)
1105 OR ( (Recinfo.Last_Update_Date IS NULL)
1106 AND (p_stop_info.Last_Update_Date IS NULL)))
1107 AND ( (Recinfo.Last_Updated_By = p_stop_info.Last_Updated_By)
1108 OR ( (Recinfo.Last_Updated_By IS NULL)
1109 AND (p_stop_info.Last_Updated_By IS NULL)))
1110 AND ( (Recinfo.Last_Update_Login = p_stop_info.Last_Update_Login)
1111 OR ( (Recinfo.Last_Update_Login IS NULL)
1112 AND (p_stop_info.Last_Update_Login IS NULL)))
1113 AND ( (Recinfo.Program_Application_Id = p_stop_info.Program_Application_Id)
1114 OR ( (Recinfo.Program_Application_Id IS NULL)
1115 AND (p_stop_info.Program_Application_Id IS NULL)))
1116 AND ( (Recinfo.Program_Id = p_stop_info.Program_Id)
1117 OR ( (Recinfo.Program_Id IS NULL)
1118 AND (p_stop_info.Program_Id IS NULL)))
1119 AND ( (Recinfo.Program_Update_Date = p_stop_info.Program_Update_Date)
1120 OR ( (Recinfo.Program_Update_Date IS NULL)
1121 AND (p_stop_info.Program_Update_Date IS NULL)))
1122 AND ( (Recinfo.Request_Id = p_stop_info.Request_Id)
1123 OR ( (Recinfo.Request_Id IS NULL)
1124 AND (p_stop_info.Request_Id IS NULL)))
1125 AND ( (Recinfo.Attribute_Category = p_stop_info.Attribute_Category)
1126 OR ( (Recinfo.Attribute_Category IS NULL)
1127 AND (p_stop_info.Attribute_Category IS NULL)))
1128 AND ( (Recinfo.Attribute1 = p_stop_info.Attribute1)
1129 OR ( (Recinfo.Attribute1 IS NULL)
1130 AND (p_stop_info.Attribute1 IS NULL)))
1131 AND ( (Recinfo.Attribute2 = p_stop_info.Attribute2)
1132 OR ( (Recinfo.Attribute2 IS NULL)
1133 AND (p_stop_info.Attribute2 IS NULL)))
1134 AND ( (Recinfo.Attribute3 = p_stop_info.Attribute3)
1135 OR ( (Recinfo.Attribute3 IS NULL)
1136 AND (p_stop_info.Attribute3 IS NULL)))
1137 AND ( (Recinfo.Attribute4 = p_stop_info.Attribute4)
1138 OR ( (Recinfo.Attribute4 IS NULL)
1139 AND (p_stop_info.Attribute4 IS NULL)))
1140 AND ( (Recinfo.Attribute5 = p_stop_info.Attribute5)
1141 OR ( (Recinfo.Attribute5 IS NULL)
1142 AND (p_stop_info.Attribute5 IS NULL)))
1143 AND ( (Recinfo.Attribute6 = p_stop_info.Attribute6)
1144 OR ( (Recinfo.Attribute6 IS NULL)
1145 AND (p_stop_info.Attribute6 IS NULL)))
1146 AND ( (Recinfo.Attribute7 = p_stop_info.Attribute7)
1147 OR ( (Recinfo.Attribute7 IS NULL)
1148 AND (p_stop_info.Attribute7 IS NULL)))
1149 AND ( (Recinfo.Attribute8 = p_stop_info.Attribute8)
1150 OR ( (Recinfo.Attribute8 IS NULL)
1151 AND (p_stop_info.Attribute8 IS NULL)))
1152 AND ( (Recinfo.Attribute9 = p_stop_info.Attribute9)
1153 OR ( (Recinfo.Attribute9 IS NULL)
1154 AND (p_stop_info.Attribute9 IS NULL)))
1155 AND ( (Recinfo.Attribute10 = p_stop_info.Attribute10)
1156 OR ( (Recinfo.Attribute10 IS NULL)
1157 AND (p_stop_info.Attribute10 IS NULL)))
1158 AND ( (Recinfo.Attribute11 = p_stop_info.Attribute11)
1159 OR ( (Recinfo.Attribute11 IS NULL)
1160 AND (p_stop_info.Attribute11 IS NULL)))
1161 AND ( (Recinfo.Attribute12 = p_stop_info.Attribute12)
1162 OR ( (Recinfo.Attribute12 IS NULL)
1163 AND (p_stop_info.Attribute12 IS NULL)))
1164 AND ( (Recinfo.Attribute13 = p_stop_info.Attribute13)
1165 OR ( (Recinfo.Attribute13 IS NULL)
1166 AND (p_stop_info.Attribute13 IS NULL)))
1167 AND ( (Recinfo.Attribute14 = p_stop_info.Attribute14)
1168 OR ( (Recinfo.Attribute14 IS NULL)
1169 AND (p_stop_info.Attribute14 IS NULL)))
1170 AND ( (Recinfo.Attribute15 = p_stop_info.Attribute15)
1171 OR ( (Recinfo.Attribute15 IS NULL)
1172 AND (p_stop_info.Attribute15 IS NULL)))
1173 AND ( (Recinfo.Tp_Attribute_Category = p_stop_info.Tp_Attribute_Category)
1174 OR ( (Recinfo.Tp_Attribute_Category IS NULL)
1175 AND (p_stop_info.Tp_Attribute_Category IS NULL)))
1176 AND ( (Recinfo.Tp_Attribute1 = p_stop_info.Tp_Attribute1)
1177 OR ( (Recinfo.Tp_Attribute1 IS NULL)
1178 AND (p_stop_info.Tp_Attribute1 IS NULL)))
1179 AND ( (Recinfo.Tp_Attribute2 = p_stop_info.Tp_Attribute2)
1180 OR ( (Recinfo.Tp_Attribute2 IS NULL)
1181 AND (p_stop_info.Tp_Attribute2 IS NULL)))
1182 AND ( (Recinfo.Tp_Attribute3 = p_stop_info.Tp_Attribute3)
1183 OR ( (Recinfo.Tp_Attribute3 IS NULL)
1184 AND (p_stop_info.Tp_Attribute3 IS NULL)))
1185 AND ( (Recinfo.Tp_Attribute4 = p_stop_info.Tp_Attribute4)
1186 OR ( (Recinfo.Tp_Attribute4 IS NULL)
1187 AND (p_stop_info.Tp_Attribute4 IS NULL)))
1188 AND ( (Recinfo.Tp_Attribute5 = p_stop_info.Tp_Attribute5)
1189 OR ( (Recinfo.Tp_Attribute5 IS NULL)
1190 AND (p_stop_info.Tp_Attribute5 IS NULL)))
1191 AND ( (Recinfo.Tp_Attribute6 = p_stop_info.Tp_Attribute6)
1192 OR ( (Recinfo.Tp_Attribute6 IS NULL)
1193 AND (p_stop_info.Tp_Attribute6 IS NULL)))
1194 AND ( (Recinfo.Tp_Attribute7 = p_stop_info.Tp_Attribute7)
1195 OR ( (Recinfo.Tp_Attribute7 IS NULL)
1196 AND (p_stop_info.Tp_Attribute7 IS NULL)))
1197 AND ( (Recinfo.Tp_Attribute8 = p_stop_info.Tp_Attribute8)
1198 OR ( (Recinfo.Tp_Attribute8 IS NULL)
1199 AND (p_stop_info.Tp_Attribute8 IS NULL)))
1200 AND ( (Recinfo.Tp_Attribute9 = p_stop_info.Tp_Attribute9)
1201 OR ( (Recinfo.Tp_Attribute9 IS NULL)
1202 AND (p_stop_info.Tp_Attribute9 IS NULL)))
1203 AND ( (Recinfo.Tp_Attribute10 = p_stop_info.Tp_Attribute10)
1204 OR ( (Recinfo.Tp_Attribute10 IS NULL)
1205 AND (p_stop_info.Tp_Attribute10 IS NULL)))
1206 AND ( (Recinfo.Tp_Attribute11 = p_stop_info.Tp_Attribute11)
1207 OR ( (Recinfo.Tp_Attribute11 IS NULL)
1208 AND (p_stop_info.Tp_Attribute11 IS NULL)))
1209 AND ( (Recinfo.Tp_Attribute12 = p_stop_info.Tp_Attribute12)
1210 OR ( (Recinfo.Tp_Attribute12 IS NULL)
1211 AND (p_stop_info.Tp_Attribute12 IS NULL)))
1212 AND ( (Recinfo.Tp_Attribute13 = p_stop_info.Tp_Attribute13)
1213 OR ( (Recinfo.Tp_Attribute13 IS NULL)
1214 AND (p_stop_info.Tp_Attribute13 IS NULL)))
1215 AND ( (Recinfo.Tp_Attribute14 = p_stop_info.Tp_Attribute14)
1216 OR ( (Recinfo.Tp_Attribute14 IS NULL)
1217 AND (p_stop_info.Tp_Attribute14 IS NULL)))
1218 AND ( (Recinfo.Tp_Attribute15 = p_stop_info.Tp_Attribute15)
1219 OR ( (Recinfo.Tp_Attribute15 IS NULL)
1220 AND (p_stop_info.Tp_Attribute15 IS NULL)))
1221 AND ( (Recinfo.wsh_location_id = p_stop_info.wsh_location_id)
1222 OR ( (Recinfo.wsh_location_id IS NULL)
1223 AND (p_stop_info.wsh_location_id IS NULL)))
1224 AND ( (Recinfo.tracking_drilldown_flag = p_stop_info.tracking_drilldown_flag)
1225 OR ( (Recinfo.tracking_drilldown_flag IS NULL)
1226 AND (p_stop_info.tracking_drilldown_flag IS NULL)))
1227 AND ( (Recinfo.tracking_remarks = p_stop_info.tracking_remarks)
1228 OR ( (Recinfo.tracking_remarks IS NULL)
1229 AND (p_stop_info.tracking_remarks IS NULL)))
1230 AND ( (Recinfo.carrier_est_departure_date = p_stop_info.carrier_est_departure_date)
1231 OR ( (Recinfo.carrier_est_departure_date IS NULL)
1232 AND (p_stop_info.carrier_est_departure_date IS NULL)))
1233 AND ( (Recinfo.carrier_est_arrival_date = p_stop_info.carrier_est_arrival_date)
1234 OR ( (Recinfo.carrier_est_arrival_date IS NULL)
1235 AND (p_stop_info.carrier_est_arrival_date IS NULL)))
1236 AND ( (Recinfo.loading_start_datetime = p_stop_info.loading_start_datetime)
1237 OR ( (Recinfo.loading_start_datetime IS NULL)
1238 AND (p_stop_info.loading_start_datetime IS NULL)))
1239 AND ( (Recinfo.loading_end_datetime = p_stop_info.loading_end_datetime)
1240 OR ( (Recinfo.loading_end_datetime IS NULL)
1241 AND (p_stop_info.loading_end_datetime IS NULL)))
1242 AND ( (Recinfo.unloading_start_datetime = p_stop_info.unloading_start_datetime)
1243 OR ( (Recinfo.unloading_start_datetime IS NULL)
1244 AND (p_stop_info.unloading_start_datetime IS NULL)))
1245 AND ( (Recinfo.unloading_end_datetime = p_stop_info.unloading_end_datetime)
1246 OR ( (Recinfo.unloading_end_datetime IS NULL)
1247 AND (p_stop_info.unloading_end_datetime IS NULL)))
1248 AND ( (nvl(Recinfo.shipments_type_flag, 'O') = nvl(p_stop_info.shipments_type_flag,'O'))
1249 OR ( (Recinfo.shipments_type_flag IS NULL)
1250 AND (p_stop_info.shipments_type_flag IS NULL)))
1251 -- J: W/V Changes
1252 AND ( (Recinfo.wv_frozen_flag = p_stop_info.wv_frozen_flag)
1253 OR ( (Recinfo.wv_frozen_flag IS NULL)
1254 AND (p_stop_info.wv_frozen_flag IS NULL)))
1255 AND ( (Recinfo.tp_stop_id = p_stop_info.tp_stop_id)
1256 OR ( (Recinfo.tp_stop_id IS NULL)
1257 AND (p_stop_info.tp_stop_id IS NULL)))
1258 AND ( (Recinfo.wkend_layover_stops = p_stop_info.wkend_layover_stops)
1259 OR ( (Recinfo.wkend_layover_stops IS NULL)
1260 AND (p_stop_info.wkend_layover_stops IS NULL)))
1261 AND ( (Recinfo.wkday_layover_stops = p_stop_info.wkday_layover_stops)
1262 OR ( (Recinfo.wkday_layover_stops IS NULL)
1263 AND (p_stop_info.wkday_layover_stops IS NULL)))
1264 AND ( (Recinfo.physical_stop_id = p_stop_info.physical_stop_id)
1265 OR ( (Recinfo.physical_stop_id IS NULL)
1266 AND (p_stop_info.physical_stop_id IS NULL)))
1267 AND ( (Recinfo.physical_location_id = p_stop_info.physical_location_id)
1268 OR ( (Recinfo.physical_location_id IS NULL)
1269 AND (p_stop_info.physical_location_id IS NULL)))
1270 -- OTM R12, glog proj
1271 AND ( (Recinfo.TMS_INTERFACE_FLAG = p_stop_info.TMS_INTERFACE_FLAG)
1272 OR ( (Recinfo.TMS_INTERFACE_FLAG is NULL)
1273 AND (p_stop_info.TMS_INTERFACE_FLAG is NULL)))
1274
1275 ) THEN
1276 --
1277 -- Debug Statements
1278 --
1279 IF l_debug_on THEN
1280 WSH_DEBUG_SV.pop(l_module_name,'RETURN');
1281 END IF;
1282 --
1283 return;
1284 ELSE
1285 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1286 app_exception.raise_exception;
1287 END IF;
1288
1289 --
1290 -- Debug Statements
1291 --
1292 IF l_debug_on THEN
1293 WSH_DEBUG_SV.pop(l_module_name);
1294 END IF;
1295 --
1296 EXCEPTION
1297 WHEN app_exception.application_exception or app_exception.record_lock_exception THEN
1298 if (lock_row%ISOPEN) then
1299 close lock_row;
1300 end if;
1301 --
1302 IF l_debug_on THEN
1303 WSH_DEBUG_SV.logmsg(l_module_name,'APP_EXCEPTION.APPLICATION_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1304 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:APP_EXCEPTION.APPLICATION_EXCEPTION');
1305 END IF;
1306 --
1307 RAISE;
1308 --
1309 WHEN others THEN
1310 --
1311 if (lock_row%ISOPEN) then
1312 close lock_row;
1313 end if;
1314 --
1315 IF l_debug_on THEN
1316 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1317 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1318 END IF;
1319 --
1320 raise;
1321 --
1322 END Lock_Trip_Stop;
1323
1324 Procedure Populate_Record(
1325 p_stop_id IN NUMBER,
1326 x_stop_info OUT NOCOPY trip_stop_rec_type,
1327 x_return_status OUT NOCOPY VARCHAR2) IS
1328
1329 CURSOR stop_record IS
1330 SELECT
1331 STOP_ID,
1332 TRIP_ID,
1333 STOP_LOCATION_ID,
1334 STATUS_CODE,
1335 STOP_SEQUENCE_NUMBER,
1336 PLANNED_ARRIVAL_DATE,
1337 PLANNED_DEPARTURE_DATE,
1338 ACTUAL_ARRIVAL_DATE,
1339 ACTUAL_DEPARTURE_DATE,
1340 DEPARTURE_GROSS_WEIGHT,
1341 DEPARTURE_NET_WEIGHT,
1342 WEIGHT_UOM_CODE,
1343 DEPARTURE_VOLUME,
1344 VOLUME_UOM_CODE,
1345 DEPARTURE_SEAL_CODE,
1346 DEPARTURE_FILL_PERCENT,
1347 TP_ATTRIBUTE_CATEGORY,
1348 TP_ATTRIBUTE1,
1349 TP_ATTRIBUTE2,
1350 TP_ATTRIBUTE3,
1351 TP_ATTRIBUTE4,
1352 TP_ATTRIBUTE5,
1353 TP_ATTRIBUTE6,
1354 TP_ATTRIBUTE7,
1355 TP_ATTRIBUTE8,
1356 TP_ATTRIBUTE9,
1357 TP_ATTRIBUTE10,
1358 TP_ATTRIBUTE11,
1359 TP_ATTRIBUTE12,
1360 TP_ATTRIBUTE13,
1361 TP_ATTRIBUTE14,
1362 TP_ATTRIBUTE15,
1363 ATTRIBUTE_CATEGORY,
1364 ATTRIBUTE1,
1365 ATTRIBUTE2,
1366 ATTRIBUTE3,
1367 ATTRIBUTE4,
1368 ATTRIBUTE5,
1369 ATTRIBUTE6,
1370 ATTRIBUTE7,
1371 ATTRIBUTE8,
1372 ATTRIBUTE9,
1373 ATTRIBUTE10,
1374 ATTRIBUTE11,
1375 ATTRIBUTE12,
1376 ATTRIBUTE13,
1377 ATTRIBUTE14,
1378 ATTRIBUTE15,
1379 CREATION_DATE,
1380 CREATED_BY,
1381 LAST_UPDATE_DATE,
1382 LAST_UPDATED_BY,
1383 LAST_UPDATE_LOGIN,
1384 PROGRAM_APPLICATION_ID,
1385 PROGRAM_ID,
1386 PROGRAM_UPDATE_DATE,
1387 REQUEST_ID,
1388 WSH_LOCATION_ID,
1389 TRACKING_DRILLDOWN_FLAG,
1390 TRACKING_REMARKS,
1391 CARRIER_EST_DEPARTURE_DATE,
1392 CARRIER_EST_ARRIVAL_DATE,
1393 LOADING_START_DATETIME,
1394 LOADING_END_DATETIME,
1395 UNLOADING_START_DATETIME,
1396 UNLOADING_END_DATETIME,
1397 ROWID,
1398 NULL,
1399 NULL,
1400 NULL,
1401 NULL,
1402 NULL,
1403 NULL,
1404 NULL,
1405 SHIPMENTS_TYPE_FLAG,
1406 -- J: W/V Changes
1407 WV_FROZEN_FLAG,
1408 wkend_layover_stops,
1409 wkday_layover_stops,
1410 tp_stop_id,
1411 physical_stop_id,
1412 physical_location_id,
1413 tms_interface_flag -- OTM R12, glog proj
1414 FROM wsh_trip_stops
1415 WHERE stop_id = p_stop_id;
1416
1417 others EXCEPTION;
1418
1419 --
1420 l_debug_on BOOLEAN;
1421 --
1422 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'POPULATE_RECORD';
1423 --
1424 BEGIN
1425 --
1426 -- Debug Statements
1427 --
1428 --
1429 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1430 --
1431 IF l_debug_on IS NULL
1432 THEN
1433 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1434 END IF;
1435 --
1436 IF l_debug_on THEN
1437 WSH_DEBUG_SV.push(l_module_name);
1438 --
1439 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
1440 END IF;
1441 --
1442 IF (p_stop_id IS NULL) THEN
1443 raise others;
1444 END IF;
1445
1446 OPEN stop_record;
1447 FETCH stop_record INTO x_stop_info;
1448
1449 IF (stop_record%NOTFOUND) THEN
1450 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
1451 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1452 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
1453 ELSE
1454 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1455 END IF;
1456
1457 CLOSE stop_record;
1458
1459 --
1460 -- Debug Statements
1461 --
1462 IF l_debug_on THEN
1463 WSH_DEBUG_SV.pop(l_module_name);
1464 END IF;
1465 --
1466 EXCEPTION
1467 WHEN others THEN
1468 wsh_util_core.default_handler('WSH_TRIP_STOPS_PVT.POPULATE_RECORD',l_module_name);
1469 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1470
1471 --
1472 -- Debug Statements
1473 --
1474 IF l_debug_on THEN
1475 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1476 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1477 END IF;
1478 --
1479 END Populate_Record;
1480
1481
1482 --
1483 -- Function: Get_Name
1484 -- Parameters: p_stop_id - Id for stop
1485 -- Description: This procedure will return stop Name for a stop Id
1486 --
1487
1488 FUNCTION Get_Name
1489 (p_stop_id IN NUMBER,
1490 --tkt
1491 p_caller IN VARCHAR2
1492 --tkt
1493 ) RETURN VARCHAR2 IS
1494
1495 --tkt
1496 CURSOR get_id IS
1497 SELECT stop_location_id, physical_location_id
1498 FROM wsh_trip_stops
1499 WHERE stop_id = p_stop_id;
1500 --tkt
1501 x_name VARCHAR2(60);
1502 x_id NUMBER;
1503 l_phys_loc_id NUMBER;
1504
1505 /* Bug 7325837 Increasing Size of the variable as returned variable from get_location_description
1506 can be of size upto 185 (location_code and city or address1) */
1507 --l_name wsh_locations.location_code%TYPE;
1508 l_name VARCHAR2(185);
1509
1510 others EXCEPTION;
1511
1512 --
1513 l_debug_on BOOLEAN;
1514 --
1515 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_NAME';
1516 --
1517 BEGIN
1518
1519 --
1520 -- Debug Statements
1521 --
1522 --
1523 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1524 --
1525 IF l_debug_on IS NULL
1526 THEN
1527 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1528 END IF;
1529 --
1530 IF l_debug_on THEN
1531 WSH_DEBUG_SV.push(l_module_name);
1532 --
1533 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
1534 --tkt
1535 WSH_DEBUG_SV.log(l_module_name,'p_caller',p_caller);
1536 --tkt
1537 END IF;
1538 --
1539 IF (p_stop_id IS NULL) THEN
1540 raise others;
1541 END IF;
1542
1543 OPEN get_id;
1544 FETCH get_id INTO x_id, l_phys_loc_id;
1545
1546 IF get_id%NOTFOUND THEN
1547 CLOSE get_id;
1548 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
1549 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
1550 IF l_debug_on THEN
1551 WSH_DEBUG_SV.pop(l_module_name);
1552 END IF;
1553 RETURN null;
1554 END IF;
1555 IF get_id%ISOPEN THEN
1556 CLOSE get_id;
1557 END IF;
1558
1559 --tkt
1560 IF l_phys_loc_id IS NOT NULL
1561 AND ( nvl(p_caller,'@@@') like 'FTE%'
1562 OR nvl(p_caller,'@@@') like 'WSH_IB%'
1563 OR nvl(p_caller,'@@@') like 'WSH_TP_RELEASE%'
1564 ) THEN
1565 x_id:=l_phys_loc_id;
1566 END IF;
1567 --
1568 -- Debug Statements
1569 --
1570 IF l_debug_on THEN
1571 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_LOCATION_DESCRIPTION',WSH_DEBUG_SV.C_PROC_LEVEL);
1572 WSH_DEBUG_SV.log(l_module_name,'x_id',x_id);
1573 END IF;
1574 --
1575 l_name := WSH_UTIL_CORE.get_location_description(x_id, 'NEW UI CODE');
1576 x_name := SUBSTR(l_name,1,60);
1577
1578 IF (x_name IS NULL) THEN
1579 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
1580 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
1581 --
1582 -- Debug Statements
1583 --
1584 IF l_debug_on THEN
1585 WSH_DEBUG_SV.pop(l_module_name);
1586 END IF;
1587 --
1588 RETURN null;
1589 END IF;
1590
1591 --
1592 -- Debug Statements
1593 --
1594 IF l_debug_on THEN
1595 WSH_DEBUG_SV.pop(l_module_name);
1596 END IF;
1597 --
1598 RETURN x_name;
1599
1600 EXCEPTION
1601
1602 WHEN others THEN
1603 wsh_util_core.default_handler('WSH_TRIP_STOPS_PVT.GET_NAME',l_module_name);
1604 --
1605 IF l_debug_on THEN
1606 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1607 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1608 END IF;
1609 --
1610 RETURN null;
1611
1612 --
1613 END Get_Name;
1614
1615
1616 -----------------------------------------------------------------------------
1617 --
1618 -- Procedure: Get_Disabled_List
1619 -- Parameters: stop_id, x_return_status, p_trip_flag
1620 -- Description: Get the disabled columns/fields in a trip
1621 --
1622 -----------------------------------------------------------------------------
1623 PROCEDURE Get_Disabled_List (
1624 p_stop_id IN NUMBER,
1625 p_parent_entity_id IN NUMBER,
1626 p_list_type IN VARCHAR2,
1627 x_return_status OUT NOCOPY VARCHAR2,
1628 x_disabled_list OUT NOCOPY wsh_util_core.column_tab_type,
1629 x_msg_count OUT NOCOPY NUMBER,
1630 x_msg_data OUT NOCOPY VARCHAR2,
1631 p_caller IN VARCHAR2 -- DEFAULT NULL, --3509004:public api changes
1632 ) IS
1633
1634 l_status_code VARCHAR2(10) := NULL;
1635 l_planned_flag VARCHAR2(10) := NULL;
1636 i NUMBER := 0;
1637 dummy_id NUMBER := 0;
1638 l_msg_summary VARCHAR2(2000);
1639 l_msg_details VARCHAR2(4000);
1640 l_shipments_type_flag VARCHAR(30) := NULL; --3509004:public api changes
1641 e_all_disabled EXCEPTION ; --3509004:public api changes
1642
1643
1644 CURSOR get_stop_status(x_stop_id NUMBER) IS
1645 SELECT status_code,
1646 stop_sequence_number, -- Bug 3814592
1647 planned_arrival_date, -- Bug 3814592
1648 SHIPMENTS_TYPE_FLAG , --3509004:public api changes
1649 physical_stop_id -- csun 10+ internal location
1650 FROM wsh_trip_stops
1651 WHERE stop_id = x_stop_id;
1652
1653 CURSOR c_has_closed_dummy_stop(x_stop_id NUMBER, x_trip_id NUMBER) IS
1654 SELECT stop_id
1655 FROM wsh_trip_stops
1656 WHERE trip_id = x_trip_id
1657 AND status_code = 'CL'
1658 AND physical_stop_id = x_stop_id;
1659
1660 --OTM R12, glog proj
1661 CURSOR get_trip_status(p_entity_id IN NUMBER) IS
1662 SELECT status_code, planned_flag,tp_plan_name
1663 FROM wsh_trips
1664 WHERE trip_id = p_entity_id;
1665
1666 CURSOR has_pick_up_deliveries(x_stop_id NUMBER) IS
1667 SELECT delivery_id
1668 FROM wsh_delivery_legs
1669 WHERE pick_up_stop_id = x_stop_id
1670 AND rownum = 1;
1671
1672 CURSOR has_drop_off_deliveries(x_stop_id NUMBER) IS
1673 SELECT delivery_id
1674 FROM wsh_delivery_legs
1675 WHERE drop_off_stop_id = x_stop_id
1676 AND rownum = 1;
1677
1678
1679 -- Bug 3814592
1680 -- Used for In-transit Mixed trips which can have
1681 -- Open Stops before a closed stop (lower sequence Number)
1682 -- Check if there exists a closed/arrived stop with a sequence
1683 -- number higher than the current open stop(use stop_sequence_number to compare)
1684 CURSOR get_updateable_open_stop (v_stop_sequence_number NUMBER)IS
1685 SELECT cur.stop_id
1686 FROM wsh_trip_stops cur
1687 WHERE cur.trip_id = p_parent_entity_id
1688 AND cur.stop_id <> p_stop_id
1689 AND cur.status_code in ('CL','AR')
1690 AND cur.stop_sequence_number > v_stop_sequence_number;
1691
1692 l_open_stop get_updateable_open_stop%ROWTYPE;
1693 l_stop_sequence_number NUMBER;
1694 l_planned_arrival_date DATE;
1695 l_found BOOLEAN;
1696 -- End of Bug 3814592
1697 l_physical_stop_id NUMBER;
1698 --
1699
1700 WSH_DP_NO_ENTITY EXCEPTION;
1701 WSH_INV_LIST_TYPE EXCEPTION;
1702 l_ssn_disabled BOOLEAN; -- used only to track this column when stop is OPEN
1703 l_pad_disabled BOOLEAN; -- used only to track this column when stop is OPEN
1704
1705 -- OTM R12, glog proj
1706 l_tp_plan_name WSH_TRIPS.TP_PLAN_NAME%TYPE;
1707 l_gc3_is_installed VARCHAR2(1);
1708
1709 l_debug_on BOOLEAN;
1710 --
1711 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DISABLED_LIST';
1712 --
1713
1714 BEGIN
1715 --
1716 -- Debug Statements
1717 --
1718 --
1719 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1720 --
1721 IF l_debug_on IS NULL
1722 THEN
1723 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1724 END IF;
1725 --
1726 IF l_debug_on THEN
1727 WSH_DEBUG_SV.push(l_module_name);
1728 --
1729 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
1730 WSH_DEBUG_SV.log(l_module_name,'P_PARENT_ENTITY_ID',P_PARENT_ENTITY_ID);
1731 WSH_DEBUG_SV.log(l_module_name,'P_LIST_TYPE',P_LIST_TYPE);
1732 END IF;
1733 --
1734 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1735 --OTM R12, glog proj
1736 l_tp_plan_name := NULL;
1737
1738 --OTM R12, glog proj, use Global Variable
1739 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
1740
1741 -- If null, call the function
1742 IF l_gc3_is_installed IS NULL THEN
1743 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
1744 END IF;
1745 -- end of OTM R12, glog proj
1746
1747 -- clear up the list table
1748 x_disabled_list.DELETE;
1749
1750 OPEN get_stop_status(p_stop_id);
1751 FETCH get_stop_status
1752 INTO l_status_code,
1753 l_stop_sequence_number, -- Bug 3814592
1754 l_planned_arrival_date, -- Bug 3814592
1755 l_shipments_type_flag, --3509004:public api changes
1756 l_physical_stop_id; -- csun 10+ internal location
1757 if get_stop_status%NOTFOUND then
1758 close get_stop_status;
1759 raise WSH_DP_NO_ENTITY;
1760 end if;
1761 CLOSE get_stop_status;
1762 IF (p_list_type <> 'FORM') THEN
1763 RAISE WSH_INV_LIST_TYPE;
1764 END IF;
1765
1766 IF (l_status_code = 'CL') THEN
1767 i:=i+1; x_disabled_list(i) := 'FULL';
1768 i:=i+1; x_disabled_list(i) := 'TP_FLEXFIELD';
1769 i:=i+1; x_disabled_list(i) := 'DESC_FLEX';
1770
1771 ELSIF ( l_status_code = 'AR') THEN
1772 -- FRONT PORTING Bug 3134466
1773 l_status_code := NULL;
1774 OPEN get_trip_status(p_parent_entity_id);
1775 FETCH get_trip_status
1776 INTO l_status_code, l_planned_flag,
1777 -- OTM R12, glog proj
1778 l_tp_plan_name;
1779 IF get_trip_status%NOTFOUND THEN
1780 CLOSE get_trip_status;
1781 FND_MESSAGE.Set_Name('WSH','WSH_API_INVALID_PARAM_VALUE');
1782 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1783 --
1784 RETURN;
1785 END IF;
1786 CLOSE get_trip_status;
1787
1788 IF NVL(p_caller,'!!!') LIKE 'FTE%' AND NVL(p_caller,'!!!') <> 'FTE_TMS_INTEGRATION' THEN --3509004:public api changes
1789 i:=i+1; x_disabled_list(i) := 'STOP_LOCATION_ID';
1790 ELSE
1791 i:=i+1; x_disabled_list(i) := 'STOP_LOCATION_CODE';
1792 END IF;
1793 i:=i+1; x_disabled_list(i) := 'PLANNED_ARRIVAL_DATE';
1794 i:=i+1; x_disabled_list(i) := 'STOP_SEQUENCE_NUMBER';
1795 -- Bug 3282166
1796 --
1797 i:=i+1; x_disabled_list(i) := 'CARRIER_EST_DEPARTURE_DATE';
1798 i:=i+1; x_disabled_list(i) := 'CARRIER_EST_ARRIVAL_DATE';
1799 --
1800 -- csun 10+ internal location
1801 IF l_planned_flag = 'Y'
1802 OR l_physical_stop_id is not NULL THEN
1803 i:=i+1; x_disabled_list(i) := 'PLANNED_DEPARTURE_DATE';
1804 END IF;
1805
1806 ELSIF (l_status_code = 'OP') THEN
1807 l_pad_disabled := FALSE;
1808 l_ssn_disabled := FALSE;
1809
1810 IF (p_parent_entity_id IS NULL) THEN
1811 FND_MESSAGE.Set_Name('WSH','WSH_API_INVALID_PARAM_VALUE');
1812 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1813 --
1814 -- Debug Statements
1815 --
1816 IF l_debug_on THEN
1817 WSH_DEBUG_SV.pop(l_module_name);
1818 END IF;
1819 --
1820 RETURN;
1821 END IF;
1822
1823 l_status_code := NULL;
1824 OPEN get_trip_status(p_parent_entity_id);
1825 FETCH get_trip_status
1826 INTO l_status_code, l_planned_flag,
1827 -- OTM R12, glog proj
1828 l_tp_plan_name;
1829 IF get_trip_status%NOTFOUND THEN
1830 CLOSE get_trip_status;
1831 FND_MESSAGE.Set_Name('WSH','WSH_API_INVALID_PARAM_VALUE');
1832 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1833 --
1834 -- Debug Statements
1835 --
1836 IF l_debug_on THEN
1837 WSH_DEBUG_SV.pop(l_module_name);
1838 END IF;
1839 --
1840 RETURN;
1841 END IF;
1842 CLOSE get_trip_status;
1843
1844 IF (l_planned_flag IN ('Y','F')) THEN
1845 -- trip is planned.
1846 IF NVL(p_caller,'!!!') LIKE 'FTE%' AND NVL(p_caller,'!!!') <> 'FTE_TMS_INTEGRATION' THEN --3509004:public api changes
1847 i:=i+1; x_disabled_list(i) := 'STOP_LOCATION_ID';
1848 ELSE
1849 i:=i+1; x_disabled_list(i) := 'STOP_LOCATION_CODE';
1850 END IF;
1851 l_pad_disabled := TRUE;
1852 i:=i+1; x_disabled_list(i) := 'PLANNED_ARRIVAL_DATE';
1853 i:=i+1; x_disabled_list(i) := 'PLANNED_DEPARTURE_DATE';
1854
1855 -- SSN change
1856 -- THIS SHOULD BE TRUE INDEPENDENT OF PROFILE
1857 l_ssn_disabled := TRUE;
1858 i:=i+1; x_disabled_list(i) := 'STOP_SEQUENCE_NUMBER';
1859
1860 ELSE
1861 -- ---------------------------------------------------------------
1862 -- if trip is not planned, check which condition it is
1863 -- 1. has both pick up and drop deliveries
1864 -- 2. has only pick up deliveries
1865 -- 3. has only drop off deliveries
1866 -- 4. none, all the fields are enabled, return empty table
1867 -- ---------------------------------------------------------------
1868
1869 -- begin csun 10+ internal location
1870 IF l_physical_stop_id is not NULL THEN
1871 l_pad_disabled := TRUE;
1872 i:=i+1; x_disabled_list(i) := 'PLANNED_ARRIVAL_DATE';
1873 i:=i+1; x_disabled_list(i) := 'PLANNED_DEPARTURE_DATE';
1874 -- end csun 10+ internal location
1875 -- SSN change
1876 -- THIS SHOULD BE TRUE INDEPENDENT OF PROFILE
1877 l_ssn_disabled := TRUE;
1878 i:=i+1; x_disabled_list(i) := 'STOP_SEQUENCE_NUMBER';
1879
1880 ELSIF l_status_code = 'IT' THEN
1881 -- Bug 3814592, For trips which are in-transit, open stops which lie
1882 -- inbetween closed/arrived stops, planned arrival date should
1883 -- not be updateable (Mixed Trips scenario, combination of Inbound
1884 -- and Outbound).
1885 IF l_debug_on THEN
1886 WSH_DEBUG_SV.log(l_module_name,'Intransit Trip',p_parent_entity_id);
1887 END IF;
1888 -- Check if the current stop being processed lies inbetween stops
1889 -- which are closed/arrived
1890 OPEN get_updateable_open_stop(l_stop_sequence_number);
1891 FETCH get_updateable_open_stop
1892 INTO l_open_stop;
1893
1894 l_found := get_updateable_open_stop%FOUND;
1895
1896 CLOSE get_updateable_open_stop;
1897
1898 IF l_found THEN--Found Arrived/Closed Stop with higher sequence number
1899 l_pad_disabled := TRUE;
1900 i:=i+1; x_disabled_list(i) := 'PLANNED_ARRIVAL_DATE';
1901 --
1902 -- SSN Change
1903 -- THIS SHOULD BE TRUE INDEPENDENT OF PROFILE
1904 l_ssn_disabled := TRUE;
1905 i:=i+1; x_disabled_list(i) := 'STOP_SEQUENCE_NUMBER';
1906 --
1907
1908 IF l_debug_on THEN
1909 WSH_DEBUG_SV.log(l_module_name,'Mixed Trip Open Stop-',p_stop_id);
1910 END IF;
1911 END IF;
1912 ELSE
1913 -- SSN Change
1914 -- Only for Profile set to PAD and none of the above conditions are met
1915 IF WSH_TRIPS_ACTIONS.GET_STOP_SEQ_MODE =
1916 WSH_INTERFACE_GRP.G_STOP_SEQ_MODE_PAD THEN
1917 l_ssn_disabled := TRUE;
1918 i:=i+1; x_disabled_list(i) := 'STOP_SEQUENCE_NUMBER';
1919 END IF;
1920 --
1921 END IF; -- physical_stop_id is NOT NULL
1922 -- End of Bug 3814592
1923
1924 -- FRONT PORTIN BUG FIX 3134466
1925 OPEN has_pick_up_deliveries(p_stop_id);
1926 FETCH has_pick_up_deliveries INTO dummy_id;
1927 IF has_pick_up_deliveries%FOUND THEN
1928 IF NVL(p_caller,'!!!') LIKE 'FTE%' AND NVL(p_caller,'!!!') <> 'FTE_TMS_INTEGRATION' THEN --3509004:public api changes
1929 i:=i+1; x_disabled_list(i) := 'STOP_LOCATION_ID';
1930 ELSE
1931 i:=i+1; x_disabled_list(i) := 'STOP_LOCATION_CODE';
1932 END IF;
1933 IF l_debug_on THEN
1934 WSH_DEBUG_SV.log(l_module_name,'Pickup Delivery found-',l_planned_flag);
1935 END IF;
1936 ELSE
1937 -- no pick_up deliveries, check if it has drop_off deliveries
1938 OPEN has_drop_off_deliveries(p_stop_id);
1939 FETCH has_drop_off_deliveries INTO dummy_id;
1940 IF has_drop_off_deliveries%FOUND THEN
1941 IF NVL(p_caller,'!!!') LIKE 'FTE%' AND NVL(p_caller,'!!!') <> 'FTE_TMS_INTEGRATION' THEN --3509004:public api changes
1942 i:=i+1; x_disabled_list(i) := 'STOP_LOCATION_ID';
1943 ELSE
1944 i:=i+1; x_disabled_list(i) := 'STOP_LOCATION_CODE';
1945 END IF;
1946 END IF;
1947 CLOSE has_drop_off_deliveries;
1948 IF l_debug_on THEN
1949 WSH_DEBUG_SV.log(l_module_name,'Pickup Delivery Not found-',l_planned_flag);
1950 END IF;
1951 END IF;
1952 CLOSE has_pick_up_deliveries;
1953
1954 -- END OF FRONT PORTING BUG FIX 3134466
1955
1956 -- OTM R12, glog project
1957 IF l_debug_on THEN
1958 WSH_DEBUG_SV.log(l_module_name,'Caller--',p_caller);
1959 WSH_DEBUG_SV.log(l_module_name,'Tp Plan Name--',l_tp_plan_name);
1960 WSH_DEBUG_SV.log(l_module_name,'GC3 Installed --',l_gc3_is_installed);
1961 END IF;
1962 -- Disable Stop Location and Stop Sequence Number
1963 -- for GC3 trips (tp_plan_name is not null)
1964 -- For UI, disable Stop_Location_Code, for Inbound message
1965 --(Planned Shipment Interface) from OTM,disable Location id.
1966 IF l_gc3_is_installed = 'Y' AND l_tp_plan_name IS NOT NULL THEN
1967 i:=i+1; x_disabled_list(i) := 'STOP_SEQUENCE_NUMBER';
1968 -- allow Planned Shipment Interface to Update, else disable
1969 IF nvl(p_caller,'@@@') <> 'FTE_TMS_INTEGRATION' THEN
1970 i:=i+1; x_disabled_list(i) := 'PLANNED_ARRIVAL_DATE';
1971 i:=i+1; x_disabled_list(i) := 'PLANNED_DEPARTURE_DATE';
1972 END IF;
1973 END IF;
1974 -- End of OTM R12, glog project change
1975 --
1976
1977
1978
1979 END IF; /* check if the stop is planned */
1980
1981 -- stop is open; check if we need to look for linked, closed stop:
1982 -- we are not allowed to resequence the stop
1983 -- when its linked dummy stop is closed.
1984 -- This will avoid synchronization issues
1985 -- in wsh_trip_actions.handle_internal_stops.
1986 -- When sequencing mode is PAD, we will disable the planned arrival date.
1987 -- When sequencing mode is SSN, we will disable both stop sequence number
1988 -- and planned arrival date (because the dummy stop's PAD has to be in sync).
1989 --
1990 IF (NOT l_ssn_disabled) OR (NOT l_pad_disabled) THEN
1991
1992 OPEN c_has_closed_dummy_stop(p_stop_id, p_parent_entity_id);
1993 FETCH c_has_closed_dummy_stop INTO dummy_id;
1994 IF c_has_closed_dummy_stop%NOTFOUND THEN
1995 dummy_id := NULL;
1996 END IF;
1997 CLOSE c_has_closed_dummy_stop;
1998
1999 IF dummy_id IS NOT NULL THEN
2000 IF l_debug_on THEN
2001 WSH_DEBUG_SV.log(l_module_name,'Stop has a closed linked dummy stop',dummy_id);
2002 WSH_DEBUG_SV.log(l_module_name,'l_pad_disabled',l_pad_disabled);
2003 WSH_DEBUG_SV.log(l_module_name,'l_ssn_disabled',l_ssn_disabled);
2004 END IF;
2005 IF NOT l_ssn_disabled THEN
2006 i:=i+1; x_disabled_list(i) := 'STOP_SEQUENCE_NUMBER';
2007 END IF;
2008 IF NOT l_pad_disabled THEN
2009 i:=i+1; x_disabled_list(i) := 'PLANNED_ARRIVAL_DATE';
2010 END IF;
2011 END IF;
2012 END IF;
2013
2014 END IF; /* check status code */
2015
2016 -- 3509004:public api changes
2017 -- J-IB-NPARIKH-{
2018 --
2019 --
2020 -- Update on inbound trip stops are allowed only if caller
2021 -- starts with one of the following:
2022 -- - FTE
2023 -- - WSH_IB
2024 -- - WSH_PUB
2025 -- - WSH_TP_RELEASE
2026 --
2027 IF NVL(l_shipments_type_flag,'O') = 'I'
2028 AND NVL(p_caller, '!!!') NOT LIKE 'FTE%'
2029 AND NVL(p_caller, '!!!') NOT LIKE 'WSH_PUB%'
2030 AND NVL(p_caller, '!!!') NOT LIKE 'WSH_IB%'
2031 AND NVL(p_caller, '!!!') NOT LIKE 'WSH_TP_RELEASE%'
2032 THEN
2033 RAISE e_all_disabled;
2034 END IF;
2035 --
2036 --
2037 IF l_status_code IN ( 'CL')
2038 AND (
2039 NVL(l_shipments_type_flag,'O') = 'I'
2040 OR (
2041 NVL(l_shipments_type_flag,'O') = 'M'
2042 AND ( NVL(p_caller, '!!!') LIKE 'FTE%'
2043 OR NVL(p_caller, '!!!') LIKE 'WSH_PUB%'
2044 OR NVL(p_caller, '!!!') LIKE 'WSH_IB%'
2045 )
2046 )
2047 )
2048 THEN
2049 --{
2050 --
2051 -- For inbound/mixed stops, weight/volume are updateable even if stop is closed.
2052 --
2053 -- For mixed stops, update is allowed only if caller starts with FTE/WSH_PUB/WSH_IB
2054 --
2055 IF x_disabled_list(1) = 'FULL'
2056 THEN
2057 i := x_disabled_list.count;
2058 --
2059 i:=i+1; x_disabled_list(i) := 'DEPARTURE_GROSS_WEIGHT';
2060 i:=i+1; x_disabled_list(i) := 'DEPARTURE_NET_WEIGHT';
2061 i:=i+1; x_disabled_list(i) := 'DEPARTURE_VOLUME';
2062 END IF;
2063 --}
2064 END IF;
2065 --
2066 -- J-IB-NPARIKH-}
2067 --
2068
2069 --
2070 -- Debug Statements
2071 --
2072 IF l_debug_on THEN
2073 WSH_DEBUG_SV.pop(l_module_name);
2074 END IF;
2075 --
2076 EXCEPTION
2077 WHEN e_all_disabled THEN --3509004:public api changes
2078 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2079 FND_MESSAGE.SET_NAME('WSH','WSH_ALL_COLS_DISABLED');
2080 FND_MESSAGE.Set_Token('ENTITY_ID',p_stop_id);
2081 wsh_util_core.add_message(x_return_status,l_module_name);
2082 IF l_debug_on THEN
2083 -- Nothing is updateable
2084 WSH_DEBUG_SV.pop(l_module_name,'e_all_disabled');
2085 END IF;
2086
2087 WHEN WSH_DP_NO_ENTITY THEN
2088 FND_MESSAGE.SET_NAME('WSH', 'WSH_DP_NO_ENTITY');
2089 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
2090 x_return_status := FND_API.G_RET_STS_ERROR;
2091 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
2092 if x_msg_count > 1 then
2093 x_msg_data := l_msg_summary || l_msg_details;
2094 else
2095 x_msg_data := l_msg_summary;
2096 end if;
2097
2098 -- invalid list type
2099 --
2100 -- Debug Statements
2101 --
2102 IF l_debug_on THEN
2103 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_DP_NO_ENTITY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2104 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_DP_NO_ENTITY');
2105 END IF;
2106 --
2107 WHEN WSH_INV_LIST_TYPE THEN
2108 FND_MESSAGE.SET_NAME('WSH', 'WSH_INV_LIST_TYPE');
2109 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
2110 x_return_status := FND_API.G_RET_STS_ERROR;
2111 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
2112 if x_msg_count > 1 then
2113 x_msg_data := l_msg_summary || l_msg_details;
2114 else
2115 x_msg_data := l_msg_summary;
2116 end if;
2117
2118 --
2119 -- Debug Statements
2120 --
2121 IF l_debug_on THEN
2122 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INV_LIST_TYPE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2123 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INV_LIST_TYPE');
2124 END IF;
2125 --
2126 WHEN OTHERS THEN
2127 IF get_stop_status%ISOPEN THEN
2128 CLOSE get_stop_status;
2129 END IF;
2130 IF c_has_closed_dummy_stop%ISOPEN THEN
2131 CLOSE c_has_closed_dummy_stop;
2132 END IF;
2133 IF has_pick_up_deliveries%ISOPEN THEN
2134 CLOSE has_pick_up_deliveries;
2135 END IF;
2136 IF has_drop_off_deliveries%ISOPEN THEN
2137 CLOSE has_drop_off_deliveries;
2138 END IF;
2139 -- Bug 3814592
2140 IF get_updateable_open_stop%ISOPEN THEN
2141 CLOSE get_updateable_open_stop;
2142 END IF;
2143 -- End of Bug 3814592
2144
2145 IF get_trip_status%ISOPEN THEN
2146 CLOSE get_trip_status;
2147 END IF;
2148
2149 FND_MESSAGE.Set_Name('WSH','WSH_UNEXPECTED_ERROR');
2150 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2151
2152 IF l_debug_on THEN
2153 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2154 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2155 END IF;
2156 END Get_Disabled_List ;
2157
2158 PROCEDURE lock_trip_stop_no_compare (p_stop_id IN NUMBER)
2159 IS
2160 l_stop_id NUMBER;
2161 l_debug_on BOOLEAN;
2162 --
2163 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.'
2164 || 'lock_trip_stop_no_compare';
2165 CURSOR c_lock_stop IS
2166 SELECT stop_id
2167 FROM wsh_trip_stops
2168 WHERE stop_id = p_stop_id
2169 FOR UPDATE NOWAIT;
2170
2171 BEGIN
2172 --
2173 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2174 --
2175 IF l_debug_on IS NULL
2176 THEN
2177 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2178 END IF;
2179 --
2180 IF l_debug_on THEN
2181 WSH_DEBUG_SV.push(l_module_name);
2182 WSH_DEBUG_SV.log(l_module_name,'p_stop_id',p_stop_id);
2183 END IF;
2184
2185 OPEN c_lock_stop;
2186 FETCH c_lock_stop INTO l_stop_id;
2187 CLOSE c_lock_stop;
2188
2189 IF l_debug_on THEN
2190 WSH_DEBUG_SV.log(l_module_name,'stop id is locked',l_stop_id);
2191 END IF;
2192
2193 IF l_debug_on THEN
2194 WSH_DEBUG_SV.pop(l_module_name);
2195 END IF;
2196
2197 EXCEPTION
2198 WHEN app_exception.application_exception
2199 OR app_exception.record_lock_exception THEN
2200 IF l_debug_on THEN
2201 wsh_debug_sv.log(l_module_name, 'Could not lock stop', p_stop_id);
2202 WSH_DEBUG_SV.pop(l_module_name, 'EXCEPTION:APPLICTION_EXCEPTION');
2203 END IF;
2204 --
2205 RAISE;
2206
2207 WHEN OTHERS THEN
2208 --
2209 wsh_util_core.default_handler('WSH_TRIP_STOPS_PVT.lock_trip_stop_no_compare',l_module_name);
2210 --
2211 IF l_debug_on THEN
2212 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2213 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2214 END IF;
2215 --
2216 RAISE;
2217 END lock_trip_stop_no_compare;
2218
2219 procedure Lock_Trip_Stop(
2220 p_rec_attr_tab IN Stop_Attr_Tbl_Type,
2221 p_caller IN VARCHAR2,
2222 p_valid_index_tab IN wsh_util_core.id_tab_type,
2223 x_valid_ids_tab OUT NOCOPY wsh_util_core.id_tab_type,
2224 x_return_status OUT NOCOPY VARCHAR2
2225 )
2226 IS
2227 --
2228 --
2229 l_index NUMBER := 0;
2230 l_num_errors NUMBER := 0;
2231
2232 --
2233 l_debug_on BOOLEAN;
2234 --
2235 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_TRIP_STOP_WRAPPER';
2236 --
2237 BEGIN
2238 --
2239 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2240 --
2241 IF l_debug_on IS NULL
2242 THEN
2243 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2244 END IF;
2245 --
2246 IF l_debug_on THEN
2247 WSH_DEBUG_SV.push(l_module_name);
2248 WSH_DEBUG_SV.log(l_module_name,'p_caller',p_caller);
2249 WSH_DEBUG_SV.log(l_module_name,'Total Number of Stop Records being locked',p_valid_index_tab.COUNT);
2250 END IF;
2251 --
2252 --
2253 l_index := p_valid_index_tab.FIRST;
2254 --
2255 while l_index is not null loop
2256 begin
2257 --
2258 savepoint lock_trip_stop_loop;
2259 --
2260 IF p_caller = 'WSH_FSTRX' THEN
2261 lock_trip_stop(p_rowid => p_rec_attr_tab(l_index).rowid,
2262 p_stop_info => p_rec_attr_tab(l_index)
2263 );
2264 ELSE
2265 lock_trip_stop_no_compare(p_rec_attr_tab(l_index).stop_id);
2266 END IF;
2267
2268 IF nvl(p_caller,'!') <> 'WSH_FSTRX' THEN
2269 x_valid_ids_tab(x_valid_ids_tab.COUNT + 1) := p_rec_attr_tab(l_index).stop_id;
2270 ELSE
2271 x_valid_ids_tab(x_valid_ids_tab.COUNT + 1) := l_index;
2272 END IF;
2273 --
2274 exception
2275 --
2276 WHEN app_exception.application_exception or app_exception.record_lock_exception THEN
2277 rollback to lock_trip_stop_loop;
2278 IF nvl(p_caller,'!') <> 'WSH_FSTRX' THEN
2279 FND_MESSAGE.SET_NAME('WSH', 'WSH_STOP_LOCK_FAILED');
2280 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',p_rec_attr_tab(l_index).stop_id);
2281 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
2282 END IF;
2283 l_num_errors := l_num_errors + 1;
2284 --
2285 IF l_debug_on THEN
2286 WSH_DEBUG_SV.log(l_module_name,'Unable to obtain lock on the Stop Id',p_rec_attr_tab(l_index).stop_id);
2287 END IF;
2288 --
2289 WHEN others THEN
2290 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2291 end;
2292 --
2293 l_index := p_valid_index_tab.NEXT(l_index);
2294 --
2295 end loop;
2296 --
2297 IF p_valid_index_tab.COUNT = 0 THEN
2298 x_return_status := wsh_util_core.g_ret_sts_success;
2299 ELSIF l_num_errors = p_valid_index_tab.COUNT THEN
2300 FND_MESSAGE.SET_NAME('WSH', 'WSH_UI_NOT_PERFORMED');
2301 x_return_status := wsh_util_core.g_ret_sts_error;
2302 wsh_util_core.add_message(x_return_status,l_module_name);
2303 IF l_debug_on THEN
2304 wsh_debug_sv.logmsg(l_module_name, 'WSH_UI_NOT_PERFORMED');
2305 END IF;
2306 RAISE FND_API.G_EXC_ERROR;
2307 ELSIF l_num_errors > 0 THEN
2308 FND_MESSAGE.SET_NAME('WSH', 'WSH_UI_NOT_PROCESSED');
2309 x_return_status := wsh_util_core.g_ret_sts_warning;
2310 wsh_util_core.add_message(x_return_status,l_module_name);
2311 IF l_debug_on THEN
2312 wsh_debug_sv.logmsg(l_module_name, 'WSH_UI_NOT_PROCESSED');
2313 END IF;
2314 raise wsh_util_core.g_exc_warning;
2315 ELSE
2316 x_return_status := wsh_util_core.g_ret_sts_success;
2317 END IF;
2318 --
2319 IF l_debug_on THEN
2320 WSH_DEBUG_SV.pop(l_module_name);
2321 END IF;
2322 --
2323 EXCEPTION
2324 --
2325 --
2326 WHEN FND_API.G_EXC_ERROR THEN
2327 --
2328 x_return_status := wsh_util_core.g_ret_sts_error;
2329 --
2330 IF l_debug_on THEN
2331 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2332 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2333 END IF;
2334 --
2335 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2336 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
2337 --
2338 IF l_debug_on THEN
2339 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2340 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2341 END IF;
2342 --
2343 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
2344 x_return_status := wsh_util_core.g_ret_sts_warning;
2345 --
2346 IF l_debug_on THEN
2347 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2348 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2349 END IF;
2350 --
2351 WHEN OTHERS THEN
2352 --
2353 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
2354 --
2355 wsh_util_core.default_handler('WSH_TRIP_STOPS_PVT.LOCK_TRIP_STOP_WRAPPER',l_module_name);
2356 --
2357 IF l_debug_on THEN
2358 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2359 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2360 END IF;
2361 --
2362 END Lock_Trip_Stop;
2363
2364 --OTM R12, glog proj, new procedure
2365 ----------------------------------------------------------
2366 -- PROCEDURE UPDATE_TMS_INTERFACE_FLAG
2367 --
2368 -- Parameters: p_stop_id_tab table of stop ids to update
2369 -- p_tms_interface_flag_tab table of the interface_flag
2370 -- for the stop to set to
2371 -- x_return_status return status
2372 --
2373 -- Description: This procedure updates the stop's tms_interface_flag
2374 -- according to the flag in the p_tms_interface_flag_tab.
2375 ----------------------------------------------------------
2376 Procedure Update_Tms_interface_flag
2377 (p_stop_id_tab IN WSH_UTIL_CORE.ID_TAB_TYPE,
2378 p_tms_interface_flag_tab IN WSH_UTIL_CORE.COLUMN_TAB_TYPE,
2379 x_return_status OUT NOCOPY VARCHAR2) IS
2380
2381 l_stop_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
2382 l_dbi_rs VARCHAR2(1); -- DBI Project
2383 l_loop_counter NUMBER;
2384 RECORD_LOCKED EXCEPTION;
2385 PRAGMA EXCEPTION_INIT(RECORD_LOCKED, -54);
2386
2387 --
2388 l_debug_on BOOLEAN;
2389 --
2390 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_TMS_INTERFACE_FLAG';
2391 --
2392 BEGIN
2393 --
2394 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2395 --
2396 IF l_debug_on IS NULL THEN
2397 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2398 END IF;
2399 --
2400 IF l_debug_on THEN
2401 WSH_DEBUG_SV.push(l_module_name);
2402 WSH_DEBUG_SV.log(l_module_name,'STOP_Tab Count',p_stop_id_tab.COUNT);
2403 IF p_stop_id_tab.count > 0 THEN
2404 l_loop_counter := 0;
2405 l_loop_counter := p_stop_id_tab.FIRST;
2406 LOOP--{
2407 WSH_DEBUG_SV.log(l_module_name,
2408 'STOP_ID',p_stop_id_tab(l_loop_counter));
2409 WSH_DEBUG_SV.log(l_module_name,'TMS_INTERFACE_FLAG',
2410 p_tms_interface_flag_tab(l_loop_counter));
2411 EXIT WHEN l_loop_counter >= p_stop_id_tab.LAST;
2412 l_loop_counter := p_stop_id_tab.NEXT(l_loop_counter);
2413 END LOOP;--}
2414 END IF;
2415 END IF;
2416 --
2417 SAVEPOINT update_tms_interface;
2418
2419 IF ((p_stop_id_tab.COUNT <> p_tms_interface_flag_tab.COUNT)
2420 OR (p_stop_id_tab.COUNT=0)) THEN
2421 IF l_debug_on THEN
2422 WSH_DEBUG_SV.logmsg(l_module_name,
2423 'Stop ID and TMS_interface_flag_tab count does not match');
2424 END IF;
2425 RAISE FND_API.G_EXC_ERROR;
2426 END IF;
2427
2428 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2429
2430 IF (p_stop_id_tab.COUNT > 0) THEN--{
2431 FORALL i in p_stop_id_tab.FIRST..p_stop_id_tab.LAST
2432 UPDATE wsh_trip_stops
2433 SET TMS_INTERFACE_FLAG = p_tms_interface_flag_tab(i),
2434 last_update_date = SYSDATE,
2435 last_updated_by = FND_GLOBAL.USER_ID,
2436 last_update_login = FND_GLOBAL.LOGIN_ID
2437 WHERE STOP_ID = p_stop_id_tab(i);
2438
2439 --
2440 -- DBI Project
2441 -- Update WSH_TRIP_STOPS.
2442 -- Call DBI API after the UPDATE.
2443 -- This API will also check for DBI Installed or not
2444 l_loop_counter := 0;
2445 l_loop_counter := p_stop_id_tab.FIRST;
2446 LOOP--{
2447 IF l_debug_on THEN
2448 WSH_DEBUG_SV.log(l_module_name,
2449 'Calling DBI API.Stop id-',
2450 p_stop_id_tab(l_loop_counter));
2451 END IF;
2452
2453 l_dbi_rs := NULL;
2454 l_stop_tab(1) := p_stop_id_tab(l_loop_counter);
2455
2456 WSH_INTEGRATION.dbi_update_trip_stop_log
2457 (p_stop_id_tab => l_stop_tab,
2458 p_dml_type => 'UPDATE',
2459 x_return_status => l_dbi_rs);
2460
2461 IF l_debug_on THEN
2462 WSH_DEBUG_SV.log(l_module_name,
2463 'Return Status after DBI Call-',
2464 l_dbi_rs);
2465 END IF;
2466
2467 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2468 IF l_debug_on THEN
2469 WSH_DEBUG_SV.log(l_module_name,
2470 'DBI API Returned Unexpected error '||x_return_status);
2471 END IF;
2472 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2473 END IF;
2474
2475 EXIT WHEN l_loop_counter >= p_stop_id_tab.LAST;
2476 l_loop_counter := p_stop_id_tab.NEXT(l_loop_counter);
2477 END LOOP;--}
2478 -- End of Code for DBI Project
2479 --
2480
2481 END IF;--}
2482 --
2483 -- Debug Statements
2484 --
2485 IF l_debug_on THEN
2486 WSH_DEBUG_SV.log(l_module_name,'Return Status'||x_return_status);
2487 WSH_DEBUG_SV.pop(l_module_name);
2488 END IF;
2489 --
2490 EXCEPTION
2491 --
2492 WHEN FND_API.G_EXC_ERROR THEN
2493 ROLLBACK TO update_tms_interface;
2494 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2495 --
2496 IF l_debug_on THEN
2497 WSH_DEBUG_SV.logmsg(l_module_name,
2498 'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2499 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2500 END IF;
2501 --
2502 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2503 --
2504 ROLLBACK TO update_tms_interface;
2505 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2506 --
2507 IF l_debug_on THEN
2508 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2509 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2510 END IF;
2511 --
2512 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
2513 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2514 --
2515 IF l_debug_on THEN
2516 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2517 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2518 END IF;
2519 --
2520
2521 WHEN RECORD_LOCKED THEN
2522 ROLLBACK TO update_tms_interface;
2523 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2524 IF l_debug_on THEN
2525 WSH_DEBUG_SV.logmsg(l_module_name,'Record_locked exception has occured. Cannot update stop tms_interface_flag', WSH_DEBUG_SV.C_EXCEP_LEVEL);
2526 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:RECORD_LOCKED');
2527 END IF;
2528
2529 WHEN others THEN
2530 ROLLBACK TO update_tms_interface;
2531 wsh_util_core.default_handler('WSH_TRIP_STOPS_PVT.UPDATE_TMS_INTERFACE_FLAG',l_module_name);
2532 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2533
2534 --
2535 -- Debug Statements
2536 --
2537 IF l_debug_on THEN
2538 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2539 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2540 END IF;
2541 --
2542 END UPDATE_TMS_INTERFACE_FLAG;
2543
2544 END WSH_TRIP_STOPS_PVT;