EBS Integration with OIM : Employee Reconciliation : NumberFormatException: “BUSINESS_GROUP_ID”
This post is from our demo environment to configure Segregation of Duties (SoD) in EBS using GRC/OAACG/OIM. Contact Us if you are interested in demo of GRC/OAACG/OIM/EBS integration for SoD.
I discussed about Oracle EBS (R12/11i) integration with Oracle Identity Manager (OIM) here, and two type of connectors available for EBS integration are
a) EBS UM Connector : User Management to provisioning Accounts in EBS (FND_USER)
b) EBS ER Connector : Employee Reconciliation to create users in OIM from EBS EMployee record (PER_ALL_PEOPLE_F).
In this post I am going to share an issue I encountered in EBS-ER connector during reconciliation of Employee record from EBS to OIM.
For reconciliation of Employee Record from EBS to OIM, you run schedule job eBusiness Suite HRMS Trusted Reconciliation in OIM.
When I run this scheduled job I encountered error like
___
<Apr 10, 2014 11:24:03 PM BST> <Error> <OIMCP.EBSER> <BEA-000000> <================= Start Stack Trace =======================>
<Apr 10, 2014 11:24:03 PM BST> <Error> <OIMCP.EBSER> <BEA-000000> <oracle.iam.connectors.ebs.hrms.tasks.EmployeeReconciliationTask : execute>
<Apr 10, 2014 11:24:03 PM BST> <Error> <OIMCP.EBSER> <BEA-000000> <Query execution failed>
<Apr 10, 2014 11:24:03 PM BST> <Error> <OIMCP.EBSER> <BEA-000000> <Description : Failed to execute the query>
<Apr 10, 2014 11:24:03 PM BST> <Error> <OIMCP.EBSER> <BEA-000000> <oracle.iam.connectors.ebs.common.TargetOperationException: Failed to execute the query
at oracle.iam.connectors.ebs.common.dao.DBUtil.getFirstPage(Unknown Source)
at oracle.iam.platform.tx.OIMTransaction CallbackWithoutResult.process (OIMTransactionCallbackWithoutResult.java:9)
at oracle.iam.platform.tx.OIMTransactionCallback. doInTransaction(OIMTransactionCallback.java:13)
at org.springframework.transaction.support. TransactionTemplate.execute(TransactionTemplate.java:128)
at oracle.iam.platform.tx.OIMTransactionManager. execute(OIMTransactionManager.java:22)
ActionExecutorWrapper.execute(AbstractSubjectSecurity.java:228)
at oracle.security.jps.internal.jaas.CascadeActionExecutor$ SubjectPrivilegedAction.run(CascadeActionExecutor.java:68)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
CascadeActionExecutor.execute(CascadeActionExecutor.java:50)
at oracle.security.jps.internal.jaas.AbstractSubjectSecurity
$ActionExecutorWrapper.execute(AbstractSubjectSecurity.java:228)
at Thor.API.Security.LoginHandler.Assertion
LoginSession.runAs(AssertionLoginSession.java:93)
at oracle.iam.scheduler.impl.quartz.QuartzJob.execute(QuartzJob.java:77)
Caused by: oracle.iam.connectors.ebs.common.TargetOperationException: Failed to get the paged records
at oracle.iam.connectors.ebs.common.dao.DBUtil.getPagedRecords(Unknown Source)
… 32 more
Caused by: oracle.iam.connectors.ebs.common.TargetOperationException: Invalid format of NUMBER value
at oracle.iam.connectors.ebs.common.dao.DBUtil.setNamedParameters(Unknown Source)
… 33 more
Caused by: java.lang.NumberFormatException: For input string: “BUSINESS_GROUP_ID”
at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
… 34 more
>
<Apr 10, 2014 11:24:03 PM BST> <Error> <OIMCP.EBSER> <BEA-000000> <================= End Stack Trace =======================>
________
Import message in the whole error stack was Caused by: java.lang.NumberFormatException: For input string: “BUSINESS_GROUP_ID”
When reconciliation Job eBusiness Suite HRMS Trusted Reconciliation is run, it runs SQL query that fetches data from table PER_ALL_PEOPLE_F table of EBS and look for column BUSINESS_GROUP_ID
Query : Table PER_ALL_PEOPLE_F (search for BUSINESS_GROUP_ID and CURRENT_EMPLOYEE_FLAG, EFFECTIVE_START_DATE)
Fix : Set lookup Lookup.EBS.HRMS.QueryFilters in OIM Design Console
Log into OIM Design Console -> Administration=>Lookup Definition and search for Lookup.EBS.HRMS.QueryFilters
Enter the following Values…
fromDate = 01-Jan-2012|Date|DD-Mon-YYYY
businessGroupID = 202|number
toDate = 12-Apr-2014|Date|DD-Mon-YYYY
Save the Changes
(Here 202 is BUSINESS_GROUP_ID from PER_ALL_PEOPLE_F table)
Run the schedule job eBusiness Suite HRMS Trusted Reconciliation this should now create users in OIM (from EBS Employee)
Contact Us if you are interested in demo of GRC/OAACG/OIM/EBS integration or just integration of OIM with Oracle EBS.
About the Author Atul Kumar
Oracle ACE, Author, Speaker and Founder of K21 Technologies & K21 Academy : Specialising in Design, Implement, and Trainings.