How to Submit Concurrent Requests Outside of Oracle EBS

An option to our AventX product is a PL/SQL API that allows you to programmatically fax, email, archive, and print documents from Oracle EBS via business logic that makes sense for your company. Typically we integrate directly with the output from the Concurrent Manager as users submit requests, but customers have run into situations where their business flows do not involve their users using the Submit Request form. The API allows these customers to integrate directly with backend PL/SQL processes, forms, workflow, reports, etc…and in the end, takes all of the information set by the developer and calls the standard Oracle function fnd_request.submit() to submit the document to the Concurrent Manager so that AventX can process it.

A customer was tinkering with our API this past week and ran into some known caveats with fnd_request.submit().

The first caveat was just being able to test the API from SQL Developer. Simply making the function calls to see the results. From within a form in Oracle EBS, the API would work flawlessly, it would submit the concurrent request, and AventX would process it accordingly. However, in SQL Developer, the API would raise an exception that indicated the call to fnd_request.submit() failed, and instead of returning the request id, it returned 0.

The solution here was to make sure to set up the proper apps session before calling fnd_request.submit(). To do this programmatically, you can simply call the procedure:

fnd_global.apps_initialize(user_id, responsibility_id, responsibility_application_id);

You can determine the appropriate values for these arguments by either querying the database directly or examining profile values while logged into Oracle EBS. The same code worked via a form in Oracle EBS because the app’s environment is already set up.

After getting the test PL/SQL running in SQL Developer, the final resting spot for the code was an Oracle Report. The customer used the After Report Trigger to populate our API and submit a request to AventX. After adding the appropriate code (that now works in SQL Developer and a form in Oracle EBS) to the report trigger, the report would no longer compile! Instead, the error the customer received was:

Error 707 at line 0, column 0 unsupported construct or internal error [2601]

I’ll admit, I hadn’t seen this one before. Doing some digging, we found that Oracle Reports (certain versions?) does not respect the ‘default’ parameter value of a function/procedure. Instead, it requires you to specify ALL parameters regardless of value. For example, our API has a function that wraps fnd_request.submit(). This function accepts similar parameters to fnd_request.submit() in that it will allow a user to specify 100 arguments to be used as parameters for the Concurrent Request:

function Submit (…, Argument1 in varchar2 default chr(0), vArgument2 in varchar2 default chr(0) … vArgument100 in varchar2 default chr(0)) returns number;

Utilizing the ‘default’ keyword in PL/SQL allows developers not to have to specify all 100 arguments but instead just lets them accept the default value (chr(0)). Well, in this case, Oracle Reports requires you to specify ALL default variables, or you get the above error. So, in the end, to ‘fix’ this compilation error, we had the customer specify a value for each argument, and now the Oracle Report runs with no issues.

I was curious as to why the Oracle Reports’ PL/SQL interpreter behaved this way versus the standard operating procedure of accepting the defaults. I did some digging in the Oracle Reports development guides and on Metalink, but the only thing I could find were a few bugs on My Oracle Support that referenced the error, but nothing really specific or concrete as to why it’s different.