Query to get list of active employee having Marketing Manager Position

select distinct paa.assignment_number, ppx.first_name, ps.name
from per_all_assignments_f paa,
per_people_x ppx, per_all_positions ps
where paa.position_id = ps.position_id
and ps.name = 'Marketing Manager'               
and paa.business_group_id = 7671 -- use your business group id
and ppx.person_id = paa.person_id
and trunc(sysdate) between trunc(ppx.effective_start_date) and trunc(ppx.effective_end_date)                  
order by paa.assignment_number;