Steps to Create Alert in Oracle HRMS

By | September 1, 2016

In this post, you will find the steps to create the alerts in oracle hrms.

By default when you install Vision instance, Only few application are licensed. You need to license the required products in order to use it. If you are working on vision instance, Please find the steps to enable alerts license mentioned in my previous post.

Following are the steps to create the alert in vision instance.

Create Alert Definition
Create the Scenario
Create an Alert
Define Action
Define Action set
Verify Alert
Submit Alert
Find the Alert History

Create Alert Definition

Login with User that has responsibility of Alert Manager – Vision University

Oracle HRMS Overview title 1

Go to Alert Manager responsibility and click on Define Function

Oracle HRMS Overvoiew title 2

Following form will be opened

Oracle HRMS Overview title 3

Select the Human Resources Application

Oracle HRMS Overview title 4

Back to top

Create the Scenario

 

We want to create an alert that should give us the details of all those employees whose assignment is changed. Let me first show you how you can create this scenario on your instance so that you can replicate the same in your oracle hrms instnace.

Go to AE HRMS Manager responsibility. You can go to any HRMS Responsibility.

Oracle HRMS Overview title 5

Go to following navigation

Oracle HRMS O verview title 6

Following screen will open

Oracle HRMS Overview title 7

Enter any employee Number and press find. For example, we searched for employee number 3

Oracle HRMS Overview title 8

This is his current assignment

Oracle HRMS Overview title 9

Lets change any part of his assignment so that our alert query could fetch it.

We have changed his job from Assistant to Personal Assistant as shown below

Oracle HRMS Overview title 10

Likewise for Employee 15, we changed the Grade from Management 9 to Management 10

Oracle HRMS Overview title 11

Following is the query that should bring all those employees whose assignment is changed today

select ppf.full_name,ppf.employee_number
,(select name from per_grades where grade_id = paaf.grade_id) curr_grade ,(select name from per_grades where grade_id = paaf_prev.grade_id) prev_grade
,(select name from per_jobs where job_id = paaf.job_id) curr_job ,(select name from per_jobs where job_id = paaf_prev.job_id) prev_job
,(select name from per_positions where position_id = paaf.position_id) curr_pos ,(select name from per_positions where position_id = paaf_prev.position_id) prev_pos
,(select payroll_name from pay_all_payrolls_f where payroll_id = paaf.payroll_id) curr_Payroll,(select payroll_name from pay_all_payrolls_f where payroll_id =paaf_prev.payroll_id) Prev_Payroll
from per_all_people_f ppf, per_all_assignments_f paaf, per_all_assignments_f paaf_prev
where 1=1
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
and paaf.person_id = ppf.person_id
and ppf.business_group_id = paaf.business_group_id
and paaf_prev.person_id = ppf.person_id
and paaf_prev.business_group_id= ppf.business_group_id
and paaf.effective_start_date-1 between paaf_prev.effective_start_date and paaf_prev.effective_end_date
and (paaf.grade_id <> paaf_prev.grade_id
or paaf.job_id <> paaf_prev.job_id
or paaf.position_id <> paaf_prev.position_id
or nvl(paaf.payroll_id,1) <> nvl(paaf_prev.payroll_id,1))
and trunc(paaf.LAST_UPDATE_DATE) = trunc(sysdate)

Figure below shows the two records for which assignment is updated

Oracle HRMS Overview title 12

Data looks like this

Oracle HRMS Overview title 13

Oracle HRMS Overview title 14

Back to top

Create an Alert

Go to Alert Manager responsibility

Oracle HRMS Overview title 15

Open the screen shown in the navigation below

Oracle HRMS Overview title 16

Following screen will open

Oracle HRMS Overview title 17

Enter the Data in Alert Form

Oracle HRMS Overview title 18

Following is the query

select ppf.full_name,ppf.employee_number
,(select name from per_grades where grade_id = paaf.grade_id) curr_grade ,(select name from per_grades where grade_id = paaf_prev.grade_id) prev_grade
,(select name from per_jobs where job_id = paaf.job_id) curr_job ,(select name from per_jobs where job_id = paaf_prev.job_id) prev_job
,(select name from per_positions where position_id = paaf.position_id) curr_pos ,(select name from per_positions where position_id = paaf_prev.position_id) prev_pos
,(select payroll_name from pay_all_payrolls_f where payroll_id = paaf.payroll_id) curr_Payroll,(select payroll_name from pay_all_payrolls_f where payroll_id =paaf_prev.payroll_id) Prev_Payroll
into
&full_name,&employee_number
,&curr_grade ,&prev_grade
,&curr_job ,&prev_job
,&curr_pos ,&prev_pos
,&curr_Payroll,&Prev_Payroll
from per_all_people_f ppf, per_all_assignments_f paaf, per_all_assignments_f paaf_prev
where 1=1
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
and paaf.person_id = ppf.person_id
and ppf.business_group_id = paaf.business_group_id
and paaf_prev.person_id = ppf.person_id
and paaf_prev.business_group_id= ppf.business_group_id
and paaf.effective_start_date-1 between paaf_prev.effective_start_date and paaf_prev.effective_end_date
and (paaf.grade_id <> paaf_prev.grade_id
or paaf.job_id <> paaf_prev.job_id
or paaf.position_id <> paaf_prev.position_id
or nvl(paaf.payroll_id,1) <> nvl(paaf_prev.payroll_id,1))
and trunc(paaf.LAST_UPDATE_DATE) = trunc(sysdate)

Save it.

Make sure you set Keep Days at least a week time or 10 days. Otherwise you will not be able to see the history or Alerts.

Back to top

Define Action

Click on Action Button

Oracle HRMS Overview title 19

Enter the name of action

Oracle HRMS Overview title 20

Click on Save button , Following screen will be opened

Oracle HRMS Overview title 21

Enter the data as given in the table

Oracle HRMS Overview title 22

Text Body is as given below

Dear HR Team
Following is the list of employees for assignment changes=**= Enter summary template below this line =**=
**Employee Number : &EMPLOYEE_NUMBER
Current Grade: &CURR_GRADE
Previous Grade: &PREV_GRADE
Current Job: &curr_job
Previous Job: &prev_job
Current Position: &curr_pos
Previous Position: &prev_pos
Current Payroll: &curr_Payroll
Previous Payroll: &Prev_Payroll
———————————–
=**= Enter summary template above this line =**=Regards
SysadminNote: This is a system generated email, Please do not reply to this email.

Save and close this form

Back to top

Define Action Set


Now click on Action Sets Button

Oracle HRMS Overview title 23

Following form will be opened, Enter the Action set name

Oracle HRMS Overview title 24

Click on Action Set Details button and select the Members tab

Oracle HRMS Overview title 25

Click on Action Field and click on small button to open the LOV

Oracle HRMS Overview title 26

In the LOV, write % and click find button

Oracle HRMS Overview title 27

Select the Assignment Change List

Oracle HRMS Overview title 28

Save the records.

Back to top

Verify Alert


Once Alert is ready, Click on Verify button to validate the query

Oracle HRMS Overview title 29

Submit Alert


Go to following Navigation

Oracle HRMS Overview title 30

Enter the Application Name and Alert Name and in the Start Date make sure to set the time field 00:00:00 so that it should fire immediately

Oracle HRMS Overview title 31

Click on Submit Request, Following Message is shown

Oracle HRMS Overview title 32

You can see the Alert Request Pending to be Executed by Concurrent Manager, Wait till it gets completed.

Oracle HRMS Overview title 33

Back to top

Find the Alert History


To see the output, go to following navigation

Oracle HRMS Overview title 34

Enter the details and click Find Actions Button

Oracle HRMS Overview title 35

Following screen will be shown

Oracle HRMS Overview title 36

Select the Action Name Field and Click on Action Log button

Oracle HRMS Overview title 37

It will show the email that should be received by the employee

Oracle HRMS Overview title 38

If you have liked the contents in this post and you think it can be helpful to others, please share it at least once in your circle, in this way, you will join me in my cause to Learn Share and Grow. Come on, Lets Grow Together.

Related Posts

Enable Alerts License on vision Instance
Ho to Implement Zoom Functionality
How to create SQL Loader based Concurrent Program
WISTU – Oracle Workflow Builder
Free Online Test
Solved Issued of Empty Email Notification
Steps for creating custom form in Oracle Apps
How to create backend logging mechanism
Useful Apps SQL Queries Pool
Top 10 Apps Technical Interview Questions