[Home] [Help]
| Product: | IGS - Student System (Obsolete) |
|---|---|
| Description: | This view is of the candidature table. It includes a column called course_cd which is sca_course_cd if it exists, otherwise acai_nominated_course_cd. This view is to support form RESF32111. |
| Implementation/DBA Data: | Not implemented in this database |
SELECT CA.ROW_ID
, IGS_EN_GEN_003.ENRP_GET_ENCMBRD_IND(PBV.PERSON_ID) ENCUMBERED_IND
, DECODE(PP.DATE_OF_DEATH
, NULL
, NVL(PD.DECEASED_IND
, 'N')
, 'Y') DECEASED_IND
, PIT.API_PERSON_ID API_PERSON_ID
, CA.PERSON_ID
, PBV.PERSON_NUMBER PERSON_NUMBER
, PBV.FULL_NAME FULL_NAME
, CA.SEQUENCE_NUMBER
, CA.SCA_COURSE_CD
, CA.ACAI_ADMISSION_APPL_NUMBER
, CA.ACAI_NOMINATED_COURSE_CD
, CA.ACAI_SEQUENCE_NUMBER
, NVL(CA.SCA_COURSE_CD
, ACAI.COURSE_CD) CAV_COURSE_CD
, NVL(CA.SCA_COURSE_CD
, ACAI.COURSE_CD)||' '||NVL(SCA.VERSION_NUMBER
, ACAI.CRV_VERSION_NUMBER) CAV_COURSE_VERSION
, NVL(CA.ACAI_ADMISSION_APPL_NUMBER
, 1) CAV_ADMISSION_APPL_NUMBER
, NVL(CA.ACAI_NOMINATED_COURSE_CD
, 'NONE') CAV_NOMINATED_COURSE_CD
, NVL(CA.ACAI_SEQUENCE_NUMBER
, 1) CAV_ADM_SEQUENCE_NUMBER
, CA.ATTENDANCE_PERCENTAGE
, CA.GOVT_TYPE_OF_ACTIVITY_CD
, CA.MAX_SUBMISSION_DT
, CA.MIN_SUBMISSION_DT
, NVL(CA.MAX_SUBMISSION_DT
, IGS_RE_GEN_001.RESP_CLC_MAX_SBMSN( CA.PERSON_ID
, CA.SEQUENCE_NUMBER
, CA.SCA_COURSE_CD
, CA.ACAI_ADMISSION_APPL_NUMBER
, CA.ACAI_NOMINATED_COURSE_CD
, CA.ACAI_SEQUENCE_NUMBER
, CA.ATTENDANCE_PERCENTAGE
, NULL)) DERIVED_MAX_SUBMISSION_DT
, NVL(CA.MIN_SUBMISSION_DT
, IGS_RE_GEN_001.RESP_CLC_MIN_SBMSN( CA.PERSON_ID
, CA.SEQUENCE_NUMBER
, CA.SCA_COURSE_CD
, CA.ACAI_ADMISSION_APPL_NUMBER
, CA.ACAI_NOMINATED_COURSE_CD
, CA.ACAI_SEQUENCE_NUMBER
, CA.ATTENDANCE_PERCENTAGE
, NULL)) DERIVED_MIN_SUBMISSION_DT
, CA.CREATED_BY
, CA.CREATION_DATE
, CA.LAST_UPDATED_BY
, CA.LAST_UPDATE_DATE
, CA.LAST_UPDATE_LOGIN
, CA.RESEARCH_TOPIC
, CA.INDUSTRY_LINKS FROM IGS_RE_CANDIDATURE CA
, IGS_AD_PS_APPL_INST ACAI
, IGS_EN_STDNT_PS_ATT SCA
, IGS_PE_PERSON_BASE_V PBV
, IGS_PE_HZ_PARTIES PD
, HZ_PERSON_PROFILES PP
, IGS_PE_PERSON_ID_TYPE_V PIT
WHERE ACAI.PERSON_ID (+) = CA.PERSON_ID
AND ACAI.ADMISSION_APPL_NUMBER (+) = CA.ACAI_ADMISSION_APPL_NUMBER
AND ACAI.NOMINATED_COURSE_CD (+) = CA.ACAI_NOMINATED_COURSE_CD
AND ACAI.SEQUENCE_NUMBER (+) = CA.ACAI_SEQUENCE_NUMBER
AND SCA.PERSON_ID (+) = CA.PERSON_ID
AND SCA.COURSE_CD (+) = CA.SCA_COURSE_CD
AND PBV.PERSON_ID = CA.PERSON_ID
AND PBV.PERSON_ID = PP.PARTY_ID (+)
AND PP.CONTENT_SOURCE_TYPE(+) ='USER_ENTERED'
AND SYSDATE BETWEEN PP.EFFECTIVE_START_DATE
AND NVL(PP.EFFECTIVE_END_DATE
, SYSDATE)
AND PBV.PERSON_ID =PD.PARTY_ID (+)
AND PBV.PERSON_ID = PIT.PE_PERSON_ID (+)