How to Find Active Project Manager and Project Controller based on the Oracle Projects

How to Find Active Project Manager based on the Oracle Projects

Select distinct ppa.project_id,ppa.segment1 Project_No,

ppa.name project_name,pr.name PM_name,

ppp.start_date_active,ppp.end_date_active

,ppp.project_role_type,pap.email_address,pap.employee_number

from PA_PROJECTS_all PPA,

PA_PROJECT_PLAYERS PPP,

PA_RESOURCES PR,

PA_PROJECT_OPTIONS PPO,

PER_ALL_PEOPLE_F PAP

where 1=1

and ppa.project_id = ppp.project_id

and ppp.resource_id = pr.resource_id

and pap.person_id = ppp.person_id

and (ppp.end_date_active is  null

or ppp.end_date_active > sysdate)

and ppp.project_role_type = 'PROJECT MANAGER'

and pap.business_group_id = '102'

and ppa.segment1 in ( '401012-01914','401012-01915')

How to Find Active Project Manager for All Projects:

Select distinct ppa.project_id,ppa.segment1 Project_No,

ppa.name project_name,pr.name PM_name,

ppp.start_date_active,ppp.end_date_active

,ppp.project_role_type,pap.email_address,pap.employee_number

from PA_PROJECTS_all PPA,

PA_PROJECT_PLAYERS PPP,

PA_RESOURCES PR,

PA_PROJECT_OPTIONS PPO,

PER_ALL_PEOPLE_F PAP

where 1=1

and ppa.project_id = ppp.project_id

and ppp.resource_id = pr.resource_id

and pap.person_id = ppp.person_id

and (ppp.end_date_active is  null

or ppp.end_date_active > sysdate)

and ppp.project_role_type = 'PROJECT MANAGER'

and pap.business_group_id = '102'

How to Find Active Project Controller for All Projects:

select distinct ppa.project_id,ppa.segment1 Project_num,
papf.full_name,haou.name Org_name,hou.name OU_Name 
from 
pa_projects_all ppa,
pa_project_role_types_vl pprt,
PA_PROJECT_PLAYERS ppp,
per_all_people_f papf,
hr_all_organization_units haou,
hr_operating_units hou
where 1=1
and ppa.project_id = ppp.project_id
and pprt.project_role_type = ppp.project_role_type
and ppp.person_id = papf.person_id
and ppa.carrying_out_organization_id = haou.organization_id
and ppa.org_id = hou.organization_id
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and trunc(sysdate) between ppp.start_date_active and nvl(ppp.end_date_active,'31-dec-4712')
and ppa.org_id in ('106','31211','39991','14010')
and ppp.project_role_type = '7001'
and ppa.project_status_code = 'APPROVED'
order by ppa.segment1;