[Home] [Help]
PACKAGE BODY: APPS.IGS_PR_S_CRV_PRG_CAL_PKG
Source
1 package body IGS_PR_S_CRV_PRG_CAL_PKG as
2 /* $Header: IGSQI21B.pls 115.3 2002/11/29 03:19:23 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PR_S_CRV_PRG_CAL%RowType;
6 new_references IGS_PR_S_CRV_PRG_CAL%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_course_cd IN VARCHAR2 DEFAULT NULL,
12 x_version_number IN NUMBER DEFAULT NULL,
13 x_prg_cal_type IN VARCHAR2 DEFAULT NULL,
14 x_stream_num IN NUMBER DEFAULT NULL,
15 x_show_cause_length IN NUMBER DEFAULT NULL,
16 x_appeal_length IN NUMBER DEFAULT NULL,
17 x_creation_date IN DATE DEFAULT NULL,
18 x_created_by IN NUMBER DEFAULT NULL,
19 x_last_update_date IN DATE DEFAULT NULL,
20 x_last_updated_by IN NUMBER DEFAULT NULL,
21 x_last_update_login IN NUMBER DEFAULT NULL
22 ) AS
23
24 CURSOR cur_old_ref_values IS
25 SELECT *
26 FROM IGS_PR_S_CRV_PRG_CAL
27 WHERE rowid = x_rowid;
28
29 BEGIN
30
31 l_rowid := x_rowid;
32
33 -- Code for setting the Old and New Reference Values.
34 -- Populate Old Values.
35 Open cur_old_ref_values;
36 Fetch cur_old_ref_values INTO old_references;
37 IF (cur_old_ref_values%NOTFOUND) AND (p_action not in ('INSERT','VALIDATE_INSERT')) THEN
38 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
39 IGS_GE_MSG_STACK.ADD;
40 Close cur_old_ref_values;
41 App_Exception.Raise_Exception;
42
43 Return;
44 END IF;
45 Close cur_old_ref_values;
46
47 -- Populate New Values.
48 new_references.course_cd := x_course_cd;
49 new_references.version_number := x_version_number;
50 new_references.prg_cal_type := x_prg_cal_type;
51 new_references.stream_num := x_stream_num;
52 new_references.show_cause_length := x_show_cause_length;
53 new_references.appeal_length := x_appeal_length;
54 IF (p_action = 'UPDATE') THEN
55 new_references.creation_date := old_references.creation_date;
56 new_references.created_by := old_references.created_by;
57 ELSE
58 new_references.creation_date := x_creation_date;
59 new_references.created_by := x_created_by;
60 END IF;
61 new_references.last_update_date := x_last_update_date;
62 new_references.last_updated_by := x_last_updated_by;
63 new_references.last_update_login := x_last_update_login;
64
65 END Set_Column_Values;
66
67 -- Trigger description :-
68 -- "OSS_TST".trg_scpca_br_iu
69 -- BEFORE INSERT OR UPDATE
70 -- ON IGS_PR_S_CRV_PRG_CAL
71 -- FOR EACH ROW
72
73 PROCEDURE BeforeRowInsertUpdate1(
74 p_inserting IN BOOLEAN DEFAULT FALSE,
75 p_updating IN BOOLEAN DEFAULT FALSE,
76 p_deleting IN BOOLEAN DEFAULT FALSE
77 ) AS
78 v_message_name varchar2(30);
79 BEGIN
80 -- Validate the progression calendar type
81 IF p_inserting THEN
82 IF IGS_PR_VAL_SCPCA.prgp_val_cfg_cat (
83 new_references.prg_cal_type,
84 v_message_name) = FALSE THEN
85 Fnd_Message.Set_Name('IGS',v_message_name);
86 IGS_GE_MSG_STACK.ADD;
87 App_Exception.Raise_Exception;
88 END IF;
89 END IF;
90 -- Validate the show cause length
91 IF p_inserting OR (p_updating AND
92 new_references.show_cause_length <> old_references.show_cause_length) THEN
93 IF IGS_PR_VAL_SCPCA.prgp_val_scpca_cause (
94 new_references.course_cd,
95 new_references.version_number,
96 new_references.show_cause_length,
97 v_message_name) = FALSE THEN
98 Fnd_Message.Set_Name('IGS',v_message_name);
99 IGS_GE_MSG_STACK.ADD;
100 App_Exception.Raise_Exception;
101 END IF;
102 END IF;
103 -- Validate the appeal length
104 IF p_inserting OR (p_updating AND
105 new_references.appeal_length <> old_references.appeal_length) THEN
106 IF IGS_PR_VAL_SCPCA.prgp_val_scpca_apl (
107 new_references.course_cd,
108 new_references.version_number,
109 new_references.appeal_length,
110 v_message_name) = FALSE THEN
111 Fnd_Message.Set_Name('IGS',v_message_name);
112 IGS_GE_MSG_STACK.ADD;
113 App_Exception.Raise_Exception;
114 END IF;
115 END IF;
116
117
118 END BeforeRowInsertUpdate1;
119
120 PROCEDURE Check_Parent_Existance AS
121 BEGIN
122
123 IF (((old_references.prg_cal_type = new_references.prg_cal_type)) OR
124 ((new_references.prg_cal_type IS NULL))) THEN
125 NULL;
126 ELSE
127 IF NOT IGS_CA_TYPE_PKG.Get_PK_For_Validation (
128 new_references.prg_cal_type
129 ) THEN
130 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
131 IGS_GE_MSG_STACK.ADD;
132 App_Exception.Raise_Exception;
133 END IF;
134 END IF;
135
136 IF (((old_references.course_cd = new_references.course_cd) AND
137 (old_references.version_number = new_references.version_number)) OR
138 ((new_references.course_cd IS NULL) OR
139 (new_references.version_number IS NULL))) THEN
140 NULL;
141 ELSE
142 IF NOT IGS_PR_S_CRV_PRG_CON_PKG.Get_PK_For_Validation (
143 new_references.course_cd,
144 new_references.version_number
145 ) THEN
146 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
147 IGS_GE_MSG_STACK.ADD;
148 App_Exception.Raise_Exception;
149 END IF;
150 END IF;
151
152 END Check_Parent_Existance;
153
154 FUNCTION Get_PK_For_Validation (
155 x_course_cd IN VARCHAR2,
156 x_version_number IN NUMBER,
157 x_prg_cal_type IN VARCHAR2
158 ) RETURN BOOLEAN AS
159
160 CURSOR cur_rowid IS
161 SELECT rowid
162 FROM IGS_PR_S_CRV_PRG_CAL
163 WHERE course_cd = x_course_cd
164 AND version_number = x_version_number
165 AND prg_cal_type = x_prg_cal_type
166 FOR UPDATE NOWAIT;
167
168 lv_rowid cur_rowid%RowType;
169
170 BEGIN
171
172 Open cur_rowid;
173 Fetch cur_rowid INTO lv_rowid;
174 IF (cur_rowid%FOUND) THEN
175 Close Cur_rowid;
176 Return(TRUE);
177 ELSE
178 Close cur_rowid;
179 Return(FALSE);
180 END IF;
181
182 END Get_PK_For_Validation;
183
184 PROCEDURE GET_FK_IGS_CA_TYPE (
185 x_cal_type IN VARCHAR2
186 ) AS
187
188 CURSOR cur_rowid IS
189 SELECT rowid
190 FROM IGS_PR_S_CRV_PRG_CAL
191 WHERE prg_cal_type = x_cal_type ;
192
193 lv_rowid cur_rowid%RowType;
194
195 BEGIN
196
197 Open cur_rowid;
198 Fetch cur_rowid INTO lv_rowid;
199 IF (cur_rowid%FOUND) THEN
200 Fnd_Message.Set_Name ('IGS', 'IGS_PR_SCPCA_CAT_FK');
201 IGS_GE_MSG_STACK.ADD;
202 Close cur_rowid;
203 App_Exception.Raise_Exception;
204
205 Return;
206 END IF;
207 Close cur_rowid;
208
209 END GET_FK_IGS_CA_TYPE;
210
211 PROCEDURE GET_FK_IGS_PR_S_CRV_PRG_CON (
212 x_course_cd IN VARCHAR2,
213 x_version_number IN NUMBER
214 ) AS
215
216 CURSOR cur_rowid IS
217 SELECT rowid
218 FROM IGS_PR_S_CRV_PRG_CAL
219 WHERE course_cd = x_course_cd
220 AND version_number = x_version_number ;
221
222 lv_rowid cur_rowid%RowType;
223
224 BEGIN
225
226 Open cur_rowid;
227 Fetch cur_rowid INTO lv_rowid;
228 IF (cur_rowid%FOUND) THEN
229 Fnd_Message.Set_Name ('IGS', 'IGS_PR_SCPCA_SCPC_FK');
230 IGS_GE_MSG_STACK.ADD;
231 Close cur_rowid;
232 App_Exception.Raise_Exception;
233
234 Return;
235 END IF;
236 Close cur_rowid;
237
238 END GET_FK_IGS_PR_S_CRV_PRG_CON;
239
240 PROCEDURE Before_DML (
241 p_action IN VARCHAR2,
242 x_rowid IN VARCHAR2 DEFAULT NULL,
243 x_course_cd IN VARCHAR2 DEFAULT NULL,
244 x_version_number IN NUMBER DEFAULT NULL,
245 x_prg_cal_type IN VARCHAR2 DEFAULT NULL,
246 x_stream_num IN NUMBER DEFAULT NULL,
247 x_show_cause_length IN NUMBER DEFAULT NULL,
248 x_appeal_length IN NUMBER DEFAULT NULL,
249 x_creation_date IN DATE DEFAULT NULL,
250 x_created_by IN NUMBER DEFAULT NULL,
251 x_last_update_date IN DATE DEFAULT NULL,
252 x_last_updated_by IN NUMBER DEFAULT NULL,
253 x_last_update_login IN NUMBER DEFAULT NULL
254 ) AS
255 BEGIN
256
257 Set_Column_Values (
258 p_action,
259 x_rowid,
260 x_course_cd,
261 x_version_number,
262 x_prg_cal_type,
263 x_stream_num,
264 x_show_cause_length,
265 x_appeal_length,
266 x_creation_date,
267 x_created_by,
268 x_last_update_date,
269 x_last_updated_by,
270 x_last_update_login
271 );
272
273 IF (p_action = 'INSERT') THEN
274 -- Call all the procedures related to Before Insert.
275 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
276 IF Get_PK_For_Validation (
277 new_references.course_cd,
278 new_references.version_number,
279 new_references.prg_cal_type
280 ) THEN
281 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
282 IGS_GE_MSG_STACK.ADD;
283 App_Exception.Raise_Exception;
284 END IF;
285 Check_Constraints;
286 Check_Parent_Existance;
287 ELSIF (p_action = 'UPDATE') THEN
288 -- Call all the procedures related to Before Update.
289 BeforeRowInsertUpdate1 ( p_updating => TRUE );
290 Check_Constraints;
291 Check_Parent_Existance;
292 ELSIF (p_action = 'VALIDATE_INSERT') THEN
293 -- Call all the procedures related to Before Insert.
294 IF Get_PK_For_Validation (
295 new_references.course_cd,
296 new_references.version_number,
297 new_references.prg_cal_type
298 ) THEN
299 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
300 IGS_GE_MSG_STACK.ADD;
301 App_Exception.Raise_Exception;
302 END IF;
303 Check_Constraints;
304 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
305 -- Call all the procedures related to Before Update.
306 Check_Constraints;
307 END IF;
308
309 END Before_DML;
310
311 procedure INSERT_ROW (
312 X_ROWID in out NOCOPY VARCHAR2,
313 X_COURSE_CD in VARCHAR2,
314 X_VERSION_NUMBER in NUMBER,
315 X_PRG_CAL_TYPE in VARCHAR2,
316 X_STREAM_NUM in NUMBER,
317 X_SHOW_CAUSE_LENGTH in NUMBER,
318 X_APPEAL_LENGTH in NUMBER,
319 X_MODE in VARCHAR2 default 'R'
320 ) as
321 cursor C is select ROWID from IGS_PR_S_CRV_PRG_CAL
322 where COURSE_CD = X_COURSE_CD
323 and VERSION_NUMBER = X_VERSION_NUMBER
324 and PRG_CAL_TYPE = X_PRG_CAL_TYPE;
325 X_LAST_UPDATE_DATE DATE;
326 X_LAST_UPDATED_BY NUMBER;
327 X_LAST_UPDATE_LOGIN NUMBER;
328 begin
329 X_LAST_UPDATE_DATE := SYSDATE;
330 if(X_MODE = 'I') then
331 X_LAST_UPDATED_BY := 1;
332 X_LAST_UPDATE_LOGIN := 0;
333 elsif (X_MODE = 'R') then
334 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
335 if X_LAST_UPDATED_BY is NULL then
336 X_LAST_UPDATED_BY := -1;
337 end if;
338 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
339 if X_LAST_UPDATE_LOGIN is NULL then
340 X_LAST_UPDATE_LOGIN := -1;
341 end if;
342 else
343 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
344 IGS_GE_MSG_STACK.ADD;
345 app_exception.raise_exception;
346 end if;
347
348 Before_DML(
349 p_action => 'INSERT',
350 x_rowid => X_ROWID,
351 x_appeal_length => X_APPEAL_LENGTH,
352 x_course_cd => X_COURSE_CD,
353 x_prg_cal_type => X_PRG_CAL_TYPE,
354 x_show_cause_length => X_SHOW_CAUSE_LENGTH,
355 x_stream_num => X_STREAM_NUM,
356 x_version_number => X_VERSION_NUMBER,
357 x_creation_date => X_LAST_UPDATE_DATE,
358 x_created_by => X_LAST_UPDATED_BY,
359 x_last_update_date => X_LAST_UPDATE_DATE,
360 x_last_updated_by => X_LAST_UPDATED_BY,
361 x_last_update_login => X_LAST_UPDATE_LOGIN
362 );
363
364 insert into IGS_PR_S_CRV_PRG_CAL (
365 COURSE_CD,
366 VERSION_NUMBER,
367 PRG_CAL_TYPE,
368 STREAM_NUM,
369 SHOW_CAUSE_LENGTH,
370 APPEAL_LENGTH,
371 CREATION_DATE,
372 CREATED_BY,
373 LAST_UPDATE_DATE,
374 LAST_UPDATED_BY,
375 LAST_UPDATE_LOGIN
376 ) values (
377 NEW_REFERENCES.COURSE_CD,
378 NEW_REFERENCES.VERSION_NUMBER,
379 NEW_REFERENCES.PRG_CAL_TYPE,
380 NEW_REFERENCES.STREAM_NUM,
381 NEW_REFERENCES.SHOW_CAUSE_LENGTH,
382 NEW_REFERENCES.APPEAL_LENGTH,
383 X_LAST_UPDATE_DATE,
384 X_LAST_UPDATED_BY,
385 X_LAST_UPDATE_DATE,
386 X_LAST_UPDATED_BY,
387 X_LAST_UPDATE_LOGIN
388 );
389
390 open c;
391 fetch c into X_ROWID;
392 if (c%notfound) then
393 close c;
394 raise no_data_found;
395 end if;
396 close c;
397
398 end INSERT_ROW;
399
400 procedure LOCK_ROW (
401 X_ROWID in VARCHAR2,
402 X_COURSE_CD in VARCHAR2,
403 X_VERSION_NUMBER in NUMBER,
404 X_PRG_CAL_TYPE in VARCHAR2,
405 X_STREAM_NUM in NUMBER,
406 X_SHOW_CAUSE_LENGTH in NUMBER,
407 X_APPEAL_LENGTH in NUMBER
408 ) as
409 cursor c1 is select
410 STREAM_NUM,
411 SHOW_CAUSE_LENGTH,
412 APPEAL_LENGTH
413 from IGS_PR_S_CRV_PRG_CAL
414 where ROWID = X_ROWID
415 for update nowait;
416 tlinfo c1%rowtype;
417
418 begin
419 open c1;
420 fetch c1 into tlinfo;
421 if (c1%notfound) then
422 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
423 IGS_GE_MSG_STACK.ADD;
424 close c1;
425 app_exception.raise_exception;
426
427 return;
428 end if;
429 close c1;
430
431 if ( (tlinfo.STREAM_NUM = X_STREAM_NUM)
432 AND ((tlinfo.SHOW_CAUSE_LENGTH = X_SHOW_CAUSE_LENGTH)
433 OR ((tlinfo.SHOW_CAUSE_LENGTH is null)
434 AND (X_SHOW_CAUSE_LENGTH is null)))
435 AND ((tlinfo.APPEAL_LENGTH = X_APPEAL_LENGTH)
436 OR ((tlinfo.APPEAL_LENGTH is null)
437 AND (X_APPEAL_LENGTH is null)))
438 ) then
439 null;
440 else
441 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
442 IGS_GE_MSG_STACK.ADD;
443 app_exception.raise_exception;
444 end if;
445 return;
446 end LOCK_ROW;
447
448 procedure UPDATE_ROW (
449 X_ROWID in VARCHAR2,
450 X_COURSE_CD in VARCHAR2,
451 X_VERSION_NUMBER in NUMBER,
452 X_PRG_CAL_TYPE in VARCHAR2,
453 X_STREAM_NUM in NUMBER,
454 X_SHOW_CAUSE_LENGTH in NUMBER,
455 X_APPEAL_LENGTH in NUMBER,
456 X_MODE in VARCHAR2 default 'R'
457 ) as
458 X_LAST_UPDATE_DATE DATE;
459 X_LAST_UPDATED_BY NUMBER;
460 X_LAST_UPDATE_LOGIN NUMBER;
461 begin
462 X_LAST_UPDATE_DATE := SYSDATE;
463 if(X_MODE = 'I') then
464 X_LAST_UPDATED_BY := 1;
465 X_LAST_UPDATE_LOGIN := 0;
466 elsif (X_MODE = 'R') then
467 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
468 if X_LAST_UPDATED_BY is NULL then
469 X_LAST_UPDATED_BY := -1;
470 end if;
471 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
472 if X_LAST_UPDATE_LOGIN is NULL then
473 X_LAST_UPDATE_LOGIN := -1;
474 end if;
475 else
476 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
477 IGS_GE_MSG_STACK.ADD;
478 app_exception.raise_exception;
479 end if;
480
481 Before_DML(
482 p_action => 'UPDATE',
483 x_rowid => X_ROWID,
484 x_appeal_length => X_APPEAL_LENGTH,
485 x_course_cd => X_COURSE_CD,
486 x_prg_cal_type => X_PRG_CAL_TYPE,
487 x_show_cause_length => X_SHOW_CAUSE_LENGTH,
488 x_stream_num => X_STREAM_NUM,
489 x_version_number => X_VERSION_NUMBER,
490 x_creation_date => X_LAST_UPDATE_DATE,
491 x_created_by => X_LAST_UPDATED_BY,
492 x_last_update_date => X_LAST_UPDATE_DATE,
493 x_last_updated_by => X_LAST_UPDATED_BY,
494 x_last_update_login => X_LAST_UPDATE_LOGIN
495 );
496
497 update IGS_PR_S_CRV_PRG_CAL set
498 STREAM_NUM = NEW_REFERENCES.STREAM_NUM,
499 SHOW_CAUSE_LENGTH = NEW_REFERENCES.SHOW_CAUSE_LENGTH,
500 APPEAL_LENGTH = NEW_REFERENCES.APPEAL_LENGTH,
501 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
502 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
503 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
504 where ROWID = X_ROWID
505 ;
506 if (sql%notfound) then
507 raise no_data_found;
508 end if;
509
510 end UPDATE_ROW;
511
512 procedure ADD_ROW (
513 X_ROWID in out NOCOPY VARCHAR2,
514 X_COURSE_CD in VARCHAR2,
515 X_VERSION_NUMBER in NUMBER,
516 X_PRG_CAL_TYPE in VARCHAR2,
517 X_STREAM_NUM in NUMBER,
518 X_SHOW_CAUSE_LENGTH in NUMBER,
519 X_APPEAL_LENGTH in NUMBER,
520 X_MODE in VARCHAR2 default 'R'
521 ) as
522 cursor c1 is select rowid from IGS_PR_S_CRV_PRG_CAL
523 where COURSE_CD = X_COURSE_CD
524 and VERSION_NUMBER = X_VERSION_NUMBER
525 and PRG_CAL_TYPE = X_PRG_CAL_TYPE
526 ;
527
528 begin
529 open c1;
530 fetch c1 into X_ROWID;
531 if (c1%notfound) then
532 close c1;
533 INSERT_ROW (
534 X_ROWID,
535 X_COURSE_CD,
536 X_VERSION_NUMBER,
537 X_PRG_CAL_TYPE,
538 X_STREAM_NUM,
539 X_SHOW_CAUSE_LENGTH,
540 X_APPEAL_LENGTH,
541 X_MODE);
542 return;
543 end if;
544 close c1;
545 UPDATE_ROW (
546 X_ROWID,
547 X_COURSE_CD,
548 X_VERSION_NUMBER,
549 X_PRG_CAL_TYPE,
550 X_STREAM_NUM,
551 X_SHOW_CAUSE_LENGTH,
552 X_APPEAL_LENGTH,
553 X_MODE);
554 end ADD_ROW;
555
556 procedure DELETE_ROW (
557 X_ROWID in VARCHAR2
558 ) as
559 begin
560
561 Before_DML(
562 p_action=>'DELETE',
563 x_rowid=>X_ROWID
564 );
565
566 delete from IGS_PR_S_CRV_PRG_CAL
567 where ROWID = X_ROWID;
568 if (sql%notfound) then
569 raise no_data_found;
570 end if;
571
572 end DELETE_ROW;
573
574 PROCEDURE Check_Constraints (
575 Column_Name IN VARCHAR2 DEFAULT NULL,
576 Column_Value IN VARCHAR2 DEFAULT NULL
577 ) AS
578 BEGIN
579
580 IF Column_Name is null THEN
581 NULL;
582 ELSIF upper(Column_name) = 'COURSE_CD' THEN
583 new_references.COURSE_CD:= COLUMN_VALUE ;
584
585 ELSIF upper(Column_name) = 'PRG_CAL_TYPE' THEN
586 new_references.PRG_CAL_TYPE:= COLUMN_VALUE ;
587
588 ELSIF upper(Column_name) = 'STREAM_NUM' THEN
589 new_references.STREAM_NUM:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
590
591 ELSIF upper(Column_name) = 'SHOW_CAUSE_LENGTH' THEN
592 new_references.SHOW_CAUSE_LENGTH:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
593
594 ELSIF upper(Column_name) = 'APPEAL_LENGTH' THEN
595 new_references.APPEAL_LENGTH:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
596
597 END IF ;
598
599 IF upper(Column_name) = 'COURSE_CD' OR COLUMN_NAME IS NULL THEN
600 IF new_references.COURSE_CD<> upper(new_references.COURSE_CD) then
601 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
602 IGS_GE_MSG_STACK.ADD;
603 App_Exception.Raise_Exception ;
604 END IF;
605
606 END IF ;
607
608 IF upper(Column_name) = 'PRG_CAL_TYPE' OR COLUMN_NAME IS NULL THEN
609 IF new_references.PRG_CAL_TYPE<> upper(new_references.PRG_CAL_TYPE) then
610 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
611 IGS_GE_MSG_STACK.ADD;
612 App_Exception.Raise_Exception ;
613 END IF;
614
615 END IF ;
616
617 IF upper(Column_name) = 'STREAM_NUM' OR COLUMN_NAME IS NULL THEN
618 IF new_references.STREAM_NUM < 0 or new_references.STREAM_NUM > 99 then
619 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
620 IGS_GE_MSG_STACK.ADD;
621 App_Exception.Raise_Exception ;
622 END IF;
623
624 END IF ;
625
626 IF upper(Column_name) = 'SHOW_CAUSE_LENGTH' OR COLUMN_NAME IS NULL THEN
627 IF new_references.SHOW_CAUSE_LENGTH < 0 or new_references.SHOW_CAUSE_LENGTH > 999 then
628 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
629 IGS_GE_MSG_STACK.ADD;
630 App_Exception.Raise_Exception ;
631 END IF;
632
633 END IF ;
634
635 IF upper(Column_name) = 'APPEAL_LENGTH' OR COLUMN_NAME IS NULL THEN
636 IF new_references.APPEAL_LENGTH < 0 or new_references.APPEAL_LENGTH > 999 then
637 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
638 IGS_GE_MSG_STACK.ADD;
639 App_Exception.Raise_Exception ;
640 END IF;
641
642 END IF ;
643
644
645 END Check_Constraints;
646
647 end IGS_PR_S_CRV_PRG_CAL_PKG;