Modifying Timezones

Looks like there is no one form which can be used to change the Timezone drop down lists in various application. For eg. I had a request to change the timezone drop down that shows up in iSupport.

On asking Oracle Support, they came up with the following SQL statements

1. Connect to SQLPlus using APPS username

2. Verify that following query returns exactly one row with value for the combination of timezone_code and enabled_flag = N

i) SELECT timezone_code, enabled_flag
FROM fnd_timezones_b
WHERE timezone_code LIKE ‘%Africa%’ ;

ii) SELECT timezone_code, name, enabled_flag, active_timezone_code
FROM fnd_timezones_vl
WHERE timezone_code LIKE ‘%Africa%’ ;

=> in both the queries there has to be only one row returned, and find that the value for enabled_flag = N

You will notice a set of records as follows

Africa/Ouagadougou N GMT
Africa/Porto-Novo N Africa/Tunis
Africa/Sao_Tome N GMT
Africa/Timbuktu N GMT
Africa/Tripoli Y Africa/Tripoli
Africa/Tunis Y Africa/Tunis
Africa/Windhoek N
Africa/Abidjan N GMT
Africa/Accra N GMT
Africa/Addis_Ababa N Asia/Riyadh
Africa/Algiers N Africa/Tunis
Africa/Asmera N Asia/Riyadh
Africa/Bamako N GMT
Africa/Bangui N Africa/Tunis
Africa/Banjul N GMT
Africa/Bissau N GMT
Africa/Blantyre N Africa/Tripoli
Africa/Brazzaville N Africa/Tunis
Africa/Bujumbura N Africa/Tripoli

3. Now enable the Africa timezones using following
UPDATE fnd_timezones_b
SET enabled_flag = ‘Y’
WHERE timezone_code LIKE ‘%Africa%’ ;
=> 1 Row updated.

You may also update timezone belonging to only one African city, eg. Johannesburg, as follows

UPDATE fnd_timezones_b
SET enabled_flag = ‘Y’
WHERE timezone_code LIKE ‘%Africa%Johannes%’
1 row(s) updated

COMMIT ;
=> Commit successful .No errors .

4. Verify that following query returns exactly one row with value for the combination of timezone_code and enabled_flag = Y

i) SELECT timezone_code, enabled_flag
FROM fnd_timezones_b
WHERE timezone_code LIKE ‘%Africa%Johannes%’ ;

ii) SELECT timezone_code, name, enabled_flag, active_timezone_code
FROM fnd_timezones_vl
WHERE timezone_code LIKE ‘%Africa%Johannes%’ ;

=> in both the queries there has to be only one row returned, and find that the value for enabled_flag = Y

For this change to take effect in any of the Self-Service Application (eg. iSupport or iLearning), DBA will need to bounce the Apache server.