Oracle Fusion ERP provides Integration Web Service that enable seamless communication and interaction with the ERP system from external applications or integration platforms. Integration Web Service enables automation of various processes, including File-Based Data Import (FBDI). FBDI allows users to upload data from external files into Fusion ERP, streamlining data migration and integration tasks. Here’s an overview of Oracle Fusion ERP Integration Web Service:
We will go through various steps and stages of automating FBDI templates via ERP integration SOAP service. We are using SOAP Ui here for the demonstration and we will use GL Journal import FBDI template for this scenario.
Process flow to Automate FBDI using Integration Web Service
We will follow this process workflow to automate the FBDI GL journal import in Oracle Fusion ERP by using ERP Integration Service in SOAP Ui. The same process can be followed for other FBDI template.
Download the journal import template https://docs.oracle.com/en/cloud/saas/financials/23d/oefbf/journalimport-3099.html#journalimport-3099
To fine the URL of erp integration service WSDL file, open fusion erp, go to tools and then developer connect and search for ERP Integration service and copy the URL
https://yourerpinstance.oraclecloud.com/fscmService/ErpIntegrationService?WSDL
Load FBDI template data file to UCM
In SOAP UI, start a new project and enter above WSDL URL and prepare payload for uploadFileToUcm operation as shown below. The yellow highlighted needs to be changed with your data and greens highlighted should be used as-is. In <erp:Content> tag, paste Base64 encoded string of FBDI data template csv zip file. You can use following tool to convert zip file to Base64
https://www.motobit.com/util/base64-decoder-encoder.asp
Pass the authorization credentials and run the above operation in SOAP Ui. Copy the document id from response payload and save it for further calls. You can also check in application under File Import and Export if the file loaded in Ucm.
Load data from UCM to Interface table
Next we will use submitESSJobRequest operation to load this file in interface table as shown below.
The job package and definition name can be derived as follows
- Go to Setup and Maintenance
- Open global search and search for Manage%Enterprise%job% and choose Manage Enterprise Scheduler Job Definitions and Job Sets for Financial, Supply Chain Management, and Related Applications
- Search “Load Interface File For Import” and select and click edit
For parameter list highlighted in green in previous snippet, open any previous Load File for Import job and see arguments below for reference to fill in API payload. Argument2 will be replaced with document id.
Run the operation in SOAP Ui and copy the request id from response payload. We will now call getESSJobStatus operation to see if it was successful. Replace yellow highlighted request id with your request id
Submit Import Journal ESS Job
Next we will run import journal job to load the data from interface table to base tables. We will use submitESSJobRequest with follow details.
Replace yellow text by opening job definition “Import Journals” in manage enterprise job page. For parameters, open any previously run instance of import journal job. Choose ledger name/id etc. as shown in green highlighted text. Call the API and note down the process id. Check the status of GL Journal import job by calling getESSJobStatus operations as shown previously during interface file loading.
Download Import Journal Job Output
Now we will download the output of Import Journal job and for that we need child process id of import journal job. To get the child id, we will call getESSJobExecutionDetails. Replace yellow highlighted with your request id. Download the log file and change its extension to .zip. Then unzip and open log file and copy sub-request (child) id.
Now we have child process id, we will call the operation downloadExportOutput. We can also call downloadESSJobExecutionDetails (use filetype as out) operation for the same.
Save attachment as zip file from response payload and open the text file and view output.
Conclusion
Automating FBDI using Oracle Fusion ERP Integration Web Service streamlines data import processes, reduces manual effort, and improves data accuracy. By integrating FBDI automation into your workflow using Integration Web Services, organizations can achieve greater efficiency and agility in data management and integration tasks.