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