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