E-PPR: Overlapping Start And End Dates In PSTZOFFSET When Generating Query Offsets For Timezones (Doc ID 1517325.1)

Last updated on JANUARY 02, 2015

Applies to:

PeopleSoft Enterprise PT PeopleTools - Version 8.52 and later
Information in this document applies to any platform.

Symptoms

1. PT > Utilities > International > Timezones > "Generate Query Offsets" button
2. Enter a date range where where part of the date range already has data in PSTZOFFSET
 
In 8.51 and earlier, this functionality (which calls the PeopleCode function GenTimeZoneOffsets) detects and avoid any overlap in the rows generated in PSTZOFFSET.
 
In 8.52 however, it does not.  This impacts applications that depends on data in this table, such as Time Admin in Time & Labor.

Here's a detailed example to illustrate the problem clearly.

Example:

The PSTZOFFSET may contain the following rows already:

...
EST       05-NOV-17 02.00.00.000000000 AM 10-MAR-18 11.00.00.000000000 PM 180                    EST          
EST       10-MAR-18 11.00.00.000000000 PM 11-MAR-18 02.00.00.000000000 AM 240                    EDT          
EST       11-MAR-18 02.00.00.000000000 AM 03-NOV-18 11.00.00.000000000 PM 180                    EDT          
EST       03-NOV-18 11.00.00.000000000 PM 04-NOV-18 02.00.00.000000000 AM 120                    EST          
EST       04-NOV-18 02.00.00.000000000 AM 31-DEC-18 12.00.00.000000000 AM 180                    EST  


Go to: PT > Utilities > International > Timezones > Generate Query Offsets
Use Start Date: Jan 1, 2018
Use End Date: Dec 31, 2019

In an 8.51.15 environment, after the offsets were generated, the result from the same SQL should show these rows:

...
EST       05-NOV-17 02.00.00.000000000 AM 01-JAN-18 12.00.00.000000000 AM 180                    EST          
EST       01-JAN-18 12.00.00.000000000 AM 10-MAR-18 11.00.00.000000000 PM 180                    EST          
EST       10-MAR-18 11.00.00.000000000 PM 11-MAR-18 02.00.00.000000000 AM 240                    EDT          
EST       11-MAR-18 02.00.00.000000000 AM 03-NOV-18 11.00.00.000000000 PM 180                    EDT          
EST       03-NOV-18 11.00.00.000000000 PM 04-NOV-18 02.00.00.000000000 AM 120                    EST          
EST       04-NOV-18 02.00.00.000000000 AM 09-MAR-19 11.00.00.000000000 PM 180                    EST          
EST       09-MAR-19 11.00.00.000000000 PM 10-MAR-19 02.00.00.000000000 AM 240                    EDT          
EST       10-MAR-19 02.00.00.000000000 AM 02-NOV-19 11.00.00.000000000 PM 180                    EDT          
EST       02-NOV-19 11.00.00.000000000 PM 03-NOV-19 02.00.00.000000000 AM 120                    EST          
EST       03-NOV-19 02.00.00.000000000 AM 31-DEC-19 12.00.00.000000000 AM 180                    EST    

Notice the original row with STARTDATETIME 05-NOV-17 and ENDDATETIME 10-MAR-18 no longer exists, and is now replaced with 05-NOV-17 to 01-JAN-18, and another row from 01-JAN-18 to 10-MAR-18.  This way there is no overlap.

In 8.52 however if the same query offset generation was done, the following rows appear:

EST       05-NOV-17 02.00.00.000000000 AM 10-MAR-18 11.00.00.000000000 PM 180                    EST                      
EST       01-JAN-18 12.00.00.000000000 AM 10-MAR-18 11.00.00.000000000 PM 180                    EST                      
EST       10-MAR-18 11.00.00.000000000 PM 11-MAR-18 02.00.00.000000000 AM 240                    EDT                      
EST       11-MAR-18 02.00.00.000000000 AM 03-NOV-18 11.00.00.000000000 PM 180                    EDT                      
EST       03-NOV-18 11.00.00.000000000 PM 04-NOV-18 02.00.00.000000000 AM 120                    EST                      
EST       04-NOV-18 02.00.00.000000000 AM 09-MAR-19 11.00.00.000000000 PM 180                    EST                      
EST       09-MAR-19 11.00.00.000000000 PM 10-MAR-19 02.00.00.000000000 AM 240                    EDT                      
EST       10-MAR-19 02.00.00.000000000 AM 02-NOV-19 11.00.00.000000000 PM 180                    EDT                      
EST       02-NOV-19 11.00.00.000000000 PM 03-NOV-19 02.00.00.000000000 AM 120                    EST                      
EST       03-NOV-19 02.00.00.000000000 AM 31-DEC-19 12.00.00.000000000 AM 180                    EST                      

Notice the original row with 05-NOV-17 to 10-MAR-18 exists, and so does a new row with 01-JAN-18 to 10-MAR-18.  There is overlap in the dates covered by the two rows.

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