It's All About the Platform.
-
Integration and PaaS,
Web Services
April 30, 2015
Integrating With Fusion Application Using Services (Visual Basic for Applications)
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).
Prerequisites
This example was implemented and tested with Microsoft Office Excel 2007; however the sample should work on other versions also.
Implementing Web Service Call
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:
- The "Timestamp" may be required depending on the OWSM policy. My service is secured with "oracle/wss_username_token_over_ssl_service_policy" and the policy is configured to require timestamp so it needs to be provided. Also the value of the timestamp must be "recent", the server is configured with the a threshold and the times used in the timestamp must be within the threshold.
- Username and password need to be updated to match your authentication details
- The "RuleId" must match a rule identifier that exists in your system:
<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:
Summary
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.
Hi,
I tested the above one successfully. But, How do I generate Timestamp and username token. I'm getting when I try to generate them myself.
Kindly help
Thanks,
Naveen
I am not sure what the exact issue is but for the time stamp you would should use ISO 8601:
https://en.wikipedia.org/wiki/ISO_8601
See MSDN documentation on how to deal with dates:
https://msdn.microsoft.com/en-us/library/az4se3k1%28v=vs.110%29.aspx
For the username token you would have to obtain the user and credentials from somewhere, I do not have an example for that however if you use search engine there seems to be some options available for storing credentials.
If you are looking for a way to simply generate the security content manually I generally use SOAP UI, see this blog entry for details:
https://blogs.oracle.com/fadevrel/entry/integrating_with_fusion_application_using14
--
Jani Rautiainen
Fusion Applications Developer Relations
Hi,
Appreciate your efforts. I manually generated username and timestamp tokens using soap UI. But, I want to generate it automatically using some vba piece of code.
can you pls help me in this regard? I could not find a proper source googling.
Thanks
Flyer!
Maybe I am missing something here but the as I understand this the "generation" here is concatenating a string that contains the SOAP Envelope. Most of the content would be static text with some of the parameters like timestamp and credentials dynamically derived and concatenated. If I remember correctly you can simple use ampersand "&" to concatenate and use the link given:
https://msdn.microsoft.com/en-us/library/az4se3k1%28v=vs.110%29.aspx
to "generate" the the content of the the timestamp. The username is another matter though, I do not understand how you would be able to generate the values as the username / credential you would have to know or obtain from somewhere. I do not have a sample on how to do that but you can search with google and see some related samples like this:
http://stackoverflow.com/questions/3217014/how-to-securely-store-connection-string-details-in-vba
--
Jani Rautiainen
Fusion Applications Developer Relations
HI
Can i get any soap payload to deal with standard objects like Accounts,Opportunities services.
My doubt is how to add credentials in order to call the OSC.
Please share any knowledge regarding this.
Thanks,
Mastan
Hi.
I believe there are some samples on MyOracleSupport however the following might also help:
1) Use a testing client like SOAPUI and it'll generate a sample payload based on the WSDL and XSD documents.
2) Check out the documentation for understanding of the Service Data Objects and the payload attributes. For example:
http://docs.oracle.com/cloud/latest/salescs_gs/OESWS/serviceReferenceOverview.htm#serviceReferenceOverview
3) Take a look at our YouTube channel which shows using some of these where you can see some examples. https://www.youtube.com/user/FADeveloperRelations
Kind regards
Richard
Hello,
Have followed these instructions and indeed I am able to get the output in both SoapUI and VisualBasic, however SoapUI gives me the XML64 Output but all VBA does is to printout the exact XML Payload I have provided for value of SEnvelope, pretty much like your last screenshot in the Immediate Window. I think I may be missing something here in order to have the actual XML64 Output to be something like <env:Body>
<ns2:runReportResponse xmlns:ns2="http://xmlns.oracle.com/oxp/service/PublicReportService">
<ns2:runReportReturn>
<ns2:reportBytes>PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iVVRGLTgiPz4KPCEtLUdlbmVyYXRlZCBieSBP
8L0RBVEFfRFM+</ns2:reportBytes>
<ns2:reportContentType>text/xml</ns2:reportContentType>
<ns2:reportFileID xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>
<ns2:reportLocale xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>
<ns2:metaDataList xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>
</ns2:runReportReturn>
</ns2:runReportResponse>
</env:Body>
I am not sure I understand, the output should be the response envelope. Also the response should be the same in Soap UI and VBA call as long as the payload, credentials etc. are the same. Seems you are calling some report service so maybe the the content type is something else ? In any case post the details to the forum where its easier to discuss the issue:
https://community.oracle.com/community/oracle-applications/fusion_applications/customizations__extensions_and_integrations/content
--
Jani Rautiainen
Fusion Apps Developer Relations