E-QR: PS Query EFFDT Criteria Does Not Include All Key Fields (Doc ID 1532781.1)

Last updated on MARCH 30, 2016

Applies to:

PeopleSoft Enterprise PT PeopleTools - Version 8.54 to 8.54 [Release 8.4]
PeopleSoft Enterprise PT PeopleTools - Version 8.50 to 8.53 [Release 8.4]
Information in this document applies to any platform.

Symptoms

When attempting to create a PS Query using an Effective Dated record, the effective date logic criteria which is added automatically, it does not include all the key fields. As an observation the key fields that fall before the EFFDT field in any record definition get automatically included in the Effective date logic in PS Query.

Steps
-------
The issue can be reproduced at will with the following steps:
1. Login to PIA

2. Navigate to Query > Query Manager

3. Create a new Query using an effective dated record (e.g CF_ATTRIB_TBL)

4. For this particular record there are 6 key fields (SETID, CHARTFIELD_VALUE, EFFDT, FIELDNAME, CF_ATTRIBUTE, CF_ATTRIB_VALUE, )

5. The Query SQL generated looks as below:

SELECT A.SETID, A.FIELDNAME, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.CF_ATTRIBUTE,
A.CF_ATTRIB_VALUE, A.CHARTFIELD_VALUE
  FROM PS_CF_ATTRIB_TBL A
  WHERE ( A.EFFDT =
  (SELECT MAX(A_ED.EFFDT) FROM PS_CF_ATTRIB_TBL A_ED
  WHERE A.SETID = A_ED.SETID
  AND A.CHARTFIELD_VALUE = A_ED.CHARTFIELD_VALUE
  AND A_ED.EFFDT

6. The EFFDT logic generated adds only the first 2 key fileds that are placed above the EFFDT key field.


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