The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure UPDATE_ROW (
X_OPTION_ID in number
,X_VALUE in varchar2
,X_ENCRYPTED in varchar2
,X_LAST_UPDATE_DATE in DATE
,X_LAST_UPDATED_BY in NUMBER
,X_LAST_UPDATE_LOGIN in NUMBER
,X_OBJECT_VERSION_NUMBER in NUMBER
) is
l_integration_id number;
select integration_id,option_type_id,option_level_id
into l_integration_id,l_option_type_id,l_option_level_id
from hr_ki_options
where OPTION_ID = X_OPTION_ID;
update HR_KI_OPTIONS
set
VALUE = l_value ,
ENCRYPTED = X_ENCRYPTED,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER+1
where OPTION_ID = X_OPTION_ID;
end UPDATE_ROW;
procedure INSERT_ROW (
X_ROWID in out nocopy VARCHAR2,
X_OPTION_ID in out nocopy NUMBER,
X_OPTION_TYPE_ID in number,
X_OPTION_LEVEL in number,
X_OPTION_LEVEL_ID in varchar2,
X_INTEGRATION_ID in number,
X_VALUE in varchar2,
X_ENCRYPTED 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
) is
cursor C is select ROWID from HR_KI_OPTIONS
where option_id = X_OPTION_ID;
select HR_KI_OPTIONS_S.NEXTVAL into X_OPTION_ID from sys.dual;
insert into HR_KI_OPTIONS (
OPTION_ID,
OPTION_TYPE_ID,
OPTION_LEVEL,
OPTION_LEVEL_ID,
INTEGRATION_ID,
VALUE,
ENCRYPTED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER
) values (
X_OPTION_ID,
X_OPTION_TYPE_ID,
X_OPTION_LEVEL,
X_OPTION_LEVEL_ID,
X_INTEGRATION_ID,
l_value,
X_ENCRYPTED,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN,
1
);
'hr_ki_options.insert_row');
end INSERT_ROW;
select integration_id
from
hr_ki_integrations
where
upper(integration_key) = upper(X_INTEGRATION_KEY);
select option_type_id
from
hr_ki_option_types
where
upper(option_type_key) = upper(X_OPTION_TYPE_KEY);
select application_id from fnd_application
where
application_short_name = X_OPTION_LEVEL_KEY;
select responsibility_id||'#'||application_id from fnd_responsibility
where
responsibility_key = X_OPTION_LEVEL_KEY;
select user_id from fnd_user
where
user_name = X_OPTION_LEVEL_KEY;
X_LAST_UPDATE_DATE in VARCHAR2
)
is
l_proc VARCHAR2(31) := 'HR_KI_OPTIONS_API.LOAD_ROW';
l_last_update_date HR_KI_OPTIONS.last_update_date%TYPE := SYSDATE;
l_last_updated_by HR_KI_OPTIONS.last_updated_by%TYPE := 0;
l_last_update_login HR_KI_OPTIONS.last_update_login%TYPE := 0;
db_ludate date; -- entity update date in db
select
distinct opt.option_id,opt.object_version_number
from HR_KI_OPTIONS opt,HR_KI_OPTION_TYPES oty,
hr_ki_integrations int
where
opt.option_type_id=oty.option_type_id
and int.integration_id=opt.integration_id
and upper(int.integration_key) = upper(X_INTEGRATION_KEY)
and upper(oty.option_type_key)=upper(X_OPTION_TYPE_KEY)
and opt.option_level=to_number(X_OPTION_LEVEL)
and opt.option_level_id is null;
select
distinct opt.option_id,opt.object_version_number
from HR_KI_OPTIONS opt,HR_KI_OPTION_TYPES oty,
hr_ki_integrations int
where
opt.option_type_id=oty.option_type_id
and int.integration_id=opt.integration_id
and upper(int.integration_key) = upper(X_INTEGRATION_KEY)
and upper(oty.option_type_key) =upper(X_OPTION_TYPE_KEY)
and opt.option_level=to_number(X_OPTION_LEVEL);
select
distinct opt.option_id,opt.object_version_number
from HR_KI_OPTIONS opt,HR_KI_OPTION_TYPES oty,
hr_ki_integrations int,
fnd_user usr
where
opt.option_type_id=oty.option_type_id
and int.integration_id=opt.integration_id
and upper(int.integration_key) = upper(X_INTEGRATION_KEY)
and upper(oty.option_type_key) =upper(X_OPTION_TYPE_KEY)
and opt.option_level=to_number(X_OPTION_LEVEL)
and usr.user_name=X_OPTION_LEVEL_KEY
and to_char(usr.user_id)=opt.OPTION_LEVEL_ID;
select
distinct opt.option_id,opt.object_version_number
from HR_KI_OPTIONS opt,HR_KI_OPTION_TYPES oty,
hr_ki_integrations int,
fnd_responsibility resp
where
opt.option_type_id=oty.option_type_id
and int.integration_id=opt.integration_id
and upper(int.integration_key) = upper(X_INTEGRATION_KEY)
and upper(oty.option_type_key) = upper(X_OPTION_TYPE_KEY)
and opt.option_level=to_number(X_OPTION_LEVEL)
and resp.responsibility_key= X_OPTION_LEVEL_KEY
and resp.responsibility_id =
(substr(option_level_id, 0, instr(option_level_id, '#') - 1))
and resp.application_id =
(substr(option_level_id, instr(option_level_id, '#') + 1));
select
distinct opt.option_id,opt.object_version_number
from HR_KI_OPTIONS opt,HR_KI_OPTION_TYPES oty,
hr_ki_integrations int,
fnd_application app
where
opt.option_type_id=oty.option_type_id
and int.integration_id=opt.integration_id
and upper(int.integration_key) = upper(X_INTEGRATION_KEY)
and upper(oty.option_type_key) =upper(X_OPTION_TYPE_KEY)
and opt.option_level=to_number(X_OPTION_LEVEL)
and app.application_short_name=X_OPTION_LEVEL_KEY
and to_char(app.application_id)=opt.OPTION_LEVEL_ID;
l_last_updated_by := fnd_load_util.owner_id(X_OWNER);
l_last_update_date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD hh24:mi:ss'), sysdate);
select LAST_UPDATED_BY, LAST_UPDATE_DATE
into db_luby, db_ludate
from HR_KI_OPTIONS
where option_id = l_option_id;
if (fnd_load_util.upload_test(l_last_updated_by, l_last_update_date, db_luby,
db_ludate, X_CUSTOM_MODE)) then
UPDATE_ROW
(
X_OPTION_ID => l_option_id
,X_VALUE => X_VALUE
,X_ENCRYPTED => X_ENCRYPTED
,X_LAST_UPDATE_DATE => l_last_update_date
,X_LAST_UPDATED_BY => l_last_updated_by
,X_LAST_UPDATE_LOGIN => l_last_update_login
,X_OBJECT_VERSION_NUMBER => l_object_version_number
);
INSERT_ROW
(X_ROWID => l_rowid
,X_OPTION_ID => l_option_id
,X_OPTION_TYPE_ID => l_option_type_id
,X_OPTION_LEVEL => to_number(X_OPTION_LEVEL)
,X_OPTION_LEVEL_ID => l_option_level_id
,X_INTEGRATION_ID => l_integration_id
,X_VALUE => X_VALUE
,X_ENCRYPTED => X_ENCRYPTED
,X_CREATED_BY => l_created_by
,X_CREATION_DATE => l_creation_date
,X_LAST_UPDATE_DATE => l_last_update_date
,X_LAST_UPDATED_BY => l_last_updated_by
,X_LAST_UPDATE_LOGIN => l_last_update_login
);