1 PACKAGE BODY igs_ad_aplins_admreq_pkg AS
2 /* $Header: IGSAIE6B.pls 120.3 2005/10/03 08:23:34 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_ad_aplins_admreq%ROWTYPE;
6 new_references igs_ad_aplins_admreq%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_aplins_admreq_id IN NUMBER ,
12 x_person_id IN NUMBER ,
13 x_admission_appl_number IN NUMBER ,
14 x_course_cd IN VARCHAR2 ,
15 x_sequence_number IN NUMBER ,
16 x_tracking_id IN NUMBER ,
17 x_creation_date IN DATE ,
18 x_created_by IN NUMBER ,
19 x_last_update_date IN DATE ,
20 x_last_updated_by IN NUMBER ,
21 x_last_update_login IN NUMBER
22 ) AS
23 /*
24 || Created By : [email protected]
25 || Created On : 22-JUL-2001
26 || Purpose : Initialises the Old and New references for the columns of the table.
27 || Known limitations, enhancements or remarks :
28 || Change History :
29 || Who When What
30 || (reverse chronological order - newest change first)
31 */
32
33 CURSOR cur_old_ref_values IS
34 SELECT *
35 FROM IGS_AD_APLINS_ADMREQ
36 WHERE rowid = x_rowid;
37
38 BEGIN
39
40 l_rowid := x_rowid;
41
42 -- Code for setting the Old and New Reference Values.
43 -- Populate Old Values.
44 OPEN cur_old_ref_values;
45 FETCH cur_old_ref_values INTO old_references;
46 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
47 CLOSE cur_old_ref_values;
48 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
49 igs_ge_msg_stack.add;
50 app_exception.raise_exception;
51 RETURN;
52 END IF;
53 CLOSE cur_old_ref_values;
54
55 -- Populate New Values.
56 new_references.aplins_admreq_id := x_aplins_admreq_id;
57 new_references.person_id := x_person_id;
58 new_references.admission_appl_number := x_admission_appl_number;
59 new_references.course_cd := x_course_cd;
60 new_references.sequence_number := x_sequence_number;
61 new_references.tracking_id := x_tracking_id;
62
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
71 new_references.last_update_date := x_last_update_date;
72 new_references.last_updated_by := x_last_updated_by;
73 new_references.last_update_login := x_last_update_login;
74
75 END set_column_values;
76
77
78 PROCEDURE check_uniqueness AS
79 /*
80 || Created By : [email protected]
81 || Created On : 22-JUL-2001
82 || Purpose : Handles the Unique Constraint logic defined for the columns.
83 || Known limitations, enhancements or remarks :
84 || Change History :
85 || Who When What
86 || (reverse chronological order - newest change first)
87 */
88 BEGIN
89
90 IF ( get_uk_for_validation (
91 new_references.admission_appl_number,
92 new_references.course_cd,
93 new_references.person_id,
94 new_references.sequence_number,
95 new_references.tracking_id
96 )
97 ) THEN
98 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
99 igs_ge_msg_stack.add;
100 app_exception.raise_exception;
101 END IF;
102
103 END check_uniqueness;
104
105
106 PROCEDURE check_parent_existance AS
107 /*
108 || Created By : [email protected]
109 || Created On : 22-JUL-2001
110 || Purpose : Checks for the existance of Parent records.
111 || Known limitations, enhancements or remarks :
112 || Change History :
113 || Who When What
114 || (reverse chronological order - newest change first)
115 */
116 BEGIN
117
118 IF (((old_references.person_id = new_references.person_id) AND
119 (old_references.admission_appl_number = new_references.admission_appl_number) AND
120 (old_references.course_cd = new_references.course_cd) AND
121 (old_references.sequence_number = new_references.sequence_number)) OR
122 ((new_references.person_id IS NULL) OR
123 (new_references.admission_appl_number IS NULL) OR
124 (new_references.course_cd IS NULL) OR
125 (new_references.sequence_number IS NULL))) THEN
126 NULL;
127 ELSIF NOT Igs_Ad_Ps_Appl_Inst_Pkg.Get_PKNolock_For_Validation ( -- changed the function call from Igs_Ad_Ps_Appl_Inst_Pkg.Get_PK_For_Validation to
128 new_references.person_id, -- Igs_Ad_Ps_Appl_Inst_Pkg.Get_PKNolock_For_Validation (For Bug 2760811 - ADCR061
129 new_references.admission_appl_number, -- locking issues -- rghosh )
130 new_references.course_cd,
131 new_references.sequence_number
132 ) THEN
133 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
134 igs_ge_msg_stack.add;
135 app_exception.raise_exception;
136 END IF;
137
138 IF (((old_references.tracking_id = new_references.tracking_id)) OR
139 ((new_references.tracking_id IS NULL))) THEN
140 NULL;
141 ELSIF NOT igs_tr_item_pkg.get_pk_for_validation (
142 new_references.tracking_id
143 ) THEN
144 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
145 igs_ge_msg_stack.add;
146 app_exception.raise_exception;
147 END IF;
148
149 END check_parent_existance;
150
151
152 FUNCTION get_pk_for_validation (
153 x_aplins_admreq_id IN NUMBER
154 ) RETURN BOOLEAN AS
155 /*
156 || Created By : [email protected]
157 || Created On : 22-JUL-2001
158 || Purpose : Validates the Primary Key of the table.
159 || Known limitations, enhancements or remarks :
160 || Change History :
161 || Who When What
162 || (reverse chronological order - newest change first)
163 */
164 CURSOR cur_rowid IS
165 SELECT rowid
166 FROM igs_ad_aplins_admreq
167 WHERE aplins_admreq_id = x_aplins_admreq_id
168 FOR UPDATE NOWAIT;
169
170 lv_rowid cur_rowid%RowType;
171
172 BEGIN
173
174 OPEN cur_rowid;
175 FETCH cur_rowid INTO lv_rowid;
176 IF (cur_rowid%FOUND) THEN
177 CLOSE cur_rowid;
178 RETURN(TRUE);
179 ELSE
180 CLOSE cur_rowid;
181 RETURN(FALSE);
182 END IF;
183
184 END get_pk_for_validation;
185
186
187 FUNCTION get_uk_for_validation (
188 x_admission_appl_number IN NUMBER,
189 x_course_cd IN VARCHAR2,
190 x_person_id IN NUMBER,
191 x_sequence_number IN NUMBER,
192 x_tracking_id IN NUMBER
193 ) RETURN BOOLEAN AS
194 /*
195 || Created By : [email protected]
196 || Created On : 22-JUL-2001
197 || Purpose : Validates the Unique Keys of the table.
198 || Known limitations, enhancements or remarks :
199 || Change History :
200 || Who When What
201 || (reverse chronological order - newest change first)
202 */
203 CURSOR cur_rowid IS
204 SELECT rowid
205 FROM igs_ad_aplins_admreq
206 WHERE admission_appl_number = x_admission_appl_number
207 AND course_cd = x_course_cd
208 AND person_id = x_person_id
209 AND sequence_number = x_sequence_number
210 AND tracking_id = x_tracking_id
211 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
212
213 lv_rowid cur_rowid%RowType;
214
215 BEGIN
216
217 OPEN cur_rowid;
218 FETCH cur_rowid INTO lv_rowid;
219 IF (cur_rowid%FOUND) THEN
220 CLOSE cur_rowid;
221 RETURN (true);
222 ELSE
223 CLOSE cur_rowid;
224 RETURN(FALSE);
225 END IF;
226
227 END get_uk_for_validation ;
228
229
230 PROCEDURE get_fk_igs_ad_ps_appl_inst_all (
231 x_person_id IN NUMBER,
232 x_admission_appl_number IN NUMBER,
233 x_nominated_course_cd IN VARCHAR2,
234 x_sequence_number IN NUMBER
235 ) AS
236 /*
237 || Created By : [email protected]
238 || Created On : 22-JUL-2001
239 || Purpose : Validates the Foreign Keys for the table.
240 || Known limitations, enhancements or remarks :
241 || Change History :
242 || Who When What
243 || rghosh 08-nov-2002 changed the message name in the
244 || procedure get_fk_igs_ad_ps_appl_inst_all (bug #2619603)
245 || (reverse chronological order - newest change first)
246 */
247 CURSOR cur_rowid IS
248 SELECT rowid
249 FROM igs_ad_aplins_admreq
250 WHERE ((admission_appl_number = x_admission_appl_number) AND
251 (course_cd = x_nominated_course_cd) AND
252 (person_id = x_person_id) AND
253 (sequence_number = x_sequence_number));
254
255 lv_rowid cur_rowid%RowType;
256
257 BEGIN
258
259 OPEN cur_rowid;
260 FETCH cur_rowid INTO lv_rowid;
261 IF (cur_rowid%FOUND) THEN
262 CLOSE cur_rowid;
263 --changed the message name -rghosh (bug #2619603)
264 fnd_message.set_name ('IGS', 'IGS_AD_APLINS_ACAI_FK');
265 igs_ge_msg_stack.add;
266 app_exception.raise_exception;
267 RETURN;
268 END IF;
269 CLOSE cur_rowid;
270
271 END get_fk_igs_ad_ps_appl_inst_all;
272
273
274 PROCEDURE get_fk_igs_tr_item_all (
275 x_tracking_id IN NUMBER
276 ) AS
277 /*
278 || Created By : [email protected]
279 || Created On : 22-JUL-2001
280 || Purpose : Validates the Foreign Keys for the table.
281 || Known limitations, enhancements or remarks :
282 || Change History :
283 || Who When What
284 || (reverse chronological order - newest change first)
285 */
286 CURSOR cur_rowid IS
287 SELECT rowid
288 FROM igs_ad_aplins_admreq
289 WHERE ((tracking_id = x_tracking_id));
290
291 lv_rowid cur_rowid%RowType;
292
293 BEGIN
294
295 OPEN cur_rowid;
296 FETCH cur_rowid INTO lv_rowid;
297 IF (cur_rowid%FOUND) THEN
298 CLOSE cur_rowid;
299 fnd_message.set_name ('IGS', 'IGS_FOREIGN_KEY_REFERENCE');
300 igs_ge_msg_stack.add;
301 app_exception.raise_exception;
302 RETURN;
303 END IF;
304 CLOSE cur_rowid;
305
306 END get_fk_igs_tr_item_all;
307
308 PROCEDURE check_appl_compl_stat (
309 x_tracking_id IN NUMBER,
310 x_person_id IN NUMBER,
311 x_admission_appl_number IN NUMBER,
312 x_course_cd IN VARCHAR2 ,
313 x_sequence_number IN NUMBER
314 ) IS
315 /*************************************************************
316 Created By : rghosh
317 Date Created By : 20-Feb-2003
318 Purpose : When a record is getting created , the requirements do not get added after the
319 application completion status is set to SATISFIED for an application instance
320 Know limitations, enhancements or remarks
321 Change History
322 Who When What
323
324 (reverse chronological order - newest change first)
325 ***************************************************************/
326 CURSOR c_get_adm_doc_status (
327 p_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
328 p_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
329 p_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
330 p_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE ) IS
331 SELECT 'X'
332 FROM igs_ad_ps_appl_inst_all
333 WHERE person_id = p_person_id
334 AND admission_appl_number = p_admission_appl_number
335 AND nominated_course_cd = p_nominated_course_cd
336 AND sequence_number = p_sequence_number
337 AND IGS_AD_GEN_007.ADMP_GET_SADS (adm_doc_status) = 'SATISFIED';
338
339 CURSOR c_not_post_adm (cp_tracking_id igs_tr_item.tracking_id%TYPE) IS
340 SELECT 'X'
341 FROM igs_tr_item a,
342 igs_tr_type b
343 WHERE a.tracking_id = cp_tracking_id
344 AND b.tracking_type = a.tracking_type
345 AND b.S_TRACKING_TYPE <> 'POST_ADMISSION' ;
346
347 l_get_adm_doc_status VARCHAR2(1);
348 l_not_post_adm VARCHAR2(1);
349
350 BEGIN
351
352 OPEN c_get_adm_doc_status (
353 x_person_id,
354 x_admission_appl_number,
355 x_course_cd,
356 x_sequence_number );
357 FETCH c_get_adm_doc_status INTO l_get_adm_doc_status;
358 OPEN c_not_post_adm (x_tracking_id) ;
359 FETCH c_not_post_adm INTO l_not_post_adm ;
360 IF c_get_adm_doc_status % FOUND AND c_not_post_adm%FOUND THEN
361 Fnd_Message.Set_name('IGS','IGS_AD_NOT_INST_UPD_REQ_DOC');
362 IGS_GE_MSG_STACK.ADD;
363 CLOSE c_get_adm_doc_status;
364 CLOSE c_not_post_adm;
365 App_Exception.Raise_Exception;
366 END IF;
367 CLOSE c_get_adm_doc_status;
368 CLOSE c_not_post_adm;
369
370 END check_appl_compl_stat;
371
372
373 PROCEDURE before_dml (
374 p_action IN VARCHAR2,
375 x_rowid IN VARCHAR2 ,
376 x_aplins_admreq_id IN NUMBER ,
377 x_person_id IN NUMBER ,
378 x_admission_appl_number IN NUMBER ,
379 x_course_cd IN VARCHAR2 ,
380 x_sequence_number IN NUMBER ,
381 x_tracking_id IN NUMBER ,
382 x_creation_date IN DATE ,
383 x_created_by IN NUMBER ,
384 x_last_update_date IN DATE ,
385 x_last_updated_by IN NUMBER ,
386 x_last_update_login IN NUMBER
387 ) AS
388 /*
389 || Created By : [email protected]
390 || Created On : 22-JUL-2001
391 || Purpose : Initialises the columns, Checks Constraints, Calls the
392 || Trigger Handlers for the table, before any DML operation.
396 || (reverse chronological order - newest change first)
393 || Known limitations, enhancements or remarks :
394 || Change History :
395 || Who When What
397 || apadegal 2-09-2005 changed teh call for check_adm_appl_inst_stat for IGS.M
398 */
399
400 --begin apadegal adtd001 igs.m
401 -- cursor to find the System Tracking type of the given Tracking item
402 CURSOR c_track_type (cp_tracking_id igs_tr_item.tracking_id%TYPE) IS
403 SELECT ttype.S_TRACKING_TYPE
404 FROM igs_tr_item titem, igs_tr_type ttype
405 WHERE titem.tracking_id = cp_tracking_id AND
406 titem.tracking_type = ttype.tracking_type;
407
408 lv_tracking_type IGS_TR_TYPE.S_TRACKING_TYPE%TYPE;
409
410 --end apadegal adtd001 igs.m
411 BEGIN
412
413 set_column_values (
414 p_action,
415 x_rowid,
416 x_aplins_admreq_id,
417 x_person_id,
418 x_admission_appl_number,
419 x_course_cd,
420 x_sequence_number,
421 x_tracking_id,
422 x_creation_date,
423 x_created_by,
424 x_last_update_date,
425 x_last_updated_by,
426 x_last_update_login
427 );
428
429
430 OPEN c_track_type(x_tracking_id);
431 FETCH c_track_type INTO lv_tracking_type;
432 CLOSE c_track_type;
433
434 -- BEGIN APADEGAL adtd001 igs.m
435
436 IF lv_tracking_type ='ADM_PROCESSING'
437 THEN
438 igs_ad_gen_002.check_adm_appl_inst_stat(
439 nvl(x_person_id,old_references.person_id),
440 nvl(x_admission_appl_number,old_references.admission_appl_number),
441 nvl(x_course_cd,old_references.course_cd),
442 nvl(x_sequence_number,old_references.sequence_number),
443 'N' -- reconsider phase, (cannot assign admission processing requirements in proceed phase);
444 );
445 END IF;
446 IF lv_tracking_type ='POST_ADMISSION'
447 THEN
448 igs_ad_gen_002.check_adm_appl_inst_stat(
449 nvl(x_person_id,old_references.person_id),
450 nvl(x_admission_appl_number,old_references.admission_appl_number),
451 nvl(x_course_cd,old_references.course_cd),
452 nvl(x_sequence_number,old_references.sequence_number),
453 'Y' -- proceed phase, (cann assign post admission requirements in proceed phase);
454 );
455 END IF;
456
457 -- END APADEGAL adtd001 igs.m
458 IF (p_action = 'INSERT') THEN
459 -- Call all the procedures related to Before Insert.
460 IF ( get_pk_for_validation(
461 new_references.aplins_admreq_id
462 )
463 ) THEN
464 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
465 igs_ge_msg_stack.add;
466 app_exception.raise_exception;
467 END IF;
468 check_uniqueness;
469 check_parent_existance;
470 check_appl_compl_stat (
471 new_references.tracking_id,
472 new_references.person_id,
473 new_references.admission_appl_number,
474 new_references.course_cd,
475 new_references.sequence_number );
476 ELSIF (p_action = 'UPDATE') THEN
477 -- Call all the procedures related to Before Update.
478 check_uniqueness;
479 check_parent_existance;
480 check_appl_compl_stat (
481 new_references.tracking_id,
482 new_references.person_id,
483 new_references.admission_appl_number,
484 new_references.course_cd,
485 new_references.sequence_number );
486 ELSIF (p_action = 'VALIDATE_INSERT') THEN
487 -- Call all the procedures related to Before Insert.
488 IF ( get_pk_for_validation (
489 new_references.aplins_admreq_id
490 )
491 ) THEN
492 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
493 igs_ge_msg_stack.add;
494 app_exception.raise_exception;
495 END IF;
496 check_uniqueness;
497 check_appl_compl_stat (
498 new_references.tracking_id,
499 new_references.person_id,
500 new_references.admission_appl_number,
501 new_references.course_cd,
502 new_references.sequence_number );
503 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
504 check_uniqueness;
505 check_appl_compl_stat (
506 new_references.tracking_id,
507 new_references.person_id,
508 new_references.admission_appl_number,
509 new_references.course_cd,
510 new_references.sequence_number );
511 END IF;
512 l_rowid := NULL; --Bug:2863832
513 END before_dml;
514
515
516 PROCEDURE insert_row (
517 x_rowid IN OUT NOCOPY VARCHAR2,
518 x_aplins_admreq_id IN OUT NOCOPY NUMBER,
519 x_person_id IN NUMBER,
520 x_admission_appl_number IN NUMBER,
521 x_course_cd IN VARCHAR2,
522 x_sequence_number IN NUMBER,
526 /*
523 x_tracking_id IN NUMBER,
524 x_mode IN VARCHAR2
525 ) AS
527 || Created By : [email protected]
528 || Created On : 22-JUL-2001
529 || Purpose : Handles the INSERT DML logic for the table.
530 || Known limitations, enhancements or remarks :
531 || Change History :
532 || Who When What
533 || (reverse chronological order - newest change first)
534 || ravishar 05/30/05 Security related changes
535 */
536 CURSOR c IS
537 SELECT rowid
538 FROM igs_ad_aplins_admreq
539 WHERE aplins_admreq_id = x_aplins_admreq_id;
540
541 x_last_update_date DATE;
542 x_last_updated_by NUMBER;
543 x_last_update_login NUMBER;
544 x_request_id NUMBER;
545 x_program_id NUMBER;
546 x_program_application_id NUMBER;
547 x_program_update_date DATE;
548
549 BEGIN
550
551 x_last_update_date := SYSDATE;
552 IF (x_mode = 'I') THEN
553 x_last_updated_by := 1;
554 x_last_update_login := 0;
555 ELSIF (X_MODE IN ('R', 'S')) THEN
556 x_last_updated_by := fnd_global.user_id;
557 IF (x_last_updated_by IS NULL) THEN
558 x_last_updated_by := -1;
559 END IF;
560 x_last_update_login := fnd_global.login_id;
561 IF (x_last_update_login IS NULL) THEN
562 x_last_update_login := -1;
563 END IF;
564 x_request_id := fnd_global.conc_request_id;
565 x_program_id := fnd_global.conc_program_id;
566 x_program_application_id := fnd_global.prog_appl_id;
567
568 IF (x_request_id = -1) THEN
569 x_request_id := NULL;
570 x_program_id := NULL;
571 x_program_application_id := NULL;
572 x_program_update_date := NULL;
573 ELSE
574 x_program_update_date := SYSDATE;
575 END IF;
576 ELSE
577 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
578 igs_ge_msg_stack.add;
579 app_exception.raise_exception;
580 END IF;
581
582 x_aplins_admreq_id := -1;
583 before_dml(
584 p_action => 'INSERT',
585 x_rowid => x_rowid,
586 x_aplins_admreq_id => x_aplins_admreq_id,
587 x_person_id => x_person_id,
588 x_admission_appl_number => x_admission_appl_number,
589 x_course_cd => x_course_cd,
590 x_sequence_number => x_sequence_number,
591 x_tracking_id => x_tracking_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 );
598
599 IF (x_mode = 'S') THEN
600 igs_sc_gen_001.set_ctx('R');
601 END IF;
602 INSERT INTO igs_ad_aplins_admreq (
603 aplins_admreq_id,
604 person_id,
605 admission_appl_number,
606 course_cd,
607 sequence_number,
608 tracking_id,
609 creation_date,
610 created_by,
611 last_update_date,
612 last_updated_by,
613 last_update_login,
614 request_id,
615 program_id,
616 program_application_id,
617 program_update_date
618 ) VALUES (
619 igs_ad_aplins_admreq_s.NEXTVAL,
620 new_references.person_id,
621 new_references.admission_appl_number,
622 new_references.course_cd,
623 new_references.sequence_number,
624 new_references.tracking_id,
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 aplins_admreq_id INTO x_aplins_admreq_id;
635 IF (x_mode = 'S') THEN
636 igs_sc_gen_001.unset_ctx('R');
637 END IF;
638
639
640 OPEN c;
641 FETCH c INTO x_rowid;
642 IF (c%NOTFOUND) THEN
643 CLOSE c;
644 RAISE NO_DATA_FOUND;
645 END IF;
646 CLOSE c;
647
648 EXCEPTION
649 WHEN OTHERS THEN
650 IF (x_mode = 'S') THEN
651 igs_sc_gen_001.unset_ctx('R');
652 END IF;
653 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
654 -- Code to handle Security Policy error raised
655 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
656 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
657 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
661 IGS_GE_MSG_STACK.ADD;
658 -- that the ownerof policy function does not have privilege to access.
659 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
660 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
662 app_exception.raise_exception;
663 ELSE
664 RAISE;
665 END IF;
666 END insert_row;
667
668
669 PROCEDURE lock_row (
670 x_rowid IN VARCHAR2,
671 x_aplins_admreq_id IN NUMBER,
672 x_person_id IN NUMBER,
673 x_admission_appl_number IN NUMBER,
674 x_course_cd IN VARCHAR2,
675 x_sequence_number IN NUMBER,
676 x_tracking_id IN NUMBER
677 ) AS
678 /*
679 || Created By : [email protected]
680 || Created On : 22-JUL-2001
681 || Purpose : Handles the LOCK mechanism for the table.
682 || Known limitations, enhancements or remarks :
683 || Change History :
684 || Who When What
685 || (reverse chronological order - newest change first)
686 */
687 CURSOR c1 IS
688 SELECT
689 person_id,
690 admission_appl_number,
691 course_cd,
692 sequence_number,
693 tracking_id
694 FROM igs_ad_aplins_admreq
695 WHERE rowid = x_rowid
696 FOR UPDATE NOWAIT;
697
698 tlinfo c1%ROWTYPE;
699
700 BEGIN
701
702 OPEN c1;
703 FETCH c1 INTO tlinfo;
704 IF (c1%notfound) THEN
705 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
706 igs_ge_msg_stack.add;
707 CLOSE c1;
708 app_exception.raise_exception;
709 RETURN;
710 END IF;
711 CLOSE c1;
712
713 IF (
714 (tlinfo.person_id = x_person_id)
715 AND (tlinfo.admission_appl_number = x_admission_appl_number)
716 AND (tlinfo.course_cd = x_course_cd)
717 AND (tlinfo.sequence_number = x_sequence_number)
718 AND (tlinfo.tracking_id = x_tracking_id)
719 ) THEN
720 NULL;
721 ELSE
722 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
723 igs_ge_msg_stack.add;
724 app_exception.raise_exception;
725 END IF;
726
727 RETURN;
728
729 END lock_row;
730
731
732 PROCEDURE update_row (
733 x_rowid IN VARCHAR2,
734 x_aplins_admreq_id IN NUMBER,
735 x_person_id IN NUMBER,
736 x_admission_appl_number IN NUMBER,
737 x_course_cd IN VARCHAR2,
738 x_sequence_number IN NUMBER,
739 x_tracking_id IN NUMBER,
740 x_mode IN VARCHAR2
741 ) AS
742 /*
743 || Created By : [email protected]
744 || Created On : 22-JUL-2001
745 || Purpose : Handles the UPDATE DML logic for the table.
746 || Known limitations, enhancements or remarks :
747 || Change History :
748 || Who When What
749 || ravishar 05/27/05 Security related changes
750 || (reverse chronological order - newest change first)
751 */
752 x_last_update_date DATE ;
753 x_last_updated_by NUMBER;
754 x_last_update_login NUMBER;
755 x_request_id NUMBER;
756 x_program_id NUMBER;
757 x_program_application_id NUMBER;
758 x_program_update_date DATE;
759
760 BEGIN
761
762 x_last_update_date := SYSDATE;
763 IF (X_MODE = 'I') THEN
764 x_last_updated_by := 1;
765 x_last_update_login := 0;
766 ELSIF (X_MODE IN ('R', 'S')) THEN
767 x_last_updated_by := fnd_global.user_id;
768 IF x_last_updated_by IS NULL THEN
769 x_last_updated_by := -1;
770 END IF;
771 x_last_update_login := fnd_global.login_id;
772 IF (x_last_update_login IS NULL) THEN
773 x_last_update_login := -1;
774 END IF;
775 ELSE
776 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
777 igs_ge_msg_stack.add;
778 app_exception.raise_exception;
779 END IF;
780
781 before_dml(
782 p_action => 'UPDATE',
783 x_rowid => x_rowid,
784 x_aplins_admreq_id => x_aplins_admreq_id,
785 x_person_id => x_person_id,
786 x_admission_appl_number => x_admission_appl_number,
787 x_course_cd => x_course_cd,
788 x_sequence_number => x_sequence_number,
789 x_tracking_id => x_tracking_id,
790 x_creation_date => x_last_update_date,
791 x_created_by => x_last_updated_by,
792 x_last_update_date => x_last_update_date,
793 x_last_updated_by => x_last_updated_by,
794 x_last_update_login => x_last_update_login
795 );
796
797 IF (X_MODE IN ('R', 'S')) THEN
801 IF (x_request_id = -1) THEN
798 x_request_id := fnd_global.conc_request_id;
799 x_program_id := fnd_global.conc_program_id;
800 x_program_application_id := fnd_global.prog_appl_id;
802 x_request_id := old_references.request_id;
803 x_program_id := old_references.program_id;
804 x_program_application_id := old_references.program_application_id;
805 x_program_update_date := old_references.program_update_date;
806 ELSE
807 x_program_update_date := SYSDATE;
808 END IF;
809 END IF;
810
811 IF (x_mode = 'S') THEN
812 igs_sc_gen_001.set_ctx('R');
813 END IF;
814 UPDATE igs_ad_aplins_admreq
815 SET
816 person_id = new_references.person_id,
817 admission_appl_number = new_references.admission_appl_number,
818 course_cd = new_references.course_cd,
819 sequence_number = new_references.sequence_number,
820 tracking_id = new_references.tracking_id,
821 last_update_date = x_last_update_date,
822 last_updated_by = x_last_updated_by,
823 last_update_login = x_last_update_login ,
824 request_id = x_request_id,
825 program_id = x_program_id,
826 program_application_id = x_program_application_id,
827 program_update_date = x_program_update_date
828 WHERE rowid = x_rowid;
829
830 IF (SQL%NOTFOUND) THEN
831 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
832 igs_ge_msg_stack.add;
833 IF (x_mode = 'S') THEN
834 igs_sc_gen_001.set_ctx('R');
835 END IF;
836 app_exception.raise_exception;
837 END IF;
838 IF (x_mode = 'S') THEN
839 igs_sc_gen_001.unset_ctx('R');
840 END IF;
841
842
843 EXCEPTION
844 WHEN OTHERS THEN
845 IF (x_mode = 'S') THEN
846 igs_sc_gen_001.set_ctx('R');
847 END IF;
848 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
849 -- Code to handle Security Policy error raised
850 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
851 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
852 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
853 -- that the ownerof policy function does not have privilege to access.
854 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
855 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
856 IGS_GE_MSG_STACK.ADD;
857 app_exception.raise_exception;
858 ELSE
859 RAISE;
860 END IF;
861 END update_row;
862
863
864 PROCEDURE add_row (
865 x_rowid IN OUT NOCOPY VARCHAR2,
866 x_aplins_admreq_id IN OUT NOCOPY NUMBER,
867 x_person_id IN NUMBER,
868 x_admission_appl_number IN NUMBER,
869 x_course_cd IN VARCHAR2,
870 x_sequence_number IN NUMBER,
871 x_tracking_id IN NUMBER,
872 x_mode IN VARCHAR2
873 ) AS
874 /*
875 || Created By : [email protected]
876 || Created On : 22-JUL-2001
877 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
878 || Known limitations, enhancements or remarks :
879 || Change History :
880 || Who When What
881 || (reverse chronological order - newest change first)
882 */
883 CURSOR c1 IS
884 SELECT rowid
885 FROM igs_ad_aplins_admreq
886 WHERE aplins_admreq_id = x_aplins_admreq_id;
887
888 BEGIN
889
890 OPEN c1;
891 FETCH c1 INTO x_rowid;
892 IF (c1%NOTFOUND) THEN
893 CLOSE c1;
894
895 insert_row (
896 x_rowid,
897 x_aplins_admreq_id,
898 x_person_id,
899 x_admission_appl_number,
900 x_course_cd,
901 x_sequence_number,
902 x_tracking_id,
903 x_mode
904 );
905 RETURN;
906 END IF;
907 CLOSE c1;
908
909 update_row (
910 x_rowid,
911 x_aplins_admreq_id,
912 x_person_id,
913 x_admission_appl_number,
914 x_course_cd,
915 x_sequence_number,
916 x_tracking_id,
917 x_mode
918 );
919
920 END add_row;
921
922
923 PROCEDURE delete_row (
924 x_rowid IN VARCHAR2,
925 x_mode IN VARCHAR2
926 ) AS
927 /*
928 || Created By : [email protected]
929 || Created On : 22-JUL-2001
930 || Purpose : Handles the DELETE DML logic for the table.
931 || Known limitations, enhancements or remarks :
932 || Change History :
933 || Who When What
934 || ravishar 05/27/05 Security related changes
935 || (reverse chronological order - newest change first)
936 */
937 BEGIN
938
939 before_dml (
940 p_action => 'DELETE',
941 x_rowid => x_rowid
942 );
943
944 IF (x_mode = 'S') THEN
945 igs_sc_gen_001.set_ctx('R');
946 END IF;
947 DELETE FROM igs_ad_aplins_admreq
948 WHERE rowid = x_rowid;
949
950 IF (SQL%NOTFOUND) THEN
951 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
952 igs_ge_msg_stack.add;
953 IF (x_mode = 'S') THEN
954 igs_sc_gen_001.set_ctx('R');
955 END IF;
956 app_exception.raise_exception;
957 END IF;
958 IF (x_mode = 'S') THEN
959 igs_sc_gen_001.unset_ctx('R');
960 END IF;
961
962
963 EXCEPTION
964 WHEN OTHERS THEN
965 IF (x_mode = 'S') THEN
966 igs_sc_gen_001.unset_ctx('R');
967 END IF;
968 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
969 -- Code to handle Security Policy error raised
970 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
971 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
972 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
973 -- that the ownerof policy function does not have privilege to access.
974 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
975 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
976 IGS_GE_MSG_STACK.ADD;
977 app_exception.raise_exception;
978 ELSE
979 RAISE;
980 END IF;
981 END delete_row;
982
983
984 END igs_ad_aplins_admreq_pkg;
[Home] [Help]