This Employee Driving License Info query displays active employees, assignment, driver license, job, and position details across organizations as of today, helping HR teams ensure compliance and maintain up-to-date employee records.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--Retrieve Active employee assignment, job, position, license, and location details.
PAPF.PERSON_NUMBER EMPLOYEE_NUMBER,
PPNF.FULL_NAME EMPLOYEE_NAME,
PAAM.ASSIGNMENT_NUMBER,
PAAM.ASSIGNMENT_NAME,
PAAM.ASSIGNMENT_STATUS_TYPE,
PJ.NAME JOB_NAME,
HAPFT.NAME POSITION_NAME,
HL.LOCATION_CODE || ' - ' || HL.DESCRIPTION LOCATION,
PDL.LICENSE_NUMBER,
PDL.DATE_FROM,
PDL.DATE_TO,
PDLT.LICENSE_TYPE,
HAOU.NAME ORGANIZATION_NAME
FROM
PER_PERSON_NAMES_F_V PPNF,
PER_ALL_ASSIGNMENTS_M PAAM,
HR_ALL_ORGANIZATION_UNITS HAOU,
PER_ALL_PEOPLE_F PAPF,
PER_DRIVERS_LICENSES PDL,
PER_DRIVERS_LICENSE_TYPES PDLT,
PER_JOBS PJ,
HR_ALL_POSITIONS_F_TL HAPFT,
HR_LOCATIONS HL
WHERE
PPNF.PERSON_ID = PAPF.PERSON_ID
AND PAPF.PERSON_ID = PAAM.PERSON_ID
AND PAPF.PERSON_ID = PDL.PERSON_ID(+)
AND PDL.DRIVERS_LICENSE_ID = PDLT.DRIVERS_LICENSE_ID(+)
AND PAAM.ASSIGNMENT_TYPE NOT IN ('ET', 'CT', 'PT')
AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND PAAM.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
AND PAAM.JOB_ID = PJ.JOB_ID
AND PAAM.POSITION_ID = HAPFT.POSITION_ID
AND PAAM.LOCATION_ID = HL.LOCATION_ID
AND SYSDATE BETWEEN PJ.EFFECTIVE_START_DATE AND PJ.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN HAPFT.EFFECTIVE_START_DATE AND HAPFT.EFFECTIVE_END_DATE
AND HAPFT.LANGUAGE = 'US'
AND PAAM.PRIMARY_FLAG = 'Y'
AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
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 PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE