E-PG: UK Date Format DDMMYYYY Is Not Ordered Correctly In Filter And Grid Row (Doc ID 2257207.1)

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.

Symptoms

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.

Steps
--------
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:
EXT_ORG_TBL

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.

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