Oracle Apps Notes

A collection of my random notes, primarily on Oracle Apps

Invoking a Web Service from seeded Oracle EBS form through PL/SQL

The scenario : Invoking a Web Service hosted in a SAP PI server from a seeded Oracle E-Business Suite form

The solution : Writing a PL/SQL function which invokes the Web Service and calling the function from the seeded form. The PL/SQL function will use the UTL_HTTP package to invoke the Web Service through a HTTP POST request.

The Steps : Generate the expected SOAP Request and Response XML documents for the Web Service. There are several tools which you can use to get the Request and Response XML documents. I found soapUI to be the best(read easiest) of the lot. All you need to do is to create a new project in soapUI based on the WSDL and generate a MockService. soapUI will generate the Request XML for you which you can then run to get the Response XML.

Next we will create the PL/SQL function which will invoke the Web Service.

create or replace function xxx_sample_ws(p_em_amount number)
  return varchar2 is
  soap_request varchar2(30000);
  soap_respond varchar2(30000);
  http_req     utl_http.req;
  http_resp    utl_http.resp;
  resp         XMLType;
  exp_resp     XMLType;
  endpoint_url varchar2(1000);
  username CONSTANT varchar2(10) := 'user_100';
  password CONSTANT varchar2(10) := 'pass_100';
  pname varchar2(100);
  outs  varchar2(10000) := null;

begin

  --The SOAP Request XML document obtained from soapUI
  --the parameters are embedded into the request
  soap_request := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://rapdrp.com/updateemd/transactional/model/1.0/" xmlns:ns1="http://rapdrp.com/common/bodcomponents/transactional/model/1.0/">
   <soapenv:Header/>
   <soapenv:Body>
      <ns:UpdateEMDReq>
         <ns:ApplicationArea>
            <!--Optional:-->
            <ns1:Sender>
               <!--Optional:-->
               <ns1:Id>?</ns1:Id>
               <!--Optional:-->
               <ns1:Properties>
                  <!--Optional:-->
                  <ns1:Property>?</ns1:Property>
                  <!--Optional:-->
                  <ns1:Value>?</ns1:Value>
               </ns1:Properties>
            </ns1:Sender>
            <!--Optional:-->
            <ns1:Receiver>
               <!--Optional:-->
               <ns1:Id>?</ns1:Id>
               <!--Optional:-->
               <ns1:CompanyName>?</ns1:CompanyName>
               <!--Optional:-->
               <ns1:CompanyId>?</ns1:CompanyId>
               <!--Optional:-->
               <ns1:Properties>
                  <!--Optional:-->
                  <ns1:Property>?</ns1:Property>
                  <!--Optional:-->
                  <ns1:Value>?</ns1:Value>
               </ns1:Properties>
            </ns1:Receiver>
            <!--Optional:-->
            <ns1:Reference>
               <!--Optional:-->
               <ns1:BODCreationDateTime>?</ns1:BODCreationDateTime>
               <!--Optional:-->
               <ns1:BusinessProcessId>?</ns1:BusinessProcessId>
               <!--Optional:-->
               <ns1:MessageId>?</ns1:MessageId>
               <!--Optional:-->
               <ns1:CorrelationId>?</ns1:CorrelationId>
               <!--Zero or more repetitions:-->
               <ns1:Properties>
                  <!--Optional:-->
                  <ns1:Property>?</ns1:Property>
                  <!--Optional:-->
                  <ns1:Value>?</ns1:Value>
               </ns1:Properties>
               <!--Optional:-->
               <ns1:Username>?</ns1:Username>
               <!--Optional:-->
               <ns1:Token>?</ns1:Token>
            </ns1:Reference>
         </ns:ApplicationArea>
         <ns:DataArea>
            <!--Optional:-->
            <ns:ApplicationNo>?</ns:ApplicationNo>
            <!--Optional:-->
            <ns:ApplicationDate>?</ns:ApplicationDate>
            <!--Optional:-->
            <ns:ErnestMoneyAmt>'||p_em_amount||'</ns:ErnestMoneyAmt>
            <!--Optional:-->
            <ns:ApplicantName>?</ns:ApplicantName>
         </ns:DataArea>
      </ns:UpdateEMDReq>
   </soapenv:Body>
</soapenv:Envelope>';

  --the endpoint URL of the Web Service
  endpoint_url := 'http://10.95.244.75:8000/sap/xi/engine?type=entry' || '&' ||
                  'sap-user=user_100' || '&' || 'sap-password=pass_100' || '&' ||
                  'version=3.0' || '&' ||
                  'Sender.Service=CB_SZPDRP_DEV_ORACRM' || '&' ||
                  'Interface=urn%3Awb.szpdrp.oracrm.updateemd%5EUpdateEMD_OB';

  -- if proxy server is involved, use the following for authentication
  -- where 'username' and 'passwd' are the credentials required to access the proxy server
  -- with IP address 'ip_addredd' at port number 'port'
  -- utl_http.set_proxy('http://username:passwd@ip_address:port');

  --begin HTTP request
  http_req := utl_http.begin_request(endpoint_url, 'POST', 'HTTP/1.1');

  --set basic HTTP authentication
  --not required for this case as the endpoint URL contains the username/password
  --utl_http.set_authentication(http_req, username, password);
  utl_http.set_header(http_req, 'Content-Type', 'text/xml');
  utl_http.set_header(http_req, 'Content-Length', length(soap_request));

  --The SOAP action for the Web Service can also be obtained from soapUI
  utl_http.set_header(http_req,
                      'SOAPAction',
                      'http://sap.com/xi/WebService/soap1.1');
  utl_http.write_text(http_req, soap_request);
  http_resp := utl_http.get_response(http_req);
  utl_http.read_text(http_resp, soap_respond);
  utl_http.end_response(http_resp);
  resp := XMLType.createXML(soap_respond);

  --extract the value of the relevant element from the SOAP Response XML
  --Google 'Oracle XMLType' for more information
  begin
    exp_resp := resp.extract('//ns1:Status/text()',
                             'xmlns:ns1="http://rapdrp.com/updateemd/transactional/model/1.0/"');
    outs     := exp_resp.getStringVal();

  exception
    when others then
      outs := SQLERRM;
  end;

  --return the extracted value or the error details
  return outs;

  --in case of exceptions return the error details
exception
  when others then
    outs := SQLERRM;
    return outs;
end;

You can now call the function from the seeded Form through Form Personalization to invoke the Web Service and display an appropriate message to the user if the invocation was successful.

Nice friendly message for the user!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: