Concurrent Manager:cleanup_node failed due to ORA-01427

While starting the concurrent Manager in the env, we were getting below error
Cause: cleanup_node failed due to ORA-01427: single-row subquery returns more than one row
ORA-06512: at “APPS.FND_CP_FNDSM”, line 29
ORA-06512: at line 1.
This error happens when we have more than two FNDSM of FNDIM defined for the node.So we selected the node name of all the concurrent Manager in the system
Solution:

We can resolve the solution manually through below update
SQL> select CONCURRENT_QUEUE_NAME,NODE_NAME from fnd_concurrent_queues group by CONCURRENT_QUEUE_NAME,NODE_NAME;

CONCURRENT_QUEUE_NAME NODE_NAME
FNDSCH SUNTEST
PODAMGR SUNTEST
FTE_TXN_MANAGER SUNTEST
FNDSM_20151_SUNTEST SUNTEST
FNDICM SUNTEST
FNDTMTST SUNTEST
STANDARD SUNTEST
FNDIM_SUNTEST SUNTEST
FNDIM_20153_SUNTEST SUNTEST
WFMLRSVC SUNTEST
XDP_Q_FE_READY_SVC SUNTEST
WMSTAMGR SUNTEST
INVMGR SUNTEST
AMSDMIN SUNTEST
FNDSM_SUNTEST SUNTEST

 

We can see there are two FNDSM and FNDIM for the node SUNTEST.
FNDSM_20151_SUNTEST SUNTEST
FNDIM_SUNTEST SUNTEST
FNDIM_20153_SUNTEST SUNTEST
FNDSM_SUNTEST SUNTEST

SQL> update fnd_concurrent_queues set NODE_NAME=null where CONCURRENT_QUEUE_NAME=’FNDSM_20151_SUNTEST’;

1 row updated.
SQL> update fnd_concurrent_queues set NODE_NAME=null where CONCURRENT_QUEUE_NAME=’FNDIM_20153_SUNTEST’;

1 row updated.

SQL> commit;

Commit complete.

SQL> select CONCURRENT_QUEUE_NAME,NODE_NAME from fnd_concurrent_queues group by CONCURRENT_QUEUE_NAME,NODE_NAME;

 

CONCURRENT_QUEUE_NAME NODE_NAME
FNDSCH SUNTEST
PODAMGR SUNTEST
FTE_TXN_MANAGER SUNTEST
FNDSM_20151_SUNTEST
FNDICM SUNTEST
FNDTMTST SUNTEST
STANDARD SUNTEST
FNDIM_SUNTEST SUNTEST
FNDIM_20153_SUNTEST
WFMLRSVC SUNTEST
XDP_Q_FE_READY_SVC SUNTEST
WMSTAMGR SUNTEST
INVMGR SUNTEST
AMSDMIN SUNTEST
FNDSM_SUNTEST SUNTEST

Or we can run conc clean and run autoconfig again to resolve the issue
SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;
EXIT;

General stuff about ORA-01427: single-row subquery returns more than one row

This error is raised, when a sub-query returns more than one row to an Equality or in-Equality operator. The reason being there are restrictions on the legal comparison operators

The single row subquery operator are

Symbol Meaning
= equal
> greater than
>= greater than or equal
< less than
<= less than or equal
<> not equal
!= not equal

There are two ways to solve the problem

  1. either we change the query to use Multiple -row Subquery Operators 
    Multiple-row Sub-query Operators
    Symbol Meaning
    IN equal to any member in a list
    NOT IN not equal to any member in a list
    ANY returns rows that match any value on a list
    ALL returns rows that match all the values in a list

    2)  We look at the query and find out what is happening, why it is returning more rows than expected. Like the way we resolved this error for Concurrent Manager issue. It is generally advisable  use the sub-query based on primary key in order to get consistent result,