1 PACKAGE BODY igs_uc_export_to_oss AS
2 /* $Header: IGSUC20B.pls 120.11 2006/08/21 03:55:46 jbaber ship $ */
3
4
5
6 -- Get application choice details to update error code, batch id, export status and requesty id
7 CURSOR c_upd_ch ( cp_app_no igs_uc_app_choices.app_no%TYPE ,
8 cp_choice_no igs_uc_app_choices.choice_no%TYPE,
9 cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
10 SELECT ch.ROWID , ch.*
11 FROM igs_uc_app_choices ch
12 WHERE ch.app_no = cp_app_no
13 AND ch.choice_no = cp_choice_no
14 AND ch.ucas_cycle = cp_ucas_cycle;
15 c_upd_ch_rec c_upd_ch%ROWTYPE ;
16
17 l_conc_request_id NUMBER;
18 l_org_id CONSTANT igs_ps_ver.org_id%TYPE := IGS_GE_GEN_003.GET_ORG_ID ;
19
20
21 PROCEDURE populate_imp_int (
22 p_app_no IN igs_uc_applicants.app_no%TYPE,
23 p_choice_no IN igs_uc_app_choices.choice_no%TYPE ,
24 p_source_type_id igs_pe_src_types_all.source_type_id%TYPE,
25 p_batch_id NUMBER,
26 p_orgid NUMBER) AS
27 /******************************************************************
28 Created By : smaddali
29 Date Created By : 08-MAR-2002
30 Purpose : To populate import application interface tables
31 for all valid choices in status OC of the passed application
32 Known limitations,enhancements,remarks:
33 Change History
34 Who When What
35 --smaddali ,bug2643048 UCFD102 build. Modified procedure to add check for igs_uc_defaults.system_code
36 -- and modified insert row of igs_ad_apl_int to insert values for columns alt_appl_id and admission_application_type
37 -- and to get the admission and academic calendars set up in igs_uc_defaults for the application choice system
38 --pmarada 22-aug-2003 REmoved the insert row call to igs_ad_stat_int table, this is not required.bug 3094409
39 --jchakrab 03-Oct-2005 Modified for 4506750 Impact - added extra filter for IGS_AD_CODE_CLASSES.class_type_code
40 --jchakrab 10-Oct-2005 Modified for 4424068 - added CANCEL functionality for prog version change
41 --jchin 20-jan-2006 Modified for R12 perf improvements - bug 3691277 and 3691250
42 --jchakrab 22-May-2006 Modified for 5165624
43 ***************************************************************** */
44
45 l_status CONSTANT NUMBER := 2;
46 l_record_status CONSTANT NUMBER := 2;
47 l_created_by CONSTANT NUMBER := FND_GLOBAL.USER_ID;
48 l_last_updated_by CONSTANT NUMBER := FND_GLOBAL.LOGIN_ID;
49 l_request_id CONSTANT NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
50 l_program_application_id CONSTANT NUMBER := FND_GLOBAL.PROG_APPL_ID;
51 l_program_id CONSTANT NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
52 l_ch_error igs_uc_app_choices.error_code%TYPE ;
53 l_ch_batch_id igs_uc_app_choices.batch_id%TYPE ;
54 l_export_to_oss_status igs_uc_app_choices.export_to_oss_status%TYPE ;
55 l_aca_cal_type igs_uc_sys_calndrs.aca_cal_type%TYPE ;
56 l_aca_seq_no igs_uc_sys_calndrs.aca_cal_seq_no%TYPE;
57 l_adm_cal_type igs_uc_sys_calndrs.adm_cal_type%TYPE ;
58 l_adm_seq_no igs_uc_sys_calndrs.adm_cal_seq_no%TYPE ;
59 l_update_adm_appl_number igs_ad_apl_int.update_adm_appl_number%TYPE;
60 l_update_adm_seq_number igs_ad_ps_appl_inst_int.update_adm_seq_number%TYPE;
61
62 -- Get the admission application corresponding to the passed choice record
63 -- Smaddali modified this cursor to add check for c.alt_appl_id , bug 2643048 UCFD012
64 CURSOR c_match_adm_appl(cp_app_no igs_uc_app_choices.app_no%TYPE ,
65 cp_choice_no igs_uc_app_choices.choice_no%TYPE ) IS
66 SELECT c.admission_appl_number, c.person_id
67 FROM igs_uc_applicants a , igs_uc_app_choices b , igs_ad_appl_all c
68 WHERE a.app_no = b.app_no AND
69 TO_CHAR(a.app_no) = c.alt_appl_id AND
70 a.oss_person_id = c.person_id AND
71 b.choice_no = c.choice_number AND
72 c.acad_cal_type = l_aca_cal_type AND
73 c.acad_ci_sequence_number = l_aca_seq_no AND
74 c.adm_cal_type = l_adm_cal_type AND
75 c.adm_ci_sequence_number = l_adm_seq_no AND
76 b.app_no = cp_app_no AND
77 b.choice_no = cp_choice_no AND
78 c.adm_appl_status <> 'COMPLETED';
79 c_match_adm_appl_rec c_match_adm_appl%ROWTYPE ;
80
81 -- Get the admission application instance sequence number corresponding to the passed OSS details
82 CURSOR c_match_adm_appl_inst ( cp_person_id igs_ad_appl_all.person_id%TYPE,
83 cp_admission_appl_number igs_ad_appl_all.admission_appl_number%TYPE,
84 cp_oss_program_code igs_uc_app_choices.oss_program_code%TYPE,
85 cp_oss_program_version igs_uc_app_choices.oss_program_version%TYPE,
86 cp_oss_location igs_uc_app_choices.oss_location%TYPE,
87 cp_oss_attendance_mode igs_uc_app_choices.oss_attendance_mode%TYPE,
88 cp_oss_attendance_type igs_uc_app_choices.oss_attendance_type%TYPE,
89 cp_unit_set_cd igs_ps_ofr_opt_unit_set_v.unit_set_cd%TYPE,
90 cp_us_version_number igs_ps_ofr_opt_unit_set_v.us_version_number%TYPE ) IS
91 SELECT acai.sequence_number
92 FROM igs_ad_ps_appl_inst acai
93 WHERE acai.person_id = cp_person_id
94 AND acai.admission_appl_number = cp_admission_appl_number
95 AND acai.nominated_course_cd = cp_oss_program_code
96 AND acai.crv_version_number = cp_oss_program_version
97 AND NVL(acai.location_cd,'X') = NVL( cp_oss_location ,'X')
98 AND NVL(acai.attendance_mode,'X') = NVL( cp_oss_attendance_mode ,'X')
99 AND NVL(acai.attendance_type,'X') = NVL( cp_oss_attendance_type ,'X')
100 AND NVL(acai.unit_set_cd,'X') = NVL( cp_unit_set_cd ,'X')
101 AND NVL(acai.us_version_number,-1)= NVL( cp_us_version_number ,-1);
102
103 c_match_adm_appl_inst_rec c_match_adm_appl_inst%ROWTYPE;
104
105 -- Get the person details from oss for the passed applicant
106 CURSOR c_pe_person IS
107 SELECT p.party_id person_id, p.party_number person_number, p.person_last_name surname,
108 p.person_middle_name middle_name, p.person_first_name given_names,
109 pp.gender sex,p.person_title title, p.person_name_suffix suffix,
110 p.person_pre_name_adjunct pre_name_adjunct,
111 Pd.proof_of_ins , pd.proof_of_immu,
112 pp.date_of_birth birth_dt, p.known_as preferred_given_name,
113 pd.level_of_qual level_of_qual_id, pd.military_service_reg,
114 pd.veteran, e.application_date
115 FROM igs_pe_HZ_parties pd, hz_parties p , hz_person_profiles pp ,
116 igs_uc_applicants e
117 WHERE p.party_id = e.oss_person_id AND e.app_no = p_app_no AND
118 pp.party_id(+)=p.party_id AND p.party_id=pd.party_id(+) AND
119 SYSDATE BETWEEN NVL(pp.effective_start_date,SYSDATE) AND NVL(pp.effective_end_date,SYSDATE);
120 c_pe_person_rec c_pe_person%ROWTYPE;
121 -- Get the interface id
122 CURSOR c_int_id IS
123 SELECT igs_ad_interface_s.NEXTVAL int_id
124 FROM dual;
125 c_int_id_rec c_int_id%ROWTYPE;
126
127 -- Get application interface ID
128 CURSOR c_int_appl_id IS
129 SELECT igs_ad_apl_int_s.NEXTVAL int_appl_id
130 FROM dual;
131 c_int_appl_id_rec c_int_appl_id%ROWTYPE;
132 -- Get application instance interface id
133 CURSOR c_appl_inst_int_id IS
134 SELECT igs_ad_ps_appl_inst_int_s.NEXTVAL appl_inst_int_id
135 FROM dual;
136 appl_inst_int_id_rec c_appl_inst_int_id%ROWTYPE;
137 -- Get ucas application source from code classes
138 CURSOR cur_code_id IS
139 SELECT code_id
140 FROM igs_ad_code_classes
141 WHERE class = 'SYS_APPL_SOURCE'
142 AND name = 'UCAS'
143 AND class_type_code = 'ADM_CODE_CLASSES';
144
145 l_code_id igs_ad_code_classes.code_id%TYPE;
146 -- Get all the application choices belonging to the passed app_no and choice_no in status OC
147 -- if choice no is null then get all the choices in status OC belonging to the current institution
148 -- smaddali modified cursor to select system_code also ,for bug 2643048
149 CURSOR c_uc_app_ch IS
150 SELECT DISTINCT a.ucas_program_code, a.campus, a.choice_no, a.oss_program_code, a.oss_program_version,
151 a.oss_location, a.point_of_entry, a.deferred, a.oss_attendance_type, a.oss_attendance_mode,
152 a.route_b_pref_round, b.application_source , a.app_no , a.system_code, a.ucas_cycle, a.entry_year, a.entry_month
153 FROM igs_uc_app_choices a, igs_uc_applicants b
154 WHERE a.app_no=b.app_no AND
155 b.app_no=NVL(p_app_no ,b.app_no) AND
156 a.institute_code = (SELECT df.current_inst_code FROM igs_uc_defaults df
157 WHERE df.system_code = a.system_code) AND
158 a.export_to_oss_status = 'OC' AND
159 a.choice_no = NVL(p_choice_no,a.choice_no)
160 ORDER BY a.choice_no ;
161
162
163 -- Get the unit set code corresponding to the application choice point of entry
164 -- jchin - bug 3691277 and 3691250
165 CURSOR c_unit_set_cd(p_seq_no igs_ps_us_prenr_cfg.sequence_no%TYPE,
166 p_course_cd igs_ps_ofr_opt_unit_set_v.course_cd%TYPE,
167 p_version_number igs_ps_ofr_opt_unit_set_v.crv_version_number%TYPE,
168 p_acad_cal_type igs_ps_ofr_opt_unit_set_v.cal_type%TYPE,
169 p_location_cd igs_ps_ofr_opt_unit_set_v.location_cd%TYPE,
170 p_attendance_mode igs_ps_ofr_opt_unit_set_v.attendance_mode%TYPE,
171 p_attendance_type igs_ps_ofr_opt_unit_set_v.attendance_type%TYPE) IS
172 SELECT US.UNIT_SET_CD,
173 US.VERSION_NUMBER US_VERSION_NUMBER
174 FROM IGS_PS_OFR_UNIT_SET COUS,
175 IGS_PS_OFR_OPT COO,
176 IGS_EN_UNIT_SET US,
177 IGS_EN_UNIT_SET_CAT USC,
178 IGS_PS_US_PRENR_CFG CFG
179 WHERE COUS.COURSE_CD = P_COURSE_CD
180 AND COUS.CRV_VERSION_NUMBER = P_VERSION_NUMBER
181 AND COUS.CAL_TYPE = P_ACAD_CAL_TYPE
182 AND COO.LOCATION_CD = P_LOCATION_CD
183 AND COO.ATTENDANCE_MODE = P_ATTENDANCE_MODE
184 AND COO.ATTENDANCE_TYPE = P_ATTENDANCE_TYPE
185 AND COO.COURSE_CD = COUS.COURSE_CD
186 AND COO.VERSION_NUMBER = COUS.CRV_VERSION_NUMBER
187 AND COO.CAL_TYPE = COUS.CAL_TYPE
188 AND US.UNIT_SET_CD = COUS.UNIT_SET_CD
189 AND US.VERSION_NUMBER = COUS.US_VERSION_NUMBER
190 AND US.UNIT_SET_CAT = USC.UNIT_SET_CAT
191 AND USC.S_UNIT_SET_CAT ='PRENRL_YR'
192 AND US.UNIT_SET_CD = CFG.UNIT_SET_CD
193 AND CFG.SEQUENCE_NO = P_SEQ_NO
194 AND NOT EXISTS (SELECT COURSE_CD FROM IGS_PS_OF_OPT_UNT_ST COOUS WHERE COOUS.COO_ID = COO.COO_ID)
195 UNION ALL
196 SELECT US.UNIT_SET_CD,
197 US.VERSION_NUMBER US_VERSION_NUMBER
198 FROM IGS_PS_OF_OPT_UNT_ST COOUS,
199 IGS_EN_UNIT_SET US,
200 IGS_EN_UNIT_SET_CAT USC,
201 IGS_PS_US_PRENR_CFG CFG
202 WHERE COOUS.COURSE_CD = P_COURSE_CD
203 AND COOUS.CRV_VERSION_NUMBER = P_VERSION_NUMBER
204 AND COOUS.CAL_TYPE = P_ACAD_CAL_TYPE
205 AND COOUS.LOCATION_CD = P_LOCATION_CD
206 AND COOUS.ATTENDANCE_MODE = P_ATTENDANCE_MODE
207 AND COOUS.ATTENDANCE_TYPE = P_ATTENDANCE_TYPE
208 AND US.UNIT_SET_CD = COOUS.UNIT_SET_CD
209 AND US.VERSION_NUMBER = COOUS.US_VERSION_NUMBER
210 AND US.UNIT_SET_CAT = USC.UNIT_SET_CAT
211 AND USC.S_UNIT_SET_CAT ='PRENRL_YR'
212 AND US.UNIT_SET_CD = CFG.UNIT_SET_CD
213 AND CFG.SEQUENCE_NO = P_SEQ_NO;
214 c_unit_set_cd_rec c_unit_set_cd%ROWTYPE;
215
216 -- Get the oss POP to which ucas program is mapped to
217 -- smaddali modified cursor to add parameter cp_system_code and its check in where clause ,for bug 2643048
218 CURSOR cur_oss_prog_mapped (cp_ucas_program_code igs_uc_crse_dets.ucas_program_code%TYPE,
219 cp_ucas_campus igs_uc_crse_dets.ucas_campus%TYPE,
220 cp_system_code igs_uc_crse_dets.system_code%TYPE ) IS
221 SELECT cr.oss_program_code, cr.oss_program_version, cr.oss_location, cr.oss_attendance_type,
222 cr.oss_attendance_mode
223 FROM igs_uc_crse_dets cr
224 WHERE cr.ucas_program_code = cp_ucas_program_code
225 AND cr.ucas_campus = cp_ucas_campus
226 AND cr.institute = (SELECT current_inst_code FROM igs_uc_defaults df WHERE df.system_code = cr.system_code)
227 AND cr.system_code = cp_system_code
228 AND cr.oss_program_code IS NOT NULL
229 AND cr.oss_location IS NOT NULL;
230 oss_prog_mapped_rec cur_oss_prog_mapped%ROWTYPE;
231
232 -- Get the application choice details for updating error code,request id and export status fields
233 CURSOR cur_app_choices(cp_appno igs_uc_app_choices.app_no%TYPE,
234 cp_choiceno igs_uc_app_choices.choice_no%TYPE,
235 cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE ) IS
236 SELECT a.ROWID, a.*
237 FROM igs_uc_app_choices a
238 WHERE a.app_no = cp_appno
239 AND a.choice_no = cp_choiceno
240 AND a.ucas_cycle = cp_ucas_cycle;
241 app_choices_rec cur_app_choices%ROWTYPE;
242
243 -- smaddali added cursor ,for bug 2643048
244 CURSOR c_defaults( cp_system_code igs_uc_defaults.system_code%TYPE) IS
245 SELECT *
246 FROM igs_uc_defaults def
247 WHERE system_code = cp_system_code;
248 c_defaults_rec c_defaults%ROWTYPE ;
249
250 --Cursor to get the Calendar details for the given System, Entry Month and Entry Year.
251 CURSOR cur_sys_entry_cal_det ( cp_system_code igs_uc_sys_calndrs.system_code%TYPE,
252 cp_entry_year igs_uc_sys_calndrs.entry_year%TYPE,
253 cp_entry_month igs_uc_sys_calndrs.entry_month%TYPE ) IS
254 SELECT sc.aca_cal_type,
255 sc.aca_cal_seq_no,
256 sc.adm_cal_type,
257 sc.adm_cal_seq_no
258 FROM igs_uc_sys_calndrs sc
259 WHERE sc.system_code = cp_system_code
260 AND sc.entry_year = cp_entry_year
261 AND sc.entry_month = cp_entry_month;
262
263 l_sys_entry_cal_det_rec cur_sys_entry_cal_det%ROWTYPE;
264
265 BEGIN
266
267 SAVEPOINT sp_current_person;
268
269 -- Get the person details from oss for the passed applicant
270 c_pe_person_rec := NULL ;
271 OPEN c_pe_person;
272 FETCH c_pe_person INTO c_pe_person_rec;
273 IF c_pe_person%NOTFOUND THEN
274 -- This applicant is not present in OSS ,hence log error and skip this person
275 fnd_message.set_name('IGS','IGS_UC_NO_OSS_PERS');
276 fnd_message.set_token('APP_NO',p_app_no);
277 fnd_file.put_line( fnd_file.LOG ,fnd_message.get );
278 ELSE
279 -- Since person is present in OSS , get his person details to populate person interface tables
280
281 -- Get the interface ID for this person to be used to create record in igs_ad_interface table
282 c_int_id_rec := NULL ;
283 OPEN c_int_id;
284 FETCH c_int_id INTO c_int_id_rec;
285 CLOSE c_int_id;
286
287 -- Create an interface record for this person
288 INSERT INTO igs_ad_interface(person_number,
289 interface_id,
290 batch_id,
291 org_id,
292 source_type_id,
293 surname,
294 middle_name,
295 given_names,
296 sex,
297 title,
298 suffix,
299 pre_name_adjunct,
300 proof_of_insurance,
301 proof_of_immun,
302 birth_dt,
303 preferred_given_name,
304 level_of_qual,
305 military_service_reg,
306 veteran,
307 status,
308 record_status,
309 match_ind,
310 person_id,
311 created_by,
312 creation_date,
313 last_updated_by,
314 last_update_date,
315 request_id,
316 program_application_id,
317 program_id,
318 program_update_date )
319 VALUES(c_pe_person_rec.person_number,
320 c_int_id_rec.int_id,
321 p_batch_id,
322 p_orgid,
323 p_source_type_id,
324 NVL(c_pe_person_rec.surname,' '),
325 c_pe_person_rec.middle_name,
326 NVL(c_pe_person_rec.given_names,' '),
327 c_pe_person_rec.sex,
328 c_pe_person_rec.title,
329 c_pe_person_rec.suffix,
330 c_pe_person_rec.pre_name_adjunct,
331 c_pe_person_rec.proof_of_ins,
332 c_pe_person_rec.proof_of_immu,
333 c_pe_person_rec.birth_dt,
334 c_pe_person_rec.preferred_given_name,
335 c_pe_person_rec.level_of_qual_id,
336 c_pe_person_rec.military_service_reg,
337 c_pe_person_rec.veteran,
338 l_status,
339 l_record_status,
340 '15',
341 c_pe_person_rec.person_id,
342 l_created_by,
343 SYSDATE,
344 l_last_updated_by,
345 SYSDATE,
346 l_request_id,
347 l_program_application_id,
348 l_program_id,
349 SYSDATE );
350
351 -- Get the application choice details belonging to the passed app_no and choice_no parameters.
352 -- For each application choice record create records in apl_int and appl_inst_int tables
353 FOR j IN c_uc_app_ch
354 LOOP
355 -- initialise all the local variables
356 l_aca_cal_type := NULL ;
357 l_aca_seq_no := NULL ;
358 l_adm_cal_type := NULL ;
359 l_adm_seq_no := NULL ;
360
361 -- smaddali added this cursor code to get the default set up for the application choice system code
362 -- Get the default UCAS setup values and keep them in package variable c_defaults_rec
363 c_defaults_rec := NULL ;
364 OPEN c_defaults(j.system_code) ;
365 FETCH c_defaults INTO c_defaults_rec;
366 CLOSE c_defaults ;
367
368 --Get the Calendar details for the given System, Entry Month and Entry Year from System Calendards table.
369 l_sys_entry_cal_det_rec := NULL;
370 OPEN cur_sys_entry_cal_det(j.system_code, j.entry_year, j.entry_month);
371 FETCH cur_sys_entry_cal_det INTO l_sys_entry_cal_det_rec;
372 --If no matching Entry Year and Entry Month record for the system is found in the System Calendars table then
373 -- get the calendar details from the IGS_UC_SYS_CALNDRS table based on the system, Entry Year and Entry Month as 0 (Zero).
374 IF cur_sys_entry_cal_det%NOTFOUND THEN
375 CLOSE cur_sys_entry_cal_det;
376 OPEN cur_sys_entry_cal_det(j.system_code, j.entry_year, 0);
377 FETCH cur_sys_entry_cal_det INTO l_sys_entry_cal_det_rec;
378 END IF;
379 CLOSE cur_sys_entry_cal_det;
380
381 -- Find out the oss POP to which this choice record's ucas program is mapped to
382 IF j.oss_program_code IS NULL OR j.oss_location IS NULL OR j.oss_attendance_type IS NULL OR
383 j.oss_attendance_mode IS NULL OR j.oss_attendance_type IS NULL THEN
384
385 OPEN cur_oss_prog_mapped(j.ucas_program_code, j.campus, j.system_code) ;
386 FETCH cur_oss_prog_mapped INTO oss_prog_mapped_rec ;
387 IF cur_oss_prog_mapped%FOUND THEN
388 --Populate the OSS code mapping done in IGSUC013 Form from the IGS_UC_CRSE_DETS Table.
389 j.oss_program_code := oss_prog_mapped_rec.oss_program_code;
390 j.oss_program_version := oss_prog_mapped_rec.oss_program_version;
391 j.oss_location := oss_prog_mapped_rec.oss_location;
392 j.oss_attendance_mode := oss_prog_mapped_rec.oss_attendance_mode;
393 j.oss_attendance_type := oss_prog_mapped_rec.oss_attendance_type;
394 END IF;
395 CLOSE cur_oss_prog_mapped;
396 END IF;
397
398 -- If ucas program of this choice is not mapped to any oss pop then log an error and skip the choice
399 IF j.oss_program_code IS NULL OR j.oss_location IS NULL OR j.oss_attendance_mode IS NULL OR j.oss_attendance_type IS NULL THEN
400 fnd_message.set_name('IGS','IGS_UC_NO_OSS_PROG_MAPPED');
401 fnd_message.set_token('APP_NO',TO_CHAR(j.app_no));
402 fnd_message.set_token('CHOICE_NO',TO_CHAR(j.choice_no));
403 fnd_message.set_token('SYSTEM_CODE',j.system_code);
404 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
405
406 -- set the choice record error code to A002 and export status to OC to indicate that
407 -- import interface tables have not been successfully populated but an error was encountered
408 l_ch_error := 'A002' ;
409 l_export_to_oss_status := 'OC' ;
410 l_ch_batch_id := NULL ;
411
412 ELSE
413
414 -- Get the academic and admission calendars for the application choice
415 l_aca_cal_type := l_sys_entry_cal_det_rec.aca_cal_type ;
416 l_aca_seq_no := l_sys_entry_cal_det_rec.aca_cal_seq_no;
417 l_adm_cal_type := l_sys_entry_cal_det_rec.adm_cal_type;
418 l_adm_seq_no := l_sys_entry_cal_det_rec.adm_cal_seq_no ;
419
420 -- Get the Unit set cd corresponding to the application choice point of entry and POP
421 c_unit_set_cd_rec := NULL ;
422 OPEN c_unit_set_cd(NVL(j.point_of_entry,1),
423 j.oss_program_code,
424 j.oss_program_version,
425 l_aca_cal_type,
426 j.oss_location,
427 j.oss_attendance_mode,
428 j.oss_attendance_type );
429 FETCH c_unit_set_cd INTO c_unit_set_cd_rec;
430
431 IF c_unit_set_cd%NOTFOUND THEN
432 -- If the point of entry doesnot correspond to a valid Unit set cd then log error and skip the choice
433 CLOSE c_unit_set_cd;
434 fnd_message.set_name('IGS','IGS_UC_NO_UNIT_SET_CD');
435 fnd_message.set_token('APP_NO',TO_CHAR(j.app_no));
436 fnd_message.set_token('CHOICE_NO',TO_CHAR(j.choice_no));
437 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
438 -- set the choice record error code to A003 and export status to OC to indicate that
439 -- import interface tables have not been successfully populated but an error was encountered
440 l_ch_error := 'A003' ;
441 l_export_to_oss_status := 'OC' ;
442 l_ch_batch_id := NULL ;
443 ELSE
444 CLOSE c_unit_set_cd;
445
446 -- Check if this application choice has been exported previously ,
447 -- i.e if an APPLICATION already exists for this choice . If so then update that application
448 -- Else create a new application and application instance
449 c_match_adm_appl_rec := NULL ;
450 OPEN c_match_adm_appl(j.app_no , j.choice_no) ;
451 FETCH c_match_adm_appl INTO c_match_adm_appl_rec ;
452 IF c_match_adm_appl%NOTFOUND THEN
453 -- new application is being created by this choice
454 l_update_adm_appl_number := NULL ;
455 ELSE
456 -- existing application instance needs to be updated
457 l_update_adm_appl_number := c_match_adm_appl_rec.admission_appl_number ;
458 END IF ;
459 CLOSE c_match_adm_appl ;
460
461 -- Populate the admission application interface table for the application choice
462 -- get the application interface ID
463 c_int_appl_id_rec := NULL ;
464 OPEN c_int_appl_id;
465 FETCH c_int_appl_id INTO c_int_appl_id_rec;
466 CLOSE c_int_appl_id;
467
468 -- smaddali added new columns admission_application_type and alt_appl_id for bug 2643048
469 INSERT INTO igs_ad_apl_int
470 ( interface_appl_id
471 ,interface_id
472 ,appl_dt
473 ,acad_cal_type
474 ,acad_ci_sequence_number
475 ,adm_cal_type
476 ,adm_ci_sequence_number
477 ,tac_appl_ind
478 ,status
479 ,created_by
480 ,creation_date
481 ,last_updated_by
482 ,last_update_date
483 ,choice_number
484 ,routeb_pref
485 ,update_adm_appl_number
486 ,admission_application_type
487 ,alt_appl_id
488 )
489 VALUES( c_int_appl_id_rec.int_appl_id,
490 c_int_id_rec.int_id,
491 NVL(c_pe_person_rec.application_date,SYSDATE),
492 l_aca_cal_type,
493 l_aca_seq_no,
494 l_adm_cal_type,
495 l_adm_seq_no,
496 'N',
497 l_status,
498 l_created_by,
499 SYSDATE,
500 l_last_updated_by,
501 SYSDATE,
502 j.choice_no,
503 j.route_b_pref_round,
504 l_update_adm_appl_number,
505 c_defaults_rec.application_type,
506 TO_CHAR(j.app_no)
507 );
508
509 -- Populate the application instance interface tables
510 -- Get the application instance interface ID
511 appl_inst_int_id_rec := NULL ;
512 OPEN c_appl_inst_int_id;
513 FETCH c_appl_inst_int_id INTO appl_inst_int_id_rec;
514 CLOSE c_appl_inst_int_id;
515 -- Get the application source code
516 l_code_id := NULL ;
517 OPEN cur_code_id;
518 FETCH cur_code_id INTO l_code_id;
519 CLOSE cur_code_id;
520
521 -- Check if this application choice has been exported previously ,
522 -- i.e if an APPLICATION INSTANCE already exists for this choice . If so then update that application instance
523 -- Else create a new application instance
524 c_match_adm_appl_inst_rec := NULL ;
525 OPEN c_match_adm_appl_inst ( c_match_adm_appl_rec.person_id,
526 c_match_adm_appl_rec.admission_appl_number,
527 j.oss_program_code,
528 j.oss_program_version,
529 j.oss_location,
530 j.oss_attendance_mode,
531 j.oss_attendance_type,
532 c_unit_set_cd_rec.unit_set_cd,
533 c_unit_set_cd_rec.us_version_number ) ;
534 FETCH c_match_adm_appl_inst INTO c_match_adm_appl_inst_rec ;
535 IF c_match_adm_appl_inst%NOTFOUND THEN
536 -- new application instance is being created by this choice
537 l_update_adm_seq_number := NULL ;
538 ELSE
539 -- existing application instance needs to be updated
540 l_update_adm_seq_number := c_match_adm_appl_inst_rec.sequence_number ;
541 END IF ;
542 CLOSE c_match_adm_appl_inst ;
543
544 INSERT INTO igs_ad_ps_appl_inst_int
545 ( interface_appl_id
546 ,interface_ps_appl_inst_id
547 ,nominated_course_cd
548 ,req_for_adv_standing_ind
549 ,app_source_id
550 ,crv_version_number
551 ,location_cd
552 ,attendance_mode
553 ,attendance_type
554 ,preference_number
555 ,unit_set_cd
556 ,us_version_number
557 ,status
558 ,created_by
559 ,creation_date
560 ,last_updated_by
561 ,last_update_date
562 ,update_adm_seq_number
563 )
564 VALUES ( c_int_appl_id_rec.int_appl_id,
565 appl_inst_int_id_rec.appl_inst_int_id,
566 j.oss_program_code,
567 'N',
568 DECODE(j.application_source, 'U',l_code_id,NULL),
569 j.oss_program_version,
570 j.oss_location,
571 j.oss_attendance_mode,
572 j.oss_attendance_type,
573 1,
574 c_unit_set_cd_rec.unit_set_cd,
575 c_unit_set_cd_rec.us_version_number,
576 l_status,
577 l_created_by,
578 SYSDATE,
579 l_last_updated_by,
580 SYSDATE,
581 l_update_adm_seq_number
582 );
583
584 -- set the choice record error code to null and export status to AP to indicate that
585 -- import interface tables have been successfully populated
586 l_ch_error := NULL ;
587 l_ch_batch_id := NULL ;
588 l_export_to_oss_status := 'AP' ;
589
590 END IF; -- c_unit_set_cd%NOTFOUND Conditon
591
592 END IF; -- j.oss_program_code IS NULL OR j.choice_no IS NULL Conditon
593
594
595 -- Update the application choice record with the oss POP mapped to ucas program
596 -- and to set export_to_oss_status = AP / OC , error code , request id
597 -- depending on whether admission application interface tables have been successfully populted or not
598 app_choices_rec := NULL ;
599 OPEN cur_app_choices(j.app_no, j.choice_no, j.ucas_cycle);
600 FETCH cur_app_choices INTO app_choices_rec;
601 CLOSE cur_app_choices;
602
603 igs_uc_app_choices_pkg.update_row
604 ( x_rowid => app_choices_rec.ROWID
605 ,x_app_choice_id => app_choices_rec.app_choice_id
606 ,x_app_id => app_choices_rec.app_id
607 ,x_app_no => app_choices_rec.app_no
608 ,x_choice_no => app_choices_rec.choice_no
609 ,x_last_change => app_choices_rec.last_change
610 ,x_institute_code => app_choices_rec.institute_code
611 ,x_ucas_program_code => app_choices_rec.ucas_program_code
612 ,x_oss_program_code => j.oss_program_code
613 ,x_oss_program_version => j.oss_program_version
614 ,x_oss_attendance_type => j.oss_attendance_type
615 ,x_oss_attendance_mode => j.oss_attendance_mode
616 ,x_campus => app_choices_rec.campus
617 ,x_oss_location => j.oss_location
618 ,x_faculty => app_choices_rec.faculty
619 ,x_entry_year => app_choices_rec.entry_year
620 ,x_entry_month => app_choices_rec.entry_month
621 ,x_point_of_entry => app_choices_rec.point_of_entry
622 ,x_home => app_choices_rec.home
623 ,x_deferred => app_choices_rec.deferred
624 ,x_route_b_pref_round => app_choices_rec.route_b_pref_round
625 ,x_route_b_actual_round => app_choices_rec.route_b_actual_round
626 ,x_condition_category => app_choices_rec.condition_category
627 ,x_condition_code => app_choices_rec.condition_code
628 ,x_decision => app_choices_rec.decision
629 ,x_decision_date => app_choices_rec.decision_date
630 ,x_decision_number => app_choices_rec.decision_number
631 ,x_reply => app_choices_rec.reply
632 ,x_summary_of_cond => app_choices_rec.summary_of_cond
633 ,x_choice_cancelled => app_choices_rec.choice_cancelled
634 ,x_action => app_choices_rec.action
635 ,x_substitution => app_choices_rec.substitution
636 ,x_date_substituted => app_choices_rec.date_substituted
637 ,x_prev_institution => app_choices_rec.prev_institution
638 ,x_prev_course => app_choices_rec.prev_course
639 ,x_prev_campus => app_choices_rec.prev_campus
640 ,x_ucas_amendment => app_choices_rec.ucas_amendment
641 ,x_withdrawal_reason => app_choices_rec.withdrawal_reason
642 ,x_offer_course => app_choices_rec.offer_course
643 ,x_offer_campus => app_choices_rec.offer_campus
644 ,x_offer_crse_length => app_choices_rec.offer_crse_length
645 ,x_offer_entry_month => app_choices_rec.offer_entry_month
646 ,x_offer_entry_year => app_choices_rec.offer_entry_year
647 ,x_offer_entry_point => app_choices_rec.offer_entry_point
648 ,x_offer_text => app_choices_rec.offer_text
649 ,x_export_to_oss_status => l_export_to_oss_status
650 ,x_error_code => l_ch_error
651 ,x_batch_id => l_ch_batch_id
652 ,x_request_id => l_conc_request_id
653 ,x_mode => 'R'
654 ,x_extra_round_nbr => app_choices_rec.extra_round_nbr
655 ,x_system_code => app_choices_rec.system_code
656 ,x_part_time => app_choices_rec.part_time
657 ,x_interview => app_choices_rec.interview
658 ,x_late_application => app_choices_rec.late_application
659 ,x_modular => app_choices_rec.modular
660 ,x_residential => app_choices_rec.residential
661 ,x_ucas_cycle => app_choices_rec.ucas_cycle);
662 END LOOP ; -- loop for application choice records
663
664 END IF; -- person details not found in OSS for the passed ucas application
665 CLOSE c_pe_person;
666
667
668 EXCEPTION
669 WHEN OTHERS THEN
670 ROLLBACK TO sp_current_person;
671 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
672 fnd_message.set_token('NAME','IGS_UCAS_EXPORT_TO_OSS.POPULATE_IMP_INT'||' - '||SQLERRM);
673 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
674 IGS_GE_MSG_STACK.ADD;
675 App_Exception.Raise_Exception;
676
677 END populate_imp_int;
678
679
680 PROCEDURE import_process(
681 p_source_type_id igs_pe_src_types_all.source_type_id%TYPE,
682 p_batch_id NUMBER,
683 p_orgid NUMBER
684 ) IS
685
686 /******************************************************************
687 Created By : smaddali
688 Date Created By : 12-sep-2002
689 Purpose : Submit the concurrent request for admission application import process
690 Known limitations,enhancements,remarks:
691 Change History
692 Who When What
693 --smaddali initialising l_rowid to null for bug 2626178
694 -- smaddali modified this procedure for bug 2643048 UCFD102 build , to remove code creating admission
695 -- interface ctl record
696 ***************************************************************** */
697
698 l_row_id VARCHAR2(26);
699
700 CURSOR cur_match_set IS
701 SELECT match_set_id
702 FROM igs_pe_match_sets
703 WHERE source_type_id = p_source_type_id;
704 match_set_rec cur_match_set%ROWTYPE;
705
706 l_interface_run_id igs_ad_interface_ctl.interface_run_id%TYPE;
707 l_errbuff VARCHAR2(100) ;
708 l_retcode NUMBER ;
709
710
711 BEGIN
712
713 -- Get the match set criteria corresponding to the ucas source type to be used for the person import
714 match_set_rec := NULL ;
715 OPEN cur_match_set;
716 FETCH cur_match_set INTO match_set_rec;
717 CLOSE cur_match_set;
718
719 l_interface_run_id := NULL ;
720 l_errbuff:= NULL ;
721 l_retcode := NULL ;
722 -- Call admission application import process procedure because current process has to wait until import process is finished
723 IGS_AD_IMP_001.IMP_ADM_DATA ( errbuf => l_errbuff,
724 retcode => l_retcode ,
725 p_batch_id => p_batch_id,
726 p_source_type_id => p_source_type_id,
727 p_match_set_id => match_set_rec.match_set_id,
728 p_acad_cal_type => NULL ,
729 p_acad_sequence_number => NULL ,
730 p_adm_cal_type => NULL ,
731 p_adm_sequence_number => NULL ,
732 p_admission_cat => NULL ,
733 p_s_admission_process_type => NULL ,
734 p_interface_run_id => l_interface_run_id ,
735 P_org_id => NULL ) ;
736
737
738 EXCEPTION
739 WHEN OTHERS THEN
740 -- even though the admission import process completes in error , this process should continue processing
741 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
742 fnd_message.set_token('NAME','IGS_UCAS_EXPORT_TO_OSS.IMPORT_PROCESS'||' - '||SQLERRM);
743 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
744
745 END import_process;
746
747 PROCEDURE obsolete_applications(
748 p_app_no igs_uc_applicants.app_no%TYPE ,
749 p_choice_no igs_uc_app_choices.choice_no%TYPE
750 ) IS
751 /******************************************************************
752 Created By : smaddali
753 Date Created By : 12-SEP-2002
754 Purpose : To obsolete old applications when the
755 choice record will result in a new application/instance
756 Known limitations,enhancements,remarks:
757 Change History
758 Who When What
759 smaddali initialising l_rowid to null for bug 2626178
760 smaddali checking if record exists before inserting into igs_uc_old_oustat if so then update ,
761 else insert. Also added code to get unit set cd for point of entry and use it in finding
762 matching admissions application, for bug 2630219
763 ayedubat 24-MAR-2003 Added the Logic to identify the Applications Choices modified by UCAS which already went UF
764 and institution asking for change in Course, Entry Month, Entry Year or Point of Entry for
765 the Bug, 2669209
766 rbezawad 7-Oct-03 Added a validation before setting the export_to_oss status to 'UF' which will check for
767 ucas decision is 'U' or 'A' and the reply is 'F'. Bug: 3179630
768 jchakrab 10-Oct-2005 Modified for 4424068 - added CANCEL functionality for prog version change
769 jchin 20-jan-2006 Modified for R12 Perf improvements - bug 3691277 and 3691250
770 jchakrab 04-May-2006 Modified for 5203018 - modified to close c_cancel_appl cursor correctly
771 jchakrab 22-May-2006 Modified for 5165624
772 ***************************************************************** */
773
774 l_description igs_ad_batc_def_det_all.description%TYPE ;
775 l_rowid VARCHAR2(50) ;
776
777 l_batch_id igs_ad_batc_def_det_all.batch_id%TYPE ;
778
779 l_appl_batch_id NUMBER;
780 l_dec_batch_id NUMBER;
781 l_aca_cal_type igs_uc_sys_calndrs.aca_cal_type%TYPE ;
782 l_aca_seq_no igs_uc_sys_calndrs.aca_cal_seq_no%TYPE;
783 l_adm_cal_type igs_uc_sys_calndrs.adm_cal_type%TYPE ;
784 l_adm_seq_no igs_uc_sys_calndrs.adm_cal_seq_no%TYPE ;
785 l_interface_mkdes_id NUMBER ;
786 l_count NUMBER ;
787 l_new_appl BOOLEAN ;
788 l_new_appl_inst BOOLEAN ;
789 l_cancel_appl BOOLEAN; -- added for bug 4424068
790 l_ch_error igs_uc_app_choices.error_code%TYPE ;
791 l_ch_batch_id igs_uc_app_choices.batch_id%TYPE ;
792 l_export_to_oss_status igs_uc_app_choices.export_to_oss_status%TYPE ;
793 l_error_message fnd_new_messages.message_text%TYPE; -- Bug 3297241
794 l_return_status VARCHAR2(100) ;
795 l_pref_excep BOOLEAN ;
796
797 -- Get all valid application choices with status NEW belonging to current institution
798 -- smaddali modified this cursor to add new field system_code in select clause ,
799 -- and check for system_code for bug 2630219
800 CURSOR c_new_app_ch IS
801 SELECT ch.app_no , ch.choice_no , ch.deferred , ch.batch_id , ch.export_to_oss_status ,
802 ch.point_of_entry , ch.oss_program_code, ch.oss_location ,ch.oss_program_version ,
803 ch.oss_attendance_type ,ch.oss_attendance_mode , ch.system_code, ch.ucas_cycle,
804 ch.entry_year, ch.entry_month, ch.decision, ch.reply
805 FROM igs_uc_app_choices ch
806 WHERE ch.app_no = NVL(p_app_no,ch.app_no) AND
807 ch.export_to_oss_status = 'NEW' AND
808 ch.choice_no = NVL(p_choice_no , ch.choice_no) AND
809 ch.institute_code = (SELECT df.current_inst_code FROM igs_uc_defaults df
810 WHERE df.system_code = ch.system_code)
811 ORDER BY ch.ucas_cycle, ch.app_no, ch.choice_no;
812
813 -- Get all valid application choices with status OO belonging to current institution
814 -- smaddali modified this cursor to add check for system_code ,bug 2643048 UCFD102 build
815 CURSOR c_oo_ch IS
816 SELECT ch.batch_id, ch.deferred , ch.app_no , ch.choice_no , ch.system_code, ch.ucas_cycle, ch.entry_year, ch.entry_month
817 FROM igs_uc_app_choices ch
818 WHERE ch.app_no = NVL(p_app_no,ch.app_no) AND
819 ch.export_to_oss_status ='OO' AND
820 ch.choice_no = NVL(p_choice_no , ch.choice_no) AND
821 ch.institute_code = (SELECT df.current_inst_code FROM igs_uc_defaults df
822 WHERE df.system_code = ch.system_code)
823 ORDER BY ch.ucas_cycle, ch.app_no, ch.choice_no;
824
825 -- Get the admission application corresponding to the passed application choice
826 -- i.e if this choice has been previously exported to oss or not
827 -- smaddali modified this cursor to add check for alt_appl_id ,bug 2643048 UCFD102 build
828 CURSOR c_adm_appl( cp_app_no igs_uc_app_choices.app_no%TYPE,
829 cp_choice_no igs_uc_app_choices.choice_no%TYPE ) IS
830 SELECT c.person_id , c.admission_appl_number
831 FROM igs_uc_applicants a , igs_uc_app_choices b , igs_ad_appl_all c
832 WHERE a.app_no = b.app_no AND
833 a.oss_person_id = c.person_id AND
834 TO_CHAR(a.app_no) = c.alt_appl_id AND
835 b.choice_no = c.choice_number AND
836 b.app_no = cp_app_no AND
837 b.choice_no = cp_choice_no ;
838 c_adm_appl_rec c_adm_appl%ROWTYPE ;
839
840 -- Get the admission application number of the application corresponding to the passed application choice
841 -- smaddali modified this cursor to add check for alt_appl_id ,bug 2643048 UCFD102 build
842 CURSOR c_match_adm_appl(cp_app_no igs_uc_app_choices.app_no%TYPE,
843 cp_choice_no igs_uc_app_choices.choice_no%TYPE ) IS
844 SELECT c.person_id,c.admission_appl_number ,b.choice_no
845 FROM igs_uc_applicants a , igs_uc_app_choices b , igs_ad_appl_all c
846 WHERE a.app_no = b.app_no AND
847 a.oss_person_id = c.person_id AND
848 TO_CHAR(a.app_no) = c.alt_appl_id AND
849 b.choice_no = c.choice_number AND
850 c.acad_cal_type = l_aca_cal_type AND
851 c.acad_ci_sequence_number = l_aca_seq_no AND
852 c.adm_cal_type = l_adm_cal_type AND
853 c.adm_ci_sequence_number = l_adm_seq_no AND
854 b.app_no = cp_app_no AND
855 b.choice_no = cp_choice_no AND
856 igs_ad_gen_007.Admp_Get_Saas(c.adm_appl_status) <> 'COMPLETED';
857 c_match_adm_appl_rec c_match_adm_appl%ROWTYPE ;
858
859 -- Get the admission application instance corresponding to the passed application choice
860 -- smaddali added unit set cd and version number parameters and in where clause using these parameters ,bug2630219
861 -- smaddali modified this cursor to add check for alt_appl_id ,bug 2643048 UCFD102 build
862 CURSOR c_adm_appl_inst (cp_app_no igs_uc_app_choices.app_no%TYPE,
863 cp_choice_no igs_uc_app_choices.choice_no%TYPE,
864 cp_adm_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE ,
865 cp_unit_set_cd igs_ad_ps_appl_inst.unit_set_cd%TYPE ,
866 cp_us_version_number igs_ad_ps_appl_inst.us_version_number%TYPE ) IS
867 SELECT 'X'
868 FROM igs_uc_applicants a , igs_uc_app_choices b , igs_ad_appl_all c,
869 igs_ad_ps_appl_all d , igs_ad_ps_appl_inst_all e
870 WHERE a.app_no = b.app_no AND
871 a.oss_person_id = c.person_id AND
872 TO_CHAR(a.app_no) = c.alt_appl_id AND
873 b.choice_no = c.choice_number AND
874 c.person_id = d.person_id AND
875 c.admission_appl_number = cp_adm_appl_number AND
876 c.admission_appl_number = d.admission_appl_number AND
877 d.person_id = e.person_id AND
878 d.admission_appl_number = e.admission_appl_number AND
879 d.nominated_course_cd = e.nominated_course_cd AND
880 b.oss_program_code = e.course_cd AND
881 b.oss_program_version = e.crv_version_number AND
882 b.oss_location = e.location_cd AND
883 b.oss_attendance_type = e.attendance_type AND
884 b.oss_attendance_mode = e.attendance_mode AND
885 e.unit_set_cd = cp_unit_set_cd AND
886 e.us_version_number = cp_us_version_number AND
887 b.app_no = cp_app_no AND
888 b.choice_no = cp_choice_no
889 ORDER BY e.preference_number ASC ;
890 c_adm_appl_inst_rec c_adm_appl_inst%ROWTYPE ;
891
892 -- Get the admission application instance corresponding to the passed application choice
893 -- for bug 4424068 - need to check if the oss-program-version has changed for the application
894 -- if the only change in the new choice record is the program version number
895 -- and the current application status is 'RECEIVED', and current outcome status is 'PENDING'/'WITHDRAWN'
896
897 CURSOR c_cancel_appl (cp_app_no igs_uc_app_choices.app_no%TYPE,
898 cp_choice_no igs_uc_app_choices.choice_no%TYPE,
899 cp_adm_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE ,
900 cp_unit_set_cd igs_ad_ps_appl_inst.unit_set_cd%TYPE ,
901 cp_us_version_number igs_ad_ps_appl_inst.us_version_number%TYPE ) IS
902 SELECT
903 c.person_id , c.admission_appl_number ,c.choice_number, e.sequence_number,
904 e.decision_date , e.decision_reason_id , e.decision_make_id ,e.adm_outcome_status ,
905 e.nominated_course_cd , c.adm_cal_type , c.adm_ci_sequence_number,
906 c.acad_cal_type , c.acad_ci_sequence_number ,c.admission_cat ,c.s_admission_process_type
907 FROM igs_uc_applicants a , igs_uc_app_choices b , igs_ad_appl_all c,
908 igs_ad_ps_appl_all d , igs_ad_ps_appl_inst_all e
909 WHERE a.app_no = b.app_no AND
910 a.oss_person_id = c.person_id AND
911 TO_CHAR(a.app_no) = c.alt_appl_id AND
912 b.choice_no = c.choice_number AND
913 c.person_id = d.person_id AND
914 c.admission_appl_number = cp_adm_appl_number AND
915 c.admission_appl_number = d.admission_appl_number AND
916 d.person_id = e.person_id AND
917 d.admission_appl_number = e.admission_appl_number AND
918 d.nominated_course_cd = e.nominated_course_cd AND
919 b.oss_program_code = e.course_cd AND
920 b.oss_program_version <> e.crv_version_number AND
921 b.oss_location = e.location_cd AND
922 b.oss_attendance_type = e.attendance_type AND
923 b.oss_attendance_mode = e.attendance_mode AND
924 e.unit_set_cd = cp_unit_set_cd AND
925 e.us_version_number = cp_us_version_number AND
926 b.app_no = cp_app_no AND
927 b.choice_no = cp_choice_no AND
928 igs_ad_gen_007.Admp_Get_Saas(c.adm_appl_status) = 'RECEIVED' AND
929 igs_ad_gen_008.Admp_Get_Saos(e.adm_outcome_status) IN ('PENDING','SUSPEND');
930 c_cancel_appl_rec c_cancel_appl%ROWTYPE;
931
932
933 -- Get the interface ctl ID
934 CURSOR c_interface_run_id IS
935 SELECT igs_ad_interface_ctl_s.NEXTVAL
936 FROM dual ;
937 l_interface_run_id NUMBER ;
938
939 -- Get the application to be voided ,i.e the currently active application instance
940 -- smaddali modified this cursor to add check for alt_appl_id ,bug 2643048 UCFD102 build
941 CURSOR c_obsol_appl_cnt(cp_app_no igs_uc_applicants.app_no%TYPE ,
942 cp_choice_number igs_ad_appl_all.choice_number%TYPE) IS
943 SELECT COUNT(*)
944 FROM igs_uc_applicants a , igs_ad_appl_all c, igs_ad_ps_appl_all d ,
945 igs_ad_ps_appl_inst_all e , igs_ad_ou_stat ou
946 WHERE a.app_no = cp_app_no AND
947 a.oss_person_id = c.person_id AND
948 TO_CHAR(a.app_no) = c.alt_appl_id AND
949 c.choice_number = cp_choice_number AND
950 c.person_id = d.person_id AND
951 c.admission_appl_number = d.admission_appl_number AND
952 d.person_id = e.person_id AND
953 d.admission_appl_number = e.admission_appl_number AND
954 d.nominated_course_cd = e.nominated_course_cd AND
955 e.adm_outcome_status = ou.adm_outcome_status AND
956 ou.s_adm_outcome_status NOT IN ('SUSPEND','VOIDED','WITHDRAWN');
957 l_obsol_appl_cnt NUMBER;
958
959 -- Get the application to be voided ,i.e the currently active application instance
960 -- smaddali modified this cursor to add check for alt_appl_id ,bug 2643048 UCFD102 build
961 CURSOR c_obsol_appl(cp_app_no igs_uc_applicants.app_no%TYPE ,
962 cp_choice_number igs_ad_appl_all.choice_number%TYPE) IS
963 SELECT c.person_id , c.admission_appl_number ,c.choice_number, e.sequence_number,
964 e.decision_date , e.decision_reason_id , e.decision_make_id ,e.adm_outcome_status ,
965 e.nominated_course_cd , c.adm_cal_type , c.adm_ci_sequence_number,
966 c.acad_cal_type , c.acad_ci_sequence_number ,c.admission_cat ,c.s_admission_process_type
967 FROM igs_uc_applicants a , igs_ad_appl_all c, igs_ad_ps_appl_all d ,
968 igs_ad_ps_appl_inst_all e , igs_ad_ou_stat ou
969 WHERE a.app_no = cp_app_no AND
970 a.oss_person_id = c.person_id AND
971 TO_CHAR(a.app_no) = c.alt_appl_id AND
972 c.choice_number = cp_choice_number AND
973 c.person_id = d.person_id AND
974 c.admission_appl_number = d.admission_appl_number AND
975 d.person_id = e.person_id AND
976 d.admission_appl_number = e.admission_appl_number AND
977 d.nominated_course_cd = e.nominated_course_cd AND
978 e.adm_outcome_status = ou.adm_outcome_status AND
979 ou.s_adm_outcome_status NOT IN ('SUSPEND','VOIDED','WITHDRAWN');
980 c_obsol_appl_rec c_obsol_appl%ROWTYPE ;
981
982 -- Get the application instance details for incrementing preference number
983 CURSOR c_upd_appl_inst ( cp_person_id igs_ad_ps_appl_inst.person_id%TYPE,
984 cp_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE ) IS
985 SELECT a.ROWID , a.*
986 FROM igs_ad_ps_appl_inst_all a
987 WHERE a.person_id = cp_person_id AND
988 a.admission_appl_number = cp_admission_appl_number
989 ORDER BY a.preference_number DESC ;
990
991
992 -- Get the admission decision import interface record for the passed admission application instace.
993 CURSOR c_dec_int( p_batch_id IGS_AD_BATC_DEF_DET_ALL.batch_id%TYPE ,
994 p_person_id IGS_AD_ADMDE_INT.person_id%TYPE,
995 p_admission_appl_number IGS_AD_ADMDE_INT.admission_appl_number%TYPE,
996 p_nominated_course_cd IGS_AD_ADMDE_INT.nominated_course_cd%TYPE,
997 p_sequence_number IGS_AD_ADMDE_INT.sequence_number%TYPE ) IS
998 SELECT error_code , status
999 FROM IGS_AD_ADMDE_INT_ALL
1000 WHERE batch_id = p_batch_id AND
1001 person_id = p_person_id AND
1002 admission_appl_number = p_admission_appl_number AND
1003 nominated_course_cd = p_nominated_course_cd AND
1004 sequence_number = p_sequence_number ;
1005 c_dec_int_rec c_dec_int%ROWTYPE;
1006
1007 --smaddali added this cursor for bug 2630219
1008 -- Check if old outcome status record exists for the passed application choice
1009 CURSOR c_old_oustat ( cp_app_no igs_uc_old_oustat.app_no%TYPE,
1010 cp_choice_no igs_uc_old_oustat.choice_no%TYPE ) IS
1011 SELECT ou.ROWID , ou.*
1012 FROM igs_uc_old_oustat ou
1013 WHERE app_no = cp_app_no AND
1014 choice_no = cp_choice_no ;
1015 c_old_oustat_rec c_old_oustat%ROWTYPE ;
1016
1017 -- Get the unit set code corresponding to the application choice point of entry
1018 -- smaddali added this cursor for bug 2630219
1019 -- jchin - bug 3691277 and 3691250
1020 CURSOR c_unit_set_cd(p_seq_no igs_ps_us_prenr_cfg.sequence_no%TYPE,
1021 p_course_cd igs_ps_ofr_opt_unit_set_v.course_cd%TYPE,
1022 p_version_number igs_ps_ofr_opt_unit_set_v.crv_version_number%TYPE,
1023 p_acad_cal_type igs_ps_ofr_opt_unit_set_v.cal_type%TYPE,
1024 p_location_cd igs_ps_ofr_opt_unit_set_v.location_cd%TYPE,
1025 p_attendance_mode igs_ps_ofr_opt_unit_set_v.attendance_mode%TYPE,
1026 p_attendance_type igs_ps_ofr_opt_unit_set_v.attendance_type%TYPE) IS
1027 SELECT US.UNIT_SET_CD,
1028 US.VERSION_NUMBER US_VERSION_NUMBER
1029 FROM IGS_PS_OFR_UNIT_SET COUS,
1030 IGS_PS_OFR_OPT COO,
1031 IGS_EN_UNIT_SET US,
1032 IGS_EN_UNIT_SET_CAT USC,
1033 IGS_PS_US_PRENR_CFG CFG
1034 WHERE COUS.COURSE_CD = P_COURSE_CD
1035 AND COUS.CRV_VERSION_NUMBER = P_VERSION_NUMBER
1036 AND COUS.CAL_TYPE = P_ACAD_CAL_TYPE
1037 AND COO.LOCATION_CD = P_LOCATION_CD
1038 AND COO.ATTENDANCE_MODE = P_ATTENDANCE_MODE
1039 AND COO.ATTENDANCE_TYPE = P_ATTENDANCE_TYPE
1040 AND COO.COURSE_CD = COUS.COURSE_CD
1041 AND COO.VERSION_NUMBER = COUS.CRV_VERSION_NUMBER
1042 AND COO.CAL_TYPE = COUS.CAL_TYPE
1043 AND US.UNIT_SET_CD = COUS.UNIT_SET_CD
1044 AND US.VERSION_NUMBER = COUS.US_VERSION_NUMBER
1045 AND US.UNIT_SET_CAT = USC.UNIT_SET_CAT
1046 AND USC.S_UNIT_SET_CAT ='PRENRL_YR'
1047 AND US.UNIT_SET_CD = CFG.UNIT_SET_CD
1048 AND CFG.SEQUENCE_NO = P_SEQ_NO
1049 AND NOT EXISTS (SELECT COURSE_CD FROM IGS_PS_OF_OPT_UNT_ST COOUS WHERE COOUS.COO_ID = COO.COO_ID)
1050 UNION ALL
1051 SELECT US.UNIT_SET_CD,
1052 US.VERSION_NUMBER US_VERSION_NUMBER
1053 FROM IGS_PS_OF_OPT_UNT_ST COOUS,
1054 IGS_EN_UNIT_SET US,
1055 IGS_EN_UNIT_SET_CAT USC,
1056 IGS_PS_US_PRENR_CFG CFG
1057 WHERE COOUS.COURSE_CD = P_COURSE_CD
1058 AND COOUS.CRV_VERSION_NUMBER = P_VERSION_NUMBER
1059 AND COOUS.CAL_TYPE = P_ACAD_CAL_TYPE
1060 AND COOUS.LOCATION_CD = P_LOCATION_CD
1061 AND COOUS.ATTENDANCE_MODE = P_ATTENDANCE_MODE
1062 AND COOUS.ATTENDANCE_TYPE = P_ATTENDANCE_TYPE
1063 AND US.UNIT_SET_CD = COOUS.UNIT_SET_CD
1064 AND US.VERSION_NUMBER = COOUS.US_VERSION_NUMBER
1065 AND US.UNIT_SET_CAT = USC.UNIT_SET_CAT
1066 AND USC.S_UNIT_SET_CAT ='PRENRL_YR'
1067 AND US.UNIT_SET_CD = CFG.UNIT_SET_CD
1068 AND CFG.SEQUENCE_NO = P_SEQ_NO;
1069 c_unit_set_cd_rec c_unit_set_cd%ROWTYPE;
1070
1071 -- smaddali added cursors ,for bug 2643048 UCFD102 build
1072 CURSOR c_defaults( cp_system_code igs_uc_defaults.system_code%TYPE) IS
1073 SELECT *
1074 FROM igs_uc_defaults def
1075 WHERE def.system_code = NVL(cp_system_code, def.system_code);
1076 c_defaults_rec c_defaults%ROWTYPE ;
1077
1078 CURSOR c_obs_ou_stat ( cp_out_stat igs_ad_ou_stat.adm_outcome_status%TYPE) IS
1079 SELECT s_Adm_outcome_status
1080 FROM igs_Ad_ou_stat
1081 WHERE adm_outcome_status = cp_out_stat ;
1082 l_s_obsol_ou_stat igs_ad_ou_stat.s_adm_outcome_status%TYPE ;
1083
1084 -- Cursor to fetch the latest Transaction
1085 CURSOR cur_latest_trans( p_app_no igs_uc_app_choices.app_no%TYPE,
1086 p_choice_no igs_uc_app_choices.choice_no%TYPE,
1087 p_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
1088 SELECT transaction_type,program_code,entry_month,entry_year,entry_point
1089 FROM IGS_UC_TRANSACTIONS tran
1090 WHERE tran.app_no = p_app_no
1091 AND tran.choice_no = p_choice_no
1092 AND tran.ucas_cycle = p_ucas_cycle
1093 ORDER BY tran.uc_tran_id DESC;
1094 cur_latest_trans_rec cur_latest_trans%ROWTYPE;
1095
1096 -- Cursor to find whether a Completed OSS Admission Application already exist
1097 -- for the UCAS Application Choice
1098 CURSOR cur_comp_app_choice(p_app_no igs_uc_app_choices.app_no%TYPE ,
1099 p_choice_no igs_uc_app_choices.choice_no%TYPE,
1100 p_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE ) IS
1101 SELECT apl.person_id,apl.admission_appl_number
1102 FROM IGS_UC_APP_CHOICES uac,
1103 IGS_UC_APPLICANTS ua,
1104 IGS_AD_APPL_ALL apl,
1105 IGS_AD_PS_APPL_ALL aplps,
1106 IGS_AD_PS_APPL_INST_ALL aplinst
1107 WHERE uac.app_no = p_app_no
1108 AND uac.choice_no = p_choice_no
1109 AND uac.ucas_cycle= p_ucas_cycle
1110 AND ua.app_no = uac.app_no
1111 AND ua.oss_person_id = apl.person_id
1112 AND TO_CHAR(ua.app_no) = apl.alt_appl_id
1113 AND apl.choice_number = uac.choice_no
1114 AND apl.person_id = aplps.person_id
1115 AND apl.admission_appl_number = aplps.admission_appl_number
1116 AND aplps.person_id = aplinst.person_id
1117 AND aplps.admission_appl_number = aplinst.admission_appl_number
1118 AND aplps.nominated_course_cd = aplinst.nominated_course_cd
1119 AND igs_ad_gen_007.admp_get_saas(apl.adm_appl_status) = 'COMPLETED'
1120 AND igs_ad_gen_008.Admp_Get_Saos(aplinst.adm_outcome_status) <> 'CANCELLED';
1121 cur_comp_app_choice_rec cur_comp_app_choice%ROWTYPE;
1122
1123 -- Cursor to find any change in the UCAS Application Choice with the OSS Admission
1124 -- Application Instance
1125 CURSOR change_in_adm_appl_cur (
1126 cp_app_no igs_uc_app_choices.app_no%TYPE,
1127 cp_choice_no igs_uc_app_choices.choice_no%TYPE,
1128 cp_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE,
1129 cp_person_id igs_pe_person.person_id%TYPE,
1130 cp_admission_appl_number igs_ad_ps_appl_inst.admission_appl_number%TYPE) IS
1131 SELECT
1132 'X'
1133 FROM
1134 igs_uc_app_choices uac,
1135 igs_uc_sys_calndrs ucal,
1136 igs_ad_appl_all apl,
1137 igs_ad_ps_appl_inst_all aplinst,
1138 igs_en_unit_set us,
1139 igs_en_unit_set_cat usc,
1140 igs_ps_us_prenr_cfg cnfg,
1141 igs_uc_map_out_stat mos,
1142 igs_uc_map_off_resp mor
1143 WHERE uac.app_no = cp_app_no
1144 AND uac.choice_no = cp_choice_no
1145 AND uac.ucas_cycle= cp_ucas_cycle
1146 -- Comparing the Entry Year and Entry Month mapping with the Calendars
1147 AND ucal.system_code = uac.system_code
1148 AND uac.entry_year = ucal.entry_year
1149 AND (uac.entry_month = ucal.entry_month OR ucal.entry_month = 0)
1150 AND apl.person_id = cp_person_id
1151 AND apl.admission_appl_number = cp_admission_appl_number
1152 AND apl.acad_cal_type = ucal.aca_cal_type
1153 AND apl.acad_ci_sequence_number = ucal.aca_cal_seq_no
1154 AND apl.adm_cal_type = ucal.adm_cal_type
1155 AND apl.adm_ci_sequence_number = ucal.adm_cal_seq_no
1156 -- Comparing the OSS Program Instance
1157 AND aplinst.person_id = apl.person_id
1158 AND aplinst.admission_appl_number = apl.admission_appl_number
1159 AND aplinst.nominated_course_cd = uac.oss_program_code
1160 AND aplinst.crv_version_number = uac.oss_program_version
1161 AND aplinst.location_cd = uac.oss_location
1162 AND aplinst.attendance_mode = uac.oss_attendance_mode
1163 AND aplinst.attendance_type = uac.oss_attendance_type
1164 -- Comparing the Final Unit Set
1165 AND aplinst.unit_set_cd = us.unit_set_cd
1166 AND aplinst.us_version_number = us.version_number
1167 AND us.unit_set_cat = usc.unit_set_cat
1168 AND usc.s_unit_set_cat = 'PRENRL_YR'
1169 AND us.unit_set_cd = cnfg.unit_set_cd
1170 AND cnfg.sequence_no = NVL(uac.point_of_entry,1)
1171 -- Comparing the Admission Outcome Status
1172 AND mos.system_code = uac.system_code
1173 AND mos.decision_code = uac.decision
1174 AND mos.default_ind = 'Y'
1175 AND mos.closed_ind <> 'Y'
1176 AND mos.adm_outcome_status = aplinst.adm_outcome_status
1177 -- Comparing the Admission Offer Response Status
1178 AND(uac.reply IS NULL OR
1179 (mor.system_code = uac.system_code
1180 AND mor.decision_code = uac.decision
1181 AND mor.reply_code = uac.reply
1182 AND mor.closed_ind <> 'Y'
1183 AND mor.adm_offer_resp_status = aplinst.adm_offer_resp_status ) );
1184 l_dummy VARCHAR2(1);
1185
1186 -- to get all the distinct system_codes belonging to the passed application choice parameter
1187 CURSOR c_ch_system IS
1188 SELECT DISTINCT a.system_code, a.entry_year, a.entry_month
1189 FROM igs_uc_app_choices a
1190 WHERE a.app_no = NVL(p_app_no, a.app_no)
1191 AND a.choice_no = NVL(p_choice_no,a.choice_no)
1192 AND a.export_to_oss_status = 'NEW'
1193 AND a.institute_code IN (SELECT df.current_inst_code FROM igs_uc_defaults df);
1194
1195 --Cursor to get the Calendar details for the given System, Entry Month and Entry Year.
1196 CURSOR cur_sys_entry_cal_det (cp_system_code igs_uc_sys_calndrs.system_code%TYPE,
1197 cp_entry_year igs_uc_sys_calndrs.entry_year%TYPE,
1198 cp_entry_month igs_uc_sys_calndrs.entry_month%TYPE ) IS
1199 SELECT aca_cal_type,
1200 aca_cal_seq_no,
1201 adm_cal_type,
1202 adm_cal_seq_no
1203 FROM igs_uc_sys_calndrs
1204 WHERE system_code = cp_system_code
1205 AND entry_year = cp_entry_year
1206 AND entry_month = cp_entry_month;
1207
1208 l_sys_entry_cal_det_rec cur_sys_entry_cal_det%ROWTYPE;
1209
1210 --Cursor to get the Admission Process Category and Admission Process Type for the
1211 --Admission Application Type defined for the System in UCAS Setup.
1212 CURSOR cur_apc_det ( cp_application_type igs_uc_defaults.application_type%TYPE) IS
1213 SELECT admission_cat, s_admission_process_type
1214 FROM igs_ad_ss_appl_typ
1215 WHERE admission_application_type = cp_application_type
1216 AND closed_ind = 'N';
1217
1218 l_apc_det_rec cur_apc_det%ROWTYPE;
1219
1220 --Record Type to hold the batch_id created for a system cycle calendars.
1221 TYPE batch_det_type IS RECORD
1222 ( system_code igs_uc_app_choices.system_code%TYPE,
1223 entry_year igs_uc_app_choices.entry_year%TYPE,
1224 entry_month igs_uc_app_choices.entry_month%TYPE,
1225 batch_id igs_ad_batc_def_det_all.batch_id%TYPE
1226 );
1227
1228 --Table Type to hold the batch_id created for diferrent system cycle calendars.
1229 TYPE batch_det_table_type IS TABLE OF batch_det_type INDEX BY BINARY_INTEGER;
1230
1231 --Table/Collection variable to hold the records for batch ids created of diferrent system, cycle and calendars.
1232 l_batch_id_det batch_det_table_type;
1233 l_batch_id_loc NUMBER;
1234
1235
1236 PROCEDURE get_batchid_loc( p_system_code IN igs_uc_app_choices.system_code%TYPE,
1237 p_entry_year IN igs_uc_app_choices.entry_year%TYPE,
1238 p_entry_month IN igs_uc_app_choices.entry_month%TYPE,
1239 p_batch_id_loc OUT NOCOPY NUMBER) IS
1240 /******************************************************************
1241 Created By : rbezawad
1242 Date Created By : 14-Jun-03
1243 Purpose : Local Procedure to obsolete_applications() procedure, which retuns the Batch ID location
1244 in pl/sql table(l_batch_id_det) of Batch ID for passed parameter criteria.
1245 Known limitations,enhancements,remarks:
1246 Change History
1247 Who When What
1248 rbezawad 24-Jul-2003 Done modifications to retrieve the batch id location based on system code, entry year and entry month.
1249 Modifications are done as part of UCCR007 and UCCR203 enhancement, Bug No: 3022067.
1250 ***************************************************************** */
1251 BEGIN
1252
1253 -- Search for the Batch ID location only when the PL/SQL table has some data.
1254 IF l_batch_id_det.FIRST IS NOT NULL AND l_batch_id_det.LAST IS NOT NULL THEN
1255
1256 --Loop through the pl/sql table and check for the values.
1257 FOR l_loc IN l_batch_id_det.FIRST..l_batch_id_det.LAST LOOP
1258 IF l_batch_id_det(l_loc).system_code = p_system_code AND
1259 l_batch_id_det(l_loc).entry_year = p_entry_year AND
1260 l_batch_id_det(l_loc).entry_month = p_entry_month THEN
1261 --If the Batch ID found for the matching parameters then return the location of batch id in to out parameter p_batch_id_loc.
1262 p_batch_id_loc := l_loc;
1263 EXIT;
1264 END IF;
1265 END LOOP;
1266
1267 END IF;
1268
1269 EXCEPTION
1270 WHEN OTHERS THEN
1271 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
1272 fnd_message.set_token('NAME','IGS_UCAS_EXPORT_TO_OSS.GET_BATCHID_LOC'||' - '||SQLERRM);
1273 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
1274 App_Exception.Raise_Exception;
1275
1276 END get_batchid_loc;
1277
1278 BEGIN
1279
1280 l_batch_id_loc := 0;
1281
1282 -- Insert a record into the Admission Decision Import Batch table,IGS_AD_BATC_DEF_DET_ALL
1283 -- for the deffered academic/admission calendar session details
1284 -- This Batch ID will be used while populating the Admission Decision Import Process Interface Table
1285 FOR c_ch_system_rec IN c_ch_system LOOP
1286
1287 --Get the Admission Process category details available in UCAS Setup.
1288 FOR c_defaults_rec IN c_defaults(c_ch_system_rec.system_code) LOOP
1289
1290 --Get the APC details corresponding to the Application Type defined in UCAS Setup
1291 OPEN cur_apc_det(c_defaults_rec.application_type);
1292 FETCH cur_apc_det INTO l_apc_det_rec;
1293 CLOSE cur_apc_det;
1294
1295 -- We need to create a separate batch id for each of the UCAS System's calendars
1296 -- Get the Batch ID Description value from the Message,IGS_UC_DEC_BATCH
1297 fnd_message.set_name('IGS','IGS_UC_DEC_BATCH');
1298 l_description := fnd_message.Get() ;
1299 l_rowid := NULL ;
1300 l_batch_id := NULL;
1301
1302 --Get the Calendar details for the given System, Entry Month and Entry Year from System Calendards table.
1303 l_sys_entry_cal_det_rec := NULL;
1304 OPEN cur_sys_entry_cal_det(c_ch_system_rec.system_code, c_ch_system_rec.entry_year,c_ch_system_rec.entry_month );
1305 FETCH cur_sys_entry_cal_det INTO l_sys_entry_cal_det_rec;
1306 --If no matching Entry Year and Entry Month record for the system is found in the System Calendars table then
1307 -- get the calendar details from the IGS_UC_SYS_CALNDRS table based on the system, Entry Year and Entry Month as 0 (Zero).
1308 IF cur_sys_entry_cal_det%NOTFOUND THEN
1309 CLOSE cur_sys_entry_cal_det;
1310 OPEN cur_sys_entry_cal_det(c_ch_system_rec.system_code, c_ch_system_rec.entry_year, 0);
1311 FETCH cur_sys_entry_cal_det INTO l_sys_entry_cal_det_rec;
1312 END IF;
1313 CLOSE cur_sys_entry_cal_det;
1314
1315 -- We need to create a separate batch id for each of the calendar setup available for UCAS System, Entry Year and Entry Month details.
1316 igs_ad_batc_def_det_pkg.insert_row(
1317 x_rowid => l_rowid,
1318 x_batch_id => l_batch_id,
1319 x_description => l_description,
1320 x_acad_cal_type => l_sys_entry_cal_det_rec.aca_cal_type,
1321 x_acad_ci_sequence_number => l_sys_entry_cal_det_rec.aca_cal_seq_no,
1322 x_adm_cal_type => l_sys_entry_cal_det_rec.adm_cal_type,
1323 x_adm_ci_sequence_number => l_sys_entry_cal_det_rec.adm_cal_seq_no,
1324 x_admission_cat => l_apc_det_rec.admission_cat,
1325 x_s_admission_process_type => l_apc_det_rec.s_admission_process_type,
1326 x_decision_make_id => c_defaults_rec.decision_make_id,
1327 x_decision_date => SYSDATE,
1328 x_decision_reason_id => c_defaults_rec.decision_reason_id,
1329 x_pending_reason_id => NULL,
1330 x_offer_dt => NULL,
1331 x_offer_response_dt => NULL,
1332 x_mode => 'R' );
1333
1334 --Store the information of Batch ID created into a pl/sql table
1335 l_batch_id_det(l_batch_id_loc).system_code := c_ch_system_rec.system_code;
1336 l_batch_id_det(l_batch_id_loc).entry_year := c_ch_system_rec.entry_year;
1337 l_batch_id_det(l_batch_id_loc).entry_month := c_ch_system_rec.entry_month;
1338 l_batch_id_det(l_batch_id_loc).batch_id := l_batch_id;
1339 l_batch_id_loc := l_batch_id_loc + 1;
1340
1341 END LOOP ;
1342
1343 END LOOP ;
1344
1345 l_batch_id_loc := NULL;
1346
1347 -- Get all the valid application choices in status NEW and
1348 -- check if the old applications need to be obsoleted
1349 FOR c_new_app_ch_rec IN c_new_app_ch LOOP
1350 Savepoint pref ;
1351 l_new_appl := FALSE ;
1352 l_new_appl_inst := FALSE ;
1353 l_export_to_oss_status := NULL ;
1354 l_ch_batch_id := NULL ;
1355 l_ch_error := NULL ;
1356 l_cancel_appl := FALSE;
1357
1358 cur_comp_app_choice_rec := NULL;
1359 OPEN cur_comp_app_choice(c_new_app_ch_rec.app_no, c_new_app_ch_rec.choice_no,c_new_app_ch_rec.ucas_cycle) ;
1360 FETCH cur_comp_app_choice INTO cur_comp_app_choice_rec;
1361 IF cur_comp_app_choice%FOUND THEN
1362 --Check the Last Transaction Type whether it is 'RD' and Course, Entry Month, Entry Year and Point of Entry are NULL or not
1363 cur_latest_trans_rec := NULL ;
1364 OPEN cur_latest_trans(c_new_app_ch_rec.app_no, c_new_app_ch_rec.choice_no, c_new_app_ch_rec.ucas_cycle);
1365 FETCH cur_latest_trans INTO cur_latest_trans_rec;
1366
1367 -- If Transaction Type is 'RD' and Course, Entry Month, Entry Year and Point of Entry are NULL
1368 -- and ucas decision is 'U' or 'A' and the reply is 'F' then set the export_to_oss_status to 'UF'
1369 IF cur_latest_trans%FOUND AND cur_latest_trans_rec.transaction_type = 'RD' AND
1370 cur_latest_trans_rec.program_code IS NULL AND cur_latest_trans_rec.entry_month IS NULL AND
1371 cur_latest_trans_rec.entry_year IS NULL AND cur_latest_trans_rec.entry_point IS NULL AND
1372 c_new_app_ch_rec.decision IN ('U','A') AND c_new_app_ch_rec.reply = 'F' THEN
1373 l_export_to_oss_status:= 'UF' ;
1374 ELSE
1375
1376 -- Check whether any change in Decision, Reply, Program Offering Option, Point of entry map to
1377 -- OSS Admission Application Instance
1378 OPEN change_in_adm_appl_cur(c_new_app_ch_rec.app_no, c_new_app_ch_rec.choice_no, c_new_app_ch_rec.ucas_cycle,
1379 cur_comp_app_choice_rec.person_id, cur_comp_app_choice_rec.admission_appl_number );
1380 FETCH change_in_adm_appl_cur INTO l_dummy;
1381
1382 -- If no change was found then change the status to COMP, else to MAN
1383 IF change_in_adm_appl_cur%FOUND THEN
1384 l_export_to_oss_status:= 'COMP';
1385 ELSE
1386 l_export_to_oss_status:= 'MAN';
1387 l_ch_error := 'M001' ;
1388 END IF;
1389 CLOSE change_in_adm_appl_cur;
1390
1391 END IF ;
1392 CLOSE cur_latest_trans;
1393
1394 ELSE
1395 -- Get the default UCAS setup values and keep them in package variable c_defaults_rec
1396 c_defaults_rec := NULL ;
1397 OPEN c_defaults(c_new_app_ch_rec.system_code) ;
1398 FETCH c_defaults INTO c_defaults_rec;
1399 CLOSE c_defaults ;
1400
1401 --Get the Calendar details for the given System, Entry Month and Entry Year from System Calendards table.
1402 l_sys_entry_cal_det_rec := NULL;
1403 OPEN cur_sys_entry_cal_det(c_new_app_ch_rec.system_code, c_new_app_ch_rec.entry_year, c_new_app_ch_rec.entry_month );
1404 FETCH cur_sys_entry_cal_det INTO l_sys_entry_cal_det_rec;
1405 --If no matching Entry Year and Entry Month record for the system is found in the System Calendars table then
1406 -- get the calendar details from the IGS_UC_SYS_CALNDRS table based on the system, Entry Year and Entry Month as 0 (Zero).
1407 IF cur_sys_entry_cal_det%NOTFOUND THEN
1408 CLOSE cur_sys_entry_cal_det;
1409 OPEN cur_sys_entry_cal_det(c_new_app_ch_rec.system_code, c_new_app_ch_rec.entry_year, 0);
1410 FETCH cur_sys_entry_cal_det INTO l_sys_entry_cal_det_rec;
1411 END IF;
1412 CLOSE cur_sys_entry_cal_det;
1413
1414 -- Get the system outcome status
1415 l_s_obsol_ou_stat := NULL ;
1416 OPEN c_obs_ou_stat( c_defaults_rec.obsolete_outcome_status) ;
1417 FETCH c_obs_ou_stat INTO l_s_obsol_ou_stat;
1418 CLOSE c_obs_ou_stat ;
1419
1420 -- Determine if a new application / application instance needs to be created for this choice , or
1421 -- an application instance already exists for this choice
1422 OPEN c_adm_appl(c_new_app_ch_rec.app_no, c_new_app_ch_rec.choice_no) ;
1423 FETCH c_adm_appl INTO c_adm_appl_rec ;
1424
1425 -- If there is no application in admissions with this choice number then
1426 -- it means that this choice had never been imported previously.
1427 -- Hence no need to void any existing application
1428 IF c_adm_appl%NOTFOUND THEN
1429 CLOSE c_adm_appl ;
1430 l_export_to_oss_status := 'OC' ;
1431 -- If this choice had been exported previously ,an application exits for it.
1432 ELSE
1433
1434 CLOSE c_adm_appl ;
1435 -- Get the admission and academic calendars for this choice
1436 l_aca_cal_type := l_sys_entry_cal_det_rec.aca_cal_type ;
1437 l_aca_seq_no := l_sys_entry_cal_det_rec.aca_cal_seq_no;
1438 l_adm_cal_type := l_sys_entry_cal_det_rec.adm_cal_type;
1439 l_adm_seq_no := l_sys_entry_cal_det_rec.adm_cal_seq_no ;
1440
1441 -- Check if an application exists for corresponding to this choice and calendars
1442 OPEN c_match_adm_appl(c_new_app_ch_rec.app_no, c_new_app_ch_rec.choice_no) ;
1443 FETCH c_match_adm_appl INTO c_match_adm_appl_rec ;
1444 --If applications exist for this choice number but not for the choice's calendars then
1445 -- set flag that new application needs to be created and old application needs to be voided
1446 IF c_match_adm_appl%NOTFOUND THEN
1447 l_new_appl := TRUE ;
1448 ELSE
1449 --If applications exists for this choice number and choice's calendars then
1450 -- check if the application instance for this choice's POP and unit set also exist
1451
1452 -- smaddali added this code to find the unit set code corresponding to the point of entry of the
1453 -- current application choice ,for bug 2630219
1454 -- Find Unit set cd
1455 -- Get the Unit set cd corresponding to the application choice point of entry and POP
1456 c_unit_set_cd_rec := NULL ;
1457 OPEN c_unit_set_cd(NVL(c_new_app_ch_rec.point_of_entry,1),
1458 c_new_app_ch_rec.oss_program_code,
1459 c_new_app_ch_rec.oss_program_version,
1460 l_aca_cal_type,
1461 c_new_app_ch_rec.oss_location,
1462 c_new_app_ch_rec.oss_attendance_mode,
1463 c_new_app_ch_rec.oss_attendance_type );
1464 FETCH c_unit_set_cd INTO c_unit_set_cd_rec;
1465 IF c_unit_set_cd%NOTFOUND THEN
1466 -- If the point of entry doesnot correspond to a valid Unit set cd then log error and skip the choice
1467 fnd_message.set_name('IGS','IGS_UC_NO_UNIT_SET_CD');
1468 fnd_message.set_token('APP_NO',TO_CHAR(c_new_app_ch_rec.app_no));
1469 fnd_message.set_token('CHOICE_NO',TO_CHAR(c_new_app_ch_rec.choice_no));
1470 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
1471 -- set the choice record error code to A003 and export status to OC to indicate that
1472 -- import interface tables have not been successfully populated but an error was encountered
1473 l_ch_error := 'A003' ;
1474 l_export_to_oss_status := c_new_app_ch_rec.export_to_oss_status ;
1475 l_ch_batch_id := NULL ;
1476 ELSE
1477 -- smaddali added unit set cd and version number parameters to this cursor for bug 2630219
1478 OPEN c_adm_appl_inst(c_new_app_ch_rec.app_no, c_new_app_ch_rec.choice_no,
1479 c_match_adm_appl_rec.admission_appl_number ,
1480 c_unit_set_cd_rec.unit_set_cd , c_unit_set_cd_rec.us_version_number) ;
1481 FETCH c_adm_appl_inst INTO c_adm_appl_inst_rec ;
1482 -- If application instance corresponding to the current choice is not found then
1483 -- a new application instance needs to be created under the old application and
1484 -- the old application instance needs to be obsoleted
1485 IF c_adm_appl_inst%NOTFOUND THEN
1486 l_new_appl_inst := TRUE ;
1487
1488 --for bug 4424068 - need to check if the oss-program-version has changed for the application
1489 --if the only change in the new choice record is the program version number
1490 --and the current application status is 'RECEIVED', and current outcome status is 'PENDING'/'WITHDRAWN'
1491 --we need to CANCEL the application
1492 OPEN c_cancel_appl (c_new_app_ch_rec.app_no, c_new_app_ch_rec.choice_no,
1493 c_match_adm_appl_rec.admission_appl_number ,
1494 c_unit_set_cd_rec.unit_set_cd , c_unit_set_cd_rec.us_version_number) ;
1495 FETCH c_cancel_appl INTO c_cancel_appl_rec ;
1496
1497 IF c_cancel_appl%NOTFOUND THEN
1498 --no action
1499 NULL;
1500 ELSE
1501 l_cancel_appl := TRUE;
1502 --need to cancel the application
1503
1504 -- capture outcome status, decision_maker_id,decision reason
1505 -- If a record exists for this application choice then update it ,else create new record
1506 c_old_oustat_rec := NULL ;
1507 OPEN c_old_oustat(c_new_app_ch_rec.app_no , c_new_app_ch_rec.choice_no ) ;
1508 FETCH c_old_oustat INTO c_old_oustat_rec ;
1509 IF c_old_oustat%NOTFOUND THEN
1510 l_rowid := NULL ;
1511 igs_uc_old_oustat_pkg.insert_row (
1512 X_ROWID => l_rowid
1513 ,X_APP_NO => c_new_app_ch_rec.app_no
1514 ,X_CHOICE_NO => c_new_app_ch_rec.choice_no
1515 ,X_OLD_OUTCOME_STATUS => c_cancel_appl_rec.adm_outcome_status
1516 ,X_DECISION_DATE => c_cancel_appl_rec.decision_date
1517 ,X_DECISION_REASON_ID => c_cancel_appl_rec.decision_reason_id
1518 ,X_DECISION_MAKE_ID => c_cancel_appl_rec.decision_make_id
1519 ,X_MODE => 'R'
1520 ) ;
1521 ELSE
1522 igs_uc_old_oustat_pkg.update_row (
1523 X_ROWID => c_old_oustat_rec.ROWID
1524 ,X_APP_NO => c_old_oustat_rec.app_no
1525 ,X_CHOICE_NO => c_old_oustat_rec.choice_no
1526 ,X_OLD_OUTCOME_STATUS => c_cancel_appl_rec.adm_outcome_status
1527 ,X_DECISION_DATE => c_cancel_appl_rec.decision_date
1528 ,X_DECISION_REASON_ID => c_cancel_appl_rec.decision_reason_id
1529 ,X_DECISION_MAKE_ID => c_cancel_appl_rec.decision_make_id
1530 ,X_MODE => 'R'
1531 ) ;
1532 END IF ;
1533 CLOSE c_old_oustat ;
1534
1535 -- populate decision import interface tables
1536 -- 1. Get the Batch ID of the Admission Decision Import Batch table,IGS_AD_BATC_DEF_DET_ALL
1537 l_dec_batch_id := NULL;
1538 l_batch_id_loc :=NULL;
1539 get_batchid_loc(p_system_code => c_new_app_ch_rec.system_code,
1540 p_entry_year => c_new_app_ch_rec.entry_year,
1541 p_entry_month => c_new_app_ch_rec.entry_month,
1542 p_batch_id_loc => l_batch_id_loc);
1543 IF l_batch_id_loc IS NOT NULL THEN
1544 l_dec_batch_id := l_batch_id_det(l_batch_id_loc).batch_id;
1545 END IF;
1546
1547 -- 2. Populate the Admission Decision Import Interface table,IGS_AD_ADMDE_INT_ALL
1548 /* call the insert_row of the Admission Decision Import Interface table, IGS_AD_ADMDE_INT_ALL */
1549 l_interface_run_id := NULL ;
1550 l_interface_mkdes_id := NULL ;
1551 l_error_message := NULL ;
1552 l_return_status := NULL ;
1553 OPEN c_interface_run_id ;
1554 FETCH c_interface_run_id INTO l_interface_run_id ;
1555 CLOSE c_interface_run_id ;
1556 l_rowid := NULL ;
1557
1558 -- Create record in decision import interface tables for the applications which need to be voided
1559 igs_ad_admde_int_pkg.insert_row (
1560 x_rowid => l_rowid,
1561 x_interface_mkdes_id => l_interface_mkdes_id,
1562 x_interface_run_id => l_interface_run_id ,
1563 x_batch_id => l_dec_batch_id,
1564 x_person_id => c_cancel_appl_rec.person_id,
1565 x_admission_appl_number => c_cancel_appl_rec.admission_appl_number,
1566 x_nominated_course_cd => c_cancel_appl_rec.nominated_course_cd,
1567 x_sequence_number => c_cancel_appl_rec.sequence_number,
1568 x_adm_outcome_status => IGS_AD_GEN_009.Admp_Get_Sys_Aos('CANCELLED'),
1569 x_decision_make_id => c_defaults_rec.decision_make_id,
1570 x_decision_date => TRUNC(SYSDATE),
1571 x_decision_reason_id => c_defaults_rec.decision_reason_id,
1572 x_pending_reason_id => NULL,
1573 x_offer_dt => NULL,
1574 x_offer_response_dt => NULL,
1575 x_status => '2', -- pending status
1576 x_error_code => NULL,
1577 x_mode => 'R',
1578 x_reconsider_flag => 'N' );
1579
1580 -- 3. call the decision import process to obsolete old applications
1581 igs_ad_imp_adm_des.prc_adm_outcome_status(
1582 p_person_id => c_cancel_appl_rec.person_id ,
1583 p_admission_appl_number => c_cancel_appl_rec.admission_appl_number ,
1584 p_nominated_course_cd => c_cancel_appl_rec.nominated_course_cd ,
1585 p_sequence_number => c_cancel_appl_rec.sequence_number,
1586 p_adm_outcome_status => IGS_AD_GEN_009.Admp_Get_Sys_Aos('CANCELLED'),
1587 p_s_adm_outcome_status => 'CANCELLED' ,
1588 p_acad_cal_type => c_cancel_appl_rec.acad_cal_type ,
1589 p_acad_ci_sequence_number => c_cancel_appl_rec.acad_ci_sequence_number,
1590 p_adm_cal_type => c_cancel_appl_rec.adm_cal_type ,
1591 p_adm_ci_sequence_number => c_cancel_appl_rec.adm_ci_sequence_number ,
1592 p_admission_cat => c_cancel_appl_rec.admission_cat ,
1593 p_s_admission_process_type => c_cancel_appl_rec.s_admission_process_type ,
1594 p_batch_id => l_dec_batch_id,
1595 p_interface_run_id => l_interface_run_id ,
1596 p_interface_mkdes_id => l_interface_mkdes_id,
1597 p_error_message => l_error_message,
1598 p_return_status => l_return_status ,
1599 p_ucas_transaction => 'N',
1600 p_reconsideration => 'N' );
1601
1602 -- if the decision import completed in error then set appropriate error code in app choice record
1603 IF l_error_message IS NOT NULL OR l_return_status = 'FALSE' THEN
1604 /* raise the error with code 'O002' */
1605 fnd_message.set_name('IGS','IGS_UC_CANCEL_APP_DEC_IMP_ERR');
1606 fnd_message.set_token('APP_NO', c_new_app_ch_rec.app_no);
1607 fnd_message.set_token('CHOICE_NO', c_new_app_ch_rec.choice_no);
1608 fnd_message.set_token('BATCH_ID', l_dec_batch_id);
1609 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
1610 l_ch_error := 'O002' ;
1611 l_ch_batch_id := l_dec_batch_id ;
1612 l_export_to_oss_status := 'OO' ;
1613 ELSE
1614 -- decision import for obsoletion is successful
1615 l_ch_error := NULL ;
1616 l_ch_batch_id := NULL ;
1617 l_export_to_oss_status := 'OC' ;
1618
1619 -- Log a message that the application choice has been successfully obsoleted
1620 fnd_message.set_name('IGS','IGS_UC_CANCEL_APP_DEC_IMP_SUC');
1621 fnd_message.set_token('APP_NO', c_new_app_ch_rec.app_no);
1622 fnd_message.set_token('CHOICE_NO', c_new_app_ch_rec.choice_no);
1623 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
1624 END IF ; -- decision import failed or passed
1625
1626 END IF; -- if c_cancel_appl%FOUND
1627 CLOSE c_cancel_appl;
1628
1629 ELSE
1630 -- If application instance corresponding to the current choice is not found then
1631 -- no need to obsolete any applications
1632 l_export_to_oss_status := 'OC';
1633
1634 END IF ;
1635 CLOSE c_adm_appl_inst ;
1636
1637 END IF ; -- If unit set cd mapped to point of entry is not found
1638 CLOSE c_unit_set_cd;
1639
1640 END IF ;
1641 CLOSE c_match_adm_appl ;
1642
1643 END IF ;
1644
1645
1646 -- Populate the decision application import interface tables when new application / instance is to be created
1647 IF (l_new_appl OR l_new_appl_inst) AND NOT l_cancel_appl THEN
1648
1649 -- Check the currently active admission application instances
1650 l_obsol_appl_cnt := NULL;
1651 OPEN c_obsol_appl_cnt(c_new_app_ch_rec.app_no,
1652 c_new_app_ch_rec.choice_no );
1653 FETCH c_obsol_appl_cnt INTO l_obsol_appl_cnt ;
1654 CLOSE c_obsol_appl_cnt ;
1655
1656 -- If More than one application instances is active for the application log error message .
1657 IF l_obsol_appl_cnt > 1 THEN
1658 fnd_message.set_name('IGS','IGS_UC_MANY_APPL');
1659 fnd_message.set_token('APP_NO', c_new_app_ch_rec.app_no);
1660 fnd_message.set_token('CHOICE_NO', c_new_app_ch_rec.choice_no);
1661 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
1662 l_ch_error := 'O003' ;
1663 l_export_to_oss_status:= c_new_app_ch_rec.export_to_oss_status ;
1664 l_ch_batch_id := NULL;
1665
1666 -- if there are no currently active application instances then no need to obsolete anything
1667 ELSIF l_obsol_appl_cnt = 0 THEN
1668 l_export_to_oss_status:= 'OC' ;
1669 l_ch_error := NULL ;
1670 l_ch_batch_id := NULL;
1671
1672 -- If exactly one active application instance found then obsolete it
1673 ELSIF l_obsol_appl_cnt = 1 THEN
1674 -- Get the old application instance to be voided
1675 c_obsol_appl_rec := NULL;
1676 OPEN c_obsol_appl(c_new_app_ch_rec.app_no,
1677 c_new_app_ch_rec.choice_no );
1678 FETCH c_obsol_appl INTO c_obsol_appl_rec ;
1679 CLOSE c_obsol_appl ;
1680
1681 -- populate application interface tables to
1682 -- increment preference numbers of all existing application instances of the identified application to be obsoleted
1683 BEGIN
1684 FOR c_upd_appl_inst_rec IN c_upd_appl_inst(c_obsol_appl_rec.person_id,
1685 c_obsol_appl_rec.admission_appl_number) LOOP
1686
1687 c_upd_appl_inst_rec.preference_number := c_upd_appl_inst_rec.preference_number + 1;
1688 -- call TBH update row
1689 l_pref_excep := FALSE;
1690 igs_ad_ps_appl_inst_pkg.update_row (
1691 x_rowid => c_upd_appl_inst_rec.ROWID ,
1692 x_person_id => c_upd_appl_inst_rec.person_id ,
1693 x_admission_appl_number => c_upd_appl_inst_rec.admission_appl_number ,
1694 x_nominated_course_cd => c_upd_appl_inst_rec.nominated_course_cd ,
1695 x_sequence_number => c_upd_appl_inst_rec.sequence_number ,
1696 x_predicted_gpa => c_upd_appl_inst_rec.predicted_gpa ,
1697 x_academic_index => c_upd_appl_inst_rec.academic_index ,
1698 x_adm_cal_type => c_upd_appl_inst_rec.adm_cal_type ,
1699 x_app_file_location => c_upd_appl_inst_rec.app_file_location ,
1700 x_adm_ci_sequence_number => c_upd_appl_inst_rec.adm_ci_sequence_number ,
1701 x_course_cd => c_upd_appl_inst_rec.course_cd ,
1702 x_app_source_id => c_upd_appl_inst_rec.app_source_id ,
1703 x_crv_version_number => c_upd_appl_inst_rec.crv_version_number ,
1704 x_waitlist_rank => c_upd_appl_inst_rec.waitlist_rank ,
1705 x_location_cd => c_upd_appl_inst_rec.location_cd ,
1706 x_attent_other_inst_cd => c_upd_appl_inst_rec.attent_other_inst_cd ,
1707 x_attendance_mode => c_upd_appl_inst_rec.attendance_mode ,
1708 x_edu_goal_prior_enroll_id => c_upd_appl_inst_rec.edu_goal_prior_enroll_id ,
1709 x_attendance_type => c_upd_appl_inst_rec.attendance_type ,
1710 x_decision_make_id => c_upd_appl_inst_rec.decision_make_id ,
1711 x_unit_set_cd => c_upd_appl_inst_rec.unit_set_cd ,
1712 x_decision_date => c_upd_appl_inst_rec.decision_date ,
1713 x_attribute_category => c_upd_appl_inst_rec.attribute_category ,
1714 x_attribute1 => c_upd_appl_inst_rec.attribute1 ,
1715 x_attribute2 => c_upd_appl_inst_rec.attribute2 ,
1716 x_attribute3 => c_upd_appl_inst_rec.attribute3 ,
1717 x_attribute4 => c_upd_appl_inst_rec.attribute4 ,
1718 x_attribute5 => c_upd_appl_inst_rec.attribute5 ,
1719 x_attribute6 => c_upd_appl_inst_rec.attribute6 ,
1720 x_attribute7 => c_upd_appl_inst_rec.attribute7 ,
1721 x_attribute8 => c_upd_appl_inst_rec.attribute8 ,
1722 x_attribute9 => c_upd_appl_inst_rec.attribute9 ,
1723 x_attribute10 => c_upd_appl_inst_rec.attribute10 ,
1724 x_attribute11 => c_upd_appl_inst_rec.attribute11 ,
1725 x_attribute12 => c_upd_appl_inst_rec.attribute12 ,
1726 x_attribute13 => c_upd_appl_inst_rec.attribute13 ,
1727 x_attribute14 => c_upd_appl_inst_rec.attribute14 ,
1728 x_attribute15 => c_upd_appl_inst_rec.attribute15 ,
1729 x_attribute16 => c_upd_appl_inst_rec.attribute16 ,
1730 x_attribute17 => c_upd_appl_inst_rec.attribute17 ,
1731 x_attribute18 => c_upd_appl_inst_rec.attribute18 ,
1732 x_attribute19 => c_upd_appl_inst_rec.attribute19 ,
1733 x_attribute20 => c_upd_appl_inst_rec.attribute20 ,
1734 x_decision_reason_id => c_upd_appl_inst_rec.decision_reason_id ,
1735 x_us_version_number => c_upd_appl_inst_rec.us_version_number ,
1736 x_decision_notes => c_upd_appl_inst_rec.decision_notes ,
1737 x_pending_reason_id => c_upd_appl_inst_rec.pending_reason_id ,
1738 x_preference_number => c_upd_appl_inst_rec.preference_number ,
1739 x_adm_doc_status => c_upd_appl_inst_rec.adm_doc_status ,
1740 x_adm_entry_qual_status => c_upd_appl_inst_rec.adm_entry_qual_status ,
1741 x_deficiency_in_prep => c_upd_appl_inst_rec.deficiency_in_prep ,
1742 x_late_adm_fee_status => c_upd_appl_inst_rec.late_adm_fee_status ,
1743 x_spl_consider_comments => c_upd_appl_inst_rec.spl_consider_comments ,
1744 x_apply_for_finaid => c_upd_appl_inst_rec.apply_for_finaid ,
1745 x_finaid_apply_date => c_upd_appl_inst_rec.finaid_apply_date ,
1746 x_adm_outcome_status => c_upd_appl_inst_rec.adm_outcome_status ,
1747 x_adm_otcm_stat_auth_per_id => c_upd_appl_inst_rec.adm_otcm_status_auth_person_id ,
1748 x_adm_outcome_status_auth_dt => c_upd_appl_inst_rec.adm_outcome_status_auth_dt ,
1749 x_adm_outcome_status_reason => c_upd_appl_inst_rec.adm_outcome_status_reason ,
1750 x_offer_dt => c_upd_appl_inst_rec.offer_dt ,
1751 x_offer_response_dt => c_upd_appl_inst_rec.offer_response_dt ,
1752 x_prpsd_commencement_dt => c_upd_appl_inst_rec.prpsd_commencement_dt,
1753 x_adm_cndtnl_offer_status => c_upd_appl_inst_rec.adm_cndtnl_offer_status ,
1754 x_cndtnl_offer_satisfied_dt => c_upd_appl_inst_rec.cndtnl_offer_satisfied_dt ,
1755 x_cndnl_ofr_must_be_stsfd_ind => c_upd_appl_inst_rec.cndtnl_offer_must_be_stsfd_ind ,
1756 x_adm_offer_resp_status => c_upd_appl_inst_rec.adm_offer_resp_status ,
1757 x_actual_response_dt => c_upd_appl_inst_rec.actual_response_dt,
1758 x_adm_offer_dfrmnt_status => c_upd_appl_inst_rec.adm_offer_dfrmnt_status ,
1759 x_deferred_adm_cal_type => c_upd_appl_inst_rec.deferred_adm_cal_type ,
1760 x_deferred_adm_ci_sequence_num => c_upd_appl_inst_rec.deferred_adm_ci_sequence_num ,
1761 x_deferred_tracking_id => c_upd_appl_inst_rec.deferred_tracking_id ,
1762 x_ass_rank => c_upd_appl_inst_rec.ass_rank ,
1763 x_secondary_ass_rank => c_upd_appl_inst_rec.secondary_ass_rank ,
1764 x_intr_accept_advice_num => c_upd_appl_inst_rec.intrntnl_acceptance_advice_num ,
1765 x_ass_tracking_id => c_upd_appl_inst_rec.ass_tracking_id ,
1766 x_fee_cat => c_upd_appl_inst_rec.fee_cat ,
1767 x_hecs_payment_option => c_upd_appl_inst_rec.hecs_payment_option ,
1768 x_expected_completion_yr => c_upd_appl_inst_rec.expected_completion_yr ,
1769 x_expected_completion_perd => c_upd_appl_inst_rec.expected_completion_perd,
1770 x_correspondence_cat => c_upd_appl_inst_rec.correspondence_cat ,
1771 x_enrolment_cat => c_upd_appl_inst_rec.enrolment_cat ,
1772 x_funding_source => c_upd_appl_inst_rec.funding_source ,
1773 x_applicant_acptnce_cndtn => c_upd_appl_inst_rec.applicant_acptnce_cndtn ,
1774 x_cndtnl_offer_cndtn => c_upd_appl_inst_rec.cndtnl_offer_cndtn ,
1775 x_mode => 'R' ,
1776 x_ss_application_id => c_upd_appl_inst_rec.ss_application_id ,
1777 x_ss_pwd => c_upd_appl_inst_rec.ss_pwd ,
1778 x_authorized_dt => c_upd_appl_inst_rec.authorized_dt ,
1779 x_authorizing_pers_id => c_upd_appl_inst_rec.authorizing_pers_id ,
1780 x_entry_status => c_upd_appl_inst_rec.entry_status ,
1781 x_entry_level => c_upd_appl_inst_rec.entry_level ,
1782 x_sch_apl_to_id => c_upd_appl_inst_rec.sch_apl_to_id ,
1783 x_idx_calc_date => c_upd_appl_inst_rec.idx_calc_date ,
1784 x_waitlist_status => c_upd_appl_inst_rec.waitlist_status ,
1785 x_attribute21 => c_upd_appl_inst_rec.attribute21 ,
1786 x_attribute22 => c_upd_appl_inst_rec.attribute22 ,
1787 x_attribute23 => c_upd_appl_inst_rec.attribute23 ,
1788 x_attribute24 => c_upd_appl_inst_rec.attribute24 ,
1789 x_attribute25 => c_upd_appl_inst_rec.attribute25 ,
1790 x_attribute26 => c_upd_appl_inst_rec.attribute26 ,
1791 x_attribute27 => c_upd_appl_inst_rec.attribute27 ,
1792 x_attribute28 => c_upd_appl_inst_rec.attribute28 ,
1793 x_attribute29 => c_upd_appl_inst_rec.attribute29 ,
1794 x_attribute30 => c_upd_appl_inst_rec.attribute30 ,
1795 x_attribute31 => c_upd_appl_inst_rec.attribute31 ,
1796 x_attribute32 => c_upd_appl_inst_rec.attribute32 ,
1797 x_attribute33 => c_upd_appl_inst_rec.attribute33 ,
1798 x_attribute34 => c_upd_appl_inst_rec.attribute34 ,
1799 x_attribute35 => c_upd_appl_inst_rec.attribute35 ,
1800 x_attribute36 => c_upd_appl_inst_rec.attribute36 ,
1801 x_attribute37 => c_upd_appl_inst_rec.attribute37 ,
1802 x_attribute38 => c_upd_appl_inst_rec.attribute38 ,
1803 x_attribute39 => c_upd_appl_inst_rec.attribute39 ,
1804 x_attribute40 => c_upd_appl_inst_rec.attribute40 ,
1805 x_fut_acad_cal_type => c_upd_appl_inst_rec.future_acad_cal_type ,
1806 x_fut_acad_ci_sequence_number => c_upd_appl_inst_rec.future_acad_ci_sequence_number ,
1807 x_fut_adm_cal_type => c_upd_appl_inst_rec.future_adm_cal_type ,
1808 x_fut_adm_ci_sequence_number => c_upd_appl_inst_rec.future_adm_ci_sequence_number ,
1809 x_prev_term_adm_appl_number => c_upd_appl_inst_rec.previous_term_adm_appl_number ,
1810 x_prev_term_sequence_number => c_upd_appl_inst_rec.previous_term_sequence_number ,
1811 x_fut_term_adm_appl_number => c_upd_appl_inst_rec.future_term_adm_appl_number ,
1812 x_fut_term_sequence_number => c_upd_appl_inst_rec.future_term_sequence_number ,
1813 x_def_acad_cal_type => c_upd_appl_inst_rec.def_acad_cal_type ,
1814 x_def_acad_ci_sequence_num => c_upd_appl_inst_rec.def_acad_ci_sequence_num ,
1815 x_def_prev_term_adm_appl_num => c_upd_appl_inst_rec.def_prev_term_adm_appl_num ,
1816 x_def_prev_appl_sequence_num => c_upd_appl_inst_rec.def_prev_appl_sequence_num ,
1817 x_def_term_adm_appl_num => c_upd_appl_inst_rec.def_term_adm_appl_num ,
1818 x_def_appl_sequence_num => c_upd_appl_inst_rec.def_appl_sequence_num,
1819 X_APPL_INST_STATUS => c_upd_appl_inst_rec.appl_inst_status,
1820 x_ais_reason => c_upd_appl_inst_rec.ais_reason,
1821 x_decline_ofr_reason => c_upd_appl_inst_rec.decline_ofr_reason
1822 ) ;
1823
1824 END LOOP ;
1825 EXCEPTION
1826 WHEN OTHERS THEN
1827 ROLLBACK TO pref;
1828 fnd_message.set_name('IGS','IGS_UC_OBS_APP_INC_PREF_ERR');
1829 fnd_message.set_token('APP_NO', c_new_app_ch_rec.app_no);
1830 fnd_message.set_token('CHOICE_NO', c_new_app_ch_rec.choice_no);
1831 fnd_message.set_token('ADM_APPL', c_new_app_ch_rec.choice_no);
1832 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
1833 fnd_file.put_line(fnd_file.LOG, SQLERRM);
1834
1835 l_export_to_oss_status := c_new_app_ch_rec.export_to_oss_status;
1836 l_ch_error := 'O001' ;
1837 l_ch_batch_id := NULL;
1838 l_pref_excep := TRUE;
1839 END ; -- end of incrementing preference number
1840
1841 -- If incrementing preference numbers has failed then skip this choice
1842 IF NOT l_pref_excep THEN
1843
1844 -- capture outcome status, decision_maker_id,decision reason
1845 -- smaddali added the cursor c_old_oustat and update_row calls for bug 2630219
1846 -- If a record exists for this application choice then update it ,else create new record
1847 c_old_oustat_rec := NULL ;
1848 OPEN c_old_oustat(c_new_app_ch_rec.app_no , c_new_app_ch_rec.choice_no ) ;
1849 FETCH c_old_oustat INTO c_old_oustat_rec ;
1850 IF c_old_oustat%NOTFOUND THEN
1851 --smaddali initialising l_rowid to null for bug 2626178
1852 l_rowid := NULL ;
1853 igs_uc_old_oustat_pkg.insert_row (
1854 X_ROWID => l_rowid
1855 ,X_APP_NO => c_new_app_ch_rec.app_no
1856 ,X_CHOICE_NO => c_new_app_ch_rec.choice_no
1857 ,X_OLD_OUTCOME_STATUS => c_obsol_appl_rec.adm_outcome_status
1858 ,X_DECISION_DATE => c_obsol_appl_rec.decision_date
1859 ,X_DECISION_REASON_ID => c_obsol_appl_rec.decision_reason_id
1860 ,X_DECISION_MAKE_ID => c_obsol_appl_rec.decision_make_id
1861 ,X_MODE => 'R'
1862 ) ;
1863 ELSE
1864 igs_uc_old_oustat_pkg.update_row (
1865 X_ROWID => c_old_oustat_rec.ROWID
1866 ,X_APP_NO => c_old_oustat_rec.app_no
1867 ,X_CHOICE_NO => c_old_oustat_rec.choice_no
1868 ,X_OLD_OUTCOME_STATUS => c_obsol_appl_rec.adm_outcome_status
1869 ,X_DECISION_DATE => c_obsol_appl_rec.decision_date
1870 ,X_DECISION_REASON_ID => c_obsol_appl_rec.decision_reason_id
1871 ,X_DECISION_MAKE_ID => c_obsol_appl_rec.decision_make_id
1872 ,X_MODE => 'R'
1873 ) ;
1874 END IF ;
1875 CLOSE c_old_oustat ;
1876
1877 -- populate decision import interface tables
1878 -- 1. Get the Batch ID of the Admission Decision Import Batch table,IGS_AD_BATC_DEF_DET_ALL
1879 l_dec_batch_id := NULL;
1880 l_batch_id_loc :=NULL;
1881 get_batchid_loc(p_system_code => c_new_app_ch_rec.system_code,
1882 p_entry_year => c_new_app_ch_rec.entry_year,
1883 p_entry_month => c_new_app_ch_rec.entry_month,
1884 p_batch_id_loc => l_batch_id_loc);
1885 IF l_batch_id_loc IS NOT NULL THEN
1886 l_dec_batch_id := l_batch_id_det(l_batch_id_loc).batch_id;
1887 END IF;
1888
1889 -- 2. Populate the Admission Decision Import Interface table,IGS_AD_ADMDE_INT_ALL
1890 /* call the insert_row of the Admission Decision Import Interface table, IGS_AD_ADMDE_INT_ALL */
1891
1892 l_interface_run_id := NULL ;
1893 l_interface_mkdes_id := NULL ;
1894 l_error_message := NULL ;
1895 l_return_status := NULL ;
1896 OPEN c_interface_run_id ;
1897 FETCH c_interface_run_id INTO l_interface_run_id ;
1898 CLOSE c_interface_run_id ;
1899 l_rowid := NULL ;
1900
1901 -- Create record in decision import interface tables for the applications which need to be voided
1902 igs_ad_admde_int_pkg.insert_row (
1903 x_rowid => l_rowid,
1904 x_interface_mkdes_id => l_interface_mkdes_id,
1905 x_interface_run_id => l_interface_run_id ,
1906 x_batch_id => l_dec_batch_id,
1907 x_person_id => c_obsol_appl_rec.person_id,
1908 x_admission_appl_number => c_obsol_appl_rec.admission_appl_number,
1909 x_nominated_course_cd => c_obsol_appl_rec.nominated_course_cd,
1910 x_sequence_number => c_obsol_appl_rec.sequence_number,
1911 x_adm_outcome_status => c_defaults_rec.obsolete_outcome_status,
1912 x_decision_make_id => c_defaults_rec.decision_make_id,
1913 x_decision_date => TRUNC(SYSDATE),
1914 x_decision_reason_id => c_defaults_rec.decision_reason_id,
1915 x_pending_reason_id => NULL,
1916 x_offer_dt => NULL,
1917 x_offer_response_dt => NULL,
1918 x_status => '2', -- pending status
1919 x_error_code => NULL,
1920 x_mode => 'R',
1921 x_reconsider_flag => 'N' );
1922
1923 -- 3. call the decision import process to obsolete old applications
1924 igs_ad_imp_adm_des.prc_adm_outcome_status(
1925 p_person_id => c_obsol_appl_rec.person_id ,
1926 p_admission_appl_number => c_obsol_appl_rec.admission_appl_number ,
1927 p_nominated_course_cd => c_obsol_appl_rec.nominated_course_cd ,
1928 p_sequence_number => c_obsol_appl_rec.sequence_number,
1929 p_adm_outcome_status => c_defaults_rec.obsolete_outcome_status,
1930 p_s_adm_outcome_status => l_s_obsol_ou_stat ,
1931 p_acad_cal_type => c_obsol_appl_rec.acad_cal_type ,
1932 p_acad_ci_sequence_number => c_obsol_appl_rec.acad_ci_sequence_number,
1933 p_adm_cal_type => c_obsol_appl_rec.adm_cal_type ,
1934 p_adm_ci_sequence_number => c_obsol_appl_rec.adm_ci_sequence_number ,
1935 p_admission_cat => c_obsol_appl_rec.admission_cat ,
1936 p_s_admission_process_type => c_obsol_appl_rec.s_admission_process_type ,
1937 p_batch_id => l_dec_batch_id,
1938 p_interface_run_id => l_interface_run_id ,
1939 p_interface_mkdes_id => l_interface_mkdes_id,
1940 p_error_message => l_error_message, --Bug 3297241 replaced error_code with error_message
1941 p_return_status => l_return_status ,
1942 p_ucas_transaction => 'N',
1943 p_reconsideration => 'N' );
1944
1945 -- if the decision import completed in error then set appropriate error code in app choice record
1946 IF l_error_message IS NOT NULL OR l_return_status = 'FALSE' THEN
1947 /* raise the error with code 'O002' */
1948 fnd_message.set_name('IGS','IGS_UC_OBS_APP_DEC_IMP_ERR');
1949 fnd_message.set_token('APP_NO', c_new_app_ch_rec.app_no);
1950 fnd_message.set_token('CHOICE_NO', c_new_app_ch_rec.choice_no);
1951 fnd_message.set_token('BATCH_ID', l_dec_batch_id);
1952 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
1953 l_ch_error := 'O002' ;
1954 l_ch_batch_id := l_dec_batch_id ;
1955 l_export_to_oss_status := 'OO' ;
1956 ELSE
1957 -- decision import for obsoletion is successful
1958 l_ch_error := NULL ;
1959 l_ch_batch_id := NULL ;
1960 l_export_to_oss_status := 'OC' ;
1961
1962 -- Log a message that the application choice has been successfully obsoleted
1963 fnd_message.set_name('IGS','IGS_UC_OBS_APP_DEC_IMP_SUC');
1964 fnd_message.set_token('APP_NO', c_new_app_ch_rec.app_no);
1965 fnd_message.set_token('CHOICE_NO', c_new_app_ch_rec.choice_no);
1966 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
1967 END IF ; -- decision import failed or passed
1968
1969 END IF ; -- preference increment raised exception
1970
1971 END IF ; -- If more than one application instance active
1972
1973 END IF ; -- if no new application or instance is created
1974
1975 END IF; -- If the Application Choice has a Completed OSS Admission Application Instance.
1976 CLOSE cur_comp_app_choice;
1977
1978 -- 4. Update the Application Choice Record with export_to_oss_status=OP/OO/OC and Concurrent Request ID
1979 c_upd_ch_rec := NULL ;
1980 OPEN c_upd_ch( c_new_app_ch_rec.app_no , c_new_app_ch_rec.choice_no, c_new_app_ch_rec.ucas_cycle);
1981 FETCH c_upd_ch INTO c_upd_ch_rec ;
1982 CLOSE c_upd_ch ;
1983 igs_uc_app_choices_pkg.update_row
1984 ( x_rowid => c_upd_ch_rec.rowid
1985 ,x_app_choice_id => c_upd_ch_rec.app_choice_id
1986 ,x_app_id => c_upd_ch_rec.app_id
1987 ,x_app_no => c_upd_ch_rec.app_no
1988 ,x_choice_no => c_upd_ch_rec.choice_no
1989 ,x_last_change => c_upd_ch_rec.last_change
1990 ,x_institute_code => c_upd_ch_rec.institute_code
1991 ,x_ucas_program_code => c_upd_ch_rec.ucas_program_code
1992 ,x_oss_program_code => c_upd_ch_rec.oss_program_code
1993 ,x_oss_program_version => c_upd_ch_rec.oss_program_version
1994 ,x_oss_attendance_type => c_upd_ch_rec.oss_attendance_type
1995 ,x_oss_attendance_mode => c_upd_ch_rec.oss_attendance_mode
1996 ,x_campus => c_upd_ch_rec.campus
1997 ,x_oss_location => c_upd_ch_rec.oss_location
1998 ,x_faculty => c_upd_ch_rec.faculty
1999 ,x_entry_year => c_upd_ch_rec.entry_year
2000 ,x_entry_month => c_upd_ch_rec.entry_month
2001 ,x_point_of_entry => c_upd_ch_rec.point_of_entry
2002 ,x_home => c_upd_ch_rec.home
2003 ,x_deferred => c_upd_ch_rec.deferred
2004 ,x_route_b_pref_round => c_upd_ch_rec.route_b_pref_round
2005 ,x_route_b_actual_round => c_upd_ch_rec.route_b_actual_round
2006 ,x_condition_category => c_upd_ch_rec.condition_category
2007 ,x_condition_code => c_upd_ch_rec.condition_code
2008 ,x_decision => c_upd_ch_rec.decision
2009 ,x_decision_date => c_upd_ch_rec.decision_date
2010 ,x_decision_number => c_upd_ch_rec.decision_number
2011 ,x_reply => c_upd_ch_rec.reply
2012 ,x_summary_of_cond => c_upd_ch_rec.summary_of_cond
2013 ,x_choice_cancelled => c_upd_ch_rec.choice_cancelled
2014 ,x_action => c_upd_ch_rec.action
2015 ,x_substitution => c_upd_ch_rec.substitution
2016 ,x_date_substituted => c_upd_ch_rec.date_substituted
2017 ,x_prev_institution => c_upd_ch_rec.prev_institution
2018 ,x_prev_course => c_upd_ch_rec.prev_course
2019 ,x_prev_campus => c_upd_ch_rec.prev_campus
2020 ,x_ucas_amendment => c_upd_ch_rec.ucas_amendment
2021 ,x_withdrawal_reason => c_upd_ch_rec.withdrawal_reason
2022 ,x_offer_course => c_upd_ch_rec.offer_course
2023 ,x_offer_campus => c_upd_ch_rec.offer_campus
2024 ,x_offer_crse_length => c_upd_ch_rec.offer_crse_length
2025 ,x_offer_entry_month => c_upd_ch_rec.offer_entry_month
2026 ,x_offer_entry_year => c_upd_ch_rec.offer_entry_year
2027 ,x_offer_entry_point => c_upd_ch_rec.offer_entry_point
2028 ,x_offer_text => c_upd_ch_rec.offer_text
2029 ,x_export_to_oss_status => l_export_to_oss_status
2030 ,x_error_code => l_ch_error
2031 ,x_request_id => l_conc_request_id
2032 ,x_batch_id => l_ch_batch_id
2033 ,x_mode => 'R'
2034 ,x_extra_round_nbr => c_upd_ch_rec.extra_round_nbr
2035 ,x_system_code => c_upd_ch_rec.system_code
2036 ,x_part_time => c_upd_ch_rec.part_time
2037 ,x_interview => c_upd_ch_rec.interview
2038 ,x_late_application => c_upd_ch_rec.late_application
2039 ,x_modular => c_upd_ch_rec.modular
2040 ,x_residential => c_upd_ch_rec.residential
2041 ,x_ucas_cycle => c_upd_ch_rec.ucas_cycle);
2042
2043 COMMIT ;
2044 END LOOP ; -- loop thru new choices
2045
2046 -- for any other choice records in status OO due to previous runs
2047 -- then change their status if they are successfully imported
2048 FOR c_oo_ch_rec IN c_oo_ch LOOP
2049
2050 -- smaddali added the code to get the system code and the proper batchid for that system , as part
2051 -- of UCFD102 build , bug 2643048
2052 -- Get the default UCAS setup values and keep them in package variable c_defaults_rec
2053 c_defaults_rec := NULL ;
2054 OPEN c_defaults(c_oo_ch_rec.system_code) ;
2055 FETCH c_defaults INTO c_defaults_rec;
2056 CLOSE c_defaults ;
2057
2058 -- Get the Batch ID created for the calendar setup for this Application choice corresponding to the UCAS system, Entry Year and Entry Month
2059 -- available in Admission Decision Import Batch table,IGS_AD_BATC_DEF_DET_ALL
2060 l_dec_batch_id := NULL ;
2061 l_batch_id_loc :=NULL;
2062 get_batchid_loc(p_system_code => c_oo_ch_rec.system_code,
2063 p_entry_year => c_oo_ch_rec.entry_year,
2064 p_entry_month => c_oo_ch_rec.entry_month,
2065 p_batch_id_loc => l_batch_id_loc);
2066 IF l_batch_id_loc IS NOT NULL THEN
2067 l_dec_batch_id := l_batch_id_det(l_batch_id_loc).batch_id;
2068 ELSE
2069 --If batch ID is not availabe then assign it as zero. So that the application choice
2070 -- will be identified as errored in previous run and necessary checks will be performed
2071 -- to move the application choice status to OC.
2072 l_dec_batch_id := 0;
2073 END IF;
2074
2075 -- If this application choice errored out during a previous run
2076 -- then check if old application obsoletion has been successfull or not and
2077 -- update the choice status accordingly
2078 IF c_oo_ch_rec.batch_id <> l_dec_batch_id THEN
2079 c_obsol_appl_rec := NULL;
2080 OPEN c_obsol_appl(c_oo_ch_rec.app_no,
2081 c_oo_ch_rec.choice_no );
2082 FETCH c_obsol_appl INTO c_obsol_appl_rec ;
2083 CLOSE c_obsol_appl ;
2084
2085 -- Find the decision interface record corresponding to the application choice
2086 c_dec_int_rec := NULL ;
2087 OPEN c_dec_int(c_oo_ch_rec.batch_id,c_obsol_appl_rec.person_id,
2088 c_obsol_appl_rec.admission_appl_number,
2089 c_obsol_appl_rec.nominated_course_cd,
2090 c_obsol_appl_rec.sequence_number);
2091 FETCH c_dec_int INTO c_dec_int_rec ;
2092 CLOSE c_dec_int ;
2093 -- if the decision import completed in error then set appropriate error code in app choice record
2094 IF c_dec_int_rec.error_code IS NOT NULL OR c_dec_int_rec.status IN ('2','3') THEN
2095 fnd_message.set_name('IGS','IGS_UC_OBS_APP_DEC_IMP_ERR');
2096 fnd_message.set_token('APP_NO', c_oo_ch_rec.app_no);
2097 fnd_message.set_token('CHOICE_NO', c_oo_ch_rec.choice_no);
2098 fnd_message.set_token('BATCH_ID', c_oo_ch_rec.batch_id );
2099 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
2100 ELSE
2101 l_ch_error := NULL ;
2102 l_ch_batch_id := NULL ;
2103 l_export_to_oss_status := 'OC' ;
2104
2105 -- Log a message that the application choice has been successfully obsoleted
2106 fnd_message.set_name('IGS','IGS_UC_OBS_APP_DEC_IMP_SUC');
2107 fnd_message.set_token('APP_NO', c_oo_ch_rec.app_no);
2108 fnd_message.set_token('CHOICE_NO', c_oo_ch_rec.choice_no);
2109 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
2110
2111 -- 1. Update the Application Choice Record with export_to_oss_status=OP/OO/OC and Concurrent Request ID
2112 c_upd_ch_rec := NULL ;
2113 OPEN c_upd_ch( c_oo_ch_rec.app_no , c_oo_ch_rec.choice_no, c_oo_ch_rec.ucas_cycle) ;
2114 FETCH c_upd_ch INTO c_upd_ch_rec ;
2115 CLOSE c_upd_ch ;
2116 igs_uc_app_choices_pkg.update_row
2117 ( x_rowid => c_upd_ch_rec.ROWID
2118 ,x_app_choice_id => c_upd_ch_rec.app_choice_id
2119 ,x_app_id => c_upd_ch_rec.app_id
2120 ,x_app_no => c_upd_ch_rec.app_no
2121 ,x_choice_no => c_upd_ch_rec.choice_no
2122 ,x_last_change => c_upd_ch_rec.last_change
2123 ,x_institute_code => c_upd_ch_rec.institute_code
2124 ,x_ucas_program_code => c_upd_ch_rec.ucas_program_code
2125 ,x_oss_program_code => c_upd_ch_rec.oss_program_code
2126 ,x_oss_program_version => c_upd_ch_rec.oss_program_version
2127 ,x_oss_attendance_type => c_upd_ch_rec.oss_attendance_type
2128 ,x_oss_attendance_mode => c_upd_ch_rec.oss_attendance_mode
2129 ,x_campus => c_upd_ch_rec.campus
2130 ,x_oss_location => c_upd_ch_rec.oss_location
2131 ,x_faculty => c_upd_ch_rec.faculty
2132 ,x_entry_year => c_upd_ch_rec.entry_year
2133 ,x_entry_month => c_upd_ch_rec.entry_month
2134 ,x_point_of_entry => c_upd_ch_rec.point_of_entry
2135 ,x_home => c_upd_ch_rec.home
2136 ,x_deferred => c_upd_ch_rec.deferred
2137 ,x_route_b_pref_round => c_upd_ch_rec.route_b_pref_round
2138 ,x_route_b_actual_round => c_upd_ch_rec.route_b_actual_round
2139 ,x_condition_category => c_upd_ch_rec.condition_category
2140 ,x_condition_code => c_upd_ch_rec.condition_code
2141 ,x_decision => c_upd_ch_rec.decision
2142 ,x_decision_date => c_upd_ch_rec.decision_date
2143 ,x_decision_number => c_upd_ch_rec.decision_number
2144 ,x_reply => c_upd_ch_rec.reply
2145 ,x_summary_of_cond => c_upd_ch_rec.summary_of_cond
2146 ,x_choice_cancelled => c_upd_ch_rec.choice_cancelled
2147 ,x_action => c_upd_ch_rec.action
2148 ,x_substitution => c_upd_ch_rec.substitution
2149 ,x_date_substituted => c_upd_ch_rec.date_substituted
2150 ,x_prev_institution => c_upd_ch_rec.prev_institution
2151 ,x_prev_course => c_upd_ch_rec.prev_course
2152 ,x_prev_campus => c_upd_ch_rec.prev_campus
2153 ,x_ucas_amendment => c_upd_ch_rec.ucas_amendment
2154 ,x_withdrawal_reason => c_upd_ch_rec.withdrawal_reason
2155 ,x_offer_course => c_upd_ch_rec.offer_course
2156 ,x_offer_campus => c_upd_ch_rec.offer_campus
2157 ,x_offer_crse_length => c_upd_ch_rec.offer_crse_length
2158 ,x_offer_entry_month => c_upd_ch_rec.offer_entry_month
2159 ,x_offer_entry_year => c_upd_ch_rec.offer_entry_year
2160 ,x_offer_entry_point => c_upd_ch_rec.offer_entry_point
2161 ,x_offer_text => c_upd_ch_rec.offer_text
2162 ,x_export_to_oss_status => l_export_to_oss_status
2163 ,x_error_code => l_ch_error
2164 ,x_request_id => l_conc_request_id
2165 ,x_batch_id => l_ch_batch_id
2166 ,x_mode => 'R'
2167 ,x_extra_round_nbr => c_upd_ch_rec.extra_round_nbr
2168 ,x_system_code => c_upd_ch_rec.system_code
2169 ,x_part_time => c_upd_ch_rec.part_time
2170 ,x_interview => c_upd_ch_rec.interview
2171 ,x_late_application => c_upd_ch_rec.late_application
2172 ,x_modular => c_upd_ch_rec.modular
2173 ,x_residential => c_upd_ch_rec.residential
2174 ,x_ucas_cycle => c_upd_ch_rec.ucas_cycle);
2175 END IF ; -- If decision import errored out
2176
2177 END IF ; -- if this choice is a rerun
2178
2179 END LOOP ; -- check for choices of previous runs in status oc
2180
2181
2182 EXCEPTION
2183
2184 WHEN OTHERS THEN
2185 Rollback;
2186 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2187 fnd_message.set_token('NAME','IGS_UCAS_EXPORT_TO_OSS.OBSOLETE_APPLICATIONS'||' - '||SQLERRM);
2188 fnd_file.put_line(fnd_file.LOG,fnd_message.get());
2189 IGS_GE_MSG_STACK.ADD;
2190 App_Exception.Raise_Exception;
2191 END obsolete_applications ;
2192
2193
2194
2195 PROCEDURE export_applications(
2196 p_app_no igs_uc_applicants.app_no%TYPE ,
2197 p_choice_no igs_uc_app_choices.choice_no%TYPE
2198 ) IS
2199 /******************************************************************
2200 Created By : smaddali
2201 Date Created By : 12-SEP-2002
2202 Purpose : To create new application/instance or update old applications for choices in status OC
2203 Known limitations,enhancements,remarks:
2204 Change History
2205 Who When What
2206 smaddali bug 2643048 UCFD102 build , modified cursors to add check for system_code
2207 rbezawad 25-Feb-03 Modified w.r.t. Bug 2777247. Added code to insert record into IGS_AD_IMP_BATCH_DET table.
2208 dsridhar 02-JUN-03 Modified the cursor 'c_oc_app_ch' in the procedure 'export_applications' to tune the
2209 performance.Bug No: 2913922
2210 dsridhar 27-OCT-03 Bug No: 2898153. Removed the space from the message name IGS_UC_EXPORT_APP_ERR.
2211 jchakrab 20-feb-06 Modified for 3691186 - changed c_oc_app_ch to a ref cursor to execute different queries based on parameter values
2212 ***************************************************************** */
2213
2214 --jchakrab added for 3691186
2215 TYPE t_oc_app_ch IS REF CURSOR;
2216 c_oc_app_ch t_oc_app_ch;
2217
2218 l_app_no IGS_UC_APPLICANTS.APP_NO%TYPE;
2219
2220 l_imp_batch_id NUMBER;
2221 l_export_to_oss_status igs_uc_app_choices.export_to_oss_status%TYPE ;
2222 l_ch_error igs_uc_app_choices.error_code%TYPE ;
2223 l_ch_batch_id igs_uc_app_choices.batch_id%TYPE ;
2224 l_conc_process BOOLEAN ;
2225 l_phase VARCHAR2(10) ;
2226 l_status VARCHAR2(10) ;
2227 l_dev_phase VARCHAR2(10) ;
2228 l_dev_status VARCHAR2(10) ;
2229 l_message VARCHAR2(100) ;
2230
2231
2232 -- Get all the application choices with status AP
2233 -- smaddali modified this cursor to add the validation for system_code , UCFD102 build bug 2643048
2234 CURSOR c_ap_ch IS
2235 SELECT ch.app_no , ch.choice_no , ch.batch_id, ch.ucas_cycle
2236 FROM igs_uc_app_choices ch
2237 WHERE ch.app_no = NVL(p_app_no,ch.app_no) AND
2238 ch.export_to_oss_status = 'AP' AND
2239 ch.choice_no = NVL(p_choice_no , ch.choice_no) AND
2240 ch.institute_code = (SELECT df.current_inst_code FROM igs_uc_defaults df
2241 WHERE df.system_code = ch.system_code)
2242 ORDER BY ch.app_no , ch.choice_no ;
2243 c_ap_ch_rec c_ap_ch%ROWTYPE ;
2244
2245 -- Get the Batch ID for admission application import process
2246 CURSOR c_bat_id IS
2247 SELECT igs_ad_interface_batch_id_s.NEXTVAL
2248 FROM dual;
2249
2250 -- Get the Source type ID of UCAS for admission import process
2251 CURSOR c_src_type_id IS
2252 SELECT source_type_id
2253 FROM igs_pe_src_types_all
2254 WHERE source_type LIKE 'UCAS APPL';
2255 c_src_type_id_rec c_src_type_id%ROWTYPE;
2256
2257 -- Get the admission application instance interface record corresponding to the
2258 -- passed ucas application choice record whose import has failed
2259 -- smaddali modified this cursor to add b.alt_appl_id = a.app_no condition as part of ucfd102 build, bug 2643048
2260 CURSOR c_appl_int( cp_batch_id igs_uc_app_choices.batch_id%TYPE,
2261 cp_app_no igs_uc_app_choices.app_no%TYPE ,
2262 cp_choice_no igs_uc_app_choices.choice_no%TYPE ) IS
2263 SELECT a.interface_id -- if application details import fails
2264 FROM igs_ad_interface a, igs_ad_apl_int b , igs_ad_ps_appl_inst_int c ,
2265 igs_uc_app_choices ch , igs_uc_applicants ap
2266 WHERE a.batch_id = cp_batch_id AND
2267 a.interface_id = b.interface_id AND
2268 b.choice_number = ch.choice_no AND
2269 b.interface_appl_id = c.interface_appl_id AND
2270 ( c.status IN ( '2','3') OR b.status IN ('2','3') ) AND
2271 ap.app_no = ch.app_no AND
2272 ap.oss_person_id = a.person_id AND
2273 TO_CHAR(ap.app_no) = b.alt_appl_id AND
2274 ch.app_no = cp_app_no AND
2275 ch.choice_no = cp_choice_no
2276 UNION -- if person or person details import fail
2277 SELECT a.interface_id
2278 FROM igs_ad_interface a, igs_ad_apl_int b , igs_ad_ps_appl_inst_int c ,
2279 igs_uc_app_choices ch , igs_uc_applicants ap
2280 WHERE a.batch_id = cp_batch_id AND
2281 a.interface_id = b.interface_id AND
2282 b.choice_number = ch.choice_no AND
2283 b.interface_appl_id = c.interface_appl_id AND
2284 b.status = '2' AND
2285 c.status='2' AND
2286 ( a.record_status = '3' OR a.status IN ('2', '3' ) ) AND
2287 ap.app_no = ch.app_no AND
2288 ap.oss_person_id = a.person_id AND
2289 TO_CHAR(ap.app_no) = b.alt_appl_id AND
2290 ch.app_no = cp_app_no AND
2291 ch.choice_no = cp_choice_no ;
2292 c_appl_int_rec c_appl_int%ROWTYPE ;
2293
2294 /* Cursors used to import domicile code */
2295 CURSOR cur_app_dtls(cp_app_no igs_uc_applicants.app_no%TYPE) IS
2296 SELECT a.oss_person_id, a.domicile_apr, b.party_number
2297 FROM igs_uc_applicants a, hz_parties b
2298 WHERE a.oss_person_id = b.party_id
2299 AND app_no = cp_app_no;
2300 l_app_dtls cur_app_dtls%ROWTYPE;
2301
2302 CURSOR cur_ad_appl_inst (p_per_id igs_pe_person.person_id%TYPE) IS
2303 SELECT admission_appl_number,
2304 nominated_course_cd,
2305 sequence_number
2306 FROM igs_ad_ps_appl_inst_all
2307 WHERE person_id = p_per_id;
2308
2309 CURSOR cur_he_ad_dtl_all(p_per_id igs_pe_person.person_id%TYPE) IS
2310 SELECT COUNT(*)
2311 FROM igs_he_ad_dtl_all
2312 WHERE person_id = p_per_id;
2313
2314 CURSOR get_had_details (l_per_id igs_pe_person.person_id%TYPE) IS
2315 SELECT had.ROWID ,had.*
2316 FROM igs_he_ad_dtl_all had
2317 WHERE person_id = l_per_id;
2318
2319 -- UCAS Association mapping
2320 CURSOR cur_ucas_oss_map (cp_assoc IGS_HE_CODE_MAP_VAL.association_code%TYPE,
2321 cp_map1 IGS_HE_CODE_MAP_VAL.map2%TYPE ) IS
2322 SELECT map2
2323 FROM IGS_HE_CODE_MAP_VAL
2324 WHERE association_code = cp_assoc
2325 AND map1 = cp_map1;
2326
2327 l_dom_cd igs_he_ad_dtl_all.domicile_cd%TYPE;
2328 l_count NUMBER;
2329 l_rowid VARCHAR2(250);
2330
2331 BEGIN
2332 l_imp_batch_id := NULL ;
2333
2334 -- Get the distinct applicants who have choices in status OC
2335 -- smaddali modified this cursor to add the validation for system_code , UCFD102 build bug 2643048
2336 -- jchakrab changed c_oc_app_ch to a ref cursor to execute different queries based on parameter values (3691186)
2337 IF p_app_no IS NOT NULL AND p_choice_no IS NOT NULL THEN
2338 OPEN c_oc_app_ch FOR
2339 SELECT DISTINCT AP.APP_NO
2340 FROM IGS_UC_APP_CHOICES CH , IGS_UC_APPLICANTS AP, IGS_UC_DEFAULTS DF
2341 WHERE AP.APP_NO = CH.APP_NO AND
2342 CH.APP_NO = P_APP_NO AND
2343 DF.SYSTEM_CODE = CH.SYSTEM_CODE AND
2344 CH.EXPORT_TO_OSS_STATUS = 'OC' AND
2345 CH.CHOICE_NO = P_CHOICE_NO AND
2346 CH.INSTITUTE_CODE = DF.CURRENT_INST_CODE
2347 ORDER BY AP.APP_NO;
2348
2349 ELSIF p_app_no IS NOT NULL AND p_choice_no IS NULL THEN
2350 OPEN c_oc_app_ch FOR
2351 SELECT DISTINCT AP.APP_NO
2352 FROM IGS_UC_APP_CHOICES CH , IGS_UC_APPLICANTS AP, IGS_UC_DEFAULTS DF
2353 WHERE AP.APP_NO = CH.APP_NO AND
2354 CH.APP_NO = P_APP_NO AND
2355 DF.SYSTEM_CODE = CH.SYSTEM_CODE AND
2356 CH.EXPORT_TO_OSS_STATUS = 'OC' AND
2357 CH.INSTITUTE_CODE = DF.CURRENT_INST_CODE
2358 ORDER BY AP.APP_NO;
2359
2360 ELSE
2361 OPEN c_oc_app_ch FOR
2362 SELECT DISTINCT AP.APP_NO
2363 FROM IGS_UC_APP_CHOICES CH , IGS_UC_APPLICANTS AP, IGS_UC_DEFAULTS DF
2364 WHERE AP.APP_NO = CH.APP_NO AND
2365 DF.SYSTEM_CODE = CH.SYSTEM_CODE AND
2366 CH.EXPORT_TO_OSS_STATUS = 'OC' AND
2367 CH.INSTITUTE_CODE = DF.CURRENT_INST_CODE
2368 ORDER BY AP.APP_NO;
2369
2370 END IF;
2371
2372 LOOP
2373 FETCH c_oc_app_ch INTO l_app_no;
2374 EXIT WHEN c_oc_app_ch%NOTFOUND;
2375
2376 -- check if there are any application choice records in status OC which need to be imported
2377 -- if so ,get the batch_id and source_type_id
2378
2379 --only need to populate batch id once for all applicants
2380 IF l_imp_batch_id IS NULL THEN
2381
2382 -- Get the batch ID for running the application import process
2383 OPEN c_bat_id;
2384 FETCH c_bat_id
2385 INTO l_imp_batch_id;
2386 CLOSE c_bat_id;
2387
2388 INSERT INTO igs_ad_imp_batch_det ( batch_id,
2389 batch_desc,
2390 created_by,
2391 creation_date,
2392 last_updated_by,
2393 last_update_date,
2394 last_update_login,
2395 request_id,
2396 program_application_id,
2397 program_update_date,
2398 program_id)
2399 VALUES ( l_imp_batch_id,
2400 fnd_message.get_string('IGS','IGS_UC_EXP_TO_OSS_BATCH_ID'),
2401 fnd_global.user_id,
2402 SYSDATE,
2403 fnd_global.user_id,
2404 SYSDATE,
2405 fnd_global.login_id,
2406 DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_request_id),
2407 DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.prog_appl_id),
2408 DECODE(fnd_global.conc_request_id,-1,NULL,SYSDATE),
2409 DECODE(fnd_global.conc_request_id,-1,NULL,fnd_global.conc_program_id)
2410 );
2411
2412 -- Get the ucas cource type id for import process
2413 c_src_type_id_rec := NULL ;
2414 OPEN c_src_type_id;
2415 FETCH c_src_type_id
2416 INTO c_src_type_id_rec;
2417 CLOSE c_src_type_id;
2418 END IF ;
2419
2420 -- populate application import interface tables to create new application / instance
2421 -- or update the existing application instance corresponding to all the choice records
2422 -- belonging to the current application
2423 populate_imp_int(l_app_no,p_choice_no, c_src_type_id_rec.source_type_id, l_imp_batch_id, l_org_id);
2424
2425 END LOOP ;
2426 CLOSE c_oc_app_ch ;
2427
2428
2429 -- run the application import process to create / update admission applications
2430 -- corresponding to the ucas application choice records with status AP
2431 OPEN c_ap_ch ;
2432 FETCH c_ap_ch INTO c_ap_ch_rec ;
2433 IF c_ap_ch%FOUND AND l_imp_batch_id IS NOT NULL THEN
2434
2435 -- call the application import process to create new application /instance
2436 -- or update the old application instance
2437 fnd_file.put_line( fnd_file.LOG ,' ');
2438 fnd_message.set_name('IGS','IGS_UC_ADM_IMP_PROC_LAUNCH');
2439 fnd_message.set_token('REQ_ID',TO_CHAR(l_imp_batch_id));
2440 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2441 fnd_file.put_line( fnd_file.LOG ,'-----------------------------------');
2442
2443 COMMIT ;
2444 import_process(c_src_type_id_rec.source_type_id, l_imp_batch_id, l_org_id);
2445
2446 END IF ;
2447 CLOSE c_ap_ch ;
2448
2449 -- Insert space in log file
2450 fnd_file.put_line( fnd_file.LOG ,' ');
2451
2452 -- check if the application import has been successful for all application choices with status AP
2453 FOR c_ap_ch_rec IN c_ap_ch LOOP
2454
2455 -- corresponding to the current application choice record ,If an admission application interface table
2456 -- record exists then this means that the import process failed for this application.
2457 -- Hence update application choice accordingly with error code, batch_id export_to_oss_status and request Id
2458 OPEN c_appl_int(NVL(c_ap_ch_rec.batch_id,l_imp_batch_id ),
2459 c_ap_ch_rec.app_no, c_ap_ch_rec.choice_no);
2460 FETCH c_appl_int INTO c_appl_int_rec ;
2461 IF c_appl_int%FOUND THEN
2462 -- Import of this application has completed in error
2463 fnd_file.put_line( fnd_file.LOG ,' ');
2464 -- Bug No: 2898153. Removed the space from the message name IGS_UC_EXPORT_APP_ERR.
2465 fnd_message.set_name('IGS','IGS_UC_EXPORT_APP_ERR');
2466 fnd_message.set_token('APP_NO',c_ap_ch_rec.app_no) ;
2467 fnd_message.set_token('CHOICE_NO',c_ap_ch_rec.choice_no) ;
2468 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2469
2470 l_ch_error := 'A001' ;
2471 l_ch_batch_id := NVL(c_ap_ch_rec.batch_id,l_imp_batch_id ) ;
2472 l_export_to_oss_status := 'AP';
2473 ELSE
2474 -- choice successfully imported to oss
2475 l_ch_error := NULL ;
2476 l_ch_batch_id := NULL ;
2477 l_export_to_oss_status := 'AC';
2478 END IF ;
2479 CLOSE c_appl_int ;
2480
2481 -----------------------------------------------------------------------------------------------------------------
2482 /**** Import domicile code into igs_he_ad_dtl_all table ******/
2483 -----------------------------------------------------------------------------------------------------------------
2484 --If sent to oss succesfull we can create igs_he_ad_dtl record to import domicile code
2485 IF l_export_to_oss_status = 'AC' THEN
2486
2487 /* fetch the DOMICILE CODE for the applicant */
2488 l_dom_cd := NULL ;
2489
2490 OPEN cur_app_dtls(c_ap_ch_rec.app_no);
2491 FETCH cur_app_dtls INTO l_app_dtls;
2492 CLOSE cur_app_dtls;
2493
2494 IF l_app_dtls.domicile_apr IS NOT NULL THEN
2495
2496 OPEN cur_ucas_oss_map('UC_OSS_HE_DOM_ASSOC',l_app_dtls.domicile_apr);
2497 FETCH cur_ucas_oss_map INTO l_dom_cd;
2498 CLOSE cur_ucas_oss_map;
2499
2500 IF l_dom_cd IS NOT NULL THEN
2501
2502 l_count :=0 ;
2503 OPEN cur_he_ad_dtl_all(l_app_dtls.oss_person_id) ;
2504 FETCH cur_he_ad_dtl_all INTO l_count;
2505 CLOSE cur_he_ad_dtl_all ;
2506
2507 IF l_count = 0 THEN
2508
2509 FOR lv_ad_appl_inst IN cur_ad_appl_inst(l_app_dtls.oss_person_id) LOOP
2510
2511 l_rowid := NULL ;
2512 igs_he_ad_dtl_all_pkg.insert_row(
2513 x_rowid => l_rowid,
2514 x_org_id => NULL,
2515 x_hesa_ad_dtl_id => l_count,
2516 x_person_id => l_app_dtls.oss_person_id,
2517 x_admission_appl_number => lv_ad_appl_inst.admission_appl_number,
2518 x_nominated_course_cd => lv_ad_appl_inst.nominated_course_cd,
2519 x_sequence_number => lv_ad_appl_inst.sequence_number,
2520 x_occupation_cd => NULL,
2521 x_domicile_cd => l_dom_cd,
2522 x_social_class_cd => NULL,
2523 x_special_student_cd => NULL,
2524 x_mode => 'R' );
2525
2526 END LOOP ;
2527
2528 ELSE
2529
2530 FOR had_rec IN get_had_details(l_app_dtls.oss_person_id) LOOP
2531 igs_he_ad_dtl_all_pkg.update_row (
2532 x_mode => 'R',
2533 x_rowid => had_rec.ROWID,
2534 x_org_id => had_rec.org_id,
2535 x_hesa_ad_dtl_id => had_rec.hesa_ad_dtl_id,
2536 x_person_id => had_rec.person_id,
2537 x_admission_appl_number => had_rec.admission_appl_number,
2538 x_nominated_course_cd => had_rec.nominated_course_cd,
2539 x_sequence_number => had_rec.sequence_number,
2540 x_occupation_cd => had_rec.occupation_cd,
2541 x_domicile_cd => NVL(l_dom_cd,had_rec.domicile_cd),
2542 x_social_class_cd => had_rec.social_class_cd,
2543 x_special_student_cd => had_rec.special_student_cd );
2544
2545 END LOOP ;
2546
2547 END IF ; -- l_count = 0
2548
2549 ELSE
2550 -- Was unable to map domicile code so log warning
2551 FND_MESSAGE.Set_Name('IGS','IGS_UC_NO_DOM_MAPPING');
2552 FND_MESSAGE.Set_Token('PERSON_ID',l_app_dtls.party_number);
2553 FND_MESSAGE.Set_Token('CODE',l_app_dtls.domicile_apr);
2554 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.get );
2555
2556 END IF; -- l_dom_cd IS NOT NULL
2557
2558 END IF; -- cur_proc_applicant_rec.domicile_apr IS NOT NULL
2559
2560 END IF; -- l_export_to_oss_status = 'AC'
2561
2562 -- 1. Update the Application Choice Record with export_to_oss_status=OP/OO/OC ,batch id and Concurrent Request ID
2563 c_upd_ch_rec := NULL ;
2564 OPEN c_upd_ch( c_ap_ch_rec.app_no , c_ap_ch_rec.choice_no, c_ap_ch_rec.ucas_cycle) ;
2565 FETCH c_upd_ch INTO c_upd_ch_rec ;
2566 CLOSE c_upd_ch ;
2567 igs_uc_app_choices_pkg.update_row
2568 ( x_rowid => c_upd_ch_rec.ROWID
2569 ,x_app_choice_id => c_upd_ch_rec.app_choice_id
2570 ,x_app_id => c_upd_ch_rec.app_id
2571 ,x_app_no => c_upd_ch_rec.app_no
2572 ,x_choice_no => c_upd_ch_rec.choice_no
2573 ,x_last_change => c_upd_ch_rec.last_change
2574 ,x_institute_code => c_upd_ch_rec.institute_code
2575 ,x_ucas_program_code => c_upd_ch_rec.ucas_program_code
2576 ,x_oss_program_code => c_upd_ch_rec.oss_program_code
2577 ,x_oss_program_version => c_upd_ch_rec.oss_program_version
2578 ,x_oss_attendance_type => c_upd_ch_rec.oss_attendance_type
2579 ,x_oss_attendance_mode => c_upd_ch_rec.oss_attendance_mode
2580 ,x_campus => c_upd_ch_rec.campus
2581 ,x_oss_location => c_upd_ch_rec.oss_location
2582 ,x_faculty => c_upd_ch_rec.faculty
2583 ,x_entry_year => c_upd_ch_rec.entry_year
2584 ,x_entry_month => c_upd_ch_rec.entry_month
2585 ,x_point_of_entry => c_upd_ch_rec.point_of_entry
2586 ,x_home => c_upd_ch_rec.home
2587 ,x_deferred => c_upd_ch_rec.deferred
2588 ,x_route_b_pref_round => c_upd_ch_rec.route_b_pref_round
2589 ,x_route_b_actual_round => c_upd_ch_rec.route_b_actual_round
2590 ,x_condition_category => c_upd_ch_rec.condition_category
2591 ,x_condition_code => c_upd_ch_rec.condition_code
2592 ,x_decision => c_upd_ch_rec.decision
2593 ,x_decision_date => c_upd_ch_rec.decision_date
2594 ,x_decision_number => c_upd_ch_rec.decision_number
2595 ,x_reply => c_upd_ch_rec.reply
2596 ,x_summary_of_cond => c_upd_ch_rec.summary_of_cond
2597 ,x_choice_cancelled => c_upd_ch_rec.choice_cancelled
2598 ,x_action => c_upd_ch_rec.action
2599 ,x_substitution => c_upd_ch_rec.substitution
2600 ,x_date_substituted => c_upd_ch_rec.date_substituted
2601 ,x_prev_institution => c_upd_ch_rec.prev_institution
2602 ,x_prev_course => c_upd_ch_rec.prev_course
2603 ,x_prev_campus => c_upd_ch_rec.prev_campus
2604 ,x_ucas_amendment => c_upd_ch_rec.ucas_amendment
2605 ,x_withdrawal_reason => c_upd_ch_rec.withdrawal_reason
2606 ,x_offer_course => c_upd_ch_rec.offer_course
2607 ,x_offer_campus => c_upd_ch_rec.offer_campus
2608 ,x_offer_crse_length => c_upd_ch_rec.offer_crse_length
2609 ,x_offer_entry_month => c_upd_ch_rec.offer_entry_month
2610 ,x_offer_entry_year => c_upd_ch_rec.offer_entry_year
2611 ,x_offer_entry_point => c_upd_ch_rec.offer_entry_point
2612 ,x_offer_text => c_upd_ch_rec.offer_text
2613 ,x_export_to_oss_status => l_export_to_oss_status
2614 ,x_error_code => l_ch_error
2615 ,x_request_id => l_conc_request_id
2616 ,x_batch_id => l_ch_batch_id
2617 ,x_mode => 'R'
2618 ,x_extra_round_nbr => c_upd_ch_rec.extra_round_nbr
2619 ,x_system_code => c_upd_ch_rec.system_code
2620 ,x_part_time => c_upd_ch_rec.part_time
2621 ,x_interview => c_upd_ch_rec.interview
2622 ,x_late_application => c_upd_ch_rec.late_application
2623 ,x_modular => c_upd_ch_rec.modular
2624 ,x_residential => c_upd_ch_rec.residential
2625 ,x_ucas_cycle => c_upd_ch_rec.ucas_cycle);
2626
2627 END LOOP ;
2628
2629 EXCEPTION
2630 WHEN OTHERS THEN
2631 Rollback;
2632 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2633 fnd_message.set_token('NAME','IGS_UCAS_EXPORT_TO_OSS.EXPORT_APPLICATIONS'||' - '||SQLERRM);
2634 fnd_file.put_line(fnd_file.log,fnd_message.get());
2635 IGS_GE_MSG_STACK.ADD;
2636 App_Exception.Raise_Exception;
2637
2638 END export_applications ;
2639
2640
2641 PROCEDURE main_process(
2642 errbuf OUT NOCOPY VARCHAR2,
2643 retcode OUT NOCOPY NUMBER,
2644 p_app_no igs_uc_applicants.app_no%TYPE ,
2645 p_choice_no igs_uc_app_choices.choice_no%TYPE
2646 ) IS
2647 /******************************************************************
2648 Created By : smaddali
2649 Date Created By : 12-SEP-2002
2650 Purpose : Main process called from concurrent manager for "export applications to OSS" process
2651 Known limitations,enhancements,remarks:
2652 Change History
2653 Who When What
2654 jchakrab 11-Oct-2005 Added check for checking setup of CANCELLED system outcome status
2655 anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
2656 ***************************************************************** */
2657
2658 IGS_UC_HE_NOT_ENABLED_EXCEP EXCEPTION;
2659 l_no_setup BOOLEAN ;
2660 l_rep_request_id NUMBER ;
2661
2662 -- smaddali added this cursor for bug 2643048 ,
2663 -- to get all the distinct system_codes belonging to the passed application choice parameter
2664 CURSOR c_ch_system IS
2665 SELECT DISTINCT a.system_code
2666 FROM igs_uc_app_choices a
2667 WHERE a.app_no = NVL(p_app_no, a.app_no) AND
2668 a.choice_no = NVL(p_choice_no,a.choice_no) ;
2669
2670 -- smaddali added cursor ,for bug 2643048
2671 CURSOR c_defaults( cp_system_code igs_uc_defaults.system_code%TYPE) IS
2672 SELECT *
2673 FROM igs_uc_defaults def
2674 WHERE system_code = cp_system_code;
2675 c_defaults_rec c_defaults%ROWTYPE ;
2676
2677 --Curosor to get the Entry Year, Entry Month details that are need to check for calendar setup.
2678 CURSOR cur_app_choice_entry_det (cp_system_code igs_uc_defaults.system_code%TYPE) IS
2679 SELECT DISTINCT entry_year, entry_month
2680 FROM igs_uc_app_choices
2681 WHERE app_no = NVL(p_app_no, app_no)
2682 AND choice_no = NVL(p_choice_no, choice_no)
2683 AND system_code = cp_system_code;
2684
2685 --Cursor to get the Calendar details for the given System, Entry Month and Entry Year.
2686 CURSOR cur_sys_entry_cal_det (cp_system_code igs_uc_sys_calndrs.system_code%TYPE,
2687 cp_entry_year igs_uc_sys_calndrs.entry_year%TYPE,
2688 cp_entry_month igs_uc_sys_calndrs.entry_month%TYPE ) IS
2689 SELECT aca_cal_type,
2690 aca_cal_seq_no,
2691 adm_cal_type,
2692 adm_cal_seq_no
2693 FROM igs_uc_sys_calndrs
2694 WHERE system_code = cp_system_code
2695 AND entry_year = cp_entry_year
2696 AND (entry_month = cp_entry_month OR entry_month = 0)
2697 ORDER BY entry_month DESC;
2698
2699 l_sys_entry_cal_det_rec cur_sys_entry_cal_det%ROWTYPE;
2700
2701 --Cursor to check whether APC defined for the System includes the step "Reconsideration" or not.
2702 CURSOR cur_prcs_cat_step (cp_admission_cat igs_ad_ss_appl_typ.admission_cat%TYPE,
2703 cp_s_admission_process_type igs_ad_ss_appl_typ.s_admission_process_type%TYPE ) IS
2704 SELECT 'X'
2705 FROM igs_ad_prcs_cat_step
2706 WHERE admission_cat = cp_admission_cat
2707 AND s_admission_process_type = cp_s_admission_process_type
2708 AND s_admission_step_type = 'RECONSIDER';
2709
2710 l_found_flag VARCHAR2(1);
2711
2712 --Cursor to get the Admission Process Category and Admission Process Type for the
2713 --Admission Application Type defined for the System in UCAS Setup.
2714 CURSOR cur_apc_det ( cp_application_type igs_uc_defaults.application_type%TYPE) IS
2715 SELECT admission_cat, s_admission_process_type
2716 FROM igs_ad_ss_appl_typ
2717 WHERE admission_application_type = cp_application_type
2718 AND closed_ind = 'N';
2719
2720 l_apc_det_rec cur_apc_det%ROWTYPE;
2721
2722 BEGIN
2723
2724 --anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
2725 IGS_GE_GEN_003.SET_ORG_ID;
2726
2727 -- inititalize variables
2728 errbuf := NULL;
2729 retcode := 0;
2730 l_no_setup := FALSE ;
2731 -- Get the Concurrent Request ID of the current export of UCAS application to
2732 -- OSS admission applications run . this is a global variable
2733 l_conc_request_id := fnd_global.conc_request_id();
2734
2735 -- Checking whether the UK profile is enabled
2736 IF Not (igs_uc_utils.is_ucas_hesa_enabled) THEN
2737 Raise igs_uc_he_not_enabled_excep; -- user defined exception
2738 END IF;
2739
2740 -- smaddali added the for loop of the application choice systems for bug 2643048
2741 -- For each of the application choice ucas system , if setup is not proper then log an error
2742 FOR c_ch_system_rec IN c_ch_system LOOP
2743 -- Get the default UCAS setup values and keep them in package variable c_defaults_rec
2744 c_defaults_rec := NULL ;
2745 OPEN c_defaults(c_ch_system_rec.system_code) ;
2746 FETCH c_defaults INTO c_defaults_rec;
2747 CLOSE c_defaults ;
2748
2749 -- Check if the set up data for admission Application Type is found in ucas defaults form
2750 IF c_defaults_rec.application_type IS NULL THEN
2751 fnd_message.set_name('IGS','IGS_UC_SETUP_ADM_APPL_TYPE');
2752 fnd_message.set_token('SYSTEM', c_ch_system_rec.system_code);
2753 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2754 l_no_setup := TRUE ;
2755 END IF ;
2756
2757 --Check if the set up data for admission Application Type is found in ucas defaults form but the Application Type is closed.
2758 IF c_defaults_rec.application_type IS NOT NULL THEN
2759 l_apc_det_rec := NULL;
2760 --Get the APC details corresponding to the Application Type defined in UCAS Setup
2761 OPEN cur_apc_det(c_defaults_rec.application_type);
2762 FETCH cur_apc_det INTO l_apc_det_rec;
2763 CLOSE cur_apc_det;
2764 IF l_apc_det_rec.admission_cat IS NULL OR l_apc_det_rec.s_admission_process_type IS NULL THEN
2765 fnd_message.set_name('IGS','IGS_UC_SETUP_ADM_APC');
2766 fnd_message.set_token('SYSTEM', c_ch_system_rec.system_code);
2767 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2768 l_no_setup := TRUE ;
2769 ELSE
2770 l_found_flag := NULL;
2771 --Check whether APC defined for the System includes the step "Reconsideration" or not.
2772 OPEN cur_prcs_cat_step(l_apc_det_rec.admission_cat, l_apc_det_rec.s_admission_process_type);
2773 FETCH cur_prcs_cat_step INTO l_found_flag;
2774 CLOSE cur_prcs_cat_step;
2775
2776 --Log the error if Admission Process Category Step "Reconsideration" not exists for the given APC details.
2777 IF l_found_flag IS NULL THEN
2778 fnd_message.set_name('IGS','IGS_UC_APC_RECNSDR_NOT_INCL');
2779 fnd_message.set_token('PROCCAT', l_apc_det_rec.admission_cat);
2780 fnd_message.set_token('PROCTYPE', l_apc_det_rec.s_admission_process_type);
2781 fnd_message.set_token('SYSTEM', c_ch_system_rec.system_code);
2782 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2783 l_no_setup := TRUE ;
2784 END IF;
2785 END IF;
2786
2787 END IF;
2788
2789 FOR app_choice_entry_det_rec IN cur_app_choice_entry_det(c_ch_system_rec.system_code)
2790 LOOP
2791
2792 --Get the Calendar details for the given System, Entry Month and Entry Year from System Calendards table.
2793 l_sys_entry_cal_det_rec := NULL;
2794 OPEN cur_sys_entry_cal_det(c_ch_system_rec.system_code, app_choice_entry_det_rec.entry_year, app_choice_entry_det_rec.entry_month);
2795 FETCH cur_sys_entry_cal_det INTO l_sys_entry_cal_det_rec;
2796 CLOSE cur_sys_entry_cal_det;
2797
2798 --Log the error if calendar details are not found.
2799 IF l_sys_entry_cal_det_rec.adm_cal_type IS NULL OR l_sys_entry_cal_det_rec.adm_cal_seq_no IS NULL OR
2800 l_sys_entry_cal_det_rec.aca_cal_type IS NULL OR l_sys_entry_cal_det_rec.aca_cal_seq_no IS NULL THEN
2801 fnd_message.set_name('IGS','IGS_UC_NO_SYS_CAL_MAP_EXIST');
2802 fnd_message.set_token('SYSTEM', c_ch_system_rec.system_code);
2803 fnd_message.set_token('ENTRYYEAR', app_choice_entry_det_rec.entry_year);
2804 fnd_message.set_token('ENTRYMONTH', app_choice_entry_det_rec.entry_month);
2805 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2806 l_no_setup := TRUE ;
2807 END IF ;
2808
2809 END LOOP;
2810
2811 -- Check if the set up data for obsolete outcome status is found in ucas defaults form
2812 IF c_defaults_rec.obsolete_outcome_status IS NULL THEN
2813 fnd_message.set_name('IGS','IGS_UC_SETUP_OBS_OUSTAT');
2814 fnd_message.set_token('SYSTEM', c_ch_system_rec.system_code);
2815 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2816 l_no_setup := TRUE ;
2817 END IF;
2818
2819 -- Check if the set up data for pending outcome status is found in ucas defaults form
2820 IF c_defaults_rec.pending_outcome_status IS NULL THEN
2821 fnd_message.set_name('IGS','IGS_UC_SETUP_PENDING_OUSTAT');
2822 fnd_message.set_token('SYSTEM', c_ch_system_rec.system_code);
2823 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2824 l_no_setup := TRUE ;
2825 END IF;
2826
2827 -- Check if the set up data for rejected outcome status is found in ucas defaults form
2828 IF c_defaults_rec.rejected_outcome_status IS NULL THEN
2829 fnd_message.set_name('IGS','IGS_UC_SETUP_REJC_OUSTAT');
2830 fnd_message.set_token('SYSTEM', c_ch_system_rec.system_code);
2831 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2832 l_no_setup := TRUE ;
2833 END IF;
2834
2835 -- Check if the set up data for decision maker is found in ucas defaults form
2836 IF c_defaults_rec.decision_make_id IS NULL THEN
2837 fnd_message.set_name('IGS','IGS_UC_SETUP_DEC_MAKE');
2838 fnd_message.set_token('SYSTEM', c_ch_system_rec.system_code);
2839 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2840 l_no_setup := TRUE ;
2841 END IF;
2842
2843 -- Check if the set up data for decision reason is found in ucas defaults form
2844 IF c_defaults_rec.decision_reason_id IS NULL THEN
2845 fnd_message.set_name('IGS','IGS_UC_SETUP_DEC_REASON');
2846 fnd_message.set_token('SYSTEM', c_ch_system_rec.system_code);
2847 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2848 l_no_setup := TRUE ;
2849 END IF;
2850
2851 END LOOP ; -- checking for setup
2852
2853 --add check for checking whether CANCELLED system status has been mapped
2854 IF IGS_AD_GEN_009.Admp_Get_Sys_Aos('CANCELLED') IS NULL THEN
2855 fnd_message.set_name('IGS','IGS_UC_SETUP_CANCEL_OUSTAT');
2856 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2857 l_no_setup := TRUE ;
2858 END IF;
2859
2860 IF l_no_setup THEN
2861 -- end job in warning state
2862 retcode := 1 ;
2863 ELSE
2864
2865 c_defaults_rec := NULL ;
2866
2867 -- Call the sub process to obsolete the old admission application when
2868 -- the application choice results in a new application/instance
2869 -- for all application choices in status New
2870 fnd_file.put_line( fnd_file.LOG ,' ');
2871 FND_MESSAGE.SET_NAME('IGS','IGS_UC_OBSOL_APP');
2872 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2873 fnd_file.put_line( fnd_file.LOG ,'-----------------------------------');
2874
2875 obsolete_applications( p_app_no, p_choice_no) ;
2876
2877 -- Call the sub process to create new application/instance or update the existing admissionapplication
2878 -- for all application choices in status OC
2879 fnd_file.put_line( fnd_file.LOG ,' ');
2880 FND_MESSAGE.SET_NAME('IGS','IGS_UC_CRE_APP');
2881 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2882 fnd_file.put_line( fnd_file.LOG ,'-----------------------------------');
2883
2884 export_applications( p_app_no, p_choice_no) ;
2885
2886 -- Call the process to import the UCAS decision field into oss outcome status field
2887 -- for all application choices in status AC
2888 fnd_file.put_line( fnd_file.LOG ,' ');
2889 FND_MESSAGE.SET_NAME('IGS','IGS_UC_DEC_IMP');
2890 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2891 fnd_file.put_line( fnd_file.LOG ,'-----------------------------------');
2892
2893 igs_uc_export_decision_reply.export_decision( p_app_no, p_choice_no) ;
2894
2895 -- Call the process to import the UCAS Reply field into oss offer response status field
2896 -- for all application choices in status DC
2897 fnd_file.put_line( fnd_file.LOG ,' ');
2898 FND_MESSAGE.SET_NAME('IGS','IGS_UC_OFR_IMP');
2899 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2900 fnd_file.put_line( fnd_file.LOG ,'-----------------------------------');
2901
2902 igs_uc_export_decision_reply.export_reply( p_app_no, p_choice_no) ;
2903
2904 -- log message that the Export applications to oss process has completed
2905 fnd_file.put_line( fnd_file.LOG ,' ');
2906 fnd_message.set_name('IGS','IGS_UC_EXP_APP_PROC_COMP');
2907 fnd_file.put_line(fnd_file.log,fnd_message.get());
2908 fnd_file.put_line( fnd_file.LOG ,'-----------------------------------');
2909
2910 -- Submit the Error report to show the errors generated while exporting applications
2911 l_rep_request_id := NULL ;
2912 l_rep_request_id := Fnd_Request.Submit_Request
2913 ( 'IGS',
2914 'IGSUCS35',
2915 'Export Applications to OSS Error Report',
2916 NULL,
2917 FALSE,
2918 l_conc_request_id ,
2919 NULL,
2920 NULL,
2921 NULL,
2922 CHR(0) ,
2923 NULL, NULL, NULL, NULL, NULL,
2924 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2925 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2926 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2927 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2928 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2929 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2930 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2931 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2932 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
2933 IF l_rep_request_id > 0 THEN
2934 -- if error report successfully submitted then log message
2935 fnd_file.put_line( fnd_file.LOG ,' ');
2936 fnd_message.set_name('IGS','IGS_UC_REP_SUBM');
2937 fnd_message.set_token('REQ_ID',TO_CHAR(l_rep_request_id));
2938 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2939 ELSE
2940 -- if error report failed to be launched then log message
2941 fnd_message.set_name('IGS','IGS_UC_REP_SUBM_ERR');
2942 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
2943 END IF;
2944
2945 END IF; -- If ucas setup proper or not
2946
2947
2948 EXCEPTION
2949 WHEN IGS_UC_HE_NOT_ENABLED_EXCEP THEN
2950 -- ucas functionality is not enabled
2951 Errbuf := fnd_message.get_string ('IGS', 'IGS_UC_HE_NOT_ENABLED');
2952 Retcode := 2 ;
2953 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
2954
2955 WHEN OTHERS THEN
2956 Rollback;
2957 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2958 fnd_message.set_token('NAME','IGS_UCAS_EXPORT_TO_OSS.MAIN_PROCESS'||' - '||SQLERRM);
2959 fnd_message.retrieve (Errbuf);
2960 Retcode := 2 ;
2961 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
2962
2963 END main_process ;
2964
2965 END igs_uc_export_to_oss;
[Home] [Help]