Oracle Financials and ARCS: A Two-Part Journey into the World of Data Integrations

Co-authored with Rebecca Green.

As you know, one of the key components in any implementation is the data. Users cannot budget without seeing some sort of trend from past months. Users cannot close their books without Actuals flowing into their consolidation application. Reconciliations cannot be performed without the data from an ERP system. Join us on this two-part journey as we explore (1) connections to Oracle Financials, including drill-back functionality, and (2) the new Custom integration feature within Data Management.

PART 1: Connection to the GL Balances Cube in Oracle Financials Including Drill-Through

This blog will discuss the steps for creating a connection between the GL Balance Cube (Essbase) in Oracle Financials and an ARCS application, as well implementing the drill-back feature.

Let’s begin on the Setup tab in Data Management. For those that are unfamiliar, Data Management is a data integration (ETL) tool that is used for loading data from one source to another; for our example we will focus on integrations to ARCS. From the Source System page, we have the option to load flat files (fixed width or delimited files) as well as connect to Oracle ERP Cloud (Oracle Financials).

Source System

Connecting to Oracle Financials requires you to configure a source connection which includes providing a permissioned user name and password, as well as the Oracle Financials URL. Begin by clicking the Add button, to create a new source system.

In the Details section, enter a Source System Name. We are connecting to Oracle Financials, so we used ‘Oracle ERP Cloud.’

From the Type drop-down list, select Oracle Financials Cloud.

Optional: enter a Source System Description.

In the Drill Through URL text box, enter “R13”. Entering R13 lets Data Management know to use the built-in Oracle Financials Cloud Release 13 URL format.

Next, you must configure the source connection to Oracle Financials. To do this, click the Configure Source Connection button at the top of the page.

Enter the User Name and Password of the account that has the appropriate permissions to Oracle Financials. Typically a service account would be created and used for the data integration. NOTE: Each time the password expires, you will need to update the password on the Configure Source Connection dialog box.

Enter in the Web Service URL. For example, https://server-test.fa.us6.oraclecloud.com

Click the Test Connection button to make sure the credentials are correct and you can successfully connect using the URL. If successful, a dialog box displays. Click OK to close the message.

Click Configure and return to the Source System window.

Next, you will initialize the connection by clicking the Initialize button. A dialog box displays indicating the initializing process has been submitted.

You can view the request in Oracle Financials.

As well as from Workflow > Process Details from within Data Management.

Target Application

After configuring your source system options continue on to creating the target applications. By default, initializing the Source System connection to Oracle Financials created a Target Application with the name of the Oracle Financials ledger. From the Application Details section you can see the Dimension column has been populated with all applicable dimensions from the connected ledger. This will be the source for our GL Balances for our ARCS integration. No other actions are required on this screen.

Import Format

The Import Format is used to identify each field (column) in the data file and connects the GL Balance cube created in the Target Application configuration step to the ARCS application. Click the Add button to add a new Import Format.

In the Details section, enter in a Name for the Import Format. Then click the Search button to select a Source.

In this example, our Target Application is Ledger. Select Ledger and click OK.

By selecting Ledger, the fields applicable to a source type of File are removed. Lastly, select Account Reconciliation Manager for the Target.

In our example, no specific Mappings are required for this Import Format. Next, create a Location. Click the Save button when you are done with your selections.

Location

Locations define a set of staging tables used to house the data imported from the source system. Each location is assigned an Import Format which aligns each field of the source data records to fields in the staging table. The selected import format defines the Source and Target for the data; in our example, Ledger and the ARCS application respectively. Click the Add button to add a new Location.

Enter a Name for the Location. Click the Search button to locate the Import Format created in the prior step. Click the Save button when you are done with your selections.

Period Mapping

Period Mapping creates the mapping relationship between the source system periods and the target application periods. To begin you must define the Global Mappings.

When you have multiple target applications, which is the case with ARCS (ARCS and Transaction Matching connections), you must also define the Application Mappings.

Category Mapping

Global Category mapping are used to define mappings that cross multiple applications. In our example, we have left the default mappings.

Data Load Rule

From the Data Load Rule section you can identify the filters needed to pull the data from the desired intersections on the GL Balance Cube (Essbase) to the ARCS application for the Location you just created. Begin by clicking the Location name in the POV bar located at the bottom of the window.

Next, click the Add button to add a new Data Load Rule. Enter a Name and select the Category. Since we are connecting to ARCS, the category corresponds to the Currency Buckets you have created in your ARCS application.

From the Source Filters section, click the blue ellipses button to make the applicable selections for each relevant dimension of the source ERP (Segments). Click the Save button when you are done with your selections.

After creating the Data Load Rule for our Essbase Location, you can import the data by clicking the Execute button. Make your selections, then click the Run button.

A dialog box displays indicating that the job has been submitted. You can view its process from the Process Details screen.

After the data load has completed successfully, as indicated by the green checkmarks, you can view the data from the Data Load Workbench screen.

Drill Through

As mentioned above, by making a connection to the Oracle Financials Essbase cube, we can easily drill back to the source of our data. Drill through can be achieved using two different methods: from Data Management or from within our ARCS application.

Drill Through from Data Management

From the Data Load Workbench screen you can click on any value in the Source Amount column and select Drill Through to Source from the drop-down list.

This action opens the transaction in Oracle Financials. Once inside Oracle Financials, you can continue to drill on drillable values (blue values).

Drill Through from ARCS

From within the ARCS application you can drill back to Oracle Financials from an open reconciliation by clicking on a drillable value (blue value).

Clicking on the drillable value opens the Amount Details window that displays the data imported to Data Management. From this window, click the Gear icon on the applicable row, and then select Drill Through to Source.

This action opens a new window in Oracle Financials. Again, clicking on a blue, drillable value, let’s you continue to drill deeper into the transaction.

In this example, we can drill down to the actual receipt that was uploaded to Oracle Financials.

This concludes Part 1 of our two-part series, connecting to the GL Balances Cube in Oracle Financials including Drill-Through capability. Next in the series is Part 2: Custom Integrations; the Options are Endless.