[Home] [Help]
PACKAGE BODY: APPS.IGS_PR_RU_OU_PKG
Source
1 package body IGS_PR_RU_OU_PKG AS
2 /* $Header: IGSQI13B.pls 115.13 2003/02/26 07:00:32 shtatiko ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PR_RU_OU_ALL%RowType;
6 new_references IGS_PR_RU_OU_ALL%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_progression_rule_cat IN VARCHAR2,
12 x_pra_sequence_number IN NUMBER,
13 x_sequence_number IN NUMBER,
14 x_number_of_failures IN NUMBER,
15 x_progression_outcome_type IN VARCHAR2,
16 x_apply_automatically_ind IN VARCHAR2,
17 x_prg_rule_repeat_fail_type IN VARCHAR2,
18 x_override_show_cause_ind IN VARCHAR2,
19 x_override_appeal_ind IN VARCHAR2,
20 x_duration IN NUMBER,
21 x_duration_type IN VARCHAR2,
22 x_rank IN NUMBER,
23 x_encmb_course_group_cd IN VARCHAR2,
24 x_restricted_enrolment_cp IN NUMBER,
25 x_restricted_attendance_type IN VARCHAR2,
26 x_comments IN VARCHAR2,
27 x_creation_date IN DATE,
28 x_created_by IN NUMBER,
29 x_last_update_date IN DATE,
30 x_last_updated_by IN NUMBER,
31 x_last_update_login IN NUMBER ,
32 x_org_id IN NUMBER,
33 -- anilk, bug#2784198
34 x_logical_delete_dt in DATE
35 ) AS
36
37 CURSOR cur_old_ref_values IS
38 SELECT *
39 FROM IGS_PR_RU_OU_ALL
40 WHERE rowid = x_rowid;
41
42 BEGIN
43
44 l_rowid := x_rowid;
45
46 -- Code for setting the Old and New Reference Values.
47 -- Populate Old Values.
48 Open cur_old_ref_values;
49 Fetch cur_old_ref_values INTO old_references;
50 IF (cur_old_ref_values%NOTFOUND) AND (p_action not in ('INSERT','VALIDATE_INSERT')) THEN
51 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
52 IGS_GE_MSG_STACK.ADD;
53 Close cur_old_ref_values;
54 App_Exception.Raise_Exception;
55
56 Return;
57 END IF;
58 Close cur_old_ref_values;
59
60 -- Populate New Values.
61 new_references.progression_rule_cat := x_progression_rule_cat;
62 new_references.pra_sequence_number := x_pra_sequence_number;
63 new_references.sequence_number := x_sequence_number;
64 new_references.number_of_failures := x_number_of_failures;
65 new_references.progression_outcome_type := x_progression_outcome_type;
66 new_references.apply_automatically_ind := x_apply_automatically_ind;
67 new_references.prg_rule_repeat_fail_type := x_prg_rule_repeat_fail_type;
68 new_references.override_show_cause_ind := x_override_show_cause_ind;
69 new_references.override_appeal_ind := x_override_appeal_ind;
70 new_references.duration := x_duration;
71 new_references.duration_type := x_duration_type;
72 new_references.rank := x_rank;
73 new_references.encmb_course_group_cd := x_encmb_course_group_cd;
74 new_references.restricted_enrolment_cp := x_restricted_enrolment_cp;
75 new_references.restricted_attendance_type := x_restricted_attendance_type;
76 new_references.comments := x_comments;
77 new_references.org_id := x_org_id;
78 -- anilk, bug#2784198
79 new_references.logical_delete_dt := x_logical_delete_dt;
80
81 IF (p_action = 'UPDATE') THEN
82 new_references.creation_date := old_references.creation_date;
83 new_references.created_by := old_references.created_by;
84 ELSE
85 new_references.creation_date := x_creation_date;
86 new_references.created_by := x_created_by;
87 END IF;
88 new_references.last_update_date := x_last_update_date;
89 new_references.last_updated_by := x_last_updated_by;
90 new_references.last_update_login := x_last_update_login;
91
92 END Set_Column_Values;
93 PROCEDURE Check_Uniqueness AS
94
95 BEGIN
96
97 IF GET_UK_FOR_VALIDATION(NEW_REFERENCES.progression_rule_cat,
98 NEW_REFERENCES.pra_sequence_number,
99 NEW_REFERENCES.number_of_failures,
100 NEW_REFERENCES.prg_rule_repeat_fail_type,
101 NEW_REFERENCES.rank)
102 THEN
103 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
104 IGS_GE_MSG_STACK.ADD;
105 App_Exception.Raise_Exception;
106
107 END IF;
108 end Check_Uniqueness;
109
110
111 PROCEDURE Check_Parent_Existance AS
112 BEGIN
113
114 IF (((old_references.restricted_attendance_type = new_references.restricted_attendance_type)) OR
115 ((new_references.restricted_attendance_type IS NULL))) THEN
116 NULL;
117 ELSE
118 IF NOT IGS_EN_ATD_TYPE_PKG.Get_PK_For_Validation (
119 new_references.restricted_attendance_type
120 ) THEN
121 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
122 IGS_GE_MSG_STACK.ADD;
123 App_Exception.Raise_Exception;
124 END IF;
125
126 END IF;
127
128 IF (((old_references.encmb_course_group_cd = new_references.encmb_course_group_cd)) OR
129 ((new_references.encmb_course_group_cd IS NULL))) THEN
130 NULL;
131 ELSE
132 IF NOT IGS_PS_GRP_PKG.Get_PK_For_Validation (
133 new_references.encmb_course_group_cd
134 ) THEN
135 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
136 IGS_GE_MSG_STACK.ADD;
137 App_Exception.Raise_Exception;
138 END IF;
139
140 END IF;
141
142 IF (((old_references.progression_outcome_type = new_references.progression_outcome_type)) OR
143 ((new_references.progression_outcome_type IS NULL))) THEN
144 NULL;
145 ELSE
146 IF NOT IGS_PR_OU_TYPE_PKG.Get_PK_For_Validation (
147 new_references.progression_outcome_type
148 ) THEN
149 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
150 IGS_GE_MSG_STACK.ADD;
151 App_Exception.Raise_Exception;
152 END IF;
153
154 END IF;
155
156 IF (((old_references.progression_rule_cat = new_references.progression_rule_cat) AND
157 (old_references.pra_sequence_number = new_references.pra_sequence_number)) OR
158 ((new_references.progression_rule_cat IS NULL) OR
159 (new_references.pra_sequence_number IS NULL))) THEN
160 NULL;
161 ELSE
162 IF NOT IGS_PR_RU_APPL_PKG.Get_PK_For_Validation (
163 new_references.progression_rule_cat,
164 new_references.pra_sequence_number
165 ) THEN
166 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
167 IGS_GE_MSG_STACK.ADD;
168 App_Exception.Raise_Exception;
169 END IF;
170
171 END IF;
172
173 IF (((old_references.prg_rule_repeat_fail_type = new_references.prg_rule_repeat_fail_type)) OR
174 ((new_references.prg_rule_repeat_fail_type IS NULL))) THEN
175 NULL;
176 ELSE
177 IF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation (
178 'PRG_RULE_REPEAT_FAIL_TYPE',
179 new_references.prg_rule_repeat_fail_type
180 ) THEN
181 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
182 IGS_GE_MSG_STACK.ADD;
183 App_Exception.Raise_Exception;
184 END IF;
185
186 END IF;
187
188 END Check_Parent_Existance;
189
190 PROCEDURE Check_Child_Existance AS
191 BEGIN
192
193 IGS_PR_OU_PS_PKG.GET_FK_IGS_PR_RU_OU (
194 old_references.progression_rule_cat,
195 old_references.pra_sequence_number,
196 old_references.sequence_number
197 );
198
199 IGS_PR_OU_UNIT_PKG.GET_FK_IGS_PR_RU_OU (
200 old_references.progression_rule_cat,
201 old_references.pra_sequence_number,
202 old_references.sequence_number
203 );
204
205 IGS_PR_OU_UNIT_SET_PKG.GET_FK_IGS_PR_RU_OU (
206 old_references.progression_rule_cat,
207 old_references.pra_sequence_number,
208 old_references.sequence_number
209 );
210
211 IGS_PR_RU_APPL_PKG.GET_FK_IGS_PR_RU_OU (
212 old_references.progression_rule_cat,
213 old_references.pra_sequence_number,
214 old_references.sequence_number
215 );
216
217 IGS_PR_STDNT_PR_OU_PKG.GET_FK_IGS_PR_RU_OU (
218 old_references.progression_rule_cat,
219 old_references.pra_sequence_number,
220 old_references.sequence_number
221 );
222
223 IGS_PR_OU_AWD_PKG.GET_FK_IGS_PR_RU_OU (
224 old_references.progression_rule_cat,
225 old_references.pra_sequence_number,
226 old_references.sequence_number
227 );
228
229 IGS_PR_OU_FND_PKG.GET_FK_IGS_PR_RU_OU(
230 old_references.progression_rule_cat,
231 old_references.pra_sequence_number,
232 old_references.sequence_number
233 );
234
235 END Check_Child_Existance;
236
237 FUNCTION Get_PK_For_Validation (
238 x_progression_rule_cat IN VARCHAR2,
239 x_pra_sequence_number IN NUMBER,
240 x_sequence_number IN NUMBER
241 ) RETURN BOOLEAN AS
242
243 CURSOR cur_rowid IS
244 SELECT rowid
245 FROM IGS_PR_RU_OU_ALL
246 WHERE progression_rule_cat = x_progression_rule_cat
247 AND pra_sequence_number = x_pra_sequence_number
248 AND sequence_number = x_sequence_number;
249
250 lv_rowid cur_rowid%RowType;
251
252 BEGIN
253
254 Open cur_rowid;
255 Fetch cur_rowid INTO lv_rowid;
256 IF (cur_rowid%FOUND) THEN
257 Close cur_rowid;
258 Return (TRUE);
259 ELSE
260 Close cur_rowid;
261 Return (FALSE);
262 END IF;
263
264 END Get_PK_For_Validation;
265
266 PROCEDURE GET_FK_IGS_EN_ATD_TYPE (
267 x_attendance_type IN varchar2
268 ) AS
269
270 CURSOR cur_rowid IS
271 SELECT rowid
272 FROM IGS_PR_RU_OU_ALL
273 WHERE restricted_attendance_type = x_attendance_type ;
274
275 lv_rowid cur_rowid%RowType;
276
277 BEGIN
278
279 Open cur_rowid;
280 Fetch cur_rowid INTO lv_rowid;
281 IF (cur_rowid%FOUND) THEN
282 Fnd_Message.Set_Name ('IGS', 'IGS_PR_PRO_ATT_FK');
283 IGS_GE_MSG_STACK.ADD;
284 Close cur_rowid;
285 App_Exception.Raise_Exception;
286
287 Return;
288 END IF;
289 Close cur_rowid;
290
291 END GET_FK_IGS_EN_ATD_TYPE;
292
293 PROCEDURE GET_FK_IGS_PR_OU_TYPE (
294 x_progression_outcome_type IN VARCHAR2
295 ) AS
296
297 CURSOR cur_rowid IS
298 SELECT rowid
299 FROM IGS_PR_RU_OU_ALL
300 WHERE progression_outcome_type = x_progression_outcome_type ;
301
302 lv_rowid cur_rowid%RowType;
303
304 BEGIN
305
306 Open cur_rowid;
307 Fetch cur_rowid INTO lv_rowid;
308 IF (cur_rowid%FOUND) THEN
309 Fnd_Message.Set_Name ('IGS', 'IGS_PR_PRO_POT_FK');
310 IGS_GE_MSG_STACK.ADD;
311 Close cur_rowid;
312 App_Exception.Raise_Exception;
313
314 Return;
315 END IF;
316 Close cur_rowid;
317
318 END GET_FK_IGS_PR_OU_TYPE;
319
320 PROCEDURE GET_FK_IGS_PR_RU_APPL (
321 x_progression_rule_cat IN VARCHAR2,
322 x_sequence_number IN NUMBER
323 ) AS
324
325 CURSOR cur_rowid IS
326 SELECT rowid
327 FROM IGS_PR_RU_OU_ALL
328 WHERE progression_rule_cat = x_progression_rule_cat
329 AND pra_sequence_number = x_sequence_number ;
330
331 lv_rowid cur_rowid%RowType;
332
333 BEGIN
334
335 Open cur_rowid;
336 Fetch cur_rowid INTO lv_rowid;
337 IF (cur_rowid%FOUND) THEN
338 Fnd_Message.Set_Name ('IGS', 'IGS_PR_PRO_PRA_FK');
339 IGS_GE_MSG_STACK.ADD;
340 Close cur_rowid;
341 App_Exception.Raise_Exception;
342
343 Return;
344 END IF;
345 Close cur_rowid;
346
347 END GET_FK_IGS_PR_RU_APPL;
348
349 PROCEDURE GET_FK_IGS_LOOKUPS_VIEW (
350 x_s_prg_rule_repeat_fail_type IN NUMBER
351 ) AS
352
353 CURSOR cur_rowid IS
354 SELECT rowid
355 FROM IGS_PR_RU_OU_ALL
356 WHERE prg_rule_repeat_fail_type = x_s_prg_rule_repeat_fail_type ;
357
358 lv_rowid cur_rowid%RowType;
359
360 BEGIN
361
362 Open cur_rowid;
363 Fetch cur_rowid INTO lv_rowid;
364 IF (cur_rowid%FOUND) THEN
365 Fnd_Message.Set_Name ('IGS', 'IGS_PR_PRO_SPRRFT_FK');
366 IGS_GE_MSG_STACK.ADD;
367 Close cur_rowid;
368 App_Exception.Raise_Exception;
369
370 Return;
371 END IF;
372 Close cur_rowid;
373
374 END GET_FK_IGS_LOOKUPS_VIEW;
375
376 PROCEDURE Before_DML (
377 p_action IN VARCHAR2,
378 x_rowid IN VARCHAR2,
379 x_progression_rule_cat IN VARCHAR2,
380 x_pra_sequence_number IN NUMBER,
381 x_sequence_number IN NUMBER,
382 x_number_of_failures IN NUMBER,
383 x_progression_outcome_type IN VARCHAR2,
384 x_apply_automatically_ind IN VARCHAR2,
385 x_prg_rule_repeat_fail_type IN VARCHAR2,
386 x_override_show_cause_ind IN VARCHAR2,
387 x_override_appeal_ind IN VARCHAR2,
388 x_duration IN NUMBER,
389 x_duration_type IN VARCHAR2,
390 x_rank IN NUMBER,
391 x_encmb_course_group_cd IN VARCHAR2,
392 x_restricted_enrolment_cp IN NUMBER,
393 x_restricted_attendance_type IN VARCHAR2,
394 x_comments IN VARCHAR2,
395 x_creation_date IN DATE,
396 x_created_by IN NUMBER,
397 x_last_update_date IN DATE,
398 x_last_updated_by IN NUMBER,
399 x_last_update_login IN NUMBER,
400 x_org_id IN NUMBER,
401 -- anilk, bug#2784198
402 x_logical_delete_dt in DATE
403 ) AS
404 BEGIN
405
406 Set_Column_Values (
407 p_action,
408 x_rowid,
409 x_progression_rule_cat,
410 x_pra_sequence_number,
411 x_sequence_number,
412 x_number_of_failures,
413 x_progression_outcome_type,
414 x_apply_automatically_ind,
415 x_prg_rule_repeat_fail_type,
416 x_override_show_cause_ind,
417 x_override_appeal_ind,
418 x_duration,
419 x_duration_type,
420 x_rank,
421 x_encmb_course_group_cd,
422 x_restricted_enrolment_cp,
423 x_restricted_attendance_type,
424 x_comments,
425 x_creation_date,
426 x_created_by,
427 x_last_update_date,
428 x_last_updated_by,
429 x_last_update_login ,
430 x_org_id,
431 -- anilk, bug#2784198
432 x_logical_delete_dt
433 );
434
435 IF (p_action = 'INSERT') THEN
436 -- Call all the procedures related to Before Insert.
437 Check_Parent_Existance;
438 IF Get_PK_For_Validation (
439 new_references.progression_rule_cat,
440 new_references.pra_sequence_number,
441 new_references.sequence_number) THEN
442 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
443 IGS_GE_MSG_STACK.ADD;
444 App_Exception.Raise_Exception;
445
446 END IF;
447 CHECK_UNIQUENESS;
448 CHECK_CONSTRAINTS;
449
450 ELSIF (p_action = 'UPDATE') THEN
451 -- Call all the procedures related to Before Update.
452 Check_Parent_Existance;
453 CHECK_UNIQUENESS;
454 CHECK_CONSTRAINTS;
455 ELSIF (p_action = 'DELETE') THEN
456 -- Call all the procedures related to Before Delete.
457 Check_Child_Existance;
458
459 ELSIF (p_action = 'VALIDATE_INSERT') THEN
460 IF Get_PK_For_Validation (
461 new_references.progression_rule_cat,
462 new_references.pra_sequence_number,
463 new_references.sequence_number) THEN
464 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
465 IGS_GE_MSG_STACK.ADD;
466 App_Exception.Raise_Exception;
467
468 END IF;
469 CHECK_UNIQUENESS;
470 CHECK_CONSTRAINTS;
471 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
472 CHECK_UNIQUENESS;
473 CHECK_CONSTRAINTS;
474
475 ELSIF (p_action = 'VALIDATE_DELETE') THEN
476 Check_Child_Existance;
477
478 END IF;
479
480 END Before_DML;
481 PROCEDURE After_DML (
482 p_action IN VARCHAR2,
483 x_rowid IN VARCHAR2
484 ) IS
485 /*************************************************************
486 Created By : jdeekoll
487 Date Created On : 27-12-2000
488 Purpose : Creation of TBH
489 Know limitations, enhancements or remarks
490 Change History
491 Who When What
492
493 (reverse chronological order - newest change first)
494 ***************************************************************/
495
496 BEGIN
497
498 l_rowid := x_rowid;
499
500 IF (p_action = 'INSERT') THEN
501 -- Call all the procedures related to After Insert.
502 Null;
503 ELSIF (p_action = 'UPDATE') THEN
504 -- Call all the procedures related to After Update.
505 Null;
506 ELSIF (p_action = 'DELETE') THEN
507 -- Call all the procedures related to After Delete.
508 Null;
509 END IF;
510 l_rowid:=NULL;
511 END After_DML;
512
513 procedure INSERT_ROW (
514 X_ROWID in out NOCOPY VARCHAR2,
515 X_PROGRESSION_RULE_CAT in VARCHAR2,
516 X_PRA_SEQUENCE_NUMBER in NUMBER,
517 X_SEQUENCE_NUMBER in NUMBER,
518 X_NUMBER_OF_FAILURES in NUMBER,
519 X_PROGRESSION_OUTCOME_TYPE in VARCHAR2,
520 X_APPLY_AUTOMATICALLY_IND in VARCHAR2,
521 X_PRG_RULE_REPEAT_FAIL_TYPE in VARCHAR2,
522 X_OVERRIDE_SHOW_CAUSE_IND in VARCHAR2,
523 X_OVERRIDE_APPEAL_IND in VARCHAR2,
524 X_DURATION in NUMBER,
525 X_DURATION_TYPE in VARCHAR2,
526 X_RANK in NUMBER,
527 X_ENCMB_COURSE_GROUP_CD in VARCHAR2,
528 X_RESTRICTED_ENROLMENT_CP in NUMBER,
529 X_RESTRICTED_ATTENDANCE_TYPE in VARCHAR2,
530 X_COMMENTS in VARCHAR2,
531 X_MODE in VARCHAR2,
532 X_ORG_ID IN NUMBER,
533 -- anilk, bug#2784198
534 X_LOGICAL_DELETE_DT in DATE
535 ) AS
536 cursor C is select ROWID from IGS_PR_RU_OU_ALL
537 where PROGRESSION_RULE_CAT = X_PROGRESSION_RULE_CAT
538 and PRA_SEQUENCE_NUMBER = X_PRA_SEQUENCE_NUMBER
539 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
540 X_LAST_UPDATE_DATE DATE;
541 X_LAST_UPDATED_BY NUMBER;
542 X_LAST_UPDATE_LOGIN NUMBER;
543 begin
544 X_LAST_UPDATE_DATE := SYSDATE;
545 if(X_MODE = 'I') then
546 X_LAST_UPDATED_BY := 1;
547 X_LAST_UPDATE_LOGIN := 0;
548 elsif (X_MODE = 'R') then
549 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
550 if X_LAST_UPDATED_BY is NULL then
551 X_LAST_UPDATED_BY := -1;
552 end if;
553 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
554 if X_LAST_UPDATE_LOGIN is NULL then
555 X_LAST_UPDATE_LOGIN := -1;
556 end if;
557 else
558 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
559 IGS_GE_MSG_STACK.ADD;
560 app_exception.raise_exception;
561 end if;
562 Before_DML (
563 p_action => 'INSERT',
564 x_rowid => x_rowid ,
565 x_progression_rule_cat => x_progression_rule_cat ,
566 x_pra_sequence_number => x_pra_sequence_number ,
567 x_sequence_number => x_sequence_number ,
568 x_number_of_failures => x_number_of_failures ,
569 x_progression_outcome_type => x_progression_outcome_type ,
570 x_apply_automatically_ind => nvl( x_apply_automatically_ind, 'N') ,
571 x_prg_rule_repeat_fail_type => x_prg_rule_repeat_fail_type ,
572 x_override_show_cause_ind => x_override_show_cause_ind ,
573 x_override_appeal_ind => x_override_appeal_ind ,
574 x_duration => x_duration ,
575 x_duration_type => x_duration_type ,
576 x_rank => x_rank ,
577 x_encmb_course_group_cd => x_encmb_course_group_cd ,
578 x_restricted_enrolment_cp => x_restricted_enrolment_cp ,
579 x_restricted_attendance_type => x_restricted_attendance_type ,
580 x_comments => x_comments ,
581 x_creation_date => x_last_update_date ,
582 x_created_by => x_last_updated_by ,
583 x_last_update_date => x_last_update_date ,
584 x_last_updated_by => x_last_updated_by ,
585 x_last_update_login => x_last_update_login,
586 x_org_id => igs_ge_gen_003.get_org_id,
587 -- anilk, bug#2784198
588 x_logical_delete_dt => x_logical_delete_dt
589 ) ;
590 insert into IGS_PR_RU_OU_ALL (
591 PROGRESSION_RULE_CAT,
592 PRA_SEQUENCE_NUMBER,
593 SEQUENCE_NUMBER,
594 NUMBER_OF_FAILURES,
595 PROGRESSION_OUTCOME_TYPE,
596 APPLY_AUTOMATICALLY_IND,
597 PRG_RULE_REPEAT_FAIL_TYPE,
598 OVERRIDE_SHOW_CAUSE_IND,
599 OVERRIDE_APPEAL_IND,
600 DURATION,
601 DURATION_TYPE,
602 RANK,
603 ENCMB_COURSE_GROUP_CD,
604 RESTRICTED_ENROLMENT_CP,
605 RESTRICTED_ATTENDANCE_TYPE,
606 COMMENTS,
607 CREATION_DATE,
608 CREATED_BY,
609 LAST_UPDATE_DATE,
610 LAST_UPDATED_BY,
611 LAST_UPDATE_LOGIN,
612 ORG_ID,
613 -- anilk, bug#2784198
614 LOGICAL_DELETE_DT
615
616 ) values (
617 NEW_REFERENCES.PROGRESSION_RULE_CAT,
618 NEW_REFERENCES.PRA_SEQUENCE_NUMBER,
619 NEW_REFERENCES.SEQUENCE_NUMBER,
620 NEW_REFERENCES.NUMBER_OF_FAILURES,
621 NEW_REFERENCES.PROGRESSION_OUTCOME_TYPE,
622 NEW_REFERENCES.APPLY_AUTOMATICALLY_IND,
623 NEW_REFERENCES.PRG_RULE_REPEAT_FAIL_TYPE,
624 NEW_REFERENCES.OVERRIDE_SHOW_CAUSE_IND,
625 NEW_REFERENCES.OVERRIDE_APPEAL_IND,
626 NEW_REFERENCES.DURATION,
627 NEW_REFERENCES.DURATION_TYPE,
628 NEW_REFERENCES.RANK,
629 NEW_REFERENCES.ENCMB_COURSE_GROUP_CD,
630 NEW_REFERENCES.RESTRICTED_ENROLMENT_CP,
631 NEW_REFERENCES.RESTRICTED_ATTENDANCE_TYPE,
632 NEW_REFERENCES.COMMENTS,
633 X_LAST_UPDATE_DATE,
634 X_LAST_UPDATED_BY,
635 X_LAST_UPDATE_DATE,
636 X_LAST_UPDATED_BY,
637 X_LAST_UPDATE_LOGIN,
638 NEW_REFERENCES.ORG_ID,
639 -- anilk, bug#2784198
640 NEW_REFERENCES.LOGICAL_DELETE_DT
641 );
642
643 open c;
644 fetch c into X_ROWID;
645 if (c%notfound) then
646 close c;
647 raise no_data_found;
648 end if;
649 close c;
650 After_DML (
651 p_action => 'INSERT' ,
652 x_rowid => X_ROWID );
653
654 end INSERT_ROW;
655
656 procedure LOCK_ROW (
657 X_ROWID in VARCHAR2,
658 X_PROGRESSION_RULE_CAT in VARCHAR2,
659 X_PRA_SEQUENCE_NUMBER in NUMBER,
660 X_SEQUENCE_NUMBER in NUMBER,
661 X_NUMBER_OF_FAILURES in NUMBER,
662 X_PROGRESSION_OUTCOME_TYPE in VARCHAR2,
663 X_APPLY_AUTOMATICALLY_IND in VARCHAR2,
664 X_PRG_RULE_REPEAT_FAIL_TYPE in VARCHAR2,
665 X_OVERRIDE_SHOW_CAUSE_IND in VARCHAR2,
666 X_OVERRIDE_APPEAL_IND in VARCHAR2,
667 X_DURATION in NUMBER,
668 X_DURATION_TYPE in VARCHAR2,
669 X_RANK in NUMBER,
670 X_ENCMB_COURSE_GROUP_CD in VARCHAR2,
671 X_RESTRICTED_ENROLMENT_CP in NUMBER,
672 X_RESTRICTED_ATTENDANCE_TYPE in VARCHAR2,
673 X_COMMENTS in VARCHAR2,
674 -- anilk, bug#2784198
675 X_LOGICAL_DELETE_DT in DATE
676 ) AS
677 cursor c1 is select
678 NUMBER_OF_FAILURES,
679 PROGRESSION_OUTCOME_TYPE,
680 APPLY_AUTOMATICALLY_IND,
681 PRG_RULE_REPEAT_FAIL_TYPE,
682 OVERRIDE_SHOW_CAUSE_IND,
683 OVERRIDE_APPEAL_IND,
684 DURATION,
685 DURATION_TYPE,
686 RANK,
687 ENCMB_COURSE_GROUP_CD,
688 RESTRICTED_ENROLMENT_CP,
689 RESTRICTED_ATTENDANCE_TYPE,
690 COMMENTS,
691 -- anilk, bug#2784198
692 LOGICAL_DELETE_DT
693 from IGS_PR_RU_OU_ALL
694 where ROWID = X_ROWID for update nowait;
695 tlinfo c1%rowtype;
696
697 begin
698 open c1;
699 fetch c1 into tlinfo;
700 if (c1%notfound) then
701 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
702 IGS_GE_MSG_STACK.ADD;
703 close c1;
704 app_exception.raise_exception;
705
706 return;
707 end if;
708 close c1;
709
710 if (
711
712 ((tlinfo.NUMBER_OF_FAILURES = X_NUMBER_OF_FAILURES)
713 OR (tlinfo.NUMBER_OF_FAILURES is null and X_NUMBER_OF_FAILURES is null))
714 AND
715 (tlinfo.PROGRESSION_OUTCOME_TYPE = X_PROGRESSION_OUTCOME_TYPE)
716 AND (tlinfo.APPLY_AUTOMATICALLY_IND = X_APPLY_AUTOMATICALLY_IND)
717 AND ((tlinfo.PRG_RULE_REPEAT_FAIL_TYPE = X_PRG_RULE_REPEAT_FAIL_TYPE)
718 OR (tlinfo.PRG_RULE_REPEAT_FAIL_TYPE is null and X_PRG_RULE_REPEAT_FAIL_TYPE is null))
719 AND ((tlinfo.OVERRIDE_SHOW_CAUSE_IND = X_OVERRIDE_SHOW_CAUSE_IND)
720 OR ((tlinfo.OVERRIDE_SHOW_CAUSE_IND is null)
721 AND (X_OVERRIDE_SHOW_CAUSE_IND is null)))
722 AND ((tlinfo.OVERRIDE_APPEAL_IND = X_OVERRIDE_APPEAL_IND)
723 OR ((tlinfo.OVERRIDE_APPEAL_IND is null)
724 AND (X_OVERRIDE_APPEAL_IND is null)))
725 AND ((tlinfo.DURATION = X_DURATION)
726 OR ((tlinfo.DURATION is null)
727 AND (X_DURATION is null)))
728 AND ((tlinfo.DURATION_TYPE = X_DURATION_TYPE)
729 OR ((tlinfo.DURATION_TYPE is null)
730 AND (X_DURATION_TYPE is null)))
731 AND (tlinfo.RANK = X_RANK)
732 AND ((tlinfo.ENCMB_COURSE_GROUP_CD = X_ENCMB_COURSE_GROUP_CD)
733 OR ((tlinfo.ENCMB_COURSE_GROUP_CD is null)
734 AND (X_ENCMB_COURSE_GROUP_CD is null)))
735 AND ((tlinfo.RESTRICTED_ENROLMENT_CP = X_RESTRICTED_ENROLMENT_CP)
736 OR ((tlinfo.RESTRICTED_ENROLMENT_CP is null)
737 AND (X_RESTRICTED_ENROLMENT_CP is null)))
738 AND ((tlinfo.RESTRICTED_ATTENDANCE_TYPE = X_RESTRICTED_ATTENDANCE_TYPE)
739 OR ((tlinfo.RESTRICTED_ATTENDANCE_TYPE is null)
740 AND (X_RESTRICTED_ATTENDANCE_TYPE is null)))
741 AND ((tlinfo.COMMENTS = X_COMMENTS)
742 OR ((tlinfo.COMMENTS is null)
743 AND (X_COMMENTS is null)))
744 -- anilk, bug#2784198
745 AND ((tlinfo.LOGICAL_DELETE_DT = X_LOGICAL_DELETE_DT)
746 OR ((tlinfo.LOGICAL_DELETE_DT is null)
747 AND (X_LOGICAL_DELETE_DT is null)))
748 ) then
749 null;
750 else
751 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
752 IGS_GE_MSG_STACK.ADD;
753 app_exception.raise_exception;
754 end if;
755 return;
756 end LOCK_ROW;
757
758 procedure UPDATE_ROW (
759 X_ROWID in VARCHAR2,
760 X_PROGRESSION_RULE_CAT in VARCHAR2,
761 X_PRA_SEQUENCE_NUMBER in NUMBER,
762 X_SEQUENCE_NUMBER in NUMBER,
763 X_NUMBER_OF_FAILURES in NUMBER,
764 X_PROGRESSION_OUTCOME_TYPE in VARCHAR2,
765 X_APPLY_AUTOMATICALLY_IND in VARCHAR2,
766 X_PRG_RULE_REPEAT_FAIL_TYPE in VARCHAR2,
767 X_OVERRIDE_SHOW_CAUSE_IND in VARCHAR2,
768 X_OVERRIDE_APPEAL_IND in VARCHAR2,
769 X_DURATION in NUMBER,
770 X_DURATION_TYPE in VARCHAR2,
771 X_RANK in NUMBER,
772 X_ENCMB_COURSE_GROUP_CD in VARCHAR2,
773 X_RESTRICTED_ENROLMENT_CP in NUMBER,
774 X_RESTRICTED_ATTENDANCE_TYPE in VARCHAR2,
775 X_COMMENTS in VARCHAR2,
776 X_MODE in VARCHAR2 ,
777 -- anilk, bug#2784198
778 X_LOGICAL_DELETE_DT in DATE
779 ) AS
780 X_LAST_UPDATE_DATE DATE;
781 X_LAST_UPDATED_BY NUMBER;
782 X_LAST_UPDATE_LOGIN NUMBER;
783 begin
784 X_LAST_UPDATE_DATE := SYSDATE;
785 if(X_MODE = 'I') then
786 X_LAST_UPDATED_BY := 1;
787 X_LAST_UPDATE_LOGIN := 0;
788 elsif (X_MODE = 'R') then
789 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
790 if X_LAST_UPDATED_BY is NULL then
791 X_LAST_UPDATED_BY := -1;
792 end if;
793 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
794 if X_LAST_UPDATE_LOGIN is NULL then
795 X_LAST_UPDATE_LOGIN := -1;
796 end if;
797 else
798 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
799 IGS_GE_MSG_STACK.ADD;
800 app_exception.raise_exception;
801 end if;
802 Before_DML (
803 p_action => 'UPDATE',
804 x_rowid => x_rowid ,
805 x_progression_rule_cat => x_progression_rule_cat ,
806 x_pra_sequence_number => x_pra_sequence_number ,
807 x_sequence_number => x_sequence_number ,
808 x_number_of_failures => x_number_of_failures ,
809 x_progression_outcome_type => x_progression_outcome_type ,
810 x_apply_automatically_ind => x_apply_automatically_ind ,
811 x_prg_rule_repeat_fail_type => x_prg_rule_repeat_fail_type ,
812 x_override_show_cause_ind => x_override_show_cause_ind ,
813 x_override_appeal_ind => x_override_appeal_ind ,
814 x_duration => x_duration ,
815 x_duration_type => x_duration_type ,
816 x_rank => x_rank ,
817 x_encmb_course_group_cd => x_encmb_course_group_cd ,
818 x_restricted_enrolment_cp => x_restricted_enrolment_cp ,
819 x_restricted_attendance_type => x_restricted_attendance_type ,
820 x_comments => x_comments ,
821 x_creation_date => x_last_update_date ,
822 x_created_by => x_last_updated_by ,
823 x_last_update_date => x_last_update_date ,
824 x_last_updated_by => x_last_updated_by ,
825 x_last_update_login => x_last_update_login,
826 -- anilk, bug#2784198
827 x_logical_delete_dt => x_logical_delete_dt
828 ) ;
829
830 update IGS_PR_RU_OU_ALL set
831 NUMBER_OF_FAILURES = NEW_REFERENCES.NUMBER_OF_FAILURES,
832 PROGRESSION_OUTCOME_TYPE = NEW_REFERENCES.PROGRESSION_OUTCOME_TYPE,
833 APPLY_AUTOMATICALLY_IND = NEW_REFERENCES.APPLY_AUTOMATICALLY_IND,
834 PRG_RULE_REPEAT_FAIL_TYPE = NEW_REFERENCES.PRG_RULE_REPEAT_FAIL_TYPE,
835 OVERRIDE_SHOW_CAUSE_IND = NEW_REFERENCES.OVERRIDE_SHOW_CAUSE_IND,
836 OVERRIDE_APPEAL_IND = NEW_REFERENCES.OVERRIDE_APPEAL_IND,
837 DURATION = NEW_REFERENCES.DURATION,
838 DURATION_TYPE = NEW_REFERENCES.DURATION_TYPE,
839 RANK = NEW_REFERENCES.RANK,
840 ENCMB_COURSE_GROUP_CD = NEW_REFERENCES.ENCMB_COURSE_GROUP_CD,
841 RESTRICTED_ENROLMENT_CP = NEW_REFERENCES.RESTRICTED_ENROLMENT_CP,
842 RESTRICTED_ATTENDANCE_TYPE = NEW_REFERENCES.RESTRICTED_ATTENDANCE_TYPE,
843 COMMENTS = NEW_REFERENCES.COMMENTS,
844 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
845 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
846 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
847 -- anilk, bug#2784198
848 LOGICAL_DELETE_DT = NEW_REFERENCES.LOGICAL_DELETE_DT
849 where ROWID = X_ROWID
850 ;
851 if (sql%notfound) then
852 raise no_data_found;
853 end if;
854
855 After_DML (
856 p_action => 'UPDATE' ,
857 x_rowid => X_ROWID );
858
859 end UPDATE_ROW;
860
861 procedure ADD_ROW (
862 X_ROWID in out NOCOPY VARCHAR2,
863 X_PROGRESSION_RULE_CAT in VARCHAR2,
864 X_PRA_SEQUENCE_NUMBER in NUMBER,
865 X_SEQUENCE_NUMBER in NUMBER,
866 X_NUMBER_OF_FAILURES in NUMBER,
867 X_PROGRESSION_OUTCOME_TYPE in VARCHAR2,
868 X_APPLY_AUTOMATICALLY_IND in VARCHAR2,
869 X_PRG_RULE_REPEAT_FAIL_TYPE in VARCHAR2,
870 X_OVERRIDE_SHOW_CAUSE_IND in VARCHAR2,
871 X_OVERRIDE_APPEAL_IND in VARCHAR2,
872 X_DURATION in NUMBER,
873 X_DURATION_TYPE in VARCHAR2,
874 X_RANK in NUMBER,
875 X_ENCMB_COURSE_GROUP_CD in VARCHAR2,
876 X_RESTRICTED_ENROLMENT_CP in NUMBER,
877 X_RESTRICTED_ATTENDANCE_TYPE in VARCHAR2,
878 X_COMMENTS in VARCHAR2,
879 X_MODE in VARCHAR2 ,
880 X_ORG_ID IN NUMBER,
881 -- anilk, bug#2784198
882 X_LOGICAL_DELETE_DT in DATE
883 ) AS
884 cursor c1 is select rowid from IGS_PR_RU_OU_ALL
885 where PROGRESSION_RULE_CAT = X_PROGRESSION_RULE_CAT
886 and PRA_SEQUENCE_NUMBER = X_PRA_SEQUENCE_NUMBER
887 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
888 ;
889 begin
890 open c1;
891 fetch c1 into X_ROWID;
892 if (c1%notfound) then
893 close c1;
894 INSERT_ROW (
895 X_ROWID,
896 X_PROGRESSION_RULE_CAT,
897 X_PRA_SEQUENCE_NUMBER,
898 X_SEQUENCE_NUMBER,
899 X_NUMBER_OF_FAILURES,
900 X_PROGRESSION_OUTCOME_TYPE,
901 X_APPLY_AUTOMATICALLY_IND,
902 X_PRG_RULE_REPEAT_FAIL_TYPE,
903 X_OVERRIDE_SHOW_CAUSE_IND,
904 X_OVERRIDE_APPEAL_IND,
905 X_DURATION,
906 X_DURATION_TYPE,
907 X_RANK,
908 X_ENCMB_COURSE_GROUP_CD,
909 X_RESTRICTED_ENROLMENT_CP,
910 X_RESTRICTED_ATTENDANCE_TYPE,
911 X_COMMENTS,
912 X_MODE,
913 x_org_id,
914 -- anilk, bug#2784198
915 X_LOGICAL_DELETE_DT
916 );
917 return;
918 end if;
919 close c1;
920 UPDATE_ROW (
921 X_ROWID ,
922 X_PROGRESSION_RULE_CAT,
923 X_PRA_SEQUENCE_NUMBER,
924 X_SEQUENCE_NUMBER,
925 X_NUMBER_OF_FAILURES,
926 X_PROGRESSION_OUTCOME_TYPE,
927 X_APPLY_AUTOMATICALLY_IND,
928 X_PRG_RULE_REPEAT_FAIL_TYPE,
929 X_OVERRIDE_SHOW_CAUSE_IND,
930 X_OVERRIDE_APPEAL_IND,
931 X_DURATION,
932 X_DURATION_TYPE,
933 X_RANK,
934 X_ENCMB_COURSE_GROUP_CD,
935 X_RESTRICTED_ENROLMENT_CP,
936 X_RESTRICTED_ATTENDANCE_TYPE,
937 X_COMMENTS,
938 X_MODE,
939 -- anilk, bug#2784198
940 X_LOGICAL_DELETE_DT
941 );
942 end ADD_ROW;
943
944 FUNCTION GET_UK_FOR_VALIDATION(X_progression_rule_cat IN VARCHAR2,
945 X_pra_sequence_number IN NUMBER,
946 X_number_of_failures IN NUMBER,
947 X_prg_rule_repeat_fail_type IN VARCHAR2,
948 X_rank IN NUMBER)
949 RETURN BOOLEAN AS
950 CURSOR cur_rowid IS
951 SELECT rowid
952 FROM IGS_PR_RU_OU_ALL
953 WHERE progression_rule_cat = x_progression_rule_cat
954 AND pra_sequence_number = x_pra_sequence_number
955 AND prg_rule_repeat_fail_type = X_prg_rule_repeat_fail_type
956 AND number_of_failures = X_number_of_failures
957 AND rank = X_rank
958 AND ((l_rowid IS NULL) OR (rowid <> l_rowid))
959 FOR UPDATE NOWAIT;
960
961 lv_rowid cur_rowid%RowType;
962
963 BEGIN
964
965 Open cur_rowid;
966 Fetch cur_rowid INTO lv_rowid;
967 IF (cur_rowid%FOUND) THEN
968 Close cur_rowid;
969 Return (TRUE);
970 ELSE
971 Close cur_rowid;
972 Return (FALSE);
973 END IF;
974 END GET_UK_FOR_VALIDATION;
975
976 PROCEDURE Check_Constraints (
977 Column_Name IN VARCHAR2,
978 Column_Value IN VARCHAR2
979 ) AS
980 BEGIN
981
982 IF Column_Name is null THEN
983 NULL;
984 ELSIF upper(Column_name) = 'PRA_SEQUENCE_NUMBER' THEN
985 new_references.PRA_SEQUENCE_NUMBER:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
986
987 ELSIF upper(Column_name) = 'SEQUENCE_NUMBER' THEN
988 new_references.SEQUENCE_NUMBER:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
989
990 ELSIF upper(Column_name) = 'APPLY_AUTOMATICALLY_IND' THEN
991 new_references.APPLY_AUTOMATICALLY_IND:= COLUMN_VALUE ;
992
993 ELSIF upper(Column_name) = 'OVERRIDE_SHOW_CAUSE_IND' THEN
994 new_references.OVERRIDE_SHOW_CAUSE_IND:= COLUMN_VALUE ;
995
996 ELSIF upper(Column_name) = 'OVERRIDE_APPEAL_IND' THEN
997 new_references.OVERRIDE_APPEAL_IND:= COLUMN_VALUE ;
998
999 ELSIF upper(Column_name) = 'DURATION' THEN
1000 new_references.DURATION:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
1001
1002 ELSIF upper(Column_name) = 'DURATION_TYPE' THEN
1003 new_references.DURATION_TYPE:= COLUMN_VALUE ;
1004
1005 ELSIF upper(Column_name) = 'ENCMB_COURSE_GROUP_CD' THEN
1006 new_references.ENCMB_COURSE_GROUP_CD:= COLUMN_VALUE ;
1007
1008 ELSIF upper(Column_name) = 'PRG_RULE_REPEAT_FAIL_TYPE' THEN
1009 new_references.PRG_RULE_REPEAT_FAIL_TYPE:= COLUMN_VALUE ;
1010
1011 ELSIF upper(Column_name) = 'PROGRESSION_OUTCOME_TYPE' THEN
1012 new_references.PROGRESSION_OUTCOME_TYPE:= COLUMN_VALUE ;
1013
1014 ELSIF upper(Column_name) = 'PROGRESSION_RULE_CAT' THEN
1015 new_references.PROGRESSION_RULE_CAT:= COLUMN_VALUE ;
1016
1017 ELSIF upper(Column_name) = 'RESTRICTED_ATTENDANCE_TYPE' THEN
1018 new_references.RESTRICTED_ATTENDANCE_TYPE:= COLUMN_VALUE ;
1019
1020 ELSIF upper(Column_name) = 'RANK' THEN
1021 new_references.RANK:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
1022
1023 ELSIF upper(Column_name) = 'RESTRICTED_ENROLMENT_CP' THEN
1024 new_references.RESTRICTED_ENROLMENT_CP:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
1025
1026 END IF ;
1027
1028 IF upper(Column_name) = 'PRA_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
1029 IF new_references.PRA_SEQUENCE_NUMBER < 1 or new_references.PRA_SEQUENCE_NUMBER > 999999 then
1030 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1031 IGS_GE_MSG_STACK.ADD;
1032 App_Exception.Raise_Exception ;
1033 END IF;
1034
1035 END IF ;
1036
1037 IF upper(Column_name) = 'SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
1038 IF new_references.SEQUENCE_NUMBER < 1 or new_references.SEQUENCE_NUMBER > 999999 then
1039 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1040 IGS_GE_MSG_STACK.ADD;
1041 App_Exception.Raise_Exception ;
1042 END IF;
1043
1044 END IF ;
1045
1046 IF upper(Column_name) = 'APPLY_AUTOMATICALLY_IND' OR COLUMN_NAME IS NULL THEN
1047 IF new_references.APPLY_AUTOMATICALLY_IND<> upper(new_references.APPLY_AUTOMATICALLY_IND) then
1048 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1049 IGS_GE_MSG_STACK.ADD;
1050 App_Exception.Raise_Exception ;
1051 END IF;
1052
1053 IF new_references.APPLY_AUTOMATICALLY_IND not in ('Y','N') then
1054 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1055 IGS_GE_MSG_STACK.ADD;
1056 App_Exception.Raise_Exception ;
1057 END IF;
1058
1059 END IF ;
1060
1061 IF upper(Column_name) = 'OVERRIDE_SHOW_CAUSE_IND' OR COLUMN_NAME IS NULL THEN
1062 IF new_references.OVERRIDE_SHOW_CAUSE_IND<> upper(new_references.OVERRIDE_SHOW_CAUSE_IND) then
1063 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1064 IGS_GE_MSG_STACK.ADD;
1065 App_Exception.Raise_Exception ;
1066 END IF;
1067
1068 IF new_references.OVERRIDE_SHOW_CAUSE_IND not in ('Y','N') then
1069 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1070 IGS_GE_MSG_STACK.ADD;
1071 App_Exception.Raise_Exception ;
1072 END IF;
1073
1074 END IF ;
1075
1076 IF upper(Column_name) = 'OVERRIDE_APPEAL_IND' OR COLUMN_NAME IS NULL THEN
1077 IF new_references.OVERRIDE_APPEAL_IND<> upper(new_references.OVERRIDE_APPEAL_IND) then
1078 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1079 IGS_GE_MSG_STACK.ADD;
1080 App_Exception.Raise_Exception ;
1081 END IF;
1082
1083 IF new_references.OVERRIDE_APPEAL_IND not in ('Y','N') then
1084 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1085 IGS_GE_MSG_STACK.ADD;
1086 App_Exception.Raise_Exception ;
1087 END IF;
1088
1089 END IF ;
1090
1091 IF upper(Column_name) = 'DURATION' OR COLUMN_NAME IS NULL THEN
1092 IF new_references.DURATION < 1 or new_references.DURATION > 999 then
1093 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1094 IGS_GE_MSG_STACK.ADD;
1095 App_Exception.Raise_Exception ;
1096 END IF;
1097
1098 END IF ;
1099
1100 IF upper(Column_name) = 'DURATION_TYPE' OR COLUMN_NAME IS NULL THEN
1101 IF new_references.DURATION_TYPE<> upper(new_references.DURATION_TYPE) then
1102 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1103 IGS_GE_MSG_STACK.ADD;
1104 App_Exception.Raise_Exception ;
1105 END IF;
1106
1107 IF new_references.DURATION_TYPE not in ( 'NORMAL' , 'EFFECTIVE' ) then
1108 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1109 IGS_GE_MSG_STACK.ADD;
1110 App_Exception.Raise_Exception ;
1111 END IF;
1112
1113 END IF ;
1114
1115 IF upper(Column_name) = 'ENCMB_COURSE_GROUP_CD' OR COLUMN_NAME IS NULL THEN
1116 IF new_references.ENCMB_COURSE_GROUP_CD<> upper(new_references.ENCMB_COURSE_GROUP_CD) then
1117 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1118 IGS_GE_MSG_STACK.ADD;
1119 App_Exception.Raise_Exception ;
1120 END IF;
1121
1122 END IF ;
1123
1124 IF upper(Column_name) = 'PRG_RULE_REPEAT_FAIL_TYPE' OR COLUMN_NAME IS NULL THEN
1125 IF new_references.PRG_RULE_REPEAT_FAIL_TYPE<> upper(new_references.PRG_RULE_REPEAT_FAIL_TYPE) then
1126 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1127 IGS_GE_MSG_STACK.ADD;
1128 App_Exception.Raise_Exception ;
1129 END IF;
1130
1131 END IF ;
1132
1133 IF upper(Column_name) = 'PROGRESSION_OUTCOME_TYPE' OR COLUMN_NAME IS NULL THEN
1134 IF new_references.PROGRESSION_OUTCOME_TYPE<> upper(new_references.PROGRESSION_OUTCOME_TYPE) then
1135 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1136 IGS_GE_MSG_STACK.ADD;
1137 App_Exception.Raise_Exception ;
1138 END IF;
1139
1140 END IF ;
1141
1142 IF upper(Column_name) = 'PROGRESSION_RULE_CAT' OR COLUMN_NAME IS NULL THEN
1143 IF new_references.PROGRESSION_RULE_CAT<> upper(new_references.PROGRESSION_RULE_CAT) then
1144 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1145 IGS_GE_MSG_STACK.ADD;
1146 App_Exception.Raise_Exception ;
1147 END IF;
1148
1149 END IF ;
1150
1151 IF upper(Column_name) = 'RESTRICTED_ATTENDANCE_TYPE' OR COLUMN_NAME IS NULL THEN
1152 IF new_references.RESTRICTED_ATTENDANCE_TYPE<> upper(new_references.RESTRICTED_ATTENDANCE_TYPE) then
1153 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1154 IGS_GE_MSG_STACK.ADD;
1155 App_Exception.Raise_Exception ;
1156 END IF;
1157
1158 END IF ;
1159
1160 IF upper(Column_name) = 'RANK' OR COLUMN_NAME IS NULL THEN
1161 IF new_references.RANK < 0 or new_references.RANK > 99 then
1162 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1163 IGS_GE_MSG_STACK.ADD;
1164 App_Exception.Raise_Exception ;
1165 END IF;
1166
1167 END IF ;
1168
1169 IF upper(Column_name) = 'RESTRICTED_ENROLMENT_CP' OR COLUMN_NAME IS NULL THEN
1170 IF new_references.RESTRICTED_ENROLMENT_CP < 0 or new_references.RESTRICTED_ENROLMENT_CP > 999.999 then
1171 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1172 IGS_GE_MSG_STACK.ADD;
1173 App_Exception.Raise_Exception ;
1174 END IF;
1175
1176 END IF ;
1177
1178 END Check_Constraints;
1179
1180
1181 END IGS_PR_RU_OU_PKG;