Invalid Transactions with error ORA-01422: exact fetch returns more than request number of rows

book

Article ID: 141703

calendar_today

Updated On:

Products

STARTER PACK-CLARITY PPM Clarity PPM SaaS

Issue/Introduction

There are timesheet transactions that are in the Invalid Transactions list after timesheet posting.  The rejection reason is "New Transaction"
In looking at the IMP_TRANSACTIONIMPORT table on the database these transaction have the following:
sourcemodule = 51 (timesheet)
importstatus = E
fatalError = "-1422 - 8=>ORA-01422: exact fetch returns more than request number of rows"

Cause

Duplicate resource entry causes this issue. 

Environment

Release : All supported Clarity releases

Resolution

The issue is caused by a duplicate for the resource_code in PAC_MNT_RESOURCES table:
To find out the duplicate, run:
select * from pac_mnt_resources where resource_code ='<put the resource code here>'

Then compare with SRM_RESOURCES table and CMN_SEC_USERS table. You will usually find that one of the records is inactive and incorrect.
Our recommendation is, based on what is incorrect, to update the record to the correct resource_code and active status to be 0 on the wrong entry. For example, if the user in CMN_SEC_USERS is already inactive, and the only issue is the PAC_MNT_RESOURCES resource_code, update the record to match the correct one. (For assistance please let Support know)
Once this is done, run Post Transactions to Financials and it should correctly pick up those entries.