1 PACKAGE BODY igs_uc_qual_dets_imp_pkg AS
2 /* $Header: IGSUC28B.pls 120.3 2006/05/29 04:18:27 jbaber noship $ */
3
4 PROCEDURE igs_uc_qual_dets_imp (errbuf OUT NOCOPY VARCHAR2,
5 retcode OUT NOCOPY NUMBER ) AS
6 /*************************************************************
7 Created By : rgopalan
8 Date Created On : 2002/02/22
9 Purpose : This procedure will import Qualification details
10 of an applicant from the UCAS INterface table to Sec/Ter
11 qualification details table
12 Know limitations, enhancements or remarks
13 Change History
14 Who When What
15 jbaber 25-MAY-06 Bug# 5210481 R12 Performance Repository
16 anwest 18-JAN-06 Bug# 4950285 R12 Disable OSS Mandate
17 rbezawad 09-DEC-02 Modified the process w.r.t. Bug 2639319.
18 smaddali Modified cursor cur_del_rec to delete the unique record being inserted ,
19 identified by unique key fields for bug 2450449 , also modified call to htis cursor
20 bayadav 08-JAN-03 Modified Cur_person_id cursor to cehck for EBL_AMMENDED_RESULT first then EBL_RESULT
21 while fetching value for approved result
22 ayedubat 07-MAR-03 For the Bug,2824978 the column x_imported_date in Igs_uc_qual_dets_pkg.insert_row
23 call is populated with SYSDATE.
24 For the Bug, 2825034 the existing qualifications are deleted first and importing all
25 the qualifications in the UCAS Interface table again.
26 For the Bug, 2825118 a validation is added to check the parent existence of the
27 Grading Schema+Grade Combination before calling the TBH for insertion/updation.
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
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
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,
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
53 AND iua.oss_person_id IS NOT NULL
54 AND pe.party_id = iua.oss_person_id ;
55
56 /* Fetch all the Qualifications Imported from UCAS for deletion */
57 CURSOR cur_del_qual_dets IS
58 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
59 FROM IGS_UC_QUAL_DETS uqd
60 WHERE imported_flag = 'Y' ;
61
62 /* this is to check whether the record is unique in igs_uc_qual_dets table */
63 --smaddali 27-jun-2002 added parameter l_approved_result as this field also is part of the unique key
64 -- also added NVL condition for fields which are nullable for bug 2430139
65 --smaddali 18-jul-02 interchanged position of parameters exam_level and subject_code for bug 2430139
66 CURSOR Cur_check_uniqueness ( l_person_id igs_uc_qual_dets.person_id%TYPE,
67 l_exam_level igs_uc_qual_dets.exam_level%TYPE,
68 l_subject_code igs_uc_qual_dets.subject_code%TYPE,
69 l_year igs_uc_com_ebl_subj.year%TYPE,
70 l_sitting igs_uc_com_ebl_subj.sitting%TYPE,
71 l_awarding_body igs_uc_com_ebl_subj.awarding_body%TYPE ,
72 l_approved_result igs_uc_qual_dets.approved_result%TYPE ) IS
73 SELECT imported_flag
74 FROM igs_uc_qual_dets
75 WHERE person_id = l_person_id
76 AND Exam_level = l_exam_level
77 AND ((subject_code = l_subject_code) OR (subject_code IS NULL AND l_subject_code IS NULL))
78 AND ((year = l_year) OR (year IS NULL AND l_year IS NULL))
79 AND ((sitting = l_sitting) OR (sitting IS NULL AND l_sitting IS NULL))
80 AND ((awarding_body = l_awarding_body) OR (awarding_body IS NULL AND l_awarding_body IS NULL))
81 AND ( (approved_result = l_approved_result) OR (approved_result IS NULL AND l_approved_result IS NULL) ) ;
82
83 /* this will pickup data from HESA table for Awarding body, based in the association code */
84 CURSOR Cur_Awarding_body (l_awarding_body igs_uc_com_ebl_subj.awarding_body%TYPE) IS
85 SELECT Map2 FROM Igs_he_code_map_val
86 WHERE Association_code = 'UCAS_OSS_AWD_BDY_ASSOC'
87 AND Map1 = l_awarding_body;
88
89 /* this will pickup data from HESA table for Subject code, based in the association code */
90 CURSOR Cur_subject_code (l_subject_code igs_uc_com_ebl_subj.subject_code%TYPE) IS
91 SELECT Map2 FROM Igs_he_code_map_val
92 WHERE Association_code = 'UCAS_OSS_SBJ_ASSOC'
93 AND Map1 = l_subject_code;
94
95 /* this will pickup data from HESA table for Exam_level, based in the association code */
96 CURSOR Cur_exam_level (l_exam_level igs_uc_com_ebl_subj.exam_level%TYPE) IS
97 SELECT Map2 FROM Igs_he_code_map_val
98 WHERE Association_code = 'UCAS_OSS_AWD_ASSOC'
99 AND Map1 = l_exam_level;
100
101 /* this is to get values from igs_uc_qual_dets table for updation when manually entered */
102 CURSOR Cur_qual_dets (l_person_id igs_uc_qual_dets.person_id%TYPE,
103 l_exam_level igs_uc_qual_dets.exam_level%TYPE,
104 l_subject_code igs_uc_qual_dets.subject_code%TYPE,
105 l_year igs_uc_com_ebl_subj.year%TYPE,
106 l_sitting igs_uc_com_ebl_subj.sitting%TYPE,
107 l_awarding_body igs_uc_com_ebl_subj.awarding_body%TYPE) IS
108 SELECT ROWID, Qual_dets_id, Person_id, Exam_level, Subject_code, Year, Sitting, Awarding_body,
109 Grading_schema_cd, Version_number, Predicted_result, Approved_result, Claimed_result,
110 UCAS_tariff, Imported_flag, Imported_date
111 FROM igs_uc_qual_dets
112 WHERE person_id = l_person_id
113 AND Exam_level = l_exam_level
114 AND Subject_code = l_subject_code
115 AND Year = l_year
116 AND Sitting = l_sitting
117 AND Awarding_body = l_awarding_body;
118 cur_qual_dets_val Cur_qual_dets%ROWTYPE;
119
120 --smaddali added cursor for bug 2409543 to get the grading schema cd associated to the qualification (award)
121 CURSOR c_grad_sch (cp_award_cd igs_ps_awd.award_cd%TYPE ) IS
122 SELECT grading_schema_cd , gs_version_number
123 FROM igs_ps_awd
124 WHERE award_cd = cp_award_cd ;
125 c_grad_sch_rec c_grad_sch%ROWTYPE ;
126
127 --Check if Advanced standing details exists for the qualification or not.
128 CURSOR cur_adv_std_exists(cp_qual_dets_id igs_uc_qual_dets.qual_dets_id%TYPE) IS
129 SELECT 'X'
130 FROM igs_av_stnd_unit_lvl_all
131 WHERE qual_dets_id = cp_qual_dets_id;
132
133 --Get the Person Number for the Person ID passed.
134 CURSOR cur_per_no(cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
135 SELECT person_number
136 FROM igs_pe_person_base_v
137 WHERE person_id = cp_person_id;
138
139 --To Identify whether the qualification is valid or not
140 CURSOR cur_qual_valid ( cp_person_id igs_uc_qual_dets.person_id%TYPE,
141 cp_subject_code igs_uc_qual_dets.subject_code%TYPE,
142 cp_year igs_uc_qual_dets.year%TYPE,
143 cp_sitting igs_uc_qual_dets.sitting%TYPE,
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 )
153 AND apr.subject_id IN ( SELECT subject_id
154 FROM igs_uc_com_ebl_subj
155 WHERE subject_code IN ( SELECT msbj.map1
156 FROM igs_he_code_map_val msbj
157 WHERE msbj.association_code = 'UCAS_OSS_SBJ_ASSOC'
158 AND msbj.map2 = cp_subject_code )
159 )
160 AND apr.year = cp_year
161 AND apr.sitting = cp_sitting
162 AND apr.award_body IN ( SELECT mawb.map1
163 FROM igs_he_code_map_val mawb
164 WHERE mawb.association_code = 'UCAS_OSS_AWD_BDY_ASSOC'
165 AND mawb.map2 = cp_awarding_body
166 )
167 AND apr.exam_level IN ( SELECT mawd.map1
168 FROM igs_he_code_map_val mawd
169 WHERE mawd.association_code = 'UCAS_OSS_AWD_ASSOC'
170 AND mawd.map2 = cp_exam_level
171 )
172 AND ( (NVL(UPPER(TRIM(apr.ebl_amended_result)),UPPER(TRIM(apr.ebl_result))) = cp_approved_result)
173 OR (cp_approved_result IS NULL AND apr.ebl_amended_result IS NULL AND apr.ebl_result IS NULL)
174 );
175
176 l_oss_awarding_body igs_uc_qual_dets.Awarding_body%TYPE;
177 l_oss_subject_code igs_uc_qual_dets.subject_code%TYPE;
178 l_oss_exam_level igs_uc_qual_dets.exam_level%TYPE;
179 l_person_id igs_uc_qual_dets.person_id%TYPE;
180 l_exam_level igs_uc_qual_dets.exam_level%TYPE;
181 l_subject_code igs_uc_qual_dets.subject_code%TYPE;
182 l_year igs_uc_com_ebl_subj.year%TYPE;
183 l_sitting igs_uc_com_ebl_subj.sitting%TYPE;
184 l_awarding_body igs_uc_com_ebl_subj.awarding_body%TYPE;
185 l_imported igs_uc_qual_dets.imported_flag%TYPE;
186 l_qual_dets_id igs_uc_qual_dets.Qual_dets_id%TYPE;
187 igs_uc_he_not_enabled_excep EXCEPTION;
188
189 l_records_updated NUMBER;
190 l_records_inserted NUMBER;
191 l_records_deleted NUMBER;
192 l_records_errored NUMBER;
193
194 l_msg_index NUMBER;
195 l_msg_count NUMBER;
196 l_mesg_data VARCHAR2(2000);
197 l_rowid VARCHAR2(25);
198 l_validation_status BOOLEAN ;
199 l_rec_found VARCHAR2(1);
200 l_person_number igs_pe_person_base_v.person_number%TYPE;
201
202 BEGIN
203
204 --anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
205 IGS_GE_GEN_003.SET_ORG_ID;
206
207 /* defaulting the err buf and err code so that if it ends with out error this value will be returned */
208 errbuf := NULL;
209 retcode := 0;
210 l_records_deleted := 0;
211
212 /* Checking whether the UK profile is enabled */
213 IF NOT (igs_uc_utils.is_ucas_hesa_enabled) THEN
214 RAISE igs_uc_he_not_enabled_excep; /* user defined exception */
215 END IF;
216
217 -- Displays log message "Deleting the existing qualifications that are imported from UCAS and not having Advanced standing details associated with it.".
218 fnd_message.set_name('IGS','IGS_UC_QUAL_DETS_DELETE');
219 fnd_file.put_line(fnd_file.log, fnd_message.get);
220 fnd_file.put_line(fnd_file.log,' ' );
221
222 -- Deleting All the existing Qualifications Records Imported from UCAS
223 -- and creating the Qualifications from the UCAS Interface Table.
224 -- This was enhanced for the bug,2825034
225 FOR cur_del_qual_dets_rec IN cur_del_qual_dets LOOP
226
227 l_rec_found := NULL;
228 --Check if Advanced standing details exists for the qualification or not.
229 OPEN cur_adv_std_exists(cur_del_qual_dets_rec.qual_dets_id);
230 FETCH cur_adv_std_exists INTO l_rec_found;
231 CLOSE cur_adv_std_exists ;
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;
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,
249 cur_del_qual_dets_rec.awarding_body,
250 cur_del_qual_dets_rec.exam_level,
251 cur_del_qual_dets_rec.approved_result );
252 FETCH cur_qual_valid INTO l_rec_found;
253 CLOSE cur_qual_valid;
254
255 --If record exists then consider the Qualification as a valid otherwise its an invalid qualification which was not resent by UCAS.
256 IF l_rec_found = 'X' THEN
257 --Valid Qualification. So display appropriate message asking user's mannual review.
258 fnd_message.set_name('IGS','IGS_UC_QUAL_ADV_DET_EXISTS');
259 fnd_message.set_token('PER_NO', l_person_number);
260 fnd_message.set_token('EXM_LVL', cur_del_qual_dets_rec.exam_level);
261 fnd_message.set_token('SUBJ_CD', cur_del_qual_dets_rec.subject_code);
262 fnd_message.set_token('YEAR', cur_del_qual_dets_rec.year);
263 fnd_message.set_token('SITTING', cur_del_qual_dets_rec.sitting);
264 fnd_message.set_token('AWD_BDY', cur_del_qual_dets_rec.awarding_body);
265 fnd_message.set_token('EBL_RSLT',cur_del_qual_dets_rec.approved_result);
266 fnd_file.put_line(fnd_file.log, fnd_message.get);
267 fnd_file.put_line(fnd_file.log,' ' );
268 ELSE
269 --Invalid Qualification. So display appropriate message asking user's mannual review.
270 fnd_message.set_name('IGS','IGS_UC_QUAL_DETS_INVALID');
271 fnd_message.set_token('PER_NO', l_person_number);
272 fnd_message.set_token('EXM_LVL', cur_del_qual_dets_rec.exam_level);
273 fnd_message.set_token('SUBJ_CD', cur_del_qual_dets_rec.subject_code);
274 fnd_message.set_token('YEAR', cur_del_qual_dets_rec.year);
275 fnd_message.set_token('SITTING', cur_del_qual_dets_rec.sitting);
276 fnd_message.set_token('AWD_BDY', cur_del_qual_dets_rec.awarding_body);
277 fnd_message.set_token('EBL_RSLT',cur_del_qual_dets_rec.approved_result);
278 fnd_file.put_line(fnd_file.log, fnd_message.get);
279 fnd_file.put_line(fnd_file.log,' ' );
280 END IF;
281
282 ELSE
283 --Delete the existing imported Qualification when advanced standing details doesn't exists.
284 igs_uc_qual_dets_pkg.delete_row( x_rowid => cur_del_qual_dets_rec.ROWID );
285 l_records_deleted := l_records_deleted + 1;
286 END IF;
287
288 END LOOP ;
289
290 -- Displays log message "Importing Qualification details data from UCAS Interface table to Qualification Details table".
291 fnd_file.put_line(fnd_file.log,' ' );
292 fnd_message.set_name('IGS','IGS_UC_IMP_QUAL_DETS');
293 fnd_file.put_line(fnd_file.log, fnd_message.get);
294 fnd_file.put_line(fnd_file.log,' ' );
295
296 /* Picking up records from igs_uc_com_ebl_subj table for Importing in to qual dets table */
297 FOR I IN cur_ucas_interface LOOP
298
299 -- initializing the local variables
300 l_oss_exam_level := NULL ;
301 l_oss_subject_code := NULL ;
302 l_oss_awarding_body := NULL ;
303
304 /* taking the map1 value for the exam level */
305 OPEN Cur_exam_level (I.Exam_level);
306 FETCH Cur_exam_level INTO l_oss_exam_level;
307 CLOSE Cur_exam_level;
308
309 /* taking the map1 value for the Subject code*/
310 OPEN Cur_Subject_code (I.subject_code);
311 FETCH Cur_Subject_code INTO l_oss_subject_code;
312 CLOSE Cur_Subject_code;
313
314 /* taking the map1 value for the Awarding body */
315 OPEN Cur_Awarding_body (I.Awarding_body);
316 FETCH Cur_Awarding_body INTO l_oss_awarding_body;
317 CLOSE Cur_Awarding_body;
318
319
320 /* getting the associated person id for inserting in to igs_uc_qual_dets table */
321 FOR J IN cur_person_id (I.subject_id) LOOP
322 BEGIN
323
324 l_msg_count := igs_ge_msg_stack.count_msg;
325 l_imported := NULL;
326
327 /* checking whether the same record combination exists */
328 --smaddali 27-jun-2002 added parameter j.ebl_result as this field also is part of te unique key for bug 2430139
329 OPEN Cur_check_uniqueness (J.person_id, l_oss_exam_level, l_oss_subject_code,
330 I.year, I.sitting, l_oss_awarding_body, J.ebl_result);
331 FETCH Cur_check_uniqueness INTO l_imported ;
332 CLOSE Cur_check_uniqueness;
333
334 IF l_imported = 'Y' THEN
335
336 --Diplays message "the record is already imported.
337 --so nothing should happen in this case i.e., not considered for import".
338 fnd_message.set_name('IGS','IGS_UC_QUAL_DETS_REC_EXISTS');
339 fnd_message.set_token('PER_NO', J.person_number);
340 fnd_message.set_token('APP_NO', J.app_no);
341 fnd_message.set_token('EXM_LVL', l_oss_exam_level);
342 fnd_message.set_token('SUBJ_CD', l_oss_subject_code);
343 fnd_message.set_token('YEAR', I.year);
344 fnd_message.set_token('SITTING', I.sitting);
345 fnd_message.set_token('AWD_BDY', l_oss_awarding_body);
346 fnd_message.set_token('EBL_RSLT',J.ebl_result);
347 fnd_file.put_line(fnd_file.log, fnd_message.get);
348
349 ELSIF l_imported = 'N' THEN
350
351 /* This means the record is manually entered so only the approved result should be updated */
352 OPEN cur_qual_dets (J.person_id, l_oss_exam_level, l_oss_subject_code, I.year, I.sitting, l_oss_awarding_body);
353 FETCH cur_qual_dets INTO Cur_qual_dets_val;
354 CLOSE cur_qual_dets;
355
356 l_rec_found := NULL;
357 --Check if Advanced standing details exists for the non-imported qualification or not.
358 OPEN cur_adv_std_exists(Cur_qual_dets_val.qual_dets_id);
359 FETCH cur_adv_std_exists INTO l_rec_found;
360 CLOSE cur_adv_std_exists ;
361
362 IF l_rec_found = 'X' THEN
363 --Display a message that qualification is not updated because of advanced standing details exist.
364 fnd_message.set_name('IGS','IGS_UC_ADV_STD_DET_EXISTS');
365 fnd_message.set_token('PER_NO', J.person_number);
366 fnd_message.set_token('APP_NO', J.app_no);
367 fnd_message.set_token('EXM_LVL', Cur_qual_dets_val.Exam_level);
368 fnd_message.set_token('SUBJ_CD', Cur_qual_dets_val.subject_code);
369 fnd_message.set_token('YEAR', Cur_qual_dets_val.year);
370 fnd_message.set_token('SITTING', Cur_qual_dets_val.sitting);
371 fnd_message.set_token('AWD_BDY', Cur_qual_dets_val.awarding_body);
372 fnd_message.set_token('EBL_RSLT',J.ebl_result);
373 fnd_file.put_line(fnd_file.log, fnd_message.get);
374 ELSE
375 --Diplays message "Updating record".
376 fnd_message.set_name('IGS','IGS_UC_UPD_QUAL_DETS_REC');
377 fnd_message.set_token('PER_NO', J.person_number);
378 fnd_message.set_token('APP_NO', J.app_no);
379 fnd_message.set_token('EXM_LVL', Cur_qual_dets_val.Exam_level);
380 fnd_message.set_token('SUBJ_CD', Cur_qual_dets_val.subject_code);
381 fnd_message.set_token('YEAR', Cur_qual_dets_val.year);
382 fnd_message.set_token('SITTING', Cur_qual_dets_val.sitting);
383 fnd_message.set_token('AWD_BDY', Cur_qual_dets_val.awarding_body);
384 fnd_message.set_token('GS_CD', Cur_qual_dets_val.grading_schema_cd);
385 fnd_message.set_token('GS_VER', Cur_qual_dets_val.version_number);
386 fnd_message.set_token('PRD_RSLT',Cur_qual_dets_val.predicted_result);
387 fnd_message.set_token('EBL_RSLT',J.ebl_result);
388 fnd_message.set_token('CLM_RSLT',Cur_qual_dets_val.claimed_result);
389 fnd_message.set_token('TARIFF', Cur_qual_dets_val.ucas_tariff);
390 fnd_file.put_line(fnd_file.log, fnd_message.get);
391
392 IF cur_qual_dets_val.grading_schema_cd IS NOT NULL
393 AND cur_qual_dets_val.version_number IS NOT NULL
394 AND j.ebl_result IS NOT NULL
395 AND NOT igs_as_grd_sch_grade_pkg.get_pk_for_validation (
396 UPPER(TRIM(c_grad_sch_rec.grading_schema_cd)),
397 c_grad_sch_rec.gs_version_number,
398 UPPER(TRIM(j.claimed_result)) ) THEN
399
400 fnd_message.set_name('IGS','IGS_UC_GRAD_NOT_FOUND');
401 fnd_message.set_token('GS_CD',cur_qual_dets_val.grading_schema_cd);
402 fnd_message.set_token('GS_VER',cur_qual_dets_val.version_number);
403 fnd_message.set_token('APP_RESULT',j.ebl_result);
404 fnd_file.put_line(fnd_file.log, fnd_message.get);
405 l_records_errored := NVL(l_records_errored ,0) + 1 ;
406
407 ELSE
408
409 igs_uc_qual_dets_pkg.Update_row ( x_mode => 'R',
410 x_rowid => Cur_qual_dets_val.ROWID,
411 x_Qual_dets_id => Cur_qual_dets_val.Qual_dets_id,
412 x_Person_id => Cur_qual_dets_val.person_id,
413 x_Exam_level => Cur_qual_dets_val.Exam_level,
414 x_Subject_code => Cur_qual_dets_val.subject_code,
415 x_Year => Cur_qual_dets_val.year,
416 x_Sitting => Cur_qual_dets_val.sitting,
417 x_Awarding_body => Cur_qual_dets_val.awarding_body,
418 x_grading_schema_cd => Cur_qual_dets_val.grading_schema_cd,
419 x_version_number => Cur_qual_dets_val.version_number,
420 x_Predicted_result => Cur_qual_dets_val.predicted_result,
421 x_Approved_result => J.EBL_result,
422 x_Claimed_result => Cur_qual_dets_val.claimed_result,
423 x_UCAS_tariff => Cur_qual_dets_val.ucas_tariff,
424 x_Imported_flag => 'Y',
425 x_Imported_date => Cur_qual_dets_val.Imported_date );
426 l_records_updated := NVL(l_records_updated,0) + 1 ;
427
428 END IF;
429
430 END IF; -- End of check for existence of Advanced Standing Unit level details
431
432 ELSIF l_imported IS NULL Then
433
434 --smaddali added this code for bug 2409543
435 c_grad_sch_rec := NULL ;
436 OPEN c_grad_sch (l_oss_exam_level) ;
437 FETCH c_grad_sch INTO c_grad_sch_rec ;
438 CLOSE c_grad_sch ;
439
440 --smaddali 27-jun-2002 added fields grading_schema_cd , version_number, logging person_number
441 --instead of person_id and logging application number for bug 2430139
442 --Diplays message "Inserting record".
443 fnd_message.set_name('IGS','IGS_UC_INS_QUAL_DETS_REC');
444 fnd_message.set_token('PER_NO', J.person_Number);
445 fnd_message.set_token('APP_NO', J.app_no);
446 fnd_message.set_token('EXM_LVL', l_oss_exam_level||' ('||I.exam_level||') ');
447 fnd_message.set_token('SUBJ_CD', l_oss_subject_code||' (' ||I.subject_code||') ');
448 fnd_message.set_token('YEAR', I.year);
449 fnd_message.set_token('SITTING', I.sitting);
450 fnd_message.set_token('AWD_BDY', l_oss_awarding_body||' ('||I.Awarding_body||') ');
451 fnd_message.set_token('GS_CD', C_grad_sch_rec.grading_schema_cd);
452 fnd_message.set_token('GS_VER', C_grad_sch_rec.gs_version_number);
453 fnd_message.set_token('EBL_RSLT',J.ebl_result);
454 fnd_message.set_token('CLM_RSLT',J.claimed_result);
455 fnd_file.put_line(fnd_file.log, fnd_message.get);
456
457 l_rowid := NULL ;
458 l_validation_status := TRUE ;
459
460 -- Check whether the UCAS EXam Level to OSS Award Mapping exist in the HESA Table
461 IF l_oss_exam_level IS NULL THEN
462 fnd_message.set_name('IGS','IGS_UC_INV_MAPPING_VAL');
463 fnd_message.set_token('CODE',I.Exam_level);
464 fnd_message.set_token('TYPE', 'EXAM LEVEL');
465 fnd_file.put_line(fnd_file.log, fnd_message.get);
466 l_validation_status := FALSE ;
467 END IF ;
468
469 -- Check whether the Grading Scema and Grade Combination exist in the Parent Table
470 IF c_grad_sch_rec.grading_schema_cd IS NOT NULL
471 AND c_grad_sch_rec.gs_version_number IS NOT NULL
472 AND j.ebl_result IS NOT NULL
473 AND NOT igs_as_grd_sch_grade_pkg.get_pk_for_validation (
474 UPPER(TRIM(c_grad_sch_rec.grading_schema_cd)),
475 c_grad_sch_rec.gs_version_number,
476 UPPER(TRIM(j.ebl_result)) ) THEN
477
478 fnd_message.set_name('IGS','IGS_UC_GRAD_NOT_FOUND');
479 fnd_message.set_token('GS_CD',c_grad_sch_rec.grading_schema_cd);
480 fnd_message.set_token('GS_VER',c_grad_sch_rec.gs_version_number);
481 fnd_message.set_token('APP_RESULT',j.ebl_result);
482 fnd_file.put_line(fnd_file.log, fnd_message.get);
483 l_validation_status := FALSE ;
484
485 ELSIF c_grad_sch_rec.grading_schema_cd IS NOT NULL
486 AND c_grad_sch_rec.gs_version_number IS NOT NULL
487 AND j.claimed_result IS NOT NULL
488 AND NOT igs_as_grd_sch_grade_pkg.get_pk_for_validation (
489 UPPER(TRIM(c_grad_sch_rec.grading_schema_cd)),
490 c_grad_sch_rec.gs_version_number,
491 UPPER(TRIM(j.claimed_result)) ) THEN
492
493 fnd_message.set_name('IGS','IGS_UC_GRAD_NOT_FOUND');
494 fnd_message.set_token('GS_CD',c_grad_sch_rec.grading_schema_cd);
495 fnd_message.set_token('GS_VER',c_grad_sch_rec.gs_version_number);
496 fnd_message.set_token('APP_RESULT',j.claimed_result);
497 fnd_file.put_line(fnd_file.log, fnd_message.get);
498 l_validation_status := FALSE ;
499
500 END IF ;
501
502 IF l_validation_status = TRUE THEN
503
504 /* Inserting record in to igs_uc_qual_dets table thru TBH */
505 Igs_uc_qual_dets_pkg.Insert_row ( x_mode => 'R',
506 x_rowid => l_rowid,
507 x_Qual_dets_id => l_Qual_dets_id,
508 x_Person_id => J.person_id,
509 x_Exam_level => l_oss_exam_level,
510 x_Subject_code => l_oss_subject_code,
511 x_Year => I.year,
512 x_Sitting => I.sitting,
513 x_Awarding_body => l_oss_awarding_body,
514 x_grading_schema_cd => c_grad_sch_rec.grading_schema_cd ,
515 x_version_number => c_grad_sch_rec.gs_version_number,
516 x_Predicted_result => NULL,
517 x_Approved_result => J.EBL_result,
518 x_Claimed_result => J.claimed_result,
519 x_UCAS_tariff => NULL,
520 x_Imported_flag => 'Y',
521 x_Imported_date => TRUNC(SYSDATE) );
522
523 l_records_inserted := NVL(l_records_inserted ,0) + 1 ;
524 ELSE
525 l_records_errored := NVL(l_records_errored ,0) + 1 ;
526 END IF;
527
528 END IF;
529
530 EXCEPTION
531 WHEN OTHERS THEN
532 --When the error occurs, log the Error message and continue with processing of next record.
533 l_records_errored := NVL(l_records_errored ,0) + 1 ;
534 l_mesg_data := NULL;
535 l_msg_index := NULL;
536 IF ( l_msg_count <> igs_ge_msg_stack.count_msg) THEN
537 igs_ge_msg_stack.get(igs_ge_msg_stack.count_msg,fnd_api.g_false, l_mesg_data, l_msg_index);
538 IF l_mesg_data IS NOT NULL THEN
539 l_mesg_data := ' - '||l_mesg_data;
540 END IF;
541 ELSE
542 l_mesg_data := SQLERRM;
543 IF l_mesg_data IS NOT NULL THEN
544 l_mesg_data := ' - '||l_mesg_data;
545 END IF;
546 END IF;
547 fnd_message.set_name('IGS','IGS_UC_QUAL_DET_IMP_ERR');
548 fnd_file.put_line(fnd_file.log,fnd_message.get()||l_mesg_data);
549
550 END; -- End of Anonymous block
551
552 fnd_file.put_line(fnd_file.log,' ' );
553 l_msg_count := NULL;
554
555 END LOOP; -- Insert/Update Igs_uc_qual_dets looop
556
557 END LOOP; -- igs_uc_com_ebl_subj Records Loop
558
559 fnd_file.put_line(fnd_file.log,' ' );
560 fnd_file.put_line(fnd_file.log,RPAD('-',22,'-'));
561 fnd_file.put_line(fnd_file.log,SUBSTR(fnd_message.get_string('IGS','IGS_UC_INS_REC_STAT_INT'), 29, 22));
562 fnd_file.put_line(fnd_file.log,RPAD('-',22,'-'));
563 fnd_message.set_name('IGS','IGS_UC_DEL_REC_COUNT');
564 fnd_message.set_token('REC_CNT', NVL(l_records_deleted,0));
565 fnd_file.put_line(fnd_file.log, fnd_message.get);
566 fnd_message.set_name('IGS','IGS_UC_INS_REC_COUNT');
567 fnd_message.set_token('REC_CNT', NVL(l_records_inserted,0));
568 fnd_file.put_line(fnd_file.log, fnd_message.get);
569 fnd_message.set_name('IGS','IGS_UC_UPD_REC_COUNT');
570 fnd_message.set_token('REC_CNT', NVL(l_records_updated,0));
571 fnd_file.put_line(fnd_file.log, fnd_message.get);
572 fnd_message.set_name('IGS','IGS_UC_ERR_REC_COUNT');
573 fnd_message.set_token('REC_CNT', NVL(l_records_errored,0));
574 fnd_file.put_line(fnd_file.log, fnd_message.get);
575 fnd_file.put_line(fnd_file.log,' ' );
576
577
578 EXCEPTION
579
580 WHEN igs_uc_he_not_enabled_excep THEN
581 fnd_message.set_name('IGS','IGS_UC_HE_NOT_ENABLED');
582 fnd_file.put_line(fnd_file.log, fnd_message.get);
583 retcode := 2;
584 igs_ge_msg_stack.conc_exception_hndl;
585
586 WHEN OTHERS THEN
587 ROLLBACK;
588 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
589 fnd_message.set_token('NAME', 'IGS_UC_QUAL_DETS_IMP_PKG.IGS_UC_QUAL_DETS_IMP'||' - '||SQLERRM);
590 fnd_message.retrieve (Errbuf);
591 retcode := 2;
592 igs_ge_msg_stack.conc_exception_hndl;
593
594 END igs_uc_qual_dets_imp;
595
596
597
598
599
600 PROCEDURE validate_pe_qual(p_uc_qual_cur igs_ad_imp_028.c_uc_qual_cur%ROWTYPE,
601 p_status OUT NOCOPY VARCHAR2,
602 p_error_code OUT NOCOPY VARCHAR2) IS
603 /*************************************************************
604 Created By : rgangara
605 Date Created On : 19-May-03
606 Purpose : This procedure will be called by Admission Import Process
607 while importing Previous QUalification details Legacy Data.
608 This is a validation procedure which would validate the
609 data populated in Interface table before being imported into OSS.
610 UCAS Bug for tracking this change in UCAS - Bug# 2961536
611 Know limitations, enhancements or remarks
612 Change History
613 Who When What
614 anwest 09-Jun-04 Changes for bug #4401841
615 rgangara 19-May-03 Create version for Adm Legacy import API.
616 UCAS bug for this Enh 2961536.
617
618 rgangara 04-JUL-03 Removed references to CLOSED INdicators as it is not
619 required for Legacy import. Also allowing for
620 S_Award_Type = COURSE.(bug#3037207, 3037229, 3037238).
621 Also removed reference to institution status = 'ACTIVE'
622 after clarifying with Babitha/Sara.
623
624 (reverse chronological order - newest change first)
625 ***************************************************************/
626
627
628 -- For validating Exam level/Qualification
629 CURSOR Cur_exam_lvl IS
630 SELECT grading_schema_cd,
631 gs_version_number
632 FROM igs_ps_awd
633 WHERE award_cd = p_uc_qual_cur.exam_level
634 AND s_award_type IN ('ENTRYQUAL', 'COURSE');
635
636 exam_lvl_rec cur_exam_lvl%ROWTYPE;
637
638 -- For validating Subject Code
639 CURSOR cur_subj_code IS
640 SELECT 'X'
641 FROM igs_ps_fld_of_study
642 WHERE field_of_study = p_uc_qual_cur.subject_code;
643
644 -- Hard coded values for YEAR validation as mentioned in Adm TD TD_LegacyImport_Admissions_s1a.
645 p_min_year NUMBER := 1900;
646 p_max_year NUMBER := 2200;
647
648
649 -- For validating Awarding Body
650 -- anwest 09-Jun-05
651 -- updated cursor for HZ_PARTIES.PARTY_NUMBER issue - bug #4401841
652 CURSOR cur_awd_body IS
653 SELECT 'X'
654 FROM hz_parties hp,
655 igs_pe_hz_parties ihp,
656 igs_or_org_inst_type oit,
657 igs_or_inst_stat ois
658 WHERE hp.party_id = ihp.party_id
659 AND ihp.oss_org_unit_cd = p_uc_qual_cur.awarding_body
660 AND hp.status = 'A'
661 AND ihp.oi_institution_status = ois.institution_status (+)
662 AND ihp.oi_institution_type = oit.institution_type (+)
663 AND ihp.inst_org_ind = 'I'
664 AND oit.system_inst_type IN ('POST-SECONDARY','OTHER');
665
666
667 -- Validating Grading Schema.
668 -- Grading schema can be provided by user in the INT Table.
669 -- If user provided the Grading schema details in the INT table, then
670 -- it has to be validated that it exists in Grading schema master table.
671 -- If found then check whether any of the Grades (Claimed, Predicted and
672 -- Approved) are valid for the grading schema given. If not log error
673 -- else import the grading schema details.
674 -- However, when user has not provided any grading schema details then
675 -- the grading schema derived from the Exam level is to be used for all
676 -- validations.
677 CURSOR cur_grd_sch (cp_grd_sch igs_as_grd_sch_grade.grading_schema_cd%TYPE,
678 cp_grd_ver igs_as_grd_sch_grade.version_number%TYPE,
679 cp_grade igs_as_grd_sch_grade.grade%TYPE) IS
680 SELECT 'X'
681 FROM igs_as_grd_sch_grade gsch,
682 igs_as_grd_schema ags
683 WHERE gsch.grading_schema_cd = ags.grading_schema_cd
684 AND gsch.version_number = ags.version_number
685 AND gsch.grading_schema_cd = cp_grd_sch
686 AND gsch.version_number = cp_grd_ver
687 AND ags.grading_schema_type = 'AWARD'
688 AND gsch.grade = NVL(cp_grade, gsch.grade);
689
690 l_grd_sch_rec cur_grd_sch%ROWTYPE;
691 lv_found VARCHAR2(1) := 'N';
692 l_grading_schema igs_as_grd_sch_grade.grading_schema_cd%TYPE;
693 l_version_number igs_as_grd_sch_grade.version_number%TYPE;
694
695 BEGIN
696
697 -- Exam Level/Qualification Validation
698 OPEN Cur_exam_lvl;
699 FETCH cur_exam_lvl INTO exam_lvl_rec;
700
701 IF cur_exam_lvl%NOTFOUND THEN
702 CLOSE cur_exam_lvl;
703 exam_lvl_rec.grading_schema_cd := NULL;
704 exam_lvl_rec.GS_version_number := NULL;
705 p_status := '3';
706 p_error_code := 'E627';
707 Return;
708 ELSE
709 CLOSE cur_exam_lvl;
710 END IF;
711
712 -- Subject Code validation
713 IF p_uc_qual_cur.subject_code IS NOT NULL THEN
714 OPEN Cur_subj_code;
715 FETCH Cur_subj_code INTO lv_found;
716 IF Cur_subj_code%NOTFOUND THEN
717 CLOSE cur_subj_code;
718 p_status := '3';
719 p_error_code := 'E628';
720 Return;
721 ELSE
722 CLOSE cur_subj_code;
723 END IF;
724 END IF;
725
726 -- YEAR validation
727 IF p_uc_qual_cur.year IS NOT NULL THEN
728 IF p_uc_qual_cur.year NOT BETWEEN p_min_year AND p_max_year THEN
729 p_status := '3';
730 p_error_code := 'E629';
731 Return;
732 END IF;
733 END IF;
734
735
736 -- Awarding Body validation
737 IF p_uc_qual_cur.awarding_body IS NOT NULL THEN
738 OPEN Cur_awd_body;
739 FETCH Cur_awd_body INTO lv_found;
740 IF Cur_awd_body%NOTFOUND THEN
741 p_status := '3';
742 p_error_code := 'E630';
743 CLOSE cur_awd_body;
744 Return;
745 ELSE
746 CLOSE cur_awd_body;
747 END IF;
748 END IF;
749
750 -- validating that if Grading Schema is provided by the user, it exists in OSS.
751 IF p_uc_qual_cur.grading_schema_cd IS NOT NULL OR p_uc_qual_cur.version_Number IS NOT NULL THEN
752 -- Since only grading schema is to be checked, passing NULL for Grade parameter
753 OPEN Cur_grd_sch(p_uc_qual_cur.grading_schema_cd,
754 p_uc_qual_cur.version_Number,
755 '');
756 FETCH Cur_grd_sch INTO l_grd_sch_rec;
757 IF Cur_grd_sch%NOTFOUND THEN
758 CLOSE Cur_grd_sch;
759 p_status := '3';
760 p_error_code := 'E682';
761 Return;
762 ELSE
763 -- Since user entered Grading schema is valid, set the variables with these values for
764 -- further processing and validations
765 l_grading_schema := p_uc_qual_cur.grading_schema_cd;
766 l_version_number := p_uc_qual_cur.version_number;
767 CLOSE Cur_grd_sch;
768 END IF;
769 ELSE
770 -- Since user entered Grading schema is NULL, set the variables with values from Exam level's
771 -- grading schema for further processing and validations.
772 l_grading_schema := exam_lvl_rec.grading_schema_cd;
773 l_version_number := exam_lvl_rec.gs_version_number;
774 END IF;
775
776 -- Grading Schema and Version for Predicted Result
777 IF p_uc_qual_cur.predicted_result IS NOT NULL THEN
778 OPEN Cur_grd_sch (l_grading_schema,
779 l_version_number,
780 p_uc_qual_cur.predicted_result);
781 FETCH Cur_grd_sch INTO l_grd_sch_rec;
782 IF Cur_grd_sch%NOTFOUND THEN
783 CLOSE Cur_grd_sch;
784 p_status := '3';
785 p_error_code := 'E631';
786 Return;
787 ELSE
788 CLOSE Cur_grd_sch;
789 END IF;
790 END IF;
791
792
793 -- Grading Schema and Version for Approved Result
794 IF p_uc_qual_cur.approved_result IS NOT NULL THEN
795 OPEN Cur_grd_sch (l_grading_schema,
796 l_version_number,
797 p_uc_qual_cur.approved_result);
798 FETCH Cur_grd_sch INTO l_grd_sch_rec;
799 IF Cur_grd_sch%NOTFOUND THEN
800 CLOSE Cur_grd_sch;
801 p_status := '3';
802 p_error_code := 'E632';
803 Return;
804 ELSE
805 CLOSE Cur_grd_sch;
806 END IF;
807 END IF;
808
809
810 -- Grading Schema and Version for CLAIMED Result
811 IF p_uc_qual_cur.claimed_result IS NOT NULL THEN
812 OPEN Cur_grd_sch (l_grading_schema,
813 l_version_number,
814 p_uc_qual_cur.claimed_result);
815 FETCH Cur_grd_sch INTO l_grd_sch_rec;
816 IF Cur_grd_sch%NOTFOUND THEN
817 CLOSE Cur_grd_sch;
818 p_status := '3';
819 p_error_code := 'E633';
820 Return;
821 ELSE
822 CLOSE Cur_grd_sch;
823 END IF;
824 END IF;
825
826
827 -- Validate SITTING
828 IF NOT p_uc_qual_cur.Sitting IN ('S', 'W') THEN
829 p_status := '3';
830 p_error_code := 'E634';
831 Return;
832 END IF;
833
834 -- At this point all validations are successful
835 p_status := 1;
836 p_error_code := NULL;
837
838 END validate_pe_qual;
839
840 END igs_uc_qual_dets_imp_pkg;
[Home] [Help]