• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:August 23, 2021
  • Reading time:5 mins read
You are currently viewing SQL Query to get the User status and assignment information
SQL Query to get the User status and assignment information

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:

image 17 1024x572 - SQL Query to get the User status and assignment information
If you like the content, please follow us on LinkedInFacebook, and Twitter to get updated with the latest content.