[Home] [Help]
PACKAGE BODY: APPS.VEA_PROGRAM_UNITS_SV
Source
1 PACKAGE BODY VEA_PROGRAM_UNITS_SV as
2 /* $Header: VEAVAPUB.pls 115.14 2004/07/27 02:42:47 rvishnuv ship $ */
3 --{
4 /*====================== vea_program_units_sv =========================*/
5 /*========================================================================
6 PURPOSE: Table handler package for table VEA_PROGRAM_UNITS
7
8 NOTES: To run the script:
9
10 sql> start VEAVAPKB.pls
11
12 HISTORY
13 Created N PARIKH 09/09/99 10:00 AM
14
15 =========================================================================*/
16
17 G_PACKAGE_NAME CONSTANT VARCHAR2(30) := 'VEA_PROGRAM_UNITS_SV';
18 --
19 --
20 /*========================================================================
21
22 PROCEDURE NAME: insert_row
23
24 PURPOSE: Inserts a record into VEA_PROGRAM_UNITS table
25
26 ========================================================================*/
27 PROCEDURE
28 insert_row
29 (
30 p_layer_provider_code IN vea_program_units.layer_provider_code%TYPE,
31 p_program_unit_id IN vea_program_units.program_unit_id%TYPE,
32 p_package_id IN vea_program_units.program_unit_id%TYPE,
33 p_program_unit_type IN vea_program_units.program_unit_type%TYPE,
34 p_public_flag IN vea_program_units.public_flag%TYPE,
35 p_customizable_flag IN vea_program_units.customizable_flag%TYPE,
36 p_tps_flag IN vea_program_units.tps_flag%TYPE,
37 p_name IN vea_program_units.name%TYPE,
38 p_label IN vea_program_units.label%TYPE,
39 p_return_type IN vea_program_units.return_type%TYPE,
40 p_tpa_program_unit_id IN vea_program_units.tpa_program_unit_id%TYPE,
41 p_description IN vea_program_units.description%TYPE
42 )
43 IS
44 --{
45 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
46 l_location VARCHAR2(32767);
47 --
48 --
49 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
50 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
51 --}
52 BEGIN
53 --{
54 l_location := '0010';
55 --
56 INSERT INTO vea_program_units
57 (
58 layer_provider_code, program_unit_id,
59 package_id, program_unit_type,
60 name, label,
61 public_flag, customizable_flag,
62 tps_flag,
63 return_type, tpa_program_unit_id,
64 description,
65 created_by, creation_date,
66 last_updated_by, last_update_date,
67 last_update_login
68 )
69 VALUES
70 (
71 p_layer_provider_code, p_program_unit_id,
72 p_package_id, p_program_unit_type,
73 p_name, p_label,
74 p_public_flag, p_customizable_flag,
75 p_tps_flag,
76 p_return_type, p_tpa_program_unit_id,
77 p_description,
78 l_user_id, SYSDATE,
79 l_user_id, SYSDATE,
80 l_login_id
81 );
82 --}
83 EXCEPTION
84 --{
85 WHEN OTHERS
86 THEN
87 --{
88 vea_tpa_util_pvt.add_exc_message_and_raise
89 (
90 p_package_name => G_PACKAGE_NAME,
91 p_api_name => l_api_name,
92 p_location => l_location
93 );
94 --}
95 --}
96 END insert_row;
97 --
98 --
99 /*========================================================================
100
101 PROCEDURE NAME: update_row
102
103 PURPOSE: Updates a record into VEA_PROGRAM_UNITS table
104
105 ========================================================================*/
106 PROCEDURE
107 update_row
108 (
109 p_layer_provider_code IN vea_program_units.layer_provider_code%TYPE,
110 p_program_unit_id IN vea_program_units.program_unit_id%TYPE,
111 p_package_id IN vea_program_units.program_unit_id%TYPE,
112 p_program_unit_type IN vea_program_units.program_unit_type%TYPE,
113 p_public_flag IN vea_program_units.public_flag%TYPE,
114 p_customizable_flag IN vea_program_units.customizable_flag%TYPE,
115 p_tps_flag IN vea_program_units.tps_flag%TYPE,
116 p_name IN vea_program_units.name%TYPE,
117 p_label IN vea_program_units.label%TYPE,
118 p_return_type IN vea_program_units.return_type%TYPE,
119 p_tpa_program_unit_id IN vea_program_units.tpa_program_unit_id%TYPE,
120 p_description IN vea_program_units.description%TYPE
121 )
122 IS
123 --{
124 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
125 l_location VARCHAR2(32767);
126 --
127 --
128 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
129 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
130 --}
131 BEGIN
132 --{
133 l_location := '0010';
134 --
135 UPDATE vea_program_units
136 SET package_id = p_package_id,
137 program_unit_type = p_program_unit_type,
138 name = p_name,
139 label = p_label,
140 public_flag = p_public_flag,
141 customizable_flag = p_customizable_flag,
142 tps_flag = p_tps_flag,
143 return_type = p_return_type,
144 tpa_program_unit_id = p_tpa_program_unit_id,
145 description = p_description,
146 last_updated_by = l_user_id,
147 last_update_date = SYSDATE,
148 last_update_login = l_login_id
149 WHERE layer_provider_code = p_layer_provider_code
150 AND program_unit_id = p_program_unit_id;
151 --}
152 EXCEPTION
153 --{
154 WHEN OTHERS
155 THEN
156 --{
157 vea_tpa_util_pvt.add_exc_message_and_raise
158 (
159 p_package_name => G_PACKAGE_NAME,
160 p_api_name => l_api_name,
161 p_location => l_location
162 );
163 --}
164 --}
165 END update_row;
166 --
167 --
168 /*========================================================================
169
170 PROCEDURE NAME: delete_row
171
172 PURPOSE: Deletes all parameters of the specified program unit and
173 program unit itself.
174
175 ========================================================================*/
176 PROCEDURE
177 delete_row
178 (
179 p_layer_provider_code IN vea_layers.layer_provider_code%TYPE,
180 p_program_unit_id IN vea_program_units.program_unit_id%TYPE
181 )
182 IS
183 --{
184 l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
185 l_location VARCHAR2(32767);
186 --
187 --
188 --}
189 BEGIN
190 --{
191 l_location := '0010';
192 --
193 vea_parameters_sv.delete_rows
194 (
195 p_layer_provider_code => p_layer_provider_code,
196 p_program_unit_id => p_program_unit_id
197 );
198 --
199 --
200 l_location := '0020';
201 --
202 DELETE vea_program_units
203 WHERE layer_provider_code = p_layer_provider_code
204 AND program_unit_id = p_program_unit_id;
205 --}
206 EXCEPTION
207 --{
208 WHEN OTHERS
209 THEN
210 --{
211 vea_tpa_util_pvt.add_exc_message_and_raise
212 (
213 p_package_name => G_PACKAGE_NAME,
214 p_api_name => l_api_name,
215 p_location => l_location
216 );
217 --}
218 --}
219 END delete_row;
220 --
221 --
222 /*========================================================================
223
224 PROCEDURE NAME: delete_rows
225
226 PURPOSE: Deletes all packages developed by specified layer provider and
227 used in the specified TP layer of any customizable program
228 units of the specified application.
229
230 It first queries all program units developed by specified layer
231 provider and belonging to the specified package.
232
233 For each program unit,
234 - it deletes the program units ( and their parameters ),
235 if
236 - it unit is not a TPS program unit
237 AND it is used in the specified TP layer in any
238 customizable program units of the specified application.
239 OR
240 - if it is a TPS program unit and not used anywhere.
241
242 ========================================================================*/
243 PROCEDURE
244 delete_rows
245 (
246 p_layer_provider_code IN vea_layers.layer_provider_code%TYPE,
247 p_tp_layer_id IN vea_tp_layers.tp_layer_id%TYPE,
248 p_package_id IN vea_packages.package_id%TYPE,
249 p_application_short_name IN vea_packages.application_short_name%TYPE,
250 x_program_unit_count OUT NOCOPY NUMBER,
251 x_tps_program_unit_count OUT NOCOPY NUMBER
252 )
253 IS
254 --{
255 l_api_name CONSTANT VARCHAR2(30) := 'delete_rows';
256 l_location VARCHAR2(32767);
257 --
258 --
259 CURSOR program_unit_cur
260 (
261 p_layer_provider_code IN vea_layers.layer_provider_code%TYPE,
262 p_package_id IN vea_packages.package_id%TYPE
263 )
264 IS
265 SELECT program_unit_id,
266 layer_provider_code,
267 tps_flag
268 FROM vea_program_units
269 WHERE layer_provider_code = p_layer_provider_code
270 AND package_id = p_package_id;
271 --
272 --
273 CURSOR tps_cur
274 (
275 p_tps_program_unit_lp_code IN vea_layers.layer_provider_code%TYPE,
276 p_tps_program_unit_id IN vea_program_units.program_unit_id%TYPE
277 )
278 IS
279 SELECT 'x'
280 FROM vea_layer_headers
281 WHERE tps_program_unit_lp_code = p_tps_program_unit_lp_code
282 AND tps_program_unit_id = p_tps_program_unit_id;
283 --
284 --
285 CURSOR application_cur
286 (
287 p_layer_provider_code IN vea_layers.layer_provider_code%TYPE,
288 p_tp_layer_id IN vea_tp_layers.tp_layer_id%TYPE,
289 p_package_id IN vea_packages.package_id%TYPE,
290 p_program_unit_id IN vea_program_units.program_unit_id%TYPE
291 )
292 IS
293 SELECT PK.application_short_name
294 FROM vea_program_units LPU,
295 vea_layer_headers LH,
296 vea_packages PK,
297 vea_program_units PU,
298 vea_layers_v LA
299 WHERE LA.layer_provider_code = p_layer_provider_code
300 AND LA.tp_layer_id = p_tp_layer_id
301 AND LH.layer_provider_code = LA.layer_provider_code
302 AND LH.layer_header_id = LA.layer_header_id
303 AND PU.program_unit_id = LH.program_unit_id
304 AND PU.layer_provider_code = LH.program_unit_lp_code
305 AND PK.package_id = PU.package_id
306 AND PK.layer_provider_code = PU.layer_provider_code
307 AND PK.tpa_flag = 'Y'
308 AND PU.tpa_program_unit_id IS NOT NULL
309 AND LPU.package_id = p_package_id
310 AND LPU.program_unit_id = p_program_unit_id
311 AND LPU.program_unit_id = LA.new_program_unit_id
312 AND LPU.layer_provider_code = LA.program_unit_lp_code;
313 --
314 --
315 l_program_unit_count NUMBER := 0;
316 l_tps_program_unit_count NUMBER := 0;
317 l_layer_header_count NUMBER := 0;
318 l_count NUMBER := 0;
319 --}
320 BEGIN
321 --{
322 l_location := '0010';
323 --
324 l_program_unit_count := 0;
325 l_tps_program_unit_count := 0;
326 l_layer_header_count := 0;
327 --
328 --
329 l_location := '0020';
330 --
331 FOR program_unit_rec IN program_unit_cur
332 (
333 p_layer_provider_code => p_layer_provider_code,
334 p_package_id => p_package_id
335 )
336 LOOP
337 --{
338 l_location := '0030';
339 --
340 IF program_unit_rec.tps_flag = 'N'
341 THEN
342 --{
343 l_location := '0040';
344 --
345 l_count := 0;
346 --
347 --
348 l_location := '0050';
349 --
350 FOR application_rec IN application_cur
351 (
352 p_layer_provider_code => p_layer_provider_code,
353 p_tp_layer_id => p_tp_layer_id,
354 p_package_id => p_package_id,
355 p_program_unit_id => program_unit_rec.program_unit_id
356 )
357 LOOP
358 --{
359 l_location := '0060';
360 --
361 IF application_rec.application_short_name
362 <>
363 NVL(
364 p_application_short_name,
365 application_rec.application_short_name
366 )
367 THEN
368 --{
369 l_location := '0070';
370 --
371 l_count := l_count + 1;
372 --}
373 END IF;
374 --}
375 END LOOP;
376 --
377 --
378 l_location := '0080';
379 --
380 IF l_count = 0
381 THEN
382 --{
383 l_location := '0090';
384 --
385 delete_row
386 (
387 p_layer_provider_code => program_unit_rec.layer_provider_code,
388 p_program_unit_id => program_unit_rec.program_unit_id
389 );
390 --}
391 ELSE
392 --{
393 l_location := '0100';
394 --
395 l_program_unit_count := l_program_unit_count + 1;
396 --}
397 END IF;
398 --}
399 ELSE
400 --{
401 l_location := '0110';
402 --
403 l_layer_header_count := 0;
404 --
405 --
406 l_location := '0120';
407 --
408 FOR tps_rec IN tps_cur
409 (
410 p_tps_program_unit_lp_code => program_unit_rec.layer_provider_code,
411 p_tps_program_unit_id => program_unit_rec.program_unit_id
412 )
413 LOOP
414 --{
415 l_location := '0130';
416 --
417 l_layer_header_count := l_layer_header_count + 1;
418 --}
419 END LOOP;
420 --
421 --
422 l_location := '0140';
423 --
424 IF l_layer_header_count = 0
425 THEN
426 --{
427 l_location := '0150';
428 --
429 delete_row
430 (
431 p_layer_provider_code => program_unit_rec.layer_provider_code,
432 p_program_unit_id => program_unit_rec.program_unit_id
433 );
434 --}
435 ELSE
436 --{
437 l_location := '0160';
438 --
439 l_program_unit_count := l_program_unit_count + 1;
440 l_tps_program_unit_count := l_tps_program_unit_count + 1;
441 --}
442 END IF;
443 --}
444 END IF;
445 --}
446 END LOOP;
447 --
448 --
449 l_location := '0170';
450 --
451 x_program_unit_count := NVL(l_program_unit_count,0);
452 x_tps_program_unit_count := NVL(l_tps_program_unit_count,0);
453 --}
454 EXCEPTION
455 --{
456 WHEN OTHERS
457 THEN
458 --{
459 vea_tpa_util_pvt.add_exc_message_and_raise
460 (
461 p_package_name => G_PACKAGE_NAME,
462 p_api_name => l_api_name,
463 p_location => l_location
464 );
465 --}
466 --}
467 END delete_rows;
468 --
469 --
470 /*========================================================================
471
472 PROCEDURE NAME: getName
473
474 PURPOSE:
475
476 ========================================================================*/
477 PROCEDURE
478 getName
479 (
480 p_layer_provider_code IN vea_program_units.layer_provider_code%TYPE,
481 p_program_unit_id IN vea_program_units.program_unit_id%TYPE,
482 x_program_unit_name OUT NOCOPY vea_program_units.name%TYPE,
483 x_package_name OUT NOCOPY vea_packages.name%TYPE
484 )
485 IS
486 --{
487 l_api_name CONSTANT VARCHAR2(30) := 'getName';
488 l_location VARCHAR2(32767);
489 --
490 --
491 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
492 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
493 --
494 --
495 CURSOR program_unit_cur
496 IS
497 SELECT PU.name program_unit_name,
498 PK.name package_name
499 FROM vea_program_units PU,
500 vea_packages PK
501 WHERE PU.layer_provider_code = p_layer_provider_code
502 AND PU.program_unit_id = p_program_unit_id
503 AND PK.layer_provider_code = PU.layer_provider_code
504 AND PK.package_id = PU.package_id;
505 --}
506 BEGIN
507 --{
508 l_location := '0010';
509 --
510 FOR program_unit_rec IN program_unit_cur
511 LOOP
512 --{
513 l_location := '0020';
514 --
515 x_program_unit_name := program_unit_rec.program_unit_name;
516 x_package_name := program_unit_rec.package_name;
517 --}
518 END LOOP;
519 --}
520 EXCEPTION
521 --{
522 WHEN OTHERS
523 THEN
524 --{
525 vea_tpa_util_pvt.add_exc_message_and_raise
526 (
527 p_package_name => G_PACKAGE_NAME,
528 p_api_name => l_api_name,
529 p_location => l_location
530 );
531 --}
532 --}
533 END getName;
534 --
535 --
536 /*========================================================================
537
538 PROCEDURE NAME: validateMapping
539
540 PURPOSE:
541
542 ========================================================================*/
543 PROCEDURE
544 validateMapping
545 (
546 p_layer_provider_code IN vea_program_units.layer_provider_code%TYPE,
547 p_program_unit_id IN vea_program_units.program_unit_id%TYPE,
548 p_tpa_program_unit_id IN vea_program_units.tpa_program_unit_id%TYPE,
549 p_old_tpa_program_unit_id IN vea_program_units.tpa_program_unit_id%TYPE
550 )
551 IS
552 --{
553 l_api_name CONSTANT VARCHAR2(30) := 'validateMapping';
554 l_location VARCHAR2(32767);
555 --
556 --
557 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
558 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
559 --
560 --
561 l_program_unit_name vea_program_units.name%TYPE;
562 l_package_name vea_packages.name%TYPE;
563 l_pub_program_unit_name vea_program_units.name%TYPE;
564 l_pub_package_name vea_packages.name%TYPE;
565 l_tpa_program_unit_name vea_program_units.name%TYPE;
566 l_tpa_package_name vea_packages.name%TYPE;
567 --}
568 BEGIN
569 --{
570 l_location := '0010';
571 --
572 IF vea_tpa_util_pvt.validate
573 AND NOT(vea_tpa_util_pvt.isLayerMergeOn)
574 AND p_old_tpa_program_unit_id <> p_tpa_program_unit_id
575 THEN
576 --{
577 l_location := '0020';
578 --
579 getName
580 (
581 p_layer_provider_code => p_layer_provider_code,
582 p_program_unit_id => p_program_unit_id,
583 x_program_unit_name => l_tpa_program_unit_name,
584 x_package_name => l_tpa_package_name
585 );
586 --
587 --
588 l_location := '0020';
589 --
590 getName
591 (
592 p_layer_provider_code => p_layer_provider_code,
593 p_program_unit_id => p_old_tpa_program_unit_id,
594 x_program_unit_name => l_program_unit_name,
595 x_package_name => l_package_name
596 );
597 --
598 --
599 l_location := '0020';
600 --
601 getName
602 (
603 p_layer_provider_code => p_layer_provider_code,
604 p_program_unit_id => p_tpa_program_unit_id,
605 x_program_unit_name => l_pub_program_unit_name,
606 x_package_name => l_pub_package_name
607 );
608 --
609 --
610 l_location := '0020';
611 --
612 vea_tpa_util_pvt.add_message_and_raise
613 (
614 p_error_name => 'VEA_IMPORT_DUP_TPA_MAPPING',
615 p_token1 => 'TPA_PROGRAM_UNIT_NAME',
616 p_value1 => l_tpa_program_unit_name,
617 p_token2 => 'TPA_PACKAGE_NAME',
618 p_value2 => l_tpa_package_name,
619 p_token3 => 'PROGRAM_UNIT_NAME',
620 p_value3 => l_program_unit_name,
621 p_token4 => 'PACKAGE_NAME',
622 p_value4 => l_package_name,
623 p_token5 => 'PUB_PROGRAM_UNIT_NAME',
624 p_value5 => l_pub_program_unit_name,
625 p_token6 => 'PUB_PACKAGE_NAME',
626 p_value6 => l_pub_package_name
627 );
628 --}
629 END IF;
630 --}
631 EXCEPTION
632 --{
633 WHEN FND_API.G_EXC_ERROR
634 THEN
635 --{
636 RAISE;
637 --}
638 WHEN OTHERS
639 THEN
640 --{
641 vea_tpa_util_pvt.add_exc_message_and_raise
642 (
643 p_package_name => G_PACKAGE_NAME,
644 p_api_name => l_api_name,
645 p_location => l_location
646 );
647 --}
648 --}
649 END validateMapping;
650 --
651 --
652 /*========================================================================
653
654 PROCEDURE NAME: deleteUnreferencedProgramUnits
655
656 PURPOSE:
657
658 ========================================================================*/
659 PROCEDURE
660 deleteUnreferencedProgramUnits
661 (
662 p_layer_provider_code IN vea_program_units.layer_provider_code%TYPE,
663 p_program_unit_id IN vea_program_units.program_unit_id%TYPE,
664 p_tpa_program_unit_id IN vea_program_units.tpa_program_unit_id%TYPE
665 )
666 IS
667 --{
668 l_api_name CONSTANT VARCHAR2(30) := 'deleteUnreferencedProgramUnits';
669 l_location VARCHAR2(32767);
670 --
671 --
672 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
673 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
674 --
675 --
676 CURSOR program_unit_cur
677 IS
678 SELECT program_unit_id
679 FROM vea_program_units
680 WHERE tpa_program_unit_id = p_tpa_program_unit_id
681 AND layer_provider_code = p_layer_provider_code;
682 --}
683 BEGIN
684 --{
685 l_location := '0010';
686 --
687 FOR program_unit_rec IN program_unit_cur
688 LOOP
689 --{
690 l_location := '0020';
691 --
692 IF program_unit_rec.program_unit_id <> p_program_unit_id
693 THEN
694 --{
695 l_location := '0030';
696 --
697 delete_row
698 (
699 p_layer_provider_code => p_layer_provider_code,
700 p_program_unit_id => program_unit_rec.program_unit_id
701 );
702 --}
703 END IF;
704 --}
705 END LOOP;
706 --}
707 EXCEPTION
708 --{
709 WHEN OTHERS
710 THEN
711 --{
712 vea_tpa_util_pvt.add_exc_message_and_raise
713 (
714 p_package_name => G_PACKAGE_NAME,
715 p_api_name => l_api_name,
716 p_location => l_location
717 );
718 --}
719 --}
720 END deleteUnreferencedProgramUnits;
721 --
722 --
723 --
724 /*========================================================================
725
726 PROCEDURE NAME: getId
727
728 PURPOSE:
729
730 ========================================================================*/
731 FUNCTION
732 getId
733 (
734 p_layer_provider_code IN vea_program_units.layer_provider_code%TYPE,
735 p_package_id IN vea_program_units.package_id%TYPE,
736 p_name IN vea_program_units.name%TYPE
737 )
738 RETURN NUMBER
739 IS
740 --{
741 l_api_name CONSTANT VARCHAR2(30) := 'getId';
742 l_location VARCHAR2(32767);
743 --
744 --
745 CURSOR program_unit_cur
746 (
747 p_layer_provider_code IN vea_program_units.layer_provider_code%TYPE,
748 p_package_id IN vea_program_units.package_id%TYPE,
749 p_name IN vea_program_units.name%TYPE
750 )
751 IS
752 SELECT program_unit_id
753 FROM vea_program_units
754 WHERE layer_provider_code = p_layer_provider_code
755 AND package_id = p_package_id
756 AND UPPER(name) = UPPER(p_name);
757 --}
758 BEGIN
759 --{
760 l_location := '0010';
761 --
762 FOR program_unit_rec IN program_unit_cur
763 (
764 p_layer_provider_code => p_layer_provider_code,
765 p_package_id => p_package_id,
766 p_name => p_name
767 )
768 LOOP
769 --{
770 l_location := '0020';
771 --
772 RETURN (program_unit_rec.program_unit_id);
773 --}
774 END LOOP;
775 --
776 --
777 RETURN(NULL);
778 --}
779 EXCEPTION
780 --{
781 WHEN OTHERS
782 THEN
783 --{
784 RAISE;
785 --}
786 --}
787 END getId;
788 --
789 --
790 /*========================================================================
791
792 PROCEDURE NAME: process
793
794 PURPOSE: Table hadndler API for VEA_PROGRAM_UNITS table.
795
796 It inserts/updates a record in VEA_PROGRAM_UNITS table.
797
798 ========================================================================*/
799 PROCEDURE
800 process
801 (
802 p_api_version IN NUMBER,
803 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
804 p_commit IN VARCHAR2 := FND_API.G_FALSE,
805 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
806 x_return_status OUT NOCOPY VARCHAR2,
807 x_msg_count OUT NOCOPY NUMBER,
808 x_msg_data OUT NOCOPY VARCHAR2,
809 x_id OUT NOCOPY vea_program_units.program_unit_id%TYPE,
810 p_layer_provider_code IN vea_program_units.layer_provider_code%TYPE,
811 p_package_id IN vea_program_units.program_unit_id%TYPE,
812 p_program_unit_type IN vea_program_units.program_unit_type%TYPE,
813 p_public_flag IN vea_program_units.public_flag%TYPE,
814 p_customizable_flag IN vea_program_units.customizable_flag%TYPE,
815 p_tps_flag IN vea_program_units.tps_flag%TYPE,
816 p_name IN vea_program_units.name%TYPE,
817 p_label IN vea_program_units.label%TYPE,
818 p_return_type IN vea_program_units.return_type%TYPE,
819 p_tpa_program_unit_id IN vea_program_units.tpa_program_unit_id%TYPE,
820 p_description IN vea_program_units.description%TYPE,
821 p_id IN vea_program_units.program_unit_id%TYPE := NULL,
822 p_tp_layer_id IN vea_tp_layers.tp_layer_id%TYPE := NULL
823 )
824 IS
825 --{
826 l_api_name CONSTANT VARCHAR2(30) := 'process';
827 l_api_version CONSTANT NUMBER := 1.0;
828 l_api_type CONSTANT VARCHAR2(3) := vea_tpa_util_pvt.G_PUBLIC_API;
829 --
830 --
831 l_location VARCHAR2(32767);
832 l_savepoint_name VARCHAR2(30);
833 l_program_unit_id vea_program_units.program_unit_id%TYPE;
834 l_db_tpa_program_unit_id vea_program_units.tpa_program_unit_id%TYPE;
835 l_tpa_program_unit_id vea_program_units.tpa_program_unit_id%TYPE;
836 l_package_id vea_packages.package_id%TYPE;
837 l_tp_layer_id vea_tp_layers.tp_layer_id%TYPE;
838 --
839 --
840 CURSOR program_unit_cur
841 (
842 p_layer_provider_code IN vea_program_units.layer_provider_code%TYPE,
843 p_program_unit_id IN vea_program_units.program_unit_id%TYPE,
844 p_package_id IN vea_program_units.package_id%TYPE,
845 p_name IN vea_program_units.name%TYPE
846 )
847 IS
848 SELECT program_unit_id, tpa_program_unit_id
849 FROM vea_program_units
850 WHERE layer_provider_code = p_layer_provider_code
851 AND package_id = p_package_id
852 AND UPPER(name) = UPPER(p_name);
853 -- Commented out this code because we no longer base our processing on the ids
854 -- stored in the flat file. We go by the names of the packages
855 -- and derive the ids based on the names and use the derived ids
856 -- in further processing.
857 /*
858 AND (
859 (
860 p_program_unit_id IS NOT NULL
861 AND program_unit_id = p_program_unit_id
862 )
863 OR
864 (
865 p_program_unit_id IS NULL
866 AND package_id = p_package_id
867 AND name = p_name
868 )
869 );
870 */
871 --
872 --
873 --}
874 BEGIN
875 --{
876 l_location := '0010';
877 --
878 IF NOT( vea_tpa_util_pvt.is_vea_installed() )
879 THEN
880 RETURN;
881 END IF;
882 --
883 --
884 -- Standard API Header
885 --
886 l_location := '0020';
887 --
888 vea_tpa_util_pvt.api_header
889 (
890 p_package_name => G_PACKAGE_NAME,
891 p_api_name => l_api_name,
892 p_api_type => l_api_type,
893 p_api_current_version => l_api_version,
894 p_api_caller_version => p_api_version,
895 p_init_msg_list => p_init_msg_list,
896 x_savepoint_name => l_savepoint_name,
897 x_api_return_status => x_return_status
898 );
899 --
900 --
901 --{ API Body
902 --
903 --
904 l_location := '0030';
905 --
906 g_program_unit_id := p_id;
907 l_tpa_program_unit_id := p_tpa_program_unit_id;
908 --
909 --
910 IF (VEA_TPA_UTIL_PVT.isLayerMergeOn) THEN
911 --{
912 --FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_package_id ' || p_package_id);
913 --l_package_id := nvl(vea_packages_sv.g_package_id,p_package_id);
914 l_location := '0040';
915 --
916 l_package_id := vea_packages_sv.g_package_id;
917 l_tp_layer_id := nvl(vea_packages_sv.g_tp_layer_id,p_tp_layer_id);
918 --
919 --
920 IF p_tpa_program_unit_id IS NOT NULL
921 THEN
922 --{
923 BEGIN
924 l_location := '0050';
925 --
926 vea_tpa_util_pvt.get
927 (
928 p_key => p_tpa_program_unit_id,
929 p_cache_tbl => vea_tpa_util_pvt.g_PU_fileId_dbId_tbl,
930 p_cache_ext_tbl => vea_tpa_util_pvt.g_PU_fileId_dbId_ext_tbl,
931 x_value => l_tpa_program_unit_id
932 );
933 EXCEPTION
934 WHEN FND_API.G_EXC_ERROR THEN
935 l_tpa_program_unit_id := NULL;
936 END;
937 --}
938 END IF;
939 --}
940 ELSE
941 --{
942 l_location := '0060';
943 --
944 l_package_id := p_package_id;
945 l_tp_layer_id := p_tp_layer_id;
946 --}
947 END IF;
948 --
949 --
950 l_location := '0070';
951 --
952 IF vea_layer_licenses_sv.isLicensed
953 (
954 p_layer_provider_code => p_layer_provider_code,
955 p_tp_layer_id => l_tp_layer_id
956 )
957 THEN
958 --{
959 l_location := '0080';
960 --
961 l_program_unit_id := NULL;
962 --l_tpa_program_unit_id := 0;
963 g_program_unit_id := NULL;
964 --
965 --
966 l_location := '0090';
967 --
968 FOR program_unit_rec IN program_unit_cur
969 (
970 p_layer_provider_code => p_layer_provider_code,
971 p_program_unit_id => p_id,
972 p_package_id => l_package_id,
973 p_name => p_name
974 )
975 LOOP
976 --{
977 l_location := '0100';
978 --
979 l_program_unit_id := program_unit_rec.program_unit_id;
980 l_db_tpa_program_unit_id := program_unit_rec.tpa_program_unit_id;
981 g_program_unit_id := program_unit_rec.program_unit_id;
982 --}
983 END LOOP;
984 --
985 --
986 l_location := '0110';
987 --
988 IF l_program_unit_id IS NULL
989 THEN
990 --{
991 l_location := '0120';
992 --
993 IF p_layer_provider_code = vea_tpa_util_pvt.g_current_layer_provider_code
994 THEN
995 SELECT NVL( p_id, vea_program_units_s.NEXTVAL )
996 INTO l_program_unit_id
997 FROM DUAL;
998 ELSE
999 SELECT vea_program_units_s.NEXTVAL
1000 INTO l_program_unit_id
1001 FROM DUAL;
1002 END IF;
1003 --
1004 --
1005 l_location := '0130';
1006 --
1007 IF p_tpa_program_unit_id IS NOT NULL
1008 AND l_tpa_program_unit_id IS NULL
1009 THEN
1010 vea_tpa_util_pvt.put
1011 (
1012 p_key => l_program_unit_id,
1013 p_value => p_tpa_program_unit_id,
1014 x_cache_tbl => vea_tpa_util_pvt.g_pend_puId_tpaPUId_tbl,
1015 x_cache_ext_tbl => vea_tpa_util_pvt.g_pend_puId_tpaPUId_ext_tbl
1016 );
1017 END IF;
1018 --
1019 --
1020 l_location := '0140';
1021 --
1022 deleteUnreferencedProgramUnits
1023 (
1024 p_layer_provider_code => p_layer_provider_code,
1025 p_program_unit_id => l_program_unit_id,
1026 p_tpa_program_unit_id => l_tpa_program_unit_id
1027 );
1028 --p_tpa_program_unit_id => p_tpa_program_unit_id
1029 --
1030 --
1031 l_location := '0150';
1032 --
1033 insert_row
1034 (
1035 p_layer_provider_code => p_layer_provider_code,
1036 p_program_unit_id => l_program_unit_id,
1037 p_package_id => l_package_id,
1038 p_program_unit_type => p_program_unit_type,
1039 p_name => p_name,
1040 p_label => p_label,
1041 p_public_flag => p_public_flag,
1042 p_customizable_flag => p_customizable_flag,
1043 p_tps_flag => p_tps_flag,
1044 p_return_type => p_return_type,
1045 p_tpa_program_unit_id => l_tpa_program_unit_id,
1046 --p_tpa_program_unit_id => p_tpa_program_unit_id,
1047 p_description => p_description
1048 );
1049 -- This very important, otherwise insert on parameters will fail
1050 g_program_unit_id := l_program_unit_id;
1051 --}
1052 ELSE
1053 --{
1054 l_location := '0160';
1055 --
1056 IF p_tpa_program_unit_id IS NOT NULL
1057 AND l_tpa_program_unit_id IS NULL
1058 THEN
1059 l_tpa_program_unit_id := l_db_tpa_program_unit_id;
1060 END IF;
1061 --
1062 --
1063 l_location := '0161';
1064 --
1065 IF p_tpa_program_unit_id IS NOT NULL
1066 THEN
1067 --{
1068 l_location := '0170';
1069 --
1070 validateMapping
1071 (
1072 p_layer_provider_code => p_layer_provider_code,
1073 p_program_unit_id => l_program_unit_id,
1074 p_tpa_program_unit_id => l_tpa_program_unit_id,
1075 --p_tpa_program_unit_id => p_tpa_program_unit_id,
1076 p_old_tpa_program_unit_id => l_db_tpa_program_unit_id
1077 );
1078 --}
1079 END IF;
1080 --
1081 --
1082 l_location := '0180';
1083 --
1084 update_row
1085 (
1086 p_layer_provider_code => p_layer_provider_code,
1087 p_program_unit_id => l_program_unit_id,
1088 p_package_id => l_package_id,
1089 p_program_unit_type => p_program_unit_type,
1090 p_name => p_name,
1091 p_label => p_label,
1092 p_public_flag => p_public_flag,
1093 p_customizable_flag => p_customizable_flag,
1094 p_tps_flag => p_tps_flag,
1095 p_return_type => p_return_type,
1096 p_tpa_program_unit_id => l_tpa_program_unit_id,
1097 --p_tpa_program_unit_id => p_tpa_program_unit_id,
1098 p_description => p_description
1099 );
1100 --}
1101 END IF;
1102 --
1103 --
1104 l_location := '0190';
1105 --
1106 x_id := l_program_unit_id;
1107 --
1108 l_location := '0195';
1109 --
1110 --
1111 IF p_id IS NOT NULL
1112 THEN
1113 vea_tpa_util_pvt.put
1114 (
1115 p_key => p_id,
1116 p_value => l_program_unit_id,
1117 x_cache_tbl => vea_tpa_util_pvt.g_PU_fileId_dbId_tbl,
1118 x_cache_ext_tbl => vea_tpa_util_pvt.g_PU_fileId_dbId_ext_tbl
1119 );
1120 END IF;
1121 --
1122 --}
1123 END IF;
1124 --
1125 --
1126 --} API Body
1127 --
1128 --
1129 -- Standard API Footer
1130 --
1131 l_location := '0200';
1132 --
1133 vea_tpa_util_pvt.api_footer
1134 (
1135 p_commit => p_commit,
1136 x_msg_count => x_msg_count,
1137 x_msg_data => x_msg_data
1138 );
1139 --}
1140 EXCEPTION
1141 --{
1142 WHEN FND_API.G_EXC_ERROR
1143 THEN
1144 --{
1145 --RAISE;
1146 vea_tpa_util_pvt.handle_error
1147 (
1148 p_error_type => vea_tpa_util_pvt.G_ERROR,
1149 p_savepoint_name => l_savepoint_name,
1150 p_package_name => G_PACKAGE_NAME,
1151 p_api_name => l_api_name,
1152 p_location => l_location,
1153 x_msg_count => x_msg_count,
1154 x_msg_data => x_msg_data,
1155 x_api_return_status => x_return_status
1156 );
1157 --}
1158 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1159 THEN
1160 --{
1161 --RAISE;
1162 vea_tpa_util_pvt.handle_error
1163 (
1164 p_error_type => vea_tpa_util_pvt.G_UNEXPECTED_ERROR,
1165 p_savepoint_name => l_savepoint_name,
1166 p_package_name => G_PACKAGE_NAME,
1167 p_api_name => l_api_name,
1168 p_location => l_location,
1169 x_msg_count => x_msg_count,
1170 x_msg_data => x_msg_data,
1171 x_api_return_status => x_return_status
1172 );
1173 --}
1174 WHEN OTHERS
1175 THEN
1176 --{
1177 --RAISE;
1178 vea_tpa_util_pvt.handle_error
1179 (
1180 p_error_type => vea_tpa_util_pvt.G_OTHER_ERROR,
1181 p_savepoint_name => l_savepoint_name,
1182 p_package_name => G_PACKAGE_NAME,
1183 p_api_name => l_api_name,
1184 p_location => l_location,
1185 x_msg_count => x_msg_count,
1186 x_msg_data => x_msg_data,
1187 x_api_return_status => x_return_status
1188 );
1189 --}
1190 --}
1191 END process;
1192 --}
1193 END VEA_PROGRAM_UNITS_SV;