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


Article ID: 141703


Updated On:


Clarity PPM SaaS


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"


Duplicate resource entry causes this issue. 


Release : All supported Clarity releases


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.