[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row(p_Rowid IN OUT NOCOPY VARCHAR2,
p_Sub_Classification_Rule_Id IN OUT NOCOPY NUMBER,
p_Effective_Start_Date DATE,
p_Effective_End_Date DATE,
p_Element_Type_Id NUMBER,
p_Classification_Id NUMBER,
p_Business_Group_Id NUMBER,
p_Legislation_Code VARCHAR2,
p_Last_Update_Date DATE,
p_Last_Updated_By NUMBER,
p_Last_Update_Login NUMBER,
p_Created_By NUMBER,
p_Creation_Date DATE) IS
cursor csr_new_rowid is
select rowid
from pay_sub_classification_rules_f
where sub_classification_rule_id = p_sub_classification_rule_id
and effective_start_date = p_effective_start_date;
select pay_sub_classification_rules_s.nextval
from sys.dual;
hr_utility.set_location ('PAY_SUB_CLASS_RULES_PKG.INSERT_ROW',1);
hr_utility.set_location ('PAY_SUB_CLASS_RULES_PKG.INSERT_ROW',2);
insert into pay_sub_classification_rules_f(
sub_classification_rule_id,
effective_start_date,
effective_end_date,
element_type_id,
classification_id,
business_group_id,
legislation_code,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date
) VALUES (
p_Sub_Classification_Rule_Id,
p_Effective_Start_Date,
p_Effective_End_Date,
p_Element_Type_Id,
p_Classification_Id,
p_Business_Group_Id,
p_Legislation_Code,
sysdate,
c_user_id,
c_login_id,
c_user_id,
sysdate
);
hr_utility.set_location ('PAY_SUB_CLASS_RULES_PKG.INSERT_ROW',3);
hr_utility.set_message_token('PROCEDURE','PAY_SUB_CLASS_RULES_PKG.INSERT_ROW');
hr_utility.set_location ('PAY_SUB_CLASS_RULES_PKG.INSERT_ROW',4);
insert into hr_application_ownerships
(key_name,
key_value,
product_name)
select 'SUB_CLASSIFICATION_RULE_ID',
p_sub_classification_rule_id,
ao.product_name
from hr_application_ownerships ao
where ao.key_name = 'ELEMENT_TYPE_ID'
and ao.key_value = p_element_type_id
and not exists (select 'SUB_CLASSIFICATION_RULE_ID',
p_sub_classification_rule_id,
ao.product_name
from hr_application_ownerships ao
where ao.key_name = 'ELEMENT_TYPE_ID'
and ao.key_value = p_element_type_id);
hr_utility.set_location ('PAY_SUB_CLASS_RULES_PKG.INSERT_ROW',5);
end insert_row;
select *
from pay_sub_classification_rules_f
where rowid = p_rowid
for update of sub_classification_rule_id NOWAIT;
PROCEDURE Update_Row(p_Rowid VARCHAR2,
p_Sub_Classification_Rule_Id NUMBER,
p_Effective_Start_Date DATE,
p_Effective_End_Date DATE,
p_Element_Type_Id NUMBER,
p_Classification_Id NUMBER,
p_Business_Group_Id NUMBER,
p_Legislation_Code VARCHAR2,
p_Last_Update_Date DATE,
p_Last_Updated_By NUMBER,
p_Last_Update_Login NUMBER) IS
BEGIN
UPDATE pay_sub_classification_rules_f
SET
sub_classification_rule_id = p_Sub_Classification_Rule_Id,
effective_start_date = p_Effective_Start_Date,
effective_end_date = p_Effective_End_Date,
element_type_id = p_Element_Type_Id,
classification_id = p_Classification_Id,
business_group_id = p_Business_Group_Id,
legislation_code = p_Legislation_Code,
last_update_date = sysdate,
last_updated_by = c_user_id,
last_update_login = c_login_id
WHERE rowid = p_rowid;
hr_utility.set_message_token('PROCEDURE','PAY_SUB_CLASS_RULES_PKG.UPDATE_ROW');
END Update_Row;
procedure INSERT_DEFAULTS (
--
--******************************************************************************
--* Inserts a row into the base table for each default sub-classification *
--* belonging to the primary classification of a newly inserted element type *
--******************************************************************************
--
-- Parameters are:
--
p_element_type_id number,
p_classification_id number,
p_effective_start_date date,
p_effective_end_date date,
p_business_group_id number,
p_legislation_code varchar2 ) is
--
cursor csr_legislation_code is
select legislation_code
from per_business_groups_perf
where business_group_id = p_business_group_id;
select classification_id
from pay_element_classifications
where parent_classification_id = p_classification_id
and create_by_default_flag = 'Y'
and (p_business_group_id = business_group_id + 0
or (business_group_id is null
and (legislation_code =
nvl(hr_api.return_legislation_code(p_business_group_id)
,p_legislation_code))
));
hr_utility.set_location ('pay_sub_class_rules_pkg.insert_defaults',1);
for default_insertion in csr_next_default LOOP
--
insert_row (
dummy_rowid,
dummy_id,
p_effective_start_date,
p_effective_end_date,
p_element_type_id,
default_insertion.classification_id,
p_business_group_id,
p_legislation_code,
null,null,null,null,null);
end insert_defaults;
select min(effective_start_date) -1
from pay_sub_classification_rules_f
where element_type_id = p_element_type_id
and classification_id = p_classification_id
and effective_end_date > p_session_date;
select pay_sub_classification_rules_s.nextval
from sys.dual;
p_delete_mode varchar2,
p_validation_start_date date,
p_validation_end_date date ) is
--
begin
--
hr_utility.set_location ('pay_sub_class_rules_pkg.MAINTAIN_DELETION_INTEGRITY',1);
p_delete_mode,
p_validation_start_date,
p_validation_end_date );
procedure DELETE_ROW (
--
--******************************************************************************
--* Handles deletion from the base table either for forms based on *
--* non-updatable view or for implicit deletions caused by action on other *
--* entities. *
--******************************************************************************
--
-- Parameters to be passed in are:
--
p_rowid varchar2,
p_sub_classification_rule_id number,
p_delete_mode varchar2,
p_validation_start_date date,
p_validation_end_date date ) is
--
begin
--
hr_utility.set_location ('pay_sub_class_rules_pkg.DELETE_ROW',1);
p_delete_mode,
p_validation_start_date,
p_validation_end_date );
delete from pay_sub_classification_rules_f
where rowid = p_rowid;
'PAY_SUB_CLASS_RULES_PKG.DELETE_ROW');
delete from hr_application_ownerships
where key_name = 'SUB_CLASSIFICATION_RULE_ID'
and key_value = p_sub_classification_rule_id;
end delete_row;
procedure PARENT_DELETED (
--
--******************************************************************************
--* Handles the case when any row referenced by a foreign key of the base *
--* is deleted (in whatever Date Track mode). ie If a parent record is zapped *
--* then the deletion is cascaded; if it is date-effectively deleted, then the *
p_parent_id number,-- The foreign key for the deleted parent
p_session_date date default trunc (sysdate),
p_validation_start_date date,
p_validation_end_date date,
p_delete_mode varchar2 default 'DELETE',
p_parent_name varchar2 -- The name of the parent entity
) is
--
-- The following cursor fetches all rows identified by the foreign key to
-- the parent being deleted. The parent name identifies foreign key column
-- to use, thus the procedure is generic to any parent deletion
--
cursor csr_rows_owned_by_parent is
select rowid,pay_sub_classification_rules_f.*
from pay_sub_classification_rules_f
where p_parent_id = decode (p_parent_name,
'PAY_ELEMENT_TYPES_F',element_type_id,
classification_id)
for update;
hr_utility.set_location ('pay_sub_class_rules_pkg.parent_deleted',1);
-- If in ZAP mode then all rows belonging to the deleted
-- parent must be deleted. If in DELETE (ie date-effective
-- delete) mode then only rows with a future start date
-- must be deleted, and current rows must be updated so
-- that their end dates match that of their closed-down
-- parent. Current and future are determined by session
-- date.
--
if p_delete_mode = 'ZAP' -- ie delete all rows
or (p_delete_mode = 'DELETE' -- ie delete all future rows
and fetched_rule.effective_start_date > p_session_date) then
--
delete_row( fetched_rule.rowid,
fetched_rule.sub_classification_rule_id,
p_delete_mode,
p_validation_start_date,
p_validation_end_date );
elsif p_delete_mode = 'DELETE'
and p_session_date between fetched_rule.effective_start_date
and fetched_rule.effective_end_date then
--
update pay_sub_classification_rules_f
set effective_end_date = p_session_date
where current of csr_rows_owned_by_parent;
elsif (p_delete_mode in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE')) then
update pay_sub_classification_rules_f
set effective_end_date = p_validation_end_date
where current of csr_rows_owned_by_parent;
end parent_deleted;