PJF_PROJ_TEAM_MEMBERS

Details

Schema: FUSION
Object owner: PJF
Object type: VIEW

Columns

Name
PROJECT_PARTY_ID PROJECT_ID RESOURCE_SOURCE_ID RESOURCE_SOURCE_NAME PROJECT_ROLE_ID START_DATE_ACTIVE END_DATE_ACTIVE

Name

PROJECT_PARTY_ID

PROJECT_ID

RESOURCE_SOURCE_ID

RESOURCE_SOURCE_NAME

PROJECT_ROLE_ID

START_DATE_ACTIVE

END_DATE_ACTIVE

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_Role_Id AS Project_Role_Id, Ppp.Start_Date_Active, PPP.END_DATE_ACTIVE from PJF_PROJECT_PARTIES PPP join PER_PERSON_NAMES_F_V PPN on PPP.RESOURCE_SOURCE_ID = PPN.PERSON_ID and trunc(PPP.START_DATE_ACTIVE) between trunc(PPN.EFFECTIVE_START_DATE) and trunc(PPN.EFFECTIVE_END_DATE) 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) 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_ROLE_ID as PROJECT_ROLE_ID, PPP.START_DATE_ACTIVE as START_DATE_ACTIVE, PPP.END_DATE_ACTIVE as END_DATE_ACTIVE 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 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_Role_Id AS Project_Role_Id,

Ppp.Start_Date_Active,

PPP.END_DATE_ACTIVE

from PJF_PROJECT_PARTIES PPP

join PER_PERSON_NAMES_F_V PPN on PPP.RESOURCE_SOURCE_ID = PPN.PERSON_ID and trunc(PPP.START_DATE_ACTIVE) between trunc(PPN.EFFECTIVE_START_DATE) and trunc(PPN.EFFECTIVE_END_DATE)

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)

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_ROLE_ID as PROJECT_ROLE_ID,

PPP.START_DATE_ACTIVE as START_DATE_ACTIVE,

PPP.END_DATE_ACTIVE as END_DATE_ACTIVE

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

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

and HZR.STATUS = 'A'

And Hzr.Directional_Flag = 'F'