1 PACKAGE BODY igs_ad_pnmembr_dtls_pkg AS
2 /* $Header: IGSAIH2B.pls 120.4 2006/01/29 22:29:30 pfotedar ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_ad_pnmembr_dtls%ROWTYPE;
6 new_references igs_ad_pnmembr_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_role_type_code IN VARCHAR2,
13 x_member_person_id IN NUMBER,
14 x_interview_date IN DATE,
15 x_interview_time IN DATE,
16 x_location_cd IN VARCHAR2,
17 x_room_id IN NUMBER,
18 x_member_decision_code IN VARCHAR2,
19 x_member_decision_type IN VARCHAR2,
20 x_member_decision_date IN DATE,
21 x_attribute_category IN VARCHAR2,
22 x_attribute1 IN VARCHAR2,
23 x_attribute2 IN VARCHAR2,
24 x_attribute3 IN VARCHAR2,
25 x_attribute4 IN VARCHAR2,
26 x_attribute5 IN VARCHAR2,
27 x_attribute6 IN VARCHAR2,
28 x_attribute7 IN VARCHAR2,
29 x_attribute8 IN VARCHAR2,
30 x_attribute9 IN VARCHAR2,
31 x_attribute10 IN VARCHAR2,
32 x_attribute11 IN VARCHAR2,
33 x_attribute12 IN VARCHAR2,
34 x_attribute13 IN VARCHAR2,
35 x_attribute14 IN VARCHAR2,
36 x_attribute15 IN VARCHAR2,
37 x_attribute16 IN VARCHAR2,
38 x_attribute17 IN VARCHAR2,
39 x_attribute18 IN VARCHAR2,
40 x_attribute19 IN VARCHAR2,
41 x_attribute20 IN VARCHAR2,
42 x_creation_date IN DATE,
43 x_created_by IN NUMBER,
44 x_last_update_date IN DATE,
45 x_last_updated_by IN NUMBER,
46 x_last_update_login IN NUMBER
47 ) AS
48 /*
49 || Created By : Navin Sinha
50 || Created On : 16-JUN-2003
51 || Purpose : Initialises the Old and New references for the columns of the table.
52 || Known limitations, enhancements or remarks :
53 || Change History :
54 || Who When What
55 || (reverse chronological order - newest change first)
56 */
57
58 CURSOR cur_old_ref_values IS
59 SELECT *
60 FROM igs_ad_pnmembr_dtls
61 WHERE rowid = x_rowid;
62
63 BEGIN
64
65 l_rowid := x_rowid;
66 old_references := NULL;
67
68 -- Code for setting the Old and New Reference Values.
69 -- Populate Old Values.
70 OPEN cur_old_ref_values;
71 FETCH cur_old_ref_values INTO old_references;
72 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
73 CLOSE cur_old_ref_values;
74 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
75 igs_ge_msg_stack.add;
76 app_exception.raise_exception;
77 RETURN;
78 END IF;
79 CLOSE cur_old_ref_values;
80
81 -- Populate New Values.
82 new_references.panel_dtls_id := x_panel_dtls_id;
83 new_references.role_type_code := x_role_type_code;
84 new_references.member_person_id := x_member_person_id;
85 new_references.interview_date := TRUNC(x_interview_date);
86 new_references.interview_time := x_interview_time;
87 new_references.location_cd := x_location_cd;
88 new_references.room_id := x_room_id;
89 new_references.member_decision_code := x_member_decision_code;
90 new_references.member_decision_type := x_member_decision_type;
91 new_references.member_decision_date := TRUNC(x_member_decision_date);
92 new_references.attribute_category := x_attribute_category;
93 new_references.attribute1 := x_attribute1;
94 new_references.attribute2 := x_attribute2;
95 new_references.attribute3 := x_attribute3;
96 new_references.attribute4 := x_attribute4;
97 new_references.attribute5 := x_attribute5;
98 new_references.attribute6 := x_attribute6;
99 new_references.attribute7 := x_attribute7;
100 new_references.attribute8 := x_attribute8;
101 new_references.attribute9 := x_attribute9;
102 new_references.attribute10 := x_attribute10;
103 new_references.attribute11 := x_attribute11;
104 new_references.attribute12 := x_attribute12;
105 new_references.attribute13 := x_attribute13;
106 new_references.attribute14 := x_attribute14;
107 new_references.attribute15 := x_attribute15;
108 new_references.attribute16 := x_attribute16;
109 new_references.attribute17 := x_attribute17;
110 new_references.attribute18 := x_attribute18;
111 new_references.attribute19 := x_attribute19;
112 new_references.attribute20 := x_attribute20;
113
114 IF (p_action = 'UPDATE') THEN
115 new_references.creation_date := old_references.creation_date;
116 new_references.created_by := old_references.created_by;
117 ELSE
118 new_references.creation_date := x_creation_date;
119 new_references.created_by := x_created_by;
120 END IF;
121
122 new_references.last_update_date := x_last_update_date;
123 new_references.last_updated_by := x_last_updated_by;
124 new_references.last_update_login := x_last_update_login;
125
126 END set_column_values;
127
128
129 PROCEDURE check_parent_existance AS
130 /*
131 || Created By : Navin Sinha
132 || Created On : 16-JUN-2003
133 || Purpose : Checks for the existance of Parent records.
134 || Known limitations, enhancements or remarks :
135 || Change History :
136 || Who When What
137 || (reverse chronological order - newest change first)
138 */
139 BEGIN
140
141 IF (((old_references.panel_dtls_id = new_references.panel_dtls_id)) OR
142 ((new_references.panel_dtls_id IS NULL))) THEN
143 NULL;
144 ELSIF NOT igs_ad_panel_dtls_pkg.get_pk_for_validation (
145 new_references.panel_dtls_id
146 ) THEN
147 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
148 igs_ge_msg_stack.add;
149 app_exception.raise_exception;
150 END IF;
151
152 IF (((old_references.member_decision_code = new_references.member_decision_code) AND
153 (old_references.member_decision_type = new_references.member_decision_type)) OR
154 ((new_references.member_decision_code IS NULL) OR
155 (new_references.member_decision_type IS NULL))) THEN
156 NULL;
157 ELSIF NOT igs_ad_code_classes_pkg.get_uk_For_validation (
158 new_references.member_decision_code,
159 new_references.member_decision_type ,
160 'N'
161 ) THEN
162 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
163 igs_ge_msg_stack.add;
164 app_exception.raise_exception;
165 END IF;
166
167 IF (((old_references.location_cd = new_references.location_cd)) OR
168 ((new_references.location_cd IS NULL))) THEN
169 NULL;
170 ELSIF NOT igs_ad_location_pkg.get_pk_for_validation (
171 new_references.location_cd ,
172 'N'
173 ) THEN
174 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
175 igs_ge_msg_stack.add;
176 app_exception.raise_exception;
177 END IF;
178
179 IF (((old_references.room_id = new_references.room_id)) OR
180 ((new_references.room_id IS NULL))) THEN
181 NULL;
182 ELSIF NOT igs_ad_room_pkg.get_pk_for_validation (
183 new_references.room_id ,
184 'N'
185 ) THEN
186 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
187 igs_ge_msg_stack.add;
188 app_exception.raise_exception;
189 END IF;
190
191 IF (((old_references.member_person_id = new_references.member_person_id)) OR
192 ((new_references.member_person_id IS NULL))) THEN
193 NULL;
194 ELSE
195 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
196 new_references.member_person_id
197 )THEN
198 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
199 IGS_AD_GEN_001.SET_TOKEN('From IGS_PE_PERSON ->Parameter: Person_Id ');
200 IGS_GE_MSG_STACK.ADD;
201 APP_EXCEPTION.RAISE_EXCEPTION;
202 END IF;
203 END IF;
204
205
206 END check_parent_existance;
207
208
209 PROCEDURE check_child_existance AS
210 /*
211 || Created By : Navin Sinha
212 || Created On : 16-JUN-2003
213 || Purpose : Checks for the existance of Child records.
214 || Known limitations, enhancements or remarks :
215 || Change History :
216 || Who When What
217 || (reverse chronological order - newest change first)
218 */
219 BEGIN
220 igs_ad_pnmembr_nots_pkg.get_fk_igs_ad_pnmembr_dtls (
221 old_references.panel_dtls_id,
222 old_references.member_person_id
223 );
224 END check_child_existance;
225
226
227 FUNCTION get_pk_for_validation (
228 x_panel_dtls_id IN NUMBER,
229 x_member_person_id IN NUMBER
230 ) RETURN BOOLEAN AS
231 /*
232 || Created By : Navin Sinha
233 || Created On : 16-JUN-2003
234 || Purpose : Validates the Primary Key of the table.
235 || Known limitations, enhancements or remarks :
236 || Change History :
237 || Who When What
238 || (reverse chronological order - newest change first)
239 */
240 CURSOR cur_rowid IS
241 SELECT rowid
242 FROM igs_ad_pnmembr_dtls
243 WHERE panel_dtls_id = x_panel_dtls_id
244 AND member_person_id = x_member_person_id
245 FOR UPDATE NOWAIT;
246
247 lv_rowid cur_rowid%RowType;
248
249 BEGIN
250
251 OPEN cur_rowid;
252 FETCH cur_rowid INTO lv_rowid;
253 IF (cur_rowid%FOUND) THEN
254 CLOSE cur_rowid;
255 RETURN(TRUE);
256 ELSE
257 CLOSE cur_rowid;
258 RETURN(FALSE);
259 END IF;
260
261 END get_pk_for_validation;
262
263
264 PROCEDURE get_fk_igs_ad_panel_dtls (
265 x_panel_dtls_id IN NUMBER
266 ) AS
267 /*
268 || Created By : Navin Sinha
269 || Created On : 16-JUN-2003
270 || Purpose : Validates the Foreign Keys for the table.
271 || Known limitations, enhancements or remarks :
272 || Change History :
273 || Who When What
274 || (reverse chronological order - newest change first)
275 */
276 CURSOR cur_rowid IS
277 SELECT rowid
278 FROM igs_ad_pnmembr_dtls
279 WHERE ((panel_dtls_id = x_panel_dtls_id));
280
281 lv_rowid cur_rowid%RowType;
282
283 BEGIN
284
285 OPEN cur_rowid;
286 FETCH cur_rowid INTO lv_rowid;
287 IF (cur_rowid%FOUND) THEN
288 CLOSE cur_rowid;
289 fnd_message.set_name ('IGS', 'IGS_AD_PNLMBMR_PNDTLS_FK');
290 igs_ge_msg_stack.add;
291 app_exception.raise_exception;
292 RETURN;
293 END IF;
294 CLOSE cur_rowid;
295
296 END get_fk_igs_ad_panel_dtls;
297
298
299 PROCEDURE get_ufk_igs_ad_code_classes (
300 x_name IN VARCHAR2,
301 x_class IN VARCHAR2
302 ) AS
303 /*
304 || Created By : Navin Sinha
305 || Created On : 16-JUN-2003
306 || Purpose : Validates the Foreign Keys for the table.
307 || Known limitations, enhancements or remarks :
308 || Change History :
309 || Who When What
310 || (reverse chronological order - newest change first)
311 */
312 CURSOR cur_rowid IS
313 SELECT rowid
314 FROM igs_ad_pnmembr_dtls
315 WHERE ((member_decision_code = x_name) AND
316 (member_decision_type = x_class));
317
318 lv_rowid cur_rowid%RowType;
319
320 BEGIN
321
322 OPEN cur_rowid;
323 FETCH cur_rowid INTO lv_rowid;
324 IF (cur_rowid%FOUND) THEN
325 CLOSE cur_rowid;
326 fnd_message.set_name ('IGS', 'IGS_AD_PNLMBMR_CODE_CLS_FK');
327 igs_ge_msg_stack.add;
328 app_exception.raise_exception;
329 RETURN;
330 END IF;
331 CLOSE cur_rowid;
332
333 END get_ufk_igs_ad_code_classes;
334
335
336 PROCEDURE get_fk_igs_ad_location (
337 x_location_cd IN VARCHAR2
338 ) AS
339 /*
340 || Created By : Navin Sinha
341 || Created On : 16-JUN-2003
342 || Purpose : Validates the Foreign Keys for the table.
343 || Known limitations, enhancements or remarks :
344 || Change History :
345 || Who When What
346 || (reverse chronological order - newest change first)
347 */
348 CURSOR cur_rowid IS
349 SELECT rowid
350 FROM igs_ad_pnmembr_dtls
351 WHERE ((location_cd = x_location_cd));
352
353 lv_rowid cur_rowid%RowType;
354
355 BEGIN
356
357 OPEN cur_rowid;
358 FETCH cur_rowid INTO lv_rowid;
359 IF (cur_rowid%FOUND) THEN
360 CLOSE cur_rowid;
361 fnd_message.set_name ('IGS', 'IGS_AD_PNLMBMR_LOC_FK');
362 igs_ge_msg_stack.add;
363 app_exception.raise_exception;
364 RETURN;
365 END IF;
366 CLOSE cur_rowid;
367
368 END get_fk_igs_ad_location;
369
370
371 PROCEDURE get_fk_igs_ad_room (
372 x_room_id IN NUMBER
373 ) AS
374 /*
375 || Created By : Navin Sinha
376 || Created On : 16-JUN-2003
377 || Purpose : Validates the Foreign Keys for the table.
378 || Known limitations, enhancements or remarks :
379 || Change History :
380 || Who When What
381 || (reverse chronological order - newest change first)
382 */
383 CURSOR cur_rowid IS
384 SELECT rowid
385 FROM igs_ad_pnmembr_dtls
386 WHERE ((room_id = x_room_id));
387
388 lv_rowid cur_rowid%RowType;
389
390 BEGIN
391
392 OPEN cur_rowid;
393 FETCH cur_rowid INTO lv_rowid;
394 IF (cur_rowid%FOUND) THEN
395 CLOSE cur_rowid;
396 fnd_message.set_name ('IGS', 'IGS_AD_PNLMBMR_ROOM_FK');
397 igs_ge_msg_stack.add;
398 app_exception.raise_exception;
399 RETURN;
400 END IF;
401 CLOSE cur_rowid;
402
403 END get_fk_igs_ad_room;
404
405 PROCEDURE beforerowinsertupdatedelete1 (
406 p_inserting BOOLEAN,
407 p_updating BOOLEAN,
408 p_deleting BOOLEAN ) IS
409
410 ----------------------------------------------------------------
411 --Created by : Navin Sinha
412 --Date created: 16-Jun-03
413 --
414 --Purpose: BUG NO : 1366894 - Interview Build.
415 --
416 --
417 --Known limitations/enhancements and/or remarks:
418 --
419 --Change History:
420 --Who When What
421 ----------------------------------------------------------------
422 -- Cursor to check if person type of interviewer exists.
423 CURSOR c_interviewer_exist IS
424 SELECT 'x'
425 FROM IGS_PE_TYP_INSTANCES_ALL TI,
426 IGS_PE_PERSON_TYPES PT
427 WHERE ti.person_id = new_references.member_person_id
428 AND pt.system_type = 'INTERVIEWER'
429 AND (ti.end_date is NULL OR (ti.end_date IS NOT NULL AND trunc(ti.end_date) > trunc(SYSDATE)))
430 AND TI.PERSON_TYPE_CODE = PT.PERSON_TYPE_CODE;
431
432 l_interviewer_exist VARCHAR2(1);
433
434 -- Cursor to check the application date of the application
435 CURSOR c_appl_dt IS
436 SELECT appl_dt
437 FROM igs_ad_panel_dtls pd,
438 igs_ad_appl aa
439 WHERE pd.panel_dtls_id = new_references.panel_dtls_id
440 AND pd.admission_appl_number = aa.admission_appl_number
441 AND pd.person_id = aa.person_id;
442
443 l_appl_dt igs_ad_appl.appl_dt%TYPE;
444
445 -- Cursor to check final decision associated to panel code.
446 CURSOR c_chk_final_decision (cp_panel_dtls_id igs_ad_panel_dtls.panel_dtls_id%TYPE) IS
447 SELECT *
448 FROM igs_ad_panel_dtls
449 WHERE panel_dtls_id = cp_panel_dtls_id;
450
451 rec_chk_final_decision c_chk_final_decision%ROWTYPE;
452 l_final_decision igs_ad_code_classes.system_status%TYPE;
453
454 l_member_decision igs_ad_code_classes.system_status%TYPE;
455 l_old_member_decision igs_ad_code_classes.system_status%TYPE;
456
457 -- Cursor to check if member Decisions is mapped to the system defaulted Decisions of 'PENDING'
458 CURSOR cur_dflt_panl_cd IS
459 SELECT *
460 FROM igs_ad_code_classes
461 WHERE system_status = 'PENDING'
462 AND name = new_references.member_decision_code
463 AND class = new_references.member_decision_type
464 AND closed_ind = 'N'
465 AND CLASS_TYPE_CODE='ADM_CODE_CLASSES';
466
467 rec_dflt_panl_cd cur_dflt_panl_cd%ROWTYPE;
468
469 CURSOR c_instance_dets IS
470 SELECT person_id,admission_appl_number,nominated_course_Cd,sequence_number
471 FROM igs_ad_panel_dtls pdtls,
472 igs_Ad_pnmembr_Dtls pmbr
473 WHERE pdtls.panel_Dtls_id = pmbr.panel_dtls_id
474 AND pmbr.panel_dtls_id = NVL(old_references.panel_dtls_id, new_references.panel_dtls_id);
475
476 instance_dets_rec c_instance_dets%ROWTYPE;
477
478 CURSOR c_intvw_pnl_exsts (cp_instance_rec c_instance_dets%ROWTYPE ) IS
479 SELECT 'X'
480 FROM igs_ad_intvw_pnls pnls,
481 igs_ad_panel_dtls pdtls
482 WHERE pnls.panel_type_code='INTERVIEW'
483 AND pnls.panel_code = pdtls.panel_code
484 AND pdtls.person_id = cp_instance_rec.person_id
485 AND pdtls.admission_appl_number = cp_instance_rec.admission_appl_number
486 AND pdtls.nominated_course_Cd = cp_instance_rec.nominated_course_Cd
487 AND pdtls.sequence_number = cp_instance_rec.sequence_number;
488
489 intvw_pnl_exsts_rec c_intvw_pnl_exsts%ROWTYPE;
490
491 -- Cursor to get the system defaulted Decisions mapped to 'PENDING'
492 CURSOR cur_dflt_panlm_cd(cp_panel_type_code igs_ad_intvw_pnls.panel_type_code%TYPE) IS
493 SELECT *
494 FROM igs_ad_code_classes
495 WHERE system_status = 'PENDING'
496 AND class = cp_panel_type_code --'INTERVIEW', 'SCREENING', 'FINAL_SCREENING', 'FINAL_INTERVIEW'
497 AND NVL(system_default, 'N') = 'Y'
498 AND closed_ind = 'N'
499 AND CLASS_TYPE_CODE='ADM_CODE_CLASSES';
500
501 rec_dflt_panlm_cd cur_dflt_panlm_cd%ROWTYPE;
502
503 --get the panel type code
504 CURSOR c_panel_type_code IS
505 SELECT ip.panel_type_Code
506 FROM igs_ad_intvw_pnls ip,
507 igs_ad_panel_dtls pd
508 WHERE ip.panel_code = pd.panel_code
509 AND pd.panel_dtls_id = new_references.panel_dtls_id;
510
511 l_panel_type_Code igs_ad_intvw_pnls.panel_type_Code%TYPE;
512
513 -- Cursor to get the meaning for lookup code
514 CURSOR c_lkup_cd_mean(cp_lookup_type igs_lookups_view.lookup_type%TYPE, cp_lookup_code igs_lookups_view.lookup_code%TYPE) IS
515 SELECT meaning
516 FROM igs_lookups_view
517 WHERE lookup_type = cp_lookup_type
518 AND lookup_code = cp_lookup_code;
519
520 l_class_meaning igs_lookups_view.meaning%TYPE;
521 l_sys_stat_meaning igs_lookups_view.meaning%TYPE;
522 l_dec_type igs_ad_code_classes.class%TYPE;
523
524 BEGIN
525 IF NVL(p_inserting,FALSE) THEN
526 -- A Person added to a panel must have the system person type of Interviewer. Else raise an error message.
527 OPEN c_interviewer_exist;
528 FETCH c_interviewer_exist INTO l_interviewer_exist;
529 IF c_interviewer_exist%NOTFOUND THEN
530 CLOSE c_interviewer_exist;
531 fnd_message.set_name('IGS','IGS_AD_NO_INTERVIEWER_PTY'); -- Message: The person you are adding does not have the person type of Interviewer.
532 igs_ge_msg_stack.add;
533 app_exception.raise_exception;
534 END IF;
535 IF c_interviewer_exist%ISOPEN THEN
536 CLOSE c_interviewer_exist;
537 END IF;
538
539 IF new_references.member_decision_code IS NULL THEN
540 --get the panel type code
541 OPEN c_panel_type_code;
542 FETCH c_panel_type_code INTO l_panel_type_Code;
543 CLOSE c_panel_type_code;
544
545 IF l_panel_type_Code = 'SCREENING' THEN
546 l_dec_type := 'SCREENING';
547 ELSE
548 l_dec_type := 'INTERVIEW';
549 END IF;
550
551 OPEN cur_dflt_panlm_cd(l_dec_type);
552 FETCH cur_dflt_panlm_cd INTO rec_dflt_panlm_cd;
553 IF cur_dflt_panlm_cd%NOTFOUND THEN
554 CLOSE cur_dflt_panlm_cd;
555 -- Get the value for message token CLASS_MEANING
556 OPEN c_lkup_cd_mean(l_dec_type, 'PENDING');
557 FETCH c_lkup_cd_mean INTO l_class_meaning;
558 CLOSE c_lkup_cd_mean;
559
560 -- Get the value for message token SYS_STAT_MEANING
561 OPEN c_lkup_cd_mean('INTR_DECSN', l_dec_type);
562 FETCH c_lkup_cd_mean INTO l_sys_stat_meaning;
563 CLOSE c_lkup_cd_mean;
564
565 fnd_message.set_name('IGS','IGS_AD_NO_DECISION_CD_SETUP'); -- Message: Unable to assign panel members to the application instance.
566 fnd_message.set_token('CLASS_MEANING', l_class_meaning);
567 fnd_message.set_token('SYS_STAT_MEANING', l_sys_stat_meaning);
568 igs_ge_msg_stack.add;
569 app_exception.raise_exception;
570 ELSE
571 --defaulting final_decision_type, final_decision_type
572 new_references.member_decision_code := rec_dflt_panlm_cd.name;
573 new_references.member_decision_type := rec_dflt_panlm_cd.class;
574 CLOSE cur_dflt_panlm_cd;
575 END IF;
576 END IF;
577 END IF;
578
579 IF NVL(p_inserting,FALSE) OR NVL(p_updating,FALSE) THEN
580 -- Check if member Decisions is mapped to the system defaulted Decisions of 'PENDING'
581 OPEN cur_dflt_panl_cd;
582 FETCH cur_dflt_panl_cd INTO rec_dflt_panl_cd;
583 IF cur_dflt_panl_cd%NOTFOUND THEN
584 CLOSE cur_dflt_panl_cd;
585
586 IF ((new_references.member_decision_date IS NULL AND new_references.member_decision_code IS NOT NULL) OR
587 (new_references.member_decision_date IS NOT NULL AND new_references.member_decision_code IS NULL)) THEN
588 -- Decision Date must be entered if a Decision is entered. If the Decision is saved without Decision Date then raise an error message.
589 fnd_message.set_name('IGS','IGS_AD_MAND_DECISION_INFO'); -- Message: Decision and Decision Date must be entered.
590 igs_ge_msg_stack.add;
591 app_exception.raise_exception;
592 END IF;
593 ELSE
594 IF new_references.member_decision_date IS NOT NULL THEN
595 fnd_message.set_name('IGS','IGS_AD_MAND_DECISION_INFO'); -- Message: Decision and Decision Date must be entered.
596 igs_ge_msg_stack.add;
597 app_exception.raise_exception;
598 END IF;
599 END IF;
600 IF cur_dflt_panl_cd%ISOPEN THEN
601 CLOSE cur_dflt_panl_cd;
602 END IF;
603
604 OPEN c_appl_dt;
605 FETCH c_appl_dt INTO l_appl_dt;
606 CLOSE c_appl_dt;
607
608 IF NVL(new_references.interview_date,sysdate) < l_appl_dt THEN
609 fnd_message.set_name('IGS','IGS_AD_APPL_DATE_ERROR'); -- NAME cannot be less than Application Date
610 fnd_message.set_token ('NAME',fnd_message.get_string('IGS','IGS_AD_INTVW_DATE')); -- Message: Decision Date
611 igs_ge_msg_stack.add;
612 app_exception.raise_exception;
613 END IF;
614
615 IF new_references.member_decision_date IS NOT NULL THEN
616 -- Decision Date entered must be greater than or equal to the Application Date. Else raise an Error message.
617 IF new_references.member_decision_date < l_appl_dt OR new_references.member_decision_date > SYSDATE THEN
618 fnd_message.set_name('IGS','IGS_AD_DECISION_DATE'); -- Decision Date Can Neither be greater than System Date nor be less than Application Date
619 igs_ge_msg_stack.add;
620 app_exception.raise_exception;
621 END IF;
622 END IF;
623 -- A screening decision for a reviewer may be updated if no final screening decision exists for the panel. Else raise an Error message.
624 -- Check final decision associated to panel code.
625 OPEN c_chk_final_decision(new_references.panel_dtls_id);
626 FETCH c_chk_final_decision INTO rec_chk_final_decision;
627 CLOSE c_chk_final_decision;
628 l_final_decision := igs_ad_gen_013.get_sys_code_status(rec_chk_final_decision.final_decision_code,
629 rec_chk_final_decision.final_decision_type);
630
631 IF NVL(l_final_decision,'PENDING') <> 'PENDING' THEN
632 fnd_message.set_name('IGS','IGS_AD_PNL_FNL_DECISION_EXITS'); -- Message: Cannot update/insert panel member's information when the panel final decision is already recorded.
633 igs_ge_msg_stack.add;
634 app_exception.raise_exception;
635 END IF;
636 END IF;
637
638 IF NVL(p_inserting,FALSE) OR NVL(p_updating,FALSE) OR NVL(p_deleting,FALSE) THEN
639 IF new_references.member_decision_type = 'SCREENING' OR old_references.member_decision_type = 'SCREENING' THEN
640 OPEN c_instance_dets;
641 FETCH c_instance_dets INTO instance_dets_rec;
642 CLOSE c_instance_dets;
643
644 OPEN c_intvw_pnl_exsts(instance_dets_rec);
645 FETCH c_intvw_pnl_exsts INTO intvw_pnl_exsts_rec;
646 IF c_intvw_pnl_exsts%FOUND THEN
647 CLOSE c_intvw_pnl_exsts;
648 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.
649 igs_ge_msg_stack.add;
650 app_exception.raise_exception;
651 END IF;
652 CLOSE c_intvw_pnl_exsts;
653 END IF;
654 -- If a panel is closed then the interviewer decisions cannot be entered/updated and adding/deleting of interviewers is prohibited.
655 -- Check closed flag associated to panel code.
656 OPEN c_chk_final_decision(NVL(old_references.panel_dtls_id, new_references.panel_dtls_id));
657 FETCH c_chk_final_decision INTO rec_chk_final_decision;
658 CLOSE c_chk_final_decision;
659
660 IF NVL(rec_chk_final_decision.closed_flag,'N') <> 'N' THEN
661 fnd_message.set_name('IGS','IGS_AD_PNL_IS_CLOSED'); -- Message: Cannot update or delete a closed panel.
662 igs_ge_msg_stack.add;
663 app_exception.raise_exception;
664 END IF;
665 END IF;
666
667 IF NVL(p_updating,FALSE) THEN
668
669 -- If a screening decision entered, is mapped to the system screening decision of 'VOIDED' then the screening decision may not be updated for the reviewer.
670 -- Throw an error message if the screening decision is attempted to be updated from 'VOIDED' to any other status.
671 -- Check the member's decision associated with panel member.
672 l_member_decision := igs_ad_gen_013.get_sys_code_status(new_references.member_decision_code,
673 new_references.member_decision_type);
674 -- Check the old member's decision associated with panel member.
675 l_old_member_decision := igs_ad_gen_013.get_sys_code_status(old_references.member_decision_code,
676 old_references.member_decision_type);
677 IF l_old_member_decision = 'VOIDED' AND l_member_decision <> 'VOIDED' THEN
678 fnd_message.set_name('IGS','IGS_AD_NO_UPD_VOID_DECISION'); -- Message: Cannot update the reviewer's decision as it is mapped to a status of Voided.
679 igs_ge_msg_stack.add;
680 app_exception.raise_exception;
681 END IF;
682
683 END IF;
684
685 IF NVL(p_deleting,FALSE) THEN
686 -- A reviewer may be deleted from a panel if there is no existing screening decision and no final screening decision exists for the panel.
687 -- If a reviewer having screening decision is deleted then raise an error message and fail deletion.
688
689 -- Check final decision associated to panel code.
690 OPEN c_chk_final_decision(old_references.panel_dtls_id);
691 FETCH c_chk_final_decision INTO rec_chk_final_decision;
692 CLOSE c_chk_final_decision;
693 l_final_decision := igs_ad_gen_013.get_sys_code_status(rec_chk_final_decision.final_decision_code,
694 rec_chk_final_decision.final_decision_type);
695
696 -- Check member decision associated with panel member.
697 l_member_decision := igs_ad_gen_013.get_sys_code_status(old_references.member_decision_code,
698 old_references.member_decision_type);
699
700 IF NVL(l_final_decision,'*') <> 'PENDING' OR NVL(l_member_decision,'*') <> 'PENDING' THEN
701 fnd_message.set_name('IGS','IGS_AD_PNL_DECISION_EXITS'); -- Message: Cannot delete the panel member when the decision is already recorded.
702 igs_ge_msg_stack.add;
703 app_exception.raise_exception;
704 END IF;
705 END IF;
706 END beforerowinsertupdatedelete1;
707
708 PROCEDURE before_dml (
709 p_action IN VARCHAR2,
710 x_rowid IN VARCHAR2,
711 x_panel_dtls_id IN NUMBER,
712 x_role_type_code IN VARCHAR2,
713 x_member_person_id IN NUMBER,
714 x_interview_date IN DATE,
715 x_interview_time IN DATE,
716 x_location_cd IN VARCHAR2,
717 x_room_id IN NUMBER,
718 x_member_decision_code IN VARCHAR2,
719 x_member_decision_type IN VARCHAR2,
720 x_member_decision_date IN DATE,
721 x_attribute_category IN VARCHAR2,
722 x_attribute1 IN VARCHAR2,
723 x_attribute2 IN VARCHAR2,
724 x_attribute3 IN VARCHAR2,
725 x_attribute4 IN VARCHAR2,
726 x_attribute5 IN VARCHAR2,
727 x_attribute6 IN VARCHAR2,
728 x_attribute7 IN VARCHAR2,
729 x_attribute8 IN VARCHAR2,
730 x_attribute9 IN VARCHAR2,
731 x_attribute10 IN VARCHAR2,
732 x_attribute11 IN VARCHAR2,
733 x_attribute12 IN VARCHAR2,
734 x_attribute13 IN VARCHAR2,
735 x_attribute14 IN VARCHAR2,
736 x_attribute15 IN VARCHAR2,
737 x_attribute16 IN VARCHAR2,
738 x_attribute17 IN VARCHAR2,
739 x_attribute18 IN VARCHAR2,
740 x_attribute19 IN VARCHAR2,
741 x_attribute20 IN VARCHAR2,
742 x_creation_date IN DATE,
743 x_created_by IN NUMBER,
744 x_last_update_date IN DATE,
745 x_last_updated_by IN NUMBER,
746 x_last_update_login IN NUMBER
747 ) AS
748 /*
749 || Created By : Navin Sinha
750 || Created On : 16-JUN-2003
751 || Purpose : Initialises the columns, Checks Constraints, Calls the
752 || Trigger Handlers for the table, before any DML operation.
753 || Known limitations, enhancements or remarks :
754 || Change History :
755 || Who When What
756 || (reverse chronological order - newest change first)
757 */
758 BEGIN
759
760 set_column_values (
761 p_action,
762 x_rowid,
763 x_panel_dtls_id,
764 x_role_type_code,
765 x_member_person_id,
766 x_interview_date,
767 x_interview_time,
768 x_location_cd,
769 x_room_id,
770 x_member_decision_code,
771 x_member_decision_type,
772 x_member_decision_date,
773 x_attribute_category,
774 x_attribute1,
775 x_attribute2,
776 x_attribute3,
777 x_attribute4,
778 x_attribute5,
779 x_attribute6,
780 x_attribute7,
781 x_attribute8,
782 x_attribute9,
783 x_attribute10,
784 x_attribute11,
785 x_attribute12,
786 x_attribute13,
787 x_attribute14,
788 x_attribute15,
789 x_attribute16,
790 x_attribute17,
791 x_attribute18,
792 x_attribute19,
793 x_attribute20,
794 x_creation_date,
795 x_created_by,
796 x_last_update_date,
797 x_last_updated_by,
798 x_last_update_login
799 );
800
801 IF (p_action = 'INSERT') THEN
802 -- Call all the procedures related to Before Insert.
803 IF ( get_pk_for_validation(
804 new_references.panel_dtls_id,
805 new_references.member_person_id
806 )
807 ) THEN
808 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
809 igs_ge_msg_stack.add;
810 app_exception.raise_exception;
811 END IF;
812 beforerowinsertupdatedelete1( p_inserting => TRUE , p_updating => FALSE, p_deleting=> FALSE);
813 check_parent_existance;
814 ELSIF (p_action = 'UPDATE') THEN
815 -- Call all the procedures related to Before Update.
816 beforerowinsertupdatedelete1( p_inserting => FALSE , p_updating => TRUE, p_deleting=> FALSE);
817 check_parent_existance;
818 ELSIF (p_action = 'DELETE') THEN
819 -- Call all the procedures related to Before Delete.
820 beforerowinsertupdatedelete1( p_inserting => FALSE , p_updating => FALSE, p_deleting=> TRUE);
821 check_child_existance;
822 ELSIF (p_action = 'VALIDATE_INSERT') THEN
823 -- Call all the procedures related to Before Insert.
824 IF ( get_pk_for_validation (
825 new_references.panel_dtls_id,
826 new_references.member_person_id
827 )
828 ) THEN
829 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
830 igs_ge_msg_stack.add;
831 app_exception.raise_exception;
832 END IF;
833 beforerowinsertupdatedelete1( p_inserting => TRUE , p_updating => FALSE, p_deleting=> FALSE);
834 ELSIF (p_action = 'VALIDATE_DELETE') THEN
835 beforerowinsertupdatedelete1( p_inserting => FALSE , p_updating => FALSE, p_deleting=> TRUE);
836 check_child_existance;
837 END IF;
838
839 END before_dml;
840
841
842 PROCEDURE insert_row (
843 x_rowid IN OUT NOCOPY VARCHAR2,
844 x_panel_dtls_id IN NUMBER,
845 x_role_type_code IN VARCHAR2,
846 x_member_person_id IN NUMBER,
847 x_interview_date IN DATE,
848 x_interview_time IN DATE,
849 x_location_cd IN VARCHAR2,
850 x_room_id IN NUMBER,
851 x_member_decision_code IN VARCHAR2,
852 x_member_decision_type IN VARCHAR2,
853 x_member_decision_date IN DATE,
854 x_attribute_category IN VARCHAR2,
855 x_attribute1 IN VARCHAR2,
856 x_attribute2 IN VARCHAR2,
857 x_attribute3 IN VARCHAR2,
858 x_attribute4 IN VARCHAR2,
859 x_attribute5 IN VARCHAR2,
860 x_attribute6 IN VARCHAR2,
861 x_attribute7 IN VARCHAR2,
862 x_attribute8 IN VARCHAR2,
863 x_attribute9 IN VARCHAR2,
864 x_attribute10 IN VARCHAR2,
865 x_attribute11 IN VARCHAR2,
866 x_attribute12 IN VARCHAR2,
867 x_attribute13 IN VARCHAR2,
868 x_attribute14 IN VARCHAR2,
869 x_attribute15 IN VARCHAR2,
870 x_attribute16 IN VARCHAR2,
871 x_attribute17 IN VARCHAR2,
872 x_attribute18 IN VARCHAR2,
873 x_attribute19 IN VARCHAR2,
874 x_attribute20 IN VARCHAR2,
875 x_mode IN VARCHAR2
876 ) AS
877 /*
878 || Created By : Navin Sinha
879 || Created On : 16-JUN-2003
880 || Purpose : Handles the INSERT DML logic for the table.
881 || Known limitations, enhancements or remarks :
882 || Change History :
883 || Who When What
884 || ravishar 05/27/05 Security related changes
885 || (reverse chronological order - newest change first)
886 */
887
888 x_last_update_date DATE;
889 x_last_updated_by NUMBER;
890 x_last_update_login NUMBER;
891 l_mode VARCHAR2(1);
892 BEGIN
893 l_mode := NVL(x_mode, 'R');
894 x_last_update_date := SYSDATE;
895 IF (l_mode = 'I') THEN
896 x_last_updated_by := 1;
897 x_last_update_login := 0;
898 ELSIF (l_mode IN ('R','S')) THEN
899 x_last_updated_by := fnd_global.user_id;
900 IF (x_last_updated_by IS NULL) THEN
901 x_last_updated_by := -1;
902 END IF;
903 x_last_update_login := fnd_global.login_id;
904 IF (x_last_update_login IS NULL) THEN
905 x_last_update_login := -1;
906 END IF;
907 ELSE
908 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
909 fnd_message.set_token ('ROUTINE', 'IGS_AD_PNMEMBR_DTLS_PKG.INSERT_ROW');
910 igs_ge_msg_stack.add;
911 app_exception.raise_exception;
912 END IF;
913
914 before_dml(
915 p_action => 'INSERT',
916 x_rowid => x_rowid,
917 x_panel_dtls_id => x_panel_dtls_id,
918 x_role_type_code => x_role_type_code,
919 x_member_person_id => x_member_person_id,
920 x_interview_date => x_interview_date,
921 x_interview_time => x_interview_time,
922 x_location_cd => x_location_cd,
923 x_room_id => x_room_id,
924 x_member_decision_code => x_member_decision_code,
925 x_member_decision_type => x_member_decision_type,
926 x_member_decision_date => x_member_decision_date,
927 x_attribute_category => x_attribute_category,
928 x_attribute1 => x_attribute1,
929 x_attribute2 => x_attribute2,
930 x_attribute3 => x_attribute3,
931 x_attribute4 => x_attribute4,
932 x_attribute5 => x_attribute5,
933 x_attribute6 => x_attribute6,
934 x_attribute7 => x_attribute7,
935 x_attribute8 => x_attribute8,
936 x_attribute9 => x_attribute9,
937 x_attribute10 => x_attribute10,
938 x_attribute11 => x_attribute11,
939 x_attribute12 => x_attribute12,
940 x_attribute13 => x_attribute13,
941 x_attribute14 => x_attribute14,
942 x_attribute15 => x_attribute15,
943 x_attribute16 => x_attribute16,
944 x_attribute17 => x_attribute17,
945 x_attribute18 => x_attribute18,
946 x_attribute19 => x_attribute19,
947 x_attribute20 => x_attribute20,
948 x_creation_date => x_last_update_date,
949 x_created_by => x_last_updated_by,
950 x_last_update_date => x_last_update_date,
951 x_last_updated_by => x_last_updated_by,
952 x_last_update_login => x_last_update_login
953 );
954
955 IF (x_mode = 'S') THEN
956 igs_sc_gen_001.set_ctx('R');
957 END IF;
958 INSERT INTO igs_ad_pnmembr_dtls (
959 panel_dtls_id,
960 role_type_code,
961 member_person_id,
962 interview_date,
963 interview_time,
964 location_cd,
965 room_id,
966 member_decision_code,
967 member_decision_type,
968 member_decision_date,
969 attribute_category,
970 attribute1,
971 attribute2,
972 attribute3,
973 attribute4,
974 attribute5,
975 attribute6,
976 attribute7,
977 attribute8,
978 attribute9,
979 attribute10,
980 attribute11,
981 attribute12,
982 attribute13,
983 attribute14,
984 attribute15,
985 attribute16,
986 attribute17,
987 attribute18,
988 attribute19,
989 attribute20,
990 creation_date,
991 created_by,
992 last_update_date,
993 last_updated_by,
994 last_update_login
995 ) VALUES (
996 new_references.panel_dtls_id,
997 new_references.role_type_code,
998 new_references.member_person_id,
999 new_references.interview_date,
1000 new_references.interview_time,
1001 new_references.location_cd,
1002 new_references.room_id,
1003 new_references.member_decision_code,
1004 new_references.member_decision_type,
1005 new_references.member_decision_date,
1006 new_references.attribute_category,
1007 new_references.attribute1,
1008 new_references.attribute2,
1009 new_references.attribute3,
1010 new_references.attribute4,
1011 new_references.attribute5,
1012 new_references.attribute6,
1013 new_references.attribute7,
1014 new_references.attribute8,
1015 new_references.attribute9,
1016 new_references.attribute10,
1017 new_references.attribute11,
1018 new_references.attribute12,
1019 new_references.attribute13,
1020 new_references.attribute14,
1021 new_references.attribute15,
1022 new_references.attribute16,
1023 new_references.attribute17,
1024 new_references.attribute18,
1025 new_references.attribute19,
1026 new_references.attribute20,
1027 x_last_update_date,
1028 x_last_updated_by,
1029 x_last_update_date,
1030 x_last_updated_by,
1031 x_last_update_login
1032 ) RETURNING ROWID INTO x_rowid;
1033 IF (x_mode = 'S') THEN
1034 igs_sc_gen_001.unset_ctx('R');
1035 END IF;
1036
1037
1038 EXCEPTION
1039 WHEN OTHERS THEN
1040 IF (x_mode = 'S') THEN
1041 igs_sc_gen_001.unset_ctx('R');
1042 END IF;
1043 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
1044 -- Code to handle Security Policy error raised
1045 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1046 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1047 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1048 -- that the ownerof policy function does not have privilege to access.
1049 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1050 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1051 IGS_GE_MSG_STACK.ADD;
1052 app_exception.raise_exception;
1053 ELSE
1054 RAISE;
1055 END IF;
1056 END insert_row;
1057
1058
1059 PROCEDURE lock_row (
1060 x_rowid IN VARCHAR2,
1061 x_panel_dtls_id IN NUMBER,
1062 x_role_type_code IN VARCHAR2,
1063 x_member_person_id IN NUMBER,
1064 x_interview_date IN DATE,
1065 x_interview_time IN DATE,
1066 x_location_cd IN VARCHAR2,
1067 x_room_id IN NUMBER,
1068 x_member_decision_code IN VARCHAR2,
1069 x_member_decision_type IN VARCHAR2,
1070 x_member_decision_date IN DATE,
1071 x_attribute_category IN VARCHAR2,
1072 x_attribute1 IN VARCHAR2,
1073 x_attribute2 IN VARCHAR2,
1074 x_attribute3 IN VARCHAR2,
1075 x_attribute4 IN VARCHAR2,
1076 x_attribute5 IN VARCHAR2,
1077 x_attribute6 IN VARCHAR2,
1078 x_attribute7 IN VARCHAR2,
1079 x_attribute8 IN VARCHAR2,
1080 x_attribute9 IN VARCHAR2,
1081 x_attribute10 IN VARCHAR2,
1082 x_attribute11 IN VARCHAR2,
1083 x_attribute12 IN VARCHAR2,
1084 x_attribute13 IN VARCHAR2,
1085 x_attribute14 IN VARCHAR2,
1086 x_attribute15 IN VARCHAR2,
1087 x_attribute16 IN VARCHAR2,
1088 x_attribute17 IN VARCHAR2,
1089 x_attribute18 IN VARCHAR2,
1090 x_attribute19 IN VARCHAR2,
1091 x_attribute20 IN VARCHAR2
1092 ) AS
1093 /*
1094 || Created By : Navin Sinha
1095 || Created On : 16-JUN-2003
1096 || Purpose : Handles the LOCK mechanism for the table.
1097 || Known limitations, enhancements or remarks :
1098 || Change History :
1099 || Who When What
1100 || (reverse chronological order - newest change first)
1101 */
1102 CURSOR c1 IS
1103 SELECT
1104 role_type_code,
1105 interview_date,
1106 interview_time,
1107 location_cd,
1108 room_id,
1109 member_decision_code,
1110 member_decision_type,
1111 member_decision_date,
1112 attribute_category,
1113 attribute1,
1114 attribute2,
1115 attribute3,
1116 attribute4,
1117 attribute5,
1118 attribute6,
1119 attribute7,
1120 attribute8,
1121 attribute9,
1122 attribute10,
1123 attribute11,
1124 attribute12,
1125 attribute13,
1126 attribute14,
1127 attribute15,
1128 attribute16,
1129 attribute17,
1130 attribute18,
1131 attribute19,
1132 attribute20
1133 FROM igs_ad_pnmembr_dtls
1134 WHERE rowid = x_rowid
1135 FOR UPDATE NOWAIT;
1136
1137 tlinfo c1%ROWTYPE;
1138
1139 BEGIN
1140
1141 OPEN c1;
1142 FETCH c1 INTO tlinfo;
1143 IF (c1%notfound) THEN
1144 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1145 igs_ge_msg_stack.add;
1146 CLOSE c1;
1147 app_exception.raise_exception;
1148 RETURN;
1149 END IF;
1150 CLOSE c1;
1151
1152 IF (
1153 (tlinfo.role_type_code = x_role_type_code)
1154 AND ((TRUNC(tlinfo.interview_date) = TRUNC(x_interview_date)) OR ((tlinfo.interview_date IS NULL) AND (X_interview_date IS NULL)))
1155 AND ((tlinfo.interview_time = x_interview_time) OR ((tlinfo.interview_time IS NULL) AND (X_interview_time IS NULL)))
1156 AND ((tlinfo.location_cd = x_location_cd) OR ((tlinfo.location_cd IS NULL) AND (X_location_cd IS NULL)))
1157 AND ((tlinfo.room_id = x_room_id) OR ((tlinfo.room_id IS NULL) AND (X_room_id IS NULL)))
1158 AND (tlinfo.member_decision_code = x_member_decision_code)
1159 AND (tlinfo.member_decision_type = x_member_decision_type)
1160 AND ((TRUNC(tlinfo.member_decision_date) = TRUNC(x_member_decision_date)) OR ((tlinfo.member_decision_date IS NULL) AND (X_member_decision_date IS NULL)))
1161 AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (X_attribute_category IS NULL)))
1162 AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (X_attribute1 IS NULL)))
1163 AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (X_attribute2 IS NULL)))
1164 AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (X_attribute3 IS NULL)))
1165 AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (X_attribute4 IS NULL)))
1166 AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (X_attribute5 IS NULL)))
1167 AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (X_attribute6 IS NULL)))
1168 AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (X_attribute7 IS NULL)))
1169 AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (X_attribute8 IS NULL)))
1170 AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (X_attribute9 IS NULL)))
1171 AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (X_attribute10 IS NULL)))
1172 AND ((tlinfo.attribute11 = x_attribute11) OR ((tlinfo.attribute11 IS NULL) AND (X_attribute11 IS NULL)))
1173 AND ((tlinfo.attribute12 = x_attribute12) OR ((tlinfo.attribute12 IS NULL) AND (X_attribute12 IS NULL)))
1174 AND ((tlinfo.attribute13 = x_attribute13) OR ((tlinfo.attribute13 IS NULL) AND (X_attribute13 IS NULL)))
1175 AND ((tlinfo.attribute14 = x_attribute14) OR ((tlinfo.attribute14 IS NULL) AND (X_attribute14 IS NULL)))
1176 AND ((tlinfo.attribute15 = x_attribute15) OR ((tlinfo.attribute15 IS NULL) AND (X_attribute15 IS NULL)))
1177 AND ((tlinfo.attribute16 = x_attribute16) OR ((tlinfo.attribute16 IS NULL) AND (X_attribute16 IS NULL)))
1178 AND ((tlinfo.attribute17 = x_attribute17) OR ((tlinfo.attribute17 IS NULL) AND (X_attribute17 IS NULL)))
1179 AND ((tlinfo.attribute18 = x_attribute18) OR ((tlinfo.attribute18 IS NULL) AND (X_attribute18 IS NULL)))
1180 AND ((tlinfo.attribute19 = x_attribute19) OR ((tlinfo.attribute19 IS NULL) AND (X_attribute19 IS NULL)))
1181 AND ((tlinfo.attribute20 = x_attribute20) OR ((tlinfo.attribute20 IS NULL) AND (X_attribute20 IS NULL)))
1182 ) THEN
1183 NULL;
1184 ELSE
1185 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1186 igs_ge_msg_stack.add;
1187 app_exception.raise_exception;
1188 END IF;
1189
1190 RETURN;
1191
1192 END lock_row;
1193
1194
1195 PROCEDURE update_row (
1196 x_rowid IN VARCHAR2,
1197 x_panel_dtls_id IN NUMBER,
1198 x_role_type_code IN VARCHAR2,
1199 x_member_person_id IN NUMBER,
1200 x_interview_date IN DATE,
1201 x_interview_time IN DATE,
1202 x_location_cd IN VARCHAR2,
1203 x_room_id IN NUMBER,
1204 x_member_decision_code IN VARCHAR2,
1205 x_member_decision_type IN VARCHAR2,
1206 x_member_decision_date IN DATE,
1207 x_attribute_category IN VARCHAR2,
1208 x_attribute1 IN VARCHAR2,
1209 x_attribute2 IN VARCHAR2,
1210 x_attribute3 IN VARCHAR2,
1211 x_attribute4 IN VARCHAR2,
1212 x_attribute5 IN VARCHAR2,
1213 x_attribute6 IN VARCHAR2,
1214 x_attribute7 IN VARCHAR2,
1215 x_attribute8 IN VARCHAR2,
1216 x_attribute9 IN VARCHAR2,
1217 x_attribute10 IN VARCHAR2,
1218 x_attribute11 IN VARCHAR2,
1219 x_attribute12 IN VARCHAR2,
1220 x_attribute13 IN VARCHAR2,
1221 x_attribute14 IN VARCHAR2,
1222 x_attribute15 IN VARCHAR2,
1223 x_attribute16 IN VARCHAR2,
1224 x_attribute17 IN VARCHAR2,
1225 x_attribute18 IN VARCHAR2,
1226 x_attribute19 IN VARCHAR2,
1227 x_attribute20 IN VARCHAR2,
1228 x_mode IN VARCHAR2
1229 ) AS
1230 /*
1231 || Created By : Navin Sinha
1232 || Created On : 16-JUN-2003
1233 || Purpose : Handles the UPDATE DML logic for the table.
1234 || Known limitations, enhancements or remarks :
1235 || Change History :
1236 || Who When What
1237 || ravishar 05/27/05 Security related changes
1238 || (reverse chronological order - newest change first)
1239 */
1240 x_last_update_date DATE ;
1241 x_last_updated_by NUMBER;
1242 x_last_update_login NUMBER;
1243 l_mode VARCHAR2(1);
1244 BEGIN
1245 l_mode := NVL(x_mode, 'R');
1246 x_last_update_date := SYSDATE;
1247 IF (l_mode = 'I') THEN
1248 x_last_updated_by := 1;
1249 x_last_update_login := 0;
1250 ELSIF (l_mode IN ('R','S')) THEN
1251 x_last_updated_by := fnd_global.user_id;
1252 IF x_last_updated_by IS NULL THEN
1253 x_last_updated_by := -1;
1254 END IF;
1255 x_last_update_login := fnd_global.login_id;
1256 IF (x_last_update_login IS NULL) THEN
1257 x_last_update_login := -1;
1258 END IF;
1259 ELSE
1260 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1261 fnd_message.set_token ('ROUTINE', 'IGS_AD_PNMEMBR_DTLS_PKG.UPDATE_ROW');
1262 igs_ge_msg_stack.add;
1263 app_exception.raise_exception;
1264 END IF;
1265
1266 before_dml(
1267 p_action => 'UPDATE',
1268 x_rowid => x_rowid,
1269 x_panel_dtls_id => x_panel_dtls_id,
1270 x_role_type_code => x_role_type_code,
1271 x_member_person_id => x_member_person_id,
1272 x_interview_date => x_interview_date,
1273 x_interview_time => x_interview_time,
1274 x_location_cd => x_location_cd,
1275 x_room_id => x_room_id,
1276 x_member_decision_code => x_member_decision_code,
1277 x_member_decision_type => x_member_decision_type,
1278 x_member_decision_date => x_member_decision_date,
1279 x_attribute_category => x_attribute_category,
1280 x_attribute1 => x_attribute1,
1281 x_attribute2 => x_attribute2,
1282 x_attribute3 => x_attribute3,
1283 x_attribute4 => x_attribute4,
1284 x_attribute5 => x_attribute5,
1285 x_attribute6 => x_attribute6,
1286 x_attribute7 => x_attribute7,
1287 x_attribute8 => x_attribute8,
1288 x_attribute9 => x_attribute9,
1289 x_attribute10 => x_attribute10,
1290 x_attribute11 => x_attribute11,
1291 x_attribute12 => x_attribute12,
1292 x_attribute13 => x_attribute13,
1293 x_attribute14 => x_attribute14,
1294 x_attribute15 => x_attribute15,
1295 x_attribute16 => x_attribute16,
1296 x_attribute17 => x_attribute17,
1297 x_attribute18 => x_attribute18,
1298 x_attribute19 => x_attribute19,
1299 x_attribute20 => x_attribute20,
1300 x_creation_date => x_last_update_date,
1301 x_created_by => x_last_updated_by,
1302 x_last_update_date => x_last_update_date,
1303 x_last_updated_by => x_last_updated_by,
1304 x_last_update_login => x_last_update_login
1305 );
1306
1307 IF (x_mode = 'S') THEN
1308 igs_sc_gen_001.set_ctx('R');
1309 END IF;
1310 UPDATE igs_ad_pnmembr_dtls
1311 SET
1312 role_type_code = new_references.role_type_code,
1313 interview_date = new_references.interview_date,
1314 interview_time = new_references.interview_time,
1315 location_cd = new_references.location_cd,
1316 room_id = new_references.room_id,
1317 member_decision_code = new_references.member_decision_code,
1318 member_decision_type = new_references.member_decision_type,
1319 member_decision_date = new_references.member_decision_date,
1320 attribute_category = new_references.attribute_category,
1321 attribute1 = new_references.attribute1,
1322 attribute2 = new_references.attribute2,
1323 attribute3 = new_references.attribute3,
1324 attribute4 = new_references.attribute4,
1325 attribute5 = new_references.attribute5,
1326 attribute6 = new_references.attribute6,
1327 attribute7 = new_references.attribute7,
1328 attribute8 = new_references.attribute8,
1329 attribute9 = new_references.attribute9,
1330 attribute10 = new_references.attribute10,
1331 attribute11 = new_references.attribute11,
1332 attribute12 = new_references.attribute12,
1333 attribute13 = new_references.attribute13,
1334 attribute14 = new_references.attribute14,
1335 attribute15 = new_references.attribute15,
1336 attribute16 = new_references.attribute16,
1337 attribute17 = new_references.attribute17,
1338 attribute18 = new_references.attribute18,
1339 attribute19 = new_references.attribute19,
1340 attribute20 = new_references.attribute20,
1341 last_update_date = x_last_update_date,
1342 last_updated_by = x_last_updated_by,
1343 last_update_login = x_last_update_login
1344 WHERE rowid = x_rowid;
1345
1346 IF (SQL%NOTFOUND) THEN
1347 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1348 igs_ge_msg_stack.add;
1349 IF (x_mode = 'S') THEN
1350 igs_sc_gen_001.unset_ctx('R');
1351 END IF;
1352 app_exception.raise_exception;
1353 END IF;
1354 IF (x_mode = 'S') THEN
1355 igs_sc_gen_001.unset_ctx('R');
1356 END IF;
1357
1358
1359 EXCEPTION
1360 WHEN OTHERS THEN
1361 IF (x_mode = 'S') THEN
1362 igs_sc_gen_001.unset_ctx('R');
1363 END IF;
1364 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
1365 -- Code to handle Security Policy error raised
1366 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1367 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1368 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1369 -- that the ownerof policy function does not have privilege to access.
1370 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1371 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1372 IGS_GE_MSG_STACK.ADD;
1373 app_exception.raise_exception;
1374 ELSE
1375 RAISE;
1376 END IF;
1377 END update_row;
1378
1379
1380 PROCEDURE add_row (
1381 x_rowid IN OUT NOCOPY VARCHAR2,
1382 x_panel_dtls_id IN NUMBER,
1383 x_role_type_code IN VARCHAR2,
1384 x_member_person_id IN NUMBER,
1385 x_interview_date IN DATE,
1386 x_interview_time IN DATE,
1387 x_location_cd IN VARCHAR2,
1388 x_room_id IN NUMBER,
1389 x_member_decision_code IN VARCHAR2,
1390 x_member_decision_type IN VARCHAR2,
1391 x_member_decision_date IN DATE,
1392 x_attribute_category IN VARCHAR2,
1393 x_attribute1 IN VARCHAR2,
1394 x_attribute2 IN VARCHAR2,
1395 x_attribute3 IN VARCHAR2,
1396 x_attribute4 IN VARCHAR2,
1397 x_attribute5 IN VARCHAR2,
1398 x_attribute6 IN VARCHAR2,
1399 x_attribute7 IN VARCHAR2,
1400 x_attribute8 IN VARCHAR2,
1401 x_attribute9 IN VARCHAR2,
1402 x_attribute10 IN VARCHAR2,
1403 x_attribute11 IN VARCHAR2,
1404 x_attribute12 IN VARCHAR2,
1405 x_attribute13 IN VARCHAR2,
1406 x_attribute14 IN VARCHAR2,
1407 x_attribute15 IN VARCHAR2,
1408 x_attribute16 IN VARCHAR2,
1409 x_attribute17 IN VARCHAR2,
1410 x_attribute18 IN VARCHAR2,
1411 x_attribute19 IN VARCHAR2,
1412 x_attribute20 IN VARCHAR2,
1413 x_mode IN VARCHAR2
1414 ) AS
1415 /*
1416 || Created By : Navin Sinha
1417 || Created On : 16-JUN-2003
1418 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1419 || Known limitations, enhancements or remarks :
1420 || Change History :
1421 || Who When What
1422 || (reverse chronological order - newest change first)
1423 */
1424 CURSOR c1 IS
1425 SELECT rowid
1426 FROM igs_ad_pnmembr_dtls
1427 WHERE panel_dtls_id = x_panel_dtls_id
1428 AND member_person_id = x_member_person_id;
1429 l_mode VARCHAR2(1);
1430 BEGIN
1431 l_mode := NVL(x_mode, 'R');
1432 OPEN c1;
1433 FETCH c1 INTO x_rowid;
1434 IF (c1%NOTFOUND) THEN
1435 CLOSE c1;
1436
1437 insert_row (
1438 x_rowid,
1439 x_panel_dtls_id,
1440 x_role_type_code,
1441 x_member_person_id,
1442 x_interview_date,
1443 x_interview_time,
1444 x_location_cd,
1445 x_room_id,
1446 x_member_decision_code,
1447 x_member_decision_type,
1448 x_member_decision_date,
1449 x_attribute_category,
1450 x_attribute1,
1451 x_attribute2,
1452 x_attribute3,
1453 x_attribute4,
1454 x_attribute5,
1455 x_attribute6,
1456 x_attribute7,
1457 x_attribute8,
1458 x_attribute9,
1459 x_attribute10,
1460 x_attribute11,
1461 x_attribute12,
1462 x_attribute13,
1463 x_attribute14,
1464 x_attribute15,
1465 x_attribute16,
1466 x_attribute17,
1467 x_attribute18,
1468 x_attribute19,
1469 x_attribute20,
1470 l_mode
1471 );
1472 RETURN;
1473 END IF;
1474 CLOSE c1;
1475
1476 update_row (
1477 x_rowid,
1478 x_panel_dtls_id,
1479 x_role_type_code,
1480 x_member_person_id,
1481 x_interview_date,
1482 x_interview_time,
1483 x_location_cd,
1484 x_room_id,
1485 x_member_decision_code,
1486 x_member_decision_type,
1487 x_member_decision_date,
1488 x_attribute_category,
1489 x_attribute1,
1490 x_attribute2,
1491 x_attribute3,
1492 x_attribute4,
1493 x_attribute5,
1494 x_attribute6,
1495 x_attribute7,
1496 x_attribute8,
1497 x_attribute9,
1498 x_attribute10,
1499 x_attribute11,
1500 x_attribute12,
1501 x_attribute13,
1502 x_attribute14,
1503 x_attribute15,
1504 x_attribute16,
1505 x_attribute17,
1506 x_attribute18,
1507 x_attribute19,
1508 x_attribute20,
1509 l_mode
1510 );
1511
1512 END add_row;
1513
1514
1515 PROCEDURE delete_row (
1516 x_rowid IN VARCHAR2,
1517 x_mode IN VARCHAR2
1518 ) AS
1519 /*
1520 || Created By : Navin Sinha
1521 || Created On : 16-JUN-2003
1522 || Purpose : Handles the DELETE DML logic for the table.
1523 || Known limitations, enhancements or remarks :
1524 || Change History :
1525 || Who When What
1526 || ravishar 5/30/2005 Security related changes
1527 || (reverse chronological order - newest change first)
1528 */
1529 BEGIN
1530
1531 before_dml (
1532 p_action => 'DELETE',
1533 x_rowid => x_rowid
1534 );
1535
1536 IF (x_mode = 'S') THEN
1537 igs_sc_gen_001.set_ctx('R');
1538 END IF;
1539 DELETE FROM igs_ad_pnmembr_dtls
1540 WHERE rowid = x_rowid;
1541
1542 IF (SQL%NOTFOUND) THEN
1543 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1544 igs_ge_msg_stack.add;
1545 IF (x_mode = 'S') THEN
1546 igs_sc_gen_001.unset_ctx('R');
1547 END IF;
1548 app_exception.raise_exception;
1549 END IF;
1550 IF (x_mode = 'S') THEN
1551 igs_sc_gen_001.unset_ctx('R');
1552 END IF;
1553
1554
1555 EXCEPTION
1556 WHEN OTHERS THEN
1557 IF (x_mode = 'S') THEN
1558 igs_sc_gen_001.unset_ctx('R');
1559 END IF;
1560 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
1561 -- Code to handle Security Policy error raised
1562 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1563 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1564 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1565 -- that the ownerof policy function does not have privilege to access.
1566 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1567 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1568 IGS_GE_MSG_STACK.ADD;
1569 app_exception.raise_exception;
1570 ELSE
1571 RAISE;
1572 END IF;
1573 END delete_row;
1574
1575
1576 END igs_ad_pnmembr_dtls_pkg;
[Home] [Help]