Oracle Financials Cloud IntegrationPublished on: Author: Stefan van Glabbeek Category: Oracle
For one of our clients we built a proof of concept for integration with the Oracle Financials Cloud using web services. The client will use BizTalk for the integration, but for our proof of concept we used SOAPUI.
One of the requested services was an outbound interface from Oracle to a data warehouse. A report had already been created in OTBI, the output of which had to be sent to the data warehouse as a CSV file.
According to the Oracle documentation, the web service erpIntegrationService – operation exportBulkData should be used. With this operation, you can specify the necessary process, which will then run in Oracle as a scheduled process. Within the SOAP message to be used, there is also a tag for a callback service. This tag should include a URL of a callback web service that still needs to be created, in this case in BizTalk. When the message is fired to Oracle, Oracle will send a message to the callback service when the OTBI process is ready. From then on, another operation needs to be used: getDocumentForDocumentId. This operation allows us to download the output of our OTBI process and send it to the data warehouse.
However, in this setup we need to create a callback web service, which wasn’t the desired option. An alternative was to run the process and send a polling message to request the status of the process to find out when it was ready. We therefore did the following:
- Used submitESSJobRequest to start the OTBI process. The operation answers with the unique request-id.
- Used getESSJobStatus to retrieve the status of the request-id and repeat this until the status is “succeeded”.
- Used downloadExportOutput to get the output of the report. The output file is an attachment to this message.
Import of journal entries
Another request was an inbound interface with journal entries to be imported into the general ledger. In this case, we used the web service importBulkData. In this case we could make use of a callback service. Again, we didn’t use that tag and left it out of our message. In the operation importBulkData you can specify the ZIP file you want to upload, as well as details about the JournalImportLauncher process (the name and directory of this process and the specification of its parameters). The specified file will be uploaded to UCM, the content management server, and then directly imported as journal entries. The zipped file must be in the import format that Oracle expects.
Since we didn’t use the callback service we simply retrieved the status of the import using the operation getESSJobStatus again. In the event of an error, someone needs to check what went wrong in Oracle. In the operation importBulkData, you also need to specify the content within the content-tags. The content is a base64 version of the ZIP file. The whole outcome of this must be put in the message between the content start and end tag. This appears to be an unnecessary amount of data, but Oracle Support has acknowledged that this content has to be entered into the message. Leaving it out generates an error response.
We used a similar method to create an import for invoices in table AP_INVOICE_OPEN_INTERFACE. Some of the message details differ, but the concept is the same.
The last request was a combination of the two. The output of an OTBI report needed to be imported as journal entries. In our POC we ran the OTBI report to retrieve the output CSV file. The OTBI report was created in such a way that the output was already in the format of the journal entry import. Then, when you have the output file, you can import the same file again using the same method for importing of journal entries described above. You can also import the journal entries one by one, with messaging instead of importing a file. This can be done via the web service JournalImportService.