JBO-27122: SQL Error: While Viewing Or Updating A PO (Doc ID 1920250.1)

Last updated on SEPTEMBER 06, 2016

Applies to:

Oracle Procurement Contracts - Version 12.1.1 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.1 , Deliverables Management

When trying to open a deliverable from a PO Document : 'Contract Terms' page Or from Manage Deliverables page from Buyers work center, the following error occurs:

ERROR
-----------------------
You have encountered an unexpected error.

The exception stack shows the following
Error Page
Exception Details.
 oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT DeliverableEO.DELIVERABLE_ID,
  DeliverableEO.DELIVERABLE_NAME,
  DeliverableEO.DESCRIPTION,
  DeliverableEO.COMMENTS,
  DeliverableEO.DELIVERABLE_TYPE,
  DeliverableEO.DELIVERABLE_STATUS,
  DeliverableEO.STATUS_CHANGE_NOTES,
  DeliverableEO.BUSINESS_DOCUMENT_TYPE,
  DeliverableEO.BUSINESS_DOCUMENT_ID,
  DeliverableEO.BUSINESS_DOCUMENT_NUMBER,
  DeliverableEO.BUSINESS_DOCUMENT_VERSION,
  DeliverableEO.RESPONSIBLE_PARTY,
  DeliverableEO.EXTERNAL_PARTY_CONTACT_ID,
  DeliverableEO.INTERNAL_PARTY_CONTACT_ID,
  DeliverableEO.MANAGE_YN,
  DeliverableEO.DISPLAY_SEQUENCE,
  DeliverableEO.FIXED_DUE_DATE_YN,
  DeliverableEO.ACTUAL_DUE_DATE,
  DeliverableEO.PRINT_DUE_DATE_MSG_NAME,
  DeliverableEO.RECURRING_YN,
  DeliverableEO.NOTIFY_PRIOR_DUE_DATE_YN,
  DeliverableEO.NOTIFY_PRIOR_DUE_DATE_VALUE,
  DeliverableEO.NOTIFY_PRIOR_DUE_DATE_UOM,
  DeliverableEO.NOTIFY_COMPLETED_YN,
  DeliverableEO.NOTIFY_OVERDUE_YN,
  DeliverableEO.NOTIFY_ESCALATION_YN,
  DeliverableEO.NOTIFY_ESCALATION_VALUE,
  DeliverableEO.NOTIFY_ESCALATION_UOM,
  DeliverableEO.ESCALATION_ASSIGNEE,
  DeliverableEO.AMENDMENT_OPERATION,
  DeliverableEO.AMENDMENT_NOTES,
  DeliverableEO.LAST_AMENDMENT_DATE,
  DeliverableEO.PRIOR_NOTIFICATION_ID,
  DeliverableEO.COMPLETED_NOTIFICATION_ID,
  DeliverableEO.OVERDUE_NOTIFICATION_ID,
  DeliverableEO.ESCALATION_NOTIFICATION_ID,
  DeliverableEO.ORIGINAL_DELIVERABLE_ID,
  DeliverableEO.REQUESTER_ID,
  DeliverableEO.INTERNAL_PARTY_ID,
  DeliverableEO.EXTERNAL_PARTY_ID,
  DeliverableEO.RECURRING_DEL_PARENT_ID,
  DeliverableEO.RELATIVE_ST_DATE_DURATION,
  DeliverableEO.RELATIVE_ST_DATE_UOM,
  DeliverableEO.RELATIVE_ST_DATE_EVENT_ID,
  DeliverableEO.RELATIVE_END_DATE_DURATION,
  DeliverableEO.RELATIVE_END_DATE_UOM,
  DeliverableEO.RELATIVE_END_DATE_EVENT_ID,
  DeliverableEO.REPEATING_DAY_OF_MONTH,
  DeliverableEO.REPEATING_DAY_OF_WEEK,
  DeliverableEO.REPEATING_FREQUENCY_UOM,
  DeliverableEO.FIXED_START_DATE,
  DeliverableEO.FIXED_END_DATE,
  DeliverableEO.LANGUAGE,
  DeliverableEO.OBJECT_VERSION_NUMBER,
  DeliverableEO.DISABLE_NOTIFICATIONS_YN,
  DeliverableEO.ATTRIBUTE_CATEGORY,
  DeliverableEO.ATTRIBUTE1,
  DeliverableEO.ATTRIBUTE2,
  DeliverableEO.ATTRIBUTE3,
  DeliverableEO.ATTRIBUTE4,
  DeliverableEO.ATTRIBUTE5,
  DeliverableEO.ATTRIBUTE6,
  DeliverableEO.ATTRIBUTE7,
  DeliverableEO.ATTRIBUTE8,
  DeliverableEO.ATTRIBUTE9,
  DeliverableEO.ATTRIBUTE10,
  DeliverableEO.ATTRIBUTE11,
  DeliverableEO.ATTRIBUTE12,
  DeliverableEO.ATTRIBUTE13,
  DeliverableEO.ATTRIBUTE14,
  DeliverableEO.ATTRIBUTE15,
  DeliverableEO.REPEATING_DURATION,
  (SELECT buyer_contact.full_name
  FROM per_all_people_f buyer_contact
  WHERE buyer_contact.person_id = DeliverableEO.internal_party_contact_id
  AND buyer_contact.effective_start_date = buyer_contact.start_date
  ) buyer_name,
  (SELECT requester_contact.full_name
  FROM per_all_people_f requester_contact
  WHERE DeliverableEO.requester_id = requester_contact.person_id
  AND requester_contact.effective_start_date = requester_contact.start_date
  ) requestor_name,
  (SELECT escalation_contact.full_name
  FROM per_all_people_f escalation_contact
  WHERE DeliverableEO.escalation_assignee = escalation_contact.person_id
  AND escalation_contact.effective_start_date = escalation_contact.start_date
  ) escalation_name,
  CASE DeliverableEO.BUSINESS_DOCUMENT_TYPE
  WHEN 'TEMPLATE' THEN (SELECT resp_party_tl.name
  FROM okc_resp_parties_tl resp_party_tl,
  okc_bus_doc_types_b obd,
  okc_terms_templates_all ota
  WHERE DeliverableEO.responsible_party = resp_party_tl.resp_party_code
  AND resp_party_tl.language = USERENV('LANG')
  AND obd.document_type_class = resp_party_tl.document_type_class
  AND obd.document_type = DeliverableEO.business_document_type
  AND ota.template_id = DeliverableEO.BUSINESS_DOCUMENT_ID
  AND ota.intent = resp_party_tl.intent)
  ELSE (SELECT resp_party_tl.name
  FROM okc_resp_parties_tl resp_party_tl,
  okc_bus_doc_types_b obd
  WHERE DeliverableEO.responsible_party = resp_party_tl.resp_party_code
  AND resp_party_tl.language = USERENV('LANG')
  AND obd.document_type_class = resp_party_tl.document_type_class
  AND obd.document_type = DeliverableEO.business_document_type
  AND obd.intent = resp_party_tl.intent)
  END responsible_party_meaning,
  DeliverableEO.deliverable_status current_status,
  DeliverableEO.business_document_line_id,
  DeliverableEO.external_party_site_id,
  OKC_DELIVERABLE_PROCESS_PVT.getDueDateMsgText(
  DeliverableEO.relative_st_date_event_id
  ,DeliverableEO.relative_end_date_event_id
  ,DeliverableEO.relative_st_date_duration
  ,DeliverableEO.relative_end_date_duration
  ,DeliverableEO.repeating_day_of_week
  ,DeliverableEO.repeating_day_of_month
  ,DeliverableEO.repeating_duration
  ,DeliverableEO.print_due_date_msg_name
  ,DeliverableEO.fixed_start_date
  ,DeliverableEO.fixed_end_date) due_date_msg_txt,
  DeliverableEO.amendment_operation ExistingAmendmentOperation,
  CASE DeliverableEO.fixed_due_date_yn
  WHEN 'Y' THEN 'dueDateFixed'
  ELSE 'dueDateData'
  END fixed_due_date_switcher,
  DeliverableEO.start_event_date,
  DeliverableEO.end_event_date,
  DeliverableEO.summary_amend_operation_code,
  (SELECT document_type_class
  FROM okc_bus_doc_types_b
  WHERE DeliverableEO.business_document_type = document_type) document_type_class,
  (SELECT party_name
  FROM hz_parties
  WHERE party_id = DeliverableEO.external_party_contact_id) supplier_name,
  start_evts_b.business_event_code start_event_code,
  (SELECT meaning
  FROM okc_bus_doc_events_tl
  WHERE bus_doc_event_id = DeliverableEO.relative_st_date_event_id
  AND language = USERENV('LANG')) start_event_name,
  start_evts_b.bus_doc_type start_event_doc_type,
  start_evts_b.before_after start_before_after,
  end_evts_b.business_event_code end_event_code,
  (SELECT meaning
  FROM okc_bus_doc_events_tl
  WHERE bus_doc_event_id = DeliverableEO.relative_end_date_event_id
  AND language = USERENV('LANG')) end_event_name,
  end_evts_b.bus_doc_type end_event_doc_type,
  end_evts_b.before_after end_before_after,
  start_evts_b.bus_doc_type||start_evts_b.business_event_code start_event_key,
  end_evts_b.bus_doc_type||end_evts_b.business_event_code end_event_key,
  DeliverableEO.external_party_role,
  okc_deliverable_process_pvt.get_party_name(external_party_id,external_party_role) external_party_name,
  (SELECT name
  FROM okc_deliverable_types_tl
  WHERE DeliverableEO.deliverable_type = deliverable_type_code
  AND language = USERENV('LANG')) DeliverableTypeMeaning,
  (SELECT Name
  FROM hr_all_organization_units
  WHERE DeliverableEO.internal_party_id = organization_id) InternalOrganization,
  DeliverableEO.PAY_HOLD_PRIOR_DUE_DATE_YN,
  DeliverableEO.PAY_HOLD_PRIOR_DUE_DATE_VALUE,
  DeliverableEO.PAY_HOLD_PRIOR_DUE_DATE_UOM,
  DeliverableEO.PAY_HOLD_OVERDUE_YN
FROM okc_deliverables DeliverableEO,
  okc_bus_doc_events_b start_evts_b,
  okc_bus_doc_events_b end_evts_b
WHERE DeliverableEO.relative_st_date_event_id = start_evts_b.bus_doc_event_id (+)
AND DeliverableEO.relative_end_date_event_id = end_evts_b.bus_doc_event_id (+)) QRSLT WHERE (DELIVERABLE_ID = :1)
at oracle.apps.fnd.framework.OAException.wrapperInvocationTargetException(OAException.java:996)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:211)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:153)
.
.
.
## Detail 0 ##
java.sql.SQLException: ORA-01427: single-row subquery returns more than one row

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)java:713)

What is working:
This error is specific to the ones created by a specific USER (the buyer name) who has 2 records in in per_all_people_f table. The query is returning 2 values for this buyer.

(SELECT buyer_contact.full_name
  FROM per_all_people_f buyer_contact
  WHERE buyer_contact.person_id = DeliverableEO.internal_party_contact_id
  AND buyer_contact.effective_start_date = buyer_contact.start_date
  )

out of the sql above is,
  Full Name EMP_NUM PERSON_ID eff_start_dt start_dt
*****, ***** A 5212 3006 8/19/1993 8/19/1993
*****, ***** A 3006 7/11/1992 7/11/1992
 
STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. (R) Purchasing Super User
2. Navigate to Buyers Work center and open any PO document
3. Go to Contract Terms page and click on Deliverables tab
4. Click the Deliverable name to get the error.


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms