1 PACKAGE BODY igf_sp_stdnt_rel_pkg AS
2 /* $Header: IGFPI04B.pls 115.2 2003/03/19 08:50:04 smadathi noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_sp_stdnt_rel_all%ROWTYPE;
6 new_references igf_sp_stdnt_rel_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_spnsr_stdnt_id IN NUMBER DEFAULT NULL,
12 x_fund_id IN NUMBER DEFAULT NULL,
13 x_base_id IN NUMBER DEFAULT NULL,
14 x_person_id IN NUMBER DEFAULT NULL,
15 x_ld_cal_type IN VARCHAR2 DEFAULT NULL,
16 x_ld_sequence_number IN NUMBER DEFAULT NULL,
17 x_tot_spnsr_amount IN NUMBER DEFAULT NULL,
18 x_min_credit_points IN NUMBER DEFAULT NULL,
19 x_min_attendance_type IN VARCHAR2 DEFAULT NULL,
20 x_creation_date IN DATE DEFAULT NULL,
21 x_created_by IN NUMBER DEFAULT NULL,
22 x_last_update_date IN DATE DEFAULT NULL,
23 x_last_updated_by IN NUMBER DEFAULT NULL,
24 x_last_update_login IN NUMBER DEFAULT NULL
25 ) AS
26 /*
27 || Created By :
28 || Created On : 28-DEC-2001
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 igf_sp_stdnt_rel_all
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.spnsr_stdnt_id := x_spnsr_stdnt_id;
60 new_references.fund_id := x_fund_id;
61 new_references.base_id := x_base_id;
62 new_references.person_id := x_person_id;
63 new_references.ld_cal_type := x_ld_cal_type;
64 new_references.ld_sequence_number := x_ld_sequence_number;
65 new_references.tot_spnsr_amount := x_tot_spnsr_amount;
66 new_references.min_credit_points := x_min_credit_points;
67 new_references.min_attendance_type := x_min_attendance_type;
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
84 PROCEDURE check_uniqueness AS
85 /*
86 || Created By :
87 || Created On : 28-DEC-2001
88 || Purpose : Handles the Unique Constraint logic defined for the columns.
89 || Known limitations, enhancements or remarks :
90 || Change History :
91 || Who When What
92 || (reverse chronological order - newest change first)
93 */
94 BEGIN
95
96 IF ( get_uk_for_validation (
97 new_references.fund_id,
98 new_references.base_id,
99 new_references.ld_cal_type,
100 new_references.ld_sequence_number
101 )
102 ) THEN
103 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
104 igs_ge_msg_stack.add;
105 app_exception.raise_exception;
106 END IF;
107
108 END check_uniqueness;
109
110
111 PROCEDURE check_parent_existance AS
112 /*
113 || Created By :
114 || Created On : 28-DEC-2001
115 || Purpose : Checks for the existance of Parent records.
116 || Known limitations, enhancements or remarks :
117 || Change History :
118 || Who When What
119 || (reverse chronological order - newest change first)
120 */
121 BEGIN
122
123
124 IF (((old_references.ld_cal_type = new_references.ld_cal_type) AND
125 (old_references.ld_sequence_number = new_references.ld_sequence_number)) OR
126 ((new_references.ld_cal_type IS NULL) OR
127 (new_references.ld_sequence_number IS NULL))) THEN
128 NULL;
129 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
130 new_references.ld_cal_type,
131 new_references.ld_sequence_number
132 ) THEN
133 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
134 igs_ge_msg_stack.add;
135 app_exception.raise_exception;
136 END IF;
137
138 IF (((old_references.base_id = new_references.base_id)) OR
139 ((new_references.base_id IS NULL))) THEN
140 NULL;
141 ELSIF NOT igf_ap_fa_base_rec_pkg.get_pk_for_validation (
142 new_references.base_id
143 ) THEN
144 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
145 igs_ge_msg_stack.add;
146 app_exception.raise_exception;
147 END IF;
148
149 IF (((old_references.fund_id = new_references.fund_id)) OR
150 ((new_references.fund_id IS NULL))) THEN
151 NULL;
152 ELSIF NOT igf_aw_fund_mast_pkg.get_pk_for_validation (
153 new_references.fund_id
154 ) THEN
155 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
156 igs_ge_msg_stack.add;
157 app_exception.raise_exception;
158 END IF;
159
160 END check_parent_existance;
161
162
163 PROCEDURE check_child_existance IS
164 /*
165 || Created By :
166 || Created On : 28-DEC-2001
167 || Purpose : Checks for the existance of Child records.
168 || Known limitations, enhancements or remarks :
169 || Change History :
170 || Who When What
171 || (reverse chronological order - newest change first)
172 */
173 BEGIN
174
175 igf_sp_std_fc_pkg.get_fk_igf_sp_stdnt_rel (
176 old_references.spnsr_stdnt_id
177 );
178
179 END check_child_existance;
180
181
182 FUNCTION get_pk_for_validation (
183 x_spnsr_stdnt_id IN NUMBER
184 ) RETURN BOOLEAN AS
185 /*
186 || Created By :
187 || Created On : 28-DEC-2001
188 || Purpose : Validates the Primary Key of the table.
189 || Known limitations, enhancements or remarks :
190 || Change History :
191 || Who When What
192 || (reverse chronological order - newest change first)
193 */
194 CURSOR cur_rowid IS
195 SELECT rowid
196 FROM igf_sp_stdnt_rel_all
197 WHERE spnsr_stdnt_id = x_spnsr_stdnt_id
198 FOR UPDATE NOWAIT;
199
200 lv_rowid cur_rowid%RowType;
201
202 BEGIN
203
204 OPEN cur_rowid;
205 FETCH cur_rowid INTO lv_rowid;
206 IF (cur_rowid%FOUND) THEN
207 CLOSE cur_rowid;
208 RETURN(TRUE);
209 ELSE
210 CLOSE cur_rowid;
211 RETURN(FALSE);
212 END IF;
213
214 END get_pk_for_validation;
215
216
217 FUNCTION get_uk_for_validation (
218 x_fund_id IN NUMBER,
219 x_base_id IN NUMBER,
220 x_ld_cal_type IN VARCHAR2,
221 x_ld_sequence_number IN NUMBER
222 ) RETURN BOOLEAN AS
223 /*
224 || Created By :
225 || Created On : 28-DEC-2001
226 || Purpose : Validates the Unique Keys of the table.
227 || Known limitations, enhancements or remarks :
228 || Change History :
229 || Who When What
230 || (reverse chronological order - newest change first)
231 */
232 CURSOR cur_rowid IS
233 SELECT rowid
234 FROM igf_sp_stdnt_rel_all
235 WHERE fund_id = x_fund_id
236 AND base_id = x_base_id
237 AND ld_cal_type = x_ld_cal_type
238 AND ld_sequence_number = x_ld_sequence_number
239 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
240
241 lv_rowid cur_rowid%RowType;
242
243 BEGIN
244
245 OPEN cur_rowid;
246 FETCH cur_rowid INTO lv_rowid;
247 IF (cur_rowid%FOUND) THEN
248 CLOSE cur_rowid;
249 RETURN (true);
250 ELSE
251 CLOSE cur_rowid;
252 RETURN(FALSE);
253 END IF;
254
255 END get_uk_for_validation ;
256
257
258 PROCEDURE get_fk_hz_parties (
259 x_party_id IN NUMBER
260 ) AS
261 /*
262 || Created By :
263 || Created On : 28-DEC-2001
264 || Purpose : Validates the Foreign Keys for the table.
265 || Known limitations, enhancements or remarks :
266 || Change History :
267 || Who When What
268 || (reverse chronological order - newest change first)
269 */
270 CURSOR cur_rowid IS
271 SELECT rowid
272 FROM igf_sp_stdnt_rel_all
273 WHERE ((person_id = x_party_id));
274
275 lv_rowid cur_rowid%RowType;
276
277 BEGIN
278
279 OPEN cur_rowid;
280 FETCH cur_rowid INTO lv_rowid;
281 IF (cur_rowid%FOUND) THEN
282 CLOSE cur_rowid;
283 fnd_message.set_name ('IGF', 'IGF_SP_SPSTD_HZ_FK');
284 igs_ge_msg_stack.add;
285 app_exception.raise_exception;
286 RETURN;
287 END IF;
288 CLOSE cur_rowid;
289
290 END get_fk_hz_parties;
291
292
293 PROCEDURE get_fk_igs_ca_inst (
294 x_cal_type IN VARCHAR2,
295 x_sequence_number IN NUMBER
296 ) AS
297 /*
298 || Created By :
299 || Created On : 28-DEC-2001
300 || Purpose : Validates the Foreign Keys for the table.
301 || Known limitations, enhancements or remarks :
302 || Change History :
303 || Who When What
304 || (reverse chronological order - newest change first)
305 */
306 CURSOR cur_rowid IS
307 SELECT rowid
308 FROM igf_sp_stdnt_rel_all
309 WHERE ((ld_cal_type = x_cal_type) AND
310 (ld_sequence_number = x_sequence_number));
311
312 lv_rowid cur_rowid%RowType;
313
314 BEGIN
315
316 OPEN cur_rowid;
317 FETCH cur_rowid INTO lv_rowid;
318 IF (cur_rowid%FOUND) THEN
319 CLOSE cur_rowid;
320 fnd_message.set_name ('IGF', 'IGF_SP_SPSTD_CA_FK');
321 igs_ge_msg_stack.add;
322 app_exception.raise_exception;
323 RETURN;
324 END IF;
325 CLOSE cur_rowid;
326
327 END get_fk_igs_ca_inst;
328
329
330 PROCEDURE get_fk_igf_ap_fa_base_rec (
331 x_base_id IN NUMBER
332 ) AS
333 /*
334 || Created By :
335 || Created On : 28-DEC-2001
336 || Purpose : Validates the Foreign Keys for the table.
337 || Known limitations, enhancements or remarks :
338 || Change History :
339 || Who When What
340 || (reverse chronological order - newest change first)
341 */
342 CURSOR cur_rowid IS
343 SELECT rowid
344 FROM igf_sp_stdnt_rel_all
345 WHERE ((base_id = x_base_id));
346
347 lv_rowid cur_rowid%RowType;
348
349 BEGIN
350
351 OPEN cur_rowid;
352 FETCH cur_rowid INTO lv_rowid;
353 IF (cur_rowid%FOUND) THEN
354 CLOSE cur_rowid;
355 fnd_message.set_name ('IGF', 'IGF_SP_SPSTD_FA_DETAIL_FK');
356 igs_ge_msg_stack.add;
357 app_exception.raise_exception;
358 RETURN;
359 END IF;
360 CLOSE cur_rowid;
361
362 END get_fk_igf_ap_fa_base_rec;
363
364
365 PROCEDURE get_fk_igf_aw_fund_mast (
366 x_fund_id IN NUMBER
367 ) AS
368 /*
369 || Created By :
370 || Created On : 28-DEC-2001
371 || Purpose : Validates the Foreign Keys for the table.
372 || Known limitations, enhancements or remarks :
373 || Change History :
374 || Who When What
375 || (reverse chronological order - newest change first)
376 */
377 CURSOR cur_rowid IS
378 SELECT rowid
379 FROM igf_sp_stdnt_rel_all
380 WHERE ((fund_id = x_fund_id));
381
382 lv_rowid cur_rowid%RowType;
383
384 BEGIN
385
386 OPEN cur_rowid;
387 FETCH cur_rowid INTO lv_rowid;
388 IF (cur_rowid%FOUND) THEN
389 CLOSE cur_rowid;
390 fnd_message.set_name ('IGF', 'IGF_SP_SPSTD_FMAST_FK');
391 igs_ge_msg_stack.add;
392 app_exception.raise_exception;
393 RETURN;
394 END IF;
395 CLOSE cur_rowid;
396
397 END get_fk_igf_aw_fund_mast;
398
399
400 PROCEDURE before_dml (
401 p_action IN VARCHAR2,
402 x_rowid IN VARCHAR2 DEFAULT NULL,
403 x_spnsr_stdnt_id IN NUMBER DEFAULT NULL,
404 x_fund_id IN NUMBER DEFAULT NULL,
405 x_base_id IN NUMBER DEFAULT NULL,
406 x_person_id IN NUMBER DEFAULT NULL,
407 x_ld_cal_type IN VARCHAR2 DEFAULT NULL,
408 x_ld_sequence_number IN NUMBER DEFAULT NULL,
409 x_tot_spnsr_amount IN NUMBER DEFAULT NULL,
410 x_min_credit_points IN NUMBER DEFAULT NULL,
411 x_min_attendance_type IN VARCHAR2 DEFAULT NULL,
412 x_creation_date IN DATE DEFAULT NULL,
413 x_created_by IN NUMBER DEFAULT NULL,
414 x_last_update_date IN DATE DEFAULT NULL,
415 x_last_updated_by IN NUMBER DEFAULT NULL,
416 x_last_update_login IN NUMBER DEFAULT NULL
417 ) AS
418 /*
419 || Created By :
420 || Created On : 28-DEC-2001
421 || Purpose : Initialises the columns, Checks Constraints, Calls the
422 || Trigger Handlers for the table, before any DML operation.
423 || Known limitations, enhancements or remarks :
424 || Change History :
425 || Who When What
426 || (reverse chronological order - newest change first)
427 || smadathi 18-FEB-2003 Bug 2473845. Added logic to re initialize l_rowid to null.
428 */
429 BEGIN
430
431 set_column_values (
432 p_action,
433 x_rowid,
434 x_spnsr_stdnt_id,
435 x_fund_id,
436 x_base_id,
437 x_person_id,
438 x_ld_cal_type,
439 x_ld_sequence_number,
440 x_tot_spnsr_amount,
441 x_min_credit_points,
442 x_min_attendance_type,
443 x_creation_date,
444 x_created_by,
445 x_last_update_date,
446 x_last_updated_by,
447 x_last_update_login
448 );
449
450 IF (p_action = 'INSERT') THEN
451 -- Call all the procedures related to Before Insert.
452 IF ( get_pk_for_validation(
453 new_references.spnsr_stdnt_id
454 )
455 ) THEN
456 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
457 igs_ge_msg_stack.add;
458 app_exception.raise_exception;
459 END IF;
460 check_uniqueness;
461 check_parent_existance;
462 ELSIF (p_action = 'UPDATE') THEN
463 -- Call all the procedures related to Before Update.
464 check_uniqueness;
465 check_parent_existance;
466 ELSIF (p_action = 'DELETE') THEN
467 -- Call all the procedures related to Before Delete.
468 check_child_existance;
469 ELSIF (p_action = 'VALIDATE_INSERT') THEN
470 -- Call all the procedures related to Before Insert.
471 IF ( get_pk_for_validation (
472 new_references.spnsr_stdnt_id
473 )
474 ) THEN
475 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
476 igs_ge_msg_stack.add;
477 app_exception.raise_exception;
478 END IF;
479 check_uniqueness;
480 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
481 check_uniqueness;
482 ELSIF (p_action = 'VALIDATE_DELETE') THEN
483 check_child_existance;
484 END IF;
485 l_rowid := NULL;
486 END before_dml;
487
488
489 PROCEDURE insert_row (
490 x_rowid IN OUT NOCOPY VARCHAR2,
491 x_spnsr_stdnt_id IN OUT NOCOPY NUMBER,
492 x_fund_id IN NUMBER,
493 x_base_id IN NUMBER,
494 x_person_id IN NUMBER,
495 x_ld_cal_type IN VARCHAR2,
496 x_ld_sequence_number IN NUMBER,
497 x_tot_spnsr_amount IN NUMBER,
498 x_min_credit_points IN NUMBER,
499 x_min_attendance_type IN VARCHAR2,
500 x_mode IN VARCHAR2 DEFAULT 'R'
501 ) AS
502 /*
503 || Created By :
504 || Created On : 28-DEC-2001
505 || Purpose : Handles the INSERT DML logic for the table.
506 || Known limitations, enhancements or remarks :
507 || Change History :
508 || Who When What
509 || (reverse chronological order - newest change first)
510 */
511 CURSOR c IS
512 SELECT rowid
513 FROM igf_sp_stdnt_rel_all
514 WHERE spnsr_stdnt_id = x_spnsr_stdnt_id;
515
516 x_last_update_date DATE;
517 x_last_updated_by NUMBER;
518 x_last_update_login NUMBER;
519
520 BEGIN
521
522 x_last_update_date := SYSDATE;
523 IF (x_mode = 'I') THEN
524 x_last_updated_by := 1;
525 x_last_update_login := 0;
526 ELSIF (x_mode = 'R') THEN
527 x_last_updated_by := fnd_global.user_id;
528 IF (x_last_updated_by IS NULL) THEN
529 x_last_updated_by := -1;
530 END IF;
531 x_last_update_login := fnd_global.login_id;
532 IF (x_last_update_login IS NULL) THEN
533 x_last_update_login := -1;
534 END IF;
535 ELSE
536 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
537 igs_ge_msg_stack.add;
538 app_exception.raise_exception;
539 END IF;
540
541 SELECT igf_sp_stdnt_rel_s.NEXTVAL
542 INTO x_spnsr_stdnt_id
543 FROM dual;
544
545 new_references.org_id := igs_ge_gen_003.get_org_id;
546
547 before_dml(
548 p_action => 'INSERT',
549 x_rowid => x_rowid,
550 x_spnsr_stdnt_id => x_spnsr_stdnt_id,
551 x_fund_id => x_fund_id,
552 x_base_id => x_base_id,
553 x_person_id => x_person_id,
554 x_ld_cal_type => x_ld_cal_type,
555 x_ld_sequence_number => x_ld_sequence_number,
556 x_tot_spnsr_amount => x_tot_spnsr_amount,
557 x_min_credit_points => x_min_credit_points,
558 x_min_attendance_type => x_min_attendance_type,
559 x_creation_date => x_last_update_date,
560 x_created_by => x_last_updated_by,
561 x_last_update_date => x_last_update_date,
562 x_last_updated_by => x_last_updated_by,
563 x_last_update_login => x_last_update_login
564 );
565
566 INSERT INTO igf_sp_stdnt_rel_all (
567 spnsr_stdnt_id,
568 fund_id,
569 base_id,
570 person_id,
571 ld_cal_type,
572 ld_sequence_number,
573 tot_spnsr_amount,
574 min_credit_points,
575 min_attendance_type,
576 org_id,
577 creation_date,
578 created_by,
579 last_update_date,
580 last_updated_by,
581 last_update_login
582 ) VALUES (
583 new_references.spnsr_stdnt_id,
584 new_references.fund_id,
585 new_references.base_id,
586 new_references.person_id,
587 new_references.ld_cal_type,
588 new_references.ld_sequence_number,
589 new_references.tot_spnsr_amount,
590 new_references.min_credit_points,
591 new_references.min_attendance_type,
592 new_references.org_id,
593 x_last_update_date,
594 x_last_updated_by,
595 x_last_update_date,
596 x_last_updated_by,
597 x_last_update_login
598 );
599
600 OPEN c;
601 FETCH c INTO x_rowid;
602 IF (c%NOTFOUND) THEN
603 CLOSE c;
604 RAISE NO_DATA_FOUND;
605 END IF;
606 CLOSE c;
607
608 END insert_row;
609
610
611 PROCEDURE lock_row (
612 x_rowid IN VARCHAR2,
613 x_spnsr_stdnt_id IN NUMBER,
614 x_fund_id IN NUMBER,
615 x_base_id IN NUMBER,
616 x_person_id IN NUMBER,
617 x_ld_cal_type IN VARCHAR2,
618 x_ld_sequence_number IN NUMBER,
619 x_tot_spnsr_amount IN NUMBER,
620 x_min_credit_points IN NUMBER,
621 x_min_attendance_type IN VARCHAR2
622 ) AS
623 /*
624 || Created By :
625 || Created On : 28-DEC-2001
626 || Purpose : Handles the LOCK mechanism for the table.
627 || Known limitations, enhancements or remarks :
628 || Change History :
629 || Who When What
630 || (reverse chronological order - newest change first)
631 */
632 CURSOR c1 IS
633 SELECT
634 fund_id,
635 base_id,
636 person_id,
637 ld_cal_type,
638 ld_sequence_number,
639 tot_spnsr_amount,
640 min_credit_points,
641 min_attendance_type
642 FROM igf_sp_stdnt_rel_all
643 WHERE rowid = x_rowid
644 FOR UPDATE NOWAIT;
645
646 tlinfo c1%ROWTYPE;
647
648 BEGIN
649
650 OPEN c1;
651 FETCH c1 INTO tlinfo;
652 IF (c1%notfound) THEN
653 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
654 igs_ge_msg_stack.add;
655 CLOSE c1;
656 app_exception.raise_exception;
657 RETURN;
658 END IF;
659 CLOSE c1;
660
661 IF (
662 (tlinfo.fund_id = x_fund_id)
663 AND (tlinfo.base_id = x_base_id)
664 AND (tlinfo.person_id = x_person_id)
665 AND (tlinfo.ld_cal_type = x_ld_cal_type)
666 AND (tlinfo.ld_sequence_number = x_ld_sequence_number)
667 AND ((tlinfo.tot_spnsr_amount = x_tot_spnsr_amount) OR ((tlinfo.tot_spnsr_amount IS NULL) AND (X_tot_spnsr_amount IS NULL)))
668 AND ((tlinfo.min_credit_points = x_min_credit_points) OR ((tlinfo.min_credit_points IS NULL) AND (X_min_credit_points IS NULL)))
669 AND ((tlinfo.min_attendance_type = x_min_attendance_type) OR ((tlinfo.min_attendance_type IS NULL) AND (X_min_attendance_type IS NULL)))
670 ) THEN
671 NULL;
672 ELSE
673 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
674 igs_ge_msg_stack.add;
675 app_exception.raise_exception;
676 END IF;
677
678 RETURN;
679
680 END lock_row;
681
682
683 PROCEDURE update_row (
684 x_rowid IN VARCHAR2,
685 x_spnsr_stdnt_id IN NUMBER,
686 x_fund_id IN NUMBER,
687 x_base_id IN NUMBER,
688 x_person_id IN NUMBER,
689 x_ld_cal_type IN VARCHAR2,
690 x_ld_sequence_number IN NUMBER,
691 x_tot_spnsr_amount IN NUMBER,
692 x_min_credit_points IN NUMBER,
693 x_min_attendance_type IN VARCHAR2,
694 x_mode IN VARCHAR2 DEFAULT 'R'
695 ) AS
696 /*
697 || Created By :
698 || Created On : 28-DEC-2001
699 || Purpose : Handles the UPDATE DML logic for the table.
700 || Known limitations, enhancements or remarks :
701 || Change History :
702 || Who When What
703 || (reverse chronological order - newest change first)
704 */
705 x_last_update_date DATE ;
706 x_last_updated_by NUMBER;
707 x_last_update_login NUMBER;
708
709 BEGIN
710
711 x_last_update_date := SYSDATE;
712 IF (X_MODE = 'I') THEN
713 x_last_updated_by := 1;
714 x_last_update_login := 0;
715 ELSIF (x_mode = 'R') THEN
716 x_last_updated_by := fnd_global.user_id;
717 IF x_last_updated_by IS NULL THEN
718 x_last_updated_by := -1;
719 END IF;
720 x_last_update_login := fnd_global.login_id;
721 IF (x_last_update_login IS NULL) THEN
722 x_last_update_login := -1;
723 END IF;
724 ELSE
725 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
726 igs_ge_msg_stack.add;
727 app_exception.raise_exception;
728 END IF;
729
730 before_dml(
731 p_action => 'UPDATE',
732 x_rowid => x_rowid,
733 x_spnsr_stdnt_id => x_spnsr_stdnt_id,
734 x_fund_id => x_fund_id,
735 x_base_id => x_base_id,
736 x_person_id => x_person_id,
737 x_ld_cal_type => x_ld_cal_type,
738 x_ld_sequence_number => x_ld_sequence_number,
739 x_tot_spnsr_amount => x_tot_spnsr_amount,
740 x_min_credit_points => x_min_credit_points,
741 x_min_attendance_type => x_min_attendance_type,
742 x_creation_date => x_last_update_date,
743 x_created_by => x_last_updated_by,
744 x_last_update_date => x_last_update_date,
745 x_last_updated_by => x_last_updated_by,
746 x_last_update_login => x_last_update_login
747 );
748
749 UPDATE igf_sp_stdnt_rel_all
750 SET
751 fund_id = new_references.fund_id,
752 base_id = new_references.base_id,
753 person_id = new_references.person_id,
754 ld_cal_type = new_references.ld_cal_type,
755 ld_sequence_number = new_references.ld_sequence_number,
756 tot_spnsr_amount = new_references.tot_spnsr_amount,
757 min_credit_points = new_references.min_credit_points,
758 min_attendance_type = new_references.min_attendance_type,
759 last_update_date = x_last_update_date,
760 last_updated_by = x_last_updated_by,
761 last_update_login = x_last_update_login
762 WHERE rowid = x_rowid;
763
764 IF (SQL%NOTFOUND) THEN
765 RAISE NO_DATA_FOUND;
766 END IF;
767
768 END update_row;
769
770
771 PROCEDURE add_row (
772 x_rowid IN OUT NOCOPY VARCHAR2,
773 x_spnsr_stdnt_id IN OUT NOCOPY NUMBER,
774 x_fund_id IN NUMBER,
775 x_base_id IN NUMBER,
776 x_person_id IN NUMBER,
777 x_ld_cal_type IN VARCHAR2,
778 x_ld_sequence_number IN NUMBER,
779 x_tot_spnsr_amount IN NUMBER,
780 x_min_credit_points IN NUMBER,
781 x_min_attendance_type IN VARCHAR2,
782 x_mode IN VARCHAR2 DEFAULT 'R'
783 ) AS
784 /*
785 || Created By :
786 || Created On : 28-DEC-2001
787 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
788 || Known limitations, enhancements or remarks :
789 || Change History :
790 || Who When What
791 || (reverse chronological order - newest change first)
792 */
793 CURSOR c1 IS
794 SELECT rowid
795 FROM igf_sp_stdnt_rel_all
796 WHERE spnsr_stdnt_id = x_spnsr_stdnt_id;
797
798 BEGIN
799
800 OPEN c1;
801 FETCH c1 INTO x_rowid;
802 IF (c1%NOTFOUND) THEN
803 CLOSE c1;
804
805 insert_row (
806 x_rowid,
807 x_spnsr_stdnt_id,
808 x_fund_id,
809 x_base_id,
810 x_person_id,
811 x_ld_cal_type,
812 x_ld_sequence_number,
813 x_tot_spnsr_amount,
814 x_min_credit_points,
815 x_min_attendance_type,
816 x_mode
817 );
818 RETURN;
819 END IF;
820 CLOSE c1;
821
822 update_row (
823 x_rowid,
824 x_spnsr_stdnt_id,
825 x_fund_id,
826 x_base_id,
827 x_person_id,
828 x_ld_cal_type,
829 x_ld_sequence_number,
830 x_tot_spnsr_amount,
831 x_min_credit_points,
832 x_min_attendance_type,
833 x_mode
834 );
835
836 END add_row;
837
838
839 PROCEDURE delete_row (
840 x_rowid IN VARCHAR2
841 ) AS
842 /*
843 || Created By :
844 || Created On : 28-DEC-2001
845 || Purpose : Handles the DELETE DML logic for the table.
846 || Known limitations, enhancements or remarks :
847 || Change History :
848 || Who When What
849 || (reverse chronological order - newest change first)
850 */
851 BEGIN
852
853 before_dml (
854 p_action => 'DELETE',
855 x_rowid => x_rowid
856 );
857
858 DELETE FROM igf_sp_stdnt_rel_all
859 WHERE rowid = x_rowid;
860
861 IF (SQL%NOTFOUND) THEN
862 RAISE NO_DATA_FOUND;
863 END IF;
864
865 END delete_row;
866
867
868 END igf_sp_stdnt_rel_pkg;
[Home] [Help]