PJF_PROJ_ALL_MEMBERS

Details

Schema: FUSION
Object owner: PJF
Object type: VIEW

Columns

Name
PROJECT_PARTY_ID PROJECT_ID RESOURCE_SOURCE_ID RESOURCE_SOURCE_NAME PROJECT_PARTY_TYPE PROJECT_ROLE_ID START_DATE_ACTIVE END_DATE_ACTIVE ORGANIZATION_ID ORGANIZATION_NAME PHONE_NUMBER EMAIL_ADDRESS JOB_TITLE

Name

PROJECT_PARTY_ID

PROJECT_ID

RESOURCE_SOURCE_ID

RESOURCE_SOURCE_NAME

PROJECT_PARTY_TYPE

PROJECT_ROLE_ID

START_DATE_ACTIVE

END_DATE_ACTIVE

ORGANIZATION_ID

ORGANIZATION_NAME

PHONE_NUMBER

EMAIL_ADDRESS

JOB_TITLE

Query

SQL_Statement
select PPP.PROJECT_PARTY_ID as PROJECT_PARTY_ID, PPP.PROJECT_ID as PROJECT_ID, PPP.RESOURCE_SOURCE_ID as RESOURCE_SOURCE_ID, PPN.FULL_NAME as RESOURCE_SOURCE_NAME, PPP.PROJECT_PARTY_TYPE as PROJECT_PARTY_TYPE, PPP.PROJECT_ROLE_ID as PROJECT_ROLE_ID, PPP.START_DATE_ACTIVE as START_DATE_ACTIVE, PPP.END_DATE_ACTIVE as END_DATE_ACTIVE, HRO.ORGANIZATION_ID as ORGANIZATION_ID, HRO.NAME as ORGANIZATION_NAME, PPH.PHONE_NUMBER as PHONE_NUMBER, PEA.EMAIL_ADDRESS as EMAIL_ADDRESS, PPJ.NAME as JOB_TITLE from PJF_PROJECT_PARTIES PPP join PER_ALL_PEOPLE_F PAP on PPP.RESOURCE_SOURCE_ID = PAP.PERSON_ID and GREATEST(SYSDATE, PPP.START_DATE_ACTIVE) between trunc(PAP.EFFECTIVE_START_DATE) and trunc(PAP.EFFECTIVE_END_DATE) join PER_PERSON_NAMES_F_V PPN on PAP.PERSON_ID = PPN.PERSON_ID and GREATEST(SYSDATE, PPP.START_DATE_ACTIVE) between trunc(PPN.EFFECTIVE_START_DATE) and trunc(PPN.EFFECTIVE_END_DATE) left outer join PER_PHONES_V PPH on PAP.PRIMARY_PHONE_ID = PPH.PHONE_ID left outer join PER_EMAIL_ADDRESSES_V PEA on PAP.PRIMARY_EMAIL_ID = PEA.EMAIL_ADDRESS_ID join PER_ALL_ASSIGNMENTS_F PAA on PPP.RESOURCE_SOURCE_ID = PAA.PERSON_ID and trunc(PPP.START_DATE_ACTIVE) between trunc(PAA.EFFECTIVE_START_DATE) and trunc(PAA.EFFECTIVE_END_DATE) join PER_DEPARTMENTS HRO on PAA.ORGANIZATION_ID = HRO.ORGANIZATION_ID and trunc(PPP.START_DATE_ACTIVE) between trunc(HRO.EFFECTIVE_START_DATE) and trunc(HRO.EFFECTIVE_END_DATE) left outer join PER_JOBS_F_VL PPJ on PAA.JOB_ID = PPJ.JOB_ID and trunc(PPP.START_DATE_ACTIVE) between trunc(PPJ.EFFECTIVE_START_DATE) and trunc(PPJ.EFFECTIVE_END_DATE) where PPP.PROJECT_PARTY_TYPE = 'IN' and PAA.PRIMARY_FLAG = 'Y' and PAA.ASSIGNMENT_TYPE in ('E' , 'C') and HRO.STATUS = 'A' and (PAA.ASSIGNMENT_STATUS_TYPE='ACTIVE' OR PAA.ASSIGNMENT_STATUS_TYPE='SUSPENDED') union all SELECT PPP.PROJECT_PARTY_ID as PROJECT_PARTY_ID, PPP.PROJECT_ID as PROJECT_ID, PPP.RESOURCE_SOURCE_ID as RESOURCE_SOURCE_ID, HZP.PARTY_NAME as RESOURCE_SOURCE_NAME, PPP.PROJECT_PARTY_TYPE as PROJECT_PARTY_TYPE, PPP.PROJECT_ROLE_ID as PROJECT_ROLE_ID, PPP.START_DATE_ACTIVE as START_DATE_ACTIVE, PPP.END_DATE_ACTIVE as END_DATE_ACTIVE, HZO.PARTY_ID as ORGANIZATION_ID, HZO.PARTY_NAME as ORGANIZATION_NAME, HZCP.PHONE_AREA_CODE \|\| decode( HZCP.PHONE_NUMBER,NULL,NULL, decode( HZCP.PHONE_AREA_CODE, NULL,HZCP.PHONE_NUMBER, '-'\|\| HZCP.PHONE_NUMBER)) \|\| decode( HZCP.PHONE_EXTENSION,NULL,NULL,'+'\|\|HZCP.PHONE_EXTENSION) as PHONE_NUMBER, HZP.EMAIL_ADDRESS as EMAIL_ADDRESS, NULL as JOB_TITLE from PJF_PROJECT_PARTIES PPP join HZ_RELATIONSHIPS HZR on PPP.RELATIONSHIP_ID = HZR.RELATIONSHIP_ID join HZ_PARTIES HZP on HZR.SUBJECT_ID = HZP.PARTY_ID join HZ_PARTIES HZO on HZR.OBJECT_ID = HZO.PARTY_ID left outer join HZ_CONTACT_POINTS HZCP on HZP.PRIMARY_PHONE_CONTACT_PT_ID = HZCP.CONTACT_POINT_ID where PPP.PROJECT_PARTY_TYPE in ('CC','PC','SC') and HZR.STATUS = 'A' and HZR.DIRECTIONAL_FLAG = 'F'

SQL_Statement

select PPP.PROJECT_PARTY_ID as PROJECT_PARTY_ID,

PPP.PROJECT_ID as PROJECT_ID,

PPP.RESOURCE_SOURCE_ID as RESOURCE_SOURCE_ID,

PPN.FULL_NAME as RESOURCE_SOURCE_NAME,

PPP.PROJECT_PARTY_TYPE as PROJECT_PARTY_TYPE,

PPP.PROJECT_ROLE_ID as PROJECT_ROLE_ID,

PPP.START_DATE_ACTIVE as START_DATE_ACTIVE,

PPP.END_DATE_ACTIVE as END_DATE_ACTIVE,

HRO.ORGANIZATION_ID as ORGANIZATION_ID,

HRO.NAME as ORGANIZATION_NAME,

PPH.PHONE_NUMBER as PHONE_NUMBER,

PEA.EMAIL_ADDRESS as EMAIL_ADDRESS,

PPJ.NAME as JOB_TITLE

from PJF_PROJECT_PARTIES PPP

join PER_ALL_PEOPLE_F PAP on PPP.RESOURCE_SOURCE_ID = PAP.PERSON_ID and GREATEST(SYSDATE, PPP.START_DATE_ACTIVE) between trunc(PAP.EFFECTIVE_START_DATE) and trunc(PAP.EFFECTIVE_END_DATE)

join PER_PERSON_NAMES_F_V PPN on PAP.PERSON_ID = PPN.PERSON_ID and GREATEST(SYSDATE, PPP.START_DATE_ACTIVE) between trunc(PPN.EFFECTIVE_START_DATE) and trunc(PPN.EFFECTIVE_END_DATE)

left outer join PER_PHONES_V PPH on PAP.PRIMARY_PHONE_ID = PPH.PHONE_ID

left outer join PER_EMAIL_ADDRESSES_V PEA on PAP.PRIMARY_EMAIL_ID = PEA.EMAIL_ADDRESS_ID

join PER_ALL_ASSIGNMENTS_F PAA on PPP.RESOURCE_SOURCE_ID = PAA.PERSON_ID and trunc(PPP.START_DATE_ACTIVE) between trunc(PAA.EFFECTIVE_START_DATE) and trunc(PAA.EFFECTIVE_END_DATE)

join PER_DEPARTMENTS HRO on PAA.ORGANIZATION_ID = HRO.ORGANIZATION_ID and trunc(PPP.START_DATE_ACTIVE) between trunc(HRO.EFFECTIVE_START_DATE) and trunc(HRO.EFFECTIVE_END_DATE)

left outer join PER_JOBS_F_VL PPJ on PAA.JOB_ID = PPJ.JOB_ID and trunc(PPP.START_DATE_ACTIVE) between trunc(PPJ.EFFECTIVE_START_DATE) and trunc(PPJ.EFFECTIVE_END_DATE)

where PPP.PROJECT_PARTY_TYPE = 'IN'

and PAA.PRIMARY_FLAG = 'Y'

and PAA.ASSIGNMENT_TYPE in ('E' , 'C')

and HRO.STATUS = 'A'

and (PAA.ASSIGNMENT_STATUS_TYPE='ACTIVE' OR PAA.ASSIGNMENT_STATUS_TYPE='SUSPENDED')

union all

SELECT PPP.PROJECT_PARTY_ID as PROJECT_PARTY_ID,

PPP.PROJECT_ID as PROJECT_ID,

PPP.RESOURCE_SOURCE_ID as RESOURCE_SOURCE_ID,

HZP.PARTY_NAME as RESOURCE_SOURCE_NAME,

PPP.PROJECT_PARTY_TYPE as PROJECT_PARTY_TYPE,

PPP.PROJECT_ROLE_ID as PROJECT_ROLE_ID,

PPP.START_DATE_ACTIVE as START_DATE_ACTIVE,

PPP.END_DATE_ACTIVE as END_DATE_ACTIVE,

HZO.PARTY_ID as ORGANIZATION_ID,

HZO.PARTY_NAME as ORGANIZATION_NAME,

HZCP.PHONE_AREA_CODE || decode( HZCP.PHONE_NUMBER,NULL,NULL,

decode( HZCP.PHONE_AREA_CODE, NULL,HZCP.PHONE_NUMBER,

'-'|| HZCP.PHONE_NUMBER))

|| decode(

HZCP.PHONE_EXTENSION,NULL,NULL,'+'||HZCP.PHONE_EXTENSION)

as PHONE_NUMBER,

HZP.EMAIL_ADDRESS as EMAIL_ADDRESS,

NULL as JOB_TITLE

from PJF_PROJECT_PARTIES PPP

join HZ_RELATIONSHIPS HZR on PPP.RELATIONSHIP_ID = HZR.RELATIONSHIP_ID

join HZ_PARTIES HZP on HZR.SUBJECT_ID = HZP.PARTY_ID

join HZ_PARTIES HZO on HZR.OBJECT_ID = HZO.PARTY_ID

left outer join HZ_CONTACT_POINTS HZCP on HZP.PRIMARY_PHONE_CONTACT_PT_ID = HZCP.CONTACT_POINT_ID

where PPP.PROJECT_PARTY_TYPE in ('CC','PC','SC')

and HZR.STATUS = 'A'

and HZR.DIRECTIONAL_FLAG = 'F'