Wednesday, October 19, 2016

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.


Step 2:- Package Body

Compile the below package body in APPS schema.



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.

Wednesday, October 12, 2016

Hierarchical SQL with Tree Diagram output

The greatest value of a picture is when it forces us to notice what we never expected to see
                                                                                                              - John Wilder Tukey

Hierarchical SQL output is not easy to read in tabular format. I have seen developers trying to visualize the output in various ways. Provided you have a browser, it is not difficult to generate an output like the below using HTML and JavaScript.

Steps

1. Prepare Hierarchical SQL

I am using the classic EMP table in SCOTT schema.

SELECT e.ename node
,m.ename PARENT
,e.empno || ' ' || e.job tooltip
FROM scott.emp e
,scott.emp mWHERE e.mgr = m.empno(+)
CONNECT BY PRIOR e.empno = e.mgr
START WITH e.job = 'PRESIDENT'

Please make sure that there are 3 columns in your SQL with aliases as below.

  1. Node :- Unique name for the node
  2. Parent :- Name of the parent Node
  3. Tooltip : Tooltip description to be displayed when mouse is moved over the node.

2. Compile the below procedure in database.



Step 3 : Execute the PLSQL procedure

Invoke the above PLSQL procedure with SQL in Step 1 as parameter. The output will be written to DBMS output. Please make sure that the output size is set accordingly.


Step 4 : Save the output as an htm file.

The output will be written with html tags with JavaScript. Save the output with an htm extension.

Step 5: Open the HTM file in web browser

This will display the hierarchical output as a tree diagram assuming the local machine has internet connection. Internet connection is required since the JavaScript tries to access the library on the Google website.