[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Status_Processing_Rule_Id IN OUT NOCOPY NUMBER,
X_Effective_Start_Date DATE,
X_Effective_End_Date DATE,
X_Business_Group_Id NUMBER,
X_Legislation_Code VARCHAR2,
X_Element_Type_Id NUMBER,
X_Assignment_Status_Type_Id NUMBER,
X_Formula_Id NUMBER,
X_Processing_Rule VARCHAR2,
X_Comment_Id NUMBER,
X_Legislation_Subgroup VARCHAR2,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER,
X_Created_By NUMBER,
X_Creation_Date DATE) IS
CURSOR C IS SELECT rowid FROM pay_status_processing_rules_f
WHERE status_processing_rule_id= X_status_processing_rule_id
AND effective_start_date = X_Effective_Start_Date;
CURSOR C2 IS SELECT pay_status_processing_rules_s.nextval FROM sys.dual;
INSERT INTO pay_status_processing_rules_f(
status_processing_rule_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
element_type_id,
assignment_status_type_id,
formula_id,
processing_rule,
comment_id,
legislation_subgroup,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date
) VALUES (
X_Status_Processing_Rule_Id,
X_Effective_Start_Date,
X_Effective_End_Date,
X_Business_Group_Id,
X_Legislation_Code,
X_Element_Type_Id,
X_Assignment_Status_Type_Id,
X_Formula_Id,
X_Processing_Rule,
X_Comment_Id,
X_Legislation_Subgroup,
X_Last_Update_Date,
X_Last_Updated_By,
X_Last_Update_Login,
X_Created_By,
X_Creation_Date
);
END Insert_Row;
SELECT *
FROM pay_status_processing_rules_f
WHERE rowid = X_Rowid
FOR UPDATE of status_processing_rule_id NOWAIT;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Status_Processing_Rule_Id NUMBER,
X_Effective_Start_Date DATE,
X_Effective_End_Date DATE,
X_Business_Group_Id NUMBER,
X_Legislation_Code VARCHAR2,
X_Element_Type_Id NUMBER,
X_Assignment_Status_Type_Id NUMBER,
X_Formula_Id NUMBER,
X_Processing_Rule VARCHAR2,
X_Comment_Id NUMBER,
X_Legislation_Subgroup VARCHAR2,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER
) IS
BEGIN
UPDATE pay_status_processing_rules_f
SET
status_processing_rule_id = X_Status_Processing_Rule_Id,
effective_start_date = X_Effective_Start_Date,
effective_end_date = X_Effective_End_Date,
business_group_id = X_Business_Group_Id,
legislation_code = X_Legislation_Code,
element_type_id = X_Element_Type_Id,
assignment_status_type_id = X_Assignment_Status_Type_Id,
formula_id = X_Formula_Id,
processing_rule = X_Processing_Rule,
comment_id = X_Comment_Id,
legislation_subgroup = X_Legislation_Subgroup,
last_update_date = X_Last_Update_Date,
last_updated_by = X_Last_Updated_By,
last_update_login = X_Last_Update_Login
WHERE rowid = X_rowid;
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2,
p_session_date date,
p_delete_mode varchar2,
p_status_processing_rule_id number) IS
BEGIN
DELETE FROM pay_status_processing_rules_f
WHERE rowid = X_Rowid;
pay_formula_result_rules_pkg.parent_deleted (
--
'PAY_STATUS_PROCESSING_RULES_F',
p_status_processing_rule_id,
p_session_date,
p_delete_mode );
END Delete_Row;
select max(effective_end_date)
from pay_status_processing_rules_f
where status_processing_rule_id = p_status_processing_rule_id;
select min(effective_start_date) -1
from pay_status_processing_rules_f
where status_processing_rule_id = p_status_processing_rule_id
and formula_id <> p_formula_id;
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 *
-- The value of the foreign key for the deleted parent
p_element_type_id number,
--
-- The date of date-effective deletion
p_session_date date default trunc (sysdate),
--
-- The type of deletion action being performed
p_delete_mode varchar2 default 'DELETE'
--
) is
--
-- The following cursor fetches all rows identified by the foreign key to
-- the parent being deleted.
--
cursor csr_rows_owned_by_parent is
select *
from pay_status_processing_rules_f
where element_type_id = p_element_type_id
for update;
hr_utility.set_location ('PAY_STATUS_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
--
hr_utility.set_location ('PAY_STATUS_RULES_PKG.PARENT_DELETED',2);
delete from pay_status_processing_rules_f
where current of csr_rows_owned_by_parent;
hr_utility.set_location ('PAY_STATUS_RULES_PKG.PARENT_DELETED',3);
delete from hr_application_ownerships
where key_name = 'STATUS_PROCESSING_RULE_ID'
and key_value = fetched_rule.status_processing_rule_id;
elsif p_delete_mode = 'DELETE'
and p_session_date between fetched_rule.effective_start_date
and fetched_rule.effective_end_date then
--
hr_utility.set_location ('PAY_STATUS_RULES_PKG.PARENT_DELETED',4);
update pay_status_processing_rules_f
set effective_end_date = p_session_date
where current of csr_rows_owned_by_parent;
hr_utility.set_location ('PAY_STATUS_RULES_PKG.PARENT_DELETED',5);
pay_formula_result_rules_pkg.parent_deleted (
--
'PAY_STATUS_PROCESSING_RULES_F',
fetched_rule.status_processing_rule_id,
p_session_date,
p_delete_mode );
end parent_deleted;
select count( distinct iv.name)
from pay_input_values_f_tl IV_TL,
pay_input_values_f IV
where iv_tl.input_value_id = iv.input_value_id
and iv.element_type_id = p_element_type_id
and userenv('LANG') = iv_tl.language
and translate(upper(iv_tl.name),' ','_') in
(select item_name from ff_fdi_usages_f
where formula_id = p_formula_id);
select count(distinct fdi.ITEM_NAME)
from pay_input_values_f_tl IV_TL,
pay_input_values_f IV,
ff_fdi_usages_f FDI
where iv_tl.input_value_id = iv.input_value_id
and fdi.formula_id = p_formula_id
and userenv('LANG') = iv_tl.language
and fdi.usage in ( 'I', 'B' ) -- either input or in/output item
and iv.element_type_id = p_element_type_id
and translate (upper(iv_tl.name),' ','_')
= translate (upper(fdi.item_name),' ','_')
and ((fdi.data_type = 'D' and iv.uom = 'D')
or (fdi.data_type = 'T' and iv.uom = 'C')
or (fdi.data_type = 'N'
and substr(iv.uom,1,1) in ('H','I','M','N')));
function DATE_EFFECTIVELY_UPDATED (
--
--******************************************************************************
--* Returns TRUE if the record has more than one date-effective row
--******************************************************************************
--
p_status_processing_rule_id number,
p_rowid varchar2) return boolean is
--
cursor csr_dated_updates is
select 1
from pay_status_processing_rules_f
where status_processing_rule_id = p_status_processing_rule_id
and rowid <> p_rowid;
date_effective_updates_exist boolean := FALSE;
hr_utility.set_location ('PAY_STATUS_RULES_PKG.DATE_EFFECTIVELY_UPDATED',1);
open csr_dated_updates;
fetch csr_dated_updates into g_dummy;
date_effective_updates_exist := csr_dated_updates%found;
close csr_dated_updates;
return date_effective_updates_exist;
end date_effectively_updated;
select 1
from pay_formula_result_rules_f
where status_processing_rule_id = p_status_processing_rule_id
and effective_start_date <= p_end_date
and effective_end_date >= p_start_date;
select min(sprf.effective_start_date)
into v_next_status_rule_start_date
from pay_status_processing_rules_f sprf
where sprf.element_type_id = p_element_type_id
and nvl(sprf.assignment_status_type_id,0) = nvl(p_assignment_status_type_id,0)
and sprf.processing_rule = p_processing_rule
and sprf.effective_end_date >= p_session_date
and sprf.status_processing_rule_id <> nvl(p_status_processing_rule_id,0)
and (
--
-- The row on the database is 'Generic'
--
(sprf.business_group_id is null
and sprf.legislation_code is null)
--
-- The row to be inserted is 'Generic'
--
or (p_business_group_id is null
and p_legislation_code is null)
--
-- The bg of the row to be inserted conflicts with the bg
-- of an existing row or an existing legislation row with
-- the same legislation as the bg of the row being inserted.
--
or (p_business_group_id is not null
and (nvl(sprf.business_group_id,-1) = p_business_group_id
or nvl(sprf.legislation_code,'~') = p_legislation_code))
--
-- The legislation of the row to be inserted conflicts with an
-- existing legislative row or with the legislation of an existing
-- bg specific row.
--
or (p_business_group_id is null
and p_legislation_code is not null
and (p_legislation_code = nvl(sprf.legislation_code,'~')
or p_legislation_code = (select legislation_code
from per_business_groups
where business_group_id = nvl(sprf.business_group_id,-1))))
);
select max(ff.effective_end_date)
into v_max_formula_end_date
from ff_formulas_f ff
where ff.formula_id = p_formula_id;