Login
Register

Home

Trainings

Fusion

R12

Blogs

Contact Us

Fusion PayRoll
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new Click here to raise Support Ticket. Get reply within 48 hours.

Search Courses

User Rating: 0 / 5

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
 

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,

      'This email address is being protected from spambots. You need JavaScript enabled to view it.' parameter1,

      'This email address is being protected from spambots. You need JavaScript enabled to view it.' parameter2,

      'This email address is being protected from spambots. You need JavaScript enabled to view it.' 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,

      'This email address is being protected from spambots. You need JavaScript enabled to view it.' 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


Ashish Harbhajanka

About the Author

Ashish Harbhajanka

 

Oracle Fusion HCM Techno Functional Consultant with overall 10 years of Experience in software industry with 5 years in EBS HRMS and rest 5 in Fusion HCM.

My areas of intesrest in Fusion HCM include :

a) Inbound Outbound Integration using FBL/HDL or BIP/HCM Extracts.

b) Fast Formula

c) BIP Reports

d) OTBI Reports

e) RESTFUL API / Web Service Call

f) Functional Setup

g) End to End Testing

h) Regression Testing

i) Preparing COnfiguration Workbooks

j) Creating Speed Solutions

k) Preparing User Guides

l) UPK

........

Overall Rating (0)

0 out of 5 stars
  • No comments found

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Nov 2017  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
    1  2  3  4  5
  6  7  8  9101112
13141516171819
2021222324
   

Enquire For Training

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner