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.