eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: PA_XC_PROJECT_PEOPLE_V

Product:	PA - Projects
Description:	View List of People for a Specified Project
Implementation/DBA Data:	Not implemented in this database

View Text

SELECT PRJ.PROJECT_ID PROJECT_ID
, PRJ.NAME PROJECT_NAME
, PRJ.SEGMENT1 PROJECT_NUMBER
, FM.MENU_NAME PROJECT_ROLE_TYPE_CODE
, FMT.USER_MENU_NAME PROJECT_ROLE_TYPE
, PTY1.PARTY_ID PERSON_ID
, PTY1.PARTY_NAME PERSON_NAME
, PTY2.PARTY_ID COMPANY_ID
, PTY2.PARTY_NAME COMPANY_NAME
, PP.START_DATE_ACTIVE START_DATE
, PP.END_DATE_ACTIVE END_DATE
, USR.EMAIL_ADDRESS EMAIL
, PP.OBJECT_ID
, PP.OBJECT_TYPE
, PRJ.NAME
, USR.USER_NAME USERNAME
, PP.ROWID PROWID
, PRJ.DESCRIPTION
, 'PERSON'
, HC1.PHONE_NUMBER
, PP.GRANT_ID
FROM PA_PROJECTS_ALL PRJ
, PA_PROJECT_PARTIES PP
, FND_USER USR
, HZ_PARTIES PTY1
, HZ_PARTIES PTY2
, HZ_PARTY_RELATIONSHIPS PTY1REL
, FND_MENUS FM
, FND_MENUS_TL FMT
, PA_PROJECT_ROLE_TYPES PPRT
, HZ_CONTACT_POINTS HC1
WHERE PRJ.PROJECT_ID = PP.PROJECT_ID
AND PP.RESOURCE_SOURCE_ID = PTY1.PARTY_ID
AND PTY1.PARTY_TYPE = 'PERSON'
AND PTY2.PARTY_TYPE = 'ORGANIZATION'
AND PTY1REL.SUBJECT_ID = PTY1.PARTY_ID
AND PTY1REL.OBJECT_ID = PTY2.PARTY_ID
AND PTY1REL.PARTY_RELATIONSHIP_TYPE = 'EMPLOYEE_OF'
AND PTY1.PARTY_ID = USR.CUSTOMER_ID(+)
AND PPRT.PROJECT_ROLE_TYPE = FM.MENU_NAME
AND FMT.MENU_ID = FM.MENU_ID
AND FMT.LANGUAGE = USERENV('LANG')
AND PP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID
AND PP.OBJECT_TYPE = 'PA_PROJECTS'
AND HC1.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND HC1.OWNER_TABLE_ID (+) = PTY1.PARTY_ID
AND HC1.CONTACT_POINT_TYPE (+)= 'PHONE'
AND HC1.PHONE_LINE_TYPE (+) = 'GEN' UNION SELECT PRJ.PROJECT_ID PROJECT_ID
, PRJ.NAME PROJECT_NAME
, PRJ.SEGMENT1 PROJECT_NUMBER
, FM.MENU_NAME PROJECT_ROLE_TYPE_CODE
, FMT.USER_MENU_NAME PROJECT_ROLE_TYPE
, PTY1.PARTY_ID PERSON_ID
, PTY1.PARTY_NAME PERSON_NAME
, -999 COMPANY_ID
, '' COMPANY_NAME
, PP.START_DATE_ACTIVE START_DATE
, PP.END_DATE_ACTIVE END_DATE
, '' EMAIL
, PP.OBJECT_ID
, PP.OBJECT_TYPE
, PRJ.NAME
, '' USERNAME
, PP.ROWID PROWID
, PRJ.DESCRIPTION
, 'GROUP'
, HC1.PHONE_NUMBER
, PP.GRANT_ID
FROM PA_PROJECTS_ALL PRJ
, PA_PROJECT_PARTIES PP
, HZ_PARTIES PTY1
, FND_MENUS FM
, FND_MENUS_TL FMT
, PA_PROJECT_ROLE_TYPES PPRT
, HZ_CONTACT_POINTS HC1
WHERE PRJ.PROJECT_ID = PP.PROJECT_ID
AND PP.RESOURCE_SOURCE_ID = PTY1.PARTY_ID
AND PTY1.PARTY_TYPE ='GROUP'
AND PP.OBJECT_TYPE = 'PA_PROJECTS'
AND PPRT.PROJECT_ROLE_TYPE = FM.MENU_NAME
AND FMT.MENU_ID = FM.MENU_ID
AND FMT.LANGUAGE = USERENV('LANG')
AND PP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID
AND HC1.OWNER_TABLE_NAME (+)= 'HZ_PARTIES'
AND HC1.OWNER_TABLE_ID (+)= PTY1.PARTY_ID
AND HC1.CONTACT_POINT_TYPE (+)= 'PHONE'
AND HC1.PHONE_LINE_TYPE (+) = 'GEN' UNION SELECT PRJ.PROJECT_ID PROJECT_ID
, PRJ.NAME PROJECT_NAME
, PRJ.SEGMENT1 PROJECT_NUMBER
, FM.MENU_NAME PROJECT_ROLE_TYPE_CODE
, FMT.USER_MENU_NAME PROJECT_ROLE_TYPE
, PTY1.PARTY_ID PERSON_ID
, PTY1.PARTY_NAME PERSON_NAME
, -999 COMPANY_ID
, '' COMPANY_NAME
, PP.START_DATE_ACTIVE START_DATE
, PP.END_DATE_ACTIVE END_DATE
, '' EMAIL
, PP.OBJECT_ID
, PP.OBJECT_TYPE
, PT.TASK_NAME
, '' USERNAME
, PP.ROWID PROWID
, PT.DESCRIPTION
, 'GROUP'
, HC1.PHONE_NUMBER
, PP.GRANT_ID
FROM PA_PROJECTS_ALL PRJ
, PA_PROJECT_PARTIES PP
, HZ_PARTIES PTY1
, FND_MENUS FM
, FND_MENUS_TL FMT
, PA_PROJECT_ROLE_TYPES PPRT
, HZ_CONTACT_POINTS HC1
, PA_TASKS PT
WHERE PRJ.PROJECT_ID = PP.PROJECT_ID
AND PP.RESOURCE_SOURCE_ID = PTY1.PARTY_ID
AND PTY1.PARTY_TYPE ='GROUP'
AND PP.OBJECT_TYPE = 'PA_TASKS'
AND PP.OBJECT_ID = PT.TASK_ID
AND PPRT.PROJECT_ROLE_TYPE = FM.MENU_NAME
AND FMT.MENU_ID = FM.MENU_ID
AND FMT.LANGUAGE = USERENV('LANG')
AND PP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID
AND HC1.OWNER_TABLE_NAME (+)= 'HZ_PARTIES'
AND HC1.OWNER_TABLE_ID (+)= PTY1.PARTY_ID
AND HC1.CONTACT_POINT_TYPE (+)= 'PHONE'
AND HC1.PHONE_LINE_TYPE (+) = 'GEN' UNION SELECT PRJ.PROJECT_ID PROJECT_ID
, PRJ.NAME PROJECT_NAME
, PRJ.SEGMENT1 PROJECT_NUMBER
, FM.MENU_NAME PROJECT_ROLE_TYPE_CODE
, FMT.USER_MENU_NAME PROJECT_ROLE_TYPE
, PTY1.PARTY_ID PERSON_ID
, PTY1.PARTY_NAME PERSON_NAME
, PTY2.PARTY_ID COMPANY_ID
, PTY2.PARTY_NAME COMPANY_NAME
, PP.START_DATE_ACTIVE START_DATE
, PP.END_DATE_ACTIVE END_DATE
, USR.EMAIL_ADDRESS EMAIL
, PP.OBJECT_ID
, PP.OBJECT_TYPE
, PT.TASK_NAME
, USR.USER_NAME USERNAME
, PP.ROWID PROWID
, PT.DESCRIPTION
, 'PERSON'
, HC1.PHONE_NUMBER
, PP.GRANT_ID
FROM PA_PROJECTS_ALL PRJ
, PA_PROJECT_PARTIES PP
, FND_USER USR
, HZ_PARTIES PTY1
, HZ_PARTIES PTY2
, HZ_PARTY_RELATIONSHIPS PTY1REL
, FND_MENUS FM
, FND_MENUS_TL FMT
, PA_PROJECT_ROLE_TYPES PPRT
, PA_TASKS PT
, HZ_CONTACT_POINTS HC1
WHERE PRJ.PROJECT_ID = PP.PROJECT_ID
AND PP.RESOURCE_SOURCE_ID = PTY1.PARTY_ID
AND PTY1.PARTY_TYPE = 'PERSON'
AND PTY2.PARTY_TYPE = 'ORGANIZATION'
AND PTY1REL.SUBJECT_ID = PTY1.PARTY_ID
AND PTY1REL.OBJECT_ID = PTY2.PARTY_ID
AND PTY1REL.PARTY_RELATIONSHIP_TYPE = 'EMPLOYEE_OF'
AND PTY1.PARTY_ID = USR.CUSTOMER_ID(+)
AND PPRT.PROJECT_ROLE_TYPE = FM.MENU_NAME
AND FMT.MENU_ID = FM.MENU_ID
AND FMT.LANGUAGE = USERENV('LANG')
AND PP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID
AND PP.OBJECT_TYPE = 'PA_TASKS'
AND PT.PROJECT_ID = PRJ.PROJECT_ID
AND PT.TASK_ID = PP.OBJECT_ID
AND HC1.OWNER_TABLE_NAME (+)= 'HZ_PARTIES'
AND HC1.OWNER_TABLE_ID (+)= PTY1.PARTY_ID
AND HC1.CONTACT_POINT_TYPE (+)= 'PHONE'
AND HC1.PHONE_LINE_TYPE (+) = 'GEN' UNION SELECT PRJ.PROJECT_ID PROJECT_ID
, PRJ.NAME PROJECT_NAME
, PRJ.SEGMENT1 PROJECT_NUMBER
, FM.MENU_NAME PROJECT_ROLE_TYPE_CODE
, FMT.USER_MENU_NAME PROJECT_ROLE_TYPE
, PTY1.PARTY_ID PERSON_ID
, PTY1.PARTY_NAME PERSON_NAME
, PTY2.PARTY_ID COMPANY_ID
, PTY2.PARTY_NAME COMPANY_NAME
, MIN(PT.START_DATE) START_DATE
, MAX(PT.COMPLETION_DATE) END_DATE
, USR.EMAIL_ADDRESS EMAIL
, PT.TASK_ID
, 'PA_TASKS'
, PT.TASK_NAME
, USR.USER_NAME USERNAME
, MAX(PT.ROWID) PROWID
, PT.DESCRIPTION
, 'PERSON'
, HC1.PHONE_NUMBER
, SYS_GUID()
FROM PA_PROJECTS_ALL PRJ
, FND_USER USR
, HZ_PARTIES PTY1
, HZ_PARTIES PTY2
, HZ_PARTY_RELATIONSHIPS PTY1REL
, PA_TASKS PT
, FND_MENUS FM
, FND_MENUS_TL FMT
, HZ_CONTACT_POINTS HC1
WHERE PRJ.PROJECT_ID = PT.PROJECT_ID
AND PT.TASK_MANAGER_PERSON_ID = PTY1.PARTY_ID
AND PTY1.PARTY_TYPE = 'PERSON'
AND PTY2.PARTY_TYPE = 'ORGANIZATION'
AND PTY1REL.SUBJECT_ID = PTY1.PARTY_ID
AND PTY1REL.OBJECT_ID = PTY2.PARTY_ID
AND PTY1REL.PARTY_RELATIONSHIP_TYPE = 'EMPLOYEE_OF'
AND PTY1.PARTY_ID = USR.CUSTOMER_ID(+)
AND FM.MENU_NAME = 'PA_EX_TASK_MGR'
AND FMT.MENU_ID = FM.MENU_ID
AND FMT.LANGUAGE = USERENV('LANG')
AND HC1.OWNER_TABLE_NAME (+)= 'HZ_PARTIES'
AND HC1.OWNER_TABLE_ID (+)= PTY1.PARTY_ID
AND HC1.CONTACT_POINT_TYPE (+) = 'PHONE'
AND HC1.PHONE_LINE_TYPE (+) = 'GEN' GROUP BY PRJ.PROJECT_ID
, PRJ.NAME
, PRJ.SEGMENT1
, FM.MENU_NAME
, FMT.USER_MENU_NAME
, PTY1.PARTY_ID
, PTY1.PARTY_NAME
, PTY2.PARTY_ID
, PTY2.PARTY_NAME
, USR.EMAIL_ADDRESS
, USR.USER_NAME
, PT.TASK_ID
, PT.TASK_NAME
, PT.DESCRIPTION
, HC1.PHONE_NUMBER

Columns

Name
PROJECT_ID
PROJECT_NAME
PROJECT_NUMBER
PROJECT_ROLE_TYPE_CODE
PROJECT_ROLE_TYPE
PROJECT_PERSON_ID
PROJECT_PERSON_NAME
COMPANY_ID
COMPANY_NAME
PERSON_START_DATE
PERSON_END_DATE
PERSON_EMAIL_ADDRESS
OBJECT_ID
OBJECT_TYPE
OBJECT_NAME
USER_NAME
P_ROWID
DESCRIPTION
PARTY_TYPE
PHONE_NUMBER
GRANT_ID