Oracle Apps Diary: Concurrent Program with Organizational Chart Output

As described in my previous post, we can use HTML and JavaScript to draw tree diagrams from the output of a Hierarchical SQL. This could also be used for various applications in Oracle eBusiness Suite. One of the uses will be to visualize organizational structure.

The purpose of the below exercise is to develop a concurrent program which accepts an operating unit as input parameter and draw the Organizational diagram in tree format as shown in the sample below.

Please follow the below steps to define this concurrent program.

Step 1:- Package Specification

Compile the below package specification in APPS schema.

CREATE OR REPLACE PACKAGE xx_org_chart_pk IS
  PROCEDURE output
  (
    x_ret_code        OUT VARCHAR2
   ,x_err_buf         OUT VARCHAR2
   ,p_organization_id IN NUMBER
  );
END xx_org_chart_pk;

Step 2:- Package Body

Compile the below package body in APPS schema.

CREATE OR REPLACE PACKAGE BODY xx_org_chart_pk IS
  ---Developed by Binuraj Nair (oracleappsdiary.blogspot.com)
  --------------------------------------------------------------------------------------
  --Output
  ---------------------------------------------------------------------------------------
  PROCEDURE output_line(p_message IN VARCHAR2) IS
  BEGIN
    fnd_file.put_line(fnd_file.output
                     ,p_message);
  END output_line;
  --------------------------------------------------------------------------------------
  --Generated HTML
  ---------------------------------------------------------------------------------------
  PROCEDURE draw_tree_diagram(p_sql IN VARCHAR2) IS
    l_html1 VARCHAR2(32767) := '<html>
  <head>
    <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'');

        // For each orgchart box, provide the name, manager, and tooltip to show.
        data.addRows([
          ';
  
    l_html2 VARCHAR2(32767) := '
   ]);
          // Create the chart.
        var chart = new google.visualization.OrgChart(document.getElementById(''chart_div''));
        // Draw the chart, setting the allowHtml option to true for the tooltips.
        chart.draw(data, {allowHtml:true});
        chart.draw(data, {allowCollapse:true});
      }
   </script>
    </head>
  <body>
    <div id="chart_div">
</div>
</body>
</html>';
  
    TYPE empcurtyp IS REF CURSOR;
    v_emp_cursor empcurtyp;
    TYPE emp_record_type IS RECORD(
       node    VARCHAR2(100)
      ,PARENT  VARCHAR2(100)
      ,tooltip VARCHAR2(300));
    rec            emp_record_type;
    l_record_count NUMBER := 0;
  BEGIN
    output_line(l_html1);
  
    OPEN v_emp_cursor FOR p_sql;
  
    LOOP
      FETCH v_emp_cursor
        INTO rec;
      EXIT WHEN v_emp_cursor%NOTFOUND;
    
      IF l_record_count <> 0
      THEN
        output_line(',');
      END IF;
    
      output_line('[''' || rec.node || ''', ''' || rec.parent ||
                  ''', ''' || rec.tooltip || ''']');
      l_record_count := l_record_count + 1;
    
    END LOOP;
  
    CLOSE v_emp_cursor;
  
    output_line(l_html2);
  
  END draw_tree_diagram;
  --------------------------------------------------------------------------------------
  --Main Procedure
  ---------------------------------------------------------------------------------------
  PROCEDURE output
  (
    x_ret_code        OUT VARCHAR2
   ,x_err_buf         OUT VARCHAR2
   ,p_organization_id IN NUMBER
  ) IS
  BEGIN
    draw_tree_diagram('SELECT 
    org.name node
      ,(SELECT NAME 
        FROM   hr_all_organization_units
        WHERE  organization_id = pose.organization_id_parent) PARENT,       
      type tooltip
FROM   hr_all_organization_units  org
      ,per_org_structure_elements pose
WHERE  1 = 1
AND    org.organization_id = pose.organization_id_child
-- AND pose.org_structure_version_id = 62
START  WITH pose.organization_id_parent = ' ||
                      p_organization_id ||
                      ' CONNECT BY PRIOR
            pose.organization_id_child = pose.organization_id_parent');
  END output;

END xx_org_chart_pk;

Step 3:- Define Concurrent Program Executable

Define the concurrent program executable of PL/SQL type with execution file name as xx_org_chart_pk.output

Step 4:- Define Concurrent Program

Define the concurrent program for the executable in previous step. Please make sure that the output format is HTML.

Step 5: Add Operating Unit Parameter

Add one parameter to the program. The selected value for this parameter will be the top node in the diagram.

Now that concurrent program is defined, you can attach it to appropriate responsibilities and submit from Standard Request Submission window.

Foot Notes

The client machine requires internet connection to display the diagram. This is since the code accesses JavaScript library source from the corresponding site.
The JavaScript library used is from gstatic. Please read the documentation to extend this functionality.
The PLSQL program in the demo handles only the basic case for simplicity. Please feel free to extend the code.
On double clicking a parent node, it collapses.
Some browsers have trouble displaying chart when the number of nodes exceeds the set limit for that browser. I have experienced it with Internet Explorer. Try different browsers if you are getting blank output.

Oracle Apps Diary

Wednesday, October 19, 2016

Concurrent Program with Organizational Chart Output

No comments: