Generation of Org-Chart from Oracle HRMS

Dear Readers

Just wanted to share a useful information (maybe it can be used somewhere else in future) of how we can generate an org-chart from Oracle HR data using Google API’s

Step-1: Using the data in PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F, generate a list of employees with their supervisor name as per below.

select

ppf.full_name empl_name,

ppfm.full_name mgr_name

from per_all_people_f ppf, per_all_people_f ppfm, per_all_assignments_f paf

where sysdate between ppf.effective_start_date and ppf.effective_end_date

and sysdate between ppfm.effective_start_date and ppfm.effective_end_date

and ppf.person_id = paf.person_id

and sysdate between paf.effective_start_date and paf.effective_end_date

and paf.supervisor_id = ppfm.person_id (+)

and ppfm.full_name like 'Stock%'

order by 2 desc

Step-2: Use the below PLSQL block to write out a HTML file in a directory

DECLARE

v_file utl_file.file_type;

BEGIN

v_file := utl_file.fopen('/usr/tmp', 'Sample.htm', 'W');

utl_file.put_line(v_file, '<html>');

utl_file.put_line(v_file, ' <head>');

utl_file.put_line(v_file, ' <script type="text/javascript" src="https://www.google.com/jsapi"></script>');

utl_file.put_line(v_file, ' <script type="text/javascript">');

utl_file.put_line(v_file, ' google.load("visualization", "1", {packages:["orgchart"]});');

utl_file.put_line(v_file, ' google.setOnLoadCallback(drawChart);');

utl_file.put_line(v_file, ' function drawChart() {');

utl_file.put_line(v_file, ' var data = new google.visualization.DataTable();');

utl_file.put_line(v_file, ' data.addColumn(''string'', ''Name'');');

utl_file.put_line(v_file, ' data.addColumn(''string'', ''Manager'');');

utl_file.put_line(v_file, ' data.addColumn(''string'', ''ToolTip'');');

utl_file.put_line(v_file, ' data.addRows([');

FOR i IN

(

select

ppf.full_name empl_name,

ppfm.full_name mgr_name

from per_all_people_f ppf, per_all_people_f ppfm, per_all_assignments_f paf

where sysdate between ppf.effective_start_date and ppf.effective_end_date

and sysdate between ppfm.effective_start_date and ppfm.effective_end_date

and ppf.person_id = paf.person_id

and sysdate between paf.effective_start_date and paf.effective_end_date

and paf.supervisor_id = ppfm.person_id (+)

and ppfm.full_name like 'Stock%'

order by 2 desc

) loop

utl_file.put_line(v_file, '[''' || i.empl_name || ''', ''' || i.mgr_name || ''', ''''],');

END LOOP;

utl_file.put_line(v_file, ']);');

utl_file.put_line(v_file, ' var chart = new google.visualization.OrgChart(document.getElementById(''chart_div''));');

utl_file.put_line(v_file, ' chart.draw(data, {allowHtml:true,allowCollapse:true,color:''#ffffff'',selectionColor:''#cc3636''});');

utl_file.put_line(v_file, ' }');

utl_file.put_line(v_file, ' </script>');

utl_file.put_line(v_file, ' </head>');

utl_file.put_line(v_file, ' <body>');

utl_file.put_line(v_file, ' <div id="chart_div"></div>');

utl_file.put_line(v_file, ' </body>');

utl_file.put_line(v_file, '</html>');

utl_file.fclose(v_file);

END;

Step-3: Voila!!! The HTML file (when opened in Chrome/Firefox etc.) chart as below

Hope this helps you sometime

Cheers
A

Monday, February 9, 2015

Generation of Org-Chart from Oracle HRMS

1 comment: