FSTP: Task Report Has Oracle.apps.fnd.framework.OAException: Oracle.jbo.SQLStmtException ORA-1427 (Doc ID 2074202.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Field Service - Version 12.2.4 and later
Information in this document applies to any platform.

Symptoms

When trying to open the Task Report (from debrief page_ for a task that is
for an SR with an instance on it that has multiple counters, an error occurs:

ERROR
------
Exception Details.
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException:
JBO-27122: SQL error during statement preparation. Statement: select
hp.party_name,
hp.party_number,
NVL(csp_pick_utils.GET_CONTACT_NAME, ' ')CONTACT_NAME,
NVL(ci_all.customer_number, ' ')CUSTOMER_NUMBER,
ci_all.account_id,
hz.account_number,
hz_format_pub.format_address(hps.location_id,null,null,' '
,null,null,null,null)customer_address,
NVL(ci_all.incident_country, ' ')INCIDENT_COUNTRY,
ci_all.incident_number,
ci_all.incident_id,
NVL(ci_all.owner, ' ')OWNER,
NVL(ci_all.PURCHASE_ORDER_NUM, ' ')PURCHASE_ORDER_NUM,
citv.name SR_TYPE,
csp_pick_utils.get_object_name(jta.resource_type_code,jta.resource_id)
resource_name,
to_char(ci_all.incident_date,
to_char(fnd_profile.value('ICX_DATE_FORMAT_MASK'))||' '||'HH24:MI')
incident_date,
to_char(ci_all.creation_date,
to_char(fnd_profile.value('ICX_DATE_FORMAT_MASK'))||' '||'HH24:MI')
creation_date,
to_char(ci_all.last_update_date,
to_char(fnd_profile.value('ICX_DATE_FORMAT_MASK'))||' '||'HH24:MI')
last_update_date,
NVL(mskfv.concatenated_segments,' ') product_name,
NVL(cii.instance_number,' ')INSTANCE_NUMBER,
NVL(mskfv.description, ' ') product_description,
NVL(ci_all.contract_number, ' ')CONTRACT_NUMBER,
nvl(ci_all.external_reference, ' ') tag,
NVL(cii.serial_number, ' ')SERIAL_NUMBER,
nvl((SELECT counter_reading
FROM csi_counter_readings csr
WHERE csr.counter_id =
(SELECT counter_id
FROM csi_counter_associations
WHERE sysdate BETWEEN NVL(start_date_active, sysdate -1) AND
NVL(end_date_active, sysdate + 1)
AND source_object_id = :1
)
AND(value_timestamp =
(SELECT MAX(value_timestamp)
FROM csi_counter_readings b
WHERE csr.counter_id = b.counter_id
AND b.disabled_flag = 'N'
GROUP BY counter_id
))), -1)COUNTER,
ci_all.summary,
SignData.file_data,
apps.CSFW_SIGNATURE_PVT.encode_base64(SignData.file_data) base64Image,
tasks.task_name,
statuses.name status,
NVL(priorities.name,' ') priority,
jttv.name task_type,
NVL(jtf_task_utl.get_escalation_level(jta.TASK_ID), ' ') ESCALATION_LEVEL,
tasks.PLANNED_EFFORT_UOM conc_planned_effort,
tasks.task_id,
tasks.task_number,
jta.task_assignment_id,
to_char(sysdate, to_char(fnd_profile.value('ICX_DATE_FORMAT_MASK')))
current_date,
dbf_hdr.debrief_header_id,
NVL(hz_format_phone_v2pub.get_formatted_phone(cp_phone.phone_country_code,cp_phone.phone_area_code,cp_phone.phone_number, cp_phone.phone_extension,
cp_phone.phone_line_type), ' ')phone_number,
NVL(cp_email.email_address, ' ') email_address,
SignData.signer_name,
SignData.signed_date,
citv.description sr_type_desc,
(SELECT hzp.party_name
FROM hz_parties hzp,hz_party_sites hzs
WHERE hzs.party_id=hzp.party_id AND hzs.party_site_id=
ci_all.bill_to_site_id ) bill_to_party,
(select account_number from hz_cust_accounts hza where
hza.cust_account_id=ci_all.bill_to_account_id) bill_to_account,
(select okh.contract_number||' '||okh.contract_number_modifier
contract_number
from OKC_K_HEADERS_ALL_B okh
where okh.id = ci_all.contract_id) okh_contract_number
from hz_parties hp, cs_incidents_all_vl ci_all,
mtl_system_items_kfv mskfv,
csi_item_instances cii,
jtf_tasks_vl tasks,
jtf_task_statuses_vl statuses,
jtf_task_priorities_vl priorities,
hz_party_sites hps,
csf_debrief_headers dbf_hdr,
CS_INCIDENT_TYPES_vl citv,
JTF_TASK_ALL_ASSIGNMENTS jta,
jtf_task_types_vl jttv,
--fnd_documents_vl fnddoc_vl,
--fnd_document_categories_vl fnddoccat_vl,
--fnd_attached_documents fndatt,
--fnd_lobs lob,
hz_cust_accounts hz,
hz_contact_points cp_phone,
hz_contact_points cp_email,
(select lob.file_data file_data,
apps.CSFW_SIGNATURE_PVT.encode_base64(lob.file_data) base64Image,
NVL(substr(fnddoc_vl.description,1,instr(fnddoc_vl.description,' ',-1)-1), '
') signer_name,
to_char(to_date(substr(fnddoc_vl.description,instr(fnddoc_vl.description,'
',-1)+1, length(fnddoc_vl.description)), 'MM/DD/YY'),
to_char(fnd_profile.value('ICX_DATE_FORMAT_MASK'))) signed_date,
cdh.debrief_header_id
from fnd_documents_vl fnddoc_vl,
fnd_document_categories_vl fnddoccat_vl,
fnd_attached_documents fndatt,
fnd_lobs lob,
csf_debrief_headers cdh
where fndatt.entity_name = 'CSF_DEBRIEF_HEADERS'
AND fnddoccat_vl.name = 'SIGNATURE'
AND to_char(cdh.debrief_header_id) = fndatt.pk1_value
AND fndatt.document_id = fnddoc_vl.document_id
AND fnddoc_vl.media_id = lob.file_id
AND fnddoccat_vl.category_id = fnddoc_vl.category_id) SignData
where hp.party_id = ci_all.customer_id
and ci_all.account_id = hz.cust_account_id (+)
and jta.task_id = tasks.task_id
and ci_all.incident_id = :2
and mskfv.inventory_item_id(+) = ci_all.inventory_item_id
and mskfv.organization_id(+)= fnd_profile.value('CS_INV_VALIDATION_ORG')
and cii.instance_id(+) = ci_all.customer_product_id
and tasks.task_id = :3
and tasks.source_object_id = ci_all.incident_id
and statuses.task_status_id = tasks.task_status_id
and priorities.task_priority_id = tasks.task_priority_id
and jttv.task_type_id = tasks.task_type_id
and hps.party_site_id = tasks.address_id
and dbf_hdr.task_assignment_id = :4
and jta.task_assignment_id = dbf_hdr.task_assignment_id
and citv.incident_type_id = ci_all.incident_type_id
and SignData.debrief_header_id(+) = dbf_hdr.debrief_header_id
--AND (CASE WHEN (fndatt.pk1_value IS NULL) AND (fndatt.entity_name IS NULL)
THEN 1
-- WHEN (fndatt.pk1_value IS NOT NULL) AND (fndatt.entity_name =
'CSF_DEBRIEF_HEADERS') THEN 1
-- ELSE 0
-- END = 1)
--AND (CASE WHEN (fndatt.pk1_value IS NULL) AND (fnddoccat_vl.name IS NULL)
THEN 1
-- WHEN (fndatt.pk1_value IS NOT NULL) AND (fnddoccat_vl.name =
'SIGNATURE') THEN 1
-- ELSE 0
-- END = 1)
--AND fndatt.entity_name = 'CSF_DEBRIEF_HEADERS'
--AND fnddoccat_vl.name = 'SIGNATURE'
--AND to_char(dbf_hdr.debrief_header_id) = fndatt.pk1_value(+)
--AND fndatt.document_id = fnddoc_vl.document_id(+)
--AND fnddoc_vl.media_id = lob.file_id(+)
--AND fnddoccat_vl.category_id(+) = fnddoc_vl.category_id
and hp.party_id = cp_phone.owner_table_id(+)
and hp.party_id = cp_email.owner_table_id(+)
and cp_phone.owner_table_name(+) = 'HZ_PARTIES'
and cp_email.owner_table_name(+) = 'HZ_PARTIES'
and cp_phone.contact_point_type(+) = 'PHONE'
and cp_email.contact_point_type(+) = 'EMAIL'
and cp_phone.primary_flag(+) = 'Y'
and cp_email.primary_flag(+) = 'Y'
 at
oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)
 at
oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:886)
 at
oracle.apps.fnd.framework.OAException.wrapperInvocationTargetException(OAException.java:1009)
..
## Detail 0 ##
java.sql.SQLException: ORA-01427: single-row subquery returns more than one
row


when the instance has only 1 counter associated to it, the task report opens fine without any error and shows details as expected.

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