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

In my previous blog, I talked about how we 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 members of teams at Explorer for use in various places in the application.

Microsoft Graph API

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 started looking for other ways we could retrieve this information. I’m not a big fan of exactly the same information having to be 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.

APEX API

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. The first to retrieve all the members of a team (the teams are relatively static):

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

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, in particular, the important one for this exercise 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 organisation. Something which had been on my ‘rainy day’ task list for quite a while!

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!

Author: Paul Jones

Job Title: Oracle APEX Development Consultant

Bio: Paul is a Development Consultant at DSP-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.