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