Many clients I have worked for have expressed an interest in a graphical organization chart generated from their E-Business Suite ERP system. Unfortunately the org chart diagrammer in EBS isn’t really all that great.
And the strategic Oracle reporting tools (Business Intelligence etc) are even worse!
It’s actually surprisingly difficult to render a tree structure – it can be done purely using lists and CSS, and whilst extremely powerful, it can become difficult to manage for a beginner. So I thought I’d present another option – using Google Charts Organization Chart API (which I suspect is just a wrapper around HTML lists anyway). I will do this for now in Oracle APEX however the principle applies equally to other technologies (I will possibly post follow-ups with OBIEE etc in the future).
So how does the API work? Basically we include the API on our page and then make calls to the various methods provided. The developer page has a simple example that we can use as our starting point. Please take a quick look over there before continuing reading.
Ok, so let’s start off. First of all we will create a new APEX desktop application. I am using the very latest APEX 5.1.1.00.087 build, however this should apply to most recent versions.
Next we need the query which will generate the hierarchy. The nice thing about this is that Google charts does that for you if you provide parent/child tuples, so there is no need to traverse the hierarchy ourselves. The following simple query gives us the current primary organization structure records.
Select org_parent.name parent, org_child.name child From per_organization_structures pos, per_org_structure_versions posv, per_org_structure_elements pose, per_business_groups pbg, hr_all_organization_units org_parent, hr_all_organization_units org_child Where posv.organization_structure_id = pos.organization_structure_id And pose.org_structure_version_id = posv.org_structure_version_id And pbg.business_group_id = pos.business_group_id And org_parent.organization_id = pose.organization_id_parent And org_child.organization_id = pose.organization_id_child And Trunc(Sysdate) >= posv.date_from And (Trunc(Sysdate) <= posv.date_to Or posv.date_to Is Null) And pos.primary_structure_flag = 'Y' And pbg.name = 'Vision Corporation';
Now we’ll create a region to display our org chart. The demo on Google goes this by placing the API calls in the Head section of the page, however we can equally do it in the Body section. There are a number of different ways to pushing the data to the API – looping through the rows, as a JSON structure etc – to keep it simple I’m going to use the former and PL/SQL Dynamic Content region. Including the API calls, and calls to Htp.P to output the HTML, we get something along the lines of this as a starter for ten.
Begin
Htp.P(‘<div id=”chart_div”></div>’);
Htp.P(
q'[
<script type=”text/javascript” src=”https://www.gstatic.com/charts/loader.js”></script>
<script type=”text/javascript”>
google.charts.load(‘current’, {packages:[“orgchart”]});
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
var data = new google.visualization.DataTable();
data.addColumn(‘string’, ‘Name’);
data.addColumn(‘string’, ‘Manager’);
data.addColumn(‘string’, ‘ToolTip’);
data.addRows([
]’
);
For i In (
Select org_parent.name parent, org_child.name child,
Case When Count(*) Over () = Row_Number() Over (Order By 1) Then ‘Y’ End last_row
From per_organization_structures pos,
per_org_structure_versions posv,
per_org_structure_elements pose,
per_business_groups pbg,
hr_all_organization_units org_parent,
hr_all_organization_units org_child
Where posv.organization_structure_id = pos.organization_structure_id
And pose.org_structure_version_id = posv.org_structure_version_id
And pbg.business_group_id = pos.business_group_id
And org_parent.organization_id = pose.organization_id_parent
And org_child.organization_id = pose.organization_id_child
And Trunc(Sysdate) >= posv.date_from
And (Trunc(Sysdate) <= posv.date_to Or posv.date_to Is Null)
And pos.primary_structure_flag = ‘Y’
And pbg.name = ‘Vision Corporation’ )
Loop
Htp.P(‘[”’ || i.child || ”’,”’ || i.parent || ”’,””]’ || Case When i.last_row Is Null Then ‘,’ End);
End Loop;
Htp.P(
q'[
]);
var chart = new google.visualization.OrgChart(document.getElementById(‘chart_div’));
chart.draw(data, {allowHtml:true});
}
</script>
]’
);
End;
Which we then add as the region source. Note we have added an extra column in our query that flags the “last row” – this is so we know not to append a comma to the final record.
Now run our application and we have our full org chart.
Note though that it is very wide, however that is simply because we are working with the entire structure of the organization. We can easily alter our query to return just a sub-branch such as Sales. This requires us to traverse the hierarchy though because we need to ensure we retrieve all rows under Sales, not just direct subordinates. I’d always recommend doing some level of filtering like this to protect against future expansion.
Select org_parent.name parent, org_child.name child From per_organization_structures pos, per_org_structure_versions posv, per_org_structure_elements pose, per_business_groups pbg, hr_all_organization_units org_parent, hr_all_organization_units org_child Where posv.organization_structure_id = pos.organization_structure_id And pose.org_structure_version_id = posv.org_structure_version_id And pbg.business_group_id = pos.business_group_id And org_parent.organization_id = pose.organization_id_parent And org_child.organization_id = pose.organization_id_child And Trunc(Sysdate) >= posv.date_from And (Trunc(Sysdate) <= posv.date_to Or posv.date_to Is Null) And pos.primary_structure_flag = 'Y' And pbg.name = 'Vision Corporation' And pose.organization_id_child In ( Select pose1.organization_id_child From per_org_structure_elements pose1 Where pose1.org_structure_version_id = pose.org_structure_version_id Start With pose1.organization_id_parent = (Select h.organization_id From hr_all_organization_units h Where name='Sales' And h.business_group_id = pose1.business_group_id) Connect By Prior pose1.organization_id_child = pose1.organization_id_parent And Prior pose1.org_structure_version_id = pose1.org_structure_version_id );
It is of course trivial to include HTML links etc on there that will branch off elsewhere, as well as the Google Charts supported things such as tooltips and HTML embedded content. Similarly, any hierarchy can be represented in this way – position, supervisor, cost centre, account etc.
I’ll probably be posting some more similar tutorials in due course so keep a lookout. The next post will probably be looking at how to do the above manually to give a lot more power and flexibility.