Creating a dynamic Organisation Chart in APEX in under an hour from your Active Directory

In my previous blog, I talked about how DSP-Explorer were moving from a local exchange server to use Office 365, which meant we were no longer going to be using Exchange Web Services to manage calendars.

One other method we used from the ms_ews_utils_pkg package (part of the alexandria-plsql-utils library) was the expand_public_dl_as_list function which takes a group distribution email address and returns a list of all the members of that group from exchange. We used that function (which ran on a schedule or could be manually ran) to download a list of all the different team members at DSP-Explorer for use in various places in the application.

I had a look to see if there was something similar available in the Microsoft Graph API but nothing stood out as being able to give the same results as quickly. So I started looking for other ways we could retrieve this information. I’m not a big fan of identical information being manually input in to more than one place.

I then remembered that the same information needed was already stored somewhere else, locally, in our Active Directory.

We have an API available in the APEX API for using the LDAP protocol to talk to Active Directory called ‘APEX_LDAP’ and a function for searching called ‘APEX_LDAP.SEARCH’.

I didn’t need to set up anything in terms of ACLs, certificates or permissions as we already used APEX_LDAP.AUTHENTICATE in order to manage authentication for users.

I added a couple of cursors to the procedure used to pull this information. Firstly to retrieve all the members of a team (the teams are relatively static):

Organisation Chart

And then for each team member, retrieve the attributes required:

Organisation Chart

As you can see from the above, one thing I spotted when I queried Active Directory is that more information was available in the attributes of a member. The important attribute for this exercise on particular, is the manager.

If we have all the relations between the records then have all the information needed in order to relate the records for each and then utilising a hierarchical query would, very quickly, be able to create an Organisation Chart.

I created a new (Public) APEX application, installed the Apex D3 Organization Chart region plugin, supplied the hierarchical query needed and was instantly presented with an expandable, dynamic organisation chart for the entire company. Something which had been on my ‘rainy day’ task list for quite a while!

Organisation Chart

Summary

It’s rarely advisable to store the same information in more than one place. Almost all integrations you can think of are possible from the Oracle database (and therefore available to utilise in an APEX application). So, if you can, get integrating!

Paul Jones Contributor
Oracle APEX Development Consultant , Explorer UK Ltd

Paul is a Development Consultant at Explorer. Paul has a BSc in Computing from the University of Leeds and is now building on considerable experience in development and support using Oracle PL/SQL and Oracle E-Business Suite (EBS). Paul is employing APEX to provide quality, bespoke software solutions both internally and to a range of organisations.

follow me