Employee Assignment Status (SQL Script)

Human Capital Management (HCM) Human Resources (HRMS)
1

This Employee Assignment Status query displays active employee assignment details, including organization, status, type, and employment category, for currently employed staff, helping HR teams track and manage workforce information.

#sqlquery

SELECT 
       --SQL4Fusion (An Orbit Analytics Project)
       --This query retrieves active employee details with assignment and organization info.
       PAPF.PERSON_NUMBER EMPLOYEE_NUMBER,
       PPNFV.FULL_NAME EMPLOYEE_NAME,
       PASM.ASSIGNMENT_NAME,
       PASM.EMPLOYEE_CATEGORY,
       PASM.EMPLOYMENT_CATEGORY,
       PASTT.USER_STATUS ASSIGNMENT_STATUS,
       HAOU.NAME ORGANIZATION_NAME
FROM 
       PER_ALL_PEOPLE_F PAPF,
       PER_ALL_ASSIGNMENTS_M PASM,
       HR_ALL_ORGANIZATION_UNITS HAOU,
       PER_PERSON_NAMES_F_V PPNFV,
       PER_ASSIGNMENT_STATUS_TYPES PAST,
       PER_ASSIGNMENT_STATUS_TYPES_TL PASTT
WHERE  
       PAPF.PERSON_ID = PPNFV.PERSON_ID
       AND PAPF.PERSON_ID = PASM.PERSON_ID
       AND PASM.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
       AND PASM.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID
       AND PAST.ASSIGNMENT_STATUS_TYPE_ID = PASTT.ASSIGNMENT_STATUS_TYPE_ID
       AND PASTT.SOURCE_LANG = 'US'
       AND TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
                               AND PAPF.EFFECTIVE_END_DATE
	   AND TRUNC (SYSDATE) BETWEEN PPNFV.EFFECTIVE_START_DATE
                               AND PPNFV.EFFECTIVE_END_DATE					   
       AND PASM.PRIMARY_ASSIGNMENT_FLAG = 'Y'
       AND PASM.ASSIGNMENT_TYPE = 'E'
       AND PASM.EFFECTIVE_LATEST_CHANGE = 'Y'
       AND TRUNC (SYSDATE) BETWEEN PASM.EFFECTIVE_START_DATE
                               AND PASM.EFFECTIVE_END_DATE
       AND TRUNC (SYSDATE) BETWEEN PAST.START_DATE
                               AND NVL (PAST.END_DATE, SYSDATE)