Project and Portfolio Management Practitioners Forum
cancel

How can I extract the calendar of a resource?

SOLVED
Go to solution
Highlighted
Luigi Frignani
Contributor.

How can I extract the calendar of a resource?

Hi,

I'm working on PPM Center 7.1 SP 7.
Does exist a method to extract the calendar of a resource (holidays, work days, other) such as a report or other?

Otherwise could you give me a query to get this informations into a file?

Best regards,

Luigi Frignani
4 REPLIES
Mahen M
Acclaimed Contributor.

Re: How can I extract the calendar of a resource?

Select User_id, Full_name, Calendar_id from KNTA_Users

Above query will give you the resource ID, Name and resource Calendar ID.

Select U.User_id, U.Full_name, C.Calendar_id, C.calendar_name, C.Parent_Calendar_id from KNTA_Users U,KDRV_CALENDARS C where U.Calendar_id=C.Calendar_id

Regards,
Mahendran M
Erik Cole_1
Outstanding Contributor.
Solution

Re: How can I extract the calendar of a resource?

Hi Luigi,

The following returns non-holiday calendar events (vacation, out of office, etc) from the resource calendars. You will probably want modify it to change the locale to your own. Hope this helps.

Select u.USER_ID,u.FULL_NAME,ce.EVENT_DATE,ce.REASON_CODE,ce.DESCRIPTION
From KDRV_CALENDAR_EXCEPTIONS ce
Join KDRV_CALENDARS c on c.CALENDAR_ID = ce.CALENDAR_ID
Join KNTA_USERS u on u.CALENDAR_ID = c.CALENDAR_ID
Where 1=1
And c.CALENDAR_TYPE_CODE = 'RESOURCE_CALENDAR'
And u.END_DATE Is Null
And ce.EVENT_DATE Not In (select EVENT_DATE from KDRV_CALENDAR_EXCEPTIONS
Where REASON_CODE = 'HOLIDAY' and DESCRIPTION like 'United States%')
Luigi Frignani
Contributor.

Re: How can I extract the calendar of a resource?

Thanks for your support!

The query revisited is:

select *
from (
Select u.USER_ID,u.FULL_NAME,ce.EVENT_DATE,ce.REASON_CODE,ce.DESCRIPTION
From KDRV_CALENDAR_EXCEPTIONS ce
Join KDRV_CALENDARS c on c.CALENDAR_ID = ce.CALENDAR_ID
Join KNTA_USERS u on u.CALENDAR_ID = c.CALENDAR_ID
Where c.CALENDAR_TYPE_CODE = 'RESOURCE_CALENDAR'
union
select u.USER_ID,u.FULL_NAME, ce.EVENT_DATE,ce.REASON_CODE,ce.DESCRIPTION
from KNTA_USERS u, KDRV_CALENDAR_EXCEPTIONS ce
where ce.REASON_CODE = 'HOLIDAY'
and ce.DESCRIPTION like '%%')
where event_date between to_date(, 'DD/MM/YYYY') and to_date(, 'DD/MM/YYYY')
and user_id =
order by event_date

With this query I get holidays and vacations days.

Thanks a lot!
Luigi Frignani
Contributor.

Re: How can I extract the calendar of a resource?

Write the solution in thread.