Oracle eBusiness Suite: Concurrent Program with Pie Chart output

Oracle provides the option to produce an HTML output for concurrent programs. Using JavaScript in HTML output enables developers to produce graphical output. The possibilities are endless as anything which can be displayed on a web page can be shown as concurrent program output.

The below sample requirement demonstrates this by displaying a Pie chart as the output of the concurrent program.

 Sample Requirement

Develop a concurrent program that will output the number of employees from each country working for the organization as a pie-chart as shown below.

 Steps

1.    Download and compile xx_pie_chart_pk in APPS schema. The PLSQL uses JavaScript as demonstrated in Google Developers site.

2.    Prepare the SQL to retrieve required data. The SQL is expected to have 2 fields in the SELECT clause with first one as the field description and then the count.

SELECT territory_short_name

      ,COUNT(*) cnt

FROM   per_addresses      addr

      ,fnd_territories_tl terr

WHERE  addr.country = terr.territory_code

GROUP  BY territory_short_name

HAVING COUNT(*) > 100;

3.    Create PLSQL package specification for the concurrent program as below.

CREATE OR REPLACE PACKAGE xx_emp_country_report IS PROCEDURE draw ( x_ret_code OUT VARCHAR2 ,x_err_buf OUT VARCHAR2 ); END xx_emp_country_report;

4.    Create PLSQL package body for the concurrent program as below. Please note that 2 parameters are the SQL from previous step and the title to be displayed in the output.

CREATE OR REPLACE PACKAGE BODY xx_emp_country_report IS PROCEDURE draw ( x_ret_code OUT VARCHAR2 ,x_err_buf OUT VARCHAR2 ) IS l_sql VARCHAR2(32767) := 'SELECT territory_short_name ,COUNT(*)*13 cnt FROM per_addresses addr ,fnd_territories_tl terr WHERE addr.country = terr.territory_code GROUP BY territory_short_name HAVING COUNT(*) BETWEEN 50 AND 100'; BEGIN xx_pie_chart_pk.draw_chart(p_title => 'Employees by Country' ,p_sql => l_sql); END draw; END xx_emp_country_report;

5.    Define Concurrent program executable.

6.Define Concurrent program. Please make sure that the output is set as ‘HTML’.

7. Attach the concurrent program to suitable responsibility.

8. Submit the concurrent request and review the output.

There are a vast number of types of charts and other graphical components which can be added to the HTML output using Javascript.