Overview:
This article explains how to use Groovy and connectors to call an external REST API using Oracle EPM Cloud and Middleware technology called MuleSoft.
Context:
There is an option to call both internal and external REST APIs using the Groovy EPM object model. To generate GL files from ERPs and load them into FCCS using data management (an internal API), you write and run a Groovy rule that calls an external MuleSoft REST API.
Required conditions:
Possess access to a Cloud EPM Enterprise Service instance as a Service Administrator.
You must first construct an interface in MuleSoft to cause the stored procedure in ERPs to generate the trail balance file in the appropriate format before you can begin using the MuleSoft APIs, which include the endpoint REST API. Visit Please visit the link below to learn how to create endpoints in MuleSoft’s Partner Manager:
https://docs.mulesoft.com/partner-manager/latest/create-endpoints
If you run into migration errors importing the snapshot, re-run the migration excluding the HSS-Shared Services component, as well as the Security and User Preferences artifacts in the Core component. For more information on uploading and importing snapshots, refer to the Administering Migration for Oracle Enterprise Performance Management Cloud documentation.
Creating Groovy script to download ERP Actual file and loading to Consolidation tool.
Below are the steps we have defined for the above integration.
- Creating a connection in Oracle EPM to call the MuleSoft web services.
- Before calling the endpoints of MuleSoft, it is necessary to authorize them. To achieve this, we need to create a logic that generates a bearer token using the client ID and Secret of MuleSoft API. The script below will help us to generate the bearer token efficiently.We pass the client ID and secret as the body.
- We parse the JSON response output and assign the token to a variable using the method below.
“Jaccess_token” is the bearer token generated from the “Mule” connection by using the above method. - First, we authorize the API endpoint using a bearer token to generate a trail balance file for the requested fiscal year and period. To create a new connection, we start by providing the endpoint details.
- Creating a connection for the endpoint.
- Using a Groovy script to connect and call the endpoint with a bear token created via web services connection.
- A URL passed in the get() method will have parameters to specify the fiscal year and period for general ledger data from ERP. The syntax for the URL is as follows: def url = “/epmtrailbalance/us/summary?year=<FISCAL_YEAR>&month=<PERIOD>”
- The JSON response “jsonResponse1” will contain details of the trial balance files that have been generated in the SFTP location.
- It’s worth noting that we don’t have the file yet in EPM that we need to load into the target consolidation close application. Right now, it’s located in a cloud share location, which is managed by a third-party tool called “MoveIT.” MoveIT serves as a file-sharing interface through which we can transfer or modify the necessary files.
- We have implemented additional measures to ensure that our financial files are compliant and not stored in a cloud file share. We now download the files from the cloud file share and delete them from MoveIT. MOVEit encrypts files and uses secure file transfer protocols such as FTP(S) and SFTP to transfer data. It also provides automation services, analytics, and failover options.
- To harness the power of Oracle EPM integration, we established a new connection for MoveIT, a cloud tool that can be accessed via a RestAPI for file transfer..
- To call MoveIT, you can utilize a Groovy script similar to the one shown below.
- Below is the Groovy script to download the file into the EPM inbox directory.
- After step 6, the JSON response will include the details of the files created in the SFTP location. We will use these details to download the file from the SFTP location to the EPM Inbox/Outbox directory.
- As this is financial data, we must avoid any compliance issues by deleting the cloud-shared file from the SFTP location. Please follow the steps below to do so.
- Now we have the trial balance file in the EPM inbox directory and will be loading the file into FCCS using the data load rule created for transforming the files.
- Initiate data load rule by establishing a connection to call the internal API of data management.
- The script is designed to execute the data load rule that loads ledger files from the Inbox/Outbox directory to FCCS..
- All of the aforementioned procedures require the submission of a parameter. The appropriate mapping of data loading, validation logic, and import formatting are defined in data management.
- Below are the run time prompts which are created to take the params and execute the complete script.
- After creating all the necessary steps and script, ensure to validate and deploy it by clicking on
“Validate and Deploy”.
