The following lines contain the word 'select', 'insert', 'update' or 'delete':
select nvl(max(psv.version_number),0) + 1
into l_next_free_no
from per_pos_structure_versions psv
where psv.Position_Structure_Id = X_Position_Structure_Id;
select null
into l_dummy
from sys.dual
where exists (select 1
from per_pos_structure_versions psv
where psv.position_structure_id = X_Position_Structure_Id
and psv.version_number = X_Version_Number
and (psv.rowid <> X_Rowid
or X_Rowid is null));
select max(psv.date_to)
into l_max_end_date
from per_pos_structure_versions psv
where psv.date_from < X_Date_From
--
-- Bug 608815: add missing clause to restrict the hierarchy, or the code
-- looks for the max date across all hierarchies. RMF 09-Jan-98.
--
and psv.position_structure_id = X_Position_Structure_Id
and (psv.rowid <> X_Rowid
or X_Rowid is null);
select min(psv.date_from)
into l_min_start_date
from per_pos_structure_versions psv
where psv.position_structure_id = X_Position_Structure_Id
and psv.date_from > X_Date_To
and (psv.rowid <> X_Rowid
or psv.rowid is null);
select null
into l_dummy
from sys.dual
where exists
(select 1
from per_pos_structure_versions psv
where psv.position_structure_id = X_Position_Structure_Id
and X_Date_From > psv.date_from
and psv.date_to is null);
update per_pos_structure_versions psv
set psv.date_to = (X_date_from - 1)
where psv.position_structure_id = X_Position_Structure_Id
and psv.date_to is null
and (psv.rowid <> X_Rowid
or X_Rowid is null);
select null
into l_dummy
from sys.dual
where exists
(select 1
from per_pos_structure_versions psv
where psv.date_from <= nvl(X_Date_To, X_End_Of_Time)
and nvl(psv.date_to,X_End_Of_Time) >= X_Date_From
and psv.position_structure_id = X_Position_Structure_Id
and (psv.rowid <> X_Rowid
or X_Rowid is null));
select *
from per_pos_structure_elements pse
where pse.pos_structure_version_id = X_Copy_Structure_Version_Id;
INSERT INTO PER_POS_STRUCTURE_ELEMENTS(
pos_structure_element_id,
business_group_id,
pos_structure_version_id,
subordinate_position_id,
parent_position_id
)
select per_pos_structure_elements_s.nextval,
business_group_id,
x_pos_structure_version_id,
subordinate_position_id,
parent_position_id
from per_pos_structure_elements pse
where pse.pos_structure_version_id = X_Copy_Structure_Version_Id;
PER_POS_STRUCTURE_ELEMENTS_PKG.Insert_Row(
X_Rowid => l_Rowid
,X_Pos_Structure_Element_Id =>l_Structure_element_id
,X_Business_Group_Id => ele_record.Business_Group_Id
,X_Pos_Structure_Version_Id => X_Pos_Structure_Version_Id
,X_Subordinate_Position_Id => ele_record.Subordinate_Position_Id
,X_Parent_Position_Id => ele_record.Parent_Position_Id);
PROCEDURE pre_delete_checks(X_Pos_Structure_Version_Id NUMBER,
X_Business_Group_Id NUMBER,
X_Position_Structure_Id NUMBER,
X_Hr_Installed VARCHAR2) IS
--
-- Local Variable
--
l_dummy VARCHAR2(1);
select null
into l_dummy
from sys.dual
where exists( select null
from PER_POS_STRUCTURE_ELEMENTS PSE
where PSE.POS_STRUCTURE_VERSION_ID = X_Pos_Structure_Version_Id);
select null
into l_dummy
from sys.dual
where exists(select null
from per_security_profiles
where business_group_id + 0 = X_Business_Group_Id
and position_structure_id = X_Position_Structure_Id);
end pre_delete_checks;
PROCEDURE update_copies(X_Pos_Structure_Version_Id NUMBER) IS
--
--
--
cursor ele_update is
select rowid
from per_pos_structure_versions psv
where psv.copy_structure_version_id = X_Pos_Structure_Version_Id
for update of psv.copy_structure_version_id nowait;
open ele_update;
fetch ele_update into l_Rowid;
exit when ele_update%NOTFOUND;
update per_pos_structure_versions psv
set psv.copy_structure_version_id = NULL
where psv.rowid = l_Rowid;
fetch ele_update into l_Rowid;
close ele_update;
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Pos_Structure_Version_Id IN OUT NOCOPY NUMBER,
X_Business_Group_Id NUMBER,
X_Position_Structure_Id NUMBER,
X_Date_From DATE,
X_Version_Number NUMBER,
X_Copy_Structure_Version_Id NUMBER ,
X_Date_To DATE ,
X_end_of_time DATE,
X_Next_no_free IN OUT NOCOPY NUMBER,
X_closedown_warning IN OUT NOCOPY VARCHAR2,
X_gap_warning IN OUT NOCOPY VARCHAR2
) IS
CURSOR C IS SELECT rowid FROM PER_POS_STRUCTURE_VERSIONS
WHERE pos_structure_version_id = X_Pos_Structure_Version_Id;
CURSOR C2 IS SELECT per_pos_structure_versions_s.nextval FROM sys.dual;
INSERT INTO PER_POS_STRUCTURE_VERSIONS(
pos_structure_version_id,
business_group_id,
position_structure_id,
date_from,
version_number,
copy_structure_version_id,
date_to
) VALUES (
X_Pos_Structure_Version_Id,
X_Business_Group_Id,
X_Position_Structure_Id,
X_Date_From,
X_Version_Number,
X_Copy_Structure_Version_Id,
X_Date_To
);
end Insert_Row;
SELECT *
FROM PER_POS_STRUCTURE_VERSIONS
WHERE rowid = X_Rowid
FOR UPDATE of Pos_Structure_Version_Id NOWAIT;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Pos_Structure_Version_Id NUMBER,
X_Business_Group_Id NUMBER,
X_Position_Structure_Id NUMBER,
X_Date_From DATE,
X_Version_Number NUMBER,
X_Copy_Structure_Version_Id NUMBER ,
X_Date_To DATE ,
X_end_of_time DATE,
X_Next_no_free IN OUT NOCOPY NUMBER,
X_closedown_warning IN OUT NOCOPY VARCHAR2,
X_gap_warning IN OUT NOCOPY VARCHAR2
) IS
BEGIN
PER_POS_STRUCTURE_VERSIONS_PKG.check_version_number(
X_Position_Structure_Id=> X_Position_Structure_Id
,X_Version_Number => X_Version_Number
,X_Rowid => X_Rowid);
UPDATE PER_POS_STRUCTURE_VERSIONS
SET
pos_structure_version_id = X_Pos_Structure_Version_Id,
business_group_id = X_Business_Group_Id,
position_structure_id = X_Position_Structure_Id,
date_from = X_Date_From,
version_number = X_Version_Number,
copy_structure_version_id = X_Copy_Structure_Version_Id,
date_to = X_Date_To
WHERE rowid = X_rowid;
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2,
X_Pos_Structure_Version_Id NUMBER,
X_Business_Group_Id NUMBER,
X_Position_Structure_Id NUMBER,
X_Hr_Installed VARCHAR2,
X_Next_no_free IN OUT NOCOPY NUMBER,
X_closedown_warning IN OUT NOCOPY VARCHAR2) IS
BEGIN
pre_delete_checks(X_Pos_Structure_Version_Id => X_Pos_Structure_Version_Id,
X_Business_Group_Id => X_Business_Group_Id,
X_Position_Structure_Id => X_Position_Structure_Id,
X_Hr_Installed => X_Hr_Installed);
DELETE FROM PER_POS_STRUCTURE_VERSIONS
WHERE rowid = X_Rowid;
END Delete_Row;