Fusion Applications provides Web services that allow external systems to integrate with Fusion Applications. There are two types of services: ADF services and composite services. ADF services are created for a logical business object and provide functionality to access and manipulate these objects. The composite services are mostly process oriented and provide an orchestration of multiple steps.
Information about the web services provided by Fusion Applications is hosted in Oracle Enterprise Repository (OER). The information provided by OER can be used to understand the functionality provided by the service and how the service can be called.
This series of articles describes how one can invoke SOAP web services provided by Fusion Applications using various technologies. In this article we will cover how to invoke a Fusion Application web service using Visual Basic for Applications (VBA).
This example was implemented and tested with Microsoft Office Excel 2007; however the sample should work on other versions also.
VBA allows Visual Basic code to be executed from tools such as Excel. First ensure that macros can be executed; open Excel and navigate "Start > Excel Options > Trust Center Trust Center Settings > Macro Settings":
Do note that this enables all macros; this is likely not desirable for production and proper security would need implemented. Copy the following SOAP envelope attached to the cell A1. Do note that the content of the SOAP envelope has to be updated to match your environment:
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/incentiveCompensation/cn/creditSetup/creditRule/creditRuleService/types/"> <soapenv:Header> <wsse:Security soapenv:mustUnderstand="1" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd"> <wsu:Timestamp wsu:Id="TS-2"> <wsu:Created>2015-02-19T09:36:46.951Z</wsu:Created> <wsu:Expires>2015-02-19T09:53:26.951Z</wsu:Expires> </wsu:Timestamp> <wsse:UsernameToken wsu:Id="UsernameToken-1"> <wsse:Username>username</wsse:Username> <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">password</wsse:Password> <wsse:Nonce EncodingType="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-soap-message-security-1.0#Base64Binary">579SGaQdjWBE3RLhFWxadg==</wsse:Nonce> <wsu:Created>2015-02-19T09:36:46.941Z</wsu:Created> </wsse:UsernameToken> </wsse:Security> </soapenv:Header> <soapenv:Body> <typ:getRule> <typ:ruleId>300000000851162</typ:ruleId> </typ:getRule> </soapenv:Body> </soapenv:Envelope>
Next we implement the VBA code to make the call to the web service. Open the VBA editor by pressing "alt-F11", copy the following coded to the "Visual Basic Editor" and update the host and port to match your environment:
Sub TestWsCall() Dim sURL As String, sResult As String, sEnvelope As String Dim xmlHttp As Object sEnvelope = Cells(1, 1) Debug.Print "Call the service" sURL = "https://host:port/icCnSetupCreditRulesPublicService/CreditRuleService" Set xmlHttp = CreateObject("MSXML2.XMLHTTP") xmlHttp.Open "POST", sURL, False xmlHttp.setRequestHeader "Content-Type", "Text/Xml" xmlHttp.Send (sEnvelope) sResult = xmlHttp.responseText Debug.Print sResult End Sub
Finally run the macro; the results are shown in the output window:
In this article we covered an example using VBA in Excel to integrate with Fusion Applications using web services. In future articles other technologies for invoking Fusion Applications web services will be covered.