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