[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERTED boolean := FALSE; /*if at least a profile is stored */
** changes within that profile level to refresh it (delete it).
*/
if (FND_CACHE_VERSIONS_PKG.check_version(USER_CACHE,USER_CACHE_VERSION)
= FALSE) then
if (USER_CACHE_VERSION = -1) then
FND_CACHE_VERSIONS_PKG.add_cache_name(USER_CACHE);
USER_NAME_TAB.DELETE();
USER_VAL_TAB.DELETE();
RESP_NAME_TAB.DELETE();
RESP_VAL_TAB.DELETE();
APPL_NAME_TAB.DELETE();
APPL_VAL_TAB.DELETE();
ORG_NAME_TAB.DELETE();
ORG_VAL_TAB.DELETE();
SERVER_NAME_TAB.DELETE();
SERVER_VAL_TAB.DELETE();
SERVRESP_NAME_TAB.DELETE();
SERVRESP_VAL_TAB.DELETE();
SITE_NAME_TAB.DELETE();
SITE_VAL_TAB.DELETE();
** PUT - Set or Insert a profile option value in cache
*/
procedure PUT(
NAME in varchar2, -- should be passed UPPER value
VAL in varchar2,
nameTable in out NOCOPY NAME_TAB_TYPE,
valueTable in out NOCOPY VAL_TAB_TYPE,
PROFILE_HASH_VALUE in binary_integer) is
TABLE_INDEX binary_integer;
INSERTED := TRUE; /* At least, a profile is stored */
** PUT - Set or Insert a profile option value into the generic PUT cache
*/
procedure PUT(
NAME in varchar2,
VAL in varchar2)
is
/* Bug 5603664: APPSPERF:FND:OPTIMIZE FND_PROFILE.VALUE
** UPPER call is done early and value is passed on, which minimizes
** number of UPPER calls
*/
NAME_UPPER varchar2(80) := UPPER(NAME);
select profile_option_value
from fnd_profile_option_values
where profile_option_id = pid
and application_id = aid
and level_id = lid
and level_value = lval
and profile_option_value is not null;
select profile_option_value
from fnd_profile_option_values
where profile_option_id = pid
and application_id = aid
and level_id = 10003
and level_value = lval
and level_value_application_id = laid
and profile_option_value is not null;
select profile_option_value
from fnd_profile_option_values
where profile_option_id = pid
and application_id = aid
and level_id = 10007
and level_value = lval
and level_value_application_id = laid
and level_value2 = lval2
and profile_option_value is not null;
select profile_option_id,
application_id,
site_enabled_flag ,
app_enabled_flag ,
resp_enabled_flag ,
user_enabled_flag,
org_enabled_flag ,
server_enabled_flag,
SERVERRESP_ENABLED_FLAG,
hierarchy_type,
user_changeable_flag -- Bug 4257739
from fnd_profile_options
where profile_option_name = name_z -- Bug 5599946: Removed UPPER call
and start_date_active <= sysdate
and nvl(end_date_active, sysdate) >= sysdate;
select profile_option_id,
application_id,
site_enabled_flag ,
app_enabled_flag ,
resp_enabled_flag ,
user_enabled_flag,
org_enabled_flag ,
server_enabled_flag,
serverresp_enabled_flag,
hierarchy_type,
user_changeable_flag -- Bug 4257739
from fnd_profile_options
where profile_option_name = name_z
and start_date_active <= sysdate
and nvl(end_date_active, sysdate) >= sysdate;
select profile_option_value
from fnd_profile_option_values
where profile_option_id = pid
and application_id = aid
and level_id = lid
and level_value = lval
and profile_option_value is not null;
select profile_option_value
from fnd_profile_option_values
where profile_option_id = pid
and application_id = aid
and level_id = 10003
and level_value = lval
and level_value_application_id = laid
and profile_option_value is not null;
select profile_option_value
from fnd_profile_option_values
where profile_option_id = pid
and application_id = aid
and level_id = 10007
and level_value = lval
and level_value_application_id = laid
and level_value2 = lval2
and profile_option_value is not null;
p_event_name=>'oracle.apps.fnd.profile.value.update',
p_event_key=>event_key);
x_last_updated_by NUMBER;
x_last_update_login NUMBER;
x_last_update_date DATE;
select application_id, profile_option_id
from fnd_profile_options po
where po.profile_option_name = X_NAME_UPPER
/* Bug 5591340: FND_PROFILE.SAVE SHOULD NOT UPDATE VALUES FOR END_DATED
** PROFILE OPTIONS
** Added these date-sensitive conditions to prevent processing of
** end-dated profile options
*/
and po.start_date_active <= sysdate
and nvl(po.end_date_active, sysdate) >= sysdate;
FND_PROFILE_OPTION_VALUES_PKG.DELETE_ROW(x_application_id,
x_profile_option_id, x_level_id, x_level_value_actual,
X_LEVEL_VALUE_APP_ID, x_level_value2_actual);
FND_PROFILE_OPTION_VALUES_PKG.DELETE_ROW(x_application_id,
x_profile_option_id, x_level_id, x_level_value_actual,
X_LEVEL_VALUE_APP_ID);
x_last_update_date := SYSDATE;
x_last_updated_by := fnd_profile.value('USER_ID');
if x_last_updated_by is NULL then
x_last_updated_by := -1;
x_last_update_login := fnd_profile.value('LOGIN_ID');
if x_last_update_login is NULL then
x_last_update_login := -1;
FND_PROFILE_OPTION_VALUES_PKG.UPDATE_ROW(x_application_id,
x_profile_option_id, x_level_id, x_level_value_actual,
X_LEVEL_VALUE_APP_ID, x_level_value2_actual, X_VALUE,
x_last_update_date, x_last_updated_by, x_last_update_login);
FND_PROFILE_OPTION_VALUES_PKG.UPDATE_ROW(x_application_id,
x_profile_option_id, x_level_id, x_level_value_actual,
X_LEVEL_VALUE_APP_ID, X_VALUE, x_last_update_date,
x_last_updated_by, x_last_update_login);
** This block of code was separated from the update/insert code block of
** SAVE() so that deleted values are properly reflected in level caches
** just like non-NULL values are cached when saved.
** Previously, only non-NULL values were being cached in level caches
** when a new non-NULL value was saved, such that when a value is
** deleted, the get apis would still return the previous cached value.
*/
if (x_level_id = 10007) then
invalidate_cache(x_level_name,x_level_value,x_level_value_app_id,
X_NAME_UPPER,x_level_value2);
/* 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 ((X_NAME_UPPER = 'ICX_LANGUAGE')
or (X_NAME_UPPER = 'ICX_TERRITORY')) then
if ((X_LEVEL_NAME = 'USER') and (X_LEVEL_VALUE is not null)) then
select user_name
into x_user_name
from fnd_user
where user_id = to_number(X_LEVEL_VALUE);
** Check if fnd_cache_versions was updated. This refreshes level caches
** in order for value_specific to return accurate values should a new
** profile value be saved in another session. This will introduce a
** performance degradation which has been deemed necessary for
** value_specific return values.
*/
CHECK_CACHE_VERSIONS();
** The main usage for this routine would be in a SELECT statement where
** VALUE() is not allowed since it writes package state.
**
** This routine does the same thing as VALUE(); it returns a profile value
ORG_NAME_TAB.DELETE();
ORG_VAL_TAB.DELETE();
NAME_TAB.DELETE();
VAL_TAB.DELETE();
USER_NAME_TAB.DELETE();
USER_VAL_TAB.DELETE();
RESP_NAME_TAB.DELETE();
RESP_VAL_TAB.DELETE();
SERVRESP_NAME_TAB.DELETE();
SERVRESP_VAL_TAB.DELETE();
APPL_NAME_TAB.DELETE();
APPL_VAL_TAB.DELETE();
RESP_NAME_TAB.DELETE();
RESP_VAL_TAB.DELETE();
SERVRESP_NAME_TAB.DELETE();
SERVRESP_VAL_TAB.DELETE();
SERVER_NAME_TAB.DELETE();
SERVER_VAL_TAB.DELETE();
SERVRESP_NAME_TAB.DELETE();
SERVRESP_VAL_TAB.DELETE();
SELECT USER_NAME
INTO NAME
FROM FND_USER
WHERE USER_ID = user_id_z;
SELECT RESPONSIBILITY_NAME
INTO NAME
FROM FND_RESPONSIBILITY_VL
WHERE RESPONSIBILITY_ID = responsibility_id_z
AND APPLICATION_ID = application_id_z;
select node_name
into NAME
from fnd_nodes
where node_id = PROFILES_SERVER_ID;
if (not INSERTED) then
return null;
* oracle.apps.fnd.profile.value.update event. This function calls
* FND_CACHE_VERSION_PKG.bump_version to increase the version of the
* appropriate profile level cache.
*/
function bumpCacheVersion_RF (
p_subscription_guid in raw,
p_event in out NOCOPY WF_EVENT_T)
return varchar2 is
l_event_key varchar2(255);
** 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; the caller must
** 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);
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.
l_deleted boolean;
l_deleted := SAVE(X_NAME,
NULL,
X_LEVEL_NAME,
X_LEVEL_VALUE,
X_LEVEL_VALUE_APP_ID,
X_LEVEL_VALUE2);
return l_deleted;