[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
smaddali Modified cursor cur_del_rec to delete the unique record being inserted ,
identified by unique key fields for bug 2450449 , also modified call to htis cursor
bayadav 08-JAN-03 Modified Cur_person_id cursor to cehck for EBL_AMMENDED_RESULT first then EBL_RESULT
while fetching value for approved result
ayedubat 07-MAR-03 For the Bug,2824978 the column x_imported_date in Igs_uc_qual_dets_pkg.insert_row
call is populated with SYSDATE.
For the Bug, 2825034 the existing qualifications are deleted first and importing all
the qualifications in the UCAS Interface table again.
For the Bug, 2825118 a validation is added to check the parent existence of the
Grading Schema+Grade Combination before calling the TBH for insertion/updation.
(reverse chronological order - newest change first)
***************************************************************/
/* This is to pickup records from UCAS interface table which have atleast
one record in UCAS Interface Table,IGS_UC_APP_RESULTS */
CURSOR Cur_UCAS_interface IS
SELECT ROWID, Subject_id, year, sitting, Awarding_body,
external_ref, UPPER(TRIM(Exam_level)) exam_level, Title,
UPPER(TRIM(Subject_code)) subject_code, Imported
FROM IGS_UC_COM_EBL_SUBJ
WHERE subject_id IN ( SELECT DISTINCT subject_id FROM IGS_UC_APP_RESULTS ) ;
SELECT iua.app_no, pe.party_number person_number, iua.Oss_person_id person_id,
NVL(UPPER(TRIM(iuar.EBL_AMENDED_RESULT)),UPPER(TRIM(iuar.EBL_result))) ebl_result,
UPPER(TRIM(iuar.Claimed_result)) claimed_result
FROM IGS_UC_APP_RESULTS iuar,
IGS_UC_APPLICANTS iua,
HZ_PARTIES pe
WHERE iuar.Subject_id = l_subject_id
AND iuar.App_id = iua.App_id
AND iua.oss_person_id IS NOT NULL
AND pe.party_id = iua.oss_person_id ;
SELECT uqd.ROWID, uqd.qual_dets_id, uqd.person_id, uqd.exam_level, uqd.subject_code, uqd.year, uqd.sitting, uqd.awarding_body, uqd.approved_result
FROM IGS_UC_QUAL_DETS uqd
WHERE imported_flag = 'Y' ;
SELECT imported_flag
FROM igs_uc_qual_dets
WHERE person_id = l_person_id
AND Exam_level = l_exam_level
AND ((subject_code = l_subject_code) OR (subject_code IS NULL AND l_subject_code IS NULL))
AND ((year = l_year) OR (year IS NULL AND l_year IS NULL))
AND ((sitting = l_sitting) OR (sitting IS NULL AND l_sitting IS NULL))
AND ((awarding_body = l_awarding_body) OR (awarding_body IS NULL AND l_awarding_body IS NULL))
AND ( (approved_result = l_approved_result) OR (approved_result IS NULL AND l_approved_result IS NULL) ) ;
SELECT Map2 FROM Igs_he_code_map_val
WHERE Association_code = 'UCAS_OSS_AWD_BDY_ASSOC'
AND Map1 = l_awarding_body;
SELECT Map2 FROM Igs_he_code_map_val
WHERE Association_code = 'UCAS_OSS_SBJ_ASSOC'
AND Map1 = l_subject_code;
SELECT Map2 FROM Igs_he_code_map_val
WHERE Association_code = 'UCAS_OSS_AWD_ASSOC'
AND Map1 = l_exam_level;
SELECT ROWID, Qual_dets_id, Person_id, Exam_level, Subject_code, Year, Sitting, Awarding_body,
Grading_schema_cd, Version_number, Predicted_result, Approved_result, Claimed_result,
UCAS_tariff, Imported_flag, Imported_date
FROM igs_uc_qual_dets
WHERE person_id = l_person_id
AND Exam_level = l_exam_level
AND Subject_code = l_subject_code
AND Year = l_year
AND Sitting = l_sitting
AND Awarding_body = l_awarding_body;
SELECT grading_schema_cd , gs_version_number
FROM igs_ps_awd
WHERE award_cd = cp_award_cd ;
SELECT 'X'
FROM igs_av_stnd_unit_lvl_all
WHERE qual_dets_id = cp_qual_dets_id;
SELECT person_number
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
SELECT 'X'
FROM igs_uc_app_results apr
WHERE apr.app_no IN ( SELECT app_no
FROM igs_uc_applicants
WHERE oss_person_id = cp_person_id
)
AND apr.subject_id IN ( SELECT subject_id
FROM igs_uc_com_ebl_subj
WHERE subject_code IN ( SELECT msbj.map1
FROM igs_he_code_map_val msbj
WHERE msbj.association_code = 'UCAS_OSS_SBJ_ASSOC'
AND msbj.map2 = cp_subject_code )
)
AND apr.year = cp_year
AND apr.sitting = cp_sitting
AND apr.award_body IN ( SELECT mawb.map1
FROM igs_he_code_map_val mawb
WHERE mawb.association_code = 'UCAS_OSS_AWD_BDY_ASSOC'
AND mawb.map2 = cp_awarding_body
)
AND apr.exam_level IN ( SELECT mawd.map1
FROM igs_he_code_map_val mawd
WHERE mawd.association_code = 'UCAS_OSS_AWD_ASSOC'
AND mawd.map2 = cp_exam_level
)
AND ( (NVL(UPPER(TRIM(apr.ebl_amended_result)),UPPER(TRIM(apr.ebl_result))) = cp_approved_result)
OR (cp_approved_result IS NULL AND apr.ebl_amended_result IS NULL AND apr.ebl_result IS NULL)
);
l_records_updated NUMBER;
l_records_inserted NUMBER;
l_records_deleted NUMBER;
l_records_deleted := 0;
fnd_message.set_name('IGS','IGS_UC_QUAL_DETS_DELETE');
igs_uc_qual_dets_pkg.delete_row( x_rowid => cur_del_qual_dets_rec.ROWID );
l_records_deleted := l_records_deleted + 1;
/* getting the associated person id for inserting in to igs_uc_qual_dets table */
FOR J IN cur_person_id (I.subject_id) LOOP
BEGIN
l_msg_count := igs_ge_msg_stack.count_msg;
/* This means the record is manually entered so only the approved result should be updated */
OPEN cur_qual_dets (J.person_id, l_oss_exam_level, l_oss_subject_code, I.year, I.sitting, l_oss_awarding_body);
igs_uc_qual_dets_pkg.Update_row ( x_mode => 'R',
x_rowid => Cur_qual_dets_val.ROWID,
x_Qual_dets_id => Cur_qual_dets_val.Qual_dets_id,
x_Person_id => Cur_qual_dets_val.person_id,
x_Exam_level => Cur_qual_dets_val.Exam_level,
x_Subject_code => Cur_qual_dets_val.subject_code,
x_Year => Cur_qual_dets_val.year,
x_Sitting => Cur_qual_dets_val.sitting,
x_Awarding_body => Cur_qual_dets_val.awarding_body,
x_grading_schema_cd => Cur_qual_dets_val.grading_schema_cd,
x_version_number => Cur_qual_dets_val.version_number,
x_Predicted_result => Cur_qual_dets_val.predicted_result,
x_Approved_result => J.EBL_result,
x_Claimed_result => Cur_qual_dets_val.claimed_result,
x_UCAS_tariff => Cur_qual_dets_val.ucas_tariff,
x_Imported_flag => 'Y',
x_Imported_date => Cur_qual_dets_val.Imported_date );
l_records_updated := NVL(l_records_updated,0) + 1 ;
/* Inserting record in to igs_uc_qual_dets table thru TBH */
Igs_uc_qual_dets_pkg.Insert_row ( x_mode => 'R',
x_rowid => l_rowid,
x_Qual_dets_id => l_Qual_dets_id,
x_Person_id => J.person_id,
x_Exam_level => l_oss_exam_level,
x_Subject_code => l_oss_subject_code,
x_Year => I.year,
x_Sitting => I.sitting,
x_Awarding_body => l_oss_awarding_body,
x_grading_schema_cd => c_grad_sch_rec.grading_schema_cd ,
x_version_number => c_grad_sch_rec.gs_version_number,
x_Predicted_result => NULL,
x_Approved_result => J.EBL_result,
x_Claimed_result => J.claimed_result,
x_UCAS_tariff => NULL,
x_Imported_flag => 'Y',
x_Imported_date => TRUNC(SYSDATE) );
l_records_inserted := NVL(l_records_inserted ,0) + 1 ;
END LOOP; -- Insert/Update Igs_uc_qual_dets looop
fnd_message.set_token('REC_CNT', NVL(l_records_deleted,0));
fnd_message.set_token('REC_CNT', NVL(l_records_inserted,0));
fnd_message.set_token('REC_CNT', NVL(l_records_updated,0));
SELECT grading_schema_cd,
gs_version_number
FROM igs_ps_awd
WHERE award_cd = p_uc_qual_cur.exam_level
AND s_award_type IN ('ENTRYQUAL', 'COURSE');
SELECT 'X'
FROM igs_ps_fld_of_study
WHERE field_of_study = p_uc_qual_cur.subject_code;
SELECT 'X'
FROM hz_parties hp,
igs_pe_hz_parties ihp,
igs_or_org_inst_type oit,
igs_or_inst_stat ois
WHERE hp.party_id = ihp.party_id
AND ihp.oss_org_unit_cd = p_uc_qual_cur.awarding_body
AND hp.status = 'A'
AND ihp.oi_institution_status = ois.institution_status (+)
AND ihp.oi_institution_type = oit.institution_type (+)
AND ihp.inst_org_ind = 'I'
AND oit.system_inst_type IN ('POST-SECONDARY','OTHER');
SELECT 'X'
FROM igs_as_grd_sch_grade gsch,
igs_as_grd_schema ags
WHERE gsch.grading_schema_cd = ags.grading_schema_cd
AND gsch.version_number = ags.version_number
AND gsch.grading_schema_cd = cp_grd_sch
AND gsch.version_number = cp_grd_ver
AND ags.grading_schema_type = 'AWARD'
AND gsch.grade = NVL(cp_grade, gsch.grade);