Calendar is Null in the Database for a Resource

Products

• Clarity PPM SaaS

Issue/Introduction

The Base Calendar field on the Calendar tab of a resource (Home - Resources) shows a selected value in the UI. However, the database shows this value as null. 

Environment

Release : Any

Component : CA PPM Resource Management

Resolution

When looking at the calendar values in the database, there are two scenarios that need to be considered. 

1. A user has been assigned a calendar, and no changes have been made to the calendar associated to their resource under the Calendar tab. 

In this case, the PRCALENDARID can be queried on the PRJ_RESOURCES table and simply tied back to the corresponding calendar having the same ID on the PRCALENDAR table to get the Calendar name.

Sample Query: 

SELECT
srm_resources.unique_name ResID,
prcalendar.prname CalName
FROM prcalendar
INNER JOIN prj_resources ON prj_resources.prcalendarid = prcalendar.prid
INNER JOIN srm_resources ON srm_resources.id = prj_resources.prid
WHERE prcalendar.prname is not null

Note: The query above without the last line will return null for resources who have made modifications to their calendar, this is expected.

2. A user has been assigned a calendar, and he or someone else has made changes to the calendar associated to their resource. For example, changed their shifts or work days.

In this case, the the PRCALENDAR table needs to be referenced twice. Once to get the ID of the base calendar that they started out with and a second time to get that base calendar's name. 

Sample Query: 

SELECT
srm_resources.unique_name ResID,
prcalendar.prname CalName
FROM prcalendar
INNER JOIN prcalendar prcalendar1 ON prcalendar1.prbasecalendarid = prcalendar.prid
INNER JOIN prj_resources ON prj_resources.prcalendarid = prcalendar1.prid
INNER JOIN srm_resources ON srm_resources.id = prj_resources.prid