- Use data from the HR system to determine hierarchy;
- Use functionality of an ERP module in another process;
- Expose services for self service purposes.
Oracle E-Business suite has built-in support for these types of integration. In this post we will look at an example of the third use case: a self service application for expense reports, that stores the result (a pay element) in the E-Business Suite.
There is a lot of documentation available, so far I have used:
Preparation
Like any other feature in the Oracle EBS, you need to have the right privileges to use the SOA Gateway.
Therefore, you have to create a user that has the correct permissions to generate and deploy a web service. The following table can be found in theOracle® E-Business Suite Integrated SOA Gateway User's Guide:
In my experience it is easiest to just use the Integration Repository Administrator role in the development environment. If you want to limit the number of people that can actually create the web services you can do that in the testing, staging and production environment.
In the Integrated SOA Gateway Implementation Guide it is explained in "Setting up Oracle E-Business Suite Integrated SOA Gateway" how you can set up these roles.
Generate the WSDL
To generate the WSDL, navigate to the PL/SQL API in the Integration Repository. In the project we generated a WSDL for PAY_ELEMENT_ENTRY_API, the function CREATE_ELEMENT.
Oracle® E-Business Suite Integrated SOA Gateway Developer's Guide describes in "Using PL/SQL as Web services" how you can accomplish this. In the screen shot below, you see what it looks like for the HR_EMPLOYEE_API for the function CREATE_EMPLOYEE. (NB: I use the HR_EMPLOYEE_API example in the screen shots, because in our environment we already generated the WSDL for the PAY_ELEMENT_ENTRY, so the screens look different).
Now you can view the WSDL in your browser, by selecting: "View WSDL". The Web Service status is added and is set to "Generated"
Deploy the WSDL
Once you have generated the WSDL, you need to actually deploy the webservice. This will install the required files on the Oracle Application Server.
In this example we use username tokens as authentication mechanism, but SAML tokens are also supported.
Grant Access
Now you have to grant access to the webservice by using the "Create Grant" option. The best way to handle this, is to create one user for example "selfsvc" that has access to the webservices on behalf of the users that use the self service application.
Call the Web Service
SQLJUTL is used when generating the web services from PL/SQL. This is the same technology that is used by the Oracle Database adapter and Eclipselink DBWS. This requires some specific knowlegde. For example a PL/SQL boolean is generated as an integer:
<element name="P_VALIDATE" type="int" db:index="1" db:type="INTEGER" minOccurs="0" nillable="true" />
Documentation about the mapping form PL/SQL to XML can be found in Oracle® Application Server Adapters for Files, FTP, Databases, and Enterprise Messaging User's Guide: "Acceptable values for the original
Calling the webservice requires both knowledge of the implementation of the EBS module and knowledge of XML and Web Services. For example, it expects an assignment_id and an element_link_id as a parameter, it uses date-tracking and has the option to validate the call, instead of actually executing (committing) the data.
Information about this can be found in the "Oracle Human Resources Management Systems Workforce Sourcing, Deployment, and Talent Management Guide". This guide explains for example the assignment concept:
"The assignment is the central concept that relates employees to the structures in which they work, and the compensation and benefits for which they are eligible.
You can test the web service using SOAPUI, or the standard test client on the Oracle Application server.
To test it with SOAP UI, you need to import the WSDL. You can find the address of the WSDL when you click on "View WSDL". Just copy the url in your browser into SOAP UI. It will look something like this:
http://[host]:[port]/webservices/SOAProvider/plsql/pay_element_entry_api/?wsdl
To test the web service in your browser, put the link in your browser, without the '?wsdl'
http://[host]:[port]/webservices/SOAProvider/plsql/pay_element_entry_api/
This will give you a webpage to test the service with:
Conclusion
We needed to install a number of patches before we were able to successfully use the SOA Gateway, but once the whole thing is setup (patches, users privileges etc), creating the web service is trivial. A very important benefit is that it is visible in the integration repository that a Web Service is generated, based on the PL/SQL API. This is much harder to keep track of when you use a Database adapter, Eclipselink DBWS or the Oracle Apps adapter.
Knowledge of the PL/SQL APIs in EBS is a must in your project. In our project we worked closely with the EBS functional and technical people to figure out how to use the PL/SQL APIs correctly and what APIs we needed. The biggest challenge in this type of project is to combine knowledge of EBS and PL/SQL with knowledge of Web Services and integration.
What is seriously lacking in the PL/SQL APIs are simple 'read functions'. So if the first use case applies (using data from HR), you need to write your own APIs or call the database tables directly. The latter is a bad idea in my opinion. This mean that you always have to write some APIs yourself when interfacing with EBS. In the Oracle® E-Business Suite Integrated SOA Gateway Developer's Guide you can find a description how you can add these custom interfaces to the repository and generate WSDLs from them in chapter "Creating and using custom integration interfaces".
There is a lot of documentation available, so far I have used:
- Oracle® Integration Repository User's Guide
- Oracle® E-Business Suite Integrated SOA Gateway Developer's Guide
- Oracle® E-Business Suite Integrated SOA Gateway User's Guide
- Integrated SOA Gateway Implementation Guide
- Oracle® Application Server Adapters for Files, FTP, Databases, and Enterprise Messaging User's Guide
- Oracle Human Resources Management Systems Workforce Sourcing, Deployment, and Talent Management Guide
Preparation
Like any other feature in the Oracle EBS, you need to have the right privileges to use the SOA Gateway.
Therefore, you have to create a user that has the correct permissions to generate and deploy a web service. The following table can be found in theOracle® E-Business Suite Integrated SOA Gateway User's Guide:
Privileges
|
System Integration Analyst
|
System Integration Developer
|
Integration Repository Administrator
|
|---|---|---|---|
| View public interfaces | Yes | Yes | Yes |
| View private/internal interfaces | No | Yes | Yes |
| Generate/regenerate Web Services (WSDL) | No | No | Yes |
| Deploy/redeploy Web Services | No | No | Yes |
| Undeploy Web Services | No | No | Yes |
| Subscribe to business events | No | No | Yes |
| Create Grants | No | No | Yes |
| Download Composite services | No (configurable) | Yes | Yes |
In my experience it is easiest to just use the Integration Repository Administrator role in the development environment. If you want to limit the number of people that can actually create the web services you can do that in the testing, staging and production environment.
In the Integrated SOA Gateway Implementation Guide it is explained in "Setting up Oracle E-Business Suite Integrated SOA Gateway" how you can set up these roles.
Generate the WSDL
To generate the WSDL, navigate to the PL/SQL API in the Integration Repository. In the project we generated a WSDL for PAY_ELEMENT_ENTRY_API, the function CREATE_ELEMENT.
Oracle® E-Business Suite Integrated SOA Gateway Developer's Guide describes in "Using PL/SQL as Web services" how you can accomplish this. In the screen shot below, you see what it looks like for the HR_EMPLOYEE_API for the function CREATE_EMPLOYEE. (NB: I use the HR_EMPLOYEE_API example in the screen shots, because in our environment we already generated the WSDL for the PAY_ELEMENT_ENTRY, so the screens look different).
Now you can view the WSDL in your browser, by selecting: "View WSDL". The Web Service status is added and is set to "Generated"
Deploy the WSDL
Once you have generated the WSDL, you need to actually deploy the webservice. This will install the required files on the Oracle Application Server.
In this example we use username tokens as authentication mechanism, but SAML tokens are also supported.
Grant Access
Now you have to grant access to the webservice by using the "Create Grant" option. The best way to handle this, is to create one user for example "selfsvc" that has access to the webservices on behalf of the users that use the self service application.
Call the Web Service
SQLJUTL is used when generating the web services from PL/SQL. This is the same technology that is used by the Oracle Database adapter and Eclipselink DBWS. This requires some specific knowlegde. For example a PL/SQL boolean is generated as an integer:
<element name="P_VALIDATE" type="int" db:index="1" db:type="INTEGER" minOccurs="0" nillable="true" />
Documentation about the mapping form PL/SQL to XML can be found in Oracle® Application Server Adapters for Files, FTP, Databases, and Enterprise Messaging User's Guide: "Acceptable values for the original
BOOLEAN parameter now that it is an INTEGER are 1 for true and 0 for false. Any value other than 1 is considered false. The generated wrapper procedure uses APIs from the SYS.SQLJUTL package to convert from INTEGER to BOOLEAN and vice-versa."Calling the webservice requires both knowledge of the implementation of the EBS module and knowledge of XML and Web Services. For example, it expects an assignment_id and an element_link_id as a parameter, it uses date-tracking and has the option to validate the call, instead of actually executing (committing) the data.
Information about this can be found in the "Oracle Human Resources Management Systems Workforce Sourcing, Deployment, and Talent Management Guide". This guide explains for example the assignment concept:
"The assignment is the central concept that relates employees to the structures in which they work, and the compensation and benefits for which they are eligible.
In Oracle HRMS, many of the activities you undertake in human resource management, such as vacancy management and budget planning, are based around assignments and not people. In particular, you enter all earnings, deductions, and other pay-related elements for the employee assignment, rather than the employee. This makes it possible to give an employee two or more assignments when this is necessary.
For example, if an employee has more than one role within your enterprise, he or she can have a separate assignment for each role."
You can test the web service using SOAPUI, or the standard test client on the Oracle Application server.
To test it with SOAP UI, you need to import the WSDL. You can find the address of the WSDL when you click on "View WSDL". Just copy the url in your browser into SOAP UI. It will look something like this:
http://[host]:[port]/webservices/SOAProvider/plsql/pay_element_entry_api/?wsdl
To test the web service in your browser, put the link in your browser, without the '?wsdl'
http://[host]:[port]/webservices/SOAProvider/plsql/pay_element_entry_api/
This will give you a webpage to test the service with:
Conclusion
We needed to install a number of patches before we were able to successfully use the SOA Gateway, but once the whole thing is setup (patches, users privileges etc), creating the web service is trivial. A very important benefit is that it is visible in the integration repository that a Web Service is generated, based on the PL/SQL API. This is much harder to keep track of when you use a Database adapter, Eclipselink DBWS or the Oracle Apps adapter.
Knowledge of the PL/SQL APIs in EBS is a must in your project. In our project we worked closely with the EBS functional and technical people to figure out how to use the PL/SQL APIs correctly and what APIs we needed. The biggest challenge in this type of project is to combine knowledge of EBS and PL/SQL with knowledge of Web Services and integration.
What is seriously lacking in the PL/SQL APIs are simple 'read functions'. So if the first use case applies (using data from HR), you need to write your own APIs or call the database tables directly. The latter is a bad idea in my opinion. This mean that you always have to write some APIs yourself when interfacing with EBS. In the Oracle® E-Business Suite Integrated SOA Gateway Developer's Guide you can find a description how you can add these custom interfaces to the repository and generate WSDLs from them in chapter "Creating and using custom integration interfaces".
Nice information. Very helpful.
ReplyDelete