1 PACKAGE BODY igs_uc_export_hesa_to_oss_pkg AS
2 /* $Header: IGSUC25B.pls 120.3 2006/08/21 03:52:22 jbaber noship $ */
3
4 PROCEDURE chk_person_present (l_per_id igs_pe_person.person_id%TYPE, l_per_present OUT NOCOPY VARCHAR2) IS
5 /*************************************************************
6 Created By : sowsubra
7 Date Created By : 11-FEB-2002
8 Purpose :
9 Know limitations, enhancements or remarks
10 Change History
11 Who When What
12 ayedubat 31-DEC-2002 Changed the cur_person_present_in_oss to fetch the records from
13 igs_pe_person_base_v instead of igs_pe_person to improve performance
14 for bug, 2726113
15 (reverse chronological order - newest change first)
16 ***************************************************************/
17
18 /* To check if the person is present in the oss tables*/
19 CURSOR cur_person_present_in_oss( l_per_id IGS_PE_PERSON.person_id%TYPE) IS
20 SELECT COUNT(*) n_count
21 FROM igs_pe_person_base_v
22 WHERE person_id = l_per_id;
23 person_present_rec cur_person_present_in_oss%ROWTYPE;
24
25 BEGIN
26
27 -- check if the person exists in OSS and set the return flag
28 person_present_rec := NULL ;
29 OPEN cur_person_present_in_oss(l_per_id);
30 FETCH cur_person_present_in_oss INTO person_present_rec;
31 IF person_present_rec.n_count = 0 THEN
32 l_per_present := 'N';
33 ELSE
34 l_per_present :='Y';
35 END IF;
36 CLOSE cur_person_present_in_oss ;
37
38 EXCEPTION WHEN OTHERS THEN
39 RAISE;
40 END chk_person_present;
41
42
43 PROCEDURE pre_enrollement_process( l_person_id IGS_PE_PERSON.person_id%TYPE ,l_COURSE_CD VARCHAR2 ,l_VERSION_NUMBER NUMBER) IS
44 /*************************************************************
45 Created By : sowsubra
46 Date Created By : 11-FEB-2002
47 Purpose :
48
49 Know limitations, enhancements or remarks
50 Change History
51 Who When What
52
53 (reverse chronological order - newest change first)
54 ***************************************************************/
55 p_message VARCHAR2(100);
56 p_status NUMBER;
57 retcode NUMBER;
58 errbuf VARCHAR2(100);
59
60 BEGIN
61 -- call the Pre-enrollment process
62 igs_en_hesa_pkg.hesa_stats_enr( l_person_id,l_COURSE_CD,l_VERSION_NUMBER,p_message,p_status);
63
64 EXCEPTION
65 WHEN OTHERS THEN
66 ROLLBACK;
67 retcode :=p_status;
68 errbuf :=p_message;
69 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
70
71 END pre_enrollement_process;
72
73
74 PROCEDURE import_process(
75 p_source_type_id igs_pe_src_types_all.source_type_id%TYPE,
76 p_batch_id NUMBER
77 ) IS
78
79 /******************************************************************
80 Created By : rbezawad
81 Date Created By : 22-Sep-03
82 Purpose : Submit the call for admission application import process
83 Known limitations,enhancements,remarks:
84 Change History
85 Who When What
86 ***************************************************************** */
87
88 l_row_id VARCHAR2(26);
89
90 CURSOR cur_match_set IS
91 SELECT match_set_id
92 FROM igs_pe_match_sets
93 WHERE source_type_id = p_source_type_id;
94 match_set_rec cur_match_set%ROWTYPE;
95
96 l_interface_run_id igs_ad_interface_ctl.interface_run_id%TYPE;
97 l_errbuff VARCHAR2(100) ;
98 l_retcode NUMBER ;
99
100 BEGIN
101
102 -- Get the match set criteria corresponding to the ucas source type to be used for the person import
103 match_set_rec := NULL ;
104 OPEN cur_match_set;
105 FETCH cur_match_set INTO match_set_rec;
106 CLOSE cur_match_set;
107
108 -- Call admission application import process procedure because current process has to wait until import process is finished
109 igs_ad_imp_001.imp_adm_data ( errbuf => l_errbuff,
110 retcode => l_retcode ,
111 p_batch_id => p_batch_id,
112 p_source_type_id => p_source_type_id,
113 p_match_set_id => match_set_rec.match_set_id,
114 p_acad_cal_type => NULL ,
115 p_acad_sequence_number => NULL ,
116 p_adm_cal_type => NULL ,
117 p_adm_sequence_number => NULL ,
118 p_admission_cat => NULL ,
119 p_s_admission_process_type => NULL ,
120 p_interface_run_id => l_interface_run_id ,
121 P_org_id => NULL ) ;
122
123 EXCEPTION
124 WHEN OTHERS THEN
125 IF cur_match_set%ISOPEN THEN
126 CLOSE cur_match_set;
127 END IF ;
128 -- even though the admission import process completes in error , this process should continue processing
129 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
130 fnd_message.set_token('NAME','IGS_UC_EXPORT_HESA_TO_OSS_PKG.IMPORT_PROCESS'||' - '||SQLERRM);
131 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
132 END import_process;
133
134
135 PROCEDURE export_data(errbuf OUT NOCOPY VARCHAR2,
136 retcode OUT NOCOPY NUMBER,
137 p_person_id IGS_PE_PERSON.person_id%TYPE) IS
138 /*************************************************************
139 Created By : sowsubra
140 Date Created By : 11-FEB-2002
141 Purpose :
142
143 Know limitations, enhancements or remarks
144 Change History (reverse chronological order - newest change first)
145 Who When What
146 --------------------------------------------------------------------------------
147 smaddali 26-mar-2002 UCCR002(bug#2278817) replaced direct DML with TBH calls,
148 replaced text being written into LOG file with Message names
149 smaddali 02-apr-2002 Modified the source field for social class from starh_social_class to
150 starh_socio_economic field of table igs_uc_app_stats for UCCR002 bug#2278817
151 Also changed the name of table igs_uc_attendance_history to igs_uc_attend_hist
152 and package name igs_uc_attendance_history to igs_uc_attend_hist.
153 Changed tbh calls of igs_uc_app_stats_pkg and igs_uc_applicants to add new columns
154 smaddali 15-jul-02 Modified cursors to get mapped values for domicile,social_class,institution,occupation for bug 2497509
155 smaddali 18-jul-02 Added cursor c_person and passing person_number instead of person_id to log messages,bug2497516
156 bayadav 06-Nov-2002 Added Columns as part of UCFD102 Build. Bug NO: 2643048
157 pmarada 26-dec-02 Bug 2726132, i)Removed the igs_he_st_spa_all_pkg.update_row.
158 ii)creating record in igs_uc_attend_hist if the record not exist else updating.
159 ayedubat 31-DEC-2002 Fixed all the issues as mentioned in the bug, 2727487
160 rbezawad 19-Sep-2003 Modified the process w.r.t. UCFD210 Build, Bug 2893542 to populate the Previous education details into
161 OSS Academic History and obsolete the functionality related to IGS_UC_ATTEND_HIST.
162 rgangara 30-Jan-2004 Modified cur_all_applicants cursor to check for Sent_to_hesa flag from IGS_UC_APP_STATS instead of
163 IGS_UC_APPLICANTS. The Sent_to_hesa is for all practical purposes obsolete. Also removed update of
164 Applicant's.Sent_to_hesa as it is no more required as part of bug 3405245
165 arvsrini 27-Jul-2004 Added code to shift the exporting ethnic code logic from IGSUC44B.pls to the current process.
166 Included logic to export ethnic details in case of the same person having multiple information coming from
167 different systems Bug#3796641
168 anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
169 ***************************************************************/
170
171 /* smaddali added this cursor to get the person_number to display in the log file , bug 2497516 */
172 CURSOR c_person ( cp_person_id igs_pe_person.person_id%TYPE ) IS
173 SELECT person_number
174 FROM igs_pe_person_base_v
175 WHERE person_id = cp_person_id ;
176 l_person_number igs_pe_person.person_number%TYPE := NULL ;
177
178 /* cursor to select all applicants and details whose details have not been exported to HESA */
179 CURSOR cur_all_applicants(cp_person_id igs_pe_person.person_id%TYPE ) IS
180 SELECT app.app_no,
181 app.app_id,
182 app.oss_person_id,
183 TO_CHAR(app.domicile_apr) domicile_apr,
184 app.system_code,
185 app.country_birth,
186 stat.starh_pocc,
187 stat.starh_socio_economic,
188 stat.starh_pocc_edu_chg_dt
189 FROM igs_uc_applicants app, igs_uc_app_stats stat
190 WHERE app.app_no = stat.app_no
191 AND oss_person_id = NVL(cp_person_id, oss_person_id)
192 AND stat.sent_to_hesa = 'N'
193 ORDER BY app.system_code, app.app_no ;
194
195 --Check whether the UCAS setup is defined or not for the Default prev_inst_left_date value.
196 CURSOR cur_ucas_setup ( cp_person_id igs_pe_person.person_id%TYPE ) IS
197 SELECT system_code
198 FROM igs_uc_defaults
199 WHERE prev_inst_left_date IS NULL
200 AND system_code IN ( SELECT DISTINCT app.system_code
201 FROM igs_uc_applicants app, igs_uc_app_stats stat
202 WHERE app.app_no = stat.app_no
203 AND oss_person_id = NVL(cp_person_id, oss_person_id)
204 AND stat.sent_to_hesa = 'N' );
205
206 -- Get the Source type ID of UCAS for admission import process
207 --smaddali modified this cursor to get the source type UCAS PER instead of UCAS APPL ,bug 2724140
208 CURSOR cur_src_type_id IS
209 SELECT source_type_id
210 FROM igs_pe_src_types_all
211 WHERE source_type = 'UCAS PER'
212 AND NVL(closed_ind,'N') = 'N';
213
214 l_src_type_id_rec cur_src_type_id%ROWTYPE;
215
216 --Check whether the Source Category of Academic History is included within the source Type "UCAS PER" or not.
217 CURSOR cur_pe_src_cat (cp_source_type_id igs_pe_src_types_all.source_type_id%TYPE,
218 cp_category IGS_AD_SOURCE_CAT.category_name%TYPE) IS
219 SELECT 'X'
220 FROM igs_ad_source_cat_v
221 WHERE source_type_id = cp_source_type_id
222 AND category_name = cp_category
223 AND include_ind = 'Y';
224
225 -- Cursor to find the OSS mapping values for UCAS codes
226 CURSOR cur_ucas_oss_map ( cp_association_code IGS_HE_CODE_MAP_VAL.association_code%TYPE,
227 cp_map1 IGS_HE_CODE_MAP_VAL.map1%TYPE ) IS
228 SELECT map2
229 FROM igs_he_code_map_val
230 WHERE association_code = cp_association_code
231 AND map1 = cp_map1 ;
232
233 CURSOR get_had_details (l_per_id igs_pe_person.person_id%TYPE) IS
234 SELECT had.ROWID ,had.*
235 FROM igs_he_ad_dtl_all had
236 WHERE person_id = l_per_id;
237
238 CURSOR get_st_spa_details( l_per_id igs_pe_person.person_id%TYPE) IS
239 SELECT course_cd, version_number
240 FROM igs_he_st_spa_all hestspa
241 WHERE person_id = l_per_id;
242
243 --smaddali start ,new cursors created to remove direct DML from code
244 CURSOR cur_upd_uc_appl ( p_app_id IGS_UC_APPLICANTS.app_id%TYPE) IS
245 SELECT app.ROWID , app.*
246 FROM igs_uc_applicants app
247 WHERE app.app_id = p_app_id ;
248
249 CURSOR cur_upd_uc_app_stats ( p_app_id IGS_UC_APP_STATS.app_id%TYPE) IS
250 SELECT apst.ROWID , apst.*
251 FROM igs_uc_app_stats apst
252 WHERE apst.app_id = p_app_id ;
253
254 CURSOR cur_ad_appl_inst (p_per_id igs_pe_person.person_id%TYPE) IS
255 SELECT admission_appl_number,
256 nominated_course_cd,
257 sequence_number
258 FROM igs_ad_ps_appl_inst_all
259 WHERE person_id = p_per_id;
260
261 CURSOR cur_he_ad_dtl_all(p_per_id igs_pe_person.person_id%TYPE) IS
262 SELECT COUNT(*)
263 FROM igs_he_ad_dtl_all
264 WHERE person_id = p_per_id;
265
266 -- Get the Batch ID for admission application import process
267 CURSOR c_bat_id IS
268 SELECT igs_ad_interface_batch_id_s.NEXTVAL
269 FROM dual;
270
271 -- Get the Person number for the passed person id.
272 CURSOR c_person_info (cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
273 SELECT person_number, last_name surname, first_name given_names, gender sex, birth_date birth_dt
274 FROM igs_pe_person_base_v
275 WHERE person_id = cp_person_id;
276 l_person_info_rec c_person_info%ROWTYPE;
277
278 -- Get the admission application instance interface records whose import has failed
279 CURSOR c_adm_int( cp_batch_id igs_ad_interface.batch_id%TYPE) IS
280 SELECT a.person_number, a.interface_id
281 FROM igs_ad_interface a
282 WHERE a.batch_id = cp_batch_id
283 AND ( a.status IN ('2','3') OR a.record_status='3' ) ;
284
285 -- to fetch the system code in the order U,S,N,G in case there are multiple ethnic data
286 CURSOR cur_sys_ethnic(cp_person_id igs_pe_person.person_id%TYPE ) IS
287 SELECT app.system_code,app.app_no
288 FROM igs_uc_applicants app, igs_uc_app_stats stat
289 WHERE app.app_no = stat.app_no
290 AND oss_person_id = cp_person_id
291 ORDER BY app.system_code DESC;
292
293 -- Get the Applicant Statistics interface records whose import has failed
294 CURSOR c_stat_int (cp_interface_id igs_ad_acadhis_int_all.interface_id%TYPE) IS
295 SELECT a.*
296 FROM igs_ad_stat_int_all a
297 WHERE a.interface_id = cp_interface_id
298 AND a.status = '3';
299 l_interface_stat_rec c_stat_int%ROWTYPE ;
300
301 l_imp_batch_id igs_ad_interface_all.batch_id%TYPE ;
302 l_interface_id igs_ad_interface_all.interface_id%TYPE ;
303 l_interface_stat_id igs_ad_stat_int_all.interface_stat_id%TYPE;
304 l_chk_per_present VARCHAR2(1) := 'Y';
305 l_col_ad_null VARCHAR2(1) := 'Y';
306 l_col_spa_null VARCHAR2(1) := 'Y';
307 l_dom_cd igs_he_ad_dtl_all.domicile_cd%TYPE;
308 l_occ_code igs_he_ad_dtl_all.occupation_cd%TYPE;
309 l_soc_code igs_he_ad_dtl_all.social_class_cd%TYPE;
310 l_starh_pocc_edu_chg_dt igs_uc_app_stats.starh_pocc_edu_chg_dt%TYPE;
311
312 l_oss_religion_cd igs_ad_stat_int.religion_cd%TYPE;
313 l_oss_ethnic_origin igs_ad_stat_int.ethnic_origin%TYPE;
314 l_ethnic_cd igs_uc_app_stats.starh_ethnic%TYPE;
315 l_max_sys_ethnic cur_sys_ethnic%ROWTYPE;
316
317 had_rec get_had_details%ROWTYPE;
318 hestspa_rec get_st_spa_details%ROWTYPE;
319 all_appl_rec cur_all_applicants%ROWTYPE;
320 l_app_stat_rec cur_upd_uc_app_stats%ROWTYPE;
321
322 l_rowid VARCHAR2(250);
323 l_count NUMBER := 0;
324 l_mapping_failed VARCHAR2(1) ;
325 l_rec_found VARCHAR2(1);
326 l_no_setup VARCHAR2(1);
327 l_error_occurred VARCHAR2(1);
328 l_stat_int_rec_populated VARCHAR2(1);
329 l_return_status VARCHAR2(1);
330 l_msg_data VARCHAR2(100);
331
332 igs_uc_he_not_enabled_excep EXCEPTION;
333
334 -- anwest 17-FEB-2006 Bug#5034713
335 l_ucas_code igs_uc_app_stats.starh_pocc%TYPE;
336
337 BEGIN
338
339 --anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
340 IGS_GE_GEN_003.SET_ORG_ID;
341
342 /* To check if the country code profile is set to UK - if not the process should be terminated */
343 IF NOT (IGS_UC_UTILS.IS_UCAS_HESA_ENABLED) THEN
344 RAISE IGS_UC_HE_NOT_ENABLED_EXCEP;
345 END IF;
346
347 --Check if any UCAS applicants exist whose HESA data hasn't been exported yet ,
348 -- If not log a message and exit
349 OPEN cur_all_applicants(p_person_id);
350 FETCH cur_all_applicants INTO all_appl_rec;
351 /* If there are no applicants with sent_to_hesa value = 'N' then make an entry into the log file */
352 IF (cur_all_applicants%NOTFOUND) THEN
353
354 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
355 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.get_string('IGS','IGS_UC_HE_NO_DATA'));
356 retcode:=1;
357 RETURN;
358
359 END IF;
360 CLOSE cur_all_applicants;
361
362 --Previous Institution leaving Day/Month details must be defined in UCAS Setup for all the systems for which
363 -- applications are being exported to OSS. If the value is not defined for any of the systems an error should
364 -- be recorded in the log file and the process should halt.
365 l_rec_found := 'N';
366 l_no_setup := 'N';
367 FOR l_ucas_setup_rec IN cur_ucas_setup(p_person_id)
368 LOOP
369 fnd_message.set_name('IGS','IGS_UC_SETUP_PREV_INST_DET');
370 fnd_message.set_token('SYSTEM',l_ucas_setup_rec.system_code);
371 fnd_file.put_line(fnd_file.log,fnd_message.get);
372 l_no_setup := 'Y';
373 END LOOP;
374
375 -- Check whether the Person Source Type 'UCAS PER' defined in the setup
376 l_src_type_id_rec := NULL ;
377 OPEN cur_src_type_id;
378 FETCH cur_src_type_id INTO l_src_type_id_rec;
379 IF cur_src_type_id%NOTFOUND THEN
380 fnd_message.set_name('IGS','IGS_UC_NO_UCAS_SRC_TYP');
381 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
382 l_no_setup := 'Y';
383 ELSE
384
385 -- Check the Admission Source Categories Setup included the Category. If included Populate
386 -- the Interface Table, so that this record will be processed by the Admission Import Process
387 OPEN cur_pe_src_cat(l_src_type_id_rec.source_type_id,'PERSON_STATISTICS');
388 FETCH cur_pe_src_cat INTO l_rec_found;
389 IF cur_pe_src_cat%NOTFOUND THEN
390 fnd_message.set_name('IGS','IGS_UC_ADM_INT_NOT_IMP');
391 fnd_message.set_token('INT_TYPE', 'STATISTIC');
392 fnd_file.put_line(fnd_file.log, fnd_message.get);
393 l_no_setup := 'Y';
394 END IF;
395 CLOSE cur_pe_src_cat;
396
397 END IF;
398 CLOSE cur_src_type_id;
399
400 IF l_no_setup = 'Y' THEN
401 retcode:=1;
402 RETURN;
403 END IF;
404
405 /* Loop through all the UCAS Applicant records whose data need to be exported */
406
407 FOR all_appl_rec IN cur_all_applicants(p_person_id) LOOP
408
409 fnd_file.put_line(fnd_file.log,' ');
410 /* check if the person is present in OSS, if not then donot process the person */
411 chk_person_present(all_appl_rec.oss_person_id, l_chk_per_present);
412 IF (l_chk_per_present = 'N') THEN
413
414 FND_MESSAGE.Set_Name('IGS','IGS_UC_HE_NO_PERS');
415 FND_MESSAGE.Set_Token('PERSON_ID',all_appl_rec.app_no);
416 FND_FILE.PUT_LINE (FND_FILE.LOG,FND_MESSAGE.get );
417 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.get_string('IGS','IGS_UC_HE_NOT_PROC') );
418
419 ELSE /* If the person details present in OSS*/
420
421 l_mapping_failed := 'N';
422
423 --smaddali added this cursor to get the person_number to be shown in the log file , bug2497516
424 l_person_number := NULL ;
425 OPEN c_person(all_appl_rec.oss_person_id);
426 FETCH c_person INTO l_person_number;
427 CLOSE c_person ;
428
429 -----------------------------------------------------------------------------------------------------------------
430 /**** Find all the OSS mapping codes for the values to be exported to OSS from igs_he_code_map_val table ******/
431 -----------------------------------------------------------------------------------------------------------------
432
433 /* fetch the DOMICILE CODE for the applicant */
434 l_dom_cd := NULL ;
435 IF all_appl_rec.domicile_apr IS NOT NULL THEN
436 OPEN cur_ucas_oss_map('UC_OSS_HE_DOM_ASSOC',all_appl_rec.domicile_apr);
437 FETCH cur_ucas_oss_map INTO l_dom_cd;
438
439 IF cur_ucas_oss_map%NOTFOUND THEN
440
441 FND_MESSAGE.Set_Name('IGS','IGS_UC_NO_DOM_MAPPING');
442 FND_MESSAGE.Set_Token('PERSON_ID',l_person_number);
443 FND_MESSAGE.Set_Token('CODE',all_appl_rec.domicile_apr);
444 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.get );
445 l_mapping_failed := 'Y';
446
447 END IF ;
448 CLOSE cur_ucas_oss_map;
449
450 END IF;
451
452
453 /* fetch the occupation code for the particular applicant */
454 l_occ_code := NULL ;
455 IF all_appl_rec.starh_pocc IS NOT NULL THEN
456
457 -- anwest 17-FEB-2006 Bug#5034713
458 l_ucas_code := nvl(ltrim(all_appl_rec.starh_pocc,'0'),'0');
459
460 OPEN cur_ucas_oss_map('UC_OSS_HE_OCC_ASSOC', l_ucas_code);
461 FETCH cur_ucas_oss_map INTO l_occ_code;
462
463 IF cur_ucas_oss_map%NOTFOUND THEN
464
465 FND_MESSAGE.Set_Name('IGS','IGS_UC_NO_OCC_MAPPING');
466 FND_MESSAGE.Set_Token('PERSON_ID',l_person_number);
467 FND_MESSAGE.Set_Token('CODE',all_appl_rec.starh_pocc );
468 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.get );
469 l_mapping_failed := 'Y';
470
471 END IF;
472 CLOSE cur_ucas_oss_map;
473
474 END IF ;
475
476
477 /* fetch the social class code for the particular applicant */
478 l_soc_code := NULL ;
479 IF all_appl_rec.starh_socio_economic IS NOT NULL THEN
480
481 OPEN cur_ucas_oss_map('UC_OSS_HE_SOC_ASSOC',all_appl_rec.starh_socio_economic);
482 FETCH cur_ucas_oss_map INTO l_soc_code;
483 IF cur_ucas_oss_map%NOTFOUND THEN
484
485 FND_MESSAGE.Set_Name('IGS','IGS_UC_NO_SOC_MAPPING');
486 FND_MESSAGE.Set_Token('PERSON_ID',l_person_number);
487 FND_MESSAGE.Set_Token('CODE',all_appl_rec.starh_socio_economic );
488 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.get );
489 l_mapping_failed := 'Y';
490
491 END IF ;
492 CLOSE cur_ucas_oss_map;
493 END IF ;
494
495 l_max_sys_ethnic := NULL;
496 OPEN cur_sys_ethnic(all_appl_rec.oss_person_id);
497 FETCH cur_sys_ethnic INTO l_max_sys_ethnic;
498 CLOSE cur_sys_ethnic;
499
500 -- to process only one record in case multiple records are present for the same person
501 IF all_appl_rec.system_code = l_max_sys_ethnic.system_code THEN
502
503 -- Get the country description for the IGS_UC_APPLICANTS.COUNTRY_BIRTH column,
504 -- and populate IGS_AD_STAT_INT interface table
505 -- Also get the ethnic and religion code mappings from mapping table and populate it
506 -- into populate IGS_AD_STAT_INT interface table as per bug 3094405
507 l_app_stat_rec := NULL;
508 OPEN cur_upd_uc_app_stats (all_appl_rec.app_id);
509 FETCH cur_upd_uc_app_stats INTO l_app_stat_rec;
510 CLOSE cur_upd_uc_app_stats;
511 l_ethnic_cd := NVL(l_app_stat_rec.starh_ethnic,l_app_stat_rec.starx_ethnic);
512
513 IF l_ethnic_cd IS NOT NULL OR l_app_stat_rec.ivstarx_religion IS NOT NULL THEN
514 l_oss_ethnic_origin := NULL;
515 l_oss_religion_cd := NULL;
516
517 -- Get the OSS Ethnic code mapping
518 IF l_ethnic_cd IS NOT NULL THEN
519 OPEN cur_ucas_oss_map('UC_OSS_HE_ETH_ASSOC',l_ethnic_cd);
520 FETCH cur_ucas_oss_map INTO l_oss_ethnic_origin;
521 IF cur_ucas_oss_map%NOTFOUND THEN
522 l_oss_ethnic_origin := NULL;
523 l_mapping_failed := 'Y';
524 fnd_message.set_name('IGS','IGS_UC_INV_MAPPING_VAL');
525 fnd_message.set_token('CODE',l_ethnic_cd );
526 fnd_message.set_token('TYPE','ETHNIC' );
527 fnd_file.put_line(fnd_file.log, fnd_message.get);
528 END IF;
529 CLOSE cur_ucas_oss_map;
530 END IF ;
531
532 -- Get the OSS Religion code mapping
533 IF l_app_stat_rec.ivstarx_religion IS NOT NULL THEN
534 OPEN cur_ucas_oss_map('OSS_HESA_RELIG_ASSOC',l_app_stat_rec.ivstarx_religion);
535 FETCH cur_ucas_oss_map INTO l_oss_religion_cd;
536 IF cur_ucas_oss_map%NOTFOUND THEN
537 l_oss_religion_cd := NULL;
538 l_mapping_failed := 'Y';
539 fnd_message.set_name('IGS','IGS_UC_INV_MAPPING_VAL');
540 fnd_message.set_token('CODE',l_app_stat_rec.ivstarx_religion );
541 fnd_message.set_token('TYPE','RELIGION' );
542 fnd_file.put_line(fnd_file.log, fnd_message.get);
543 END IF;
544 CLOSE cur_ucas_oss_map;
545 END IF;
546
547 END IF; --Ethnic values NOT NULL check.
548 END IF; -- checking sytem code
549
550
551 /********* END of mapping the HESA codes **************/
552
553 IF l_mapping_failed = 'N' THEN
554
555 /* Fetch the fields in the tables for this particular person_id and call the TBH to insert/update the table */
556 -- check if there is a record for the passed person in igs_he_ad_dtl_all ,
557 -- if not then create a new record from the corresponding OSS admission record for the person
558 -- smaddali start replacing the select statement with a cursor
559 l_count :=0 ;
560 OPEN cur_he_ad_dtl_all(all_appl_rec.oss_person_id) ;
561 FETCH cur_he_ad_dtl_all INTO l_count;
562 CLOSE cur_he_ad_dtl_all ;
563
564 IF l_count = 0 THEN
565
566 FOR lv_ad_appl_inst IN cur_ad_appl_inst(all_appl_rec.oss_person_id) LOOP
567 l_rowid := NULL ;
568 igs_he_ad_dtl_all_pkg.insert_row(
569 x_rowid => l_rowid,
570 x_org_id => NULL,
571 x_hesa_ad_dtl_id => l_count,
572 x_person_id => all_appl_rec.oss_person_id,
573 x_admission_appl_number => lv_ad_appl_inst.admission_appl_number,
574 x_nominated_course_cd => lv_ad_appl_inst.nominated_course_cd,
575 x_sequence_number => lv_ad_appl_inst.sequence_number,
576 x_occupation_cd => l_occ_code,
577 x_domicile_cd => l_dom_cd,
578 x_social_class_cd => l_soc_code,
579 x_special_student_cd => NULL,
580 x_mode => 'R' );
581
582 END LOOP ;
583
584 ELSE
585
586 FOR had_rec IN get_had_details(all_appl_rec.oss_person_id) LOOP
587 igs_he_ad_dtl_all_pkg.update_row (
588 x_mode => 'R',
589 x_rowid => had_rec.ROWID,
590 x_org_id => had_rec.org_id,
591 x_hesa_ad_dtl_id => had_rec.hesa_ad_dtl_id,
592 x_person_id => had_rec.person_id,
593 x_admission_appl_number => had_rec.admission_appl_number,
594 x_nominated_course_cd => had_rec.nominated_course_cd,
595 x_sequence_number => had_rec.sequence_number,
596 x_occupation_cd => NVL(l_occ_code,had_rec.occupation_cd ),
597 x_domicile_cd => NVL(l_dom_cd,had_rec.domicile_cd ),
598 x_social_class_cd => NVL(l_soc_code,had_rec.social_class_cd ),
599 x_special_student_cd => had_rec.special_student_cd );
600
601 END LOOP ;
602
603 END IF ;
604
605 l_interface_id := NULL;
606
607 -- call the pre-enrollment process to create person details, it will update/create rec in IGS_HE_ST_SPA_ALL table.
608 hestspa_rec := NULL; -- initializing to NULL
609 OPEN get_st_spa_details(all_appl_rec.oss_person_id);
610 FETCH get_st_spa_details INTO hestspa_rec;
611 CLOSE get_st_spa_details;
612
613 --Call to pre-enrollment
614 pre_enrollement_process( all_appl_rec.oss_person_id,hestspa_rec.course_cd,hestspa_rec.version_number);
615
616 -- l_acadhis_int_rec_populated := 'N';
617 l_stat_int_rec_populated := 'N';
618 l_error_occurred := 'N';
619
620
621 -- Start of - Ethnic code origin processing
622 -- Check if current applicant's system_code is max(system_code) among all applications of the person.
623 IF all_appl_rec.system_code = l_max_sys_ethnic.system_code THEN
624
625 IF l_error_occurred = 'N' AND l_oss_ethnic_origin IS NOT NULL THEN
626 OPEN c_person_info(all_appl_rec.oss_person_id);
627 FETCH c_person_info INTO l_person_info_rec;
628 CLOSE c_person_info;
629
630
631 IF l_imp_batch_id IS NULL THEN
632 OPEN c_bat_id;
633 FETCH c_bat_id INTO l_imp_batch_id;
634 CLOSE c_bat_id;
635
636 INSERT INTO igs_ad_imp_batch_det ( batch_id,
637 batch_desc,
638 created_by,
639 creation_date,
640 last_updated_by,
641 last_update_date,
642 last_update_login,
643 request_id,
644 program_application_id,
645 program_update_date,
646 program_id)
647 VALUES ( l_imp_batch_id,
648 fnd_message.get_string('IGS','IGS_UC_IMP_ACAD_HIST_BATCH_ID'),
649 fnd_global.user_id,
650 SYSDATE,
651 fnd_global.user_id,
652 SYSDATE,
653 fnd_global.login_id,
654 DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_request_id),
655 DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.prog_appl_id),
656 DECODE(fnd_global.conc_request_id,-1,NULL,SYSDATE),
657 DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_program_id)
658 );
659 END IF;
660
661 IF l_interface_id IS NULL THEN
662
663
664 INSERT INTO igs_ad_interface(person_number,
665 interface_id,
666 batch_id,
667 source_type_id,
668 person_id,
669 surname,
670 given_names,
671 sex,
672 birth_dt,
673 status,
674 record_status,
675 match_ind,
676 created_by,
677 creation_date,
678 last_updated_by,
679 last_update_date,
680 last_update_login,
681 request_id,
682 program_application_id,
683 program_update_date,
684 program_id)
685 VALUES(l_person_info_rec.person_number,
686 igs_ad_interface_s.NEXTVAL,
687 l_imp_batch_id,
688 l_src_type_id_rec.source_type_id,
689 all_appl_rec.oss_person_id,
690 l_person_info_rec.surname,
691 l_person_info_rec.given_names,
692 l_person_info_rec.sex,
693 l_person_info_rec.birth_dt,
694 '1', --status
695 '2', --record_status,
696 '15', --Match_Ind
697 fnd_global.user_id,
698 SYSDATE,
699 fnd_global.user_id,
700 SYSDATE,
701 fnd_global.login_id,
702 DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_request_id),
703 DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.prog_appl_id),
704 DECODE(fnd_global.conc_request_id,-1,NULL,SYSDATE),
705 DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_program_id) )
706 RETURNING interface_id INTO l_interface_id;
707 END IF;
708
709 l_interface_stat_id := NULL;
710
711 INSERT INTO igs_ad_stat_int_all (
712 interface_stat_id
713 ,interface_id
714 ,ethnic_origin
715 ,religion_cd
716 ,status
717 ,created_by
718 ,creation_date
719 ,last_updated_by
720 ,last_update_date
721 ,last_update_login )
722 VALUES(
723 IGS_AD_STAT_INT_S.NEXTVAL,
724 l_interface_id,
725 l_oss_ethnic_origin,
726 l_oss_religion_cd,
727 '2',
728 fnd_global.user_id,
729 SYSDATE,
730 fnd_global.user_id,
731 SYSDATE,
732 fnd_global.login_id )
733 RETURNING interface_stat_id INTO l_interface_stat_id;
734
735 l_stat_int_rec_populated := 'Y';
736
737 END IF; -- checking l_error_code
738 ELSE
739 fnd_message.set_name('IGS','IGS_UC_SYS_STAT_NOT_IMP') ;
740 fnd_message.set_token('APP_NO1',all_appl_rec.app_no);
741 fnd_message.set_token('SYS_CODE1',all_appl_rec.system_code);
742 fnd_message.set_token('APP_NO2',l_max_sys_ethnic.app_no);
743 fnd_message.set_token('SYS_CODE2',l_max_sys_ethnic.system_code);
744 fnd_message.set_token('PER_NO',l_person_number);
745 fnd_file.put_line(fnd_file.log,fnd_message.get);
746 END IF; -- checking for system code
747
748
749
750 IF l_error_occurred = 'N' THEN
751
752 FOR j IN cur_upd_uc_app_stats(all_appl_rec.app_id) LOOP
753 igs_uc_app_stats_pkg.update_row(
754 X_ROWID => j.ROWID ,
755 X_APP_STAT_ID => j.app_stat_id ,
756 X_APP_ID => j.app_id ,
757 X_APP_NO => j.app_no ,
758 X_STARH_ETHNIC => j.starh_ethnic ,
759 X_STARH_SOCIAL_CLASS => j.starh_social_class ,
760 X_STARH_POCC_EDU_CHG_DT => j.starh_POCC_edu_chg_dt ,
761 X_STARH_POCC => j.starh_POCC ,
762 X_STARH_POCC_TEXT => j.starh_POCC_text ,
763 X_STARH_LAST_EDU_INST => j.starh_last_edu_inst ,
764 X_STARH_EDU_LEAVE_DATE => j.starh_edu_leave_date ,
765 X_STARH_LEA => j.starh_LEA ,
766 X_STARX_ETHNIC => j.starx_ethnic ,
767 X_STARX_POCC_EDU_CHG => j.starx_POCC_edu_chg ,
768 X_STARX_POCC => j.starx_POCC ,
769 X_STARX_POCC_TEXT => j.starx_POCC_text ,
770 X_SENT_TO_HESA => 'Y' ,
771 X_MODE => 'R' ,
772 -- 2-apr-2002 smaddali added these 3 new columns for UCCR002 bug#2278817
773 X_STARH_SOCIO_ECONOMIC => j.starh_socio_economic ,
774 X_STARX_SOCIO_ECONOMIC => j.starx_socio_economic ,
775 X_STARX_OCC_BACKGROUND => j.starx_occ_background,
776 -- Added following Columns as part of UCFD102Build. Bug NO: 2643048 by bayadav
777 x_ivstarh_dependants => j.ivstarh_dependants,
778 x_ivstarh_married => j.ivstarh_married,
779 x_ivstarx_religion => j.ivstarx_religion,
780 x_ivstarx_dependants => j.ivstarx_dependants,
781 x_ivstarx_married => j.ivstarx_married );
782
783 END LOOP;
784
785 IF l_stat_int_rec_populated = 'Y' THEN
786 /* Record the successful export of HESA details into Interface tables in log file */
787 fnd_message.set_name('IGS','IGS_UC_EXP_STAT_INT_SUCC') ;
788 fnd_message.set_token('PERSON_NO',l_person_number);
789 fnd_file.put_line(fnd_file.log,fnd_message.get);
790 END IF;
791
792 /* Record the successful export of HESA details into OSS in log file */
793 fnd_message.set_name('IGS','IGS_UC_EXP_SUCC') ;
794 fnd_message.set_token('PERSON_ID',l_person_number);
795 fnd_file.put_line(fnd_file.log,fnd_message.get);
796
797 END IF; --End of l_error_occurred = 'N' check.
798
799 END IF ; /* End mapping failed check */
800
801 END IF; /* End of checking, the person is present in OSS */
802
803 END LOOP;
804
805 -- If Academic History import interface tables have been populated
806 -- then call the import process
807 IF l_imp_batch_id IS NOT NULL THEN
808 fnd_file.put_line( fnd_file.LOG ,' ');
809 fnd_message.set_name('IGS','IGS_UC_ADM_IMP_PROC_LAUNCH');
810 fnd_message.set_token('REQ_ID',TO_CHAR(l_imp_batch_id));
811 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
812 fnd_file.put_line( fnd_file.LOG ,'-----------------------------------');
813 COMMIT;
814 --Call the import_process() procedure to launch the AD import process.
815 import_process(l_src_type_id_rec.source_type_id, l_imp_batch_id);
816 fnd_file.put_line( fnd_file.LOG ,'-----------------------------------');
817 fnd_file.put_line( fnd_file.LOG ,' ');
818
819 -- For each import interface record corresponding the Admissions Batch ID
820 FOR l_imp_int_rec IN c_adm_int(l_imp_batch_id) LOOP
821
822 -- Get the Ethnic(statistic) import interface record corresponding to the interface record and
823 -- log the error if the import has failed for this record
824 OPEN c_stat_int(l_imp_int_rec.interface_id);
825 FETCH c_stat_int INTO l_interface_stat_rec;
826 IF c_stat_int%FOUND THEN
827 --When Statistic import failed.
828 fnd_message.set_name('IGS','IGS_UC_IMP_STAT_FAIL');
829 fnd_message.set_token('PERSON_NO',l_imp_int_rec.person_number);
830 fnd_message.set_token('ETHNIC', l_interface_stat_rec.ethnic_origin);
831 fnd_message.set_token('INT_ID', l_imp_int_rec.interface_id);
832 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
833 END IF;
834 CLOSE c_stat_int;
835 END LOOP ;
836
837 END IF ; -- if person interface records have been populated
838
839 EXCEPTION
840
841 WHEN IGS_UC_HE_NOT_ENABLED_EXCEP THEN
842 retcode :=2;
843 errbuf :=FND_MESSAGE.get_string('IGS','IGS_UC_HE_NOT_ENABLED');
844 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
845
846 WHEN OTHERS THEN
847 ROLLBACK;
848 retcode :=2;
849 FND_FILE.PUT_LINE(FND_FILE.LOG,'SQLERRM -> ' || SQLERRM);
850 FND_MESSAGE.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
851 FND_MESSAGE.Set_Token('NAME','igs_uc_export_hesa_to_oss_pkg.export_data');
852 errbuf :=FND_MESSAGE.get ;
853 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
854
855 END export_data;
856
857
858 END igs_uc_export_hesa_to_oss_pkg;
[Home] [Help]