[Home] [Help]
PACKAGE BODY: APPS.IGS_PR_STDNT_PR_OU_PKG
Source
1 PACKAGE BODY igs_pr_stdnt_pr_ou_pkg AS
2 /* $Header: IGSQI15B.pls 120.0 2005/07/05 12:07:10 appldev noship $ */
3
4
5 l_rowid VARCHAR2(25);
6 old_references IGS_PR_STDNT_PR_OU_ALL%RowType;
7 new_references IGS_PR_STDNT_PR_OU_ALL%RowType;
8
9 /* Forward Declaration of apply_appr_outcome*/
10 PROCEDURE apply_appr_outcome;
11
12 PROCEDURE Set_Column_Values (
13 p_action IN VARCHAR2,
14 x_rowid IN VARCHAR2 ,
15 x_prg_cal_type IN VARCHAR2 ,
16 x_prg_ci_sequence_number IN NUMBER ,
17 x_rule_check_dt IN DATE ,
18 x_progression_rule_cat IN VARCHAR2 ,
19 x_pra_sequence_number IN NUMBER ,
20 x_pro_sequence_number IN NUMBER ,
21 x_progression_outcome_type IN VARCHAR2 ,
22 x_duration IN NUMBER ,
23 x_duration_type IN VARCHAR2 ,
24 x_decision_status IN VARCHAR2 ,
25 x_decision_dt IN DATE,
26 x_decision_org_unit_cd IN VARCHAR2,
27 x_decision_ou_start_dt IN DATE,
28 x_applied_dt IN DATE,
29 x_show_cause_expiry_dt IN DATE,
30 x_show_cause_dt IN DATE,
31 x_show_cause_outcome_dt IN DATE,
32 x_show_cause_outcome_type IN VARCHAR2,
33 x_appeal_expiry_dt IN DATE,
34 x_appeal_dt IN DATE,
35 x_appeal_outcome_dt IN DATE,
36 x_appeal_outcome_type IN VARCHAR2 ,
37 x_encmb_course_group_cd IN VARCHAR2 ,
38 x_restricted_enrolment_cp IN NUMBER ,
39 x_restricted_attendance_type IN VARCHAR2,
40 x_comments IN VARCHAR2 ,
41 x_show_cause_comments IN VARCHAR2,
42 x_appeal_comments IN VARCHAR2,
43 x_person_id IN NUMBER ,
44 x_course_cd IN VARCHAR2 ,
45 x_sequence_number IN NUMBER ,
46 x_expiry_dt IN DATE ,
47 x_pro_pra_sequence_number IN NUMBER,
48 x_creation_date IN DATE ,
49 x_created_by IN NUMBER,
50 x_last_update_date IN DATE ,
51 x_last_updated_by IN NUMBER ,
52 x_last_update_login IN NUMBER ,
53 x_org_id IN NUMBER
54 ) AS
55
56 CURSOR cur_old_ref_values IS
57 SELECT *
58 FROM IGS_PR_STDNT_PR_OU_ALL
59 WHERE ROWID = x_rowid;
60
61 BEGIN
62
63 l_rowid := x_rowid;
64
65 -- Code for setting the Old and New Reference Values.
66 -- Populate Old Values.
67 Open cur_old_ref_values;
68 Fetch cur_old_ref_values INTO old_references;
69 IF (cur_old_ref_values%NOTFOUND) AND
70 (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
71 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
72 IGS_GE_MSG_STACK.ADD;
73 Close cur_old_ref_values;
74 App_Exception.Raise_Exception;
75
76 Return;
77 END IF;
78 Close cur_old_ref_values;
79
80 -- Populate New Values.
81 new_references.prg_cal_type := x_prg_cal_type;
82 new_references.prg_ci_sequence_number := x_prg_ci_sequence_number;
83 new_references.rule_check_dt := x_rule_check_dt;
84 new_references.progression_rule_cat := x_progression_rule_cat;
85 new_references.pra_sequence_number := x_pra_sequence_number;
86 new_references.pro_sequence_number := x_pro_sequence_number;
87 new_references.progression_outcome_type := x_progression_outcome_type;
88 new_references.duration := x_duration;
89 new_references.duration_type := x_duration_type;
90 new_references.decision_status := x_decision_status;
91 new_references.decision_dt := x_decision_dt;
92 new_references.decision_org_unit_cd := x_decision_org_unit_cd;
93 new_references.decision_ou_start_dt := x_decision_ou_start_dt;
94 new_references.applied_dt := x_applied_dt;
95 new_references.show_cause_expiry_dt := x_show_cause_expiry_dt;
96 new_references.show_cause_dt := x_show_cause_dt;
97 new_references.show_cause_outcome_dt := x_show_cause_outcome_dt;
98 new_references.show_cause_outcome_type := x_show_cause_outcome_type;
99 new_references.appeal_expiry_dt := x_appeal_expiry_dt;
100 new_references.appeal_dt := x_appeal_dt;
101 new_references.appeal_outcome_dt := x_appeal_outcome_dt;
102 new_references.appeal_outcome_type := x_appeal_outcome_type;
103 new_references.encmb_course_group_cd := x_encmb_course_group_cd;
104 new_references.restricted_enrolment_cp := x_restricted_enrolment_cp;
105 new_references.restricted_attendance_type := x_restricted_attendance_type;
106 new_references.comments := x_comments;
107 new_references.show_cause_comments := x_show_cause_comments;
108 new_references.appeal_comments := x_appeal_comments;
109 new_references.person_id := x_person_id;
110 new_references.course_cd := x_course_cd;
111 new_references.sequence_number := x_sequence_number;
112 new_references.expiry_dt := x_expiry_dt;
113 new_references.pro_pra_sequence_number := x_pro_pra_sequence_number;
114 IF (p_action = 'UPDATE') THEN
115 new_references.creation_date := old_references.creation_date;
116 new_references.created_by := old_references.created_by;
117 ELSE
118 new_references.creation_date := x_creation_date;
119 new_references.created_by := x_created_by;
120 END IF;
121 new_references.last_update_date := x_last_update_date;
122 new_references.last_updated_by := x_last_updated_by;
123 new_references.last_update_login := x_last_update_login;
124 new_references.org_id := x_org_id;
125
126 END Set_Column_Values;
127
128
129 PROCEDURE Check_Parent_Existance AS
130 BEGIN
131
132 IF (((old_references.restricted_attendance_type =
133 new_references.restricted_attendance_type)) OR
134 ((new_references.restricted_attendance_type IS NULL))) THEN
135 NULL;
136 ELSE
137 IF NOT IGS_EN_ATD_TYPE_PKG.Get_PK_For_Validation (
138 new_references.restricted_attendance_type
139 ) THEN
140 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
141 IGS_GE_MSG_STACK.ADD;
142 App_Exception.Raise_Exception;
143 END IF;
144 END IF;
145
146 IF (((old_references.encmb_course_group_cd =
147 new_references.encmb_course_group_cd)) OR
148 ((new_references.encmb_course_group_cd IS NULL))) THEN
149 NULL;
150 ELSE
151 IF NOT IGS_PS_GRP_PKG.Get_PK_For_Validation (
152 new_references.encmb_course_group_cd
153 )THEN
154 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
155 IGS_GE_MSG_STACK.ADD;
156 App_Exception.Raise_Exception;
157 END IF;
158 END IF;
159
160 IF (((old_references.decision_org_unit_cd =
161 new_references.decision_org_unit_cd) AND
162 (old_references.decision_ou_start_dt =
163 new_references.decision_ou_start_dt)) OR
164 ((new_references.decision_org_unit_cd IS NULL) OR
165 (new_references.decision_ou_start_dt IS NULL))) THEN
166 NULL;
167 ELSE
168 IF NOT IGS_OR_UNIT_PKG.Get_PK_For_Validation (
169 new_references.decision_org_unit_cd,
170 new_references.decision_ou_start_dt
171 )THEN
172 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
173 IGS_GE_MSG_STACK.ADD;
174 App_Exception.Raise_Exception;
175 END IF;
176 END IF;
177
178 IF (((old_references.progression_outcome_type =
179 new_references.progression_outcome_type)) OR
180 ((new_references.progression_outcome_type IS NULL))) THEN
181 NULL;
182 ELSE
183 IF NOT IGS_PR_OU_TYPE_PKG.Get_PK_For_Validation (
184 new_references.progression_outcome_type
185 )THEN
186 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
187 IGS_GE_MSG_STACK.ADD;
188 App_Exception.Raise_Exception;
189 END IF;
190 END IF;
191
192 IF (((old_references.progression_rule_cat =
193 new_references.progression_rule_cat) AND
194 (old_references.pro_pra_sequence_number =
195 new_references.pro_pra_sequence_number) AND
196 (old_references.pro_sequence_number =
197 new_references.pro_sequence_number)) OR
198 ((new_references.progression_rule_cat IS NULL) OR
199 (new_references.pro_pra_sequence_number IS NULL) OR
200 (new_references.pro_sequence_number IS NULL))) THEN
201 NULL;
202 ELSE
203 IF NOT IGS_PR_RU_OU_PKG.Get_PK_For_Validation (
204 new_references.progression_rule_cat,
205 new_references.pro_pra_sequence_number,
206 new_references.pro_sequence_number
207 )THEN
208 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
209 IGS_GE_MSG_STACK.ADD;
210 App_Exception.Raise_Exception;
211 END IF;
212 END IF;
213
214 IF (((old_references.person_id = new_references.person_id) AND
215 (old_references.course_cd = new_references.course_cd) AND
216 (old_references.prg_cal_type = new_references.prg_cal_type) AND
217 (old_references.prg_ci_sequence_number =
218 new_references.prg_ci_sequence_number) AND
219 (old_references.progression_rule_cat =
220 new_references.progression_rule_cat) AND
221 (old_references.pra_sequence_number =
222 new_references.pra_sequence_number) AND
223 (old_references.rule_check_dt = new_references.rule_check_dt)) OR
224 ((new_references.person_id IS NULL) OR
225 (new_references.course_cd IS NULL) OR
226 (new_references.prg_cal_type IS NULL) OR
227 (new_references.prg_ci_sequence_number IS NULL) OR
228 (new_references.progression_rule_cat IS NULL) OR
229 (new_references.pra_sequence_number IS NULL) OR
230 (new_references.rule_check_dt IS NULL))) THEN
231 NULL;
232 ELSE
233 IF NOT IGS_PR_SDT_PR_RU_CK_PKG.Get_PK_For_Validation (
234 new_references.person_id,
235 new_references.course_cd,
236 new_references.prg_cal_type,
237 new_references.prg_ci_sequence_number,
238 new_references.progression_rule_cat,
239 new_references.pra_sequence_number,
240 new_references.rule_check_dt
241 )THEN
242 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
243 IGS_GE_MSG_STACK.ADD;
244 App_Exception.Raise_Exception;
245 END IF;
246 END IF;
247
248 END Check_Parent_Existance;
249
250 PROCEDURE Check_Child_Existance AS
251 BEGIN
252 igs_pr_ru_appl_pkg.get_fk_igs_pr_stdnt_pr_ou (
253 old_references.person_id,
254 old_references.course_cd,
255 old_references.sequence_number
256 );
257
258 igs_pr_stdnt_pr_ps_pkg.get_fk_igs_pr_stdnt_pr_ou (
259 old_references.person_id,
260 old_references.course_cd,
261 old_references.sequence_number
262 );
263
264 igs_pr_stdnt_pr_unit_pkg.get_fk_igs_pr_stdnt_pr_ou (
265 old_references.person_id,
266 old_references.course_cd,
267 old_references.sequence_number
268 );
269
270 igs_pr_sdt_pr_unt_st_pkg.get_fk_igs_pr_stdnt_pr_ou (
271 old_references.person_id,
272 old_references.course_cd,
273 old_references.sequence_number
274 );
275
276 igs_pr_stdnt_pr_awd_pkg.get_fk_igs_pr_stdnt_pr_ou (
277 old_references.person_id,
278 old_references.course_cd,
279 old_references.sequence_number
280 );
281
282 igs_pr_stdnt_pr_fnd_pkg.get_fk_igs_pr_stdnt_pr_ou(
283 old_references.person_id,
284 old_references.course_cd,
285 old_references.sequence_number
286 );
287 END Check_Child_Existance;
288
289 FUNCTION Get_PK_For_Validation (
290 x_person_id IN NUMBER,
291 x_course_cd IN VARCHAR2,
292 x_sequence_number IN NUMBER
293 ) RETURN BOOLEAN AS
294
295 CURSOR cur_rowid IS
296 SELECT ROWID
297 FROM igs_pr_stdnt_pr_ou_all
298 WHERE person_id = x_person_id
299 AND course_cd = x_course_cd
300 AND sequence_number = x_sequence_number
301 FOR UPDATE NOWAIT;
302
303 lv_rowid cur_rowid%RowType;
304
305 BEGIN
306
307 Open cur_rowid;
308 Fetch cur_rowid INTO lv_rowid;
309 IF (cur_rowid%FOUND) THEN
310 Close cur_rowid;
311 Return (TRUE);
312 ELSE
313 Close cur_rowid;
314 Return (FALSE);
315 END IF;
316
317 END Get_PK_For_Validation;
318
319 PROCEDURE GET_FK_IGS_EN_ATD_TYPE (
320 x_attendance_type IN VARCHAR2
321 ) AS
322
323 CURSOR cur_rowid IS
324 SELECT ROWID
325 FROM igs_pr_stdnt_pr_ou_all
326 WHERE restricted_attendance_type = x_attendance_type ;
327
328 lv_rowid cur_rowid%RowType;
329
330 BEGIN
331
332 Open cur_rowid;
333 Fetch cur_rowid INTO lv_rowid;
334 IF (cur_rowid%FOUND) THEN
335 Fnd_Message.Set_Name ('IGS', 'IGS_PR_SPO_ATT_FK');
336 IGS_GE_MSG_STACK.ADD;
337 Close cur_rowid;
338 App_Exception.Raise_Exception;
339 Return;
340 END IF;
341 Close cur_rowid;
342
343 END GET_FK_IGS_EN_ATD_TYPE;
344
345
346 PROCEDURE GET_FK_IGS_OR_UNIT (
347 x_org_unit_cd IN VARCHAR2,
348 x_start_dt IN DATE
349 ) AS
350
351 CURSOR cur_rowid IS
352 SELECT ROWID
353 FROM igs_pr_stdnt_pr_ou_all
354 WHERE decision_org_unit_cd = x_org_unit_cd
355 AND decision_ou_start_dt = x_start_dt ;
356
357 lv_rowid cur_rowid%RowType;
358
359 BEGIN
360
361 Open cur_rowid;
362 Fetch cur_rowid INTO lv_rowid;
363 IF (cur_rowid%FOUND) THEN
364 Fnd_Message.Set_Name ('IGS', 'IGS_PR_SPO_OU_FK');
365 IGS_GE_MSG_STACK.ADD;
366 Close cur_rowid;
367 App_Exception.Raise_Exception;
368 Return;
369 END IF;
370 Close cur_rowid;
371
372 END GET_FK_IGS_OR_UNIT;
373
374 PROCEDURE GET_FK_IGS_PR_OU_TYPE (
375 x_progression_outcome_type IN VARCHAR2
376 ) AS
377
378 CURSOR cur_rowid IS
379 SELECT ROWID
380 FROM igs_pr_stdnt_pr_ou_all
381 WHERE progression_outcome_type = x_progression_outcome_type ;
382
383 lv_rowid cur_rowid%RowType;
384
385 BEGIN
386
387 Open cur_rowid;
388 Fetch cur_rowid INTO lv_rowid;
389 IF (cur_rowid%FOUND) THEN
390 Fnd_Message.Set_Name ('IGS', 'IGS_PR_SPO_POT_FK');
391 IGS_GE_MSG_STACK.ADD;
392 Close cur_rowid;
393 App_Exception.Raise_Exception;
394 Return;
395 END IF;
396 Close cur_rowid;
397
398 END GET_FK_IGS_PR_OU_TYPE;
399
400 PROCEDURE GET_FK_IGS_PR_RU_OU (
401 x_progression_rule_cat IN VARCHAR2,
402 x_pra_sequence_number IN NUMBER,
403 x_sequence_number IN NUMBER
404 ) AS
405
406 CURSOR cur_rowid IS
407 SELECT ROWID
408 FROM igs_pr_stdnt_pr_ou_all
409 WHERE progression_rule_cat = x_progression_rule_cat
410 AND pro_pra_sequence_number = x_pra_sequence_number
411 AND pro_sequence_number = x_sequence_number ;
412
413 lv_rowid cur_rowid%RowType;
414
415 BEGIN
416
417 Open cur_rowid;
418 Fetch cur_rowid INTO lv_rowid;
419 IF (cur_rowid%FOUND) THEN
420 Fnd_Message.Set_Name ('IGS', 'IGS_PR_SPO_PRO_FK');
421 IGS_GE_MSG_STACK.ADD;
422 Close cur_rowid;
423 App_Exception.Raise_Exception;
424 Return;
425 END IF;
426 Close cur_rowid;
427
428 END GET_FK_IGS_PR_RU_OU;
429
430 PROCEDURE GET_FK_IGS_PR_SDT_PR_RU_CK (
431 x_person_id IN NUMBER,
432 x_course_cd IN VARCHAR2,
433 x_prg_cal_type IN VARCHAR2,
434 x_prg_ci_sequence_number IN NUMBER,
435 x_progression_rule_cat IN VARCHAR2,
436 x_pra_sequence_number IN NUMBER,
437 x_rule_check_dt IN DATE
438 ) AS
439
440 CURSOR cur_rowid IS
441 SELECT ROWID
442 FROM igs_pr_stdnt_pr_ou_all
443 WHERE person_id = x_person_id
444 AND course_cd = x_course_cd
445 AND prg_cal_type = x_prg_cal_type
446 AND prg_ci_sequence_number = x_prg_ci_sequence_number
447 AND rule_check_dt = x_rule_check_dt
448 AND progression_rule_cat = x_progression_rule_cat
449 AND pra_sequence_number = x_pra_sequence_number ;
450
451 lv_rowid cur_rowid%RowType;
452
453 BEGIN
454
455 Open cur_rowid;
456 Fetch cur_rowid INTO lv_rowid;
457 IF (cur_rowid%FOUND) THEN
458 Fnd_Message.Set_Name ('IGS', 'IGS_PR_SPO_SPRC_FK');
459 IGS_GE_MSG_STACK.ADD;
460 Close cur_rowid;
461 App_Exception.Raise_Exception;
462 Return;
463 END IF;
464 Close cur_rowid;
465
466 END GET_FK_IGS_PR_SDT_PR_RU_CK;
467
468 PROCEDURE BeforeInsertUpdate( p_action VARCHAR2 ) AS
469 /*
470 || Created By : anilk
471 || Created On : 25-FEB-2003
472 || Known limitations, enhancements or remarks :
473 || Change History :
474 || Who When What
475 || (reverse chronological order - newest change first)
476 */
477 CURSOR c_parent (
478 cp_progression_rule_cat IGS_PR_RU_OU.progression_rule_cat%TYPE,
479 cp_pro_pra_sequence_number IGS_PR_RU_OU.pra_sequence_number%TYPE,
480 cp_sequence_number IGS_PR_RU_OU.sequence_number%TYPE ) IS
481 SELECT 1
482 FROM IGS_PR_RU_OU pro
483 WHERE pro.progression_rule_cat = cp_progression_rule_cat AND
484 pro.pra_sequence_number = cp_pro_pra_sequence_number AND
485 pro.sequence_number = cp_sequence_number AND
486 pro.logical_delete_dt is NULL;
487 l_dummy NUMBER;
488 BEGIN
489 IF (p_action = 'INSERT') AND new_references.progression_rule_cat IS NOT NULL
490 AND new_references.pro_pra_sequence_number IS NOT NULL
491 AND new_references.pro_sequence_number IS NOT NULL THEN
492 OPEN c_parent( new_references.progression_rule_cat, new_references.pro_pra_sequence_number, new_references.pro_sequence_number );
493 FETCH c_parent INTO l_dummy;
494 IF c_parent%NOTFOUND THEN
495 CLOSE c_parent;
496 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
497 IGS_GE_MSG_STACK.ADD;
498 APP_EXCEPTION.RAISE_EXCEPTION;
499 END IF;
500 CLOSE c_parent;
501 ELSIF(p_action = 'UPDATE') THEN
502 IF NVL(new_references.progression_rule_cat,'1') <> NVL(old_references.progression_rule_cat,'1') OR
503 NVL(new_references.pro_pra_sequence_number,1) <> NVL(old_references.pro_pra_sequence_number,1) OR
504 NVL(new_references.pro_sequence_number,1) <> NVL(old_references.pro_sequence_number,1) THEN
505 OPEN c_parent( new_references.progression_rule_cat, new_references.pro_pra_sequence_number, new_references.pro_sequence_number );
506 FETCH c_parent INTO l_dummy;
507 IF c_parent%NOTFOUND THEN
508 CLOSE c_parent;
509 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
510 IGS_GE_MSG_STACK.ADD;
511 APP_EXCEPTION.RAISE_EXCEPTION;
512 END IF;
513 CLOSE c_parent;
514 END IF;
515 END IF;
516 END BeforeInsertUpdate;
517
518 PROCEDURE Before_DML (
519 p_action IN VARCHAR2,
520 x_rowid IN VARCHAR2 ,
521 x_prg_cal_type IN VARCHAR2,
522 x_prg_ci_sequence_number IN NUMBER,
523 x_rule_check_dt IN DATE ,
524 x_progression_rule_cat IN VARCHAR2,
525 x_pra_sequence_number IN NUMBER,
526 x_pro_sequence_number IN NUMBER,
527 x_progression_outcome_type IN VARCHAR2,
528 x_duration IN NUMBER ,
529 x_duration_type IN VARCHAR2,
530 x_decision_status IN VARCHAR2,
531 x_decision_dt IN DATE,
532 x_decision_org_unit_cd IN VARCHAR2,
533 x_decision_ou_start_dt IN DATE,
534 x_applied_dt IN DATE,
535 x_show_cause_expiry_dt IN DATE,
536 x_show_cause_dt IN DATE,
537 x_show_cause_outcome_dt IN DATE,
538 x_show_cause_outcome_type IN VARCHAR2,
539 x_appeal_expiry_dt IN DATE,
540 x_appeal_dt IN DATE,
541 x_appeal_outcome_dt IN DATE,
542 x_appeal_outcome_type IN VARCHAR2,
543 x_encmb_course_group_cd IN VARCHAR2,
544 x_restricted_enrolment_cp IN NUMBER,
545 x_restricted_attendance_type IN VARCHAR2,
546 x_comments IN VARCHAR2,
547 x_show_cause_comments IN VARCHAR2,
548 x_appeal_comments IN VARCHAR2,
549 x_person_id IN NUMBER,
550 x_course_cd IN VARCHAR2,
551 x_sequence_number IN NUMBER,
552 x_expiry_dt IN DATE,
553 x_pro_pra_sequence_number IN NUMBER ,
554 x_creation_date IN DATE,
555 x_created_by IN NUMBER,
556 x_last_update_date IN DATE,
557 x_last_updated_by IN NUMBER,
558 x_last_update_login IN NUMBER,
559 x_org_id IN NUMBER
560 ) AS
561
562 CURSOR c_sysout_type IS
563 SELECT s_progression_outcome_type
564 FROM igs_pr_ou_type
565 WHERE progression_outcome_type = x_progression_outcome_type;
566 lvSystem_Outcome_Type IGS_PR_ou_type.S_PROGRESSION_OUTCOME_TYPE%TYPE;
567
568 BEGIN
569
570 Set_Column_Values (
571 p_action,
572 x_rowid,
573 x_prg_cal_type,
574 x_prg_ci_sequence_number,
575 x_rule_check_dt,
576 x_progression_rule_cat,
577 x_pra_sequence_number,
578 x_pro_sequence_number,
579 x_progression_outcome_type,
580 x_duration,
581 x_duration_type,
582 x_decision_status,
583 x_decision_dt,
584 x_decision_org_unit_cd,
585 x_decision_ou_start_dt,
586 x_applied_dt,
587 x_show_cause_expiry_dt,
588 x_show_cause_dt,
589 x_show_cause_outcome_dt,
590 x_show_cause_outcome_type,
591 x_appeal_expiry_dt,
592 x_appeal_dt,
593 x_appeal_outcome_dt,
594 x_appeal_outcome_type,
595 x_encmb_course_group_cd,
596 x_restricted_enrolment_cp,
597 x_restricted_attendance_type,
598 x_comments,
599 x_show_cause_comments,
600 x_appeal_comments,
601 x_person_id,
602 x_course_cd,
603 x_sequence_number,
604 x_expiry_dt,
605 x_pro_pra_sequence_number,
606 x_creation_date,
607 x_created_by,
608 x_last_update_date,
609 x_last_updated_by,
610 x_last_update_login,
611 x_org_id
612 );
613
614 -- Added this code as part of progression build, bug 2138644, pmarada
615 IF (p_action = 'INSERT' OR P_ACTION = 'UPDATE') THEN
616 OPEN c_sysout_type;
617 FETCH c_sysout_type INTO lvSystem_Outcome_Type;
618 CLOSE c_sysout_type;
619 IF lvSystem_Outcome_Type = 'SUSPENSION' THEN
620 IF (x_duration IS NULL OR x_duration_type IS NULL) THEN
621 Fnd_Message.Set_Name('IGS','IGS_PR_SUSP_OUC_MUST_DURAT');
622 IGS_GE_MSG_STACK.ADD;
623 App_Exception.Raise_Exception;
624 END IF;
625 END IF;
626 IF lvSystem_Outcome_Type <> 'PROBATION' THEN
627 IF x_duration_type = 'EFFECTIVE' THEN
628 Fnd_Message.Set_Name('IGS','IGS_PR_PROB_OUC_ONLY_EFCT_DUR');
629 IGS_GE_MSG_STACK.ADD;
630 App_Exception.Raise_Exception;
631 END IF;
632 END IF;
633 END IF; -- end of the added code, pmarada
634
635 IF (p_action = 'INSERT') THEN
636 -- Call all the procedures related to Before Insert.
637 check_parent_existance;
638 IF get_pk_for_validation (
639 new_references.person_id ,
640 new_references.course_cd,
641 new_references.sequence_number) THEN
642 Fnd_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
643 IGS_GE_MSG_STACK.ADD;
644 App_Exception.Raise_Exception;
645 END IF;
646 check_constraints;
647 ELSIF (p_action = 'UPDATE') THEN
648 -- Call all the procedures related to Before Update.
649 check_parent_existance;
650 check_constraints;
651 ELSIF (p_action = 'DELETE') THEN
652 -- Call all the procedures related to Before Delete.
653 check_child_existance;
654 ELSIF (p_action = 'VALIDATE_INSERT') THEN
655 IF Get_PK_For_Validation (
656 new_references.person_id ,
657 new_references.course_cd,
658 new_references.sequence_number) THEN
659 Fnd_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
660 IGS_GE_MSG_STACK.ADD;
661 App_Exception.Raise_Exception;
662 END IF;
663 check_constraints;
664 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
665 check_constraints;
666 ELSIF (p_action = 'VALIDATE_DELETE') THEN
667 check_child_existance;
668 END IF;
669
670 -- anilk, bug#2784198
671 BeforeInsertUpdate(p_action);
672
673 END Before_DML;
674
675 PROCEDURE INSERT_ROW (
676 X_ROWID IN OUT NOCOPY VARCHAR2,
677 X_PERSON_ID IN NUMBER,
678 X_COURSE_CD IN VARCHAR2,
679 X_SEQUENCE_NUMBER IN NUMBER,
680 X_PRG_CAL_TYPE IN VARCHAR2,
681 X_PRG_CI_SEQUENCE_NUMBER IN NUMBER,
682 X_RULE_CHECK_DT IN DATE,
683 X_PROGRESSION_RULE_CAT IN VARCHAR2,
684 X_PRA_SEQUENCE_NUMBER IN NUMBER,
685 X_PRO_SEQUENCE_NUMBER IN NUMBER,
686 X_PROGRESSION_OUTCOME_TYPE IN VARCHAR2,
687 X_DURATION IN NUMBER,
688 X_DURATION_TYPE IN VARCHAR2,
689 X_DECISION_STATUS IN VARCHAR2,
690 X_DECISION_DT IN DATE,
691 X_DECISION_ORG_UNIT_CD IN VARCHAR2,
692 X_DECISION_OU_START_DT IN DATE,
693 X_APPLIED_DT IN DATE,
694 X_SHOW_CAUSE_EXPIRY_DT IN DATE,
695 X_SHOW_CAUSE_DT IN DATE,
696 X_SHOW_CAUSE_OUTCOME_DT IN DATE,
697 X_SHOW_CAUSE_OUTCOME_TYPE IN VARCHAR2,
698 X_APPEAL_EXPIRY_DT IN DATE,
699 X_APPEAL_DT IN DATE,
700 X_APPEAL_OUTCOME_DT IN DATE,
701 X_APPEAL_OUTCOME_TYPE IN VARCHAR2,
702 X_ENCMB_COURSE_GROUP_CD IN VARCHAR2,
703 X_RESTRICTED_ENROLMENT_CP IN NUMBER,
704 X_RESTRICTED_ATTENDANCE_TYPE IN VARCHAR2,
705 X_COMMENTS IN VARCHAR2,
706 X_SHOW_CAUSE_COMMENTS IN VARCHAR2,
707 X_APPEAL_COMMENTS IN VARCHAR2,
708 X_EXPIRY_DT IN DATE,
709 X_PRO_PRA_SEQUENCE_NUMBER IN NUMBER,
710 X_MODE IN VARCHAR2,
711 X_ORG_ID IN NUMBER
712 ) AS
713 CURSOR C IS
714 SELECT ROWID
715 FROM igs_pr_stdnt_pr_ou_all
716 WHERE person_id = x_person_id
717 AND course_cd = x_course_cd
718 AND sequence_number = x_sequence_number;
719 X_LAST_UPDATE_DATE DATE;
720 X_LAST_UPDATED_BY NUMBER;
721 X_LAST_UPDATE_LOGIN NUMBER;
722 BEGIN
723 X_LAST_UPDATE_DATE := SYSDATE;
724 IF (X_MODE = 'I') THEN
725 X_LAST_UPDATED_BY := 1;
726 X_LAST_UPDATE_LOGIN := 0;
727 ELSIF (X_MODE IN ('R', 'S')) THEN
728 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
729 IF X_LAST_UPDATED_BY IS NULL THEN
730 X_LAST_UPDATED_BY := -1;
731 END IF;
732 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
733 IF X_LAST_UPDATE_LOGIN IS NULL THEN
734 X_LAST_UPDATE_LOGIN := -1;
735 END IF;
736 ELSE
737 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
738 IGS_GE_MSG_STACK.ADD;
739 app_exception.raise_exception;
740 END IF;
741
742 Before_DML (
743 p_action =>'INSERT',
744 x_rowid => x_rowid ,
745 x_prg_cal_type => x_prg_cal_type ,
746 x_prg_ci_sequence_number => x_prg_ci_sequence_number ,
747 x_rule_check_dt => x_rule_check_dt ,
748 x_progression_rule_cat => x_progression_rule_cat ,
749 x_pra_sequence_number => x_pra_sequence_number ,
750 x_pro_sequence_number => x_pro_sequence_number ,
751 x_progression_outcome_type => x_progression_outcome_type ,
752 x_duration => x_duration ,
753 x_duration_type => x_duration_type ,
754 x_decision_status => x_decision_status ,
755 x_decision_dt => x_decision_dt ,
756 x_decision_org_unit_cd => x_decision_org_unit_cd ,
757 x_decision_ou_start_dt => x_decision_ou_start_dt ,
758 x_applied_dt => x_applied_dt ,
759 x_show_cause_expiry_dt => x_show_cause_expiry_dt ,
760 x_show_cause_dt => x_show_cause_dt ,
761 x_show_cause_outcome_dt => x_show_cause_outcome_dt ,
762 x_show_cause_outcome_type => x_show_cause_outcome_type ,
763 x_appeal_expiry_dt => x_appeal_expiry_dt ,
764 x_appeal_dt => x_appeal_dt ,
765 x_appeal_outcome_dt => x_appeal_outcome_dt ,
766 x_appeal_outcome_type => x_appeal_outcome_type ,
767 x_encmb_course_group_cd => x_encmb_course_group_cd ,
768 x_restricted_enrolment_cp => x_restricted_enrolment_cp ,
769 x_restricted_attendance_type => x_restricted_attendance_type ,
770 x_comments => x_comments ,
771 x_show_cause_comments => x_show_cause_comments ,
772 x_appeal_comments => x_appeal_comments ,
773 x_person_id => x_person_id ,
774 x_course_cd => x_course_cd ,
775 x_sequence_number => x_sequence_number ,
776 x_expiry_dt => x_expiry_dt,
777 x_pro_pra_sequence_number => x_pro_pra_sequence_number,
778 x_creation_date => x_last_update_date ,
779 x_created_by => x_last_updated_by ,
780 x_last_update_date => x_last_update_date ,
781 x_last_updated_by => x_last_updated_by ,
782 x_last_update_login => x_last_update_login,
783 x_org_id => igs_ge_gen_003.get_org_id
784 ) ;
785
786 IF (x_mode = 'S') THEN
787 igs_sc_gen_001.set_ctx('R');
788 END IF;
789 INSERT INTO igs_pr_stdnt_pr_ou_all (
790 person_id,
791 course_cd,
792 sequence_number,
793 prg_cal_type,
794 prg_ci_sequence_number,
795 rule_check_dt,
796 progression_rule_cat,
797 pra_sequence_number,
798 pro_sequence_number,
799 progression_outcome_type,
800 duration,
801 duration_type,
802 decision_status,
803 decision_dt,
804 decision_org_unit_cd,
805 decision_ou_start_dt,
806 applied_dt,
807 show_cause_expiry_dt,
808 show_cause_dt,
809 show_cause_outcome_dt,
810 show_cause_outcome_type,
811 appeal_expiry_dt,
812 appeal_dt,
813 appeal_outcome_dt,
814 appeal_outcome_type,
815 encmb_course_group_cd,
816 restricted_enrolment_cp,
817 restricted_attendance_type,
818 comments,
819 show_cause_comments,
820 appeal_comments,
821 expiry_dt,
822 pro_pra_sequence_number,
823 creation_date,
824 created_by,
825 last_update_date,
826 last_updated_by,
827 last_update_login,
828 org_id
829 ) VALUES (
830 new_references.person_id,
831 new_references.course_cd,
832 new_references.sequence_number,
833 new_references.prg_cal_type,
834 new_references.prg_ci_sequence_number,
835 new_references.rule_check_dt,
836 new_references.progression_rule_cat,
837 new_references.pra_sequence_number,
838 new_references.pro_sequence_number,
839 new_references.progression_outcome_type,
840 new_references.duration,
841 new_references.duration_type,
842 new_references.decision_status,
843 new_references.decision_dt,
844 new_references.decision_org_unit_cd,
845 new_references.decision_ou_start_dt,
846 new_references.applied_dt,
847 new_references.show_cause_expiry_dt,
848 new_references.show_cause_dt,
849 new_references.show_cause_outcome_dt,
850 new_references.show_cause_outcome_type,
851 new_references.appeal_expiry_dt,
852 new_references.appeal_dt,
853 new_references.appeal_outcome_dt,
854 new_references.appeal_outcome_type,
855 new_references.encmb_course_group_cd,
856 new_references.restricted_enrolment_cp,
857 new_references.restricted_attendance_type,
858 new_references.comments,
859 new_references.show_cause_comments,
860 new_references.appeal_comments,
861 new_references.expiry_dt,
862 new_references.pro_pra_sequence_number,
863 x_last_update_date,
864 x_last_updated_by,
865 x_last_update_date,
866 x_last_updated_by,
867 x_last_update_login,
868 new_references.org_id
869 );
870 IF (x_mode = 'S') THEN
871 igs_sc_gen_001.unset_ctx('R');
872 END IF;
873
874
875 OPEN C;
876 FETCH C INTO x_rowid;
877 IF (C%NOTFOUND) THEN
878 CLOSE C;
879 RAISE NO_DATA_FOUND;
880 END IF;
881 CLOSE C;
882 apply_appr_outcome;
883
884 EXCEPTION
885 WHEN OTHERS THEN
886 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
887 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
888 fnd_message.set_token ('ERR_CD', SQLCODE);
889 igs_ge_msg_stack.add;
890 igs_sc_gen_001.unset_ctx('R');
891 app_exception.raise_exception;
892 ELSE
893 igs_sc_gen_001.unset_ctx('R');
894 RAISE;
895 END IF;
896 END INSERT_ROW;
897
898 PROCEDURE LOCK_ROW (
899 X_ROWID IN VARCHAR2,
900 X_PERSON_ID IN NUMBER,
901 X_COURSE_CD IN VARCHAR2,
902 X_SEQUENCE_NUMBER IN NUMBER,
903 X_PRG_CAL_TYPE IN VARCHAR2,
904 X_PRG_CI_SEQUENCE_NUMBER IN NUMBER,
905 X_RULE_CHECK_DT IN DATE,
906 X_PROGRESSION_RULE_CAT IN VARCHAR2,
907 X_PRA_SEQUENCE_NUMBER IN NUMBER,
908 X_PRO_SEQUENCE_NUMBER IN NUMBER,
909 X_PROGRESSION_OUTCOME_TYPE IN VARCHAR2,
910 X_DURATION IN NUMBER,
911 X_DURATION_TYPE IN VARCHAR2,
912 X_DECISION_STATUS IN VARCHAR2,
913 X_DECISION_DT IN DATE,
914 X_DECISION_ORG_UNIT_CD IN VARCHAR2,
915 X_DECISION_OU_START_DT IN DATE,
916 X_APPLIED_DT IN DATE,
917 X_SHOW_CAUSE_EXPIRY_DT IN DATE,
918 X_SHOW_CAUSE_DT IN DATE,
919 X_SHOW_CAUSE_OUTCOME_DT IN DATE,
920 X_SHOW_CAUSE_OUTCOME_TYPE IN VARCHAR2,
921 X_APPEAL_EXPIRY_DT IN DATE,
922 X_APPEAL_DT IN DATE,
923 X_APPEAL_OUTCOME_DT IN DATE,
924 X_APPEAL_OUTCOME_TYPE IN VARCHAR2,
925 X_ENCMB_COURSE_GROUP_CD IN VARCHAR2,
926 X_RESTRICTED_ENROLMENT_CP IN NUMBER,
927 X_RESTRICTED_ATTENDANCE_TYPE IN VARCHAR2,
928 X_COMMENTS IN VARCHAR2,
929 X_SHOW_CAUSE_COMMENTS IN VARCHAR2,
930 X_APPEAL_COMMENTS IN VARCHAR2,
931 X_EXPIRY_DT IN DATE,
932 X_PRO_PRA_SEQUENCE_NUMBER IN NUMBER
933 ) AS
934 CURSOR c1 IS SELECT
935 prg_cal_type,
936 prg_ci_sequence_number,
937 rule_check_dt,
938 progression_rule_cat,
939 pra_sequence_number,
940 pro_sequence_number,
941 progression_outcome_type,
942 duration,
943 duration_type,
944 decision_status,
945 decision_dt,
946 decision_org_unit_cd,
947 decision_ou_start_dt,
948 applied_dt,
949 show_cause_expiry_dt,
950 show_cause_dt,
951 show_cause_outcome_dt,
952 show_cause_outcome_type,
953 appeal_expiry_dt,
954 appeal_dt,
955 appeal_outcome_dt,
956 appeal_outcome_type,
957 encmb_course_group_cd,
958 restricted_enrolment_cp,
959 restricted_attendance_type,
960 comments,
961 show_cause_comments,
962 appeal_comments,
963 expiry_dt,
964 pro_pra_sequence_number
965 FROM igs_pr_stdnt_pr_ou_all
966 WHERE ROWID = x_rowid FOR UPDATE NOWAIT;
967 tlinfo c1%ROWTYPE;
968
969 BEGIN
970 OPEN c1;
971 FETCH c1 INTO tlinfo;
972 IF (c1%NOTFOUND) THEN
973 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
974 IGS_GE_MSG_STACK.ADD;
975 CLOSE c1;
976 app_exception.raise_exception;
977 RETURN;
978 END IF;
979 CLOSE c1;
980
981 IF ( ((tlinfo.PRG_CAL_TYPE = X_PRG_CAL_TYPE)
982 OR ((tlinfo.PRG_CAL_TYPE IS NULL)
983 AND (X_PRG_CAL_TYPE IS NULL)))
984 AND ((tlinfo.PRG_CI_SEQUENCE_NUMBER = X_PRG_CI_SEQUENCE_NUMBER)
985 OR ((tlinfo.PRG_CI_SEQUENCE_NUMBER IS NULL)
986 AND (X_PRG_CI_SEQUENCE_NUMBER IS NULL)))
987 AND ((tlinfo.RULE_CHECK_DT = X_RULE_CHECK_DT)
988 OR ((tlinfo.RULE_CHECK_DT IS NULL)
989 AND (X_RULE_CHECK_DT IS NULL)))
990 AND ((tlinfo.PROGRESSION_RULE_CAT = X_PROGRESSION_RULE_CAT)
991 OR ((tlinfo.PROGRESSION_RULE_CAT IS NULL)
992 AND (X_PROGRESSION_RULE_CAT IS NULL)))
993 AND ((tlinfo.PRA_SEQUENCE_NUMBER = X_PRA_SEQUENCE_NUMBER)
994 OR ((tlinfo.PRA_SEQUENCE_NUMBER IS NULL)
995 AND (X_PRA_SEQUENCE_NUMBER IS NULL)))
996 AND ((tlinfo.PRO_SEQUENCE_NUMBER = X_PRO_SEQUENCE_NUMBER)
997 OR ((tlinfo.PRO_SEQUENCE_NUMBER IS NULL)
998 AND (X_PRO_SEQUENCE_NUMBER IS NULL)))
999 AND ((tlinfo.PROGRESSION_OUTCOME_TYPE = X_PROGRESSION_OUTCOME_TYPE)
1000 OR ((tlinfo.PROGRESSION_OUTCOME_TYPE IS NULL)
1001 AND (X_PROGRESSION_OUTCOME_TYPE IS NULL)))
1002 AND ((tlinfo.DURATION = X_DURATION)
1003 OR ((tlinfo.DURATION IS NULL)
1004 AND (X_DURATION IS NULL)))
1005 AND ((tlinfo.DURATION_TYPE = X_DURATION_TYPE)
1006 OR ((tlinfo.DURATION_TYPE IS NULL)
1007 AND (X_DURATION_TYPE IS NULL)))
1008 AND (tlinfo.DECISION_STATUS = X_DECISION_STATUS)
1009 AND ((TRUNC(tlinfo.DECISION_DT) = TRUNC(X_DECISION_DT))
1010 OR ((tlinfo.DECISION_DT IS NULL)
1011 AND (X_DECISION_DT IS NULL)))
1012 AND ((tlinfo.DECISION_ORG_UNIT_CD = X_DECISION_ORG_UNIT_CD)
1013 OR ((tlinfo.DECISION_ORG_UNIT_CD IS NULL)
1014 AND (X_DECISION_ORG_UNIT_CD IS NULL)))
1015 AND ((TRUNC(tlinfo.DECISION_OU_START_DT) =
1016 TRUNC(X_DECISION_OU_START_DT))
1017 OR ((tlinfo.DECISION_OU_START_DT IS NULL)
1018 AND (X_DECISION_OU_START_DT IS NULL)))
1019 AND ((TRUNC(tlinfo.APPLIED_DT) = TRUNC(X_APPLIED_DT))
1020 OR ((tlinfo.APPLIED_DT IS NULL)
1021 AND (X_APPLIED_DT IS NULL)))
1022 AND ((TRUNC(tlinfo.SHOW_CAUSE_EXPIRY_DT) =
1023 TRUNC(X_SHOW_CAUSE_EXPIRY_DT))
1024 OR ((tlinfo.SHOW_CAUSE_EXPIRY_DT IS NULL)
1025 AND (X_SHOW_CAUSE_EXPIRY_DT IS NULL)))
1026 AND ((TRUNC(tlinfo.SHOW_CAUSE_DT) = TRUNC(X_SHOW_CAUSE_DT))
1027 OR ((tlinfo.SHOW_CAUSE_DT IS NULL)
1028 AND (X_SHOW_CAUSE_DT IS NULL)))
1029 AND ((TRUNC(tlinfo.SHOW_CAUSE_OUTCOME_DT) =
1030 TRUNC(X_SHOW_CAUSE_OUTCOME_DT))
1031 OR ((tlinfo.SHOW_CAUSE_OUTCOME_DT IS NULL)
1032 AND (X_SHOW_CAUSE_OUTCOME_DT IS NULL)))
1033 AND ((tlinfo.SHOW_CAUSE_OUTCOME_TYPE = X_SHOW_CAUSE_OUTCOME_TYPE)
1034 OR ((tlinfo.SHOW_CAUSE_OUTCOME_TYPE IS NULL)
1035 AND (X_SHOW_CAUSE_OUTCOME_TYPE IS NULL)))
1036 AND ((TRUNC(tlinfo.APPEAL_EXPIRY_DT) = TRUNC(X_APPEAL_EXPIRY_DT))
1037 OR ((tlinfo.APPEAL_EXPIRY_DT IS NULL)
1038 AND (X_APPEAL_EXPIRY_DT IS NULL)))
1039 AND ((TRUNC(tlinfo.APPEAL_DT) = TRUNC(X_APPEAL_DT))
1040 OR ((tlinfo.APPEAL_DT IS NULL)
1041 AND (X_APPEAL_DT IS NULL)))
1042 AND ((TRUNC(tlinfo.APPEAL_OUTCOME_DT) = TRUNC(X_APPEAL_OUTCOME_DT))
1043 OR ((tlinfo.APPEAL_OUTCOME_DT IS NULL)
1044 AND (X_APPEAL_OUTCOME_DT IS NULL)))
1045 AND ((tlinfo.APPEAL_OUTCOME_TYPE = X_APPEAL_OUTCOME_TYPE)
1046 OR ((tlinfo.APPEAL_OUTCOME_TYPE IS NULL)
1047 AND (X_APPEAL_OUTCOME_TYPE IS NULL)))
1048 AND ((tlinfo.ENCMB_COURSE_GROUP_CD = X_ENCMB_COURSE_GROUP_CD)
1049 OR ((tlinfo.ENCMB_COURSE_GROUP_CD IS NULL)
1050 AND (X_ENCMB_COURSE_GROUP_CD IS NULL)))
1051 AND ((tlinfo.RESTRICTED_ENROLMENT_CP = X_RESTRICTED_ENROLMENT_CP)
1052 OR ((tlinfo.RESTRICTED_ENROLMENT_CP IS NULL)
1053 AND (X_RESTRICTED_ENROLMENT_CP IS NULL)))
1054 AND ((tlinfo.RESTRICTED_ATTENDANCE_TYPE = X_RESTRICTED_ATTENDANCE_TYPE)
1055 OR ((tlinfo.RESTRICTED_ATTENDANCE_TYPE IS NULL)
1056 AND (X_RESTRICTED_ATTENDANCE_TYPE IS NULL)))
1057 AND ((tlinfo.COMMENTS = X_COMMENTS)
1058 OR ((tlinfo.COMMENTS IS NULL)
1059 AND (X_COMMENTS IS NULL)))
1060 AND ((tlinfo.SHOW_CAUSE_COMMENTS = X_SHOW_CAUSE_COMMENTS)
1061 OR ((tlinfo.SHOW_CAUSE_COMMENTS IS NULL)
1062 AND (X_SHOW_CAUSE_COMMENTS IS NULL)))
1063 AND ((tlinfo.APPEAL_COMMENTS = X_APPEAL_COMMENTS)
1064 OR ((tlinfo.APPEAL_COMMENTS IS NULL)
1065 AND (X_APPEAL_COMMENTS IS NULL)))
1066 AND ((TRUNC(tlinfo.EXPIRY_DT) = TRUNC(X_EXPIRY_DT))
1067 OR ((tlinfo.EXPIRY_DT IS NULL)
1068 AND (X_EXPIRY_DT IS NULL)))
1069 AND ((tlinfo.PRO_PRA_SEQUENCE_NUMBER = X_PRO_PRA_SEQUENCE_NUMBER)
1070 OR ((tlinfo.PRO_PRA_SEQUENCE_NUMBER IS NULL)
1071 AND (X_PRO_PRA_SEQUENCE_NUMBER IS NULL)))
1072 ) THEN
1073 NULL;
1074 ELSE
1075 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1076 IGS_GE_MSG_STACK.ADD;
1077 app_exception.raise_exception;
1078 END IF;
1079 RETURN;
1080 END LOCK_ROW;
1081
1082 PROCEDURE UPDATE_ROW (
1083 X_ROWID IN VARCHAR2,
1084 X_PERSON_ID IN NUMBER,
1085 X_COURSE_CD IN VARCHAR2,
1086 X_SEQUENCE_NUMBER IN NUMBER,
1087 X_PRG_CAL_TYPE IN VARCHAR2,
1088 X_PRG_CI_SEQUENCE_NUMBER IN NUMBER,
1089 X_RULE_CHECK_DT IN DATE,
1090 X_PROGRESSION_RULE_CAT IN VARCHAR2,
1091 X_PRA_SEQUENCE_NUMBER IN NUMBER,
1092 X_PRO_SEQUENCE_NUMBER IN NUMBER,
1093 X_PROGRESSION_OUTCOME_TYPE IN VARCHAR2,
1094 X_DURATION IN NUMBER,
1095 X_DURATION_TYPE IN VARCHAR2,
1096 X_DECISION_STATUS IN VARCHAR2,
1097 X_DECISION_DT IN DATE,
1098 X_DECISION_ORG_UNIT_CD IN VARCHAR2,
1099 X_DECISION_OU_START_DT IN DATE,
1100 X_APPLIED_DT IN DATE,
1101 X_SHOW_CAUSE_EXPIRY_DT IN DATE,
1102 X_SHOW_CAUSE_DT IN DATE,
1103 X_SHOW_CAUSE_OUTCOME_DT IN DATE,
1104 X_SHOW_CAUSE_OUTCOME_TYPE IN VARCHAR2,
1105 X_APPEAL_EXPIRY_DT IN DATE,
1106 X_APPEAL_DT IN DATE,
1107 X_APPEAL_OUTCOME_DT IN DATE,
1108 X_APPEAL_OUTCOME_TYPE IN VARCHAR2,
1109 X_ENCMB_COURSE_GROUP_CD IN VARCHAR2,
1110 X_RESTRICTED_ENROLMENT_CP IN NUMBER,
1111 X_RESTRICTED_ATTENDANCE_TYPE IN VARCHAR2,
1112 X_COMMENTS IN VARCHAR2,
1113 X_SHOW_CAUSE_COMMENTS IN VARCHAR2,
1114 X_APPEAL_COMMENTS IN VARCHAR2,
1115 X_EXPIRY_DT IN DATE,
1116 X_PRO_PRA_SEQUENCE_NUMBER IN NUMBER,
1117 X_MODE IN VARCHAR2
1118 ) AS
1119 X_LAST_UPDATE_DATE DATE;
1120 X_LAST_UPDATED_BY NUMBER;
1121 X_LAST_UPDATE_LOGIN NUMBER;
1122 BEGIN
1123 X_LAST_UPDATE_DATE := SYSDATE;
1124 IF (X_MODE = 'I') THEN
1125 X_LAST_UPDATED_BY := 1;
1126 X_LAST_UPDATE_LOGIN := 0;
1127 ELSIF (X_MODE IN ('R', 'S')) THEN
1128 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1129 IF X_LAST_UPDATED_BY IS NULL THEN
1130 X_LAST_UPDATED_BY := -1;
1131 END IF;
1132 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1133 IF X_LAST_UPDATE_LOGIN IS NULL THEN
1134 X_LAST_UPDATE_LOGIN := -1;
1135 END IF;
1136 ELSE
1137 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1138 IGS_GE_MSG_STACK.ADD;
1139 app_exception.raise_exception;
1140 END IF;
1141
1142 Before_DML (
1143 p_action =>'UPDATE',
1144 x_rowid => x_rowid ,
1145 x_prg_cal_type => x_prg_cal_type ,
1146 x_prg_ci_sequence_number => x_prg_ci_sequence_number ,
1147 x_rule_check_dt => x_rule_check_dt ,
1148 x_progression_rule_cat => x_progression_rule_cat ,
1149 x_pra_sequence_number => x_pra_sequence_number ,
1150 x_pro_sequence_number => x_pro_sequence_number ,
1151 x_progression_outcome_type => x_progression_outcome_type ,
1152 x_duration => x_duration ,
1153 x_duration_type => x_duration_type ,
1154 x_decision_status => x_decision_status ,
1155 x_decision_dt => x_decision_dt ,
1156 x_decision_org_unit_cd => x_decision_org_unit_cd ,
1157 x_decision_ou_start_dt => x_decision_ou_start_dt ,
1158 x_applied_dt => x_applied_dt ,
1159 x_show_cause_expiry_dt => x_show_cause_expiry_dt ,
1160 x_show_cause_dt => x_show_cause_dt ,
1161 x_show_cause_outcome_dt => x_show_cause_outcome_dt ,
1162 x_show_cause_outcome_type => x_show_cause_outcome_type ,
1163 x_appeal_expiry_dt => x_appeal_expiry_dt ,
1164 x_appeal_dt => x_appeal_dt ,
1165 x_appeal_outcome_dt => x_appeal_outcome_dt ,
1166 x_appeal_outcome_type => x_appeal_outcome_type ,
1167 x_encmb_course_group_cd => x_encmb_course_group_cd ,
1168 x_restricted_enrolment_cp => x_restricted_enrolment_cp ,
1169 x_restricted_attendance_type => x_restricted_attendance_type ,
1170 x_comments => x_comments ,
1171 x_show_cause_comments => x_show_cause_comments ,
1172 x_appeal_comments => x_appeal_comments ,
1173 x_person_id => x_person_id ,
1174 x_course_cd => x_course_cd ,
1175 x_sequence_number => x_sequence_number ,
1176 x_expiry_dt => x_expiry_dt,
1177 x_pro_pra_sequence_number => x_pro_pra_sequence_number,
1178 x_creation_date => x_last_update_date ,
1179 x_created_by => x_last_updated_by ,
1180 x_last_update_date => x_last_update_date ,
1181 x_last_updated_by => x_last_updated_by ,
1182 x_last_update_login => x_last_update_login
1183 );
1184
1185 IF (x_mode = 'S') THEN
1186 igs_sc_gen_001.set_ctx('R');
1187 END IF;
1188 UPDATE igs_pr_stdnt_pr_ou_all SET
1189 prg_cal_type = new_references.prg_cal_type,
1190 prg_ci_sequence_number = new_references.prg_ci_sequence_number,
1191 rule_check_dt = new_references.rule_check_dt,
1192 progression_rule_cat = new_references.progression_rule_cat,
1193 pra_sequence_number = new_references.pra_sequence_number,
1194 pro_sequence_number = new_references.pro_sequence_number,
1195 progression_outcome_type = new_references.progression_outcome_type,
1196 duration = new_references.duration,
1197 duration_type = new_references.duration_type,
1198 decision_status = new_references.decision_status,
1199 decision_dt = new_references.decision_dt,
1200 decision_org_unit_cd = new_references.decision_org_unit_cd,
1201 decision_ou_start_dt = new_references.decision_ou_start_dt,
1202 applied_dt = new_references.applied_dt,
1203 show_cause_expiry_dt = new_references.show_cause_expiry_dt,
1204 show_cause_dt = new_references.show_cause_dt,
1205 show_cause_outcome_dt = new_references.show_cause_outcome_dt,
1206 show_cause_outcome_type = new_references.show_cause_outcome_type,
1207 appeal_expiry_dt = new_references.appeal_expiry_dt,
1208 appeal_dt = new_references.appeal_dt,
1209 appeal_outcome_dt = new_references.appeal_outcome_dt,
1210 appeal_outcome_type = new_references.appeal_outcome_type,
1211 encmb_course_group_cd = new_references.encmb_course_group_cd,
1212 restricted_enrolment_cp = new_references.restricted_enrolment_cp,
1213 restricted_attendance_type = new_references.restricted_attendance_type,
1214 comments = new_references.comments,
1215 show_cause_comments = new_references.show_cause_comments,
1216 appeal_comments = new_references.appeal_comments,
1217 expiry_dt = new_references.expiry_dt,
1218 pro_pra_sequence_number = new_references.pro_pra_sequence_number,
1219 last_update_date = x_last_update_date,
1220 last_updated_by = x_last_updated_by,
1221 last_update_login = x_last_update_login
1222 WHERE ROWID = X_ROWID;
1223 IF (SQL%NOTFOUND) THEN
1224 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1225 igs_ge_msg_stack.add;
1226 igs_sc_gen_001.unset_ctx('R');
1227 app_exception.raise_exception;
1228 END IF;
1229 IF (x_mode = 'S') THEN
1230 igs_sc_gen_001.unset_ctx('R');
1231 END IF;
1232
1233 apply_appr_outcome;
1234
1235 EXCEPTION
1236 WHEN OTHERS THEN
1237 IF (SQLCODE = (-28115)) THEN
1238 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1239 fnd_message.set_token ('ERR_CD', SQLCODE);
1240 igs_ge_msg_stack.add;
1241 igs_sc_gen_001.unset_ctx('R');
1242 app_exception.raise_exception;
1243 ELSE
1244 igs_sc_gen_001.unset_ctx('R');
1245 RAISE;
1246 END IF;
1247 END UPDATE_ROW;
1248
1249
1250 PROCEDURE ADD_ROW (
1251 X_ROWID IN OUT NOCOPY VARCHAR2,
1252 X_PERSON_ID IN NUMBER,
1253 X_COURSE_CD IN VARCHAR2,
1254 X_SEQUENCE_NUMBER IN NUMBER,
1255 X_PRG_CAL_TYPE IN VARCHAR2,
1256 X_PRG_CI_SEQUENCE_NUMBER IN NUMBER,
1257 X_RULE_CHECK_DT IN DATE,
1258 X_PROGRESSION_RULE_CAT IN VARCHAR2,
1259 X_PRA_SEQUENCE_NUMBER IN NUMBER,
1260 X_PRO_SEQUENCE_NUMBER IN NUMBER,
1261 X_PROGRESSION_OUTCOME_TYPE IN VARCHAR2,
1262 X_DURATION IN NUMBER,
1263 X_DURATION_TYPE IN VARCHAR2,
1264 X_DECISION_STATUS IN VARCHAR2,
1265 X_DECISION_DT IN DATE,
1266 X_DECISION_ORG_UNIT_CD IN VARCHAR2,
1267 X_DECISION_OU_START_DT IN DATE,
1268 X_APPLIED_DT IN DATE,
1269 X_SHOW_CAUSE_EXPIRY_DT IN DATE,
1270 X_SHOW_CAUSE_DT IN DATE,
1271 X_SHOW_CAUSE_OUTCOME_DT IN DATE,
1272 X_SHOW_CAUSE_OUTCOME_TYPE IN VARCHAR2,
1273 X_APPEAL_EXPIRY_DT IN DATE,
1274 X_APPEAL_DT IN DATE,
1275 X_APPEAL_OUTCOME_DT IN DATE,
1276 X_APPEAL_OUTCOME_TYPE IN VARCHAR2,
1277 X_ENCMB_COURSE_GROUP_CD IN VARCHAR2,
1278 X_RESTRICTED_ENROLMENT_CP IN NUMBER,
1279 X_RESTRICTED_ATTENDANCE_TYPE IN VARCHAR2,
1280 X_COMMENTS IN VARCHAR2,
1281 X_SHOW_CAUSE_COMMENTS IN VARCHAR2,
1282 X_APPEAL_COMMENTS IN VARCHAR2,
1283 X_EXPIRY_DT IN DATE,
1284 X_PRO_PRA_SEQUENCE_NUMBER IN NUMBER,
1285 X_MODE IN VARCHAR2 ,
1286 X_ORG_ID IN NUMBER
1287 ) AS
1288 CURSOR c1 IS
1289 SELECT ROWID
1290 FROM igs_pr_stdnt_pr_ou_all
1291 WHERE person_id = x_person_id
1292 AND course_cd = x_course_cd
1293 AND sequence_number = x_sequence_number;
1294 BEGIN
1295 OPEN c1;
1296 FETCH c1 INTO X_ROWID;
1297 IF (c1%NOTFOUND) THEN
1298 CLOSE c1;
1299 INSERT_ROW (
1300 X_ROWID,
1301 X_PERSON_ID,
1302 X_COURSE_CD,
1303 X_SEQUENCE_NUMBER,
1304 X_PRG_CAL_TYPE,
1305 X_PRG_CI_SEQUENCE_NUMBER,
1306 X_RULE_CHECK_DT,
1307 X_PROGRESSION_RULE_CAT,
1308 X_PRA_SEQUENCE_NUMBER,
1309 X_PRO_SEQUENCE_NUMBER,
1310 X_PROGRESSION_OUTCOME_TYPE,
1311 X_DURATION,
1312 X_DURATION_TYPE,
1313 X_DECISION_STATUS,
1314 X_DECISION_DT,
1315 X_DECISION_ORG_UNIT_CD,
1316 X_DECISION_OU_START_DT,
1317 X_APPLIED_DT,
1318 X_SHOW_CAUSE_EXPIRY_DT,
1319 X_SHOW_CAUSE_DT,
1320 X_SHOW_CAUSE_OUTCOME_DT,
1321 X_SHOW_CAUSE_OUTCOME_TYPE,
1322 X_APPEAL_EXPIRY_DT,
1323 X_APPEAL_DT,
1324 X_APPEAL_OUTCOME_DT,
1325 X_APPEAL_OUTCOME_TYPE,
1326 X_ENCMB_COURSE_GROUP_CD,
1327 X_RESTRICTED_ENROLMENT_CP,
1328 X_RESTRICTED_ATTENDANCE_TYPE,
1329 X_COMMENTS,
1330 X_SHOW_CAUSE_COMMENTS,
1331 X_APPEAL_COMMENTS,
1332 X_EXPIRY_DT,
1333 X_PRO_PRA_SEQUENCE_NUMBER,
1334 X_MODE,
1335 X_ORG_ID);
1336 RETURN;
1337 END IF;
1338 CLOSE c1;
1339 UPDATE_ROW (
1340 X_ROWID ,
1341 X_PERSON_ID,
1342 X_COURSE_CD,
1343 X_SEQUENCE_NUMBER,
1344 X_PRG_CAL_TYPE,
1345 X_PRG_CI_SEQUENCE_NUMBER,
1346 X_RULE_CHECK_DT,
1347 X_PROGRESSION_RULE_CAT,
1348 X_PRA_SEQUENCE_NUMBER,
1349 X_PRO_SEQUENCE_NUMBER,
1350 X_PROGRESSION_OUTCOME_TYPE,
1351 X_DURATION,
1352 X_DURATION_TYPE,
1353 X_DECISION_STATUS,
1354 X_DECISION_DT,
1355 X_DECISION_ORG_UNIT_CD,
1356 X_DECISION_OU_START_DT,
1357 X_APPLIED_DT,
1358 X_SHOW_CAUSE_EXPIRY_DT,
1359 X_SHOW_CAUSE_DT,
1360 X_SHOW_CAUSE_OUTCOME_DT,
1361 X_SHOW_CAUSE_OUTCOME_TYPE,
1362 X_APPEAL_EXPIRY_DT,
1363 X_APPEAL_DT,
1364 X_APPEAL_OUTCOME_DT,
1365 X_APPEAL_OUTCOME_TYPE,
1366 X_ENCMB_COURSE_GROUP_CD,
1367 X_RESTRICTED_ENROLMENT_CP,
1368 X_RESTRICTED_ATTENDANCE_TYPE,
1369 X_COMMENTS,
1370 X_SHOW_CAUSE_COMMENTS,
1371 X_APPEAL_COMMENTS,
1372 X_EXPIRY_DT,
1373 X_PRO_PRA_SEQUENCE_NUMBER,
1374 X_MODE
1375 );
1376 END ADD_ROW;
1377
1378 PROCEDURE DELETE_ROW (
1379 X_ROWID IN VARCHAR2,
1380 x_mode IN VARCHAR2
1381 ) AS
1382 BEGIN
1383 Before_DML (
1384 p_action => 'DELETE',
1385 x_rowid => X_ROWID
1386 ) ;
1387
1388 IF (x_mode = 'S') THEN
1389 igs_sc_gen_001.set_ctx('R');
1390 END IF;
1391 DELETE FROM igs_pr_stdnt_pr_ou_all
1392 WHERE ROWID = x_rowid;
1393 IF (SQL%NOTFOUND) THEN
1394 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1395 igs_ge_msg_stack.add;
1396 igs_sc_gen_001.unset_ctx('R');
1397 app_exception.raise_exception;
1398 END IF;
1399 IF (x_mode = 'S') THEN
1400 igs_sc_gen_001.unset_ctx('R');
1401 END IF;
1402
1403
1404 END DELETE_ROW;
1405
1406 PROCEDURE Check_Constraints (
1407 Column_Name IN VARCHAR2 ,
1408 Column_Value IN VARCHAR2
1409 ) AS
1410 BEGIN
1411 IF Column_Name IS NULL THEN
1412 NULL;
1413 ELSIF UPPER (Column_name) = 'SEQUENCE_NUMBER' THEN
1414 new_references.SEQUENCE_NUMBER:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
1415 ELSIF UPPER (Column_name) = 'PRG_CI_SEQUENCE_NUMBER' THEN
1416 new_references.PRG_CI_SEQUENCE_NUMBER:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
1417 ELSIF UPPER (Column_name) = 'PRA_SEQUENCE_NUMBER' THEN
1418 new_references.PRA_SEQUENCE_NUMBER:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
1419 ELSIF UPPER (Column_name) = 'PRO_SEQUENCE_NUMBER' THEN
1420 new_references.PRO_SEQUENCE_NUMBER:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
1421 ELSIF UPPER (Column_name) = 'DURATION' THEN
1422 new_references.DURATION:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
1423 ELSIF UPPER (Column_name) = 'DURATION_TYPE' THEN
1424 new_references.DURATION_TYPE:= COLUMN_VALUE ;
1425 ELSIF UPPER (Column_name) = 'DECISION_STATUS' THEN
1426 new_references.DECISION_STATUS:= COLUMN_VALUE ;
1427 ELSIF UPPER (Column_name) = 'SHOW_CAUSE_OUTCOME_TYPE' THEN
1428 new_references.SHOW_CAUSE_OUTCOME_TYPE:= COLUMN_VALUE ;
1429 ELSIF UPPER (Column_name) = 'APPEAL_OUTCOME_TYPE' THEN
1430 new_references.APPEAL_OUTCOME_TYPE:= COLUMN_VALUE ;
1431 ELSIF UPPER (Column_name) = 'RESTRICTED_ENROLMENT_CP' THEN
1432 new_references.RESTRICTED_ENROLMENT_CP:= IGS_GE_NUMBER.to_num(COLUMN_VALUE);
1433 ELSIF UPPER (Column_name) = 'COURSE_CD' THEN
1434 new_references.COURSE_CD:= COLUMN_VALUE ;
1435 ELSIF UPPER (Column_name) = 'DECISION_ORG_UNIT_CD' THEN
1436 new_references.DECISION_ORG_UNIT_CD:= COLUMN_VALUE ;
1437 ELSIF UPPER (Column_name) = 'ENCMB_COURSE_GROUP_CD' THEN
1438 new_references.ENCMB_COURSE_GROUP_CD:= COLUMN_VALUE ;
1439 ELSIF UPPER (Column_name) = 'PRG_CAL_TYPE' THEN
1440 new_references.PRG_CAL_TYPE:= COLUMN_VALUE ;
1441 ELSIF UPPER (Column_name) = 'PROGRESSION_OUTCOME_TYPE' THEN
1442 new_references.PROGRESSION_OUTCOME_TYPE:= COLUMN_VALUE ;
1443 ELSIF UPPER (Column_name) = 'PROGRESSION_RULE_CAT' THEN
1444 new_references.PROGRESSION_RULE_CAT:= COLUMN_VALUE ;
1445 ELSIF UPPER (Column_name) = 'RESTRICTED_ATTENDANCE_TYPE' THEN
1446 new_references.RESTRICTED_ATTENDANCE_TYPE:= COLUMN_VALUE ;
1447 END IF;
1448
1449 IF UPPER (Column_name) = 'SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
1450 IF new_references.sequence_number < 1 OR
1451 new_references.SEQUENCE_NUMBER > 999999 THEN
1452 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1453 IGS_GE_MSG_STACK.ADD;
1454 App_Exception.Raise_Exception ;
1455 END IF;
1456 END IF ;
1457
1458 IF UPPER (Column_name) = 'PRG_CI_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
1459 IF new_references.PRG_CI_SEQUENCE_NUMBER < 1 OR
1460 new_references.PRG_CI_SEQUENCE_NUMBER > 999999 THEN
1461 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1462 IGS_GE_MSG_STACK.ADD;
1463 App_Exception.Raise_Exception ;
1464 END IF;
1465 END IF ;
1466
1467 IF UPPER (Column_name) = 'PRA_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
1468 IF new_references.PRA_SEQUENCE_NUMBER < 1 OR
1469 new_references.PRA_SEQUENCE_NUMBER > 999999 THEN
1470 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1471 IGS_GE_MSG_STACK.ADD;
1472 App_Exception.Raise_Exception ;
1473 END IF;
1474 END IF ;
1475
1476 IF UPPER (Column_name) = 'PRO_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
1477 IF new_references.PRO_SEQUENCE_NUMBER < 1 OR
1478 new_references.PRO_SEQUENCE_NUMBER > 999999 THEN
1479 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1480 IGS_GE_MSG_STACK.ADD;
1481 App_Exception.Raise_Exception ;
1482 END IF;
1483 END IF ;
1484
1485 IF UPPER (Column_name) = 'DURATION' OR COLUMN_NAME IS NULL THEN
1486 IF new_references.DURATION < 1 OR new_references.DURATION > 999 THEN
1487 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1488 IGS_GE_MSG_STACK.ADD;
1489 App_Exception.Raise_Exception ;
1490 END IF;
1491 END IF ;
1492
1493 IF UPPER (Column_name) = 'DURATION_TYPE' OR COLUMN_NAME IS NULL THEN
1494 IF new_references.DURATION_TYPE<> UPPER (new_references.DURATION_TYPE) THEN
1495 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1496 IGS_GE_MSG_STACK.ADD;
1497 App_Exception.Raise_Exception ;
1498 END IF;
1499 IF new_references.DURATION_TYPE NOT IN ('NORMAL' , 'EFFECTIVE') THEN
1500 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1501 IGS_GE_MSG_STACK.ADD;
1502 App_Exception.Raise_Exception ;
1503 END IF;
1504 END IF ;
1505
1506 IF UPPER (Column_name) = 'DECISION_STATUS' OR COLUMN_NAME IS NULL THEN
1507 IF new_references.DECISION_STATUS <> UPPER (new_references.DECISION_STATUS)
1508 THEN
1509 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1510 IGS_GE_MSG_STACK.ADD;
1511 App_Exception.Raise_Exception ;
1512 END IF;
1513 IF new_references.DECISION_STATUS NOT IN ('PENDING', 'APPROVED',
1514 'WAIVED', 'CANCELLED', 'REMOVED') THEN
1515 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1516 IGS_GE_MSG_STACK.ADD;
1517 App_Exception.Raise_Exception ;
1518 END IF;
1519 END IF ;
1520
1521 IF UPPER (Column_name) = 'SHOW_CAUSE_OUTCOME_TYPE' OR COLUMN_NAME IS NULL THEN
1522 IF new_references.SHOW_CAUSE_OUTCOME_TYPE <>
1523 UPPER (new_references.SHOW_CAUSE_OUTCOME_TYPE) THEN
1524 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1525 IGS_GE_MSG_STACK.ADD;
1526 App_Exception.Raise_Exception ;
1527 END IF;
1528 IF new_references.SHOW_CAUSE_OUTCOME_TYPE NOT IN ('UPHELD', 'DISMISSED')
1529 THEN
1530 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1531 IGS_GE_MSG_STACK.ADD;
1532 App_Exception.Raise_Exception ;
1533 END IF;
1534 END IF ;
1535
1536 IF UPPER (Column_name) = 'APPEAL_OUTCOME_TYPE' OR COLUMN_NAME IS NULL THEN
1537 IF new_references.APPEAL_OUTCOME_TYPE <>
1538 UPPER (new_references.APPEAL_OUTCOME_TYPE) THEN
1539 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1540 IGS_GE_MSG_STACK.ADD;
1541 App_Exception.Raise_Exception ;
1542 END IF;
1543 IF new_references.APPEAL_OUTCOME_TYPE NOT IN ('UPHELD' , 'DISMISSED') THEN
1544 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1545 IGS_GE_MSG_STACK.ADD;
1546 App_Exception.Raise_Exception ;
1547 END IF;
1548 END IF ;
1549
1550 IF UPPER (Column_name) = 'RESTRICTED_ENROLMENT_CP' OR COLUMN_NAME IS NULL THEN
1551 IF new_references.RESTRICTED_ENROLMENT_CP < 0 OR
1552 new_references.RESTRICTED_ENROLMENT_CP > 999.999 THEN
1553 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1554 IGS_GE_MSG_STACK.ADD;
1555 App_Exception.Raise_Exception ;
1556 END IF;
1557 END IF ;
1558
1559 IF UPPER (Column_name) = 'COURSE_CD' OR COLUMN_NAME IS NULL THEN
1560 IF new_references.COURSE_CD<> UPPER (new_references.COURSE_CD) THEN
1561 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1562 IGS_GE_MSG_STACK.ADD;
1563 App_Exception.Raise_Exception ;
1564 END IF;
1565 END IF ;
1566
1567 IF UPPER (Column_name) = 'ENCMB_COURSE_GROUP_CD' OR COLUMN_NAME IS NULL THEN
1568 IF new_references.ENCMB_COURSE_GROUP_CD <>
1569 UPPER (new_references.ENCMB_COURSE_GROUP_CD) THEN
1570 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1571 IGS_GE_MSG_STACK.ADD;
1572 App_Exception.Raise_Exception ;
1573 END IF;
1574 END IF ;
1575
1576 IF UPPER (Column_name) = 'PRG_CAL_TYPE' OR COLUMN_NAME IS NULL THEN
1577 IF new_references.PRG_CAL_TYPE<> UPPER (new_references.PRG_CAL_TYPE) THEN
1578 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1579 IGS_GE_MSG_STACK.ADD;
1580 App_Exception.Raise_Exception ;
1581 END IF;
1582 END IF ;
1583
1584 IF UPPER (Column_name) = 'PROGRESSION_OUTCOME_TYPE' OR COLUMN_NAME IS NULL
1585 THEN
1586 IF new_references.PROGRESSION_OUTCOME_TYPE <>
1587 UPPER (new_references.PROGRESSION_OUTCOME_TYPE) THEN
1588 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1589 IGS_GE_MSG_STACK.ADD;
1590 App_Exception.Raise_Exception ;
1591 END IF;
1592 END IF ;
1593
1594 IF UPPER (Column_name) = 'PROGRESSION_RULE_CAT' OR COLUMN_NAME IS NULL THEN
1595 IF new_references.PROGRESSION_RULE_CAT <>
1596 UPPER (new_references.PROGRESSION_RULE_CAT) THEN
1597 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1598 IGS_GE_MSG_STACK.ADD;
1599 App_Exception.Raise_Exception ;
1600 END IF;
1601 END IF ;
1602
1603 IF UPPER (Column_name) = 'RESTRICTED_ATTENDANCE_TYPE' OR
1604 COLUMN_NAME IS NULL THEN
1605 IF new_references.RESTRICTED_ATTENDANCE_TYPE <>
1606 UPPER (new_references.RESTRICTED_ATTENDANCE_TYPE) THEN
1607 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1608 IGS_GE_MSG_STACK.ADD;
1609 App_Exception.Raise_Exception ;
1610 END IF;
1611 END IF ;
1612
1613 END Check_Constraints;
1614
1615 PROCEDURE apply_appr_outcome AS
1616 --Created by:
1617 --Who When What
1618 --prchandr 09-Oct-2002 Added the New procedure to sent the notification
1619 -- when the changes are done to the outcomes
1620 -- like applying an outcome, approving an outcome,
1621 -- cancelling it, waiving the approved outcomes
1622 -- and the outcome with showcause and appeal dates
1623 CURSOR cur_positive IS
1624 SELECT positive_outcome_ind, description
1625 FROM igs_pr_ou_type pot
1626 WHERE pot.progression_outcome_type =
1627 new_references.progression_outcome_type;
1628 lcur_positive cur_positive%ROWTYPE;
1629 BEGIN
1630 OPEN cur_positive;
1631 FETCH cur_positive INTO lcur_positive;
1632 IF cur_positive%NOTFOUND THEN
1633 CLOSE cur_positive;
1634 END IF;
1635
1636 IF lcur_positive.positive_outcome_ind = 'N' AND
1637 old_references.decision_status <> 'APPROVED' AND
1638 new_references.decision_status = 'APPROVED' AND
1639 new_references.applied_dt IS NULL THEN
1640 igs_pr_stdnt_pr_ou_be_pkg.approve_otcm (
1641 new_references.person_id,
1642 new_references.course_cd,
1643 new_references.sequence_number,
1644 new_references.decision_status,
1645 new_references.decision_dt,
1646 new_references.progression_outcome_type,
1647 lcur_positive.description,
1648 new_references.appeal_expiry_dt,
1649 new_references.show_cause_expiry_dt
1650 );
1651 END IF;
1652
1653 IF lcur_positive.positive_outcome_ind = 'Y' AND
1654 old_references.applied_dt IS NULL AND
1655 new_references.applied_dt IS NOT NULL AND
1656 new_references.decision_status = 'APPROVED' THEN
1657 igs_pr_stdnt_pr_ou_be_pkg.apply_positive_otcm (
1658 new_references.person_id,
1659 new_references.course_cd,
1660 new_references.sequence_number,
1661 new_references.decision_status,
1662 new_references.decision_dt,
1663 new_references.progression_outcome_type,
1664 lcur_positive.description,
1665 new_references.applied_dt
1666 );
1667 END IF;
1668
1669 IF lcur_positive.positive_outcome_ind = 'N' AND
1670 old_references.applied_dt IS NULL AND
1671 new_references.applied_dt IS NOT NULL AND
1672 new_references.decision_status = 'APPROVED' THEN
1673 igs_pr_stdnt_pr_ou_be_pkg.apply_otcm (
1674 new_references.person_id,
1675 new_references.course_cd,
1676 new_references.sequence_number,
1677 new_references.decision_status,
1678 new_references.decision_dt,
1679 new_references.progression_outcome_type,
1680 lcur_positive.description,
1681 new_references.appeal_expiry_dt,
1682 new_references.show_cause_expiry_dt,
1683 new_references.applied_dt
1684 );
1685 END IF;
1686
1687 IF lcur_positive.positive_outcome_ind = 'N' AND
1688 ((old_references.show_cause_outcome_type IS NULL AND
1689 new_references.show_cause_outcome_type IS NOT NULL) OR
1690 (old_references.show_cause_outcome_type IS NOT NULL AND
1691 new_references.show_cause_outcome_type IS NOT NULL AND
1692 new_references.show_cause_outcome_type <>
1693 old_references.show_cause_outcome_type)) THEN
1694 igs_pr_stdnt_pr_ou_be_pkg.show_cause_uph_dsm (
1695 new_references.person_id,
1696 new_references.course_cd,
1697 new_references.sequence_number,
1698 new_references.decision_status,
1699 new_references.decision_dt,
1700 new_references.progression_outcome_type,
1701 lcur_positive.description,
1702 new_references.applied_dt,
1703 new_references.show_cause_dt,
1704 new_references.show_cause_outcome_dt,
1705 new_references.show_cause_outcome_type
1706 );
1707 END IF;
1708
1709 --
1710 -- kdande; 10-Jan-2003; Bug# 2696065; Changed the following condition to
1711 -- raise a workflow notification for appeal outcome type changes
1712 --
1713 IF lcur_positive.positive_outcome_ind = 'N' AND
1714 ((old_references.appeal_outcome_type IS NULL AND
1715 new_references.appeal_outcome_type IS NOT NULL) OR
1716 (old_references.appeal_outcome_type IS NOT NULL AND
1717 new_references.appeal_outcome_type IS NOT NULL AND
1718 new_references.appeal_outcome_type <>
1719 old_references.appeal_outcome_type)) THEN
1720 igs_pr_stdnt_pr_ou_be_pkg.appeal_uph_dsm (
1721 new_references.person_id,
1722 new_references.course_cd,
1723 new_references.sequence_number,
1724 new_references.decision_status,
1725 new_references.decision_dt,
1726 new_references.progression_outcome_type,
1727 lcur_positive.description,
1728 new_references.applied_dt,
1729 new_references.appeal_dt,
1730 new_references.appeal_outcome_dt,
1731 new_references.appeal_outcome_type
1732 );
1733 END IF;
1734
1735 IF lcur_positive.positive_outcome_ind = 'N' AND
1736 old_references.decision_status = 'APPROVED' AND
1737 new_references.decision_status IN ('REMOVED', 'WAIVED', 'CANCELLED') THEN
1738 igs_pr_stdnt_pr_ou_be_pkg.remove_waive_cancel_otcm (
1739 new_references.person_id,
1740 new_references.course_cd,
1741 new_references.sequence_number,
1742 new_references.decision_status,
1743 new_references.decision_dt,
1744 new_references.progression_outcome_type,
1745 lcur_positive.description,
1746 new_references.applied_dt
1747 );
1748 END IF;
1749
1750 END apply_appr_outcome;
1751
1752 END igs_pr_stdnt_pr_ou_pkg;