1. Connectivity and Knowledge Modules Guide for Oracle Data Integrator
  2. Applications
  3. Oracle Enterprise Resource Planning Cloud

32 Oracle Enterprise Resource Planning Cloud

It is important to understand how to work with Oracle Enterprise Resource Planning (ERP) Cloud in Oracle Data Integrator.

This chapter includes the following sections:

32.1 Introduction

Oracle Enterprise Resource Planning (ERP) Cloud is a suite of cloud applications for finance, project management, procurement, risk management and other core day-to-day activities important in every business, regardless of size, industry or geography.

Oracle Data Integrator (ODI) seamlessly integrates with Oracle Enterprise Resource Planning (ERP) Cloud. Oracle Data Integrator features are designed to work best with ERP Cloud, including reverse-engineering and mappings.

32.1.1 Concepts

The Oracle ERP Cloud technology concepts map the Oracle Data Integrator concepts as follows: An Oracle ERP Cloud Instance corresponds to a data server in Oracle Data Integrator. Within this instance, a schema maps to an Oracle Data Integrator physical schema.

32.1.2 Knowledge Modules

Oracle Data Integrator provides the following Knowledge Modules (KMs) for handling Oracle ERP Cloud data.

Table 32-1 Oracle ERP Cloud Knowledge Modules

Knowledge Module Description

LKM Oracle ERP Cloud to SQL

Extracts data from an existing BI Publisher report and inserts it into a staging table.

LKM Oracle ERP Cloud to File Direct

Extracts data from an existing BI Publisher report and inserts it into a file.

32.2 Prerequisites

The following prerequisites are essential for working with the Oracle ERP Cloud technology.

Make sure you go through the following prerequisites before working with Oracle ERP Cloud:

32.3 Installation and Configuration

Make sure you have read the information in this section before you start working with the Oracle ERP Cloud technology:

32.3.1 System Requirements and Certifications

Before performing any installation, you should read the system requirements and certification documentation to ensure that your environment meets the minimum installation requirements for the products you are installing.

The list of supported platforms and versions is available on Oracle Technology Network (OTN):

http://www.oracle.com/technetwork/middleware/data-integrator/documentation/index.html

32.3.2 Technology Specific Requirements

There are no technology-specific requirements for using Oracle ERP Cloud in Oracle Data Integrator.

32.3.3 Connectivity Requirements

There are no specific connectivity requirements for using Oracle ERP Cloud in Oracle Data Integrator.

32.4 Setting up the Topology

Setting up the topology consists of:

32.4.1 Creating an Oracle ERP Cloud Data Server

Create a data server for the Oracle ERP Cloud technology using the standard procedure, as described in Creating a Data Server of Administering Oracle Data Integrator. This section details only the fields required or specific for defining an Oracle ERP Cloud data server:
  1. In the Definition tab:
    1. Name : Enter a name for the data server definition
    2. WSDL URL : Enter the BI Publisher web service used for the Oracle ERP Cloud instance.
      You can specify either of the following BI Publisher web services in the WSDL URL field:

      • ReportService: Provides methods to interact with BI Publisher Report object, such as to run reports, get information about reports, define and modify reports, and upload report templates. For more details on ReportService, refer to ReportService section of Developer's Guide for Oracle Business Intelligence Publisher.

      • ScheduleService: Provides methods for executing scheduler tasks, such as to schedule report jobs, retrieve report outputs, and manage report history. For more details on ScheduleService, refer to ScheduleService section of Developer's Guide for Oracle Business Intelligence Publisher.

      WSDL URL examples:

      • https://efsdcr12pt05.fs.efops.oraclecorp.com/xmlpserver/services/PublicReportWSSService?wsdl

      • https://efsdcr12pt05.fs.efops.oraclecorp.com/xmlpserver/services/ScheduleReportWSSService?wsdl

  2. Under Connection, enter a user name and password for connecting to the Oracle ERP Cloud instance.

32.4.2 Creating an Oracle ERP Cloud Physical Schema

Create a physical schema for the Oracle ERP Cloud data server using the standard procedure, as described in Creating a Physical Schema in Administering Oracle Data Integrator. This section details only the fields required or specific for defining an Oracle ERP Cloud physical schema:
  1. In the Definition tab:
    1. Directory (Schema): Directory where the BI Publisher report is placed after extract. This location is also required for reverse engineering. The ODI Agent needs access to this location to perform the mapping, while ODI Studio requires it to perform reverse engineering. If it is not possible for this location to be shared by the ODI Agent and ODI Studio, then you will need to set up two separate physical schemas and have a reverse engineering context and a runtime context.
    2. Directory (Work Schema): Directory where log files and temporary files are located (for example, responses from SOAP requests). The ODI Agent needs access to this location.
  2. Check the Default box if you want this schema to be the default one for this data server (The first physical schema is always the default one).
Create for this physical schema a logical schema using the standard procedure, as described in Creating a Logical Schema in Administering Oracle Data Integrator and associate it in a given context.

32.5 Creating and Reverse-Engineering an Oracle ERP Cloud Datastore

This section contains the following topics:

32.5.1 Creating an Oracle ERP Cloud Model

Create an Oracle ERP Cloud model using the standard procedure, as described in Creating a Model of Developing Integration Projects with Oracle Data Integrator.

32.5.2 Creating an Oracle ERP Cloud Datastore

Create a datastore for the Oracle ERP Cloud technology using the standard procedure, as described in Creating a Datastore of Developing Integration Projects with Oracle Data Integrator. This section details only the fields required or specific for defining an Oracle ERP Cloud datastore:
  1. In the Definition tab:
    1. Resource Name: Name of the BI Publisher report file (whose output format is .csv) which is used for reverse-engineering. This name will also serve as the name of the extract file (downloaded from UCM).
  2. Select Delimited as the Storage Format in the Storage tab.
  3. In the Properties tab:
    1. Enter BIPReportLocation in the Key field.
    2. Enter the location of the BI Publisher report file in the Value field corresponding to the key.

    Note:

    The value for BIPReportLocation must not be empty. This refers to the location of the BI publisher report on the BI server, and can be found on the BI server once the corresponding report is open.

32.5.2.1 Defining Parameters for BI Publisher Report

The BI Publisher report can have various parameters to restrict the data coming into the report. You can define all the parameters that are required in the format of a key/value pair in the Properties tab. The name must match the parameter name defined in the BI Publisher report. The value can be either a real value (for example, “PRIMARY”) or an ODI variable (for example, “#PROJ_ERP.PARAMS1”).

32.5.3 Reverse-Engineering an Oracle ERP Cloud Datastore

Oracle ERP Cloud supports delimited file reverse-engineering. To perform a delimited file reverse-engineering:

  1. In the Datastore Editor, go to the Attributes tab.
  2. In the editor toolbar, click Reverse Engineer.
  3. Verify the data type and length for the reverse-engineered attributes. Oracle Data Integrator infers the field’s data types and length from the first record of the file, but may set default values (for example, 50 for the string field length) or incorrect data types in this process. In case of an empty field, data type is set to String with length 50.

    Attributes are created with pre-generated names (C1, C2 and so on) if the file has no header and it is the first non-header record.

  4. Select Save from the File main menu.

32.6 Designing a Mapping

You can use Oracle ERP Cloud as a source of a mapping.

The KM choice for a mapping determines the abilities and performance of this mapping. The recommendations in this section help in the selection of the KM for different situations concerning an Oracle ERP Cloud server.

32.6.1 Loading Data from Oracle ERP Cloud

Oracle ERP Cloud can be used as a source of a mapping. The LKM choice in the Mapping's Loading Knowledge Module tab to load data between Oracle ERP Cloud and another type of data server is essential for the performance of a mapping.

Use the knowledge modules listed in the below table to load data from an Oracle ERP Cloud server to a target or staging area database.

Table 32-2 KMs for loading data from Oracle ERP Cloud

Staging Area/Target Technology KM Notes

SQL

LKM Oracle ERP Cloud to SQL

Extracts data from an existing BI Publisher report and inserts it into a staging table, where data can be loaded into any target using an IKM. The LKM will not return until the BI Publisher job is finished and data is loaded into the staging table. This LKM only supports BI Publisher reports that output in CSV format.

The DELETE_TEMPORARY_OBJECTS LKM option should be set to Yes. This option is set in order to delete temporary objects at the end of the mapping, including the staging table and all the response files from SOAP requests.

File

LKM Oracle ERP Cloud to File Direct

Extracts data from an existing BI Publisher report and inserts it into a file. The LKM will not return until the BI Publisher job is finished and data is loaded into the file. This LKM only supports BI Publisher reports that output in CSV format.

The DELETE_TEMPORARY_OBJECTS LKM option should be set to Yes. This option is set in order to delete temporary objects at the end of the mapping, including the staging table and all the response files from SOAP requests.

Remote Agent Configuration

Data extraction can be done on a remote agent. To perform data extraction on a remote agent:

  1. Create the physical and logical agent using the standard procedure, as described in Creating a Physical Agent of Administering Oracle Data Integrator.

  2. Edit the ODI_HOME/user_projects/domains/base_domain/config/fmwconfig/components/ODI/<Agent_Name>/bin/instance.cmd(.sh) configuration file as follows:

    • No proxy: Comment the following code: 

      #if [ ! -z $IS_AGENT_SCRIPT ] ; then 
# ODI_SSL_PROPERTIES="-Djavax.net.ssl.trustStore=${WL_HOME}/server/lib/DemoTru 
st.jks -Djavax.net.ssl.keyStore=${DOMAIN_HOME}/security/DemoIdentity.jks" 
#else 
#ODI_SSL_PROPERTIES="-Djavax.net.ssl.trustStore=${WL_HOME}/server/lib/DemoTrust 
.jks -Djavax.net.ssl.trustStorePassword=DemoTrustKeyStorePassPhrase" 
#fi

    • With proxy: Set ODI_INSTANCE_JAVA_OPTIONS as follows: 

      ODI_INSTANCE_JAVA_OPTIONS="$ODI_ADDITIONAL_JAVA_OPTIONS $ODI_SSL_PROPERTIES
-Doracle.odi.standalone.agent.useauthenticator=false 
-Dhttp.proxyHost=www-proxy.us.oracle.com -Dhttp.proxyPort=80 
-Dhttps.proxyHost=www-proxy.us.oracle.com -Dhttps.proxyPort=80 
-Dhttp.nonProxyHosts=localhost|127.0.0.0/8|localhost.localdomain|127.0.0.1|::1 
|adc01jjl.us.oracle.com|adc01jjl.us.oracle.com|10.229.118.112"

32.7 Troubleshooting

This section provides information on how to troubleshoot problems that you might encounter when using the Oracle ERP Cloud technology in Oracle Data Integrator.

Please find below the most common problems and the ways to resolve them:

  • Run the BI Publisher report as standalone from the Fusion Apps BI Console and ensure that it executes properly.

  • Execute the BI Publisher report using a SOAP client such as SoapUI to ensure that it can be called successfully from an external client.

  • Review log files in Fusion Console to ensure successful completion or find any errors.

  • Ensure that the path of the BI Publisher report is provided correctly, as this is the most common error.

  • Advanced errors need to be tracked by Fusion System Administrators to look into BI server error logs.