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