My Oracle Support Banner

Task Report Is Not Opening From Field Service Technician Portal (Doc ID 2621511.1)

Last updated on DECEMBER 18, 2019

Applies to:

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

Symptoms

On : 12.2.6 version, Field Service Engineer Portal

When attempting to open the Task Report from Field Service Technician Portal (FSTP), the following error occurs:

ERROR
-----------------------
JBO-27122:SQL error during statement preparation.
Statement: SELECT DISTINCT * FROM (SELECT hp.party_name, hp.party_number, NVL(CsfPoSrDetailsEO.customer_number, ' ')CUSTOMER_NUMBER, CsfPoSrDetailsEO.account_id, hca.account_number, NVL(CsfPoSrDetailsEO.incident_country, ' ')INCIDENT_COUNTRY, CsfPoSrDetailsEO.INCIDENT_NUMBER, CsfPoSrDetailsEO.INCIDENT_ID, NVL(CsfPoSrDetailsEO.owner, ' ')OWNER, NVL(CsfPoSrDetailsEO.CUSTOMER_PO_NUMBER, ' ')PURCHASE_ORDER_NUM, citv.name SR_TYPE, TO_CHAR(hz_timezone_pub.Convert_DateTime(fnd_profile.value('SERVER_TIMEZONE_ID'),fnd_profile.value('CLIENT_TIMEZONE_ID'),CsfPoSrDetailsEO.INCIDENT_OCCURRED_DATE), TO_CHAR(fnd_profile.value('ICX_DATE_FORMAT_MASK')) ||' ' ||'HH24:MI') sr_incident_date, TO_CHAR(hz_timezone_pub.Convert_DateTime(fnd_profile.value('SERVER_TIMEZONE_ID'),fnd_profile.value('CLIENT_TIMEZONE_ID'),CsfPoSrDetailsEO.creation_date), TO_CHAR(fnd_profile.value('ICX_DATE_FORMAT_MASK')) ||' ' ||'HH24:MI') sr_creation_date, TO_CHAR(hz_timezone_pub.Convert_DateTime(fnd_profile.value('SERVER_TIMEZONE_ID'),fnd_profile.value('CLIENT_TIMEZONE_ID'),CsfPoSrDetailsEO.last_update_date), TO_CHAR(fnd_profile.value('ICX_DATE_FORMAT_MASK')) ||' ' ||'HH24:MI') sr_last_update_date, NVL(mskfv.concatenated_segments,'') product_name, NVL(cii.instance_number,' ')INSTANCE_NUMBER, NVL(mskfv.description, ' ') product_description, NVL(CsfPoSrDetailsEO.contract_number, ' ')CONTRACT_NUMBER, NVL(NVL(CsfPoSrDetailsEO.external_reference,cii.external_reference),'') tag, NVL(NVL(CsfPoSrDetailsEO.current_serial_number,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 ))), '') COUNTER, CsfPoSrDetailsEO.SUMMARY, NVL(CsfPoSrDetailsEO.INCIDENT_SEVERITY_ID,'') INCIDENT_SEVERITY_ID, NVL(CsfPoSrDetailsEO.CUSTOMER_PRODUCT_ID,'') CUSTOMER_PRODUCT_ID, CsfPoSrDetailsEO.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER, NVL(CsfPoSrDetailsEO.PROBLEM_CODE,'') PROBLEM_CODE, NVL(CsfPoSrDetailsEO.RESOLUTION_CODE,'') RESOLUTION_CODE, NVL(CsfPoSrDetailsEO.RESOLUTION_SUMMARY,'') RESOLUTION_SUMMARY, cis.NAME severity, (SELECT NVL(contact_comm_pref,'') FROM csf_po_contact_points_v cpc WHERE cpc.contact_name = ( SELECT contact_name FROM csf_po_contact_points_v a WHERE primary_flag = 'Y' AND CsfPoSrDetailsEO.incident_id = :3 AND a.incident_id = CsfPoSrDetailsEO.incident_id ) AND CsfPoSrDetailsEO.incident_id = cpc.incident_id AND CsfPoSrDetailsEO.incident_id = :4 AND cpc.contact_point_type = 'PHONE' AND rownum = 1) contact_phone, (SELECT NVL(contact_comm_pref,'') FROM csf_po_contact_points_v cpc WHERE cpc.contact_name = ( SELECT contact_name FROM csf_po_contact_points_v a WHERE primary_flag = 'Y' AND CsfPoSrDetailsEO.incident_id = :5 AND a.incident_id = CsfPoSrDetailsEO.incident_id ) AND CsfPoSrDetailsEO.incident_id = cpc.incident_id AND CsfPoSrDetailsEO.incident_id = :6 AND cpc.contact_point_type = 'EMAIL' AND rownum = 1) contact_email, ( SELECT nvl(cpc.contact_name,'') FROM csf_po_contact_points_v cpc WHERE cpc.incident_id = CsfPoSrDetailsEO.incident_id AND CsfPoSrDetailsEO.incident_id =:7 AND cpc.primary_flag = 'Y') contact_name, --NVL(cpc.contact_comm_pref,'') contact_phone, --NVL(cpce.contact_comm_pref,'') contact_email, --NVL(cpc.contact_name,'') contact_name, NVL(CsfPoSrDetailsEO.INCIDENT_TYPE_ID,'') INCIDENT_TYPE_ID, CsfPoSrDetailsEO.customer_id customer_id, CsfPoSrDetailsEO.INCIDENT_CONTEXT INCIDENT_CONTEXT, cisv.name incidnet_status, NVL(cisv.disallow_request_update,'') disallow_request_update, NVL(cisv.disallow_action_update,'') disallow_action_update, NVL(CsfPoSrDetailsEO.inventory_item_id,'') inventory_item_id, NVL(CsfPoSrDetailsEO.INCIDENT_NUMBER,'') Document_number, csp_pick_utils.get_object_type_meaning('SR') Document_type, NVL(CsfPoSrDetailsEO.product_revision,'') revision, NVL(mck.CONCATENATED_SEGMENTS,'') item_category, CsfPoSrDetailsEO.PROBLEM_CODE || DECODE(NVL(CsfPoSrDetailsEO.PROBLEM_CODE,'X'),'X','',': ') || cl.meaning conc_problem_Code, CsfPoSrDetailsEO.resolution_CODE || DECODE(NVL(CsfPoSrDetailsEO.resolution_CODE,'X'),'X','',': ') || cl1.meaning conc_resolution_Code, 'HZ_PARTY_SITE' Instance_location_type, NVL(cii.operational_status_code,'') operational_status_code, CsfPoSrDetailsEO.contract_id, CSfPoSrDetailsEO.CONTRACT_SERVICE_ID, citv.BUSINESS_PROCESS_ID, mtl.concatenated_segments Contract_service, mtl.description contract_service_description, NVL(CsfPoSrDetailsEO.customer_po_number,'') customer_po_number, DECODE(CsfPoSrDetailsEO.INCIDENT_LOCATION_TYPE, 'HZ_LOCATION', hz_format_pub.format_address(CsfPoSrDetailsEO.INCIDENT_LOCATION_ID), 'HZ_PARTY_SITE', (SELECT hz_format_pub.format_address(hps.location_id) FROM hz_party_sites hps WHERE hps.party_site_id = CsfPoSrDetailsEO.INCIDENT_LOCATION_ID ), '') sr_address, DECODE(CsfPoSrDetailsEO.INCIDENT_LOCATION_TYPE, 'HZ_LOCATION', '', 'HZ_PARTY_SITE', (SELECT hps.party_site_name FROM hz_party_sites hps WHERE hps.party_site_id = CsfPoSrDetailsEO.INCIDENT_LOCATION_ID ), '') sr_site_name, DECODE(CsfPoSrDetailsEO.INCIDENT_LOCATION_TYPE, 'HZ_LOCATION', (SELECT country FROM hz_locations WHERE location_id = CsfPoSrDetailsEO.INCIDENT_LOCATION_ID ), 'HZ_PARTY_SITE', (SELECT loc.country FROM hz_party_sites hps, hz_locations loc WHERE hps.party_site_id = CsfPoSrDetailsEO.INCIDENT_LOCATION_ID AND hps.location_id = loc.location_id ), '') sr_country FROM CS_INCIDENTS_ALL_VL CsfPoSrDetailsEO, mtl_system_items_kfv mskfv, --CSF_PO_CONTACT_POINTS_V cpc, --to fix Bug #27581539 --CSF_PO_CONTACT_POINTS_V cpce, --to fix Bug #27581539 hz_parties hp, CS_INCIDENT_SEVERITIES_VL cis, CSI_ITEM_INSTANCES cii, cs_incident_statuses_vl cisv, CS_INCIDENT_TYPES_vl citv, HZ_CUST_ACCOUNTS hca, MTL_CATEGORIES_KFV mck, cs_lookups cl, cs_lookups cl1, Okc_k_items ki, mtl_system_items_kfv mtl WHERE mskfv.inventory_item_id(+) = CsfPoSrDetailsEO.inventory_item_id --AND cpc.incident_id(+) = CsfPoSrDetailsEO.incident_id --AND cpc.CONTACT_POINT_TYPE(+) = 'PHONE' --AND cpce.incident_id(+) = CsfPoSrDetailsEO.incident_id --AND cpce.CONTACT_POINT_TYPE(+) = 'EMAIL' AND hp.party_id = CsfPoSrDetailsEO.customer_id AND mskfv.organization_id(+) = NVL(CsfPoSrDetailsEO.inv_organization_id,fnd_profile.value('CS_INV_VALIDATION_ORG')) AND cis.INCIDENT_SEVERITY_ID = CsfPoSrDetailsEO.incident_severity_id AND cis.INCIDENT_SUBTYPE = 'INC' AND cii.instance_id(+) = CsfPoSrDetailsEO.customer_product_id AND cisv.incident_status_id = CsfPoSrDetailsEO.incident_status_id AND citv.incident_type_id = CsfPoSrDetailsEO.incident_type_id AND hca.cust_account_id(+) = CsfPoSrDetailsEO.account_id AND mck.CATEGORY_ID(+) = CsfPoSrDetailsEO.CATEGORY_ID AND cl.LOOKUP_TYPE(+) = 'REQUEST_PROBLEM_CODE' AND cl.lookup_code(+) = CsfPoSrDetailsEO.problem_code AND cl1.LOOKUP_TYPE(+) = 'REQUEST_RESOLUTION_CODE' AND cl1.lookup_code(+) = CsfPoSrDetailsEO.resolution_code AND NVL(CsfPoSrDetailsEO.incident_location_type,'HZ_PARTY_SITE') = 'HZ_PARTY_SITE' AND ki.cle_id(+) = CsfPoSrDetailsEO.contract_service_id AND mtl.inventory_item_id(+) = ki.object1_id1 AND mtl.organization_id(+) = ki.object1_id2 AND CsfPoSrDetailsEO.INCIDENT_ID = :2 --AND cpc.contact_party_id = cpce.contact_party_id --to fix Bug #27581539 )


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Log in with FSTP responsibility.
2. Open task.
3. Click Task Report button.

Changes

 

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!


In this Document
Symptoms
Changes
Cause
Solution


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