Generating Email Notifications Using OTBI In Oracle Fusion Cloud

Business Requirement

Many a times there is a need to send email notifications to employees, managers, HR Analysts, Payroll Administrators etc to inform them about specific business events which might have occurred during a course of time.

Some typical examples being:

Sending New Joinee Details to Manager
Sending Termination Details to Manager
Sending Birthday wishes to Employee
Sending Work Anniversary Greetings to Employee

And many more

While this kind of functionality was generally achieved in an EBS environment using Alerts the same results can be obtained in a Cloud Environment by using “ Narrative View” Feature of an OTBI Analysis.

In this example, we would try to create a “Termination Initiation” notification email to the Line Manager asking him/her to initiate exit formalities of subordinate.

So, without much ado let’s get started.

Creating OTBI Analysis

In order to generate such a notification email the first and foremost thing is to get your Data Content in place. For this example we would be using “Workforce Management – Worker Assignment Event Real Time” subject area and try to fetch relevant details.

The analysis XML used is given below:

Analysis XML

<saw:report xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sawx="com.siebel.analytics.web/expression/v1.1" xmlVersion="201201160">

<saw:criteria xsi:type="saw:simpleCriteria" subjectArea=""Workforce Management - Worker Assignment Event Real Time"" withinHierarchy="true">

<saw:columns>

<saw:column xsi:type="saw:regularColumn" columnID="c9ad74745af4b8295">

<saw:columnFormula>

<sawx:expr xsi:type="sawx:sqlExpression">"Worker"."Person Number"</sawx:expr></saw:columnFormula></saw:column>

<saw:column xsi:type="saw:regularColumn" columnID="c2c8d60d34b28271e">

<saw:columnFormula>

<sawx:expr xsi:type="sawx:sqlExpression">CASE WHEN "Worker"."Employee Gender" = 'Male' THEN 'His' ELSE 'Her' END</sawx:expr></saw:columnFormula></saw:column>

<saw:column xsi:type="saw:regularColumn" columnID="caab4f0128a8bbbbd">

<saw:columnFormula>

<sawx:expr xsi:type="sawx:sqlExpression">"Worker"."Employee Gender"</sawx:expr></saw:columnFormula></saw:column>

<saw:column xsi:type="saw:regularColumn" columnID="c3b78607c9b518bf2">

<saw:columnFormula>

<sawx:expr xsi:type="sawx:sqlExpression">"Worker"."Employee Individual Title"</sawx:expr></saw:columnFormula></saw:column>

<saw:column xsi:type="saw:regularColumn" columnID="c147df6ca9ef0f404">

<saw:columnFormula>

<sawx:expr xsi:type="sawx:sqlExpression">"Worker"."Employee Email Address"</sawx:expr></saw:columnFormula></saw:column>

<saw:column xsi:type="saw:regularColumn" columnID="c8507e33c85a60e49">

<saw:columnFormula>

<sawx:expr xsi:type="sawx:sqlExpression">"Worker"."Manager Person Number"</sawx:expr></saw:columnFormula></saw:column>

<saw:column xsi:type="saw:regularColumn" columnID="c78598ce2e8dde16e">

<saw:columnFormula>

<sawx:expr xsi:type="sawx:sqlExpression">"Worker"."Manager Name"</sawx:expr></saw:columnFormula></saw:column>

<saw:column xsi:type="saw:regularColumn" columnID="cb6066ae41b3498cf">

<saw:columnFormula>

<sawx:expr xsi:type="sawx:sqlExpression">"Worker"."Manager Type"</sawx:expr></saw:columnFormula></saw:column>

<saw:column xsi:type="saw:regularColumn" columnID="cb6066ae41b3498cf_d1">

<saw:columnFormula>

<sawx:expr xsi:type="sawx:sqlExpression">"Worker"."Employee Name"</sawx:expr></saw:columnFormula></saw:column>

<saw:column xsi:type="saw:regularColumn" columnID="c6d07682e90d7f915">

<saw:columnFormula>

<sawx:expr xsi:type="sawx:sqlExpression">"Worker"."Manager E-Mail Address"</sawx:expr></saw:columnFormula></saw:column>

<saw:column xsi:type="saw:regularColumn" columnID="cf2aadb6b5e823ef3">

<saw:columnFormula>

<sawx:expr xsi:type="sawx:sqlExpression">"Worker"."Employee Individual Title"</sawx:expr></saw:columnFormula></saw:column>

<saw:column xsi:type="saw:regularColumn" columnID="c465ed0acc86fed2f">

<saw:columnFormula>

<sawx:expr xsi:type="sawx:sqlExpression">"Worker"."Termination Date"</sawx:expr></saw:columnFormula></saw:column>

<saw:column xsi:type="saw:regularColumn" columnID="c9e336f5a37da0404">

<saw:columnFormula>

<sawx:expr xsi:type="sawx:sqlExpression">"Assignment Event Details"."Action Code"</sawx:expr></saw:columnFormula></saw:column>

<saw:column xsi:type="saw:regularColumn" columnID="cfe1bf947284f40f8">

<saw:columnFormula>

<sawx:expr xsi:type="sawx:sqlExpression">"HR Action"."Action Description"</sawx:expr></saw:columnFormula></saw:column></saw:columns>

<saw:filter>

<sawx:expr xsi:type="sawx:logical" op="and">

<sawx:expr xsi:type="sawx:comparison" op="notNull">

<sawx:expr xsi:type="sawx:sqlExpression">"Worker"."Manager Person Number"</sawx:expr></sawx:expr>

<sawx:expr xsi:type="sawx:comparison" op="notNull">

<sawx:expr xsi:type="sawx:sqlExpression">"Worker"."Termination Date"</sawx:expr></sawx:expr>

<sawx:expr xsi:type="sawx:comparison" op="equal" protected="true" source="drillDown">

<sawx:expr xsi:type="sawx:sqlExpression">"Worker"."Manager Type"</sawx:expr>

<sawx:expr xsi:type="xsd:string">LINE_MANAGER</sawx:expr></sawx:expr>

<sawx:expr op="equal" xsi:type="sawx:comparison">

<sawx:expr xsi:type="sawx:sqlExpression">"Assignment Event Details"."Action Code"</sawx:expr>

<sawx:expr xsi:type="xsd:string">RESIGNATION</sawx:expr></sawx:expr>

<sawx:expr xsi:type="sawx:sql">"Worker"."Termination Date" BETWEEN timestampadd(SQL_TSI_DAY,-1,CURRENT_DATE) AND (CURRENT_DATE)</sawx:expr></sawx:expr></saw:filter></saw:criteria>

<saw:views currentView="0">

<saw:view xsi:type="saw:compoundView" name="compoundView!1">

<saw:cvTable>

<saw:cvRow>

<saw:cvCell viewName="narrativeView!5">

<saw:displayFormat>

<saw:formatSpec hAlign="left"/></saw:displayFormat></saw:cvCell></saw:cvRow></saw:cvTable></saw:view>

<saw:view xsi:type="saw:tableView" name="tableView!1" scrollingEnabled="true" autoPreview="false">

<saw:edges>

<saw:edge axis="page" showColumnHeader="true"/>

<saw:edge axis="section"/>

<saw:edge axis="row" showColumnHeader="true">

<saw:edgeLayers>

<saw:edgeLayer type="column" columnID="c9ad74745af4b8295"/>

<saw:edgeLayer type="column" columnID="c147df6ca9ef0f404"/>

<saw:edgeLayer type="column" columnID="c8507e33c85a60e49"/>

<saw:edgeLayer type="column" columnID="c78598ce2e8dde16e"/>

<saw:edgeLayer type="column" columnID="cb6066ae41b3498cf"/>

<saw:edgeLayer columnID="cb6066ae41b3498cf_d1" type="column"/>

<saw:edgeLayer type="column" columnID="c6d07682e90d7f915"/>

<saw:edgeLayer type="column" columnID="cf2aadb6b5e823ef3"/>

<saw:edgeLayer type="column" columnID="c465ed0acc86fed2f"/>

<saw:edgeLayer type="column" columnID="c9e336f5a37da0404"/>

<saw:edgeLayer type="column" columnID="cfe1bf947284f40f8"/>

<saw:edgeLayer type="column" columnID="c3b78607c9b518bf2"/>

<saw:edgeLayer type="column" columnID="caab4f0128a8bbbbd"/>

<saw:edgeLayer type="column" columnID="c2c8d60d34b28271e"/></saw:edgeLayers></saw:edge>

<saw:edge axis="column" showColumnHeader="rollover"/></saw:edges></saw:view>

<saw:view xsi:type="saw:narrativeView" name="narrativeView!5">

<saw:viewCaption>

<saw:caption>

<saw:text>Initiiate Exit Formalities for Subordinate</saw:text></saw:caption></saw:viewCaption>

<saw:narrative>

<saw:caption fmt="text">

<saw:text>Hi @{c78598ce2e8dde16e},[br/][br/]

One of your subordinate @{cb6066ae41b3498cf_d1} has decided to leave the organization.[br/][br/]

Last Working Day is @{c465ed0acc86fed2f}[br/][br/]

As such, you are requested to initiate exit formalities.[br/][br/]

Details about the exit process can be found below:[br/][br/]

http://www.mycompany.com/exitformalities[br/][br/]

Kind Regards,[br/]

HR Team</saw:text></saw:caption></saw:narrative></saw:view></saw:views></saw:report>

Issued SQL is as below:

Issued SQL

SET VARIABLE PREFERRED_CURRENCY='User Preferred Currency 1';SELECT

0 s_0,

"Workforce Management - Worker Assignment Event Real Time"."Assignment Event Details"."Action Code" s_1,

"Workforce Management - Worker Assignment Event Real Time"."HR Action"."Action Description" s_2,

"Workforce Management - Worker Assignment Event Real Time"."Worker"."Employee Email Address" s_3,

"Workforce Management - Worker Assignment Event Real Time"."Worker"."Employee Gender" s_4,

"Workforce Management - Worker Assignment Event Real Time"."Worker"."Employee Individual Title" s_5,

"Workforce Management - Worker Assignment Event Real Time"."Worker"."Employee Name" s_6,

"Workforce Management - Worker Assignment Event Real Time"."Worker"."Manager E-Mail Address" s_7,

"Workforce Management - Worker Assignment Event Real Time"."Worker"."Manager Name" s_8,

"Workforce Management - Worker Assignment Event Real Time"."Worker"."Manager Person Number" s_9,

"Workforce Management - Worker Assignment Event Real Time"."Worker"."Manager Type" s_10,

"Workforce Management - Worker Assignment Event Real Time"."Worker"."Person Number" s_11,

"Workforce Management - Worker Assignment Event Real Time"."Worker"."Termination Date" s_12,

CASE WHEN "Workforce Management - Worker Assignment Event Real Time"."Worker"."Employee Gender" ='Male' THEN 'His' ELSE 'Her' END s_13,

DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Event Real Time"."Worker"."Employee Gender") s_14

FROM "Workforce Management - Worker Assignment Event Real Time"

WHERE

(("Worker"."Manager Person Number" IS NOT NULL) AND ("Worker"."Termination Date" IS NOT NULL) AND ("Worker"."Manager Type" = 'LINE_MANAGER') AND ("Assignment Event Details"."Action Code" = 'RESIGNATION') AND ("Worker"."Termination Date" BETWEEN timestampadd(SQL_TSI_DAY,-1,CURRENT_DATE) AND (CURRENT_DATE)))

ORDER BY 1, 12 ASC NULLS LAST, 14 ASC NULLS LAST, 5 ASC NULLS LAST, 15 ASC NULLS LAST, 6 ASC NULLS LAST, 4 ASC NULLS LAST, 10 ASC NULLS LAST, 9 ASC NULLS LAST, 11 ASC NULLS LAST, 7 ASC NULLS LAST, 8 ASC NULLS LAST, 13 ASC NULLS LAST, 2 ASC NULLS LAST, 3 ASC NULLS LAST

FETCH FIRST 75001 ROWS ONLY

Creating a Narrative View

Once we have created the OTBI Analysis we now need to create a Narrative View.

For this we would need to navigate to the “Results” section of analysis and choose

New View->Other Views -> Narrative

Next we need to define the narrative view and it is as follows:

Narrative View Template

Hi @7,[br/][br/]

One of your subordinate @9 has decided to leave the organization.[br/][br/]

@2 last working day is @12[br/][br/]

As such, you are requested to initiate exit formalities.[br/][br/]

Details about the exit process can be found below:[br/][br/]

http://www.mycompany.com/exitformalities[br/][br/]

Kind Regards,[br/]

HR Team

@7 means that the value of 7th Column from Analysis should be dynamically placed

[br/] is for line break

Creating Agent

Now that we have created the Analysis and Narrative View we would need to create an agent.

We would select the delivery content to be delivered directly and would also assign an Subject to Email Notification : ‘ Initiate Exit Formalities for Your Subordinate’and also assign a recipient email id (as shown)

Once done we should save the agent.

Running the Agent and Verifying the results

As a last step we would need to run the agent and verify results.

Ashish Harbhajanka

Comments

0 #1 Suhaib J Ahmed 2021-06-09 20:45

Hi,
Great post. I just woud like to know how this email will reach the intended recipients, i.e. the line manager?
The steps indicate that the recipient has been hard-coded.
Appreciate it if this confusion can be cleared.
Thank you.

Quote

Refresh comments list
RSS feed for comments to this post

Home

Trainings

Fusion Blog

EBS Blog

Authors

CONTACT US

Search Courses