My Oracle Support Banner

JBO-27122: SQL Error During Statement Preparation When Opening Task Report (Doc ID 2664616.1)

Last updated on JUNE 30, 2020

Applies to:

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

Symptoms

On : 12.2.6 version,

When attempting to open the Task Report or saving the signatures for tasks created  from service requests with item instances that have multiple counters, the following error is generated:

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, 
--CSF_PO_CONTACT_POINTS_V cpce, 
    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
  )

Steps to reproduce
---------

1. Create service request for an item instance that has more than 1 counter.
2. Create a task and assign it.
3. Open task in Field Service Technician Portal.
4. Click on Report or try to save signature.



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
References


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