[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure insert_row(p_rowid in out NOCOPY varchar2,
p_user_table_id in out NOCOPY number,
p_business_group_id in number,
p_legislation_code in varchar2,
p_legislation_subgroup in varchar2,
p_range_or_match in varchar2,
p_user_key_units in varchar2,
p_user_table_name in varchar2,
p_user_row_title in varchar2 ) is
cursor c1 is
select pay_user_tables_s.nextval
from sys.dual ;
select rowid
from pay_user_tables
where user_table_id = p_user_table_id ;
insert into PAY_USER_TABLES
( USER_TABLE_ID,
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
LEGISLATION_SUBGROUP,
RANGE_OR_MATCH,
USER_KEY_UNITS,
USER_TABLE_NAME,
USER_ROW_TITLE )
values ( p_user_table_id,
p_business_group_id,
p_legislation_code,
p_legislation_subgroup,
p_range_or_match,
p_user_key_units,
p_user_table_name,
p_user_row_title ) ;
end insert_row ;
procedure update_row(p_rowid in varchar2,
p_user_table_id in number,
p_business_group_id in number,
p_legislation_code in varchar2,
p_legislation_subgroup in varchar2,
p_range_or_match in varchar2,
p_user_key_units in varchar2,
p_user_table_name in varchar2,
p_user_row_title in varchar2,
p_base_user_table_name in varchar2 default hr_api.g_varchar2,
p_base_user_row_title in varchar2 default hr_api.g_varchar2) is
begin
--
check_unique ( p_rowid => p_rowid,
p_user_table_name => p_user_table_name,
p_business_group_id => p_business_group_id,
p_legislation_code => p_legislation_code,
p_base_user_table_name => p_base_user_table_name) ;
update PAY_USER_TABLES
set USER_TABLE_ID = p_user_table_id,
BUSINESS_GROUP_ID = p_business_group_id ,
LEGISLATION_CODE = p_legislation_code,
LEGISLATION_SUBGROUP = p_legislation_subgroup ,
RANGE_OR_MATCH = p_range_or_match,
USER_KEY_UNITS = p_user_key_units,
USER_TABLE_NAME = p_base_user_table_name,
USER_ROW_TITLE = p_base_user_row_title
where ROWID = p_rowid;
end update_row;
procedure delete_row(p_rowid in varchar2,
p_user_table_id in number ) is
--
begin
check_references( p_user_table_id => p_user_table_id ) ;
delete from PAY_USER_TABLES
where ROWID = p_rowid;
end delete_row;
cursor T is select *
from PAY_USER_TABLES_TL
where user_table_id = p_user_table_id
and language = userenv('lang')
for update NOWAIT ;
cursor C is select *
from PAY_USER_TABLES
where rowid = p_rowid
for update of USER_TABLE_ID NOWAIT ;
select '1'
from pay_user_tables ut
where upper(ut.user_table_name) = upper(p_base_user_table_name)
and nvl(ut.business_group_id,nvl(p_business_group_id, -1))
= nvl(p_business_group_id, -1)
and nvl(ut.legislation_code, nvl(p_legislation_code,'~~nvl~~'))
= nvl(p_legislation_code, '~~nvl~~')
and ( p_rowid is null
or ( p_rowid is not null
and p_rowid <> ut.rowid )
) ;
select '1'
from pay_user_tables ut,pay_user_tables_tl utl
where upper(utl.user_table_name) = upper(p_user_table_name)
and utl.user_table_id = ut.user_table_id
and nvl(ut.business_group_id,nvl(p_business_group_id, -1))
= nvl(p_business_group_id, -1)
and nvl(ut.legislation_code, nvl(p_legislation_code,'~~nvl~~'))
= nvl(p_legislation_code, '~~nvl~~')
and (utl.rowid not in ((select rowid from pay_user_tables_tl ptt
where ptt.user_table_id = (select user_table_id from
pay_user_tables
where rowid = p_rowid)
--and language = userenv('lang')
)));
select '1'
from pay_user_columns
where user_table_id = p_user_table_id ;
select '1'
from pay_user_rows_f
where user_table_id = p_user_table_id ;
procedure check_base_update(p_base_user_table_name in varchar2,
p_rowid in varchar2) is
l_package_name VARCHAR2(80) := 'PAY_USER_TABLES_PKG.CHECK_UPDATE';
select base_user_table_name into original_user_table_name
from pay_user_tables_vl
where row_id = p_rowid;
end check_base_update;
select lo.lookup_code,
lo.meaning
from hr_lookups lo
where lookup_type = 'USER_VALUES_PROMPT'
and lo.lookup_code in ( 'L' , 'U' , 'E' ) ;
delete from PAY_USER_TABLES_TL T
where not exists
(select NULL
from PAY_USER_TABLES B
where B.USER_TABLE_ID = T.USER_TABLE_ID
);
update PAY_USER_TABLES_TL T set (
USER_TABLE_NAME,
USER_ROW_TITLE
) = (select
B.USER_TABLE_NAME,
B.USER_ROW_TITLE
from PAY_USER_TABLES_TL B
where B.USER_TABLE_ID = T.USER_TABLE_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.USER_TABLE_ID,
T.LANGUAGE
) in (select
SUBT.USER_TABLE_ID,
SUBT.LANGUAGE
from PAY_USER_TABLES_TL SUBB, PAY_USER_TABLES_TL SUBT
where SUBB.USER_TABLE_ID = SUBT.USER_TABLE_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.USER_TABLE_NAME <> SUBT.USER_TABLE_NAME
or SUBB.USER_ROW_TITLE <> SUBT.USER_ROW_TITLE
or (SUBB.USER_ROW_TITLE is null and SUBT.USER_ROW_TITLE is not null)
or (SUBB.USER_ROW_TITLE is not null and SUBT.USER_ROW_TITLE is null)
));
insert into PAY_USER_TABLES_TL (
USER_TABLE_ID,
USER_TABLE_NAME,
USER_ROW_TITLE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LANGUAGE,
SOURCE_LANG
) select
B.USER_TABLE_ID,
B.USER_TABLE_NAME,
B.USER_ROW_TITLE,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN,
B.CREATED_BY,
B.CREATION_DATE,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from PAY_USER_TABLES_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from PAY_USER_TABLES_TL T
where T.USER_TABLE_ID = B.USER_TABLE_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
UPDATE PAY_USER_TABLES_tl
SET USER_TABLE_NAME = nvl(X_USER_TABLE_NAME,USER_TABLE_NAME),
USER_ROW_TITLE = nvl(X_USER_ROW_TITLE,USER_ROW_TITLE),
last_update_date = SYSDATE,
last_updated_by = decode(x_owner,'SEED',1,0),
last_update_login = 0,
source_lang = userenv('LANG')
WHERE userenv('LANG') IN (language,source_lang)
AND USER_TABLE_ID in
(select USER_TABLE_ID
from PAY_USER_TABLES
where nvl(USER_TABLE_NAME,'~null~')=nvl(X_B_USER_TABLE_NAME,'~null~')
and nvl(LEGISLATION_CODE,'~null~') = nvl(X_B_LEGISLATION_CODE,'~null~')
and BUSINESS_GROUP_ID is NULL);
SELECT 1
FROM pay_user_tables_tl ptt,
pay_user_tables put
WHERE upper(ptt.user_table_name)=upper(p_user_table_name)
AND ptt.user_table_id = put.user_table_id
AND ptt.language = p_language
AND (put.user_table_id <> p_user_table_id OR p_user_table_id IS NULL)
AND (nvl(put.business_group_id,-1) = nvl(p_bus_grp_id,-1) OR p_bus_grp_id IS NULL)
AND (nvl(put.LEGISLATION_CODE,'~null~') = nvl(p_leg_code,'~null~') OR p_leg_code IS NULL);