Employee Position Details (SQL Script)

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

This Employee Position Details sql report provides details of active employees with primary assignments, including their position titles, position effective dates, and organizational affiliations. It filters out non-standard assignments (ET, CT, PT) and ensures all data is current and reflects the most recent effective changes.

#sqlquery

SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves the Employee Position Details
  HAOU.NAME 				ORGANIZATION_NAME,
  PAPF.PERSON_NUMBER 		EMPLOYEE_NUMBER,
  PPNF.FULL_NAME 			EMPLOYEE_NAME,
  PAAF.ASSIGNMENT_NUMBER,
  PAAF.ASSIGNMENT_STATUS_TYPE,
  HAPF.NAME 				POSITION_NAME,
  HAPF.EFFECTIVE_START_DATE POSITION_START_DATE,
  HAPF.EFFECTIVE_END_DATE 	POSITION_END_DATE
FROM
  PER_PERSON_NAMES_F_V 		PPNF,
  PER_ALL_ASSIGNMENTS_M 	PAAF,
  HR_ALL_ORGANIZATION_UNITS HAOU,
  PER_ALL_PEOPLE_F 			PAPF,
  HR_ALL_POSITIONS_F_VL 	HAPF
WHERE PPNF.PERSON_ID 				= PAPF.PERSON_ID
  AND PAPF.PERSON_ID 				= PAAF.PERSON_ID
  AND PAAF.ORGANIZATION_ID 			= HAOU.ORGANIZATION_ID
  AND PAAF.POSITION_ID 				= HAPF.POSITION_ID
  AND PAAF.ASSIGNMENT_STATUS_TYPE 	= 'ACTIVE'
  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 HAPF.EFFECTIVE_START_DATE AND HAPF.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