1 PACKAGE BODY igs_av_val_asu AS
2 /* $Header: IGSAV04B.pls 120.4 2006/03/27 01:33:39 shimitta ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 24-AUG-2001 Bug No. 1956374 .The function genp_val_staff_prsn removed
7 --msrinivi 24-AUG-2001 Bug No. 1956374 .The function genp_val_prsn_id removed
8 -- To validate the advanced standing basis IGS_OR_INSTITUTION code.
9 --
10 -- skoppula 15-SEP-2001 Enhancement Bug for Academic Records Maintenance DLD
11 -- To change the credit_percentage logic to include advance standing credit points
12 --
13 -- nalkumar 11-Sep-2001 Added Parameter 'p_adv_stnd_trans' in advp_val_as_dates, advp_val_expiry_dt
14 -- advp_val_status_dts functions.
15 -- These changes has been done as per the Career Impact DLD.
16 -- Bug# 2027984.
17 --nalkumar 05-June-2002 Replaced the referances of the igs_av_stnd_unit/unit_lvl.(PREV_UNIT_CD and TEST_DETAILS_ID) columns
18 -- to igs_av_stnd_unit/unit_lvl.(unit_details_id and tst_rslt_dtls_id) columns. This is as per Bug# 2401170
19 --
20 --nalkumar 09-Sept-2002 Removed the references to the 'igs_av_stnd_conf_all.adv_stnd_cutoff_dt_alias' coulmn.
21 -- This column has been obsoleted as part of the Bug# 2446569.
22 -- Modified the 'advp_get_ua_del_alwd' function and removed the validations related to the obsoleted column.
23 --kdande 16-Oct-2002 Bug# 2627933. Changed the data type to column%TYPE to avoid value error.
24 -- And added check to see that the total approved exemption is < 999.999.
25 --svenkata 27-Nov-02 The routine 'adv_Credit_pts' has been modified to check the value of the fetched parameter l_adv_credits.s_adv_stnd_granting_status
26 -- instead of the IN parameter , which is incorrect.
27 -- kdande 08-Oct-03 Bug# 3154803; Changed the select to handle NULL values
28 --
29 -- nalkumar 10-Dec-2003 Bug# 3270446 RECR50 Build; Obsoleted the IGS_AV_STND_UNIT.CREDIT_PERCENTAGE column.
30 -- lkaki 05-Apr-2005 Bug# 4135171 The check for looking whether the computed value of 'v_total_exmptn_perc_grntd' is
31 -- changed to include values beyond 999.
32 -- swaghmar 25-Nov-2005 Bug# 4754378 Modified the cursor C_ADV_CP_PER
33 -- shimitta 07-Mar-2006 BUg# 5068233
34 -------------------------------------------------------------------------------------------
35 --
36 -- Validate the exemption institution code
37 --
38 FUNCTION advp_val_asu_inst (
39 p_exempt_inst IN VARCHAR2,
40 p_message_name OUT NOCOPY VARCHAR2
41 ) RETURN BOOLEAN IS
42 gv_other_detail VARCHAR2 (255);
43 BEGIN
44 DECLARE
45 CURSOR c_adv_stnd_exempt_inst_v (
46 cp_exempt_inst igs_av_adv_standing.exemption_institution_cd%TYPE
47 ) IS
48 SELECT ihp.oss_org_unit_cd exemption_institution_cd
49 FROM igs_pe_hz_parties ihp
50 WHERE ihp.inst_org_ind = 'I'
51 AND ihp.oss_org_unit_cd = cp_exempt_inst
52 UNION ALL
53 SELECT lk.lookup_code exemption_institution_cd
54 FROM igs_lookup_values lk
55 WHERE lk.lookup_type = 'OR_INSTITUTION_ADVSTEX'
56 AND lk.enabled_flag = 'Y'
57 AND lk.lookup_code = cp_exempt_inst;
58 BEGIN
59 -- Validate that exemption_institution_cd (IGS_AV_STND_UNIT or IGS_AV_STND_UNIT_LVL
60 -- ) is valid.
61 -- The status is not considered, as it is allowable to select an inactive
62 -- IGS_OR_INSTITUTION for advanced standing basis details.
63
64 p_message_name := NULL;
65
66 -- Validate input parameters.
67 IF (p_exempt_inst IS NULL) THEN
68 RETURN TRUE;
69 END IF;
70
71 -- Validate that exemption IGS_OR_INSTITUTION is valid.
72 FOR v_adv_stnd_exempt_inst_rec IN c_adv_stnd_exempt_inst_v (p_exempt_inst) LOOP
73 RETURN TRUE;
74 END LOOP;
75
76 p_message_name := 'IGS_GE_INVALID_VALUE';
77 RETURN FALSE;
78 EXCEPTION
79 WHEN OTHERS THEN
80 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
81 fnd_message.set_token ('NAME', 'IGS_AV_VAL_ASU.ADVP_VAL_ASU_INST');
82 igs_ge_msg_stack.ADD;
83 app_exception.raise_exception;
84 END;
85 END advp_val_asu_inst;
86 --
87 -- To validate the granting of advanced standing (form level only)
88 -- shimitta 07-Mar-2006 BUg# 5068233
89 FUNCTION advp_val_as_frm_grnt (
90 p_person_id IN NUMBER,
91 p_course_cd IN VARCHAR2,
92 p_version_number IN NUMBER,
93 p_s_adv_stnd_granting_status IN VARCHAR2,
94 p_message_name OUT NOCOPY VARCHAR2
95 )
96 RETURN BOOLEAN IS
97 BEGIN
98 DECLARE
99 CURSOR c_course_details (
100 cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
101 cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE
102 ) IS
103 SELECT sca.version_number,
104 sca.course_attempt_status
105 FROM igs_en_stdnt_ps_att sca
106 WHERE sca.person_id = cp_person_id
107 AND sca.course_cd = cp_course_cd;
108
109 v_other_detail VARCHAR2 (255);
110 v_version_number igs_en_stdnt_ps_att.version_number%TYPE;
111 v_course_attempt_status igs_en_stdnt_ps_att.course_attempt_status%TYPE;
112 BEGIN
113 -- This function validates that an advanced standing
114 -- IGS_PS_UNIT can be granted.
115 -- IGS_GE_NOTE : this does not include IGS_PE_PERSON encumbrance
116 -- checks and IGS_PS_COURSE version advanced standing limit
117 -- checks.
118 -- This checks the IGS_AV_STND_UNIT.adv_stnd_granting
119 -- status maps to APPROVED, the IGS_EN_STDNT_PS_ATT
120 -- exists and has an attempt status of enrolled, inactive or
121 -- intermit and the IGS_EN_STDNT_PS_ATT version matches
122 -- the advanced standing approved.
123 -- set the default message number
124 p_message_name := NULL;
125
126 -- validate the input parameters
127 IF (p_person_id IS NULL
128 OR p_course_cd IS NULL
129 OR p_version_number IS NULL
130 OR p_s_adv_stnd_granting_status IS NULL
131 ) THEN
132 p_message_name := 'IGS_AV_INSUFFICIENT_INFO';
133 RETURN FALSE;
134 END IF;
135
136 -- validate that the current advanced standing
137 -- status is approved
138 IF (p_s_adv_stnd_granting_status <> 'APPROVED') THEN
139 p_message_name := 'IGS_AV_GRANTED_CURSTATUS_APPR';
140 RETURN FALSE;
141 END IF;
142
143 -- validate that a IGS_EN_STDNT_PS_ATT exists,
144 -- whether it is the correct version and has a
145 -- IGS_PS_COURSE attempt status of 'ENROLLED','INACTIVE' or
146 -- 'INTERMIT'
147 OPEN c_course_details (p_person_id, p_course_cd);
148 FETCH c_course_details INTO v_version_number,
149 v_course_attempt_status;
150
151 -- check if a record was found or not
152 IF (c_course_details%NOTFOUND) THEN
153 CLOSE c_course_details;
154 p_message_name := 'IGS_AV_GRANTED_STUDPRG_EXISTS';
155 RETURN FALSE;
156 ELSE
157 CLOSE c_course_details;
158
159 -- check if the versions are the same,
160 -- which they must be
161 IF (v_version_number <> p_version_number) THEN
162 p_message_name := 'IGS_AV_GRANTED_STUD_PRGVER';
163 RETURN FALSE;
164 END IF;
165
166 -- check the IGS_PS_COURSE attempt status,
167 -- which must be enrolled
168 IF (v_course_attempt_status IN('UNCONFIRM', 'LAPSED', 'DELETED')) THEN
169 p_message_name := 'IGS_AV_GRANTED_STUD_ENR';
170 RETURN FALSE;
171 ELSIF (v_course_attempt_status = 'INTERMIT') THEN
172 p_message_name := 'IGS_AV_NOTGRANT_ON_INTERMISSI';
173 RETURN FALSE;
174 END IF;
175 END IF;
176
177 -- set the default return type
178 RETURN TRUE;
179 EXCEPTION
180 WHEN OTHERS THEN
181 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
182 fnd_message.set_token ('NAME', 'IGS_AV_VAL_ASU.ADVP_VAL_AS_FRM_GRNT');
183 igs_ge_msg_stack.ADD;
184 app_exception.raise_exception;
185 END;
186 END advp_val_as_frm_grnt;
187 --
188 -- To validate the various dates of advanced standing units or levels.
189 --
190 FUNCTION advp_val_as_dates (
191 p_advanced_standing_dt IN DATE,
192 p_date_type IN VARCHAR2,
193 p_message_name OUT NOCOPY VARCHAR2,
194 p_adv_stnd_trans IN VARCHAR2
195 ) -- This parameter has been added for Career Impact DLD.
196 RETURN BOOLEAN IS
197 gv_other_detail VARCHAR2 (255);
198 BEGIN
199 -- advp_val_as_dates
200 -- Validate that IGS_AV_ADV_STANDING related dates are not
201 -- greater than the current date.
202 DECLARE
203 v_ret_val BOOLEAN := TRUE;
204 BEGIN
205 p_message_name := NULL;
206
207 IF (p_advanced_standing_dt IS NULL
208 OR p_date_type IS NULL
209 ) THEN
210 RETURN TRUE;
211 END IF;
212
213 IF (p_advanced_standing_dt > SYSDATE)
214 AND p_adv_stnd_trans = 'N' THEN
215 IF (p_date_type = 'APPROVED') THEN
216 p_message_name := 'IGS_AV_APRVDT_LE_CURDT';
217 v_ret_val := FALSE;
218 ELSIF (p_date_type = 'GRANTED') THEN
219 p_message_name := 'IGS_AV_GRANTDT_LE_CURDT';
220 v_ret_val := FALSE;
221 ELSIF (p_date_type = 'CANCELLED') THEN
222 p_message_name := 'IGS_AV_CANCELDT_LE_CURDT';
223 v_ret_val := FALSE;
224 ELSIF (p_date_type = 'REVOKED') THEN
225 p_message_name := 'IGS_AV_REVOKED_LE_CURDT';
226 v_ret_val := FALSE;
227 ELSE
228 -- by default return TRUE
229 NULL;
230 END IF;
231 END IF;
232
233 RETURN v_ret_val;
234 END;
235 EXCEPTION
236 WHEN OTHERS THEN
237 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
238 fnd_message.set_token ('NAME', 'IGS_AV_VAL_ASU.ADVP_VAL_AS_DATES');
239 igs_ge_msg_stack.ADD;
240 app_exception.raise_exception;
241 END advp_val_as_dates;
242 --
243 -- Routine to clear rowids saved in a PL/SQL TABLE from a prior commit.
244 --
245 -- To validate the status dates of advanced standing units or levels.
246 --
247 FUNCTION advp_val_status_dts (
248 p_granting_status IN VARCHAR2,
249 p_related_dt IN DATE,
250 p_message_name OUT NOCOPY VARCHAR2,
251 p_adv_stnd_trans IN VARCHAR2
252 ) -- This parameter has been added for Career Impact DLD.
253 RETURN BOOLEAN IS
254 gv_other_detail VARCHAR2 (255);
255 BEGIN -- advp_val_status_dts
256 -- Validate that if s_adv_stnd_granting_status is specified,
257 -- then its correspondeing date is also specified.
258 DECLARE
259 v_ret_val BOOLEAN := TRUE;
260 BEGIN
261 p_message_name := NULL;
262
263 IF (p_granting_status IS NULL) THEN
264 RETURN TRUE;
265 END IF;
266
267 -- validate that related_dt is not null
268 IF (p_related_dt IS NULL)
269 AND p_adv_stnd_trans = 'N' THEN
270 p_message_name := 'IGS_AV_ASSODT_SET_GRANT_ST';
271 v_ret_val := FALSE;
272 END IF;
273
274 RETURN v_ret_val;
275 END;
276 EXCEPTION
277 WHEN OTHERS THEN
278 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
279 fnd_message.set_token ('NAME', 'IGS_AV_VAL_ASU.ADVP_VAL_STATUS_DTS');
280 igs_ge_msg_stack.ADD;
281 app_exception.raise_exception;
282 END advp_val_status_dts;
283 --
284 -- Validate the AS recognition type closed indicator.
285 --
286 FUNCTION advp_val_asrt_closed (
287 p_recognition_type IN VARCHAR2,
288 p_message_name OUT NOCOPY VARCHAR2
289 )
290 RETURN BOOLEAN IS
291 gv_other_detail VARCHAR2 (255);
292 BEGIN -- check if the s_adv_stnd_recognition_type is closed
293 DECLARE
294 v_closed_ind CHAR;
295
296 CURSOR c_get_closed_ind (
297 cp_recognition_type igs_lookups_view.lookup_code%TYPE
298 ) IS
299 SELECT closed_ind
300 FROM igs_lookups_view
301 WHERE lookup_type = 'ADV_STND_RECOGNITION_TYPE'
302 AND lookup_code = p_recognition_type;
303 BEGIN
304 p_message_name := NULL;
305
306 -- Validate input parameters
307 IF (p_recognition_type IS NULL) THEN
308 RETURN TRUE;
309 END IF;
310
311 -- Validate if the advanced standing recognition type is closed
312 OPEN c_get_closed_ind (p_recognition_type);
313 FETCH c_get_closed_ind INTO v_closed_ind;
314
315 IF (c_get_closed_ind%NOTFOUND) THEN
316 CLOSE c_get_closed_ind;
317 RETURN TRUE;
318 END IF;
319
320 CLOSE c_get_closed_ind;
321
322 IF (v_closed_ind = 'Y') THEN
323 p_message_name := 'IGS_AV_RECOGNITION_TYPE_CLOSE';
324 RETURN FALSE;
325 END IF;
326
327 RETURN TRUE;
328 END;
329 EXCEPTION
330 WHEN OTHERS THEN
331 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
332 fnd_message.set_token ('NAME', 'IGS_AV_VAL_ASU.ADVP_VAL_ASRT_CLOSED');
333 igs_ge_msg_stack.ADD;
334 app_exception.raise_exception;
335 END advp_val_asrt_closed;
336 --
337 -- To validate the approved date of advanced standing units or levels.
338 --
339 FUNCTION advp_val_as_aprvd_dt (
340 p_approved_dt IN DATE,
341 p_related_dt IN DATE,
342 p_message_name OUT NOCOPY VARCHAR2
343 )
344 RETURN BOOLEAN IS
348 -- less than the granted_dt, cancelled_dt OR revoked_dt for the same record
345 gv_other_detail VARCHAR2 (255);
346 BEGIN -- advp_val_as_aprvd_dt
347 -- Validate that approved_dt(adv-stnd_unit OR IGS_AV_STND_UNIT_LVL) is not
349 DECLARE
350 v_ret_val BOOLEAN := TRUE;
351 BEGIN
352 p_message_name := NULL;
353
354 -- validate input parameter
355 IF (p_approved_dt IS NULL
356 OR p_related_dt IS NULL
357 ) THEN
358 RETURN TRUE;
359 END IF;
360
361 -- Validate that related_dt is greater than or equal to the approved_dt
362 IF (p_related_dt < p_approved_dt) THEN
363 p_message_name := 'IGS_AV_DTASSO_LE_APPRVDT';
364 v_ret_val := FALSE;
365 END IF;
366
367 RETURN v_ret_val;
368 END;
369 EXCEPTION
370 WHEN OTHERS THEN
371 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
372 fnd_message.set_token ('NAME', 'IGS_AV_VAL_ASU.ADVP_VAL_AS_APRVD_DT');
373 igs_ge_msg_stack.ADD;
374 app_exception.raise_exception;
375 END advp_val_as_aprvd_dt;
376 --
377 -- To validate the approved date of advanced standing units or levels.
378 --
379 FUNCTION advp_val_approved_dt (
380 p_approved_dt IN DATE,
381 p_expiry_dt IN DATE,
382 p_message_name OUT NOCOPY VARCHAR2
383 )
384 RETURN BOOLEAN IS
385 gv_other_detail VARCHAR2 (255);
386 BEGIN -- Validate that IGS_AV_STND_UNIT.approved_dt is less
387 -- than or equal to IGS_AV_STND_UNIT.expiry_dt
388 DECLARE
389 BEGIN
390 p_message_name := NULL;
391
392 -- Validate input parameters
393 IF (p_approved_dt IS NULL
394 OR p_expiry_dt IS NULL
395 ) THEN
396 RETURN TRUE;
397 END IF;
398
399 -- Validate that approved_dt is less than or equal to expiry_dt
400 IF (TRUNC (p_approved_dt) > TRUNC (p_expiry_dt)) THEN
401 p_message_name := 'IGS_AV_APRVDT_NOT_GT_EXPDT';
402 RETURN FALSE;
403 ELSE
404 RETURN TRUE;
405 END IF;
406 END;
407 EXCEPTION
408 WHEN OTHERS THEN
409 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
410 fnd_message.set_token ('NAME', 'IGS_AV_VAL_ASU.ADVP_VAL_APPROVED_DT');
411 igs_ge_msg_stack.ADD;
412 app_exception.raise_exception;
413 END advp_val_approved_dt;
414 --
415 -- To validate the expiry date of advanced standing units or levels.
416 --
417 FUNCTION advp_val_expiry_dt (
418 p_expiry_dt IN DATE,
419 p_message_name OUT NOCOPY VARCHAR2,
420 p_adv_stnd_trans IN VARCHAR2
421 ) -- This parameter has been added for Career Impact DLD.
422 RETURN BOOLEAN IS
423 gv_other_detail VARCHAR2 (255);
424 BEGIN -- Validate that IGS_AV_STND_UNIT.expiry_dt
425 -- is greater than the current date
426 DECLARE
427 BEGIN
428 p_message_name := NULL;
429
430 -- Validate input parameters
431 IF (p_expiry_dt IS NULL) THEN
432 RETURN TRUE;
433 END IF;
434
435 -- Validate that expiry_dt is greater than the current date
436 IF (TRUNC (p_expiry_dt) <= TRUNC (SYSDATE))
437 AND p_adv_stnd_trans = 'N' THEN
438 p_message_name := 'IGS_AV_EXPDT_GT_CURDT';
439 RETURN FALSE;
440 ELSE
441 RETURN TRUE;
442 END IF;
443 END;
444 EXCEPTION
445 WHEN OTHERS THEN
446 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
447 fnd_message.set_token ('NAME', 'IGS_AV_VAL_ASU.ADVP_VAL_EXPIRY_DT');
448 igs_ge_msg_stack.ADD;
449 app_exception.raise_exception;
450 END advp_val_expiry_dt;
451 --
452 -- To validate the credit percentage of advanced standing units.
453 --
454 FUNCTION advp_val_credit_perc (
455 p_percentage IN NUMBER,
456 p_message_name OUT NOCOPY VARCHAR2
457 )
458 RETURN BOOLEAN IS
459 gv_other_detail VARCHAR2 (255);
460 BEGIN -- advp_val_credit_perc
461 -- Validate that the credit spcified for an advanced standing
462 -- IGS_PS_UNIT is either: a multiple of 5, equal to 33 or equal to 66
463 DECLARE
464 v_ret_val BOOLEAN := TRUE;
465 BEGIN
466 p_message_name := NULL;
467
468 IF (p_percentage = 33
469 OR p_percentage = 66
470 OR p_percentage MOD 5 = 0
471 ) THEN
472 p_message_name := NULL;
473 v_ret_val := TRUE;
474 ELSE
475 p_message_name := 'IGS_AV_CREDITPRC_33_66_MULT5';
476 v_ret_val := FALSE;
477 END IF;
478
479 RETURN v_ret_val;
480 END;
481 EXCEPTION
482 WHEN OTHERS THEN
483 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
484 fnd_message.set_token ('NAME', 'IGS_AV_VAL_ASU.ADVP_VAL_CREDIT_PERC');
485 igs_ge_msg_stack.ADD;
486 app_exception.raise_exception;
487 END advp_val_credit_perc;
488 --
489 -- To validate internal/external advanced standing IGS_PS_COURSE limits.
490 --
491
492
496 p_version_number IN NUMBER,
493 FUNCTION advp_val_as_totals (
494 p_person_id IN NUMBER,
495 p_course_cd IN VARCHAR2,
497 p_include_approved IN BOOLEAN,
498 p_asu_unit_cd IN VARCHAR2,
499 p_asu_version_number IN NUMBER,
500 p_asu_advstnd_granting_status IN VARCHAR2,
501 p_asul_unit_level IN VARCHAR2,
502 p_asul_exmptn_institution_cd IN VARCHAR2,
503 p_asul_advstnd_granting_status IN VARCHAR2,
504 p_total_exmptn_approved OUT NOCOPY NUMBER,
505 p_total_exmptn_granted OUT NOCOPY NUMBER,
506 p_total_exmptn_perc_grntd OUT NOCOPY NUMBER,
507 p_message_name OUT NOCOPY VARCHAR2,
508 p_unit_details_id IN NUMBER,
509 p_tst_rslt_dtls_id IN NUMBER,
510 p_asu_exmptn_institution_cd IN VARCHAR2
511 )
512 RETURN BOOLEAN IS
513 BEGIN
514 DECLARE
515 cst_approved CONSTANT VARCHAR2 (10) := 'APPROVED';
516 cst_granted CONSTANT VARCHAR2 (10) := 'GRANTED';
517 cst_cancelled CONSTANT VARCHAR2 (10) := 'CANCELLED';
518 cst_revoked CONSTANT VARCHAR2 (10) := 'REVOKED';
519 cst_expired CONSTANT VARCHAR2 (10) := 'EXPIRED';
520 cst_credit CONSTANT VARCHAR2 (10) := 'CREDIT';
521 cst_not_instn CONSTANT VARCHAR2 (10) := 'NOT INSTN';
522 cst_unknown CONSTANT VARCHAR2 (10) := 'UNKNOWN';
523 --
524 -- Cursor to get the Advanced Standing Limits from the Course Version setup
525 -- kdande; 07-Oct-2003; Bug# 3154803; Changed the select to handle NULL values
526 --
527 CURSOR c_course_version_details (
528 cp_course_cd igs_ps_ver.course_cd%TYPE,
529 cp_version_number igs_ps_ver.version_number%TYPE
530 ) IS
531 SELECT NVL (cv.external_adv_stnd_limit, 0) external_adv_stnd_limit,
532 NVL (cv.internal_adv_stnd_limit, 0) internal_adv_stnd_limit,
533 NVL (cv.credit_points_required, 0) credit_points_required
534 FROM igs_ps_ver cv
535 WHERE cv.course_cd = cp_course_cd
536 AND cv.version_number = cp_version_number;
537
538 CURSOR c_local_inst_ind (cp_ins_cd igs_or_institution.institution_cd%TYPE) IS
539 SELECT ins.local_institution_ind
540 FROM igs_or_institution ins
541 WHERE ins.institution_cd = cp_ins_cd;
542
543 CURSOR c_adv_stnd_unit_details (
544 cp_person_id igs_av_stnd_unit.person_id%TYPE,
545 cp_course_cd igs_av_stnd_unit.as_course_cd%TYPE,
546 cp_version_number igs_av_stnd_unit.as_version_number%TYPE
547 ) IS
548 SELECT /* asu.credit_percentage, */
549 asu.exemption_institution_cd,
550 asu.s_adv_stnd_granting_status,
551 asu.unit_cd,
552 asu.version_number,
553 -- asu.prev_unit_cd,
554 asu.unit_details_id,
555 -- asu.test_segment_id
556 asu.tst_rslt_dtls_id
557 FROM igs_av_stnd_unit asu
558 WHERE asu.person_id = cp_person_id
559 AND asu.as_course_cd = cp_course_cd
560 AND asu.as_version_number = cp_version_number
561 AND asu.s_adv_stnd_granting_status IN (cst_approved, cst_granted)
562 AND asu.s_adv_stnd_recognition_type = cst_credit;
563
564
565 CURSOR c_adv_stnd_unit_level (
566 cp_person_id igs_av_stnd_unit_lvl.person_id%TYPE,
567 cp_course_cd igs_av_stnd_unit_lvl.as_course_cd%TYPE,
568 cp_version_num igs_av_stnd_unit_lvl.as_version_number%TYPE
569 ) IS
570 SELECT NVL (asul.credit_points, 0) credit_points,
571 asul.exemption_institution_cd,
572 asul.unit_level,
573 asul.s_adv_stnd_granting_status
574 FROM igs_av_stnd_unit_lvl asul
575 WHERE asul.person_id = cp_person_id
576 AND asul.as_course_cd = cp_course_cd
577 AND asul.as_version_number = cp_version_num
578 AND asul.s_adv_stnd_granting_status IN (cst_approved, cst_granted);
579
580 v_other_detail VARCHAR2 (255);
581 v_adv_stnd_granting_status igs_av_stnd_unit.s_adv_stnd_granting_status%TYPE;
582 v_ext_adv_stnd_limit igs_ps_ver.external_adv_stnd_limit%TYPE;
583 v_int_adv_stnd_limit igs_ps_ver.internal_adv_stnd_limit%TYPE;
584 v_credit_points_reqd igs_ps_ver.credit_points_required%TYPE;
585 v_local_inst_ind igs_or_institution.institution_cd%TYPE;
586 v_external_adv_stnd_total NUMBER;
587 v_internal_adv_stnd_total NUMBER;
588 -- 16-Oct-2002; kdande; Bug# 2627933
589 -- Changed the data type to column%TYPE to avoid value error.
590 v_total_exmptn_approved igs_av_adv_standing_all.total_exmptn_approved%TYPE;
591 v_total_exmptn_granted igs_av_adv_standing_all.total_exmptn_granted%TYPE;
592 v_total_exmptn_perc_grntd igs_av_adv_standing_all.total_exmptn_perc_grntd%TYPE;
593 v_add_in_totals BOOLEAN;
594 v_adv_stnd c_adv_stnd_unit_details%ROWTYPE;
595 v_credits NUMBER;
596 v_inst_credits NUMBER;
597 BEGIN
601 -- advanced standing exemption totals.
598 -- This function validates that the advanced standing
599 -- approved/granted has not exceeded the advanced
600 -- standing limits of the IGS_PS_COURSE version. It returns
602 -- initialise counts
603 v_external_adv_stnd_total := 0;
604 v_internal_adv_stnd_total := 0;
605 v_total_exmptn_approved := 0;
606 v_total_exmptn_granted := 0;
607 v_total_exmptn_perc_grntd := 0;
608
609 -- validate the input parameters
610 IF (p_person_id IS NULL
611 OR p_course_cd IS NULL
612 OR p_version_number IS NULL
613 ) THEN
614 p_total_exmptn_approved := v_total_exmptn_approved;
615 p_total_exmptn_granted := v_total_exmptn_granted;
616 p_total_exmptn_perc_grntd := v_total_exmptn_perc_grntd;
617 p_message_name := 'IGS_AV_INSUFFICIENT_INFO_VER';
618 RETURN FALSE;
619 END IF;
620
621 IF (p_asu_unit_cd IS NOT NULL) THEN
622 IF (p_asu_version_number IS NULL
623 OR p_asu_advstnd_granting_status IS NULL
624 ) THEN
625 p_total_exmptn_approved := v_total_exmptn_approved;
626 p_total_exmptn_granted := v_total_exmptn_granted;
627 p_total_exmptn_perc_grntd := v_total_exmptn_perc_grntd;
628 p_message_name := 'IGS_AV_INSUFFICIENT_INFO_VER';
629 RETURN FALSE;
630 END IF;
631 END IF;
632
633 -- added as part of academic records maintenance DLD ;
634 -- If unit is not null abd both unit_details_id and tst_rslt_dtls_id are null then returns false
635 IF p_asu_unit_cd IS NOT NULL
636 AND (p_unit_details_id IS NULL
637 AND p_tst_rslt_dtls_id IS NULL
638 ) THEN
639 p_total_exmptn_approved := v_total_exmptn_approved;
640 p_total_exmptn_granted := v_total_exmptn_granted;
641 p_total_exmptn_perc_grntd := v_total_exmptn_perc_grntd;
642 p_message_name := 'IGS_AV_INSUFFICIENT_INFO_VER';
643 RETURN FALSE;
644 END IF;
645
646 -- added as part of academic records maintenance DLD ;
647 -- If both unit_details_id and tst_rslt_dtls_id are not null then returns false
648 IF p_unit_details_id IS NOT NULL
649 AND p_tst_rslt_dtls_id IS NOT NULL THEN
650 p_total_exmptn_approved := v_total_exmptn_approved;
651 p_total_exmptn_granted := v_total_exmptn_granted;
652 p_total_exmptn_perc_grntd := v_total_exmptn_perc_grntd;
653 p_message_name := 'IGS_AV_INSUFFICIENT_INFO_VER';
654 RETURN FALSE;
655 END IF;
656
657 IF (p_asul_unit_level IS NOT NULL) THEN
658 IF (p_asul_exmptn_institution_cd IS NULL
659 OR p_asul_advstnd_granting_status IS NULL
660 ) THEN
661 p_total_exmptn_approved := v_total_exmptn_approved;
662 p_total_exmptn_granted := v_total_exmptn_granted;
663 p_total_exmptn_perc_grntd := v_total_exmptn_perc_grntd;
664 p_message_name := 'IGS_AV_INSUFFICIENT_INFO_VER';
665 RETURN FALSE;
666 END IF;
667 END IF;
668
669 -- get the IGS_PS_VER advanced
670 -- standing limits
671 OPEN c_course_version_details (p_course_cd, p_version_number);
672 FETCH c_course_version_details INTO v_ext_adv_stnd_limit,
673 v_int_adv_stnd_limit,
674 v_credit_points_reqd;
675
676 IF (c_course_version_details%NOTFOUND) THEN
677 -- invalid parameters entered
678 CLOSE c_course_version_details;
679 p_total_exmptn_approved := v_total_exmptn_approved;
680 p_total_exmptn_granted := v_total_exmptn_granted;
681 p_total_exmptn_perc_grntd := v_total_exmptn_perc_grntd;
682 p_message_name := 'IGS_AV_INSUFFICIENT_INFO_VER';
683 RETURN FALSE;
684 END IF;
685
686 CLOSE c_course_version_details;
687
688 FOR v_adv_stnd IN c_adv_stnd_unit_details (
689 p_person_id,
690 p_course_cd,
691 p_version_number
692 ) LOOP
693 v_add_in_totals := TRUE;
694 v_adv_stnd_granting_status := v_adv_stnd.s_adv_stnd_granting_status;
695
696 IF (p_asu_unit_cd IS NOT NULL) THEN
697 -- check the status of p_asu_unit_cd
698 IF (p_asu_unit_cd = v_adv_stnd.unit_cd
699 AND p_asu_version_number = v_adv_stnd.version_number
700 ) THEN
701 IF (p_asu_advstnd_granting_status IN
702 (cst_cancelled, cst_revoked, cst_expired)
703 ) THEN
704 -- do not include in counts
705 -- continue processing
706 v_add_in_totals := FALSE;
707 ELSE
708 v_adv_stnd_granting_status := p_asu_advstnd_granting_status;
709 END IF;
710 END IF;
711 END IF;
712
713 advp_get_adv_credit_pts (
714 p_person_id,
715 p_course_cd,
716 p_version_number,
717 v_adv_stnd_granting_status,
718 v_adv_stnd.unit_cd,
719 v_adv_stnd.version_number,
720 -- v_adv_stnd.prev_unit_cd,
724 v_credits,
721 -- v_adv_stnd.test_segment_id,
722 v_adv_stnd.unit_details_id,
723 v_adv_stnd.tst_rslt_dtls_id,
725 v_inst_credits,
726 p_asu_exmptn_institution_cd
727 );
728 v_credits := NVL (v_credits, 0);
729 v_inst_credits := NVL(v_inst_credits,0);
730 -- To handle null values
731
732 IF (v_add_in_totals = TRUE) THEN
733 -- add to exemption totals
734 -- only include total exempt IGS_PS_UNITs
735 IF (v_adv_stnd_granting_status = cst_approved) THEN
736 -- add achieveable_credit_points to v_total_exmptn_approved
737
738 v_total_exmptn_approved := v_total_exmptn_approved + v_inst_credits;
739 ELSE
740 -- add achieveable_credit_points to v_total_exmptn_granted
741 v_total_exmptn_granted := v_total_exmptn_granted + v_inst_credits;
742 END IF;
743 -- add to totals for validation
744 IF NOT p_include_approved
745 AND (v_adv_stnd_granting_status = cst_approved) THEN
746 -- don't include in totals
747 NULL;
748 ELSE
749 IF (v_adv_stnd.exemption_institution_cd IN
750 (cst_not_instn, cst_unknown)
751 ) THEN
752 -- add to external totals
753 v_external_adv_stnd_total := v_external_adv_stnd_total + v_credits;
754 ELSE
755 -- selecting the IGS_OR_INSTITUTION.local_institution_cd
756 OPEN c_local_inst_ind (v_adv_stnd.exemption_institution_cd);
757 FETCH c_local_inst_ind INTO v_local_inst_ind;
758
759 -- not in IGS_OR_INSTITUTION table, so add to external totals
760 IF (c_local_inst_ind%NOTFOUND) THEN
761 v_external_adv_stnd_total :=
762 v_external_adv_stnd_total + v_credits;
763 CLOSE c_local_inst_ind;
764 ELSE
765 CLOSE c_local_inst_ind;
766
767 IF (v_local_inst_ind = 'Y') THEN
768 -- add to internal totals
769 v_internal_adv_stnd_total :=
770 v_internal_adv_stnd_total + v_credits;
771 ELSE
772 -- add to external totals
773 v_external_adv_stnd_total :=
774 v_external_adv_stnd_total + v_credits;
775 END IF;
776 END IF;
777 END IF;
778 END IF;
779 END IF;
780 END LOOP;
781
782
783 --CLOSE v_av_cp_cur;
784 -- select IGS_AV_STND_UNIT_LVL for parameters
785 -- to add to existing totals
786
787 FOR v_unit_level IN c_adv_stnd_unit_level (
788 p_person_id,
789 p_course_cd,
790 p_version_number
791 ) LOOP
792 v_add_in_totals := TRUE;
793 v_adv_stnd_granting_status := v_unit_level.s_adv_stnd_granting_status;
794
795 IF (p_asul_unit_level IS NOT NULL) THEN
796 -- check the status of p_asul_unit_cd
797 IF (p_asul_unit_level = v_unit_level.unit_level
798 AND p_asul_exmptn_institution_cd =
799 v_unit_level.exemption_institution_cd
800 ) THEN
801 IF (p_asul_advstnd_granting_status IN
802 (cst_cancelled, cst_revoked, cst_expired)
803 ) THEN
804 -- do not include in counts
805 -- continue processing
806 v_add_in_totals := FALSE;
807 ELSE
808 v_adv_stnd_granting_status := p_asul_advstnd_granting_status;
809 END IF;
810 END IF;
811 END IF;
812
813 IF (v_add_in_totals = TRUE) THEN
814 -- add to exemption totals
815 IF (v_adv_stnd_granting_status = cst_approved) THEN
816 -- 16-Oct-2002; kdande; Bug# 2627933
817 -- Added check to see that the total approved exemption is < 999.999
818 -- Start of fix for Bug# 2627933
819 IF ((v_total_exmptn_approved + v_unit_level.credit_points) > 999.999) THEN
820 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
821 igs_ge_msg_stack.ADD;
822 app_exception.raise_exception;
823 ELSIF (p_asul_exmptn_institution_cd IS NOT NULL) AND
824 (v_unit_level.exemption_institution_cd = p_asul_exmptn_institution_cd) THEN
825 v_total_exmptn_approved :=
826 v_total_exmptn_approved + v_unit_level.credit_points;
827 END IF;
828
829 -- End of fix for Bug# 2627933
830 ELSE
831 IF(p_asul_exmptn_institution_cd IS NOT NULL) AND
832 (v_unit_level.exemption_institution_cd = p_asul_exmptn_institution_cd) THEN
833 v_total_exmptn_granted :=
834 v_total_exmptn_granted + v_unit_level.credit_points;
835 END IF;
836 END IF;
837
838 -- add to totals for validation
839 IF NOT p_include_approved
843 ELSE
840 AND (v_adv_stnd_granting_status = cst_approved) THEN
841 -- don't include the totals
842 NULL;
844 IF (v_unit_level.exemption_institution_cd IN
845 (cst_not_instn, cst_unknown)
846 ) THEN
847 -- add to the external totals
848 v_external_adv_stnd_total :=
849 v_external_adv_stnd_total + v_unit_level.credit_points;
850 ELSE
851 -- selecting the IGS_OR_INSTITUTION.local_institution_cd
852 OPEN c_local_inst_ind (v_unit_level.exemption_institution_cd);
853 FETCH c_local_inst_ind INTO v_local_inst_ind;
854
855 -- not in IGS_OR_INSTITUTION table, so add to external totals
856 IF (c_local_inst_ind%NOTFOUND) THEN
857 v_external_adv_stnd_total :=
858 v_external_adv_stnd_total + v_unit_level.credit_points;
859 CLOSE c_local_inst_ind;
860 ELSE
861 CLOSE c_local_inst_ind;
862
863 IF (v_local_inst_ind = 'Y') THEN
864 -- add to internal totals
865 v_internal_adv_stnd_total :=
866 v_internal_adv_stnd_total + v_unit_level.credit_points;
867 ELSE
868 -- add to external totals
869 v_external_adv_stnd_total :=
870 v_external_adv_stnd_total + v_unit_level.credit_points;
871 END IF;
872 END IF;
873 END IF;
874 END IF;
875 END IF;
876 END LOOP;
877
878 -- after processing all records
879 -- set v_total_exmptn_perc_grnted to 0
880 -- if v_total_exmptn_granted or
881 -- v_credit_points_reqd is = 0, as
882 -- a division would produce an error
883 -- if trying to divide by 0
884 IF (v_total_exmptn_granted = 0
885 OR v_credit_points_reqd = 0
886 ) THEN
887 v_total_exmptn_perc_grntd := 0;
888 ELSE
889 -- determine IGS_PS_COURSE percentage covered by
890 -- advanced standing granted
891 -- can perform the division, as values are not 0
892 IF (((v_total_exmptn_granted/v_credit_points_reqd)*100) > 100) THEN
893 v_total_exmptn_perc_grntd := 100;
894 ELSE
895 v_total_exmptn_perc_grntd:=((v_total_exmptn_granted/v_credit_points_reqd)*100) ;
896 END IF;
897 END IF;
898
899 -- Check if granted total exceeds credit points
900 -- required for the IGS_PS_COURSE version.
901 -- Also check if the granted plus approved total exceeds the
902 -- credit points required for the IGS_PS_COURSE version.
903 IF NOT p_include_approved THEN
904 IF (v_total_exmptn_granted > v_credit_points_reqd) THEN
905 IF (p_asul_advstnd_granting_status = cst_granted
906 OR p_asu_advstnd_granting_status = cst_granted
907 ) THEN
908 p_total_exmptn_approved := v_total_exmptn_approved;
909 p_total_exmptn_granted := v_total_exmptn_granted;
910 p_total_exmptn_perc_grntd := v_total_exmptn_perc_grntd;
911 p_message_name := 'IGS_AV_MINIMUM_CREDIT_POINTS';
912 -- RETURN FALSE;
913 END IF;
914 END IF;
915 ELSE
916 IF ((v_total_exmptn_approved + v_total_exmptn_granted) >
917 v_credit_points_reqd
918 ) THEN
919 IF (p_asul_advstnd_granting_status = cst_approved
920 OR p_asu_advstnd_granting_status = cst_approved
921 ) THEN
922 p_total_exmptn_approved := v_total_exmptn_approved;
923 p_total_exmptn_granted := v_total_exmptn_granted;
924 p_total_exmptn_perc_grntd := v_total_exmptn_perc_grntd;
925 p_message_name := 'IGS_AV_MINIMUM_CREDIT_POINTS';
926 -- RETURN FALSE;
927 END IF;
928 END IF;
929 END IF;
930
931 -- check if totals exceed limits
932 -- external totals
933 IF (v_external_adv_stnd_total > v_ext_adv_stnd_limit) THEN
934 -- external limit exceeded
935 p_total_exmptn_approved := v_total_exmptn_approved;
936 p_total_exmptn_granted := v_total_exmptn_granted;
937 p_total_exmptn_perc_grntd := v_total_exmptn_perc_grntd;
938 p_message_name := 'IGS_AV_EXCEEDS_PRGVER_EXT_LMT';
939 RETURN FALSE;
940 END IF;
941
942 -- internal totals
943 IF (v_internal_adv_stnd_total > v_int_adv_stnd_limit) THEN
944 -- internal limit exceeded
945 p_total_exmptn_approved := v_total_exmptn_approved;
946 p_total_exmptn_granted := v_total_exmptn_granted;
947 p_total_exmptn_perc_grntd := v_total_exmptn_perc_grntd;
948 p_message_name := 'IGS_AV_EXCEEDS_PRGVER_INT_LMT';
949 RETURN FALSE;
950 END IF;
951
952 -- return totals
953 p_total_exmptn_approved := v_total_exmptn_approved;
954 p_total_exmptn_granted := v_total_exmptn_granted;
955 p_total_exmptn_perc_grntd := v_total_exmptn_perc_grntd;
956 -- p_message_name := null;
957 -- fnd_file.put_line(fnd_file.log,'returning true');
961 RETURN TRUE;
958 --dbms_output.put_line('exemption approved '||to_char(v_total_exmptn_approved));
959 --dbms_output.put_line('exemption granted '||to_char(v_total_exmptn_granted));
960 --dbms_output.put_line('exemption perc granted '||to_char(v_total_exmptn_perc_grntd));
962 fnd_file.put_line (fnd_file.LOG, 'returned');
963 EXCEPTION
964 WHEN OTHERS THEN
965 /*Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
966 Fnd_Message.Set_Token('NAME','IGS_AV_VAL_ASU.ADVP_VAL_AS_TOTALS');
967 Igs_Ge_Msg_Stack.Add;*/
968 --dbms_output.put_line('igsavo4b '||sqlerrm);
969 app_exception.raise_exception;
970 END;
971 END advp_val_as_totals;
972 --
973 -- To get whether delete of student IGS_PS_UNIT attempt is allowed.
974 --
975 FUNCTION advp_get_ua_del_alwd (
976 p_cal_type IN VARCHAR2,
977 p_ci_sequence_number IN NUMBER,
978 p_effective_dt IN DATE
979 )
980 RETURN BOOLEAN IS
981 BEGIN
982 DECLARE
983 CURSOR c_daiv (
984 cp_cal_type igs_ca_inst.cal_type%TYPE,
985 cp_ci_seq_num igs_ca_inst.sequence_number%TYPE
986 ) IS
987 SELECT daiv.alias_val
988 FROM igs_ca_da_inst_v daiv,
989 igs_ge_s_gen_cal_con sgcc
990 WHERE daiv.cal_type = cp_cal_type
991 AND daiv.ci_sequence_number = cp_ci_seq_num
992 AND daiv.dt_alias = sgcc.census_dt_alias
993 AND sgcc.s_control_num = 1;
994 BEGIN
995 -- This module checks whether it is possible, as
996 -- at the effective date, to delete student IGS_PS_UNIT
997 -- attempts in the nominated teaching period
998 -- calendar instance, as a result of advanced
999 -- standing granting
1000 -- if p_effective_dt is greater than the census
1001 -- date, then do not allow deletion
1002 FOR v_daiv IN c_daiv (p_cal_type, p_ci_sequence_number) LOOP
1003 IF (p_effective_dt > v_daiv.alias_val) THEN
1004 RETURN FALSE;
1005 END IF;
1006 END LOOP;
1007
1008 RETURN TRUE;
1009 EXCEPTION
1010 WHEN OTHERS THEN
1011 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1012 fnd_message.set_token ('NAME', 'IGS_AV_VAL_ASU.ADVP_GET_UA_DEL_ALWD');
1013 igs_ge_msg_stack.ADD;
1014 app_exception.raise_exception;
1015 END;
1016 END advp_get_ua_del_alwd;
1017 --
1018 -- To validate the granting of advanced standing.
1019 -- shimitta 07-Mar-2006 BUg# 5068233
1020 FUNCTION advp_val_as_grant (
1021 p_person_id IN NUMBER,
1022 p_course_cd IN VARCHAR2,
1023 p_version_number IN NUMBER,
1024 p_s_adv_stnd_granting_status IN VARCHAR2,
1025 p_message_name OUT NOCOPY VARCHAR2
1026 )
1027 RETURN BOOLEAN IS
1028 BEGIN
1029 DECLARE
1030 CURSOR c_course_details (
1031 cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
1032 cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE
1033 ) IS
1034 SELECT sca.version_number,
1035 sca.course_attempt_status
1036 FROM igs_en_stdnt_ps_att sca
1037 WHERE sca.person_id = cp_person_id
1038 AND sca.course_cd = cp_course_cd;
1039
1040 v_other_detail VARCHAR2 (255);
1041 v_version_number igs_en_stdnt_ps_att.version_number%TYPE;
1042 v_course_attempt_status igs_en_stdnt_ps_att.course_attempt_status%TYPE;
1043 BEGIN
1044 -- This function validates that an advanced standing
1045 -- IGS_PS_UNIT can be granted.
1046 -- IGS_GE_NOTE : this does not include IGS_PE_PERSON encumbrance
1047 -- checks and IGS_PS_COURSE version advanced standing limit
1048 -- checks.
1049 -- This checks the IGS_AV_STND_UNIT.adv_stnd_granting
1050 -- status maps to APPROVED, the IGS_EN_STDNT_PS_ATT
1051 -- exists and has an attempt status of enrolled and
1052 -- the IGS_EN_STDNT_PS_ATT version matches the
1053 -- advanced standing approved.
1054 -- set the default message number
1055 p_message_name := NULL;
1056
1057 -- validate the input parameters
1058 IF (p_person_id IS NULL
1059 OR p_course_cd IS NULL
1060 OR p_version_number IS NULL
1061 OR p_s_adv_stnd_granting_status IS NULL
1062 ) THEN
1063 p_message_name := 'IGS_AV_INSUFFICIENT_INFO';
1064 RETURN FALSE;
1065 END IF;
1066
1067 -- validate that the current advanced standing
1068 -- status is approved
1069 IF (p_s_adv_stnd_granting_status <> 'APPROVED') THEN
1070 p_message_name := 'IGS_AV_GRANTED_CURSTATUS_APPR';
1071 RETURN FALSE;
1072 END IF;
1073
1074 -- validate that a IGS_EN_STDNT_PS_ATT exists,
1075 -- whether it is the correct version and has a
1076 -- IGS_PS_COURSE attempt status of 'ENROLLED'
1077 OPEN c_course_details (p_person_id, p_course_cd);
1078 FETCH c_course_details INTO v_version_number,
1079 v_course_attempt_status;
1080
1084 p_message_name := 'IGS_AV_GRANTED_STUDPRG_EXISTS';
1081 -- check if a record was found or not
1082 IF (c_course_details%NOTFOUND) THEN
1083 CLOSE c_course_details;
1085 RETURN FALSE;
1086 ELSE
1087 CLOSE c_course_details;
1088
1089 -- check if the versions are the same,
1090 -- which they must be
1091 IF (v_version_number <> p_version_number) THEN
1092 p_message_name := 'IGS_AV_GRANTED_STUD_PRGVER';
1093 RETURN FALSE;
1094 END IF;
1095
1096 -- check the IGS_PS_COURSE attempt status,
1097 -- which must be enrolled
1098 IF (v_course_attempt_status IN('UNCONFIRM', 'LAPSED', 'DELETED')) THEN
1099 p_message_name := 'IGS_AV_GRANTED_STUD_ENR';
1100 RETURN FALSE;
1101 ELSIF (v_course_attempt_status = 'INTERMIT') THEN
1102 p_message_name := 'IGS_AV_NOTGRANT_ON_INTERMISSI';
1103 RETURN FALSE;
1104 END IF;
1105 END IF;
1106
1107 -- set the default return type
1108 RETURN TRUE;
1109 EXCEPTION
1110 WHEN OTHERS THEN
1111 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1112 fnd_message.set_token ('NAME', 'IGS_AV_VAL_ASU.ADVP_VAL_AS_GRANT');
1113 igs_ge_msg_stack.ADD;
1114 app_exception.raise_exception;
1115 END;
1116 END advp_val_as_grant;
1117 --
1118 -- This procdure passes back advance standing credit points
1119 -- Intorduced as part of Academic Records Maintenance DLD
1120 -- MODIFIED BY DATE DESCRIPTION
1121 -- swaghmar 09-Dec-2005 Bug# 4869528
1122
1123 PROCEDURE advp_get_adv_credit_pts (
1124 p_person_id IN NUMBER,
1125 p_course_cd IN VARCHAR2,
1126 p_version_number IN NUMBER,
1127 p_s_adv_stnd_granting_status IN VARCHAR2,
1128 p_unit_cd IN VARCHAR2,
1129 p_unit_version IN NUMBER,
1130 -- p_previous_unit IN VARCHAR2,
1131 -- p_test_segment IN VARCHAR2,
1132 p_unit_details_id IN NUMBER,
1133 p_tst_rslt_dtls_id IN NUMBER,
1134 p_credit_points OUT NOCOPY NUMBER,
1135 p_inst_credit_points OUT NOCOPY NUMBER,
1136 p_exemption_institution_cd IN VARCHAR2
1137 ) AS
1138 CURSOR c_adv_pre_unit IS
1139 SELECT asu.achievable_credit_points advance_standing_cp,
1140 asu.exemption_institution_cd,
1141 /* asu.credit_percentage, */
1142 NVL (puv.achievable_credit_points, puv.enrolled_credit_points) enrolled_cp
1143 FROM igs_av_stnd_unit asu,
1144 igs_ps_unit_ver puv
1145 WHERE asu.person_id = p_person_id
1146 AND asu.as_course_cd = p_course_cd
1147 AND asu.as_version_number = p_version_number
1148 AND asu.unit_cd = p_unit_cd
1149 AND asu.version_number = p_unit_version
1150 AND
1151 --** asu.prev_unit_cd = p_previous_unit AND
1152 asu.unit_details_id = p_unit_details_id
1153 AND asu.s_adv_stnd_granting_status IN ('APPROVED', 'GRANTED')
1154 AND asu.s_adv_stnd_recognition_type = 'CREDIT'
1155 AND puv.unit_cd = asu.unit_cd
1156 AND puv.version_number = asu.version_number;
1157
1158 CURSOR c_adv_test IS
1159 SELECT asu.achievable_credit_points advance_standing_cp,
1160 asu.exemption_institution_cd,
1161 /* asu.credit_percentage, */
1162 NVL (puv.achievable_credit_points, puv.enrolled_credit_points) enrolled_cp
1163 FROM igs_av_stnd_unit asu,
1164 igs_ps_unit_ver puv
1165 WHERE asu.person_id = p_person_id
1166 AND asu.as_course_cd = p_course_cd
1167 AND asu.as_version_number = p_version_number
1168 AND asu.unit_cd = p_unit_cd
1169 AND asu.version_number = p_unit_version
1170 AND
1171 -- asu.test_Segment_id = p_test_segment AND
1172 asu.tst_rslt_dtls_id = p_tst_rslt_dtls_id
1173 AND asu.s_adv_stnd_granting_status IN ('APPROVED', 'GRANTED')
1174 AND asu.s_adv_stnd_recognition_type = 'CREDIT'
1175 AND puv.unit_cd = asu.unit_cd
1176 AND puv.version_number = asu.version_number;
1177
1178 l_adv_preunit c_adv_pre_unit%ROWTYPE;
1179 l_adv_test c_adv_test%ROWTYPE;
1180 BEGIN
1181 IF p_unit_details_id IS NOT NULL THEN
1182 -- if previous unit cd is not null then : 1. Checks whether credit points or credit
1183 -- percentage is null.If credit points is not null then
1184 -- selects achievable credit points from igs_av_stnd_unit table.If credit percentage
1185 -- equals 100 then selects achievable credit points from igs_ps_unit_ver table.
1186
1187 OPEN c_adv_pre_unit;
1188 FETCH c_adv_pre_unit INTO l_adv_preunit;
1189
1190 IF l_adv_preunit.advance_standing_cp IS NOT NULL THEN
1191 IF (p_exemption_institution_cd IS NOT NULL) AND
1192 (l_adv_preunit.exemption_institution_cd = p_exemption_institution_cd) THEN
1193 p_inst_credit_points :=l_adv_preunit.advance_standing_cp;
1194 END IF;
1195 p_credit_points := l_adv_preunit.advance_standing_cp;
1196 /* ELSIF l_adv_preunit.advance_standing_cp IS NULL
1197 AND l_adv_preunit.credit_percentage = 100 THEN
1201 CLOSE c_adv_pre_unit;
1198 p_credit_points := l_adv_preunit.enrolled_cp; */ --Obsoleted credit_percentage column as part of RECR50 Build.
1199 END IF;
1200
1202 ELSIF p_tst_rslt_dtls_id IS NOT NULL THEN
1203 -- if test segment is not null then : 1. Checks whether credit points or credit
1204 -- percentage is null.If credit points is not null then
1205 -- selects achievable credit points from igs_av_stnd_unit table.If credit percentage
1206 -- equals 100 then selects achievable credit points from igs_ps_unit_ver table.
1207
1208 OPEN c_adv_test;
1209 FETCH c_adv_test INTO l_adv_test;
1210
1211 IF l_adv_test.advance_standing_cp IS NOT NULL THEN
1212 IF (p_exemption_institution_cd IS NOT NULL) AND
1213 (l_adv_test.exemption_institution_cd = p_exemption_institution_cd) THEN
1214 p_inst_credit_points :=l_adv_test.advance_standing_cp;
1215 END IF;
1216 p_credit_points := l_adv_test.advance_standing_cp;
1217 /* ELSIF l_adv_test.advance_standing_cp IS NULL
1218 AND l_adv_test.credit_percentage = 100 THEN
1219 p_credit_points := l_adv_test.enrolled_cp; */ --Obsoleted credit_percentage column as part of RECR50 Build.
1220 END IF;
1221 END IF;
1222 END advp_get_adv_credit_pts;
1223 --
1224 -- Function to return whether advance standing is granted for a student
1225 --
1226 FUNCTION granted_adv_standing (
1227 p_person_id IN NUMBER,
1228 p_asu_course_cd IN VARCHAR2,
1229 p_asu_version_number IN NUMBER,
1230 p_unit_cd IN VARCHAR2,
1231 p_version_number IN NUMBER,
1232 p_s_adv_stnd_granting_status IN VARCHAR2,
1233 p_effective_dt IN DATE
1234 )
1235 RETURN VARCHAR2 AS
1236 CURSOR c_enrolled_cp IS
1237 SELECT NVL (achievable_credit_points, enrolled_credit_points) enrolled_cp
1238 FROM igs_ps_unit_ver
1239 WHERE unit_cd = p_unit_cd
1240 AND (version_number = p_version_number
1241 OR p_version_number IS NULL
1242 );
1243
1244 CURSOR c_adv_cp_sum IS
1245 SELECT DECODE (
1246 p_s_adv_stnd_granting_status,
1247 s_adv_stnd_granting_status, NVL (asu.achievable_credit_points, 0),
1248 'BOTH', NVL (asu.achievable_credit_points, 0),
1249 0
1250 ) advance_standing_credits,
1251 asu.unit_cd,
1252 asu.version_number,
1253 s_adv_stnd_granting_status
1254 FROM igs_av_stnd_unit asu
1255 WHERE asu.person_id = p_person_id
1256 AND asu.as_course_cd = p_asu_course_cd
1257 AND asu.as_version_number = p_asu_version_number
1258 AND asu.unit_cd = p_unit_cd
1259 AND (asu.version_number = p_version_number
1260 OR p_version_number IS NULL
1261 )
1262 AND asu.s_adv_stnd_granting_status IN ('GRANTED', 'APPROVED')
1263 AND asu.s_adv_stnd_recognition_type = 'CREDIT'
1264 AND (p_effective_dt IS NULL
1265 OR asu.granted_dt <= TRUNC (p_effective_dt)
1266 );
1267
1268 CURSOR c_adv_cp_per IS
1269 SELECT s_adv_stnd_granting_status
1270 FROM igs_av_stnd_unit asu
1271 WHERE asu.person_id = p_person_id
1272 AND asu.as_course_cd = p_asu_course_cd
1273 AND as_version_number = p_asu_version_number
1274 AND asu.unit_cd = p_unit_cd
1275 AND (asu.version_number = p_version_number
1276 OR p_version_number IS NULL
1277 )
1278 AND asu.s_adv_stnd_granting_status IN ('GRANTED', 'APPROVED')
1279 AND asu.s_adv_stnd_recognition_type = 'CREDIT'
1280 AND (p_effective_dt IS NULL
1281 OR asu.granted_dt <= TRUNC (p_effective_dt));
1282
1283 l_adv_credits c_adv_cp_sum%ROWTYPE;
1284 l_adv_per_credits c_adv_cp_per%ROWTYPE;
1285 l_enrolled_cp NUMBER;
1286 l_chk_exists NUMBER := 0;
1287 l_credits NUMBER := 0;
1288 l_appr_credits NUMBER := 0;
1289 l_grant_credits NUMBER := 0;
1290 l_gr_exists BOOLEAN := FALSE;
1291 l_appr_exists BOOLEAN := FALSE;
1292 BEGIN
1293 OPEN c_enrolled_cp;
1294 FETCH c_enrolled_cp INTO l_enrolled_cp;
1295 CLOSE c_enrolled_cp;
1296 OPEN c_adv_cp_sum;
1297
1298 LOOP
1299 FETCH c_adv_cp_sum INTO l_adv_credits;
1300 EXIT WHEN c_adv_cp_sum%NOTFOUND;
1301 l_chk_exists := l_chk_exists + 1;
1302 l_credits := l_credits + NVL (l_adv_credits.advance_standing_credits, 0);
1303
1304 IF l_adv_credits.s_adv_stnd_granting_status = 'APPROVED' THEN
1305 l_appr_credits :=
1306 l_appr_credits + NVL (
1307 l_adv_credits.advance_standing_credits,
1308 0
1309 );
1310 l_appr_exists := TRUE;
1311 ELSIF p_s_adv_stnd_granting_status = 'GRANTED' THEN
1312 l_grant_credits :=
1313 l_grant_credits + NVL (
1314 l_adv_credits.advance_standing_credits,
1315 0
1316 );
1317 l_gr_exists := TRUE;
1318 END IF;
1319 END LOOP;
1320
1321 CLOSE c_adv_cp_sum;
1322
1323 IF l_chk_exists = 0 THEN
1324 RETURN 'FALSE';
1325 ELSIF l_credits >= l_enrolled_cp THEN
1326 RETURN 'TRUE';
1327 ELSE
1328 OPEN c_adv_cp_per;
1329 FETCH c_adv_cp_per INTO l_adv_per_credits;
1330
1331 IF c_adv_cp_per%NOTFOUND THEN
1332 CLOSE c_adv_cp_per;
1333 RETURN 'FALSE';
1334 ELSIF (p_s_adv_stnd_granting_status =
1335 l_adv_per_credits.s_adv_stnd_granting_status
1336 )
1337 OR (p_s_adv_stnd_granting_status = 'BOTH') THEN
1338 CLOSE c_adv_cp_per;
1339 RETURN 'TRUE';
1340 ELSE
1341 CLOSE c_adv_cp_per;
1342 RETURN 'FALSE';
1343 END IF;
1344 END IF;
1345 END granted_adv_standing;
1346 --
1347 --
1348 --
1349 FUNCTION adv_credit_pts (
1350 p_person_id IN NUMBER,
1351 p_asu_course_cd IN VARCHAR2,
1352 p_asu_version_number IN NUMBER,
1353 p_unit_cd IN VARCHAR2,
1354 p_version_number IN NUMBER,
1355 p_s_adv_stnd_granting_status IN VARCHAR2,
1356 p_effective_dt IN DATE,
1357 p_cr_points OUT NOCOPY NUMBER,
1358 p_adv_grant_status OUT NOCOPY VARCHAR2,
1359 p_msg OUT NOCOPY VARCHAR2
1360 )
1361 RETURN BOOLEAN IS
1362 CURSOR c_enrolled_cp IS
1363 SELECT NVL (achievable_credit_points, enrolled_credit_points) enrolled_cp
1364 FROM igs_ps_unit_ver
1365 WHERE unit_cd = p_unit_cd
1366 AND (version_number = p_version_number
1367 OR p_version_number IS NULL
1368 );
1369
1370 CURSOR c_adv_cp_sum IS
1371 SELECT DECODE (
1372 p_s_adv_stnd_granting_status,
1373 s_adv_stnd_granting_status, NVL (asu.achievable_credit_points, 0),
1374 'BOTH', NVL (asu.achievable_credit_points, 0),
1375 0
1376 ) advance_standing_credits,
1377 asu.unit_cd,
1378 asu.version_number,
1379 s_adv_stnd_granting_status
1380 FROM igs_av_stnd_unit asu
1381 WHERE asu.person_id = p_person_id
1382 AND asu.as_course_cd = p_asu_course_cd
1383 AND asu.as_version_number = p_asu_version_number
1384 AND asu.unit_cd = p_unit_cd
1385 AND (asu.version_number = p_version_number
1386 OR p_version_number IS NULL
1387 )
1388 AND asu.s_adv_stnd_granting_status IN ('GRANTED', 'APPROVED')
1389 AND asu.s_adv_stnd_recognition_type = 'CREDIT'
1390 AND (p_effective_dt IS NULL
1391 OR asu.granted_dt <= TRUNC (p_effective_dt)
1392 );
1393
1394 CURSOR c_adv_cp_per IS
1395 SELECT s_adv_stnd_granting_status
1396 FROM igs_av_stnd_unit asu
1397 WHERE asu.person_id = p_person_id
1398 AND asu.as_course_cd = p_asu_course_cd
1399 AND as_version_number = p_asu_version_number
1400 AND asu.unit_cd = p_unit_cd
1401 AND (asu.version_number = p_version_number
1402 OR p_version_number IS NULL
1403 )
1404 AND asu.s_adv_stnd_granting_status IN ('GRANTED', 'APPROVED')
1405 AND asu.s_adv_stnd_recognition_type = 'CREDIT'
1406 AND p_effective_dt IS NULL
1407 OR asu.granted_dt <= TRUNC (p_effective_dt)
1408 /* AND credit_percentage = 100 */;
1409
1410 l_adv_credits c_adv_cp_sum%ROWTYPE;
1411 l_adv_per_credits c_adv_cp_per%ROWTYPE;
1412 l_enrolled_cp NUMBER;
1413 l_chk_exists NUMBER := 0;
1414 l_credits NUMBER := 0;
1415 l_appr_credits NUMBER := 0;
1416 l_grant_credits NUMBER := 0;
1417 l_gr_exists BOOLEAN := FALSE;
1418 l_appr_exists BOOLEAN := FALSE;
1419 BEGIN
1420 OPEN c_enrolled_cp;
1421 FETCH c_enrolled_cp INTO l_enrolled_cp;
1422 CLOSE c_enrolled_cp;
1423 OPEN c_adv_cp_sum;
1424
1425 LOOP
1426 FETCH c_adv_cp_sum INTO l_adv_credits;
1427 EXIT WHEN c_adv_cp_sum%NOTFOUND;
1428 l_chk_exists := l_chk_exists + 1;
1429 l_credits := l_credits + NVL (l_adv_credits.advance_standing_credits, 0);
1430
1431 IF l_adv_credits.s_adv_stnd_granting_status = 'APPROVED' THEN
1432 l_appr_credits :=
1433 l_appr_credits + NVL (
1434 l_adv_credits.advance_standing_credits,
1435 0
1436 );
1437 l_appr_exists := TRUE;
1438 ELSIF l_adv_credits.s_adv_stnd_granting_status = 'GRANTED' THEN
1439 l_grant_credits :=
1440 l_grant_credits + NVL (
1441 l_adv_credits.advance_standing_credits,
1442 0
1443 );
1444 l_gr_exists := TRUE;
1445 END IF;
1446 END LOOP;
1447
1448 CLOSE c_adv_cp_sum;
1449
1450 IF l_chk_exists = 0 THEN
1451 p_cr_points := 0;
1452 p_msg := 'IGS_AV_NO_PERSON_UNITS';
1453 RETURN FALSE;
1454 ELSIF l_credits >= l_enrolled_cp THEN
1455 p_cr_points := l_credits;
1456
1457 IF l_appr_exists
1458 AND l_gr_exists THEN
1459 p_adv_grant_status := 'BOTH';
1460 ELSIF l_appr_exists THEN
1461 p_adv_grant_status := 'APPROVED';
1462 ELSIF l_gr_exists THEN
1463 p_adv_grant_status := 'GRANTED';
1464 END IF;
1465
1466 RETURN TRUE;
1467 ELSE
1468 OPEN c_adv_cp_per;
1469 FETCH c_adv_cp_per INTO l_adv_per_credits;
1470
1471 IF c_adv_cp_per%NOTFOUND THEN
1472 CLOSE c_adv_cp_per;
1473 p_cr_points := l_credits; -- if there are no records with 100% then assign credits to calculated credits
1474 RETURN FALSE;
1475 ELSIF (p_s_adv_stnd_granting_status =
1476 l_adv_per_credits.s_adv_stnd_granting_status
1477 )
1478 OR (p_s_adv_stnd_granting_status = 'BOTH') THEN
1479 p_cr_points := l_enrolled_cp;
1480
1481 IF l_adv_per_credits.s_adv_stnd_granting_status = 'APPROVED' THEN
1482 p_adv_grant_status := 'APPROVED';
1483 ELSIF l_adv_per_credits.s_adv_stnd_granting_status = 'GRANTED' THEN
1484 p_adv_grant_status := 'GRANTED';
1485 END IF;
1486
1487 CLOSE c_adv_cp_per;
1488 RETURN TRUE;
1489 ELSE
1490 p_cr_points := l_credits; -- if there are no records with 100% then assign credits to calculated credits
1491 CLOSE c_adv_cp_per;
1492 RETURN FALSE;
1493 END IF;
1494 END IF;
1495 END adv_credit_pts;
1496 END igs_av_val_asu;
[Home] [Help]