1 PACKAGE BODY fnd_app_server_pkg AS
2 /* $Header: AFSCASRB.pls 120.11 2010/05/21 17:38:28 pdeluna ship $ */
3
4 /* create_server_id
5 **
6 ** Returns a new unique server_id. The server_id is a string of 64 characters.
7 ** The 1st 32 is a globally unique id and the next 32 is a randomnly
8 ** generated number. Dynamic sql is being used to create the globally unique id
9 ** because PL/SQL doesn't yet support it. This is called by
10 ** AdminAppServer.createServerId().
11 */
12 FUNCTION create_server_id
13 RETURN VARCHAR2
14 IS
15 svrid fnd_nodes.server_id%TYPE;
16 guid VARCHAR2(32);
17 rnd_dt VARCHAR2(32);
18 curs INTEGER;
19 sqlbuf VARCHAR2(2000);
20 rows INTEGER;
21 BEGIN
22 curs := dbms_sql.open_cursor;
23 sqlbuf := 'select substr(RawToHex(sys_op_guid()),0,32) from dual';
24 dbms_sql.parse(curs, sqlbuf, dbms_sql.v7);
25 dbms_sql.define_column(curs, 1, guid, 32);
26 rows := dbms_sql.execute(curs);
27 rows := dbms_sql.fetch_rows(curs);
28 dbms_sql.column_value(curs, 1, guid);
29 dbms_sql.close_cursor(curs);
30 fnd_random_pkg.init(7);
31 fnd_random_pkg.seed(to_number(TO_CHAR(sysdate, 'JSSSSS')), 10, FALSE);
32 rnd_dt:=SUBSTR((TO_CHAR(fnd_random_pkg.get_next)||TO_CHAR(fnd_random_pkg.get_next)||TO_CHAR(fnd_random_pkg.get_next)||TO_CHAR(fnd_random_pkg.get_next)),0,32);
33 RETURN guid||rnd_dt;
34 END;
35
36 /* get_platform_code
37 **
38 ** This is a private API that Returns the platform_code based on the
39 ** lookup_type 'PLATFORM' defined in afoamluplt.ldt. This is called by
40 ** insert_server and update_server.
41 **
42 ** Testcase to test API:
43 set serverout on
44 begin
45 dbms_output.put_line('Solaris = '||
46 FND_APP_SERVER_PKG.get_platform_code('Solaris'));
47 dbms_output.put_line('HP-UX = '||
48 FND_APP_SERVER_PKG.get_platform_code('HP-UX'));
49 dbms_output.put_line('UNIX Alpha = '||
50 FND_APP_SERVER_PKG.get_platform_code('UNIX Alpha'));
51 dbms_output.put_line('IBM AIX = '||
52 FND_APP_SERVER_PKG.get_platform_code('IBM AIX'));
53 dbms_output.put_line('Intel_Solaris = '||
54 FND_APP_SERVER_PKG.get_platform_code('Intel_Solaris'));
55 dbms_output.put_line('Linux = '||
56 FND_APP_SERVER_PKG.get_platform_code('Linux'));
57 dbms_output.put_line('Windows NT = '||
58 FND_APP_SERVER_PKG.get_platform_code('Windows NT'));
59 dbms_output.put_line('Others = '||
60 FND_APP_SERVER_PKG.get_platform_code('Others'));
61 -- R12 only
62 dbms_output.put_line('Others = '||
63 FND_APP_SERVER_PKG.get_platform_code('LINUX_X86-64'));
64 end;
65
66 Output should be similar to this:
67
68 Solaris = 453 (23 for R12)
69 HP-UX = 2 (59 for R12)
70 UNIX Alpha = 87
71 IBM AIX = 319 (212 for R12)
72 Intel_Solaris = 173
73 Linux = 46
74 Windows NT = 912
75 Others = 100000
76 LINUX_X86-64 = 226 (R12)
77 */
78 FUNCTION get_platform_code(p_platform IN VARCHAR2)
79 RETURN VARCHAR2
80 IS
81 l_platform_code VARCHAR2(30);
82 BEGIN
83 -- The FND_LOOKUP_VALUES.TAG column was updated for the PLATFORM lookup_type
84 -- to support the p_platform values derived from
85 -- SystemCheck.DetectPlatform().
86 --
87 -- The following query was modeled not to violate the FND_LOOKUP_VALUES_U1
88 -- unique index. See bug 5723530.
89 SELECT lookup_code
90 INTO l_platform_code
91 FROM fnd_lookup_values
92 WHERE lookup_type = 'PLATFORM'
93 AND tag = p_platform
94 AND language = userenv('LANG')
95 AND view_application_id = 0
96 AND security_group_id = 0;
97
98 RETURN l_platform_code;
99 EXCEPTION
100 WHEN no_data_found THEN
101 raise no_data_found;
102 END;
103
104 /* node_name_exists
105 **
106 ** Check to see if the node_name already exists in FND_NODES
107 */
108 FUNCTION node_name_exists(p_node_name IN VARCHAR2)
109 RETURN BOOLEAN
110 IS
111 kount NUMBER := 0;
112 l_node_name VARCHAR2(30) := UPPER(p_node_name);
113 BEGIN
114 SELECT COUNT(*)
115 INTO kount
116 FROM fnd_nodes
117 WHERE upper(node_name) = l_node_name;
118
119 IF kount > 0 THEN
120 RETURN true;
121 ELSE
122 RETURN false;
123 END IF;
124 END;
125
126 /* desktop_node_exists
127 **
128 ** Check to see if the node_name already exists in FND_NODES where the
129 ** support_* columns = 'N'.
130 */
131 FUNCTION desktop_node_exists(p_node_name IN OUT NOCOPY VARCHAR2)
132 RETURN BOOLEAN
133 IS
134 kount NUMBER := 0;
135 l_node_name VARCHAR2(30) := UPPER(p_node_name);
136 BEGIN
137 -- Check if a node_name exists with support_* = 'N' and server_address <> '*'
138 SELECT COUNT(*)
139 INTO kount
140 FROM fnd_nodes
141 WHERE upper(node_name) = l_node_name
142 AND SUPPORT_CP = 'N'
143 AND SUPPORT_FORMS = 'N'
144 AND SUPPORT_WEB = 'N'
145 AND SUPPORT_ADMIN = 'N'
146 AND SUPPORT_DB = 'N'
147 AND PLATFORM_CODE = '100000'
148 AND ((SERVER_ADDRESS IS NOT NULL
149 AND SERVER_ADDRESS <> '*')
150 OR SERVER_ADDRESS IS NULL);
151
152 IF kount > 0 THEN
153 RETURN true;
154 ELSE
155 RETURN false;
156 END IF;
157 END;
158
159 /* server_address_exists
160 **
161 ** Check to see if the server_address already exists in FND_NODES
162 */
163 FUNCTION server_address_exists(p_address IN VARCHAR2)
164 RETURN BOOLEAN
165 IS
166 kount NUMBER := 0;
167 BEGIN
168 SELECT COUNT(*)
169 INTO kount
170 FROM fnd_nodes
171 WHERE server_address = p_address;
172
173 IF kount > 0 THEN
174 RETURN true;
175 ELSE
176 RETURN false;
177 END IF;
178 END;
179
180 /* get_server_id
181 **
182 ** Returns the server_id given a unique node_name or server_address. This API is
183 ** biased towards the unique node_name. The server_address is used only when the
184 ** node_name cannot be used. server_id can be null if the node has been
185 ** deleted" using delete_server or delete_desktop_server.
186 */
187 FUNCTION get_server_id
188 (
189 p_node_name IN VARCHAR2 DEFAULT NULL,
190 p_address IN VARCHAR2 DEFAULT NULL)
191 RETURN VARCHAR2
192 IS
193 l_server_id fnd_nodes.server_id%TYPE := NULL;
194 l_node_name VARCHAR2(30) := UPPER(p_node_name);
195 BEGIN
196 -- Node name is unique, so if node name is passed in, that is what the code
197 -- uses to obtain the server_id.
198 IF l_node_name IS NOT NULL AND node_name_exists(l_node_name) THEN
199 SELECT server_id
200 INTO l_server_id
201 FROM fnd_nodes
202 WHERE upper(node_name) = l_node_name;
203 -- Server address should be unique but since it is nullable in FND_NODES,
204 -- it may not be the best condition to use to derive a unique server_id.
205 -- However, if the server_address exists, return one of the server_ids.
206 elsif p_address IS NOT NULL AND server_address_exists(p_address) THEN
207 SELECT server_id
208 INTO l_server_id
209 FROM fnd_nodes
210 WHERE server_address = p_address
211 AND server_id IS NOT NULL
212 AND rownum < 2;
213 END IF;
214
215 RETURN l_server_id;
216 END;
217
218 /* get_server_address
219 **
220 ** Returns the server_address given a unique node_name. server_address can be
221 ** null for desktop nodes.
222 **
223 */
224 FUNCTION get_server_address(p_node_name IN VARCHAR2)
225 RETURN VARCHAR2
226 IS
227 l_server_address fnd_nodes.server_address%TYPE := NULL;
228 l_node_name VARCHAR2(30) := UPPER(p_node_name);
229 BEGIN
230 IF node_name_exists(l_node_name)THEN
231 SELECT server_address
232 INTO l_server_address
233 FROM fnd_nodes
234 WHERE upper(node_name) = l_node_name;
235 END IF;
236
237 RETURN l_server_address;
238 END;
239
240 /* get_node_name
241 **
242 ** Returns a node_name given a server_address. server_address is nullable and
243 ** has no unique constraint, so it is possible to have multiple nodes having the
244 ** same server_address. In such a case, the API will return one of the
245 ** node_name(s).
246 **
247 */
248 FUNCTION get_node_name(p_address IN VARCHAR2)
249 RETURN VARCHAR2
250 IS
251 l_node_name fnd_nodes.node_name%TYPE := NULL;
252 BEGIN
253 IF server_address_exists(p_address)THEN
254 SELECT node_name
255 INTO l_node_name
256 FROM fnd_nodes
257 WHERE server_address = p_address
258 AND node_name IS NOT NULL
259 AND rownum < 2;
260 END IF;
261
262 RETURN l_node_name;
263 END;
264
265 /* insert_server
266 **
267 ** Inserts information for a new Application Server. The function
268 ** create_server_id must be called to generate a valid id prior to
269 ** calling this api. This is called by AdminAppServer.changeServerInDB().
270 **
271 ** FND_APP_SERVER_PKG.INSERT_SERVER is a wrapper to
272 ** FND_CONCURRENT.REGISTER_NODE.
273 */
274 PROCEDURE insert_server
275 (
276 p_server_id IN OUT NOCOPY VARCHAR2,
277 p_address IN VARCHAR2,
278 p_node_name IN VARCHAR2,
279 p_description IN VARCHAR2 DEFAULT NULL,
280 p_webhost IN VARCHAR2 DEFAULT NULL,
281 p_platform_code IN VARCHAR2 DEFAULT NULL,
282 p_support_cp IN VARCHAR2 DEFAULT NULL,
283 p_support_forms IN VARCHAR2 DEFAULT NULL,
284 p_support_web IN VARCHAR2 DEFAULT NULL,
285 p_support_admin IN VARCHAR2 DEFAULT NULL,
286 p_support_db IN VARCHAR2 DEFAULT NULL)
287 IS
288 kount NUMBER := 0;
289 curr_node_id NUMBER := 0;
290 curr_node_name VARCHAR2(30) := NULL;
291 l_node_name VARCHAR2(30) := UPPER(p_node_name);
292 l_platform_code VARCHAR2(30);
293 BEGIN
294 -- Bug 3736714: Platform Code is required by FND_NODES to register a
295 -- node. AdminAppServer has been modified to support platform code.
296 l_platform_code := get_platform_code(p_platform_code);
297
298 -- If a desktop node is being inserted using insert_server, then redirect to
299 -- insert_desktop_server. A desktop node has the support_* columns = 'N' and
300 -- p_address <> '*'.
301 IF (p_support_cp = 'N' AND p_support_forms = 'N' AND p_support_web = 'N' AND
302 p_support_admin = 'N' AND p_support_db = 'N') AND ((p_address IS NOT NULL
303 AND p_address <> '*') OR p_address IS NULL) THEN
304 insert_desktop_server(l_node_name, p_server_id, p_address, p_description);
305 ELSE
306 -- If node exists and
307 IF server_address_exists(p_address) THEN
308 /* Added for Bug 3736714 to clean up entries that have node names which
309 ** are not fully qualified hostnames.
310 */
311 -- If platform_code passed in is UNIX Alpha
312 IF (l_platform_code = '87') THEN
313 -- Then, get the node_id and node_name of the existing node
314 -- entry.
315 SELECT node_id, node_name
316 INTO curr_node_id, curr_node_name
317 FROM fnd_nodes
318 WHERE server_address = p_address;
319
320 -- If the current node_name is not a fully qualified hostname
321 IF INSTR(curr_node_name, '.') = 0 THEN
322 -- Insert the new node using fnd_concurrent.register_node
323 FND_CONCURRENT.REGISTER_NODE(
324 name => l_node_name,
325 platform_id => to_number(l_platform_code),
326 forms_tier => p_support_forms,
327 cp_tier => p_support_cp,
331 p_address => p_address,
328 web_tier => p_support_web,
329 admin_tier => p_support_admin,
330 p_server_id => p_server_id,
332 p_description => p_description,
333 db_tier => p_support_db);
334
335 -- Update the old node with the correct platform and null
336 -- out server_id and server_address so that the node does
337 -- not get referenced again.
338 UPDATE FND_NODES
339 SET PLATFORM_CODE = '87',
340 SERVER_ADDRESS = NULL,
341 SERVER_ID = NULL
342 WHERE NODE_ID = curr_node_id;
343
344 -- Added for Bug 3292353.
345 SELECT COUNT(*)
346 INTO kount
347 FROM fnd_nodes
348 WHERE UPPER(node_name) = l_node_name;
349
350 -- Since fnd_concurrent.register_node() does not handle
351 -- the hostname at this time, it will be manually inserted
352 -- using the update_server API IF the node was properly
353 -- inserted using fnd_concurrent.register_node().
354 IF kount > 0 THEN
355 -- If webhost was provided.
356 IF (p_webhost IS NOT NULL) THEN
357 update_server(p_server_id, p_address, p_description, p_webhost);
358 END IF;
359 END IF; -- kount > 0
360 END IF; -- INSTR(curr_node_name, '.') = 0
361
362 ELSE
363 /* Node already exists. It should not be inserted again, just
364 updated.*/
365 update_server(p_server_id, p_address, p_description, p_webhost,
366 p_platform_code);
367 END IF; -- (l_platform_code = '87')
368
369 ELSE /* Node does not exist, the server will be inserted. */
370
371 -- Check if p_node_name is a fully qualified hostname with domain.
372 -- FND_NODES.NODE_NAME should only have hostname if platform is not
373 -- UNIX Alpha.
374 IF (l_platform_code <> '87' AND INSTR(p_node_name, '.') <> 0) THEN
375 -- The hostname should be the beginning of the string up until
376 -- the first period. FND_NODES.NODE_NAME is stored in
377 -- UPPERCASE.
378 l_node_name := UPPER(SUBSTR(p_node_name, 0,
379 INSTR(p_node_name, '.') - 1));
380 END IF;
381
382 -- Insert the node using fnd_concurrent.register_node
383 FND_CONCURRENT.REGISTER_NODE(
384 name => l_node_name,
385 platform_id => to_number(l_platform_code),
386 forms_tier => p_support_forms,
387 cp_tier => p_support_cp,
388 web_tier => p_support_web,
389 admin_tier => p_support_admin,
390 p_server_id => p_server_id,
391 p_address => p_address,
392 p_description => p_description,
393 db_tier => p_support_db);
394
395 -- Added for Bug 3292353.
396 SELECT COUNT(*)
397 INTO kount
398 FROM fnd_nodes
399 WHERE UPPER(node_name) = l_node_name;
400
401 -- Since fnd_concurrent.register_node() does not handle the hostname
402 -- at this time, it will be manually inserted using the
403 -- update_server API IF the node was properly inserted using
404 -- fnd_concurrent.register_node().
405 IF kount > 0 THEN
406 -- If webhost was provided, update only the servers, not desktop
407 -- nodes.
408 IF (p_webhost IS NOT NULL) AND (l_platform_code <> '100000') THEN
409 update_server(p_server_id, p_address, p_description, p_webhost);
410 END IF;
411 ELSE -- kount = 0
412 -- Bug 5279502: DESKTOP NODE SUPPORT IN FND_APP_SERVER_PKG
413 -- Added this for better error handling.
414 -- If for any reason that the FND_CONCURRENT.REGISTER_NODE() fails
415 -- to add the node, no_data_found should be raised to signal the
416 -- failure to add the node_name. If the node existed prior to
417 -- calling FND_CONCURRENT.REGISTER_NODE(), kount should still be
418 -- > 0.
419 RAISE no_data_found;
420 END IF;
421 END IF; -- kount > 0
422 END IF; -- desktop node
423 END insert_server;
424
425 /* delete_server
426 **
427 ** This procedure used to remove an Application Server row from the database.
428 ** Due to the migration of FND_APPLICATION_SERVERS to FND_NODES,
429 ** fnd_nodes.server_id is nulled out instead in order to preserve the
430 ** node_name and avoid dangling references to the node_name. This is called by
431 ** AdminAppServer.delSvrFromDB().
432 */
433 PROCEDURE delete_server(p_address IN VARCHAR2)
434 IS
435 BEGIN
436 UPDATE fnd_nodes
437 SET server_id = NULL
438 WHERE server_address = p_address;
439 EXCEPTION
440 WHEN no_data_found THEN
441 raise no_data_found;
442 END;
443
444 /* update_server
445 **
446 ** This procedure should only be used for updating Application Server Nodes.
447 ** The server_id, description, host and domain are updated if they are not
448 ** NULL. If a new server_id is required, the create_server_id function should
449 ** be called prior to this. This is called by
450 ** AdminAppServer.changeServerInDB().
451 */
452 PROCEDURE update_server
453 (
454 p_server_id IN VARCHAR2 DEFAULT NULL,
455 p_address IN VARCHAR2,
456 p_description IN VARCHAR2 DEFAULT NULL,
457 p_webhost IN VARCHAR2 DEFAULT NULL,
458 p_platform IN VARCHAR2 DEFAULT NULL)
459 IS
460 l_node_name VARCHAR2(30);
461 l_support_cp VARCHAR2(1);
462 l_support_forms VARCHAR2(1);
463 l_support_web VARCHAR2(1);
464 l_support_admin VARCHAR2(1);
465 l_support_db VARCHAR2(1);
466 l_platform VARCHAR2(30);
467 l_platform2 VARCHAR2(30);
471 -- The API should not allow updates of Desktop Nodes. This query obtains
468
469 BEGIN
470 /* Bug 5279502: DESKTOP NODE SUPPORT IN FND_APP_SERVER_PKG */
472 -- the value of columns that determines a Desktop Node.
473
474 SELECT NODE_NAME,
475 SUPPORT_CP,
476 SUPPORT_FORMS,
477 SUPPORT_WEB,
478 SUPPORT_ADMIN,
479 SUPPORT_DB,
480 PLATFORM_CODE
481 INTO l_node_name,
482 l_support_cp,
483 l_support_forms,
484 l_support_web,
485 l_support_admin,
486 l_support_db,
487 l_platform
488 FROM fnd_nodes
489 WHERE server_address = p_address;
490
491 IF SQL%notfound THEN
492 RAISE no_data_found;
493 END IF;
494
495 -- Desktop Nodes will have the SUPPORT_* columns explicitly set to 'N' and
496 -- PLATFORM_CODE = '100000' when registered. If any of the column values
497 -- are not what a Desktop should have, then it can be processed in this API
498 IF (((l_support_cp = 'Y' OR l_support_forms = 'Y' OR l_support_web = 'Y' OR
499 l_support_admin = 'Y' OR l_support_db = 'Y') AND (p_address IS NOT NULL))
500 -- Bug 9688017: added for Authentication row
501 OR (p_address = '*')) THEN
502
503 IF(l_node_name <> UPPER(l_node_name)) THEN
504 UPDATE fnd_nodes
505 SET node_name = UPPER(l_node_name)
506 WHERE server_address = p_address;
507 END IF;
508
509 IF(p_server_id IS NOT NULL) THEN
510 UPDATE fnd_nodes
511 SET server_id = p_server_id
512 WHERE server_address = p_address;
513 END IF;
514
515 IF(p_description IS NOT NULL) THEN
516 UPDATE fnd_nodes
517 SET description = p_description
518 WHERE server_address = p_address;
519 END IF;
520
521 -- Added for Bug 3292353. ICX code will be calling FND_NODES.WEBHOST so
522 -- there needs to be a way to populate/update the column. This may
523 -- later be changed to use CP APIs.
524 IF(p_webhost IS NOT NULL) THEN
525 UPDATE fnd_nodes
526 SET webhost = p_webhost
527 WHERE server_address = p_address;
528 END IF;
529
530 -- Added for Bug 3736714. Since AdminAppServer derives the platform and
531 -- only AdminAppServer calls FND_APP_SERVER_PKG, then if a platform is
532 -- passed in, the platform_code is likely correct and the platform_code
533 -- for the node needs to be updated.
534 IF (p_platform IS NOT NULL) THEN
538 WHERE server_address = p_address;
535 l_platform2 := get_platform_code(p_platform);
536 UPDATE fnd_nodes
537 SET PLATFORM_CODE = l_platform2
539 END IF;
540
541 /*
542 Bug 3773424:AUTHENTICATION NODE HAS NO VALUES FOR FND_NODES.SUPPORT_*
543 COLUMNS. A system alert is being logged stating that it "Could not
544 contact Service Manager FNDSM_AUTHENTICATION_*". It seems that the
545 ICM attempts to tnsping all remote Service Managers using the query:
546
547 SELECT NODE_NAME, STATUS, NODE_MODE
548 FROM FND_NODES
549 WHERE NOT (SUPPORT_CP = 'N' AND
550 SUPPORT_WEB = 'N' AND SUPPORT_FORMS = 'N' AND
551 SUPPORT_ADMIN = 'N' AND SUPPORT_CP is NOT NULL AND
552 SUPPORT_WEB is NOT NULL AND
553 SUPPORT_ADMIN is NOT NULL AND SUPPORT_FORMS is NOT NULL)
554
555 Since the AUTHENTICATION row is inserted without these SUPPORT_*
556 columns, the system alert gets logged. This IF block has been added
557 to check whether those columns have a value for the AUTHENTICATION row.
558 It the column(s) have a null value, 'N' is explicitly set for the
559 applicable SUPPORT_* column.
560 */
561 IF (p_address = '*') THEN
562
563 IF (l_support_cp IS NULL) THEN
564 UPDATE fnd_nodes
565 SET SUPPORT_CP = 'N'
566 WHERE server_address = p_address;
567 END IF;
568
569 IF (l_support_forms IS NULL) THEN
570 UPDATE fnd_nodes
571 SET SUPPORT_FORMS = 'N'
572 WHERE server_address = p_address;
573 END IF;
574
575 IF (l_support_web IS NULL) THEN
576 UPDATE fnd_nodes
577 SET SUPPORT_WEB = 'N'
578 WHERE server_address = p_address;
579 END IF;
580
581 IF (l_support_admin IS NULL) THEN
582 UPDATE fnd_nodes
583 SET SUPPORT_ADMIN = 'N'
584 WHERE server_address = p_address;
585 END IF;
586
587 IF (l_support_db IS NULL) THEN
588 UPDATE fnd_nodes
589 SET SUPPORT_DB = 'N'
590 WHERE server_address = p_address;
591 END IF;
592
596 IF (p_platform IS NOT NULL) THEN
593 /* Bug 3736714 - Need to ensure that AUTHENTICATION row has the right
594 ** platform.
595 */
597 l_platform2 := get_platform_code(p_platform);
598 IF (l_platform <> l_platform2) THEN
599 UPDATE fnd_nodes
600 SET PLATFORM_CODE = l_platform2
601 WHERE server_address = p_address;
602 END IF;
603 END IF;
604 END IF;
605 END IF; -- IF THEN for Desktop Node
606 END update_server;
607
608 /* authenticate
609 **
610 ** This procedure is used to turn toggle AUTHENTICATION for an Application
611 ** Server Node. If the AUTHENTICATION row does not exist yet, the procedure
612 ** will insert it. The row with server_address='*' indicates the authentication
613 ** value. If the row already exists, the procedure updates the value to what
614 ** has been passed. The valid AUTHENTICATION values are:
615 ** 'ON'
616 ** 'OFF'
617 ** 'SECURE'
618 ** This is called by AdminAppServer.setAuthentication().and ((SERVER_ADDRESS
619 ** is not NULL and SERVER_ADDRESS <> '*') or SERVER_ADDRESS is null);
620 **
621 ** Bug 3736714: the p_platform argument was added so that the authentication
622 ** row can be added with the correct platform. The platform is determined in
623 ** AdminAppServer.java.
624 */
625 PROCEDURE authenticate
626 (
627 p_value IN OUT NOCOPY VARCHAR2,
628 p_platformcode IN VARCHAR2 DEFAULT NULL )
629 IS
630 BEGIN
631 /* Bug 3736714 - The AUTHENTICATION row should be seeded with the correct
632 ** platform even if it isn't technically a node.
633 */
634 update_server(
635 p_server_id => p_value,
636 p_address => '*',
637 p_description => 'Authentication Value',
638 p_platform => p_platformcode);
639 EXCEPTION
640 WHEN no_data_found THEN
641 /*
642 insert_server(p_value,'*','AUTHENTICATION','Authentication value');
643
644 Bug 3773424:AUTHENTICATION NODE HAS NO VALUES FOR FND_NODES.SUPPORT_*
645 COLUMNS. A system alert is being logged stating that it "Could not
646 contact Service Manager FNDSM_AUTHENTICATION_*". It seems that the
647 ICM attempts to tnsping all remote Service Managers using the query:
648
649 SELECT NODE_NAME, STATUS, NODE_MODE
650 FROM FND_NODES
651 WHERE NOT (SUPPORT_CP = 'N' AND
652 SUPPORT_WEB = 'N' AND SUPPORT_FORMS = 'N' AND
653 SUPPORT_ADMIN = 'N' AND SUPPORT_CP is NOT NULL AND
654 SUPPORT_WEB is NOT NULL AND
655 SUPPORT_ADMIN is NOT NULL AND SUPPORT_FORMS is NOT NULL)
656
657 Since the AUTHENTICATION row is inserted without these SUPPORT_*
658 columns, the system alert gets logged. The call to insert_server has
659 been modified to explicitly set the SUPPORT_* with a value of 'N'.
660 */
661 insert_server (
662 p_server_id => p_value,
663 p_address => '*',
664 p_node_name => 'AUTHENTICATION',
665 p_description => 'Authentication value',
666 p_platform_code => p_platformcode,
667 p_support_cp => 'N',
668 p_support_forms => 'N',
669 p_support_web => 'N',
670 p_support_admin => 'N',
671 p_support_db => 'N');
672 END;
673
674 /* insert_desktop_server
675 **
676 ** This API is used for Desktop Nodes only.
677 ** It calls insert_server and sets all the SUPPORT_* collumns to 'N' and the
678 ** PLATFORM_CODE to 'Others'. It also places 'Desktop Node' as the description
679 ** if NULL was passed.
680 ** A server_id is passed into this API from the caller. This API does not
681 ** check whether the server_id exists because the caller would have already
682 ** checked whether it exists or not.
683 */
684 PROCEDURE insert_desktop_server
685 (
686 p_node_name IN VARCHAR2,
687 p_server_id IN OUT NOCOPY VARCHAR2,
688 p_address IN VARCHAR2 DEFAULT NULL,
689 p_description IN VARCHAR2 DEFAULT NULL)
690 IS
691 l_node_name fnd_nodes.node_name%TYPE := UPPER(p_node_name);
692 l_node_name_from_address fnd_nodes.node_name%TYPE := NULL;
693 l_server_id fnd_nodes.server_id%TYPE := NULL;
694 l_server_address fnd_nodes.server_address%TYPE;
695 BEGIN
696 /* Bug 5279502: DESKTOP NODE SUPPORT IN FND_APP_SERVER_PKG */
697 /*===============+
698 | Desktop Node |
699 ================*/
700 -- If the node_name is used by a desktop node, then just update the
701 -- desktop node record.
702 IF desktop_node_exists(l_node_name) THEN
706 -- Case 1. node_name does not exist in FND_NODES
703 update_desktop_server(l_node_name, p_server_id, p_address, p_description);
704 ELSE
705
707 -- and server_address does not exist or is null.
708 -- Action: register desktop_node using FND_CONCURRENT.REGISTER_NODE
709 IF (NOT node_name_exists(l_node_name) AND ((p_address IS NOT NULL AND
710 NOT server_address_exists(p_address)) OR p_address IS NULL)) THEN
711 -- Insert the node using fnd_concurrent.register_node
712 FND_CONCURRENT.REGISTER_NODE(
713 name => l_node_name,
714 platform_id => to_number(get_platform_code('Others')),
715 forms_tier => 'N',
716 cp_tier => 'N',
717 web_tier => 'N',
718 admin_tier => 'N',
719 p_server_id => NVL(insert_desktop_server.p_server_id,
720 FND_APP_SERVER_PKG.CREATE_SERVER_ID),
721 p_address => insert_desktop_server.p_address,
722 p_description => NVL(insert_desktop_server.p_description,
723 'Desktop Node'),
724 db_tier => 'N');
725
726 -- Case 2. node_name exists in FND_NODES
727 -- Action: return server_id of node. if server_id is null, then:
728 -- a. use server_id passed in,
729 -- b. update record and convert node into desktop_node
730 -- c. return server_id of node.
731 ELSIF node_name_exists(l_node_name) THEN
732 -- node_name exists, retrieve the server_id of existing node and return
733 -- the server_id to the desktop node, so that it builds the dbc file with
734 -- that server_id.
735 l_server_id := get_server_id(l_node_name, NULL);
736 -- If server_id is null, then the node was deleted using
737 -- delete_server. So, the node is currently a deleted server_node.
738 IF l_server_id IS NULL THEN
739 -- Use the server_id passed in and convert the deleted server node into
740 -- a desktop node. Note that the server address provided is not updated
741 -- for the desktop node, primarily because the server_node usually has
742 -- the right server_address and there's no need to change.
743 UPDATE fnd_nodes
744 SET server_id = insert_desktop_server.p_server_id,
745 support_forms = 'N',
746 support_cp = 'N',
747 support_web = 'N',
748 support_admin = 'N',
749 support_db = 'N',
750 platform_code = '100000',
751 description = 'Desktop Node, converted from Server'
752 WHERE upper(node_name) = l_node_name
753 AND ((SERVER_ADDRESS IS NOT NULL
754 AND SERVER_ADDRESS <> '*')
755 OR SERVER_ADDRESS IS NULL);
756 ELSE
757 p_server_id := l_server_id;
758 END IF;
759
760 -- Case 3. node_name does not exist BUT server_address exists.
761 -- Action: return server_id of one of the nodes. If server_id is null, then:
762 -- a. use server_id passed in
763 -- b. update record and convert node into desktop_node
764 -- c. return server_id of node.
765 ELSIF (NOT node_name_exists(l_node_name) AND (p_address IS NOT NULL
766 AND server_address_exists(p_address))) THEN
767 -- node_name does not exist but server_address does, so retrieve the
768 -- server_id of existing node and return the server_id to the desktop node,
769 -- so that it builds the dbc file with that server_id.
770 l_server_id := get_server_id(NULL, p_address);
771
772 -- If server_id is null, then the node was deleted using
773 -- delete_server. So, the node is currently a deleted server_node.
774 IF l_server_id IS NULL THEN
775
776 -- Get the node_name of a node using server_address to use as the
777 -- condition to update the record. Since it is possible to have
778 -- multiple nodes with the same server_address and the server_id
779 -- returned for the server_address provided, we can convert one of the
780 -- nodes by obtaining a unique node_name.
781 l_node_name_from_address := get_node_name(p_address);
782
783 -- Use the server_id passed in and convert the deleted server node into
784 -- a desktop node.
785 UPDATE fnd_nodes
786 SET server_id = insert_desktop_server.p_server_id,
787 support_forms = 'N',
788 support_cp = 'N',
789 support_web = 'N',
790 support_admin = 'N',
791 support_db = 'N',
792 platform_code = '100000',
793 description = 'Desktop Node, converted from Server'
794 WHERE upper(node_name) = l_node_name_from_address
795 AND ((SERVER_ADDRESS IS NOT NULL
796 AND SERVER_ADDRESS <> '*')
797 OR SERVER_ADDRESS IS NULL);
798 ELSE
799 p_server_id := l_server_id;
800 END IF;
801 END IF;
802 END IF;
803 END insert_desktop_server;
804
805 /* update_desktop_server
806 **
807 ** This API is used for Desktop Nodes only.
811 ** where condition.
808 ** Update the FND_NODES row associated with p_node_name with the specified
809 ** values for server_id, address, and description. If NULLs are passed, do not
810 ** update. update_server cannot be used here because it uses p_address as the
812 */
813 PROCEDURE update_desktop_server
814 (
815 p_node_name IN VARCHAR2,
816 p_server_id IN VARCHAR2 DEFAULT NULL,
817 p_address IN VARCHAR2 DEFAULT NULL,
818 p_description IN VARCHAR2 DEFAULT NULL)
819 IS
820 kount NUMBER := 0;
821 l_node_name fnd_nodes.node_name%TYPE := UPPER(p_node_name);
822 BEGIN
823 -- If desktop node exists, proceed to update the desktop node.
824 -- If not a desktop node, do nothing. A desktop node maybe using the
825 -- server_id of a server node. A desktop node cannot update a server node's
826 -- data.
827 IF desktop_node_exists(l_node_name) THEN
828 -- If server_id is provided, update using the node_name as condition.
829 IF(p_server_id IS NOT NULL) THEN
830 UPDATE fnd_nodes
831 SET server_id = p_server_id
832 WHERE upper(node_name) = l_node_name
833 AND support_cp = 'N'
834 AND support_forms = 'N'
835 AND support_web = 'N'
836 AND support_admin = 'N'
837 AND support_db = 'N'
838 AND platform_code = '100000'
839 AND ((SERVER_ADDRESS IS NOT NULL
840 AND SERVER_ADDRESS <> '*')
841 OR SERVER_ADDRESS IS NULL);
842 END IF;
843
844 -- If a server_address is given, update using the node_name as
845 -- condition. server_address cannot be equal to '*' for a desktop node.
846 -- The (p_address <> '*') condition is intentionally left redundant.
847 IF (p_address IS NOT NULL) AND (p_address <> '*')
848 AND NOT (server_address_exists(p_address)) THEN
849 UPDATE fnd_nodes
850 SET server_address = p_address
851 WHERE upper(node_name) = l_node_name
852 AND support_cp = 'N'
853 AND support_forms = 'N'
854 AND support_web = 'N'
855 AND support_admin = 'N'
856 AND support_db = 'N'
857 AND platform_code = '100000'
858 AND ((SERVER_ADDRESS IS NOT NULL
859 AND SERVER_ADDRESS <> '*')
860 OR SERVER_ADDRESS IS NULL);
861 END IF;
862
863 -- If a description is given, update using the node_name as condition.
864 -- Default value is 'Desktop Node', per insert_desktop_server().
865 IF(p_description IS NOT NULL) THEN
866 UPDATE fnd_nodes
867 SET description = p_description
868 WHERE upper(node_name) = l_node_name
869 AND support_cp = 'N'
870 AND support_forms = 'N'
871 AND support_web = 'N'
872 AND support_admin = 'N'
873 AND support_db = 'N'
874 AND platform_code = '100000'
875 AND ((SERVER_ADDRESS IS NOT NULL
876 AND SERVER_ADDRESS <> '*')
877 OR SERVER_ADDRESS IS NULL);
878 END IF;
879 END IF;
880 END update_desktop_server;
881
882 /* delete_desktop_server
883 **
884 ** This API is used for Desktop Nodes only.
885 ** Similar to delete_server, server_id is NULLed out, the row is not physically
886 ** deleted.
887 */
888 PROCEDURE delete_desktop_server(p_node_name IN VARCHAR2)
889 IS
890 l_node_name fnd_nodes.node_name%TYPE := UPPER(p_node_name);
891 BEGIN
892 -- If desktop node exists, proceed
893 -- If not a desktop node, do nothing. A desktop node maybe using the
894 -- server_id of a server node. A desktop node cannot update a server node's
895 -- data.
896 IF desktop_node_exists(l_node_name) THEN
897 UPDATE fnd_nodes
898 SET server_id = NULL
899 WHERE node_name = l_node_name
900 AND support_cp = 'N'
901 AND support_forms = 'N'
902 AND support_web = 'N'
903 AND support_admin = 'N'
904 AND support_db = 'N'
905 AND platform_code = '100000'
906 AND ((SERVER_ADDRESS IS NOT NULL
907 AND SERVER_ADDRESS <> '*')
908 OR SERVER_ADDRESS IS NULL);
909 END IF;
910 END delete_desktop_server;
911
912 END fnd_app_server_pkg;
[Home] [Help]