1 PACKAGE BODY igs_he_import_data AS
2 /* $Header: IGSHE24B.pls 120.2 2006/02/09 17:43:04 jbaber noship $ */
3
4 l_conc_request_id NUMBER;
5 l_org_id CONSTANT igs_ps_ver.org_id%TYPE := IGS_GE_GEN_003.GET_ORG_ID ;
6 l_oss_domicile igs_he_code_map_val.map2%TYPE ;
7 l_oss_ethnicity igs_he_code_map_val.map2%TYPE ;
8 l_oss_nation igs_he_code_map_val.map2%TYPE ;
9 l_oss_occupation igs_he_code_map_val.map2%TYPE ;
10 l_oss_gender igs_he_code_map_val.map2%TYPE ;
11 l_oss_high_qual igs_he_code_map_val.map2%TYPE ;
12 l_oss_subj1 igs_he_code_map_val.map2%TYPE ;
13 l_oss_subj2 igs_he_code_map_val.map2%TYPE ;
14 l_oss_subj3 igs_he_code_map_val.map2%TYPE ;
15 l_oss_proportion igs_he_code_map_val.map2%TYPE ;
16 l_oss_disability igs_he_code_map_val.map2%TYPE ;
17 l_oss_fee_elig igs_he_code_values.value%TYPE ;
18 l_oss_inst igs_or_org_alt_ids.org_structure_id%TYPE ;
19 l_oss_social_class igs_he_code_map_val.map2%TYPE ;
20 l_batch_id igs_he_ucas_imp_err.batch_id%TYPE ;
21 l_error_flag BOOLEAN ;
22
23 -- Get the oss person details
24 CURSOR c_pe_det ( cp_person_number igs_pe_person.person_number%TYPE ) IS
25 SELECT p.party_id person_id, p.person_last_name surname,
26 p.person_first_name given_names,
27 pp.gender sex,
28 pp.date_of_birth birth_dt
29 FROM hz_parties p , hz_person_profiles pp
30 WHERE p.party_number = cp_person_number AND
31 pp.party_id(+)=p.party_id AND
32 SYSDATE BETWEEN NVL(pp.effective_start_date,SYSDATE) AND NVL(pp.effective_end_date,SYSDATE);
33 c_pe_det_rec c_pe_det%ROWTYPE ;
34
35 PROCEDURE log_error(p_error_code igs_he_ucas_imp_err.error_code%TYPE ,
36 p_interface_id igs_he_ucas_imp_err.interface_hesa_id%TYPE,
37 p_append VARCHAR2) IS
38 /******************************************************************
39 Created By : smaddali
40 Date Created By : 30-oct-2002
41 Purpose : To create error records in import interface error table
42 for the passed batch_id and interface_id and error code
43 Known limitations,enhancements,remarks:
44 Change History
45 Who When What
46 ***************************************************************** */
47
48 l_error_interface_id NUMBER ;
49 l_rowid VARCHAR2(50) ;
50 l_error_text igs_he_ucas_imp_err.error_text%TYPE ;
51
52 CURSOR c_err_text IS
53 SELECT description
54 FROM igs_lookups_view
55 WHERE lookup_type = 'IGS_HE_IMP_ERR' AND
56 lookup_code = p_error_code AND
57 closed_ind = 'N' ;
58
59 BEGIN
60
61 -- Get the error text from lookups
62 l_error_text := NULL ;
63 OPEN c_err_text ;
64 FETCH c_err_text INTO l_error_text ;
65 CLOSE c_err_text ;
66
67 -- If data needs to be appended to the error text then append at the end
68 IF p_append IS NOT NULL THEN
69 l_error_text := l_error_text || ' ' || p_append ;
70 END IF ;
71
72 l_rowid := NULL ;
73 l_error_interface_id := NULL ;
74 igs_he_ucas_imp_err_pkg.insert_row ( X_ROWID => l_rowid ,
75 X_ERROR_INTERFACE_ID => l_error_interface_id ,
76 X_INTERFACE_HESA_ID => p_interface_id ,
77 X_BATCH_ID => l_batch_id ,
78 X_ERROR_CODE => p_error_code ,
79 X_ERROR_TEXT => l_error_text ,
80 X_MODE => 'R' ) ;
81
82 EXCEPTION
83
84 WHEN OTHERS THEN
85 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
86 fnd_message.set_token('NAME','IGS_HE_IMPORT_DATA.LOG_ERROR'||' - '||SQLERRM);
87 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
88 IGS_GE_MSG_STACK.ADD;
89 App_Exception.Raise_Exception;
90 END log_error ;
91
92 FUNCTION deleted_alt_id_exists(p_person_id igs_pe_alt_pers_id.pe_person_id%TYPE,
93 p_alt_pers_id igs_pe_alt_pers_id.api_person_id%TYPE,
94 p_alt_person_type igs_pe_alt_pers_id.person_id_type%TYPE,
95 p_start_dt igs_pe_alt_pers_id.start_dt%TYPE) RETURN BOOLEAN AS
96
97 /******************************************************************
98 Created By : sjlaport
99 Date Created By : 07-February-2005
100 Purpose : Determines if a logically deleted Alternate Id
101 record exists with the value and start date.
102
103 Known limitations,enhancements,remarks:
104 Change History
105 Who When What
106 ***************************************************************** */
107
108
109 CURSOR c_pe_alt_pers_del IS
110 SELECT *
111 FROM igs_pe_alt_pers_id
112 WHERE pe_person_id = p_person_id
113 AND person_id_type= p_alt_person_type
114 AND api_person_id = p_alt_pers_id
115 AND TRUNC(start_dt) = TRUNC(p_start_dt);
116
117 c_pe_alt_pers_del_rec c_pe_alt_pers_del%ROWTYPE;
118
119 l_row_found BOOLEAN;
120
121
122 BEGIN
123
124 l_row_found := FALSE;
125
126 OPEN c_pe_alt_pers_del;
127 FETCH c_pe_alt_pers_del INTO c_pe_alt_pers_del_rec;
128
129 l_row_found := c_pe_alt_pers_del%FOUND;
130
131 CLOSE c_pe_alt_pers_del;
132
133 RETURN l_row_found;
134
135 EXCEPTION
136 WHEN OTHERS THEN
137
138 IF c_pe_alt_pers_del%ISOPEN THEN
139 CLOSE c_pe_alt_pers_del;
140 END IF ;
141
142 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
143 fnd_message.set_token('NAME','IGS_HE_IMPORT_DATA.DELETED_ALT_ID_EXISTS'||' - '||SQLERRM);
144 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
145 IGS_GE_MSG_STACK.ADD;
146
147 App_Exception.Raise_Exception;
148
149
150 END deleted_alt_id_exists;
151
152
153 PROCEDURE import_alternate_person_id(p_interface_hesa_id igs_he_ucas_imp_int.interface_hesa_id%TYPE,
154 p_interface_id NUMBER,
155 p_status VARCHAR,
156 p_orgid NUMBER,
157 p_person_id igs_pe_alt_pers_id.pe_person_id%TYPE,
158 p_alt_pers_id igs_pe_alt_pers_id.api_person_id%TYPE,
159 p_alt_person_type igs_pe_alt_pers_id.person_id_type%TYPE) AS
160
161 /******************************************************************
162 Created By : sjlaport
163 Date Created By : 24-September-2004
164 Purpose : Import alternate person id based on alternate person
165 type
166
167 Known limitations,enhancements,remarks:
168 Change History
169 Who When What
170 sjlaport 31-Jan-05 Modified parameter type and cursor c_pe_alt_pers
171 for HE358 to ignore logically deleted records. Included
172 call to new function to check for logically deleted records.
173 ***************************************************************** */
174
175 l_interface_api_id NUMBER ;
176 l_created_by CONSTANT NUMBER := FND_GLOBAL.USER_ID;
177 l_last_updated_by CONSTANT NUMBER := FND_GLOBAL.LOGIN_ID;
178 l_api_start_dt igs_ad_api_int.start_dt%TYPE;
179 l_insert_record BOOLEAN;
180
181 -- sjlaport HEFD350 Process 4
182 -- Select the latest alternate person id details for the specified person
183 CURSOR c_pe_alt_pers(cp_person_id_type igs_pe_alt_pers_id.person_id_type%TYPE) IS
184 SELECT *
185 FROM igs_pe_alt_pers_id
186 WHERE pe_person_id = p_person_id
187 AND person_id_type= cp_person_id_type
188 AND (end_dt IS NULL OR start_dt <> end_dt)
189 ORDER BY start_dt DESC;
190
191 c_pe_alt_pers_rec c_pe_alt_pers%ROWTYPE;
192
193 BEGIN
194
195 l_insert_record := FALSE;
196
197 -- Check if the person already has this alternate person id record with this person id type
198 OPEN c_pe_alt_pers(p_alt_person_type);
199 FETCH c_pe_alt_pers INTO c_pe_alt_pers_rec;
200
201 IF c_pe_alt_pers%NOTFOUND THEN
202
203 CLOSE c_pe_alt_pers;
204
205 IF deleted_alt_id_exists(p_person_id, p_alt_pers_id, p_alt_person_type, TRUNC(SYSDATE)) THEN
206 log_error('E39', p_interface_hesa_id, p_alt_person_type || ' ' || p_alt_pers_id);
207 ELSE
208
209 -- No records exist with for person being imported
210 -- so create this id directly
211 INSERT INTO igs_ad_api_int(status,
212 org_id,
213 person_id_type,
214 alternate_id,
215 start_dt,
216 end_dt,
217 interface_api_id,
218 interface_id,
219 created_by,
220 creation_date,
221 last_updated_by,
222 last_update_date)
223 VALUES (p_status,
224 p_orgid,
225 p_alt_person_type,
226 p_alt_pers_id,
227 TRUNC(SYSDATE),
228 NULL,
229 igs_ad_api_int_s.NEXTVAL,
230 p_interface_id,
231 l_created_by,
232 SYSDATE,
233 l_last_updated_by,
234 SYSDATE ) RETURNING interface_api_id INTO l_interface_api_id;
235
236 END IF;
237
238 ELSE
239
240 CLOSE c_pe_alt_pers;
241
242 -- An existing record was found. Determine if the
243 -- latest record matches the id that we are importing
244 IF c_pe_alt_pers_rec.api_person_id <> p_alt_pers_id THEN
245
246
247 IF Trunc(c_pe_alt_pers_rec.start_dt) >= Trunc(SYSDATE) THEN
248
249 -- Unable to create alternate person id record
250 -- because future dated records exists with a different for this person id type
251 log_error('E37', p_interface_hesa_id, p_alt_person_type || ' ' || p_alt_pers_id);
252
253 ELSE
254
255 -- If all the other api records had started before sysdate then set
256 -- the last started record's end date to sysdate - 1 and create the new api
257 -- record starting from sysdate
258 IF NVL(Trunc(c_pe_alt_pers_rec.end_dt), Trunc(SYSDATE)) >= Trunc(SYSDATE) THEN
259
260
261 IF p_alt_person_type <> 'HUSID' THEN
262
263 -- Log error as active id already exists for this
264 -- record with a different id
265 log_error('E38', p_interface_hesa_id, p_alt_person_type || ' ' || p_alt_pers_id);
266
267 ELSE
268
269 IF deleted_alt_id_exists(p_person_id, p_alt_pers_id, p_alt_person_type, TRUNC(SYSDATE)) THEN
270 log_error('E39', p_interface_hesa_id, p_alt_person_type || ' ' || p_alt_pers_id);
271 ELSE
272
273 -- Close existing record
274 INSERT INTO igs_ad_api_int(status,
275 org_id,
276 person_id_type,
277 alternate_id,
278 start_dt,
279 end_dt,
280 interface_api_id,
281 interface_id,
282 created_by,
283 creation_date,
284 last_updated_by,
285 last_update_date)
286 VALUES (p_status,
287 p_orgid,
288 p_alt_person_type,
289 c_pe_alt_pers_rec.api_person_id,
290 NVL(c_pe_alt_pers_rec.start_dt , Trunc(SYSDATE - 1) ),
291 Trunc(SYSDATE - 1),
292 igs_ad_api_int_s.NEXTVAL,
293 p_interface_id,
294 l_created_by,
295 SYSDATE,
296 l_last_updated_by,
297 SYSDATE ) RETURNING interface_api_id INTO l_interface_api_id;
298
299
300 -- Flag the insert of the new imported record
301 l_insert_record := TRUE;
302
303 END IF;
304
305 END IF;
306
307 ELSE
308
309 IF deleted_alt_id_exists(p_person_id, p_alt_pers_id, p_alt_person_type, TRUNC(SYSDATE)) THEN
310 log_error('E39', p_interface_hesa_id, p_alt_person_type || ' ' || p_alt_pers_id);
311 ELSE
312
313 -- A new record can be inserted for all alternate person id types
314 -- if End Date < SYSDATE
315 l_insert_record := TRUE;
316
317 END IF;
318
319
320 END IF;
321
322
323 IF l_insert_record THEN
324
325 -- Create a new record based on the interface record
326 -- starting from SYSDATE
327 INSERT INTO igs_ad_api_int(status,
328 org_id,
329 person_id_type,
330 alternate_id,
331 start_dt,
332 end_dt,
333 interface_api_id,
334 interface_id,
335 created_by,
336 creation_date,
337 last_updated_by,
338 last_update_date)
339 VALUES (p_status,
340 p_orgid,
341 p_alt_person_type,
342 p_alt_pers_id,
343 TRUNC(SYSDATE),
344 NULL,
345 igs_ad_api_int_s.NEXTVAL,
346 p_interface_id,
347 l_created_by,
348 SYSDATE,
349 l_last_updated_by,
350 SYSDATE ) RETURNING interface_api_id INTO l_interface_api_id;
351
352 END IF;
353
354 END IF;
355
356 ELSE -- Matching id found
357
358 IF TRUNC(c_pe_alt_pers_rec.start_dt) >= TRUNC(SYSDATE) THEN
359
360 -- Issue warning as future dated record already
361 -- exists will the same id
362 fnd_message.set_name('IGS','IGS_HE_ALT_FUTURE_REC_EXISTS');
363 fnd_message.set_token('ALT_PERS_TYPE',p_alt_person_type);
364 fnd_message.set_token('ALT_PERS_ID',p_alt_pers_id);
365 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
366
367 ELSIF TRUNC(c_pe_alt_pers_rec.end_dt) >= TRUNC(SYSDATE) THEN
368
369 IF p_alt_person_type <> 'HUSID' THEN
370
371 -- Issue warning that we are unable to create alternate person id record
372 -- because future dated record exists with the same id
373 fnd_message.set_name('IGS','IGS_HE_ALT_ACTIVE_REC_EXISTS');
374 fnd_message.set_token('ALT_PERS_TYPE',p_alt_person_type);
375 fnd_message.set_token('ALT_PERS_ID',p_alt_pers_id);
376 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
377
378 ELSE
379
380 -- Create the record starting from the previous records end date
381 l_api_start_dt := TRUNC(c_pe_alt_pers_rec.end_dt + 1);
382
383
384 IF deleted_alt_id_exists(p_person_id, p_alt_pers_id, p_alt_person_type, l_api_start_dt) THEN
385 log_error('E39', p_interface_hesa_id, p_alt_person_type || ' ' || p_alt_pers_id);
386 ELSE
387
388 l_insert_record := TRUE;
389
390 -- Issue warning that future dated record will be created
391 fnd_message.set_name('IGS','IGS_HE_ALT_FUTURE_REC_CREATED');
392 fnd_message.set_token('ALT_PERS_TYPE',p_alt_person_type);
393 fnd_message.set_token('ALT_PERS_ID',p_alt_pers_id);
394 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
395
396 END IF;
397
398 END IF; -- Alternate id type is not HUSID
399
400 ELSIF TRUNC(c_pe_alt_pers_rec.end_dt) IS NOT NULL THEN
401
402
403 IF deleted_alt_id_exists(p_person_id, p_alt_pers_id, p_alt_person_type, TRUNC(SYSDATE)) THEN
404 log_error('E39', p_interface_hesa_id, p_alt_person_type || ' ' || p_alt_pers_id);
405 ELSE
406
407 -- End date < SYSDATE
408 -- Create record from SYSDATE
409 l_api_start_dt := TRUNC(SYSDATE);
410
411 l_insert_record := TRUE;
412
413 END IF;
414
415 END IF; -- End date >= SYSDATE
416
417 IF l_insert_record THEN
418
419 -- Create record from specified start date
420 INSERT INTO igs_ad_api_int(status,
421 org_id,
422 person_id_type,
423 alternate_id,
424 start_dt,
425 end_dt,
426 interface_api_id,
427 interface_id,
428 created_by,
429 creation_date,
430 last_updated_by,
431 last_update_date)
432 VALUES (p_status,
433 p_orgid,
434 p_alt_person_type,
435 p_alt_pers_id,
436 l_api_start_dt,
437 NULL,
438 igs_ad_api_int_s.NEXTVAL,
439 p_interface_id,
440 l_created_by,
441 SYSDATE,
442 l_last_updated_by,
443 SYSDATE ) RETURNING interface_api_id INTO l_interface_api_id;
444
445 END IF; -- Insert record
446
447 END IF; -- Existing non-matching record found
448
449 END IF; -- No record found
450
451 EXCEPTION
452 WHEN OTHERS THEN
453
454 IF c_pe_alt_pers%ISOPEN THEN
455 CLOSE c_pe_alt_pers;
456 END IF ;
457
458 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
459 fnd_message.set_token('NAME','IGS_HE_IMPORT_DATA.IMPORT_ALTERNATE_PERSON_ID'||' - '||SQLERRM);
460 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
461 IGS_GE_MSG_STACK.ADD;
462
463 App_Exception.Raise_Exception;
464
465 END import_alternate_person_id;
466
467 PROCEDURE populate_imp_int (
468 p_source_type_id igs_pe_src_types_all.source_type_id%TYPE,
469 p_batch_id NUMBER, -- admission import batch id
470 p_orgid NUMBER ,
471 p_person_id igs_pe_person.person_id%TYPE ,
472 p_hesa_id igs_he_ucas_imp_int.interface_hesa_id%TYPE) AS
473 /******************************************************************
474 Created By : smaddali
475 Date Created By : 29-oct-2002
476 Purpose : To populate import person details interface tables
477 for the passed person
478 Known limitations,enhancements,remarks:
479 Change History
480 Who When What
481
482 uudayapr 25-Nov-2003 Removed the Trim Statement from the Cursor c_interface for Birth_dt as a part of bug#3175113 fix
483 smaddali 17-dec-2002 giving NVL(sysdate) for start_date of disability,citizen and
484 alternate person id record for bug 2715487
485 smaddali 31-dec-2002 modified this procedure to import disaibility type, bug 2730129
486 smaddali 9-jan-03 modified cursor c_interface to trim fields being selected to remove spaces , bug 2740653
487 ayedubat 09-OCT-03 Removed the cursor,c_pe_stat and put an additional validation
488 to populate the admission statistics interface table only
489 if the ethnic origin is provided in the interface table for Bug# 3175020
490 ayedubat 14-OCT-03 Changed the Logic for populating the altenate person id interface table
491 if the student has an Alternate Person ID record with the same TYPE and ID
492 and that is the latest record for Bug# 2762866
493 sjlaport 07-Dec-04 Added call to method import_alternate_person_id and removed cursors that were
494 were no longer used
495 ***************************************************************** */
496
497 l_status VARCHAR2(2);
498 l_record_status VARCHAR2(1);
499 l_created_by CONSTANT NUMBER := FND_GLOBAL.USER_ID;
500 l_last_updated_by CONSTANT NUMBER := FND_GLOBAL.LOGIN_ID;
501 l_request_id CONSTANT NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
502 l_program_application_id CONSTANT NUMBER := FND_GLOBAL.PROG_APPL_ID;
503 l_program_id CONSTANT NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
504 l_interface_id NUMBER ;
505 l_interface_stat_id NUMBER ;
506 l_interface_api_id NUMBER ;
507 l_interface_disablty_id NUMBER ;
508 l_interface_citizenship_id NUMBER ;
509
510 -- Get all the interface records for the passed batch id
511 -- smaddali 9-jan-03 modified this cursor for bug 2740653 , trimming interface record fields to remove spaces
512 CURSOR c_interface IS
513 SELECT trim(a.interface_hesa_id) interface_hesa_id , trim(a.batch_id) batch_id,
514 upper(trim(a.husid)) husid , trim(a.person_number) person_number,
515 upper(trim(a.ucasnum)) ucasnum, upper(trim(a.scotvec)) scotvec, trim(a.surname) surname,
516 trim(a.given_names) given_names , (a.birth_dt) birth_dt,
517 trim(a.country_code) country_code, trim(a.disability_type) disability_type
518 FROM igs_he_ucas_imp_int a
519 WHERE a.batch_id = l_batch_id AND
520 interface_hesa_id = p_hesa_id ;
521 c_interface_rec c_interface%ROWTYPE ;
522
523 -- Get the latest citizenship record for the person and interface country code
524 CURSOR c_pe_nat IS
525 SELECT *
526 FROM hz_citizenship
527 WHERE party_id = p_person_id AND
528 country_code = l_oss_nation
529 ORDER BY date_recognized DESC ;
530 c_pe_nat_rec c_pe_nat%ROWTYPE ;
531
532 -- Get all the citizenship records for the person of a different country than
533 -- the interface country code , and close them
534 CURSOR c_other_nat IS
535 SELECT *
536 FROM hz_citizenship
537 WHERE party_id = p_person_id AND
538 country_code <> l_oss_nation
539 ORDER BY country_code DESC ;
540
541 -- check if the person has a record for the interface disability type which is not ended
542 -- smaddali modified this cursor to check that end_date is null and
543 -- start date could be null or not equal to sysdate, bug 2730129
544 CURSOR c_pe_dis IS
545 SELECT dis.rowid,dis.*
546 FROM igs_pe_pers_disablty dis
547 WHERE person_id = p_person_id AND
548 disability_type = l_oss_disability AND
549 end_date IS NULL
550 ORDER BY start_date DESC ;
551 c_pe_dis_rec c_pe_dis%ROWTYPE ;
552
553 -- Get all the disability records for the person to be closed
554 -- smaddali modified this cursor to exclude current disability type , bug 2730129
555 CURSOR c_other_dis IS
556 SELECT dis.*
557 FROM igs_pe_pers_disablty dis
558 WHERE person_id = p_person_id
559 AND disability_type <> l_oss_disability
560 AND start_date IS NOT NULL
561 ORDER BY dis.disability_type DESC ;
562
563 -- smaddali added this cursor for bug 2730129
564 -- check if there are any disability records of the passed type which are starting on sysdate
565 CURSOR c_pe_dis_sysdate IS
566 SELECT dis.*
567 FROM igs_pe_pers_disablty dis
568 WHERE person_id = p_person_id AND
569 disability_type = l_oss_disability AND
570 Trunc(start_date) = Trunc(SYSDATE)
571 ORDER BY start_date DESC ;
572 c_pe_dis_sysdate_rec c_pe_dis_sysdate%ROWTYPE ;
573
574 BEGIN
575 l_status := '2' ;
576 l_record_status := '2';
577
578 /* *************** importing person details ***************** */
579
580 -- Get the hesa interface record details
581 OPEN c_interface ;
582 FETCH c_interface INTO c_interface_rec ;
583 CLOSE c_interface ;
584
585 -- Create an interface record for this person
586 -- Since this person already exists in oss and the person identifier is known , matching can be skipped
587 -- and directly the person record can be updated ,hence match_ind is set to 15
588 INSERT INTO igs_ad_interface(person_number,
589 interface_id,
590 batch_id,
591 org_id,
592 source_type_id,
593 person_id,
594 surname,
595 given_names,
596 sex,
597 birth_dt,
598 status,
599 record_status,
600 match_ind,
601 created_by,
602 creation_date,
603 last_updated_by,
604 last_update_date,
605 request_id,
606 program_application_id,
607 program_id,
608 program_update_date )
609 VALUES(c_interface_rec.person_number,
610 igs_ad_interface_s.NEXTVAL,
611 p_batch_id,
612 p_orgid,
613 p_source_type_id,
614 c_pe_det_rec.person_id,
615 NVL(c_interface_rec.surname,c_pe_det_rec.surname),
616 NVL(c_interface_rec.given_names,c_pe_det_rec.given_names),
617 NVL(l_oss_gender,c_pe_det_rec.sex),
618 NVL(c_interface_rec.birth_dt,c_pe_det_rec.birth_dt),
619 l_status,
620 l_record_status,
621 '15',
622 l_created_by,
623 SYSDATE,
624 l_last_updated_by,
625 SYSDATE,
626 l_request_id,
627 l_program_application_id,
628 l_program_id,
629 SYSDATE ) RETURNING interface_id INTO l_interface_id;
630
631 -- Populate the person Statistics interface table with the ethnic origin, if provided
632 -- If interface record does not have ethnic origin then statistic record need not be required to populate
633 -- modified this as part of the bug, 3175020
634 -- Get the person statistical details
635
636 IF l_oss_ethnicity IS NOT NULL THEN
637
638 l_interface_stat_id := NULL;
639 INSERT INTO igs_ad_stat_int(interface_stat_id,
640 interface_id,
641 status,
642 org_id,
643 ethnic_origin,
644 created_by,
645 creation_date,
646 last_updated_by,
647 last_update_date)
648 VALUES (igs_ad_stat_int_s.NEXTVAL,
649 l_interface_id,
650 l_status,
651 p_orgid,
652 l_oss_ethnicity,
653 l_created_by,
654 SYSDATE,
655 l_last_updated_by,
656 SYSDATE) RETURNING interface_stat_id INTO l_interface_stat_id;
657 END IF;
658
659 /* *************** end of importing person details ***************** */
660
661
662 /* *************** importing person disability details ***************** */
663
664 -- If disability type is given in interface recprd then Import person disability type into oss
665 IF c_interface_rec.disability_type IS NOT NULL THEN
666 -- Firstly Close all the existing disability records for this person
667 FOR c_other_dis_rec IN c_other_dis LOOP
668 -- Close the disability record by end dating it
669 INSERT INTO igs_ad_disablty_int(interface_disablty_id,
670 interface_id,
671 status,
672 disability_type,
673 start_date,
674 end_date,
675 created_by,
676 creation_date,
677 last_updated_by,
678 last_update_date)
679 VALUES (igs_ad_disablty_int_s.NEXTVAL ,
680 l_interface_id,
681 l_status,
682 c_other_dis_rec.disability_type,
683 NVL(c_other_dis_rec.start_date,TRUNC(SYSDATE)),
684 TRUNC(SYSDATE),
685 l_created_by,
686 SYSDATE,
687 l_last_updated_by,
688 SYSDATE) RETURNING interface_disablty_id INTO l_interface_disablty_id ;
689 END LOOP ;
690
691 -- Check if already person has any records of this disability type which are not ended .
692 -- If not then create a new record
693 -- smaddali removed code making end_date null for existing records , bug 2730129
694 OPEN c_pe_dis ;
695 FETCH c_pe_dis INTO c_pe_dis_rec;
696 IF c_pe_dis%NOTFOUND THEN
697 CLOSE c_pe_dis;
698 -- create a new disability record if already some other record did not start on sysdate
699 OPEN c_pe_dis_sysdate ;
700 FETCH c_pe_dis_sysdate INTO c_pe_dis_sysdate_rec;
701 IF c_pe_dis_sysdate%NOTFOUND THEN
702 INSERT INTO igs_ad_disablty_int(interface_disablty_id,
703 interface_id,
704 status,
705 disability_type,
706 start_date,
707 end_date,
708 created_by,
709 creation_date,
710 last_updated_by,
711 last_update_date)
712 VALUES (igs_ad_disablty_int_s.NEXTVAL ,
713 l_interface_id,
714 l_status,
715 l_oss_disability,
716 TRUNC(SYSDATE),
717 NULL,
718 l_created_by,
719 SYSDATE,
720 l_last_updated_by,
721 SYSDATE) RETURNING interface_disablty_id INTO l_interface_disablty_id ;
722 ELSE
723 -- If already this disability record exists which started on sysdate and ended some date ,
724 -- we need to update that records end date to null so that it will be active
725 INSERT INTO igs_ad_disablty_int(interface_disablty_id,
726 interface_id,
727 status,
728 disability_type,
729 start_date,
730 end_date,
731 created_by,
732 creation_date,
733 last_updated_by,
734 last_update_date)
735 VALUES (igs_ad_disablty_int_s.NEXTVAL ,
736 l_interface_id,
737 l_status,
738 c_pe_dis_sysdate_rec.disability_type,
739 NVL(c_pe_dis_sysdate_rec.start_date, Trunc(SYSDATE)),
740 NULL,
741 l_created_by,
742 SYSDATE,
743 l_last_updated_by,
744 SYSDATE) RETURNING interface_disablty_id INTO l_interface_disablty_id ;
745 END IF ;
746 CLOSE c_pe_dis_sysdate;
747
748
749 ELSE
750 CLOSE c_pe_dis;
751 -- The person has same disability type records which are open
752 IF c_pe_dis_rec.start_date IS NULL THEN
753 -- If this disability record already exists with an open end date and start_date as nul then
754 -- make its start date as sysdate if there there is not one more disability record of this type which has started on sysdate
755 OPEN c_pe_dis_sysdate ;
756 FETCH c_pe_dis_sysdate INTO c_pe_dis_sysdate_rec;
757 IF c_pe_dis_sysdate%NOTFOUND THEN
758 CLOSE c_pe_dis_sysdate;
759 BEGIN
760 IGS_PE_PERS_DISABLTY_PKG.UPDATE_ROW (
761 X_ROWID => c_pe_dis_rec.rowid,
762 X_IGS_PE_PERS_DISABLTY_ID => c_pe_dis_rec.igs_pe_pers_disablty_id,
763 x_PERSON_ID => c_pe_dis_rec.person_id,
764 x_DISABILITY_TYPE => c_pe_dis_rec.disability_type,
765 x_CONTACT_IND => c_pe_dis_rec.contact_ind ,
766 x_SPECIAL_ALLOW_CD => c_pe_dis_rec.special_allow_cd,
767 x_SUPPORT_LEVEL_CD => c_pe_dis_rec.support_level_cd ,
768 x_DOCUMENTED => c_pe_dis_rec.documented ,
769 x_SPECIAL_SERVICE_ID => c_pe_dis_rec.special_service_id ,
770 x_ATTRIBUTE_CATEGORY => c_pe_dis_rec.attribute_category ,
771 x_ATTRIBUTE1 => c_pe_dis_rec.attribute1,
772 x_ATTRIBUTE2 => c_pe_dis_rec.attribute2,
773 x_ATTRIBUTE3 => c_pe_dis_rec.attribute3,
774 x_ATTRIBUTE4 => c_pe_dis_rec.attribute4,
775 x_ATTRIBUTE5 => c_pe_dis_rec.attribute5,
776 x_ATTRIBUTE6 => c_pe_dis_rec.attribute6,
777 x_ATTRIBUTE7 => c_pe_dis_rec.attribute7,
778 x_ATTRIBUTE8 => c_pe_dis_rec.attribute8,
779 x_ATTRIBUTE9 => c_pe_dis_rec.attribute9,
780 x_ATTRIBUTE10 => c_pe_dis_rec.attribute10,
781 x_ATTRIBUTE11 => c_pe_dis_rec.attribute11,
782 x_ATTRIBUTE12 => c_pe_dis_rec.attribute12,
783 x_ATTRIBUTE13 => c_pe_dis_rec.attribute13,
784 x_ATTRIBUTE14 => c_pe_dis_rec.attribute14,
785 x_ATTRIBUTE15 => c_pe_dis_rec.attribute15,
786 x_ATTRIBUTE16 => c_pe_dis_rec.attribute16,
787 x_ATTRIBUTE17 => c_pe_dis_rec.attribute17,
788 x_ATTRIBUTE18 => c_pe_dis_rec.attribute18,
789 x_ATTRIBUTE19 => c_pe_dis_rec.attribute19,
790 x_ATTRIBUTE20 => c_pe_dis_rec.attribute20,
791 X_ELIG_EARLY_REG_IND => c_pe_dis_rec.elig_early_reg_ind,
792 X_START_DATE => TRUNC(SYSDATE),
793 X_END_DATE => NULL,
794 X_INFO_SOURCE => c_pe_dis_rec.info_source,
795 X_INTERVIEWER_ID => c_pe_dis_rec.interviewer_id,
796 X_INTERVIEWER_DATE => c_pe_dis_rec.interviewer_date,
797 X_MODE => 'R'
798 );
799 EXCEPTION
800 WHEN OTHERS THEN
801 -- log error message that update of disability records start date from null to
802 -- sysdate failed due to unhandled exception raised by the tbh
803 log_error('E34' ,c_interface_rec.interface_hesa_id, SQLERRM);
804 END ;
805 ELSE
806 -- already this disability type record starting on sysdate exists for this person ,
807 -- so cannot create one more
808 CLOSE c_pe_dis_sysdate;
809 -- If already this disability record exists which started on sysdate and ended some date ,
810 -- we need to update that records end date to null so that it will be active
811 INSERT INTO igs_ad_disablty_int(interface_disablty_id,
812 interface_id,
813 status,
814 disability_type,
815 start_date,
816 end_date,
817 created_by,
818 creation_date,
819 last_updated_by,
820 last_update_date)
821 VALUES (igs_ad_disablty_int_s.NEXTVAL ,
822 l_interface_id,
823 l_status,
824 c_pe_dis_sysdate_rec.disability_type,
825 c_pe_dis_sysdate_rec.start_date,
826 NULL,
827 l_created_by,
828 SYSDATE,
829 l_last_updated_by,
830 SYSDATE) RETURNING interface_disablty_id INTO l_interface_disablty_id ;
831 END IF ;
832
833 END IF ; -- if existing record's start date is null then make it sysdate
834
835 END IF ; -- if open disability record doesnot exist
836 END IF ; -- import disability type
837
838 /* *************** end of importing person disability details ***************** */
839
840
841 /* *************** importing person citizenship details ***************** */
842
843 -- If country code is given in the interface record then import person nationality to oss
844 IF c_interface_rec.country_code IS NOT NULL THEN
845 -- Firstly Close all the existing citizenship records for this person
846 FOR c_other_nat_rec IN c_other_nat LOOP
847 -- Close the citizenship record by end dating it
848 INSERT INTO igs_pe_citizen_int(interface_citizenship_id,
849 interface_id,
850 status,
851 country_code,
852 date_recognized,
853 date_disowned,
854 end_date,
855 created_by,
856 creation_date,
857 last_updated_by,
858 last_update_date)
859 VALUES (igs_pe_citizen_int_s.NEXTVAL,
860 l_interface_id,
861 l_status,
862 c_other_nat_rec.country_code,
863 NVL(c_other_nat_rec.date_recognized,TRUNC(SYSDATE)) ,
864 NULL,
865 TRUNC(SYSDATE),
866 l_created_by,
867 SYSDATE,
868 l_last_updated_by,
869 SYSDATE) RETURNING interface_citizenship_id INTO l_interface_citizenship_id;
870 END LOOP ;
871
872 -- Check if the person already has a citizenship record for this country
873 -- If no then create a new record
874 OPEN c_pe_nat ;
875 FETCH c_pe_nat INTO c_pe_nat_rec ;
876 IF c_pe_nat%NOTFOUND THEN
877 CLOSE c_pe_nat ;
878 -- If person is not already having citizenship for this country then
879 -- create a new nationality record
880 INSERT INTO igs_pe_citizen_int(interface_citizenship_id,
881 interface_id,
882 status,
883 country_code,
884 date_recognized,
885 created_by,
886 creation_date,
887 last_updated_by,
888 last_update_date)
889 VALUES (igs_pe_citizen_int_s.NEXTVAL,
890 l_interface_id,
891 l_status,
892 l_oss_nation,
893 Trunc(SYSDATE) ,
894 l_created_by,
895 SYSDATE,
896 l_last_updated_by,
897 SYSDATE) RETURNING interface_citizenship_id INTO l_interface_citizenship_id;
898 ELSE
899 CLOSE c_pe_nat ;
900 -- If already the person has a citizenship record for this country then
901 -- Get the Latest citizenship record for this person in this country and update its
902 -- start and end dates appropriately
903 IF Trunc(c_pe_nat_rec.date_recognized) <= Trunc(SYSDATE) THEN
904 -- If the latest record start date is before sysdate then update its end date to NULL
905 -- But if it is a future record then we cannot create a valid citizenship record ,hence log error
906 INSERT INTO igs_pe_citizen_int(interface_citizenship_id,
907 interface_id,
908 status,
909 country_code,
910 date_recognized,
911 date_disowned,
912 end_date,
913 created_by,
914 creation_date,
915 last_updated_by,
916 last_update_date)
917 VALUES (igs_pe_citizen_int_s.NEXTVAL,
918 l_interface_id,
919 l_status,
920 c_pe_nat_rec.country_code,
921 NVL(c_pe_nat_rec.date_recognized,TRUNC(SYSDATE)) ,
922 NULL,
923 NULL,
924 l_created_by,
925 SYSDATE,
926 l_last_updated_by,
927 SYSDATE) RETURNING interface_citizenship_id INTO l_interface_citizenship_id;
928 ELSE
929 -- log error message that unable to create citizenship record due to future dated records existing
930 log_error('E29' ,c_interface_rec.interface_hesa_id, NULL);
931 END IF; -- date comparisions
932
933 END IF ; -- nationality record already exists
934 END IF ; -- import country code
935
936 /* *************** end of importing person citizenship details ***************** */
937
938
939
940 /* *************** importing UCASID alternate person id details ***************** */
941
942 -- Populate alternate person ID details interface table with UCASID
943 IF c_interface_rec.ucasnum IS NOT NULL THEN
944
945 import_alternate_person_id(c_interface_rec.interface_hesa_id,
946 l_interface_id,
947 l_status,
948 p_orgid,
949 p_person_id,
950 c_interface_rec.ucasnum, 'UCASID');
951 END IF;
952
953 /* *************** end of importing UCASID alternate person id details ***************** */
954
955 /* *************** importing HUSID alternate person id details ***************** */
956
957 -- Populate alternate person ID details interface table with HUSID
958 IF c_interface_rec.husid IS NOT NULL THEN
959
960 import_alternate_person_id(c_interface_rec.interface_hesa_id,
961 l_interface_id,
962 l_status,
963 p_orgid,
964 p_person_id,
965 c_interface_rec.husid, 'HUSID');
966 END IF;
967
968 /* *************** end of importing HUSID alternate person id details ***************** */
969
970 /* *************** importing UCASREGNO alternate person id details ***************** */
971
972 -- Populate alternate person ID details interface table with UCASREGNO
973 IF c_interface_rec.scotvec IS NOT NULL THEN
974
975 import_alternate_person_id(c_interface_rec.interface_hesa_id,
976 l_interface_id,
977 l_status,
978 p_orgid,
979 p_person_id,
980 c_interface_rec.scotvec, 'UCASREGNO');
981 END IF;
982
983 /* *************** end of importing UCASREGNO alternate person id details ***************** */
984
985
986
987 EXCEPTION
988 WHEN OTHERS THEN
989 IF c_interface%ISOPEN THEN
990 CLOSE c_interface ;
991 END IF ;
992 IF c_pe_nat%ISOPEN THEN
993 CLOSE c_pe_nat;
994 END IF ;
995 IF c_pe_dis%ISOPEN THEN
996 CLOSE c_pe_dis;
997 END IF ;
998 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
999 fnd_message.set_token('NAME','IGS_UCAS_EXPORT_TO_OSS.POPULATE_IMP_INT'||' - '||SQLERRM);
1000 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
1001 IGS_GE_MSG_STACK.ADD;
1002 App_Exception.Raise_Exception;
1003
1004 END populate_imp_int;
1005
1006
1007 PROCEDURE import_process(
1008 p_source_type_id igs_pe_src_types_all.source_type_id%TYPE,
1009 p_batch_id NUMBER,
1010 p_orgid NUMBER
1011 ) IS
1012
1013 /******************************************************************
1014 Created By : smaddali
1015 Date Created By : 29-oct-2002
1016 Purpose : Submit the call for admission application import process
1017 Known limitations,enhancements,remarks:
1018 Change History
1019 Who When What
1020 ***************************************************************** */
1021
1022 l_row_id VARCHAR2(26);
1023
1024 CURSOR cur_match_set IS
1025 SELECT match_set_id
1026 FROM igs_pe_match_sets
1027 WHERE source_type_id = p_source_type_id;
1028 match_set_rec cur_match_set%ROWTYPE;
1029
1030 l_interface_run_id igs_ad_interface_ctl.interface_run_id%TYPE;
1031 l_errbuff VARCHAR2(100) ;
1032 l_retcode NUMBER ;
1033
1034 BEGIN
1035
1036 -- Get the match set criteria corresponding to the ucas source type to be used for the person import
1037 match_set_rec := NULL ;
1038 OPEN cur_match_set;
1039 FETCH cur_match_set INTO match_set_rec;
1040 CLOSE cur_match_set;
1041
1042
1043 -- Call admission application import process procedure because current process has to wait until import process is finished
1044 IGS_AD_IMP_001.IMP_ADM_DATA ( errbuf => l_errbuff,
1045 retcode => l_retcode ,
1046 p_batch_id => p_batch_id,
1047 p_source_type_id => p_source_type_id,
1048 p_match_set_id => match_set_rec.match_set_id,
1049 p_acad_cal_type => NULL ,
1050 p_acad_sequence_number => NULL ,
1051 p_adm_cal_type => NULL ,
1052 p_adm_sequence_number => NULL ,
1053 p_admission_cat => NULL ,
1054 p_s_admission_process_type => NULL ,
1055 p_interface_run_id => l_interface_run_id ,
1056 P_org_id => NULL ) ;
1057
1058
1059 EXCEPTION
1060 WHEN OTHERS THEN
1061 IF cur_match_set%ISOPEN THEN
1062 CLOSE cur_match_set;
1063 END IF ;
1064 -- even though the admission import process completes in error , this process should continue processing
1065 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
1066 fnd_message.set_token('NAME','IGS_HE_IMPORT_DATA.IMPORT_PROCESS'||' - '||SQLERRM);
1067 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
1068 END import_process;
1069
1070 PROCEDURE main_process(
1071 errbuf OUT NOCOPY VARCHAR2,
1072 retcode OUT NOCOPY NUMBER,
1073 p_batch_id igs_he_batch_int.batch_id%TYPE ,
1074 p_pers_det VARCHAR2 ,
1075 p_spa_det VARCHAR2
1076 ) IS
1077 /******************************************************************
1078 Created By : smaddali
1079 Date Created By : 29-oct-2002
1080 Purpose : Main process called from concurrent manager for "Import HESA Student Details" process
1081 Known limitations,enhancements,remarks:
1082 Change History
1083 Who When What
1084 uudayapr 03-dec-2003 enh#3291662 Modified the cursor c_alt_orgid,c_inst as per the td of HECR212.
1085 uudayapr 25-Nov-2003 Removed the Trim Statement from the Cursor c_interface for Birth_dt as a part of bug#3175113 fix
1086 smaddali Added validations for the 12 ucas tariff fields to check if oss code is mapped or not
1087 and to pass oss code while creating tariff records , bug 2671022
1088 ayedubat 11-FEB-2003 Added two new cursors,cur_calc_type and cur_ut_award_map for fetching the new
1089 UCAS Tariff Calculation Setup created in this Enhancement.
1090 Changed the logic of getting the HESA Tariff to OSS Award mapping from
1091 'HESA_AWD_UT_ASSOC' HESA Association to the new setup. Bug # 2717744
1092 rbezawad 26-Feb-03 Modified w.r.t. Bug 2777247. Added code to insert record into IGS_AD_IMP_BATCH_DET table.
1093 rbezawad 16-Sep-03 Modified the process w.r.t. UCFD210 Build, Bug 2893542 to populate the Previous education details into
1094 OSS Academic History and obsolete the functionality related to IGS_UC_ATTEND_HIST.
1095 sjlaport 24-Feb-05 Corrected reference to HESA disability association OSS_HESA_DISABILITY_ASSOC
1096 sjlaport 31-Jan-05 Modified cursor c_alt_persid for HE358 to ignore logically deleted records.
1097 anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
1098 ***************************************************************** */
1099
1100 IGS_UC_HE_NOT_ENABLED_EXCEP EXCEPTION;
1101 l_no_setup BOOLEAN ;
1102 l_rep_request_id NUMBER ;
1103 l_imp_batch_id igs_ad_interface.batch_id%TYPE ;
1104 l_ins_tariff BOOLEAN ;
1105 l_upd_spa BOOLEAN ;
1106 l_upd_susa BOOLEAN ;
1107 l_upd_person BOOLEAN ;
1108 l_rowid VARCHAR2(50) ;
1109 l_hesa_st_spau_id igs_he_st_spa_ut.hesa_st_spau_id%TYPE;
1110
1111
1112 -- Get all the error records for the passed batch id and interface id
1113 CURSOR c_del_err(cp_interface_id igs_he_ucas_imp_int.interface_hesa_id%TYPE) IS
1114 SELECT err.rowid
1115 FROM igs_he_ucas_imp_err err
1116 WHERE err.batch_id = p_batch_id AND
1117 err.interface_hesa_id = NVL(cp_interface_id, err.interface_hesa_id);
1118 c_del_err_rec c_del_err%ROWTYPE ;
1119
1120 -- check if person id type is setup
1121 CURSOR c_alt_persid_type( cp_persid_type igs_pe_person_id_typ.person_id_type%TYPE ) IS
1122 SELECT 'X'
1123 FROM igs_pe_person_id_typ
1124 WHERE person_id_type = cp_persid_type ;
1125
1126 -- Check if alternate id type HESA_INST for institution is setup
1127 CURSOR c_alt_orgid IS
1128 SELECT 'X'
1129 FROM igs_or_org_alt_idtyp
1130 WHERE system_id_type = 'HESA_INST'
1131 AND NVL (close_ind, 'N') = 'N' ;
1132
1133 l_altid VARCHAR2(1) ;
1134
1135 --Check whether the Source Category of Academic History is included within the source Type "UCAS PER" or not.
1136 CURSOR cur_pe_src_cat (cp_source_type_id igs_pe_src_types_all.source_type_id%TYPE) IS
1137 SELECT 'X'
1138 FROM igs_ad_source_cat_v
1139 WHERE source_type_id = cp_source_type_id
1140 AND category_name = 'PERSON_ACADEMIC_HISTORY'
1141 AND include_ind = 'Y';
1142
1143 -- Get all the interface records for the passed batch id
1144 -- smaddali modified this cursor to add trim to all columns to remove spaces as part of bug 2740653
1145 CURSOR c_interface IS
1146 SELECT TRIM(a.interface_hesa_id) interface_hesa_id , TRIM(a.batch_id) batch_id,
1147 UPPER(TRIM(a.husid)) husid , TRIM(a.person_number) person_number, TRIM(a.course_cd) course_cd,
1148 TRIM(a.unit_set_cd) unit_set_cd, UPPER(TRIM(a.ucasnum)) ucasnum, UPPER(TRIM(a.scotvec)) scotvec,
1149 TRIM(a.surname) surname, TRIM(a.given_names) given_names , (a.birth_dt) birth_dt,
1150 TRIM(a.sex) sex, TRIM(a.domicile_cd) domicile_cd, TRIM(a.country_code) country_code,
1151 TRIM(a.ethnic_origin) ethnic_origin, TRIM(a.disability_type) disability_type,
1152 a.prev_inst_left_date prev_inst_left_date, TRIM(a.occcode) occcode, TRIM(a.highest_qual_on_entry) highest_qual_on_entry,
1153 TRIM(a.subject_qualaim1) subject_qualaim1, TRIM(a.subject_qualaim2) subject_qualaim2 ,
1154 TRIM(a.subject_qualaim3) subject_qualaim3 ,TRIM(a.qualaim_proportion) qualaim_proportion,
1155 TRIM(a.fee_eligibility) fee_eligibility, TRIM(a.postcode) postcode, TRIM(a.social_class_ind) social_class_ind,
1156 TRIM(a.occupation_code) occupation_code, TRIM(a.inst_code) inst_code, TRIM(a.gceasn) gceasn,
1157 TRIM(a.gceasts) gceasts, TRIM(a.vceasn) vceasn, TRIM(a.vceasts) vceasts, TRIM(a.gcean) gcean,
1158 TRIM(a.gceats) gceats, TRIM(a.vcean) vcean, TRIM(a.vceats) vceats, TRIM(a.ksqn) ksqn,
1159 TRIM(a.ksqts) ksqts, TRIM(a.uksan) uksan, TRIM(a.uksats) uksats, TRIM(a.sahn) sahn,
1160 TRIM(a.sahts) sahts, TRIM(a.shn) shn, TRIM(a.shts) shts, TRIM(a.si2n) si2n, TRIM(a.si2ts) si2ts,
1161 TRIM(a.ssgcn) ssgcn, TRIM(a.ssgcts) ssgcts, TRIM(a.scsn) scsn, TRIM(a.scsts) scsts,
1162 TRIM(a.aean) aean, TRIM(a.aeats) aeats, TRIM(a.total_ucas_tariff) total_ucas_tariff
1163 FROM igs_he_ucas_imp_int a
1164 WHERE a.batch_id = p_batch_id ;
1165 c_interface_rec c_interface%ROWTYPE ;
1166
1167
1168 -- Get the person id for the passed alternate person id
1169 CURSOR c_alt_persid( cp_persid_type igs_pe_alt_pers_id.person_id_type%TYPE,
1170 cp_person_id igs_pe_alt_pers_id.api_person_id%TYPE ) IS
1171 SELECT party_number person_number
1172 FROM igs_pe_alt_pers_id , hz_parties
1173 WHERE person_id_type = cp_persid_type AND
1174 api_person_id = cp_person_id AND
1175 NVL(start_dt,SYSDATE) <= SYSDATE AND
1176 end_dt IS NULL AND
1177 party_id = pe_person_id
1178 AND (end_dt IS NULL OR start_dt <> end_dt);
1179
1180 c_alt_persid_rec c_alt_persid%ROWTYPE ;
1181
1182 l_person_number igs_pe_person.person_number%TYPE ;
1183
1184 -- get the student program attempt hesa record
1185 CURSOR c_spa( cp_person_id igs_he_st_spa.person_id%TYPE ,
1186 cp_course_cd igs_he_st_spa.course_cd%TYPE ) IS
1187 SELECT person_id ,course_cd , version_number
1188 FROM igs_he_st_spa_all
1189 WHERE person_id = cp_person_id AND
1190 course_cd = NVL(cp_course_cd,course_cd) ;
1191 l_spa c_spa%ROWTYPE;
1192
1193 -- get the student unit set attempt hesa record
1194 CURSOR c_susa( cp_person_id igs_he_en_susa.person_id%TYPE ,
1195 cp_course_cd igs_he_en_susa.course_cd%TYPE ,
1196 cp_unit_set_cd igs_he_en_susa.unit_set_cd%TYPE ) IS
1197 SELECT 'X'
1198 FROM igs_he_en_susa
1199 WHERE person_id = cp_person_id AND
1200 course_cd = NVL(cp_course_cd,course_cd) AND
1201 unit_set_cd = NVL(cp_unit_set_cd,unit_set_cd) ;
1202 l_susa VARCHAR2(1);
1203
1204 -- get oss value from ucas oss hesa association
1205 CURSOR c_mapping ( cp_assoc_code igs_he_code_map_val.association_code%TYPE ,
1206 cp_value igs_he_code_map_val.map3%TYPE ) IS
1207 SELECT map2
1208 FROM igs_he_code_map_val
1209 WHERE association_code = cp_assoc_code AND
1210 map3 = cp_value ;
1211
1212 -- get oss value from oss hesa association
1213 CURSOR c_mapping1 ( cp_assoc_code igs_he_code_map_val.association_code%TYPE ,
1214 cp_value igs_he_code_map_val.map1%TYPE ) IS
1215 SELECT map2
1216 FROM igs_he_code_map_val
1217 WHERE association_code = cp_assoc_code AND
1218 map1 = cp_value ;
1219
1220 -- get the oss code for highest qualification on entry field
1221 -- smaddali modified this cursor for bug 2726086 to compare highest_qual to grade instead of rank
1222 -- smaddali modified cursor topget onlyopen code values , bug 2730388
1223 -- modified the cursor replace the equal comparision with EXISTS for bug, 3463819
1224 CURSOR c_high_qual( cp_high_qual igs_as_grd_sch_grade.grade%TYPE ) IS
1225 SELECT grade
1226 FROM igs_as_grd_sch_grade gsg
1227 WHERE
1228 EXISTS( SELECT 'X' FROM igs_he_code_values
1229 WHERE code_type = 'HESA_HIGH_QUAL_ON_ENT' AND
1230 value = gsg.grading_schema_cd AND
1231 NVL(closed_ind,'N' ) = 'N' )
1232 AND gsg.grade= cp_high_qual
1233 AND ROWNUM < 2;
1234
1235 -- get the oss inst code
1236 CURSOR c_inst( cp_inst_code igs_or_org_alt_ids.org_alternate_id%TYPE ) IS
1237 SELECT ORG_STRUCTURE_ID
1238 FROM IGS_OR_ORG_ALT_IDS OAI,IGS_OR_ORG_ALT_IDTYP_V OAIT
1239 WHERE OAI.ORG_alternate_ID = CP_INST_CODE
1240 AND OAI.ORG_STRUCTURE_TYPE = 'INSTITUTE'
1241 AND TRUNC (SYSDATE) BETWEEN TRUNC (OAI.START_DATE) AND NVL (TRUNC (OAI.END_DATE), TRUNC (SYSDATE)+1)
1242 AND OAI.ORG_ALTERNATE_ID_TYPE = OAIT.ORG_ALTERNATE_ID_TYPE
1243 AND OAIT.SYSTEM_ID_TYPE = 'HESA_INST';
1244
1245 -- Get the oss field of study
1246 CURSOR c_field_study( cp_subject igs_ps_fld_of_study.field_of_study%TYPE) IS
1247 SELECT a.field_of_study
1248 FROM igs_ps_fld_of_study a
1249 WHERE a.govt_field_of_study = cp_subject AND
1250 a.closed_ind = 'N'
1251 ORDER BY a.field_of_study ;
1252
1253 -- Get the Batch ID for admission application import process
1254 CURSOR c_bat_id IS
1255 SELECT igs_ad_interface_batch_id_s.NEXTVAL
1256 FROM dual;
1257
1258 -- Get the Source type ID of UCAS for admission import process
1259 --smaddali modified this cursor to get the source type UCAS PER instead of UCAS APPL ,bug 2724140
1260 CURSOR c_src_type_id IS
1261 SELECT source_type_id
1262 FROM igs_pe_src_types_all
1263 WHERE source_type = 'UCAS PER'
1264 AND NVL(closed_ind,'N') = 'N';
1265
1266 c_src_type_id_rec c_src_type_id%ROWTYPE;
1267
1268 -- get the student program attempt hesa record for update
1269 CURSOR c_upd_spa( cp_person_id igs_he_st_spa.person_id%TYPE ,
1270 cp_course_cd igs_he_st_spa.course_cd%TYPE ) IS
1271 SELECT spa.rowid , spa.*
1272 FROM igs_he_st_spa_all spa
1273 WHERE spa.person_id = cp_person_id AND
1274 spa.course_cd = NVL(cp_course_cd,course_cd) ;
1275
1276 -- get the student unit set attempt hesa record
1277 CURSOR c_upd_susa( cp_person_id igs_he_en_susa.person_id%TYPE ,
1278 cp_course_cd igs_he_en_susa.course_cd%TYPE ,
1279 cp_unit_set_cd igs_he_en_susa.unit_set_cd%TYPE ) IS
1280 SELECT susa.rowid , susa.*
1281 FROM igs_he_en_susa susa
1282 WHERE susa.person_id = cp_person_id AND
1283 susa.course_cd = NVL(cp_course_cd,course_cd) AND
1284 susa.unit_set_cd = NVL(cp_unit_set_cd,unit_set_cd) ;
1285
1286 -- get all the ucas tariff records for the student
1287 CURSOR c_del_tariff( cp_person_id igs_he_st_spa_ut.person_id%TYPE ,
1288 cp_course_cd igs_he_st_spa_ut.course_cd%TYPE ) IS
1289 SELECT rowid
1290 FROM igs_he_st_spa_ut_all
1291 WHERE person_id = cp_person_id AND
1292 course_cd = cp_course_cd ;
1293
1294 -- get the Academic history record for the student
1295 CURSOR c_acad_hist ( cp_person_id igs_ad_acad_history_v.person_id%TYPE ,
1296 cp_inst_cd igs_ad_acad_history_v.institution_code%TYPE ) IS
1297 SELECT a.*
1298 FROM igs_ad_acad_history_v a
1299 WHERE a.person_id = cp_person_id
1300 AND a.institution_code = cp_inst_cd ;
1301 l_acad_hist_rec c_acad_hist%ROWTYPE ;
1302
1303 -- get the Academic history record for the student
1304 CURSOR c_acad_hist_count ( cp_person_id igs_ad_acad_history_v.person_id%TYPE ,
1305 cp_inst_cd igs_ad_acad_history_v.institution_code%TYPE ) IS
1306 SELECT COUNT(*)
1307 FROM igs_ad_acad_history_v a
1308 WHERE a.person_id = cp_person_id
1309 AND a.institution_code = cp_inst_cd ;
1310 l_acad_hist_count NUMBER(3);
1311
1312 -- Get the Person number for the passed person id.
1313 CURSOR c_person_info (cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
1314 SELECT person_number, last_name surname, first_name given_names, gender sex, birth_date birth_dt
1315 FROM igs_pe_person_base_v
1316 WHERE person_id = cp_person_id;
1317 l_person_info_rec c_person_info%ROWTYPE;
1318
1319 -- Get the Admission Interface ID while populating Acad Hisotry Interface table
1320 -- if it is already created as part of Person details import or not.
1321 CURSOR c_adm_int_id( cp_batch_id igs_ad_interface_all.batch_id%TYPE,
1322 cp_person_id igs_ad_interface_all.person_id%TYPE ) IS
1323 SELECT a.interface_id
1324 FROM igs_ad_interface_all a
1325 WHERE a.batch_id = cp_batch_id
1326 AND a.person_id= cp_person_id
1327 AND a.status = '2'
1328 AND a.record_status='2';
1329 l_interface_id igs_ad_interface_all.interface_id%TYPE ;
1330
1331 -- Get the admission application instance interface records whose import has failed
1332 CURSOR c_adm_int( cp_batch_id igs_ad_interface.batch_id%TYPE,
1333 cp_person_number igs_pe_person.person_number%TYPE ) IS
1334 SELECT a.person_number, a.interface_id
1335 FROM igs_ad_interface a
1336 WHERE a.batch_id = cp_batch_id AND
1337 a.person_number= cp_person_number AND
1338 ( a.status IN ('2','3') OR a.record_status='3' ) ;
1339 c_adm_int_rec c_adm_int%ROWTYPE ;
1340
1341 -- Get the Academic History interface records whose import has failed
1342 CURSOR c_acadhis_int (cp_interface_id igs_ad_acadhis_int_all.interface_id%TYPE) IS
1343 SELECT a.interface_acadhis_id
1344 FROM igs_ad_acadhis_int_all a
1345 WHERE a.interface_id = cp_interface_id
1346 AND a.status = '3';
1347 l_interface_acadhis_id igs_ad_acadhis_int_all.interface_acadhis_id%TYPE ;
1348
1349 -- Get the hesa import interface record for the passed person number
1350 -- smaddali modified this cursor to add trim to remove spaces as part of bug fix 2740653
1351 CURSOR c_imp_int IS
1352 SELECT trim(a.interface_hesa_id) interface_hesa_id , trim(a.person_number) person_number
1353 FROM igs_he_ucas_imp_int a
1354 WHERE a.batch_id = p_batch_id ;
1355 c_imp_int_rec c_imp_int%ROWTYPE ;
1356
1357 -- To get the External Calculation Type used for UCAS Tariff Calculation
1358 CURSOR cur_calc_type IS
1359 SELECT utct.tariff_calc_type_cd
1360 FROM IGS_HE_UT_CALC_TYPE utct
1361 WHERE utct.external_calc_ind = 'Y'
1362 AND utct.closed_ind = 'N' ;
1363 l_tariff_calc_type_cd IGS_HE_UT_CALC_TYPE.tariff_calc_type_cd%TYPE ;
1364
1365 -- To get the Default OSS Ward mapped to a HESA Tariff Level
1366 CURSOR cur_ut_award_map ( cp_calc_type IGS_HE_UT_LVL_AWARD.tariff_calc_type_cd%TYPE,
1367 cp_tariff_level_cd IGS_HE_UT_LVL_AWARD.tariff_level_cd%TYPE ) IS
1368 SELECT utla.award_cd
1369 FROM IGS_HE_UT_LVL_AWARD utla
1370 WHERE utla.tariff_calc_type_cd = cp_calc_type
1371 AND utla.tariff_level_cd = cp_tariff_level_cd
1372 AND utla.default_award_ind = 'Y'
1373 AND utla.closed_ind = 'N' ;
1374
1375 -- smaddali added these local variables to hold OSS Award code for each HESA UT qualification ,bug#2671022
1376 l_oss_gceasn IGS_HE_UT_LVL_AWARD.award_cd%TYPE;
1377 l_oss_vceasn IGS_HE_UT_LVL_AWARD.award_cd%TYPE;
1378 l_oss_gcean IGS_HE_UT_LVL_AWARD.award_cd%TYPE;
1379 l_oss_vcean IGS_HE_UT_LVL_AWARD.award_cd%TYPE;
1380 l_oss_ksqn IGS_HE_UT_LVL_AWARD.award_cd%TYPE;
1381 l_oss_uksan IGS_HE_UT_LVL_AWARD.award_cd%TYPE;
1382 l_oss_sahn IGS_HE_UT_LVL_AWARD.award_cd%TYPE;
1383 l_oss_shn IGS_HE_UT_LVL_AWARD.award_cd%TYPE;
1384 l_oss_si2n IGS_HE_UT_LVL_AWARD.award_cd%TYPE;
1385 l_oss_ssgcn IGS_HE_UT_LVL_AWARD.award_cd%TYPE;
1386 l_oss_scsn IGS_HE_UT_LVL_AWARD.award_cd%TYPE;
1387 l_oss_aean IGS_HE_UT_LVL_AWARD.award_cd%TYPE;
1388 l_call_pers_imp BOOLEAN ;
1389 l_return_status VARCHAR2(1);
1390 l_msg_data VARCHAR2(100);
1391
1392 BEGIN
1393
1394 --anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
1395 IGS_GE_GEN_003.SET_ORG_ID;
1396
1397 -- inititalize variables
1398 errbuf := NULL;
1399 l_no_setup := FALSE ;
1400 l_batch_id := p_batch_id ;
1401
1402 -- Checking whether the UK profile is enabled
1403 -- If country code is not set to GB then exit job
1404 IF Not (IGS_UC_UTILS.IS_UCAS_HESA_ENABLED) THEN
1405 Raise IGS_UC_HE_NOT_ENABLED_EXCEP; -- user defined exception
1406 END IF;
1407
1408 -- Validate the Parameters ,atleast one of person details or program attempt details should be imported
1409 -- Both p_spa_det and p_pers_det should not be having value N
1410 IF p_pers_det = 'N' AND p_spa_det = 'N' THEN
1411 FND_MESSAGE.SET_NAME('IGS','IGS_HE_IMP_INV_PARAM');
1412 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
1413 retcode := 3 ;
1414 RETURN ;
1415 END IF ;
1416
1417 -- Delete all the error records existing for the passed batch_id
1418 FOR c_del_err_rec IN c_del_err(NULL) LOOP
1419 igs_he_ucas_imp_err_pkg.delete_row( X_ROWID => c_del_err_rec.rowid ) ;
1420 END LOOP ;
1421
1422 -- Person Id type setup validations
1423 -- If a person id type HUSID is not setup then log error
1424 l_altid := NULL ;
1425 OPEN c_alt_persid_type('HUSID') ;
1426 FETCH c_alt_persid_type INTO l_altid ;
1427 IF c_alt_persid_type%NOTFOUND THEN
1428 FND_MESSAGE.SET_NAME('IGS','IGS_HE_NO_HUSID');
1429 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
1430 l_no_setup := TRUE ;
1431 END IF;
1432 CLOSE c_alt_persid_type ;
1433
1434 -- If a person if type of UCASID is not setup then log error
1435 l_altid := NULL ;
1436 OPEN c_alt_persid_type('UCASID') ;
1437 FETCH c_alt_persid_type INTO l_altid ;
1438 IF c_alt_persid_type%NOTFOUND THEN
1439 FND_MESSAGE.SET_NAME('IGS','IGS_HE_NO_UCASID');
1440 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
1441 l_no_setup := TRUE ;
1442 END IF;
1443 CLOSE c_alt_persid_type ;
1444
1445 -- If a person id type of UCASREGNO is not setup then log error
1446 l_altid := NULL ;
1447 OPEN c_alt_persid_type('UCASREGNO') ;
1448 FETCH c_alt_persid_type INTO l_altid ;
1449 IF c_alt_persid_type%NOTFOUND THEN
1450 FND_MESSAGE.SET_NAME('IGS','IGS_HE_NO_SCOTVEC');
1451 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
1452 l_no_setup := TRUE ;
1453 END IF;
1454 CLOSE c_alt_persid_type ;
1455
1456 -- If an institution alternate id type of HESA_INST has not been setup then log error
1457 l_altid := NULL ;
1458 OPEN c_alt_orgid ;
1459 FETCH c_alt_orgid INTO l_altid ;
1460 IF c_alt_orgid%NOTFOUND THEN
1461 FND_MESSAGE.SET_NAME('IGS','IGS_HE_NO_HESAINST');
1462 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
1463 l_no_setup := TRUE ;
1464 END IF;
1465 CLOSE c_alt_orgid ;
1466
1467 -- Check whether the Person Source Type 'UCAS PER' defined in the setup
1468 c_src_type_id_rec := NULL ;
1469 OPEN c_src_type_id;
1470 FETCH c_src_type_id INTO c_src_type_id_rec;
1471 IF c_src_type_id%NOTFOUND THEN
1472 fnd_message.set_name('IGS','IGS_UC_NO_UCAS_SRC_TYP');
1473 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
1474 l_no_setup := TRUE ;
1475 ELSE
1476 --Check whether the Source Category of Academic History is included within the source Type "UCAS PER" or not.
1477 l_altid := NULL ;
1478 OPEN cur_pe_src_cat(c_src_type_id_rec.source_type_id);
1479 FETCH cur_pe_src_cat INTO l_altid;
1480 IF cur_pe_src_cat%NOTFOUND THEN
1481 fnd_message.set_name('IGS','IGS_UC_SETUP_SRC_CAT');
1482 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
1483 l_no_setup := TRUE ;
1484 END IF;
1485 CLOSE cur_pe_src_cat;
1486 END IF;
1487 CLOSE c_src_type_id;
1488
1489 -- If setup is not found then end the job
1490 IF l_no_setup THEN
1491 -- end job in error state
1492 retcode := 3 ;
1493 RETURN ;
1494 END IF ;
1495
1496 -- If there are no interface records for the passed batch_id then log error and exit job
1497 c_interface_rec := NULL ;
1498 OPEN c_interface ;
1499 FETCH c_interface INTO c_interface_rec ;
1500 IF c_interface%NOTFOUND THEN
1501 CLOSE c_interface ;
1502 retcode := 3;
1503 FND_MESSAGE.SET_NAME('IGS','IGS_HE_NO_INT_RECS');
1504 FND_MESSAGE.SET_TOKEN('BATCH_ID',p_batch_id) ;
1505 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
1506 RETURN ;
1507 ELSE
1508 CLOSE c_interface ;
1509 END IF ;
1510
1511 l_call_pers_imp := FALSE ;
1512 -- If Import person details parameter is set to Yes then Generate the batch_id and source_type_id
1513 -- for calling the import process
1514 IF p_pers_det = 'Y' THEN
1515 -- Get the batch ID for populating person interface tables and
1516 -- running the application import process
1517 l_imp_batch_id := NULL ;
1518 OPEN c_bat_id;
1519 FETCH c_bat_id INTO l_imp_batch_id;
1520 CLOSE c_bat_id;
1521
1522 INSERT INTO igs_ad_imp_batch_det ( batch_id,
1523 batch_desc,
1524 created_by,
1525 creation_date,
1526 last_updated_by,
1527 last_update_date,
1528 last_update_login,
1529 request_id,
1530 program_application_id,
1531 program_update_date,
1532 program_id)
1533 VALUES ( l_imp_batch_id,
1534 fnd_message.get_string('IGS','IGS_HE_IMP_HESA_DET_BATCH_ID'),
1535 fnd_global.user_id,
1536 SYSDATE,
1537 fnd_global.user_id,
1538 SYSDATE,
1539 fnd_global.login_id,
1540 DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_request_id),
1541 DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.prog_appl_id),
1542 DECODE(fnd_global.conc_request_id,-1,NULL,SYSDATE),
1543 DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_program_id)
1544 );
1545 END IF ;
1546
1547 -- Process all the import interface reocrds for the passed Batch ID
1548 FOR c_interface_rec IN c_interface LOOP
1549 fnd_file.put_line( fnd_file.LOG ,' ');
1550 fnd_message.set_name('IGS','IGS_HE_PROC_INT');
1551 fnd_message.set_token('INTERFACE_ID',c_interface_rec.interface_hesa_id) ;
1552 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
1553
1554 l_error_flag := FALSE ;
1555 l_person_number := NULL ;
1556 l_oss_gceasn := NULL ;
1557 l_oss_vceasn := NULL ;
1558 l_oss_gcean := NULL ;
1559 l_oss_vcean := NULL ;
1560 l_oss_ksqn := NULL ;
1561 l_oss_uksan := NULL ;
1562 l_oss_sahn := NULL ;
1563 l_oss_shn := NULL ;
1564 l_oss_si2n := NULL ;
1565 l_oss_ssgcn := NULL ;
1566 l_oss_scsn := NULL ;
1567 l_oss_aean := NULL ;
1568 l_oss_gender := NULL ;
1569 l_oss_nation := NULL ;
1570 l_oss_disability := NULL ;
1571 l_oss_ethnicity := NULL ;
1572 l_oss_subj1 := NULL ;
1573 l_oss_subj2 := NULL ;
1574 l_oss_subj3 := NULL ;
1575 l_oss_proportion := NULL ;
1576 l_oss_domicile := NULL ;
1577 l_oss_occupation := NULL ;
1578 l_oss_social_class := NULL ;
1579 l_oss_inst := NULL ;
1580 l_oss_high_qual := NULL ;
1581 l_oss_fee_elig := NULL ;
1582
1583 l_ins_tariff := FALSE ;
1584 l_upd_spa := FALSE ;
1585 l_upd_person := FALSE ;
1586
1587
1588 -- Validate person identifier
1589 -- If person identifier is not provided for this interface record then log error
1590 IF c_interface_rec.HUSID IS NULL AND c_interface_rec.UCASNUM IS NULL AND
1591 c_interface_rec.PERSON_NUMBER IS NULL THEN
1592 log_error('E01' , c_interface_rec.interface_hesa_id , NULL ) ;
1593 l_error_flag := TRUE ;
1594 ELSE
1595 IF c_interface_rec.person_number IS NOT NULL THEN
1596 --If person number is given in the interface record then use that identify oss person
1597 l_person_number := c_interface_rec.person_number ;
1598 ELSIF c_interface_rec.husid IS NOT NULL THEN
1599 -- Elsif husid is given ,use it to get the oss person
1600 OPEN c_alt_persid('HUSID' , c_interface_rec.husid) ;
1601 FETCH c_alt_persid INTO c_alt_persid_rec ;
1602 IF c_alt_persid%FOUND THEN
1603 -- If the given husid is a valid alternate person id then get the person number
1604 CLOSE c_alt_persid ;
1605 l_person_number := c_alt_persid_rec.person_number ;
1606 ELSE
1607 -- If given husid is not a valid alternate person id then use ucasid
1608 CLOSE c_alt_persid ;
1609 OPEN c_alt_persid('UCASID' , c_interface_rec.ucasnum) ;
1610 FETCH c_alt_persid INTO c_alt_persid_rec ;
1611 IF c_alt_persid%FOUND THEN
1612 -- if ucasid is a valid alternate person id then get the person number
1613 l_person_number := c_alt_persid_rec.person_number ;
1614 END IF ;
1615 CLOSE c_alt_persid ;
1616 END IF ;
1617 ELSE -- husid is null but ucasid is not null in the interface record
1618 OPEN c_alt_persid('UCASID' , c_interface_rec.ucasnum) ;
1619 FETCH c_alt_persid INTO c_alt_persid_rec ;
1620 IF c_alt_persid%FOUND THEN
1621 -- if ucasid is a valid aleternate person id then get the person number
1622 l_person_number := c_alt_persid_rec.person_number ;
1623 END IF ;
1624 CLOSE c_alt_persid ;
1625 END IF ;
1626
1627 -- If the person number of the interface record is identified then get the oss person details
1628 IF l_person_number IS NOT NULL THEN
1629 OPEN c_pe_det( l_person_number) ;
1630 FETCH c_pe_det INTO c_pe_det_rec ;
1631 IF c_pe_det%NOTFOUND THEN
1632 -- If the oss person record for the identified person number is not found then log error
1633 log_error('E02' ,c_interface_rec.interface_hesa_id, NULL);
1634 l_error_flag := TRUE;
1635 ELSE
1636 -- If oss person record is found but the person identifier in the interface record is null
1637 -- then update the interface record with the identified person number
1638 IF c_interface_rec.person_number IS NULL THEN
1639 c_interface_rec.person_number := l_person_number ;
1640 UPDATE igs_he_ucas_imp_int SET person_number = l_person_number
1641 WHERE batch_id =p_batch_id AND interface_hesa_id = c_interface_rec.interface_hesa_id ;
1642 END IF ;
1643 END IF ;
1644 CLOSE c_pe_det ;
1645 ELSE
1646 -- If person identifier is not found then log an error
1647 log_error('E02' ,c_interface_rec.interface_hesa_id, NULL);
1648 l_error_flag := TRUE;
1649 END IF ; -- if person details found
1650
1651 END IF ; -- if person identifier found
1652
1653 -- If person found in oss then continue with the other validations ,else skip this record
1654 IF NOT l_error_flag THEN
1655
1656 -- validate program and unit set fields
1657 IF p_spa_det = 'Y' THEN
1658 -- check if the hesa program attempt record exists for this person
1659 OPEN c_spa(c_pe_det_rec.person_id,c_interface_rec.course_cd) ;
1660 FETCH c_spa INTO l_spa ;
1661 IF c_spa%NOTFOUND THEN
1662 IF c_interface_rec.course_cd IS NOT NULL THEN
1663 -- Log a message that no student program attempt hesa record found for
1664 -- the course specified in the interface record
1665 log_error('E03' ,c_interface_rec.interface_hesa_id, NULL);
1666 l_error_flag := TRUE;
1667 ELSE
1668 -- Log a message that no student program attempt hesa records found for
1669 -- the student in any program
1670 log_error('E05' ,c_interface_rec.interface_hesa_id, NULL);
1671 l_error_flag := TRUE;
1672 END IF ;
1673 END IF ;
1674 CLOSE c_spa ;
1675
1676 -- If fee eligibility needs to be imported then
1677 -- check if the hesa unit set attempt record exists for this person
1678 IF c_interface_rec.fee_eligibility IS NOT NULL THEN
1679 OPEN c_susa(c_pe_det_rec.person_id,c_interface_rec.course_cd,c_interface_rec.unit_set_cd) ;
1680 FETCH c_susa INTO l_susa ;
1681 IF c_susa%NOTFOUND THEN
1682 -- Log a message that no student unit set attempt hesa record found for
1683 -- the course and unit set specified in the interface record
1684 IF c_interface_rec.unit_set_cd IS NOT NULL THEN
1685 log_error('E04' ,c_interface_rec.interface_hesa_id, NULL);
1686 l_error_flag := TRUE;
1687 ELSE
1688 -- Log a message that no student unit set attempt hesa records found for
1689 -- the student in any unit set
1690 log_error('E06' ,c_interface_rec.interface_hesa_id, NULL);
1691 l_error_flag := TRUE;
1692 END IF ;
1693 END IF ;
1694 CLOSE c_susa ;
1695 END IF ; -- if fee eligibility is not null
1696 END IF ; -- import program details parameter is set to yes
1697
1698 -- If course and unit set validations passed then continue with the other validations
1699 -- else skip this record
1700 IF NOT l_error_flag THEN
1701
1702 -- if import person details parameter has value yes then validate if hesa coded person fields
1703 -- are mapped to corresponding oss codes
1704 IF p_pers_det = 'Y' THEN
1705
1706 -- if sex is not mapped to oss gender code then log error
1707 IF c_interface_rec.sex IS NOT NULL THEN
1708 l_upd_person := TRUE ;
1709 OPEN c_mapping('UC_OSS_HE_GEN_ASSOC' , c_interface_rec.sex) ;
1710 FETCH c_mapping INTO l_oss_gender ;
1711 IF c_mapping%NOTFOUND THEN
1712 log_error('E07' ,c_interface_rec.interface_hesa_id, NULL);
1713 l_error_flag := TRUE;
1714 END IF ;
1715 CLOSE c_mapping ;
1716 END IF ;
1717
1718 -- if country is not mapped to oss nationality code the log error
1719 IF c_interface_rec.country_code IS NOT NULL THEN
1720 l_upd_person := TRUE ;
1721 OPEN c_mapping('UC_OSS_HE_NAT_ASSOC' , c_interface_rec.country_code) ;
1722 FETCH c_mapping INTO l_oss_nation ;
1723 IF c_mapping%NOTFOUND THEN
1724 log_error('E08' ,c_interface_rec.interface_hesa_id, NULL);
1725 l_error_flag := TRUE;
1726 END IF ;
1727 CLOSE c_mapping ;
1728 END IF ;
1729
1730 -- if disability type is not mapped to oss disability code then log error
1731 IF c_interface_rec.disability_type IS NOT NULL THEN
1732 l_upd_person := TRUE ;
1733 OPEN c_mapping1('OSS_HESA_DISABILITY_ASSOC' , c_interface_rec.disability_type) ;
1734 FETCH c_mapping1 INTO l_oss_disability ;
1735 IF c_mapping1%NOTFOUND THEN
1736 log_error('E09' ,c_interface_rec.interface_hesa_id, NULL);
1737 l_error_flag := TRUE;
1738 END IF ;
1739 CLOSE c_mapping1 ;
1740 END IF ;
1741
1742 -- if ethnicity is not mapped to oss ethnicity code then log error
1743 IF c_interface_rec.ethnic_origin IS NOT NULL THEN
1744 l_upd_person := TRUE ;
1745 OPEN c_mapping('UC_OSS_HE_ETH_ASSOC' , c_interface_rec.ethnic_origin) ;
1746 FETCH c_mapping INTO l_oss_ethnicity ;
1747 IF c_mapping%NOTFOUND THEN
1748 log_error('E10' ,c_interface_rec.interface_hesa_id, NULL);
1749 l_error_flag := TRUE;
1750 END IF ;
1751 CLOSE c_mapping ;
1752 END IF ;
1753
1754
1755 -- if inst_code is not mapped to oss institution code then log error
1756 IF c_interface_rec.inst_code IS NOT NULL THEN
1757 --Get the OSS Institution Code from the Alternate Institution Codes where Code Type = HESA_INST
1758 OPEN c_inst( c_interface_rec.inst_code) ;
1759 FETCH c_inst INTO l_oss_inst ;
1760 CLOSE c_inst ;
1761
1762 IF l_oss_inst IS NULL THEN
1763 IF SUBSTR (c_interface_rec.inst_code,1,1) = 'U' THEN
1764 --Get the OSS Institution Code(map2) from HESA Mapping with Association code as UC_OSS_HE_INS_ASSOC
1765 -- and map1 as UCAS Institute code without left most character.
1766 OPEN c_mapping1('UC_OSS_HE_INS_ASSOC', SUBSTR(c_interface_rec.inst_code,2,LENGTH(c_interface_rec.inst_code)) );
1767 FETCH c_mapping1 INTO l_oss_inst;
1768 CLOSE c_mapping1;
1769 IF l_oss_inst IS NULL THEN
1770 log_error('E18' ,c_interface_rec.interface_hesa_id, NULL);
1771 l_error_flag := TRUE;
1772 END IF;
1773 ELSE
1774 log_error('E18' ,c_interface_rec.interface_hesa_id, NULL);
1775 l_error_flag := TRUE;
1776 END IF;
1777 END IF ;
1778
1779 END IF ;
1780
1781 -- If prev_inst_left_date is given without giving the institute last attended then log error
1782 IF c_interface_rec.prev_inst_left_date IS NOT NULL AND c_interface_rec.inst_code IS NULL THEN
1783 log_error('E28' ,c_interface_rec.interface_hesa_id, NULL);
1784 l_error_flag := TRUE;
1785 END IF ;
1786
1787
1788 -- if any person details have been populated then we need to populate the person import interface tables
1789 IF c_interface_rec.given_names IS NOT NULL OR
1790 c_interface_rec.surname IS NOT NULL OR
1791 c_interface_rec.ucasnum IS NOT NULL OR
1792 c_interface_rec.husid IS NOT NULL OR
1793 c_interface_rec.scotvec IS NOT NULL THEN
1794 l_upd_person := TRUE ;
1795 END IF ;
1796
1797 END IF; -- person details being imported
1798
1799 -- if import program attempt details parameter has value yes then validate if program attempt
1800 -- hesa coded fields are mapped to corresponding oss values
1801 IF p_spa_det = 'Y' THEN
1802
1803 -- if subject of qualification aim1 is not mapped to oss field of study then log error
1804 IF c_interface_rec.subject_qualaim1 IS NOT NULL THEN
1805 l_upd_spa := TRUE ;
1806 OPEN c_field_study(c_interface_rec.subject_qualaim1) ;
1807 FETCH c_field_study INTO l_oss_subj1 ;
1808 IF c_field_study%NOTFOUND THEN
1809 log_error('E11' ,c_interface_rec.interface_hesa_id,'1');
1810 l_error_flag := TRUE;
1811 END IF ;
1812 CLOSE c_field_study ;
1813 END IF ;
1814
1815 -- if subject of qualification aim2 is not mapped to oss field of study then log error
1816 IF c_interface_rec.subject_qualaim2 IS NOT NULL THEN
1817 l_upd_spa := TRUE ;
1818 OPEN c_field_study(c_interface_rec.subject_qualaim2) ;
1819 FETCH c_field_study INTO l_oss_subj2 ;
1820 IF c_field_study%NOTFOUND THEN
1821 log_error('E11' ,c_interface_rec.interface_hesa_id,'2');
1822 l_error_flag := TRUE;
1823 END IF ;
1824 CLOSE c_field_study ;
1825 END IF ;
1826
1827 -- if subject of qualification aim3 is not mapped to oss field of study then log error
1828 IF c_interface_rec.subject_qualaim3 IS NOT NULL THEN
1829 l_upd_spa := TRUE ;
1830 OPEN c_field_study(c_interface_rec.subject_qualaim3) ;
1831 FETCH c_field_study INTO l_oss_subj3 ;
1832 IF c_field_study%NOTFOUND THEN
1833 log_error('E11' ,c_interface_rec.interface_hesa_id,'3');
1834 l_error_flag := TRUE;
1835 END IF ;
1836 CLOSE c_field_study ;
1837 END IF ;
1838
1839
1840 -- if qualification aim proportion is not mapped to oss proportion code then log error
1841 IF c_interface_rec.qualaim_proportion IS NOT NULL THEN
1842 l_upd_spa := TRUE ;
1843 OPEN c_mapping1('OSS_HESA_PROPORTION_ASSOC' , c_interface_rec.qualaim_proportion) ;
1844 FETCH c_mapping1 INTO l_oss_proportion ;
1845 IF c_mapping1%NOTFOUND THEN
1846 log_error('E12' ,c_interface_rec.interface_hesa_id, NULL);
1847 l_error_flag := TRUE;
1848 END IF ;
1849 CLOSE c_mapping1 ;
1850 END IF ;
1851
1852 -- if domicile is not mapped to oss domicile code then log error
1853 IF c_interface_rec.domicile_cd IS NOT NULL THEN
1854 l_upd_spa := TRUE ;
1855 OPEN c_mapping('UC_OSS_HE_DOM_ASSOC' , c_interface_rec.domicile_cd) ;
1856 FETCH c_mapping INTO l_oss_domicile ;
1857 IF c_mapping%NOTFOUND THEN
1858 log_error('E13' ,c_interface_rec.interface_hesa_id, NULL);
1859 l_error_flag := TRUE;
1860 END IF ;
1861 CLOSE c_mapping ;
1862 END IF ;
1863
1864 -- if occupation is not mapped to oss occupation code then log error
1865 IF c_interface_rec.occupation_code IS NOT NULL THEN
1866 l_upd_spa := TRUE ;
1867 OPEN c_mapping('UC_OSS_HE_OCC_ASSOC' , c_interface_rec.occupation_code) ;
1868 FETCH c_mapping INTO l_oss_occupation ;
1869 IF c_mapping%NOTFOUND THEN
1870 log_error('E14' ,c_interface_rec.interface_hesa_id, NULL);
1871 l_error_flag := TRUE;
1872 END IF ;
1873 CLOSE c_mapping ;
1874 END IF ;
1875
1876 -- if social class is not mapped to oss social class code then log error
1877 IF c_interface_rec.social_class_ind IS NOT NULL THEN
1878 l_upd_spa := TRUE ;
1879 OPEN c_mapping('UC_OSS_HE_SOC_ASSOC' , c_interface_rec.social_class_ind) ;
1880 FETCH c_mapping INTO l_oss_social_class ;
1881 IF c_mapping%NOTFOUND THEN
1882 log_error('E15' ,c_interface_rec.interface_hesa_id, NULL);
1883 l_error_flag := TRUE;
1884 END IF ;
1885 CLOSE c_mapping ;
1886 END IF ;
1887
1888 -- if highest qualification is not mapped to oss grade then log error
1889 IF c_interface_rec.highest_qual_on_entry IS NOT NULL THEN
1890 l_upd_spa := TRUE ;
1891 OPEN c_high_qual( c_interface_rec.highest_qual_on_entry) ;
1892 FETCH c_high_qual INTO l_oss_high_qual ;
1893 IF c_high_qual%NOTFOUND THEN
1894 log_error('E16' ,c_interface_rec.interface_hesa_id, NULL);
1895 l_error_flag := TRUE;
1896 END IF ;
1897 CLOSE c_high_qual ;
1898 END IF ;
1899
1900 -- if fee eligibility is not mapped to oss code then log error
1901 IF c_interface_rec.fee_eligibility IS NOT NULL THEN
1902 OPEN c_mapping1('OSS_HESA_FEEELIG_ASSOC' , c_interface_rec.fee_eligibility) ;
1903 FETCH c_mapping1 INTO l_oss_fee_elig ;
1904 IF c_mapping1%NOTFOUND THEN
1905 log_error('E17' ,c_interface_rec.interface_hesa_id, NULL);
1906 l_error_flag := TRUE;
1907 END IF ;
1908 CLOSE c_mapping1 ;
1909 END IF ;
1910
1911 -- if number of qualifications is given but tariff score is not given for any qualification then log error
1912 IF c_interface_rec.gceasn IS NOT NULL AND c_interface_rec.gceasts IS NULL THEN
1913 log_error('E19' ,c_interface_rec.interface_hesa_id, 'GCSEAS');
1914 l_error_flag := TRUE;
1915 END IF ;
1916
1917 IF c_interface_rec.vceasn IS NOT NULL AND c_interface_rec.vceasts IS NULL THEN
1918 log_error('E19' ,c_interface_rec.interface_hesa_id, 'VCSEAS');
1919 l_error_flag := TRUE;
1920 END IF ;
1921
1922 IF c_interface_rec.gcean IS NOT NULL AND c_interface_rec.gceats IS NULL THEN
1923 log_error('E19' ,c_interface_rec.interface_hesa_id, 'GCSEA');
1924 l_error_flag := TRUE;
1925 END IF ;
1926
1927 IF c_interface_rec.vcean IS NOT NULL AND c_interface_rec.vceats IS NULL THEN
1928 log_error('E19' ,c_interface_rec.interface_hesa_id, 'VCSEA');
1929 l_error_flag := TRUE;
1930 END IF ;
1931
1932 IF c_interface_rec.ksqn IS NOT NULL AND c_interface_rec.ksqts IS NULL THEN
1933 log_error('E19' ,c_interface_rec.interface_hesa_id, 'KEYSKL');
1934 l_error_flag := TRUE;
1935 END IF ;
1936
1937 IF c_interface_rec.uksan IS NOT NULL AND c_interface_rec.uksats IS NULL THEN
1938 log_error('E19' ,c_interface_rec.interface_hesa_id, '1UNKEYSKL');
1939 l_error_flag := TRUE;
1940 END IF ;
1941
1942 IF c_interface_rec.sahn IS NOT NULL AND c_interface_rec.sahts IS NULL THEN
1943 log_error('E19' ,c_interface_rec.interface_hesa_id, 'SCOTADH');
1944 l_error_flag := TRUE;
1945 END IF ;
1946
1947 IF c_interface_rec.shn IS NOT NULL AND c_interface_rec.shts IS NULL THEN
1948 log_error('E19' ,c_interface_rec.interface_hesa_id, 'SCOTH');
1949 l_error_flag := TRUE;
1950 END IF ;
1951
1952 IF c_interface_rec.si2n IS NOT NULL AND c_interface_rec.si2ts IS NULL THEN
1953 log_error('E19' ,c_interface_rec.interface_hesa_id, 'SCOTI2');
1954 l_error_flag := TRUE;
1955 END IF ;
1956
1957 IF c_interface_rec.ssgcn IS NOT NULL AND c_interface_rec.ssgcts IS NULL THEN
1958 log_error('E19' ,c_interface_rec.interface_hesa_id, 'SCOTST');
1959 l_error_flag := TRUE;
1960 END IF ;
1961
1962 IF c_interface_rec.scsn IS NOT NULL AND c_interface_rec.scsts IS NULL THEN
1963 log_error('E19' ,c_interface_rec.interface_hesa_id, 'SCOTCO');
1964 l_error_flag := TRUE;
1965 END IF ;
1966
1967
1968 -- if number of qualifications is not given but tariff score is given for any qualification then log error
1969 IF c_interface_rec.gceasn IS NULL AND c_interface_rec.gceasts IS NOT NULL THEN
1970 log_error('E20' ,c_interface_rec.interface_hesa_id, 'GCSEAS');
1971 l_error_flag := TRUE;
1972 END IF ;
1973
1974 IF c_interface_rec.vceasn IS NULL AND c_interface_rec.vceasts IS NOT NULL THEN
1975 log_error('E20' ,c_interface_rec.interface_hesa_id, 'VCSEAS');
1976 l_error_flag := TRUE;
1977 END IF ;
1978
1979 IF c_interface_rec.gcean IS NULL AND c_interface_rec.gceats IS NOT NULL THEN
1980 log_error('E20' ,c_interface_rec.interface_hesa_id, 'GCSEA');
1981 l_error_flag := TRUE;
1982 END IF ;
1983
1984 IF c_interface_rec.vcean IS NULL AND c_interface_rec.vceats IS NOT NULL THEN
1985 log_error('E20' ,c_interface_rec.interface_hesa_id, 'VCSEA');
1986 l_error_flag := TRUE;
1987 END IF ;
1988
1989 IF c_interface_rec.ksqn IS NULL AND c_interface_rec.ksqts IS NOT NULL THEN
1990 log_error('E20' ,c_interface_rec.interface_hesa_id, 'KEYSKL');
1991 l_error_flag := TRUE;
1992 END IF ;
1993
1994 IF c_interface_rec.uksan IS NULL AND c_interface_rec.uksats IS NOT NULL THEN
1995 log_error('E20' ,c_interface_rec.interface_hesa_id, '1UNKEYSKL');
1996 l_error_flag := TRUE;
1997 END IF ;
1998
1999 IF c_interface_rec.sahn IS NULL AND c_interface_rec.sahts IS NOT NULL THEN
2000 log_error('E20' ,c_interface_rec.interface_hesa_id, 'SCOTADH');
2001 l_error_flag := TRUE;
2002 END IF ;
2003
2004 IF c_interface_rec.shn IS NULL AND c_interface_rec.shts IS NOT NULL THEN
2005 log_error('E20' ,c_interface_rec.interface_hesa_id, 'SCOTH');
2006 l_error_flag := TRUE;
2007 END IF ;
2008
2009 IF c_interface_rec.si2n IS NULL AND c_interface_rec.si2ts IS NOT NULL THEN
2010 log_error('E20' ,c_interface_rec.interface_hesa_id, 'SCOTI2');
2011 l_error_flag := TRUE;
2012 END IF ;
2013
2014 IF c_interface_rec.ssgcn IS NULL AND c_interface_rec.ssgcts IS NOT NULL THEN
2015 log_error('E20' ,c_interface_rec.interface_hesa_id, 'SCOTST');
2016 l_error_flag := TRUE;
2017 END IF ;
2018
2019 IF c_interface_rec.scsn IS NULL AND c_interface_rec.scsts IS NOT NULL THEN
2020 log_error('E20' ,c_interface_rec.interface_hesa_id, 'SCOTCO');
2021 l_error_flag := TRUE;
2022 END IF ;
2023
2024 -- Check whether any of the UCAS Tariff fields is imported
2025 IF c_interface_rec.gceasn IS NOT NULL OR c_interface_rec.vceasn IS NOT NULL
2026 OR c_interface_rec.gcean IS NOT NULL OR c_interface_rec.vcean IS NOT NULL
2027 OR c_interface_rec.ksqn IS NOT NULL OR c_interface_rec.uksan IS NOT NULL
2028 OR c_interface_rec.sahn IS NOT NULL OR c_interface_rec.shn IS NOT NULL
2029 OR c_interface_rec.si2n IS NOT NULL OR c_interface_rec.ssgcn IS NOT NULL
2030 OR c_interface_rec.scsn IS NOT NULL OR c_interface_rec.aean IS NOT NULL
2031 OR c_interface_rec.aeats IS NOT NULL THEN
2032
2033 -- Check whether UCAS Tariff Calculation Type setup is defined for External Caculation Type
2034 -- If not find, then log the error
2035 OPEN cur_calc_type;
2036 FETCH cur_calc_type INTO l_tariff_calc_type_cd;
2037 IF cur_calc_type%NOTFOUND THEN
2038 log_error('E35', c_interface_rec.interface_hesa_id,NULL );
2039 l_error_flag := TRUE;
2040 END IF;
2041 CLOSE cur_calc_type;
2042
2043 IF NOT l_error_flag THEN
2044
2045 -- if OSS Award code is not mapped for any qualification then log error
2046 IF c_interface_rec.gceasn IS NOT NULL OR c_interface_rec.gceasts IS NOT NULL THEN
2047 OPEN cur_ut_award_map ( l_tariff_calc_type_cd,'GCSEAS' );
2048 FETCH cur_ut_award_map INTO l_oss_gceasn ;
2049 IF cur_ut_award_map%NOTFOUND THEN
2050 log_error('E33' ,c_interface_rec.interface_hesa_id, 'GCSEAS');
2051 l_error_flag := TRUE;
2052 END IF ;
2053 CLOSE cur_ut_award_map ;
2054 END IF ;
2055
2056 IF c_interface_rec.vceasn IS NOT NULL OR c_interface_rec.vceasts IS NOT NULL THEN
2057 OPEN cur_ut_award_map( l_tariff_calc_type_cd ,'VCSEAS' ) ;
2058 FETCH cur_ut_award_map INTO l_oss_vceasn ;
2059 IF cur_ut_award_map%NOTFOUND THEN
2060 log_error('E33' ,c_interface_rec.interface_hesa_id, 'VCSEAS');
2061 l_error_flag := TRUE;
2062 END IF ;
2063 CLOSE cur_ut_award_map ;
2064 END IF ;
2065
2066 IF c_interface_rec.gcean IS NOT NULL OR c_interface_rec.gceats IS NOT NULL THEN
2067 OPEN cur_ut_award_map(l_tariff_calc_type_cd , 'GCSEA' ) ;
2068 FETCH cur_ut_award_map INTO l_oss_gcean ;
2069 IF cur_ut_award_map%NOTFOUND THEN
2070 log_error('E33' ,c_interface_rec.interface_hesa_id, 'GCSEA');
2071 l_error_flag := TRUE;
2072 END IF ;
2073 CLOSE cur_ut_award_map ;
2074 END IF ;
2075
2076 IF c_interface_rec.vcean IS NOT NULL OR c_interface_rec.vceats IS NOT NULL THEN
2077 OPEN cur_ut_award_map(l_tariff_calc_type_cd , 'VCSEA') ;
2078 FETCH cur_ut_award_map INTO l_oss_vcean ;
2079 IF cur_ut_award_map%NOTFOUND THEN
2080 log_error('E33' ,c_interface_rec.interface_hesa_id, 'VCSEA');
2081 l_error_flag := TRUE;
2082 END IF ;
2083 CLOSE cur_ut_award_map ;
2084 END IF ;
2085
2086 IF c_interface_rec.ksqn IS NOT NULL OR c_interface_rec.ksqts IS NOT NULL THEN
2087 OPEN cur_ut_award_map(l_tariff_calc_type_cd , 'KEYSKL') ;
2088 FETCH cur_ut_award_map INTO l_oss_ksqn ;
2089 IF cur_ut_award_map%NOTFOUND THEN
2090 log_error('E33' ,c_interface_rec.interface_hesa_id, 'KEYSKL');
2091 l_error_flag := TRUE;
2092 END IF ;
2093 CLOSE cur_ut_award_map ;
2094 END IF ;
2095
2096 IF c_interface_rec.uksan IS NOT NULL OR c_interface_rec.uksats IS NOT NULL THEN
2097 OPEN cur_ut_award_map(l_tariff_calc_type_cd , '1UNKEYSKL') ;
2098 FETCH cur_ut_award_map INTO l_oss_uksan;
2099 IF cur_ut_award_map%NOTFOUND THEN
2100 log_error('E33' ,c_interface_rec.interface_hesa_id, '1UNKEYSKL');
2101 l_error_flag := TRUE;
2102 END IF ;
2103 CLOSE cur_ut_award_map ;
2104 END IF ;
2105
2106 IF c_interface_rec.sahn IS NOT NULL OR c_interface_rec.sahts IS NOT NULL THEN
2107 OPEN cur_ut_award_map(l_tariff_calc_type_cd , 'SCOTADH') ;
2108 FETCH cur_ut_award_map INTO l_oss_sahn ;
2109 IF cur_ut_award_map%NOTFOUND THEN
2110 log_error('E33' ,c_interface_rec.interface_hesa_id, 'SCOTADH');
2111 l_error_flag := TRUE;
2112 END IF ;
2113 CLOSE cur_ut_award_map ;
2114 END IF ;
2115
2116 IF c_interface_rec.shn IS NOT NULL OR c_interface_rec.shts IS NOT NULL THEN
2117 OPEN cur_ut_award_map(l_tariff_calc_type_cd , 'SCOTH') ;
2118 FETCH cur_ut_award_map INTO l_oss_shn ;
2119 IF cur_ut_award_map%NOTFOUND THEN
2120 log_error('E33' ,c_interface_rec.interface_hesa_id, 'SCOTH');
2121 l_error_flag := TRUE;
2122 END IF ;
2123 CLOSE cur_ut_award_map ;
2124 END IF ;
2125
2126 IF c_interface_rec.si2n IS NOT NULL OR c_interface_rec.si2ts IS NOT NULL THEN
2127 OPEN cur_ut_award_map(l_tariff_calc_type_cd , 'SCOTI2') ;
2128 FETCH cur_ut_award_map INTO l_oss_si2n ;
2129 IF cur_ut_award_map%NOTFOUND THEN
2130 log_error('E33' ,c_interface_rec.interface_hesa_id, 'SCOTI2');
2131 l_error_flag := TRUE;
2132 END IF ;
2133 CLOSE cur_ut_award_map ;
2134 END IF ;
2135
2136 IF c_interface_rec.ssgcn IS NOT NULL OR c_interface_rec.ssgcts IS NOT NULL THEN
2137 OPEN cur_ut_award_map(l_tariff_calc_type_cd , 'SCOTST') ;
2138 FETCH cur_ut_award_map INTO l_oss_ssgcn ;
2139 IF cur_ut_award_map%NOTFOUND THEN
2140 log_error('E33' ,c_interface_rec.interface_hesa_id, 'SCOTST');
2141 l_error_flag := TRUE;
2142 END IF ;
2143 CLOSE cur_ut_award_map ;
2144 END IF ;
2145
2146 IF c_interface_rec.scsn IS NOT NULL OR c_interface_rec.scsts IS NOT NULL THEN
2147 OPEN cur_ut_award_map(l_tariff_calc_type_cd , 'SCOTCO') ;
2148 FETCH cur_ut_award_map INTO l_oss_scsn ;
2149 IF cur_ut_award_map%NOTFOUND THEN
2150 log_error('E33' ,c_interface_rec.interface_hesa_id, 'SCOTCO');
2151 l_error_flag := TRUE;
2152 END IF ;
2153 CLOSE cur_ut_award_map ;
2154 END IF ;
2155
2156 IF c_interface_rec.aean IS NOT NULL OR c_interface_rec.aeats IS NOT NULL THEN
2157 OPEN cur_ut_award_map(l_tariff_calc_type_cd , 'ADVEXT') ;
2158 FETCH cur_ut_award_map INTO l_oss_aean ;
2159 IF cur_ut_award_map%NOTFOUND THEN
2160 log_error('E33' ,c_interface_rec.interface_hesa_id,'ADVEXT');
2161 l_error_flag := TRUE;
2162 END IF ;
2163 CLOSE cur_ut_award_map ;
2164 END IF ;
2165
2166 END IF; /* End of checking the UCAS Tariff Calculation Setup exist */
2167
2168 END IF ; /* End of validating the UCAS Tariff related fields */
2169
2170 -- If total tariff is given without individual tariffs present , then log error
2171 IF c_interface_rec.total_ucas_tariff IS NOT NULL AND c_interface_rec.aeats IS NULL AND
2172 c_interface_rec.scsts IS NULL AND c_interface_rec.ssgcts IS NULL AND
2173 c_interface_rec.si2ts IS NULL AND c_interface_rec.shts IS NULL AND
2174 c_interface_rec.sahts IS NULL AND c_interface_rec.uksats IS NULL AND
2175 c_interface_rec.ksqts IS NULL AND c_interface_rec.vceats IS NULL AND
2176 c_interface_rec.gceats IS NULL AND c_interface_rec.vceasts IS NULL AND
2177 c_interface_rec.gceasts IS NULL THEN
2178 log_error('E27' ,c_interface_rec.interface_hesa_id, NULL);
2179 l_error_flag := TRUE;
2180 END IF ;
2181
2182 -- check if ucas tariff records need to be created , i.e if tariff details have been populated or not
2183 IF c_interface_rec.aeats IS NOT NULL OR
2184 c_interface_rec.scsts IS NOT NULL OR c_interface_rec.ssgcts IS NOT NULL OR
2185 c_interface_rec.si2ts IS NOT NULL OR c_interface_rec.shts IS NOT NULL OR
2186 c_interface_rec.sahts IS NOT NULL OR c_interface_rec.uksats IS NOT NULL OR
2187 c_interface_rec.ksqts IS NOT NULL OR c_interface_rec.vceats IS NOT NULL OR
2188 c_interface_rec.gceats IS NOT NULL OR c_interface_rec.vceasts IS NOT NULL OR
2189 c_interface_rec.gceasts IS NOT NULL THEN
2190 l_ins_tariff := TRUE;
2191 END IF ;
2192
2193 -- check if spa hesa record needs to be updated
2194 IF c_interface_rec.total_ucas_tariff IS NOT NULL OR c_interface_rec.postcode IS NOT NULL OR
2195 c_interface_rec.occcode IS NOT NULL THEN
2196 l_upd_spa := TRUE ;
2197 END IF ;
2198
2199 END IF ; -- import student program details
2200
2201 -- If hesa code validations passed then continue with the import of person and program details
2202 -- else skip this record
2203 IF NOT l_error_flag THEN
2204
2205 -- if import person details parameter is set then populate person interface tables
2206 IF p_pers_det = 'Y' AND l_upd_person THEN
2207
2208 -- Set flag that person import process needs to be called
2209 l_call_pers_imp := TRUE ;
2210 -- Populate the admission person import interface tables
2211 populate_imp_int ( c_src_type_id_rec.source_type_id, l_imp_batch_id, l_org_id ,
2212 c_pe_det_rec.person_id , c_interface_rec.interface_hesa_id);
2213
2214
2215 -- Import academic history details along with person details
2216 -- If institute_cd is given then create or upadate attendance history records
2217 IF c_interface_rec.inst_code IS NOT NULL THEN
2218 --Get the Perosn Number for the Person ID passed.
2219 OPEN c_person_info(c_pe_det_rec.person_id);
2220 FETCH c_person_info INTO l_person_info_rec;
2221 CLOSE c_person_info;
2222
2223 --Check if there exists a Academic History record for the person and OSS Institution Code.
2224 OPEN c_acad_hist(c_pe_det_rec.person_id , l_oss_inst);
2225 FETCH c_acad_hist INTO l_acad_hist_rec ;
2226
2227 IF c_acad_hist%FOUND THEN
2228 CLOSE c_acad_hist ;
2229 --Check If there are multiple Academic History records for the person and OSS institution passed.
2230 OPEN c_acad_hist_count(c_pe_det_rec.person_id , l_oss_inst);
2231 FETCH c_acad_hist_count INTO l_acad_hist_count ;
2232 CLOSE c_acad_hist_count ;
2233
2234 --When there are more than 1 Academic History records existing for Person and OSS Institution passed
2235 IF l_acad_hist_count > 1 THEN
2236 --Log a message asking users for mannual review and update
2237 fnd_message.set_name('IGS','IGS_UC_ACAD_HIST_REC_EXISTS');
2238 fnd_message.set_token('PERSON_NO',l_person_info_rec.person_number);
2239 fnd_message.set_token('INST', l_oss_inst);
2240 fnd_message.set_token('END_DT', TO_CHAR(c_interface_rec.prev_inst_left_date,'DD-MON-YYYY'));
2241 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2242
2243 ELSE
2244 --When only one Academic History record exists then Check the discrepancy rule set for Source Type "UCAS PER"
2245 -- is set to "Updating Existing Values With Imported Values" or not.
2246 IF igs_ad_imp_001.find_source_cat_rule (c_src_type_id_rec.source_type_id, 'PERSON_ACADEMIC_HISTORY') <> 'I' THEN
2247 --Log a message asking users for mannual review and update
2248 fnd_message.set_name('IGS','IGS_UC_DSCRPNCY_RULE_NOT_SET');
2249 fnd_message.set_token('PERSON_NO',l_person_info_rec.person_number);
2250 fnd_message.set_token('INST', l_oss_inst);
2251 fnd_message.set_token('END_DT', TO_CHAR(c_interface_rec.prev_inst_left_date,'DD-MON-YYYY'));
2252 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2253 ELSE
2254 --When the discrepancy is set to import then Update the history record for the student with the date left
2255 BEGIN
2256 l_return_status := NULL;
2257 l_msg_data := NULL;
2258 Igs_Ad_Acad_History_Pkg.update_row (
2259 x_rowid => l_acad_hist_rec.row_id,
2260 x_attribute14 => l_acad_hist_rec.attribute14,
2261 x_attribute15 => l_acad_hist_rec.attribute15,
2262 x_attribute16 => l_acad_hist_rec.attribute16,
2263 x_attribute17 => l_acad_hist_rec.attribute17,
2264 x_attribute18 => l_acad_hist_rec.attribute18,
2265 x_attribute19 => l_acad_hist_rec.attribute19,
2266 x_attribute20 => l_acad_hist_rec.attribute20,
2267 x_attribute13 => l_acad_hist_rec.attribute13,
2268 x_attribute11 => l_acad_hist_rec.attribute11,
2269 x_attribute12 => l_acad_hist_rec.attribute12,
2270 x_education_id => l_acad_hist_rec.Education_Id,
2271 x_person_id => l_acad_hist_rec.person_id,
2272 x_current_inst => l_acad_hist_rec.current_inst,
2273 x_degree_attempted => l_acad_hist_rec.degree_attempted, --modified academic History LOV Build
2274 x_program_code => l_acad_hist_rec.Program_Code,
2275 x_degree_earned => l_acad_hist_rec.degree_earned,
2276 x_comments => l_acad_hist_rec.Comments,
2277 x_start_date => l_acad_hist_rec.Start_Date,
2278 x_end_date => NVL(c_interface_rec.prev_inst_left_date,l_acad_hist_rec.End_Date),
2279 x_planned_completion_date => l_acad_hist_rec.planned_completion_date,
2280 x_recalc_total_cp_attempted => l_acad_hist_rec.recalc_total_cp_attempted,
2281 x_recalc_total_cp_earned => l_acad_hist_rec.recalc_total_cp_earned,
2282 x_recalc_total_unit_gp => l_acad_hist_rec.recalc_total_unit_gp,
2283 x_recalc_tot_gpa_units_attemp=> l_acad_hist_rec.recalc_total_gpa_units_attemp,
2284 x_recalc_inst_gpa => l_acad_hist_rec.recalc_inst_gpa,
2285 x_recalc_grading_scale_id => l_acad_hist_rec.recalc_grading_scale_id,
2286 x_selfrep_total_cp_attempted => l_acad_hist_rec.selfrep_total_cp_attempted,
2287 x_selfrep_total_cp_earned => l_acad_hist_rec.selfrep_total_cp_earned,
2288 x_selfrep_total_unit_gp => l_acad_hist_rec.selfrep_total_unit_gp,
2289 x_selfrep_tot_gpa_uts_attemp => l_acad_hist_rec.selfrep_total_gpa_units_attemp,
2290 x_selfrep_inst_gpa => l_acad_hist_rec.selfrep_inst_gpa,
2291 x_selfrep_grading_scale_id => l_acad_hist_rec.selfrep_grading_scale_id,
2292 x_selfrep_weighted_gpa => l_acad_hist_rec.selfrep_weighted_gpa,
2293 x_selfrep_rank_in_class => l_acad_hist_rec.selfrep_rank_in_class,
2294 x_selfrep_weighed_rank => l_acad_hist_rec.selfrep_weighed_rank,
2295 x_type_of_school => l_acad_hist_rec.type_of_school,
2296 x_institution_code => l_acad_hist_rec.institution_code,
2297 x_attribute_category => l_acad_hist_rec.attribute_category,
2298 x_attribute1 => l_acad_hist_rec.attribute1,
2299 x_attribute2 => l_acad_hist_rec.attribute2,
2300 x_attribute3 => l_acad_hist_rec.attribute3,
2301 x_attribute4 => l_acad_hist_rec.attribute4,
2302 x_attribute5 => l_acad_hist_rec.attribute5,
2303 x_attribute6 => l_acad_hist_rec.attribute6,
2304 x_attribute7 => l_acad_hist_rec.attribute7,
2305 x_attribute8 => l_acad_hist_rec.attribute8,
2306 x_attribute9 => l_acad_hist_rec.attribute9,
2307 x_attribute10 => l_acad_hist_rec.attribute10,
2308 x_selfrep_class_size => l_acad_hist_rec.selfrep_class_size,
2309 x_transcript_required => l_acad_hist_rec.transcript_required,
2310 x_object_version_number => l_acad_hist_rec.object_version_number,
2311 x_msg_data => l_msg_data,
2312 x_return_status => l_return_status,
2313 x_mode => 'R');
2314
2315 IF l_return_status IN ('E','U') THEN
2316 log_error('E25' ,c_interface_rec.interface_hesa_id, l_msg_data);
2317 l_error_flag := TRUE;
2318 fnd_message.set_name('IGS','IGS_HE_UPD_ATT_FAIL');
2319 fnd_file.put_line( fnd_file.LOG ,fnd_message.get ||' - '|| l_msg_data);
2320 END IF;
2321
2322 EXCEPTION
2323 WHEN OTHERS THEN
2324 log_error('E25' ,c_interface_rec.interface_hesa_id, l_msg_data);
2325 l_error_flag := TRUE;
2326 fnd_message.set_name('IGS','IGS_HE_UPD_ATT_FAIL');
2327 fnd_file.put_line( fnd_file.LOG ,fnd_message.get ||' - '|| l_msg_data);
2328 END;
2329
2330 END IF; --End of Discrepancy Rule Check.
2331
2332 END IF; --End of Multiple Academic History records check.
2333
2334 ELSE
2335 --When there is no Academic History reocrd exists for the person and OSS Institution passed.
2336 CLOSE c_acad_hist ;
2337 BEGIN
2338
2339 -- Retrieve the Interface ID already created as part of Person details import.
2340 l_interface_id := NULL;
2341 OPEN c_adm_int_id (l_imp_batch_id, c_pe_det_rec.person_id);
2342 FETCH c_adm_int_id INTO l_interface_id;
2343 CLOSE c_adm_int_id;
2344
2345 l_interface_acadhis_id := NULL;
2346 -- Create an Academic History interface record for this person
2347 INSERT INTO igs_ad_acadhis_int_all ( interface_acadhis_id,
2348 interface_id,
2349 institution_code,
2350 current_inst,
2351 end_date,
2352 status,
2353 transcript_required,
2354 created_by,
2355 creation_date,
2356 last_updated_by,
2357 last_update_date,
2358 last_update_login,
2359 request_id,
2360 program_application_id,
2361 program_id,
2362 program_update_date )
2363 VALUES ( igs_ad_acadhis_int_s.NEXTVAL,
2364 l_interface_id,
2365 l_oss_inst,
2366 'N',
2367 c_interface_rec.prev_inst_left_date,
2368 '2',
2369 'N',
2370 fnd_global.user_id,
2371 SYSDATE,
2372 fnd_global.user_id,
2373 SYSDATE,
2374 fnd_global.login_id,
2375 DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_request_id),
2376 DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.prog_appl_id),
2377 DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_program_id),
2378 DECODE(fnd_global.conc_request_id,-1,NULL,SYSDATE) )
2379 RETURNING interface_acadhis_id INTO l_interface_acadhis_id;
2380
2381 EXCEPTION
2382 WHEN OTHERS THEN
2383 log_error('E24' ,c_interface_rec.interface_hesa_id, NULL);
2384 l_error_flag := TRUE;
2385 fnd_message.set_name('IGS','IGS_HE_INS_ATT_FAIL');
2386 fnd_file.put_line( fnd_file.LOG ,fnd_message.get || ' - ' || SQLERRM);
2387 END ;
2388 END IF ; -- record already exists
2389 END IF ; -- end of updating or inserting attendance history records
2390
2391 END IF ; -- person details to be imported
2392
2393 -- if import program attempt details parameter set then import data into hesa tables
2394 IF p_spa_det = 'Y' THEN
2395
2396 -- If there is data to be imported into spa record then update student program attempt hesa records
2397 IF l_upd_spa THEN
2398 BEGIN
2399 FOR c_upd_spa_rec IN c_upd_spa( c_pe_det_rec.person_id , c_interface_rec.course_cd) LOOP
2400 igs_he_st_spa_all_pkg.update_row (
2401 X_ROWID => c_upd_spa_rec.rowid ,
2402 X_HESA_ST_SPA_ID => c_upd_spa_rec.hesa_st_spa_id,
2403 X_ORG_ID => c_upd_spa_rec.org_id,
2404 X_PERSON_ID => c_upd_spa_rec.person_id,
2405 X_COURSE_CD => c_upd_spa_rec.course_cd,
2406 X_VERSION_NUMBER => c_upd_spa_rec.version_number,
2407 X_FE_STUDENT_MARKER => c_upd_spa_rec.fe_student_marker,
2408 X_DOMICILE_CD => NVL(l_oss_domicile, c_upd_spa_rec.domicile_cd),
2409 X_INST_LAST_ATTENDED => c_upd_spa_rec.inst_last_attended,
2410 X_YEAR_LEFT_LAST_INST => c_upd_spa_rec.year_left_last_inst,
2411 X_HIGHEST_QUAL_ON_ENTRY => NVL(c_interface_rec.highest_qual_on_entry,c_upd_spa_rec.highest_qual_on_entry),
2412 X_DATE_QUAL_ON_ENTRY_CALC => c_upd_spa_rec.date_qual_on_entry_calc,
2413 X_A_LEVEL_POINT_SCORE => c_upd_spa_rec.a_level_point_score,
2414 X_HIGHERS_POINTS_SCORES => c_upd_spa_rec.highers_points_scores,
2415 X_OCCUPATION_CODE => NVL(l_oss_occupation,c_upd_spa_rec.occupation_code),
2416 X_COMMENCEMENT_DT => c_upd_spa_rec.commencement_dt,
2417 X_SPECIAL_STUDENT => c_upd_spa_rec.special_student,
2418 X_STUDENT_QUAL_AIM => c_upd_spa_rec.student_qual_aim,
2419 X_STUDENT_FE_QUAL_AIM => c_upd_spa_rec.student_fe_qual_aim,
2420 X_TEACHER_TRAIN_PROG_ID => c_upd_spa_rec.teacher_train_prog_id,
2421 X_ITT_PHASE => c_upd_spa_rec.itt_phase,
2422 X_BILINGUAL_ITT_MARKER => c_upd_spa_rec.bilingual_itt_marker,
2423 X_TEACHING_QUAL_GAIN_SECTOR => c_upd_spa_rec.teaching_qual_gain_sector,
2424 X_TEACHING_QUAL_GAIN_SUBJ1 => c_upd_spa_rec.teaching_qual_gain_subj1,
2425 X_TEACHING_QUAL_GAIN_SUBJ2 => c_upd_spa_rec.teaching_qual_gain_subj2 ,
2426 X_TEACHING_QUAL_GAIN_SUBJ3 => c_upd_spa_rec.teaching_qual_gain_subj3,
2427 X_STUDENT_INST_NUMBER => c_upd_spa_rec.student_inst_number,
2428 X_DESTINATION => c_upd_spa_rec.destination,
2429 X_ITT_PROG_OUTCOME => c_upd_spa_rec.itt_prog_outcome,
2430 X_HESA_RETURN_NAME => c_upd_spa_rec.hesa_return_name,
2431 X_HESA_RETURN_ID => c_upd_spa_rec.hesa_return_id,
2432 X_HESA_SUBMISSION_NAME => c_upd_spa_rec.hesa_submission_name,
2433 X_ASSOCIATE_UCAS_NUMBER => c_upd_spa_rec.associate_ucas_number,
2434 X_ASSOCIATE_SCOTT_CAND => c_upd_spa_rec.associate_scott_cand,
2435 X_ASSOCIATE_TEACH_REF_NUM => c_upd_spa_rec.associate_teach_ref_num,
2436 X_ASSOCIATE_NHS_REG_NUM => c_upd_spa_rec.associate_nhs_reg_num,
2437 X_NHS_FUNDING_SOURCE => c_upd_spa_rec.nhs_funding_source,
2438 X_UFI_PLACE => c_upd_spa_rec.ufi_place,
2439 X_POSTCODE => NVL(c_interface_rec.postcode,c_upd_spa_rec.postcode),
2440 X_SOCIAL_CLASS_IND => NVL(l_oss_social_class,c_upd_spa_rec.social_class_ind),
2441 X_OCCCODE => NVL(c_interface_rec.occcode,c_upd_spa_rec.occcode),
2442 X_TOTAL_UCAS_TARIFF => NVL(c_interface_rec.total_ucas_tariff,c_upd_spa_rec.total_ucas_tariff),
2443 X_NHS_EMPLOYER => c_upd_spa_rec.nhs_employer,
2444 X_RETURN_TYPE => c_upd_spa_rec.return_type,
2445 X_QUAL_AIM_SUBJ1 => NVL(l_oss_subj1,c_upd_spa_rec.qual_aim_subj1),
2446 X_QUAL_AIM_SUBJ2 => NVL(l_oss_subj2,c_upd_spa_rec.qual_aim_subj2),
2447 X_QUAL_AIM_SUBJ3 => NVL(l_oss_subj3,c_upd_spa_rec.qual_aim_subj3),
2448 X_QUAL_AIM_PROPORTION => NVL(l_oss_proportion,c_upd_spa_rec.qual_aim_proportion) ,
2449 X_MODE => 'R',
2450 X_DEPENDANTS_CD => c_upd_spa_rec.dependants_cd ,
2451 X_IMPLIED_FUND_RATE => c_upd_spa_rec.implied_fund_rate ,
2452 X_GOV_INITIATIVES_CD => c_upd_spa_rec.gov_initiatives_cd ,
2453 X_UNITS_FOR_QUAL => c_upd_spa_rec.units_for_qual ,
2454 X_DISADV_UPLIFT_ELIG_CD => c_upd_spa_rec.disadv_uplift_elig_cd ,
2455 X_FRANCH_PARTNER_CD => c_upd_spa_rec.franch_partner_cd ,
2456 X_UNITS_COMPLETED => c_upd_spa_rec.units_completed ,
2457 X_FRANCH_OUT_ARR_CD => c_upd_spa_rec.franch_out_arr_cd ,
2458 X_EMPLOYER_ROLE_CD => c_upd_spa_rec.employer_role_cd ,
2459 X_DISADV_UPLIFT_FACTOR => c_upd_spa_rec.disadv_uplift_factor ,
2460 X_ENH_FUND_ELIG_CD => c_upd_spa_rec.enh_fund_elig_cd,
2461 X_EXCLUDE_FLAG => c_upd_spa_rec.exclude_flag
2462 ) ;
2463 END LOOP;
2464 EXCEPTION
2465 WHEN OTHERS THEN
2466 log_error('E22' ,c_interface_rec.interface_hesa_id, NULL);
2467 l_error_flag := TRUE;
2468 fnd_message.set_name('IGS','IGS_HE_UPD_SPA_FAIL');
2469 fnd_file.put_line( fnd_file.LOG ,fnd_message.get|| ' - ' || SQLERRM);
2470 END ;
2471
2472 END IF ; -- end of updating spa
2473
2474 -- If tariff data is to be imported and Program attempt update has not failed then
2475 -- import tariff details into oss
2476 IF l_ins_tariff AND NOT l_error_flag THEN
2477 BEGIN
2478 -- If course cd is given in the interface record then create tariff records under that spa
2479 -- If course cd is not specified then create tariff records under all the spa records for the student
2480 FOR c_spa_rec IN c_spa( c_pe_det_rec.person_id , c_interface_rec.course_cd) LOOP
2481 -- delete existing tariff records for the student program attempt record
2482
2483 FOR c_del_tariff_rec IN c_del_tariff(c_spa_rec.person_id, c_spa_rec.course_cd) LOOP
2484 igs_he_st_spa_ut_all_pkg.delete_row( X_ROWID => c_del_tariff_rec.rowid );
2485 END LOOP ;
2486
2487 -- smaddali modified the igs_he_st_spa_yt_all_pkg insert row calls for bug 2671022
2488 -- to pass the OSS award code instead of the HESA UT code
2489
2490 -- If qualification GCSEAS details are given then create a ucas tariff record
2491 IF c_interface_rec.gceasn IS NOT NULL THEN
2492 l_rowid := NULL ;
2493 l_hesa_st_spau_id := NULL ;
2494 igs_he_st_spa_ut_all_pkg.insert_row (
2495 X_ROWID => l_rowid ,
2496 X_HESA_ST_SPAU_ID => l_hesa_st_spau_id ,
2497 X_ORG_ID => l_org_id ,
2498 X_PERSON_ID => c_spa_rec.person_id ,
2499 X_COURSE_CD => c_spa_rec.course_cd,
2500 X_VERSION_NUMBER => c_spa_rec.version_number ,
2501 X_QUALIFICATION_LEVEL => l_oss_gceasn ,
2502 X_NUMBER_OF_QUAL => c_interface_rec.gceasn ,
2503 X_TARIFF_SCORE => c_interface_rec.gceasts ,
2504 X_MODE => 'R'
2505 ) ;
2506 END IF ;
2507
2508 -- If qualification VCSEAS details are given then create a ucas tariff record
2509 IF c_interface_rec.vceasn IS NOT NULL THEN
2510 l_rowid := NULL ;
2511 l_hesa_st_spau_id := NULL ;
2512 igs_he_st_spa_ut_all_pkg.insert_row (
2513 X_ROWID => l_rowid ,
2514 X_HESA_ST_SPAU_ID => l_hesa_st_spau_id ,
2515 X_ORG_ID => l_org_id ,
2516 X_PERSON_ID => c_spa_rec.person_id ,
2517 X_COURSE_CD => c_spa_rec.course_cd,
2518 X_VERSION_NUMBER => c_spa_rec.version_number ,
2519 X_QUALIFICATION_LEVEL => l_oss_vceasn ,
2520 X_NUMBER_OF_QUAL => c_interface_rec.vceasn ,
2521 X_TARIFF_SCORE => c_interface_rec.vceasts ,
2522 X_MODE => 'R'
2523 ) ;
2524 END IF ;
2525
2526 -- If qualification GCSEA details are given then create a ucas tariff record
2527 IF c_interface_rec.gcean IS NOT NULL THEN
2528 l_rowid := NULL ;
2529 l_hesa_st_spau_id := NULL ;
2530 igs_he_st_spa_ut_all_pkg.insert_row (
2531 X_ROWID => l_rowid ,
2532 X_HESA_ST_SPAU_ID => l_hesa_st_spau_id ,
2533 X_ORG_ID => l_org_id ,
2534 X_PERSON_ID => c_spa_rec.person_id ,
2535 X_COURSE_CD => c_spa_rec.course_cd,
2536 X_VERSION_NUMBER => c_spa_rec.version_number ,
2537 X_QUALIFICATION_LEVEL => l_oss_gcean ,
2538 X_NUMBER_OF_QUAL => c_interface_rec.gcean ,
2539 X_TARIFF_SCORE => c_interface_rec.gceats ,
2540 X_MODE => 'R'
2541 ) ;
2542 END IF ;
2543
2544 -- If qualification VCSEA details are given then create a ucas tariff record
2545 IF c_interface_rec.vcean IS NOT NULL THEN
2546 l_rowid := NULL ;
2547 l_hesa_st_spau_id := NULL ;
2548 igs_he_st_spa_ut_all_pkg.insert_row (
2549 X_ROWID => l_rowid ,
2550 X_HESA_ST_SPAU_ID => l_hesa_st_spau_id ,
2551 X_ORG_ID => l_org_id ,
2552 X_PERSON_ID => c_spa_rec.person_id ,
2553 X_COURSE_CD => c_spa_rec.course_cd,
2554 X_VERSION_NUMBER => c_spa_rec.version_number ,
2555 X_QUALIFICATION_LEVEL => l_oss_vcean ,
2556 X_NUMBER_OF_QUAL => c_interface_rec.vcean ,
2557 X_TARIFF_SCORE => c_interface_rec.vceats ,
2558 X_MODE => 'R'
2559 ) ;
2560 END IF ;
2561
2562 -- If qualification KEYSKL details are given then create a ucas tariff record
2563 IF c_interface_rec.ksqn IS NOT NULL THEN
2564 l_rowid := NULL ;
2565 l_hesa_st_spau_id := NULL ;
2566 igs_he_st_spa_ut_all_pkg.insert_row (
2567 X_ROWID => l_rowid ,
2568 X_HESA_ST_SPAU_ID => l_hesa_st_spau_id ,
2569 X_ORG_ID => l_org_id,
2570 X_PERSON_ID => c_spa_rec.person_id ,
2571 X_COURSE_CD => c_spa_rec.course_cd,
2572 X_VERSION_NUMBER => c_spa_rec.version_number ,
2573 X_QUALIFICATION_LEVEL => l_oss_ksqn ,
2574 X_NUMBER_OF_QUAL => c_interface_rec.ksqn ,
2575 X_TARIFF_SCORE => c_interface_rec.ksqts ,
2576 X_MODE => 'R'
2577 ) ;
2578 END IF ;
2579
2580 -- If qualification 1UNKEYSKL details are given then create a ucas tariff record
2581 IF c_interface_rec.uksan IS NOT NULL THEN
2582 l_rowid := NULL ;
2583 l_hesa_st_spau_id := NULL ;
2584 igs_he_st_spa_ut_all_pkg.insert_row (
2585 X_ROWID => l_rowid ,
2586 X_HESA_ST_SPAU_ID => l_hesa_st_spau_id ,
2587 X_ORG_ID => l_org_id ,
2588 X_PERSON_ID => c_spa_rec.person_id ,
2589 X_COURSE_CD => c_spa_rec.course_cd,
2590 X_VERSION_NUMBER => c_spa_rec.version_number ,
2591 X_QUALIFICATION_LEVEL => l_oss_uksan,
2592 X_NUMBER_OF_QUAL => c_interface_rec.uksan ,
2593 X_TARIFF_SCORE => c_interface_rec.uksats ,
2594 X_MODE => 'R'
2595 ) ;
2596 END IF ;
2597
2598 -- If qualification SCOTADH details are given then create a ucas tariff record
2599 IF c_interface_rec.sahn IS NOT NULL THEN
2600 l_rowid := NULL ;
2601 l_hesa_st_spau_id := NULL ;
2602 igs_he_st_spa_ut_all_pkg.insert_row (
2603 X_ROWID => l_rowid ,
2604 X_HESA_ST_SPAU_ID => l_hesa_st_spau_id ,
2605 X_ORG_ID => l_org_id ,
2606 X_PERSON_ID => c_spa_rec.person_id ,
2607 X_COURSE_CD => c_spa_rec.course_cd,
2608 X_VERSION_NUMBER => c_spa_rec.version_number ,
2609 X_QUALIFICATION_LEVEL => l_oss_sahn ,
2610 X_NUMBER_OF_QUAL => c_interface_rec.sahn ,
2611 X_TARIFF_SCORE => c_interface_rec.sahts ,
2612 X_MODE => 'R'
2613 ) ;
2614 END IF ;
2615
2616 -- If qualification SCOTH details are given then create a ucas tariff record
2617 IF c_interface_rec.shn IS NOT NULL THEN
2618 l_rowid := NULL ;
2619 l_hesa_st_spau_id := NULL ;
2620 igs_he_st_spa_ut_all_pkg.insert_row (
2621 X_ROWID => l_rowid ,
2622 X_HESA_ST_SPAU_ID => l_hesa_st_spau_id ,
2623 X_ORG_ID => l_org_id,
2624 X_PERSON_ID => c_spa_rec.person_id ,
2625 X_COURSE_CD => c_spa_rec.course_cd,
2626 X_VERSION_NUMBER => c_spa_rec.version_number ,
2627 X_QUALIFICATION_LEVEL => l_oss_shn,
2628 X_NUMBER_OF_QUAL => c_interface_rec.shn ,
2629 X_TARIFF_SCORE => c_interface_rec.shts ,
2630 X_MODE => 'R'
2631 ) ;
2632 END IF ;
2633
2634 -- If qualification SCOTST details are given then create a ucas tariff record
2635 IF c_interface_rec.ssgcn IS NOT NULL THEN
2636 l_rowid := NULL ;
2637 l_hesa_st_spau_id := NULL ;
2638 igs_he_st_spa_ut_all_pkg.insert_row (
2639 X_ROWID => l_rowid ,
2640 X_HESA_ST_SPAU_ID => l_hesa_st_spau_id ,
2641 X_ORG_ID => l_org_id ,
2642 X_PERSON_ID => c_spa_rec.person_id ,
2643 X_COURSE_CD => c_spa_rec.course_cd,
2644 X_VERSION_NUMBER => c_spa_rec.version_number ,
2645 X_QUALIFICATION_LEVEL => l_oss_ssgcn,
2646 X_NUMBER_OF_QUAL => c_interface_rec.ssgcn ,
2647 X_TARIFF_SCORE => c_interface_rec.ssgcts ,
2648 X_MODE => 'R'
2649 ) ;
2650 END IF ;
2651
2652 -- If qualification SCOTI2 details are given then create a ucas tariff record
2653 IF c_interface_rec.si2n IS NOT NULL THEN
2654 l_rowid := NULL ;
2655 l_hesa_st_spau_id := NULL ;
2656 igs_he_st_spa_ut_all_pkg.insert_row (
2657 X_ROWID => l_rowid ,
2658 X_HESA_ST_SPAU_ID => l_hesa_st_spau_id ,
2659 X_ORG_ID => l_org_id ,
2660 X_PERSON_ID => c_spa_rec.person_id ,
2661 X_COURSE_CD => c_spa_rec.course_cd,
2662 X_VERSION_NUMBER => c_spa_rec.version_number ,
2663 X_QUALIFICATION_LEVEL => l_oss_si2n ,
2664 X_NUMBER_OF_QUAL => c_interface_rec.si2n ,
2665 X_TARIFF_SCORE => c_interface_rec.si2ts ,
2666 X_MODE => 'R'
2667 ) ;
2668 END IF ;
2669
2670 -- If qualification SCOTCO details are given then create a ucas tariff record
2671 IF c_interface_rec.scsn IS NOT NULL THEN
2672 l_rowid := NULL ;
2673 l_hesa_st_spau_id := NULL ;
2674 igs_he_st_spa_ut_all_pkg.insert_row (
2675 X_ROWID => l_rowid ,
2676 X_HESA_ST_SPAU_ID => l_hesa_st_spau_id ,
2677 X_ORG_ID => l_org_id,
2678 X_PERSON_ID => c_spa_rec.person_id ,
2679 X_COURSE_CD => c_spa_rec.course_cd,
2680 X_VERSION_NUMBER => c_spa_rec.version_number ,
2681 X_QUALIFICATION_LEVEL => l_oss_scsn ,
2682 X_NUMBER_OF_QUAL => c_interface_rec.scsn ,
2683 X_TARIFF_SCORE => c_interface_rec.scsts ,
2684 X_MODE => 'R'
2685 ) ;
2686 END IF ;
2687
2688 -- If qualification ADVEXT details are given then create a ucas tariff record
2689 IF c_interface_rec.aean IS NOT NULL OR c_interface_rec.aeats IS NOT NULL THEN
2690 l_rowid := NULL ;
2691 l_hesa_st_spau_id := NULL ;
2692 igs_he_st_spa_ut_all_pkg.insert_row (
2693 X_ROWID => l_rowid ,
2694 X_HESA_ST_SPAU_ID => l_hesa_st_spau_id ,
2695 X_ORG_ID => l_org_id ,
2696 X_PERSON_ID => c_spa_rec.person_id ,
2697 X_COURSE_CD => c_spa_rec.course_cd,
2698 X_VERSION_NUMBER => c_spa_rec.version_number ,
2699 X_QUALIFICATION_LEVEL => l_oss_aean ,
2700 X_NUMBER_OF_QUAL => c_interface_rec.aean,
2701 X_TARIFF_SCORE => c_interface_rec.aeats,
2702 X_MODE => 'R'
2703 ) ;
2704 END IF ;
2705
2706 END LOOP ;
2707 EXCEPTION
2708 WHEN OTHERS THEN
2709 log_error('E21' ,c_interface_rec.interface_hesa_id, NULL);
2710 l_error_flag := TRUE;
2711 fnd_message.set_name('IGS','IGS_HE_INS_TAR_FAIL');
2712 fnd_file.put_line( fnd_file.LOG ,fnd_message.get|| ' - ' || SQLERRM);
2713 END ;
2714 END IF ; -- end of inserting tariff records
2715
2716 -- If fee_eligibility needs to be imported and tariff creation is successful then update student unit set attempt hesa records
2717 -- If unit set is given in the interface record then Update the student susa records
2718 -- belonging to this unit set , else update all susa records for that student
2719 IF c_interface_rec.fee_eligibility IS NOT NULL AND NOT l_error_flag THEN
2720 BEGIN
2721 FOR c_upd_susa_rec IN c_upd_susa(c_pe_det_rec.person_id,c_interface_rec.course_cd, c_interface_rec.unit_set_cd) LOOP
2722 igs_he_en_susa_pkg.update_row(
2723 X_ROWID => c_upd_susa_rec.rowid ,
2724 X_HESA_EN_SUSA_ID => c_upd_susa_rec.hesa_en_susa_id,
2725 X_PERSON_ID => c_upd_susa_rec.person_id ,
2726 X_COURSE_CD => c_upd_susa_rec.course_cd ,
2727 X_UNIT_SET_CD => c_upd_susa_rec.unit_set_cd ,
2728 X_US_VERSION_NUMBER => c_upd_susa_rec.us_version_number ,
2729 X_SEQUENCE_NUMBER => c_upd_susa_rec.sequence_number ,
2730 X_NEW_HE_ENTRANT_CD => c_upd_susa_rec.new_he_entrant_cd ,
2731 X_TERM_TIME_ACCOM => c_upd_susa_rec.term_time_accom ,
2732 X_DISABILITY_ALLOW => c_upd_susa_rec.disability_allow ,
2733 X_ADDITIONAL_SUP_BAND => c_upd_susa_rec.additional_sup_band ,
2734 X_SLDD_DISCRETE_PROV => c_upd_susa_rec.sldd_discrete_prov ,
2735 X_STUDY_MODE => c_upd_susa_rec.study_mode ,
2736 X_STUDY_LOCATION => c_upd_susa_rec.study_location ,
2737 X_FTE_PERC_OVERRIDE => c_upd_susa_rec.fte_perc_override ,
2738 X_FRANCHISING_ACTIVITY => c_upd_susa_rec.franchising_activity ,
2739 X_COMPLETION_STATUS => c_upd_susa_rec.completion_status ,
2740 X_GOOD_STAND_MARKER => c_upd_susa_rec.good_stand_marker ,
2741 X_COMPLETE_PYR_STUDY_CD => c_upd_susa_rec.complete_pyr_study_cd ,
2742 X_CREDIT_VALUE_YOP1 => c_upd_susa_rec.credit_value_yop1 ,
2743 X_CREDIT_VALUE_YOP2 => c_upd_susa_rec.credit_value_yop2 ,
2744 X_CREDIT_VALUE_YOP3 => c_upd_susa_rec.credit_value_yop3 ,
2745 X_CREDIT_VALUE_YOP4 => c_upd_susa_rec.credit_value_yop4 ,
2746 X_CREDIT_LEVEL_ACHIEVED1 => c_upd_susa_rec.credit_level_achieved1 ,
2747 X_CREDIT_LEVEL_ACHIEVED2 => c_upd_susa_rec.credit_level_achieved2 ,
2748 X_CREDIT_LEVEL_ACHIEVED3 => c_upd_susa_rec.credit_level_achieved3 ,
2749 X_CREDIT_LEVEL_ACHIEVED4 => c_upd_susa_rec.credit_level_achieved4 ,
2750 X_CREDIT_PT_ACHIEVED1 => c_upd_susa_rec.credit_pt_achieved1 ,
2751 X_CREDIT_PT_ACHIEVED2 => c_upd_susa_rec.credit_pt_achieved2 ,
2752 X_CREDIT_PT_ACHIEVED3 => c_upd_susa_rec.credit_pt_achieved3 ,
2753 X_CREDIT_PT_ACHIEVED4 => c_upd_susa_rec.credit_pt_achieved4 ,
2754 X_CREDIT_LEVEL1 => c_upd_susa_rec.credit_level1 ,
2755 X_CREDIT_LEVEL2 => c_upd_susa_rec.credit_level2 ,
2756 X_CREDIT_LEVEL3 => c_upd_susa_rec.credit_level3 ,
2757 X_CREDIT_LEVEL4 => c_upd_susa_rec.credit_level4 ,
2758 X_ADDITIONAL_SUP_COST => c_upd_susa_rec.additional_sup_cost,
2759 X_ENH_FUND_ELIG_CD => c_upd_susa_rec.enh_fund_elig_cd,
2760 X_DISADV_UPLIFT_FACTOR => c_upd_susa_rec.disadv_uplift_factor,
2761 X_YEAR_STU => c_upd_susa_rec.year_stu,
2762 X_GRAD_SCH_GRADE => c_upd_susa_rec.grad_sch_grade ,
2763 X_MARK => c_upd_susa_rec.mark ,
2764 X_TEACHING_INST1 => c_upd_susa_rec.teaching_inst1 ,
2765 X_TEACHING_INST2 => c_upd_susa_rec.teaching_inst2 ,
2766 X_PRO_NOT_TAUGHT => c_upd_susa_rec.pro_not_taught ,
2767 X_FUNDABILITY_CODE => c_upd_susa_rec.fundability_code ,
2768 X_FEE_ELIGIBILITY => l_oss_fee_elig ,
2769 X_FEE_BAND => c_upd_susa_rec.fee_band ,
2770 X_NON_PAYMENT_REASON => c_upd_susa_rec.non_payment_reason ,
2771 X_STUDENT_FEE => c_upd_susa_rec.student_fee ,
2772 X_FTE_INTENSITY => c_upd_susa_rec.fte_intensity ,
2773 X_CALCULATED_FTE => c_upd_susa_rec.calculated_fte ,
2774 X_FTE_CALC_TYPE => c_upd_susa_rec.fte_calc_type ,
2775 X_TYPE_OF_YEAR => c_upd_susa_rec.type_of_year ,
2776 X_MODE => 'R'
2777 ) ;
2778 END LOOP ;
2779 EXCEPTION
2780 WHEN OTHERS THEN
2781 log_error('E23' ,c_interface_rec.interface_hesa_id, NULL);
2782 l_error_flag := TRUE;
2783 fnd_message.set_name('IGS','IGS_HE_UPD_SUSA_FAIL');
2784 fnd_file.put_line( fnd_file.LOG ,fnd_message.get || ' - ' || SQLERRM);
2785 END;
2786 END IF ; -- susa to be updated
2787
2788 END IF ; -- program details to be imported
2789
2790
2791 END IF ; -- if hesa code mapping validations pass
2792
2793 END IF ; -- if course and unit set validations are passed
2794
2795 END IF ; -- if person validations passed
2796
2797 END LOOP ; -- loop interface records
2798
2799 -- If person import tables have been populated and population of person interface tables is successful
2800 -- then call the import process
2801 IF l_call_pers_imp THEN
2802
2803 -- call the application import process to update person details ,
2804 -- create/update citizenship , disability,ethnic_origin,alternate person id records
2805 fnd_file.put_line( fnd_file.LOG ,' ');
2806 fnd_message.set_name('IGS','IGS_UC_ADM_IMP_PROC_LAUNCH');
2807 fnd_message.set_token('REQ_ID',TO_CHAR(l_imp_batch_id));
2808 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2809 fnd_file.put_line( fnd_file.LOG ,'-----------------------------------');
2810 COMMIT;
2811 import_process(c_src_type_id_rec.source_type_id, l_imp_batch_id, l_org_id);
2812 fnd_file.put_line( fnd_file.LOG ,'-----------------------------------');
2813 fnd_file.put_line( fnd_file.LOG ,' ');
2814
2815 -- For each failed person import record create an interface error record in hesa interface error table
2816 FOR c_imp_int_rec IN c_imp_int LOOP
2817 -- Get the person import interface record corresponding to the hesa interface record and
2818 -- create error record for this interface record if the import has failed for this record
2819 OPEN c_adm_int(l_imp_batch_id, c_imp_int_rec.person_number );
2820 FETCH c_adm_int INTO c_adm_int_rec ;
2821 IF c_adm_int%FOUND THEN
2822 --Check if Admission import failed because of Academic History details.
2823 OPEN c_acadhis_int(c_adm_int_rec.interface_id);
2824 FETCH c_acadhis_int INTO l_interface_acadhis_id;
2825 IF c_acadhis_int%FOUND THEN
2826 --When Academic History import failed.
2827 log_error('E36' ,c_imp_int_rec.interface_hesa_id, NULL);
2828 l_error_flag := TRUE;
2829 fnd_message.set_name('IGS','IGS_HE_ACAD_HIST_IMP_FAIL');
2830 fnd_message.set_token('INT_ID',c_imp_int_rec.interface_hesa_id);
2831 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2832 ELSE
2833 log_error('E26' ,c_imp_int_rec.interface_hesa_id, NULL);
2834 l_error_flag := TRUE;
2835 fnd_message.set_name('IGS','IGS_HE_PER_IMP_FAIL');
2836 fnd_message.set_token('INT_ID',c_imp_int_rec.interface_hesa_id);
2837 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2838 END IF;
2839 CLOSE c_acadhis_int;
2840 END IF ;
2841 CLOSE c_adm_int ;
2842 END LOOP ;
2843
2844 END IF ; -- if person interface records have been populated
2845
2846 -- delete successfully imported interface records
2847 FOR c_interface_rec IN c_interface LOOP
2848 -- If no errors have been logged for this interface record then delete this interface record
2849 OPEN c_del_err(c_interface_rec.interface_hesa_id) ;
2850 FETCH c_del_err INTO c_del_err_rec ;
2851 IF c_del_err%NOTFOUND THEN
2852 DELETE FROM igs_he_ucas_imp_int WHERE batch_id = p_batch_id
2853 AND interface_hesa_id = c_interface_rec.interface_hesa_id ;
2854 END IF ;
2855 CLOSE c_del_err ;
2856 END LOOP; -- deleting successful interface records
2857
2858 -- delete batch definition record if all the interface records of the batch has been imported successfully
2859 OPEN c_interface ;
2860 FETCH c_interface INTO c_interface_rec ;
2861 IF c_interface%NOTFOUND THEN
2862 DELETE FROM igs_he_batch_int WHERE batch_id = p_batch_id ;
2863 END IF ;
2864 CLOSE c_interface ;
2865
2866 -- Submit the Error report to show the errors generated while importing hesa interface records
2867 l_rep_request_id := NULL ;
2868 l_rep_request_id := Fnd_Request.Submit_Request
2869 ( 'IGS',
2870 'IGSHES02',
2871 'Import HESA Student Details Error Report - Landscape',
2872 NULL,
2873 FALSE,
2874 p_batch_id ,
2875 CHR(0),
2876 NULL,
2877 NULL,
2878 NULL ,
2879 NULL, NULL, NULL, NULL, NULL,
2880 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2881 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2882 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2883 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2884 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2885 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2886 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2887 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2888 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
2889 IF l_rep_request_id > 0 THEN
2890 -- if error report successfully submitted then log message
2891 fnd_file.put_line( fnd_file.LOG ,' ');
2892 fnd_message.set_name('IGS','IGS_HE_REPSUBM');
2893 fnd_message.set_token('REQ_ID',TO_CHAR(l_rep_request_id));
2894 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2895 ELSE
2896 -- if error report failed to be launched then log message
2897 fnd_message.set_name('IGS','IGS_HE_REP_SUBM_ERR');
2898 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2899 END IF; -- submitted error report
2900
2901
2902
2903 EXCEPTION
2904 WHEN IGS_UC_HE_NOT_ENABLED_EXCEP THEN
2905 -- ucas functionality is not enabled
2906 Errbuf := fnd_message.get_string ('IGS', 'IGS_UC_HE_NOT_ENABLED');
2907 Retcode := 3 ;
2908 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
2909
2910 WHEN OTHERS THEN
2911 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2912 fnd_message.set_token('NAME','IGS_HE_IMPORT_DATA.MAIN_PROCESS'||' - '||SQLERRM);
2913 fnd_file.put_line(fnd_file.LOG,fnd_message.get);
2914 Retcode := 3 ;
2915 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
2916
2917 END main_process;
2918
2919 END igs_he_import_data;
[Home] [Help]