The following lines contain the word 'select', 'insert', 'update' or 'delete':
sqlbuf := 'select substr(RawToHex(sys_op_guid()),0,32) from dual';
** insert_server and update_server.
**
** Testcase to test API:
set serverout on
begin
dbms_output.put_line('Solaris = '||
FND_APP_SERVER_PKG.get_platform_code('Solaris'));
select lookup_code
into l_platform_code
from fnd_lookup_values
where lookup_type = 'PLATFORM'
and tag = p_platform
and language = userenv('LANG')
and view_application_id = 0
and security_group_id = 0;
/* insert_server
**
** Inserts information for a new Application Server. The function
** create_server_id must be called to generate a valid id prior to
** calling this api. This is called by AdminAppServer.changeServerInDB().
**
** FND_APP_SERVER_PKG.INSERT_SERVER is a wrapper to
** FND_CONCURRENT.REGISTER_NODE.
*/
PROCEDURE insert_server (
p_server_id IN VARCHAR2,
p_address IN VARCHAR2,
p_node_name IN VARCHAR2,
p_description IN VARCHAR2 DEFAULT NULL,
p_webhost IN VARCHAR2 DEFAULT NULL,
p_platform_code IN VARCHAR2 DEFAULT NULL,
p_support_cp IN VARCHAR2 DEFAULT NULL,
p_support_forms IN VARCHAR2 DEFAULT NULL,
p_support_web IN VARCHAR2 DEFAULT NULL,
p_support_admin IN VARCHAR2 DEFAULT NULL,
p_support_db IN VARCHAR2 DEFAULT NULL)
IS
kount number := 0;
select count(*) into kount
from fnd_nodes
where UPPER(node_name) = l_node_name;
update_desktop_server(l_node_name, p_server_id,p_address,
p_description);
select count(*) into kount
from fnd_nodes
where server_address = p_address;
select node_id, node_name
into curr_node_id, curr_node_name
from fnd_nodes
where server_address = p_address;
UPDATE FND_NODES
SET PLATFORM_CODE = '87',
SERVER_ADDRESS = NULL,
SERVER_ID = NULL
WHERE NODE_ID = curr_node_id;
select count(*) into kount
from fnd_nodes
where UPPER(node_name) = l_node_name;
update_server(p_server_id, p_address, p_description,
p_webhost);
/* Node already exists. It should not be inserted again, just
updated.*/
update_server(p_server_id, p_address, p_description, p_webhost,
p_platform_code);
ELSE /* Node does not exist, the server will be inserted. */
-- Check if p_node_name is a fully qualified hostname with domain.
-- FND_NODES.NODE_NAME should only have hostname if platform is not
-- UNIX Alpha.
if (l_platform_code <> '87' and INSTR(p_node_name, '.') <> 0) then
-- The hostname should be the beginning of the string up until
-- the first period. FND_NODES.NODE_NAME is stored in
-- UPPERCASE.
l_node_name := UPPER(SUBSTR(p_node_name, 0,
INSTR(p_node_name, '.') - 1));
select count(*) into kount
from fnd_nodes
where UPPER(node_name) = l_node_name;
update_server(p_server_id, p_address, p_description,
p_webhost);
END insert_server;
/* delete_server
**
** This procedure used to remove an Application Server row from the database.
** Due to the migration of FND_APPLICATION_SERVERS to FND_NODES,
** fnd_nodes.server_id is nulled out instead in order to preserve the
** node_name and avoid dangling references to the node_name. This is called by
** AdminAppServer.delSvrFromDB().
*/
PROCEDURE delete_server (p_address IN VARCHAR2)
IS
BEGIN
UPDATE fnd_nodes
SET server_id = NULL
WHERE server_address = p_address;
/* update_server
**
** This procedure should only be used for updating Application Server Nodes.
** The server_id, description, host and domain are updated if they are not
** NULL. If a new server_id is required, the create_server_id function should
** be called prior to this. This is called by
** AdminAppServer.changeServerInDB().
*/
PROCEDURE update_server (
p_server_id IN VARCHAR2 DEFAULT NULL,
p_address IN VARCHAR2,
p_description IN VARCHAR2 DEFAULT NULL,
p_webhost IN VARCHAR2 DEFAULT NULL,
p_platform IN VARCHAR2 DEFAULT NULL)
IS
l_node_name VARCHAR2(30);
select NODE_NAME, SUPPORT_CP, SUPPORT_FORMS, SUPPORT_WEB, SUPPORT_ADMIN,
SUPPORT_DB, PLATFORM_CODE
into l_node_name, l_support_cp, l_support_forms, l_support_web,
l_support_admin,l_support_db, l_platform
from fnd_nodes
where server_address = p_address;
UPDATE fnd_nodes
SET node_name = UPPER(l_node_name)
WHERE server_address = p_address;
UPDATE fnd_nodes
SET server_id = p_server_id
WHERE server_address = p_address;
UPDATE fnd_nodes
SET description = p_description
WHERE server_address = p_address;
UPDATE fnd_nodes
SET webhost = p_webhost
WHERE server_address = p_address;
UPDATE fnd_nodes
SET PLATFORM_CODE = l_platform2
WHERE server_address = p_address;
SELECT NODE_NAME, STATUS, NODE_MODE
FROM FND_NODES
WHERE NOT (SUPPORT_CP = 'N' AND
SUPPORT_WEB = 'N' AND SUPPORT_FORMS = 'N' AND
SUPPORT_ADMIN = 'N' AND SUPPORT_CP is NOT NULL AND
SUPPORT_WEB is NOT NULL AND
SUPPORT_ADMIN is NOT NULL AND SUPPORT_FORMS is NOT NULL)
Since the AUTHENTICATION row is inserted without these SUPPORT_*
columns, the system alert gets logged. This IF block has been added
to check whether those columns have a value for the AUTHENTICATION row.
It the column(s) have a null value, 'N' is explicitly set for the
applicable SUPPORT_* column.
*/
IF (p_address = '*') THEN
IF (l_support_cp is NULL) THEN
UPDATE fnd_nodes
SET SUPPORT_CP = 'N'
WHERE server_address = p_address;
UPDATE fnd_nodes
SET SUPPORT_FORMS = 'N'
WHERE server_address = p_address;
UPDATE fnd_nodes
SET SUPPORT_WEB = 'N'
WHERE server_address = p_address;
UPDATE fnd_nodes
SET SUPPORT_ADMIN = 'N'
WHERE server_address = p_address;
UPDATE fnd_nodes
SET SUPPORT_DB = 'N'
WHERE server_address = p_address;
UPDATE fnd_nodes
SET PLATFORM_CODE = l_platform2
WHERE server_address = p_address;
END update_server;
** will insert it. The row with server_address='*' indicates the authentication
** value. If the row already exists, the procedure updates the value to what
** has been passed. The valid AUTHENTICATION values are:
** 'ON'
** 'OFF'
** 'SECURE'
** This is called by AdminAppServer.setAuthentication().
**
** Bug 3736714: the p_platform argument was added so that the authentication
** row can be added with the correct platform. The platform is determined in
** AdminAppServer.java.
*/
PROCEDURE authenticate (
p_value IN VARCHAR2,
p_platformcode IN VARCHAR2 DEFAULT NULL
)
IS
BEGIN
/* Bug 3736714 - The AUTHENTICATION row should be seeded with the correct
** platform even if it isn't technically a node.
*/
update_server(
p_server_id => p_value,
p_address => '*',
p_description => 'Authentication Value',
p_platform => p_platformcode);
insert_server(p_value,'*','AUTHENTICATION','Authentication value');
SELECT NODE_NAME, STATUS, NODE_MODE
FROM FND_NODES
WHERE NOT (SUPPORT_CP = 'N' AND
SUPPORT_WEB = 'N' AND SUPPORT_FORMS = 'N' AND
SUPPORT_ADMIN = 'N' AND SUPPORT_CP is NOT NULL AND
SUPPORT_WEB is NOT NULL AND
SUPPORT_ADMIN is NOT NULL AND SUPPORT_FORMS is NOT NULL)
Since the AUTHENTICATION row is inserted without these SUPPORT_*
columns, the system alert gets logged. The call to insert_server has
been modified to explicitly set the SUPPORT_* with a value of 'N'.
*/
insert_server (
p_server_id => p_value,
p_address => '*',
p_node_name => 'AUTHENTICATION',
p_description => 'Authentication value',
p_platform_code => p_platformcode,
p_support_cp => 'N',
p_support_forms => 'N',
p_support_web => 'N',
p_support_admin => 'N',
p_support_db => 'N');
/* insert_desktop_server
**
** This API is used for Desktop Nodes only.
** It calls insert_server and sets all the SUPPORT_* collumns to 'N' and the
** PLATFORM_CODE to 'Others'. It also places 'Desktop Node' as the description
** if NULL was passed.
*/
PROCEDURE insert_desktop_server(
p_node_name IN VARCHAR2,
p_server_id IN VARCHAR2,
p_address IN VARCHAR2 DEFAULT NULL,
p_description IN VARCHAR2 DEFAULT NULL)
IS
BEGIN
-- Check that the server_address passed is not for the AUTHENTICATION row.
-- The AUTHENTICATION row also has SUPPORT_* columns = 'N', so it needs to
-- be distinguished from Desktop Nodes.
-- a NULL server_address can be passed, but not server_address = '*'
IF ((p_address is not NULL) and (p_address <> '*')) or (p_address is NULL) THEN
insert_server (
p_server_id => p_server_id,
p_address => p_address,
p_node_name => p_node_name,
p_description => nvl(p_description,'Desktop Node'),
p_platform_code => 'Others', -- Use 'Others' as the platform_code
p_support_cp => 'N',
p_support_forms => 'N',
p_support_web => 'N',
p_support_admin => 'N',
p_support_db => 'N');
END insert_desktop_server;
/* update_desktop_server
**
** This API is used for Desktop Nodes only.
** Update the FND_NODES row associated with p_node_name with the specified
** values for server_id, address, and description. If NULLs are passed, do not
** update. update_server cannot be used here because it uses p_address as the
** where condition.
*/
PROCEDURE update_desktop_server(
p_node_name IN VARCHAR2,
p_server_id IN VARCHAR2 DEFAULT NULL,
p_address IN VARCHAR2 DEFAULT NULL,
p_description IN VARCHAR2 DEFAULT NULL)
IS
kount number := 0;
select count(*)
into kount
from fnd_nodes
where node_name = p_node_name
and SUPPORT_CP = 'N'
and SUPPORT_FORMS = 'N'
and SUPPORT_WEB = 'N'
and SUPPORT_ADMIN = 'N'
and SUPPORT_DB = 'N'
and PLATFORM_CODE = '100000';
UPDATE fnd_nodes
SET server_id = p_server_id
WHERE node_name = UPPER(p_node_name)
and support_cp = 'N'
and support_forms = 'N'
and support_web = 'N'
and support_admin = 'N'
and support_db = 'N'
and platform_code = '100000';
UPDATE fnd_nodes
SET server_address = p_address
WHERE node_name = UPPER(p_node_name)
and support_cp = 'N'
and support_forms = 'N'
and support_web = 'N'
and support_admin = 'N'
and support_db = 'N'
and platform_code = '100000';
UPDATE fnd_nodes
SET description = p_description
WHERE node_name = UPPER(p_node_name)
and support_cp = 'N'
and support_forms = 'N'
and support_web = 'N'
and support_admin = 'N'
and support_db = 'N'
and platform_code = '100000';
/* delete_desktop_server
**
** This API is used for Desktop Nodes only.
** Similar to delete_server, server_id is NULLed out, the row is not physically
** deleted.
*/
PROCEDURE delete_desktop_server (p_node_name IN VARCHAR2)
IS
BEGIN
UPDATE fnd_nodes
SET server_id = NULL
WHERE node_name = UPPER(p_node_name)
and support_cp = 'N'
and support_forms = 'N'
and support_web = 'N'
and support_admin = 'N'
and support_db = 'N'
and platform_code = '100000';