SQL Query to fetch Public Holidays configured as Calendar Event

Public Holidays configured in application are utilized in deriving correct Work Schedules defined for the organization differentiating working days to non-working days, and helps calculating correct absence duration for leaves registered.

This query will come handy when you need to pull up the list of Public Holidays configured as Calendar Event in HCM application.

Columns fetching are, Country code, Holiday Name, Start Date, End Date -

SELECT 
	D.pk1_start_value as country_code, 
	B.name, 
	to_char(C.start_date_time,'DD-MM-YYYY') as start_date, to_char(C.end_date_time,'DD-MM-YYYY') as end_date
FROM
   	per_cal_event_coverage A,
  	per_calendar_events_tl B,
   	per_calendar_events C,
   	per_geo_tree_node D
WHERE 1=1
   	and A.calendar_event_id = B.calendar_event_id
   	and B.calendar_event_id = C.calendar_event_id
   	and C.CATEGORY = 'PH'   --- 'Public Holiday'
   	and D.tree_node_id = A.tree_node_id
   	and D.tree_code='KWGT' --- 'replace it with correct tree code'
	and B.language='US'

Above snippet is base query which can further be modified to derive required results.