Connector for Oracle eBusiness Suite

The Oracle eBusiness Suite Connector is built on the Web Services Connector. Please refer to the TechNet documentation for the Web Services Connector for additional information.

Summary

Features / Supported variants
Connected data source versions /
  • Oracle E-Business Suite 12.1.3

Scenarios /
  • Object Lifecycle Management
  • Password Management

Operations /
  • Full import(with custom code)
  • Delta import (with custom code)
  • Export
  • Password Management

Schema /
  • Person/Employee
  • User

Permissions in connected data source

To create or perform any of the supported tasks in Web Service connector for all the supported data sources, you must have following permissions.

  • Apps Schema Connect Role' (UMX|APPS_SCHEMA_CONNECT)

Ports and protocols

This depends upon the Oracle E-Business Suite installation and configuration.

Connector update history

Build / Release / Revision list
5.0.458.0 / 2012 June / First release of the Web Services Connector.

Requirements, before you begin, and installation

Installation of Default Projects

The default project installer file is available at theMicrosoft Download Center.Download the installer file and run to install.

Double click the downloaded project file to begin installation.

  1. The following screen appears, click Yes.

  1. Next license agreement screen appears; click Yes to accept the terms and conditions.

  1. The next screen prompts to specify the location for installing the default project. Specify the location: %FIM_INSTALL_DIR\2010\Synchronization Service\Extensionsand click OK.
  1. The installation starts and the successful completion is reported. Click OK to exit setup wizard.

Content of Default Project

Web Services

The discovery operation retrievethe endpoints defaultPersonAdd, defaultPersonAssignment, defaultPersonFullImport, defaultPersonFullImportCustom, defaultPersonUpdate, defaultUserCreate, defaultUserFullImportCustom and defaultUserUpdateDisableand all the packages that have been exposed through the web service at Oracle EBS. The exposed web service here includes the packages listed below:

  • FND_USER_PKG.LOAD_ROW
  • IRC_PARTY_API.UPDATE_USER
  • HR_EMPLOYEE_API. CREATE_EMPLOYEE
  • HR_PERSON_ADDRESS_API.CREATE_PERSON_ADDRESS
  • HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA
  • HR_ASSIGNMENT_API.UPDATE_EMP_ASG
  • HR_PHONE_API.CREATE_PHONE
  • HR_PERSON_API.DELETE_PERSON
  • HR_ASSIGNMENT_API.DELETE_ASSIGNMENT
  • HR_PHONE_API.DELETE_PHONE
  • HR_PERSON_API.UPDATE_PERSON
  • HR_ASSIGNMENT_API.UPDATE_EMP_ASG
  • HR_PERSON_ADDRESS_API.UPDATE_PERSON_ADDRESS
  • HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA
  • HR_ASSIGNMENT_API.ACTIVATE_EMP_ASG
  • HR_ASSIGNMENT_API.ACTUAL_TERMINATION_EMP_ASG
  • HR_ASSIGNMENT_API.SET_NEW_PRIMARY_ASG
  • HR_ASSIGNMENT_API.SUSPEND_EMP_ASG
  • HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA
  • HR_EX_EMPLOYEE_API.ACTUAL_TERMINATION_EMP
  • HR_EX_EMPLOYEE_API.UPDATE_TERM_DETAILS_EMP
  • HR_PHONE_API.UPDATE_PHONE

Workflows

A native package in Oracle EBS is used to perform a single task. There are certain operations for which native packages are not available and hence the default project support them using custom packages. Following are the workflows that are supported for:

User Object

FIM Operation / Implemented through native web service (package) operation
Full Import / Yes
Delta Import / No
Export Add / Yes
Export Delete / Yes
Export Replace / Yes
Set Password / Yes
Change Password / Yes

Person Object

FIM Operation / Implemented through native web service (package) operation
Full Import / Yes
Delta Import / No
Export Add / Yes
Export Delete / Yes
Export Replace / Yes
Set Password / N/A
Change Password / N/A

Exposing Native & Custom Web Service for Oracle EBS 12.1.3 Connector

Web Service Configuration Tool discovers the Web service through a WSDL (Web Services Description Language) and retrieves its services, endpoints and operations (APIs) it provides. Services, endpoints and operations (APIs) are used by the Web Service Connector to access the Oracle e-Business Suite (EBS) server and synchronize identities with Forefront Identity Manager (FIM) 2010.

For a web service to be discovered, it is first required to be exposed at the Oracle EBS 12.1.3. This topic describes the process of exposing the web service from Oracle EBS 12.1.3Integrated SOA Gateway. You must perform following activities to expose the web service at Oracle EBS server:

  1. Generate and Deploy WSDL
  2. Grant WSDL access to users

Generate and Deploy WSDL

  1. Login with Admin user SYSADMIN.

  1. The Integrated SOA Gateway responsibility screen appears.

  1. After selecting the Integrated SOA Gateway responsibility, access Integrated SOA Repository.
  1. Navigate through Application Technology --> User Management, to retreive all the API’s that can be exposed in the module.
  1. To expose User related API, click User under User Management. You will see the list of available user related API’s that can be exposed. Click the one you want to expose.

After selecting, the next screen provides the details of integration point. Click the new Generate WSDL button to expose the selected API.

  1. After two or three seconds you should get a confirmation message, like the one below, to let you know this was completed successfully. Also, the Generate WSDL button will be replaced by Regenerate WSDL. Next you need to enable this new service. This is done by clicking on the Deploy button which is highlighted in the image below.After successful WSDL generation, button Generate WSDL will disappear and you will see the Regenerate WSDL and a Deploy button below.
  1. After successful deployment, you will receive the confirmation for successful deployment and the Redeploy button appears. Using View WSDL link, you can see the complete WSDL that is generated and use it to call the exposed service.

Grant WSDL access to users

The final step is to grant access to the service, so that you will be able to invoke it. To do this, expand the list of operations in the last part of the interface page in Integration Repository, and check the box next to the interface you want to grant access to, and click the Create Grant button.

You can choose to grant it to “All Users” or “Specific User”.

  1. Select the operation/operations to be granted and click Create Grant button.
  1. Now enter the username for the user you want to grant access to, or use the search button to find the user. In this example, it will be sufficient to grant access to ASADMIN.
  1. Select the user and Apply it.
  1. If you want to see to which Users have been Granted the permission to consume the WSDL, Click on Show. Once expanded, you can see thelist of users to which permission have been granted.
Important:
This security grant will not be effective unless we bounce the application tier.

Command for bouncing the application tier services.
1)Go to %ADMIN_SCRIPTS_HOME%
2)Run adoacorectl.cmd STOP
3)Run adoafmctl.cmd STOP
4)Run adoacorectl.cmd START
5)Run adoafmctl.cmd START

Custom Web Service

Native packages are not available to achieve following features:

  • Get User Details
  • Get Person Object Version number
  • Get Assignment Object Version number
  • Get Last Employee Id
  • Get Last User Id

Develop the custom package and later expose them as Web Service and Web Service Configuration Tool discover the Web service through a WSDL.

Important:
Perquisites to develop and expose custom packages:
  1. Integration Repository Parser
  2. Perl Module
  3. Compress-Raw-Zlib-2.009
  4. Compress-Zlib-2.009
  5. Class-MethodMaker-1.12

Sample Custom Operations

Get User Detail and Last User Id Operations

Package Specification

CREATEOR REPLACE PACKAGEFND_USER_DTLAS

/* $Header: FND_USER_DTL.pls */

/*#

* This is the source file to FND_USER_DTL

* @rep:scope public

* @rep:product FND

* @rep:displayname FND User Detail

*/

--

--

/*#

* This is source for GET_USER_DETAILS

* @rep:displayname Get User Details

* @rep:category BUSINESS_ENTITY FND_USER

* @rep:scope public

* @rep:lifecycle active

*/

PROCEDUREGET_USER_DETAILS(p_start_user_idINnumber,

p_end_user_id INnumber,

p_userOUTSYS_REFCURSOR,

p_errorOUTnocopyvarchar2);

/*#

* This is source for GET_USER_DETAILS

* @rep:displayname Get Last User ID

* @rep:category BUSINESS_ENTITY FND_USER

* @rep:scope public

* @rep:lifecycle active

*/

PROCEDUREGET_LAST_USER_ID(p_last_user_idOUTnumber);

ENDFND_USER_DTL;

Package Body

CREATEor REPLACE PACKAGEBODYFND_USER_DTLAS

/* $Header: FND_USER_DTL.pls $ */

--

--Procedure to return user details

--

PROCEDUREGET_USER_DETAILS(p_start_user_idINNUMBER,

p_end_user_id INNUMBER,

p_userOUTSYS_REFCURSOR,

p_errorOUTNOCOPYVARCHAR2)

IS

BEGIN

OPEN p_user FOR

SELECT user_id,

user_name,

description,

start_date,

end_date,

last_logon_date,

password_date,

password_accesses_left,

password_lifespan_accesses,

password_lifespan_days,

employee_id,

email_address,

fax,

customer_id,

supplier_id,

person_party_id

FROM FND_USER

WHERE user_idBETWEENp_start_user_idANDp_end_user_id;

EXCEPTION

WHENOTHERSTHEN

p_error:=substr(SQLERRM,1,1500);

ENDGET_USER_DETAILS;

--

-- Procedure for returning Last User ID

--

PROCEDUREGET_LAST_USER_ID(p_last_user_idOUTNUMBER)IS

BEGIN

SELECTMAX(user_id)INTOp_last_user_id

FROMFND_USER;

ENDGET_LAST_USER_ID;

ENDFND_USER_DTL;

Get Last Person Id, Person Object and Assignment Object Version number

Package Specification

CREATEORREPLACEpackageHR_PERSON_INFOAS

/* $Header: HR_PERSON_INFO.pls $ */

/*#

* This package contains APIs

* for getting Last person ID, object_version_number.

* @rep:scope public

* @rep:product per

* @rep:displayname Person Table Info

* @rep:lifecycle active

* @rep:compatibility S

* @rep:category BUSINESS_ENTITY PER_EMPLOYEE

*/

--

-- Returns Last Person_ID from PER_ALL_PEOPLE_F

--

-- {Start Of Comments}

/*#

* This API returns the Last Person ID

* @paramx_last_person_ID out number Last Person ID

* @rep:scope public

* @rep:lifecycle active

* @rep:displayname Last Person ID

* @rep:compatibility S

*/

PROCEDUREGet_Last_Person_ID(x_last_person_IDOUTNUMBER);

--

-- Get_person_object_version (PUBLIC)

-- Get person version number for a person on some effective date

--

-- Input (Mandatory)

-- Effective Date

-- Person ID

-- Returns

-- Get person version number for a person on some effective date

--

-- {Start Of Comments}

/*#

* This API returns the person object version number

* @paramx_effective_date Effective Date

* @paramx_person_id Person ID

* @paramx_object_version_number Object Version Number

* @rep:scope public

* @rep:lifecycle active

* @rep:displayname Person object Version Number

* @rep:compatibility S

*/

PROCEDUREGet_person_object_version

(

x_person_idINNUMBER,

x_effective_dateINDATE,

x_object_version_numberOUTNUMBER

);

--

-- Get_assignment_object_version (PUBLIC)

-- Get assignment version number for a person on some effective date

--

-- Input (Mandatory)

-- Effective Date

-- Assignment ID

-- Returns

-- Get assignment version number for a person on some effective date

--

-- {Start Of Comments}

/*#

* This API returns the assignment object version number

* @paramx_effective_date Effective Date

* @paramx_assignment_id Assignment ID

* @paramx_object_version_number Object Version Number

* @rep:scope public

* @rep:lifecycle active

* @rep:displayname Assignment Version Number

* @rep:compatibility S

*/

PROCEDUREGet_assignment_object_version

(

x_assignment_idIN NUMBER,

x_effective_dateINDATE,

x_object_version_numberOUTNUMBER

);

ENDHR_PERSON_INFO;

Package Body

CREATEorREPLACEPACKAGE BODYHR_PERSON_INFOAS

--

-- Procedure Get_Last_Person_ID Returns the Last Person_ID in the system

--

PROCEDUREGet_Last_Person_ID(x_last_person_ID OUTnumber)IS

l_max_person_idNUMBER;

BEGIN

SELECTMAX(person_id)INTOl_max_person_id

FROMper_all_people_f;

x_last_person_ID:=l_max_person_id;

ENDGet_Last_Person_ID;

--

-- Procedure Get_person_object_version Returns the person object_version_number on some effective date

--

PROCEDUREGet_person_object_version

(

x_person_idIN NUMBER,

x_effective_dateINDATE,

x_object_version_numberOUTNUMBER

)IS

l_object_version_numberNUMBER;

BEGIN

IFx_person_idISNULLTHEN

fnd_message.set_name('PER','PER_INVALID_PERSON');

fnd_message.set_token('PERSON_ID',X_PERSON_ID);

app_exception.raise_exception;

ENDIF;

IFx_person_idISNULLTHEN

fnd_message.set_name('PER','EFFECTIVE_DATE cannot be Blank');

fnd_message.set_token('EFFECTIVE_DATE',X_EFFECTIVE_DATE);

app_exception.raise_exception;

ENDIF;

SELECTobject_version_numberINTOl_object_version_number

FROMper_all_people_f

WHEREperson_id=x_person_id

ANDx_effective_dateBETWEENeffective_start_dateANDeffective_end_date;

x_object_version_number :=l_object_version_number;

ENDGet_person_object_version;

--

-- Procedure Get_assignmen_object_version Returns the assignment object_version_number on some effective date

--

PROCEDUREGet_assignment_object_version

(

x_assignment_idIN NUMBER,

x_effective_dateINDATE,

x_object_version_numberOUTNUMBER

)IS

BEGIN

IFx_assignment_idISNULLTHEN

fnd_message.set_name('PER','ASSIGNMENT_ID cannot be Blank');

fnd_message.set_token('ASSIGNMENT_ID',X_ASSIGNMENT_ID);

app_exception.raise_exception;

ENDIF;

IFx_effective_dateISNULLTHEN

fnd_message.set_name('PER','EFFECTIVE_DATE cannot be Blank');

fnd_message.set_token('PERSON_ID',X_EFFECTIVE_DATE);

app_exception.raise_exception;

ENDIF;

SELECTobject_version_numberINTOx_object_version_number

FROMper_all_assignments_f

WHEREassignment_id=x_assignment_id

ANDx_effective_dateBETWEENeffective_start_dateANDeffective_end_date;

ENDGet_assignment_object_version;

ENDHR_PERSON_INFO;

Generating iLDT file

Expose custom packages as web service required ildt file need to generate. Please follow the instructions from the Integrated SOA Gateway Implementation Guide(Integration Repository Parser) section “Generating ILDT Files”.

Note:
If “.pls” file is not placed on proper location for example:
User:<drive>:\< Oracle Install folder>\<Server Name>\apps\apps_st\appl\fnd\12.0.0\patch\115\sql
Person:<drive>:\< Oracle Install folder>\<Server Name>\apps\apps_st\appl\per\12.0.0\patch\115\sql
If “.pls” file are not placed on above location integration repository parser will not be able to read the file and will give errors.

Uploading iLDT file

Once the ildt file are generated please follow the follow the instructions from the Integrated SOA Gateway Implementation Guide(Integration Repository Parser) section “Uploading ILDT Files to Integration Repository”

Creating security grants

This is similar to the native packages.

Generating web services

This is similar to the native packages.

Deploying web service

This is similar to the native packages.

Note:
This setting will not be effective unless we bounce the application tier.

Performance Testing

Scale / Topology / Hardware
  • 10,000 User objects data
  • 10,000 Person objects data
/
  • Two servers
  • FIM Synchronization Service and FIM Synchronization database collocated on one server (FIM Server).
  • Oracle ebusiness Suite Application Server
/ FIM Server Hardware Configuration:
  • 2-gigabyte (GB) SDRAM
  • Intel® Xeron® 2.27GHz Processor
  • Hard disk volumes:
  • Single volume

Note: The server hardware used is not representative for a large organization. The numbers presented should be used to understand the difference between different operations. You are encouraged and expected to configure your own test environments to more accurately estimate capacity and performance. Microsoft cannot guarantee that organizations will experience the same capacity or performance characteristics, even if the FIM Synchronization service components are deployed and configured identically to the components that are described in this guide,

The tests and results shown in the following table were performed using scripted provisioning code. The Web Service Connector was connected to the Oracle EBS Serveras shown in the previous table.

Operation / Elapsed time (minutes: seconds) / Statistics / Rate
OracleEBS Connector Full Import (User Object) / 67:12 / Total Imported users:
10,000 / 2.48 objects read/ second
OracleEBS Connector Full Import (Person Object) / 25:19 / Total Imported Persons:
10,000 / 6.58 objects read/ second
OracleEBS Connector Export (User
Object) / a)124:23
b)236:24
c)115:49 / a)Adds: 10,000 Users
b)Update: 10,000 Users
c)Delete: 10,000 Users / a)1.34 Objects exported / second
b).705 Objects updated/ second
c)1.439 Objects deleted / second
OracleEBS Connector Export (Person Object) / a)440:55
b)872:36
c)136:36 / a)Adds: 10,000 Persons
b)Update: 10,000 Persons
c)Delete:10,000 Persons / a)0.378 Objects exported/ second
d)0.191 Objects updated/ second
b)1.22 Objects deleted / second

Reference information