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 OCTOBER 29, 2016

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

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