Employee Directory Report

SELECT 
  e.EMPLOYEE_ID,
  e.FIRST_NAME,
  e.LAST_NAME,
  e.EMAIL,
  e.PHONE_NUMBER,
  e.HIRE_DATE,
  j.JOB_TITLE,
  d.DEPARTMENT_NAME,
  e.MANAGER_ID,
  e.SALARY,
  e.COMMISSION_PCT,
  l.CITY,
  l.STATE_PROVINCE,
  l.COUNTRY_ID
FROM EMPLOYEES e
JOIN JOBS j ON e.JOB_ID = j.JOB_ID
JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
LEFT JOIN LOCATIONS l ON d.LOCATION_ID = l.LOCATION_ID
WHERE e.ACTIVE_FLAG = 'Y'
  AND (:department_id IS NULL OR e.DEPARTMENT_ID = :department_id)
  AND (:hire_date_from IS NULL OR e.HIRE_DATE >= :hire_date_from)
  AND (:hire_date_to IS NULL OR e.HIRE_DATE <= :hire_date_to)
ORDER BY e.LAST_NAME, e.FIRST_NAME