1 PACKAGE BODY IGS_EN_GEN_002 AS
2 /* $Header: IGSEN02B.pls 120.1 2006/01/15 23:05:12 ctyagi noship $ */
3
4 --History
5 --Who When What
6 --PTANDON 13-Jun-2003 Modified the Function Enrp_Get_Acad_Alt_Cd to skip recursive check to find the superior
7 -- Academic Calendar instance if the subordinate calendar instance is a Load Calendar
8 -- Instance - Bug# 2917463
9
10 Function Enrp_Ext_Enrl_Form(
11 p_key IN VARCHAR2 ,
12 p_log_type IN VARCHAR2,
13 p_message_name out NOCOPY Varchar2 )
14 RETURN BOOLEAN AS
15
16 BEGIN -- enrp_ext_enrl_form
17 -- This module extracts enrolment forms data from the IGS_GE_S_LOG_ENTRY table
18 -- and writes it to the server host.
19 DECLARE
20
21 -- commented for conc issue...
22 --cst_enrol_form CONSTANT VARCHAR2(10) := 'ENROL-FORM';
23
24 CURSOR c_sle IS
25 SELECT sle.text
26 FROM IGS_GE_S_LOG_ENTRY sle,
27 IGS_GE_S_LOG sl
28 WHERE sl.key = p_key AND
29 sl.s_log_type = p_log_type AND
30 sle.s_log_type = sl.s_log_type AND
31 sle.creation_dt = sl.creation_dt
32 ORDER BY sle.sequence_number;
33
34 v_sle_rec c_sle%ROWTYPE;
35 v_sle_found BOOLEAN DEFAULT FALSE;
36 BEGIN
37 -- Set the default message number
38 p_message_name := null;
39 -- loop through records found and write out NOCOPY each line
40 -- fetched to the output file
41 FOR v_sle_rec IN c_sle LOOP
42 v_sle_found := TRUE;
43 FND_FILE.PUT_LINE(
44 FND_FILE.OUTPUT,
45 v_sle_rec.text);
46 END LOOP;
47 IF NOT v_sle_found THEN
48 -- close output file
49 -- anilk, bug#2744709
50 p_message_name := 'IGS_EN_NOENR_FORM_DATA_FOUND';
51
52 RETURN FALSE;
53 END IF;
54 -- Return the default value
55 RETURN TRUE;
56 EXCEPTION
57 WHEN OTHERS THEN
58 IF c_sle%ISOPEN THEN
59 CLOSE c_sle;
60 END IF;
61 p_message_name := 'IGS_EN_NOTWRITE_ENR_EXTFILE';
62 RETURN FALSE;
63 END;
64 EXCEPTION
65 WHEN OTHERS THEN
66 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
67 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_002.enrp_ext_enrl_form');
68 IGS_GE_MSG_STACK.ADD;
69 App_Exception.Raise_Exception;
70 END enrp_ext_enrl_form;
71
72
73 Function Enrp_Get_1st_Enr_Crs(
74 p_person_id IN NUMBER ,
75 p_course_cd IN VARCHAR2 )
76 RETURN VARCHAR2 AS
77 BEGIN
78 DECLARE
79 v_output VARCHAR2(25);
80 v_first_enrolment_course VARCHAR2(25);
81 CURSOR c_suav_details (
82 cp_person_id IGS_PE_PERSON.person_id%TYPE,
83 cp_course_cd IGS_PS_COURSE.course_cd%TYPE) IS
84 SELECT IGS_EN_GEN_014.enrs_get_acad_alt_cd(SUAV.cal_type,SUAV.ci_sequence_number)
85 ||'/'|| IGS_CA_GEN_001.calp_get_alt_cd(SUAV.cal_type,SUAV.ci_sequence_number)
86 FROM IGS_EN_SU_ATTEMPT SUAV,
87 IGS_CA_INST CI
88 WHERE SUAV.person_id = cp_person_id AND
89 SUAV.course_cd = cp_course_cd AND
90 SUAV.enrolled_dt IS NOT NULL AND
91 SUAV.cal_type = CI.cal_type AND
92 SUAV.ci_sequence_number = CI.sequence_number
93 ORDER BY CI.start_dt,
94 CI.end_dt ;
95 BEGIN
96 -- This module determines the enrolment period a
97 -- IGS_PE_PERSON is first enrolled in for a specified
98 -- IGS_PS_COURSE and returns it. A null value may
99 -- also be returned.
100 -- Retrieving the student IGS_PS_UNIT attempt records for the
101 -- IGS_PE_PERSON's IGS_PS_COURSE attempt.
102 -- The order the records are returned will ensure that
103 -- the oldest IGS_PS_UNIT attempt record for the IGS_PE_PERSON's IGS_PS_COURSE
104 -- attempt is returned first.
105 -- modified cursor c_suav_details for performance bug 3687016
106 OPEN c_suav_details(p_person_id,
107 p_course_cd);
108 FETCH c_suav_details INTO v_first_enrolment_course;
109 IF (c_suav_details%FOUND) THEN
110 CLOSE c_suav_details;
111 v_output := v_first_enrolment_course;
112 RETURN v_output;
113 ELSE -- no records are found
114 CLOSE c_suav_details;
115 v_output := NULL;
116 RETURN v_output;
117 END IF;
118 EXCEPTION
119 WHEN OTHERS THEN
120 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
121 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_002.enrp_get_1st_enr_crs');
122 IGS_GE_MSG_STACK.ADD;
123 App_Exception.Raise_Exception;
124 END;
125 END enrp_get_1st_enr_crs;
126
127
128 Function Enrp_Get_Acad_Alt_Cd(
129 p_cal_type IN VARCHAR2 ,
130 p_ci_sequence_number IN NUMBER ,
131 p_acad_cal_type OUT NOCOPY VARCHAR2 ,
132 p_acad_ci_sequence_number OUT NOCOPY NUMBER ,
133 p_acad_ci_start_dt OUT NOCOPY DATE ,
134 p_acad_ci_end_dt OUT NOCOPY DATE ,
135 p_message_name out NOCOPY Varchar2 )
136 RETURN VARCHAR2 AS
137
138 gv_acad_rec_found BOOLEAN := FALSE;
139 gv_alternate_code IGS_CA_INST.alternate_code%TYPE;
140 gv_s_cal_cat IGS_CA_TYPE.s_cal_cat%TYPE;
141
142 BEGIN -- enrp_get_acad_alt_cd
143 -- Get the alternate_code for the superior ACADEMIC IGS_CA_INST of the
144 -- IGS_CA_INST passed
145 DECLARE
146 -- This function recursively track down the first superior academic
147 -- IGS_CA_INST (deep first recursive). Returning TRUE if it can find one,
148 -- FALSE otherwise
149
150 CURSOR c_check_s_cal_cat (cp_cal_type IGS_CA_TYPE.cal_type%TYPE) IS
151 SELECT cat.s_cal_cat
152 FROM IGS_CA_TYPE cat
153 WHERE cat.cal_type = cp_cal_type;
154
155 CURSOR c_get_super_ci_for_load (cp_cal_type IGS_CA_INST.cal_type%TYPE,
156 cp_ci_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
157 SELECT cir.sup_cal_type,
158 cir.sup_ci_sequence_number,
159 ci.start_dt,
160 ci.end_dt,
161 ci.alternate_code
162 FROM IGS_CA_INST_REL cir,
163 IGS_CA_INST ci,
164 IGS_CA_TYPE cat
165 WHERE cir.sub_cal_type = cp_cal_type AND
166 cir.sub_ci_sequence_number = cp_ci_sequence_number AND
167 ci.cal_type = cir.sup_cal_type AND
168 ci.sequence_number = cir.sup_ci_sequence_number AND
169 cat.cal_type = ci.cal_type AND
170 cat.s_cal_cat = 'ACADEMIC'
171 ORDER BY
172 ci.start_dt asc,
173 ci.end_dt asc;
174
175 FUNCTION enrpl_get_acad_alt_cd_re (cp_cal_type IGS_CA_INST.cal_type%TYPE,
176 cp_ci_sequence_number IGS_CA_INST.sequence_number%TYPE)
177 RETURN BOOLEAN
178 AS
179 BEGIN
180 DECLARE
181
182 CURSOR c_get_super_ci (cp_cal_type IGS_CA_INST.cal_type%TYPE,
183 cp_ci_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
184 SELECT cir.sup_cal_type,
185 cir.sup_ci_sequence_number,
186 ci.start_dt,
187 ci.end_dt,
188 ci.alternate_code,
189 cat.s_cal_cat
190 FROM IGS_CA_INST_REL cir,
191 IGS_CA_INST ci,
192 IGS_CA_TYPE cat
193 WHERE cir.sub_cal_type = cp_cal_type AND
194 cir.sub_ci_sequence_number = cp_ci_sequence_number AND
195 ci.cal_type = cir.sup_cal_type AND
196 ci.sequence_number = cir.sup_ci_sequence_number AND
197 cat.cal_type = ci.cal_type
198 ORDER BY
199 ci.start_dt asc,
200 ci.end_dt asc;
201 BEGIN
202 FOR super_ci_rec IN c_get_super_ci(cp_cal_type, cp_ci_sequence_number) LOOP
203 IF (super_ci_rec.s_cal_cat = 'ACADEMIC') THEN
204 p_acad_cal_type := super_ci_rec.sup_cal_type;
205 p_acad_ci_sequence_number := super_ci_rec.sup_ci_sequence_number;
206 p_acad_ci_start_dt := super_ci_rec.start_dt;
207 p_acad_ci_end_dt := super_ci_rec.end_dt;
208 gv_alternate_code := super_ci_rec.alternate_code;
209 -- found it! exit right away
210 gv_acad_rec_found := TRUE;
211 EXIT;
212 ELSE
213 -- recursively process this superior cal instance
214 IF (enrpl_get_acad_alt_cd_re(super_ci_rec.sup_cal_type,
215 super_ci_rec.sup_ci_sequence_number) = TRUE) THEN
216 EXIT;
217 END IF;
218 END IF;
219 END LOOP;
220 IF (gv_acad_rec_found = TRUE) THEN
221 RETURN TRUE;
222 END IF;
223 RETURN FALSE;
224 /*
225 EXCEPTION
226 WHEN OTHERS THEN
227 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
228 App_Exception.Raise_Exception;
229 */
230 END;
231 END enrpl_get_acad_alt_cd_re;
232 BEGIN
233 OPEN c_check_s_cal_cat(p_cal_type);
234 FETCH c_check_s_cal_cat INTO gv_s_cal_cat;
235 CLOSE c_check_s_cal_cat;
236
237 p_message_name := null;
238 IF gv_s_cal_cat='LOAD' THEN
239 FOR super_ci_rec IN c_get_super_ci_for_load(p_cal_type, p_ci_sequence_number) LOOP
240 p_acad_cal_type := super_ci_rec.sup_cal_type;
241 p_acad_ci_sequence_number := super_ci_rec.sup_ci_sequence_number;
242 p_acad_ci_start_dt := super_ci_rec.start_dt;
243 p_acad_ci_end_dt := super_ci_rec.end_dt;
244 gv_alternate_code := super_ci_rec.alternate_code;
245 -- found it! exit right away
246 gv_acad_rec_found := TRUE;
247 EXIT;
248 END LOOP;
249 IF (gv_acad_rec_found = TRUE) THEN
250 RETURN gv_alternate_code;
251 END IF;
252 ELSE
253 IF (enrpl_get_acad_alt_cd_re (p_cal_type,
254 p_ci_sequence_number) = TRUE) THEN
255 RETURN gv_alternate_code;
256 END IF;
257 END IF;
258 p_message_name := 'IGS_EN_SUP_ACACAL_INST_NOTDFN';
259 RETURN NULL;
260 END;
261 /*
262 EXCEPTION
263 WHEN OTHERS THEN
264 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
265 App_Exception.Raise_Exception;
266 */
267 END enrp_get_acad_alt_cd;
268
269
270 Function Enrp_Get_Acad_Comm(
271 p_acad_cal_type IN VARCHAR2 ,
272 p_acad_ci_sequence_number IN NUMBER ,
273 p_person_id IN NUMBER ,
274 p_course_cd IN VARCHAR2 ,
275 p_adm_admission_appl_number IN NUMBER ,
276 p_adm_nominated_course_cd IN VARCHAR2 ,
277 p_adm_sequence_number IN NUMBER ,
278 p_chk_adm_prpsd_comm_ind IN VARCHAR2)
279 RETURN DATE AS
280
281 --History
282 --Who When What
283 --stutta 24-Aug-2004 Reverted back the check to p_chk_adm_prpsd_comm_ind = 'N'
284 -- Bug#3793016
285 BEGIN
286 -- enrp_get_acad_comm
287 -- This module gets the default student IGS_PS_COURSE attempt commencement date.
288 -- This is used by ENRF3000 and ENRP_INS_SNEW_PRENRL.
289 DECLARE
290 cst_transfer CONSTANT VARCHAR2(10) := 'TRANSFER';
291 cst_academic CONSTANT VARCHAR2(10) := 'ACADEMIC';
292 CURSOR c_sca IS
293 SELECT MIN(sca.commencement_dt) commencement_dt
294 FROM IGS_PS_STDNT_TRN sct,
295 IGS_EN_STDNT_PS_ATT sca,
296 IGS_PS_VER crv
297 WHERE sct.person_id = p_person_id AND
298 sct.course_cd = p_course_cd AND
299 sct.person_id = sca.person_id AND
300 sct.transfer_course_cd = sca.course_cd AND
301 crv.course_cd = sca.course_cd AND
302 crv.version_number = sca.version_number AND
303 crv.generic_course_ind = 'Y';
304 v_sca_rec c_sca%ROWTYPE;
305
306
307 CURSOR c_daiv (
308 cp_acad_cal_type IGS_CA_DA_INST_V.cal_type%TYPE,
309 cp_acad_ci_sequence_number IGS_CA_DA_INST_V.ci_sequence_number%TYPE) IS
310 SELECT MIN(IGS_CA_GEN_001.calp_set_alias_value
311 (
312 daiv.absolute_val,
313 IGS_CA_GEN_002.cals_clc_dt_from_dai
314 (
315 daiv.ci_sequence_number,
316 daiv.CAL_TYPE,
317 daiv.DT_ALIAS,
318 daiv.sequence_number
319 )
320 )
321 ) alias_val
322 FROM IGS_CA_DA_INST daiv,
323 IGS_EN_CAL_CONF secc
324 WHERE daiv.cal_type = cp_acad_cal_type AND
325 daiv.ci_sequence_number = cp_acad_ci_sequence_number AND
326 secc.commencement_dt_alias = daiv.dt_alias AND
327 secc.s_control_num = 1;
328 v_daiv_rec c_daiv%ROWTYPE;
329
330 CURSOR c_acaiv IS
331 SELECT NVL(acai.adm_cal_type, aa.adm_cal_type) adm_cal_type,
332 NVL(acai.adm_ci_sequence_number, aa.adm_ci_sequence_number) adm_ci_sequence_number,
333 acai.prpsd_commencement_dt prpsd_commencement_dt
334 FROM
335 IGS_AD_PS_APPL_INST acai,
336 IGS_AD_APPL aa,
337 IGS_CA_INST ci,
338 IGS_AD_PS_APPL aca,
339 IGS_PS_VER crv
340 WHERE acai.person_id = p_person_id AND
341 aa.person_id = acai.person_id AND
342 acai.admission_appl_number = p_adm_admission_appl_number AND
343 aa.admission_appl_number = acai.admission_appl_number AND
344 ci.cal_type (+) = acai.deferred_adm_cal_type AND
345 ci.sequence_number (+) = acai.deferred_adm_ci_sequence_num AND
346 aca.person_id = acai.person_id AND
347 aca.admission_appl_number = acai.admission_appl_number AND
348 acai.nominated_course_cd = p_adm_nominated_course_cd AND
349 aca.nominated_course_cd = acai.nominated_course_cd AND
350 crv.course_cd = acai.course_cd AND
351 crv.version_number = acai.crv_version_number AND
352 acai.sequence_number = p_adm_sequence_number;
353 v_acaiv_rec c_acaiv%ROWTYPE;
354
355 CURSOR c_aa IS
356 SELECT aa.s_admission_process_type
357 FROM IGS_AD_APPL aa
358 WHERE person_id = p_person_id AND
359 admission_appl_number = p_adm_admission_appl_number;
360 v_aa_rec c_aa%ROWTYPE;
361 CURSOR c_scae_cir_ci_cat IS
362 SELECT cir.sup_cal_type,
363 cir.sup_ci_sequence_number
364 FROM IGS_AS_SC_ATMPT_ENR scae,
365 IGS_CA_INST_REL cir,
366 IGS_CA_INST ci,
367 IGS_CA_TYPE cat
368 WHERE scae.person_id = p_person_id AND
369 scae.course_cd = p_course_cd AND
370 scae.cal_type = cir.sub_cal_type AND
371 scae.ci_sequence_number = cir.sub_ci_sequence_number AND
375 cat.s_cal_cat = cst_academic
372 cir.sub_cal_type = ci.cal_type AND
373 cir.sub_ci_sequence_number = ci_sequence_number AND
374 cir.sup_cal_type = cat.cal_type AND
376 ORDER BY ci.start_dt ASC;
377 v_acad_cal_type IGS_CA_INST_REL.sup_cal_type%TYPE;
378 v_acad_ci_sequence_number
379 IGS_CA_INST_REL.sup_ci_sequence_number%TYPE;
380 v_course_start_dt DATE DEFAULT NULL;
381 BEGIN
382
383 -- Determine that student IGS_PS_COURSE attempt is not the result of a
384 -- generic IGS_PS_COURSE transfer
385 OPEN c_sca;
386 FETCH c_sca INTO v_sca_rec;
387 IF c_sca%FOUND AND
388 v_sca_rec.commencement_dt IS NOT NULL THEN
389 CLOSE c_sca;
390 -- Default to commencement date of generic IGS_PS_COURSE
391 RETURN v_sca_rec.commencement_dt;
392 END IF;
393 CLOSE c_sca;
394 IF p_adm_admission_appl_number IS NULL AND
395 p_adm_nominated_course_cd IS NULL AND
396 p_adm_sequence_number IS NULL THEN
397 -- Student IGS_PS_COURSE attempt is not the result of an admissions
398 -- application offer, therefore default commencement date to
399 -- latest of today?s date and academic commencement date
400 IF p_acad_cal_type IS NULL OR
401 p_acad_ci_sequence_number IS NULL THEN
402 --Determine academic period from latest enrolment period
403 OPEN c_scae_cir_ci_cat;
404 FETCH c_scae_cir_ci_cat INTO
405 v_acad_cal_type,
406 v_acad_ci_sequence_number;
407 IF c_scae_cir_ci_cat%NOTFOUND THEN
408 CLOSE c_scae_cir_ci_cat;
409 RETURN TRUNC(SYSDATE);
410 END IF;
411 CLOSE c_scae_cir_ci_cat;
412 ELSE
413 v_acad_cal_type := p_acad_cal_type;
414 v_acad_ci_sequence_number := p_acad_ci_sequence_number;
415 END IF;
416 OPEN c_daiv (
417 v_acad_cal_type,
418 v_acad_ci_sequence_number);
419 FETCH c_daiv INTO v_daiv_rec;
420 CLOSE c_daiv;
421 IF v_daiv_rec.alias_val IS NULL OR
422 v_daiv_rec.alias_val < TRUNC(SYSDATE) THEN
423 -- Academic commencement date unable to be determined,
424 -- return today?s date
425 RETURN TRUNC(SYSDATE);
426 END IF;
427 RETURN v_daiv_rec.alias_val;
428 END IF;
429 -- Student IGS_PS_COURSE attempt is the result of an admissions offer, therefore
430 -- default to latest of admission period IGS_PS_COURSE start date and today?s date
431 OPEN c_acaiv;
432 FETCH c_acaiv INTO v_acaiv_rec;
433 IF c_acaiv%FOUND THEN
434 CLOSE c_acaiv;
435 IF v_acaiv_rec.prpsd_commencement_dt IS NULL OR
436 NVL(p_chk_adm_prpsd_comm_ind,'N') = 'N' THEN
437 -- Get IGS_PS_COURSE start date of admission period of offer
438
439 v_course_start_dt := IGS_AD_GEN_005.admp_get_crv_strt_dt(
440 v_acaiv_rec.adm_cal_type,
441 v_acaiv_rec.adm_ci_sequence_number);
442
443 IF v_course_start_dt IS NULL OR
444 v_course_start_dt < TRUNC(SYSDATE) THEN
445 -- Admission IGS_PS_COURSE start date could not be determined,
446 -- return today?s date
447 RETURN TRUNC(SYSDATE);
448 END IF;
449 -- Determine if admission application is a IGS_PS_COURSE transfer
450 OPEN c_aa;
451 FETCH c_aa INTO v_aa_rec;
452 IF c_aa%NOTFOUND OR
453 v_aa_rec.s_admission_process_type = cst_transfer THEN
454 CLOSE c_aa;
455 RETURN TRUNC(SYSDATE);
456 END IF;
457 CLOSE c_aa;
458 RETURN v_course_start_dt;
459 ELSE
460 RETURN v_acaiv_rec.prpsd_commencement_dt;
461 END IF;
462 END IF;
463 CLOSE c_acaiv;
464 -- Admission IGS_PS_COURSE start date could not be determined,
465 -- return today?s date
466 RETURN TRUNC(SYSDATE);
467 EXCEPTION
468 WHEN OTHERS THEN
469 IF c_acaiv%ISOPEN THEN
470 CLOSE c_acaiv;
471 END IF;
472 IF c_sca%ISOPEN THEN
473 CLOSE c_sca;
474 END IF;
475 IF c_daiv%ISOPEN THEN
476 CLOSE c_daiv;
477 END IF;
478 IF c_aa%ISOPEN THEN
479 CLOSE c_aa;
480 END IF;
481 IF c_scae_cir_ci_cat%ISOPEN THEN
482 CLOSE c_scae_cir_ci_cat;
483 END IF;
484 RAISE;
485
486 END;
487 /*
488 EXCEPTION
489 WHEN OTHERS THEN
490 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
491 App_Exception.Raise_Exception;
492 */
493 END enrp_get_acad_comm;
494
495
496 Function Enrp_Get_Acad_P_Att(
497 p_load_figure IN NUMBER )
498 RETURN VARCHAR2 AS
499 BEGIN
500 DECLARE
501 v_record_found BOOLEAN;
502 v_record_count NUMBER;
503 v_attendance_type IGS_EN_ATD_TYPE.attendance_type%TYPE;
504 CURSOR c_attendance_type(
505 cp_load_figure IGS_EN_ATD_TYPE.lower_enr_load_range%TYPE) IS
506 SELECT attendance_type
507 FROM IGS_EN_ATD_TYPE
508 WHERE lower_enr_load_range <= p_load_figure AND
509 upper_enr_load_range >= p_load_figure AND
510 lower_enr_load_range <> 0 AND -- testing for non zero ensures a
511 upper_enr_load_range <> 0; -- valid derivable load range has
512 -- been specified
513 BEGIN
514 -- Get the attendance type for a nominated load figure.
515 -- This is done by searching for an attendance_type record which specifies
516 -- the load ranges for the different attendance types within the academic
517 -- period. If no record is found then NULL is returned, as it is not possible
521 END IF;
518 -- to derive the figure.
519 IF p_load_figure = 0 THEN
520 RETURN NULL;
522 v_record_found := FALSE;
523 v_record_count := 0;
524 FOR v_attendance_type_rec IN c_attendance_type(
525 p_load_figure)
526 LOOP
527 v_record_found := TRUE;
528 v_record_count := v_record_count + 1;
529 v_attendance_type := v_attendance_type_rec.attendance_type;
530 END LOOP;
531 IF(v_record_found = FALSE) THEN
532 RETURN NULL;
533 END IF;
534 IF(v_record_count > 1) THEN
535 RETURN NULL;
536 ELSE
537 RETURN v_attendance_type;
538 END IF;
539 EXCEPTION
540 WHEN OTHERS THEN
541 IF SQLCODE <> -20001 THEN
542 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXP');
543 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_002.enrp_get_acad_p_att');
544 IGS_GE_MSG_STACK.ADD;
545 App_Exception.Raise_Exception(NULL, NULL, fnd_message.get);
546 ELSE
547 RAISE;
548 END IF;
549 -- Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXP');
550 -- App_Exception.Raise_Exception ;
551 END;
552 END enrp_get_acad_p_att;
553
554
555 Function Enrp_Get_Acai_Offer(
556 p_adm_outcome_status IN VARCHAR2 ,
557 p_adm_offer_resp_status IN VARCHAR2 )
558 RETURN VARCHAR2 AS
559 CURSOR c_aos IS
560 SELECT s_adm_outcome_status
561 FROM IGS_AD_OU_STAT aos
562 WHERE adm_outcome_status = p_adm_outcome_status;
563
564 v_s_adm_outcome_status IGS_AD_OU_STAT.s_adm_outcome_status%TYPE;
565
566 CURSOR c_aors IS
567 SELECT s_adm_offer_resp_status
568 FROM IGS_AD_OFR_RESP_STAT aors
569 WHERE adm_offer_resp_status = p_adm_offer_resp_status;
570
571 v_s_adm_offer_resp_status IGS_AD_OFR_RESP_STAT.s_adm_offer_resp_status%TYPE;
572 gv_other_detail varchar2(255);
573 BEGIN
574 OPEN c_aos;
575 FETCH c_aos INTO v_s_adm_outcome_status;
576 IF c_aos%NOTFOUND THEN
577 CLOSE c_aos;
578 RETURN 'N';
579 ELSE
580 IF v_s_adm_outcome_status NOT IN ('OFFER','COND-OFFER') THEN
581 CLOSE c_aos;
582 RETURN 'N';
583 ELSE
584 -- Select the system offer response status. Check that it hasn't lapsed or been rejected.
585 OPEN c_aors;
586 FETCH c_aors INTO v_s_adm_offer_resp_status;
587 IF c_aors%NOTFOUND THEN
588 CLOSE c_aos;
589 CLOSE c_aors;
590 RETURN 'N';
591 ELSE
592 IF v_s_adm_offer_resp_status IN ('LAPSED','REJECTED') THEN
593 CLOSE c_aos;
594 CLOSE c_aors;
595 RETURN 'N';
596 ELSE
597 CLOSE c_aos;
598 CLOSE c_aors;
599 RETURN 'Y';
600 END IF;
601 END IF;
602 END IF;
603 END IF;
604 END enrp_get_acai_offer;
605
606
607 Function Enrp_Get_Att_Dflt(
608 p_course_cd IN VARCHAR2 ,
609 p_version_number IN NUMBER ,
610 p_attendance_type IN VARCHAR2 ,
611 p_load_cal_type IN VARCHAR2 ,
612 p_eftsu IN OUT NOCOPY NUMBER ,
613 p_credit_points OUT NOCOPY NUMBER )
614 RETURN boolean AS
615
616 BEGIN -- enrp_get_att_dflt
617 -- Description: Get the default EFTSU / CP value for an attendance type within
618 -- a nominated load calendar type for the first year of the coure. This
619 -- routine is designed for use in calculating prospective figures for
620 -- admissions fees. The EFTSU is calculated by using the
621 -- IGS_ST_DFT_LOAD_APPO.default_eftsu figure.
622 -- The CP is calculated by multiplying the proportion of the EFTSU into
623 -- 1.000 multiplied by the standard annual load of the first year of the
624 -- IGS_PS_COURSE.
625 DECLARE
626 v_atl_rec IGS_EN_ATD_TYPE_LOAD.default_eftsu%TYPE;
627 v_cal_rec IGS_PS_ANL_LOAD.annual_load_val%TYPE;
628 v_crv_rec IGS_PS_VER.std_annual_load%TYPE;
629 v_annual_load NUMBER;
630 CURSOR c_atl IS
631 SELECT atl.default_eftsu
632 FROM IGS_EN_ATD_TYPE_LOAD atl
633 WHERE atl.cal_type = p_load_cal_type AND
634 atl.attendance_type = p_attendance_type;
635 CURSOR c_cal IS
636 SELECT annual_load_val
637 FROM IGS_PS_ANL_LOAD cal
638 WHERE cal.course_cd = p_course_cd AND
639 cal.version_number = p_version_number
640 ORDER BY yr_num ASC;
641 CURSOR c_crv IS
642 SELECT std_annual_load
643 FROM IGS_PS_VER crv
644 WHERE crv.course_cd = p_course_cd AND
645 crv.version_number = p_version_number;
646 BEGIN
647 v_annual_load := 0;
648 --Select detail from default load apportion
649 OPEN c_atl;
650 FETCH c_atl INTO v_atl_rec;
651 IF (c_atl%NOTFOUND) THEN
652 CLOSE c_atl;
653 RETURN FALSE;
654 END IF;
655 CLOSE c_atl;
656 IF (v_atl_rec IS NULL) THEN
657 RETURN FALSE;
658 END IF;
659 p_EFTSU := v_atl_rec;
660 -- Determine the standard annual load from IGS_PS_COURSE structure tables.
661 OPEN c_cal;
662 FETCH c_cal INTO v_cal_rec;
663 IF (c_cal%FOUND) THEN
664 CLOSE c_cal;
665 v_annual_load := v_cal_rec;
666 ELSE
667 CLOSE c_cal;
668 OPEN c_crv;
669 FETCH c_crv INTO v_crv_rec;
670 IF (c_crv%NOTFOUND) THEN
671 CLOSE c_crv;
672 v_annual_load := 0;
673 ELSE
674 CLOSE c_crv;
678 p_credit_points := (p_EFTSU / 1.000) * v_annual_load;
675 v_annual_load := v_crv_rec;
676 END IF;
677 END IF;
679 RETURN TRUE;
680 EXCEPTION
681 WHEN OTHERS THEN
682 IF (c_atl%ISOPEN) THEN
683 CLOSE c_atl;
684 END IF;
685 IF (c_cal%ISOPEN) THEN
686 CLOSE c_cal;
687 END IF;
688 IF (c_crv%ISOPEN) THEN
689 CLOSE c_crv;
690 END IF;
691 RAISE;
692 END;
693 EXCEPTION
694 WHEN OTHERS THEN
695 if SQLCODE <> -20001 then
696 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXP');
697 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_002.enrp_get_att_dflt');
698 IGS_GE_MSG_STACK.ADD;
699 App_Exception.Raise_Exception(Null, Null, fnd_message.get);
700 else
701 RAISE;
702 end if;
703 -- Fnd_Message.Set_Name('IGS' , 'IGS_GE_UNHANDLED_EXP');
704 -- App_Exception.Raise_Exception ;
705 END enrp_get_att_dflt;
706
707
708 Procedure Enrp_Get_Crs_Exists(
709 P_PERSON_ID IN NUMBER ,
710 P_COURSE_CD IN VARCHAR2 ,
711 P_EFFECTIVE_DT IN DATE ,
712 p_acad_cal_type IN VARCHAR2 ,
713 p_acad_sequence_number IN NUMBER ,
714 p_check_hecs IN boolean ,
715 p_check_unitset IN boolean ,
716 p_check_notes IN boolean ,
717 p_check_research IN boolean ,
718 p_check_prenrol IN boolean ,
719 p_hecs_exists OUT NOCOPY boolean ,
720 p_unitset_exists OUT NOCOPY boolean ,
721 p_notes_exists OUT NOCOPY boolean ,
722 p_research_exists OUT NOCOPY boolean ,
723 p_prenrol_exists OUT NOCOPY boolean )
724 AS
725 BEGIN -- enrp_get_crs_exists
726 -- Return output parameters indicating whether or not data exists on IGS_PE_PERSON
727 -- IGS_PS_COURSE attempt detail tables for the specified IGS_PE_PERSON ID.
728 DECLARE
729 v_check VARCHAR2(1);
730 CURSOR c_scan
731 IS
732 SELECT 'x'
733 FROM IGS_AS_SC_ATMPT_NOTE scan
734 WHERE scan.person_id = p_person_id AND
735 Course_cd = p_course_cd;
736 CURSOR c_susa
737 IS
738 SELECT 'x'
739 FROM IGS_AS_SU_SETATMPT susa
740 WHERE susa.person_id = p_person_id AND
741 susa.course_cd = p_course_cd AND
742 susa.student_confirmed_ind = 'Y' AND
743 susa.end_dt IS NULL;
744 CURSOR c_scho
745 IS
746 SELECT 'x'
747 FROM IGS_EN_STDNTPSHECSOP scho
748 WHERE scho.person_id = p_person_id AND
749 scho.course_cd = p_course_cd AND
750 scho.start_dt <= p_effective_dt AND
751 (scho.end_dt IS NULL OR
752 scho.end_dt > p_effective_dt);
753 CURSOR c_ca
754 IS
755 SELECT 'x'
756 FROM IGS_RE_CANDIDATURE ca
757 WHERE ca.person_id = p_person_id AND
758 ca.sca_course_cd = p_course_cd;
759 CURSOR c_scae
760 IS
761 SELECT 'x'
762 FROM IGS_AS_SC_ATMPT_ENR scae
763 WHERE scae.person_id = p_person_id AND
764 scae.course_cd = p_course_cd AND
765 IGS_EN_GEN_014.ENRS_GET_WITHIN_CI(
766 p_acad_cal_type,
767 p_acad_sequence_number,
768 scae.cal_type,
769 scae.ci_sequence_number,
770 'N') = 'Y';
771 BEGIN
772 -- Initialise output parameters.
773 p_notes_exists := FALSE;
774 p_unitset_exists := FALSE;
775 p_hecs_exists := FALSE;
776 p_research_exists := FALSE;
777 p_prenrol_exists := FALSE;
778 IF p_check_notes = TRUE THEN
779 -- Check for the existence of a IGS_PE_PERSON Notes record.
780 OPEN c_scan;
781 FETCH c_scan INTO v_check;
782 IF c_scan%FOUND THEN
783 CLOSE c_scan;
784 p_notes_exists := TRUE;
785 ELSE
786 CLOSE c_scan;
787 END IF;
788 END IF;
789 IF p_check_unitset = TRUE THEN
790 -- Check for the existence of a Student IGS_PS_UNIT Set Attempt record.
791 OPEN c_susa;
792 FETCH c_susa INTO v_check;
793 IF c_susa%FOUND THEN
794 CLOSE c_susa;
795 p_unitset_exists := TRUE;
796 ELSE
797 CLOSE c_susa;
798 END IF;
799 END IF;
800 IF p_check_hecs = TRUE THEN
801 -- Check for the existence of a Student IGS_PS_COURSE HECS Option record.
802 OPEN c_scho;
803 FETCH c_scho INTO v_check;
804 IF c_scho%FOUND THEN
805 CLOSE c_scho;
806 p_hecs_exists := TRUE;
807 ELSE
808 CLOSE c_scho;
809 END IF;
810 END IF;
811 IF p_check_research = TRUE THEN
812 -- Check for the existence of a research IGS_RE_CANDIDATURE record
813 OPEN c_ca;
814 FETCH c_ca INTO v_check;
815 IF c_ca%FOUND THEN
816 CLOSE c_ca;
817 p_research_exists := TRUE;
818 ELSE
819 CLOSE c_ca;
820 END IF;
821 END IF;
822 IF p_check_prenrol = TRUE AND
823 p_acad_cal_type IS NOT NULL AND
824 p_acad_sequence_number IS NOT NULL THEN
825 -- Check for the existence of a Student IGS_PS_COURSE Enrolment record
826 OPEN c_scae;
827 FETCH c_scae INTO v_check;
828 IF c_scae%FOUND THEN
829 CLOSE c_scae;
830 p_prenrol_exists := TRUE;
831 ELSE
832 CLOSE c_scae;
833 END IF;
834 END IF;
835 EXCEPTION
836 WHEN OTHERS THEN
837 IF c_scan%ISOPEN THEN
838 CLOSE c_scan;
839 END IF;
840 IF c_susa%ISOPEN THEN
841 CLOSE c_susa;
842 END IF;
843 IF c_scho%ISOPEN THEN
844 CLOSE c_scho;
845 END IF;
846 IF c_ca%ISOPEN THEN
847 CLOSE c_ca;
848 END IF;
849 IF c_scae%ISOPEN THEN
850 CLOSE c_scae;
851 END IF;
852 RAISE;
853 END;
854 EXCEPTION
855 WHEN OTHERS THEN
856 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
857 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_002.enrp_get_crs_exists');
858 IGS_GE_MSG_STACK.ADD;
859 App_Exception.Raise_Exception;
860 END enrp_get_crs_exists;
861
862 END IGS_EN_GEN_002;
[Home] [Help]