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