Calendar holidays

[Jaya Santosh Kumar Patchipulusu]

Hello All - I'm trying to get Holidays list from a Calendar, so help me how to proceed in this.

Here Holidays mean - The day we make it as "Non-work day".

PRCALENDAR has Calendar name, it's Base calendar , Slice_status, hours_per_day columns.

RPT_CALENDAR has Period_type, Start and Finish dates, FTE.

Any other areas/tables do I need to focus on ?

Please help.

[Liz Williamson]

Hi Jaya,

You could use the table rpt_calendar which will show the work/non-work days based on the FTE field.
This table uses the standard calendar and updates with either the Load DW job or running Update Report Tables job with the Calendar Option.

Another way is to use the admin user (or someone who would not edit their calendar) and then query the slice table for availability.
The daily slice is DAILYRESOURCEAVAILCURVE (id = 1) and you would link that to the user through prj_object_id

Your daily slices would have to cover the period you need and then the query would look something like
select * from prj_blb_slices where slice_request_id = 1 and prj_object_id = 1 (where prj_object_id = 1 is the admin user internal ID)

------------------------------
Liz Williamson
Sr. Support Engineer - Clarity
Broadcom
------------------------------

[Jaya Santosh Kumar Patchipulusu]

Hi Liz  -  I have tried this way, Am I still missing anything,

SELECT

(SELECT PRNAME FROM PRCALENDAR WHERE PRID=PRJ.PRCALENDARID) Cal
,SLICES.SLICE_DATE S_DATE
,DECODE(SLICES.SLICE,0,'Holiday',SLICES.SLICE) ALLOCATION
FROM
PRJ_BLB_SLICES SLICES
JOIN SRM_RESOURCES RES ON RES.ID=SLICES.PRJ_OBJECT_ID
JOIN PRJ_RESOURCES PRJ ON PRJ.PRID=RES.ID
JOIN (SELECT
START_DATE SD
,END_DATE-1 ED
FROM
BIZ_COM_PERIODS
WHERE
PERIOD_TYPE='ANNUALLY'
AND TRUNC(SYSDATE) BETWEEN START_DATE AND END_DATE-1
) BIZ on 1=1
WHERE
SLICES.SLICE_REQUEST_ID=1
AND SLICES.SLICE=0
AND (SLICES.SLICE_DATE BETWEEN BIZ.SD and BIZ.ED)
AND RES.ID=1
ORDER BY
Cal,SLICES.SLICE_DATE;

[Liz Williamson]

Hi Jaya,

I ran your query against my db and it returned correct results.
Also compare that to my simple query of 

select * from prj_blb_slices where slice_request_id = 1 and prj_object_id = 1 and slice = 0

Which is simple version of yours and the results matched.

Does that work for you?

------------------------------
Liz Williamson
Sr. Support Engineer - Clarity
Broadcom
------------------------------

[Jaya Santosh Kumar Patchipulusu]

Hi Liz - My application has 3 calendars, but why only Standard calendar records are appearing, other 2 also having Holidays in them.

Again other challenge is we need to remove the Saturday and Sundays - these are weekends and holidays by default.

[Liz Williamson]

Hi Jaya,

Basically that is the calendar the admin user has, that is why it is pulling that.

To get rid of the weekends you would have add the day of the week to your date format and exclude Saturday and Sunday or something like that.

To get the other base calendars you would have to do the same thing for a user in each other calendar.

It would be important here to find a resource's calendar which has not been modified.

You can use the prcalendar table and prj_resources  tables to find resources who have the different calendar.
If the resource is not in prcalendar table  they have not modified their calendar.

I recently created a query like this that shows the resource information and if they have the base calendar or if they modified it.
Look at the CASE statement to see if they modified their base:

SELECT r.UNIQUE_NAME,
r.FIRST_NAME,
r.LAST_NAME,
r.EMAIL,
c.PRNAME,
c.PRRESOURCEID,
c.PRBASECALENDARID,
c1.PRNAME AS PRNAME1,
CASE WHEN
c.prname is null then c1.prname
else c.prname end as calendar,
c1.PRID
FROM prj_resources p
INNER JOIN SRM_RESOURCES r
ON p.PRID = r.ID
INNER JOIN PRCALENDAR c
ON p.PRCALENDARID = c.PRID
LEFT JOIN PRCALENDAR c1
ON c.PRBASECALENDARID = c1.PRID


------------------------------
Liz Williamson
Sr. Support Engineer - Clarity
Broadcom
------------------------------

[Jaya Santosh Kumar Patchipulusu]

Hello Liz -  I just roughly made the query removing the Saturday and Sunday. - 

But running this query - The Standard Calendar name is not being shown, rest are okay.

Please advise.

SELECT * FROM (
Select DISTINCT
C.CAL
,C.CALID
,C.PYEAR
,C.S_DATE
,TO_CHAR(C.S_DATE,'DAY') WDAY
,TO_CHAR(C.S_DATE,'DY') WDAY1
,C.ALLOCATION
from
(
SELECT

(SELECT PRNAME FROM PRCALENDAR WHERE PRID=PRJ.PRCALENDARID) Cal
,(SELECT PRID FROM PRCALENDAR WHERE PRID=PRJ.PRCALENDARID) CalID
,BIZ.PYEAR
,SLICES.SLICE_DATE S_DATE
,DECODE(SLICES.SLICE,0,'Holiday',SLICES.SLICE) ALLOCATION
FROM
PRJ_BLB_SLICES SLICES
JOIN SRM_RESOURCES RES ON RES.ID=SLICES.PRJ_OBJECT_ID
JOIN PRJ_RESOURCES PRJ ON PRJ.PRID=RES.ID
JOIN (SELECT
START_DATE SD
,END_DATE-1 ED
,P_YEAR PYEAR
FROM
BIZ_COM_PERIODS
WHERE
PERIOD_TYPE='ANNUALLY'
AND TRUNC(SYSDATE) BETWEEN START_DATE AND END_DATE-1
) BIZ on 1=1
WHERE
SLICES.SLICE_REQUEST_ID=1
AND SLICES.SLICE=0
AND (SLICES.SLICE_DATE BETWEEN BIZ.SD and BIZ.ED)

ORDER BY
Cal,SLICES.SLICE_DATE
) C

where 1=1
order by c.cal
) Cal where cal.WDAY1 not in ('SAT','SUN')

[Liz Williamson]

Jaya,
Is that field blank?  If so, may be because that field is blank in the join.


------------------------------
Liz Williamson
Sr. Support Engineer - Clarity
Broadcom
------------------------------