Oracle Fusion :- BIP Bursting In Oracle Fusion Cloud

Oracle Fusion :- BIP Bursting In Oracle Fusion Cloud

Business Requirement Many a times there is a need to send details to a specific distribution list or an individual based on a specific event/transaction which has taken place in the ERP Application. Some of such use case scenario being: Sending Payslip to Employee via Email Sending Employee Joining Info to Manager via Email Sending Invoice Details to Payment Team Sending Birthday/Work Anniversary Greetings Email to Employee All of the above were easily achieved in an On-Premise environment (EBS particularly) either by creating a Custom Alert (Periodic/Event depending on the specific business requirement) or a custom pl/sql program which would send details over email or even by using BI Publisher Bursting Feature. However, in a Cloud Environment we do not have the flexibility of using custom code and although the Alert Manager feature is available (starting Release 12) one need to check whether there are events already present against which one could raise the alert action. So does this means one would not be able to perform such actions in a Cloud Setup? No, absolutely not. While we don’t have the flexibility of writing a custom pl/sql nor can we can define new alerts as per our requirement but we still can use BI Publisher Bursting Feature and achieve desired results. In this article, we would try to demonstrate the same. For this example, we would try to send a “Termination Initiation Notice” to Manager whenever a subordinate submits resignation. The manager would be send the information about the Employee Name along with his/her Person# and actual termination date in an Email. The Email should also have a static internal portal link (which will contain knowledge documents describing how to perform exit formalities). Additionally, there should also be a PDF attachment in the Email. So, let-us begin then. Worked Example We would have to create a SQL Query which would form our data source for this BIP Report. The Report should fetch details like: Employee Person Number Employee Email Employee Title Employee Display Name Employee Actual Termination Date Manager’s Email Manager Display Name Data Model Query SELECT papf.person_number EmployeeNumber,       INITCAP(ppnf.title)         EmployeeTitle,       ppnf.display_name  EmployeeName,       pea.email_address EmployeeEmail,       to_char(pps.actual_termination_date,'MM-DD-YYYY') TerminationDate,       (select papf1.person_number        from   per_all_people_f papf1        where  papf1.person_id = pasf.manager_id        and    trunc(sysdate) between papf1.effective_start_date and papf1.effective_end_date) AssignmentManagerPersonNumber,       (select ppnf1.display_name        from   per_person_names_f ppnf1        where  ppnf1.person_id = pasf.manager_id        and    ppnf1.name_type = 'GLOBAL'        and    trunc(sysdate) between ppnf1.effective_start_date and ppnf1.effective_end_date) ManagerName,      (select pea1.email_address        from   per_email_addresses pea1        where  pea1.person_id = pasf.manager_id        and    pea1.email_type = 'W1') ManagerEmail,       to_char(trunc(pps.last_update_date),'MM-DD-YYYY') ppslud,       to_char(trunc(sysdate-1),'MM-DD-YYYY') yesterdaydate,       to_char(trunc(sysdate),'MM-DD-YYYY') currentdate,       papf.person_id papfpersonid,       pasf.person_id pasfpersonid,       pasf.manager_id pasfmgrid FROM   per_all_people_f papf,       per_all_assignments_m paam,       per_assignment_supervisors_f pasf,       per_person_names_f ppnf,       per_email_addresses pea,       per_periods_of_service pps where  papf.person_id = paam.person_id and    paam.assignment_type IN ('E','C') and    pps.person_id = papf.person_id and    pps.actual_termination_date is not null and    pea.email_type = 'W1' and    pea.person_id = papf.person_id and    ppnf.person_id = papf.person_id and    ppnf.name_type = 'GLOBAL' and    pasf.person_id = paam.person_id and    paam.primary_flag = 'Y' and    trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and    trunc(sysdate) between paam.effective_start_date and paam.effective_end_date and    trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date and    trunc(sysdate) between pasf.effective_start_date and pasf.effective_end_date and    pasf.manager_type = 'LINE_MANAGER' and    trunc(pps.last_update_date) between trunc(sysdate - 1) and trunc(sysdate) order by pps.last_update_date desc  We would also need to create a BI Bursting Query BI Bursting Query SELECT EmployeeNumber KEY,       'BITemplate' TEMPLATE,       'en-us' LOCALE,       'PDF' OUTPUT_FORMAT,       'EMAIL' DEL_CHANNEL,       'abc@mymail.com' parameter1,       'def@mymail.net' parameter2,       'orafusion@bursting.com' parameter3,       'Termination Initiation' parameter4,       'This is to inform you that one of your subordinate have decided to leave the organization for personal reasons.        Please initiate exit formalities.        Refer https://www.mycompany.com/exitformalities for details.        Regards        HR Team ' parameter5,       'true' parameter6,       'replyto@nowhere.com' parameter7 FROM    (SELECT papf.person_number EmployeeNumber,       INITCAP(ppnf.title)         EmployeeTitle,       ppnf.display_name  EmployeeName,       pea.email_address EmployeeEmail,       to_char(pps.actual_termination_date,'MM-DD-YYYY') TerminationDate,       (select papf1.person_number        from   per_all_people_f papf1        where  papf1.person_id = pasf.manager_id        and    trunc(sysdate) between papf1.effective_start_date and papf1.effective_end_date) AssignmentManagerPersonNumber,       (select ppnf1.display_name        from   per_person_names_f ppnf1        where  ppnf1.person_id = pasf.manager_id        and    ppnf1.name_type = 'GLOBAL'        and    trunc(sysdate) between ppnf1.effective_start_date and ppnf1.effective_end_date) ManagerName,      (select pea1.email_address        from   per_email_addresses pea1        where  pea1.person_id = pasf.manager_id        and    pea1.email_type = 'W1') ManagerEmail,       to_char(trunc(pps.last_update_date),'MM-DD-YYYY') ppslud,       to_char(trunc(sysdate-1),'MM-DD-YYYY') yesterdaydate,       to_char(trunc(sysdate),'MM-DD-YYYY') currentdate,       papf.person_id papfpersonid,       pasf.person_id pasfpersonid,       pasf.manager_id pasfmgrid FROM   per_all_people_f papf,       per_all_assignments_m paam,       per_assignment_supervisors_f pasf,       per_person_names_f ppnf,       per_email_addresses pea,       per_periods_of_service pps where  papf.person_id = paam.person_id and    paam.assignment_type IN ('E','C') and    pps.person_id = papf.person_id and    pps.actual_termination_date is not null and    pea.email_type = 'W1' and    pea.person_id = papf.person_id and    ppnf.person_id = papf.person_id and    ppnf.name_type = 'GLOBAL' and    pasf.person_id = paam.person_id and    paam.primary_flag = 'Y' and    trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and    trunc(sysdate) between paam.effective_start_date and paam.effective_end_date and    trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date and    trunc(sysdate) between pasf.effective_start_date and pasf.effective_end_date and    pasf.manager_type = 'LINE_MANAGER' and    trunc(pps.last_update_date) between trunc(sysdate - 1) and trunc(sysdate) order by pps.last_update_date desc ) Also, we have created a ‘Schedule Trigger’ to ensure that the Scheduled Report should only send email whenever there is data returned from the mail Data Model SQL. In this case the Schedule Trigger would only return a TRUE value whenever a termination happens between Current System Date and one day prior to it. Schedule Trigger Query SELECT 1 FROM   per_all_people_f papf,       per_all_assignments_m paam,       per_assignment_supervisors_f pasf,       per_person_names_f ppnf,       per_email_addresses pea,       per_periods_of_service pps where  papf.person_id = paam.person_id and    paam.assignment_type IN ('E','C') and    pps.person_id = papf.person_id and    pps.actual_termination_date is not null and    pea.email_type = 'W1' and    pea.person_id = papf.person_id and    ppnf.person_id = papf.person_id and    ppnf.name_type = 'GLOBAL' and    pasf.person_id = paam.person_id and    paam.primary_flag = 'Y' and    trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and    trunc(sysdate) between paam.effective_start_date and paam.effective_end_date and    trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date and    trunc(sysdate) between pasf.effective_start_date and pasf.effective_end_date and    pasf.manager_type = 'LINE_MANAGER' and    trunc(pps.last_update_date) between trunc(sysdate - 1) and trunc(sysdate) order by pps.last_update_date desc RTF Template Running the Report When we run the report and check the same using the ‘Schedule Option’ we can see the following: Verifying the Results A quick check on the received email confirms that the expected content is delivered And the attachment looks as per the BI Template