Custom Interface PL/SQL to return Employee Information using Oracle E-Business Suite Integrated SOA Gateway

In my previous posts, I wrote about how to create custom interface types using Java as part of the Oracle E-Business Suite Integrated SOA Gateway. In this post, I will be building the service using the same requirement I had in the Application Module Service. Only this time, I will be using PL/SQL.

First thing we'll do is to create our package scripts. One for the specification and one for the body.

Here's the package specification. Filename will be xxhr_soa_pkg.pls

create or replace package APPS.xxhr_soa_pkg as.
/* $Header: $ */
/*#
* This is the a sample custom HR SOA Web Service
* @rep:scope public
* @rep:product per
* @rep:category BUSINESS_ENTITY HR_USER_HOOK
* @rep:displayname HR SOA Web Service
*/

  type person_rec is record ( person_id             number
                            , effective_start_date  date
                            , effective_end_date    date
                            , employee_number       varchar2(30)
                            , marital_status        varchar2(30)
                            , date_of_birth         date );
                            
  type person_tbl is table of person_rec;

/*#
 * This API returns a list of person information.
 * @param p_employee_number Employee Number of the Person
 * @return This API will return person_tbl type.
 * @rep:displayname Get Employees
 * @rep:scope public
 * @rep:lifecycle active
*/
  function get_employees ( p_employee_number in varchar2 ) return person_tbl;
end;
/

Here's the package body and the filename will be xxhr_soa_pkg.plb.

create or replace package body APPS.xxhr_soa_pkg as

  function get_employees ( p_employee_number in varchar2 ) return person_tbl
  is
    l_employees person_tbl;
  begin
    select person_id
         , effective_start_date
         , effective_end_date
         , employee_number
         , marital_status
         , date_of_birth
      bulk
   collect
      into l_employees
      from per_people_x
     where hr_person_type_usage_info.get_user_person_type(sysdate,person_id) like 'Employee%'
       and employee_number = nvl(p_employee_number,employee_number);
       
    return l_employees;
  end;
end;
/

Note that only the package specification requires the appropriate annotations. More information on PL/SQL Annotations here.

Of course, we also need to compile the package scripts into the target database. After that, we need to move these files (the specification script at the least) so that we can generate the iLDT file similar to what we did in the previous posts. See command below.

[las@server ~/soa-plsql]$ $IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=sysadmin per:patch/115/sql:xxhr_soa_pkg.pls:12.0=xxhr_soa_pkg.pls

If everything went well, the expected output should be somewhat similar to this.

[las@server ~/soa-plsql]$ $IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=sysadmin per:patch/115/sql:xxhr_soa_pkg.pls:12.0=xxhr_soa_pkg.pls
# Interface Repository Annotation Processor, 12.0.0

#
# Generating annotation output.
# Processing file 'xxhr_soa_pkg.pls'.
# Using YAPP-based parser.
#  Found a package-level annotation for 'APPS.XXHR_SOA_PKG'.
#  Found a detail-level annotation...
# Found a function named 'GET_EMPLOYEES'.
# Done all files.
[las@server ~/soa-plsql]$

Next, we need to upload the iLDT file to our Integrated SOA repository. We can do this by executing the fndload command with the appropriate lct file as shown below.

[las@server ~/soa-plsql]$ FNDLOAD apps/<apps_password> 0 Y UPLOAD $FND_TOP/patch/115/import/wfirep.lct

Again if successful, expected output should be somewhat similar to this.

[las@server ~/soa-plsql]$ FNDLOAD apps/<apps_password> 0 Y UPLOAD $FND_TOP/patch/115/import/wfirep.lct xxhr_soa_pkg_pls.ildt
Log filename : L6603415.log


Report filename : O6603415.out
[las@server~/soa-plsql]$

Now let’s check the service in the Repository.

Login to your Oracle E-Business Suite instance as SYSADMIN.



Switch to Integrated SOA Gateway responsibility.



Select Integration Repository



Click Search and then search for your service. In this case, we'll search for HR SOA Web Service as Internal Name.

Notice here that for PL/SQL Interface Type, you have both SOAP and REST web service available. In this post, I will be focusing on the REST web service. Before we go into that, let's click on the Get_Employees item below to see more information.

Moving on, click on the REST Web Service tab. In here, we will need to set an alias for this service which will be used as part of the URL when invoking it. I will enter hr in this case. Then click Deploy.

Click on the View WADL link to get more information on how to invoke the service. This will show you an XML description of the service.

<?xml version = '1.0' encoding = 'UTF-8'?>
<application name="XXHR_SOA_PKG" targetNamespace="http://xmlns.oracle.com/apps/per/soaprovider/plsql/rest/hr/" xmlns:tns="http://xmlns.oracle.com/apps/per/soaprovider/plsql/rest/hr/" xmlns="http://wadl.dev.java.net/2009/02" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:tns1="http://xmlns.oracle.com/apps/per/rest/hr/get_employees/">
   <grammars>
      <include href="http://host:port/webservices/rest/hr/?XSD=GET_EMPLOYEES_SYNCH_TYPEDEF.xsd" xmlns="http://www.w3.org/2001/XMLSchema"/>
   </grammars>
   <resources base="http://host:port/webservices/rest/hr/">
      <resource path="get_employees/">
         <method id="GET_EMPLOYEES" name="POST">
            <request>
               <representation mediaType="application/xml" type="GET_EMPLOYEES_Input"/>
               <representation mediaType="application/json" type="GET_EMPLOYEES_Input"/>
            </request>
            <response>
               <representation mediaType="application/xml" type="GET_EMPLOYEES_Output"/>
               <representation mediaType="application/json" type="GET_EMPLOYEES_Output"/>
            </response>
         </method>
      </resource>
   </resources>
</application>

Get the URL for the xsd and open it on a different browser tab/window for additional information. In this case, the URL is

http://host:port/webservices/rest/hr/?XSD=GET_EMPLOYEES_SYNCH_TYPEDEF.xsd

<?xml version = '1.0' encoding = 'UTF-8'?>
<schema targetNamespace="http://xmlns.oracle.com/apps/per/rest/hr/get_employees/" xmlns="http://www.w3.org/2001/XMLSchema" xmlns:db="http://xmlns.oracle.com/apps/per/rest/hr/get_employees/" elementFormDefault="qualified" xmlns:head="http://xmlns.oracle.com/apps/fnd/rest/header" xmlns:isgf="http://xmlns.oracle.com/isg/servicefault">
   <import namespace="http://xmlns.oracle.com/isg/servicefault" schemaLocation="http://host:port/webservices/rest/hr/?XSD=ISGServiceFault.xsd"/>
   <import namespace="http://xmlns.oracle.com/apps/fnd/rest/header" schemaLocation="http://host:port/webservices/rest/hr/?XSD=RESTHeader.xsd"/>
   <element name="InputParameters">
      <complexType>
         <sequence>
            <element name="P_EMPLOYEE_NUMBER" type="string" db:index="1" db:type="VARCHAR2" minOccurs="0" nillable="true"/>
         </sequence>
      </complexType>
   </element>
   <element name="OutputParameters">
      <complexType>
         <sequence>
            <element name="XXHR_SOA_PKG-24GET_EMPLOYEES" type="db:APPS.XXHR_SOA_PKG_PERX3501705X1X1" db:index="0" db:type="Array" minOccurs="0" nillable="true"/>
         </sequence>
      </complexType>
   </element>
   <complexType name="APPS.XXHR_SOA_PKG_PERX3501705X1X2">
      <sequence>
         <element name="PERSON_ID" type="decimal" db:type="NUMBER" minOccurs="0" nillable="true"/>
         <element name="EFFECTIVE_START_DATE" type="dateTime" db:type="DATE" minOccurs="0" nillable="true"/>
         <element name="EFFECTIVE_END_DATE" type="dateTime" db:type="DATE" minOccurs="0" nillable="true"/>
         <element name="EMPLOYEE_NUMBER" db:type="VARCHAR2" minOccurs="0" nillable="true">
            <simpleType>
               <restriction base="string">
                  <maxLength value="30"/>
               </restriction>
            </simpleType>
         </element>
         <element name="MARITAL_STATUS" db:type="VARCHAR2" minOccurs="0" nillable="true">
            <simpleType>
               <restriction base="string">
                  <maxLength value="30"/>
               </restriction>
            </simpleType>
         </element>
         <element name="DATE_OF_BIRTH" type="dateTime" db:type="DATE" minOccurs="0" nillable="true"/>
      </sequence>
   </complexType>
   <complexType name="APPS.XXHR_SOA_PKG_PERX3501705X1X1">
      <sequence>
         <element name="XXHR_SOA_PKG-24GET_EMPLOYEES_ITEM" type="db:APPS.XXHR_SOA_PKG_PERX3501705X1X2" db:type="Struct" minOccurs="0" maxOccurs="unbounded" nillable="true"/>
      </sequence>
   </complexType>
<element name="GET_EMPLOYEES_Input">
      <complexType>
         <sequence>
            <element ref="head:RESTHeader"/>
            <element ref="db:InputParameters"/>
         </sequence>
      </complexType>
   </element>
   <element name="GET_EMPLOYEES_Output">
      <complexType>
         <choice>
            <element ref="db:OutputParameters"/>
            <element ref="isgf:ISGServiceFault"/>
         </choice>
      </complexType>
   </element>
</schema>

Let's not forget to setup the grants. In my case, I granted it to SYSADMIN user.

Then of course we need to bounce the following services

opmn
oafm

Forgot to mention that PL/SQL Interface Type is always invoke using POST method. And since this is the case, we need to build the request body. We will also use Basic Authentication as our authorization method.

So to put it all together, the URL will be:

http://host:port/webservices/rest/hr/get_employees/

The request body will be:

{
  "GET_EMPLOYEES_Input": {
    "@xmlns": "http://xmlns.oracle.com/apps/per/rest/hr/get_employees/",
    "RESTHeader": {
      "@xmlns": "http://xmlns.oracle.com/apps/fnd/rest/header",
      "Responsibility": "SYSTEM_ADMINISTRATOR",
      "RespApplication": "SYSADMIN",
      "SecurityGroup": "STANDARD",
      "NLSLanguage": "AMERICAN"
    },
    "InputParameters": { "P_EMPLOYEE_NUMBER": "123456" }
  }
}

Here's a sample screen shot invoking the service using Postman and getting a response.

Some pointers that I noticed. Input Parameters seems to be optional. So if you wanted to pass a null value for P_EMPLOYEE_NUMBER, you can do either of the following:

"InputParameters": { "P_EMPLOYEE_NUMBER": "" }

or just simply not include it in the body.

"InputParameters": { }

And that's it. Enjoy!