PS Query automatically does a Join to the Security Table but missing criteria for HRS_JO_SCRSUM_I
Last updated on FEBRUARY 15, 2018
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 = 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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms