Introduction
In this series of three posts, I will show you how to integrate data with Oracle Cloud EPM Account Reconciliation (ARCS) using Oracle APEX and the available ARCS REST APIs.
Part 1 Getting Started and File Handling in ARCS (this post)
Part 2 Running ESS Jobs in ARCS with APEX
Part 3 Extracting Data from ARCS with APEX
What is Oracle ARCS?
Oracle Cloud EPM Account Reconciliation (ARCS) is a cloud-based solution for managing and reconciling accounts in an organization's financial systems. It provides a range of features and tools that help finance teams automate and streamline the reconciliation process, including the ability to import and match transactions, identify and resolve exceptions, and create and track reconciliation tasks.
Getting Started
Base URL for ARCS REST APIs
Before we can use any of the ARCS REST APIs, we must determine the Base URL for our ARCS instance. The easiest way is to log in to ARCS and see the URL in the browser.
If this was the URL in the browser: https://example.epm.us2.oraclecloud.com/arm/faces/StructureHomeTF/StructureHome?_adf.ctrl-state=1b8dwfbxhz_8&_afrLoop=24172333975857591
The Base URL is: https://example.epm.us2.oraclecloud.com
I will refer to the Base URL as <baseURL> from now on.
Authenticating ARCS REST Services
In this post, I will use Basic Authentication to authenticate against the ARCS REST APIs. If your version of ARCS runs on an OCI Gen 2 environment, then you can also use OAuth2 (a much better option). Look at the documentation to learn more about authenticating ARCS REST APIs.
Service Account
For Basic Authentication, you will need to create a 'Service Account' in ARCS. The username and password for this account will be used in the Basic Authentication credentials when calling the ARCS REST APIs.
Note: The Service Account must be set up with the 'Service Administrator' role.
Postman Authentication
Basic Authentication in Postman is pretty straightforward. Let's say we wanted a list of files on the ARCS file system. We can do this by making a GET request on the List Files REST API. The URL is: https://<baseURL>/interop/rest/v2/files/list
The Authentication Tab would look like this:
We also need to set the Content-Type HTTP header:
When you run the List Files service, we get back a list of all of the files on the ARCS file system:
APEX Authentication
Now let's do the same from APEX. In APEX, you must create a Web Credential to securely store the Username and Password for the ARCS Service Account. APEX Web Credentials can securely reference credentials from the APEX_WEB_SERVICE PL/SQL API or standard APEX components like REST Data Sources. Check out my post for more on APEX Web Credentials.
To demonstrate using an APEX Web Credential with our ARCS username and password, we will create an APEX Rest Data Source. APEX REST Data Sources allow you to configure calls to REST services and then utilize the configuration in standard APEX components like forms and reports.
Here is what a REST Data Source looks like, which was created on the ARCS List Files REST API.
When you create an APEX REST Source, it automatically inspects the response from the REST service and creates a Data Profile. You can then adjust the Data Profile as necessary.
You can test the REST Data Source from APEX Builder to confirm you are getting the expected results.
Then, it is a simple matter of referencing the REST data Source from an APEX Interactive Report:
Then, you are 75% of the way to building a file browser for ARCS!
Uploading Files to ARCS
Many inbound ARCS integrations require you first post CSV data files to the ARCS file system before importing them. This makes it essential to know how to generate and upload files to the ARCS file system.
Preparing a CSV File to Upload
For this post, I will take the example of integrating AR Cash Receipts from Oracle e-Business Suite (EBS) to ARCS. The Transaction Matching functionality of ARCS will then be used to match these Cash Receipts against Billing Transactions from a third-party billing system.
Generate a CSV CLOB
Because the starting point is EBS (and, therefore, an Oracle database), we will perform the integration steps using APEX and PL/SQL. To create the CSV file, we must loop through records in the Cash Receipts table (ar_cash_receipts_all) and append each record to a CLOB variable.
Convert the CLOB to a BLOB
As you will see in the next section, the ACS Upload web service expects the CSV file to be a BLOB. ⭐ APEX PL/SQL APIs to the rescue! We can easily convert a CLOB to a BLOB using the apex_util.clob_to_blob function.
Upload a File Using Postman
Let's start by seeing how to upload a CSV file to ARCS using Postman and the Upload API.
The URL includes the file name that will be created on the ARCS file system. You must URL encode the file name in case it has any special characters.
In this example, I have included the extDirPath parameter to upload the file to the inbox folder.
- In Postman, the file is attached to the request body as a binary.
When we run the request, we get a JSON response like the following:
- As long as the
status field in the response equals 0 then the upload was successful. If it is anything other than 0 then the details field will contain an error message.
Upload the CSV File Using APEX
To upload the file from APEX and PL/SQL, we can employ the APEX_WEB_SERVICE PL/SQL API. I have created a helper procedure upload_file in a package arcs_utl_pk to perform the upload. I have included comments in the code to explain each step.
The PL/SQL block below shows how you can use the above procedure to upload a file to ARCS. I have included comments in the code to describe each step.
Note: When building a CLOB by appending VARCHAR2 values, make sure you read this post from Martin D'Souza on how to do this in the most performant way.
Deleting a File
If you upload many files to the ARCS file system, you will want to delete them from time to time. You can use the Delete Files API to delete a file from the ARCS File System.
Delete Using Postman
- The file path and name are passed in the request body in a file called
fileName.
When we run the request, we get a JSON response like the following:
- As long as the
status field in the response equals 0 then the delete was successful. If it is anything other than 0 then the details field will contain an error message. For example, if the file goes not exist, the status field will be 8 and the details field will be Resource does not exist : <FileName>
Delete File Using APEX
Deleting a file from APEX is pretty straightforward. I have created a helper procedure delete_file in a package arcs_utl_pk to perform the upload. I have included comments in the code to explain each step.
The PL/SQL block below shows how you can use the above procedure to upload a file to ARCS. I have included comments in the code to describe each step.
File uploads are pretty quick, for example, I uploaded a 4.6 MB file with 100,000 records in just 2 seconds.
Conclusion
Hopefully, this post demonstrated that APEX is very capable of integrating data with Cloud-based SaaS solutions. In many cases, you do not need a full-blown integration like Oracle Integration Cloud.
In the next two posts of my ARCS series, I will show you how to run ESS jobs in ARCS and how to extract data from ARCS, both using APEX.
🔗 Read More
