In this article we will look into how to get the Cancelled Work Relationship and Reverse Termination details.
When you cancel work relationship, the assignment and work relationship related information is deleted from PER_ALL_ASSIGNMENTS_M and PER_PERIODS_OF_SERVICE tables. Only the personal details will remain in the system for any future use.
Cancelling work relationship could be done due to candidates don’t turn up for joining (No show) cases, or data has been loaded incorrectly in a different legal employer.
This information cannot be retrieved using the OTBI subject areas as the assignment information is completely deleted for these employees.
For objects like Assignment, which use the Actions Framework, whenever an action occurs, a corresponding entry is made in the action occurrences table (PER_ACTION_OCCURRENCES).
So, Whenever there is Cancel Work Relationship or a Reverse Termination, even though the assignment record is deleted, there is an entry in this action occurrences table which we can use to get details of the transaction.
This table can be queried to find out the persons for whom a Cancel Work Relationship or a Reverse Termination or any other actions have occurred.
Specifically in the case of Cancel Work Relationship and Reverse Termination, the column PARENT_ENTITY_KEY_ID stores the PERSON_ID of the worker for whom this Cancel Work Relationship or Reverse Termination has occurred.
If there are any integrations to downstream applications then they need to be informed of this transaction to delete this employee information.
Table of Contents
SQL Query to get Cancelled Work Relationship Details:
SELECT PAPF.PERSON_NUMBER ,PER_TYPES.SYSTEM_PERSON_TYPE ,NAMES.LAST_NAME ,NAMES.FIRST_NAME ,PPTUM.LAST_UPDATED_BY AS MODIFIED_BY ,TO_CHAR(PPTUM.LAST_UPDATE_DATE, 'MM-dd-yyyy') AS MODIFIED_DATE FROM PER_ALL_PEOPLE_F PAPF ,PER_PERSON_TYPE_USAGES_M PPTUM ,PER_PERSON_TYPES_VL PER_TYPES ,PER_PERSON_NAMES_F NAMES WHERE 1=1 AND PAPF.PERSON_ID = PPTUM.PERSON_ID(+) AND PPTUM.PERSON_TYPE_ID = PER_TYPES.PERSON_TYPE_ID AND PER_TYPES.SYSTEM_PERSON_TYPE = 'CANCELED_HIRE' AND PAPF.PERSON_ID = NAMES.PERSON_ID AND NAMES.NAME_TYPE = 'GLOBAL' AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE AND SYSDATE BETWEEN PPTUM.EFFECTIVE_START_DATE AND PPTUM.EFFECTIVE_END_DATE AND SYSDATE BETWEEN NAMES.EFFECTIVE_START_DATE AND NAMES.EFFECTIVE_END_DATE
SQL Query to get details on which action has been performed and when for Cancelled Work Relationship and Reverse Termination Cases:
SELECT PAOCC.ACTION_OCCURRENCE_ID, PACTB.ACTION_CODE, PACTB.ACTION_TYPE_CODE, PAOCC.ACTION_DATE, PAOCC.PARENT_ENTITY_KEY_ID AS "PERSON ID", PAOCC.CREATED_BY, PAOCC.CREATION_DATE, PAOCC.LAST_UPDATE_DATE, PAOCC.LAST_UPDATED_BY FROM PER_ACTION_OCCURRENCES PAOCC, PER_ACTIONS_B PACTB WHERE PAOCC.ACTION_ID = PACTB.ACTION_ID AND PACTB.ACTION_CODE IN ('EMPL_CANCEL_WR', 'ORA_EMPL_REV_TERMINATION')
SQL Query to get cancelled Person and Assignment information from Audit tables:
select distinct ppos_.PERSON_ID PERSON_ID , papf_.PERSON_NUMBER PERSON_NUMBER , ppos_.DATE_START ASSIGNMENT_START_DATE , paam_.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER , paam_.EFFECTIVE_SEQUENCE ASSIGNMENT_SEQUENCE , ppos_.LAST_UPDATE_DATE LAST_UPDATE_DATE , ppos_.LAST_UPDATED_BY LAST_UPDATED_BY , ppos_.AUDIT_ACTION_TYPE_ ACTION from PER_PERIODS_OF_SERVICE_ ppos_ , PER_ALL_PEOPLE_F_ papf_ , PER_ALL_ASSIGNMENTS_M_ paam_ where paam_.ASSIGNMENT_TYPE IN ('E','C') AND ppos_.AUDIT_ACTION_TYPE_='DELETE' AND paam_.PERIOD_OF_SERVICE_ID = ppos_.PERIOD_OF_SERVICE_ID AND paam_.PERSON_ID=papf_.PERSON_ID AND ppos_.PERSON_ID=papf_.PERSON_ID
This query will work only if the audit functionality is enabled for Assignment business object.
Tip: For Cancelling Work Relationship, the Assignment information is completely purged, however the person details still stay in the system. And we can get the transaction details from Action Occurrences tables.
Top 10 Popular Posts from our Knowledgebase
- DataFusing FREE Cloud based SQL Developer like tool for Oracle Cloud
- Core HR Entity Relationship Diagram (ERD) for HCM Cloud
- Most Frequently used Document IDs from Oracle Support
- FREE Oracle Training & Accreditation with Oracle Learning Explorer
- How HDL Transformation Formula works with ElementEntry Sample
- FREE Oracle Fusion Demo Instance (VISION) Access
- Top 10 FREE useful tools for Fusion Consultants
- Learning Fusion HCM Technical Skillset
- Sample HCM Data Loader (HDL) files for all Business Objects
- Most frequently used Tables list in Fusion HCM