Creating Custom ESS Job to get Assignment Audit History in Oracle HCM Cloud Application

Introduction

One of the business need for an HCM Cloud implementation project is to get detailed information about the Assignment Changes which has taken place in the application along with the insight of who performed the change and when. These details can be fetched if the Audit Functionality is enabled.

In this article we will try to demonstrate how we can create a custom ESS job which can be used to get Assignment Audit History Table.

Worked-Out Example

We would have to follow the below mentioned high level steps in order to accomplish the same:

Enable Auditing for Assignment Business Object
Create a custom data model
Create a custom report
Create a custom ESS Job
Run ESS Job to get results.

Enable Auditing for Assignment Business Object

Navigation: Setup and Maintenance -> Search (Manage Audit Policies)->Configure Business Object Attributes

A screenshot of text

Description automatically generated

Creating a Custom Data Model

We would need to create a custom data model using SQL.

SQL

select ppnf.list_name employee_name,

papf.person_number employee_number,

asg_audit.action_code action,

asg_audit.last_update_date action_date,

asg_audit.audit_action_type_ audit_action_type,

(SELECT ppnf.list_name

FROM per_person_names_f ppnf,

per_users pu

WHERE ppnf.name_type = 'GLOBAL'

AND trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date

AND ppnf.person_id = pu.person_id

AND pu.username = asg_audit.created_by

) action_taken_by,

asg_audit.effective_start_date asg_action_date

from per_all_assignments_m_ asg_audit,

per_all_people_f papf,

per_person_names_f ppnf

where papf.person_id = asg_audit.person_id

and papf.person_id = ppnf.person_id

and asg_audit.assignment_type = 'E'

and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date

and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date

and ppnf.name_type = 'GLOBAL'

and papf.person_number = nvl(:p_person_number,papf.person_number) /* Colleague Number for whom investigation is required*/

and asg_audit.action_code = nvl(:p_action_code,asg_audit.action_code)

--and asg_audit.effective_start_date >= TO_DATE('2020/06/15','YYYY/MM/DD') /* Date on which Discrepancy happened */

order by asg_audit.last_update_date desc

Create a Custom Report

We would now need to create a report from the data model. The Report would appear as below:

A screenshot of a cell phone

Description automatically generated

Create a Custom ESS Job

In this step, we would create a custom ESS job.

Navigation->Setup and Maintenance-> Search (Manage Enterprise Scheduler Job Definitions and Job Sets for Human Capital Management and Related Applications)

A screenshot of a social media post

Description automatically generated

Before we proceed with creating a new Custom ESS job we could create a custom List of Values which we will utilize for “Assignment Action Code”.

Navigation->Setup and Maintenance-> Search (Manage Enterprise Scheduler Job Definitions and Job Sets for Human Capital Management and Related Applications)->Manage List of Values Sources

Details to be populated in the LOV are as below:

AssignmentActionCodeLOV
Attribute Name	Attribute Value
*Application Name	Global Human Resources
*User List of Values Source Name	AssignmentActionCodeLOV
Description	Custom List of values to display all Assignment Action Code Values
*LOV Type	User-Defined
*Query	select distinct asg_audit.action_code from per_all_assignments_m_ asg_audit order by asg_audit.action_code

A screenshot of a cell phone

Description automatically generated

Now we will create the ESS Job. Details used are as below:

Assignment Audit Report
Attribute Name	Attribute Value
*Display Name	Assignment Audit Report
Name	ASG_AUDIT_REPORT
Path	/hr/
Application	Global Human Resources
Description	Custom ESS job to get assignment audit details
*Job Application Name	EarHcmEss
Default Output Format	TXT
*Report ID	/Custom/Practice Samples/AssignmentAuditReport.xdo
Priority	2
Allow Multiple Pending Submissions	True
Enable Submission from Scheduled Processes	Checked