• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:June 12, 2020
  • Reading time:5 mins read
You are currently viewing SQL Query to fetch Cancelled Work Relationship and Reverse Termination
SQL Query to fetch Cancelled Work Relationship and Reverse Termination

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.