520 total views
In this article we will look into the schedule trigger concept in BI Publisher and the use cases of it.
Want to learn Fusion Technical tools? Checkout this article
- 1 What is Schedule Trigger?
- 2 What are the Use cases?
- 3 How to define Schedule Triggers?
- 4 How to use Schedule Triggers?
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.
Latest posts by Sricharan Monigari (see all)
- Renaming HCM Extracts is a possibility from 20D - November 24, 2020
- Sangam20 – Oracle Users Group Conference - November 23, 2020
- Passing Parameter values from Flow Instance to HDL Transformation Formula - November 23, 2020
- Solve manual FTP server update issue for HCM Extracts post every P2T refresh - November 16, 2020