Skip navigation.

Angelo Santagata

Syndicate content
Fusion Middleware / Cloud Integration Architect - If its middleware, or PaaS for SaaS, then I'm interested!
Updated: 10 hours 34 min ago

Accessing Fusion Data from BI Reports using Java

Sat, 2016-04-30 03:57
Introduction

In a recent article by Richard Williams on A-Team Chronicles, Richard explained how you can execute a BI publisher report from a SOAP Service and retrieve the report, as XML, as part of the response of the SOAP call.  This blog article serves as a follow on blog article providing a tutorial style walk through on how to implement the above procedure in Java.

This article assumes you have already followed the steps in Richard's blog article and created your report in BI Publisher, exposed it as a SOAP Service and tested this using SOAPUI, or another SOAP testing tool.

Following Richards guidance we know that he correct SOAP call could look like this

<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:pub="http://xmlns.oracle.com/oxp/service/PublicReportService">
   <soap:Header/>
   <soap:Body>
      <pub:runReport>
         <pub:reportRequest>
            <pub:reportAbsolutePath>/~angelo.santagata@oracle.com/Bi report.xdo</pub:reportAbsolutePath>
            <pub:reportRawData xsi:nil="true" >true</pub:reportRawData>
            <pub:sizeOfDataChunkDownload>-1</pub:sizeOfDataChunkDownload>
            <pub:flattenXML>true</pub:flattenXML>
            <pub:byPassCache>true</pub:byPassCache>
         </pub:reportRequest>
         <pub:appParams/>
      </pub:runReport>
   </soap:Body>
</soap:Envelope>

Tip :One easy way to determine the reports location is to run the report and then examine the URL in the browser.

 

Implementing the SOAP call using JDeveloper 11g

We can now need to implement the Java SOAP Client to call our SOAP Service. For this blog we will use JDeveloper 11g, the IDE recommended for extending Oracle Fusion, however you are free to use your IDE of choice, e.g. NetBeans, Eclipse, VI, Notepad etc, the steps will obviously be different.

Creating the project

Within JDeveloper 11g start by creating a new Application and within this application create two generic projects. Call one project “BISOAPServiceProxy” and the other “FusionReportsIntegration”. The "BISOAPServiceProxy" project will contain a SOAP Proxy we are going to generate from JDeveloper 11g and the "FusionReportsIntegration" project will contain our custom client code. It is good practice to create separate projects so that the SOAP Proxies resides in its own separate project, this allows us to regenerate the proxy from scratch without affecting any other code.

Generating the SOAP Proxy

For this example we will be using the SOAP Proxy wizard as part of JDeveloper. This functionality generates a static proxy for us, which in turn makes it easier to generate the required SOAP call later.

  1. 1. With the BISOAPService project selected, start the JDeveloper SOAP Proxy wizard.
    File-> New-> Business Tier-> Web Services-> Web Service Proxy
  2. Proxy1
  3. 2. Click Next
  4. 3. Skipping the first welcome screen, in step 2 enter the JAX-WS Style as the type of SOAP Proxy you wish to generate in step 3 enter the WSDL of your Fusion Application BI Publisher webservice WSDL. It’s best to check this URL returns a WSDL document in your web browser before entering it here. The WSDL location will normally be something like : http://<your fusion Applications Server>/xmlpserver/services/ExternalReportWSSService?wsdl
  5. Proxy2
  6. It's recommended that you leave the copy WSDL into project check-box selected.
  7. 4. Give a package name, unless you need to it's recommended to leave the Root Package for generated types to be left blank
  8. proxy3
  9. 5. Now hit Finish
Fixing the project dependencies

We now need to make sure that the “FusionReportsIntegration” is able to see classes generated by the  “BISOAPServiceProxy” proxy. To resolve this in JDeveloper we simply need to setup a dependency between the two projects.

  1. 1. With the FusionReportsIntegration project selected, right-mouse click on the project and select “Project properties
  2. 2. In the properties panel select Dependencies
  3. 3. Select the little pencil icon and in the resulting dialog select “Build Output”. This selection tells JDeveloper that “this project depends on the successful build output” of the other project.
  4. 4. Save the Dialog
    dependancies1
  5. 5. Close [OK] the Project Properties dialog
  6. 6. Now is a good time to hit compile and make sure the SOAP proxy compiles without any errors, given we haven't written any code yet it should compile just fine.
Writing the code to execute the SOAP call

With the SOAP Proxy generated, the project dependency setup, we’re now ready to write the code which will call the BI Server using the generated SOAP Proxy

  1. 1. With the Fusion Reports Integration selected , right mouse Click -> New -> Java -> Java Class
    javacode
  2. 2. Enter a name, and java package name, for your class
  3. 3. Ensure that “Main Method” is selected. This is so we can execute the code from the command line, you will want to change this depending on where you execute your code from, e.g. A library, a servlet etc.
  4. 4. Within the main method you will need to enter the following code snippet, once this code snippet is pasted you will need to correct and resolve imports for your project.
  5. 1.	ExternalReportWSSService_Service externalReportWSSService_Service;
    2.	// Initialise the SOAP Proxy generated by JDeveloper based on the following WSDL xmlpserver/services/ExternalReportWSSService?wsdl
    3.	externalReportWSSService_Service = new ExternalReportWSSService_Service();
    4.	// Set security Policies to reflect your fusion applications
    5.	SecurityPoliciesFeature securityFeatures = new SecurityPoliciesFeature(new String[]
    6.	{ "oracle/wss_username_token_over_ssl_client_policy" });
    7.	// Initialise the SOAP Endpoint
    8.	ExternalReportWSSService externalReportWSSService = externalReportWSSService_Service.getExternalReportWSSService(securityFeatures);
    9.	// Create a new binding, this example hardcodes the username/password, 
    10.	// the recommended approach is to store the username/password in a CSF keystore
    11.	WSBindingProvider wsbp = (WSBindingProvider)externalReportWSSService;
    12.	Map<String, Object> requestContext = wsbp.getRequestContext();
    13.	//Map to appropriate Fusion user ID, no need to provide password with SAML authentication
    14.	requestContext.put(WSBindingProvider.USERNAME_PROPERTY, "username");
    15.	requestContext.put(WSBindingProvider.PASSWORD_PROPERTY, "password");
    16.	requestContext.put(WSBindingProvider.ENDPOINT_ADDRESS_PROPERTY, "https://yourERPServer:443/xmlpserver/services/ExternalReportWSSService");
    
    17.	// Create a new ReportRequest object using the generated ObjectFactory
    18.	ObjectFactory of = new ObjectFactory();
    19.	ReportRequest reportRequest = of.createReportRequest();
    20.	// reportAbsolutePath contains the path+name of your report
    21.	reportRequest.setReportAbsolutePath("/~angelo.santagata@oracle.com/Bi report.xdo");
    22.	// We want raw data
    23.	reportRequest.setReportRawData("");
    24.	// Get all the data
    25.	reportRequest.setSizeOfDataChunkDownload(-1); 
    26.	// Flatten the XML response
    27.	reportRequest.setFlattenXML(true);
    28.	// ByPass the cache to ensure we get the latest data
    29.	reportRequest.setByPassCache(true);
    30.	// Run the report
    31.	ReportResponse reportResponse = externalReportWSSService.runReport(reportRequest, "");
    32.	// Display the output, note the response is an array of bytes, you can convert this to a String
    33.	// or you can use a DocumentBuilder to put the values into a XLM Document object for further processing
    34.	System.out.println("Content Type="+reportResponse.getReportContentType());
    35.	System.out.println("Data ");
    36.	System.out.println("-------------------------------");
    37.	String data=new String (reportResponse.getReportBytes());
    38.	System.out.println(data);
    39.	System.out.println("-------------------------------");
  6. Going through the code
  7.  LineWhat does it do1-3This is the instantiation of a new class containing the WebService Proxy object. This was generated for us earlier5Initialise a new instance of a security policy object, with the correct security policy, for your Oracle Fusion server . The most common security policy is that of “oracle/wss_username_token_over_ssl_client_policy", however your server maybe setup differently8Calls the factory method to initialise a SOAP endpoint with the correct security features set9-16These lines setup the SOAP binding so that it knows which endpoint to execute (i.e. the Hostname+URI of your webservice which is not necessarily the endpoint where the SOAP Proxy was generated, the username and the password.In this example we are hard coding the details because we are going to be running this example on the command line. If this code is to be  executed on a JEE server, e.g. Weblogic, then we recommend this data is stored in the Credential store as CSF keys.17-19Here we create a reportRequest object and populate it with the appropriate parameters for the SOAP call. Although not mandatory its recommended that you use the objectFactory generated by the SOAP proxy wizard in JDeveloper.21This set the ReportPath parameter, including path to the report23This line ensures we get the raw data without decoration, layouts etc.25By default BI Publisher publishes data on a range basis, e.g. 50 rows at a time, for this usecase we want all the rows, and setting this to -1 will ensure this27Tells the webservice to flatten out the XML which is produced29This is an optional flag which instructs the BI Server to bypass the cache and go direct to the database30This line executes the SOAP call , passing the “reportReport” object we previously populated as a parameter. The return value is a reportResponse object34-39These lines print out the results from the BI Server. Of notable interest is the XML document is returned as a byte array. In this sample we simply print out the results to the output, however you would normally pass the resulting XML into Java routines to generate a XML Document.

 

 

Because we are running this code from the command line as a java client code we need to import the Fusion Apps Certificate into the Java Key Store. If you run the code from within JDeveloper then the java keystore is stored in <JDeveloperHome>\wlserver_10.3\server\lib\DemoTrust.jks

Importing certificates

 

  1. 1. Download the Fusion Applications SSL certificate, using a browser like internet explorer navigate to the SOAP WSDL URL
  2. 2. Mouse click on the security Icon which will bring you to the certificate details
  3. 3. View Certificate
    4. Export Certificate as a CER File
  4. 5. From the command line we now need to import the certificate into our DemoTrust.jks file using the following commandkeytool -import -alias fusionKey -file fusioncert.cer -keystore DemoIdentity.jks

jks

Now ready to run the code!

With the runReport.java file selected press the “Run” button, if all goes well then the code will execute and you should see the XML result of the BI Report displayed on the console.

 

Loading Data into Oracle Cloud ERP R10 using the new LoadAndImportData operation

Sat, 2016-04-30 03:54

 

Introduction

As part of Oracle ERP cloud release 10 a new SOAP function has been made available to our customers which greatly simplifies the loading of ERP data using the batch oriented SOAP Services.

This article aims to give the reader, details of this new SOAP Service and how it helps in loading data files into Oracle ERP cloud.

Assuming the input file has been already produced, loading the data into Oracle ERP cloud service is traditionally a multi-step process.

The typical "happy" path is :

  1. 1. Load the file into Oracle Fusion ERP UCM service
  2. 2. Execute the first ESS Job which transfers the file from UCM to the Oracle ERP interface tables
  3. 3. Using a polling technique check to see when the ESS job has finished transferring the file into the interface tables
  4. 4. Execute a second ESS job, which transfers the file from Oracle ERP interface tables to the Oracle ERP data object tables
  5. 5. Use a polling technique to check to see when the file has been processed
    6. Finally execute a call to the downloadESSJobExecutionDetails() operation to download a log file so you can check for success,or any errors, which need dealing with.

Whilst this approach appears attractive, as it allows the developer a great deal of control of the process, in truth this internal processing should be something that the SaaS application [Oracle ERP Cloud] should manage and provide feedback to the developer when things finish

New SOAP method in R10

As of Oracle ERP cloud Release 10 there is a new API called "loadAndImportData", which is held within the ERPintegrationService, ( https://(FinancialDomain,Financial Common)/publicFinancialCommonErpIntegration/ErpIntegrationService?WSDL). This service has been specifically created to simplify the loading of data into Oracle ERP Cloud service by allowing you the ability to submit a file which is then automatically taken through the various stages of processing within Oracle ERP Cloud, without the user needing to execute each step of the process manually.

The operation takes the following parameters :

Element NameTypeDescriptiondocumentDocument Information SDOList of elements, each containing the details of the file to be uploaded. The details include the file content, file name, content type, file title, author, security group, and accountjobListProcess Details SDOList of elements, each containing the details of the Enterprise Scheduling Service job to be submitted to import and process the uploaded file. The details include the job definition name, job package name, and list of parametersinterfaceDetailsstringThe interface whose data is to be loaded.notificationCodestringA two-digit number that represents the manner and timing in which a notification is sent.callbackURLstringThe callback URL of the service implemented by customers to receive the Enterprise Scheduling Service job status on completion of the job

 

Diving into the Details

A sample soap payload, which imports journal records, looks like the following :

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/" xmlns:erp="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/">
   <soapenv:Header/>
   <soapenv:Body>
      <typ:loadAndImportData>
         <typ:document>
            <erp:Content>  UEsDBBQAAAAIAMG2b0hvJGqkiAAAAKsBAAAPAAAAR2xJbnRlcmZhY2UuY3N2tY+xDoJADIZ3E9+hD9BIexojIwQWB0wU49yQqgMcyYnv7wELiYEwQIe2/9+mzZelD2Q0xMeA9gExxlKKLRRyJ/bzVIdXrepmoO+3ZLgfoU9EhGzYtA11ajTCcNePc5UKIuhKLE3xPnnz4r+8FM7111kpW+c/eOp8miXbzXJQB2aeAQU91rpUP1BLAQIUABQAAAAIAMG2b0hvJGqkiAAAAKsBAAAPAAAAAAAAAAAAIAAAAAAAAABHbEludGVyZmFjZS5jc3ZQSwUGAAAAAAEAAQA9AAAAtQAAAAAA</erp:Content>
            <erp:FileName>LoadGLData1.zip</erp:FileName>
            <erp:ContentType>zip</erp:ContentType>
            <erp:DocumentTitle>ImportJournalEntry</erp:DocumentTitle>
            <erp:DocumentAuthor></erp:DocumentAuthor>
            <erp:DocumentSecurityGroup>FAFusionImportExport</erp:DocumentSecurityGroup>
            <erp:DocumentAccount>fin$/journal$/import$</erp:DocumentAccount>
         </typ:document>
         <typ:jobList>
      
           <erp:JobName>oracle/apps/ess/financials/generalLedger/programs/common,JournalImportLauncher</erp:JobName>
           <erp:ParameterList>1061,Balance Transfer,1,123,N,N,N</erp:ParameterList>
         </typ:jobList>
         <typ:interfaceDetails>15</typ:interfaceDetails>
         <typ:notificationCode>50</typ:notificationCode>
         <typ:callbackURL>http://somecallbackserver.domain.com/mycallback</typ:callbackURL>
      </typ:loadAndImportData>
   </soapenv:Body>
</soapenv:Envelope>

Now lets dive into each element and explain what it represents and more importantly where you derive the data from :

  • Document : This element contains the details of the document to be uploaded
    • content : This is the document itself, base64 encoded and in-lined in the soap payload. There  are many tools on the internet to base64encode a document and in Java there is a  helper Base64.Encoder which does this for you.
    • contentType : This value should be set to "zip", this means your files must be zipped before base64encoding them and in lining them above
    • documentTitle : A title for the document, this is so you can find it in UCM later if you need to.
    • documentSecurityGroup : Needs to be set to a security group that secures the document, for our example we've used FAFusionImportExport
    • documentAccount : This needs to be set to the correct account depending on the data which is being loaded. For our journal import we need to set the account to fin$/journal$import. This is the same Account used when you "manually" upload files into Oracle ERP for loading.. If you don't know the what UCM account your data should be loaded into you can find it by going into File Based Data Import for Financials Cloud documentation and searching for your data object. In our case the object is "Journal Import" and the documentation states that the UCM account is fin/journal/import. For our SOAP Service we need to prefix each "/" with a "$"
  • jobList : This element contains data describing the job which needs to be executed for this batch upload
    • jobName : This is the "package name" of the ESS job which loads the data into Oracle ERP Cloud. You can find this in FusionAppsOER or in the documentation. The format for the field is "packageName,jobName"
    • parameterList : This is the list of parameters which the job requires to execute. The parameters depend on the ESS Job being executed. In our case the ESS Job is for journals and in our case the parameters are Data Access Set ID, Source (Balance Transfer), LedgerID, GroupID (aka BatchID) etc
    • [caption id="attachment_37609" align="alignnone" width="300"]journalimport Example from FusionOER[/caption]
  • interfaceDetails :  This is set to 15 for journals  (no longer needed in R11)
  • notificationCode : This is set to 50 (no longer needed in R11)
  • callBackURL :  The magic about this service is that it executes all of the ESS jobs in the background and then executes a callback to your service when its finished. This response contains the "last" ESSjob ID executed so you can then query the status of the jobs using the downloadESSJobExecutionDetails method.

 

Handling the callback
  • As mentioned earlier, the loadAndImportData operation does all heavy lifting, and orchestration , within Oracle Fusion ERP SaaS, the only thing the developer needs to implement [optionally but very desirable], is a webservice endpoint which manages the callback generated by the ESS framework. This service needs to implement the ESS onJobCompletion operation, which will deliver three pieces of data, the requestId of the ESSJob which completed, the state of the process and a status message. For more information on handing the ESS callbacks please see this documentation link, and additionally if you are using BPEL to execute the SOAP Service then this documentation link may be of interest (Section 11.7.7 : Receive the Job Completion Status)

 

Conclusion

The new LoadAndImportData operation will most certainly make importing of data into Oracle ERP a much simpler process, its biggest advantage is that developers will easily be able to trigger the import with a single SOAP call which can easily be done without the need to worry about orchestration. There are however scenarios when you would you probably use the traditional , step by step method, for example when you want to control/trigger external notification providers that each step has been executed at the macro level or when the import file size is very large (>100Mb). In the latter case you might want to upload the file into Oracle UCM using UCMs native IdcWebService, which supports MTOM and then execute the ESS jobs in order as we have traditionally done.

 

 

Working with MCS Offline? Need some hints and tips?

Thu, 2016-03-31 10:40

Mobile Cloud Service
Another collection of videos I was highlighted today on MCS on how to do data offline sync. Awesome stuff , one note however is that the videos are IOS based today but Im being told that the Android versions are enroute.... These videos provide a end2end discussion on how to work with these capabilities not just the APIs themselves.. Very nice.... Also remember to read the docs which cover both Android and IOS

Using ICS to integrate SaaS Applications??

Thu, 2016-03-24 08:17

Are you doing SaaS, or EBS,  integrations and using Oracle Integration Cloud Service (ICS)?

Do you need some inspiration? Well this is your lucky day!

Below you'll find a collection of ICS Integration videos , produced by our product managers and our UA development team which go though, step by step, how to integrate  two SaaS applications

 

There are plenty more videos available at the Oracle Help Centre here 

Make sure you comment on the videos in youtube so that the developers can enhance these and any future youtube videos

Angelo 

Integrating Oracle Document Cloud and Oracle Sales Cloud, maintaining data level business object security

Thu, 2016-01-14 11:10
Just written an article on http://www.ateam-oracle.com/ on how one would integrate SalesCloud and Documents cloud together. Interested? go and check it out!
Link to article

Adding a contact to a sales cloud account

Thu, 2016-01-14 11:04

A common question is how do you add a contact to a Account in Sales Cloud? Ive seen developers (including me) try to issue a mergeOrganizaiton on the org, however this is not correct. In Oracle Fusion , Contacts are not "addded" to accounts but they are "related" to accounts.. Therefore to add a contact to a account you need to add a "Relationship" which links the contact and the organization. Here's a sample payload

WSDL <host>/crmCommonSalesParties/RelationshipService?wsdl

SOAP Request Payload

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/crmCommon/salesParties/relationshipService/types/" xmlns:rel="http://xmlns.oracle.com/apps/crmCommon/salesParties/relationshipService/">

   <soapenv:Header/>
 <soapenv:Body>
      <typ:createRelationship>
         <typ:relationship>
<!-- ContactId -->
            <rel:ObjectPartyId>300000000943126</rel:ObjectPartyId>
<!-- AccountId -->
            <rel:SubjectPartyId>300000000943078</rel:SubjectPartyId>
            <rel:RelationshipType>CONTACT</rel:RelationshipType>
            <rel:RelationshipCode>CONTACT</rel:RelationshipCode>
            <rel:CreatedByModule>HZ_WS</rel:CreatedByModule>
            <rel:Status>A</rel:Status>
         </typ:relationship>
      </typ:createRelationship>
   </soapenv:Body>
</soapenv:Envelope>

Groovy Script Example

def addContact =
[
        ObjectPartyId        : '300000000943126', /*Contact */
        SubjectPartyId       : '300000000943078', /*Account */
        RelationshipType  : 'CONTACT',
        RelationshipCode  : 'CONTACT',
        CreatedByModule : 'HZ_WS',
        Status  : 'A'
];

adf.webServices.RelationshipService.createRelationship(addContact);

 

 

 

 

 

Intro to JCS

Mon, 2016-01-11 06:04
v\:* {behavior:url(#default#VML);} o\:* {behavior:url(#default#VML);} w\:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);} Angelo Santagata Angelo Santagata 3 3 2016-01-11T12:00:00Z 2016-01-11T12:03:00Z 1 62 356 2 1 417 15.00 Clean Clean false false false false EN-GB X-NONE X-NONE MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Calibri",sans-serif; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-fareast-language:EN-US;}

Happy New Year all!!!

 

To start this year's blogging I thought id highlight a nice and easy quick win on getting started with Oracle Java Cloud Service..  This link https://www.youtube.com/watch?v=5DHsE2x5mks takes you to a youtube video which I found very clear and easy to follow.

 

Enjoy