1. Tables and Views for Project Management
  2. PJF_PROJ_ALL_ORGS_V

PJF_PROJ_ALL_ORGS_V

Details

  • Schema: FUSION

  • Object owner: PJF

  • Object type: VIEW

Columns

Name

PROJECT_ID

PROJECT_PARTY_TYPE

ORGANIZATION_ID

ORGANIZATION_NAME

Query

SQL_Statement

select distinct PPP.PROJECT_ID as PROJECT_ID,

PPP.PROJECT_PARTY_TYPE as PROJECT_PARTY_TYPE,

HRO.ORGANIZATION_ID as ORGANIZATION_ID,

HRO.NAME as ORGANIZATION_NAME

from PJF_PROJECT_PARTIES PPP,

PER_ALL_ASSIGNMENTS_M_V PAA,

HR_ORGANIZATION_V HRO

where PPP.PROJECT_PARTY_TYPE = 'IN'

and PPP.RESOURCE_SOURCE_ID = PAA.PERSON_ID

and PAA.ORGANIZATION_ID = HRO.ORGANIZATION_ID

and PAA.PRIMARY_FLAG = 'Y'

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

and trunc(PPP.START_DATE_ACTIVE) between trunc(PAA.EFFECTIVE_START_DATE) and

trunc(PAA.EFFECTIVE_END_DATE)

and HRO.CLASSIFICATION_CODE = 'HR_ORG'

union all

SELECT PPP.PROJECT_ID as PROJECT_ID,

PPP.PROJECT_PARTY_TYPE as PROJECT_PARTY_TYPE,

HZP.PARTY_ID as ORGANIZATION_ID,

HZP.PARTY_NAME as ORGANIZATION_NAME

from PJF_PROJECT_PARTIES PPP,

HZ_PARTIES HZP

where PPP.PROJECT_PARTY_TYPE in ('CO','PO','SO')

and PPP.RESOURCE_SOURCE_ID = HZP.PARTY_ID