[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 1
from PAY_FORMULA_RESULT_RULES_F
where status_processing_rule_id = p_status_processing_rule_id
and result_rule_type = 'D'
and (p_rowid is null
or (p_rowid is not null and p_rowid <> rowid))
and effective_start_date <=p_effective_end_date
and effective_end_date >=p_session_date;
select 1
from pay_formula_result_rules_f
where status_processing_rule_id = p_status_processing_rule_id
and result_rule_type = 'M'
and result_name = p_result_name
and effective_start_date <=p_effective_end_date
and effective_end_date >=p_session_date
and (p_rowid is null
or (p_rowid is not null and p_rowid <> rowid));
select 1
from pay_formula_result_rules_f
where status_processing_rule_id = p_status_processing_rule_id
and result_rule_type = 'S'
and result_name = p_result_name
and (p_rowid is null
or (p_rowid is not null and p_rowid <> rowid))
and effective_start_date <=p_effective_end_date
and effective_end_date >=p_session_date
and element_type_id = p_element_type_id;
select 1
from pay_formula_result_rules_f
where status_processing_rule_id = p_status_processing_rule_id
and result_rule_type = p_result_rule_type
and result_name = p_result_name
and input_value_id = p_input_value_id
and (p_rowid is null
or (p_rowid is not null and p_rowid <> rowid))
and effective_start_date <=p_effective_end_date
and effective_end_date >=p_session_date;
select ipv.input_value_id
from pay_input_values_f_tl ipv_tl,
pay_input_values_f ipv
where ipv_tl.input_value_id = ipv.input_value_id
and userenv('LANG') = ipv_tl.language
and ipv.element_type_id = p_element_type_id
and ipv_tl.name = v_pay_value
and ((p_result_data_type = 'D' and ipv.uom = 'D')
or (p_result_data_type = 'T' and ipv.uom = 'C')
or (p_result_data_type = 'N' and substr(ipv.uom,1,1) in ('H','I','M','N')));
PROCEDURE Insert_Row(p_Rowid IN OUT NOCOPY VARCHAR2,
p_Formula_Result_Rule_Id IN OUT NOCOPY NUMBER,
p_Effective_Start_Date DATE,
p_Effective_End_Date DATE,
p_Business_Group_Id NUMBER,
p_Legislation_Code VARCHAR2,
p_Element_Type_Id NUMBER,
p_Status_Processing_Rule_Id NUMBER,
p_Result_Name VARCHAR2,
p_Result_Rule_Type VARCHAR2,
p_Legislation_Subgroup VARCHAR2,
p_Severity_Level VARCHAR2,
p_Input_Value_Id NUMBER,
p_Created_By NUMBER,
p_session_date date
) IS
CURSOR C IS SELECT rowid FROM pay_formula_result_rules_f
WHERE formula_result_rule_id = p_formula_result_rule_id
AND effective_start_date = p_Effective_Start_Date;
CURSOR C2 IS SELECT pay_formula_result_rules_s.nextval FROM sys.dual;
hr_utility.set_location ('pay_formula_result_rules_pkg.insert_row',1);
hr_utility.set_location ('pay_formula_result_rules_pkg.insert_row',2);
hr_utility.set_location ('pay_formula_result_rules_pkg.insert_row',3);
insert into pay_formula_result_rules_f(
formula_result_rule_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
element_type_id,
status_processing_rule_id,
result_name,
result_rule_type,
legislation_subgroup,
severity_level,
input_value_id,
creation_date
)
values (
p_Formula_Result_Rule_Id,
p_Effective_Start_Date,
p_Effective_End_Date,
p_Business_Group_Id,
p_Legislation_Code,
p_Element_Type_Id,
p_Status_Processing_Rule_Id,
p_Result_Name,
p_Result_Rule_Type,
p_Legislation_Subgroup,
p_Severity_Level,
p_Input_Value_Id,
sysdate);
hr_utility.set_location ('pay_formula_result_rules_pkg.insert_row',4);
END Insert_Row;
SELECT *
FROM pay_formula_result_rules_f
WHERE rowid = p_Rowid
FOR UPDATE of formula_result_rule_id NOWAIT;
PROCEDURE Update_Row(p_Rowid VARCHAR2,
p_Formula_Result_Rule_Id NUMBER,
p_Effective_Start_Date DATE,
p_Effective_End_Date DATE,
p_Business_Group_Id NUMBER,
p_Legislation_Code VARCHAR2,
p_Element_Type_Id NUMBER,
p_Status_Processing_Rule_Id NUMBER,
p_Result_Name VARCHAR2,
p_Result_Rule_Type VARCHAR2,
p_Legislation_Subgroup VARCHAR2,
p_Severity_Level VARCHAR2,
p_Input_Value_Id NUMBER,
p_Last_Update_Date DATE,
p_Last_Updated_By NUMBER,
p_Last_Update_Login NUMBER
) IS
BEGIN
UPDATE pay_formula_result_rules_f
SET
formula_result_rule_id = p_Formula_Result_Rule_Id,
effective_start_date = p_Effective_Start_Date,
effective_end_date = p_Effective_End_Date,
business_group_id = p_Business_Group_Id,
legislation_code = p_Legislation_Code,
element_type_id = p_Element_Type_Id,
status_processing_rule_id = p_Status_Processing_Rule_Id,
result_name = p_Result_Name,
result_rule_type = p_Result_Rule_Type,
legislation_subgroup = p_Legislation_Subgroup,
severity_level = p_Severity_Level,
input_value_id = p_Input_Value_Id,
last_update_date = p_Last_Update_Date,
last_updated_by = p_Last_Updated_By,
last_update_login = p_Last_Update_Login
WHERE rowid = p_rowid;
END Update_Row;
PROCEDURE Delete_Row(p_Rowid VARCHAR2) IS
BEGIN
DELETE FROM pay_formula_result_rules_f
WHERE rowid = p_Rowid;
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,
p_delete_mode varchar2
) 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_formula_result_rules_f.*
from pay_formula_result_rules_f
where p_parent_id = decode (p_parent_name,
'PAY_STATUS_PROCESSING_RULES_F',status_processing_rule_id,
'PAY_ELEMENT_TYPES_F',element_type_id,
'PAY_INPUT_VALUES_F',input_value_id)
for update;
hr_utility.set_location ('pay_sub_class_rules_pkg.parent_deleted',1);
if p_delete_mode = 'ZAP' -- ie delete all rows
or (p_delete_mode = 'DELETE' -- ie delete all future rows
and fetched_row.effective_start_date > p_session_date) then
--
-- Do not allow zapping of result rules which target the parent element
-- (and thereby prevent zapping of the parent element)
if p_delete_mode = 'ZAP' and fetched_row.result_rule_type = 'S'
and p_parent_name = 'PAY_ELEMENT_TYPES_F' then
hr_utility.set_message (801,'PAY_6157_ELEMENT_NO_DEL_FRR');
delete from pay_formula_result_rules_f
where current of csr_rows_owned_by_parent;
delete from hr_application_ownerships
where key_name = 'FORMULA_RESULT_RULE_ID'
and key_value = fetched_row.formula_result_rule_id;
elsif p_delete_mode = 'DELETE'
and p_session_date between fetched_row.effective_start_date
and fetched_row.effective_end_date then
--
update pay_formula_result_rules_f
set effective_end_date = p_session_date
where current of csr_rows_owned_by_parent;
elsif p_delete_mode = 'DELETE_NEXT_CHANGE'
and p_parent_name = 'PAY_STATUS_PROCESSING_RULES_F' then
--
-- Do not allow delete-next-change to orphan result rules
--
hr_utility.set_message (801,'HR_7451_SPR_NO_DEL_NEXT_CHANGE');
end parent_deleted;
select min(frr.effective_start_date) -1
from pay_formula_result_rules_f frr
where frr.status_processing_rule_id = p_status_processing_rule_id
and frr.result_rule_type = 'D'
and frr.formula_result_rule_id <> nvl(p_formula_result_rule_id,0)
and frr.effective_end_date >= p_session_date;
select min(frr.effective_start_date) -1
from pay_formula_result_rules_f frr
where frr.status_processing_rule_id = p_status_processing_rule_id
and frr.result_rule_type = 'M'
and frr.result_name = p_result_name
and frr.formula_result_rule_id <> nvl(p_formula_result_rule_id,0)
and frr.effective_end_date >= p_session_date;
select min(frr.effective_start_date) -1
from pay_formula_result_rules_f frr
where frr.status_processing_rule_id = p_status_processing_rule_id
and frr.result_rule_type = 'S'
and frr.result_name = p_result_name
and frr.element_type_id = p_element_type_id
and frr.formula_result_rule_id <> nvl(p_formula_result_rule_id,0)
and frr.effective_end_date >= p_session_date;
select min(frr.effective_start_date) -1
from pay_formula_result_rules_f frr
where frr.status_processing_rule_id = p_status_processing_rule_id
and frr.result_rule_type = p_result_rule_type
and frr.result_name = p_result_name
and frr.input_value_id = p_input_value_id
and frr.formula_result_rule_id <> nvl(p_formula_result_rule_id,0)
and frr.effective_end_date >= p_session_date;
select 1
from dual
where p_result_name not in (select ffu.item_name
from ff_fdi_usages_f ffu
where ffu.formula_id = p_formula_id
and ffu.usage in ('O','B')
and effective_start_date <= p_effective_end_date
and effective_end_date >= p_effective_start_date
and (ffu.data_type = 'N'
or p_result_rule_type is null
or p_result_rule_type <> 'O'));