OIC Agent (Oracle Integration Cloud Connectivity Agent for on-premise system)

 

OIC Agent diagram

Introduction

OIC agents are the agent that help Oracle Integration Cloud (OIC) to connect and integrate on-premise application. 

The agent is required on install and configure at on-premise network. It can be installed at any machine/node within the network where it must have access to target system.

Implementation

To implement OCI Agent, we need to create agent group in OIC and then download installation files.

let's create agent group in integration cloud

Navigate to Agents from the Integration homepage

Select Create Agent Group button

OIC Agent 1

Give any meaningful Name

Identifier will be auto filled

We need this identifier to specify in the configuration file after the download

Edit OIC Agent

Click Save

Select Download and then Connectivity Agent

OIC Agents

Download and unzip at any local on-premise location

OIC Agent 

Go the the oic_conn_agent_installer directory

Edit the InstallerProfile.cfg file and provide the OCI home url and agent Group Identifier that we noted down while create agent group the first step

OIC Agent

Save configuration file and lets start the agent

I downloaded to a window machine that has access to target server

Before you start:

• Java home must be set
• you are in the agent directory

Run the below script 

java.exe" -jar connectivityagent.jar

I was in the agent directory and referred the java.exe with the path as below

C:\xxx\Agent\oic_conn_agent_installer>"c:\Program Files\Java\jdk1.8.0_51\bin\java.exe" -jar connectivityagent.jar

OIC Agent

Agent Instillation and configuration is done

You can view that agent group status. this should be green

Thank for reaching to my blog. for further information please visit below oracle library  

Using Integrations in Oracle Integration

Oracle Fusion Supplier FBDI (File-Based Data Import for Oracle Procurement Cloud)

 Step1

The below diagram shows FBD modal upload process. 

for more detail, Visit https://docs.oracle.com/en/cloud/saas/procurement/20d/oefbp/supplier-model.html#supplier-model

Select Import Suppliers and  Download below xlsm file 

We can can create new suppliers or update existing suppliers by setting Import Action column value

After reviewing the data, we need to create zip file that we will upload to Fusion Apps

Select Schedule Processes under Tools from Fusion Apps Menu/homepage

Select Schedule New Processes and search for Load Interface File for Import 

Select Import Suppliers  and upload the zip file and submit the job

And Submit note down the request id

After successfully completion of above job, Schedule new process and search for Import Suppliers 

and provide the details as below

After successfully completion of the Import Supplier job, you can view the log and Supplier search

Oracle Fusion General Ledger FBDI (File-Based Data Import for Financials)

Before you start

You must have appropriate Ledger assigned, specific period must open

Visit https://docs.oracle.com/en/cloud/saas/financials/20b/oefbf/index.html

Download below xlsm file 

Enter Journal lines with correct information i.e Debit and Credit must balance

After reviewing the data, we need to create zip file that we will upload to Fusion Apps

Select Schedule Processes under Tools from Fusion Apps Menu/homepage

Select Schedule New Processes and search for Load Interface File for Import 

Select Import Journals  and upload the zip file and submit the job

Note down request id, this will help in search our submitted request

Now submit Import Journals job

Oracle EBS - Java - Creating csv File from Database for Oracle Apps Java Concurrent Program

Introduction

Oracle EBS provide the functionality to leverage Java into action. When you love java and to feel in your EBS environment.

I am sharing here a sample java class code that will will be used for Oracle EBS Concurrent program executable and perform the tasks of fetching data from database with power of JDBC and will create a csv file on application server file system.

I have used a string variable to hold string content you as payload to create file

Java Code

package oracle.apps.fnd.cp.request;

 

 

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.FileWriter;

import java.io.IOException;

import java.io.PrintWriter;

import java.io.StringWriter;

 

import java.security.NoSuchProviderException;

 

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

 

import java.text.SimpleDateFormat;

 

import java.util.Date;

 

import oracle.apps.fnd.util.ParameterList;

 

import oracle.jdbc.OracleCallableStatement;

 

 

public class GeneratePaymentFile implements JavaConcurrentProgram {

    String pDate;

    String directory_path = "/Top Directory Path";

 

    public GeneratePaymentFile() {

    }

 

    public void runProgram(CpContext pCpContext) {

        Connection mJConn = pCpContext.getJDBCConnection();

 

        ReqCompletion lRC = pCpContext.getReqCompletion();

 

        OutFile lOF = pCpContext.getOutFile();

 

        LogFile lLF = pCpContext.getLogFile();

 

 

        try {

 

            Date date1 = new Date();

 

            String shortDate = 

                new SimpleDateFormat("dd-MMM-yyyy").format(date1);

 

            String lQuery = 

                "SELECT * \n" + "from XX_PAYMENT_V \n" + " where upper(APPROVAL_STATUS) = 'APPROVED' "; // modify you sql query to pick required rows to prepare file

 

 

            lOF.writeln("+-------------------------------+");

            lOF.writeln("Generating Payment File ");

            lOF.writeln("+--------------------------------+");

            lOF.writeln("");

 

 

            lOF.writeln("Running for Date : " + shortDate);

            lOF.writeln("");

 

            PreparedStatement lStmt = mJConn.prepareStatement(lQuery);

            ResultSet lRs = lStmt.executeQuery();

 

            String txtLines = "";

 

 

            FileWriter fw;

            int record_count = 0;

 

            Date time1 = new Date();

 

            String mDateTimeStr = 

                new SimpleDateFormat("ddMMMyy").format(date1).toUpperCase();

            mDateTimeStr = 

                    mDateTimeStr + new SimpleDateFormat("HHmmss").format(time1);

 

            String file_name = "/Request/TEST_FILE_NAME_" + mDateTimeStr;

 

 

            try {

                while (lRs.next()) {

 

                    if ((txtLines.length()) > 0) {

                        txtLines = txtLines + "\r\n";

                    }

 

                    txtLines = 

                            txtLines + checkNull(lRs.getString("VALUE_DATE")) + 

                            "|" + checkNull(lRs.getString("CURRENCY_CODE")) + 

                            "|" + lRs.getString("AMOUNT") + "|" + 

                            checkNull(lRs.getString("DEBIT_BANK_ACCOUNT_NUM")) + 

                            "|" + "OUR|" + 

                            checkNull(lRs.getString("DEBIT_BANK_ACCOUNT_NUM")) + 

                            "|" + "|" + lRs.getString("CHECK_ID");

 

 

                    try {

 

                        OracleCallableStatement eStmt = null;

 

 

                        String stmt = 

                            "Update xx_Payment_tbl \n" + "Set STATUS = 'Sent', " + 

                            "where CHECK_ID = ?";

 

 

                        eStmt = 

                                (OracleCallableStatement)mJConn.prepareCall(stmt);

                        eStmt.setString(1, lRs.getString("CHECK_ID"));

 

                        eStmt.execute();

                        eStmt.close();

 

                    } catch (SQLException e) {

                        lOF.writeln("Please check the log for error details)");

                        e.printStackTrace();

 

                        lRC.setCompletion(ReqCompletion.ERROR, e.toString());

                    }

 

 

                    record_count++;

                }

 

                if (txtLines.length() > 0) {

 

 

                    lLF.writeln("File Name : " + file_name, LogFile.STATEMENT);

 

                    fw = 

 new FileWriter(new File(directory_path + file_name + ".csv"));

 

                    fw.write(txtLines);

 

                    fw.close();

 

                } else {

                    lOF.writeln("No data found, file not created");

                }

 

            } catch (IOException ex) {

                lStmt.close();

                lOF.writeln("Please check the log for error details)");

                lOF.writeln(ex.getStackTrace().toString());

 

                lRC.setCompletion(ReqCompletion.ERROR, ex.toString());

 

            }

 

            lOF.writeln("");

            lOF.writeln("+--------------------------------------+");

            lOF.writeln("ayment File created");

            lOF.writeln("+---------------------------------------+");

 

 

            lStmt.close();

            lRC.setCompletion(ReqCompletion.NORMAL, 

                              "Request Completed Normal");

        } catch (SQLException e) {

 

 

            StringWriter errors = new StringWriter();

            e.printStackTrace(new PrintWriter(errors));

 

            lOF.writeln(errors.toString());

 

            lRC.setCompletion(ReqCompletion.ERROR, e.toString());

 

 

        } finally {

 

            pCpContext.releaseJDBCConnection();

        }

    }

 

 

    String checkNull(String m) {

 

        if (m == null) {

            return "";

        } else {

            return m;

        }

 

    }

 

}

 Thank for reaching to my blog. 

REST API Client (Oracle Apps Java Concurrent Program)

Sample java REST API client class for Oracle EBS Concurrent program

package oracle.apps.fnd.cp.request;

 

 

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.FileWriter;

import java.io.IOException;

import java.io.PrintWriter;

import java.io.StringWriter;

 

import java.security.NoSuchProviderException;

 

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

 

import java.text.SimpleDateFormat;

 

import java.util.Date;

 

import oracle.apps.fnd.util.ParameterList;

 

import oracle.jdbc.OracleCallableStatement;

 

public class JavaConcRestAPIClient implements JavaConcurrentProgram {

    String pDate;

 

    public JavaConcRestAPIClient() {

    }

 

    public void runProgram(CpContext pCpContext)  {

        // get the JDBC connection object 

        Connection mJConn = pCpContext.getJDBCConnection();

 

        // get parameter list object from CpContext 

        ParameterList lPara = pCpContext.getParameterList();

 

        // get ReqCompletion object from CpContext 

        ReqCompletion lRC = pCpContext.getReqCompletion();

 

        // get OutFile object from CpContext 

        OutFile lOF = pCpContext.getOutFile();

 

        // get LogFile object from CpContext 

        LogFile lLF = pCpContext.getLogFile();

 

        // value and use it as the application short name in the query 

        while (lPara.hasMoreElements()) {

            NameValueType aNVT = lPara.nextParameter();

            if (aNVT.getName().equals("pdate"))

                pDate = aNVT.getValue();

        }

      lOF.writeln("+--------------------------------------+");

      lOF.writeln("My Concurrent Program REST API Client");

      lOF.writeln("+---------------------------------------+");

      lOF.writeln("");

 

      try {

         

      lOF.writeln("+---------------------------------------+");

      lOF.writeln("Calling API ");

      lOF.writeln("+----------------------------------------+");

      lOF.writeln("");

 

        try {

 

            URL url;

                url = new URL("http://dummy.restapiexample.com/api/v1/employees");

         

            lOF.writeln("URL : " + url);

         

            HttpURLConnection con;

                con = (HttpURLConnection) url.openConnection();

    

            con.setRequestMethod("GET");

 

             int status = con.getResponseCode();

             BufferedReader in = new BufferedReader(new InputStreamReader(con.getInputStream()));

             String inputLine;

             StringBuffer content = new StringBuffer();

             while((inputLine = in.readLine()) != null) {

                     content.append(inputLine);

                    System.out.println(inputLine);

             }

             in.close();

             con.disconnect();

             System.out.println("Response status: " + status);

             System.out.println(content.toString());

             

            lOF.writeln("Content : " + content.toString());

 

 

            lOF.writeln("");

            lOF.writeln("+--------------------------------------+");

            lOF.writeln("My Concurrent Program REST API Complete");

            lOF.writeln("+--------------------------------------+");

 

            lRC.setCompletion(ReqCompletion.NORMAL, 

                    "Request Completed Normal");

       

        } catch (ProtocolException e) {

             lOF.writeln("Please check the log for error details)");

             lRC.setCompletion(ReqCompletion.ERROR, e.toString());

        

         } catch (IOException e) {

             lOF.writeln("Please check the log for error details)");

             lRC.setCompletion(ReqCompletion.ERROR, e.toString());

        

        } /*catch (MalformedURLException e) {

             lOF.writeln("Please check the log for error details)");

             lRC.setCompletion(ReqCompletion.ERROR, e.toString());

              

        }*/ 

        finally {

            pCpContext.releaseJDBCConnection();

        }

    }

}