GMS_AWARD_PI_NAME_V

Details

• Schema: FUSION

• Object owner: GMS

• Object type: VIEW

Columns

Name
ID ACTIVE_PI_ID START_DATE END_DATE PERSON_ID PI_NAME P_PERSON_ID

Query

SQL_Statement

select PI.AWARD_ID ID, PI.PERSONNEL_ID ACTIVE_PI_ID, PI.START_DATE, PI.END_DATE, PERSONNAMEDPEO.PERSON_ID, PERSONNAMEDPEO.DISPLAY_NAME PI_NAME, PERSONPEO.PERSON_ID P_PERSON_ID from (select a.id, a.AWARD_ID, a.START_DATE, a.END_DATE, a.PERSONNEL_ID from GMS_AWARD_PERSONNEL a, (select AWD_PER.id as id, ACT_PER.START_DATE as ACT_START_DATE, FUT_PER.START_DATE as FUT_START_DATE, PAS_PER.START_DATE as PAS_START_DATE, NVL(ACT_PER.START_DATE, (NVL(FUT_PER.START_DATE, NVL(PAS_PER.START_DATE, sysdate))))as FINAL_DATE from GMS_AWARD_HEADERS_B AWD_PER, (select START_DATE, AWARD_ID as AWARD_ID from GMS_AWARD_PERSONNEL where ROLE_ID = 2 and AWD_PROJECT_LNK_ID is null and TRUNC(sysdate) between TRUNC(START_DATE) and TRUNC(NVL(END_DATE, sysdate)) ) ACT_PER, (select min(START_DATE) as START_DATE, AWARD_ID as AWARD_ID from GMS_AWARD_PERSONNEL where ROLE_ID = 2 and AWD_PROJECT_LNK_ID is null and START_DATE > sysdate group by AWARD_ID ) FUT_PER, (select max(START_DATE) as START_DATE, AWARD_ID as AWARD_ID from GMS_AWARD_PERSONNEL where ROLE_ID = 2 and AWD_PROJECT_LNK_ID is null and END_DATE < sysdate group by AWARD_ID ) PAS_PER where AWD_PER.id = ACT_PER.AWARD_ID(+) and AWD_PER.id = FUT_PER.AWARD_ID(+) and AWD_PER.id = PAS_PER.AWARD_ID(+))PI_IN where a.AWARD_ID = PI_IN.id and a.START_DATE = PI_IN.FINAL_DATE and a.ROLE_ID = 2 and a.AWD_PROJECT_LNK_ID is null )PI, PER_PERSON_NAMES_F_V PERSONNAMEDPEO, PER_PERSONS PERSONPEO where PI.PERSONNEL_ID = PERSONPEO.PERSON_ID and PERSONNAMEDPEO.PERSON_ID = PERSONPEO.PERSON_ID and TRUNC(GREATEST(sysdate, PERSONPEO.START_DATE)) between TRUNC(PERSONNAMEDPEO.EFFECTIVE_START_DATE) and TRUNC(PERSONNAMEDPEO.EFFECTIVE_END_DATE)