1 PACKAGE BODY igs_ad_spl_talents_pkg AS
2 /* $Header: IGSAI97B.pls 120.3 2005/10/03 08:21:07 appldev ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ad_spl_talents%RowType;
5 new_references igs_ad_spl_talents%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_spl_talent_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_talent_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_TALENTS
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_talent_id := x_spl_talent_id;
56 new_references.person_id := x_person_id;
57 new_references.admission_appl_number := x_admission_appl_number;
58 new_references.special_talent_type_id := x_special_talent_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_talent_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.special_talent_type_id = new_references.special_talent_type_id)) OR
136 ((new_references.special_talent_type_id IS NULL))) THEN
137 NULL;
138 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_UK2_For_Validation (
139 new_references.special_talent_type_id ,
140 'SPECIAL_TALENTS',
141 'N'
142 ) THEN
143 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
144 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_SPL_TALENT_TYPE'));
145 IGS_GE_MSG_STACK.ADD;
146 App_Exception.Raise_Exception;
147 END IF;
148
149 IF (((old_references.person_id = new_references.person_id) AND
150 (old_references.admission_appl_number = new_references.admission_appl_number)) OR
151 ((new_references.person_id IS NULL) OR
152 (new_references.admission_appl_number IS NULL))) THEN
153 NULL;
154 ELSIF NOT Igs_Ad_Appl_Pkg.Get_PK_For_Validation (
155 new_references.person_id,
156 new_references.admission_appl_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_APPL'));
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_talent_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_talents
184 WHERE spl_talent_id = x_spl_talent_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_talent_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_talents
222 WHERE special_talent_type_id = x_special_talent_type_id
223 AND admission_appl_number = x_admission_appl_number
224 AND person_id = x_person_id and ((l_rowid is null) or (rowid <> l_rowid))
225
226 ;
227 lv_rowid cur_rowid%RowType;
228
229 BEGIN
230
231 Open cur_rowid;
232 Fetch cur_rowid INTO lv_rowid;
233 IF (cur_rowid%FOUND) THEN
234 Close cur_rowid;
235 return (true);
236 ELSE
237 close cur_rowid;
238 return(false);
239 END IF;
240 END Get_UK_For_Validation ;
241 PROCEDURE Get_FK_Igs_Ad_Code_Classes (
242 x_code_id 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_talents
259 WHERE special_talent_type_id = x_code_id ;
260
261 lv_rowid cur_rowid%RowType;
262
263 BEGIN
264
265 Open cur_rowid;
266 Fetch cur_rowid INTO lv_rowid;
267 IF (cur_rowid%FOUND) THEN
268 Close cur_rowid;
269 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ASPLT_ACDC_FK');
270 IGS_GE_MSG_STACK.ADD;
271 App_Exception.Raise_Exception;
272 Return;
273 END IF;
274 Close cur_rowid;
275
276 END Get_FK_Igs_Ad_Code_Classes;
277
278 PROCEDURE Get_FK_Igs_Ad_Appl (
279 x_person_id IN NUMBER,
280 x_admission_appl_number 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_talents
297 WHERE person_id = x_person_id
298 AND admission_appl_number = x_admission_appl_number;
299
300 lv_rowid cur_rowid%RowType;
301
302 BEGIN
303
304 Open cur_rowid;
305 Fetch cur_rowid INTO lv_rowid;
306 IF (cur_rowid%FOUND) THEN
307 Close cur_rowid;
308 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ASPLT_ACAI_FK');
309 IGS_GE_MSG_STACK.ADD;
310 App_Exception.Raise_Exception;
311 Return;
312 END IF;
313 Close cur_rowid;
314
315 END Get_FK_Igs_Ad_Appl;
316
317 PROCEDURE Before_DML (
318 p_action IN VARCHAR2,
319 x_rowid IN VARCHAR2 DEFAULT NULL,
320 x_spl_talent_id IN NUMBER DEFAULT NULL,
321 x_person_id IN NUMBER DEFAULT NULL,
322 x_admission_appl_number IN NUMBER DEFAULT NULL,
323 x_special_talent_type_id IN NUMBER DEFAULT NULL,
324 x_creation_date IN DATE DEFAULT NULL,
325 x_created_by IN NUMBER DEFAULT NULL,
326 x_last_update_date IN DATE DEFAULT NULL,
327 x_last_updated_by IN NUMBER DEFAULT NULL,
328 x_last_update_login IN NUMBER DEFAULT NULL
329 ) AS
330 /*************************************************************
331 Created By :
332 Date Created By :
333 Purpose :
334 Know limitations, enhancements or remarks
335 Change History
336 Who When What
337
338 (reverse chronological order - newest change first)
339 ***************************************************************/
340
341 BEGIN
342
343 Set_Column_Values (
344 p_action,
345 x_rowid,
346 x_spl_talent_id,
347 x_person_id,
348 x_admission_appl_number,
349 x_special_talent_type_id,
350 x_creation_date,
351 x_created_by,
352 x_last_update_date,
353 x_last_updated_by,
354 x_last_update_login
355 );
356
357 igs_ad_gen_002.check_adm_appl_inst_stat(
358 nvl(x_person_id,old_references.person_id),
359 nvl(x_admission_appl_number,old_references.admission_appl_number),
360 NULL,
361 NULL,
362 'Y' -- proceed phase - apadegal adtd001 igs.m
363 );
364
365 IF (p_action = 'INSERT') THEN
366 -- Call all the procedures related to Before Insert.
367 Null;
368 IF Get_Pk_For_Validation(
369 new_references.spl_talent_id) THEN
370 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
371 IGS_GE_MSG_STACK.ADD;
372 App_Exception.Raise_Exception;
373 END IF;
374 Check_Uniqueness;
375 Check_Constraints;
376 Check_Parent_Existance;
377 ELSIF (p_action = 'UPDATE') THEN
378 -- Call all the procedures related to Before Update.
379 Null;
380 Check_Uniqueness;
381 Check_Constraints;
382 Check_Parent_Existance;
383 ELSIF (p_action = 'DELETE') THEN
384 -- Call all the procedures related to Before Delete.
385 Null;
386 ELSIF (p_action = 'VALIDATE_INSERT') THEN
387 -- Call all the procedures related to Before Insert.
388 IF Get_PK_For_Validation (
389 new_references.spl_talent_id) THEN
390 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
391 IGS_GE_MSG_STACK.ADD;
392 App_Exception.Raise_Exception;
393 END IF;
394 Check_Uniqueness;
395 Check_Constraints;
396 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
397 Check_Uniqueness;
398 Check_Constraints;
399 ELSIF (p_action = 'VALIDATE_DELETE') THEN
400 Null;
401 END IF;
402 l_rowid := NULL;
403
404 END Before_DML;
405
406 PROCEDURE After_DML (
407 p_action IN VARCHAR2,
408 x_rowid IN VARCHAR2
409 ) IS
410 /*************************************************************
411 Created By :
412 Date Created By :
413 Purpose :
414 Know limitations, enhancements or remarks
415 Change History
416 Who When What
417
418 (reverse chronological order - newest change first)
419 ***************************************************************/
420
421 BEGIN
422
423 l_rowid := x_rowid;
424
425 IF (p_action = 'INSERT') THEN
426 -- Call all the procedures related to After Insert.
427 Null;
428 ELSIF (p_action = 'UPDATE') THEN
429 -- Call all the procedures related to After Update.
430 Null;
431 ELSIF (p_action = 'DELETE') THEN
432 -- Call all the procedures related to After Delete.
433 Null;
434 END IF;
435
436 l_rowid:=NULL;
437 END After_DML;
438
439 procedure INSERT_ROW (
440 X_ROWID in out NOCOPY VARCHAR2,
441 x_SPL_TALENT_ID IN OUT NOCOPY NUMBER,
442 x_PERSON_ID IN NUMBER,
443 x_ADMISSION_APPL_NUMBER IN NUMBER,
444 x_SPECIAL_TALENT_TYPE_ID IN NUMBER,
445 X_MODE in VARCHAR2
446 ) AS
447 /*************************************************************
448 Created By :
449 Date Created By :
450 Purpose :
451 Know limitations, enhancements or remarks
452 Change History
453 Who When What
454 ravishar 05/27/05 Security related changes
455
456 (reverse chronological order - newest change first)
457 ***************************************************************/
458
459 cursor C is select ROWID from IGS_AD_SPL_TALENTS
460 where SPL_TALENT_ID= X_SPL_TALENT_ID
461 ;
462 X_LAST_UPDATE_DATE DATE ;
463 X_LAST_UPDATED_BY NUMBER ;
464 X_LAST_UPDATE_LOGIN NUMBER ;
465 X_REQUEST_ID NUMBER;
466 X_PROGRAM_ID NUMBER;
467 X_PROGRAM_APPLICATION_ID NUMBER;
468 X_PROGRAM_UPDATE_DATE DATE;
469 begin
470 X_LAST_UPDATE_DATE := SYSDATE;
471 if(X_MODE = 'I') then
472 X_LAST_UPDATED_BY := 1;
473 X_LAST_UPDATE_LOGIN := 0;
474 elsif (X_MODE IN ('R', 'S')) then
475 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
476 if X_LAST_UPDATED_BY is NULL then
477 X_LAST_UPDATED_BY := -1;
478 end if;
479 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
480 if X_LAST_UPDATE_LOGIN is NULL then
481 X_LAST_UPDATE_LOGIN := -1;
482 end if;
483 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
484 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
485 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
486 if (X_REQUEST_ID = -1) then
487 X_REQUEST_ID := NULL;
488 X_PROGRAM_ID := NULL;
489 X_PROGRAM_APPLICATION_ID := NULL;
490 X_PROGRAM_UPDATE_DATE := NULL;
491 else
492 X_PROGRAM_UPDATE_DATE := SYSDATE;
493 end if;
494 else
495 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
496 IGS_GE_MSG_STACK.ADD;
497 app_exception.raise_exception;
498 end if;
499
500 X_SPL_TALENT_ID := -1;
501 Before_DML(
502 p_action=>'INSERT',
503 x_rowid=>X_ROWID,
504 x_spl_talent_id=>X_SPL_TALENT_ID,
505 x_person_id=>X_PERSON_ID,
506 x_admission_appl_number=>X_ADMISSION_APPL_NUMBER,
507 x_special_talent_type_id=>X_SPECIAL_TALENT_TYPE_ID,
508 x_creation_date=>X_LAST_UPDATE_DATE,
509 x_created_by=>X_LAST_UPDATED_BY,
510 x_last_update_date=>X_LAST_UPDATE_DATE,
511 x_last_updated_by=>X_LAST_UPDATED_BY,
512 x_last_update_login=>X_LAST_UPDATE_LOGIN);
513 IF (x_mode = 'S') THEN
514 igs_sc_gen_001.set_ctx('R');
515 END IF;
516 INSERT INTO IGS_AD_SPL_TALENTS (
517 SPL_TALENT_ID
518 ,PERSON_ID
519 ,ADMISSION_APPL_NUMBER
520 ,SPECIAL_TALENT_TYPE_ID
521 ,CREATION_DATE
522 ,CREATED_BY
523 ,LAST_UPDATE_DATE
524 ,LAST_UPDATED_BY
525 ,LAST_UPDATE_LOGIN
526 ,REQUEST_ID
527 ,PROGRAM_ID
528 ,PROGRAM_APPLICATION_ID
529 ,PROGRAM_UPDATE_DATE
530 ) values (
531 IGS_AD_SPL_TALENTS_S.NEXTVAL
532 ,NEW_REFERENCES.PERSON_ID
533 ,NEW_REFERENCES.ADMISSION_APPL_NUMBER
534 ,NEW_REFERENCES.SPECIAL_TALENT_TYPE_ID
535 ,X_LAST_UPDATE_DATE
536 ,X_LAST_UPDATED_BY
537 ,X_LAST_UPDATE_DATE
538 ,X_LAST_UPDATED_BY
539 ,X_LAST_UPDATE_LOGIN
540 ,X_REQUEST_ID
541 ,X_PROGRAM_ID
542 ,X_PROGRAM_APPLICATION_ID
543 ,X_PROGRAM_UPDATE_DATE
544 ) RETURNING SPL_TALENT_ID INTO X_SPL_TALENT_ID;
545 IF (x_mode = 'S') THEN
546 igs_sc_gen_001.unset_ctx('R');
547 END IF;
548
549 open c;
550 fetch c into X_ROWID;
551 if (c%notfound) then
552 close c;
553 raise no_data_found;
554 end if;
555 close c;
556 After_DML (
557 p_action => 'INSERT' ,
558 x_rowid => X_ROWID );
559 EXCEPTION
560 WHEN OTHERS THEN
561 IF (x_mode = 'S') THEN
562 igs_sc_gen_001.unset_ctx('R');
563 END IF;
564 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
565 -- Code to handle Security Policy error raised
566 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
567 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
568 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
569 -- that the ownerof policy function does not have privilege to access.
570 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
571 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
572 IGS_GE_MSG_STACK.ADD;
573 app_exception.raise_exception;
574 ELSE
575 RAISE;
576 END IF;
577 END INSERT_ROW;
578 PROCEDURE LOCK_ROW (
579 X_ROWID in VARCHAR2,
580 x_SPL_TALENT_ID IN NUMBER,
581 x_PERSON_ID IN NUMBER,
582 x_ADMISSION_APPL_NUMBER IN NUMBER,
583 x_SPECIAL_TALENT_TYPE_ID IN NUMBER ) 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_TALENT_TYPE_ID
599 from IGS_AD_SPL_TALENTS
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_TALENT_TYPE_ID = X_SPECIAL_TALENT_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_TALENT_ID IN NUMBER,
629 x_PERSON_ID IN NUMBER,
630 x_ADMISSION_APPL_NUMBER IN NUMBER,
631 x_SPECIAL_TALENT_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_talent_id=>X_SPL_TALENT_ID,
676 x_person_id=>X_PERSON_ID,
677 x_admission_appl_number=>X_ADMISSION_APPL_NUMBER,
678 x_special_talent_type_id=>X_SPECIAL_TALENT_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_TALENTS set
703 PERSON_ID = NEW_REFERENCES.PERSON_ID,
704 ADMISSION_APPL_NUMBER = NEW_REFERENCES.ADMISSION_APPL_NUMBER,
705 SPECIAL_TALENT_TYPE_ID = NEW_REFERENCES.SPECIAL_TALENT_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 igs_sc_gen_001.unset_ctx('R');
718 app_exception.raise_exception;
719 end if;
720 IF (x_mode = 'S') THEN
721 igs_sc_gen_001.unset_ctx('R');
722 END IF;
723
724
725 After_DML (
726 p_action => 'UPDATE' ,
727 x_rowid => X_ROWID
728 );
729 EXCEPTION
730 WHEN OTHERS THEN
731 IF (x_mode = 'S') THEN
732 igs_sc_gen_001.unset_ctx('R');
733 END IF;
734 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
735 -- Code to handle Security Policy error raised
736 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
737 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
738 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
739 -- that the ownerof policy function does not have privilege to access.
740 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
741 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
742 IGS_GE_MSG_STACK.ADD;
743 app_exception.raise_exception;
744 ELSE
745 RAISE;
746 END IF;
747 END UPDATE_ROW;
748 PROCEDURE ADD_ROW (
749 X_ROWID in out NOCOPY VARCHAR2,
750 x_SPL_TALENT_ID IN OUT NOCOPY NUMBER,
751 x_PERSON_ID IN NUMBER,
752 x_ADMISSION_APPL_NUMBER IN NUMBER,
753 x_SPECIAL_TALENT_TYPE_ID IN NUMBER,
754 X_MODE in VARCHAR2
755 ) AS
756 /*************************************************************
757 Created By :
758 Date Created By :
759 Purpose :
760 Know limitations, enhancements or remarks
761 Change History
762 Who When What
763
764 (reverse chronological order - newest change first)
765 ***************************************************************/
766
767 cursor c1 is select ROWID from IGS_AD_SPL_TALENTS
768 where SPL_TALENT_ID= X_SPL_TALENT_ID
769 ;
770 begin
771 open c1;
772 fetch c1 into X_ROWID;
773 if (c1%notfound) then
774 close c1;
775 INSERT_ROW (
776 X_ROWID,
777 X_SPL_TALENT_ID,
778 X_PERSON_ID,
779 X_ADMISSION_APPL_NUMBER,
780 X_SPECIAL_TALENT_TYPE_ID,
781 X_MODE );
782 return;
783 end if;
784 close c1;
785 UPDATE_ROW (
786 X_ROWID,
787 X_SPL_TALENT_ID,
788 X_PERSON_ID,
789 X_ADMISSION_APPL_NUMBER,
790 X_SPECIAL_TALENT_TYPE_ID,
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 05/27/05 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_TALENTS
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 igs_sc_gen_001.unset_ctx('R');
823 app_exception.raise_exception;
824 END IF;
825 IF (x_mode = 'S') THEN
826 igs_sc_gen_001.unset_ctx('R');
827 END IF;
828
829 After_DML (
830 p_action => 'DELETE',
831 x_rowid => X_ROWID
832 );
833 EXCEPTION
834 WHEN OTHERS THEN
835 IF (x_mode = 'S') THEN
836 igs_sc_gen_001.unset_ctx('R');
837 END IF;
838 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
839 -- Code to handle Security Policy error raised
840 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
841 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
842 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
843 -- that the ownerof policy function does not have privilege to access.
844 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
845 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
846 IGS_GE_MSG_STACK.ADD;
847 app_exception.raise_exception;
848 ELSE
849 RAISE;
850 END IF;
851 END DELETE_ROW;
852
853 END igs_ad_spl_talents_pkg;
[Home] [Help]