Query to Extract Department Trees in Oracle Cloud HCM
SELECT /*+ materialize */
DISTINCT *
FROM (
SELECT (
SELECT
haoufv_p.name
FROM
hr_all_organization_units_f_vl haoufv_p
WHERE
haoufv_p.organization_id = potnv.parent_organization_id
AND
TRUNC(SYSDATE) BETWEEN haoufv_p.effective_start_date AND haoufv_p.effective_end_date
) parent_org_name
,(
SELECT
haoufv_c.name
FROM
hr_all_organization_units_f_vl haoufv_c
WHERE
haoufv_c.organization_id = potnv.organization_id
AND
TRUNC(SYSDATE) BETWEEN haoufv_c.effective_start_date AND
haoufv_c.effective_end_date
) child_org_name
,potnv.tree_structure_code
,potnv.parent_organization_id parent_org_id
,potnv.organization_id child_org_id
,LEVEL levelcount
FROM
per_dept_tree_node_v potnv
, fnd_tree_version ftv
WHERE
potnv.tree_structure_code = 'PER_DEPT_TREE_STRUCTURE'
AND potnv.tree_code = '<XXXXX>'
AND
potnv.tree_version_id = ftv.tree_version_id
AND
ftv.tree_code = potnv.tree_code
AND
ftv.status = 'ACTIVE'
AND
TRUNC(SYSDATE) BETWEEN ftv.effective_start_date AND ftv.effective_end_date
START WITH potnv.parent_organization_id IS
NULL
CONNECT BY PRIOR potnv.organization_id =
potnv.parent_organization_id
)
ORDER BY
levelcount ASC
)
,dept_tree
AS (
SELECT /*+
materialize */
level1.child_org_name "level1"
,level2.child_org_name "level2"
,level3.child_org_name "level3"
,level4.child_org_name "level4"
FROM org_tree
level1
, org_tree level2
, org_tree level3
, org_tree level4
, hr_all_organization_units_f haouf
WHERE
level1.child_org_id = level2.parent_org_id
AND level2.child_org_id =
level3.parent_org_id
AND level3.child_org_id =
level4.parent_org_id
AND
level1.parent_org_name IS NULL
AND haouf.organization_id =
level4.child_org_id
AND TRUNC(SYSDATE) BETWEEN
haouf.effective_start_date AND haouf.effective_end_date
)
SELECT * FROM dept_tree
Query to get the Employee Hierarchy in the Organization
SELECT ppnf_emp.full_name
,pmhd.person_id
,papf_emp.person_number
,pmhd.assignment_id
,pmhd.manager_id
,papf_sup.person_number manager_number
,pmhd.manager_assignment_id
,pmhd.manager_level
,pmhd.manager_type
,pmhd.effective_start_date
,pmhd.effective_end_date
,decode(pmhd.manager_level, '1', 'Direct Reportee',
'Indirect Reportee') Direct_Indirect
FROM per_manager_hrchy_dn pmhd
,per_person_names_f_v ppnf_emp
,per_all_people_f papf_emp
,per_all_people_f papf_sup
,per_person_names_f_v ppnf_sup
WHERE 1 = 1
AND pmhd.manager_type = 'LINE_MANAGER'
AND pmhd.person_id = ppnf_emp.person_id
AND ppnf_emp.person_id = papf_emp.person_id
AND papf_sup.person_id = pmhd.manager_id
AND ppnf_sup.person_id = pmhd.manager_id
AND ppnf_emp.name_type = 'GLOBAL'
AND ppnf_sup.name_type = 'GLOBAL'
AND sysdate BETWEEN papf_emp.effective_start_date
AND papf_emp.effective_end_date
AND sysdate BETWEEN papf_sup.effective_start_date
AND papf_sup.effective_end_date
AND sysdate BETWEEN ppnf_emp.effective_start_date
AND ppnf_emp.effective_end_date
AND sysdate BETWEEN ppnf_sup.effective_start_date
AND ppnf_sup.effective_end_date
AND sysdate BETWEEN pmhd.effective_start_date
AND pmhd.effective_end_date
AND papf_sup.person_number = :MANAGER_PERSON_NUMBER
--AND pmhd.manager_level = '1' -- Use 1 for direct reports,
comment it for all reportees
ORDER BY papf_emp.person_number
Comments
Post a Comment