ORA-01427 Error While Updating Purchase Requisition Document Type with Spot Buy (Doc ID 2806319.1)

Last updated on NOVEMBER 05, 2021

Applies to:

Symptoms

Updating Purchase Requisition Document Type ends with the below error, if the employee added to spot buy section, has more than one record in PER_ALL_PEOPLE_F table

ERROR
## Detail 0 ##
java.sql.SQLException: ORA-01427: single-row subquery returns more than one row

oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException:
JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM
(SELECT PoDocTypeCategoriesEO.ATTRIBUTE1,
PoDocTypeCategoriesEO.ATTRIBUTE10,
PoDocTypeCategoriesEO.ATTRIBUTE11,
PoDocTypeCategoriesEO.ATTRIBUTE12,
PoDocTypeCategoriesEO.ATTRIBUTE13,
PoDocTypeCategoriesEO.ATTRIBUTE14,
PoDocTypeCategoriesEO.ATTRIBUTE15,
PoDocTypeCategoriesEO.ATTRIBUTE2,
PoDocTypeCategoriesEO.ATTRIBUTE3,
PoDocTypeCategoriesEO.ATTRIBUTE4,
PoDocTypeCategoriesEO.ATTRIBUTE5,
PoDocTypeCategoriesEO.ATTRIBUTE6,
PoDocTypeCategoriesEO.ATTRIBUTE7,
PoDocTypeCategoriesEO.ATTRIBUTE8,
PoDocTypeCategoriesEO.ATTRIBUTE9,
PoDocTypeCategoriesEO.ATTRIBUTE_CATEGORY,
PoDocTypeCategoriesEO.CATEGORY_ID,
PoDocTypeCategoriesEO.CREATED_BY,
PoDocTypeCategoriesEO.CREATION_DATE,
PoDocTypeCategoriesEO.DOCUMENT_SUBTYPE,
PoDocTypeCategoriesEO.DOCUMENT_TYPE_CODE,
PoDocTypeCategoriesEO.DOC_TYPE_CATEGORY_ID,
PoDocTypeCategoriesEO.LAST_UPDATED_BY,
PoDocTypeCategoriesEO.LAST_UPDATE_DATE,
PoDocTypeCategoriesEO.LAST_UPDATE_LOGIN,
PoDocTypeCategoriesEO.ORG_ID,
PoDocTypeCategoriesEO.PROGRAM_APPLICATION_ID,
PoDocTypeCategoriesEO.PROGRAM_ID,
PoDocTypeCategoriesEO.PROGRAM_UPDATE_DATE,
PoDocTypeCategoriesEO.REQUEST_ID,
PoDocTypeCategoriesEO.ROWID,
PoDocTypeCategoriesEO.THRESHOLD_AMOUNT,
PoDocTypeCategoriesEO.USER_ID,
(SELECT USER_NAME FROM FND_USER FU WHERE
FU.USER_ID=PoDocTypeCategoriesEO.USER_ID) USER_NAME,
(SELECT FULL_NAME FROM PER_ALL_PEOPLE_F PER, FND_USER FU WHERE
PER.PERSON_ID=FU.EMPLOYEE_ID AND FU.USER_ID=PoDocTypeCategoriesEO.USER_ID)
EMPLOYEE_NAME,
(SELECT CONCATENATED_SEGMENTS FROM mtl_categories_kfv MCK WHERE
MCK.category_id=PoDocTypeCategoriesEO.category_id) CATEGORY_NAME
FROM PO_DOC_TYPE_CATEGORIES PoDocTypeCategoriesEO) QRSLT WHERE
DOCUMENT_TYPE_CODE = :1 AND DOCUMENT_SUBTYPE = :2 AND ORG_ID = :3

STEPS TO REPLICATE

The issue can be reproduced at will with the following steps:
1. Navigate to Purchasing Responsibility --> Setup --> Document Types
2. Query for Requisition Document type
3. Enter Spot Buy Options
4. Save the Changes, then error occurs.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.