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 MAY 23, 2023

Applies to:

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

Cause

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