MDS Test Project Key Members

SELECT distinct ppa.segment1 project_number
     , ppa.name project_name
     , papf.full_name
     , papf.employee_number
     , pprtt.meaning role
     , 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
     , ppa.project_type
     , pps.project_status_name project_status
     , 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 pprtt.language = 'US'
   AND 1 = 1
   --AND ppa.segment1 = :projectnum;
   --AND(:projectnum IS NULL OR ppa.segment1 LIKE :projectnum)
   --Order by ppa.segment1;
 
 
   --AND ppa.segment1 = :projectnum;
   AND(:projectnum IS NULL OR ppa.segment1 LIKE :projectnum)
   Order by ppa.segment1