SELECT d.department_id "Department ID",
       d.department_name "Department Name",
       COUNT(e2.employee_id) "Number of Employees",
       SUBSTR(e.first_name,1,1)||'. '|| e.last_name "Manager Name",
       c.country_name "Location"
FROM oehr_departments d,
     oehr_employees e,
     oehr_locations l,
     oehr_countries c,
     oehr_employees e2
WHERE d.manager_id = e.employee_id
  AND d.location_id = l.location_id
  AND d.department_id = e2.department_id
  AND l.country_id = c.country_id
  AND INSTR(upper(d.department_name),upper(nvl(:P2_REPORT_SEARCH,d.department_name))) > 0
GROUP BY d.department_id, d.department_name,
SUBSTR(e.first_name,1,1)||'. '||e.last_name, c.country_name