[Home] [Help]
PACKAGE BODY: APPS.IGS_PR_GEN_003
Source
1 PACKAGE BODY IGS_PR_GEN_003 AS
2 /* $Header: IGSPR24B.pls 115.13 2004/01/08 14:05:53 kdande ship $ */
3 PROCEDURE IGS_PR_GET_CAL_PARM(
4 p_course_cd IN VARCHAR2 ,
5 p_version_number IN NUMBER ,
6 p_prg_cal_type IN VARCHAR2 ,
7 p_level OUT NOCOPY VARCHAR2 ,
8 p_org_unit_cd OUT NOCOPY VARCHAR2 ,
9 p_ou_start_dt OUT NOCOPY DATE ,
10 p_stream_number OUT NOCOPY NUMBER ,
11 p_show_cause_length OUT NOCOPY NUMBER ,
12 p_appeal_length OUT NOCOPY NUMBER )
13 IS
14 gv_other_detail VARCHAR2(255);
15 BEGIN -- IGS_PR_GET_CAL_PARM
16 -- Get the configuration parameters for a calendar type from the relevant
17 -- level of the configuration structure. This routine also checks the level
18 -- at which the parameters are defined by calling another routine.
19 DECLARE
20 cst_course CONSTANT VARCHAR2(10) := 'COURSE';
21 cst_ou CONSTANT VARCHAR2(10) := 'OU';
22 v_basic_level VARCHAR2(10);
23 v_calendar_level VARCHAR2(10);
24 v_org_unit_cd IGS_OR_UNIT.org_unit_cd%TYPE;
25 v_ou_start_dt IGS_OR_UNIT.start_dt%TYPE;
26 v_stream_number IGS_PR_S_PRG_CAL.stream_num%TYPE;
27 v_show_cause_length IGS_PR_S_PRG_CAL.show_cause_length%TYPE;
28 v_appeal_length IGS_PR_S_PRG_CAL.appeal_length%TYPE;
29 CURSOR c_scpca IS
30 SELECT scpca.stream_num,
31 scpca.show_cause_length,
32 scpca.appeal_length
33 FROM IGS_PR_S_CRV_PRG_CAL scpca
34 WHERE scpca.course_cd = p_course_cd AND
35 scpca.version_number = p_version_number AND
36 scpca.prg_cal_type = p_prg_cal_type;
37 CURSOR c_sopca (
38 cp_org_unit_cd IGS_OR_UNIT.org_unit_cd%TYPE,
39 cp_ou_start_dt IGS_OR_UNIT.start_dt%TYPE) IS
40 SELECT sopca.stream_num,
41 sopca.show_cause_length,
42 sopca.appeal_length
43 FROM IGS_PR_S_OU_PRG_CAL sopca
44 WHERE sopca.org_unit_cd = cp_org_unit_cd AND
45 sopca.ou_start_dt = cp_ou_start_dt AND
46 sopca.prg_cal_type = p_prg_cal_type;
47 CURSOR c_spca IS
48 SELECT spca.stream_num,
49 spca.show_cause_length,
50 spca.appeal_length
51 FROM IGS_PR_S_PRG_CAL spca
52 WHERE spca.s_control_num = 1 AND
53 spca.prg_cal_type = p_prg_cal_type;
54 BEGIN
55 -- Call routine to determine configuration level
56 IGS_PR_GET_CONFIG_LVL (
57 p_course_cd,
58 p_version_number,
59 v_basic_level,
60 v_calendar_level,
61 v_org_unit_cd,
62 v_ou_start_dt);
63 p_level := v_calendar_level;
64 p_org_unit_cd := v_org_unit_cd;
65 p_ou_start_dt := v_ou_start_dt;
66 IF v_calendar_level = cst_course THEN
67 OPEN c_scpca;
68 FETCH c_scpca INTO
69 v_stream_number,
70 v_show_cause_length,
71 v_appeal_length;
72 IF c_scpca%FOUND THEN
73 CLOSE c_scpca;
74 p_stream_number := v_stream_number;
75 p_show_cause_length := v_show_cause_length;
76 p_appeal_length := v_appeal_length;
77 RETURN;
78 END IF;
79 CLOSE c_scpca;
80 p_level := NULL;
81 RETURN;
82 ELSIF v_calendar_level = cst_ou THEN
83 OPEN c_sopca (
84 v_org_unit_cd,
85 v_ou_start_dt);
86 FETCH c_sopca INTO
87 v_stream_number,
88 v_show_cause_length,
89 v_appeal_length;
90 IF c_sopca%FOUND THEN
91 CLOSE c_sopca;
92 p_stream_number := v_stream_number;
93 p_show_cause_length := v_show_cause_length;
94 p_appeal_length := v_appeal_length;
95 RETURN;
96 END IF;
97 CLOSE c_sopca;
98 p_level := NULL;
99 RETURN;
100 ELSE
101 OPEN c_spca;
102 FETCH c_spca INTO
103 v_stream_number,
104 v_show_cause_length,
105 v_appeal_length;
106 IF c_spca%FOUND THEN
107 CLOSE c_spca;
108 p_stream_number := v_stream_number;
109 p_show_cause_length := v_show_cause_length;
110 p_appeal_length := v_appeal_length;
111 RETURN;
112 END IF;
113 CLOSE c_spca;
114 p_level := NULL;
115 RETURN;
116 END IF;
117 EXCEPTION
118 WHEN OTHERS THEN
119 IF c_scpca%ISOPEN THEN
120 CLOSE c_scpca;
121 END IF;
122 IF c_sopca%ISOPEN THEN
123 CLOSE c_sopca;
124 END IF;
125 IF c_spca%ISOPEN THEN
126 CLOSE c_spca;
127 END IF;
128 RAISE;
129 END;
130 EXCEPTION
131 WHEN OTHERS THEN
132
133 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
134 IGS_GE_MSG_STACK.ADD;
135 App_Exception.Raise_Exception;
136 END IGS_PR_GET_CAL_PARM;
137 PROCEDURE IGS_PR_GET_CONFIG_LVL(
138 p_course_cd IN VARCHAR2 ,
139 p_version_number IN NUMBER ,
140 p_basic_level OUT NOCOPY VARCHAR2 ,
141 p_calendar_level OUT NOCOPY VARCHAR2 ,
142 p_org_unit_cd OUT NOCOPY VARCHAR2 ,
143 p_ou_start_dt OUT NOCOPY DATE )
144 IS
145 gv_other_detail VARCHAR2(255);
146 BEGIN -- IGS_PR_GET_CONFIG_LVL
147 -- Get the system configuration level that applies to a nominated course
148 -- version.
149 -- Determines at what level the configuration parameters have been specified
150 -- for a nominated course version. Looks for both base configuration
151 -- parameters and calendar configuration parameters.
152 -- If the level is organisational unit, then the org unit code is also
153 -- returned in OUT NOCOPY parameters.
154 DECLARE
155 cst_course CONSTANT VARCHAR2(10) := 'COURSE';
156 cst_system CONSTANT VARCHAR2(10) := 'SYSTEM';
157 cst_ou CONSTANT VARCHAR2(10) := 'OU';
158 v_org_unit_cd IGS_PR_S_OU_PRG_CONF.org_unit_cd%TYPE;
159 v_ou_start_dt IGS_PR_S_OU_PRG_CONF.ou_start_dt%TYPE;
160 v_dummy VARCHAR2(1);
161 CURSOR c_scpc IS
162 SELECT 'X'
163 FROM IGS_PR_S_CRV_PRG_CON scpc
164 WHERE scpc.course_cd = p_course_cd AND
165 scpc.version_number = p_version_number;
166 CURSOR c_scpca IS
167 SELECT 'X'
168 FROM IGS_PR_S_CRV_PRG_CAL scpca
169 WHERE scpca.course_cd = p_course_cd AND
170 scpca.version_number = p_version_number;
171 CURSOR c_sopc IS
172 SELECT sopc.org_unit_cd,
173 sopc.ou_start_dt
174 FROM IGS_PR_S_OU_PRG_CONF sopc
175 WHERE IGS_PR_GEN_001.PRGP_GET_CRV_CMT (
176 p_course_cd,
177 p_version_number,
178 sopc.org_unit_cd,
179 sopc.ou_start_dt) = 'Y';
180 CURSOR c_sopca IS
181 SELECT sopca.org_unit_cd,
182 sopca.ou_start_dt
183 FROM IGS_PR_S_OU_PRG_CAL sopca
184 WHERE IGS_PR_GEN_001.PRGP_GET_CRV_CMT (
185 p_course_cd,
186 p_version_number,
187 sopca.org_unit_cd,
188 sopca.ou_start_dt) = 'Y';
189 BEGIN
190 p_basic_level := NULL;
191 p_calendar_level := NULL;
192 -- Select from within course override structure
193 OPEN c_scpc;
194 FETCH c_scpc INTO v_dummy;
195 IF c_scpc%FOUND THEN
196 CLOSE c_scpc;
197 p_basic_level := cst_course;
198 v_dummy := NULL;
199 OPEN c_scpca;
200 FETCH c_scpca INTO v_dummy;
201 IF c_scpca%FOUND THEN
202 CLOSE c_scpca;
203 p_calendar_level := cst_course;
204 RETURN;
205 END IF;
206 CLOSE c_scpca;
207 ELSE
208 CLOSE c_scpc;
209 END IF;
210 -- Select from within organisation unit structure
211 OPEN c_sopc;
212 FETCH c_sopc INTO
213 v_org_unit_cd,
214 v_ou_start_dt;
215 IF c_sopc%FOUND THEN
216 CLOSE c_sopc;
217 IF p_basic_level IS NULL THEN
218 p_basic_level := cst_ou;
219 p_org_unit_cd := v_org_unit_cd;
220 p_ou_start_dt := v_ou_start_dt;
221 END IF;
222 v_org_unit_cd := NULL;
223 v_ou_start_dt := NULL;
224 OPEN c_sopca;
225 FETCH c_sopca INTO
226 v_org_unit_cd,
227 v_ou_start_dt;
228 IF c_sopca%FOUND THEN
229 CLOSE c_sopca;
230 p_calendar_level := cst_ou;
231 p_org_unit_cd := v_org_unit_cd;
232 p_ou_start_dt := v_ou_start_dt;
233 RETURN;
234 END IF;
235 CLOSE c_sopca;
236 ELSE
237 CLOSE c_sopc;
238 END IF;
239 IF p_basic_level IS NULL THEN
240 p_basic_level := cst_system;
241 END IF;
242 p_calendar_level := cst_system;
243 EXCEPTION
244 WHEN OTHERS THEN
245 IF c_scpc%ISOPEN THEN
246 CLOSE c_scpc;
247 END IF;
248 IF c_scpca%ISOPEN THEN
249 CLOSE c_scpca;
250 END IF;
251 IF c_sopc%ISOPEN THEN
252 CLOSE c_sopc;
253 END IF;
254 IF c_sopca%ISOPEN THEN
255 CLOSE c_sopca;
256 END IF;
257 RAISE;
258 END;
259 EXCEPTION
260 WHEN OTHERS THEN
261
262 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
263 IGS_GE_MSG_STACK.ADD;
264 App_Exception.Raise_Exception;
265 END IGS_PR_GET_CONFIG_LVL;
266 PROCEDURE IGS_PR_GET_CONFIG_PARM(
267 p_course_cd IN VARCHAR2 ,
268 p_version_number IN NUMBER ,
269 p_apply_start_dt_alias OUT NOCOPY VARCHAR2 ,
270 p_apply_end_dt_alias OUT NOCOPY VARCHAR2 ,
271 p_end_benefit_dt_alias OUT NOCOPY VARCHAR2 ,
272 p_end_penalty_dt_alias OUT NOCOPY VARCHAR2 ,
273 p_show_cause_cutoff_dt_alias OUT NOCOPY VARCHAR2 ,
274 p_appeal_cutoff_dt_alias OUT NOCOPY VARCHAR2 ,
275 p_show_cause_ind OUT NOCOPY VARCHAR2 ,
276 p_apply_before_show_ind OUT NOCOPY VARCHAR2 ,
277 p_appeal_ind OUT NOCOPY VARCHAR2 ,
278 p_apply_before_appeal_ind OUT NOCOPY VARCHAR2 ,
279 p_count_sus_in_time_ind OUT NOCOPY VARCHAR2 ,
280 p_count_exc_in_time_ind OUT NOCOPY VARCHAR2 ,
281 p_calculate_wam_ind OUT NOCOPY VARCHAR2 ,
282 p_calculate_gpa_ind OUT NOCOPY VARCHAR2 ,
283 p_outcome_check_type OUT NOCOPY VARCHAR2 )
284 IS
285 gv_other_detail VARCHAR2(255);
286 BEGIN -- IGS_PR_GET_CONFIG_PARM
287 -- Get the configuration parameters applicable to a course version.
288 DECLARE
289 cst_course CONSTANT VARCHAR2(10) := 'COURSE';
290 cst_system CONSTANT VARCHAR2(10) := 'SYSTEM';
291 cst_ou CONSTANT VARCHAR2(10) := 'OU';
292 v_basic_level VARCHAR2(10);
293 v_calendar_level VARCHAR2(10);
294 v_org_unit_cd IGS_OR_UNIT.org_unit_cd%TYPE;
295 v_ou_start_dt IGS_OR_UNIT.start_dt%TYPE;
296 CURSOR c_sprgc IS
297 SELECT sprgc.apply_start_dt_alias,
298 sprgc.apply_end_dt_alias,
299 sprgc.end_benefit_dt_alias,
300 sprgc.end_penalty_dt_alias,
301 sprgc.show_cause_cutoff_dt_alias,
302 sprgc.appeal_cutoff_dt_alias,
303 sprgc.show_cause_ind,
304 sprgc.apply_before_show_ind,
305 sprgc.appeal_ind,
306 sprgc.apply_before_appeal_ind,
307 sprgc.count_sus_in_time_ind,
308 sprgc.count_exc_in_time_ind,
309 sprgc.calculate_wam_ind,
310 sprgc.calculate_gpa_ind,
311 sprgc.outcome_check_type
312 FROM IGS_PR_S_PRG_CONF sprgc
313 WHERE sprgc.s_control_num = 1;
314 CURSOR c_sopc (
315 cp_org_unit_cd IGS_PR_S_OU_PRG_CONF.org_unit_cd%TYPE,
316 cp_ou_start_dt IGS_PR_S_OU_PRG_CONF.ou_start_dt%TYPE) IS
317 SELECT sopc.apply_start_dt_alias,
318 sopc.apply_end_dt_alias,
319 sopc.end_benefit_dt_alias,
320 sopc.end_penalty_dt_alias,
321 sopc.show_cause_cutoff_dt_alias,
322 sopc.appeal_cutoff_dt_alias,
323 sopc.show_cause_ind,
324 sopc.apply_before_show_ind,
325 sopc.appeal_ind,
326 sopc.apply_before_appeal_ind,
327 sopc.count_sus_in_time_ind,
328 sopc.count_exc_in_time_ind,
329 sopc.calculate_wam_ind,
330 sopc.calculate_gpa_ind,
331 sopc.outcome_check_type
332 FROM IGS_PR_S_OU_PRG_CONF sopc
333 WHERE sopc.org_unit_cd = cp_org_unit_cd AND
334 sopc.ou_start_dt = cp_ou_start_dt;
335 CURSOR c_scpc IS
336 SELECT scpc.apply_start_dt_alias,
337 scpc.apply_end_dt_alias,
338 scpc.end_benefit_dt_alias,
339 scpc.end_penalty_dt_alias,
340 scpc.show_cause_cutoff_dt_alias,
341 scpc.appeal_cutoff_dt_alias,
342 scpc.show_cause_ind,
343 scpc.apply_before_show_ind,
344 scpc.appeal_ind,
345 scpc.apply_before_appeal_ind,
346 scpc.count_sus_in_time_ind,
347 scpc.count_exc_in_time_ind,
348 scpc.calculate_wam_ind,
349 scpc.calculate_gpa_ind,
350 scpc.outcome_check_type
351 FROM IGS_PR_S_CRV_PRG_CON scpc
352 WHERE scpc.course_cd = p_course_cd AND
353 scpc.version_number = p_version_number;
354 BEGIN
355 IGS_PR_GET_CONFIG_LVL (
356 p_course_cd,
357 p_version_number,
358 v_basic_level,
359 v_calendar_level,
360 v_org_unit_cd,
361 v_ou_start_dt);
362 IF v_basic_level = cst_system THEN
363 OPEN c_sprgc;
364 FETCH c_sprgc INTO
365 p_apply_start_dt_alias,
366 p_apply_end_dt_alias,
367 p_end_benefit_dt_alias,
368 p_end_penalty_dt_alias,
369 p_show_cause_cutoff_dt_alias,
370 p_appeal_cutoff_dt_alias,
371 p_show_cause_ind,
372 p_apply_before_show_ind,
373 p_appeal_ind,
374 p_apply_before_appeal_ind,
375 p_count_sus_in_time_ind,
376 p_count_exc_in_time_ind,
377 p_calculate_wam_ind,
378 p_calculate_gpa_ind,
379 p_outcome_check_type;
380 CLOSE c_sprgc;
381 ELSIF v_basic_level = cst_ou THEN
382 OPEN c_sopc (
383 v_org_unit_cd,
384 v_ou_start_dt);
385 FETCH c_sopc INTO
386 p_apply_start_dt_alias,
387 p_apply_end_dt_alias,
388 p_end_benefit_dt_alias,
389 p_end_penalty_dt_alias,
390 p_show_cause_cutoff_dt_alias,
391 p_appeal_cutoff_dt_alias,
392 p_show_cause_ind,
393 p_apply_before_show_ind,
394 p_appeal_ind,
395 p_apply_before_appeal_ind,
396 p_count_sus_in_time_ind,
397 p_count_exc_in_time_ind,
398 p_calculate_wam_ind,
399 p_calculate_gpa_ind,
400 p_outcome_check_type;
401 CLOSE c_sopc;
402 ELSIF v_basic_level = cst_course THEN
403 OPEN c_scpc;
404 FETCH c_scpc INTO
405 p_apply_start_dt_alias,
406 p_apply_end_dt_alias,
407 p_end_benefit_dt_alias,
408 p_end_penalty_dt_alias,
409 p_show_cause_cutoff_dt_alias,
410 p_appeal_cutoff_dt_alias,
411 p_show_cause_ind,
412 p_apply_before_show_ind,
413 p_appeal_ind,
414 p_apply_before_appeal_ind,
415 p_count_sus_in_time_ind,
416 p_count_exc_in_time_ind,
417 p_calculate_wam_ind,
418 p_calculate_gpa_ind,
419 p_outcome_check_type;
420 CLOSE c_scpc;
421 END IF;
422 EXCEPTION
423 WHEN OTHERS THEN
424 IF c_sprgc%ISOPEN THEN
425 CLOSE c_sprgc;
426 END IF;
427 IF c_sopc%ISOPEN THEN
428 CLOSE c_sopc;
429 END IF;
430 IF c_scpc%ISOPEN THEN
431 CLOSE c_scpc;
432 END IF;
433 RAISE;
434 END;
435 EXCEPTION
436 WHEN OTHERS THEN
437
438 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
439 IGS_GE_MSG_STACK.ADD;
440 App_Exception.Raise_Exception;
441 END IGS_PR_GET_CONFIG_PARM;
442 PROCEDURE IGS_PR_INS_ADV_TODO(
443 p_person_id IN NUMBER ,
444 p_course_cd IN VARCHAR2 ,
445 p_version_number IN NUMBER ,
446 p_old_adv_stnd_type IN VARCHAR2 ,
447 p_new_adv_stnd_type IN VARCHAR2 ,
448 p_old_s_adv_stnd_grant_status IN VARCHAR2 ,
449 p_new_s_adv_stnd_grant_status IN VARCHAR2 ,
450 p_old_credit_points IN NUMBER ,
451 p_new_credit_points IN NUMBER ,
452 p_old_credit_percentage IN NUMBER ,
453 p_new_credit_percentage IN NUMBER )
454 IS
455 gv_other_detail VARCHAR2(255);
456 BEGIN -- IGS_PR_INS_ADV_TODO
457 -- Insert todo records for progression checks where advanced standing detail
458 -- (either adv_stnd_unit_level or adv_stnd_unithas changed in the following
459 -- scenarios:
460 -- * s_adv_stnd_type is CREDIT and s_adv_stnd_granting_status has changed
461 -- old or new s_adv_stnd_granting_status is/was GRANTED
462 -- * s_adv_stnd_granting_status is GRANTED and s_adv_stnd_type is CREDIT and
463 -- credit_points has changed
464 -- * s_adv_stnd_granting_status is GRANTED and s_adv_stnd_type is CREDIT and
465 -- credit_percentage has changed and either new or old value is/was 100
466 -- If any progression periods are open then records are stored for these.
467 -- ijeddy, 4 Dec 2003, Bug 3258610. removed ref to Credit_percentage
468 DECLARE
469 cst_credit CONSTANT VARCHAR2(10) := 'CREDIT';
470 cst_granted CONSTANT VARCHAR2(10) := 'GRANTED';
471 cst_todo CONSTANT VARCHAR2(10) := 'TODO';
472 cst_prg_check CONSTANT VARCHAR2(10) := 'PRG_CHECK';
473 v_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
474 v_sequence_number NUMBER;
475 v_insert_todo BOOLEAN DEFAULT FALSE;
476 v_start_dt DATE;
477 v_cutoff_dt DATE;
478 CURSOR c_sca IS
479 SELECT sca.version_number
480 FROM IGS_EN_STDNT_PS_ATT sca
481 WHERE sca.person_id = p_person_id AND
482 sca.course_cd = p_course_cd;
483 CURSOR c_spc IS
484 SELECT spc.prg_cal_type,
485 spc.prg_ci_sequence_number
486 FROM IGS_PR_STDNT_PR_CK spc
487 WHERE spc.person_id = p_person_id AND
488 spc.course_cd = p_course_cd;
489 BEGIN
490
491 IF (NVL(p_old_s_adv_stnd_grant_status, ' ') <>
492 NVL(p_new_s_adv_stnd_grant_status, ' ') AND
493 ((NVL(p_old_s_adv_stnd_grant_status, ' ') = cst_granted OR
494 NVL(p_new_s_adv_stnd_grant_status, ' ') = cst_granted) AND
495 (NVL(p_old_adv_stnd_type, ' ') = cst_credit OR
496 NVL(p_new_adv_stnd_type, ' ') = cst_credit))) OR
497 (((NVL(p_old_s_adv_stnd_grant_status, ' ') = cst_granted OR
498 NVL(p_new_s_adv_stnd_grant_status, ' ') = cst_granted) AND
499 (NVL(p_old_adv_stnd_type, ' ') = cst_credit OR
500 NVL(p_new_adv_stnd_type, ' ') = cst_credit)) OR
501 (NVL(p_old_credit_points, 0) <> NVL(p_new_credit_points, 0)
502 )) THEN
503 -- If version number not passed then load it from IGS_EN_STDNT_PS_ATT
504 -- record
505
506 IF p_version_number IS NULL THEN
507 OPEN c_sca;
508 FETCH c_sca INTO v_version_number;
509 IF c_sca%NOTFOUND THEN
510
511 CLOSE c_sca;
512 RETURN;
513 END IF;
514 CLOSE c_sca;
515 ELSE
516 v_version_number := p_version_number;
517 END IF;
518 FOR v_spc_rec IN c_spc LOOP
519 IF IGS_PR_GEN_006.IGS_PR_GET_WITHIN_APPL (
520 v_spc_rec.prg_cal_type,
521 v_spc_rec.prg_ci_sequence_number,
522 p_course_cd,
523 v_version_number,
524 cst_todo,
525 v_start_dt,
526 v_cutoff_dt) = 'Y' THEN
527 v_insert_todo := TRUE;
528 EXIT;
529 END IF;
530 END LOOP;
531
532
533 IF v_insert_todo THEN
534 -- Insert todo entry
535 v_sequence_number := IGS_GE_GEN_003.GENP_INS_STDNT_TODO (
536 p_person_id,
537 cst_prg_check,
538 NULL,
539 'Y');
540 IGS_GE_GEN_003.GENP_INS_TODO_REF (
541 p_person_id,
542 cst_prg_check,
543 v_sequence_number,
544 NULL,
545 NULL,
546 p_course_cd,
547 NULL,
548 NULL,
549 --
550 -- kdande; 23-Apr-2003; Bug# 2829262
551 -- Passing uoo_id parameter as NULL to igs_ge_gen_003.genp_ins_todo_ref
552 --
553 NULL);
554 END IF;
555 END IF;
556
557 RETURN;
558 EXCEPTION
559 WHEN OTHERS THEN
560 IF c_sca%ISOPEN THEN
561 CLOSE c_sca;
562 END IF;
563 IF c_spc%ISOPEN THEN
564 CLOSE c_spc;
565 END IF;
566 RAISE;
567 END;
568 EXCEPTION
569 WHEN OTHERS THEN
570
571 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
572 IGS_GE_MSG_STACK.ADD;
573 App_Exception.Raise_Exception;
574 END IGS_PR_INS_ADV_TODO;
575
576 PROCEDURE IGS_PR_INS_PRG_MSR(
577 p_person_id IN NUMBER ,
578 p_course_cd IN VARCHAR2 ,
579 p_version_number IN NUMBER ,
580 p_prg_cal_type IN VARCHAR2 ,
581 p_prg_sequence_number IN NUMBER )
582 IS
583 /*------------------------------------------------------------------
584 --Known limitations/enhancements and/or remarks:
585 --
586 --Change History:
587 --Who When What
588 -- Prajeesh 21-apr-2002 When the course_gpa,period_gpa,course_wam,period_wam
589 -- is null then nvl to 0
590 --svanukur 30-jul-2003 replaced the call to the fnction IGS_PR_GEN_006.IGS_PR_GET_STD_GPA
591 -- to calcualate period_gpa and course_gpa with igs_pr_cp_gpa.get_all_stats
592 -- as partof bug 3031749
593 --svanukur 07-aug-03 undoing the above mentioned changes to revert back to the original file
594 since this file is not being modified as part of the bug#3031749
595 -------------------------------------------------------------------*/
596
597 gv_other_detail VARCHAR2(255);
598 BEGIN -- IGS_PR_GEN_001.IGS_PR_INS_PRG_MSR
599 -- Insert the appropriate progression measures for a nominated
600 -- student course attempt within a nominated progression period.
601 --
602 --The routine checks the system parameters applying to the students
603 -- course version and inserts the appropriate GPA/WAM values accordingly.
604 DECLARE
605 v_apply_start_dt_alias IGS_PR_S_PRG_CONF.apply_start_dt_alias%TYPE;
606 v_apply_end_dt_alias IGS_PR_S_PRG_CONF.apply_end_dt_alias%TYPE;
607 v_end_benefit_dt_alias IGS_PR_S_PRG_CONF.end_benefit_dt_alias%TYPE;
608 v_end_penalty_dt_alias IGS_PR_S_PRG_CONF.end_penalty_dt_alias%TYPE;
609 v_show_cause_cutoff_dt IGS_PR_S_PRG_CONF.show_cause_cutoff_dt_alias%TYPE;
610 v_appeal_cutoff_dt IGS_PR_S_PRG_CONF.appeal_cutoff_dt_alias%TYPE;
611 v_show_cause_ind IGS_PR_S_PRG_CONF.show_cause_ind%TYPE;
612 v_apply_before_show_ind IGS_PR_S_PRG_CONF.apply_before_show_ind%TYPE;
613 v_appeal_ind IGS_PR_S_PRG_CONF.appeal_ind%TYPE;
614 v_apply_before_appeal_ind IGS_PR_S_PRG_CONF.apply_before_appeal_ind%TYPE;
615 v_count_sus_in_time_ind IGS_PR_S_PRG_CONF.count_sus_in_time_ind%TYPE;
616 v_count_exc_in_time_ind IGS_PR_S_PRG_CONF.count_exc_in_time_ind%TYPE;
617 v_calculate_wam_ind IGS_PR_S_PRG_CONF.calculate_wam_ind%TYPE;
618 v_calculate_gpa_ind IGS_PR_S_PRG_CONF.calculate_gpa_ind%TYPE;
619 v_outcome_check_type IGS_PR_S_PRG_CONF.outcome_check_type%TYPE;
620 v_course_gpa NUMBER;
621 v_period_gpa NUMBER;
622 v_course_wam NUMBER;
623 v_period_wam NUMBER;
624 v_value NUMBER;
625 CURSOR c_scpm (cp_prg_measure_type
626 IGS_PR_SDT_PS_PR_MSR.s_prg_measure_type%TYPE) IS
627 SELECT scpm.value
628 FROM IGS_PR_SDT_PS_PR_MSR scpm
629 WHERE scpm.person_id = p_person_id AND
630 scpm.course_cd = p_course_cd AND
631 scpm.prg_cal_type = p_prg_cal_type AND
632 scpm.prg_ci_sequence_number = p_prg_sequence_number AND
633 scpm.s_prg_measure_type = cp_prg_measure_type
634 ORDER BY scpm.calculation_dt DESC;
635
636 --
637 -- Start of new code added to fix Bug# 3103892; nalkumar; 22-Aug-2003
638 CURSOR c_cir (cp_prg_cal_type igs_ca_inst.cal_type%TYPE,
639 cp_prg_sequence_number igs_ca_inst.sequence_number%TYPE) IS
640 SELECT cir.sub_cal_type cal_type, cir.sub_ci_sequence_number ci_sequence_number
641 FROM IGS_CA_INST ci ,
642 IGS_CA_INST_REL cir,
643 IGS_CA_TYPE cat,
644 IGS_CA_STAT cs
645 WHERE cir.sup_cal_type = cp_prg_cal_type
646 AND cir.sup_ci_sequence_number = cp_prg_sequence_number
647 AND ci.cal_type = cir.sub_cal_type
648 AND ci.sequence_number = cir.sub_ci_sequence_number
649 AND cat.cal_type = ci.cal_type
650 AND cat.s_cal_cat = 'LOAD'
651 AND cs.CAL_STATUS = ci.CAL_STATUS
652 AND cs.s_CAL_STATUS = 'ACTIVE';
653 rec_cir c_cir%ROWTYPE;
654 l_return_status VARCHAR2(1);
655 l_msg_count NUMBER;
656 l_msg_data VARCHAR2(2000);
657 v_gpa_value NUMBER;
658 v_gpa_cp NUMBER;
659 v_gpa_quality_points NUMBER;
660 -- End of new code added to fix Bug# 3103892; nalkumar; 22-Aug-2003
661 --
662
663 BEGIN
664 -- Call routine to get parameters applicable to course version.
665 IGS_PR_GET_CONFIG_PARM(
666 p_course_cd,
667 p_version_number,
668 v_apply_start_dt_alias,
669 v_apply_end_dt_alias,
670 v_end_benefit_dt_alias,
671 v_end_penalty_dt_alias,
672 v_show_cause_cutoff_dt,
673 v_appeal_cutoff_dt,
674 v_show_cause_ind,
675 v_apply_before_show_ind,
676 v_appeal_ind,
677 v_apply_before_appeal_ind,
678 v_count_sus_in_time_ind,
679 v_count_exc_in_time_ind,
680 v_calculate_wam_ind,
681 v_calculate_gpa_ind,
682 v_outcome_check_type);
683 -- Get GPA
684 --
685 -- Start of new code added to fix Bug# 3103892; nalkumar; 22-Aug-2003
686 OPEN c_cir(p_prg_cal_type, p_prg_sequence_number);
687 FETCH c_cir INTO rec_cir;
688 CLOSE c_cir;
689 -- End of new code added to fix Bug# 3103892; nalkumar; 22-Aug-2003
690 IF v_calculate_gpa_ind = 'Y' THEN
691 --
692 -- Start of new code added to fix Bug# 3103892; nalkumar; 22-Aug-2003
693 IGS_PR_CP_GPA.get_gpa_stats(
694 p_person_id => p_person_id,
695 p_course_cd => p_course_cd ,
696 p_stat_type => NULL,
697 p_load_cal_type => rec_cir.cal_type,
698 p_load_ci_sequence_number => rec_cir.ci_sequence_number,
699 p_system_stat => NULL,
700 p_cumulative_ind => 'Y',
701 p_gpa_value => v_course_gpa,
702 p_gpa_cp => v_gpa_cp,
703 p_gpa_quality_points => v_gpa_quality_points,
704 p_return_status => l_return_status,
705 p_msg_count => l_msg_count,
706 p_msg_data => l_msg_data);
707 -- End of new code added to fix Bug# 3103892; nalkumar; 22-Aug-2003
708 --
709
710 /* v_course_gpa := IGS_PR_GEN_006.IGS_PR_GET_STD_GPA(
711 p_person_id,
712 p_course_cd,
713 NULL,
714 NULL);
715 */
716 v_value := NULL;
717 OPEN c_scpm('COURSE-GPA');
718 FETCH c_scpm INTO v_value;
719 CLOSE c_scpm;
720 IF v_course_gpa IS NOT NULL OR v_value IS NOT NULL THEN
721 IF NVL(v_course_gpa,-1) <> NVL(v_value,-1) THEN
722 v_course_gpa:=nvl(v_course_gpa,0);
723 DECLARE
724 lv_rowid VARCHAR2(25);
725 BEGIN
726 IGS_PR_SDT_PS_PR_MSR_PKG .INSERT_ROW (
727 X_ROWID => lv_rowid,
728 X_PERSON_ID => p_person_id,
729 X_COURSE_CD => p_course_cd,
730 X_PRG_CAL_TYPE => p_prg_cal_type,
731 X_PRG_CI_SEQUENCE_NUMBER => p_prg_sequence_number,
732 X_S_PRG_MEASURE_TYPE => 'COURSE-GPA',
733 X_CALCULATION_DT => SYSDATE,
734 X_VALUE =>v_course_gpa,
735 X_MODE => 'R' );
736 END;
737 END IF;
738 END IF;
739 /* v_period_gpa := IGS_PR_GEN_006.IGS_PR_GET_STD_GPA(
740 p_person_id,
741 p_course_cd,
742 p_prg_cal_type,
743 p_prg_sequence_number);
744 */
745 --
746 -- Start of new code added to fix Bug# 3103892; nalkumar; 22-Aug-2003
747 IGS_PR_CP_GPA.get_gpa_stats(
748 p_person_id => p_person_id,
749 p_course_cd => p_course_cd ,
750 p_stat_type => NULL,
751 p_load_cal_type => rec_cir.cal_type,
752 p_load_ci_sequence_number => rec_cir.ci_sequence_number,
753 p_system_stat => NULL,
754 p_cumulative_ind => 'N',
755 p_gpa_value => v_period_gpa,
756 p_gpa_cp => v_gpa_cp,
757 p_gpa_quality_points => v_gpa_quality_points,
758 p_return_status => l_return_status,
759 p_msg_count => l_msg_count,
760 p_msg_data => l_msg_data);
761 -- End of new code added to fix Bug# 3103892; nalkumar; 22-Aug-2003
762 --
763 -- NULLIFY v_value else there is a problem that PERIOD-GPA will come same as COURSE-GPA
764 --
765 v_value := NULL;
766 OPEN c_scpm('PERIOD-GPA');
767 FETCH c_scpm INTO v_value;
768 CLOSE c_scpm;
769 IF v_period_gpa IS NOT NULL OR v_value IS NOT NULL THEN
770 IF NVL(v_period_gpa,-1) <> NVL(v_value,-1) THEN
771 v_period_gpa:=nvl(v_period_gpa,0);
772 DECLARE
773 lv_rowid VARCHAR2(25);
774 BEGIN
775 IGS_PR_SDT_PS_PR_MSR_PKG.INSERT_ROW (
776 X_ROWID => lv_rowid,
777 X_PERSON_ID => p_person_id,
778 X_COURSE_CD => p_course_cd,
779 X_PRG_CAL_TYPE => p_prg_cal_type,
780 X_PRG_CI_SEQUENCE_NUMBER => p_prg_sequence_number,
781 X_S_PRG_MEASURE_TYPE => 'PERIOD-GPA',
782 X_CALCULATION_DT => SYSDATE,
783 X_VALUE =>v_period_gpa,
784 X_MODE => 'R' );
785 END;
786 END IF;
787 END IF;
788 END IF;
789 -- Get WAM
790 IF v_calculate_wam_ind = 'Y' THEN
791 v_course_wam := IGS_PR_GEN_006.IGS_PR_GET_STD_WAM(
792 p_person_id ,
793 p_course_cd ,
794 p_version_number ,
795 NULL,
796 NULL) ;
797 v_value := NULL;
798 OPEN c_scpm('COURSE-WAM');
799 FETCH c_scpm INTO v_value;
800 CLOSE c_scpm;
801 IF v_course_wam IS NOT NULL OR v_value IS NOT NULL THEN
802 IF NVL(v_course_wam,-1) <> NVL(v_value,-1) THEN
803 /* INSERT INTO IGS_PR_SDT_PS_PR_MSR(
804 person_id,
805 course_cd,
806 prg_cal_type,
807 prg_ci_sequence_number,
808 s_prg_measure_type,
809 calculation_dt,
810 value)
811 VALUES(
812 p_person_id,
813 p_course_cd,
814 p_prg_cal_type,
815 p_prg_sequence_number,
816 'COURSE-WAM',
817 SYSDATE,
818 v_course_wam); */
819 v_course_wam:=nvl(v_course_wam,0);
820 DECLARE
821 lv_rowid VARCHAR2(25);
822 BEGIN
823 IGS_PR_SDT_PS_PR_MSR_PKG .INSERT_ROW (
824 X_ROWID => lv_rowid,
825 X_PERSON_ID => p_person_id,
826 X_COURSE_CD => p_course_cd,
827 X_PRG_CAL_TYPE => p_prg_cal_type,
828 X_PRG_CI_SEQUENCE_NUMBER => p_prg_sequence_number,
829 X_S_PRG_MEASURE_TYPE => 'COURSE-WAM',
830 X_CALCULATION_DT => SYSDATE,
831 X_VALUE =>v_course_wam,
832 X_MODE => 'R' );
833 END;
834 END IF;
835 END IF;
836 v_period_wam := IGS_PR_GEN_006.IGS_PR_GET_STD_WAM(
837 p_person_id,
838 p_course_cd,
839 p_version_number,
840 p_prg_cal_type,
841 p_prg_sequence_number);
842 v_value := NULL;
843 OPEN c_scpm('PERIOD-WAM');
844 FETCH c_scpm INTO v_value;
845 CLOSE c_scpm;
846 IF v_period_wam IS NOT NULL OR v_value IS NOT NULL THEN
847 IF NVL(v_period_wam,1) <> NVL(v_value,-1) THEN
848 /*
849 INSERT INTO IGS_PR_SDT_PS_PR_MSR(
850 person_id,
851 course_cd,
852 prg_cal_type,
853 prg_ci_sequence_number,
854 s_prg_measure_type,
855 calculation_dt,
856 value)
857 VALUES(
858 p_person_id,
859 p_course_cd,
860 p_prg_cal_type,
861 p_prg_sequence_number,
862 'PERIOD-WAM',
863 SYSDATE,
864 v_period_wam); */
865 v_period_wam:=nvl(v_period_wam,0);
866 DECLARE
867 lv_rowid VARCHAR2(25);
868 BEGIN
869 IGS_PR_SDT_PS_PR_MSR_PKG .INSERT_ROW (
870 X_ROWID => lv_rowid,
871 X_PERSON_ID => p_person_id,
872 X_COURSE_CD => p_course_cd,
873 X_PRG_CAL_TYPE => p_prg_cal_type,
874 X_PRG_CI_SEQUENCE_NUMBER => p_prg_sequence_number,
875 X_S_PRG_MEASURE_TYPE => 'PERIOD-WAM',
876 X_CALCULATION_DT => SYSDATE,
877 X_VALUE =>v_period_wam,
878 X_MODE => 'R' );
879 END;
880 END IF;
881 END IF;
882 END IF;
883 END;
884 EXCEPTION
885 WHEN OTHERS THEN
886
887 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
888 IGS_GE_MSG_STACK.ADD;
889 App_Exception.Raise_Exception;
890 END IGS_PR_INS_PRG_MSR;
891
892 PROCEDURE IGS_PR_INS_SPO_HIST(
893 p_person_id IN NUMBER ,
894 p_course_cd IN VARCHAR2 ,
895 p_sequence_number IN NUMBER ,
896 p_new_prg_cal_type IN VARCHAR2 ,
897 p_old_prg_cal_type IN VARCHAR2 ,
898 p_new_prg_ci_sequence_number IN NUMBER ,
899 p_old_prg_ci_sequence_number IN NUMBER ,
900 p_new_rule_check_dt IN DATE ,
901 p_old_rule_check_dt IN DATE ,
902 p_new_progression_rule_cat IN VARCHAR2 ,
903 p_old_progression_rule_cat IN VARCHAR2 ,
904 p_new_pra_sequence_number IN NUMBER ,
905 p_old_pra_sequence_number IN NUMBER ,
906 p_new_pro_pra_sequence_number IN NUMBER ,
907 p_old_pro_pra_sequence_number IN NUMBER ,
908 p_new_pro_sequence_number IN NUMBER ,
909 p_old_pro_sequence_number IN NUMBER ,
910 p_new_progression_outcome_type IN VARCHAR2 ,
911 p_old_progression_outcome_type IN VARCHAR2 ,
912 p_new_duration IN NUMBER ,
913 p_old_duration IN NUMBER ,
914 p_new_duration_type IN VARCHAR2 ,
915 p_old_duration_type IN VARCHAR2 ,
916 p_new_decision_status IN VARCHAR2 ,
917 p_old_decision_status IN VARCHAR2 ,
918 p_new_decision_dt IN DATE ,
919 p_old_decision_dt IN DATE ,
920 p_new_decision_org_unit_cd IN VARCHAR2 ,
921 p_old_decision_org_unit_cd IN VARCHAR2 ,
922 p_new_decision_ou_start_dt IN DATE ,
923 p_old_decision_ou_start_dt IN DATE ,
924 p_new_applied_dt IN DATE ,
925 p_old_applied_dt IN DATE ,
926 p_new_expiry_dt IN DATE ,
927 p_old_expiry_dt IN DATE ,
928 p_new_show_cause_expiry_dt IN DATE ,
929 p_old_show_cause_expiry_dt IN DATE ,
930 p_new_show_cause_dt IN DATE ,
931 p_old_show_cause_dt IN DATE ,
932 p_new_show_cause_outcome_dt IN DATE ,
933 p_old_show_cause_outcome_dt IN DATE ,
934 p_new_show_cause_outcome_type IN VARCHAR2 ,
935 p_old_show_cause_outcome_type IN VARCHAR2 ,
936 p_new_appeal_expiry_dt IN DATE ,
937 p_old_appeal_expiry_dt IN DATE ,
938 p_new_appeal_dt IN DATE ,
939 p_old_appeal_dt IN DATE ,
940 p_new_appeal_outcome_dt IN DATE ,
941 p_old_appeal_outcome_dt IN DATE ,
942 p_new_appeal_outcome_type IN VARCHAR2 ,
943 p_old_appeal_outcome_type IN VARCHAR2 ,
944 p_new_encmb_course_group_cd IN VARCHAR2 ,
945 p_old_encmb_course_group_cd IN VARCHAR2 ,
946 p_new_restricted_enrolment_cp IN NUMBER ,
947 p_old_restricted_enrolment_cp IN NUMBER ,
948 p_new_restricted_att_type IN VARCHAR2 ,
949 p_old_restricted_att_type IN VARCHAR2 ,
950 p_new_LAST_UPDATED_BY IN VARCHAR2 ,
951 p_old_LAST_UPDATED_BY IN VARCHAR2 ,
952 p_new_LAST_UPDATE_DATE IN DATE ,
953 p_old_LAST_UPDATE_DATE IN DATE ,
954 p_new_comments IN VARCHAR2 ,
955 p_old_comments IN VARCHAR2 ,
956 p_new_show_cause_comments IN VARCHAR2 ,
957 p_old_show_cause_comments IN VARCHAR2 ,
958 p_new_appeal_comments IN VARCHAR2 ,
959 p_old_appeal_comments IN VARCHAR2 )
960 IS
961 gv_other_detail VARCHAR2(255);
962 BEGIN -- IGS_PR_INS_SPO_HIST
963 DECLARE
964 r_spoh IGS_PR_STU_OU_HIST%ROWTYPE;
965 v_create_history BOOLEAN DEFAULT FALSE;
966 BEGIN
967 -- check if a history record is required
968 IF NVL(p_new_prg_cal_type, 'NULL') <> NVL(p_old_prg_cal_type, 'NULL') THEN
969 r_spoh.prg_cal_type := p_old_prg_cal_type;
970 v_create_history := TRUE;
971 END IF;
972 IF NVL(p_new_prg_ci_sequence_number, 0) <>
973 NVL(p_old_prg_ci_sequence_number, 0) THEN
974 r_spoh.prg_ci_sequence_number := p_old_prg_ci_sequence_number;
975 v_create_history := TRUE;
976 END IF;
977 IF NVL(p_new_rule_check_dt, TO_DATE('01/01/0001', 'DD/MM/YYYY')) <>
978 NVL(p_old_rule_check_dt, TO_DATE('01/01/0001', 'DD/MM/YYYY')) THEN
979 r_spoh.rule_check_dt := p_old_rule_check_dt;
980 v_create_history := TRUE;
981 END IF;
982 IF NVL(p_new_progression_rule_cat, 'NULL') <>
983 NVL(p_old_progression_rule_cat, 'NULL') THEN
984 r_spoh.progression_rule_cat := p_old_progression_rule_cat;
985 v_create_history := TRUE;
986 END IF;
987 IF NVL(p_new_pra_sequence_number, 0) <> NVL(p_old_pra_sequence_number, 0) THEN
988 r_spoh.pra_sequence_number := p_old_pra_sequence_number;
989 v_create_history := TRUE;
990 END IF;
991 IF NVL(p_new_pro_pra_sequence_number, 0) <>
992 NVL(p_old_pro_pra_sequence_number, 0) THEN
993 r_spoh.pro_pra_sequence_number := p_old_pro_pra_sequence_number;
994 v_create_history := TRUE;
995 END IF;
996 IF NVL(p_new_pro_sequence_number, 0) <> NVL(p_old_pro_sequence_number, 0) THEN
997 r_spoh.pro_sequence_number := p_old_pro_sequence_number;
998 v_create_history := TRUE;
999 END IF;
1000 IF NVL(p_new_progression_outcome_type, 'NULL') <>
1001 NVL(p_old_progression_outcome_type, 'NULL') THEN
1002 r_spoh.progression_outcome_type := p_old_progression_outcome_type;
1003 v_create_history := TRUE;
1004 END IF;
1005 IF NVL(p_new_duration, 0) <> NVL(p_old_duration, 0) THEN
1006 r_spoh.duration := p_old_duration;
1007 v_create_history := TRUE;
1008 END IF;
1009 IF NVL(p_new_duration_type, 'NULL') <> NVL(p_old_duration_type, 'NULL') THEN
1010 r_spoh.duration_type := p_old_duration_type;
1011 v_create_history := TRUE;
1012 END IF;
1013 IF p_new_decision_status <> p_old_decision_status THEN
1014 r_spoh.decision_status := p_old_decision_status;
1015 v_create_history := TRUE;
1016 END IF;
1017 IF NVL(p_new_decision_dt, TO_DATE('01/01/0001', 'DD/MM/YYYY')) <>
1018 NVL(p_old_decision_dt, TO_DATE('01/01/0001', 'DD/MM/YYYY')) THEN
1019 r_spoh.decision_dt := p_old_decision_dt;
1020 v_create_history := TRUE;
1021 END IF;
1022 IF NVL(p_new_decision_org_unit_cd, 'NULL') <>
1023 NVL(p_old_decision_org_unit_cd, 'NULL') THEN
1024 r_spoh.decision_org_unit_cd := p_old_decision_org_unit_cd;
1025 v_create_history := TRUE;
1026 END IF;
1027 IF NVL(p_new_decision_ou_start_dt, TO_DATE('01/01/0001', 'DD/MM/YYYY')) <>
1028 NVL(p_old_decision_ou_start_dt, TO_DATE('01/01/0001', 'DD/MM/YYYY')) THEN
1029 r_spoh.decision_ou_start_dt := p_old_decision_ou_start_dt;
1030 v_create_history := TRUE;
1031 END IF;
1032 IF NVL(p_new_applied_dt, TO_DATE('01/01/0001', 'DD/MM/YYYY')) <>
1033 NVL(p_old_applied_dt, TO_DATE('01/01/0001', 'DD/MM/YYYY')) THEN
1034 r_spoh.applied_dt := p_old_applied_dt;
1035 v_create_history := TRUE;
1036 END IF;
1037 IF NVL(p_new_expiry_dt, TO_DATE('01/01/0001', 'DD/MM/YYYY')) <>
1038 NVL(p_old_expiry_dt, TO_DATE('01/01/0001', 'DD/MM/YYYY')) THEN
1039 r_spoh.expiry_dt := p_old_expiry_dt;
1040 v_create_history := TRUE;
1041 END IF;
1042 IF NVL(p_new_show_cause_expiry_dt, TO_DATE('01/01/0001', 'DD/MM/YYYY')) <>
1043 NVL(p_old_show_cause_expiry_dt, TO_DATE('01/01/0001', 'DD/MM/YYYY')) THEN
1044 r_spoh.show_cause_expiry_dt := p_old_show_cause_expiry_dt;
1045 v_create_history := TRUE;
1046 END IF;
1047 IF NVL(p_new_show_cause_dt, TO_DATE('01/01/0001', 'DD/MM/YYYY')) <>
1048 NVL(p_old_show_cause_dt, TO_DATE('01/01/0001', 'DD/MM/YYYY')) THEN
1049 r_spoh.show_cause_dt := p_old_show_cause_dt;
1050 v_create_history := TRUE;
1051 END IF;
1052 IF NVL(p_new_show_cause_outcome_dt, TO_DATE('01/01/0001', 'DD/MM/YYYY')) <>
1053 NVL(p_old_show_cause_outcome_dt, TO_DATE('01/01/0001', 'DD/MM/YYYY')) THEN
1054 r_spoh.show_cause_outcome_dt := p_old_show_cause_outcome_dt;
1055 v_create_history := TRUE;
1056 END IF;
1057 IF NVL(p_new_show_cause_outcome_type, 'NULL') <>
1058 NVL(p_old_show_cause_outcome_type, 'NULL') THEN
1059 r_spoh.show_cause_outcome_type := p_old_show_cause_outcome_type;
1060 v_create_history := TRUE;
1061 END IF;
1062 IF NVL(p_new_appeal_expiry_dt, TO_DATE('01/01/0001', 'DD/MM/YYYY')) <>
1063 NVL(p_old_appeal_expiry_dt, TO_DATE('01/01/0001', 'DD/MM/YYYY')) THEN
1064 r_spoh.appeal_expiry_dt := p_old_appeal_expiry_dt;
1065 v_create_history := TRUE;
1066 END IF;
1067 IF NVL(p_new_appeal_dt, TO_DATE('01/01/0001', 'DD/MM/YYYY')) <>
1068 NVL(p_old_appeal_dt, TO_DATE('01/01/0001', 'DD/MM/YYYY')) THEN
1069 r_spoh.appeal_dt := p_old_appeal_dt;
1070 v_create_history := TRUE;
1071 END IF;
1072 IF NVL(p_new_appeal_outcome_dt, TO_DATE('01/01/0001', 'DD/MM/YYYY')) <>
1073 NVL(p_old_appeal_outcome_dt, TO_DATE('01/01/0001', 'DD/MM/YYYY')) THEN
1074 r_spoh.appeal_outcome_dt := p_old_appeal_outcome_dt;
1075 v_create_history := TRUE;
1076 END IF;
1077 IF NVL(p_new_appeal_outcome_type, 'NULL') <>
1078 NVL(p_old_appeal_outcome_type, 'NULL') THEN
1079 r_spoh.appeal_outcome_type := p_old_appeal_outcome_type;
1080 v_create_history := TRUE;
1081 END IF;
1082 IF NVL(p_new_encmb_course_group_cd, 'NULL') <>
1083 NVL(p_old_encmb_course_group_cd, 'NULL') THEN
1084 r_spoh.encmb_course_group_cd := p_old_encmb_course_group_cd;
1085 v_create_history := TRUE;
1086 END IF;
1087 IF NVL(p_new_restricted_enrolment_cp, 0) <>
1088 NVL(p_old_restricted_enrolment_cp, 0) THEN
1089 r_spoh.restricted_enrolment_cp := p_old_restricted_enrolment_cp;
1090 v_create_history := TRUE;
1091 END IF;
1092 IF NVL(p_new_restricted_att_type, 'NULL') <>
1093 NVL(p_old_restricted_att_type, 'NULL') THEN
1094 r_spoh.restricted_attendance_type := p_old_restricted_att_type;
1095 v_create_history := TRUE;
1096 END IF;
1097 IF p_new_LAST_UPDATED_BY <> p_old_LAST_UPDATED_BY THEN
1098 r_spoh.LAST_UPDATED_BY := p_old_LAST_UPDATED_BY;
1099 v_create_history := TRUE;
1100 END IF;
1101 IF p_new_LAST_UPDATE_DATE <> p_old_LAST_UPDATE_DATE THEN
1102 r_spoh.LAST_UPDATE_DATE := p_old_LAST_UPDATE_DATE;
1103 v_create_history := TRUE;
1104 END IF;
1105 IF NVL(p_new_comments, 'NULL') <> NVL(p_old_comments, 'NULL') THEN
1106 r_spoh.comments := p_old_comments;
1107 v_create_history := TRUE;
1108 END IF;
1109 IF NVL(p_new_show_cause_comments, 'NULL') <>
1110 NVL(p_old_show_cause_comments, 'NULL') THEN
1111 r_spoh.show_cause_comments := p_old_show_cause_comments;
1112 v_create_history := TRUE;
1113 END IF;
1114 IF NVL(p_new_appeal_comments, 'NULL') <>
1115 NVL(p_old_appeal_comments, 'NULL') THEN
1116 r_spoh.appeal_comments := p_old_appeal_comments;
1117 v_create_history := TRUE;
1118 END IF;
1119 -- create a history record if a column has changed value
1120 IF v_create_history = TRUE THEN
1121 r_spoh.person_id := p_person_id;
1122 r_spoh.course_cd := p_course_cd;
1123 r_spoh.sequence_number := p_sequence_number;
1124 r_spoh.hist_start_dt := p_old_LAST_UPDATE_DATE;
1125 r_spoh.hist_end_dt := p_new_LAST_UPDATE_DATE;
1126 r_spoh.hist_who := p_old_LAST_UPDATED_BY;
1127 -- remove one second from the hist_start_dt value when the hist_start_dt
1128 -- and hist_end_dt are the same to avoid a primary key constraint from
1129 -- occurring when saving the record
1130 IF r_spoh.hist_start_dt = r_spoh.hist_end_dt THEN
1131 r_spoh.hist_start_dt := r_spoh.hist_start_dt - 1 / (60*24*60);
1132 END IF;
1133 /*
1134 INSERT INTO IGS_PR_STU_OU_HIST (
1135 person_id,
1136 course_cd,
1137 sequence_number,
1138 hist_start_dt,
1139 hist_end_dt,
1140 hist_who,
1141 prg_cal_type,
1142 prg_ci_sequence_number,
1143 rule_check_dt,
1144 progression_rule_cat,
1145 pra_sequence_number,
1146 pro_pra_sequence_number,
1147 pro_sequence_number,
1148 progression_outcome_type,
1149 duration,
1150 duration_type,
1151 decision_status,
1152 decision_dt,
1153 decision_org_unit_cd,
1154 decision_ou_start_dt,
1155 applied_dt,
1156 expiry_dt,
1157 show_cause_expiry_dt,
1158 show_cause_dt,
1159 show_cause_outcome_dt,
1160 show_cause_outcome_type,
1161 appeal_expiry_dt,
1162 appeal_dt,
1163 appeal_outcome_dt,
1164 appeal_outcome_type,
1165 encmb_course_group_cd,
1166 restricted_enrolment_cp,
1167 restricted_attendance_type,
1168 comments,
1169 show_cause_comments,
1170 appeal_comments)
1171 VALUES (
1172 r_spoh.person_id,
1173 r_spoh.course_cd,
1174 r_spoh.sequence_number,
1175 r_spoh.hist_start_dt,
1176 r_spoh.hist_end_dt,
1177 r_spoh.hist_who,
1178 r_spoh.prg_cal_type,
1179 r_spoh.prg_ci_sequence_number,
1180 r_spoh.rule_check_dt,
1181 r_spoh.progression_rule_cat,
1182 r_spoh.pra_sequence_number,
1183 r_spoh.pro_pra_sequence_number,
1184 r_spoh.pro_sequence_number,
1185 r_spoh.progression_outcome_type,
1186 r_spoh.duration,
1187 r_spoh.duration_type,
1188 r_spoh.decision_status,
1189 r_spoh.decision_dt,
1190 r_spoh.decision_org_unit_cd,
1191 r_spoh.decision_ou_start_dt,
1192 r_spoh.applied_dt,
1193 r_spoh.expiry_dt,
1194 r_spoh.show_cause_expiry_dt,
1195 r_spoh.show_cause_dt,
1196 r_spoh.show_cause_outcome_dt,
1197 r_spoh.show_cause_outcome_type,
1198 r_spoh.appeal_expiry_dt,
1199 r_spoh.appeal_dt,
1200 r_spoh.appeal_outcome_dt,
1201 r_spoh.appeal_outcome_type,
1202 r_spoh.encmb_course_group_cd,
1203 r_spoh.restricted_enrolment_cp,
1204 r_spoh.restricted_attendance_type,
1205 r_spoh.comments,
1206 r_spoh.show_cause_comments,
1207 r_spoh.appeal_comments);
1208 */
1209 DECLARE
1210 lv_rowid VARCHAR2(25);
1211 l_org_id NUMBER(15);
1212 BEGIN
1213 l_org_id := igs_ge_gen_003.get_org_id;
1214 IGS_PR_STU_OU_HIST_PKG.INSERT_ROW (
1215 X_ROWID => lv_rowid,
1216 x_PERSON_ID => r_spoh.person_id,
1217 x_COURSE_CD => r_spoh.course_cd,
1218 x_SEQUENCE_NUMBER => r_spoh.sequence_number,
1219 x_HIST_START_DT => r_spoh.hist_start_dt,
1220 x_APPEAL_COMMENTS => r_spoh.appeal_comments,
1221 x_APPEAL_DT => r_spoh.appeal_dt,
1222 x_APPEAL_EXPIRY_DT => r_spoh.appeal_expiry_dt,
1223 x_APPEAL_OUTCOME_DT => r_spoh.appeal_outcome_dt,
1224 x_APPEAL_OUTCOME_TYPE => r_spoh.appeal_outcome_type,
1225 x_APPLIED_DT => r_spoh.applied_dt,
1226 x_COMMENTS => r_spoh.comments,
1227 x_DECISION_DT => r_spoh.decision_dt,
1228 x_DECISION_ORG_UNIT_CD => r_spoh.decision_org_unit_cd,
1229 x_DECISION_OU_START_DT =>r_spoh.decision_ou_start_dt,
1230 x_DECISION_STATUS => r_spoh.decision_status,
1231 x_DURATION => r_spoh.duration,
1232 x_DURATION_TYPE => r_spoh.duration_type,
1233 x_ENCMB_COURSE_GROUP_CD => r_spoh.encmb_course_group_cd,
1234 x_EXPIRY_DT => r_spoh.expiry_dt,
1235 x_HIST_END_DT => r_spoh.hist_end_dt,
1236 x_HIST_WHO => r_spoh.hist_who,
1237 x_PRA_SEQUENCE_NUMBER => r_spoh.pra_sequence_number,
1238 x_PRG_CAL_TYPE => r_spoh.prg_cal_type,
1239 x_PRG_CI_SEQUENCE_NUMBER => r_spoh.prg_ci_sequence_number,
1240 x_PROGRESSION_OUTCOME_TYPE => r_spoh.progression_outcome_type,
1241 x_PROGRESSION_RULE_CAT => r_spoh.progression_rule_cat,
1242 x_PRO_PRA_SEQUENCE_NUMBER => r_spoh.pro_pra_sequence_number,
1243 x_PRO_SEQUENCE_NUMBER => r_spoh.pro_sequence_number,
1244 x_RESTRICTED_ATTENDANCE_TYPE => r_spoh.restricted_attendance_type,
1245 x_RESTRICTED_ENROLMENT_CP => r_spoh.restricted_enrolment_cp,
1246 x_RULE_CHECK_DT => r_spoh.rule_check_dt,
1247 x_SHOW_CAUSE_COMMENTS => r_spoh.show_cause_comments,
1248 x_SHOW_CAUSE_DT => r_spoh.show_cause_dt,
1249 x_SHOW_CAUSE_EXPIRY_DT => r_spoh.show_cause_expiry_dt,
1250 x_SHOW_CAUSE_OUTCOME_DT => r_spoh.show_cause_outcome_dt,
1251 x_SHOW_CAUSE_OUTCOME_TYPE =>r_spoh.show_cause_outcome_type,
1252 X_MODE => 'R',
1253 X_ORG_ID => l_org_id );
1254 END;
1255 END IF;
1256 RETURN;
1257 END;
1258 EXCEPTION
1259 WHEN OTHERS THEN
1260
1261 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1262 IGS_GE_MSG_STACK.ADD;
1263 App_Exception.Raise_Exception;
1264 END IGS_PR_INS_SPO_HIST;
1265 PROCEDURE IGS_PR_INS_SSP(
1266 p_creation_dt IN DATE ,
1267 p_key IN VARCHAR2 ,
1268 p_s_message_name IN VARCHAR2 ,
1269 p_text IN VARCHAR2 ,
1270 p_ssp_sequence_number OUT NOCOPY NUMBER )
1271 IS
1272 BEGIN
1273 DECLARE
1274 v_other_detail VARCHAR2(350);
1275 CURSOR c_get_nxt_seq IS
1276 --SELECT IGS_PR_S_SCRATCH_PAD_S.nextval
1277 SELECT IGS_PR_RU_APPL_SEQ_NUM_S.nextval
1278 FROM DUAL;
1279 v_ssp_sequence_number IGS_PR_S_SCRATCH_PAD.sequence_number%TYPE;
1280 BEGIN
1281 -- this module inserts and entry into the
1282 -- system scratch pad table
1283 -- Get the next sequence number;
1284 OPEN c_get_nxt_seq;
1285 FETCH c_get_nxt_seq INTO v_ssp_sequence_number;
1286 CLOSE c_get_nxt_seq;
1287 /* INSERT INTO s_scratch_pad (
1288 sequence_number,
1289 creation_dt,
1290 key,
1291 message_number,
1292 text)
1293 VALUES (
1294 v_ssp_sequence_number,
1295 p_creation_dt,
1296 p_key,
1297 p_s_message_num,
1298 p_text); */
1299 DECLARE
1300 lv_rowid VARCHAR2(25);
1301 l_org_id NUMBER(15);
1302 BEGIN
1303 l_org_id := igs_ge_gen_003.get_org_id;
1304 IGS_PR_S_SCRATCH_PAD_PKG.INSERT_ROW (
1305 X_ROWID =>lv_rowid,
1306 x_SEQUENCE_NUMBER =>v_ssp_sequence_number,
1307 x_CREATION_DT =>p_creation_dt,
1308 x_KEY =>p_key,
1309 x_MESSAGE_NAME =>p_s_message_name,
1310 x_TEXT =>p_text,
1311 X_MODE =>'R',
1312 X_ORG_ID => l_org_id );
1313 END;
1314 p_ssp_sequence_number := v_ssp_sequence_number;
1315 EXCEPTION
1316 WHEN OTHERS THEN
1317
1318 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1319 IGS_GE_MSG_STACK.ADD;
1320 App_Exception.Raise_Exception;
1321 END ;
1322 END IGS_PR_INS_SSP;
1323 END IGS_PR_GEN_003;