GMS_AWARD_PI_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 D_PERSON_ID PERSON_NUMBER

Query

SQL_Statement

select PI.AWARD_ID as ID, PI.PERSONNEL_ID ACTIVE_PI_ID, PI.START_DATE, PI.END_DATE, PERSONNAMEDPEO.PERSON_ID, PERSONNAMEDPEO.DISPLAY_NAME as PI_NAME, PERSONPEO.PERSON_ID as P_PERSON_ID, PERSONDPEO.PERSON_ID as D_PERSON_ID, PERSONDPEO.PERSON_NUMBER from (select a.id, a.AWARD_ID, a.START_DATE, a.END_DATE, a.PERSONNEL_ID from FUSION.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 FUSION.GMS_AWARD_HEADERS_B AWD_PER, (select START_DATE, AWARD_ID as AWARD_ID from FUSION.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 FUSION.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 FUSION.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_ALL_PEOPLE_F PERSONDPEO, PER_PERSONS PERSONPEO where PI.PERSONNEL_ID = PERSONNAMEDPEO.PERSON_ID and PI.PERSONNEL_ID = PERSONDPEO.PERSON_ID and (PERSONDPEO.PERSON_ID = PERSONPEO.PERSON_ID and TRUNC(GREATEST(PI.START_DATE, PERSONPEO.START_DATE)) between TRUNC(PERSONDPEO.EFFECTIVE_START_DATE) and TRUNC(PERSONDPEO.EFFECTIVE_END_DATE)) and TRUNC(GREATEST(sysdate, PERSONPEO.START_DATE)) between TRUNC(PERSONNAMEDPEO.EFFECTIVE_START_DATE) and TRUNC(PERSONNAMEDPEO.EFFECTIVE_END_DATE)