You Are Here

Audit Assignment Changes

Objective

One of the business need for an HCM Cloud 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

Steps to Achieve

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

  • Enable Auditing for Assignment Business Object(This has been explained in one of other shared document)
  • Create a custom data model
  • Create a custom report

Creating a Custom Data Model

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

SQL
select ppnf.list_nameemployee_name,       papf.person_numberemployee_number,

       asg_audit.action_code action,

  asg_audit.last_update_dateaction_date,

       asg_audit.audit_action_type_ audit_action_type,

       (SELECT ppnf.list_name

  FROM   per_person_names_fppnf,

          per_userspu

  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_dateasg_action_date

from per_all_assignments_m_ asg_audit,

     per_all_people_fpapf,

per_person_names_fppnf

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) /* Employee Number for whom analysis 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_datedesc
  • Create a Custom Report as per requirement
  • Similarly, we can plan for some other queries as well

Related Posts