My Oracle Support Banner

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

Last updated on AUGUST 28, 2018

Applies to:

Oracle Field Service - Version 12.1.3 to 12.2.4 [Release 12.1 to 12.2]
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

To view full details, 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 a vibrant support community of peers and Oracle experts.