[Home] [Help]
PACKAGE BODY: APPS.FND_APP_SYSTEM
Source
1 package body FND_APP_SYSTEM as
2 /* $Header: AFCPSYSB.pls 120.3 2005/12/20 09:59:35 ssuraj noship $ */
3
4 /*==========================================================================*/
5
6 procedure register_system ( Name varchar2,
7 owner varchar2,
8 CSI_NUMBER varchar2,
9 System_Guid raw default null
10 )
11 as
12 l_rec fnd_apps_system%rowtype;
13 l_insert boolean := false;
14
15 begin
16
17 l_rec.System_Guid := System_Guid;
18 l_rec.Name := Name;
19 l_rec.owner := owner;
20 l_rec.CSI_NUMBER := CSI_NUMBER;
21
22 if ( l_rec.System_Guid is null )
23 then
24 begin
25
26 select a.System_Guid
27 into l_rec.System_Guid
28 from fnd_apps_system a
29 where a.name = l_rec.Name;
30
31 exception
32 when no_data_found then
33 l_insert := true;
34 end;
35 end if;
36
37 if ( l_insert )
38 then
39 insert into fnd_apps_system
40 (name,Version,Owner,CSI_Number,System_GUID,Source_System_Guid,
41 last_updated_by,last_update_date,last_update_login,
42 creation_date,created_by
43 )
44 values ( l_rec.Name,'1',l_rec.owner,l_rec.CSI_NUMBER,sys_guid(),
45 sys_guid(),
46 1,sysdate,0,sysdate,1 );
47 else
48
49 update fnd_apps_system a
50 set a.name = nvl(l_rec.Name,a.name),
51 a.owner= nvl(l_rec.Owner,a.owner),
52 a.csi_number=nvl(l_rec.CSI_NUMBER,a.csi_number),
53 a.last_update_date = SYSDATE,
54 a.last_updated_by = 1
55 where a.System_Guid = l_rec.System_Guid;
56
57 end if;
58
59 end;
60
61 /*==========================================================================*/
62
63 procedure register_oraclehome ( name varchar2,
64 Node_Id varchar2,
65 Path varchar2,
66 Version varchar2,
67 Description varchar2,
68 File_System_GUID raw,
69 Oracle_Home_Id raw default null )
70 as
71 l_rec fnd_oracle_homes%rowtype;
72 l_insert boolean := false;
73
74 begin
75
76 l_rec.Oracle_Home_Id := Oracle_Home_Id;
77 l_rec.name := name;
78 l_rec.Node_Id := Node_Id;
79 l_rec.Path := Path;
80 l_rec.Version := Version;
81 l_rec.Description := Description;
82 l_rec.File_System_GUID := File_System_GUID;
83
84 if ( l_rec.Oracle_Home_Id is null )
85 then
86 begin
87
88 select a.Oracle_Home_Id
89 into l_rec.Oracle_Home_Id
90 from fnd_oracle_homes a
91 where a.node_id = l_rec.Node_Id
92 and a.path = l_rec.Path;
93 exception
94 when no_data_found then
95 l_insert := true;
96 end;
97 end if;
98
99 if ( l_insert )
100 then
101
102 insert into fnd_oracle_homes
103 (Oracle_Home_Id,name,Node_Id,Path,Version,
104 Description,File_System_GUID,
105 last_updated_by,last_update_date,last_update_login,
106 creation_date,created_by,created
107 )
108 values ( sys_guid(),l_rec.name,l_rec.Node_Id,l_rec.Path,
109 l_rec.Version,
110 l_rec.Description,nvl(l_rec.File_System_GUID,sys_guid()),
111 1,sysdate,0,sysdate,1,sysdate );
112 else
113
114 update fnd_oracle_homes a
115 set a.name = nvl(l_rec.Name,a.name),
116 a.node_id = nvl(l_rec.Node_Id,a.Node_Id),
117 a.path = nvl(l_rec.Path,a.path),
118 a.version = nvl(l_rec.Version,a.version),
119 a.Description = nvl(l_rec.Description,a.Description),
120 a.File_System_GUID = nvl(l_rec.File_System_GUID,a.File_System_GUID),
121 a.last_update_date = SYSDATE,
122 a.last_updated_by = 1
123 where a.Oracle_Home_Id = l_rec.Oracle_Home_Id;
124
125 end if;
126
127 end;
128
129 /*==========================================================================*/
130
131 procedure register_appltop ( name varchar2,
132 Node_Id varchar2,
133 Path varchar2,
134 Shared varchar2,
135 File_System_GUID raw,
136 Appl_Top_Guid raw default null )
137 as
138 l_rec fnd_appl_tops%rowtype;
139 l_insert boolean := false;
140
141 begin
142
143 l_rec.Appl_Top_Guid := Appl_Top_Guid;
144 l_rec.name := name;
145 l_rec.Node_Id := Node_Id;
146 l_rec.Path := Path;
147 l_rec.Shared := Shared;
148 l_rec.File_System_GUID:= File_System_GUID;
149
150 if ( l_rec.Appl_Top_Guid is null )
151 then
152 begin
153
154 select a.appl_top_guid
155 into l_rec.Appl_Top_Guid
156 from fnd_appl_tops a
157 where a.node_id = l_rec.Node_Id
158 and a.path = l_rec.Path;
159
160 exception
161 when no_data_found then
162 l_insert := true;
163 end;
164 end if;
165
166 if ( l_insert )
167 then
168
169 insert into fnd_appl_tops
170 (appl_top_guid,name,Node_Id,Path,Shared,File_System_GUID,
171 last_updated_by,last_update_date,last_update_login,
172 creation_date,created_by
173 )
174 values ( sys_guid(),l_rec.name,l_rec.Node_Id,l_rec.Path,
175 l_rec.Shared,nvl(l_rec.File_System_GUID,sys_guid()),
176 1,sysdate,0,sysdate,1 );
177 else
178
179 update fnd_appl_tops a
180 set a.name = nvl(l_rec.Name,a.name),
181 a.node_id = nvl(l_rec.Node_Id,a.Node_Id),
182 a.path = nvl(l_rec.Path,a.path),
183 a.shared = nvl(l_rec.shared,a.shared),
184 a.File_System_GUID = nvl(l_rec.File_System_GUID,a.File_System_GUID),
185 a.last_update_date = SYSDATE,
186 a.last_updated_by = 1
187 where a.appl_top_guid = l_rec.Appl_Top_Guid;
188
189 end if;
190
191 end;
192
193 /*==========================================================================*/
194
195 procedure register_server ( Name varchar2,
196 Node_Id varchar2,
197 Internal varchar2,
198 Appl_Top_Guid raw,
199 Server_type varchar2,
200 Pri_Oracle_Home raw,
201 Aux_Oracle_Home raw,
202 Server_GUID raw default null )
203 as
204 l_rec fnd_app_servers%rowtype;
205 l_insert boolean := false;
206
207 begin
208
209 l_rec.Server_GUID := Server_GUID;
210 l_rec.Name := Name;
211 l_rec.Node_Id := Node_Id;
212 l_rec.Internal := Internal;
213 l_rec.Appl_Top_Guid := Appl_Top_Guid;
214 l_rec.Server_type := Server_type;
215 l_rec.Pri_Oracle_Home := Pri_Oracle_Home;
216 l_rec.Aux_Oracle_Home := Aux_Oracle_Home;
217
218 if ( l_rec.Server_GUID is null )
219 then
220 begin
221
222 select a.Server_GUID
223 into l_rec.Server_GUID
224 from fnd_app_servers a
225 where a.name = l_rec.name;
226
227 exception
228 when no_data_found then
229 l_insert := true;
230 end;
231 end if;
232
233 if ( l_insert )
234 then
235
236 insert into fnd_app_servers
237 (Server_GUID,name,Node_Id,Internal,Appl_Top_Guid,
238 Server_type,Pri_Oracle_Home,Aux_Oracle_Home,
239 last_updated_by,last_update_date,last_update_login,
240 creation_date,created_by
241 )
242 values ( sys_guid(),l_rec.name,l_rec.Node_Id,l_rec.internal,
243 l_rec.Appl_Top_Guid,l_rec.Server_type,
244 nvl(l_rec.Pri_Oracle_Home,sys_guid()),
245 l_rec.Aux_Oracle_Home,
246 1,sysdate,0,sysdate,1 );
247 else
248
249 update fnd_app_servers a
250 set a.name = nvl(l_rec.Name,a.name),
251 a.node_id = nvl(l_rec.Node_Id,a.Node_Id),
252 a.internal = nvl(l_rec.internal,a.internal),
253 a.Appl_Top_Guid = nvl(l_rec.Appl_Top_Guid,a.Appl_Top_Guid),
254 a.Server_type = nvl(l_rec.Server_type,a.Server_type),
255 a.Pri_Oracle_Home = nvl(l_rec.Pri_Oracle_Home,a.Pri_Oracle_Home),
256 a.Aux_Oracle_Home = nvl(l_rec.Aux_Oracle_Home,a.Aux_Oracle_Home),
257 a.last_update_date = SYSDATE,
258 a.last_updated_by = 1
259 where a.Server_GUID = l_rec.Server_GUID;
260
261 end if;
262
263 end;
264
265 /*==========================================================================*/
266
267 procedure register_servermap ( Server_GUID raw,
268 System_Guid raw )
269 as
270 l_rec fnd_system_server_map%rowtype;
271 l_insert boolean := false;
272
273 begin
274
275 l_rec.Server_GUID := Server_GUID;
276 l_rec.System_Guid := System_Guid;
277
278 begin
279
280 select a.Server_GUID,a.System_Guid
281 into l_rec.Server_GUID,l_rec.System_Guid
282 from fnd_system_server_map a
283 where a.Server_GUID = l_rec.Server_GUID
284 and a.System_Guid = l_rec.System_Guid;
285
286 exception
287 when no_data_found then
288 l_insert := true;
289 end;
290
291 if ( l_insert )
292 then
293
294 insert into fnd_system_server_map
295 (Server_GUID,System_Guid,
296 last_updated_by,last_update_date,last_update_login,
297 creation_date,created_by
298 )
299 values ( l_rec.Server_GUID,l_rec.System_Guid,1,sysdate,0,sysdate,1 );
300
301 else
302
303 update fnd_system_server_map a
304 set a.Server_GUID = nvl(l_rec.Server_GUID,a.Server_GUID),
305 a.System_Guid = nvl(l_rec.System_Guid,a.System_Guid),
306 a.last_update_date = SYSDATE,
307 a.last_updated_by = 1
308 where a.Server_GUID = l_rec.Server_GUID
309 and a.System_Guid = l_rec.System_Guid;
310
311 end if;
312
313 end;
314
315 /*==========================================================================*/
316
317 procedure register_database ( db_name varchar2,
318 db_domain varchar2,
319 Default_TNS_Alias_Guid raw,
320 Is_Rac_db varchar2,
321 Version varchar2,
322 db_guid raw default null
323 )
324 as
325 l_rec fnd_databases%rowtype;
326 l_insert boolean := false;
327 begin
328
329 l_rec.db_guid := db_guid;
330 l_rec.db_name := db_name;
331 l_rec.db_domain := db_domain;
332 l_rec.Default_TNS_Alias_Guid := Default_TNS_Alias_Guid;
333 l_rec.Is_Rac_db := Is_Rac_db;
334 l_rec.Version := Version;
335
336 if ( l_rec.db_guid is null )
337 then
338 begin
339
340 select a.db_guid
341 into l_rec.db_guid
342 from fnd_databases a
343 where a.db_name = l_rec.db_name
344 and a.db_domain= l_rec.db_domain;
345
346 exception
347 when no_data_found then
348 l_insert := true;
349 end;
350 end if;
351
352 if ( l_insert )
353 then
354
355 insert into fnd_databases
356 (db_guid,DB_Name,DB_Domain,Default_TNS_Alias_Guid,Is_Rac_db,
357 Version,
358 last_updated_by,last_update_date,last_update_login,
359 creation_date,created_by
360 )
361 values ( sys_guid(),l_rec.db_name,l_rec.db_domain,
362 nvl(l_rec.Default_TNS_Alias_Guid,sys_guid()),
363 l_rec.Is_Rac_db,l_rec.Version,
364 1,sysdate,0,sysdate,1 );
365 else
366
367 update fnd_databases a
368 set a.db_name = nvl(l_rec.db_Name,a.db_name),
369 a.db_domain =nvl(l_rec.db_domain,a.db_domain),
370 a.Default_TNS_Alias_Guid =
371 nvl(l_rec.Default_TNS_Alias_Guid,a.Default_TNS_Alias_Guid),
372 a.Is_Rac_db = nvl(l_rec.Is_Rac_db,a.Is_Rac_db),
373 a.version = nvl(l_rec.Version,a.version),
374 a.last_update_date = SYSDATE,
375 a.last_updated_by = 1
376 where a.db_guid = l_rec.db_guid;
377
378 end if;
379
380 end;
381
382 /*==========================================================================*/
383
384 procedure register_database_asg( db_name varchar2,
385 assignment varchar2,
386 db_domain varchar2
387 )
388 as
389 l_rec fnd_database_assignments%rowtype;
390 l_insert boolean := false;
391 l_db_domain fnd_databases.db_domain%TYPE;
392 l_db_name fnd_databases.db_name%TYPE;
393 l_count number;
394
395 begin
396
397 l_db_name := db_name;
398 l_db_domain := db_domain;
399 l_rec.assignment := assignment;
400
401 select a.db_guid
402 into l_rec.db_guid
403 from fnd_databases a
404 where a.db_name = l_db_name
405 and a.db_domain = l_db_domain;
406
407 begin
408
409 select a.db_guid
410 into l_rec.db_guid
411 from fnd_database_assignments a
412 where a.db_guid = l_rec.db_guid
413 and a.assignment = l_rec.assignment;
414
415 exception
416 when no_data_found then
417 l_insert := true;
418 end;
419
420 if ( l_insert )
421 then
422
423 insert into fnd_database_assignments
424 (db_guid,assignment,
425 last_updated_by,last_update_date,last_update_login,
426 creation_date,created_by
427 )
428 values ( l_rec.db_guid,l_rec.assignment,
429 1,sysdate,0,sysdate,1 );
430 else
431
432 update fnd_database_assignments a
433 set a.assignment = nvl(l_rec.assignment,a.assignment),
434 a.last_update_date = SYSDATE,
435 a.last_updated_by = 1
436 where a.db_guid = l_rec.db_guid
437 and a.assignment = l_rec.assignment;
441 end;
438
439 end if;
440
442
443 /*==========================================================================*/
444
445 procedure register_instance ( db_name varchar2,
446 Instance_Name varchar2,
447 Instance_Number Number,
448 Sid_GUID raw,
449 Sid varchar2,
450 Default_TNS_Alias_GUID raw,
451 Server_GUID raw,
452 Local_Listener_Alias raw,
453 Remote_Listener_Alias raw,
454 Configuration varchar2,
455 Description varchar2,
456 Interconnect_name varchar2,
457 Instance_Guid raw default null,
458 db_domain varchar2
459 )
460 as
461 l_rec fnd_database_instances%rowtype;
462 l_insert boolean := false;
463 l_db_name fnd_databases.db_name%type;
464 l_db_domain fnd_databases.db_domain%type;
465 begin
466
467 l_db_name := db_name;
468 l_db_domain := db_domain;
469 l_rec.Instance_Guid := Instance_Guid;
470 l_rec.Instance_Name := Instance_Name;
471 l_rec.Instance_Number := Instance_Number;
472 l_rec.Sid_GUID := Sid_GUID;
473 l_rec.Sid := Sid;
474 l_rec.Default_TNS_Alias_GUID := Default_TNS_Alias_GUID;
475 l_rec.Server_GUID := Server_GUID;
476 l_rec.Local_Listener_Alias := Local_Listener_Alias;
477 l_rec.Remote_Listener_Alias := Remote_Listener_Alias;
478 l_rec.Configuration := Configuration;
479 l_rec.Description := Description;
480 l_rec.Interconnect_name := Interconnect_name;
481
482 select a.db_guid
483 into l_rec.db_guid
484 from fnd_databases a
485 where a.db_name = l_db_name
486 and a.db_domain = l_db_domain;
487
488 if ( l_rec.Instance_Guid is null )
489 then
490 begin
491
492 select a.db_guid,a.Instance_Guid
493 into l_rec.db_guid,l_rec.Instance_Guid
494 from fnd_database_Instances a
495 where a.db_guid = l_rec.db_guid
496 and a.Instance_Name = l_rec.Instance_Name;
497
498 exception
499 when no_data_found then
500 l_insert := true;
501 end;
502 end if;
503
504 if ( l_insert )
505 then
506
507 insert into fnd_database_instances
508 (db_guid,Instance_Guid,Instance_Name,Instance_Number,Sid_GUID,
509 Sid,
510 Default_TNS_Alias_GUID,Server_GUID,Local_Listener_Alias,
511 Remote_Listener_Alias,Configuration,Description,
512 Interconnect_name,
513 last_updated_by,last_update_date,last_update_login,
514 creation_date,created_by
515 )
516 values (
517 l_rec.db_guid,sys_guid(),l_rec.Instance_Name,
518 l_rec.Instance_Number,nvl(l_rec.Sid_GUID,sys_guid()),l_rec.sid,
519 nvl(l_rec.Default_TNS_Alias_GUID,sys_guid()),
520 l_rec.Server_GUID,
521 nvl(l_rec.Local_Listener_Alias,sys_guid()),
522 l_rec.Remote_Listener_Alias,
523 l_rec.Configuration,l_rec.Description,l_rec.Interconnect_name,
524 1,sysdate,0,sysdate,1 );
525 else
526
527 update fnd_database_instances a
528 set a.Instance_Name = nvl(l_rec.Instance_Name,a.Instance_Name),
529 a.Instance_Number = nvl(l_rec.Instance_Number,a.Instance_Number),
530 a.Sid_GUID = nvl(l_rec.Sid_GUID,a.Sid_GUID),
531 a.Sid = nvl(l_rec.Sid,a.Sid),
532 a.Default_TNS_Alias_GUID = nvl(l_rec.Default_TNS_Alias_GUID,
533 a.Default_TNS_Alias_GUID),
534 a.Server_GUID = nvl(l_rec.Server_GUID,a.Server_GUID),
535 a.Local_Listener_Alias = nvl(l_rec.Local_Listener_Alias,
536 a.Local_Listener_Alias),
537 a.Remote_Listener_Alias = nvl(l_rec.Remote_Listener_Alias,
538 a.Remote_Listener_Alias),
539 a.Configuration = nvl(l_rec.Configuration,a.Configuration),
540 a.Description = nvl(l_rec.Description,a.Description),
541 a.Interconnect_name = nvl(l_rec.Interconnect_name,
542 a.Interconnect_name),
543 a.last_update_date = SYSDATE,
544 a.last_updated_by = 1
545 where a.db_guid = l_rec.db_guid
546 and a.Instance_Guid = l_rec.Instance_Guid;
547
548 end if;
549
550 end;
551
552 /*==========================================================================*/
553
554 procedure register_sid ( Sid varchar2,
555 sid_guid raw
556 )
557 as
558 l_rec fnd_sids%rowtype;
559 l_insert boolean := false;
560 begin
561
562 l_rec.sid := Sid;
563 l_rec.sid_guid := sid_guid;
564
568 l_rec.sid_guid := sys_guid();
565 if ( l_rec.sid_Guid is null )
566 then
567 l_insert := true;
569 else
570
571 begin
572
573 select a.sid_guid
574 into l_rec.sid_guid
575 from fnd_sids a
576 where a.sid_guid = l_rec.sid_guid;
577
578 exception
579 when no_data_found then
580 l_insert := true;
581 end;
582
583 end if;
584
585 if ( l_insert )
586 then
587
588 insert into fnd_sids
589 (sid_guid,Sid,
590 last_updated_by,last_update_date,last_update_login,
591 creation_date,created_by
592 )
593 values ( l_rec.sid_guid,l_rec.sid,
594 1,sysdate,0,sysdate,1 );
595 else
596
597 update fnd_sids a
598 set a.sid = nvl(l_rec.sid,a.sid),
599 a.last_update_date = SYSDATE,
600 a.last_updated_by = 1
601 where a.sid_guid = l_rec.sid_guid;
602
603 end if;
604
605 end;
606
607 /*==========================================================================*/
608
609 procedure register_service ( Service_name varchar2,
610 db_name varchar2,
611 db_domain varchar2,
612 Description varchar2,
613 db_service_guid raw default null
614 )
615 as
616 l_rec fnd_database_services%rowtype;
617 l_insert boolean := false;
618 l_db_name fnd_databases.db_name%type;
619 l_db_domain fnd_databases.db_domain%type;
620
621 begin
622
623 l_rec.DB_Service_GUID := DB_Service_GUID;
624 l_db_name := db_name;
625 l_db_domain := db_domain;
626 l_rec.Service_Name := Service_Name;
627 l_rec.Description := Description;
628
629 begin
630
631 select a.db_guid
632 into l_rec.db_guid
633 from fnd_databases a
634 where a.db_name = l_db_name
635 and a.db_domain= l_db_domain;
636
637 end;
638
639 if ( l_rec.db_service_guid is null )
640 then
641 begin
642
643 select a.db_service_guid
644 into l_rec.db_service_guid
645 from fnd_database_services a
646 where a.db_guid = l_rec.db_guid
647 and a.Service_Name = l_rec.Service_Name;
648
649 exception
650 when no_data_found then
651 l_insert := true;
652 end;
653 end if;
654
655 if ( l_insert )
656 then
657
658 insert into fnd_database_services
659 (DB_Service_GUID,db_guid,Service_Name,Description,
660 last_updated_by,last_update_date,last_update_login,
661 creation_date,created_by
662 )
663 values ( sys_guid(),l_rec.db_guid,l_rec.Service_Name,
664 l_rec.Description,
665 1,sysdate,0,sysdate,1 );
666 else
667
668 update fnd_database_services a
669 set a.Service_Name = nvl(l_rec.Service_Name,a.Service_Name),
670 a.Description = nvl(l_rec.Description,a.Description),
671 a.last_update_date = SYSDATE,
672 a.last_updated_by = 1
673 where a.DB_Service_GUID = l_rec.DB_Service_GUID;
674
675 end if;
676
677 end;
678
679 /*==========================================================================*/
680
681 procedure register_service_members ( db_name varchar2,
682 instance_name varchar2,
683 instance_type varchar2,
684 db_service_guid raw default null,
685 db_domain varchar2
686 )
687 as
688 l_rec fnd_db_service_members%rowtype;
689 l_insert boolean := false;
690 l_db_name fnd_databases.db_name%type;
691 l_instance_name fnd_database_instances.instance_name%type;
692 l_db_domain fnd_databases.db_domain%type;
693
694 begin
695
696 l_rec.db_service_guid := db_service_guid;
697 l_rec.instance_type := instance_type;
698 l_db_name := db_name;
699 l_db_domain := db_domain;
700 l_instance_name := instance_name;
701
702 select a.db_guid
703 into l_rec.db_guid
704 from fnd_databases a
705 where a.db_name = l_db_name
706 and a.db_domain = l_db_domain;
707
708 begin
709
710 select instance_guid
711 into l_rec.instance_guid
712 from fnd_database_instances a
713 where a.db_guid = l_rec.db_guid
714 and a.instance_name = l_instance_name;
715
716 end;
717
718 if ( l_rec.db_service_guid is null )
719 then
720 begin
721
725 where a.DB_GUID = l_rec.DB_GUID
722 select a.db_service_guid
723 into l_rec.db_service_guid
724 from fnd_db_service_members a
726 and a.Instance_Guid = l_rec.Instance_Guid;
727
728 exception
729 when no_data_found then
730 l_insert := true;
731 end;
732
733 if ( l_insert )
734 then
735 l_rec.db_service_guid := sys_guid();
736 end if;
737
738 else
739
740 begin
741
742 select a.db_service_guid
743 into l_rec.db_service_guid
744 from fnd_db_service_members a
745 where a.db_service_guid = l_rec.db_service_guid
746 and a.Instance_Guid = l_rec.Instance_Guid;
747
748 exception
749 when no_data_found then
750 l_insert := true;
751
752 end;
753
754 end if;
755
756 if ( l_insert )
757 then
758
759 insert into fnd_db_service_members
760 (db_service_guid,DB_GUID,Instance_Guid,instance_type,
761 last_updated_by,last_update_date,last_update_login,
762 creation_date,created_by
763 )
764 values ( l_rec.db_service_guid,l_rec.DB_GUID,l_rec.Instance_Guid,
765 l_rec.instance_type,
766 1,sysdate,0,sysdate,1 );
767 else
768
769 update fnd_db_service_members a
770 set a.DB_GUID = nvl(l_rec.DB_GUID,a.DB_GUID),
771 a.Instance_Guid = nvl(l_rec.Instance_Guid,a.Instance_Guid),
772 a.instance_type = nvl(l_rec.instance_type,a.instance_type),
773 a.last_update_date = SYSDATE,
774 a.last_updated_by = 1
775 where a.db_service_guid = l_rec.db_service_guid
776 and a.Instance_Guid = l_rec.Instance_Guid;
777
778 end if;
779
780 end;
781
782 /*==========================================================================*/
783
784 procedure register_listener ( Listener_Name varchar2,
785 Server_name varchar2,
786 tns_alias_name varchar2,
787 Listener_GUID raw default null,
788 alias_set_name varchar2
789 )
790 as
791 l_rec fnd_tns_listeners%rowtype;
792 l_insert boolean := false;
793 l_server_name fnd_app_servers.name%type;
794 l_tns_alias_name fnd_tns_aliases.alias_name%type;
795 l_alias_set_guid fnd_tns_alias_sets.tns_alias_set_guid%type;
796 l_alias_set_name fnd_tns_alias_sets.tns_alias_set_name%type;
797
798 begin
799
800 l_rec.Listener_GUID := Listener_GUID;
801 l_rec.Listener_Name := Listener_Name;
802 l_server_name := Server_name;
803 l_tns_alias_name := tns_alias_name;
804 l_alias_set_name := alias_set_name;
805
806 select a.server_guid
807 into l_rec.server_guid
808 from fnd_app_servers a
809 where a.name = l_server_name;
810
811 select a.tns_alias_set_guid
812 into l_alias_set_guid
813 from fnd_tns_alias_sets a
814 where a.tns_alias_set_name = l_alias_set_name;
815
816 begin
817
818 select a.tns_alias_guid
819 into l_rec.tns_alias_guid
820 from fnd_tns_aliases a
821 where a.alias_name = l_tns_alias_name
822 and a.alias_set_guid = l_alias_set_guid;
823
824 exception
825 when no_data_found then
826 l_rec.tns_alias_guid := null;
827 end;
828
829 if ( l_rec.Listener_GUID is null )
830 then
831 begin
832
833 select a.Listener_GUID
834 into l_rec.Listener_GUID
835 from fnd_tns_listeners a
836 where a.Server_GUID = l_rec.Server_GUID
837 and a.Listener_Name = l_rec.Listener_Name;
838
839 exception
840 when no_data_found then
841 l_insert := true;
842 end;
843 end if;
844
845 if ( l_insert )
846 then
847
848 insert into fnd_tns_listeners
849 (Listener_GUID,Listener_Name,Server_GUID,tns_alias_guid,
850 last_updated_by,last_update_date,last_update_login,
851 creation_date,created_by
852 )
853 values (
854 sys_guid(),l_rec.Listener_Name,l_rec.Server_GUID,
855 nvl(l_rec.tns_alias_guid,sys_guid()),
856 1,sysdate,0,sysdate,1 );
857 else
858
859 update fnd_tns_listeners a
860 set a.Listener_Name = nvl(l_rec.Listener_Name,a.Listener_Name),
861 a.tns_alias_guid = nvl(l_rec.tns_alias_guid,a.tns_alias_guid),
862 a.last_update_date = SYSDATE,
863 a.last_updated_by = 1
864 where a.Listener_GUID = l_rec.Listener_GUID;
865
866 end if;
867
868 end;
869
870 /*==========================================================================*/
871
872 procedure register_tnsalias ( Alias_Name varchar2,
873 Alias_Type varchar2,
877 alias_set_name varchar2
874 Failover varchar2,
875 Load_Balance varchar2,
876 TNS_ALIAS_GUID raw default null,
878 )
879 as
880 l_rec fnd_tns_aliases%rowtype;
881 l_insert boolean := false;
882 l_tns_alias_set_name fnd_tns_alias_sets.tns_alias_set_name%type;
883
884 begin
885
886 l_rec.TNS_ALIAS_GUID := TNS_ALIAS_GUID;
887 l_rec.Alias_Name := Alias_Name;
888 l_rec.Alias_Type := Alias_Type;
889 l_rec.Failover := Failover;
890 l_rec.Load_Balance := Load_Balance;
891
892 l_tns_alias_set_name := alias_set_name;
893
894 select a.tns_alias_set_GUID
895 into l_rec.alias_set_guid
896 from fnd_tns_alias_sets a
897 where a.tns_alias_set_name = l_tns_alias_set_name;
898
899 if ( l_rec.TNS_ALIAS_GUID is null )
900 then
901 begin
902
903 select a.TNS_ALIAS_GUID
904 into l_rec.TNS_ALIAS_GUID
905 from fnd_tns_aliases a
906 where a.alias_set_guid = l_rec.alias_set_guid
907 and a.alias_name = l_rec.alias_name;
908
909 exception
910 when no_data_found then
911 l_rec.TNS_ALIAS_GUID := sys_guid();
912 l_insert := true;
913 end;
914 else
915 begin
916
917 select a.TNS_ALIAS_GUID
918 into l_rec.TNS_ALIAS_GUID
919 from fnd_tns_aliases a
920 where a.tns_alias_guid = l_rec.TNS_ALIAS_GUID;
921
922 exception
923 when no_data_found then
924 l_insert := true;
925 end;
926 end if;
927
928 if ( l_insert )
929 then
930
931 insert into fnd_tns_aliases
932 (TNS_ALIAS_GUID,Alias_Name,Alias_set_guid,Alias_Type,
933 Failover,Load_Balance,
934 last_updated_by,last_update_date,last_update_login,
935 creation_date,created_by
936 )
937 values (
938 l_rec.TNS_ALIAS_GUID,l_rec.Alias_Name,l_rec.alias_set_GUID,
939 l_rec.Alias_Type,
940 l_rec.Failover,
941 l_rec.Load_Balance,
942 1,sysdate,0,sysdate,1 );
943 else
944
945 update fnd_tns_aliases a
946 set a.alias_name = nvl(l_rec.Alias_Name,a.alias_name),
947 a.alias_set_guid = nvl(l_rec.alias_set_GUID,a.alias_set_guid),
948 a.Alias_Type = nvl(l_rec.Alias_Type,a.Alias_Type),
949 a.Failover = nvl(l_rec.Failover,a.Failover),
950 a.Load_Balance=nvl(l_rec.Load_Balance,a.Load_Balance),
951 a.last_update_date = SYSDATE,
952 a.last_updated_by = 1
953 where a.TNS_ALIAS_GUID = l_rec.TNS_ALIAS_GUID;
954
955 end if;
956
957 end;
958
959 /*==========================================================================*/
960
961 procedure register_tns_description
962 ( alias_set_name varchar2,
963 Alias_Name varchar2,
964 Sequence_Number number default null,
965 Failover varchar2,
966 Load_Balance varchar2,
967 Service_GUID raw,
968 Instance_Guid raw,
969 Service_Name varchar2,
970 Instance_Name varchar2,
971 TNS_ALIAS_DESCRIPTION_GUID
972 raw default null
973 )
974 as
975 l_rec fnd_tns_alias_descriptions%rowtype;
976 l_insert boolean := false;
977 l_tns_alias_set_name fnd_tns_alias_sets.tns_alias_set_name%type;
978 l_tns_alias_name fnd_tns_aliases.alias_name%type;
979
980 begin
981
982 l_rec.TNS_ALIAS_DESCRIPTION_GUID := TNS_ALIAS_DESCRIPTION_GUID;
983 l_rec.Sequence_Number := nvl(Sequence_Number,0);
984 l_rec.Failover := Failover;
985 l_rec.Load_Balance := Load_Balance;
986 l_rec.DB_Service_GUID := Service_GUID;
987 l_rec.DB_Instance_Guid := Instance_Guid;
988 l_rec.Service_Name := Service_Name;
989 l_rec.Instance_Name := Instance_Name;
990 l_rec.tns_alias_guid := null;
991
992 l_tns_alias_name := Alias_Name;
993 l_tns_alias_set_name := alias_set_name;
994
995 select b.tns_alias_GUID
996 into l_rec.tns_alias_guid
997 from fnd_tns_alias_sets a, fnd_tns_aliases b
998 where a.tns_alias_set_name = l_tns_alias_set_name
999 and b.alias_set_guid = a.tns_alias_set_guid
1000 and b.alias_name = l_tns_alias_name;
1001
1002 if ( l_rec.TNS_ALIAS_DESCRIPTION_GUID is null )
1003 then
1004 begin
1005
1006 select a.TNS_ALIAS_DESCRIPTION_GUID
1010 and a.Sequence_Number= l_rec.Sequence_Number;
1007 into l_rec.TNS_ALIAS_DESCRIPTION_GUID
1008 from fnd_tns_alias_descriptions a
1009 where a.tns_alias_guid = l_rec.tns_alias_guid
1011
1012 exception
1013 when no_data_found then
1014 l_rec.TNS_ALIAS_DESCRIPTION_GUID := sys_guid();
1015 l_insert := true;
1016 end;
1017 else
1018
1019 begin
1020
1021 select a.TNS_ALIAS_DESCRIPTION_GUID
1022 into l_rec.TNS_ALIAS_DESCRIPTION_GUID
1023 from fnd_tns_alias_descriptions a
1024 where a.TNS_ALIAS_DESCRIPTION_GUID = l_rec.TNS_ALIAS_DESCRIPTION_GUID;
1025
1026 exception
1027 when no_data_found then
1028 l_insert := true;
1029 end;
1030 end if;
1031
1032 if ( l_insert )
1033 then
1034
1035 insert into fnd_tns_alias_descriptions
1036 (TNS_ALIAS_GUID,TNS_ALIAS_DESCRIPTION_GUID,sequence_number,
1037 Failover,Load_Balance,
1038 DB_Service_GUID,DB_Instance_Guid,
1039 Service_Name,Instance_Name,
1040 last_updated_by,last_update_date,last_update_login,
1041 creation_date,created_by
1042 )
1043 values (
1044 l_rec.tns_alias_guid,l_rec.TNS_ALIAS_DESCRIPTION_GUID,
1045 l_rec.Sequence_Number,
1046 l_rec.Failover,l_rec.Load_Balance,
1047 l_rec.DB_Service_GUID,l_rec.DB_Instance_Guid,
1048 l_rec.Service_Name,l_rec.Instance_Name,
1049 1,sysdate,0,sysdate,1 );
1050 else
1051
1052 update fnd_tns_alias_descriptions a
1053 set a.Failover = nvl(l_rec.Failover,a.Failover),
1054 a.Load_Balance=nvl(l_rec.Load_Balance,a.Load_Balance),
1055 a.DB_Service_GUID=l_rec.DB_Service_GUID,
1056 a.DB_Instance_Guid = l_rec.DB_Instance_Guid,
1057 a.TNS_ALIAS_GUID= nvl(l_rec.tns_alias_guid,a.TNS_ALIAS_GUID),
1058 a.sequence_number=nvl(l_rec.Sequence_Number,a.sequence_number),
1059 a.Service_Name=l_rec.Service_Name,
1060 a.Instance_Name=l_rec.Instance_Name,
1061 a.last_update_date = SYSDATE,
1062 a.last_updated_by = 1
1063 where a.TNS_ALIAS_DESCRIPTION_GUID = l_rec.TNS_ALIAS_DESCRIPTION_GUID;
1064
1065 end if;
1066
1067 end;
1068
1069 /*==========================================================================*/
1070
1071 procedure register_tns_address_list
1072 ( TNS_ALIAS_DESCRIPTION_GUID
1073 raw,
1074 Sequence_Number number default null,
1075 Failover varchar2,
1076 Load_Balance varchar2,
1077 TNS_ALIAS_ADDRESS_LIST_GUID
1078 raw default null
1079 )
1080 as
1081 l_rec fnd_tns_alias_address_lists%rowtype;
1082 l_insert boolean := false;
1083
1084 begin
1085
1086 l_rec.TNS_ALIAS_DESCRIPTION_GUID := TNS_ALIAS_DESCRIPTION_GUID;
1087 l_rec.Sequence_Number := nvl(Sequence_Number,0);
1088 l_rec.Failover := Failover;
1089 l_rec.Load_Balance := Load_Balance;
1090 l_rec.TNS_ALIAS_ADDRESS_LIST_GUID := TNS_ALIAS_ADDRESS_LIST_GUID;
1091
1092 if ( l_rec.TNS_ALIAS_ADDRESS_LIST_GUID is null )
1093 then
1094
1095 begin
1096
1097 select a.TNS_ALIAS_ADDRESS_LIST_GUID,a.TNS_ALIAS_DESCRIPTION_GUID
1098 into l_rec.TNS_ALIAS_ADDRESS_LIST_GUID,l_rec.TNS_ALIAS_DESCRIPTION_GUID
1099 from fnd_tns_alias_address_lists a
1100 where a.TNS_ALIAS_DESCRIPTION_GUID = l_rec.TNS_ALIAS_DESCRIPTION_GUID
1101 and a.sequence_number = l_rec.Sequence_Number;
1102
1103 exception
1104 when no_data_found then
1105 l_rec.TNS_ALIAS_ADDRESS_LIST_GUID := sys_guid();
1106 l_insert := true;
1107 end;
1108
1109 else
1110
1111 begin
1112
1113 select a.TNS_ALIAS_ADDRESS_LIST_GUID,a.TNS_ALIAS_DESCRIPTION_GUID
1114 into l_rec.TNS_ALIAS_ADDRESS_LIST_GUID,l_rec.TNS_ALIAS_DESCRIPTION_GUID
1115 from fnd_tns_alias_address_lists a
1116 where a.TNS_ALIAS_ADDRESS_LIST_GUID = l_rec.TNS_ALIAS_ADDRESS_LIST_GUID;
1117
1118 exception
1119 when no_data_found then
1120 l_insert := true;
1121 end;
1122
1123 end if;
1124
1125 if ( l_insert )
1126 then
1127
1128 insert into fnd_tns_alias_address_lists
1129 (TNS_ALIAS_ADDRESS_LIST_GUID,TNS_ALIAS_DESCRIPTION_GUID,
1130 Sequence_Number,Failover,Load_Balance,
1131 last_updated_by,last_update_date,last_update_login,
1132 creation_date,created_by
1133 )
1134 values (
1135 l_rec.TNS_ALIAS_ADDRESS_LIST_GUID,
1136 l_rec.TNS_ALIAS_DESCRIPTION_GUID,
1137 l_rec.Sequence_Number,
1138 l_rec.Failover,
1139 l_rec.Load_Balance,
1143 update fnd_tns_alias_address_lists a
1140 1,sysdate,0,sysdate,1 );
1141 else
1142
1144 set a.Failover = nvl(l_rec.Failover,a.Failover),
1145 a.Load_Balance=nvl(l_rec.Load_Balance,a.Load_Balance),
1146 a.sequence_number=nvl(l_rec.Sequence_Number,a.sequence_number),
1147 a.last_update_date = SYSDATE,
1148 a.last_updated_by = 1
1149 where a.TNS_ALIAS_ADDRESS_LIST_GUID = l_rec.TNS_ALIAS_ADDRESS_LIST_GUID;
1150
1151 end if;
1152
1153 end;
1154
1155 /*==========================================================================*/
1156
1157 procedure register_tnsalias_address ( TNS_ALIAS_ADDRESS_LIST_GUID raw,
1158 Listener_port_GUID raw
1159 )
1160 as
1161 l_rec fnd_tns_alias_addresses%rowtype;
1162 l_insert boolean := false;
1163
1164 begin
1165
1166 l_rec.TNS_ALIAS_ADDRESS_LIST_GUID := TNS_ALIAS_ADDRESS_LIST_GUID;
1167 l_rec.Listener_port_GUID := Listener_port_GUID;
1168
1169 begin
1170
1171 select a.TNS_ALIAS_ADDRESS_LIST_GUID,a.Listener_port_GUID
1172 into l_rec.TNS_ALIAS_ADDRESS_LIST_GUID,l_rec.Listener_port_GUID
1173 from fnd_tns_alias_addresses a
1174 where a.TNS_ALIAS_ADDRESS_LIST_GUID = l_rec.TNS_ALIAS_ADDRESS_LIST_GUID
1175 and a.Listener_port_GUID = l_rec.Listener_port_GUID;
1176
1177 exception
1178 when no_data_found then
1179 l_insert := true;
1180 end;
1181
1182 if ( l_insert )
1183 then
1184
1185 insert into fnd_tns_alias_addresses
1186 (TNS_ALIAS_ADDRESS_LIST_GUID,Listener_port_GUID,
1187 last_updated_by,last_update_date,last_update_login,
1188 creation_date,created_by
1189 )
1190 values (
1191 l_rec.TNS_ALIAS_ADDRESS_LIST_GUID,l_rec.Listener_port_GUID,
1192 1,sysdate,0,sysdate,1 );
1193 else
1194
1195 update fnd_tns_alias_addresses a
1196 set a.Listener_port_GUID=nvl(l_rec.Listener_port_GUID,
1197 a.Listener_port_GUID),
1198 a.last_update_date = SYSDATE,
1199 a.last_updated_by = 1
1200 where a.TNS_ALIAS_ADDRESS_LIST_GUID = l_rec.TNS_ALIAS_ADDRESS_LIST_GUID
1201 and a.Listener_port_GUID = l_rec.Listener_port_GUID;
1202
1203 end if;
1204
1205 end;
1206
1207 /*==========================================================================*/
1208
1209 procedure register_node( name varchar2, /* Max 30 bytes */
1210 platform_id number, /* Platform ID from BugDB */
1211 forms_tier varchar2, /* 'Y'/'N' */
1212 cp_tier varchar2, /* 'Y'/'N' */
1213 web_tier varchar2, /* 'Y'/'N' */
1214 admin_tier varchar2, /* 'Y'/'N' */
1215 p_server_id varchar2, /* ID of server */
1216 p_address varchar2, /* IP address of server */
1217 p_description varchar2, /* description of server*/
1218 p_host_name varchar2,
1219 p_domain varchar2,
1220 db_tier varchar2, /* 'Y'/'N' */
1221 p_virtual_ip varchar2 default null
1222 )
1223 as
1224
1225 register_node_complete boolean := false;
1226
1227 l_sql_str varchar2(2000);
1228 l_sql_str_dom varchar2(2000);
1229 l_sql_str_db_tier
1230 varchar2(2000);
1231 l_sql_str_db_tier_virtualip
1232 varchar2(2000);
1233 kount number;
1234
1235 begin
1236
1237
1238 l_sql_str := 'begin fnd_concurrent.register_node ' ||
1239 '(:v1, :v2, :v3, :v4, :v5, :v6, :v7, :v8, :v9); end;';
1240 l_sql_str_dom := 'begin fnd_concurrent.register_node' ||
1241 '(:v1, :v2, :v3, :v4, :v5, :v6, :v7, :v8, :v9, :v10, :v11); end;';
1242 l_sql_str_db_tier := 'begin fnd_concurrent.register_node' ||
1243 '(:v1, :v2, :v3, :v4, :v5, :v6, :v7, :v8, :v9, :v10, :v11, :v12); end;';
1244 l_sql_str_db_tier_virtualip := 'begin fnd_concurrent.register_node' ||
1245 '(:v1, :v2, :v3, :v4, :v5, :v6, :v7, :v8, :v9, :v10, :v11, :v12, :v13); end;';
1246
1247 -- Try with db_tier_virtualip
1248
1249 begin
1250
1251 execute immediate l_sql_str_db_tier_virtualip using
1252 name,
1253 platform_id ,
1254 forms_tier ,
1255 cp_tier ,
1256 web_tier ,
1257 admin_tier ,
1258 p_server_id ,
1259 p_address ,
1260 p_description ,
1261 p_host_name ,
1262 p_domain ,
1263 db_tier ,
1264 p_virtual_ip ;
1268 exception
1265
1266 register_node_complete := true;
1267
1269 when others then null;
1270 end;
1271
1272 if ( register_node_complete )
1273 then
1274 return;
1275 end if;
1276
1277 -- Try with db_tier
1278
1279 begin
1280
1281 execute immediate l_sql_str_db_tier using
1282 name,
1283 platform_id ,
1284 forms_tier ,
1285 cp_tier ,
1286 web_tier ,
1287 admin_tier ,
1288 p_server_id ,
1289 p_address ,
1290 p_description ,
1291 p_host_name ,
1292 p_domain ,
1293 db_tier ;
1294
1295 register_node_complete := true;
1296
1297 exception
1298 when others then null;
1299 end;
1300
1301 if ( register_node_complete )
1302 then
1303 return;
1304 end if;
1305
1306 -- Try with host/domain.
1307
1308 begin
1309
1310 execute immediate l_sql_str_dom using name,
1311 platform_id ,
1312 forms_tier ,
1313 cp_tier ,
1314 web_tier ,
1315 admin_tier ,
1316 p_server_id ,
1317 p_address ,
1318 p_description ,
1319 p_host_name ,
1320 p_domain ;
1321
1322 register_node_complete := true;
1323
1324 exception
1325 when others then null;
1326 end;
1327
1328 if ( register_node_complete )
1329 then
1330 return;
1331 end if;
1332
1333 -- Try without host/domain
1334
1335 begin
1336
1337 execute immediate l_sql_str using name,
1338 platform_id ,
1339 forms_tier ,
1340 cp_tier ,
1341 web_tier ,
1342 admin_tier ,
1343 p_server_id ,
1344 p_address ,
1345 p_description ;
1346
1347 register_node_complete := true;
1348
1349 exception
1350 when others then null;
1351 end;
1352
1353 if ( register_node_complete )
1354 then
1355 return;
1356 end if;
1357
1358 -- Register node doesn't even exist. Do the DML here.
1359
1360 -- Copied from AFCPUTLB.pls - 115.56
1361
1362 select count(*)
1363 into kount
1364 from fnd_nodes
1365 where upper(node_name) = upper(name);
1366
1367 if (kount = 0) then
1368 execute immediate
1369 'insert into fnd_nodes ' ||
1370 ' (node_id, node_name,' ||
1371 ' support_forms, support_cp, support_web, support_admin,' || ' platform_code, created_by, creation_date,' ||
1372 ' last_updated_by, last_update_date, last_update_login,' ||
1373 ' node_mode, server_id, server_address, description)' ||
1374 ' select ' ||
1375 ' fnd_nodes_s.nextval, :v1,' ||
1376 ' :v2, :v3, :v4, :v5, :v6, 1, SYSDATE, 1, SYSDATE, 0,' ||
1377 ' ''O'', :v7, :v8, :v9 ' ||
1378 ' from dual '
1379 using name, forms_tier, cp_tier, web_tier, admin_tier,
1380 platform_id, p_server_id, p_address, p_description;
1381 else
1382 update fnd_nodes
1383 set description = p_description,
1384 support_forms = decode(forms_tier, 'Y', 'Y', support_forms),
1385 support_cp = decode(cp_tier, 'Y', 'Y', support_cp),
1386 support_web = decode(web_tier, 'Y', 'Y', support_web),
1387 support_admin = decode(admin_tier, 'Y', 'Y', support_admin),
1388 platform_code = platform_id,
1389 last_update_date = SYSDATE, last_updated_by = 1
1390 where upper(node_name) = upper(name);
1391
1392 if (p_server_id is not null) then
1393 execute immediate 'update fnd_nodes ' ||
1394 ' set server_id = :v1' ||
1395 ' where upper(node_name) = upper(:v2) '
1396 using p_server_id, name;
1397 end if;
1398
1399 if (p_address is not null) then
1400 execute immediate 'update fnd_nodes ' ||
1401 ' set server_address = :v1' ||
1405 end if;
1402 ' where upper(node_name) = upper(:v2)'
1403 using p_address,name;
1404 end if;
1406
1407 register_node_complete := true;
1408
1409 end;
1410
1411 /*==========================================================================*/
1412
1413 procedure register_listener_ports (Listener_Name varchar2,
1414 Port number,
1415 server_guid raw,
1416 Listener_Port_Guid raw default null)
1417 as
1418 l_rec fnd_tns_listener_ports%rowtype;
1419 l_insert boolean := false;
1420 l_listener_name fnd_tns_listeners.listener_name%type;
1421 l_server_guid fnd_tns_listeners.server_guid%type;
1422 begin
1423
1424 l_Listener_Name := Listener_Name;
1425 l_rec.Listener_Port_Guid := Listener_Port_Guid;
1426 l_rec.Port := Port;
1427 l_server_guid := server_guid;
1428
1429 select a.Listener_GUID
1430 into l_rec.Listener_GUID
1431 from fnd_tns_listeners a
1432 where a.Server_GUID = l_server_guid
1433 and a.Listener_Name = l_Listener_Name;
1434
1435 if ( l_rec.Listener_Port_Guid is null )
1436 then
1437
1438 begin
1439
1440 select a.listener_port_guid
1441 into l_rec.listener_port_guid
1442 from fnd_tns_listener_ports a
1443 where a.listener_guid = l_rec.listener_GUID
1444 and a.port = l_rec.Port;
1445
1446 exception
1447 when no_data_found then
1448 l_insert := true;
1449 end;
1450
1451 end if;
1452
1453 if ( l_insert )
1454 then
1455 insert into fnd_tns_listener_ports
1456 (Listener_Port_GUID,Listener_GUID,Protocol,Port,
1457 Created_By,Creation_Date,
1458 Last_Updated_By,Last_Update_Date,Last_Update_Login)
1459 values (sys_guid(),l_rec.Listener_guid,fnd_app_system.c_protocol_tcp,
1460 l_rec.Port,1,sysdate,1,sysdate,0 );
1461 else
1462
1463 update fnd_tns_listener_ports a
1464 set a.port = nvl(l_rec.Port,a.port),
1465 a.last_update_date = SYSDATE,
1466 a.last_updated_by = 1
1467 where a.Listener_port_GUID = l_rec.Listener_port_GUID;
1468
1469 end if;
1470
1471 end;
1472
1473 /*=========================================================================*/
1474
1475 procedure register_tnsalias_sets ( Alias_set_Name varchar2,
1476 Alias_set_type varchar2 )
1477 as
1478 l_rec fnd_tns_alias_sets%rowtype;
1479 l_insert boolean := false;
1480
1481 begin
1482
1483 l_rec.tns_Alias_set_Name := Alias_set_Name;
1484 l_rec.tns_alias_set_type := alias_set_type;
1485
1486 begin
1487
1488 select tns_alias_set_guid
1489 into l_rec.tns_alias_set_guid
1490 from fnd_tns_alias_sets a
1491 where a.tns_alias_set_name = l_rec.tns_alias_set_name;
1492
1493 exception
1494 when no_data_found then
1495 l_insert := true;
1496 end;
1497
1498 if ( l_insert )
1499 then
1500
1501 insert into fnd_tns_alias_sets
1502 (TNS_ALIAS_SET_GUID,tns_Alias_set_name,tns_Alias_set_type,
1503 last_updated_by,last_update_date,last_update_login,
1504 creation_date,created_by
1505 )
1506 values (
1507 sys_guid(),l_rec.tns_Alias_set_Name,l_rec.tns_alias_set_type,
1508 1,sysdate,0,sysdate,1 );
1509 else
1510
1511 update fnd_tns_alias_sets a
1512 set a.tns_alias_set_name = nvl(l_rec.tns_Alias_set_Name,
1513 a.tns_alias_set_name),
1514 a.tns_alias_set_type = nvl(l_rec.tns_alias_set_type,
1515 a.tns_alias_set_type),
1516 a.last_update_date = SYSDATE,
1517 a.last_updated_by = 1
1518 where a.TNS_ALIAS_set_GUID = l_rec.TNS_ALIAS_set_GUID;
1519
1520 end if;
1521
1522 end;
1523
1524 /*===========================================================================*/
1525
1526 procedure register_aliasset_usage ( TNS_ALIAS_set_GUID raw,
1527 server_guid raw
1528 )
1529 as
1530 l_rec fnd_tns_alias_set_usage%rowtype;
1531 l_insert boolean := false;
1532
1533 begin
1534
1535 l_rec.TNS_ALIAS_set_GUID := TNS_ALIAS_set_GUID;
1536 l_rec.server_guid := server_guid;
1537
1538 begin
1539
1540 select a.TNS_ALIAS_set_GUID,a.server_guid
1541 into l_rec.TNS_ALIAS_set_GUID,l_rec.server_guid
1542 from fnd_tns_alias_set_usage a
1543 where a.TNS_ALIAS_set_GUID = l_rec.TNS_ALIAS_set_GUID
1544 and a.server_guid = l_rec.server_guid;
1545
1546 exception
1547 when no_data_found then
1548 l_insert := true;
1549 end;
1550
1551 if ( l_insert )
1552 then
1553
1554 insert into fnd_tns_alias_set_usage
1555 (TNS_ALIAS_set_guid,server_guid,
1556 last_updated_by,last_update_date,last_update_login,
1557 creation_date,created_by
1558 )
1559 values (
1560 l_rec.TNS_ALIAS_set_GUID,l_rec.server_guid,
1561 1,sysdate,0,sysdate,1 );
1562 else
1563
1564 update fnd_tns_alias_set_usage a
1565 set a.server_guid=nvl(l_rec.server_guid,a.server_guid),
1566 a.last_update_date = SYSDATE,
1567 a.last_updated_by = 1
1568 where a.TNS_ALIAS_set_GUID = l_rec.TNS_ALIAS_set_GUID
1569 and a.server_guid = l_rec.server_guid;
1570
1571 end if;
1572
1573 end;
1574
1575 end FND_APP_SYSTEM;