[Home] [Help]
PACKAGE BODY: APPS.IGS_AV_GEN_001
Source
1 PACKAGE BODY IGS_AV_GEN_001 AS
2 /* $Header: IGSAV01B.pls 120.12 2006/05/04 00:02:26 amanohar ship $ */
3
4
5 /******************************************************************
6 Created By :
7 Date Created By :
8 Purpose :
9 remarks :
10 Change History
11 Who When What
12 svenkata 20-NOV-2002 Modified the call to the function igs_en_val_sua.enrp_val_sua_discont to add value 'N' for the parameter
13 p_legacy. Bug#2661533
14 nalkumar 12-July-2002 Modify the 'ADVP_UPD_AS_GRANT' procedure to grant only the advanced standing which is of 'CREDIT' type.
15 Modified the 'ADVP_UPD_AS_PE_EXPRY' procedure to expire only the Advanced Standing which is not of 'PRECLUSION' type.
16 this is as per Bug# 2441175.
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 nalkumar 28-May-2002 Bug# 2382566. Added the call to the repeat logic.
20 kdande 20-Mar-2002 Bug # 2241710. Changed all references of 'IGS_PS_UNIT ' to 'UNIT ' and
21 'IGS_PS_UNIT LEVEL' to 'UNIT LEVEL'
22 nalkumar 04-Mar-2002 Modified the advp_upd_as_pe_grant procedure to fix the Bug# 2121621
23 prraj 21-Feb-2002 Added column QUAL_DETS_ID to the tbh calls of pkg
24 IGS_AV_STND_UNIT_LVL_PKG (Bug# 2233334)
25 pmarada 27-Nov-2001 Added the AV_STND_UNIT_ID column in igs_av_stnd_unit_pkg and
26 AV_STND_UNIT_LVL_ID column in igs_av_stnd_unit_lvl_pkg.
27 sarakshi 21-SEP-2001 Removes all logic of deleting/updating the units enrollemnts due to advance standing
28 records processing.Also changes the percentage logic as mentioned in the dld Acedemic
29 Records Maintanence Build(bug no:1960126)
30 knaraset 02-May-03 Modified the function advp_upd_sua_advstnd to pass uoo_id to internal function enrpl_delete_sua_recs
31 as part of MUS build bug 2829262
32 rvivekan 09-sep-2003 Modified the behaviour of repeatable_ind column in igs_ps_unit_ver table. PSP integration build #3052433
33 stutta 27-Oct-2003 Modified funcion advp_upd_sua_advstnd by removing calls to functions IGS_EN_VAL_SUA.enrp_val_sca_supunit,
34 IGS_EN_VAL_SUA.enrp_val_sca_subunit as part of build #3052438
35 nalkumar 10-Dec-2003 Bug# 3270446 RECR50 Build; Obsoleted the IGS_AV_STND_UNIT.CREDIT_PERCENTAGE column.
36 swaghmar 15-Jun-2005 Bug# 4377816. Changed the cursor queries to pick party_number from igs_pe_hz_parties instead of hz_parties
37 sgurusam 17-Jun-2005 Modified to pass aditional parameter p_calling_obj = 'JOB' in the calls to
38 igs_en_elgbl_unit.eval_unit_repeat
39 jhanda 10-july-05 Build 4327991 BUILD FOR RE105 TRANSFER EVALUATION UI ENHANCEMENTS
40
41 amanohar 23-Nov-2005 Bug#4726833 IGSQUKRM:ADVANCED STANDING CREDIT POINTS NOT SUMMUING IN YOP MODE VAH PAGE
42 sepalani 21-Mar-2006 Bug#5104563 12A-M1R: INFO QUERY ON ADV STANDING GRANTING REPORT
43
44 *********************************************************************************************************/
45
46 FUNCTION advp_del_adv_stnd(
47 p_person_id IN NUMBER ,
48 p_course_cd IN VARCHAR2 ,
49 p_message_name OUT NOCOPY VARCHAR2,
50 p_default_message OUT NOCOPY VARCHAR2 )
51 RETURN BOOLEAN IS
52 gv_other_detail VARCHAR2(255);
53 BEGIN -- advp_del_adv_stnd
54 -- Delete advanced standing details for a student course attempt
55 DECLARE
56 cst_approved CONSTANT VARCHAR2(30) := 'APPROVED';
57 e_resource_busy EXCEPTION;
58 PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
59 v_dummy VARCHAR2(1);
60 v_error_number NUMBER;
61 v_constraint user_constraints.constraint_name%TYPE;
62 v_ri_check_failed BOOLEAN DEFAULT FALSE;
63 CURSOR c_asu IS
64 SELECT 'X'
65 FROM IGS_AV_STND_UNIT asu
66 WHERE asu.person_id = p_person_id AND
67 asu.as_course_cd = p_course_cd AND
68 asu.s_adv_stnd_granting_status <> cst_approved;
69 CURSOR c_asul IS
70 SELECT 'X'
71 FROM IGS_AV_STND_UNIT_LVL asul
72 WHERE asul.person_id = p_person_id AND
73 asul.as_course_cd = p_course_cd AND
74 asul.s_adv_stnd_granting_status <> cst_approved;
75
76 CURSOR c_unit_all IS
77 SELECT rowid,av_stnd_unit_id
78 FROM IGS_AV_STND_UNIT_ALL asua
79 WHERE asua.person_id = p_person_id AND
80 asua.as_course_cd = p_course_cd ;
81
82 CURSOR c_adv_all IS
83 SELECT rowid
84 FROM IGS_AV_ADV_STANDING_ALL asal
85 WHERE asal.person_id = p_person_id AND
86 asal.course_cd = p_course_cd;
87
88 CURSOR c_unit_lvl_all IS
89 SELECT rowid,av_stnd_unit_lvl_id
90 FROM IGS_AV_STND_UNIT_LVL_ALL aslvl
91 WHERE aslvl.person_id = p_person_id AND
92 aslvl.as_course_cd = p_course_cd;
93
94 CURSOR c_unit_basis_all(cp_unit_id IGS_AV_STD_UNT_BASIS_ALL.av_stnd_unit_id%TYPE) IS
95 SELECT rowid
96 FROM IGS_AV_STD_UNT_BASIS_ALL asba
97 WHERE asba.av_stnd_unit_id = cp_unit_id;
98
99 CURSOR c_ulvlbasis_all(cp_unit_lvl_id IGS_AV_STD_ULVLBASIS_ALL.av_stnd_unit_lvl_id%TYPE) IS
100 SELECT rowid
101 FROM IGS_AV_STD_ULVLBASIS_ALL asbl
102 WHERE asbl.av_stnd_unit_lvl_id = cp_unit_lvl_id;
103
104 CURSOR c_alt_unt_all(cp_alt_unit_id IGS_AV_STND_ALT_UNIT.av_stnd_unit_id%TYPE) IS
105 SELECT rowid
106 FROM IGS_AV_STND_ALT_UNIT asau
107 WHERE asau.av_stnd_unit_id = cp_alt_unit_id;
108
109
110 BEGIN
111 p_default_message := NULL;
112 -- Check if the advanced standing can be deleted (Can only delete granting
113 -- status of 'APPROVED')
114 OPEN c_asu;
115 FETCH c_asu INTO v_dummy;
116 IF c_asu%FOUND THEN
117 CLOSE c_asu;
118 p_message_name := 'IGS_AV_CANNOT_DELETE';
119 RETURN FALSE;
120 END IF;
121 CLOSE c_asu;
122 OPEN c_asul;
123 FETCH c_asul INTO v_dummy;
124 IF c_asul%FOUND THEN
125 CLOSE c_asul;
126 p_message_name := 'IGS_AV_CANNOT_DELETE';
127 RETURN FALSE;
128 END IF;
129 CLOSE c_asul;
130
131
132
133 FOR v_unit_all IN c_unit_all
134 LOOP
135
136 FOR v_unit_basis_all IN c_unit_basis_all(v_unit_all.av_stnd_unit_id)
137 LOOP
138 igs_av_std_unt_basis_pkg.DELETE_ROW (
139 X_ROWID => v_unit_basis_all.rowid );
140 END LOOP;
141
142 IF (fnd_log.level_statement >=FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
143 fnd_log.string( fnd_log.level_statement, 'igs.plsql.IGS_AV_GEN_001.advp_del_adv_stnd','Deleted unit basis for'||v_unit_all.av_stnd_unit_id );
144 END IF;
145
146 FOR v_alt_unt_all IN c_alt_unt_all(v_unit_all.av_stnd_unit_id)
147 LOOP
148 igs_av_stnd_alt_unit_pkg.DELETE_ROW (
149 X_ROWID => v_alt_unt_all.rowid );
150 END LOOP;
151
152
153 igs_av_stnd_unit_pkg.DELETE_ROW (
154 X_ROWID => v_unit_all.rowid );
155
156
157 IF (fnd_log.level_statement >=FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
158 fnd_log.string( fnd_log.level_statement, 'igs.plsql.IGS_AV_GEN_001.advp_del_adv_stnd','Deleted unit');
159 END IF;
160
161 END LOOP;
162
163
164 FOR v_unit_lvl_all IN c_unit_lvl_all
165 LOOP
166
167 FOR v_ulvlbasis_all IN c_ulvlbasis_all(v_unit_lvl_all.av_stnd_unit_lvl_id)
168 LOOP
169 igs_av_std_ulvlbasis_pkg.DELETE_ROW (
170 X_ROWID => v_ulvlbasis_all.rowid );
171 END LOOP;
172
173 IF (fnd_log.level_statement >=FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
174 fnd_log.string( fnd_log.level_statement, 'igs.plsql.IGS_AV_GEN_001.advp_del_adv_stnd','Deleted unit level basis'||v_unit_lvl_all.av_stnd_unit_lvl_id);
175 END IF;
176
177 igs_av_stnd_unit_lvl_pkg.DELETE_ROW (
178 X_ROWID => v_unit_lvl_all.rowid );
179
180
181 IF (fnd_log.level_statement >=FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
182 fnd_log.string( fnd_log.level_statement, 'igs.plsql.IGS_AV_GEN_001.advp_del_adv_stnd','Deleted unit level');
183 END IF;
184
185 END LOOP;
186
187 FOR v_adv_all IN c_adv_all
188 LOOP
189 igs_av_adv_standing_pkg.DELETE_ROW (
190 X_ROWID => v_adv_all.rowid );
191
192 IF (fnd_log.level_statement >=FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
193 fnd_log.string( fnd_log.level_statement, 'igs.plsql.IGS_AV_GEN_001.advp_del_adv_stnd','Deleted from IGS_AV_ADV_STANDING_ALL');
194 END IF;
195
196 END LOOP;
197
198 p_message_name := NULL;
199 p_default_message := NULL;
200 RETURN TRUE;
201
202 EXCEPTION
203 WHEN e_resource_busy THEN
204 RETURN FALSE;
205 WHEN OTHERS THEN
206 IF c_asu%ISOPEN THEN
207 CLOSE c_asu;
208 END IF;
209 IF c_asul%ISOPEN THEN
210 CLOSE c_asul;
211 END IF;
212 IF c_unit_all%ISOPEN THEN
213 CLOSE c_unit_all;
214 END IF;
215 IF c_unit_basis_all%ISOPEN THEN
216 CLOSE c_unit_basis_all;
217 END IF;
218 IF c_ulvlbasis_all%ISOPEN THEN
219 CLOSE c_ulvlbasis_all;
220 END IF;
221 IF c_unit_lvl_all%ISOPEN THEN
222 CLOSE c_unit_lvl_all;
223 END IF;
224 IF c_adv_all%ISOPEN THEN
225 CLOSE c_adv_all;
226 END IF;
227 RAISE;
228 END;
229 EXCEPTION
230 WHEN OTHERS THEN
231 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
232 Fnd_Message.Set_Token('NAME','IGS_AV_GEN_001.ADVP_DEL_ADV_STND');
233 Igs_Ge_Msg_Stack.Add;
234 App_Exception.Raise_Exception;
235 END advp_del_adv_stnd;
236
237 FUNCTION adv_credit_pts(p_person_id IN NUMBER,
238 p_course_cd IN VARCHAR2 ,
239 p_effective_dt IN DATE) RETURN NUMBER IS
240
241 CURSOR c_adv_cp_sum IS
242 SELECT SUM(NVL(asu.achievable_credit_points,0)) advance_standing_credits,
243 SUM(NVL(puv.achievable_Credit_points,puv.enrolled_Credit_points)) enrolled_cp ,
244 asu.unit_cd,asu.version_number
245 FROM igs_av_stnd_unit asu,igs_ps_unit_ver puv WHERE
246 asu.person_id = p_person_id AND
247 asu.as_course_cd = p_course_cd AND
248 puv.unit_cd = asu.unit_cd AND
249 puv.version_number = asu.version_number AND
250 asu.s_adv_stnd_granting_status = 'GRANTED' AND
251 asu.s_adv_stnd_recognition_type = 'CREDIT' AND
252 (p_effective_dt IS NULL OR asu.granted_dt <= TRUNC(p_effective_dt))
253 GROUP BY asu.unit_cd,asu.version_number;
254
255 CURSOR c_adv_cp_per(cp_unit_cd igs_av_stnd_unit.unit_cd%TYPE,
256 cp_version_number igs_av_stnd_unit.version_number%TYPE) IS
257 SELECT NVL(puv.achievable_Credit_points,puv.enrolled_Credit_points) enrolled_credits
258 FROM igs_av_stnd_unit asu,igs_ps_unit_ver puv
259 WHERE asu.person_id = p_person_id
260 AND asu.as_course_cd = p_course_cd
261 AND asu.unit_cd = cp_unit_cd
262 AND asu.version_number = cp_version_number
263 AND asu.s_adv_stnd_granting_status = 'GRANTED'
264 AND asu.s_adv_stnd_recognition_type = 'CREDIT'
265 AND (p_effective_dt IS NULL OR asu.granted_dt <= TRUNC(p_effective_dt))
266 /* AND credit_percentage = 100 */
267 AND puv.unit_cd = asu.unit_cd
268 AND puv.version_number = asu.version_number;
269
270 l_adv_cp_sum c_adv_cp_sum%ROWTYPE;
271 l_adv_cp_per c_adv_cp_per%ROWTYPE;
272 l_total_cp NUMBER;
273
274 BEGIN
275 l_total_cp := 0;
276 OPEN c_adv_cp_sum;
277 LOOP
278 FETCH c_adv_cp_sum INTO l_adv_cp_sum;
279 EXIT WHEN c_adv_cp_sum%NOTFOUND;
280 IF l_adv_cp_sum.advance_standing_credits < l_adv_cp_sum.enrolled_cp THEN
281 OPEN c_adv_cp_per(l_adv_cp_sum.unit_cd,l_adv_cp_sum.version_number);
282 FETCH c_adv_cp_per INTO l_adv_cp_per;
283 IF c_adv_cp_per%FOUND THEN
284 l_total_cp := l_total_cp + l_adv_cp_per.enrolled_credits;
285 END IF;
286 CLOSE c_adv_cp_per;
287 ELSE
288 l_total_cp := l_total_cp + l_adv_cp_sum.advance_standing_credits;
289 END IF;
290 END LOOP;
291 CLOSE c_adv_cp_sum;
292 RETURN l_total_cp;
293
294 END adv_credit_pts;
295
296
297 FUNCTION advp_get_as_total(
298 p_person_id IN NUMBER ,
299 p_course_cd IN VARCHAR2 ,
300 p_effective_dt IN DATE )
301 RETURN NUMBER IS
302 gv_other_detail VARCHAR2(255);
303 BEGIN -- advp_get_as_total
304 DECLARE
305 v_adv_total NUMBER;
306 v_asu_uv_sum NUMBER;
307 v_asul_sum NUMBER;
308 cst_granted CONSTANT IGS_AV_STND_UNIT.s_adv_stnd_granting_status%TYPE := 'GRANTED';
309
310 CURSOR c_asul IS
311 SELECT SUM(credit_points)
312 FROM IGS_AV_STND_UNIT_LVL asul
313 WHERE asul.person_id = p_person_id AND
314 asul.as_course_cd = p_course_cd AND
315 asul.s_adv_stnd_granting_status = cst_granted AND
316 (p_effective_dt IS NULL OR
317 asul.granted_dt <= TRUNC(p_effective_dt));
318 BEGIN
319 -- Set the default message number
320 v_adv_total := 0.00;
321 v_adv_total := ADV_CREDIT_PTS(p_person_id,p_course_cd,p_effective_dt );
322 OPEN c_asul;
323 FETCH c_asul INTO v_asul_sum;
324 IF c_asul%FOUND AND v_asul_sum IS NOT NULL THEN
325 v_adv_total := (v_adv_total + v_asul_sum);
326 END IF;
327 CLOSE c_asul;
328 -- Return the default value
329 RETURN v_adv_total;
330 EXCEPTION
331 WHEN OTHERS THEN
332
333 IF c_asul%ISOPEN THEN
334 CLOSE c_asul;
335 END IF;
336 RAISE;
337 END;
338 END advp_get_as_total;
339
340 PROCEDURE adv_validate_grade (p_grdschcode IN VARCHAR2,p_grde IN VARCHAR2,p_grschverno IN NUMBER,validity OUT NOCOPY VARCHAR2)
341 IS
342 v_valid_grades NUMBER(2);
343
344 CURSOR c_validate_grade_cur(grschcd VARCHAR2,grschvno NUMBER,grd VARCHAR2)
345 IS
346 SELECT COUNT (rowid)
347 FROM igs_as_grd_sch_grade
348 WHERE grading_schema_cd = grschcd
349 AND version_number = grschvno
350 AND grade = grd;
351
352 BEGIN
353
354 OPEN c_validate_grade_cur(p_grdschcode,p_grschverno,p_grde);
355
356 FETCH c_validate_grade_cur INTO v_valid_grades;
357
358 IF (v_valid_grades = 1)
359 THEN
360 validity := 'VALID';
361 ELSE
362 validity := 'INVALID';
363 END IF;
364
365 CLOSE c_validate_grade_cur;
366
367 END adv_validate_grade;
368
369
370 PROCEDURE advp_upd_as_grant(
371 errbuf OUT NOCOPY VARCHAR2,
372 retcode OUT NOCOPY NUMBER,
373 p_org_id IN NUMBER )
374 IS
375 -- This procedure will get all eligible persons and process them for
376 -- advance standing
377 v_other_details VARCHAR2(255);
378 BEGIN
379 -- To set org_id as in request of job.
380 -- This is added to fix Bug no# 1635976.
381 IGS_GE_GEN_003.set_org_id(p_org_id);
382
383 DECLARE
384 v_ret_value BOOLEAN;
385 v_s_log_type IGS_GE_S_LOG.s_log_type%TYPE DEFAULT NULL;
386 v_creation_dt IGS_GE_S_LOG.creation_dt%TYPE DEFAULT NULL;
387 cst_approved CONSTANT IGS_AV_STND_UNIT.s_adv_stnd_granting_status%TYPE := 'APPROVED';
388 cst_credit CONSTANT IGS_AV_STND_UNIT.s_adv_stnd_recognition_type%TYPE := 'CREDIT';
389 v_message_name VARCHAR2(30) DEFAULT NULL ;
390 -- Counters
391 tot_rec_process NUMBER DEFAULT 0;
392 v_ret_false NUMBER DEFAULT 0;
393 V_MESSAGE1 VARCHAR2(50);
394 V_MESSAGE2 VARCHAR2(50);
395 V_MESSAGE3 VARCHAR2(50);
396 CURSOR c_adv_stnd_unit IS
397 SELECT person_id
398 FROM IGS_AV_STND_UNIT_ALL
399 WHERE s_adv_stnd_granting_status = cst_approved AND
400 s_adv_stnd_recognition_type = cst_credit
401 UNION
402 SELECT person_id
403 FROM IGS_AV_STND_UNIT_LVL_ALL
404 WHERE s_adv_stnd_granting_status = cst_approved;
405 BEGIN
406 FOR v_adv_stnd_unit IN c_adv_stnd_unit LOOP
407 IF NOT advp_upd_as_pe_grant(
408 v_adv_stnd_unit.person_id,
409 NULL,
410 NULL,
411 SYSDATE,
412 'ALL',
413 v_s_log_type,
414 v_creation_dt,
415 v_message_name) THEN
416 v_ret_false := v_ret_false + 1;
417
418 V_MESSAGE1 := FND_MESSAGE.GET_STRING ('IGS','IGS_AV_STAND_NOT_GRANT')||TO_CHAR(v_adv_stnd_unit.person_id);
419 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING ('IGS',V_MESSAGE1));
420 END IF;
421 IF v_message_name IS NOT NULL AND v_message_name NOT IN ('IGS_AV_HAS_UNIT_ATT') THEN
422 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING ('IGS',V_MESSAGE_NAME));
423 END IF;
424 tot_rec_process := tot_rec_process + 1;
425 END LOOP;
426 IF tot_rec_process = 0 THEN
427 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING ('IGS','IGS_AV_NO_AV_STAND_PRS'));
428 ELSE
429 V_MESSAGE2 := FND_MESSAGE.GET_STRING ('IGS','IGS_GE_TOTAL_REC_PROCESSED')||TO_CHAR(tot_rec_process) ;
430 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING ('IGS',V_MESSAGE2));
431 V_MESSAGE3 := FND_MESSAGE.GET_STRING ('IGS','IGS_GE_TOTAL_REC_FAILED')||TO_CHAR(v_ret_false) ;
432 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING ('IGS',V_MESSAGE3));
433 END IF;
434 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING ('IGS','IGS_AV_STAND_PRS_SUCCESS'));
435 END;
436 EXCEPTION
437 WHEN OTHERS THEN
438 RETCODE:=2;
439 ERRBUF:=FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
440 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
441 END advp_upd_as_grant;
442
443 FUNCTION advp_upd_as_inst(
444 p_person_id IN NUMBER ,
445 p_course_cd IN VARCHAR2 ,
446 p_version_number IN NUMBER ,
447 p_message_name OUT NOCOPY VARCHAR2)
448 RETURN BOOLEAN IS
449 gv_other_detail VARCHAR2(255);
450 BEGIN -- advp_upd_as_inst
451 -- Determine the institution which has contributed the majority
452 -- of the student's granted advanced standing. It then updates
453 -- the advanced standing exemption institution.
454 DECLARE
455 e_resource_busy EXCEPTION;
456 PRAGMA EXCEPTION_INIT (e_resource_busy, -54);
457 v_major_exmpt_inst VARCHAR2(64);
458 v_exemption_institution_cd IGS_AV_STND_UNIT_CREDIT_V.exemption_institution_cd%TYPE;
459 v_max_credit NUMBER(5);
460 v_check CHAR;
461 v_advanced_standing IGS_AV_ADV_STANDING%ROWTYPE;
462 CURSOR c_ascv IS
463 SELECT suc.exemption_institution_cd,
464 SUM (suc.credit)
465 FROM (SELECT asu.person_id person_id,
466 asu.as_course_cd course_cd,
467 asu.as_version_number version_number,
468 asu.exemption_institution_cd exemption_institution_cd,
469 uv.achievable_credit_points credit
470 FROM igs_av_stnd_unit_all asu,
471 igs_ps_unit_ver_all uv
472 WHERE asu.unit_cd = uv.unit_cd
473 AND asu.version_number = uv.version_number
474 AND asu.s_adv_stnd_recognition_type = 'CREDIT'
475 AND asu.s_adv_stnd_granting_status = 'GRANTED'
476 UNION ALL
477 SELECT asule.person_id,
478 asule.as_course_cd,
479 asule.as_version_number,
480 asule.exemption_institution_cd,
481 asule.credit_points credit
482 FROM igs_av_stnd_unit_lvl_all asule
483 WHERE asule.s_adv_stnd_granting_status = 'GRANTED') suc
484 WHERE suc.person_id = p_person_id
485 AND suc.course_cd = p_course_cd
486 AND suc.version_number = p_version_number
487 GROUP BY suc.exemption_institution_cd
488 ORDER BY SUM (suc.credit) DESC;
489 CURSOR c_exempt_inst_v (
490 cp_exemption_institution_cd igs_pe_hz_parties.inst_org_ind%TYPE) IS
491 SELECT 'x'
492 FROM igs_pe_hz_parties ihp
493 where ihp.inst_org_ind = 'I'
494 AND ihp.oi_govt_institution_cd IS NOT NULL
495 AND ihp.oss_org_unit_cd = cp_exemption_institution_cd
496 UNION ALL
497 SELECT 'x'
498 FROM igs_lookup_values lk
499 WHERE lk.lookup_type = 'OR_INST_EXEMPTIONS'
500 AND lk.enabled_flag = 'Y'
501 AND lk.lookup_code = cp_exemption_institution_cd;
502 CURSOR c_institution (
503 cp_exemption_institution_cd IGS_OR_INSTITUTION.institution_cd%TYPE) IS
504 SELECT 'x'
505 FROM hz_parties hp,
506 igs_pe_hz_parties ihp
507 WHERE ihp.oss_org_unit_cd = cp_exemption_institution_cd --swaghmar bug# 4377816
508 AND hp.party_id = ihp.party_id
509 AND ihp.inst_org_ind = 'I'
510 AND ihp.oi_os_ind = 'Y';
511 CURSOR c_advanced_standing IS
512 SELECT *
513 FROM IGS_AV_ADV_STANDING
514 WHERE person_id = p_person_id AND
515 course_cd = p_course_cd AND
516 version_number = p_version_number
517 FOR UPDATE OF exemption_institution_cd NOWAIT;
518 BEGIN
519 p_message_name := NULL;
520 -- Validate input parameters
521 IF ( p_person_id IS NULL OR
522 p_course_cd IS NULL OR
523 p_version_number IS NULL) THEN
524 RETURN TRUE;
525 END IF;
526 -- Determine if person is a commencing student.
527 -- Determine the exemption institution code which has been the source of the
528 -- greatest amount of granted credit.
529 OPEN c_ascv;
530 FETCH c_ascv INTO v_exemption_institution_cd,
531 v_max_credit;
532 IF ((c_ascv%NOTFOUND) OR
533 ((c_ascv%FOUND) AND
534 (v_max_credit IS NULL))) THEN
535 -- No credit granted
536 v_major_exmpt_inst := '1';
537 ELSE
538 -- Map the institution to appropriate DEETYA code for inclusion in Adv_stnd
539 OPEN c_exempt_inst_v (v_exemption_institution_cd);
540 FETCH c_exempt_inst_v INTO v_check;
541 IF (c_exempt_inst_v%FOUND) THEN
542 v_major_exmpt_inst := v_exemption_institution_cd;
543 ELSIF (v_exemption_institution_cd = 'UNKNOWN') THEN
544 v_major_exmpt_inst := '4999';
545 ELSIF (v_exemption_institution_cd = 'NOT INSTN') THEN
546 v_major_exmpt_inst := '8004';
547 ELSE
548 OPEN c_institution (v_exemption_institution_cd);
549 FETCH c_institution INTO v_check;
550 IF (c_institution%FOUND) THEN
551 v_major_exmpt_inst := '8002';
552 ELSE
553 v_major_exmpt_inst := '4999';
554 END IF;
555 CLOSE c_institution;
556 END IF;
557 CLOSE c_exempt_inst_v;
558 END IF; -- c_ascv%NOTFOUND
559 CLOSE c_ascv;
560 OPEN c_advanced_standing;
561 FETCH c_advanced_standing INTO v_advanced_standing;
562 IF (c_advanced_standing%FOUND) THEN
563 UPDATE IGS_AV_ADV_STANDING
564 SET exemption_institution_cd = v_major_exmpt_inst
565 WHERE CURRENT OF c_advanced_standing;
566 END IF;
567 CLOSE c_advanced_standing;
568 RETURN TRUE;
569 EXCEPTION
570 WHEN e_resource_busy THEN
571 IF (c_advanced_standing%ISOPEN) THEN
572 CLOSE c_advanced_standing;
573 END IF;
574 p_message_name := 'IGS_AV_UNABLE_UPD_TOTALS';
575 RETURN FALSE;
576 END;
577 END advp_upd_as_inst;
578
579 FUNCTION advp_upd_as_pe_grant(
580 p_person_id IN NUMBER ,
581 p_course_cd IN VARCHAR2 ,
582 p_version_number IN NUMBER ,
583 p_granted_dt IN DATE ,
584 p_process_type IN VARCHAR2 ,
585 p_s_log_type IN OUT NOCOPY VARCHAR2 ,
586 p_creation_dt IN OUT NOCOPY DATE ,
587 p_message_name OUT NOCOPY VARCHAR2 )
588 RETURN BOOLEAN IS
589 gv_other_detail VARCHAR2(255);
590 lv_param_values VARCHAR2(1080);
591 BEGIN -- advp_upd_as_pe_grant
592 -- Grant approved advance standing for a person/course and
593 -- impacts the student's enrolment if necessary.
594 --
595 -- kdande 20-Mar-2002.
596 -- Bug # 2241710. Changed all references of 'IGS_PS_UNIT ' to 'UNIT ' and
597 -- 'IGS_PS_UNIT LEVEL' to 'UNIT LEVEL'
598 --
599
600 --
601 -- sepalani - 22-Mar-2006 Bug # 5104563 12A-M1R : INFO QUERY ON ADV STANDING GRANTING REPORT
602 -- logic changed for "repeat set to none" unit codes.
603 --
604 DECLARE
605 cst_adv_stnd_grant CONSTANT VARCHAR2(10) := 'ADV-GRANT';
606 cst_credit CONSTANT VARCHAR2(10) := 'CREDIT';
607 cst_course CONSTANT VARCHAR2(10) := 'COURSE';
608 cst_person CONSTANT VARCHAR2(10) := 'PERSON';
609 cst_all CONSTANT VARCHAR2(10) := 'ALL';
610 cst_approved CONSTANT VARCHAR2(10) := 'APPROVED';
611 cst_granted CONSTANT VARCHAR2(30) := 'GRANTED';
612 cst_granted_ge IGS_LOOKUPS_VIEW.lookup_code%TYPE;
613 v_total_exmptn_approved NUMBER(5);
614 v_total_exmptn_granted NUMBER(5);
615 v_total_exmptn_perc_grntd NUMBER(5);
616 v_granted_dt DATE;
617 v_check CHAR;
618 v_key IGS_GE_S_LOG.key%TYPE;
619 v_s_log_type IGS_GE_S_LOG.s_log_type%TYPE;
620 v_creation_dt IGS_GE_S_LOG.creation_dt%TYPE;
621 v_skip_course_cd IGS_AV_STND_UNIT.as_course_cd%TYPE DEFAULT 'ISNULL';
622 v_skip_course_cd1 IGS_AV_STND_UNIT.as_course_cd%TYPE DEFAULT 'ISNULL';
623 v_skip_version_number IGS_AV_STND_UNIT.as_version_number%TYPE DEFAULT 0;
624 v_last_course_cd IGS_AV_STND_UNIT.as_course_cd%TYPE DEFAULT 'ISNULL';
625 v_last_version_number IGS_AV_STND_UNIT.as_version_number%TYPE DEFAULT 0;
626 v_message_name VARCHAR2(30) DEFAULT NULL;
627 v_message_key VARCHAR2(255);
628 v_update_flag BOOLEAN DEFAULT TRUE;
629
630 CURSOR c_sl (
631 cp_s_log_type IGS_GE_S_LOG.s_log_type%TYPE,
632 cp_creation_dt IGS_GE_S_LOG.creation_dt%TYPE) IS
633 SELECT 'x'
634 FROM IGS_GE_S_LOG
635 WHERE s_log_type = cp_s_log_type AND
636 creation_dt = cp_creation_dt;
637
638 CURSOR c_asu (cp_person_id IGS_PE_PERSON.person_id%TYPE) IS
639 SELECT *
640 FROM IGS_AV_STND_UNIT
641 WHERE
642 s_adv_stnd_granting_status = cst_approved AND
643 s_adv_stnd_recognition_type = cst_credit AND
644 person_id = cp_person_id
645 ORDER BY
646 person_id,
647 as_course_cd,
648 as_version_number,
649 approved_dt desc,
650 granted_dt desc;
651 -- FOR UPDATE NOWAIT;
652
653 CURSOR c_asul (cp_person_id IGS_PE_PERSON.person_id%TYPE) IS
654 SELECT *
655 FROM IGS_AV_STND_UNIT_LVL
656 WHERE
657 s_adv_stnd_granting_status = cst_approved AND
658 person_id = cp_person_id
659 ORDER BY
660 person_id,
661 as_course_cd,
662 as_version_number,
663 approved_dt desc,
664 granted_dt desc
665 FOR UPDATE NOWAIT;
666 BEGIN
667 p_message_name := NULL;
668 -- Validate input parameters
669 IF (p_process_type IN (cst_all, cst_person, cst_course)) THEN
670 IF (p_person_id IS NULL) OR
671 (p_process_type = cst_course AND
672 (p_course_cd IS NULL OR p_version_number IS NULL)) THEN
673 p_message_name := 'IGS_AV_NOT_DTRMINE_INSUF_INFO';
674 RETURN FALSE;
675 ELSE
676 NULL; -- do nothing, continue processing
677 END IF;
678 ELSE
679 p_message_name := 'IGS_AV_NOT_DTRMINE_INSUF_INFO';
680 RETURN FALSE;
681 END IF;
682 IF (p_granted_dt IS NULL) THEN
683 v_granted_dt := SYSDATE;
684 ELSE
685 v_granted_dt := p_granted_dt;
686 END IF;
687 -- Insert Advanced standing granting process into system logging if it
688 -- doesn't already exist
689 OPEN c_sl(
690 p_s_log_type,
691 p_creation_dt);
692 FETCH c_sl INTO v_check;
693 IF c_sl%NOTFOUND THEN
694 v_key := NULL;
695 v_s_log_type := cst_adv_stnd_grant;
696 IF p_process_type IN (cst_person, cst_course) THEN
697 v_key := p_person_id;
698 END IF;
699 IF (p_process_type = cst_course) THEN
700 v_key := v_key|| '|' || p_course_cd || '|' || p_version_number;
701 END IF;
702 IGS_GE_GEN_003.GENP_INS_LOG(
703 v_s_log_type,
704 v_key,
705 v_creation_dt);
706 -- Set parameters
707 p_s_log_type := v_s_log_type;
708 p_creation_dt := v_creation_dt;
709 ELSE
710 v_s_log_type := p_s_log_type;
711 v_creation_dt := p_creation_dt;
712 END IF;
713 CLOSE c_sl;
714 -- Update approved IGS_AV_STND_UNIT
715 p_message_name := 'IGS_AV_UNIT_UPD_ANOTHER_PRC';
716
717
718 FOR v_asu_rec IN c_asu(p_person_id) LOOP
719 IF (p_process_type = cst_course AND
720 (v_asu_rec.as_course_cd <> p_course_cd OR
721 v_asu_rec.as_version_number <> p_version_number)) OR
722 (v_asu_rec.as_course_cd = v_skip_course_cd) OR
723 (v_asu_rec.as_course_cd = v_skip_course_cd1 AND
724 v_asu_rec.as_version_number = v_skip_version_number)
725 THEN
726 NULL; -- do nothing, continue IGS_AV_STND_UNIT
727 ELSE
728 -- For each IGS_AV_STND_UNIT.as_course_cd
729 -- Validate that person/course is not excluded from advanced standing
730 IF (IGS_EN_VAL_ENCMB.enrp_val_excld_prsn(
731 v_asu_rec.person_id,
732 v_asu_rec.as_course_cd,
733 v_granted_dt,
734 v_message_name) = FALSE) THEN
735 -- Insert into messages for reporting
736 -- Do not process any more IGS_AV_STND_UNIT for this course
737 v_message_key := 'UNIT ' || '|' ||
738 TO_CHAR(v_asu_rec.person_id) || '|' ||
739 v_asu_rec.as_course_cd || '|' ||
740 TO_CHAR(v_asu_rec.as_version_number) || '|' ||
741 v_asu_rec.unit_cd || '|' ||
742 TO_CHAR(v_asu_rec.version_number) || '|' ||
743 FND_DATE.DATE_TO_DISPLAYDATE(v_granted_dt);
744 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
745 v_s_log_type,
746 v_creation_dt,
747 v_message_key,
748 v_message_name,
749 '');
750 v_skip_course_cd := v_asu_rec.as_course_cd;
751 GOTO continue;
752 END IF;
753 -- For each IGS_AV_STND_UNIT.as_course_cd/as_version_number
754 -- Validate advanced standing course version
755 IF (v_asu_rec.as_course_cd = v_last_course_cd AND
756 v_asu_rec.as_version_number = v_last_version_number) THEN
757 NULL; -- do nothing, continue IGS_AV_STND_UNIT
758 ELSE
759 v_last_course_cd := v_asu_rec.as_course_cd;
760 v_last_version_number := v_asu_rec.as_version_number;
761 IF (IGS_AV_VAL_ASU.advp_val_as_grant(
762 v_asu_rec.person_id,
763 v_asu_rec.as_course_cd,
764 v_asu_rec.as_version_number,
765 v_asu_rec.s_adv_stnd_granting_status,
766 v_message_name) = FALSE) THEN
767 -- Insert into messages for reporting
768 -- Do not process any more IGS_AV_STND_UNIT for this course version
769 v_message_key := 'UNIT ' || '|' ||
770 TO_CHAR(v_asu_rec.person_id) || '|' ||
771 v_asu_rec.as_course_cd || '|' ||
772 TO_CHAR(v_asu_rec.as_version_number) || '|' ||
773 v_asu_rec.unit_cd || '|' ||
774 TO_CHAR(v_asu_rec.version_number) || '|' ||
775 FND_DATE.DATE_TO_DISPLAYDATE(v_granted_dt);
776 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
777 v_s_log_type,
778 v_creation_dt,
779 v_message_key,
780 v_message_name,
781 '');
782 v_skip_course_cd1 := v_asu_rec.as_course_cd;
783 v_skip_version_number := v_asu_rec.as_version_number;
784 GOTO continue;
785 END IF;
786 END IF;
787 -- Validate course version advanced standing internal/external limits
788 IF (IGS_AV_VAL_ASU.advp_val_as_totals(
789 v_asu_rec.person_id,
790 v_asu_rec.as_course_cd,
791 v_asu_rec.as_version_number,
792 TRUE,
793 v_asu_rec.unit_cd,
794 v_asu_rec.version_number,
795 cst_granted,
796 '', -- IGS_AV_STND_UNIT_LVL.unit_level
797 '', -- IGS_AV_STND_UNIT_LVL.exemption_institution_cd
798 '', -- IGS_AV_STND_UNIT_LVL.s_adv_stnd_granting_status
799 v_total_exmptn_approved,
800 v_total_exmptn_granted,
801 v_total_exmptn_perc_grntd,
802 v_message_name,
803 v_asu_rec.unit_details_id,
804 v_asu_rec.tst_rslt_dtls_id,
805 v_asu_rec.exemption_institution_cd) = FALSE) THEN
806 -- Insert into messages for reporting
807 -- Do not process any more IGS_AV_STND_UNIT for this course
808 v_message_key := 'UNIT ' || '|' ||
809 TO_CHAR(v_asu_rec.person_id) || '|' ||
810 v_asu_rec.as_course_cd || '|' ||
811 TO_CHAR(v_asu_rec.as_version_number) || '|' ||
812 v_asu_rec.unit_cd || '|' ||
813 TO_CHAR(v_asu_rec.version_number) || '|' ||
814 FND_DATE.DATE_TO_DISPLAYDATE(v_granted_dt);
815 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
816 v_s_log_type,
817 v_creation_dt,
818 v_message_key,
819 v_message_name,
820 '');
821 v_skip_course_cd1 := v_asu_rec.as_course_cd;
822 v_skip_version_number := v_asu_rec.as_version_number;
823 GOTO continue;
824 END IF;
825 -- For each IGS_AV_STND_UNIT.unit_cd/version_number
826 -- Update student enrolment
827 IF ( v_asu_rec.s_adv_stnd_recognition_type = cst_credit AND
828 (v_asu_rec.achievable_credit_points > 0 )) THEN
829 v_message_name := NULL;
830 IF (advp_upd_sua_advstnd(
831 v_asu_rec.person_id,
832 v_asu_rec.as_course_cd,
833 v_asu_rec.unit_cd,
834 v_asu_rec.version_number,
835 v_granted_dt,
836 v_message_name) = FALSE) THEN
837 -- Insert into messages for reporting
838 -- Do not update IGS_AV_STND_UNIT
839 v_message_key := 'UNIT ' || '|' ||
840 TO_CHAR(v_asu_rec.person_id) || '|' ||
841 v_asu_rec.as_course_cd || '|' ||
842 TO_CHAR(v_asu_rec.as_version_number) || '|' ||
843 v_asu_rec.unit_cd || '|' ||
844 TO_CHAR(v_asu_rec.version_number) || '|' ||
845 FND_DATE.DATE_TO_DISPLAYDATE(v_granted_dt);
846 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
847 v_s_log_type,
848 v_creation_dt,
849 v_message_key,
850 v_message_name,
851 '');
852 --
853 -- Next IF condition added as pe the bug# 2382566.
854 --
855 IF p_message_name NOT IN ('IGS_AV_HAS_UNIT_ATT') THEN
856 v_update_flag := FALSE;
857 ELSE
858 v_update_flag := TRUE;
859 END IF;
860 END IF;
861 END IF;
862
863 IF (v_update_flag = TRUE) THEN
864 -- Update IGS_AV_STND_UNIT
865 -- *****************************************************************************************
866 DECLARE
867 /* Cursor to select rowid and all columns of the table */
868 CURSOR Cur_IGS_AV_STND_UNIT IS
869 SELECT rowid, IGS_AV_STND_UNIT.*
870 FROM IGS_AV_STND_UNIT
871 WHERE person_id = p_person_id
872 AND as_course_cd = v_asu_rec.as_course_cd
873 AND as_version_number = v_asu_rec.as_version_number
874 AND unit_cd = v_asu_rec.unit_cd
875 AND version_number = v_asu_rec.version_number;
876
877 CURSOR cur_get_person_num IS
878 SELECT party_number
879 FROM hz_parties
880 WHERE party_id = p_person_id;
881 l_cur_get_person_num cur_get_person_num%ROWTYPE;
882 l_message_name fnd_new_messages.message_name%TYPE;
883 l_repeat_tag VARCHAR2(100);
884 BEGIN
885 FOR IGS_AV_STND_UNIT_rec in Cur_IGS_AV_STND_UNIT LOOP
886 /* For the column to be updated, modify the record variable value fetched */
887 IGS_AV_STND_UNIT_rec.granted_dt := v_granted_dt;
888 IGS_AV_STND_UNIT_rec.s_adv_stnd_granting_status := cst_granted;
889 /* Call server side TBH package procedure */
890 --
891 -- To check the repeat logic. Added as per the Bug# 2382566.
892 -- Start of new code.
893
894 --
895 -- sepalani 22-Mar-2006 Bug# 5104563 12A-M1R : INFO QUERY ON ADV STANDING GRANTING REPORT
896 -- "eval_unit_repeat" function returns true, if the unit is repeatable
897 -- it also returns true when the unit has "Repeat set to None" and For Reenroll
898 --
899
900 IF eval_unit_repeat (
901 p_person_id => igs_av_stnd_unit_rec.person_id,
902 p_load_cal_type => igs_av_stnd_unit_rec.cal_type,
903 p_load_cal_seq_number => igs_av_stnd_unit_rec.ci_sequence_number,
904 p_uoo_id => null,
905 p_program_cd => igs_av_stnd_unit_rec.as_course_cd,
906 p_program_version => igs_av_stnd_unit_rec.as_version_number,
907 p_message => l_message_name,
908 p_deny_warn => 'DENY',
909 p_repeat_tag => l_repeat_tag,
910 p_unit_cd => igs_av_stnd_unit_rec.unit_cd,
911 p_unit_version => igs_av_stnd_unit_rec.version_number,
912 p_calling_obj => 'JOB') = 'N' THEN
913
914 OPEN cur_get_person_num;
915 FETCH cur_get_person_num INTO l_cur_get_person_num;
916 CLOSE cur_get_person_num;
917 fnd_message.set_name( 'IGS', 'IGS_AV_REPEAT_FAIL');
918 fnd_message.set_token('UNIT',igs_av_stnd_unit_rec.unit_cd);
919 fnd_message.set_token('PERSON',l_cur_get_person_num.party_number);
920 fnd_file.put_line(fnd_file.log,fnd_message.get());
921 fnd_file.put_line(FND_FILE.LOG,' ');
922 v_message_key := 'UNIT ' || '|' ||
923 TO_CHAR(v_asu_rec.person_id) || '|' ||
924 v_asu_rec.as_course_cd || '|' ||
925 TO_CHAR(v_asu_rec.as_version_number) || '|' ||
926 v_asu_rec.unit_cd || '|' ||
927 TO_CHAR(v_asu_rec.version_number) || '|' ||
928 FND_DATE.DATE_TO_DISPLAYDATE(v_granted_dt);
929 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
930 v_s_log_type,
931 v_creation_dt,
932 v_message_key,
933 'IGS_AV_REPEAT_FAIL',
934 '');
935 ELSE -- otherwise grant advanced standing.
936 --
937 -- End of new code which was added as per the Bug# 2382566.
938 --
939 IGS_AV_STND_UNIT_PKG.Update_Row (
940 X_Rowid => IGS_AV_STND_UNIT_rec.rowid,
941 X_PERSON_ID => IGS_AV_STND_UNIT_rec.PERSON_ID,
942 X_AS_COURSE_CD => IGS_AV_STND_UNIT_rec.AS_COURSE_CD,
943 X_AS_VERSION_NUMBER => IGS_AV_STND_UNIT_rec.AS_VERSION_NUMBER,
944 X_S_ADV_STND_TYPE => IGS_AV_STND_UNIT_rec.S_ADV_STND_TYPE,
945 X_UNIT_CD => IGS_AV_STND_UNIT_rec.UNIT_CD,
946 X_VERSION_NUMBER => IGS_AV_STND_UNIT_rec.VERSION_NUMBER,
947 X_S_ADV_STND_GRANTING_STATUS => IGS_AV_STND_UNIT_rec.S_ADV_STND_GRANTING_STATUS,
948 X_CREDIT_PERCENTAGE => NULL,
949 X_S_ADV_STND_RECOGNITION_TYPE => IGS_AV_STND_UNIT_rec.S_ADV_STND_RECOGNITION_TYPE,
950 X_APPROVED_DT => IGS_AV_STND_UNIT_rec.APPROVED_DT,
951 X_AUTHORISING_PERSON_ID => IGS_AV_STND_UNIT_rec.AUTHORISING_PERSON_ID,
952 X_CRS_GROUP_IND => IGS_AV_STND_UNIT_rec.CRS_GROUP_IND,
953 X_EXEMPTION_INSTITUTION_CD => IGS_AV_STND_UNIT_rec.EXEMPTION_INSTITUTION_CD,
954 X_GRANTED_DT => IGS_AV_STND_UNIT_rec.granted_dt,
955 X_EXPIRY_DT => IGS_AV_STND_UNIT_rec.EXPIRY_DT,
956 X_CANCELLED_DT => IGS_AV_STND_UNIT_rec.CANCELLED_DT,
957 X_REVOKED_DT => IGS_AV_STND_UNIT_rec.REVOKED_DT,
958 X_COMMENTS => IGS_AV_STND_UNIT_rec.COMMENTS,
959 X_AV_STND_UNIT_ID => IGS_AV_STND_UNIT_rec.AV_STND_UNIT_ID,
960 X_CAL_TYPE => IGS_AV_STND_UNIT_rec.CAL_TYPE,
961 X_CI_SEQUENCE_NUMBER => IGS_AV_STND_UNIT_rec.CI_SEQUENCE_NUMBER,
962 X_INSTITUTION_CD => IGS_AV_STND_UNIT_rec.INSTITUTION_CD,
963 X_UNIT_DETAILS_ID => IGS_AV_STND_UNIT_rec.UNIT_DETAILS_ID,
964 X_TST_RSLT_DTLS_ID => IGS_AV_STND_UNIT_rec.TST_RSLT_DTLS_ID,
965 X_GRADING_SCHEMA_CD => IGS_AV_STND_UNIT_rec.GRADING_SCHEMA_CD,
966 X_GRD_SCH_VERSION_NUMBER => IGS_AV_STND_UNIT_rec.GRD_SCH_VERSION_NUMBER,
967 X_GRADE => IGS_AV_STND_UNIT_rec.GRADE,
968 X_ACHIEVABLE_CREDIT_POINTS => IGS_AV_STND_UNIT_rec.ACHIEVABLE_CREDIT_POINTS,
969 X_MODE => 'R');
970 END IF;
971 END LOOP;
972 END;
973 -- *****************************************************************************************
974 -- Set message key
975 v_message_key := 'UNIT ' || '|' ||
976 TO_CHAR(v_asu_rec.person_id) || '|' ||
977 v_asu_rec.as_course_cd || '|' ||
978 TO_CHAR(v_asu_rec.as_version_number) || '|' ||
979 v_asu_rec.unit_cd || '|' ||
980 TO_CHAR(v_asu_rec.version_number) || '|' ||
981 FND_DATE.DATE_TO_DISPLAYDATE(v_granted_dt);
982 -- Insert into messages for reporting
983
984 fnd_message.set_name('IGS','IGS_AV_GRANTED');
985 cst_granted_ge := fnd_message.get;
986
987 IF (v_message_name is NULL) THEN
988 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
989 v_s_log_type,
990 v_creation_dt,
991 v_message_key,
992 '',
993 cst_granted_ge);
994 ELSE
995 -- Warning from advp_upd_sua_advstnd
996
997 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
998 v_s_log_type,
999 v_creation_dt,
1000 v_message_key,
1001 v_message_name,
1002 cst_granted_ge);
1003 v_message_name := NULL;
1004 END IF;
1005 ELSE
1006 v_update_flag := TRUE;
1007 END IF;
1008 p_message_name := NULL;
1009 END IF; -- p_process_type = cst_course
1010 <<continue>> -- simulate C continue statement
1011 NULL; -- just make the compiler happy
1012 END LOOP; -- process IGS_AV_STND_UNIT
1013
1014 v_skip_course_cd := 'ISNULL';
1015 v_skip_course_cd1 := 'ISNULL';
1016 v_skip_version_number := 0;
1017 v_last_course_cd := 'ISNULL';
1018 v_last_version_number := 0;
1019 -- UPDATE APPROVED IGS_AV_STND_UNIT_LVL
1020 p_message_name := 'IGS_AV_UNITLVL_UPDANOTHER_PRC';
1021
1022 FOR v_asul_rec IN c_asul(p_person_id) LOOP
1023 IF (p_process_type = cst_course AND
1024 (v_asul_rec.as_course_cd <> p_course_cd OR
1025 v_asul_rec.as_version_number <> p_version_number)) OR
1026 (v_asul_rec.as_course_cd = v_skip_course_cd) OR
1027 (v_asul_rec.as_course_cd = v_skip_course_cd1 AND
1028 v_asul_rec.as_version_number = v_skip_version_number)
1029 THEN
1030 -- Do nothing, continue IGS_AV_STND_UNIT_LVL
1031 NULL;
1032 ELSE
1033 -- For each IGS_AV_STND_UNIT_LVL.as_course_cd
1034 -- Validate that person/course is not excluded from advanced standing
1035 IF (IGS_EN_VAL_ENCMB.enrp_val_excld_prsn(
1036 v_asul_rec.person_id,
1037 v_asul_rec.as_course_cd,
1038 v_granted_dt,
1039 v_message_name) = FALSE) THEN
1040 -- Insert into messages for reporting
1041 -- Do not process any more IGS_AV_STND_UNIT_LVL for this course
1042 v_message_key := 'UNIT LEVEL'|| '|' ||
1043 TO_CHAR(v_asul_rec.person_id) || '|' ||
1044 v_asul_rec.as_course_cd || '|' ||
1045 TO_CHAR(v_asul_rec.as_version_number) || '|' ||
1046 v_asul_rec.unit_level || '|' ||
1047 v_asul_rec.crs_group_ind || '|' ||
1048 v_asul_rec.exemption_institution_cd ||'|' ||
1049 FND_DATE.DATE_TO_DISPLAYDATE(v_granted_dt);
1050 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1051 v_s_log_type,
1052 v_creation_dt,
1053 v_message_key,
1054 v_message_name,
1055 '');
1056 v_skip_course_cd := v_asul_rec.as_course_cd;
1057 GOTO continue1;
1058 END IF;
1059 -- For each IGS_AV_STND_UNIT_LVL.as_course_cd/as_version_number
1060 -- Validate advanced standing course version
1061 IF (v_asul_rec.as_course_cd = v_last_course_cd AND
1062 v_asul_rec.as_version_number = v_last_version_number) THEN
1063 NULL; -- do nothing, continue IGS_AV_STND_UNIT
1064 ELSE
1065 v_last_course_cd := v_asul_rec.as_course_cd;
1066 v_last_version_number := v_asul_rec.as_version_number;
1067 IF (IGS_AV_VAL_ASU.advp_val_as_grant(
1068 v_asul_rec.person_id,
1069 v_asul_rec.as_course_cd,
1070 v_asul_rec.as_version_number,
1071 v_asul_rec.s_adv_stnd_granting_status,
1072 v_message_name) = FALSE) THEN
1073 -- Insert into messages for reporting
1074 -- Do not process any more IGS_AV_STND_UNIT_LVL for this course
1075 v_message_key := 'UNIT LEVEL'|| '|' ||
1076 TO_CHAR(v_asul_rec.person_id) || '|' ||
1077 v_asul_rec.as_course_cd || '|' ||
1078 TO_CHAR(v_asul_rec.as_version_number) || '|' ||
1079 v_asul_rec.unit_level || '|' ||
1080 v_asul_rec.crs_group_ind || '|' ||
1081 v_asul_rec.exemption_institution_cd ||'|' ||
1082 FND_DATE.DATE_TO_DISPLAYDATE(v_granted_dt);
1083 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1084 v_s_log_type,
1085 v_creation_dt,
1086 v_message_key,
1087 v_message_name,
1088 '');
1089 v_skip_course_cd1 := v_asul_rec.as_course_cd;
1090 v_skip_version_number := v_asul_rec.as_version_number;
1091 GOTO continue1;
1092 END IF;
1093 END IF;
1094 -- Validate course version advanced standing limits
1095 IF (IGS_AV_VAL_ASU.advp_val_as_totals(
1096 v_asul_rec.person_id,
1097 v_asul_rec.as_course_cd,
1098 v_asul_rec.as_version_number,
1099 TRUE,
1100 '', -- IGS_AV_STND_UNIT.unit_cd
1101 '', -- IGS_AV_STND_UNIT.version_number
1102 '', -- IGS_AV_STND_UNIT.s_adv_stnd_granting_status
1103 v_asul_rec.unit_level,
1104 v_asul_rec.exemption_institution_cd,
1105 cst_granted,
1106 v_total_exmptn_approved,
1107 v_total_exmptn_granted,
1108 v_total_exmptn_perc_grntd,
1109 v_message_name,
1110 v_asul_rec.unit_details_id,
1111 v_asul_rec.tst_rslt_dtls_id,
1112 NULL) = FALSE) THEN
1113 -- Insert into messages for reporting
1114 -- Do not process any more IGS_AV_STND_UNIT_LVL for this course
1115 v_message_key := 'UNIT LEVEL'|| '|' ||
1116 TO_CHAR(v_asul_rec.person_id) || '|' ||
1117 v_asul_rec.as_course_cd || '|' ||
1118 TO_CHAR(v_asul_rec.as_version_number) || '|' ||
1119 v_asul_rec.unit_level || '|' ||
1120 v_asul_rec.crs_group_ind || '|' ||
1121 v_asul_rec.exemption_institution_cd ||'|' ||
1122 FND_DATE.DATE_TO_DISPLAYDATE(v_granted_dt);
1123 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1124 v_s_log_type,
1125 v_creation_dt,
1126 v_message_key,
1127 v_message_name,
1128 '');
1129 v_skip_course_cd1 := v_asul_rec.as_course_cd;
1130 v_skip_version_number := v_asul_rec.as_version_number;
1131 GOTO continue1;
1132 END IF;
1133 -- Update IGS_AV_STND_UNIT_LVL
1134 -- ********************************************************************************************
1135 DECLARE
1136 /* Cursor to select rowid and all columns of the table */
1137 CURSOR Cur_IGS_AV_STND_UNIT_lvl IS
1138 SELECT rowid, IGS_AV_STND_UNIT_lvl.*
1139 FROM IGS_AV_STND_UNIT_lvl
1140 WHERE person_id = p_person_id
1141 AND as_course_cd = v_asul_rec.as_course_cd
1142 AND as_version_number = v_asul_rec.as_version_number
1143 AND unit_level = v_asul_rec.unit_level
1144 AND crs_group_ind = v_asul_rec.crs_group_ind
1145 AND exemption_institution_cd = v_asul_rec.exemption_institution_cd;
1146 BEGIN
1147 FOR IGS_AV_STND_UNIT_lvl_rec IN Cur_IGS_AV_STND_UNIT_LVL LOOP
1148 /* For the column to be updated, modify the record variable value fetched */
1149 IGS_AV_STND_UNIT_lvl_rec.granted_dt := v_granted_dt;
1150 IGS_AV_STND_UNIT_lvl_rec.s_adv_stnd_granting_status := cst_granted;
1151 /* Call server side TBH package procedure */
1152 IGS_AV_STND_UNIT_LVL_PKG.update_row(
1153 X_Rowid => IGS_AV_STND_UNIT_LVL_rec.rowid,
1154 X_PERSON_ID => IGS_AV_STND_UNIT_LVL_rec.PERSON_ID ,
1155 X_AS_COURSE_CD => IGS_AV_STND_UNIT_LVL_rec.AS_COURSE_CD ,
1156 X_AS_VERSION_NUMBER => IGS_AV_STND_UNIT_LVL_rec.AS_VERSION_NUMBER ,
1157 X_S_ADV_STND_TYPE => IGS_AV_STND_UNIT_LVL_rec.S_ADV_STND_TYPE ,
1158 X_UNIT_LEVEL => IGS_AV_STND_UNIT_LVL_rec.UNIT_LEVEL ,
1159 X_CRS_GROUP_IND => IGS_AV_STND_UNIT_LVL_rec.CRS_GROUP_IND ,
1160 X_EXEMPTION_INSTITUTION_CD => IGS_AV_STND_UNIT_LVL_rec.EXEMPTION_INSTITUTION_CD ,
1161 X_S_ADV_STND_GRANTING_STATUS => IGS_AV_STND_UNIT_LVL_rec.S_ADV_STND_GRANTING_STATUS ,
1162 X_CREDIT_POINTS => IGS_AV_STND_UNIT_LVL_rec.CREDIT_POINTS ,
1163 X_APPROVED_DT => IGS_AV_STND_UNIT_LVL_rec.APPROVED_DT ,
1164 X_AUTHORISING_PERSON_ID => IGS_AV_STND_UNIT_LVL_rec.AUTHORISING_PERSON_ID ,
1165 X_GRANTED_DT => IGS_AV_STND_UNIT_LVL_rec.GRANTED_DT ,
1166 X_EXPIRY_DT => IGS_AV_STND_UNIT_LVL_rec.EXPIRY_DT ,
1167 X_CANCELLED_DT => IGS_AV_STND_UNIT_LVL_rec.CANCELLED_DT ,
1168 X_REVOKED_DT => IGS_AV_STND_UNIT_LVL_rec.REVOKED_DT ,
1169 X_COMMENTS => IGS_AV_STND_UNIT_LVL_rec.COMMENTS ,
1170 X_AV_STND_UNIT_LVL_ID => IGS_AV_STND_UNIT_LVL_rec.AV_STND_UNIT_LVL_ID ,
1171 X_CAL_TYPE => IGS_AV_STND_UNIT_LVL_rec.CAL_TYPE ,
1172 X_CI_SEQUENCE_NUMBER => IGS_AV_STND_UNIT_LVL_rec.CI_SEQUENCE_NUMBER ,
1173 X_INSTITUTION_CD => IGS_AV_STND_UNIT_LVL_rec.INSTITUTION_CD ,
1174 X_UNIT_DETAILS_ID => IGS_AV_STND_UNIT_LVL_rec.UNIT_DETAILS_ID ,
1175 X_TST_RSLT_DTLS_ID => IGS_AV_STND_UNIT_LVL_rec.TST_RSLT_DTLS_ID ,
1176 X_MODE => 'R' ,
1177 X_QUAL_DETS_ID => IGS_AV_STND_UNIT_LVL_rec.QUAL_DETS_ID -- Added column to tbh call w.r.t to ARCR032 (Bug# 2233334)
1178 );
1179 END LOOP;
1180 END;
1181 -- *****************************************************************************************
1182 -- Insert into messages for reporting
1183 v_message_key := 'UNIT LEVEL'|| '|' ||
1184 TO_CHAR(v_asul_rec.person_id) || '|' ||
1185 v_asul_rec.as_course_cd || '|' ||
1186 TO_CHAR(v_asul_rec.as_version_number) || '|' ||
1187 v_asul_rec.unit_level || '|' ||
1188 v_asul_rec.crs_group_ind || '|' ||
1189 v_asul_rec.exemption_institution_cd ||'|' ||
1190 FND_DATE.DATE_TO_DISPLAYDATE(v_granted_dt);
1191 fnd_message.set_name('IGS','IGS_AV_GRANTED');
1192 cst_granted_ge := fnd_message.get;
1193 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1194 v_s_log_type,
1195 v_creation_dt,
1196 v_message_key,
1197 '',
1198 cst_granted_ge);
1199 END IF;
1200 <<continue1>> -- simulate C continue statement
1201 NULL; -- just make the compiler happy
1202 END LOOP; -- process IGS_AV_STND_UNIT_LVL
1203 COMMIT;
1204 p_message_name := NULL;
1205 RETURN TRUE;
1206 END;
1207 EXCEPTION
1208 WHEN OTHERS THEN
1209 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
1210 Fnd_Message.Set_Token('NAME','IGS_AV_GEN_001.ADVP_UPD_AS_PE_GRANT');
1211 Igs_Ge_Msg_Stack.Add;
1212
1213 lv_param_values := To_Char(p_person_id)||p_course_cd||To_Char(p_version_number)||
1214 FND_DATE.DATE_TO_DISPLAYDATE(p_granted_dt)||p_process_type||p_s_log_type||
1215 FND_DATE.DATE_TO_DISPLAYDATE(p_creation_dt);
1216 Fnd_Message.Set_Name('IGS','IGS_GE_PARAMETERS');
1217 Fnd_Message.Set_Token('VALUE',lv_param_values);
1218 Igs_Ge_Msg_Stack.Add;
1219
1220 App_Exception.Raise_Exception;
1221 END advp_upd_as_pe_grant;
1222
1223 FUNCTION advp_upd_as_totals(
1224 p_person_id IN NUMBER ,
1225 p_course_cd IN VARCHAR2 ,
1226 p_version_number IN NUMBER ,
1227 p_message_name OUT NOCOPY VARCHAR2,
1228 p_exemption_institution_cd IN VARCHAR2)
1229 RETURN BOOLEAN IS
1230 lv_param_values VARCHAR2(1080);
1231 BEGIN
1232 DECLARE
1233 CURSOR c_adv_stnd_details (
1234 cp_person_id IGS_AV_STND_UNIT.person_id%TYPE,
1235 cp_course_cd IGS_AV_STND_UNIT.as_course_cd%TYPE,
1236 cp_version_number IGS_AV_STND_UNIT.as_version_number%TYPE
1237 ) IS
1238 SELECT rowid , adv.*
1239 FROM IGS_AV_ADV_STANDING adv
1240 WHERE adv.person_id = cp_person_id AND
1241 adv.course_cd = cp_course_cd AND
1242 adv.version_number = cp_version_number AND
1243 adv.exemption_institution_cd = p_exemption_institution_cd;
1244 --NEXT LINE COMMENTED OUT NOCOPY TO FIX BUG# 1618537.
1245 --FOR UPDATE NOWAIT;
1246 v_other_detail VARCHAR2(255);
1247 v_total_exmptn_approved NUMBER;
1248 v_total_exmptn_granted NUMBER;
1249 v_total_exmptn_perc_grntd NUMBER;
1250 v_message_name VARCHAR2(30);
1251 v_adv_stnd_recs_found BOOLEAN;
1252 BEGIN
1253 -- This function validates that the advanced standing
1254 -- approved/granted has not exceeded the advanced
1255 -- standing limits of the course version. It then
1256 -- updates the advanced standing exemption totals.
1257
1258 -- validate the input parameters
1259 IF (p_person_id IS NULL OR
1260 p_course_cd IS NULL OR
1261 p_version_number IS NULL) THEN
1262 p_message_name := 'IGS_AV_INSUFFICIENT_INFO_VER';
1263 RETURN FALSE;
1264 END IF;
1265 -- get advanced standing exemption totals
1266 IF (IGS_AV_VAL_ASU.advp_val_as_totals (
1267 p_person_id,
1268 p_course_cd,
1269 p_version_number,
1270 TRUE,
1271 '', -- IGS_AV_STND_UNIT.unit_cd
1272 '', -- IGS_AV_STND_UNIT.version_number
1273 '', -- IGS_AV_STND_UNIT.s_adv_stnd_granting_status
1274 '', -- IGS_AV_STND_UNIT_LVL.unit_level
1275 p_exemption_institution_cd, -- IGS_AV_STND_UNIT_LVL.exemption_institution_cd
1276 '', -- IGS_AV_STND_UNIT_LVL.s_adv_stnd_granting_status
1277 v_total_exmptn_approved,
1278 v_total_exmptn_granted,
1279 v_total_exmptn_perc_grntd,
1280 p_message_name,
1281 null,
1282 null,
1283 p_exemption_institution_cd) = FALSE) THEN
1284 RETURN FALSE;
1285 END IF;
1286 -- set that no records have yet been found
1287 v_adv_stnd_recs_found := FALSE;
1288 -- setting the message number beforehand
1289 -- so if failure of the lock occurs, this
1290 -- value can be passed to the exception handler
1291 p_message_name := 'IGS_AV_UNABLE_UPD_TOTALS';
1292 -- select IGS_AV_STND_UNIT for parameters to determine
1293 -- existing totals
1294
1295 FOR v_adv_stnd IN c_adv_stnd_details(p_person_id,
1296 p_course_cd,
1297 p_version_number
1298 ) LOOP
1299 -- set that a record has been found
1300 v_adv_stnd_recs_found := TRUE;
1301 -- ****************************************************************************************
1302 IGS_AV_ADV_STANDING_PKG.Update_Row(
1303 X_Rowid => v_adv_stnd.rowid,
1304 X_PERSON_ID => v_adv_stnd.person_id,
1305 X_COURSE_CD => v_adv_stnd.course_cd,
1306 X_VERSION_NUMBER => v_adv_stnd.version_number ,
1307 X_TOTAL_EXMPTN_APPROVED => v_total_exmptn_approved,
1308 X_TOTAL_EXMPTN_GRANTED => v_total_exmptn_granted ,
1309 X_TOTAL_EXMPTN_PERC_GRNTD => NVL(v_total_exmptn_perc_grntd,0) ,
1310 X_EXEMPTION_INSTITUTION_CD => v_adv_stnd.EXEMPTION_INSTITUTION_CD ,
1311 X_MODE => 'R');
1312 -- ***************************************************************************************
1313 END LOOP;
1314 -- set the default message number and return type
1315 p_message_name := NULL;
1316 RETURN TRUE;
1317 END;
1318 END advp_upd_as_totals;
1319
1320 FUNCTION upd_sua_advstnd(
1321 p_person_id IN NUMBER ,
1322 p_course_cd IN VARCHAR2 ,
1323 p_unit_cd IN VARCHAR2 ,
1324 p_version_number IN NUMBER ,
1325 p_granted_dt IN DATE ,
1326 p_message_name OUT NOCOPY VARCHAR2)
1327 RETURN VARCHAR2 IS
1328 BEGIN
1329
1330 IF(advp_upd_sua_advstnd(
1331 p_person_id ,
1332 p_course_cd ,
1333 p_unit_cd ,
1334 p_version_number ,
1335 p_granted_dt ,
1336 p_message_name )) THEN
1337 RETURN 'Y';
1338 ELSE
1339 RETURN 'N';
1340 END IF;
1341
1342 END upd_sua_advstnd ;
1343 FUNCTION advp_upd_sua_advstnd(
1344 p_person_id IN NUMBER ,
1345 p_course_cd IN VARCHAR2 ,
1346 p_unit_cd IN VARCHAR2 ,
1347 p_version_number IN NUMBER ,
1348 p_granted_dt IN DATE ,
1349 p_message_name OUT NOCOPY VARCHAR2)
1350 RETURN BOOLEAN IS
1351 BEGIN
1352 DECLARE
1353 cst_unconfirm CONSTANT VARCHAR2(10) := 'UNCONFIRM';
1354 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
1355 cst_invalid CONSTANT VARCHAR2(10) := 'INVALID';
1356 cst_discontin CONSTANT VARCHAR2(10) := 'DISCONTIN';
1357 cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
1358 cst_duplicate CONSTANT VARCHAR2(10) := 'DUPLICATE';
1359 cst_fail CONSTANT VARCHAR2(10) := 'FAIL';
1360 cst_incomp CONSTANT VARCHAR2(10) := 'INCOMP';
1361 CURSOR gc_sua_rec (
1362 cp_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
1363 cp_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE,
1364 cp_unit_cd IGS_EN_SU_ATTEMPT.unit_cd%TYPE,
1365 cp_version_number IGS_EN_SU_ATTEMPT.version_number%TYPE) IS
1366 SELECT sua.unit_attempt_status,
1367 sua.cal_type,
1368 sua.ci_sequence_number,
1369 sua.ci_start_dt,
1370 sua.enrolled_dt,
1371 uv.repeatable_ind,
1372 sua.uoo_id
1373 FROM IGS_EN_SU_ATTEMPT sua,
1374 IGS_PS_UNIT_VER uv
1375 WHERE sua.person_id = cp_person_id AND
1376 sua.course_cd = cp_course_cd AND
1377 sua.unit_cd = cp_unit_cd AND
1378 sua.version_number = cp_version_number AND
1379 uv.unit_cd = sua.unit_cd AND
1380 uv.version_number = sua.version_number
1381 FOR UPDATE NOWAIT
1382 ORDER BY DECODE(sua.unit_attempt_status,
1383 'DISCONTIN',1,
1384 'UNCONFIRM',2,
1385 'INVALID',3,
1386 'COMPLETED',4,
1387 'ENROLLED',5);
1388
1389 CURSOR gc_sub_sua_rec (
1390 cp_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
1391 cp_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE,
1392 cp_sup_unit_cd IGS_EN_SU_ATTEMPT.sup_unit_cd%TYPE,
1393 cp_sup_vers_num IGS_EN_SU_ATTEMPT.sup_version_number%TYPE) IS
1394 SELECT *
1395 FROM IGS_EN_SU_ATTEMPT sub_sua
1396 WHERE sub_sua.person_id = cp_person_id AND
1397 sub_sua.course_cd = cp_course_cd AND
1398 sub_sua.sup_unit_cd = cp_sup_unit_cd AND
1399 sub_sua.sup_version_number = cp_sup_vers_num
1400 FOR UPDATE NOWAIT;
1401
1402 CURSOR gc_daiv(
1403 cp_cal_type IGS_CA_INST.cal_type%TYPE,
1404 cp_ci_seq_num IGS_CA_INST.sequence_number%TYPE) IS
1405 SELECT daiv.alias_val
1406 FROM IGS_CA_DA_INST_V daiv,
1407 IGS_GE_S_GEN_CAL_CON sgcc
1408 WHERE daiv.cal_type = cp_cal_type and
1409 daiv.ci_sequence_number = cp_ci_seq_num and
1410 daiv.dt_alias = sgcc.census_dt_alias and
1411 sgcc.s_control_num = 1
1412 ORDER BY
1413 daiv.alias_val DESC;
1414
1415 CURSOR cur_get_person_num IS
1416 SELECT party_number
1417 FROM hz_parties
1418 WHERE party_id = p_person_id;
1419 l_cur_get_person_num cur_get_person_num%ROWTYPE;
1420
1421 gv_other_detail VARCHAR2(255);
1422 gv_s_result_type VARCHAR2(10);
1423 v_sua_rec_found BOOLEAN;
1424 v_sub_sua_recs_found BOOLEAN;
1425 gv_grading_schema_cd IGS_AS_GRD_SCH_GRADE.grading_schema_cd%TYPE;
1426 gv_gs_version_number IGS_AS_GRD_SCH_GRADE.version_number%TYPE;
1427 gv_grade IGS_AS_GRD_SCH_GRADE.grade%TYPE;
1428 gv_administrative_unit_status IGS_EN_SU_ATTEMPT.administrative_unit_status%TYPE;
1429 gv_unit_cd IGS_EN_SU_ATTEMPT.unit_cd%TYPE;
1430 gv_version_number IGS_EN_SU_ATTEMPT.version_number%TYPE;
1431 gv_cal_type IGS_EN_SU_ATTEMPT.cal_type%TYPE;
1432 gv_ci_sequence_number IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE;
1433 gv_ci_start_dt IGS_EN_SU_ATTEMPT.ci_start_dt%TYPE;
1434 gv_enrolled_dt IGS_EN_SU_ATTEMPT.enrolled_dt%TYPE;
1435 gv_unit_attempt_status IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE;
1436 gv_repeatable_ind IGS_PS_UNIT_VER.repeatable_ind%TYPE;
1437 gv_census_dt IGS_CA_DA_INST_V.alias_val%TYPE;
1438 gv_message_num VARCHAR2(30);
1439 gv_message_num2 VARCHAR2(30);
1440 gv_sub_unit BOOLEAN;
1441 gv_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE;
1442
1443 FUNCTION enrpl_delete_sua_recs (
1444 p_del_person_id IN IGS_EN_SU_ATTEMPT.person_id%TYPE,
1445 p_del_course_cd IN IGS_EN_SU_ATTEMPT.course_cd%TYPE,
1446 p_del_granted_dt IN DATE,
1447 p_del_unit_cd IN IGS_EN_SU_ATTEMPT.unit_cd%TYPE,
1448 p_del_vers_num IN IGS_EN_SU_ATTEMPT.version_number%TYPE,
1449 p_del_cal_type IN IGS_EN_SU_ATTEMPT.cal_type%TYPE,
1450 p_del_ci_seq_num IN IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE,
1451 p_del_ci_start_dt IN IGS_EN_SU_ATTEMPT.ci_start_dt%TYPE,
1452 p_del_enrolled_dt IN IGS_EN_SU_ATTEMPT.enrolled_dt%TYPE,
1453 p_del_unit_atmpt_status IN IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE,
1454 p_del_sub_unit IN BOOLEAN,
1455 p_del_message_num OUT NOCOPY NUMBER,
1456 p_del_uoo_id IN IGS_EN_SU_ATTEMPT.uoo_id%TYPE)
1457 RETURN BOOLEAN IS
1458 BEGIN
1459 DECLARE
1460 v_other_detail VARCHAR2(255);
1461 v_administrative_unit_status IGS_EN_SU_ATTEMPT.administrative_unit_status%TYPE;
1462 v_message_name VARCHAR2(30);
1463 v_alias_val IGS_CA_DA_INST_V.alias_val%TYPE;
1464 v_admin_unit_status_str VARCHAR2(2000);
1465 BEGIN
1466 -- this module will delete student_unit_attempts
1467 -- (will need to return TRUE or FALSE)
1468 -- this determines if a IGS_EN_SU_ATTEMPT
1469 -- can be deleted because advanced standing is
1470 -- granted prior to the advanced standing cut-off date
1471 IF (IGS_AV_VAL_ASU.advp_get_ua_del_alwd (
1472 p_del_cal_type,
1473 p_del_ci_seq_num,
1474 p_del_granted_dt) = TRUE) THEN
1475 -- delete the record
1476 --Deleting functionality has been removed as per the Acedemic record maintenance build. Bug no-1960126
1477 -- checking the value of p_del_sub_unit
1478 IF (p_del_sub_unit) THEN -- is TRUE
1479 p_del_message_num := 2050;
1480 ELSE -- is FALSE
1481 p_del_message_num := 2049;
1482 END IF;
1483 -- exit this sub function, and return TRUE
1484 RETURN TRUE;
1485 ELSE -- advp_get_ua_del_alwd = FALSE
1486 -- determine if IGS_EN_SU_ATTEMPT can be
1487 -- deleted because of UNIT discontinuation date
1488 -- criteria
1489
1490 IF (IGS_EN_GEN_008.enrp_get_ua_del_alwd (
1491 p_del_cal_type,
1492 p_del_ci_seq_num,
1493 p_del_granted_dt,
1494 p_del_uoo_id) = 'Y') THEN
1495 -- cheking the value of p_del_sub_unit
1496 IF (p_del_sub_unit) THEN -- is TRUE
1497 p_del_message_num := 2050;
1498 ELSE -- is FALSE
1499 p_del_message_num := 2049;
1500 END IF;
1501 -- exit this sub function, and return TRUE
1502 RETURN TRUE;
1503 ELSE -- enrp_get_ua_del_alwd returned N
1504 -- discontinue IGS_EN_SU_ATTEMPT
1505 -- get administrative UNIT status associated
1506 -- with disocntinuation v_administrative_unit_status
1507
1508 v_administrative_unit_status := (IGS_EN_GEN_008.enrp_get_uddc_aus(
1509 p_del_granted_dt,
1510 p_del_cal_type,
1511 p_del_ci_seq_num,
1512 v_admin_unit_status_str,
1513 v_alias_val,
1514 p_del_uoo_id));
1515 IF (v_administrative_unit_status IS NULL) THEN
1516 IF (p_del_sub_unit) THEN -- is TRUE
1517 p_del_message_num := 1980;
1518 ELSE -- is FALSE
1519 p_del_message_num := 1979;
1520 END IF;
1521 -- exit this sub function, and return FALSE
1522 RETURN FALSE;
1523 END IF;
1524 -- validate discontinuation
1525 IF (IGS_EN_VAL_SUA.enrp_val_sua_discont(
1526 p_del_person_id,
1527 p_del_course_cd,
1528 p_del_unit_cd,
1529 p_del_vers_num,
1530 p_del_ci_start_dt,
1531 p_del_enrolled_dt,
1532 v_administrative_unit_status,
1533 p_del_unit_atmpt_status,
1534 p_del_granted_dt,
1535 v_message_name ,
1536 'N' ) = FALSE) THEN
1537 -- checking the value of p_del_sub_unit
1538 IF (p_del_sub_unit) THEN -- is TRUE
1539 p_del_message_num := 1797;
1540 ELSE -- is FALSE
1541 p_del_message_num := 1808;
1542 END IF;
1543 -- exit this sub function, and return FALSE
1544 RETURN FALSE;
1545 ELSE -- enrp_val_sua_discont returned TRUE
1546 -- checking the value of p_del_sub_unit
1547 IF (p_del_sub_unit) THEN -- is TRUE
1548 p_del_message_num := 1811;
1549 ELSE -- is FALSE
1550 p_del_message_num := 1812;
1551 END IF;
1552 -- exit this sub function, and return TRUE
1553 RETURN TRUE;
1554 END IF;
1555 END IF;
1556 END IF;
1557 EXCEPTION
1558 WHEN OTHERS THEN
1559 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
1560 Fnd_Message.Set_Token('NAME','IGS_AV_GEN_001.ENRPL_DELETE_SUA_RECS');
1561 Igs_Ge_Msg_Stack.Add;
1562 App_Exception.Raise_Exception;
1563 END;
1564 END enrpl_delete_sua_recs;
1565 BEGIN
1566 -- This function updates a student's enrolment
1567 -- when an advanced standing UNIT is granted
1568 -- set the default message number
1569 p_message_name := NULL;
1570 -- validate the input parameters
1571 IF (p_person_id IS NULL OR
1572 p_course_cd IS NULL OR
1573 p_unit_cd IS NULL OR
1574 p_version_number IS NULL OR
1575 p_granted_dt IS NULL) THEN
1576 p_message_name := 'IGS_AV_MAPADV_SUA_CANNOT_DTRM';
1577 RETURN FALSE;
1578 END IF;
1579 -- set that no IGS_EN_SU_ATTEMPT
1580 -- records haven't been found yet
1581 v_sua_rec_found := FALSE;
1582 -- setting the message number beforehand
1583 -- so if failure of the lock occurs, this
1584 -- value can be passed to the exception handler
1585 p_message_name := 'IGS_AV_UNABLE_UPD_ENRDET';
1586 -- Establish a savepoint
1587 SAVEPOINT sp_discontinue_sua;
1588 -- update IGS_EN_SU_ATTEMPT
1589 FOR gv_sua_rec IN gc_sua_rec (p_person_id,
1590 p_course_cd,
1591 p_unit_cd,
1592 p_version_number) LOOP
1593
1594 -- set that a record was found
1595 v_sua_rec_found := TRUE;
1596
1597 --Deleting functionality has been removed as per the Acedemic record maintenance build. Bug no-1960126
1598
1599 -- IGS_EN_SU_ATTEMPT is deleted/discontined if enrolled
1600 IF (gv_sua_rec.unit_attempt_status = cst_enrolled) THEN
1601 IF gv_sua_rec.repeatable_ind <> 'X' THEN
1602 -- Do not delete student UNIT attempt if UNIT is repeatable
1603 p_message_name := 'IGS_AV_CURENR_REPEATABLE_UNIT';
1604 ELSE
1605 -- setting the values
1606 gv_unit_cd := p_unit_cd;
1607 gv_version_number := p_version_number;
1608 gv_cal_type := gv_sua_rec.cal_type;
1609 gv_ci_sequence_number := gv_sua_rec.ci_sequence_number;
1610 gv_ci_start_dt := gv_sua_rec.ci_start_dt;
1611 gv_enrolled_dt := gv_sua_rec.enrolled_dt;
1612 gv_unit_attempt_status := gv_sua_rec.unit_attempt_status;
1613 gv_sub_unit := FALSE;
1614 gv_uoo_id := gv_sua_rec.uoo_id;
1615 -- deleting/discontinue IGS_EN_SU_ATTEMPT
1616 IF (enrpl_delete_sua_recs(
1617 p_person_id,
1618 p_course_cd,
1619 p_granted_dt,
1620 gv_unit_cd,
1621 gv_version_number,
1622 gv_cal_type,
1623 gv_ci_sequence_number,
1624 gv_ci_start_dt,
1625 gv_enrolled_dt,
1626 gv_unit_attempt_status,
1627 gv_sub_unit,
1628 gv_message_num,
1629 gv_uoo_id) = FALSE) THEN
1630 -- Rollback any changes to student_unit_attempts
1631 ROLLBACK to sp_discontinue_sua;
1632 p_message_name := gv_message_num;
1633 RETURN FALSE;
1634 ELSE -- returns true
1635 --p_message_num := gv_message_num;
1636 -- setting another message number
1637 -- so if no subordinate records are
1638 -- found, it won't return the message
1639 -- number for locking problems, but
1640 -- the message number returned from
1641 -- the called routine
1642 gv_message_num2 := gv_message_num;
1643 END IF;
1644 -- set that no subordinate records
1645 -- were found
1646 v_sub_sua_recs_found := FALSE;
1647 -- setting the message number beforehand
1648 -- so if failure of the lock occurs, this
1649 -- value can be passed to the exception handler
1650 p_message_name := 'IGS_AV_ANOTHERPRC_UPDATING';
1651 -- delete/discontinue sub-ordinate student_unit_attempts
1652 -- if they exist
1653 FOR gv_sub_sua_rec IN gc_sub_sua_rec(
1654 p_person_id,
1655 p_course_cd,
1656 p_unit_cd,
1657 p_version_number) LOOP
1658 -- set that subordinate records were found
1659 v_sub_sua_recs_found := TRUE;
1660 -- setting the values
1661 gv_unit_cd := gv_sub_sua_rec.unit_cd;
1662 gv_version_number := gv_sub_sua_rec.version_number;
1663 gv_cal_type := gv_sub_sua_rec.cal_type;
1664 gv_ci_sequence_number := gv_sub_sua_rec.ci_sequence_number;
1665 gv_ci_start_dt := gv_sub_sua_rec.ci_start_dt;
1666 gv_enrolled_dt := gv_sub_sua_rec.enrolled_dt;
1667 gv_unit_attempt_status := gv_sub_sua_rec.unit_attempt_status;
1668 gv_sub_unit := TRUE;
1669 gv_uoo_id := gv_sub_sua_rec.uoo_id;
1670 -- deleting/discontinue IGS_EN_SU_ATTEMPT
1671 IF (enrpl_delete_sua_recs(
1672 p_person_id,
1673 p_course_cd,
1674 p_granted_dt,
1675 gv_unit_cd,
1676 gv_version_number,
1677 gv_cal_type,
1678 gv_ci_sequence_number,
1679 gv_ci_start_dt,
1680 gv_enrolled_dt,
1681 gv_unit_attempt_status,
1682 gv_sub_unit,
1683 gv_message_num,
1684 gv_uoo_id) = FALSE) THEN
1685 -- Rollback any changes to student_unit_attempts
1686 ROLLBACK to sp_discontinue_sua;
1687 p_message_name := gv_message_num;
1688 RETURN FALSE;
1689 ELSE -- returned true
1690 p_message_name := gv_message_num;
1691 END IF;
1692 END LOOP;
1693 -- set that no subordinate records were found
1694 IF (v_sub_sua_recs_found = FALSE) THEN
1695 -- set the message number that was
1696 -- returned from the called function
1697 -- if this isn't done, and no records
1698 -- are found, the message number returned
1699 -- would be 1813 (which isn't what we want -
1700 -- as no locking problems occurred).
1701 p_message_name := gv_message_num2;
1702 END IF;
1703 END IF;
1704 END IF;
1705
1706 -- IGS_EN_SU_ATTEMPT is not altered if invalid, return error
1707 -- to indiate that the advanced standing should not be granted
1708 IF (gv_sua_rec.unit_attempt_status = cst_invalid) THEN
1709 -- Rollback any changes to student_unit_attempts
1710 ROLLBACK to sp_discontinue_sua;
1711 p_message_name := 'IGS_AV_CANNOTBE_GRANT_EXISTS';
1712 RETURN FALSE;
1713 END IF;
1714
1715 IF (gv_sua_rec.unit_attempt_status IN ( cst_enrolled,
1716 cst_completed,
1717 cst_invalid)) THEN
1718 --
1719 -- Added as per the bug# 2382566
1720 -- Catch Enrolled, Completed and Invalid and just warn the user that the student has an Unit Attempt with this status.
1721 -- Start of new code.
1722 OPEN cur_get_person_num;
1723 FETCH cur_get_person_num INTO l_cur_get_person_num;
1724 CLOSE cur_get_person_num;
1725
1726 p_message_name := 'IGS_AV_HAS_UNIT_ATT';
1727 fnd_message.set_name('IGS',p_message_name);
1728 fnd_message.set_token('PERSON',l_cur_get_person_num.party_number);
1729 fnd_message.set_token('UNIT',p_unit_cd);
1730 fnd_file.put_line(fnd_file.log,fnd_message.get());
1731 fnd_file.put_line(FND_FILE.LOG,' ');
1732 -- End of new code. Added as per the bug# 2382566.
1733 ELSIF (gv_sua_rec.unit_attempt_status NOT IN (cst_duplicate)) THEN
1734 p_message_name := NULL;
1735 END IF;
1736 END LOOP;
1737 -- checking whether IGS_EN_SU_ATTEMPT
1738 -- records were found
1739 IF (v_sua_rec_found = FALSE) THEN
1740 p_message_name := NULL;
1741 RETURN TRUE;
1742 END IF;
1743 -- set the default return type
1744 RETURN TRUE;
1745 EXCEPTION
1746 WHEN OTHERS THEN
1747 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
1748 Fnd_Message.Set_Token('NAME','IGS_AV_GEN_001.ADVP_UPD_SUA_ADVSTND');
1749 Igs_Ge_Msg_Stack.Add;
1750 App_Exception.Raise_Exception;
1751 END;
1752 END advp_upd_sua_advstnd;
1753
1754 PROCEDURE advp_upd_as_pe_expry(errbuf OUT NOCOPY VARCHAR2,
1755 retcode OUT NOCOPY NUMBER,
1756 p_org_id IN NUMBER ) AS
1757 BEGIN -- advp_upd_as_pe_expire
1758 --This procedure expires approved advanced standing for a person
1759
1760 -- To set org_id as in request of job.
1761 -- This is added to fix Bug no# 1159910.
1762 IGS_GE_GEN_003.set_org_id(p_org_id);
1763
1764
1765 DECLARE
1766 lv_count NUMBER:=0;
1767 e_resource_busy EXCEPTION;
1768 PRAGMA EXCEPTION_INIT (e_resource_busy, -54);
1769
1770 CURSOR c_person IS
1771 SELECT person_id
1772 FROM IGS_AV_STND_UNIT_ALL
1773 WHERE s_adv_stnd_granting_status = 'APPROVED' AND
1774 NVL(expiry_dt, IGS_GE_DATE.IGSDATE('9999/01/01')) < SYSDATE
1775 UNION
1776 SELECT person_id
1777 FROM IGS_AV_STND_UNIT_LVL_ALL
1778 WHERE s_adv_stnd_granting_status = 'APPROVED' AND
1779 NVL(expiry_dt, IGS_GE_DATE.IGSDATE('9999/01/01')) < SYSDATE;
1780
1781 CURSOR c_adv_stnd_unit (cp_person_id IGS_PE_PERSON.person_id%TYPE) IS
1782 SELECT rowid , IGS_AV_STND_UNIT.*
1783 FROM igs_av_stnd_unit
1784 WHERE s_adv_stnd_granting_status = 'APPROVED' AND
1785 NVL(expiry_dt, IGS_GE_DATE.IGSDATE('9999/01/01')) < SYSDATE AND
1786 person_id = cp_person_id AND
1787 s_adv_stnd_recognition_type <> 'PRECLUSION' /* Added as per Bug# 2441175 */
1788 ORDER BY
1789 person_id,
1790 as_course_cd,
1791 as_version_number
1792 FOR UPDATE OF s_adv_stnd_granting_status NOWAIT;
1793
1794 CURSOR c_adv_stnd_unit_level (cp_person_id IGS_PE_PERSON.person_id%TYPE) IS
1795 SELECT rowid,IGS_AV_STND_UNIT_LVL.*
1796 FROM IGS_AV_STND_UNIT_LVL
1797 WHERE s_adv_stnd_granting_status = 'APPROVED' AND
1798 NVL(expiry_dt, IGS_GE_DATE.IGSDATE('9999/01/01')) < SYSDATE AND
1799 person_id = cp_person_id
1800 ORDER BY
1801 person_id,
1802 as_course_cd,
1803 as_version_number
1804 FOR UPDATE OF s_adv_stnd_granting_status NOWAIT;
1805 BEGIN
1806 errbuf:=NULL;
1807 FOR v_person_rec IN c_person LOOP
1808 BEGIN
1809 RETCODE:=0;
1810 BEGIN
1811 FOR v_asu_rec IN c_adv_stnd_unit(v_person_rec.person_id) LOOP
1812
1813 -- ******************************************************************************************
1814 IGS_AV_STND_UNIT_PKG.update_row(
1815 X_Rowid => v_asu_rec.rowid ,
1816 X_PERSON_ID => v_asu_rec.PERSON_ID ,
1817 X_AS_COURSE_CD => v_asu_rec.AS_COURSE_CD ,
1818 X_AS_VERSION_NUMBER => v_asu_rec.AS_VERSION_NUMBER ,
1819 X_S_ADV_STND_TYPE => v_asu_rec.S_ADV_STND_TYPE ,
1820 X_UNIT_CD => v_asu_rec.UNIT_CD ,
1821 X_VERSION_NUMBER => v_asu_rec.VERSION_NUMBER ,
1822 X_S_ADV_STND_GRANTING_STATUS => 'EXPIRED' ,
1823 /* X_CREDIT_PERCENTAGE => v_asu_rec.CREDIT_PERCENTAGE , */
1824 X_S_ADV_STND_RECOGNITION_TYPE => v_asu_rec.S_ADV_STND_RECOGNITION_TYPE ,
1825 X_APPROVED_DT => v_asu_rec.APPROVED_DT ,
1826 X_AUTHORISING_PERSON_ID => v_asu_rec.AUTHORISING_PERSON_ID ,
1827 X_CRS_GROUP_IND => v_asu_rec.CRS_GROUP_IND ,
1828 X_EXEMPTION_INSTITUTION_CD => v_asu_rec.EXEMPTION_INSTITUTION_CD ,
1829 X_GRANTED_DT => v_asu_rec.GRANTED_DT ,
1830 X_EXPIRY_DT => v_asu_rec.EXPIRY_DT ,
1831 X_CANCELLED_DT => v_asu_rec.CANCELLED_DT ,
1832 X_REVOKED_DT => v_asu_rec.REVOKED_DT ,
1833 X_COMMENTS => v_asu_rec.COMMENTS ,
1834 X_AV_STND_UNIT_ID => v_asu_rec.AV_STND_UNIT_ID ,
1835 X_CAL_TYPE => v_asu_rec.CAL_TYPE ,
1836 X_CI_SEQUENCE_NUMBER => v_asu_rec.CI_SEQUENCE_NUMBER ,
1837 X_INSTITUTION_CD => v_asu_rec.INSTITUTION_CD ,
1838 X_UNIT_DETAILS_ID => v_asu_rec.UNIT_DETAILS_ID ,
1839 X_TST_RSLT_DTLS_ID => v_asu_rec.TST_RSLT_DTLS_ID ,
1840 X_GRADING_SCHEMA_CD => v_asu_rec.GRADING_SCHEMA_CD ,
1841 X_GRD_SCH_VERSION_NUMBER => v_asu_rec.GRD_SCH_VERSION_NUMBER ,
1842 X_GRADE => v_asu_rec.GRADE ,
1843 X_ACHIEVABLE_CREDIT_POINTS => v_asu_rec.ACHIEVABLE_CREDIT_POINTS ,
1844 X_MODE => 'R');
1845 -- *****************************************************************************************
1846
1847 END LOOP;
1848 EXCEPTION
1849 WHEN e_resource_busy THEN
1850
1851 IF (c_adv_stnd_unit%ISOPEN) THEN
1852 CLOSE c_adv_stnd_unit;
1853 END IF;
1854 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(v_person_rec.person_id) || ' '||
1855 FND_MESSAGE.GET_STRING('IGS',
1856 'IGS_AV_UNABLE_EXP_UNIT_UPD'));
1857 RAISE;
1858 WHEN OTHERS THEN
1859 RAISE;
1860 END;
1861 BEGIN
1862 FOR v_asul_rec IN c_adv_stnd_unit_level(v_person_rec.person_id) LOOP
1863 -- ****************************************************************************************
1864
1865 IGS_AV_STND_UNIT_LVL_PKG.update_row(
1866 X_Rowid => v_asul_rec.rowid ,
1867 X_PERSON_ID => v_asul_rec.PERSON_ID ,
1868 X_AS_COURSE_CD => v_asul_rec.AS_COURSE_CD ,
1869 X_AS_VERSION_NUMBER => v_asul_rec.AS_VERSION_NUMBER ,
1870 X_S_ADV_STND_TYPE => v_asul_rec.S_ADV_STND_TYPE ,
1871 X_UNIT_LEVEL => v_asul_rec.UNIT_LEVEL ,
1872 X_CRS_GROUP_IND => v_asul_rec.CRS_GROUP_IND ,
1873 X_EXEMPTION_INSTITUTION_CD => v_asul_rec.EXEMPTION_INSTITUTION_CD ,
1874 X_S_ADV_STND_GRANTING_STATUS => 'EXPIRED' ,
1875 X_CREDIT_POINTS => v_asul_rec.CREDIT_POINTS ,
1876 X_APPROVED_DT => v_asul_rec.APPROVED_DT ,
1877 X_AUTHORISING_PERSON_ID => v_asul_rec.AUTHORISING_PERSON_ID ,
1878 X_GRANTED_DT => v_asul_rec.GRANTED_DT ,
1879 X_EXPIRY_DT => v_asul_rec.EXPIRY_DT ,
1880 X_CANCELLED_DT => v_asul_rec.CANCELLED_DT ,
1881 X_REVOKED_DT => v_asul_rec.REVOKED_DT ,
1882 X_COMMENTS => v_asul_rec.COMMENTS ,
1883 X_AV_STND_UNIT_LVL_ID => v_asul_rec.AV_STND_UNIT_LVL_ID ,
1884 X_CAL_TYPE => v_asul_rec.CAL_TYPE ,
1885 X_CI_SEQUENCE_NUMBER => v_asul_rec.CI_SEQUENCE_NUMBER ,
1886 X_INSTITUTION_CD => v_asul_rec.INSTITUTION_CD ,
1887 X_UNIT_DETAILS_ID => v_asul_rec.UNIT_DETAILS_ID ,
1888 X_TST_RSLT_DTLS_ID => v_asul_rec.TST_RSLT_DTLS_ID ,
1889 X_MODE => 'R' ,
1890 X_QUAL_DETS_ID => v_asul_rec.QUAL_DETS_ID -- Added column to tbh call w.r.t to ARCR032 (Bug# 2233334)
1891 );
1892 -- ***************************************************************************************
1893 END LOOP;
1894 EXCEPTION
1895 WHEN e_resource_busy THEN
1896 IF (c_adv_stnd_unit_level%ISOPEN) THEN
1897 CLOSE c_adv_stnd_unit_level;
1898 END IF;
1899 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(v_person_rec.person_id) || ' '||
1900 FND_MESSAGE.GET_STRING('IGS','IGS_AV_LVL_UPD_ANOTHER_PRC'));
1901 RAISE;
1902 WHEN OTHERS THEN
1903 RAISE;
1904 END;
1905 EXCEPTION
1906 WHEN e_resource_busy THEN
1907 RETCODE:=2;
1908 END;
1909 IF RETCODE='0' THEN
1910 lv_count:=lv_count+1;
1911 END IF;
1912 END LOOP; --End of Person Id Loop
1913 errbuf:=FND_MESSAGE.GET_STRING('IGS', 'IGS_GE_TOTAL_REC_PROCESSED')|| ' ' ||to_char(lv_count) ;
1914 retcode:=0;
1915 EXCEPTION
1916 WHEN OTHERS THEN
1917 ERRBUF:= FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1918 retcode:=2;
1919 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
1920 END;
1921 END advp_upd_as_pe_expry;
1922 PROCEDURE advp_create_basis(
1923 p_person_id IN NUMBER ,
1924 p_course_cd IN VARCHAR2 ,
1925 p_version_number IN NUMBER ) IS
1926 CURSOR C_AV_UNT is
1927 select AV_STND_UNIT_ID from IGS_AV_STND_UNIT_ALL unt where
1928 person_id =p_person_id and
1929 as_course_cd = p_course_cd and
1930 as_version_number = p_version_number and
1931 not exists ( select 1 from IGS_AV_STD_UNT_BASIS_ALL basis where basis.AV_STND_UNIT_ID= unt.AV_STND_UNIT_ID) ;
1932
1933 CURSOR C_AV_UNT_LVL is
1934 select AV_STND_UNIT_LVL_ID from IGS_AV_STND_UNIT_LVL_ALL ulvl where
1935 person_id =p_person_id and
1936 as_course_cd = p_course_cd and
1937 as_version_number = p_version_number and
1938 not exists ( select 1 from IGS_AV_STD_ULVLBASIS_ALL basis where basis.AV_STND_UNIT_LVL_ID= ulvl.AV_STND_UNIT_LVL_ID) ;
1939
1940 lv_rowid VARCHAR2(25);
1941
1942 BEGIN
1943
1944
1945 FOR V_AV_UNT IN C_AV_UNT LOOP
1946 Igs_Av_Std_Unt_Basis_Pkg.Insert_Row (
1947 X_Mode => 'R',
1948 X_RowId => lv_rowid,
1949 X_Av_Stnd_Unit_Id => V_AV_UNT.AV_STND_UNIT_ID,
1950 X_Basis_Course_Type => null,
1951 X_Basis_Year => null,
1952 X_Basis_Completion_Ind => null
1953 ,X_ORG_ID => FND_PROFILE.VALUE('ORG_ID')
1954 );
1955 lv_rowid :=null;
1956 END LOOP;
1957
1958 FOR V_AV_UNT IN C_AV_UNT_LVL LOOP
1959 Igs_Av_Std_Ulvlbasis_Pkg.Insert_Row (
1960 X_Mode => 'R',
1961 X_RowId => lv_rowid ,
1962 X_Av_Stnd_Unit_Lvl_Id => V_AV_UNT.AV_STND_UNIT_LVL_ID,
1963 X_Basis_Course_Type => null,
1964 X_Basis_Year => null,
1965 X_Basis_Completion_Ind => null
1966 ,X_ORG_ID => FND_PROFILE.VALUE('ORG_ID')
1967 );
1968 lv_rowid :=null;
1969 END LOOP;
1970 commit;
1971
1972 END advp_create_basis;
1973 FUNCTION advp_val_basis_year(
1974 p_basis_year IN NUMBER ,
1975 p_course_cd IN VARCHAR2 ,
1976 p_version_number IN NUMBER ,
1977 p_message_name OUT NOCOPY VARCHAR2,
1978 p_return_type OUT NOCOPY VARCHAR2 )
1979 RETURN VARCHAR2 IS
1980 gv_other_detail VARCHAR2(255);
1981 BEGIN -- advp_val_basis_year
1982 -- validate the basis year
1983 DECLARE
1984 v_qualification_recency IGS_PS_VER.qualification_recency%TYPE;
1985 CURSOR c_qualification_recency IS
1986 SELECT qualification_recency
1987 FROM IGS_PS_VER
1988 WHERE course_cd = p_course_cd AND
1989 version_number = p_version_number;
1990 BEGIN
1991 p_message_name := null;
1992 -- Validate input parameter
1993 IF (p_basis_year IS NULL OR
1994 p_course_cd IS NULL OR
1995 p_version_number IS NULL) THEN
1996 RETURN 'Y';
1997 END IF;
1998 -- Validate that basis_year is not greater than the current year.(E)
1999 IF (p_basis_year > TO_NUMBER(SUBSTR(IGS_GE_DATE.IGSCHAR(SYSDATE),1,4))) THEN
2000 p_message_name := 'IGS_AV_LYENR_NOTGT_CURYR';
2001 p_return_type := 'E';
2002 RETURN 'Y';
2003 END IF;
2004 -- Validate that basis_yr is not outside the recency for the IGS_PS_COURSE version (W)
2005 OPEN c_qualification_recency;
2006 FETCH c_qualification_recency INTO v_qualification_recency;
2007 IF (c_qualification_recency%NOTFOUND) THEN
2008 CLOSE c_qualification_recency;
2009 RAISE NO_DATA_FOUND;
2010 END IF;
2011 CLOSE c_qualification_recency;
2012 IF (p_basis_year <
2013 TO_NUMBER(SUBSTR(IGS_GE_DATE.IGSCHAR(SYSDATE),1,4)) - v_qualification_recency) THEN
2014 p_message_name := 'IGS_AV_LRENR_OUTSIDE_QUALIFY';
2015 p_return_type := 'W';
2016 RETURN 'Y';
2017 END IF;
2018 RETURN 'Y';
2019 EXCEPTION
2020 WHEN OTHERS THEN
2021 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
2022 Fnd_Message.Set_Token('NAME','IGS_AV_VAL_ASULEB.ADVP_VAL_BASIS_YEAR');
2023 App_Exception.Raise_Exception;
2024 IGS_GE_MSG_STACK.ADD;
2025 END;
2026 END advp_val_basis_year;
2027
2028 FUNCTION eval_unit_repeat (
2029 p_person_id IN NUMBER,
2030 p_load_cal_type IN VARCHAR2,
2031 p_load_cal_seq_number IN NUMBER,
2032 p_uoo_id IN NUMBER,
2033 p_program_cd IN VARCHAR2,
2034 p_program_version IN NUMBER,
2035 p_message IN OUT NOCOPY VARCHAR2,
2036 p_deny_warn IN VARCHAR2,
2037 p_repeat_tag OUT NOCOPY VARCHAR2 ,
2038 p_unit_cd IN VARCHAR2 ,
2039 p_unit_version IN NUMBER,
2040 p_calling_obj IN VARCHAR2
2041 ) RETURN VARCHAR2 AS
2042
2043 -- sepalani 22-Mar-2006 Bug# 5104563
2044 --
2045 -- Cursor to select all the Unit Attempts of the Student.
2046 --
2047 CURSOR cur_student_attempts (
2048 cp_unit_cd igs_ps_unit_ver.unit_cd%TYPE,
2049 cp_version_number igs_ps_unit_ver.version_number%TYPE
2050 ) IS
2051 SELECT 'X'
2052 FROM igs_en_su_attempt
2053 WHERE person_id = p_person_id
2054 AND ((unit_attempt_status IN ('ENROLLED', 'DISCONTIN','COMPLETED','INVALID','UNCONFIRM'))
2055 OR (unit_attempt_status = 'WAITLISTED' AND FND_PROFILE.VALUE('IGS_EN_VAL_WLST') ='Y'))
2056 AND (unit_cd, version_number) IN
2057 (SELECT unit_cd,
2058 version_number
2059 FROM igs_ps_unit_ver
2060 WHERE (unit_cd = cp_unit_cd AND version_number = cp_version_number)
2061 OR rpt_fmly_id =
2062 ( SELECT psu.rpt_fmly_id
2063 FROM igs_ps_unit_ver psu,
2064 igs_ps_rpt_fmly rep
2065 WHERE psu.unit_cd = cp_unit_cd
2066 AND psu.version_number = cp_version_number
2067 AND psu.rpt_fmly_id = rep.rpt_fmly_id
2068 AND NVL(rep.closed_ind,'N') = 'N' ));
2069 --
2070 -- Cursor to find if the unit version is repeatable
2071 --
2072 CURSOR cur_unit_repeat_for_cp(cp_unit_cd igs_ps_unit_ver.unit_cd%TYPE,
2073 cp_version_number igs_ps_unit_ver.version_number%TYPE) IS
2074 SELECT repeatable_ind
2075 FROM igs_ps_unit_ver
2076 WHERE unit_cd = cp_unit_cd
2077 AND version_number = cp_version_number;
2078
2079 -- sepalani 22-Mar-2006 Bug# 5104563
2080 v_student_attempts CHAR := 'Y';
2081 l_unit_repeat BOOLEAN := FALSE;
2082 v_repeatable_ind CHAR := 'Y';
2083
2084 BEGIN
2085
2086 --
2087 -- sepalani 22-Mar-2006 Bug# 5104563
2088 -- "eval_unit_repeat" function returns true, if the unit is repeatable
2089 -- it also returns true when the unit has "Repeat set to None" and For Reenroll
2090 --
2091
2092 l_unit_repeat := igs_en_elgbl_unit.eval_unit_repeat (
2093 p_person_id => p_person_id ,
2094 p_load_cal_type => p_load_cal_type ,
2095 p_load_cal_seq_number => p_load_cal_seq_number ,
2096 p_uoo_id => p_uoo_id ,
2097 p_program_cd => p_program_cd ,
2098 p_program_version => p_program_version ,
2099 p_message => p_message ,
2100 p_deny_warn => p_deny_warn ,
2101 p_repeat_tag => p_repeat_tag ,
2102 p_unit_cd => p_unit_cd ,
2103 p_unit_version => p_unit_version ,
2104 p_calling_obj => p_calling_obj);
2105
2106 --
2107 -- open the cursor to find the status of repeatable ind for a given unit and version of the unit.
2108 -- Logic
2109 -- v_repeatable_ind = 'X' --> Repeat set to None (Not Allowed)
2110 -- v_repeatable_ind = 'Y' --> Reenroll Allowed
2111 -- v_repeatable_ind = 'Y' --> Repeat Allowed
2112
2113 OPEN cur_unit_repeat_for_cp(p_unit_cd,p_unit_version);
2114 FETCH cur_unit_repeat_for_cp into v_repeatable_ind;
2115 CLOSE cur_unit_repeat_for_cp;
2116
2117 --
2118 -- open the cursor to find out whether the student has units enrolled
2119 -- for a given unit and version of the unit.
2120 --
2121
2122 OPEN cur_student_attempts(p_unit_cd,p_unit_version);
2123 FETCH cur_student_attempts into v_student_attempts;
2124 CLOSE cur_student_attempts;
2125
2126 --
2127 -- Check if the unit is repeatable
2128 -- or
2129 -- if the student has "Repeat not Allowed" and enrolled the same course.
2130 -- if the above evaluates to true then throw exception.
2131 -- otherwise grant advanced standing
2132 --
2133
2134 IF l_unit_repeat = FALSE OR
2135 (l_unit_repeat = TRUE AND v_repeatable_ind = 'X' AND v_student_attempts = 'X') THEN
2136 RETURN 'N';
2137 ELSE
2138 RETURN 'Y';
2139 END IF;
2140
2141 RETURN 'Y';
2142 EXCEPTION
2143 WHEN OTHERS THEN
2144 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
2145 Fnd_Message.Set_Token('NAME','IGS_AV_VAL_ASULEB.eval_unit_repeat');
2146 App_Exception.Raise_Exception;
2147 IGS_GE_MSG_STACK.ADD;
2148 END eval_unit_repeat;
2149
2150 PROCEDURE advp_updt_advstnd(
2151 p_person_id IN NUMBER ,
2152 p_course_cd IN VARCHAR2 ,
2153 p_version_number IN NUMBER ) IS
2154
2155 CURSOR c_adv_stnd
2156 IS
2157 SELECT *
2158 FROM igs_av_adv_standing_all
2159 WHERE person_id = p_person_id
2160 AND course_cd = p_course_cd
2161 AND version_number = p_version_number;
2162
2163 l_app_val NUMBER;
2164 l_grnt_val NUMBER;
2165
2166 CURSOR c_approved (
2167 p_exemption_institution_cd igs_av_stnd_unit_all.exemption_institution_cd%TYPE
2168 )
2169 IS
2170 SELECT nvl(SUM (cp) , 0)
2171 FROM (SELECT SUM (achievable_credit_points) cp
2172 FROM igs_av_stnd_unit_all unt
2173 WHERE unt.exemption_institution_cd = p_exemption_institution_cd
2174 AND unt.person_id = p_person_id
2175 AND p_course_cd = unt.as_course_cd
2176 AND p_version_number = unt.as_version_number
2177 AND unt.s_adv_stnd_granting_status = 'APPROVED'
2178 UNION ALL
2179 SELECT SUM (credit_points) cp
2180 FROM igs_av_stnd_unit_lvl_all unt
2181 WHERE unt.exemption_institution_cd = p_exemption_institution_cd
2182 AND unt.person_id = p_person_id
2183 AND p_course_cd = unt.as_course_cd
2184 AND p_version_number = unt.as_version_number
2185 AND unt.s_adv_stnd_granting_status = 'APPROVED');
2186
2187 CURSOR c_granted (
2188 p_exemption_institution_cd igs_av_stnd_unit_all.exemption_institution_cd%TYPE
2189 )
2190 IS
2191 SELECT nvl(SUM (cp),0)
2192 FROM (SELECT SUM (achievable_credit_points) cp
2193 FROM igs_av_stnd_unit_all unt
2194 WHERE unt.exemption_institution_cd = p_exemption_institution_cd
2195 AND unt.person_id = p_person_id
2196 AND p_course_cd = unt.as_course_cd
2197 AND p_version_number = unt.as_version_number
2198 AND unt.s_adv_stnd_granting_status = 'GRANTED'
2199 UNION ALL
2200 SELECT SUM (credit_points) cp
2201 FROM igs_av_stnd_unit_lvl_all unt
2202 WHERE unt.exemption_institution_cd = p_exemption_institution_cd
2203 AND unt.person_id = p_person_id
2204 AND p_course_cd = unt.as_course_cd
2205 AND p_version_number = unt.as_version_number
2206 AND unt.s_adv_stnd_granting_status = 'GRANTED');
2207 BEGIN
2208 FOR l_adv_stnd IN c_adv_stnd
2209 LOOP
2210 l_app_val := 0;
2211 l_grnt_val := 0;
2212
2213 OPEN c_approved (l_adv_stnd.exemption_institution_cd);
2214
2215 FETCH c_approved
2216 INTO l_app_val;
2217
2218 CLOSE c_approved;
2219
2220 OPEN c_granted (l_adv_stnd.exemption_institution_cd);
2221
2222 FETCH c_granted
2223 INTO l_grnt_val;
2224
2225 CLOSE c_granted;
2226
2227 UPDATE igs_av_adv_standing_all
2228 SET total_exmptn_approved = l_app_val,
2229 total_exmptn_granted = l_grnt_val
2230 WHERE person_id = p_person_id
2231 AND course_cd = p_course_cd
2232 AND version_number = p_version_number
2233 AND exemption_institution_cd = l_adv_stnd.exemption_institution_cd;
2234 END LOOP;
2235 EXCEPTION
2236 WHEN OTHERS
2237 THEN
2238 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
2239 Fnd_Message.Set_Token('NAME','IGS_AV_VAL_ASULEB.advp_updt_advstnd' || sqlerrm);
2240 App_Exception.Raise_Exception;
2241 IGS_GE_MSG_STACK.ADD;
2242
2243 END advp_updt_advstnd;
2244
2245
2246 PROCEDURE adv_cal_creditpts (
2247 p_personid IN NUMBER,
2248 p_coursecd IN VARCHAR2,
2249 p_unitsetcd IN VARCHAR2,
2250 p_usverno IN VARCHAR2,
2251 creditpts OUT NOCOPY NUMBER
2252 )
2253 IS
2254 v_credit_pts_1 NUMBER (10, 5);
2255 v_credit_pts_2 NUMBER (10, 5);
2256 v_credit_pts_3 NUMBER (10, 5);
2257
2258
2259
2260 CURSOR c_credit_pts_1 (
2261 personid NUMBER,
2262 coursecd VARCHAR2,
2263 unitsetcd VARCHAR2,
2264 usverno VARCHAR2
2265 )
2266 IS
2267 SELECT SUM
2268 (igs_as_calc_award_mark.get_earned_cp (he.person_id,
2269 he.course_cd,
2270 he.unit_cd,
2271 he.version_number,
2272 he.unit_attempt_status,
2273 he.cal_type,
2274 he.ci_sequence_number,
2275 he.uoo_id,
2276 NULL,
2277 NULL
2278 )
2279 )
2280 FROM igs_en_sua_year_v he
2281 WHERE he.person_id = personid
2282 AND he.course_cd = coursecd
2283 AND he.unit_set_cd = unitsetcd
2284 AND he.us_version_number = usverno;
2285
2286 CURSOR c_credit_pts_2 (
2287 personid NUMBER,
2288 coursecd VARCHAR2,
2289 unitsetcd VARCHAR2,
2290 usverno VARCHAR2
2291 )
2292 IS
2293 SELECT SUM (a.achievable_credit_points)
2294 FROM igs_av_stnd_unit_all a, igs_pe_hz_parties ipz
2295 WHERE a.s_adv_stnd_granting_status = 'GRANTED'
2296 AND a.s_adv_stnd_recognition_type = 'CREDIT'
2297 AND a.exemption_institution_cd(+) = ipz.oss_org_unit_cd
2298 AND (a.cal_type, a.ci_sequence_number) IN (
2299 SELECT ca.load_cal_type, ca.load_ci_sequence_number
2300 FROM igs_en_sua_year_v susa, igs_ca_teach_to_load_v ca
2301 WHERE susa.person_id = a.person_id
2302 AND susa.course_cd = a.as_course_cd
2303 AND susa.cal_type = ca.teach_cal_type
2304 AND susa.ci_sequence_number = ca.teach_ci_sequence_number
2305 AND susa.unit_set_cd = unitsetcd
2306 AND susa.us_version_number = usverno)
2307 AND ((personid = person_id) AND (coursecd = as_course_cd));
2308
2309 CURSOR c_credit_pts_3 (personid NUMBER, coursecd VARCHAR2)
2310 IS
2311 SELECT SUM (a.credit_points)
2312 FROM igs_av_stnd_unit_lvl_all a, igs_pe_hz_parties ipz
2313 WHERE a.s_adv_stnd_granting_status = 'GRANTED'
2314 AND a.exemption_institution_cd(+) = ipz.oss_org_unit_cd
2315 AND (personid = person_id)
2316 AND (coursecd = as_course_cd);
2317 BEGIN
2318 creditpts :=0;
2319
2320 begin
2321 OPEN c_credit_pts_1 (p_personid, p_coursecd, p_unitsetcd, p_usverno);
2322
2323 FETCH c_credit_pts_1
2324 INTO v_credit_pts_1;
2325
2326 CLOSE c_credit_pts_1;
2327 end;
2328
2329 begin
2330 OPEN c_credit_pts_2 (p_personid, p_coursecd, p_unitsetcd, p_usverno);
2331
2332 FETCH c_credit_pts_2
2333 INTO v_credit_pts_2;
2334
2335 CLOSE c_credit_pts_2;
2336 end;
2337
2338 begin
2339 OPEN c_credit_pts_3 (p_personid, p_coursecd);
2340
2341 FETCH c_credit_pts_3
2342 INTO v_credit_pts_3;
2343
2344 CLOSE c_credit_pts_3;
2345 end;
2346 creditpts := nvl(v_credit_pts_1,0) + nvl(v_credit_pts_2,0) + nvl(v_credit_pts_3,0);
2347 END adv_cal_creditpts;
2348
2349
2350
2351 END IGS_AV_GEN_001;