DBA Data[Home] [Help]

PACKAGE BODY: APPS.IGS_PR_RU_APPL_PKG

Source


1 package body IGS_PR_RU_APPL_PKG AS
2 /* $Header: IGSQI10B.pls 115.15 2003/06/05 13:02:48 sarakshi ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references IGS_PR_RU_APPL_ALL%RowType;
6   new_references IGS_PR_RU_APPL_ALL%RowType;
7 
8   PROCEDURE Set_Column_Values (
9     p_action IN VARCHAR2,
10     x_rowid IN VARCHAR2 DEFAULT NULL,
11     x_sca_course_cd IN VARCHAR2 DEFAULT NULL,
12     x_pro_progression_rule_cat IN VARCHAR2 DEFAULT NULL,
13     x_pro_pra_sequence_number IN NUMBER DEFAULT NULL,
14     x_pro_sequence_number IN NUMBER DEFAULT NULL,
15     x_spo_person_id IN NUMBER DEFAULT NULL,
16     x_spo_course_cd IN VARCHAR2 DEFAULT NULL,
17     x_spo_sequence_number IN NUMBER DEFAULT NULL,
18     x_logical_delete_dt IN DATE DEFAULT NULL,
19     x_message IN VARCHAR2 DEFAULT NULL,
20     x_progression_rule_cat IN VARCHAR2 DEFAULT NULL,
21     x_sequence_number IN NUMBER DEFAULT NULL,
22     x_s_relation_type IN VARCHAR2 DEFAULT NULL,
23     x_progression_rule_cd IN VARCHAR2 DEFAULT NULL,
24     x_reference_cd IN VARCHAR2 DEFAULT NULL,
25     x_rul_sequence_number IN NUMBER DEFAULT NULL,
26     x_attendance_type IN VARCHAR2 DEFAULT NULL,
27     x_ou_org_unit_cd IN VARCHAR2 DEFAULT NULL,
28     x_ou_start_dt IN DATE DEFAULT NULL,
29     x_course_type IN VARCHAR2 DEFAULT NULL,
30     x_crv_course_cd IN VARCHAR2 DEFAULT NULL,
31     x_crv_version_number IN NUMBER DEFAULT NULL,
32     x_sca_person_id IN NUMBER DEFAULT NULL,
33     x_creation_date IN DATE DEFAULT NULL,
34     x_created_by IN NUMBER DEFAULT NULL,
35     x_last_update_date IN DATE DEFAULT NULL,
36     x_last_updated_by IN NUMBER DEFAULT NULL,
37     x_last_update_login IN NUMBER DEFAULT NULL ,
38     x_org_id IN NUMBER DEFAULT NULL,
39     x_min_cp IN NUMBER DEFAULT NULL,
40     x_max_cp IN NUMBER DEFAULT NULL,
41     x_igs_pr_class_std_id IN NUMBER DEFAULT NULL
42   ) AS
43 
44     CURSOR cur_old_ref_values IS
45       SELECT   *
46       FROM     IGS_PR_RU_APPL_ALL
47       WHERE    rowid = x_rowid;
48 
49   BEGIN
50 
51     l_rowid := x_rowid;
52 
53     -- Code for setting the Old and New Reference Values.
54     -- Populate Old Values.
55     Open cur_old_ref_values;
56     Fetch cur_old_ref_values INTO old_references;
57     IF (cur_old_ref_values%NOTFOUND) AND (p_action not in ('INSERT','VALIDATE_INSERT')) THEN
58       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
59       IGS_GE_MSG_STACK.ADD;
60 	  Close cur_old_ref_values;
61       App_Exception.Raise_Exception;
62       Return;
63     END IF;
64     Close cur_old_ref_values;
65 
66     -- Populate New Values.
67     new_references.sca_course_cd := x_sca_course_cd;
68     new_references.pro_progression_rule_cat := x_pro_progression_rule_cat;
69     new_references.pro_pra_sequence_number := x_pro_pra_sequence_number;
70     new_references.pro_sequence_number := x_pro_sequence_number;
71     new_references.spo_person_id := x_spo_person_id;
72     new_references.spo_course_cd := x_spo_course_cd;
73     new_references.spo_sequence_number := x_spo_sequence_number;
74     new_references.logical_delete_dt := x_logical_delete_dt;
75     new_references.message := x_message;
76     new_references.progression_rule_cat := x_progression_rule_cat;
77     new_references.sequence_number := x_sequence_number;
78     new_references.s_relation_type := x_s_relation_type;
79     new_references.progression_rule_cd := x_progression_rule_cd;
80     new_references.reference_cd := x_reference_cd;
81     new_references.rul_sequence_number := x_rul_sequence_number;
82     new_references.attendance_type := x_attendance_type;
83     new_references.ou_org_unit_cd := x_ou_org_unit_cd;
84     new_references.ou_start_dt := x_ou_start_dt;
85     new_references.course_type := x_course_type;
86     new_references.crv_course_cd := x_crv_course_cd;
87     new_references.crv_version_number := x_crv_version_number;
88     new_references.sca_person_id := x_sca_person_id;
89     new_references.org_id := x_org_id;
90     new_references.min_cp := x_min_cp;
91     new_references.max_cp := x_max_cp;
92     new_references.igs_pr_class_std_id := x_igs_pr_class_std_id;
93 
94     IF (p_action = 'UPDATE') THEN
95       new_references.creation_date := old_references.creation_date;
96       new_references.created_by := old_references.created_by;
97     ELSE
98       new_references.creation_date := x_creation_date;
99       new_references.created_by := x_created_by;
100     END IF;
101     new_references.last_update_date := x_last_update_date;
102     new_references.last_updated_by := x_last_updated_by;
103     new_references.last_update_login := x_last_update_login;
104 
105   END Set_Column_Values;
106 
107   -- Trigger description :-
108   -- "OSS_TST".trg_pra_br_iu
109   -- BEFORE INSERT OR UPDATE
110   -- ON IGS_PR_RU_APPL
111   -- FOR EACH ROW
112 
113   PROCEDURE BeforeRowInsertUpdate1(
114     p_inserting IN BOOLEAN DEFAULT FALSE,
115     p_updating IN BOOLEAN DEFAULT FALSE,
116     p_deleting IN BOOLEAN DEFAULT FALSE
117     ) AS
118 	v_message_name varchar2(30);
119   BEGIN
120 	-- Validate the IGS_PS_COURSE type is not closed
121 	IF p_inserting OR
122 	  (p_updating AND NVL(new_references.course_type, 'NULL')  <>
123 	  NVL(old_references.course_type,'NULL')) THEN
124 		IF IGS_as_VAL_acot.crsp_val_cty_closed (
125 					new_references.course_type,
126 					v_message_name) = FALSE THEN
127 			Fnd_Message.Set_Name('IGS',v_message_name);
128                         IGS_GE_MSG_STACK.ADD;
129 			App_Exception.Raise_Exception;
130 		END IF;
131 	END IF;
132 	-- Validate the IGS_PS_COURSE version is active
133 	IF p_inserting OR
134 	  (p_updating AND (NVL(new_references.crv_course_cd, 'NULL')  <>
135 	  NVL(old_references.crv_course_cd,'NULL') OR
136 	  NVL(new_references.crv_version_number, 0)  <>
137 	  NVL(old_references.crv_version_number,0))) THEN
138 		IF IGS_PR_VAL_PRA.crsp_val_crv_active (
139 					new_references.crv_course_cd,
140 					new_references.crv_version_number,
141 					v_message_name) = FALSE THEN
142 			Fnd_Message.Set_Name('IGS',v_message_name);
143                         IGS_GE_MSG_STACK.ADD;
144 			App_Exception.Raise_Exception;
145 		END IF;
146 	END IF;
147 	-- Validate the IGS_OR_UNIT is active
148 	IF p_inserting OR
149 	  (p_updating AND (NVL(new_references.ou_org_unit_cd, 'NULL')  <>
150 	  NVL(old_references.ou_org_unit_cd,'NULL') OR
151 	  NVL((fnd_date.date_to_canonical(new_references.ou_start_dt)),  '1900/01/01')  <>
152 	  NVL((fnd_date.date_to_canonical(old_references.ou_start_dt)), '1900/01/01'))) THEN
153 		IF IGS_PR_VAL_SOPC.prgp_val_ou_active (
154 					new_references.ou_org_unit_cd,
155 					new_references.ou_start_dt,
156 					v_message_name) = FALSE THEN
157 			Fnd_Message.Set_Name('IGS',v_message_name);
158                         IGS_GE_MSG_STACK.ADD;
159 			App_Exception.Raise_Exception;
160 		END IF;
161 	END IF;
162 	-- Validate the progression IGS_RU_RULE category
163 	IF p_inserting THEN
164 		IF IGS_PR_VAL_PRA.prgp_val_prgc_closed (
165 					new_references.progression_rule_cat,
166 					v_message_name) = FALSE THEN
167 			Fnd_Message.Set_Name('IGS',v_message_name);
168                         IGS_GE_MSG_STACK.ADD;
169 			App_Exception.Raise_Exception;
170 		END IF;
171 	END IF;
172 	-- Validate the progression IGS_RU_RULE
173 	IF new_references.progression_rule_cd IS NOT NULL AND (p_inserting OR
174 	  (p_updating AND NVL(new_references.progression_rule_cd, 'NULL')  <>
175 	  NVL(old_references.progression_rule_cd,'NULL'))) THEN
176 		IF IGS_PR_VAL_PRA.prgp_val_prr_closed (
177 					new_references.progression_rule_cd,
178 					v_message_name) = FALSE THEN
179 			Fnd_Message.Set_Name('IGS',v_message_name);
180                         IGS_GE_MSG_STACK.ADD;
181 			App_Exception.Raise_Exception;
182 		END IF;
183 	END IF;
184 	-- Validate the attendance type
185 	IF new_references.attendance_type IS NOT NULL AND (p_inserting OR
186 	  (p_updating AND NVL(new_references.attendance_type, 'NULL')  <>
187 	  NVL(old_references.attendance_type, 'NULL'))) THEN
188           --
189           -- bug id : 1956374
190           -- sjadhav , 28-aug-2001
191           -- change igs_pr_val_pra.enrp_val_att_closed
192           -- to     igs_en_val_pee.enrp_val_att_closed
193           --
194 		IF IGS_EN_VAL_PEE.enrp_val_att_closed (
195 					new_references.attendance_type,
196 					v_message_name) = FALSE THEN
197 			Fnd_Message.Set_Name('IGS',v_message_name);
198                         IGS_GE_MSG_STACK.ADD;
199 			App_Exception.Raise_Exception;
200 		END IF;
201 	END IF;
202 	-- Validate the record has the required details
203 	IF p_inserting OR p_updating THEN
204 		IF IGS_PR_VAL_PRA.prgp_val_pra_rqrd (
205 					new_references.s_relation_type,
206 					new_references.progression_rule_cd,
207 					new_references.rul_sequence_number,
208 					new_references.ou_org_unit_cd,
209 					new_references.ou_start_dt,
210 					new_references.course_type,
211 					new_references.crv_course_cd,
212 					new_references.crv_version_number,
213 					new_references.sca_person_id,
214 					new_references.sca_course_cd,
215 					new_references.pro_progression_rule_cat,
216 					new_references.pro_pra_sequence_number,
217 					new_references.pro_sequence_number,
218 					new_references.spo_person_id,
219 					new_references.spo_course_cd,
220 					new_references.spo_sequence_number,
221 					v_message_name) = FALSE THEN
222 
223 			Fnd_Message.Set_Name('IGS',v_message_name);
224                         IGS_GE_MSG_STACK.ADD;
225 			App_Exception.Raise_Exception;
226 		END IF;
227 	END IF;
228 
229 
230   END BeforeRowInsertUpdate1;
231 
232   PROCEDURE Check_Parent_Existance AS
233   BEGIN
234 
235     IF (((old_references.attendance_type = new_references.attendance_type)) OR
236         ((new_references.attendance_type IS NULL))) THEN
237       NULL;
238     ELSE
239       IF NOT IGS_EN_ATD_TYPE_PKG.Get_PK_For_Validation (
240         new_references.attendance_type
241         ) THEN
242 	Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
243         IGS_GE_MSG_STACK.ADD;
244 	App_Exception.Raise_Exception;
245 
246       END IF;
247 
248     END IF;
249 
250     IF (((old_references.crv_course_cd = new_references.crv_course_cd) AND
251          (old_references.crv_version_number = new_references.crv_version_number)) OR
252         ((new_references.crv_course_cd IS NULL) OR
253          (new_references.crv_version_number IS NULL))) THEN
254       NULL;
255     ELSE
256       IF NOT IGS_PS_VER_PKG.Get_PK_For_Validation (
257         new_references.crv_course_cd,
258         new_references.crv_version_number
259         ) THEN
260 
261 		Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
262                 IGS_GE_MSG_STACK.ADD;
263 		App_Exception.Raise_Exception;
264 
265 	END IF;
266 
267     END IF;
268 
269     IF (((old_references.course_type = new_references.course_type)) OR
270         ((new_references.course_type IS NULL))) THEN
271       NULL;
272     ELSE
273       IF NOT IGS_PS_TYPE_PKG.Get_PK_For_Validation (
274         new_references.course_type
275         ) THEN
276 
277 			Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
278                         IGS_GE_MSG_STACK.ADD;
279 			App_Exception.Raise_Exception;
280       END IF;
281 
282     END IF;
283 
284     IF (((old_references.ou_org_unit_cd = new_references.ou_org_unit_cd) AND
285          (old_references.ou_start_dt = new_references.ou_start_dt)) OR
286         ((new_references.ou_org_unit_cd IS NULL) OR
287          (new_references.ou_start_dt IS NULL))) THEN
288       NULL;
289     ELSE
290       IF NOT IGS_OR_UNIT_PKG.Get_PK_For_Validation (
291         new_references.ou_org_unit_cd,
292         new_references.ou_start_dt
293         ) THEN
294 
295 			Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
296                         IGS_GE_MSG_STACK.ADD;
297 			App_Exception.Raise_Exception;
298 
299       END IF;
300 
301     END IF;
302 
303     IF (((old_references.progression_rule_cat = new_references.progression_rule_cat)) OR
304         ((new_references.progression_rule_cat IS NULL))) THEN
305       NULL;
306     ELSE
307       IF NOT IGS_PR_RU_CAT_PKG.Get_PK_For_Validation (
308         new_references.progression_rule_cat
309         ) THEN
310 
311 		Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
312                 IGS_GE_MSG_STACK.ADD;
313 		App_Exception.Raise_Exception;
314 
315       END IF;
316 
317     END IF;
318 
319     IF (((old_references.pro_progression_rule_cat = new_references.pro_progression_rule_cat) AND
320          (old_references.pro_pra_sequence_number = new_references.pro_pra_sequence_number) AND
321          (old_references.pro_sequence_number = new_references.pro_sequence_number)) OR
322         ((new_references.pro_progression_rule_cat IS NULL) OR
323          (new_references.pro_pra_sequence_number IS NULL) OR
324          (new_references.pro_sequence_number IS NULL))) THEN
325       NULL;
326     ELSE
327       IF NOT IGS_PR_RU_OU_PKG.Get_PK_For_Validation (
328         new_references.pro_progression_rule_cat,
329         new_references.pro_pra_sequence_number,
330         new_references.pro_sequence_number
331         ) THEN
332 
333 		Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
334                 IGS_GE_MSG_STACK.ADD;
335 		App_Exception.Raise_Exception;
336 
337 	END IF;
338 
339     END IF;
340 
341     IF (((old_references.progression_rule_cat = new_references.progression_rule_cat) AND
342          (old_references.progression_rule_cd = new_references.progression_rule_cd)) OR
343         ((new_references.progression_rule_cat IS NULL) OR
344          (new_references.progression_rule_cd IS NULL))) THEN
345       NULL;
346     ELSE
347       IF NOT IGS_PR_RULE_PKG.Get_PK_For_Validation (
348         new_references.progression_rule_cat,
349         new_references.progression_rule_cd
350         ) THEN
351 		Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
352                 IGS_GE_MSG_STACK.ADD;
353 		App_Exception.Raise_Exception;
354 
355 	END IF;
356 
357     END IF;
358 
359     IF (((old_references.sca_person_id = new_references.sca_person_id) AND
360          (old_references.sca_course_cd = new_references.sca_course_cd)) OR
361         ((new_references.sca_person_id IS NULL) OR
362          (new_references.sca_course_cd IS NULL))) THEN
363       NULL;
364     ELSE
365       IF NOT IGS_EN_STDNT_PS_ATT_PKG.Get_PK_For_Validation (
366         new_references.sca_person_id,
367         new_references.sca_course_cd
368         ) THEN
369 
370 		Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
371                 IGS_GE_MSG_STACK.ADD;
372 		App_Exception.Raise_Exception;
373 
374 	END IF;
375 
376     END IF;
377 
378     IF (((old_references.spo_person_id = new_references.spo_person_id) AND
379          (old_references.spo_course_cd = new_references.spo_course_cd) AND
380          (old_references.spo_sequence_number = new_references.spo_sequence_number)) OR
381         ((new_references.spo_person_id IS NULL) OR
382          (new_references.spo_course_cd IS NULL) OR
383          (new_references.spo_sequence_number IS NULL))) THEN
384       NULL;
385     ELSE
386       IF NOT IGS_PR_STDNT_PR_OU_PKG.Get_PK_For_Validation (
387         new_references.spo_person_id,
388         new_references.spo_course_cd,
389         new_references.spo_sequence_number
390         ) THEN
391 
392 		Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
393                 IGS_GE_MSG_STACK.ADD;
394 		App_Exception.Raise_Exception;
395 
396 	END IF;
397 
398     END IF;
399 
400     -- This piece of code is added as part of 'Academic Standing and Progression DLD'
401 
402     IF (((old_references.igs_pr_class_std_id = new_references.igs_pr_class_std_id)) OR
403         ((new_references.igs_pr_class_std_id IS NULL))) THEN
404       NULL;
405     ELSE
406       IF NOT IGS_PR_CLASS_STD_PKG.Get_PK_For_Validation (
407          new_references.igs_pr_class_std_id
408         ) THEN
409 
410 	  Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
411           IGS_GE_MSG_STACK.ADD;
412 	  App_Exception.Raise_Exception;
413 
414       END IF;
415 
416     END IF;
417 
418   END Check_Parent_Existance;
419 
420   PROCEDURE Check_Child_Existance AS
421   BEGIN
422 
423     IGS_PR_RU_CA_TYPE_PKG.GET_FK_IGS_PR_RU_APPL (
424       old_references.progression_rule_cat,
425       old_references.sequence_number
426       );
427 
428     IGS_PR_RU_OU_PKG.GET_FK_IGS_PR_RU_APPL (
429       old_references.progression_rule_cat,
430       old_references.sequence_number
431       );
432 
433     IGS_PR_SDT_PR_RU_CK_PKG.GET_FK_IGS_PR_RU_APPL (
434       old_references.progression_rule_cat,
435       old_references.sequence_number
436       );
437 
438   END Check_Child_Existance;
439 
440   FUNCTION Get_PK_For_Validation (
441     x_progression_rule_cat IN VARCHAR2,
442     x_sequence_number IN NUMBER
443     ) RETURN BOOLEAN AS
444 
445     CURSOR cur_rowid IS
446       SELECT   rowid
447       FROM     IGS_PR_RU_APPL_ALL
448       WHERE    progression_rule_cat = x_progression_rule_cat
449       AND      sequence_number = x_sequence_number
450       FOR UPDATE NOWAIT;
451 
452     lv_rowid cur_rowid%RowType;
453 
454   BEGIN
455 
456     Open cur_rowid;
457     Fetch cur_rowid INTO lv_rowid;
458     	IF (cur_rowid%FOUND) THEN
459 		Close cur_rowid;
460 		Return (TRUE);
461 	ELSE
462 		Close cur_rowid;
463 		Return (FALSE);
464 	END IF;
465   END Get_PK_For_Validation;
466 
467   PROCEDURE GET_FK_IGS_PR_CLASS_STD (
468     x_igs_pr_class_std_id IN NUMBER
469     ) AS
470 
471     CURSOR cur_rowid IS
472       SELECT   rowid,logical_delete_dt
473       FROM     IGS_PR_RU_APPL_ALL
474       WHERE    igs_pr_class_std_id = x_igs_pr_class_std_id;
475 
476     lv_rowid cur_rowid%RowType;
477 
478   BEGIN
479 
480     Open cur_rowid;
481     Fetch cur_rowid INTO lv_rowid;
482     IF (cur_rowid%FOUND and lv_rowid.logical_delete_dt is null) THEN
483       Fnd_Message.Set_Name ('IGS', 'IGS_PR_PRA_PCS_FK');
484       IGS_GE_MSG_STACK.ADD;
485 	  Close cur_rowid;
486       App_Exception.Raise_Exception;
487       Return;
488     END IF;
489     Close cur_rowid;
490 
491   END GET_FK_IGS_PR_CLASS_STD;
492 
493   PROCEDURE GET_FK_IGS_EN_ATD_TYPE (
494     x_attendance_type IN VARCHAR2
495     ) AS
496 
497     CURSOR cur_rowid IS
498       SELECT   rowid,logical_delete_dt
499       FROM     IGS_PR_RU_APPL_ALL
500       WHERE    attendance_type = x_attendance_type ;
501 
502     lv_rowid cur_rowid%RowType;
503 
504   BEGIN
505 
506     Open cur_rowid;
507     Fetch cur_rowid INTO lv_rowid;
508     IF (cur_rowid%FOUND and lv_rowid.logical_delete_dt is null ) THEN
509       Fnd_Message.Set_Name ('IGS', 'IGS_PR_PRA_ATT_FK');
510       IGS_GE_MSG_STACK.ADD;
511 	  Close cur_rowid;
512       App_Exception.Raise_Exception;
513       Return;
514     END IF;
515     Close cur_rowid;
516 
517   END GET_FK_IGS_EN_ATD_TYPE;
518 
519   PROCEDURE GET_FK_IGS_PS_VER (
520     x_course_cd IN VARCHAR2,
521     x_version_number IN NUMBER
522     ) AS
523 
524     CURSOR cur_rowid IS
525       SELECT   rowid,logical_delete_dt
526       FROM     IGS_PR_RU_APPL_ALL
527       WHERE    crv_course_cd = x_course_cd
528       AND      crv_version_number = x_version_number ;
529 
530     lv_rowid cur_rowid%RowType;
531 
532   BEGIN
533 
534     Open cur_rowid;
535     Fetch cur_rowid INTO lv_rowid;
536     IF (cur_rowid%FOUND and lv_rowid.logical_delete_dt is null) THEN
537       Fnd_Message.Set_Name ('IGS', 'IGS_PR_PRA_CRV_FK');
538       IGS_GE_MSG_STACK.ADD;
539 	  Close cur_rowid;
540       App_Exception.Raise_Exception;
541       Return;
542     END IF;
543     Close cur_rowid;
544 
545   END GET_FK_IGS_PS_VER;
546 
547 
548   PROCEDURE GET_FK_IGS_OR_UNIT (
549     x_org_unit_cd IN VARCHAR2,
550     x_start_dt IN VARCHAR2
551     ) AS
552 
553     CURSOR cur_rowid IS
554       SELECT   rowid,logical_delete_dt
555       FROM     IGS_PR_RU_APPL_ALL
556       WHERE    ou_org_unit_cd = x_org_unit_cd
557       AND      ou_start_dt = x_start_dt ;
558 
559     lv_rowid cur_rowid%RowType;
560 
561   BEGIN
562 
563     Open cur_rowid;
564     Fetch cur_rowid INTO lv_rowid;
565     IF (cur_rowid%FOUND and lv_rowid.logical_delete_dt is null) THEN
566       Fnd_Message.Set_Name ('IGS', 'IGS_PR_PRA_OU_FK');
567       IGS_GE_MSG_STACK.ADD;
568 	  Close cur_rowid;
569       App_Exception.Raise_Exception;
570       Return;
571     END IF;
572     Close cur_rowid;
573 
574   END GET_FK_IGS_OR_UNIT;
575 
576   PROCEDURE GET_FK_IGS_PR_RU_CAT (
577     x_progression_rule_cat IN VARCHAR2
578     ) AS
579 
580     CURSOR cur_rowid IS
581       SELECT   rowid,logical_delete_dt
582       FROM     IGS_PR_RU_APPL_ALL
583       WHERE    progression_rule_cat = x_progression_rule_cat ;
584 
585     lv_rowid cur_rowid%RowType;
586 
587   BEGIN
588 
589     Open cur_rowid;
590     Fetch cur_rowid INTO lv_rowid;
591     IF (cur_rowid%FOUND and lv_rowid.logical_delete_dt is null) THEN
592       Fnd_Message.Set_Name ('IGS', 'IGS_PR_PRA_PRGC_FK');
593       IGS_GE_MSG_STACK.ADD;
594 	  Close cur_rowid;
595       App_Exception.Raise_Exception;
596 
597       Return;
598     END IF;
599     Close cur_rowid;
600 
601   END GET_FK_IGS_PR_RU_CAT;
602 
603   PROCEDURE GET_FK_IGS_PR_RU_OU (
604     x_progression_rule_cat IN VARCHAR2,
605     x_pra_sequence_number IN NUMBER,
606     x_sequence_number IN NUMBER
607     ) AS
608 
609     CURSOR cur_rowid IS
610       SELECT   rowid,logical_delete_dt
611       FROM     IGS_PR_RU_APPL_ALL
612       WHERE    pro_progression_rule_cat = x_progression_rule_cat
613       AND      pro_pra_sequence_number = x_pra_sequence_number
614       AND      pro_sequence_number = x_sequence_number ;
615 
616     lv_rowid cur_rowid%RowType;
617 
618   BEGIN
619 
620     Open cur_rowid;
621     Fetch cur_rowid INTO lv_rowid;
622     IF (cur_rowid%FOUND and lv_rowid.logical_delete_dt is null) THEN
623       Fnd_Message.Set_Name ('IGS', 'IGS_PR_PRA_PRO_FK');
624       IGS_GE_MSG_STACK.ADD;
625 	  Close cur_rowid;
626       App_Exception.Raise_Exception;
627 
628       Return;
629     END IF;
630     Close cur_rowid;
631 
632   END GET_FK_IGS_PR_RU_OU;
633 
634   PROCEDURE GET_FK_IGS_PR_RULE (
635     x_progression_rule_cat IN VARCHAR2,
636     x_progression_rule_cd IN VARCHAR2
637     ) AS
638 
639     CURSOR cur_rowid IS
640       SELECT   rowid,logical_delete_dt
641       FROM     IGS_PR_RU_APPL_ALL
642       WHERE    progression_rule_cat = x_progression_rule_cat
643       AND      progression_rule_cd = x_progression_rule_cd ;
644 
645     lv_rowid cur_rowid%RowType;
646 
647   BEGIN
648 
649     Open cur_rowid;
650     Fetch cur_rowid INTO lv_rowid;
651     IF (cur_rowid%FOUND and lv_rowid.logical_delete_dt is null) THEN
652       Fnd_Message.Set_Name ('IGS', 'IGS_PR_PRA_PRR_FK');
653       IGS_GE_MSG_STACK.ADD;
654 	  Close cur_rowid;
655       App_Exception.Raise_Exception;
656 
657       Return;
658     END IF;
659     Close cur_rowid;
660 
661   END GET_FK_IGS_PR_RULE;
662 
663   PROCEDURE GET_FK_IGS_EN_STDNT_PS_ATT (
664     x_person_id IN VARCHAR2,
665     x_course_cd IN VARCHAR2
666     ) AS
667 
668     CURSOR cur_rowid IS
669       SELECT   rowid,logical_delete_dt
670       FROM     IGS_PR_RU_APPL_ALL
671       WHERE    sca_person_id = x_person_id
672       AND      sca_course_cd = x_course_cd ;
673 
674     lv_rowid cur_rowid%RowType;
675 
676   BEGIN
677 
678     Open cur_rowid;
679     Fetch cur_rowid INTO lv_rowid;
680     IF (cur_rowid%FOUND and lv_rowid.logical_delete_dt is null) THEN
681       Fnd_Message.Set_Name ('IGS', 'IGS_PR_PRA_SCA_FK');
682       IGS_GE_MSG_STACK.ADD;
683 	  Close cur_rowid;
684       App_Exception.Raise_Exception;
685 
686       Return;
687     END IF;
688     Close cur_rowid;
689 
690   END GET_FK_IGS_EN_STDNT_PS_ATT;
691 
692   PROCEDURE GET_FK_IGS_PR_STDNT_PR_OU (
693     x_person_id IN VARCHAR2,
694     x_course_cd IN VARCHAR2,
695     x_sequence_number IN NUMBER
696     ) AS
697 
698     CURSOR cur_rowid IS
699       SELECT   rowid,logical_delete_dt
700       FROM     IGS_PR_RU_APPL_ALL
701       WHERE    spo_person_id = x_person_id
702       AND      spo_course_cd = x_course_cd
703       AND      spo_sequence_number = x_sequence_number ;
704 
705     lv_rowid cur_rowid%RowType;
706 
707   BEGIN
708 
709     Open cur_rowid;
710     Fetch cur_rowid INTO lv_rowid;
711     IF (cur_rowid%FOUND and lv_rowid.logical_delete_dt is null) THEN
712       Fnd_Message.Set_Name ('IGS', 'IGS_PR_PRA_SPO_FK');
713       IGS_GE_MSG_STACK.ADD;
714 	  Close cur_rowid;
715       App_Exception.Raise_Exception;
716 
717       Return;
718     END IF;
719     Close cur_rowid;
720 
721   END GET_FK_IGS_PR_STDNT_PR_OU;
722 
723   PROCEDURE BeforeInsertUpdate( p_action VARCHAR2 ) AS
724   /*
725   ||  Created By : anilk
726   ||  Created On : 25-FEB-2003
727   ||  Known limitations, enhancements or remarks :
728   ||  Change History :
729   ||  Who             When            What
730   ||  (reverse chronological order - newest change first)
731   */
732     CURSOR c_parent (
733          cp_progression_rule_cat    IGS_PR_RU_OU.progression_rule_cat%TYPE,
734          cp_pra_sequence_number     IGS_PR_RU_OU.pra_sequence_number%TYPE,
735          cp_sequence_number         IGS_PR_RU_OU.sequence_number%TYPE  ) IS
736      SELECT 1
737      FROM   IGS_PR_RU_OU pro
738      WHERE  pro.progression_rule_cat = cp_progression_rule_cat    AND
739             pro.pra_sequence_number  = cp_pra_sequence_number AND
740             pro.sequence_number      = cp_sequence_number     AND
741             pro.logical_delete_dt is NULL;
742 
743     l_dummy NUMBER;
744 
745   BEGIN
746 
747    IF (p_action = 'INSERT') AND new_references.pro_progression_rule_cat IS NOT NULL
748                             AND new_references.pro_pra_sequence_number  IS NOT NULL
749 			    AND new_references.pro_sequence_number      IS NOT NULL THEN
750       OPEN c_parent( new_references.pro_progression_rule_cat, new_references.pro_pra_sequence_number, new_references.pro_sequence_number );
751       FETCH c_parent INTO l_dummy;
752       IF c_parent%NOTFOUND THEN
753           CLOSE c_parent;
754           FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
755           IGS_GE_MSG_STACK.ADD;
756           APP_EXCEPTION.RAISE_EXCEPTION;
757       END IF;
758       CLOSE c_parent;
759    ELSIF(p_action = 'UPDATE') THEN
760       IF NVL(new_references.pro_progression_rule_cat,'1') <> NVL(old_references.pro_progression_rule_cat,'1')  OR
761          NVL(new_references.pro_pra_sequence_number,1) <> NVL(old_references.pro_pra_sequence_number,1)  OR
762          NVL(new_references.pro_sequence_number,1) <> NVL(old_references.pro_sequence_number,1)  THEN
763         OPEN c_parent( new_references.pro_progression_rule_cat,  new_references.pro_pra_sequence_number, new_references.pro_sequence_number );
764         FETCH c_parent INTO l_dummy;
765         IF c_parent%NOTFOUND THEN
766           CLOSE c_parent;
767           FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
768           IGS_GE_MSG_STACK.ADD;
769           APP_EXCEPTION.RAISE_EXCEPTION;
770         END IF;
771         CLOSE c_parent;
772       END IF;
773    END IF;
774 
775   END BeforeInsertUpdate;
776 
777 	PROCEDURE Before_DML (
778     p_action IN VARCHAR2,
779     x_rowid IN  VARCHAR2 DEFAULT NULL,
780     x_sca_course_cd IN VARCHAR2 DEFAULT NULL,
781     x_pro_progression_rule_cat IN VARCHAR2 DEFAULT NULL,
782     x_pro_pra_sequence_number IN NUMBER DEFAULT NULL,
783     x_pro_sequence_number IN NUMBER DEFAULT NULL,
784     x_spo_person_id IN NUMBER DEFAULT NULL,
785     x_spo_course_cd IN VARCHAR2 DEFAULT NULL,
786     x_spo_sequence_number IN NUMBER DEFAULT NULL,
787     x_logical_delete_dt IN DATE DEFAULT NULL,
788     x_message IN VARCHAR2 DEFAULT NULL,
789     x_progression_rule_cat IN VARCHAR2 DEFAULT NULL,
790     x_sequence_number IN NUMBER DEFAULT NULL,
791     x_s_relation_type IN VARCHAR2 DEFAULT NULL,
792     x_progression_rule_cd IN VARCHAR2 DEFAULT NULL,
793     x_reference_cd IN VARCHAR2 DEFAULT NULL,
794     x_rul_sequence_number IN NUMBER DEFAULT NULL,
795     x_attendance_type IN VARCHAR2 DEFAULT NULL,
796     x_ou_org_unit_cd IN VARCHAR2 DEFAULT NULL,
797     x_ou_start_dt IN DATE DEFAULT NULL,
798     x_course_type IN VARCHAR2 DEFAULT NULL,
799     x_crv_course_cd IN VARCHAR2 DEFAULT NULL,
800     x_crv_version_number IN NUMBER DEFAULT NULL,
801     x_sca_person_id IN NUMBER DEFAULT NULL,
802     x_creation_date IN DATE DEFAULT NULL,
803     x_created_by IN NUMBER DEFAULT NULL,
804     x_last_update_date IN DATE DEFAULT NULL,
805     x_last_updated_by IN NUMBER DEFAULT NULL,
806     x_last_update_login IN NUMBER DEFAULT NULL,
807     x_org_id IN NUMBER DEFAULT NULL,
808     x_min_cp IN NUMBER DEFAULT NULL,
809     x_max_cp IN NUMBER DEFAULT NULL,
810     x_igs_pr_class_std_id IN NUMBER DEFAULT NULL
811   )
812 
813 /*****************************************************************************************************************************
814  --
815  -- Changed History
816  -- Who          When            What
817  -- Aiyer        16-Apr-2002     Modified for the bug #2274631 Call to BeforeRowInsertUpdate1 from before_dml in case of update
818  --                              needs to happen only for those a records which have not been logically deleted.
819  --
820  *********************************************************************************************************************************/
821 
822   AS
823   BEGIN
824 
825     Set_Column_Values (
826       p_action,
827       x_rowid,
828       x_sca_course_cd,
829       x_pro_progression_rule_cat,
830       x_pro_pra_sequence_number,
831       x_pro_sequence_number,
832       x_spo_person_id,
833       x_spo_course_cd,
834       x_spo_sequence_number,
835       x_logical_delete_dt,
836       x_message,
837       x_progression_rule_cat,
838       x_sequence_number,
839       x_s_relation_type,
840       x_progression_rule_cd,
841       x_reference_cd,
842       x_rul_sequence_number,
843       x_attendance_type,
844       x_ou_org_unit_cd,
845       x_ou_start_dt,
846       x_course_type,
847       x_crv_course_cd,
848       x_crv_version_number,
849       x_sca_person_id,
850       x_creation_date,
851       x_created_by,
852       x_last_update_date,
853       x_last_updated_by,
854       x_last_update_login ,
855       x_org_id,
856       x_min_cp,
857       x_max_cp,
858       x_igs_pr_class_std_id
859     );
860 
861     IF (p_action = 'INSERT') THEN
862       -- Call all the procedures related to Before Insert.
863       BeforeRowInsertUpdate1 ( p_inserting => TRUE );
864       Check_Parent_Existance;
865 	IF Get_PK_For_Validation (
866     			new_references.progression_rule_cat,
867 			new_references.sequence_number
868 				    ) THEN
869 		Fnd_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
870       IGS_GE_MSG_STACK.ADD;
871 		App_Exception.Raise_Exception;
872 	END IF;
873 	CHECK_CONSTRAINTS;
874 
875     ELSIF (p_action = 'UPDATE') THEN
876 
877       -- Modified by aiyer for the bug #2274631
878       -- Call BeforeRowInsertUpdate1 procedure
879       -- only for those a records which have not been logically deleted
880       IF x_logical_delete_dt IS NULL THEN
881         BeforeRowInsertUpdate1 ( p_updating => TRUE );
882         Check_Parent_Existance;
883 	CHECK_CONSTRAINTS;
884       END IF;
885     ELSIF (p_action = 'DELETE') THEN
886       -- Call all the procedures related to Before Delete.
887       Check_Child_Existance;
888 
889 
890 	ELSIF (p_action = 'VALIDATE_INSERT') THEN
891 		IF Get_PK_For_Validation (
892     			new_references.progression_rule_cat,
893 			new_references.sequence_number
894 				    ) THEN
895 		  Fnd_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
896                   IGS_GE_MSG_STACK.ADD;
897 		  App_Exception.Raise_Exception;
898 	       END IF;
899 	CHECK_CONSTRAINTS;
900 
901 	ELSIF (p_action = 'VALIDATE_UPDATE') THEN
902 		CHECK_CONSTRAINTS;
903 
904 	ELSIF (p_action = 'VALIDATE_DELETE') THEN
905 	Check_Child_Existance;
906     END IF;
907 
908     -- anilk, bug#2784198
909     BeforeInsertUpdate(p_action);
910 
911   END Before_DML;
912 
913 procedure INSERT_ROW (
914   X_ROWID in out NOCOPY VARCHAR2,
915   X_PROGRESSION_RULE_CAT in VARCHAR2,
916   X_SEQUENCE_NUMBER in NUMBER,
917   X_S_RELATION_TYPE in VARCHAR2,
918   X_PROGRESSION_RULE_CD in VARCHAR2,
919   X_REFERENCE_CD in VARCHAR2,
920   X_RUL_SEQUENCE_NUMBER in NUMBER,
921   X_ATTENDANCE_TYPE in VARCHAR2,
922   X_OU_ORG_UNIT_CD in VARCHAR2,
923   X_OU_START_DT in DATE,
924   X_COURSE_TYPE in VARCHAR2,
925   X_CRV_COURSE_CD in VARCHAR2,
926   X_CRV_VERSION_NUMBER in NUMBER,
927   X_SCA_PERSON_ID in NUMBER,
928   X_SCA_COURSE_CD in VARCHAR2,
929   X_PRO_PROGRESSION_RULE_CAT in VARCHAR2,
930   X_PRO_PRA_SEQUENCE_NUMBER in NUMBER,
931   X_PRO_SEQUENCE_NUMBER in NUMBER,
932   X_SPO_PERSON_ID in NUMBER,
933   X_SPO_COURSE_CD in VARCHAR2,
934   X_SPO_SEQUENCE_NUMBER in NUMBER,
935   X_LOGICAL_DELETE_DT in DATE,
936   X_MESSAGE in VARCHAR2,
937   X_MODE in VARCHAR2 default 'R',
938   X_ORG_ID IN NUMBER,
939   X_MIN_CP IN NUMBER DEFAULT NULL,
940   X_MAX_CP IN NUMBER DEFAULT NULL,
941   X_IGS_PR_CLASS_STD_ID IN NUMBER DEFAULT NULL
942   ) AS
943     cursor C is select ROWID from IGS_PR_RU_APPL_ALL
944       where PROGRESSION_RULE_CAT = X_PROGRESSION_RULE_CAT
945       and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
946     X_LAST_UPDATE_DATE DATE;
947     X_LAST_UPDATED_BY NUMBER;
948     X_LAST_UPDATE_LOGIN NUMBER;
949 begin
950   X_LAST_UPDATE_DATE := SYSDATE;
951   if(X_MODE = 'I') then
952     X_LAST_UPDATED_BY := 1;
953     X_LAST_UPDATE_LOGIN := 0;
954   elsif (X_MODE = 'R') then
955     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
956     if X_LAST_UPDATED_BY is NULL then
957       X_LAST_UPDATED_BY := -1;
958     end if;
959     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
960     if X_LAST_UPDATE_LOGIN is NULL then
961       X_LAST_UPDATE_LOGIN := -1;
962     end if;
963   else
964     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
965       IGS_GE_MSG_STACK.ADD;
966     app_exception.raise_exception;
967   end if;
968 
969   Before_DML (
970     p_action => 'INSERT',
971     x_rowid => x_rowid ,
972     x_sca_course_cd => x_sca_course_cd ,
973     x_pro_progression_rule_cat => x_pro_progression_rule_cat ,
974     x_pro_pra_sequence_number => x_pro_pra_sequence_number ,
975     x_pro_sequence_number => x_pro_sequence_number ,
976     x_spo_person_id => x_spo_person_id ,
977     x_spo_course_cd => x_spo_course_cd ,
978     x_spo_sequence_number => x_spo_sequence_number ,
979     x_logical_delete_dt => x_logical_delete_dt ,
980     x_message => x_message ,
981     x_progression_rule_cat => x_progression_rule_cat ,
982     x_sequence_number => x_sequence_number ,
983     x_s_relation_type => x_s_relation_type ,
984     x_progression_rule_cd => x_progression_rule_cd ,
985     x_reference_cd => x_reference_cd ,
986     x_rul_sequence_number => x_rul_sequence_number ,
987     x_attendance_type => x_attendance_type ,
988     x_ou_org_unit_cd => x_ou_org_unit_cd ,
989     x_ou_start_dt => x_ou_start_dt ,
990     x_course_type => x_course_type ,
991     x_crv_course_cd => x_crv_course_cd ,
992     x_crv_version_number => x_crv_version_number ,
993     x_sca_person_id => x_sca_person_id ,
994     x_creation_date => x_last_update_date ,
995     x_created_by => x_last_updated_by ,
996     x_last_update_date => x_last_update_date ,
997     x_last_updated_by => x_last_updated_by ,
998     x_last_update_login =>x_last_update_login ,
999     x_org_id => igs_ge_gen_003.get_org_id,
1000     x_min_cp => x_min_cp,
1001     x_max_cp => x_max_cp,
1002     x_igs_pr_class_std_id => x_igs_pr_class_std_id
1003   );
1004 
1005   insert into IGS_PR_RU_APPL_ALL (
1006     PROGRESSION_RULE_CAT,
1007     SEQUENCE_NUMBER,
1008     S_RELATION_TYPE,
1009     PROGRESSION_RULE_CD,
1010     REFERENCE_CD,
1011     RUL_SEQUENCE_NUMBER,
1012     ATTENDANCE_TYPE,
1013     OU_ORG_UNIT_CD,
1014     OU_START_DT,
1015     COURSE_TYPE,
1016     CRV_COURSE_CD,
1017     CRV_VERSION_NUMBER,
1018     SCA_PERSON_ID,
1019     SCA_COURSE_CD,
1020     PRO_PROGRESSION_RULE_CAT,
1021     PRO_PRA_SEQUENCE_NUMBER,
1022     PRO_SEQUENCE_NUMBER,
1023     SPO_PERSON_ID,
1024     SPO_COURSE_CD,
1025     SPO_SEQUENCE_NUMBER,
1026     LOGICAL_DELETE_DT,
1027     MESSAGE,
1028     CREATION_DATE,
1029     CREATED_BY,
1030     LAST_UPDATE_DATE,
1031     LAST_UPDATED_BY,
1032     LAST_UPDATE_LOGIN,
1033     ORG_ID,
1034     MIN_CP,
1035     MAX_CP,
1036     IGS_PR_CLASS_STD_ID
1037   ) values (
1038     NEW_REFERENCES.PROGRESSION_RULE_CAT,
1039     NEW_REFERENCES.SEQUENCE_NUMBER,
1040     NEW_REFERENCES.S_RELATION_TYPE,
1041     NEW_REFERENCES.PROGRESSION_RULE_CD,
1042     NEW_REFERENCES.REFERENCE_CD,
1043     NEW_REFERENCES.RUL_SEQUENCE_NUMBER,
1044     NEW_REFERENCES.ATTENDANCE_TYPE,
1045     NEW_REFERENCES.OU_ORG_UNIT_CD,
1046     NEW_REFERENCES.OU_START_DT,
1047     NEW_REFERENCES.COURSE_TYPE,
1048     NEW_REFERENCES.CRV_COURSE_CD,
1049     NEW_REFERENCES.CRV_VERSION_NUMBER,
1050     NEW_REFERENCES.SCA_PERSON_ID,
1051     NEW_REFERENCES.SCA_COURSE_CD,
1052     NEW_REFERENCES.PRO_PROGRESSION_RULE_CAT,
1053     NEW_REFERENCES.PRO_PRA_SEQUENCE_NUMBER,
1054     NEW_REFERENCES.PRO_SEQUENCE_NUMBER,
1055     NEW_REFERENCES.SPO_PERSON_ID,
1056     NEW_REFERENCES.SPO_COURSE_CD,
1057     NEW_REFERENCES.SPO_SEQUENCE_NUMBER,
1058     NEW_REFERENCES.LOGICAL_DELETE_DT,
1059     NEW_REFERENCES.MESSAGE,
1060     X_LAST_UPDATE_DATE,
1061     X_LAST_UPDATED_BY,
1062     X_LAST_UPDATE_DATE,
1063     X_LAST_UPDATED_BY,
1064     X_LAST_UPDATE_LOGIN,
1065     NEW_REFERENCES.ORG_ID,
1066     NEW_REFERENCES.MIN_CP,
1067     NEW_REFERENCES.MAX_CP,
1068     NEW_REFERENCES.IGS_PR_CLASS_STD_ID
1069   );
1070 
1071   open c;
1072   fetch c into X_ROWID;
1073   if (c%notfound) then
1074     close c;
1075     raise no_data_found;
1076   end if;
1077   close c;
1078 end INSERT_ROW;
1079 
1080 procedure LOCK_ROW (
1081   X_ROWID in VARCHAR2,
1082   X_PROGRESSION_RULE_CAT in VARCHAR2,
1083   X_SEQUENCE_NUMBER in NUMBER,
1084   X_S_RELATION_TYPE in VARCHAR2,
1085   X_PROGRESSION_RULE_CD in VARCHAR2,
1086   X_REFERENCE_CD in VARCHAR2,
1087   X_RUL_SEQUENCE_NUMBER in NUMBER,
1088   X_ATTENDANCE_TYPE in VARCHAR2,
1089   X_OU_ORG_UNIT_CD in VARCHAR2,
1090   X_OU_START_DT in DATE,
1091   X_COURSE_TYPE in VARCHAR2,
1092   X_CRV_COURSE_CD in VARCHAR2,
1093   X_CRV_VERSION_NUMBER in NUMBER,
1094   X_SCA_PERSON_ID in NUMBER,
1095   X_SCA_COURSE_CD in VARCHAR2,
1096   X_PRO_PROGRESSION_RULE_CAT in VARCHAR2,
1097   X_PRO_PRA_SEQUENCE_NUMBER in NUMBER,
1098   X_PRO_SEQUENCE_NUMBER in NUMBER,
1099   X_SPO_PERSON_ID in NUMBER,
1100   X_SPO_COURSE_CD in VARCHAR2,
1101   X_SPO_SEQUENCE_NUMBER in NUMBER,
1102   X_LOGICAL_DELETE_DT in DATE,
1103   X_MESSAGE in VARCHAR2,
1104   X_MIN_CP IN NUMBER DEFAULT NULL,
1105   X_MAX_CP IN NUMBER DEFAULT NULL,
1106   X_IGS_PR_CLASS_STD_ID IN NUMBER DEFAULT NULL
1107 ) AS
1108   cursor c1 is select
1109       S_RELATION_TYPE,
1110       PROGRESSION_RULE_CD,
1111       REFERENCE_CD,
1112       RUL_SEQUENCE_NUMBER,
1113       ATTENDANCE_TYPE,
1114       OU_ORG_UNIT_CD,
1115       OU_START_DT,
1116       COURSE_TYPE,
1117       CRV_COURSE_CD,
1118       CRV_VERSION_NUMBER,
1119       SCA_PERSON_ID,
1120       SCA_COURSE_CD,
1121       PRO_PROGRESSION_RULE_CAT,
1122       PRO_PRA_SEQUENCE_NUMBER,
1123       PRO_SEQUENCE_NUMBER,
1124       SPO_PERSON_ID,
1125       SPO_COURSE_CD,
1126       SPO_SEQUENCE_NUMBER,
1127       LOGICAL_DELETE_DT,
1128       MESSAGE,
1129       MIN_CP,
1130       MAX_CP,
1131       IGS_PR_CLASS_STD_ID
1132     from IGS_PR_RU_APPL_ALL
1133     where ROWID = X_ROWID for update nowait;
1134   tlinfo c1%rowtype;
1135 
1136 begin
1137   open c1;
1138   fetch c1 into tlinfo;
1139   if (c1%notfound) then
1140     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1141       IGS_GE_MSG_STACK.ADD;
1142 	close c1;
1143     app_exception.raise_exception;
1144 
1145     return;
1146   end if;
1147   close c1;
1148 
1149   if ( (tlinfo.S_RELATION_TYPE = X_S_RELATION_TYPE)
1150       AND ((tlinfo.PROGRESSION_RULE_CD = X_PROGRESSION_RULE_CD)
1151            OR ((tlinfo.PROGRESSION_RULE_CD is null)
1152                AND (X_PROGRESSION_RULE_CD is null)))
1153       AND ((tlinfo.REFERENCE_CD = X_REFERENCE_CD)
1154            OR ((tlinfo.REFERENCE_CD is null)
1155                AND (X_REFERENCE_CD is null)))
1156       AND ((tlinfo.RUL_SEQUENCE_NUMBER = X_RUL_SEQUENCE_NUMBER)
1157            OR ((tlinfo.RUL_SEQUENCE_NUMBER is null)
1158                AND (X_RUL_SEQUENCE_NUMBER is null)))
1159       AND ((tlinfo.ATTENDANCE_TYPE = X_ATTENDANCE_TYPE)
1160            OR ((tlinfo.ATTENDANCE_TYPE is null)
1161                AND (X_ATTENDANCE_TYPE is null)))
1162       AND ((tlinfo.OU_ORG_UNIT_CD = X_OU_ORG_UNIT_CD)
1163            OR ((tlinfo.OU_ORG_UNIT_CD is null)
1164                AND (X_OU_ORG_UNIT_CD is null)))
1165       AND ((tlinfo.OU_START_DT = X_OU_START_DT)
1166            OR ((tlinfo.OU_START_DT is null)
1167                AND (X_OU_START_DT is null)))
1168       AND ((tlinfo.COURSE_TYPE = X_COURSE_TYPE)
1169            OR ((tlinfo.COURSE_TYPE is null)
1170                AND (X_COURSE_TYPE is null)))
1171       AND ((tlinfo.CRV_COURSE_CD = X_CRV_COURSE_CD)
1172            OR ((tlinfo.CRV_COURSE_CD is null)
1173                AND (X_CRV_COURSE_CD is null)))
1174       AND ((tlinfo.CRV_VERSION_NUMBER = X_CRV_VERSION_NUMBER)
1175            OR ((tlinfo.CRV_VERSION_NUMBER is null)
1176                AND (X_CRV_VERSION_NUMBER is null)))
1177       AND ((tlinfo.SCA_PERSON_ID = X_SCA_PERSON_ID)
1178            OR ((tlinfo.SCA_PERSON_ID is null)
1179                AND (X_SCA_PERSON_ID is null)))
1180       AND ((tlinfo.SCA_COURSE_CD = X_SCA_COURSE_CD)
1181            OR ((tlinfo.SCA_COURSE_CD is null)
1182                AND (X_SCA_COURSE_CD is null)))
1183       AND ((tlinfo.PRO_PROGRESSION_RULE_CAT = X_PRO_PROGRESSION_RULE_CAT)
1184            OR ((tlinfo.PRO_PROGRESSION_RULE_CAT is null)
1185                AND (X_PRO_PROGRESSION_RULE_CAT is null)))
1186       AND ((tlinfo.PRO_PRA_SEQUENCE_NUMBER = X_PRO_PRA_SEQUENCE_NUMBER)
1187            OR ((tlinfo.PRO_PRA_SEQUENCE_NUMBER is null)
1188                AND (X_PRO_PRA_SEQUENCE_NUMBER is null)))
1189       AND ((tlinfo.PRO_SEQUENCE_NUMBER = X_PRO_SEQUENCE_NUMBER)
1190            OR ((tlinfo.PRO_SEQUENCE_NUMBER is null)
1191                AND (X_PRO_SEQUENCE_NUMBER is null)))
1192       AND ((tlinfo.SPO_PERSON_ID = X_SPO_PERSON_ID)
1193            OR ((tlinfo.SPO_PERSON_ID is null)
1194                AND (X_SPO_PERSON_ID is null)))
1195       AND ((tlinfo.SPO_COURSE_CD = X_SPO_COURSE_CD)
1196            OR ((tlinfo.SPO_COURSE_CD is null)
1197                AND (X_SPO_COURSE_CD is null)))
1198       AND ((tlinfo.SPO_SEQUENCE_NUMBER = X_SPO_SEQUENCE_NUMBER)
1199            OR ((tlinfo.SPO_SEQUENCE_NUMBER is null)
1200                AND (X_SPO_SEQUENCE_NUMBER is null)))
1201       AND ((tlinfo.LOGICAL_DELETE_DT = X_LOGICAL_DELETE_DT)
1202            OR ((tlinfo.LOGICAL_DELETE_DT is null)
1203                AND (X_LOGICAL_DELETE_DT is null)))
1204       AND ((tlinfo.MIN_CP = X_MIN_CP)
1205            OR ((tlinfo.MIN_CP is null)
1206                AND (X_MIN_CP is null)))
1207       AND ((tlinfo.MAX_CP = X_MAX_CP)
1208            OR ((tlinfo.MAX_CP is null)
1209                AND (X_MAX_CP is null)))
1210       AND ((tlinfo.IGS_PR_CLASS_STD_ID = X_IGS_PR_CLASS_STD_ID)
1211            OR ((tlinfo.IGS_PR_CLASS_STD_ID is null)
1212                AND (X_IGS_PR_CLASS_STD_ID is null)))
1213       AND ((tlinfo.MESSAGE = X_MESSAGE)
1214            OR ((tlinfo.MESSAGE is null)
1215                AND (X_MESSAGE is null)))
1216   ) then
1217     null;
1218   else
1219     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1220       IGS_GE_MSG_STACK.ADD;
1221     app_exception.raise_exception;
1222   end if;
1223   return;
1224 end LOCK_ROW;
1225 
1226 procedure UPDATE_ROW (
1227   X_ROWID in VARCHAR2,
1228   X_PROGRESSION_RULE_CAT in VARCHAR2,
1229   X_SEQUENCE_NUMBER in NUMBER,
1230   X_S_RELATION_TYPE in VARCHAR2,
1231   X_PROGRESSION_RULE_CD in VARCHAR2,
1232   X_REFERENCE_CD in VARCHAR2,
1233   X_RUL_SEQUENCE_NUMBER in NUMBER,
1234   X_ATTENDANCE_TYPE in VARCHAR2,
1235   X_OU_ORG_UNIT_CD in VARCHAR2,
1236   X_OU_START_DT in DATE,
1237   X_COURSE_TYPE in VARCHAR2,
1238   X_CRV_COURSE_CD in VARCHAR2,
1239   X_CRV_VERSION_NUMBER in NUMBER,
1240   X_SCA_PERSON_ID in NUMBER,
1241   X_SCA_COURSE_CD in VARCHAR2,
1242   X_PRO_PROGRESSION_RULE_CAT in VARCHAR2,
1243   X_PRO_PRA_SEQUENCE_NUMBER in NUMBER,
1244   X_PRO_SEQUENCE_NUMBER in NUMBER,
1245   X_SPO_PERSON_ID in NUMBER,
1246   X_SPO_COURSE_CD in VARCHAR2,
1247   X_SPO_SEQUENCE_NUMBER in NUMBER,
1248   X_LOGICAL_DELETE_DT in DATE,
1249   X_MESSAGE in VARCHAR2,
1250   X_MODE in VARCHAR2 default 'R',
1251   X_MIN_CP IN NUMBER DEFAULT NULL,
1252   X_MAX_CP IN NUMBER DEFAULT NULL,
1253   X_IGS_PR_CLASS_STD_ID IN NUMBER DEFAULT NULL
1254   ) AS
1255     X_LAST_UPDATE_DATE DATE;
1256     X_LAST_UPDATED_BY NUMBER;
1257     X_LAST_UPDATE_LOGIN NUMBER;
1258 begin
1259   X_LAST_UPDATE_DATE := SYSDATE;
1260   if(X_MODE = 'I') then
1261     X_LAST_UPDATED_BY := 1;
1262     X_LAST_UPDATE_LOGIN := 0;
1263   elsif (X_MODE = 'R') then
1264     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1265     if X_LAST_UPDATED_BY is NULL then
1266       X_LAST_UPDATED_BY := -1;
1267     end if;
1268     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1269     if X_LAST_UPDATE_LOGIN is NULL then
1270       X_LAST_UPDATE_LOGIN := -1;
1271     end if;
1272   else
1273     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1274       IGS_GE_MSG_STACK.ADD;
1275     app_exception.raise_exception;
1276   end if;
1277 Before_DML (
1278     p_action => 'UPDATE',
1279     x_rowid => x_rowid ,
1280     x_sca_course_cd => x_sca_course_cd,
1281     x_pro_progression_rule_cat => x_pro_progression_rule_cat ,
1282     x_pro_pra_sequence_number => x_pro_pra_sequence_number ,
1283     x_pro_sequence_number => x_pro_sequence_number ,
1284     x_spo_person_id => x_spo_person_id ,
1285     x_spo_course_cd => x_spo_course_cd ,
1286     x_spo_sequence_number => x_spo_sequence_number ,
1287     x_logical_delete_dt => x_logical_delete_dt ,
1288     x_message => x_message ,
1289     x_progression_rule_cat => x_progression_rule_cat ,
1290     x_sequence_number => x_sequence_number ,
1291     x_s_relation_type => x_s_relation_type ,
1292     x_progression_rule_cd => x_progression_rule_cd ,
1293     x_reference_cd => x_reference_cd ,
1294     x_rul_sequence_number => x_rul_sequence_number ,
1295     x_attendance_type => x_attendance_type ,
1296     x_ou_org_unit_cd => x_ou_org_unit_cd ,
1297     x_ou_start_dt => x_ou_start_dt ,
1298     x_course_type => x_course_type ,
1299     x_crv_course_cd => x_crv_course_cd ,
1300     x_crv_version_number => x_crv_version_number ,
1301     x_sca_person_id => x_sca_person_id ,
1302     x_creation_date => x_last_update_date ,
1303     x_created_by => x_last_updated_by ,
1304     x_last_update_date => x_last_update_date ,
1305     x_last_updated_by => x_last_updated_by ,
1306     x_last_update_login =>x_last_update_login,
1307     x_min_cp => x_min_cp,
1308     x_max_cp => x_max_cp,
1309     x_igs_pr_class_std_id => x_igs_pr_class_std_id
1310   );
1311 
1312   update IGS_PR_RU_APPL_ALL set
1313     S_RELATION_TYPE = NEW_REFERENCES.S_RELATION_TYPE,
1314     PROGRESSION_RULE_CD = NEW_REFERENCES.PROGRESSION_RULE_CD,
1315     REFERENCE_CD = NEW_REFERENCES.REFERENCE_CD,
1316     RUL_SEQUENCE_NUMBER = NEW_REFERENCES.RUL_SEQUENCE_NUMBER,
1317     ATTENDANCE_TYPE = NEW_REFERENCES.ATTENDANCE_TYPE,
1318     OU_ORG_UNIT_CD = NEW_REFERENCES.OU_ORG_UNIT_CD,
1319     OU_START_DT = NEW_REFERENCES.OU_START_DT,
1320     COURSE_TYPE = NEW_REFERENCES.COURSE_TYPE,
1321     CRV_COURSE_CD = NEW_REFERENCES.CRV_COURSE_CD,
1322     CRV_VERSION_NUMBER = NEW_REFERENCES.CRV_VERSION_NUMBER,
1323     SCA_PERSON_ID = NEW_REFERENCES.SCA_PERSON_ID,
1324     SCA_COURSE_CD = NEW_REFERENCES.SCA_COURSE_CD,
1325     PRO_PROGRESSION_RULE_CAT = NEW_REFERENCES.PRO_PROGRESSION_RULE_CAT,
1326     PRO_PRA_SEQUENCE_NUMBER = NEW_REFERENCES.PRO_PRA_SEQUENCE_NUMBER,
1327     PRO_SEQUENCE_NUMBER = NEW_REFERENCES.PRO_SEQUENCE_NUMBER,
1328     SPO_PERSON_ID = NEW_REFERENCES.SPO_PERSON_ID,
1329     SPO_COURSE_CD = NEW_REFERENCES.SPO_COURSE_CD,
1330     SPO_SEQUENCE_NUMBER = NEW_REFERENCES.SPO_SEQUENCE_NUMBER,
1331     LOGICAL_DELETE_DT = NEW_REFERENCES.LOGICAL_DELETE_DT,
1332     MESSAGE = NEW_REFERENCES.MESSAGE,
1333     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1334     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1335     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1336     MIN_CP = NEW_REFERENCES.MIN_CP,
1337     MAX_CP = NEW_REFERENCES.MAX_CP,
1338     IGS_PR_CLASS_STD_ID = NEW_REFERENCES.IGS_PR_CLASS_STD_ID
1339   where ROWID = X_ROWID  ;
1340 
1341   if (sql%notfound) then
1342     raise no_data_found;
1343   end if;
1344 end UPDATE_ROW;
1345 
1346 procedure ADD_ROW (
1347   X_ROWID in out NOCOPY VARCHAR2,
1348   X_PROGRESSION_RULE_CAT in VARCHAR2,
1349   X_SEQUENCE_NUMBER in NUMBER,
1350   X_S_RELATION_TYPE in VARCHAR2,
1351   X_PROGRESSION_RULE_CD in VARCHAR2,
1352   X_REFERENCE_CD in VARCHAR2,
1353   X_RUL_SEQUENCE_NUMBER in NUMBER,
1354   X_ATTENDANCE_TYPE in VARCHAR2,
1355   X_OU_ORG_UNIT_CD in VARCHAR2,
1356   X_OU_START_DT in DATE,
1357   X_COURSE_TYPE in VARCHAR2,
1358   X_CRV_COURSE_CD in VARCHAR2,
1359   X_CRV_VERSION_NUMBER in NUMBER,
1360   X_SCA_PERSON_ID in NUMBER,
1361   X_SCA_COURSE_CD in VARCHAR2,
1362   X_PRO_PROGRESSION_RULE_CAT in VARCHAR2,
1363   X_PRO_PRA_SEQUENCE_NUMBER in NUMBER,
1364   X_PRO_SEQUENCE_NUMBER in NUMBER,
1365   X_SPO_PERSON_ID in NUMBER,
1366   X_SPO_COURSE_CD in VARCHAR2,
1367   X_SPO_SEQUENCE_NUMBER in NUMBER,
1368   X_LOGICAL_DELETE_DT in DATE,
1369   X_MESSAGE in VARCHAR2,
1370   X_MODE in VARCHAR2 default 'R',
1371   X_ORG_ID IN NUMBER,
1372   X_MIN_CP IN NUMBER DEFAULT NULL,
1373   X_MAX_CP IN NUMBER DEFAULT NULL,
1374   X_IGS_PR_CLASS_STD_ID IN NUMBER DEFAULT NULL
1375   ) AS
1376      cursor c1 is select rowid from IGS_PR_RU_APPL_ALL
1377      where PROGRESSION_RULE_CAT = X_PROGRESSION_RULE_CAT
1378      and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER  ;
1379 begin
1380   open c1;
1381   fetch c1 into X_ROWID;
1382   if (c1%notfound) then
1383     close c1;
1384     INSERT_ROW (
1385      X_ROWID,
1386      X_PROGRESSION_RULE_CAT,
1387      X_SEQUENCE_NUMBER,
1388      X_S_RELATION_TYPE,
1389      X_PROGRESSION_RULE_CD,
1390      X_REFERENCE_CD,
1391      X_RUL_SEQUENCE_NUMBER,
1392      X_ATTENDANCE_TYPE,
1393      X_OU_ORG_UNIT_CD,
1394      X_OU_START_DT,
1395      X_COURSE_TYPE,
1396      X_CRV_COURSE_CD,
1397      X_CRV_VERSION_NUMBER,
1398      X_SCA_PERSON_ID,
1399      X_SCA_COURSE_CD,
1400      X_PRO_PROGRESSION_RULE_CAT,
1401      X_PRO_PRA_SEQUENCE_NUMBER,
1402      X_PRO_SEQUENCE_NUMBER,
1403      X_SPO_PERSON_ID,
1404      X_SPO_COURSE_CD,
1405      X_SPO_SEQUENCE_NUMBER,
1406      X_LOGICAL_DELETE_DT,
1407      X_MESSAGE,
1408      X_MODE,
1409      X_ORG_ID,
1410      X_MIN_CP,
1411      X_MAX_CP,
1412      X_IGS_PR_CLASS_STD_ID);
1413     return;
1414   end if;
1415   close c1;
1416   UPDATE_ROW (
1417    X_ROWID,
1418    X_PROGRESSION_RULE_CAT,
1419    X_SEQUENCE_NUMBER,
1420    X_S_RELATION_TYPE,
1421    X_PROGRESSION_RULE_CD,
1422    X_REFERENCE_CD,
1423    X_RUL_SEQUENCE_NUMBER,
1424    X_ATTENDANCE_TYPE,
1425    X_OU_ORG_UNIT_CD,
1426    X_OU_START_DT,
1427    X_COURSE_TYPE,
1428    X_CRV_COURSE_CD,
1429    X_CRV_VERSION_NUMBER,
1430    X_SCA_PERSON_ID,
1431    X_SCA_COURSE_CD,
1432    X_PRO_PROGRESSION_RULE_CAT,
1433    X_PRO_PRA_SEQUENCE_NUMBER,
1434    X_PRO_SEQUENCE_NUMBER,
1435    X_SPO_PERSON_ID,
1436    X_SPO_COURSE_CD,
1437    X_SPO_SEQUENCE_NUMBER,
1438    X_LOGICAL_DELETE_DT,
1439    X_MESSAGE,
1440    X_MODE,
1441    X_MIN_CP,
1442    X_MAX_CP,
1443    X_IGS_PR_CLASS_STD_ID);
1444 
1445 end ADD_ROW;
1446 
1447 procedure DELETE_ROW (
1448   X_ROWID in VARCHAR2
1449 ) is
1450 begin
1451   Before_DML (
1452     p_action => 'DELETE',
1453     x_rowid => X_ROWID
1454   );
1455   delete from IGS_PR_RU_APPL_ALL
1456   where ROWID = X_ROWID;
1457   if (sql%notfound) then
1458     raise no_data_found;
1459   end if;
1460 end DELETE_ROW;
1461 
1462 PROCEDURE Check_Constraints (
1463 	Column_Name IN VARCHAR2 DEFAULT NULL,
1464 	Column_Value IN VARCHAR2 DEFAULT NULL
1465 	) AS
1466     BEGIN
1467 IF Column_Name is null THEN
1468   NULL;
1469 ELSIF upper(Column_name) = 'ATTENDANCE_TYPE' THEN
1470   new_references.ATTENDANCE_TYPE:= COLUMN_VALUE ;
1471 
1472 ELSIF upper(Column_name) = 'COURSE_TYPE' THEN
1473   new_references.COURSE_TYPE:= COLUMN_VALUE ;
1474 
1475 ELSIF upper(Column_name) = 'CRV_COURSE_CD' THEN
1476   new_references.CRV_COURSE_CD:= COLUMN_VALUE ;
1477 
1478 ELSIF upper(Column_name) = 'OU_ORG_UNIT_CD' THEN
1479   new_references.OU_ORG_UNIT_CD:= COLUMN_VALUE ;
1480 
1481 ELSIF upper(Column_name) = 'PROGRESSION_RULE_CAT' THEN
1482   new_references.PROGRESSION_RULE_CAT:= COLUMN_VALUE ;
1483 
1484 ELSIF upper(Column_name) = 'PROGRESSION_RULE_CD' THEN
1485   new_references.PROGRESSION_RULE_CD:= COLUMN_VALUE ;
1486 
1487 ELSIF upper(Column_name) = 'REFERENCE_CD' THEN
1488   new_references.REFERENCE_CD:= COLUMN_VALUE ;
1489 
1490 ELSIF upper(Column_name) = 'SCA_COURSE_CD' THEN
1491   new_references.SCA_COURSE_CD:= COLUMN_VALUE ;
1492 
1493 ELSIF upper(Column_name) = 'SPO_COURSE_CD' THEN
1494   new_references.SPO_COURSE_CD:= COLUMN_VALUE ;
1495 
1496 ELSIF upper(Column_name) = 'PRO_PROGRESSION_RULE_CAT' THEN
1497   new_references.PRO_PROGRESSION_RULE_CAT:= COLUMN_VALUE ;
1498 
1499 ELSIF upper(Column_name) = 'S_RELATION_TYPE' THEN
1500   new_references.S_RELATION_TYPE:= COLUMN_VALUE ;
1501 
1502 ELSIF upper(Column_name) = 'PRO_PRA_SEQUENCE_NUMBER' THEN
1503   new_references.PRO_PRA_SEQUENCE_NUMBER:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
1504 
1505 ELSIF upper(Column_name) = 'PRO_SEQUENCE_NUMBER' THEN
1506   new_references.PRO_SEQUENCE_NUMBER:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
1507 
1508 ELSIF upper(Column_name) = 'SPO_SEQUENCE_NUMBER' THEN
1509   new_references.SPO_SEQUENCE_NUMBER:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
1510 
1511 ELSIF upper(Column_name) = 'MIN_CP' THEN
1512   new_references.MIN_CP := IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
1513 
1514 ELSIF upper(Column_name) = 'MAX_CP' THEN
1515   new_references.MAX_CP := IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
1516 
1517 ELSIF upper(Column_name) = 'IGS_PR_CLASS_STD_ID' THEN
1518   new_references.IGS_PR_CLASS_STD_ID := IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
1519 
1520 END IF ;
1521 
1522 IF upper(Column_name) = 'ATTENDANCE_TYPE' OR COLUMN_NAME IS NULL THEN
1523   IF new_references.ATTENDANCE_TYPE<> upper(new_references.ATTENDANCE_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 
1529 END IF ;
1530 
1531 IF upper(Column_name) = 'COURSE_TYPE' OR COLUMN_NAME IS NULL THEN
1532   IF new_references.COURSE_TYPE<> upper(new_references.COURSE_TYPE) then
1533     Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1534       IGS_GE_MSG_STACK.ADD;
1535     App_Exception.Raise_Exception ;
1536   END IF;
1537 
1538 END IF ;
1539 
1540 IF upper(Column_name) = 'CRV_COURSE_CD' OR COLUMN_NAME IS NULL THEN
1541   IF new_references.CRV_COURSE_CD<> upper(new_references.CRV_COURSE_CD) then
1542     Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1543       IGS_GE_MSG_STACK.ADD;
1544     App_Exception.Raise_Exception ;
1545   END IF;
1546 
1547 END IF ;
1548 
1549 
1550 IF upper(Column_name) = 'PROGRESSION_RULE_CAT' OR COLUMN_NAME IS NULL THEN
1551   IF new_references.PROGRESSION_RULE_CAT<> upper(new_references.PROGRESSION_RULE_CAT) then
1552     Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1553       IGS_GE_MSG_STACK.ADD;
1554     App_Exception.Raise_Exception ;
1555   END IF;
1556 
1557 END IF ;
1558 
1559 IF upper(Column_name) = 'PROGRESSION_RULE_CD' OR COLUMN_NAME IS NULL THEN
1560   IF new_references.PROGRESSION_RULE_CD<> upper(new_references.PROGRESSION_RULE_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 
1566 END IF ;
1567 
1568 IF upper(Column_name) = 'REFERENCE_CD' OR COLUMN_NAME IS NULL THEN
1569   IF new_references.REFERENCE_CD<> upper(new_references.REFERENCE_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 
1575 END IF ;
1576 
1577 IF upper(Column_name) = 'SCA_COURSE_CD' OR COLUMN_NAME IS NULL THEN
1578   IF new_references.SCA_COURSE_CD<> upper(new_references.SCA_COURSE_CD) then
1579     Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1580       IGS_GE_MSG_STACK.ADD;
1581     App_Exception.Raise_Exception ;
1582   END IF;
1583 
1584 END IF ;
1585 
1586 IF upper(Column_name) = 'SPO_COURSE_CD' OR COLUMN_NAME IS NULL THEN
1587   IF new_references.SPO_COURSE_CD<> upper(new_references.SPO_COURSE_CD) 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 
1593 END IF ;
1594 
1595 IF upper(Column_name) = 'PRO_PROGRESSION_RULE_CAT' OR COLUMN_NAME IS NULL THEN
1596   IF new_references.PRO_PROGRESSION_RULE_CAT<> upper(new_references.PRO_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 
1602 END IF ;
1603 
1604 IF upper(Column_name) = 'S_RELATION_TYPE' OR COLUMN_NAME IS NULL THEN
1605   IF new_references.S_RELATION_TYPE<> upper(new_references.S_RELATION_TYPE) then
1606     Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1607       IGS_GE_MSG_STACK.ADD;
1608     App_Exception.Raise_Exception ;
1609   END IF;
1610 
1611   IF new_references.S_RELATION_TYPE not in  ('OU' , 'CTY' , 'CRV' , 'SCA' , 'PRR' , 'PRGC' , 'SPO' , 'PRO' ) then
1612     Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1613       IGS_GE_MSG_STACK.ADD;
1614     App_Exception.Raise_Exception ;
1615   END IF;
1616 
1617 END IF ;
1618 
1619 IF upper(Column_name) = 'PRO_PRA_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
1620   IF new_references.PRO_PRA_SEQUENCE_NUMBER < 1 or new_references.PRO_PRA_SEQUENCE_NUMBER > 999999 then
1621     Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1622       IGS_GE_MSG_STACK.ADD;
1623     App_Exception.Raise_Exception ;
1624   END IF;
1625 
1626 END IF ;
1627 
1628 IF upper(Column_name) = 'PRO_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
1629   IF new_references.PRO_SEQUENCE_NUMBER < 1 or new_references.PRO_SEQUENCE_NUMBER > 999999 then
1630     Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1631       IGS_GE_MSG_STACK.ADD;
1632     App_Exception.Raise_Exception ;
1633   END IF;
1634 
1635 END IF ;
1636 
1637 IF upper(Column_name) = 'SPO_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
1638   IF new_references.SPO_SEQUENCE_NUMBER < 1 or new_references.SPO_SEQUENCE_NUMBER > 999999 then
1639     Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1640       IGS_GE_MSG_STACK.ADD;
1641     App_Exception.Raise_Exception ;
1642   END IF;
1643 
1644 END IF ;
1645 
1646 END Check_Constraints;
1647 
1648 
1649 end IGS_PR_RU_APPL_PKG;