1 package body IGS_PR_S_CRV_PRG_CON_PKG AS
2 /* $Header: IGSQI22B.pls 115.5 2002/11/29 03:19:40 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PR_S_CRV_PRG_CON%RowType;
6 new_references IGS_PR_S_CRV_PRG_CON%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_apply_start_dt_alias IN VARCHAR2 DEFAULT NULL,
14 x_apply_end_dt_alias IN VARCHAR2 DEFAULT NULL,
15 x_end_benefit_dt_alias IN VARCHAR2 DEFAULT NULL,
16 x_end_penalty_dt_alias IN VARCHAR2 DEFAULT NULL,
17 x_show_cause_cutoff_dt_alias IN VARCHAR2 DEFAULT NULL,
18 x_appeal_cutoff_dt_alias IN VARCHAR2 DEFAULT NULL,
19 x_show_cause_ind IN VARCHAR2 DEFAULT NULL,
20 x_apply_before_show_ind IN VARCHAR2 DEFAULT NULL,
21 x_appeal_ind IN VARCHAR2 DEFAULT NULL,
22 x_apply_before_appeal_ind IN VARCHAR2 DEFAULT NULL,
23 x_count_sus_in_time_ind IN VARCHAR2 DEFAULT NULL,
24 x_count_exc_in_time_ind IN VARCHAR2 DEFAULT NULL,
25 x_calculate_wam_ind IN VARCHAR2 DEFAULT NULL,
26 x_calculate_gpa_ind IN VARCHAR2 DEFAULT NULL,
27 x_outcome_check_type IN VARCHAR2 DEFAULT NULL,
28 x_creation_date IN DATE DEFAULT NULL,
29 x_created_by IN NUMBER DEFAULT NULL,
30 x_last_update_date IN DATE DEFAULT NULL,
31 x_last_updated_by IN NUMBER DEFAULT NULL,
32 x_last_update_login IN NUMBER DEFAULT NULL
33 ) as
34
35 CURSOR cur_old_ref_values IS
36 SELECT *
37 FROM IGS_PR_S_CRV_PRG_CON
38 WHERE rowid = x_rowid;
39
40 BEGIN
41
42 l_rowid := x_rowid;
43
44 -- Code for setting the Old and New Reference Values.
45 -- Populate Old Values.
46 Open cur_old_ref_values;
47 Fetch cur_old_ref_values INTO old_references;
48 IF (cur_old_ref_values%NOTFOUND) AND (p_action not in ('INSERT','VALIDATE_INSERT')) THEN
49 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
50 IGS_GE_MSG_STACK.ADD;
51 Close cur_old_ref_values;
52 App_Exception.Raise_Exception;
53
54 Return;
55 END IF;
56 Close cur_old_ref_values;
57
58 -- Populate New Values.
59 new_references.course_cd := x_course_cd;
60 new_references.version_number := x_version_number;
61 new_references.apply_start_dt_alias := x_apply_start_dt_alias;
62 new_references.apply_end_dt_alias := x_apply_end_dt_alias;
63 new_references.end_benefit_dt_alias := x_end_benefit_dt_alias;
64 new_references.end_penalty_dt_alias := x_end_penalty_dt_alias;
65 new_references.show_cause_cutoff_dt_alias := x_show_cause_cutoff_dt_alias;
66 new_references.appeal_cutoff_dt_alias := x_appeal_cutoff_dt_alias;
67 new_references.show_cause_ind := x_show_cause_ind;
68 new_references.apply_before_show_ind := x_apply_before_show_ind;
69 new_references.appeal_ind := x_appeal_ind;
70 new_references.apply_before_appeal_ind := x_apply_before_appeal_ind;
71 new_references.count_sus_in_time_ind := x_count_sus_in_time_ind;
72 new_references.count_exc_in_time_ind := x_count_exc_in_time_ind;
73 new_references.calculate_wam_ind := x_calculate_wam_ind;
74 new_references.calculate_gpa_ind := x_calculate_gpa_ind;
75 new_references.outcome_check_type := x_outcome_check_type;
76 IF (p_action = 'UPDATE') THEN
77 new_references.creation_date := old_references.creation_date;
78 new_references.created_by := old_references.created_by;
79 ELSE
80 new_references.creation_date := x_creation_date;
81 new_references.created_by := x_created_by;
82 END IF;
83 new_references.last_update_date := x_last_update_date;
84 new_references.last_updated_by := x_last_updated_by;
85 new_references.last_update_login := x_last_update_login;
86
87 END Set_Column_Values;
88
89 -- Trigger description :-
90 -- "OSS_TST".trg_scpc_br_iu
91 -- BEFORE INSERT OR UPDATE
92 -- ON IGS_PR_S_CRV_PRG_CON
93 -- FOR EACH ROW
94
95 PROCEDURE BeforeRowInsertUpdate1(
96 p_inserting IN BOOLEAN DEFAULT FALSE,
97 p_updating IN BOOLEAN DEFAULT FALSE,
98 p_deleting IN BOOLEAN DEFAULT FALSE
99 ) as
100 v_message_name varchar2(30);
101 BEGIN
102 -- Validate the org IGS_PS_UNIT is active
103 IF p_inserting THEN
104 IF IGS_PR_VAL_PRA.crsp_val_crv_active (
105 new_references.course_cd,
106 new_references.version_number,
107 v_message_name) = FALSE THEN
108 Fnd_Message.Set_Name('IGS',v_message_name);
109 IGS_GE_MSG_STACK.ADD;
110 App_Exception.Raise_Exception;
111 END IF;
112 END IF;
113 -- Validate the application start date alias
114 IF p_inserting OR (p_updating AND
115 new_references.apply_start_dt_alias <> old_references.apply_start_dt_alias) THEN
116 IF IGS_PR_VAL_SCPC.prgp_val_da_closed (
117 new_references.apply_start_dt_alias,
118 v_message_name) = FALSE THEN
119 Fnd_Message.Set_Name('IGS',v_message_name);
120 IGS_GE_MSG_STACK.ADD;
121 App_Exception.Raise_Exception;
122 END IF;
123 END IF;
124 -- Validate the application end date alias
125 IF p_inserting OR (p_updating AND
126 new_references.apply_end_dt_alias <> old_references.apply_end_dt_alias) THEN
127 IF IGS_PR_VAL_SCPC.prgp_val_da_closed (
128 new_references.apply_end_dt_alias,
129 v_message_name) = FALSE THEN
130 Fnd_Message.Set_Name('IGS',v_message_name);
131 IGS_GE_MSG_STACK.ADD;
132 App_Exception.Raise_Exception;
133 END IF;
134 END IF;
135 -- Validate the end benefit date alias
136 IF p_inserting OR (p_updating AND
137 new_references.end_benefit_dt_alias <> old_references.end_benefit_dt_alias) THEN
138 IF IGS_PR_VAL_SCPC.prgp_val_da_closed (
139 new_references.end_benefit_dt_alias,
140 v_message_name) = FALSE THEN
141 Fnd_Message.Set_Name('IGS',v_message_name);
142 IGS_GE_MSG_STACK.ADD;
143 App_Exception.Raise_Exception;
144 END IF;
145 END IF;
146 -- Validate the end penalty date alias
147 IF p_inserting OR (p_updating AND
148 new_references.end_penalty_dt_alias <> old_references.end_penalty_dt_alias) THEN
149 IF IGS_PR_VAL_SCPC.prgp_val_da_closed (
150 new_references.end_penalty_dt_alias,
151 v_message_name) = FALSE THEN
152 Fnd_Message.Set_Name('IGS',v_message_name);
153 IGS_GE_MSG_STACK.ADD;
154 App_Exception.Raise_Exception;
155 END IF;
156 END IF;
157 -- Validate the show cause indicator
158 IF p_inserting OR (p_updating AND
159 new_references.show_cause_ind <> old_references.show_cause_ind) THEN
160 IF igs_pr_val_sopc.prgp_val_cause_ind (
161 new_references.show_cause_ind,
162 v_message_name) = FALSE THEN
163 Fnd_Message.Set_Name('IGS',v_message_name);
164 IGS_GE_MSG_STACK.ADD;
165 App_Exception.Raise_Exception;
166 END IF;
167 END IF;
168 -- Validate the appeal indicator
169 IF p_inserting OR (p_updating AND
170 new_references.appeal_ind <> old_references.appeal_ind) THEN
171 IF igs_pr_val_sopc.prgp_val_cause_ind (
172 new_references.appeal_ind,
173 v_message_name) = FALSE THEN
174 Fnd_Message.Set_Name('IGS',v_message_name);
175 IGS_GE_MSG_STACK.ADD;
176 App_Exception.Raise_Exception;
177 END IF;
178 END IF;
179 -- Validate the show cause cut off date alias can only be set when the
180 -- show cause ind indicator is set to Y
181 IF p_inserting OR p_updating THEN
182 IF IGS_PR_VAL_SCPC.prgp_val_cause_da (
183 new_references.show_cause_ind,
184 new_references.show_cause_cutoff_dt_alias,
185 v_message_name) = FALSE THEN
186 Fnd_Message.Set_Name('IGS',v_message_name);
187 IGS_GE_MSG_STACK.ADD;
188 App_Exception.Raise_Exception;
189 END IF;
190 END IF;
191 -- Validate the appeal cut off date alias can only be set when the
192 -- appeal ind indicator is set to Y
193 IF p_inserting OR p_updating THEN
194 IF IGS_PR_VAL_SCPC.prgp_val_appeal_da (
195 new_references.appeal_ind,
196 new_references.appeal_cutoff_dt_alias,
197 v_message_name) = FALSE THEN
198 Fnd_Message.Set_Name('IGS',v_message_name);
199 IGS_GE_MSG_STACK.ADD;
200 App_Exception.Raise_Exception;
201 END IF;
202 END IF;
203 -- Validate the show cause ind indicator cannot be set to N when there are
204 -- related progression calendar records with show cause length set.
205 IF p_inserting OR p_updating THEN
206 IF IGS_PR_VAL_SCPC.prgp_val_scpc_cause (
207 new_references.course_cd,
208 new_references.version_number,
209 new_references.show_cause_ind,
210 v_message_name) = FALSE THEN
211 Fnd_Message.Set_Name('IGS',v_message_name);
212 IGS_GE_MSG_STACK.ADD;
213 App_Exception.Raise_Exception;
214 END IF;
215 END IF;
216 -- Validate the appeal ind indicator cannot be set to N when there are
217 -- related progression calendar records with appeallength set.
218 IF p_inserting OR p_updating THEN
219 IF IGS_PR_VAL_SCPC.prgp_val_scpc_apl (
220 new_references.course_cd,
221 new_references.version_number,
222 new_references.appeal_ind,
223 v_message_name) = FALSE THEN
224 Fnd_Message.Set_Name('IGS',v_message_name);
225 IGS_GE_MSG_STACK.ADD;
226 App_Exception.Raise_Exception;
227 END IF;
228 END IF;
229 -- Validate the show cause cut off date alias
230 IF p_inserting OR (p_updating AND
231 NVL(new_references.show_cause_cutoff_dt_alias, 'NULL') <>
232 NVL(old_references.show_cause_cutoff_dt_alias, 'NULL')) THEN
233 IF IGS_PR_VAL_SCPC.prgp_val_da_closed (
234 new_references.show_cause_cutoff_dt_alias,
235 v_message_name) = FALSE THEN
236 Fnd_Message.Set_Name('IGS',v_message_name);
237 IGS_GE_MSG_STACK.ADD;
238 App_Exception.Raise_Exception;
239 END IF;
240 END IF;
241 -- Validate the appeal cut off date alias
242 IF p_inserting OR (p_updating AND
243 NVL(new_references.appeal_cutoff_dt_alias, 'NULL') <>
244 NVL(old_references.appeal_cutoff_dt_alias, 'NULL')) THEN
245 IF IGS_PR_VAL_SCPC.prgp_val_da_closed (
246 new_references.appeal_cutoff_dt_alias,
247 v_message_name) = FALSE THEN
248 Fnd_Message.Set_Name('IGS',v_message_name);
249 IGS_GE_MSG_STACK.ADD;
250 App_Exception.Raise_Exception;
251 END IF;
252 END IF;
253
254
255 END BeforeRowInsertUpdate1;
256
257
258 PROCEDURE Check_Parent_Existance AS
259 BEGIN
260
261 IF (((old_references.appeal_cutoff_dt_alias = new_references.appeal_cutoff_dt_alias)) OR
262 ((new_references.appeal_cutoff_dt_alias IS NULL))) THEN
263 NULL;
264 ELSE
265 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
266 new_references.appeal_cutoff_dt_alias
267 ) THEN
268 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
269 IGS_GE_MSG_STACK.ADD;
270 App_Exception.Raise_Exception;
271 END IF;
272 END IF;
273
274 IF (((old_references.end_benefit_dt_alias = new_references.end_benefit_dt_alias)) OR
275 ((new_references.end_benefit_dt_alias IS NULL))) THEN
276 NULL;
277 ELSE
281 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
278 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
279 new_references.end_benefit_dt_alias
280 ) THEN
282 IGS_GE_MSG_STACK.ADD;
283 App_Exception.Raise_Exception;
284 END IF;
285 END IF;
286
287 IF (((old_references.show_cause_cutoff_dt_alias = new_references.show_cause_cutoff_dt_alias)) OR
288 ((new_references.show_cause_cutoff_dt_alias IS NULL))) THEN
289 NULL;
290 ELSE
291 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
292 new_references.show_cause_cutoff_dt_alias
293 ) THEN
294 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
295 IGS_GE_MSG_STACK.ADD;
296 App_Exception.Raise_Exception;
297 END IF;
298 END IF;
299
300 IF (((old_references.apply_end_dt_alias = new_references.apply_end_dt_alias)) OR
301 ((new_references.apply_end_dt_alias IS NULL))) THEN
302 NULL;
303 ELSE
304 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
305 new_references.apply_end_dt_alias
306 ) THEN
307 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
308 IGS_GE_MSG_STACK.ADD;
309 App_Exception.Raise_Exception;
310 END IF;
311 END IF;
312
313 IF (((old_references.end_penalty_dt_alias = new_references.end_penalty_dt_alias)) OR
314 ((new_references.end_penalty_dt_alias IS NULL))) THEN
315 NULL;
316 ELSE
317 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
318 new_references.end_penalty_dt_alias
319 ) THEN
320 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
321 IGS_GE_MSG_STACK.ADD;
322 App_Exception.Raise_Exception;
323 END IF;
324 END IF;
325
326 IF (((old_references.apply_start_dt_alias = new_references.apply_start_dt_alias)) OR
327 ((new_references.apply_start_dt_alias IS NULL))) THEN
328 NULL;
329 ELSE
330 IF NOT IGS_CA_DA_PKG.Get_PK_For_Validation (
331 new_references.apply_start_dt_alias
332 ) THEN
333 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
334 IGS_GE_MSG_STACK.ADD;
335 App_Exception.Raise_Exception;
336 END IF;
337 END IF;
338
339 IF (((old_references.course_cd = new_references.course_cd) AND
340 (old_references.version_number = new_references.version_number)) OR
341 ((new_references.course_cd IS NULL) OR
342 (new_references.version_number IS NULL))) THEN
343 NULL;
344 ELSE
345 IF NOT IGS_PS_VER_PKG.Get_PK_For_Validation (
346 new_references.course_cd,
347 new_references.version_number
348 ) THEN
349 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
350 IGS_GE_MSG_STACK.ADD;
351 App_Exception.Raise_Exception;
352 END IF;
353 END IF;
354
355 END Check_Parent_Existance;
356
357 PROCEDURE Check_Child_Existance as
358 BEGIN
359
360 IGS_PR_S_CRV_PRG_CAL_PKG.GET_FK_IGS_PR_S_CRV_PRG_CON(
361 old_references.course_cd,
362 old_references.version_number
363 );
364
365 END Check_Child_Existance;
366
367 FUNCTION Get_PK_For_Validation (
368 x_course_cd IN VARCHAR2,
369 x_version_number IN NUMBER
370 ) RETURN BOOLEAN AS
371
372 CURSOR cur_rowid IS
373 SELECT rowid
374 FROM IGS_PR_S_CRV_PRG_CON
375 WHERE course_cd = x_course_cd
376 AND version_number = x_version_number
377 FOR UPDATE NOWAIT;
378
379 lv_rowid cur_rowid%RowType;
380
381 BEGIN
382
383 Open cur_rowid;
384 Fetch cur_rowid INTO lv_rowid;
385 IF (cur_rowid%FOUND) THEN
386 Close Cur_rowid;
387 Return(TRUE);
388 ELSE
389 Close cur_rowid;
390 Return(FALSE);
391 END IF;
392
393 END Get_PK_For_Validation;
394
395 PROCEDURE GET_FK_IGS_CA_DA (
396 x_dt_alias IN varchar2
397 ) as
398
402 WHERE appeal_cutoff_dt_alias = x_dt_alias
399 CURSOR cur_rowid IS
400 SELECT rowid
401 FROM IGS_PR_S_CRV_PRG_CON
403 OR end_benefit_dt_alias = x_dt_alias
404 OR show_cause_cutoff_dt_alias = x_dt_alias
405 OR apply_end_dt_alias = x_dt_alias
406 OR end_penalty_dt_alias = x_dt_alias
407 OR apply_start_dt_alias = x_dt_alias;
408
409 lv_rowid cur_rowid%RowType;
410
411 BEGIN
412
413 Open cur_rowid;
414 Fetch cur_rowid INTO lv_rowid;
415 IF (cur_rowid%FOUND) THEN
416 Fnd_Message.Set_Name ('IGS', 'IGS_PR_SCPC_DA_APPEAL_FK');
417 IGS_GE_MSG_STACK.ADD;
418 Close cur_rowid;
419 App_Exception.Raise_Exception;
420
421 Return;
422 END IF;
423 Close cur_rowid;
424
425 END GET_FK_IGS_CA_DA;
426
427 PROCEDURE GET_FK_IGS_PS_VER (
428 x_course_cd IN VARCHAR2,
429 x_version_number IN NUMBER
430 ) as
431
432 CURSOR cur_rowid IS
433 SELECT rowid
434 FROM IGS_PR_S_CRV_PRG_CON
435 WHERE course_cd = x_course_cd
436 AND version_number = x_version_number ;
437
438 lv_rowid cur_rowid%RowType;
439
440 BEGIN
441
442 Open cur_rowid;
443 Fetch cur_rowid INTO lv_rowid;
444 IF (cur_rowid%FOUND) THEN
445 Fnd_Message.Set_Name ('IGS', 'IGS_PR_SCPS_CRV_FK');
446 IGS_GE_MSG_STACK.ADD;
447 Close cur_rowid;
448 App_Exception.Raise_Exception;
449
450 Return;
451 END IF;
452 Close cur_rowid;
453
454 END GET_FK_IGS_PS_VER;
455
456 PROCEDURE Before_DML (
457 p_action IN VARCHAR2,
458 x_rowid IN VARCHAR2 DEFAULT NULL,
459 x_course_cd IN VARCHAR2 DEFAULT NULL,
460 x_version_number IN NUMBER DEFAULT NULL,
461 x_apply_start_dt_alias IN VARCHAR2 DEFAULT NULL,
462 x_apply_end_dt_alias IN VARCHAR2 DEFAULT NULL,
463 x_end_benefit_dt_alias IN VARCHAR2 DEFAULT NULL,
464 x_end_penalty_dt_alias IN VARCHAR2 DEFAULT NULL,
465 x_show_cause_cutoff_dt_alias IN VARCHAR2 DEFAULT NULL,
466 x_appeal_cutoff_dt_alias IN VARCHAR2 DEFAULT NULL,
467 x_show_cause_ind IN VARCHAR2 DEFAULT NULL,
468 x_apply_before_show_ind IN VARCHAR2 DEFAULT NULL,
469 x_appeal_ind IN VARCHAR2 DEFAULT NULL,
470 x_apply_before_appeal_ind IN VARCHAR2 DEFAULT NULL,
471 x_count_sus_in_time_ind IN VARCHAR2 DEFAULT NULL,
472 x_count_exc_in_time_ind IN VARCHAR2 DEFAULT NULL,
473 x_calculate_wam_ind IN VARCHAR2 DEFAULT NULL,
474 x_calculate_gpa_ind IN VARCHAR2 DEFAULT NULL,
475 x_outcome_check_type IN VARCHAR2 DEFAULT NULL,
476 x_creation_date IN DATE DEFAULT NULL,
477 x_created_by IN NUMBER DEFAULT NULL,
478 x_last_update_date IN DATE DEFAULT NULL,
479 x_last_updated_by IN NUMBER DEFAULT NULL,
480 x_last_update_login IN NUMBER DEFAULT NULL
481 ) as
482 BEGIN
483
484 Set_Column_Values (
485 p_action,
486 x_rowid,
487 x_course_cd,
488 x_version_number,
489 x_apply_start_dt_alias,
490 x_apply_end_dt_alias,
491 x_end_benefit_dt_alias,
492 x_end_penalty_dt_alias,
493 x_show_cause_cutoff_dt_alias,
494 x_appeal_cutoff_dt_alias,
495 x_show_cause_ind,
496 x_apply_before_show_ind,
497 x_appeal_ind,
498 x_apply_before_appeal_ind,
499 x_count_sus_in_time_ind,
500 x_count_exc_in_time_ind,
501 x_calculate_wam_ind,
502 x_calculate_gpa_ind,
503 x_outcome_check_type,
504 x_creation_date,
505 x_created_by,
506 x_last_update_date,
507 x_last_updated_by,
508 x_last_update_login
509 );
510
511 IF (p_action = 'INSERT') THEN
512 -- Call all the procedures related to Before Insert.
513 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
514 IF Get_PK_For_Validation (
515 new_references.course_cd,
516 new_references.version_number) THEN
517 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
518 IGS_GE_MSG_STACK.ADD;
519 App_Exception.Raise_Exception;
520 END IF;
521 Check_Constraints;
522 Check_Parent_Existance;
523 ELSIF (p_action = 'UPDATE') THEN
524 -- Call all the procedures related to Before Update.
525 BeforeRowInsertUpdate1 ( p_updating => TRUE );
526 Check_Constraints;
527 Check_Parent_Existance;
528 ELSIF (p_action = 'DELETE') THEN
529 -- Call all the procedures related to Before Delete.
530 Check_Child_Existance;
531 ELSIF (p_action = 'VALIDATE_INSERT') THEN
532 -- Call all the procedures related to Before Insert.
533 IF Get_PK_For_Validation (
534 new_references.course_cd,
535 new_references.version_number) THEN
539 END IF;
536 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
537 IGS_GE_MSG_STACK.ADD;
538 App_Exception.Raise_Exception;
540 Check_Constraints;
541 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
542 -- Call all the procedures related to Before Update.
543 Check_Constraints;
544 ELSIF (p_action = 'VALIDATE_DELETE') THEN
545 -- Call all the procedures related to Before Delete.
546 Check_Child_Existance;
547 END IF;
548
549 END Before_DML;
550
551 procedure INSERT_ROW (
552 X_ROWID in out NOCOPY VARCHAR2,
553 X_COURSE_CD in VARCHAR2,
554 X_VERSION_NUMBER in NUMBER,
555 X_APPLY_START_DT_ALIAS in VARCHAR2,
556 X_APPLY_END_DT_ALIAS in VARCHAR2,
557 X_END_BENEFIT_DT_ALIAS in VARCHAR2,
558 X_END_PENALTY_DT_ALIAS in VARCHAR2,
559 X_SHOW_CAUSE_CUTOFF_DT_ALIAS in VARCHAR2,
560 X_APPEAL_CUTOFF_DT_ALIAS in VARCHAR2,
561 X_SHOW_CAUSE_IND in VARCHAR2,
562 X_APPLY_BEFORE_SHOW_IND in VARCHAR2,
563 X_APPEAL_IND in VARCHAR2,
564 X_APPLY_BEFORE_APPEAL_IND in VARCHAR2,
565 X_COUNT_SUS_IN_TIME_IND in VARCHAR2,
566 X_COUNT_EXC_IN_TIME_IND in VARCHAR2,
567 X_CALCULATE_WAM_IND in VARCHAR2,
568 X_CALCULATE_GPA_IND in VARCHAR2,
569 X_OUTCOME_CHECK_TYPE in VARCHAR2,
570 X_MODE in VARCHAR2 default 'R'
571 ) as
572 cursor C is select ROWID from IGS_PR_S_CRV_PRG_CON
573 where COURSE_CD = X_COURSE_CD
574 and VERSION_NUMBER = X_VERSION_NUMBER;
575 X_LAST_UPDATE_DATE DATE;
576 X_LAST_UPDATED_BY NUMBER;
577 X_LAST_UPDATE_LOGIN NUMBER;
578 begin
579 X_LAST_UPDATE_DATE := SYSDATE;
580 if(X_MODE = 'I') then
581 X_LAST_UPDATED_BY := 1;
582 X_LAST_UPDATE_LOGIN := 0;
583 elsif (X_MODE = 'R') then
584 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
585 if X_LAST_UPDATED_BY is NULL then
586 X_LAST_UPDATED_BY := -1;
587 end if;
588 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
589 if X_LAST_UPDATE_LOGIN is NULL then
590 X_LAST_UPDATE_LOGIN := -1;
591 end if;
592 else
593 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
594 IGS_GE_MSG_STACK.ADD;
595 app_exception.raise_exception;
596 end if;
597
598 Before_DML(
599 p_action => 'INSERT',
600 x_rowid => X_ROWID,
601 x_appeal_cutoff_dt_alias => X_APPEAL_CUTOFF_DT_ALIAS,
602 x_appeal_ind => nvl( X_APPEAL_IND, 'Y'),
603 x_apply_before_appeal_ind => nvl( X_APPLY_BEFORE_APPEAL_IND, 'Y'),
604 x_apply_before_show_ind => nvl( X_APPLY_BEFORE_SHOW_IND, 'N'),
605 x_apply_end_dt_alias => X_APPLY_END_DT_ALIAS,
606 x_apply_start_dt_alias => X_APPLY_START_DT_ALIAS,
607 x_calculate_gpa_ind => nvl( X_CALCULATE_GPA_IND, 'N'),
608 x_calculate_wam_ind => nvl( X_CALCULATE_WAM_IND, 'N'),
609 x_count_exc_in_time_ind => nvl( X_COUNT_EXC_IN_TIME_IND, 'N'),
610 x_count_sus_in_time_ind => nvl( X_COUNT_SUS_IN_TIME_IND, 'N'),
611 x_course_cd => X_COURSE_CD,
612 x_end_benefit_dt_alias => X_END_BENEFIT_DT_ALIAS,
613 x_end_penalty_dt_alias => X_END_PENALTY_DT_ALIAS,
614 x_show_cause_cutoff_dt_alias => X_SHOW_CAUSE_CUTOFF_DT_ALIAS,
615 x_show_cause_ind => nvl( X_SHOW_CAUSE_IND, 'Y'),
616 x_version_number => X_VERSION_NUMBER,
617 x_outcome_check_type => X_OUTCOME_CHECK_TYPE,
618 x_creation_date => X_LAST_UPDATE_DATE,
619 x_created_by => X_LAST_UPDATED_BY,
620 x_last_update_date => X_LAST_UPDATE_DATE,
621 x_last_updated_by => X_LAST_UPDATED_BY,
622 x_last_update_login => X_LAST_UPDATE_LOGIN
623 );
624
625 insert into IGS_PR_S_CRV_PRG_CON (
626 COURSE_CD,
627 VERSION_NUMBER,
628 APPLY_START_DT_ALIAS,
629 APPLY_END_DT_ALIAS,
630 END_BENEFIT_DT_ALIAS,
631 END_PENALTY_DT_ALIAS,
632 SHOW_CAUSE_CUTOFF_DT_ALIAS,
633 APPEAL_CUTOFF_DT_ALIAS,
634 SHOW_CAUSE_IND,
635 APPLY_BEFORE_SHOW_IND,
636 APPEAL_IND,
637 APPLY_BEFORE_APPEAL_IND,
638 COUNT_SUS_IN_TIME_IND,
639 COUNT_EXC_IN_TIME_IND,
640 CALCULATE_WAM_IND,
641 CALCULATE_GPA_IND,
642 OUTCOME_CHECK_TYPE,
643 CREATION_DATE,
644 CREATED_BY,
645 LAST_UPDATE_DATE,
646 LAST_UPDATED_BY,
647 LAST_UPDATE_LOGIN
648 ) values (
649 NEW_REFERENCES.COURSE_CD,
650 NEW_REFERENCES.VERSION_NUMBER,
651 NEW_REFERENCES.APPLY_START_DT_ALIAS,
652 NEW_REFERENCES.APPLY_END_DT_ALIAS,
653 NEW_REFERENCES.END_BENEFIT_DT_ALIAS,
654 NEW_REFERENCES.END_PENALTY_DT_ALIAS,
655 NEW_REFERENCES.SHOW_CAUSE_CUTOFF_DT_ALIAS,
656 NEW_REFERENCES.APPEAL_CUTOFF_DT_ALIAS,
657 NEW_REFERENCES.SHOW_CAUSE_IND,
658 NEW_REFERENCES.APPLY_BEFORE_SHOW_IND,
659 NEW_REFERENCES.APPEAL_IND,
660 NEW_REFERENCES.APPLY_BEFORE_APPEAL_IND,
661 NEW_REFERENCES.COUNT_SUS_IN_TIME_IND,
662 NEW_REFERENCES.COUNT_EXC_IN_TIME_IND,
663 NEW_REFERENCES.CALCULATE_WAM_IND,
664 NEW_REFERENCES.CALCULATE_GPA_IND,
665 NEW_REFERENCES.OUTCOME_CHECK_TYPE,
666 X_LAST_UPDATE_DATE,
667 X_LAST_UPDATED_BY,
668 X_LAST_UPDATE_DATE,
669 X_LAST_UPDATED_BY,
670 X_LAST_UPDATE_LOGIN
671 );
672
673 open c;
674 fetch c into X_ROWID;
675 if (c%notfound) then
679 close c;
676 close c;
677 raise no_data_found;
678 end if;
680
681 end INSERT_ROW;
682
683 procedure LOCK_ROW (
684 X_ROWID in VARCHAR2,
685 X_COURSE_CD in VARCHAR2,
686 X_VERSION_NUMBER in NUMBER,
687 X_APPLY_START_DT_ALIAS in VARCHAR2,
688 X_APPLY_END_DT_ALIAS in VARCHAR2,
689 X_END_BENEFIT_DT_ALIAS in VARCHAR2,
690 X_END_PENALTY_DT_ALIAS in VARCHAR2,
691 X_SHOW_CAUSE_CUTOFF_DT_ALIAS in VARCHAR2,
692 X_APPEAL_CUTOFF_DT_ALIAS in VARCHAR2,
693 X_SHOW_CAUSE_IND in VARCHAR2,
694 X_APPLY_BEFORE_SHOW_IND in VARCHAR2,
695 X_APPEAL_IND in VARCHAR2,
696 X_APPLY_BEFORE_APPEAL_IND in VARCHAR2,
697 X_COUNT_SUS_IN_TIME_IND in VARCHAR2,
698 X_COUNT_EXC_IN_TIME_IND in VARCHAR2,
699 X_CALCULATE_WAM_IND in VARCHAR2,
700 X_CALCULATE_GPA_IND in VARCHAR2,
701 X_OUTCOME_CHECK_TYPE in VARCHAR2
702 ) as
703 cursor c1 is select
704 APPLY_START_DT_ALIAS,
705 APPLY_END_DT_ALIAS,
706 END_BENEFIT_DT_ALIAS,
707 END_PENALTY_DT_ALIAS,
708 SHOW_CAUSE_CUTOFF_DT_ALIAS,
709 APPEAL_CUTOFF_DT_ALIAS,
710 SHOW_CAUSE_IND,
711 APPLY_BEFORE_SHOW_IND,
712 APPEAL_IND,
713 APPLY_BEFORE_APPEAL_IND,
714 COUNT_SUS_IN_TIME_IND,
715 COUNT_EXC_IN_TIME_IND,
716 CALCULATE_WAM_IND,
717 CALCULATE_GPA_IND,
718 OUTCOME_CHECK_TYPE
719 from IGS_PR_S_CRV_PRG_CON
720 where ROWID = X_ROWID
721 for update nowait;
722 tlinfo c1%rowtype;
723
724 begin
725 open c1;
726 fetch c1 into tlinfo;
727 if (c1%notfound) then
728 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
729 IGS_GE_MSG_STACK.ADD;
730 close c1;
731 app_exception.raise_exception;
732
733 return;
734 end if;
735 close c1;
736
737 if ( (tlinfo.APPLY_START_DT_ALIAS = X_APPLY_START_DT_ALIAS)
738 AND (tlinfo.APPLY_END_DT_ALIAS = X_APPLY_END_DT_ALIAS)
739 AND (tlinfo.END_BENEFIT_DT_ALIAS = X_END_BENEFIT_DT_ALIAS)
740 AND (tlinfo.END_PENALTY_DT_ALIAS = X_END_PENALTY_DT_ALIAS)
741 AND ((tlinfo.SHOW_CAUSE_CUTOFF_DT_ALIAS = X_SHOW_CAUSE_CUTOFF_DT_ALIAS)
742 OR ((tlinfo.SHOW_CAUSE_CUTOFF_DT_ALIAS is null)
743 AND (X_SHOW_CAUSE_CUTOFF_DT_ALIAS is null)))
744 AND ((tlinfo.APPEAL_CUTOFF_DT_ALIAS = X_APPEAL_CUTOFF_DT_ALIAS)
745 OR ((tlinfo.APPEAL_CUTOFF_DT_ALIAS is null)
746 AND (X_APPEAL_CUTOFF_DT_ALIAS is null)))
747 AND (tlinfo.SHOW_CAUSE_IND = X_SHOW_CAUSE_IND)
748 AND (tlinfo.APPLY_BEFORE_SHOW_IND = X_APPLY_BEFORE_SHOW_IND)
749 AND (tlinfo.APPEAL_IND = X_APPEAL_IND)
750 AND (tlinfo.APPLY_BEFORE_APPEAL_IND = X_APPLY_BEFORE_APPEAL_IND)
751 AND (tlinfo.COUNT_SUS_IN_TIME_IND = X_COUNT_SUS_IN_TIME_IND)
752 AND (tlinfo.COUNT_EXC_IN_TIME_IND = X_COUNT_EXC_IN_TIME_IND)
753 AND (tlinfo.CALCULATE_WAM_IND = X_CALCULATE_WAM_IND)
754 AND (tlinfo.CALCULATE_GPA_IND = X_CALCULATE_GPA_IND)
755 AND ((tlinfo.OUTCOME_CHECK_TYPE = X_OUTCOME_CHECK_TYPE)
756 OR ((tlinfo.OUTCOME_CHECK_TYPE is null)
757 AND (X_OUTCOME_CHECK_TYPE is null)))
758 ) then
759 null;
760 else
761 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
762 IGS_GE_MSG_STACK.ADD;
763 app_exception.raise_exception;
764 end if;
765 return;
766 end LOCK_ROW;
767
768 procedure UPDATE_ROW (
769 X_ROWID in VARCHAR2,
770 X_COURSE_CD in VARCHAR2,
771 X_VERSION_NUMBER in NUMBER,
772 X_APPLY_START_DT_ALIAS in VARCHAR2,
773 X_APPLY_END_DT_ALIAS in VARCHAR2,
774 X_END_BENEFIT_DT_ALIAS in VARCHAR2,
775 X_END_PENALTY_DT_ALIAS in VARCHAR2,
776 X_SHOW_CAUSE_CUTOFF_DT_ALIAS in VARCHAR2,
777 X_APPEAL_CUTOFF_DT_ALIAS in VARCHAR2,
778 X_SHOW_CAUSE_IND in VARCHAR2,
779 X_APPLY_BEFORE_SHOW_IND in VARCHAR2,
780 X_APPEAL_IND in VARCHAR2,
781 X_APPLY_BEFORE_APPEAL_IND in VARCHAR2,
782 X_COUNT_SUS_IN_TIME_IND in VARCHAR2,
783 X_COUNT_EXC_IN_TIME_IND in VARCHAR2,
784 X_CALCULATE_WAM_IND in VARCHAR2,
785 X_CALCULATE_GPA_IND in VARCHAR2,
786 X_OUTCOME_CHECK_TYPE in VARCHAR2,
787 X_MODE in VARCHAR2 default 'R'
788 ) as
789 X_LAST_UPDATE_DATE DATE;
790 X_LAST_UPDATED_BY NUMBER;
791 X_LAST_UPDATE_LOGIN NUMBER;
792 begin
793 X_LAST_UPDATE_DATE := SYSDATE;
794 if(X_MODE = 'I') then
795 X_LAST_UPDATED_BY := 1;
796 X_LAST_UPDATE_LOGIN := 0;
797 elsif (X_MODE = 'R') then
798 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
799 if X_LAST_UPDATED_BY is NULL then
800 X_LAST_UPDATED_BY := -1;
801 end if;
802 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
803 if X_LAST_UPDATE_LOGIN is NULL then
804 X_LAST_UPDATE_LOGIN := -1;
805 end if;
806 else
807 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
808 IGS_GE_MSG_STACK.ADD;
809 app_exception.raise_exception;
810 end if;
811
812 Before_DML(
813 p_action => 'UPDATE',
814 x_rowid => X_ROWID,
815 x_appeal_cutoff_dt_alias => X_APPEAL_CUTOFF_DT_ALIAS,
816 x_appeal_ind => X_APPEAL_IND,
820 x_apply_start_dt_alias => X_APPLY_START_DT_ALIAS,
817 x_apply_before_appeal_ind => X_APPLY_BEFORE_APPEAL_IND,
818 x_apply_before_show_ind => X_APPLY_BEFORE_SHOW_IND,
819 x_apply_end_dt_alias => X_APPLY_END_DT_ALIAS,
821 x_calculate_gpa_ind => X_CALCULATE_GPA_IND,
822 x_calculate_wam_ind => X_CALCULATE_WAM_IND,
823 x_count_exc_in_time_ind => X_COUNT_EXC_IN_TIME_IND,
824 x_count_sus_in_time_ind => X_COUNT_SUS_IN_TIME_IND,
825 x_course_cd => X_COURSE_CD,
826 x_end_benefit_dt_alias => X_END_BENEFIT_DT_ALIAS,
827 x_end_penalty_dt_alias => X_END_PENALTY_DT_ALIAS,
828 x_show_cause_cutoff_dt_alias => X_SHOW_CAUSE_CUTOFF_DT_ALIAS,
829 x_show_cause_ind => X_SHOW_CAUSE_IND,
830 x_version_number => X_VERSION_NUMBER,
831 x_outcome_check_type => X_OUTCOME_CHECK_TYPE,
832 x_creation_date => X_LAST_UPDATE_DATE,
833 x_created_by => X_LAST_UPDATED_BY,
834 x_last_update_date => X_LAST_UPDATE_DATE,
835 x_last_updated_by => X_LAST_UPDATED_BY,
836 x_last_update_login => X_LAST_UPDATE_LOGIN
837 );
838
839 update IGS_PR_S_CRV_PRG_CON set
840 APPLY_START_DT_ALIAS = NEW_REFERENCES.APPLY_START_DT_ALIAS,
841 APPLY_END_DT_ALIAS = NEW_REFERENCES.APPLY_END_DT_ALIAS,
842 END_BENEFIT_DT_ALIAS = NEW_REFERENCES.END_BENEFIT_DT_ALIAS,
843 END_PENALTY_DT_ALIAS = NEW_REFERENCES.END_PENALTY_DT_ALIAS,
844 SHOW_CAUSE_CUTOFF_DT_ALIAS = NEW_REFERENCES.SHOW_CAUSE_CUTOFF_DT_ALIAS,
845 APPEAL_CUTOFF_DT_ALIAS = NEW_REFERENCES.APPEAL_CUTOFF_DT_ALIAS,
846 SHOW_CAUSE_IND = NEW_REFERENCES.SHOW_CAUSE_IND,
847 APPLY_BEFORE_SHOW_IND = NEW_REFERENCES.APPLY_BEFORE_SHOW_IND,
848 APPEAL_IND = NEW_REFERENCES.APPEAL_IND,
849 APPLY_BEFORE_APPEAL_IND = NEW_REFERENCES.APPLY_BEFORE_APPEAL_IND,
850 COUNT_SUS_IN_TIME_IND = NEW_REFERENCES.COUNT_SUS_IN_TIME_IND,
851 COUNT_EXC_IN_TIME_IND = NEW_REFERENCES.COUNT_EXC_IN_TIME_IND,
852 CALCULATE_WAM_IND = NEW_REFERENCES.CALCULATE_WAM_IND,
853 CALCULATE_GPA_IND = NEW_REFERENCES.CALCULATE_GPA_IND,
854 OUTCOME_CHECK_TYPE = NEW_REFERENCES.OUTCOME_CHECK_TYPE,
855 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
856 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
857 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
858 where ROWID = X_ROWID
859 ;
860 if (sql%notfound) then
861 raise no_data_found;
862 end if;
863
864 end UPDATE_ROW;
865
866 procedure ADD_ROW (
867 X_ROWID in out NOCOPY VARCHAR2,
868 X_COURSE_CD in VARCHAR2,
869 X_VERSION_NUMBER in NUMBER,
870 X_APPLY_START_DT_ALIAS in VARCHAR2,
871 X_APPLY_END_DT_ALIAS in VARCHAR2,
872 X_END_BENEFIT_DT_ALIAS in VARCHAR2,
873 X_END_PENALTY_DT_ALIAS in VARCHAR2,
874 X_SHOW_CAUSE_CUTOFF_DT_ALIAS in VARCHAR2,
875 X_APPEAL_CUTOFF_DT_ALIAS in VARCHAR2,
876 X_SHOW_CAUSE_IND in VARCHAR2,
877 X_APPLY_BEFORE_SHOW_IND in VARCHAR2,
878 X_APPEAL_IND in VARCHAR2,
879 X_APPLY_BEFORE_APPEAL_IND in VARCHAR2,
880 X_COUNT_SUS_IN_TIME_IND in VARCHAR2,
881 X_COUNT_EXC_IN_TIME_IND in VARCHAR2,
882 X_CALCULATE_WAM_IND in VARCHAR2,
883 X_CALCULATE_GPA_IND in VARCHAR2,
884 X_OUTCOME_CHECK_TYPE in VARCHAR2,
885 X_MODE in VARCHAR2 default 'R'
886 ) as
887 cursor c1 is select rowid from IGS_PR_S_CRV_PRG_CON
888 where COURSE_CD = X_COURSE_CD
889 and VERSION_NUMBER = X_VERSION_NUMBER
890 ;
891 begin
892 open c1;
893 fetch c1 into X_ROWID;
894 if (c1%notfound) then
895 close c1;
896 INSERT_ROW (
897 X_ROWID,
898 X_COURSE_CD,
899 X_VERSION_NUMBER,
900 X_APPLY_START_DT_ALIAS,
901 X_APPLY_END_DT_ALIAS,
902 X_END_BENEFIT_DT_ALIAS,
903 X_END_PENALTY_DT_ALIAS,
904 X_SHOW_CAUSE_CUTOFF_DT_ALIAS,
905 X_APPEAL_CUTOFF_DT_ALIAS,
906 X_SHOW_CAUSE_IND,
907 X_APPLY_BEFORE_SHOW_IND,
908 X_APPEAL_IND,
909 X_APPLY_BEFORE_APPEAL_IND,
910 X_COUNT_SUS_IN_TIME_IND,
911 X_COUNT_EXC_IN_TIME_IND,
912 X_CALCULATE_WAM_IND,
913 X_CALCULATE_GPA_IND,
914 X_OUTCOME_CHECK_TYPE,
915 X_MODE);
916 return;
917 end if;
918 close c1;
919 UPDATE_ROW (
920 X_ROWID,
921 X_COURSE_CD,
922 X_VERSION_NUMBER,
923 X_APPLY_START_DT_ALIAS,
924 X_APPLY_END_DT_ALIAS,
925 X_END_BENEFIT_DT_ALIAS,
926 X_END_PENALTY_DT_ALIAS,
927 X_SHOW_CAUSE_CUTOFF_DT_ALIAS,
928 X_APPEAL_CUTOFF_DT_ALIAS,
929 X_SHOW_CAUSE_IND,
930 X_APPLY_BEFORE_SHOW_IND,
931 X_APPEAL_IND,
932 X_APPLY_BEFORE_APPEAL_IND,
933 X_COUNT_SUS_IN_TIME_IND,
934 X_COUNT_EXC_IN_TIME_IND,
935 X_CALCULATE_WAM_IND,
936 X_CALCULATE_GPA_IND,
937 X_OUTCOME_CHECK_TYPE,
938 X_MODE);
939 end ADD_ROW;
940
941 procedure DELETE_ROW (
942 X_ROWID in VARCHAR2
943 ) as
944 begin
945
946 Before_DML(
947 p_action=>'DELETE',
948 x_rowid=>X_ROWID
949 );
950
951
952 delete from IGS_PR_S_CRV_PRG_CON
953 where ROWID = X_ROWID;
954 if (sql%notfound) then
955 raise no_data_found;
956 end if;
957
958 end DELETE_ROW;
959
960 PROCEDURE Check_Constraints (
961 Column_Name IN VARCHAR2 DEFAULT NULL,
965 IF Column_Name is null THEN
962 Column_Value IN VARCHAR2 DEFAULT NULL
963 ) AS
964 BEGIN
966 NULL;
967 ELSIF upper(Column_name) = 'APPLY_BEFORE_APPEAL_IND' THEN
968 new_references.APPLY_BEFORE_APPEAL_IND:= COLUMN_VALUE ;
969
970 ELSIF upper(Column_name) = 'COUNT_SUS_IN_TIME_IND' THEN
971 new_references.COUNT_SUS_IN_TIME_IND:= COLUMN_VALUE ;
972
973 ELSIF upper(Column_name) = 'COUNT_EXC_IN_TIME_IND' THEN
974 new_references.COUNT_EXC_IN_TIME_IND:= COLUMN_VALUE ;
975
976 ELSIF upper(Column_name) = 'CALCULATE_WAM_IND' THEN
977 new_references.CALCULATE_WAM_IND := COLUMN_VALUE ;
978
979 ELSIF upper(Column_name) = 'CALCULATE_GPA_IND' THEN
980 new_references.CALCULATE_GPA_IND := COLUMN_VALUE ;
981
982 ELSIF upper(Column_name) = 'APPEAL_CUTOFF_DT_ALIAS' THEN
983 new_references.APPEAL_CUTOFF_DT_ALIAS:= COLUMN_VALUE ;
984
985 ELSIF upper(Column_name) = 'APPEAL_IND' THEN
986 new_references.APPEAL_IND:= COLUMN_VALUE ;
987
988 ELSIF upper(Column_name) = 'APPLY_BEFORE_APPEAL_IND' THEN
989 new_references.APPLY_BEFORE_APPEAL_IND:= COLUMN_VALUE ;
990
991 ELSIF upper(Column_name) = 'APPLY_BEFORE_SHOW_IND' THEN
992 new_references.APPLY_BEFORE_SHOW_IND:= COLUMN_VALUE ;
993
994 ELSIF upper(Column_name) = 'APPLY_END_DT_ALIAS' THEN
995 new_references.APPLY_END_DT_ALIAS:= COLUMN_VALUE ;
996
997 ELSIF upper(Column_name) = 'APPLY_START_DT_ALIAS' THEN
998 new_references.APPLY_START_DT_ALIAS:= COLUMN_VALUE ;
999
1000 ELSIF upper(Column_name) = 'COURSE_CD' THEN
1001 new_references.COURSE_CD:= COLUMN_VALUE ;
1002
1003 ELSIF upper(Column_name) = 'END_BENEFIT_DT_ALIAS' THEN
1004 new_references.END_BENEFIT_DT_ALIAS:= COLUMN_VALUE ;
1005
1006 ELSIF upper(Column_name) = 'END_PENALTY_DT_ALIAS' THEN
1007 new_references.END_PENALTY_DT_ALIAS:= COLUMN_VALUE ;
1008
1009 ELSIF upper(Column_name) = 'SHOW_CAUSE_CUTOFF_DT_ALIAS' THEN
1010 new_references.SHOW_CAUSE_CUTOFF_DT_ALIAS:= COLUMN_VALUE ;
1011
1012 ELSIF upper(Column_name) = 'SHOW_CAUSE_IND' THEN
1013 new_references.SHOW_CAUSE_IND:= COLUMN_VALUE ;
1014
1015 ELSIF upper(Column_name) = 'OUTCOME_CHECK_TYPE' THEN
1016 new_references.OUTCOME_CHECK_TYPE:= COLUMN_VALUE ;
1017
1018 END IF ;
1019
1020 IF upper(Column_name) = 'APPLY_BEFORE_APPEAL_IND' OR COLUMN_NAME IS NULL THEN
1021 IF new_references.APPLY_BEFORE_APPEAL_IND not in ('Y','N') then
1022 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1023 IGS_GE_MSG_STACK.ADD;
1024 App_Exception.Raise_Exception ;
1025 END IF;
1026
1027 END IF ;
1028
1029 IF upper(Column_name) = 'COUNT_SUS_IN_TIME_IND' OR COLUMN_NAME IS NULL THEN
1030 IF new_references.COUNT_SUS_IN_TIME_IND<> upper(new_references.COUNT_SUS_IN_TIME_IND) then
1031 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1032 IGS_GE_MSG_STACK.ADD;
1033 App_Exception.Raise_Exception ;
1034 END IF;
1035
1036 IF new_references.COUNT_SUS_IN_TIME_IND not in ('Y','N') then
1037 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1038 IGS_GE_MSG_STACK.ADD;
1039 App_Exception.Raise_Exception ;
1040 END IF;
1041
1042 END IF ;
1043
1044 IF upper(Column_name) = 'COUNT_EXC_IN_TIME_IND' OR COLUMN_NAME IS NULL THEN
1045 IF new_references.COUNT_EXC_IN_TIME_IND<> upper(new_references.COUNT_EXC_IN_TIME_IND) then
1046 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1047 IGS_GE_MSG_STACK.ADD;
1048 App_Exception.Raise_Exception ;
1049 END IF;
1050
1051 IF new_references.COUNT_EXC_IN_TIME_IND not in ('Y','N') then
1052 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1053 IGS_GE_MSG_STACK.ADD;
1054 App_Exception.Raise_Exception ;
1055 END IF;
1056
1057 END IF ;
1058
1059 IF upper(Column_name) = 'CALCULATE_WAM_IND ' OR COLUMN_NAME IS NULL THEN
1060 IF new_references.CALCULATE_WAM_IND <> upper(new_references.CALCULATE_WAM_IND) then
1061 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1062 IGS_GE_MSG_STACK.ADD;
1063 App_Exception.Raise_Exception ;
1064 END IF;
1065
1066 IF new_references.CALCULATE_WAM_IND not in ('Y','N') then
1067 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1068 IGS_GE_MSG_STACK.ADD;
1069 App_Exception.Raise_Exception ;
1070 END IF;
1071
1072 END IF ;
1073
1074 IF upper(Column_name) = 'CALCULATE_GPA_IND' OR COLUMN_NAME IS NULL THEN
1075 IF new_references.CALCULATE_GPA_IND <> upper(new_references.CALCULATE_GPA_IND) then
1076 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1077 IGS_GE_MSG_STACK.ADD;
1078 App_Exception.Raise_Exception ;
1079 END IF;
1080
1081 IF new_references.CALCULATE_GPA_IND not in ('Y','N') then
1082 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1083 IGS_GE_MSG_STACK.ADD;
1084 App_Exception.Raise_Exception ;
1085 END IF;
1086
1087 END IF ;
1088
1089 IF upper(Column_name) = 'APPEAL_CUTOFF_DT_ALIAS' OR COLUMN_NAME IS NULL THEN
1090 IF new_references.APPEAL_CUTOFF_DT_ALIAS<> upper(new_references.APPEAL_CUTOFF_DT_ALIAS) then
1091 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1092 IGS_GE_MSG_STACK.ADD;
1093 App_Exception.Raise_Exception ;
1094 END IF;
1095
1096 END IF ;
1097
1098 IF upper(Column_name) = 'APPEAL_IND' OR COLUMN_NAME IS NULL THEN
1099 IF new_references.APPEAL_IND<> upper(new_references.APPEAL_IND) then
1100 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1101 IGS_GE_MSG_STACK.ADD;
1102 App_Exception.Raise_Exception ;
1103 END IF;
1104
1105 IF new_references.APPEAL_IND not in ('Y','N') then
1106 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1107 IGS_GE_MSG_STACK.ADD;
1108 App_Exception.Raise_Exception ;
1109 END IF;
1110
1111 END IF ;
1112
1113 IF upper(Column_name) = 'APPLY_BEFORE_APPEAL_IND' OR COLUMN_NAME IS NULL THEN
1114 IF new_references.APPLY_BEFORE_APPEAL_IND<> upper(new_references.APPLY_BEFORE_APPEAL_IND) then
1115 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1116 IGS_GE_MSG_STACK.ADD;
1117 App_Exception.Raise_Exception ;
1118 END IF;
1119
1120 END IF ;
1121
1122 IF upper(Column_name) = 'APPLY_BEFORE_SHOW_IND' OR COLUMN_NAME IS NULL THEN
1123 IF new_references.APPLY_BEFORE_SHOW_IND<> upper(new_references.APPLY_BEFORE_SHOW_IND) then
1124 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1125 IGS_GE_MSG_STACK.ADD;
1126 App_Exception.Raise_Exception ;
1127 END IF;
1128
1129 IF new_references.APPLY_BEFORE_SHOW_IND not in ('Y','N') then
1130 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1131 IGS_GE_MSG_STACK.ADD;
1132 App_Exception.Raise_Exception ;
1133 END IF;
1134
1135 END IF ;
1136
1137 IF upper(Column_name) = 'APPLY_END_DT_ALIAS' OR COLUMN_NAME IS NULL THEN
1138 IF new_references.APPLY_END_DT_ALIAS<> upper(new_references.APPLY_END_DT_ALIAS) then
1139 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1140 IGS_GE_MSG_STACK.ADD;
1141 App_Exception.Raise_Exception ;
1142 END IF;
1143
1144 END IF ;
1145
1146 IF upper(Column_name) = 'APPLY_START_DT_ALIAS' OR COLUMN_NAME IS NULL THEN
1147 IF new_references.APPLY_START_DT_ALIAS<> upper(new_references.APPLY_START_DT_ALIAS) then
1148 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1149 IGS_GE_MSG_STACK.ADD;
1150 App_Exception.Raise_Exception ;
1151 END IF;
1152
1153 END IF ;
1154
1155 IF upper(Column_name) = 'COURSE_CD' OR COLUMN_NAME IS NULL THEN
1156 IF new_references.COURSE_CD<> upper(new_references.COURSE_CD) then
1157 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1158 IGS_GE_MSG_STACK.ADD;
1159 App_Exception.Raise_Exception ;
1160 END IF;
1161
1162 END IF ;
1163
1164 IF upper(Column_name) = 'END_BENEFIT_DT_ALIAS' OR COLUMN_NAME IS NULL THEN
1165 IF new_references.END_BENEFIT_DT_ALIAS<> upper(new_references.END_BENEFIT_DT_ALIAS) then
1166 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1167 IGS_GE_MSG_STACK.ADD;
1168 App_Exception.Raise_Exception ;
1169 END IF;
1170
1171 END IF ;
1172
1173 IF upper(Column_name) = 'END_PENALTY_DT_ALIAS' OR COLUMN_NAME IS NULL THEN
1174 IF new_references.END_PENALTY_DT_ALIAS<> upper(new_references.END_PENALTY_DT_ALIAS) then
1175 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1176 IGS_GE_MSG_STACK.ADD;
1177 App_Exception.Raise_Exception ;
1178 END IF;
1179
1180 END IF ;
1181
1182 IF upper(Column_name) = 'SHOW_CAUSE_CUTOFF_DT_ALIAS' OR COLUMN_NAME IS NULL THEN
1183 IF new_references.SHOW_CAUSE_CUTOFF_DT_ALIAS<> upper(new_references.SHOW_CAUSE_CUTOFF_DT_ALIAS) then
1184 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1185 IGS_GE_MSG_STACK.ADD;
1186 App_Exception.Raise_Exception ;
1187 END IF;
1188
1189 END IF ;
1190
1191 IF upper(Column_name) = 'SHOW_CAUSE_IND' OR COLUMN_NAME IS NULL THEN
1192 IF new_references.SHOW_CAUSE_IND<> upper(new_references.SHOW_CAUSE_IND) then
1193 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1194 IGS_GE_MSG_STACK.ADD;
1195 App_Exception.Raise_Exception ;
1196 END IF;
1197
1198 IF new_references.SHOW_CAUSE_IND not in ('Y','N') then
1199 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1200 IGS_GE_MSG_STACK.ADD;
1201 App_Exception.Raise_Exception ;
1202 END IF;
1203
1204 END IF ;
1205
1206 IF upper(Column_name) = 'OUTCOME_CHECK_TYPE' OR COLUMN_NAME IS NULL THEN
1207 IF new_references.OUTCOME_CHECK_TYPE<> upper(new_references.OUTCOME_CHECK_TYPE) then
1208 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
1209 IGS_GE_MSG_STACK.ADD;
1210 App_Exception.Raise_Exception ;
1211 END IF;
1212
1213 END IF ;
1214
1215
1216 END Check_Constraints;
1217
1218 end IGS_PR_S_CRV_PRG_CON_PKG;
[Home] [Help]