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