Query to find Project Manager Info of an Oracle 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) ANDNVL(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;