1 PACKAGE BODY IGS_RE_GEN_001 AS
2 /* $Header: IGSRE01B.pls 120.1 2005/11/24 04:36:30 appldev ship $ */
3
4 -------------------------------------------------------------------------------------------
5 --Change History:
6 --Who When What
7 --Nishikant 11DEC2002 ENCR027 Build (Program Length Integration) . In the function RESP_CLC_MAX_SBMSN and
8 -- RESP_CLC_MIN_SBMSN the signature of the function call igs_ps_gen_002.crsp_get_crv_eftd got
9 -- modified, which used to get the value for the variable v_crv_eftd.
10 --vvutukur 19_oct-2002 Enh#2608227.Modified functions resp_clc_max_sbmsn,resp_clc_min_sbmsn.
11 --prchandr 08-Jan-01 Enh Bug No: 2174101, As the Part of Change in IGSEN18B
12 -- Passing NULL as parameters to ENRP_CLC_SUA_EFTSU
13 -- ENRP_CLC_EFTSU_TOTAL for Key course cd and version number
14 -------------------------------------------------------------------------------------------
15 FUNCTION RESP_CLC_EFTSU_TRUNC(
16 p_person_id IN NUMBER ,
17 p_ca_sequence_number IN NUMBER ,
18 p_unit_cd IN VARCHAR2 ,
19 p_version_number IN NUMBER ,
20 p_uoo_id IN NUMBER ,
21 p_census_dt IN DATE ,
22 p_eftsu IN NUMBER )
23 RETURN NUMBER AS
24 gv_other_detail VARCHAR2(255);
25 BEGIN -- resp_clc_eftsu_trunc
26 -- Routine to perform the necessary truncation on the DEETYA reported EFTSU
27 -- for research units - this is in accordance with the DEETYA guidelines, and
28 -- is required to be able to reconcile EFTSU figures calculated on a day to
29 -- day basis with those reported in the DEETYA submissions.
30 -- This routine will ?roll down? the EFTSU to the lowest common denominator,
31 -- truncate the value and then ?roll up? to the required level.
32 -- Research IGS_PS_UNIT AOU?s are defined by the links with their supervisors (or
33 -- if not specified, by the standards means).
34 DECLARE
35 CURSOR c_rsup (
36 cp_census_dt IGS_CA_DA_INST.absolute_val%TYPE)
37 IS
38 SELECT rsup.org_unit_cd,
39 rsup.ou_start_dt,
40 sum(rsup.funding_percentage) sum_fund_perc
41 FROM IGS_RE_SPRVSR rsup
42 WHERE rsup.ca_person_id = p_person_id AND
43 rsup.ca_sequence_number = p_ca_sequence_number AND
44 rsup.funding_percentage IS NOT NULL AND
45 rsup.funding_percentage > 0 AND
46 rsup.start_dt <= cp_census_dt AND
47 (rsup.end_dt IS NULL OR
48 rsup.end_dt >= cp_census_dt)
49 GROUP BY
50 rsup.org_unit_cd,
51 rsup.ou_start_dt;
52 CURSOR c_udis IS
53 SELECT udis.percentage
54 FROM IGS_PS_UNIT_DSCP udis
55 WHERE udis.unit_cd = p_unit_cd AND
56 udis.version_number = p_version_number;
57 CURSOR c_sgcc_dai IS
58 SELECT NVL(dai.absolute_val, IGS_CA_GEN_001.calp_get_alias_val(
59 dai.dt_alias,
60 dai.sequence_number,
61 dai.cal_type,
62 dai.ci_sequence_number)) census_dt
63 FROM IGS_PS_UNIT_OFR_OPT uoo,
64 IGS_GE_S_GEN_CAL_CON sgcc,
65 IGS_CA_DA_INST dai
66 WHERE uoo.uoo_id = p_uoo_id AND
67 sgcc.s_control_num = 1 and
68 dai.dt_alias = sgcc.census_dt_alias AND
69 dai.cal_type = uoo.cal_type AND
70 dai.ci_sequence_number = uoo.ci_sequence_number
71 ORDER BY census_dt;
72 v_census_dt IGS_CA_DA_INST.absolute_val%TYPE;
73 v_eftsu_total NUMBER;
74 -- So I can test whether any records were found for statement 2
75 v_records_found BOOLEAN := FALSE;
76 BEGIN
77 v_eftsu_total := 0.000;
78 IF p_census_dt IS NULL THEN
79 OPEN c_sgcc_dai;
80 FETCH c_sgcc_dai INTO v_census_dt;
81 IF c_sgcc_dai%NOTFOUND THEN
82 CLOSE c_sgcc_dai;
83 -- Bad parameters - return zero
84 RETURN 0;
85 END IF;
86 CLOSE c_sgcc_dai;
87 ELSE -- p_census_dt IS NOT NULL
88 v_census_dt := p_census_dt;
89 END IF;
90 FOR v_rsup_rec IN c_rsup (
91 v_census_dt) LOOP
92 FOR v_udis_rec IN c_udis LOOP
93 -- No reason to reenter this IF - when v_records_found is already true
94 IF NOT v_records_found THEN
95 -- A supervision percentage record found!
96 v_records_found := TRUE;
97 END IF;
98 v_eftsu_total :=
99 v_eftsu_total +
100 TRUNC(
101 (p_eftsu * (v_rsup_rec.sum_fund_perc / 100)
102 * (v_udis_rec.percentage / 100)),
103 3);
104 END LOOP;
105 END LOOP;
106 IF NOT v_records_found THEN
107 -- If no supervision percentage found then use the IGS_PS_UNIT values
108 v_eftsu_total := IGS_EN_PRC_LOAD.enrp_clc_eftsu_trunc(
109 p_unit_cd,
110 p_version_number,
111 p_uoo_id,
112 p_eftsu);
113 END IF;
114 RETURN v_eftsu_total;
115 EXCEPTION
116 WHEN OTHERS THEN
117 IF c_rsup %ISOPEN THEN
118 CLOSE c_rsup;
119 END IF;
120 IF c_udis %ISOPEN THEN
121 CLOSE c_udis;
122 END IF;
123 IF c_sgcc_dai%ISOPEN THEN
124 CLOSE c_sgcc_dai;
125 END IF;
126 RAISE;
127 END;
128 END resp_clc_eftsu_trunc;
129
130
131 FUNCTION RESP_CLC_LOAD_EFTSU(
132 p_acad_cal_type IN VARCHAR2 ,
133 p_acad_ci_sequence_number IN NUMBER ,
134 p_load_cal_type IN VARCHAR2 ,
135 p_load_ci_sequence_number IN NUMBER )
136 RETURN NUMBER AS
137 gv_other_detail VARCHAR2(255);
138 BEGIN -- resp_clc_load_eftsu
139 -- Calculate the maximum EFTSU applicable in a load calendar according to the
140 -- percentage field held in the IGS_CA_INST_REL table for the
141 -- relationship between the academic and load calendar instances.
142 DECLARE
143 v_load_research_percentage
144 IGS_CA_INST_REL.load_research_percentage%TYPE;
145 CURSOR c_cir IS
146 SELECT cir.load_research_percentage
147 FROM IGS_CA_INST_REL cir
148 WHERE sup_cal_type = p_acad_cal_type AND
149 sup_ci_sequence_number = p_acad_ci_sequence_number AND
150 sub_cal_type = p_load_cal_type AND
151 sub_ci_sequence_number = p_load_ci_sequence_number;
152 BEGIN
153 OPEN c_cir;
154 FETCH c_cir INTO v_load_research_percentage;
155 IF v_load_research_percentage IS NULL THEN
156 CLOSE c_cir;
157 RETURN 0;
158 END IF;
159 CLOSE c_cir;
160 RETURN v_load_research_percentage/100;
161 EXCEPTION
162 WHEN OTHERS THEN
163 IF (c_cir%ISOPEN) THEN
164 CLOSE c_cir;
165 END IF;
166 RAISE;
167 END;
168 END resp_clc_load_eftsu;
169
170
171 FUNCTION RESP_CLC_MAX_SBMSN(
172 p_person_id IN NUMBER ,
173 p_ca_sequence_number IN NUMBER ,
174 p_sca_course_cd IN VARCHAR2 ,
175 p_acai_admission_appl_number IN NUMBER ,
176 p_acai_nominated_course_cd IN VARCHAR2 ,
177 p_acai_sequence_number IN NUMBER ,
178 p_attendance_percentage IN NUMBER ,
179 p_commencement_dt IN DATE )
180 RETURN DATE AS
181 /*----------------------------------------------------------------------------
182 || Created By :
183 || Created On :
184 || Purpose :
185 || Known limitations, enhancements or remarks :
186 || Change History :
187 || Who When What
188 || (reverse chronological order - newest change first)
189 || Nishikant 11DEC2002 The signature of the function call igs_ps_gen_002.crsp_get_crv_eftd got
190 || modified, which used to get the value for the variable v_crv_eftd
191 || vvutukur 19-Oct-2002 Enh#2608227.Added cursor cur_coo_id to fetch coo_id of course and modified
192 || call to igs_ps_gen_002.crsp_get_crv_eftd.
193 || svanukur 28-jul-2004 implemented an nvl check to pass p_acai_nominated_course_cd in the call to
194 || igs_ps_gen_002.crsp_get_crv_eftd bug 3487851
195 ----------------------------------------------------------------------------*/
196 gv_other_detail VARCHAR2(255);
197 BEGIN -- resp_clc_max_sbmsn
198 -- Calculate the minimum submission date of a student IGS_PS_COURSE attempt
199 -- IGS_RE_CANDIDATURE. This is calculated as the remaining EFTD (Effective
200 -- Full Time Days) remaining in the IGS_PS_COURSE multiplied by the minimum
201 -- submission percentage (stored against the students IGS_PS_COURSE version)
202 -- factored with the students current attendance percentage.
203 DECLARE
204 cst_unconfirm CONSTANT VARCHAR2(10) := 'UNCONFIRM';
205 v_ca_acai_adm_appl_number IGS_RE_CANDIDATURE.acai_admission_appl_number%TYPE;
206 v_ca_acai_nominated_course_cd IGS_RE_CANDIDATURE.acai_nominated_course_cd%TYPE;
207 v_ca_acai_sequence_number IGS_RE_CANDIDATURE.acai_sequence_number%TYPE;
208 v_ca_sca_course_cd IGS_RE_CANDIDATURE.sca_course_cd%TYPE;
209 v_ca_attendance_percentage IGS_RE_CANDIDATURE.attendance_percentage%TYPE;
210 v_candidature_exists_ind VARCHAR2(1);
211 v_sca_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE;
212 v_sca_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
213 v_sca_attendance_type IGS_EN_STDNT_PS_ATT.attendance_type%TYPE;
214 v_sca_course_attempt_status
215 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
216 v_sca_commencement_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
217 v_acai_course_cd IGS_PS_VER.course_cd%TYPE;
218 v_acai_version_number IGS_PS_VER.version_number%TYPE;
219 v_acai_attendance_type IGS_EN_ATD_TYPE.attendance_type%TYPE;
220 v_attendance_percentage IGS_EN_ATD_TYPE.research_percentage%TYPE;
221 v_att_attendance_percentage IGS_EN_ATD_TYPE.research_percentage%TYPE;
222 v_crv_eftd NUMBER;
223 v_used_eftd_days NUMBER;
224 v_eftd_remaining NUMBER;
225 v_remaining_days NUMBER;
226 v_commencement_dt DATE;
227
228 CURSOR c_ca IS
229 SELECT ca.acai_admission_appl_number,
230 ca.acai_nominated_course_cd,
231 ca.acai_sequence_number,
232 ca.sca_course_cd,
233 ca.attendance_percentage
234 FROM IGS_RE_CANDIDATURE ca
235 WHERE ca.person_id = p_person_id AND
236 ca.sequence_number = p_ca_sequence_number;
237 CURSOR c_sca IS
238 SELECT sca.course_cd,
239 sca.version_number,
240 sca.attendance_type,
241 sca.commencement_dt,
242 sca.course_attempt_status
243 FROM IGS_EN_STDNT_PS_ATT sca
244 WHERE sca.person_id = p_person_id AND
245 sca.course_cd = v_ca_sca_course_cd;
246
247 CURSOR c_acaiv IS
248 SELECT acai.course_cd,
249 acai.crv_version_number,
250 acai.attendance_type
251 FROM
252 IGS_AD_PS_APPL_INST acai,
253 IGS_AD_APPL aa,
254 IGS_CA_INST ci,
255 IGS_AD_PS_APPL aca,
256 IGS_PS_VER crv
257 WHERE acai.person_id = p_person_id AND
258 acai.admission_appl_number = v_ca_acai_adm_appl_number AND
259 acai.nominated_course_cd = v_ca_acai_nominated_course_cd AND
260 acai.sequence_number = v_ca_acai_sequence_number AND
261 aa.person_id = acai.person_id AND
262 aa.admission_appl_number = acai.admission_appl_number AND
263 ci.cal_type (+) = acai.deferred_adm_cal_type AND
264 ci.sequence_number (+) = acai.deferred_adm_ci_sequence_num AND
265 aca.person_id = acai.person_id AND
266 aca.admission_appl_number = acai.admission_appl_number AND
267 aca.nominated_course_cd = acai.nominated_course_cd AND
268 crv.course_cd = acai.course_cd AND
269 crv.version_number = acai.crv_version_number;
270
271 CURSOR c_att IS
272 SELECT att.research_percentage
273 FROM IGS_EN_ATD_TYPE att
274 WHERE att.attendance_type = NVL(v_sca_attendance_type,v_acai_attendance_type);
275
276 l_dummy_bool BOOLEAN;
277 l_message_name fnd_new_messages.message_name%TYPE;
278
279 BEGIN
280 v_ca_acai_adm_appl_number := NULL;
281 v_ca_acai_nominated_course_cd := NULL;
282 v_ca_acai_sequence_number := NULL;
283 v_ca_sca_course_cd := NULL;
284 v_ca_attendance_percentage := NULL;
285 IF p_ca_sequence_number IS NOT NULL THEN
286 -- Select details from IGS_RE_CANDIDATURE
287 OPEN c_ca;
288 FETCH c_ca INTO v_ca_acai_adm_appl_number,
289 v_ca_acai_nominated_course_cd,
290 v_ca_acai_sequence_number,
291 v_ca_sca_course_cd,
292 v_ca_attendance_percentage;
293 IF c_ca%NOTFOUND THEN
294 CLOSE c_ca;
295 RETURN NULL;
296 END IF;
297 CLOSE c_ca;
298 ELSE
299 IF p_sca_course_cd IS NULL AND
300 (p_acai_admission_appl_number IS NULL OR
301 p_acai_nominated_course_cd IS NULL OR
302 p_acai_sequence_number IS NULL) THEN
303 RETURN NULL;
304 END IF;
305 v_ca_acai_adm_appl_number := p_acai_admission_appl_number;
306 v_ca_acai_nominated_course_cd := p_acai_nominated_course_cd;
307 v_ca_acai_sequence_number := p_acai_sequence_number;
308 v_ca_sca_course_cd := p_sca_course_cd;
309 END IF;
310 -- Either select details from the admission application or the student
311 -- IGS_PS_COURSE attempt depending on the IGS_RE_CANDIDATURE fields which are set.
312 IF v_ca_sca_course_cd IS NOT NULL THEN
313 OPEN c_sca;
314 FETCH c_sca INTO v_sca_course_cd,
315 v_sca_version_number,
316 v_sca_attendance_type,
317 v_sca_commencement_dt,
318 v_sca_course_attempt_status;
319 IF c_sca%NOTFOUND THEN
320 CLOSE c_sca;
321 RETURN NULL;
322 END IF;
323 CLOSE c_sca;
324 ELSE
325 v_sca_course_cd := NULL;
326 v_sca_version_number := NULL;
327 v_sca_attendance_type := NULL;
328 v_sca_course_attempt_status := NULL;
329 END IF;
330 -- If the admission details have been passed then use these.
331 IF v_ca_acai_adm_appl_number IS NOT NULL THEN
332
333 OPEN c_acaiv;
334 FETCH c_acaiv INTO v_acai_course_cd,
335 v_acai_version_number,
336 v_acai_attendance_type;
337 IF c_acaiv%NOTFOUND THEN
338 CLOSE c_acaiv;
339 RETURN NULL;
340 END IF;
341 CLOSE c_acaiv;
342
343 ELSE
344 v_acai_course_cd := NULL;
345 v_acai_version_number := NULL;
346 v_acai_attendance_type := NULL;
347 END IF;
348
349 --Call routine to get the program length.
350 --v_crv_eftd will have a valid value if the EFTD calculated properly in the
351 --below function otherwise it will have a value zero
352 --passing p_acai_nominated_course_cd since sca_course_cd will be null when called
353 -- from admissions before prenerolment is triggered
354 v_crv_eftd := igs_ps_gen_002.crsp_get_crv_eftd( p_person_id,
355 nvl(p_sca_course_cd,p_acai_nominated_course_cd));
356
357 IF v_crv_eftd <= 0 THEN
358 -- Cannot calculate date if the IGS_PS_COURSE version has no EFTD
359 RETURN NULL;
360 END IF;
361 if v_crv_eftd is NULL then
362 v_crv_eftd := 0 ;
363 END IF;
364 OPEN c_att;
365 FETCH c_att INTO v_att_attendance_percentage;
366 CLOSE c_att;
367 -- Determine attendance percentage
368 IF NVL(p_attendance_percentage, v_ca_attendance_percentage) IS NULL THEN
369 v_attendance_percentage := v_att_attendance_percentage;
370 ELSE
371 v_attendance_percentage := NVL(
372 p_attendance_percentage,
373 v_ca_attendance_percentage);
374 END IF;
375 --added this condition since the research percentage can be set to 0
376 -- this will raise an unhandled exception since it will result in
377 -- a divide by 0
378
379 IF (v_attendance_percentage = 0 ) OR (v_att_attendance_percentage = 0) THEN
380 RETURN NULL;
381 END IF;
382 IF v_ca_sca_course_cd IS NOT NULL THEN
383 -- Call routine to determine the effective full time days already used
384 v_used_eftd_days := resp_clc_used_eftd(
385 p_person_id,
386 v_ca_sca_course_cd,
387 'Y',
388 p_ca_sequence_number,
389 v_attendance_percentage);
390 ELSE
391 -- Only enrolled students could have used EFTD
392 v_used_eftd_days := 0;
393 END IF;
394 v_eftd_remaining := v_crv_eftd - v_used_eftd_days;
395 IF v_eftd_remaining <= 0 THEN
396 -- No days left - return the current date
397 RETURN TRUNC(SYSDATE);
398 END IF;
399 -- Multiply the number of remaining EFTD by the students current attendance
400 -- to determine the maximum submission date
401 IF v_attendance_percentage <> 0 THEN
402 v_remaining_days := v_eftd_remaining / (v_attendance_percentage / 100);
403 ELSE
404 v_remaining_days := v_eftd_remaining / (v_att_attendance_percentage / 100);
405 END IF;
406 -- Determine if the students commencement date is a future date - if so,
407 -- the remaining days are added to their prospective commencement date
408 -- rather than the current date
409 IF p_commencement_dt IS NULL THEN
410 IF v_ca_acai_adm_appl_number IS NULL OR
411 NVL(v_sca_course_attempt_status, cst_unconfirm) <> cst_unconfirm THEN
412 v_commencement_dt := NVL(v_sca_commencement_dt, TRUNC(SYSDATE));
413 ELSE
414 v_commencement_dt := resp_get_ca_comm(
415 p_person_id,
416 v_ca_sca_course_cd,
417 v_ca_acai_adm_appl_number,
418 v_ca_acai_nominated_course_cd,
419 v_ca_acai_sequence_number);
420 END IF;
421 ELSE
422 v_commencement_dt := p_commencement_dt;
423 END IF;
424 IF v_commencement_dt >= TRUNC(SYSDATE) THEN
425 RETURN (v_commencement_dt + v_remaining_days);
426 ELSE
427 RETURN (TRUNC(SYSDATE) + NVL(v_remaining_days,0));
428 END IF;
429 EXCEPTION
430 WHEN OTHERS THEN
431 IF c_ca%ISOPEN THEN
432 CLOSE c_ca;
433 END IF;
434 IF c_sca%ISOPEN THEN
435 CLOSE c_sca;
436 END IF;
437
438 IF c_acaiv%ISOPEN THEN
439 CLOSE c_acaiv;
440 END IF;
441
442 IF c_att%ISOPEN THEN
443 CLOSE c_att;
444 END IF;
445 RAISE;
446 END;
447 END resp_clc_max_sbmsn;
448
449
450 FUNCTION RESP_CLC_MIN_SBMSN(
451 P_PERSON_ID IN NUMBER ,
452 p_ca_sequence_number IN NUMBER ,
453 p_sca_course_cd IN VARCHAR2 ,
454 p_acai_admission_appl_number IN NUMBER ,
455 p_acai_nominated_course_cd IN VARCHAR2 ,
456 p_acai_sequence_number IN NUMBER ,
457 p_attendance_percentage IN NUMBER ,
458 p_commencement_dt IN DATE )
459 RETURN DATE AS
460 /*----------------------------------------------------------------------------
461 || Created By :
462 || Created On :
463 || Purpose :
464 || Known limitations, enhancements or remarks :
465 || Change History :
466 || Who When What
467 || (reverse chronological order - newest change first)
468 || Nishikant 11DEC2002 The signature of the function call igs_ps_gen_002.crsp_get_crv_eftd got
469 || modified, which used to get the value for the variable v_crv_eftd
470 || vvutukur 19-Oct-2002 Enh#2608227.Added cursor cur_coo_id to fetch coo_id of course and modified
471 || call to igs_ps_gen_002.crsp_get_crv_eftd.
472 || svanukur 28-jul-2004 implemented an nvl check to pass p_acai_nominated_course_cd in the call to
473 || igs_ps_gen_002.crsp_get_crv_eftd bug 3487851
474 ----------------------------------------------------------------------------*/
475 gv_other_detail VARCHAR2(255);
476 BEGIN -- resp_clc_min_sbmsn
477 -- Calculate the minimum submission date of a student IGS_PS_COURSE attempt
478 -- IGS_RE_CANDIDATURE. This is calculated as the remaining EFTD (Effective
479 -- Full Time Days) remaining in the IGS_PS_COURSE multiplied by the minimum
480 -- submission percentage (stored against the students IGS_PS_COURSE version)
481 -- factored with the students current attendance percentage.
482 DECLARE
483 cst_unconfirm CONSTANT VARCHAR2(10) := 'UNCONFIRM';
484 v_ca_acai_adm_appl_number IGS_RE_CANDIDATURE.acai_admission_appl_number%TYPE;
485 v_ca_acai_nominated_course_cd IGS_RE_CANDIDATURE.acai_nominated_course_cd%TYPE;
486 v_ca_acai_sequence_number IGS_RE_CANDIDATURE.acai_sequence_number%TYPE;
487 v_ca_sca_course_cd IGS_RE_CANDIDATURE.sca_course_cd%TYPE;
488 v_ca_attendance_percentage IGS_RE_CANDIDATURE.attendance_percentage%TYPE;
489 v_candidature_exists_ind VARCHAR2(1);
490 v_sca_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE;
491 v_sca_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
492 v_sca_attendance_type IGS_EN_STDNT_PS_ATT.attendance_type%TYPE;
493 v_sca_course_attempt_status
494 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
495 v_sca_commencement_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
496 v_acai_course_cd IGS_PS_VER.course_cd%TYPE;
497 v_acai_version_number IGS_PS_VER.version_number%TYPE;
498 v_acai_attendance_type IGS_EN_ATD_TYPE.attendance_type%TYPE;
499 v_crv_min_sbmsn_percentage IGS_PS_VER.min_sbmsn_percentage%TYPE;
500 v_attendance_percentage IGS_EN_ATD_TYPE.research_percentage%TYPE;
501 v_att_attendance_percentage IGS_EN_ATD_TYPE.research_percentage%TYPE;
502 v_crv_eftd NUMBER;
503 v_used_eftd_days NUMBER;
504 v_minimum_eftd NUMBER;
505 v_eftd_remaining NUMBER;
506 v_remaining_days NUMBER;
507 v_commencement_dt DATE;
508 CURSOR c_ca IS
509 SELECT ca.acai_admission_appl_number,
510 ca.acai_nominated_course_cd,
511 ca.acai_sequence_number,
512 ca.sca_course_cd,
513 ca.attendance_percentage
514 FROM IGS_RE_CANDIDATURE ca
515 WHERE ca.person_id = p_person_id AND
516 ca.sequence_number = p_ca_sequence_number;
517 CURSOR c_sca IS
518 SELECT sca.course_cd,
519 sca.version_number,
520 sca.attendance_type,
521 sca.commencement_dt,
522 sca.course_attempt_status
523 FROM IGS_EN_STDNT_PS_ATT sca
524 WHERE sca.person_id = p_person_id AND
525 sca.course_cd = v_ca_sca_course_cd;
526
527 CURSOR c_acaiv IS
528 SELECT acai.course_cd,
529 acai.crv_version_number,
530 acai.attendance_type
531 FROM
532 IGS_AD_PS_APPL_INST acai,
533 IGS_AD_APPL aa,
534 IGS_CA_INST ci,
535 IGS_AD_PS_APPL aca,
536 IGS_PS_VER crv
537 WHERE acai.person_id = p_person_id AND
538 acai.admission_appl_number = v_ca_acai_adm_appl_number AND
539 acai.nominated_course_cd = v_ca_acai_nominated_course_cd AND
540 acai.sequence_number = v_ca_acai_sequence_number AND
541 aa.person_id = acai.person_id AND
542 aa.admission_appl_number = acai.admission_appl_number AND
543 ci.cal_type (+) = acai.deferred_adm_cal_type AND
544 ci.sequence_number (+) = acai.deferred_adm_ci_sequence_num AND
545 aca.person_id = acai.person_id AND
546 aca.admission_appl_number = acai.admission_appl_number AND
547 aca.nominated_course_cd = acai.nominated_course_cd AND
548 crv.course_cd = acai.course_cd AND
549 crv.version_number = acai.crv_version_number;
550
551
552 CURSOR c_crv IS
553 SELECT crv.min_sbmsn_percentage
554 FROM IGS_PS_VER crv
555 WHERE crv.course_cd = NVL(
556 v_sca_course_cd,
557 v_acai_course_cd) AND
558 crv.version_number = NVL(
559 v_sca_version_number,
560 v_acai_version_number);
561 CURSOR c_att IS
562 SELECT att.research_percentage
563 FROM IGS_EN_ATD_TYPE att
564 WHERE att.attendance_type = NVL(
565 v_sca_attendance_type,
566 v_acai_attendance_type);
567
568 l_dummy_bool BOOLEAN;
569 l_message_name fnd_new_messages.message_name%TYPE;
570
571 BEGIN
572 v_ca_acai_adm_appl_number := NULL;
573 v_ca_acai_nominated_course_cd := NULL;
574 v_ca_acai_sequence_number := NULL;
575 v_ca_sca_course_cd := NULL;
576 v_ca_attendance_percentage := NULL;
577 IF p_ca_sequence_number IS NOT NULL THEN
578 -- Select details from IGS_RE_CANDIDATURE
579 OPEN c_ca;
580 FETCH c_ca INTO v_ca_acai_adm_appl_number,
581 v_ca_acai_nominated_course_cd,
582 v_ca_acai_sequence_number,
583 v_ca_sca_course_cd,
584 v_ca_attendance_percentage;
585 IF c_ca%NOTFOUND THEN
586 CLOSE c_ca;
587 RETURN NULL;
588 END IF;
589 CLOSE c_ca;
590 ELSE
591 IF p_sca_course_cd IS NULL AND
592 (p_acai_admission_appl_number IS NULL OR
593 p_acai_nominated_course_cd IS NULL OR
594 p_acai_sequence_number IS NULL) THEN
595 RETURN NULL;
596 END IF;
597 v_ca_acai_adm_appl_number := p_acai_admission_appl_number;
598 v_ca_acai_nominated_course_cd := p_acai_nominated_course_cd;
599 v_ca_acai_sequence_number := p_acai_sequence_number;
600 v_ca_sca_course_cd := p_sca_course_cd;
601 END IF;
602 -- Either select details from the admission application or the student
603 -- IGS_PS_COURSE attempt depending on the IGS_RE_CANDIDATURE fields which are set.
604 IF v_ca_sca_course_cd IS NOT NULL THEN
605 OPEN c_sca;
606 FETCH c_sca INTO v_sca_course_cd,
607 v_sca_version_number,
608 v_sca_attendance_type,
609 v_sca_commencement_dt,
610 v_sca_course_attempt_status;
611 IF c_sca%NOTFOUND THEN
612 CLOSE c_sca;
613 RETURN NULL;
614 END IF;
615 CLOSE c_sca;
616 ELSE
617 v_sca_course_cd := NULL;
618 v_sca_version_number := NULL;
619 v_sca_attendance_type := NULL;
620 v_sca_course_attempt_status := NULL;
621 END IF;
622 -- If the admission details have been passed then use these.
623 IF v_ca_acai_adm_appl_number IS NOT NULL THEN
624
625 OPEN c_acaiv;
626 FETCH c_acaiv INTO v_acai_course_cd,
627 v_acai_version_number,
628 v_acai_attendance_type;
629 IF c_acaiv%NOTFOUND THEN
630 CLOSE c_acaiv;
631 RETURN NULL;
632 END IF;
633 CLOSE c_acaiv;
634 ELSE
635 v_acai_course_cd := NULL;
636 v_acai_version_number := NULL;
637 v_acai_attendance_type := NULL;
638 END IF;
639 -- Select details from the IGS_PS_COURSE version table
640 OPEN c_crv;
641 FETCH c_crv INTO v_crv_min_sbmsn_percentage;
642 CLOSE c_crv;
643
644 -- Call routine to get the program length.
645 --v_crv_eftd will have a valid value if the EFTD calculated properly in the
646 --below function otherwise it will have a value zero
647 --passing p_acai_nominated_course_cd since sca_course_cd will be null when called
648 -- from admissions before prenerolment is triggered.
649 v_crv_eftd := igs_ps_gen_002.crsp_get_crv_eftd( p_person_id,
650 nvl(p_sca_course_cd,p_acai_nominated_course_cd));
651
652 IF v_crv_eftd <= 0 THEN
653 -- Cannot calculate date if the IGS_PS_COURSE version has no EFTD
654 RETURN NULL;
655 END IF;
656 IF v_crv_eftd is NULL then
657 v_crv_eftd := 0;
658 END IF;
659 OPEN c_att;
660 FETCH c_att INTO v_att_attendance_percentage;
661 CLOSE c_att;
662 -- Determine attendance percentage
663 IF NVL(p_attendance_percentage, v_ca_attendance_percentage) IS NULL THEN
664 v_attendance_percentage := v_att_attendance_percentage;
665 ELSE
666 v_attendance_percentage := NVL(
667 p_attendance_percentage,
668 v_ca_attendance_percentage);
669 END IF;
670
671 --added this condition since the research percentage can be set to 0
672 -- this will raise an unhandled exception since it will result in
673 -- a divide by 0
674 IF (v_attendance_percentage = 0 ) OR (v_att_attendance_percentage = 0) THEN
675 RETURN NULL;
676 END IF;
677
678 IF v_ca_sca_course_cd IS NOT NULL THEN
679 -- Call routine to determine the effective full time days already used
680 v_used_eftd_days := resp_clc_used_eftd(
681 p_person_id,
682 v_ca_sca_course_cd,
683 'Y',
684 p_ca_sequence_number,
685 v_attendance_percentage);
686 ELSE
687 -- Only enrolled students could have used days
688 v_used_eftd_days := 0;
689 END IF;
690 v_minimum_eftd := v_crv_eftd * (v_crv_min_sbmsn_percentage / 100);
691 IF v_used_eftd_days > v_minimum_eftd THEN
692 -- Student has already consumed more days than the IGS_PS_COURSE permitted
693 -- * return the current date as the minimum submission date
694 RETURN TRUNC(SYSDATE);
695 END IF;
696 v_eftd_remaining := v_minimum_eftd - v_used_eftd_days;
697 -- Multiply the number of remaining EFTD by the students current attendance
698 -- to determine the maximum submission date
699 IF v_attendance_percentage <> 0 THEN
700 v_remaining_days := v_eftd_remaining / (v_attendance_percentage / 100);
701 ELSE
702 v_remaining_days := v_eftd_remaining / (v_att_attendance_percentage / 100);
703 END IF;
704 -- Determine if the students commencement date is a future date - if so,
705 -- the remaining days are added to their prospective commencement date
706 -- rather than the current date
707 IF p_commencement_dt IS NULL THEN
708 IF v_ca_acai_adm_appl_number IS NULL OR
709 NVL(v_sca_course_attempt_status, cst_unconfirm) <> cst_unconfirm THEN
710 v_commencement_dt := NVL(v_sca_commencement_dt, TRUNC(SYSDATE));
711 ELSE
712 v_commencement_dt := resp_get_ca_comm(
713 p_person_id,
714 v_ca_sca_course_cd,
715 v_ca_acai_adm_appl_number,
716 v_ca_acai_nominated_course_cd,
717 v_ca_acai_sequence_number);
718 END IF;
719 ELSE
720 v_commencement_dt := p_commencement_dt;
721 END IF;
722 IF v_commencement_dt >= TRUNC(SYSDATE) THEN
723 RETURN (v_commencement_dt + v_remaining_days);
724 ELSE
725 RETURN (TRUNC(SYSDATE) + NVL(v_remaining_days,0));
726 END IF;
727 EXCEPTION
728 WHEN OTHERS THEN
729 IF c_ca%ISOPEN THEN
730 CLOSE c_ca;
731 END IF;
732 IF c_sca%ISOPEN THEN
733 CLOSE c_sca;
734 END IF;
735
736 IF c_acaiv%ISOPEN THEN
737 CLOSE c_acaiv;
738 END IF;
739
740 IF c_crv%ISOPEN THEN
741 CLOSE c_crv;
742 END IF;
743 IF c_att%ISOPEN THEN
744 CLOSE c_att;
745 END IF;
746 RAISE;
747 END;
748 END resp_clc_min_sbmsn;
749
750
751 FUNCTION RESP_CLC_SUA_EFTSU(
752 p_person_id IN NUMBER ,
753 p_course_cd IN VARCHAR2 ,
754 p_unit_cd IN VARCHAR2 ,
755 p_cal_type IN VARCHAR2 ,
756 p_ci_sequence_number IN NUMBER ,
757 p_load_cal_type IN VARCHAR2 ,
758 p_load_ci_sequence_number IN NUMBER ,
759 p_truncate_ind IN VARCHAR2,
760 p_uoo_id igs_en_su_attempt.uoo_id%TYPE )
761 RETURN NUMBER AS
762 /*
763 | Who When What
764 | knaraset 09-May-03 modified function to add parameter uoo_id which is used in cursor c_sua, as part of MUS build bug 2829262
765 |
766 |
767 */
768 gv_other_detail VARCHAR2(255);
769 BEGIN -- resp_clc_sua_eftsu
770 -- Calculate the EFTSU figure for a research load IGS_PS_UNIT attempt. This routine
771 -- will,
772 -- . Determine the base EFTSU figure for the SUA as either the override value
773 -- or the value from the research EFTD.
774 -- . Calculates the coursework load within the same teaching period and
775 -- adjusts the research IGS_PS_UNIT accordingly.
776 -- . Truncates the EFTSU (if the parameter requires it).
777 -- Assumptions (highlighted by the calendar quality check):
778 -- ? The EFTSU within research teaching periods only contributes to a single
779 -- load calendar (ie. is never split across multiple load calendars).
780 -- ? A student is only ever enrolled in a single research IGS_PS_UNIT within a load
781 -- calendar (ie. multiple units are not taken into consideration when
782 -- comparing the research load with the coursework load).
783 DECLARE
784 v_census_dt IGS_CA_DA_INST.absolute_val%TYPE;
785 v_ca_person_id IGS_RE_CANDIDATURE.person_id%TYPE;
786 v_ca_sequence_number IGS_RE_CANDIDATURE.sequence_number%TYPE;
787 v_sua_override_eftsu IGS_EN_SU_ATTEMPT.override_eftsu%TYPE;
788 v_sua_cal_type IGS_EN_SU_ATTEMPT.cal_type%TYPE;
789 v_sua_ci_sequence_number IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE;
790 v_sua_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE;
791 v_sua_version_number IGS_EN_SU_ATTEMPT.version_number%TYPE;
792 v_acad_cal_type IGS_CA_INST.cal_type%TYPE;
793 v_acad_ci_sequence_number IGS_CA_INST.sequence_number%TYPE;
794 v_acad_ci_start_dt IGS_CA_INST.start_dt%TYPE;
795 v_acad_ci_end_dt IGS_CA_INST.end_dt%TYPE;
796 v_message_name VARCHAR2(30);
797 v_credit_points NUMBER;
798 v_load_eftsu NUMBER;
799 v_sua_eftd NUMBER;
800 v_cal_type_eftd NUMBER;
801 v_sua_eftsu NUMBER;
802 v_coursework_eftsu NUMBER;
803 v_dummy_string VARCHAR2(10);
804 v_final_eftsu NUMBER;
805 v_census_att_perc NUMBER;
806 CURSOR c_ca IS
807 SELECT ca.person_id,
808 ca.sequence_number
809 FROM IGS_RE_CANDIDATURE ca
810 WHERE ca.person_id = p_person_id AND
811 NVL(ca.sca_course_cd, 'NULL') = NVL(p_course_cd, 'NULL');
812 CURSOR c_sua(cp_person_id igs_en_su_attempt.person_id%TYPE,
813 cp_course_cd igs_en_su_attempt.course_cd%TYPE,
814 cp_uoo_id igs_en_su_attempt.uoo_id%TYPE) IS
815 SELECT sua.override_eftsu,
816 sua.cal_type,
817 sua.ci_sequence_number,
818 sua.uoo_id,
819 sua.version_number
820 FROM IGS_EN_SU_ATTEMPT sua,
821 IGS_EN_STDNT_PS_ATT sca,
822 IGS_PS_UNIT_VER uv
823 WHERE sua.person_id = cp_person_id AND
824 sua.course_cd = cp_course_cd AND
825 sua.uoo_id = cp_uoo_id AND
826 sca.person_id = sua.person_id AND
827 sca.course_cd = sua.course_cd AND
828 uv.unit_cd = sua.unit_cd AND
829 uv.version_number = sua.version_number AND
830 uv.research_unit_ind = 'Y';
831 CURSOR c_sgcc (
832 cp_cal_type IGS_EN_SU_ATTEMPT.cal_type%TYPE,
833 cp_ci_sequence_number IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE)
834 IS
835 SELECT NVL(
836 dai.absolute_val,
837 IGS_CA_GEN_001.calp_get_alias_val(
838 dai.dt_alias,
839 dai.sequence_number,
840 dai.cal_type,
841 dai.ci_sequence_number)) v_census_dt
842 FROM IGS_GE_S_GEN_CAL_CON sgcc,
843 IGS_CA_DA_INST dai
844 WHERE sgcc.s_control_num = 1 AND
845 dai.cal_type = cp_cal_type AND
846 dai.ci_sequence_number = cp_ci_sequence_number AND
847 dai.dt_alias = sgcc.census_dt_alias
848 ORDER BY 1;
849 BEGIN
850 -- 1. Select detail from research IGS_RE_CANDIDATURE
851 OPEN c_ca;
852 FETCH c_ca INTO
853 v_ca_person_id,
854 v_ca_sequence_number;
855 IF c_ca%NOTFOUND THEN
856 -- No research IGS_PS_UNIT load for non-research students.
857 CLOSE c_ca;
858 RETURN NULL;
859 END IF;
860 CLOSE c_ca;
861 -- 2. Select detail from IGS_PS_UNIT attempt and IGS_PS_UNIT version tables
862 OPEN c_sua(p_person_id,p_course_cd,p_uoo_id);
863 FETCH c_sua INTO
864 v_sua_override_eftsu,
865 v_sua_cal_type,
866 v_sua_ci_sequence_number,
867 v_sua_uoo_id,
868 v_sua_version_number;
869 IF c_sua%NOTFOUND THEN
870 -- Only existing IGS_PS_UNIT attempts in research 'Load' units can have EFTSU
871 -- calculated through this method ; return zero.
872 CLOSE c_sua;
873 RETURN 0;
874 END IF;
875 CLOSE c_sua;
876 -- 3. Calculate the EFTSU figure for the load calendar instance
877 v_dummy_string := IGS_EN_GEN_002.enrp_get_acad_alt_cd (
878 v_sua_cal_type,
879 v_sua_ci_sequence_number,
880 v_acad_cal_type,
881 v_acad_ci_sequence_number,
882 v_acad_ci_start_dt,
883 v_acad_ci_end_dt,
884 v_message_name);
885 v_load_eftsu := resp_clc_load_eftsu (
886 v_acad_cal_type,
887 v_acad_ci_sequence_number,
888 p_load_cal_type,
889 p_load_ci_sequence_number);
890 IF v_load_eftsu = 0 THEN
891 -- No load EFTSU will lead to 0 SUA EFTSU, so exit at this point
892 -- to enable standard EFTSU calculation ***JES
893 RETURN NULL;
894 END IF;
895 -- 4. Retrieve the census date from the teaching period.
896 -- Because I am ordering by 1 desc, if there are multiple records the
897 -- earliest census date will be selected automatically.
898 OPEN c_sgcc (
899 v_sua_cal_type,
900 v_sua_ci_sequence_number);
901 FETCH c_sgcc INTO v_census_dt;
902 IF c_sgcc%NOTFOUND THEN
903 -- No census date - invalid calendar set-up - return zero
904 CLOSE c_sgcc;
905 RETURN 0;
906 END IF;
907 CLOSE c_sgcc;
908 IF v_sua_override_eftsu IS NULL THEN
909 -- 5. Call routine to determine the EFTD figure for the teaching period
910 -- within the nominated load calendar instance.
911 v_sua_eftd := IGS_RE_GEN_002.resp_get_sua_eftd (
912 p_person_id,
913 p_course_cd,
914 p_unit_cd,
915 v_sua_version_number,
916 p_cal_type,
917 p_ci_sequence_number,
918 v_census_dt,
919 p_load_cal_type,
920 p_load_ci_sequence_number,
921 v_cal_type_eftd);
922 IF v_cal_type_eftd = 0 THEN
923 RETURN NULL;
924 END IF;
925 IF v_sua_eftd = 0 THEN
926 RETURN 0;
927 END IF;
928 -- 6. Calculate the EFTSU figure for the IGS_PS_UNIT attempt; this is as a
929 -- proportion of the SUA EFTD against the teaching period EFTD multiplied
930 -- by the load calendar EFTSU.
931 v_sua_eftsu := (v_sua_eftd / v_cal_type_eftd )
932 * v_load_eftsu;
933 ELSE -- override EFTSU has been specified.
934 IF v_sua_override_eftsu = 0 THEN
935 -- Override of zero - no further processing.
936 RETURN 0;
937 ELSE
938 v_sua_eftsu := v_sua_override_eftsu;
939 END IF;
940 END IF;
941 IF v_sua_override_eftsu IS NULL THEN
942 -- 7. Call routine to determine the total of the coursework EFTSU in the load
943 -- calendar instance. First get the academic parent of the parameter load
944 -- calendar.
945 v_dummy_string := IGS_EN_GEN_002.enrp_get_acad_alt_cd(
946 p_load_cal_type,
947 p_load_ci_sequence_number,
948 v_acad_cal_type,
949 v_acad_ci_sequence_number,
950 v_acad_ci_start_dt,
951 v_acad_ci_end_dt,
952 v_message_name);
953 v_coursework_eftsu := IGS_EN_PRC_LOAD.enrp_clc_eftsu_total(
954 p_person_id,
955 p_course_cd,
956 v_acad_cal_type,
957 v_acad_ci_sequence_number,
958 p_load_cal_type,
959 p_load_ci_sequence_number,
960 p_truncate_ind,
961 'N', -- Don't include research units
962 NULL,
963 NULL,
964 v_credit_points);
965 -- Calculate the final figure factoring in the coursework component.
966 IF v_coursework_eftsu = 0 THEN
967 v_final_eftsu := v_sua_eftsu;
968 ELSIF v_coursework_eftsu >= v_sua_eftsu THEN
969 RETURN 0;
970 ELSE
971 v_final_eftsu := v_sua_eftsu - v_coursework_eftsu;
972 END IF;
973 END IF;
974 -- 9. If truncation is required then call the appropriate routine.
975 IF p_truncate_ind = 'Y' THEN
976 RETURN resp_clc_eftsu_trunc (
977 v_ca_person_id,
978 v_ca_sequence_number,
979 p_unit_cd,
980 v_sua_version_number,
981 v_sua_uoo_id,
982 v_census_dt,
983 v_final_eftsu);
984 ELSE
985 RETURN v_final_eftsu;
986 END IF;
987 EXCEPTION
988 WHEN OTHERS THEN
989 IF c_ca %ISOPEN THEN
990 CLOSE c_ca;
991 END IF;
992 IF c_sua %ISOPEN THEN
993 CLOSE c_sua;
994 END IF;
995 IF c_sgcc %ISOPEN THEN
996 CLOSE c_sgcc;
997 END IF;
998 RAISE;
999 END;
1000
1001 END resp_clc_sua_eftsu;
1002
1003
1004 FUNCTION RESP_CLC_USED_EFTD(
1005 p_person_id IN NUMBER ,
1006 p_course_cd IN VARCHAR2 ,
1007 p_candidature_identified_ind IN VARCHAR2,
1008 p_ca_sequence_number IN NUMBER ,
1009 p_attendance_percentage IN NUMBER )
1010 RETURN NUMBER AS
1011 gv_other_detail VARCHAR2(255);
1012 BEGIN -- resp_clc_used_eftd
1013 -- Calculates the effective full time days used
1014 -- by a student within a research IGS_PS_COURSE.
1015 -------------------------------------------------------------------------------------------
1016 --Change History:
1017 --Who When What
1018 -- svanukur 01-APR-2004 Changed cursor c_cah to consider all history records for calcultation
1019 -- of used EFTD instead of the using only those history records that
1020 -- start after the commencement date. bug 3544986
1021 -- rnirwani 13-Sep-2004 changed cursor c_sci to not consider logically deleted records and
1022 -- also to avoid un-approved intermission records. Bug# 3885804
1023 -------------------------------------------------------------------------------------------
1024 DECLARE
1025 cst_unconfirm CONSTANT
1026 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'UNCONFIRM';
1027 v_ca_sequence_number IGS_RE_CANDIDATURE.sequence_number%TYPE;
1028 v_ca_attendance_percentage IGS_RE_CANDIDATURE.attendance_percentage%TYPE;
1029 v_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
1030 v_commencement_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
1031 v_attendance_type IGS_EN_STDNT_PS_ATT.attendance_type%TYPE;
1032 v_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
1033 v_last_hist_end_dt IGS_RE_CDT_ATT_HIST.hist_end_dt%TYPE;
1034 v_last_attendance_percentage IGS_RE_CDT_ATT_HIST.attendance_percentage%TYPE;
1035 v_cah_hist_start_dt IGS_RE_CDT_ATT_HIST.hist_start_dt%TYPE;
1036 v_cah_hist_end_dt IGS_RE_CDT_ATT_HIST.hist_end_dt%TYPE;
1037 v_cah_attendance_percentage IGS_RE_CDT_ATT_HIST.attendance_percentage%TYPE;
1038 v_research_percentage IGS_EN_ATD_TYPE.research_percentage%TYPE;
1039 v_count_intrmsn_in_time_ind IGS_PS_VER.count_intrmsn_in_time_ind%TYPE;
1040 v_start_dt IGS_RE_CDT_ATT_HIST.hist_start_dt%TYPE;
1041 v_last_end_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
1042 v_cah_last_end_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
1043 v_attendance_percentage IGS_RE_CANDIDATURE.attendance_percentage%TYPE;
1044 v_today_dt DATE;
1045 v_EFTD_total NUMBER;
1046 v_next_rec_flg BOOLEAN;
1047 v_sci_end_dt IGS_EN_STDNT_PS_INTM.end_dt%TYPE;
1048 v_end_dt IGS_EN_STDNT_PS_INTM.end_dt%TYPE;
1049 CURSOR c_ca IS
1050 SELECT ca.sequence_number,
1051 ca.attendance_percentage
1052 FROM IGS_RE_CANDIDATURE ca
1053 WHERE ca.person_id = p_person_id AND
1054 (ca.sca_course_cd IS NULL OR
1055 ca.sca_course_cd = p_course_cd);
1056 CURSOR c_sca IS
1057 SELECT sca.course_attempt_status,
1058 sca.commencement_dt,
1059 sca.attendance_type,
1060 sca.version_number
1061 FROM IGS_EN_STDNT_PS_ATT sca
1062 WHERE sca.person_id = p_person_id AND
1063 sca.course_cd = p_course_cd;
1064 CURSOR c_cah (
1065 cp_sequence_number IGS_RE_CANDIDATURE.sequence_number%TYPE
1066 ) IS
1067 SELECT cah.hist_start_dt,
1068 cah.hist_end_dt,
1069 cah.attendance_percentage
1070 FROM IGS_RE_CDT_ATT_HIST cah
1071 WHERE cah.person_id = p_person_id AND
1072 cah.ca_sequence_number = cp_sequence_number
1073 ORDER BY cah.hist_start_dt ASC;
1074
1075
1076 CURSOR c_att (
1077 cp_attendance_type IGS_EN_STDNT_PS_ATT.attendance_type%TYPE) IS
1078 SELECT att.research_percentage
1079 FROM IGS_EN_ATD_TYPE att
1080 WHERE att.attendance_type = cp_attendance_type AND
1081 att.closed_ind = 'N';
1082 CURSOR c_crv (
1083 cp_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE) IS
1084 SELECT crv.count_intrmsn_in_time_ind
1085 FROM IGS_PS_VER crv
1086 WHERE crv.course_cd = p_course_cd AND
1087 crv.version_number = cp_version_number;
1088 CURSOR c_sci IS
1089 SELECT sci.start_dt,
1090 sci.end_dt
1091 FROM IGS_EN_STDNT_PS_INTM sci,
1092 IGS_EN_INTM_TYPES eit
1093 WHERE sci.person_id = p_person_id AND
1094 sci.course_cd = p_course_cd AND
1095 sci.approved = eit.appr_reqd_ind AND
1096 eit.intermission_type = sci.intermission_type AND
1097 sci.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY')
1098 ORDER BY sci.start_dt;
1099 CURSOR c_cah2 (
1100 cp_sequence_number IGS_RE_CANDIDATURE.sequence_number%TYPE,
1101 cp_sci_end_dt IGS_EN_STDNT_PS_INTM.end_dt%TYPE,
1102 cp_sci_start_dt IGS_EN_STDNT_PS_INTM.start_dt%TYPE) IS
1103 SELECT cah.hist_start_dt,
1104 cah.hist_end_dt,
1105 cah.attendance_percentage
1106 FROM IGS_RE_CDT_ATT_HIST cah
1107 WHERE cah.person_id = p_person_id AND
1108 cah.ca_sequence_number = cp_sequence_number AND
1109 cah.hist_start_dt <= cp_sci_end_dt AND
1110 cah.hist_end_dt >= cp_sci_start_dt
1111 ORDER BY cah.hist_start_dt ASC;
1112 BEGIN
1113 -- 1. Determine whether IGS_PE_PERSON has a research
1114 -- IGS_RE_CANDIDATURE for the IGS_PS_COURSE attempt.
1115 IF (p_candidature_identified_ind = 'Y') THEN
1116 v_attendance_percentage := p_attendance_percentage;
1117 v_ca_sequence_number := p_ca_sequence_number;
1118 ELSE
1119 OPEN c_ca;
1120 FETCH c_ca INTO v_ca_sequence_number,
1121 v_ca_attendance_percentage;
1122 IF (c_ca%NOTFOUND) THEN
1123 -- Not a research student in the IGS_PS_COURSE - return zero
1124 CLOSE c_ca;
1125 RETURN 0;
1126 END IF;
1127 CLOSE c_ca;
1128 v_attendance_percentage := v_ca_attendance_percentage;
1129 END IF;
1130 -- 2. Select details from student IGS_PS_COURSE attempt record
1131 -- to be used further in routine.
1132 OPEN c_sca;
1133 FETCH c_sca INTO v_course_attempt_status,
1134 v_commencement_dt,
1135 v_attendance_type,
1136 v_version_number;
1137 CLOSE c_sca;
1138 IF (v_course_attempt_status = cst_unconfirm) THEN
1139 -- Unconfirmed IGS_PS_COURSE attempts can't have used any days
1140 RETURN 0;
1141 END IF;
1142 v_today_dt := TRUNC(SYSDATE);
1143 -- 3. Loop though IGS_RE_CANDIDATURE history totalling effective attendance periods
1144 v_EFTD_total := 0;
1145
1146 FOR v_cah_rec IN c_cah( v_ca_sequence_number) LOOP
1147 v_start_dt := v_cah_rec.hist_start_dt;
1148 -- If the history starts after the commencement date then assume
1149 -- the first record applies from the commencement date onwards.
1150 IF (c_cah%ROWCOUNT = 1) THEN
1151 IF (v_cah_rec.hist_start_dt > v_commencement_dt) THEN
1152 v_start_dt := v_commencement_dt;
1153 END IF;
1154 ELSE
1155 IF ((v_cah_rec.hist_start_dt - v_last_hist_end_dt) > 1) THEN
1156
1157 v_EFTD_total := v_EFTD_total +
1158 ((v_cah_rec.hist_start_dt - v_last_hist_end_dt - 1) *
1159 (v_last_attendance_percentage / 100));
1160 END IF;
1161 END IF;
1162 -- Add the EFTD figure for the effective period of time
1163 v_EFTD_total := v_EFTD_total +
1164 ((v_cah_rec.hist_end_dt - v_start_dt + 1 ) *
1165 (v_cah_rec.attendance_percentage / 100));
1166 v_last_hist_end_dt := v_cah_rec.hist_end_dt;
1167 v_last_attendance_percentage := v_cah_rec.attendance_percentage;
1168 END LOOP;
1169 OPEN c_cah(
1170 v_ca_sequence_number
1171 );
1172 FETCH c_cah INTO v_cah_hist_start_dt,
1173 v_cah_hist_end_dt,
1174 v_cah_attendance_percentage;
1175 IF (c_cah%NOTFOUND) THEN
1176 v_last_end_dt := v_commencement_dt - 1 ; -- Allow for inclusive commence dt
1177 ELSE
1178 v_last_end_dt := v_last_hist_end_dt;
1179 END IF;
1180 CLOSE c_cah;
1181 IF (v_last_end_dt < v_today_dt - 1) THEN
1182 -- If the current (IGS_RE_CANDIDATURE) attendance percentage is set then use it,
1183 -- otherwise load the default from the attendance type
1184 IF (v_attendance_percentage IS NULL) THEN
1185 OPEN c_att(v_attendance_type);
1186 FETCH c_att INTO v_research_percentage;
1187 CLOSE c_att;
1188 v_attendance_percentage := v_research_percentage;
1189 END IF;
1190 v_EFTD_total := v_EFTD_total +
1191 ((v_today_dt - v_last_end_dt - 1) *
1192 (v_attendance_percentage / 100));
1193 END IF;
1194 -- Retain last end date for intermission processing
1195 v_cah_last_end_dt := v_last_end_dt;
1196 -- 4. If intermission is not counted in time, Subtract any
1197 -- applicable periods of intermission from the total.
1198 OPEN c_crv(v_version_number);
1199 FETCH c_crv INTO v_count_intrmsn_in_time_ind;
1200 CLOSE c_crv;
1201 IF (v_count_intrmsn_in_time_ind = 'N') THEN
1202 FOR v_sci_rec IN c_sci LOOP
1203 v_next_rec_flg := FALSE;
1204 -- If period of intermission doesn't overlap then exclude.
1205 --i.e if intermission is in future then exclude
1206 IF v_sci_rec.start_dt >= v_today_dt THEN
1207 -- Set flag to loop to next record
1208 v_next_rec_flg := TRUE;
1209 END IF;
1210 IF (v_next_rec_flg = FALSE) THEN
1211
1212
1213 -- added the end day to include intermission end date.
1214 v_sci_end_dt := (v_sci_rec.end_dt );
1215 -- Loop though periods of attendance history
1216 -- and remove appropriate EFTD figures.
1217 v_last_hist_end_dt := NULL;
1218
1219 --EFTD calculation has been changed as part of the bug 3453123
1220 --All history records are to be taken into account even if they are
1221 --prior to the commencement date of the student.
1222
1223 FOR v_cah2_rec IN c_cah2(
1224 v_ca_sequence_number,
1225 v_sci_end_dt,
1226 v_sci_rec.start_dt) LOOP
1227 -- If first history doesn't go back as far as the intermission start
1228 -- then assume it applies during that period (as no other history exists)
1229 v_start_dt := v_cah2_rec.hist_start_dt;
1230 IF (c_cah2%ROWCOUNT = 1) THEN
1231 IF (v_cah2_rec.hist_start_dt <v_sci_rec.start_dt) THEN
1232 v_start_dt := v_sci_rec.start_dt;
1233 END IF;
1234 ELSE
1235 IF ((v_cah2_rec.hist_start_dt - v_last_hist_end_dt) > 1) THEN
1236 -- Use the same details as the previous
1237 -- history to fill any gap in history
1238 v_EFTD_total := v_EFTD_total +
1239 ((v_cah2_rec.hist_start_dt -
1240 v_last_hist_end_dt - 1) *
1241 (v_last_attendance_percentage / 100));
1242 END IF;
1243 END IF;
1244 v_end_dt := LEAST(
1245 v_sci_end_dt,
1246 v_cah2_rec.hist_end_dt);
1247 -- Remove EFTD figure from total
1248 v_EFTD_total := v_EFTD_total -
1249 ((v_end_dt - v_start_dt + 1) *
1250 (v_cah2_rec.attendance_percentage / 100));
1251
1252 v_last_hist_end_dt := v_cah2_rec.hist_end_dt;
1253 v_last_attendance_percentage := v_cah2_rec.attendance_percentage;
1254 END LOOP;
1255 -- Determine intermission current attendance
1256 IF v_sci_end_dt > v_cah_last_end_dt THEN
1257 IF v_last_hist_end_dt IS NULL THEN
1258 v_last_end_dt := v_sci_rec.start_dt - 1;
1259 ELSE
1260 v_last_end_dt := v_last_hist_end_dt;
1261 END IF;
1262 -- If the current (IGS_RE_CANDIDATURE) attendance percentage is set then
1263 -- use it, otherwise load the default from the attendance type
1264 IF (v_attendance_percentage IS NULL) THEN
1265 OPEN c_att(v_attendance_type);
1266 FETCH c_att INTO v_research_percentage;
1267 CLOSE c_att;
1268 v_attendance_percentage := v_research_percentage;
1269 END IF;
1270 v_sci_end_dt := LEAST(
1271 v_today_dt - 1,
1272 v_sci_end_dt);
1273 -- Subtract the last portion between the last history and the current date
1274 v_EFTD_total := v_EFTD_total -
1275 ((v_sci_end_dt - v_last_end_dt) *
1276 (v_attendance_percentage / 100));
1277
1278 END IF;
1279 END IF;
1280 END LOOP;
1281 END IF;
1282 RETURN v_EFTD_total;
1283 EXCEPTION
1284 WHEN OTHERS THEN
1285 IF (c_ca%ISOPEN) THEN
1286 CLOSE c_ca;
1287 END IF;
1288 IF (c_sca%ISOPEN) THEN
1289 CLOSE c_sca;
1290 END IF;
1291 IF (c_cah%ISOPEN) THEN
1292 CLOSE c_cah;
1293 END IF;
1294 IF (c_att%ISOPEN) THEN
1295 CLOSE c_att;
1296 END IF;
1297 IF (c_crv%ISOPEN) THEN
1298 CLOSE c_crv;
1299 END IF;
1300 IF (c_sci%ISOPEN) THEN
1301 CLOSE c_sci;
1302 END IF;
1303 IF (c_cah2%ISOPEN) THEN
1304 CLOSE c_cah2;
1305 END IF;
1306 RAISE;
1307 END;
1308 END resp_clc_used_eftd;
1309
1310
1311 FUNCTION RESP_GET_CA_ATT(
1312 p_person_id IN NUMBER ,
1313 p_course_cd IN VARCHAR2 ,
1314 p_effective_dt IN DATE ,
1315 p_ca_sequence_number IN NUMBER ,
1316 p_attendance_type IN VARCHAR2 ,
1317 p_attendance_percentage IN NUMBER )
1318 RETURN NUMBER AS
1319 gv_other_detail VARCHAR2(255);
1320 BEGIN -- resp_get_ca_att
1321 -- Get the attendance percentage of a IGS_RE_CANDIDATURE as at a given date.
1322 -- This is determined by looking through attendance history for the persons
1323 -- IGS_RE_CANDIDATURE.
1324 -- IGS_GE_NOTE: A returned value of NULL indicates that the value could not be
1325 -- determined.
1326 DECLARE
1327 v_ca_sequence_number IGS_RE_CANDIDATURE.sequence_number%TYPE;
1328 v_attendance_percentage IGS_RE_CANDIDATURE.attendance_percentage%TYPE;
1329 v_attendance_type IGS_EN_STDNT_PS_ATT.attendance_type%TYPE;
1330 CURSOR c_ca_sca IS
1331 SELECT ca.sequence_number,
1332 ca.attendance_percentage,
1333 sca.attendance_type
1334 FROM IGS_RE_CANDIDATURE ca,
1335 IGS_EN_STDNT_PS_ATT sca
1336 WHERE ca.person_id = p_person_id AND
1337 ca.sca_course_cd = p_course_cd AND
1338 sca.course_cd = ca.sca_course_cd AND
1339 sca.person_id = ca.person_id;
1340 CURSOR c_cah (
1341 cp_person_id IGS_RE_CANDIDATURE.person_id%TYPE,
1342 cp_ca_sequence_number IGS_RE_CANDIDATURE.sequence_number%TYPE,
1343 cp_effective_dt DATE) IS
1344 SELECT cah.attendance_type,
1345 cah.attendance_percentage
1346 FROM IGS_RE_CDT_ATT_HIST cah
1347 WHERE cah.person_id = cp_person_id AND
1348 cah.ca_sequence_number = cp_ca_sequence_number AND
1349 cah.hist_end_dt >= cp_effective_dt
1350 ORDER BY cah.hist_start_dt ASC;
1351 v_cah_rec c_cah%ROWTYPE;
1352 CURSOR c_att (
1353 cp_attendance_type IGS_EN_STDNT_PS_ATT.attendance_type%TYPE) IS
1354 SELECT att.research_percentage
1355 FROM IGS_EN_ATD_TYPE att
1356 WHERE att.attendance_type = cp_attendance_type;
1357 BEGIN
1358 -- If IGS_RE_CANDIDATURE details have not been passed then load the record
1359 IF p_ca_sequence_number IS NULL OR
1360 p_attendance_type IS NULL THEN
1361 OPEN c_ca_sca;
1362 FETCH c_ca_sca INTO v_ca_sequence_number,
1363 v_attendance_percentage,
1364 v_attendance_type;
1365 IF c_ca_sca%NOTFOUND THEN
1366 CLOSE c_ca_sca;
1367 RETURN NULL;
1368 END IF;
1369 CLOSE c_ca_sca;
1370 ELSE
1371 v_ca_sequence_number := p_ca_sequence_number;
1372 v_attendance_percentage := p_attendance_percentage;
1373 v_attendance_type := p_attendance_type;
1374 END IF;
1375 OPEN c_cah(
1376 p_person_id,
1377 v_ca_sequence_number,
1378 p_effective_dt);
1379 FETCH c_cah INTO v_cah_rec;
1380 IF c_cah%FOUND THEN
1381 CLOSE c_cah;
1382 v_attendance_type := v_cah_rec.attendance_type;
1383 v_attendance_percentage := v_cah_rec.attendance_percentage;
1384 ELSE
1385 CLOSE c_cah;
1386 END IF;
1387 IF v_attendance_percentage IS NULL THEN
1388 -- If no percentage then get the default for the attendance type.
1389 OPEN c_att(v_attendance_type);
1390 FETCH c_att INTO v_attendance_percentage;
1391 CLOSE c_att;
1392 END IF;
1393 RETURN v_attendance_percentage;
1394 EXCEPTION
1395 WHEN OTHERS THEN
1396 IF c_ca_sca%ISOPEN THEN
1397 CLOSE c_ca_sca;
1398 END IF;
1399 IF c_cah%ISOPEN THEN
1400 CLOSE c_cah;
1401 END IF;
1402 IF c_att%ISOPEN THEN
1403 CLOSE c_att;
1404 END IF;
1405 RAISE;
1406 END;
1407 END resp_get_ca_att;
1408
1409
1410 FUNCTION resp_get_ca_comm(
1411 p_person_id IN NUMBER ,
1412 p_sca_course_cd IN VARCHAR2 ,
1413 p_acai_admission_appl_number IN NUMBER ,
1414 p_acai_nominated_course_cd IN VARCHAR2 ,
1415 p_acai_sequence_number IN NUMBER )
1416 RETURN DATE AS
1417 gv_other_detail VARCHAR2(255);
1418 BEGIN -- resp_get_ca_comm
1419 -- Set to IGS_EN_STDNT_PS_ATT.commencement_dt if it exists.
1420 -- Set to derived commencement date if IGS_EN_STDNT_PS_ATT.commencement_dt
1421 -- doesn't exist.
1422 DECLARE
1423 CURSOR c_sca IS
1424 SELECT sca.commencement_dt
1425 FROM IGS_EN_STDNT_PS_ATT sca
1426 WHERE sca.person_id = p_person_id AND
1427 sca.course_cd = p_sca_course_cd;
1428 v_sca_rec c_sca%ROWTYPE;
1429 BEGIN
1430 IF p_sca_course_cd IS NOT NULL THEN
1431 OPEN c_sca;
1432 FETCH c_sca INTO v_sca_rec;
1433 IF (c_sca%NOTFOUND) THEN
1434 CLOSE c_sca;
1435 RETURN SYSDATE;
1436 END IF;
1437 CLOSE c_sca;
1438 IF v_sca_rec.commencement_dt IS NOT NULL THEN
1439 RETURN v_sca_rec.commencement_dt;
1440 END IF;
1441 END IF;
1442 RETURN IGS_EN_GEN_002.enrp_get_acad_comm(
1443 NULL,
1444 NULL,
1445 p_person_id,
1446 p_sca_course_cd,
1447 p_acai_admission_appl_number,
1448 p_acai_nominated_course_cd,
1449 p_acai_sequence_number,
1450 'Y');
1451 END;
1452 END resp_get_ca_comm;
1453
1454 END IGS_RE_GEN_001 ;
[Home] [Help]