[Home] [Help]
PACKAGE BODY: APPS.VEA_PACKAGES_SV
Source
1 PACKAGE BODY VEA_PACKAGES_SV as
2 /* $Header: VEAVAPKB.pls 115.16 2004/07/27 00:09:19 rvishnuv ship $ */
3 --{
4 /*======================== vea_packages_sv =============================*/
5 /*========================================================================
6 PURPOSE: Table handler package for table VEA_PACKAGES
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_PACKAGES_SV';
18 --
19 --
20 /*========================================================================
21
22 PROCEDURE NAME: validateNamingConventions
23
24 PURPOSE: Validates naming conventions for package name and file name.
25
26 ========================================================================*/
27 PROCEDURE
28 validateNamingConventions
29 (
30 p_client_server_flag IN vea_packages.client_server_flag%TYPE,
31 p_name IN vea_packages.name%TYPE,
32 p_specification_filename IN vea_packages.specification_filename%TYPE,
33 p_body_filename IN vea_packages.body_filename%TYPE,
34 p_application_short_name IN vea_packages.application_short_name%TYPE
35 )
36 IS
37 --{
38 l_api_name CONSTANT VARCHAR2(30) := 'validateNamingConventions';
39 l_location VARCHAR2(32767);
40 --
41 --
42 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
43 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
44 --
45 --
46 l_curr_layer_provider_code vea_packages.layer_provider_code%TYPE;
47 l_prefix VARCHAR2(32767);
48 l_package_name_prefix VARCHAR2(32767);
49 l_file_name_prefix VARCHAR2(32767);
50
51 l_file_message_name VARCHAR2(32767);
52 l_package_message_name VARCHAR2(32767);
53 --}
54 BEGIN
55 --{
56 l_location := '0010';
57 --
58 l_curr_layer_provider_code := vea_tpa_util_pvt.get_curr_layer_provider_code;
59 --
60 --
61 l_location := '0020';
62 --
63 IF l_curr_layer_provider_code = vea_tpa_util_pvt.G_ORACLE
64 THEN
65 --{
66 l_location := '0030';
67 --
68 l_prefix := p_application_short_name;
69 --
70 --
71 l_location := '0040';
72 --
73 l_file_message_name := 'VEA_IMPORT_ORCL_FILE_NAME_STD';
74 --
75 --
76 l_location := '0050';
77 --
78 l_package_message_name := 'VEA_IMPORT_ORCL_PKG_NAME_STD';
79 --}
80 ELSE
81 --{
82 l_location := '0060';
83 --
84 l_prefix := l_curr_layer_provider_code;
85 --
86 --
87 l_location := '0070';
88 --
89 l_file_message_name := 'VEA_IMPORT_LP_FILE_NAME_STD';
90 --
91 --
92 l_location := '0080';
93 --
94 l_package_message_name := 'VEA_IMPORT_LP_PKG_NAME_STD';
95 --}
96 END IF;
97 --
98 --
99 l_location := '0090';
100 --
101 l_file_name_prefix := l_prefix
102 || vea_tpa_util_pvt.G_WILD_CARD;
103 --
104 --
105 l_location := '0100';
106 --
107 l_package_name_prefix := l_prefix
108 || '\'
109 || vea_tpa_util_pvt.G_UNDERSCORE
110 || vea_tpa_util_pvt.G_WILD_CARD;
111 --
112 --
113 l_location := '0110';
114 --
115 IF p_name NOT LIKE l_package_name_prefix ESCAPE '\'
116 THEN
117 --{
118 l_location := '0120';
119 --
120 vea_tpa_util_pvt.add_message_and_raise
121 (
122 p_error_name => l_package_message_name,
123 p_token1 => 'PACKAGE_NAME',
124 p_value1 => p_name
125 );
126 --}
127 END IF;
128 --
129 --
130 l_location := '0130';
131 --
132 IF p_specification_filename NOT LIKE l_file_name_prefix
133 THEN
134 --{
135 l_location := '0140';
136 --
137 vea_tpa_util_pvt.add_message_and_raise
138 (
139 p_error_name => l_file_message_name,
140 p_token1 => 'FILE_NAME',
141 p_value1 => p_specification_filename
142 );
143 --}
144 END IF;
145 --
146 --
147 l_location := '0150';
148 --
149 IF p_body_filename IS NOT NULL
150 AND p_body_filename NOT LIKE l_file_name_prefix
151 THEN
152 --{
153 l_location := '0160';
154 --
155 vea_tpa_util_pvt.add_message_and_raise
156 (
157 p_error_name => l_file_message_name,
158 p_token1 => 'FILE_NAME',
159 p_value1 => p_body_filename
160 );
161 --}
162 END IF;
163 --}
164 EXCEPTION
165 --{
166 WHEN FND_API.G_EXC_ERROR
167 THEN
168 --{
169 RAISE;
170 --}
171 WHEN OTHERS
172 THEN
173 --{
174 vea_tpa_util_pvt.add_exc_message_and_raise
175 (
176 p_package_name => G_PACKAGE_NAME,
177 p_api_name => l_api_name,
178 p_location => l_location
179 );
180 --}
181 --}
182 END validateNamingConventions;
183 --
184 --
185 /*========================================================================
186
187 PROCEDURE NAME: validateUniqueServerFileNames
188
189 PURPOSE: Validates a record of VEA_PACKAGES table
190
191 ========================================================================*/
192 PROCEDURE
193 validateUniqueServerFileNames
194 (
195 p_layer_provider_code IN vea_packages.layer_provider_code%TYPE,
196 p_package_id IN vea_packages.package_id%TYPE,
197 p_name IN vea_packages.name%TYPE,
198 p_specification_filename IN vea_packages.specification_filename%TYPE,
199 p_body_filename IN vea_packages.body_filename%TYPE
200 )
201 IS
202 --{
203 l_api_name CONSTANT VARCHAR2(30) := 'validateUniqueServerFileNames';
204 l_location VARCHAR2(32767);
205 --
206 --
207 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
208 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
209 --
210 --
211 CURSOR pkg_spec_file_cur
212 (
213 p_layer_provider_code IN vea_packages.layer_provider_code%TYPE,
214 p_specification_filename IN vea_packages.specification_filename%TYPE
215 )
216 IS
217 SELECT package_id, name
218 FROM vea_packages
219 WHERE specification_filename = p_specification_filename
220 AND layer_provider_code = p_layer_provider_code;
221 --
222 --
223 CURSOR pkg_body_file_cur
224 (
225 p_layer_provider_code IN vea_packages.layer_provider_code%TYPE,
226 p_body_filename IN vea_packages.body_filename%TYPE
227 )
228 IS
229 SELECT package_id, name
230 FROM vea_packages
231 WHERE body_filename = p_body_filename
232 AND layer_provider_code = p_layer_provider_code;
233 --}
234 BEGIN
235 --{
236 l_location := '0010';
237 --
238 FOR pkg_spec_file_rec IN pkg_spec_file_cur
239 (
240 p_layer_provider_code => p_layer_provider_code,
241 p_specification_filename => p_specification_filename
242 )
243 LOOP
244 --{
245 l_location := '0020';
246 --
247 IF pkg_spec_file_rec.package_id <> p_package_id
248 THEN
249 --{
250 l_location := '0030';
251 --
252 vea_tpa_util_pvt.add_message_and_raise
253 (
254 p_error_name => 'VEA_IMPORT_UNIQ_SPEC_FILE',
255 p_token1 => 'FILE_NAME',
256 p_value1 => p_specification_filename,
257 p_token2 => 'PACKAGE_NAME',
258 p_value2 => pkg_spec_file_rec.name,
259 p_token3 => 'SVR_PACKAGE_NAME',
260 p_value3 => p_name
261 );
262 --}
263 END IF;
264 --}
265 END LOOP;
266 --
267 --
268 l_location := '0040';
269 --
270 IF p_body_filename IS NOT NULL
271 THEN
272 --{
273 l_location := '0050';
274 --
275 FOR pkg_body_file_rec IN pkg_body_file_cur
276 (
277 p_layer_provider_code => p_layer_provider_code,
278 p_body_filename => p_body_filename
279 )
280 LOOP
281 --{
282 l_location := '0060';
283 --
284 IF pkg_body_file_rec.package_id <> p_package_id
285 THEN
286 --{
287 l_location := '0070';
288 --
289 vea_tpa_util_pvt.add_message_and_raise
290 (
291 p_error_name => 'VEA_IMPORT_UNIQ_BODY_FILE',
292 p_token1 => 'FILE_NAME',
293 p_value1 => p_body_filename,
294 p_token2 => 'PACKAGE_NAME',
295 p_value2 => pkg_body_file_rec.name,
296 p_token3 => 'SVR_PACKAGE_NAME',
297 p_value3 => p_name
298 );
299 --}
300 END IF;
301 --}
302 END LOOP;
303 --}
304 END IF;
305 --}
306 EXCEPTION
307 --{
308 WHEN FND_API.G_EXC_ERROR
309 THEN
310 --{
311 RAISE;
312 --}
313 WHEN OTHERS
314 THEN
315 --{
316 vea_tpa_util_pvt.add_exc_message_and_raise
317 (
318 p_package_name => G_PACKAGE_NAME,
319 p_api_name => l_api_name,
320 p_location => l_location
321 );
322 --}
323 --}
324 END validateUniqueServerFileNames;
325 --
326 --
327 --
328 /*========================================================================
329
330 PROCEDURE NAME: validateUniqueClientFileNames
331
332 PURPOSE: Validates a record of VEA_PACKAGES table
333
334 ========================================================================*/
335 PROCEDURE
336 validateUniqueClientFileNames
337 (
338 p_layer_provider_code IN vea_packages.layer_provider_code%TYPE,
339 p_package_id IN vea_packages.package_id%TYPE,
340 p_name IN vea_packages.name%TYPE,
341 p_specification_filename IN vea_packages.specification_filename%TYPE,
342 p_application_short_name IN vea_packages.application_short_name%TYPE
343 )
344 IS
345 --{
346 l_api_name CONSTANT VARCHAR2(30) := 'validateUniqueClientFileNames';
347 l_location VARCHAR2(32767);
348 --
349 --
350 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
351 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
352 --
353 --
354 CURSOR pkg_spec_file_cur
355 (
356 p_layer_provider_code IN vea_packages.layer_provider_code%TYPE,
357 p_specification_filename IN vea_packages.specification_filename%TYPE
358 )
359 IS
360 SELECT package_id, application_short_name
361 FROM vea_packages
362 WHERE specification_filename = p_specification_filename
363 AND layer_provider_code = p_layer_provider_code;
364 --}
365 BEGIN
366 --{
367 l_location := '0010';
368 --
369 FOR pkg_spec_file_rec IN pkg_spec_file_cur
370 (
371 p_layer_provider_code => p_layer_provider_code,
372 p_specification_filename => p_specification_filename
373 )
374 LOOP
375 --{
376 l_location := '0020';
377 --
378 IF pkg_spec_file_rec.package_id <> p_package_id
379 AND pkg_spec_file_rec.application_short_name <> p_application_short_name
380 THEN
381 --{
382 l_location := '0030';
383 --
384 vea_tpa_util_pvt.add_message_and_raise
385 (
386 p_error_name => 'VEA_IMPORT_UNIQ_CLIENT_FILE',
387 p_token1 => 'FILE_NAME',
388 p_value1 => p_specification_filename,
389 p_token2 => 'APP_SHORT_NAME',
390 p_value2 => pkg_spec_file_rec.application_short_name,
391 p_token3 => 'SVR_PACKAGE_NAME',
392 p_value3 => p_name
393 );
394 --}
395 END IF;
396 --}
397 END LOOP;
398 --}
399 EXCEPTION
400 --{
401 WHEN FND_API.G_EXC_ERROR
402 THEN
403 --{
404 RAISE;
405 --}
406 WHEN OTHERS
407 THEN
408 --{
409 vea_tpa_util_pvt.add_exc_message_and_raise
410 (
411 p_package_name => G_PACKAGE_NAME,
412 p_api_name => l_api_name,
413 p_location => l_location
414 );
415 --}
416 --}
417 END validateUniqueClientFileNames;
418 --
419 --
420 /*========================================================================
421
422 PROCEDURE NAME: validate
423
424 PURPOSE: Validates a record of VEA_PACKAGES table
425
426 ========================================================================*/
427 PROCEDURE
428 validate
429 (
430 p_layer_provider_code IN vea_packages.layer_provider_code%TYPE,
431 p_package_id IN vea_packages.package_id%TYPE,
432 p_client_server_flag IN vea_packages.client_server_flag%TYPE,
433 p_generate_flag IN vea_packages.generate_flag%TYPE,
434 p_tpa_flag IN vea_packages.tpa_flag%TYPE,
435 p_name IN vea_packages.name%TYPE,
436 p_specification_filename IN vea_packages.specification_filename%TYPE,
437 p_body_filename IN vea_packages.body_filename%TYPE,
438 p_label IN vea_packages.label%TYPE,
439 p_version_number IN vea_packages.version_number%TYPE,
440 p_description IN vea_packages.description%TYPE,
441 p_application_short_name IN vea_packages.application_short_name%TYPE,
442 p_tp_layer_id IN vea_packages.tp_layer_id%TYPE
443 )
444 IS
445 --{
446 l_api_name CONSTANT VARCHAR2(30) := 'validate';
447 l_location VARCHAR2(32767);
448 --
449 --
450 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
451 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
452 --}
453 BEGIN
454 --{
455 l_location := '0010';
456 --
457 IF vea_tpa_util_pvt.validate
458 AND NOT(vea_tpa_util_pvt.isLayerMergeOn)
459 THEN
460 --{
461 l_location := '0020';
462 --
463 validateNamingConventions
464 (
465 p_client_server_flag => p_client_server_flag,
466 p_name => p_name,
467 p_specification_filename => p_specification_filename,
468 p_body_filename => p_body_filename,
469 p_application_short_name => p_application_short_name
470 );
471 --
472 --
473 l_location := '0030';
474 --
475 IF p_client_server_flag = 'S'
476 THEN
477 --{
478 l_location := '0040';
479 --
480 validateUniqueServerFileNames
481 (
482 p_layer_provider_code => p_layer_provider_code,
483 p_package_id => p_package_id,
484 p_name => p_name,
485 p_specification_filename => p_specification_filename,
486 p_body_filename => p_body_filename
487 );
488 --}
489 ELSE
490 --{
491 l_location := '0050';
492 --
493 validateUniqueClientFileNames
494 (
495 p_layer_provider_code => p_layer_provider_code,
496 p_package_id => p_package_id,
497 p_name => p_name,
498 p_specification_filename => p_specification_filename,
499 p_application_short_name => p_application_short_name
500 );
501 --}
502 END IF;
503 --}
504 END IF;
505 --}
506 EXCEPTION
507 --{
508 WHEN FND_API.G_EXC_ERROR
509 THEN
510 --{
511 RAISE;
512 --}
513 WHEN OTHERS
514 THEN
515 --{
516 vea_tpa_util_pvt.add_exc_message_and_raise
517 (
518 p_package_name => G_PACKAGE_NAME,
519 p_api_name => l_api_name,
520 p_location => l_location
521 );
522 --}
523 --}
524 END validate;
525 --
526 --
527 /*========================================================================
528
529 PROCEDURE NAME: insert_row
530
531 PURPOSE: Inserts a record into VEA_PACKAGES table
532
533 ========================================================================*/
534 PROCEDURE
535 insert_row
536 (
537 p_layer_provider_code IN vea_packages.layer_provider_code%TYPE,
538 p_package_id IN vea_packages.package_id%TYPE,
539 p_client_server_flag IN vea_packages.client_server_flag%TYPE,
540 p_generate_flag IN vea_packages.generate_flag%TYPE,
541 p_tpa_flag IN vea_packages.tpa_flag%TYPE,
542 p_name IN vea_packages.name%TYPE,
543 p_specification_filename IN vea_packages.specification_filename%TYPE,
544 p_body_filename IN vea_packages.body_filename%TYPE,
545 p_label IN vea_packages.label%TYPE,
546 p_version_number IN vea_packages.version_number%TYPE,
547 p_description IN vea_packages.description%TYPE,
548 p_application_short_name IN vea_packages.application_short_name%TYPE,
549 p_tp_layer_id IN vea_packages.tp_layer_id%TYPE
550 )
551 IS
552 --{
553 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
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 BEGIN
561 --{
562 l_location := '0010';
563 --
564 INSERT INTO vea_packages
565 (
566 layer_provider_code, package_id,
567 client_server_flag,
568 name, label,
569 generate_flag, tpa_flag,
570 specification_filename, body_filename,
571 --version_number,
572 description,
573 application_short_name, tp_layer_id,
574 created_by, creation_date,
575 last_updated_by, last_update_date,
576 last_update_login
577 )
578 VALUES
579 (
580 p_layer_provider_code, p_package_id,
581 p_client_server_flag,
582 UPPER(p_name), p_label,
583 p_generate_flag, p_tpa_flag,
584 p_specification_filename, p_body_filename,
585 --p_version_number,
586 p_description,
587 UPPER(p_application_short_name), p_tp_layer_id,
588 l_user_id, SYSDATE,
589 l_user_id, SYSDATE,
590 l_login_id
591 );
592 --}
593 EXCEPTION
594 --{
595 WHEN OTHERS
596 THEN
597 --{
598 vea_tpa_util_pvt.add_exc_message_and_raise
599 (
600 p_package_name => G_PACKAGE_NAME,
601 p_api_name => l_api_name,
602 p_location => l_location
603 );
604 --}
605 --}
606 END insert_row;
607 --
608 --
609 /*========================================================================
610
611 PROCEDURE NAME: update_row
612
613 PURPOSE: Updates a record into VEA_PACKAGES table
614
615 ========================================================================*/
616 PROCEDURE
617 update_row
618 (
619 p_layer_provider_code IN vea_packages.layer_provider_code%TYPE,
620 p_package_id IN vea_packages.package_id%TYPE,
621 p_client_server_flag IN vea_packages.client_server_flag%TYPE,
622 p_generate_flag IN vea_packages.generate_flag%TYPE,
623 p_tpa_flag IN vea_packages.tpa_flag%TYPE,
624 p_name IN vea_packages.name%TYPE,
625 p_specification_filename IN vea_packages.specification_filename%TYPE,
626 p_body_filename IN vea_packages.body_filename%TYPE,
627 p_label IN vea_packages.label%TYPE,
628 p_version_number IN vea_packages.version_number%TYPE,
629 p_description IN vea_packages.description%TYPE,
630 p_application_short_name IN vea_packages.application_short_name%TYPE,
631 p_tp_layer_id IN vea_packages.tp_layer_id%TYPE
632 )
633 IS
634 --{
635 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
636 l_location VARCHAR2(32767);
637 --
638 --
639 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
640 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
641 --}
642 BEGIN
643 --{
644 l_location := '0010';
645 --
646 UPDATE vea_packages
647 SET client_server_flag = p_client_server_flag,
648 name = p_name,
649 label = p_label,
650 generate_flag = p_generate_flag,
651 tpa_flag = p_tpa_flag,
652 specification_filename = p_specification_filename,
653 body_filename = p_body_filename,
654 --version_number = p_version_number,
655 description = p_description,
656 application_short_name = p_application_short_name,
657 tp_layer_id = p_tp_layer_id,
658 last_updated_by = l_user_id,
659 last_update_date = SYSDATE,
660 last_update_login = l_login_id
661 WHERE layer_provider_code = p_layer_provider_code
662 AND package_id = p_package_id;
663 --}
664 EXCEPTION
665 --{
666 WHEN OTHERS
667 THEN
668 --{
669 vea_tpa_util_pvt.add_exc_message_and_raise
670 (
671 p_package_name => G_PACKAGE_NAME,
672 p_api_name => l_api_name,
673 p_location => l_location
674 );
675 --}
676 --}
677 END update_row;
678 --
679 --
680 /*========================================================================
681
682 PROCEDURE NAME: deleteUnreferencedPackages
683
684 PURPOSE: Deletes all TPA packages which do not have any program units.
685 ========================================================================*/
686 PROCEDURE
687 deleteUnreferencedPackages
688 IS
689 --{
690 l_api_name CONSTANT VARCHAR2(30) := 'deleteUnreferencedPackages';
691 l_location VARCHAR2(32767);
692 --
693 --
694 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
695 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
696 --
697 --
698 --}
699 BEGIN
700 --{
701 l_location := '0010';
702 --
703 IF vea_tpa_util_pvt.get_curr_layer_provider_code = vea_tpa_util_pvt.G_ORACLE
704 THEN
705 --{
706 delete vea_packages PK
707 where tpa_flag = 'Y'
708 and not exists ( select 1
709 from vea_program_units PU
710 where PU.layer_provider_code = PK.layer_provider_code
711 AND PU.package_id = PK.package_id
712 );
713 --}
714 END IF;
715 --}
716 EXCEPTION
717 --{
718 WHEN OTHERS
719 THEN
720 --{
721 vea_tpa_util_pvt.add_exc_message_and_raise
722 (
723 p_package_name => G_PACKAGE_NAME,
724 p_api_name => l_api_name,
725 p_location => l_location
726 );
727 --}
728 --}
729 END deleteUnreferencedPackages;
730 --
731 --
732 /*========================================================================
733
734 PROCEDURE NAME: delete_rows
735
736 PURPOSE: Deletes all packages developed by specified layer provider and
737 used in the specified TP layer of any customizable program
738 units of the specified application.
739
740 It first queries all packages developed by specified layer
741 provider and belonging to the specified TP Layer.
742
743 For each package,
744 - it deletes all program units ( and their parameters ),
745 if
746 - it unit is not a TPS program unit
747 AND it is used in the specified TP layer in any
748 customizable program units of the specified application.
749 OR
750 - if it is a TPS program unit and not used anywhere.
751 - It deletes the package, if it has no more program units.
752 - It updates the TP_LAYER_ID to null, if it has only TPS
753 program units.
754 ========================================================================*/
755 PROCEDURE
756 delete_rows
757 (
758 p_layer_provider_code IN vea_layer_headers.layer_provider_code%TYPE,
759 p_tp_layer_id IN vea_tp_layers.tp_layer_id%TYPE,
760 p_application_short_name IN vea_packages.application_short_name%TYPE,
761 x_package_count OUT NOCOPY NUMBER
762 )
763 IS
764 --{
765 l_api_name CONSTANT VARCHAR2(30) := 'delete_rows';
766 l_location VARCHAR2(32767);
767 --
768 --
769 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
770 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
771 --
772 --
773 CURSOR package_cur
774 (
775 p_layer_provider_code IN vea_layer_headers.layer_provider_code%TYPE,
776 p_tp_layer_id IN vea_tp_layers.tp_layer_id%TYPE
777 )
778 IS
779 SELECT package_id
780 FROM vea_packages
781 WHERE layer_provider_code = p_layer_provider_code
782 AND tp_layer_id = p_tp_layer_id;
783 --
784 --
785 l_program_unit_count NUMBER;
786 l_tps_program_unit_count NUMBER;
787 l_package_count NUMBER;
788 --
789 --
790 --}
791 BEGIN
792 --{
793 l_location := '0010';
794 --
795 l_package_count := 0;
796 --
797 --
798 l_location := '0020';
799 --
800 FOR package_rec IN package_cur
801 (
802 p_layer_provider_code => p_layer_provider_code,
803 p_tp_layer_id => p_tp_layer_id
804 )
805 LOOP
806 --{
807 l_location := '0030';
808 --
809 vea_program_units_sv.delete_rows
810 (
811 p_layer_provider_code => p_layer_provider_code,
812 p_tp_layer_id => p_tp_layer_id,
813 p_package_id => package_rec.package_id,
814 p_application_short_name => p_application_short_name,
815 x_program_unit_count => l_program_unit_count,
816 x_tps_program_unit_count => l_tps_program_unit_count
817 );
818 --
819 --
820 l_location := '0040';
821 --
822 IF l_program_unit_count = 0
823 THEN
824 --{
825 l_location := '0050';
826 --
827 DELETE vea_packages
828 WHERE layer_provider_code = p_layer_provider_code
829 AND package_id = package_rec.package_id;
830 --}
831 ELSE
832 --{
833 l_location := '0060';
834 --
835 IF l_program_unit_count = l_tps_program_unit_count
836 THEN
837 --{
838 l_location := '0070';
839 --
840 UPDATE vea_packages
841 SET tp_layer_id = null
842 WHERE layer_provider_code = p_layer_provider_code
843 AND package_id = package_rec.package_id;
844 --}
845 ELSE
846 --{
847 l_package_count := l_package_count + 1;
848 --}
849 END IF;
850 --}
851 END IF;
852 --}
853 END LOOP;
854 --
855 --
856 l_location := '0080';
857 --
858 x_package_count := NVL(l_package_count,0);
859 --}
860 EXCEPTION
861 --{
862 WHEN OTHERS
863 THEN
864 --{
865 vea_tpa_util_pvt.add_exc_message_and_raise
866 (
867 p_package_name => G_PACKAGE_NAME,
868 p_api_name => l_api_name,
869 p_location => l_location
870 );
871 --}
872 --}
873 END delete_rows;
874 --
875 --
876 /*========================================================================
877
878 PROCEDURE NAME: getVersionNumber
879
880 PURPOSE: Increment the input version number by 1. Returns '115.0' if
881 input version number is NULL.
882
883 ========================================================================*/
884 FUNCTION
885 getVersionNumber
886 (
887 p_version_number IN vea_packages.version_number%TYPE
888 )
889 RETURN VARCHAR2
890 IS
891 --{
892 l_api_name CONSTANT VARCHAR2(30) := 'getVersionNumber';
893 l_location VARCHAR2(32767);
894 --
895 --
896 l_pos NUMBER;
897 --}
898 BEGIN
899 --{
900 l_location := '0010';
901 --
902 IF p_version_number is NULL
903 THEN
904 RETURN('115.0');
905 END IF;
906 --
907 --
908 l_location := '0020';
909 --
910 l_pos := INSTR( p_version_number, '.' );
911 --
912 --
913 l_location := '0030';
914 --
915 RETURN(
916 SUBSTR(p_version_number,1,l_pos)
917 || ( SUBSTR(p_version_number,l_pos+1) + 1)
918 );
919 --}
920 EXCEPTION
921 --{
922 WHEN OTHERS
923 THEN
924 --{
925 vea_tpa_util_pvt.add_exc_message_and_raise
926 (
927 p_package_name => G_PACKAGE_NAME,
928 p_api_name => l_api_name,
929 p_location => l_location
930 );
931 --}
932 --}
933 END getVersionNumber;
934 --
935 --
936 /*========================================================================
937
938 PROCEDURE NAME: updateVersionNumber
939
940 PURPOSE: Increments version number by 1 for the specified package.
941
942 ========================================================================*/
943 PROCEDURE
944 updateVersionNumber
945 (
946 p_api_version IN NUMBER,
947 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
948 p_commit IN VARCHAR2 := FND_API.G_FALSE,
949 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
950 x_return_status OUT NOCOPY VARCHAR2,
951 x_msg_count OUT NOCOPY NUMBER,
952 x_msg_data OUT NOCOPY VARCHAR2,
953 p_layer_provider_code IN vea_packages.layer_provider_code%TYPE,
954 p_package_id IN vea_packages.package_id%TYPE,
955 x_version_number OUT NOCOPY VARCHAR2,
956 p_user_name IN VARCHAR2
957 )
958 IS
959 --{
960 l_api_name CONSTANT VARCHAR2(30) := 'updateVersionNumber';
961 l_api_version CONSTANT NUMBER := 1.0;
962 l_api_type CONSTANT VARCHAR2(3) := vea_tpa_util_pvt.G_PUBLIC_API;
963 --
964 --
965 l_location VARCHAR2(32767);
966 l_savepoint_name VARCHAR2(30);
967 --
968 --
969 CURSOR pkg_cur
970 IS
971 SELECT version_number, name
972 FROM vea_packages
973 WHERE layer_provider_code = p_layer_provider_code
974 AND package_id = p_package_id;
975 --
976 --
977 l_version_number vea_packages.version_number%TYPE;
978 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
979 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
980
981 l_version_text VARCHAR2(200);
982
983 --}
984 BEGIN
985 --{
986 --
987 -- Standard API Header
988 --
989 l_location := '0010';
990 --
991 vea_tpa_util_pvt.api_header
992 (
993 p_package_name => G_PACKAGE_NAME,
994 p_api_name => l_api_name,
995 p_api_type => l_api_type,
996 p_api_current_version => l_api_version,
997 p_api_caller_version => p_api_version,
998 p_init_msg_list => p_init_msg_list,
999 x_savepoint_name => l_savepoint_name,
1000 x_api_return_status => x_return_status
1001 );
1002 --
1003 --
1004 --{ API Body
1005
1006
1007 --
1008 --
1009 l_location := '0020';
1010 --
1011 FOR pkg_rec IN pkg_cur
1012 LOOP
1013 --{
1014 l_location := '0030';
1015 --
1016 begin
1017 --
1018 select SUBSTRB(text, INSTRB(text, ' ',1,3) + 1 , INSTRB(text, ' ',1,4) - INSTRB(text, ' ',1,3))
1019 into l_version_text
1020 from all_source
1021 where name = pkg_rec.name
1022 and type ='PACKAGE'
1023 and owner = UPPER(p_user_name)
1024 and text like '%$Head%';
1025 --
1026 pkg_rec.version_number := l_version_text;
1027 --
1028 exception when others
1029 then
1030 null;
1031 --
1032 end;
1033 --
1034 l_version_number := getVersionNumber(pkg_rec.version_number);
1035 --
1036 --
1037 l_location := '0040';
1038 --
1039 UPDATE vea_packages
1040 SET version_number = l_version_number,
1041 last_updated_by = l_user_id,
1042 last_update_date = SYSDATE,
1043 last_update_login = l_login_id
1044 WHERE layer_provider_code = p_layer_provider_code
1045 AND package_id = p_package_id;
1046 --}
1047 END LOOP;
1048 --
1049 --
1050 l_location := '0050';
1051 --
1052 x_version_number := l_version_number;
1053 --
1054 --
1055 --} API Body
1056 --
1057 --
1058 -- Standard API Footer
1059 --
1060 l_location := '0060';
1061 --
1062 vea_tpa_util_pvt.api_footer
1063 (
1064 p_commit => p_commit,
1065 x_msg_count => x_msg_count,
1066 x_msg_data => x_msg_data
1067 );
1068 --}
1069 EXCEPTION
1070 --{
1071 WHEN FND_API.G_EXC_ERROR
1072 THEN
1073 --{
1074 --RAISE;
1075 vea_tpa_util_pvt.handle_error
1076 (
1077 p_error_type => vea_tpa_util_pvt.G_ERROR,
1078 p_savepoint_name => l_savepoint_name,
1079 p_package_name => G_PACKAGE_NAME,
1080 p_api_name => l_api_name,
1081 p_location => l_location,
1082 x_msg_count => x_msg_count,
1083 x_msg_data => x_msg_data,
1084 x_api_return_status => x_return_status
1085 );
1086 --}
1087 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1088 THEN
1089 --{
1090 --RAISE;
1091 vea_tpa_util_pvt.handle_error
1092 (
1093 p_error_type => vea_tpa_util_pvt.G_UNEXPECTED_ERROR,
1094 p_savepoint_name => l_savepoint_name,
1095 p_package_name => G_PACKAGE_NAME,
1096 p_api_name => l_api_name,
1097 p_location => l_location,
1098 x_msg_count => x_msg_count,
1099 x_msg_data => x_msg_data,
1100 x_api_return_status => x_return_status
1101 );
1102 --}
1103 WHEN OTHERS
1104 THEN
1105 --{
1106 --RAISE;
1107 vea_tpa_util_pvt.handle_error
1108 (
1109 p_error_type => vea_tpa_util_pvt.G_OTHER_ERROR,
1110 p_savepoint_name => l_savepoint_name,
1111 p_package_name => G_PACKAGE_NAME,
1112 p_api_name => l_api_name,
1113 p_location => l_location,
1114 x_msg_count => x_msg_count,
1115 x_msg_data => x_msg_data,
1116 x_api_return_status => x_return_status
1117 );
1118 --}
1119 --}
1120 END updateVersionNumber;
1121 --
1122 --
1123 /*========================================================================
1124
1125 PROCEDURE NAME: updateVersionNumber
1126
1127 PURPOSE: Increments version number by 1 for all the packages in the
1128 specified client-side library file.
1129
1130 ========================================================================*/
1131 PROCEDURE
1132 updateVersionNumber
1133 (
1134 p_api_version IN NUMBER,
1135 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1136 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1137 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1138 x_return_status OUT NOCOPY VARCHAR2,
1139 x_msg_count OUT NOCOPY NUMBER,
1140 x_msg_data OUT NOCOPY VARCHAR2,
1141 p_application_short_name IN vea_packages.application_short_name%TYPE,
1142 p_specification_filename IN vea_packages.specification_filename%TYPE,
1143 x_version_number OUT NOCOPY VARCHAR2,
1144 p_user_name IN VARCHAR2
1145 )
1146 IS
1147 --{
1148 l_api_name CONSTANT VARCHAR2(30) := 'updateVersionNumber';
1149 l_api_version CONSTANT NUMBER := 1.0;
1150 l_api_type CONSTANT VARCHAR2(3) := vea_tpa_util_pvt.G_PUBLIC_API;
1151 --
1152 --
1153 l_location VARCHAR2(32767);
1154 l_savepoint_name VARCHAR2(30);
1155 --
1156 --
1157 CURSOR pkg_cur
1158 IS
1159 SELECT package_id,
1160 layer_provider_code,
1161 version_number,
1162 name
1163 FROM vea_packages
1164 WHERE specification_filename = p_specification_filename;
1165
1166 --
1167 --
1168 l_version_number vea_packages.version_number%TYPE;
1169 l_user_id NUMBER := vea_tpa_util_pvt.get_user_id;
1170 l_login_id NUMBER := vea_tpa_util_pvt.get_login_id;
1171 --
1172 --
1173 l_version_text VARCHAR2(200);
1174 --}
1175 BEGIN
1176 --{
1177 --
1178 -- Standard API Header
1179 --
1180 l_location := '0010';
1181 --
1182 vea_tpa_util_pvt.api_header
1183 (
1184 p_package_name => G_PACKAGE_NAME,
1185 p_api_name => l_api_name,
1186 p_api_type => l_api_type,
1187 p_api_current_version => l_api_version,
1188 p_api_caller_version => p_api_version,
1189 p_init_msg_list => p_init_msg_list,
1190 x_savepoint_name => l_savepoint_name,
1191 x_api_return_status => x_return_status
1192 );
1193 --
1194 --
1195 --{ API Body
1196 --
1197 --
1198 l_location := '0020';
1199 --
1200 FOR pkg_rec IN pkg_cur
1201 LOOP
1202 --{
1203 l_location := '0030';
1204 --
1205 begin
1206 --
1207 select SUBSTRB(text, INSTRB(text, ' ',1,3) + 1 , INSTRB(text, ' ',1,4) - INSTRB(text, ' ',1,3))
1208 into l_version_text
1209 from all_source
1210 where name = pkg_rec.name
1211 and type ='PACKAGE'
1212 and owner = UPPER(p_user_name)
1213 and text like '%$Head%';
1214 --
1215 pkg_rec.version_number := l_version_text;
1216 --
1217 exception when others
1218 then
1219 null;
1220 --
1221 end;
1222 --
1223 l_version_number := getVersionNumber(pkg_rec.version_number);
1224 --
1225 --
1226 l_location := '0040';
1227 --
1228 UPDATE vea_packages
1229 SET version_number = l_version_number,
1230 last_updated_by = l_user_id,
1231 last_update_date = SYSDATE,
1232 last_update_login = l_login_id
1233 WHERE layer_provider_code = pkg_rec.layer_provider_code
1234 AND package_id = pkg_rec.package_id;
1235 --}
1236 END LOOP;
1237 --
1238 --
1239 l_location := '0050';
1240 --
1241 x_version_number := l_version_number;
1242 --
1243 --
1244 --} API Body
1245 --
1246 --
1247 -- Standard API Footer
1248 --
1249 l_location := '0060';
1250 --
1251 vea_tpa_util_pvt.api_footer
1252 (
1253 p_commit => p_commit,
1254 x_msg_count => x_msg_count,
1255 x_msg_data => x_msg_data
1256 );
1257 --}
1258 EXCEPTION
1259 --{
1260 WHEN FND_API.G_EXC_ERROR
1261 THEN
1262 --{
1263 --RAISE;
1264 vea_tpa_util_pvt.handle_error
1265 (
1266 p_error_type => vea_tpa_util_pvt.G_ERROR,
1267 p_savepoint_name => l_savepoint_name,
1268 p_package_name => G_PACKAGE_NAME,
1269 p_api_name => l_api_name,
1270 p_location => l_location,
1271 x_msg_count => x_msg_count,
1272 x_msg_data => x_msg_data,
1273 x_api_return_status => x_return_status
1274 );
1275 --}
1276 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1277 THEN
1278 --{
1279 --RAISE;
1280 vea_tpa_util_pvt.handle_error
1281 (
1282 p_error_type => vea_tpa_util_pvt.G_UNEXPECTED_ERROR,
1283 p_savepoint_name => l_savepoint_name,
1284 p_package_name => G_PACKAGE_NAME,
1285 p_api_name => l_api_name,
1286 p_location => l_location,
1287 x_msg_count => x_msg_count,
1288 x_msg_data => x_msg_data,
1289 x_api_return_status => x_return_status
1290 );
1291 --}
1292 WHEN OTHERS
1293 THEN
1294 --{
1295 --RAISE;
1296 vea_tpa_util_pvt.handle_error
1297 (
1298 p_error_type => vea_tpa_util_pvt.G_OTHER_ERROR,
1299 p_savepoint_name => l_savepoint_name,
1300 p_package_name => G_PACKAGE_NAME,
1301 p_api_name => l_api_name,
1302 p_location => l_location,
1303 x_msg_count => x_msg_count,
1304 x_msg_data => x_msg_data,
1305 x_api_return_status => x_return_status
1306 );
1307 --}
1308 --}
1309 END updateVersionNumber;
1310 --
1311 --
1312 /*========================================================================
1313
1314 PROCEDURE NAME: getId
1315
1316 PURPOSE:
1317
1318 ========================================================================*/
1319 FUNCTION
1320 getId
1321 (
1322 p_layer_provider_code IN vea_packages.layer_provider_code%TYPE,
1323 p_client_server_flag IN vea_packages.client_server_flag%TYPE,
1324 p_name IN vea_packages.name%TYPE,
1325 p_application_short_name IN vea_packages.application_short_name%TYPE
1326 )
1327 RETURN NUMBER
1328 IS
1329 --{
1330 l_api_name CONSTANT VARCHAR2(30) := 'getId';
1331 l_location VARCHAR2(32767);
1332 --
1333 --
1334 CURSOR package_cur
1335 (
1336 p_layer_provider_code IN vea_packages.layer_provider_code%TYPE,
1337 p_application_short_name IN vea_packages.application_short_name%TYPE,
1338 p_client_server_flag IN vea_packages.client_server_flag%TYPE,
1339 p_name IN vea_packages.name%TYPE
1340 )
1341 IS
1342 SELECT package_id
1343 FROM vea_packages
1344 WHERE layer_provider_code = p_layer_provider_code
1345 AND application_short_name = p_application_short_name
1346 AND client_server_flag = p_client_server_flag
1347 AND UPPER(name) = UPPER(p_name);
1348 --}
1349 BEGIN
1350 --{
1351 l_location := '0010';
1352 --
1353 FOR package_rec IN package_cur
1354 (
1355 p_layer_provider_code => p_layer_provider_code,
1356 p_application_short_name => p_application_short_name,
1357 p_client_server_flag => p_client_server_flag,
1358 p_name => p_name
1359 )
1360 LOOP
1361 --{
1362 l_location := '0020';
1363 --
1364 RETURN (package_rec.package_id);
1365 --}
1366 END LOOP;
1367 --
1368 --
1369 RETURN(NULL);
1370 --}
1371 EXCEPTION
1372 --{
1373 WHEN OTHERS
1374 THEN
1375 --{
1376 RAISE;
1377 --}
1378 --}
1379 END getId;
1380 --
1381 --
1382 /*========================================================================
1383
1384 PROCEDURE NAME: process
1385
1386 PURPOSE: Table hadndler API for VEA_PACKAGES table.
1387
1388 It inserts/updates a record in VEA_PACKAGES table.
1389
1390 ========================================================================*/
1391 PROCEDURE
1392 process
1393 (
1394 p_api_version IN NUMBER,
1395 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1396 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1397 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1398 x_return_status OUT NOCOPY VARCHAR2,
1399 x_msg_count OUT NOCOPY NUMBER,
1400 x_msg_data OUT NOCOPY VARCHAR2,
1401 x_id OUT NOCOPY vea_packages.package_id%TYPE,
1402 p_layer_provider_code IN vea_packages.layer_provider_code%TYPE,
1403 p_client_server_flag IN vea_packages.client_server_flag%TYPE,
1404 p_generate_flag IN vea_packages.generate_flag%TYPE,
1405 p_tpa_flag IN vea_packages.tpa_flag%TYPE,
1406 p_name IN vea_packages.name%TYPE,
1407 p_specification_filename IN vea_packages.specification_filename%TYPE,
1408 p_body_filename IN vea_packages.body_filename%TYPE,
1409 p_label IN vea_packages.label%TYPE,
1410 p_version_number IN vea_packages.version_number%TYPE,
1411 p_description IN vea_packages.description%TYPE,
1412 p_application_short_name IN vea_packages.application_short_name%TYPE,
1413 p_tp_layer_id IN vea_packages.tp_layer_id%TYPE,
1414 p_id IN vea_packages.package_id%TYPE := NULL
1415 )
1416 IS
1417 --{
1418 l_api_name CONSTANT VARCHAR2(30) := 'process';
1419 l_api_version CONSTANT NUMBER := 1.0;
1420 l_api_type CONSTANT VARCHAR2(3) := vea_tpa_util_pvt.G_PUBLIC_API;
1421 --
1422 --
1423 l_location VARCHAR2(32767);
1424 l_savepoint_name VARCHAR2(30);
1425 l_package_id vea_packages.package_id%TYPE;
1426 l_tp_layer_id vea_tp_layers.tp_layer_id%TYPE;
1427 --
1428 --
1429 CURSOR package_cur
1430 (
1431 p_layer_provider_code IN vea_packages.layer_provider_code%TYPE,
1432 --p_package_id IN vea_packages.package_id%TYPE,
1433 p_application_short_name IN vea_packages.application_short_name%TYPE,
1434 p_client_server_flag IN vea_packages.client_server_flag%TYPE,
1435 p_name IN vea_packages.name%TYPE
1436 )
1437 IS
1438 SELECT package_id
1439 FROM vea_packages
1440 WHERE layer_provider_code = p_layer_provider_code
1441 AND application_short_name = p_application_short_name
1442 AND client_server_flag = p_client_server_flag
1443 AND name = p_name;
1444 -- Commented out this code because we no longer base our processing on the ids
1445 -- stored in the flat file. We go by the names of the packages
1446 -- and derive the ids based on the names and use the derived ids
1447 -- in further processing.
1448 /*
1449 AND (
1450 (
1451 p_package_id IS NOT NULL
1452 AND package_id = p_package_id
1453 )
1454 OR
1455 (
1456 p_package_id IS NULL
1457 AND application_short_name = p_application_short_name
1458 AND client_server_flag = p_client_server_flag
1459 AND name = p_name
1460 )
1461 );
1462 */
1463 --}
1464 BEGIN
1465 --{
1466 l_location := '0010';
1467 --
1468 IF NOT( vea_tpa_util_pvt.is_vea_installed() )
1469 THEN
1470 RETURN;
1471 END IF;
1472 --
1473 --
1474 -- Standard API Header
1475 --
1476 l_location := '0020';
1477 --
1478 vea_tpa_util_pvt.api_header
1479 (
1480 p_package_name => G_PACKAGE_NAME,
1481 p_api_name => l_api_name,
1482 p_api_type => l_api_type,
1483 p_api_current_version => l_api_version,
1484 p_api_caller_version => p_api_version,
1485 p_init_msg_list => p_init_msg_list,
1486 x_savepoint_name => l_savepoint_name,
1487 x_api_return_status => x_return_status
1488 );
1489 --
1490 --
1491 --{ API Body
1492 --
1493 --
1494 l_location := '0030';
1495 --g_package_id := p_id;
1496 l_tp_layer_id := p_tp_layer_id;
1497 g_tp_layer_id := NULL;
1498 --
1499 IF (VEA_TPA_UTIL_PVT.isLayerMergeOn)
1500 AND p_tp_layer_id IS NOT NULL
1501 THEN
1502 --{
1503 vea_tpa_util_pvt.get
1504 (
1505 p_key => p_tp_layer_id,
1506 p_cache_tbl => VEA_TPA_UTIL_PVT.g_tpLyr_fileId_dbId_tbl,
1507 p_cache_ext_tbl => VEA_TPA_UTIL_PVT.g_tpLyr_fileId_dbId_ext_tbl,
1508 x_value => l_tp_layer_id
1509 );
1510 --}
1511 ELSE
1512 --{
1513 l_tp_layer_id := p_tp_layer_id;
1514 --}
1515 END IF;
1516 --
1517 g_tp_layer_id := l_tp_layer_id;
1518 --
1519 IF vea_layer_licenses_sv.isLicensed
1520 (
1521 p_layer_provider_code => p_layer_provider_code,
1522 p_tp_layer_id => l_tp_layer_id
1523 )
1524 THEN
1525 --{
1526 l_location := '0035';
1527 --
1528 l_package_id := NULL;
1529 --
1530 --
1531 g_package_id := NULL;
1532 --
1533 --
1534 l_location := '0040';
1535 --
1536 --
1537 l_package_id := getId
1538 (
1539 p_layer_provider_code => p_layer_provider_code,
1540 --p_package_id => p_id,
1541 p_application_short_name => p_application_short_name,
1542 p_client_server_flag => p_client_server_flag,
1543 p_name => p_name
1544 );
1545 --
1546 --
1547 l_location := '0050';
1548 --
1549 g_package_id := l_package_id;
1550 --FND_FILE.PUT_LINE ( FND_FILE.LOG, ' l_package_id is ' || l_package_id);
1551 --
1552 --
1553 l_location := '0060';
1554 --
1555 IF l_package_id IS NULL
1556 THEN
1557 --{
1558 l_location := '0070';
1559 --
1560 --FND_FILE.PUT_LINE ( FND_FILE.LOG, ' Inserting a row in vea_packages');
1561 --
1562 IF p_layer_provider_code = vea_tpa_util_pvt.g_current_layer_provider_code
1563 THEN
1564 SELECT NVL( p_id, vea_packages_s.NEXTVAL )
1565 INTO l_package_id
1566 FROM DUAL;
1567 ELSE
1568 SELECT vea_packages_s.NEXTVAL
1569 INTO l_package_id
1570 FROM DUAL;
1571 END IF;
1572 --
1573 --
1574 --
1575 --FND_FILE.PUT_LINE ( FND_FILE.LOG, ' l_package_id ' || l_package_id);
1576 l_location := '0080';
1577 --
1578 validate
1579 (
1580 p_layer_provider_code => p_layer_provider_code,
1581 p_package_id => l_package_id,
1582 p_client_server_flag => p_client_server_flag,
1583 p_name => p_name,
1584 p_label => p_label,
1585 p_generate_flag => p_generate_flag,
1586 p_tpa_flag => p_tpa_flag,
1587 p_specification_filename => p_specification_filename,
1588 p_body_filename => p_body_filename,
1589 p_version_number => p_version_number,
1590 p_description => p_description,
1591 p_application_short_name => p_application_short_name,
1592 p_tp_layer_id => l_tp_layer_id
1593 );
1594 --
1595 --
1596 l_location := '0090';
1597 --
1598 insert_row
1599 (
1600 p_layer_provider_code => p_layer_provider_code,
1601 p_package_id => l_package_id,
1602 p_client_server_flag => p_client_server_flag,
1603 p_name => p_name,
1604 p_label => p_label,
1605 p_generate_flag => p_generate_flag,
1606 p_tpa_flag => p_tpa_flag,
1607 p_specification_filename => p_specification_filename,
1608 p_body_filename => p_body_filename,
1609 p_version_number => p_version_number,
1610 p_description => p_description,
1611 p_application_short_name => p_application_short_name,
1612 p_tp_layer_id => l_tp_layer_id
1613 );
1614 --FND_FILE.PUT_LINE ( FND_FILE.LOG, ' l_package_id 2 = ' || l_package_id);
1615 -- This very important, otherwise insert on program unit will fail
1616 g_package_id := l_package_id;
1617 --}
1618 ELSE
1619 --{
1620 l_location := '0100';
1621 --
1622 validate
1623 (
1624 p_layer_provider_code => p_layer_provider_code,
1625 p_package_id => l_package_id,
1626 p_client_server_flag => p_client_server_flag,
1627 p_name => p_name,
1628 p_label => p_label,
1629 p_generate_flag => p_generate_flag,
1630 p_tpa_flag => p_tpa_flag,
1631 p_specification_filename => p_specification_filename,
1632 p_body_filename => p_body_filename,
1633 p_version_number => p_version_number,
1634 p_description => p_description,
1635 p_application_short_name => p_application_short_name,
1636 p_tp_layer_id => l_tp_layer_id
1637 );
1638 --
1639 --
1640 l_location := '0110';
1641 --
1642 update_row
1643 (
1644 p_layer_provider_code => p_layer_provider_code,
1645 p_package_id => l_package_id,
1646 p_client_server_flag => p_client_server_flag,
1647 p_name => p_name,
1648 p_label => p_label,
1649 p_generate_flag => p_generate_flag,
1650 p_tpa_flag => p_tpa_flag,
1651 p_specification_filename => p_specification_filename,
1652 p_body_filename => p_body_filename,
1653 p_version_number => p_version_number,
1654 p_description => p_description,
1655 p_application_short_name => p_application_short_name,
1656 p_tp_layer_id => l_tp_layer_id
1657 );
1658 --}
1659 END IF;
1660 --
1661 --
1662 l_location := '0120';
1663 --
1664 x_id := l_package_id;
1665 --
1666 --
1667 --}
1668 END IF;
1669 --
1670 --
1671 --} API Body
1672 --
1673 --
1674 -- Standard API Footer
1675 --
1676 l_location := '0130';
1677 --
1678 vea_tpa_util_pvt.api_footer
1679 (
1680 p_commit => p_commit,
1681 x_msg_count => x_msg_count,
1682 x_msg_data => x_msg_data
1683 );
1684 --}
1685 EXCEPTION
1686 --{
1687 WHEN FND_API.G_EXC_ERROR
1688 THEN
1689 --{
1690 --RAISE;
1691 vea_tpa_util_pvt.handle_error
1692 (
1693 p_error_type => vea_tpa_util_pvt.G_ERROR,
1694 p_savepoint_name => l_savepoint_name,
1695 p_package_name => G_PACKAGE_NAME,
1696 p_api_name => l_api_name,
1697 p_location => l_location,
1698 x_msg_count => x_msg_count,
1699 x_msg_data => x_msg_data,
1700 x_api_return_status => x_return_status
1701 );
1702 --}
1703 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1704 THEN
1705 --{
1706 --RAISE;
1707 vea_tpa_util_pvt.handle_error
1708 (
1709 p_error_type => vea_tpa_util_pvt.G_UNEXPECTED_ERROR,
1710 p_savepoint_name => l_savepoint_name,
1711 p_package_name => G_PACKAGE_NAME,
1712 p_api_name => l_api_name,
1713 p_location => l_location,
1714 x_msg_count => x_msg_count,
1715 x_msg_data => x_msg_data,
1716 x_api_return_status => x_return_status
1717 );
1718 --}
1719 WHEN OTHERS
1720 THEN
1721 --{
1722 --RAISE;
1723 vea_tpa_util_pvt.handle_error
1724 (
1725 p_error_type => vea_tpa_util_pvt.G_OTHER_ERROR,
1726 p_savepoint_name => l_savepoint_name,
1727 p_package_name => G_PACKAGE_NAME,
1728 p_api_name => l_api_name,
1729 p_location => l_location,
1730 x_msg_count => x_msg_count,
1731 x_msg_data => x_msg_data,
1732 x_api_return_status => x_return_status
1733 );
1734 --}
1735 --}
1736 END process;
1737 --}
1738 END VEA_PACKAGES_SV;