Introduction
This report displays employee master information such as employee ID, name, person type, position, job grade, manager, contact details, employment status, location, and long-term leave details for HR administration and reporting.
SELECT
ppnf.TITLE,
ppnf.FIRST_NAME,
ppnf.MIDDLE_NAMES,
ppnf.LAST_NAME,
ppnf.FULL_NAME,
papf.person_id EMPLOYEE_ID,
ppt.SYSTEM_PERSON_TYPE PERSON_TYPE,
ppt.PERSON_TYPE_ID,
haouf.ORGANIZATION_ID,
hapf.POSITION_ID,
pj.NAME POSITION_NAME,
PJ.JOB_ID,
pgftl.NAME JOB_GRADE,
paam.ASSIGNMENT_STATUS_TYPE STATUS,
TO_CHAR(APRAE.START_DATE,’DD-MM-YY’) START_DATE_LONG_TERM_LEAVE,
TO_CHAR(APRAE.END_DATE,’DD-MM-YY’) END_DATE_LONG_TREM_LEAVE,
paam.LOCATION_ID,
To_Char(paam.EFFECTIVE_START_DATE,’DD-MM-YY’) START_DATE,
To_Char(paam.EFFECTIVE_END_DATE,’day’,’nls_date_language = AMERICAN’) LAST_WORKING_DAY,
To_char(ppos.ATTRIBUTE_DATE3,’DD-MM-YY’) ACCESS_CANCELLATION_DATE,
(MJR.FIRST_NAME||’ ‘||MJR.MIDDLE_NAMES||’ ‘||MJR.LAST_NAME) MANAGER,
to_char(pps.DATE_OF_BIRTH,’DD-MM-YY’) DATE_OF_BIRTH,
pea.EMAIL_ADDRESS EMAIL_ACCOUNT,
pp.PHONE_NUMBER PRIMARYPHONE,
PARTL.ACTION_REASON TERMINATION_REASON,
papef.PERSON_ID,
ppnf.FIRST_NAME PREFFERED_FIRST_NAME,
ppnf.LAST_NAME PREFFERED_LAST_NAME,
ppnf.DISPLAY_NAME PREFFERED_DISPLAY_NAME
FROM
per_person_names_f ppnf,
per_all_people_f papf,
PER_PERSON_TYPES ppt,
per_all_assignments_m paam,
HR_ALL_ORGANIZATION_UNITS_F haouf,
hr_all_positions_f hapf,
PER_JOBS pj,
PER_GRADES_F_TL pgftl,
per_grades_f pgf,
ANC_PER_ABS_ENTRIES APRAE,
per_periods_of_service ppos,
per_person_names_f MJR,
per_assignment_supervisors_f pasf,
PER_PERSONS pps,
PER_EMAIL_ADDRESSES pea,
PER_PHONES PP,
PER_ACTION_REASONS_TL PARTL,
PER_ACTION_REASONS_B PARB,
PER_ACTION_OCCURRENCES PAO,
per_all_people_f papef
WHERE 1=1
AND papf.PERSON_ID = ppnf.PERSON_ID
AND ppnf.name_type = ‘GLOBAL’
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 papf.PERSON_ID = paam.PERSON_ID
AND paam.PERSON_TYPE_ID = ppt.PERSON_TYPE_ID
AND haouf.organization_id = paam.organization_id
AND SYSDATE BETWEEN haouf.EFFECTIVE_START_DATE AND haouf.EFFECTIVE_END_DATE
AND paam.position_id = hapf.position_id(+)
AND SYSDATE BETWEEN hapf.EFFECTIVE_START_DATE(+) AND hapf.EFFECTIVE_END_DATE(+)
AND paam.job_id = pj.job_id
AND TRUNC(SYSDATE) BETWEEN PJ.EFFECTIVE_START_DATE(+) AND PJ.EFFECTIVE_END_DATE(+)
AND pgftl.grade_id = pgf.grade_id
AND pgftl.LANGUAGE = USERENV(‘LANG’)
AND pgf.grade_id = paam.grade_id
AND SYSDATE BETWEEN pgf.effective_start_date and pgf.effective_end_date
AND paam.ASSIGNMENT_TYPE IN (‘E’,’C’)
AND PAAM.PRIMARY_ASSIGNMENT_FLAG = ‘Y’
AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND PAAM.PRIMARY_FLAG =’Y’
AND APRAE.period_of_service_id = ppos.period_of_service_id
AND ppos.PERSON_ID = papf.PERSON_ID
AND pasf.manager_id = mjr.PERSON_ID
AND mjr.name_type = ‘GLOBAL’
AND TRUNC(SYSDATE) BETWEEN mjr.effective_start_date AND mjr.effective_end_date
AND TRUNC(SYSDATE) BETWEEN pasf.effective_start_date AND pasf.effective_end_date
AND papf.PERSON_ID = pasf.PERSON_ID
AND pasf.manager_type = ‘LINE_MANAGER’
AND pps.PERSON_ID = papf.PERSON_ID
AND Pea.EMAIL_ADDRESS_ID = Papf.PRIMARY_EMAIL_ID
AND PP.PHONE_ID = PAPF.PRIMARY_PHONE_ID
AND PARTL.ACTION_REASON_ID = PARB.ACTION_REASON_ID
AND PARB.ACTION_REASON_ID = PAO.ACTION_REASON_ID
AND PAO.ACTION_OCCURRENCE_ID = PAAM.ACTION_OCCURRENCE_ID
AND PARTL.LANGUAGE = USERENV(‘LANG’)
AND papef.PERSON_ID = ppnf.PERSON_ID
AND SYSDATE BETWEEN papef.EFFECTIVE_START_DATE AND papef.EFFECTIVE_END_DATE
AND papef.PERSON_ID = paam.PERSON_ID
Conclusion:
The Employee Information report provides a centralized view of key employee details, enabling HR and management to efficiently monitor workforce information. By consolidating personal, organizational, employment, and contact details into a single report, it supports informed decision-making, improves data accuracy, and streamlines day-to-day HR operations and employee record management.
