Introduction

Subledger integration for Balance Comparison involves loading subledger balances. These balances can be imported from out-of-the box reports, available in Oracle ERP Cloud, through direct integrations in Oracle EPM Cloud.

Objectives

In this tutorial, you perform the following tasks before creating an integration:

  1. Perform preliminary tasks in Oracle Transactional Business Intelligence (OTBI).
  2. Create integrations in EPM Cloud Account Reconciliation

Prerequisites

Before starting this tutorial, you should have:

  • A working knowledge about EPM Cloud Account Reconciliation.
  • Access to the Oracle Fusion Cloud ERP instance.
OOB Reports

Preliminary tasks in Oracle Transactional Business Intelligence

The following tasks should be performed in Oracle Transactional Business Intelligence (OTBI), prior to creating an integration in EPM Cloud:

  • Set the default format to CSV.
  • Note down the Job Path and Name.
  • Run (Schedule) the Report.
  • Note down the parameters.
  • Download the report.

We will go through these steps using the Payables Trial Balance Report as an example.

Setting the default format to CSV

Integrations within EPM Cloud work best with CSV files. You need to set the default output format of your report as CSV.

Note:

Although the default format is set to CSV, while running the report manually in Oracle Fusion Cloud, one can always choose the required output format among HTML, PDF, RTF, XLSX, PPTX and CSV.

In Oracle Fusion, you can set the default output as CSV by performing the following steps:

  1. From the OTBI interface, click the Catalog tab and then then navigate to Financials > Payables > Period Close.
    2. Period Close
  2. Click Edit under Payables Trial Balance Report.
    3. Click Edit
  3. Click View a list.
    4. Click View a List
  4. For the required layout, select Data (CSV) as the Default Format and select the Default Layout checkbox.
    5. Select Data (CSV)
  5. Click the Save icon.
    6. Click Save

Noting down Job Path and Job Name

To note down a job path and job name, perform the following steps:

  1. Click More under Payables Trial Balance Report and select Properties.
    2. Click More
  2. Make a note of the details provided under the Custom Properties section. Click OK.
    3. Custom Properties

Running (Schedule) a Report

You can run (schedule) a report by using the following steps:

  1. From the Oracle Fusion home page, click the Navigator and select Reports and Analytics under Tools.
    2. Select Reports and Analytics
  2. Navigate to the Financials > Payables > Period Close folder, click the Actions icon for Payables Trial Balance Report and select Schedule.
    3. Select Schedule
  3. In the Basic Options section, enter the required Liability Account As-of Date. Optionally, you can select a Ledger.
    4. Enter the Liability Account As-of Date
  4. Click Submit.
    5. Click Submit

Noting down Parameters

To note down parameters, perform the following steps:

  1. From the OTBI interface, click the Home tab and select Report Job History.
    2. Select Report Job History
  2. Click the first entry under Report Job Name to open the latest report job for Payables Trial Balance Report.
    3. Click the required Report Job Name
  3. In the Report Parameters section, you can see that the Liability Account As-of Date entered earlier is visible here. There is also a Ledger ID and other parameters. Make note of them.
    4. Report Parameters

Download the report in csv

To download the report in csv, perform the following steps:

  1. Continuing from the previous section, click the Republish icon.
    2. Select Report Job History
  2. Click the Actions icon. Click Export, and then select CSV. The CSV report will be downloaded locally.

    3. Optionally, rename the downloaded report as PayablesTrialBalanceReport.csv. This filename will appear as the name of the application in Data Integration.

    Note:

    When you open the CSV report, column headers that have a P_ prefix are Report Parameters from the previous section.
    Click the required Report Job Name

Creating a Subledger Integration

In EPM Account Reconciliation, you can create a subledger integration by performing the following steps:

  1. From the Account Reconciliation homepage, click on Application, and then select Data Exchange.
    2. Select Data Exchange
  2. Click the Actions drop-down and select Applications.
    3. Select Applications
  3. Click the Add icon.
    4. Click Add
  4. In the Create Application pop-up window, choose Category as Data Source, Type as Oracle ERP Cloud (Custom). For File, click the File Explorer icon, upload the report from the previous section into Data Integration, and then select the file. Prefix is optional. Click OK to continue.
    5. Select Data (CSV)
  5. Select the application, click the Actions icon, and then select Edit Options.
    6. Click Actions
  6. Add LEDGER_ID as a parameter by clicking the Add icon, select Property Level as Application and Integration, and then click Save.
    7. Click Save
  7. Click the application to open it.
    8. Click the application
  8. The columns from the report will appear under the Dimensions tab.
    9. View the Dimenstions tab
  9. In the Options tab, provide the required properties:
    • Connection Name: ERP Cloud Connection Name
    • Execution Method: ESS Job
    • ESS Job Path: Job Path from preliminary tasks in OTBI
    • ESS Job Name: Job Name from preliminary tasks in OTBI
    • Parameter List: Report Parameters from preliminary tasks in OTBI

    Click Save to proceed.

    Click Save