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