Create Organization From ASN, When Website And Phone Number Is Given Serious Performance Issue Seen (Doc ID 1636228.1)

Last updated on SEPTEMBER 02, 2016

Applies to:

Oracle Sales - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

Creating a new Organization is very slow when both phone and web are provided, and can result in Internal Server error.
If only the phone or only the web is entered, then the save is fast.

Steps:
 Sales User (resp )
 Customer (Short cut )
 Create
  Enter Details,   including phone number and website.
Click on apply.

Save can take 5 minutes.
Or in worst case result in Internal Server error.

Setups:

Profiles options:

  HZ: Duplicate Allowed N
  HZ: Enable Duplicate Prevention at Party Creation Set to Person and Organization ( at application level )
  HZ: Match Rule for Contact Duplicate Prevention - SAMPLE: SEARCH
  HZ: Match Rule for Organization Duplicate Prevention - SAMPLE: SEARCH
  HZ: Match Rule for Person Duplicate Prevention - SAMPLE: SEARCH

 

Trace shows the long running select:


SELECT CONTACT_POINT_ID, STAGE.CONTACT_POINT_TYPE, PARTY_ID, PARTY_SITE_ID,
 ORG_CONTACT_ID , TX1, TX5, TX6, TX8
FROM
HZ_STAGED_CONTACT_POINTS STAGE WHERE CONTAINS( CONCAT_COL, :B3 )>0 AND
 EXISTS ( SELECT 1 FROM HZ_STAGED_PARTIES P WHERE P.PARTY_ID =
 STAGE.PARTY_ID AND( (:B2 ='Y' ) OR (:B2 = 'I' AND NVL(P.STATUS, 'A') IN
 ('A', 'I')) OR (:B2 = 'N' AND NVL(P.STATUS, 'A') IN ('A')) ) AND ((:B4 IS
 NULL OR :B4 ||' ' = P.TX36))) AND( (:B2 ='Y' ) OR (:B2 = 'I' AND
 NVL(STAGE.STATUS_FLAG, 'A') IN ('A', 'I')) OR (:B2 = 'N' AND
 NVL(STAGE.STATUS_FLAG, 'A') = 'A') ) AND (:B1 IS NULL OR STAGE.PARTY_ID <>  :B1 )


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