1 PACKAGE BODY igs_ad_appl_perstat_pkg AS
2 /* $Header: IGSAIG3B.pls 120.3 2005/10/03 08:24:03 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_ad_appl_perstat%ROWTYPE;
6 new_references igs_ad_appl_perstat%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_appl_perstat_id IN NUMBER DEFAULT NULL,
12 x_person_id IN NUMBER DEFAULT NULL,
13 x_admission_appl_number IN NUMBER DEFAULT NULL,
14 x_persl_stat_type IN VARCHAR2 DEFAULT NULL,
15 x_date_received IN DATE 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 || Created By : vdixit
24 || Created On : 21-DEC-2001
25 || Purpose : Initialises the Old and New references for the columns of the table.
26 || Known limitations, enhancements or remarks :
27 || Change History :
28 || Who When What
29 || (reverse chronological order - newest change first)
30 */
31
32 CURSOR cur_old_ref_values IS
33 SELECT *
34 FROM igs_ad_appl_perstat
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.appl_perstat_id := x_appl_perstat_id;
56 new_references.person_id := x_person_id;
57 new_references.admission_appl_number := x_admission_appl_number;
58 new_references.persl_stat_type := x_persl_stat_type;
59 new_references.date_received := TRUNC(x_date_received);
60
61 IF (p_action = 'UPDATE') THEN
62 new_references.creation_date := old_references.creation_date;
63 new_references.created_by := old_references.created_by;
64 ELSE
65 new_references.creation_date := x_creation_date;
66 new_references.created_by := x_created_by;
67 END IF;
68
69 new_references.last_update_date := x_last_update_date;
70 new_references.last_updated_by := x_last_updated_by;
71 new_references.last_update_login := x_last_update_login;
72
73 END set_column_values;
74
75 PROCEDURE BeforeRowInsertUpdate(
76 p_inserting IN BOOLEAN DEFAULT FALSE,
77 p_updating IN BOOLEAN DEFAULT FALSE,
78 p_deleting IN BOOLEAN DEFAULT FALSE
79 ) as
80 CURSOR c_birth_date(p_person_id igs_pe_person_base_v.person_id%TYPE) IS
81 SELECT birth_date
82 FROM igs_pe_person_base_v
83 WHERE person_id =p_person_id ;
84
85 l_birth_date igs_pe_person_base_v.birth_date%TYPE;
86
87 BEGIN
88 OPEN c_birth_date(new_references.person_id);
89 FETCH c_birth_date INTO l_birth_date;
90 CLOSE c_birth_date;
91
92 IF ((l_birth_date IS NOT NULL) AND (l_birth_date > new_references.date_received)) THEN
93 FND_MESSAGE.SET_NAME('IGS','IGS_AD_DOB_ERROR');
94 FND_MESSAGE.SET_TOKEN ('NAME',fnd_message.get_string('IGS', 'IGS_AD_DT_RECV'));
95 IGS_GE_MSG_STACK.ADD;
96 APP_EXCEPTION.RAISE_EXCEPTION;
97 END IF;
98
99 IF (new_references.date_received > SYSDATE) THEN
100 FND_MESSAGE.SET_NAME('IGS','IGS_AD_DATE_SYSDATE');
101 FND_MESSAGE.SET_TOKEN ('NAME',fnd_message.get_string('IGS', 'IGS_AD_DT_RECV'));
102 IGS_GE_MSG_STACK.ADD;
103 APP_EXCEPTION.RAISE_EXCEPTION;
104 END IF;
105 END BeforeRowInsertUpdate;
106
107 PROCEDURE check_parent_existance AS
108 /*
109 || Created By : vdixit
110 || Created On : 21-DEC-2001
111 || Purpose : Checks for the existance of Parent records.
112 || Known limitations, enhancements or remarks :
113 || Change History :
114 || Who When What
115 || (reverse chronological order - newest change first)
116 */
117 BEGIN
118
119 IF (((old_references.persl_stat_type = new_references.persl_stat_type)) OR
120 ((new_references.persl_stat_type IS NULL))) THEN
121 NULL;
122 ELSIF NOT igs_ad_per_stm_typ_pkg.get_pk_for_validation (
123 new_references.persl_stat_type ,
124 'N'
125 ) THEN
126 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
127 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_CHILD_PER_STAT'));
128 IGS_GE_MSG_STACK.ADD;
129 App_Exception.Raise_Exception;
130 END IF;
131
132 END check_parent_existance;
133
134 FUNCTION get_pk_for_validation (
135 x_appl_perstat_id IN NUMBER,
136 x_person_id IN NUMBER,
137 x_admission_appl_number IN NUMBER
138 ) RETURN BOOLEAN AS
139 /*
140 || Created By :
141 || Created On : 21-DEC-2001
142 || Purpose : Validates the Primary Key of the table.
143 || Known limitations, enhancements or remarks :
144 || Change History :
145 || Who When What
146 || (reverse chronological order - newest change first)
147 */
148 CURSOR cur_rowid IS
149 SELECT rowid
150 FROM igs_ad_appl_perstat
151 WHERE appl_perstat_id = x_appl_perstat_id AND
152 person_id = x_person_id AND
153 admission_appl_number = x_admission_appl_number
154 FOR UPDATE NOWAIT;
155
156 lv_rowid cur_rowid%RowType;
157
158 BEGIN
159
160 OPEN cur_rowid;
161 FETCH cur_rowid INTO lv_rowid;
162 IF (cur_rowid%FOUND) THEN
163 CLOSE cur_rowid;
164 RETURN(TRUE);
165 ELSE
166 CLOSE cur_rowid;
167 RETURN(FALSE);
168 END IF;
169
170 END get_pk_for_validation;
171
172
173 PROCEDURE get_fk_igs_ad_per_stm_typ (
174 x_persl_stat_type IN VARCHAR2
175 ) AS
176 /*
177 || Created By : vdixit
178 || Created On : 21-DEC-2001
179 || Purpose : Validates the Foreign Keys for the table.
180 || Known limitations, enhancements or remarks :
181 || Change History :
182 || Who When What
183 || (reverse chronological order - newest change first)
184 */
185 CURSOR cur_rowid IS
186 SELECT rowid
187 FROM igs_ad_appl_perstat
188 WHERE ((persl_stat_type = x_persl_stat_type));
189
190 lv_rowid cur_rowid%RowType;
191
192 BEGIN
193
194 OPEN cur_rowid;
195 FETCH cur_rowid INTO lv_rowid;
196 IF (cur_rowid%FOUND) THEN
197 CLOSE cur_rowid;
198 fnd_message.set_name ('IGS', 'IGS_AD_APS_ST_FK');
199 igs_ge_msg_stack.add;
200 app_exception.raise_exception;
201 RETURN;
202 END IF;
203 CLOSE cur_rowid;
204
205 END get_fk_igs_ad_per_stm_typ;
206
207
208 PROCEDURE before_dml (
209 p_action IN VARCHAR2,
210 x_rowid IN VARCHAR2 DEFAULT NULL,
211 x_appl_perstat_id IN NUMBER DEFAULT NULL,
212 x_person_id IN NUMBER DEFAULT NULL,
213 x_admission_appl_number IN NUMBER DEFAULT NULL,
214 x_persl_stat_type IN VARCHAR2 DEFAULT NULL,
215 x_date_received IN DATE DEFAULT NULL,
216 x_creation_date IN DATE DEFAULT NULL,
217 x_created_by IN NUMBER DEFAULT NULL,
218 x_last_update_date IN DATE DEFAULT NULL,
219 x_last_updated_by IN NUMBER DEFAULT NULL,
220 x_last_update_login IN NUMBER DEFAULT NULL
221 ) AS
222 /*
223 || Created By : vdixit
224 || Created On : 21-DEC-2001
225 || Purpose : Initialises the columns, Checks Constraints, Calls the
226 || Trigger Handlers for the table, before any DML operation.
227 || Known limitations, enhancements or remarks :
228 || Change History :
229 || Who When What
230 || (reverse chronological order - newest change first)
231 */
232
233 BEGIN
234 set_column_values (
235 p_action,
236 x_rowid,
237 x_appl_perstat_id,
238 x_person_id,
239 x_admission_appl_number,
240 x_persl_stat_type,
241 x_date_received,
242 x_creation_date,
243 x_created_by,
244 x_last_update_date,
245 x_last_updated_by,
246 x_last_update_login
247 );
248
249 igs_ad_gen_002.check_adm_appl_inst_stat(
250 nvl(x_person_id,old_references.person_id),
251 nvl(x_admission_appl_number,old_references.admission_appl_number),
252 NULL,
253 NULL,
254 'Y' -- proceed phase - apadegal adtd001 igs.m
255 );
256
257 IF (p_action = 'INSERT') THEN
258 -- Call all the procedures related to Before Insert.
259 BeforeRowInsertUpdate ( p_inserting => TRUE );
260 IF ( get_pk_for_validation( new_references.appl_perstat_id,
261 new_references.person_id,
262 new_references.admission_appl_number )
263 ) THEN
264 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
265 igs_ge_msg_stack.add;
266 app_exception.raise_exception;
267 END IF;
268 check_parent_existance;
269 ELSIF (p_action = 'UPDATE') THEN
270 -- Call all the procedures related to Before Update.
271 BeforeRowInsertUpdate ( p_updating => TRUE );
272 check_parent_existance;
273 ELSIF (p_action = 'VALIDATE_INSERT') THEN
274 -- Call all the procedures related to Before Insert.
275 IF ( get_pk_for_validation ( new_references.appl_perstat_id,
276 new_references.person_id,
277 new_references.admission_appl_number
278 )
279 ) THEN
280 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
281 igs_ge_msg_stack.add;
282 app_exception.raise_exception;
283 END IF;
284 END IF;
285
286 END before_dml;
287
288
289 PROCEDURE insert_row (
290 x_rowid IN OUT NOCOPY VARCHAR2,
291 x_appl_perstat_id IN OUT NOCOPY NUMBER,
292 x_person_id IN NUMBER,
293 x_admission_appl_number IN NUMBER,
294 x_persl_stat_type IN VARCHAR2,
295 x_date_received IN DATE,
296 x_mode IN VARCHAR2
297 ) AS
298 /*
299 || Created By : vdixit
300 || Created On : 21-DEC-2001
301 || Purpose : Handles the INSERT DML logic for the table.
302 || Known limitations, enhancements or remarks :
303 || Change History :
304 || Who When What
305 || ravishar 05/27/05 Security related changes
306 || (reverse chronological order - newest change first)
307 */
308 CURSOR c IS
309 SELECT rowid
310 FROM igs_ad_appl_perstat
311 WHERE appl_perstat_id = x_appl_perstat_id;
312
313 x_last_update_date DATE;
314 x_last_updated_by NUMBER;
315 x_last_update_login NUMBER;
316 x_request_id NUMBER;
317 x_program_id NUMBER;
318 x_program_application_id NUMBER;
319 x_program_update_date DATE;
320
321 BEGIN
322
323 x_last_update_date := SYSDATE;
324 IF (x_mode = 'I') THEN
325 x_last_updated_by := 1;
326 x_last_update_login := 0;
327 ELSIF (X_MODE IN ('R', 'S')) THEN
328 x_last_updated_by := fnd_global.user_id;
329 IF (x_last_updated_by IS NULL) THEN
330 x_last_updated_by := -1;
331 END IF;
332 x_last_update_login := fnd_global.login_id;
333 IF (x_last_update_login IS NULL) THEN
334 x_last_update_login := -1;
335 END IF;
336 x_request_id := fnd_global.conc_request_id;
337 x_program_id := fnd_global.conc_program_id;
338 x_program_application_id := fnd_global.prog_appl_id;
339
340 IF (x_request_id = -1) THEN
341 x_request_id := NULL;
342 x_program_id := NULL;
343 x_program_application_id := NULL;
344 x_program_update_date := NULL;
345 ELSE
346 x_program_update_date := SYSDATE;
347 END IF;
348 ELSE
349 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
350 igs_ge_msg_stack.add;
351 app_exception.raise_exception;
355 before_dml(
352 END IF;
353
354 x_appl_perstat_id := -1;
356 p_action => 'INSERT',
357 x_rowid => x_rowid,
358 x_appl_perstat_id => x_appl_perstat_id,
359 x_person_id => x_person_id,
360 x_admission_appl_number => x_admission_appl_number,
361 x_persl_stat_type => x_persl_stat_type,
362 x_date_received => x_date_received,
363 x_creation_date => x_last_update_date,
364 x_created_by => x_last_updated_by,
365 x_last_update_date => x_last_update_date,
366 x_last_updated_by => x_last_updated_by,
367 x_last_update_login => x_last_update_login
368 );
369
370 IF (x_mode = 'S') THEN
371 igs_sc_gen_001.set_ctx('R');
372 END IF;
373 INSERT INTO igs_ad_appl_perstat (
374 appl_perstat_id,
375 person_id,
376 admission_appl_number,
377 persl_stat_type,
378 date_received,
379 creation_date,
380 created_by,
381 last_update_date,
382 last_updated_by,
383 last_update_login,
384 request_id,
385 program_id,
386 program_application_id,
387 program_update_date
388 ) VALUES (
389 igs_ad_appl_perstat_s.NEXTVAL,
390 new_references.person_id,
391 new_references.admission_appl_number,
392 new_references.persl_stat_type,
393 new_references.date_received,
394 x_last_update_date,
395 x_last_updated_by,
396 x_last_update_date,
397 x_last_updated_by,
398 x_last_update_login ,
399 x_request_id,
400 x_program_id,
401 x_program_application_id,
402 x_program_update_date
403 ) RETURNING appl_perstat_id INTO x_appl_perstat_id;
404 IF (x_mode = 'S') THEN
405 igs_sc_gen_001.unset_ctx('R');
406 END IF;
407
408
409 OPEN c;
410 FETCH c INTO x_rowid;
411 IF (c%NOTFOUND) THEN
412 CLOSE c;
413 RAISE NO_DATA_FOUND;
414 END IF;
415 CLOSE c;
416
417 EXCEPTION
418 WHEN OTHERS THEN
419 IF (x_mode = 'S') THEN
420 igs_sc_gen_001.unset_ctx('R');
421 END IF;
422
423 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
424 -- Code to handle Security Policy error raised
425 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
426 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
427 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
428 -- that the ownerof policy function does not have privilege to access.
429 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
430 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
431 IGS_GE_MSG_STACK.ADD;
432 app_exception.raise_exception;
433 ELSE
434 RAISE;
435 END IF;
436 END insert_row;
437
438
439 PROCEDURE lock_row (
440 x_rowid IN VARCHAR2,
441 x_appl_perstat_id IN NUMBER,
442 x_person_id IN NUMBER,
443 x_admission_appl_number IN NUMBER,
444 x_persl_stat_type IN VARCHAR2,
445 x_date_received IN DATE
446 ) AS
447 /*
448 || Created By : vdixit
449 || Created On : 21-DEC-2001
450 || Purpose : Handles the LOCK mechanism for the table.
451 || Known limitations, enhancements or remarks :
452 || Change History :
453 || Who When What
454 || (reverse chronological order - newest change first)
455 */
456 CURSOR c1 IS
457 SELECT
458 appl_perstat_id,
459 person_id,
460 admission_appl_number,
461 persl_stat_type,
462 date_received
463 FROM igs_ad_appl_perstat
464 WHERE rowid = x_rowid
465 FOR UPDATE NOWAIT;
466
467 tlinfo c1%ROWTYPE;
468
469 BEGIN
470
471 OPEN c1;
472 FETCH c1 INTO tlinfo;
473 IF (c1%notfound) THEN
474 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
475 igs_ge_msg_stack.add;
476 CLOSE c1;
477 app_exception.raise_exception;
478 RETURN;
479 END IF;
480 CLOSE c1;
481
482 IF (
483 (tlinfo.appl_perstat_id = x_appl_perstat_id)
484 AND (tlinfo.person_id = x_person_id)
485 AND (tlinfo.admission_appl_number = x_admission_appl_number)
486 AND (tlinfo.persl_stat_type = x_persl_stat_type)
487 AND (TRUNC(tlinfo.date_received) = TRUNC(x_date_received) )
488 ) THEN
489 NULL;
490 ELSE
491 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
492 igs_ge_msg_stack.add;
493 app_exception.raise_exception;
494 END IF;
495
499
496 RETURN;
497
498 END lock_row;
500
501 PROCEDURE update_row (
502 x_rowid IN VARCHAR2,
503 x_appl_perstat_id IN NUMBER,
504 x_person_id IN NUMBER,
505 x_admission_appl_number IN NUMBER,
506 x_persl_stat_type IN VARCHAR2,
507 x_date_received IN DATE,
508 x_mode IN VARCHAR2
509 ) AS
510 /*
511 || Created By : vdixit
512 || Created On : 21-DEC-2001
513 || Purpose : Handles the UPDATE DML logic for the table.
514 || Known limitations, enhancements or remarks :
515 || Change History :
516 || Who When What
517 || ravishar 05/27/05 Security related changes
518 || (reverse chronological order - newest change first)
519 */
520 x_last_update_date DATE ;
521 x_last_updated_by NUMBER;
522 x_last_update_login NUMBER;
523 x_request_id NUMBER;
524 x_program_id NUMBER;
525 x_program_application_id NUMBER;
526 x_program_update_date DATE;
527
528 BEGIN
529
530 x_last_update_date := SYSDATE;
531 IF (X_MODE = 'I') THEN
532 x_last_updated_by := 1;
533 x_last_update_login := 0;
534 ELSIF (X_MODE IN ('R', 'S')) THEN
535 x_last_updated_by := fnd_global.user_id;
536 IF x_last_updated_by IS NULL THEN
537 x_last_updated_by := -1;
538 END IF;
539 x_last_update_login := fnd_global.login_id;
540 IF (x_last_update_login IS NULL) THEN
541 x_last_update_login := -1;
542 END IF;
543 ELSE
544 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
545 igs_ge_msg_stack.add;
546 app_exception.raise_exception;
547 END IF;
548
549 before_dml(
550 p_action => 'UPDATE',
551 x_rowid => x_rowid,
552 x_appl_perstat_id => x_appl_perstat_id,
553 x_person_id => x_person_id,
554 x_admission_appl_number => x_admission_appl_number,
555 x_persl_stat_type => x_persl_stat_type,
556 x_date_received => x_date_received,
557 x_creation_date => x_last_update_date,
558 x_created_by => x_last_updated_by,
559 x_last_update_date => x_last_update_date,
560 x_last_updated_by => x_last_updated_by,
561 x_last_update_login => x_last_update_login
562 );
563
564 IF (X_MODE IN ('R', 'S')) THEN
565 x_request_id := fnd_global.conc_request_id;
566 x_program_id := fnd_global.conc_program_id;
567 x_program_application_id := fnd_global.prog_appl_id;
568 IF (x_request_id = -1) THEN
569 x_request_id := old_references.request_id;
570 x_program_id := old_references.program_id;
571 x_program_application_id := old_references.program_application_id;
572 x_program_update_date := old_references.program_update_date;
573 ELSE
574 x_program_update_date := SYSDATE;
575 END IF;
576 END IF;
577
578 IF (x_mode = 'S') THEN
579 igs_sc_gen_001.set_ctx('R');
580 END IF;
581 UPDATE igs_ad_appl_perstat
582 SET
583 appl_perstat_id = new_references.appl_perstat_id,
584 person_id = new_references.person_id,
585 admission_appl_number = new_references.admission_appl_number,
586 persl_stat_type = new_references.persl_stat_type,
587 date_received = new_references.date_received,
588 last_update_date = x_last_update_date,
589 last_updated_by = x_last_updated_by,
590 last_update_login = x_last_update_login ,
591 request_id = x_request_id,
592 program_id = x_program_id,
593 program_application_id = x_program_application_id,
594 program_update_date = x_program_update_date
595 WHERE rowid = x_rowid;
596
597 IF (SQL%NOTFOUND) THEN
598 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
599 igs_ge_msg_stack.add;
600 IF (x_mode = 'S') THEN
601 igs_sc_gen_001.unset_ctx('R');
602 END IF;
603 app_exception.raise_exception;
604 END IF;
605 IF (x_mode = 'S') THEN
606 igs_sc_gen_001.unset_ctx('R');
607 END IF;
608
609
610 EXCEPTION
611 WHEN OTHERS THEN
612 IF (x_mode = 'S') THEN
613 igs_sc_gen_001.unset_ctx('R');
614 END IF;
615 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
616 -- Code to handle Security Policy error raised
617 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
618 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
619 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
620 -- that the ownerof policy function does not have privilege to access.
621 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
622 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
623 IGS_GE_MSG_STACK.ADD;
624 app_exception.raise_exception;
625 ELSE
626 RAISE;
627 END IF;
628 END update_row;
629
630
631 PROCEDURE add_row (
632 x_rowid IN OUT NOCOPY VARCHAR2,
633 x_appl_perstat_id IN OUT NOCOPY NUMBER,
634 x_person_id IN NUMBER,
635 x_admission_appl_number IN NUMBER,
636 x_persl_stat_type IN VARCHAR2,
637 x_date_received IN DATE,
638 x_mode IN VARCHAR2
639 ) AS
640 /*
641 || Created By : vdixit
642 || Created On : 21-DEC-2001
643 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
644 || Known limitations, enhancements or remarks :
645 || Change History :
646 || Who When What
647 || (reverse chronological order - newest change first)
648 */
649 CURSOR c1 IS
650 SELECT rowid
651 FROM igs_ad_appl_perstat
652 WHERE appl_perstat_id = x_appl_perstat_id;
653
654 BEGIN
655
656 OPEN c1;
657 FETCH c1 INTO x_rowid;
658 IF (c1%NOTFOUND) THEN
659 CLOSE c1;
660
661 insert_row (
662 x_rowid,
663 x_appl_perstat_id,
664 x_person_id,
665 x_admission_appl_number,
666 x_persl_stat_type,
667 x_date_received,
668 x_mode
669 );
670 RETURN;
671 END IF;
672 CLOSE c1;
673
674 update_row (
675 x_rowid,
676 x_appl_perstat_id,
677 x_person_id,
678 x_admission_appl_number,
679 x_persl_stat_type,
680 x_date_received,
681 x_mode
682 );
683
684 END add_row;
685
686
687 PROCEDURE delete_row (
688 x_rowid IN VARCHAR2,
689 x_mode IN VARCHAR2
690 ) AS
691 /*
692 || Created By : vdixit
693 || Created On : 21-DEC-2001
694 || Purpose : Handles the DELETE DML logic for the table.
695 || Known limitations, enhancements or remarks :
696 || Change History :
697 || Who When What
698 || ravishar 05/27/05 Security related changes
699 || (reverse chronological order - newest change first)
700 */
701 BEGIN
702
703 before_dml (
704 p_action => 'DELETE',
705 x_rowid => x_rowid
706 );
707
708 IF (x_mode = 'S') THEN
709 igs_sc_gen_001.set_ctx('R');
710 END IF;
711 DELETE FROM igs_ad_appl_perstat
712 WHERE rowid = x_rowid;
713
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.set_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 EXCEPTION
728 WHEN OTHERS THEN
729 IF (x_mode = 'S') THEN
730 igs_sc_gen_001.set_ctx('R');
731 END IF;
732 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
733 -- Code to handle Security Policy error raised
734 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
735 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
736 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
737 -- that the ownerof policy function does not have privilege to access.
738 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
739 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
740 IGS_GE_MSG_STACK.ADD;
741 app_exception.raise_exception;
742 ELSE
743 RAISE;
744 END IF;
745 END delete_row;
746
747
748 END igs_ad_appl_perstat_pkg;
[Home] [Help]