Record Views Using 'Oracle' Definitions After 9.2.13 - Causing PeopleSoft Performance Degradation And Query To Time-Out - PS_EMPLMT_SRCH_QRY And PS_PERALL_SEC_QRY (Doc ID 2204109.1)

Last updated on JANUARY 26, 2024

Applies to:

Symptoms

Problem 1:  Before HCM 9.2.13, some customers reported the 'default' definitions for PS_EMPLMT_SRCH_QRY and PS_PERALL_SEC_QRY led to PeopleSoft Queries timing-out. 

Problem 2:  Some customers who applied the 'Oracle' specific view definitions delivered in HCM 9.2.13 for Problem 1, have reported significant performance degradation.

Problem 3:  Some customers trying to create the PSBUILD Create View SQL script using the 'default' view definition, have reported that only the 'Oracle' definition is used in the PSBUILD scripts, regardless of which definition is selected in App Designer.

Customers may find that with the 'default' view definition selected, the 'Oracle' specific view is in the PSBUILD Create View script.
Even though the Record Definition is pointing to the 'default' definition, the Create View SQL script built by PSBUILD is actually the 'Oracle' one, not the 'default'.
- When the View is opened in SQL Editor, the definition is pointing to 'default'.  
- However when the PSBUILD Create Views runs, it builds the 'Oracle' SQL statement.

Customers need the ability to have the definition selected included in the PSBUILD script so that after running the script, this is the actual definition at the database level.

Steps;
1.   In App Designer, Open the Record, Click the Record Type Tab, Click to open SQL Editor > open the definition.
2.   Select either the 'default' or the 'Oracle' definition.  Save the View definition.   After saving and reopening, the selected definition should appear.  
      Presently the 'default' is always shown and changes are never saved.
3.   If the definition contains the expression '%sql', this is PeopleCode and is not executable directly in a Create View script.
      With the definition open > right-click > Resolve META SQL > to see the actual SQL statement.
      Save any changes, Insert Record into Project, Save the Project, and close the record definition.
4.   Run the Build > Project> Create Views > create the SQL script which conforms to the selected definition, whether it is 'default' or 'Oracle'.    
      Presently only the 'Oracle' definition SQL is built regardless of the view definition which is selected.
5.   Note that the %sql of the 'default' definition may be decoded by right-clicking on the definition > Resolve Meta SQL > code appears in the Meta SQL Tab of the App Designer output window.
      The expression '%sql' is PeopleCode which is not directly executable at the database level. 

Steps:
Starting with one of the two Queries PS_PERALL_SEC_QRY
1.  Connect to App Designer as PS
2.  Open the Record = PS_PERALL_SEC_QRY [also PS_EMPLMT_SRCH_QRY ]
3.  Definition is 'default'.
4.  Change to 'Oracle'.  Cannot save the change
5.  Go to PSBUILD > Create Views > PSBUILD.sql is created using the 'Oracle'
definition

--UNCOMMENT SET DEFINE OFF TO RUN THIS SCRIPT IN SQLPLUS/SQL DEVELOPER

--SET DEFINE OFF

--UNCOMMENT SET SQLBLANKLINES ON TO RUN THIS SCRIPT IN SQLPLUS

--SET SQLBLANKLINES ON

DROP VIEW PS_PERALL_SEC_QRY
/
CREATE VIEW PS_PERALL_SEC_QRY (OPRID, EMPLID) AS SELECT DISTINCT
OPR.OPRID , SEC.EMPLID FROM PS_SJT_PERSON SEC , PSOPRDEFN OPR WHERE
SEC.APPT_TYPE <> '1' AND SEC.FUTURE_FLG <> 'Y' AND EXISTS( SELECT 'X'
FROM PS_SJT_CLASS_ALL CLS , PS_SJT_OPR_CLS SOC WHERE
CLS.SCRTY_TYPE_CD = SEC.SCRTY_TYPE_CD AND CLS.SCRTY_KEY1 =
SEC.SCRTY_KEY1 AND CLS.SCRTY_KEY2 = SEC.SCRTY_KEY2 AND CLS.SCRTY_KEY3
= SEC.SCRTY_KEY3 AND SOC.OPRID = OPR.OPRID AND SOC.CLASSID =
CLS.CLASSID AND CLS.SCRTY_SET_CD IN ('PPLJOB', 'PPLPOI') AND
(CLS.TREE = 'N' OR (CLS.TREE = 'Y' AND SOC.CLASSID = OPR.ROWSECCLASS
AND SOC.SEC_RSC_FLG IN ('1', '3'))))

Changes

Cause

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