Introduction:
This SQL query is used to fetch the Holidays list for Employees like Employee number, Holiday name, Start date, End date etc…
Cause of the issue:
Business wants a report that contains Holidays list for Employees from Fusion HCM module. So, business wants to create a custom report to pull the required data based on Last updated date.
How do we solve:
Create a report in BI Publisher using below SQL query to extract the Holiday Calendar details.
SQL Query:
SELECT DISTINCT
pce.calendar_event_id,
nvl(in_pcec.override_name, pce.name) Holiday_name,
to_char(pce.start_date_time, ‘DD-MM-YYYY’) START_DATE_TIME,
to_char(pce.creation_date, ‘DD-MM-YYYY’) creation_date,
pce.created_by,
papf.person_number
FROM
per_calendar_events_vl pce,
per_geo_tree_node_rf pgtn_rf,
per_cal_event_coverage in_pcec,
per_location_details_f pldf,
per_all_assignments_f plaf,
per_all_people_f papf
WHERE
pce.coverage_type = ‘G’
AND pce.tree_structure_code = pgtn_rf.tree_structure_code
AND pce.tree_code = pgtn_rf.tree_code
AND pce.tree_version_id = pgtn_rf.tree_version_id
AND pce.calendar_event_id = in_pcec.calendar_event_id
AND ( ( pgtn_rf.tree_node_id = in_pcec.tree_node_id
AND pgtn_rf.is_leaf = ‘Y’ )
OR pgtn_rf.ancestor_tree_node_id = in_pcec.tree_node_id )
AND pgtn_rf.pk1_value = pldf.geo_hierarchy_node_value
AND trunc(sysdate) BETWEEN trunc(pldf.effective_start_date) AND trunc(pldf.effective_end_date)
AND plaf.location_id = pldf.location_id
AND plaf.person_id = papf.person_id
AND trunc(sysdate) BETWEEN trunc(nvl(papf.effective_start_date, sysdate)) AND trunc(nvl(papf.effective_end_date, sysdate))
AND (pce.creation_date >= to_date(:p_last_run_dt, ‘YYYY-MM-DD”T”HH24:MI:SS’))
and plaf.assignment_status_type in(‘ACTIVE’)
AND trunc(sysdate) BETWEEN trunc(nvl(pldf.effective_start_date(+), sysdate)) AND trunc(nvl(pldf.effective_end_date, sysdate))
AND upper(plaf.primary_flag(+)) = ( ‘Y’ )
AND plaf.assignment_type IN (
‘E’,
‘C’
)
AND trunc(sysdate) BETWEEN trunc(nvl(plaf.effective_start_date(+), sysdate)) AND trunc(nvl(plaf.effective_end_date(+), sysdate))
AND plaf.effective_latest_change = ‘Y’
AND category IN(
SELECT
pse.exception_code
FROM
per_schedule_exceptions pse, per_schedule_assignments psa1, zmm_sr_schedules_tl zss
WHERE
1 = 1
AND pse.schedule_id = zss.schedule_id
AND psa1.resource_id = plaf.assignment_id
AND psa1.schedule_id = zss.schedule_id
AND pse.exception_code IS NOT NULL
AND sysdate BETWEEN psa1.start_date AND psa1.end_date
)
AND EXISTS (
SELECT
1
FROM
per_periods_of_service ppos
WHERE
ppos.person_id = plaf.person_id
AND ppos.actual_termination_date is null
)
