Get the newest active profile for an employee by returning the first row of a sub-query which will grab all employee profiles for provided employee_id.
SELECT *
FROM
(SELECT unit_id,
dept,
last_name,
first_name,
pos_code,
title,
pos_strt_date
from EMPLOYEE_PROFILE
WHERE EMPLOYEE_ID = 000000 --replace with EMPLOYEE_ID
AND mss_unit_term_dt IS NULL
ORDER BY POS_STRT_DATE DESC
)
WHERE rownum = 1;