[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;