Cloud

O SpecGold OracleBusIntApps7 clr

 Gcloud

 

   Call us now 

  Manchester Office

  +44 (0) 8450 940 998

 

  

 

John's Blog

This is my blog of various topics, from things that I think might be useful to others, to things that I just find interesting personally. If you have any comments or questions on any of my posts then please do ask - any positive contribution is very much welcomed.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.
  • Team Blogs
    Team Blogs Find your favorite team blogs here.
  • Login
    Login Login form

Creating an Organization or Position Hierarchy Chart

  • Font size: Larger Smaller
  • Subscribe to this entry
  • Print
Creating an Organization or Position Hierarchy Chart

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.

EBS Diagrammer
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.

APEX Application

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.

APEX Dynamic PLSQL Region

Now run our application and we have our full org chart.

Org Chart V1

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
   );

Org Chart Sales

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.

Last modified on
Tagged in: APEX E-Business Suite
in Technical Hits: 1064 2 Comments
0
My primary area of interest and expertise is Oracle E-Business Suite, particularly Foundation, Human Resources, Payroll, Time and Labor, Warehouse Management and Inventory. Whilst I prefer to focus on functional aspects I do have a keen interest in technical areas too such as SQL tuning/the optimizer, Application Express (APEX), Unix (particularly Linux) and general development.


Comments

  • Guest
    Geoff Scott Tuesday, 11 July 2017

    Hi, John.
    What a great blog entry - its opened up a big avenue of discovery.
    I've recently implemented this in Answers (using a narrative view), and added a prompt base on the second level of the department hierarchy and using a presentation variable.
    This works great (I've verified the results using a simple table view).
    However, after the initial org chart is displayed, once the prompt is changed and the Apply button in selected the chart disappears (but the table view refreshed as expected).
    I suspect there's some sort of method I need to call to reload the analysis with the google chart / narrative view but as I have absolutely zero java knowledge or where to even call this method I'm stumped.
    Any thoughts?
    Geoff
    Home Group Ltd

Leave your comment

Guest
Guest Wednesday, 20 September 2017