Query to find Project Manager Info of an Oracle Project

The below query can be useful when you are working in Oracle Projects and you need to find out the Project Manager Name and Email Address for a particular project.

 SELECT PE.FULL_NAME,
  PE.EMAIL_ADDRESS
FROM PA_PROJECT_PARTIES PPP,
  PA_PROJECTS_ALL PPA,
  PA_PROJECT_ROLE_TYPES PPRT,
  PER_ALL_PEOPLE_F PE,
  PA_PROJECT_ASSIGNMENTS PA,
  FND_USER U,
  (SELECT PJ.NAME JOB_NAME,
    HAOU.ORGANIZATION_ID ORG_ID,
    HAOU.NAME ORG_NAME,
    PAF.PERSON_ID,
    PAF.ASSIGNMENT_TYPE
  FROM PER_ALL_ASSIGNMENTS_F PAF,
    PER_JOBS PJ,
    HR_ALL_ORGANIZATION_UNITS HAOU
  WHERE TRUNC(SYSDATE) BETWEEN TRUNC(PAF.EFFECTIVE_START_DATE) AND TRUNC(PAF.EFFECTIVE_END_DATE)
  AND PAF.PRIMARY_FLAG     = 'Y'
  AND PAF.ORGANIZATION_ID  = HAOU.ORGANIZATION_ID
  AND NVL(PAF.JOB_ID, -99) = PJ.JOB_ID(+)
  ) PRD
WHERE PPA.PROJECT_ID                                              = :P_PROJECT_ID
AND DECODE(PA.ASSIGNMENT_ID,NULL,PPRT.MEANING,PA.ASSIGNMENT_NAME) = 'Project Manager'
AND PPP.PROJECT_ID                                                = PPA.PROJECT_ID
AND PPP.PROJECT_ROLE_ID                                           = PPRT.PROJECT_ROLE_ID
AND PPP.RESOURCE_SOURCE_ID                                        = PE.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(PPP.START_DATE_ACTIVE) AND NVL(TRUNC(PPP.END_DATE_ACTIVE),SYSDATE)
AND PE.EFFECTIVE_START_DATE =
  (SELECT MIN(PAPF.EFFECTIVE_START_DATE)
  FROM PER_ALL_PEOPLE_F PAPF
  WHERE PAPF.PERSON_ID         =PE.PERSON_ID
  AND PAPF.EFFECTIVE_END_DATE >= TRUNC(SYSDATE)
  )
AND PE.EFFECTIVE_END_DATE          >=TRUNC(SYSDATE)
AND PPP.PROJECT_PARTY_ID            = PA.PROJECT_PARTY_ID(+)
AND NVL(PRD.ASSIGNMENT_TYPE,'-99') IN ('C',DECODE(DECODE(PE.CURRENT_EMPLOYEE_FLAG,'Y','Y', DECODE(PE.CURRENT_NPW_FLAG,'Y','Y','N')),'Y','E', 'B'),'E', '-99')
AND PPP.RESOURCE_SOURCE_ID          = PRD.PERSON_ID(+)
AND U.EMPLOYEE_ID (+)               = PPP.RESOURCE_SOURCE_ID
AND PPP.OBJECT_TYPE                 = 'PA_PROJECTS'
AND ppp.object_id                   = ppa.project_id;

Oracle also has provided a seeded package called PA_PROJECT_PARTIES_UTILS that have couple of good functions that can be used to get Project Parties information.