Open Project with Terminated PMs or PDs

SELECT pou.NAME carrying_out_org_name
,ppa.NAME project_name
         ,ppa.segment1 project_num
,ppa.segment1 project_number
–,TO_CHAR(ppa.completion_date, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) completion_date
         –,ppa.description
         –,TO_CHAR(ppp.end_date_active, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) end_date_active
         ,TO_CHAR((SELECT MAX (a.effective_start_date)
   FROM per_all_people_f a, per_person_types_tl b, per_assignments_f c
  WHERE 1=1
    AND a.person_id=c.person_id(+)
AND b.person_type_id=c.person_type_id(+)
AND b.user_person_type=’Employee’
    AND a.person_id = pap.person_id), ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) effective_start_date
,TO_CHAR((pap.effective_start_date-1), ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) effective_end_date
,pap.person_number employee_number
,ppn.full_name       
         ,prt.project_role_name project_role
         ,pps.project_status_name
         –,ppt.project_type
         –,TO_CHAR(ppp.start_date_active, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) start_date_active
         –,ppa.attribute1 OMS
         — ,NULL auto_billing_frequency
     FROM pjf_projects_all_vl ppa, 
          pjf_project_types_vl ppt,
          hr_all_organization_units pou,         
          pjf_project_statuses_vl pps,
  pjf_project_parties ppp,
  –pa_role_controls prc,
          pjf_proj_role_types_v prt,         
          per_all_people_f pap,
  per_person_names_f ppn,
  per_person_types_tl pptl,
  per_assignments_f paf
    WHERE ppa.project_type_id = ppt.project_type_id
  AND ppa.project_status_code = pps.project_status_code
      AND ppa.carrying_out_organization_id = pou.organization_id         
      AND ppp.project_id = ppa.project_id
      AND ppp.project_role_id = prt.project_role_id
      AND ppp.resource_source_id = pap.person_id
  AND ppp.resource_source_id = ppn.person_id
  AND ppn.name_type = ‘GLOBAL’
  AND ppp.object_type = ‘PA_PROJECTS’
  –AND ppp.resource_type_id = 101
  –AND ppp.project_role_id = prc.project_role_id
      –AND prc.role_control_code = ‘ALLOW_AS_PROJ_MEMBER’
      AND TRUNC (SYSDATE) BETWEEN pap.effective_start_date
                              AND pap.effective_end_date
      AND UPPER(pps.project_status_name) IN (‘SUBMITTED’, ‘ACTIVE’)
  AND pap.person_id=paf.person_id(+)
  AND pptl.person_type_id=paf.person_type_id(+)
  AND pptl.user_person_type=’Ex-employee’
Order By pap.effective_end_date,ppn.full_name