This discussion is archived
7 Replies Latest reply: Jan 6, 2011 7:23 AM by wale odusanya RSS

Unable to Add Role to the user

788860 Newbie
Currently Being Moderated
Hi

I have list of roles from this table in JTF_RS_ROLES_TL in EBS User Database.

Now I am trying to call the Stored Procedure WF_LOCAL_SYNC.PropagateUserRole from external application with a valid role from JTF_RS_ROLES_TL table.

But the stored procedure is throwing me the below errors.

ERROR [ACTIVE] ExecuteThread: '4' for queue: 'weblogic.kernel.Default (self-tuning)' OIMCP.EBSUM - ================= Start Stack Trace =======================
ERROR [ACTIVE] ExecuteThread: '4' for queue: 'weblogic.kernel.Default (self-tuning)' OIMCP.EBSUM - oracle.iam.connectors.ebs.usermgmt.integration.EBSUserManagement : addRoleToUser
ERROR [ACTIVE] ExecuteThread: '4' for queue: 'weblogic.kernel.Default (self-tuning)' OIMCP.EBSUM - Failed to add user role
ERROR [ACTIVE] ExecuteThread: '4' for queue: 'weblogic.kernel.Default (self-tuning)' OIMCP.EBSUM - Description : *ORA-20002: [WF_NO_ROLE] NAME=61~Technical Writer ORIG_SYSTEM=NULL ORIG_SYSTEM_ID=NULL*
ORA-06512: at "APPS.WF_LOCAL_SYNCH", line 2052
ORA-06512: at line 1

ERROR [ACTIVE] ExecuteThread: '4' for queue: 'weblogic.kernel.Default (self-tuning)' OIMCP.EBSUM - java.sql.SQLException: *ORA-20002: [WF_NO_ROLE] NAME=61~Technical Writer ORIG_SYSTEM=NULL ORIG_SYSTEM_ID=NULL*
ORA-06512: at "APPS.WF_LOCAL_SYNCH", line 2052
ORA-06512: at line 1

     at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
     at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
     at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
     at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
     at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
     at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030)
     at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:191)
     at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:944)
     at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
     at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3381)
     at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3462)
     at oracle.jdbc.driver.OracleCallableStatement.executeUpdate(OracleCallableStatement.java:3877)
     at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1349)
     at oracle.iam.connectors.ebs.usermgmt.integration.EBSUserManagementHelper.addRoleToEBSUser(Unknown Source)
     at oracle.iam.connectors.ebs.usermgmt.integration.EBSUserManagement.addRoleToUser(Unknown Source)
     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
     at java.lang.reflect.Method.invoke(Method.java:597)
     at com.thortech.xl.adapterGlue.ScheduleItemEvents.adpEBSADDROLE.ADDROLE(adpEBSADDROLE.java:175)
     at com.thortech.xl.adapterGlue.ScheduleItemEvents.adpEBSADDROLE.implementation(adpEBSADDROLE.java:92)
     at com.thortech.xl.client.events.tcBaseEvent.run(Unknown Source)
     at com.thortech.xl.dataobj.tcDataObj.runEvent(Unknown Source)
     at com.thortech.xl.dataobj.tcScheduleItem.runMilestoneEvent(Unknown Source)
     at com.thortech.xl.dataobj.tcScheduleItem.eventPostInsert(Unknown Source)
     at com.thortech.xl.dataobj.tcDataObj.insert(Unknown Source)
     at com.thortech.xl.dataobj.tcDataObj.save(Unknown Source)
     at com.thortech.xl.dataobj.tcOrderItemInfo.processChildTableChange(Unknown Source)
     at com.thortech.xl.dataobj.tcOrderItemInfo.eventPostInsert(Unknown Source)
     at com.thortech.xl.dataobj.tcUDProcess.eventPostInsert(Unknown Source)
     at com.thortech.xl.dataobj.tcDataObj.insert(Unknown Source)
     at com.thortech.xl.dataobj.tcDataObj.save(Unknown Source)
     at com.thortech.xl.dataobj.tcTableDataObj.save(Unknown Source)
     at com.thortech.xl.ejb.beansimpl.tcFormInstanceOperationsBean.addProcessFormChildData(Unknown Source)
     at com.thortech.xl.ejb.beansimpl.tcFormInstanceOperationsBean.addProcessFormChildData(Unknown Source)
     at com.thortech.xl.ejb.beans.tcFormInstanceOperationsSession.addProcessFormChildData(Unknown Source)
     at com.thortech.xl.ejb.beans.tcFormInstanceOperations_2j82mm_EOImpl.addProcessFormChildData(tcFormInstanceOperations_2j82mm_EOImpl.java:782)
     at Thor.API.Operations.tcFormInstanceOperationsClient.addProcessFormChildData(Unknown Source)
     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
     at java.lang.reflect.Method.invoke(Method.java:597)
     at Thor.API.Base.SecurityInvocationHandler$1.run(Unknown Source)
     at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
     at weblogic.security.service.SecurityManager.runAs(Unknown Source)
     at weblogic.security.Security.runAs(Security.java:41)
     at Thor.API.Security.LoginHandler.weblogicLoginSession.runAs(Unknown Source)
     at Thor.API.Base.SecurityInvocationHandler.invoke(Unknown Source)
     at $Proxy75.addProcessFormChildData(Unknown Source)
     at com.thortech.xl.webclient.actions.UserDefinedFormAction.addChildFormData(Unknown Source)
     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
     at java.lang.reflect.Method.invoke(Method.java:597)
     at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:280)
     at com.thortech.xl.webclient.actions.tcLookupDispatchAction.execute(Unknown Source)
     at com.thortech.xl.webclient.actions.tcActionBase.execute(Unknown Source)
     at com.thortech.xl.webclient.actions.tcAction.execute(Unknown Source)
     at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
     at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
     at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
     at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
     at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)
     at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)
     at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:292)
     at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)
     at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:42)
     at com.thortech.xl.webclient.security.SecurityFilter.doFilter(Unknown Source)
     at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:42)
     at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3496)
     at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
     at weblogic.security.service.SecurityManager.runAs(Unknown Source)
     at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2180)
     at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2086)
     at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1406)
     at weblogic.work.ExecuteThread.execute(ExecuteThread.java:201)
     at weblogic.work.ExecuteThread.run(ExecuteThread.java:173)



Pls Help.
  • 1. Re: Unable to Add Role to the user
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    Please see if (Error when Assigning Using a Responsibility and Security Profile [ID 334270.1]) helps.

    Thanks,
    Hussein
  • 2. Re: Unable to Add Role to the user
    788860 Newbie
    Currently Being Moderated
    Hi

    I am not getting any error while adding the responsibility.

    I am fetching the roles from JTF_RS_ROLES_TL but I think when ever the procedure is getting executed it is expecting the values for ORIG_SYSTEM and ORIG_SYSTEM_ID.

    But I don't understand what is the relation between the JTF_RS_ROLES_TL and these columns ORIG_SYSTEM and ORIG_SYSTEM_ID.

    Can anyone help me?

    Edited by: AAG on Jan 4, 2011 8:17 AM
  • 3. Re: Unable to Add Role to the user
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl post details of OS, database and EBS versions. What is the purpose of your procedure ?

    HTH
    Srini
  • 4. Re: Unable to Add Role to the user
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    Have you checked Oracle eTRM website?

    Thanks,
    Hussein
  • 5. Re: Unable to Add Role to the user
    788860 Newbie
    Currently Being Moderated
    Actually I have EBS User Management Connector(installed in Oracle Identity Manager) which provisions users with roles and responsibilities to EBS Suite User.

    With this OOTB connector I am able to create user and add responsibilities, but for adding roles the EBS team has told me to query the table JTF_RS_ROLES_TL instead of the table wf_local_roles which was there before.

    So with the roles from JTF_RS_ROLES_TL, I am not able to add Role but with the roles from wf_local_roles I am able to add roles to user.

    OOTB query
    Lookup.EBS.UMX.Roles=SELECT (CONCAT(fa.application_id || '~', b.name)) AS CODE, (b.display_name) AS DECODE FROM fnd_application fa, wf_local_roles b WHERE b.orig_system = 'UMX' AND b.status = 'ACTIVE' AND fa.application_short_name = b.owner_tag

    New Query
    Lookup.EBS.UMX.Roles=SELECT CRM.ROLE_NAME AS CODE, CRM.ROLE_DESC AS DECODE FROM JTF_RS_ROLES_TL CRM

    The product details are E-Business Suite HRMS 11.5.10 on OS HP-UX.

    Pls help.
  • 6. Re: Unable to Add Role to the user
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    Now I am trying to call the Stored Procedure WF_LOCAL_SYNC.PropagateUserRole from external application with a valid role from JTF_RS_ROLES_TL table.
    Please post the code you are using.

    Thanks,
    Hussein
  • 7. Re: Unable to Add Role to the user
    wale odusanya Newbie
    Currently Being Moderated
    The code:

    PROCEDURE propagateUserRole(p_user_name in varchar2,
    p_role_name in varchar2,
    p_user_orig_system in varchar2 default null,
    p_user_orig_system_id in number default null,
    p_role_orig_system in varchar2 default null,
    p_role_orig_system_id in number default null,
    p_start_date in date default null,
    p_expiration_date in date default null,
    p_overwrite in boolean default FALSE,
    p_raiseErrors in boolean default FALSE,
    p_parent_orig_system in varchar2 default null,
    p_parent_orig_system_id in varchar2 default null,
    p_ownerTag in varchar2 default null,
    p_createdBy in number default null,
    p_lastUpdatedBy in number default null,
    p_lastUpdateLogin in number default null,
    p_creationDate in date default null,
    p_lastUpdateDate in date default null,
    p_assignmentReason in varchar2 default null,
    p_updateWho in boolean default null,
    p_attributes in wf_parameter_list_t default null);

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points