Sending PDF Document with E-mail in Oracle Fusion using Bursting and Trigger
Hi everyone, I try to explain this process to understand clearly and easily. I hope, this article will be helpfull.
Firstly, you need to create data model for values to use in document you will send. Data Model consists of many components like Datasets, Trigger, Bursting. In this section, it is enough to create datasets. We will create trigger and bursting next steps.
For now, in example, we have a list of absence all but the main purpose isn’t sending document for all absence and for all user. Our main purpose is sending document has absence information to one user for one matched absence approved after running job for report (We will run the job in every 5 minutes).
SQL is so important especially Where section. In this example, I added some critical where clause in the end of my SQL code. Job and SQL code must be synchronous.
Where …
and ABSENTRY.Last_Update_Date Between (SYSDATE — INTERVAL ‘5’ MINUTE) and (SYSDATE)
and ABSENTRY.APPROVAL_STATUS_CD = ‘APPROVED’
I know when approved an absence data will change inside last update date cell and I want to receive instant data by the way and I know I will run the job every five minutes and my sql code must be like that because I want data for last 5 minutes. You can change of course what every you want. Maybe you define every 30 minutes. It doesn’t matter.
So you have to create a report has connected with the data model created before. This report is document you will send to users. Document name is important. We will use the name of document in Bursting section preparing mail SQL. In the example, Name is ‘Absence_PrintOut_Form’
We are ready to pass Bursting. So we can open date model and create a bursting. We will write SQL code for bursting again but has rule and layout.
“Split By” is about how we can separate data list. We send absence document for one absence to one user. The user can create 2 or more absence but it doesn’t matter. We will send document for each absence. We will choose Absence_Id value inside main dataset.
“Delivery By” is about the user and email who we will send document about absence. We can select User_Id or something like this if you have user information inside dataset so we can reach email address but I prefer to reach email of user using absence query. I choose Absence_Id.
Last one “Delivery SQL Query” is about layout, arrangements and settings of email. We will write sql codes linked with Delivery By. SQL code must have some rules.
You can examine source link in end of this document for layout. Title is Defining the Query for the Delivery XML. Every value is important and changeable for demands but ‘KEY’ must be the same Delivery By and ‘TEMPLATE’ must be the name of document so It’s “Absence_PrintOut_Form”. I added SQL Deliver Codes end of the document. In this section you can use SQL skills to reach data for using email body, subject, email etc.
We can create Trigger after Bursting. We can control to exist data when we run sql code with trigger. Type must be Schedule. Because we will use in Schedule and code is so simple. We can say minified of Absence query.
Purpose of trigger is If there is data, the system runs the job. if not, the system skips the job. Actually, this is saving time and the system run the job if its necessary.
We need to choose Bursting Template from Report. So we are going to Edit Report and click Properties button. We check Enable Bursting and select name of Bursting inside Data Model we created before.
We will schedule the report. Click to More then Schedule button.
You don’t change anything in General and Output tabs. The system arranges automatically these tabs.
We will use trigger and set schedule time in Schedule Tab. We want to check by system for every 5 minutes so schedule settings must be the same like screenshot in the below.
To use trigger, we need to check Use Trigger Checkbox. You can use trigger from different Data model. Then We select Trigger name we created before inside data model.
Job can face an error because of some reason you cannot notice. Settings in Notification tab when system face some error etc. system send error mail to defined mail in tab. So you can solve error with error mail from system.
Steps are done. Finally you created a job that system ready to send pdf document with email to users for each absence using Bursting, Trigger.
Source : https://www.youtube.com/watch?v=dvSrZBuhAZ4
SQL, PDF Document, Data Model : https://www.kisa.link/NYgK
We will meet again… Thanks a lot.
