1. Administering Data Management for Oracle Enterprise Performance Management Cloud
  2. Integrating Data
  3. Loading Data from the Oracle ERP Cloud
  4. Process Description for Integrating Oracle ERP Cloud Data Using a Custom Query

Process Description for Integrating Oracle ERP Cloud Data Using a Custom Query

You can fetch data from the Oracle ERP using a custom query and load it into Oracle Enterprise Performance Management Cloud. The custom query is run against any report from Oracle Business Intelligence Publisher that creates a CSV formatted data file and then load the data to the EPM Cloud. In this case, Data Management executes the report to extract the data and loads it to EPM Cloud.

These are the steps for loading data from the Oracle ERP Cloud into EPM Cloud using Oracle Business Intelligence Publisher data extracts using a custom query.

Data Management integration.

  1. An Oracle ERP Cloud integration requires that you have privileges or user role and data access to all ERP ledgers to be integrated. For more information, see .

  2. Navigate to the Oracle ERP Cloud and execute an BI Publisher report by clicking Schedule New Process.

    Image shows BI Publisher with Schedule New Process selected.

  3. From the Search and Select page, in Name, select a report or extract, and click OK.

    You can select any BI Publisher report just as long as it produces an output file in a CSV format file. Not all reports in Fusion produce a CSV format file.

    Image show report selections

    For example, enter Trial to search for a Trial Balance Report.

    Image shows the Search and Select page

  4. From Process Details, select the parameters for the extract or report, and click Submit.

    In the following example, "Ledger" is Vision Operations and "Amount type" is YTD or PTD.

    Be sure to specify the Accounting Period. The Accounting Period is the parameter that will be set up in Data Management so that the report can be reused.

    Note:

    The integration to Oracle ERP Cloud will fail unless the selected extract on the Oracle ERP Cloud side has one or more bind parameters passed from the EPM Cloud. The bind parameter is a placeholder for actual values in the SQL statement. Bind parameters must be enclosed in tilde (~~) characters. For example, to use "Period" as a bind parameter specify: ~PERIOD~. The name must exactly match the name specified in the SQL query.

    To do this, create a bind parameter directly in the report, which is not referenced in the Data Model query. In Data Management, specify a random string such as "ABC" in the "Report Parameter List" that will be passed to the bind parameter you created in the report definition.

    Image shows search for Accounting Period

    Image shows the Process Details page.

    When the report has been generated, the Output section shows the results of the submission.

    Image shows the output section for the report.

  5. Click Republish, and then from the report output page, click csv.

  6. Select the CSV output file, right click it, and then select Open.

    Image shows the CSV output file of the report.

  7. Save the report locally in your file system.

    Rename the downloaded output file as Appname.csv where Appnam is the intended application name for the "Data Source" application in Data Management, which represents the BI Publisher extract.

  8. Navigate to Data Management, select the Setup tab, and then under Register, select Target Application.

  9. In Target Application, in the summary grid, click Add, and then select Data Source.

  10. From the Select Source screen, select Oracle ERP Cloud (Custom).

    Image shows the Source System screen.

  11. From the Select screen, navigate to the folder where you saved the CSV file, select it and click OK.

    Image shows the Select screen.

    The report is saved as the target application and the Application Name is populated automatically.

  12. In Prefix, specify a prefix to make the application name unique.

    The prefix is concatenated with the file name to form a unique application name. For example, if you want to name an application with the same name as an existing one, you can assign your initials as the prefix.

  13. Click OK.

  14. Click Save.

    Data Management registers the application and returns all the columns in Dimension Details.

  15. Click Application Filters.

    Image shows the Application Filters screen.

  16. In Source System Name, specify the name of the Oracle Financial source system.

    For example, if the name of your source system is "ERP Cloud," specify ERP Cloud.

    You can use an Oracle ERP Cloud or GL source system name or define a new one.

    For more information, see Configuring a Source Connection for an Oracle ERP Cloud Source System.

  17. In Report Name, specify the path and name of the report in the Oracle ERP Cloud.

    Steps 17 - 23 show you how to get the report name from Oracle ERP Cloud. If you already have the report path and name, enter the information in the Report name field (in Data Management) and skip to step 24.

    Image shows the Report Name field on the Application Filters screen.

  18. Navigate to Oracle ERP Cloud, find the report, and select Reports and Analytics to retrieve the parameter information.

    Image shows selecting the Reports and Analytics option

  19. Click Browse Catalog.

  20. Locate and select the extract or report.

    Image shows locating the Trial Balance Report.

  21. Click More and then Properties.

    Image shows the Properties screen.

  22. In the Custom Properties section, scroll down to the path field.

  23. Copy the path (and name) and paste it to the Report Name field when registering the target application in Data Management.

  24. Return to Data Management and in the Report Parameter list, specify the report parameters of the custom query.

    Make sure you specify a random string such as "ABC" in the "Report Parameter List" that will be passed to the bind parameter you created in the report definition. If you create a report with a query that doesn’t have bind parameters passed from the EPM Cloud, the process will fail on the Oracle ERP Cloud side.

    Steps 24 - 25 explain how to get the report parameters from the BI Publisher extract and then populate the Report Parameter List field with them in Data Management.

    Image shows Application Filters screen.

  25. Navigate to Oracle ERP Cloud, and from the Overview page, select the report and click Resubmit.

    This step enables you to view and capture the report parameters defined in the BI Publisher extract or report.

    Image shows resubmitting the report from the Overview page.

    A list of report parameters is generated.

    Image shows the list of parameters.

  26. Copy the report parameters shown in the Warnings window.

  27. Navigate to Data Management and paste the report parameter list from the Warnings window into the Report Parameter List of your custom query.

    The image below shows the report parameter pasted into the Report Parameter List

    Image shows a report parameter list.

  28. Set up the integration mapping between the Oracle ERP Cloud data source and the target application by building an import format.

    See Working with Import Formats.

  29. Define the location used to associate the import format.

    See Defining Locations.

  30. Define data mapping to map the members from the source to target.

    See Creating Member Mappings.

  31. Define the data load rule and specify and source and target options.

    See Defining Data Load Rule Details.

  32. Execute the data load rule in Data Management or run the integration in Data Integration.

    See Running Data Load Rules