Employee New Hires (SQL Script)

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

This Employee New Hires sql report provides details of employees in the organization. It includes the employee number, full name, hire date, organization, job, grade, and position. The report ensures the data is current by checking effective date ranges across all key HR tables and includes only those with active employment-type assignments. It helps HR teams monitor onboarding activity and analyze hiring trends across departments and positions.

#sqlquery

SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves active employee assignments with organization, job, grade, and position details
  HAOOU.NAME 			ORGANIZATION,
  PAPF.PERSON_NUMBER 	EMPLOYEE_NUMBER,
  PPNFV.FULL_NAME 		EMPLOYEE_NAME,
  PPOS.DATE_START 		HIRE_DATE,
  PGFVL.NAME 			GRADE,
  PJFVL.NAME 			JOB,
  HAPFVL.NAME 			POSITION
FROM
  PER_ALL_PEOPLE_F 			PAPF,
  PER_ALL_ASSIGNMENTS_M 	PAAM,
  PER_PERIODS_OF_SERVICE 	PPOS,
  PER_PERSON_TYPES 			PPT,
  PER_PERSON_TYPE_USAGES_M 	PPTUM,
  PER_PERSON_NAMES_F_V 		PPNFV,
  HR_ALL_ORGANIZATION_UNITS HAOOU,
  PER_GRADES_F_VL 			PGFVL,
  PER_JOBS_F_VL 			PJFVL,
  HR_ALL_POSITIONS_F_VL 	HAPFVL
WHERE
  1 = 1
  AND PAPF.PERSON_ID 				= PAAM.PERSON_ID
  AND PPOS.PERSON_ID 				= PAAM.PERSON_ID
  AND PAPF.PERSON_ID 				= PPNFV.PERSON_ID 
  AND PPNFV.NAME_TYPE 				= 'GLOBAL'
  AND PAAM.ORGANIZATION_ID 			= HAOOU.ORGANIZATION_ID
  AND PAAM.ASSIGNMENT_TYPE 			= 'E'
  AND PPTUM.PERSON_TYPE_ID 			= PAAM.PERSON_TYPE_ID
  AND PPTUM.PERSON_TYPE_ID 			= PPT.PERSON_TYPE_ID
  AND PPTUM.PERSON_ID 				= PAPF.PERSON_ID
  AND PAAM.GRADE_ID 				= PGFVL.GRADE_ID
  AND PAAM.JOB_ID 					= PJFVL.JOB_ID
  AND PAAM.POSITION_ID 				= HAPFVL.POSITION_ID
  AND PAAM.PERIOD_OF_SERVICE_ID 	= PPOS.PERIOD_OF_SERVICE_ID  
  AND PAAM.ASSIGNMENT_STATUS_TYPE 	= 'ACTIVE'
  AND PAAM.PRIMARY_FLAG           	= 'Y'
  AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
  AND SYSDATE BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
  AND SYSDATE BETWEEN PPNFV.EFFECTIVE_START_DATE AND PPNFV.EFFECTIVE_END_DATE
  AND SYSDATE BETWEEN PPTUM.EFFECTIVE_START_DATE AND PPTUM.EFFECTIVE_END_DATE 
  AND SYSDATE BETWEEN HAPFVL.EFFECTIVE_START_DATE AND HAPFVL.EFFECTIVE_END_DATE
  AND SYSDATE BETWEEN PGFVL.EFFECTIVE_START_DATE AND PGFVL.EFFECTIVE_END_DATE
  AND SYSDATE BETWEEN PJFVL.EFFECTIVE_START_DATE AND PJFVL.EFFECTIVE_END_DATE
ORDER BY  PAPF.PERSON_NUMBER