1 PACKAGE BODY igs_uc_exp_applicant_dtls AS
2 /* $Header: IGSUC44B.pls 120.6 2006/08/30 03:36:37 jbaber ship $ */
3
4 -- Standard WHO columns to be used across all the package
5 g_created_by NUMBER := fnd_global.user_id;
6 g_last_updated_by NUMBER := g_created_by;
7 g_last_update_login NUMBER := fnd_global.login_id;
8
9
10 PROCEDURE export_process ( errbuf OUT NOCOPY VARCHAR2,
11 retcode OUT NOCOPY NUMBER,
12 p_app_no IN NUMBER,
13 p_addr_usage_home IN VARCHAR2,
14 p_addr_usage_corr IN VARCHAR2
15 ) AS
16 /******************************************************************
17 Created By : AYEDUBAT
18 Date Created By : 13-JUN-2003
19 Purpose : This is the main procedure which is being called from the
20 concurrent manager to export the UCAS applicant details to OSS.
21 This Process is mainly divided into 6 parts :
22 1. Populating Admission Interface Tables to export Applicant Details to OSS
23 2. Populating Admission Interface Tables to export Applicant Address Details to OSS
24 3. Displaying the Manual Updations required to Applicant Names Information
25 4. Calling the Admission Import Process, if interface records are populated
26 5. Process the Admission Interface Records for errors and
27 update the UCAS Interface Tables with Sent To OSS Flag
28 6. Launching Export UCAS Applicant to OSS Error Report, if any errors found
29 Known limitations,enhancements,remarks:
30
31 CHANGE HISTORY:
32 WHO WHEN WHAT
33 AYEDUBAT 15-JUL-03 Changed the cursor,cur_ninumber_alt_type to remove the condition,
34 ni_number_alt_pers_type IS NULL as part of Multiple Cycles Enh Bug#2669208
35 AYEDUBAT 16-JUL-2003 Modified to correct the the title value of UCAS with the pre-adjucent_name in OSS
36 Added ORDER BY Clause while processing the Applications for Bug#2669208
37 DSRIDHAR 25-SEP-2003 Bug No. 2980137. Added a local variable to obtain the return code from
38 pop_res_dtls_int.
39 RGANGARA 10-APR-2004 bug# 3553352. Added validation to check Whether Decision Maker ID has been set for each
40 of the Systems which have atleast one Applicant record for processing.
41 ANWEST 30-SEP-2004 Bug# 3642740 Added 2 new cursors and 2 FOR
42 LOOPS to review and process all 'I' records
43 ANWEST 25-NOV-2004 Modified for UCFD040 - Bug# 4015492 Added 2 new cursors,
44 2 new data types, 6 new local variables, 1 new procedure,
45 1 more mandatory check and code logic associated with
46 person residency term
47 ANWEST 21-JUL-2005 Bug# 4465994 Corrected app_no parameter
48 ANWEST 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
49 JCHAKRAB 20-Feb-2006 Modified for bugs 3691220, 3691210, 3691176 - replaced existing cursors
50 cur_exp_applicant_dtls, cur_app_address_dtls, cur_app_name_dtls with REF CURSORS
51 to improve performance
52 ***************************************************************** */
53
54 -- Fetch the Source Type ID for UCAS Person from Person Source Types Table
55 CURSOR cur_pe_src_types IS
56 SELECT source_type_id
57 FROM IGS_PE_SRC_TYPES_ALL
58 WHERE source_type = 'UCAS PER'
59 AND NVL(closed_ind,'N') = 'N';
60
61 l_src_type_id IGS_PE_SRC_TYPES_ALL.source_type_id%TYPE;
62
63 -- Fetch the NI Number Alternate Type from UCAS Setup for NMAS System
64 CURSOR cur_ninumber_alt_type IS
65 SELECT name,ni_number_alt_pers_type
66 FROM IGS_UC_DEFAULTS
67 WHERE system_code = 'N';
68
69 cur_ninumber_alt_type_rec cur_ninumber_alt_type%ROWTYPE;
70
71 -- Cursors to get the Distinct Systems for the records to be selected for processing
72 -- Added for bug# bug# 3553352
73 CURSOR cur_chk_app_systems(cp_app_no igs_uc_applicants.app_no%TYPE ) IS
74 SELECT DISTINCT system_code
75 FROM igs_uc_applicants ucap
76 WHERE ucap.app_no = NVL(cp_app_no, ucap.app_no)
77 AND NVL(ucap.sent_to_oss,'N') = 'N';
78
79 -- Cursor to check whether decision make id is set for the sytem
80 -- Added for bug# bug# 3553352
81 CURSOR cur_chk_dcsn_maker_setup(cp_system_code igs_uc_defaults.system_code%TYPE) IS
82 SELECT name, decision_make_id
83 FROM igs_uc_defaults
84 WHERE system_code = cp_system_code;
85
86 chk_dcsn_maker_setup_rec cur_chk_dcsn_maker_setup%ROWTYPE ;
87
88
89 -- Fetch the OSS Person Details for an UCAS Applicant
90 CURSOR cur_uc_person_dtls(cp_person_id IGS_PE_PERSON.person_id%TYPE) IS
91 SELECT person_id, person_number,title,
92 last_name surname, first_name given_names, gender sex,
93 birth_date, pre_name_adjunct
94 FROM IGS_PE_PERSON_BASE_V
95 WHERE person_id = cp_person_id;
96 cur_uc_person_dtls_rec cur_uc_person_dtls%ROWTYPE;
97
98 -- Fetch the OSS Person ID of an UCAS Applicant
99 CURSOR cur_uc_app_dtls (cp_app_no IGS_UC_APPLICANTS.app_no%TYPE) IS
100 SELECT ucap.app_no, ucap.oss_person_id, domicile_apr, ucap.country_birth
101 FROM IGS_UC_APPLICANTS ucap
102 WHERE ucap.app_no = cp_app_no;
103 cur_uc_app_dtls_rec cur_uc_app_dtls%ROWTYPE;
104
105 -- Fetch the Interface ID for the passed Batch ID and Person ID
106 CURSOR cur_ad_interface_id ( cp_batch_id IGS_AD_INTERFACE_ALL.batch_id%TYPE,
107 cp_person_id IGS_AD_INTERFACE_ALL.person_id%TYPE) IS
108 SELECT interface_id
109 FROM IGS_AD_INTERFACE_ALL
110 WHERE batch_id = cp_batch_id
111 AND person_id = cp_person_id;
112
113 /* Cursors used in the Export Applicant Details Logic */
114
115 -- Cursor to fetch the Applicant Details for Update
116 CURSOR cur_upd_ucas_app ( cp_app_no IGS_UC_APPLICANTS.app_no%TYPE) IS
117 SELECT ucap.rowid,ucap.*
118 FROM IGS_UC_APPLICANTS ucap
119 WHERE ucap.app_no = cp_app_no;
120 cur_ucas_app_rec cur_upd_ucas_app%ROWTYPE;
121
122
123 /* Cursors used in the Export Applicant Address Details to OSS Logic */
124
125 -- Cursor to fetch the Applicant Address Details for Update
126 CURSOR cur_upd_app_address ( cp_app_no IGS_UC_APP_ADDRESES.app_no%TYPE) IS
127 SELECT ucad.rowid,ucad.*
128 FROM IGS_UC_APP_ADDRESES ucad
129 WHERE ucad.app_no = cp_app_no;
130 cur_app_address_rec cur_upd_app_address%ROWTYPE;
131
132
133 /* Cursors used in displaying Applicant Name Details to be changed manually */
134
135 -- Cursor to fetch the Applicant Name Details for Update
136 CURSOR cur_upd_app_name ( cp_app_no IGS_UC_APP_NAMES.app_no%TYPE) IS
137 SELECT ucn.rowid,ucn.*
138 FROM IGS_UC_APP_NAMES ucn
139 WHERE ucn.app_no = cp_app_no;
140 cur_app_name_rec cur_upd_app_name%ROWTYPE;
141
142 -- Fetch the HESA Mapping value
143 CURSOR cur_hesa_map (cp_assoc IGS_HE_CODE_MAP_VAL.association_code%TYPE,
144 cp_map1 IGS_HE_CODE_MAP_VAL.map2%TYPE ) IS
145 SELECT map2
146 FROM IGS_HE_CODE_MAP_VAL
147 WHERE association_code = cp_assoc
148 AND map1 = cp_map1;
149 l_oss_val IGS_HE_CODE_MAP_VAL.map2%TYPE;
150
151 /* Cursors used to Process the Admission Interface Tables data */
152
153 CURSOR cur_proc_applicants(cp_batch_id IGS_AD_INTERFACE_ALL.batch_id%TYPE) IS
154 SELECT ucap.rowid,ucap.*
155 FROM IGS_UC_APPLICANTS ucap
156 WHERE ucap.ad_batch_id = cp_batch_id;
157
158 CURSOR cur_proc_app_address(cp_batch_id IGS_AD_INTERFACE_ALL.batch_id%TYPE) IS
159 SELECT ucad.rowid,ucad.*
160 FROM IGS_UC_APP_ADDRESES ucad
161 WHERE ucad.ad_batch_id = cp_batch_id;
162
163 CURSOR cur_ad_interface_exist (cp_batch_id IGS_AD_INTERFACE_ALL.batch_id%TYPE,
164 cp_interface_id IGS_AD_INTERFACE_ALL.INTERFACE_ID%TYPE) IS
165 SELECT 'X'
166 FROM IGS_AD_INTERFACE_ALL
167 WHERE batch_id = cp_batch_id
168 AND interface_id = cp_interface_id;
169
170 -- anwest Bug# 3642740 New cursor to store UCAS applicants in error
171 CURSOR cur_proc_applicants_i IS
172 SELECT ucapi.rowid, ucapi.*
173 FROM IGS_UC_APPLICANTS ucapi
174 WHERE ucapi.sent_to_oss = 'I';
175
176 -- anwest Bug# 3642740 New cursor to store UCAS applicant addresses in error
177 CURSOR cur_proc_app_address_i IS
178 SELECT ucadi.rowid, ucadi.*
179 FROM IGS_UC_APP_ADDRESES ucadi
180 WHERE ucadi.sent_to_oss_flag = 'I';
181
182
183 /* Cursors used to retrieve the Term Calender for insertion in the Residency
184 Interface Table. */
185
186 -- anwest UCFD040 Bug# 4015492 New cursor to store maximum current cycle
187 CURSOR cur_get_current_cycle IS
188 SELECT max(current_cycle)
189 FROM IGS_UC_DEFAULTS;
190 l_max_curr_cycle IGS_UC_DEFAULTS.current_cycle%TYPE;
191
192 -- anwest UCFD040 Bug# 4015492 New cursor to store load calender instances for UCAS
193 -- system codes and cycles
194 CURSOR cur_get_term(cp_entry_year igs_uc_sys_calndrs.entry_year%type) IS
195 SELECT DISTINCT ucsyscal.system_code,
196 ucsyscal.entry_year,
197 cainstall.cal_type,
198 cainstall.sequence_number,
199 cainstall.start_dt
200 FROM IGS_CA_INST_ALL cainstall,
201 IGS_CA_INST_REL cainstrel,
202 IGS_CA_TYPE catype,
203 IGS_CA_STAT castat,
204 IGS_UC_SYS_CALNDRS ucsyscal
205 WHERE castat.s_cal_status = 'ACTIVE' and
206 catype.s_cal_cat = 'LOAD' and
207 cainstall.cal_status = castat.s_cal_status and
208 cainstall.cal_type = catype.cal_type and
209 cainstall.cal_type = cainstrel.sub_cal_type and
210 cainstrel.sub_ci_sequence_number = cainstall.sequence_number and
211 cainstrel.sup_cal_type = ucsyscal.aca_cal_type and
212 cainstrel.sup_ci_sequence_number = ucsyscal.aca_cal_seq_no and
213 ucsyscal.entry_year <= cp_entry_year + 1 and
214 ucsyscal.entry_year >= cp_entry_year - 1
215 ORDER BY ucsyscal.system_code, cainstall.start_dt;
216
217 -- anwest UCFD040 Bug# 4015492 New type to hold attributes of a cursor above
218 TYPE res_term_type IS RECORD (system_code igs_uc_sys_calndrs.system_code%TYPE,
219 entry_year igs_uc_sys_calndrs.entry_year%TYPE,
220 cal_type igs_ca_inst_all.cal_type%TYPE,
221 sequence_number igs_ca_inst_all.sequence_number%TYPE);
222
223 -- anwest UCFD040 Bug# 4015492 New type to store multiple record types
224 TYPE res_term_table_type IS TABLE OF res_term_type INDEX BY BINARY_INTEGER;
225
226 --jchakrab added for 3691176
227 TYPE applicant_record IS RECORD (
228 app_no IGS_UC_APPLICANTS.APP_NO%TYPE,
229 system_code IGS_UC_APPLICANTS.SYSTEM_CODE%TYPE,
230 oss_person_id IGS_UC_APPLICANTS.OSS_PERSON_ID%TYPE,
231 scn IGS_UC_APPLICANTS.SCN%TYPE,
232 ni_number IGS_UC_APPLICANTS.NI_NUMBER%TYPE,
233 residential_category IGS_UC_APPLICANTS.RESIDENTIAL_CATEGORY%TYPE,
234 nationality IGS_UC_APPLICANTS.NATIONALITY%TYPE,
235 dual_nationality IGS_UC_APPLICANTS.DUAL_NATIONALITY%TYPE,
236 special_needs IGS_UC_APPLICANTS.SPECIAL_NEEDS%TYPE,
237 school IGS_UC_APPLICANTS.SCHOOL%TYPE,
238 application_date IGS_UC_APPLICANTS.APPLICATION_DATE%TYPE,
239 country_birth IGS_UC_APPLICANTS.COUNTRY_BIRTH%TYPE );
240
241 TYPE t_cur_exp_applicant_dtls IS REF CURSOR RETURN applicant_record;
242 cur_exp_applicant_dtls t_cur_exp_applicant_dtls;
243
244 cur_exp_applicant_dtls_rec applicant_record;
245
246 --jchakrab added for 3691210
247 TYPE t_cur_app_address_dtls IS REF CURSOR RETURN IGS_UC_APP_ADDRESES%ROWTYPE;
248 cur_app_address_dtls t_cur_app_address_dtls;
249
250 --cur_app_address_dtls_rec app_address_record;
251 cur_app_address_dtls_rec IGS_UC_APP_ADDRESES%ROWTYPE;
252
253 --jchakrab added for 3691220
254 TYPE app_name_record IS RECORD (
255 app_no IGS_UC_APP_NAMES.APP_NO%TYPE,
256 title IGS_UC_APP_NAMES.TITLE%TYPE,
257 fore_names IGS_UC_APP_NAMES.FORE_NAMES%TYPE,
258 surname IGS_UC_APP_NAMES.SURNAME%TYPE,
259 birth_date IGS_UC_APP_NAMES.BIRTH_DATE%TYPE,
260 sex IGS_UC_APP_NAMES.SEX%TYPE );
261
262 TYPE t_cur_app_name_dtls IS REF CURSOR RETURN app_name_record;
263 cur_app_name_dtls t_cur_app_name_dtls;
264
265 cur_app_name_dtls_rec app_name_record;
266
267 -- local variables
268 l_mandatory_check BOOLEAN;
269 l_app_valid_status BOOLEAN;
270 l_adm_imp_status BOOLEAN;
271 l_adm_error_encountered BOOLEAN;
272 l_dummy VARCHAR2(1);
273 l_oss_country_code igs_he_code_map_val.map2%TYPE;
274
275 l_rep_request_id NUMBER;
276 l_sent_to_oss_flag IGS_UC_APP_NAMES.sent_to_oss_flag%TYPE;
277 l_dom_text_value IGS_UC_REF_APR.dom_text%TYPE;
278 l_ad_batch_id IGS_AD_IMP_BATCH_DET.batch_id%TYPE;
279 l_ad_interface_id IGS_AD_INTERFACE_ALL.interface_id%TYPE;
280
281
282 -- Bug No. 2980137. Variable to obtain the return code.
283 l_retcode NUMBER;
284
285 -- anwest UCFD040 Bug# 4015492 New local variables required
286 l_res_term_det res_term_table_type;
287 l_res_term_loc NUMBER;
288 l_term_cal_type IGS_CA_INST_ALL.cal_type%TYPE;
289 l_term_sequence_number IGS_CA_INST_ALL.sequence_number%TYPE;
290 l_prev_system_code IGS_UC_SYS_CALNDRS.system_code%TYPE;
291 l_prev_entry_year IGS_UC_SYS_CALNDRS.entry_year%TYPE;
292
293 PROCEDURE get_term_dtls(p_app_no IN NUMBER,
294 p_res_term_det IN res_term_table_type,
295 p_term_cal_type OUT NOCOPY VARCHAR2,
296 p_term_sequence_number OUT NOCOPY NUMBER,
297 p_app_valid_status IN OUT NOCOPY BOOLEAN) IS
298
299 /******************************************************************************
300 Created By : ANWEST
301 Date Created By : 25-NOV-2004
302 Purpose : Created for UCFD040 Bug# 4015492. Attempts to retrieve the
303 earliest Load Calender Instance from the local Table data
304 type, matching the application system code and entry year
305
306 Known limitations,enhancements,remarks:
307
308 CHANGE HISTORY:
309 WHO WHEN WHAT
310 anwest 20-Dec_2004 Bug# 4080259 Modified logging logic for applicants
311 without any choices
312 ******************************************************************************/
313
314 -- retrieves application system codes and entry years from their
315 -- application choices
316 CURSOR cur_get_syscode_entryyr(cp_app_no igs_uc_applicants.app_no%TYPE ) IS
317 SELECT system_code, entry_year
318 FROM IGS_UC_APP_CHOICES
319 WHERE app_no = cp_app_no
320 ORDER BY entry_year;
321
322 -- local variables required
323 l_appl_sys_code IGS_UC_APP_CHOICES.system_code%TYPE;
324 l_appl_entry_yr IGS_UC_APP_CHOICES.entry_year%TYPE;
325 l_found_term BOOLEAN;
326 l_count NUMBER;
327
328 BEGIN
329
330 -- set local variables
331 l_appl_sys_code := NULL;
332 l_appl_entry_yr := NULL;
333 l_found_term := FALSE;
334 l_count := 0;
335
336 -- loop through each application choice of an applicant
337 FOR cur_get_syscode_entryyr_rec IN cur_get_syscode_entryyr(p_app_no) LOOP
338
339 l_count := l_count + 1;
340 l_appl_sys_code := cur_get_syscode_entryyr_rec.system_code;
341 l_appl_entry_yr := cur_get_syscode_entryyr_rec.entry_year;
342
343 -- loop through the collection looking for a match
344 FOR l_loc IN p_res_term_det.FIRST..p_res_term_det.LAST LOOP
345
346 -- if a match is found set the OUT parameters
347 -- and exit this loop
348 IF p_res_term_det(l_loc).system_code = l_appl_sys_code AND
349 p_res_term_det(l_loc).entry_year = l_appl_entry_yr THEN
350
351 p_term_cal_type := p_res_term_det(l_loc).cal_type;
352 p_term_sequence_number := p_res_term_det(l_loc).sequence_number;
353 l_found_term := TRUE;
354 EXIT;
355
356 END IF;
357
358 END LOOP;
359
360 -- and then exit this loop as well
361 IF l_found_term THEN
362 EXIT;
363 END IF;
364
365 END LOOP;
366
367 -- anwest Bug# 4080259 Moved logging of this error message to outside
368 -- FOR LOOP to catch applicants without any choices
369 IF l_count = 0 THEN
370 fnd_file.put_line( fnd_file.LOG ,' ');
371 fnd_message.set_name('IGS','IGS_UC_APPNO_NOT_FOUND');
372 fnd_message.set_token('APP_NO', p_app_no);
373 fnd_file.put_line(fnd_file.log, fnd_message.get);
374 ELSIF p_term_cal_type IS NULL OR p_term_sequence_number IS NULL THEN
375 fnd_file.put_line( fnd_file.LOG ,' ');
376 fnd_message.set_name('IGS','IGS_UC_NO_TERM_CAL');
377 fnd_message.set_token('APP_NO', p_app_no);
378 fnd_file.put_line(fnd_file.log, fnd_message.get);
379 END IF;
380
381 EXCEPTION
382
383 WHEN OTHERS THEN
384 p_app_valid_status := FALSE;
385 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
386 fnd_message.set_token('NAME','IGS_UC_EXP_APPLICANT_DTLS.GET_TERM_DTLS'||' - '||SQLERRM);
387 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
388
389 END get_term_dtls;
390
391
392 BEGIN
393
394 --anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
395 IGS_GE_GEN_003.SET_ORG_ID;
396
397 -- inititalize concurrent manager variables
398 errbuf := NULL;
399 retcode := 0;
400
401 /* Validate parameters before starting the processing */
402 IF p_addr_usage_home IS NULL AND p_addr_usage_corr = 'HOME' THEN
403 fnd_message.set_name('IGS','IGS_UC_ADDR_USAGE_CORR');
404 errbuf := fnd_message.get;
405 fnd_file.put_line(fnd_file.LOG,errbuf);
406 retcode := 2 ;
407 RETURN;
408 ELSIF p_addr_usage_corr IS NULL AND p_addr_usage_home = 'CORR' THEN
409 fnd_message.set_name('IGS','IGS_UC_ADDR_USAGE_HOME');
410 errbuf := fnd_message.get;
411 fnd_file.put_line(fnd_file.LOG,errbuf);
412 retcode := 2 ;
413 RETURN;
414 ELSIF p_addr_usage_corr = p_addr_usage_home THEN
415 fnd_message.set_name('IGS','IGS_UC_ADDR_USAGE_SAME');
416 errbuf := fnd_message.get;
417 fnd_file.put_line(fnd_file.LOG,errbuf);
418 retcode := 2 ;
419 RETURN;
420 END IF;
421
422
423 /* Check the mandatory validations before starting the processing
424 If any one of them are not satisfied then log message and exit the processing */
425 l_mandatory_check := TRUE;
426
427 -- Check whether the Person Source Type 'UCAS PER' defined in the setup
428 OPEN cur_pe_src_types;
429 FETCH cur_pe_src_types INTO l_src_type_id;
430 IF cur_pe_src_types%NOTFOUND THEN
431
432 fnd_file.put_line( fnd_file.LOG ,' ');
433 fnd_message.set_name('IGS','IGS_UC_NO_UCAS_SRC_TYP');
434 fnd_file.put_line(fnd_file.log, fnd_message.get);
435 l_mandatory_check := FALSE;
436
437 END IF;
438 CLOSE cur_pe_src_types;
439
440 -- Check whether the NMAS System is defined and NI Number Person ID Type
441 -- is not defined in UCAS Setup for NMAS System
442 OPEN cur_ninumber_alt_type;
443 FETCH cur_ninumber_alt_type INTO cur_ninumber_alt_type_rec ;
444 IF cur_ninumber_alt_type%FOUND AND cur_ninumber_alt_type_rec.ni_number_alt_pers_type IS NULL THEN
445
446 fnd_file.put_line( fnd_file.LOG ,' ');
447 fnd_message.set_name('IGS','IGS_UC_NI_TYPE_NOT_SET');
448 fnd_message.set_token('SYSTEM_NAME', cur_ninumber_alt_type_rec.name);
449 fnd_file.put_line(fnd_file.log, fnd_message.get);
450 l_mandatory_check := FALSE;
451
452 END IF;
453 CLOSE cur_ninumber_alt_type;
454
455 -- Check whether the Residency Class Profile is defined for Residency Category import
456 IF fnd_profile.value('IGS_FI_RES_CLASS_ID') IS NULL THEN
457
458 fnd_file.put_line( fnd_file.LOG ,' ');
459 fnd_message.set_name('IGS','IGS_UC_RES_CLASS_NOT_DEF');
460 fnd_file.put_line(fnd_file.log, fnd_message.get);
461 l_mandatory_check := FALSE;
462
463 END IF;
464
465 -- Check Whether Decision Maker ID has been set for each of the Systems which have atleast
466 -- one Applicant record for processing. Added the code as part of bug# 3553352
467 FOR chk_app_systems_rec IN cur_chk_app_systems(p_app_no)
468 LOOP
469
470 OPEN cur_chk_dcsn_maker_setup(chk_app_systems_rec.system_code);
471 FETCH cur_chk_dcsn_maker_setup INTO chk_dcsn_maker_setup_rec;
472 CLOSE cur_chk_dcsn_maker_setup;
473
474 IF chk_dcsn_maker_setup_rec.decision_make_id IS NULL THEN
475 fnd_message.set_name('IGS','IGS_UC_SETUP_DEC_MAKE');
476 fnd_message.set_token('SYSTEM', chk_dcsn_maker_setup_rec.name );
477 fnd_file.put_line(fnd_file.log, fnd_message.get);
478 l_mandatory_check := FALSE;
479 END IF;
480
481 END LOOP;
482
483 -- anwest UCFD040 Bug# 4015492 New mandatory check
484 -- Check whether there is at least one current cycle for any system code present
485 -- If there is, check whether any term/load calenders can be retrieved for that year
486 -- If it does, populate the Table data type for quick lookup later when populating
487 -- residency term
488
489 OPEN cur_get_current_cycle;
490 FETCH cur_get_current_cycle INTO l_max_curr_cycle;
491
492 IF cur_get_current_cycle%NOTFOUND OR
493 l_max_curr_cycle IS NULL THEN
494
495 CLOSE cur_get_current_cycle;
496 fnd_file.put_line(fnd_file.LOG ,' ');
497 fnd_message.set_name('IGS','IGS_UC_CYCLE_NOT_FOUND');
498 fnd_file.put_line(fnd_file.log, fnd_message.get);
499 l_mandatory_check := FALSE;
500
501 ELSE
502
503 CLOSE cur_get_current_cycle;
504 l_res_term_loc := 0;
505
506 FOR cur_get_term_rec IN cur_get_term(l_max_curr_cycle - 2000) LOOP
507
508 IF (l_prev_system_code IS NULL AND l_prev_entry_year IS NULL) OR
509 (cur_get_term_rec.system_code <> l_prev_system_code) OR
510 (cur_get_term_rec.entry_year <> l_prev_entry_year) THEN
511
512 l_res_term_det(l_res_term_loc).system_code := cur_get_term_rec.system_code;
513 l_res_term_det(l_res_term_loc).entry_year := cur_get_term_rec.entry_year;
514 l_res_term_det(l_res_term_loc).cal_type := cur_get_term_rec.cal_type;
515 l_res_term_det(l_res_term_loc).sequence_number := cur_get_term_rec.sequence_number;
516 l_prev_system_code := cur_get_term_rec.system_code;
517 l_prev_entry_year := cur_get_term_rec.entry_year;
518 l_res_term_loc := l_res_term_loc + 1;
519
520 END IF;
521
522 END LOOP;
523
524 IF l_res_term_loc = 0 THEN
525
526 fnd_file.put_line(fnd_file.LOG ,' ');
527 fnd_message.set_name('IGS','IGS_UC_NO_LOAD_CAL_FOUND');
528 fnd_message.set_token('PREV_CYCLE', l_max_curr_cycle - 1);
529 fnd_message.set_token('CURR_CYCLE', l_max_curr_cycle);
530 fnd_message.set_token('NEXT_CYCLE', l_max_curr_cycle + 1);
531 fnd_file.put_line(fnd_file.log, fnd_message.get);
532 l_mandatory_check := FALSE;
533
534 END IF;
535
536 END IF;
537
538
539 -- If any of the Mandatory Validations are failed then exit the processing
540 IF l_mandatory_check = FALSE THEN
541 retcode := 2 ;
542 RETURN;
543 END IF;
544
545 /******** End mandatory checks */
546
547
548 /******** Populating the Admission Interface Tables to export Applicant Details to OSS *********/
549
550 -- Initialize the Batch ID to NULL
551 l_ad_batch_id := NULL;
552
553 -- Log the message 'Exporting Applicant details to OSS' with TimeStamp
554 fnd_message.set_name('IGS','IGS_UC_EXP_APPLCNT_DET');
555 fnd_file.put_line(fnd_file.log, fnd_message.get||' ('||to_char(SYSDATE,'DD-MON-YYYY HH:MI:SS')||')');
556 fnd_file.put_line( fnd_file.LOG ,' ');
557
558 --jchakrab added for 3691176
559 IF p_app_no IS NULL THEN
560 OPEN cur_exp_applicant_dtls FOR
561 SELECT APP_NO, SYSTEM_CODE, OSS_PERSON_ID, SCN, NI_NUMBER, RESIDENTIAL_CATEGORY,
562 NATIONALITY, DUAL_NATIONALITY, SPECIAL_NEEDS, SCHOOL, APPLICATION_DATE,
563 COUNTRY_BIRTH
564 FROM IGS_UC_APPLICANTS UCAP
565 WHERE UCAP.SENT_TO_OSS = 'N'
566 ORDER BY UCAP.APP_NO;
567
568 ELSE
569 OPEN cur_exp_applicant_dtls FOR
570 SELECT APP_NO, SYSTEM_CODE, OSS_PERSON_ID, SCN, NI_NUMBER, RESIDENTIAL_CATEGORY,
571 NATIONALITY, DUAL_NATIONALITY, SPECIAL_NEEDS, SCHOOL, APPLICATION_DATE,
572 COUNTRY_BIRTH
573 FROM IGS_UC_APPLICANTS UCAP
574 WHERE UCAP.APP_NO = p_app_no AND
575 UCAP.SENT_TO_OSS = 'N'
576 ORDER BY UCAP.APP_NO;
577 END IF;
578
579
580 -- Loop through all the records in IGS_UC_APPLICANTS table satisfying the criteria,
581 -- SENT_TO_OSS is N and passed parameter,P_APP_NO
582 LOOP
583 FETCH cur_exp_applicant_dtls INTO cur_exp_applicant_dtls_rec;
584 EXIT WHEN cur_exp_applicant_dtls%NOTFOUND;
585
586 -- Check whether the OSS Person ID value is populated for this Applicant
587 -- If OSS Person ID is null then log the message and stop processing the current applicant
588 IF cur_exp_applicant_dtls_rec.oss_person_id is NULL THEN
589
590 -- Log the message 'OSS Person ID is not populated for the Application Number'
591 fnd_message.set_name('IGS','IGS_UC_APP_PER_ID_NOT_EXITS');
592 fnd_message.set_token('APP_NO',cur_exp_applicant_dtls_rec.app_no);
593 fnd_file.put_line(fnd_file.log, fnd_message.get);
594
595 ELSE
596
597 -- get the Applicant Person Details in OSS
598 cur_uc_person_dtls_rec := NULL;
599 OPEN cur_uc_person_dtls(cur_exp_applicant_dtls_rec.oss_person_id);
600 FETCH cur_uc_person_dtls INTO cur_uc_person_dtls_rec;
601 CLOSE cur_uc_person_dtls;
602
603 -- Log the message 'Processing the Applicant with Person Number: XXX and Application Number: XXX'
604 fnd_message.set_name('IGS','IGS_UC_EXP_APP_DET_PROC');
605 fnd_message.set_token('PER_NO',cur_uc_person_dtls_rec.person_number);
606 fnd_message.set_token('APP_NO',cur_exp_applicant_dtls_rec.app_no);
607 fnd_file.put_line(fnd_file.log, fnd_message.get);
608
609 -- If the Batch ID is already generated use the same,
610 -- otherwise create the new Batch ID ( i.e. for First Time)
611 IF l_ad_batch_id IS NULL THEN
612
613 -- Fetch the Batch ID from the Sequence, IGS_AD_INTERFACE_BATCH_ID_S and
614 -- populate the admission interface batch table
615 INSERT INTO igs_ad_imp_batch_det (
616 batch_id,
617 batch_desc,
618 created_by,
619 creation_date,
620 last_updated_by,
621 last_update_date,
622 last_update_login,
623 request_id,
624 program_application_id,
625 program_update_date,
626 program_id )
627 VALUES (
628 IGS_AD_INTERFACE_BATCH_ID_S.NEXTVAL,
629 fnd_message.get_string('IGS','IGS_UC_IMP_FROM_UCAS_BATCH_ID'),
630 g_created_by,
631 SYSDATE,
632 g_last_updated_by,
633 SYSDATE,
634 g_last_update_login,
635 DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_request_id),
636 DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.prog_appl_id),
637 DECODE(fnd_global.conc_request_id,-1,NULL,SYSDATE),
638 DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_program_id) )
639 RETURNING batch_id INTO l_ad_batch_id;
640
641 END IF;
642
643 -- create the save point, l_exp_curr_applicant
644 SAVEPOINT l_exp_curr_applicant;
645
646 -- Initialize the Applicant valid status to TRUE
647 l_app_valid_status := TRUE;
648
649 /* Populate the IGS_AD_INTERFACE_ALL table with Interface ID, Batch ID, Source Type ID,Person ID,
650 match_ind as "15" and person details */
651
652 -- Check if a record exists in IGS_AD_INTERFACE_ALL for the Admissions Batch ID and Person ID
653 -- If exists use the same ID( that is If same person was processed earlier for some other
654 -- UCAS system Application), otherwise create a new Interface ID
655 l_ad_interface_id := NULL;
656
657 OPEN cur_ad_interface_id(l_ad_batch_id, cur_exp_applicant_dtls_rec.oss_person_id);
658 FETCH cur_ad_interface_id INTO l_ad_interface_id;
659 CLOSE cur_ad_interface_id;
660
661 IF l_ad_interface_id IS NULL THEN
662
663 -- Get OSS country code
664 l_oss_country_code := NULL;
665 OPEN cur_hesa_map('UCAS_OSS_COUNTRY_ASSOC',cur_exp_applicant_dtls_rec.country_birth);
666 FETCH cur_hesa_map INTO l_oss_country_code;
667 CLOSE cur_hesa_map;
668
669 -- Log a warning if country code exists but OSS mapping doesn't
670 IF l_oss_country_code IS NULL AND cur_exp_applicant_dtls_rec.country_birth IS NOT NULL THEN
671 fnd_message.set_name('IGS','IGS_UC_INV_COUNTRY_MAP');
672 fnd_file.put_line(fnd_file.log, fnd_message.get);
673 END IF;
674
675 INSERT INTO igs_ad_interface_all (
676 interface_id,
677 batch_id,
678 source_type_id,
679 person_id,
680 match_ind,
681 surname,
682 given_names,
683 sex,
684 birth_dt,
685 pre_name_adjunct,
686 status,
687 record_status,
688 pref_alternate_id,
689 birth_country,
690 created_by,
691 creation_date,
692 last_updated_by,
693 last_update_date,
694 last_update_login )
695 VALUES (
696 IGS_AD_INTERFACE_S.NEXTVAL,
697 l_ad_batch_id,
698 l_src_type_id,
699 cur_uc_person_dtls_rec.person_id,
700 '15',
701 cur_uc_person_dtls_rec.surname,
702 cur_uc_person_dtls_rec.given_names,
703 cur_uc_person_dtls_rec.sex,
704 cur_uc_person_dtls_rec.birth_date,
705 cur_uc_person_dtls_rec.pre_name_adjunct,
706 '2',
707 '2',
708 NULL,
709 l_oss_country_code,
710 g_created_by,
711 SYSDATE,
712 g_last_updated_by,
713 SYSDATE,
714 g_last_update_login )
715 RETURNING interface_id INTO l_ad_interface_id ;
716
717 END IF;
718
719 -- Check the Admission Source Categories Setup included Alternate Person Type Category.
720 -- If included Populate the Interface Table,
721 -- so that this record will be processed by the Admission Import Process
722 IF cur_exp_applicant_dtls_rec.ni_number IS NOT NULL OR cur_exp_applicant_dtls_rec.scn IS NOT NULL THEN
723
724 IF chk_src_cat(l_src_type_id, 'PERSON_ID_TYPES') THEN
725
726 -- Call the procedure to populate the Alternate Person ID interface table for NI NUMBER
727 pop_api_int ( cur_exp_applicant_dtls_rec.ni_number,
728 cur_ninumber_alt_type_rec.ni_number_alt_pers_type,
729 cur_exp_applicant_dtls_rec.scn,
730 cur_exp_applicant_dtls_rec.oss_person_id,
731 l_ad_interface_id, l_app_valid_status);
732 ELSE
733
734 -- Display the warning message in the log file
735 fnd_message.set_name('IGS','IGS_UC_ADM_INT_NOT_IMP');
736 fnd_message.set_token('INT_TYPE', 'ALTERNATE PERSON ID');
737 fnd_file.put_line(fnd_file.log, fnd_message.get);
738
739 END IF;
740
741 END IF;
742
743 -- Check the Admission Source Categories Setup included Residential Ctaegory Category.
744 -- If included Populate the Interface Table,
745 -- so that this record will be processed by the Admission Import Process
746 IF cur_exp_applicant_dtls_rec.residential_category IS NOT NULL THEN
747
748 IF chk_src_cat(l_src_type_id,'PERSON_RESIDENCY_DETAILS') THEN
749
750 -- anwest UCFD040 Bug# 4015492 New logic to retrieve Load Calender
751 -- before populating IGS_PE_RES_DTLS_INT
752
753 l_term_cal_type := NULL;
754 l_term_sequence_number := NULL;
755
756 -- retrieve the CAL_TYPE and SEQUENCE_NUMBER from the user
757 -- defined Table type for this application
758 -- anwest 21-Jul-2005 Bug# 4465994 Corrected app_no parameter
759 get_term_dtls(cur_exp_applicant_dtls_rec.app_no,
760 l_res_term_det, l_term_cal_type,
761 l_term_sequence_number,
762 l_app_valid_status);
763
764 -- anwest Bug# 4080259 Moved logging of error messages to
765 -- get_term_dtls procedure
766 -- if they exist, populate the interface table
767 IF l_term_cal_type IS NOT NULL AND
768 l_term_sequence_number IS NOT NULL THEN
769
770 pop_res_dtls_int(cur_exp_applicant_dtls_rec.residential_category,
771 cur_exp_applicant_dtls_rec.application_date,
772 cur_exp_applicant_dtls_rec.system_code,
773 l_ad_interface_id,
774 l_term_cal_type, -- anwest UCFD040 Bug# 4015492 Added new parameter
775 l_term_sequence_number, -- anwest UCFD040 Bug# 4015492 Added new parameter
776 l_app_valid_status);
777 END IF;
778
779 ELSE
780
781 -- Display the warning message in the log file
782 fnd_message.set_name('IGS','IGS_UC_ADM_INT_NOT_IMP');
783 fnd_message.set_token('INT_TYPE', 'RESIDENCY');
784 fnd_file.put_line(fnd_file.log, fnd_message.get);
785
786 END IF;
787
788 END IF;
789
790 -- Check the Admission Source Categories Setup included International Details Category.
791 -- If included Populate the Interface Table,
792 -- so that this record will be processed by the Admission Import Process
793 IF cur_exp_applicant_dtls_rec.nationality IS NOT NULL OR cur_exp_applicant_dtls_rec.dual_nationality IS NOT NULL THEN
794
795 IF chk_src_cat(l_src_type_id,'PERSON_INTERNATIONAL_DETAILS') THEN
796
797 -- Call the procedure to populate the Nationality and Dual Nationality details
798 pop_citizen_int(cur_exp_applicant_dtls_rec.nationality,
799 cur_exp_applicant_dtls_rec.dual_nationality,
800 cur_exp_applicant_dtls_rec.oss_person_id,
801 cur_exp_applicant_dtls_rec.application_date,
802 l_ad_interface_id, l_app_valid_status);
803 ELSE
804
805 -- Display the warning message in the log file
806 fnd_message.set_name('IGS','IGS_UC_ADM_INT_NOT_IMP');
807 fnd_message.set_token('INT_TYPE', 'INTERNATIONAL');
808 fnd_file.put_line(fnd_file.log, fnd_message.get);
809
810 END IF;
811
812 END IF;
813
814 -- Check the Admission Source Categories Setup included Special Needs Category.
815 -- If included Populate the Interface Table,
816 -- so that this record will be processed by the Admission Import Process
817 IF cur_exp_applicant_dtls_rec.special_needs IS NOT NULL THEN
818
819 IF chk_src_cat(l_src_type_id,'PERSON_SPECIAL_NEEDS') THEN
820 -- Call the procedure to populate the Special Needs
821 pop_disability_int(cur_exp_applicant_dtls_rec.special_needs,
822 cur_exp_applicant_dtls_rec.oss_person_id,
823 cur_exp_applicant_dtls_rec.application_date,
824 l_ad_interface_id, l_app_valid_status);
825 ELSE
826
827 -- Display the warning message in the log file
828 fnd_message.set_name('IGS','IGS_UC_ADM_INT_NOT_IMP');
829 fnd_message.set_token('INT_TYPE', 'SPECIAL NEEDS');
830 fnd_file.put_line(fnd_file.log, fnd_message.get);
831
832 END IF;
833
834 END IF;
835
836 -- Check the Admission Source Categories Setup included Academic History Category
837 -- If included Populate the Interface Table,
838 -- so that this record will be processed by the Admission Import Process
839 IF cur_exp_applicant_dtls_rec.school IS NOT NULL THEN
840
841 IF chk_src_cat(l_src_type_id,'PERSON_ACADEMIC_HISTORY') THEN
842
843 -- Call the procedure to populate the academic history
844 pop_acad_hist_int(cur_exp_applicant_dtls_rec.oss_person_id,
845 cur_uc_person_dtls_rec.person_number,
846 cur_exp_applicant_dtls_rec.school,
847 l_ad_interface_id,
848 l_app_valid_status);
849 ELSE
850 -- Display the warning message in the log file
851 fnd_message.set_name('IGS','IGS_UC_ADM_INT_NOT_IMP');
852 fnd_message.set_token('INT_TYPE', 'ACADEMIC HISTORY');
853 fnd_file.put_line(fnd_file.log, fnd_message.get);
854
855 END IF;
856
857
858 END IF;
859
860 IF l_app_valid_status = FALSE THEN
861
862 -- Delete all the entries for the current applicant from interface tables
863 -- except the batch Table since the batch_id is same for all Applicants
864 -- i.e. roll back upto to l_exp_curr_applicant
865 ROLLBACK TO l_exp_curr_applicant;
866
867 ELSE
868
869 cur_ucas_app_rec := NULL;
870 OPEN cur_upd_ucas_app (cur_exp_applicant_dtls_rec.app_no);
871 FETCH cur_upd_ucas_app INTO cur_ucas_app_rec;
872 CLOSE cur_upd_ucas_app;
873
874 -- Update the UCAS Applicants Table with Batch ID and Interface ID
875 igs_uc_applicants_pkg.update_row (
876 x_rowid => cur_ucas_app_rec.rowid
877 ,x_app_id => cur_ucas_app_rec.app_id
878 ,x_app_no => cur_ucas_app_rec.app_no
879 ,x_check_digit => cur_ucas_app_rec.check_digit
880 ,x_personal_id => cur_ucas_app_rec.personal_id
881 ,x_enquiry_no => cur_ucas_app_rec.enquiry_no
882 ,x_oss_person_id => cur_ucas_app_rec.oss_person_id
883 ,x_application_source => cur_ucas_app_rec.application_source
884 ,x_name_change_date => cur_ucas_app_rec.name_change_date
885 ,x_student_support => cur_ucas_app_rec.student_support
886 ,x_address_area => cur_ucas_app_rec.address_area
887 ,x_application_date => cur_ucas_app_rec.application_date
888 ,x_application_sent_date => cur_ucas_app_rec.application_sent_date
889 ,x_application_sent_run => cur_ucas_app_rec.application_sent_run
890 ,x_lea_code => cur_ucas_app_rec.lea_code
891 ,x_fee_payer_code => cur_ucas_app_rec.fee_payer_code
892 ,x_fee_text => cur_ucas_app_rec.fee_text
893 ,x_domicile_apr => cur_ucas_app_rec.domicile_apr
894 ,x_code_changed_date => cur_ucas_app_rec.code_changed_date
895 ,x_school => cur_ucas_app_rec.school
896 ,x_withdrawn => cur_ucas_app_rec.withdrawn
897 ,x_withdrawn_date => cur_ucas_app_rec.withdrawn_date
898 ,x_rel_to_clear_reason => cur_ucas_app_rec.rel_to_clear_reason
899 ,x_route_b => cur_ucas_app_rec.route_b
900 ,x_exam_change_date => cur_ucas_app_rec.exam_change_date
901 ,x_a_levels => cur_ucas_app_rec.a_levels
902 ,x_as_levels => cur_ucas_app_rec.as_levels
903 ,x_highers => cur_ucas_app_rec.highers
904 ,x_csys => cur_ucas_app_rec.csys
905 ,x_winter => cur_ucas_app_rec.winter
906 ,x_previous => cur_ucas_app_rec.previous
907 ,x_gnvq => cur_ucas_app_rec.gnvq
908 ,x_btec => cur_ucas_app_rec.btec
909 ,x_ilc => cur_ucas_app_rec.ilc
910 ,x_ailc => cur_ucas_app_rec.ailc
911 ,x_ib => cur_ucas_app_rec.ib
912 ,x_manual => cur_ucas_app_rec.manual
913 ,x_reg_num => cur_ucas_app_rec.reg_num
914 ,x_oeq => cur_ucas_app_rec.oeq
915 ,x_eas => cur_ucas_app_rec.eas
916 ,x_roa => cur_ucas_app_rec.roa
917 ,x_status => cur_ucas_app_rec.status
918 ,x_firm_now => cur_ucas_app_rec.firm_now
919 ,x_firm_reply => cur_ucas_app_rec.firm_reply
920 ,x_insurance_reply => cur_ucas_app_rec.insurance_reply
921 ,x_conf_hist_firm_reply => cur_ucas_app_rec.conf_hist_firm_reply
922 ,x_conf_hist_ins_reply => cur_ucas_app_rec.conf_hist_ins_reply
923 ,x_residential_category => cur_ucas_app_rec.residential_category
924 ,x_personal_statement => cur_ucas_app_rec.personal_statement
925 ,x_match_prev => cur_ucas_app_rec.match_prev
926 ,x_match_prev_date => cur_ucas_app_rec.match_prev_date
927 ,x_match_winter => cur_ucas_app_rec.match_winter
928 ,x_match_summer => cur_ucas_app_rec.match_summer
929 ,x_gnvq_date => cur_ucas_app_rec.gnvq_date
930 ,x_ib_date => cur_ucas_app_rec.ib_date
931 ,x_ilc_date => cur_ucas_app_rec.ilc_date
932 ,x_ailc_date => cur_ucas_app_rec.ailc_date
933 ,x_gcseqa_date => cur_ucas_app_rec.gcseqa_date
934 ,x_uk_entry_date => cur_ucas_app_rec.uk_entry_date
935 ,x_prev_surname => cur_ucas_app_rec.prev_surname
936 ,x_criminal_convictions => cur_ucas_app_rec.criminal_convictions
937 ,x_sent_to_hesa => cur_ucas_app_rec.sent_to_hesa
938 ,x_sent_to_oss => cur_ucas_app_rec.sent_to_oss
939 ,x_batch_identifier => cur_ucas_app_rec.batch_identifier
940 ,x_mode => 'R'
941 ,x_gce => cur_ucas_app_rec.gce
942 ,x_vce => cur_ucas_app_rec.vce
943 ,x_sqa => cur_ucas_app_rec.sqa
944 ,x_previousas => cur_ucas_app_rec.previousas
945 ,x_keyskills => cur_ucas_app_rec.keyskills
946 ,x_vocational => cur_ucas_app_rec.vocational
947 ,x_scn => cur_ucas_app_rec.scn
948 ,x_prevoeq => cur_ucas_app_rec.prevoeq
949 ,x_choices_transparent_ind => cur_ucas_app_rec.choices_transparent_ind
950 ,x_extra_status => cur_ucas_app_rec.extra_status
951 ,x_extra_passport_no => cur_ucas_app_rec.extra_passport_no
952 ,x_request_app_dets_ind => cur_ucas_app_rec.request_app_dets_ind
953 ,x_request_copy_app_frm_ind => cur_ucas_app_rec.request_copy_app_frm_ind
954 ,x_cef_no => cur_ucas_app_rec.cef_no
955 ,x_system_code => cur_ucas_app_rec.system_code
956 ,x_gcse_eng => cur_ucas_app_rec.gcse_eng
957 ,x_gcse_math => cur_ucas_app_rec.gcse_math
958 ,x_degree_subject => cur_ucas_app_rec.degree_subject
959 ,x_degree_status => cur_ucas_app_rec.degree_status
960 ,x_degree_class => cur_ucas_app_rec.degree_class
961 ,x_gcse_sci => cur_ucas_app_rec.gcse_sci
962 ,x_welshspeaker => cur_ucas_app_rec.welshspeaker
963 ,x_ni_number => cur_ucas_app_rec.ni_number
964 ,x_earliest_start => cur_ucas_app_rec.earliest_start
965 ,x_near_inst => cur_ucas_app_rec.near_inst
966 ,x_pref_reg => cur_ucas_app_rec.pref_reg
967 ,x_qual_eng => cur_ucas_app_rec.qual_eng
968 ,x_qual_math => cur_ucas_app_rec.qual_math
969 ,x_qual_sci => cur_ucas_app_rec.qual_sci
970 ,x_main_qual => cur_ucas_app_rec.main_qual
971 ,x_qual_5 => cur_ucas_app_rec.qual_5
972 ,x_future_serv => cur_ucas_app_rec.future_serv
973 ,x_future_set => cur_ucas_app_rec.future_set
974 ,x_present_serv => cur_ucas_app_rec.present_serv
975 ,x_present_set => cur_ucas_app_rec.present_set
976 ,x_curr_employment => cur_ucas_app_rec.curr_employment
977 ,x_edu_qualification => cur_ucas_app_rec.edu_qualification
978 ,x_ad_batch_id => l_ad_batch_id
979 ,x_ad_interface_id => l_ad_interface_id
980 ,x_nationality => cur_ucas_app_rec.nationality
981 ,x_dual_nationality => cur_ucas_app_rec.dual_nationality
982 ,x_special_needs => cur_ucas_app_rec.special_needs
983 ,x_country_birth => cur_ucas_app_rec.country_birth );
984
985 END IF; -- End of validating the current Applicant Status
986
987 END IF; -- End of OSS Person ID Check
988
989 END LOOP; -- End of looping through the Applicant Records
990 CLOSE cur_exp_applicant_dtls;
991
992 /************* End of Exporting Applicant Details to OSS *****************/
993
994
995 /****** Poulating the Admission Interface Tables to export Applicant Address Details to OSS *****/
996
997 -- Log the message 'Exporting Applicant Address details to OSS' with Time Stamp
998 fnd_file.put_line( fnd_file.LOG ,' ');
999 fnd_message.set_name('IGS','IGS_UC_EXP_APPLCNT_ADDR_DET');
1000 fnd_file.put_line(fnd_file.log, fnd_message.get||' ('||to_char(SYSDATE,'DD-MON-YYYY HH:MI:SS')||')');
1001 fnd_file.put_line( fnd_file.LOG ,' ');
1002
1003 --jchakrab added for 3691210
1004 IF p_app_no IS NULL THEN
1005 OPEN cur_app_address_dtls FOR
1006 SELECT *
1007 FROM IGS_UC_APP_ADDRESES
1008 WHERE SENT_TO_OSS_FLAG = 'N'
1009 ORDER BY APP_NO;
1010 ELSE
1011 OPEN cur_app_address_dtls FOR
1012 SELECT *
1013 FROM IGS_UC_APP_ADDRESES
1014 WHERE APP_NO = p_app_no AND
1015 SENT_TO_OSS_FLAG = 'N'
1016 ORDER BY APP_NO;
1017 END IF;
1018
1019 -- Loop through all the records in IGS_UC_APP_ADDRESES table satisfying the criteria,
1020 -- SENT_TO_OSS_FLAG is N and passed parameter,P_APP_NO
1021 LOOP
1022 FETCH cur_app_address_dtls INTO cur_app_address_dtls_rec;
1023 EXIT WHEN cur_app_address_dtls%NOTFOUND;
1024
1025 -- Get the OSS Person ID of the UCAS Applicant
1026 cur_uc_app_dtls_rec := NULL;
1027 OPEN cur_uc_app_dtls(cur_app_address_dtls_rec.app_no);
1028 FETCH cur_uc_app_dtls INTO cur_uc_app_dtls_rec;
1029 CLOSE cur_uc_app_dtls;
1030
1031 -- Check whether the OSS Person ID value is populated for this Applicant
1032 -- If OSS Person ID is null then log the message and stop processing the current applicant
1033 IF cur_uc_app_dtls_rec.oss_person_id IS NULL THEN
1034
1035 -- Log the message 'OSS Person ID is not populated for the Application Number'
1036 fnd_message.set_name('IGS','IGS_UC_APP_PER_ID_NOT_EXITS');
1037 fnd_message.set_token('APP_NO',cur_uc_app_dtls_rec.app_no);
1038 fnd_file.put_line(fnd_file.log, fnd_message.get);
1039
1040 ELSE
1041
1042 -- get the Applicant Person Details in OSS
1043 OPEN cur_uc_person_dtls(cur_uc_app_dtls_rec.oss_person_id);
1044 FETCH cur_uc_person_dtls INTO cur_uc_person_dtls_rec;
1045 CLOSE cur_uc_person_dtls;
1046
1047 -- Log the message 'Processing the Applicant with Person Number: XXX and Applicantion Number: XXX'
1048 fnd_message.set_name('IGS','IGS_UC_EXP_APP_DET_PROC');
1049 fnd_message.set_token('PER_NO',cur_uc_person_dtls_rec.person_number);
1050 fnd_message.set_token('APP_NO',cur_uc_app_dtls_rec.app_no);
1051 fnd_file.put_line(fnd_file.log, fnd_message.get);
1052
1053 -- If the Batch ID is already generated use the same,
1054 -- otherwise create the new Batch ID ( i.e. for First Time)
1055 IF l_ad_batch_id IS NULL THEN
1056
1057 -- Fetch the Batch ID from the Sequence, IGS_AD_INTERFACE_BATCH_ID_S and
1058 -- populate the admission interface batch table
1059
1060 INSERT INTO igs_ad_imp_batch_det (
1061 batch_id,
1062 batch_desc,
1063 created_by,
1064 creation_date,
1065 last_updated_by,
1066 last_update_date,
1067 last_update_login,
1068 request_id,
1069 program_application_id,
1070 program_update_date,
1071 program_id)
1072 VALUES (
1073 IGS_AD_INTERFACE_BATCH_ID_S.NEXTVAL,
1074 fnd_message.get_string('IGS','IGS_UC_IMP_FROM_UCAS_BATCH_ID'),
1075 fnd_global.user_id,
1076 SYSDATE,
1077 fnd_global.user_id,
1078 SYSDATE,
1079 fnd_global.login_id,
1080 DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_request_id),
1081 DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.prog_appl_id),
1082 DECODE(fnd_global.conc_request_id,-1,NULL,SYSDATE),
1083 DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_program_id)
1084 )
1085 RETURNING batch_id INTO l_ad_batch_id;
1086
1087 END IF;
1088
1089 -- create the save point, l_exp_curr_applicant
1090 SAVEPOINT l_exp_curr_applicant;
1091
1092 -- Initialize the Applicant valid status to TRUE
1093 l_app_valid_status := TRUE;
1094
1095 -- Check if a record exists in IGS_AD_INTERFACE_ALL for the Admissions Batch ID and Person ID
1096 -- If exists use the same ID, otherwise create a new Interface ID
1097 l_ad_interface_id := NULL;
1098
1099 OPEN cur_ad_interface_id(l_ad_batch_id, cur_uc_app_dtls_rec.oss_person_id);
1100 FETCH cur_ad_interface_id INTO l_ad_interface_id;
1101 CLOSE cur_ad_interface_id;
1102
1103 IF l_ad_interface_id IS NULL THEN
1104
1105 -- Get OSS country code
1106 l_oss_country_code := NULL;
1107 OPEN cur_hesa_map('UCAS_OSS_COUNTRY_ASSOC',cur_uc_app_dtls_rec.country_birth);
1108 FETCH cur_hesa_map INTO l_oss_country_code;
1109 CLOSE cur_hesa_map;
1110
1111 -- Log a warning if country code exists but OSS mapping doesn't
1112 IF l_oss_country_code IS NULL AND cur_uc_app_dtls_rec.country_birth IS NOT NULL THEN
1113 fnd_message.set_name('IGS','IGS_UC_INV_COUNTRY_MAP');
1114 fnd_file.put_line(fnd_file.log, fnd_message.get);
1115 END IF;
1116
1117 INSERT INTO igs_ad_interface_all (
1118 interface_id,
1119 batch_id,
1120 source_type_id,
1121 person_id,
1122 match_ind,
1123 surname,
1124 given_names,
1125 sex,
1126 birth_dt,
1127 pre_name_adjunct,
1128 status,
1129 record_status,
1130 pref_alternate_id,
1131 birth_country,
1132 created_by,
1133 creation_date,
1134 last_updated_by,
1135 last_update_date,
1136 last_update_login )
1137 VALUES (
1138 IGS_AD_INTERFACE_S.NEXTVAL,
1139 l_ad_batch_id,
1140 l_src_type_id,
1141 cur_uc_app_dtls_rec.oss_person_id,
1142 '15',
1143 cur_uc_person_dtls_rec.surname,
1144 cur_uc_person_dtls_rec.given_names,
1145 cur_uc_person_dtls_rec.sex,
1146 cur_uc_person_dtls_rec.birth_date,
1147 cur_uc_person_dtls_rec.pre_name_adjunct,
1148 '2',
1149 '2',
1150 NULL,
1151 l_oss_country_code,
1152 g_created_by,
1153 SYSDATE,
1154 g_last_updated_by,
1155 SYSDATE,
1156 g_last_update_login )
1157 RETURNING interface_id INTO l_ad_interface_id ;
1158
1159 END IF;
1160
1161 -- Check the Admission Source Categories Setup included Contact Details Category.
1162 -- If included Populate the Interface Table,
1163 -- so that this record will be processed by the Admission Import Process
1164 IF cur_app_address_dtls_rec.telephone IS NOT NULL OR cur_app_address_dtls_rec.email IS NOT NULL
1165 OR cur_app_address_dtls_rec.home_phone IS NOT NULL OR cur_app_address_dtls_rec.mobile IS NOT NULL THEN
1166
1167 IF chk_src_cat(l_src_type_id,'PERSON_CONTACTS') THEN
1168
1169
1170 /* Call the Local Procedure, pop_contact_int to populate the columns,
1171 TELEPHONE, EMAIL and HOMEPHONE */
1172 pop_contact_int ( cur_app_address_dtls_rec.telephone,
1173 cur_app_address_dtls_rec.email,
1174 cur_app_address_dtls_rec.home_phone,
1175 cur_app_address_dtls_rec.mobile,
1176 l_ad_interface_id, l_app_valid_status );
1177 ELSE
1178
1179 -- Display the warning message in the log file
1180 fnd_message.set_name('IGS','IGS_UC_ADM_INT_NOT_IMP');
1181 fnd_message.set_token('INT_TYPE', 'CONTACTS');
1182 fnd_file.put_line(fnd_file.log, fnd_message.get);
1183
1184 END IF;
1185
1186 END IF;
1187
1188 -- Check the Admission Source Categories Setup included Address Details Category.
1189 -- If included Populate the Interface Table,
1190 -- so that this record will be processed by the Admission Import Process
1191 IF cur_app_address_dtls_rec.address1 IS NOT NULL OR cur_app_address_dtls_rec.address2 IS NOT NULL OR
1192 cur_app_address_dtls_rec.address3 IS NOT NULL OR cur_app_address_dtls_rec.address4 IS NOT NULL OR
1193 cur_app_address_dtls_rec.post_code IS NOT NULL OR cur_app_address_dtls_rec.home_address1 IS NOT NULL OR
1194 cur_app_address_dtls_rec.home_address2 IS NOT NULL OR cur_app_address_dtls_rec.home_address3 IS NOT NULL OR
1195 cur_app_address_dtls_rec.home_address4 IS NOT NULL OR cur_app_address_dtls_rec.home_postcode IS NOT NULL THEN
1196
1197 IF chk_src_cat(l_src_type_id,'PERSON_ADDRESS') THEN
1198
1199
1200 /* Call the Local Procedure, pop_address_int to populate the columns,
1201 Address Intreface and Address Usage Interface Tables */
1202 pop_address_int ( cur_app_address_dtls_rec, cur_uc_app_dtls_rec.domicile_apr,
1203 l_ad_interface_id, p_addr_usage_home, p_addr_usage_corr, l_app_valid_status );
1204
1205 ELSE
1206
1207 -- Display the warning message in the log file
1208 fnd_message.set_name('IGS','IGS_UC_ADM_INT_NOT_IMP');
1209 fnd_message.set_token('INT_TYPE', 'ADDRESS');
1210 fnd_file.put_line(fnd_file.log, fnd_message.get);
1211
1212 END IF;
1213
1214 END IF;
1215
1216 IF l_app_valid_status = FALSE THEN
1217
1218 -- Delete all the entries for the current applicant from interface tables
1219 -- except the batch table since the batch_id is same for all Applicants
1220 -- i.e. roll back upto to l_exp_curr_applicant
1221 ROLLBACK TO l_exp_curr_applicant;
1222
1223 ELSE
1224
1225 /* Update the IGS_UC_APP_ADDRESES Table with AD_BATCH_ID => Admissions Batch ID and
1226 AD_INTERFACE_ID => Admissions Interface ID */
1227 cur_app_address_rec := NULL;
1228 OPEN cur_upd_app_address (cur_app_address_dtls_rec.app_no);
1229 FETCH cur_upd_app_address INTO cur_app_address_rec;
1230 CLOSE cur_upd_app_address;
1231 igs_uc_app_addreses_pkg.update_row (
1232 x_rowid => cur_app_address_rec.rowid,
1233 x_app_no => cur_app_address_rec.app_no,
1234 x_address_area => cur_app_address_rec.address_area,
1235 x_address1 => cur_app_address_rec.address1,
1236 x_address2 => cur_app_address_rec.address2,
1237 x_address3 => cur_app_address_rec.address3,
1238 x_address4 => cur_app_address_rec.address4,
1239 x_post_code => cur_app_address_rec.post_code,
1240 x_mail_sort => cur_app_address_rec.mail_sort,
1241 x_telephone => cur_app_address_rec.telephone,
1242 x_fax => cur_app_address_rec.fax,
1243 x_email => cur_app_address_rec.email,
1244 x_home_address1 => cur_app_address_rec.home_address1,
1245 x_home_address2 => cur_app_address_rec.home_address2,
1246 x_home_address3 => cur_app_address_rec.home_address3,
1247 x_home_address4 => cur_app_address_rec.home_address4,
1248 x_home_postcode => cur_app_address_rec.home_postcode,
1249 x_home_phone => cur_app_address_rec.home_phone,
1250 x_home_fax => cur_app_address_rec.home_fax,
1251 x_home_email => cur_app_address_rec.home_email,
1252 x_sent_to_oss_flag => cur_app_address_rec.sent_to_oss_flag,
1253 x_mobile => cur_app_address_rec.mobile,
1254 x_country_code => cur_app_address_rec.country_code,
1255 x_home_country_code => cur_app_address_rec.home_country_code,
1256 x_ad_batch_id => l_ad_batch_id,
1257 x_ad_interface_id => l_ad_interface_id,
1258 x_mode => 'R' );
1259
1260 END IF;
1261
1262 END IF; -- End of OSS_PERSON_ID Check
1263
1264 END LOOP;
1265 CLOSE cur_app_address_dtls;
1266
1267 /***** End of Exporting Applicant Address Details to OSS *******/
1268
1269
1270 /***** Displaying the Manual Updations required to Applicant Names Information *******/
1271
1272 -- Log the message 'Following changes in Applicant information need to be updated manually in Student System'
1273 fnd_file.put_line( fnd_file.LOG ,' ');
1274 fnd_message.set_name('IGS','IGS_UC_EXP_UPD_APP_INF');
1275 fnd_file.put_line(fnd_file.log, fnd_message.get||' ('||to_char(SYSDATE,'DD-MON-YYYY HH:MI:SS')||')');
1276 fnd_file.put_line( fnd_file.LOG ,' ');
1277
1278 --jchakrab added for 3691220
1279 IF p_app_no IS NULL THEN
1280 OPEN cur_app_name_dtls FOR
1281 SELECT APP_NO,TITLE, FORE_NAMES, SURNAME, BIRTH_DATE, SEX
1282 FROM IGS_UC_APP_NAMES
1283 WHERE SENT_TO_OSS_FLAG = 'N'
1284 ORDER BY APP_NO;
1285
1286 ELSE
1287 OPEN cur_app_name_dtls FOR
1288 SELECT APP_NO,TITLE, FORE_NAMES, SURNAME, BIRTH_DATE, SEX
1289 FROM IGS_UC_APP_NAMES
1290 WHERE APP_NO = p_app_no AND
1291 SENT_TO_OSS_FLAG = 'N'
1292 ORDER BY APP_NO;
1293 END IF;
1294
1295 -- Loop through all the records in IGS_UC_APP_NAMES table satisfying the criteria,
1296 -- SENT_TO_OSS_FLAG is N and passed parameter,P_APP_NO
1297 LOOP
1298 FETCH cur_app_name_dtls INTO cur_app_name_dtls_rec;
1299 EXIT WHEN cur_app_name_dtls%NOTFOUND;
1300
1301 -- Get the OSS Person ID of the UCAS Applicant
1302 cur_uc_app_dtls_rec := NULL;
1303 OPEN cur_uc_app_dtls(cur_app_name_dtls_rec.app_no);
1304 FETCH cur_uc_app_dtls INTO cur_uc_app_dtls_rec;
1305 CLOSE cur_uc_app_dtls;
1306
1307 -- Check whether the OSS Person ID value is populated for this Applicant
1308 -- If OSS Person ID is null then log the message and stop processing the current applicant
1309 IF cur_uc_app_dtls_rec.oss_person_id IS NULL THEN
1310
1311 -- Log the message 'OSS Person ID is not populated for the Application Number'
1312 fnd_message.set_name('IGS','IGS_UC_APP_PER_ID_NOT_EXITS');
1313 fnd_message.set_token('APP_NO',cur_uc_app_dtls_rec.app_no);
1314 fnd_file.put_line(fnd_file.log, fnd_message.get);
1315
1316 ELSE
1317
1318 -- Get the Applicant Person Details in OSS
1319 OPEN cur_uc_person_dtls(cur_uc_app_dtls_rec.oss_person_id);
1320 FETCH cur_uc_person_dtls INTO cur_uc_person_dtls_rec;
1321 CLOSE cur_uc_person_dtls;
1322
1323 -- Log the message 'Processing the Applicant with Person Number: XXX and Applicantion Number: XXX'
1324 fnd_message.set_name('IGS','IGS_UC_EXP_APP_DET_PROC');
1325 fnd_message.set_token('PER_NO',cur_uc_person_dtls_rec.person_number);
1326 fnd_message.set_token('APP_NO',cur_uc_app_dtls_rec.app_no);
1327 fnd_file.put_line(fnd_file.log, fnd_message.get);
1328
1329 -- get the OSS mapping value for Sex
1330 -- If sex is null or mapping not found, log the message and continue with the next applicant
1331 l_oss_val := NULL;
1332 OPEN cur_hesa_map('UC_OSS_HE_GEN_ASSOC', cur_app_name_dtls_rec.sex);
1333 FETCH cur_hesa_map INTO l_oss_val;
1334
1335 IF cur_hesa_map%NOTFOUND AND cur_app_name_dtls_rec.sex IS NOT NULL THEN
1336
1337 fnd_message.set_name('IGS','IGS_UC_INV_MAPPING_VAL');
1338 fnd_message.set_token('CODE', cur_app_name_dtls_rec.sex);
1339 fnd_message.set_token('TYPE','SEX');
1340 fnd_file.put_line(fnd_file.log, fnd_message.get);
1341
1342 ELSE
1343 -- If OSS person details are not equal to UCAS person details then report the changes required
1344 IF( NVL(cur_app_name_dtls_rec.title,'*') <> NVL(cur_uc_person_dtls_rec.pre_name_adjunct,'*') OR
1345 NVL(cur_app_name_dtls_rec.fore_names,'*') <> NVL(cur_uc_person_dtls_rec.given_names,'*') OR
1346 NVL(cur_app_name_dtls_rec.surname,'*') <> NVL(cur_uc_person_dtls_rec.surname,'*') OR
1347 NVL(TRUNC(cur_app_name_dtls_rec.birth_date),SYSDATE) <> NVL(TRUNC(cur_uc_person_dtls_rec.birth_date),SYSDATE) OR
1348 NVL(l_oss_val,'*') <> NVL(cur_uc_person_dtls_rec.sex,'*') ) THEN
1349
1350 -- Log the message displaying the APP_NAME Interface Table Details
1351 fnd_message.set_name('IGS','IGS_UC_EXP_UPD_APP_DET');
1352 fnd_message.set_token('TITLE',cur_app_name_dtls_rec.title);
1353 fnd_message.set_token('FORE_NAMES',cur_app_name_dtls_rec.fore_names);
1354 fnd_message.set_token('SURNAME',cur_app_name_dtls_rec.surname);
1355 fnd_message.set_token('BIRTH_DATE',cur_app_name_dtls_rec.birth_date);
1356 fnd_message.set_token('SEX',l_oss_val);
1357 fnd_file.put_line(fnd_file.log, fnd_message.get);
1358
1359 END IF;
1360
1361 /* Update the IGS_UC_APP_NAMES Table with sent_to_oss_flag to 'Y' */
1362 cur_app_name_rec := NULL;
1363 OPEN cur_upd_app_name (cur_app_name_dtls_rec.app_no);
1364 FETCH cur_upd_app_name INTO cur_app_name_rec;
1365 CLOSE cur_upd_app_name;
1366
1367 igs_uc_app_names_pkg.update_row(
1368 x_rowid => cur_app_name_rec.rowid
1369 ,x_app_no => cur_app_name_rec.app_no
1370 ,x_check_digit => cur_app_name_rec.check_digit
1371 ,x_name_change_date => cur_app_name_rec.name_change_date
1372 ,x_title => cur_app_name_rec.title
1373 ,x_fore_names => cur_app_name_rec.fore_names
1374 ,x_surname => cur_app_name_rec.surname
1375 ,x_birth_date => cur_app_name_rec.birth_date
1376 ,x_sex => cur_app_name_rec.sex
1377 ,x_sent_to_oss_flag => 'Y'
1378 ,x_mode => 'R' );
1379
1380 END IF;
1381 CLOSE cur_hesa_map;
1382
1383 END IF;
1384
1385 END LOOP;
1386 CLOSE cur_app_name_dtls;
1387
1388 /***** End of the Manual Updations required to Applicant Information *******/
1389
1390
1391 /****************** Call the Admission Import Process ****************/
1392
1393 -- Initialize the status variables
1394 l_adm_imp_status := TRUE;
1395 l_adm_error_encountered := FALSE;
1396
1397 adm_import_process (l_ad_batch_id, l_src_type_id, l_adm_imp_status );
1398
1399
1400 /******* Process the Interface Records and update the UCAS Interface Tables ******/
1401
1402 IF l_adm_imp_status = TRUE THEN
1403
1404 -- Processing the records in the IGS_UC_APPLICANTS Table
1405 FOR cur_proc_applicant_rec IN cur_proc_applicants(l_ad_batch_id) LOOP
1406
1407 -- Check whether the record exist in the IGS_AD_INTERFACE Table for the
1408 -- Admission Batch ID and Interface ID
1409 OPEN cur_ad_interface_exist( cur_proc_applicant_rec.ad_batch_id, cur_proc_applicant_rec.ad_interface_id);
1410 FETCH cur_ad_interface_exist INTO l_dummy;
1411
1412 IF cur_ad_interface_exist%FOUND THEN
1413 l_sent_to_oss_flag := 'I';
1414 l_adm_error_encountered := TRUE;
1415 ELSE
1416 l_sent_to_oss_flag := 'Y';
1417 END IF;
1418 CLOSE cur_ad_interface_exist;
1419
1420 -- update the IGS_UC_APPLICANTS Table
1421 cur_ucas_app_rec := NULL;
1422 OPEN cur_upd_ucas_app (cur_proc_applicant_rec.app_no);
1423 FETCH cur_upd_ucas_app INTO cur_ucas_app_rec;
1424 CLOSE cur_upd_ucas_app;
1425
1426 -- Update the UCAS Applicants Table with Batch ID and Interface ID
1427 igs_uc_applicants_pkg.update_row (
1428 x_rowid => cur_ucas_app_rec.rowid
1429 ,x_app_id => cur_ucas_app_rec.app_id
1430 ,x_app_no => cur_ucas_app_rec.app_no
1431 ,x_check_digit => cur_ucas_app_rec.check_digit
1432 ,x_personal_id => cur_ucas_app_rec.personal_id
1433 ,x_enquiry_no => cur_ucas_app_rec.enquiry_no
1434 ,x_oss_person_id => cur_ucas_app_rec.oss_person_id
1435 ,x_application_source => cur_ucas_app_rec.application_source
1436 ,x_name_change_date => cur_ucas_app_rec.name_change_date
1437 ,x_student_support => cur_ucas_app_rec.student_support
1438 ,x_address_area => cur_ucas_app_rec.address_area
1439 ,x_application_date => cur_ucas_app_rec.application_date
1440 ,x_application_sent_date => cur_ucas_app_rec.application_sent_date
1441 ,x_application_sent_run => cur_ucas_app_rec.application_sent_run
1442 ,x_lea_code => cur_ucas_app_rec.lea_code
1443 ,x_fee_payer_code => cur_ucas_app_rec.fee_payer_code
1444 ,x_fee_text => cur_ucas_app_rec.fee_text
1445 ,x_domicile_apr => cur_ucas_app_rec.domicile_apr
1446 ,x_code_changed_date => cur_ucas_app_rec.code_changed_date
1447 ,x_school => cur_ucas_app_rec.school
1448 ,x_withdrawn => cur_ucas_app_rec.withdrawn
1449 ,x_withdrawn_date => cur_ucas_app_rec.withdrawn_date
1450 ,x_rel_to_clear_reason => cur_ucas_app_rec.rel_to_clear_reason
1451 ,x_route_b => cur_ucas_app_rec.route_b
1452 ,x_exam_change_date => cur_ucas_app_rec.exam_change_date
1453 ,x_a_levels => cur_ucas_app_rec.a_levels
1454 ,x_as_levels => cur_ucas_app_rec.as_levels
1455 ,x_highers => cur_ucas_app_rec.highers
1456 ,x_csys => cur_ucas_app_rec.csys
1457 ,x_winter => cur_ucas_app_rec.winter
1458 ,x_previous => cur_ucas_app_rec.previous
1459 ,x_gnvq => cur_ucas_app_rec.gnvq
1460 ,x_btec => cur_ucas_app_rec.btec
1461 ,x_ilc => cur_ucas_app_rec.ilc
1462 ,x_ailc => cur_ucas_app_rec.ailc
1463 ,x_ib => cur_ucas_app_rec.ib
1464 ,x_manual => cur_ucas_app_rec.manual
1465 ,x_reg_num => cur_ucas_app_rec.reg_num
1466 ,x_oeq => cur_ucas_app_rec.oeq
1467 ,x_eas => cur_ucas_app_rec.eas
1468 ,x_roa => cur_ucas_app_rec.roa
1469 ,x_status => cur_ucas_app_rec.status
1470 ,x_firm_now => cur_ucas_app_rec.firm_now
1471 ,x_firm_reply => cur_ucas_app_rec.firm_reply
1472 ,x_insurance_reply => cur_ucas_app_rec.insurance_reply
1473 ,x_conf_hist_firm_reply => cur_ucas_app_rec.conf_hist_firm_reply
1474 ,x_conf_hist_ins_reply => cur_ucas_app_rec.conf_hist_ins_reply
1475 ,x_residential_category => cur_ucas_app_rec.residential_category
1476 ,x_personal_statement => cur_ucas_app_rec.personal_statement
1477 ,x_match_prev => cur_ucas_app_rec.match_prev
1478 ,x_match_prev_date => cur_ucas_app_rec.match_prev_date
1479 ,x_match_winter => cur_ucas_app_rec.match_winter
1480 ,x_match_summer => cur_ucas_app_rec.match_summer
1481 ,x_gnvq_date => cur_ucas_app_rec.gnvq_date
1482 ,x_ib_date => cur_ucas_app_rec.ib_date
1483 ,x_ilc_date => cur_ucas_app_rec.ilc_date
1484 ,x_ailc_date => cur_ucas_app_rec.ailc_date
1485 ,x_gcseqa_date => cur_ucas_app_rec.gcseqa_date
1486 ,x_uk_entry_date => cur_ucas_app_rec.uk_entry_date
1487 ,x_prev_surname => cur_ucas_app_rec.prev_surname
1488 ,x_criminal_convictions => cur_ucas_app_rec.criminal_convictions
1489 ,x_sent_to_hesa => cur_ucas_app_rec.sent_to_hesa
1490 ,x_sent_to_oss => l_sent_to_oss_flag -- updated column
1491 ,x_batch_identifier => cur_ucas_app_rec.batch_identifier
1492 ,x_mode => 'R'
1493 ,x_gce => cur_ucas_app_rec.gce
1494 ,x_vce => cur_ucas_app_rec.vce
1495 ,x_sqa => cur_ucas_app_rec.sqa
1496 ,x_previousas => cur_ucas_app_rec.previousas
1497 ,x_keyskills => cur_ucas_app_rec.keyskills
1498 ,x_vocational => cur_ucas_app_rec.vocational
1499 ,x_scn => cur_ucas_app_rec.scn
1500 ,x_prevoeq => cur_ucas_app_rec.prevoeq
1501 ,x_choices_transparent_ind => cur_ucas_app_rec.choices_transparent_ind
1502 ,x_extra_status => cur_ucas_app_rec.extra_status
1503 ,x_extra_passport_no => cur_ucas_app_rec.extra_passport_no
1504 ,x_request_app_dets_ind => cur_ucas_app_rec.request_app_dets_ind
1505 ,x_request_copy_app_frm_ind => cur_ucas_app_rec.request_copy_app_frm_ind
1506 ,x_cef_no => cur_ucas_app_rec.cef_no
1507 ,x_system_code => cur_ucas_app_rec.system_code
1508 ,x_gcse_eng => cur_ucas_app_rec.gcse_eng
1509 ,x_gcse_math => cur_ucas_app_rec.gcse_math
1510 ,x_degree_subject => cur_ucas_app_rec.degree_subject
1511 ,x_degree_status => cur_ucas_app_rec.degree_status
1512 ,x_degree_class => cur_ucas_app_rec.degree_class
1513 ,x_gcse_sci => cur_ucas_app_rec.gcse_sci
1514 ,x_welshspeaker => cur_ucas_app_rec.welshspeaker
1515 ,x_ni_number => cur_ucas_app_rec.ni_number
1516 ,x_earliest_start => cur_ucas_app_rec.earliest_start
1517 ,x_near_inst => cur_ucas_app_rec.near_inst
1518 ,x_pref_reg => cur_ucas_app_rec.pref_reg
1519 ,x_qual_eng => cur_ucas_app_rec.qual_eng
1520 ,x_qual_math => cur_ucas_app_rec.qual_math
1521 ,x_qual_sci => cur_ucas_app_rec.qual_sci
1522 ,x_main_qual => cur_ucas_app_rec.main_qual
1523 ,x_qual_5 => cur_ucas_app_rec.qual_5
1524 ,x_future_serv => cur_ucas_app_rec.future_serv
1525 ,x_future_set => cur_ucas_app_rec.future_set
1526 ,x_present_serv => cur_ucas_app_rec.present_serv
1527 ,x_present_set => cur_ucas_app_rec.present_set
1528 ,x_curr_employment => cur_ucas_app_rec.curr_employment
1529 ,x_edu_qualification => cur_ucas_app_rec.edu_qualification
1530 ,x_ad_batch_id => cur_ucas_app_rec.ad_batch_id
1531 ,x_ad_interface_id => cur_ucas_app_rec.ad_interface_id
1532 ,x_nationality => cur_ucas_app_rec.nationality
1533 ,x_dual_nationality => cur_ucas_app_rec.dual_nationality
1534 ,x_special_needs => cur_ucas_app_rec.special_needs
1535 ,x_country_birth => cur_ucas_app_rec.country_birth );
1536
1537 END LOOP;
1538
1539 -- Processing the records in the IGS_UC_APP_ADDRESES Table
1540 FOR cur_proc_app_address_rec IN cur_proc_app_address(l_ad_batch_id) LOOP
1541
1542 -- Check whether the record exist in the IGS_AD_INTERFACE Table for the
1543 -- Admission Batch ID and Interface ID
1544 OPEN cur_ad_interface_exist( cur_proc_app_address_rec.ad_batch_id, cur_proc_app_address_rec.ad_interface_id);
1545 FETCH cur_ad_interface_exist INTO l_dummy;
1546
1547 IF cur_ad_interface_exist%FOUND THEN
1548 l_sent_to_oss_flag := 'I';
1549 l_adm_error_encountered := TRUE;
1550 ELSE
1551 l_sent_to_oss_flag := 'Y';
1552 END IF;
1553 CLOSE cur_ad_interface_exist;
1554
1555 -- Update the IGS_UC_APP_ADDRESES Table
1556 cur_app_address_rec := NULL;
1557 OPEN cur_upd_app_address (cur_proc_app_address_rec.app_no);
1558 FETCH cur_upd_app_address INTO cur_app_address_rec;
1559 CLOSE cur_upd_app_address;
1560 igs_uc_app_addreses_pkg.update_row (
1561 x_rowid => cur_app_address_rec.rowid,
1562 x_app_no => cur_app_address_rec.app_no,
1563 x_address_area => cur_app_address_rec.address_area,
1564 x_address1 => cur_app_address_rec.address1,
1565 x_address2 => cur_app_address_rec.address2,
1566 x_address3 => cur_app_address_rec.address3,
1567 x_address4 => cur_app_address_rec.address4,
1568 x_post_code => cur_app_address_rec.post_code,
1569 x_mail_sort => cur_app_address_rec.mail_sort,
1570 x_telephone => cur_app_address_rec.telephone,
1571 x_fax => cur_app_address_rec.fax,
1572 x_email => cur_app_address_rec.email,
1573 x_home_address1 => cur_app_address_rec.home_address1,
1574 x_home_address2 => cur_app_address_rec.home_address2,
1575 x_home_address3 => cur_app_address_rec.home_address3,
1576 x_home_address4 => cur_app_address_rec.home_address4,
1577 x_home_postcode => cur_app_address_rec.home_postcode,
1578 x_home_phone => cur_app_address_rec.home_phone,
1579 x_home_fax => cur_app_address_rec.home_fax,
1580 x_home_email => cur_app_address_rec.home_email,
1581 x_sent_to_oss_flag => l_sent_to_oss_flag,
1582 x_mobile => cur_app_address_rec.mobile,
1583 x_country_code => cur_app_address_rec.country_code,
1584 x_home_country_code => cur_app_address_rec.home_country_code,
1585 x_ad_batch_id => cur_app_address_rec.ad_batch_id,
1586 x_ad_interface_id => cur_app_address_rec.ad_interface_id,
1587 x_mode => 'R' );
1588
1589 END LOOP;
1590
1591 END IF;
1592
1593 -- anwest Bug# 3642740
1594 -- Processing the records in the IGS_UC_APPLICANTS Table with SENT_TO_OSS set to 'I'
1595 FOR cur_proc_applicant_i_rec IN cur_proc_applicants_i LOOP
1596
1597 -- Check whether the record exist in the IGS_AD_INTERFACE Table for the
1598 -- Admission Batch ID and Interface ID
1599 OPEN cur_ad_interface_exist(cur_proc_applicant_i_rec.ad_batch_id, cur_proc_applicant_i_rec.ad_interface_id);
1600 FETCH cur_ad_interface_exist INTO l_dummy;
1601
1602 IF cur_ad_interface_exist%NOTFOUND THEN
1603
1604 -- does not exist so update the UCAS Applicants Table with 'Y' for SENT_TO_OSS
1605 cur_ucas_app_rec := NULL;
1606 OPEN cur_upd_ucas_app (cur_proc_applicant_i_rec.app_no);
1607 FETCH cur_upd_ucas_app INTO cur_ucas_app_rec;
1608 CLOSE cur_upd_ucas_app;
1609 igs_uc_applicants_pkg.update_row (
1610 x_rowid => cur_ucas_app_rec.rowid
1611 ,x_app_id => cur_ucas_app_rec.app_id
1612 ,x_app_no => cur_ucas_app_rec.app_no
1613 ,x_check_digit => cur_ucas_app_rec.check_digit
1614 ,x_personal_id => cur_ucas_app_rec.personal_id
1615 ,x_enquiry_no => cur_ucas_app_rec.enquiry_no
1616 ,x_oss_person_id => cur_ucas_app_rec.oss_person_id
1617 ,x_application_source => cur_ucas_app_rec.application_source
1618 ,x_name_change_date => cur_ucas_app_rec.name_change_date
1619 ,x_student_support => cur_ucas_app_rec.student_support
1620 ,x_address_area => cur_ucas_app_rec.address_area
1621 ,x_application_date => cur_ucas_app_rec.application_date
1622 ,x_application_sent_date => cur_ucas_app_rec.application_sent_date
1623 ,x_application_sent_run => cur_ucas_app_rec.application_sent_run
1624 ,x_lea_code => cur_ucas_app_rec.lea_code
1625 ,x_fee_payer_code => cur_ucas_app_rec.fee_payer_code
1626 ,x_fee_text => cur_ucas_app_rec.fee_text
1627 ,x_domicile_apr => cur_ucas_app_rec.domicile_apr
1628 ,x_code_changed_date => cur_ucas_app_rec.code_changed_date
1629 ,x_school => cur_ucas_app_rec.school
1630 ,x_withdrawn => cur_ucas_app_rec.withdrawn
1631 ,x_withdrawn_date => cur_ucas_app_rec.withdrawn_date
1632 ,x_rel_to_clear_reason => cur_ucas_app_rec.rel_to_clear_reason
1633 ,x_route_b => cur_ucas_app_rec.route_b
1634 ,x_exam_change_date => cur_ucas_app_rec.exam_change_date
1635 ,x_a_levels => cur_ucas_app_rec.a_levels
1636 ,x_as_levels => cur_ucas_app_rec.as_levels
1637 ,x_highers => cur_ucas_app_rec.highers
1638 ,x_csys => cur_ucas_app_rec.csys
1639 ,x_winter => cur_ucas_app_rec.winter
1640 ,x_previous => cur_ucas_app_rec.previous
1641 ,x_gnvq => cur_ucas_app_rec.gnvq
1642 ,x_btec => cur_ucas_app_rec.btec
1643 ,x_ilc => cur_ucas_app_rec.ilc
1644 ,x_ailc => cur_ucas_app_rec.ailc
1645 ,x_ib => cur_ucas_app_rec.ib
1646 ,x_manual => cur_ucas_app_rec.manual
1647 ,x_reg_num => cur_ucas_app_rec.reg_num
1648 ,x_oeq => cur_ucas_app_rec.oeq
1649 ,x_eas => cur_ucas_app_rec.eas
1650 ,x_roa => cur_ucas_app_rec.roa
1651 ,x_status => cur_ucas_app_rec.status
1652 ,x_firm_now => cur_ucas_app_rec.firm_now
1653 ,x_firm_reply => cur_ucas_app_rec.firm_reply
1654 ,x_insurance_reply => cur_ucas_app_rec.insurance_reply
1655 ,x_conf_hist_firm_reply => cur_ucas_app_rec.conf_hist_firm_reply
1656 ,x_conf_hist_ins_reply => cur_ucas_app_rec.conf_hist_ins_reply
1657 ,x_residential_category => cur_ucas_app_rec.residential_category
1658 ,x_personal_statement => cur_ucas_app_rec.personal_statement
1659 ,x_match_prev => cur_ucas_app_rec.match_prev
1660 ,x_match_prev_date => cur_ucas_app_rec.match_prev_date
1661 ,x_match_winter => cur_ucas_app_rec.match_winter
1662 ,x_match_summer => cur_ucas_app_rec.match_summer
1663 ,x_gnvq_date => cur_ucas_app_rec.gnvq_date
1664 ,x_ib_date => cur_ucas_app_rec.ib_date
1665 ,x_ilc_date => cur_ucas_app_rec.ilc_date
1666 ,x_ailc_date => cur_ucas_app_rec.ailc_date
1667 ,x_gcseqa_date => cur_ucas_app_rec.gcseqa_date
1668 ,x_uk_entry_date => cur_ucas_app_rec.uk_entry_date
1669 ,x_prev_surname => cur_ucas_app_rec.prev_surname
1670 ,x_criminal_convictions => cur_ucas_app_rec.criminal_convictions
1671 ,x_sent_to_hesa => cur_ucas_app_rec.sent_to_hesa
1672 ,x_sent_to_oss => 'Y'
1673 ,x_batch_identifier => cur_ucas_app_rec.batch_identifier
1674 ,x_mode => 'R'
1675 ,x_gce => cur_ucas_app_rec.gce
1676 ,x_vce => cur_ucas_app_rec.vce
1677 ,x_sqa => cur_ucas_app_rec.sqa
1678 ,x_previousas => cur_ucas_app_rec.previousas
1679 ,x_keyskills => cur_ucas_app_rec.keyskills
1680 ,x_vocational => cur_ucas_app_rec.vocational
1681 ,x_scn => cur_ucas_app_rec.scn
1682 ,x_prevoeq => cur_ucas_app_rec.prevoeq
1683 ,x_choices_transparent_ind => cur_ucas_app_rec.choices_transparent_ind
1684 ,x_extra_status => cur_ucas_app_rec.extra_status
1685 ,x_extra_passport_no => cur_ucas_app_rec.extra_passport_no
1686 ,x_request_app_dets_ind => cur_ucas_app_rec.request_app_dets_ind
1687 ,x_request_copy_app_frm_ind => cur_ucas_app_rec.request_copy_app_frm_ind
1688 ,x_cef_no => cur_ucas_app_rec.cef_no
1689 ,x_system_code => cur_ucas_app_rec.system_code
1690 ,x_gcse_eng => cur_ucas_app_rec.gcse_eng
1691 ,x_gcse_math => cur_ucas_app_rec.gcse_math
1692 ,x_degree_subject => cur_ucas_app_rec.degree_subject
1693 ,x_degree_status => cur_ucas_app_rec.degree_status
1694 ,x_degree_class => cur_ucas_app_rec.degree_class
1695 ,x_gcse_sci => cur_ucas_app_rec.gcse_sci
1696 ,x_welshspeaker => cur_ucas_app_rec.welshspeaker
1697 ,x_ni_number => cur_ucas_app_rec.ni_number
1698 ,x_earliest_start => cur_ucas_app_rec.earliest_start
1699 ,x_near_inst => cur_ucas_app_rec.near_inst
1700 ,x_pref_reg => cur_ucas_app_rec.pref_reg
1701 ,x_qual_eng => cur_ucas_app_rec.qual_eng
1702 ,x_qual_math => cur_ucas_app_rec.qual_math
1703 ,x_qual_sci => cur_ucas_app_rec.qual_sci
1704 ,x_main_qual => cur_ucas_app_rec.main_qual
1705 ,x_qual_5 => cur_ucas_app_rec.qual_5
1706 ,x_future_serv => cur_ucas_app_rec.future_serv
1707 ,x_future_set => cur_ucas_app_rec.future_set
1708 ,x_present_serv => cur_ucas_app_rec.present_serv
1709 ,x_present_set => cur_ucas_app_rec.present_set
1710 ,x_curr_employment => cur_ucas_app_rec.curr_employment
1711 ,x_edu_qualification => cur_ucas_app_rec.edu_qualification
1712 ,x_ad_batch_id => cur_ucas_app_rec.ad_batch_id
1713 ,x_ad_interface_id => cur_ucas_app_rec.ad_interface_id
1714 ,x_nationality => cur_ucas_app_rec.nationality
1715 ,x_dual_nationality => cur_ucas_app_rec.dual_nationality
1716 ,x_special_needs => cur_ucas_app_rec.special_needs
1717 ,x_country_birth => cur_ucas_app_rec.country_birth );
1718
1719 END IF;
1720
1721 CLOSE cur_ad_interface_exist;
1722
1723 END LOOP;
1724
1725
1726 -- anwest Bug# 3642740
1727 -- Processing the records in the IGS_UC_APP_ADDRESES Table with SENT_TO_OSS_FLAG set to 'I'
1728 FOR cur_proc_app_address_i_rec IN cur_proc_app_address_i LOOP
1729
1730 -- Check whether the record exist in the IGS_AD_INTERFACE Table for the
1731 -- Admission Batch ID and Interface ID
1732 OPEN cur_ad_interface_exist(cur_proc_app_address_i_rec.ad_batch_id, cur_proc_app_address_i_rec.ad_interface_id);
1733 FETCH cur_ad_interface_exist INTO l_dummy;
1734
1735 IF cur_ad_interface_exist%NOTFOUND THEN
1736
1737 -- does not exist so update the UCAS Applicant Addresses Table with 'Y' for SENT_TO_OSS_FLAG
1738 cur_app_address_rec := NULL;
1739 OPEN cur_upd_app_address (cur_proc_app_address_i_rec.app_no);
1740 FETCH cur_upd_app_address INTO cur_app_address_rec;
1741 CLOSE cur_upd_app_address;
1742 igs_uc_app_addreses_pkg.update_row (
1743 x_rowid => cur_app_address_rec.rowid,
1744 x_app_no => cur_app_address_rec.app_no,
1745 x_address_area => cur_app_address_rec.address_area,
1746 x_address1 => cur_app_address_rec.address1,
1747 x_address2 => cur_app_address_rec.address2,
1748 x_address3 => cur_app_address_rec.address3,
1749 x_address4 => cur_app_address_rec.address4,
1750 x_post_code => cur_app_address_rec.post_code,
1751 x_mail_sort => cur_app_address_rec.mail_sort,
1752 x_telephone => cur_app_address_rec.telephone,
1753 x_fax => cur_app_address_rec.fax,
1754 x_email => cur_app_address_rec.email,
1755 x_home_address1 => cur_app_address_rec.home_address1,
1756 x_home_address2 => cur_app_address_rec.home_address2,
1757 x_home_address3 => cur_app_address_rec.home_address3,
1758 x_home_address4 => cur_app_address_rec.home_address4,
1759 x_home_postcode => cur_app_address_rec.home_postcode,
1760 x_home_phone => cur_app_address_rec.home_phone,
1761 x_home_fax => cur_app_address_rec.home_fax,
1762 x_home_email => cur_app_address_rec.home_email,
1763 x_sent_to_oss_flag => 'Y',
1764 x_mobile => cur_app_address_rec.mobile,
1765 x_country_code => cur_app_address_rec.country_code,
1766 x_home_country_code => cur_app_address_rec.home_country_code,
1767 x_ad_batch_id => cur_app_address_rec.ad_batch_id,
1768 x_ad_interface_id => cur_app_address_rec.ad_interface_id,
1769 x_mode => 'R' );
1770 END IF;
1771
1772 CLOSE cur_ad_interface_exist;
1773
1774 END LOOP;
1775
1776
1777 /******* End of Processing the Interface Records *******/
1778
1779
1780 /******** Launching Export UCAS Applicant to OSS Error Report job ********/
1781
1782 IF l_adm_error_encountered = TRUE THEN
1783
1784 -- Submit the Error report to show the errors generated while exporting the applicant details
1785 l_rep_request_id := NULL ;
1786 l_rep_request_id := fnd_request.submit_request(
1787 'IGS','IGSUCS37','Export UCAS Applicant Details to OSS Error Report',
1788 NULL, FALSE, NULL , NULL, NULL, NULL, CHR(0),
1789 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1790 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1791 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1792 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1793 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1794 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1795 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1796 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1797 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1798 NULL, NULL, NULL, NULL, NULL );
1799
1800 IF l_rep_request_id > 0 THEN
1801
1802 -- If error report successfully submitted then log message
1803 fnd_file.put_line( fnd_file.LOG ,' ');
1804 fnd_message.set_name('IGS','IGS_UC_EXP_APP_REP_SUBM');
1805 fnd_message.set_token('REQ_ID',TO_CHAR(l_rep_request_id));
1806 fnd_file.put_line( fnd_file.LOG ,fnd_message.get||' ('||to_char(SYSDATE,'DD-MON-YYYY HH:MI:SS')||')');
1807
1808 ELSE
1809
1810 -- If error report failed to be launched then log message
1811 fnd_file.put_line( fnd_file.LOG ,' ');
1812 fnd_message.set_name('IGS','IGS_UC_EXP_APP_SUBM_ERR');
1813 fnd_file.put_line( fnd_file.LOG ,fnd_message.get||' ('||to_char(SYSDATE,'DD-MON-YYYY HH:MI:SS')||')');
1814
1815 END IF;
1816
1817 END IF;
1818
1819 /******** End of Launching Export UCAS Applicant to OSS Error Report ********/
1820
1821 EXCEPTION
1822
1823 WHEN OTHERS THEN
1824
1825 ROLLBACK;
1826 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1827 fnd_message.set_token('NAME','IGS_UC_EXP_APPLICANT_DTLS.EXPORT_PROCESS'||' - '||SQLERRM);
1828 errbuf := fnd_message.get;
1829 retcode := 2 ;
1830 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
1831
1832 END export_process;
1833
1834
1835 PROCEDURE pop_api_int ( p_ni_number IN VARCHAR2,
1836 p_ninumber_alt_type IN VARCHAR2,
1837 p_scn IN VARCHAR2,
1838 p_person_id IN NUMBER,
1839 p_interface_id IN NUMBER,
1840 p_app_valid_status IN OUT NOCOPY BOOLEAN) IS
1841 /******************************************************************
1842 Created By : AYEDUBAT
1843 Date Created By : 16-JUN-2003
1844 Purpose : Populates the Admission Alternate ID Interface Tables and set the
1845 parameter,p_app_valid_status to FALSE if an exception is raised.
1846 Known limitations,enhancements,remarks:
1847
1848 CHANGE HISTORY:
1849 WHO WHEN WHAT
1850
1851 ***************************************************************** */
1852
1853 CURSOR cur_alt_pers_dtls ( cp_person_id IGS_PE_ALT_PERS_ID.pe_person_id%TYPE,
1854 cp_pers_id_type IGS_PE_ALT_PERS_ID.person_id_type%TYPE) IS
1855 SELECT api_person_id, start_dt, end_dt
1856 FROM IGS_PE_ALT_PERS_ID
1857 WHERE pe_person_id = cp_person_id
1858 AND person_id_type = cp_pers_id_type
1859 ORDER BY START_DT DESC;
1860 cur_alt_pers_dtls_rec cur_alt_pers_dtls%ROWTYPE;
1861
1862 CURSOR cur_pers_type_unique ( cp_person_id_type IGS_PE_PERSON_ID_TYP.person_id_type%TYPE) IS
1863 SELECT 'X'
1864 FROM IGS_PE_PERSON_ID_TYP
1865 WHERE person_id_type = cp_person_id_type
1866 AND unique_ind = 'Y'
1867 AND closed_ind = 'N';
1868
1869 CURSOR cur_pers_id_exist ( cp_api_person_id IGS_PE_ALT_PERS_ID.api_person_id%TYPE,
1870 cp_person_id_type IGS_PE_ALT_PERS_ID.person_id_type%TYPE) IS
1871 SELECT 'X'
1872 FROM IGS_PE_ALT_PERS_ID
1873 WHERE api_person_id = cp_api_person_id
1874 AND person_id_type = cp_person_id_type;
1875 l_dummy VARCHAR2(1);
1876
1877 BEGIN
1878
1879 -- Creating the Alternate ID type for NI_NNUMBER column
1880 IF p_ni_number IS NOT NULL AND p_ninumber_alt_type IS NOT NULL THEN
1881
1882 -- Check if a record already exists for NI Number Person ID Type for the current applicant
1883 OPEN cur_alt_pers_dtls ( p_person_id, p_ninumber_alt_type );
1884 FETCH cur_alt_pers_dtls INTO cur_alt_pers_dtls_rec;
1885
1886 IF cur_alt_pers_dtls%FOUND THEN
1887
1888 -- If UCAS NI Number is not equal to the existing OSS Alternate Person ID
1889 -- Then Log the message
1890 IF p_ni_number <> cur_alt_pers_dtls_rec.api_person_id THEN
1891
1892 fnd_message.set_name('IGS','IGS_UC_NI_NUM_NOT_MATCH');
1893 fnd_file.put_line(fnd_file.log, fnd_message.get);
1894
1895 ELSIF cur_alt_pers_dtls_rec.end_dt IS NOT NULL THEN
1896
1897 fnd_message.set_name('IGS','IGS_UC_NI_NUM_END_DATED');
1898 fnd_file.put_line(fnd_file.log, fnd_message.get);
1899
1900 END IF;
1901
1902 ELSE
1903
1904 -- Check whether the NI Number Alternate Person ID Type is defined as Unique
1905 OPEN cur_pers_type_unique(p_ninumber_alt_type);
1906 FETCH cur_pers_type_unique INTO l_dummy;
1907
1908 -- Check whether the NI Number is used by another person or not
1909 OPEN cur_pers_id_exist(p_ni_number, p_ninumber_alt_type);
1910 FETCH cur_pers_id_exist INTO l_dummy;
1911
1912 -- If NI Number Alternate Person ID Type is defined as Unique and NI Number is used by another person
1913 -- display log message 'Warning - UCAS NI Number is in use by another person- please review
1914 IF cur_pers_type_unique%FOUND AND cur_pers_id_exist%FOUND THEN
1915
1916 fnd_message.set_name('IGS','IGS_UC_NI_NUM_END_DATED');
1917 fnd_file.put_line(fnd_file.log, fnd_message.get);
1918
1919 ELSE
1920
1921 -- Populate the IGS_AD_API_INT table
1922 INSERT INTO igs_ad_api_int (
1923 interface_api_id
1924 ,interface_id
1925 ,person_id_type
1926 ,alternate_id
1927 ,status
1928 ,created_by
1929 ,creation_date
1930 ,last_updated_by
1931 ,last_update_date
1932 ,last_update_login )
1933 VALUES(
1934 IGS_AD_API_INT_S.NEXTVAL
1935 ,p_interface_id
1936 ,p_ninumber_alt_type
1937 ,p_ni_number
1938 ,'2'
1939 ,g_created_by
1940 ,SYSDATE
1941 ,g_last_updated_by
1942 ,SYSDATE
1943 ,g_last_update_login );
1944
1945 END IF;
1946 CLOSE cur_pers_type_unique;
1947 CLOSE cur_pers_id_exist;
1948
1949 END IF;
1950 CLOSE cur_alt_pers_dtls;
1951
1952 END IF;
1953
1954 -- Creating the Alternate ID type for SCN column
1955 IF p_scn IS NOT NULL THEN
1956
1957 -- Check if a record already exists for UCASREGNO Person ID Type for the current applicant
1958 cur_alt_pers_dtls_rec := NULL;
1959 OPEN cur_alt_pers_dtls ( p_person_id, 'UCASREGNO' );
1960 FETCH cur_alt_pers_dtls INTO cur_alt_pers_dtls_rec;
1961
1962 IF cur_alt_pers_dtls%FOUND THEN
1963
1964 -- If UCAS SCN is not equal to the existing OSS Alternate Person ID
1965 -- Then Log the message in the Log file
1966 IF p_scn <> cur_alt_pers_dtls_rec.api_person_id THEN
1967
1968 fnd_message.set_name('IGS','IGS_UC_SCN_NOT_MATCH');
1969 fnd_file.put_line(fnd_file.log, fnd_message.get);
1970
1971 ELSIF cur_alt_pers_dtls_rec.end_dt IS NOT NULL THEN
1972
1973 fnd_message.set_name('IGS','IGS_UC_SCN_END_DATED');
1974 fnd_file.put_line(fnd_file.log, fnd_message.get);
1975
1976 END IF;
1977
1978 ELSE
1979
1980 -- Check whether the 'UCASREGNO' Alternate Person ID Type is defined as Unique
1981 OPEN cur_pers_type_unique('UCASREGNO');
1982 FETCH cur_pers_type_unique INTO l_dummy;
1983
1984 -- Check whether the SCN is used by another person or not
1985 OPEN cur_pers_id_exist(p_scn, 'UCASREGNO');
1986 FETCH cur_pers_id_exist INTO l_dummy;
1987
1988 -- If UCASREGNO Alternate Person ID Type is defined as Unique and SCN is used by another person
1989 -- display log message 'Warning - SCN is in use by another person- please review
1990 IF cur_pers_type_unique%FOUND AND cur_pers_id_exist%FOUND THEN
1991
1992 fnd_message.set_name('IGS','IGS_UC_SCN_IN_USE');
1993 fnd_file.put_line(fnd_file.log, fnd_message.get);
1994
1995 ELSE
1996
1997 INSERT INTO igs_ad_api_int (
1998 interface_api_id
1999 ,interface_id
2000 ,person_id_type
2001 ,alternate_id
2002 ,status
2003 ,created_by
2004 ,creation_date
2005 ,last_updated_by
2006 ,last_update_date
2007 ,last_update_login )
2008 VALUES (
2009 IGS_AD_API_INT_S.NEXTVAL
2010 ,p_interface_id
2011 ,'UCASREGNO'
2012 ,p_scn
2013 ,'2'
2014 ,g_created_by
2015 ,SYSDATE
2016 ,g_last_updated_by
2017 ,SYSDATE
2018 ,g_last_update_login );
2019
2020 END IF;
2021 CLOSE cur_pers_type_unique;
2022 CLOSE cur_pers_id_exist;
2023
2024 END IF;
2025 CLOSE cur_alt_pers_dtls;
2026
2027 END IF;
2028
2029 EXCEPTION
2030 WHEN OTHERS THEN
2031 p_app_valid_status := FALSE;
2032 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2033 fnd_message.set_token('NAME','IGS_UC_EXP_APPLICANT_DTLS.POP_API_INT'||' - '||SQLERRM);
2034 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
2035
2036 END pop_api_int;
2037
2038
2039 PROCEDURE pop_res_dtls_int( p_rescat IN VARCHAR2,
2040 p_application_date IN DATE,
2041 p_system_code IN VARCHAR2,
2042 p_interface_id IN NUMBER,
2043 p_cal_type IN VARCHAR2, -- anwest UCFD040 Bug# 4015492 Added new parameter
2044 p_sequence_number IN NUMBER, -- anwest UCFD040 Bug# 4015492 Added new parameter
2045 p_app_valid_status IN OUT NOCOPY BOOLEAN) AS
2046 /******************************************************************
2047 Created By : AYEDUBAT
2048 Date Created By : 16-JUN-2003
2049 Purpose : Populate the Residency Details Interface Table, IGS_PE_RES_DTLS_INT
2050 and set the parameter,p_app_valid_status to FALSE
2051 if any validation is failed or an exception is raised.
2052 Known limitations,enhancements,remarks:
2053
2054 CHANGE HISTORY:
2055 WHO WHEN WHAT
2056 dsridhar 25-SEP-2003 Bug No. 2980137. While inserting into IGS_PE_RES_DTLS_INT,
2057 the EVALUATOR field is populated with PERSON_NUMBER instead
2058 of DECISION_MAKE_ID. Added a cursor to get the PERSON_NUMBER
2059 from DECISION_MAKE_ID. Added a new variable p_retcode to this
2060 procedure to return the error code in case of an error.
2061 rgangara 10-APR-2004 Bug 3553352. Removed cursor cur_uc_defaults and associated
2062 validation as this has been moved to top of the process as
2063 mandatory check instead of doing it at AppNo level.
2064 anwest 25-NOV-2004 UCFD040 - Bug# 4015492 Added 2 new parameters to the signature
2065 and updated the INSERT statement to include these and remove
2066 START_DT
2067 ***************************************************************** */
2068
2069 -- Fetch the HESA Mapping value
2070 CURSOR cur_hesa_map (cp_assoc IGS_HE_CODE_MAP_VAL.association_code%TYPE,
2071 cp_map1 IGS_HE_CODE_MAP_VAL.map2%TYPE ) IS
2072 SELECT map2
2073 FROM IGS_HE_CODE_MAP_VAL
2074 WHERE association_code = cp_assoc
2075 AND map1 = cp_map1;
2076 l_oss_val IGS_HE_CODE_MAP_VAL.map2%TYPE;
2077
2078 l_residency_status_cd IGS_PE_RES_DTLS.residency_status_cd%TYPE;
2079
2080 -- Cursor to fetch the person number based on person id. Bug No. 2980137.
2081 CURSOR cur_person_number IS
2082 SELECT pv.person_number
2083 FROM igs_pe_person_base_v pv, igs_uc_defaults ucd
2084 WHERE pv.person_id = ucd.decision_make_id
2085 AND ucd.system_code = p_system_code;
2086
2087 l_person_number igs_pe_person_base_v.person_number%TYPE;
2088
2089 BEGIN
2090
2091 -- Importing the Residency Details for p_rescat
2092
2093 -- Find the OSS mapping value for the UCAS Residential Category value using the
2094 -- HESA Association Code,UC_OSS_RESCAT_ASSOC
2095 l_residency_status_cd := NULL ;
2096 OPEN cur_hesa_map ('UC_OSS_RESCAT_ASSOC', p_rescat) ;
2097 FETCH cur_hesa_map INTO l_residency_status_cd ;
2098 CLOSE cur_hesa_map ;
2099
2100 IF l_residency_status_cd IS NULL THEN
2101 p_app_valid_status := FALSE;
2102 fnd_message.set_name('IGS','IGS_UC_INV_MAPPING_VAL');
2103 fnd_message.set_token('CODE', p_rescat);
2104 fnd_message.set_token('TYPE', 'RESIDENTIAL CATEGORY');
2105 fnd_file.put_line(fnd_file.log, fnd_message.get);
2106 END IF ;
2107
2108
2109 IF p_app_valid_status = TRUE THEN
2110
2111 -- Bug No. 2980137. Fetching the Person Number based on decision_make_id
2112 l_person_number := NULL;
2113 OPEN cur_person_number;
2114 FETCH cur_person_number INTO l_person_number;
2115 CLOSE cur_person_number;
2116
2117 -- Populate the residency details import interface table,IGS_PE_RES_DTLS_INT
2118 INSERT INTO IGS_PE_RES_DTLS_INT (
2119 INTERFACE_RES_ID,
2120 INTERFACE_ID,
2121 RESIDENCY_STATUS_CD,
2122 RESIDENCY_CLASS_CD,
2123 EVALUATION_DATE,
2124 EVALUATOR,
2125 STATUS,
2126 CREATED_BY,
2127 CREATION_DATE,
2128 LAST_UPDATED_BY,
2129 LAST_UPDATE_DATE,
2130 LAST_UPDATE_LOGIN,
2131 CAL_TYPE, -- anwest UCFD040 Bug# 4015492 Added new parameter
2132 SEQUENCE_NUMBER) -- anwest UCFD040 Bug# 4015492 Added new parameter
2133 VALUES (
2134 igs_pe_res_dtls_int_s.NEXTVAL
2135 ,p_interface_id
2136 ,l_residency_status_cd
2137 ,fnd_profile.value('IGS_FI_RES_CLASS_ID')
2138 ,TRUNC(SYSDATE)
2139 ,l_person_number -- Bug No. 2980137. Replaced decision_make_id with l_person_number
2140 ,'2' -- Pending Status
2141 ,g_created_by
2142 ,SYSDATE
2143 ,g_last_updated_by
2144 ,SYSDATE
2145 ,g_last_update_login
2146 ,p_cal_type -- anwest UCFD040 Bug# 4015492 Added new parameter
2147 ,p_sequence_number); -- anwest UCFD040 Bug# 4015492 Added new parameter
2148
2149 END IF;
2150
2151 EXCEPTION
2152 WHEN OTHERS THEN
2153 p_app_valid_status := FALSE;
2154 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2155 fnd_message.set_token('NAME','IGS_UC_EXP_APPLICANT_DTLS.POP_RES_DTLS_INT'||' - '||SQLERRM);
2156 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
2157
2158 END pop_res_dtls_int;
2159
2160 PROCEDURE pop_acad_hist_int( p_person_id IN NUMBER,
2161 p_person_number IN VARCHAR2,
2162 p_school IN NUMBER,
2163 p_interface_id IN NUMBER,
2164 p_app_valid_status IN OUT NOCOPY BOOLEAN) AS
2165 /******************************************************************
2166 Created By : JTMATHEW
2167 Date Created By : 08-APR-2005
2168 Purpose : Populate the Admission Interface Table, IGS_AD_ACADHIS_INT_ALL for
2169 importing the Academic History data and set the parameter,p_app_valid_status
2170 to FALSE if any validation is failed or an exception is raised.
2171 Known limitations,enhancements,remarks:
2172
2173 CHANGE HISTORY:
2174 WHO WHEN WHAT
2175 jchin 24-Feb-2006 Modified for R12 perf bugs 4950293
2176 ***************************************************************** */
2177
2178 -- UCAS Association mapping
2179 CURSOR cur_ucas_oss_map (cp_assoc IGS_HE_CODE_MAP_VAL.association_code%TYPE,
2180 cp_map1 IGS_HE_CODE_MAP_VAL.map2%TYPE ) IS
2181 SELECT map2
2182 FROM IGS_HE_CODE_MAP_VAL
2183 WHERE association_code = cp_assoc
2184 AND map1 = cp_map1;
2185
2186 -- get the Academic history record for the student
2187 -- jchin - bug 4950293
2188 CURSOR cur_get_hist_details ( cp_person_id igs_ad_acad_history_v.person_id%TYPE ,
2189 cp_inst_cd igs_ad_acad_history_v.institution_code%TYPE ) IS
2190 SELECT 'X'
2191 FROM igs_ad_acad_history_v a
2192 WHERE a.person_id = cp_person_id
2193 AND a.institution_code = cp_inst_cd ;
2194
2195 l_oss_inst igs_he_code_map_val.map2%TYPE;
2196 l_acad_hist_rec cur_get_hist_details%ROWTYPE;
2197 l_mapping_failed VARCHAR2(1) ;
2198
2199 BEGIN
2200
2201 -- initialize variables
2202 l_acad_hist_rec := NULL;
2203 l_oss_inst := NULL;
2204 l_mapping_failed := 'N';
2205
2206 -- Fetch the OSS school value when given the UCAS school value
2207 OPEN cur_ucas_oss_map('UC_OSS_HE_INS_ASSOC', p_school);
2208 FETCH cur_ucas_oss_map INTO l_oss_inst;
2209 IF (cur_ucas_oss_map%NOTFOUND) THEN
2210
2211 FND_MESSAGE.Set_Name('IGS','IGS_UC_NO_INST');
2212 FND_MESSAGE.Set_Token('PERSON_ID',p_person_number);
2213 FND_MESSAGE.Set_Token('CODE',p_school );
2214 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.get );
2215 l_mapping_failed := 'Y';
2216
2217 END IF;
2218 CLOSE cur_ucas_oss_map;
2219
2220 IF l_mapping_failed = 'N' THEN
2221
2222 OPEN cur_get_hist_details(p_person_id,l_oss_inst) ;
2223 FETCH cur_get_hist_details INTO l_acad_hist_rec;
2224
2225 IF cur_get_hist_details%NOTFOUND THEN
2226
2227 -- When no Academic History record exists for the person and OSS Institution passed
2228 -- Create a new record in Academic History Interface table.
2229
2230 -- Create an Academic History interface record for this person
2231 INSERT INTO igs_ad_acadhis_int_all ( interface_acadhis_id,
2232 interface_id,
2233 institution_code,
2234 current_inst,
2235 end_date,
2236 status,
2237 transcript_required,
2238 created_by,
2239 creation_date,
2240 last_updated_by,
2241 last_update_date,
2242 last_update_login,
2243 request_id,
2244 program_application_id,
2245 program_id,
2246 program_update_date )
2247 VALUES ( igs_ad_acadhis_int_s.NEXTVAL,
2248 p_interface_id,
2249 l_oss_inst,
2250 'N',
2251 NULL,
2252 '2',
2253 'N',
2254 fnd_global.user_id,
2255 SYSDATE,
2256 fnd_global.user_id,
2257 SYSDATE,
2258 fnd_global.login_id,
2259 DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_request_id),
2260 DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.prog_appl_id),
2261 DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_program_id),
2262 DECODE(fnd_global.conc_request_id,-1,NULL,SYSDATE) );
2263
2264 END IF; -- End of record already exists check => cur_get_hist_details%NOTFOUND
2265 CLOSE cur_get_hist_details ;
2266
2267 END IF; -- Mapping failed check
2268
2269
2270 EXCEPTION
2271 WHEN OTHERS THEN
2272 p_app_valid_status := FALSE;
2273 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2274 fnd_message.set_token('NAME','IGS_UC_EXP_APPLICANT_DTLS.POP_ACAD_HIST_INT'||' - '||SQLERRM);
2275 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
2276
2277 END pop_acad_hist_int;
2278
2279 PROCEDURE pop_citizen_int ( p_nationality IN NUMBER,
2280 p_dual_nationality IN NUMBER,
2281 p_person_id IN NUMBER,
2282 p_application_date IN DATE,
2283 p_interface_id IN NUMBER,
2284 p_app_valid_status IN OUT NOCOPY BOOLEAN) AS
2285 /******************************************************************
2286 Created By : AYEDUBAT
2287 Date Created By : 16-JUN-2003
2288 Purpose : Populate the Interface Table, IGS_PE_CITIZEN_INT
2289 and set the parameter,p_app_valid_status to FALSE
2290 if any validation is failed or an exception is raised.
2291 Known limitations,enhancements,remarks:
2292
2293 CHANGE HISTORY:
2294 WHO WHEN WHAT
2295
2296 ***************************************************************** */
2297
2298 -- Fetch the HESA Mapping value
2299 CURSOR cur_hesa_map (cp_assoc IGS_HE_CODE_MAP_VAL.association_code%TYPE,
2300 cp_map1 IGS_HE_CODE_MAP_VAL.map2%TYPE ) IS
2301 SELECT map2
2302 FROM IGS_HE_CODE_MAP_VAL
2303 WHERE association_code = cp_assoc
2304 AND map1 = cp_map1;
2305 l_oss_val IGS_HE_CODE_MAP_VAL.map2%TYPE;
2306
2307 CURSOR cur_citizen_exist (cp_person_id IGS_PE_CITIZENSHIP_V.party_id%TYPE,
2308 cp_country_code IGS_PE_CITIZENSHIP_V.country_code%TYPE) IS
2309 SELECT 'X'
2310 FROM IGS_PE_CITIZENSHIP_V
2311 WHERE party_id = cp_person_id
2312 AND country_code = cp_country_code;
2313 l_dummy VARCHAR2(1);
2314
2315 BEGIN
2316
2317 /* Populating the Interface Table for Nationality Details creation */
2318 IF p_nationality IS NOT NULL THEN
2319
2320 -- Get the OSS mapping value for nationality
2321 l_oss_val := NULL;
2322 OPEN cur_hesa_map('UC_OSS_HE_NAT_ASSOC', p_nationality);
2323 FETCH cur_hesa_map INTO l_oss_val;
2324
2325 IF cur_hesa_map%NOTFOUND THEN
2326
2327 p_app_valid_status := FALSE;
2328 fnd_message.set_name('IGS','IGS_UC_INV_MAPPING_VAL');
2329 fnd_message.set_token('CODE', p_nationality);
2330 fnd_message.set_token('TYPE', 'NATIONALITY');
2331 fnd_file.put_line(fnd_file.log, fnd_message.get);
2332
2333 ELSE
2334
2335 -- Check if Nationality already exists as citizenship for the current applicant
2336 OPEN cur_citizen_exist ( p_person_id, l_oss_val);
2337 FETCH cur_citizen_exist INTO l_dummy;
2338
2339 IF cur_citizen_exist%NOTFOUND THEN
2340
2341 INSERT INTO igs_pe_citizen_int (
2342 interface_citizenship_id
2343 ,interface_id
2344 ,country_code
2345 ,status
2346 ,date_recognized
2347 ,created_by
2348 ,creation_date
2349 ,last_updated_by
2350 ,last_update_date
2351 ,last_update_login )
2352 VALUES (
2353 IGS_PE_CITIZEN_INT_S.NEXTVAL
2354 ,p_interface_id
2355 ,l_oss_val
2356 ,'2'
2357 ,p_application_date
2358 ,g_created_by
2359 ,SYSDATE
2360 ,g_last_updated_by
2361 ,SYSDATE
2362 ,g_last_update_login );
2363
2364 END IF;
2365 CLOSE cur_citizen_exist;
2366
2367 END IF;
2368 CLOSE cur_hesa_map;
2369
2370 END IF; -- End of Nationality Import
2371
2372 /* Populating the Interface Table for Dual Nationality Details creation */
2373 IF p_dual_nationality IS NOT NULL THEN
2374
2375 l_oss_val := NULL;
2376 OPEN cur_hesa_map('UC_OSS_HE_NAT_ASSOC', p_dual_nationality);
2377 FETCH cur_hesa_map INTO l_oss_val;
2378
2379 IF cur_hesa_map%NOTFOUND THEN
2380
2381 p_app_valid_status := FALSE;
2382 fnd_message.set_name('IGS','IGS_UC_INV_MAPPING_VAL');
2383 fnd_message.set_token('CODE', p_dual_nationality);
2384 fnd_message.set_token('TYPE', 'NATIONALITY');
2385 fnd_file.put_line(fnd_file.log, fnd_message.get);
2386
2387 ELSE
2388
2389 -- Check if Dual Nationality already exists as citizenship for the current applicant
2390 OPEN cur_citizen_exist ( p_person_id, l_oss_val);
2391 FETCH cur_citizen_exist INTO l_dummy;
2392
2393 IF cur_citizen_exist%NOTFOUND THEN
2394
2395 INSERT INTO igs_pe_citizen_int (
2396 interface_citizenship_id
2397 ,interface_id
2398 ,country_code
2399 ,status
2400 ,date_recognized
2401 ,created_by
2402 ,creation_date
2403 ,last_updated_by
2404 ,last_update_date
2405 ,last_update_login )
2406 VALUES (
2407 IGS_PE_CITIZEN_INT_S.NEXTVAL
2408 ,p_interface_id
2409 ,l_oss_val
2410 ,'2'
2411 ,p_application_date
2412 ,g_created_by
2413 ,SYSDATE
2414 ,g_last_updated_by
2415 ,SYSDATE
2416 ,g_last_update_login );
2417
2418 END IF;
2419 CLOSE cur_citizen_exist;
2420
2421 END IF;
2422 CLOSE cur_hesa_map;
2423
2424 END IF; -- End of Dual Nationality Import
2425
2426 EXCEPTION
2427 WHEN OTHERS THEN
2428 p_app_valid_status := FALSE;
2429 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2430 fnd_message.set_token('NAME','IGS_UC_EXP_APPLICANT_DTLS.POP_CITIZEN_INT'||' - '||SQLERRM);
2431 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
2432
2433 END pop_citizen_int;
2434
2435
2436 PROCEDURE pop_disability_int(p_special_needs IN VARCHAR2,
2437 p_person_id IN NUMBER,
2438 p_application_date IN DATE,
2439 p_interface_id IN NUMBER,
2440 p_app_valid_status IN OUT NOCOPY BOOLEAN) AS
2441 /******************************************************************
2442 Created By : AYEDUBAT
2443 Date Created By : 16-JUN-2003
2444 Purpose : Puplate the Admission Interface Table, IGS_AD_DISABLTY_INT_ALL for
2445 importing the Special Needs data and set the parameter,p_app_valid_status
2446 to FALSE if any validation is failed or an exception is raised.
2447 Known limitations,enhancements,remarks:
2448
2449 CHANGE HISTORY:
2450 WHO WHEN WHAT
2451 jchakrab 20-Sep-2004 Modified for HEFD350 - replaced 3-way mapping UC_OSS_HE_DIS_ASSOC
2452 with new 2-way mapping UCAS_OSS_DISABILITY_ASSOC
2453 ***************************************************************** */
2454
2455 -- Fetch the HESA Mapping value
2456 CURSOR cur_hesa_map (cp_assoc IGS_HE_CODE_MAP_VAL.association_code%TYPE,
2457 cp_map1 IGS_HE_CODE_MAP_VAL.map2%TYPE ) IS
2458 SELECT map2
2459 FROM IGS_HE_CODE_MAP_VAL
2460 WHERE association_code = cp_assoc
2461 AND map1 = cp_map1;
2462 l_oss_val IGS_HE_CODE_MAP_VAL.map2%TYPE;
2463
2464 CURSOR cur_disablty_dtls ( cp_person_id IGS_PE_PERS_DISABLTY_V.person_id%TYPE,
2465 cp_disability_type IGS_PE_PERS_DISABLTY_V.disability_type%TYPE) IS
2466 SELECT start_date, end_date
2467 FROM IGS_PE_PERS_DISABLTY
2468 WHERE person_id = cp_person_id
2469 AND disability_type = cp_disability_type
2470 ORDER BY start_date DESC;
2471 cur_disablty_dtls_rec cur_disablty_dtls%ROWTYPE;
2472
2473 -- Fetch the past disability records with end date as NULL
2474 CURSOR cur_other_disablty_dtls ( cp_person_id IGS_PE_PERS_DISABLTY_V.person_id%TYPE) IS
2475 SELECT disability_type,start_date
2476 FROM IGS_PE_PERS_DISABLTY
2477 WHERE person_id = cp_person_id
2478 AND TRUNC(start_date) <= TRUNC(SYSDATE)
2479 AND end_date IS NULL;
2480
2481 l_dis_start_dt IGS_AD_DISABLTY_INT_ALL.start_date%TYPE;
2482 l_dis_end_dated BOOLEAN;
2483
2484 BEGIN
2485
2486 -- Importing Special Needs
2487
2488 -- get the OSS mapping value for the UCAS Special Needs column
2489 l_oss_val := NULL;
2490 OPEN cur_hesa_map('UCAS_OSS_DISABILITY_ASSOC', p_special_needs);
2491 FETCH cur_hesa_map INTO l_oss_val;
2492
2493 IF cur_hesa_map%NOTFOUND THEN
2494
2495 p_app_valid_status := FALSE;
2496 fnd_message.set_name('IGS','IGS_UC_INV_MAPPING_VAL');
2497 fnd_message.set_token('CODE', p_special_needs);
2498 fnd_message.set_token('TYPE', 'DISABILITY TYPE');
2499 fnd_file.put_line(fnd_file.log, fnd_message.get);
2500
2501 ELSE
2502
2503 -- Check if Special Needs already exists as disability for the current applicant
2504 OPEN cur_disablty_dtls(p_person_id, l_oss_val );
2505 FETCH cur_disablty_dtls INTO cur_disablty_dtls_rec;
2506
2507 IF cur_disablty_dtls%FOUND THEN
2508
2509 IF cur_disablty_dtls_rec.end_date IS NOT NULL THEN
2510
2511 fnd_message.set_name('IGS','IGS_UC_DISABLTY_END_DATED');
2512 fnd_file.put_line(fnd_file.log, fnd_message.get);
2513
2514 END IF;
2515
2516 ELSE -- If record not found, then populate the interface table
2517
2518 l_dis_end_dated := FALSE;
2519 --- Loop through the existing disability records with end date as NULL
2520 FOR cur_other_disablty_dtls_rec IN cur_other_disablty_dtls(p_person_id) LOOP
2521
2522 -- Populate the Interface Table for the Disability type with End Date as SYSDATE
2523 -- Populate the Interface Table, IGS_AD_DISABLTY_INT_ALL
2524 l_dis_end_dated := TRUE;
2525 INSERT INTO igs_ad_disablty_int_all (
2526 interface_disablty_id
2527 ,interface_id
2528 ,disability_type
2529 ,start_date
2530 ,end_date
2531 ,status
2532 ,created_by
2533 ,creation_date
2534 ,last_updated_by
2535 ,last_update_date
2536 ,last_update_login )
2537 VALUES (
2538 IGS_AD_DISABLTY_INT_S.NEXTVAL
2539 ,p_interface_id
2540 ,cur_other_disablty_dtls_rec.disability_type
2541 ,cur_other_disablty_dtls_rec.start_date
2542 ,TRUNC(SYSDATE)
2543 ,'2'
2544 ,g_created_by
2545 ,SYSDATE
2546 ,g_last_updated_by
2547 ,SYSDATE
2548 ,g_last_update_login );
2549
2550 END LOOP;
2551
2552 -- If the Previous disability record is end dated then create a new record with
2553 -- Start Date as SYSDATE else with Application Date
2554 IF l_dis_end_dated = TRUE THEN
2555 l_dis_start_dt := TRUNC(SYSDATE);
2556 ELSE
2557 l_dis_start_dt := TRUNC(p_application_date);
2558 END IF;
2559
2560 -- Populate the Interface Table, IGS_AD_DISABLTY_INT_ALL
2561 INSERT INTO igs_ad_disablty_int_all (
2562 interface_disablty_id
2563 ,interface_id
2564 ,disability_type
2565 ,start_date
2566 ,status
2567 ,created_by
2568 ,creation_date
2569 ,last_updated_by
2570 ,last_update_date
2571 ,last_update_login )
2572 VALUES (
2573 IGS_AD_DISABLTY_INT_S.NEXTVAL
2574 ,p_interface_id
2575 ,l_oss_val
2576 ,l_dis_start_dt
2577 ,'2'
2578 ,g_created_by
2579 ,SYSDATE
2580 ,g_last_updated_by
2581 ,SYSDATE
2582 ,g_last_update_login );
2583
2584 END IF;
2585 CLOSE cur_disablty_dtls;
2586
2587 END IF;
2588 CLOSE cur_hesa_map;
2589
2590 EXCEPTION
2591 WHEN OTHERS THEN
2592 p_app_valid_status := FALSE;
2593 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2594 fnd_message.set_token('NAME','IGS_UC_EXP_APPLICANT_DTLS.POP_DISABILITY_INT'||' - '||SQLERRM);
2595 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
2596
2597 END pop_disability_int;
2598
2599
2600 PROCEDURE pop_contact_int ( p_telephone IN VARCHAR2,
2601 p_email IN VARCHAR2,
2602 p_home_phone IN VARCHAR2,
2603 p_mobile IN VARCHAR2,
2604 p_interface_id IN NUMBER,
2605 p_app_valid_status IN OUT NOCOPY BOOLEAN) AS
2606 /******************************************************************
2607 Created By : AYEDUBAT
2608 Date Created By : 16-JUN-2003
2609 Purpose : Puplate the Admission Interface Table, IGS_AD_CONTACTS_INT_ALL
2610 and set the Parameter,p_app_valid_status to FALSE if an exception is raised
2611 Known limitations,enhancements,remarks:
2612
2613 CHANGE HISTORY:
2614 WHO WHEN WHAT
2615 jbaber 11-Jul-05 Added mobile for UC325 - UCAS 2007 Support
2616 ***************************************************************** */
2617
2618 --To get the Email format to populate into igs_ad_contacts_int_all table.
2619 CURSOR cur_email_format IS
2620 SELECT lookup_code
2621 FROM fnd_lookup_values
2622 WHERE lookup_type = 'EMAIL_FORMAT'
2623 AND enabled_flag ='Y'
2624 AND NVL(START_DATE_ACTIVE,SYSDATE) <=SYSDATE
2625 AND NVL(END_DATE_ACTIVE,SYSDATE) >= SYSDATE
2626 AND LANGUAGE = USERENV('LANG') AND view_application_id = 222 AND security_group_id(+) = 0;
2627
2628 l_email_format FND_LOOKUP_VALUES.lookup_code%TYPE;
2629
2630 BEGIN
2631
2632 -- Populate the Admission Contacts Interface Table for Primary Phone Number
2633 IF p_telephone IS NOT NULL THEN
2634
2635 INSERT INTO igs_ad_contacts_int_all (
2636 interface_contacts_id
2637 ,interface_id
2638 ,phone_number
2639 ,status
2640 ,contact_point_type
2641 ,primary_flag
2642 ,phone_line_type
2643 ,created_by
2644 ,creation_date
2645 ,last_updated_by
2646 ,last_update_date
2647 ,last_update_login )
2648 VALUES (
2649 IGS_AD_CONTACTS_INT_S.NEXTVAL
2650 ,p_interface_id
2651 ,p_telephone
2652 ,'2'
2653 ,'PHONE'
2654 ,'Y'
2655 ,'GEN'
2656 ,g_created_by
2657 ,SYSDATE
2658 ,g_last_updated_by
2659 ,SYSDATE
2660 ,g_last_update_login );
2661
2662 END IF;
2663
2664 -- Populate the Admission Contacts Interface Table for E-Mail Address creation
2665 IF p_email IS NOT NULL THEN
2666
2667 -- Find the EMAIL Format to be used from the Look ups
2668 OPEN cur_email_format;
2669 FETCH cur_email_format INTO l_email_format;
2670 CLOSE cur_email_format;
2671
2672 INSERT INTO igs_ad_contacts_int_all (
2673 interface_contacts_id
2674 ,interface_id
2675 ,email_address
2676 ,email_format
2677 ,status
2678 ,contact_point_type
2679 ,primary_flag
2680 ,phone_line_type
2681 ,created_by
2682 ,creation_date
2683 ,last_updated_by
2684 ,last_update_date
2685 ,last_update_login )
2686 VALUES (
2687 IGS_AD_CONTACTS_INT_S.NEXTVAL
2688 ,p_interface_id
2689 ,p_email
2690 ,l_email_format
2691 ,'2'
2692 ,'EMAIL'
2693 ,'N'
2694 ,'GEN'
2695 ,g_created_by
2696 ,SYSDATE
2697 ,g_last_updated_by
2698 ,SYSDATE
2699 ,g_last_update_login );
2700
2701 END IF;
2702
2703 -- Populate the Admission Contacts Interface Table for Home Phone Number creation
2704 IF p_home_phone IS NOT NULL THEN
2705
2706 INSERT INTO igs_ad_contacts_int_all (
2707 interface_contacts_id
2708 ,interface_id
2709 ,phone_number
2710 ,status
2711 ,contact_point_type
2712 ,primary_flag
2713 ,phone_line_type
2714 ,created_by
2715 ,creation_date
2716 ,last_updated_by
2717 ,last_update_date
2718 ,last_update_login )
2719 VALUES (
2720 IGS_AD_CONTACTS_INT_S.NEXTVAL
2721 ,p_interface_id
2722 ,p_home_phone
2723 ,'2'
2724 ,'PHONE'
2725 ,'N'
2726 ,'GEN'
2727 ,g_created_by
2728 ,SYSDATE
2729 ,g_last_updated_by
2730 ,SYSDATE
2731 ,g_last_update_login );
2732
2733 END IF;
2734
2735 -- Populate the Admission Contacts Interface Table for Mobile Number creation
2736 IF p_mobile IS NOT NULL THEN
2737
2738 INSERT INTO igs_ad_contacts_int_all (
2739 interface_contacts_id
2740 ,interface_id
2741 ,phone_number
2742 ,status
2743 ,contact_point_type
2744 ,primary_flag
2745 ,phone_line_type
2746 ,created_by
2747 ,creation_date
2748 ,last_updated_by
2749 ,last_update_date
2750 ,last_update_login )
2751 VALUES (
2752 IGS_AD_CONTACTS_INT_S.NEXTVAL
2753 ,p_interface_id
2754 ,p_mobile
2755 ,'2'
2756 ,'PHONE'
2757 ,'N'
2758 ,'MOBILE'
2759 ,g_created_by
2760 ,SYSDATE
2761 ,g_last_updated_by
2762 ,SYSDATE
2763 ,g_last_update_login );
2764
2765 END IF;
2766
2767 EXCEPTION
2768 WHEN OTHERS THEN
2769 p_app_valid_status := FALSE;
2770 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2771 fnd_message.set_token('NAME','IGS_UC_EXP_APPLICANT_DTLS.POP_CONTACT_INT'||' - '||SQLERRM);
2772 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
2773
2774 END pop_contact_int;
2775
2776
2777 FUNCTION get_country_code(p_app_no IN IGS_UC_APP_ADDRESES.app_no%TYPE,
2778 p_ucas_country_code IN VARCHAR2,
2779 p_address_area IN VARCHAR2,
2780 p_address4 IN VARCHAR2,
2781 p_adr_type IN VARCHAR2)
2782 RETURN VARCHAR2 AS
2783 /******************************************************************
2784 Created By : JBaber
2785 Date Created By : 16-Jul-2006
2786 Purpose : Derive country code value
2787 Known limitations,enhancements,remarks:
2788
2789 CHANGE HISTORY:
2790 WHO WHEN WHAT
2791 ***************************************************************** */
2792
2793 -- Fetch the Territory Code for the Address4 or HomeAddress4
2794 CURSOR cur_short_name ( p_short_name FND_TERRITORIES_VL.territory_short_name%TYPE ) IS
2795 SELECT territory_code
2796 FROM FND_TERRITORIES_VL
2797 WHERE territory_short_name = p_short_name ;
2798 cur_short_name_rec cur_short_name%ROWTYPE ;
2799
2800 -- Fetch the OSS Nationality Code mapped to Domicile APR
2801 CURSOR cur_map (p_assoc igs_he_code_map_val.association_code%TYPE,
2802 p_map1 igs_he_code_map_val.map2%TYPE ) IS
2803 SELECT map2
2804 FROM IGS_HE_CODE_MAP_VAL
2805 WHERE association_code = p_assoc
2806 AND map1 = p_map1;
2807
2808 l_country_code IGS_AD_ADDR_INT_ALL.country%TYPE;
2809
2810 BEGIN
2811
2812 /* Derive Country Code */
2813 l_country_code := NULL ;
2814 IF p_ucas_country_code IS NOT NULL THEN
2815
2816 -- This gets the US standard country code
2817 OPEN cur_map('UCAS_OSS_COUNTRY_ASSOC', p_ucas_country_code);
2818 FETCH cur_map INTO l_country_code;
2819 CLOSE cur_map;
2820
2821 ELSE
2822
2823 -- If address4 is TCA Country description then consider country code
2824 -- associated with Country description
2825 OPEN cur_short_name(p_address4);
2826 FETCH cur_short_name INTO cur_short_name_rec ;
2827
2828 IF cur_short_name%FOUND THEN
2829 l_country_code := cur_short_name_rec.territory_code;
2830 END IF;
2831
2832 CLOSE cur_short_name ;
2833
2834 END IF;
2835
2836 -- If country code could not be derived,
2837 -- assign the Obsolete Country CD Territory
2838 IF l_country_code IS NULL THEN
2839 l_country_code := 'ZR' ;
2840
2841 -- Display warning that the country for this student address should be checked
2842 fnd_message.set_name('IGS','IGS_UC_INVAL_COUNTRY_CODE') ;
2843 fnd_message.set_token('APPNO',p_app_no) ;
2844 fnd_message.set_token('ADR_TYPE',p_adr_type) ;
2845 fnd_file.put_line(fnd_file.log, fnd_message.get) ;
2846 END IF ;
2847
2848 RETURN l_country_code;
2849
2850 END get_country_code;
2851
2852
2853 PROCEDURE pop_address_int ( p_app_address_dtls_rec IN IGS_UC_APP_ADDRESES%ROWTYPE,
2854 p_domocile_apr IN VARCHAR2,
2855 p_interface_id IN NUMBER,
2856 p_addr_usage_home IN VARCHAR2,
2857 p_addr_usage_corr IN VARCHAR2,
2858 p_app_valid_status IN OUT NOCOPY BOOLEAN) AS
2859 /******************************************************************
2860 Created By : AYEDUBAT
2861 Date Created By : 16-JUN-2003
2862 Purpose : Puplate the Admission Interface Tables,
2863 IGS_AD_ADDR_INT_ALL and IGS_AD_ADDRUSAGE_INT_ALL
2864 Known limitations,enhancements,remarks:
2865
2866 CHANGE HISTORY:
2867 WHO WHEN WHAT
2868
2869 anwest 20-Sep-2004 Bug# 3622076 - Code to prevent NULL value
2870 for UCAS home_address1
2871 ***************************************************************** */
2872
2873
2874 l_country_code IGS_AD_ADDR_INT_ALL.country%TYPE;
2875 l_interface_addr_id IGS_AD_ADDR_INT_ALL.interface_addr_id%TYPE;
2876
2877 -- anwest Bug# 3622076 Local variable to store first line of address in
2878 l_home_address1 igs_ad_addr_int_all.addr_line_1%TYPE;
2879
2880 l_home_flag BOOLEAN := TRUE;
2881 l_corr_flag BOOLEAN := TRUE;
2882 l_usage VARCHAR2(80);
2883
2884 BEGIN
2885
2886
2887 -- Check if home address exists
2888 IF p_app_address_dtls_rec.home_address1 IS NULL AND
2889 p_app_address_dtls_rec.home_address2 IS NULL AND
2890 p_app_address_dtls_rec.home_address3 IS NULL AND
2891 p_app_address_dtls_rec.home_address4 IS NULL AND
2892 p_app_address_dtls_rec.home_postcode IS NULL THEN
2893
2894 l_home_flag := FALSE;
2895
2896 END IF;
2897
2898 -- Check if correspondence address exists
2899 IF p_app_address_dtls_rec.address1 IS NULL AND
2900 p_app_address_dtls_rec.address2 IS NULL AND
2901 p_app_address_dtls_rec.address3 IS NULL AND
2902 p_app_address_dtls_rec.address4 IS NULL AND
2903 p_app_address_dtls_rec.post_code IS NULL THEN
2904
2905 l_corr_flag := FALSE;
2906
2907 END IF;
2908
2909 -------------------------------------------------------------------
2910 /* Import correspondence address */
2911 -------------------------------------------------------------------
2912 IF l_corr_flag THEN
2913
2914 -- Logic for caluculating the 'COUNTRY' coumn value for both Correspondence Address
2915 l_country_code := get_country_code(p_app_address_dtls_rec.app_no,
2916 p_app_address_dtls_rec.country_code,
2917 p_app_address_dtls_rec.address_area,
2918 p_app_address_dtls_rec.address4,
2919 'CORRESPONDENCE') ;
2920
2921 -- Populate the Address Interface table for single address, and secondary address
2922 l_interface_addr_id := NULL;
2923 INSERT INTO igs_ad_addr_int_all (
2924 interface_addr_id
2925 ,interface_id
2926 ,addr_line_1
2927 ,addr_line_2
2928 ,addr_line_3
2929 ,addr_line_4
2930 ,postcode
2931 ,country
2932 ,delivery_point_code
2933 ,correspondence_flag
2934 ,start_date
2935 ,status
2936 ,created_by
2937 ,creation_date
2938 ,last_updated_by
2939 ,last_update_date
2940 ,last_update_login )
2941 VALUES (
2942 IGS_AD_ADDR_INT_S.NEXTVAL
2943 ,p_interface_id
2944 ,p_app_address_dtls_rec.address1
2945 ,p_app_address_dtls_rec.address2
2946 ,p_app_address_dtls_rec.address3
2947 ,p_app_address_dtls_rec.address4
2948 ,p_app_address_dtls_rec.post_code
2949 ,l_country_code
2950 ,p_app_address_dtls_rec.mail_sort
2951 ,'Y'
2952 ,SYSDATE
2953 ,'2'
2954 ,g_created_by
2955 ,SYSDATE
2956 ,g_last_updated_by
2957 ,SYSDATE
2958 ,g_last_update_login )
2959 RETURNING interface_addr_id INTO l_interface_addr_id ;
2960
2961 -- If no home address was provided then correspondence address usage is set to HOME
2962 IF NOT l_home_flag THEN
2963 l_usage := NVL(p_addr_usage_home,'HOME');
2964 ELSE
2965 l_usage := NVL(p_addr_usage_corr,'CORR');
2966 END IF;
2967
2968 -- Populating the Address Usage Interface Table for Correspondence Address
2969 INSERT INTO igs_ad_addrusage_int_all (
2970 interface_addrusage_id
2971 ,interface_addr_id
2972 ,site_use_code
2973 ,status
2974 ,created_by
2975 ,creation_date
2976 ,last_updated_by
2977 ,last_update_date
2978 ,last_update_login )
2979 VALUES (
2980 igs_ad_addrusage_int_s.NEXTVAL
2981 ,l_interface_addr_id
2982 ,l_usage
2983 ,'2'
2984 ,g_created_by
2985 ,SYSDATE
2986 ,g_last_updated_by
2987 ,SYSDATE
2988 ,g_last_update_login );
2989
2990 END IF;
2991
2992
2993 -------------------------------------------------------------------
2994 /* Import home address */
2995 -------------------------------------------------------------------
2996 IF l_home_flag THEN
2997
2998 -- Logic for caluculating the 'COUNTRY' coumn value for both Correspondence Address
2999 l_country_code := get_country_code(p_app_address_dtls_rec.app_no,
3000 p_app_address_dtls_rec.home_country_code,
3001 p_app_address_dtls_rec.address_area,
3002 p_app_address_dtls_rec.home_address4,
3003 'HOME') ;
3004
3005 -- anwest Bug# 3622076 If address line 1 is NULL substitute dummy value
3006 IF p_app_address_dtls_rec.home_address1 IS NULL THEN
3007 fnd_message.set_name('IGS', 'IGS_UC_NO_ADD_GIVEN');
3008 l_home_address1 := fnd_message.get;
3009 ELSE
3010 l_home_address1 := p_app_address_dtls_rec.home_address1;
3011 END IF;
3012
3013 l_interface_addr_id := NULL;
3014 INSERT INTO igs_ad_addr_int_all(
3015 interface_addr_id
3016 ,interface_id
3017 ,addr_line_1
3018 ,addr_line_2
3019 ,addr_line_3
3020 ,addr_line_4
3021 ,postcode
3022 ,country
3023 ,delivery_point_code
3024 ,correspondence_flag
3025 ,start_date
3026 ,status
3027 ,created_by
3028 ,creation_date
3029 ,last_updated_by
3030 ,last_update_date
3031 ,last_update_login )
3032 VALUES (
3033 IGS_AD_ADDR_INT_S.NEXTVAL
3034 ,p_interface_id
3035 ,l_home_address1
3036 ,p_app_address_dtls_rec.home_address2
3037 ,p_app_address_dtls_rec.home_address3
3038 ,p_app_address_dtls_rec.home_address4
3039 ,p_app_address_dtls_rec.home_postcode
3040 ,l_country_code
3041 ,p_app_address_dtls_rec.mail_sort
3042 ,'N'
3043 ,SYSDATE
3044 ,'2'
3045 ,g_created_by
3046 ,SYSDATE
3047 ,g_last_updated_by
3048 ,SYSDATE
3049 ,g_last_update_login )
3050 RETURNING interface_addr_id INTO l_interface_addr_id ;
3051
3052 -- Populating the Address Usage Interface Table for Home Address
3053 -- Usage is given by p_addr_usage_home if available, otherwise use HOME
3054 INSERT INTO igs_ad_addrusage_int_all (
3055 interface_addrusage_id
3056 ,interface_addr_id
3057 ,site_use_code
3058 ,status
3059 ,created_by
3060 ,creation_date
3061 ,last_updated_by
3062 ,last_update_date
3063 ,last_update_login )
3064 VALUES (
3065 igs_ad_addrusage_int_s.NEXTVAL
3066 ,l_interface_addr_id
3067 ,NVL(p_addr_usage_home, 'HOME')
3068 ,'2'
3069 ,g_created_by
3070 ,SYSDATE
3071 ,g_last_updated_by
3072 ,SYSDATE
3073 ,g_last_update_login );
3074
3075 END IF;
3076
3077
3078 EXCEPTION
3079 WHEN OTHERS THEN
3080 p_app_valid_status := FALSE;
3081 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
3082 fnd_message.set_token('NAME','IGS_UC_EXP_APPLICANT_DTLS.POP_ADDRESS_INT'||' - '||SQLERRM);
3083 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
3084
3085 END pop_address_int;
3086
3087 FUNCTION chk_src_cat ( p_source_type_id IN NUMBER,
3088 p_category IN VARCHAR2 )
3089 RETURN BOOLEAN AS
3090 /******************************************************************
3091 Created By : AYEDUBAT
3092 Date Created By : 16-JUN-2003
3093 Purpose : To check whether a source type is included or not
3094 Known limitations,enhancements,remarks:
3095
3096 CHANGE HISTORY:
3097 WHO WHEN WHAT
3098
3099 ***************************************************************** */
3100
3101 l_dummy VARCHAR2(1);
3102 CURSOR cur_src_included ( cp_source_type_id IGS_AD_SOURCE_CAT.source_type_id%TYPE,
3103 cp_category IGS_AD_SOURCE_CAT.category_name%TYPE) IS
3104 SELECT 'X'
3105 FROM IGS_AD_SOURCE_CAT
3106 WHERE source_type_id = cp_source_type_id AND
3107 category_name = cp_category AND
3108 include_ind = 'Y';
3109
3110 BEGIN
3111
3112 -- Check whether the Source Type is included
3113 OPEN cur_src_included(p_source_type_id, p_category);
3114 FETCH cur_src_included INTO l_dummy;
3115
3116 -- If included return True, otherwise return False
3117 IF cur_src_included%FOUND THEN
3118 CLOSE cur_src_included;
3119 RETURN TRUE;
3120 ELSE
3121 CLOSE cur_src_included;
3122 RETURN FALSE;
3123 END IF;
3124
3125 EXCEPTION
3126 WHEN OTHERS THEN
3127 IF cur_src_included%ISOPEN THEN
3128 CLOSE cur_src_included;
3129 END IF;
3130 RETURN FALSE;
3131
3132 END chk_src_cat;
3133
3134
3135 PROCEDURE adm_import_process( p_ad_batch_id IN NUMBER,
3136 p_source_type_id IN NUMBER,
3137 p_status IN OUT NOCOPY BOOLEAN) IS
3138 /******************************************************************
3139 Created By : AYEDUBAT
3140 Date Created By : 16-JUN-2003
3141 Purpose : To Call Admission Import Process API
3142 Known limitations,enhancements,remarks:
3143
3144 CHANGE HISTORY:
3145 WHO WHEN WHAT
3146
3147 ***************************************************************** */
3148
3149 CURSOR cur_match_set ( cp_source_type_id IGS_PE_MATCH_SETS.source_type_id%TYPE) IS
3150 SELECT match_set_id
3151 FROM IGS_PE_MATCH_SETS
3152 WHERE source_type_id = cp_source_type_id;
3153 l_match_set_id IGS_PE_MATCH_SETS.match_set_id%TYPE;
3154
3155 -- Fetch the Interface ID for the passed Batch ID
3156 CURSOR cur_ad_interface ( cp_batch_id IGS_AD_INTERFACE_ALL.batch_id%TYPE) IS
3157 SELECT interface_id
3158 FROM IGS_AD_INTERFACE_ALL
3159 WHERE batch_id = cp_batch_id;
3160 l_interface_id IGS_AD_INTERFACE_ALL.interface_id%TYPE;
3161
3162 l_interface_run_id igs_ad_interface_ctl.interface_run_id%TYPE;
3163 l_errbuff VARCHAR2(100) ;
3164 l_retcode NUMBER ;
3165
3166
3167 BEGIN
3168
3169 -- Initialize the variables
3170 l_interface_run_id := NULL ;
3171 l_errbuff:= NULL ;
3172 l_retcode := NULL ;
3173
3174 -- Get the match set criteria corresponding to the ucas source type to be used for the person import
3175 l_match_set_id := NULL ;
3176 OPEN cur_match_set(p_source_type_id);
3177 FETCH cur_match_set INTO l_match_set_id;
3178 CLOSE cur_match_set;
3179
3180 -- Check whether any records exist in the Admission Interface Table for the Batch ID
3181 OPEN cur_ad_interface(p_ad_batch_id);
3182 FETCH cur_ad_interface INTO l_interface_id;
3183
3184 /* The admission import process should be launched only if admission interface records are inserted
3185 in instance of the current run and the Source Type ID and Match Set ID are not null */
3186 IF NVL(p_ad_batch_id,0) <> 0 AND cur_ad_interface%FOUND AND
3187 NVL(p_source_type_id,0) <> 0 AND NVL(l_match_set_id,0) <> 0 THEN
3188
3189 p_status := TRUE;
3190 -- Display the Message in the Log File
3191 fnd_file.put_line( fnd_file.LOG ,' ');
3192 fnd_message.set_name('IGS','IGS_UC_ADM_IMP_PROC_LAUNCH');
3193 fnd_message.set_token('REQ_ID',TO_CHAR(p_ad_batch_id));
3194 fnd_file.put_line( fnd_file.LOG ,fnd_message.get||' ('||to_char(SYSDATE,'DD-MON-YYYY HH:MI:SS')||')');
3195
3196 -- Call admission application import process procedure because current process
3197 -- has to wait until import process is finished
3198 IGS_AD_IMP_001.IMP_ADM_DATA ( errbuf => l_errbuff,
3199 retcode => l_retcode ,
3200 p_batch_id => p_ad_batch_id,
3201 p_source_type_id => p_source_type_id,
3202 p_match_set_id => l_match_set_id,
3203 p_acad_cal_type => NULL ,
3204 p_acad_sequence_number => NULL ,
3205 p_adm_cal_type => NULL ,
3206 p_adm_sequence_number => NULL ,
3207 p_admission_cat => NULL ,
3208 p_s_admission_process_type => NULL ,
3209 p_interface_run_id => l_interface_run_id ,
3210 p_org_id => NULL ) ;
3211
3212
3213 ELSE
3214
3215 p_status := FALSE;
3216 -- As the required parameters are not avilable to launch Admission Import Process,
3217 -- Log message is populated in log file.
3218 fnd_file.put_line( fnd_file.LOG ,' ');
3219 fnd_message.set_name('IGS','IGS_UC_NOT_LAUNCH_IMP_PROC');
3220 fnd_file.put_line(fnd_file.log, fnd_message.get);
3221
3222 END IF;
3223 CLOSE cur_ad_interface;
3224
3225 EXCEPTION
3226 WHEN OTHERS THEN
3227 p_status := FALSE;
3228 -- even though the admission import process completes in error , this process should continue processing
3229 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
3230 fnd_message.set_token('NAME','IGS_UC_EXP_APPLICANT_DTLS.ADM_IMPORT_PROCESS'||' - '||SQLERRM);
3231 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
3232
3233 END adm_import_process;
3234
3235 END igs_uc_exp_applicant_dtls;
[Home] [Help]