[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure insert_row(p_rowid in out NOCOPY varchar2,
p_user_column_id in out NOCOPY number,
p_user_table_id in number,
p_business_group_id in number,
p_legislation_code in varchar2,
p_legislation_subgroup in varchar2,
p_user_column_name in varchar2,
p_formula_id in number ) is
cursor c1 is
select pay_user_columns_s.nextval
from sys.dual ;
select rowid
from pay_user_columns
where user_column_id = p_user_column_id ;
insert into PAY_USER_COLUMNS
( USER_COLUMN_ID,
USER_TABLE_ID,
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
LEGISLATION_SUBGROUP,
USER_COLUMN_NAME,
FORMULA_ID )
values ( p_user_column_id,
p_user_table_id,
p_business_group_id,
p_legislation_code,
p_legislation_subgroup,
p_user_column_name,
p_formula_id ) ;
end insert_row ;
procedure update_row(p_rowid in varchar2,
p_user_column_id in number,
p_user_table_id in number,
p_business_group_id in number,
p_legislation_code in varchar2,
p_legislation_subgroup in varchar2,
p_user_column_name in varchar2,
p_formula_id in number,
p_base_user_column_name in varchar2 default hr_api.g_varchar2) is
begin
--
update PAY_USER_COLUMNS
set USER_COLUMN_ID = p_user_column_id,
USER_TABLE_ID = p_user_table_id,
BUSINESS_GROUP_ID = p_business_group_id ,
LEGISLATION_CODE = p_legislation_code,
LEGISLATION_SUBGROUP = p_legislation_subgroup ,
USER_COLUMN_NAME = p_base_user_column_name,
FORMULA_ID = p_formula_id
where ROWID = p_rowid;
end update_row;
procedure delete_row(p_rowid in varchar2) is
--
ucid NUMBER;
select user_column_id into ucid from pay_user_columns
where rowid = p_rowid;
delete from PAY_USER_COLUMNS
where ROWID = p_rowid;
end delete_row;
cursor T is select *
from PAY_USER_COLUMNS_TL
where user_column_id = p_user_column_id
and language = userenv('lang')
for update NOWAIT ;
cursor C is select *
from PAY_USER_COLUMNS
where rowid = p_rowid
for update of USER_COLUMN_ID NOWAIT ;
select '1'
from pay_user_columns uc
where upper(uc.user_column_name) = upper( p_base_user_column_name)
and uc.user_table_id = p_user_table_id
and ( p_rowid is null
or ( p_rowid is not null
and p_rowid <> uc.rowid ) )
and nvl(uc.business_group_id,nvl(p_business_group_id, -1))
= nvl(p_business_group_id, -1);
select '1'
from pay_user_columns uc
where upper(uc.user_column_name) = upper( p_base_user_column_name)
and uc.user_table_id = p_user_table_id
and ( p_rowid is null
or ( p_rowid is not null
and p_rowid <> uc.rowid ) )
and nvl(uc.business_group_id,nvl(p_business_group_id, -1))
= nvl(p_business_group_id, -1)
and nvl(uc.legislation_code, nvl(p_legislation_code,'~~nvl~~'))
= nvl(p_legislation_code, '~~nvl~~');
select '1'
from pay_user_columns uc,pay_user_columns_tl ucl
where upper(ucl.user_column_name) = upper(p_user_column_name)
and ucl.user_column_id = uc.user_column_id
and uc.user_table_id = p_user_table_id
and nvl(uc.business_group_id,nvl(p_business_group_id, -1))
= nvl(p_business_group_id, -1)
and nvl(uc.legislation_code, nvl(p_legislation_code,'~~nvl~~'))
= nvl(p_legislation_code, '~~nvl~~')
and (ucl.rowid not in ((select rowid from pay_user_columns_tl pct
where pct.user_column_id = (select user_column_id from
pay_user_columns
where rowid = p_rowid)
--and language = userenv('lang')
)));
procedure check_delete ( p_user_column_id in number ) is
cursor c1 is
select null
from pay_user_column_instances_f
where user_column_id = p_user_column_id ;
end check_delete ;
procedure check_base_update(p_base_user_column_name in varchar2,
p_rowid in varchar2) is
l_package_name VARCHAR2(80) := 'PAY_USER_COLUMNS_PKG.CHECK_UPDATE';
select base_user_column_name into original_user_column_name
from pay_user_columns_vl
where row_id = p_rowid;
end check_base_update;
delete from PAY_USER_COLUMNS_TL T
where not exists
(select NULL
from PAY_USER_COLUMNS B
where B.USER_COLUMN_ID = T.USER_COLUMN_ID
);
update PAY_USER_COLUMNS_TL T
set (USER_COLUMN_NAME) =
(select B.USER_COLUMN_NAME
from PAY_USER_COLUMNS_TL B
where B.USER_COLUMN_ID = T.USER_COLUMN_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (T.USER_COLUMN_ID,T.LANGUAGE) in
(select SUBT.USER_COLUMN_ID,SUBT.LANGUAGE
from PAY_USER_COLUMNS_TL SUBB, PAY_USER_COLUMNS_TL SUBT
where SUBB.USER_COLUMN_ID = SUBT.USER_COLUMN_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.USER_COLUMN_NAME <> SUBT.USER_COLUMN_NAME
));
insert into PAY_USER_COLUMNS_TL (
USER_COLUMN_ID,
USER_COLUMN_NAME,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LANGUAGE,
SOURCE_LANG
) select
B.USER_COLUMN_ID,
B.USER_COLUMN_NAME,
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_COLUMNS_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_COLUMNS_TL T
where T.USER_COLUMN_ID = B.USER_COLUMN_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
UPDATE PAY_USER_COLUMNS_TL
SET USER_COLUMN_NAME = nvl(X_USER_COLUMN_NAME,USER_COLUMN_NAME),
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_COLUMN_ID in
(select USER_COLUMN_ID
from PAY_USER_COLUMNS
where nvl(USER_COLUMN_NAME,'~null~')=nvl(X_B_USER_COLUMN_NAME,'~null~')
and nvl(LEGISLATION_CODE,'~null~') = nvl(X_B_LEGISLATION_CODE,'~null~')
and BUSINESS_GROUP_ID is NULL);
select '1'
from pay_user_columns uc,
pay_user_columns_tl ucl
where upper(ucl.user_column_name) = upper(p_user_column_name)
AND uc.user_column_id = ucl.user_column_id
AND (ucl.user_column_id <> p_user_column_id OR p_user_column_id IS NULL)
AND uc.user_table_id = p_user_table_id
AND ucl.language = p_language
AND (nvl(uc.business_group_id,-1) = nvl(p_bus_grp_id,-1) OR p_bus_grp_id IS NULL)
AND (nvl(uc.LEGISLATION_CODE,'~null~') = nvl(p_leg_code,'~null~') OR p_leg_code IS NULL);