Exposing pl/sql as REST Web Services using

Integrated SOA Gateway

Author: Mahendran Ranganathan
May-2018
Overview
This document describes the process of exposing Standard and Custom API as Rest Web service using
Integrated SOA Gateway. This document will help developers in understanding the ISG Customization, Rest
Web Services and its development. This document is complete guide to ISG Customization with screenshots .It
covers following case studies:

1. Exposing Standard API as REST Web Service and invoking it

2. Exposing Pl/Sql Function in Package as REST Web Service returning single value and invoking it.
3. Exposing Pl/Sql Procedure in Package as REST Web Service with Table Type as OUT Variable and invoking
it.

REST Web Services are available in Oracle E-Business Suite Release 12.2.3 and onwards only. REST based web
services are deployed on Oracle E-Business Suite’s application server. They do not dependent on Oracle SOA
Suite and Oracle Applications Adapter.

Integrated SOA Gateway

Oracle E-Business Suite Integrated SOA Gateway (ISG) is a complete set of service infrastructure to provide,
consume, and administer Oracle E-Business Suite Web services. You can use this tool to easily discover and
search on interfaces, regardless of custom or Oracle seeded ones.

Major Features of ISG

 Oracle E-Business Suite Integrated SOA Gateway can do the following:
 Display all Oracle E-Business Suite integration interface definitions through Oracle Integration
Repository
 Support custom integration interfaces from Oracle Integration Repository
 Provide service enablement capability (SOAP and REST services) for seeded and custom integration
interfaces within Oracle E-Business Suite
 Use the Integration Repository user interface to perform design-time activities such as generate and
deploy Oracle E-Business Suite Web services
 Support synchronous interaction pattern for REST-based Web services
 Support multiple authentication types for inbound service requests in securing Web service content
 Enforce function security and role-based access control security to allow only authorized users to
execute administrative functions
 Provide centralized, user-friendly logging configuration for Web services generated through Oracle E-
Business Suite Integrated SOA Gateway's service provider
 Audit and monitor Oracle E-Business Suite inbound service operations from Service Monitor
 Leverage Oracle Workflow Business Event System to enable Web service invocation from Oracle E-
Business Suite
REST Web Services

The acronym REST stands for Representational State Transfer, this basically means that each unique URL is a
representation of some object. REST describes a set of architectural principles by which data can be transmitted
over a standardized interface (such as HTTP). You can get the contents of that object using an HTTP GET, to
delete it, you then might use a POST, PUT, or DELETE to modify the object (in practice most of the services use
a POST for this).

Key features of EBS REST Services are as follows:

 Supports POST HTTP Verb
 Supports XML and JSON message format
 Described in WADL, XSDs
 No expensive tools require to interact with the Web service
 Smaller learning curve
 Efficient (SOAP uses XML for all messages, REST can use smaller message formats)
 Fast (no extensive processing required)
 REST-based implementation is simple compared to SOAP.

Integration Repository Customization

The term Customization of Integration Repository means option to add custom interfaces into Integration
Repository. The term interface stands for all types of programs and technologies in Integration Repository, eg.
plsql, java, business events, xml gateway etc.
Integration developers create and annotate custom integration interfaces based on the Integration Repository
annotation standards. Integration administrators use a standalone design-time tool to validate these annotated
source files against the annotation standards. After validation, a loader file is generated and then uploaded to
the Integration Repository through backend processing. These custom interfaces are displayed based on the
interface types to which they belong and displayed together with Oracle seeded ones from the Integration
Repository user interface.
Process Flow
Annotation Standards
Please refer the below link for details on Annotating Pl/Sql code.
http://docs.oracle.com/cd/E18727_01/doc.121/e12065/T511473T545912.htm

Setting Up Oracle E-Business Suite Integrated SOA Gateway

Please refer to below link for details:

http://docs.oracle.com/cd/E18727_01/doc.121/e12169/T511175T517162.htm

Also, please refer to the Doc ID 1311068.1 for ISG installation for REST Services.
Case Studies

Exposing Custom API as REST Web Service and Invoking it.

This step-by-step tutorial walks you through the following:
 Annotate the Custom Package
 Create Integration Repository loader file ( iLDT file)
 Upload iLDT file to the Integration Repository
 Deploy API as REST Service or SOAP Service
 Create Grants for REST Service or SOAP Service
 Invoke EBS REST Service (in JSON message format)
 Verify the result in EBS

Annotate the Custom Package

This example will illustrate on how to develop a simple interface using PL/SQL. The Integration Repository will
recognize integrations through annotations in the code (details about annotations can be found in the
Integrated SOA Gateway Developer's Guide, Appendix A).

 Annotate the package spec and save in the mentioned format *.pls and *.pkh files.
 For PL/SQL packages, only the package spec should be annotated. Do not annotate the body.

Example of annotated package spec

CREATE OR REPLACE PACKAGE XXDMV_MOBILE_OMS_COMMON_PKG as

/* $Header: $ */
/*#
* This package facilitates various sales order process.
* @rep:scope public
* @rep:product ont
* @rep:displayname Sales Order - Custom
* @rep:lifecycle active
* @rep:compatibility S
* @rep:category BUSINESS_ENTITY ONT_SALES_ORDER
*/

PROCEDURE create_reservation (p_org_id IN NUMBER,

p_item_code IN VARCHAR2,
p_party_id IN NUMBER,
p_cust_account_no IN VARCHAR2,
p_reserve_qty IN NUMBER,
p_serial_number IN VARCHAR2,
p_remarks IN VARCHAR2,
p_rp_item_id IN NUMBER,
p_rma_flag IN VARCHAR2,
p_rma_header_id IN NUMBER,
p_rma_line_id IN NUMBER,
p_sale_type IN VARCHAR2,
p_quote_header_id IN NUMBER,
p_quote_line_id IN NUMBER,
p_lead_master_id IN NUMBER,
p_organization_id IN NUMBER,
p_user_id IN NUMBER,
x_reservation_no OUT NUMBER,
x_return_code OUT VARCHAR2,
x_message OUT VARCHAR2)
/*#
* Use this procedure to create Reservation
* @param p_org_id Operation unit
* @param p_item_code Itam code
* @param p_party_id Customer indentifier
* @param p_cust_account_no Customer Account Number
* @param p_reserve_qty resrvation quantity
* @param p_serial_number VIN Number
* @param p_remarks return Reservatiob remarks
* @param p_rp_item_id Rust Proofing item defualt null
* @param p_rma_flag RMA flag
* @param p_rma_header_id RMA reference Header ID
* @param p_rma_line_id RMA reference line ID
* @param p_sale_type Sale type
* @param p_quote_header_id Quotation reference ID
* @param p_quote_line_id Quotation reference Line ID
* @param p_lead_master_id OMS Reference ID
* @param p_organization_id Branch ID
* @param p_user_id sales executive ID
* @param x_reservation_no return the reservation number
* @param x_return_code return the process code
* @param x_message return the process message
* @param x_message return the
* @rep:displayname create_reservation
* @rep:category BUSINESS_ENTITY ONT_SALES_ORDER
* @rep:scope public
* @rep:lifecycle active
*/
;

END XXDMV_MOBILE_OMS_COMMON_PKG;

Note: The annotation BUSINESS_ENTITY is where in the Integration Repository your custom integration will be
found. The below queries will help you find the correct BUSINESS_ENTITY. It's not possible to add your
own BUSINESS_ENTITYs in the current version.

 SELECT * FROM fnd_lookup_assignments WHERE lookup_type = BUSINESS_ENTITY'

 SELECT * FROM fnd_lookups WHERE lookup_type = 'BUSINESS_ENTITY'
Create Integration Repository loader file ( iLDT file)

The 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. Before the iLDT file can be created
XXDMV_MOBILE_OMS_COMMON_PKG.pls need to be transferred to the server (any folder it can be placed. in
this case the we put the file in /home/appladev/Temp)

Run the command to generate the iLDT file:

$IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -
username=sysadmin gl:patch/115/sql: XXDMV_MOBILE_OMS_COMMON_PKG.pls:12.0=/tmp/
XXDMV_MOBILE_OMS_COMMON_PKG.pls

Note: If you are generating a new iLDT file for an already uploaded interface you need to add a higher version
number then the last uploaded.
$IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -
username=sysadmin gl:patch/115/sql: XXDMV_MOBILE_OMS_COMMON_PKG.pls:12.1=/tmp/
XXDMV_MOBILE_OMS_COMMON_PKG.pls

Upload iLDT file to the Integration Repository

When the iLDT file is successfully created we can upload it to the Integration Repository using:
$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/wfirep.lct
XXDMV_MOBILE_OMS_COMMON_PKG.ildt
Deploy API as REST Service or SOAP Service

Login in ERP and navigate to Integrated SOA Gateway  Integration Repository, click on search which will be
navigated to integration repository search page.

1. Search for the “Sales order - Custom” (the display name mentioned in package spec annotation)
2. Click Go
3. Click on the link Sales Order - Custom

Successfully uploaded to the Integration Repository

4. Navigate to Rest Web service tab

5. Enter Service Alias name
6. Deploy Rest service. When the web service is deployed it is possible to Redeploy and Undeploy the
service
Once deployed the rest service status will change to Deployed and Like to View WADL will be enabled

<?xml version="1.0" encoding="UTF-8" ?>

- <application name="XXDMV_MOBILE_OMS_COMMON_PKG"
targetNamespace="http://xmlns.oracle.com/apps/ont/soaprovider/plsql/rest/Create
Reservation/"
xmlns:tns="http://xmlns.oracle.com/apps/ont/soaprovider/plsql/rest/CreateReserv
ation/" xmlns="http://wadl.dev.java.net/2009/02"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:tns1="http://xmlns.oracle.com/apps/ont/rest/CreateReservation/create_rese
rvation/">
- <grammars>
<include
href="http://gitsdevap1.arg.ae:8000/webservices/rest/CreateReservation/?XSD=CR
EATE_RESERVATION_SYNCH_TYPEDEF.xsd"
xmlns="http://www.w3.org/2001/XMLSchema" />
</grammars>
- <resources
base="http://gitsdevap1.arg.ae:8000/webservices/rest/CreateReservation/">
- <resource path="create_reservation/">
- <method id="CREATE_RESERVATION" name="POST">
- <request>
<representation mediaType="application/xml" type="CREATE_RESERVATION_Input" />
<representation mediaType="application/json" type="CREATE_RESERVATION_Input" />
</request>
- <response>
<representation mediaType="application/xml" type="CREATE_RESERVATION_Output" />
<representation mediaType="application/json" type="CREATE_RESERVATION_Output" />
</response>
</method>
</resource>
</resources>
</application>
The last thing before we can invoke the web service is to set the Grant, this is done by clicking the Create Grant
button.

7. Navigate to Grants tab

8. Click on create grant and select the specific or all users from the list

9. Before the web service can be invoked we need to restart the Application server and clear the cache.
10. Clear cache: Use responsibility Functional Administrator and go to Core Services -> Caching
Framework -> Global Configuration -> Clear all cache.
Invoke EBS REST Service (in JSON message format)

1. Create Web service Request using ARC

Method: POST
Request URL: http://aets.arg.ae:8000/webservices/rest/Reservation/create_reservation/

2. Set Header parameters

Header Name: Authorization

Click on Edit icon and capture Username and Password

Header name: Content-Type

Header Value: application/json

Header Name: Accept-Language

Header Value: en-US
3. Set Body

{
"CreateReservation": {
"@xmlns":
"http://xmlns.oracle.com/apps/ont/rest/CreateReservation/create_reservation",
"RESTHeader": {
"xmlns": "http://xmlns.oracle.com/apps/fnd/rest/header",
"Responsibility": "ORDER_MGMT_SUPER_USER",
"RespApplication": "ONT",
"SecurityGroup": "STANDARD",
"NLSLanguage": "AMERICAN",
"Org_Id": "81"
},
"InputParameters": {
"P_ORG_ID": "81",
"P_ITEM_CODE": "AL2-K23-C-18",
"P_PARTY_ID": "221988638",
"P_CUST_ACCOUNT_NO": "2538461",
"P_RESERVE_QTY": "1",
"P_SERIAL_NUMBER": "",
"P_REMARKS": "",
"P_RP_ITEM_ID": "",
"P_RMA_FLAG": "",
"P_RMA_HEADER_ID": "",
"P_RMA_LINE_ID": "",
"P_SALE_TYPE": "New",
"P_QUOTE_HEADER_ID": "",
"P_QUOTE_LINE_ID": "",
"P_LEAD_MASTER_ID": "953211",
"P_ORGANIZATION_ID": "224",
"P_USER_ID": "1541"
}
}
}
4. Invoke Web service by clicking send button

5. Check the response by scrolling down

Verify the result in EBS

References

1. Oracle E-Business Suite Integrated SOA Gateway Implementation Guide

2. Setting Up Oracle E-Business Suite Integrated SOA Gateway
https://docs.oracle.com/cd/E26401_01/doc.122/e20925/T511175T578675.htm

3. How to create a custom integration interface in SOA Gateway

https://ankurjain26.blogspot.ae/2017/11/expose-custom-pl-sql-apis-as-soa.html