[Home] [Help]
PACKAGE BODY: APPS.IGS_PR_S_OU_PRG_CAL_PKG
Source
1 package body IGS_PR_S_OU_PRG_CAL_PKG as
2 /* $Header: IGSQI23B.pls 115.5 2002/12/23 07:32:15 ddey ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PR_S_OU_PRG_CAL%RowType;
6 new_references IGS_PR_S_OU_PRG_CAL%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_org_unit_cd IN VARCHAR2 DEFAULT NULL,
12 x_ou_start_dt IN DATE 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_OU_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.org_unit_cd := x_org_unit_cd;
49 new_references.ou_start_dt := x_ou_start_dt;
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_sopca_br_iu
69 -- BEFORE INSERT OR UPDATE
70 -- ON IGS_PR_S_OU_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_SOPCA.prgp_val_sopca_cause (
94 new_references.org_unit_cd,
95 new_references.ou_start_dt,
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_SOPCA.prgp_val_sopca_apl (
107 new_references.org_unit_cd,
108 new_references.ou_start_dt,
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
121 PROCEDURE Check_Parent_Existance AS
122 BEGIN
123
124 IF (((old_references.prg_cal_type = new_references.prg_cal_type)) OR
125 ((new_references.prg_cal_type IS NULL))) THEN
126 NULL;
127 ELSE
128 IF NOT IGS_CA_TYPE_PKG.Get_PK_For_Validation (
129 new_references.prg_cal_type
130 ) THEN
131 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
132 IGS_GE_MSG_STACK.ADD;
133 App_Exception.Raise_Exception;
134 END IF;
135 END IF;
136
137 IF (((old_references.org_unit_cd = new_references.org_unit_cd) AND
138 (old_references.ou_start_dt = new_references.ou_start_dt)) OR
139 ((new_references.org_unit_cd IS NULL) OR
140 (new_references.ou_start_dt IS NULL))) THEN
141 NULL;
142 ELSE
143 IF NOT IGS_PR_S_OU_PRG_CONF_PKG.Get_PK_For_Validation (
144 new_references.org_unit_cd,
145 new_references.ou_start_dt
146 ) THEN
147 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
148 IGS_GE_MSG_STACK.ADD;
149 App_Exception.Raise_Exception;
150 END IF;
151 END IF;
152
153 END Check_Parent_Existance;
154
155 FUNCTION Get_PK_For_Validation (
156 x_org_unit_cd IN VARCHAR2,
157 x_ou_start_dt IN DATE,
158 x_prg_cal_type IN VARCHAR2
159 ) RETURN BOOLEAN AS
160
161 CURSOR cur_rowid IS
162 SELECT rowid
163 FROM IGS_PR_S_OU_PRG_CAL
164 WHERE org_unit_cd = x_org_unit_cd
165 AND ou_start_dt = x_ou_start_dt
166 AND prg_cal_type = x_prg_cal_type
167 FOR UPDATE NOWAIT;
168
169 lv_rowid cur_rowid%RowType;
170
171 BEGIN
172
173 Open cur_rowid;
174 Fetch cur_rowid INTO lv_rowid;
175 IF (cur_rowid%FOUND) THEN
176 Close Cur_rowid;
177 Return(TRUE);
178 ELSE
179 Close cur_rowid;
180 Return(FALSE);
181 END IF;
182
183 END Get_PK_For_Validation;
184
185 PROCEDURE GET_FK_IGS_CA_TYPE (
186 x_cal_type IN VARCHAR2
187 ) AS
188
189 CURSOR cur_rowid IS
190 SELECT rowid
191 FROM IGS_PR_S_OU_PRG_CAL
192 WHERE prg_cal_type = x_cal_type ;
193
194 lv_rowid cur_rowid%RowType;
195
196 BEGIN
197
198 Open cur_rowid;
199 Fetch cur_rowid INTO lv_rowid;
200 IF (cur_rowid%FOUND) THEN
201 Fnd_Message.Set_Name ('IGS', 'IGS_PR_SOPCA_CAT_FK');
202 IGS_GE_MSG_STACK.ADD;
203 Close cur_rowid;
204 App_Exception.Raise_Exception;
205
206 Return;
207 END IF;
208 Close cur_rowid;
209
210 END GET_FK_IGS_CA_TYPE;
211
212 PROCEDURE GET_FK_IGS_PR_S_OU_PRG_CONF (
213 x_org_unit_cd IN VARCHAR2,
214 x_ou_start_dt IN DATE
215 ) AS
216
217 CURSOR cur_rowid IS
218 SELECT rowid
219 FROM IGS_PR_S_OU_PRG_CAL
220 WHERE org_unit_cd = x_org_unit_cd
221 AND ou_start_dt = x_ou_start_dt ;
222
223 lv_rowid cur_rowid%RowType;
224
225 BEGIN
226
227 Open cur_rowid;
228 Fetch cur_rowid INTO lv_rowid;
229 IF (cur_rowid%FOUND) THEN
230 Fnd_Message.Set_Name ('IGS', 'IGS_PR_SOPCA_SOPC_FK');
231 IGS_GE_MSG_STACK.ADD;
232 Close cur_rowid;
233 App_Exception.Raise_Exception;
234
235 Return;
236 END IF;
237 Close cur_rowid;
238
239 END GET_FK_IGS_PR_S_OU_PRG_CONF;
240
241 PROCEDURE Before_DML (
242 p_action IN VARCHAR2,
243 x_rowid IN VARCHAR2 DEFAULT NULL,
244 x_org_unit_cd IN VARCHAR2 DEFAULT NULL,
245 x_ou_start_dt IN DATE DEFAULT NULL,
246 x_prg_cal_type IN VARCHAR2 DEFAULT NULL,
247 x_stream_num IN NUMBER DEFAULT NULL,
248 x_show_cause_length IN NUMBER DEFAULT NULL,
249 x_appeal_length IN NUMBER DEFAULT NULL,
250 x_creation_date IN DATE DEFAULT NULL,
251 x_created_by IN NUMBER DEFAULT NULL,
252 x_last_update_date IN DATE DEFAULT NULL,
253 x_last_updated_by IN NUMBER DEFAULT NULL,
254 x_last_update_login IN NUMBER DEFAULT NULL
255 ) AS
256 BEGIN
257
258 Set_Column_Values (
259 p_action,
260 x_rowid,
261 x_org_unit_cd,
262 x_ou_start_dt,
263 x_prg_cal_type,
264 x_stream_num,
265 x_show_cause_length,
266 x_appeal_length,
267 x_creation_date,
268 x_created_by,
269 x_last_update_date,
270 x_last_updated_by,
271 x_last_update_login
272 );
273
274 IF (p_action = 'INSERT') THEN
275 -- Call all the procedures related to Before Insert.
276 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
277 IF Get_PK_For_Validation (
278 new_references.org_unit_cd,
279 new_references.ou_start_dt,
280 new_references.prg_cal_type
281 ) THEN
282 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
283 IGS_GE_MSG_STACK.ADD;
284 App_Exception.Raise_Exception;
285 END IF;
286 Check_Constraints;
287 Check_Parent_Existance;
288 ELSIF (p_action = 'UPDATE') THEN
289 -- Call all the procedures related to Before Update.
290 BeforeRowInsertUpdate1 ( p_updating => TRUE );
291 Check_Constraints;
292 Check_Parent_Existance;
293 ELSIF (p_action = 'VALIDATE_INSERT') THEN
294 -- Call all the procedures related to Before Insert.
295 IF Get_PK_For_Validation (
296 new_references.org_unit_cd,
297 new_references.ou_start_dt,
298 new_references.prg_cal_type
299 ) THEN
300 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
301 IGS_GE_MSG_STACK.ADD;
302 App_Exception.Raise_Exception;
303 END IF;
304 Check_Constraints;
305 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
306 -- Call all the procedures related to Before Update.
307 Check_Constraints;
308 END IF;
309
310 END Before_DML;
311
312 procedure INSERT_ROW (
313 X_ROWID in out NOCOPY VARCHAR2,
314 X_ORG_UNIT_CD in VARCHAR2,
315 X_OU_START_DT in DATE,
316 X_PRG_CAL_TYPE in VARCHAR2,
317 X_STREAM_NUM in NUMBER,
318 X_SHOW_CAUSE_LENGTH in NUMBER,
319 X_APPEAL_LENGTH in NUMBER,
320 X_MODE in VARCHAR2 default 'R'
321 ) as
322 cursor C is select ROWID from IGS_PR_S_OU_PRG_CAL
323 where ORG_UNIT_CD = X_ORG_UNIT_CD
324 and OU_START_DT = X_OU_START_DT
325 and PRG_CAL_TYPE = X_PRG_CAL_TYPE;
326 X_LAST_UPDATE_DATE DATE;
327 X_LAST_UPDATED_BY NUMBER;
328 X_LAST_UPDATE_LOGIN NUMBER;
329 begin
330 X_LAST_UPDATE_DATE := SYSDATE;
331 if(X_MODE = 'I') then
332 X_LAST_UPDATED_BY := 1;
333 X_LAST_UPDATE_LOGIN := 0;
334 elsif (X_MODE = 'R') then
335 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
336 if X_LAST_UPDATED_BY is NULL then
337 X_LAST_UPDATED_BY := -1;
338 end if;
339 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
340 if X_LAST_UPDATE_LOGIN is NULL then
341 X_LAST_UPDATE_LOGIN := -1;
342 end if;
343 else
344 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
345 IGS_GE_MSG_STACK.ADD;
346 app_exception.raise_exception;
347 end if;
348
349 Before_DML(
350 p_action => 'INSERT',
351 x_rowid => X_ROWID,
352 x_appeal_length => X_APPEAL_LENGTH,
353 x_org_unit_cd => X_ORG_UNIT_CD,
354 x_ou_start_dt => X_OU_START_DT,
355 x_prg_cal_type => X_PRG_CAL_TYPE,
356 x_show_cause_length => X_SHOW_CAUSE_LENGTH,
357 x_stream_num => X_STREAM_NUM,
358 x_creation_date => X_LAST_UPDATE_DATE,
359 x_created_by => X_LAST_UPDATED_BY,
360 x_last_update_date => X_LAST_UPDATE_DATE,
361 x_last_updated_by => X_LAST_UPDATED_BY,
362 x_last_update_login => X_LAST_UPDATE_LOGIN
363 );
364
365
366 insert into IGS_PR_S_OU_PRG_CAL (
367 ORG_UNIT_CD,
368 OU_START_DT,
369 PRG_CAL_TYPE,
370 STREAM_NUM,
371 SHOW_CAUSE_LENGTH,
372 APPEAL_LENGTH,
373 CREATION_DATE,
374 CREATED_BY,
375 LAST_UPDATE_DATE,
376 LAST_UPDATED_BY,
377 LAST_UPDATE_LOGIN
378 ) values (
379 NEW_REFERENCES.ORG_UNIT_CD,
380 NEW_REFERENCES.OU_START_DT,
381 NEW_REFERENCES.PRG_CAL_TYPE,
382 NEW_REFERENCES.STREAM_NUM,
383 NEW_REFERENCES.SHOW_CAUSE_LENGTH,
384 NEW_REFERENCES.APPEAL_LENGTH,
385 X_LAST_UPDATE_DATE,
386 X_LAST_UPDATED_BY,
387 X_LAST_UPDATE_DATE,
388 X_LAST_UPDATED_BY,
389 X_LAST_UPDATE_LOGIN
390 );
391
392 open c;
393 fetch c into X_ROWID;
394 if (c%notfound) then
395 close c;
396 raise no_data_found;
397 end if;
398 close c;
399
400 end INSERT_ROW;
401
402 procedure LOCK_ROW (
403 X_ROWID in VARCHAR2,
404 X_ORG_UNIT_CD in VARCHAR2,
405 X_OU_START_DT in DATE,
406 X_PRG_CAL_TYPE in VARCHAR2,
407 X_STREAM_NUM in NUMBER,
408 X_SHOW_CAUSE_LENGTH in NUMBER,
409 X_APPEAL_LENGTH in NUMBER
410 ) as
411 cursor c1 is select
412 STREAM_NUM,
413 SHOW_CAUSE_LENGTH,
414 APPEAL_LENGTH
415 from IGS_PR_S_OU_PRG_CAL
416 where ROWID = X_ROWID
417 for update nowait;
418 tlinfo c1%rowtype;
419
420 begin
421 open c1;
422 fetch c1 into tlinfo;
423 if (c1%notfound) then
424 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
425 IGS_GE_MSG_STACK.ADD;
426 close c1;
427 app_exception.raise_exception;
428
429 return;
430 end if;
431 close c1;
432
433 if ( (tlinfo.STREAM_NUM = X_STREAM_NUM)
434 AND ((tlinfo.SHOW_CAUSE_LENGTH = X_SHOW_CAUSE_LENGTH)
435 OR ((tlinfo.SHOW_CAUSE_LENGTH is null)
436 AND (X_SHOW_CAUSE_LENGTH is null)))
437 AND ((tlinfo.APPEAL_LENGTH = X_APPEAL_LENGTH)
438 OR ((tlinfo.APPEAL_LENGTH is null)
439 AND (X_APPEAL_LENGTH is null)))
440 ) then
441 null;
442 else
443 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
444 IGS_GE_MSG_STACK.ADD;
445 app_exception.raise_exception;
446 end if;
447 return;
448 end LOCK_ROW;
449
450 procedure UPDATE_ROW (
451 X_ROWID in VARCHAR2,
452 X_ORG_UNIT_CD in VARCHAR2,
453 X_OU_START_DT in DATE,
454 X_PRG_CAL_TYPE in VARCHAR2,
455 X_STREAM_NUM in NUMBER,
456 X_SHOW_CAUSE_LENGTH in NUMBER,
457 X_APPEAL_LENGTH in NUMBER,
458 X_MODE in VARCHAR2 default 'R'
459 ) as
460 X_LAST_UPDATE_DATE DATE;
461 X_LAST_UPDATED_BY NUMBER;
462 X_LAST_UPDATE_LOGIN NUMBER;
463 begin
464 X_LAST_UPDATE_DATE := SYSDATE;
465 if(X_MODE = 'I') then
466 X_LAST_UPDATED_BY := 1;
467 X_LAST_UPDATE_LOGIN := 0;
468 elsif (X_MODE = 'R') then
469 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
470 if X_LAST_UPDATED_BY is NULL then
471 X_LAST_UPDATED_BY := -1;
472 end if;
473 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
474 if X_LAST_UPDATE_LOGIN is NULL then
475 X_LAST_UPDATE_LOGIN := -1;
476 end if;
477 else
478 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
479 IGS_GE_MSG_STACK.ADD;
480 app_exception.raise_exception;
481 end if;
482
483 Before_DML(
484 p_action => 'UPDATE',
485 x_rowid => X_ROWID,
486 x_appeal_length => X_APPEAL_LENGTH,
487 x_org_unit_cd => X_ORG_UNIT_CD,
488 x_ou_start_dt => X_OU_START_DT,
489 x_prg_cal_type => X_PRG_CAL_TYPE,
490 x_show_cause_length => X_SHOW_CAUSE_LENGTH,
491 x_stream_num => X_STREAM_NUM,
492 x_creation_date => X_LAST_UPDATE_DATE,
493 x_created_by => X_LAST_UPDATED_BY,
494 x_last_update_date => X_LAST_UPDATE_DATE,
495 x_last_updated_by => X_LAST_UPDATED_BY,
496 x_last_update_login => X_LAST_UPDATE_LOGIN
497 );
498
499 update IGS_PR_S_OU_PRG_CAL set
500 STREAM_NUM = NEW_REFERENCES.STREAM_NUM,
501 SHOW_CAUSE_LENGTH = NEW_REFERENCES.SHOW_CAUSE_LENGTH,
502 APPEAL_LENGTH = NEW_REFERENCES.APPEAL_LENGTH,
503 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
504 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
505 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
506 where ROWID = X_ROWID
507 ;
508 if (sql%notfound) then
509 raise no_data_found;
510 end if;
511
512 end UPDATE_ROW;
513
514 procedure ADD_ROW (
515 X_ROWID in out NOCOPY VARCHAR2,
516 X_ORG_UNIT_CD in VARCHAR2,
517 X_OU_START_DT in DATE,
518 X_PRG_CAL_TYPE in VARCHAR2,
519 X_STREAM_NUM in NUMBER,
520 X_SHOW_CAUSE_LENGTH in NUMBER,
521 X_APPEAL_LENGTH in NUMBER,
522 X_MODE in VARCHAR2 default 'R'
523 ) as
524 cursor c1 is select rowid from IGS_PR_S_OU_PRG_CAL
525 where ORG_UNIT_CD = X_ORG_UNIT_CD
526 and OU_START_DT = X_OU_START_DT
527 and PRG_CAL_TYPE = X_PRG_CAL_TYPE
528 ;
529 begin
530 open c1;
531 fetch c1 into X_ROWID;
532 if (c1%notfound) then
533 close c1;
534 INSERT_ROW (
535 X_ROWID,
536 X_ORG_UNIT_CD,
537 X_OU_START_DT,
538 X_PRG_CAL_TYPE,
539 X_STREAM_NUM,
540 X_SHOW_CAUSE_LENGTH,
541 X_APPEAL_LENGTH,
542 X_MODE);
543 return;
544 end if;
545 close c1;
546 UPDATE_ROW (
547 X_ROWID,
548 X_ORG_UNIT_CD,
549 X_OU_START_DT,
550 X_PRG_CAL_TYPE,
551 X_STREAM_NUM,
552 X_SHOW_CAUSE_LENGTH,
553 X_APPEAL_LENGTH,
554 X_MODE);
555 end ADD_ROW;
556
557 procedure DELETE_ROW (
558 X_ROWID in VARCHAR2
559 ) as
560 begin
561
562 Before_DML(
563 p_action=>'DELETE',
564 x_rowid=>X_ROWID
565 );
566
567 delete from IGS_PR_S_OU_PRG_CAL
568 where ROWID = X_ROWID;
569 if (sql%notfound) then
570 raise no_data_found;
571 end if;
572
573 end DELETE_ROW;
574
575 PROCEDURE Check_Constraints (
576 Column_Name IN VARCHAR2 DEFAULT NULL,
577 Column_Value IN VARCHAR2 DEFAULT NULL
578 ) AS
579 BEGIN
580
581 IF column_name is null then
582 NULL;
583 ELSIF upper(Column_Name) = 'SHOW_CAUSE_LENGTH' then
584 new_references.SHOW_CAUSE_LENGTH := IGS_GE_NUMBER.to_num(Column_Value);
585 ELSIF upper(Column_Name) = 'APPEAL_LENGTH' then
586 new_references.APPEAL_LENGTH := IGS_GE_NUMBER.to_num(Column_Value);
587 ELSIF upper(Column_Name) = 'ORG_UNIT_CD' then
588 new_references.ORG_UNIT_CD := Column_Value;
589 ELSIF upper(Column_Name) = 'PRG_CAL_TYPE' then
590 new_references.PRG_CAL_TYPE := Column_Value;
591 END IF;
592
593 IF upper(column_name) = 'SHOW_CAUSE_LENGTH' OR
594 column_name is NULL THEN
595 IF TO_NUMBER(new_references.SHOW_CAUSE_LENGTH) < 0 OR
596 TO_NUMBER(new_references.SHOW_CAUSE_LENGTH) > 999 THEN
597 Fnd_Message.Set_Name('IGS','IGS_INDVALID_VALUE');
598 IGS_GE_MSG_STACK.ADD;
599 App_Exception.Raise_Exception;
600 END IF;
601 END IF;
602
603 IF upper(column_name) = 'APPEAL_LENGTH' OR
604 column_name is NULL THEN
605 IF TO_NUMBER(new_references.APPEAL_LENGTH) < 0 OR
606 TO_NUMBER(new_references.APPEAL_LENGTH) > 999 THEN
607 Fnd_Message.Set_Name('IGS','IGS_INVALID_VALUE');
608 IGS_GE_MSG_STACK.ADD;
609 App_Exception.Raise_Exception;
610 END IF;
611 END IF;
612
613 IF upper(column_name) = 'PRG_CAL_TYPE' OR
614 column_name is NULL THEN
615 IF new_references.PRG_CAL_TYPE <> UPPER(new_references.PRG_CAL_TYPE) THEN
616 Fnd_Message.Set_Name('IGS','IGS_INVALID_VALUE');
617 IGS_GE_MSG_STACK.ADD;
618 App_Exception.Raise_Exception;
619 END IF;
620 END IF;
621
622 END Check_Constraints;
623
624 end IGS_PR_S_OU_PRG_CAL_PKG;