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