Restrict

Friday, May 13, 2016

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;