VIEW: APPS.IGS_PE_TYP_INSTANCES_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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