Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

how to find holiday information of a user

SOLVED
Go to solution
Highlighted
riznad
Regular Collector

how to find holiday information of a user

Hello;

A resource can enter holiday information from

Open > administration > view my resource information

at calendar tab.

 

Which table stores this information.

I wnat to build a report to find personel in vacation.

Regards

6 REPLIES
Utkarsh_Mishra
Honored Contributor

Re: how to find holiday information of a user

select  * from KDRV_CALENDAR_EXCEPTIONS_V ............(check created_by)

 

Or refer tables:

 

KDRV_CALENDARS

KDRV_CALENDAR_EXCEPTIONS

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
Niraj Prabhu
Frequent Visitor

Re: how to find holiday information of a user

Riznad,

You can also consider looking at
KDRV_WORKING_DAYS
KRML_CALENDAR_DAYS
Niraj P.
d4y4n4
Frequent Visitor

Re: how to find holiday information of a user

You can use the below query to identify the Holidays for a User with his associated Regional Calendar.


A gentle note, when you are replying please reply to all .
--------------------------------------------------
select event_date
from knta_users ku,
kNTA_REGIONS kr,
kdrv_calendars kd,
KDRV_CALENDAR_EXCEPTIONS ce,
KNTA_LOOKUPS lk
where user_id = 30903 --- Specific User ID
and kr.calendar_id = kd.calendar_id
and ku.region_id = kr.region_id
and ce.reason_code = lk.lookup_code(+)
AND ce.calendar_id = kd.calendar_id
AND kd.calendar_type_code = 'REGIONAL_CALENDAR'
and to_char(event_date, 'D') not in (7, 1)
and to_char(event_date, 'yy') between to_char(sysdate, 'yy') and
(to_char(sysdate, 'yy') + 1)
order by 1;
--------------------------------------------------

Regards,
Dayana
riznad
Regular Collector

Re: how to find holiday information of a user

Hi ;

I entered vocation information for one user. But it is not displayed in your query.

Here screen:

 

Here query:

select ku.full_name, CE.event_date, CE.REASON_CODE,CE.DESCRIPTION
from PPMTFKB_USR.knta_users ku,
PPMTFKB_USR.kNTA_REGIONS kr,
PPMTFKB_USR.kdrv_calendars kd,
PPMTFKB_USR.KDRV_CALENDAR_EXCEPTIONS ce,
PPMTFKB_USR.KNTA_LOOKUPS lk
where ku.username = 'oc008331' --- Specific User ID
and kr.calendar_id = kd.calendar_id
and ku.region_id = kr.region_id
and ce.reason_code = lk.lookup_code(+)
AND ce.calendar_id = kd.calendar_id
AND kd.calendar_type_code = 'REGIONAL_CALENDAR'
and to_char(event_date, 'D') not in (7, 1)
and to_char(event_date, 'yy') between to_char(sysdate, 'yy') and
(to_char(sysdate, 'yy') + 1)
order by 1;

 

Here result:

Faruk Çevik 18/10/2013 HOLIDAY Kurban Bayramı
Faruk Çevik 16/10/2013 HOLIDAY Kurban Bayramı
Faruk Çevik 17/10/2013 HOLIDAY Kurban Bayramı
Faruk Çevik 15/10/2013 HOLIDAY Kurban Bayramı
Faruk Çevik 09/08/2013 HOLIDAY Ramazan Bayramı Tatili
Faruk Çevik 08/08/2013 HOLIDAY Ramazan Bayramı Tatili
Faruk Çevik 10/08/2013 HOLIDAY Ramazan Bayramı Tatili

 

riznad
Regular Collector

Re: how to find holiday information of a user

For the query :

SELECT ku.username,

  ku.full_name,

  kce.event_date as event_date,

  kce.reason_meaning as reason,

  kce.description as description

FROM PPMTFKB_USR.knta_users_v ku,

  PPMTFKB_USR.kdrv_calendar_exceptions_v kce

WHERE ku.calendar_id=kce.calendar_id

AND ku.enabled_flag ='Y'

and to_char(event_date, 'yy') between to_char(sysdate, 'yy') and

(to_char(sysdate, 'yy') + 1)

and ku.username='oc008331'

 

It retursn one row only for the day 18.04.2013

 

23.04.2013 and 01.05.2013 days ara not found (Look the calendar screen shot)

Is there any query which unions all the holidays.

riznad
Regular Collector
Solution

Re: how to find holiday information of a user

Hi;

Here a fully working query

Thanks Cantürk Topraklı

You can use it by changing username='oc008331'

 

select ku.username, ku.full_name, ce.event_date, ce.description

from knta_users ku,

  kdrv_calendar_exceptions_v ce,

kdrv_calendars kd

where 1=1

and  kd.calendar_name='Turkey Holidays'

AND ce.calendar_id = kd.calendar_id

and ku.username='oc008331'

and to_char(event_date, 'yy') between to_char(sysdate, 'yy') and (to_char(sysdate, 'yy') + 1)

UNION ALL

select ku.username, ku.full_name, ce.event_date, ce.description

from knta_users_v ku, kdrv_calendar_exceptions_v ce

WHERE 1=1

AND ku.enabled_flag ='Y'

and ku.username='oc008331'

AND ku.calendar_ıd=ce.calendar_id

and to_char(event_date, 'yy') between to_char(sysdate, 'yy') and (to_char(sysdate, 'yy') + 1)

//Add this to "OnDomLoad" event