Queries on Timezone related outputs in ERP Cloud

Abhilash K - Deloitte USI

Our users are in PST Timezone and has raised concerns about report outputs being in UTC Timezone. We are trying to update report code and wanted to clarify few queries before we proceed to update all the report code.

• Parameters have an option whether to be input in UTC using the ignore timezone flag. So if the “ignore time zone” option is checked then no date conversions are needed is what we assume. Is this correct?
• Similarly internal sysdate and current_date are in UTC and when comparing with other table date fields which are all in UTC, so we believe we don’t need a date conversion in such cases.
• All date conversions in where clause impact report performance.
• Will all these TZ conversion modified report outputs be in sync with system/standard report outputs? Does standard reports display based on timezone preferences?
• How do we take care of Dates on OTBI/FRS reports for timezone related updates?

Thanks

Abhilash

Abhilash K - Deloitte USI

We decided to modify the query with conversion of the timestamp columns so we reduce the load of the post processor. thanks all.

Renuka Nuguru-Oracle

Hi Abhishak,

Go thorough below given KM docs and let us know this helps.

>>Oracle Fusion OTBI: Why are the Dates Off By one Day for some users and not for others (Doc ID 2097586.1)

>>Set Default Time Zone For All Users in Fusion Applications (Doc ID 1630493.1)

>>Can you change the time zone and test the issue

1. Navigator> Tools> Reporting and Analytics

2. Click 'Browse Catalog' icon

3. On the next screen, click 'My Account' under the user name.

4. Under the 'Preferences' (tab) change the time zone 

5. Click 'Ok' to save and re-run the SQL from Issue SQL

If you still facing the issue...

Change the time zone in the Application UI >> Set Preferences >> Region >>Time Zone and test the SQL

Thanks,

Renuka

Abhilash K - Deloitte USI

We did both with no success @Renuka Nuguru-Oracle . Any other suggestions?

Renuka Nuguru-Oracle

Hi Abhilash,

1. Can you provide screenshot of Fusion Application preferences and also User preferences screenshot.
2. Do you see issue with all the date columns or columns with Timestamp? As some dates have only date without timestamp.
3. Can you reconfirm, column you are referring to date column or date with timestamp. Can you also provide the screenshot of the date column properties.

Previous update by us should resolve the issue if the date column is with timestamp.

Thanks,

Renuka

Abhilash K - Deloitte USI

Time Zone Issue Screenshots.docx

Please review attachment

Abhilash K - Deloitte USI

@Renuka Nuguru-Oracle

Application preferences.

Renuka Nuguru-Oracle

I have reviewed the screenshot of the dashboard report creation date data and the database data.

Take 15786 creation date is 2023-12-15T18:06....... Which is 2023-12-15 6 pm UTC time

On Dashboard you see PST timing which is correct datetimestamp.

Try to check the timestamp using timezone convertor

https://www.worldtimebuddy.com/utc-to-pst-converter

If you have further questions, please open the Service Request with OTBI team

Thanks,

Renuka

Abhilash K - Deloitte USI

Hi @Renuka Nuguru-Oracle ,

Thanks for the update. Our users are requesting that both UI and report output show the same time and date.

We are looking for options which would enable us to report data in PST time which is by now 7 hours behind UTC.

Users are seeing the date changing to next day when they create transactions after 5pm PST. For example, if they create a transaction on 5pm PST on 03/14 the report output shows as 03/15 12am on UTC causing confusion to users why there is a different time and date in future. We mentioned that the database stores it in UTC and users want that to be updated to PST. Hope this clarifies.

Renuka Nuguru-Oracle

@Abhilash K - Deloitte USI If you want to show only date without time zone in the report, use cast(datecolumn as date) and test the data.

Otherwise best way to debug is through SR

Abhilash K - Deloitte USI

@Renuka Nuguru-Oracle - Usually, if we use a transformation on where clause, it calls for a performance test. Is there anything simple to accomplish this for PST clients? It appears odd that we do not have an option to workaround this without a code change.