[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
BEGIN
DELETE FROM PAY_LINK_INPUT_VALUES_F
WHERE rowid = X_Rowid;
END Delete_Row;
SELECT *
FROM PAY_LINK_INPUT_VALUES_F
WHERE rowid = X_Rowid
FOR UPDATE of Link_Input_Value_Id NOWAIT;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Link_Input_Value_Id NUMBER,
X_Effective_Start_Date DATE,
X_Effective_End_Date DATE,
X_Element_Link_Id NUMBER,
X_Input_Value_Id NUMBER,
X_Costed_Flag VARCHAR2,
X_Default_Value VARCHAR2,
X_Max_Value VARCHAR2,
X_Min_Value VARCHAR2,
X_Warning_Or_Error VARCHAR2) IS
BEGIN
UPDATE PAY_LINK_INPUT_VALUES_F
SET
link_input_value_id = X_Link_Input_Value_Id,
effective_start_date = X_Effective_Start_Date,
effective_end_date = X_Effective_End_Date,
element_link_id = X_Element_Link_Id,
input_value_id = X_Input_Value_Id,
costed_flag = X_Costed_Flag,
default_value = X_Default_Value,
max_value = X_Max_Value,
min_value = X_Min_Value,
warning_or_error = X_Warning_Or_Error
WHERE rowid = X_rowid;
END Update_Row;
select *
from pay_input_values_f
where element_type_id = p_element_type_id
and effective_start_date <= p_effective_end_date
and effective_end_date >= p_effective_start_date
order by input_value_id,effective_start_date;
select pay_link_input_values_s.nextval
into v_link_input_value_id
from sys.dual;
insert into pay_link_input_values_f
(link_input_value_id,
effective_start_date,
effective_end_date,
element_link_id,
input_value_id,
costed_flag,
default_value,
max_value,
min_value,
warning_or_error,
creation_date)
values (
v_link_input_value_id,
v_effective_start_date,
v_effective_end_date,
p_element_link_id,
fetched_input_value.input_value_id,
v_costed_flag,
v_default_value,
v_max_value,
v_min_value,
v_warning_or_error,
sysdate);
select *
from pay_element_links_f
where element_type_id = p_element_type_id
and effective_start_date <= p_effective_end_date
and effective_end_date >= p_effective_start_date
order by element_link_id,effective_start_date;
select pay_link_input_values_s.nextval
into v_link_input_value_id
from sys.dual;
insert into pay_link_input_values_f
(link_input_value_id,
effective_start_date,
effective_end_date,
element_link_id,
input_value_id,
costed_flag,
default_value,
max_value,
min_value,
warning_or_error,
creation_date)
values(
v_link_input_value_id,
greatest(fetched_link.effective_start_date,p_effective_start_date),
least(fetched_link.effective_end_date,p_effective_end_date),
fetched_link.element_link_id,
p_input_value_id,
v_costed_flag,
v_default_value,
v_max_value,
v_min_value,
v_warning_or_error,
sysdate);
select 1
from pay_input_values_f TYPE,
pay_link_input_values_f LINK
where p_session_date between type.effective_start_date
and type.effective_end_date
and p_session_date between link.effective_start_date
and link.effective_end_date
and type.input_value_id = link.input_value_id
and link.element_link_id = p_element_link_id
and type.mandatory_flag = 'Y'
and ((type.hot_default_flag = 'N'
and link.default_value is null)
or (type.hot_default_flag = 'Y'
and nvl (link.default_value,
type.default_value) is null));
select 1
from pay_input_values_f
where input_value_id = p_input_value_id
and effective_start_date <=p_effective_end_date
and effective_end_date >=p_effective_start_date
and default_value is null;
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,
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_link_input_values_f.*
from pay_link_input_values_f
where p_parent_id = decode (p_parent_name,
'PAY_INPUT_VALUES_F',input_value_id,
'PAY_ELEMENT_LINKS_F',element_link_id)
for update;
hr_utility.set_location ('pay_link_input_values_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_row.effective_start_date > p_session_date) then
--
delete from pay_link_input_values_f
where current of csr_rows_owned_by_parent;
elsif p_delete_mode = 'DELETE'
and p_session_date between fetched_row.effective_start_date
and fetched_row.effective_end_date then
--
update pay_link_input_values_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_validation_end_date = c_end_of_time then
--
update pay_link_input_values_f
set effective_end_date = c_end_of_time
where current of csr_rows_owned_by_parent;
end parent_deleted;
select max(effective_end_date)
from pay_element_links_f
where element_link_id = p_link_id;