Employee Salary Report (SQL Script)

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

This Employee Salary Report sql report displays salary details for employees, including the salary amount, effective date ranges. It links salary records with assignment and personal information to provide a comprehensive view of employee compensation history.

#sqlquery

 SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves the information about the Employee Salary Details.
  -- Organization information
  HAOU.NAME 				ORGANIZATION_UNIT_NAME,
  -- Employee Information
  PAPF.PERSON_NUMBER 		EMPLOYEE_NUMBER,
  PPNF.FULL_NAME 			EMPLOYEE_NAME,
  -- Assignment Information
  PAAM.ASSIGNMENT_NUMBER,
  PAAM.ASSIGNMENT_TYPE,
  PAAM.EMPLOYEE_CATEGORY,
  PAAM.EFFECTIVE_START_DATE ASSIGNMENT_START_DATE,
  PAAM.EFFECTIVE_END_DATE 	ASSIGNMENT_END_DATE,
  -- Job Information  
  HL.LOCATION_NAME 			WORK_LOCATION,
  PJ.JOB_CODE,
  PJ.NAME 					JOB_TITLE,
  CS.CURRENCY_CODE 			CURRENCY,
  -- Salary Information
  CS.SALARY_AMOUNT
FROM
  PER_ALL_PEOPLE_F 			PAPF,
  PER_ALL_ASSIGNMENTS_M 	PAAM,
  PER_JOBS 					PJ,
  HR_LOCATIONS 				HL,
  HR_ALL_ORGANIZATION_UNITS HAOU,
  CMP_SALARY 				CS,
  PER_PERSON_NAMES_F_V 		PPNF
WHERE
  1 = 1
  AND PAPF.PERSON_ID 		= PAAM.PERSON_ID  
  AND PAAM.JOB_ID 			= PJ.JOB_ID
  AND PAAM.LOCATION_ID 		= HL.LOCATION_ID
  AND PAAM.BUSINESS_UNIT_ID = HAOU.ORGANIZATION_ID
  AND PAAM.ASSIGNMENT_ID 	= CS.ASSIGNMENT_ID
  AND PPNF.PERSON_ID 		= PAPF.PERSON_ID
  AND PPNF.NAME_TYPE 		= 'GLOBAL'
  AND CS.LAST_UPDATE_DATE BETWEEN CS.DATE_FROM AND CS.DATE_TO
  AND SYSDATE BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
  AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
ORDER BY  PAPF.PERSON_NUMBER