VIEW: APPS.IGS_RE_CANDIDATURE_V

Source

View Text - Preformatted

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 (+)

View Text - HTML Formatted

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 (+)