In this article, we will look into how to get the User’s active/suspended status along with the latest assignment information of the employee.
PER_USERS is the main table where we can find the user information. We will join this table to the person and assignment tables to get the information that we need.
WITH EMP_TERM AS (
SELECT PAPF.PERSON_ID
,PAPF.PERSON_NUMBER
,PPNF.FIRST_NAME
,PPNF.LAST_NAME
,PPNF.DISPLAY_NAME
,MAX(PAAF.ASSIGNMENT_NUMBER) MAX_ASSIGNMENT_NUMBER
,CASE WHEN MAX(NVL(PPOS.ACTUAL_TERMINATION_DATE,SYSDATE))=SYSDATE then NULL ELSE TO_CHAR(MAX(PPOS.ACTUAL_TERMINATION_DATE), 'YYYY-MM-DD') END MAX_TERMINATION_DATE
FROM PER_ALL_PEOPLE_F PAPF
,PER_PERSON_NAMES_F PPNF
,PER_ALL_ASSIGNMENTS_F PAAF
,PER_PERIODS_OF_SERVICE PPOS
WHERE PAPF.PERSON_ID = PPNF.PERSON_ID
AND PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAPF.PERSON_ID = PPOS.PERSON_ID
AND PAAF.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
AND PAAF.PRIMARY_ASSIGNMENT_FLAG = 'Y'
AND PAAF.ASSIGNMENT_TYPE = 'E'
AND PAAF.EFFECTIVE_LATEST_CHANGE = 'Y'
AND PPNF.NAME_TYPE = 'GLOBAL'
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
GROUP BY PAPF.PERSON_ID,PAPF.PERSON_NUMBER,PPNF.FIRST_NAME,PPNF.LAST_NAME,PPNF.DISPLAY_NAME
ORDER BY PAPF.PERSON_NUMBER
)
,EMP_ASSIGNMENT AS (
SELECT PAPF.PERSON_ID
,PAPF.PERSON_NUMBER
,PPNF.FULL_NAME
,PJFT.NAME JOB_NAME
,PJF.APPROVAL_AUTHORITY JOB_LEVEL
,HLA.LOCATION_NAME LOCATION_NAME
,HAUFT.NAME DEPARTMENT
FROM PER_ALL_PEOPLE_F PAPF
JOIN PER_PERSON_NAMES_F PPNF ON PAPF.PERSON_ID = PPNF.PERSON_ID
JOIN PER_ALL_ASSIGNMENTS_F PAAF ON PAPF.PERSON_ID = PAAF.PERSON_ID
JOIN PER_JOBS_F PJF ON PAAF.JOB_ID = PJF.JOB_ID
JOIN PER_JOBS_F_TL PJFT ON PJF.JOB_ID = PJFT.JOB_ID
JOIN HR_LOCATIONS_ALL HLA ON PAAF.LOCATION_ID = HLA.LOCATION_ID
JOIN HR_ALL_ORGANIZATION_UNITS_F HAOUF ON PAAF.ORGANIZATION_ID = HAOUF.ORGANIZATION_ID
JOIN HR_ORG_UNIT_CLASSIFICATIONS_F HOUCF ON HAOUF.ORGANIZATION_ID = HOUCF.ORGANIZATION_ID
JOIN HR_ORGANIZATION_UNITS_F_TL HAUFT ON HAOUF.ORGANIZATION_ID = HAUFT.ORGANIZATION_ID
AND HAUFT.EFFECTIVE_START_DATE = HAOUF.EFFECTIVE_START_DATE
AND HAUFT.EFFECTIVE_END_DATE = HAOUF.EFFECTIVE_END_DATE
WHERE PPNF.NAME_TYPE = 'GLOBAL'
AND PAAF.PRIMARY_ASSIGNMENT_FLAG = 'Y'
AND PAAF.ASSIGNMENT_TYPE = 'E'
AND PAAF.EFFECTIVE_LATEST_CHANGE = 'Y'
AND PAAF.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND PJFT.LANGUAGE = 'US'
AND HAUFT.LANGUAGE = 'US'
AND HOUCF.CLASSIFICATION_CODE = 'DEPARTMENT'
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 TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PJFT.EFFECTIVE_START_DATE AND PJFT.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PJF.EFFECTIVE_START_DATE AND PJF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN HAUFT.EFFECTIVE_START_DATE AND HAUFT.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN HLA.EFFECTIVE_START_DATE AND HLA.EFFECTIVE_END_DATE
AND HAOUF.EFFECTIVE_START_DATE BETWEEN HOUCF.EFFECTIVE_START_DATE AND HOUCF.EFFECTIVE_END_DATE
ORDER BY UPPER(PAPF.PERSON_ID)
)
SELECT
U.USERNAME
,CASE U.SUSPENDED WHEN 'Y' THEN 'Inactive' ELSE 'Active' END ACTIVE_FLAG
,NVL(ETD.DISPLAY_NAME,(SELECT USER_DISPLAY_NAME FROM ASE_USER_VL AUV WHERE AUV.USER_GUID = U.USER_GUID FETCH FIRST 1 ROWS ONLY)) DISPLAY_NAME
,ETD.PERSON_NUMBER
,ETD.FIRST_NAME
,ETD.LAST_NAME
,ETD.MAX_ASSIGNMENT_NUMBER LAST_ASSIGNMENT_NUMBER
,ETD.MAX_TERMINATION_DATE LAST_TERMINATION_DATE
,EA.JOB_NAME
,EA.JOB_LEVEL
,EA.DEPARTMENT
,U.USER_ID
,U.PERSON_ID
,U.PARTY_ID
FROM PER_USERS U
LEFT OUTER JOIN EMP_TERM ETD
ON U.PERSON_ID = ETD.PERSON_ID
LEFT OUTER JOIN EMP_ASSIGNMENT EA
ON u.PERSON_ID = EA.PERSON_ID
WHERE U.USER_ID IN (SELECT USER_ID FROM PER_USER_ROLES WHERE ACTIVE_FLAG='Y')
ORDER BY UPPER(ETD.LAST_NAME),UPPER(ETD.FIRST_NAME),UPPER(U.USERNAME)Sample Output:
Trending Content
