[Home] [Help]
PACKAGE BODY: APPS.JTF_LOC_AREAS_PVT
Source
1 PACKAGE BODY JTF_Loc_Areas_PVT AS
2 /* $Header: jtfvloab.pls 120.2 2005/08/18 22:55:16 stopiwal ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'JTF_Loc_Areas_PVT';
5
6 /*****************************************************************************/
7 -- Procedure: create_loc_area
8 --
9 -- History
10 -- 11/22/1999 julou created
11 -------------------------------------------------------------------------------
12 PROCEDURE create_loc_area
13 (
14 p_api_version IN NUMBER,
15 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
16 p_commit IN VARCHAR2 := FND_API.g_false,
17 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
18
19 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
20 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
21 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
22
23 p_loc_area_rec IN loc_area_rec_type,
24 x_loc_area_id OUT NOCOPY /* file.sql.39 change */ NUMBER
25 )
26 IS
27
28 l_api_version CONSTANT NUMBER := 1.0;
29 l_api_name CONSTANT VARCHAR2(30) := 'create_loc_area';
30 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
31 l_return_status VARCHAR2(1);
32 l_loc_area_rec loc_area_rec_type := p_loc_area_rec;
33 l_loc_area_count NUMBER;
34
35 CURSOR c_loc_area_seq IS
36 SELECT JTF_LOC_AREAS_B_S.NEXTVAL
37 FROM DUAL;
38
39 CURSOR c_loc_area_count(loc_area_id IN NUMBER) IS
40 SELECT COUNT(*)
41 FROM JTF_LOC_AREAS_VL
42 WHERE location_area_id = loc_area_id;
43
44 BEGIN
45 -- initialize
46 SAVEPOINT create_loc_area;
47
48 IF FND_API.to_boolean(p_init_msg_list) THEN
49 FND_MSG_PUB.initialize;
50 END IF;
51
52
53 JTF_Utility_PVT.debug_message(l_full_name || ': start');
54
55 IF NOT FND_API.compatible_api_call
56 (
57 l_api_version,
58 p_api_version,
59 l_api_name,
60 g_pkg_name
61 )
62 THEN
63 RAISE FND_API.g_exc_unexpected_error;
64 END IF;
65
66 x_return_status := FND_API.g_ret_sts_success;
67
68 -- validate
69 -- IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
70 JTF_Utility_PVT.debug_message(l_full_name || ': validate');
71
72 validate_loc_area
73 (
74 p_api_version => l_api_version,
75 p_init_msg_list => p_init_msg_list,
76 p_validation_level => p_validation_level,
77 x_return_status => l_return_status,
78 x_msg_count => x_msg_count,
79 x_msg_data => x_msg_data,
80 p_loc_area_rec => l_loc_area_rec
81 );
82
83 IF l_return_status = FND_API.g_ret_sts_error THEN
84 RAISE FND_API.g_exc_error;
85 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
86 RAISE FND_API.g_exc_unexpected_error;
87 END IF;
88 -- END IF;
89
90 -- insert
91 JTF_Utility_PVT.debug_message(l_full_name || ': insert');
92
93 IF l_loc_area_rec.location_area_id IS NULL THEN
94 LOOP
95 OPEN c_loc_area_seq;
96 FETCH c_loc_area_seq INTO l_loc_area_rec.location_area_id;
97 CLOSE c_loc_area_seq;
98
99 OPEN c_loc_area_count(l_loc_area_rec.location_area_id);
100 FETCH c_loc_area_count INTO l_loc_area_count;
101 CLOSE c_loc_area_count;
102
103 EXIT WHEN l_loc_area_count = 0;
104 END LOOP;
105 END IF;
106
107 INSERT INTO JTF_LOC_AREAS_B
108 (
109 location_area_id,
110 last_update_date,
111 last_updated_by,
112 creation_date,
113 created_by,
114 object_version_number,
115 last_update_login,
116 request_id,
117 program_application_id,
118 program_id,
119 program_update_date,
120 location_type_code,
121 start_date_active,
122 end_date_active,
123 location_area_code,
124 orig_system_id,
125 orig_system_ref,
126 parent_location_area_id
127 )
128 VALUES
129 (
130 l_loc_area_rec.location_area_id,
131 SYSDATE,
132 FND_GLOBAL.user_id,
133 SYSDATE,
134 FND_GLOBAL.user_id,
135 1,
136 FND_GLOBAL.conc_login_id,
137 l_loc_area_rec.request_id,
138 l_loc_area_rec.program_application_id,
139 l_loc_area_rec.program_id,
140 l_loc_area_rec.program_update_date,
141 l_loc_area_rec.location_type_code,
142 l_loc_area_rec.start_date_active,
143 l_loc_area_rec.end_date_active,
144 l_loc_area_rec.location_area_code,
145 l_loc_area_rec.orig_system_id,
146 l_loc_area_rec.orig_system_ref,
147 l_loc_area_rec.parent_location_area_id
148 );
149
150 INSERT INTO JTF_LOC_AREAS_TL
151 (
152 location_area_id,
153 language,
154 last_update_date,
155 last_updated_by,
156 creation_date,
157 created_by,
158 last_update_login,
159 source_lang,
160 location_area_name,
161 location_area_description
162 )
163 SELECT
164 l_loc_area_rec.location_area_id,
165 l.language_code,
166 SYSDATE,
167 FND_GLOBAL.user_id,
168 SYSDATE,
169 FND_GLOBAL.user_id,
170 FND_GLOBAL.conc_login_id,
171 USERENV('LANG'),
172 l_loc_area_rec.location_area_name,
173 l_loc_area_rec.location_area_description
174 FROM fnd_languages l
175 WHERE l.installed_flag in ('I', 'B')
176 AND NOT EXISTS
177 (
178 SELECT NULL
179 FROM JTF_LOC_AREAS_TL t
180 WHERE t.location_area_id = l_loc_area_rec.location_area_id
181 AND t.language = l.language_code
182 );
183
184 -- finish
185 x_loc_area_id := l_loc_area_rec.location_area_id;
186
187 IF FND_API.to_boolean(p_commit) THEN
188 COMMIT;
189 END IF;
190
191 FND_MSG_PUB.count_and_get
192 (
193 p_encoded => FND_API.g_false,
194 p_count => x_msg_count,
195 p_data => x_msg_data
196 );
197
198 JTF_Utility_PVT.debug_message(l_full_name||': end');
199
200 EXCEPTION
201
202 WHEN FND_API.g_exc_error THEN
203 ROLLBACK TO create_loc_area;
204 x_return_status := FND_API.g_ret_sts_error;
205 FND_MSG_PUB.count_and_get
206 (
207 p_encoded => FND_API.g_false,
208 p_count => x_msg_count,
209 p_data => x_msg_data
210 );
211
212 WHEN FND_API.g_exc_unexpected_error THEN
213 ROLLBACK TO create_loc_area;
214 x_return_status := FND_API.g_ret_sts_unexp_error;
215 FND_MSG_PUB.count_and_get
216 (
217 p_encoded => FND_API.g_false,
218 p_count => x_msg_count,
219 p_data => x_msg_data
220 );
221
222 WHEN OTHERS THEN
223 ROLLBACK TO create_loc_area;
224 x_return_status :=FND_API.g_ret_sts_unexp_error;
225 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
226 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
227 END IF;
228 FND_MSG_PUB.count_and_get
229 (
230 p_encoded => FND_API.g_false,
231 p_count => x_msg_count,
232 p_data => x_msg_data
233 );
234
235 END create_loc_area;
236
237
238 /*****************************************************************************/
239 -- Procedure: update_loc_area
240 --
241 -- History
242 -- 11/22/1999 julou created
243 -------------------------------------------------------------------------------
244 PROCEDURE update_loc_area
245 (
246 p_api_version IN NUMBER,
247 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
248 p_commit IN VARCHAR2 := FND_API.g_false,
249 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
250
251 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
252 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
253 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
254
255 p_loc_area_rec IN loc_area_rec_type,
256 p_remove_flag IN VARCHAR2 := 'N'
257 )
258 IS
259
260 l_api_version CONSTANT NUMBER := 1.0;
261 l_api_name CONSTANT VARCHAR2(30) := 'update_loc_area';
262 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
263 l_return_status VARCHAR2(1);
264 l_loc_area_rec loc_area_rec_type := p_loc_area_rec;
265
266 BEGIN
267
268 -- initialize
269 SAVEPOINT update_loc_area;
270
271 IF FND_API.to_boolean(p_init_msg_list) THEN
272 FND_MSG_PUB.initialize;
273 END IF;
274
275 JTF_Utility_PVT.debug_message(l_full_name || ': start');
276
277 IF NOT FND_API.compatible_api_call
278 (
279 l_api_version,
280 p_api_version,
281 l_api_name,
282 g_pkg_name
283 )
284 THEN
285 RAISE FND_API.g_exc_unexpected_error;
286 END IF;
287
288 x_return_status := FND_API.g_ret_sts_success;
289
290 -- complete record
291 complete_loc_area_rec
292 (
293 p_loc_area_rec,
294 l_loc_area_rec
295 );
296
297 -- validate
298 IF p_remove_flag <> 'Y' THEN
299 -- item level
300 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
301 JTF_Utility_PVT.debug_message(l_full_name || ': validate');
302
303 check_items
304 (
305 p_validation_mode => JTF_PLSQL_API.g_update,
306 x_return_status => l_return_status,
307 p_loc_area_rec => l_loc_area_rec
308 );
309
310 IF l_return_status = FND_API.g_ret_sts_error THEN
311 RAISE FND_API.g_exc_error;
312 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
313 RAISE FND_API.g_exc_unexpected_error;
314 END IF;
315 END IF;
316
317 -- record level
318 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
319 JTF_Utility_PVT.debug_message(l_full_name||': check record');
320 check_record
321 (
322 p_loc_area_rec => p_loc_area_rec,
323 p_complete_rec => l_loc_area_rec,
324 x_return_status => l_return_status
325 );
326
327 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
328 RAISE FND_API.g_exc_unexpected_error;
329 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
330 RAISE FND_API.g_exc_error;
331 END IF;
332 END IF;
333 END IF;
334
335 -- update
336 JTF_Utility_PVT.debug_message(l_full_name||': update');
337
338 IF p_remove_flag = 'Y' THEN
339 l_loc_area_rec.end_date_active := SYSDATE;
340 END IF;
341
342 UPDATE JTF_LOC_AREAS_B SET
343 last_update_date = SYSDATE,
344 last_updated_by = FND_GLOBAL.user_id,
345 object_version_number = l_loc_area_rec.object_version_number + 1,
346 last_update_login = FND_GLOBAL.conc_login_id,
347 request_id = l_loc_area_rec.request_id,
348 program_application_id = l_loc_area_rec.program_application_id,
349 program_id = l_loc_area_rec.program_id,
350 program_update_date = l_loc_area_rec.program_update_date,
351 location_type_code = l_loc_area_rec.location_type_code,
352 start_date_active = l_loc_area_rec.start_date_active,
353 end_date_active = l_loc_area_rec.end_date_active,
354 location_area_code = l_loc_area_rec.location_area_code,
355 orig_system_id = l_loc_area_rec.orig_system_id,
356 orig_system_ref = l_loc_area_rec.orig_system_ref,
357 parent_location_area_id = l_loc_area_rec.parent_location_area_id
358 WHERE location_area_id = l_loc_area_rec.location_area_id
359 AND object_version_number = l_loc_area_rec.object_version_number;
360
361 IF (SQL%NOTFOUND) THEN
362 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
363 FND_MESSAGE.set_name('JTF', 'JTF_API_RECORD_NOT_FOUND');
364 FND_MSG_PUB.add;
365 END IF;
366 RAISE FND_API.g_exc_error;
367 END IF;
368
369 UPDATE JTF_LOC_AREAS_TL SET
370 last_update_date = SYSDATE,
371 last_updated_by = FND_GLOBAL.user_id,
372 last_update_login = FND_GLOBAL.conc_login_id,
373 source_lang = USERENV('LANG'),
374 location_area_name = l_loc_area_rec.location_area_name,
375 location_area_description = l_loc_area_rec.location_area_description
376 WHERE location_area_id = l_loc_area_rec.location_area_id
377 AND USERENV('LANG') IN (language, source_lang);
378
379 IF (SQL%NOTFOUND) THEN
380 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
381 FND_MESSAGE.set_name('JTF', 'JTF_API_RECORD_NOT_FOUND');
382 FND_MSG_PUB.add;
383 END IF;
384 RAISE FND_API.g_exc_error;
385 END IF;
386
387 -- finish
388 IF FND_API.to_boolean(p_commit) THEN
389 COMMIT;
390 END IF;
391
392 FND_MSG_PUB.count_and_get
393 (
394 P_ENCODED => FND_API.g_false,
395 p_count => x_msg_count,
396 p_data => x_msg_data
397 );
398
399 JTF_Utility_PVT.debug_message(l_full_name || ': end');
400
401 EXCEPTION
402
403 WHEN FND_API.g_exc_error THEN
404 ROLLBACK TO update_loc_area;
405 x_return_status := FND_API.g_ret_sts_error;
406 FND_MSG_PUB.count_and_get
407 (
408 p_encoded => FND_API.g_false,
409 p_count => x_msg_count,
410 p_data => x_msg_data
411 );
412
413 WHEN FND_API.g_exc_unexpected_error THEN
414 ROLLBACK TO update_loc_area;
415 x_return_status := FND_API.g_ret_sts_unexp_error;
416 FND_MSG_PUB.count_and_get
417 (
418 p_encoded => FND_API.g_false,
419 p_count => x_msg_count,
420 p_data => x_msg_data
421 );
422
423 WHEN OTHERS THEN
424 ROLLBACK TO update_loc_area;
425 x_return_status :=FND_API.g_ret_sts_unexp_error;
426 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
427 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
428 END IF;
429 FND_MSG_PUB.count_and_get
430 (
431 p_encoded => FND_API.g_false,
432 p_count => x_msg_count,
433 p_data => x_msg_data
434 );
435
436 END update_loc_area;
437
438
439 /*****************************************************************************/
440 -- Procedure: delete_loc_area
441 --
442 -- History
443 -- 11/22/1999 julou created
444 -- 24-APR-2001 julou updating the end_date_active to current date,
445 -- instead of deleting the record
446 -------------------------------------------------------------------------------
447 PROCEDURE delete_loc_area
448 (
449 p_api_version IN NUMBER,
450 P_init_msg_list IN VARCHAR2 := FND_API.g_false,
451 p_commit IN VARCHAR2 := FND_API.g_false,
452
453 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
454 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
455 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
456
457 p_loc_area_id IN NUMBER,
458 p_object_version IN NUMBER
459 )
460 IS
461
465 l_child_exist NUMBER := NULL;
462 l_api_version CONSTANT NUMBER := 1.0;
463 l_api_name CONSTANT VARCHAR2(30) := 'delete_loc_area';
464 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
466
467 CURSOR c_child_exist(l_id NUMBER) IS
468 SELECT 1
469 FROM DUAL
470 WHERE EXISTS(SELECT 1
471 FROM jtf_loc_areas_b
472 WHERE nvl(end_date_active,SYSDATE + 1) > SYSDATE
473 AND parent_location_area_id = l_id);
474
475 BEGIN
476 -- initialize
477 SAVEPOINT delete_loc_area;
478
479 JTF_Utility_PVT.debug_message(l_full_name || ': start');
480
481 IF FND_API.to_boolean(p_init_msg_list) THEN
482 FND_MSG_PUB.initialize;
483 END IF;
484
485 IF NOT FND_API.compatible_api_call
486 (
487 l_api_version,
488 p_api_version,
489 l_api_name,
490 g_pkg_name
491 )
492 THEN
493 RAISE FND_API.g_exc_unexpected_error;
494 END IF;
495
496 x_return_status := FND_API.g_ret_sts_success;
497
498 -- delete
499 JTF_Utility_PVT.debug_message(l_full_name || ': delete');
500
501 OPEN c_child_exist(p_loc_area_id);
502 FETCH c_child_exist INTO l_child_exist;
503 CLOSE c_child_exist;
504
505 -- check if the area has child
506 IF l_child_exist IS NOT NULL
507 THEN
508 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
509 FND_MESSAGE.set_name('JTF', 'JTF_LOC_AREA_HAS_CHILD');
510 FND_MSG_PUB.add;
511 END IF;
512 RAISE FND_API.g_exc_error;
513 END IF;
514
515 /* removed by julou for bug 1717907/1703508
516 DELETE FROM JTF_LOC_AREAS_TL
517 WHERE location_area_id = p_loc_area_id;
518
519 IF (SQL%NOTFOUND) THEN
520 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
521 FND_MESSAGE.set_name('JTF', 'JTF_API_RECORD_NOT_FOUND');
522 FND_MSG_PUB.add;
523 END IF;
524 RAISE FND_API.g_exc_error;
525 END IF;
526
527 DELETE FROM JTF_LOC_AREAS_B
528 WHERE location_area_id = p_loc_area_id
529 AND object_version_number = p_object_version;
530 */
531
532 -- added by julou 24-APR-2001 for bug 1717907/1703508
533 UPDATE jtf_loc_areas_b
534 SET last_update_date = SYSDATE
535 ,last_updated_by = FND_GLOBAL.user_id
536 ,last_update_login = FND_GLOBAL.conc_login_id
537 ,end_date_active = SYSDATE
538 ,object_version_number = object_version_number + 1
539 WHERE location_area_id = p_loc_area_id
540 AND object_version_number = p_object_version;
541 -- end of added
542
543 IF (SQL%NOTFOUND) THEN
544 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
545 FND_MESSAGE.set_name('JTF', 'JTF_API_RECORD_NOT_FOUND');
546 FND_MSG_PUB.add;
547 END IF;
548 RAISE FND_API.g_exc_error;
549 END IF;
550
551 -- finish
552 IF FND_API.to_boolean(p_commit) THEN
553 COMMIT;
554 END IF;
555
556 FND_MSG_PUB.count_and_get
557 (
558 P_ENCODED => FND_API.g_false,
559 p_count => x_msg_count,
560 p_data => x_msg_data
561 );
562
563 JTF_Utility_PVT.debug_message(l_full_name || ': end');
564
565 EXCEPTION
566
567 WHEN FND_API.g_exc_error THEN
568 ROLLBACK TO delete_loc_area;
569 x_return_status := FND_API.g_ret_sts_error;
570 FND_MSG_PUB.count_and_get
571 (
572 p_encoded => FND_API.g_false,
573 p_count => x_msg_count,
574 p_data => x_msg_data
575 );
576
577 WHEN FND_API.g_exc_unexpected_error THEN
578 ROLLBACK TO delete_loc_area;
579 x_return_status := FND_API.g_ret_sts_unexp_error;
580 FND_MSG_PUB.count_and_get
581 (
582 p_encoded => FND_API.g_false,
583 p_count => x_msg_count,
584 p_data => x_msg_data
585 );
586
587 WHEN OTHERS THEN
588 ROLLBACK TO delete_loc_area;
589 x_return_status :=FND_API.g_ret_sts_unexp_error;
590 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
591 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
592 END IF;
593 FND_MSG_PUB.count_and_get
594 (
595 p_encoded => FND_API.g_false,
596 p_count => x_msg_count,
597 p_data => x_msg_data
598 );
599
600 END delete_loc_area;
601
602
603 /*****************************************************************************/
604 -- Procedure: lock_loc_area
605 --
606 -- History
607 -- 11/22/1999 julou created
608 -------------------------------------------------------------------------------
609 PROCEDURE lock_loc_area
610 (
611 p_api_version IN NUMBER,
612 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
613
614 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
615 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
616 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
617
621 IS
618 p_loc_area_id IN NUMBER,
619 p_object_version IN NUMBER
620 )
622
623 l_api_version CONSTANT NUMBER := 1.0;
624 l_api_name CONSTANT VARCHAR2(30) := 'lock_loc_area';
625 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
626 l_loc_area_id NUMBER;
627
628 CURSOR c_loc_area_b IS
629 SELECT location_area_id
630 FROM JTF_LOC_AREAS_B
631 WHERE location_area_id = p_loc_area_id
632 AND object_version_number = p_object_version
633 FOR UPDATE OF location_area_id NOWAIT;
634
635 CURSOR c_loc_area_tl IS
636 SELECT location_area_id
637 FROM JTF_LOC_AREAS_TL
638 WHERE location_area_id = p_loc_area_id
639 AND USERENV('LANG') IN (language, source_lang)
640 FOR UPDATE OF location_area_id NOWAIT;
641
642 BEGIN
643 -- initialize
644 JTF_Utility_PVT.debug_message(l_full_name || ': start');
645
646 IF FND_API.to_boolean(p_init_msg_list) THEN
647 FND_MSG_PUB.initialize;
648 END IF;
649
650 IF NOT FND_API.compatible_api_call
651 (
652 l_api_version,
653 p_api_version,
654 l_api_name,
655 g_pkg_name
656 )
657 THEN
658 RAISE FND_API.g_exc_unexpected_error;
659 END IF;
660
661 x_return_status := FND_API.g_ret_sts_success;
662
663 -- lock
664 JTF_Utility_PVT.debug_message(l_full_name || ': lock');
665
666 OPEN c_loc_area_b;
667 FETCH c_loc_area_b INTO l_loc_area_id;
668 IF (c_loc_area_b%NOTFOUND) THEN
669 CLOSE c_loc_area_b;
670 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
671 FND_MESSAGE.set_name('JTF', 'JTF_API_RECORD_NOT_FOUND');
672 FND_MSG_PUB.add;
673 END IF;
674 RAISE FND_API.g_exc_error;
675 END IF;
676 CLOSE c_loc_area_b;
677
678 OPEN c_loc_area_tl;
679 FETCH c_loc_area_tl INTO l_loc_area_id;
680 IF (c_loc_area_tl%NOTFOUND) THEN
681 CLOSE c_loc_area_tl;
682 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
683 FND_MESSAGE.set_name('JTF', 'JTF_API_RECORD_NOT_FOUND');
684 FND_MSG_PUB.add;
685 END IF;
686 RAISE FND_API.g_exc_error;
687 END IF;
688 CLOSE c_loc_area_tl;
689
690 -- finish
691 FND_MSG_PUB.count_and_get
692 (
693 p_encoded => FND_API.g_false,
694 p_count => x_msg_count,
695 p_data => x_msg_data
696 );
697
698 JTF_Utility_PVT.debug_message(l_full_name || ': end');
699
700 EXCEPTION
701
702 WHEN JTF_Utility_PVT.resource_locked THEN
703 x_return_status := FND_API.g_ret_sts_error;
704 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
705 FND_MESSAGE.set_name('JTF', 'JTF_API_RESOURCE_LOCKED');
706 FND_MSG_PUB.add;
707 END IF;
708 FND_MSG_PUB.count_and_get
709 (
710 p_encoded => FND_API.g_false,
711 p_count => x_msg_count,
712 p_data => x_msg_data
713 );
714
715 WHEN FND_API.g_exc_error THEN
716 x_return_status := FND_API.g_ret_sts_error;
717 FND_MSG_PUB.count_and_get
718 (
719 p_encoded => FND_API.g_false,
720 p_count => x_msg_count,
721 p_data => x_msg_data
722 );
723
724 WHEN FND_API.g_exc_unexpected_error THEN
725 x_return_status := FND_API.g_ret_sts_unexp_error;
726 FND_MSG_PUB.count_and_get
727 (
728 p_encoded => FND_API.g_false,
729 p_count => x_msg_count,
730 p_data => x_msg_data
731 );
732
733 WHEN OTHERS THEN
734 x_return_status :=FND_API.g_ret_sts_unexp_error;
735 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
736 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
737 END IF;
738 FND_MSG_PUB.count_and_get
739 (
740 p_encoded => FND_API.g_false,
741 p_count => x_msg_count,
742 p_data => x_msg_data
743 );
744
745 END lock_loc_area;
746
747
748 /*****************************************************************************/
749 -- PROCEDURE
750 -- validate_loc_area
751 --
752 -- HISTORY
753 -- 11/29/99 julou Created.
754 --------------------------------------------------------------------
755 PROCEDURE validate_loc_area
756 (
757 p_api_version IN NUMBER,
758 P_init_msg_list IN VARCHAR2 := FND_API.g_false,
759 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
760
761 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
762 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
763 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
764
765 p_loc_area_rec IN loc_area_rec_type
766 )
767 IS
768
769 l_api_version CONSTANT NUMBER := 1.0;
770 l_api_name CONSTANT VARCHAR2(30) := 'validate_loc_area';
771 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
772
776
773 l_return_status VARCHAR2(1);
774
775 BEGIN
777 ----------------------- initialize --------------------
778 JTF_Utility_PVT.debug_message(l_full_name||': start');
779
780 IF NOT FND_API.compatible_api_call
781 (
782 l_api_version,
783 p_api_version,
784 l_api_name,
785 g_pkg_name
786 )
787 THEN
788 RAISE FND_API.g_exc_unexpected_error;
789 END IF;
790
791 x_return_status := FND_API.g_ret_sts_success;
792
793 ---------------------- validate ------------------------
794 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
795 JTF_Utility_PVT.debug_message(l_full_name||': check items');
796 check_items
797 (
798 p_validation_mode => JTF_PLSQL_API.g_create,
799 x_return_status => l_return_status,
800 p_loc_area_rec => p_loc_area_rec
801 );
802
803 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
804 RAISE FND_API.g_exc_unexpected_error;
805 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
806 RAISE FND_API.g_exc_error;
807 END IF;
808 END IF;
809
810 -- record level
811 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
812 JTF_Utility_PVT.debug_message(l_full_name||': check record');
813 check_record
814 (
815 p_loc_area_rec => p_loc_area_rec,
816 p_complete_rec => p_loc_area_rec,
817 x_return_status => l_return_status
818 );
819
820 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
821 RAISE FND_API.g_exc_unexpected_error;
822 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
823 RAISE FND_API.g_exc_error;
824 END IF;
825 END IF;
826 -------------------- finish --------------------------
827 FND_MSG_PUB.count_and_get
828 (
829 p_encoded => FND_API.g_false,
830 p_count => x_msg_count,
831 p_data => x_msg_data
832 );
833
834 JTF_Utility_PVT.debug_message(l_full_name ||': end');
835
836 EXCEPTION
837 WHEN FND_API.g_exc_error THEN
838 x_return_status := FND_API.g_ret_sts_error;
839 FND_MSG_PUB.count_and_get
840 (
841 p_encoded => FND_API.g_false,
842 p_count => x_msg_count,
843 p_data => x_msg_data
844 );
845
846 WHEN FND_API.g_exc_unexpected_error THEN
847 x_return_status := FND_API.g_ret_sts_unexp_error ;
848 FND_MSG_PUB.count_and_get
849 (
850 p_encoded => FND_API.g_false,
851 p_count => x_msg_count,
852 p_data => x_msg_data
853 );
854
855 WHEN OTHERS THEN
856 x_return_status := FND_API.g_ret_sts_unexp_error;
857 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
858 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
859 END IF;
860
861 FND_MSG_PUB.count_and_get
862 (
863 p_encoded => FND_API.g_false,
864 p_count => x_msg_count,
865 p_data => x_msg_data
866 );
867
868 END validate_loc_area;
869
870 /*****************************************************************************/
871 -- Procedure: check_items
872 --
873 -- History
874 -- 11/22/1999 julou created
875 -------------------------------------------------------------------------------
876 PROCEDURE check_items
877 (
878 p_validation_mode IN VARCHAR2,
879 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
880 p_loc_area_rec IN loc_area_rec_type
881 )
882 IS
883
884 l_api_version CONSTANT NUMBER := 1.0;
885 l_api_name CONSTANT VARCHAR2(30) := 'check_items';
886 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
887
888 BEGIN
889 -- initialize
890 JTF_Utility_PVT.debug_message(l_full_name || ': start');
891
892 x_return_status := FND_API.g_ret_sts_success;
893
894 -- check required items
895 JTF_Utility_PVT.debug_message(l_full_name || ': check required items');
896 check_loc_area_req_items
897 (
898 p_validation_mode => p_validation_mode,
899 p_loc_area_rec => p_loc_area_rec,
900 x_return_status => x_return_status
901 );
902
903 IF x_return_status <> FND_API.g_ret_sts_success THEN
904 RETURN;
905 END IF;
906
907 -- check unique key items
908 JTF_Utility_PVT.debug_message(l_full_name || ': check uk items');
909 check_loc_area_uk_items
910 (
911 p_validation_mode => p_validation_mode,
912 p_loc_area_rec => p_loc_area_rec,
913 x_return_status => x_return_status
914 );
915
916 IF x_return_status <> FND_API.g_ret_sts_success THEN
917 RETURN;
918 END IF;
919
920 -- check foreign key items
921 JTF_Utility_PVT.debug_message(l_full_name || ': check fk items');
922 check_loc_area_fk_items
923 (
924 p_loc_area_rec => p_loc_area_rec,
928 IF x_return_status <> FND_API.g_ret_sts_success THEN
925 x_return_status => x_return_status
926 );
927
929 RETURN;
930 END IF;
931
932 END check_items;
933
934
935 /*****************************************************************************/
936 -- Procedure: check_loc_area_req_items
937 --
938 -- History
939 -- 11/22/1999 julou created
940 -------------------------------------------------------------------------------
941 PROCEDURE check_loc_area_req_items
942 (
943 p_validation_mode IN VARCHAR2,
944 p_loc_area_rec IN loc_area_rec_type,
945 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
946 )
947 IS
948
949 CURSOR c_world_exist IS
950 SELECT 1
951 FROM DUAL
952 WHERE EXISTS(SELECT 1
953 FROM jtf_loc_areas_b
954 WHERE location_type_code = 'AREA1');
955
956 l_world_exist NUMBER;
957
958 BEGIN
959
960 x_return_status := FND_API.g_ret_sts_success;
961
962 -- check location_area_id
963 IF p_loc_area_rec.location_area_id IS NULL
964 AND p_validation_mode = JTF_PLSQL_API.g_update THEN
965 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
966 FND_MESSAGE.set_name('JTF', 'JTF_LOC_AREA_NO_LOC_AREA_ID');
967 FND_MSG_PUB.add;
968 END IF;
969
970 x_return_status := FND_API.g_ret_sts_error;
971 RETURN;
972 END IF;
973
974 -- check location_type_code
975 IF p_loc_area_rec.location_type_code IS NULL THEN
976 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
977 FND_MESSAGE.set_name('JTF', 'JTF_LOC_AREA_NO_LOC_TYPE_CODE');
978 FND_MSG_PUB.add;
979 END IF;
980
981 x_return_status := FND_API.g_ret_sts_error;
982 RETURN;
983 END IF;
984
985 -- check object_version_number
986 IF p_loc_area_rec.object_version_number IS NULL
987 AND p_validation_mode = JTF_PLSQL_API.g_update
988 THEN
989 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
990 FND_MESSAGE.set_name('JTF', 'JTF_API_NO_OBJ_VER_NUM');
991 FND_MSG_PUB.add;
992 END IF;
993
994 x_return_status := FND_API.g_ret_sts_error;
995 RETURN;
996 END IF;
997
998 -- check start_date_active
999 IF p_loc_area_rec.start_date_active IS NULL THEN
1000 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1001 FND_MESSAGE.set_name('JTF', 'JTF_LOC_AREA_NO_START_DATE');
1002 FND_MSG_PUB.add;
1003 END IF;
1004
1005 x_return_status := FND_API.g_ret_sts_error;
1006 RETURN;
1007 END IF;
1008
1009 -- check locaton_area_name
1010 IF p_loc_area_rec.location_area_name IS NULL THEN
1011 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1012 FND_MESSAGE.set_name('JTF', 'JTF_LOC_AREA_NO_AREA_NAME');
1013 FND_MSG_PUB.add;
1014 END IF;
1015
1016 x_return_status := FND_API.g_ret_sts_error;
1017 RETURN;
1018 END IF;
1019
1020 -- check location_area_code
1021 IF p_loc_area_rec.location_area_code IS NULL
1022 OR p_loc_area_rec.location_area_code = FND_API.g_miss_char
1023 THEN
1024 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1025 FND_MESSAGE.set_name('JTF', 'JTF_LOC_AREA_NO_AREA_CODE');
1026 FND_MSG_PUB.add;
1027 END IF;
1028
1029 x_return_status := FND_API.g_ret_sts_error;
1030 RETURN;
1031 END IF;
1032
1033 -- check parent_location_area_id
1034 IF p_loc_area_rec.location_type_code <> 'AREA1' THEN
1035 IF p_loc_area_rec.parent_location_area_id = FND_API.g_miss_num
1036 OR p_loc_area_rec.parent_location_area_id IS NULL
1037 THEN
1038 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1039 FND_MESSAGE.set_name('JTF', 'JTF_LOC_AREA_BAD_PARENT_ID');
1040 FND_MSG_PUB.add;
1041 END IF;
1042
1043 x_return_status := FND_API.g_ret_sts_error;
1044 RETURN;
1045 END IF;
1046 ELSE -- user trying to create world type area, check the existence of world first
1047 OPEN c_world_exist;
1048 FETCH c_world_exist INTO l_world_exist;
1049 CLOSE c_world_exist;
1050
1051 IF l_world_exist IS NOT NULL -- world exists, cant create another world
1052 THEN
1053 IF p_validation_mode = JTF_PLSQL_API.g_create
1054 THEN
1055 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1056 FND_MESSAGE.set_name('JTF', 'JTF_LOC_AREA_WORLD_EXISTS');
1057 FND_MSG_PUB.add;
1058 END IF;
1059
1060 x_return_status := FND_API.g_ret_sts_error;
1061 RETURN;
1062 END IF;
1063 ELSE
1064 IF p_loc_area_rec.parent_location_area_id IS NOT NULL
1065 AND p_loc_area_rec.parent_location_area_id <> FND_API.g_miss_num
1066 THEN -- creating new world, no parent allowed
1067 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1068 FND_MESSAGE.set_name('JTF', 'JTF_LOC_AREA_BAD_PARENT_ID');
1069 FND_MSG_PUB.add;
1070 END IF;
1071
1072 x_return_status := FND_API.g_ret_sts_error;
1073 RETURN;
1074 END IF;
1078 END check_loc_area_req_items;
1075 END IF;
1076 END IF;
1077
1079
1080
1081 /*****************************************************************************/
1082 -- Procedure: check_loc_area_uk_items
1083 --
1084 -- History
1085 -- 11/22/1999 julou created
1086 -------------------------------------------------------------------------------
1087 PROCEDURE check_loc_area_uk_items
1088 (
1089 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1090 p_loc_area_rec IN loc_area_rec_type,
1091 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1092 )
1093 IS
1094
1095 l_uk_flag VARCHAR2(1);
1096 l_parent_loc_str VARCHAR2(60);
1097 l_count NUMBER := NULL;
1098
1099 CURSOR c_area_name_count1(l_id NUMBER, l_parent_id NUMBER, l_name VARCHAR2) IS
1100 SELECT 1
1101 FROM DUAL
1102 WHERE EXISTS(SELECT 1
1103 FROM jtf_loc_areas_b b, jtf_loc_areas_tl t
1104 WHERE t.location_area_name = l_name
1105 AND t.language = USERENV('LANG')
1106 AND t.location_area_id <> l_id
1107 AND b.parent_location_area_id = l_parent_id
1108 AND b.location_area_id =t.location_area_id);
1109
1110 CURSOR c_area_name_count2(l_parent_id NUMBER, l_name VARCHAR2) IS
1111 SELECT 1
1112 FROM DUAL
1113 WHERE EXISTS(SELECT 1
1114 FROM jtf_loc_areas_b b, jtf_loc_areas_tl t
1115 WHERE t.location_area_name = l_name
1116 AND t.language = USERENV('LANG')
1117 AND b.parent_location_area_id = l_parent_id
1118 AND b.location_area_id =t.location_area_id);
1119
1120 BEGIN
1121
1122 x_return_status := FND_API.g_ret_sts_success;
1123
1124 -- check PK, if location_area_id is passed in, must check if it is duplicate
1125 IF p_validation_mode = JTF_PLSQL_API.g_create
1126 AND p_loc_area_rec.location_area_id IS NOT NULL
1127 THEN
1128 l_uk_flag := JTF_Utility_PVT.check_uniqueness
1129 (
1130 'JTF_LOC_AREAS_VL',
1131 'location_area_id = ' || p_loc_area_rec.location_area_id
1132 );
1133 END IF;
1134
1135 IF l_uk_flag = FND_API.g_false THEN
1136 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1137 FND_MESSAGE.set_name('JTF', 'JTF_LOC_AREAS_DUPLICATE_ID');
1138 FND_MSG_PUB.add;
1139 END IF;
1140
1141 x_return_status := FND_API.g_ret_sts_error;
1142 RETURN;
1143 END IF;
1144
1145 -- 07/09/2001 julou modified where clause for parent_location_area_id
1146 -- check location_area_code
1147 IF p_loc_area_rec.parent_location_area_id = FND_API.g_miss_num
1148 OR p_loc_area_rec.parent_location_area_id IS NULL
1149 THEN
1150 l_parent_loc_str := ' AND parent_location_area_id = NULL';
1151 ELSE
1152 l_parent_loc_str := ' AND parent_location_area_id = ' || p_loc_area_rec.parent_location_area_id;
1153 END IF;
1154
1155 IF p_loc_area_rec.location_area_id IS NOT NULL THEN
1156 l_uk_flag := JTF_Utility_PVT.check_uniqueness
1157 (
1158 'JTF_LOC_AREAS_VL',
1159 'location_area_id <> ' || p_loc_area_rec.location_area_id
1160 || ' AND location_area_code = ''' || p_loc_area_rec.location_area_code || ''''
1161 || l_parent_loc_str
1162 );
1163 ELSE
1164 l_uk_flag := JTF_Utility_PVT.check_uniqueness
1165 (
1166 'JTF_LOC_AREAS_VL',
1167 'location_area_code = ''' || p_loc_area_rec.location_area_code || ''''
1168 || l_parent_loc_str
1169 );
1170 END IF;
1171
1172 IF l_uk_flag = FND_API.g_false THEN
1173 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1174 FND_MESSAGE.set_name('JTF', 'JTF_LOC_AREA_DUP_AREA_CODE');
1175 FND_MSG_PUB.add;
1176 END IF;
1177
1178 x_return_status := FND_API.g_ret_sts_error;
1179 RETURN;
1180 END IF;
1181
1182
1183 -- check location_area_name, language
1184
1185 IF p_loc_area_rec.location_area_id IS NOT NULL THEN
1186 OPEN c_area_name_count1(p_loc_area_rec.location_area_id, p_loc_area_rec.parent_location_area_id, p_loc_area_rec.location_area_name);
1187 FETCH c_area_name_count1 INTO l_count;
1188 CLOSE c_area_name_count1;
1189 ELSE
1190 OPEN c_area_name_count2(p_loc_area_rec.parent_location_area_id, p_loc_area_rec.location_area_name);
1191 FETCH c_area_name_count2 INTO l_count;
1192 CLOSE c_area_name_count2;
1193 END IF;
1194
1195 IF l_count = 1 THEN
1196 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1197 FND_MESSAGE.set_name('JTF', 'JTF_LOC_AREAS_DUP_NAME_LANG');
1198 FND_MSG_PUB.add;
1199 END IF;
1200
1201 x_return_status := FND_API.g_ret_sts_error;
1202 RETURN;
1203 END IF;
1204
1205 END check_loc_area_uk_items;
1206
1207
1208 /*****************************************************************************/
1209 -- Procedure: check_loc_area_fk_items
1210 --
1211 -- History
1212 -- 11/22/1999 julou created
1216 p_loc_area_rec IN loc_area_rec_type,
1213 -------------------------------------------------------------------------------
1214 PROCEDURE check_loc_area_fk_items
1215 (
1217 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1218 )
1219 IS
1220
1221 l_fk_flag VARCHAR2(1);
1222
1223 BEGIN
1224
1225 x_return_status := FND_API.g_ret_sts_success;
1226
1227 -- check location_type_code
1228 l_fk_flag := JTF_Utility_PVT.check_fk_exists
1229 (
1230 'JTF_LOC_TYPES_VL',
1231 'location_type_code',
1232 p_loc_area_rec.location_type_code,
1233 2 -- varchar2 type
1234 );
1235
1236 IF l_fk_flag = FND_API.g_false THEN
1237 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1238 FND_MESSAGE.set_name('JTF', 'JTF_LOC_AREA_BAD_LOC_TYPE_CODE');
1239 FND_MSG_PUB.add;
1240 END IF;
1241
1242 x_return_status := FND_API.g_ret_sts_error;
1243 RETURN;
1244 END IF;
1245
1246 -- check parent_location_area_id
1247 IF p_loc_area_rec.parent_location_area_id IS NOT NULL
1248 AND p_loc_area_rec.parent_location_area_id <> FND_API.g_miss_num
1249 THEN
1250 l_fk_flag := JTF_Utility_PVT.check_fk_exists
1251 (
1252 'JTF_LOC_AREAS_VL',
1253 'location_area_id',
1254 p_loc_area_rec.parent_location_area_id
1255 );
1256
1257 IF l_fk_flag = FND_API.g_false THEN
1258 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1259 FND_MESSAGE.set_name('JTF', 'JTF_LOC_AREA_BAD_PARENT_ID');
1260 FND_MSG_PUB.add;
1261 END IF;
1262
1263 x_return_status := FND_API.g_ret_sts_error;
1264 RETURN;
1265 END IF;
1266 END IF;
1267
1268 END check_loc_area_fk_items;
1269
1270
1271 /*****************************************************************************/
1272 -- PROCEDURE
1273 -- check_record
1274 --
1275 -- HISTORY
1276 -- 12/23/99 julou Created.
1277 -------------------------------------------------------------------------------
1278 PROCEDURE check_record
1279 (
1280 p_loc_area_rec IN loc_area_rec_type,
1281 p_complete_rec IN loc_area_rec_type,
1282 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1283 )
1284 IS
1285
1286 l_start_date DATE;
1287 l_end_date DATE;
1288
1289 BEGIN
1290
1291 x_return_status := FND_API.g_ret_sts_success;
1292
1293 -- check that start_date_active <= end_date_active
1294 IF p_complete_rec.start_date_active <> FND_API.g_miss_date
1295 AND p_complete_rec.end_date_active <> FND_API.g_miss_date
1296 AND p_complete_rec.end_date_active IS NOT NULL
1297 THEN
1298 l_start_date := p_complete_rec.start_date_active;
1299 l_end_date := p_complete_rec.end_date_active;
1300 IF l_start_date > l_end_date THEN
1301 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1302 FND_MESSAGE.set_name('JTF', 'JTF_START_DATE_AFTER_END_DATE');
1303 FND_MSG_PUB.add;
1304 END IF;
1305
1306 x_return_status := FND_API.g_ret_sts_error;
1307 RETURN;
1308 END IF;
1309 END IF;
1310
1311 END check_record;
1312
1313
1314 /*****************************************************************************/
1315 -- Procedure: complete_loc_area_rec
1316 --
1317 -- History
1318 -- 11/22/1999 julou created
1319 -------------------------------------------------------------------------------
1320 PROCEDURE complete_loc_area_rec
1321 (
1322 p_loc_area_rec IN loc_area_rec_type,
1323 x_complete_rec OUT NOCOPY /* file.sql.39 change */ loc_area_rec_type
1324 )
1325 IS
1326
1327 CURSOR c_loc_area IS
1328 SELECT * FROM JTF_LOC_AREAS_VL
1329 WHERE location_area_id = p_loc_area_rec.location_area_id;
1330
1331 l_loc_area_rec c_loc_area%ROWTYPE;
1332
1333 BEGIN
1334
1335 x_complete_rec := p_loc_area_rec;
1336
1337 OPEN c_loc_area;
1338 FETCH c_loc_area INTO l_loc_area_rec;
1339 IF (c_loc_area%NOTFOUND) THEN
1340 CLOSE c_loc_area;
1341 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1342 FND_MESSAGE.set_name('JTF', 'JTF_API_RECORD_NOT_FOUND');
1343 FND_MSG_PUB.add;
1344 END IF;
1345 RAISE FND_API.g_exc_error;
1346 END IF;
1347 CLOSE c_loc_area;
1348
1349 IF p_loc_area_rec.request_id = FND_API.g_miss_num THEN
1350 x_complete_rec.request_id := l_loc_area_rec.request_id;
1351 END IF;
1352
1353 IF p_loc_area_rec.program_application_id = FND_API.g_miss_num THEN
1354 x_complete_rec.program_application_id := l_loc_area_rec.program_application_id;
1355 END IF;
1356
1357 IF p_loc_area_rec.program_id = FND_API.g_miss_num THEN
1358 x_complete_rec.program_id := l_loc_area_rec.program_id;
1359 END IF;
1360
1361 IF p_loc_area_rec.program_update_date = FND_API.g_miss_date THEN
1362 x_complete_rec.program_update_date := l_loc_area_rec.program_update_date;
1363 END IF;
1364
1365 IF p_loc_area_rec.location_type_code = FND_API.g_miss_char THEN
1366 x_complete_rec.location_type_code := l_loc_area_rec.location_type_code;
1367 END IF;
1368
1369 IF p_loc_area_rec.start_date_active = FND_API.g_miss_date THEN
1370 x_complete_rec.start_date_active := l_loc_area_rec.start_date_active;
1371 END IF;
1372
1373 IF p_loc_area_rec.end_date_active = FND_API.g_miss_date THEN
1374 x_complete_rec.end_date_active := l_loc_area_rec.end_date_active;
1375 END IF;
1376
1377 IF p_loc_area_rec.location_area_code = FND_API.g_miss_char THEN
1378 x_complete_rec.location_area_code := l_loc_area_rec.location_area_code;
1379 END IF;
1380
1381 IF p_loc_area_rec.location_area_name = FND_API.g_miss_char THEN
1382 x_complete_rec.location_area_name := l_loc_area_rec.location_area_name;
1383 END IF;
1384
1385 IF p_loc_area_rec.location_area_description = FND_API.g_miss_char THEN
1386 x_complete_rec.location_area_description := l_loc_area_rec.location_area_description;
1387 END IF;
1388
1389 IF p_loc_area_rec.orig_system_id = FND_API.g_miss_num THEN
1390 x_complete_rec.orig_system_id := l_loc_area_rec.orig_system_id;
1391 END IF;
1392
1393 IF p_loc_area_rec.orig_system_ref = FND_API.g_miss_char THEN
1394 x_complete_rec.orig_system_ref := l_loc_area_rec.orig_system_ref;
1395 END IF;
1396
1397 IF p_loc_area_rec.parent_location_area_id = FND_API.g_miss_num THEN
1398 x_complete_rec.parent_location_area_id := l_loc_area_rec.parent_location_area_id;
1399 END IF;
1400
1401 END complete_loc_area_rec;
1402
1403
1404 /****************************************************************************/
1405 -- Procedure
1406 -- init_rec
1407 --
1408 -- HISTORY
1409 -- 12/19/1999 julou Created.
1410 ------------------------------------------------------------------------------
1411 PROCEDURE init_rec
1412 (
1413 x_loc_area_rec OUT NOCOPY /* file.sql.39 change */ loc_area_rec_type
1414 )
1415 IS
1416
1417 BEGIN
1418
1419 x_loc_area_rec.location_area_id := FND_API.g_miss_num;
1420 x_loc_area_rec.last_update_date := FND_API.g_miss_date;
1421 x_loc_area_rec.last_updated_by := FND_API.g_miss_num;
1422 x_loc_area_rec.creation_date := FND_API.g_miss_date;
1423 x_loc_area_rec.created_by := FND_API.g_miss_num;
1424 x_loc_area_rec.last_update_login := FND_API.g_miss_num;
1425 x_loc_area_rec.object_version_number := FND_API.g_miss_num;
1426 x_loc_area_rec.request_id := FND_API.g_miss_num;
1427 x_loc_area_rec.program_application_id := FND_API.g_miss_num;
1428 x_loc_area_rec.program_id := FND_API.g_miss_num;
1429 x_loc_area_rec.program_update_date := FND_API.g_miss_date;
1430 x_loc_area_rec.location_type_code := FND_API.g_miss_char;
1431 x_loc_area_rec.start_date_active := FND_API.g_miss_date;
1432 x_loc_area_rec.end_date_active := FND_API.g_miss_date;
1433 x_loc_area_rec.location_area_code := FND_API.g_miss_char;
1434 x_loc_area_rec.orig_system_id := FND_API.g_miss_num;
1435 x_loc_area_rec.orig_system_ref := FND_API.g_miss_char;
1436 x_loc_area_rec.parent_location_area_id := FND_API.g_miss_num;
1437 x_loc_area_rec.location_area_name := FND_API.g_miss_char;
1438 x_loc_area_rec.location_area_description := FND_API.g_miss_char;
1439
1440 END init_rec;
1441
1442 END JTF_Loc_Areas_PVT;