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