1 PACKAGE BODY igf_ap_attend_map_pkg AS
2 /* $Header: IGFAI44B.pls 115.9 2002/11/28 14:02:04 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_ap_attend_map_all%ROWTYPE;
6 new_references igf_ap_attend_map_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2 ,
10 x_rowid IN VARCHAR2 ,
11 x_atm_id IN NUMBER ,
12 x_attendance_type IN VARCHAR2 ,
13 x_pell_att_code IN VARCHAR2 ,
14 x_cl_att_code IN VARCHAR2 ,
15 x_ap_att_code IN VARCHAR2 ,
16 x_cal_type IN VARCHAR2,
17 x_sequence_number IN NUMBER ,
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 : 11-JUN-2001
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_AP_ATTEND_MAP_ALL
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.atm_id := x_atm_id;
58 new_references.attendance_type := x_attendance_type;
59 new_references.pell_att_code := x_pell_att_code;
60 new_references.cl_att_code := x_cl_att_code;
61 new_references.ap_att_code := x_ap_att_code;
62 new_references.cal_type := x_cal_type;
63 new_references.sequence_number := x_sequence_number;
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_uniqueness AS
81 /*
82 || Created By : [email protected]
83 || Created On : 11-JUN-2001
84 || Purpose : Handles the Unique Constraint logic defined for the columns.
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 ( get_uk_for_validation (
93 new_references.attendance_type,
94 new_references.org_id,
95 new_references.cal_type ,
96 new_references.sequence_number
97 )
98 ) THEN
99 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
100 igs_ge_msg_stack.add;
101 app_exception.raise_exception;
102 END IF;
103
104 END check_uniqueness;
105
106
107 PROCEDURE check_parent_existance AS
108 /*
109 || Created By : [email protected]
110 || Created On : 11-JUN-2001
111 || Purpose : Checks for the existance of Parent records.
112 || Known limitations, enhancements or remarks :
113 || Change History :
114 || Who When What
115 || adhawan 24-oct-2002 Added check for Award Year
116 || 2613546
117 || (reverse chronological order - newest change first)
118 */
119 BEGIN
120
121 IF (((old_references.attendance_type = new_references.attendance_type)) OR
122 ((new_references.attendance_type IS NULL))) THEN
123 NULL;
124 ELSIF NOT igs_en_atd_type_pkg.get_pk_for_validation (
125 new_references.attendance_type
126 ) THEN
127 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
128 igs_ge_msg_stack.add;
129 app_exception.raise_exception;
130 --Bug ID 2613546
131 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation(new_references.cal_type,
132 new_references.sequence_number) 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 END check_parent_existance;
139
140
141 FUNCTION get_pk_for_validation (
142 x_atm_id IN NUMBER
143 ) RETURN BOOLEAN AS
144 /*
145 || Created By : [email protected]
146 || Created On : 11-JUN-2001
147 || Purpose : Validates the Primary Key of 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_rowid IS
154 SELECT rowid
155 FROM igf_ap_attend_map_all
156 WHERE atm_id = x_atm_id
157 FOR UPDATE NOWAIT;
158
159 lv_rowid cur_rowid%RowType;
160
161 BEGIN
162
163 OPEN cur_rowid;
164 FETCH cur_rowid INTO lv_rowid;
165 IF (cur_rowid%FOUND) THEN
166 CLOSE cur_rowid;
167 RETURN(TRUE);
168 ELSE
169 CLOSE cur_rowid;
170 RETURN(FALSE);
171 END IF;
172
173 END get_pk_for_validation;
174
175
176 FUNCTION get_uk_for_validation (
177 x_attendance_type IN VARCHAR2,
178 x_org_id IN NUMBER ,
179 x_cal_type IN VARCHAR2,
180 x_sequence_number IN NUMBER
181 ) RETURN BOOLEAN AS
182 /*
183 || Created By : [email protected]
184 || Created On : 11-JUN-2001
185 || Purpose : Validates the Unique Keys of the table.
186 || Known limitations, enhancements or remarks :
187 || Change History :
188 || Who When What
189 || vvutukur 18-feb-2002 modified org_id check in cur_rowid cursor with new local variable l_org_id.Bug:2222272.
190 || (reverse chronological order - newest change first)
191 */
192
193 l_org_id igf_ap_attend_map.org_id%TYPE := igf_aw_gen.get_org_id;
194
195 CURSOR cur_rowid IS
196 SELECT rowid
197 FROM igf_ap_attend_map
198 WHERE attendance_type = x_attendance_type
199 AND cal_type = x_cal_type
200 AND sequence_number = x_sequence_number
201 AND NVL(org_id,NVL(l_org_id,-99)) = NVL(l_org_id,-99)
202 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
203
204 lv_rowid cur_rowid%RowType;
205
206 BEGIN
207
208 OPEN cur_rowid;
209 FETCH cur_rowid INTO lv_rowid;
210 IF (cur_rowid%FOUND) THEN
211 CLOSE cur_rowid;
212 RETURN (true);
213 ELSE
214 CLOSE cur_rowid;
215 RETURN(FALSE);
216 END IF;
217
218 END get_uk_for_validation ;
219
220 PROCEDURE get_fk_igs_ca_inst (
221 x_cal_type IN VARCHAR2,
222 x_sequence_number IN NUMBER
223 ) AS
224 /*
225 || Created By : adhawan
226 || Created On : 24-OCT-2002
227 || Purpose : Validates the Foreign Keys for the table.
228 || Known limitations, enhancements or remarks :
229 || Change History : Bug id 2613546
230 || Who When What
231 ||
232 || (reverse chronological order - newest change first)
233 */
234 CURSOR cur_rowid IS
235 SELECT rowid
236 FROM igf_ap_attend_map_all
237 WHERE ((cal_type = x_cal_type) AND
238 (sequence_number = x_sequence_number)
239 );
240 lv_rowid cur_rowid%RowType;
241
242 BEGIN
243
244 OPEN cur_rowid;
245 FETCH cur_rowid INTO lv_rowid;
246 IF (cur_rowid%FOUND) THEN
247 CLOSE cur_rowid;
248 fnd_message.set_name ('IGF','IGF_AP_ATM_CI_FK');
249 igs_ge_msg_stack.add;
250 app_exception.raise_exception;
251 END IF;
252 CLOSE cur_rowid;
253
254 END get_fk_igs_ca_inst;
255
256
257 PROCEDURE get_fk_igs_en_atd_type_all (
258 x_attendance_type IN VARCHAR2
259 ) AS
260 /*
261 || Created By : [email protected]
262 || Created On : 11-JUN-2001
263 || Purpose : Validates the Foreign Keys for the table.
264 || Known limitations, enhancements or remarks :
265 || Change History :
266 || Who When What
267 || masehgal 14-Jun-2002 # 2413695 Changed message to
268 || 'IGF','IGF_AP_ATM_ATT_FK'
269 || (reverse chronological order - newest change first)
270 */
271 CURSOR cur_rowid IS
272 SELECT rowid
273 FROM igf_ap_attend_map_all
274 WHERE ((attendance_type = x_attendance_type));
275
276 lv_rowid cur_rowid%RowType;
277
278 BEGIN
279
280 OPEN cur_rowid;
281 FETCH cur_rowid INTO lv_rowid;
282 IF (cur_rowid%FOUND) THEN
283 CLOSE cur_rowid;
284 fnd_message.set_name ('IGF','IGF_AP_ATM_ATT_FK');
285 igs_ge_msg_stack.add;
286 app_exception.raise_exception;
287 RETURN;
288 END IF;
289 CLOSE cur_rowid;
290
291 END get_fk_igs_en_atd_type_all;
292
293
294 PROCEDURE before_dml (
295 p_action IN VARCHAR2 ,
296 x_rowid IN VARCHAR2 ,
297 x_atm_id IN NUMBER ,
298 x_attendance_type IN VARCHAR2 ,
299 x_pell_att_code IN VARCHAR2 ,
300 x_cl_att_code IN VARCHAR2 ,
301 x_ap_att_code IN VARCHAR2 ,
302 x_cal_type IN VARCHAR2 ,
303 x_sequence_number IN NUMBER ,
304 x_creation_date IN DATE ,
305 x_created_by IN NUMBER ,
306 x_last_update_date IN DATE ,
307 x_last_updated_by IN NUMBER ,
308 x_last_update_login IN NUMBER
309 ) AS
310 /*
311 || Created By : [email protected]
312 || Created On : 11-JUN-2001
313 || Purpose : Initialises the columns, Checks Constraints, Calls the
314 || Trigger Handlers for the table, before any DML operation.
315 || Known limitations, enhancements or remarks :
316 || Change History :
317 || Who When What
318 || (reverse chronological order - newest change first)
319 */
320 BEGIN
321
322 set_column_values (
323 p_action,
324 x_rowid,
325 x_atm_id,
326 x_attendance_type,
327 x_pell_att_code,
328 x_cl_att_code,
329 x_ap_att_code,
330 x_cal_type ,
331 x_sequence_number,
332 x_creation_date,
333 x_created_by,
334 x_last_update_date,
335 x_last_updated_by,
336 x_last_update_login
337 );
338
339 IF (p_action = 'INSERT') THEN
340 -- Call all the procedures related to Before Insert.
341 IF ( get_pk_for_validation(
342 new_references.atm_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 check_parent_existance;
351 ELSIF (p_action = 'UPDATE') THEN
352 -- Call all the procedures related to Before Update.
353 check_uniqueness;
354 check_parent_existance;
355 ELSIF (p_action = 'VALIDATE_INSERT') THEN
356 -- Call all the procedures related to Before Insert.
357 IF ( get_pk_for_validation (
358 new_references.atm_id
359 )
360 ) THEN
361 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
362 igs_ge_msg_stack.add;
363 app_exception.raise_exception;
364 END IF;
365 check_uniqueness;
366 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
367 check_uniqueness;
368 END IF;
369
370 END before_dml;
371
372
373 PROCEDURE insert_row (
374 x_rowid IN OUT NOCOPY VARCHAR2,
375 x_atm_id IN OUT NOCOPY NUMBER,
376 x_attendance_type IN VARCHAR2,
377 x_pell_att_code IN VARCHAR2,
378 x_cl_att_code IN VARCHAR2,
379 x_ap_att_code IN VARCHAR2,
380 x_cal_type IN VARCHAR2,
381 x_sequence_number IN NUMBER,
382 x_mode IN VARCHAR2
383 ) AS
384 /*
385 || Created By : [email protected]
386 || Created On : 11-JUN-2001
387 || Purpose : Handles the INSERT DML logic for the table.
388 || Known limitations, enhancements or remarks :
389 || Change History :
390 || Who When What
391 || (reverse chronological order - newest change first)
392 */
393 CURSOR c IS
394 SELECT rowid
395 FROM igf_ap_attend_map_all
396 WHERE atm_id = x_atm_id;
397
398 x_last_update_date DATE;
399 x_last_updated_by NUMBER;
400 x_last_update_login NUMBER;
401
402 BEGIN
403
404 x_last_update_date := SYSDATE;
405 IF (x_mode = 'I') THEN
406 x_last_updated_by := 1;
407 x_last_update_login := 0;
408 ELSIF (x_mode = 'R') THEN
409 x_last_updated_by := fnd_global.user_id;
410 IF (x_last_updated_by IS NULL) THEN
411 x_last_updated_by := -1;
412 END IF;
413 x_last_update_login := fnd_global.login_id;
414 IF (x_last_update_login IS NULL) THEN
415 x_last_update_login := -1;
416 END IF;
417 ELSE
418 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
419 igs_ge_msg_stack.add;
420 app_exception.raise_exception;
421 END IF;
422
423 SELECT igf_ap_attend_map_s.NEXTVAL
424 INTO x_atm_id
425 FROM dual;
426
427 new_references.org_id := igs_ge_gen_003.get_org_id;
428
429 before_dml(
433 x_attendance_type => x_attendance_type,
430 p_action => 'INSERT',
431 x_rowid => x_rowid,
432 x_atm_id => x_atm_id,
434 x_pell_att_code => x_pell_att_code,
435 x_cl_att_code => x_cl_att_code,
436 x_ap_att_code => x_ap_att_code,
437 x_cal_type => x_cal_type ,
438 x_sequence_number => x_sequence_number,
439 x_creation_date => x_last_update_date,
440 x_created_by => x_last_updated_by,
441 x_last_update_date => x_last_update_date,
442 x_last_updated_by => x_last_updated_by,
443 x_last_update_login => x_last_update_login
444 );
445
446 INSERT INTO igf_ap_attend_map_all (
447 atm_id,
448 attendance_type,
449 pell_att_code,
450 cl_att_code,
451 ap_att_code,
452 cal_type,
453 sequence_number,
454 org_id,
455 creation_date,
456 created_by,
457 last_update_date,
458 last_updated_by,
459 last_update_login
460 ) VALUES (
461 new_references.atm_id,
462 new_references.attendance_type,
463 new_references.pell_att_code,
464 new_references.cl_att_code,
465 new_references.ap_att_code,
466 new_references.cal_type ,
467 new_references.sequence_number,
468 new_references.org_id,
469 x_last_update_date,
470 x_last_updated_by,
471 x_last_update_date,
472 x_last_updated_by,
473 x_last_update_login
474 );
475
476 OPEN c;
477 FETCH c INTO x_rowid;
478 IF (c%NOTFOUND) THEN
479 CLOSE c;
480 RAISE NO_DATA_FOUND;
481 END IF;
482 CLOSE c;
483
484 END insert_row;
485
486
487 PROCEDURE lock_row (
488 x_rowid IN VARCHAR2,
489 x_atm_id IN NUMBER,
490 x_attendance_type IN VARCHAR2,
491 x_pell_att_code IN VARCHAR2,
492 x_cl_att_code IN VARCHAR2,
493 x_ap_att_code IN VARCHAR2,
494 x_cal_type IN VARCHAR2,
495 x_sequence_number IN NUMBER
496 ) AS
497 /*
498 || Created By : [email protected]
499 || Created On : 11-JUN-2001
500 || Purpose : Handles the LOCK mechanism for the table.
501 || Known limitations, enhancements or remarks :
502 || Change History :
503 || Who When What
504 || (reverse chronological order - newest change first)
505 */
506 CURSOR c1 IS
507 SELECT
508 attendance_type,
509 pell_att_code,
510 cl_att_code,
511 ap_att_code,
512 cal_type ,
513 sequence_number
514 FROM igf_ap_attend_map_all
515 WHERE rowid = x_rowid
516 FOR UPDATE NOWAIT;
517
518 tlinfo c1%ROWTYPE;
519
520 BEGIN
521
522 OPEN c1;
523 FETCH c1 INTO tlinfo;
524 IF (c1%notfound) THEN
525 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
526 igs_ge_msg_stack.add;
527 CLOSE c1;
528 app_exception.raise_exception;
529 RETURN;
530 END IF;
531 CLOSE c1;
532
533 IF (
534 (tlinfo.attendance_type = x_attendance_type)
535 AND ((tlinfo.pell_att_code = x_pell_att_code) OR ((tlinfo.pell_att_code IS NULL) AND (X_pell_att_code IS NULL)))
536 AND ((tlinfo.cl_att_code = x_cl_att_code) OR ((tlinfo.cl_att_code IS NULL) AND (X_cl_att_code IS NULL)))
537 AND ((tlinfo.ap_att_code = x_ap_att_code) OR ((tlinfo.ap_att_code IS NULL) AND (X_ap_att_code IS NULL)))
538 AND ((tlinfo.cal_type = x_cal_type ) OR ((tlinfo.cal_type IS NULL) AND (x_cal_type IS NULL)))
539 AND ((tlinfo.sequence_number = x_sequence_number ) OR ((tlinfo.sequence_number IS NULL) AND (x_sequence_number IS NULL)))
540 ) THEN
541 NULL;
542 ELSE
543 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
544 igs_ge_msg_stack.add;
545 app_exception.raise_exception;
546 END IF;
547
548 RETURN;
549
550 END lock_row;
551
552
553 PROCEDURE update_row (
554 x_rowid IN VARCHAR2,
555 x_atm_id IN NUMBER,
556 x_attendance_type IN VARCHAR2,
557 x_pell_att_code IN VARCHAR2,
558 x_cl_att_code IN VARCHAR2,
559 x_ap_att_code IN VARCHAR2,
560 x_cal_type IN VARCHAR2,
561 x_sequence_number IN NUMBER,
562 x_mode IN VARCHAR2
563 ) AS
564 /*
565 || Created By : [email protected]
566 || Created On : 11-JUN-2001
567 || Purpose : Handles the UPDATE DML logic for the table.
568 || Known limitations, enhancements or remarks :
569 || Change History :
570 || Who When What
571 || (reverse chronological order - newest change first)
572 */
573 x_last_update_date DATE ;
574 x_last_updated_by NUMBER;
575 x_last_update_login NUMBER;
576
577 BEGIN
578
579 x_last_update_date := SYSDATE;
580 IF (X_MODE = 'I') THEN
581 x_last_updated_by := 1;
582 x_last_update_login := 0;
583 ELSIF (x_mode = 'R') THEN
584 x_last_updated_by := fnd_global.user_id;
585 IF x_last_updated_by IS NULL THEN
586 x_last_updated_by := -1;
587 END IF;
588 x_last_update_login := fnd_global.login_id;
589 IF (x_last_update_login IS NULL) THEN
590 x_last_update_login := -1;
591 END IF;
592 ELSE
593 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
594 igs_ge_msg_stack.add;
598 before_dml(
595 app_exception.raise_exception;
596 END IF;
597
599 p_action => 'UPDATE',
600 x_rowid => x_rowid,
601 x_atm_id => x_atm_id,
602 x_attendance_type => x_attendance_type,
603 x_pell_att_code => x_pell_att_code,
604 x_cl_att_code => x_cl_att_code,
605 x_ap_att_code => x_ap_att_code,
606 x_cal_type => x_cal_type ,
607 x_sequence_number => x_sequence_number,
608 x_creation_date => x_last_update_date,
609 x_created_by => x_last_updated_by,
610 x_last_update_date => x_last_update_date,
611 x_last_updated_by => x_last_updated_by,
612 x_last_update_login => x_last_update_login
613 );
614
615 UPDATE igf_ap_attend_map_all
616 SET
617 attendance_type = new_references.attendance_type,
618 pell_att_code = new_references.pell_att_code,
619 cl_att_code = new_references.cl_att_code,
620 ap_att_code = new_references.ap_att_code,
621 cal_type = new_references.cal_type ,
622 sequence_number = new_references.sequence_number,
623 last_update_date = x_last_update_date,
624 last_updated_by = x_last_updated_by,
625 last_update_login = x_last_update_login
626 WHERE rowid = x_rowid;
627
628 IF (SQL%NOTFOUND) THEN
629 RAISE NO_DATA_FOUND;
630 END IF;
631
632 END update_row;
633
634
635 PROCEDURE add_row (
636 x_rowid IN OUT NOCOPY VARCHAR2,
637 x_atm_id IN OUT NOCOPY NUMBER,
638 x_attendance_type IN VARCHAR2,
639 x_pell_att_code IN VARCHAR2,
640 x_cl_att_code IN VARCHAR2,
641 x_ap_att_code IN VARCHAR2,
642 x_cal_type IN VARCHAR2,
643 x_sequence_number IN NUMBER ,
644 x_mode IN VARCHAR2
645 ) AS
646 /*
647 || Created By : [email protected]
648 || Created On : 11-JUN-2001
649 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
650 || Known limitations, enhancements or remarks :
651 || Change History :
652 || Who When What
653 || (reverse chronological order - newest change first)
654 */
655 CURSOR c1 IS
656 SELECT rowid
657 FROM igf_ap_attend_map_all
658 WHERE atm_id = x_atm_id;
659
660 BEGIN
661
662 OPEN c1;
663 FETCH c1 INTO x_rowid;
664 IF (c1%NOTFOUND) THEN
665 CLOSE c1;
666
667 insert_row (
668 x_rowid,
669 x_atm_id,
670 x_attendance_type,
671 x_pell_att_code,
672 x_cl_att_code,
673 x_ap_att_code,
674 x_cal_type,
675 x_sequence_number,
676 x_mode
677 );
678 RETURN;
679 END IF;
680 CLOSE c1;
681
682 update_row (
683 x_rowid,
684 x_atm_id,
685 x_attendance_type,
686 x_pell_att_code,
687 x_cl_att_code,
688 x_ap_att_code,
689 x_cal_type ,
690 x_sequence_number,
691 x_mode
692 );
693
694 END add_row;
695
696
697 PROCEDURE delete_row (
698 x_rowid IN VARCHAR2
699 ) AS
700 /*
701 || Created By : [email protected]
702 || Created On : 11-JUN-2001
703 || Purpose : Handles the DELETE DML logic for the table.
704 || Known limitations, enhancements or remarks :
705 || Change History :
706 || Who When What
707 || (reverse chronological order - newest change first)
708 */
709 BEGIN
710
711 before_dml (
712 p_action => 'DELETE',
713 x_rowid => x_rowid
714 );
715
716 DELETE FROM igf_ap_attend_map_all
717 WHERE rowid = x_rowid;
718
719 IF (SQL%NOTFOUND) THEN
720 RAISE NO_DATA_FOUND;
721 END IF;
722
723 END delete_row;
724
725
726 END igf_ap_attend_map_pkg;
[Home] [Help]