Serendipity and Integrated SOA Gateway

Serendipity - the accidental discovery of something pleasant, valuable, or useful.


I am an avid reader of quite a few Oracle blogs such as Steven Chan's blog, eBS Integration Blog, Tom Kyte, BI Publisher and many, many more. There's so much information out there on so many topics that I read about and hope to implement, so when I do get a chance to use what I learned from these blogs, I feel ever so grateful to the kind folks in the Oracle blogosphere. So, thank you Steven Chan, Vijay Shanmugan, Gautam Satpathy and others.


And so it was that some time in late 2009 I read about ISG and Web Services Invocation Framework in R12 when I was still working on a R11i project. The integration options made available by using web services were awesome. I rolled off that project and found a R12 implementation and one of the requirements was to build a custom approval process. No big whoop - been there done that. However, while setting up Workflow Notification Mailer, I find out that corporate policy does not allow IMAP on their mail servers. So,my choices are to come up with a solution that does not involve Oracle Workfow or I use only the outbound portion of the mailer and use something else for inbound processing. 
Not using WF just because IMAP was not allowed didn't seem right, so I went back and re-read the blog posts and documentation on ISG and decided to give it a go.
I created a custom PL/SQL procedure that called WF_NOTIFICATION.RESPOND and added it to the Integration Repository, created the WSDL and exposed the procedure as a web service. 
I created a workflow notification that included a hyperlink to a web page residing on the applications tier. The web page uses server side JavaScript to invoke the web service using XMLHttpRequest. I had to modify the standard WFMAIL workflow item in order to suppress the standard features for inbound processing.
Why JavaScript? Let's take the FND_PROFILE web service for example. The WSDL would be http://<your ebs apps server:port>/webservices/SOAProvider/plsql/fnd_profile/?wsdl . If you entered http://<your ebs apps server:port>/webservices/SOAProvider/plsql/fnd_profile/ in the address bar of your browser, you would see something like this:

Download the JavaScript stub and you have a starting point. Edit the stub to send well formed XML to the URL for the web service. Here's a sample for FND_PROFILE.GET without the WSSE:Security part:



<?xml version = '1.0' encoding = 'UTF-8'?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Header>
<fnd:SOAHeader xmlns:fnd="http://xmlns.oracle.com/apps/fnd/soaprovider/plsql/fnd_profile/"> 
<fnd:Responsibility>FND_REP_APP</fnd:Responsibility> 
<fnd:RespApplication>FND</fnd:RespApplication> 
<fnd:SecurityGroup>STANDARD</fnd:SecurityGroup> 
<fnd:NLSLanguage>AMERICAN</fnd:NLSLanguage> 
<fnd:Org_id>100</fnd:Org_id> </fnd:SOAHeader>
</soapenv:Header>
<soapenv:Body>
<get:InputParameters xmlns:get="http://xmlns.oracle.com/apps/fnd/soaprovider/plsql/fnd_profile/get/"> 
<get:NAME>APPS_WEB_AGENT</get:NAME> 
</get:InputParameters>
</soapenv:Body>
</soapenv:Envelope>


There you have it. Thanks to the customer's policy on IMAP, I could implement a neat solution using ISG. The only hitch was that the approvers had to be on the network for this to work. 


Next up - getting this solution to work on mobile devices using SSL.