Employee Organization Details (SQL Script)

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

This Employee Organization Details report retrieves a list of active employees holding primary assignments, excluding certain assignment types (ET, CT, PT), along with their personal numbers, names, assignment details, and the names of their respective organizations. The data is filtered to ensure it’s current and reflects the latest effective changes.

#sqlquery

SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves the information about the Employee Organization Details
  --Organization info
  HAOU.NAME 			ORGANIZATION_NAME,
  --Employee Info
  PAPF.PERSON_NUMBER 	EMPLOYEE_NUMBER,
  PPNF.FULL_NAME 		EMPLOYEE_NAME,
  -- Assignment info
  PAAF.ASSIGNMENT_NUMBER,
  PAAF.ASSIGNMENT_STATUS_TYPE  ASSIGNMENT_STATUS
FROM
  PER_PERSON_NAMES_F_V 		PPNF,
  PER_ALL_ASSIGNMENTS_M 	PAAF,
  HR_ALL_ORGANIZATION_UNITS HAOU,
  PER_ALL_PEOPLE_F 			PAPF
WHERE PPNF.PERSON_ID 				= PAPF.PERSON_ID
  AND PAPF.PERSON_ID 				= PAAF.PERSON_ID
 -- AND PAAF.ASSIGNMENT_STATUS_TYPE 	= 'ACTIVE'
  AND PAAF.ORGANIZATION_ID 			= HAOU.ORGANIZATION_ID
  AND PAAF.PRIMARY_FLAG 			= 'Y'
  AND PAAF.EFFECTIVE_LATEST_CHANGE 	= 'Y'
  AND PAAF.ASSIGNMENT_TYPE NOT IN ('ET', 'CT', 'PT')
  AND SYSDATE BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
  AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
  AND SYSDATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
ORDER BY  PAPF.PERSON_NUMBER