X

Welcome to All Things Data Integration: Announcements, Insights, Best Practices, Tips & Tricks, and Trend Related...

Oracle Data Integrator (ODI) to Load into Fusion Applications (Oracle ERP Cloud)

Sandrine Riley
Product Management - Data Integration Solutions at Oracle

Guest Author:  Srinidhi Koushik, Senior Sales Consultant at Oracle

Oracle Data Integrator (ODI) is the best tool to extract and load onto Oracle ERP Cloud given its dexterity to work with complex transformation as well as support for heterogenous source / target format. ODI is available on-premises as well as in the Oracle Cloud with Data Integration Platform Cloud and ODI Cloud Service (ODI-CS).
In this article we will walk you through how to upload data into Oracle ERP Cloud

Overview of the integration process

In this series of blogs we discuss the two facets of Integration: Extraction and Loading. In this specific article we will concentrate on Loading data onto ERP Cloud using the following process:

a) Create a data file based on the standard Template (list of Mandatory columns)

    required by the table within Oracle ERP cloud

        List of attributes or mandatory columns needs to be derived and a template

     created. List of columns can be seen from the link below

        https://docs.oracle.com/en/cloud/saas/financials/r13-update17d/oefbf/Customer-Import-30693307-fbdi-9.html

        The file name in this case containing customer data has to follow a given

      naming convention as per above documentation eg: HzImpPartiesT.csv

b) Zip the content of the file

    The above file created as per ELT / ETL process needs to be zipped as per

    upload requirement as well as to compress the data

    OdiZip can be used alternatively a script can also be written to zip the file

c) Convert the zip file onto BASE64 string using java program or alternate

    programs

   Java Program construct: utilEncodeBase.java file content

import java.io.ByteArrayOutputStream; import java.io.File;

import java.io.FileInputStream; import java.io.FileReader; import java.io.IOException; import java.io.InputStream;

import org.apache.commons.codec.binary.Base64;

 

public class utilEncodeBase {

    public utilEncodeBase() {

super();

}

 

public static void main(String[] a) throws Exception {

 

// Enter the filename as input

    File br = new File("/home/oracle/movie/moviework/odi/CustomerImport.zip");

// Convert the file into Byte

byte[] bytes = loadFile(br);

 

// Call the api for Base64 encoding

byte[] encoded = Base64.encodeBase64(bytes);

String encStr = new String(encoded);

// Print the file

System.out.println(encStr);

 

}                      private static byte[] getByteArray(String fileName) {

                    File file = new File(fileName);

FileInputStream is = null;

ByteArrayOutputStream buffer = new ByteArrayOutputStream();

int nRead;

byte[] data = new byte[16384]; try {

is = new FileInputStream(file);

while ((nRead = is.read(data, 0, data.length)) != -1) {

    buffer.write(data, 0, nRead);

}

buffer.flush();

} catch (IOException e) {

System.out.println("In getByteArray:IO Exception"); e.printStackTrace();

}

return buffer.toByteArray();

}                      private static byte[] loadFile(File file) throws IOException { InputStream is = new FileInputStream(file);

 

long length = file.length();

if (length > Integer.MAX_VALUE) {

// File is too large

}

byte[] bytes = new byte[(int)length];

 

int offset = 0; int numRead = 0;

while (offset < bytes.length &&

(numRead = is.read(bytes, offset, bytes.length - offset)) >= 0) {

offset += numRead;

}

if (offset < bytes.length) {

throw new IOException("Could not completely read file " +

file.getName());

}

is.close(); return bytes;

}

}

d) Create a XML file to be passed via the Webservices to load the file. We have

    java program to load the BASE64 string to XML and in turn use this XML as

    the request file. The below program helps generates a XML with input

    string (BASE64)

Java Program construct: genxml.java file content

import java.io.BufferedReader;

import java.io.FileOutputStream;

import java.io.FileReader;

import org.jdom.Document;

import org.jdom.Element;

import org.jdom.Namespace;

import org.jdom.output.Format;

import org.jdom.output.XMLOutputter;

public class GenerateXML

{

  public GenerateXML() {}

 

  public static void main(String[] paramArrayOfString)

  {

    try

    {

      BufferedReader localBufferedReader = null;

      FileReader localFileReader = null;

     

 

      localFileReader = new FileReader("/home/oracle/Elogs/Conv_Out.txt");

      localBufferedReader = new BufferedReader(localFileReader);

     

      StringBuffer localStringBuffer = new StringBuffer();

     

      String str;

      while ((str = localBufferedReader.readLine()) != null) {

        localStringBuffer.append(str);

      }  

      Document localDocument = new Document();  

      localDocument.setRootElement(new Element("Envelope", "soapenv", "http://schemas.xmlsoap.org/soap/envelope/"));

      localDocument.getRootElement().addNamespaceDeclaration(Namespace.getNamespace("typ", "http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/"));

      localDocument.getRootElement().addNamespaceDeclaration(Namespace.getNamespace("erp", "http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/")); 

      Element localElement1 = new Element("Header", "soapenv", "http://schemas.xmlsoap.org/soap/envelope/");

      localDocument.getRootElement().addContent(localElement1);   

      Element localElement2 = new Element("Body", "soapenv", "http://schemas.xmlsoap.org/soap/envelope/");

      Element localElement3 = new Element("uploadFileToUcm", "typ", "http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/");

      Element localElement4 = new Element("document", "typ", "http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/");

      Element localElement5 = new Element("Content", "erp", "http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/");

      localElement5.addContent(localStringBuffer.toString());

      localElement4.addContent(localElement5); 

      Element localElement6 = new Element("FileName", "erp", "http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/");

      localElement4.addContent(localElement6);

      Element localElement7 = new Element("ContentType", "erp", "http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/");

      localElement4.addContent(localElement7);  

      Element localElement8 = new Element("DocumentTitle", "erp", "http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/");

      localElement4.addContent(localElement8);

      Element localElement9 = new Element("DocumentAuthor", "erp", "http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/");

      localElement4.addContent(localElement9);

      Element localElement10 = new Element("DocumentSecurityGroup", "erp", "http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/");

      localElement4.addContent(localElement10);

      Element localElement11 = new Element("DocumentAccount", "erp", "http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/");

      localElement4.addContent(localElement11);  

      Element localElement12 = new Element("DocumentName", "erp", "http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/");

      localElement4.addContent(localElement12); 

      Element localElement13 = new Element("DocumentId", "erp", "http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/");

      localElement4.addContent(localElement13);

      localElement3.addContent(localElement4);

      localElement2.addContent(localElement3);

      localDocument.getRootElement().addContent(localElement2);   

      XMLOutputter localXMLOutputter = new XMLOutputter(Format.getPrettyFormat());

      localXMLOutputter.output(localDocument, new FileOutputStream("/home/oracle/TgtFiles/uploadfiletoucm_payload.xml"));

    }

    catch (Exception localException) {

      localException.printStackTrace();

    }

  }

}

 e) Upload the file to UCM-Cloud using the appropriate web service call with

     BASE64 as an input value against "Content" tag on XML

Request file:

Response File: (need to capture value against tag “result”

f) Extract the value of result and capture the result onto a Variable

grep -oE '<result xmlns="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/">[^<]*</result>' /home/oracle/Elogs/Con_Resp.xml | awk -F"[<>]" '{print $3}'

g) Assign this value onto a Variable to be used for subsequent webservice call

h) Invoke ERP-Cloud to trigger the import process, using the appropriate web

    service call.

Here is an example of this process implemented in an ODI Package:

The ‘OdiInvokeWebService’ construct and the topology created for this requirement provided below

Please note:

  1. OWSM rule that needs to be selected is oracle/wss_username_token_over_ssl_client_policy
  2. Requirement for this to work is to use ODI Enterprise configured with an Agent deployed in WebLogic Server

Inputs Sought by Web service:

Screenshot from ERP Cloud of loaded customers and statistics:

Customers loaded in ERP Cloud from ODI:

Conclusion:

The features available in ODI make it relatively easy to generate a file, compress / zip the file and call specific Java / Linux scripts / functions and invoking necessary web services on ERP Cloud to aid uploading the necessary files onto specific ERP Cloud directory.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha