[Home] [Help]
PACKAGE BODY: APPS.VEA_LAYERPROVIDERS_SV
Source
1 PACKAGE BODY VEA_LAYERPROVIDERS_SV as
2 /* $Header: VEAVALPB.pls 115.14 2003/12/02 01:31:08 rvishnuv noship $ */
3 --{
4 /*======================== vea_layerproviders_sv =============================*/
5 /*========================================================================
6 PURPOSE: Table handler package for table VEA_LAYERPROVIDERS
7
8 NOTES: To run the script:
9
10 sql> start VEAVALPB.pls
11
12 HISTORY
13 Created MOHANA NARAYAN 06/05/2000 10:00 AM
14
15 =========================================================================*/
16
17 G_PACKAGE_NAME CONSTANT VARCHAR2(30) := 'VEA_LAYERPROVIDERS_SV';
18 --
19 --
20 /*========================================================================
21
22 PROCEDURE NAME: validateUniqueCodes
23
24 PURPOSE: Validates a record of VEA_LAYERPROVIDER table
25
26 ========================================================================*/
27 PROCEDURE
28 validateUniqueCodes
29 (
30 p_layer_provider_code IN vea_layer_providers.layer_provider_code%TYPE,
31 p_layer_provider_name IN vea_layer_providers.layer_provider_name%TYPE
32 )
33 IS
34 --{
35 l_api_name CONSTANT VARCHAR2(30) := 'validateUniqueCodes';
36 l_location VARCHAR2(32767);
37 --
38 --
39 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
40 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
41 --
42 --
43 CURSOR layer_cur IS
44 --
45 SELECT layer_provider_code
46 FROM vea_layer_providers
47 WHERE layer_provider_code = p_layer_provider_code;
48 --
49 --}
50 BEGIN
51 --{
52 l_location := '0010';
53 --
54 FOR layer_rec IN layer_cur
55 LOOP
56 --{
57 l_location := '0020';
58 --
59 --
60 vea_tpa_util_pvt.add_message_and_raise
61 (
62 p_error_name => 'VEA_LAYER_PROVIDER_CODE_EXIST',
63 p_token1 => 'LAYER_PROVIDER_NAME',
64 p_value1 => p_layer_provider_name
65 );
66 --}
67 END LOOP;
68 --
69 --
70 l_location := '0030';
71 --
72 --}
73 --}
74 EXCEPTION
75 --{
76 WHEN FND_API.G_EXC_ERROR
77 THEN
78 --{
79 RAISE;
80 --}
81 WHEN OTHERS
82 THEN
83 --{
84 vea_tpa_util_pvt.add_exc_message_and_raise
85 (
86 p_package_name => G_PACKAGE_NAME,
87 p_api_name => l_api_name,
88 p_location => l_location
89 );
90 --}
91 --}
92 END validateUniqueCodes;
93 --
94 --
95 /*========================================================================
96
97 PROCEDURE NAME: validate
98
99 PURPOSE: Validates a record of VEA_LAYERPROVIDER table
100
101 ========================================================================*/
102 PROCEDURE
103 validate
104 (
105 p_layer_provider_code IN vea_layer_providers.layer_provider_code%TYPE,
106 p_layer_provider_name IN vea_layer_providers.layer_provider_name%TYPE
107 )
108 IS
109 --{
110 l_api_name CONSTANT VARCHAR2(30) := 'validate';
111 l_location VARCHAR2(32767);
112 --
113 --
114 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
115 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
116 --}
117 BEGIN
118 --{
119 l_location := '0010';
120 --
121 -- what does this do ??
122 IF vea_tpa_util_pvt.validate
123 AND NOT(vea_tpa_util_pvt.isLayerMergeOn)
124 THEN
125
126 --{
127 l_location := '0020';
128 --
129 validateUniqueCodes
130 (
131 p_layer_provider_code => p_layer_provider_code,
132 p_layer_provider_name => p_layer_provider_name
133 );
134 --
135 --
136 l_location := '0030';
137 --
138 --}
139 END IF;
140 --}
141 EXCEPTION
142 --{
143 WHEN FND_API.G_EXC_ERROR
144 THEN
145 --{
146 RAISE;
147 --}
148 WHEN OTHERS
149 THEN
150 --{
151 vea_tpa_util_pvt.add_exc_message_and_raise
152 (
153 p_package_name => G_PACKAGE_NAME,
154 p_api_name => l_api_name,
155 p_location => l_location
156 );
157 --}
158 --}
159 END validate;
160 --
161 --
162 /*========================================================================
163
164 PROCEDURE NAME: insert_row
165
166 PURPOSE: Inserts a record into VEA_LAYER_PROVIDER table
167
168 ========================================================================*/
169 PROCEDURE
170 insert_row
171 (
172 p_layer_provider_id IN vea_layer_providers.layer_provider_id%TYPE,
173 p_layer_provider_code IN vea_layer_providers.layer_provider_code%TYPE,
174 p_layer_provider_name IN vea_layer_providers.layer_provider_name%TYPE,
175 p_description IN vea_layer_providers.description%TYPE
176 )
177 IS
178 --{
179 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
180 l_location VARCHAR2(32767);
181 --
182 --
183 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
184 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
185 --}
186 BEGIN
187 --{
188 l_location := '0010';
189 --
190 --
191 validateUniqueCodes
192 (
193 p_layer_provider_code => p_layer_provider_code,
194 p_layer_provider_name => p_layer_provider_name
195 );
196 --
197 --
198 l_location := '0020';
199 --
200 --
201 INSERT INTO vea_layer_providers
202 (
203 layer_provider_id,
204 layer_provider_code,
205 layer_provider_name,
206 description,
207 created_by, creation_date,
208 last_updated_by, last_update_date,
209 last_update_login
210 )
211 VALUES
212 (
213 p_layer_provider_id,
214 p_layer_provider_code,
215 p_layer_provider_name,
216 p_description,
217 l_user_id, SYSDATE,
218 l_user_id, SYSDATE,
219 l_login_id
220 );
221 --}
222 EXCEPTION
223 --{
224 WHEN OTHERS
225 THEN
226 --{
227 vea_tpa_util_pvt.add_exc_message_and_raise
228 (
229 p_package_name => G_PACKAGE_NAME,
230 p_api_name => l_api_name,
231 p_location => l_location
232 );
233 --}
234 --}
235 END insert_row;
236 --
237 --
238 /*========================================================================
239
240 PROCEDURE NAME: update_row
241
242 PURPOSE: Updates a record into VEA_LAYER_PROVIDER table
243
244 ========================================================================*/
245 PROCEDURE
246 update_row
247 (
248 p_layer_provider_id IN vea_layer_providers.layer_provider_id%TYPE,
249 p_layer_provider_code IN vea_layer_providers.layer_provider_code%TYPE,
250 p_layer_provider_name IN vea_layer_providers.layer_provider_name%TYPE,
251 p_description IN vea_layer_providers.description%TYPE
252 )
253 IS
254 --{
255 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
256 l_location VARCHAR2(32767);
257 --
258 --
259 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
260 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
261 --}
262 BEGIN
263 --{
264 l_location := '0010';
265 --
266 UPDATE vea_layer_providers
267 SET layer_provider_code = p_layer_provider_code,
268 layer_provider_name = p_layer_provider_name,
269 description = p_description,
270 last_updated_by = l_user_id,
271 last_update_date = SYSDATE,
272 last_update_login = l_login_id
273 WHERE layer_provider_id = p_layer_provider_id;
274 --}
275 EXCEPTION
276 --{
277 WHEN OTHERS
278 THEN
279 --{
280 vea_tpa_util_pvt.add_exc_message_and_raise
281 (
282 p_package_name => G_PACKAGE_NAME,
283 p_api_name => l_api_name,
284 p_location => l_location
285 );
286 --}
287 --}
288 END update_row;
289 --
290 --
291 /*========================================================================
292
293 PROCEDURE NAME: delete_rows
294
295 PURPOSE: Deletes the layer provider from the VEA_LAYER_PROVIDER table
296 if no layers have been developed using this layer provider code.
297
298 ========================================================================*/
299 PROCEDURE
300 delete_rows
301 (
302 p_layer_provider_code IN vea_layer_providers.layer_provider_code%TYPE,
303 p_layer_provider_name IN vea_layer_providers.layer_provider_name%TYPE
304 )
305 IS
306 --{
307 l_api_name CONSTANT VARCHAR2(30) := 'delete_rows';
308 l_location VARCHAR2(32767);
309 --
310 --
311 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
312 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
313 --
314 --
315 l_layer_count NUMBER;
316 --
317 --
318 --}
319 BEGIN
320 --{
321 l_location := '0010';
322 --
323 l_layer_count := 0;
324 --
325 --
326 l_location := '0020';
327 --
328 --
329 SELECT count(tp_layer_id)
330 INTO l_layer_count
331 FROM vea_tp_layers
332 WHERE layer_provider_code = p_layer_provider_code;
333 --
334 --
335 l_location := '0030';
336 --
337 --
338 IF l_layer_count = 0 THEN
339 --
340 --{
341 l_location := '0040';
342 --
343 IF p_layer_provider_code <> 'ORCL' THEN
344 -- Bug No: 1387766 - rvishnuv
345 --{
346 DELETE vea_layer_providers
347 WHERE layer_provider_code = p_layer_provider_code;
348 -- }
349 END IF;
350 --
351 --
352 l_location := '0050';
353 --}
354 --
355 ELSIF NOT(vea_tpa_util_pvt.isLayerMergeOn) THEN
356 --
357 --{
358 l_location := '0060';
359 --
360 vea_tpa_util_pvt.add_message_and_raise
361 (
362 p_error_name => 'VEA_LAYER_EXISTS_FOR_PROVIDER',
363 p_token1 => 'LAYER_PROVIDER_NAME',
364 p_value1 => p_layer_provider_name
365 );
366 --}
367 END IF;
368 --
369 --
370 l_location := '0060';
371 --
372 --
373 --}
374 EXCEPTION
375 --{
376 WHEN OTHERS
377 THEN
378 --{
379 vea_tpa_util_pvt.add_exc_message_and_raise
380 (
381 p_package_name => G_PACKAGE_NAME,
382 p_api_name => l_api_name,
383 p_location => l_location
384 );
385 --}
386 --}
387 END delete_rows;
388 --
389 --
390 /*========================================================================
391
392 PROCEDURE NAME: getLayerProviderCode
393
394 PURPOSE: Returns the layer developer's code given a name.
395
396 ========================================================================*/
397 FUNCTION
398 getLayerProviderCode
399 (
400 p_layer_provider_name IN vea_layer_providers.layer_provider_name%TYPE
401 )
402 RETURN VARCHAR2
403 IS
404 --{
405 l_api_name CONSTANT VARCHAR2(30) := 'getLayerProviderCode';
406 l_location VARCHAR2(32767);
407 --
408 --
409 l_layer_provider_code vea_layer_providers.layer_provider_code%TYPE;
410 --
411 --
412 CURSOR layer_cur IS
413 --
414 SELECT layer_provider_code
415 FROM vea_layer_providers
416 WHERE layer_provider_name = p_layer_provider_name;
417 --
418 --}
419 BEGIN
420 --{
421 l_location := '0010';
422 --
423 FOR layer_rec IN layer_cur
424 LOOP
425 --{
426 l_location := '0020';
427 --
428 l_layer_provider_code := layer_rec.layer_provider_code;
429 --
430 l_location := '0030';
431 --
432 RETURN(l_layer_provider_code);
433 --}
434 END LOOP;
435 --
436 --
437 l_location := '0040';
438 --
439 --
440 /* No such layer provider name found */
441 vea_tpa_util_pvt.add_message_and_raise
442 (
443 p_error_name => 'VEA_LAYER_NAME_NOTFOUND',
444 p_token1 => 'LAYER_PROVIDER_NAME',
445 p_value1 => p_layer_provider_name
446 );
447 --
448 --
449 --}
450 EXCEPTION
451 --{
452 WHEN OTHERS
453 THEN
454 --{
455 vea_tpa_util_pvt.add_exc_message_and_raise
456 (
457 p_package_name => G_PACKAGE_NAME,
458 p_api_name => l_api_name,
459 p_location => l_location
460 );
461 --}
462 --}
463 END getLayerProviderCode;
464 --
465 --
469
466 /*========================================================================
467
468 PROCEDURE NAME: getLayerProviderName
470 PURPOSE: Returns the layer developer's name given a code.
471
472 ========================================================================*/
473 FUNCTION
474 getLayerProviderName
475 (
476 p_layer_provider_code IN vea_layer_providers.layer_provider_code%TYPE
477 )
478 RETURN VARCHAR2
479 IS
480 --{
481 l_api_name CONSTANT VARCHAR2(30) := 'getLayerProviderName';
482 l_location VARCHAR2(32767);
483 --
484 --
485 l_layer_provider_name vea_layer_providers.layer_provider_name%TYPE;
486 --
487 --
488 CURSOR layer_cur IS
489 --
490 SELECT layer_provider_name
491 FROM vea_layer_providers
492 WHERE layer_provider_code = p_layer_provider_code;
493 --
494 --}
495 BEGIN
496 --{
497 l_location := '0010';
498 --
499 FOR layer_rec IN layer_cur
500 LOOP
501 --{
502 l_location := '0020';
503 --
504 l_layer_provider_name := layer_rec.layer_provider_name;
505 --
506 l_location := '0030';
507 --
508 RETURN(l_layer_provider_name);
509 --}
510 END LOOP;
511 --
512 --
513 l_location := '0040';
514 --
515 --
516 /* No such layer provider code found */
517 vea_tpa_util_pvt.add_message_and_raise
518 (
519 p_error_name => 'VEA_LAYER_CODE_NOTFOUND',
520 p_token1 => 'LAYER_PROVIDER_CODE',
521 p_value1 => p_layer_provider_code
522 );
523 --
524 --
525 --}
526 EXCEPTION
527 --{
528 WHEN OTHERS
529 THEN
530 --{
531 vea_tpa_util_pvt.add_exc_message_and_raise
532 (
533 p_package_name => G_PACKAGE_NAME,
534 p_api_name => l_api_name,
535 p_location => l_location
536 );
537 --}
538 --}
539 END getLayerProviderName;
540 --
541 --
542 /*========================================================================
543
544 PROCEDURE NAME: process
545
546 PURPOSE: Table hadndler API for VEA_LAYER_PROVIDER table.
547
548 It inserts/updates a record in VEA_LAYER_PROVIDER table.
549
550 ========================================================================*/
551 PROCEDURE
552 process
553 (
554 p_api_version IN NUMBER,
555 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
556 p_commit IN VARCHAR2 := FND_API.G_FALSE,
557 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
558 x_return_status OUT NOCOPY VARCHAR2,
559 x_msg_count OUT NOCOPY NUMBER,
560 x_msg_data OUT NOCOPY VARCHAR2,
561 p_layer_provider_code IN vea_layer_providers.layer_provider_code%TYPE,
562 p_layer_provider_name IN vea_layer_providers.layer_provider_name%TYPE,
563 p_description IN vea_layer_providers.description%TYPE
564 )
565 IS
566 --{
567 l_api_name CONSTANT VARCHAR2(30) := 'process';
568 l_api_version CONSTANT NUMBER := 1.0;
569 l_api_type CONSTANT VARCHAR2(3) := vea_tpa_util_pvt.G_PUBLIC_API;
570 --
571 --
572 l_location VARCHAR2(32767);
573 l_savepoint_name VARCHAR2(30);
574 l_insert BOOLEAN := TRUE;
575 l_layer_provider_name vea_layer_providers.layer_provider_name%TYPE;
576 l_layer_provider_id vea_layer_providers.layer_provider_id%TYPE;
577 l_layer_provider_code vea_layer_providers.layer_provider_code%TYPE;
578 l_layer_count NUMBER := 0;
579 --
580 --
581 TYPE lp_rec_type IS
582 RECORD
583 (
584 layer_provider_id vea_layer_providers.layer_provider_id%TYPE,
585 layer_provider_code vea_layer_providers.layer_provider_code%TYPE,
586 layer_provider_name vea_layer_providers.layer_provider_name%TYPE
587 );
588 --
589 --
590 TYPE layer_cur_type IS REF CURSOR ;
591 --RETURN lp_rec_type;
592 --
593 --
594 layer_cur layer_cur_type;
595 layer_rec lp_rec_type;
596 --}
597 BEGIN
598 --{
599 l_location := '0010';
600 --
601 IF NOT( vea_tpa_util_pvt.is_vea_installed() )
602 THEN
603 RETURN;
604 END IF;
605 --
606 --
607 -- Standard API Header
608 --
609 l_location := '0020';
610 --
611 vea_tpa_util_pvt.api_header
612 (
613 p_package_name => G_PACKAGE_NAME,
614 p_api_name => l_api_name,
615 p_api_type => l_api_type,
616 p_api_current_version => l_api_version,
617 p_api_caller_version => p_api_version,
621 );
618 p_init_msg_list => p_init_msg_list,
619 x_savepoint_name => l_savepoint_name,
620 x_api_return_status => x_return_status
622 --
623 --
624 --{ API Body
625 --
626 --
627 l_location := '0030';
628 --
629 l_location := '0035';
630 --
631 --
632 l_insert := TRUE;
633 --
634 --
635 l_location := '0040';
636 --
637 --
638 IF (vea_tpa_util_pvt.isLayerMergeOn)
639 THEN
640 open layer_cur for
641 'select layer_provider_id,
642 layer_provider_code, layer_provider_name
643 from vea_layer_providers
644 where layer_provider_code = :lp_code'
645 using p_layer_provider_code;
646 ELSE
647 open layer_cur for
648 select layer_provider_id,
649 layer_provider_code, layer_provider_name
650 from vea_layer_providers
651 where layer_provider_code is not null;
652 END IF;
653
654 LOOP
655 --{
656 l_location := '0045';
657
658 FETCH layer_cur INTO layer_rec;
659
660 EXIT WHEN layer_cur%NOTFOUND;
661 --
662 --
663 l_insert := FALSE;
664 --
665 --
666 l_location := '0070';
667 --
668 --
669 validate
670 (
671 p_layer_provider_code => p_layer_provider_code,
672 p_layer_provider_name => p_layer_provider_name
673 );
674 --
675 --
676 l_location := '0090';
677 --
678 SELECT count(tp_layer_id)
679 INTO l_layer_count
680 FROM vea_tp_layers
681 WHERE layer_provider_code = layer_rec.layer_provider_code;
682 --
683 --
684 l_location := '0100';
685 --
686 -- Bug No: 1389096.
687 --IF ( vea_tpa_util_pvt.isLayerMergeOn ) AND (l_layer_count = 0)
688 IF ( vea_tpa_util_pvt.isLayerMergeOn )
689 THEN
690 --{
691 update_row
692 (
693 p_layer_provider_id => layer_rec.layer_provider_id,
694 p_layer_provider_code => p_layer_provider_code,
695 p_layer_provider_name => p_layer_provider_name,
696 p_description => p_description
697 );
698 ELSIF l_layer_count = 0
699 THEN
700 update_row
701 (
702 p_layer_provider_id => layer_rec.layer_provider_id,
703 p_layer_provider_code => p_layer_provider_code,
704 p_layer_provider_name => p_layer_provider_name,
705 p_description => p_description
706 );
707 vea_tpa_util_pvt.update_lookup_values
708 (
709 p_lookup_type => 'VEA_LAYER_PROVIDERS',
710 p_new_lookup_code => p_layer_provider_code,
711 p_current_lookup_code => layer_rec.layer_provider_code,
712 p_meaning => p_layer_provider_name,
713 p_description => p_layer_provider_name
714 );
715 ELSE
716 vea_tpa_util_pvt.add_message_and_raise
717 (
718 p_error_name => 'VEA_LAYER_EXISTS_FOR_PROVIDER',
719 p_token1 => 'LAYER_PROVIDER_NAME',
720 p_value1 => layer_rec.layer_provider_name
721 );
722 --}
723 END IF;
724
725 --}
726 END LOOP;
727
728 close layer_cur;
729
730 IF l_insert = TRUE THEN
731 --{
732 l_location := '0110';
733 --
734 --
735 SELECT vea_layer_providers_s.NEXTVAL
736 INTO l_layer_provider_id
737 FROM DUAL;
738 --
739 --
740 insert_row
741 (
742 p_layer_provider_id => l_layer_provider_id,
743 p_layer_provider_code => p_layer_provider_code,
744 p_layer_provider_name => p_layer_provider_name,
745 p_description => p_description
746 );
747 IF NOT( vea_tpa_util_pvt.isLayerMergeOn )
748 THEN
749 --{
750 vea_tpa_util_pvt.insert_lookup_values
751 (
752 p_lookup_type => 'VEA_LAYER_PROVIDERS',
753 p_lookup_code => p_layer_provider_code,
757 --}
754 p_meaning => p_layer_provider_name,
755 p_description => p_layer_provider_name
756 );
758 END IF;
759
760 --}
761 END IF;
762 --
763 --
764 l_location := '0120';
765 --
766 --
767 --
768 --
769 --} API Body
770 --
771 --
772 -- Standard API Footer
773 --
774 l_location := '0130';
775 --
776 vea_tpa_util_pvt.api_footer
777 (
778 p_commit => p_commit,
779 x_msg_count => x_msg_count,
780 x_msg_data => x_msg_data
781 );
782 --}
783 EXCEPTION
784 --{
785 WHEN FND_API.G_EXC_ERROR
786 THEN
787 --{
788 --RAISE;
789 vea_tpa_util_pvt.handle_error
790 (
791 p_error_type => vea_tpa_util_pvt.G_ERROR,
792 p_savepoint_name => l_savepoint_name,
793 p_package_name => G_PACKAGE_NAME,
794 p_api_name => l_api_name,
795 p_location => l_location,
796 x_msg_count => x_msg_count,
797 x_msg_data => x_msg_data,
798 x_api_return_status => x_return_status
799 );
800 --}
801 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
802 THEN
803 --{
804 --RAISE;
805 vea_tpa_util_pvt.handle_error
806 (
807 p_error_type => vea_tpa_util_pvt.G_UNEXPECTED_ERROR,
808 p_savepoint_name => l_savepoint_name,
809 p_package_name => G_PACKAGE_NAME,
810 p_api_name => l_api_name,
811 p_location => l_location,
812 x_msg_count => x_msg_count,
813 x_msg_data => x_msg_data,
814 x_api_return_status => x_return_status
815 );
816 --}
817 WHEN OTHERS
818 THEN
819 --{
820 --RAISE;
821 vea_tpa_util_pvt.handle_error
822 (
823 p_error_type => vea_tpa_util_pvt.G_OTHER_ERROR,
824 p_savepoint_name => l_savepoint_name,
825 p_package_name => G_PACKAGE_NAME,
826 p_api_name => l_api_name,
827 p_location => l_location,
828 x_msg_count => x_msg_count,
829 x_msg_data => x_msg_data,
830 x_api_return_status => x_return_status
831 );
832 --}
833 --}
834 END process;
835 --}
836 END VEA_LAYERPROVIDERS_SV;