Project Key Members in oracle apps

SELECT ppa.segment1

     , ppa.project_type

     , pps.project_status_name status

     , ppa.name

     , papp.start_date_active member_from

     , papp.end_date_active member_to

     , pprtb.start_date_active role_from

     , pprtb.end_date_active role_to

     , haou.name hr_org

     , papf.full_name

     , papf.employee_number

     , pprtt.meaning role

     , pprtt.project_role_id

     , NVL (papf.email_address, 'n/a') email_address

     , NVL (pp.phone_number, 'n/a') telno_number

     , hla.description user_location

     , hla.location_code

  FROM pa.pa_project_parties papp

     , pa.pa_projects_all ppa

     , pa.pa_project_role_types_tl pprtt

     , pa.pa_project_role_types_b pprtb

     , pa.pa_project_statuses pps

     , hr.per_all_people_f papf

     , hr.per_all_assignments_f paaf

     , hr.hr_locations_all hla

     , hr.per_phones pp

     , hr.hr_all_organization_units haou

 WHERE ppa.project_id = papp.project_id

   AND papp.resource_source_id = papf.person_id

   AND PPRTT.PROJECT_ROLE_ID = PPRTB.PROJECT_ROLE_ID

   AND pprtt.project_role_id = papp.project_role_id

   AND ppa.project_status_code = pps.project_status_code

   AND papf.person_id = paaf.person_id

   AND paaf.location_id = hla.location_id(+)

   AND papf.person_id = pp.parent_id(+)

   AND ppa.carrying_out_organization_id = haou.organization_id

   AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date

   AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date

   AND SYSDATE BETWEEN papp.start_date_active AND NVL (papp.end_date_active, SYSDATE)

   AND NVL (pp.date_to, SYSDATE + 1) > SYSDATE

   AND NVL (pp.phone_type, 'W1') = 'W1'

   AND ppa.segment1 = :projectnum