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