When Querying POSITION_DATA, Duplicate Rows Appear If Position Uses A Dept with A Future Dated Row (Doc ID 2133862.1)

Last updated on MAY 04, 2016

Applies to:

PeopleSoft Enterprise HCM Human Resources - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

When querying POSITION_DATA, duplicate rows appear if position uses a Dept. with a Future Dated Row. When running a query inside Query Manager on POSITION_DATA, it automatically adds the security view called PS_POSTN_SRCH_QRY. If the specific position is in a department that has a future dated row, then the query will result in duplicate rows.

Sample SQL:

SELECT A.POSITION_NBR
FROM PS_POSITION_DATA A, PS_POSTN_SRCH_QRY A1
WHERE ( A.POSITION_NBR = A1.POSITION_NBR
AND A1.OPRID = '<UserName when running the query>'
AND ( A.POSITION_NBR = '<position that is in a department that has a future dated row>' ))

Only one row must be retrieved.

STEPS
--------
The issue can be reproduced at will with the following steps:
1) Go into DEMO, and add a future dated row to a department. Eg : Dept : 10000. Add a future dated row for Jan 16 2016
2) Find a position that that uses that Department. Eg : POS: 19000013
3) Do a query on POSITION_DATA where POSITION_NBR = 19000013. Include the fields Position Number, and Effective date.

SQL:
SELECT A.POSITION_NBR, TO_CHAR(A.EFFDT,'YYYY-MM-DD')
  FROM PS_POSITION_DATA A, PS_POSTN_SRCH_QRY A1
  WHERE ( A.POSITION_NBR = A1.POSITION_NBR
  AND A1.OPRID = 'VP1'
  AND ( A.POSITION_NBR = '19000013' ))

4) It will return duplicate rows.
5) If you remove the future dated rows in department table, then the duplicate rows disappear.

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