[Home] [Help]
PACKAGE BODY: APPS.JTF_LOC_POSTAL_CODES_PVT
Source
1 PACKAGE BODY JTF_Loc_Postal_Codes_PVT AS
2 /* $Header: jtfvlopb.pls 120.2 2005/08/18 22:55:35 stopiwal ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'JTF_Loc_Postal_Codes_PVT';
5
6 /*****************************************************************************/
7 -- Procedure: create_postal_code
8 --
9 -- History
10 -- 12/23/1999 julou created
11 -------------------------------------------------------------------------------
12 PROCEDURE create_postal_code
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_postal_code_rec IN postal_code_rec_type,
24 x_postal_code_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_postal_code';
30 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
31 l_return_status VARCHAR2(1);
32 l_postal_code_rec postal_code_rec_type := p_postal_code_rec;
33 l_postal_code_count NUMBER;
34
35 CURSOR c_postal_code_seq IS
36 SELECT JTF_LOC_POSTAL_CODES_S.NEXTVAL
37 FROM DUAL;
38
39 CURSOR c_postal_code_count(postal_code_id IN NUMBER) IS
40 SELECT COUNT(*)
41 FROM JTF_LOC_POSTAL_CODES
42 WHERE location_postal_code_id = postal_code_id;
43
44 BEGIN
45 -- initialize
46 SAVEPOINT create_postal_code;
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 JTF_Utility_PVT.debug_message(l_full_name || ': validate');
69
70 validate_postal_code
71 (
72 p_api_version => l_api_version,
73 p_init_msg_list => p_init_msg_list,
74 p_validation_level => p_validation_level,
75 x_return_status => l_return_status,
76 x_msg_count => x_msg_count,
77 x_msg_data => x_msg_data,
78 p_postal_code_rec => l_postal_code_rec
79 );
80
81 IF l_return_status = FND_API.g_ret_sts_error THEN
82 RAISE FND_API.g_exc_error;
83 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
84 RAISE FND_API.g_exc_unexpected_error;
85 END IF;
86
87 -- generate an unique ID if it is not parsed in
88 IF l_postal_code_rec.location_postal_code_id IS NULL THEN
89 LOOP
90 OPEN c_postal_code_seq;
91 FETCH c_postal_code_seq INTO l_postal_code_rec.location_postal_code_id;
92 CLOSE c_postal_code_seq;
93
94 OPEN c_postal_code_count(l_postal_code_rec.location_postal_code_id);
95 FETCH c_postal_code_count INTO l_postal_code_count;
96 CLOSE c_postal_code_count;
97
98 EXIT WHEN l_postal_code_count = 0;
99 END LOOP;
100 END IF;
101
102 -- insert
103 JTF_Utility_PVT.debug_message(l_full_name || ': insert');
104
105 INSERT INTO JTF_LOC_POSTAL_CODES
106 (
107 location_postal_code_id,
108 last_update_date,
109 last_updated_by,
110 creation_date,
111 created_by,
112 last_update_login,
113 object_version_number,
114 orig_system_ref,
115 orig_system_id,
116 location_area_id,
117 start_date_active,
118 end_date_active,
119 postal_code_start,
120 postal_code_end
121 )
122 VALUES
123 (
124 l_postal_code_rec.location_postal_code_id,
125 SYSDATE,
126 FND_GLOBAL.user_id,
127 SYSDATE,
128 FND_GLOBAL.user_id,
129 FND_GLOBAL.conc_login_id,
130 1,
131 l_postal_code_rec.orig_system_ref,
132 l_postal_code_rec.orig_system_id,
133 l_postal_code_rec.location_area_id,
134 l_postal_code_rec.start_date_active,
135 l_postal_code_rec.end_date_active,
136 l_postal_code_rec.postal_code_start,
137 l_postal_code_rec.postal_code_end
138 );
139
140 -- finish
141 x_postal_code_id := l_postal_code_rec.location_postal_code_id;
142
143 IF FND_API.to_boolean(p_commit) THEN
144 COMMIT;
145 END IF;
146
147 FND_MSG_PUB.count_and_get
148 (
149 p_encoded => FND_API.g_false,
150 p_count => x_msg_count,
151 p_data => x_msg_data
152 );
153
154 JTF_Utility_PVT.debug_message(l_full_name||': end');
155
156 EXCEPTION
157
158 WHEN FND_API.g_exc_error THEN
159 ROLLBACK TO create_postal_code;
160 x_return_status := FND_API.g_ret_sts_error;
161 FND_MSG_PUB.count_and_get
162 (
163 p_encoded => FND_API.g_false,
164 p_count => x_msg_count,
165 p_data => x_msg_data
166 );
167
168 WHEN FND_API.g_exc_unexpected_error THEN
169 ROLLBACK TO create_postal_code;
170 x_return_status := FND_API.g_ret_sts_unexp_error;
171 FND_MSG_PUB.count_and_get
172 (
173 p_encoded => FND_API.g_false,
174 p_count => x_msg_count,
175 p_data => x_msg_data
176 );
177
178 WHEN OTHERS THEN
179 ROLLBACK TO create_postal_code;
180 x_return_status :=FND_API.g_ret_sts_unexp_error;
181 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
182 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
183 END IF;
184 FND_MSG_PUB.count_and_get
185 (
186 p_encoded => FND_API.g_false,
187 p_count => x_msg_count,
188 p_data => x_msg_data
189 );
190
191 END create_postal_code;
192
193
194 /*****************************************************************************/
195 -- Procedure: update_postal_code
196 --
197 -- History
198 -- 12/23/1999 julou created
199 -------------------------------------------------------------------------------
200 PROCEDURE update_postal_code
201 (
202 p_api_version IN NUMBER,
203 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
204 p_commit IN VARCHAR2 := FND_API.g_false,
205 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
206
207 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
208 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
209 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
210
211 p_postal_code_rec IN postal_code_rec_type,
212 p_remove_flag IN VARCHAR2 := 'N'
213 )
214 IS
215
216 l_api_version CONSTANT NUMBER := 1.0;
217 l_api_name CONSTANT VARCHAR2(30) := 'update_postal_code';
218 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
219 l_return_status VARCHAR2(1);
220 l_postal_code_rec postal_code_rec_type := p_postal_code_rec;
221
222 BEGIN
223
224 -- initialize
225 SAVEPOINT update_postal_code;
226
227 IF FND_API.to_boolean(p_init_msg_list) THEN
228 FND_MSG_PUB.initialize;
229 END IF;
230
231 JTF_Utility_PVT.debug_message(l_full_name || ': start');
232
233 IF NOT FND_API.compatible_api_call
234 (
235 l_api_version,
236 p_api_version,
237 l_api_name,
238 g_pkg_name
239 )
240 THEN
241 RAISE FND_API.g_exc_unexpected_error;
242 END IF;
243
244 x_return_status := FND_API.g_ret_sts_success;
245
246 -- complete the record
247 complete_rec
248 (
249 p_postal_code_rec,
250 l_postal_code_rec
251 );
252
253 IF p_remove_flag <> 'Y' THEN
254 -- item level
255 JTF_Utility_PVT.debug_message(l_full_name || ': validate');
256 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
257 JTF_Utility_PVT.debug_message(l_full_name || ': check items');
258 check_items
259 (
260 p_validation_mode => JTF_PLSQL_API.g_update,
261 x_return_status => l_return_status,
262 p_postal_code_rec => l_postal_code_rec
263 );
264
265 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
266 RAISE FND_API.g_exc_unexpected_error;
267 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
268 RAISE FND_API.g_exc_error;
269 END IF;
270 END IF;
271
272 -- record level
273 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
274 JTF_Utility_PVT.debug_message(l_full_name||': check record');
275 check_record
276 (
277 p_postal_code_rec => p_postal_code_rec,
278 p_complete_rec => l_postal_code_rec,
279 x_return_status => l_return_status
280 );
281
282 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
283 RAISE FND_API.g_exc_unexpected_error;
284 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
285 RAISE FND_API.g_exc_error;
286 END IF;
287 END IF;
288 END IF;
289
290 -- update
291 JTF_Utility_PVT.debug_message(l_full_name||': update');
292
293 IF p_remove_flag = 'Y' THEN
294 UPDATE JTF_LOC_POSTAL_CODES SET
295 end_date_active = SYSDATE
296 WHERE location_postal_code_id = l_postal_code_rec.location_postal_code_id
297 AND object_version_number = l_postal_code_rec.object_version_number;
298 ELSE
299 UPDATE JTF_LOC_POSTAL_CODES SET
300 last_update_date = SYSDATE,
301 last_updated_by = FND_GLOBAL.user_id,
302 object_version_number = l_postal_code_rec.object_version_number + 1,
303 last_update_login = FND_GLOBAL.conc_login_id,
304 orig_system_ref = l_postal_code_rec.orig_system_ref,
305 orig_system_id = l_postal_code_rec.orig_system_id,
306 location_area_id = l_postal_code_rec.location_area_id,
307 start_date_active = l_postal_code_rec.start_date_active,
308 end_date_active = l_postal_code_rec.end_date_active,
309 postal_code_start = l_postal_code_rec.postal_code_start,
310 postal_code_end = l_postal_code_rec.postal_code_end
311 WHERE location_postal_code_id = l_postal_code_rec.location_postal_code_id
312 AND object_version_number = l_postal_code_rec.object_version_number;
313 END IF;
314
315 IF (SQL%NOTFOUND) THEN
316 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
317 FND_MESSAGE.set_name('JTF', 'JTF_API_RECORD_NOT_FOUND');
318 FND_MSG_PUB.add;
319 END IF;
320 RAISE FND_API.g_exc_error;
321 END IF;
322
323 -- finish
324 IF FND_API.to_boolean(p_commit) THEN
325 COMMIT;
326 END IF;
327
328 FND_MSG_PUB.count_and_get
329 (
330 P_ENCODED => FND_API.g_false,
331 p_count => x_msg_count,
332 p_data => x_msg_data
333 );
334
335 JTF_Utility_PVT.debug_message(l_full_name || ': end');
336
337 EXCEPTION
338
339 WHEN FND_API.g_exc_error THEN
340 ROLLBACK TO update_postal_code;
341 x_return_status := FND_API.g_ret_sts_error;
342 FND_MSG_PUB.count_and_get
343 (
344 p_encoded => FND_API.g_false,
345 p_count => x_msg_count,
346 p_data => x_msg_data
347 );
348
349 WHEN FND_API.g_exc_unexpected_error THEN
350 ROLLBACK TO update_postal_code;
351 x_return_status := FND_API.g_ret_sts_unexp_error;
352 FND_MSG_PUB.count_and_get
353 (
354 p_encoded => FND_API.g_false,
355 p_count => x_msg_count,
356 p_data => x_msg_data
357 );
358
359 WHEN OTHERS THEN
360 ROLLBACK TO update_postal_code;
361 x_return_status :=FND_API.g_ret_sts_unexp_error;
362 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
363 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
364 END IF;
365 FND_MSG_PUB.count_and_get
366 (
367 p_encoded => FND_API.g_false,
368 p_count => x_msg_count,
369 p_data => x_msg_data
370 );
371
372 END update_postal_code;
373
374
375 /*****************************************************************************/
376 -- Procedure: delete_postal_code
377 --
378 -- History
379 -- 12/23/1999 julou created
380 -------------------------------------------------------------------------------
381 PROCEDURE delete_postal_code
382 (
383 p_api_version IN NUMBER,
384 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
385 p_commit IN VARCHAR2 := FND_API.g_false,
386
387 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
388 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
389 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
390
391 p_postal_code_id IN NUMBER,
392 p_object_version IN NUMBER
393 )
394 IS
395
396 l_api_version CONSTANT NUMBER := 1.0;
397 l_api_name CONSTANT VARCHAR2(30) := 'delete_postal_code';
398 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
399
400 BEGIN
401 -- initialize
402 SAVEPOINT delete_postal_code;
403
404 IF FND_API.to_boolean(p_init_msg_list) THEN
405 FND_MSG_PUB.initialize;
406 END IF;
407
408 JTF_Utility_PVT.debug_message(l_full_name || ': start');
409
410 IF NOT FND_API.compatible_api_call
411 (
412 l_api_version,
413 p_api_version,
414 l_api_name,
415 g_pkg_name
416 )
417 THEN
418 RAISE FND_API.g_exc_unexpected_error;
419 END IF;
420
421 x_return_status := FND_API.g_ret_sts_success;
422
423 -- delete
424 JTF_Utility_PVT.debug_message(l_full_name || ': delete');
425
426 DELETE FROM JTF_LOC_POSTAL_CODES
427 WHERE location_postal_code_id = p_postal_code_id
428 AND object_version_number = p_object_version;
429
430 IF (SQL%NOTFOUND) THEN
431 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
432 FND_MESSAGE.set_name('JTF', 'JTF_API_RECORD_NOT_FOUND');
433 FND_MSG_PUB.add;
434 END IF;
435 RAISE FND_API.g_exc_error;
436 END IF;
437
438 -- finish
439 IF FND_API.to_boolean(p_commit) THEN
440 COMMIT;
441 END IF;
442
443 FND_MSG_PUB.count_and_get
444 (
445 P_ENCODED => FND_API.g_false,
446 p_count => x_msg_count,
447 p_data => x_msg_data
448 );
449
450 JTF_Utility_PVT.debug_message(l_full_name || ': end');
451
452 EXCEPTION
453
454 WHEN FND_API.g_exc_error THEN
455 ROLLBACK TO delete_postal_code;
456 x_return_status := FND_API.g_ret_sts_error;
457 FND_MSG_PUB.count_and_get
458 (
459 p_encoded => FND_API.g_false,
460 p_count => x_msg_count,
461 p_data => x_msg_data
462 );
463
464 WHEN FND_API.g_exc_unexpected_error THEN
465 ROLLBACK TO delete_postal_code;
466 x_return_status := FND_API.g_ret_sts_unexp_error;
467 FND_MSG_PUB.count_and_get
468 (
469 p_encoded => FND_API.g_false,
470 p_count => x_msg_count,
471 p_data => x_msg_data
472 );
473
474 WHEN OTHERS THEN
475 ROLLBACK TO delete_postal_code;
476 x_return_status :=FND_API.g_ret_sts_unexp_error;
477 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
478 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
479 END IF;
480 FND_MSG_PUB.count_and_get
481 (
482 p_encoded => FND_API.g_false,
483 p_count => x_msg_count,
484 p_data => x_msg_data
485 );
486
487 END delete_postal_code;
488
489
490 /*****************************************************************************/
491 -- Procedure: lock_postal_code
492 --
493 -- History
494 -- 12/23/1999 julou created
495 -------------------------------------------------------------------------------
496 PROCEDURE lock_postal_code
497 (
498 p_api_version IN NUMBER,
499 P_init_msg_list IN VARCHAR2 := FND_API.g_false,
500
501 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
502 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
503 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
504
505 p_postal_code_id IN NUMBER,
506 p_object_version IN NUMBER
507 )
508 IS
509
510 l_api_version CONSTANT NUMBER := 1.0;
511 l_api_name CONSTANT VARCHAR2(30) := 'lock_postal_code';
512 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
513 l_postal_code_id NUMBER;
514
515 CURSOR c_postal_code IS
516 SELECT location_postal_code_id
517 FROM JTF_LOC_POSTAL_CODES
518 WHERE location_postal_code_id = p_postal_code_id
519 AND object_version_number = p_object_version
520 FOR UPDATE OF location_postal_code_id NOWAIT;
521
522 BEGIN
523 -- initialize
524 JTF_Utility_PVT.debug_message(l_full_name || ': start');
525
526 IF FND_API.to_boolean(p_init_msg_list) THEN
527 FND_MSG_PUB.initialize;
528 END IF;
529
530 IF NOT FND_API.compatible_api_call
531 (
532 l_api_version,
533 p_api_version,
534 l_api_name,
535 g_pkg_name
536 )
537 THEN
538 RAISE FND_API.g_exc_unexpected_error;
539 END IF;
540
541 x_return_status := FND_API.g_ret_sts_success;
542
543 -- lock
544 JTF_Utility_PVT.debug_message(l_full_name || ': lock');
545
546 OPEN c_postal_code;
547 FETCH c_postal_code INTO l_postal_code_id;
548 IF (c_postal_code%NOTFOUND) THEN
549 CLOSE c_postal_code;
550 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
551 FND_MESSAGE.set_name('JTF', 'JTF_API_RECORD_NOT_FOUND');
552 FND_MSG_PUB.add;
553 END IF;
554 RAISE FND_API.g_exc_error;
555 END IF;
556 CLOSE c_postal_code;
557
558 -- finish
559 FND_MSG_PUB.count_and_get
560 (
561 p_encoded => FND_API.g_false,
562 p_count => x_msg_count,
563 p_data => x_msg_data
564 );
565
566 JTF_Utility_PVT.debug_message(l_full_name || ': end');
567
568 EXCEPTION
569
570 WHEN JTF_Utility_PVT.resource_locked THEN
571 x_return_status := FND_API.g_ret_sts_error;
572 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
573 FND_MESSAGE.set_name('JTF', 'JTF_API_RESOURCE_LOCKED');
574 FND_MSG_PUB.add;
575 END IF;
576 FND_MSG_PUB.count_and_get
577 (
578 p_encoded => FND_API.g_false,
579 p_count => x_msg_count,
580 p_data => x_msg_data
581 );
582
583 WHEN FND_API.g_exc_error THEN
584 x_return_status := FND_API.g_ret_sts_error;
585 FND_MSG_PUB.count_and_get
586 (
587 p_encoded => FND_API.g_false,
588 p_count => x_msg_count,
589 p_data => x_msg_data
590 );
591
592 WHEN FND_API.g_exc_unexpected_error THEN
593 x_return_status := FND_API.g_ret_sts_unexp_error;
594 FND_MSG_PUB.count_and_get
595 (
596 p_encoded => FND_API.g_false,
597 p_count => x_msg_count,
598 p_data => x_msg_data
599 );
600
601 WHEN OTHERS THEN
602 x_return_status :=FND_API.g_ret_sts_unexp_error;
603 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
604 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
605 END IF;
606 FND_MSG_PUB.count_and_get
607 (
608 p_encoded => FND_API.g_false,
609 p_count => x_msg_count,
610 p_data => x_msg_data
611 );
612
613 END lock_postal_code;
614
615
619 --
616 /*****************************************************************************/
617 -- PROCEDURE
618 -- validate_postal_code
620 -- HISTORY
621 -- 12/23/99 julou Created.
622 --------------------------------------------------------------------
623 PROCEDURE validate_postal_code
624 (
625 p_api_version IN NUMBER,
626 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
627 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
628
629 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
630 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
631 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
632
633 p_postal_code_rec IN postal_code_rec_type
634 )
635 IS
636
637 l_api_version CONSTANT NUMBER := 1.0;
638 l_api_name CONSTANT VARCHAR2(30) := 'validate_postal_code';
639 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
640
641 l_return_status VARCHAR2(1);
642
643 BEGIN
644
645 ----------------------- initialize --------------------
646 JTF_Utility_PVT.debug_message(l_full_name||': start');
647
648 IF NOT FND_API.compatible_api_call
649 (
650 l_api_version,
651 p_api_version,
652 l_api_name,
653 g_pkg_name
654 )
655 THEN
656 RAISE FND_API.g_exc_unexpected_error;
657 END IF;
658
659 x_return_status := FND_API.g_ret_sts_success;
660
661 ---------------------- validate ------------------------
662 -- item level
663 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
664 JTF_Utility_PVT.debug_message(l_full_name||': check items');
665 check_items
666 (
667 p_validation_mode => JTF_PLSQL_API.g_create,
668 x_return_status => l_return_status,
669 p_postal_code_rec => p_postal_code_rec
670 );
671
672 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
673 RAISE FND_API.g_exc_unexpected_error;
674 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
675 RAISE FND_API.g_exc_error;
676 END IF;
677 END IF;
678
679 -- record level
680 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
681 JTF_Utility_PVT.debug_message(l_full_name||': check record');
682 check_record
683 (
684 p_postal_code_rec => p_postal_code_rec,
685 p_complete_rec => p_postal_code_rec,
686 x_return_status => l_return_status
687 );
688
689 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
690 RAISE FND_API.g_exc_unexpected_error;
691 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
692 RAISE FND_API.g_exc_error;
693 END IF;
694 END IF;
695
696 -------------------- finish --------------------------
697 FND_MSG_PUB.count_and_get
698 (
699 p_encoded => FND_API.g_false,
700 p_count => x_msg_count,
701 p_data => x_msg_data
702 );
703
704 JTF_Utility_PVT.debug_message(l_full_name ||': end');
705
706 EXCEPTION
707 WHEN FND_API.g_exc_error THEN
708 x_return_status := FND_API.g_ret_sts_error;
709 FND_MSG_PUB.count_and_get
710 (
711 p_encoded => FND_API.g_false,
712 p_count => x_msg_count,
713 p_data => x_msg_data
714 );
715
716 WHEN FND_API.g_exc_unexpected_error THEN
717 x_return_status := FND_API.g_ret_sts_unexp_error ;
718 FND_MSG_PUB.count_and_get
719 (
720 p_encoded => FND_API.g_false,
721 p_count => x_msg_count,
722 p_data => x_msg_data
723 );
724
725 WHEN OTHERS THEN
726 x_return_status := FND_API.g_ret_sts_unexp_error;
727 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
728 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
729 END IF;
730
731 FND_MSG_PUB.count_and_get
732 (
733 p_encoded => FND_API.g_false,
734 p_count => x_msg_count,
735 p_data => x_msg_data
736 );
737
738 END validate_postal_code;
739
740
741 /*****************************************************************************/
742 -- Procedure: check_items
743 --
744 -- History
745 -- 12/23/1999 julou created
746 -------------------------------------------------------------------------------
747 PROCEDURE check_items
748 (
749 p_validation_mode IN VARCHAR2,
750 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
751 p_postal_code_rec IN postal_code_rec_type
752 )
753 IS
754
755 l_api_version CONSTANT NUMBER := 1.0;
756 l_api_name CONSTANT VARCHAR2(30) := 'check_items';
757 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
758
759 BEGIN
760 -- initialize
761 JTF_Utility_PVT.debug_message(l_full_name || ': start');
762
763 x_return_status := FND_API.g_ret_sts_success;
764
765 -- check required items
769 p_validation_mode => p_validation_mode,
766 JTF_Utility_PVT.debug_message(l_full_name || ': check required items');
767 check_req_items
768 (
770 p_postal_code_rec => p_postal_code_rec,
771 x_return_status => x_return_status
772 );
773
774 IF x_return_status <> FND_API.g_ret_sts_success THEN
775 RETURN;
776 END IF;
777
778 -- check foreign key items
779 JTF_Utility_PVT.debug_message(l_full_name || ': check fk items');
780 check_fk_items
781 (
782 p_postal_code_rec => p_postal_code_rec,
783 x_return_status => x_return_status
784 );
785
786 IF x_return_status <> FND_API.g_ret_sts_success THEN
787 RETURN;
788 END IF;
789
790 END check_items;
791
792
793 /*****************************************************************************/
794 -- Procedure: check_req_items
795 --
796 -- History
797 -- 12/23/1999 julou created
798 -------------------------------------------------------------------------------
799 PROCEDURE check_req_items
800 (
801 p_validation_mode IN VARCHAR2,
802 p_postal_code_rec IN postal_code_rec_type,
803 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
804 )
805 IS
806
807 BEGIN
808
809 x_return_status := FND_API.g_ret_sts_success;
810
811 -- check location_postal_code_id
812 IF p_postal_code_rec.location_postal_code_id IS NULL
813 AND p_validation_mode = JTF_PLSQL_API.g_update
814 THEN
815 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
816 FND_MESSAGE.set_name('JTF', 'JTF_LOC_POS_NO_LOC_POS_CODE_ID');
817 FND_MSG_PUB.add;
818 END IF;
819
820 x_return_status := FND_API.g_ret_sts_error;
821 RETURN;
822 END IF;
823
824 -- check object_version_number
825 IF p_postal_code_rec.object_version_number IS NULL
826 AND p_validation_mode = JTF_PLSQL_API.g_update
827 THEN
828 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
829 FND_MESSAGE.set_name('JTF', 'JTF_API_NO_OBJ_VER_NUM');
830 FND_MSG_PUB.add;
831 END IF;
832
833 x_return_status := FND_API.g_ret_sts_error;
834 RETURN;
835 END IF;
836
837 -- check location_area_id
838 IF p_postal_code_rec.location_area_id IS NULL THEN
839 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
840 FND_MESSAGE.set_name('JTF', 'JTF_LOC_POS_NO_LOC_AREA_ID');
841 FND_MSG_PUB.add;
842 END IF;
843
844 x_return_status := FND_API.g_ret_sts_error;
845 RETURN;
846 END IF;
847
848 -- check start_date_active
849 IF p_postal_code_rec.start_date_active IS NULL THEN
850 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
851 FND_MESSAGE.set_name('JTF', 'JTF_LOC_POS_NO_START_DATES');
852 FND_MSG_PUB.add;
853 END IF;
854
855 x_return_status := FND_API.g_ret_sts_error;
856 RETURN;
857 END IF;
858
859 END check_req_items;
860
861
862 /*****************************************************************************/
863 -- Procedure: check_fk_items
864 --
865 -- History
866 -- 12/23/1999 julou created
867 -------------------------------------------------------------------------------
868 PROCEDURE check_fk_items
869 (
870 p_postal_code_rec IN postal_code_rec_type,
871 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
872 )
873 IS
874
875 l_fk_flag VARCHAR2(1);
876
877 BEGIN
878
879 x_return_status := FND_API.g_ret_sts_success;
880
881 IF p_postal_code_rec.location_area_id IS NOT NULL THEN
882 l_fk_flag := JTF_Utility_PVT.check_fk_exists
883 (
884 'JTF_LOC_AREAS_VL',
885 'location_area_id',
886 p_postal_code_rec.location_area_id
887 );
888
889 IF l_fk_flag = FND_API.g_false 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_POS_BAD_LOC_AREA_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 END IF;
899
900 END check_fk_items;
901
902
903 /*****************************************************************************/
904 -- PROCEDURE
905 -- check_record
906 --
907 -- HISTORY
908 -- 12/23/99 julou Created.
909 -------------------------------------------------------------------------------
910 PROCEDURE check_record
911 (
912 p_postal_code_rec IN postal_code_rec_type,
913 p_complete_rec IN postal_code_rec_type,
914 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
915 )
916 IS
917
918 l_start_date DATE;
919 l_end_date DATE;
920
921 BEGIN
922
923 x_return_status := FND_API.g_ret_sts_success;
924
925 -- check that start_date_active <= end_date_active
929 THEN
926 IF p_complete_rec.start_date_active <> FND_API.g_miss_date
927 AND p_complete_rec.end_date_active <> FND_API.g_miss_date
928 AND p_complete_rec.end_date_active IS NOT NULL
930 l_start_date := p_complete_rec.start_date_active;
931 l_end_date := p_complete_rec.end_date_active;
932 IF l_start_date > l_end_date THEN
933 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
934 FND_MESSAGE.set_name('JTF', 'JTF_START_DATE_AFTER_END_DATE');
935 FND_MSG_PUB.add;
936 END IF;
937
938 x_return_status := FND_API.g_ret_sts_error;
939 RETURN;
940 END IF;
941 END IF;
942
943 END check_record;
944
945
946 /*****************************************************************************/
947 -- Procedure: complete_rec
948 --
949 -- History
950 -- 12/23/1999 julou created
951 -------------------------------------------------------------------------------
952 PROCEDURE complete_rec
953 (
954 p_postal_code_rec IN postal_code_rec_type,
955 x_complete_rec OUT NOCOPY /* file.sql.39 change */ postal_code_rec_type
956 )
957 IS
958
959 CURSOR c_postal_code IS
960 SELECT * FROM JTF_LOC_POSTAL_CODES
961 WHERE location_postal_code_id = p_postal_code_rec.location_postal_code_id;
962
963 l_postal_code_rec c_postal_code%ROWTYPE;
964
965 BEGIN
966
967 x_complete_rec := p_postal_code_rec;
968
969 OPEN c_postal_code;
970 FETCH c_postal_code INTO l_postal_code_rec;
971 IF (c_postal_code%NOTFOUND) THEN
972 CLOSE c_postal_code;
973 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
974 FND_MESSAGE.set_name('JTF', 'JTF_API_RECORD_NOT_FOUND');
975 FND_MSG_PUB.add;
976 END IF;
977 RAISE FND_API.g_exc_error;
978 END IF;
979 CLOSE c_postal_code;
980
981 IF p_postal_code_rec.orig_system_ref = FND_API.g_miss_char THEN
982 x_complete_rec.orig_system_ref := l_postal_code_rec.orig_system_ref;
983 END IF;
984
985 IF p_postal_code_rec.orig_system_id = FND_API.g_miss_num THEN
986 x_complete_rec.orig_system_id := l_postal_code_rec.orig_system_id;
987 END IF;
988
989 IF p_postal_code_rec.location_area_id = FND_API.g_miss_num THEN
990 x_complete_rec.location_area_id := l_postal_code_rec.location_area_id;
991 END IF;
992
993 IF p_postal_code_rec.start_date_active = FND_API.g_miss_date THEN
994 x_complete_rec.start_date_active := l_postal_code_rec.start_date_active;
995 END IF;
996
997 IF p_postal_code_rec.end_date_active = FND_API.g_miss_date THEN
998 x_complete_rec.end_date_active := l_postal_code_rec.end_date_active;
999 END IF;
1000
1001 IF p_postal_code_rec.postal_code_start = FND_API.g_miss_char THEN
1002 x_complete_rec.postal_code_start := l_postal_code_rec.postal_code_start;
1003 END IF;
1004
1005 IF p_postal_code_rec.postal_code_end = FND_API.g_miss_char THEN
1006 x_complete_rec.postal_code_end := l_postal_code_rec.postal_code_end;
1007 END IF;
1008
1009 END complete_rec;
1010
1011
1012 /****************************************************************************/
1013 -- Procedure
1014 -- init_rec
1015 --
1016 -- HISTORY
1017 -- 12/23/1999 julou Created.
1018 ------------------------------------------------------------------------------
1019 PROCEDURE init_rec
1020 (
1021 x_postal_code_rec OUT NOCOPY /* file.sql.39 change */ postal_code_rec_type
1022 )
1023 IS
1024
1025 BEGIN
1026
1027 x_postal_code_rec.location_postal_code_id := FND_API.g_miss_num;
1028 x_postal_code_rec.last_update_date := FND_API.g_miss_date;
1029 x_postal_code_rec.last_updated_by := FND_API.g_miss_num;
1030 x_postal_code_rec.creation_date := FND_API.g_miss_date;
1031 x_postal_code_rec.created_by := FND_API.g_miss_num;
1032 x_postal_code_rec.last_update_login := FND_API.g_miss_num;
1033 x_postal_code_rec.object_version_number := FND_API.g_miss_num;
1034 x_postal_code_rec.orig_system_ref := FND_API.g_miss_char;
1035 x_postal_code_rec.orig_system_id := FND_API.g_miss_num;
1036 x_postal_code_rec.location_area_id := FND_API.g_miss_num;
1037 x_postal_code_rec.start_date_active := FND_API.g_miss_date;
1038 x_postal_code_rec.end_date_active := FND_API.g_miss_date;
1039 x_postal_code_rec.postal_code_start := FND_API.g_miss_char;
1040 x_postal_code_rec.postal_code_end := FND_API.g_miss_char;
1041
1042 END init_rec;
1043
1044 END JTF_Loc_Postal_Codes_PVT;