1 PACKAGE BODY igs_ps_faclty_degrs_pkg AS
2 /* $Header: IGSPI0OB.pls 120.1 2005/06/28 06:23:35 appldev ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ps_faclty_degrs%RowType;
5 new_references igs_ps_faclty_degrs%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_faclty_degrd_id IN NUMBER DEFAULT NULL,
11 x_person_id IN NUMBER DEFAULT NULL,
12 x_degree_cd IN VARCHAR2 DEFAULT NULL,
13 x_program IN VARCHAR2 DEFAULT NULL,
14 x_institution_cd IN VARCHAR2 DEFAULT NULL,
15 x_degree_date IN DATE DEFAULT NULL,
16 x_creation_date IN DATE DEFAULT NULL,
17 x_created_by IN NUMBER DEFAULT NULL,
18 x_last_update_date IN DATE DEFAULT NULL,
19 x_last_updated_by IN NUMBER DEFAULT NULL,
20 x_last_update_login IN NUMBER DEFAULT NULL
21 ) AS
22
23 /*************************************************************
24 Created By :ssuri
25 Date Created By :11-MAY-2000
26 Purpose :NEW TABLE
27 Know limitations, enhancements or remarks
28 Change History
29 Who When What
30
31 (reverse chronological order - newest change first)
32 ***************************************************************/
33
34 CURSOR cur_old_ref_values IS
35 SELECT *
36 FROM IGS_PS_FACLTY_DEGRS
37 WHERE rowid = x_rowid;
38
39 BEGIN
40
41 l_rowid := x_rowid;
42
43 -- Code for setting the Old and New Reference Values.
44 -- Populate Old Values.
45 Open cur_old_ref_values;
46 Fetch cur_old_ref_values INTO old_references;
47 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
48 Close cur_old_ref_values;
49 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
50 IGS_GE_MSG_STACK.ADD;
51 App_Exception.Raise_Exception;
52 Return;
53 END IF;
54 Close cur_old_ref_values;
55
56 -- Populate New Values.
57 new_references.faclty_degrd_id := x_faclty_degrd_id;
58 new_references.person_id := x_person_id;
59 new_references.degree_cd := x_degree_cd;
60 new_references.program := x_program;
61 new_references.institution_cd := x_institution_cd;
62 new_references.degree_date := x_degree_date;
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 DEFAULT NULL,
78 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
79 /*************************************************************
80 Created By :ssuri
81 Date Created By :11-MAY-2000
82 Purpose :NEW TABLE
83 Know limitations, enhancements or remarks
84 Change History
85 Who When What
86 (reverse chronological order - newest change first)
87 vvutukur 26-03-2002 restricted program column to upper case only.
88 for bug:2082568.
89 ***************************************************************/
90
91 BEGIN
92
93 IF column_name IS NULL THEN
94 NULL;
95 ELSIF UPPER(column_name) = 'PROGRAM' THEN
96 new_references.program := column_value;
97 END IF;
98
99 --If entered value in program column is not in upper case ,error out.
100 IF UPPER(column_name) = 'PROGRAM' OR column_name IS NULL THEN
101 IF new_references.program <> UPPER(new_references.program) THEN
102 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
103 IGS_GE_MSG_STACK.ADD;
104 APP_EXCEPTION.RAISE_EXCEPTION;
105 END IF;
106 END IF;
107
108 END Check_Constraints;
109
110 PROCEDURE Check_Uniqueness AS
111 /*************************************************************
112 Created By :ssuri
113 Date Created By :11-MAY-2000
114 Purpose :NEW TABLE
115 Know limitations, enhancements or remarks
116 Change History
117 Who When What
118
119 (reverse chronological order - newest change first)
120 ***************************************************************/
121
122 begin
123 IF Get_Uk_For_Validation (
124 new_references.degree_cd
125 ,new_references.person_id
126 ,new_references.program
127 ) THEN
128 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
129 IGS_GE_MSG_STACK.ADD;
130 app_exception.raise_exception;
131 END IF;
132 END Check_Uniqueness ;
133 PROCEDURE Check_Parent_Existance AS
134 /*************************************************************
135 Created By :ssuri
136 Date Created By :11-MAY-2000
137 Purpose :NEW TABLE
138 Know limitations, enhancements or remarks
139 Change History
140 Who When What
141
142 (reverse chronological order - newest change first)
143 ***************************************************************/
144
145 BEGIN
146
147 IF (((old_references.degree_cd = new_references.degree_cd)) OR
148 ((new_references.degree_cd IS NULL))) THEN
149 NULL;
150 ELSIF NOT Igs_Ps_Degrees_Pkg.Get_PK_For_Validation (
151 new_references.degree_cd
152 ) THEN
153 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
154 IGS_GE_MSG_STACK.ADD;
155 App_Exception.Raise_Exception;
156 END IF;
157
158 IF (((old_references.institution_cd = new_references.institution_cd)) OR
159 ((new_references.institution_cd IS NULL))) THEN
160 NULL;
161 ELSIF NOT Igs_Or_Institution_Pkg.Get_PK_For_Validation (
162 new_references.institution_cd
163 ) THEN
164 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
165 IGS_GE_MSG_STACK.ADD;
166 App_Exception.Raise_Exception;
167 END IF;
168
169 IF (((old_references.person_id = new_references.person_id)) OR
170 ((new_references.person_id IS NULL))) THEN
171 NULL;
172 ELSIF NOT Igs_Pe_Person_Pkg.Get_PK_For_Validation (
173 new_references.person_id
174 ) THEN
175 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
176 IGS_GE_MSG_STACK.ADD;
177 App_Exception.Raise_Exception;
178 END IF;
179
180 END Check_Parent_Existance;
181
182 FUNCTION Get_PK_For_Validation (
183 x_faclty_degrd_id IN NUMBER
184 ) RETURN BOOLEAN AS
185
186 /*************************************************************
187 Created By :ssuri
188 Date Created By :11-MAY-2000
189 Purpose :NEW TABLE
190 Know limitations, enhancements or remarks
191 Change History
192 Who When What
193
194 (reverse chronological order - newest change first)
195 ***************************************************************/
196
197 CURSOR cur_rowid IS
198 SELECT rowid
199 FROM igs_ps_faclty_degrs
200 WHERE faclty_degrd_id = x_faclty_degrd_id
201 FOR UPDATE NOWAIT;
202
203 lv_rowid cur_rowid%RowType;
204
205 BEGIN
206
207 Open cur_rowid;
208 Fetch cur_rowid INTO lv_rowid;
209 IF (cur_rowid%FOUND) THEN
210 Close cur_rowid;
211 Return(TRUE);
212 ELSE
213 Close cur_rowid;
214 Return(FALSE);
215 END IF;
216 END Get_PK_For_Validation;
217
218 FUNCTION get_uk_for_validation (
219 x_degree_cd IN VARCHAR2,
220 x_person_id IN NUMBER,
221 x_program IN VARCHAR2 --for bug:2082568
222 ) RETURN BOOLEAN AS
223
224 /*************************************************************
225 Created By :ssuri
226 Date Created By :11-MAY-2000
227 Purpose :NEW TABLE
228 Know limitations, enhancements or remarks
229 Change History
230 Who When What
231 vvutukur 14-MAR-2002 added program column also as a part
232 of unique key for bug:2082568.
233 (reverse chronological order - newest change first)
234 ***************************************************************/
235
236 CURSOR cur_rowid IS
237 SELECT rowid
238 FROM igs_ps_faclty_degrs
239 WHERE degree_cd = x_degree_cd
240 AND person_id = x_person_id
241 AND program = x_program
242 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
243
244 lv_rowid cur_rowid%RowType;
245
246 BEGIN
247
248 OPEN cur_rowid;
249 FETCH cur_rowid INTO lv_rowid;
250 IF (cur_rowid%FOUND) THEN
251 CLOSE cur_rowid;
252 RETURN(TRUE);
253 ELSE
254 CLOSE cur_rowid;
255 RETURN(FALSE);
256 END IF;
257 END get_uk_for_validation;
258
259 PROCEDURE Get_FK_Igs_Ps_Degrees (
260 x_degree_cd IN VARCHAR2
261 ) AS
262
263 /*************************************************************
264 Created By :ssuri
265 Date Created By :11-MAY-2000
266 Purpose :NEW TABLE
267 Know limitations, enhancements or remarks
268 Change History
269 Who When What
270
271 (reverse chronological order - newest change first)
272 ***************************************************************/
273
274 CURSOR cur_rowid IS
275 SELECT rowid
276 FROM igs_ps_faclty_degrs
277 WHERE degree_cd = x_degree_cd ;
278
279 lv_rowid cur_rowid%RowType;
280
281 BEGIN
282
283 Open cur_rowid;
284 Fetch cur_rowid INTO lv_rowid;
285 IF (cur_rowid%FOUND) THEN
286 Close cur_rowid;
287 Fnd_Message.Set_Name ('IGS', 'IGS_PS_FD_DEG_FK');
288 IGS_GE_MSG_STACK.ADD;
289 App_Exception.Raise_Exception;
290 Return;
291 END IF;
292 Close cur_rowid;
293
294 END Get_FK_Igs_Ps_Degrees;
295
296 PROCEDURE Get_FK_Igs_Or_Institution (
297 x_institution_cd IN VARCHAR2
298 ) AS
299
300 /*************************************************************
301 Created By :ssuri
302 Date Created By :11-MAY-2000
303 Purpose :NEW TABLE
304 Know limitations, enhancements or remarks
305 Change History
306 Who When What
307
308 (reverse chronological order - newest change first)
309 ***************************************************************/
310
311 CURSOR cur_rowid IS
312 SELECT rowid
313 FROM igs_ps_faclty_degrs
314 WHERE institution_cd = x_institution_cd ;
315
316 lv_rowid cur_rowid%RowType;
317
318 BEGIN
319
320 Open cur_rowid;
321 Fetch cur_rowid INTO lv_rowid;
322 IF (cur_rowid%FOUND) THEN
323 Close cur_rowid;
324 Fnd_Message.Set_Name ('IGS', 'IGS_PS_FD_INS_FK');
325 IGS_GE_MSG_STACK.ADD;
326 App_Exception.Raise_Exception;
327 Return;
328 END IF;
329 Close cur_rowid;
330
331 END Get_FK_Igs_Or_Institution;
332
333 PROCEDURE Get_FK_Igs_Pe_Person (
334 x_person_id IN NUMBER
335 ) AS
336
337 /*************************************************************
338 Created By :ssuri
339 Date Created By :11-MAY-2000
340 Purpose :NEW TABLE
341 Know limitations, enhancements or remarks
342 Change History
343 Who When What
344
345 (reverse chronological order - newest change first)
346 ***************************************************************/
347
348 CURSOR cur_rowid IS
349 SELECT rowid
350 FROM igs_ps_faclty_degrs
351 WHERE person_id = x_person_id ;
352
353 lv_rowid cur_rowid%RowType;
354
355 BEGIN
356
357 Open cur_rowid;
358 Fetch cur_rowid INTO lv_rowid;
359 IF (cur_rowid%FOUND) THEN
360 Close cur_rowid;
361 Fnd_Message.Set_Name ('IGS', 'IGS_PS_FD_PE_FK');
362 IGS_GE_MSG_STACK.ADD;
363 App_Exception.Raise_Exception;
364 Return;
365 END IF;
366 Close cur_rowid;
367
368 END Get_FK_Igs_Pe_Person;
369
370 PROCEDURE Before_DML (
371 p_action IN VARCHAR2,
372 x_rowid IN VARCHAR2 DEFAULT NULL,
373 x_faclty_degrd_id IN NUMBER DEFAULT NULL,
374 x_person_id IN NUMBER DEFAULT NULL,
375 x_degree_cd IN VARCHAR2 DEFAULT NULL,
376 x_program IN VARCHAR2 DEFAULT NULL,
377 x_institution_cd IN VARCHAR2 DEFAULT NULL,
378 x_degree_date IN DATE DEFAULT NULL,
379 x_creation_date IN DATE DEFAULT NULL,
380 x_created_by IN NUMBER DEFAULT NULL,
381 x_last_update_date IN DATE DEFAULT NULL,
382 x_last_updated_by IN NUMBER DEFAULT NULL,
383 x_last_update_login IN NUMBER DEFAULT NULL
384 ) AS
385 /*************************************************************
386 Created By :ssuri
387 Date Created By :11-MAY-2000
388 Purpose :NEW TABLE
389 Know limitations, enhancements or remarks
390 Change History
391 Who When What
392
393 (reverse chronological order - newest change first)
394 ***************************************************************/
395
396 BEGIN
397
398 Set_Column_Values (
399 p_action,
400 x_rowid,
401 x_faclty_degrd_id,
402 x_person_id,
403 x_degree_cd,
404 x_program,
405 x_institution_cd,
406 x_degree_date,
407 x_creation_date,
408 x_created_by,
409 x_last_update_date,
410 x_last_updated_by,
411 x_last_update_login
412 );
413
414 IF (p_action = 'INSERT') THEN
415 -- Call all the procedures related to Before Insert.
416 Null;
417 IF Get_Pk_For_Validation(
418 new_references.faclty_degrd_id) THEN
419 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
420 IGS_GE_MSG_STACK.ADD;
421 App_Exception.Raise_Exception;
422 END IF;
423 Check_Uniqueness;
424 Check_Constraints;
425 Check_Parent_Existance;
426 ELSIF (p_action = 'UPDATE') THEN
427 -- Call all the procedures related to Before Update.
428 Null;
429 Check_Uniqueness;
430 Check_Constraints;
431 Check_Parent_Existance;
432 ELSIF (p_action = 'DELETE') THEN
433 -- Call all the procedures related to Before Delete.
434 Null;
435 ELSIF (p_action = 'VALIDATE_INSERT') THEN
436 -- Call all the procedures related to Before Insert.
437 IF Get_PK_For_Validation (
438 new_references.faclty_degrd_id) THEN
439 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
440 IGS_GE_MSG_STACK.ADD;
441 App_Exception.Raise_Exception;
442 END IF;
443 Check_Uniqueness;
444 Check_Constraints;
445 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
446 Check_Uniqueness;
447 Check_Constraints;
448 ELSIF (p_action = 'VALIDATE_DELETE') THEN
449 Null;
450 END IF;
451
452 END Before_DML;
453
454 PROCEDURE After_DML (
455 p_action IN VARCHAR2,
456 x_rowid IN VARCHAR2
457 ) IS
458 /*************************************************************
459 Created By :ssuri
460 Date Created By :11-MAY-2000
461 Purpose :NEW TABLE
462 Know limitations, enhancements or remarks
463 Change History
464 Who When What
465
466 (reverse chronological order - newest change first)
467 ***************************************************************/
468
469 BEGIN
470
471 l_rowid := x_rowid;
472
473 IF (p_action = 'INSERT') THEN
474 -- Call all the procedures related to After Insert.
475 Null;
476 ELSIF (p_action = 'UPDATE') THEN
477 -- Call all the procedures related to After Update.
478 Null;
479 ELSIF (p_action = 'DELETE') THEN
480 -- Call all the procedures related to After Delete.
481 Null;
482 END IF;
483 l_rowid:=NULL;
484 END After_DML;
485
486 procedure INSERT_ROW (
487 X_ROWID in out NOCOPY VARCHAR2,
488 x_FACLTY_DEGRD_ID IN OUT NOCOPY NUMBER,
489 x_PERSON_ID IN NUMBER,
490 x_DEGREE_CD IN VARCHAR2,
491 x_PROGRAM IN VARCHAR2,
492 x_INSTITUTION_CD IN VARCHAR2,
493 x_DEGREE_DATE IN DATE,
494 X_MODE in VARCHAR2 default 'R'
495 ) AS
496
497 /*************************************************************
498 Created By :ssuri
499 Date Created By :11-MAY-2000
500 Purpose :NEW TABLE
501 Know limitations, enhancements or remarks
502 Change History
503 Who When What
504
505 (reverse chronological order - newest change first)
506 ***************************************************************/
507
508 cursor C is select ROWID from IGS_PS_FACLTY_DEGRS
509 where FACLTY_DEGRD_ID= X_FACLTY_DEGRD_ID
510 ;
511 X_LAST_UPDATE_DATE DATE ;
512 X_LAST_UPDATED_BY NUMBER ;
513 X_LAST_UPDATE_LOGIN NUMBER ;
514 begin
515 X_LAST_UPDATE_DATE := SYSDATE;
516 if(X_MODE = 'I') then
517 X_LAST_UPDATED_BY := 1;
518 X_LAST_UPDATE_LOGIN := 0;
519 elsif (X_MODE IN ('R', 'S')) then
520 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
521 if X_LAST_UPDATED_BY is NULL then
522 X_LAST_UPDATED_BY := -1;
523 end if;
524 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
525 if X_LAST_UPDATE_LOGIN is NULL then
526 X_LAST_UPDATE_LOGIN := -1;
527 end if;
528 else
529 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
530 IGS_GE_MSG_STACK.ADD;
531 app_exception.raise_exception;
532 end if;
533 SELECT IGS_PS_FACLTY_DEGRD_ID_S.nextval INTO x_FACLTY_DEGRD_ID FROM dual;
534 Before_DML(
535 p_action=>'INSERT',
536 x_rowid=>X_ROWID,
537 x_faclty_degrd_id=>X_FACLTY_DEGRD_ID,
538 x_person_id=>X_PERSON_ID,
539 x_degree_cd=>X_DEGREE_CD,
540 x_program=>X_PROGRAM,
541 x_institution_cd=>X_INSTITUTION_CD,
542 x_degree_date=>X_DEGREE_DATE,
543 x_creation_date=>X_LAST_UPDATE_DATE,
544 x_created_by=>X_LAST_UPDATED_BY,
545 x_last_update_date=>X_LAST_UPDATE_DATE,
546 x_last_updated_by=>X_LAST_UPDATED_BY,
547 x_last_update_login=>X_LAST_UPDATE_LOGIN);
548 IF (x_mode = 'S') THEN
549 igs_sc_gen_001.set_ctx('R');
550 END IF;
551 insert into IGS_PS_FACLTY_DEGRS (
552 FACLTY_DEGRD_ID
553 ,PERSON_ID
554 ,DEGREE_CD
555 ,PROGRAM
556 ,INSTITUTION_CD
557 ,DEGREE_DATE
558 ,CREATION_DATE
559 ,CREATED_BY
560 ,LAST_UPDATE_DATE
561 ,LAST_UPDATED_BY
562 ,LAST_UPDATE_LOGIN
563 ) values (
564 NEW_REFERENCES.FACLTY_DEGRD_ID
565 ,NEW_REFERENCES.PERSON_ID
566 ,NEW_REFERENCES.DEGREE_CD
567 ,NEW_REFERENCES.PROGRAM
568 ,NEW_REFERENCES.INSTITUTION_CD
569 ,NEW_REFERENCES.DEGREE_DATE
570 ,X_LAST_UPDATE_DATE
571 ,X_LAST_UPDATED_BY
572 ,X_LAST_UPDATE_DATE
573 ,X_LAST_UPDATED_BY
574 ,X_LAST_UPDATE_LOGIN
575 );
576 IF (x_mode = 'S') THEN
577 igs_sc_gen_001.unset_ctx('R');
578 END IF;
579
580 open c;
581 fetch c into X_ROWID;
582 if (c%notfound) then
583 close c;
584 raise no_data_found;
585 end if;
586 close c;
587 After_DML (
588 p_action => 'INSERT' ,
589 x_rowid => X_ROWID );
590 EXCEPTION
591 WHEN OTHERS THEN
592 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
593 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
594 fnd_message.set_token ('ERR_CD', SQLCODE);
595 igs_ge_msg_stack.add;
596 igs_sc_gen_001.unset_ctx('R');
597 app_exception.raise_exception;
598 ELSE
599 igs_sc_gen_001.unset_ctx('R');
600 RAISE;
601 END IF;
602
603 end INSERT_ROW;
604 procedure LOCK_ROW (
605 X_ROWID in VARCHAR2,
606 x_FACLTY_DEGRD_ID IN NUMBER,
607 x_PERSON_ID IN NUMBER,
608 x_DEGREE_CD IN VARCHAR2,
609 x_PROGRAM IN VARCHAR2,
610 x_INSTITUTION_CD IN VARCHAR2,
611 x_DEGREE_DATE IN DATE ) AS
612 /*************************************************************
613 Created By :ssuri
614 Date Created By :11-MAY-2000
615 Purpose :NEW TABLE
616 Know limitations, enhancements or remarks
617 Change History
618 Who When What
619
620 (reverse chronological order - newest change first)
621 ***************************************************************/
622
623 cursor c1 is select
624 PERSON_ID
625 , DEGREE_CD
626 , PROGRAM
627 , INSTITUTION_CD
628 , DEGREE_DATE
629 from IGS_PS_FACLTY_DEGRS
630 where ROWID = X_ROWID
631 for update nowait;
632 tlinfo c1%rowtype;
633 begin
634 open c1;
635 fetch c1 into tlinfo;
636 if (c1%notfound) then
637 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
638 IGS_GE_MSG_STACK.ADD;
639 close c1;
640 app_exception.raise_exception;
641 return;
642 end if;
643 close c1;
644 if ( ( tlinfo.PERSON_ID = X_PERSON_ID)
645 AND (tlinfo.DEGREE_CD = X_DEGREE_CD)
646 AND (tlinfo.PROGRAM = X_PROGRAM)
647 AND (tlinfo.INSTITUTION_CD = X_INSTITUTION_CD)
648 AND (tlinfo.DEGREE_DATE = X_DEGREE_DATE)
649 ) then
650 null;
651 else
652 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
653 IGS_GE_MSG_STACK.ADD;
654 app_exception.raise_exception;
655 end if;
656 return;
657 end LOCK_ROW;
658 Procedure UPDATE_ROW (
659 X_ROWID in VARCHAR2,
660 x_FACLTY_DEGRD_ID IN NUMBER,
661 x_PERSON_ID IN NUMBER,
662 x_DEGREE_CD IN VARCHAR2,
663 x_PROGRAM IN VARCHAR2,
664 x_INSTITUTION_CD IN VARCHAR2,
665 x_DEGREE_DATE IN DATE,
666 X_MODE in VARCHAR2 default 'R'
667 ) AS
668 /*************************************************************
669 Created By :ssuri
670 Date Created By :11-MAY-2000
671 Purpose :NEW TABLE
672 Know limitations, enhancements or remarks
673 Change History
674 Who When What
675
676 (reverse chronological order - newest change first)
677 ***************************************************************/
678
679 X_LAST_UPDATE_DATE DATE ;
680 X_LAST_UPDATED_BY NUMBER ;
681 X_LAST_UPDATE_LOGIN NUMBER ;
682 begin
683 X_LAST_UPDATE_DATE := SYSDATE;
684 if(X_MODE = 'I') then
685 X_LAST_UPDATED_BY := 1;
686 X_LAST_UPDATE_LOGIN := 0;
687 elsif (X_MODE IN ('R', 'S')) then
688 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
689 if X_LAST_UPDATED_BY is NULL then
690 X_LAST_UPDATED_BY := -1;
691 end if;
692 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
693 if X_LAST_UPDATE_LOGIN is NULL then
694 X_LAST_UPDATE_LOGIN := -1;
695 end if;
696 else
697 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
698 IGS_GE_MSG_STACK.ADD;
699 app_exception.raise_exception;
700 end if;
701 Before_DML(
702 p_action=>'UPDATE',
703 x_rowid=>X_ROWID,
704 x_faclty_degrd_id=>X_FACLTY_DEGRD_ID,
705 x_person_id=>X_PERSON_ID,
706 x_degree_cd=>X_DEGREE_CD,
707 x_program=>X_PROGRAM,
708 x_institution_cd=>X_INSTITUTION_CD,
709 x_degree_date=>X_DEGREE_DATE,
710 x_creation_date=>X_LAST_UPDATE_DATE,
711 x_created_by=>X_LAST_UPDATED_BY,
712 x_last_update_date=>X_LAST_UPDATE_DATE,
713 x_last_updated_by=>X_LAST_UPDATED_BY,
714 x_last_update_login=>X_LAST_UPDATE_LOGIN);
715 IF (x_mode = 'S') THEN
716 igs_sc_gen_001.set_ctx('R');
717 END IF;
718 update IGS_PS_FACLTY_DEGRS set
719 PERSON_ID = NEW_REFERENCES.PERSON_ID,
720 DEGREE_CD = NEW_REFERENCES.DEGREE_CD,
721 PROGRAM = NEW_REFERENCES.PROGRAM,
722 INSTITUTION_CD = NEW_REFERENCES.INSTITUTION_CD,
723 DEGREE_DATE = NEW_REFERENCES.DEGREE_DATE,
724 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
725 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
726 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
727 where ROWID = X_ROWID;
728 if (sql%notfound) then
729 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
730 igs_ge_msg_stack.add;
731 igs_sc_gen_001.unset_ctx('R');
732 app_exception.raise_exception;
733 end if;
734 IF (x_mode = 'S') THEN
735 igs_sc_gen_001.unset_ctx('R');
736 END IF;
737
738
739 After_DML (
740 p_action => 'UPDATE' ,
741 x_rowid => X_ROWID
742 );
743 EXCEPTION
744 WHEN OTHERS THEN
745 IF (SQLCODE = (-28115)) THEN
746 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
747 fnd_message.set_token ('ERR_CD', SQLCODE);
748 igs_ge_msg_stack.add;
749 igs_sc_gen_001.unset_ctx('R');
750 app_exception.raise_exception;
751 ELSE
752 igs_sc_gen_001.unset_ctx('R');
753 RAISE;
754 END IF;
755
756 end UPDATE_ROW;
757 procedure ADD_ROW (
758 X_ROWID in out NOCOPY VARCHAR2,
759 x_FACLTY_DEGRD_ID IN OUT NOCOPY NUMBER,
760 x_PERSON_ID IN NUMBER,
761 x_DEGREE_CD IN VARCHAR2,
762 x_PROGRAM IN VARCHAR2,
763 x_INSTITUTION_CD IN VARCHAR2,
764 x_DEGREE_DATE IN DATE,
765 X_MODE in VARCHAR2 default 'R'
766 ) AS
767 /*************************************************************
768 Created By :ssuri
769 Date Created By :11-MAY-2000
770 Purpose :NEW TABLE
771 Know limitations, enhancements or remarks
772 Change History
773 Who When What
774
775 (reverse chronological order - newest change first)
776 ***************************************************************/
777
778 cursor c1 is select ROWID from IGS_PS_FACLTY_DEGRS
779 where FACLTY_DEGRD_ID= X_FACLTY_DEGRD_ID
780 ;
781 begin
782 open c1;
783 fetch c1 into X_ROWID;
784 if (c1%notfound) then
785 close c1;
786 INSERT_ROW (
787 X_ROWID,
788 X_FACLTY_DEGRD_ID,
789 X_PERSON_ID,
790 X_DEGREE_CD,
791 X_PROGRAM,
792 X_INSTITUTION_CD,
793 X_DEGREE_DATE,
794 X_MODE );
795 return;
796 end if;
797 close c1;
798 UPDATE_ROW (
799 X_ROWID,
800 X_FACLTY_DEGRD_ID,
801 X_PERSON_ID,
802 X_DEGREE_CD,
803 X_PROGRAM,
804 X_INSTITUTION_CD,
805 X_DEGREE_DATE,
806 X_MODE );
807 end ADD_ROW;
808 procedure DELETE_ROW (
809 X_ROWID in VARCHAR2,
810 x_mode IN VARCHAR2
811 ) AS
812 /*************************************************************
813 Created By :ssuri
814 Date Created By :11-MAY-2000
815 Purpose :NEW TABLE
816 Know limitations, enhancements or remarks
817 Change History
818 Who When What
819
820 (reverse chronological order - newest change first)
821 ***************************************************************/
822
823 begin
824 Before_DML (
825 p_action => 'DELETE',
826 x_rowid => X_ROWID
827 );
828 IF (x_mode = 'S') THEN
829 igs_sc_gen_001.set_ctx('R');
830 END IF;
831 delete from IGS_PS_FACLTY_DEGRS
832 where ROWID = X_ROWID;
833 if (sql%notfound) then
834 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
835 igs_ge_msg_stack.add;
836 igs_sc_gen_001.unset_ctx('R');
837 app_exception.raise_exception;
838 end if;
839 IF (x_mode = 'S') THEN
840 igs_sc_gen_001.unset_ctx('R');
841 END IF;
842
843 After_DML (
844 p_action => 'DELETE',
845 x_rowid => X_ROWID
846 );
847 end DELETE_ROW;
848 END igs_ps_faclty_degrs_pkg;
[Home] [Help]