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