My Oracle Support Banner

PS Query automatically does a Join to the Security Table but missing criteria for HRS_JO_SCRSUM_I (Doc ID 2193440.1)

Last updated on FEBRUARY 03, 2019

Applies to:

PeopleSoft Enterprise HCM Talent Acquisition Manager - Version 9.2 to 9.2 [Release 9]
PeopleSoft Enterprise PT PeopleTools - Version 8.55 to 8.55 [Release 8.4]
Information in this document applies to any platform.

Symptoms

When doing a PSQuery on record PS_HRS_JO_SCR_SM_I it automatically does a join to the security table PS_HRS_JO_SEC_VW but is missing a criteria statement(not joining to its key).

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
- Go to Reporting Tools > Query > Query Manager
- Click Create New Query
- Add Record HRS_JO_SCR_SM_I
- Check All fields
- Go to the Criteria tab and click the Add Criteria button
- Add criteria A.HRS_JOB_OPENING_ID = 500402 and click OK
- Go to the Run tab and you see that you get many duplicate rows which is the issue.
- If you click on the View SQL tab you see this..
SELECT A.HRS_JO_SCR_SUMM_ID, A.HRS_JOB_OPENING_ID, A.HRS_JO_RQMT_SEQ, A.JOBCODE, A.HRS_SCR_TTL_PT, A.HRS_SCR_TRANSMUTE, A.HRS_SCR_DEPENDENT, A.GVT_TIE_BREAKER, A.GVT_VP_SCORE_LMT, A.GVT_AUG_SCORE_LMT, A.GVT_RANK_COMP_CAT1, A.GVT_RANK_COMP_CAT2, A.GVT_RANK_COMP_CAT3, A.GVT_RANK_COMP_CAT4, A.GVT_CONSID_VP_COMP, A.GVT_RANK_NCMP_CAT1, A.GVT_RANK_NCMP_CAT2, A.GVT_RANK_NCMP_CAT3, A.GVT_RANK_NCMP_CAT4, A.GVT_CONSID_VP_NCMP, A.HRS_G_JO_WQL_SCR, A.HRS_G_JO_BQL_SCR, TO_CHAR(CAST((A.HRS_ROW_ADD_DTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), A.HRS_ROW_ADD_OPRID, TO_CHAR(CAST((A.HRS_ROW_UPD_DTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), A.HRS_ROW_UPD_OPRID
 FROM PS_HRS_JO_SCR_SM_I A, PS_HRS_JO_SEC_VW A1
 WHERE ( A1.OPRID = 'PS'
  AND ( A.HRS_JOB_OPENING_ID = 500402 ))

However the security view used is PS_HRS_JO_SEC_VW which is defined like this.  Therefore without PSQuery adding AND A.HRS_JOB_OPENING_ID = A1.HRS_JOB_OPENING_ID we will see duplicate rows because the two table are not matching on the job opening ID.


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
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.