1 PACKAGE BODY IGS_PE_ACAD_HONORS_PKG AS
2 /* $Header: IGSNI99B.pls 120.2 2005/10/17 02:22:26 appldev ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_pe_acad_honors%RowType;
5 new_references igs_pe_acad_honors%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 ,
10 x_acad_honor_id IN NUMBER ,
11 x_person_id IN NUMBER,
12 x_comments IN VARCHAR2,
13 x_honor_date IN DATE ,
14 x_creation_date IN DATE ,
15 x_created_by IN NUMBER ,
16 x_last_update_date IN DATE ,
17 x_last_updated_by IN NUMBER,
18 x_last_update_login IN NUMBER ,
19 x_acad_honor_type IN VARCHAR2
20 ) AS
21
22 /*************************************************************
23 Created By :samaresh
24 Date Created By :15-May-2000
25 Purpose :
26 Know limitations, enhancements or remarks
27 Change History
28 Who When What
29
30 (reverse chronological order - newest change first)
31 kamohan 1/21/02 Removed references of nominated_course_Cd
32 and sequence_number
33 ***************************************************************/
34
35 CURSOR cur_old_ref_values IS
36 SELECT *
37 FROM igs_pe_acad_honors
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 Close cur_old_ref_values;
50 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
51 IGS_GE_MSG_STACK.ADD;
52 App_Exception.Raise_Exception;
53 Return;
54 END IF;
55 Close cur_old_ref_values;
56
57 -- Populate New Values.
58 new_references.acad_honor_id := x_acad_honor_id;
59 new_references.person_id := x_person_id;
60 new_references.comments := x_comments;
61 new_references.honor_date := x_honor_date; --code added in the next line by gautam
62 new_references.acad_honor_type:=x_acad_honor_type;
63 IF (p_action = 'UPDATE') THEN
64 new_references.creation_date := old_references.creation_date;
65 new_references.created_by := old_references.created_by;
66 ELSE
67 new_references.creation_date := x_creation_date;
68 new_references.created_by := x_created_by;
69 END IF;
70 new_references.last_update_date := x_last_update_date;
71 new_references.last_updated_by := x_last_updated_by;
72 new_references.last_update_login := x_last_update_login;
73
74 END Set_Column_Values;
75
76 PROCEDURE Check_Constraints (
77 Column_Name IN VARCHAR2 ,
78 Column_Value IN VARCHAR2 ) AS
79 /*************************************************************
80 Created By :samaresh
81 Date Created By :15-May-2000
82 Purpose :
83 Know limitations, enhancements or remarks
84 Change History
85 Who When What
86
87 (reverse chronological order - newest change first)
88 ***************************************************************/
89 BEGIN
90 IF column_name IS NULL THEN
91 NULL;
92 END IF;
93 END Check_Constraints;
94
95 FUNCTION Get_UK_For_Validation (
96 x_acad_honor_type IN VARCHAR2,
97 x_honor_date IN DATE,
98 x_person_id IN NUMBER
99 ) RETURN BOOLEAN AS
100 /*************************************************************
101 Created By :ssomasun.in
102 Date Created By :29-May-2000
103 Purpose :
104 Know limitations, enhancements or remarks
105 Change History
106 Who When What
107
108 (reverse chronological order - newest change first)
109 kamohan 1/21/02 removed the reference to Nominated_course_cd
110 and sequence_number
111 ***************************************************************/
112
113 CURSOR cur_rowid IS
114 SELECT rowid
115 FROM igs_pe_acad_honors
116 WHERE acad_honor_type = x_acad_honor_type
117 AND nvl(honor_date,sysdate) = nvl(x_honor_date,sysdate)
118 AND person_id = x_person_id
119 AND ((l_rowid is null) or (rowid <> l_rowid));
120 lv_rowid cur_rowid%RowType;
121
122 BEGIN
123
124 Open cur_rowid;
125 Fetch cur_rowid INTO lv_rowid;
126 IF (cur_rowid%FOUND) THEN
127 Close cur_rowid;
128 return (true);
129 ELSE
130 close cur_rowid;
131 return(false);
132 END IF;
133 END Get_UK_For_Validation ;
134
135 PROCEDURE Check_Uniqueness AS
136 /*************************************************************
137 Created By :ssomasun.in
138 Date Created By :29-May-2000
139 Purpose :
140 Know limitations, enhancements or remarks
141 Change History
142 Who When What
143
144 (reverse chronological order - newest change first)
145 ***************************************************************/
146 begin
147 IF Get_Uk_For_Validation (
148 new_references.acad_honor_type, --changed acad_honor_type_id to acad_honor_TYPE
149 new_references.honor_date,
150 new_references.person_id
151 ) THEN
152 Fnd_Message.Set_Name ('IGS', 'IGS_PE_ACAD_HNRS_DUP_EXISTS');
153 IGS_GE_MSG_STACK.ADD;
154 app_exception.raise_exception;
155 END IF;
156 END Check_Uniqueness ;
157
158 PROCEDURE Check_Parent_Existance AS
159 /*************************************************************
160 Created By :ssomasun.in
161 Date Created By :29-May-2000
162 Purpose :
163 Know limitations, enhancements or remarks
164 Change History
165 Who When What
166
167 (reverse chronological order - newest change first)
168 kamohan 1/21/02 removed the ref to Nominated_course_cd
169 and sequence_number
170 ***************************************************************/
171
172 BEGIN
173
174 IF (((old_references.acad_honor_type = new_references.acad_honor_type)) OR
175 ((new_references.acad_honor_type IS NULL))) THEN
176 NULL;
177 ELSIF NOT Igs_lookups_view_Pkg.Get_PK_For_Validation (
178 'PE_ACAD_HONORS', new_references.acad_honor_type
179 ) THEN
180 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
181 IGS_GE_MSG_STACK.ADD;
182 App_Exception.Raise_Exception;
183 END IF;
184
185
186
187 END Check_Parent_Existance;
188
189 FUNCTION Get_PK_For_Validation (
190 x_acad_honor_id IN NUMBER
191 ) RETURN BOOLEAN AS
192
193 /*************************************************************
194 Created By :samaresh
195 Date Created By :15-May-2000
196 Purpose :
197 Know limitations, enhancements or remarks
198 Change History
199 Who When What
200
201 (reverse chronological order - newest change first)
202 ***************************************************************/
203
204 CURSOR cur_rowid IS
205 SELECT rowid
206 FROM igs_pe_acad_honors
207 WHERE acad_honor_id = x_acad_honor_id
208 FOR UPDATE NOWAIT;
209
210 lv_rowid cur_rowid%RowType;
211
212 BEGIN
213
214 Open cur_rowid;
215 Fetch cur_rowid INTO lv_rowid;
216 IF (cur_rowid%FOUND) THEN
217 Close cur_rowid;
218 Return(TRUE);
219 ELSE
220 Close cur_rowid;
221 Return(FALSE);
222 END IF;
223 END Get_PK_For_Validation;
224
225
226 PROCEDURE Before_DML (
227 p_action IN VARCHAR2,
228 x_rowid IN VARCHAR2 ,
229 x_acad_honor_id IN NUMBER,
230 x_person_id IN NUMBER ,
231 x_comments IN VARCHAR2 ,
232 x_honor_date IN DATE ,
233 x_creation_date IN DATE ,
234 x_created_by IN NUMBER ,
235 x_last_update_date IN DATE,
236 x_last_updated_by IN NUMBER ,
237 x_last_update_login IN NUMBER ,
238 x_acad_honor_type IN VARCHAR2
239 ) AS
240 /*************************************************************
241 Created By :
242 Date Created By :
243 Purpose :
244 Know limitations, enhancements or remarks
245 Change History
246 Who When What
247
248 (reverse chronological order - newest change first)
249 ***************************************************************/
250
251 BEGIN
252
253 Set_Column_Values (
254 p_action,
255 x_rowid,
256 x_acad_honor_id,
257 x_person_id,
258 x_comments,
259 x_honor_date,
260 x_creation_date,
261 x_created_by,
262 x_last_update_date,
263 x_last_updated_by,
264 x_last_update_login,
265 x_acad_honor_type
266 );
267
268 IF (p_action = 'INSERT') THEN
269 -- Call all the procedures related to Before Insert.
270 IF Get_Pk_For_Validation(
271 new_references.acad_honor_id) THEN
272 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
273 IGS_GE_MSG_STACK.ADD;
274 App_Exception.Raise_Exception;
275 END IF;
276 Check_Uniqueness;
277 Check_Constraints;
278 Check_Parent_Existance;
279 ELSIF (p_action = 'UPDATE') THEN
280 -- Call all the procedures related to Before Update.
281 Check_Uniqueness;
282 Check_Constraints;
283 Check_Parent_Existance;
284 ELSIF (p_action = 'DELETE') THEN
285 -- Call all the procedures related to Before Delete.
286 Null;
287 ELSIF (p_action = 'VALIDATE_INSERT') THEN
288 -- Call all the procedures related to Before Insert.
289 IF Get_PK_For_Validation (
290 new_references.acad_honor_id) THEN
291 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
292 IGS_GE_MSG_STACK.ADD;
293 App_Exception.Raise_Exception;
294 END IF;
295 Check_Uniqueness;
296 Check_Constraints;
297 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
298 Check_Uniqueness;
299 Check_Constraints;
300 ELSIF (p_action = 'VALIDATE_DELETE') THEN
301 Null;
302 END IF;
303
304 END Before_DML;
305
306 PROCEDURE After_DML (
307 p_action IN VARCHAR2,
308 x_rowid IN VARCHAR2
309 ) IS
310 /*************************************************************
311 Created By :samaresh
312 Date Created By :15-May-2000
313 Purpose :
314 Know limitations, enhancements or remarks
315 Change History
316 Who When What
317
318 (reverse chronological order - newest change first)
319 ***************************************************************/
320
321 BEGIN
322
323 l_rowid := x_rowid;
324
325 IF (p_action = 'INSERT') THEN
326 -- Call all the procedures related to After Insert.
327 Null;
328 ELSIF (p_action = 'UPDATE') THEN
329 -- Call all the procedures related to After Update.
330 Null;
331 ELSIF (p_action = 'DELETE') THEN
332 -- Call all the procedures related to After Delete.
333 Null;
334 END IF;
335
336 l_rowid:=NULL;
337 END After_DML;
338
339 procedure INSERT_ROW (
340 X_ROWID in out NOCOPY VARCHAR2,
341 x_ACAD_HONOR_ID IN OUT NOCOPY NUMBER,
342 x_PERSON_ID IN NUMBER,
343 x_COMMENTS IN VARCHAR2,
344 x_HONOR_DATE IN DATE,
345 X_MODE in VARCHAR2 ,
346 X_ACAD_HONOR_TYPE IN VARCHAR2
347 ) AS
348 /*************************************************************
349 Created By :samaresh
350 Date Created By :15-May-2000
351 Purpose :
352 Know limitations, enhancements or remarks
353 Change History
354 Who When What
355
356 (reverse chronological order - newest change first)
357 ***************************************************************/
358
359 cursor C is select ROWID from igs_pe_acad_honors
360 where ACAD_HONOR_ID= X_ACAD_HONOR_ID;
361
362 X_LAST_UPDATE_DATE DATE ;
363 X_LAST_UPDATED_BY NUMBER ;
364 X_LAST_UPDATE_LOGIN NUMBER ;
365
366 X_REQUEST_ID NUMBER;
367 X_PROGRAM_ID NUMBER;
368 X_PROGRAM_APPLICATION_ID NUMBER;
369 X_PROGRAM_UPDATE_DATE DATE;
370 begin
371 X_LAST_UPDATE_DATE := SYSDATE;
372 if(X_MODE = 'I') then
373 X_LAST_UPDATED_BY := 1;
374 X_LAST_UPDATE_LOGIN := 0;
375 elsif (X_MODE IN ('R', 'S')) then
376 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
377 if X_LAST_UPDATED_BY is NULL then
378 X_LAST_UPDATED_BY := -1;
379 end if;
380 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
381 if X_LAST_UPDATE_LOGIN is NULL then
382 X_LAST_UPDATE_LOGIN := -1;
383 end if;
384 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
385 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
386 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
387 if (X_REQUEST_ID = -1) then
388 X_REQUEST_ID := NULL;
389 X_PROGRAM_ID := NULL;
390 X_PROGRAM_APPLICATION_ID := NULL;
391 X_PROGRAM_UPDATE_DATE := NULL;
392 else
393 X_PROGRAM_UPDATE_DATE := SYSDATE;
394 end if;
395 else
396 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
397 IGS_GE_MSG_STACK.ADD;
398 app_exception.raise_exception;
399 end if;
400
401
402
403 SELECT igs_pe_acad_honors_s.nextval
404 INTO X_ACAD_HONOR_ID
405 FROM dual;
406
407 Before_DML(
408 p_action=>'INSERT',
409 x_rowid=>X_ROWID,
410 x_acad_honor_id=>X_ACAD_HONOR_ID,
411 x_person_id=>X_PERSON_ID,
412 x_comments=>X_COMMENTS,
413 x_honor_date=>X_HONOR_DATE,
414 x_creation_date=>X_LAST_UPDATE_DATE,
415 x_created_by=>X_LAST_UPDATED_BY,
416 x_last_update_date=>X_LAST_UPDATE_DATE,
417 x_last_updated_by=>X_LAST_UPDATED_BY,
418 x_last_update_login=>X_LAST_UPDATE_LOGIN,
419 x_acad_honor_type=>X_ACAD_HONOR_TYPE);
420
421
422
423 IF (x_mode = 'S') THEN
424 igs_sc_gen_001.set_ctx('R');
425 END IF;
426 insert into igs_pe_acad_honors (
427 ACAD_HONOR_ID
428 ,PERSON_ID
429 ,COMMENTS
430 ,HONOR_DATE
431 ,CREATION_DATE
432 ,CREATED_BY
433 ,LAST_UPDATE_DATE
434 ,LAST_UPDATED_BY
435 ,LAST_UPDATE_LOGIN
436 ,REQUEST_ID
437 ,PROGRAM_ID
438 ,PROGRAM_APPLICATION_ID
439 ,PROGRAM_UPDATE_DATE
440 ,ACAD_HONOR_TYPE
441 ) values (
442 NEW_REFERENCES.ACAD_HONOR_ID
443 ,NEW_REFERENCES.PERSON_ID
444 ,NEW_REFERENCES.COMMENTS
445 ,NEW_REFERENCES.HONOR_DATE
446 ,X_LAST_UPDATE_DATE
447 ,X_LAST_UPDATED_BY
448 ,X_LAST_UPDATE_DATE
449 ,X_LAST_UPDATED_BY
450 ,X_LAST_UPDATE_LOGIN
451 ,X_REQUEST_ID
452 ,X_PROGRAM_ID
453 ,X_PROGRAM_APPLICATION_ID
454 ,X_PROGRAM_UPDATE_DATE
455 ,NEW_REFERENCES.ACAD_HONOR_TYPE
456 );
457 IF (x_mode = 'S') THEN
458 igs_sc_gen_001.unset_ctx('R');
459 END IF;
460
461 open c;
462 fetch c into X_ROWID;
463 if (c%notfound) then
464 close c;
465 raise no_data_found;
466 end if;
467 close c;
468
469 After_DML (
470 p_action => 'INSERT' ,
471 x_rowid => X_ROWID );
472 EXCEPTION
473 WHEN OTHERS THEN
474 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
475 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
476 fnd_message.set_token ('ERR_CD', SQLCODE);
477 igs_ge_msg_stack.add;
478 igs_sc_gen_001.unset_ctx('R');
479 app_exception.raise_exception;
480 ELSE
481 igs_sc_gen_001.unset_ctx('R');
482 RAISE;
483 END IF;
484
485 end INSERT_ROW;
486 procedure LOCK_ROW (
487 X_ROWID in VARCHAR2,
488 x_ACAD_HONOR_ID IN NUMBER,
489 x_PERSON_ID IN NUMBER,
490 x_COMMENTS IN VARCHAR2,
491 x_HONOR_DATE IN DATE,
492 x_ACAD_HONOR_TYPE IN VARCHAR2) AS
493 /*************************************************************
494 Created By :samaresh
495 Date Created By :15-May-2000
496 Purpose :
497 Know limitations, enhancements or remarks
498 Change History
499 Who When What
500
501 (reverse chronological order - newest change first)
502 kamohan 1/21/02 Removed the reference to nominated_course_cd
503 and sequence_number
504 ***************************************************************/
505
506 cursor c1 is select
507 PERSON_ID
508 , ACAD_HONOR_TYPE --changed type_id to cd
509 , COMMENTS
510 , HONOR_DATE
511 from igs_pe_acad_honors
512 where ROWID = X_ROWID
513 for update nowait;
514 tlinfo c1%rowtype;
515 begin
516 open c1;
517 fetch c1 into tlinfo;
518 if (c1%notfound) then
519 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
520 IGS_GE_MSG_STACK.ADD;
521 close c1;
522 app_exception.raise_exception;
523 return;
524 end if;
525 close c1;
526 if ( ( tlinfo.PERSON_ID = X_PERSON_ID)
527 AND (tlinfo.ACAD_HONOR_TYPE = X_ACAD_HONOR_TYPE) --CHANGED ACAD_HONOR_TYPE_ID TO ACAD_HONOR_TYPE AND ID TO CD
528 AND ((tlinfo.COMMENTS = X_COMMENTS)
529 OR ((tlinfo.COMMENTS is null)
530 AND (X_COMMENTS is null)))
531 AND ((tlinfo.HONOR_DATE = X_HONOR_DATE)
532 OR ((tlinfo.HONOR_DATE is null)
533 AND (X_HONOR_DATE is null)))
534 ) then
535 null;
536 else
537 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
538 IGS_GE_MSG_STACK.ADD;
539 app_exception.raise_exception;
540 end if;
541 return;
542 end LOCK_ROW;
543 Procedure UPDATE_ROW (
544 X_ROWID in VARCHAR2,
545 x_ACAD_HONOR_ID IN NUMBER,
546 x_PERSON_ID IN NUMBER,
547 x_COMMENTS IN VARCHAR2,
548 x_HONOR_DATE IN DATE,
549 X_MODE in VARCHAR2 ,
550 x_ACAD_HONOR_TYPE IN VARCHAR2
551 ) AS
552 /*************************************************************
553 Created By :samaresh
554 Date Created By :15-May-2000
555 Purpose :
556 Know limitations, enhancements or remarks
557 Change History
558 Who When What
559
560 (reverse chronological order - newest change first)
561 ***************************************************************/
562
563 X_LAST_UPDATE_DATE DATE ;
564 X_LAST_UPDATED_BY NUMBER ;
565 X_LAST_UPDATE_LOGIN NUMBER ;
566 X_REQUEST_ID NUMBER;
567 X_PROGRAM_ID NUMBER;
568 X_PROGRAM_APPLICATION_ID NUMBER;
569 X_PROGRAM_UPDATE_DATE DATE;
570 begin
571 X_LAST_UPDATE_DATE := SYSDATE;
572 if(X_MODE = 'I') then
573 X_LAST_UPDATED_BY := 1;
574 X_LAST_UPDATE_LOGIN := 0;
575 elsif (X_MODE IN ('R', 'S')) then
576 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
577 if X_LAST_UPDATED_BY is NULL then
578 X_LAST_UPDATED_BY := -1;
579 end if;
580 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
581 if X_LAST_UPDATE_LOGIN is NULL then
582 X_LAST_UPDATE_LOGIN := -1;
583 end if;
584 else
585 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
586 IGS_GE_MSG_STACK.ADD;
587 app_exception.raise_exception;
588 end if;
589 Before_DML(
590 p_action=>'UPDATE',
591 x_rowid=>X_ROWID,
592 x_acad_honor_id=>X_ACAD_HONOR_ID,
593 x_person_id=>X_PERSON_ID,
594 x_comments=>X_COMMENTS,
595 x_honor_date=>X_HONOR_DATE,
596 x_creation_date=>X_LAST_UPDATE_DATE,
597 x_created_by=>X_LAST_UPDATED_BY,
598 x_last_update_date=>X_LAST_UPDATE_DATE,
599 x_last_updated_by=>X_LAST_UPDATED_BY,
600 x_last_update_login=>X_LAST_UPDATE_LOGIN,
601 x_acad_honor_type=>x_ACAD_HONOR_TYPE);
602
603 if (X_MODE IN ('R', 'S')) then
604 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
605 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
606 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
607 if (X_REQUEST_ID = -1) then
608 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
609 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
610 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
611 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
612 else
613 X_PROGRAM_UPDATE_DATE := SYSDATE;
614 end if;
615 end if;
616
617 IF (x_mode = 'S') THEN
618 igs_sc_gen_001.set_ctx('R');
619 END IF;
620 update igs_pe_acad_honors set
621 PERSON_ID = NEW_REFERENCES.PERSON_ID,
622 ACAD_HONOR_TYPE = NEW_REFERENCES.ACAD_HONOR_TYPE,--CHANGED HERE
623 COMMENTS = NEW_REFERENCES.COMMENTS,
624 HONOR_DATE = NEW_REFERENCES.HONOR_DATE,
625 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
626 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
627 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
628 REQUEST_ID = X_REQUEST_ID,
629 PROGRAM_ID = X_PROGRAM_ID,
630 PROGRAM_APPLICATION_ID = PROGRAM_APPLICATION_ID,
631 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
632 where ROWID = X_ROWID;
633 if (sql%notfound) then
634 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
635 igs_ge_msg_stack.add;
636 igs_sc_gen_001.unset_ctx('R');
637 app_exception.raise_exception;
638 end if;
639 IF (x_mode = 'S') THEN
640 igs_sc_gen_001.unset_ctx('R');
641 END IF;
642
643
644 After_DML (
645 p_action => 'UPDATE' ,
646 x_rowid => X_ROWID
647 );
648 EXCEPTION
649 WHEN OTHERS THEN
650 IF (SQLCODE = (-28115)) THEN
651 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
652 fnd_message.set_token ('ERR_CD', SQLCODE);
653 igs_ge_msg_stack.add;
654 igs_sc_gen_001.unset_ctx('R');
655 app_exception.raise_exception;
656 ELSE
657 igs_sc_gen_001.unset_ctx('R');
658 RAISE;
659 END IF;
660
661 end UPDATE_ROW;
662 procedure ADD_ROW (
663 X_ROWID in out NOCOPY VARCHAR2,
664 x_ACAD_HONOR_ID IN OUT NOCOPY NUMBER,
665 x_PERSON_ID IN NUMBER,
666 x_COMMENTS IN VARCHAR2,
667 x_HONOR_DATE IN DATE,
668 X_MODE in VARCHAR2 ,
669 x_ACAD_HONOR_TYPE IN VARCHAR2
670 ) AS
671 /*************************************************************
672 Created By :samaresh
673 Date Created By :15-May-2000
674 Purpose :
675 Know limitations, enhancements or remarks
676 Change History
677 Who When What
678
679 (reverse chronological order - newest change first)
680 ***************************************************************/
681
682 cursor c1 is select ROWID from igs_pe_acad_honors
683 where ACAD_HONOR_ID= X_ACAD_HONOR_ID;
684 begin
685 open c1;
686 fetch c1 into X_ROWID;
687 if (c1%notfound) then
688 close c1;
689 INSERT_ROW (
690 X_ROWID,
691 X_ACAD_HONOR_ID,
692 X_PERSON_ID,
693 X_COMMENTS,
694 X_HONOR_DATE,
695 X_MODE,
696 x_ACAD_HONOR_TYPE);
697 return;
698 end if;
699 close c1;
700 UPDATE_ROW (
701 X_ROWID,
702 X_ACAD_HONOR_ID,
703 X_PERSON_ID,
704 X_COMMENTS,
705 X_HONOR_DATE,
706 X_MODE,
707 x_ACAD_HONOR_TYPE);
708 end ADD_ROW;
709
710 procedure DELETE_ROW (
711 X_ROWID in VARCHAR2,
712 x_mode IN VARCHAR2
713 ) AS
714 /*************************************************************
715 Created By :samaresh
716 Date Created By :15-May-2000
717 Purpose :
718 Know limitations, enhancements or remarks
719 Change History
720 Who When What
721
722 (reverse chronological order - newest change first)
723 ***************************************************************/
724
725 begin
726 /*Before_DML (
727 p_action => 'DELETE',
728 x_rowid => X_ROWID,
729 );*/
730 IF (x_mode = 'S') THEN
731 igs_sc_gen_001.set_ctx('R');
732 END IF;
733 delete from igs_pe_acad_honors
734 where ROWID = X_ROWID;
735 if (sql%notfound) then
736 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
737 igs_ge_msg_stack.add;
738 igs_sc_gen_001.unset_ctx('R');
739 app_exception.raise_exception;
740 end if;
741 IF (x_mode = 'S') THEN
742 igs_sc_gen_001.unset_ctx('R');
743 END IF;
744
745 After_DML (
746 p_action => 'DELETE',
747 x_rowid => X_ROWID
748 );
749 end DELETE_ROW;
750
751 END IGS_PE_ACAD_HONORS_PKG;