[Home] [Help]
PACKAGE BODY: APPS.IGS_PR_COHINST_RANK_PKG
Source
1 PACKAGE BODY igs_pr_cohinst_rank_pkg AS
2 /* $Header: IGSQI43B.pls 120.0 2005/07/05 12:06:37 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pr_cohinst_rank%ROWTYPE;
6 new_references igs_pr_cohinst_rank%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_cohort_name IN VARCHAR2,
12 x_load_cal_type IN VARCHAR2,
13 x_load_ci_sequence_number IN NUMBER,
14 x_person_id IN NUMBER,
15 x_course_cd IN VARCHAR2,
16 x_as_of_rank_gpa IN NUMBER,
17 x_cohort_rank IN NUMBER,
18 x_cohort_override_rank IN NUMBER,
19 x_comments 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 ) AS
26 /*
27 || Created By : [email protected]
28 || Created On : 30-OCT-2002
29 || Purpose : Initialises the Old and New references for the columns of the table.
30 || Known limitations, enhancements or remarks :
31 || Change History :
32 || Who When What
33 || (reverse chronological order - newest change first)
34 */
35
36 CURSOR cur_old_ref_values IS
37 SELECT *
38 FROM igs_pr_cohinst_rank
39 WHERE rowid = x_rowid;
40
41 BEGIN
42
43 l_rowid := x_rowid;
44
45 -- Code for setting the Old and New Reference Values.
46 -- Populate Old Values.
47 OPEN cur_old_ref_values;
48 FETCH cur_old_ref_values INTO old_references;
49 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
50 CLOSE cur_old_ref_values;
51 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
52 igs_ge_msg_stack.add;
53 app_exception.raise_exception;
54 RETURN;
55 END IF;
56 CLOSE cur_old_ref_values;
57
58 -- Populate New Values.
59 new_references.cohort_name := x_cohort_name;
60 new_references.load_cal_type := x_load_cal_type;
61 new_references.load_ci_sequence_number := x_load_ci_sequence_number;
62 new_references.person_id := x_person_id;
63 new_references.course_cd := x_course_cd;
64 new_references.as_of_rank_gpa := x_as_of_rank_gpa;
65 new_references.cohort_rank := x_cohort_rank;
66 new_references.cohort_override_rank := x_cohort_override_rank;
67 new_references.comments := x_comments;
68
69 IF (p_action = 'UPDATE') THEN
70 new_references.creation_date := old_references.creation_date;
71 new_references.created_by := old_references.created_by;
72 ELSE
73 new_references.creation_date := x_creation_date;
74 new_references.created_by := x_created_by;
75 END IF;
76
77 new_references.last_update_date := x_last_update_date;
78 new_references.last_updated_by := x_last_updated_by;
79 new_references.last_update_login := x_last_update_login;
80
81 END set_column_values;
82
83 PROCEDURE check_parent_existance AS
84 /*
85 || Created By : [email protected]
86 || Created On : 30-OCT-2002
87 || Purpose : Checks for the existance of Parent records.
88 || Known limitations, enhancements or remarks :
89 || Change History :
90 || Who When What
91 || (reverse chronological order - newest change first)
92 */
93 BEGIN
94
95 IF (((old_references.cohort_name = new_references.cohort_name) AND
96 (old_references.load_cal_type = new_references.load_cal_type) AND
97 (old_references.load_ci_sequence_number = new_references.load_ci_sequence_number)) OR
98 ((new_references.cohort_name IS NULL) OR
99 (new_references.load_cal_type IS NULL) OR
100 (new_references.load_ci_sequence_number IS NULL))) THEN
101 NULL;
102 ELSIF NOT igs_pr_cohort_inst_pkg.get_pk_for_validation (
103 new_references.cohort_name,
104 new_references.load_cal_type,
105 new_references.load_ci_sequence_number
106 ) THEN
107 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
108 igs_ge_msg_stack.add;
109 app_exception.raise_exception;
110 END IF;
111
112 IF (((old_references.person_id = new_references.person_id) AND
113 (old_references.course_cd = new_references.course_cd)) OR
114 ((new_references.person_id IS NULL) OR
115 (new_references.course_cd IS NULL))) THEN
116 NULL;
117 ELSIF NOT igs_en_stdnt_ps_att_pkg.get_pk_for_validation (
118 new_references.person_id,
119 new_references.course_cd
120 ) THEN
121 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
122 igs_ge_msg_stack.add;
123 app_exception.raise_exception;
124 END IF;
125
126 END check_parent_existance;
127
128
129 FUNCTION get_pk_for_validation (
130 x_person_id IN NUMBER,
131 x_course_cd IN VARCHAR2,
132 x_cohort_name IN VARCHAR2,
133 x_load_cal_type IN VARCHAR2,
134 x_load_ci_sequence_number IN NUMBER
135 ) RETURN BOOLEAN AS
136 /*
137 || Created By : [email protected]
138 || Created On : 30-OCT-2002
139 || Purpose : Validates the Primary Key of the table.
140 || Known limitations, enhancements or remarks :
141 || Change History :
142 || Who When What
143 || (reverse chronological order - newest change first)
144 */
145 CURSOR cur_rowid IS
146 SELECT rowid
147 FROM igs_pr_cohinst_rank
148 WHERE person_id = x_person_id
149 AND course_cd = x_course_cd
150 AND cohort_name = x_cohort_name
151 AND load_cal_type = x_load_cal_type
152 AND load_ci_sequence_number = x_load_ci_sequence_number
153 FOR UPDATE NOWAIT;
154
155 lv_rowid cur_rowid%RowType;
156
157 BEGIN
158
159 OPEN cur_rowid;
160 FETCH cur_rowid INTO lv_rowid;
161 IF (cur_rowid%FOUND) THEN
162 CLOSE cur_rowid;
163 RETURN(TRUE);
164 ELSE
165 CLOSE cur_rowid;
166 RETURN(FALSE);
167 END IF;
168
169 END get_pk_for_validation;
170
171 PROCEDURE get_fk_igs_pr_cohort_inst (
172 x_cohort_name IN VARCHAR2,
173 x_load_cal_type IN VARCHAR2,
174 x_load_ci_sequence_number IN NUMBER
175 ) AS
176 /*
177 || Created By : [email protected]
178 || Created On : 30-OCT-2002
179 || Purpose : Validates the Foreign Keys for the table.
180 || Known limitations, enhancements or remarks :
181 || Change History :
182 || Who When What
183 || (reverse chronological order - newest change first)
184 */
185 CURSOR cur_rowid IS
186 SELECT rowid
187 FROM igs_pr_cohinst_rank
188 WHERE ((cohort_name = x_cohort_name) AND
189 (load_cal_type = x_load_cal_type) AND
190 (load_ci_sequence_number = x_load_ci_sequence_number));
191
192 lv_rowid cur_rowid%RowType;
193
194 BEGIN
195
196 OPEN cur_rowid;
197 FETCH cur_rowid INTO lv_rowid;
198 IF (cur_rowid%FOUND) THEN
199 CLOSE cur_rowid;
200 fnd_message.set_name ('IGS', 'IGS_PR_COHI_COHIR_FK');
201 igs_ge_msg_stack.add;
202 app_exception.raise_exception;
203 RETURN;
204 END IF;
205 CLOSE cur_rowid;
206
207 END get_fk_igs_pr_cohort_inst;
208
209
210 PROCEDURE get_fk_igs_en_stdnt_ps_att (
211 x_person_id IN NUMBER,
212 x_course_cd IN VARCHAR2
213 ) AS
214 /*
215 || Created By : [email protected]
216 || Created On : 30-OCT-2002
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_pr_cohinst_rank
226 WHERE ((course_cd = x_course_cd) AND
227 (person_id = x_person_id));
228
229 lv_rowid cur_rowid%RowType;
230
231 BEGIN
232
233 OPEN cur_rowid;
234 FETCH cur_rowid INTO lv_rowid;
235 IF (cur_rowid%FOUND) THEN
236 CLOSE cur_rowid;
237 fnd_message.set_name ('IGS', 'IGS_PR_COHIR_SPA_FK');
238 igs_ge_msg_stack.add;
239 app_exception.raise_exception;
240 RETURN;
241 END IF;
242 CLOSE cur_rowid;
243
244 END get_fk_igs_en_stdnt_ps_att;
245
246
247 PROCEDURE before_dml (
248 p_action IN VARCHAR2,
249 x_rowid IN VARCHAR2,
250 x_cohort_name IN VARCHAR2,
251 x_load_cal_type IN VARCHAR2,
252 x_load_ci_sequence_number IN NUMBER,
253 x_person_id IN NUMBER,
254 x_course_cd IN VARCHAR2,
255 x_as_of_rank_gpa IN NUMBER,
256 x_cohort_rank IN NUMBER,
257 x_cohort_override_rank IN NUMBER,
258 x_comments IN VARCHAR2,
259 x_creation_date IN DATE,
260 x_created_by IN NUMBER,
261 x_last_update_date IN DATE,
262 x_last_updated_by IN NUMBER,
263 x_last_update_login IN NUMBER
264 ) AS
265 /*
266 || Created By : [email protected]
267 || Created On : 30-OCT-2002
268 || Purpose : Initialises the columns, Checks Constraints, Calls the
269 || Trigger Handlers for the table, before any DML operation.
270 || Known limitations, enhancements or remarks :
271 || Change History :
272 || Who When What
273 || (reverse chronological order - newest change first)
274 */
275 BEGIN
276
277 set_column_values (
278 p_action,
279 x_rowid,
280 x_cohort_name,
281 x_load_cal_type,
282 x_load_ci_sequence_number,
283 x_person_id,
284 x_course_cd,
285 x_as_of_rank_gpa,
286 x_cohort_rank,
287 x_cohort_override_rank,
288 x_comments,
289 x_creation_date,
290 x_created_by,
291 x_last_update_date,
292 x_last_updated_by,
293 x_last_update_login
294 );
295
296 IF (p_action = 'INSERT') THEN
297 -- Call all the procedures related to Before Insert.
298 IF ( get_pk_for_validation(
299 new_references.person_id,
300 new_references.course_cd,
301 new_references.cohort_name,
302 new_references.load_cal_type,
303 new_references.load_ci_sequence_number
304 )
305 ) THEN
306 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
307 igs_ge_msg_stack.add;
308 app_exception.raise_exception;
309 END IF;
310 check_parent_existance;
311 ELSIF (p_action = 'UPDATE') THEN
312 -- Call all the procedures related to Before Update.
313 check_parent_existance;
314 ELSIF (p_action = 'VALIDATE_INSERT') THEN
315 -- Call all the procedures related to Before Insert.
316 IF ( get_pk_for_validation (
317 new_references.person_id,
318 new_references.course_cd,
319 new_references.cohort_name,
320 new_references.load_cal_type,
321 new_references.load_ci_sequence_number
322 )
323 ) THEN
324 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
325 igs_ge_msg_stack.add;
326 app_exception.raise_exception;
327 END IF;
328 END IF;
329
330 END before_dml;
331
332
333 PROCEDURE insert_row (
334 x_rowid IN OUT NOCOPY VARCHAR2,
335 x_cohort_name IN VARCHAR2,
336 x_load_cal_type IN VARCHAR2,
337 x_load_ci_sequence_number IN NUMBER,
338 x_person_id IN NUMBER,
339 x_course_cd IN VARCHAR2,
340 x_as_of_rank_gpa IN NUMBER,
341 x_cohort_rank IN NUMBER,
342 x_cohort_override_rank IN NUMBER,
343 x_comments IN VARCHAR2,
344 x_mode IN VARCHAR2
345 ) AS
346 /*
347 || Created By : [email protected]
348 || Created On : 30-OCT-2002
349 || Purpose : Handles the INSERT DML logic for the table.
350 || Known limitations, enhancements or remarks :
351 || Change History :
352 || Who When What
353 || (reverse chronological order - newest change first)
354 */
355
356 x_last_update_date DATE;
357 x_last_updated_by NUMBER;
358 x_last_update_login NUMBER;
359 x_request_id NUMBER;
360 x_program_id NUMBER;
361 x_program_application_id NUMBER;
362 x_program_update_date DATE;
363
364 BEGIN
365
366 x_last_update_date := SYSDATE;
367 IF (x_mode = 'I') THEN
368 x_last_updated_by := 1;
369 x_last_update_login := 0;
370 ELSIF (x_mode = 'R') THEN
371 x_last_updated_by := fnd_global.user_id;
372 IF (x_last_updated_by IS NULL) THEN
373 x_last_updated_by := -1;
374 END IF;
375 x_last_update_login := fnd_global.login_id;
376 IF (x_last_update_login IS NULL) THEN
377 x_last_update_login := -1;
378 END IF;
379 x_request_id := fnd_global.conc_request_id;
380 x_program_id := fnd_global.conc_program_id;
381 x_program_application_id := fnd_global.prog_appl_id;
382
383 IF (x_request_id = -1) THEN
384 x_request_id := NULL;
385 x_program_id := NULL;
386 x_program_application_id := NULL;
387 x_program_update_date := NULL;
388 ELSE
389 x_program_update_date := SYSDATE;
390 END IF;
391 ELSE
392 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
393 igs_ge_msg_stack.add;
394 app_exception.raise_exception;
395 END IF;
396
397 before_dml(
398 p_action => 'INSERT',
399 x_rowid => x_rowid,
400 x_cohort_name => x_cohort_name,
401 x_load_cal_type => x_load_cal_type,
402 x_load_ci_sequence_number => x_load_ci_sequence_number,
403 x_person_id => x_person_id,
404 x_course_cd => x_course_cd,
405 x_as_of_rank_gpa => x_as_of_rank_gpa,
406 x_cohort_rank => x_cohort_rank,
407 x_cohort_override_rank => x_cohort_override_rank,
408 x_comments => x_comments,
409 x_creation_date => x_last_update_date,
410 x_created_by => x_last_updated_by,
411 x_last_update_date => x_last_update_date,
412 x_last_updated_by => x_last_updated_by,
413 x_last_update_login => x_last_update_login
414 );
415
416 INSERT INTO igs_pr_cohinst_rank (
417 cohort_name,
418 load_cal_type,
419 load_ci_sequence_number,
420 person_id,
421 course_cd,
422 as_of_rank_gpa,
423 cohort_rank,
424 cohort_override_rank,
425 comments,
426 creation_date,
427 created_by,
428 last_update_date,
429 last_updated_by,
430 last_update_login,
431 request_id,
432 program_id,
433 program_application_id,
434 program_update_date
435 ) VALUES (
436 new_references.cohort_name,
437 new_references.load_cal_type,
438 new_references.load_ci_sequence_number,
439 new_references.person_id,
440 new_references.course_cd,
441 new_references.as_of_rank_gpa,
442 new_references.cohort_rank,
443 new_references.cohort_override_rank,
444 new_references.comments,
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 x_request_id,
451 x_program_id,
452 x_program_application_id,
453 x_program_update_date
454 ) RETURNING ROWID INTO x_rowid;
455
456 END insert_row;
457
458
459 PROCEDURE lock_row (
460 x_rowid IN VARCHAR2,
461 x_cohort_name IN VARCHAR2,
462 x_load_cal_type IN VARCHAR2,
463 x_load_ci_sequence_number IN NUMBER,
464 x_person_id IN NUMBER,
465 x_course_cd IN VARCHAR2,
466 x_as_of_rank_gpa IN NUMBER,
467 x_cohort_rank IN NUMBER,
468 x_cohort_override_rank IN NUMBER,
469 x_comments IN VARCHAR2
470 ) AS
471 /*
472 || Created By : [email protected]
473 || Created On : 30-OCT-2002
474 || Purpose : Handles the LOCK mechanism for the table.
475 || Known limitations, enhancements or remarks :
476 || Change History :
477 || Who When What
478 || (reverse chronological order - newest change first)
479 */
480 CURSOR c1 IS
481 SELECT
482 as_of_rank_gpa,
483 cohort_rank,
484 cohort_override_rank,
485 comments
486 FROM igs_pr_cohinst_rank
487 WHERE rowid = x_rowid
488 FOR UPDATE NOWAIT;
489
490 tlinfo c1%ROWTYPE;
491
492 BEGIN
493
494 OPEN c1;
495 FETCH c1 INTO tlinfo;
496 IF (c1%notfound) THEN
497 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
498 igs_ge_msg_stack.add;
499 CLOSE c1;
500 app_exception.raise_exception;
501 RETURN;
502 END IF;
503 CLOSE c1;
504
505 IF (
506 (tlinfo.as_of_rank_gpa = x_as_of_rank_gpa)
507 AND (tlinfo.cohort_rank = x_cohort_rank)
508 AND ((tlinfo.cohort_override_rank = x_cohort_override_rank) OR ((tlinfo.cohort_override_rank IS NULL) AND (X_cohort_override_rank IS NULL)))
509 AND ((tlinfo.comments = x_comments) OR ((tlinfo.comments IS NULL) AND (X_comments IS NULL)))
510 ) THEN
511 NULL;
512 ELSE
513 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
514 igs_ge_msg_stack.add;
515 app_exception.raise_exception;
516 END IF;
517
518 RETURN;
519
520 END lock_row;
521
522
523 PROCEDURE update_row (
524 x_rowid IN VARCHAR2,
525 x_cohort_name IN VARCHAR2,
526 x_load_cal_type IN VARCHAR2,
527 x_load_ci_sequence_number IN NUMBER,
528 x_person_id IN NUMBER,
529 x_course_cd IN VARCHAR2,
530 x_as_of_rank_gpa IN NUMBER,
531 x_cohort_rank IN NUMBER,
532 x_cohort_override_rank IN NUMBER,
533 x_comments IN VARCHAR2,
534 x_mode IN VARCHAR2
535 ) AS
536 /*
537 || Created By : [email protected]
538 || Created On : 30-OCT-2002
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 x_request_id NUMBER;
549 x_program_id NUMBER;
550 x_program_application_id NUMBER;
551 x_program_update_date DATE;
552
553 BEGIN
554
555 x_last_update_date := SYSDATE;
556 IF (X_MODE = 'I') THEN
557 x_last_updated_by := 1;
558 x_last_update_login := 0;
559 ELSIF (x_mode = 'R') THEN
560 x_last_updated_by := fnd_global.user_id;
561 IF x_last_updated_by IS NULL THEN
562 x_last_updated_by := -1;
563 END IF;
564 x_last_update_login := fnd_global.login_id;
565 IF (x_last_update_login IS NULL) THEN
566 x_last_update_login := -1;
567 END IF;
568 ELSE
569 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
570 igs_ge_msg_stack.add;
571 app_exception.raise_exception;
572 END IF;
573
574 before_dml(
575 p_action => 'UPDATE',
576 x_rowid => x_rowid,
577 x_cohort_name => x_cohort_name,
578 x_load_cal_type => x_load_cal_type,
579 x_load_ci_sequence_number => x_load_ci_sequence_number,
580 x_person_id => x_person_id,
581 x_course_cd => x_course_cd,
582 x_as_of_rank_gpa => x_as_of_rank_gpa,
583 x_cohort_rank => x_cohort_rank,
584 x_cohort_override_rank => x_cohort_override_rank,
585 x_comments => x_comments,
586 x_creation_date => x_last_update_date,
587 x_created_by => x_last_updated_by,
588 x_last_update_date => x_last_update_date,
589 x_last_updated_by => x_last_updated_by,
590 x_last_update_login => x_last_update_login
591 );
592
593 IF (x_mode = 'R') THEN
594 x_request_id := fnd_global.conc_request_id;
595 x_program_id := fnd_global.conc_program_id;
596 x_program_application_id := fnd_global.prog_appl_id;
597 IF (x_request_id = -1) THEN
598 x_request_id := old_references.request_id;
599 x_program_id := old_references.program_id;
600 x_program_application_id := old_references.program_application_id;
601 x_program_update_date := old_references.program_update_date;
602 ELSE
603 x_program_update_date := SYSDATE;
604 END IF;
605 END IF;
606
607 UPDATE igs_pr_cohinst_rank
608 SET
609 as_of_rank_gpa = new_references.as_of_rank_gpa,
610 cohort_rank = new_references.cohort_rank,
611 cohort_override_rank = new_references.cohort_override_rank,
612 comments = new_references.comments,
613 last_update_date = x_last_update_date,
614 last_updated_by = x_last_updated_by,
615 last_update_login = x_last_update_login ,
616 request_id = x_request_id,
617 program_id = x_program_id,
618 program_application_id = x_program_application_id,
619 program_update_date = x_program_update_date
620 WHERE rowid = x_rowid;
621
622 IF (SQL%NOTFOUND) THEN
623 RAISE NO_DATA_FOUND;
624 END IF;
625
626 -- raise business event whenever there is a change in override rank or override
627 -- rank is given
628 DECLARE
629 -- cursor to fetch person details
630 CURSOR c_pers (cp_person_id igs_pe_person.person_id%TYPE) IS
631 SELECT person_number, full_name
632 FROM igs_pe_person_base_v
633 WHERE person_id = cp_person_id;
634 rec_pers c_pers%ROWTYPE;
635 rec_admin c_pers%ROWTYPE;
636
637 CURSOR c_fnd IS
638 SELECT distinct person_party_id
639 FROm FND_USER
640 WHERE user_id = FND_GLOBAL.USER_ID;
641 l_person_party_id fnd_user.person_party_id%TYPE;
642
643 CURSOR c_meaning IS
644 SELECT meaning
645 FROM igs_lookups_view
646 WHERE lookup_code = 'ADMIN'
647 AND lookup_type = 'IGS_PT_SS_ROLE_TYPES';
648 l_meaning c_meaning%ROWTYPE;
649
650 BEGIN
651 IF new_references.cohort_override_rank IS NOT NULL AND
652 (new_references.cohort_override_rank <> old_references.cohort_override_rank OR
653 (new_references.cohort_override_rank IS NOT NULL AND old_references.cohort_override_rank IS NULL)) THEN
654 OPEN c_fnd;
655 FETCH c_fnd INTO l_person_party_id;
656 CLOSE c_fnd;
657 OPEN c_pers (new_references.person_id);
658 FETCH c_pers INTO rec_pers;
659 CLOSE c_pers;
660 OPEN c_pers (l_person_party_id);
661 FETCH c_pers INTO rec_admin;
662 CLOSE c_pers;
663 OPEN c_meaning;
664 FETCH c_meaning INTO l_meaning;
665 CLOSE c_meaning;
666
667 IGS_PR_CLASS_RANK.RAISE_CLSRANK_BE_CR002 (
668 P_PERSON_ID => new_references.person_id,
669 P_PERSON_NUMBER => rec_pers.person_number,
670 P_PERSON_NAME => rec_pers.full_name,
671 P_CURRENT_RANK => new_references.cohort_rank,
672 P_OVERRIDE_RANK => new_references.cohort_override_rank,
673 P_OVRBY_PERSON_ID => FND_GLOBAL.USER_ID,
674 P_OVRBY_PERSON_NUMBER => rec_admin.person_number,
675 P_OVRBY_PERSON_NAME => NVL(rec_admin.full_name,l_meaning.meaning)
676 );
677 END IF;
678 END;
679
680
681 END update_row;
682
683
684 PROCEDURE add_row (
685 x_rowid IN OUT NOCOPY VARCHAR2,
686 x_cohort_name IN VARCHAR2,
687 x_load_cal_type IN VARCHAR2,
688 x_load_ci_sequence_number IN NUMBER,
689 x_person_id IN NUMBER,
690 x_course_cd IN VARCHAR2,
691 x_as_of_rank_gpa IN NUMBER,
692 x_cohort_rank IN NUMBER,
693 x_cohort_override_rank IN NUMBER,
694 x_comments IN VARCHAR2,
695 x_mode IN VARCHAR2
696 ) AS
697 /*
698 || Created By : [email protected]
699 || Created On : 30-OCT-2002
700 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
701 || Known limitations, enhancements or remarks :
702 || Change History :
703 || Who When What
704 || (reverse chronological order - newest change first)
705 */
706 CURSOR c1 IS
707 SELECT rowid
708 FROM igs_pr_cohinst_rank
709 WHERE person_id = x_person_id
710 AND course_cd = x_course_cd
711 AND cohort_name = x_cohort_name
712 AND load_cal_type = x_load_cal_type
713 AND load_ci_sequence_number = x_load_ci_sequence_number;
714
715 BEGIN
716
717 OPEN c1;
718 FETCH c1 INTO x_rowid;
719 IF (c1%NOTFOUND) THEN
720 CLOSE c1;
721
722 insert_row (
723 x_rowid,
724 x_cohort_name,
725 x_load_cal_type,
726 x_load_ci_sequence_number,
727 x_person_id,
728 x_course_cd,
729 x_as_of_rank_gpa,
730 x_cohort_rank,
731 x_cohort_override_rank,
732 x_comments,
733 x_mode
734 );
735 RETURN;
736 END IF;
737 CLOSE c1;
738
739 update_row (
740 x_rowid,
741 x_cohort_name,
742 x_load_cal_type,
743 x_load_ci_sequence_number,
744 x_person_id,
745 x_course_cd,
746 x_as_of_rank_gpa,
747 x_cohort_rank,
748 x_cohort_override_rank,
749 x_comments,
750 x_mode
751 );
752
753 END add_row;
754
755
756 PROCEDURE delete_row (
757 x_rowid IN VARCHAR2
758 ) AS
759 /*
760 || Created By : [email protected]
761 || Created On : 30-OCT-2002
762 || Purpose : Handles the DELETE DML logic for the table.
763 || Known limitations, enhancements or remarks :
764 || Change History :
765 || Who When What
766 || (reverse chronological order - newest change first)
767 */
768 BEGIN
769
770 before_dml (
771 p_action => 'DELETE',
772 x_rowid => x_rowid
773 );
774
775 DELETE FROM igs_pr_cohinst_rank
776 WHERE rowid = x_rowid;
777
778 IF (SQL%NOTFOUND) THEN
779 RAISE NO_DATA_FOUND;
780 END IF;
781
782 END delete_row;
783
784
785 END igs_pr_cohinst_rank_pkg;