[Home] [Help]
PACKAGE BODY: APPS.FTE_TRIPS_PVT
Source
1 PACKAGE BODY FTE_TRIPS_PVT AS
2 /* $Header: FTETRTHB.pls 115.22 2003/04/24 23:48:35 nltan noship $ */
3
4 --===================
5 -- CONSTANTS
6 --===================
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FTE_TRIPS_PVT';
8
9
10
11 -- Wrapper around create_trip and update_trip
12 -- (create pl/sql record and depending on p_action_code is 'CREATE' or 'UPDATE' or 'DELETE'
13
14 PROCEDURE Create_Update_Delete_Fte_Trip
15 (
16 p_api_version_number IN NUMBER,
17 p_init_msg_list IN VARCHAR2,
18 x_msg_count OUT NOCOPY NUMBER,
19 x_msg_data OUT NOCOPY VARCHAR2,
20 pp_FTE_TRIP_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
21 pp_NAME IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
22 pp_STATUS_CODE IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
23 pp_PRIVATE_TRIP IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
24 pp_VALIDATION_REQUIRED IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
25 pp_CREATION_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
26 pp_CREATED_BY IN NUMBER DEFAULT FND_API.G_MISS_NUM,
27 pp_LAST_UPDATE_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
28 pp_LAST_UPDATED_BY IN NUMBER DEFAULT FND_API.G_MISS_NUM,
29 pp_LAST_UPDATE_LOGIN IN NUMBER DEFAULT FND_API.G_MISS_NUM,
30 pp_PROGRAM_APPLICATION_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
31 pp_PROGRAM_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
32 pp_PROGRAM_UPDATE_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
33 pp_REQUEST_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
34 pp_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
35 pp_ATTRIBUTE1 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
36 pp_ATTRIBUTE2 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
37 pp_ATTRIBUTE3 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
38 pp_ATTRIBUTE4 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
39 pp_ATTRIBUTE5 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
40 pp_ATTRIBUTE6 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
41 pp_ATTRIBUTE7 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
42 pp_ATTRIBUTE8 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
43 pp_ATTRIBUTE9 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
44 pp_ATTRIBUTE10 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
45 pp_ATTRIBUTE11 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
46 pp_ATTRIBUTE12 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
47 pp_ATTRIBUTE13 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
48 pp_ATTRIBUTE14 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
49 pp_ATTRIBUTE15 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
50 pp_ROUTE_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
51 p_action_code IN VARCHAR2,
52 x_trip_id OUT NOCOPY NUMBER,
53 x_name OUT NOCOPY VARCHAR2,
54 x_return_status OUT NOCOPY VARCHAR2
55 ) IS
56 -- <insert here your local variables declaration>
57 l_api_version_number CONSTANT NUMBER := 1.0;
58 l_api_name CONSTANT VARCHAR2(30):= 'Create_Update_Fte_Trip';
59
60 -- <insert here your local variables declaration>
61 l_message VARCHAR2(50);
62
63
64 l_tmp_out NUMBER;
65
66 p_trip_info fte_trip_rec_type;
67
68 BEGIN
69
70 -- wsh_debug_sv.start_debug ('FteTrip-' || pp_fte_trip_id);
71 wsh_debug_sv.start_debug ('FteTrip');
72 wsh_debug_sv.dpush (c_sdebug, 'Create_Update_Delete_Fte_Trip');
73
74 wsh_debug_sv.dlog (c_debug,'Name',pp_name);
75 wsh_debug_sv.dlog (c_debug,'Action',p_action_code);
76
77 -- Standard call to check for call compatibility
78 IF NOT FND_API.Compatible_API_Call
79 ( l_api_version_number
80 , p_api_version_number
81 , l_api_name
82 , G_PKG_NAME
83 )
84 THEN
85 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
86 END IF;
87
88 -- Initialize message stack if required
89 IF FND_API.to_Boolean(p_init_msg_list) THEN
90 FND_MSG_PUB.initialize;
91 END IF;
92
93
94 x_return_status := FND_API.G_RET_STS_SUCCESS;
95
96 --create trip record
97 p_trip_info.FTE_TRIP_ID :=pp_fte_trip_id;
98 p_trip_info.NAME :=pp_name;
99 p_trip_info.STATUS_CODE :=pp_status_code;
100 p_trip_info.PRIVATE_TRIP :=pp_private_trip;
101 p_trip_info.VALIDATION_REQUIRED :=pp_validation_required;
102 p_trip_info.CREATION_DATE :=pp_creation_date;
103 p_trip_info.CREATED_BY :=pp_created_by;
104 p_trip_info.LAST_UPDATE_DATE :=pp_last_update_date;
105 p_trip_info.LAST_UPDATED_BY :=pp_last_updated_by;
106 p_trip_info.LAST_UPDATE_LOGIN :=pp_last_update_login;
107 p_trip_info.PROGRAM_APPLICATION_ID :=pp_program_application_id;
108 p_trip_info.PROGRAM_ID :=pp_program_id;
109 p_trip_info.PROGRAM_UPDATE_DATE :=pp_program_update_date;
110 p_trip_info.REQUEST_ID :=pp_request_id;
111 p_trip_info.ATTRIBUTE_CATEGORY :=pp_attribute_category;
112 p_trip_info.ATTRIBUTE1 :=pp_attribute1;
113 p_trip_info.ATTRIBUTE2 :=pp_attribute2;
114 p_trip_info.ATTRIBUTE3 :=pp_attribute3;
115 p_trip_info.ATTRIBUTE4 :=pp_attribute4;
116 p_trip_info.ATTRIBUTE5 :=pp_attribute5;
117 p_trip_info.ATTRIBUTE6 :=pp_attribute6;
118 p_trip_info.ATTRIBUTE7 :=pp_attribute7;
119 p_trip_info.ATTRIBUTE8 :=pp_attribute8;
120 p_trip_info.ATTRIBUTE9 :=pp_attribute9;
121 p_trip_info.ATTRIBUTE10 :=pp_attribute10;
122 p_trip_info.ATTRIBUTE11 :=pp_attribute11;
123 p_trip_info.ATTRIBUTE12 :=pp_attribute12;
124 p_trip_info.ATTRIBUTE13 :=pp_attribute13;
125 p_trip_info.ATTRIBUTE14 :=pp_attribute14;
126 p_trip_info.ATTRIBUTE15 :=pp_attribute15;
127 p_trip_info.ROUTE_ID :=pp_route_id;
128
129
130 if (p_action_code='CREATE') then
131 Create_Trip (p_trip_info =>p_trip_info,
132 x_trip_id =>x_trip_id,
133 x_name =>x_name,
134 x_return_status =>x_return_status
135 );
136 elsif (p_action_code='UPDATE') then
137 Update_Trip
138 (p_trip_info =>p_trip_info,
139 x_return_status =>x_return_status);
140 elsif (p_action_code='DELETE') then
141 Delete_Trip(p_trip_id =>p_trip_info.fte_trip_id,
142 x_return_status =>x_return_status);
143 end if;
144
145 -- report success
146
147 FND_MSG_PUB.Count_And_Get
148 ( p_encoded => FND_API.G_FALSE,
149 p_count => x_msg_count
150 , p_data => x_msg_data
151 );
152
153 wsh_debug_sv.dlog (c_debug,'Return Status',x_Return_Status);
154 wsh_debug_sv.dlog (c_debug,'Message Count',x_msg_count);
155 wsh_debug_sv.dlog (c_debug,'Message Data',x_msg_data);
156 wsh_debug_sv.dpop (c_sdebug);
157 wsh_debug_sv.stop_debug;
158
159 EXCEPTION
160 WHEN FND_API.G_EXC_ERROR THEN
161 x_return_status := FND_API.G_RET_STS_ERROR;
162 -- Get message count and data
163 FND_MSG_PUB.Count_And_Get
164 ( p_encoded => FND_API.G_FALSE,
165 p_count => x_msg_count
166 , p_data => x_msg_data
167 );
168 wsh_debug_sv.dlog (c_debug,'In error in Create_Update_Delete');
169 wsh_debug_sv.dpop (c_sdebug);
170 wsh_debug_sv.stop_debug;
171
172
173 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
174 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
175 -- Get message count and data
176 FND_MSG_PUB.Count_And_Get
177 ( p_encoded => FND_API.G_FALSE,
178 p_count => x_msg_count
179 , p_data => x_msg_data
180 );
181 wsh_debug_sv.dlog (c_debug,'In Unexpected error in Create_Update_Delete');
182 wsh_debug_sv.dpop (c_sdebug);
183 wsh_debug_sv.stop_debug;
184
185 WHEN others THEN
186 wsh_util_core.default_handler('FTE_TRIPS_PVT.Create_Update_Delete_Fte_Trip');
187 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
188 FND_MSG_PUB.Count_And_Get
189 ( p_encoded => FND_API.G_FALSE,
190 p_count => x_msg_count
191 , p_data => x_msg_data
192 );
193 wsh_debug_sv.dlog (c_debug,'In others in Create_Update_Delete');
194 wsh_debug_sv.dpop (c_sdebug);
195 wsh_debug_sv.stop_debug;
196
197 END Create_Update_Delete_Fte_Trip;
198
199
200
201
202
203 --========================================================================
204 -- PROCEDURE : Create_Trip
205 --
206 -- PARAMETERS: p_trip_info Attributes for the trip entity
207 -- x_return_status Return status of API
208 -- COMMENT : Creates trip record with p_trip_info information
209 --========================================================================
210
211 PROCEDURE Create_Trip
212 (p_trip_info IN fte_trip_rec_type,
213 x_trip_id OUT NOCOPY NUMBER,
214 x_name OUT NOCOPY VARCHAR2,
215 x_return_status OUT NOCOPY VARCHAR2
216 ) IS
217
218 CURSOR get_next_trip IS
219 SELECT fte_trips_s.nextval
220 FROM sys.dual;
221
222 CURSOR check_trip_names (v_trip_name VARCHAR2) IS
223 SELECT fte_trip_id FROM fte_trips
224 WHERE name = v_trip_name;
225
226 CURSOR check_trip_ids (v_trip_id NUMBER) IS
227 SELECT fte_trip_id FROM fte_trips
228 WHERE fte_trip_id = v_trip_id;
229
230 l_name fte_trips.name%TYPE;
231 l_row_check NUMBER;
232 l_temp_id NUMBER;
233
234 l_tmp_count NUMBER := 0;
235
236 wsh_duplicate_name EXCEPTION;
237
238 BEGIN
239
240 wsh_debug_sv.dpush (c_sdebug, 'Create_Trip');
241
242 wsh_debug_sv.dlog (c_debug,'trip_id ',p_trip_info.fte_trip_id);
243 wsh_debug_sv.dlog (c_debug,'Name ',p_trip_info.name);
244
245 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
246
247 x_trip_id := p_trip_info.fte_trip_id;
248 x_name := p_trip_info.name;
249
250 wsh_debug_sv.dlog (c_debug,'about to validate trip id...');
251
252 IF (x_trip_id IS NULL) OR (x_trip_id = FND_API.G_MISS_NUM) THEN
253
254 LOOP
255
256 OPEN get_next_trip;
257 FETCH get_next_trip INTO x_trip_id;
258 CLOSE get_next_trip;
259
260 l_row_check := NULL;
261
262 OPEN check_trip_ids(x_trip_id);
263 FETCH check_trip_ids INTO l_row_check;
264
265 IF (check_trip_ids%NOTFOUND) THEN
266 CLOSE check_trip_ids;
267 EXIT;
268 END IF;
269
270 CLOSE check_trip_ids;
271
272 END LOOP;
273
274
275 END IF;
276
277 wsh_debug_sv.dlog (c_debug,'about to validate trip name...');
278
279 IF (x_name IS NULL) OR (x_name = FND_API.G_MISS_CHAR) THEN
280 l_temp_id := x_trip_id;
281 LOOP
282 l_name := to_char(l_temp_id);
283
284 OPEN check_trip_names( l_name);
285 FETCH check_trip_names INTO l_row_check;
286
287 IF (check_trip_names%NOTFOUND) THEN
288 CLOSE check_trip_names;
289 EXIT;
290 END IF;
291
292 CLOSE check_trip_names;
293
294 OPEN get_next_trip;
295 FETCH get_next_trip INTO l_temp_id;
296 CLOSE get_next_trip;
297
298 END LOOP;
299
300 x_trip_id := l_temp_id;
301 x_name := l_name;
302
303 ELSE
304
305 wsh_debug_sv.dlog (c_debug,'about to call Validate_CreateTrip...');
306
307 --validation
308 Validate_CreateTrip (p_trip_name =>x_name,
309 x_return_status=>x_return_status);
310
311 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS)
312 AND (x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)
313 THEN
314 RETURN;
315 END IF;
316
317 END IF;
318
319 wsh_debug_sv.dlog (c_debug,'about to insert into fte_trips table...');
320 wsh_debug_sv.dlog (c_debug,'trip id ', x_trip_id);
321 wsh_debug_sv.dlog (c_debug,'name', x_name);
322
323 INSERT INTO fte_trips(
324 FTE_TRIP_ID ,
325 NAME ,
326 STATUS_CODE ,
327 PRIVATE_TRIP ,
328 VALIDATION_REQUIRED ,
329 CREATION_DATE ,
330 CREATED_BY ,
331 LAST_UPDATE_DATE ,
332 LAST_UPDATED_BY ,
333 LAST_UPDATE_LOGIN ,
334 PROGRAM_APPLICATION_ID ,
335 PROGRAM_ID ,
336 PROGRAM_UPDATE_DATE ,
337 REQUEST_ID ,
338 ATTRIBUTE_CATEGORY ,
339 ATTRIBUTE1 ,
340 ATTRIBUTE2 ,
341 ATTRIBUTE3 ,
342 ATTRIBUTE4 ,
343 ATTRIBUTE5 ,
344 ATTRIBUTE6 ,
345 ATTRIBUTE7 ,
346 ATTRIBUTE8 ,
347 ATTRIBUTE9 ,
348 ATTRIBUTE10 ,
349 ATTRIBUTE11 ,
350 ATTRIBUTE12 ,
351 ATTRIBUTE13 ,
352 ATTRIBUTE14 ,
353 ATTRIBUTE15 ,
354 ROUTE_ID
355 )
356 VALUES(
357 x_trip_id,
358 x_name,
359 'OP',
360 decode(p_trip_info.private_trip, FND_API.G_MISS_CHAR, NULL, p_trip_info.private_trip),
361 decode(p_trip_info.validation_required, FND_API.G_MISS_CHAR, NULL, p_trip_info.validation_required),
362 decode(p_trip_info.creation_date, FND_API.G_MISS_DATE, SYSDATE, NULL, SYSDATE, p_trip_info.creation_date),
363 decode(p_trip_info.created_by,FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, NULL, FND_GLOBAL.USER_ID, p_trip_info.created_by),
364 decode(p_trip_info.last_update_date,FND_API.G_MISS_DATE, SYSDATE, NULL, SYSDATE, p_trip_info.creation_date),
365 decode(p_trip_info.last_updated_by,FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, NULL, FND_GLOBAL.USER_ID, p_trip_info.last_updated_by),
366 decode(p_trip_info.last_update_login,FND_API.G_MISS_NUM, FND_GLOBAL.LOGIN_ID, NULL, FND_GLOBAL.LOGIN_ID, p_trip_info.last_update_login),
367 decode(p_trip_info.program_application_id, FND_API.G_MISS_NUM, NULL, p_trip_info.program_application_id),
368 decode(p_trip_info.program_id, FND_API.G_MISS_NUM, NULL, p_trip_info.program_id),
369 decode(p_trip_info.program_update_date, FND_API.G_MISS_DATE, NULL, p_trip_info.program_update_date),
370 decode(p_trip_info.request_id, FND_API.G_MISS_NUM, NULL, p_trip_info.request_id),
371 decode(p_trip_info.attribute_category, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute_category),
372 decode(p_trip_info.attribute1, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute1),
373 decode(p_trip_info.attribute2, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute2),
374 decode(p_trip_info.attribute3, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute3),
375 decode(p_trip_info.attribute4, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute4),
376 decode(p_trip_info.attribute5, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute5),
377 decode(p_trip_info.attribute6, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute6),
378 decode(p_trip_info.attribute7, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute7),
379 decode(p_trip_info.attribute8, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute8),
380 decode(p_trip_info.attribute9, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute9),
381 decode(p_trip_info.attribute10, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute10),
382 decode(p_trip_info.attribute11, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute11),
383 decode(p_trip_info.attribute12, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute12),
384 decode(p_trip_info.attribute13, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute13),
385 decode(p_trip_info.attribute14, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute14),
386 decode(p_trip_info.attribute15, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute15),
387 decode(p_trip_info.route_id, FND_API.G_MISS_NUM, NULL, p_trip_info.route_id)
388 );
389
390 wsh_debug_sv.dlog (c_debug,'right after insert statement---');
391 wsh_debug_sv.dlog (c_debug,'SQL%FOUND', SQL%FOUND);
392 wsh_debug_sv.dlog (c_debug,'SQL%ROWCOUNT', SQL%ROWCOUNT);
393
394 wsh_debug_sv.dlog (c_debug,'Return Status',x_Return_Status);
395 wsh_debug_sv.dpop (c_sdebug);
396
397 EXCEPTION
398 WHEN wsh_duplicate_name THEN
399 FND_MESSAGE.Set_Name('FND', 'FORM_DUPLICATE_KEY_IN_INDEX');
400 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
401 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
402
403 wsh_debug_sv.dlog (c_debug,'Return Status',x_Return_Status);
404 wsh_debug_sv.dlog (c_debug,'wsh_duplicate_name exception');
405 wsh_debug_sv.dpop (c_sdebug);
406
407 WHEN others THEN
408 wsh_util_core.default_handler('FTE_TRIPS_PVT.CREATE_TRIP');
409 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
410
411 wsh_debug_sv.dlog (c_debug,'Return Status',x_Return_Status);
412 wsh_debug_sv.dlog (c_debug,'others exception');
413 wsh_debug_sv.dpop (c_sdebug);
414
415 END Create_Trip;
416
417
418 PROCEDURE Validate_CreateTrip
419 (p_trip_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
420 p_trip_name IN VARCHAR2,
421 x_return_status OUT NOCOPY VARCHAR2
422 ) IS
423
424 CURSOR check_trip_names (v_trip_name VARCHAR2) IS
425 SELECT fte_trip_id FROM fte_trips
426 WHERE name = v_trip_name;
427
428 l_name fte_trips.name%TYPE;
429 l_row_check NUMBER;
430 wsh_duplicate_name EXCEPTION;
431
432 BEGIN
433
434 wsh_debug_sv.dpush (c_sdebug, 'Validate_CreateTrip');
435
436 wsh_debug_sv.dlog (c_debug,'trip_id ',p_trip_id);
437 wsh_debug_sv.dlog (c_debug,'Name ',p_trip_name);
438
439 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
440
441 wsh_debug_sv.dlog (c_debug,'about to check trip name... ');
442
443 OPEN check_trip_names(p_trip_name);
444 FETCH check_trip_names INTO l_row_check;
445
446 IF (check_trip_names%FOUND) THEN
447 CLOSE check_trip_names;
448 RAISE wsh_duplicate_name;
449 END IF;
450 CLOSE check_trip_names;
451
452 wsh_debug_sv.dlog (c_debug,'Return Status',x_Return_Status);
453 wsh_debug_sv.dpop (c_sdebug);
454
455 EXCEPTION
456 WHEN wsh_duplicate_name THEN
457 FND_MESSAGE.Set_Name('FND', 'FORM_DUPLICATE_KEY_IN_INDEX');
458 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
459 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
460
461 wsh_debug_sv.dlog (c_debug,'Return Status',x_Return_Status);
462 wsh_debug_sv.dlog (c_debug,'wsh_duplicate_name exception');
463 wsh_debug_sv.dpop (c_sdebug);
464
465 WHEN others THEN
466 wsh_util_core.default_handler('FTE_TRIPS_PVT.VALIDATE_CREATETRIP');
467 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
468
469 wsh_debug_sv.dlog (c_debug,'Return Status',x_Return_Status);
470 wsh_debug_sv.dlog (c_debug,'others exception');
471 wsh_debug_sv.dpop (c_sdebug);
472
473 END Validate_CreateTrip;
474
475
476
477
478
479 --========================================================================
480 -- PROCEDURE : Update_Trip
481 --
482 -- PARAMETERS: p_trip_info Attributes for the trip entity
483 -- x_return_status Return status of API
484 -- COMMENT : Updates trip record with p_trip_info information
485 --========================================================================
486
487 PROCEDURE Update_Trip(
488 p_trip_info IN fte_trip_rec_type,
489 x_return_status OUT NOCOPY VARCHAR2) IS
490
491 CURSOR check_trip_names (v_trip_name VARCHAR2) IS
492 SELECT fte_trip_id FROM fte_trips
493 WHERE name = v_trip_name;
494
495 l_row_check NUMBER;
496
497 wsh_duplicate_name EXCEPTION;
498 BEGIN
499
500 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
501
502 --validation
503 Validate_UpdateTrip(p_trip_id =>p_trip_info.fte_trip_id,
504 p_trip_name =>p_trip_info.name,
505 p_trip_status =>p_trip_info.status_code,
506 x_return_status => x_return_status);
507
508 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS)
509 AND (x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)
510 THEN
511 RETURN;
512 END IF;
513
514
515 UPDATE fte_trips SET
516 fte_trip_id = decode(p_trip_info.fte_trip_id,FND_API.G_MISS_NUM, fte_trip_id, p_trip_info.fte_trip_id),
517 name = decode(p_trip_info.name, FND_API.G_MISS_CHAR, name, p_trip_info.name),
518 status_code = decode(p_trip_info.status_code, FND_API.G_MISS_CHAR, status_code, p_trip_info.status_code),
519 private_trip=decode(p_trip_info.private_trip, FND_API.G_MISS_CHAR, NULL, p_trip_info.private_trip),
520 validation_required=decode(p_trip_info.validation_required, FND_API.G_MISS_CHAR, NULL, p_trip_info.validation_required),
521 creation_date=decode(p_trip_info.creation_date, FND_API.G_MISS_DATE, SYSDATE, NULL, SYSDATE, p_trip_info.creation_date),
522 created_by=decode(p_trip_info.created_by,FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, NULL, FND_GLOBAL.USER_ID, p_trip_info.created_by),
523 last_update_date=decode(p_trip_info.last_update_date,FND_API.G_MISS_DATE, SYSDATE, NULL, SYSDATE, p_trip_info.creation_date),
524 last_updated_by=decode(p_trip_info.last_updated_by,FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, NULL, FND_GLOBAL.USER_ID, p_trip_info.last_updated_by),
525 last_update_login=decode(p_trip_info.last_update_login,FND_API.G_MISS_NUM, FND_GLOBAL.LOGIN_ID, NULL, FND_GLOBAL.LOGIN_ID, p_trip_info.last_update_login),
526 program_application_id=decode(p_trip_info.program_application_id, FND_API.G_MISS_NUM, NULL, p_trip_info.program_application_id),
527 program_id= decode(p_trip_info.program_id, FND_API.G_MISS_NUM, NULL, p_trip_info.program_id),
528 program_update_date=decode(p_trip_info.program_update_date, FND_API.G_MISS_DATE, NULL, p_trip_info.program_update_date),
529 request_id=decode(p_trip_info.request_id, FND_API.G_MISS_NUM, NULL, p_trip_info.request_id),
530 attribute_category=decode(p_trip_info.attribute_category, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute_category),
531 attribute1=decode(p_trip_info.attribute1, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute1),
532 attribute2=decode(p_trip_info.attribute2, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute2),
533 attribute3=decode(p_trip_info.attribute3, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute3),
534 attribute4=decode(p_trip_info.attribute4, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute4),
535 attribute5=decode(p_trip_info.attribute5, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute5),
536 attribute6=decode(p_trip_info.attribute6, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute6),
537 attribute7=decode(p_trip_info.attribute7, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute7),
538 attribute8=decode(p_trip_info.attribute8, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute8),
539 attribute9=decode(p_trip_info.attribute9, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute9),
540 attribute10=decode(p_trip_info.attribute10, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute10),
541 attribute11=decode(p_trip_info.attribute11, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute11),
542 attribute12=decode(p_trip_info.attribute12, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute12),
543 attribute13=decode(p_trip_info.attribute13, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute13),
544 attribute14=decode(p_trip_info.attribute14, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute14),
545 attribute15=decode(p_trip_info.attribute15, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute15),
546 route_id=decode(p_trip_info.route_id, FND_API.G_MISS_NUM, NULL, p_trip_info.route_id)
547 WHERE fte_trip_id = p_trip_info.fte_trip_id;
548
549
550
551 IF (SQL%NOTFOUND) THEN
552 RAISE no_data_found;
553 END IF;
554
555 EXCEPTION
556 WHEN wsh_duplicate_name THEN
557 FND_MESSAGE.Set_Name('FND', 'FORM_DUPLICATE_KEY_IN_INDEX');
558 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
559 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
560 WHEN no_data_found THEN
561 FND_MESSAGE.SET_NAME('FTE','FTE_TRIP_NOT_FOUND');
562 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
563 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
564 WHEN others THEN
565 wsh_util_core.default_handler('FTE_TRIPS_PVT.UPDATE_TRIP');
566 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
567
568 END Update_Trip;
569
570
571 PROCEDURE Validate_UpdateTrip(
572 p_trip_id IN NUMBER,
573 p_trip_name IN VARCHAR2,
574 p_trip_status IN VARCHAR2,
575 x_return_status OUT NOCOPY VARCHAR2) IS
576
577 CURSOR check_trip_names (v_trip_name VARCHAR2) IS
578 SELECT fte_trip_id FROM fte_trips
579 WHERE name = v_trip_name;
580
581 l_row_check NUMBER;
582
583 no_fte_trip_id EXCEPTION;
584 wsh_duplicate_name EXCEPTION;
585 invalid_fte_trip_status EXCEPTION;
586 BEGIN
587
588 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
589
590 IF (p_trip_id = FND_API.G_MISS_NUM) THEN
591 RAISE no_fte_trip_id;
592 END IF;
593
594 IF (p_trip_status = 'CL') THEN
595 RAISE invalid_fte_trip_status;
596 END IF;
597
598 if (p_trip_name<>to_char(p_trip_id)) then
599 OPEN check_trip_names(p_trip_name);
600 FETCH check_trip_names INTO l_row_check;
601
602 IF (check_trip_names%FOUND) THEN
603 if (l_row_check<>(p_trip_id)) then
604 CLOSE check_trip_names;
605 RAISE wsh_duplicate_name;
606 end if;
607 END IF;
608
609 CLOSE check_trip_names;
610 end if;
611
612 EXCEPTION
613 WHEN wsh_duplicate_name THEN
614 FND_MESSAGE.Set_Name('FND', 'FORM_DUPLICATE_KEY_IN_INDEX');
615 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
616 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
617 WHEN no_fte_trip_id THEN
618 FND_MESSAGE.SET_NAME('FTE','FTE_NO_TRIP_ID');
619 FND_MESSAGE.SET_TOKEN('TRIP_ID', p_trip_id);
620 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
621 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
622 WHEN invalid_fte_trip_status THEN
623 FND_MESSAGE.SET_NAME('FTE','FTE_INVALID_TRIP_STATUS');
624 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
625 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
626 WHEN others THEN
627 wsh_util_core.default_handler('FTE_TRIPS_PVT.VALIDATE_UPDATETRIP');
628 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
629
630 END Validate_UpdateTrip;
631
632
633
634
635
636 PROCEDURE Delete_Trip(
637 p_trip_id IN NUMBER,
638 x_return_status OUT NOCOPY VARCHAR2
639 ) IS
640
641 l_trip_id NUMBER;
642 l_return_status VARCHAR2(1);
643
644
645 others EXCEPTION;
646
647 BEGIN
648
649 x_return_Status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
650
651 l_trip_id := p_trip_id;
652
653 --validate
654 Validate_DeleteTrip(p_trip_id=>l_trip_id,
655 x_return_status=>x_return_status
656 );
657
658 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS)
659 AND (x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)
660 THEN
661 RETURN;
662 END IF;
663
664 DELETE FROM fte_trips
665 WHERE fte_trip_id = l_trip_id;
666
667 IF (SQL%NOTFOUND) THEN
668 FND_MESSAGE.SET_NAME('FTE','FTE_NO_TRIP_ID');
669 FND_MESSAGE.SET_TOKEN('TRIP_ID', l_trip_id);
670 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
671 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
672 END IF;
673
674 EXCEPTION
675 WHEN others THEN
676 wsh_util_core.default_handler('FTE_TRIPS_PVT.DELETE_TRIP');
677 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
678
679 END Delete_Trip;
680
681
682 PROCEDURE Validate_DeleteTrip(
683 p_trip_id IN NUMBER,
684 x_return_status OUT NOCOPY VARCHAR2
685 ) IS
686
687 CURSOR trip_segments (l_trip_id IN NUMBER) IS
688 SELECT wsh_trip_id
689 FROM fte_wsh_trips
690 WHERE fte_trip_id = l_trip_id;
691
692 l_trip_id NUMBER;
693 l_wsh_trip_ids NUMBER;
694 l_return_status VARCHAR2(1);
695 l_trip_name VARCHAR2(30);
696
697 others EXCEPTION;
698
699 BEGIN
700
701 x_return_Status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
702
703 l_trip_id := p_trip_id;
704
705 OPEN trip_segments(l_trip_id);
706 fetch trip_segments into l_wsh_trip_ids;
707 IF (trip_segments%NOTFOUND) THEN
708 return;
709 else
710 get_trip_name
711 (
712 p_trip_id =>l_trip_id,
713 x_trip_name =>l_trip_name,
714 x_return_status =>x_return_status
715 );
716 FND_MESSAGE.SET_NAME('FTE','FTE_TRIP_DELETE_ERROR');
717 FND_MESSAGE.SET_TOKEN('TRIP_NAME',l_trip_name);
718 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
719 wsh_util_core.add_message(x_return_status);
720 end if;
721
722 EXCEPTION
723 WHEN others THEN
724 wsh_util_core.default_handler('FTE_TRIPS_PVT.VALIDATE_DELETETRIP');
725 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
726
727 END Validate_DeleteTrip;
728
729
730 -- Trip Segment validation for a Trip
731 PROCEDURE Validate_Trip(
732 p_trip_id IN NUMBER,
733 x_return_status OUT NOCOPY VARCHAR2,
734 x_msg_count OUT NOCOPY NUMBER,
735 x_msg_data OUT NOCOPY VARCHAR2
736 ) IS
737
738 CURSOR trip_segments (l_trip_id IN NUMBER) IS
739 SELECT wsh_trip_id, sequence_number
740 FROM fte_wsh_trips
741 WHERE fte_trip_id = l_trip_id
742 order by sequence_number;
743
744 CURSOR trip_stops (l_wsh_trip_id IN NUMBER) IS
745 select stop_id, stop_location_id, stop_sequence_number
746 from wsh_trip_stops
747 where trip_id=l_wsh_trip_id
748 order by stop_sequence_number;
749
750 l_trip_segments trip_segments%ROWTYPE;
751 l_trip_stops trip_stops%ROWTYPE;
752
753 l_segment_origin NUMBER;
754 l_segment_dest NUMBER;
755 l_trip_segment_name VARCHAR2(100);
756 l_count NUMBER;
757 L_SEGMENT_SEQUENCENUM_NEXT NUMBER;
758 L_SEGMENT_SEQUENCENUM_PREV NUMBER;
759 l_c number;
760 l_trip_name VARCHAR2(30);
761
762
763 cannot_delete_trip EXCEPTION;
764 invalid_connect_segment EXCEPTION;
765 others EXCEPTION;
766
767 BEGIN
768
769 wsh_debug_sv.start_debug ('FteTrip-' || p_trip_id);
770 wsh_debug_sv.dpush (c_sdebug, 'Validate_Trip');
771
772 x_return_Status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
773
774 IF p_trip_id IS NOT NULL THEN
775 l_count:=0;
776 FOR l_trip_segments IN trip_segments(p_trip_id) LOOP
777 l_count:=l_count+1;
778
779 --3. segment number shud be unique (order by seq no and just check
780 -- next seq num.)
781 l_segment_sequencenum_next:=l_trip_segments.sequence_number;
782 if ((l_segment_sequencenum_prev=l_segment_sequencenum_next)) then
783
784 wsh_debug_sv.dlog (c_debug,'segment number unique check');
785
786 UPDATE FTE_TRIPS SET validation_required='Y'
787 where fte_trip_id=p_trip_id;
788
789 IF (SQL%NOTFOUND) THEN
790 RAISE no_data_found;
791 END IF;
792 get_trip_name
793 (
794 p_trip_id =>p_trip_id,
795 x_trip_name =>l_trip_name,
796 x_return_status =>x_return_status
797 );
798 FND_MESSAGE.SET_NAME('FTE','FTE_DUPLICATE_SEQ_NUM');
799 FND_MESSAGE.SET_TOKEN('TRIP_NAME',l_trip_name);
800 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
801 wsh_util_core.add_message(x_return_status);
802
803 wsh_debug_sv.dpop (c_sdebug);
804 wsh_debug_sv.stop_debug;
805
806 FND_MSG_PUB.Count_And_Get
807 ( p_encoded => FND_API.G_FALSE,
808 p_count => x_msg_count
809 ,p_data => x_msg_data
810 );
811 return;
812
813 end if;
814 l_segment_sequencenum_prev:=l_trip_segments.sequence_number;
815
816
817 fte_mls_util.get_first_stop_location_id(
818 P_trip_segment_id =>l_trip_segments.wsh_trip_id,
819 x_trip_segment_name =>l_trip_segment_name,
820 x_first_stop_location_id =>l_segment_origin,
821 x_return_status =>x_return_status
822 );
823
824 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
825 wsh_debug_sv.dlog (c_debug,'could not get 1st stop location');
826 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
827 UPDATE FTE_TRIPS SET validation_required='Y'
828 where fte_trip_id=p_trip_id;
829 IF (SQL%NOTFOUND) THEN
830 RAISE no_data_found;
831 END IF;
832
833 wsh_debug_sv.dpop (c_sdebug);
834 wsh_debug_sv.stop_debug;
835
836 FND_MSG_PUB.Count_And_Get
837 ( p_encoded => FND_API.G_FALSE,
838 p_count => x_msg_count
839 ,p_data => x_msg_data
840 );
841 RETURN;
842
843 END IF;
844
845 open trip_stops(l_trip_segments.wsh_trip_id);
846 LOOP
847 fetch trip_stops into l_trip_stops;
848 EXIT WHEN trip_stops%NOTFOUND;
849 END LOOP;
850
851 --1. segment shud have atleast 2 stops
852 if ((trip_stops%ROWCOUNT<2)) then
853 CLOSE trip_stops;
854 wsh_debug_sv.dlog (c_debug,'segment shud have atleast 2 stops');
855 UPDATE FTE_TRIPS SET validation_required='Y'
856 where fte_trip_id=p_trip_id;
857
858 IF (SQL%NOTFOUND) THEN
859 RAISE no_data_found;
860 END IF;
861 FND_MESSAGE.SET_NAME('FTE','FTE_SEGMENT_NO_TWO_STOPS');
862 FND_MESSAGE.SET_TOKEN('WSH_TRIP_NAME',l_trip_segment_name);
863 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
864 wsh_util_core.add_message(x_return_status);
865
866 wsh_debug_sv.dpop (c_sdebug);
867 wsh_debug_sv.stop_debug;
868 FND_MSG_PUB.Count_And_Get
869 ( p_encoded => FND_API.G_FALSE,
870 p_count => x_msg_count
871 ,p_data => x_msg_data
872 );
873 return;
874 end if;
875 CLOSE trip_stops;
876
877
878
879 --2.origin of 1st segment=dest. of next segment
880 if ((l_count>1) AND (l_segment_dest<>l_segment_origin)) then
881 wsh_debug_sv.dlog (c_debug,'origin of 1st segment!=dest. of next segment');
882 UPDATE FTE_TRIPS SET validation_required='Y'
883 where fte_trip_id=p_trip_id;
884 IF (SQL%NOTFOUND) THEN
885 RAISE no_data_found;
886 END IF;
887
888 get_trip_name
889 (
890 p_trip_id =>p_trip_id,
891 x_trip_name =>l_trip_name,
892 x_return_status =>x_return_status
893 );
894 FND_MESSAGE.SET_NAME('FTE','FTE_INVALID_CONNECT_SEGMENT');
895 FND_MESSAGE.SET_TOKEN('TRIP_NAME',l_trip_name);
896 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
897 wsh_util_core.add_message(x_return_status);
898 wsh_debug_sv.dpop (c_sdebug);
899 wsh_debug_sv.stop_debug;
900
901 FND_MSG_PUB.Count_And_Get
902 ( p_encoded => FND_API.G_FALSE,
903 p_count => x_msg_count
904 ,p_data => x_msg_data
905 );
906 return;
907 end if;
908
909 fte_mls_util.get_last_stop_location_id(
910 P_trip_segment_id =>l_trip_segments.wsh_trip_id,
911 x_trip_segment_name =>l_trip_segment_name,
912 x_last_stop_location_id =>l_segment_dest,
913 x_return_status =>x_return_status
914 );
915
916 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
917 wsh_debug_sv.dlog (c_debug,'could not get last stop location');
918 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
919
920 UPDATE FTE_TRIPS SET validation_required='Y'
921 where fte_trip_id=p_trip_id;
922 IF (SQL%NOTFOUND) THEN
923 RAISE no_data_found;
924 END IF;
925 wsh_debug_sv.dpop (c_sdebug);
926 wsh_debug_sv.stop_debug;
927
928 RETURN;
929 END IF;
930
931
932 END LOOP;
933
934 IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
935 wsh_debug_sv.dlog (c_debug,'validation success');
936 UPDATE FTE_TRIPS SET validation_required='N'
937 where fte_trip_id=p_trip_id;
938 IF (SQL%NOTFOUND) THEN
939 RAISE no_data_found;
940 END IF;
941 END IF;
942 else
943 raise no_data_found;
944 END IF;
945
946 wsh_debug_sv.dlog (c_debug,'Return Status',x_Return_Status);
947 wsh_debug_sv.dpop (c_sdebug);
948 wsh_debug_sv.stop_debug;
949
950 EXCEPTION
951 WHEN no_data_found THEN
952 FND_MESSAGE.SET_NAME('FTE','FTE_TRIP_NOT_FOUND');
953 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
954 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
955 wsh_debug_sv.dpop (c_sdebug);
956 wsh_debug_sv.stop_debug;
957 -- Get message count and data
958 FND_MSG_PUB.Count_And_Get
959 ( p_encoded => FND_API.G_FALSE,
960 p_count => x_msg_count
961 ,p_data => x_msg_data
962 );
963 WHEN others THEN
964 wsh_util_core.default_handler('FTE_TRIPS_PVT.VALIDATE_DELETETRIP');
965 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
966 wsh_debug_sv.dpop (c_sdebug);
967 wsh_debug_sv.stop_debug;
968 -- Get message count and data
969 FND_MSG_PUB.Count_And_Get
970 ( p_encoded => FND_API.G_FALSE,
971 p_count => x_msg_count
972 ,p_data => x_msg_data
973 );
974 END Validate_Trip;
975
976 PROCEDURE get_trip_name
977 (
978 p_trip_id IN NUMBER,
979 x_trip_name OUT NOCOPY VARCHAR2,
980 x_return_status OUT NOCOPY VARCHAR2
981 )
982 IS
983
984 l_trip_name VARCHAR2(32767);
985
986 CURSOR get_trip_cur
987 IS
988 SELECT name
989 FROM fte_trips
990 WHERE fte_trip_id = p_trip_id;
991
992 BEGIN
993
994 l_trip_name := NULL;
995
996 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
997
998 FOR get_trip_rec IN get_trip_cur
999 LOOP
1000 l_trip_name := get_trip_rec.name;
1001 END LOOP;
1002
1003 IF l_trip_name IS NULL
1004 THEN
1005 RAISE NO_DATA_FOUND;
1006 END IF;
1007
1008 x_trip_name := l_trip_name;
1009
1010 EXCEPTION
1011 WHEN OTHERS THEN
1012 wsh_util_core.default_handler('FTE_TRIPS_PVT.GET_TRIP_NAME');
1013 FND_MESSAGE.SET_NAME('FTE','FTE_GET_TRIP_NAME_ERROR');
1014 FND_MESSAGE.SET_TOKEN('TRIP_ID',p_trip_id);
1015 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1016 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1017 END get_trip_name;
1018
1019
1020 -- pass in del ids as a comma seperated list which will
1021 -- be assigned to fte_trip
1022 -- comma seperated list will be of form d100, d101, .. (have to remove
1023 -- "d" before update)
1024
1025 PROCEDURE assign_deliveries_to_ftetrip
1026 (
1027 p_del_ids IN VARCHAR2,
1028 p_fte_trip_id IN NUMBER,
1029 p_wsh_trip_id IN NUMBER,
1030 x_return_status OUT NOCOPY VARCHAR2,
1031 x_msg_count OUT NOCOPY NUMBER,
1032 x_msg_data OUT NOCOPY VARCHAR2
1033 )
1034 IS
1035
1036 l_del_ids dbms_utility.UNCL_ARRAY;
1037 l_del_id VARCHAR2(30);
1038 l_dleg_id NUMBER;
1039 l_noofrows BINARY_INTEGER;
1040
1041 cursor c_dlegs(p_del_id NUMBER) is
1042 select delivery_leg_id
1043 from wsh_delivery_legs wdl, wsh_trip_stops wts
1044 where wdl.pick_up_stop_id = wts.stop_id
1045 and wts.trip_id = p_wsh_trip_id
1046 and wdl.delivery_id=p_del_id;
1047
1048
1049 BEGIN
1050
1051 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1052
1053 dbms_utility.comma_to_table(
1054 list =>p_del_ids,
1055 tablen =>l_noofrows,
1056 tab =>l_del_ids
1057 );
1058
1059
1060
1061 FOR i IN l_del_ids.FIRST..l_del_ids.LAST LOOP
1062 l_del_id:=l_del_ids(i);
1063 l_del_id:=substr(l_del_id,2);
1064
1065 for c_dleg in c_dlegs(l_del_id) loop
1066 l_dleg_id:=c_dleg.delivery_leg_id;
1067
1068 UPDATE wsh_delivery_legs SET fte_trip_id=p_fte_trip_id
1069 where delivery_leg_id=l_dleg_id;
1070
1071 IF (SQL%NOTFOUND) THEN
1072 RAISE no_data_found;
1073 END IF;
1074 end loop;
1075 END LOOP;
1076
1077 EXCEPTION
1078 WHEN no_data_found THEN
1079 FND_MESSAGE.SET_NAME('FTE','FTE_DELIVERY_NOT_FOUND');
1080 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1081 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1082 FND_MSG_PUB.Count_And_Get
1083 ( p_encoded => FND_API.G_FALSE,
1084 p_count => x_msg_count
1085 ,p_data => x_msg_data
1086 );
1087 WHEN others THEN
1088 wsh_util_core.default_handler('FTE_TRIPS_PVT.assign_deliveries_to_ftetrip');
1089 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1090 FND_MSG_PUB.Count_And_Get
1091 ( p_encoded => FND_API.G_FALSE,
1092 p_count => x_msg_count
1093 ,p_data => x_msg_data
1094 );
1095 END assign_deliveries_to_ftetrip;
1096
1097
1098 FUNCTION GET_TRIP_BY_TENDER_NUMBER(p_tender_number NUMBER)
1099 RETURN NUMBER
1100 IS
1101
1102 l_trip_id NUMBER := -9999;
1103
1104 cursor get_trip_cur(c_tender_number NUMBER) is
1105 select trip_id
1106 from wsh_trips
1107 where load_tender_number = c_tender_number;
1108
1109 BEGIN
1110
1111 FOR get_trip_rec IN get_trip_cur(p_tender_number)
1112 LOOP
1113 --{
1114 l_trip_id := get_trip_rec.trip_id;
1115 --}
1116 END LOOP;
1117 -- END OF get trip segment info
1118 --
1119 --
1120 IF get_trip_cur%ISOPEN THEN
1121 CLOSE get_trip_cur;
1122 END IF;
1123
1124 return l_trip_id;
1125
1126 EXCEPTION
1127 WHEN OTHERS THEN
1128 RAISE;
1129 END GET_TRIP_BY_TENDER_NUMBER;
1130 --
1131 --
1132 --
1133 --
1134 PROCEDURE GET_LAST_STOP_LOCATION_INFO
1135 (
1136 P_trip_segment_id IN NUMBER,
1137 x_trip_segment_name IN OUT NOCOPY VARCHAR2,
1138 x_last_stop_location_id OUT NOCOPY NUMBER,
1139 x_return_status OUT NOCOPY VARCHAR2,
1140 x_planned_arvl_dt OUT NOCOPY DATE,
1141 x_planned_dept_dt OUT NOCOPY DATE
1142 )
1143 IS
1144 --{
1145 l_return_status VARCHAR2(32767);
1146 --
1147 --
1148 CURSOR get_last_stop_cur
1149 (
1150 p_trip_id IN NUMBER
1151 )
1152 IS
1153 SELECT stop_id, stop_location_id, stop_sequence_number,
1154 planned_departure_date,planned_arrival_date
1155 FROM wsh_trip_stops
1156 WHERE trip_id = p_trip_id
1157 AND stop_sequence_number = ( select max(stop_sequence_number)
1158 from wsh_trip_stops
1159 where trip_id = p_trip_id);
1160 --}
1161 BEGIN
1162 --{
1163 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1164 --
1165 --
1166 IF x_trip_segment_name IS NULL
1167 THEN
1168 --{
1169 get_trip_segment_name
1170 (
1171 p_trip_segment_id => p_trip_segment_id,
1172 x_trip_segment_name => x_trip_segment_name,
1173 x_return_status => l_return_status
1174 );
1175 --
1176 --
1177 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
1178 THEN
1179 --{
1180 x_return_status := l_return_status;
1181 --
1182 --
1183 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
1184 THEN
1185 --{
1186 RETURN;
1187 --}
1188 END IF;
1189 --}
1190 END IF;
1191 --}
1192 END IF;
1193 --
1194 --
1195 x_last_stop_location_id := NULL;
1196 --
1197 FOR get_last_stop_rec IN get_last_stop_cur ( p_trip_Segment_id )
1198 LOOP
1199 --{
1200 x_last_stop_location_id := get_last_stop_rec.stop_location_id;
1201 x_planned_arvl_dt := get_last_stop_rec.planned_arrival_date;
1202 x_planned_dept_dt := get_last_stop_rec.planned_departure_date;
1203
1204 --}
1205 END LOOP;
1206 --}
1207
1208 EXCEPTION
1209 --{
1210 WHEN OTHERS THEN
1211 wsh_util_core.default_handler('FTE_TRIPS_PVT.GET_LAST_STOP_LOCATION_INFO');
1212 FND_MESSAGE.SET_NAME('FTE','FTE_GET_LAST_STOP_ERROR');
1213 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_NAME',x_trip_segment_name);
1214 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1215 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1216 --}
1217 END GET_LAST_STOP_LOCATION_INFO;
1218 --
1219 --
1220 PROCEDURE GET_FIRST_STOP_LOCATION_INFO
1221 (
1222 P_trip_segment_id IN NUMBER,
1223 x_trip_segment_name IN OUT NOCOPY VARCHAR2,
1224 x_first_stop_location_id OUT NOCOPY NUMBER,
1225 x_return_status OUT NOCOPY VARCHAR2,
1226 x_planned_arvl_dt OUT NOCOPY DATE,
1227 x_planned_dept_dt OUT NOCOPY DATE
1228 )
1229 IS
1230 --{
1231 l_return_status VARCHAR2(32767);
1232 --
1233 --
1234 CURSOR get_first_stop_cur
1235 (
1236 p_trip_id IN NUMBER
1237 )
1238 IS
1239 SELECT stop_id, stop_location_id, stop_sequence_number,
1240 planned_departure_date,planned_arrival_date
1241 FROM wsh_trip_stops
1242 WHERE trip_id = p_trip_id
1243 AND stop_sequence_number = ( select min(stop_sequence_number)
1244 from wsh_trip_stops
1245 where trip_id = p_trip_id);
1246 --}
1247 BEGIN
1248 --{
1249 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1250 --
1251 --
1252 IF x_trip_segment_name IS NULL
1253 THEN
1254 --{
1255 get_trip_segment_name
1256 (
1257 p_trip_segment_id => p_trip_segment_id,
1258 x_trip_segment_name => x_trip_segment_name,
1259 x_return_status => l_return_status
1260 );
1261 --
1262 --
1263 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
1264 THEN
1265 --{
1266 x_return_status := l_return_status;
1267 --
1268 --
1269 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
1270 THEN
1271 --{
1272 RETURN;
1273 --}
1274 END IF;
1275 --}
1276 END IF;
1277 --}
1278 END IF;
1279 --
1280 --
1281 x_first_stop_location_id := NULL;
1282 --
1283 FOR get_first_stop_rec IN get_first_stop_cur( p_trip_Segment_id )
1284 LOOP
1285 --{
1286 x_first_stop_location_id := get_first_stop_rec.stop_location_id;
1287 x_planned_arvl_dt := get_first_stop_rec.planned_arrival_date;
1288 x_planned_dept_dt := get_first_stop_rec.planned_departure_date;
1289
1290 --}
1291 END LOOP;
1292 --}
1293
1294 EXCEPTION
1295 --{
1296 WHEN OTHERS THEN
1297 wsh_util_core.default_handler('FTE_TRIPS_PVT.GET_FIRST_STOP_LOCATION_INFO');
1298 FND_MESSAGE.SET_NAME('FTE','FTE_GET_FIRST_STOP_ERROR');
1299 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_NAME',x_trip_segment_name);
1300 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1301 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1302 --}
1303 END GET_FIRST_STOP_LOCATION_INFO;
1304 --
1305 --
1306 PROCEDURE GET_TRIP_SEGMENT_NAME
1307 (
1308 p_trip_segment_id IN NUMBER,
1309 x_trip_segment_name OUT NOCOPY VARCHAR2,
1310 x_return_status OUT NOCOPY VARCHAR2
1311 )
1312 IS
1313 --{
1314 l_trip_segment_name VARCHAR2(32767);
1315 --
1316 --
1317 CURSOR get_trip_segment_cur
1318 IS
1319 SELECT name
1320 FROM wsh_trips
1321 WHERE trip_id = p_trip_segment_id;
1322 --}
1323 BEGIN
1324 --{
1325 l_trip_segment_name := NULL;
1326 --
1327 FOR get_trip_segment_rec IN get_trip_segment_cur
1328 LOOP
1329 --{
1330 l_trip_segment_name := get_trip_segment_rec.name;
1331 --}
1332 END LOOP;
1333 --
1334 --
1335 IF l_trip_segment_name IS NULL
1336 THEN
1337 RAISE NO_DATA_FOUND;
1338 END IF;
1339 --
1340 --
1341 x_trip_segment_name := l_trip_segment_name;
1342 --
1343 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1344 --}
1345 EXCEPTION
1346 --{
1347 WHEN OTHERS THEN
1348 wsh_util_core.default_handler('FTE_TRIPS_PVT.GET_TRIP_SEGMENT_NAME');
1349 FND_MESSAGE.SET_NAME('FTE','FTE_GET_TRIP_SEG_NAME_ERROR');
1350 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_ID',p_trip_segment_id);
1351 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1352 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1353 --}
1354 END GET_TRIP_SEGMENT_NAME;
1355
1356 --
1357 PROCEDURE GET_SHIPMENT_INFORMATION
1358 (p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1359 p_tender_number IN NUMBER,
1360 x_return_status OUT NOCOPY VARCHAR2,
1361 x_msg_count OUT NOCOPY NUMBER,
1362 x_msg_data OUT NOCOPY VARCHAR2,
1363 x_shipment_info OUT NOCOPY VARCHAR2,
1364 x_shipping_org_name OUT NOCOPY VARCHAR2)
1365 IS
1366
1367 --{
1368 l_api_name CONSTANT VARCHAR2(30) := 'GET_SHIPMENT_INFORMATION';
1369 l_api_version CONSTANT NUMBER := 1.0;
1370
1371 l_trip_id NUMBER;
1372 l_found_first_stop VARCHAR2(1);
1373 l_stop_id NUMBER;
1374
1375 l_init_weight_uom VARCHAR2(10) := NULL;
1376 l_init_volume_uom VARCHAR2(10) := NULL;
1377 l_stop_weight_measure VARCHAR2(20);
1378 l_stop_volume_measure VARCHAR2(20);
1379 l_stop_total_weight NUMBER;
1380 l_stop_total_volume NUMBER;
1381
1382 l_pickup_location_id NUMBER;
1383 l_planned_arrival_date DATE;
1384 l_planned_departure_date DATE;
1385 l_dropoff_location_id NUMBER;
1386 l_pickup_location VARCHAR2(1000);
1387 l_dropoff_location VARCHAR2(1000);
1388
1389 l_stop_count NUMBER;
1390 l_loop_count NUMBER := 1;
1391 l_mssg_text VARCHAR2(1000);
1392
1393 -- Cursor to get trip stop info
1394 --
1395 CURSOR get_trip_stop_info_cur (c_trip_id IN NUMBER)
1396 IS
1397 SELECT departure_gross_weight, weight_uom_code,
1398 departure_volume,volume_uom_code,
1399 wt.unit_of_measure_tl weight, vol.unit_of_measure_tl volume ,
1400 st.stop_id stopid,st.planned_arrival_date, st.planned_departure_date,
1401 st.stop_location_id
1402 FROM wsh_trip_stops st,mtl_units_of_measure wt, mtl_units_of_measure vol
1403 WHERE st.trip_id = c_trip_id
1404 AND wt.UOM_CODE (+)= st.weight_uom_code
1405 AND vol.UOM_CODE (+)= st.volume_uom_code
1406 order by st.stop_sequence_number;
1407 ---
1408 --
1409 -- Cursor to get org info
1410 --
1411 CURSOR get_org_info_cur (c_stop_id IN NUMBER)
1412 IS
1413 SELECT distinct(org.organization_name) org_name
1414 FROM wsh_delivery_legs dlegs, wsh_new_deliveries dlvy,
1415 wsh_trip_stops stops, org_organization_definitions org
1416 WHERE dlegs.delivery_id = dlvy.delivery_id
1417 AND dlegs.pick_up_stop_id = stops.stop_id
1418 AND org.organization_id = dlvy.organization_id
1419 AND stops.stop_id = c_stop_id;
1420 --
1421
1422 --}
1423 BEGIN
1424 --{
1425
1426 -- Get Trip id
1427
1428 SAVEPOINT GET_SHIPMENT_INFORMATION_PUB;
1429
1430 IF FND_API.to_Boolean(p_init_msg_list) THEN
1431 FND_MSG_PUB.initialize;
1432 END IF;
1433
1434 -- Initialize API return status to success
1435 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1436 x_msg_count := 0;
1437 x_msg_data := 0;
1438
1439 l_trip_id := GET_TRIP_BY_TENDER_NUMBER(p_tender_number);
1440
1441 IF (l_trip_id = -9999)
1442 THEN
1443 FND_MESSAGE.SET_NAME('FTE','FTE_INVLD_TEND_NUM_NOTRIP');
1444 FND_MESSAGE.SET_TOKEN('TENDER_NUMBER',p_tender_number);
1445 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1446 RAISE FND_API.G_EXC_ERROR;
1447 END IF;
1448
1449 --get the stop information
1450 -- first get the stop info. get the first stop and find out the
1451 -- deliveries on it. the find the org name from there
1452 --
1453
1454 -- first get the stop count
1455 SELECT count(*) INTO l_stop_count FROM WSH_TRIP_STOPS
1456 WHERE TRIP_ID = l_trip_id;
1457
1458 l_found_first_stop := NULL;
1459
1460 x_shipment_info := NULL;
1461 FOR get_trip_stop_info_rec IN get_trip_stop_info_cur(l_trip_id)
1462 LOOP
1463 --{
1464 l_stop_id := get_trip_stop_info_rec.STOPID;
1465 l_pickup_location_id := get_trip_stop_info_rec.stop_location_id;
1466 l_planned_departure_date := get_trip_stop_info_rec.planned_departure_date;
1467 l_stop_total_weight := get_trip_stop_info_rec.departure_gross_weight;
1468 l_stop_total_volume := get_trip_stop_info_rec.departure_volume;
1469 l_stop_weight_measure := get_trip_stop_info_rec.weight;
1470 l_stop_volume_measure := get_trip_stop_info_rec.volume;
1471
1472 -- now find the org info based on this stop
1473 IF (l_loop_count = 1)
1474 THEN
1475 --IF (l_found_first_stop IS NULL)
1476 --THEN
1477 l_found_first_stop := 'Y';
1478
1479 FOR get_org_info_rec IN get_org_info_cur(l_stop_id)
1480 LOOP
1481 --{
1482 x_shipping_org_name := get_org_info_rec.org_name;
1483
1484 --}
1485 END LOOP;
1486 -- END OF get_org_info
1487 IF get_org_info_cur%ISOPEN THEN
1488 CLOSE get_org_info_cur;
1489 END IF;
1490 END IF;
1491 -- build shipment info
1492 -- Get the weight volume info
1493 --
1494 --
1495 l_pickup_location := NULL;
1496
1497 FTE_MLS_UTIL.get_location_info(l_pickup_location_id,
1498 l_pickup_location,x_return_status);
1499
1500 IF (l_loop_count = 1)
1501 THEN
1502 FND_MESSAGE.SET_NAME('FTE', 'FTE_MLS_TENDER_EMAIL_HDR_PU');
1503 l_mssg_text := FND_MESSAGE.GET;
1504 x_shipment_info := l_mssg_text;
1505
1506 --x_shipment_info := 'Shipment Pickup Information:' || FND_GLOBAL.TAB ||
1507 -- 'Date and Time,' || FND_GLOBAL.TAB ||
1508 -- FND_GLOBAL.TAB || 'Weight,' || FND_GLOBAL.TAB || 'Volume' ||
1509 -- FND_GLOBAL.NEWLINE || 'Pickup Location:';
1510 ELSIF (l_loop_count = l_stop_count)
1511 THEN
1512 FND_MESSAGE.SET_NAME('FTE', 'FTE_MLS_TENDER_EMAIL_HDR_DO');
1513 l_mssg_text := FND_MESSAGE.GET;
1514 x_shipment_info := x_shipment_info || FND_GLOBAL.NEWLINE || l_mssg_text;
1515
1516 --x_shipment_info := x_shipment_info || FND_GLOBAL.NEWLINE ||
1517 -- 'Shipment Delivery Information:' || FND_GLOBAL.TAB ||
1518 -- FND_GLOBAL.TAB || 'Date and Time,' || FND_GLOBAL.TAB ||
1519 -- FND_GLOBAL.TAB || 'Weight,' || FND_GLOBAL.TAB || 'Volume' ||
1520 -- FND_GLOBAL.NEWLINE || 'Dropoff Location:';
1521 ELSIF (l_loop_count < l_stop_count)
1522 THEN
1523 FND_MESSAGE.SET_NAME('FTE', 'FTE_MLS_TENDER_EMAIL_HDR_INT');
1524 l_mssg_text := FND_MESSAGE.GET;
1525 x_shipment_info := x_shipment_info || FND_GLOBAL.NEWLINE ||
1526 l_mssg_text ||' '|| (l_loop_count-1);
1527
1528 --x_shipment_info := x_shipment_info || FND_GLOBAL.NEWLINE ||
1529 -- 'Intermediate Location Information:' || FND_GLOBAL.TAB ||
1530 -- FND_GLOBAL.TAB || 'Date and Time,' || FND_GLOBAL.TAB ||
1531 -- FND_GLOBAL.TAB || 'Weight,' || FND_GLOBAL.TAB || 'Volume' ||
1532 -- FND_GLOBAL.NEWLINE || 'Intermediate Location:' || (l_loop_count-1);
1533 END IF;
1534
1535 x_shipment_info := x_shipment_info || FND_GLOBAL.NEWLINE ||
1536 l_pickup_location || FND_GLOBAL.TAB ||
1537 l_planned_departure_date || FND_GLOBAL.TAB ||
1538 to_char(l_stop_total_weight) || ' ' || l_stop_weight_measure ||
1539 FND_GLOBAL.TAB ||
1540 to_char(l_stop_total_volume) || ' ' || l_stop_volume_measure ||
1541 FND_GLOBAL.NEWLINE;
1542
1543
1544 l_loop_count := l_loop_count+1;
1545 --}
1546 END LOOP;
1547 -- END OF get_trip_stop_info_cur
1548 --
1549 --
1550 IF get_trip_stop_info_cur%ISOPEN THEN
1551 CLOSE get_trip_stop_info_cur;
1552 END IF;
1553 --
1554
1555 --}
1556 EXCEPTION
1557 --{
1558 WHEN FND_API.G_EXC_ERROR THEN
1559 ROLLBACK TO GET_SHIPMENT_INFORMATION_PUB;
1560 x_return_status := FND_API.G_RET_STS_ERROR ;
1561 FND_MSG_PUB.Count_And_Get
1562 (
1563 p_count => x_msg_count,
1564 p_data => x_msg_data,
1565 p_encoded => FND_API.G_FALSE
1566 );
1567 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1568 ROLLBACK TO GET_SHIPMENT_INFORMATION_PUB;
1569 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1570 FND_MSG_PUB.Count_And_Get
1571 (
1572 p_count => x_msg_count,
1573 p_data => x_msg_data,
1574 p_encoded => FND_API.G_FALSE
1575 );
1576 WHEN OTHERS THEN
1577 ROLLBACK TO GET_SHIPMENT_INFORMATION_PUB;
1578 wsh_util_core.default_handler('FTE_TRIPS_PVT.GET_SHIPMENT_INFORMATION');
1579 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1580 FND_MSG_PUB.Count_And_Get
1581 (
1582 p_count => x_msg_count,
1583 p_data => x_msg_data,
1584 p_encoded => FND_API.G_FALSE
1585 );
1586
1587 --}
1588 END GET_SHIPMENT_INFORMATION;
1589 --
1590 --
1591 --
1592 --============================================================================
1593 -- PROCEDURE : Get_Trip_Info_From_Dlvy
1594 --
1595 -- COMMENT : Sums converted weight and volume picked-up on all stops on trip
1596 --============================================================================
1597 PROCEDURE GET_TRIP_INFO_FROM_DLVY
1598 (p_tender_number IN NUMBER,
1599 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1600 x_return_status OUT NOCOPY VARCHAR2,
1601 x_msg_count OUT NOCOPY NUMBER,
1602 x_msg_data OUT NOCOPY VARCHAR2,
1603 x_total_weight OUT NOCOPY NUMBER,
1604 x_weight_uom OUT NOCOPY VARCHAR2,
1605 x_total_volume OUT NOCOPY NUMBER,
1606 x_volume_uom OUT NOCOPY VARCHAR2)
1607 IS
1608
1609 --{
1610 l_api_name CONSTANT VARCHAR2(30) := 'GET_TRIP_INFO_FROM_DLVY';
1611 l_api_version CONSTANT NUMBER := 1.0;
1612
1613 l_trip_id NUMBER := 0;
1614 l_trip_weight_measure VARCHAR2(20);
1615 l_trip_volume_measure VARCHAR2(20);
1616 l_trip_total_weight NUMBER := 0;
1617 l_trip_total_volume NUMBER := 0;
1618
1619 -- Cursor to get total weight and vol info by delivery
1620 CURSOR get_dlvy_weight_vol_cur (c_trip_id IN NUMBER)
1621 IS
1622 SELECT delivery_id, gross_weight, weight_uom_code, volume, volume_uom_code
1623 FROM wsh_new_deliveries
1624 WHERE delivery_id IN
1625 (SELECT distinct(wdl.delivery_id)
1626 FROM wsh_trip_stops wts, --t
1627 wsh_delivery_legs wdl --d
1628 WHERE wdl.pick_up_stop_id = wts.stop_id
1629 AND wts.trip_id = l_trip_id);
1630 --}
1631
1632 BEGIN
1633 --{
1634 -- Get Trip id
1635
1636 SAVEPOINT GET_TRIP_INFO_FROM_DLVY_PUB;
1637
1638 IF FND_API.to_Boolean( p_init_msg_list )
1639 THEN
1640 FND_MSG_PUB.initialize;
1641 END IF;
1642
1643 -- Initialize API return status to success
1644 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1645 x_msg_count := 0;
1646 x_msg_data := 0;
1647
1648
1649 l_trip_id := GET_TRIP_BY_TENDER_NUMBER(p_tender_number);
1650
1651 IF (l_trip_id = -9999)
1652
1653 THEN
1654 FND_MESSAGE.SET_NAME('FTE','FTE_INVLD_TEND_NUM_NOTRIP');
1655 FND_MESSAGE.SET_TOKEN('TENDER_NUMBER',p_tender_number);
1656 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1657 RAISE FND_API.G_EXC_ERROR;
1658 END IF;
1659
1660 l_trip_weight_measure := NULL;
1661 l_trip_volume_measure := NULL;
1662
1663 -- get the weight volume information from the delivery
1664 FOR get_dlvy_weight_vol_rec IN get_dlvy_weight_vol_cur(l_trip_id)
1665 LOOP
1666 --{
1667
1668 -- set preferred uom to first delivery found
1669 IF (l_trip_weight_measure IS NULL
1670 AND get_dlvy_weight_vol_rec.weight_uom_code IS NOT NULL)
1671 THEN
1672 l_trip_weight_measure := get_dlvy_weight_vol_rec.weight_uom_code;
1673 END IF;
1674
1675 IF (l_trip_volume_measure IS NULL
1676 AND get_dlvy_weight_vol_rec.volume_uom_code IS NOT NULL )
1677 THEN
1678 l_trip_volume_measure := get_dlvy_weight_vol_rec.volume_uom_code;
1679 END IF;
1680
1681 -- convert then sum weight, volume
1682 IF (get_dlvy_weight_vol_rec.gross_weight IS NOT NULL
1683 AND get_dlvy_weight_vol_rec.weight_uom_code IS NOT NULL)
1684 THEN
1685 l_trip_total_weight := l_trip_total_weight +
1686 WSH_WV_UTILS.convert_uom(get_dlvy_weight_vol_rec.weight_uom_code,
1687 l_trip_weight_measure,
1688 get_dlvy_weight_vol_rec.gross_weight,
1689 0); -- Within same UOM class
1690
1691 END IF;
1692
1693 IF (get_dlvy_weight_vol_rec.volume IS NOT NULL
1694 AND get_dlvy_weight_vol_rec.volume_uom_code IS NOT NULL )
1695 THEN
1696
1697 l_trip_total_volume := l_trip_total_volume +
1698 WSH_WV_UTILS.convert_uom(get_dlvy_weight_vol_rec.volume_uom_code,
1699 l_trip_volume_measure,
1700 get_dlvy_weight_vol_rec.volume,
1701 0); -- Within same UOM class
1702 END IF;
1703
1704 --};
1705
1706 END LOOP;
1707
1708 -- END OF get_dlvy_weight_vol_cur
1709
1710 x_total_weight := l_trip_total_weight;
1711 x_total_volume := l_trip_total_volume;
1712 x_weight_uom := l_trip_weight_measure;
1713 x_volume_uom := l_trip_volume_measure;
1714
1715 --dbms_output.put_line(' weight: ' || x_total_weight || x_weight_uom || ' - volume: ' || x_total_volume || x_volume_uom );
1716
1717 --
1718 --
1719 IF get_dlvy_weight_vol_cur%ISOPEN THEN
1720 CLOSE get_dlvy_weight_vol_cur;
1721 END IF;
1722 --
1723
1724 --}
1725 --
1726 EXCEPTION
1727 --{
1728 WHEN FND_API.G_EXC_ERROR THEN
1729 ROLLBACK TO GET_TRIP_INFO_FROM_DLVY_PUB;
1730 x_return_status := FND_API.G_RET_STS_ERROR ;
1731 FND_MSG_PUB.Count_And_Get
1732 (
1733 p_count => x_msg_count,
1734 p_data => x_msg_data,
1735 p_encoded => FND_API.G_FALSE
1736 );
1737 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1738 ROLLBACK TO GET_TRIP_INFO_FROM_DLVY_PUB;
1739 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1740 FND_MSG_PUB.Count_And_Get
1741 (
1742 p_count => x_msg_count,
1743 p_data => x_msg_data,
1744 p_encoded => FND_API.G_FALSE
1745 );
1746 WHEN OTHERS THEN
1747 ROLLBACK TO GET_TRIP_INFO_FROM_DLVY_PUB;
1748 wsh_util_core.default_handler('FTE_TRIPS_PVT.GET_TRIP_INFO_FROM_DLVY');
1749 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1750 FND_MSG_PUB.Count_And_Get
1751 (
1752 p_count => x_msg_count,
1753 p_data => x_msg_data,
1754 p_encoded => FND_API.G_FALSE
1755 );
1756
1757 --}
1758
1759 END GET_TRIP_INFO_FROM_DLVY;
1760
1761 --
1762 --
1763 --
1764 END FTE_TRIPS_PVT;