1 PACKAGE BODY igs_ad_appl_notes_pkg AS
2 /* $Header: IGSAIA6B.pls 120.2 2005/09/21 00:48:43 appldev ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ad_appl_notes%RowType;
5 new_references igs_ad_appl_notes%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_appl_notes_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_note_type_id IN NUMBER DEFAULT NULL,
16 x_ref_notes_id IN NUMBER DEFAULT NULL,
17 x_creation_date IN DATE DEFAULT NULL,
18 x_created_by IN NUMBER DEFAULT NULL,
19 x_last_update_date IN DATE DEFAULT NULL,
20 x_last_updated_by IN NUMBER DEFAULT NULL,
21 x_last_update_login IN NUMBER DEFAULT NULL
22 ) AS
23
24 /*************************************************************
25 Created By :
26 Date Created By :
27 Purpose :
28 Know limitations, enhancements or remarks
29 Change History
30 Who When What
31
32 (reverse chronological order - newest change first)
33 ***************************************************************/
34
35 CURSOR cur_old_ref_values IS
36 SELECT *
37 FROM IGS_AD_APPL_NOTES
38 WHERE rowid = x_rowid;
39
40 BEGIN
41
42 l_rowid := x_rowid;
43
44 -- Code for setting the Old and New Reference Values.
45 -- Populate Old Values.
46 Open cur_old_ref_values;
47 Fetch cur_old_ref_values INTO old_references;
48 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
49 Close cur_old_ref_values;
50 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
51 IGS_GE_MSG_STACK.ADD;
52 App_Exception.Raise_Exception;
53 Return;
54 END IF;
55 Close cur_old_ref_values;
56
57 -- Populate New Values.
58 new_references.appl_notes_id := x_appl_notes_id;
59 new_references.person_id := x_person_id;
60 new_references.admission_appl_number := x_admission_appl_number;
61 new_references.nominated_course_cd := x_nominated_course_cd;
62 new_references.sequence_number := x_sequence_number;
63 new_references.note_type_id := x_note_type_id;
64 new_references.ref_notes_id := x_ref_notes_id;
65 IF (p_action = 'UPDATE') THEN
66 new_references.creation_date := old_references.creation_date;
67 new_references.created_by := old_references.created_by;
68 ELSE
69 new_references.creation_date := x_creation_date;
70 new_references.created_by := x_created_by;
71 END IF;
72 new_references.last_update_date := x_last_update_date;
73 new_references.last_updated_by := x_last_updated_by;
74 new_references.last_update_login := x_last_update_login;
75
76 END Set_Column_Values;
77
78 PROCEDURE Check_Constraints (
79 Column_Name IN VARCHAR2 DEFAULT NULL,
80 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
81 /*************************************************************
82 Created By :
83 Date Created By :
84 Purpose :
85 Know limitations, enhancements or remarks
86 Change History
87 Who When What
88
89 (reverse chronological order - newest change first)
90 ***************************************************************/
91
92 BEGIN
93
94 IF column_name IS NULL THEN
95 NULL;
96 NULL;
97 END IF;
98
99
100
101
102 END Check_Constraints;
103
104 PROCEDURE Check_Uniqueness AS
105 /*************************************************************
106 Created By :
107 Date Created By :
108 Purpose :
109 Know limitations, enhancements or remarks
110 Change History
111 Who When What
112
113 (reverse chronological order - newest change first)
114 ***************************************************************/
115
116 begin
117 IF Get_Uk_For_Validation (
118 new_references.admission_appl_number
119 ,new_references.nominated_course_cd
120 ,new_references.note_type_id
121 ,new_references.person_id
122 ,new_references.sequence_number
123 ) THEN
124 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
125 IGS_GE_MSG_STACK.ADD;
126 app_exception.raise_exception;
127 END IF;
128 END Check_Uniqueness ;
129 PROCEDURE Check_Parent_Existance AS
130 /*************************************************************
131 Created By :
132 Date Created By :
133 Purpose :
134 Know limitations, enhancements or remarks
135 Change History
136 Who When What
137
138 (reverse chronological order - newest change first)
139 ***************************************************************/
140
141 BEGIN
142
143 IF (((old_references.person_id = new_references.person_id) AND
144 (old_references.admission_appl_number = new_references.admission_appl_number) AND
145 (old_references.nominated_course_cd = new_references.nominated_course_cd) AND
146 (old_references.sequence_number = new_references.sequence_number)) OR
147 ((new_references.person_id IS NULL) OR
148 (new_references.admission_appl_number IS NULL) OR
149 (new_references.nominated_course_cd IS NULL) OR
150 (new_references.sequence_number IS NULL))) THEN
151 NULL;
152 ELSIF NOT Igs_Ad_Ps_Appl_Inst_Pkg.Get_PK_For_Validation (
153 new_references.person_id,
154 new_references.admission_appl_number,
155 new_references.nominated_course_cd,
156 new_references.sequence_number
157 ) THEN
158 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
159 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_PROGRAM_APPL'));
160 IGS_GE_MSG_STACK.ADD;
161 App_Exception.Raise_Exception;
162 END IF;
163
164 IF (((old_references.note_type_id = new_references.note_type_id)) OR
165 ((new_references.note_type_id IS NULL))) THEN
166 NULL;
167 ELSIF NOT Igs_Ad_Note_Types_Pkg.Get_UK2_For_Validation (
168 new_references.note_type_id ,
169 NULL,
170 'N'
171 ) THEN
172 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
173 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_NOTE_TYPE'));
174 IGS_GE_MSG_STACK.ADD;
175 App_Exception.Raise_Exception;
176 END IF;
177
178 END Check_Parent_Existance;
179
180 FUNCTION Get_PK_For_Validation (
181 x_appl_notes_id IN NUMBER
182 ) RETURN BOOLEAN AS
183
184 /*************************************************************
185 Created By :
186 Date Created By :
187 Purpose :
188 Know limitations, enhancements or remarks
189 Change History
190 Who When What
191
192 (reverse chronological order - newest change first)
193 ***************************************************************/
194
195 CURSOR cur_rowid IS
196 SELECT rowid
197 FROM igs_ad_appl_notes
198 WHERE appl_notes_id = x_appl_notes_id
199 FOR UPDATE NOWAIT;
200
201 lv_rowid cur_rowid%RowType;
202
203 BEGIN
204
205 Open cur_rowid;
206 Fetch cur_rowid INTO lv_rowid;
207 IF (cur_rowid%FOUND) THEN
208 Close cur_rowid;
209 Return(TRUE);
210 ELSE
211 Close cur_rowid;
212 Return(FALSE);
213 END IF;
214 END Get_PK_For_Validation;
215
216 FUNCTION Get_UK_For_Validation (
217 x_admission_appl_number IN NUMBER,
218 x_nominated_course_cd IN VARCHAR2,
219 x_note_type_id IN NUMBER,
220 x_person_id IN NUMBER,
221 x_sequence_number IN NUMBER
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 ***************************************************************/
234
235 CURSOR cur_rowid IS
236 SELECT rowid
237 FROM igs_ad_appl_notes
238 WHERE admission_appl_number = x_admission_appl_number
239 AND nominated_course_cd = x_nominated_course_cd
240 AND note_type_id = x_note_type_id
241 AND person_id = x_person_id
242 AND sequence_number = x_sequence_number and ((l_rowid is null) or (rowid <> l_rowid))
243
244 ;
245 lv_rowid cur_rowid%RowType;
246
247 BEGIN
248
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_Ps_Appl_Inst (
260 x_person_id IN NUMBER,
261 x_admission_appl_number IN NUMBER,
262 x_nominated_course_cd IN VARCHAR2,
263 x_sequence_number IN NUMBER
264 ) AS
265
266 /*************************************************************
267 Created By :
268 Date Created By :
269 Purpose :
270 Know limitations, enhancements or remarks
271 Change History
272 Who When What
273
274 (reverse chronological order - newest change first)
275 ***************************************************************/
276
277 CURSOR cur_rowid IS
278 SELECT rowid
279 FROM igs_ad_appl_notes
280 WHERE person_id = x_person_id
281 AND admission_appl_number = x_admission_appl_number
282 AND nominated_course_cd = x_nominated_course_cd
283 AND sequence_number = x_sequence_number ;
284
285 lv_rowid cur_rowid%RowType;
286
287 BEGIN
288
289 Open cur_rowid;
290 Fetch cur_rowid INTO lv_rowid;
291 IF (cur_rowid%FOUND) THEN
292 Close cur_rowid;
293 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AAN_ACAI_FK');
294 App_Exception.Raise_Exception;
295 Return;
296 END IF;
297 Close cur_rowid;
298
299 END Get_FK_Igs_Ad_Ps_Appl_Inst;
300
301 PROCEDURE Get_FK_Igs_Ad_Note_Types (
302 x_notes_type_id IN NUMBER
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_appl_notes
319 WHERE note_type_id = x_notes_type_id ;
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_AAN_ANT_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_Ad_Note_Types;
337
338 PROCEDURE Before_DML (
339 p_action IN VARCHAR2,
340 x_rowid IN VARCHAR2 DEFAULT NULL,
341 x_appl_notes_id IN NUMBER DEFAULT NULL,
342 x_person_id IN NUMBER DEFAULT NULL,
343 x_admission_appl_number IN NUMBER DEFAULT NULL,
344 x_nominated_course_cd IN VARCHAR2 DEFAULT NULL,
345 x_sequence_number IN NUMBER DEFAULT NULL,
346 x_note_type_id IN NUMBER DEFAULT NULL,
347 x_ref_notes_id IN NUMBER DEFAULT NULL,
348 x_creation_date IN DATE DEFAULT NULL,
349 x_created_by IN NUMBER DEFAULT NULL,
350 x_last_update_date IN DATE DEFAULT NULL,
351 x_last_updated_by IN NUMBER DEFAULT NULL,
352 x_last_update_login IN NUMBER DEFAULT NULL
353 ) AS
354 /*************************************************************
355 Created By :
356 Date Created By :
357 Purpose :
358 Know limitations, enhancements or remarks
359 Change History
360 Who When What
361
362 (reverse chronological order - newest change first)
363 ***************************************************************/
364
365
366 CURSOR c_inst_status (cp_person_Id igs_ad_ps_appl_inst.Person_Id%TYPE,
370 )
367 cp_Admission_Appl_Number igs_ad_ps_appl_inst.Admission_Appl_Number%TYPE,
368 cp_Nominated_Course_Cd igs_ad_ps_appl_inst.Nominated_Course_Cd%TYPE,
369 cp_Sequence_Number igs_ad_ps_appl_inst.Sequence_Number%TYPE
371 IS
372 SELECT acaiv.appl_inst_status
373 FROM igs_ad_ps_appl_inst acaiv
374 WHERE Person_Id = cp_person_Id AND
375 Admission_Appl_Number = cp_Admission_Appl_Number AND
376 Nominated_Course_Cd = cp_Nominated_Course_Cd AND
377 Sequence_Number = cp_Sequence_Number;
378
379 lv_appl_inst_status igs_ad_ps_appl_inst.appl_inst_status%TYPE;
380
381
382
383
384
385 BEGIN
386
387 Set_Column_Values (
388 p_action,
389 x_rowid,
390 x_appl_notes_id,
391 x_person_id,
392 x_admission_appl_number,
393 x_nominated_course_cd,
394 x_sequence_number,
395 x_note_type_id,
396 x_ref_notes_id,
397 x_creation_date,
398 x_created_by,
399 x_last_update_date,
400 x_last_updated_by,
401 x_last_update_login
402 );
403
404
405 /* Application notes and Decision notes are updateable except when Application Instance Status is withdrawn
406 igs_ad_gen_002.check_adm_appl_inst_stat(
407 x_person_id,
408 x_admission_appl_number,
409 x_nominated_course_cd,
410 x_sequence_number
411 );
412 */
413 -- begin apadegal adtd001 igs.m
414 OPEN c_inst_status( x_person_id,
415 x_admission_appl_number,
416 x_nominated_course_cd,
417 x_sequence_number
418 );
419 FETCH c_inst_status INTO lv_appl_inst_status;
420 CLOSE c_inst_status;
421
422
423 -- applicaiton would have got withdrawn.
424 IF NVL(IGS_AD_GEN_007.ADMP_GET_SAAS(lv_appl_inst_status),'-1') = 'WITHDRAWN' THEN
425 Fnd_Message.Set_name('IGS','IGS_AD_APPL_INST_WITHD');
426 IGS_GE_MSG_STACK.ADD;
427 App_Exception.Raise_Exception;
428 END IF;
429 -- end apadegal adtd001 igs.m
430
431 IF (p_action = 'INSERT') THEN
432 -- Call all the procedures related to Before Insert.
433 Null;
434 IF Get_Pk_For_Validation(
435 new_references.appl_notes_id) THEN
436 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
437 IGS_GE_MSG_STACK.ADD;
438 App_Exception.Raise_Exception;
439 END IF;
440 Check_Uniqueness;
441 Check_Constraints;
442 Check_Parent_Existance;
443 ELSIF (p_action = 'UPDATE') THEN
444 -- Call all the procedures related to Before Update.
445 Null;
446 Check_Uniqueness;
447 Check_Constraints;
448 Check_Parent_Existance;
449 ELSIF (p_action = 'DELETE') THEN
450 -- Call all the procedures related to Before Delete.
451 Null;
452 ELSIF (p_action = 'VALIDATE_INSERT') THEN
453 -- Call all the procedures related to Before Insert.
454 IF Get_PK_For_Validation (
455 new_references.appl_notes_id) THEN
456 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
457 IGS_GE_MSG_STACK.ADD;
458 App_Exception.Raise_Exception;
459 END IF;
460 Check_Uniqueness;
461 Check_Constraints;
462 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
463 Check_Uniqueness;
464 Check_Constraints;
465 ELSIF (p_action = 'VALIDATE_DELETE') THEN
466 Null;
467 END IF;
468
469 END Before_DML;
470
471 PROCEDURE After_DML (
472 p_action IN VARCHAR2,
473 x_rowid IN VARCHAR2
474 ) IS
475 /*************************************************************
476 Created By :
477 Date Created By :
478 Purpose :
479 Know limitations, enhancements or remarks
480 Change History
481 Who When What
482 rboddu 21-jan-2002 added igs_ge_note_pkg.delete_row Bug:2177686
483
484 (reverse chronological order - newest change first)
485 ***************************************************************/
486
487 CURSOR C_NOTE IS
488 SELECT ROWID
489 FROM IGS_GE_NOTE
490 WHERE REFERENCE_NUMBER = (SELECT ref_notes_id FROM igs_ad_appl_notes WHERE rowid = x_rowid);
491
492 BEGIN
493
494 l_rowid := x_rowid;
495
496 IF (p_action = 'INSERT') THEN
497 -- Call all the procedures related to After Insert.
498 Null;
499 ELSIF (p_action = 'UPDATE') THEN
500 -- Call all the procedures related to After Update.
501 Null;
502 ELSIF (p_action = 'DELETE') THEN
503 -- Call all the procedures related to After Delete.
504 FOR C_NOTE_REC IN C_NOTE
505 LOOP
506 IGS_GE_NOTE_pkg.delete_row(C_NOTE_REC.ROWID);
507 END LOOP;
508
509 END IF;
510
511 l_rowid:=NULL;
512 END After_DML;
513
514 procedure INSERT_ROW (
515 X_ROWID in out NOCOPY VARCHAR2,
519 x_NOMINATED_COURSE_CD IN VARCHAR2,
516 x_APPL_NOTES_ID IN OUT NOCOPY NUMBER,
517 x_PERSON_ID IN NUMBER,
518 x_ADMISSION_APPL_NUMBER IN NUMBER,
520 x_SEQUENCE_NUMBER IN NUMBER,
521 x_NOTE_TYPE_ID IN NUMBER,
522 x_REF_NOTES_ID IN OUT NOCOPY NUMBER,
523 X_MODE in VARCHAR2
524 ) AS
525 /*************************************************************
526 Created By :
527 Date Created By :
528 Purpose :
529 Know limitations, enhancements or remarks
530 Change History
531 Who When What
532 ravishar 05/27/05 Security related changes
533
534 (reverse chronological order - newest change first)
535 ***************************************************************/
536
537 cursor C is select ROWID from IGS_AD_APPL_NOTES
538 where APPL_NOTES_ID= X_APPL_NOTES_ID
539 ;
540 X_LAST_UPDATE_DATE DATE ;
541 X_LAST_UPDATED_BY NUMBER ;
542 X_LAST_UPDATE_LOGIN NUMBER ;
543 X_REQUEST_ID NUMBER;
544 X_PROGRAM_ID NUMBER;
545 X_PROGRAM_APPLICATION_ID NUMBER;
546 X_PROGRAM_UPDATE_DATE DATE;
547 l_mode VARCHAR2(1);
548 begin
549 X_LAST_UPDATE_DATE := SYSDATE;
550 l_mode := NVL(x_mode, 'R');
551 if(l_mode = 'I') then
552 X_LAST_UPDATED_BY := 1;
553 X_LAST_UPDATE_LOGIN := 0;
554 elsif (l_mode IN ('R','S')) then
555 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
556 if X_LAST_UPDATED_BY is NULL then
557 X_LAST_UPDATED_BY := -1;
558 end if;
559 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
560 if X_LAST_UPDATE_LOGIN is NULL then
561 X_LAST_UPDATE_LOGIN := -1;
562 end if;
563 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
564 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
565 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
566 if (X_REQUEST_ID = -1) then
567 X_REQUEST_ID := NULL;
568 X_PROGRAM_ID := NULL;
569 X_PROGRAM_APPLICATION_ID := NULL;
570 X_PROGRAM_UPDATE_DATE := NULL;
571 else
572 X_PROGRAM_UPDATE_DATE := SYSDATE;
573 end if;
574 else
575 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
576 IGS_GE_MSG_STACK.ADD;
577 app_exception.raise_exception;
578 end if;
579
580 X_APPL_NOTES_ID := -1;
581 X_REF_NOTES_ID := -1;
582 Before_DML(
583 p_action=>'INSERT',
584 x_rowid=>X_ROWID,
585 x_appl_notes_id=>X_APPL_NOTES_ID,
586 x_person_id=>X_PERSON_ID,
587 x_admission_appl_number=>X_ADMISSION_APPL_NUMBER,
588 x_nominated_course_cd=>X_NOMINATED_COURSE_CD,
589 x_sequence_number=>X_SEQUENCE_NUMBER,
590 x_note_type_id=>X_NOTE_TYPE_ID,
591 x_ref_notes_id=>X_REF_NOTES_ID,
592 x_creation_date=>X_LAST_UPDATE_DATE,
593 x_created_by=>X_LAST_UPDATED_BY,
594 x_last_update_date=>X_LAST_UPDATE_DATE,
595 x_last_updated_by=>X_LAST_UPDATED_BY,
596 x_last_update_login=>X_LAST_UPDATE_LOGIN);
597 IF (x_mode = 'S') THEN
598 igs_sc_gen_001.set_ctx('R');
599 END IF;
600 INSERT INTO IGS_AD_APPL_NOTES (
601 APPL_NOTES_ID
602 ,PERSON_ID
603 ,ADMISSION_APPL_NUMBER
604 ,NOMINATED_COURSE_CD
605 ,SEQUENCE_NUMBER
606 ,NOTE_TYPE_ID
607 ,REF_NOTES_ID
608 ,CREATION_DATE
609 ,CREATED_BY
610 ,LAST_UPDATE_DATE
611 ,LAST_UPDATED_BY
612 ,LAST_UPDATE_LOGIN
613 ,REQUEST_ID
614 ,PROGRAM_ID
615 ,PROGRAM_APPLICATION_ID
616 ,PROGRAM_UPDATE_DATE
617 ) values (
618 IGS_AD_APPL_NOTES_S.NEXTVAL
619 ,NEW_REFERENCES.PERSON_ID
620 ,NEW_REFERENCES.ADMISSION_APPL_NUMBER
621 ,NEW_REFERENCES.NOMINATED_COURSE_CD
622 ,NEW_REFERENCES.SEQUENCE_NUMBER
623 ,NEW_REFERENCES.NOTE_TYPE_ID
624 ,IGS_GE_NOTE_RF_NUM_S.NEXTVAL
625 ,X_LAST_UPDATE_DATE
626 ,X_LAST_UPDATED_BY
627 ,X_LAST_UPDATE_DATE
628 ,X_LAST_UPDATED_BY
629 ,X_LAST_UPDATE_LOGIN
630 ,X_REQUEST_ID
631 ,X_PROGRAM_ID
632 ,X_PROGRAM_APPLICATION_ID
633 ,X_PROGRAM_UPDATE_DATE
634 )RETURNING APPL_NOTES_ID,REF_NOTES_ID INTO X_APPL_NOTES_ID,X_REF_NOTES_ID;
635 IF (x_mode = 'S') THEN
636 igs_sc_gen_001.unset_ctx('R');
637 END IF;
638
639 open c;
640 fetch c into X_ROWID;
641 if (c%notfound) then
642 close c;
643 raise no_data_found;
644 end if;
645 close c;
646 After_DML (
647 p_action => 'INSERT' ,
648 x_rowid => X_ROWID );
649 EXCEPTION
650 WHEN OTHERS THEN
651 IF (x_mode = 'S') THEN
652 igs_sc_gen_001.unset_ctx('R');
653 END IF;
654 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
655 -- Code to handle Security Policy error raised
656 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
657 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
661 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
658 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
659 -- that the ownerof policy function does not have privilege to access.
660 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
662 IGS_GE_MSG_STACK.ADD;
663 app_exception.raise_exception;
664 ELSE
665 RAISE;
666 END IF;
667 END INSERT_ROW;
668 PROCEDURE LOCK_ROW (
669 X_ROWID in VARCHAR2,
670 x_APPL_NOTES_ID IN NUMBER,
671 x_PERSON_ID IN NUMBER,
672 x_ADMISSION_APPL_NUMBER IN NUMBER,
673 x_NOMINATED_COURSE_CD IN VARCHAR2,
674 x_SEQUENCE_NUMBER IN NUMBER,
675 x_NOTE_TYPE_ID IN NUMBER,
676 x_REF_NOTES_ID IN NUMBER ) AS
677 /*************************************************************
678 Created By :
679 Date Created By :
680 Purpose :
681 Know limitations, enhancements or remarks
682 Change History
683 Who When What
684
685 (reverse chronological order - newest change first)
686 ***************************************************************/
687
688 cursor c1 is select
689 PERSON_ID
690 , ADMISSION_APPL_NUMBER
691 , NOMINATED_COURSE_CD
692 , SEQUENCE_NUMBER
693 , NOTE_TYPE_ID
694 , REF_NOTES_ID
695 from IGS_AD_APPL_NOTES
696 where ROWID = X_ROWID
697 for update nowait;
698 tlinfo c1%rowtype;
699 begin
700 open c1;
701 fetch c1 into tlinfo;
702 if (c1%notfound) then
703 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
704 IGS_GE_MSG_STACK.ADD;
705 close c1;
706 app_exception.raise_exception;
707 return;
708 end if;
709 close c1;
710 if ( ( tlinfo.PERSON_ID = X_PERSON_ID)
711 AND (tlinfo.ADMISSION_APPL_NUMBER = X_ADMISSION_APPL_NUMBER)
712 AND (tlinfo.NOMINATED_COURSE_CD = X_NOMINATED_COURSE_CD)
713 AND (tlinfo.SEQUENCE_NUMBER = X_SEQUENCE_NUMBER)
714 AND (tlinfo.NOTE_TYPE_ID = X_NOTE_TYPE_ID)
715 AND (tlinfo.REF_NOTES_ID = X_REF_NOTES_ID)
716 ) then
717 null;
718 else
719 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
720 IGS_GE_MSG_STACK.ADD;
721 app_exception.raise_exception;
722 end if;
723 return;
724 end LOCK_ROW;
725 Procedure UPDATE_ROW (
726 X_ROWID in VARCHAR2,
727 x_APPL_NOTES_ID IN NUMBER,
728 x_PERSON_ID IN NUMBER,
729 x_ADMISSION_APPL_NUMBER IN NUMBER,
730 x_NOMINATED_COURSE_CD IN VARCHAR2,
731 x_SEQUENCE_NUMBER IN NUMBER,
732 x_NOTE_TYPE_ID IN NUMBER,
733 x_REF_NOTES_ID IN NUMBER,
734 X_MODE in VARCHAR2
735 ) AS
736 /*************************************************************
737 Created By :
738 Date Created By :
739 Purpose :
740 Know limitations, enhancements or remarks
741 Change History
742 Who When What
743 ravishar 05/27/05 Security related changes
744
745 (reverse chronological order - newest change first)
746 ***************************************************************/
747
748 X_LAST_UPDATE_DATE DATE ;
749 X_LAST_UPDATED_BY NUMBER ;
750 X_LAST_UPDATE_LOGIN NUMBER ;
751 X_REQUEST_ID NUMBER;
752 X_PROGRAM_ID NUMBER;
753 X_PROGRAM_APPLICATION_ID NUMBER;
754 X_PROGRAM_UPDATE_DATE DATE;
755 l_mode VARCHAR2(1);
756 begin
757 X_LAST_UPDATE_DATE := SYSDATE;
758 l_mode := NVL(x_mode, 'R');
759 if(l_mode = 'I') then
760 X_LAST_UPDATED_BY := 1;
761 X_LAST_UPDATE_LOGIN := 0;
762 elsif (l_mode IN ('R','S')) then
763 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
764 if X_LAST_UPDATED_BY is NULL then
765 X_LAST_UPDATED_BY := -1;
766 end if;
767 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
768 if X_LAST_UPDATE_LOGIN is NULL then
769 X_LAST_UPDATE_LOGIN := -1;
770 end if;
771 else
772 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
773 IGS_GE_MSG_STACK.ADD;
774 app_exception.raise_exception;
775 end if;
776 Before_DML(
777 p_action=>'UPDATE',
778 x_rowid=>X_ROWID,
779 x_appl_notes_id=>X_APPL_NOTES_ID,
780 x_person_id=>X_PERSON_ID,
781 x_admission_appl_number=>X_ADMISSION_APPL_NUMBER,
782 x_nominated_course_cd=>X_NOMINATED_COURSE_CD,
783 x_sequence_number=>X_SEQUENCE_NUMBER,
784 x_note_type_id=>X_NOTE_TYPE_ID,
785 x_ref_notes_id=>X_REF_NOTES_ID,
786 x_creation_date=>X_LAST_UPDATE_DATE,
787 x_created_by=>X_LAST_UPDATED_BY,
788 x_last_update_date=>X_LAST_UPDATE_DATE,
789 x_last_updated_by=>X_LAST_UPDATED_BY,
790 x_last_update_login=>X_LAST_UPDATE_LOGIN);
791
792 if (l_mode IN ('R','S')) then
793 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
794 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
795 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
796 if (X_REQUEST_ID = -1) then
797 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
798 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
799 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
800 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
801 else
802 X_PROGRAM_UPDATE_DATE := SYSDATE;
803 end if;
804 end if;
805
806 IF (x_mode = 'S') THEN
807 igs_sc_gen_001.set_ctx('R');
808 END IF;
809 update IGS_AD_APPL_NOTES set
810 PERSON_ID = NEW_REFERENCES.PERSON_ID,
811 ADMISSION_APPL_NUMBER = NEW_REFERENCES.ADMISSION_APPL_NUMBER,
812 NOMINATED_COURSE_CD = NEW_REFERENCES.NOMINATED_COURSE_CD,
813 SEQUENCE_NUMBER = NEW_REFERENCES.SEQUENCE_NUMBER,
814 NOTE_TYPE_ID = NEW_REFERENCES.NOTE_TYPE_ID,
815 REF_NOTES_ID = NEW_REFERENCES.REF_NOTES_ID,
816 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
817 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
818 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
819 , REQUEST_ID = X_REQUEST_ID,
820 PROGRAM_ID = X_PROGRAM_ID,
821 PROGRAM_APPLICATION_ID = PROGRAM_APPLICATION_ID,
822 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
823 where ROWID = X_ROWID;
824 IF (sql%notfound) THEN
825 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
826 igs_ge_msg_stack.add;
827 IF (x_mode = 'S') THEN
828 igs_sc_gen_001.set_ctx('R');
829 END IF;
830 app_exception.raise_exception;
831 END IF;
832 IF (x_mode = 'S') THEN
833 igs_sc_gen_001.unset_ctx('R');
834 END IF;
835
836
837 After_DML (
838 p_action => 'UPDATE' ,
839 x_rowid => X_ROWID
840 );
841 EXCEPTION
842 WHEN OTHERS THEN
843 IF (x_mode = 'S') THEN
844 igs_sc_gen_001.unset_ctx('R');
845 END IF;
846 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
847 -- Code to handle Security Policy error raised
848 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
849 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
850 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
851 -- that the ownerof policy function does not have privilege to access.
852 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
853 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
854 IGS_GE_MSG_STACK.ADD;
855 app_exception.raise_exception;
856 ELSE
857 RAISE;
858 END IF;
859 END UPDATE_ROW;
860 PROCEDURE ADD_ROW (
861 X_ROWID in out NOCOPY VARCHAR2,
862 x_APPL_NOTES_ID IN OUT NOCOPY NUMBER,
863 x_PERSON_ID IN NUMBER,
864 x_ADMISSION_APPL_NUMBER IN NUMBER,
865 x_NOMINATED_COURSE_CD IN VARCHAR2,
866 x_SEQUENCE_NUMBER IN NUMBER,
867 x_NOTE_TYPE_ID IN NUMBER,
868 x_REF_NOTES_ID IN OUT NOCOPY NUMBER,
869 X_MODE in VARCHAR2
870 ) AS
871 /*************************************************************
872 Created By :
873 Date Created By :
874 Purpose :
875 Know limitations, enhancements or remarks
876 Change History
877 Who When What
878
879 (reverse chronological order - newest change first)
880 ***************************************************************/
881
882 cursor c1 is select ROWID from IGS_AD_APPL_NOTES
883 where APPL_NOTES_ID= X_APPL_NOTES_ID
884 ;
885 l_mode VARCHAR2(1);
886 begin
887 l_mode := NVL(x_mode, 'R');
888 open c1;
889 fetch c1 into X_ROWID;
890 if (c1%notfound) then
891 close c1;
892 INSERT_ROW (
893 X_ROWID,
894 X_APPL_NOTES_ID,
895 X_PERSON_ID,
896 X_ADMISSION_APPL_NUMBER,
897 X_NOMINATED_COURSE_CD,
898 X_SEQUENCE_NUMBER,
899 X_NOTE_TYPE_ID,
900 X_REF_NOTES_ID,
901 X_MODE );
902 return;
903 end if;
904 close c1;
905 UPDATE_ROW (
906 X_ROWID,
907 X_APPL_NOTES_ID,
908 X_PERSON_ID,
909 X_ADMISSION_APPL_NUMBER,
910 X_NOMINATED_COURSE_CD,
911 X_SEQUENCE_NUMBER,
912 X_NOTE_TYPE_ID,
913 X_REF_NOTES_ID,
914 l_mode );
915 end ADD_ROW;
916 procedure DELETE_ROW (
917 X_ROWID in VARCHAR2,
918 x_mode IN VARCHAR2
919 ) AS
920 /*************************************************************
921 Created By :
922 Date Created By :
923 Purpose :
924 Know limitations, enhancements or remarks
925 Change History
926 Who When What
927 ravishar 05/27/05 Security related changes
928
929 (reverse chronological order - newest change first)
930 ***************************************************************/
931
932 begin
933 Before_DML (
934 p_action => 'DELETE',
935 x_rowid => X_ROWID
936 );
937 IF (x_mode = 'S') THEN
938 igs_sc_gen_001.set_ctx('R');
939 END IF;
940 DELETE FROM IGS_AD_APPL_NOTES
941 WHERE ROWID = X_ROWID;
942 IF (sql%notfound) THEN
943 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
944 igs_ge_msg_stack.add;
945 igs_sc_gen_001.unset_ctx('R');
946 app_exception.raise_exception;
947 END IF;
948 IF (x_mode = 'S') THEN
949 igs_sc_gen_001.unset_ctx('R');
950 END IF;
951
952 After_DML (
953 p_action => 'DELETE',
954 x_rowid => X_ROWID
955 );
956 EXCEPTION
957 WHEN OTHERS THEN
958 IF (x_mode = 'S') THEN
959 igs_sc_gen_001.unset_ctx('R');
960 END IF;
961 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
962 -- Code to handle Security Policy error raised
963 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
964 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
965 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
966 -- that the ownerof policy function does not have privilege to access.
967 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
968 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
969 IGS_GE_MSG_STACK.ADD;
970 app_exception.raise_exception;
971 ELSE
972 RAISE;
973 END IF;
974 end DELETE_ROW;
975
976 END igs_ad_appl_notes_pkg;
[Home] [Help]