Approver Does Not Have Entry In WF_ROLES Error

AME Issue - Incorrect entry in WF_ROLES


Symptom:



A.  User received following error for Purchase Requisition AME workflow.

 

ORA-20001: The approver identified by the following parameters is invalid: originating system PER originating-system ID PER_ID. Please delete or replace this approver wherever they occur in AME data, including approval groups, list-modification conditions, and substitution actions. This approver does not have an entry in wf_roles. (ORIG_SYSTEM_ID=3783).

 

B.  Error occurs attempting to approve a Quote:

ORA-20001: The approver identified by the following parameters is invalid: originating system PER originating-system ID PER_ID. Please delete or replace this approver wherever they occur in AME data, including approval groups, list-modification conditions, and substitution actions. This approver does not have an entry in wf_roles. (ORIG_SYSTEM_ID=1) 

 

C.  In 11.5.10, the following errors occur when submitting Requisitions for AME Approval

1) When trying to enter a Requisition via iProcurement the error is:

You do not have the authority to approve this requisition and an Approval List was not created automatically. Please contact your system administrator.

2) When trying to create a Requisition from the Core application then the status of the requisition changes to In Process and workflow is failing with this error:

Error Message ORA-06502: PL/SQL: numeric or value error
Error Stack AME_APPROVER_TYPE_PKG.getWfRolesNameAndDisplayName(65120) AME_ABSOLUTE_JOB_LEVEL_HANDLER.handler(65121) AME_ENGINE.processActionType(65122) AME_ENGINE.processRules(65123) AME_ENGINE.updateTransactionState(65124) AME_ENGINE.getNextApprovers(65125) AME_API2.getNextApprovers4(65126) AME_API.getNextApprover(65127) POR_AME_APPROVAL_LIST_WF1S.Get_Next_Approver(Get_Next_Approver: 002-211076-REQUISITION-PURCHASE, ORA-20001: The approver identified by the following parameters is invalid: originating system PER originating-system ID PER_ID. Please delete or replace this approver wherever they occur in AME data, including approval groups, list-modification conditions, and substitution actions.
This approver does not have an entry in wf_roles. (ORIG_SYSTEM_ID=34986)) Wf_Engine_Util.Function_Call(POR_AME_APPROVAL_LIST_WF1S.GET_NEXT_APPROVER, REQAPPRV, 211076-244413, 198714, RUN)

This issue is happening only for one preparer only , with username: PREPARER

Steps to Reproduce:

1. Login as username PREPARER
2. In Purchasing Super User responsibility, navigate to Requisitions Menu --> Requisitions POXRQERQ form
3. Create a requisition and then click on the Approve button so to forward it for approval.

 

D.  Submitting a transaction (e.g. leave of absence) fails with the following error:

 

ERROR The approver identified by the following parameters is invalid: originatingsystem PER originating-system ID PER_ID. Please delete or replace this approverwherever they occur in AME data, including approval groups, list-modificationconditions, and substitution actions. This approver does not have an entry inwf_roles. (ORIG_SYSTEM_ID=-1)

Cause:

A.   No user is associated to the approver person derived by dynamic approval group. Users with person association or Responsibilities are valid roles in the workflow system.

 

B.  Upon reviewing the output of the following

i) note down the max(log_id) using the SQL

select max(log_id)
from ame_exceptions_log;

ii) reproduce the issue

iii) Provide the output of the following SQL in excel sheet format

select * from ame_exceptions_log where log_id > {provide above max(log_id) here}

And comparing this output the AME Utility report output it can be concluded that the exception details on the user's instance shows that AME is unable to construct the supervisory hierarchy due to the presence of invalid approver(with orig system id =1) in the chain.

From the set up report it can be seen that the attribute 'SUPERVISORY_NON_DEFAULT_STARTING_POINT_PERSON_ID' has this value.

The person under SUPERVISORY_NON_DEFAULT_STARTING_POINT_PERSON_ID is not a valid approver 

 

C.  AME selects a person as an approver and that person has no record in WF_ROLES table because No fnd user is associated to the approver person derived by dynamic approval group.

The following SQL returns no record, showing no row exists in WF_ROLES for the person_id indicated in the error message ' This approver does not have an entry in wf_roles. (ORIG_SYSTEM_ID=34986)) '

SQL> Select orig_system, orig_system_id, name,
display_name, status, expiration_date
from wf_roles
where orig_system_id = 34986
and orig_system = 'PER';

 

D.  One of the approver group's queries returns an invalid value (e.g. "-1" for person_id).







Useful Note ID:


Approver Does Not Have Entry In WF_ROLES Error: ORA-20001 / The Approver Identified By The Following Parameters Is Invalid (Doc ID 336625.1)




Solution:


1. Run the following SQL to identify whose approver person is missing a record in WF_ROLES table

 

SQL> Select *
from per_all_people_f
where person_id = XXXXX;

1 record with PERSON_ID XXXXX FULL_NAME LastNameApprover, Name

2. Possible reasons why the approver has missing record :

1. This may be because the person (person_id=34986) has been terminated in which case he should be removed from any groups used in AME.
1. Navigation if HR is fully installed: HR Responsibility > People > Enter and Maintain.
2. Navigation if HR is shared: Purchasing Responsibility > Setup > Personnel > Employees.
2. Query up the person, LastNameApprover, Name
1. Ensure no effective end date has been entered
2. Also, the person should not be anymore the supervisor of any preparer.  
3. If the person is a current employee then ensure the person has an FND user name and that no effective end date has been entered:
1. Navigation is: System Administrator Responsibility >Security > User >Define
4. If the person already has a FND user name then run these steps to syncronize the information of the user:
1. Login as System Administrator
2. Query user 
3. Remove values from person name and email address 
4. Save the changes 
5. Re-Query user 
6. Re-add the data in the person region for the approver 
7. Save the changes

3. Make sure, each approver has a user name ( Ideally unique user name) associated.

4. Run the Synchronize WF LOCAL tables concurrent process.

5. For symptom b. please ensure the person represented by the'SUPERVISORY_NON_DEFAULT_STARTING_POINT_PERSON_ID' attribute is an valid approver.
6. For  symptom d. please follow these steps.

First, identify which group has a query that returns the invalid value. This can be done with the following script (getApplicableRules.sql):

-- getApplicableRules.sql
-- This script gives the list of applicable rules for a transaction.
-- User has to provide the value for the variable "transactionId"
-- before running the script.

set serveroutput on size 1000000
declare
fndApplicationId integer :=800;
transactionTypeId varchar2(30) := 'SSHRMS';
transactionId varchar2(10) := <Give the transaction id here. for e.g. '1234'>;
ruleIdsOut ame_util.idList;
ruleDescriptionsOut ame_util.stringList;
approvers ame_util.approversTable2;
flagApproversAsNotified varchar2(20) := ame_util.booleanFalse;
approvalProcessCompleteYNOut varchar2(20);
printString varchar2(200) := '';

begin
ame_engine.updateTransactionState(isTestTransactionIn => false,
isLocalTransactionIn => false,
fetchConfigVarsIn => true,
fetchOldApproversIn => false,
fetchInsertionsIn => false,
fetchDeletionsIn => false,
fetchAttributeValuesIn => true,
fetchInactiveAttValuesIn => false,
processProductionActionsIn => false,
processProductionRulesIn => false,
updateCurrentApproverListIn => false,
updateOldApproverListIn => false,
processPrioritiesIn => true,
prepareItemDataIn => false,
prepareRuleIdsIn => true,
prepareRuleDescsIn => true,
transactionIdIn => transactionId,
ameApplicationIdIn => null,
fndApplicationIdIn => fndApplicationId,
transactionTypeIdIn => transactionTypeId);
ame_engine.getApplicableRules
(ruleIdsOut => ruleIdsOut
,ruleDescriptionsOut => ruleDescriptionsOut);
--
for i in 1..ruleIdsOut.count loop
dbms_output.put_line('Applicable rule ' || i || ' is : ' || ruleDescriptionsOut(i));
end loop;
--
ame_api2.getNextApprovers4(applicationIdIn => fndApplicationId,
transactionTypeIn => transactionTypeId,
transactionIdIn => transactionId,
flagApproversAsNotifiedIn => flagApproversAsNotified,
approvalProcessCompleteYNOut => approvalProcessCompleteYNOut,
nextApproversOut => approvers);

if approvalProcessCompleteYNOut = ame_util.booleanTrue then
dbms_output.put_line ('Approval Process Completed');
else
dbms_output.put_line ('Approval Process Not Yet Complete');
end if;
dbms_output.put_line ('Total Approvers Fetched = ' || approvers.count);
dbms_output.put_line (' ');
if approvers.count <> 0 then
printString := '';
printString := printString || rpad('Id',3,' ');
printString := printString || rpad('Approver',15,' ');
printString := printString || rpad('ORD',4,' ');
printString := printString || rpad('IC',10,' ');
printString := printString || rpad('IT',5,' ');
printString := printString || rpad('SL',3,' ');
printString := printString || rpad('ATID',7,' ');
printString := printString || rpad('GCID',7,' ');
printString := printString || rpad('Approval Status',20,' ');
printString := printString || rpad('Occ',4,' ');
printString := printString || rpad('MO',2,' ');
dbms_output.put_line('-------------------------------------------------------------------------------');
dbms_output.put_line(printString);
dbms_output.put_line('-------------------------------------------------------------------------------');
for i in 1 .. approvers.count loop
printString := '';
printString := printString || rpad(to_char(i),3,' ');
printString := printString || rpad(approvers(i).name,15,' ');
printString := printString || rpad(approvers(i).approver_order_number,4,' ');
printString := printString || rpad(nvl(approvers(i).item_class,'null'),10,' ');
printString := printString || rpad(nvl(approvers(i).item_id,'null'),5,' ');
printString := printString || rpad(approvers(i).authority,3,' ');
printString := printString || rpad(approvers(i).action_type_id,7,' ');
printString := printString || rpad(approvers(i).group_or_chain_id,7,' ');
printString := printString || rpad(nvl(approvers(i).approval_status,'null'),20,' ');
printString := printString || rpad(approvers(i).occurrence,4,' ');
printString := printString || rpad(approvers(i).member_order_number,2,' ');
dbms_output.put_line(printString);
end loop;
end if;
end;
/

 

Run this script from SQLPLUS and enter the transaction_id of the failing transaction. The output will be similar to the following: declare

*
ORA-20001: The approver identified by the following parameters is invalid:
originating system PER originating-system ID PER_ID.
Please delete or replace this approver wherever they occur in AME data,
including approval groups, list-modification conditions, and substitution actions. This approver
does not have an entry in wf_roles. (ORIG_SYSTEM_ID=-1)
ORA-06512: at "APPS.AME_API2", line 1338
ORA-06512: at line 41
Applicable rule 1 is : Rule A
Applicable rule 2 is : Rule B

Fix the error in the rule(s) identified by this script


SQL Queries in analyzing the issue

Use the below sqls in analyzing the issue, take the Orig_system_id from the error and place in the below query to see of the wf_roles got updated or not.

select * from wf_roles where orig_system_id = '4241';
select * from wf_per_role_roles where orig_system_id=4241;
select * from fnd_user where email_address='Blogspot@XXXXX.com';