I believe some of you may have thought of doing this and ended up re-creating the application. (For those who are not aware of the issue, Planning doesn’t allow you to change the Calendar items after the application is created. You cannot change the Start Year, Start Month, Weekly Distribution,….)
I’ve written a post which helps in changing the Start Year, this one is following the similar lanes.
I had to create an application where the start month was Jan (when we were developing) and later we had to change it to Jun. So only option left was to re-create the entire application and at that point of time I decided to play around with the Planning relational tables and see whether I could achieve it without re-creating 🙂
A word of caution before attempting this.
Take a back up of the schema, data, etc before trying this.
There are 3 tables which holds the information related to the months.
- HSP_CALENDAR – This table contains the information that you fill in when you create a Planning application.
- FY_MONTH is the column which holds the information of start month.
- 1 for January, 2 for February and so on
- HSP_OBJECT – This table contains the object name and their position and parent in the hierarchy.
- HSP_TIME_PERIOD – This table contains the information of all time related members (even DTS)
1st Objective
The first objective is to find out the object id’s of the months and their position in the hierarchy.
So I ran a SQL query to find out the starting object_id
select * from hsp_object where object_name like ‘%Jan’;
So considering quarters into account I ran a query to get all object_id’s of all members (period)
select * from hsp_object where object_id BETWEEN 50017 and 50032;
In my case Jun should have position 1 and May position 12 and also the parent_id’s should be changed.
2nd Objective
Second objective is to update the positions and parent ids. (to get the correct hierarchy, Jun, Jul, Aug will be under Q1 and so on)
update hsp_object set position=8 where object_id=50018;
Run the update statement for all the positions from 1 to 12 (level 0/ month level)
Now update parent_ids accordingly (Jun, Jul, Aug will be under Q1 and so on)
update hsp_object set parent_id=50017 where object_name=’Jun’ or object_name=’Jul’ or object_name=’Aug’;
Run the update statement to update all parent ids.
This updates will make sure that Period hierarchy is updated with the new start month.
3rd Objective
Third objective is to get the scenario start month and end month assignment corrected.
If this table is not updated, even though you are not opening a scenario for Jan, users will be able to enter values since Jan is set as 1 in this Table.
update hsp_time_period set period=8 where tp_id=50018;
Run the update statement for all the positions from 1 to 12 (level 0/ month level).
That’s it. Commit the changes that you made to the system and re-start Planning.
HTH
