[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
30-Sep-05 tvankayl 115.6 Added delete validation for element
sets of element_set_type = 'E'.
The changes were initially made in
the branch version 115.2.1159.2.
============================================================================*/
--
-----------------------------------------------------------------------------
-- Name --
-- lock_element_set --
-- Purpose --
-- Places a lock on the element set. --
-- Arguments --
-- See Be;ow. --
select els.element_set_id
from pay_element_sets els
where els.element_set_id = p_element_set_id
for update;
select ecl.costing_debit_or_credit
from pay_element_classifications ecl
where ecl.classification_id = p_classification_id
and exists
(select null
from pay_element_type_rules etr,
pay_element_types_f et,
pay_element_classifications ecl2
where etr.element_set_id = p_element_set_id
and et.element_type_id = etr.element_type_id
and ecl2.classification_id = et.classification_id
and ecl2.costing_debit_or_credit <>
ecl.costing_debit_or_credit);
select ecl.costing_debit_or_credit
from pay_element_classifications ecl
where ecl.classification_id = p_classification_id
and exists
(select null
from pay_ele_classification_rules ecr,
pay_element_classifications ecl2
where ecr.element_set_id = p_element_set_id
and ecl2.classification_id = ecr.classification_id
and ecl2.costing_debit_or_credit <>
ecl.costing_debit_or_credit);
select els.element_set_id
from pay_element_sets els
where upper(els.element_set_name) = upper(p_element_set_name)
and nvl(els.business_group_id,nvl(p_business_group_id,0)) =
nvl(p_business_group_id,0)
and nvl(els.legislation_code,nvl(p_legislation_code,' ')) =
nvl(p_legislation_code,' ')
and (p_rowid is null or
(p_rowid is not null and chartorowid(p_rowid) <> els.rowid));
procedure chk_delete_element_set
(
p_element_set_id number,
p_element_set_type varchar2
) is
--
cursor csr_element_links is
select el.element_set_id
from pay_element_links_f el
where el.element_set_id = p_element_set_id;
select pa.element_set_id
from pay_payroll_actions pa
where pa.element_set_id = p_element_set_id;
select fnd_number.canonical_to_number(rv.value)
from pay_restriction_values rv
where rv.restriction_code = 'ELEMENT_SET'
and rv.value = to_char(p_element_set_id);
select egu.element_set_id
from pay_event_group_usages egu
where egu.element_set_id = p_element_set_id;
hr_utility.set_message(801, 'HR_6051_ELE_SET_SET_DELETES');
hr_utility.set_message(801, 'HR_6054_ELE_SET_SET_DELETES');
hr_utility.set_message(801, 'HR_6050_ELE_SET_SET_DELETES');
end chk_delete_element_set;
procedure delete_element_set_cascade
(
p_element_set_id number
) is
--
begin
--
delete from pay_element_type_rules
where element_set_id = p_element_set_id;
delete from pay_ele_classification_rules
where element_set_id = p_element_set_id;
end delete_element_set_cascade;
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Element_Set_Id IN OUT NOCOPY NUMBER,
X_Business_Group_Id NUMBER,
X_Legislation_Code VARCHAR2,
X_Element_Set_Name VARCHAR2,
X_Element_Set_Type VARCHAR2,
X_Comments VARCHAR2,
-- Extra Columns
X_Session_Business_Group_Id NUMBER,
X_Session_Legislation_Code VARCHAR2) IS
--
CURSOR C IS SELECT rowid FROM pay_element_sets
WHERE element_set_id = X_Element_Set_Id;
CURSOR C2 IS SELECT pay_element_sets_s.nextval FROM sys.dual;
INSERT INTO pay_element_sets
(element_set_id,
business_group_id,
legislation_code,
element_set_name,
element_set_type,
comments
)
VALUES
(X_Element_Set_Id,
X_Business_Group_Id,
X_Legislation_Code,
X_Element_Set_Name,
X_Element_Set_Type,
X_Comments
);
'pay_element_sets_pkg.insert_row');
END Insert_Row;
CURSOR C IS SELECT * FROM pay_element_sets
WHERE rowid = X_Rowid FOR UPDATE of Element_Set_Id NOWAIT;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Element_Set_Id NUMBER,
X_Business_Group_Id NUMBER,
X_Legislation_Code VARCHAR2,
X_Element_Set_Name VARCHAR2,
X_Element_Set_Type VARCHAR2,
X_Comments VARCHAR2,
-- Extra Columns
X_Session_Business_Group_Id NUMBER,
X_Session_Legislation_Code VARCHAR2,
X_Base_Element_Set_Name in varchar2 default hr_api.g_varchar2) IS
BEGIN
--
check_unique_set
(X_Rowid,
X_Session_Business_Group_Id,
X_Session_Legislation_Code,
X_Element_Set_Name);
UPDATE pay_element_sets
SET element_set_id = X_Element_Set_Id,
business_group_id = X_Business_Group_Id,
legislation_code = X_Legislation_Code,
element_set_name = X_Element_Set_Name,
element_set_type = X_Element_Set_Type,
comments = X_Comments
WHERE rowid = X_rowid;
UPDATE pay_element_sets
SET element_set_id = X_Element_Set_Id,
business_group_id = X_Business_Group_Id,
legislation_code = X_Legislation_Code,
element_set_name = X_Base_Element_Set_Name,
element_set_type = X_Element_Set_Type,
comments = X_Comments
WHERE rowid = X_rowid;
'pay_element_sets_pkg.update_row');
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2,
-- Extra Columns
X_Element_Set_Id NUMBER,
X_Element_Set_Type VARCHAR2) IS
BEGIN
--
chk_delete_element_set
(X_Element_Set_Id,
X_Element_Set_Type);
delete_element_set_cascade
(X_Element_Set_Id);
DELETE FROM pay_element_sets
WHERE rowid = X_Rowid;
'pay_element_sets_pkg.delete_row');
END Delete_Row;
delete from PAY_ELEMENT_SETS_TL T
where not exists
(select NULL
from PAY_ELEMENT_SETS B
where B.ELEMENT_SET_ID = T.ELEMENT_SET_ID
);
update PAY_ELEMENT_SETS_TL T
set (ELEMENT_SET_NAME) =
(select B.ELEMENT_SET_NAME
from PAY_ELEMENT_SETS_TL B
where B.ELEMENT_SET_ID = T.ELEMENT_SET_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (T.ELEMENT_SET_ID,T.LANGUAGE) in
(select SUBT.ELEMENT_SET_ID,SUBT.LANGUAGE
from PAY_ELEMENT_SETS_TL SUBB, PAY_ELEMENT_SETS_TL SUBT
where SUBB.ELEMENT_SET_ID = SUBT.ELEMENT_SET_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.ELEMENT_SET_NAME <> SUBT.ELEMENT_SET_NAME
));
insert into PAY_ELEMENT_SETS_TL (
ELEMENT_SET_ID,
ELEMENT_SET_NAME,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LANGUAGE,
SOURCE_LANG
) select
B.ELEMENT_SET_ID,
B.ELEMENT_SET_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_ELEMENT_SETS_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from PAY_ELEMENT_SETS_TL T
where T.ELEMENT_SET_ID = B.ELEMENT_SET_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
UPDATE PAY_ELEMENT_SETS_TL
SET ELEMENT_SET_NAME = nvl(X_ELEMENT_SET_NAME,ELEMENT_SET_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 ELEMENT_SET_ID in
(select ELEMENT_SET_ID
from PAY_ELEMENT_SETS
where nvl(ELEMENT_SET_NAME,'~null~')=nvl(X_B_ELEMENT_SET_NAME,'~null~')
and nvl(LEGISLATION_CODE,'~null~') = nvl(X_B_LEGISLATION_CODE,'~null~')
and BUSINESS_GROUP_ID is NULL);
SELECT 1
FROM pay_element_sets_tl est,
pay_element_sets els
WHERE upper(est.element_set_name)=upper(p_element_set_name)
AND est.element_set_id = els.element_set_id
AND est.language = p_language
AND (els.element_set_id <> p_element_set_id OR p_element_set_id IS NULL)
AND (nvl(els.business_group_id,-1) = nvl(p_bus_grp_id,-1) OR p_bus_grp_id IS NULL)
AND (nvl(els.LEGISLATION_CODE,'~null~') = nvl(p_leg_code,'~null~') OR p_leg_code IS NULL);