VIEW: APPS.IGSFV_ST_HESA_ADMISSION_DETAIL

Source

View Text - Preformatted

SELECT had.HESA_AD_DTL_ID HESA_AD_DTL_ID, had.PERSON_ID PERSON_ID, per.PARTY_NUMBER PERSON_NUMBER, per.PERSON_LAST_NAME PERSON_SURNAME, per.PERSON_FIRST_NAME PERSON_GIVEN_NAMES, perp.PERSON_NAME PERSON_FULL_NAME, had.NOMINATED_COURSE_CD NOMINATED_PROGRAM_CODE, ps.TITLE PROGRAM_TITLE, ps.SHORT_TITLE PROGRAM_SHORT_TITLE, ps.ABBREVIATION PROGRAM_ABBREVIATION, had.ADMISSION_APPL_NUMBER ADMISSION_APPLICATION_NUMBER, had.SEQUENCE_NUMBER SEQUENCE_NUMBER, had.OCCUPATION_CD OCCUPATION_CODE, (SELECT VALUE_DESCRIPTION FROM IGS_HE_CODE_VALUES WHERE CODE_TYPE = 'OSS_OCC' AND VALUE = had.OCCUPATION_CD) OCCUPATION_DESC, had.DOMICILE_CD DOMICILE_CODE, (SELECT VALUE_DESCRIPTION FROM IGS_HE_CODE_VALUES WHERE CODE_TYPE = 'OSS_DOM' AND VALUE = had.DOMICILE_CD) DOMICILE_DESC, had.SOCIAL_CLASS_CD SOCIAL_CLASS_CODE, (SELECT VALUE_DESCRIPTION FROM IGS_HE_CODE_VALUES WHERE CODE_TYPE = 'OSS_SOC' AND VALUE = had.SOCIAL_CLASS_CD) SOCIAL_CLASS_DESC, had.SPECIAL_STUDENT_CD SPECIAL_STUDENT_CODE, (SELECT VALUE_DESCRIPTION FROM IGS_HE_CODE_VALUES WHERE CODE_TYPE = 'OSS_SPEC_STUD' AND VALUE = had.SPECIAL_STUDENT_CD) SPECIAL_STUDENT_DESC, had.CREATION_DATE CREATION_DATE, had.CREATED_BY CREATED_BY, had.LAST_UPDATE_DATE LAST_UPDATE_DATE, had.LAST_UPDATED_BY LAST_UPDATED_BY FROM IGS_HE_AD_DTL_ALL had, HZ_PARTIES per, HZ_PERSON_PROFILES perp, IGS_PS_VER_ALL ps WHERE per.PARTY_ID = had.PERSON_ID AND per.PARTY_ID = perp.PARTY_ID AND perp.CONTENT_SOURCE_TYPE = 'USER_ENTERED' AND SYSDATE BETWEEN perp.EFFECTIVE_START_DATE AND NVL(perp.EFFECTIVE_END_DATE,SYSDATE) AND ps.COURSE_CD = had.NOMINATED_COURSE_CD AND ps.VERSION_NUMBER = (SELECT MAX(VERSION_NUMBER) FROM IGS_PS_VER_ALL WHERE COURSE_CD = had.NOMINATED_COURSE_CD) WITH READ ONLY

View Text - HTML Formatted

SELECT HAD.HESA_AD_DTL_ID HESA_AD_DTL_ID
, HAD.PERSON_ID PERSON_ID
, PER.PARTY_NUMBER PERSON_NUMBER
, PER.PERSON_LAST_NAME PERSON_SURNAME
, PER.PERSON_FIRST_NAME PERSON_GIVEN_NAMES
, PERP.PERSON_NAME PERSON_FULL_NAME
, HAD.NOMINATED_COURSE_CD NOMINATED_PROGRAM_CODE
, PS.TITLE PROGRAM_TITLE
, PS.SHORT_TITLE PROGRAM_SHORT_TITLE
, PS.ABBREVIATION PROGRAM_ABBREVIATION
, HAD.ADMISSION_APPL_NUMBER ADMISSION_APPLICATION_NUMBER
, HAD.SEQUENCE_NUMBER SEQUENCE_NUMBER
, HAD.OCCUPATION_CD OCCUPATION_CODE
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_OCC'
AND VALUE = HAD.OCCUPATION_CD) OCCUPATION_DESC
, HAD.DOMICILE_CD DOMICILE_CODE
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_DOM'
AND VALUE = HAD.DOMICILE_CD) DOMICILE_DESC
, HAD.SOCIAL_CLASS_CD SOCIAL_CLASS_CODE
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_SOC'
AND VALUE = HAD.SOCIAL_CLASS_CD) SOCIAL_CLASS_DESC
, HAD.SPECIAL_STUDENT_CD SPECIAL_STUDENT_CODE
, (SELECT VALUE_DESCRIPTION
FROM IGS_HE_CODE_VALUES
WHERE CODE_TYPE = 'OSS_SPEC_STUD'
AND VALUE = HAD.SPECIAL_STUDENT_CD) SPECIAL_STUDENT_DESC
, HAD.CREATION_DATE CREATION_DATE
, HAD.CREATED_BY CREATED_BY
, HAD.LAST_UPDATE_DATE LAST_UPDATE_DATE
, HAD.LAST_UPDATED_BY LAST_UPDATED_BY
FROM IGS_HE_AD_DTL_ALL HAD
, HZ_PARTIES PER
, HZ_PERSON_PROFILES PERP
, IGS_PS_VER_ALL PS
WHERE PER.PARTY_ID = HAD.PERSON_ID
AND PER.PARTY_ID = PERP.PARTY_ID
AND PERP.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
AND SYSDATE BETWEEN PERP.EFFECTIVE_START_DATE
AND NVL(PERP.EFFECTIVE_END_DATE
, SYSDATE)
AND PS.COURSE_CD = HAD.NOMINATED_COURSE_CD
AND PS.VERSION_NUMBER = (SELECT MAX(VERSION_NUMBER)
FROM IGS_PS_VER_ALL
WHERE COURSE_CD = HAD.NOMINATED_COURSE_CD) WITH READ ONLY