[Home] [Help]
28: (reverse chronological order - newest change first)
29: ***************************************************************/
30:
31: /* This is to pickup records from UCAS interface table which have atleast
32: one record in UCAS Interface Table,IGS_UC_APP_RESULTS */
33: CURSOR Cur_UCAS_interface IS
34: SELECT ROWID, Subject_id, year, sitting, Awarding_body,
35: external_ref, UPPER(TRIM(Exam_level)) exam_level, Title,
36: UPPER(TRIM(Subject_code)) subject_code, Imported
34: SELECT ROWID, Subject_id, year, sitting, Awarding_body,
35: external_ref, UPPER(TRIM(Exam_level)) exam_level, Title,
36: UPPER(TRIM(Subject_code)) subject_code, Imported
37: FROM IGS_UC_COM_EBL_SUBJ
38: WHERE subject_id IN ( SELECT DISTINCT subject_id FROM IGS_UC_APP_RESULTS ) ;
39:
40: /* This is to get the person id from the oss table for the corresponding person number */
41: --smaddali 27-jun-2002 added condition that oss_person_id should be not null for bug 2430139
42: --smaddali selecting new columns iua.app_no and pe.person_number for bug 2430139 ,to show in log file
40: /* This is to get the person id from the oss table for the corresponding person number */
41: --smaddali 27-jun-2002 added condition that oss_person_id should be not null for bug 2430139
42: --smaddali selecting new columns iua.app_no and pe.person_number for bug 2430139 ,to show in log file
43: --jbaber modified to use hz_parties rather than igs_pe_person for performance - bug 5210481
44: CURSOR Cur_person_id (l_subject_id igs_uc_app_results.subject_id%TYPE) IS
45: SELECT iua.app_no, pe.party_number person_number, iua.Oss_person_id person_id,
46: NVL(UPPER(TRIM(iuar.EBL_AMENDED_RESULT)),UPPER(TRIM(iuar.EBL_result))) ebl_result,
47: UPPER(TRIM(iuar.Claimed_result)) claimed_result
48: FROM IGS_UC_APP_RESULTS iuar,
44: CURSOR Cur_person_id (l_subject_id igs_uc_app_results.subject_id%TYPE) IS
45: SELECT iua.app_no, pe.party_number person_number, iua.Oss_person_id person_id,
46: NVL(UPPER(TRIM(iuar.EBL_AMENDED_RESULT)),UPPER(TRIM(iuar.EBL_result))) ebl_result,
47: UPPER(TRIM(iuar.Claimed_result)) claimed_result
48: FROM IGS_UC_APP_RESULTS iuar,
49: IGS_UC_APPLICANTS iua,
50: HZ_PARTIES pe
51: WHERE iuar.Subject_id = l_subject_id
52: AND iuar.App_id = iua.App_id
144: cp_awarding_body igs_uc_qual_dets.awarding_body%TYPE,
145: cp_exam_level igs_uc_qual_dets.exam_level%TYPE,
146: cp_approved_result igs_uc_qual_dets.approved_result%TYPE) IS
147: SELECT 'X'
148: FROM igs_uc_app_results apr
149: WHERE apr.app_no IN ( SELECT app_no
150: FROM igs_uc_applicants
151: WHERE oss_person_id = cp_person_id
152: )
232:
233: IF l_rec_found = 'X' THEN
234: --If advanced standing details exists then check it it is a Qualification which was sent in error by UCAS.
235: --To check whether the qualification in valid or not, verify the existence of Qualification record in the
236: --IGS_UC_APP_RESULTS table. If a record exists in App. Results table then consider it as a valid qualification.
237:
238: --Get the Person Number for the Person ID passed.
239: OPEN cur_per_no (cur_del_qual_dets_rec.person_id);
240: FETCH cur_per_no INTO l_person_number;
240: FETCH cur_per_no INTO l_person_number;
241: CLOSE cur_per_no;
242:
243: l_rec_found := NULL;
244: --Check if the record exists in IGS_UC_APP_RESULTS for the Qualification Details exists in the IGS_UC_QUAL_DETS table.
245: OPEN cur_qual_valid (cur_del_qual_dets_rec.person_id,
246: cur_del_qual_dets_rec.subject_code,
247: cur_del_qual_dets_rec.year,
248: cur_del_qual_dets_rec.sitting,