1 PACKAGE BODY Igs_En_Elgbl_Override AS
2 /* $Header: IGSEN77B.pls 120.5 2006/08/18 12:14:21 amuthu noship $ */
3
4 /*-----------------------------------------------------------------------------------------------------
5 --Created by : knaraset ( Oracle IDC)
6 --Date created: 21-JUN-2001
7 --
8 --Known limitations/enhancements and/or remarks:
9 --
10 --Change History:
11 --Who When What
12 --amuthu 29-JAN-2003 As part of bug 2744715 change the message logging format for the
13 -- this job and also truncated the date being passed to the insert row
14 -- of IGS_EN_ELGB_OVR_STEP_PKG.
15 --svanukur 16-jun-2003 modified the UI and Validations associated with this job as part of
16 -- Validations ImpactCR , ENCR34, bug #2881385
17 --ptandon 02-Jul-2003 Modified the function enrp_chk_unit_over to allow unit level override
18 for multiple units/unit sections to a student in case of same unit
19 step type. Bug# 3033214
20 --rvangala 11-NOV-2003 Changed query of Cursor cur_criteria_satisfied to include call to
21 -- igs_en_gen_011.match_term_sca_params and included parameters
22 -- p_load_cal_type and p_load_cal_seq
23
24 --ckasu 18-FEB-2004 Modified enrp_elgbl_override procedure inorder to log message
25 IGS_EN_NO_STDNT_FOUND when no data is found.Bug No 3442015
26
27 --ckasu 11-APR-2006 Modified as a part of Sevis Build bug# 5140084.
28
29 --ckasu 13-JUN-2006 modified as a part of bug 5299024 inorder show proper error message
30 --amuthu 15-jun-2006 Modified enrp_chk_prg_over as part of bug 5335207/5330361.
31 --amuthu 18-Aug-2006 Modified enrp_chk_prg_over as part of bug 5435397
32 ------------------------------------------------------------------------------------------------------*/
33
34
35 PROCEDURE enrp_elgbl_override(
36 errbuf OUT NOCOPY VARCHAR2,
37 retcode OUT NOCOPY NUMBER,
38 p_trm_teach_cal_type_comb IN VARCHAR2,
39 p_program_cd_comb IN VARCHAR2,
40 p_location_cd IN VARCHAR2,
41 p_attendance_mode IN VARCHAR2,
42 p_attendance_type IN VARCHAR2,
43 p_unit_cd_comb IN VARCHAR2,
44 p_unit_set_comb IN VARCHAR2,
45 p_class_standing IN VARCHAR2,
46 p_org_unit_cd IN VARCHAR2,
47 p_person_id_group IN VARCHAR2,
48 p_person_id IN NUMBER,
49 p_program_attempt_status IN VARCHAR2,
50 p_person_step_1 IN VARCHAR2,
51 p_program_step_1 IN VARCHAR2,
52 p_over_credit_point_1 IN NUMBER,
53 p_unit_step_1 IN VARCHAR2,
54 p_unit_cd_1 IN VARCHAR2,
55 p_unit_section_1 IN NUMBER,
56 p_person_step_2 IN VARCHAR2,
57 p_program_step_2 IN VARCHAR2,
58 p_over_credit_point_2 IN NUMBER,
59 p_unit_step_2 IN VARCHAR2,
60 p_unit_cd_2 IN VARCHAR2,
61 p_unit_section_2 IN NUMBER,
62 p_person_step_3 IN VARCHAR2,
63 p_program_step_3 IN VARCHAR2,
64 p_unit_step_3 IN VARCHAR2,
65 p_unit_cd_3 IN VARCHAR2,
66 p_unit_section_3 IN NUMBER,
67 p_person_step_4 IN VARCHAR2,
68 p_program_step_4 IN VARCHAR2,
69 p_unit_step_4 IN VARCHAR2,
70 p_unit_cd_4 IN VARCHAR2,
71 p_unit_section_4 IN NUMBER,
72 p_person_ovr_step_1 IN VARCHAR2,
73 p_unit_ovr_step_1 IN VARCHAR2,
74 p_over_credit_point_3 IN NUMBER,
75 p_unit_cd_ovr_1 IN VARCHAR2,
76 p_unit_section_ovr_1 IN NUMBER,
77 p_person_ovr_step_2 IN VARCHAR2,
78 p_unit_ovr_step_2 IN VARCHAR2,
79 p_over_credit_point_4 IN NUMBER,
80 p_unit_cd_ovr_2 IN VARCHAR2,
81 p_unit_section_ovr_2 IN NUMBER,
82 p_person_ovr_step_3 IN VARCHAR2,
83 p_unit_ovr_step_3 IN VARCHAR2,
84 p_unit_cd_ovr_3 IN VARCHAR2,
85 p_unit_section_ovr_3 IN NUMBER,
86 p_person_ovr_step_4 IN VARCHAR2,
87 p_unit_ovr_step_4 IN VARCHAR2,
88 p_unit_cd_ovr_4 IN VARCHAR2,
89 p_unit_section_ovr_4 IN NUMBER,
90 p_org_id IN NUMBER,
91 p_sevis_auth_cd IN VARCHAR2,
92 p_comments IN VARCHAR2
93 ) AS
94 --------------------------------------------------------------------------------------------------------------------------------------------
95 --Created by : knaraset ( Oracle IDC)
96 --Date created: 21-JUN-2001
97 --
98 --Purpose: This procedure will create override records to the students who satisfied the given criteria,
99 -- corresponding to the all steps passed as parameters.
100 --
101 -- Flow of the process
102 --------------------------------------
103 --
104 -- ENRP_ELGBL_OVERRIDE ( ** Main Function )
105 -- |
106 -- | Function ENRP_LOG_PARA ( ** log parameters )
107 -- | Function ENRP_VAL_PARA ( ** Validate parameters )
108 -- | LOOP for all students
109 -- | Igs_En_Elgb_Ovr_Pkg.insert_row (** If not exists )
110 -- | Procedure ENRP_INS_STEP_OVER ( ** create override records for all steps passed )
111 -- | Procedure enrp_chk_pers_over/enrp_chk_prg_over/enrp_chk_unit_over ( ** based on the step type different call)
112 -- | Igs_En_Gen_015.validation_step_is_overridden ( ** whether step is overriden )
113 -- | Igs_En_Elgb_Ovr_Step_Pkg.insert_row ( ** create override record for the step )
114 --
115 --
116 --Known limitations/enhancements and/or remarks:
117 --
118 --Change History:
119 --Who When What
120 --kkillams 05-11-2002 As part of sevis build two new parameters p_sevis_auth_cd and p_comments are added.
121 -- bug no # 2641905
122 --svanukur 17-APR-2003 As part of bug #2744699, modified the parameters entered in the log file. Also used messages for prompts in the
123 -- log file instead of hard coded values for NLS compliance.
124 -- created cursors cur_person_id_grp, cur_person_id, cur_step and variable l_step to populate
125 -- the log file.
126 --rvangala 11-NOV-2003 Changed query of Cursor cur_criteria_satisfied to include call to
127 -- igs_en_gen_011.match_term_sca_params
128 --ckasu 18-FEB-2004 Variable l_not_found was initialised to FALSE and code was changed inorder to log a Message
129 -- 'No Student was selected for given Criteria' when no data was found for Override record Processing
130 -- as a part of bug 3442015
131 ---------------------------------------------------------------------------------------------------------------------------------------
132
133 CURSOR cur_elgb_over(p_person_id NUMBER,p_cal_type VARCHAR2,p_ci_seq_number NUMBER)IS
134 SELECT elgb_override_id
135 FROM igs_en_elgb_ovr
136 WHERE person_id = p_person_id AND
137 cal_type = p_cal_type AND
138 ci_sequence_number = p_ci_seq_number;
139
140 CURSOR cur_criteria_satisfied(p_course_cd VARCHAR2,p_course_version NUMBER,p_unit_cd VARCHAR2, p_unit_version NUMBER,
141 p_unit_set_cd VARCHAR2,p_unit_set_version NUMBER, p_org_unit_start_dt DATE,
142 p_load_cal_type VARCHAR2, p_load_cal_seq NUMBER) IS
143 SELECT DISTINCT(sca.person_id) person_id
144 FROM igs_en_stdnt_ps_att sca
145 WHERE (p_person_id IS NULL OR
146 sca.person_id = p_person_id) AND
147 (p_person_id_group IS NULL OR
148 EXISTS ( SELECT 'x'
149 FROM igs_pe_prsid_grp_mem pig
150 WHERE pig.group_id = p_person_id_group AND
151 pig.person_id = sca.person_id)) AND
152 sca.course_cd LIKE NVL(p_course_cd,'%') AND
153 igs_en_gen_011.match_term_sca_params (
154 sca.person_id,
155 sca.course_cd,
156 sca.version_number,
157 sca.attendance_type,
158 sca.attendance_mode,
159 sca.location_cd,
160 p_course_cd,
161 p_course_version,
162 p_attendance_type,
163 p_attendance_mode,
164 p_location_cd,
165 p_load_cal_type,
166 p_load_cal_seq) = 'Y'
167 AND
168 sca.course_attempt_status LIKE NVL(p_program_attempt_status,'%') AND
169 (p_unit_cd_comb IS NULL OR
170 EXISTS (SELECT 'X'
171 FROM igs_en_su_attempt sua
172 WHERE sua.person_id = sca.person_id AND
173 sua.course_cd = sca.course_cd AND
174 sua.unit_cd = p_unit_cd AND
175 sua.version_number = p_unit_version)) AND
176 (p_unit_set_comb IS NULL OR
177 EXISTS (SELECT 'X'
178 FROM igs_as_su_setatmpt
179 WHERE unit_set_cd = p_unit_set_cd AND
180 us_version_number = p_unit_set_version AND
181 person_id = sca.person_id AND
182 course_cd = sca.course_cd )) AND
183 /* (p_class_stand IS NULL OR
184 NVL(sca.class_standing_override,get_class_standing(sca.person_id,sca.course_cd,'N'))
185 = p_class_stand ) AND*/
186 (p_org_unit_cd IS NULL OR
187 EXISTS (SELECT 'X'
188 FROM igs_ps_ver ps
189 WHERE ps.course_cd = sca.course_cd AND
190 ps.version_number = sca.version_number AND
191 ((ps.responsible_org_unit_cd = p_org_unit_cd AND
192 ps.responsible_ou_start_dt = p_org_unit_start_dt) OR
193 igs_or_gen_001.orgp_get_within_ou(ps.responsible_org_unit_cd,
194 ps.responsible_ou_start_dt,
195 p_org_unit_cd,
196 p_org_unit_start_dt,
197 'N') = 'Y')));
198
199
200 CURSOR cur_org_unit_st_dt(p_org_unit_cd VARCHAR2) IS
201 SELECT start_dt
202 FROM igs_or_unit
203 WHERE org_unit_cd = p_org_unit_cd;
204
205 CURSOR cur_person_id_grp(p_person_id_group NUMBER) IS
206 SELECT PG.GROUP_CD||'-'||PG.GROUP_ID
207 FROM IGS_PE_PERSID_GROUP PG
208 WHERE PG.group_id = p_person_id_group;
209
210 CURSOR cur_person_id(p_person_id NUMBER) IS
211 SELECT PE.party_number person_number
212 FROM hz_parties PE
213 WHERE PE.party_id = p_person_id;
214
215 CURSOR cur_step(p_lookup_type VARCHAR2, p_step VARCHAR2) IS
216 SELECT LKV.meaning
217 FROM IGS_Lookup_values LKV
218 WHERE lkv.lookup_type = p_lookup_type AND
219 LKV.lookup_code = p_step;
220
221
222 CURSOR cur_get_cal_cat (cp_cal_type IGS_CA_TYPE.CAL_TYPE%TYPE) IS
223 SELECT s_cal_cat
224 FROM igs_ca_type
225 WHERE cal_type = cp_cal_type;
226
227 CURSOR cur_load_cal (cp_teach_cal_type VARCHAR2, cp_teach_ci_sequence_number NUMBER) IS
228 SELECT load_cal_type,load_ci_sequence_number
229 FROM igs_ca_teach_to_load_v
230 WHERE teach_cal_type= cp_teach_cal_type
231 AND teach_ci_sequence_number= cp_teach_ci_sequence_number;
232
233
234 l_cal_type igs_ca_inst.cal_type%TYPE;
235 l_ci_seq_number igs_ca_inst.sequence_number%TYPE;
236 l_course_cd igs_ps_ver.course_cd%TYPE;
237 l_course_version igs_ps_ver.version_number%TYPE;
238 l_unit_cd igs_ps_unit_ver.unit_cd%TYPE;
239 l_unit_version igs_ps_unit_ver.version_number%TYPE;
240 l_unit_set_cd igs_en_unit_set.unit_set_cd%TYPE;
241 l_unit_set_version igs_en_unit_set.version_number%TYPE;
242 l_message_name VARCHAR2(30) := NULL;
243 l_elgb_override_id igs_en_elgb_ovr.elgb_override_id%TYPE;
244 l_rowid VARCHAR2(30);
245 l_record_found BOOLEAN :=FALSE;
246 l_org_unit_start_dt igs_or_unit.start_dt%TYPE;
247 l_person_id_grp VARCHAR2(20);
248 l_person_id VARCHAR2(20);
249 l_step IGS_Lookups_view.meaning%TYPE;
250 l_load_cal_type igs_ca_teach_to_load_v.load_cal_type%TYPE;
251 l_load_cal_seq_num igs_ca_teach_to_load_v.load_ci_sequence_number%TYPE;
252 l_cal_cat IGS_CA_TYPE.S_CAL_CAT%TYPE;
253
254 l_is_teach_cal BOOLEAN;
255
256 --
257 -- procedure to log all the parameters passed
258 -- if no steps provided to override then log error message and stop the process
259 --
260 FUNCTION enrp_log_para(p_message_name OUT NOCOPY VARCHAR2
261 ) RETURN BOOLEAN AS
262
263 BEGIN
264 IF (p_person_step_1 IS NULL AND p_program_step_1 IS NULL AND p_unit_step_1 IS NULL
265 AND p_person_step_2 IS NULL AND p_program_step_2 IS NULL AND p_unit_step_2 IS NULL
266 AND p_person_step_3 IS NULL AND p_program_step_3 IS NULL AND p_unit_step_3 IS NULL
267 AND p_person_step_4 IS NULL AND p_program_step_4 IS NULL AND p_unit_step_4 IS NULL
268 AND p_person_ovr_step_1 IS NULL AND p_unit_ovr_step_1 IS NULL
269 AND p_person_ovr_step_2 IS NULL AND p_unit_ovr_step_2 IS NULL
270 AND p_person_ovr_step_3 IS NULL AND p_unit_ovr_step_3 IS NULL
271 AND p_person_ovr_step_4 IS NULL AND p_unit_ovr_step_4 IS NULL ) THEN
272
273 p_message_name := 'IGS_EN_NO_STEP_PASSED';
274 RETURN FALSE;
275 END IF;
276 Fnd_Message.SET_NAME ('IGS','IGS_CA_CAL_TYPE');
277 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET|| ': ' ||p_trm_teach_cal_type_comb);
278
279 IF p_program_cd_comb IS NOT NULL THEN
280 Fnd_Message.SET_NAME ('IGS','IGS_AD_CRS_CD');
281 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||': '||p_program_cd_comb);
282 END IF;
283 IF p_location_cd IS NOT NULL THEN
284 Fnd_Message.SET_NAME ('IGS','IGS_AD_LG_INAP_LOC');
285 Fnd_Message.Set_Token('LOC',p_location_cd);
286 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET);
287 END IF;
288 IF p_attendance_mode IS NOT NULL THEN
289 Fnd_Message.SET_NAME ('IGS','IGS_AD_LG_INAP_ATT_MODE');
290 Fnd_Message.Set_Token('ATTMODE',p_attendance_mode);
291 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET);
292 END IF;
293 IF p_attendance_type IS NOT NULL THEN
294 Fnd_Message.SET_NAME ('IGS','IGS_AD_LG_INAP_ATT_TYPE');
295 Fnd_Message.Set_Token('ATTTYPE',p_attendance_type);
296 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET);
297 END IF;
298 IF p_unit_cd_comb IS NOT NULL THEN
299 Fnd_Message.SET_NAME ('IGS','IGS_AD_UNIT');
300 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET|| ': ' ||p_unit_cd_comb);
301 END IF;
302 IF p_unit_set_comb IS NOT NULL THEN
303 Fnd_Message.SET_NAME ('IGS','IGS_AD_UNIT_SET');
304 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET|| ': ' ||p_unit_set_comb);
305 END IF;
306 IF p_class_standing IS NOT NULL THEN
307 Fnd_Message.SET_NAME ('IGS','IGS_EN_CLASS_STAND');
308 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET|| ': ' ||p_class_standing);
309
310 END IF;
311 IF p_org_unit_cd IS NOT NULL THEN
312 Fnd_Message.SET_NAME ('IGS','IGS_PR_ORG_UNIT');
313 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET|| ': '||p_org_unit_cd);
314 END IF;
315 IF p_person_id_group IS NOT NULL THEN
316 OPEN cur_person_id_grp(p_person_id_group);
317 FETCH cur_person_id_grp INTO l_person_id_grp;
318 CLOSE cur_person_id_grp;
319 Fnd_Message.SET_NAME ('IGS','IGS_AD_ADM_ENQ_ID_GRP');
320 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||': '||l_person_id_grp);
321
322 END IF;
323 IF p_person_id IS NOT NULL THEN
324 OPEN cur_person_id(p_person_id);
325 FETCH cur_person_id INTO l_person_id;
326 CLOSE cur_person_id;
327 Fnd_Message.SET_NAME ('IGS','IGS_AD_PERSON_ID');
328 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET|| ': '||l_person_id);
329
330 END IF;
331 IF p_program_attempt_status IS NOT NULL THEN
332 -- program attempt status
333 OPEN cur_step('CRS_ATTEMPT_STATUS',p_program_attempt_status);
334 FETCH cur_step INTO l_step;
335 CLOSE cur_step;
336 Fnd_Message.SET_NAME ('IGS','IGS_RE_ATTMPT_STATUS');
337 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||': '||l_step);
338
339 END IF;
340 IF p_person_step_1 IS NOT NULL THEN
341 -- person step
342 OPEN cur_step('ENROLMENT_STEP_TYPE_EXT',p_person_step_1);
343 FETCH cur_step INTO l_step;
344 CLOSE cur_step;
345 Fnd_Message.SET_NAME('IGS', 'IGS_EN_PER_STEP');
346 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 1: '||l_step);
347
348 END IF;
349 IF p_program_step_1 IS NOT NULL THEN
350 -- program step
351 OPEN cur_step('ENROLMENT_STEP_TYPE_EXT',p_program_step_1);
352 FETCH cur_step INTO l_step;
353 CLOSE cur_step;
354 Fnd_Message.SET_NAME('IGS', 'IGS_EN_PGM_STEP');
355 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 1: '||l_step);
356
357 END IF;
358
359 IF p_over_credit_point_1 IS NOT NULL THEN
360 Fnd_Message.SET_NAME('IGS', 'IGS_EN_DESIRED_LIM');
361 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 1: '||p_over_credit_point_1);
362
363 END IF;
364
365 IF p_unit_step_1 IS NOT NULL THEN
366 -- unit step
367 OPEN cur_step('ENROLMENT_STEP_TYPE_EXT',p_unit_step_1);
368 FETCH cur_step INTO l_step;
369 CLOSE cur_step;
370 Fnd_Message.SET_NAME('IGS', 'IGS_EN_UNIT_STEP');
371 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 1: '||l_step);
372
373 END IF;
374
375 IF p_unit_cd_1 IS NOT NULL THEN
376 Fnd_Message.SET_NAME('IGS', 'IGS_AD_UNIT');
377 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 1: '||p_unit_cd_1);
378
379 END IF;
380
381 IF p_unit_section_1 IS NOT NULL THEN
382 Fnd_Message.SET_NAME('IGS', 'IGS_EN_UNIT_SEC');
383 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 1: '||p_unit_section_1);
384
385 END IF;
386
387 IF p_person_step_2 IS NOT NULL THEN
388 OPEN cur_step('ENROLMENT_STEP_TYPE_EXT',p_person_step_2);
389 FETCH cur_step INTO l_step;
390 CLOSE cur_step;
391 Fnd_Message.SET_NAME('IGS', 'IGS_EN_PER_STEP');
392 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 2: '||l_step);
393
394 END IF;
395 IF p_program_step_2 IS NOT NULL THEN
396 OPEN cur_step('ENROLMENT_STEP_TYPE_EXT',p_program_step_2);
397 FETCH cur_step INTO l_step;
398 CLOSE cur_step;
399 Fnd_Message.SET_NAME('IGS', 'IGS_EN_PGM_STEP');
400 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 2: '||l_step);
401 END IF;
402 IF p_over_credit_point_2 IS NOT NULL THEN
403 Fnd_Message.SET_NAME('IGS', 'IGS_EN_DESIRED_LIM');
404 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 2: '||p_over_credit_point_2 );
405 END IF;
406
407 IF p_unit_step_2 IS NOT NULL THEN
408 OPEN cur_step('ENROLMENT_STEP_TYPE_EXT',p_unit_step_2);
409 FETCH cur_step INTO l_step;
410 CLOSE cur_step;
411 Fnd_Message.SET_NAME('IGS', 'IGS_EN_UNIT_STEP');
412 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 2: '||l_step);
413
414 END IF;
415
416 IF p_unit_cd_2 IS NOT NULL THEN
417 Fnd_Message.SET_NAME('IGS', 'IGS_AD_UNIT');
418 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 2: '||p_unit_cd_2);
419
420 END IF;
421
422 IF p_unit_section_2 IS NOT NULL THEN
423 Fnd_Message.SET_NAME('IGS', 'IGS_EN_UNIT_SEC');
424 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 2: '||p_unit_section_2);
425
426 END IF;
427
428
429 IF p_person_step_3 IS NOT NULL THEN
430 OPEN cur_step('ENROLMENT_STEP_TYPE_EXT',p_person_step_3);
431 FETCH cur_step INTO l_step;
432 CLOSE cur_step;
433 Fnd_Message.SET_NAME('IGS', 'IGS_EN_PER_STEP');
434 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 3: '||l_step);
435
436 END IF;
437 IF p_program_step_3 IS NOT NULL THEN
438 OPEN cur_step('ENROLMENT_STEP_TYPE_EXT',p_program_step_3);
439 FETCH cur_step INTO l_step;
440 CLOSE cur_step;
441 Fnd_Message.SET_NAME('IGS', 'IGS_EN_PGM_STEP');
442 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 3: '||l_step);
443 END IF;
444 IF p_unit_step_3 IS NOT NULL THEN
445 OPEN cur_step('ENROLMENT_STEP_TYPE_EXT',p_unit_step_3);
446 FETCH cur_step INTO l_step;
447 CLOSE cur_step;
448 Fnd_Message.SET_NAME('IGS', 'IGS_EN_UNIT_STEP');
449 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 3: '||l_step);
450
451 END IF;
452 IF p_unit_cd_3 IS NOT NULL THEN
453
454 Fnd_Message.SET_NAME('IGS', 'IGS_AD_UNIT');
455 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 3: '||p_unit_cd_3);
456
457 END IF;
458
459 IF p_unit_section_3 IS NOT NULL THEN
460
461 Fnd_Message.SET_NAME('IGS', 'IGS_EN_UNIT_SEC');
462 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 3: '||p_unit_section_3);
463
464 END IF;
465
466 IF p_person_step_4 IS NOT NULL THEN
467 OPEN cur_step('ENROLMENT_STEP_TYPE_EXT',p_person_step_4);
468 FETCH cur_step INTO l_step;
469 CLOSE cur_step;
470 Fnd_Message.SET_NAME('IGS', 'IGS_EN_PER_STEP');
471 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 4: '||l_step);
472
473 END IF;
474 IF p_program_step_4 IS NOT NULL THEN
475 OPEN cur_step('ENROLMENT_STEP_TYPE_EXT',p_program_step_4);
476 FETCH cur_step INTO l_step;
477 CLOSE cur_step;
478 Fnd_Message.SET_NAME('IGS', 'IGS_EN_PGM_STEP');
479 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 4: '||l_step);
480
481 END IF;
482 IF p_unit_step_4 IS NOT NULL THEN
483 OPEN cur_step('ENROLMENT_STEP_TYPE_EXT',p_unit_step_4);
484 FETCH cur_step INTO l_step;
485 CLOSE cur_step;
486 Fnd_Message.SET_NAME('IGS', 'IGS_EN_UNIT_STEP');
487 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 4: '||l_step);
488 --
489 END IF;
490
491 IF p_unit_cd_4 IS NOT NULL THEN
492 Fnd_Message.SET_NAME('IGS', 'IGS_AD_UNIT');
493 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 4: '||p_unit_cd_4);
494
495 END IF;
496
497 IF p_unit_section_4 IS NOT NULL THEN
498
499 Fnd_Message.SET_NAME('IGS', 'IGS_EN_UNIT_SEC');
500 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 4: '||p_unit_section_4);
501
502 END IF;
503
504 IF p_person_ovr_step_1 IS NOT NULL THEN
505 --Person Override Step
506 OPEN cur_step('ENROLMENT_OVR_STEP_TYPE',p_person_ovr_step_1);
507 FETCH cur_step INTO l_step;
508 CLOSE cur_step;
509 Fnd_Message.SET_NAME('IGS', 'IGS_EN_PER_OVR_STEP');
510 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 1: '||l_step);
511
512 END IF;
513
514 IF p_unit_ovr_step_1 IS NOT NULL THEN
515 --unit Override Step
516 OPEN cur_step('ENROLMENT_OVR_STEP_TYPE',p_unit_ovr_step_1);
517 FETCH cur_step INTO l_step;
518 CLOSE cur_step;
519 Fnd_Message.SET_NAME('IGS', 'IGS_EN_UNIT_OVR_STEP');
520 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 1: '||l_step);
521
522 END IF;
523 IF p_over_credit_point_3 IS NOT NULL THEN
524
525 Fnd_Message.SET_NAME('IGS', 'IGS_EN_CRDT_LIM');
526 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 1: '|| p_over_credit_point_3);
527
528 END IF;
529
530 IF p_unit_cd_ovr_1 IS NOT NULL THEN
531
532 Fnd_Message.SET_NAME('IGS', 'IGS_AD_UNIT');
533 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 1: '||p_unit_cd_ovr_1);
534
535 END IF;
536
537 IF p_unit_section_ovr_1 IS NOT NULL THEN
538
539 Fnd_Message.SET_NAME('IGS', 'IGS_EN_UNIT_SEC');
540 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 1: '||p_unit_section_ovr_1);
541
542 END IF;
543 IF p_person_ovr_step_2 IS NOT NULL THEN
544 OPEN cur_step('ENROLMENT_OVR_STEP_TYPE',p_person_ovr_step_2);
545 FETCH cur_step INTO l_step;
546 CLOSE cur_step;
547 Fnd_Message.SET_NAME('IGS', 'IGS_EN_PER_OVR_STEP');
548 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 2: '||l_step);
549
550 END IF;
551
552 IF p_unit_ovr_step_2 IS NOT NULL THEN
553 OPEN cur_step('ENROLMENT_OVR_STEP_TYPE',p_unit_ovr_step_2);
554 FETCH cur_step INTO l_step;
555 CLOSE cur_step;
556 Fnd_Message.SET_NAME('IGS', 'IGS_EN_UNIT_OVR_STEP');
557 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 2: '||l_step);
558
559 END IF;
560
561
562 IF p_over_credit_point_4 IS NOT NULL THEN
563 Fnd_Message.SET_NAME('IGS', 'IGS_EN_CRDT_LIM');
564 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 2: '||p_over_credit_point_4);
565
566 END IF;
567 IF p_unit_cd_ovr_2 IS NOT NULL THEN
568
569 Fnd_Message.SET_NAME('IGS', 'IGS_AD_UNIT');
570 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 2: '||p_unit_cd_ovr_2 );
571
572 END IF;
573
574 IF p_unit_section_ovr_2 IS NOT NULL THEN
575
576 Fnd_Message.SET_NAME('IGS', 'IGS_EN_UNIT_SEC');
577 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 2: '||p_unit_section_ovr_2 );
578 END IF;
579
580 IF p_person_ovr_step_3 IS NOT NULL THEN
581 OPEN cur_step('ENROLMENT_OVR_STEP_TYPE',p_person_ovr_step_3);
582 FETCH cur_step INTO l_step;
583 CLOSE cur_step;
584 Fnd_Message.SET_NAME('IGS', 'IGS_EN_PER_OVR_STEP');
585 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 3: '||l_step);
586 END IF;
587
588 IF p_unit_ovr_step_3 IS NOT NULL THEN
589 OPEN cur_step('ENROLMENT_OVR_STEP_TYPE',p_unit_ovr_step_3);
590 FETCH cur_step INTO l_step;
591 CLOSE cur_step;
592 Fnd_Message.SET_NAME('IGS', 'IGS_EN_UNIT_OVR_STEP');
593 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 3: '||l_step);
594 END IF;
595
596 IF p_unit_cd_ovr_3 IS NOT NULL THEN
597 Fnd_Message.SET_NAME('IGS', 'IGS_AD_UNIT');
598 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 3: '||p_unit_cd_ovr_3);
599
600 END IF;
601
602 IF p_unit_section_ovr_3 IS NOT NULL THEN
603 Fnd_Message.SET_NAME('IGS', 'IGS_EN_UNIT_SEC');
604 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 3: '||p_unit_section_ovr_3);
605 END IF;
606 IF p_person_ovr_step_4 IS NOT NULL THEN
607 OPEN cur_step('ENROLMENT_OVR_STEP_TYPE',p_person_ovr_step_4);
608 FETCH cur_step INTO l_step;
609 CLOSE cur_step;
610 Fnd_Message.SET_NAME('IGS', 'IGS_EN_PER_OVR_STEP');
611 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 4: '||l_step);
612 END IF;
613
614 IF p_unit_ovr_step_4 IS NOT NULL THEN
615 OPEN cur_step('ENROLMENT_OVR_STEP_TYPE',p_unit_ovr_step_4);
616 FETCH cur_step INTO l_step;
617 CLOSE cur_step;
618 Fnd_Message.SET_NAME('IGS', 'IGS_EN_UNIT_OVR_STEP');
619 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 4: '||l_step);
620 END IF;
621 IF p_unit_cd_ovr_4 IS NOT NULL THEN
622 Fnd_Message.SET_NAME('IGS', 'IGS_AD_UNIT');
623 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 4: '||p_unit_cd_ovr_4);
624
625 END IF;
626
627 IF p_unit_section_ovr_4 IS NOT NULL THEN
628 Fnd_Message.SET_NAME('IGS', 'IGS_EN_UNIT_SEC');
629 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET||' 4: '||p_unit_section_ovr_4);
630 END IF;
631 Fnd_File.PUT_LINE(Fnd_File.LOG,'--------------------------------------------------------------------');
632 RETURN TRUE;
633
634 END enrp_log_para;
635
636 --
637 -- Function which validates the parameters
638 -- Steps (FMIN_CRDT,FMAX_CRDT,VAR_CREDIT_APPROVAL) should have value in
639 -- credit_point_limit parameter if no value specified log error message and stop the process
640 -- if any other steps has value for then log a warning message and continue the process.
641 --
642 FUNCTION enrp_val_para(p_message_name OUT NOCOPY VARCHAR2
643 )RETURN BOOLEAN AS
644 BEGIN
645
646 IF (p_program_step_1 IS NULL OR p_program_step_1 NOT IN ('FMIN_CRDT','FMAX_CRDT'))
647 AND (p_over_credit_point_1 IS NOT NULL ) THEN
648
649 p_message_name := 'IGS_EN_DESIRED_LMT_IGN';
650
651 ELSIF (p_program_step_2 IS NULL OR p_program_step_2 NOT IN ('FMIN_CRDT','FMAX_CRDT'))
652 AND (p_over_credit_point_2 IS NOT NULL ) THEN
653
654 p_message_name := 'IGS_EN_DESIRED_LMT_IGN';
655 END IF;
656
657 IF (p_unit_ovr_step_1 IS NULL OR p_unit_ovr_step_1 NOT IN('VAR_CREDIT_APPROVAL'))
658 AND (p_over_credit_point_3 IS NOT NULL)THEN
659 --
660 p_message_name :='IGS_EN_CRDT_LMT_IGN';
661
662 ELSIF (p_unit_ovr_step_2 IS NULL OR p_unit_ovr_step_2 NOT IN ('VAR_CREDIT_APPROVAL'))
663 AND (p_over_credit_point_4 IS NOT NULL) THEN
664 --
665 p_message_name :='IGS_EN_CRDT_LMT_IGN';
666
667 END IF;
668
669 IF p_unit_ovr_step_1 = 'VAR_CREDIT_APPROVAL' AND p_over_credit_point_3 IS NULL THEN
670 p_message_name :='IGS_EN_CRDT_LMT_REQ';
671 RETURN FALSE;
672 ELSIF p_unit_ovr_step_2 = 'VAR_CREDIT_APPROVAL' AND p_over_credit_point_4 IS NULL THEN
673 p_message_name :='IGS_EN_CRDT_LMT_REQ';
674 RETURN FALSE;
675 END IF;
676
677 IF p_unit_step_1 IS NOT NULL AND p_unit_cd_1 IS NULL THEN
678 p_message_name := 'IGS_EN_UNIT_EXISTS';
679 RETURN FALSE;
680 END IF;
681
682 IF p_unit_step_2 IS NOT NULL AND p_unit_cd_2 IS NULL THEN
683 p_message_name := 'IGS_EN_UNIT_EXISTS';
684 RETURN FALSE;
685 END IF;
686 IF p_unit_step_3 IS NOT NULL AND p_unit_cd_3 IS NULL THEN
687 p_message_name := 'IGS_EN_UNIT_EXISTS';
688 RETURN FALSE;
689 END IF;
690 IF p_unit_step_4 IS NOT NULL AND p_unit_cd_4 IS NULL THEN
691 p_message_name := 'IGS_EN_UNIT_EXISTS';
692 RETURN FALSE;
693 END IF;
694 IF p_unit_ovr_step_1 IS NOT NULL AND p_unit_cd_ovr_1 IS NULL THEN
695 p_message_name := 'IGS_EN_UNIT_EXISTS';
696 RETURN FALSE;
697 END IF;
698 IF p_unit_ovr_step_2 IS NOT NULL AND p_unit_cd_ovr_2 IS NULL THEN
699 p_message_name := 'IGS_EN_UNIT_EXISTS';
700 RETURN FALSE;
701 END IF;
702 IF p_unit_ovr_step_3 IS NOT NULL AND p_unit_cd_ovr_3 IS NULL THEN
703 p_message_name := 'IGS_EN_UNIT_EXISTS';
704 RETURN FALSE;
705 END IF;
706 IF p_unit_ovr_step_4 IS NOT NULL AND p_unit_cd_ovr_4 IS NULL THEN
707 p_message_name := 'IGS_EN_UNIT_EXISTS';
708 RETURN FALSE;
709 END IF;
710
711 IF p_unit_step_1 IS NULL AND (p_unit_cd_1 IS NOT NULL
712 OR p_unit_section_1 IS NOT NULL) THEN
713 p_message_name := 'IGS_EN_UNIT_NT_REQD';
714
715 END IF;
716 IF p_unit_step_2 IS NULL AND (p_unit_cd_2 IS NOT NULL
717 OR p_unit_section_2 IS NOT NULL) THEN
718 p_message_name := 'IGS_EN_UNIT_NT_REQD';
719
720 END IF;
721 IF p_unit_step_3 IS NULL AND (p_unit_cd_3 IS NOT NULL
722 OR p_unit_section_3 IS NOT NULL) THEN
723 p_message_name := 'IGS_EN_UNIT_NT_REQD';
724
725 END IF;
726 IF p_unit_step_4 IS NULL AND (p_unit_cd_4 IS NOT NULL
727 OR p_unit_section_4 IS NOT NULL) THEN
728 p_message_name := 'IGS_EN_UNIT_NT_REQD';
729
730 END IF;
731 IF p_unit_ovr_step_1 IS NULL AND (p_unit_cd_ovr_1 IS NOT NULL
732 OR p_unit_section_ovr_1 IS NOT NULL) THEN
733 p_message_name := 'IGS_EN_UNIT_NT_REQD';
734
735 END IF;
736 IF p_unit_ovr_step_2 IS NULL AND (p_unit_cd_ovr_2 IS NOT NULL
737 OR p_unit_section_ovr_2 IS NOT NULL) THEN
738 p_message_name := 'IGS_EN_UNIT_NT_REQD';
739
740 END IF;
741
742 IF p_unit_ovr_step_3 IS NULL AND (p_unit_cd_ovr_3 IS NOT NULL
743 OR p_unit_section_ovr_3 IS NOT NULL) THEN
744 p_message_name := 'IGS_EN_UNIT_NT_REQD';
745
746 END IF;
747
748 IF p_unit_ovr_step_4 IS NULL AND (p_unit_cd_ovr_4 IS NOT NULL
749 OR p_unit_section_ovr_4 IS NOT NULL) THEN
750 p_message_name := 'IGS_EN_UNIT_NT_REQD';
751
752 END IF;
753
754 RETURN TRUE;
755
756
757 END enrp_val_para;
758
759 --
760 -- This Procedure will create(if not exists) the override records for the steps passed as parameters
761 -- corresponding to the person override record created in igs_en_elgb_ovr table
762 --
763 PROCEDURE enrp_ins_step_over(p_person_id NUMBER, p_cal_type VARCHAR2, p_ci_seq_number NUMBER,
764 p_elgb_override_id IN NUMBER, p_unit_cd VARCHAR2, p_unit_version NUMBER,
765 p_is_teach_cal BOOLEAN
766 )AS
767
768 CURSOR cur_check_override (
769 cp_elgb_override_id IGS_EN_ELGB_OVR_STEP.ELGB_OVERRIDE_ID%TYPE,
770 cp_eligibility_step_type IGS_EN_ELGB_OVR_STEP.STEP_OVERRIDE_TYPE%TYPE )IS
771 SELECT step_override_limit,
772 step_override_type
773 FROM igs_en_elgb_ovr_step eos
774 WHERE eos.elgb_override_id = cp_elgb_override_id
775 AND eos.step_override_type = cp_eligibility_step_type;
776
777 lr_cur_check_override cur_check_override%ROWTYPE;
778
779 CURSOR cur_pers_number(p_party_id NUMBER)IS
780 SELECT party_number
781 FROM hz_parties
782 WHERE party_id = p_party_id;
783
784 l_person_number hz_parties.party_number%TYPE;
785
786 CURSOR cur_step_meaning (cp_step_Type IGS_LOOKUP_VALUES.LOOKUP_CODE%TYPE) IS
787 SELECT meaning
788 FROM igs_lookup_values
789 WHERE lookup_code = cp_step_Type
790 AND (LOOKUP_TYPE = 'ENROLMENT_STEP_TYPE_EXT'
791 OR LOOKUP_TYPE = 'ENROLMENT_OVR_STEP_TYPE');
792
793 l_message VARCHAR2(5000);
794
795 --
796 -- procedure to check whether record exist, otherwise create new override record for Person Steps
797 PROCEDURE enrp_chk_pers_over(p_elgb_step IN VARCHAR2, p_is_teach_cal IN BOOLEAN
798 )AS
799
800 l_step_meaning IGS_LOOKUP_VALUES.MEANING%TYPE;
801 l_step_override_limit igs_en_elgb_ovr_step.STEP_OVERRIDE_LIMIT%TYPE;
802 l_rowid VARCHAR2(30);
803 l_elgb_ovr_step_id igs_en_elgb_ovr_step.elgb_ovr_step_id%TYPE;
804
805 BEGIN
806
807 l_step_meaning := NULL;
808 OPEN cur_step_meaning(p_elgb_step);
809 FETCH cur_step_meaning INTO l_step_meaning;
810 CLOSE cur_step_meaning;
811 IF p_is_teach_cal THEN
812 Fnd_Message.SET_NAME ('IGS','IGS_EN_CANNOT_CREATE_STEP');
813 Fnd_File.PUT_LINE(Fnd_File.LOG,' ' ||l_step_meaning || ' - ' || Fnd_Message.GET);
814 RETURN;
815 END IF;-- end of IF p_is_teach_cal THEN
816
817
818 OPEN cur_check_override(p_elgb_override_id,p_elgb_step);
819 FETCH cur_check_override INTO lr_cur_check_override;
820 BEGIN
821 IF cur_check_override%NOTFOUND THEN
822
823 Igs_En_Elgb_Ovr_Step_Pkg.insert_row(x_rowid => l_rowid,
824 x_elgb_ovr_step_id => l_elgb_ovr_step_id,
825 x_elgb_override_id => p_elgb_override_id, -- from parameter of container procedure
826
827 x_step_override_type => p_elgb_step,
828 x_step_override_dt => TRUNC(SYSDATE),
829 x_step_override_limit => NULL,
830 x_mode => 'R');
831
832
833 Fnd_Message.SET_NAME ('IGS','IGS_EN_CR_SUCCESS');
834 Fnd_File.PUT_LINE(Fnd_File.LOG,' ' ||l_step_meaning || ' - ' || Fnd_Message.GET);
835 ELSE
836 Fnd_Message.SET_NAME ('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
837 Fnd_File.PUT_LINE(Fnd_File.LOG,' ' || l_step_meaning || ' - ' || Fnd_Message.GET);
838 END IF;
839
840 EXCEPTION
841 WHEN OTHERS THEN
842 l_message := Fnd_Message.GET;
843 IF l_message IS NOT NULL THEN
844 Fnd_File.PUT_LINE(Fnd_File.LOG,' ' || l_step_meaning || ' - ' ||l_message );
845 ELSE
846 Fnd_File.PUT_LINE(Fnd_File.LOG,' ' || l_step_meaning || ' - ' ||SQLERRM );
847 END IF;
848 END;
849 CLOSE cur_check_override;
850
851 END enrp_chk_pers_over;
852
853
854 PROCEDURE enrp_chk_prg_over(
855 p_elgb_step IN VARCHAR2,
856 p_step_override_limit IN NUMBER,
857 p_is_teach_cal IN BOOLEAN
858 )AS
859 /*------------------------------------------------------------------------------------------
860 --Created by : knaraset, Oracle IDC
861 --Date created:
862 --
863 --Purpose:
864 -- procedure to check whether record exist, otherwise create new override record for Person Steps
865 --
866 --Known limitations/enhancements and/or remarks:
867 --
868 --Change History:
869 --Who When What
870 --kkillams 05-10-2002 New validation checks whether authorization code is defined or not
871 -- for the MIN credit/Force attendance program steps. If not new logic
872 -- tries to insert the authorization code, where authorization code
873 -- passed as a parameter.
874 --svanukur 16-jun-2003 as part of validation consolidtion CR bug #2881385,
875 -- inserting the unit details into igs_en_elgb_ovr_uoo table which was
876 -- created as part of build#2829272.
877 --ckasu 13-JUN-2006 modified as a part of bug 5299024.
878 --amuthu 15-jun-2006 Added logic to show a note to the user if an authorization already
879 -- exists and a new one is not being created.
880 --amuthu 18-Aug-2006 The message IGS_EN_SV_AUTH_ALREADY_EXISTS would be displayed only
881 -- for non-imig student alone. Bug 5435397
882 -------------------------------------------------------------------------------------------- */
883 --Cursor to get the calendar end date,person id for a given eligibility override.
884 CURSOR cur_elgb_end_dt IS SELECT end_dt,person_id, ci.cal_type, ci.sequence_number FROM igs_en_elgb_ovr elgb,
885 igs_ca_inst ci
886 WHERE elgb.elgb_override_id = p_elgb_override_id
887 AND elgb.cal_type = ci.cal_type
888 AND elgb.ci_sequence_number = ci.sequence_number;
889 l_step_meaning IGS_LOOKUP_VALUES.MEANING%TYPE;
890 l_rowid VARCHAR2(30);
891 l_elgb_ovr_step_id NUMBER;
892 l_step_override_limit NUMBER := NULL;
893 l_ci_end_dt igs_ca_inst.end_dt%TYPE;
894 l_cal_type igs_ca_inst.cal_type%TYPE;
895 l_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
896 l_person_id igs_en_elgb_ovr.person_id%TYPE;
897 l_message VARCHAR2(5000);
898 l_auth_start_Dt igs_en_svs_auth.START_DT%TYPE;
899 l_auth_end_dt igs_en_svs_auth.END_DT%TYPE;
900 l_sevis_auth_id igs_en_svs_auth.SEVIS_AUTH_ID%TYPE;
901 l_sevis_authorization_no igs_en_svs_auth.sevis_authorization_no%TYPE;
902
903 -- Cursor to check whether active sevis person type exists for that person
904 -- modified by ckasu as a part of bug 5300119
905 CURSOR c_sevis_person_type IS
906 SELECT 'X'
907 FROM igs_pe_typ_instances_all pti,
908 igs_pe_person_types pt
909 WHERE pt.person_type_code = pti.person_type_code
910 AND system_type IN ('NONIMG_STUDENT')
911 AND person_id = p_person_id
912 AND NVL(end_date, SYSDATE) >= SYSDATE;
913
914 l_sevis_person_type c_sevis_person_type%ROWTYPE;
915
916
917 BEGIN
918 -- if the unit step is 'FMIN_CRDT','FMAX_CRDT' then only create override record with step_override_limit
919 -- otherwise that column will be NULL
920
921 SAVEPOINT creating_auth_rec;
922
923 IF p_elgb_step IN ('FMIN_CRDT','FMAX_CRDT') THEN
924 l_step_override_limit := p_step_override_limit;
925 END IF;
926
927 l_step_meaning := NULL;
928 OPEN cur_step_meaning(p_elgb_step);
929 FETCH cur_step_meaning INTO l_step_meaning;
930 CLOSE cur_step_meaning;
931
932 IF p_is_teach_cal THEN
933
934 Fnd_Message.SET_NAME ('IGS','IGS_EN_CANNOT_CREATE_STEP');
935 Fnd_File.PUT_LINE(Fnd_File.LOG,' ' ||l_step_meaning || ' - ' || Fnd_Message.GET);
936 RETURN;
937 END IF;-- end of IF p_is_teach_cal THEN
938
939
940 OPEN cur_check_override(p_elgb_override_id,p_elgb_step);
941 FETCH cur_check_override INTO lr_cur_check_override;
942 BEGIN
943 IF cur_check_override%NOTFOUND THEN
944 --Cursor to get the calendar end date for a given eligibility override.
945 OPEN cur_elgb_end_dt;
946 FETCH cur_elgb_end_dt INTO l_ci_end_dt,l_person_id, l_cal_type,l_ci_sequence_number ;
947 CLOSE cur_elgb_end_dt;
948
949 -- modified by ckasu as part of bug 5299024 inorder to create the eligible override step
950 -- in upfront.
951
952 l_rowid:=NULL;
953
954 Igs_En_Elgb_Ovr_Step_Pkg.insert_row(x_rowid => l_rowid,
955 x_elgb_ovr_step_id => l_elgb_ovr_step_id,
956 x_elgb_override_id => p_elgb_override_id, -- from parameter of container procedure
957 x_step_override_type => p_elgb_step,
958 x_step_override_dt => TRUNC(SYSDATE),
959 x_step_override_limit => l_step_override_limit,
960 x_mode => 'R');
961
962
963 --If step is 'FMIN_CRDT' or 'FATD_TYPE', then call the igs_en_sevis.enrf_chk_sevis_auth_req function, which validates the sevis pre-requisites.
964 --And check whether authorization code is required for this step override or not.
965 IF p_elgb_step IN ('FMIN_CRDT','FATD_TYPE') THEN
966
967 IF igs_en_sevis.enrf_chk_sevis_auth_req(p_person_id => l_person_id ,
968 p_cal_type => l_cal_type,
969 p_ci_sequence_number => l_ci_sequence_number,
970 p_elgb_step => p_elgb_step
971 ) THEN
972 IF p_sevis_auth_cd IS NOT NULL THEN
973 l_rowid := NULL;
974 l_sevis_auth_id := NULL;
975 l_auth_start_dt := NULL;
976 l_auth_end_dt := NULL;
977
978 igs_en_sevis.enrp_sevis_auth_dflt_dt(
979 p_person_id => l_person_id,
980 p_cal_type => l_cal_type,
981 p_ci_sequence_number => l_ci_sequence_number,
982 p_dflt_auth_start_dt => l_auth_start_dt,
983 p_dflt_auth_end_dt => l_auth_end_dt);
984
985 IF l_auth_end_dt IS NULL OR l_auth_start_dt IS NULL THEN
986 --Log a warn message.
987 --modfied message name as a part of bug 5299024
988 ROLLBACK to creating_auth_rec;
989 Fnd_Message.SET_NAME ('IGS','IGS_EN_AUTH_REC_NO_DATES');
990 Fnd_File.PUT_LINE(Fnd_File.LOG,' ' || Fnd_Message.GET );
991 RETURN;
992 ELSE
993
994 SAVEPOINT creating_auth_rec;
995 --Creating the authorization code for the given person.
996 --p_comments,p_elgb_override_id,p_sevis_auth_cd these are parameter of container procedure.
997 igs_en_sevis.create_auth_cal_row (
998 p_sevis_authorization_code => p_sevis_auth_cd,
999 p_start_dt => l_auth_start_dt,
1000 p_end_dt => l_auth_end_dt,
1001 p_comments => p_comments,
1002 p_sevis_auth_id => l_sevis_auth_id,
1003 p_sevis_authorization_no => l_sevis_authorization_no,
1004 p_person_id => l_person_id,
1005 p_cal_type => l_cal_type,
1006 p_ci_sequence_number => l_ci_sequence_number,
1007 p_cancel_flag => 'N');
1008
1009
1010 IF igs_en_sevis.is_auth_records_overlap(l_person_id) THEN
1011 ROLLBACK to creating_auth_rec;
1012 FND_MESSAGE.SET_NAME('IGS','IGS_GE_DATES_OVERLAP');
1013 Fnd_File.PUT_LINE(Fnd_File.LOG,' ' || Fnd_Message.GET );
1014 RETURN;
1015 END IF;
1016
1017 END IF;
1018 ELSE
1019
1020 --Log a warn message.
1021 ROLLBACK to creating_auth_rec;
1022 Fnd_Message.SET_NAME ('IGS','IGS_EN_SEVIS_AUTH_REQUIRED');
1023 Fnd_File.PUT_LINE(Fnd_File.LOG,' ' ||Fnd_Message.GET );
1024 RETURN;
1025 END IF;
1026 ELSE
1027 IF FND_PROFILE.VALUE('IGS_SV_ENABLED') = 'Y' THEN
1028 OPEN c_sevis_person_type;
1029 FETCH c_sevis_person_type INTO l_sevis_person_type;
1030 IF c_sevis_person_type%FOUND THEN
1031 Fnd_Message.SET_NAME ('IGS','IGS_EN_SV_AUTH_ALREADY_EXISTS');
1032 Fnd_File.PUT_LINE(Fnd_File.LOG,' ' || Fnd_Message.GET );
1033 END IF;
1034 CLOSE c_sevis_person_type;
1035 END IF;
1036 END IF;
1037 END IF;
1038
1039
1040 Fnd_Message.SET_NAME ('IGS','IGS_EN_CR_SUCCESS');
1041 Fnd_File.PUT_LINE(Fnd_File.LOG,' ' ||l_step_meaning || ' - ' || Fnd_Message.GET);
1042 ELSE
1043 Fnd_Message.SET_NAME ('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
1044 Fnd_File.PUT_LINE(Fnd_File.LOG,' ' || l_step_meaning || ' - ' || Fnd_Message.GET);
1045 END IF;
1046
1047 EXCEPTION
1048 WHEN OTHERS THEN
1049 ROLLBACK to creating_auth_rec;
1050 l_message := Fnd_Message.GET;
1051 IF l_message IS NOT NULL THEN
1052 Fnd_File.PUT_LINE(Fnd_File.LOG,' ' || l_step_meaning || ' - ' ||l_message );
1053 ELSE
1054 Fnd_File.PUT_LINE(Fnd_File.LOG,' ' || l_step_meaning || ' - ' ||SQLERRM );
1055 END IF;
1056 END;
1057 CLOSE cur_check_override;
1058
1059
1060 END enrp_chk_prg_over;
1061
1062 --
1063 -- procedure to check whether record exist, otherwise create new override record for Person Steps
1064 PROCEDURE enrp_chk_unit_over(p_elgb_step IN VARCHAR2,p_step_override_limit IN NUMBER,p_unit_cd IN VARCHAR2,
1065 p_version_number IN NUMBER, p_uoo_id IN NUMBER
1066 )AS
1067 /*------------------------------------------------------------------------------------------
1068 --Created by : knaraset, Oracle IDC
1069 --Date created:
1070 --
1071 --Purpose:
1072 -- procedure to check whether record exist, otherwise create new override record for Person Steps
1073 --
1074 --Known limitations/enhancements and/or remarks:
1075 --
1076 --Change History:
1077 --Who When What
1078 --ptandon 02-Jul-2003 Modified to allow unit level override for multiple units/unit sections
1079 to a student in case of same unit step type if the override is for a
1080 different unit section. Bug# 3033214
1081 ------------------------------------------------------------------------------------------------------*/
1082 l_step_meaning IGS_LOOKUP_VALUES.MEANING%TYPE;
1083 l_rowid VARCHAR2(30);
1084 l_elgb_ovr_step_id NUMBER(15);
1085 l_elgb_ovr_step_uoo_id NUMBER(15);
1086 l_step_override_limit NUMBER(6,3) := NULL;
1087
1088 l_step_override_type igs_en_elgb_ovr_step.step_override_type%TYPE;
1089 l_message VARCHAR2(5000);
1090
1091 CURSOR cur_get_elgb_ovr_step_id IS
1092 SELECT elgb_ovr_step_id
1093 FROM igs_en_elgb_ovr_step eos
1094 WHERE eos.elgb_override_id = p_elgb_override_id
1095 AND eos.step_override_type = p_elgb_step;
1096
1097 lr_cur_get_elgb_ovr_step_id IGS_EN_ELGB_OVR_UOO.ELGB_OVR_STEP_ID%TYPE;
1098
1099 CURSOR cur_check_override_uooid (
1100 cp_elgb_ovr_step_id IGS_EN_ELGB_OVR_UOO.ELGB_OVR_STEP_ID%TYPE)IS
1101 SELECT elgb_ovr_step_uoo_id
1102 FROM igs_en_elgb_ovr_uoo eou
1103 WHERE eou.elgb_ovr_step_id = cp_elgb_ovr_step_id
1104 AND ((eou.uoo_id = NVL(p_uoo_id,-1) AND NVL(p_uoo_id,-1) <> -1)
1105 OR (eou.uoo_id = NVL(p_uoo_id,-1) AND NVL(p_uoo_id,-1) = -1 AND eou.unit_cd = p_unit_cd AND eou.version_number = p_version_number));
1106
1107 lr_cur_check_override_uooid cur_check_override_uooid%ROWTYPE;
1108
1109 BEGIN
1110
1111 -- if the unit step is 'VAR_CREDIT_APPROVAL' then only create override record with step_override_limit
1112 -- otherwise that column will be NULLp_person_id,
1113
1114 IF p_elgb_step = 'VAR_CREDIT_APPROVAL' THEN
1115 l_step_override_limit := p_step_override_limit;
1116 END IF;
1117
1118 l_step_meaning := NULL;
1119 OPEN cur_step_meaning(p_elgb_step);
1120 FETCH cur_step_meaning INTO l_step_meaning;
1121 CLOSE cur_step_meaning;
1122
1123 --
1124 -- check whether override record exists for the given Unit step, given student and in the given calendar.
1125 --
1126 OPEN cur_check_override(p_elgb_override_id,p_elgb_step);
1127 FETCH cur_check_override INTO lr_cur_check_override;
1128 BEGIN
1129 IF cur_check_override%NOTFOUND THEN
1130
1131 Igs_En_Elgb_Ovr_Step_Pkg.insert_row(x_rowid => l_rowid,
1132 x_elgb_ovr_step_id => l_elgb_ovr_step_id,
1133 x_elgb_override_id => p_elgb_override_id, -- from parameter of container procedure
1134
1135 x_step_override_type => p_elgb_step,
1136 x_step_override_dt => TRUNC(SYSDATE),
1137 x_step_override_limit => l_step_override_limit,
1138 x_mode => 'R');
1139
1140 l_rowid :=NULL;
1141
1142 Igs_En_Elgb_Ovr_Uoo_Pkg.insert_row(x_rowid => l_rowid,
1143 x_elgb_ovr_step_uoo_id => l_elgb_ovr_step_uoo_id,
1144 x_elgb_ovr_step_id => l_elgb_ovr_step_id,
1145 x_unit_cd => p_unit_cd,
1146 x_version_number => p_version_number,
1147 x_uoo_id => p_uoo_id,
1148 x_step_override_limit => l_step_override_limit,
1149 x_mode => 'R');
1150
1151 Fnd_Message.SET_NAME ('IGS','IGS_EN_CR_SUCCESS');
1152 Fnd_File.PUT_LINE(Fnd_File.LOG,' ' || l_step_meaning || ' - ' || Fnd_Message.GET);
1153 ELSE
1154 OPEN cur_get_elgb_ovr_step_id;
1155 FETCH cur_get_elgb_ovr_step_id INTO lr_cur_get_elgb_ovr_step_id;
1156 CLOSE cur_get_elgb_ovr_step_id;
1157
1158 OPEN cur_check_override_uooid(lr_cur_get_elgb_ovr_step_id);
1159 FETCH cur_check_override_uooid INTO lr_cur_check_override_uooid;
1160
1161 IF cur_check_override_uooid%NOTFOUND THEN
1162 Igs_En_Elgb_Ovr_Uoo_Pkg.insert_row(x_rowid => l_rowid,
1163 x_elgb_ovr_step_uoo_id => l_elgb_ovr_step_uoo_id,
1164 x_elgb_ovr_step_id => lr_cur_get_elgb_ovr_step_id,
1165 x_unit_cd => p_unit_cd,
1166 x_version_number => p_version_number,
1167 x_uoo_id => p_uoo_id,
1168 x_step_override_limit => l_step_override_limit,
1169 x_mode => 'R');
1170 Fnd_Message.SET_NAME ('IGS','IGS_EN_CR_SUCCESS');
1171 Fnd_File.PUT_LINE(Fnd_File.LOG,' ' || l_step_meaning || ' - ' || Fnd_Message.GET);
1172 ELSE
1173 Fnd_Message.SET_NAME ('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
1174 Fnd_File.PUT_LINE(Fnd_File.LOG,' ' || l_step_meaning || ' - ' || Fnd_Message.GET);
1175 END IF;
1176 CLOSE cur_check_override_uooid;
1177 END IF;
1178 EXCEPTION
1179 WHEN OTHERS THEN
1180
1181 l_message := Fnd_Message.GET;
1182 IF l_message IS NOT NULL THEN
1183 Fnd_File.PUT_LINE(Fnd_File.LOG,' ' || l_step_meaning || ' - ' ||l_message );
1184 ELSE
1185 Fnd_File.PUT_LINE(Fnd_File.LOG,' ' || l_step_meaning || ' - ' ||SQLERRM );
1186 END IF;
1187 END;
1188 CLOSE cur_check_override;
1189
1190 END enrp_chk_unit_over;
1191
1192 --
1193 -- begin of PROCEDURE enrp_ins_step_over
1194 --
1195 BEGIN
1196
1197 -- fetch the person number
1198
1199 OPEN cur_pers_number(p_person_id);
1200 FETCH cur_pers_number INTO l_person_number;
1201 CLOSE cur_pers_number;
1202
1203 Fnd_Message.SET_NAME ('IGS','IGS_PR_PERSON_ID');
1204 -- log the person number into log file for whom override records are going create
1205 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET || ': ' || l_person_number );
1206
1207 --
1208 -- for parameter set 1
1209 IF p_person_step_1 IS NOT NULL THEN
1210 enrp_chk_pers_over(p_person_step_1,p_is_teach_cal);
1211 END IF;
1212 IF p_program_step_1 IS NOT NULL THEN
1213 enrp_chk_prg_over(p_program_step_1,p_over_credit_point_1, p_is_teach_cal);
1214 END IF;
1215 IF p_unit_step_1 IS NOT NULL THEN
1216 enrp_chk_unit_over(p_unit_step_1,p_over_credit_point_1,RTRIM(SUBSTR(p_unit_cd_1,1,10)),
1217 TO_NUMBER(RTRIM(SUBSTR(p_unit_cd_1,12,3))),p_unit_section_1);
1218 END IF;
1219
1220 --
1221 -- for parameter set 2
1222 IF p_person_step_2 IS NOT NULL THEN
1223 enrp_chk_pers_over(p_person_step_2,p_is_teach_cal);
1224 END IF;
1225 IF p_program_step_2 IS NOT NULL THEN
1226 enrp_chk_prg_over(p_program_step_2,p_over_credit_point_2, p_is_teach_cal);
1227 END IF;
1228 IF p_unit_step_2 IS NOT NULL THEN
1229 enrp_chk_unit_over(p_unit_step_2,p_over_credit_point_2,RTRIM(SUBSTR(p_unit_cd_2,1,10)),
1230 TO_NUMBER(RTRIM(SUBSTR(p_unit_cd_2,12,3))),p_unit_section_2);
1231 END IF;
1232
1233 --
1234 -- for parameter set 3
1235 IF p_person_step_3 IS NOT NULL THEN
1236 enrp_chk_pers_over(p_person_step_3,p_is_teach_cal);
1237 END IF;
1238 IF p_program_step_3 IS NOT NULL THEN
1239 enrp_chk_prg_over(p_program_step_3,NULL,p_is_teach_cal);
1240 END IF;
1241 IF p_unit_step_3 IS NOT NULL THEN
1242 enrp_chk_unit_over(p_unit_step_3,NULL,RTRIM(SUBSTR(p_unit_cd_3,1,10)),
1243 TO_NUMBER(RTRIM(SUBSTR(p_unit_cd_3,12,3))),p_unit_section_3);
1244 END IF;
1245
1246 --
1247 -- for parameter set 4
1248 IF p_person_step_4 IS NOT NULL THEN
1249 enrp_chk_pers_over(p_person_step_4,p_is_teach_cal);
1250 END IF;
1251 IF p_program_step_4 IS NOT NULL THEN
1252 enrp_chk_prg_over(p_program_step_4,NULL,p_is_teach_cal);
1253 END IF;
1254 IF p_unit_step_4 IS NOT NULL THEN
1255 enrp_chk_unit_over(p_unit_step_4,NULL,RTRIM(SUBSTR(p_unit_cd_4,1,10)),
1256 TO_NUMBER(RTRIM(SUBSTR(p_unit_cd_4,12,3))),p_unit_section_4);
1257 END IF;
1258
1259 --
1260 -- for parameter set 5
1261 IF p_person_ovr_step_1 IS NOT NULL THEN
1262 enrp_chk_pers_over(p_person_ovr_step_1,FALSE);
1263 END IF;
1264 IF p_unit_ovr_step_1 IS NOT NULL THEN
1265 enrp_chk_unit_over(p_unit_ovr_step_1,p_over_credit_point_3,RTRIM(SUBSTR(p_unit_cd_ovr_1,1,10)),
1266 TO_NUMBER(RTRIM(SUBSTR(p_unit_cd_ovr_1,12,3))),p_unit_section_ovr_1);
1267 END IF;
1268
1269 --
1270 -- for parameter set 6
1271 IF p_person_ovr_step_2 IS NOT NULL THEN
1272 enrp_chk_pers_over(p_person_ovr_step_2,FALSE);
1273 END IF;
1274
1275 IF p_unit_ovr_step_2 IS NOT NULL THEN
1276 enrp_chk_unit_over(p_unit_ovr_step_2,p_over_credit_point_4,RTRIM(SUBSTR(p_unit_cd_ovr_2,1,10)),
1277 TO_NUMBER(RTRIM(SUBSTR(p_unit_cd_ovr_2,12,3))), p_unit_section_ovr_2);
1278 END IF;
1279
1280 --
1281 -- for parameter set 7
1282 IF p_person_ovr_step_3 IS NOT NULL THEN
1283 enrp_chk_pers_over(p_person_ovr_step_3,FALSE);
1284 END IF;
1285
1286 IF p_unit_ovr_step_3 IS NOT NULL THEN
1287 enrp_chk_unit_over(p_unit_ovr_step_3,NULL,RTRIM(SUBSTR(p_unit_cd_ovr_3,1,10)),
1288 TO_NUMBER(RTRIM(SUBSTR(p_unit_cd_ovr_3,12,3))),p_unit_section_ovr_3);
1289 END IF;
1290
1291 --
1292 -- for parameter set 8
1293 IF p_person_ovr_step_4 IS NOT NULL THEN
1294 enrp_chk_pers_over(p_person_ovr_step_4,FALSE);
1295 END IF;
1296
1297 IF p_unit_ovr_step_4 IS NOT NULL THEN
1298 enrp_chk_unit_over(p_unit_ovr_step_4,NULL,RTRIM(SUBSTR(p_unit_cd_ovr_4,1,10)),
1299 TO_NUMBER(RTRIM(SUBSTR(p_unit_cd_ovr_4,12,3))),p_unit_section_ovr_4);
1300 END IF;
1301
1302 END enrp_ins_step_over;
1303
1304 --
1305 -- Begin of the procedure enrp_elgbl_override
1306 --
1307 BEGIN
1308
1309 RETCODE :=0;
1310 igs_ge_gen_003.set_org_id(p_org_id);
1311
1312 --
1313 -- get the details of calendar,program, Unit code and unit set code from the parameters
1314 --
1315 l_cal_type := RTRIM(SUBSTR(p_trm_teach_cal_type_comb,101,10));
1316 l_ci_seq_number := TO_NUMBER(RTRIM(SUBSTR(p_trm_teach_cal_type_comb,112,6)));
1317
1318 IF p_program_cd_comb IS NOT NULL THEN
1319 l_course_cd := RTRIM(SUBSTR(p_program_cd_comb,1,6));
1320 l_course_version := TO_NUMBER(RTRIM(SUBSTR(p_program_cd_comb,8)));
1321 END IF;
1322
1323 IF p_unit_cd_comb IS NOT NULL THEN
1324 l_unit_cd := RTRIM(SUBSTR(p_unit_cd_comb,1,10));
1325 l_unit_version :=TO_NUMBER(RTRIM(SUBSTR(p_unit_cd_comb,12)));
1326 END IF;
1327
1328 IF p_unit_set_comb IS NOT NULL THEN
1329 l_unit_set_cd :=RTRIM(SUBSTR(p_unit_set_comb,1,10));
1330 l_unit_set_version :=TO_NUMBER(RTRIM(SUBSTR(p_unit_set_comb,12)));
1331 END IF;
1332 IF p_org_unit_cd IS NOT NULL THEN
1333 OPEN cur_org_unit_st_dt(p_org_unit_cd);
1334 FETCH cur_org_unit_st_dt INTO l_org_unit_start_dt ;
1335 CLOSE cur_org_unit_st_dt;
1336 END IF;
1337 --
1338 -- call the procedure below to log all the parameters passed to this function
1339 IF NOT enrp_log_para(l_message_name) THEN
1340 -- no steps passed in parameter to override
1341 Fnd_Message.SET_NAME ('IGS',l_message_name);
1342 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET );
1343
1344 retcode :=2;
1345 RETURN;
1346 END IF;
1347
1348 --
1349 -- Validate parameters.
1350 -- all the parameters for the container procedure will be available to the inner function
1351 --
1352 IF NOT enrp_val_para(l_message_name) THEN
1353 -- validation failed log error message
1354 Fnd_Message.SET_NAME ('IGS',l_message_name);
1355 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET );
1356
1357 retcode :=2;
1358 RETURN;
1359 END IF;
1360 IF l_message_name IS NOT NULL THEN
1361 Fnd_Message.SET_NAME ('IGS',l_message_name);
1362 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET );
1363
1364 END IF;
1365 --
1366 -- loop through all the students who satisfied the cursor criteria
1367 -- and create the override records for the steps passed in parameters
1368 --
1369 OPEN cur_get_cal_cat(l_cal_type);
1370 FETCH cur_get_cal_cat INTO l_cal_cat;
1371 CLOSE cur_get_cal_cat;
1372
1373 l_is_teach_cal := FALSE;
1374
1375 IF l_cal_cat = 'LOAD' THEN
1376 l_load_cal_type := l_cal_type;
1377 l_load_cal_seq_num := l_ci_seq_number;
1378 ELSIF l_cal_cat = 'TEACHING' THEN
1379 l_is_teach_cal := TRUE;
1380 OPEN cur_load_cal(l_cal_type,l_ci_seq_number);
1381 FETCH cur_load_cal INTO l_load_cal_type,l_load_cal_seq_num;
1382 CLOSE cur_load_cal;
1383 ELSE
1384 l_load_cal_type := NULL;
1385 l_load_cal_seq_num := NULL;
1386 END IF;
1387
1388 FOR cur_criteria_satisfied_rec IN cur_criteria_satisfied(l_course_cd ,l_course_version,l_unit_cd, l_unit_version,
1389 l_unit_set_cd,l_unit_set_version, l_org_unit_start_dt,
1390 l_load_cal_type,l_load_cal_seq_num ) LOOP
1391 --
1392 -- log the message that for the following persons override records have been created
1393
1394 IF NOT l_record_found THEN
1395 l_record_found := TRUE;
1396 Fnd_Message.SET_NAME('IGS','IGS_EN_PERSONS_OVERRIDE');
1397 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET );
1398
1399 END IF;
1400
1401 OPEN cur_elgb_over(cur_criteria_satisfied_rec.person_id,l_cal_type,l_ci_seq_number);
1402 FETCH cur_elgb_over INTO l_elgb_override_id;
1403
1404 --
1405 -- if override record is not exists for the given student in given calendar(Load/Teach)
1406 IF cur_elgb_over%NOTFOUND THEN
1407 -- create override record for the given student in given calendar(Load/Teach)
1408 l_rowid :=NULL;
1409 Igs_En_Elgb_Ovr_Pkg.insert_row(x_rowid => l_rowid,
1410 x_elgb_override_id => l_elgb_override_id,
1411 x_person_id => cur_criteria_satisfied_rec.person_id,
1412 x_cal_type => l_cal_type,
1413 x_ci_sequence_number => l_ci_seq_number,
1414 x_mode => 'R');
1415 END IF;
1416 CLOSE cur_elgb_over;
1417 --
1418 -- call the procedure to create override records correponding to the steps paased as parameters
1419 --
1420 enrp_ins_step_over(cur_criteria_satisfied_rec.person_id,l_cal_type,l_ci_seq_number,l_elgb_override_id,l_unit_cd,l_unit_version,l_is_teach_cal );
1421
1422 END LOOP; -- cur_criteria_satisfied_rec
1423
1424 -- l_record_found is initialised with False at declaration.When an override record doesn't exists then
1425 -- it retains same value and the below If condition evaluates to True and logs Message 'No Student was
1426 -- selected for the given criteria' in log file.
1427
1428 IF NOT l_record_found THEN
1429 Fnd_Message.SET_NAME('IGS','IGS_EN_NO_STDNT_FOUND');
1430 Fnd_File.PUT_LINE(Fnd_File.LOG,Fnd_Message.GET );
1431 END IF;
1432
1433 EXCEPTION
1434 WHEN OTHERS THEN
1435
1436 retcode:=2;
1437 Fnd_File.PUT_LINE(Fnd_File.LOG,SQLERRM);
1438
1439 ERRBUF := Fnd_Message.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1440 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
1441
1442 END enrp_elgbl_override;
1443
1444 END igs_en_elgbl_override;
[Home] [Help]