Rabu, 21 Februari 2018

Custom function in ICS to extend transformation capabilities

There is always the limitation in the product we use in the IT and all the product do not fill all the requirements. That is the reason we use custom options to extend the capability of products.

Same way ICS is restricted to provide some out of the box function. But if these inbuilt functions don't meet the expectation then we always try to explore some custom program to meet the requirement.

So, in the release of ICS 17.2.5 onwards, ICS has introduced the capability of importing and using custom functions. These functions are created using JavaScript and can be used in transformations, expressions and as an action in Orchestrations.

Let's go forward and see how can we achieve this with the help of a simple example.

For this blog, we have created a simple javascript function which is used to add two integer variable and return the sum of these two.

function sum(first,second)
{
    var third = first+second
    return third;
}

To use this function, we have to register this as a library using ICS. Let's save this file with name sum.js and save it to the desktop machine.

Login into the ICS and navigate to the Designer -> Libraries

Click on the Register button 


Register Library popup will get open, provide information and click on Create button
  • Select Library File(.js/.jar): Select the sum.js file
  • Name: Give any library name
  • Identifier: Would be picked up automatically based on the Name entered. However, we can change it
  • Version: Library version
  • Description: Enter description

Before we can use functions within integrations and transformations we need to configure all functions we want to use. For instance, ICS needs to know the input and output types of the JavaScript arguments and in which components we want to make the functions available. In the current version (17.1.3 Early uptake) functions can only be made available in orchestrations, but in the future, we will see the ability to also use functions in transformations using XPath and by adapters.

In this case, the library only has one function name sum which has two integer input arguments (first, second) and one integer output argument (third). When configuring the arguments we can select between three basic data types; boolean, number and string.

Configure the library as shown in the below screenshot  and click on Save button


See the library has been registered successfully


Now we are good to go to use this custom Library in the Orchestration integration.

Let's move forward and create a REST service. This REST service will accept two arguments and produces the JSON output with a single output parameter.

Drop a Trigger REST connection on the canvas and configure the wizard
  • Enter below information and Click Next
    • Name of the endpoint: SumTwoValues
    • Relative URI(must start with /): /sum
    • Select HTTP verb: GET
    • Select Add and review parameter for this endpoint
    • Select, Configure this endpoint to receive the response checkbox: This option allows us to assign response payload in the next step
  • Add two Query parameter(first, second) of integer type and click Next
  • Select JSON sample radio button, Click on inline link and provide payload
{
                "sum":1
}

  • Drop Function Call activity between the REST adapter and mapper
  • Click on Function link
  • Select sum function
  • With the help of Expression builder, assign the query parameter(first, second) to the JS input variable
  • Edit the mapper and drag output_CallSumFun_third the variable and drop to sum variable
  • Activate the integration and hit REST service


Rabu, 03 Januari 2018

Using Oracle EBS Adapter in Integration Cloud Service

Oracle ICS provides Oracle E-Business Suite Adapter that provides native and secure connectivity to Oracle E-Business Suite instance. It lets us create integrations with EBS public integration interfaces.

We have written a dedicated blog on Oracle E-Business Suite Adapter that gives the detailed description of EBS adapter and it's capabilities.

In this blog, we'll show how to use E-Business adapter in an ICS integration to call custom services that are deployed on EBS ISG(Integrated SOA Gateway).

Let's create a scheduled integration in which we'll use the EBS adapter to call PL/SQL API.


Drop the EBS connection just below the schedule component from the Invokes tab. Please see the blog which describes how to create EBS connection


EBS connection configuration wizard will be opened. Enter the Endpoint name and click Next


Select the Product family from the Next screen. For this use-case, we'll select Human Resource Suite since our custom service relates to this product family.

Please see the blog on how to deploy custom services

Then select Human Resources Personnel from Product drop-down. These two selections totally depend on PL/SQL API deployed on ISG. For this information please check with the EBS consultant who has deployed the API on ISG.

Select the XX_SAVE_EMPLOYEE API which is being deployed on the ISG. In another blog, we have demonstrated how to deploy the PL/SQL API on ISG.


Now select the operation which we want to call. As of now we only have a single method (save_employee) in the API (XX_SAVE_EMPLOYEE ). Select the method and click Next button

Exposed method(save_employee) takes two input parameter(C_ID, NAME) and insert the same into the table(xx_test).

Click Done button on the summary page. Edit the mapper and see all the input parameter exposed in the operation(save_employee)


Map the input parameters as per the requirement.

Save and activate the integration.

These steps complete the integration and now it's time to test the integration. Once integration executed successfully, the data should be inserted into the table.

Minggu, 10 Desember 2017

File Upload Process in Integration Cloud Services REST API

File upload is a very common aspect of each and every technology we used in our day to day projects as part of the REST service. This is very common requirement to upload a file that resides in the local computer and send it to the remote computer.

Sometimes we need to expose a REST service which should allow to upload files, process the data and store it some repository like a database, FTP etc..

In this blog, we are going to show how to use ICS REST adapter feature to upload a file and save the file as it is on some FTP location.

Below are the steps to achieve the use case:
  • Create REST Adapter as Trigger
  • Create FTP Adapter as Invoke
  • Create Orchestration Integration
  • TEST the Integration
Create REST Adapter as Trigger

There is one dedicated blog that explains, how to configure REST Adapter in Oracle ICS. Please have a look at the blog

Create FTP Adapter as Invoke

This has also been explained in another blog that shows, how to configure FTP Adapter in Oracle ICS. Please have a look at the blog

Create an Orchestration Integration

Create an Orchestrated Integration using steps:
  • Login into ICS console
  • Click on the Integration tile from ICS home page
  • Click on Create button from upper right corner
  • Select Orchestration pattern from the dialog box
  • Enter Below information and click on Create button
    • Select Application event or business object radio button
    • Enter Integration Name in What do you want to call your integration text box
    • An Identifier would be picked up automatically from Integration name, however, we can edit it
    • Let the Version as it is
    • Enter the description in What does this integration do input box
    • Leave the package input box as it is
  • Drag the TEST_REST_Conn Connection on the canvas from REST connection
  • Enter below information and Click Next
    • Name of the endpoint
    • Relative URI(must start with /)
    • Select HTTP verb as POST
    • Select Configure a request payload for this endpoint checkbox- This option allows us to assign payload in the next step
  • Select Accept attachments from request and Request in HTML form checkboxes then click Next and Done button
  • Drag & Drop the FTP connection on the canvas
  • Enter the Name in What do you want to call the endpoint input box and click Next button
  • Enter below information and click Next button
    • Select Operation: Select Write File
    • Select a Transfer Mode: Choose ASCII
    • Specify an Output directory: Enter the directory where you want to save the uploaded file
    • Specify a File Name Pattern: Enter the file name
  • Choose No under the Do you want to define a schema for this endpoint and click Next -> Done button
  • Edit the mapper and map the below
    • execute -> attachments -> attachment -> attachmentReference to ICSfile -> FileReference
    • partName -> fileName
partName element would contain the file name that will be uploaded


That configuration completes the integration.

Let's test the integration using POSTMAN tool:


Check the FTP location output directory that has been given during configuring FTP adapter.

Sabtu, 09 Desember 2017

Conditional Mapping in Oracle ICS

Conditional mapping is a very common question in each tech that we use in our projects. Someone asked me how to use conditional mapping in Oracle ICS which encouraged me to write this blog.

So, let's catch the below case:
  • Suppose there are two Query parameters, called A and B
  • There is another third output variable called C
  • Value of C should be as per below logic:
    • If A & B both are not null then output should be, C  = concat(A | B)
    • If A not null then output should be C  =  A
    • If B not null then output should be C  =  B
So, let's look how we can achieve the preceding use case with the power of XSLT.

We'll build our expression with the help of choose function available in Oracle ICS. Below attached screenshot depicts the logic that has been built based on the above logic


Since ICS mapper is very sophisticated, so such complex mapping is very difficult to build using ICS mapper. So I would suggest, export the integration and open the XSLT file and edit the code directly with the help of JDeveloper.

Below is the part of XSLT that has been built to achieve the use case:

<nsmpr0:C xml:id="id_17">
<xsl:choose xml:id="id_18">
<xsl:when test="/nstrgmpr:execute/nstrgmpr:QueryParameters/nsmpr1:A!='' and /nstrgmpr:execute/nstrgmpr:QueryParameters/nsmpr1:B!=''">
<xsl:value-of select="concat(/nstrgmpr:execute/nstrgmpr:QueryParameters/nsmpr1:A, ' | ', /nstrgmpr:execute/nstrgmpr:QueryParameters/nsmpr1:B)" />
</xsl:when>
<xsl:when test="/nstrgmpr:execute/nstrgmpr:QueryParameters/nsmpr1:A!=''">
<xsl:value-of select="/nstrgmpr:execute/nstrgmpr:QueryParameters/nsmpr1:A" />
</xsl:when>
<xsl:when test="/nstrgmpr:execute/nstrgmpr:QueryParameters/nsmpr1:B!=''">
<xsl:value-of select="/nstrgmpr:execute/nstrgmpr:QueryParameters/nsmpr1:B" />
</xsl:when>
</xsl:choose>
</nsmpr0:C>


Minggu, 26 November 2017

Database polling in Oracle Integration Cloud Service

In this article, we will show how to use Oracle database polling in Oracle ICS. Oracle ICS allows very straight forward solution to poll the Oracle database in comparison to SOA.

Let's see how we can achieve database polling strategy in Oracle ICS.

  • Create a database table(XX_EMPLOYEE) with below script

CREATE TABLE XX_EMPLOYEE
 (             "ID" VARCHAR2(20 BYTE),
                "NAME" VARCHAR2(200 BYTE),
                "ISNEW" VARCHAR2(10 BYTE)
 )


  • Run below script to insert some values
 insert into XX_EMPLOYEE(ID,NAME,ISNEW) values(1,'Ankur Jain','NEW')
 insert into XX_EMPLOYEE(ID,NAME,ISNEW) values(2,'Nitin','NEW')
  • Create a Oracle Database Connection in Oracle ICS. Have a look to this blog for Oracle DB connection. Make sure Role must be of Trigger type
  • Create an Orchestrated Integration
  • Drop the Oracle DB connection as a Trigger point
  • Enter Name in What do you want to call your endpoint input box and click Next
  • Click Import Tables
  • Select the DB schema in which XX_EMPLOYEE table has been created, Enter the table name and transfer to Selected Tables. Click OK button
  • We will notice the below configuration screen
  • Click on Edit button of Review the polling strategy and specify polling options and enter/select below values
    • Polling Strategy: Logical Delete
    • Logical Delete Field: ISNEW
    • Read Value: This value used to indicate the row has been processed. Enter PROCESSED
    • Unread Value: Indicate the row to be process. Enter NEW
    • Polling Frequency(Sec): Specify the polling frequency in seconds to process the new record
  • Click Next and Done
This is the only configuration required to poll the Oracle DB. Now activate the integration and check the DB rows. We'll notice that the rows has been picked by Integration and ISNEW flag values has been set to PROCESSED

Before Activate the Integration


After Activate the Integration


Sabtu, 25 November 2017

Expose custom PL SQL APIs as a SOA Gateway Webservice

Integrated SOA Gateway(ISG) is an integrated component of E-Business Suite R12. ISG allows to expose EBS functionality(which includes XML gateway, Business Service Objects, Concurrent Programs, PL/SQL API's, Business Events, E Commerce Gateway, Open Interface Tables/Views etc.) as a SOAP/REST WebServices.

To expose a PL/SQL package as a service interface, it is mandatory to annotate the package with standard annotation.

Below steps to be followed to expose a PL/SQL package as a REST interface.

  • PL/SQL package is annotated with the standard annotation

create or replace PACKAGE XX_SAVE_EMPLOYEE AS
/* $Header: $ */
/*#
* This custom PL/SQL package can be used to insert employees
* @rep:scope public
* @rep:product per
* @rep:displayname XX_SAVE_EMPLOYEE
* @rep:category BUSINESS_ENTITY PER_EMPLOYEE
*/

/*#
* Save records in emp table
* @param c_id varchar2
* @param name varchar2
* @rep:scope public
* @rep:lifecycle active
* @rep:displayname save_employee
*/
PROCEDURE save_employee_PRC(c_id IN VARCHAR2,
   name IN VARCHAR2);
END XX_SAVE_EMPLOYEE;

Preceded package contains a single procedure save_employee_PRC that takes two input parameters. This procedure is used to insert data(c_id and name) into the table.

Note: For PL/SQL packages, only the package spec should be annotated. Do not annotate the body.

  • Create a .pls file and paste the above code in the .pls file. Move the .pls file to the EBS server via winscp or putty. In this case we will copy the file at $APPL_TOP/patch/115/sql
  • Next step is to create an iLDT file (Integration Repository loader file) that we should use to upload to the Integration Repository. The file is created with a tool called Integration Repository Parser (IREP Parser), the tool will validate the file against the annotation standards.
  • Run the below command to generate the iLDT file
[root@testmachine]$ $IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=sysadminper:patch/115/sql:XX_SAVE_EMPLOYEE.pls:12.0=XX_SAVE_EMPLOYEE.pls

Note: If you are generating a new iLDT file for an already uploaded interface we need to add a higher version number then the last uploaded.

[root@testmachine]$ $IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=sysadminper:patch/115/sql:XX_SAVE_EMPLOYEE.pls:12.1=XX_SAVE_EMPLOYEE.pls

If everything OK below would be the output of the above command

# Interface Repository Annotation Processor, 12.0.0

#
# Generating annotation output.
# Processing file 'XX_SAVE_EMPLOYEE.pls'.
# Using YAPP-based parser.
#  Found a package-level annotation for 'XX_SAVE_EMPLOYEE'.
#  Found a detail-level annotation...
# Found a procedure named 'SAVE_EMPLOYEE_PRC'.
# Done all files.

Please Note: XX_SAVE_EMPLOYEE_pls.ildt would be generated.
  • Next is used to upload the generated iLDT file to integration repository. Run below command to upload the iLDT file to integration repository
[root@testmachine]$ $FND_TOP/bin/FNDLOAD <db_user>/<db_password>0 Y UPLOAD $FND_TOP/patch/115/import/wfirep.lct XX_SAVE_EMPLOYEE_pls.ildt

Note: Replace the <db_user> and <db_password> with database apps user and password.

Above command will generate a .log file. Just tail the file and see the below success output

[root@testmachine]$ tail -500f L2602439.log
+---------------------------------------------------------------------------+
Application Object Library: Version : 12.2

Copyright (c) 1998, 2013, Oracle and/or its affiliates. All rights reserved.

FNDLOAD: Generic Loader
+---------------------------------------------------------------------------+

Current system time is 15-NOV-2017 23:17:58

+---------------------------------------------------------------------------+

Uploading from the data file XX_SAVE_EMPLOYEE_pls.ildt
Altering database NLS_LANGUAGE environment to AMERICAN
Dump from LCT/LDT files
(/appl/fnd/12.0.0/patch/115/import/wfirep.lct(120.8.12020000.3), XX_SAVE_EMPLOYEE_pls.ildt) to stage tables
Dump LCT file
 /appl/fnd/12.0.0/patch/115/import/wfirep.lct(120.8.12020000.3) into FND_SEED_STAGE_CONFIG
Dump LDT file XX_SAVE_EMPLOYEE_pls.ildt into FND_SEED_STAGE_ENTITY
Dumped the batch (IREP_OBJECT PLSQL:XX_SAVE_EMPLOYEE C , PARAMS 2 0 ) into FND_SEED_STAGE_ENTITY
Upload from stage tables

+---------------------------------------------------------------------------+
Concurrent request completed successfully
Current system time is 15-NOV-2017 23:17:59
  • Now create the package and package body in the database with the below script
Package

create or replace PACKAGE XX_SAVE_EMPLOYEE AS
/* $Header: $ */
/*#
* This custom PL/SQL package can be used to insert employees
* @rep:scope public
* @rep:product per
* @rep:displayname XX_SAVE_EMPLOYEE
* @rep:category BUSINESS_ENTITY PER_EMPLOYEE
*/

/*#
* Save records in emp table
* @param c_id varchar2
* @param name varchar2
* @rep:scope public
* @rep:lifecycle active
* @rep:displayname save_employee
*/
PROCEDURE save_employee_PRC(c_id IN VARCHAR2,
   name IN VARCHAR2);
END XX_SAVE_EMPLOYEE;

Package body

create or replace PACKAGE BODY XX_SAVE_EMPLOYEE AS    
   PROCEDURE save_employee_PRC(c_id IN VARCHAR2,
   name IN VARCHAR2) IS
   BEGIN
    INSERT INTO xx_test(id,name) values(c_id,name);
   END save_employee_PRC;
END XX_SAVE_EMPLOYEE;
  • Now, login into EBS console and Navigate to the Integrated SOA Gateway -> Integration Repository
  • Enter the display name(XX_SAVE_EMPLOYEE) in Internal Name and click on Search button
  • Click on the XX_SAVE_EMPLOYEE and switch to REST Web Service tab.
  • Enter Service Alias: This would be the service name
  • Select save_employee Checkbox and click Deploy button

Once successfully deployed, success message will be displayed
  • Switch to Grants tab, select Checkbox and click on Create Grant button

  • Select Specific User from the Grantee Type drop down and enter Grantee Name to give access to specific user to execute this service. Click on Create Grant button
  • Once done, success message will appear on the screen
Now the PL/SQL has been exposed as a REST interface


Click on View WADL link to see the REST endpoint.

We can test the REST service via POSTMAN tool. Below would be the endpoint of REST service. 


During testing, add the BASIC authentication.


Create an Integration in ICS to expose SOAP service

In this blog, we'll demonstrate how we can use Integration Cloud Service to expose SOAP service. In one of my blog we have showcased how to expose REST services.

In this blog, we will create an Orchestrated integration that will be exposed as a SOAP service.

Use Case
  • Develop a SOAP service using ICS
  • Request parameter would be a user id
  • Response would be username
  • Service will return fault if user not found
Below are the steps to achieve the use case
  1. Create DB Connection
  2. Create SOAP Connection
  3. Create Orchestrated Integration
  4. Test SOAP service
Let's go ahead and achieve all the steps one by one
  1. Create DB Connection: DB connection will work as a Target point. Please check the blog how to create DB connection.
  2. Create SOAP Connection: SOAP connection will work as a Trigger point. Please check the blog how to create SOAP connection.
  3. Create Orchestrated Integration: Create an Orchestration integration using below steps:
  • Click on Create button from Integration page and select Orchestration pattern from the dialog box
  • Enter Below information and click on Create button
    • Select Application event or business object radio button
    • Enter Integration Name in What do you want to call your integration text box
    • An Identifier would be picked up automatically from Integration name, however, we can update this
    • Let the Version as it is
    • Enter the description in What does this integration do box
    • Leave the package box as it is
  • Drag SOAP_Conn on the canvas from the SOAP connection
  • Complete the SOAP wizard with some clicks Next -> Next -> Next -> Next - > Done. Once the SOAP wizard is complete, integration will look like below
  • Drag Test_DB_Conn on the canvas from the DB connection just below the Map GetUserName

  •  Enter the endpoint name and select Run a SQL Statement from What operation do you want to perform drop down then click Next

  • Enter below query then click Validate SQL Query button. Click Next  -> Done button to complete the configuration wizard

select name from xx_employee_t where id=#userId


  • Edit GetUserDetails map and map id -> userId

  • Now let's check if a user exists or not. To do so, drop the Switch activity below the DB adapter and configure if action. Put the condition, username is not blank
  • Drop the map activity in the if block and map, name -> name
  • Drop the Fault Return activity under the otherwise. A mapper will get automatically added between the otherwise and Fault Return
  • Edit the newly added mapper and you will notice that fault object that was in the WSDL will be shown automatically. Put the expression 'User doesn't exist' in the reason
Now the integration is complete. Activate the integration that will provide one WSDL once the integration is activated successfully.

Let's test the integration using SOAP UI tool.

TEST-1 : Send valid id, hit the request and notice the valid response

TEST-2 : Send invalid id, hit the request and notice fault will occur