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 JULY 14, 2020
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.
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).
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 = xxxxx 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 = xxxxx ))
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.
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