The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure INSERT_ROW (
X_ROWID in out nocopy VARCHAR2,
X_PROFILE_OPTION_ID in NUMBER,
X_CATEGORY_NAME in VARCHAR2,
X_PROFILE_OPTION_APPLICATION_I in NUMBER,
X_DISPLAY_SEQUENCE in NUMBER,
X_DISPLAY_TYPE in VARCHAR2,
X_CREATION_DATE in DATE,
X_CREATED_BY in NUMBER,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER,
X_APPLICATION_ID in NUMBER
) is
cursor C is select ROWID from FND_PROFILE_CAT_OPTIONS
where PROFILE_OPTION_ID = X_PROFILE_OPTION_ID
and CATEGORY_NAME = upper(X_CATEGORY_NAME)
and PROFILE_OPTION_APPLICATION_ID = X_PROFILE_OPTION_APPLICATION_I
and APPLICATION_ID = X_APPLICATION_ID
;
insert into FND_PROFILE_CAT_OPTIONS (
PROFILE_OPTION_APPLICATION_ID,
PROFILE_OPTION_ID,
CATEGORY_NAME,
APPLICATION_ID,
DISPLAY_SEQUENCE,
DISPLAY_TYPE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) values (
X_PROFILE_OPTION_APPLICATION_I,
X_PROFILE_OPTION_ID,
upper(X_CATEGORY_NAME),
X_APPLICATION_ID,
X_DISPLAY_SEQUENCE,
X_DISPLAY_TYPE,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN
);
insert into FND_PROFILE_CAT_OPTIONS_TL (
PROFILE_OPTION_APPLICATION_ID,
PROFILE_OPTION_ID,
CATEGORY_NAME,
APPLICATION_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
DESCRIPTION_OVERRIDE,
LANGUAGE,
SOURCE_LANG
) select
X_PROFILE_OPTION_APPLICATION_I,
X_PROFILE_OPTION_ID,
X_CATEGORY_NAME,
X_APPLICATION_ID,
X_CREATED_BY,
X_CREATION_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATE_LOGIN,
X_DESCRIPTION_OVERRIDE,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from FND_PROFILE_CAT_OPTIONS_TL T
where T.PROFILE_OPTION_ID = X_PROFILE_OPTION_ID
and T.CATEGORY_NAME = X_CATEGORY_NAME
and T.PROFILE_OPTION_APPLICATION_ID = X_PROFILE_OPTION_APPLICATION_I
and T.APPLICATION_ID = X_APPLICATION_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
end INSERT_ROW;
cursor c is select
DISPLAY_SEQUENCE,
DISPLAY_TYPE
from FND_PROFILE_CAT_OPTIONS
where PROFILE_OPTION_ID = X_PROFILE_OPTION_ID
and CATEGORY_NAME = upper(X_CATEGORY_NAME)
and PROFILE_OPTION_APPLICATION_ID = X_PROFILE_OPTION_APPLICATION_I
and APPLICATION_ID = X_APPLICATION_ID
for update of PROFILE_OPTION_ID nowait;
cursor c1 is select
DESCRIPTION_OVERRIDE,
decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
from FND_PROFILE_CAT_OPTIONS_TL
where PROFILE_OPTION_ID = X_PROFILE_OPTION_ID
and CATEGORY_NAME = X_CATEGORY_NAME
and PROFILE_OPTION_APPLICATION_ID = X_PROFILE_OPTION_APPLICATION_I
and APPLICATION_ID = X_APPLICATION_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
for update of PROFILE_OPTION_ID nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
procedure UPDATE_ROW (
X_PROFILE_OPTION_ID in NUMBER,
X_CATEGORY_NAME in VARCHAR2,
X_PROFILE_OPTION_APPLICATION_I in NUMBER,
X_DISPLAY_SEQUENCE in NUMBER,
X_DISPLAY_TYPE in VARCHAR2,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER,
X_APPLICATION_ID in NUMBER
) is
begin
update FND_PROFILE_CAT_OPTIONS set
DISPLAY_SEQUENCE = X_DISPLAY_SEQUENCE,
DISPLAY_TYPE = X_DISPLAY_TYPE,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
where PROFILE_OPTION_ID = X_PROFILE_OPTION_ID
and CATEGORY_NAME = upper(X_CATEGORY_NAME)
and PROFILE_OPTION_APPLICATION_ID = X_PROFILE_OPTION_APPLICATION_I
and APPLICATION_ID = X_APPLICATION_ID;
update FND_PROFILE_CAT_OPTIONS_TL set
DESCRIPTION_OVERRIDE = X_DESCRIPTION_OVERRIDE,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
SOURCE_LANG = userenv('LANG')
where PROFILE_OPTION_ID = X_PROFILE_OPTION_ID
and CATEGORY_NAME = X_CATEGORY_NAME
and PROFILE_OPTION_APPLICATION_ID = X_PROFILE_OPTION_APPLICATION_I
and APPLICATION_ID = X_APPLICATION_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
end UPDATE_ROW;
procedure DELETE_ROW (
X_PROFILE_OPTION_ID in NUMBER,
X_CATEGORY_NAME in VARCHAR2,
X_PROFILE_OPTION_APPLICATION_I in NUMBER,
X_APPLICATION_ID in NUMBER
) is
begin
/******************Commented. Since TL table is dropped
delete from FND_PROFILE_CAT_OPTIONS_TL
where PROFILE_OPTION_ID = X_PROFILE_OPTION_ID
and CATEGORY_NAME = X_CATEGORY_NAME
and PROFILE_OPTION_APPLICATION_ID = X_PROFILE_OPTION_APPLICATION_I
and APPLICATION_ID = X_APPLICATION_ID;
delete from FND_PROFILE_CAT_OPTIONS
where PROFILE_OPTION_ID = X_PROFILE_OPTION_ID
and CATEGORY_NAME = upper(X_CATEGORY_NAME)
and PROFILE_OPTION_APPLICATION_ID = X_PROFILE_OPTION_APPLICATION_I
and APPLICATION_ID = X_APPLICATION_ID;
end DELETE_ROW;
/** deleted all the code instead of commenting the ADD_LANGUAGE to remove
** the dependency between this change and FNDNLINS.sql which has call to
** Fnd_profile_cat_options_pkg.add_language. If the api is commented,
** then this change requires a change in FNDNLINS.sql.
**/
null;
X_LAST_UPDATE_DATE in VARCHAR2)
is
f_luby number; -- entity owner in file
f_ludate date; -- entity update date in file
db_ludate date; -- entity update date in db
select application_id into app_id
from fnd_application
where application_short_name = X_APPLICATION_SHORT_NAME;
select profile_option_id, application_id into prof_id, prof_app_id
from fnd_profile_options
where profile_option_name = X_PROFILE_OPTION_NAME;
f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
select LAST_UPDATED_BY, LAST_UPDATE_DATE
into db_luby, db_ludate
from FND_PROFILE_CAT_OPTIONS_TL
where PROFILE_OPTION_APPLICATION_ID = prof_app_id
and PROFILE_OPTION_ID = prof_id
and CATEGORY_NAME = X_CATEGORY_NAME
and APPLICATION_ID = app_id
and LANGUAGE = userenv('LANG');
update FND_PROFILE_CAT_OPTIONS_TL set
DESCRIPTION_OVERRIDE = X_DESCRIPTION_OVERRIDE,
LAST_UPDATE_DATE = f_ludate,
LAST_UPDATED_BY = f_luby,
LAST_UPDATE_LOGIN = f_luby,
SOURCE_LANG = userenv('LANG')
where PROFILE_OPTION_APPLICATION_ID = prof_app_id
and PROFILE_OPTION_ID = prof_id
and CATEGORY_NAME = X_CATEGORY_NAME
and APPLICATION_ID = app_id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
X_LAST_UPDATE_DATE in VARCHAR2,
X_APPLICATION_SHORT_NAME in VARCHAR2,
X_PROF_APPL_SHORT_NAME in VARCHAR2 default NULL)
is
row_id varchar2(64);
f_ludate date; -- entity update date in file
db_ludate date; -- entity update date in db
select application_id into app_id
from fnd_application
where application_short_name = X_APPLICATION_SHORT_NAME;
f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
select profile_option_id, application_id into prof_id, prof_app_id
from FND_PROFILE_OPTIONS
where PROFILE_OPTION_NAME = X_PROFILE_OPTION_NAME;
/* updated with the real profile information later on during */
/* the load when the real profile data gets uploaded. */
/********** WARNING !!!!!!!!!!!! *********/
/* Here fnd_profile_options_pkg.INSERT_ROW() is called instead of
* fnd_profile_options_pkg.LOAD_ROW() since in this case there's an extra
* processing for the values passed to Last_Updated_By and Last_Update_Date
* In this case, the Creation_Date should be set to X_LAST_UPDATE_DATE
* whereas the Last_Update_Date should be set to FND_API.G_MISS_DATE.
* Similarly, Created_By is set to OWNER and Last_Updated_By to 'SEED'/1.
* This is to ensure that, 'fnd_load_util.upload_test()' succeeds for this
* dummy row and consequently this Dummy profile definition always gets
* updated with the actual definition when the profile ldt shipping the
* right definition is uploaded.
*/
select fnd_profile_options_s.nextval
into prof_id
from dual;
select application_id into prof_app_id
from fnd_application
where application_short_name = nvl(X_PROF_APPL_SHORT_NAME, X_APPLICATION_SHORT_NAME);
fnd_profile_options_pkg.insert_row (
x_rowid => row_id,
x_profile_option_name => X_PROFILE_OPTION_NAME,
x_application_id => prof_app_id,
x_profile_option_id => prof_id,
x_write_allowed_flag => 'N',
x_read_allowed_flag => 'N',
x_user_changeable_flag => 'N',
x_user_visible_flag => 'N',
x_site_enabled_flag => 'N',
x_site_update_allowed_flag => 'N',
x_app_enabled_flag => 'N',
x_app_update_allowed_flag => 'N',
x_resp_enabled_flag => 'N',
x_resp_update_allowed_flag => 'N',
x_user_enabled_flag => 'N',
x_user_update_allowed_flag => 'N',
x_start_date_active => FND_API.G_MISS_DATE,
x_sql_validation => NULL,
x_end_date_active => FND_API.G_MISS_DATE,
x_user_profile_option_name => X_PROFILE_OPTION_NAME,
x_description => NULL,
x_creation_date => f_ludate,
x_created_by => f_luby,
x_last_update_date => FND_API.G_MISS_DATE,
x_last_updated_by => fnd_load_util.owner_id('SEED'),
x_last_update_login => 0,
x_hierarchy_type => 'SECURITY',
x_server_enabled_flag => 'N',
x_server_update_allowed_flag => 'N',
x_org_enabled_flag => 'N',
x_org_update_allowed_flag => 'N',
x_serverresp_enabled_flag => 'N',
x_serverresp_upd_allow_fl => 'N');
select profile_option_id, application_id
into prof_id, prof_app_id
from FND_PROFILE_OPTIONS
where PROFILE_OPTION_NAME = X_PROFILE_OPTION_NAME;
select 1 into retVal
from FND_PROFILE_CATS
where NAME = X_CATEGORY_NAME
and APPLICATION_ID = app_id;
select LAST_UPDATED_BY, LAST_UPDATE_DATE
into db_luby, db_ludate
from FND_PROFILE_CAT_OPTIONS
where PROFILE_OPTION_APPLICATION_ID = prof_app_id
and PROFILE_OPTION_ID = prof_id
and CATEGORY_NAME = upper(X_CATEGORY_NAME)
and APPLICATION_ID = app_id;
FND_PROFILE_CAT_OPTIONS_PKG.UPDATE_ROW (
X_PROFILE_OPTION_APPLICATION_I => prof_app_id,
X_PROFILE_OPTION_ID => prof_id,
X_CATEGORY_NAME => X_CATEGORY_NAME,
X_APPLICATION_ID => app_id,
X_DISPLAY_SEQUENCE => X_DISPLAY_SEQUENCE,
X_DISPLAY_TYPE => X_DISPLAY_TYPE,
X_LAST_UPDATE_DATE => f_ludate,
X_LAST_UPDATED_BY => f_luby,
X_LAST_UPDATE_LOGIN => f_luby);
FND_PROFILE_CAT_OPTIONS_PKG.INSERT_ROW (
X_ROWID => row_id,
X_PROFILE_OPTION_APPLICATION_I => prof_app_id,
X_PROFILE_OPTION_ID => prof_id,
X_CATEGORY_NAME => X_CATEGORY_NAME,
X_APPLICATION_ID => app_id,
X_DISPLAY_SEQUENCE => X_DISPLAY_SEQUENCE,
X_DISPLAY_TYPE => X_DISPLAY_TYPE,
X_CREATION_DATE => f_ludate,
X_CREATED_BY => f_luby,
X_LAST_UPDATE_DATE => f_ludate,
X_LAST_UPDATED_BY => f_luby,
X_LAST_UPDATE_LOGIN => f_luby);