Call a concurrent program from SOA

The primary purpose of this document is to show how we can call a EBS report from Oracle SOA suite by calling the concurrent program. This is a full complete document as all the steps to create a report from scratch and calling it from SOA has been mentioned in details along with proper screenshots.

The first thing is create a table in the database from where we will fetch the data to create a report.

Create a table in the oracle ebs

create table employee (name varchar2(50),employeeId varchar2(50),department varchar2(50));

and insert some record in to table.

Create a template for retrieving the data.

<?xml version="1.0" encoding="UTF-8"?>
<dataTemplate name="Employee_details" descrciption ="Employee_details" version="1.0">
<dataQuery>
<sqlStatement name="Q_ORDER">
<![CDATA[select name,employeeId ,department from employee ]]>

</sqlStatement>
</dataQuery>
<dataStructure>
<group name="G_ORDER" source="Q_ORDER">
<element name="NAME" value="name"/>
<element name="EMPLOYEEID" value="employeeId"/>
<element name="DEPARTMENT" value="department"/>
</group>
</dataStructure>
</dataTemplate>


Now create a concurrent program.

This concurrent program will be used to call the template we have created now and it will generate the required XML document from it.

Go to System Administer responsibility-->Concurrent -->Program-->Define



Sepciy the program name, a short name(the short name is important as this will be used in the data definiton of Report. we will see that later)

Application will be XML Publisher

Executable will be XDODTE

Method JAva Concurrent program

Select output format as XML



Once data template is ready and concurrent program is created

Go to XML publisher responsibility and go to data definition as shown below.





Say apply and then upload the data template that we have created earlier to the data definition



Go to security-->responsibility-->request
And do Ctrl + F11


Add your program to the list



Now go to application developer responsibility and go to view request and submit a new request



Once the concurrent program is completed

Check the output



Now create an anonymous block to call this concurrent program
Following blog gives all the details required to call the concurrent program
http://oracleapps4u.blogspot.in/2011/08/how-to-submit-concurrent-program-from.html


these information can be retrieved using following code
SELECT USER_ID,
RESPONSIBILITY_ID,
RESPONSIBILITY_APPLICATION_ID,
SECURITY_GROUP_ID
FROM apps.FND_USER_RESP_GROUPS
WHERE USER_ID = (SELECT USER_ID
FROM apps.FND_USER
WHERE USER_NAME = '&user_name')
AND RESPONSIBILITY_ID = (SELECT RESPONSIBILITY_ID
FROM apps.FND_RESPONSIBILITY_VL
WHERE RESPONSIBILITY_NAME = '&resp_name');




SO Based on the above result we will change the code to following
apps.fnd_global.apps_initialize(0,20419,0);
ln_request_id := apps.fnd_request.submit_request(application => 'XDO',
description => NULL,
program => 'ECP',
start_time => sysdate,
sub_request => FALSE);
COMMIT;
dbms_output.put_line('ln_request_id :'||ln_request_id);
end;


Here Program name is the short form of my concurrent program
And Application name is the short name of application

So we will try to call this in an anonymous block so that we can get the output displayed in sql.
So I will wrap the code as execute it as shown below
SET SERVEROUTPUT ON
DECLARE
ln_request_id NUMBER(25);
BEGIN
apps.fnd_global.apps_initialize(0,20419,0);
ln_request_id := apps.fnd_request.submit_request(application => 'XDO',
description => NULL,
program => 'ECP',
start_time => sysdate,
sub_request => FALSE);
COMMIT;
dbms_output.put_line('ln_request_id :'||ln_request_id);
end;


Once I executed this code I got the following output
anonymous block completed
ln_request_id :5904404
So now log in to your Oracle EBS and check for the completed request




You can see that the same request id is logged in oracle application and you can review the output for the same.
Now in order to call this concurrent program from oracle soa suite we need to create a pl/sql procedure because anonymous blocks are not named one and cannot be called.
Will create pl/sql procedure to execute the same.
Now I will wrap it within pl/sql procedure as follows
CREATE OR REPLACE PROCEDURE Employee_details(test in number,ln_request_id OUT NUMBER)
IS
BEGIN
apps.fnd_global.apps_initialize(0,20419,0);
ln_request_id := apps.fnd_request.submit_request(application => 'XDO',
description => NULL,
program => 'ECP',
start_time => sysdate,
sub_request => FALSE);
COMMIT;
dbms_output.put_line('ln_request_id :'||ln_request_id);
end;
here passing a input is not required but I have just defined it.
Now we can call the procedure directly from sql as below.
We first need to define a variable so that we can hold the output response in that variable
VARIABLE VAR NUMBER;
This will declare a variable called “VAR” which will be of type number and we will use this to store the response.
Now execute the code using following
exec Employee_details(5,:VAR)
now say
PRINT VAR;
And you will get the result as below
VAR
-------
5904852
You can check it at the back end to validate if the procedure is completed successfully.




Now we will try to call the same procedure using soa and will see if soa can call the concurrent program from pl/sql procedure.
I create a simple soa process to call the procedure and deployed the process to server when I tried to test it I got below error.




It is because I am using XA data source in my soa and I also have commit statement in my Pl/sql procedure. I went to admin console and changed the data source from XA to non XA
After changing the data source from xa to non-xa I again tried to test it and I am getting a different error


messages>
<input>
<Invoke1_Employee_details_InputVariable>
<part name="InputParameters">
<InputParameters>
<TEST>123</TEST>
</InputParameters>
</part>
</Invoke1_Employee_details_InputVariable>
</input>
<fault>
<bpelFault>
<faultType>0</faultType>
<bindingFault>
<part name="summary">
<summary>Exception occured when binding was invoked. Exception occured during invocation of JCA binding: "JCA Binding execute of Reference operation 'Employee_details' failed due to: Stored procedure invocation error. Error while trying to prepare and execute the APPS.EMPLOYEE_DETAILS API. An error occurred while preparing and executing the APPS.EMPLOYEE_DETAILS API. Cause: java.sql.SQLException: ORA-20001: Oracle error -20001: ORA-20001: Oracle error -2074: ORA-02074: cannot SET NLS in a distributed transaction has been detected in fnd_global.set_nls.set_parameter('NLS_LANGUAGE','AMERICAN'). has been detected in fnd_global.set_nls. ORA-06512: at "APPS.APP_EXCEPTION", line 72 ORA-06512: at "APPS.FND_GLOBAL", line 265 ORA-06512: at "APPS.FND_GLOBAL", line 1582 ORA-06512: at "APPS.FND_GLOBAL", line 2029 ORA-06512: at "APPS.FND_GLOBAL", line 2546 ORA-06512: at "APPS.FND_GLOBAL", line 2698 ORA-06512: at "APPS.FND_GLOBAL", line 2636 ORA-06512: at "APPS.EMPLOYEE_DETAILS", line 4 ORA-06512: at line 1 Check to ensure that the API is defined in the database and that the parameters match the signature of the API. This exception is considered not retriable, likely due to a modelling mistake. To classify it as retriable instead add property nonRetriableErrorCodes with value "-20001" to your deployment descriptor (i.e. weblogic-ra.xml). To auto retry a retriable fault set these composite.xml properties for this invoke: jca.retry.interval, jca.retry.count, and jca.retry.backoff. All properties are integers. ". The invoked JCA adapter raised a resource exception. Please examine the above error message carefully to determine a resolution. </summary>
</part>
<part name="detail">
<detail>ORA-20001: Oracle error -20001: ORA-20001: Oracle error -2074: ORA-02074: cannot SET NLS in a distributed transaction has been detected in fnd_global.set_nls.set_parameter('NLS_LANGUAGE','AMERICAN'). has been detected in fnd_global.set_nls. ORA-06512: at "APPS.APP_EXCEPTION", line 72 ORA-06512: at "APPS.FND_GLOBAL", line 265 ORA-06512: at "APPS.FND_GLOBAL", line 1582 ORA-06512: at "APPS.FND_GLOBAL", line 2029 ORA-06512: at "APPS.FND_GLOBAL", line 2546 ORA-06512: at "APPS.FND_GLOBAL", line 2698 ORA-06512: at "APPS.FND_GLOBAL", line 2636 ORA-06512: at "APPS.EMPLOYEE_DETAILS", line 4 ORA-06512: at line 1 </detail>
</part>
<part name="code">
<code>20001</code>
</part>
</bindingFault>
</bpelFault>
</fault>


Following document suggest a work around for this issue
http://oracle-apps-dba.blogspot.in/2008/05/fndglobal-affected-by-new-global.html
I changed the code to implement the solution and now my code is
CREATE OR REPLACE PROCEDURE Employee_details(test in number,ln_request_id OUT NUMBER)
AS pragma autonomous_transaction;
BEGIN
apps.fnd_global.apps_initialize(0,20419,0);
ln_request_id := apps.fnd_request.submit_request(application => 'XDO',
description => NULL,
program => 'ECP',
start_time => sysdate,
sub_request => FALSE);
COMMIT;
end;

I redeployed my code but again I got following error.

0

Exception occured when binding was invoked. Exception occured during invocation of JCA binding: "JCA Binding execute of Reference operation 'Employee_details' failed due to: DBWriteInteractionSpec Execute Failed Exception. unknown failed. Descriptor name: [unknown]. Caused by java.sql.SQLException: Cannot call commit when using distributed transactions. Please see the logs for the full DBAdapter logging output prior to this exception. This exception is considered retriable, likely due to a communication failure.

This is because though I have made the jndi as non xa but data source itself is xa so we will recreate a new data source
Make sure you are using the non xa driver






Retested the code again but I got again error

<messages>
<input>
<Invoke1_Employee_details_InputVariable>
<part name="InputParameters">
<InputParameters>
<TEST>123</TEST>
</InputParameters>
</part>
</Invoke1_Employee_details_InputVariable>
</input>
<fault>
<bpelFault>
<faultType>0</faultType>
<remoteFault>
<part name="summary">
<summary>Exception occured when binding was invoked. Exception occured during invocation of JCA binding: "JCA Binding execute of Reference operation 'Employee_details' failed due to: DBWriteInteractionSpec Execute Failed Exception. unknown failed. Descriptor name: [unknown]. Caused by java.sql.SQLException: Cannot call Connection.commit in distributed transaction. Transaction Manager will commit the resource manager when the distributed transaction is committed.. Please see the logs for the full DBAdapter logging output prior to this exception. This exception is considered retriable, likely due to a communication failure. To classify it as non-retriable instead add property nonRetriableErrorCodes with value "0" to your deployment descriptor (i.e. weblogic-ra.xml). To auto retry a retriable fault set these composite.xml properties for this invoke: jca.retry.interval, jca.retry.count, and jca.retry.backoff. All properties are integers. ". The invoked JCA adapter raised a resource exception. Please examine the above error message carefully to determine a resolution. </summary>
</part>
<part name="detail">
<detail>Cannot call Connection.commit in distributed transaction. Transaction Manager will commit the resource manager when the distributed transaction is committed.</detail>
</part>
<part name="code">
<code>0</code>
</part>
</remoteFault>
</bpelFault>
</fault>

Again this is because in the configuration of non-xa data source I have support global transaction enabled in the data source

Go to you data source configuration and deselect the support global transaction



I disabled the option Support global transaction and retested and this time I was able to successfully test it



validate the concurrent program