Wednesday, December 4, 2019

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:
  1. Sending Payslip to Employee via Email
  2. Sending Employee Joining Info to Manager via Email
  3. Sending Invoice Details to Payment Team
  4. 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:
  1. Employee Person Number
  2. Employee Email
  3. Employee Title
  4. Employee Display Name
  5. Employee Actual Termination Date
  6. Manager’s Email
  7. 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

No comments:

Post a Comment

Types of animations

Animation is a diverse and creative field with a variety of styles that artists and filmmakers use to bring their visions to life. Here are ...