[Home] [Help]
| Product: | IGS - Student System (Obsolete) |
|---|---|
| Description: | This will display the context of a research candidature. If the candidature is only linked to an applicant, then it will display Admission Application Instance details, otherwise the candidature must be linked to a student course attempt, a |
| Implementation/DBA Data: | Not implemented in this database |
SELECT ACAI.ROW_ID ROW_ID
, PE.API_PERSON_ID
, AA.PERSON_NUMBER
, ACAI.PERSON_ID
, ACAI.COURSE_CD
, ACAI.CRV_VERSION_NUMBER AS VERSION_NUMBER
, ACAI.COURSE_CD || ' ' || ACAI.CRV_VERSION_NUMBER AS COURSE_VERSION
, NULL AS SCA_COURSE_CD
, ACAI.ADMISSION_APPL_NUMBER ACAI_ADMISSION_APPL_NUMBER
, ACAI.NOMINATED_COURSE_CD ACAI_NOMINATED_COURSE_CD
, ACAI.SEQUENCE_NUMBER ACAI_SEQUENCE_NUMBER
, ACAI.LOCATION_CD
, ACAI.ATTENDANCE_MODE
, ACAI.ATTENDANCE_TYPE
, ACAI.CREATED_BY
, ACAI.CREATION_DATE
, ACAI.LAST_UPDATED_BY
, ACAI.LAST_UPDATE_DATE
, ACAI.LAST_UPDATE_LOGIN
, AA.APPLICATION_ID APPLICATION_ID
, AA.ALT_APPL_ID ALT_APPL_ID
FROM IGS_AD_PS_APPL_INST_SV ACAI
, IGS_AD_APPL_V AA
, IGS_PE_PERSON_V PE
WHERE PE.PERSON_ID = AA.PERSON_ID
AND ACAI.PERSON_ID = AA.PERSON_ID
AND ACAI.ADMISSION_APPL_NUMBER = AA.ADMISSION_APPL_NUMBER
AND (EXISTS (SELECT 'X'
FROM IGS_AD_PRCS_CAT_STEP APCS
WHERE APCS.ADMISSION_CAT = AA.ADMISSION_CAT
AND APCS.S_ADMISSION_PROCESS_TYPE = AA.S_ADMISSION_PROCESS_TYPE
AND APCS.S_ADMISSION_STEP_TYPE = 'RESEARCH'
AND APCS.STEP_GROUP_TYPE <> 'TRACK' ))
AND (NOT EXISTS (SELECT 'X'
FROM IGS_EN_STDNT_PS_ATT_SV SCA2 WHERE SCA2.PERSON_ID = ACAI.PERSON_ID AND SCA2.COURSE_CD = ACAI.COURSE_CD AND SCA2.ADM_ADMISSION_APPL_NUMBER = ACAI.ADMISSION_APPL_NUMBER AND SCA2.ADM_NOMINATED_COURSE_CD = ACAI.NOMINATED_COURSE_CD AND SCA2.ADM_SEQUENCE_NUMBER = ACAI.SEQUENCE_NUMBER)) UNION SELECT SCA.ROW_ID ROW_ID
, PE.API_PERSON_ID
, PE.PERSON_NUMBER
, SCA.PERSON_ID
, SCA.COURSE_CD
, SCA.VERSION_NUMBER
, SCA.COURSE_CD || ' ' || SCA.VERSION_NUMBER COURSE_VERSION
, SCA.COURSE_CD SCA_COURSE_CD
, NVL(SCA.ADM_ADMISSION_APPL_NUMBER
, 1) ACAI_ADMISSION_APPL_NUMBER
, NVL(SCA.ADM_NOMINATED_COURSE_CD
, 'NONE') ACAI_NOMINATED_COURSE_CD
, NVL(SCA.ADM_SEQUENCE_NUMBER
, 1) ACAI_SEQUENCE_NUMBER
, SCA.LOCATION_CD
, SCA.ATTENDANCE_MODE
, SCA.ATTENDANCE_TYPE
, SCA.CREATED_BY
, SCA.CREATION_DATE
, SCA.LAST_UPDATED_BY
, SCA.LAST_UPDATE_DATE
, SCA.LAST_UPDATE_LOGIN
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
FROM IGS_EN_STDNT_PS_ATT_SV SCA
, IGS_PE_PERSON_V PE
WHERE PE.PERSON_ID = SCA.PERSON_ID