[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select nvl( count(pse.pos_structure_element_id), 0)
into l_count
from per_pos_structure_elements pse
where ((X_View_All_Positions <> 'Y'
and EXISTS
(select '1'
from hr_positions_f hpf
where hpf.position_id = pse.subordinate_position_id
))
or X_View_All_Positions = 'Y')
connect by
prior pse.subordinate_position_id = pse.parent_position_id
and pse.pos_structure_version_id = X_Pos_structure_version_id
start with
pse.parent_position_id = X_Parent_Position_Id
and pse.pos_structure_version_id = X_Pos_structure_version_id;
select COUNT(DISTINCT P.PERSON_ID), '** ' || COUNT(DISTINCT P.PERSON_ID) ||' '||l_message
into X_No_Holders , l_message
from per_all_people_f p
, per_all_assignments_f a
where a.position_id = X_Position_Id
and a.business_group_id + 0 = X_Business_Group_Id
and a.organization_id = X_Organization_id
and a.assignment_type in ('E', 'C')
and a.person_id = p.person_id
and exists (select ppt.system_person_type
from per_person_types ppt, per_person_type_usages_f pptu
where pptu.person_id = p.person_id
and ppt.person_type_id = pptu.person_type_id
and ppt.system_person_type in ('EMP','CWK')
and X_session_date between pptu.effective_start_date
AND pptu.effective_end_date)
and exists (select past.per_system_status
from per_assignment_status_types past
where past.assignment_status_type_id = a.assignment_status_type_id
and past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN','ACTIVE_CWK','SUSP_CWK_ASG'))
and X_Session_date between a.effective_start_date
and a.effective_end_date
and X_Session_date between p.effective_start_date
and p.effective_end_date;
select DISTINCT p.full_name
,hr_person_type_usage_info.get_worker_number
(X_Session_date, p.person_id) worker_number
,hr_person_type_usage_info.get_worker_user_person_type
(X_Session_date, p.person_id) user_person_type
into X_Holder
,X_Employee_Number
,X_User_Person_type
from per_all_people_f p
,per_all_assignments_f a
where a.position_id = X_Position_Id
and a.business_group_id = X_Business_Group_Id
and a.organization_id = X_Organization_id
and a.assignment_type in ('E', 'C')
and a.person_id = p.person_id
and exists
(select ppt.system_person_type
from per_person_types ppt, per_person_type_usages_f ptu
where ptu.person_id = p.person_id
and ppt.person_type_id = ptu.person_type_id
and ppt.system_person_type in ('EMP', 'CWK')
and X_session_date between ptu.effective_start_date and ptu.effective_end_date)
and exists
(select past.per_system_status
from per_assignment_status_types past
where past.assignment_status_type_id
= a.assignment_status_type_id
and past.per_system_status in
('ACTIVE_ASSIGN','SUSP_ASSIGN'
,'ACTIVE_CWK', 'SUSP_CWK_ASG'))
and X_Session_date between a.effective_start_date
and a.effective_end_date
and X_Session_date between p.effective_start_date
and p.effective_end_date;
select p.organization_id
into X_Organization_Id
from hr_all_positions p
where p.position_id = X_Position_Id
and p.business_group_id + 0 = X_Business_Group_Id;
select null
into l_exists
from sys.dual
where X_Position_Id in
(select a.subordinate_position_id
from per_POS_structure_elements a
where a.business_group_id + 0 = X_Business_Group_Id
and a.POS_structure_VERSION_id =
X_Sec_Pos_Structure_Version_id
connect by prior
a.subordinate_position_id = a.parent_position_id
and a.POS_structure_VERSION_id =
X_Sec_Pos_Structure_Version_id
and a.business_group_id + 0 = X_Business_Group_Id
start with
a.parent_position_id = X_Position_Id
and a.POS_structure_VERSION_id =
X_Sec_Pos_Structure_Version_id
and a.business_group_id + 0 = X_Business_Group_Id);
select x.view_all_POSITIONs_flag
, x.POSITION_id
, x.include_top_POSITION_flag
into l_view_all_positions
, l_sec_pos_id
, l_include_top_pos_flag
from per_security_profiles x
where x.business_group_id + 0 = X_Business_Group_Id
and x.security_profile_id = X_Security_Profile_Id;
select null
into l_dummy
FROM PER_POS_STRUCTURE_ELEMENTS PSE
WHERE PSE.POS_STRUCTURE_VERSION_ID = X_Pos_Structure_Version_Id
AND PSE.PARENT_POSITION_ID = X_Parent_position_id
AND PSE.SUBORDINATE_POSITION_ID = X_Subordinate_Position_Id;
hr_utility.set_message('801','HR_6012_ROW_INSERTED');
PROCEDURE pre_delete_checks(X_Subordinate_position_Id NUMBER
,X_Position_Structure_Id NUMBER
,X_Business_Group_Id NUMBER
,X_Hr_Installed VARCHAR2
,X_Pos_Structure_version_Id NUMBER) IS
l_dummy VARCHAR2(1);
select null
into l_dummy
from sys.dual
where exists(select 1
from per_pos_structure_elements pse
where pse.parent_position_id = X_Subordinate_position_Id
and pse.pos_structure_version_id = X_Pos_Structure_version_Id);
select null
into l_dummy
from sys.dual
where exists(select 1
from per_security_profiles psp
where psp.business_group_id + 0 = X_Business_Group_Id
and psp.position_id = X_Subordinate_position_Id
and psp.position_structure_id = X_Position_Structure_Id);
end pre_delete_checks;
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Pos_Structure_Element_Id IN OUT NOCOPY NUMBER,
X_Business_Group_Id NUMBER,
X_Pos_Structure_Version_Id NUMBER,
X_Subordinate_Position_Id NUMBER,
X_Parent_Position_Id NUMBER
) IS
CURSOR C IS SELECT rowid FROM PER_POS_STRUCTURE_ELEMENTS
WHERE pos_structure_element_id = X_Pos_Structure_Element_Id;
CURSOR C2 IS SELECT per_pos_structure_elements_s.nextval FROM sys.dual;
cursor get_details is select rowid, POS_STRUCTURE_ELEMENT_ID
,BUSINESS_GROUP_ID, POS_STRUCTURE_VERSION_ID
,SUBORDINATE_POSITION_ID,PARENT_POSITION_ID
FROM PER_POS_STRUCTURE_ELEMENTS
where Subordinate_Position_Id = X_Subordinate_Position_Id
and Pos_Structure_Version_Id = X_Pos_Structure_Version_Id;
PER_POS_STRUCTURE_ELEMENTS_PKG.update_row(
X_Rowid => Recinfo.ROWID,
X_Pos_Structure_Element_Id => Recinfo.POS_STRUCTURE_ELEMENT_ID,
X_Business_Group_Id => Recinfo.BUSINESS_GROUP_ID,
X_Pos_Structure_Version_Id => Recinfo.POS_STRUCTURE_VERSION_ID,
X_Subordinate_Position_Id => Recinfo.SUBORDINATE_POSITION_ID,
X_Parent_Position_Id => X_Parent_Position_Id);
INSERT INTO PER_POS_STRUCTURE_ELEMENTS(
pos_structure_element_id,
business_group_id,
pos_structure_version_id,
subordinate_position_id,
parent_position_id
) VALUES (
X_Pos_Structure_Element_Id,
X_Business_Group_Id,
X_Pos_Structure_Version_Id,
X_Subordinate_Position_Id,
X_Parent_Position_Id
);
END Insert_Row;
SELECT *
FROM PER_POS_STRUCTURE_ELEMENTS
WHERE rowid = X_Rowid
FOR UPDATE of Pos_Structure_Element_Id NOWAIT;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Pos_Structure_Element_Id NUMBER,
X_Business_Group_Id NUMBER,
X_Pos_Structure_Version_Id NUMBER,
X_Subordinate_Position_Id NUMBER,
X_Parent_Position_Id NUMBER
) IS
BEGIN
UPDATE PER_POS_STRUCTURE_ELEMENTS
SET
pos_structure_element_id = X_Pos_Structure_Element_Id,
business_group_id = X_Business_Group_Id,
pos_structure_version_id = X_Pos_Structure_Version_Id,
subordinate_position_id = X_Subordinate_Position_Id,
parent_position_id = X_Parent_Position_Id
WHERE rowid = X_rowid;
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2
,X_Subordinate_position_Id NUMBER
,X_Position_Structure_Id NUMBER
,X_Business_Group_Id NUMBER
,X_Hr_Installed VARCHAR2
,X_Pos_Structure_version_Id NUMBER) IS
BEGIN
--
-- Do the Pre-delete checks
--
PER_POS_STRUCTURE_ELEMENTS_PKG.pre_delete_checks(
X_Subordinate_position_Id => X_Subordinate_position_Id
,X_Position_Structure_Id => X_Position_Structure_Id
,X_Business_Group_Id => X_Business_Group_Id
,X_Hr_Installed => X_Hr_Installed
,X_Pos_Structure_version_Id => X_Pos_Structure_version_Id);
DELETE FROM PER_POS_STRUCTURE_ELEMENTS
WHERE rowid = X_Rowid;
END Delete_Row;