[Home] [Help]
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;
SELECT COUNT(*)
INTO kount
FROM fnd_nodes
WHERE upper(node_name) = l_node_name;
SELECT COUNT(*)
INTO kount
FROM fnd_nodes
WHERE upper(node_name) = l_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'
AND ((SERVER_ADDRESS IS NOT NULL
AND SERVER_ADDRESS <> '*')
OR SERVER_ADDRESS IS NULL);
SELECT COUNT(*)
INTO kount
FROM fnd_nodes
WHERE server_address = p_address;
** deleted" using delete_server or delete_desktop_server.
*/
FUNCTION get_server_id
(
p_node_name IN VARCHAR2 DEFAULT NULL,
p_address IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2
IS
l_server_id fnd_nodes.server_id%TYPE := NULL;
SELECT server_id
INTO l_server_id
FROM fnd_nodes
WHERE upper(node_name) = l_node_name;
SELECT server_id
INTO l_server_id
FROM fnd_nodes
WHERE server_address = p_address
AND server_id IS NOT NULL
AND rownum < 2;
SELECT server_address
INTO l_server_address
FROM fnd_nodes
WHERE upper(node_name) = l_node_name;
SELECT node_name
INTO l_node_name
FROM fnd_nodes
WHERE server_address = p_address
AND node_name IS NOT NULL
AND rownum < 2;
/* 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 OUT NOCOPY 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;
insert_desktop_server(l_node_name, p_server_id, p_address, p_description);
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().and ((SERVER_ADDRESS
** is not NULL and SERVER_ADDRESS <> '*') or SERVER_ADDRESS is null);
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.
** A server_id is passed into this API from the caller. This API does not
** check whether the server_id exists because the caller would have already
** checked whether it exists or not.
*/
PROCEDURE insert_desktop_server
(
p_node_name IN VARCHAR2,
p_server_id IN OUT NOCOPY VARCHAR2,
p_address IN VARCHAR2 DEFAULT NULL,
p_description IN VARCHAR2 DEFAULT NULL)
IS
l_node_name fnd_nodes.node_name%TYPE := UPPER(p_node_name);
update_desktop_server(l_node_name, p_server_id, p_address, p_description);
p_server_id => NVL(insert_desktop_server.p_server_id,
FND_APP_SERVER_PKG.CREATE_SERVER_ID),
p_address => insert_desktop_server.p_address,
p_description => NVL(insert_desktop_server.p_description,
'Desktop Node'),
db_tier => 'N');
UPDATE fnd_nodes
SET server_id = insert_desktop_server.p_server_id,
support_forms = 'N',
support_cp = 'N',
support_web = 'N',
support_admin = 'N',
support_db = 'N',
platform_code = '100000',
description = 'Desktop Node, converted from Server'
WHERE upper(node_name) = l_node_name
AND ((SERVER_ADDRESS IS NOT NULL
AND SERVER_ADDRESS <> '*')
OR SERVER_ADDRESS IS NULL);
UPDATE fnd_nodes
SET server_id = insert_desktop_server.p_server_id,
support_forms = 'N',
support_cp = 'N',
support_web = 'N',
support_admin = 'N',
support_db = 'N',
platform_code = '100000',
description = 'Desktop Node, converted from Server'
WHERE upper(node_name) = l_node_name_from_address
AND ((SERVER_ADDRESS IS NOT NULL
AND SERVER_ADDRESS <> '*')
OR SERVER_ADDRESS IS NULL);
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;
UPDATE fnd_nodes
SET server_id = p_server_id
WHERE upper(node_name) = l_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'
AND ((SERVER_ADDRESS IS NOT NULL
AND SERVER_ADDRESS <> '*')
OR SERVER_ADDRESS IS NULL);
UPDATE fnd_nodes
SET server_address = p_address
WHERE upper(node_name) = l_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'
AND ((SERVER_ADDRESS IS NOT NULL
AND SERVER_ADDRESS <> '*')
OR SERVER_ADDRESS IS NULL);
UPDATE fnd_nodes
SET description = p_description
WHERE upper(node_name) = l_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'
AND ((SERVER_ADDRESS IS NOT NULL
AND SERVER_ADDRESS <> '*')
OR SERVER_ADDRESS IS NULL);
END update_desktop_server;
/* 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
l_node_name fnd_nodes.node_name%TYPE := UPPER(p_node_name);
UPDATE fnd_nodes
SET server_id = NULL
WHERE node_name = l_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'
AND ((SERVER_ADDRESS IS NOT NULL
AND SERVER_ADDRESS <> '*')
OR SERVER_ADDRESS IS NULL);
END delete_desktop_server;