NOT OPTIMIZED S_OPTY QUERY GENERATED DURING CONVERT LEAD TO OPPORTUNITY PROCESS (Doc ID 2004007.1)

Last updated on DECEMBER 01, 2016

Applies to:

Siebel Marketing - Version 8.1.1.11 [23030] and later
Information in this document applies to any platform.

Symptoms

On :  8.1.1.11 [23030] version,


ACTUAL BEHAVIOR  
---------------
OnCreateOpptyClicked method executed during conversion from Leads to Oppty generates a query based on Position only.

This is causing a performance issue during this process.

The query is executed from workflow 'Lead To Opty Conversion' step 'Create Opty'.


EXPECTED BEHAVIOR
-----------------------
Customer is expecting to have a way to get rid of this query.

The issue can be reproduced at will with the following steps:
1) Create a Lead from My Leads

2) Click in "Convert" button

3) This will create a new Oppty and navigate you to the Oppty view.

4) From the log, you can see that the following query is generated causing an undesired delay when having a large amount of oppty data:

.....

FROM
     SIEBEL.S_OPTY T1
        INNER JOIN SIEBEL.S_OPTY_POSTN T2 ON T1.PR_POSTN_ID = T2.POSITION_ID AND T1.ROW_ID = T2.OPTY_ID
        INNER JOIN SIEBEL.S_PARTY T3 ON T2.POSITION_ID = T3.ROW_ID
        LEFT OUTER JOIN SIEBEL.S_SYS_KEYMAP T4 ON T1.ROW_ID = T4.SIEBEL_SYS_KEY
        LEFT OUTER JOIN SIEBEL.S_OPTY T5 ON T1.PAR_OPTY_ID = T5.ROW_ID
        LEFT OUTER JOIN SIEBEL.S_ADDR_PER T6 ON T1.PR_OU_ADDR_ID = T6.ROW_ID
        LEFT OUTER JOIN SIEBEL.S_ORG_EXT T7 ON T1.PR_DEPT_OU_ID = T7.PAR_ROW_ID
        LEFT OUTER JOIN SIEBEL.S_ORG_EXT_ATX T8 ON T1.BU_ID = T8.PAR_ROW_ID
        LEFT OUTER JOIN SIEBEL.S_POSTN T9 ON T1.PR_POSTN_ID = T9.PAR_ROW_ID
        LEFT OUTER JOIN SIEBEL.S_PRI_LST T10 ON T7.CURR_PRI_LST_ID = T10.ROW_ID
        LEFT OUTER JOIN SIEBEL.S_USER T11 ON T9.PR_EMP_ID = T11.PAR_ROW_ID
        LEFT OUTER JOIN SIEBEL.S_SALES_METHOD T12 ON T1.SALES_METHOD_ID = T12.ROW_ID
        LEFT OUTER JOIN SIEBEL.S_STG T13 ON T1.CURR_STG_ID = T13.ROW_ID
        LEFT OUTER JOIN SIEBEL.S_OPTY_POSTN T14 ON T1.ROW_ID = T14.OPTY_ID AND T14.POSITION_ID = ?
        LEFT OUTER JOIN SIEBEL.S_OPTY_TNTX T15 ON T1.ROW_ID = T15.PAR_ROW_ID
        LEFT OUTER JOIN SIEBEL.S_OPTY_DSGN_REG T16 ON T1.ROW_ID = T16.PAR_ROW_ID
        LEFT OUTER JOIN SIEBEL.S_OPTY_SS T17 ON T1.ROW_ID = T17.PAR_ROW_ID
        LEFT OUTER JOIN SIEBEL.S_POSTN T18 ON T2.POSITION_ID = T18.PAR_ROW_ID
        LEFT OUTER JOIN SIEBEL.S_USER T19 ON T18.PR_EMP_ID = T19.PAR_ROW_ID
        LEFT OUTER JOIN SIEBEL.S_REVN T20 ON T1.SUM_REVN_ITEM_ID = T20.ROW_ID
        LEFT OUTER JOIN SIEBEL.S_OPTY_ORG T21 ON T1.PR_PRTNR_ID = T21.OU_ID AND T1.ROW_ID = T21.OPTY_ID
        LEFT OUTER JOIN SIEBEL.S_PARTY T22 ON T1.PR_PRTNR_ID = T22.ROW_ID
        LEFT OUTER JOIN SIEBEL.S_PARTY T23 ON T1.PR_CON_ID = T23.ROW_ID
        LEFT OUTER JOIN SIEBEL.S_CONTACT T24 ON T1.PR_CON_ID = T24.PAR_ROW_ID
        LEFT OUTER JOIN SIEBEL.S_SRC T25 ON T1.PR_SRC_ID = T25.ROW_ID
 WHERE
    (T1.SECURE_FLG = 'N' OR T14.OPTY_ID IS NOT NULL) AND
    (T1.TEMPLATE_FLG = 'N')

ObjMgrSqlLog Detail 4 0000000254520404:0 2014-10-30 11:03:14 Bind variable 1: 0-5220

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