Highlighted
mike_se Contributor.
Contributor.
162 views

PPM Support Tip: Create a new Regional Calendar instead of updating the schema directly

A new KCS article was published: KM00591553

 

 

PPM Support Tip: When changing the Start and Finish Dates for Task, reverts back to original date without an error

 


Client had attempted to manually update a calendar in the database.

This lead to an unexpected behavior on the front end where could not change the dates of Tasks.



There are 13 out-of-the-box/seeded Regional Calendars.

If customer wants to define their own Regional Calendar, they need to create a new Regional Calendar (cannot just change the seeded Regional Calendar manually and set Parent Calendar to null).

Example of how to get a schema back to a working state:

1. backup table kdrv_calendars, kdrv_calendar_exceptions, kdrv_working_days, knta_regions_nls
create table kdrv_calendars_bk
as select * from kdrv_calendars;

create table kdrv_calendar_exceptions_bk
as select * from kdrv_calendar_exceptions;

create table kdrv_working_days_bk
as select * from kdrv_working_days;

create table knta_regions_nls_bk
as select * from knta_regions_nls;

2. change calendar 'UAE' back to 'US'
update kdrv_calendars
set calendar_name = 'US',
parent_calendar_id = 2
where calendar_id = 20000;

3. remove holidays of calendar 'UAE'
delete from kdrv_calendar_exceptions
where calendar_id = 20000;

4. change working days of calendar 'US' back to seed data
--update working days which were changed to non-working days
update kdrv_working_days
set working_day_flag = 'Y'
where calendar_id = 20000
and working_day_flag = 'N'
and (working_day_date in(
select event_date
from kdrv_calendar_exceptions
where calendar_id = (select parent_calendar_id from kdrv_calendars where calendar_id = 20000)
and working_day_flag = 'Y')
or (not exists (
select 0 from kdrv_calendar_exceptions ce
where calendar_id = (select parent_calendar_id from kdrv_calendars where calendar_id = 20000)
and ce.event_date = WORKING_DAY_DATE
)
and to_char(WORKING_DAY_DATE, 'D') not in('1', '7')));

--update non-working days which were changed to working days
update kdrv_working_days
set working_day_flag = 'N'
where calendar_id = 20000
and working_day_flag = 'Y'
and working_day_date in(
select event_date
from kdrv_calendar_exceptions
where calendar_id = (select parent_calendar_id from kdrv_calendars where calendar_id = 20000)
and working_day_flag = 'N');

5. create a regional calendar with name 'UAE'
Open->Administration->Regions->Create Regional Calendar, choose a Base holiday as its parent calendar
in the 'Modify Regional Calendar:' page, set holidays or remove the holidays which is belong to parent holiday calendar but not belong to calendar 'UAE'
You can also set this calendar as default calendar if you need.
get the calendar_id used in next steps

6. change parent calendar of all resource calendars whose parent calendar were 'UAE'
update kdrv_calendars
set parent_calendar_id = <new_calendar_id>
where parent_calendar_id = 20000;
<new_calendar_id> is the calendar id which step5 created

7. update regions' calendar
update knta_regions_nls
set calendar_id = <new_calendar_id>
where calendar_id = 20000;
<new_calendar_id> is the calendar id which Step 5 created

NOTE: Always test any direct updates to a database in a STAGE environment prior to making any changes in PROD. Make sure to backup the data as well.

“HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.”
Labels (1)
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.