[Home] [Help]
PACKAGE BODY: APPS.FTE_TRIP_MOVES_PVT
Source
1 PACKAGE BODY FTE_TRIP_MOVES_PVT AS
2 /* $Header: FTEMTTHB.pls 115.3 2004/05/08 00:26:12 wrudge noship $ */
3
4 --===================
5 -- CONSTANTS
6 --===================
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FTE_TRIP_MOVES_PVT';
8
9
10 --========================================================================
11 -- PROCEDURE : VALIDATE_SEQUENCE
12 --
13 -- PARAMETERS:
14 -- p_trip_moves_info Attributes for the trip moves entity
15 -- x_return_status Return status of API
16 -- COMMENT :
17 --========================================================================
18
19 PROCEDURE Validate_Sequence
20 (
21 p_trip_moves_info IN TRIP_MOVES_REC_TYPE,
22 x_return_status OUT NOCOPY VARCHAR2
23 ) IS
24
25 CURSOR check_sequence_create (v_move_id NUMBER,
26 v_sequence NUMBER) IS
27 SELECT trip_move_id
28 FROM fte_trip_moves
29 WHERE move_id = v_move_id
30 and sequence_number = v_sequence;
31
32 CURSOR check_sequence_update (v_move_id NUMBER,
33 v_sequence NUMBER,
34 v_trip_move_id NUMBER) IS
35 SELECT trip_move_id
36 FROM fte_trip_moves
37 WHERE move_id = v_move_id
38 and sequence_number = v_sequence
39 and trip_move_id <> v_trip_move_id;
40
41 CURSOR check_move_id (v_move_id NUMBER) IS
42 SELECT move_id FROM fte_moves
43 WHERE move_id = v_move_id;
44
45 empty_sequence EXCEPTION;
46 duplicate_sequence EXCEPTION;
47 empty_move EXCEPTION;
48
49 l_number NUMBER;
50 l_found BOOLEAN;
51
52 BEGIN
53
54 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
55
56 --validate sequence
57
58 if (p_trip_moves_info.sequence_number = null
59 or p_trip_moves_info.sequence_number = FND_API.G_MISS_NUM) then
60 RAISE empty_sequence;
61 end if;
62
63 OPEN check_move_id(p_trip_moves_info.move_id);
64 FETCH check_move_id INTO l_number;
65
66 IF (check_move_id%NOTFOUND) THEN
67 CLOSE check_move_id;
68 RAISE empty_move;
69 END IF;
70
71 CLOSE check_move_id;
72
73 IF p_trip_moves_info.trip_move_id IS NULL THEN
74 OPEN check_sequence_create(p_trip_moves_info.move_id, p_trip_moves_info.sequence_number);
75 FETCH check_sequence_create INTO l_number;
76 l_found := check_sequence_create%FOUND;
77 CLOSE check_sequence_create;
78 ELSE
79 OPEN check_sequence_update(p_trip_moves_info.move_id,
80 p_trip_moves_info.sequence_number,
81 p_trip_moves_info.trip_move_id);
82 FETCH check_sequence_update INTO l_number;
83 l_found := check_sequence_update%FOUND;
84 CLOSE check_sequence_update;
85 END IF;
86
87
88 IF l_found THEN
89 RAISE duplicate_sequence;
90 END IF;
91
92 EXCEPTION
93 WHEN empty_move THEN
94 FND_MESSAGE.SET_NAME('FTE', 'FTE_MOVE_MISSING');
95 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
96 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
97 WHEN empty_sequence THEN
98 FND_MESSAGE.SET_NAME('FTE', 'FTE_MOVE_SEQUENCE_MISSING');
99 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
100 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
101 WHEN duplicate_sequence THEN
102 FND_MESSAGE.SET_NAME('FTE', 'FTE_MOVE_SEQUENCE_DUP');
103 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
104 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
105 WHEN others THEN
106 IF check_sequence_create%ISOPEN THEN
107 CLOSE check_sequence_create;
108 END IF;
109 IF check_sequence_update%ISOPEN THEN
110 CLOSE check_sequence_update;
111 END IF;
112 IF check_move_id%ISOPEN THEN
113 CLOSE check_move_id;
114 END IF;
115 wsh_util_core.default_handler('FTE_TRIP_MOVES_PUB.Validate_Sequence');
116 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
117 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
118
119 END Validate_Sequence;
120
121
122 --========================================================================
123 -- PROCEDURE : validate_unique_trip
124 --
125 -- PARAMETERS:
126 -- p_trip_moves_info Attributes for the trip moves entity
127 -- x_return_status Return status of API
128 -- COMMENT :
129 --========================================================================
130
131 PROCEDURE validate_unique_trip
132 (
133 p_trip_moves_info IN TRIP_MOVES_REC_TYPE,
134 x_return_status OUT NOCOPY VARCHAR2
135 ) IS
136
137 CURSOR check_unique_trip_create(v_move_id NUMBER,
138 v_trip_id NUMBER) IS
139 SELECT trip_move_id
140 FROM fte_trip_moves
141 WHERE move_id = v_move_id
142 and trip_id = v_trip_id;
143
144
145 CURSOR check_unique_trip_update(v_move_id NUMBER,
146 v_trip_id NUMBER,
147 v_trip_move_id NUMBER) IS
148 SELECT trip_move_id
149 FROM fte_trip_moves
150 WHERE move_id = v_move_id
151 and trip_id = v_trip_id
152 and trip_move_id <> v_trip_move_id;
153
154 CURSOR check_trip_id (v_trip_id NUMBER) IS
155 SELECT trip_id FROM wsh_trips
156 WHERE trip_id = v_trip_id;
157
158
159 duplicate_trip EXCEPTION;
160 empty_trip EXCEPTION;
161
162 l_number NUMBER;
163 l_found BOOLEAN;
164
165 BEGIN
166
167 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
168
169
170 OPEN check_trip_id(p_trip_moves_info.trip_id);
171 FETCH check_trip_id INTO l_number;
172
173 IF (check_trip_id%NOTFOUND) THEN
174 CLOSE check_trip_id;
175 RAISE empty_trip;
176 END IF;
177
178 CLOSE check_trip_id;
179
180
181 IF p_trip_moves_info.trip_move_id IS NULL THEN
182 OPEN check_unique_trip_create(p_trip_moves_info.move_id,
183 p_trip_moves_info.trip_id);
184 FETCH check_unique_trip_create INTO l_number;
185
186 IF (check_unique_trip_create%FOUND) THEN
187 CLOSE check_unique_trip_create;
188 RAISE duplicate_trip;
189 END IF;
190
191 CLOSE check_unique_trip_create;
192
193 ELSE
194
195 OPEN check_unique_trip_update(p_trip_moves_info.move_id,
196 p_trip_moves_info.trip_id,
197 p_trip_moves_info.trip_move_id);
198 FETCH check_unique_trip_update INTO l_number;
199
200 IF (check_unique_trip_update%FOUND) THEN
201 CLOSE check_unique_trip_update;
202 RAISE duplicate_trip;
203 END IF;
204
205 CLOSE check_unique_trip_update;
206 END IF;
207
208 EXCEPTION
209 WHEN empty_trip THEN
210 FND_MESSAGE.SET_NAME('FTE', 'FTE_TRIP_MISSING');
211 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
212 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
213 WHEN duplicate_trip THEN
214 FND_MESSAGE.SET_NAME('FTE', 'FTE_MOVE_TRIP_DUP');
215 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
216 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
217 WHEN others THEN
218 if check_trip_id%ISOPEN THEN
219 close check_trip_id;
220 end if;
221 if check_unique_trip_create%ISOPEN THEN
222 close check_unique_trip_create;
223 end if;
224 if check_unique_trip_update%ISOPEN THEN
225 close check_unique_trip_update;
226 end if;
227 wsh_util_core.default_handler('FTE_TRIP_MOVES_PUB.validate_unique_trip');
228 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
229 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
230
231 END validate_unique_trip;
232
233
234
235 --========================================================================
236 -- PROCEDURE : CREATE_TRIP_MOVES
237 --
238 -- PARAMETERS: p_init_msg_list
239 -- p_trip_moves_info Attributes for the trip moves entity
240 -- x_return_status Return status of API
241 -- COMMENT :
242 --========================================================================
243
244 PROCEDURE CREATE_TRIP_MOVES
245 (
246 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
247 p_trip_moves_info IN TRIP_MOVES_REC_TYPE,
248 x_trip_move_id OUT NOCOPY NUMBER,
249 x_return_status OUT NOCOPY VARCHAR2
250 ) IS
251
252 CURSOR get_next_trip_move IS
253 SELECT fte_trip_moves_s.nextval
254 FROM sys.dual;
255
256 l_temp_id NUMBER;
257
258 BEGIN
259
260 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
261
262 IF FND_API.to_Boolean( p_init_msg_list )
263 THEN
264 FND_MSG_PUB.initialize;
265 END IF;
266
267 Validate_Sequence
268 (
269 p_trip_moves_info,
270 x_return_status
271 );
272
273 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
274 RETURN;
275 END IF;
276
277 Validate_Unique_trip
278 (
279 p_trip_moves_info,
280 x_return_status
281 );
282
283 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
284 RETURN;
285 END IF;
286
287
288 OPEN get_next_trip_move;
289 FETCH get_next_trip_move INTO x_trip_move_id;
290 CLOSE get_next_trip_move;
291
292 insert into FTE_TRIP_MOVES
293 (
294 TRIP_MOVE_ID ,
295 MOVE_ID ,
296 TRIP_ID ,
297 SEQUENCE_NUMBER ,
298 CREATION_DATE ,
299 CREATED_BY ,
300 LAST_UPDATE_DATE ,
301 LAST_UPDATED_BY ,
302 LAST_UPDATE_LOGIN ,
303 PROGRAM_APPLICATION_ID ,
304 PROGRAM_ID ,
305 PROGRAM_UPDATE_DATE ,
306 REQUEST_ID
307 )
308 values
309 (
310 x_trip_move_id,
311 p_trip_moves_info.MOVE_ID,
312 p_trip_moves_info.TRIP_ID,
313 p_trip_moves_info.SEQUENCE_NUMBER,
314 decode(p_trip_moves_info.creation_date,NULL,SYSDATE,FND_API.G_MISS_DATE,SYSDATE,p_trip_moves_info.creation_date),
315 decode(p_trip_moves_info.created_by,NULL,FND_GLOBAL.USER_ID,FND_API.G_MISS_NUM,FND_GLOBAL.USER_ID,p_trip_moves_info.created_by),
316 decode(p_trip_moves_info.last_update_date,NULL,SYSDATE,FND_API.G_MISS_DATE,SYSDATE, p_trip_moves_info.last_update_date),
317 decode(p_trip_moves_info.last_updated_by,NULL,FND_GLOBAL.USER_ID,FND_API.G_MISS_NUM,FND_GLOBAL.USER_ID,p_trip_moves_info.last_updated_by),
318 decode(p_trip_moves_info.last_update_login,NULL,FND_GLOBAL.USER_ID,FND_API.G_MISS_NUM,FND_GLOBAL.LOGIN_ID,p_trip_moves_info.last_update_login),
319 decode(p_trip_moves_info.program_application_id,NULL,FND_GLOBAL.PROG_APPL_ID,FND_API.G_MISS_NUM,FND_GLOBAL.PROG_APPL_ID,p_trip_moves_info.program_application_id),
320 decode(p_trip_moves_info.program_id,NULL,FND_GLOBAL.CONC_PROGRAM_ID,FND_API.G_MISS_NUM, FND_GLOBAL.CONC_PROGRAM_ID,p_trip_moves_info.program_id),
321 decode(p_trip_moves_info.program_update_date,NULL,SYSDATE,FND_API.G_MISS_DATE,SYSDATE,p_trip_moves_info.program_update_date),
322 decode(p_trip_moves_info.request_id,NULL,FND_GLOBAL.CONC_REQUEST_ID,FND_API.G_MISS_NUM,FND_GLOBAL.CONC_REQUEST_ID, p_trip_moves_info.request_id)
323 );
324
325 EXCEPTION
326 WHEN others THEN
327 wsh_util_core.default_handler('FTE_TRIP_MOVES.CREATE_TRIP_MOVES');
328 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
329 END Create_Trip_Moves;
330
331
332 --========================================================================
333 -- PROCEDURE : Update_Trip_MOVES
334 --
335 -- PARAMETERS: p_trip_info Attributes for the trip entity
336 -- x_return_status Return status of API
337 -- COMMENT : Updates trip record with p_trip_info information
338 --========================================================================
339
340 PROCEDURE Update_Trip_Moves(
341 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
342 p_trip_moves_info IN TRIP_MOVES_REC_TYPE,
343 x_return_status OUT NOCOPY VARCHAR2
344 ) IS
345
346 FTE_DUPLICATE_MOVE exception;
347
348 BEGIN
349
350 IF FND_API.to_Boolean( p_init_msg_list )
351 THEN
352 FND_MSG_PUB.initialize;
353 END IF;
354
355 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
356
357 Validate_Sequence
358 (
359 p_trip_moves_info,
360 x_return_status
361 );
362
363 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
364 RETURN;
365 END IF;
366
367 Validate_Unique_trip
368 (
369 p_trip_moves_info,
370 x_return_status
371 );
372
373 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
374 RETURN;
375 END IF;
376
377
378 UPDATE fte_trip_moves SET
379 SEQUENCE_NUMBER = decode(p_trip_moves_info.SEQUENCE_NUMBER,
380 NULL,SEQUENCE_NUMBER,
381 FND_API.G_MISS_NUM,NULL,
382 p_trip_moves_info.SEQUENCE_NUMBER),
383 last_update_date = decode(p_trip_moves_info.last_update_date,
384 NULL,SYSDATE,
385 FND_API.G_MISS_DATE,SYSDATE,
386 p_trip_moves_info.last_update_date),
387 last_updated_by = decode(p_trip_moves_info.last_updated_by,
388 NULL,FND_GLOBAL.USER_ID,
389 FND_API.G_MISS_NUM,FND_GLOBAL.USER_ID,
390 p_trip_moves_info.last_updated_by),
391 last_update_login = decode(p_trip_moves_info.last_update_login,
392 NULL,FND_GLOBAL.LOGIN_ID,
393 FND_API.G_MISS_NUM, FND_GLOBAL.LOGIN_ID,
394 p_trip_moves_info.last_update_login),
395 program_application_id = decode(p_trip_moves_info.program_application_id,
396 NULL,program_application_id,
397 FND_API.G_MISS_NUM,FND_GLOBAL.PROG_APPL_ID,
398 p_trip_moves_info.program_application_id),
399 program_id = decode(p_trip_moves_info.program_id,
400 NULL,program_id,FND_API.G_MISS_NUM,
401 FND_GLOBAL.CONC_PROGRAM_ID,
402 p_trip_moves_info.program_id),
403 program_update_date = decode(p_trip_moves_info.program_update_date,
404 NULL,program_update_date,
405 FND_API.G_MISS_DATE,SYSDATE,
406 p_trip_moves_info.program_update_date),
407 request_id = decode(p_trip_moves_info.request_id,
408 NULL,request_id,FND_API.G_MISS_NUM,
409 FND_GLOBAL.CONC_REQUEST_ID,p_trip_moves_info.request_id)
410 WHERE trip_move_id = p_trip_moves_info.trip_move_id;
411
412 IF (SQL%NOTFOUND) THEN
413 RAISE no_data_found;
414 END IF;
415
416 EXCEPTION
417 WHEN FTE_DUPLICATE_MOVE THEN
418 FND_MESSAGE.Set_Name('FND', 'FORM_DUPLICATE_KEY_IN_INDEX');
419 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
420 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
421 WHEN no_data_found THEN
422 FND_MESSAGE.SET_NAME('FTE','FTE_TRIP_MOVE_NOT_FOUND');
423 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
424 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
425 WHEN others THEN
426 wsh_util_core.default_handler('FTE_TRIP_MOVES_PVT.UPDATE_TRIP_MOVES');
427 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
428
429 END Update_Trip_Moves;
430
431
432 PROCEDURE Delete_Trip_moves(
433 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
434 p_trip_move_id IN NUMBER,
435 x_return_status OUT NOCOPY VARCHAR2
436 ) IS
437
438
439 BEGIN
440
441 IF FND_API.to_Boolean( p_init_msg_list )
442 THEN
443 FND_MSG_PUB.initialize;
444 END IF;
445
446 x_return_Status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
447
448 DELETE FROM fte_trip_moves
449 WHERE trip_move_id = p_trip_move_id;
450
451 IF (SQL%NOTFOUND) THEN
452 FND_MESSAGE.SET_NAME('FTE','FTE_TRIP_MOVE_NOT_FOUND');
453 FND_MESSAGE.SET_TOKEN('TRIP_MOVE_ID', p_trip_move_id);
454 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
455 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
456 END IF;
457
458 EXCEPTION
459 WHEN others THEN
460 wsh_util_core.default_handler('FTE_TRIP_MOVES_PVT.DELETE_TRIP_MOVES');
461 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
462
463 END Delete_Trip_moves;
464
465 --
466 --
467 END FTE_TRIP_MOVES_PVT;