[Home] [Help]
PACKAGE BODY: APPS.IGS_PR_GEN_006
Source
1 PACKAGE BODY IGS_PR_GEN_006 AS
2 /* $Header: IGSPR27B.pls 120.0 2005/07/05 11:48:45 appldev noship $ */
3 /*
4 ||==============================================================================||
5 || Created By : Nalin Kumar ||
6 || Created On : 19-NOV-2002 ||
7 || Purpose : ||
8 || Known limitations, enhancements or remarks : ||
9 || Change History : ||
10 || Who When What ||
11 || (reverse chronological order - newest change first) ||
12 ||==============================================================================||
13 || sarakshi 16-Nov-2004 Enh#4000939, added column FUTURE_DATED_TRANS_FLAG in the update row call of IGS_EN_STDNT_PS_ATT_PKG in function IGS_PR_UPD_SCA_STATUS
14 || ssawhney 17-feb-2003 Bug : 2758856 : Added the parameter x_external_reference
15 || in the call to IGS_PE_PERS_ENCUMB_PKG.UPDATE_ROW
16 ||
17 || NALKUMAR 19-NOV-2002 Bug NO: 2658550 ||
18 || Modified this object as per the FA110 PR Enh. ||
19 ||==============================================================================||
20 || pkpatel 07-OCT-2002 Bug No: 2600842 ||
21 || Added the parameter auth_resp_id in the call to THB igs_pe_pers_encumb_pkg
22 | nmankodi 11-Apr-2005 fnd_user.customer_id column has been changed to
23 | fnd_user.person_party_id as an ebizsuite wide TCA mandate.
24 ||==============================================================================||
25 */
26
27 FUNCTION IGS_PR_GET_SCSC_COMP(
28 p_person_id IN NUMBER ,
29 p_course_cd IN VARCHAR2 ,
30 p_version_number IN NUMBER ,
31 p_cst_sequence_number IN NUMBER )
32 RETURN VARCHAR2 IS
33 gv_other_detail VARCHAR2(255);
34 BEGIN -- IGS_PR_get_scsc_comp
35 -- Get whether course stage has been manually completed for
36 -- a student course attempt.This is signified by the existence of
37 -- a student_crs_stage_cmpltn record.
38 DECLARE
39 cst_y CONSTANT VARCHAR2(1) := 'Y';
40 cst_n CONSTANT VARCHAR2(1) := 'N';
41 v_dummy VARCHAR2(1);
42 /* CURSOR c_scsc IS
43 SELECT 'X'
44 FROM student_crs_stage_cmpltn scsc
45 WHERE scsc.person_id = p_person_id AND
46 scsc.course_cd = p_course_cd AND
47 scsc.version_number = p_version_number AND
48 scsc.cst_sequence_number = p_cst_sequence_number; */
49 BEGIN
50
51 /*
52 OPEN c_scsc;
53 FETCH c_scsc INTO v_dummy;
54 IF c_scsc%FOUND THEN
55 CLOSE c_scsc;
56 RETURN cst_y;
57 ELSE
58 CLOSE c_scsc;
59 RETURN cst_n;
60 END IF;
61 */
62 RETURN cst_n;
63 END;
64 EXCEPTION
65 WHEN OTHERS THEN
66 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
67 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.IGS_PR_GET_SCSC_COMP');
68 IGS_GE_MSG_STACK.ADD;
69 App_Exception.Raise_Exception;
70 END IGS_PR_get_scsc_comp;
71
72 FUNCTION IGS_PR_get_spo_aply_dt(
73 p_decision_status IN VARCHAR2 ,
74 p_old_applied_dt IN DATE ,
75 p_new_applied_dt IN DATE ,
76 p_old_encmb_course_group_cd IN VARCHAR2 ,
77 p_new_encmb_course_group_cd IN VARCHAR2 ,
78 p_old_restricted_enrolment_cp IN NUMBER ,
79 p_new_restricted_enrolment_cp IN NUMBER ,
80 p_old_restricted_attend_type IN VARCHAR2 ,
81 p_new_restricted_attend_type IN VARCHAR2 ,
82 p_old_expiry_dt IN DATE ,
83 p_new_expiry_dt IN DATE ,
84 p_old_duration IN NUMBER ,
85 p_new_duration IN NUMBER ,
86 p_old_duration_type IN VARCHAR2 ,
87 p_new_duration_type IN VARCHAR2 ,
88 p_out_applied_dt OUT NOCOPY DATE )
89 RETURN BOOLEAN IS
90 gv_other_detail VARCHAR2(255);
91 BEGIN -- IGS_PR_get_spo_aply_dt
92 -- If the student progression outcome details have been changed
93 -- return the correct applied date.
94 p_out_applied_dt := NULL;
95 IF p_decision_status <> 'APPROVED' OR
96 TRUNC(NVL(p_new_applied_dt, IGS_GE_DATE.IGSDATE('9999/01/01'))) <>
97 TRUNC(NVL(p_old_applied_dt, IGS_GE_DATE.IGSDATE('9999/01/01'))) THEN
98 RETURN TRUE;
99 END IF;
100 IF NVL(p_old_encmb_course_group_cd, 'NULL') <>
101 NVL(p_new_encmb_course_group_cd, 'NULL') OR
102 NVL(p_old_restricted_enrolment_cp, 0) <>
103 NVL(p_new_restricted_enrolment_cp, 0) OR
104 NVL(p_old_restricted_attend_type, 'NULL') <>
105 NVL(p_new_restricted_attend_type, 'NULL') OR
106 TRUNC(NVL(p_old_expiry_dt, IGS_GE_DATE.IGSDATE('0001/01/01'))) <>
107 TRUNC(NVL(p_new_expiry_dt, IGS_GE_DATE.IGSDATE('0001/01/01'))) OR
108 NVL(p_old_duration, 0) <>
109 NVL(p_new_duration, 0) OR
110 NVL(p_old_duration_type, 'NULL') <>
111 NVL(p_new_duration_type, 'NULL') THEN
112 IF TRUNC(p_new_applied_dt) <>
113 TRUNC(IGS_GE_DATE.IGSDATE('0001/01/01')) THEN
114 p_out_applied_dt := IGS_GE_DATE.IGSDATE('0001/01/01');
115 RETURN FALSE;
116 END IF;
117 END IF;
118 RETURN TRUE;
119 EXCEPTION
120 WHEN OTHERS THEN
121 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
122 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.IGS_PR_GET_SPO_APLY_DT');
123 IGS_GE_MSG_STACK.ADD;
124 App_Exception.Raise_Exception;
125 END IGS_PR_get_spo_aply_dt;
126
127 FUNCTION IGS_PR_GET_SPO_CMT(
128 p_person_id IN NUMBER ,
129 p_course_cd IN VARCHAR2 ,
130 p_version_number IN NUMBER ,
131 p_org_unit_cd IN VARCHAR2 ,
132 p_ou_start_dt IN DATE ,
133 p_course_type IN VARCHAR2 ,
134 p_location_cd IN VARCHAR2 ,
135 p_attendance_mode IN VARCHAR2 )
136 RETURN VARCHAR2 IS
137 gv_other_detail VARCHAR2(255);
138 BEGIN -- IGS_PR_get_spo_cmt
139 -- Get whether student progression outcome is covered by the nominated
140 -- committee structure
141 DECLARE
142 v_ou_rel_found BOOLEAN DEFAULT FALSE;
143 v_dummy VARCHAR2(1);
144 CURSOR c_crv_cow IS
145 SELECT crv.course_type,
146 cow.org_unit_cd,
147 cow.ou_start_dt
148 FROM IGS_EN_STDNT_PS_ATT sca,
149 IGS_PS_VER crv,
150 IGS_PS_OWN cow
151 WHERE sca.person_id = p_person_id AND
152 sca.course_cd = p_course_cd AND
153 (sca.version_number = p_version_number OR
154 p_version_number IS NULL) AND
155 (sca.location_cd = p_location_cd OR
156 p_location_cd IS NULL) AND
157 (sca.attendance_mode = p_attendance_mode OR
158 p_attendance_mode IS NULL) AND
159 crv.course_cd = sca.course_cd AND
160 crv.version_number = sca.version_number AND
161 crv.course_cd = cow.course_cd AND
162 crv.version_number = cow.version_number AND
163 (crv.course_type = p_course_type OR
164 p_course_type IS NULL);
165 CURSOR c_our (
166 cp_cow_org_unit_cd IGS_OR_UNIT.org_unit_cd%TYPE,
167 cp_cow_ou_start_dt IGS_PS_VER.start_dt%TYPE,
168 cp_course_type IGS_PS_VER.course_type%TYPE) IS
169 SELECT 'X'
170 FROM IGS_OR_UNIT_REL our
171 WHERE our.parent_org_unit_cd = p_org_unit_cd AND
172 our.parent_start_dt = p_ou_start_dt AND
173 our.child_org_unit_cd = cp_cow_org_unit_cd AND
174 our.child_start_dt = cp_cow_ou_start_dt AND
175 our.logical_delete_dt IS NULL AND
176 EXISTS (
177 SELECT 'X'
178 FROM IGS_OR_REL_PS_TYPE ourct
179 WHERE our.parent_org_unit_cd = ourct.parent_org_unit_cd AND
180 our.parent_start_dt = ourct.parent_start_dt AND
181 our.child_org_unit_cd = ourct.child_org_unit_cd AND
182 our.child_start_dt = ourct.child_start_dt AND
183 our.create_dt = ourct.our_create_dt AND
184 ourct.course_type = cp_course_type);
185 BEGIN
186 FOR v_crv_cow_rec IN c_crv_cow LOOP
187 IF v_crv_cow_rec.org_unit_cd = p_org_unit_cd AND
188 v_crv_cow_rec.ou_start_dt = p_ou_start_dt THEN
189 RETURN 'Y';
190 END IF;
191 -- Firstly search for a direct match to an organisational unit with the
192 -- course type qualification, if doesn't then move onto a standard ou
193 -- relationship test.
194 OPEN c_our (
195 v_crv_cow_rec.org_unit_cd,
196 v_crv_cow_rec.ou_start_dt,
197 v_crv_cow_rec.course_type);
198 FETCH c_our INTO v_dummy;
199 IF c_our%FOUND THEN
200 CLOSE c_our;
201 v_ou_rel_found := TRUE;
202 EXIT;
203 ELSE
204 CLOSE c_our;
205 IF IGS_OR_GEN_001.ORGP_GET_WITHIN_OU (
206 p_org_unit_cd,
207 p_ou_start_dt,
208 v_crv_cow_rec.org_unit_cd,
209 v_crv_cow_rec.ou_start_dt,
210 'N') = 'Y' THEN
211 v_ou_rel_found := TRUE;
212 EXIT;
213 END IF;
214 END IF;
215 END LOOP;
216 IF v_ou_rel_found THEN
217 RETURN 'Y';
218 END IF;
219 RETURN 'N';
220 EXCEPTION
221 WHEN OTHERS THEN
222 IF c_crv_cow%ISOPEN THEN
223 CLOSE c_crv_cow;
224 END IF;
225 IF c_our%ISOPEN THEN
226 CLOSE c_our;
227 END IF;
228 RAISE;
229 END;
230 EXCEPTION
231 WHEN OTHERS THEN
232 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
233 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.IGS_PR_GET_SPO_CMT');
234 IGS_GE_MSG_STACK.ADD;
235 App_Exception.Raise_Exception;
236 END IGS_PR_get_spo_cmt;
237 FUNCTION IGS_PR_get_spo_expiry(
238 p_person_id IN NUMBER ,
239 p_course_cd IN VARCHAR2 ,
240 p_sequence_number IN NUMBER ,
241 p_spo_expiry_dt IN DATE ,
242 p_expiry_dt OUT NOCOPY DATE )
243 RETURN VARCHAR2 IS
244 gv_other_detail VARCHAR2(255);
245 BEGIN -- IGS_PR_get_spo_expiry
246 -- Calculates the expiry date of a student progression outcome record.
247 -- Note: an open-ended expiry date returns with the value 01/01/4000.
248 -- An un-determinable expiry date returns NULL.
249 DECLARE
250 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
251 cst_progress CONSTANT VARCHAR2(10) := 'PROGRESS';
252 cst_normal CONSTANT VARCHAR2(10) := 'NORMAL';
253 cst_open CONSTANT VARCHAR2(10) := 'OPEN';
254 cst_current CONSTANT VARCHAR2(10) := 'CURRENT';
255 cst_expired CONSTANT VARCHAR2(10) := 'EXPIRED';
256 v_expiry_dt IGS_CA_INST.end_dt%TYPE;
257 v_period_found BOOLEAN DEFAULT FALSE;
258 v_ci_count INTEGER DEFAULT 0;
259 v_dummy VARCHAR2(1);
260 CURSOR c_spo_ci_sca IS
261 SELECT spo.prg_cal_type,
262 spo.prg_ci_sequence_number,
263 spo.duration,
264 spo.duration_type,
265 ci.start_dt,
266 sca.version_number
267 FROM IGS_PR_STDNT_PR_OU spo,
268 IGS_CA_INST ci,
269 IGS_EN_STDNT_PS_ATT sca
270 WHERE spo.person_id = p_person_id AND
271 spo.course_cd = p_course_cd AND
272 spo.sequence_number = p_sequence_number AND
273 ci.cal_type = spo.prg_cal_type AND
274 ci.sequence_number = spo.prg_ci_sequence_number AND
275 sca.person_id = spo.person_id AND
276 sca.course_cd = spo.course_cd;
277 v_spo_rec c_spo_ci_sca%ROWTYPE;
278 CURSOR c_ci_ct_cs (
279 cp_prg_start_dt IGS_CA_INST.start_dt%TYPE,
280 cp_sca_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE,
281 cp_spo_duration_type IGS_PR_STDNT_PR_OU.duration_type%TYPE,
282 cp_spo_prg_cal_type IGS_PR_STDNT_PR_OU.prg_cal_type%TYPE) IS
283 SELECT ci.cal_type,
284 ci.sequence_number
285 FROM IGS_CA_INST ci,
286 IGS_CA_TYPE ct,
287 IGS_CA_STAT cs
288 WHERE ct.cal_type = ci.cal_type AND
289 ct.s_cal_cat = cst_progress AND
290 cs.cal_status = ci.cal_status AND
291 cs.s_cal_status = cst_active AND
292 ci.start_dt > cp_prg_start_dt AND
293 ( EXISTS (SELECT 'x'
294 FROM IGS_PR_S_PRG_CAL spc1,
295 IGS_PR_S_PRG_CAL spc2
296 WHERE spc1.s_control_num = 1 AND
297 spc2.s_control_num = 1 AND
298 spc1.prg_cal_type = cp_spo_prg_cal_type AND
299 spc2.prg_cal_type = ci.cal_type AND
300 spc1.stream_num = spc2.stream_num) OR
301 EXISTS (SELECT 'x'
302 FROM IGS_PR_S_OU_PRG_CAL sopc1,
303 IGS_PR_S_OU_PRG_CAL sopc2
304 WHERE IGS_PR_GEN_001.PRGP_GET_CRV_CMT( p_course_cd,
305 cp_sca_version_number,
306 sopc1.org_unit_cd,
307 sopc1.ou_start_dt) = 'Y' AND
308 sopc1.prg_cal_type = cp_spo_prg_cal_type AND
309 sopc2.org_unit_cd = sopc1.org_unit_cd AND
310 sopc2.ou_start_dt = sopc1.ou_start_dt AND
311 sopc2.prg_cal_type = ci.cal_type AND
312 sopc1.stream_num = sopc2.stream_num) OR
313 EXISTS (SELECT 'x'
314 FROM IGS_PR_S_CRV_PRG_CAL scpc1,
315 IGS_PR_S_CRV_PRG_CAL scpc2
316 WHERE scpc1.course_cd = p_course_cd AND
317 scpc1.version_number = cp_sca_version_number AND
318 scpc1.prg_cal_type = cp_spo_prg_cal_type AND
319 scpc2.course_cd = scpc1.course_cd AND
320 scpc2.version_number = scpc1.version_number AND
321 scpc2.prg_cal_type = ci.cal_type AND
322 scpc1.stream_num = scpc2.stream_num)) AND
323 (cp_spo_duration_type = cst_normal OR
324 (IGS_PR_GEN_001.PRGP_get_drtn_efctv (
325 ci.cal_type,
326 ci.sequence_number,
327 p_person_id,
328 p_course_cd) = 'Y') AND
329 EXISTS (
330 SELECT 'x'
331 FROM IGS_EN_SU_ATTEMPT sua,
332 IGS_CA_INST_REL cir
333 WHERE sua.person_id = p_person_id AND
334 sua.course_cd = p_coursE_cd AND
335 sua.unit_attempt_status IN ('ENROLLED','COMPLETED','DISCONTIN') AND
336 cir.sup_cal_type = ci.cal_type AND
337 cir.sup_ci_sequence_number = ci.sequence_number AND
338 cir.sub_cal_type = sua.cal_type AND
339 cir.sub_ci_sequence_number = sua.ci_sequence_number))
340 ORDER BY ci.start_dt;
341 BEGIN
342 -- Set the default expiry date
343 p_expiry_dt := NULL;
344 -- If the expiry date is set then check it and return accordingly.
345 IF p_spo_expiry_dt IS NOT NULL THEN
346 p_expiry_dt := p_spo_expiry_dt;
347 IF p_spo_expiry_dt <= TRUNC(SYSDATE) THEN
348 RETURN cst_expired;
349 ELSE
350 RETURN cst_current;
351 END IF;
352 END IF;
353 -- Select IGS_PR_STDNT_PR_OU record
354 OPEN c_spo_ci_sca;
355 FETCH c_spo_ci_sca INTO v_spo_rec;
356 IF c_spo_ci_sca%NOTFOUND THEN
357 CLOSE c_spo_ci_sca;
358 RETURN NULL;
359 END IF;
360 CLOSE c_spo_ci_sca;
361 IF v_spo_rec.duration IS NULL THEN
362 RETURN cst_open;
363 END IF;
364 -- Loop through progression periods from the application period forward until
365 -- the ending period is found ; the calendar instance end date is the expiry
366 -- date
367 FOR v_ci_rec IN c_ci_ct_cs (
368 v_spo_rec.start_dt,
369 v_spo_rec.version_number,
370 v_spo_rec.duration_type,
371 v_spo_rec.prg_cal_type) LOOP
372 v_ci_count := v_ci_count + 1;
373 IF v_ci_count = v_spo_rec.duration THEN
374 v_expiry_dt := IGS_PR_GEN_005.IGS_PR_get_prg_pen_end(
375 v_ci_rec.cal_type,
376 v_ci_rec.sequence_number);
377 v_period_found := TRUE;
378 EXIT;
379 END IF;
380 END LOOP;
381 IF v_period_found THEN
382 p_expiry_dt := v_expiry_dt;
383 IF v_expiry_dt <= TRUNC(SYSDATE) THEN
384 RETURN cst_expired;
385 END IF;
386 END IF;
387 RETURN cst_current;
388 EXCEPTION
389 WHEN OTHERS THEN
390 IF c_spo_ci_sca%ISOPEN THEN
391 CLOSE c_spo_ci_sca;
392 END IF;
393 IF c_ci_ct_cs%ISOPEN THEN
394 CLOSE c_ci_ct_cs;
395 END IF;
396 RAISE;
397 END;
398 EXCEPTION
399 WHEN OTHERS THEN
400 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
401 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.IGS_PR_GET_SPO_EXPIRY');
402 IGS_GE_MSG_STACK.ADD;
403 App_Exception.Raise_Exception;
404 END IGS_PR_get_spo_expiry;
405
406 FUNCTION IGS_PR_get_sprc_dsp(
407 p_person_id IN NUMBER ,
408 p_course_cd IN VARCHAR2 ,
409 p_prg_cal_type IN VARCHAR2 ,
410 p_prg_ci_sequence_number IN NUMBER ,
411 p_rule_check_dt IN DATE ,
412 p_progression_rule_cat IN VARCHAR2 ,
413 p_pra_sequence_number IN NUMBER )
414 RETURN VARCHAR2 IS
415 gv_other_detail VARCHAR2(255);
416 BEGIN -- IGS_PR_get_sprc_dsp
417 -- Determine if the IGS_PR_SDT_PR_RU_CK record should be displayed.
418 -- 1. If there is an IGS_PR_STDNT_PR_OU record linked to it
419 -- 2. If it is the most recent of passed or failed record
420 DECLARE
421 v_dummy VARCHAR2(1);
422 CURSOR c_spo IS
423 SELECT 'X'
424 FROM IGS_PR_STDNT_PR_OU
425 WHERE person_id = p_person_id AND
426 course_cd = p_course_cd AND
427 prg_cal_type = p_prg_cal_type AND
428 prg_ci_sequence_number = p_prg_ci_sequence_number AND
429 rule_check_dt = p_rule_check_dt AND
430 progression_rule_cat = p_progression_rule_cat AND
431 pra_sequence_number = p_pra_sequence_number AND
432 decision_status IN ('APPROVED', 'PENDING');
433 CURSOR c_sprc IS
434 SELECT 'X'
435 FROM IGS_PR_SDT_PR_RU_CK sprc
436 WHERE sprc.person_id = p_person_id AND
437 sprc.course_cd = p_course_cd AND
438 sprc.prg_cal_type = p_prg_cal_type AND
439 sprc.prg_ci_sequence_number = p_prg_ci_sequence_number AND
440 sprc.rule_check_dt = p_rule_check_dt AND
441 sprc.progression_rule_cat = p_progression_rule_cat AND
442 sprc.pra_sequence_number = p_pra_sequence_number AND
443 sprc.rule_check_dt
444 = ( SELECT MAX(sprc2.rule_check_dt)
445 FROM IGS_PR_SDT_PR_RU_CK sprc2
446 WHERE sprc2.person_id = sprc.person_id AND
447 sprc2.course_cd = sprc.course_cd AND
448 sprc2.prg_cal_type = sprc.prg_cal_type AND
449 sprc2.prg_ci_sequence_number = sprc.prg_ci_sequence_number AND
450 sprc2.progression_rule_cat = sprc.progression_rule_cat AND
451 sprc2.pra_sequence_number = sprc.pra_sequence_number);
452 BEGIN
453 -- Check parameters
454 IF p_person_id IS NULL OR
455 p_course_cd IS NULL OR
456 p_prg_cal_type IS NULL OR
457 p_prg_ci_sequence_number IS NULL OR
458 p_rule_check_dt IS NULL OR
459 p_progression_rule_cat IS NULL OR
460 p_pra_sequence_number IS NULL THEN
461 RETURN 'N';
462 END IF;
463 -- 1. Check for IGS_PR_STDNT_PR_OU records
464 OPEN c_spo;
465 FETCH c_spo INTO v_dummy;
466 IF c_spo%FOUND THEN
467 CLOSE c_spo;
468 RETURN 'Y';
469 END IF;
470 CLOSE c_spo;
471 -- 2. Check If this is the most recent of passed or failed records
472 OPEN c_sprc;
473 FETCH c_sprc INTO v_dummy;
474 IF c_sprc%FOUND THEN
475 CLOSE c_sprc;
476 RETURN 'Y';
477 END IF;
478 CLOSE c_sprc;
479 RETURN 'N';
480 EXCEPTION
481 WHEN OTHERS THEN
482 IF c_spo%ISOPEN THEN
483 CLOSE c_spo;
484 END IF;
485 IF c_sprc%ISOPEN THEN
486 CLOSE c_sprc;
487 END IF;
488 RAISE;
489 END;
490 END IGS_PR_get_sprc_dsp;
491 FUNCTION IGS_PR_GET_STD_GPA(
492 p_person_id IN NUMBER ,
493 p_course_cd IN VARCHAR2 ,
494 p_prg_cal_type IN VARCHAR2 ,
495 p_prg_sequence_number IN NUMBER )
496 RETURN NUMBER IS
497 gv_other_detail VARCHAR2(255);
498 BEGIN -- IGS_GR_get_std_gpa
499 -- Get the 'standard' GPA figure applicable to a course version / institution.
500 -- Note: currently this routine embeds the concept of the 'standard' value
501 -- within the logic, however, in future this will be expanded in a
502 -- rule / configuration option.
503 DECLARE
504 v_std_gpa NUMBER := 0;
505 BEGIN
506 v_std_gpa := IGS_PR_GEN_001.PRGP_get_sca_gpa(
507 p_person_id,
508 p_course_cd,
509 NULL,
510 NULL,
511 p_prg_cal_type,
512 p_prg_sequence_number,
513 NULL, -- No best/worst
514 'N', -- Don't use recommended
515 'N', -- Not first attempts
516 'N'); -- Not entered grades
517 RETURN v_std_gpa;
518 END;
519 EXCEPTION
520 WHEN OTHERS THEN
521 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
522 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.IGS_PR_GET_STD_GPA');
523 IGS_GE_MSG_STACK.ADD;
524 App_Exception.Raise_Exception;
525 END IGS_PR_get_std_gpa;
526
527
528 FUNCTION IGS_PR_GET_STD_WAM(
529 p_person_id IN NUMBER ,
530 p_course_cd IN VARCHAR2 ,
531 p_course_version IN NUMBER ,
532 p_prg_cal_type IN VARCHAR2 ,
533 p_prg_sequence_number IN NUMBER )
534 RETURN NUMBER IS
535 gv_other_detail VARCHAR2(255);
536 BEGIN -- IGS_PR_get_std_wam
537 -- Get the 'standard' wam figure applicable to a course version / institution.
538 -- Note: currently this routine embeds the concept of the 'standard' value
539 -- within the logic, however, in future this will be expanded in a
540 -- rule / configuration option.
541 DECLARE
542 v_std_wam NUMBER := 0;
543 BEGIN
544 v_std_wam := IGS_PR_GEN_002.PRGP_get_sca_wam(
545 p_person_id,
546 p_course_cd,
547 p_course_version,
548 NULL,
549 NULL,
550 p_prg_cal_type,
551 p_prg_sequence_number,
552 'N', -- Don't use recommended
553 'Y'); -- Abort when missing
554 RETURN v_std_wam;
555 END;
556 EXCEPTION
557 WHEN OTHERS THEN
558 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
559 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.IGS_PR_GET_STD_WAM');
560 IGS_GE_MSG_STACK.ADD;
561 App_Exception.Raise_Exception;
562 END IGS_PR_get_std_wam;
563
564
565 FUNCTION IGS_PR_get_within_appl(
566 p_prg_cal_type IN VARCHAR2 ,
567 p_prg_sequence_number IN NUMBER ,
568 p_course_cd IN VARCHAR2 ,
569 p_version_number IN NUMBER ,
570 p_application_type IN VARCHAR2 ,
571 p_start_dt OUT NOCOPY DATE ,
572 p_cutoff_dt OUT NOCOPY DATE )
573 RETURN VARCHAR2 IS
574 gv_other_detail VARCHAR2(255);
575 BEGIN -- IGS_PR_get_within_appl
576 -- Get whether student course attempt is still within the processing bounds
577 -- of the nominated progression calendar instance.
578 -- There are three possible ranges that can be used :
579 -- INITIAL Application : only eligible if within the start/end application
580 -- period applicable to the students course
581 -- TODO Application : only eligible if within the start application / latest
582 -- of {benefit, penalty} cutoff dates
583 -- BENEFIT Application : only eligible if within the start
584 -- application / benefit cutoff period applicable to the students course
585 -- PENALTY Application : only eligible if within the start
586 -- application / penalty cutoff period applicable to the students course
587 -- The applicable start/cutoff dates are returned in OUT NOCOPY parameters.
588 DECLARE
589 cst_initial CONSTANT VARCHAR2(10) := 'INITIAL';
590 cst_todo CONSTANT VARCHAR2(10) := 'TODO';
591 cst_benefit CONSTANT VARCHAR2(10) := 'BENEFIT';
592 cst_penalty CONSTANT VARCHAR2(10) := 'PENALTY';
593 v_start_dt DATE;
594 v_cutoff_dt DATE;
595 v_benefit_dt DATE;
596 v_penalty_dt DATE;
597 v_apply_start_dt_alias IGS_PR_S_PRG_CONF.apply_start_dt_alias%TYPE;
598 v_apply_end_dt_alias IGS_PR_S_PRG_CONF.apply_end_dt_alias%TYPE;
599 v_end_benefit_dt_alias IGS_PR_S_PRG_CONF.end_benefit_dt_alias%TYPE;
600 v_end_penalty_dt_alias IGS_PR_S_PRG_CONF.end_penalty_dt_alias%TYPE;
601 v_show_cause_cutoff_dt IGS_PR_S_PRG_CONF.show_cause_cutoff_dt_alias%TYPE;
602 v_appeal_cutoff_dt IGS_PR_S_PRG_CONF.appeal_cutoff_dt_alias%TYPE;
603 v_show_cause_ind IGS_PR_S_PRG_CONF.show_cause_ind%TYPE;
604 v_apply_before_show_ind IGS_PR_S_PRG_CONF.apply_before_show_ind%TYPE;
605 v_appeal_ind IGS_PR_S_PRG_CONF.appeal_ind%TYPE;
606 v_apply_before_appeal_ind IGS_PR_S_PRG_CONF.apply_before_appeal_ind%TYPE;
607 v_count_sus_in_time_ind IGS_PR_S_PRG_CONF.count_sus_in_time_ind%TYPE;
608 v_count_exc_in_time_ind IGS_PR_S_PRG_CONF.count_exc_in_time_ind%TYPE;
609 v_calculate_wam_ind IGS_PR_S_PRG_CONF.calculate_wam_ind%TYPE;
610 v_calculate_gpa_ind IGS_PR_S_PRG_CONF.calculate_gpa_ind%TYPE;
611 v_outcome_check_type IGS_PR_S_PRG_CONF.outcome_check_type%TYPE;
612 FUNCTION prgpl_get_alias_value (
613 p_dt_alias VARCHAR2)
614 RETURN DATE
615 IS
616 gvl_other_detail VARCHAR2(255);
617 BEGIN -- prgpl_get_alias_value
618 DECLARE
619 v_alias_value DATE;
620 CURSOR c_dai IS
621 SELECT IGS_CA_GEN_001.CALP_GET_ALIAS_VAL (
622 dai.dt_alias,
623 dai.sequence_number,
624 p_prg_cal_type,
625 p_prg_sequence_number)
626 FROM IGS_CA_DA_INST dai
627 WHERE dai.cal_type = p_prg_cal_type AND
628 dai.ci_sequence_number = p_prg_sequence_number AND
629 dai.dt_alias = p_dt_alias
630 ORDER BY 1 DESC;
631 BEGIN
632 OPEN c_dai;
633 FETCH c_dai INTO v_alias_value;
634 IF c_dai%FOUND THEN
635 CLOSE c_dai;
636 RETURN v_alias_value;
637 END IF;
638 CLOSE c_dai;
639 RETURN NULL;
640 EXCEPTION
641 WHEN OTHERS THEN
642 IF c_dai%ISOPEN THEN
643 CLOSE c_dai;
644 END IF;
645 RAISE;
646 END;
647 EXCEPTION
648 WHEN OTHERS THEN
649 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
650 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.IGS_PR_GET_WITHIN_APPL.PRGPL_GET_ALIAS_VALUE');
651 IGS_GE_MSG_STACK.ADD;
652 App_Exception.Raise_Exception;
653 END prgpl_get_alias_value;
654
655 BEGIN
656 IGS_PR_GEN_003.IGS_PR_get_config_parm (
657 p_course_cd,
658 p_version_number,
659 v_apply_start_dt_alias,
660 v_apply_end_dt_alias,
661 v_end_benefit_dt_alias,
662 v_end_penalty_dt_alias,
663 v_show_cause_cutoff_dt,
664 v_appeal_cutoff_dt,
665 v_show_cause_ind,
666 v_apply_before_show_ind,
667 v_appeal_ind,
668 v_apply_before_appeal_ind,
669 v_count_sus_in_time_ind,
670 v_count_exc_in_time_ind,
671 v_calculate_wam_ind,
672 v_calculate_gpa_ind,
673 v_outcome_check_type);
674 v_start_dt := prgpl_get_alias_value (v_apply_start_dt_alias);
675 -- Set the cutoff date according to the type of application
676 IF p_application_type = cst_initial THEN
677 v_cutoff_dt := prgpl_get_alias_value (v_apply_end_dt_alias);
678 ELSIF p_application_type = cst_todo THEN
679 p_start_dt := NULL;
680 p_cutoff_dt := NULL;
681 RETURN 'Y';
682 ELSIF p_application_type = cst_benefit THEN
683 v_cutoff_dt := prgpl_get_alias_value (v_end_benefit_dt_alias);
684 ELSIF p_application_type = cst_penalty THEN
685 v_cutoff_dt := prgpl_get_alias_value (v_end_penalty_dt_alias);
686 ELSE
687 RETURN 'N';
688 END IF;
689 p_start_dt := v_start_dt;
690 p_cutoff_dt := v_cutoff_dt;
691 -- If within dates then return 'Y'
692 IF NVL(v_start_dt,TRUNC(SYSDATE)+1) <= TRUNC(SYSDATE) AND
693 NVL(v_cutoff_dt,TRUNC(SYSDATE)) >= TRUNC(SYSDATE) THEN
694 RETURN 'Y';
695 ELSE
696 RETURN 'N';
697 END IF;
698 END;
699 EXCEPTION
700 WHEN OTHERS THEN
701 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
702 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.IGS_PR_GET_WITHIN_APPL');
703 IGS_GE_MSG_STACK.ADD;
704 App_Exception.Raise_Exception;
705 END IGS_PR_get_within_appl;
706
707
708 FUNCTION IGS_PR_INS_COPY_PRA(
709 p_progression_rule_cat IN VARCHAR2 ,
710 p_sequence_number IN NUMBER ,
711 p_new_course_cd IN VARCHAR2 ,
712 p_new_version_number IN NUMBER ,
713 p_new_org_unit_cd IN VARCHAR2 ,
714 p_new_ou_start_dt IN DATE ,
715 p_new_spo_person_id IN NUMBER ,
716 p_new_spo_course_cd IN VARCHAR2 ,
717 p_new_spo_sequence_number IN NUMBER ,
718 p_new_sca_person_id IN NUMBER ,
719 p_new_sca_course_cd IN VARCHAR2 ,
720 p_message_name OUT NOCOPY VARCHAR2 )
721 RETURN NUMBER IS
722 gcst_max_error_range CONSTANT NUMBER := -20999;
723 gcst_min_error_range CONSTANT NUMBER := -20000;
724 --kdande; 19-Jul-2002; Bug# 2462120; Changed the message name from 301 to IGS_PS_FAIL_COPY_PRGVER_DETAI
725 gcst_ret_message_num CONSTANT VARCHAR2(30) := 'IGS_PS_FAIL_COPY_PRGVER_DETAI';
726 gv_other_detail VARCHAR2(255);
727 gv_err_inserting VARCHAR2(255);
728 gv_err_ins_rule VARCHAR2(255); -- creating progression rule
729 gv_err_ins_pra VARCHAR2(255); -- IGS_PR_RU_APPL
730 gv_err_ins_prrc VARCHAR2(255); -- IGS_PR_RU_CA_TYPE
731 gv_err_ins_pro VARCHAR2(255); -- IGS_PR_RU_OU
732 gv_err_ins_poc VARCHAR2(255); -- IGS_PR_OU_RS
733 gv_err_ins_pous VARCHAR2(255); -- IGS_PR_OU_UNIT_SET
734 gv_err_ins_popu VARCHAR2(255); -- IGS_PR_OU_UNIT
735 gv_err_ins_popf VARCHAR2(255); -- IGS_PR_OU_FND
736 gv_new_pra_sequence_number IGS_PR_RU_APPL.sequence_number%TYPE;
737 BEGIN -- IGS_PR_ins_copy_pra
738 -- Copy a IGS_PR_RU_APPL structure. This is used when parent object
739 -- such as course versions are rolled over and need new generations of these
740 -- structures.
741 -- The routine also makes new copies of rules which are defined as one-off
742 -- within the progression rule application.
743 DECLARE
744 v_call_again BOOLEAN;
745 PROCEDURE prgpl_ins_poc_pous_popu(
746 p_progression_rule_cat IGS_PR_RU_APPL.progression_rule_cat%TYPE,
747 p_pra_sequence_number IGS_PR_RU_APPL.sequence_number%TYPE,
748 p_pro_sequence_number IGS_PR_RU_OU.sequence_number%TYPE,
749 p_new_pra_sequence_number IGS_PR_RU_APPL.sequence_number%TYPE,
750 p_new_pro_sequence_number IGS_PR_RU_OU.sequence_number%TYPE,
751 p_message_name IN OUT NOCOPY IGS_PR_S_SCRATCH_PAD.MESSAGE_NAME%TYPE)
752 IS
753 BEGIN -- prgpl_ins_poc
754 -- Prodecdure to create new IGS_PR_OU_RS, IGS_PR_OU_UNIT_SET
755 -- and IGS_PR_OU_UNIT records
756
757 --Modified as part of Academic Standing and Progression build
758 -- to add records to the IGS_PR_OU_AWD table as well
759 -- amuthu 6-Dec-2001
760 DECLARE
761 CURSOR c_poc IS
762 SELECT poc.course_cd
763 FROM IGS_PR_OU_PS poc
764 WHERE poc.progression_rule_cat = p_progression_rule_cat AND
765 poc.pra_sequence_number = p_pra_sequence_number AND
766 poc.pro_sequence_number = p_pro_sequence_number;
767 CURSOR c_pous IS
768 SELECT pous.unit_set_cd,
769 pous.us_version_number
770 FROM IGS_PR_OU_UNIT_SET pous
771 WHERE pous.progression_rule_cat = p_progression_rule_cat AND
772 pous.pra_sequence_number = p_pra_sequence_number AND
773 pous.pro_sequence_number = p_pro_sequence_number;
774
775 CURSOR c_poa IS
776 SELECT poa.award_cd
777 FROM IGS_PR_OU_AWD poa
778 WHERE poa.progression_rule_cat = p_progression_rule_cat AND
779 poa.pra_sequence_number = p_pra_sequence_number AND
780 poa.pro_sequence_number = p_pro_sequence_number;
781 CURSOR c_popu IS
782 SELECT popu.unit_cd,
783 popu.s_unit_type
784 FROM IGS_PR_OU_UNIT popu
785 WHERE popu.progression_rule_cat = p_progression_rule_cat AND
786 popu.pra_sequence_number = p_pra_sequence_number AND
787 popu.pro_sequence_number = p_pro_sequence_number;
788 --
789 -- Start of new code added as per the FA110 PR Enh. Bug# 2658550.
790 --
791 CURSOR c_popf IS
792 SELECT popf.fund_Code
793 FROM IGS_PR_OU_FND popf
794 WHERE popf.progression_rule_cat = p_progression_rule_cat AND
795 popf.pra_sequence_number = p_pra_sequence_number AND
796 popf.pro_sequence_number = p_pro_sequence_number;
797 --
798 -- End of new code added as per the FA110 PR Enh. Bug# 2658550.
799 --
800
801 BEGIN
802 FOR v_poc_rec IN c_poc LOOP
803 BEGIN
804 DECLARE
805 lv_rowid VARCHAR2(25);
806 l_org_id NUMBER(15);
807 BEGIN
808 l_org_id := igs_ge_gen_003.get_org_id;
809 IGS_PR_OU_PS_PKG.INSERT_ROW (
810 X_ROWID =>LV_ROWID,
811 X_PROGRESSION_RULE_CAT =>p_progression_rule_cat,
812 X_PRA_SEQUENCE_NUMBER =>p_new_pra_sequence_number,
813 X_PRO_SEQUENCE_NUMBER =>p_new_pro_sequence_number,
814 X_COURSE_CD =>v_poc_rec.course_cd,
815 X_MODE =>'R',
816 X_ORG_ID => l_org_id
817 );
818 END;
819 EXCEPTION
820 WHEN OTHERS THEN
821 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
822 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.C_POC');
823 IGS_GE_MSG_STACK.ADD;
824 App_Exception.Raise_Exception;
825 END;
826 END LOOP;
827 FOR v_pous_rec IN c_pous LOOP
828 BEGIN
829
830 DECLARE
831 lv_rowid VARCHAR2(25);
832 l_org_id NUMBER(15);
833 BEGIN
834 l_org_id := igs_ge_gen_003.get_org_id;
835 IGS_PR_OU_UNIT_SET_PKG.INSERT_ROW (
836 X_ROWID =>LV_ROWID,
837 X_PROGRESSION_RULE_CAT =>p_progression_rule_cat,
838 X_PRA_SEQUENCE_NUMBER =>p_new_pra_sequence_number,
839 X_PRO_SEQUENCE_NUMBER =>p_new_pro_sequence_number,
840 X_UNIT_SET_CD =>v_pous_rec.unit_set_cd,
841 X_US_VERSION_NUMBER =>v_pous_rec.us_version_number,
842 X_MODE =>'R',
843 X_ORG_ID => l_org_id
844 );
845 END;
846 EXCEPTION
847 WHEN OTHERS THEN
848 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
849 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.C_POUS');
850 IGS_GE_MSG_STACK.ADD;
851 App_Exception.Raise_Exception;
852 END;
853 END LOOP;
854
855
856
857 FOR v_poa_rec IN c_poa LOOP
858
859 BEGIN
860 DECLARE
861 lv_rowid VARCHAR2(25);
862 l_org_id NUMBER(15);
863 BEGIN
864 l_org_id := igs_ge_gen_003.get_org_id();
865 IGS_PR_OU_AWD_PKG.INSERT_ROW (
866 X_ROWID => lv_rowid,
867 X_PROGRESSION_RULE_CAT => p_progression_rule_cat,
868 X_PRA_SEQUENCE_NUMBER => p_new_pra_sequence_number,
869 X_PRO_SEQUENCE_NUMBER => p_new_pro_sequence_number,
870 X_AWARD_CD => v_poa_rec.award_cd,
871 X_MODE => 'R'
872 );
873 END;
874 EXCEPTION
875 WHEN OTHERS THEN
876 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
877 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.C_POA');
878 IGS_GE_MSG_STACK.ADD;
879 App_Exception.Raise_Exception;
880 END;
881
882 END LOOP;
883
884 FOR v_popu_rec IN c_popu LOOP
885 BEGIN
886 DECLARE
887 lv_rowid VARCHAR2(25);
888 l_org_id NUMBER(15);
889 BEGIN
890 l_org_id := igs_ge_gen_003.get_org_id;
891 IGS_PR_OU_UNIT_PKG.INSERT_ROW (
892 X_ROWID =>LV_ROWID,
893 X_PROGRESSION_RULE_CAT =>p_progression_rule_cat,
894 X_PRA_SEQUENCE_NUMBER =>p_new_pra_sequence_number,
895 X_PRO_SEQUENCE_NUMBER =>p_new_pro_sequence_number,
896 X_UNIT_CD =>v_popu_rec.unit_cd,
897 X_S_UNIT_TYPE =>v_popu_rec.s_unit_type,
898 X_MODE =>'R',
899 X_ORG_ID => l_org_id
900 );
901 END;
902 EXCEPTION
903 WHEN OTHERS THEN
904 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
905 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.C_POPU');
906 IGS_GE_MSG_STACK.ADD;
907 App_Exception.Raise_Exception;
908 END;
909 END LOOP;
910
911 --
912 -- Start of new code added as per the FA110 PR Enh. Bug# 2658550.
913 --
914 FOR v_popf_rec IN c_popf LOOP
915 BEGIN
916 DECLARE
917 lv_rowid VARCHAR2(25);
918 BEGIN
919 IGS_PR_OU_FND_PKG.INSERT_ROW (
920 X_ROWID => lv_rowid,
921 X_PROGRESSION_RULE_CAT =>p_progression_rule_cat,
922 X_PRA_SEQUENCE_NUMBER =>p_new_pra_sequence_number,
923 X_PRO_SEQUENCE_NUMBER =>p_new_pro_sequence_number,
924 X_FUND_CODE => v_popf_rec.fund_code,
925 X_MODE =>'R'
926 );
927 END;
928 EXCEPTION
929 WHEN OTHERS THEN
930 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
931 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.C_POPF');
932 IGS_GE_MSG_STACK.ADD;
933 App_Exception.Raise_Exception;
934 END;
935 END LOOP;
936 --
937 -- Start of new code added as per the FA110 PR Enh. Bug# 2658550.
938 --
939
940 EXCEPTION
941 WHEN OTHERS THEN
942 IF c_poc%ISOPEN THEN
943 CLOSE c_poc;
944 END IF;
945 IF c_pous%ISOPEN THEN
946 CLOSE c_pous;
947 END IF;
948 IF c_poa%ISOPEN THEN
949 CLOSE c_poa;
950 END IF;
951 IF c_popu%ISOPEN THEN
952 CLOSE c_popu;
953 END IF;
954 IF c_popf%ISOPEN THEN
955 CLOSE c_popf;
956 END IF;
957 RAISE;
958 END;
959 EXCEPTION
960 WHEN OTHERS THEN
961 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
962 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.IGS_PR_INS_COPY_PRA.PRGPL_INS_POC_POUS_POPU.');
963 IGS_GE_MSG_STACK.ADD;
964 App_Exception.Raise_Exception;
965 END prgpl_ins_poc_pous_popu;
966
967 FUNCTION prgpl_ins_copy_pra(
968 p_progression_rule_cat IGS_PR_RU_APPL.progression_rule_cat%TYPE,
969 p_sequence_number IGS_PR_RU_APPL.sequence_number%TYPE,
970 p_prev_progression_rule_cat
971 IGS_PR_RU_OU.progression_rule_cat%TYPE,
972 p_prev_pra_sequence_number IGS_PR_RU_OU.pra_sequence_number%TYPE,
973 p_prev_pro_sequence_number IGS_PR_RU_OU.sequence_number%TYPE,
974 p_new_course_cd IGS_PR_RU_APPL.crv_course_cd%TYPE,
975 p_new_version_number IGS_PR_RU_APPL.crv_version_number%TYPE,
976 p_new_org_unit_cd IGS_PR_RU_APPL.ou_org_unit_cd%TYPE,
977 p_new_ou_start_dt IGS_PR_RU_APPL.ou_start_dt%TYPE,
978 p_new_spo_person_id IGS_PR_STDNT_PR_OU.person_id%TYPE,
979 p_new_spo_course_cd IGS_PR_STDNT_PR_OU.course_cd%TYPE,
980 p_new_spo_sequence_number IGS_PR_STDNT_PR_OU.sequence_number%TYPE,
981 p_call_again BOOLEAN,
982 p_message_name IN OUT NOCOPY IGS_PR_S_SCRATCH_PAD.MESSAGE_NAME%TYPE)
983 RETURN NUMBER
984 IS
985 BEGIN -- prgpl_ins_copy_pra
986 -- This local routine performs all of the logic and inserts of
987 -- IGS_PR_ins_copy_pra because in some cases we are required to
988 -- call this routine again.
989 DECLARE
990 cst_sca CONSTANT VARCHAR2(10) := 'SCA';
991 cst_spo CONSTANT VARCHAR2(10) := 'SPO';
992 v_error_ins_pra BOOLEAN;
993 v_error_ins_pro BOOLEAN;
994 v_call_again BOOLEAN;
995 v_new_rule_num IGS_RU_RULE.sequence_number%TYPE;
996 v_pra_s_relation_type IGS_PR_RU_APPL.s_relation_type%TYPE;
997 v_pra_progression_rule_cd IGS_PR_RU_APPL.progression_rule_cd%TYPE;
998 v_pra_rul_sequence_number IGS_PR_RU_APPL.rul_sequence_number%TYPE;
999 v_pra_attendance_type IGS_PR_RU_APPL.attendance_type%TYPE;
1000 v_pra_ou_org_unit_cd IGS_PR_RU_APPL.ou_org_unit_cd%TYPE;
1001 v_pra_ou_start_dt IGS_PR_RU_APPL.ou_start_dt%TYPE;
1002 v_pra_course_type IGS_PR_RU_APPL.course_type%TYPE;
1003 v_pra_crv_course_cd IGS_PR_RU_APPL.crv_course_cd%TYPE;
1004 v_pra_crv_version_number IGS_PR_RU_APPL.crv_version_number%TYPE;
1005 v_pra_sca_person_id IGS_PR_RU_APPL.sca_person_id%TYPE;
1006 v_pra_sca_course_cd IGS_PR_RU_APPL.sca_course_cd%TYPE;
1007 v_pra_pro_progression_rule_cat
1008 IGS_PR_RU_APPL.pro_progression_rule_cat%TYPE;
1009 v_pra_pro_pra_sequence_number
1010 IGS_PR_RU_APPL.pro_pra_sequence_number%TYPE;
1011 v_pra_pro_sequence_number IGS_PR_RU_APPL.pro_sequence_number%TYPE;
1012 v_pra_spo_person_id IGS_PR_RU_APPL.spo_person_id%TYPE;
1013 v_pra_spo_course_cd IGS_PR_RU_APPL.spo_course_cd%TYPE;
1014 v_pra_spo_sequence_number IGS_PR_RU_APPL.spo_sequence_number%TYPE;
1015 v_pra_message IGS_PR_RU_APPL.message%TYPE;
1016 v_pra_reference_cd IGS_PR_RU_APPL.reference_cd%TYPE;
1017 v_s_rule_call_cd IGS_PR_RU_CAT.s_rule_call_cd%TYPE;
1018 v_new_pra_sequence_number IGS_PR_RU_APPL.sequence_number%TYPE;
1019 v_new_pro_sequence_number IGS_PR_RU_OU.sequence_number%TYPE;
1020 v_rul_sequence_number IGS_PR_RU_APPL.rul_sequence_number%TYPE;
1021 v_progression_rule_cat IGS_PR_RU_APPL.progression_rule_cat%TYPE;
1022 v_sequence_number IGS_PR_RU_APPL.sequence_number%TYPE;
1023 CURSOR c_pra IS
1024 SELECT pra.s_relation_type,
1025 pra.progression_rule_cd,
1026 pra.rul_sequence_number,
1027 pra.attendance_type,
1028 pra.ou_org_unit_cd,
1029 pra.ou_start_dt,
1030 pra.course_type,
1031 pra.crv_course_cd,
1032 pra.crv_version_number,
1033 pra.sca_person_id,
1034 pra.sca_course_cd,
1035 pra.pro_progression_rule_cat,
1036 pra.pro_pra_sequence_number,
1037 pra.pro_sequence_number,
1038 pra.spo_person_id,
1039 pra.spo_course_cd,
1040 pra.spo_sequence_number,
1041 pra.message,
1042 pra.reference_cd
1043 FROM IGS_PR_RU_APPL pra
1044 WHERE pra.progression_rule_cat = p_progression_rule_cat AND
1045 pra.sequence_number = p_sequence_number AND
1046 pra.logical_delete_dt IS NULL;
1047 CURSOR c_prgc IS
1048 SELECT prgc.s_rule_call_cd
1049 FROM IGS_PR_RU_CAT prgc
1050 WHERE prgc.progression_rule_cat = p_progression_rule_cat;
1051 CURSOR c_pra_sequence_number IS
1052 SELECT IGS_PR_PRA_SEQUENCE_NO_S.NEXTVAL
1053 FROM dual;
1054 CURSOR c_pro_sequence_number IS
1055 --gjha Changed the sequence fromPRA to PRO.
1056
1057 SELECT IGS_PR_PRO_SEQUENCE_NO_S.NEXTVAL
1058 FROM dual;
1059 -- Retrieve child of old IGS_PR_RU_APPL record
1060 CURSOR c_prrc IS
1061 SELECT prrc.prg_cal_type,
1062 prrc.start_sequence_number,
1063 prrc.end_sequence_number,
1064 prrc.start_effective_period,
1065 prrc.num_of_applications
1066 FROM IGS_PR_RU_CA_TYPE prrc
1067 WHERE prrc.progression_rule_cat = p_progression_rule_cat AND
1068 prrc.pra_sequence_number = p_sequence_number;
1069 -- Retrieve child of old IGS_PR_RU_APPL record
1070 CURSOR c_pro IS
1071 SELECT pro.sequence_number,
1072 pro.number_of_failures,
1073 pro.progression_outcome_type,
1074 pro.apply_automatically_ind,
1075 pro.prg_rule_repeat_fail_type,
1076 pro.override_show_cause_ind,
1077 pro.override_appeal_ind,
1078 pro.duration,
1079 pro.duration_type,
1080 pro.rank,
1081 pro.encmb_course_group_cd,
1082 pro.restricted_enrolment_cp,
1083 pro.restricted_attendance_type,
1084 pro.comments
1085 FROM IGS_PR_RU_OU pro
1086 WHERE pro.progression_rule_cat = p_progression_rule_cat AND
1087 pro.pra_sequence_number = p_sequence_number AND
1088 -- anilk, bug#2784198
1089 pro.logical_delete_dt IS NULL;
1090 CURSOR c_pra_check_pro (
1091 cp_pro_progression_rule_cat
1092 IGS_PR_RU_OU.progression_rule_cat%TYPE,
1093 cp_pro_pra_sequence_number IGS_PR_RU_OU.pra_sequence_number%TYPE,
1094 cp_pro_sequence_number IGS_PR_RU_OU.sequence_number%TYPE) IS
1095 SELECT pra.progression_rule_cat,
1096 pra.sequence_number
1097 FROM IGS_PR_RU_APPL pra
1098 WHERE pra.pro_progression_rule_cat = cp_pro_progression_rule_cat AND
1099 pra.pro_pra_sequence_number = cp_pro_pra_sequence_number AND
1100 pra.pro_sequence_number = cp_pro_sequence_number;
1101 BEGIN
1102 -- varaibles to determine if child records can be created
1103 v_error_ins_pra := FALSE;
1104 v_error_ins_pro := FALSE;
1105 v_call_again := p_call_again;
1106 -- Select detail from specified record
1107 OPEN c_pra;
1108 FETCH c_pra INTO v_pra_s_relation_type,
1109 v_pra_progression_rule_cd,
1110 v_pra_rul_sequence_number,
1111 v_pra_attendance_type,
1112 v_pra_ou_org_unit_cd,
1113 v_pra_ou_start_dt,
1114 v_pra_course_type,
1115 v_pra_crv_course_cd,
1116 v_pra_crv_version_number,
1117 v_pra_sca_person_id,
1118 v_pra_sca_course_cd,
1119 v_pra_pro_progression_rule_cat,
1120 v_pra_pro_pra_sequence_number,
1121 v_pra_pro_sequence_number,
1122 v_pra_spo_person_id,
1123 v_pra_spo_course_cd,
1124 v_pra_spo_sequence_number,
1125 v_pra_message,
1126 v_pra_reference_cd;
1127 IF c_pra%NOTFOUND THEN
1128 CLOSE c_pra;
1129 RETURN NULL;
1130 END IF;
1131 CLOSE c_pra;
1132 -- If illogical org unit parameters against record abort
1133 IF p_new_org_unit_cd IS NOT NULL AND
1134 v_pra_ou_org_unit_cd IS NULL THEN
1135 RETURN NULL;
1136 END IF;
1137 -- If illogical course version parameters against record abort
1138 IF p_new_course_cd IS NOT NULL AND
1139 v_pra_crv_course_cd IS NULL THEN
1140 RETURN NULL;
1141 END IF;
1142 IF v_pra_rul_sequence_number IS NOT NULL THEN
1143 OPEN c_prgc;
1144 FETCH c_prgc INTO v_s_rule_call_cd;
1145 CLOSE c_prgc;
1146 BEGIN
1147 v_new_rule_num := IGS_RU_GEN_003.RULP_INS_COPY_RULE(
1148 v_s_rule_call_cd,
1149 v_pra_rul_sequence_number);
1150 EXCEPTION
1151 WHEN OTHERS THEN
1152 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1153 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.IGS_PR_INS_COPY_PRA.RULE_COPY');
1154 IGS_GE_MSG_STACK.ADD;
1155 App_Exception.Raise_Exception;
1156 END;
1157 END IF;
1158 OPEN c_pra_sequence_number;
1159 FETCH c_pra_sequence_number INTO gv_new_pra_sequence_number;
1160 CLOSE c_pra_sequence_number;
1161 -- If not called recursively
1162 IF v_call_again THEN
1163 IF p_new_course_cd IS NOT NULL THEN
1164 v_pra_crv_course_cd := p_new_course_cd;
1165 v_pra_crv_version_number := p_new_version_number;
1166 END IF;
1167 IF p_new_org_unit_cd IS NOT NULL THEN
1168 v_pra_ou_org_unit_cd := p_new_org_unit_cd;
1169 v_pra_ou_start_dt := p_new_ou_start_dt;
1170 END IF;
1171 IF p_new_spo_person_id IS NOT NULL AND
1172 p_new_sca_person_id IS NULL THEN
1173 v_pra_s_relation_type := cst_spo;
1174 v_pra_spo_person_id := p_new_spo_person_id;
1175 v_pra_spo_course_cd := p_new_spo_course_cd;
1176 v_pra_spo_sequence_number := p_new_spo_sequence_number;
1177 v_pra_pro_progression_rule_cat := NULL;
1178 v_pra_pro_pra_sequence_number := NULL;
1179 v_pra_pro_sequence_number := NULL;
1180 END IF;
1181 IF p_new_sca_person_id IS NOT NULL THEN
1182 v_pra_s_relation_type := cst_sca;
1183 v_pra_sca_person_id := p_new_sca_person_id;
1184 v_pra_sca_course_cd := p_new_sca_course_cd;
1185 v_pra_spo_person_id := p_new_spo_person_id;
1186 v_pra_spo_course_cd := p_new_spo_course_cd;
1187 v_pra_spo_sequence_number := p_new_spo_sequence_number;
1188 END IF;
1189 ELSE
1190 -- set pro_sequence_number to pro_sequence_number found before the
1191 -- function was called again.
1192 v_pra_pro_progression_rule_cat := p_prev_progression_rule_cat;
1193 v_pra_pro_pra_sequence_number := p_prev_pra_sequence_number;
1194 v_pra_pro_sequence_number := p_prev_pro_sequence_number;
1195 END IF;
1196 IF v_pra_rul_sequence_number IS NOT NULL THEN
1197 v_pra_rul_sequence_number := v_new_rule_num;
1198 END IF;
1199 BEGIN
1200 DECLARE
1201 lv_rowid VARCHAR2(25);
1202 l_org_id NUMBER(15);
1203 BEGIN
1204 l_org_id := igs_ge_gen_003.get_org_id;
1205 IGS_PR_RU_APPL_PKG.INSERT_ROW (
1206 X_ROWID =>LV_ROWID,
1207 X_PROGRESSION_RULE_CAT =>p_progression_rule_cat,
1208 X_SEQUENCE_NUMBER =>gv_new_pra_sequence_number,
1209 X_S_RELATION_TYPE =>v_pra_s_relation_type,
1210 X_PROGRESSION_RULE_CD =>v_pra_progression_rule_cd,
1211 X_REFERENCE_CD =>v_pra_reference_cd,
1212 X_RUL_SEQUENCE_NUMBER =>v_pra_rul_sequence_number,
1213 X_ATTENDANCE_TYPE =>v_pra_attendance_type,
1214 X_OU_ORG_UNIT_CD =>v_pra_ou_org_unit_cd,
1215 X_OU_START_DT =>v_pra_ou_start_dt,
1216 X_COURSE_TYPE =>v_pra_course_type,
1217 X_CRV_COURSE_CD =>v_pra_crv_course_cd,
1218 X_CRV_VERSION_NUMBER =>v_pra_crv_version_number,
1219 X_SCA_PERSON_ID =>v_pra_sca_person_id,
1220 X_SCA_COURSE_CD =>v_pra_sca_course_cd,
1221 X_PRO_PROGRESSION_RULE_CAT =>v_pra_pro_progression_rule_cat,
1222 X_PRO_PRA_SEQUENCE_NUMBER =>v_pra_pro_pra_sequence_number,
1223 X_PRO_SEQUENCE_NUMBER =>v_pra_pro_sequence_number,
1224 X_SPO_PERSON_ID =>v_pra_spo_person_id,
1225 X_SPO_COURSE_CD =>v_pra_spo_course_cd,
1226 X_SPO_SEQUENCE_NUMBER =>v_pra_spo_sequence_number,
1227 X_LOGICAL_DELETE_DT =>NULL,
1228 X_MESSAGE =>v_pra_message,
1229 X_MODE =>'R',
1230 X_ORG_ID => l_org_id
1231 );
1232 END;
1233 EXCEPTION
1234 WHEN OTHERS THEN
1235 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1236 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.IGS_PR_INS_COPY_PRA.INESRT_RULE_APPL');
1237 IGS_GE_MSG_STACK.ADD;
1238 App_Exception.Raise_Exception;
1239 END;
1240 IF v_error_ins_pra = FALSE THEN
1241 FOR v_prrc_rec IN c_prrc LOOP
1242 BEGIN
1243 DECLARE
1244 lv_rowid VARCHAR2(25);
1245 l_org_id NUMBER(15);
1246 BEGIN
1247 l_org_id := igs_ge_gen_003.get_org_id;
1248 IGS_PR_RU_CA_TYPE_PKG.INSERT_ROW (
1249 X_ROWID =>LV_ROWID,
1250 X_PROGRESSION_RULE_CAT =>p_progression_rule_cat,
1251 X_PRA_SEQUENCE_NUMBER=> gv_new_pra_sequence_number,
1252 X_PRG_CAL_TYPE =>v_prrc_rec.prg_cal_type,
1253 X_START_SEQUENCE_NUMBER =>v_prrc_rec.start_sequence_number,
1254 X_END_SEQUENCE_NUMBER =>v_prrc_rec.end_sequence_number,
1255 X_START_EFFECTIVE_PERIOD =>v_prrc_rec.start_effective_period,
1256 X_NUM_OF_APPLICATIONS =>v_prrc_rec.num_of_applications,
1257 X_MODE =>'R',
1258 X_ORG_ID => l_org_id
1259 );
1260 END;
1261 EXCEPTION
1262 WHEN OTHERS THEN
1263 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1264 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.IGS_PR_INS_COPY_PRA.INSERT_RU_CA');
1265 IGS_GE_MSG_STACK.ADD;
1266 App_Exception.Raise_Exception;
1267 END;
1268 END LOOP;
1269 FOR v_pro_rec IN c_pro LOOP
1270 OPEN c_pro_sequence_number;
1271 --Gjha Changed from c_pra_sequence_number
1272 FETCH c_pro_sequence_number INTO v_new_pro_sequence_number;
1273 CLOSE c_pro_sequence_number;
1274 BEGIN
1275 DECLARE
1276 lv_rowid VARCHAR2(25);
1277 l_org_id NUMBER(15);
1278 BEGIN
1279 l_org_id := igs_ge_gen_003.get_org_id;
1280 IGS_PR_RU_OU_pkg.INSERT_ROW (
1281 X_ROWID =>lv_rowid,
1282 X_PROGRESSION_RULE_CAT=> p_progression_rule_cat,
1283 X_PRA_SEQUENCE_NUMBER=> gv_new_pra_sequence_number,
1284 X_SEQUENCE_NUMBER =>v_new_pro_sequence_number,
1285 X_NUMBER_OF_FAILURES =>v_pro_rec.number_of_failures,
1286 X_PROGRESSION_OUTCOME_TYPE =>v_pro_rec.progression_outcome_type,
1287 X_APPLY_AUTOMATICALLY_IND =>v_pro_rec.apply_automatically_ind,
1288 X_PRG_RULE_REPEAT_FAIL_TYPE =>v_pro_rec.prg_rule_repeat_fail_type,
1289 X_OVERRIDE_SHOW_CAUSE_IND =>v_pro_rec.override_show_cause_ind,
1290 X_OVERRIDE_APPEAL_IND =>v_pro_rec.override_appeal_ind,
1291 X_DURATION =>v_pro_rec.duration,
1292 X_DURATION_TYPE =>v_pro_rec.duration_type,
1293 X_RANK => v_pro_rec.rank,
1294 X_ENCMB_COURSE_GROUP_CD =>v_pro_rec.encmb_course_group_cd,
1295 X_RESTRICTED_ENROLMENT_CP =>v_pro_rec.restricted_enrolment_cp,
1296 X_RESTRICTED_ATTENDANCE_TYPE =>v_pro_rec.restricted_attendance_type,
1297 X_COMMENTS =>v_pro_rec.comments,
1298 X_MODE =>'R',
1299 X_ORG_ID => l_org_id
1300 );
1301 END;
1302 EXCEPTION
1303 WHEN OTHERS THEN
1304 v_error_ins_pro := TRUE;
1305 gv_err_ins_pro := 'IGS_PR_RU_OU ';
1306 IF (SQLCODE >= gcst_max_error_range AND
1307 SQLCODE <= gcst_min_error_range) THEN
1308 p_message_name := gcst_ret_message_num;
1309 ELSE
1310 RAISE;
1311 END IF;
1312 END;
1313 -- Where existing, copy IGS_PR_OU_RS, IGS_PR_OU_UNIT_SET and
1314 -- IGS_PR_OU_UNIT records, moving from the old to new pro record
1315 IF v_error_ins_pro = FALSE THEN
1316 prgpl_ins_poc_pous_popu(
1317 p_progression_rule_cat,
1318 p_sequence_number, -- old pra_sequence_number
1319 v_pro_rec.sequence_number, -- old
1320 gv_new_pra_sequence_number,
1321 v_new_pro_sequence_number,
1322 p_message_name); -- IN OUT NOCOPY
1323 ELSE
1324 gv_err_ins_poc := 'IGS_PR_OU_RS ';
1325 gv_err_ins_pous := 'IGS_PR_OU_UNIT_SET ';
1326 gv_err_ins_popu := 'IGS_PR_OU_UNIT ';
1327 END IF;
1328 OPEN c_pra_check_pro (
1329 p_progression_rule_cat,
1330 p_sequence_number,
1331 v_pro_rec.sequence_number);
1332 FETCH c_pra_check_pro INTO v_progression_rule_cat,
1333 v_sequence_number;
1334 IF c_pra_check_pro%FOUND THEN
1335 CLOSE c_pra_check_pro;
1336 -- Perform entire logic again
1337 -- Only do this one level deep - do not recurse.
1338 IF v_call_again THEN
1339 IF v_progression_rule_cat IS NOT NULL AND
1340 v_sequence_number IS NOT NULL THEN
1341 v_call_again := FALSE;
1342 v_new_pra_sequence_number := prgpl_ins_copy_pra (
1343 v_progression_rule_cat,
1344 v_sequence_number,
1345 p_progression_rule_cat,
1346 gv_new_pra_sequence_number,
1347 v_new_pro_sequence_number,
1348 p_new_course_cd,
1349 p_new_version_number,
1350 p_new_org_unit_cd,
1351 p_new_ou_start_dt,
1352 p_new_spo_person_id,
1353 p_new_spo_course_cd,
1354 p_new_spo_sequence_number,
1355 v_call_again,
1356 p_message_name); -- IN OUT NOCOPY
1357 IF v_new_pra_sequence_number IS NOT NULL THEN
1358 gv_new_pra_sequence_number := v_new_pra_sequence_number;
1359 END IF;
1360 v_call_again := TRUE;
1361 END IF;
1362 END IF;
1363 ELSE
1364 CLOSE c_pra_check_pro;
1365 END IF;
1366 END LOOP;
1367 ELSE
1368 -- Records cannot be inserted because a new IGS_PR_RU_APPL
1369 -- record was not created.
1370 gv_err_ins_prrc := 'IGS_PR_RU_CA_TYPE ';
1371 gv_err_ins_pro := 'IGS_PR_RU_OU ';
1372 gv_err_ins_poc := 'IGS_PR_OU_RS ';
1373 gv_err_ins_pous := 'IGS_PR_OU_UNIT_SET ';
1374 gv_err_ins_popu := 'IGS_PR_OU_UNIT ';
1375 gv_err_ins_popf := 'IGS_PR_OU_FND';
1376 END IF;
1377 RETURN gv_new_pra_sequence_number;
1378 EXCEPTION
1379 WHEN OTHERS THEN
1380 IF c_pra%ISOPEN THEN
1381 CLOSE c_pra;
1382 END IF;
1383 IF c_prgc%ISOPEN THEN
1384 CLOSE c_prgc;
1385 END IF;
1386 IF c_pra_sequence_number%ISOPEN THEN
1387 CLOSE c_pra_sequence_number;
1388 END IF;
1389 IF c_pro_sequence_number%ISOPEN THEN
1390 CLOSE c_pro_sequence_number;
1391 END IF;
1392 IF c_prrc%ISOPEN THEN
1393 CLOSE c_prrc;
1394 END IF;
1395 IF c_pro%ISOPEN THEN
1396 CLOSE c_pro;
1397 END IF;
1398 IF c_pra_check_pro%ISOPEN THEN
1399 CLOSE c_pra_check_pro;
1400 END IF;
1401 RAISE;
1402 END;
1403 EXCEPTION
1404 WHEN OTHERS THEN
1405 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1406 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.IGS_PR_INS_COPY_PRA.PRGPL_INS_COPY_PRA');
1407 IGS_GE_MSG_STACK.ADD;
1408 App_Exception.Raise_Exception;
1409 END prgpl_ins_copy_pra;
1410 BEGIN
1411 --kdande; 19-Jul-2002; Bug# 2462120; Changed the message name from 300 to IGS_PS_SUCCESS_COPY_PRGVER
1412 p_message_name := 'IGS_PS_SUCCESS_COPY_PRGVER';
1413 v_call_again := TRUE;
1414 gv_new_pra_sequence_number := prgpl_ins_copy_pra (
1415 p_progression_rule_cat,
1416 p_sequence_number,
1417 NULL, -- p_prev_progression_rule_cat
1418 NULL, -- p_prev_pra_sequence_number
1419 NULL, -- p_prev_pro_sequence_number
1420 p_new_course_cd,
1421 p_new_version_number,
1422 p_new_org_unit_cd,
1423 p_new_ou_start_dt,
1424 p_new_spo_person_id,
1425 p_new_spo_course_cd,
1426 p_new_spo_sequence_number,
1427 v_call_again,
1428 p_message_name); -- IN OUT NOCOPY
1429 IF gv_err_ins_rule IS NOT NULL OR
1430 gv_err_ins_pra IS NOT NULL OR
1431 gv_err_ins_prrc IS NOT NULL OR
1432 gv_err_ins_pro IS NOT NULL OR
1433 gv_err_ins_poc IS NOT NULL OR
1434 gv_err_ins_pous IS NOT NULL OR
1435 gv_err_ins_popf IS NOT NULL OR
1436 gv_err_ins_popu IS NOT NULL THEN
1437 gv_err_inserting := 'Creation OF NEW records failed FOR the '
1438 || 'following TABLES :'
1439 || gv_err_ins_rule
1440 || gv_err_ins_pra
1441 || gv_err_ins_prrc
1442 || gv_err_ins_pro
1443 || gv_err_ins_poc
1444 || gv_err_ins_pous
1445 || gv_err_ins_popf
1446 || gv_err_ins_popu;
1447 END IF;
1448 RETURN gv_new_pra_sequence_number;
1449 END;
1450 EXCEPTION
1451 WHEN OTHERS THEN
1452 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1453 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.IGS_PR_INS_COPY_PRA');
1454 IGS_GE_MSG_STACK.ADD;
1455 App_Exception.Raise_Exception;
1456 END IGS_PR_ins_copy_pra;
1457
1458 FUNCTION IGS_PR_INS_SSP_CMP_DTL(
1459 p_rule_text IN VARCHAR2 ,
1460 p_message_text IN VARCHAR2 ,
1461 p_log_dt IN DATE ,
1462 p_key IN VARCHAR2 ,
1463 p_message_name OUT NOCOPY VARCHAR2 )
1464 RETURN BOOLEAN IS
1465 gv_other_detail VARCHAR2(255);
1466 BEGIN -- IGS_PR_ins_ssp_cmp_dtl
1467 -- This routine is used by the functionality associated with the form PRGF9030
1468 -- Inquire On Student Completion. The form displays the completion rules
1469 -- associated with a course. This routine is called to break up the text
1470 -- fields returned from the Rule system. It will store them into a temporary
1471 -- table where the data will be queried and displayed by the form. This
1472 -- routine may be called by other modules, such as routines associated
1473 -- with a report.
1474 DECLARE
1475 v_loop_count NUMBER(4);
1476 v_rstart NUMBER(4);
1477 v_mstart NUMBER(4);
1478 v_rend NUMBER(4);
1479 v_mend NUMBER(4);
1480 v_sub_rule_text IGS_RU_NAMED_RULE.rule_text%TYPE;
1481 v_sub_message_text IGS_RU_NAMED_RULE.rule_text%TYPE;
1482 v_fail_text_separator_posn NUMBER(4);
1483 v_rule_result IGS_RU_NAMED_RULE.rule_text%TYPE;
1484 v_rule_fail_text IGS_RU_NAMED_RULE.rule_text%TYPE;
1485 v_ssp_sequence_number IGS_PR_s_scratch_pad.sequence_number%TYPE;
1486 BEGIN
1487 -- set the default message number
1488 p_message_name := NULL;
1489 --Initialise a counter as a safety check that an infinite loop does not occur.
1490 --(4000 has chosen as the current maximum length of a VARCHAR2 field.)
1491 v_loop_count := 0;
1492 --Initialise place holders
1493 v_rstart := 1;
1494 v_mstart := 1;
1495 LOOP
1496 v_loop_count := v_loop_count + 1;
1497 IF v_loop_count > 4000 THEN
1498 -- p_message_name := 5199;
1499 p_message_name := 'IGS_PR_INTERNAL_ERR';
1500 EXIT;
1501 END IF;
1502 -- Determine if the rule text and rule messages need to be split up.
1503 -- The rules and messages are separated by carriage returns.
1504 v_rend := INSTR(p_rule_text, fnd_global.local_chr(10), v_rstart);
1505 v_mend := INSTR(p_message_text, fnd_global.local_chr(10), v_mstart);
1506 IF v_rend = 0 THEN
1507 v_sub_rule_text := SUBSTR(p_rule_text, v_rstart);
1508 v_sub_message_text := SUBSTR(p_message_text, v_mstart);
1509 --Get the substring of the rule and message text fields.
1510 ELSE
1511 v_sub_rule_text := SUBSTR(p_rule_text, v_rstart, v_rend - v_rstart);
1512 v_sub_message_text := SUBSTR(p_message_text, v_mstart, v_mend - v_mstart);
1513 --There are more carriage returns in the string, hence, determine a
1514 END IF; --substring of the rule and message text fields.
1515 --Determine if the separator exists '::' in the message. The separator is used
1516 --to identify the rule result and the message.
1517 v_fail_text_separator_posn := INSTR(v_sub_message_text, '::');
1518 IF v_fail_text_separator_posn > 0 THEN
1519 --The separator exists, split the result from the message.
1520 v_rule_result := SUBSTR(v_sub_message_text, 1,
1521 v_fail_text_separator_posn - 1);
1522 v_rule_fail_text := SUBSTR(v_sub_message_text,
1523 v_fail_text_separator_posn + 2);
1524 ELSE
1525 v_rule_result := v_sub_message_text;
1526 v_rule_fail_text := NULL;
1527 END IF;
1528 -- Insert the values into the s_scratch_pad table (similar to the s_log_entry).
1529 IGS_PR_GEN_003.IGS_PR_INS_SSP(
1530 p_log_dt,
1531 p_key ,
1532 NULL,
1533 v_sub_rule_text || '|' ||
1534 v_rule_result || '|' ||
1535 v_rule_fail_text,
1536 v_ssp_sequence_number);
1537 --Check if at the end of the string
1538 IF v_rend = 0 THEN
1539 EXIT;
1540 ELSE
1541 v_rstart := v_rend + 1;
1542 v_mstart := v_mend + 1;
1543 END IF;
1544 END LOOP;
1545 -- Check the result of the processing.
1546 IF p_message_name <> NULL THEN
1547 RETURN FALSE;
1548 ELSE
1549 RETURN TRUE;
1550 END IF;
1551 END;
1552 EXCEPTION
1553 WHEN OTHERS THEN
1554 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1555 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.IGS_PR_INS_SSP_CMP_DTL');
1556 IGS_GE_MSG_STACK.ADD;
1557 App_Exception.Raise_Exception;
1558 END IGS_PR_ins_ssp_cmp_dtl;
1559
1560
1561 FUNCTION IGS_PR_upd_pen_clash(
1562 p_person_id IN NUMBER ,
1563 p_course_cd IN VARCHAR2 ,
1564 p_spo_sequence_number IN NUMBER ,
1565 p_application_type IN VARCHAR2 ,
1566 p_message_text OUT NOCOPY VARCHAR2 ,
1567 p_message_level OUT NOCOPY VARCHAR2 )
1568 RETURN BOOLEAN IS
1569 gv_other_detail VARCHAR2(255);
1570 v_message_text2 VARCHAR2(200);
1571 BEGIN -- IGS_PR_upd_pen_clash
1572 -- Get whether a clash exists between progression based encumbrance effects
1573 -- against a course and those trying to be applied.
1574 -- This routine returns a message number and one of three return types :
1575 -- WARNING : Report the message as a warning and proceed with application.
1576 -- BLOCK : Report the message as an error and do not proceed with the
1577 -- application.
1578 -- EXPIRED : Report the message as a warning - the process has expired an
1579 -- outcome to resolve a clash.
1580 -- ERROR : Report the message as an error - this indicates locking problems.
1581 DECLARE
1582 cst_batch CONSTANT VARCHAR2(10) := 'BATCH';
1583 cst_manual CONSTANT VARCHAR2(10) := 'MANUAL';
1584 cst_approved CONSTANT VARCHAR2(10) := 'APPROVED';
1585 cst_expired CONSTANT VARCHAR2(10) := 'EXPIRED';
1586 cst_block CONSTANT VARCHAR2(10) := 'BLOCK';
1587 cst_error CONSTANT VARCHAR2(10) := 'ERROR';
1588 cst_expire CONSTANT VARCHAR2(10) := 'EXPIRE';
1589 cst_ok CONSTANT VARCHAR2(10) := 'OK';
1590 cst_warning CONSTANT VARCHAR2(10) := 'WARNING';
1591 cst_rstr_ge_cp CONSTANT VARCHAR2(10) := 'RSTR_GE_CP';
1592 cst_rstr_le_cp CONSTANT VARCHAR2(10) := 'RSTR_LE_CP';
1593 cst_rstr_at_ty CONSTANT VARCHAR2(10) := 'RSTR_AT_TY';
1594 cst_sus_course CONSTANT VARCHAR2(10) := 'SUS_COURSE';
1595 cst_exc_course CONSTANT VARCHAR2(10) := 'EXC_COURSE';
1596 cst_exc_crs_gp CONSTANT VARCHAR2(10) := 'EXC_CRS_GP';
1597 cst_exc_crs_us CONSTANT VARCHAR2(10) := 'EXC_CRS_US';
1598 cst_exc_crs_u CONSTANT VARCHAR2(10) := 'EXC_CRS_U';
1599 cst_rqrd_crs_u CONSTANT VARCHAR2(10) := 'RQRD_CRS_U';
1600 cst_message_len CONSTANT INTEGER := 255;
1601 e_record_locked EXCEPTION;
1602 PRAGMA EXCEPTION_INIT (e_record_locked, -54);
1603 v_message_text1 VARCHAR2(255) DEFAULT NULL;
1604 v_message_text2 VARCHAR2(255) DEFAULT NULL;
1605 v_message_level VARCHAR2(10) DEFAULT NULL;
1606 v_encumbrance_type igs_pr_ou_type.encumbrance_type%TYPE;
1607 v_spo_expiry_dt IGS_PR_STDNT_PR_OU.expiry_dt%TYPE;
1608 v_unresolved_clash BOOLEAN DEFAULT FALSE;
1609 v_clash_type VARCHAR2(10);
1610 v_expire BOOLEAN;
1611 v_warning BOOLEAN;
1612 v_dummy VARCHAR2(1);
1613 CURSOR c_spo1 IS
1614 SELECT pot.encumbrance_type
1615 FROM IGS_PR_STDNT_PR_OU spo,
1616 igs_pr_ou_type pot
1617 WHERE spo.person_id = p_person_id AND
1618 spo.course_cd = p_course_cd AND
1619 spo.sequence_number = p_spo_sequence_number AND
1620 pot.progression_outcome_type = spo.progression_outcome_type;
1621 CURSOR c_spo2 IS
1622 SELECT spo.sequence_number,
1623 spo.progression_outcome_type,
1624 spo.expiry_dt,
1625 pot.encumbrance_type
1626 FROM IGS_PR_STDNT_PR_OU spo,
1627 igs_pr_ou_type pot
1628 WHERE spo.person_id = p_person_id AND
1629 spo.course_cd = p_course_cd AND
1630 spo.sequence_number <> p_spo_sequence_number AND
1631 spo.decision_status = cst_approved AND
1632 pot.progression_outcome_type = spo.progression_outcome_type AND
1633 pot.encumbrance_type IS NOT NULL AND
1634 IGS_PR_GEN_006.IGS_PR_GET_SPO_EXPIRY (
1635 p_person_id,
1636 p_course_cd,
1637 spo.sequence_number,
1638 spo.expiry_dt) <> cst_expired;
1639 CURSOR c_pee (
1640 cp_encumbrance_type igs_pr_ou_type.encumbrance_type%TYPE) IS
1641 SELECT 'X'
1642 FROM IGS_PE_PERSENC_EFFCT pee,
1643 IGS_PE_PERS_ENCUMB pen
1644 WHERE pen.person_id = p_person_id AND
1645 pen.spo_course_cd IS NULL AND
1646 (pen.expiry_dt IS NULL OR
1647 pen.expiry_dt > TRUNC(SYSDATE)) AND
1648 pee.person_id = pen.person_id AND
1649 pee.encumbrance_type = pen.encumbrance_type AND
1650 pee.pen_start_dt = pen.start_dt AND
1651 (pee.expiry_dt IS NULL OR
1652 pee.expiry_dt > TRUNC(SYSDATE)) AND
1653 pee.s_encmb_effect_type IN
1654 (
1655 SELECT s_encmb_effect_type
1656 FROM igs_fi_enc_dflt_eft etde
1657 WHERE etde.encumbrance_type = cp_encumbrance_type);
1658 CURSOR c_spo_expire (
1659 cp_spo_sequence_number IGS_PR_STDNT_PR_OU.sequence_number%TYPE) IS
1660 SELECT spo.*, spo.ROWID
1661 FROM IGS_PR_STDNT_PR_OU spo
1662 WHERE spo.person_id = p_person_id AND
1663 spo.course_cd = p_course_cd AND
1664 spo.sequence_number = cp_spo_sequence_number
1665 FOR UPDATE NOWAIT;
1666
1667 v_spo_expire_rec c_spo_expire%ROWTYPE;
1668 CURSOR c_edte (
1669 cp_encumbrance_type igs_pr_ou_type.encumbrance_type%TYPE) IS
1670 SELECT edte.s_encmb_effect_type
1671 FROM igs_fi_enc_dflt_eft edte
1672 WHERE edte.encumbrance_type = cp_encumbrance_type AND
1673 edte.s_encmb_effect_type IN (
1674 cst_sus_course,
1675 cst_exc_course,
1676 cst_exc_crs_gp,
1677 cst_exc_crs_us,
1678 cst_exc_crs_u,
1679 cst_rqrd_crs_u,
1680 cst_rstr_at_ty,
1681 cst_rstr_ge_cp,
1682 cst_rstr_le_cp);
1683 CURSOR c_pen_pee (
1684 cp_spo_sequence_number IGS_PR_STDNT_PR_OU.sequence_number%TYPE,
1685 cp_spo_expiry_dt IGS_PR_STDNT_PR_OU.expiry_dt%TYPE) IS
1686 SELECT pee.s_encmb_effect_type
1687 FROM IGS_PE_PERS_ENCUMB pen,
1688 IGS_PE_PERSENC_EFFCT pee
1689 WHERE pen.person_id = p_person_id AND
1690 pen.spo_course_cd = p_course_cd AND
1691 pen.spo_sequence_number = cp_spo_sequence_number AND
1692 pen.person_id = pee.person_id AND
1693 pen.encumbrance_type = pee.encumbrance_type AND
1694 pen.start_dt = pee.pen_start_dt AND
1695 pee.s_encmb_effect_type IN (
1696 cst_sus_course,
1697 cst_exc_course,
1698 cst_exc_crs_gp,
1699 cst_exc_crs_us,
1700 cst_exc_crs_u,
1701 cst_rqrd_crs_u,
1702 cst_rstr_at_ty,
1703 cst_rstr_ge_cp,
1704 cst_rstr_le_cp) AND
1705 (
1706 ( pee.expiry_dt IS NOT NULL AND
1707 pee.expiry_dt > TRUNC(SYSDATE)) OR
1708 ( pee.expiry_dt IS NULL AND
1709 IGS_PR_GEN_006.IGS_PR_GET_SPO_EXPIRY (
1710 p_person_id,
1711 p_course_cd,
1712 cp_spo_sequence_number,
1713 cp_spo_expiry_dt) <> cst_expired)
1714 );
1715 FUNCTION prgpl_upd_pen_check_clash (
1716 p_old_effect IGS_EN_ENCMB_EFCTTYP_V.s_encmb_effect_type%TYPE,
1717 p_new_effect IGS_EN_ENCMB_EFCTTYP_V.s_encmb_effect_type%TYPE)
1718 RETURN VARCHAR2
1719 IS
1720 gvl_other_detail VARCHAR2(255);
1721 BEGIN -- prgpl_upd_pen_check_clash
1722 BEGIN
1723 IF p_old_effect = cst_sus_course THEN
1724 IF p_application_type = cst_manual THEN
1725 IF p_new_effect IN (
1726 cst_exc_crs_u,
1727 cst_exc_crs_us) THEN
1728 RETURN cst_warning;
1729 ELSIF p_new_effect IN (
1730 cst_exc_course,
1731 cst_exc_crs_gp,
1732 cst_rqrd_crs_u,
1733 cst_rstr_at_ty,
1734 cst_rstr_ge_cp,
1735 cst_rstr_le_cp,
1736 cst_sus_course) THEN
1737 RETURN cst_block;
1738 END IF;
1739 ELSE -- batch process
1740 IF p_new_effect IN (
1741 cst_exc_crs_u,
1742 cst_exc_crs_us) THEN
1743 RETURN cst_warning;
1744 ELSIF p_new_effect IN (
1745 cst_exc_course,
1746 cst_exc_crs_gp) THEN
1747 RETURN cst_expire;
1748 ELSIF p_new_effect IN (
1749 cst_rqrd_crs_u,
1750 cst_rstr_at_ty,
1751 cst_rstr_ge_cp,
1752 cst_rstr_le_cp,
1753 cst_sus_course) THEN
1754 RETURN cst_block;
1755 END IF;
1756 END IF;
1757 ELSIF p_old_effect = cst_rstr_at_ty THEN
1758 IF p_application_type = cst_manual THEN
1759 IF p_new_effect IN (
1760 cst_exc_crs_u,
1761 cst_exc_crs_us,
1762 cst_rqrd_crs_u) THEN
1763 RETURN cst_ok;
1764 ELSIF p_new_effect IN (
1765 cst_exc_course,
1766 cst_exc_crs_gp) THEN
1767 RETURN cst_warning;
1768 ELSIF p_new_effect IN (
1769 cst_rstr_at_ty,
1770 cst_rstr_ge_cp,
1771 cst_rstr_le_cp,
1772 cst_sus_course) THEN
1773 RETURN cst_block;
1774 END IF;
1775 ELSE -- batch process
1776 IF p_new_effect IN (
1777 cst_exc_crs_u,
1778 cst_exc_crs_us) THEN
1779 RETURN cst_ok;
1780 ELSIF p_new_effect IN (
1781 cst_exc_course,
1782 cst_exc_crs_gp,
1783 cst_rqrd_crs_u) THEN
1784 RETURN cst_warning;
1785 ELSIF p_new_effect IN (
1786 cst_rstr_at_ty,
1787 cst_rstr_ge_cp,
1788 cst_rstr_le_cp,
1789 cst_sus_course) THEN
1790 RETURN cst_block;
1791 END IF;
1792 END IF;
1793 ELSIF p_old_effect = cst_rqrd_crs_u THEN
1794 IF p_application_type = cst_manual THEN
1795 IF p_new_effect IN (
1796 cst_exc_crs_u,
1797 cst_exc_crs_us,
1798 cst_rqrd_crs_u,
1799 cst_rstr_at_ty,
1800 cst_rstr_ge_cp,
1801 cst_rstr_le_cp) THEN
1802 RETURN cst_ok;
1803 ELSIF p_new_effect IN (
1804 cst_exc_course,
1805 cst_exc_crs_gp,
1806 cst_sus_course) THEN
1807 RETURN cst_warning;
1808 END IF;
1809 ELSE -- batch process
1810 IF p_new_effect IN (
1811 cst_exc_crs_u,
1812 cst_exc_crs_us,
1813 cst_rqrd_crs_u,
1814 cst_rstr_at_ty,
1815 cst_rstr_ge_cp,
1816 cst_rstr_le_cp) THEN
1817 RETURN cst_ok;
1818 ELSIF p_new_effect IN (
1819 cst_sus_course) THEN
1820 RETURN cst_warning;
1821 ELSIF p_new_effect IN (
1822 cst_exc_course,
1823 cst_exc_crs_gp) THEN
1824 RETURN cst_expire;
1825 END IF;
1826 END IF;
1827 ELSIF p_old_effect = cst_exc_crs_u THEN
1828 IF p_application_type = cst_manual THEN
1829 IF p_new_effect IN (
1830 cst_exc_course,
1831 cst_exc_crs_gp,
1832 cst_exc_crs_u,
1833 cst_exc_crs_us,
1834 cst_rqrd_crs_u,
1835 cst_rstr_at_ty,
1836 cst_rstr_ge_cp,
1837 cst_rstr_le_cp,
1838 cst_sus_course) THEN
1839 RETURN cst_ok;
1840 END IF;
1841 ELSE -- batch process
1842 IF p_new_effect IN (
1843 cst_exc_course,
1844 cst_exc_crs_gp,
1845 cst_exc_crs_u,
1846 cst_exc_crs_us,
1847 cst_rqrd_crs_u,
1848 cst_rstr_at_ty,
1849 cst_rstr_ge_cp,
1850 cst_rstr_le_cp,
1851 cst_sus_course) THEN
1852 RETURN cst_ok;
1853 END IF;
1854 END IF;
1855 ELSIF p_old_effect = cst_exc_course THEN
1856 IF p_application_type = cst_manual THEN
1857 IF p_new_effect IN (
1858 cst_exc_crs_u,
1859 cst_exc_crs_us) THEN
1860 RETURN cst_ok;
1861 ELSIF p_new_effect IN (
1862 cst_rqrd_crs_u,
1863 cst_rstr_at_ty,
1864 cst_rstr_ge_cp,
1865 cst_rstr_le_cp) THEN
1866 RETURN cst_warning;
1867 ELSIF p_new_effect IN (
1868 cst_exc_course,
1869 cst_exc_crs_gp,
1870 cst_sus_course) THEN
1871 RETURN cst_block;
1872 END IF;
1873 ELSE -- batch process
1874 IF p_new_effect IN (
1875 cst_exc_crs_u,
1876 cst_exc_crs_us) THEN
1877 RETURN cst_ok;
1878 ELSIF p_new_effect IN (
1879 cst_rqrd_crs_u,
1880 cst_rstr_at_ty,
1881 cst_rstr_ge_cp,
1882 cst_rstr_le_cp) THEN
1883 RETURN cst_warning;
1884 ELSIF p_new_effect IN (
1885 cst_exc_course,
1886 cst_exc_crs_gp,
1887 cst_sus_course) THEN
1888 RETURN cst_block;
1889 END IF;
1890 END IF;
1891 ELSIF p_old_effect = cst_exc_crs_gp THEN
1892 IF p_application_type = cst_manual THEN
1893 IF p_new_effect IN (
1894 cst_exc_crs_u,
1895 cst_exc_crs_us) THEN
1896 RETURN cst_ok;
1897 ELSIF p_new_effect IN (
1898 cst_rqrd_crs_u,
1899 cst_rstr_at_ty,
1900 cst_rstr_ge_cp,
1901 cst_rstr_le_cp,
1902 cst_sus_course) THEN
1903 RETURN cst_warning;
1904 ELSIF p_new_effect IN (
1905 cst_exc_course,
1906 cst_exc_crs_gp) THEN
1907 RETURN cst_block;
1908 END IF;
1909 ELSE -- batch process
1910 IF p_new_effect IN (
1911 cst_exc_crs_u,
1912 cst_exc_crs_us) THEN
1913 RETURN cst_ok;
1914 ELSIF p_new_effect IN (
1915 cst_rqrd_crs_u,
1916 cst_rstr_at_ty,
1917 cst_rstr_ge_cp,
1918 cst_rstr_le_cp,
1919 cst_sus_course) THEN
1920 RETURN cst_warning;
1921 ELSIF p_new_effect IN (
1922 cst_exc_course,
1923 cst_exc_crs_gp) THEN
1924 RETURN cst_block;
1925 END IF;
1926 END IF;
1927 ELSIF p_old_effect = cst_exc_crs_us THEN
1928 IF p_application_type = cst_manual THEN
1929 IF p_new_effect IN (
1930 cst_exc_course,
1931 cst_exc_crs_gp,
1932 cst_exc_crs_u,
1933 cst_exc_crs_us,
1934 cst_rqrd_crs_u,
1935 cst_rstr_at_ty,
1936 cst_rstr_ge_cp,
1937 cst_rstr_le_cp,
1938 cst_sus_course) THEN
1939 RETURN cst_ok;
1940 END IF;
1941 ELSE -- batch process
1942 IF p_new_effect IN (
1943 cst_exc_course,
1944 cst_exc_crs_gp,
1945 cst_exc_crs_u,
1946 cst_exc_crs_us,
1947 cst_rqrd_crs_u,
1948 cst_rstr_at_ty,
1949 cst_rstr_ge_cp,
1950 cst_rstr_le_cp,
1951 cst_sus_course) THEN
1952 RETURN cst_ok;
1953 END IF;
1954 END IF;
1955 ELSIF p_old_effect = cst_rstr_ge_cp THEN
1956 IF p_application_type = cst_manual THEN
1957 IF p_new_effect IN (
1958 cst_exc_crs_u,
1959 cst_exc_crs_us,
1960 cst_rqrd_crs_u) THEN
1961 RETURN cst_ok;
1962 ELSIF p_new_effect IN (
1963 cst_exc_course,
1964 cst_exc_crs_gp,
1965 cst_sus_course) THEN
1966 RETURN cst_warning;
1967 ELSIF p_new_effect IN (
1968 cst_rstr_at_ty,
1969 cst_rstr_ge_cp,
1970 cst_rstr_le_cp) THEN
1971 RETURN cst_block;
1972 END IF;
1973 ELSE -- batch process
1974 IF p_new_effect IN (
1975 cst_exc_crs_u,
1976 cst_exc_crs_us,
1977 cst_rqrd_crs_u) THEN
1978 RETURN cst_ok;
1979 ELSIF p_new_effect IN (
1980 cst_exc_course,
1981 cst_exc_crs_gp,
1982 cst_sus_course) THEN
1983 RETURN cst_warning;
1984 ELSIF p_new_effect IN (
1985 cst_rstr_at_ty,
1986 cst_rstr_ge_cp,
1987 cst_rstr_le_cp) THEN
1988 RETURN cst_block;
1989 END IF;
1990 END IF;
1991 ELSIF p_old_effect = cst_rstr_le_cp THEN
1992 IF p_application_type = cst_manual THEN
1993 IF p_new_effect IN (
1994 cst_exc_crs_u,
1995 cst_exc_crs_us,
1996 cst_rqrd_crs_u) THEN
1997 RETURN cst_ok;
1998 ELSIF p_new_effect IN (
1999 cst_exc_course,
2000 cst_exc_crs_gp,
2001 cst_sus_course) THEN
2002 RETURN cst_warning;
2003 ELSIF p_new_effect IN (
2004 cst_rstr_at_ty,
2005 cst_rstr_ge_cp,
2006 cst_rstr_le_cp) THEN
2007 RETURN cst_block;
2008 END IF;
2009 ELSE -- batch process
2010 IF p_new_effect IN (
2011 cst_exc_crs_u,
2012 cst_exc_crs_us,
2013 cst_rqrd_crs_u) THEN
2014 RETURN cst_ok;
2015 ELSIF p_new_effect IN (
2016 cst_exc_course,
2017 cst_exc_crs_gp,
2018 cst_sus_course) THEN
2019 RETURN cst_warning;
2020 ELSIF p_new_effect IN (
2021 cst_rstr_at_ty,
2022 cst_rstr_ge_cp,
2023 cst_rstr_le_cp) THEN
2024 RETURN cst_block;
2025 END IF;
2026 END IF;
2027 END IF;
2028 RETURN cst_ok;
2029 END;
2030 EXCEPTION
2031 WHEN OTHERS THEN
2032 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2033 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.IGS_PR_UPD_PEN_CLASH.PRGPL_UPD_PEN_CHECK_CLASH');
2034 IGS_GE_MSG_STACK.ADD;
2035 App_Exception.Raise_Exception;
2036 END prgpl_upd_pen_check_clash;
2037 BEGIN
2038 -- Initialise output parameters
2039 p_message_level := NULL;
2040 p_message_text := NULL;
2041 SAVEPOINT sp_before_check;
2042 OPEN c_spo1;
2043 FETCH c_spo1 INTO v_encumbrance_type;
2044 IF c_spo1%NOTFOUND THEN
2045 CLOSE c_spo1;
2046 -- No match - return with no error ; problem with calling routine
2047 RETURN TRUE;
2048 ELSE
2049 CLOSE c_spo1;
2050 IF v_encumbrance_type IS NULL THEN
2051 -- No encumbrances to apply ; no possible clashes
2052 RETURN TRUE;
2053 END IF;
2054 END IF;
2055 -- Check for non-progression encumbrances containing same effect types.
2056 OPEN c_pee (v_encumbrance_type);
2057 FETCH c_pee INTO v_dummy;
2058 IF c_pee%FOUND THEN
2059 CLOSE c_pee;
2060 -- p_message_text := IGS_GE_GEN_002.GENP_GET_MESSAGE(5605);
2061 p_message_level := cst_error;
2062 RETURN FALSE;
2063 ELSE
2064 CLOSE c_pee;
2065 END IF;
2066 -- Select other progression outcomes that are currently active and are
2067 -- related to encumbrances
2068 FOR v_spo2_rec IN c_spo2 LOOP
2069 v_expire := FALSE;
2070 v_warning := FALSE;
2071 -- Loop through the effects that are to be applied. Only effect types
2072 -- that can potentially clash are tested.
2073 FOR v_edte_rec IN c_edte (v_encumbrance_type) LOOP
2074 FOR v_pen_pee_rec IN c_pen_pee (
2075 v_spo2_rec.sequence_number,
2076 v_spo2_rec.expiry_dt) LOOP
2077 -- Check for a clash
2078 v_clash_type := prgpl_upd_pen_check_clash (
2079 v_pen_pee_rec.s_encmb_effect_type,
2080 v_edte_rec.s_encmb_effect_type);
2081 IF v_clash_type = cst_block THEN
2082 IF p_application_type = cst_manual THEN
2083
2084 -- Modified by Prajeesh on 08-Jul-2002
2085 -- Previously it was hardcoded and hence it was not NLS Complaint
2086 -- Now it is registered in Messages and messages are called instead of hardcoded values
2087 FND_MESSAGE.SET_NAME('IGS','IGS_PR_ENC_MAN_BLOCK_TYP');
2088 FND_MESSAGE.SET_TOKEN('OUT_TYP',v_spo2_rec.progression_outcome_type);
2089 IGS_GE_MSG_STACK.ADD;
2090 v_message_text1 := FND_MESSAGE.GET;
2091
2092 ELSE
2093 -- Modified by Prajeesh on 08-Jul-2002
2094 -- Previously it was hardcoded and hence it was not NLS Complaint
2095 -- Now it is registered in Messages and messages are called instead of hardcoded values
2096
2097 FND_MESSAGE.SET_NAME('IGS','IGS_PR_ENC_NMAN_BLOCK_TYP');
2098 FND_MESSAGE.SET_TOKEN('OUT_TYP',v_spo2_rec.progression_outcome_type);
2099 IGS_GE_MSG_STACK.ADD;
2100 v_message_text1 := FND_MESSAGE.GET;
2101
2102 END IF;
2103 v_message_level := cst_block;
2104 v_unresolved_clash := TRUE;
2105 -- Exit the routine ; no point in continuing
2106 EXIT;
2107 ELSIF v_clash_type = cst_expire THEN
2108 v_expire := TRUE;
2109 ELSIF v_clash_type = cst_warning THEN
2110 v_warning := TRUE;
2111 END IF;
2112 END LOOP; -- c_pen_pee
2113 IF v_unresolved_clash THEN
2114 EXIT;
2115 END IF;
2116 IF v_expire THEN
2117 IF p_application_type = cst_manual THEN
2118 IF v_message_text1 IS NULL OR v_message_level = cst_warning THEN
2119 -- Modified by Prajeesh on 08-Jul-2002
2120 -- Previously it was hardcoded and hence it was not NLS Complaint
2121 -- Now it is registered in Messages and messages are called instead of hardcoded values
2122
2123 FND_MESSAGE.SET_NAME('IGS','IGS_PR_ENC_MAN_EXP_TYP');
2124 FND_MESSAGE.SET_TOKEN('OUT_TYP',v_spo2_rec.progression_outcome_type);
2125 IGS_GE_MSG_STACK.ADD;
2126 v_message_text1 := FND_MESSAGE.GET;
2127 v_message_level := cst_expire;
2128 END IF;
2129 ELSE
2130 IF v_message_text1 IS NULL THEN
2131 v_message_text1 := '';
2132 END IF;
2133 -- Modified by Prajeesh on 08-Jul-2002
2134 -- Previously it was hardcoded and hence it was not NLS Complaint
2135 -- Now it is registered in Messages and messages are called instead of hardcoded values
2136
2137 FND_MESSAGE.SET_NAME('IGS','IGS_PR_ENC_NMAN_EXP_TYP');
2138 FND_MESSAGE.SET_TOKEN('OUT_TYP',v_spo2_rec.progression_outcome_type);
2139 IGS_GE_MSG_STACK.ADD;
2140 v_message_text2 := FND_MESSAGE.GET;
2141
2142 v_message_text1 := v_message_text1 ||v_message_text2;
2143
2144 v_message_level := cst_expire;
2145 END IF;
2146 -- Call routine to expire the spo that is causing the issue. It will
2147 -- apply the changes immediately so that the clashing effects will be
2148 -- removed.
2149 BEGIN
2150 OPEN c_spo_expire (
2151 v_spo2_rec.sequence_number);
2152 FETCH c_spo_expire INTO v_spo_expire_rec;
2153 IGS_PR_STDNT_PR_OU_PKG.UPDATE_ROW(
2154 X_ROWID => v_spo_expire_rec.ROWID,
2155 X_PERSON_ID => v_spo_expire_rec.PERSON_ID,
2156 X_COURSE_CD => v_spo_expire_rec.COURSE_CD,
2157 X_SEQUENCE_NUMBER => v_spo_expire_rec.SEQUENCE_NUMBER,
2158 X_PRG_CAL_TYPE => v_spo_expire_rec.PRG_CAL_TYPE,
2159 X_PRG_CI_SEQUENCE_NUMBER => v_spo_expire_rec.PRG_CI_SEQUENCE_NUMBER,
2160 X_RULE_CHECK_DT => v_spo_expire_rec.RULE_CHECK_DT,
2161 X_PROGRESSION_RULE_CAT => v_spo_expire_rec.PROGRESSION_RULE_CAT,
2162 X_PRA_SEQUENCE_NUMBER => v_spo_expire_rec.PRA_SEQUENCE_NUMBER,
2163 X_PRO_SEQUENCE_NUMBER => v_spo_expire_rec.PRO_SEQUENCE_NUMBER,
2164 X_PROGRESSION_OUTCOME_TYPE => v_spo_expire_rec.PROGRESSION_OUTCOME_TYPE,
2165 X_DURATION => v_spo_expire_rec.DURATION,
2166 X_DURATION_TYPE => v_spo_expire_rec.DURATION_TYPE,
2167 X_DECISION_STATUS => v_spo_expire_rec.DECISION_STATUS,
2168 X_DECISION_DT => v_spo_expire_rec.DECISION_DT,
2169 X_DECISION_ORG_UNIT_CD => v_spo_expire_rec.DECISION_ORG_UNIT_CD,
2170 X_DECISION_OU_START_DT => v_spo_expire_rec.DECISION_OU_START_DT,
2171 X_APPLIED_DT => v_spo_expire_rec.APPLIED_DT,
2172 X_SHOW_CAUSE_EXPIRY_DT => v_spo_expire_rec.SHOW_CAUSE_EXPIRY_DT,
2173 X_SHOW_CAUSE_DT => v_spo_expire_rec.SHOW_CAUSE_DT,
2174 X_SHOW_CAUSE_OUTCOME_DT => v_spo_expire_rec.SHOW_CAUSE_OUTCOME_DT,
2175 X_SHOW_CAUSE_OUTCOME_TYPE => v_spo_expire_rec.SHOW_CAUSE_OUTCOME_TYPE,
2176 X_APPEAL_EXPIRY_DT => v_spo_expire_rec.APPEAL_EXPIRY_DT,
2177 X_APPEAL_DT => v_spo_expire_rec.APPEAL_DT,
2178 X_APPEAL_OUTCOME_DT => v_spo_expire_rec.APPEAL_OUTCOME_DT,
2179 X_APPEAL_OUTCOME_TYPE => v_spo_expire_rec.APPEAL_OUTCOME_TYPE,
2180 X_ENCMB_COURSE_GROUP_CD => v_spo_expire_rec.ENCMB_COURSE_GROUP_CD,
2181 X_RESTRICTED_ENROLMENT_CP => v_spo_expire_rec.RESTRICTED_ENROLMENT_CP,
2182 X_RESTRICTED_ATTENDANCE_TYPE => v_spo_expire_rec.RESTRICTED_ATTENDANCE_TYPE,
2183 X_COMMENTS => v_spo_expire_rec.COMMENTS,
2184 X_SHOW_CAUSE_COMMENTS => v_spo_expire_rec.SHOW_CAUSE_COMMENTS,
2185 X_APPEAL_COMMENTS => v_spo_expire_rec.APPEAL_COMMENTS,
2186 X_EXPIRY_DT => v_spo_expire_rec.EXPIRY_DT,
2187 X_PRO_PRA_SEQUENCE_NUMBER => v_spo_expire_rec.PRO_PRA_SEQUENCE_NUMBER,
2188 X_MODE => 'R'
2189 );
2190 CLOSE c_spo_expire;
2191 EXCEPTION
2192 WHEN e_record_locked THEN
2193 IF c_spo_expire%ISOPEN THEN
2194 CLOSE c_spo_expire;
2195 END IF;
2196 v_unresolved_clash := TRUE;
2197 -- p_message_text := IGS_GE_GEN_002.GENP_GET_MESSAGE(5287);
2198 p_message_level := cst_error;
2199 EXIT;
2200 WHEN OTHERS THEN
2201 RAISE;
2202 END;
2203 ELSIF v_warning THEN
2204 IF p_application_type = cst_manual THEN
2205 IF v_message_text1 IS NULL THEN
2206
2207 -- Modified by Prajeesh on 08-Jul-2002
2208 -- Previously it was hardcoded and hence it was not NLS Complaint
2209 -- Now it is registered in Messages and messages are called instead of hardcoded values
2210
2211 FND_MESSAGE.SET_NAME('IGS','IGS_PR_ENC_MAN_WARN_TYP');
2212 FND_MESSAGE.SET_TOKEN('OUT_TYP',v_spo2_rec.progression_outcome_type);
2213 IGS_GE_MSG_STACK.ADD;
2214 v_message_text1 := FND_MESSAGE.GET;
2215
2216 v_message_level := cst_warning;
2217 END IF;
2218 ELSE
2219
2220 -- Modified by Prajeesh on 08-Jul-2002
2221 -- Previously it was hardcoded and hence it was not NLS Complaint
2222 -- Now it is registered in Messages and messages are called instead of hardcoded values
2223
2224 FND_MESSAGE.SET_NAME('IGS','IGS_PR_ENC_NMAN_WARN_TYP');
2225 FND_MESSAGE.SET_TOKEN('OUT_TYP',v_spo2_rec.progression_outcome_type);
2226 IGS_GE_MSG_STACK.ADD;
2227 v_message_text1 := FND_MESSAGE.GET;
2228
2229 v_message_level := cst_warning;
2230 END IF;
2231 END IF;
2232 END LOOP; -- c_edte
2233 IF v_unresolved_clash THEN
2234 EXIT;
2235 END IF;
2236 END LOOP; -- c_spo2
2237 IF v_message_level IS NOT NULL THEN
2238 p_message_level := v_message_level;
2239 p_message_text := v_message_text1 || '.';
2240 END IF;
2241 IF v_unresolved_clash THEN
2242 ROLLBACK TO sp_before_check;
2243 RETURN FALSE;
2244 END IF;
2245 RETURN TRUE;
2246 EXCEPTION
2247 WHEN OTHERS THEN
2248 IF c_spo1%ISOPEN THEN
2249 CLOSE c_spo1;
2250 END IF;
2251 IF c_pee%ISOPEN THEN
2252 CLOSE c_pee;
2253 END IF;
2254 IF c_pen_pee%ISOPEN THEN
2255 CLOSE c_pen_pee;
2256 END IF;
2257 IF c_spo_expire%ISOPEN THEN
2258 CLOSE c_spo_expire;
2259 END IF;
2260 IF c_edte%ISOPEN THEN
2261 CLOSE c_edte;
2262 END IF;
2263 IF c_spo2%ISOPEN THEN
2264 CLOSE c_spo2;
2265 END IF;
2266 RAISE;
2267 END;
2268 EXCEPTION
2269 WHEN OTHERS THEN
2270 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2271 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.IGS_PR_UPD_PEN_CLASH');
2272 IGS_GE_MSG_STACK.ADD;
2273 App_Exception.Raise_Exception;
2274 END IGS_PR_upd_pen_clash;
2275
2276 FUNCTION IGS_PR_UPD_SCA_STATUS(
2277 p_person_id IN NUMBER ,
2278 p_course_cd IN VARCHAR2 ,
2279 p_current_progression_status IN VARCHAR2 ,
2280 p_course_version IN NUMBER ,
2281 p_message_name OUT NOCOPY VARCHAR2 )
2282 RETURN BOOLEAN IS
2283 gv_other_detail VARCHAR2(255);
2284 BEGIN -- IGS_PR_upd_sca_status
2285 -- Re-derive the course attempt status for a single student course attempt.
2286 -- This routine will call the derivation, and if the result is different from
2287 -- the current status, will update with the new status. Locking is considered.
2288 DECLARE
2289 e_resource_busy EXCEPTION;
2290 PRAGMA EXCEPTION_INIT (e_resource_busy, -54);
2291 v_course_version IGS_EN_STDNT_PS_ATT.version_number%TYPE;
2292 v_progression_status IGS_EN_STDNT_PS_ATT.progression_status%TYPE;
2293 v_current_progression_status IGS_EN_STDNT_PS_ATT.progression_status%TYPE;
2294 v_dummy VARCHAR2(1);
2295 CURSOR c_sca_upd IS
2296 SELECT sca.*, sca.ROWID
2297 FROM IGS_EN_STDNT_PS_ATT sca
2298 WHERE sca.person_id = p_person_id AND
2299 sca.course_cd = p_course_cd
2300 FOR UPDATE NOWAIT;
2301
2302 v_sca_upd_rec c_sca_upd%ROWTYPE;
2303 CURSOR c_sca IS
2304 SELECT sca.version_number,
2305 sca.progression_status
2306 FROM IGS_EN_STDNT_PS_ATT sca
2307 WHERE sca.person_id = p_person_id AND
2308 sca.course_cd = p_course_cd;
2309
2310 CURSOR c_person(cp_party_id Number) IS
2311 SELECT PARTY_NUMBER FROM HZ_PARTIES
2312 WHERE PARTY_ID = cp_party_id;
2313
2314 lv_person_number HZ_PARTIES.PARTY_NUMBER%TYPE;
2315
2316 BEGIN
2317 -- Set the default message number
2318 --kdande; 19-Jul-2002; Bug# 2462120; Nullified the message name since it was defaulted to 0.
2319 p_message_name := '';
2320 IF p_person_id IS NULL OR
2321 p_course_cd IS NULL THEN
2322 RETURN TRUE;
2323 END IF;
2324 IF p_course_version IS NULL OR
2325 p_current_progression_status IS NULL THEN
2326 OPEN c_sca;
2327 FETCH c_sca INTO
2328 v_course_version,
2329 v_current_progression_status;
2330 IF c_sca%NOTFOUND THEN
2331 CLOSE c_sca;
2332 RETURN TRUE;
2333 END IF;
2334 CLOSE c_sca;
2335 ELSE
2336 v_course_version := p_course_version;
2337 v_current_progression_status := p_current_progression_status;
2338 END IF;
2339
2340 -- Call derivation routine
2341 v_progression_status := IGS_PR_GEN_005.IGS_PR_get_prg_status(
2342 p_person_id,
2343 p_course_cd,
2344 v_course_version,
2345 NULL,
2346 NULL
2347 );
2348 IF v_progression_status <>NVL( v_current_progression_status, 'NONE' ) THEN
2349 BEGIN
2350 OPEN c_sca_upd;
2351 FETCH c_sca_upd INTO v_sca_upd_rec;
2352 IF c_sca_upd%FOUND THEN
2353 IGS_EN_STDNT_PS_ATT_PKG.UPDATE_ROW(
2354 X_ROWID => v_sca_upd_rec.ROWID,
2355 X_PERSON_ID => v_sca_upd_rec.PERSON_ID,
2356 X_COURSE_CD => v_sca_upd_rec.COURSE_CD,
2357 X_ADVANCED_STANDING_IND => v_sca_upd_rec.ADVANCED_STANDING_IND,
2358 X_FEE_CAT => v_sca_upd_rec.FEE_CAT,
2359 X_CORRESPONDENCE_CAT => v_sca_upd_rec.CORRESPONDENCE_CAT,
2360 X_SELF_HELP_GROUP_IND => v_sca_upd_rec.SELF_HELP_GROUP_IND,
2361 X_LOGICAL_DELETE_DT => v_sca_upd_rec.LOGICAL_DELETE_DT,
2362 X_ADM_ADMISSION_APPL_NUMBER => v_sca_upd_rec.ADM_ADMISSION_APPL_NUMBER,
2363 X_ADM_NOMINATED_COURSE_CD => v_sca_upd_rec.ADM_NOMINATED_COURSE_CD,
2364 X_ADM_SEQUENCE_NUMBER => v_sca_upd_rec.ADM_SEQUENCE_NUMBER,
2365 X_VERSION_NUMBER => v_sca_upd_rec.VERSION_NUMBER,
2366 X_CAL_TYPE => v_sca_upd_rec.CAL_TYPE,
2367 X_LOCATION_CD => v_sca_upd_rec.LOCATION_CD,
2368 X_ATTENDANCE_MODE => v_sca_upd_rec.ATTENDANCE_MODE,
2369 X_ATTENDANCE_TYPE => v_sca_upd_rec.ATTENDANCE_TYPE,
2370 X_COO_ID => v_sca_upd_rec.COO_ID,
2371 X_STUDENT_CONFIRMED_IND => v_sca_upd_rec.STUDENT_CONFIRMED_IND,
2372 X_COMMENCEMENT_DT => v_sca_upd_rec.COMMENCEMENT_DT,
2373 X_COURSE_ATTEMPT_STATUS => v_sca_upd_rec.COURSE_ATTEMPT_STATUS,
2374 X_PROGRESSION_STATUS => v_progression_status, --updating this column
2375 X_DERIVED_ATT_TYPE => v_sca_upd_rec.DERIVED_ATT_TYPE,
2376 X_DERIVED_ATT_MODE => v_sca_upd_rec.DERIVED_ATT_MODE,
2377 X_PROVISIONAL_IND => v_sca_upd_rec.PROVISIONAL_IND,
2378 X_DISCONTINUED_DT => v_sca_upd_rec.DISCONTINUED_DT,
2379 X_DISCONTINUATION_REASON_CD => v_sca_upd_rec.DISCONTINUATION_REASON_CD,
2380 X_LAPSED_DT => v_sca_upd_rec.LAPSED_DT,
2381 X_FUNDING_SOURCE => v_sca_upd_rec.FUNDING_SOURCE,
2382 X_EXAM_LOCATION_CD => v_sca_upd_rec.EXAM_LOCATION_CD,
2383 X_DERIVED_COMPLETION_YR => v_sca_upd_rec.DERIVED_COMPLETION_YR,
2384 X_DERIVED_COMPLETION_PERD => v_sca_upd_rec.DERIVED_COMPLETION_PERD,
2385 X_NOMINATED_COMPLETION_YR => v_sca_upd_rec.NOMINATED_COMPLETION_YR,
2386 X_NOMINATED_COMPLETION_PERD => v_sca_upd_rec.NOMINATED_COMPLETION_PERD,
2387 X_RULE_CHECK_IND => v_sca_upd_rec.RULE_CHECK_IND,
2388 X_WAIVE_OPTION_CHECK_IND => v_sca_upd_rec.WAIVE_OPTION_CHECK_IND,
2389 X_LAST_RULE_CHECK_DT => v_sca_upd_rec.LAST_RULE_CHECK_DT,
2390 X_PUBLISH_OUTCOMES_IND => v_sca_upd_rec.PUBLISH_OUTCOMES_IND,
2391 X_COURSE_RQRMNT_COMPLETE_IND => v_sca_upd_rec.COURSE_RQRMNT_COMPLETE_IND,
2392 X_COURSE_RQRMNTS_COMPLETE_DT => v_sca_upd_rec.COURSE_RQRMNTS_COMPLETE_DT,
2393 X_S_COMPLETED_SOURCE_TYPE => v_sca_upd_rec.S_COMPLETED_SOURCE_TYPE,
2394 X_OVERRIDE_TIME_LIMITATION => v_sca_upd_rec.OVERRIDE_TIME_LIMITATION,
2395 X_MODE => 'R',
2396 X_LAST_DATE_OF_ATTENDANCE => v_sca_upd_rec.LAST_DATE_OF_ATTENDANCE,
2397 X_DROPPED_BY => v_sca_upd_rec.DROPPED_BY,
2398 X_IGS_PR_CLASS_STD_ID => v_sca_upd_rec.IGS_PR_CLASS_STD_ID,
2399 X_PRIMARY_PROGRAM_TYPE => v_sca_upd_rec.PRIMARY_PROGRAM_TYPE,
2400 X_PRIMARY_PROG_TYPE_SOURCE => v_sca_upd_rec.PRIMARY_PROG_TYPE_SOURCE,
2401 X_CATALOG_CAL_TYPE => v_sca_upd_rec.CATALOG_CAL_TYPE,
2402 X_CATALOG_SEQ_NUM => v_sca_upd_rec.CATALOG_SEQ_NUM,
2403 X_KEY_PROGRAM => v_sca_upd_rec.KEY_PROGRAM,
2404 X_MANUAL_OVR_CMPL_DT_IND => v_sca_upd_rec.MANUAL_OVR_CMPL_DT_IND ,
2405 X_OVERRIDE_CMPL_DT => v_sca_upd_rec.OVERRIDE_CMPL_DT ,
2406 X_ATTRIBUTE_CATEGORY => v_sca_upd_rec.ATTRIBUTE_CATEGORY ,
2407 X_ATTRIBUTE1 => v_sca_upd_rec.ATTRIBUTE1 ,
2408 X_ATTRIBUTE2 => v_sca_upd_rec.ATTRIBUTE2 ,
2409 X_ATTRIBUTE3 => v_sca_upd_rec.ATTRIBUTE3 ,
2410 X_ATTRIBUTE4 => v_sca_upd_rec.ATTRIBUTE4 ,
2411 X_ATTRIBUTE5 => v_sca_upd_rec.ATTRIBUTE5 ,
2412 X_ATTRIBUTE6 => v_sca_upd_rec.ATTRIBUTE6 ,
2413 X_ATTRIBUTE7 => v_sca_upd_rec.ATTRIBUTE7 ,
2414 X_ATTRIBUTE8 => v_sca_upd_rec.ATTRIBUTE8 ,
2415 X_ATTRIBUTE9 => v_sca_upd_rec.ATTRIBUTE9 ,
2416 X_ATTRIBUTE10 => v_sca_upd_rec.ATTRIBUTE10 ,
2417 X_ATTRIBUTE11 => v_sca_upd_rec.ATTRIBUTE11 ,
2418 X_ATTRIBUTE12 => v_sca_upd_rec.ATTRIBUTE12 ,
2419 X_ATTRIBUTE13 => v_sca_upd_rec.ATTRIBUTE13 ,
2420 X_ATTRIBUTE14 => v_sca_upd_rec.ATTRIBUTE14 ,
2421 X_ATTRIBUTE15 => v_sca_upd_rec.ATTRIBUTE15 ,
2422 X_ATTRIBUTE16 => v_sca_upd_rec.ATTRIBUTE16 ,
2423 X_ATTRIBUTE17 => v_sca_upd_rec.ATTRIBUTE17 ,
2424 X_ATTRIBUTE18 => v_sca_upd_rec.ATTRIBUTE18 ,
2425 X_ATTRIBUTE19 => v_sca_upd_rec.ATTRIBUTE19 ,
2426 X_ATTRIBUTE20 => v_sca_upd_rec.ATTRIBUTE20 ,
2427 X_FUTURE_DATED_TRANS_FLAG => v_sca_upd_rec.future_dated_trans_flag
2428 );
2429 CLOSE c_sca_upd;
2430 ELSE
2431 CLOSE c_sca_upd;
2432 END IF;
2433 EXCEPTION
2434 WHEN e_resource_busy THEN
2435 IF c_sca_upd%ISOPEN THEN
2436 CLOSE c_sca_upd;
2437 END IF;
2438 p_message_name := 'IGS_PR_LOCK_DETECTED';
2439 RETURN FALSE;
2440 END;
2441
2442 ELSE
2443 OPEN c_person(p_person_id);
2444 FETCH c_person INTO lv_person_number;
2445 CLOSE c_person;
2446 FND_FILE.PUT_LINE(FND_FILE.LOG,'No Change in Progression Status for the Person = '||lv_person_number
2447 ||'Course Code :='||P_COURSE_CD);
2448 END IF;
2449 RETURN TRUE;
2450 EXCEPTION
2451 WHEN OTHERS THEN
2452 IF c_sca%ISOPEN THEN
2453 CLOSE c_sca;
2454 END IF;
2455 IF c_sca_upd%ISOPEN THEN
2456 CLOSE c_sca_upd;
2457 END IF;
2458 RAISE;
2459 END;
2460 EXCEPTION
2461 WHEN OTHERS THEN
2462 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2463 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.IGS_PR_UPD_SCA_STATUS');
2464 IGS_GE_MSG_STACK.ADD;
2465 App_Exception.Raise_Exception;
2466 END IGS_PR_upd_sca_status;
2467
2468
2469 FUNCTION igs_pr_upd_spo_pen(
2470 p_person_id IN NUMBER ,
2471 p_course_cd IN VARCHAR2 ,
2472 p_spo_sequence_number IN NUMBER ,
2473 p_authorising_person_id IN NUMBER ,
2474 p_application_type IN VARCHAR2 ,
2475 p_message_text OUT NOCOPY VARCHAR2 ,
2476 p_message_level OUT NOCOPY VARCHAR2 )
2477 RETURN BOOLEAN IS
2478 /*
2479 || Created By : prabhat.patel
2480 || Created On : 03-OCT-2002
2481 || Purpose : Validates the Foreign Keys for the table.
2482 || Known limitations, enhancements or remarks :
2483 || Change History :
2484 || Who When What
2485 || ssawhney 17-feb-2003 Bug : 2758856 : Added the parameter x_external_reference in the call to IGS_PE_PERS_ENCUMB_PKG.UPDATE_ROW
2486 || kdande 17-Dec-2002 Bug# 2543601. Changed the query for c_pe
2487 || cursor to use hz_parties instead of igs_pe_person
2488 || nalkumar 19-NOV-2002 Bug NO: 2658550. Modified this function as per the FA110 PR Enh.
2489 || pkpatel 7-OCT-2002 Bug No: 2600842
2490 || Added the parameter x_auth_resp_id in the call to igs_pe_pers_encumb_pkg
2491 || (reverse chronological order - newest change first)
2492 */
2493 gv_other_detail VARCHAR2(255);
2494 gcst_sysdate CONSTANT DATE := TRUNC(SYSDATE);
2495 gcst_sysdatetime CONSTANT DATE := SYSDATE;
2496 BEGIN -- IGS_PR_upd_spo_pen
2497 -- Maintain the person encumbrance structure related to a
2498 -- IGS_PR_STDNT_PR_OU that has been approved. The encumbrance details
2499 -- resulting from spo detail are only maintainable through this routine, via
2500 -- changes through the progression screens. The routine also handles the
2501 -- removal of encumbrances if the outcome has been Cancelled/Removed.
2502 -- A database relationship exists between the IGS_PE_PERS_ENCUMB and
2503 -- IGS_PR_STDNT_PR_OU from which it has resulted.
2504 -- Note: If a student has multiple IGS_PR_STDNT_PR_OUs then they
2505 -- will result in separate IGS_PE_PERS_ENCUMB entries related accordingly.
2506 -- Overlap between encumbrance effects is possible and is resolved where
2507 -- required.
2508 -- Note: the authorising person ID parameter is designed to be passed from
2509 -- calling routines and will be recorded against the person encumbrance
2510 -- records added (only when being added and not altered).
2511 DECLARE
2512 cst_cancelled CONSTANT VARCHAR2(10) := 'CANCELLED';
2513 cst_pending CONSTANT VARCHAR2(10) := 'PENDING';
2514 cst_removed CONSTANT VARCHAR2(10) := 'REMOVED';
2515 cst_waived CONSTANT VARCHAR2(10) := 'WAIVED';
2516 cst_rstr_ge_cp CONSTANT VARCHAR2(10) := 'RSTR_GE_CP';
2517 cst_rstr_le_cp CONSTANT VARCHAR2(10) := 'RSTR_LE_CP';
2518 cst_rstr_at_ty CONSTANT VARCHAR2(10) := 'RSTR_AT_TY';
2519 cst_sus_course CONSTANT VARCHAR2(10) := 'SUS_COURSE';
2520 cst_exc_course CONSTANT VARCHAR2(10) := 'EXC_COURSE';
2521 cst_exc_crs_gp CONSTANT VARCHAR2(10) := 'EXC_CRS_GP';
2522 cst_exc_crs_us CONSTANT VARCHAR2(10) := 'EXC_CRS_US';
2523 cst_exc_crs_u CONSTANT VARCHAR2(10) := 'EXC_CRS_U';
2524 cst_rqrd_crs_u CONSTANT VARCHAR2(10) := 'RQRD_CRS_U';
2525 --
2526 cst_exc_sp_awd CONSTANT VARCHAR2(10) := 'EX_SP_AWD';
2527 cst_exc_sp_disb CONSTANT VARCHAR2(15) := 'EX_SP_DISB';
2528 cst_exc_awd CONSTANT VARCHAR2(10) := 'EX_AWD';
2529 cst_exc_disb CONSTANT VARCHAR2(10) := 'EX_DISB';
2530 --
2531 cst_excluded CONSTANT VARCHAR2(10) := 'EXCLUDED';
2532 cst_expired CONSTANT VARCHAR2(10) := 'EXPIRED';
2533 cst_error CONSTANT VARCHAR2(10) := 'ERROR';
2534 cst_required CONSTANT VARCHAR2(10) := 'REQUIRED';
2535 e_record_locked EXCEPTION;
2536 v_decode_val1 NUMBER(6,3);
2537 v_decode_val2 VARCHAR2(2);
2538 lv_spo_sequence_number IGS_PE_PERS_ENCUMB.SPO_SEQUENCE_NUMBER%TYPE;
2539 PRAGMA EXCEPTION_INIT (e_record_locked, -54);
2540 v_authorising_person_id IGS_PE_PERSON.person_id%TYPE;
2541 v_message_text VARCHAR2(2000) DEFAULT NULL;
2542 v_message_level VARCHAR2(10) DEFAULT NULL;
2543 v_action_expiry_dt IGS_PE_PERS_ENCUMB.expiry_dt%TYPE;
2544 v_pen_expiry_dt IGS_PE_PERS_ENCUMB.expiry_dt%TYPE;
2545 v_pen_exists BOOLEAN;
2546 v_expiry_status VARCHAR2(10);
2547 v_expiry_dt DATE;
2548 v_course_cd_found BOOLEAN;
2549 v_course_grp_cd_found BOOLEAN;
2550 v_unit_set_found BOOLEAN;
2551 v_unit_cd_found BOOLEAN;
2552 v_pee_sequence_number IGS_PE_PERSENC_EFFCT.sequence_number%TYPE;
2553 v_dummy VARCHAR2(1);
2554 v_fund_cd_found BOOLEAN;
2555 CURSOR c_spo IS
2556 SELECT spo.decision_status,
2557 spo.encmb_course_group_cd,
2558 spo.restricted_enrolment_cp,
2559 spo.restricted_attendance_type,
2560 spo.expiry_dt,
2561 pot.encumbrance_type,
2562 att.closed_ind
2563 FROM IGS_PR_STDNT_PR_OU spo,
2564 igs_pr_ou_type pot,
2565 igs_en_atd_type att
2566 WHERE spo.person_id = p_person_id AND
2567 spo.course_cd = p_course_cd AND
2568 spo.sequence_number = p_spo_sequence_number AND
2569 pot.progression_outcome_type = spo.progression_outcome_type AND
2570 att.attendance_type (+)= spo.restricted_attendance_type;
2571 v_spo_rec c_spo%ROWTYPE;
2572 CURSOR c_pe IS
2573 SELECT p.party_id person_id
2574 FROM hz_parties p,
2575 fnd_user u
2576 WHERE u.user_id = FND_GLOBAL.USER_ID
2577 AND u.person_party_id = p.party_id
2578 AND SUBSTR (igs_en_gen_003.get_staff_ind (p.party_id), 1, 1) = 'Y';
2579 CURSOR c_pen IS
2580 SELECT pen.expiry_dt
2581 FROM IGS_PE_PERS_ENCUMB pen
2582 WHERE pen.person_id = p_person_id AND
2583 pen.spo_course_cd = p_course_cd AND
2584 pen.spo_sequence_number = p_spo_sequence_number;
2585 CURSOR c_etde (
2586 cp_encumbrance_type IGS_PR_OU_TYPE.encumbrance_type%TYPE) IS
2587 SELECT etde.s_encmb_effect_type
2588 FROM igs_fi_enc_dflt_eft etde
2589 WHERE etde.encumbrance_type = cp_encumbrance_type;
2590
2591 CURSOR c_pee IS
2592 SELECT pee.*,
2593 pee.ROWID
2594 FROM IGS_PE_PERS_ENCUMB pen,
2595 IGS_PE_PERSENC_EFFCT pee,
2596 igs_fi_enc_dflt_eft etde
2597 WHERE pen.person_id = p_person_id AND
2598 pen.spo_course_cd = p_course_cd AND
2599 pen.spo_sequence_number = p_spo_sequence_number AND
2600 pen.person_id = pee.person_id AND
2601 pen.encumbrance_type = pee.encumbrance_type AND
2602 pen.start_dt = pee.pen_start_dt AND
2603 pee.encumbrance_type = etde.encumbrance_type AND
2604 pee.s_encmb_effect_type = etde.s_encmb_effect_type AND
2605 (pee.expiry_dt IS NULL OR
2606 pee.expiry_dt > gcst_sysdate)
2607 FOR UPDATE NOWAIT;
2608
2609 CURSOR c_pee_seq_num IS
2610 SELECT IGS_PR_PEE_SEQUEN_S.NEXTVAL
2611 FROM DUAL;
2612
2613 CURSOR c_pce (
2614 cp_encumbrance_type IGS_PE_PERSENC_EFFCT.encumbrance_type%TYPE,
2615 cp_pen_start_dt IGS_PE_PERSENC_EFFCT.pen_start_dt%TYPE,
2616 cp_s_encmb_effect_type IGS_PE_PERSENC_EFFCT.s_encmb_effect_type%TYPE,
2617 cp_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE,
2618 cp_pee_sequence_number IGS_PE_PERSENC_EFFCT.sequence_number%TYPE) IS
2619 SELECT pce.*, pce.ROWID
2620 FROM igs_pe_course_excl pce
2621 WHERE pce.person_id = p_person_id AND
2622 pce.encumbrance_type = cp_encumbrance_type AND
2623 pce.pen_start_dt = cp_pen_start_dt AND
2624 pce.s_encmb_effect_type = cp_s_encmb_effect_type AND
2625 pce.pee_start_dt = cp_pee_start_dt AND
2626 pce.pee_sequence_number = cp_pee_sequence_number AND
2627 (pce.expiry_dt IS NULL OR
2628 pce.expiry_dt > gcst_sysdate)
2629 FOR UPDATE NOWAIT;
2630
2631 TYPE t_pce_table IS TABLE OF igs_pe_course_excl.course_cd%TYPE
2632
2633 INDEX BY BINARY_INTEGER;
2634
2635
2636 v_pce_table t_pce_table;
2637
2638 v_pce_index BINARY_INTEGER;
2639
2640 CURSOR c_pce_dup (
2641 cp_encumbrance_type IGS_PE_PERSENC_EFFCT.encumbrance_type%TYPE,
2642 cp_pen_start_dt IGS_PE_PERSENC_EFFCT.pen_start_dt%TYPE,
2643 cp_s_encmb_effect_type IGS_PE_PERSENC_EFFCT.s_encmb_effect_type%TYPE,
2644 cp_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE,
2645 cp_pee_sequence_number IGS_PE_PERSENC_EFFCT.sequence_number%TYPE,
2646 cp_course_cd igs_pe_course_excl.course_cd%TYPE,
2647 cp_pce_start_dt igs_pe_course_excl.pce_start_dt%TYPE) IS
2648 SELECT pce.*, pce.ROWID
2649 FROM igs_pe_course_excl pce
2650 WHERE pce.person_id = p_person_id AND
2651 pce.encumbrance_type = cp_encumbrance_type AND
2652 pce.pen_start_dt = cp_pen_start_dt AND
2653 pce.s_encmb_effect_type = cp_s_encmb_effect_type AND
2654 pce.pee_start_dt = cp_pee_start_dt AND
2655 pce.pee_sequence_number = cp_pee_sequence_number AND
2656 pce.course_cd = cp_course_cd AND
2657 pce.pce_start_dt = cp_pce_start_dt
2658 FOR UPDATE NOWAIT;
2659 v_pce_dup_rec c_pce_dup%ROWTYPE;
2660
2661 CURSOR c_spc IS
2662 SELECT spc.course_cd
2663 FROM igs_pr_stdnt_pr_ps spc
2664 WHERE spc.person_id = p_person_id AND
2665 spc.spo_course_cd = p_course_cd AND
2666 spc.spo_sequence_number = p_spo_sequence_number;
2667 TYPE t_spc_type IS TABLE OF igs_pr_stdnt_pr_ps.course_cd%TYPE
2668 INDEX BY BINARY_INTEGER;
2669
2670 v_spc_table t_spc_type;
2671 v_spc_index BINARY_INTEGER;
2672
2673 CURSOR c_pcge (
2674 cp_encumbrance_type IGS_PE_PERSENC_EFFCT.encumbrance_type%TYPE,
2675 cp_pen_start_dt IGS_PE_PERSENC_EFFCT.pen_start_dt%TYPE,
2676 cp_s_encmb_effect_type IGS_PE_PERSENC_EFFCT.s_encmb_effect_type%TYPE,
2677 cp_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE,
2678 cp_pee_sequence_number IGS_PE_PERSENC_EFFCT.sequence_number%TYPE) IS
2679 SELECT pcge.*, pcge.ROWID
2680 FROM igs_pe_crs_grp_excl pcge
2681 WHERE pcge.person_id = p_person_id AND
2682 pcge.encumbrance_type = cp_encumbrance_type AND
2683 pcge.pen_start_dt = cp_pen_start_dt AND
2684 pcge.s_encmb_effect_type = cp_s_encmb_effect_type AND
2685 pcge.pee_start_dt = cp_pee_start_dt AND
2686 pcge.pee_sequence_number = cp_pee_sequence_number AND
2687 (pcge.expiry_dt IS NULL OR
2688 pcge.expiry_dt > gcst_sysdate)
2689 FOR UPDATE NOWAIT;
2690 TYPE t_pcge_table IS TABLE OF igs_pe_crs_grp_excl.course_group_cd%TYPE
2691 INDEX BY BINARY_INTEGER;
2692
2693 v_pcge_table t_pcge_table;
2694 v_pcge_index BINARY_INTEGER;
2695
2696 CURSOR c_pcge_dup (
2697 cp_encumbrance_type IGS_PE_PERSENC_EFFCT.encumbrance_type%TYPE,
2698 cp_pen_start_dt IGS_PE_PERSENC_EFFCT.pen_start_dt%TYPE,
2699 cp_s_encmb_effect_type IGS_PE_PERSENC_EFFCT.s_encmb_effect_type%TYPE,
2700 cp_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE,
2701 cp_pee_sequence_number IGS_PE_PERSENC_EFFCT.sequence_number%TYPE,
2702 cp_course_group_cd igs_pe_crs_grp_excl.course_group_cd%TYPE,
2703 cp_pcge_start_dt igs_pe_crs_grp_excl.pcge_start_dt%TYPE) IS
2704 SELECT pcge.*, pcge.ROWID
2705 FROM igs_pe_crs_grp_excl pcge
2706 WHERE pcge.person_id = p_person_id AND
2707 pcge.encumbrance_type = cp_encumbrance_type AND
2708 pcge.pen_start_dt = cp_pen_start_dt AND
2709 pcge.s_encmb_effect_type = cp_s_encmb_effect_type AND
2710 pcge.pee_start_dt = cp_pee_start_dt AND
2711 pcge.pee_sequence_number = cp_pee_sequence_number AND
2712 pcge.course_group_cd = cp_course_group_cd AND
2713 pcge.pcge_start_dt = cp_pcge_start_dt
2714 FOR UPDATE NOWAIT;
2715
2716 v_pcge_dup_rec c_pcge_dup%ROWTYPE;
2717
2718 CURSOR c_puse (
2719 cp_encumbrance_type IGS_PE_PERSENC_EFFCT.encumbrance_type%TYPE,
2720 cp_pen_start_dt IGS_PE_PERSENC_EFFCT.pen_start_dt%TYPE,
2721 cp_s_encmb_effect_type IGS_PE_PERSENC_EFFCT.s_encmb_effect_type%TYPE,
2722 cp_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE,
2723 cp_pee_sequence_number IGS_PE_PERSENC_EFFCT.sequence_number%TYPE) IS
2724 SELECT puse.*, puse.ROWID
2725 FROM igs_pe_unt_set_excl puse
2726 WHERE puse.person_id = p_person_id AND
2727 puse.encumbrance_type = cp_encumbrance_type AND
2728 puse.pen_start_dt = cp_pen_start_dt AND
2729 puse.s_encmb_effect_type = cp_s_encmb_effect_type AND
2730 puse.pee_start_dt = cp_pee_start_dt AND
2731 puse.pee_sequence_number = cp_pee_sequence_number AND
2732 (puse.expiry_dt IS NULL OR
2733 puse.expiry_dt > gcst_sysdate)
2734 FOR UPDATE NOWAIT;
2735
2736 TYPE r_puse_record_type IS RECORD (
2737 unit_set_cd igs_pe_unt_set_excl.unit_set_cd%TYPE,
2738 us_version_number igs_pe_unt_set_excl.us_version_number%TYPE);
2739
2740 r_puse_record r_puse_record_type;
2741 TYPE t_puse_table IS TABLE OF r_puse_record%TYPE
2742 INDEX BY BINARY_INTEGER;
2743
2744 v_puse_table t_puse_table;
2745 v_puse_index BINARY_INTEGER;
2746
2747 CURSOR c_puse_dup (
2748 cp_encumbrance_type IGS_PE_PERSENC_EFFCT.encumbrance_type%TYPE,
2749 cp_pen_start_dt IGS_PE_PERSENC_EFFCT.pen_start_dt%TYPE,
2750 cp_s_encmb_effect_type IGS_PE_PERSENC_EFFCT.s_encmb_effect_type%TYPE,
2751 cp_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE,
2752 cp_pee_sequence_number IGS_PE_PERSENC_EFFCT.sequence_number%TYPE,
2753 cp_unit_set_cd igs_pe_unt_set_excl.unit_set_cd%TYPE,
2754 cp_us_version_number igs_pe_unt_set_excl.us_version_number%TYPE,
2755 cp_puse_start_dt igs_pe_unt_set_excl.puse_start_dt%TYPE) IS
2756 SELECT puse.*, puse.ROWID
2757 FROM igs_pe_unt_set_excl puse
2758 WHERE puse.person_id = p_person_id AND
2759 puse.encumbrance_type = cp_encumbrance_type AND
2760 puse.pen_start_dt = cp_pen_start_dt AND
2761 puse.s_encmb_effect_type = cp_s_encmb_effect_type AND
2762 puse.pee_start_dt = cp_pee_start_dt AND
2763 puse.pee_sequence_number = cp_pee_sequence_number AND
2764 puse.unit_set_cd = cp_unit_set_cd AND
2765 puse.us_version_number = cp_us_version_number AND
2766 puse.puse_start_dt = cp_puse_start_dt
2767 FOR UPDATE NOWAIT;
2768
2769 v_puse_dup_rec c_puse_dup%ROWTYPE;
2770
2771 CURSOR c_spus IS
2772 SELECT spus.unit_set_cd,
2773 spus.version_number
2774 FROM igs_pr_sdt_pr_unt_st spus
2775 WHERE spus.person_id = p_person_id AND
2776 spus.course_cd = p_course_cd AND
2777 spus.spo_sequence_number = p_spo_sequence_number;
2778
2779 TYPE r_spus_record_type IS RECORD (
2780 unit_set_cd igs_pr_sdt_pr_unt_st.unit_set_cd%TYPE,
2781 version_number igs_pr_sdt_pr_unt_st.version_number%TYPE);
2782 r_spus_record r_spus_record_type;
2783
2784 TYPE t_spus_table IS TABLE OF r_spus_record%TYPE
2785 INDEX BY BINARY_INTEGER;
2786
2787 v_spus_table t_spus_table;
2788 v_spus_index BINARY_INTEGER;
2789
2790 CURSOR c_pue (
2791 cp_encumbrance_type IGS_PE_PERSENC_EFFCT.encumbrance_type%TYPE,
2792 cp_pen_start_dt IGS_PE_PERSENC_EFFCT.pen_start_dt%TYPE,
2793 cp_s_encmb_effect_type IGS_PE_PERSENC_EFFCT.s_encmb_effect_type%TYPE,
2794 cp_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE,
2795 cp_pee_sequence_number IGS_PE_PERSENC_EFFCT.sequence_number%TYPE) IS
2796 SELECT pue.*, pue.ROWID
2797 FROM igs_pe_pers_unt_excl pue
2798 WHERE pue.person_id = p_person_id AND
2799 pue.encumbrance_type = cp_encumbrance_type AND
2800 pue.pen_start_dt = cp_pen_start_dt AND
2801 pue.s_encmb_effect_type = cp_s_encmb_effect_type AND
2802 pue.pee_start_dt = cp_pee_start_dt AND
2803 pue.pee_sequence_number = cp_pee_sequence_number AND
2804 (pue.expiry_dt IS NULL OR
2805 pue.expiry_dt > gcst_sysdate)
2806 FOR UPDATE NOWAIT;
2807
2808
2809 TYPE t_pue_table IS TABLE OF igs_pe_pers_unt_excl.unit_cd%TYPE
2810 INDEX BY BINARY_INTEGER;
2811
2812 v_pue_table t_pue_table;
2813 v_pue_index BINARY_INTEGER;
2814
2815 CURSOR c_pue_dup (
2816 cp_encumbrance_type IGS_PE_PERSENC_EFFCT.encumbrance_type%TYPE,
2817 cp_pen_start_dt IGS_PE_PERSENC_EFFCT.pen_start_dt%TYPE,
2818 cp_s_encmb_effect_type IGS_PE_PERSENC_EFFCT.s_encmb_effect_type%TYPE,
2819 cp_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE,
2820 cp_pee_sequence_number IGS_PE_PERSENC_EFFCT.sequence_number%TYPE,
2821 cp_unit_cd igs_pe_pers_unt_excl.unit_cd%TYPE,
2822 cp_pue_start_dt igs_pe_pers_unt_excl.pue_start_dt%TYPE) IS
2823
2824 SELECT pue.*, pue.ROWID
2825 FROM igs_pe_pers_unt_excl pue
2826 WHERE pue.person_id = p_person_id AND
2827 pue.encumbrance_type = cp_encumbrance_type AND
2828 pue.pen_start_dt = cp_pen_start_dt AND
2829 pue.s_encmb_effect_type = cp_s_encmb_effect_type AND
2830 pue.pee_start_dt = cp_pee_start_dt AND
2831 pue.pee_sequence_number = cp_pee_sequence_number AND
2832 pue.unit_cd = cp_unit_cd AND
2833 pue.pue_start_dt = cp_pue_start_dt
2834 FOR UPDATE NOWAIT;
2835
2836 v_pue_dup_rec c_pue_dup%ROWTYPE;
2837
2838 CURSOR c_pur (
2839 cp_encumbrance_type IGS_PE_PERSENC_EFFCT.encumbrance_type%TYPE,
2840 cp_pen_start_dt IGS_PE_PERSENC_EFFCT.pen_start_dt%TYPE,
2841 cp_s_encmb_effect_type IGS_PE_PERSENC_EFFCT.s_encmb_effect_type%TYPE,
2842 cp_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE,
2843 cp_pee_sequence_number IGS_PE_PERSENC_EFFCT.sequence_number%TYPE) IS
2844 SELECT pur.*, pur.ROWID
2845 FROM igs_pe_unt_requirmnt pur
2846 WHERE pur.person_id = p_person_id AND
2847 pur.encumbrance_type = cp_encumbrance_type AND
2848 pur.pen_start_dt = cp_pen_start_dt AND
2849 pur.s_encmb_effect_type = cp_s_encmb_effect_type AND
2850 pur.pee_start_dt = cp_pee_start_dt AND
2851 pur.pee_sequence_number = cp_pee_sequence_number AND
2852 (pur.expiry_dt IS NULL OR
2853 pur.expiry_dt > gcst_sysdate)
2854 FOR UPDATE NOWAIT;
2855
2856 TYPE t_pur_table IS TABLE OF igs_pe_unt_requirmnt.unit_cd%TYPE
2857 INDEX BY BINARY_INTEGER;
2858
2859 v_pur_table t_pur_table;
2860 v_pur_index BINARY_INTEGER;
2861
2862 CURSOR c_pur_dup (
2863 cp_encumbrance_type IGS_PE_PERSENC_EFFCT.encumbrance_type%TYPE,
2864 cp_pen_start_dt IGS_PE_PERSENC_EFFCT.pen_start_dt%TYPE,
2865 cp_s_encmb_effect_type IGS_PE_PERSENC_EFFCT.s_encmb_effect_type%TYPE,
2866 cp_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE,
2867 cp_pee_sequence_number IGS_PE_PERSENC_EFFCT.sequence_number%TYPE,
2868 cp_unit_cd igs_pe_unt_requirmnt.unit_cd%TYPE,
2869 cp_pur_start_dt igs_pe_unt_requirmnt.pur_start_dt%TYPE) IS
2870 SELECT pur.*, pur.ROWID
2871 FROM igs_pe_unt_requirmnt pur
2872 WHERE pur.person_id = p_person_id AND
2873 pur.encumbrance_type = cp_encumbrance_type AND
2874 pur.pen_start_dt = cp_pen_start_dt AND
2875 pur.s_encmb_effect_type = cp_s_encmb_effect_type AND
2876 pur.pee_start_dt = cp_pee_start_dt AND
2877 pur.pee_sequence_number = cp_pee_sequence_number AND
2878 pur.unit_cd = cp_unit_cd AND
2879 pur.pur_start_dt = cp_pur_start_dt
2880 FOR UPDATE NOWAIT;
2881
2882 v_pur_dup_rec c_pur_dup%ROWTYPE;
2883
2884 CURSOR c_spu (
2885 cp_s_unit_type igs_pr_stdnt_pr_unit.s_unit_type%TYPE) IS
2886 SELECT spu.unit_cd
2887 FROM igs_pr_stdnt_pr_unit spu
2888 WHERE spu.person_id = p_person_id AND
2889 spu.course_cd = p_course_cd AND
2890 spu.spo_sequence_number = p_spo_sequence_number AND
2891 spu.s_unit_type = cp_s_unit_type;
2892
2893 TYPE t_spu_type IS TABLE OF igs_pr_stdnt_pr_unit.unit_cd%TYPE
2894 INDEX BY BINARY_INTEGER;
2895 v_spu_table t_spu_type;
2896 v_spu_index BINARY_INTEGER;
2897
2898 CURSOR c_seet (
2899 cp_s_encmb_effect_type igs_en_encmb_efcttyp.s_encmb_effect_type%TYPE) IS
2900 SELECT apply_to_course_ind
2901 FROM igs_en_encmb_efcttyp seet
2902 WHERE seet.s_encmb_effect_type = cp_s_encmb_effect_type;
2903
2904 v_apply_to_course_ind igs_en_encmb_efcttyp.apply_to_course_ind%TYPE;
2905 v_apply_course_cd IGS_PE_PERSENC_EFFCT.course_cd%TYPE;
2906
2907 TYPE r_etde_record_type IS RECORD (
2908 s_encmb_effect_type igs_fi_enc_dflt_eft.s_encmb_effect_type%TYPE);
2909 r_etde_record r_etde_record_type;
2910
2911 TYPE t_etde_type IS TABLE OF r_etde_record%TYPE
2912 INDEX BY BINARY_INTEGER;
2913
2914 v_etde_table t_etde_type;
2915 v_etde_index BINARY_INTEGER;
2916 v_index1 BINARY_INTEGER;
2917 v_index2 BINARY_INTEGER;
2918
2919 --
2920 -- Start of new code added as per the FA110 PR Enh. Bug# 2658550.
2921 --
2922 CURSOR c_pfe (
2923 cp_encumbrance_type igs_pe_persenc_effct.encumbrance_type%TYPE,
2924 cp_pen_start_dt igs_pe_persenc_effct.pen_start_dt%TYPE,
2925 cp_s_encmb_effect_type igs_pe_persenc_effct.s_encmb_effect_type%TYPE,
2926 cp_pee_start_dt igs_pe_persenc_effct.pee_start_dt%TYPE,
2927 cp_pee_sequence_number igs_pe_persenc_effct.sequence_number%TYPE) IS
2928 SELECT pfe.*, pfe.rowid
2929 FROM igs_pe_fund_excl pfe
2930 WHERE pfe.person_id = p_person_id AND
2931 pfe.encumbrance_type = cp_encumbrance_type AND
2932 pfe.pen_start_dt = cp_pen_start_dt AND
2933 pfe.s_encmb_effect_type = cp_s_encmb_effect_type AND
2934 pfe.pee_start_dt = cp_pee_start_dt AND
2935 pfe.pee_sequence_number = cp_pee_sequence_number AND
2936 (pfe.expiry_dt IS NULL OR
2937 pfe.expiry_dt > gcst_sysdate)
2938 FOR UPDATE NOWAIT;
2939
2940 TYPE t_pfe_table IS TABLE OF igs_pe_fund_excl.fund_code%TYPE
2941 INDEX BY BINARY_INTEGER;
2942 v_pfe_table t_pfe_table;
2943 v_pfe_index BINARY_INTEGER;
2944
2945 CURSOR c_pfe_dup (
2946 cp_encumbrance_type igs_pe_persenc_effct.encumbrance_type%TYPE,
2947 cp_pen_start_dt igs_pe_persenc_effct.pen_start_dt%TYPE,
2948 cp_s_encmb_effect_type igs_pe_persenc_effct.s_encmb_effect_type%TYPE,
2949 cp_pee_start_dt igs_pe_persenc_effct.pee_start_dt%TYPE,
2950 cp_pee_sequence_number igs_pe_persenc_effct.sequence_number%TYPE,
2951 cp_fund_code igs_pr_ou_fnd.fund_code%TYPE,
2952 cp_pfe_start_dt igs_pe_fund_excl.pfe_start_dt%TYPE ) IS
2953 SELECT pfe.*, pfe.ROWID
2954 FROM igs_pe_fund_excl pfe
2955 WHERE pfe.person_id = p_person_id AND
2956 pfe.encumbrance_type = cp_encumbrance_type AND
2957 pfe.pen_start_dt = cp_pen_start_dt AND
2958 pfe.s_encmb_effect_type = cp_s_encmb_effect_type AND
2959 pfe.pee_start_dt = cp_pee_start_dt AND
2960 pfe.pee_sequence_number = cp_pee_sequence_number AND
2961 pfe.fund_code = cp_fund_code AND
2962 pfe.pfe_start_dt = cp_pfe_start_dt
2963 FOR UPDATE NOWAIT;
2964 v_pfe_dup_rec c_pfe_dup%ROWTYPE;
2965
2966 CURSOR c_spf IS
2967 SELECT spf.fund_code
2968 FROM igs_pr_stdnt_pr_fnd spf
2969 WHERE spf.person_id = p_person_id AND
2970 spf.course_cd = p_course_cd AND
2971 spf.spo_sequence_number = p_spo_sequence_number;
2972
2973 TYPE t_spf_type IS TABLE OF igs_pr_stdnt_pr_fnd.fund_code%TYPE
2974 INDEX BY BINARY_INTEGER;
2975 v_spf_table t_spf_type;
2976 v_spf_index BINARY_INTEGER;
2977 --
2978 -- End of new code added as per the FA110 PR Enh. Bug# 2658550.
2979 --
2980 FUNCTION prgpl_upd_expiry_dt1 (
2981 p_expiry_dt IGS_PE_PERS_ENCUMB.expiry_dt%TYPE,
2982 p_local_message_level OUT NOCOPY VARCHAR2,
2983 p_local_message_text OUT NOCOPY VARCHAR2)
2984 RETURN BOOLEAN
2985 IS
2986 /*
2987 || Created By : prabhat.patel
2988 || Created On : 03-OCT-2002
2989 || Purpose : Validates the Foreign Keys for the table.
2990 || Known limitations, enhancements or remarks :
2991 || Change History :
2992 || Who When What
2993 || ssawhney 17-feb-2003 Bug : 2758856 : Added the parameter x_external_reference in the call to IGS_PE_PERS_ENCUMB_PKG.UPDATE_ROW
2994 || pkpatel 7-OCT-2002 Bug No: 2600842
2995 || Added the parameter x_auth_resp_id in the call to igs_pe_pers_encumb_pkg
2996 || (reverse chronological order - newest change first)
2997 */
2998 gvl_other_detail VARCHAR2(255);
2999
3000 BEGIN -- prgpl_upd_expiry_dt1
3001
3002 DECLARE
3003
3004 CURSOR c_pen (
3005 cp_expiry_dt IGS_PE_PERS_ENCUMB.expiry_dt%TYPE) IS
3006 SELECT pen.*,
3007 pen.ROWID
3008 FROM IGS_PE_PERS_ENCUMB pen
3009 WHERE pen.person_id = p_person_id AND
3010 pen.spo_course_cd = p_course_cd AND
3011 pen.spo_sequence_number = p_spo_sequence_number AND
3012 ((cp_expiry_dt IS NULL OR
3013 cp_expiry_dt > gcst_sysdate) OR
3014 (pen.expiry_dt IS NULL OR
3015 pen.expiry_dt > gcst_sysdate))
3016 FOR UPDATE NOWAIT;
3017
3018 CURSOR c_pee (
3019 cp_encumbrance_type IGS_PE_PERS_ENCUMB.encumbrance_type%TYPE,
3020 cp_pen_start_dt IGS_PE_PERS_ENCUMB.start_dt%TYPE,
3021 cp_expiry_dt IGS_PE_PERSENC_EFFCT.expiry_dt%TYPE) IS
3022 SELECT pee.*,
3023 pee.ROWID
3024 FROM IGS_PE_PERSENC_EFFCT pee
3025 WHERE pee.person_id = p_person_id AND
3026 pee.encumbrance_type = cp_encumbrance_type AND
3027 pee.pen_start_dt = cp_pen_start_dt AND
3028 ((cp_expiry_dt IS NULL OR
3029 cp_expiry_dt > gcst_sysdate) OR
3030 (pee.expiry_dt IS NULL OR
3031 pee.expiry_dt > gcst_sysdate))
3032 FOR UPDATE NOWAIT;
3033
3034 CURSOR c_pce (
3035 cp_encumbrance_type IGS_PE_PERS_ENCUMB.encumbrance_type%TYPE,
3036 cp_pen_start_dt IGS_PE_PERS_ENCUMB.start_dt%TYPE,
3037 cp_s_encmb_effect_type IGS_PE_PERSENC_EFFCT.s_encmb_effect_type%TYPE,
3038 cp_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE,
3039 cp_pee_sequence_number IGS_PE_PERSENC_EFFCT.sequence_number%TYPE) IS
3040 SELECT pce.*,pce.ROWID
3041 FROM igs_pe_course_excl pce
3042 WHERE pce.person_id = p_person_id AND
3043 pce.encumbrance_type = cp_encumbrance_type AND
3044 pce.pen_start_dt = cp_pen_start_dt AND
3045 pce.s_encmb_effect_type = cp_s_encmb_effect_type AND
3046 pce.pee_start_dt = cp_pee_start_dt AND
3047 pce.pee_sequence_number = cp_pee_sequence_number AND
3048 (pce.expiry_dt IS NULL OR
3049 pce.expiry_dt > gcst_sysdate)
3050 FOR UPDATE NOWAIT;
3051
3052 CURSOR c_pcge (
3053 cp_encumbrance_type IGS_PE_PERS_ENCUMB.encumbrance_type%TYPE,
3054 cp_pen_start_dt IGS_PE_PERS_ENCUMB.start_dt%TYPE,
3055 cp_s_encmb_effect_type IGS_PE_PERSENC_EFFCT.s_encmb_effect_type%TYPE,
3056 cp_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE,
3057 cp_pee_sequence_number IGS_PE_PERSENC_EFFCT.sequence_number%TYPE) IS
3058 SELECT pcge.*, pcge.ROWID
3059 FROM igs_pe_crs_grp_excl pcge
3060 WHERE pcge.person_id = p_person_id AND
3061 pcge.encumbrance_type = cp_encumbrance_type AND
3062 pcge.pen_start_dt = cp_pen_start_dt AND
3063 pcge.s_encmb_effect_type = cp_s_encmb_effect_type AND
3064 pcge.pee_start_dt = cp_pee_start_dt AND
3065 pcge.pee_sequence_number = cp_pee_sequence_number AND
3066 (pcge.expiry_dt IS NULL OR
3067 pcge.expiry_dt > gcst_sysdate)
3068 FOR UPDATE NOWAIT;
3069
3070 CURSOR c_puse (
3071 cp_encumbrance_type IGS_PE_PERS_ENCUMB.encumbrance_type%TYPE,
3072 cp_pen_start_dt IGS_PE_PERS_ENCUMB.start_dt%TYPE,
3073 cp_s_encmb_effect_type IGS_PE_PERSENC_EFFCT.s_encmb_effect_type%TYPE,
3074 cp_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE,
3075 cp_pee_sequence_number IGS_PE_PERSENC_EFFCT.sequence_number%TYPE) IS
3076 SELECT puse.*,puse.ROWID
3077 FROM igs_pe_unt_set_excl puse
3078 WHERE puse.person_id = p_person_id AND
3079 puse.encumbrance_type = cp_encumbrance_type AND
3080 puse.pen_start_dt = cp_pen_start_dt AND
3081 puse.s_encmb_effect_type = cp_s_encmb_effect_type AND
3082 puse.pee_start_dt = cp_pee_start_dt AND
3083 puse.pee_sequence_number = cp_pee_sequence_number AND
3084 (puse.expiry_dt IS NULL OR
3085 puse.expiry_dt > gcst_sysdate)
3086 FOR UPDATE NOWAIT;
3087
3088 CURSOR c_pue (
3089 cp_encumbrance_type IGS_PE_PERS_ENCUMB.encumbrance_type%TYPE,
3090 cp_pen_start_dt IGS_PE_PERS_ENCUMB.start_dt%TYPE,
3091 cp_s_encmb_effect_type IGS_PE_PERSENC_EFFCT.s_encmb_effect_type%TYPE,
3092 cp_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE,
3093 cp_pee_sequence_number IGS_PE_PERSENC_EFFCT.sequence_number%TYPE) IS
3094 SELECT pue.*, pue.ROWID
3095 FROM igs_pe_pers_unt_excl pue
3096 WHERE pue.person_id = p_person_id AND
3097 pue.encumbrance_type = cp_encumbrance_type AND
3098 pue.pen_start_dt = cp_pen_start_dt AND
3099 pue.s_encmb_effect_type = cp_s_encmb_effect_type AND
3100 pue.pee_start_dt = cp_pee_start_dt AND
3101 pue.pee_sequence_number = cp_pee_sequence_number AND
3102 (pue.expiry_dt IS NULL OR
3103 pue.expiry_dt > gcst_sysdate)
3104 FOR UPDATE NOWAIT;
3105
3106 CURSOR c_pur (
3107 cp_encumbrance_type IGS_PE_PERS_ENCUMB.encumbrance_type%TYPE,
3108 cp_pen_start_dt IGS_PE_PERS_ENCUMB.start_dt%TYPE,
3109 cp_s_encmb_effect_type IGS_PE_PERSENC_EFFCT.s_encmb_effect_type%TYPE,
3110 cp_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE,
3111 cp_pee_sequence_number IGS_PE_PERSENC_EFFCT.sequence_number%TYPE) IS
3112 SELECT pur.*, pur.ROWID
3113 FROM igs_pe_unt_requirmnt pur
3114 WHERE pur.person_id = p_person_id AND
3115 pur.encumbrance_type = cp_encumbrance_type AND
3116 pur.pen_start_dt = cp_pen_start_dt AND
3117 pur.s_encmb_effect_type = cp_s_encmb_effect_type AND
3118 pur.pee_start_dt = cp_pee_start_dt AND
3119 pur.pee_sequence_number = cp_pee_sequence_number AND
3120 (pur.expiry_dt IS NULL OR
3121 pur.expiry_dt > gcst_sysdate)
3122 FOR UPDATE NOWAIT;
3123
3124 --
3125 -- Start of new code added as per the FA110 PR Enh. Bug# 2658550.
3126 --
3127 CURSOR c_pfe (
3128 cp_encumbrance_type IGS_PE_PERS_ENCUMB.encumbrance_type%TYPE,
3129 cp_pen_start_dt IGS_PE_PERS_ENCUMB.start_dt%TYPE,
3130 cp_s_encmb_effect_type IGS_PE_PERSENC_EFFCT.s_encmb_effect_type%TYPE,
3131 cp_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE,
3132 cp_pee_sequence_number IGS_PE_PERSENC_EFFCT.sequence_number%TYPE) IS
3133 SELECT pfe.*, pfe.ROWID
3134 FROM igs_pe_fund_excl pfe
3135 WHERE pfe.person_id = p_person_id AND
3136 pfe.encumbrance_type = cp_encumbrance_type AND
3137 pfe.pen_start_dt = cp_pen_start_dt AND
3138 pfe.s_encmb_effect_type = cp_s_encmb_effect_type AND
3139 pfe.pee_start_dt = cp_pee_start_dt AND
3140 pfe.pee_sequence_number = cp_pee_sequence_number AND
3141 (pfe.expiry_dt IS NULL OR
3142 pfe.expiry_dt > gcst_sysdate)
3143 FOR UPDATE NOWAIT;
3144 --
3145 -- End of new code added as per the FA110 PR Enh. Bug# 2658550.
3146 --
3147 BEGIN
3148 FOR v_pen_rec IN c_pen (
3149 p_expiry_dt) LOOP
3150 FOR v_pee_rec IN c_pee (
3151 v_pen_rec.encumbrance_type,
3152 v_pen_rec.start_dt,
3153 p_expiry_dt) LOOP
3154 FOR v_pce_rec IN c_pce (
3155 v_pen_rec.encumbrance_type,
3156 v_pen_rec.start_dt,
3157 v_pee_rec.s_encmb_effect_type,
3158 v_pee_rec.pee_start_dt,
3159 v_pee_rec.sequence_number) LOOP
3160 igs_pe_course_excl_pkg.UPDATE_ROW(
3161 X_ROWID => v_pce_rec.ROWID,
3162 X_PERSON_ID => v_pce_rec.PERSON_ID,
3163 X_ENCUMBRANCE_TYPE => v_pce_rec.ENCUMBRANCE_TYPE,
3164 X_PEN_START_DT => v_pce_rec.PEN_START_DT,
3165 X_S_ENCMB_EFFECT_TYPE => v_pce_rec.S_ENCMB_EFFECT_TYPE,
3166 X_PEE_START_DT => v_pce_rec.PEE_START_DT,
3167 X_PEE_SEQUENCE_NUMBER => v_pce_rec.PEE_SEQUENCE_NUMBER,
3168 X_COURSE_CD => v_pce_rec.COURSE_CD,
3169 X_PCE_START_DT => v_pce_rec.PCE_START_DT,
3170 X_EXPIRY_DT => p_expiry_dt,
3171 X_MODE => 'R'
3172 );
3173
3174 END LOOP; -- c_pce
3175 FOR v_pcge_rec IN c_pcge (
3176 v_pen_rec.encumbrance_type,
3177 v_pen_rec.start_dt,
3178 v_pee_rec.s_encmb_effect_type,
3179 v_pee_rec.pee_start_dt,
3180 v_pee_rec.sequence_number) LOOP
3181 igs_pe_crs_grp_excl_pkg.UPDATE_ROW(
3182 X_ROWID => v_pcge_rec.ROWID,
3183 X_PERSON_ID => v_pcge_rec.PERSON_ID,
3184 X_ENCUMBRANCE_TYPE => v_pcge_rec.ENCUMBRANCE_TYPE,
3185 X_PEN_START_DT => v_pcge_rec.PEN_START_DT,
3186 X_S_ENCMB_EFFECT_TYPE => v_pcge_rec.S_ENCMB_EFFECT_TYPE,
3187 X_PEE_START_DT => v_pcge_rec.PEE_START_DT,
3188 X_PEE_SEQUENCE_NUMBER => v_pcge_rec.PEE_SEQUENCE_NUMBER,
3189 X_COURSE_GROUP_CD => v_pcge_rec.COURSE_GROUP_CD,
3190 X_PCGE_START_DT => v_pcge_rec.PCGE_START_DT,
3191 X_EXPIRY_DT => p_expiry_dt,
3192 X_MODE => 'R'
3193 );
3194 END LOOP; -- c_pcge
3195
3196 FOR v_puse_rec IN c_puse (
3197 v_pen_rec.encumbrance_type,
3198 v_pen_rec.start_dt,
3199 v_pee_rec.s_encmb_effect_type,
3200 v_pee_rec.pee_start_dt,
3201 v_pee_rec.sequence_number) LOOP
3202 igs_pe_unt_set_excl_pkg.UPDATE_ROW(
3203 X_ROWID => v_puse_rec.ROWID,
3204 X_PERSON_ID => v_puse_rec.PERSON_ID,
3205 X_ENCUMBRANCE_TYPE => v_puse_rec.ENCUMBRANCE_TYPE,
3206 X_PEN_START_DT => v_puse_rec.PEN_START_DT,
3207 X_S_ENCMB_EFFECT_TYPE => v_puse_rec.S_ENCMB_EFFECT_TYPE,
3208 X_PEE_START_DT => v_puse_rec.PEE_START_DT,
3209 X_PEE_SEQUENCE_NUMBER => v_puse_rec.PEE_SEQUENCE_NUMBER,
3210 X_UNIT_SET_CD => v_puse_rec.UNIT_SET_CD,
3211 X_US_VERSION_NUMBER => v_puse_rec.US_VERSION_NUMBER,
3212 X_PUSE_START_DT => v_puse_rec.PUSE_START_DT,
3213 X_EXPIRY_DT => p_expiry_dt,
3214 X_MODE => 'R'
3215 );
3216
3217 END LOOP; -- c_puse
3218
3219 FOR v_pue_rec IN c_pue (
3220 v_pen_rec.encumbrance_type,
3221 v_pen_rec.start_dt,
3222 v_pee_rec.s_encmb_effect_type,
3223 v_pee_rec.pee_start_dt,
3224 v_pee_rec.sequence_number) LOOP
3225 igs_pe_pers_unt_excl_pkg.UPDATE_ROW(
3226 X_ROWID => v_pue_rec.ROWID,
3227 X_PERSON_ID => v_pue_rec.PERSON_ID,
3228 X_ENCUMBRANCE_TYPE => v_pue_rec.ENCUMBRANCE_TYPE,
3229 X_PEN_START_DT => v_pue_rec.PEN_START_DT,
3230 X_S_ENCMB_EFFECT_TYPE => v_pue_rec.S_ENCMB_EFFECT_TYPE,
3231 X_PEE_START_DT => v_pue_rec.PEE_START_DT,
3232 X_PEE_SEQUENCE_NUMBER => v_pue_rec.PEE_SEQUENCE_NUMBER,
3233 X_UNIT_CD => v_pue_rec.UNIT_CD,
3234 X_PUE_START_DT => v_pue_rec.PUE_START_DT,
3235 X_EXPIRY_DT => P_EXPIRY_DT,
3236 X_MODE => 'R'
3237 );
3238 END LOOP; -- c_pue
3239
3240 FOR v_pur_rec IN c_pur (
3241 v_pen_rec.encumbrance_type,
3242 v_pen_rec.start_dt,
3243 v_pee_rec.s_encmb_effect_type,
3244 v_pee_rec.pee_start_dt,
3245 v_pee_rec.sequence_number) LOOP
3246 igs_pe_unt_requirmnt_pkg.update_row(
3247 X_ROWID => v_pur_rec.ROWID,
3248 X_PERSON_ID => v_pur_rec.PERSON_ID,
3249 X_ENCUMBRANCE_TYPE => v_pur_rec.ENCUMBRANCE_TYPE,
3250 X_PEN_START_DT => v_pur_rec.PEN_START_DT,
3251 X_S_ENCMB_EFFECT_TYPE => v_pur_rec.S_ENCMB_EFFECT_TYPE,
3252 X_PEE_START_DT => v_pur_rec.PEE_START_DT,
3253 X_PEE_SEQUENCE_NUMBER => v_pur_rec.PEE_SEQUENCE_NUMBER,
3254 X_UNIT_CD => v_pur_rec.UNIT_CD,
3255 X_PUR_START_DT => v_pur_rec.PUR_START_DT,
3256 X_EXPIRY_DT => P_EXPIRY_DT,
3257 X_MODE => 'R'
3258 );
3259 END LOOP; -- c_pur
3260
3261 --
3262 -- Start of new code added as per the FA110 PR Enh. Bug# 2658550.
3263 --
3264 FOR v_pur_rec IN c_pfe (v_pen_rec.encumbrance_type,
3265 v_pen_rec.start_dt,
3266 v_pee_rec.s_encmb_effect_type,
3267 v_pee_rec.pee_start_dt,
3268 v_pee_rec.sequence_number) LOOP
3269 igs_pe_fund_excl_pkg.update_row(
3270 X_ROWID => v_pur_rec.rowid ,
3271 X_FUND_EXCL_ID => v_pur_rec.fund_excl_id ,
3272 X_PERSON_ID => v_pur_rec.person_id ,
3273 X_ENCUMBRANCE_TYPE => v_pur_rec.encumbrance_type ,
3274 X_PEN_START_DT => v_pur_rec.pen_start_dt ,
3275 X_S_ENCMB_EFFECT_TYPE => v_pur_rec.s_encmb_effect_type,
3276 X_PEE_START_DT => v_pur_rec.pee_start_dt ,
3277 X_PEE_SEQUENCE_NUMBER => v_pur_rec.pee_sequence_number,
3278 X_FUND_CODE => v_pur_rec.fund_code ,
3279 X_PFE_START_DT => v_pur_rec.pfe_start_dt ,
3280 X_EXPIRY_DT => p_expiry_dt ,
3281 X_MODE => 'R');
3282 END LOOP; -- c_pfe
3283 --
3284 -- End of new code added as per the FA110 PR Enh. Bug# 2658550.
3285 --
3286
3287
3288 -- If the encumbrance effect is being re-opened then check for clashes.
3289 IF v_pee_rec.expiry_dt IS NOT NULL AND
3290 v_pee_rec.expiry_dt <= TRUNC(SYSDATE) AND
3291 (p_expiry_dt IS NULL OR p_expiry_dt > TRUNC(SYSDATE)) THEN
3292 IF NOT IGS_PR_GEN_006.IGS_PR_upd_pen_clash (
3293 p_person_id,
3294 p_course_cd,
3295 p_spo_sequence_number,
3296 p_application_type,
3297 v_message_text,
3298 v_message_level) THEN
3299 p_local_message_text := v_message_text;
3300 p_local_message_level := v_message_level;
3301 RETURN FALSE;
3302 ELSIF v_message_level IS NOT NULL THEN
3303 p_local_message_text := v_message_text;
3304 p_local_message_level := v_message_level;
3305 END IF;
3306 END IF;
3307 igs_pe_persenc_effct_pkg.update_row(
3308 X_ROWID => v_pee_rec.ROWID,
3309 X_PERSON_ID => v_pee_rec.PERSON_ID,
3310 X_ENCUMBRANCE_TYPE => v_pee_rec.ENCUMBRANCE_TYPE,
3311 X_PEN_START_DT => v_pee_rec.PEN_START_DT,
3312 X_S_ENCMB_EFFECT_TYPE => v_pee_rec.S_ENCMB_EFFECT_TYPE,
3313 X_PEE_START_DT => v_pee_rec.PEE_START_DT,
3314 X_SEQUENCE_NUMBER => v_pee_rec.SEQUENCE_NUMBER,
3315 X_EXPIRY_DT => P_EXPIRY_DT,
3316 X_COURSE_CD => v_pee_rec.COURSE_CD,
3317 X_RESTRICTED_ENROLMENT_CP => v_pee_rec.RESTRICTED_ENROLMENT_CP,
3318 X_RESTRICTED_ATTENDANCE_TYPE => v_pee_rec.RESTRICTED_ATTENDANCE_TYPE,
3319 X_MODE => 'R'
3320 );
3321 END LOOP; -- c_pee
3322 IGS_PE_PERS_ENCUMB_PKG.UPDATE_ROW(
3323 X_ROWID => v_pen_rec.ROWID,
3324 X_PERSON_ID => v_pen_rec.PERSON_ID,
3325 X_ENCUMBRANCE_TYPE => v_pen_rec.ENCUMBRANCE_TYPE,
3326 X_START_DT => v_pen_rec.START_DT,
3327 X_EXPIRY_DT => p_expiry_dt,
3328 X_AUTHORISING_PERSON_ID => v_pen_rec.AUTHORISING_PERSON_ID,
3329 X_COMMENTS => v_pen_rec.COMMENTS,
3330 X_SPO_COURSE_CD => v_pen_rec.SPO_COURSE_CD,
3331 X_SPO_SEQUENCE_NUMBER => v_pen_rec.SPO_SEQUENCE_NUMBER,
3332 X_CAL_TYPE => v_pen_rec.CAL_TYPE,
3333 X_SEQUENCE_NUMBER => v_pen_rec.SEQUENCE_NUMBER,
3334 x_auth_resp_id => v_pen_rec.auth_resp_id,
3335 x_external_reference => v_pen_rec.external_reference,
3336 X_MODE => 'R'
3337 );
3338
3339 END LOOP; -- c_pen
3340 RETURN TRUE;
3341 EXCEPTION
3342 WHEN e_record_locked THEN
3343 IF c_pce%ISOPEN THEN
3344 CLOSE c_pce;
3345 END IF;
3346 IF c_pcge%ISOPEN THEN
3347 CLOSE c_pcge;
3348 END IF;
3349 IF c_puse%ISOPEN THEN
3350 CLOSE c_puse;
3351 END IF;
3352 IF c_pue%ISOPEN THEN
3353 CLOSE c_pue;
3354 END IF;
3355 IF c_pfe%ISOPEN THEN
3356 CLOSE c_pfe;
3357 END IF;
3358 IF c_pur%ISOPEN THEN
3359 CLOSE c_pur;
3360 END IF;
3361 IF c_pee%ISOPEN THEN
3362 CLOSE c_pee;
3363 END IF;
3364 IF c_pen%ISOPEN THEN
3365 CLOSE c_pen;
3366 END IF;
3367 IF c_seet%ISOPEN THEN
3368 CLOSE c_seet;
3369 END IF;
3370 RETURN FALSE;
3371 WHEN OTHERS THEN
3372 IF c_pce%ISOPEN THEN
3373 CLOSE c_pce;
3374 END IF;
3375 IF c_pcge%ISOPEN THEN
3376 CLOSE c_pcge;
3377 END IF;
3378 IF c_puse%ISOPEN THEN
3379 CLOSE c_puse;
3380 END IF;
3381 IF c_pue%ISOPEN THEN
3382 CLOSE c_pue;
3383 END IF;
3384 IF c_pur%ISOPEN THEN
3385 CLOSE c_pur;
3386 END IF;
3387 IF c_pee%ISOPEN THEN
3388 CLOSE c_pee;
3389 END IF;
3390 IF c_pfe%ISOPEN THEN
3391 CLOSE c_pfe;
3392 END IF;
3393 IF c_pen%ISOPEN THEN
3394 CLOSE c_pen;
3395 END IF;
3396 IF c_seet%ISOPEN THEN
3397 CLOSE c_seet;
3398 END IF;
3399 RAISE;
3400 END;
3401 EXCEPTION
3402 WHEN OTHERS THEN
3403 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
3404 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.IGS_PR_UPD_SPO_PEN.PRGPL_UPD_EXPIRY_DT1');
3405 IGS_GE_MSG_STACK.ADD;
3406 App_Exception.Raise_Exception;
3407 END prgpl_upd_expiry_dt1;
3408
3409
3410 FUNCTION prgpl_upd_expiry_dt2
3411 RETURN BOOLEAN
3412 IS
3413 gvl_other_detail VARCHAR2(255);
3414
3415 BEGIN -- prgpl_upd_expiry_dt2
3416 -- Expire the encumbrance effect and all applicable subordinate tables
3417 DECLARE
3418 CURSOR c_pee IS
3419 SELECT pee.*,
3420 pee.ROWID
3421 FROM IGS_PE_PERS_ENCUMB pen,
3422 IGS_PE_PERSENC_EFFCT pee,
3423 igs_fi_enc_dflt_eft etde
3424 WHERE pen.person_id = p_person_id AND
3425 pen.spo_course_cd = p_course_cd AND
3426 pen.spo_sequence_number = p_spo_sequence_number AND
3427 pen.person_id = pee.person_id AND
3428 pen.encumbrance_type = pee.encumbrance_type AND
3429 pen.start_dt = pee.pen_start_dt AND
3430 pee.encumbrance_type = etde.encumbrance_type AND
3431 pee.s_encmb_effect_type <> etde.s_encmb_effect_type AND
3432 (pee.expiry_dt IS NULL OR
3433 pee.expiry_dt >= gcst_sysdate)
3434 FOR UPDATE NOWAIT;
3435
3436 CURSOR c_pce (
3437 cp_encumbrance_type IGS_PE_PERS_ENCUMB.encumbrance_type%TYPE,
3438 cp_pen_start_dt IGS_PE_PERS_ENCUMB.start_dt%TYPE,
3439 cp_s_encmb_effect_type IGS_PE_PERSENC_EFFCT.s_encmb_effect_type%TYPE,
3440 cp_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE,
3441 cp_pee_sequence_number IGS_PE_PERSENC_EFFCT.sequence_number%TYPE) IS
3442 SELECT pce.*, pce.ROWID
3443 FROM igs_pe_course_excl pce
3444 WHERE pce.person_id = p_person_id AND
3445 pce.encumbrance_type = cp_encumbrance_type AND
3446 pce.pen_start_dt = cp_pen_start_dt AND
3447 pce.s_encmb_effect_type = cp_s_encmb_effect_type AND
3448 pce.pee_start_dt = cp_pee_start_dt AND
3449 pce.pee_sequence_number = cp_pee_sequence_number
3450 FOR UPDATE NOWAIT;
3451
3452 CURSOR c_pcge (
3453 cp_encumbrance_type IGS_PE_PERS_ENCUMB.encumbrance_type%TYPE,
3454 cp_pen_start_dt IGS_PE_PERS_ENCUMB.start_dt%TYPE,
3455 cp_s_encmb_effect_type IGS_PE_PERSENC_EFFCT.s_encmb_effect_type%TYPE,
3456 cp_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE,
3457 cp_pee_sequence_number IGS_PE_PERSENC_EFFCT.sequence_number%TYPE) IS
3458 SELECT pcge.*, pcge.ROWID
3459 FROM igs_pe_crs_grp_excl pcge
3460 WHERE pcge.person_id = p_person_id AND
3461 pcge.encumbrance_type = cp_encumbrance_type AND
3462 pcge.pen_start_dt = cp_pen_start_dt AND
3463 pcge.s_encmb_effect_type = cp_s_encmb_effect_type AND
3464 pcge.pee_start_dt = cp_pee_start_dt AND
3465 pcge.pee_sequence_number = cp_pee_sequence_number
3466 FOR UPDATE NOWAIT;
3467
3468 CURSOR c_puse (
3469 cp_encumbrance_type IGS_PE_PERS_ENCUMB.encumbrance_type%TYPE,
3470 cp_pen_start_dt IGS_PE_PERS_ENCUMB.start_dt%TYPE,
3471 cp_s_encmb_effect_type IGS_PE_PERSENC_EFFCT.s_encmb_effect_type%TYPE,
3472 cp_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE,
3473 cp_pee_sequence_number IGS_PE_PERSENC_EFFCT.sequence_number%TYPE) IS
3474 SELECT puse.*, puse.ROWID
3475 FROM igs_pe_unt_set_excl puse
3476 WHERE puse.person_id = p_person_id AND
3477 puse.encumbrance_type = cp_encumbrance_type AND
3478 puse.pen_start_dt = cp_pen_start_dt AND
3479 puse.s_encmb_effect_type = cp_s_encmb_effect_type AND
3480 puse.pee_start_dt = cp_pee_start_dt AND
3481 puse.pee_sequence_number = cp_pee_sequence_number
3482 FOR UPDATE NOWAIT;
3483
3484 CURSOR c_pue (
3485 cp_encumbrance_type IGS_PE_PERS_ENCUMB.encumbrance_type%TYPE,
3486 cp_pen_start_dt IGS_PE_PERS_ENCUMB.start_dt%TYPE,
3487 cp_s_encmb_effect_type IGS_PE_PERSENC_EFFCT.s_encmb_effect_type%TYPE,
3488 cp_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE,
3489 cp_pee_sequence_number IGS_PE_PERSENC_EFFCT.sequence_number%TYPE) IS
3490 SELECT pue.*, pue.ROWID
3491 FROM igs_pe_pers_unt_excl pue
3492 WHERE pue.person_id = p_person_id AND
3493 pue.encumbrance_type = cp_encumbrance_type AND
3494 pue.pen_start_dt = cp_pen_start_dt AND
3495 pue.s_encmb_effect_type = cp_s_encmb_effect_type AND
3496 pue.pee_start_dt = cp_pee_start_dt AND
3497 pue.pee_sequence_number = cp_pee_sequence_number
3498 FOR UPDATE NOWAIT;
3499
3500 CURSOR c_pur (
3501 cp_encumbrance_type IGS_PE_PERS_ENCUMB.encumbrance_type%TYPE,
3502 cp_pen_start_dt IGS_PE_PERS_ENCUMB.start_dt%TYPE,
3503 cp_s_encmb_effect_type IGS_PE_PERSENC_EFFCT.s_encmb_effect_type%TYPE,
3504 cp_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE,
3505 cp_pee_sequence_number IGS_PE_PERSENC_EFFCT.sequence_number%TYPE) IS
3506 SELECT pur.*, pur.ROWID
3507 FROM igs_pe_unt_requirmnt pur
3508 WHERE pur.person_id = p_person_id AND
3509 pur.encumbrance_type = cp_encumbrance_type AND
3510 pur.pen_start_dt = cp_pen_start_dt AND
3511 pur.s_encmb_effect_type = cp_s_encmb_effect_type AND
3512 pur.pee_start_dt = cp_pee_start_dt AND
3513 pur.pee_sequence_number = cp_pee_sequence_number
3514 FOR UPDATE NOWAIT;
3515
3516 --
3517 -- Start of new code added as per the FA110 PR Enh. Bug# 2658550.
3518 --
3519 CURSOR c_pfe (
3520 cp_encumbrance_type IGS_PE_PERS_ENCUMB.encumbrance_type%TYPE,
3521 cp_pen_start_dt IGS_PE_PERS_ENCUMB.start_dt%TYPE,
3522 cp_s_encmb_effect_type IGS_PE_PERSENC_EFFCT.s_encmb_effect_type%TYPE,
3523 cp_pee_start_dt IGS_PE_PERSENC_EFFCT.pee_start_dt%TYPE,
3524 cp_pee_sequence_number IGS_PE_PERSENC_EFFCT.sequence_number%TYPE) IS
3525 SELECT pfe.*, pfe.ROWID
3526 FROM IGS_PE_FUND_EXCL pfe
3527 WHERE pfe.person_id = p_person_id AND
3528 pfe.encumbrance_type = cp_encumbrance_type AND
3529 pfe.pen_start_dt = cp_pen_start_dt AND
3530 pfe.s_encmb_effect_type = cp_s_encmb_effect_type AND
3531 pfe.pee_start_dt = cp_pee_start_dt AND
3532 pfe.pee_sequence_number = cp_pee_sequence_number
3533 FOR UPDATE NOWAIT;
3534 --
3535 -- End of new code added as per the FA110 PR Enh. Bug# 2658550.
3536 --
3537
3538 BEGIN
3539 FOR v_pee_rec IN c_pee LOOP
3540
3541 FOR v_pce_rec IN c_pce (
3542 v_pee_rec.encumbrance_type,
3543 v_pee_rec.pen_start_dt,
3544 v_pee_rec.s_encmb_effect_type,
3545 v_pee_rec.pee_start_dt,
3546 v_pee_rec.sequence_number) LOOP
3547 igs_pe_course_excl_pkg.UPDATE_ROW(
3548 X_ROWID => v_pce_rec.ROWID,
3549 X_PERSON_ID => v_pce_rec.PERSON_ID,
3550 X_ENCUMBRANCE_TYPE => v_pce_rec.ENCUMBRANCE_TYPE,
3551 X_PEN_START_DT => v_pce_rec.PEN_START_DT,
3552 X_S_ENCMB_EFFECT_TYPE => v_pce_rec.S_ENCMB_EFFECT_TYPE,
3553 X_PEE_START_DT => v_pce_rec.PEE_START_DT,
3554 X_PEE_SEQUENCE_NUMBER => v_pce_rec.PEE_SEQUENCE_NUMBER,
3555 X_COURSE_CD => v_pce_rec.COURSE_CD,
3556 X_PCE_START_DT => v_pce_rec.PCE_START_DT,
3557 X_EXPIRY_DT => gcst_sysdatetime, --gjha1
3558 X_MODE => 'R'
3559 );
3560 END LOOP; -- c_pce
3561
3562 FOR v_pcge_rec IN c_pcge (
3563 v_pee_rec.encumbrance_type,
3564 v_pee_rec.pen_start_dt,
3565 v_pee_rec.s_encmb_effect_type,
3566 v_pee_rec.pee_start_dt,
3567 v_pee_rec.sequence_number) LOOP
3568 igs_pe_crs_grp_excl_pkg.UPDATE_ROW(
3569 X_ROWID => v_pcge_rec.ROWID,
3570 X_PERSON_ID => v_pcge_rec.PERSON_ID,
3571 X_ENCUMBRANCE_TYPE => v_pcge_rec.ENCUMBRANCE_TYPE,
3572 X_PEN_START_DT => v_pcge_rec.PEN_START_DT,
3573 X_S_ENCMB_EFFECT_TYPE => v_pcge_rec.S_ENCMB_EFFECT_TYPE,
3574 X_PEE_START_DT => v_pcge_rec.PEE_START_DT,
3575 X_PEE_SEQUENCE_NUMBER => v_pcge_rec.PEE_SEQUENCE_NUMBER,
3576 X_COURSE_GROUP_CD => v_pcge_rec.COURSE_GROUP_CD,
3577 X_PCGE_START_DT => v_pcge_rec.PCGE_START_DT,
3578 X_EXPIRY_DT => gcst_sysdatetime , --gjha1
3579 X_MODE => 'R'
3580 );
3581 END LOOP; -- c_pcge
3582
3583 FOR v_puse_rec IN c_puse (
3584 v_pee_rec.encumbrance_type,
3585 v_pee_rec.pen_start_dt,
3586 v_pee_rec.s_encmb_effect_type,
3587 v_pee_rec.pee_start_dt,
3588 v_pee_rec.sequence_number) LOOP
3589 igs_pe_unt_set_excl_pkg.UPDATE_ROW(
3590 X_ROWID => v_puse_rec.ROWID,
3591 X_PERSON_ID => v_puse_rec.PERSON_ID,
3592 X_ENCUMBRANCE_TYPE => v_puse_rec.ENCUMBRANCE_TYPE,
3593 X_PEN_START_DT => v_puse_rec.PEN_START_DT,
3594 X_S_ENCMB_EFFECT_TYPE => v_puse_rec.S_ENCMB_EFFECT_TYPE,
3595 X_PEE_START_DT => v_puse_rec.PEE_START_DT,
3596 X_PEE_SEQUENCE_NUMBER => v_puse_rec.PEE_SEQUENCE_NUMBER,
3597 X_UNIT_SET_CD => v_puse_rec.UNIT_SET_CD,
3598 X_US_VERSION_NUMBER => v_puse_rec.US_VERSION_NUMBER,
3599 X_PUSE_START_DT => v_puse_rec.PUSE_START_DT,
3600 X_EXPIRY_DT => gcst_sysdatetime, --gjha1
3601 X_MODE => 'R'
3602 );
3603 END LOOP; -- c_puse
3604
3605 FOR v_pue_rec IN c_pue (
3606 v_pee_rec.encumbrance_type,
3607 v_pee_rec.pen_start_dt,
3608 v_pee_rec.s_encmb_effect_type,
3609 v_pee_rec.pee_start_dt,
3610 v_pee_rec.sequence_number) LOOP
3611 igs_pe_pers_unt_excl_pkg.UPDATE_ROW(
3612 X_ROWID => v_pue_rec.ROWID,
3613 X_PERSON_ID => v_pue_rec.PERSON_ID,
3614 X_ENCUMBRANCE_TYPE => v_pue_rec.ENCUMBRANCE_TYPE,
3615 X_PEN_START_DT => v_pue_rec.PEN_START_DT,
3616 X_S_ENCMB_EFFECT_TYPE => v_pue_rec.S_ENCMB_EFFECT_TYPE,
3617 X_PEE_START_DT => v_pue_rec.PEE_START_DT,
3618 X_PEE_SEQUENCE_NUMBER => v_pue_rec.PEE_SEQUENCE_NUMBER,
3619 X_UNIT_CD => v_pue_rec.UNIT_CD,
3620 X_PUE_START_DT => v_pue_rec.PUE_START_DT,
3621 X_EXPIRY_DT => gcst_sysdatetime, --gjha1
3622 X_MODE => 'R'
3623 );
3624 END LOOP; -- c_pue
3625
3626 --
3627 -- Start of new code added as per the FA110 PR Enh. Bug# 2658550.
3628 --
3629 FOR v_pfe_rec IN c_pfe (v_pee_rec.encumbrance_type,
3630 v_pee_rec.pen_start_dt,
3631 v_pee_rec.s_encmb_effect_type,
3632 v_pee_rec.pee_start_dt,
3633 v_pee_rec.sequence_number) LOOP
3634 igs_pe_fund_excl_pkg.update_row(
3635 X_ROWID => v_pfe_rec.rowid ,
3636 X_FUND_EXCL_ID => v_pfe_rec.fund_excl_id ,
3637 X_PERSON_ID => v_pfe_rec.person_id ,
3638 X_ENCUMBRANCE_TYPE => v_pfe_rec.encumbrance_type ,
3639 X_PEN_START_DT => v_pfe_rec.pen_start_dt ,
3640 X_S_ENCMB_EFFECT_TYPE => v_pfe_rec.s_encmb_effect_type,
3641 X_PEE_START_DT => v_pfe_rec.pee_start_dt ,
3642 X_PEE_SEQUENCE_NUMBER => v_pfe_rec.pee_sequence_number,
3643 X_FUND_CODE => v_pfe_rec.fund_code ,
3644 X_PFE_START_DT => v_pfe_rec.pfe_start_dt ,
3645 X_EXPIRY_DT => gcst_sysdatetime ,
3646 X_MODE => 'R');
3647 END LOOP; -- c_pfe
3648 --
3649 -- End of new code added as per the FA110 PR Enh. Bug# 2658550.
3650 --
3651
3652 FOR v_pur_rec IN c_pur (
3653 v_pee_rec.encumbrance_type,
3654 v_pee_rec.pen_start_dt,
3655 v_pee_rec.s_encmb_effect_type,
3656 v_pee_rec.pee_start_dt,
3657 v_pee_rec.sequence_number) LOOP
3658 /*
3659 UPDATE igs_pe_unt_requirmnt
3660 SET expiry_dt = gcst_sysdate
3661 WHERE CURRENT OF c_pur;
3662 */
3663 igs_pe_unt_requirmnt_pkg.UPDATE_ROW(
3664 X_ROWID => v_pur_rec.ROWID,
3665 X_PERSON_ID => v_pur_rec.PERSON_ID,
3666 X_ENCUMBRANCE_TYPE => v_pur_rec.ENCUMBRANCE_TYPE,
3667 X_PEN_START_DT => v_pur_rec.PEN_START_DT,
3668 X_S_ENCMB_EFFECT_TYPE => v_pur_rec.S_ENCMB_EFFECT_TYPE,
3669 X_PEE_START_DT => v_pur_rec.PEE_START_DT,
3670 X_PEE_SEQUENCE_NUMBER => v_pur_rec.PEE_SEQUENCE_NUMBER,
3671 X_UNIT_CD => v_pur_rec.UNIT_CD,
3672 X_PUR_START_DT => v_pur_rec.PUR_START_DT,
3673 X_EXPIRY_DT => gcst_sysdatetime,--gjha1
3674 X_MODE => 'R'
3675 );
3676 END LOOP; -- c_pur
3677 IGS_PE_PERSENC_EFFCT_PKG.UPDATE_ROW(
3678 X_ROWID => v_pee_rec.ROWID,
3679 X_PERSON_ID => v_pee_rec.PERSON_ID,
3680 X_ENCUMBRANCE_TYPE => v_pee_rec.ENCUMBRANCE_TYPE,
3681 X_PEN_START_DT => v_pee_rec.PEN_START_DT,
3682 X_S_ENCMB_EFFECT_TYPE => v_pee_rec.S_ENCMB_EFFECT_TYPE,
3683 X_PEE_START_DT => v_pee_rec.PEE_START_DT,
3684 X_SEQUENCE_NUMBER => v_pee_rec.SEQUENCE_NUMBER,
3685 X_EXPIRY_DT => gcst_sysdatetime, --updated
3686 X_COURSE_CD => v_pee_rec.COURSE_CD,
3687 X_RESTRICTED_ENROLMENT_CP => v_pee_rec.RESTRICTED_ENROLMENT_CP,
3688 X_RESTRICTED_ATTENDANCE_TYPE => v_pee_rec.RESTRICTED_ATTENDANCE_TYPE,
3689 X_MODE => 'R'
3690 );
3691 END LOOP; -- c_pee
3692 RETURN TRUE;
3693 EXCEPTION
3694 WHEN e_record_locked THEN
3695 IF c_pce%ISOPEN THEN
3696 CLOSE c_pce;
3697 END IF;
3698 IF c_pcge%ISOPEN THEN
3699 CLOSE c_pcge;
3700 END IF;
3701 IF c_puse%ISOPEN THEN
3702 CLOSE c_puse;
3703 END IF;
3704 IF c_pue%ISOPEN THEN
3705 CLOSE c_pue;
3706 END IF;
3707 IF c_pur%ISOPEN THEN
3708 CLOSE c_pur;
3709 END IF;
3710 IF c_pee%ISOPEN THEN
3711 CLOSE c_pee;
3712 END IF;
3713 IF c_pfe%ISOPEN THEN
3714 CLOSE c_pfe;
3715 END IF;
3716 RETURN FALSE;
3717 WHEN OTHERS THEN
3718 IF c_pce%ISOPEN THEN
3719 CLOSE c_pce;
3720 END IF;
3721 IF c_pcge%ISOPEN THEN
3722 CLOSE c_pcge;
3723 END IF;
3724 IF c_puse%ISOPEN THEN
3725 CLOSE c_puse;
3726 END IF;
3727 IF c_pue%ISOPEN THEN
3728 CLOSE c_pue;
3729 END IF;
3730 IF c_pur%ISOPEN THEN
3731 CLOSE c_pur;
3732 END IF;
3733 IF c_pee%ISOPEN THEN
3734 CLOSE c_pee;
3735 END IF;
3736 IF c_seet%ISOPEN THEN
3737 CLOSE c_seet;
3738 END IF;
3739 IF c_pfe%ISOPEN THEN
3740 CLOSE c_pfe;
3741 END IF;
3742 RAISE;
3743 END;
3744 EXCEPTION
3745 WHEN OTHERS THEN
3746 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
3747 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.IGS_PR_UPD_SPO_PEN.PRGPL_UPD_EXPIRY_DT2');
3748 IGS_GE_MSG_STACK.ADD;
3749 App_Exception.Raise_Exception;
3750 END prgpl_upd_expiry_dt2;
3751
3752 BEGIN
3753
3754 SAVEPOINT sp_before_update;
3755 OPEN c_spo;
3756 FETCH c_spo INTO v_spo_rec;
3757 IF c_spo%NOTFOUND THEN
3758 CLOSE c_spo;
3759 RETURN TRUE;
3760 END IF;
3761 CLOSE c_spo;
3762
3763 -- Check that authorising person ID is set ; if not, then set to current
3764 -- person ID (which is likely to be a batch queue user).
3765 IF p_authorising_person_id IS NULL THEN
3766 OPEN c_pe;
3767 FETCH c_pe INTO v_authorising_person_id;
3768 IF c_pe%NOTFOUND THEN
3769 CLOSE c_pe;
3770 -- p_message_text := IGS_GE_GEN_002.GENP_GET_MESSAGE(5274);
3771 p_message_level := cst_error;
3772 RETURN FALSE;
3773 END IF;
3774 CLOSE c_pe;
3775 ELSE
3776 v_authorising_person_id := p_authorising_person_id;
3777 END IF;
3778
3779 OPEN c_pen;
3780 FETCH c_pen INTO v_pen_expiry_dt;
3781 IF c_pen%FOUND THEN
3782 CLOSE c_pen;
3783 v_pen_exists := TRUE;
3784 ELSE
3785 CLOSE c_pen;
3786 v_pen_exists := FALSE;
3787 END IF;
3788
3789 IF v_spo_rec.decision_status IN (
3790 cst_cancelled,
3791 cst_removed,
3792 cst_pending,
3793 cst_waived) OR
3794 v_spo_rec.encumbrance_type IS NULL THEN
3795 IF v_pen_exists THEN
3796 -- If the outcome has been cancelled then expire the encumbrance effective
3797 -- immediately
3798 IF NOT prgpl_upd_expiry_dt1 (gcst_sysdatetime, --gjha1
3799 v_message_level,
3800 v_message_text) THEN
3801 -- Record locked
3802 ROLLBACK TO sp_before_update;
3803 p_message_level := cst_error;
3804 RETURN FALSE;
3805 END IF;
3806 END IF;
3807 RETURN TRUE;
3808 ELSE
3809
3810 -- Select encumbrances resulting
3811 v_etde_index := 0;
3812 FOR v_edte_rec IN c_etde (v_spo_rec.encumbrance_type) LOOP
3813 v_etde_index := v_etde_index + 1;
3814 v_etde_table(v_etde_index).s_encmb_effect_type := v_edte_rec.s_encmb_effect_type;
3815 END LOOP; -- c_etde
3816
3817 IF v_pen_exists THEN
3818 -- Remove effects that no longer apply
3819 IF NOT prgpl_upd_expiry_dt2 THEN
3820 -- Record locked
3821 ROLLBACK TO sp_before_update;
3822 -- p_message_text := IGS_GE_GEN_002.GENP_GET_MESSAGE(5273);
3823 p_message_level := cst_error;
3824 RETURN FALSE;
3825 END IF;
3826 -- Alter expiry date of person encumbrance structures where required
3827
3828 IF v_spo_rec.expiry_dt <= TRUNC(SYSDATE) THEN
3829 v_expiry_status := IGS_PR_GEN_006.IGS_PR_get_spo_expiry (
3830 p_person_id,
3831 p_course_cd,
3832 p_spo_sequence_number,
3833 v_spo_rec.expiry_dt,
3834 v_expiry_dt);
3835
3836 ELSE
3837
3838 -- Don't pass the spo.expiry_dt forcing re-derivation ; this will CHECK FOR
3839 -- differences.
3840 v_expiry_status := IGS_PR_get_spo_expiry (
3841 p_person_id,
3842 p_course_cd,
3843 p_spo_sequence_number,
3844 NULL,
3845 v_expiry_dt);
3846 END IF;
3847
3848 IF NVL(v_pen_expiry_dt,IGS_GE_DATE.IGSDATE('9999/01/01')) <>
3849 NVL(v_expiry_dt, IGS_GE_DATE.IGSDATE('9999/01/01')) THEN
3850 IF NVL(v_expiry_dt, IGS_GE_DATE.IGSDATE('9999/01/01')) < gcst_sysdate THEN
3851 v_action_expiry_dt := gcst_sysdatetime;
3852 ELSE
3853 v_action_expiry_dt := v_expiry_dt;
3854 END IF;
3855 -- Update the elements of the structure to reflect the new expiry date
3856 IF NOT prgpl_upd_expiry_dt1 (v_action_expiry_dt,
3857 v_message_level,
3858 v_message_text) THEN
3859 ROLLBACK TO sp_before_update;
3860 IF v_message_level IS NOT NULL THEN
3861 -- Encumbrance clash.
3862 p_message_text := v_message_text;
3863 p_message_level := v_message_level;
3864 ELSE
3865
3866 -- Record locked
3867 -- p_message_text := IGS_GE_GEN_002.GENP_GET_MESSAGE(5273);
3868 p_message_level := cst_error;
3869 END IF;
3870 RETURN FALSE;
3871 ELSIF v_message_level IS NOT NULL THEN
3872 -- Encumbrance warning only.
3873 IF p_message_level IS NULL OR
3874 v_message_level = cst_expired THEN
3875 p_message_text := v_message_text;
3876 p_message_level := v_message_level;
3877 END IF;
3878 END IF;
3879 END IF;
3880 IF v_expiry_dt < gcst_sysdate THEN
3881 v_expiry_dt := gcst_sysdatetime;
3882 END IF;
3883
3884 -- Alter effects that currently exist where required
3885 FOR v_pee_rec IN c_pee LOOP
3886
3887 IF v_pee_rec.s_encmb_effect_type IN (
3888 cst_rstr_ge_cp,
3889 cst_rstr_le_cp) AND
3890 NVL(v_pee_rec.restricted_enrolment_cp, -1) <>
3891 NVL(v_spo_rec.restricted_enrolment_cp, -1) THEN
3892
3893 IGS_PE_PERSENC_EFFCT_PKG.UPDATE_ROW(
3894 X_ROWID => v_pee_rec.ROWID,
3895 X_PERSON_ID => v_pee_rec.PERSON_ID,
3896 X_ENCUMBRANCE_TYPE => v_pee_rec.ENCUMBRANCE_TYPE,
3897 X_PEN_START_DT => v_pee_rec.PEN_START_DT,
3898 X_S_ENCMB_EFFECT_TYPE => v_pee_rec.S_ENCMB_EFFECT_TYPE,
3899 X_PEE_START_DT => v_pee_rec.PEE_START_DT,
3900 X_SEQUENCE_NUMBER => v_pee_rec.SEQUENCE_NUMBER,
3901 X_EXPIRY_DT => v_expiry_dt, --updated
3902 X_COURSE_CD => v_pee_rec.COURSE_CD,
3903 X_RESTRICTED_ENROLMENT_CP => v_spo_rec.restricted_enrolment_cp, --updated
3904 X_RESTRICTED_ATTENDANCE_TYPE => v_pee_rec.RESTRICTED_ATTENDANCE_TYPE,
3905 X_MODE => 'R'
3906 );
3907
3908
3909 ELSIF v_pee_rec.s_encmb_effect_type = cst_rstr_at_ty AND
3910 NVL(v_pee_rec.restricted_attendance_type,'NULL') <>
3911 NVL(v_spo_rec.restricted_attendance_type,'NULL') AND
3912 v_spo_rec.closed_ind = 'N' THEN
3913
3914 IGS_PE_PERSENC_EFFCT_PKG.UPDATE_ROW(
3915 X_ROWID => v_pee_rec.ROWID,
3916 X_PERSON_ID => v_pee_rec.PERSON_ID,
3917 X_ENCUMBRANCE_TYPE => v_pee_rec.ENCUMBRANCE_TYPE,
3918 X_PEN_START_DT => v_pee_rec.PEN_START_DT,
3919 X_S_ENCMB_EFFECT_TYPE => v_pee_rec.S_ENCMB_EFFECT_TYPE,
3920 X_PEE_START_DT => v_pee_rec.PEE_START_DT,
3921 X_SEQUENCE_NUMBER => v_pee_rec.SEQUENCE_NUMBER,
3922 X_EXPIRY_DT => v_expiry_dt, --updated
3923 X_COURSE_CD => v_pee_rec.COURSE_CD,
3924 X_RESTRICTED_ENROLMENT_CP => v_pee_rec.restricted_enrolment_cp,
3925 X_RESTRICTED_ATTENDANCE_TYPE => v_spo_rec.restricted_attendance_type, --updated
3926 X_MODE => 'R'
3927 );
3928
3929
3930 ELSIF v_pee_rec.s_encmb_effect_type IN (
3931 cst_sus_course,
3932 cst_exc_course) THEN
3933 -- Add spc.course_cd's TO spc PL/SQL TABLE
3934 v_spc_index := 0;
3935
3936 FOR v_spc_rec IN c_spc LOOP
3937 v_spc_index := v_spc_index + 1;
3938 v_spc_table(v_spc_index) := v_spc_rec.course_cd;
3939 END LOOP; -- c_spc
3940
3941 v_pce_index := 0;
3942 FOR v_pce_rec IN c_pce (
3943 v_pee_rec.encumbrance_type,
3944 v_pee_rec.pen_start_dt,
3945 v_pee_rec.s_encmb_effect_type,
3946 v_pee_rec.pee_start_dt,
3947 v_pee_rec.sequence_number) LOOP
3948 -- Add pce.course_cd to pce PL/SQL table
3949 v_pce_index := v_pce_index + 1;
3950 v_pce_table(v_pce_index) := v_pce_rec.course_cd;
3951
3952 -- Check if pce.course_cd in spc PL/SQL table
3953 v_course_cd_found := FALSE;
3954 FOR v_index1 IN 1..v_spc_index LOOP
3955 IF v_spc_table(v_index1) = v_pce_rec.course_cd THEN
3956 v_course_cd_found := TRUE;
3957 EXIT;
3958 END IF;
3959
3960 END LOOP;
3961 IF NOT v_course_cd_found THEN
3962
3963 igs_pe_course_excl_pkg.UPDATE_ROW(
3964 X_ROWID => v_pce_rec.ROWID,
3965 X_PERSON_ID => v_pce_rec.PERSON_ID,
3966 X_ENCUMBRANCE_TYPE => v_pce_rec.ENCUMBRANCE_TYPE,
3967 X_PEN_START_DT => v_pce_rec.PEN_START_DT,
3968 X_S_ENCMB_EFFECT_TYPE => v_pce_rec.S_ENCMB_EFFECT_TYPE,
3969 X_PEE_START_DT => v_pce_rec.PEE_START_DT,
3970 X_PEE_SEQUENCE_NUMBER => v_pce_rec.PEE_SEQUENCE_NUMBER,
3971 X_COURSE_CD => v_pce_rec.COURSE_CD,
3972 X_PCE_START_DT => v_pce_rec.PCE_START_DT,
3973 X_EXPIRY_DT => gcst_sysdatetime,
3974 X_MODE => 'R'
3975 );
3976
3977
3978 END IF;
3979 END LOOP; -- c_pce
3980 -- Check if all spc.course_cd's IN pce PL/SQL TABLE, IF NOT ADD NEW
3981 -- pce record
3982 FOR v_index1 IN 1..v_spc_index LOOP
3983 v_course_cd_found := FALSE;
3984 FOR v_index2 IN 1..v_pce_index LOOP
3985 IF v_spc_table(v_index1) = v_pce_table(v_index2) THEN
3986 v_course_cd_found := TRUE;
3987 EXIT;
3988 END IF;
3989 END LOOP;
3990 IF NOT v_course_cd_found THEN
3991
3992 OPEN c_pce_dup (
3993 v_pee_rec.encumbrance_type,
3994 v_pee_rec.pen_start_dt,
3995 v_pee_rec.s_encmb_effect_type,
3996 v_pee_rec.pee_start_dt,
3997 v_pee_rec.sequence_number,
3998 v_spc_table(v_index1),
3999 gcst_sysdate);
4000 FETCH c_pce_dup INTO v_pce_dup_rec;
4001 IF c_pce_dup%FOUND THEN
4002 -- Re-open closed record.
4003
4004 igs_pe_course_excl_pkg.UPDATE_ROW(
4005 X_ROWID => v_pce_dup_rec.ROWID,
4006 X_PERSON_ID => v_pce_dup_rec.PERSON_ID,
4007 X_ENCUMBRANCE_TYPE => v_pce_dup_rec.ENCUMBRANCE_TYPE,
4008 X_PEN_START_DT => v_pce_dup_rec.PEN_START_DT,
4009 X_S_ENCMB_EFFECT_TYPE => v_pce_dup_rec.S_ENCMB_EFFECT_TYPE,
4010 X_PEE_START_DT => v_pce_dup_rec.PEE_START_DT,
4011 X_PEE_SEQUENCE_NUMBER => v_pce_dup_rec.PEE_SEQUENCE_NUMBER,
4012 X_COURSE_CD => v_pce_dup_rec.COURSE_CD,
4013 X_PCE_START_DT => v_pce_dup_rec.PCE_START_DT,
4014 X_EXPIRY_DT => v_expiry_dt,
4015 X_MODE => 'R'
4016 );
4017
4018
4019 CLOSE c_pce_dup;
4020 ELSE
4021 CLOSE c_pce_dup;
4022 /*INSERT INTO igs_pe_course_excl (
4023 person_id,
4024 encumbrance_type,
4025 pen_start_dt,
4026 s_encmb_effect_type,
4027 pee_start_dt,
4028 pee_sequence_number,
4029 course_cd,
4030 pce_start_dt,
4031 expiry_dt)
4032 VALUES (
4033 p_person_id,
4034 v_pee_rec.encumbrance_type,
4035 v_pee_rec.pen_start_dt,
4036 v_pee_rec.s_encmb_effect_type,
4037 v_pee_rec.pee_start_dt,
4038 v_pee_rec.sequence_number,
4039 v_spc_table(v_index1),
4040 gcst_sysdate,
4041 v_expiry_dt); */
4042 DECLARE
4043 lv_rowid VARCHAR2(25);
4044 BEGIN
4045 BEGIN
4046 igs_pe_course_excl_pkg.INSERT_ROW (
4047 X_ROWID =>lv_rowid,
4048 X_PERSON_ID =>p_person_id,
4049 X_ENCUMBRANCE_TYPE =>v_pee_rec.encumbrance_type,
4050 X_PEN_START_DT =>v_pee_rec.pen_start_dt,
4051 X_S_ENCMB_EFFECT_TYPE =>v_pee_rec.s_encmb_effect_type,
4052 X_PEE_START_DT =>v_pee_rec.pee_start_dt,
4053 X_PEE_SEQUENCE_NUMBER =>v_pee_rec.sequence_number,
4054 X_COURSE_CD =>v_spc_table(v_index1),
4055 X_PCE_START_DT =>gcst_sysdatetime, ---- GJHA Changed it from gcst_sysdate
4056 X_EXPIRY_DT =>v_expiry_dt,
4057 X_MODE =>'R'
4058 );
4059 EXCEPTION WHEN OTHERS THEN
4060 RAISE;
4061 END;
4062 END;
4063 END IF;
4064 END IF;
4065 END LOOP;
4066 ELSIF v_pee_rec.s_encmb_effect_type = cst_exc_crs_gp THEN
4067 v_pcge_index := 0;
4068 FOR v_pcge_rec IN c_pcge (
4069 v_pee_rec.encumbrance_type,
4070 v_pee_rec.pen_start_dt,
4071 v_pee_rec.s_encmb_effect_type,
4072 v_pee_rec.pee_start_dt,
4073 v_pee_rec.sequence_number) LOOP
4074 -- Add pcge.course_cd to pcge PL/SQL table
4075 v_pcge_index := v_pcge_index + 1;
4076 v_pcge_table(v_pcge_index) := v_pcge_rec.course_group_cd;
4077 IF v_pcge_rec.course_group_cd <> v_spo_rec.encmb_course_group_cd THEN
4078 igs_pe_crs_grp_excl_pkg.UPDATE_ROW(
4079 X_ROWID => v_pcge_rec.ROWID,
4080 X_PERSON_ID => v_pcge_rec.PERSON_ID,
4081 X_ENCUMBRANCE_TYPE => v_pcge_rec.ENCUMBRANCE_TYPE,
4082 X_PEN_START_DT => v_pcge_rec.PEN_START_DT,
4083 X_S_ENCMB_EFFECT_TYPE => v_pcge_rec.S_ENCMB_EFFECT_TYPE,
4084 X_PEE_START_DT => v_pcge_rec.PEE_START_DT,
4085 X_PEE_SEQUENCE_NUMBER => v_pcge_rec.PEE_SEQUENCE_NUMBER,
4086 X_COURSE_GROUP_CD => v_pcge_rec.COURSE_GROUP_CD,
4087 X_PCGE_START_DT => v_pcge_rec.PCGE_START_DT,
4088 X_EXPIRY_DT => gcst_sysdatetime,
4089 X_MODE => 'R'
4090 );
4091 END IF;
4092 END LOOP; -- c_pcge
4093 -- Check if spo.course_group_cd in pcge PL/SQL table, if not add new
4094 -- pcge record
4095 v_course_grp_cd_found := FALSE;
4096 FOR v_index1 IN 1..v_pcge_index LOOP
4097 IF v_pcge_table(v_index1) = v_spo_rec.encmb_course_group_cd THEN
4098 v_course_grp_cd_found := TRUE;
4099 EXIT;
4100 END IF;
4101 END LOOP;
4102 IF NOT v_course_grp_cd_found THEN
4103 OPEN c_pcge_dup (
4104 v_pee_rec.encumbrance_type,
4105 v_pee_rec.pen_start_dt,
4106 v_pee_rec.s_encmb_effect_type,
4107 v_pee_rec.pee_start_dt,
4108 v_pee_rec.sequence_number,
4109 v_spo_rec.encmb_course_group_cd,
4110 gcst_sysdate);
4111 FETCH c_pcge_dup INTO v_pcge_dup_rec;
4112 IF c_pcge_dup%FOUND THEN
4113 igs_pe_crs_grp_excl_pkg.UPDATE_ROW(
4114 X_ROWID => v_pcge_dup_rec.ROWID,
4115 X_PERSON_ID => v_pcge_dup_rec.PERSON_ID,
4116 X_ENCUMBRANCE_TYPE => v_pcge_dup_rec.ENCUMBRANCE_TYPE,
4117 X_PEN_START_DT => v_pcge_dup_rec.PEN_START_DT,
4118 X_S_ENCMB_EFFECT_TYPE => v_pcge_dup_rec.S_ENCMB_EFFECT_TYPE,
4119 X_PEE_START_DT => v_pcge_dup_rec.PEE_START_DT,
4120 X_PEE_SEQUENCE_NUMBER => v_pcge_dup_rec.PEE_SEQUENCE_NUMBER,
4121 X_COURSE_GROUP_CD => v_pcge_dup_rec.COURSE_GROUP_CD,
4122 X_PCGE_START_DT => v_pcge_dup_rec.PCGE_START_DT,
4123 X_EXPIRY_DT => v_expiry_dt,
4124 X_MODE => 'R'
4125 );
4126 CLOSE c_pcge_dup;
4127 ELSE
4128 CLOSE c_pcge_dup;
4129 DECLARE
4130 lv_rowid VARCHAR2(25);
4131 BEGIN
4132 igs_pe_crs_grp_excl_pkg.INSERT_ROW(
4133 X_ROWID => lv_rowid,
4134 X_PERSON_ID => P_PERSON_ID,
4135 X_ENCUMBRANCE_TYPE => v_pee_rec.ENCUMBRANCE_TYPE,
4136 X_PEN_START_DT => v_pee_rec.PEN_START_DT,
4137 X_S_ENCMB_EFFECT_TYPE => v_pee_rec.S_ENCMB_EFFECT_TYPE,
4138 X_PEE_START_DT => v_pee_rec.PEE_START_DT,
4139 X_PEE_SEQUENCE_NUMBER => v_pee_rec.SEQUENCE_NUMBER,
4140 X_COURSE_GROUP_CD => v_spo_rec.encmb_course_group_cd,
4141 X_PCGE_START_DT => gcst_sysdatetime, --gjha Changed it from gcst_sysdate
4142 X_EXPIRY_DT => v_expiry_dt,
4143 X_MODE => 'R'
4144 );
4145 END;
4146 END IF;
4147 END IF;
4148 ELSIF v_pee_rec.s_encmb_effect_type = cst_exc_crs_us THEN
4149 -- Add spus.course_cd's TO spus PL/SQL TABLE
4150 v_spus_index := 0;
4151 FOR v_spus_rec IN c_spus LOOP
4152 v_spus_index := v_spus_index + 1;
4153 v_spus_table(v_spus_index).unit_set_cd := v_spus_rec.unit_set_cd;
4154 v_spus_table(v_spus_index).version_number := v_spus_rec.version_number;
4155 END LOOP; -- c_spus
4156 v_puse_index := 0;
4157 FOR v_puse_rec IN c_puse (
4158 v_pee_rec.encumbrance_type,
4159 v_pee_rec.pen_start_dt,
4160 v_pee_rec.s_encmb_effect_type,
4161 v_pee_rec.pee_start_dt,
4162 v_pee_rec.sequence_number) LOOP
4163 -- Add puse.unit_set_cd, us_version_number to pce PL/SQL table
4164 v_puse_index := v_puse_index + 1;
4165 v_puse_table(v_puse_index).unit_set_cd := v_puse_rec.unit_set_cd;
4166 v_puse_table(v_puse_index).us_version_number :=
4167 v_puse_rec.us_version_number;
4168 -- Check if unit set in spus PL/SQL table
4169 v_unit_set_found := FALSE;
4170 FOR v_index1 IN 1..v_spus_index LOOP
4171 IF v_spus_table(v_index1).unit_set_cd = v_puse_rec.unit_set_cd AND
4172 v_spus_table(v_index1).version_number =
4173 v_puse_rec.us_version_number THEN
4174 v_unit_set_found := TRUE;
4175 EXIT;
4176 END IF;
4177 END LOOP;
4178
4179 IF NOT v_unit_set_found THEN
4180 igs_pe_unt_set_excl_pkg.UPDATE_ROW(
4181 X_ROWID => v_puse_rec.ROWID,
4182 X_PERSON_ID => v_puse_rec.PERSON_ID,
4183 X_ENCUMBRANCE_TYPE => v_puse_rec.ENCUMBRANCE_TYPE,
4184 X_PEN_START_DT => v_puse_rec.PEN_START_DT,
4185 X_S_ENCMB_EFFECT_TYPE => v_puse_rec.S_ENCMB_EFFECT_TYPE,
4186 X_PEE_START_DT => v_puse_rec.PEE_START_DT,
4187 X_PEE_SEQUENCE_NUMBER => v_puse_rec.PEE_SEQUENCE_NUMBER,
4188 X_UNIT_SET_CD => v_puse_rec.UNIT_SET_CD,
4189 X_US_VERSION_NUMBER => v_puse_rec.US_VERSION_NUMBER,
4190 X_PUSE_START_DT => v_puse_rec.PUSE_START_DT,
4191 X_EXPIRY_DT => gcst_sysdate,
4192 X_MODE => 'R'
4193 );
4194
4195 END IF;
4196 END LOOP; -- c_puse
4197 -- Check if all spus unit sets in puse PL/SQL table, if not add new
4198 -- puse record
4199 FOR v_index1 IN 1..v_spus_index LOOP
4200 v_unit_set_found := FALSE;
4201 FOR v_index2 IN 1..v_puse_index LOOP
4202 IF v_spus_table(v_index1).unit_set_cd =
4203 v_puse_table(v_index2).unit_set_cd AND
4204 v_spus_table(v_index1).version_number =
4205 v_puse_table(v_index2).us_version_number THEN
4206 v_unit_set_found := TRUE;
4207 EXIT;
4208 END IF;
4209 END LOOP;
4210 IF NOT v_unit_set_found THEN
4211 OPEN c_puse_dup (
4212 v_pee_rec.encumbrance_type,
4213 v_pee_rec.pen_start_dt,
4214 v_pee_rec.s_encmb_effect_type,
4215 v_pee_rec.pee_start_dt,
4216 v_pee_rec.sequence_number,
4217 v_spus_table(v_index1).unit_set_cd,
4218 v_spus_table(v_index1).version_number,
4219 gcst_sysdate);
4220 FETCH c_puse_dup INTO v_puse_dup_rec;
4221 IF c_puse_dup%FOUND THEN
4222 igs_pe_unt_set_excl_pkg.UPDATE_ROW(
4223 X_ROWID => v_puse_dup_rec.ROWID,
4224 X_PERSON_ID => v_puse_dup_rec.PERSON_ID,
4225 X_ENCUMBRANCE_TYPE => v_puse_dup_rec.ENCUMBRANCE_TYPE,
4226 X_PEN_START_DT => v_puse_dup_rec.PEN_START_DT,
4227 X_S_ENCMB_EFFECT_TYPE => v_puse_dup_rec.S_ENCMB_EFFECT_TYPE,
4228 X_PEE_START_DT => v_puse_dup_rec.PEE_START_DT,
4229 X_PEE_SEQUENCE_NUMBER => v_puse_dup_rec.PEE_SEQUENCE_NUMBER,
4230 X_UNIT_SET_CD => v_puse_dup_rec.UNIT_SET_CD,
4231 X_US_VERSION_NUMBER => v_puse_dup_rec.US_VERSION_NUMBER,
4232 X_PUSE_START_DT => v_puse_dup_rec.PUSE_START_DT,
4233 X_EXPIRY_DT => v_EXPIRY_DT,
4234 X_MODE => 'R'
4235 );
4236
4237 CLOSE c_puse_dup;
4238 ELSE
4239 CLOSE c_puse_dup;
4240 DECLARE
4241 lv_rowid VARCHAR2(25);
4242 BEGIN
4243 igs_pe_unt_set_excl_pkg.INSERT_ROW (
4244 X_ROWID =>lv_rowid,
4245 X_PERSON_ID =>p_person_id,
4246 X_ENCUMBRANCE_TYPE=> v_pee_rec.encumbrance_type,
4247 X_PEN_START_DT=> v_pee_rec.pen_start_dt,
4248 X_S_ENCMB_EFFECT_TYPE =>v_pee_rec.s_encmb_effect_type,
4249 X_PEE_START_DT =>v_pee_rec.pee_start_dt,
4250 X_PEE_SEQUENCE_NUMBER =>v_pee_rec.sequence_number,
4251 X_UNIT_SET_CD =>v_spus_table(v_index1).unit_set_cd,
4252 X_US_VERSION_NUMBER =>v_spus_table(v_index1).version_number,
4253 X_PUSE_START_DT =>gcst_sysdatetime, -- GJHA Changed it from gcst_sysdate
4254 X_EXPIRY_DT =>v_expiry_dt,
4255 X_MODE =>'R'
4256 );
4257 END;
4258 END IF;
4259 END IF;
4260 END LOOP;
4261 ELSIF v_pee_rec.s_encmb_effect_type = cst_exc_crs_u THEN
4262 -- Add spu.unit_cd's TO spu PL/SQL TABLE
4263 v_spu_index := 0;
4264 FOR v_spu_rec IN c_spu (cst_excluded) LOOP
4265 v_spu_index := v_spu_index + 1;
4266 v_spu_table(v_spu_index) := v_spu_rec.unit_cd;
4267 END LOOP; -- c_spu
4268 v_pue_index := 0;
4269 FOR v_pue_rec IN c_pue (
4270 v_pee_rec.encumbrance_type,
4271 v_pee_rec.pen_start_dt,
4272 v_pee_rec.s_encmb_effect_type,
4273 v_pee_rec.pee_start_dt,
4274 v_pee_rec.sequence_number) LOOP
4275 -- Add pue.unit_cd to pue PL/SQL table
4276 v_pue_index := v_pue_index + 1;
4277 v_pue_table(v_pue_index) := v_pue_rec.unit_cd;
4278 -- Check if pue.unit_cd in spu PL/SQL table
4279 v_unit_cd_found := FALSE;
4280 FOR v_index1 IN 1..v_spu_index LOOP
4281 IF v_spu_table(v_index1) = v_pue_rec.unit_cd THEN
4282 v_unit_cd_found := TRUE;
4283 EXIT;
4284 END IF;
4285 END LOOP;
4286 IF NOT v_unit_cd_found THEN
4287 igs_pe_pers_unt_excl_pkg.UPDATE_ROW(
4288 X_ROWID => v_pue_rec.ROWID,
4289 X_PERSON_ID => v_pue_rec.PERSON_ID,
4290 X_ENCUMBRANCE_TYPE => v_pue_rec.ENCUMBRANCE_TYPE,
4291 X_PEN_START_DT => v_pue_rec.PEN_START_DT,
4292 X_S_ENCMB_EFFECT_TYPE => v_pue_rec.S_ENCMB_EFFECT_TYPE,
4293 X_PEE_START_DT => v_pue_rec.PEE_START_DT,
4294 X_PEE_SEQUENCE_NUMBER => v_pue_rec.PEE_SEQUENCE_NUMBER,
4295 X_UNIT_CD => v_pue_rec.UNIT_CD,
4296 X_PUE_START_DT => v_pue_rec.PUE_START_DT,
4297 X_EXPIRY_DT => gcst_sysdatetime,
4298 X_MODE => 'R'
4299 );
4300
4301 END IF;
4302 END LOOP; -- c_pue
4303 -- Check if all spu.unit_cd's IN pue PL/SQL TABLE, IF NOT ADD NEW
4304 -- pue record
4305 FOR v_index1 IN 1..v_spu_index LOOP
4306 v_unit_cd_found := FALSE;
4307 FOR v_index2 IN 1..v_pue_index LOOP
4308 IF v_spu_table(v_index1) = v_pue_table(v_index2) THEN
4309 v_unit_cd_found := TRUE;
4310 EXIT;
4311 END IF;
4312 END LOOP;
4313 IF NOT v_unit_cd_found THEN
4314 OPEN c_pue_dup (
4315 v_pee_rec.encumbrance_type,
4316 v_pee_rec.pen_start_dt,
4317 v_pee_rec.s_encmb_effect_type,
4318 v_pee_rec.pee_start_dt,
4319 v_pee_rec.sequence_number,
4320 v_spu_table(v_index1),
4321 gcst_sysdate);
4322 FETCH c_pue_dup INTO v_pue_dup_rec;
4323 IF c_pue_dup%FOUND THEN
4324 igs_pe_pers_unt_excl_pkg.UPDATE_ROW(
4325 X_ROWID => v_pue_dup_rec.ROWID,
4326 X_PERSON_ID => v_pue_dup_rec.PERSON_ID,
4327 X_ENCUMBRANCE_TYPE => v_pue_dup_rec.ENCUMBRANCE_TYPE,
4328 X_PEN_START_DT => v_pue_dup_rec.PEN_START_DT,
4329 X_S_ENCMB_EFFECT_TYPE => v_pue_dup_rec.S_ENCMB_EFFECT_TYPE,
4330 X_PEE_START_DT => v_pue_dup_rec.PEE_START_DT,
4331 X_PEE_SEQUENCE_NUMBER => v_pue_dup_rec.PEE_SEQUENCE_NUMBER,
4332 X_UNIT_CD => v_pue_dup_rec.UNIT_CD,
4333 X_PUE_START_DT => v_pue_dup_rec.PUE_START_DT,
4334 X_EXPIRY_DT => v_expiry_dt,
4335 X_MODE => 'R'
4336 );
4337 CLOSE c_pue_dup;
4338 ELSE
4339 CLOSE c_pue_dup;
4340 DECLARE
4341 LV_ROWID VARCHAR2(25);
4342 BEGIN
4343 igs_pe_pers_unt_excl_PKG.INSERT_ROW (
4344 X_ROWID =>LV_ROWID,
4345 X_PERSON_ID =>p_person_id,
4346 X_ENCUMBRANCE_TYPE =>v_pee_rec.encumbrance_type,
4347 X_PEN_START_DT =>v_pee_rec.pen_start_dt,
4348 X_S_ENCMB_EFFECT_TYPE =>v_pee_rec.s_encmb_effect_type,
4349 X_PEE_START_DT =>v_pee_rec.pee_start_dt,
4350 X_PEE_SEQUENCE_NUMBER =>v_pee_rec.sequence_number,
4351 X_UNIT_CD =>v_spu_table(v_index1),
4352 X_PUE_START_DT =>gcst_sysdatetime, --GJHA Changed it from gcst_sysdate
4353 X_EXPIRY_DT=>v_expiry_dt,
4354 X_MODE =>'R'
4355 );
4356 END;
4357 END IF;
4358 END IF;
4359 END LOOP;
4360 ELSIF v_pee_rec.s_encmb_effect_type = cst_rqrd_crs_u THEN
4361 -- Add spu.unit_cd's TO spu PL/SQL TABLE
4362 v_spu_index := 0;
4363 FOR v_spu_rec IN c_spu (cst_required) LOOP
4364 v_spu_index := v_spu_index + 1;
4365 v_spu_table(v_spu_index) := v_spu_rec.unit_cd;
4366 END LOOP; -- c_spu
4367 v_pur_index := 0;
4368 FOR v_pur_rec IN c_pur (
4369 v_pee_rec.encumbrance_type,
4370 v_pee_rec.pen_start_dt,
4371 v_pee_rec.s_encmb_effect_type,
4372 v_pee_rec.pee_start_dt,
4373 v_pee_rec.sequence_number) LOOP
4374 -- Add pur.unit_cd to pur PL/SQL table
4375 v_pur_index := v_pur_index + 1;
4376 v_pur_table(v_pur_index) := v_pur_rec.unit_cd;
4377 -- Check if pur.unit_cd in spu PL/SQL table
4378 v_unit_cd_found := FALSE;
4379 FOR v_index1 IN 1..v_spu_index LOOP
4380 IF v_spu_table(v_index1) = v_pur_rec.unit_cd THEN
4381 v_unit_cd_found := TRUE;
4382 EXIT;
4383 END IF;
4384 END LOOP;
4385 IF NOT v_unit_cd_found THEN
4386 igs_pe_unt_requirmnt_pkg.UPDATE_ROW(
4387 X_ROWID => v_pur_rec.ROWID,
4388 X_PERSON_ID => v_pur_rec.PERSON_ID,
4389 X_ENCUMBRANCE_TYPE => v_pur_rec.ENCUMBRANCE_TYPE,
4390 X_PEN_START_DT => v_pur_rec.PEN_START_DT,
4391 X_S_ENCMB_EFFECT_TYPE => v_pur_rec.S_ENCMB_EFFECT_TYPE,
4392 X_PEE_START_DT => v_pur_rec.PEE_START_DT,
4393 X_PEE_SEQUENCE_NUMBER => v_pur_rec.PEE_SEQUENCE_NUMBER,
4394 X_UNIT_CD => v_pur_rec.UNIT_CD,
4395 X_PUR_START_DT => v_pur_rec.PUR_START_DT,
4396 X_EXPIRY_DT => gcst_sysdatetime,
4397 X_MODE => 'R'
4398 );
4399 END IF;
4400 END LOOP; -- c_pur
4401 -- Check if all spu.unit_cd's IN pur PL/SQL TABLE, IF NOT ADD NEW
4402 -- pur record
4403 FOR v_index1 IN 1..v_spu_index LOOP
4404 v_unit_cd_found := FALSE;
4405 FOR v_index2 IN 1..v_pur_index LOOP
4406 IF v_spu_table(v_index1) = v_pur_table(v_index2) THEN
4407 v_unit_cd_found := TRUE;
4408 EXIT;
4409 END IF;
4410 END LOOP;
4411 IF NOT v_unit_cd_found THEN
4412 OPEN c_pur_dup (
4413 v_pee_rec.encumbrance_type,
4414 v_pee_rec.pen_start_dt,
4415 v_pee_rec.s_encmb_effect_type,
4416 v_pee_rec.pee_start_dt,
4417 v_pee_rec.sequence_number,
4418 v_spu_table(v_index1),
4419 gcst_sysdate);
4420 FETCH c_pur_dup INTO v_pur_dup_rec;
4421 IF c_pur_dup%FOUND THEN
4422 igs_pe_unt_requirmnt_pkg.UPDATE_ROW(
4423 X_ROWID => v_pur_dup_rec.ROWID,
4424 X_PERSON_ID => v_pur_dup_rec.PERSON_ID,
4425 X_ENCUMBRANCE_TYPE => v_pur_dup_rec.ENCUMBRANCE_TYPE,
4426 X_PEN_START_DT => v_pur_dup_rec.PEN_START_DT,
4427 X_S_ENCMB_EFFECT_TYPE => v_pur_dup_rec.S_ENCMB_EFFECT_TYPE,
4428 X_PEE_START_DT => v_pur_dup_rec.PEE_START_DT,
4429 X_PEE_SEQUENCE_NUMBER => v_pur_dup_rec.PEE_SEQUENCE_NUMBER,
4430 X_UNIT_CD => v_pur_dup_rec.UNIT_CD,
4431 X_PUR_START_DT => v_pur_dup_rec.PUR_START_DT,
4432 X_EXPIRY_DT => v_expiry_dt,
4433 X_MODE => 'R'
4434 );
4435 CLOSE c_pur_dup;
4436 ELSE
4437 CLOSE c_pur_dup;
4438 DECLARE
4439 lv_rowid VARCHAR2(25);
4440 BEGIN
4441 igs_pe_unt_requirmnt_pkg.INSERT_ROW (
4442 X_ROWID =>lv_rowid,
4443 X_PERSON_ID =>p_person_id,
4444 X_ENCUMBRANCE_TYPE=> v_pee_rec.encumbrance_type,
4445 X_PEN_START_DT=> v_pee_rec.pen_start_dt,
4446 X_S_ENCMB_EFFECT_TYPE=> v_pee_rec.s_encmb_effect_type,
4447 X_PEE_START_DT =>v_pee_rec.pee_start_dt,
4448 X_PEE_SEQUENCE_NUMBER =>v_pee_rec.sequence_number,
4449 X_UNIT_CD =>v_spu_table(v_index1),
4450 X_PUR_START_DT =>gcst_sysdatetime, --GJHA Changed it from gcst_sysdate
4451 X_EXPIRY_DT=>v_expiry_dt,
4452 X_MODE =>'R'
4453 );
4454 END;
4455 END IF;
4456 END IF;
4457 END LOOP;
4458 --
4459 -- Start of new code added as per the FA110 PR Enh. Bug# 2658550.
4460 --
4461 ELSIF v_pee_rec.s_encmb_effect_type IN (cst_exc_sp_awd, cst_exc_sp_disb) THEN
4462 -- Add spf.fund_code's TO spf PL/SQL TABLE
4463 v_spf_index := 0;
4464 FOR v_spf_rec IN c_spf LOOP
4465 v_spf_index := v_spf_index + 1;
4466 v_spf_table(v_spf_index) := v_spf_rec.fund_code;
4467 END LOOP; -- c_spf
4468
4469 v_pfe_index := 0;
4470 FOR v_pfe_rec IN c_pfe (v_pee_rec.encumbrance_type,
4471 v_pee_rec.pen_start_dt,
4472 v_pee_rec.s_encmb_effect_type,
4473 v_pee_rec.pee_start_dt,
4474 v_pee_rec.sequence_number) LOOP
4475
4476 -- Add pfe.fund_code to pue PL/SQL table
4477 v_pfe_index := v_pfe_index + 1;
4478 v_pfe_table(v_pfe_index) := v_pfe_rec.fund_code;
4479
4480 -- Check if pfe.fund_code in spf PL/SQL table
4481 v_fund_cd_found := FALSE;
4482
4483 FOR v_index1 IN 1..v_spf_index LOOP
4484 IF v_spf_table(v_index1) = v_pfe_rec.fund_code THEN
4485 v_fund_cd_found := TRUE;
4486 EXIT;
4487 END IF;
4488 END LOOP;
4489 IF NOT v_fund_cd_found THEN
4490 igs_pe_fund_excl_pkg.update_row(
4491 X_ROWID => v_pfe_rec.rowid ,
4492 X_FUND_EXCL_ID => v_pfe_rec.fund_excl_id ,
4493 X_PERSON_ID => v_pfe_rec.person_id ,
4494 X_ENCUMBRANCE_TYPE => v_pfe_rec.encumbrance_type ,
4495 X_PEN_START_DT => v_pfe_rec.pen_start_dt ,
4496 X_S_ENCMB_EFFECT_TYPE => v_pfe_rec.s_encmb_effect_type,
4497 X_PEE_START_DT => v_pfe_rec.pee_start_dt ,
4498 X_PEE_SEQUENCE_NUMBER => v_pfe_rec.pee_sequence_number,
4499 X_FUND_CODE => v_pfe_rec.fund_code ,
4500 X_PFE_START_DT => v_pfe_rec.pfe_start_dt ,
4501 X_EXPIRY_DT => gcst_sysdatetime ,
4502 X_MODE => 'R');
4503 END IF;
4504 END LOOP; -- c_pfe
4505
4506 -- Check if all spf.fund_cd's IN pue PL/SQL TABLE, IF NOT ADD NEW
4507 -- pue record
4508 FOR v_index1 IN 1..v_spf_index LOOP
4509 v_fund_cd_found := FALSE;
4510 FOR v_index2 IN 1..v_pfe_index LOOP
4511 IF v_spf_table(v_index1) = v_pfe_table(v_index2) THEN
4512 v_fund_cd_found := TRUE;
4513 EXIT;
4514 END IF;
4515 END LOOP;
4516 IF NOT v_fund_cd_found THEN
4517 OPEN c_pfe_dup (
4518 v_pee_rec.encumbrance_type,
4519 v_pee_rec.pen_start_dt,
4520 v_pee_rec.s_encmb_effect_type,
4521 v_pee_rec.pee_start_dt,
4522 v_pee_rec.sequence_number,
4523 v_spf_table(v_index1),
4524 gcst_sysdate);
4525 FETCH c_pfe_dup INTO v_pfe_dup_rec;
4526
4527 IF c_pfe_dup%FOUND THEN
4528 igs_pe_fund_excl_pkg.update_row(
4529 X_ROWID => v_pfe_dup_rec.rowid ,
4530 X_FUND_EXCL_ID => v_pfe_dup_rec.fund_excl_id ,
4531 X_PERSON_ID => v_pfe_dup_rec.person_id ,
4532 X_ENCUMBRANCE_TYPE => v_pfe_dup_rec.encumbrance_type ,
4533 X_PEN_START_DT => v_pfe_dup_rec.pen_start_dt ,
4534 X_S_ENCMB_EFFECT_TYPE => v_pfe_dup_rec.s_encmb_effect_type,
4535 X_PEE_START_DT => v_pfe_dup_rec.pee_start_dt ,
4536 X_PEE_SEQUENCE_NUMBER => v_pfe_dup_rec.pee_sequence_number,
4537 X_FUND_CODE => v_pfe_dup_rec.fund_code ,
4538 X_PFE_START_DT => v_pfe_dup_rec.pfe_start_dt ,
4539 X_EXPIRY_DT => v_expiry_dt ,
4540 X_MODE => 'R');
4541 CLOSE c_pue_dup;
4542 ELSE
4543 CLOSE c_pfe_dup;
4544 DECLARE
4545 l_rowid VARCHAR2(25);
4546 l_fund_excl_id igs_pe_fund_excl.fund_excl_id%TYPE;
4547 BEGIN
4548 igs_pe_fund_excl_pkg.insert_row (
4549 X_ROWID => l_rowid,
4550 X_FUND_EXCL_ID => l_fund_excl_id,
4551 X_PERSON_ID => p_person_id,
4552 X_ENCUMBRANCE_TYPE => v_pee_rec.encumbrance_type,
4553 X_PEN_START_DT => v_pee_rec.pee_start_dt,
4554 X_S_ENCMB_EFFECT_TYPE => v_pee_rec.s_encmb_effect_type,
4555 X_PEE_START_DT => v_pee_rec.pee_start_dt,
4556 X_PEE_SEQUENCE_NUMBER => v_pee_rec.sequence_number,
4557 X_FUND_CODE => v_spf_table(v_index1),
4558 X_PFE_START_DT => gcst_sysdatetime,
4559 X_EXPIRY_DT => v_expiry_dt,
4560 X_MODE => 'R' );
4561 END;
4562 END IF;
4563
4564 END IF;
4565 END LOOP;
4566 --
4567 -- End of new code added as per the FA110 PR Enh. Bug# 2658550.
4568 --
4569 END IF;
4570 END LOOP; -- c_pee
4571 ELSE -- PEN doesn't exist ; NEW encumbrance.
4572 -- Get the expiry date of the encumbrance
4573
4574 IF v_spo_rec.expiry_dt <= TRUNC(SYSDATE) THEN
4575 v_expiry_status := IGS_PR_get_spo_expiry (
4576 p_person_id,
4577 p_course_cd,
4578 p_spo_sequence_number,
4579 v_spo_rec.expiry_dt,
4580 v_expiry_dt);
4581
4582 ELSE
4583 -- Don't pass the spo.expiry_dt forcing re-derivation ; this will check for
4584 -- differences.
4585 v_expiry_status := IGS_PR_get_spo_expiry (
4586 p_person_id,
4587 p_course_cd,
4588 p_spo_sequence_number,
4589 NULL,
4590 v_expiry_dt);
4591
4592 END IF;
4593 -- Cannot expire retrospectively
4594 IF v_expiry_dt < gcst_sysdate THEN
4595 RETURN TRUE;
4596 END IF;
4597
4598 IF NOT IGS_PR_GEN_006.IGS_PR_upd_pen_clash (
4599 p_person_id,
4600 p_course_cd,
4601 p_spo_sequence_number,
4602 p_application_type,
4603 v_message_text,
4604 v_message_level) THEN
4605 ROLLBACK TO sp_before_update;
4606 p_message_text := v_message_text;
4607 p_message_level := v_message_level;
4608 RETURN FALSE;
4609 ELSIF v_message_level IS NOT NULL THEN
4610 -- Set the message level only if not already set, or the new
4611 -- value is expiry
4612 IF p_message_level IS NULL OR
4613 v_message_level = cst_expired THEN
4614 p_message_text := v_message_text;
4615 p_message_level := v_message_level;
4616 END IF;
4617 END IF;
4618 -- Insert new encumbrance details
4619 lv_spo_sequence_number:= p_spo_sequence_number;
4620 DECLARE
4621 lv_rowid VARCHAR2(25);
4622 BEGIN
4623
4624 IGS_PE_PERS_ENCUMB_pkg.INSERT_ROW (
4625 X_ROWID =>lv_rowid,
4626 X_PERSON_ID =>p_person_id,
4627 X_ENCUMBRANCE_TYPE =>v_spo_rec.encumbrance_type,
4628 X_START_DT=> gcst_sysdatetime, --gjha Chaged from gcst_sysdatetime -- Modified by Prajeesh to sysdatetime
4629 X_EXPIRY_DT =>v_expiry_dt,
4630 X_AUTHORISING_PERSON_ID=> v_authorising_person_id,
4631 X_COMMENTS =>NULL,
4632 X_SPO_COURSE_CD => p_course_cd,
4633 X_SPO_SEQUENCE_NUMBER => lv_spo_sequence_number,
4634 x_auth_resp_id => NULL,
4635 x_external_reference => NULL, -- ssawhney, should always be NULL when created from internal system
4636 X_MODE =>'R'
4637 );
4638
4639 END;
4640 -- Loop through edte applicable records (from PL/SQL table)
4641 FOR v_index1 IN 1..v_etde_index LOOP
4642 OPEN c_pee_seq_num;
4643 FETCH c_pee_seq_num INTO v_pee_sequence_number;
4644 CLOSE c_pee_seq_num;
4645 OPEN c_seet (v_etde_table(v_index1).s_encmb_effect_type);
4646 FETCH c_seet INTO v_apply_to_course_ind;
4647 CLOSE c_seet;
4648 IF v_apply_to_course_ind = 'Y' THEN
4649 v_apply_course_cd := p_course_cd;
4650 ELSE
4651 v_apply_course_cd := NULL;
4652 END IF;
4653 IF v_etde_table(v_index1).s_encmb_effect_type <> cst_rstr_at_ty OR
4654 NVL(v_spo_rec.closed_ind,'N') = 'N' THEN
4655 DECLARE
4656 CURSOR c_decode1 IS
4657 SELECT DECODE( v_etde_table(v_index1).s_encmb_effect_type,
4658 cst_rstr_ge_cp, v_spo_rec.restricted_enrolment_cp,
4659 cst_rstr_le_cp, v_spo_rec.restricted_enrolment_cp,
4660 NULL) FROM DUAL;
4661
4662 CURSOR c_decode2 IS
4663 SELECT DECODE( v_etde_table(v_index1).s_encmb_effect_type,
4664 cst_rstr_at_ty, v_spo_rec.restricted_attendance_type,
4665 NULL) FROM DUAL;
4666 lv_rowid VARCHAR2(25);
4667 BEGIN
4668
4669 OPEN c_decode1;
4670 FETCH c_decode1 INTO v_decode_val1 ;
4671 CLOSE c_decode1;
4672
4673 OPEN c_decode2;
4674 FETCH c_decode2 INTO v_decode_val2 ;
4675 CLOSE c_decode2;
4676
4677 IGS_PE_PERSENC_EFFCT_pkg.INSERT_ROW (
4678 X_ROWID =>lv_rowid,
4679 X_PERSON_ID =>p_person_id,
4680 X_ENCUMBRANCE_TYPE =>v_spo_rec.encumbrance_type,
4681 X_PEN_START_DT=> gcst_sysdatetime, --gjha Changed it from gcst_sysdatetime --Modified by Prajeesh to sysdatetime
4682 X_S_ENCMB_EFFECT_TYPE=> v_etde_table(v_index1).s_encmb_effect_type,
4683 X_PEE_START_DT=> gcst_sysdatetime, --gjha Changed it from gcst_sysdate
4684 X_SEQUENCE_NUMBER =>v_pee_sequence_number,
4685 X_EXPIRY_DT=> v_expiry_dt,
4686 X_COURSE_CD =>v_apply_course_cd,
4687 X_RESTRICTED_ENROLMENT_CP =>v_decode_val1,
4688 X_RESTRICTED_ATTENDANCE_TYPE =>v_decode_val2,
4689 X_MODE =>'R'
4690 );
4691
4692
4693 END;
4694 END IF;
4695 IF v_etde_table(v_index1).s_encmb_effect_type IN (
4696 cst_sus_course,
4697 cst_exc_course) THEN
4698 FOR v_spc_rec IN c_spc LOOP
4699 DECLARE
4700 lv_rowid VARCHAR2(25);
4701 BEGIN
4702
4703 igs_pe_course_excl_pkg.INSERT_ROW (
4704 X_ROWID =>lv_rowid,
4705 X_PERSON_ID =>p_person_id,
4706 X_ENCUMBRANCE_TYPE =>v_spo_rec.encumbrance_type,
4707 X_PEN_START_DT =>gcst_sysdatetime, --gjha Changed it from datetime --Modified by Prajeesh to sysdatetime
4708 X_S_ENCMB_EFFECT_TYPE =>v_etde_table(v_index1).s_encmb_effect_type,
4709 X_PEE_START_DT =>gcst_sysdatetime,
4710 X_PEE_SEQUENCE_NUMBER =>v_pee_sequence_number,
4711 X_COURSE_CD =>v_spc_rec.course_cd,
4712 X_PCE_START_DT =>gcst_sysdatetime, --GJHA Changed it from gcst_sysdate
4713 X_EXPIRY_DT =>v_expiry_dt,
4714 X_MODE =>'R'
4715 );
4716 END;
4717
4718 END LOOP; -- c_spc
4719 ELSIF v_etde_table(v_index1).s_encmb_effect_type = cst_exc_crs_gp THEN
4720 DECLARE
4721 lv_rowid VARCHAR2(25);
4722 BEGIN
4723 igs_pe_crs_grp_excl_pkg.INSERT_ROW (
4724 X_ROWID =>lv_rowid,
4725 X_PERSON_ID =>p_person_id,
4726 X_ENCUMBRANCE_TYPE=> v_spo_rec.encumbrance_type,
4727 X_PEN_START_DT =>gcst_sysdatetime, --gjha Changed it from sysdatetime --Modified by Prajeesh to sysdatetime
4728 X_S_ENCMB_EFFECT_TYPE =>v_etde_table(v_index1).s_encmb_effect_type,
4729 X_PEE_START_DT =>gcst_sysdatetime,
4730 X_PEE_SEQUENCE_NUMBER =>v_pee_sequence_number,
4731 X_COURSE_GROUP_CD =>v_spo_rec.encmb_course_group_cd,
4732 X_PCGE_START_DT=> gcst_sysdatetime, --GJHA Changed it from gcst_sysdate
4733 X_EXPIRY_DT =>v_expiry_dt,
4734 X_MODE =>'R'
4735 );
4736 END;
4737 ELSIF v_etde_table(v_index1).s_encmb_effect_type = cst_exc_crs_us THEN
4738 FOR v_spus_rec IN c_spus LOOP
4739 DECLARE
4740 lv_rowid VARCHAR2(25);
4741 BEGIN
4742 igs_pe_unt_set_excl_pkg.INSERT_ROW (
4743 X_ROWID =>lv_rowid,
4744 X_PERSON_ID =>p_person_id,
4745 X_ENCUMBRANCE_TYPE=> v_spo_rec.encumbrance_type,
4746 X_PEN_START_DT=> gcst_sysdatetime, --gjha Changed it from sysdatetime --modified by Prajeesh
4747 X_S_ENCMB_EFFECT_TYPE =>v_etde_table(v_index1).s_encmb_effect_type,
4748 X_PEE_START_DT =>gcst_sysdatetime,
4749 X_PEE_SEQUENCE_NUMBER =>v_pee_sequence_number,
4750 X_UNIT_SET_CD =>v_spus_rec.unit_set_cd,
4751 X_US_VERSION_NUMBER =>v_spus_rec.version_number,
4752 X_PUSE_START_DT =>gcst_sysdatetime, --GJHA Changed it from gcst_sysdate
4753 X_EXPIRY_DT =>v_expiry_dt,
4754 X_MODE =>'R'
4755 );
4756 END;
4757 END LOOP; -- c_spus
4758 ELSIF v_etde_table(v_index1).s_encmb_effect_type = cst_exc_crs_u THEN
4759 FOR v_spu_rec IN c_spu (cst_excluded) LOOP
4760 DECLARE
4761 LV_ROWID VARCHAR2(25);
4762 BEGIN
4763 igs_pe_pers_unt_excl_PKG.INSERT_ROW (
4764 X_ROWID =>LV_ROWID,
4765 X_PERSON_ID =>p_person_id,
4766 X_ENCUMBRANCE_TYPE =>v_spo_rec.encumbrance_type,
4767 X_PEN_START_DT =>gcst_sysdatetime, --gjha Changed it from sysdatetime --Modified by Prajeesh
4768 X_S_ENCMB_EFFECT_TYPE =>v_etde_table(v_index1).s_encmb_effect_type,
4769 X_PEE_START_DT =>gcst_sysdatetime,
4770 X_PEE_SEQUENCE_NUMBER =>v_pee_sequence_number,
4771 X_UNIT_CD =>v_spu_rec.unit_cd,
4772 X_PUE_START_DT => gcst_sysdatetime, -- gjha Changed it from v_spu_table(v_index1),
4773 X_EXPIRY_DT=>v_expiry_dt, --Gjha Changed it from gcst_sysdate,
4774 X_MODE =>'R'
4775 );
4776 END;
4777 END LOOP; -- c_spu
4778 ELSIF v_etde_table(v_index1).s_encmb_effect_type = cst_rqrd_crs_u THEN
4779 FOR v_spu_rec IN c_spu (cst_required) LOOP
4780 DECLARE
4781 lv_rowid VARCHAR2(25);
4782 BEGIN
4783 igs_pe_unt_requirmnt_pkg.INSERT_ROW (
4784 X_ROWID =>lv_rowid,
4785 X_PERSON_ID =>p_person_id,
4786 X_ENCUMBRANCE_TYPE=> v_spo_rec.encumbrance_type,
4787 X_PEN_START_DT=> gcst_sysdatetime, --gjha Changed it from gcst_sysdatetime --Modified by Prajeesh
4788 X_S_ENCMB_EFFECT_TYPE=> v_etde_table(v_index1).s_encmb_effect_type,
4789 X_PEE_START_DT =>gcst_sysdatetime,
4790 X_PEE_SEQUENCE_NUMBER =>v_pee_sequence_number,
4791 X_UNIT_CD =>v_spu_rec.unit_cd,
4792 X_PUR_START_DT =>gcst_sysdatetime,
4793 X_EXPIRY_DT=>v_expiry_dt,
4794 X_MODE =>'R'
4795 );
4796 END;
4797 END LOOP; -- c_spu
4798 --
4799 -- Start of new code added as per the FA110 PR Enh. Bug# 2658550.
4800 --
4801 ELSIF v_etde_table(v_index1).s_encmb_effect_type IN (cst_exc_sp_awd, cst_exc_sp_disb)THEN
4802 FOR v_spf_rec IN c_spf LOOP
4803 DECLARE
4804 l_rowid VARCHAR2(25);
4805 l_fund_excl_id igs_pe_fund_excl.fund_excl_id%TYPE;
4806 BEGIN
4807 igs_pe_fund_excl_pkg.insert_row (
4808 X_ROWID => l_rowid,
4809 X_FUND_EXCL_ID => l_fund_excl_id,
4810 X_PERSON_ID => p_person_id,
4811 X_ENCUMBRANCE_TYPE => v_spo_rec.encumbrance_type,
4812 X_PEN_START_DT => gcst_sysdatetime,
4813 X_S_ENCMB_EFFECT_TYPE => v_etde_table(v_index1).s_encmb_effect_type,
4814 X_PEE_START_DT => gcst_sysdatetime,
4815 X_PEE_SEQUENCE_NUMBER => v_pee_sequence_number,
4816 X_FUND_CODE => v_spf_rec.fund_code,
4817 X_PFE_START_DT => gcst_sysdatetime,
4818 X_EXPIRY_DT => v_expiry_dt,
4819 X_MODE => 'R' );
4820 END;
4821 END LOOP; -- c_spf
4822 --
4823 -- End of new code added as per the FA110 PR Enh. Bug# 2658550.
4824 --
4825 END IF;
4826 END LOOP; -- PL/SQL table
4827 END IF;
4828 END IF;
4829
4830 RETURN TRUE;
4831 EXCEPTION
4832 WHEN e_record_locked THEN
4833 IF c_spo%ISOPEN THEN
4834 CLOSE c_spo;
4835 END IF;
4836 IF c_pe%ISOPEN THEN
4837 CLOSE c_pe;
4838 END IF;
4839 IF c_pen%ISOPEN THEN
4840 CLOSE c_pen;
4841 END IF;
4842 IF c_etde%ISOPEN THEN
4843 CLOSE c_etde;
4844 END IF;
4845 IF c_pce%ISOPEN THEN
4846 CLOSE c_pce;
4847 END IF;
4848 IF c_pce_dup%ISOPEN THEN
4849 CLOSE c_pce_dup;
4850 END IF;
4851 IF c_spc%ISOPEN THEN
4852 CLOSE c_spc;
4853 END IF;
4854 IF c_pcge%ISOPEN THEN
4855 CLOSE c_pcge;
4856 END IF;
4857 IF c_pcge_dup%ISOPEN THEN
4858 CLOSE c_pcge_dup;
4859 END IF;
4860 IF c_puse%ISOPEN THEN
4861 CLOSE c_puse;
4862 END IF;
4863 IF c_puse_dup%ISOPEN THEN
4864 CLOSE c_puse_dup;
4865 END IF;
4866 IF c_spus%ISOPEN THEN
4867 CLOSE c_spus;
4868 END IF;
4869 IF c_pue%ISOPEN THEN
4870 CLOSE c_pue;
4871 END IF;
4872 IF c_pue_dup%ISOPEN THEN
4873 CLOSE c_pue_dup;
4874 END IF;
4875 IF c_pur%ISOPEN THEN
4876 CLOSE c_pur;
4877 END IF;
4878 IF c_pur_dup%ISOPEN THEN
4879 CLOSE c_pur_dup;
4880 END IF;
4881 IF c_spu%ISOPEN THEN
4882 CLOSE c_spu;
4883 END IF;
4884 IF c_pee%ISOPEN THEN
4885 CLOSE c_pee;
4886 END IF;
4887 IF c_seet%ISOPEN THEN
4888 CLOSE c_seet;
4889 END IF;
4890 IF c_pfe%ISOPEN THEN
4891 CLOSE c_pfe;
4892 END IF;
4893 IF c_pfe_dup %ISOPEN THEN
4894 CLOSE c_pfe_dup;
4895 END IF;
4896 IF c_spf%ISOPEN THEN
4897 CLOSE c_spf;
4898 END IF;
4899
4900 ROLLBACK TO sp_before_update;
4901 p_message_level := cst_error;
4902 RETURN FALSE;
4903 WHEN OTHERS THEN
4904 IF c_spo%ISOPEN THEN
4905 CLOSE c_spo;
4906 END IF;
4907 IF c_pe%ISOPEN THEN
4908 CLOSE c_pe;
4909 END IF;
4910 IF c_pen%ISOPEN THEN
4911 CLOSE c_pen;
4912 END IF;
4913 IF c_etde%ISOPEN THEN
4914 CLOSE c_etde;
4915 END IF;
4916 IF c_pce%ISOPEN THEN
4917 CLOSE c_pce;
4918 END IF;
4919 IF c_pce_dup%ISOPEN THEN
4920 CLOSE c_pce_dup;
4921 END IF;
4922 IF c_spc%ISOPEN THEN
4923 CLOSE c_spc;
4924 END IF;
4925 IF c_pcge%ISOPEN THEN
4926 CLOSE c_pcge;
4927 END IF;
4928 IF c_pcge_dup%ISOPEN THEN
4929 CLOSE c_pcge_dup;
4930 END IF;
4931 IF c_puse%ISOPEN THEN
4932 CLOSE c_puse;
4933 END IF;
4934 IF c_puse_dup%ISOPEN THEN
4935 CLOSE c_puse_dup;
4936 END IF;
4937 IF c_spus%ISOPEN THEN
4938 CLOSE c_spus;
4939 END IF;
4940 IF c_pue%ISOPEN THEN
4941 CLOSE c_pue;
4942 END IF;
4943 IF c_pue_dup%ISOPEN THEN
4944 CLOSE c_pue_dup;
4945 END IF;
4946 IF c_pur%ISOPEN THEN
4947 CLOSE c_pur;
4948 END IF;
4949 IF c_pur_dup%ISOPEN THEN
4950 CLOSE c_pur_dup;
4951 END IF;
4952 IF c_spu%ISOPEN THEN
4953 CLOSE c_spu;
4954 END IF;
4955 IF c_pee%ISOPEN THEN
4956 CLOSE c_pee;
4957 END IF;
4958 IF c_seet%ISOPEN THEN
4959 CLOSE c_seet;
4960 END IF;
4961 IF c_pfe%ISOPEN THEN
4962 CLOSE c_pfe;
4963 END IF;
4964 IF c_pfe_dup %ISOPEN THEN
4965 CLOSE c_pfe_dup;
4966 END IF;
4967 IF c_spf%ISOPEN THEN
4968 CLOSE c_spf;
4969 END IF;
4970 IF c_pee_seq_num%ISOPEN THEN
4971 CLOSE c_pee_seq_num;
4972 END IF;
4973 RAISE;
4974 END;
4975 EXCEPTION
4976 WHEN OTHERS THEN
4977 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
4978 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.IGS_PR_UPD_SPO_PEN');
4979 IGS_GE_MSG_STACK.ADD;
4980 App_Exception.Raise_Exception;
4981 END igs_pr_upd_spo_pen;
4982
4983 FUNCTION igs_pr_get_spo_expiry(
4984
4985 p_person_id IN NUMBER ,
4986
4987 p_course_cd IN VARCHAR2 ,
4988
4989 p_sequence_number IN NUMBER ,
4990
4991 p_spo_expiry_dt IN DATE )
4992
4993 RETURN VARCHAR2 IS
4994
4995 gv_other_detail VARCHAR2(255);
4996
4997 BEGIN -- IGS_PR_get_spo_expiry
4998
4999 DECLARE
5000
5001 v_expiry_dt DATE;
5002
5003 v_return_val VARCHAR2(10);
5004
5005 BEGIN
5006
5007
5008 v_return_val := IGS_PR_GET_SPO_EXPIRY(
5009 p_person_id,
5010 p_course_cd,
5011 p_sequence_number,
5012 p_spo_expiry_dt,
5013 v_expiry_dt);
5014 RETURN v_return_val;
5015 END;
5016
5017 EXCEPTION
5018 WHEN OTHERS THEN
5019 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
5020 FND_MESSAGE.SET_TOKEN('NAME','IGS_PR_GEN_006.IGS_PR_GET_SPO_EXPIRY');
5021 IGS_GE_MSG_STACK.ADD;
5022 App_Exception.Raise_Exception;
5023 END igs_pr_get_spo_expiry;
5024
5025 FUNCTION get_antcp_compl_dt(
5026 p_person_id igs_en_stdnt_ps_att_all.person_id%TYPE,
5027 p_course_cd igs_en_stdnt_ps_att_all.course_cd%TYPE
5028 )
5029 RETURN DATE
5030 IS
5031 -- function to get enrollment estimated completion date
5032 l_date DATE;
5033 l_message_name VARCHAR2(400);
5034
5035 BEGIN
5036 l_date := NULL;
5037 l_date := igs_en_gen_015.enrf_drv_cmpl_dt(
5038 p_person_id => p_person_id,
5039 p_course_cd => p_course_cd,
5040 p_achieved_cp => NULL,
5041 p_attendance_type => NULL,
5042 p_load_cal_type => NULL,
5043 p_load_ci_seq_num => NULL,
5044 p_load_ci_alt_code => NULL,
5045 p_load_ci_start_dt => NULL,
5046 p_load_ci_end_dt => NULL,
5047 p_message_name => l_message_name
5048 );
5049
5050 RETURN l_date;
5051
5052 EXCEPTION
5053 WHEN OTHERS THEN
5054 RETURN NULL;
5055
5056 END get_antcp_compl_dt;
5057
5058 END IGS_PR_GEN_006;