1 PACKAGE BODY igs_ad_panel_dtls_pkg AS
2 /* $Header: IGSAIH1B.pls 120.2 2005/09/30 05:33:22 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_ad_panel_dtls%ROWTYPE;
6 new_references igs_ad_panel_dtls%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_panel_dtls_id IN NUMBER,
12 x_person_id IN NUMBER,
13 x_admission_appl_number IN NUMBER,
14 x_nominated_course_cd IN VARCHAR2,
15 x_sequence_number IN NUMBER,
16 x_panel_code IN VARCHAR2,
17 x_interview_date IN DATE,
18 x_interview_time IN DATE,
19 x_location_cd IN VARCHAR2,
20 x_room_id IN NUMBER,
21 x_final_decision_code IN VARCHAR2,
22 x_final_decision_type IN VARCHAR2,
23 x_final_decision_date IN DATE,
24 x_closed_flag IN VARCHAR2,
25 x_attribute_category IN VARCHAR2,
26 x_attribute1 IN VARCHAR2,
27 x_attribute2 IN VARCHAR2,
28 x_attribute3 IN VARCHAR2,
29 x_attribute4 IN VARCHAR2,
30 x_attribute5 IN VARCHAR2,
31 x_attribute6 IN VARCHAR2,
32 x_attribute7 IN VARCHAR2,
33 x_attribute8 IN VARCHAR2,
34 x_attribute9 IN VARCHAR2,
35 x_attribute10 IN VARCHAR2,
36 x_attribute11 IN VARCHAR2,
37 x_attribute12 IN VARCHAR2,
38 x_attribute13 IN VARCHAR2,
39 x_attribute14 IN VARCHAR2,
40 x_attribute15 IN VARCHAR2,
41 x_attribute16 IN VARCHAR2,
42 x_attribute17 IN VARCHAR2,
43 x_attribute18 IN VARCHAR2,
44 x_attribute19 IN VARCHAR2,
45 x_attribute20 IN VARCHAR2,
46 x_creation_date IN DATE,
47 x_created_by IN NUMBER,
48 x_last_update_date IN DATE,
49 x_last_updated_by IN NUMBER,
50 x_last_update_login IN NUMBER
51 ) AS
52 /*
53 || Created By : Navin Sinha
54 || Created On : 16-JUN-2003
55 || Purpose : Initialises the Old and New references for the columns of the table.
56 || Known limitations, enhancements or remarks :
57 || Change History :
58 || Who When What
59 || (reverse chronological order - newest change first)
60 */
61
62 CURSOR cur_old_ref_values IS
63 SELECT *
64 FROM igs_ad_panel_dtls
65 WHERE rowid = x_rowid;
66
67 BEGIN
68
69 l_rowid := x_rowid;
70
71 old_references := NULL;
72 -- Code for setting the Old and New Reference Values.
73 -- Populate Old Values.
74 OPEN cur_old_ref_values;
75 FETCH cur_old_ref_values INTO old_references;
76 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
77 CLOSE cur_old_ref_values;
78 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
79 igs_ge_msg_stack.add;
80 app_exception.raise_exception;
81 RETURN;
82 END IF;
83 CLOSE cur_old_ref_values;
84
85 -- Populate New Values.
86 new_references.panel_dtls_id := x_panel_dtls_id;
87 new_references.person_id := x_person_id;
88 new_references.admission_appl_number := x_admission_appl_number;
89 new_references.nominated_course_cd := x_nominated_course_cd;
90 new_references.sequence_number := x_sequence_number;
91 new_references.panel_code := x_panel_code;
92 new_references.interview_date := TRUNC(x_interview_date);
93 new_references.interview_time := x_interview_time;
94 new_references.location_cd := x_location_cd;
95 new_references.room_id := x_room_id;
96 new_references.final_decision_code := x_final_decision_code;
97 new_references.final_decision_type := x_final_decision_type;
98 new_references.final_decision_date := TRUNC(x_final_decision_date);
99 new_references.closed_flag := x_closed_flag;
100 new_references.attribute_category := x_attribute_category;
101 new_references.attribute1 := x_attribute1;
102 new_references.attribute2 := x_attribute2;
103 new_references.attribute3 := x_attribute3;
104 new_references.attribute4 := x_attribute4;
105 new_references.attribute5 := x_attribute5;
106 new_references.attribute6 := x_attribute6;
107 new_references.attribute7 := x_attribute7;
108 new_references.attribute8 := x_attribute8;
109 new_references.attribute9 := x_attribute9;
110 new_references.attribute10 := x_attribute10;
111 new_references.attribute11 := x_attribute11;
112 new_references.attribute12 := x_attribute12;
113 new_references.attribute13 := x_attribute13;
114 new_references.attribute14 := x_attribute14;
115 new_references.attribute15 := x_attribute15;
116 new_references.attribute16 := x_attribute16;
117 new_references.attribute17 := x_attribute17;
118 new_references.attribute18 := x_attribute18;
119 new_references.attribute19 := x_attribute19;
120 new_references.attribute20 := x_attribute20;
121
122 IF (p_action = 'UPDATE') THEN
123 new_references.creation_date := old_references.creation_date;
124 new_references.created_by := old_references.created_by;
125 ELSE
126 new_references.creation_date := x_creation_date;
127 new_references.created_by := x_created_by;
128 END IF;
129
130 new_references.last_update_date := x_last_update_date;
131 new_references.last_updated_by := x_last_updated_by;
132 new_references.last_update_login := x_last_update_login;
133
134 END set_column_values;
135
136
137 PROCEDURE check_uniqueness AS
138 /*
139 || Created By : Navin Sinha
140 || Created On : 16-JUN-2003
141 || Purpose : Handles the Unique Constraint logic defined for the columns.
142 || Known limitations, enhancements or remarks :
143 || Change History :
144 || Who When What
145 || (reverse chronological order - newest change first)
146 */
147 BEGIN
148
149 IF ( get_uk_for_validation (
150 new_references.person_id,
151 new_references.admission_appl_number,
152 new_references.nominated_course_cd,
153 new_references.sequence_number,
154 new_references.panel_code
155 )
156 ) THEN
157 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
158 igs_ge_msg_stack.add;
159 app_exception.raise_exception;
160 END IF;
161
162 END check_uniqueness;
163
164
165 PROCEDURE check_parent_existance AS
166 /*
167 || Created By : Navin Sinha
168 || Created On : 16-JUN-2003
169 || Purpose : Checks for the existance of Parent records.
170 || Known limitations, enhancements or remarks :
171 || Change History :
172 || Who When What
173 || (reverse chronological order - newest change first)
174 */
175 BEGIN
176
177
178 IF (((old_references.panel_code = new_references.panel_code)) OR
179 ((new_references.panel_code IS NULL))) THEN
180 NULL;
181 ELSIF NOT igs_ad_intvw_pnls_pkg.get_pk_for_validation (
182 new_references.panel_code
183 ) THEN
184 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
185 igs_ge_msg_stack.add;
186 app_exception.raise_exception;
187 END IF;
188
189
190 IF (((old_references.person_id = new_references.person_id)) OR
191 ((new_references.person_id IS NULL))) THEN
192 NULL;
193 ELSE
194 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
195 new_references.person_id
196 )THEN
197 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
198 IGS_AD_GEN_001.SET_TOKEN('From IGS_PE_PERSON ->Parameter: Person_Id ');
199 IGS_GE_MSG_STACK.ADD;
200 APP_EXCEPTION.RAISE_EXCEPTION;
201 END IF;
202 END IF;
203
204
205 IF (((old_references.person_id = new_references.person_id) AND
206 (old_references.admission_appl_number = new_references.admission_appl_number) AND
207 (old_references.nominated_course_cd = new_references.nominated_course_cd) AND
208 (old_references.sequence_number = new_references.sequence_number)) OR
209 ((new_references.person_id IS NULL) OR
210 (new_references.admission_appl_number IS NULL) OR
211 (new_references.nominated_course_cd IS NULL) OR
212 (new_references.sequence_number IS NULL))) THEN
213 NULL;
214 ELSIF NOT igs_ad_ps_appl_inst_pkg.get_pk_for_validation (
215 new_references.person_id,
216 new_references.admission_appl_number,
217 new_references.nominated_course_cd,
218 new_references.sequence_number
219 ) THEN
220 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
221 igs_ge_msg_stack.add;
222 app_exception.raise_exception;
223 END IF;
224
225 IF (((old_references.location_cd = new_references.location_cd)) OR
226 ((new_references.location_cd IS NULL))) THEN
227 NULL;
228 ELSIF NOT igs_ad_location_pkg.get_pk_for_validation (
229 new_references.location_cd ,
230 'N'
231 ) THEN
232 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
233 igs_ge_msg_stack.add;
234 app_exception.raise_exception;
235 END IF;
236
237 IF (((old_references.room_id = new_references.room_id)) OR
238 ((new_references.room_id IS NULL))) THEN
239 NULL;
240 ELSIF NOT igs_ad_room_pkg.get_pk_for_validation (
241 new_references.room_id ,
242 'N'
243 ) THEN
244 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
245 igs_ge_msg_stack.add;
246 app_exception.raise_exception;
247 END IF;
248
249 IF (((old_references.final_decision_code = new_references.final_decision_code) AND
250 (old_references.final_decision_type = new_references.final_decision_type)) OR
251 ((new_references.final_decision_code IS NULL) OR
252 (new_references.final_decision_type IS NULL))) THEN
253 NULL;
254 ELSIF NOT igs_ad_code_classes_pkg.get_uk_For_validation (
255 new_references.final_decision_code,
256 new_references.final_decision_type,
257 'N'
258 ) THEN
259 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
260 igs_ge_msg_stack.add;
261 app_exception.raise_exception;
262 END IF;
263
264 END check_parent_existance;
265
266
267 PROCEDURE check_child_existance AS
268 /*
269 || Created By : Navin Sinha
270 || Created On : 16-JUN-2003
271 || Purpose : Checks for the existance of Child records.
272 || Known limitations, enhancements or remarks :
273 || Change History :
274 || Who When What
275 || (reverse chronological order - newest change first)
276 */
277 BEGIN
278
279 igs_ad_pnl_his_dtls_pkg.get_fk_igs_ad_panel_dtls (
280 old_references.panel_dtls_id
281 );
282
283 igs_ad_pnmembr_dtls_pkg.get_fk_igs_ad_panel_dtls (
284 old_references.panel_dtls_id
285 );
286
287 END check_child_existance;
288
289
290 FUNCTION get_pk_for_validation (
291 x_panel_dtls_id IN NUMBER
292 ) RETURN BOOLEAN AS
293 /*
294 || Created By : Navin Sinha
295 || Created On : 16-JUN-2003
296 || Purpose : Validates the Primary Key of the table.
297 || Known limitations, enhancements or remarks :
298 || Change History :
299 || Who When What
300 || (reverse chronological order - newest change first)
301 */
302 CURSOR cur_rowid IS
303 SELECT rowid
304 FROM igs_ad_panel_dtls
305 WHERE panel_dtls_id = x_panel_dtls_id
306 FOR UPDATE NOWAIT;
307
308 lv_rowid cur_rowid%RowType;
309
310 BEGIN
311
312 OPEN cur_rowid;
313 FETCH cur_rowid INTO lv_rowid;
314 IF (cur_rowid%FOUND) THEN
315 CLOSE cur_rowid;
316 RETURN(TRUE);
317 ELSE
318 CLOSE cur_rowid;
319 RETURN(FALSE);
320 END IF;
321
322 END get_pk_for_validation;
323
324
325 FUNCTION get_uk_for_validation (
326 x_person_id IN NUMBER,
327 x_admission_appl_number IN NUMBER,
328 x_nominated_course_cd IN VARCHAR2,
329 x_sequence_number IN NUMBER,
330 x_panel_code IN VARCHAR2
331 ) RETURN BOOLEAN AS
332 /*
333 || Created By : Navin Sinha
334 || Created On : 16-JUN-2003
335 || Purpose : Validates the Unique Keys of the table.
336 || Known limitations, enhancements or remarks :
337 || Change History :
338 || Who When What
339 || (reverse chronological order - newest change first)
340 */
341 CURSOR cur_rowid IS
342 SELECT rowid
343 FROM igs_ad_panel_dtls
344 WHERE person_id = x_person_id
345 AND admission_appl_number = x_admission_appl_number
346 AND nominated_course_cd = x_nominated_course_cd
347 AND sequence_number = x_sequence_number
348 AND panel_code = x_panel_code
349 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
350
351 lv_rowid cur_rowid%RowType;
352
353 BEGIN
354
355 OPEN cur_rowid;
356 FETCH cur_rowid INTO lv_rowid;
357 IF (cur_rowid%FOUND) THEN
358 CLOSE cur_rowid;
359 RETURN (true);
360 ELSE
361 CLOSE cur_rowid;
362 RETURN(FALSE);
363 END IF;
364
365 END get_uk_for_validation ;
366
367
368 PROCEDURE get_fk_igs_ad_intvw_pnls (
369 x_panel_code IN VARCHAR2
370 ) AS
371 /*
372 || Created By : Navin Sinha
373 || Created On : 16-JUN-2003
374 || Purpose : Validates the Foreign Keys for the table.
375 || Known limitations, enhancements or remarks :
376 || Change History :
377 || Who When What
378 || (reverse chronological order - newest change first)
379 */
380 CURSOR cur_rowid IS
381 SELECT rowid
382 FROM igs_ad_panel_dtls
383 WHERE ((panel_code = x_panel_code));
384
385 lv_rowid cur_rowid%RowType;
386
387 BEGIN
388
389 OPEN cur_rowid;
390 FETCH cur_rowid INTO lv_rowid;
391 IF (cur_rowid%FOUND) THEN
392 CLOSE cur_rowid;
393 fnd_message.set_name ('IGS', 'IGS_AD_PNLDTLS_PNL_FK');
394 igs_ge_msg_stack.add;
395 app_exception.raise_exception;
396 RETURN;
397 END IF;
398 CLOSE cur_rowid;
399
400 END get_fk_igs_ad_intvw_pnls;
401
402
403 PROCEDURE get_fk_igs_ad_location (
404 x_location_cd IN VARCHAR2
405 ) AS
406 /*
407 || Created By : Navin Sinha
408 || Created On : 16-JUN-2003
409 || Purpose : Validates the Foreign Keys for the table.
410 || Known limitations, enhancements or remarks :
411 || Change History :
412 || Who When What
413 || (reverse chronological order - newest change first)
414 */
415 CURSOR cur_rowid IS
416 SELECT rowid
417 FROM igs_ad_panel_dtls
418 WHERE ((location_cd = x_location_cd));
419
420 lv_rowid cur_rowid%RowType;
421
422 BEGIN
423
424 OPEN cur_rowid;
425 FETCH cur_rowid INTO lv_rowid;
426 IF (cur_rowid%FOUND) THEN
427 CLOSE cur_rowid;
428 fnd_message.set_name ('IGS', 'IGS_AD_PNLDTLS_LOC_FK');
429 igs_ge_msg_stack.add;
430 app_exception.raise_exception;
431 RETURN;
432 END IF;
433 CLOSE cur_rowid;
434
435 END get_fk_igs_ad_location;
436
437
438 PROCEDURE get_fk_igs_ad_room (
439 x_room_id IN NUMBER
440 ) AS
441 /*
442 || Created By : Navin Sinha
443 || Created On : 16-JUN-2003
444 || Purpose : Validates the Foreign Keys for the table.
445 || Known limitations, enhancements or remarks :
446 || Change History :
447 || Who When What
448 || (reverse chronological order - newest change first)
449 */
450 CURSOR cur_rowid IS
451 SELECT rowid
452 FROM igs_ad_panel_dtls
453 WHERE ((room_id = x_room_id));
454
455 lv_rowid cur_rowid%RowType;
456
457 BEGIN
458
459 OPEN cur_rowid;
460 FETCH cur_rowid INTO lv_rowid;
461 IF (cur_rowid%FOUND) THEN
462 CLOSE cur_rowid;
463 fnd_message.set_name ('IGS', 'IGS_AD_PNLDTLS_ROOM_FK');
464 igs_ge_msg_stack.add;
465 app_exception.raise_exception;
466 RETURN;
467 END IF;
468 CLOSE cur_rowid;
469
470 END get_fk_igs_ad_room;
471
472
473 PROCEDURE get_ufk_igs_ad_code_classes (
474 x_name IN VARCHAR2,
475 x_class IN VARCHAR2
476 ) AS
477 /*
478 || Created By : Navin Sinha
479 || Created On : 16-JUN-2003
480 || Purpose : Validates the Foreign Keys for the table.
481 || Known limitations, enhancements or remarks :
482 || Change History :
483 || Who When What
484 || (reverse chronological order - newest change first)
485 */
486 CURSOR cur_rowid IS
487 SELECT rowid
488 FROM igs_ad_panel_dtls
489 WHERE ((final_decision_code = x_name) AND
490 (final_decision_type = x_class));
491
492 lv_rowid cur_rowid%RowType;
493
494 BEGIN
495
496 OPEN cur_rowid;
497 FETCH cur_rowid INTO lv_rowid;
498 IF (cur_rowid%FOUND) THEN
499 CLOSE cur_rowid;
500 fnd_message.set_name ('IGS', 'IGS_AD_PNLDTLS_CODE_CLS_FK');
501 igs_ge_msg_stack.add;
502 app_exception.raise_exception;
503 RETURN;
504 END IF;
505 CLOSE cur_rowid;
506
507 END get_ufk_igs_ad_code_classes;
508
509 PROCEDURE beforerowinsertupdatedelete1 (
510 p_inserting BOOLEAN,
511 p_updating BOOLEAN,
512 p_deleting BOOLEAN ) AS
513
514 ----------------------------------------------------------------
515 --Created by : Navin Sinha
516 --Date created: 16-Jun-03
517 --
518 --Purpose: BUG NO : 1366894 - Interview Build.
519 --
520 --
521 --Known limitations/enhancements and/or remarks:
522 --
523 --Change History:
524 --Who When What
525 ----------------------------------------------------------------
526 -- Cursor to check if panel member exists.
527 CURSOR c_panel_membr_exist IS
528 SELECT 'x'
529 FROM igs_ad_panel_membrs pm
530 WHERE pm.panel_code = new_references.panel_code;
531 l_panel_membr_exist VARCHAR2(1);
532
533 -- Cursor to get all the history records associated to panel decision.
534 CURSOR c_get_pnl_history IS
535 SELECT rowid
536 FROM igs_ad_pnl_his_dtls
537 WHERE panel_dtls_id = old_references.panel_dtls_id
538 FOR UPDATE OF panel_dtls_id NOWAIT;
539
540 CURSOR c_panel_type_code IS
541 SELECT panel_type_Code
542 FROM igs_ad_intvw_pnls
543 WHERE panel_code = NVL(new_references.panel_code,old_references.panel_code)
544 AND closed_flag = 'N';
545
546 CURSOR c_apcs_step_exist IS
547 SELECT 'X'
548 FROM igs_ad_prcs_cat_step apcs ,
549 igs_Ad_appl appl
550 WHERE appl.person_id = new_references.person_id
551 AND appl.admission_appl_number = new_references.admission_appl_number
552 AND apcs.admission_cat = appl.admission_cat
553 AND apcs.s_admission_process_type = appl.s_admission_process_type
554 AND apcs.s_admission_step_type = 'SCRN_BEF_INTERVIEW' AND
555 apcs.step_group_type = 'APPL-VAL';
556
557 -- Cursor to get the system defaulted Decisions mapped to 'PENDING'
558 CURSOR cur_dflt_panl_cd(cp_dec_type igs_ad_code_classes.class%TYPE) IS
559 SELECT *
560 FROM igs_ad_code_classes
561 WHERE system_status = 'PENDING'
562 AND class = cp_dec_type --'INTERVIEW', 'SCREENING', 'FINAL_SCREENING', 'FINAL_INTERVIEW'
563 AND NVL(system_default, 'N') = 'Y'
564 AND closed_ind = 'N'
565 AND CLASS_TYPE_CODE='ADM_CODE_CLASSES';
566
567 rec_dflt_panl_cd cur_dflt_panl_cd%ROWTYPE;
568
569 -- Cursor to get the meaning for lookup code
570 CURSOR c_lkup_cd_mean(cp_lookup_type igs_lookups_view.lookup_type%TYPE, cp_lookup_code igs_lookups_view.lookup_code%TYPE) IS
571 SELECT meaning
572 FROM igs_lookups_view
573 WHERE lookup_type = cp_lookup_type
574 AND lookup_code = cp_lookup_code;
575
576 l_class_meaning igs_lookups_view.meaning%TYPE;
577 l_sys_stat_meaning igs_lookups_view.meaning%TYPE;
578
579 -- Cursor to check for panel level.
580 CURSOR c_panel_level_code IS
581 SELECT panel_level_code
582 FROM igs_ad_intvw_pnls
583 WHERE panel_code = new_references.panel_code;
584 l_panel_level_code igs_ad_intvw_pnls.panel_level_code%TYPE;
585
586 l_panel_type_Code igs_ad_intvw_pnls.panel_type_Code%TYPE;
587 l_apcs_step_exist c_apcs_step_exist%ROWTYPE;
588 l_dec_type igs_ad_code_classes.class%TYPE;
589
590 -- Cursor to Check closed flag associated to panel code.
591 CURSOR c_chk_final_decision IS
592 SELECT *
593 FROM igs_ad_panel_dtls
594 WHERE panel_dtls_id = NVL(old_references.panel_dtls_id, new_references.panel_dtls_id);
595
596 rec_chk_final_decision c_chk_final_decision%ROWTYPE;
597
598 CURSOR c_final_decison IS
599 SELECT 'X'
600 FROM
601 igs_ad_panel_dtls pdtls,
602 igs_Ad_code_classes cdcls
603 WHERE person_id = new_references.person_id
604 AND admission_appl_number = new_references.admission_Appl_number
605 AND nominated_course_cd = new_references.nominated_course_Cd
606 AND sequence_number = new_references.sequence_number
607 AND pdtls.final_decision_code = cdcls.name
608 AND pdtls.final_decision_type = cdcls.class
609 AND cdcls.class = 'FINAL_SCREENING'
610 AND cdcls.system_Status = 'INTERVIEW'
611 AND cdcls.CLASS_TYPE_CODE='ADM_CODE_CLASSES';
612
613 CURSOR c_mem_dec IS
614 SELECT 'X'
615 FROM
616 igs_ad_pnmembr_dtls pdtls,
617 igs_Ad_code_Classes cdcls
618 WHERE
619 pdtls.panel_Dtls_id = new_references.panel_Dtls_id
620 AND pdtls.member_decision_code = cdcls.name
621 AND pdtls.member_decision_type = cdcls.class
622 AND cdcls.class = DECODE(new_references.final_decision_type,
623 'FINAL_SCREENING', 'SCREENING',
624 'FINAL_INTERVIEW','INTERVIEW')
625 AND cdcls.system_Status = 'PENDING'
626 AND cdcls.CLASS_TYPE_CODE='ADM_CODE_CLASSES';
627 l_mem_dec_rec c_mem_dec%ROWTYPE;
628 l_final_decison_rec c_final_decison%ROWTYPE;
629
630 CURSOR c_intvw_pnl_exsts IS
631 SELECT 'X'
632 FROM igs_ad_intvw_pnls pnls, igs_ad_panel_dtls pdtls
633 WHERE pnls.panel_type_code='INTERVIEW'
634 AND pnls.panel_code = pdtls.panel_code
635 AND pdtls.person_id = NVL(old_references.person_id, new_references.person_id)
636 AND pdtls.admission_appl_number = NVL(old_references.admission_appl_number, new_references.admission_appl_number)
637 AND pdtls.nominated_course_Cd = NVL(old_references.nominated_course_Cd, new_references.nominated_course_Cd)
638 AND pdtls.sequence_number = NVL(old_references.sequence_number, new_references.sequence_number);
639
643 CURSOR c_memb_exsts IS
640 intvw_pnl_exsts_rec c_intvw_pnl_exsts%ROWTYPE;
641
642 -- Check any member exists for this panel instance.
644 SELECT mbrdtls.member_person_id
645 FROM igs_ad_pnmembr_dtls mbrdtls,
646 igs_ad_panel_dtls pdtls
647 WHERE mbrdtls.panel_dtls_id = pdtls.panel_dtls_id
648 AND pdtls.panel_dtls_id = new_references.panel_dtls_id;
649
650 memb_exsts_rec c_memb_exsts%ROWTYPE;
651
652 -- Cursor to check if member Decisions is mapped to the system defaulted Decisions of 'PENDING'
653 CURSOR cur_chk_dflt_panl_cd IS
654 SELECT *
655 FROM igs_ad_code_classes
656 WHERE system_status = 'PENDING'
657 AND name = new_references.final_decision_code
658 AND class = new_references.final_decision_type
659 AND closed_ind = 'N'
660 AND CLASS_TYPE_CODE='ADM_CODE_CLASSES';
661
662 rec_chk_dflt_panl_cd cur_chk_dflt_panl_cd%ROWTYPE;
663
664 -- Cursor to check the application date of the application
665 CURSOR c_appl_dt IS
666 SELECT appl_dt
667 FROM igs_ad_appl aa
668 WHERE aa.person_id = new_references.person_id
669 AND aa.admission_appl_number = new_references.admission_appl_number;
670
671 l_appl_dt igs_ad_appl.appl_dt%TYPE;
672
673 BEGIN
674 IF NVL(p_inserting,FALSE) THEN
675 -- A Person added to a panel must have the system person type of Interviewer. Else raise an error message.
676 OPEN c_panel_membr_exist;
677 FETCH c_panel_membr_exist INTO l_panel_membr_exist;
678 IF c_panel_membr_exist%NOTFOUND THEN
679 CLOSE c_panel_membr_exist;
680 FND_MESSAGE.SET_NAME('IGS','IGS_AD_PNL_NO_MBR'); -- Message: The panel inserted has no members.
681 IGS_GE_MSG_STACK.ADD;
682 APP_EXCEPTION.RAISE_EXCEPTION;
683 END IF;
684 IF c_panel_membr_exist%ISOPEN THEN
685 CLOSE c_panel_membr_exist;
686 END IF;
687 --Application instance cannot be assigned to a closed panel
688 OPEN c_panel_type_code;
689 FETCH c_panel_type_code INTO l_panel_type_Code;
690 IF c_panel_type_code%NOTFOUND THEN
691 CLOSE c_panel_type_code;
692 FND_MESSAGE.SET_NAME('IGS','IGS_AD_PNL_CLSD'); -- Message: The panel is closed or non-existing
693 IGS_GE_MSG_STACK.ADD;
694 APP_EXCEPTION.RAISE_EXCEPTION;
695 END IF;
696 CLOSE c_panel_type_code;
697
698 --If process category step 'Screening required before Interview' is not selected for application category
699 --then throw error
700 OPEN c_apcs_step_exist;
701 FETCH c_apcs_step_exist INTO l_apcs_step_exist;
702 IF c_apcs_step_exist%NOTFOUND THEN
703 CLOSE c_apcs_step_exist;
704 IF l_panel_type_Code = 'SCREENING' THEN
705 FND_MESSAGE.SET_NAME('IGS','IGS_AD_NO_SCRN_STEP'); -- Message: 'Screening required before Interview step
706 igs_ge_msg_stack.add; -- is not selected for this admission category
707 app_exception.raise_exception;
708 END IF;
709 ELSE
710 CLOSE c_apcs_step_exist;
711 IF l_panel_type_Code = 'INTERVIEW' THEN
712 OPEN c_final_decison;
713 FETCH c_final_decison INTO l_final_decison_rec;
714 IF c_final_decison%NOTFOUND THEN
715 CLOSE c_final_decison;
716 fnd_message.set_name('IGS','IGS_AD_FNLSCRN_DEC_NOT_INTVW'); -- Message: Application cannot be assigned to interview panel
717 -- unless Final Screening decision for at least one panel is INTERVIEW
718 igs_ge_msg_stack.add;
719 app_exception.raise_exception;
720 END IF;
721 CLOSE c_final_decison;
722
723 END IF;
724 END IF;
725
726 IF new_references.final_decision_code IS NULL THEN
727 IF l_panel_type_Code = 'SCREENING' THEN
728 l_dec_type := 'FINAL_SCREENING';
729 ELSE
730 l_dec_type := 'FINAL_INTERVIEW';
731 END IF;
732 OPEN cur_dflt_panl_cd(l_dec_type);
733 FETCH cur_dflt_panl_cd INTO rec_dflt_panl_cd;
734 IF cur_dflt_panl_cd%NOTFOUND THEN
735 CLOSE cur_dflt_panl_cd;
736 -- Get the value for message token CLASS_MEANING
737 OPEN c_lkup_cd_mean(l_dec_type, 'PENDING');
738 FETCH c_lkup_cd_mean INTO l_class_meaning;
739 CLOSE c_lkup_cd_mean;
740
741 -- Get the value for message token SYS_STAT_MEANING
742 OPEN c_lkup_cd_mean('INTR_DECSN', l_dec_type);
743 FETCH c_lkup_cd_mean INTO l_sys_stat_meaning;
744 CLOSE c_lkup_cd_mean;
745
746 fnd_message.set_name('IGS','IGS_AD_NO_DECISION_CD_SETUP'); -- Message: Unable to assign panel members to the application instance.
747 fnd_message.set_token('CLASS_MEANING', l_class_meaning);
748 fnd_message.set_token('SYS_STAT_MEANING', l_sys_stat_meaning);
749 igs_ge_msg_stack.add;
750 app_exception.raise_exception;
751 ELSE
755 CLOSE cur_dflt_panl_cd;
752 --defaulting final_decision_type, final_decision_type
753 new_references.final_decision_code := rec_dflt_panl_cd.name;
754 new_references.final_decision_type := rec_dflt_panl_cd.class;
756 END IF;
757 END IF;
758 END IF; ---p_inserting
759
760 IF NVL(p_inserting,FALSE) OR NVL(p_updating,FALSE) OR NVL(p_deleting,FALSE) THEN
761 IF new_references.final_decision_type = 'FINAL_SCREENING' OR old_references.final_decision_type = 'FINAL_SCREENING' THEN
762 OPEN c_intvw_pnl_exsts;
763 FETCH c_intvw_pnl_exsts INTO intvw_pnl_exsts_rec;
764 IF c_intvw_pnl_exsts%FOUND THEN
765 CLOSE c_intvw_pnl_exsts;
766 fnd_message.set_name('IGS','IGS_AD_INTVW_PNL_EXITS'); -- Message: Cannot update screening information when interview panel is already associated to the application instance.
767 igs_ge_msg_stack.add;
768 app_exception.raise_exception;
769 END IF;
770 CLOSE c_intvw_pnl_exsts;
771 END IF;
772 END IF;
773
774 IF NVL(p_inserting,FALSE) OR NVL(p_updating,FALSE) THEN
775 -- Enable the Member Interview Details button if the panel has been assigned the
776 -- panel level of 'Panel Member' and disable the Panel Interview Date,
777 -- Time, Location, and Room/Building fields.
778 IF NVL(old_references.final_decision_code,new_references.final_decision_code) <> new_references.final_decision_code THEN
779 OPEN c_mem_dec;
780 FETCH c_mem_dec INTO l_mem_dec_rec;
781 IF c_mem_dec %FOUND THEN
782 CLOSE c_mem_dec;
783 fnd_message.set_name('IGS','IGS_AD_MEM_DEC_PEND');
784 igs_ge_msg_stack.add;
785 app_exception.raise_exception;
786 END IF;
787 CLOSE c_mem_dec;
788 END IF;
789
790 OPEN c_panel_level_code;
791 FETCH c_panel_level_code INTO l_panel_level_code;
792 CLOSE c_panel_level_code;
793 IF l_panel_level_code <> 'PANEL' AND
794 (new_references.interview_date IS NOT NULL OR
795 new_references.interview_time IS NOT NULL OR
796 new_references.location_cd IS NOT NULL OR
797 new_references.room_id IS NOT NULL)
798 THEN -- PANEL_MEMBER
799 fnd_message.set_name('IGS','IGS_AD_INVALID_PNL_LVL'); -- Message: Cannot record interview details at panel level as the panel code is mapped to a panel level of Panel Member.
800 igs_ge_msg_stack.add;
801 app_exception.raise_exception;
802 END IF;
803
804 -- Check if member Decisions is mapped to the system defaulted Decisions of 'PENDING'
805 OPEN cur_chk_dflt_panl_cd;
806 FETCH cur_chk_dflt_panl_cd INTO rec_chk_dflt_panl_cd;
807 IF cur_chk_dflt_panl_cd%NOTFOUND THEN
808 CLOSE cur_chk_dflt_panl_cd;
809
810 IF ((new_references.final_decision_date IS NULL AND new_references.final_decision_code IS NOT NULL) OR
811 (new_references.final_decision_date IS NOT NULL AND new_references.final_decision_code IS NULL)) THEN
812 -- Decision Date must be entered if a Decision is entered. If the Decision is saved without Decision Date then raise an error message.
813 fnd_message.set_name('IGS','IGS_AD_MAND_DECISION_INFO'); -- Message: Decision and Decision Date must be entered.
814 igs_ge_msg_stack.add;
815 app_exception.raise_exception;
816 END IF;
817 ELSE
818 IF new_references.final_decision_date IS NOT NULL THEN
819 fnd_message.set_name('IGS','IGS_AD_MAND_DECISION_INFO'); -- Message: Decision and Decision Date must be entered.
820 igs_ge_msg_stack.add;
821 app_exception.raise_exception;
822 END IF;
823 END IF;
824 IF cur_chk_dflt_panl_cd%ISOPEN THEN
825 CLOSE cur_chk_dflt_panl_cd;
826 END IF;
827
828 OPEN c_appl_dt;
829 FETCH c_appl_dt INTO l_appl_dt;
830 CLOSE c_appl_dt;
831
832 IF NVL(new_references.interview_date,sysdate) < l_appl_dt THEN
833 fnd_message.set_name('IGS','IGS_AD_APPL_DATE_ERROR'); -- NAME cannot be less than Application Date
834 fnd_message.set_token ('NAME',fnd_message.get_string('IGS','IGS_AD_INTVW_DATE')); -- Message: Decision Date
835 igs_ge_msg_stack.add;
836 app_exception.raise_exception;
837 END IF;
838
839 IF new_references.final_decision_date IS NOT NULL THEN
840 -- Decision Date entered must be greater than or equal to the Application Date. Else raise an Error message.
841 IF new_references.final_decision_date < l_appl_dt OR new_references.final_decision_date > SYSDATE THEN
842 fnd_message.set_name('IGS','IGS_AD_DECISION_DATE'); -- Decision Date Can Neither be greater than System Date nor be less than Application Date
843 igs_ge_msg_stack.add;
844 app_exception.raise_exception;
845 END IF;
846 END IF;
847
848 END IF;
849
850
851 IF NVL(p_updating,FALSE) THEN
852 IF new_references.final_decision_code <> old_references.final_decision_code THEN
853 OPEN c_memb_exsts;
854 FETCH c_memb_exsts INTO memb_exsts_rec;
855 IF c_memb_exsts%NOTFOUND THEN
856 CLOSE c_memb_exsts;
857 FND_MESSAGE.SET_NAME('IGS','IGS_AD_NO_MBR_APPL_EXTS'); -- Message: Cannot update the final decision when no member exists for this panel.
858 IGS_GE_MSG_STACK.ADD;
859 APP_EXCEPTION.RAISE_EXCEPTION;
860 END IF;
861 CLOSE c_memb_exsts;
862 END IF;
863 END IF;
864
865 IF NVL(p_updating,FALSE) OR NVL(p_deleting,FALSE) THEN
866 -- If a panel is closed then the interviewer decisions cannot be entered/updated and adding/deleting of interviewers is prohibited.
867 -- Check closed flag associated to panel code.
868 OPEN c_chk_final_decision;
869 FETCH c_chk_final_decision INTO rec_chk_final_decision;
870 CLOSE c_chk_final_decision;
871
872 IF NVL(rec_chk_final_decision.closed_flag,'N') <> 'N' THEN
873 fnd_message.set_name('IGS','IGS_AD_PNL_IS_CLOSED'); -- Message: Closed panel details cannot be updated or deleted.
874 igs_ge_msg_stack.add;
875 app_exception.raise_exception;
876 END IF;
877 END IF;
878
879 IF NVL(p_deleting,FALSE) THEN
880 -- Delete history records from igs_ad_pnl_his_dtls.
881 FOR v_hist_rec IN c_get_pnl_history LOOP
882 igs_ad_pnl_his_dtls_pkg.delete_row (
883 x_rowid => v_hist_rec.rowid );
884 END LOOP;
885 --Application instance cannot be deleted from a closed panel
886 OPEN c_panel_type_code;
887 FETCH c_panel_type_code INTO l_panel_type_Code;
888 IF c_panel_type_code%NOTFOUND THEN
889 CLOSE c_panel_type_code;
890 FND_MESSAGE.SET_NAME('IGS','IGS_AD_PNL_CLSD'); -- Message: The panel is closed or non-existing
891 IGS_GE_MSG_STACK.ADD;
892 APP_EXCEPTION.RAISE_EXCEPTION;
893 END IF;
894 CLOSE c_panel_type_code;
895
896 END IF;
897 END beforerowinsertupdatedelete1;
898
899 PROCEDURE afterinsertupdatedelete(
900 p_inserting IN BOOLEAN,
901 p_updating IN BOOLEAN,
902 p_deleting IN BOOLEAN,
903 p_panel_dtls_id IN NUMBER
904 ) AS
905 ----------------------------------------------------------------
906 --Created by : Navin Sinha
907 --Date created: 16-Jun-03
908 --
909 --Purpose: BUG NO : 1366894 - Interview Build.
910 -- To assign the panel members associated with the panel code to the Application Instance.
911 --
912 --Known limitations/enhancements and/or remarks:
913 --
914 --Change History:
915 --Who When What
916 ----------------------------------------------------------------
917
918 l_rowid_ad_pnmembr_dtls VARCHAR2(25);
919 l_rowid_ad_pnl_history VARCHAR2(25);
920
921 -- Cursor to get the panel type of the panel code.
922 CURSOR cur_panel_type_code IS
923 SELECT panel_type_code
924 FROM igs_ad_intvw_pnls
925 WHERE panel_code = new_references.panel_code;
926
927 l_panel_type_code igs_ad_intvw_pnls.panel_type_code%TYPE;
928
929 -- Cursor to get the system defaulted Decisions mapped to 'PENDING'
930 CURSOR cur_dflt_panl_cd(cp_panel_type_code igs_ad_intvw_pnls.panel_type_code%TYPE) IS
931 SELECT *
932 FROM igs_ad_code_classes
933 WHERE system_status = 'PENDING'
934 AND class = cp_panel_type_code --'INTERVIEW', 'SCREENING', 'FINAL_SCREENING', 'FINAL_INTERVIEW'
935 AND NVL(system_default, 'N') = 'Y'
936 AND closed_ind = 'N'
937 AND CLASS_TYPE_CODE='ADM_CODE_CLASSES';
938
939 rec_dflt_panl_cd cur_dflt_panl_cd%ROWTYPE;
940
941 -- Cursor to get all the panel members associated to panel code.
942 CURSOR c_get_panel_membrs IS
943 SELECT *
944 FROM igs_ad_panel_membrs pm
945 WHERE pm.panel_code = new_references.panel_code;
946
947 rec_panel_membrs c_get_panel_membrs%ROWTYPE;
948
949 -- Cursor to get the meaning for lookup code
950 CURSOR c_lkup_cd_mean(cp_lookup_type igs_lookups_view.lookup_type%TYPE, cp_lookup_code igs_lookups_view.lookup_code%TYPE) IS
951 SELECT meaning
952 FROM igs_lookups_view
953 WHERE lookup_type = cp_lookup_type
954 AND lookup_code = cp_lookup_code;
955
956 l_class_meaning igs_lookups_view.meaning%TYPE;
957 l_sys_stat_meaning igs_lookups_view.meaning%TYPE;
958
959 l_history_date igs_ad_pnl_his_dtls.history_date%TYPE;
960
961 CURSOR c_panel_type IS
962 SELECT panel_type_code
963 FROM igs_ad_intvw_pnls
964 WHERE panel_code = new_references.panel_code;
965 l_panel_type igs_ad_intvw_pnls.panel_type_code%TYPE;
966
967 l_new_sys_stat igs_ad_code_classes.system_status%TYPE;
968 l_old_sys_stat igs_ad_code_classes.system_status%TYPE;
969
970 BEGIN
971 IF NVL(p_inserting,FALSE) THEN
972 -- Get the panel type of the panel code.
973 OPEN cur_panel_type_code;
974 FETCH cur_panel_type_code INTO l_panel_type_code;
975 CLOSE cur_panel_type_code;
976
977 -- Get the system defaulted Decisions mapped to 'PENDING'
978 OPEN cur_dflt_panl_cd(l_panel_type_code);
979 FETCH cur_dflt_panl_cd INTO rec_dflt_panl_cd;
980 IF cur_dflt_panl_cd%NOTFOUND THEN
981 CLOSE cur_dflt_panl_cd;
982
983 -- Get the value for message token CLASS_MEANING
984 OPEN c_lkup_cd_mean(l_panel_type_code, 'PENDING');
985 FETCH c_lkup_cd_mean INTO l_class_meaning;
986 CLOSE c_lkup_cd_mean;
987
988 -- Get the value for message token SYS_STAT_MEANING
989 OPEN c_lkup_cd_mean('INTR_DECSN', l_panel_type_code);
990 FETCH c_lkup_cd_mean INTO l_sys_stat_meaning;
991 CLOSE c_lkup_cd_mean;
992
993 fnd_message.set_name('IGS','IGS_AD_NO_DECISION_CD_SETUP'); -- Message: Unable to assign panel members to the application instance.
994 fnd_message.set_token('CLASS_MEANING', l_class_meaning);
995 fnd_message.set_token('SYS_STAT_MEANING', l_sys_stat_meaning);
996 igs_ge_msg_stack.add;
997 app_exception.raise_exception;
998 END IF;
999
1000 IF cur_dflt_panl_cd%ISOPEN THEN
1001 CLOSE cur_dflt_panl_cd;
1002 END IF;
1003
1004 FOR c_get_panel_membrs_rec IN c_get_panel_membrs LOOP
1005 igs_ad_pnmembr_dtls_pkg.insert_row (
1006 x_rowid => l_rowid_ad_pnmembr_dtls,
1007 x_panel_dtls_id => p_panel_dtls_id,
1008 x_role_type_code => c_get_panel_membrs_rec.role_type_code,
1009 x_member_person_id => c_get_panel_membrs_rec.member_person_id,
1010 x_interview_date => NULL,
1011 x_interview_time => NULL,
1012 x_location_cd => NULL,
1013 x_room_id => NULL,
1014 x_member_decision_code => rec_dflt_panl_cd.name,
1015 x_member_decision_type => rec_dflt_panl_cd.class,
1016 x_member_decision_date => NULL,
1017 x_attribute_category => NULL,
1018 x_attribute1 => NULL,
1019 x_attribute2 => NULL,
1020 x_attribute3 => NULL,
1021 x_attribute4 => NULL,
1022 x_attribute5 => NULL,
1023 x_attribute6 => NULL,
1024 x_attribute7 => NULL,
1025 x_attribute8 => NULL,
1026 x_attribute9 => NULL,
1027 x_attribute10 => NULL,
1028 x_attribute11 => NULL,
1029 x_attribute12 => NULL,
1030 x_attribute13 => NULL,
1031 x_attribute14 => NULL,
1032 x_attribute15 => NULL,
1033 x_attribute16 => NULL,
1034 x_attribute17 => NULL,
1035 x_attribute18 => NULL,
1036 x_attribute19 => NULL,
1037 x_attribute20 => NULL,
1038 x_mode => 'R'
1039 );
1040 END LOOP;
1041 END IF;
1042
1043 IF NVL(p_updating,FALSE) THEN
1044 -- Populate the history Table igs_ad_pnl_his_dtls.
1045 IF (NVL(new_references.final_decision_code,'NULL') <> NVL(old_references.final_decision_code,'NULL') OR
1046 NVL(new_references.final_decision_date,SYSDATE) <> TRUNC(NVL(old_references.final_decision_date,SYSDATE))) THEN
1047
1048 -- When the final screening/interview decision is changed, a record needs to get inserted into the history table.
1049 -- The primary key for this tanble is panel_dtls_id, history_date. If a record already exists, then
1050 -- increment the history date by one second and insert a record.
1051 l_history_date := old_references.last_update_date + 1 / (60*24*60);
1052
1053 igs_ad_pnl_his_dtls_pkg.insert_row (
1054 x_rowid => l_rowid_ad_pnl_history,
1055 x_panel_dtls_id => old_references.panel_dtls_id,
1056 x_history_date => l_history_date,
1057 x_final_decision_code => old_references.final_decision_code,
1058 x_final_decision_type => old_references.final_decision_type,
1059 x_mode => 'R'
1060 );
1061 END IF;
1062 END IF;
1063
1064 IF NVL(p_updating,FALSE) THEN
1065 -- Raise the Business event when the Final Screening/Interview Decision of the Panel is modified.
1066 -- When both the Decision Date and the Final Screening Decision record is committed
1067 -- and the Final Screening Decision has changed from the system final screening decision
1068 -- of 'Pending' to any other system final screening decision.
1069 l_new_sys_stat := igs_ad_gen_013.get_sys_code_status(new_references.final_decision_code, new_references.final_decision_type);
1070 l_old_sys_stat := igs_ad_gen_013.get_sys_code_status(old_references.final_decision_code, old_references.final_decision_type);
1071 IF l_old_sys_stat = 'PENDING' AND l_new_sys_stat <> 'PENDING' THEN
1072 OPEN c_panel_type;
1073 FETCH c_panel_type INTO l_panel_type; -- 'SCREENING', 'INTERVIEW'
1074 CLOSE c_panel_type;
1075 -- Raise workflow event.
1076 igs_ad_ss_appl_upd_page.final_scrn_intw_event(
1077 p_person_id => new_references.person_id,
1078 p_admission_appl_number => new_references.admission_appl_number,
1079 p_nominated_course_cd => new_references.nominated_course_cd,
1080 p_sequence_number => new_references.sequence_number,
1081 p_final_screening_decision => new_references.final_decision_code,
1082 p_final_screening_date => new_references.final_decision_date,
1083 p_panel_code => new_references.panel_code,
1084 p_raised_for => l_panel_type); -- 'SCREENING', 'INTERVIEW'
1085
1086 END IF;
1087 END IF;
1088
1089 END afterinsertupdatedelete;
1090
1091 PROCEDURE before_dml (
1092 p_action IN VARCHAR2,
1093 x_rowid IN VARCHAR2,
1094 x_panel_dtls_id IN NUMBER,
1095 x_person_id IN NUMBER,
1096 x_admission_appl_number IN NUMBER,
1097 x_nominated_course_cd IN VARCHAR2,
1098 x_sequence_number IN NUMBER,
1099 x_panel_code IN VARCHAR2,
1100 x_interview_date IN DATE,
1101 x_interview_time IN DATE,
1102 x_location_cd IN VARCHAR2,
1103 x_room_id IN NUMBER,
1104 x_final_decision_code IN VARCHAR2,
1105 x_final_decision_type IN VARCHAR2,
1106 x_final_decision_date IN DATE,
1107 x_closed_flag IN VARCHAR2,
1108 x_attribute_category IN VARCHAR2,
1109 x_attribute1 IN VARCHAR2,
1110 x_attribute2 IN VARCHAR2,
1111 x_attribute3 IN VARCHAR2,
1112 x_attribute4 IN VARCHAR2,
1113 x_attribute5 IN VARCHAR2,
1114 x_attribute6 IN VARCHAR2,
1115 x_attribute7 IN VARCHAR2,
1116 x_attribute8 IN VARCHAR2,
1117 x_attribute9 IN VARCHAR2,
1118 x_attribute10 IN VARCHAR2,
1119 x_attribute11 IN VARCHAR2,
1120 x_attribute12 IN VARCHAR2,
1121 x_attribute13 IN VARCHAR2,
1122 x_attribute14 IN VARCHAR2,
1123 x_attribute15 IN VARCHAR2,
1124 x_attribute16 IN VARCHAR2,
1125 x_attribute17 IN VARCHAR2,
1126 x_attribute18 IN VARCHAR2,
1127 x_attribute19 IN VARCHAR2,
1128 x_attribute20 IN VARCHAR2,
1129 x_creation_date IN DATE,
1130 x_created_by IN NUMBER,
1131 x_last_update_date IN DATE,
1132 x_last_updated_by IN NUMBER,
1136 || Created By : Navin Sinha
1133 x_last_update_login IN NUMBER
1134 ) AS
1135 /*
1137 || Created On : 16-JUN-2003
1138 || Purpose : Initialises the columns, Checks Constraints, Calls the
1139 || Trigger Handlers for the table, before any DML operation.
1140 || Known limitations, enhancements or remarks :
1141 || Change History :
1142 || Who When What
1143 || (reverse chronological order - newest change first)
1144 */
1145 BEGIN
1146
1147 set_column_values (
1148 p_action,
1149 x_rowid,
1150 x_panel_dtls_id,
1151 x_person_id,
1152 x_admission_appl_number,
1153 x_nominated_course_cd,
1154 x_sequence_number,
1155 x_panel_code,
1156 x_interview_date,
1157 x_interview_time,
1158 x_location_cd,
1159 x_room_id,
1160 x_final_decision_code,
1161 x_final_decision_type,
1162 x_final_decision_date,
1163 x_closed_flag,
1164 x_attribute_category,
1165 x_attribute1,
1166 x_attribute2,
1167 x_attribute3,
1168 x_attribute4,
1169 x_attribute5,
1170 x_attribute6,
1171 x_attribute7,
1172 x_attribute8,
1173 x_attribute9,
1174 x_attribute10,
1175 x_attribute11,
1176 x_attribute12,
1177 x_attribute13,
1178 x_attribute14,
1179 x_attribute15,
1180 x_attribute16,
1181 x_attribute17,
1182 x_attribute18,
1183 x_attribute19,
1184 x_attribute20,
1185 x_creation_date,
1186 x_created_by,
1187 x_last_update_date,
1188 x_last_updated_by,
1189 x_last_update_login
1190 );
1191
1192 IF (p_action = 'INSERT') THEN
1193 -- Call all the procedures related to Before Insert.
1194 IF ( get_pk_for_validation(
1195 new_references.panel_dtls_id
1196 )
1197 ) THEN
1198 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
1199 igs_ge_msg_stack.add;
1200 app_exception.raise_exception;
1201 END IF;
1202 beforerowinsertupdatedelete1( p_inserting => TRUE , p_updating => FALSE, p_deleting=> FALSE);
1203 check_uniqueness;
1204 check_parent_existance;
1205 ELSIF (p_action = 'UPDATE') THEN
1206 -- Call all the procedures related to Before Update.
1207 beforerowinsertupdatedelete1( p_inserting => FALSE , p_updating => TRUE, p_deleting=> FALSE);
1208 check_uniqueness;
1209 check_parent_existance;
1210 ELSIF (p_action = 'DELETE') THEN
1211 -- Call all the procedures related to Before Delete.
1212 beforerowinsertupdatedelete1( p_inserting => FALSE, p_updating => FALSE, p_deleting=> TRUE);
1213 check_child_existance;
1214 ELSIF (p_action = 'VALIDATE_INSERT') THEN
1215 -- Call all the procedures related to Before Insert.
1216 IF ( get_pk_for_validation (
1217 new_references.panel_dtls_id
1218 )
1219 ) THEN
1220 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
1221 igs_ge_msg_stack.add;
1222 app_exception.raise_exception;
1223 END IF;
1224 beforerowinsertupdatedelete1( p_inserting => TRUE , p_updating => FALSE, p_deleting=> FALSE);
1225 check_uniqueness;
1226 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
1227 beforerowinsertupdatedelete1( p_inserting => FALSE , p_updating => TRUE, p_deleting=> FALSE);
1228 check_uniqueness;
1229 ELSIF (p_action = 'VALIDATE_DELETE') THEN
1230 beforerowinsertupdatedelete1( p_inserting => FALSE, p_updating => FALSE, p_deleting=> TRUE);
1231 check_child_existance;
1232 END IF;
1233
1234 END before_dml;
1235
1236
1237 PROCEDURE insert_row (
1238 x_rowid IN OUT NOCOPY VARCHAR2,
1239 x_panel_dtls_id IN OUT NOCOPY NUMBER,
1240 x_person_id IN NUMBER,
1241 x_admission_appl_number IN NUMBER,
1242 x_nominated_course_cd IN VARCHAR2,
1243 x_sequence_number IN NUMBER,
1244 x_panel_code IN VARCHAR2,
1245 x_interview_date IN DATE,
1246 x_interview_time IN DATE,
1247 x_location_cd IN VARCHAR2,
1248 x_room_id IN NUMBER,
1249 x_final_decision_code IN VARCHAR2,
1250 x_final_decision_type IN VARCHAR2,
1251 x_final_decision_date IN DATE,
1252 x_closed_flag IN VARCHAR2,
1253 x_attribute_category IN VARCHAR2,
1254 x_attribute1 IN VARCHAR2,
1255 x_attribute2 IN VARCHAR2,
1256 x_attribute3 IN VARCHAR2,
1257 x_attribute4 IN VARCHAR2,
1258 x_attribute5 IN VARCHAR2,
1259 x_attribute6 IN VARCHAR2,
1260 x_attribute7 IN VARCHAR2,
1261 x_attribute8 IN VARCHAR2,
1262 x_attribute9 IN VARCHAR2,
1263 x_attribute10 IN VARCHAR2,
1264 x_attribute11 IN VARCHAR2,
1268 x_attribute15 IN VARCHAR2,
1265 x_attribute12 IN VARCHAR2,
1266 x_attribute13 IN VARCHAR2,
1267 x_attribute14 IN VARCHAR2,
1269 x_attribute16 IN VARCHAR2,
1270 x_attribute17 IN VARCHAR2,
1271 x_attribute18 IN VARCHAR2,
1272 x_attribute19 IN VARCHAR2,
1273 x_attribute20 IN VARCHAR2,
1274 x_mode IN VARCHAR2
1275 ) AS
1276 /*
1277 || Created By : Navin Sinha
1278 || Created On : 16-JUN-2003
1279 || Purpose : Handles the INSERT DML logic for the table.
1280 || Known limitations, enhancements or remarks :
1281 || Change History :
1282 || Who When What
1283 || (reverse chronological order - newest change first)
1284 */
1285
1286 x_last_update_date DATE;
1287 x_last_updated_by NUMBER;
1288 x_last_update_login NUMBER;
1289 l_mode VARCHAR2(1);
1290
1291 BEGIN
1292 l_mode := NVL(x_mode, 'R');
1293 x_last_update_date := SYSDATE;
1294 IF (l_mode = 'I') THEN
1295 x_last_updated_by := 1;
1296 x_last_update_login := 0;
1297 ELSIF (l_mode IN ('R','S')) THEN
1298 x_last_updated_by := fnd_global.user_id;
1299 IF (x_last_updated_by IS NULL) THEN
1300 x_last_updated_by := -1;
1301 END IF;
1302 x_last_update_login := fnd_global.login_id;
1303 IF (x_last_update_login IS NULL) THEN
1304 x_last_update_login := -1;
1305 END IF;
1306 ELSE
1307 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
1308 fnd_message.set_token ('ROUTINE', 'IGS_AD_PANEL_DTLS_PKG.INSERT_ROW');
1309 igs_ge_msg_stack.add;
1310 app_exception.raise_exception;
1311 END IF;
1312
1313 x_panel_dtls_id := NULL;
1314
1315 before_dml(
1316 p_action => 'INSERT',
1317 x_rowid => x_rowid,
1318 x_panel_dtls_id => x_panel_dtls_id,
1319 x_person_id => x_person_id,
1320 x_admission_appl_number => x_admission_appl_number,
1321 x_nominated_course_cd => x_nominated_course_cd,
1322 x_sequence_number => x_sequence_number,
1323 x_panel_code => x_panel_code,
1324 x_interview_date => x_interview_date,
1325 x_interview_time => x_interview_time,
1326 x_location_cd => x_location_cd,
1327 x_room_id => x_room_id,
1328 x_final_decision_code => x_final_decision_code,
1329 x_final_decision_type => x_final_decision_type,
1330 x_final_decision_date => x_final_decision_date,
1331 x_closed_flag => x_closed_flag,
1332 x_attribute_category => x_attribute_category,
1333 x_attribute1 => x_attribute1,
1334 x_attribute2 => x_attribute2,
1335 x_attribute3 => x_attribute3,
1336 x_attribute4 => x_attribute4,
1337 x_attribute5 => x_attribute5,
1338 x_attribute6 => x_attribute6,
1339 x_attribute7 => x_attribute7,
1340 x_attribute8 => x_attribute8,
1341 x_attribute9 => x_attribute9,
1342 x_attribute10 => x_attribute10,
1343 x_attribute11 => x_attribute11,
1344 x_attribute12 => x_attribute12,
1345 x_attribute13 => x_attribute13,
1346 x_attribute14 => x_attribute14,
1347 x_attribute15 => x_attribute15,
1348 x_attribute16 => x_attribute16,
1349 x_attribute17 => x_attribute17,
1350 x_attribute18 => x_attribute18,
1351 x_attribute19 => x_attribute19,
1352 x_attribute20 => x_attribute20,
1353 x_creation_date => x_last_update_date,
1354 x_created_by => x_last_updated_by,
1355 x_last_update_date => x_last_update_date,
1356 x_last_updated_by => x_last_updated_by,
1357 x_last_update_login => x_last_update_login
1358 );
1359
1360 IF (x_mode = 'S') THEN
1361 igs_sc_gen_001.set_ctx('R');
1362 END IF;
1363 INSERT INTO igs_ad_panel_dtls (
1364 panel_dtls_id,
1365 person_id,
1366 admission_appl_number,
1367 nominated_course_cd,
1368 sequence_number,
1369 panel_code,
1370 interview_date,
1371 interview_time,
1372 location_cd,
1373 room_id,
1374 final_decision_code,
1375 final_decision_type,
1376 final_decision_date,
1377 closed_flag,
1378 attribute_category,
1379 attribute1,
1380 attribute2,
1381 attribute3,
1385 attribute7,
1382 attribute4,
1383 attribute5,
1384 attribute6,
1386 attribute8,
1387 attribute9,
1388 attribute10,
1389 attribute11,
1390 attribute12,
1391 attribute13,
1392 attribute14,
1393 attribute15,
1394 attribute16,
1395 attribute17,
1396 attribute18,
1397 attribute19,
1398 attribute20,
1399 creation_date,
1400 created_by,
1401 last_update_date,
1402 last_updated_by,
1403 last_update_login
1404 ) VALUES (
1405 igs_ad_panel_dtls_s.NEXTVAL,
1406 new_references.person_id,
1407 new_references.admission_appl_number,
1408 new_references.nominated_course_cd,
1409 new_references.sequence_number,
1410 new_references.panel_code,
1411 new_references.interview_date,
1412 new_references.interview_time,
1413 new_references.location_cd,
1414 new_references.room_id,
1415 new_references.final_decision_code,
1416 new_references.final_decision_type,
1417 new_references.final_decision_date,
1418 new_references.closed_flag,
1419 new_references.attribute_category,
1420 new_references.attribute1,
1421 new_references.attribute2,
1422 new_references.attribute3,
1423 new_references.attribute4,
1424 new_references.attribute5,
1425 new_references.attribute6,
1426 new_references.attribute7,
1427 new_references.attribute8,
1428 new_references.attribute9,
1429 new_references.attribute10,
1430 new_references.attribute11,
1431 new_references.attribute12,
1432 new_references.attribute13,
1433 new_references.attribute14,
1434 new_references.attribute15,
1435 new_references.attribute16,
1436 new_references.attribute17,
1437 new_references.attribute18,
1438 new_references.attribute19,
1439 new_references.attribute20,
1440 x_last_update_date,
1441 x_last_updated_by,
1442 x_last_update_date,
1443 x_last_updated_by,
1444 x_last_update_login
1445 ) RETURNING ROWID, panel_dtls_id INTO x_rowid, x_panel_dtls_id;
1446 IF (x_mode = 'S') THEN
1447 igs_sc_gen_001.unset_ctx('R');
1448 END IF;
1449
1450
1451 -- Assign the panel members associated with the panel code to the Application Instance.
1452 afterinsertupdatedelete( p_inserting => TRUE , p_updating => FALSE, p_deleting=> FALSE, p_panel_dtls_id => x_panel_dtls_id);
1453 EXCEPTION
1454 WHEN OTHERS THEN
1455 IF (x_mode = 'S') THEN
1456 igs_sc_gen_001.unset_ctx('R');
1457 END IF;
1458 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
1459 -- Code to handle Security Policy error raised
1460 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1461 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1462 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1463 -- that the ownerof policy function does not have privilege to access.
1464 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1465 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1466 IGS_GE_MSG_STACK.ADD;
1467 app_exception.raise_exception;
1468 ELSE
1469 RAISE;
1470 END IF;
1471 END insert_row;
1472
1473
1474 PROCEDURE lock_row (
1475 x_rowid IN VARCHAR2,
1476 x_panel_dtls_id IN NUMBER,
1477 x_person_id IN NUMBER,
1478 x_admission_appl_number IN NUMBER,
1479 x_nominated_course_cd IN VARCHAR2,
1480 x_sequence_number IN NUMBER,
1481 x_panel_code IN VARCHAR2,
1482 x_interview_date IN DATE,
1483 x_interview_time IN DATE,
1484 x_location_cd IN VARCHAR2,
1485 x_room_id IN NUMBER,
1486 x_final_decision_code IN VARCHAR2,
1487 x_final_decision_type IN VARCHAR2,
1488 x_final_decision_date IN DATE,
1489 x_closed_flag IN VARCHAR2,
1490 x_attribute_category IN VARCHAR2,
1491 x_attribute1 IN VARCHAR2,
1492 x_attribute2 IN VARCHAR2,
1493 x_attribute3 IN VARCHAR2,
1494 x_attribute4 IN VARCHAR2,
1495 x_attribute5 IN VARCHAR2,
1496 x_attribute6 IN VARCHAR2,
1497 x_attribute7 IN VARCHAR2,
1498 x_attribute8 IN VARCHAR2,
1499 x_attribute9 IN VARCHAR2,
1500 x_attribute10 IN VARCHAR2,
1501 x_attribute11 IN VARCHAR2,
1502 x_attribute12 IN VARCHAR2,
1506 x_attribute16 IN VARCHAR2,
1503 x_attribute13 IN VARCHAR2,
1504 x_attribute14 IN VARCHAR2,
1505 x_attribute15 IN VARCHAR2,
1507 x_attribute17 IN VARCHAR2,
1508 x_attribute18 IN VARCHAR2,
1509 x_attribute19 IN VARCHAR2,
1510 x_attribute20 IN VARCHAR2
1511 ) AS
1512 /*
1513 || Created By : Navin Sinha
1514 || Created On : 16-JUN-2003
1515 || Purpose : Handles the LOCK mechanism for the table.
1516 || Known limitations, enhancements or remarks :
1517 || Change History :
1518 || Who When What
1519 || (reverse chronological order - newest change first)
1520 */
1521 CURSOR c1 IS
1522 SELECT
1523 person_id,
1524 admission_appl_number,
1525 nominated_course_cd,
1526 sequence_number,
1527 panel_code,
1528 interview_date,
1529 interview_time,
1530 location_cd,
1531 room_id,
1532 final_decision_code,
1533 final_decision_type,
1534 final_decision_date,
1535 closed_flag,
1536 attribute_category,
1537 attribute1,
1538 attribute2,
1539 attribute3,
1540 attribute4,
1541 attribute5,
1542 attribute6,
1543 attribute7,
1544 attribute8,
1545 attribute9,
1546 attribute10,
1547 attribute11,
1548 attribute12,
1549 attribute13,
1550 attribute14,
1551 attribute15,
1552 attribute16,
1553 attribute17,
1554 attribute18,
1555 attribute19,
1556 attribute20
1557 FROM igs_ad_panel_dtls
1558 WHERE rowid = x_rowid
1559 FOR UPDATE NOWAIT;
1560
1561 tlinfo c1%ROWTYPE;
1562
1563 BEGIN
1564
1565 OPEN c1;
1566 FETCH c1 INTO tlinfo;
1567 IF (c1%notfound) THEN
1568 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1569 igs_ge_msg_stack.add;
1570 CLOSE c1;
1571 app_exception.raise_exception;
1572 RETURN;
1573 END IF;
1574 CLOSE c1;
1575
1576 IF (
1577 (tlinfo.person_id = x_person_id)
1578 AND (tlinfo.admission_appl_number = x_admission_appl_number)
1579 AND (tlinfo.nominated_course_cd = x_nominated_course_cd)
1580 AND (tlinfo.sequence_number = x_sequence_number)
1581 AND (tlinfo.panel_code = x_panel_code)
1582 AND ((TRUNC(tlinfo.interview_date) = TRUNC(x_interview_date)) OR ((tlinfo.interview_date IS NULL) AND (X_interview_date IS NULL)))
1583 AND ((tlinfo.interview_time = x_interview_time) OR ((tlinfo.interview_time IS NULL) AND (X_interview_time IS NULL)))
1584 AND ((tlinfo.location_cd = x_location_cd) OR ((tlinfo.location_cd IS NULL) AND (X_location_cd IS NULL)))
1585 AND ((tlinfo.room_id = x_room_id) OR ((tlinfo.room_id IS NULL) AND (X_room_id IS NULL)))
1586 AND (tlinfo.final_decision_code = x_final_decision_code)
1587 AND (tlinfo.final_decision_type = x_final_decision_type)
1588 AND ((TRUNC(tlinfo.final_decision_date) = TRUNC(x_final_decision_date)) OR ((tlinfo.final_decision_date IS NULL) AND (X_final_decision_date IS NULL)))
1589 AND (tlinfo.closed_flag = x_closed_flag)
1590 AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (X_attribute_category IS NULL)))
1591 AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (X_attribute1 IS NULL)))
1592 AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (X_attribute2 IS NULL)))
1593 AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (X_attribute3 IS NULL)))
1594 AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (X_attribute4 IS NULL)))
1595 AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (X_attribute5 IS NULL)))
1596 AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (X_attribute6 IS NULL)))
1597 AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (X_attribute7 IS NULL)))
1598 AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (X_attribute8 IS NULL)))
1599 AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (X_attribute9 IS NULL)))
1600 AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (X_attribute10 IS NULL)))
1601 AND ((tlinfo.attribute11 = x_attribute11) OR ((tlinfo.attribute11 IS NULL) AND (X_attribute11 IS NULL)))
1602 AND ((tlinfo.attribute12 = x_attribute12) OR ((tlinfo.attribute12 IS NULL) AND (X_attribute12 IS NULL)))
1603 AND ((tlinfo.attribute13 = x_attribute13) OR ((tlinfo.attribute13 IS NULL) AND (X_attribute13 IS NULL)))
1604 AND ((tlinfo.attribute14 = x_attribute14) OR ((tlinfo.attribute14 IS NULL) AND (X_attribute14 IS NULL)))
1605 AND ((tlinfo.attribute15 = x_attribute15) OR ((tlinfo.attribute15 IS NULL) AND (X_attribute15 IS NULL)))
1606 AND ((tlinfo.attribute16 = x_attribute16) OR ((tlinfo.attribute16 IS NULL) AND (X_attribute16 IS NULL)))
1607 AND ((tlinfo.attribute17 = x_attribute17) OR ((tlinfo.attribute17 IS NULL) AND (X_attribute17 IS NULL)))
1608 AND ((tlinfo.attribute18 = x_attribute18) OR ((tlinfo.attribute18 IS NULL) AND (X_attribute18 IS NULL)))
1609 AND ((tlinfo.attribute19 = x_attribute19) OR ((tlinfo.attribute19 IS NULL) AND (X_attribute19 IS NULL)))
1610 AND ((tlinfo.attribute20 = x_attribute20) OR ((tlinfo.attribute20 IS NULL) AND (X_attribute20 IS NULL)))
1611 ) THEN
1612 NULL;
1613 ELSE
1614 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1615 igs_ge_msg_stack.add;
1616 app_exception.raise_exception;
1617 END IF;
1618
1619 RETURN;
1620
1621 END lock_row;
1622
1623
1624 PROCEDURE update_row (
1625 x_rowid IN VARCHAR2,
1626 x_panel_dtls_id IN NUMBER,
1627 x_person_id IN NUMBER,
1628 x_admission_appl_number IN NUMBER,
1629 x_nominated_course_cd IN VARCHAR2,
1630 x_sequence_number IN NUMBER,
1631 x_panel_code IN VARCHAR2,
1632 x_interview_date IN DATE,
1633 x_interview_time IN DATE,
1634 x_location_cd IN VARCHAR2,
1635 x_room_id IN NUMBER,
1636 x_final_decision_code IN VARCHAR2,
1637 x_final_decision_type IN VARCHAR2,
1638 x_final_decision_date IN DATE,
1639 x_closed_flag IN VARCHAR2,
1640 x_attribute_category IN VARCHAR2,
1641 x_attribute1 IN VARCHAR2,
1642 x_attribute2 IN VARCHAR2,
1643 x_attribute3 IN VARCHAR2,
1644 x_attribute4 IN VARCHAR2,
1645 x_attribute5 IN VARCHAR2,
1646 x_attribute6 IN VARCHAR2,
1647 x_attribute7 IN VARCHAR2,
1648 x_attribute8 IN VARCHAR2,
1649 x_attribute9 IN VARCHAR2,
1650 x_attribute10 IN VARCHAR2,
1651 x_attribute11 IN VARCHAR2,
1652 x_attribute12 IN VARCHAR2,
1653 x_attribute13 IN VARCHAR2,
1654 x_attribute14 IN VARCHAR2,
1655 x_attribute15 IN VARCHAR2,
1656 x_attribute16 IN VARCHAR2,
1657 x_attribute17 IN VARCHAR2,
1658 x_attribute18 IN VARCHAR2,
1659 x_attribute19 IN VARCHAR2,
1660 x_attribute20 IN VARCHAR2,
1661 x_mode IN VARCHAR2
1662 ) AS
1663 /*
1664 || Created By : Navin Sinha
1665 || Created On : 16-JUN-2003
1666 || Purpose : Handles the UPDATE DML logic for the table.
1667 || Known limitations, enhancements or remarks :
1668 || Change History :
1669 || Who When What
1670 || ravishar 05/25/05 Security related changes
1671 || (reverse chronological order - newest change first)
1672 */
1673 x_last_update_date DATE ;
1674 x_last_updated_by NUMBER;
1675 x_last_update_login NUMBER;
1676 l_mode VARCHAR2(1);
1677
1678 BEGIN
1679 l_mode := NVL(x_mode, 'R');
1680 x_last_update_date := SYSDATE;
1681 IF (l_mode = 'I') THEN
1682 x_last_updated_by := 1;
1683 x_last_update_login := 0;
1684 ELSIF (l_mode IN ('R','S')) THEN
1685 x_last_updated_by := fnd_global.user_id;
1686 IF x_last_updated_by IS NULL THEN
1687 x_last_updated_by := -1;
1688 END IF;
1689 x_last_update_login := fnd_global.login_id;
1690 IF (x_last_update_login IS NULL) THEN
1691 x_last_update_login := -1;
1692 END IF;
1693 ELSE
1694 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1695 fnd_message.set_token ('ROUTINE', 'IGS_AD_PANEL_DTLS_PKG.UPDATE_ROW');
1696 igs_ge_msg_stack.add;
1697 app_exception.raise_exception;
1698 END IF;
1699
1700 before_dml(
1701 p_action => 'UPDATE',
1702 x_rowid => x_rowid,
1703 x_panel_dtls_id => x_panel_dtls_id,
1704 x_person_id => x_person_id,
1705 x_admission_appl_number => x_admission_appl_number,
1706 x_nominated_course_cd => x_nominated_course_cd,
1707 x_sequence_number => x_sequence_number,
1708 x_panel_code => x_panel_code,
1709 x_interview_date => x_interview_date,
1710 x_interview_time => x_interview_time,
1711 x_location_cd => x_location_cd,
1712 x_room_id => x_room_id,
1713 x_final_decision_code => x_final_decision_code,
1714 x_final_decision_type => x_final_decision_type,
1715 x_final_decision_date => x_final_decision_date,
1716 x_closed_flag => x_closed_flag,
1717 x_attribute_category => x_attribute_category,
1718 x_attribute1 => x_attribute1,
1719 x_attribute2 => x_attribute2,
1720 x_attribute3 => x_attribute3,
1724 x_attribute7 => x_attribute7,
1721 x_attribute4 => x_attribute4,
1722 x_attribute5 => x_attribute5,
1723 x_attribute6 => x_attribute6,
1725 x_attribute8 => x_attribute8,
1726 x_attribute9 => x_attribute9,
1727 x_attribute10 => x_attribute10,
1728 x_attribute11 => x_attribute11,
1729 x_attribute12 => x_attribute12,
1730 x_attribute13 => x_attribute13,
1731 x_attribute14 => x_attribute14,
1732 x_attribute15 => x_attribute15,
1733 x_attribute16 => x_attribute16,
1734 x_attribute17 => x_attribute17,
1735 x_attribute18 => x_attribute18,
1736 x_attribute19 => x_attribute19,
1737 x_attribute20 => x_attribute20,
1738 x_creation_date => x_last_update_date,
1739 x_created_by => x_last_updated_by,
1740 x_last_update_date => x_last_update_date,
1741 x_last_updated_by => x_last_updated_by,
1742 x_last_update_login => x_last_update_login
1743 );
1744
1745 IF (x_mode = 'S') THEN
1746 igs_sc_gen_001.set_ctx('R');
1747 END IF;
1748 UPDATE igs_ad_panel_dtls
1749 SET
1750 person_id = new_references.person_id,
1751 admission_appl_number = new_references.admission_appl_number,
1752 nominated_course_cd = new_references.nominated_course_cd,
1753 sequence_number = new_references.sequence_number,
1754 panel_code = new_references.panel_code,
1755 interview_date = new_references.interview_date,
1756 interview_time = new_references.interview_time,
1757 location_cd = new_references.location_cd,
1758 room_id = new_references.room_id,
1759 final_decision_code = new_references.final_decision_code,
1760 final_decision_type = new_references.final_decision_type,
1761 final_decision_date = new_references.final_decision_date,
1762 closed_flag = new_references.closed_flag,
1763 attribute_category = new_references.attribute_category,
1764 attribute1 = new_references.attribute1,
1765 attribute2 = new_references.attribute2,
1766 attribute3 = new_references.attribute3,
1767 attribute4 = new_references.attribute4,
1768 attribute5 = new_references.attribute5,
1769 attribute6 = new_references.attribute6,
1770 attribute7 = new_references.attribute7,
1771 attribute8 = new_references.attribute8,
1772 attribute9 = new_references.attribute9,
1773 attribute10 = new_references.attribute10,
1774 attribute11 = new_references.attribute11,
1775 attribute12 = new_references.attribute12,
1776 attribute13 = new_references.attribute13,
1777 attribute14 = new_references.attribute14,
1778 attribute15 = new_references.attribute15,
1779 attribute16 = new_references.attribute16,
1780 attribute17 = new_references.attribute17,
1781 attribute18 = new_references.attribute18,
1782 attribute19 = new_references.attribute19,
1783 attribute20 = new_references.attribute20,
1784 last_update_date = x_last_update_date,
1785 last_updated_by = x_last_updated_by,
1786 last_update_login = x_last_update_login
1787 WHERE rowid = x_rowid;
1788
1789 IF (SQL%NOTFOUND) THEN
1790 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1791 igs_ge_msg_stack.add;
1792 IF (x_mode = 'S') THEN
1793 igs_sc_gen_001.unset_ctx('R');
1794 END IF;
1795 app_exception.raise_exception;
1796 END IF;
1797 IF (x_mode = 'S') THEN
1798 igs_sc_gen_001.unset_ctx('R');
1799 END IF;
1800
1801
1802 -- When the final screening/interview decision is changed, a record needs to get inserted into the history table.
1803 afterinsertupdatedelete( p_inserting => FALSE , p_updating => TRUE, p_deleting=> FALSE, p_panel_dtls_id => new_references.panel_dtls_id);
1804 EXCEPTION
1805 WHEN OTHERS THEN
1806 IF (x_mode = 'S') THEN
1807 igs_sc_gen_001.unset_ctx('R');
1808 END IF;
1809 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
1810 -- Code to handle Security Policy error raised
1811 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1812 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1813 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1814 -- that the ownerof policy function does not have privilege to access.
1815 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1816 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1817 IGS_GE_MSG_STACK.ADD;
1818 app_exception.raise_exception;
1819 ELSE
1820 RAISE;
1821 END IF;
1822 END update_row;
1823
1824
1828 x_person_id IN NUMBER,
1825 PROCEDURE add_row (
1826 x_rowid IN OUT NOCOPY VARCHAR2,
1827 x_panel_dtls_id IN OUT NOCOPY NUMBER,
1829 x_admission_appl_number IN NUMBER,
1830 x_nominated_course_cd IN VARCHAR2,
1831 x_sequence_number IN NUMBER,
1832 x_panel_code IN VARCHAR2,
1833 x_interview_date IN DATE,
1834 x_interview_time IN DATE,
1835 x_location_cd IN VARCHAR2,
1836 x_room_id IN NUMBER,
1837 x_final_decision_code IN VARCHAR2,
1838 x_final_decision_type IN VARCHAR2,
1839 x_final_decision_date IN DATE,
1840 x_closed_flag IN VARCHAR2,
1841 x_attribute_category IN VARCHAR2,
1842 x_attribute1 IN VARCHAR2,
1843 x_attribute2 IN VARCHAR2,
1844 x_attribute3 IN VARCHAR2,
1845 x_attribute4 IN VARCHAR2,
1846 x_attribute5 IN VARCHAR2,
1847 x_attribute6 IN VARCHAR2,
1848 x_attribute7 IN VARCHAR2,
1849 x_attribute8 IN VARCHAR2,
1850 x_attribute9 IN VARCHAR2,
1851 x_attribute10 IN VARCHAR2,
1852 x_attribute11 IN VARCHAR2,
1853 x_attribute12 IN VARCHAR2,
1854 x_attribute13 IN VARCHAR2,
1855 x_attribute14 IN VARCHAR2,
1856 x_attribute15 IN VARCHAR2,
1857 x_attribute16 IN VARCHAR2,
1858 x_attribute17 IN VARCHAR2,
1859 x_attribute18 IN VARCHAR2,
1860 x_attribute19 IN VARCHAR2,
1861 x_attribute20 IN VARCHAR2,
1862 x_mode IN VARCHAR2
1863 ) AS
1864 /*
1865 || Created By : Navin Sinha
1866 || Created On : 16-JUN-2003
1867 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1868 || Known limitations, enhancements or remarks :
1869 || Change History :
1870 || Who When What
1871 || (reverse chronological order - newest change first)
1872 */
1873 CURSOR c1 IS
1874 SELECT rowid
1875 FROM igs_ad_panel_dtls
1876 WHERE panel_dtls_id = x_panel_dtls_id;
1877 l_mode VARCHAR2(1);
1878 BEGIN
1879 l_mode := NVL(x_mode, 'R');
1880 OPEN c1;
1881 FETCH c1 INTO x_rowid;
1882 IF (c1%NOTFOUND) THEN
1883 CLOSE c1;
1884
1885 insert_row (
1886 x_rowid,
1887 x_panel_dtls_id,
1888 x_person_id,
1889 x_admission_appl_number,
1890 x_nominated_course_cd,
1891 x_sequence_number,
1892 x_panel_code,
1893 x_interview_date,
1894 x_interview_time,
1895 x_location_cd,
1896 x_room_id,
1897 x_final_decision_code,
1898 x_final_decision_type,
1899 x_final_decision_date,
1900 x_closed_flag,
1901 x_attribute_category,
1902 x_attribute1,
1903 x_attribute2,
1904 x_attribute3,
1905 x_attribute4,
1906 x_attribute5,
1907 x_attribute6,
1908 x_attribute7,
1909 x_attribute8,
1910 x_attribute9,
1911 x_attribute10,
1912 x_attribute11,
1913 x_attribute12,
1914 x_attribute13,
1915 x_attribute14,
1916 x_attribute15,
1917 x_attribute16,
1918 x_attribute17,
1919 x_attribute18,
1920 x_attribute19,
1921 x_attribute20,
1922 l_mode
1923 );
1924 RETURN;
1925 END IF;
1926 CLOSE c1;
1927
1928 update_row (
1929 x_rowid,
1930 x_panel_dtls_id,
1931 x_person_id,
1932 x_admission_appl_number,
1933 x_nominated_course_cd,
1934 x_sequence_number,
1935 x_panel_code,
1936 x_interview_date,
1937 x_interview_time,
1938 x_location_cd,
1939 x_room_id,
1940 x_final_decision_code,
1941 x_final_decision_type,
1942 x_final_decision_date,
1943 x_closed_flag,
1944 x_attribute_category,
1945 x_attribute1,
1946 x_attribute2,
1947 x_attribute3,
1948 x_attribute4,
1949 x_attribute5,
1950 x_attribute6,
1951 x_attribute7,
1952 x_attribute8,
1953 x_attribute9,
1954 x_attribute10,
1955 x_attribute11,
1956 x_attribute12,
1957 x_attribute13,
1958 x_attribute14,
1959 x_attribute15,
1960 x_attribute16,
1961 x_attribute17,
1962 x_attribute18,
1963 x_attribute19,
1964 x_attribute20,
1965 l_mode
1966 );
1967
1968 END add_row;
1969
1970
1971 PROCEDURE delete_row (
1972 x_rowid IN VARCHAR2,
1973 x_mode IN VARCHAR2
1974 ) AS
1975 /*
1976 || Created By : Navin Sinha
1977 || Created On : 16-JUN-2003
1978 || Purpose : Handles the DELETE DML logic for the table.
1979 || Known limitations, enhancements or remarks :
1980 || Change History :
1981 || Who When What
1985 BEGIN
1982 || ravishar 05/25/05 Security related changes
1983 || (reverse chronological order - newest change first)
1984 */
1986
1987 before_dml (
1988 p_action => 'DELETE',
1989 x_rowid => x_rowid
1990 );
1991
1992 IF (x_mode = 'S') THEN
1993 igs_sc_gen_001.set_ctx('R');
1994 END IF;
1995 DELETE FROM igs_ad_panel_dtls
1996 WHERE rowid = x_rowid;
1997
1998 IF (SQL%NOTFOUND) THEN
1999 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
2000 igs_ge_msg_stack.add;
2001 IF (x_mode = 'S') THEN
2002 igs_sc_gen_001.unset_ctx('R');
2003 END IF;
2004 app_exception.raise_exception;
2005 END IF;
2006 IF (x_mode = 'S') THEN
2007 igs_sc_gen_001.unset_ctx('R');
2008 END IF;
2009
2010
2011 EXCEPTION
2012 WHEN OTHERS THEN
2013 IF (x_mode = 'S') THEN
2014 igs_sc_gen_001.unset_ctx('R');
2015 END IF;
2016 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
2017 -- Code to handle Security Policy error raised
2018 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
2019 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
2020 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
2021 -- that the ownerof policy function does not have privilege to access.
2022 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
2023 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
2024 IGS_GE_MSG_STACK.ADD;
2025 app_exception.raise_exception;
2026 ELSE
2027 RAISE;
2028 END IF;
2029 END delete_row;
2030
2031
2032 END igs_ad_panel_dtls_pkg;
[Home] [Help]