Cacher is the code snippet organizer for pro developers

SELECT p2.employee_id,
  p2.last_name,
  p2.first_name,
  p2.unit_id,
  p2.pos_code,
  p2.title,
  n1.jobtypeid,
  n1.jobtype
FROM
  ( SELECT DISTINCT p1.employee_id,
    p1.last_name,
    p1.first_name,
    p1.unit_id,
    p1.pos_code,
    p1.title
  FROM EMPLOYEE_PROFILE p1
  WHERE p1.dept           IN (#getDirectorServiceAreas#)
  AND p1.mss_unit_term_dt IS NULL
  AND p1.pos_code         IN
    ( SELECT DISTINCT jobcode
    FROM NPDMGR.NPD_JOB@NPD_LINK
    WHERE jobtypeid IN (1,2,3,4,7,12)
    )
  ) p2
JOIN
  (SELECT DISTINCT jobtypeid, jobtype, jobcode FROM NPDMGR.NPD_JOB@NPD_LINK
  ) n1
ON p2.POS_CODE = n1.JOBCODE
ORDER BY n1.jobtypeid ASC,
  p2.POS_CODE DESC,
  p2.LAST_NAME ASC;