1 PACKAGE BODY igs_ad_rvgr_evaltr_pkg AS
2 /* $Header: IGSAIF6B.pls 115.10 2003/03/25 13:38:59 rghosh noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_ad_rvgr_evaltr%ROWTYPE;
6 new_references igs_ad_rvgr_evaltr%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_revgr_evaluator_id IN NUMBER DEFAULT NULL,
12 x_appl_revprof_revgr_id IN NUMBER DEFAULT NULL,
13 x_person_id IN NUMBER DEFAULT NULL,
14 x_person_number IN VARCHAR2 DEFAULT NULL,
15 x_evaluation_sequence IN NUMBER DEFAULT NULL,
16 x_program_approver_ind IN VARCHAR2 DEFAULT NULL,
17 x_creation_date IN DATE DEFAULT NULL,
18 x_created_by IN NUMBER DEFAULT NULL,
19 x_last_update_date IN DATE DEFAULT NULL,
20 x_last_updated_by IN NUMBER DEFAULT NULL,
21 x_last_update_login IN NUMBER DEFAULT NULL
22 ) AS
23 /*
24 || Created By : [email protected]
25 || Created On : 09-NOV-2001
26 || Purpose : Initialises the Old and New references for the columns of the table.
27 || Known limitations, enhancements or remarks :
28 || Change History :
29 || Who When What
30 || (reverse chronological order - newest change first)
31 */
32
33 CURSOR cur_old_ref_values IS
34 SELECT *
35 FROM igs_ad_rvgr_evaltr
36 WHERE rowid = x_rowid;
37
38 BEGIN
39
40 l_rowid := x_rowid;
41
42 -- Code for setting the Old and New Reference Values.
43 -- Populate Old Values.
44 OPEN cur_old_ref_values;
45 FETCH cur_old_ref_values INTO old_references;
46 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
47 CLOSE cur_old_ref_values;
48 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
49 igs_ge_msg_stack.add;
50 app_exception.raise_exception;
51 RETURN;
52 END IF;
53 CLOSE cur_old_ref_values;
54
55 -- Populate New Values.
56 new_references.revgr_evaluator_id := x_revgr_evaluator_id;
57 new_references.appl_revprof_revgr_id := x_appl_revprof_revgr_id;
58 new_references.person_id := x_person_id;
59 new_references.person_number := x_person_number;
60 new_references.evaluation_sequence := x_evaluation_sequence;
61 new_references.program_approver_ind := x_program_approver_ind;
62
63 IF (p_action = 'UPDATE') THEN
64 new_references.creation_date := old_references.creation_date;
65 new_references.created_by := old_references.created_by;
66 ELSE
67 new_references.creation_date := x_creation_date;
68 new_references.created_by := x_created_by;
69 END IF;
70
71 new_references.last_update_date := x_last_update_date;
72 new_references.last_updated_by := x_last_updated_by;
73 new_references.last_update_login := x_last_update_login;
74
75 END set_column_values;
76
77 PROCEDURE Check_Uniqueness AS
78 /*************************************************************
79 Created By :Rishi Ghosh
80 Date Created By :23-Dec-2002
81 Purpose :
82 Know limitations, enhancements or remarks
83 Change History
84 Who When What
85
86 (reverse chronological order - newest change first)
87 ***************************************************************/
88
89 begin
90 IF Get_Uk_For_Validation (
91 new_references.appl_revprof_revgr_id
92 ,new_references.person_id
93 ) THEN
94 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
95 IGS_GE_MSG_STACK.ADD;
96 app_exception.raise_exception;
97 END IF;
98 END Check_Uniqueness ;
99
100
101 PROCEDURE check_parent_existance AS
102 /*
103 || Created By : [email protected]
104 || Created On : 09-NOV-2001
105 || Purpose : Checks for the existance of Parent records.
106 || Known limitations, enhancements or remarks :
107 || Change History :
108 || Who When What
109 || (reverse chronological order - newest change first)
110 */
111 BEGIN
112
113 IF (((old_references.appl_revprof_revgr_id = new_references.appl_revprof_revgr_id)) OR
114 ((new_references.appl_revprof_revgr_id IS NULL))) THEN
115 NULL;
116 ELSIF NOT igs_ad_apl_rprf_rgr_pkg.get_pk_for_validation (
117 new_references.appl_revprof_revgr_id
118 ) THEN
119 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
120 igs_ge_msg_stack.add;
121 app_exception.raise_exception;
122 END IF;
123
124 IF (((old_references.person_id = new_references.person_id)) OR
125 ((new_references.person_id IS NULL))) THEN
126 NULL;
127 ELSIF NOT igs_pe_person_pkg.get_pk_for_validation (
128 new_references.person_id
129 ) THEN
130 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
131 igs_ge_msg_stack.add;
132 app_exception.raise_exception;
133 END IF;
134
135 END check_parent_existance;
136
137
138 FUNCTION get_pk_for_validation (
139 x_revgr_evaluator_id IN NUMBER
140 ) RETURN BOOLEAN AS
141 /*
142 || Created By : [email protected]
143 || Created On : 09-NOV-2001
144 || Purpose : Validates the Primary Key of the table.
145 || Known limitations, enhancements or remarks :
146 || Change History :
147 || Who When What
148 || (reverse chronological order - newest change first)
149 */
150 CURSOR cur_rowid IS
151 SELECT rowid
152 FROM igs_ad_rvgr_evaltr
153 WHERE revgr_evaluator_id = x_revgr_evaluator_id
154 FOR UPDATE NOWAIT;
155
156 lv_rowid cur_rowid%RowType;
157
158 BEGIN
159
160 OPEN cur_rowid;
161 FETCH cur_rowid INTO lv_rowid;
162 IF (cur_rowid%FOUND) THEN
163 CLOSE cur_rowid;
164 RETURN(TRUE);
165 ELSE
166 CLOSE cur_rowid;
167 RETURN(FALSE);
168 END IF;
169
170 END get_pk_for_validation;
171
172
173 FUNCTION get_uk_for_validation (
174 x_appl_revprof_revgr_id IN NUMBER,
175 x_person_id IN NUMBER
176 ) RETURN BOOLEAN AS
177 /*
178 || Created By : [email protected]
179 || Created On : 17-dec-2002
180 || Purpose : Validates the Unique Key of the table.
181 || Known limitations, enhancements or remarks :
182 || Change History :
183 || Who When What
184 || (reverse chronological order - newest change first)
185 */
186 CURSOR cur_rowid IS
187 SELECT rowid
188 FROM igs_ad_rvgr_evaltr
189 WHERE appl_revprof_revgr_id = x_appl_revprof_revgr_id
190 AND person_id = x_person_id
191 AND (l_rowid IS NULL OR rowid <> l_rowid)
192 FOR UPDATE NOWAIT;
193
194 lv_rowid cur_rowid%RowType;
195
196 BEGIN
197
198 OPEN cur_rowid;
199 FETCH cur_rowid INTO lv_rowid;
200 IF (cur_rowid%FOUND) THEN
201 CLOSE cur_rowid;
202 RETURN(TRUE);
203 ELSE
204 CLOSE cur_rowid;
205 RETURN(FALSE);
206 END IF;
207
208 END get_uk_for_validation;
209
210
211 PROCEDURE get_fk_igs_ad_apl_rprf_rgr (
212 x_appl_revprof_revgr_id IN NUMBER
213 ) AS
214 /*
215 || Created By : [email protected]
216 || Created On : 09-NOV-2001
217 || Purpose : Validates the Foreign Keys for the table.
218 || Known limitations, enhancements or remarks :
219 || Change History :
220 || Who When What
221 || (reverse chronological order - newest change first)
222 */
223 CURSOR cur_rowid IS
224 SELECT rowid
225 FROM igs_ad_rvgr_evaltr
226 WHERE ((appl_revprof_revgr_id = x_appl_revprof_revgr_id));
227
228 lv_rowid cur_rowid%RowType;
229
230 BEGIN
231
232 OPEN cur_rowid;
233 FETCH cur_rowid INTO lv_rowid;
234 IF (cur_rowid%FOUND) THEN
235 CLOSE cur_rowid;
236 fnd_message.set_name ('IGS', 'IGS_AD_RGREVL_APRRGR_FK');
237 igs_ge_msg_stack.add;
238 app_exception.raise_exception;
239 RETURN;
240 END IF;
241 CLOSE cur_rowid;
242 END get_fk_igs_ad_apl_rprf_rgr;
243
244 PROCEDURE get_fk_hz_parties (
245 x_person_id IN NUMBER
246 ) AS
247 /*
248 || Created By : [email protected]
249 || Created On : 09-NOV-2001
250 || Purpose : Validates the Foreign Keys for the table.
251 || Known limitations, enhancements or remarks :
252 || Change History :
253 || Who When What
254 || (reverse chronological order - newest change first)
255 */
256 CURSOR cur_rowid IS
257 SELECT rowid
258 FROM igs_ad_rvgr_evaltr
259 WHERE (person_id = x_person_id) ;
260
261 lv_rowid cur_rowid%RowType;
262
263 BEGIN
264
265 OPEN cur_rowid;
266 FETCH cur_rowid INTO lv_rowid;
267 IF (cur_rowid%FOUND) THEN
268 CLOSE cur_rowid;
269 fnd_message.set_name ('IGS', 'IGS_AD_RGREVL_HP_FK');
270 igs_ge_msg_stack.add;
271 app_exception.raise_exception;
272 RETURN;
273 END IF;
274 CLOSE cur_rowid;
275
276 END get_fk_hz_parties;
277
278
279 PROCEDURE before_dml (
280 p_action IN VARCHAR2,
281 x_rowid IN VARCHAR2 DEFAULT NULL,
282 x_revgr_evaluator_id IN NUMBER DEFAULT NULL,
283 x_appl_revprof_revgr_id IN NUMBER DEFAULT NULL,
284 x_person_id IN NUMBER DEFAULT NULL,
285 x_person_number IN VARCHAR2 DEFAULT NULL,
286 x_evaluation_sequence IN NUMBER DEFAULT NULL,
287 x_program_approver_ind IN VARCHAR2 DEFAULT NULL,
288 x_creation_date IN DATE DEFAULT NULL,
289 x_created_by IN NUMBER DEFAULT NULL,
290 x_last_update_date IN DATE DEFAULT NULL,
291 x_last_updated_by IN NUMBER DEFAULT NULL,
292 x_last_update_login IN NUMBER DEFAULT NULL
293 ) AS
294 /*
295 || Created By : [email protected]
296 || Created On : 09-NOV-2001
297 || Purpose : Initialises the columns, Checks Constraints, Calls the
298 || Trigger Handlers for the table, before any DML operation.
299 || Known limitations, enhancements or remarks :
300 || Change History :
301 || Who When What
302 || (reverse chronological order - newest change first)
303 */
304 BEGIN
305
306 set_column_values (
307 p_action,
308 x_rowid,
309 x_revgr_evaluator_id,
310 x_appl_revprof_revgr_id,
311 x_person_id,
312 x_person_number,
313 x_evaluation_sequence,
314 x_program_approver_ind,
315 x_creation_date,
316 x_created_by,
317 x_last_update_date,
318 x_last_updated_by,
319 x_last_update_login
320 );
321
322 IF (p_action = 'INSERT') THEN
323 -- Call all the procedures related to Before Insert.
324 IF ( get_pk_for_validation(
325 new_references.revgr_evaluator_id
326 )
327 ) THEN
328 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
329 igs_ge_msg_stack.add;
330 app_exception.raise_exception;
331 END IF;
332 Check_Uniqueness;
333
334 check_parent_existance;
335 ELSIF (p_action = 'UPDATE') THEN
336 -- Call all the procedures related to Before Update.
337 check_uniqueness;
338 check_parent_existance;
339 ELSIF (p_action = 'VALIDATE_INSERT') THEN
340 -- Call all the procedures related to Before Insert.
341 IF ( get_pk_for_validation (
342 new_references.revgr_evaluator_id
343 )
344 ) THEN
345 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
346 igs_ge_msg_stack.add;
347 app_exception.raise_exception;
348 END IF;
349 Check_Uniqueness;
350 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
351 -- Call all the procedures related to Before Insert.
352 Check_Uniqueness;
353 END IF;
354
355 l_rowid :=NULL;
356
357 END before_dml;
358
359
360 PROCEDURE insert_row (
361 x_rowid IN OUT NOCOPY VARCHAR2,
362 x_revgr_evaluator_id IN OUT NOCOPY NUMBER,
363 x_appl_revprof_revgr_id IN NUMBER,
364 x_person_id IN NUMBER,
365 x_person_number IN VARCHAR2,
366 x_evaluation_sequence IN NUMBER,
367 x_program_approver_ind IN VARCHAR2,
368 x_mode IN VARCHAR2 DEFAULT 'R'
369 ) AS
370 /*
371 || Created By : [email protected]
372 || Created On : 09-NOV-2001
373 || Purpose : Handles the INSERT DML logic for the table.
374 || Known limitations, enhancements or remarks :
375 || Change History :
376 || Who When What
377 || (reverse chronological order - newest change first)
378 */
379 CURSOR c IS
380 SELECT rowid
381 FROM igs_ad_rvgr_evaltr
382 WHERE revgr_evaluator_id = x_revgr_evaluator_id;
383
384 x_last_update_date DATE;
385 x_last_updated_by NUMBER;
386 x_last_update_login NUMBER;
387
388 BEGIN
389
390 x_last_update_date := SYSDATE;
391 IF (x_mode = 'I') THEN
392 x_last_updated_by := 1;
393 x_last_update_login := 0;
394 ELSIF (x_mode = 'R') THEN
395 x_last_updated_by := fnd_global.user_id;
396 IF (x_last_updated_by IS NULL) THEN
397 x_last_updated_by := -1;
398 END IF;
399 x_last_update_login := fnd_global.login_id;
400 IF (x_last_update_login IS NULL) THEN
401 x_last_update_login := -1;
402 END IF;
403 ELSE
404 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
405 igs_ge_msg_stack.add;
406 app_exception.raise_exception;
407 END IF;
408
409 x_revgr_evaluator_id := -1;
410 before_dml(
411 p_action => 'INSERT',
412 x_rowid => x_rowid,
413 x_revgr_evaluator_id => x_revgr_evaluator_id,
414 x_appl_revprof_revgr_id => x_appl_revprof_revgr_id,
415 x_person_id => x_person_id,
416 x_person_number => x_person_number,
417 x_evaluation_sequence => x_evaluation_sequence,
418 x_program_approver_ind => x_program_approver_ind,
419 x_creation_date => x_last_update_date,
420 x_created_by => x_last_updated_by,
421 x_last_update_date => x_last_update_date,
422 x_last_updated_by => x_last_updated_by,
423 x_last_update_login => x_last_update_login
424 );
425
426 INSERT INTO igs_ad_rvgr_evaltr (
427 revgr_evaluator_id,
428 appl_revprof_revgr_id,
429 person_id,
430 person_number,
431 evaluation_sequence,
432 program_approver_ind,
433 creation_date,
434 created_by,
435 last_update_date,
436 last_updated_by,
437 last_update_login
438 ) VALUES (
439 igs_ad_rvgr_evaltr_s.NEXTVAL,
440 new_references.appl_revprof_revgr_id,
441 new_references.person_id,
442 new_references.person_number,
443 new_references.evaluation_sequence,
444 new_references.program_approver_ind,
445 x_last_update_date,
446 x_last_updated_by,
447 x_last_update_date,
448 x_last_updated_by,
449 x_last_update_login
450 )RETURNING revgr_evaluator_id INTO x_revgr_evaluator_id;
451
452 OPEN c;
453 FETCH c INTO x_rowid;
454 IF (c%NOTFOUND) THEN
455 CLOSE c;
456 RAISE NO_DATA_FOUND;
457 END IF;
458 CLOSE c;
459
460 END insert_row;
461
462
463 PROCEDURE lock_row (
464 x_rowid IN VARCHAR2,
465 x_revgr_evaluator_id IN NUMBER,
466 x_appl_revprof_revgr_id IN NUMBER,
467 x_person_id IN NUMBER,
468 x_person_number IN VARCHAR2,
469 x_evaluation_sequence IN NUMBER,
470 x_program_approver_ind IN VARCHAR2
471 ) AS
472 /*
473 || Created By : [email protected]
474 || Created On : 09-NOV-2001
475 || Purpose : Handles the LOCK mechanism for the table.
476 || Known limitations, enhancements or remarks :
477 || Change History :
478 || Who When What
479 || (reverse chronological order - newest change first)
480 */
481 CURSOR c1 IS
482 SELECT
483 appl_revprof_revgr_id,
484 person_id,
485 person_number,
486 evaluation_sequence,
487 program_approver_ind
488 FROM igs_ad_rvgr_evaltr
489 WHERE rowid = x_rowid
490 FOR UPDATE NOWAIT;
491
492 tlinfo c1%ROWTYPE;
493
494 BEGIN
495
496 OPEN c1;
497 FETCH c1 INTO tlinfo;
498 IF (c1%notfound) THEN
499 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
500 igs_ge_msg_stack.add;
501 CLOSE c1;
502 app_exception.raise_exception;
503 RETURN;
504 END IF;
505 CLOSE c1;
506
507 IF (
508 (tlinfo.appl_revprof_revgr_id = x_appl_revprof_revgr_id)
509 AND (tlinfo.person_id = x_person_id)
510 AND (tlinfo.person_number = x_person_number)
511 AND ((tlinfo.evaluation_sequence = x_evaluation_sequence) OR ((tlinfo.evaluation_sequence IS NULL) AND (X_evaluation_sequence IS NULL)))
512 AND (tlinfo.program_approver_ind = x_program_approver_ind)
513 ) THEN
514 NULL;
515 ELSE
516 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
517 igs_ge_msg_stack.add;
518 app_exception.raise_exception;
519 END IF;
520
521 RETURN;
522
523 END lock_row;
524
525
526 PROCEDURE update_row (
527 x_rowid IN VARCHAR2,
528 x_revgr_evaluator_id IN NUMBER,
529 x_appl_revprof_revgr_id IN NUMBER,
530 x_person_id IN NUMBER,
531 x_person_number IN VARCHAR2,
532 x_evaluation_sequence IN NUMBER,
533 x_program_approver_ind IN VARCHAR2,
534 x_mode IN VARCHAR2 DEFAULT 'R'
535 ) AS
536 /*
537 || Created By : [email protected]
538 || Created On : 09-NOV-2001
539 || Purpose : Handles the UPDATE DML logic for the table.
540 || Known limitations, enhancements or remarks :
541 || Change History :
542 || Who When What
543 || (reverse chronological order - newest change first)
544 */
545 x_last_update_date DATE ;
546 x_last_updated_by NUMBER;
547 x_last_update_login NUMBER;
548
549 BEGIN
550
551 x_last_update_date := SYSDATE;
552 IF (X_MODE = 'I') THEN
553 x_last_updated_by := 1;
554 x_last_update_login := 0;
555 ELSIF (x_mode = 'R') THEN
556 x_last_updated_by := fnd_global.user_id;
557 IF x_last_updated_by IS NULL THEN
558 x_last_updated_by := -1;
559 END IF;
560 x_last_update_login := fnd_global.login_id;
561 IF (x_last_update_login IS NULL) THEN
562 x_last_update_login := -1;
563 END IF;
564 ELSE
565 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
566 igs_ge_msg_stack.add;
567 app_exception.raise_exception;
568 END IF;
569
570 before_dml(
571 p_action => 'UPDATE',
572 x_rowid => x_rowid,
573 x_revgr_evaluator_id => x_revgr_evaluator_id,
574 x_appl_revprof_revgr_id => x_appl_revprof_revgr_id,
575 x_person_id => x_person_id,
576 x_person_number => x_person_number,
577 x_evaluation_sequence => x_evaluation_sequence,
578 x_program_approver_ind => x_program_approver_ind,
579 x_creation_date => x_last_update_date,
580 x_created_by => x_last_updated_by,
581 x_last_update_date => x_last_update_date,
582 x_last_updated_by => x_last_updated_by,
583 x_last_update_login => x_last_update_login
584 );
585
586 UPDATE igs_ad_rvgr_evaltr
587 SET
588 appl_revprof_revgr_id = new_references.appl_revprof_revgr_id,
589 person_id = new_references.person_id,
590 person_number = new_references.person_number,
591 evaluation_sequence = new_references.evaluation_sequence,
592 program_approver_ind = new_references.program_approver_ind,
593 last_update_date = x_last_update_date,
594 last_updated_by = x_last_updated_by,
595 last_update_login = x_last_update_login
596 WHERE rowid = x_rowid;
597
598 IF (SQL%NOTFOUND) THEN
599 RAISE NO_DATA_FOUND;
600 END IF;
601
602 END update_row;
603
604
605 PROCEDURE add_row (
606 x_rowid IN OUT NOCOPY VARCHAR2,
607 x_revgr_evaluator_id IN OUT NOCOPY NUMBER,
608 x_appl_revprof_revgr_id IN NUMBER,
609 x_person_id IN NUMBER,
610 x_person_number IN VARCHAR2,
611 x_evaluation_sequence IN NUMBER,
612 x_program_approver_ind IN VARCHAR2,
613 x_mode IN VARCHAR2 DEFAULT 'R'
614 ) AS
615 /*
616 || Created By : [email protected]
617 || Created On : 09-NOV-2001
618 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
619 || Known limitations, enhancements or remarks :
620 || Change History :
621 || Who When What
622 || (reverse chronological order - newest change first)
623 */
624 CURSOR c1 IS
625 SELECT rowid
626 FROM igs_ad_rvgr_evaltr
627 WHERE revgr_evaluator_id = x_revgr_evaluator_id;
628
629 BEGIN
630
631 OPEN c1;
632 FETCH c1 INTO x_rowid;
633 IF (c1%NOTFOUND) THEN
634 CLOSE c1;
635
636 insert_row (
637 x_rowid,
638 x_revgr_evaluator_id,
639 x_appl_revprof_revgr_id,
640 x_person_id,
641 x_person_number,
642 x_evaluation_sequence,
643 x_program_approver_ind,
644 x_mode
645 );
646 RETURN;
647 END IF;
648 CLOSE c1;
649
650 update_row (
651 x_rowid,
652 x_revgr_evaluator_id,
653 x_appl_revprof_revgr_id,
654 x_person_id,
655 x_person_number,
656 x_evaluation_sequence,
657 x_program_approver_ind,
658 x_mode
659 );
660
661 END add_row;
662
663
664 PROCEDURE delete_row (
665 x_rowid IN VARCHAR2
666 ) AS
667 /*
668 || Created By : [email protected]
669 || Created On : 09-NOV-2001
670 || Purpose : Handles the DELETE DML logic for the table.
671 || Known limitations, enhancements or remarks :
672 || Change History :
673 || Who When What
674 || (reverse chronological order - newest change first)
675 */
676 BEGIN
677
678 before_dml (
679 p_action => 'DELETE',
680 x_rowid => x_rowid
681 );
682
683 DELETE FROM igs_ad_rvgr_evaltr
684 WHERE rowid = x_rowid;
685
686 IF (SQL%NOTFOUND) THEN
687 RAISE NO_DATA_FOUND;
688 END IF;
689
690 END delete_row;
691
692
693 END igs_ad_rvgr_evaltr_pkg;
[Home] [Help]