In this article we will look into the schedule trigger concept in BI Publisher and the use cases of it.
Table of Contents
What is Schedule Trigger?
Schedule trigger is a type of Event Trigger that we can create in the Data Model to conditionally execute an occurrence of job. It is a SQL code which runs before the schedule is triggered to see if the schedule needs to run or get skipped based on the results in the trigger SQL Query. There are other types of Event Triggers like Before Data and After Data using Packages but as we cannot create custom packages in fusion, we cannot use these types. Only some seeded reports might use Before Data and After Data type triggers.
What are the Use cases?
There are multiple use cases for creating schedule triggers. Some of them are listed below:
- If there is a need to run the report only on month start and month end dates
- If there is a need to run the report only if the current payroll has been processed
- If there is a need to run the report only if the Data Model fetches at least 1 row of data (Birthday Reminders, Anniversary Reminders, New Hire Notifications, Termination Notifications, etc.)
- If there is a need to generate Escalation emails if the tasks aren’t completed on time or reminders for tasks.
In all of the above cases if we don’t use schedule trigger then the output will return incorrect results. Like if there are no new hires on that particular day, then the report will get errored.. if there is any other master report with changes since last day, and if no rows are fetched, it will generate a blank output. In order to avoid the blank output and errored schedule schedule triggers come to our rescue.
How to define Schedule Triggers?
Schedule Triggers are defined at Data Model level. The schedule Trigger needs to be explicitly selected while scheduling the BI Report. While executing the schedule, first the trigger SQL will be evaluated to see if it returns any output, if yes then the actual report will run, else the schedule will get skipped instead of running to error.
Sample Trigger SQL Query for Last Day of the Month:
Navigate to the Data Model for the BI Report to which the schedule trigger needs to be added.
SQL Query to check if today is the last day of the month:
select 'true' from dual where trunc(sysdate) = trunc(last_day(sysdate)) -- We can select the any value in the select clause. If it doesn't return anything then the trigger will fail and the report will get skipped. If this sql returns any rows then the report will be executed.
SQL Query to run the report only on Weekdays:
select 1 from dual where UPPER(TO_CHAR(sysdate, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH')) NOT IN ('SAT','SUN')
Tip: If it’s a generic schedule trigger like month start and end date and many reports use the same logic, then the schedule trigger can be added to a single data model and the trigger can be selected while scheduling other reports as well. There is no restriction that the trigger needs to be present on the same report data model.
How to use Schedule Triggers?
Schedule Triggers will be used while scheduling the BI Reports on the schedule tab.
Select the Trigger from the dropdown. If the trigger is in another data model, then change the Data Model radio button and select the Data Model having the trigger.
Once the trigger is selected, submit the schedule.
As today isn’t the last day of the month, the report should get skipped.
With this we complete the demonstration of Schedule Triggers in BI Publisher Report.
Note: The schedule trigger concept is applicable only to BI reports and doesn’t apply for HCM Extracts as we don’t have the option to select the schedule trigger. It needs to be handled in the Data Group filter criteria or by customizing the Global Reports Data Model.
Even after following all the above steps, if you get any error “The Trigger SQL failed to execute” or “Failed to execute event trigger” like below:
Then the reason could be that there are some commented lines in the schedule query using /* comments */ or — comments in the Schedule SQL Query.
We need to remove the commented code from the SQL Query and the schedule triggers will work perfectly fine.
Top 10 Popular Posts from our Knowledgebase
- DataFusing FREE Cloud based SQL Developer like tool for Oracle Cloud
- Core HR Entity Relationship Diagram (ERD) for HCM Cloud
- Most Frequently used Document IDs from Oracle Support
- FREE Oracle Training & Accreditation with Oracle Learning Explorer
- How HDL Transformation Formula works with ElementEntry Sample
- FREE Oracle Fusion Demo Instance (VISION) Access
- Top 10 FREE useful tools for Fusion Consultants
- Learning Fusion HCM Technical Skillset
- Sample HCM Data Loader (HDL) files for all Business Objects
- Most frequently used Tables list in Fusion HCM