Thursday, April 28, 2022

Integrated SOA Gateway (ISG) - Exposing Custom API as REST Web Service

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

FNDLOAD: Generic Loader

+—————————————————————————+

+—————————————————————————+

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

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. 


https://hostname/webservices/rest/EmpInsert/SAVE_EMPLOYEE_PRC/


Share:

Tuesday, March 22, 2022

Integrated SOA Gateway (ISG) - Exposing Standard API as REST Web Service

 In a nutshell, we need to perform following steps to expose PL/SQL as a web service:

1. Search API in Integration Repository 

2. Deploy API as REST Service 

3. Create Grants for REST Service 

4. Invoke EBS REST Service (in JSON message format) 

5. Verify the results 


Here we will take an example of creating a user by exposing FND_USER_PKG Interface API and then invoking it to create user. 


Following steps will explain the process of exposing the standard API and then invoking it. 


Step 1: Navigate to Integration Repository Responsibility, Click on Search. 



Then on search Page enter “FND_USER_PKG” in Internal Name and click on Go 



Step 2: Click on the search result, it will display List of methods available in the Interface Package.



Click on REST Web Service Tab



This page will display List of methods that can be deployed. 

Step 3: Now select Create/Update User (we will create a user by invoking this Service once Deployed.) and enter Service Alias (This is mandatory) and then Click on Deploy button at bottom. 

You will receive message that service is successfully deployed and the status will be deployed. 



Step 4: Now you will see the link to WADL file, Click on the link. It shows the physical location of the service endpoint where the service is hosted.

<?xml version="1.0" encoding="UTF-8"?> 
<application xmlns:tns1="http://xmlns.oracle.com/apps/fnd/rest/create_user/load_row/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://wadl.dev.java.net/2009/02" xmlns:tns="http://xmlns.oracle.com/apps/fnd/soaprovider/plsql/rest/fnd_user_pkg/" targetNamespace="http://xmlns.oracle.com/apps/fnd/soaprovider/plsql/rest/fnd_user_pkg/" name="FND_USER_PKG"><grammars><include xmlns="http://www.w3.org/2001/XMLSchema" 
href="http://xxx-xxx-apdv01.cli.ad:8005/webservices/rest/Create_User/?XSD=LOAD_ROW_SYNCH_TYPEDEF.xsd"/> 
</grammars> 
<resources base="http://xxx-xxx-apdv01.cli.ad:8005/webservices/rest/Create_User/"><resource path="load_row/"> 
<method name="POST" id="LOAD_ROW"><request><representation type="tns1:InputParameters" mediaType="application/xml"/> 
<representation type="tns1:InputParameters" mediaType="application/json"/> 
</request> 
</method> 
</resource> 
</resources> 
</application> 

Now open the link highlighted in Yellow in new Browser window, it will show the XSD file with input parameters that will be used later for invoking the Web Service. 

Step 5.Now Click on the Grants Tab, select Create/Update User and then click on Create Grant button 



Step 6: Select a grantee type. 
Grantee Type can be one of the following values: 
o USER - The grantee is an individual user who was selected directly. 
o GROUP - The grantee is a group of users or a member of a group of users. 
o GLOBAL - The grant was issued to all users. 

If you selected Specific User or Group of Users, specify the user or group for which to create the grants in the Grantee Name field. 
In our case, we will enter SYSADMIN and then click on Create Grant Button. 



Now the service has been exposed as a REST interface.

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


https://hostname/webservices/rest/create_user/load_row/

Share:

Friday, February 25, 2022

What is Integrated SOA Gateway (ISG) and Integration Repository Customizations

What is Integrated SOA Gateway (ISG)?

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 


Integration Repository Customizations

Customization of Integration Repository means option to add custom interfaces into Integration Repository.

Interfaces are programs and technologies in Integration Repository, eg. plsql, java, business events, xml gateway etc.

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.




Share:

Friday, January 7, 2022

How to find Oracle Fusion Instance Name through SQL

Wondering how to find the name of your Fusion instance ? Here's the query that will retrieve this information -


SELECT
    SUBSTR(
        EXTERNAL_VIRTUAL_HOST,
        1,
        INSTR(EXTERNAL_VIRTUAL_HOST, '.') -1
    ),
    DEPLOYED_DOMAIN_NAME
FROM
    FUSION.ASK_DEPLOYED_DOMAINS

Share:

Thursday, December 16, 2021

What Are The Roles Required To Create Data Model in Oracle Fusion

If you don't see option to create a new Data model or update an existing one, there's a chance that your user account is missing the required roles.
Let's see what roles are required to create Data model in Oracle Fusion BI Environment.


To get Data model access, create a new custom role and map the below roles to the custom role.

  => BI Consumer Role

  => BI Publisher Data Model Developer

Assign this custom role to your user and you should get the access to create a new Data model or edit an existing one.

Share: