In a
previous post I discussed how to expose seeded APIs in Oracle EBS 12 as a Web Service.
But, as you can guess, seeded APIs are not always the easiest way to access EBS. For example, the PAY_ELEMENT.CREATE_ELEMENT_ENTRY procedure expects the programmer to take care of EBS specific arguments like element_link_id.
Our self service application has logic to submit, approve and check expenses. To do that, it uses employee ids and business rules about submitting, approving and checking steps. Users can view the state, get tasks assigned to them and can save their expenses in the application. This is setup using BPM, business rules, Java and ESB technologies. This application is blissfully ignorant about element_entry_link_ids, date tracking implementations etc. On top of that, EBS does not provide "select" APIs to get data from the application. We don't want to create SQL statements in our application, we like to hide the implementation and use APIs to communicate with EBS.
The good news is, Oracle E-Business Suite 12 provides a way to make custom code part of the integrated SOA Gateway.
This way you have the best of both worlds: monitoring and control of the services with the Integration repository and usable APIs for external applications. Let's see how that works.
Overview
The following steps have to be executed to accomplish this:
- Write your custom API
- Test it
- Annotate Custom APIs
- Validate Annotated Files
- Upload Validated Files
- Create Security Grants
- Generate Web Services
- Deploy Web Services
 |
From: Integrated SOA Gateway Developer's Guide |
Writing and testing PL/SQL code is beyond the scope of this blog and the last three steps were described in the previous post, so I will skip those steps here.
Documentation and roles
There are two roles involved: a
system integration developer and a
system integration administrator. The
'Integrated SOA Gateway Developer's Guide' describes how the annotations should be added to the package specification in the Chapter '
Creating and Using Custom Integration Interfaces'. Appendix A '
Integration Repository Annotation Standards' describes the annotations in detail. The '
Integrated SOA Gateway Implementation Guide' describes how to validate and upload the files.
Annotate Custom APIs
A system integration developer annotates the API that needs to be loaded in the integration repository. The integration repository parses the source files to populate itself.
So to populate the integration repository with your own Custom interfaces, you only need to annotate the package specification, not the package body. The syntax is similar to JavaDoc annotations:
@NameSpace:TypeName keyString
First of all, you need to annotate the package, with a scope, the product name, the displayname and the category. Secondly, you annotate the procedures that you want to expose as a Web Service. The annotations for the procedure should be placed between the definition and ';'.
create or replace package xxgr_soa_pay_elements is
/* $Header: xxgr_soa_pay_elements $ */
/*#
* This custom PL/SQL package can be used to add pay elements.
It hides some particulars of the PAY_ELEMENT_ENTRY_API
* @rep:scope public
* @rep:product PER
* @rep:displayname Create Pay element entry
* @rep:category BUSINESS_ENTITY CREATE_ELEMENT_ENTRY
*/
procedure create_element_entry
( p_employee_number in number
, p_element_name in varchar2
, p_element_eff_start_date in varchar2
, p_element_eff_end_date in varchar2
, p_entry_type in number
, p_input_value_name1 in varchar2
, p_entry_value1 in number
, p_input_value_name2 in varchar2 default null
, p_entry_value2 in number default null
, p_input_value_name3 in varchar2 default null
, p_entry_value3 in number default null
, p_date_earned in varchar2 default null
, p_subpriority in number default null
, p_effective_start_date out varchar2
, p_effective_end_date out varchar2
, p_element_entry_id out number
, p_object_version_number out number
, p_create_warning out number
)
/*#
* Use this procedure to create a single entry for an expense. NB
* this procedure needs to be optimized to use an array for the flex fields.
* Date formats are dd-MM-YYYY
* @param employee_number employee number that the pay element applies to
* @param element name name of the pay element, type of expense
* @param p_element_eff_start_date effective start date of the pay element.
* @param p_element_eff_end_date effective end date of the pay element
* @param p_entry_type a constant 'E' for earnings
* @param p_input_value_name1 label of the first entry
* @param p_entry_value1 value of the first entry, the amount that is expensed
* @param p_input_value_name2 label of the second entry optional
* @param p_entry_value2 value of the second entry, optional
* @param p_input_value_name3 label of the third entry, optional
* @param p_entry_value3 value of the third entry, optional
* @param p_date_earned date that the value is earned. Optional
* @param p_subpriority priority of the pay element, optional
* @param p_subpriority in number default null
* @param p_effective_start_date returns the effective start date
* @param p_effective_end_datereturns the effective end date
* @param p_element_entry_id the number of the pay element (expense) that is
* created
* @param p_object_version_number the version number of the object
* @param p_create_warning flags whether warning is created.1=true, 0=false
* @rep:displayname Create a pay element (expense)
* @rep:category BUSINESS_ENTITY
* @rep:scope public
* @rep:lifecycle active
*/
;
end xxgr_soa_pay_elements;
Validate Annotated Files
The integration repository administrator validates the annotated files before uploading them to the repository. This is done using the Integration Repository Parser, a standalone design time tool. While executing the parser, the annotated source files are validated based on the interface type supported for customization. If no error occurs, an Integration Repository loader file (iLDT) will be created. However, before you can start, you need to setup the environment: install Perl modules and possibly install some patches (depending on the EBS12.x version you are using).
Once you installed the necessary Perl modules, you execute the command:
$IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v
-username=<a fnd username> <product>:<relative path from product
top>:<fileName>:<version>=<Complete File Path, if not in currect
directory>
Upload Validated Files
If the annotated files were valid, they can be uploaded to the integration repository by someone with the rol integration repository administrator.
Not that for an object (or class) that is already present in the Integration Repository, the Integration Repository Loader program reloads the new definition of that object ONLY if the new version is greater than the current version. Make sure you increment the Header version of the target source file.
Note that there is no GUI for uploading validated files. You can yse Telnet to have command access to the Oracle E-Business Suite Release 12 instanc and use the following command to upload the iLDT file:
$FND_TOP/bin/FNDLOAD <db_connect> 0 Y UPLOAD $fnd/patch/115/import/wfirep.lct <ildt file>
Conclusion
We showed a PL/SQL example here, but the same procedure applies to Java classes, or other supported interface types. Check the developers guide and the implementation guide for a list of supported interface types.
As I stated in my previous post, in order to successfully interface with an Oracle E-Business Suite instance, it is critical to cooperate with the developers and administrators of the Oracle E-Business Suite. That way you ensure that the interfaces are both manageable for the people responsible for running and maintaining the Oracle E-Business suite implementation and maintainable and usable for the developers of the self service application. When executed this way, Oracle E-Business Suite becomes a first class citizen of the 'SOA-World' we live in nowadays.
