Monday, May 8, 2017

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;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect