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