Employee Org Chart / Tree
References
The purpose of this post is to discuss how to create an organizational (org) chart using iDempiere's Tree functionality. The result is a collection of employees/departments in tree structure similar to how Organizations appear in the Organization window. This enhancement also creates a Personnel Action Form (PAF) where employe records are limited to those managers above the employee in the Org Chart.
Please note that this is not a perfect solution. We bend the rules a little to get what we want.
Usage Notes:
- The Employee Org Chart window shows all active Business Partner records where Employee=Y
- You can create Departments just like you do people; however, name them as "Department". Example "IT Department"
- Refreshing the tree after you add a new employee is a little awkward. You must use the subtab "Update Tree" button to update the tree. Then you must close and re-open the Employe Org Chart window.
- When you deactivate an employee or department, the records below that employee are put to the bottom of the tree. It is better to move subordinates before remove the employee or department.
- The Personnel Action Form window shows recommended or actioned changes to employee compensation or employment. You can note or attach change details. Only managers of employees can see their employee records.
Here are the steps to install:
- Execute the below SQL (create views)
- Log in as System Admin role and Pack In this file.
- Log into your client (GWAdmin for example) and Pack In this file.
- Open the Employee Org Chart window and click the Update Tree button to rebuild the tree with missing entries. Close and reopen the window. Note: the tree is not automatically refreshed after clicking the Update Tree button.
- Below are the cryptic notes in case the pack in files do not work. It is probably that they will not work on 4.1 or below because of some tree changes in 5.1.
create or replace view chuboe_employee_orgchart_v as
select
bp.ad_client_id,
bp.ad_org_id,
bp.created,
bp.createdby,
bp.updated,
bp.updatedby,
bp.name,
'Y' as issummary,
bp.c_bpartner_id as chuboe_employee_orgchart_v_id,
bp.c_bpartner_uu as chuboe_employee_orgchart_v_uu,
'Y' as isactive,
bp.description
from c_bpartner bp
where bp.isemployee='Y'
and bp.isactive='Y';
CREATE or Replace VIEW chuboe_treenode_children AS
WITH RECURSIVE included_nodes(ad_client_id, ad_tree_id, node_id, parent_id, orig_id) AS (
SELECT ad_treenode.ad_client_id,
ad_treenode.ad_tree_id,
ad_treenode.node_id,
ad_treenode.parent_id,
ad_treenode.parent_id AS orig_id,
0 AS depth,
(((ad_treenode.parent_id || '\'::text) || ad_treenode.node_id::character varying(10)::text))::character varying(4000) AS hierarchicalpath
FROM ad_treenode
WHERE ad_treenode.parent_id > 0::numeric
UNION ALL
SELECT p.ad_client_id,
p.ad_tree_id,
p.node_id,
p.parent_id,
pr.orig_id,
pr.depth + 1,
(((pr.hierarchicalpath::text || '\'::text) || p.node_id::character varying(10)::text))::character varying(4000) AS "varchar"
FROM included_nodes pr,
ad_treenode p
WHERE p.parent_id = pr.node_id AND p.ad_tree_id = pr.ad_tree_id
)
SELECT included_nodes.ad_client_id,
included_nodes.ad_tree_id,
included_nodes.node_id,
included_nodes.parent_id,
included_nodes.orig_id,
included_nodes.depth,
included_nodes.hierarchicalpath
FROM included_nodes;
Cryptic Notes:
- Execute view
- Create T&C with flag as view - import columns
- Remove T&C view flag (needed to have tree)
- Create window (flag tab as has tree) and menu entry
- Create client => Tree record as custom table and 'all nodes'
- Add tree record as subtab to allow user to verify tree (tab sql where: lower(name) like '%employee%')
SQL to Show in Order or Hierachy
- Note: that each reporting level shows on a different record. This will duplicate rows.
select c.*, bpn.name,lpad('', depth*6, ' ') || bpn.name::varchar as indent_name
from chuboe_treenode_children c
join c_bpartner bpn on c.node_id = bpn.c_bpartner_id
where c.ad_tree_id = 1000009
order by hierarchicalpath
;
References: