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