根据组织管理【部门】的【层级】字段查出来
WITH RECURSIVE dep_level_view AS (
select
eroad_index_id as cid,
tran_dep1.value as name,code,
ARRAY[tran_dep1.value] as name_list,
ARRAY[eroad_index_id] as path_ids,
FALSE as is_cycle
from hr_department
LEFT join ir_translation AS tran_dep1 ON tran_dep1.res_id = hr_department.id AND tran_dep1.lang='zh_CN' AND tran_dep1.name='hr.department,name'
where
eroad_index_id is not null and
eroad_start_date <= current_date and
(eroad_end_date >= current_date or eroad_end_date is null) and
not is_virtual_org and status = 'active' and active and
parent_department_id is NULL
UNION
SELECT
dep_table.eroad_index_id as cid,
tran_dep2.value as name,
dep_table.code,
dep_level_view.name_list || tran_dep2.value as name_list,
dep_level_view.path_ids || dep_table.eroad_index_id as path_ids,
dep_table.eroad_index_id = any(dep_level_view.path_ids) as is_cycle
FROM hr_department dep_table
LEFT join ir_translation AS tran_dep2 ON tran_dep2.res_id = dep_table.id AND tran_dep2.lang='zh_CN' AND tran_dep2.name='hr.department,name'
JOIN dep_level_view
ON dep_table.parent_department_id = dep_level_view.cid and not dep_level_view.is_cycle and
dep_table.eroad_index_id is not null and dep_table.eroad_start_date <= current_date and
(dep_table.eroad_end_date >= current_date or dep_table.eroad_end_date is null) and
not dep_table.is_virtual_org and dep_table.status = 'active' and dep_table.active
)
select
bb.*,
dep_level_view.name_list[1] AS "一级部门", – 一级部门
dep_level_view.name_list[2] AS "二级部门", – 二级部门
dep_level_view.name_list[3] AS "三级部门", – 三级部门
dep_level_view.name_list[4] AS "四级部门", – 四级部门
dep_level_view.name_list[5] AS "五级部门", – 五级部门
dep_level_view.path_ids
from (with emp_flow AS (
SELECT
emp.id AS employee_id,
emp.hire_date AS effective_date,
emp_job.business_unit_id,
emp_job.division_id,
emp_job.department_id,
emp_job.position_id,
'入职' AS event_type
FROM hr_employee emp
LEFT JOIN emp_job ON emp_job.id = emp.job_info_id
WHERE emp.active=TRUE AND emp.hire_date IS NOT NULL
UNION ALL
SELECT
emp.id AS employee_id,
emp.probation_date AS effective_date,
emp_job.business_unit_id,
emp_job.division_id,
emp_job.department_id,
emp_job.position_id,
'转正' AS event_type
FROM hr_employee emp
LEFT JOIN emp_job ON emp_job.id = emp.job_info_id
WHERE emp.active=TRUE AND emp.probation_date IS NOT NULL
UNION ALL
SELECT
emp.id AS employee_id,
emp.termination_date AS effective_date,
emp_job.business_unit_id,
emp_job.division_id,
emp_job.department_id,
emp_job.position_id,
'离职' AS event_type
FROM hr_employee emp
LEFT JOIN emp_job ON emp_job.id = emp.job_info_id
WHERE emp.active=TRUE AND emp.termination_date IS NOT NULL
UNION ALL
SELECT
transfer.employee_id,
transfer.effective_date,
emp_job.business_unit_id,
emp_job.division_id,
emp_job.department_id,
emp_job.position_id,
'调动' AS event_type
FROM job_transfer transfer
LEFT JOIN hr_employee emp ON transfer.employee_id = emp.id
LEFT JOIN emp_job ON emp_job.id = emp.job_info_id
WHERE
transfer.x_bp_id IS NOT NULL AND transfer.x_bp_state = 'done' AND transfer.is_adjust_position
)
SELECT
emp_flow.employee_id,
emp_flow.effective_date,
emp_flow.business_unit_id,
bu.name AS bu_name,
emp_flow.division_id,
division.name AS division_name,
emp_flow.department_id,
tran_dep.value AS dep_name,
tran_level.value AS organization,
emp_flow.position_id,
position.name AS position_name,
emp_flow.event_type
FROM emp_flow
LEFT JOIN hr_business_unit bu ON bu.id = emp_flow.business_unit_id AND bu.active
LEFT JOIN hr_division division ON division.id = emp_flow.division_id AND division.active
LEFT JOIN hr_department dep ON dep.id = emp_flow.department_id AND dep.active
LEFT join ir_translation AS tran_level ON tran_level.res_id = dep.organization_level_id AND tran_level.lang='zh_CN' AND tran_level.name='res.selection,name'
LEFT join ir_translation AS tran_dep ON tran_dep.res_id = dep.id AND tran_dep.lang='zh_CN' AND tran_dep.name='hr.department,name'
LEFT JOIN hr_position position ON position.id = emp_flow.position_id AND position.active) bb
LEFT JOIN hr_employee emp on emp.id=bb.employee_id
LEFT JOIN emp_job on emp_job.id = emp.job_info_id
LEFT JOIN dep_level_view ON dep_level_view.cid = emp_job.department_id
Medium 
刘文强
丁友鹏