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.

Advertisements

One Response to Query to find Project Manager Info of an Oracle Project

  1. Pingback: Query to find Project Manager Info of an Oracle Project | Dibyajyoti Koch:A Blog on Oracle Application | Kunwardeenesh's Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: