E-QR: PS Query EFFDT Criteria Does Not Include All Key Fields
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.
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.
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,
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
6. The EFFDT logic generated adds only the first 2 key fileds that are placed above the EFFDT key field.
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