Fusion: How To Exclude the BI Report’s Schedule Frequency From Holidays

BI Report scheduling frequency can be skipped based on the localized public holidays or weekends using by report triggers.

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