Pre-Requisites
Public holidays need to be captured in the Fusion Cloud applications’ Calendar Events.
Step 1: Calendar Events
Navigation: Setup and Maintenance > Manage Calendar Events
Step 2: SQL for Calendar Events
SELECT NAME,
TO_CHAR(START_DATE_TIME, 'MM/DD/YYYY') START_DATE,
TO_CHAR(END_DATE_TIME, 'MM/DD/YYYY') END_DATE,
SHORT_CODE
FROM PER_CALENDAR_EVENTS_VL
WHERE CATEGORY = 'PH'
ORDER BY START_DATE
Step 3: Add Event Triggers in Data Model
Schedule Triggers
A schedule trigger fires when a report job is scheduled to run. Schedule triggers are of type SQL Query. When a report job is scheduled to run, the schedule trigger executes the SQL statement defined for the trigger. If data is returned, then the report job is submitted. If data is not returned from the trigger SQL query, the report job is skipped.
SQL Query
SELECT 'TRUE' FROM DUAL
WHERE NOT EXISTS
(
-- Public Holidays
SELECT 'HOLIDAY' HOLIDAY
FROM PER_CALENDAR_EVENTS_VL
WHERE 1=1
AND CATEGORY = 'PH'
AND TO_CHAR(:P_RUN_DATE, 'MM/DD/YYYY') BETWEEN TO_CHAR(START_DATE_TIME, 'MM/DD/YYYY') AND TO_CHAR(END_DATE_TIME, 'MM/DD/YYYY')
UNION
-- Weekends
SELECT 'HOLIDAY'
FROM DUAL
WHERE TO_CHAR(:P_RUN_DATE, 'D') IN (1,7)
)
The above query will exclude the weekends and public holidays.
Documents Reference
1. Fusion Global HR: How To Setup Calendar Events By Locations (Doc ID 1917689.1)
2. Adding Triggers: Guide
