21418147 Field Service Create SR Form Performance Issue In R12.2.4 (Doc ID 2059293.1)

Last updated on MARCH 23, 2016

Applies to:

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

Symptoms

On : 12.2.4 version, Service Request Form

ACTUAL BEHAVIOR
---------------
Field Service Create SR form performance issue in R12.2.4

After upgrading to Release 12.2.4 from 12.0.6, entering the Serial Number & then tabbing out results in a performance issue. The time time for the form to return the results in 15-20 minutes. The query taking a long time is:


SELECT *
  FROM ( SELECT *
  FROM (SELECT l.address1,
  l.address1
  || DECODE (l.address2, NULL, NULL, ';' || l.address2)
  || DECODE (l.address3, NULL, NULL, ';' || l.address3)
  || DECODE (l.address4, NULL, NULL, ';' || l.address4)
  address,
  l.address2,
  l.address3,
  l.address4,
  l.address_lines_phonetic,
  l.position,
  1 dummy,
  l.city,
  l.state,
  l.country,
  l.province,
  l.postal_code,
  l.county,
  l.location_id location_id,
  'HZ_LOCATION' location_type_code,
  NULL party_id,
  NULL party_name,
  NULL party_type,
  NULL party_number,
  NULL email_address,
  NULL p_city,
  NULL p_state,
  NULL p_country,
  NULL p_province,
  NULL p_postal_code,
  NULL p_address,
  NULL incident_party,
  NULL incident_party_id,
  NULL site_name,
  NULL site_number,
  NULL addressee,
  NULL site_phone
  FROM HZ_LOCATIONS l
  WHERE l.location_id NOT IN (SELECT location_id
  FROM HZ_PARTY_SITES
  WHERE status = 'A')
  AND l.country = NVL ('US', l.country)
  AND NVL (l.created_by_module, 'XX') <> 'SR_ONETIME'
  AND 'Y' = 'Y'
  UNION
  SELECT l.address1,
  l.address1
  || DECODE (l.address2, NULL, NULL, ';' || l.address2)
  || DECODE (l.address3, NULL, NULL, ';' || l.address3)
  || DECODE (l.address4, NULL, NULL, ';' || l.address4)
  address,
  l.address2,
  l.address3,
  l.address4,
  l.address_lines_phonetic,
  l.position,
  1 dummy,
  l.city,
  l.state,
  l.country,
  l.province,
  l.postal_code,
  l.county,
  DECODE (s.party_site_id,
  NULL, l.location_id,
  s.party_site_id)
  location_id,
  DECODE (s.party_site_id,
  NULL, 'HZ_LOCATION',
  'HZ_PARTY_SITE')
  location_type_code,
  p.party_id,
  p.party_name,
  p.party_type,
  p.party_number,
  p.email_address,
  p.city p_city,
  p.state p_state,
  p.country p_country,
  p.province p_province,
  p.postal_code p_postal_code,
  p.address1
  || DECODE (p.address2, NULL, NULL, ';' || p.address2)
  || DECODE (p.address3, NULL, NULL, ';' || p.address3)
  || DECODE (p.address4, NULL, NULL, ';' || p.address4)
  p_address,
  p.party_name incident_party,
  p.party_id incident_party_id,
  s.party_site_name site_name,
  s.party_site_number site_number,
  s.addressee addressee,
  cs_std.return_primary_phone (s.party_site_id)
  site_phone
  FROM HZ_LOCATIONS l, HZ_PARTY_SITES s, HZ_PARTIES p
  WHERE s.status = 'A'
  AND s.location_id = l.location_id
  AND p.status(+) = 'A'
  AND p.party_id(+) = s.party_id
  AND l.country = NVL ('US', l.country)
  AND p.party_type IN
  ('PERSON', 'ORGANIZATION', 'PARTY_RELATIONSHIP')
  AND NVL (l.created_by_module, 'XX') <> 'SR_ONETIME'
  AND 'Y' = 'Y'
  ORDER BY party_name, 2)
  ORDER BY party_name, 2)
  WHERE location_id = 68701 AND location_type_code =
  'HZ_PARTY_SITE'


EXPECTED BEHAVIOR
-----------------------
The Serial Number should be returned quickly if not immediately.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
(R) Service
(N) Service Requests > Find/Create Service Requests
Enter SR Header information
Enter the Serial Number

Then tab out
It takes 15-20 minutes to populate

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot work with the SR form effectively

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