1 PACKAGE BODY igs_pe_credentials_pkg AS
2 /* $Header: IGSNI96B.pls 120.1 2005/06/28 05:18:10 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pe_credentials%ROWTYPE;
6 new_references igs_pe_credentials%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_credential_id IN NUMBER ,
12 x_person_id IN NUMBER ,
13 x_credential_type_id IN NUMBER ,
14 x_date_received IN DATE ,
15 x_reviewer_id IN NUMBER ,
16 x_reviewer_notes IN VARCHAR2 ,
17 x_recommender_name IN VARCHAR2 ,
18 x_recommender_title IN VARCHAR2 ,
19 x_recommender_organization IN VARCHAR2 ,
20 x_creation_date IN DATE ,
21 x_created_by IN NUMBER ,
22 x_last_update_date IN DATE ,
23 x_last_updated_by IN NUMBER ,
24 x_last_update_login IN NUMBER ,
25 x_rating_code IN VARCHAR2
26 ) AS
27 /*
28 || Created By : [email protected]
29 || Created On : 10-JAN-2002
30 || Purpose : Initialises the Old and New references for the columns of the table.
31 || Known limitations, enhancements or remarks :
32 || Change History :
33 || Who When What
34 || (reverse chronological order - newest change first)
35 */
36
37 CURSOR cur_old_ref_values IS
38 SELECT *
39 FROM igs_pe_credentials
40 WHERE rowid = x_rowid;
41
42 BEGIN
43
44 l_rowid := x_rowid;
45
46 -- Code for setting the Old and New Reference Values.
47 -- Populate Old Values.
48 OPEN cur_old_ref_values;
49 FETCH cur_old_ref_values INTO old_references;
50 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
51 CLOSE cur_old_ref_values;
52 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
53 igs_ge_msg_stack.add;
54 app_exception.raise_exception;
55 RETURN;
56 END IF;
57 CLOSE cur_old_ref_values;
58
59 -- Populate New Values.
60 new_references.credential_id := x_credential_id;
61 new_references.person_id := x_person_id;
62 new_references.credential_type_id := x_credential_type_id;
63 new_references.date_received := x_date_received;
64 new_references.reviewer_id := x_reviewer_id;
65 new_references.reviewer_notes := x_reviewer_notes;
66 new_references.recommender_name := x_recommender_name;
67 new_references.recommender_title := x_recommender_title;
68 new_references.recommender_organization := x_recommender_organization;
69 new_references.rating_code := x_rating_code;
70 IF (p_action = 'UPDATE') THEN
71 new_references.creation_date := old_references.creation_date;
72 new_references.created_by := old_references.created_by;
73 ELSE
74 new_references.creation_date := x_creation_date;
75 new_references.created_by := x_created_by;
76 END IF;
77
78 new_references.last_update_date := x_last_update_date;
79 new_references.last_updated_by := x_last_updated_by;
80 new_references.last_update_login := x_last_update_login;
81
82 END set_column_values;
83
84
85 PROCEDURE check_parent_existance AS
86 /*
87 || Created By : [email protected]
88 || Created On : 10-JAN-2002
89 || Purpose : Checks for the existance of Parent records.
90 || Known limitations, enhancements or remarks :
91 || Change History :
92 || Who When What
93 || (reverse chronological order - newest change first)
94 */
95 BEGIN
96 --rating changed to rating_code by gmuralid
97 IF (((old_references.rating_code = new_references.rating_code)) OR
98 ((new_references.rating_code IS NULL))) THEN
99 NULL;
100 ELSIF NOT igs_lookups_view_pkg.Get_PK_For_Validation (
101 'PE_CRE_RATING',
102 new_references.rating_code
103 ) THEN
104 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
105 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_RATING'));
106 IGS_GE_MSG_STACK.ADD;
107 App_Exception.Raise_Exception;
108 END IF;
109
110
111 IF (((old_references.credential_type_id = new_references.credential_type_id)) OR
112 ((new_references.credential_type_id IS NULL))) THEN
113 NULL;
114 ELSIF NOT igs_ad_cred_types_pkg.get_pk_for_validation (
115 new_references.credential_type_id,
116 'N'
117 ) THEN
118 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
119 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_CREDENTIAL_TYPE'));
120 IGS_GE_MSG_STACK.ADD;
121 App_Exception.Raise_Exception;
122 END IF;
123 END check_parent_existance;
124
125
126 FUNCTION get_pk_for_validation (
127 x_credential_id IN NUMBER
128 ) RETURN BOOLEAN AS
129 /*
130 || Created By : [email protected]
131 || Created On : 10-JAN-2002
132 || Purpose : Validates the Primary Key of the table.
133 || Known limitations, enhancements or remarks :
134 || Change History :
135 || Who When What
136 || (reverse chronological order - newest change first)
137 */
138 CURSOR cur_rowid IS
139 SELECT rowid
140 FROM igs_pe_credentials
141 WHERE credential_id = x_credential_id
142 FOR UPDATE NOWAIT;
143
144 lv_rowid cur_rowid%RowType;
145
146 BEGIN
147
148 OPEN cur_rowid;
149 FETCH cur_rowid INTO lv_rowid;
150 IF (cur_rowid%FOUND) THEN
151 CLOSE cur_rowid;
152 RETURN(TRUE);
153 ELSE
154 CLOSE cur_rowid;
155 RETURN(FALSE);
156 END IF;
157
158 END get_pk_for_validation;
159
160
161 PROCEDURE get_fk_igs_ad_cred_types (
162 x_credential_type_id IN NUMBER
163 ) AS
164 /*
165 || Created By : [email protected]
166 || Created On : 10-JAN-2002
167 || Purpose : Validates the Foreign Keys for the table.
168 || Known limitations, enhancements or remarks :
169 || Change History :
170 || Who When What
171 || (reverse chronological order - newest change first)
172 */
173 CURSOR cur_rowid IS
174 SELECT rowid
175 FROM igs_pe_credentials
176 WHERE ((credential_type_id = x_credential_type_id));
177
178 lv_rowid cur_rowid%RowType;
179
180 BEGIN
181
182 OPEN cur_rowid;
183 FETCH cur_rowid INTO lv_rowid;
184 IF (cur_rowid%FOUND) THEN
185 CLOSE cur_rowid;
186 fnd_message.set_name ('IGS', 'IGS_PE_PEC_CT_FK');
187 igs_ge_msg_stack.add;
188 app_exception.raise_exception;
189 RETURN;
190 END IF;
191 CLOSE cur_rowid;
192
193 END get_fk_igs_ad_cred_types;
194
195 /* PROCEDURE get_fk_igs_ad_code_classes (
196 x_code_id IN NUMBER
197 ) AS
198
199 || Created By : [email protected]
200 || Created On : 10-JAN-2002
201 || Purpose : Validates the Foreign Keys for the table.
202 || Known limitations, enhancements or remarks :
203 || Change History :
204 || Who When What
205 || (reverse chronological order - newest change first)
206
207 CURSOR cur_rowid IS
208 SELECT rowid
209 FROM igs_pe_credentials
210 WHERE ((rating = x_code_id));
211
212 lv_rowid cur_rowid%RowType;
213
214 BEGIN
215
216 OPEN cur_rowid;
217 FETCH cur_rowid INTO lv_rowid;
218 IF (cur_rowid%FOUND) THEN
219 CLOSE cur_rowid;
220 fnd_message.set_name ('IGS', 'IGS_PE_PEC_ACC_FK');
221 igs_ge_msg_stack.add;
222 app_exception.raise_exception;
223 RETURN;
224 END IF;
225 CLOSE cur_rowid;
226
227 END get_fk_igs_ad_code_classes; */
228
229 PROCEDURE get_fk_hz_parties (
230 x_party_id IN NUMBER
231 ) AS
232 /*
233 || Created By : [email protected]
234 || Created On : 10-JAN-2002
235 || Purpose : Validates the Foreign Keys for the table.
236 || Known limitations, enhancements or remarks :
237 || Change History :
238 || Who When What
239 || (reverse chronological order - newest change first)
240 */
241 CURSOR cur_rowid IS
242 SELECT rowid
243 FROM igs_pe_credentials
244 WHERE ((person_id = x_party_id));
245
246 lv_rowid cur_rowid%RowType;
247
248 BEGIN
249
250 OPEN cur_rowid;
251 FETCH cur_rowid INTO lv_rowid;
252 IF (cur_rowid%FOUND) THEN
253 CLOSE cur_rowid;
254 fnd_message.set_name ('IGS', 'IGS_PE_PEC_HZ_FK');
255 igs_ge_msg_stack.add;
256 app_exception.raise_exception;
257 RETURN;
258 END IF;
259 CLOSE cur_rowid;
260
261 END get_fk_hz_parties;
262
263
264 PROCEDURE before_dml (
265 p_action IN VARCHAR2,
266 x_rowid IN VARCHAR2 ,
267 x_credential_id IN NUMBER ,
268 x_person_id IN NUMBER ,
269 x_credential_type_id IN NUMBER ,
270 x_date_received IN DATE ,
271 x_reviewer_id IN NUMBER ,
272 x_reviewer_notes IN VARCHAR2 ,
273 x_recommender_name IN VARCHAR2 ,
274 x_recommender_title IN VARCHAR2 ,
275 x_recommender_organization IN VARCHAR2 ,
276 x_creation_date IN DATE ,
277 x_created_by IN NUMBER ,
278 x_last_update_date IN DATE ,
279 x_last_updated_by IN NUMBER ,
280 x_last_update_login IN NUMBER ,
281 x_rating_code IN VARCHAR2
282 ) AS
283 /*
284 || Created By : [email protected]
285 || Created On : 10-JAN-2002
286 || Purpose : Initialises the columns, Checks Constraints, Calls the
287 || Trigger Handlers for the table, before any DML operation.
288 || Known limitations, enhancements or remarks :
289 || Change History :
290 || Who When What
291 || ssawhney 16-apr-2003 BUG 2893294, reviewer can BE NULL.
292 */
293 CURSOR is_reviewer_evaluator IS
294 SELECT 'X'
295 FROM IGS_PE_TYP_INSTANCES PI,
296 IGS_PE_PERSON_TYPES PT
297 WHERE
298 PT.PERSON_TYPE_CODE = PI.PERSON_TYPE_CODE AND
299 PT.SYSTEM_TYPE IN ('STAFF','FACULTY','EVALUATOR') AND
300 SYSDATE BETWEEN PI.START_DATE AND NVL(PI.END_DATE,SYSDATE) AND
301 PI.PERSON_ID = x_reviewer_id;
302 l_reviewer VARCHAR2(1);
303
304 BEGIN
305
306 set_column_values (
307 p_action,
308 x_rowid,
309 x_credential_id,
310 x_person_id,
311 x_credential_type_id,
312 x_date_received,
313 x_reviewer_id,
314 x_reviewer_notes,
315 x_recommender_name,
316 x_recommender_title,
317 x_recommender_organization,
318 x_creation_date,
319 x_created_by,
320 x_last_update_date,
321 x_last_updated_by,
322 x_last_update_login,
323 x_rating_code
324 );
325 -- code added as a part of the bug 2121046. Reviewer cannot be the person in context and the
326 -- reviewer should have an active person type of EVALUATOR, STAFF or FACULTY.
327 -- ssawhney BUG 2893294, reviewer can BE NULL.
328 IF (p_action IN ('INSERT','UPDATE')) THEN
329 IF(x_reviewer_id = x_person_id) THEN
330 FND_MESSAGE.SET_NAME('IGS','IGS_PE_REVR_PRSN_SAME');
331 IGS_GE_MSG_STACK.ADD;
332 APP_EXCEPTION.RAISE_EXCEPTION;
333 ELSE
334 IF x_reviewer_id IS NOT NULL THEN
335 OPEN is_reviewer_evaluator;
336 FETCH is_reviewer_evaluator INTO l_reviewer;
337 IF is_reviewer_evaluator%NOTFOUND THEN
338 CLOSE is_reviewer_evaluator;
339 FND_MESSAGE.SET_NAME('IGS','IGS_PE_INVALID_REVR');
340 IGS_GE_MSG_STACK.ADD;
341 APP_EXCEPTION.RAISE_EXCEPTION;
342 ELSE
343 CLOSE is_reviewer_evaluator;
344 END IF;
345 END IF;
346 END IF;
347 END IF;
348 IF (p_action = 'INSERT') THEN
349 -- Call all the procedures related to Before Insert.
350 IF ( get_pk_for_validation(
351 new_references.credential_id
352 )
353 ) THEN
354 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
355 igs_ge_msg_stack.add;
356 app_exception.raise_exception;
357 END IF;
358 check_parent_existance;
359 ELSIF (p_action = 'UPDATE') THEN
360 -- Call all the procedures related to Before Update.
361 check_parent_existance;
362 ELSIF (p_action = 'VALIDATE_INSERT') THEN
363 -- Call all the procedures related to Before Insert.
364 IF ( get_pk_for_validation (
365 new_references.credential_id
366 )
367 ) THEN
368 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
369 igs_ge_msg_stack.add;
370 app_exception.raise_exception;
371 END IF;
372 END IF;
373
374 END before_dml;
375
376
377 PROCEDURE insert_row (
378 x_rowid IN OUT NOCOPY VARCHAR2,
379 x_credential_id IN OUT NOCOPY NUMBER,
380 x_person_id IN NUMBER,
381 x_credential_type_id IN NUMBER,
382 x_date_received IN DATE,
383 x_reviewer_id IN NUMBER,
384 x_reviewer_notes IN VARCHAR2,
385 x_recommender_name IN VARCHAR2,
386 x_recommender_title IN VARCHAR2,
387 x_recommender_organization IN VARCHAR2,
388 x_mode IN VARCHAR2 ,
389 x_rating_code IN varchar2
390 ) AS
391 /*
392 || Created By : [email protected]
393 || Created On : 10-JAN-2002
394 || Purpose : Handles the INSERT DML logic for the table.
395 || Known limitations, enhancements or remarks :
396 || Change History :
397 || Who When What
398 || (reverse chronological order - newest change first)
399 */
400 CURSOR c IS
401 SELECT rowid
402 FROM igs_pe_credentials
403 WHERE credential_id = x_credential_id;
404
405 x_last_update_date DATE;
406 x_last_updated_by NUMBER;
407 x_last_update_login NUMBER;
408
409 BEGIN
410
411 x_last_update_date := SYSDATE;
412 IF (x_mode = 'I') THEN
413 x_last_updated_by := 1;
414 x_last_update_login := 0;
415 ELSIF (X_MODE IN ('R', 'S')) THEN
416 x_last_updated_by := fnd_global.user_id;
417 IF (x_last_updated_by IS NULL) THEN
418 x_last_updated_by := -1;
419 END IF;
420 x_last_update_login := fnd_global.login_id;
421 IF (x_last_update_login IS NULL) THEN
422 x_last_update_login := -1;
423 END IF;
424 ELSE
425 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
426 igs_ge_msg_stack.add;
427 app_exception.raise_exception;
428 END IF;
429
430 SELECT igs_pe_credentials_s.NEXTVAL
431 INTO x_credential_id
432 FROM dual;
433
434 before_dml(
435 p_action => 'INSERT',
436 x_rowid => x_rowid,
437 x_credential_id => x_credential_id,
438 x_person_id => x_person_id,
439 x_credential_type_id => x_credential_type_id,
440 x_date_received => x_date_received,
441 x_reviewer_id => x_reviewer_id,
442 x_reviewer_notes => x_reviewer_notes,
443 x_recommender_name => x_recommender_name,
444 x_recommender_title => x_recommender_title,
445 x_recommender_organization => x_recommender_organization,
446 x_creation_date => x_last_update_date,
447 x_created_by => x_last_updated_by,
448 x_last_update_date => x_last_update_date,
449 x_last_updated_by => x_last_updated_by,
450 x_last_update_login => x_last_update_login ,
451 x_rating_code => x_rating_code
452 );
453
454 IF (x_mode = 'S') THEN
455 igs_sc_gen_001.set_ctx('R');
456 END IF;
457 INSERT INTO igs_pe_credentials (
458 credential_id,
459 person_id,
460 credential_type_id,
461 date_received,
462 reviewer_id,
463 reviewer_notes,
464 recommender_name,
465 recommender_title,
466 recommender_organization,
467 creation_date,
468 created_by,
469 last_update_date,
470 last_updated_by,
471 last_update_login,
472 rating_code
473 ) VALUES (
474 new_references.credential_id,
475 new_references.person_id,
476 new_references.credential_type_id,
477 new_references.date_received,
478 new_references.reviewer_id,
479 new_references.reviewer_notes,
480 new_references.recommender_name,
481 new_references.recommender_title,
482 new_references.recommender_organization,
483 x_last_update_date,
484 x_last_updated_by,
485 x_last_update_date,
486 x_last_updated_by,
487 x_last_update_login,
488 new_references.rating_code
489 );
490 IF (x_mode = 'S') THEN
491 igs_sc_gen_001.unset_ctx('R');
492 END IF;
493
494
495 OPEN c;
496 FETCH c INTO x_rowid;
497 IF (c%NOTFOUND) THEN
498 CLOSE c;
499 RAISE NO_DATA_FOUND;
500 END IF;
501 CLOSE c;
502
503
504 EXCEPTION
505 WHEN OTHERS THEN
506 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
507 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
508 fnd_message.set_token ('ERR_CD', SQLCODE);
509 igs_ge_msg_stack.add;
510 igs_sc_gen_001.unset_ctx('R');
511 app_exception.raise_exception;
512 ELSE
513 igs_sc_gen_001.unset_ctx('R');
514 RAISE;
515 END IF;
516 END insert_row;
517
518
519 PROCEDURE lock_row (
520 x_rowid IN VARCHAR2,
521 x_credential_id IN NUMBER,
522 x_person_id IN NUMBER,
523 x_credential_type_id IN NUMBER,
524 x_date_received IN DATE,
525 x_reviewer_id IN NUMBER,
526 x_reviewer_notes IN VARCHAR2,
527 x_recommender_name IN VARCHAR2,
528 x_recommender_title IN VARCHAR2,
529 x_recommender_organization IN VARCHAR2,
530 x_rating_code IN VARCHAR2
531 ) AS
532 /*
533 || Created By : [email protected]
534 || Created On : 10-JAN-2002
535 || Purpose : Handles the LOCK mechanism for the table.
536 || Known limitations, enhancements or remarks :
537 || Change History :
538 || Who When What
539 || (reverse chronological order - newest change first)
540 */
541 CURSOR c1 IS
542 SELECT
543 person_id,
544 credential_type_id,
545 date_received,
546 reviewer_id,
547 reviewer_notes,
548 recommender_name,
549 recommender_title,
550 recommender_organization,
551 rating_code
552 FROM igs_pe_credentials
553 WHERE rowid = x_rowid
554 FOR UPDATE NOWAIT;
555
556 tlinfo c1%ROWTYPE;
557
558 BEGIN
559
560 OPEN c1;
561 FETCH c1 INTO tlinfo;
562 IF (c1%notfound) THEN
563 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
564 igs_ge_msg_stack.add;
565 CLOSE c1;
566 app_exception.raise_exception;
567 RETURN;
568 END IF;
569 CLOSE c1;
570
571 IF (
572 (tlinfo.person_id = x_person_id)
573 AND (tlinfo.credential_type_id = x_credential_type_id)
574 AND ((tlinfo.date_received = x_date_received) OR ((tlinfo.date_received IS NULL) AND (X_date_received IS NULL)))
575 AND ((tlinfo.reviewer_id = x_reviewer_id) OR ((tlinfo.reviewer_id IS NULL) AND (X_reviewer_id IS NULL)))
576 AND ((tlinfo.reviewer_notes = x_reviewer_notes) OR ((tlinfo.reviewer_notes IS NULL) AND (X_reviewer_notes IS NULL)))
577 AND ((tlinfo.recommender_name = x_recommender_name) OR ((tlinfo.recommender_name IS NULL) AND (X_recommender_name IS NULL)))
578 AND ((tlinfo.recommender_title = x_recommender_title) OR ((tlinfo.recommender_title IS NULL) AND (X_recommender_title IS NULL)))
579 AND ((tlinfo.recommender_organization = x_recommender_organization) OR ((tlinfo.recommender_organization IS NULL) AND (X_recommender_organization IS NULL)))
580 AND ((tlinfo.rating_code = x_rating_code) OR ((tlinfo.rating_code IS NULL) AND (X_rating_code IS NULL)))
581 ) THEN
582 NULL;
583 ELSE
584 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
585 igs_ge_msg_stack.add;
586 app_exception.raise_exception;
587 END IF;
588
589 RETURN;
590
591 END lock_row;
592
593
594 PROCEDURE update_row (
595 x_rowid IN VARCHAR2,
596 x_credential_id IN NUMBER,
597 x_person_id IN NUMBER,
598 x_credential_type_id IN NUMBER,
599 x_date_received IN DATE,
600 x_reviewer_id IN NUMBER,
601 x_reviewer_notes IN VARCHAR2,
602 x_recommender_name IN VARCHAR2,
603 x_recommender_title IN VARCHAR2,
604 x_recommender_organization IN VARCHAR2,
605 x_mode IN VARCHAR2 ,
606 x_rating_code IN VARCHAR2
607 ) AS
608 /*
609 || Created By : [email protected]
610 || Created On : 10-JAN-2002
611 || Purpose : Handles the UPDATE DML logic for the table.
612 || Known limitations, enhancements or remarks :
613 || Change History :
614 || Who When What
615 || (reverse chronological order - newest change first)
616 */
617 x_last_update_date DATE ;
618 x_last_updated_by NUMBER;
619 x_last_update_login NUMBER;
620
621 BEGIN
622
623 x_last_update_date := SYSDATE;
624 IF (X_MODE = 'I') THEN
625 x_last_updated_by := 1;
626 x_last_update_login := 0;
627 ELSIF (X_MODE IN ('R', 'S')) THEN
628 x_last_updated_by := fnd_global.user_id;
629 IF x_last_updated_by IS NULL THEN
630 x_last_updated_by := -1;
631 END IF;
632 x_last_update_login := fnd_global.login_id;
633 IF (x_last_update_login IS NULL) THEN
634 x_last_update_login := -1;
635 END IF;
636 ELSE
637 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
638 igs_ge_msg_stack.add;
639 app_exception.raise_exception;
640 END IF;
641
642 before_dml(
643 p_action => 'UPDATE',
644 x_rowid => x_rowid,
645 x_credential_id => x_credential_id,
646 x_person_id => x_person_id,
647 x_credential_type_id => x_credential_type_id,
648 x_date_received => x_date_received,
649 x_reviewer_id => x_reviewer_id,
650 x_reviewer_notes => x_reviewer_notes,
651 x_recommender_name => x_recommender_name,
652 x_recommender_title => x_recommender_title,
653 x_recommender_organization => x_recommender_organization,
654 x_creation_date => x_last_update_date,
655 x_created_by => x_last_updated_by,
656 x_last_update_date => x_last_update_date,
657 x_last_updated_by => x_last_updated_by,
658 x_last_update_login => x_last_update_login ,
659 x_rating_code => x_rating_code
660 );
661
662 IF (x_mode = 'S') THEN
663 igs_sc_gen_001.set_ctx('R');
664 END IF;
665 UPDATE igs_pe_credentials
666 SET
667 person_id = new_references.person_id,
668 credential_type_id = new_references.credential_type_id,
669 date_received = new_references.date_received,
670 reviewer_id = new_references.reviewer_id,
671 reviewer_notes = new_references.reviewer_notes,
672 recommender_name = new_references.recommender_name,
673 recommender_title = new_references.recommender_title,
674 recommender_organization = new_references.recommender_organization,
675 last_update_date = x_last_update_date,
676 last_updated_by = x_last_updated_by,
677 last_update_login = x_last_update_login ,
678 rating_code = new_references.rating_code
679 WHERE rowid = x_rowid;
680
681 IF (SQL%NOTFOUND) THEN
682 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
683 igs_ge_msg_stack.add;
684 igs_sc_gen_001.unset_ctx('R');
685 app_exception.raise_exception;
686 END IF;
687 IF (x_mode = 'S') THEN
688 igs_sc_gen_001.unset_ctx('R');
689 END IF;
690
691
692
693 EXCEPTION
694 WHEN OTHERS THEN
695 IF (SQLCODE = (-28115)) THEN
696 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
697 fnd_message.set_token ('ERR_CD', SQLCODE);
698 igs_ge_msg_stack.add;
699 igs_sc_gen_001.unset_ctx('R');
700 app_exception.raise_exception;
701 ELSE
702 igs_sc_gen_001.unset_ctx('R');
703 RAISE;
704 END IF;
705 END update_row;
706
707
708 PROCEDURE add_row (
709 x_rowid IN OUT NOCOPY VARCHAR2,
710 x_credential_id IN OUT NOCOPY NUMBER,
711 x_person_id IN NUMBER,
712 x_credential_type_id IN NUMBER,
713 x_date_received IN DATE,
714 x_reviewer_id IN NUMBER,
715 x_reviewer_notes IN VARCHAR2,
716 x_recommender_name IN VARCHAR2,
717 x_recommender_title IN VARCHAR2,
718 x_recommender_organization IN VARCHAR2,
719 x_mode IN VARCHAR2 ,
720 x_rating_code IN VARCHAR2
721 ) AS
722 /*
723 || Created By : [email protected]
724 || Created On : 10-JAN-2002
725 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
726 || Known limitations, enhancements or remarks :
727 || Change History :
728 || Who When What
729 || (reverse chronological order - newest change first)
730 */
731 CURSOR c1 IS
732 SELECT rowid
733 FROM igs_pe_credentials
734 WHERE credential_id = x_credential_id;
735
736 BEGIN
737
738 OPEN c1;
739 FETCH c1 INTO x_rowid;
740 IF (c1%NOTFOUND) THEN
741 CLOSE c1;
742
743 insert_row (
744 x_rowid,
745 x_credential_id,
746 x_person_id,
747 x_credential_type_id,
748 x_date_received,
749 x_reviewer_id,
750 x_reviewer_notes,
751 x_recommender_name,
752 x_recommender_title,
753 x_recommender_organization,
754 x_mode,
755 x_rating_code
756 );
757 RETURN;
758 END IF;
759 CLOSE c1;
760
761 update_row (
762 x_rowid,
763 x_credential_id,
764 x_person_id,
765 x_credential_type_id,
766 x_date_received,
767 x_reviewer_id,
768 x_reviewer_notes,
769 x_recommender_name,
770 x_recommender_title,
771 x_recommender_organization,
772 x_mode ,
773 x_rating_code
774 );
775
776 END add_row;
777
778
779 PROCEDURE delete_row (
780 x_rowid IN VARCHAR2,
781 x_mode IN VARCHAR2
782 ) AS
783 /*
784 || Created By : [email protected]
785 || Created On : 10-JAN-2002
786 || Purpose : Handles the DELETE DML logic for the table.
787 || Known limitations, enhancements or remarks :
788 || Change History :
789 || Who When What
790 || (reverse chronological order - newest change first)
791 */
792 BEGIN
793
794 before_dml (
795 p_action => 'DELETE',
796 x_rowid => x_rowid
797 );
798
799 IF (x_mode = 'S') THEN
800 igs_sc_gen_001.set_ctx('R');
801 END IF;
802 DELETE FROM igs_pe_credentials
803 WHERE rowid = x_rowid;
804
805 IF (SQL%NOTFOUND) THEN
806 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
807 igs_ge_msg_stack.add;
808 igs_sc_gen_001.unset_ctx('R');
809 app_exception.raise_exception;
810 END IF;
811 IF (x_mode = 'S') THEN
812 igs_sc_gen_001.unset_ctx('R');
813 END IF;
814
815
816 END delete_row;
817
818
819 END igs_pe_credentials_pkg;
[Home] [Help]