BIP – Query to extract Calendar Event details

Calendar events are used in HCM to capture public holidays available to employees based on their location or department.

Below SQL queries can be used to extract the list of configured calendar events from backend table:

List of all calendar events configured:

SELECT pcetl.name
      ,pce.short_code
      ,pcetl.description
      ,pce.category
      ,pce.coverage_type 
      ,pce.start_date_time
      ,pce.end_date_time 
      ,pce.tree_code
      ,pce.tree_structure_code 
 FROM per_calendar_events pce,
      per_calendar_events_tl pcetl
WHERE pce.calendar_event_id=pcetl.calendar_event_id
  AND pcetl.LANGUAGE=userenv('LANG')

List of calendar events by location:

SELECT hla.location_id
      ,hla.location_name             
      ,hla.internal_location_code 
      ,pcet.name
	  ,pce.short_code
      ,pcet.description
      ,pce.category
      ,pce.coverage_type	  
	  ,pce.start_date_time
	  ,pce.end_date_time
      ,pce.tree_code
      ,pce.tree_structure_code 	  
 FROM hr_locations_all hla
     ,per_calendar_events pce
	 ,per_calendar_events_tl pcet
	 ,per_cal_event_coverage pcec
	 ,per_geo_tree_node_rf pgtnr
WHERE hla.geo_hierarchy_node_value = pgtnr.ancestor_pk1_value
  AND pgtnr.tree_structure_code = 'PER_GEO_TREE_STRUCTURE'
  AND pgtnr.distance in (0)
  AND pgtnr.ancestor_tree_node_id = pcec.tree_node_id
  AND pcec.calendar_event_id = pcet.calendar_event_id
  AND pcet.calendar_event_id = pce.calendar_event_id
  AND pcet.language = userenv('lang')
  AND pcec.coverage_flag in ('I')
  AND trunc(sysdate) between hla.effective_start_date and hla.effective_end_date