- Business Needs
- Application Integrations
- Delivery Technology
- About STR Software
About STR Software
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 a couple of 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 that the call to fnd_request.submit() failed and instead of returning the request id, 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 apps environment is already setup.
After getting the test PL/SQL running in SQL Developer, the final resting spot for the code was an Oracle Report. The customer was using 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 
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 to not 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 vs. the standard operating procedure of just 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
Metalink My Oracle Support that referenced the error, but nothing really specific or concrete as to why it’s different. Anyone else have any ideas? Leave me a comment if you do.