7 Replies Latest reply: Jan 6, 2011 9:23 AM by Wale Odusanya RSS

    Unable to Add Role to the user

    788860
      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
          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
            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
              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
                Have you checked Oracle eTRM website?

                Thanks,
                Hussein
                • 5. Re: Unable to Add Role to the user
                  788860
                  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
                    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
                      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);