[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* change INSERTED to number to account for PUT deletes - bug 12975860 */
inserted NUMBER := 0; /* count of PUT profiles stored */
** Constant string used to indicate a delete request in PUT cache.
** -- Bug 12875860 - PER Rewrite
*/
FND_DELETE_VALUE VARCHAR2(30) := '**FND_DELETE_VALUE**';
SELECT ROWID
INTO profileRowId
FROM fnd_profile_options
WHERE profile_option_name = profileName
AND start_date_active <= SYSDATE
AND nvl(end_date_active, SYSDATE) >= SYSDATE;
SELECT profile_option_name
INTO profileName
FROM fnd_profile_options
WHERE profile_option_id = profileOptionId
AND application_id = applicationId;
SELECT profile_option_id
INTO profileId
FROM fnd_profile_options
WHERE ROWID = profileOptionRowId;
SELECT application_id
INTO profileApplId
FROM fnd_profile_options
WHERE ROWID = profileOptionRowId;
SELECT decode(levelName,
'USER',
user_enabled_flag,
'RESP',
resp_enabled_flag,
'APPL',
app_enabled_flag,
'SERVER',
server_enabled_flag,
'ORG',
org_enabled_flag,
'SERVRESP',
serverresp_enabled_flag,
'SITE',
site_enabled_flag)
INTO profileLevelEnabled
FROM fnd_profile_options
WHERE ROWID = profileOptionRowId;
SELECT hierarchy_type
INTO profileHierarchyType
FROM fnd_profile_options
WHERE ROWID = profileOptionRowId;
SELECT user_changeable_flag
INTO profileUserChangeable
FROM fnd_profile_options
WHERE ROWID = profileOptionRowId;
SELECT profile_option_value
INTO siteLevelValue
FROM fnd_profile_values_site_v
WHERE profile_option_id = profileOptionId
AND application_id = applicationId
AND profile_option_value IS NOT NULL;
SELECT profile_option_value
INTO applLevelValue
FROM fnd_profile_values_appl_v
WHERE profile_option_id = profileOptionId
AND application_id = applicationId
AND level_value = applId
AND profile_option_value IS NOT NULL;
SELECT profile_option_value
INTO respLevelValue
FROM fnd_profile_values_resp_v
WHERE profile_option_id = profileOptionId
AND application_id = applicationId
AND level_value = respId
AND level_value_application_id = applId
AND profile_option_value IS NOT NULL;
SELECT profile_option_value
INTO userLevelValue
FROM fnd_profile_values_user_v
WHERE profile_option_id = profileOptionId
AND application_id = applicationId
AND level_value = userId
AND profile_option_value IS NOT NULL;
SELECT profile_option_value
INTO serverLevelValue
FROM fnd_profile_values_server_v
WHERE profile_option_id = profileOptionId
AND application_id = applicationId
AND level_value = serverId
AND profile_option_value IS NOT NULL;
SELECT profile_option_value
INTO orgLevelValue
FROM fnd_profile_values_org_v
WHERE profile_option_id = profileOptionId
AND application_id = applicationId
AND level_value = orgId
AND profile_option_value IS NOT NULL;
SELECT profile_option_value
INTO servrespLevelValue
FROM fnd_profile_values_servresp_v
WHERE profile_option_id = profileOptionId
AND application_id = applicationId
AND level_value = respId
AND level_value_application_id = applId
AND level_value2 = serverId
AND profile_option_value IS NOT NULL;
val_tab(tab_index) = fnd_delete_value) THEN
-- The entry doesn't exist
RETURN table_size + 1;
** Set or insert a profile option value into the Public Put cache. This
** does not place the profile option value in the database and is only
** available during the session.
*/
PROCEDURE put
(
profileName IN VARCHAR2,
profileValue IN VARCHAR2,
profileNameTable IN OUT NOCOPY name_tab_type,
profileValueTable IN OUT NOCOPY val_tab_type,
profileHashValue IN BINARY_INTEGER
) IS
tableIndex BINARY_INTEGER;
** the 'oracle.apps.fnd.profile.value.update' event to invalidate their
** cache and reload from the database.
*/
PROCEDURE invalidate_cache
(
profileName IN VARCHAR2,
levelId IN NUMBER,
levelValue IN VARCHAR2,
levelValueAppId IN VARCHAR2,
levelValue2 IN VARCHAR2 DEFAULT NULL
) IS
level_value NUMBER;
fnd_wf_engine.default_event_raise(p_event_name => 'oracle.apps.fnd.profile.value.update',
p_event_key => event_key);
put('ORG_ID', FND_DELETE_VALUE);
name_tab.delete();
val_tab.delete();
inserted := 0; -- reset PUT count
SELECT user_name
INTO tempName
FROM fnd_user
WHERE user_id = user_id_z;
SELECT responsibility_name
INTO tempName
FROM fnd_responsibility_vl
WHERE responsibility_id = responsibility_id_z
AND application_id = application_id_z;
SELECT node_name
INTO tempName
FROM fnd_nodes
WHERE node_id = fnd_global.server_id;
** Set or insert a profile option value into the Public Put cache. This
** does not place the profile option value in the database and is only
** available during the session.
*/
PROCEDURE put
(
name IN VARCHAR2,
val IN VARCHAR2
) IS
table_index BINARY_INTEGER; -- bug 16327915 manage PUT count
IF ((val <> FND_DELETE_VALUE) AND
(val IS NOT NULL)) THEN
-- if existing profile value is marked deleted
IF (val_tab(table_index) = FND_DELETE_VALUE) THEN
-- we will be reviving the current deleted value
-- increment the PUT counter
inserted := inserted + 1;
ELSE -- we are marking this profile for delete
-- if existing profile value is NOT marked deleted
IF (val_tab(table_index) <> FND_DELETE_VALUE) THEN
-- we will be removing the current value
-- decrement PUT counter
IF (inserted > 0) THEN
inserted := inserted - 1;
IF ((val <> FND_DELETE_VALUE) AND
(val IS NOT NULL)) THEN
-- we are inserting a new value
inserted := inserted + 1;
corelog(profileNameUpper, val, 'Exit Public PUT Count=' || inserted);
fnd_profile_option_values_pkg.delete_row(applicationId,
profileOptionId,
levelId,
levelValueActual,
x_level_value_app_id,
levelValue2Actual);
fnd_profile_option_values_pkg.delete_row(applicationId,
profileOptionId,
levelId,
levelValueActual,
x_level_value_app_id);
fnd_profile_option_values_pkg.update_row(applicationId,
profileOptionId,
levelId,
levelValueActual,
x_level_value_app_id,
levelValue2Actual,
x_value,
SYSDATE,
nvl(fnd_global.user_id,
DEFAULT_CONTEXT),
nvl(fnd_global.login_id,
DEFAULT_CONTEXT));
fnd_profile_option_values_pkg.update_row(applicationId,
profileOptionId,
levelId,
levelValueActual,
x_level_value_app_id,
NULL,
x_value,
SYSDATE,
nvl(fnd_global.user_id,
DEFAULT_CONTEXT),
nvl(fnd_global.login_id,
DEFAULT_CONTEXT));
/* Bug 3203225: PREFERENCES NOT UPDATED ON FLY IN WF_ROLES VIEW
** needs to call FND_USER_PKG.User_Synch() whenever an update to
** ICX_LANGUAGE or ICX_TERRITORY is updated at the user level.
*/
IF ((profNameUpper = 'ICX_LANGUAGE') OR
(profNameUpper = 'ICX_TERRITORY')) THEN
IF ((levelId = USER_LEVEL_ID) AND (levelValueActual IS NOT NULL)) THEN
SELECT user_name
INTO userName
FROM fnd_user
WHERE user_id = levelValueActual;
** delete
** deletes the value of a profile option permanently from the database, at
** any level. This routine serves as a wrapper to the SAVE routine which
** means that this routine can be used at runtime or during patching. Like
** the SAVE routine, this routine will not actually commit the changes;
** FND_PROFILE.DELETE('P_NAME', 'SITE');
** FND_PROFILE.DELETE('P_NAME', 'APPL', 321532);
** FND_PROFILE.DELETE('P_NAME', 'RESP', 321532, 345234);
** FND_PROFILE.DELETE('P_NAME', 'USER', 123321);
** FND_PROFILE.DELETE('P_NAME', 'SERVER', 25);
** FND_PROFILE.DELETE('P_NAME', 'ORG', 204);
** FND_PROFILE.DELETE('P_NAME', 'SERVRESP', 321532, 345234, 25);
** FND_PROFILE.DELETE('P_NAME', 'SERVRESP', 321532, 345234, -1);
** FND_PROFILE.DELETE('P_NAME', 'SERVRESP', -1, -1, 25);
** profileName - Profile name whose value is to be deleted
** levelName - Level deleting at:
** 'SITE','APPL','RESP','USER', etc.
** levelValue - context value used for deleting profile option
** value, e.g. user_id for 'USER' level. This does
** not apply to 'SITE' level.
** levelValueAppId - applies to 'RESP' and 'SERVRESP' levels, i.e.
** the Resp Application_Id.
** levelValue2 - 2nd context value used for deleting profile
** option value. This applies to the 'SERVRESP'
** hierarchy.
**
** RETURNS: TRUE if successful, FALSE if failure.
*/
FUNCTION DELETE(x_name IN VARCHAR2,
/* Profile name you are setting */
x_level_name IN VARCHAR2,
/* Level that you're setting at: 'SITE','APPL','RESP','USER',
etc. */
x_level_value IN VARCHAR2 DEFAULT NULL,
/* Level value that you are setting at, e.g. user id for
'USER' level. X_LEVEL_VALUE is not used at site level. */
x_level_value_app_id IN VARCHAR2 DEFAULT NULL,
/* Used for 'RESP' and 'SERVRESP' level; Resp Application_Id. */
deleted BOOLEAN;
deleted := SAVE(x_name,
NULL,
x_level_name,
x_level_value,
x_level_value_app_id,
x_level_value2);
RETURN deleted;
END DELETE;
** oracle.apps.fnd.profile.value.update event.
** Since level hash table caches are no longer used due to the PL/SQL
** cross-session function result caching mechanism, this is really no
** longer being used. It will return SUCCESS for backward compatibility.
*/
FUNCTION bumpcacheversion_rf
(
p_subscription_guid IN RAW,
p_event IN OUT NOCOPY wf_event_t
) RETURN VARCHAR2 IS
BEGIN
RETURN 'SUCCESS';
IF (inserted = 0) THEN
RETURN NULL;