View: IGS_PE_TYP_INSTANCES_V

View Text

SELECT TI.ROWID ROW_ID
, TI.TYPE_INSTANCE_ID
, TI.PERSON_TYPE_CODE
, PT.DESCRIPTION PERSON_TYPE
, PT.SYSTEM_TYPE
, L4.MEANING ST_MEANING
, TI.PERSON_ID
, PE.PARTY_NUMBER
, TI.COURSE_CD
, TI.CC_VERSION_NUMBER
, PV.TITLE PROG_ATT_TITLE
, TI.FUNNEL_STATUS
, L3.MEANING FUNNEL_MEANING
, TI.START_DATE
, TI.END_DATE
, TI.CREATE_METHOD
, L1.MEANING CM_MEANING
, TI.ENDED_BY
, FU2.USER_NAME ENDED_BY_USER_NAME
, TI.END_METHOD
, L2.MEANING EM_MEANING
, PV.TITLE COURSE_TITLE
, TI.ADMISSION_APPL_NUMBER
, TI.SEQUENCE_NUMBER
, TI.NOMINATED_COURSE_CD
, TI.NCC_VERSION_NUMBER
, PV2.TITLE NOM_COURSE_TITLE
, TI.CREATED_BY
, FU1.USER_NAME CREATED_BY_USER_NAME
, TI.CREATION_DATE
, TI.LAST_UPDATED_BY
, TI.LAST_UPDATE_DATE
, TI.LAST_UPDATE_LOGIN
, TI.EMPLMNT_CATEGORY_CODE
FROM IGS_PE_TYP_INSTANCES_ALL TI
, IGS_LOOKUP_VALUES L1
, IGS_LOOKUP_VALUES L2
, IGS_LOOKUP_VALUES L3
, IGS_LOOKUP_VALUES L4
, FND_USER FU1
, FND_USER FU2
, IGS_PE_PERSON_TYPES PT
, IGS_PS_VER_ALL PV
, IGS_PS_VER_ALL PV2
, HZ_PARTIES PE
WHERE TI.CREATE_METHOD = L1.LOOKUP_CODE (+)
AND TI.PERSON_ID = PE.PARTY_ID
AND L1.LOOKUP_TYPE (+) = 'PERSON_TYPE_CREATE_METHOD'
AND TI.END_METHOD = L2.LOOKUP_CODE (+)
AND L2.LOOKUP_TYPE (+) = 'PERSON_TYPE_END_METHOD'
AND TI.FUNNEL_STATUS = L3.LOOKUP_CODE (+)
AND L3.LOOKUP_TYPE (+) = 'PERSON_TYPE_FUNNEL_STATUS'
AND TI.PERSON_TYPE_CODE = PT.PERSON_TYPE_CODE
AND PT.SYSTEM_TYPE = L4.LOOKUP_CODE
AND L4.LOOKUP_TYPE = 'SYSTEM_PERSON_TYPES'
AND TI.CREATED_BY = FU1.USER_ID
AND TI.ENDED_BY = FU2.USER_ID (+)
AND TI.COURSE_CD = PV.COURSE_CD (+)
AND TI.CC_VERSION_NUMBER = PV.VERSION_NUMBER (+)
AND TI.NOMINATED_COURSE_CD = PV2.COURSE_CD (+)
AND TI.NCC_VERSION_NUMBER = PV2.VERSION_NUMBER (+) UNION ALL SELECT CHARTOROWID(NULL) ROW_ID
, USG.PERSON_TYPE_USAGE_ID TYPE_INSTANCE_ID
, MAP.PERSON_TYPE_CODE
, PT.DESCRIPTION PERSON_TYPE
, PT.SYSTEM_TYPE
, LK1.MEANING ST_MEANING
, PEO.PARTY_ID PERSON_ID
, PE.PARTY_NUMBER PERSON_NUMBER
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, USG.EFFECTIVE_START_DATE START_DATE
, USG.EFFECTIVE_END_DATE END_DATE
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, USG.CREATED_BY
, FU1.USER_NAME CREATED_BY_USER_NAME
, USG.CREATION_DATE
, USG.LAST_UPDATED_BY
, USG.LAST_UPDATE_DATE
, USG.LAST_UPDATE_LOGIN
, NULL EMPLMNT_CATEGORY_CODE
FROM PER_PERSON_TYPE_USAGES_F USG
, PER_ALL_PEOPLE_F PEO
, IGS_LOOKUP_VALUES LK1
, HZ_PARTIES PE
, IGS_PE_PERSON_TYPES PT
, IGS_PE_PER_TYPE_MAP MAP
, FND_USER FU1
WHERE USG.PERSON_ID = PEO.PERSON_ID
AND PEO.PARTY_ID = PE.PARTY_ID
AND USG.PERSON_TYPE_ID = MAP.PER_PERSON_TYPE_ID
AND PT.PERSON_TYPE_CODE = MAP.PERSON_TYPE_CODE
AND LK1.LOOKUP_TYPE = 'SYSTEM_PERSON_TYPES'
AND LK1.LOOKUP_CODE = PT.SYSTEM_TYPE
AND USG.CREATED_BY = FU1.USER_ID
AND TRUNC(SYSDATE) BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE

Columns