E-PG: UK Date Format DDMMYYYY Is Not Ordered Correctly In Filter And Grid Row
Last updated on JUNE 12, 2017
Applies to:PeopleSoft Enterprise PT PeopleTools - Version 8.55 and later
Information in this document applies to any platform.
When attempting to run Pivot Grids with UK date format DDMMYYYY, the existing date values are not ordered properly in Filter and Grid Row.
The UK dates are sorted properly in Graph.
The issue can be reproduced at will with the following steps:
1. Login to PIA
2. Navigation: Main Menu – My Preference – Regional Settings
3. Set Date Format to DDMMYYYY in your account preference
Navigate to Reporting Tools > Query > Query Manager
3. Create Query UOS_EXT_ORG_TBL with the record:
4. View Query SQL:
SELECT A.EXT_ORG_ID, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.EXT_ORG_TYPE, A.DESCR
FROM PS_EXT_ORG_TBL A
WHERE ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_EXT_ORG_TBL A_ED
WHERE A.EXT_ORG_ID = A_ED.EXT_ORG_ID
AND A_ED.EFFDT <= SYSDATE))
5. Navigate to Reporting Tools > Pivot Grid > Pivot Grid Wizard
6. Create Pivot Grid UOS_EXT_ORG_TBL using the new Query created
7. In the filter, you can see that the dates are sorted by lexicographical logic rather than comparison of numerical values of the dates.
8. In the Grid Row, you can see the order of the date has the same problem as seen in the Filter.
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