1 PACKAGE BODY igs_as_stu_trn_cmts_pkg AS
2 /* $Header: IGSDI78B.pls 115.1 2003/10/14 07:58:21 kdande noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_as_stu_trn_cmts%ROWTYPE;
6 new_references igs_as_stu_trn_cmts%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_comment_id IN NUMBER,
12 x_comment_type_code IN VARCHAR2,
13 x_comment_txt IN VARCHAR2,
14 x_person_id IN NUMBER,
15 x_course_cd IN VARCHAR2,
16 x_course_type IN VARCHAR2,
17 x_award_cd IN VARCHAR2,
18 x_load_cal_type IN VARCHAR2,
19 x_load_ci_sequence_number IN NUMBER,
20 x_unit_set_cd IN VARCHAR2,
21 x_us_version_number IN NUMBER,
22 x_uoo_id IN NUMBER,
23 x_creation_date IN DATE,
24 x_created_by IN NUMBER,
25 x_last_update_date IN DATE,
26 x_last_updated_by IN NUMBER,
27 x_last_update_login IN NUMBER
28 ) AS
29 /*
30 || Created By : [email protected]
31 || Created On : 22-SEP-2003
32 || Purpose : Initialises the Old and New references for the columns of the table.
33 || Known limitations, enhancements or remarks :
34 || Change History :
35 || Who When What
36 || (reverse chronological order - newest change first)
37 */
38
39 CURSOR cur_old_ref_values IS
40 SELECT *
41 FROM igs_as_stu_trn_cmts
42 WHERE rowid = x_rowid;
43
44 BEGIN
45
46 l_rowid := x_rowid;
47
48 -- Code for setting the Old and New Reference Values.
49 -- Populate Old Values.
50 OPEN cur_old_ref_values;
51 FETCH cur_old_ref_values INTO old_references;
52 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
53 CLOSE cur_old_ref_values;
54 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
55 igs_ge_msg_stack.add;
56 app_exception.raise_exception;
57 RETURN;
58 END IF;
59 CLOSE cur_old_ref_values;
60
61 -- Populate New Values.
62 new_references.comment_id := x_comment_id;
63 new_references.comment_type_code := x_comment_type_code;
64 new_references.comment_txt := x_comment_txt;
65 new_references.person_id := x_person_id;
66 new_references.course_cd := x_course_cd;
67 new_references.course_type := x_course_type;
68 new_references.award_cd := x_award_cd;
69 new_references.load_cal_type := x_load_cal_type;
70 new_references.load_ci_sequence_number := x_load_ci_sequence_number;
71 new_references.unit_set_cd := x_unit_set_cd;
72 new_references.us_version_number := x_us_version_number;
73 new_references.uoo_id := x_uoo_id;
74
75 IF (p_action = 'UPDATE') THEN
76 new_references.creation_date := old_references.creation_date;
77 new_references.created_by := old_references.created_by;
78 ELSE
79 new_references.creation_date := x_creation_date;
80 new_references.created_by := x_created_by;
81 END IF;
82
83 new_references.last_update_date := x_last_update_date;
84 new_references.last_updated_by := x_last_updated_by;
85 new_references.last_update_login := x_last_update_login;
86
87 END set_column_values;
88
89
90 PROCEDURE check_uniqueness AS
91 /*
92 || Created By : [email protected]
93 || Created On : 22-SEP-2003
94 || Purpose : Handles the Unique Constraint logic defined for the columns.
95 || Known limitations, enhancements or remarks :
96 || Change History :
97 || Who When What
98 || (reverse chronological order - newest change first)
99 */
100 BEGIN
101
102 IF ( get_uk_for_validation (
103 new_references.comment_type_code,
104 new_references.person_id,
105 new_references.course_cd,
106 new_references.course_type,
107 new_references.award_cd,
108 new_references.load_cal_type,
109 new_references.load_ci_sequence_number,
110 new_references.unit_set_cd,
111 new_references.us_version_number,
112 new_references.uoo_id
113 )
114 ) THEN
115 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
116 igs_ge_msg_stack.add;
117 app_exception.raise_exception;
118 END IF;
119
120 END check_uniqueness;
121
122
123 PROCEDURE check_parent_existance AS
124 /*
125 || Created By : [email protected]
126 || Created On : 22-SEP-2003
127 || Purpose : Checks for the existance of Parent records.
128 || Known limitations, enhancements or remarks :
129 || Change History :
130 || Who When What
131 || (reverse chronological order - newest change first)
132 */
133 BEGIN
134
135 IF (((old_references.load_cal_type = new_references.load_cal_type) AND
136 (old_references.load_ci_sequence_number = new_references.load_ci_sequence_number)) OR
137 ((new_references.load_cal_type IS NULL) OR
138 (new_references.load_ci_sequence_number IS NULL))) THEN
139 NULL;
140 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
141 new_references.load_cal_type,
142 new_references.load_ci_sequence_number
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.unit_set_cd = new_references.unit_set_cd) AND
150 (old_references.us_version_number = new_references.us_version_number)) OR
151 ((new_references.unit_set_cd IS NULL) OR
152 (new_references.us_version_number IS NULL))) THEN
153 NULL;
154 ELSIF NOT igs_en_unit_set_pkg.get_pk_for_validation (
155 new_references.unit_set_cd,
156 new_references.us_version_number
157 ) THEN
158 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
159 igs_ge_msg_stack.add;
160 app_exception.raise_exception;
161 END IF;
162
163 IF (((old_references.award_cd = new_references.award_cd)) OR
164 ((new_references.award_cd IS NULL))) THEN
165 NULL;
166 ELSIF NOT igs_ps_awd_pkg.get_pk_for_validation (
167 new_references.award_cd
168 ) THEN
169 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
170 igs_ge_msg_stack.add;
171 app_exception.raise_exception;
172 END IF;
173
174 IF (((old_references.course_type = new_references.course_type)) OR
175 ((new_references.course_type IS NULL))) THEN
176 NULL;
177 ELSIF NOT igs_ps_type_pkg.get_pk_for_validation (
178 new_references.course_type
179 ) THEN
180 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
181 igs_ge_msg_stack.add;
182 app_exception.raise_exception;
183 END IF;
184
185 IF (((old_references.uoo_id = new_references.uoo_id)) OR
186 ((new_references.uoo_id IS NULL))) THEN
187 NULL;
188 ELSIF NOT igs_ps_unit_ofr_opt_pkg.get_uk_For_validation (
189 new_references.uoo_id
190 ) THEN
191 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
192 igs_ge_msg_stack.add;
193 app_exception.raise_exception;
194 END IF;
195
196 IF (((old_references.person_id = new_references.person_id) AND
197 (old_references.course_cd = new_references.course_cd)) OR
198 ((new_references.person_id IS NULL) OR
199 (new_references.course_cd IS NULL))) THEN
200 NULL;
201 ELSIF NOT igs_en_stdnt_ps_att_pkg.get_pk_for_validation (
202 new_references.person_id,
203 new_references.course_cd
204 ) THEN
205 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
206 igs_ge_msg_stack.add;
207 app_exception.raise_exception;
208 END IF;
209
210 END check_parent_existance;
211
212
213 FUNCTION get_pk_for_validation (
214 x_comment_id IN NUMBER
215 ) RETURN BOOLEAN AS
216 /*
217 || Created By : [email protected]
218 || Created On : 22-SEP-2003
219 || Purpose : Validates the Primary Key of the table.
220 || Known limitations, enhancements or remarks :
221 || Change History :
222 || Who When What
223 || (reverse chronological order - newest change first)
224 */
225 CURSOR cur_rowid IS
226 SELECT rowid
227 FROM igs_as_stu_trn_cmts
228 WHERE comment_id = x_comment_id
229 FOR UPDATE NOWAIT;
230
231 lv_rowid cur_rowid%RowType;
232
233 BEGIN
234
235 OPEN cur_rowid;
236 FETCH cur_rowid INTO lv_rowid;
237 IF (cur_rowid%FOUND) THEN
238 CLOSE cur_rowid;
239 RETURN(TRUE);
240 ELSE
241 CLOSE cur_rowid;
242 RETURN(FALSE);
243 END IF;
244
245 END get_pk_for_validation;
246
247
248 FUNCTION get_uk_for_validation (
249 x_comment_type_code IN VARCHAR2,
250 x_person_id IN NUMBER,
251 x_course_cd IN VARCHAR2,
252 x_course_type IN VARCHAR2,
253 x_award_cd IN VARCHAR2,
254 x_load_cal_type IN VARCHAR2,
255 x_load_ci_sequence_number IN NUMBER,
256 x_unit_set_cd IN VARCHAR2,
257 x_us_version_number IN NUMBER,
258 x_uoo_id IN NUMBER
259 ) RETURN BOOLEAN AS
260 /*
261 || Created By : [email protected]
262 || Created On : 22-SEP-2003
263 || Purpose : Validates the Unique Keys of the table.
264 || Known limitations, enhancements or remarks :
265 || Change History :
266 || Who When What
267 || (reverse chronological order - newest change first)
268 */
269 CURSOR cur_rowid IS
270 SELECT rowid
271 FROM igs_as_stu_trn_cmts
272 WHERE comment_type_code = x_comment_type_code
273 AND person_id = x_person_id
274 AND ((course_cd = x_course_cd) OR (course_cd IS NULL AND x_course_cd IS NULL))
275 AND ((course_type = x_course_type) OR (course_type IS NULL AND x_course_type IS NULL))
276 AND ((award_cd = x_award_cd) OR (award_cd IS NULL AND x_award_cd IS NULL))
277 AND ((load_cal_type = x_load_cal_type) OR (load_cal_type IS NULL AND x_load_cal_type IS NULL))
278 AND ((load_ci_sequence_number = x_load_ci_sequence_number) OR (load_ci_sequence_number IS NULL AND x_load_ci_sequence_number IS NULL))
279 AND ((unit_set_cd = x_unit_set_cd) OR (unit_set_cd IS NULL AND x_unit_set_cd IS NULL))
280 AND ((us_version_number = x_us_version_number) OR (us_version_number IS NULL AND x_us_version_number IS NULL))
281 AND ((uoo_id = x_uoo_id) OR (uoo_id IS NULL AND x_uoo_id IS NULL))
282 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
283
284 lv_rowid cur_rowid%RowType;
285
286 BEGIN
287
288 OPEN cur_rowid;
289 FETCH cur_rowid INTO lv_rowid;
290 IF (cur_rowid%FOUND) THEN
291 CLOSE cur_rowid;
292 RETURN (true);
293 ELSE
294 CLOSE cur_rowid;
295 RETURN(FALSE);
296 END IF;
297
298 END get_uk_for_validation ;
299
300
301 PROCEDURE get_fk_igs_ca_inst (
302 x_cal_type IN VARCHAR2,
303 x_sequence_number IN NUMBER
304 ) AS
305 /*
306 || Created By : [email protected]
307 || Created On : 22-SEP-2003
308 || Purpose : Validates the Foreign Keys for the table.
309 || Known limitations, enhancements or remarks :
310 || Change History :
311 || Who When What
312 || (reverse chronological order - newest change first)
313 */
314 CURSOR cur_rowid IS
315 SELECT rowid
316 FROM igs_as_stu_trn_cmts
317 WHERE ((load_cal_type = x_cal_type) AND
318 (load_ci_sequence_number = x_sequence_number));
319
320 lv_rowid cur_rowid%RowType;
321
322 BEGIN
323
324 OPEN cur_rowid;
325 FETCH cur_rowid INTO lv_rowid;
326 IF (cur_rowid%FOUND) THEN
327 CLOSE cur_rowid;
328 fnd_message.set_name ('IGS', 'IGS_GE_CHILD_REC_XS');
329 igs_ge_msg_stack.add;
330 app_exception.raise_exception;
331 RETURN;
332 END IF;
333 CLOSE cur_rowid;
334
335 END get_fk_igs_ca_inst;
336
337
338 PROCEDURE get_fk_igs_en_unit_set (
339 x_unit_set_cd IN VARCHAR2,
340 x_version_number IN NUMBER
341 ) AS
342 /*
343 || Created By : [email protected]
344 || Created On : 22-SEP-2003
345 || Purpose : Validates the Foreign Keys for the table.
346 || Known limitations, enhancements or remarks :
347 || Change History :
348 || Who When What
349 || (reverse chronological order - newest change first)
350 */
351 CURSOR cur_rowid IS
352 SELECT rowid
353 FROM igs_as_stu_trn_cmts
354 WHERE ((unit_set_cd = x_unit_set_cd) AND
355 (us_version_number = x_version_number));
356
357 lv_rowid cur_rowid%RowType;
358
359 BEGIN
360
361 OPEN cur_rowid;
362 FETCH cur_rowid INTO lv_rowid;
363 IF (cur_rowid%FOUND) THEN
364 CLOSE cur_rowid;
365 fnd_message.set_name ('IGS', 'IGS_GE_CHILD_REC_XS');
366 igs_ge_msg_stack.add;
367 app_exception.raise_exception;
368 RETURN;
369 END IF;
370 CLOSE cur_rowid;
371
372 END get_fk_igs_en_unit_set;
373
374
375 PROCEDURE get_fk_igs_ps_awd (
376 x_award_cd IN VARCHAR2
377 ) AS
378 /*
379 || Created By : [email protected]
380 || Created On : 22-SEP-2003
381 || Purpose : Validates the Foreign Keys for the table.
382 || Known limitations, enhancements or remarks :
383 || Change History :
384 || Who When What
385 || (reverse chronological order - newest change first)
386 */
387 CURSOR cur_rowid IS
388 SELECT rowid
389 FROM igs_as_stu_trn_cmts
390 WHERE ((award_cd = x_award_cd));
391
392 lv_rowid cur_rowid%RowType;
393
394 BEGIN
395
396 OPEN cur_rowid;
397 FETCH cur_rowid INTO lv_rowid;
398 IF (cur_rowid%FOUND) THEN
399 CLOSE cur_rowid;
400 fnd_message.set_name ('IGS', 'IGS_GE_CHILD_REC_XS');
401 igs_ge_msg_stack.add;
402 app_exception.raise_exception;
403 RETURN;
404 END IF;
405 CLOSE cur_rowid;
406
407 END get_fk_igs_ps_awd;
408
409
410 PROCEDURE get_fk_igs_ps_type (
411 x_course_type IN VARCHAR2
412 ) AS
413 /*
414 || Created By : [email protected]
415 || Created On : 22-SEP-2003
416 || Purpose : Validates the Foreign Keys for the table.
417 || Known limitations, enhancements or remarks :
418 || Change History :
419 || Who When What
420 || (reverse chronological order - newest change first)
421 */
422 CURSOR cur_rowid IS
423 SELECT rowid
424 FROM igs_as_stu_trn_cmts
428
425 WHERE ((course_type = x_course_type));
426
427 lv_rowid cur_rowid%RowType;
429 BEGIN
430
431 OPEN cur_rowid;
432 FETCH cur_rowid INTO lv_rowid;
433 IF (cur_rowid%FOUND) THEN
434 CLOSE cur_rowid;
435 fnd_message.set_name ('IGS', 'IGS_GE_CHILD_REC_XS');
436 igs_ge_msg_stack.add;
437 app_exception.raise_exception;
438 RETURN;
439 END IF;
440 CLOSE cur_rowid;
441
442 END get_fk_igs_ps_type;
443
444
445 PROCEDURE get_ufk_igs_ps_unit_ofr_opt (
446 x_uoo_id IN NUMBER
447 ) AS
448 /*
449 || Created By : [email protected]
450 || Created On : 22-SEP-2003
451 || Purpose : Validates the Foreign Keys for the table.
452 || Known limitations, enhancements or remarks :
453 || Change History :
454 || Who When What
455 || (reverse chronological order - newest change first)
456 */
457 CURSOR cur_rowid IS
458 SELECT rowid
459 FROM igs_as_stu_trn_cmts
460 WHERE ((uoo_id = x_uoo_id));
461
462 lv_rowid cur_rowid%RowType;
463
464 BEGIN
465
466 OPEN cur_rowid;
467 FETCH cur_rowid INTO lv_rowid;
468 IF (cur_rowid%FOUND) THEN
469 CLOSE cur_rowid;
470 fnd_message.set_name ('IGS', 'IGS_GE_CHILD_REC_XS');
471 igs_ge_msg_stack.add;
472 app_exception.raise_exception;
473 RETURN;
474 END IF;
475 CLOSE cur_rowid;
476
477 END get_ufk_igs_ps_unit_ofr_opt;
478
479
480 PROCEDURE get_fk_igs_en_stdnt_ps_att (
481 x_person_id IN NUMBER,
482 x_course_cd IN VARCHAR2
483 ) AS
484 /*
485 || Created By : [email protected]
486 || Created On : 22-SEP-2003
487 || Purpose : Validates the Foreign Keys for the table.
488 || Known limitations, enhancements or remarks :
489 || Change History :
490 || Who When What
491 || (reverse chronological order - newest change first)
492 */
493 CURSOR cur_rowid IS
494 SELECT rowid
495 FROM igs_as_stu_trn_cmts
496 WHERE ((course_cd = x_course_cd) AND
497 (person_id = x_person_id));
498
499 lv_rowid cur_rowid%RowType;
500
501 BEGIN
502
503 OPEN cur_rowid;
504 FETCH cur_rowid INTO lv_rowid;
505 IF (cur_rowid%FOUND) THEN
506 CLOSE cur_rowid;
507 fnd_message.set_name ('IGS', 'IGS_GE_CHILD_REC_XS');
508 igs_ge_msg_stack.add;
509 app_exception.raise_exception;
510 RETURN;
511 END IF;
512 CLOSE cur_rowid;
513
514 END get_fk_igs_en_stdnt_ps_att;
515
516
517 PROCEDURE before_dml (
518 p_action IN VARCHAR2,
519 x_rowid IN VARCHAR2,
520 x_comment_id IN NUMBER,
521 x_comment_type_code IN VARCHAR2,
522 x_comment_txt IN VARCHAR2,
523 x_person_id IN NUMBER,
524 x_course_cd IN VARCHAR2,
525 x_course_type IN VARCHAR2,
526 x_award_cd IN VARCHAR2,
527 x_load_cal_type IN VARCHAR2,
528 x_load_ci_sequence_number IN NUMBER,
529 x_unit_set_cd IN VARCHAR2,
530 x_us_version_number IN NUMBER,
531 x_uoo_id IN NUMBER,
532 x_creation_date IN DATE,
533 x_created_by IN NUMBER,
534 x_last_update_date IN DATE,
535 x_last_updated_by IN NUMBER,
536 x_last_update_login IN NUMBER
537 ) AS
538 /*
539 || Created By : [email protected]
540 || Created On : 22-SEP-2003
541 || Purpose : Initialises the columns, Checks Constraints, Calls the
542 || Trigger Handlers for the table, before any DML operation.
543 || Known limitations, enhancements or remarks :
544 || Change History :
545 || Who When What
546 || (reverse chronological order - newest change first)
547 */
548 BEGIN
549
550 set_column_values (
551 p_action,
552 x_rowid,
553 x_comment_id,
554 x_comment_type_code,
555 x_comment_txt,
556 x_person_id,
557 x_course_cd,
558 x_course_type,
559 x_award_cd,
560 x_load_cal_type,
561 x_load_ci_sequence_number,
562 x_unit_set_cd,
563 x_us_version_number,
564 x_uoo_id,
565 x_creation_date,
566 x_created_by,
567 x_last_update_date,
568 x_last_updated_by,
569 x_last_update_login
570 );
571
572 IF (p_action = 'INSERT') THEN
573 -- Call all the procedures related to Before Insert.
574 IF ( get_pk_for_validation(
575 new_references.comment_id
576 )
577 ) THEN
578 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
579 igs_ge_msg_stack.add;
580 app_exception.raise_exception;
581 END IF;
582 check_uniqueness;
583 check_parent_existance;
584 ELSIF (p_action = 'UPDATE') THEN
585 -- Call all the procedures related to Before Update.
586 check_uniqueness;
587 check_parent_existance;
588 ELSIF (p_action = 'VALIDATE_INSERT') THEN
589 -- Call all the procedures related to Before Insert.
590 IF ( get_pk_for_validation (
594 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
591 new_references.comment_id
592 )
593 ) THEN
595 igs_ge_msg_stack.add;
596 app_exception.raise_exception;
597 END IF;
598 check_uniqueness;
599 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
600 check_uniqueness;
601 END IF;
602
603 IF (p_action IN ('VALIDATE_INSERT', 'VALIDATE_UPDATE', 'VALIDATE_DELETE')) THEN
604 l_rowid := NULL;
605 END IF;
606
607 END before_dml;
608
609
610 PROCEDURE insert_row (
611 x_rowid IN OUT NOCOPY VARCHAR2,
612 x_comment_id IN OUT NOCOPY NUMBER,
613 x_comment_type_code IN VARCHAR2,
614 x_comment_txt IN VARCHAR2,
615 x_person_id IN NUMBER,
616 x_course_cd IN VARCHAR2,
617 x_course_type IN VARCHAR2,
618 x_award_cd IN VARCHAR2,
619 x_load_cal_type IN VARCHAR2,
620 x_load_ci_sequence_number IN NUMBER,
621 x_unit_set_cd IN VARCHAR2,
622 x_us_version_number IN NUMBER,
623 x_uoo_id IN NUMBER,
624 x_mode IN VARCHAR2
625 ) AS
626 /*
627 || Created By : [email protected]
628 || Created On : 22-SEP-2003
629 || Purpose : Handles the INSERT DML logic for the table.
630 || Known limitations, enhancements or remarks :
631 || Change History :
632 || Who When What
633 || (reverse chronological order - newest change first)
634 */
635
636 x_last_update_date DATE;
637 x_last_updated_by NUMBER;
638 x_last_update_login NUMBER;
639
640 BEGIN
641
642 x_last_update_date := SYSDATE;
643 IF (x_mode = 'I') THEN
644 x_last_updated_by := 1;
645 x_last_update_login := 0;
646 ELSIF (x_mode = 'R') THEN
647 x_last_updated_by := fnd_global.user_id;
648 IF (x_last_updated_by IS NULL) THEN
649 x_last_updated_by := -1;
650 END IF;
651 x_last_update_login := fnd_global.login_id;
652 IF (x_last_update_login IS NULL) THEN
653 x_last_update_login := -1;
654 END IF;
655 ELSE
656 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
657 fnd_message.set_token ('ROUTINE', 'IGS_AS_STU_TRN_CMTS_PKG.INSERT_ROW');
658 igs_ge_msg_stack.add;
659 app_exception.raise_exception;
660 END IF;
661
662 x_comment_id := NULL;
663
664 before_dml(
665 p_action => 'INSERT',
666 x_rowid => x_rowid,
667 x_comment_id => x_comment_id,
668 x_comment_type_code => x_comment_type_code,
669 x_comment_txt => x_comment_txt,
670 x_person_id => x_person_id,
671 x_course_cd => x_course_cd,
672 x_course_type => x_course_type,
673 x_award_cd => x_award_cd,
674 x_load_cal_type => x_load_cal_type,
675 x_load_ci_sequence_number => x_load_ci_sequence_number,
676 x_unit_set_cd => x_unit_set_cd,
677 x_us_version_number => x_us_version_number,
678 x_uoo_id => x_uoo_id,
679 x_creation_date => x_last_update_date,
680 x_created_by => x_last_updated_by,
681 x_last_update_date => x_last_update_date,
682 x_last_updated_by => x_last_updated_by,
683 x_last_update_login => x_last_update_login
684 );
685
686 INSERT INTO igs_as_stu_trn_cmts (
687 comment_id,
688 comment_type_code,
689 comment_txt,
690 person_id,
691 course_cd,
692 course_type,
693 award_cd,
694 load_cal_type,
695 load_ci_sequence_number,
696 unit_set_cd,
697 us_version_number,
698 uoo_id,
699 creation_date,
700 created_by,
701 last_update_date,
702 last_updated_by,
703 last_update_login
704 ) VALUES (
705 igs_as_stu_trns_cmts_s.NEXTVAL,
706 new_references.comment_type_code,
707 new_references.comment_txt,
708 new_references.person_id,
709 new_references.course_cd,
710 new_references.course_type,
711 new_references.award_cd,
712 new_references.load_cal_type,
713 new_references.load_ci_sequence_number,
714 new_references.unit_set_cd,
715 new_references.us_version_number,
716 new_references.uoo_id,
717 x_last_update_date,
718 x_last_updated_by,
719 x_last_update_date,
723
720 x_last_updated_by,
721 x_last_update_login
722 ) RETURNING ROWID, comment_id INTO x_rowid, x_comment_id;
724 l_rowid := NULL;
725
726 END insert_row;
727
728
729 PROCEDURE lock_row (
730 x_rowid IN VARCHAR2,
731 x_comment_id IN NUMBER,
732 x_comment_type_code IN VARCHAR2,
733 x_comment_txt IN VARCHAR2,
734 x_person_id IN NUMBER,
735 x_course_cd IN VARCHAR2,
736 x_course_type IN VARCHAR2,
737 x_award_cd IN VARCHAR2,
738 x_load_cal_type IN VARCHAR2,
739 x_load_ci_sequence_number IN NUMBER,
740 x_unit_set_cd IN VARCHAR2,
741 x_us_version_number IN NUMBER,
742 x_uoo_id IN NUMBER
743 ) AS
744 /*
745 || Created By : [email protected]
746 || Created On : 22-SEP-2003
747 || Purpose : Handles the LOCK mechanism for the table.
748 || Known limitations, enhancements or remarks :
749 || Change History :
750 || Who When What
751 || (reverse chronological order - newest change first)
752 */
753 CURSOR c1 IS
754 SELECT
755 comment_type_code,
756 comment_txt,
757 person_id,
758 course_cd,
759 course_type,
760 award_cd,
761 load_cal_type,
762 load_ci_sequence_number,
763 unit_set_cd,
764 us_version_number,
765 uoo_id
766 FROM igs_as_stu_trn_cmts
767 WHERE rowid = x_rowid
768 FOR UPDATE NOWAIT;
769
770 tlinfo c1%ROWTYPE;
771
772 BEGIN
773
774 OPEN c1;
775 FETCH c1 INTO tlinfo;
776 IF (c1%notfound) THEN
777 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
778 igs_ge_msg_stack.add;
779 CLOSE c1;
780 app_exception.raise_exception;
781 RETURN;
782 END IF;
783 CLOSE c1;
784
785 IF (
786 (tlinfo.comment_type_code = x_comment_type_code)
787 AND (tlinfo.comment_txt = x_comment_txt)
788 AND (tlinfo.person_id = x_person_id)
789 AND ((tlinfo.course_cd = x_course_cd) OR ((tlinfo.course_cd IS NULL) AND (X_course_cd IS NULL)))
790 AND ((tlinfo.course_type = x_course_type) OR ((tlinfo.course_type IS NULL) AND (X_course_type IS NULL)))
791 AND ((tlinfo.award_cd = x_award_cd) OR ((tlinfo.award_cd IS NULL) AND (X_award_cd IS NULL)))
792 AND ((tlinfo.load_cal_type = x_load_cal_type) OR ((tlinfo.load_cal_type IS NULL) AND (X_load_cal_type IS NULL)))
793 AND ((tlinfo.load_ci_sequence_number = x_load_ci_sequence_number) OR ((tlinfo.load_ci_sequence_number IS NULL) AND (X_load_ci_sequence_number IS NULL)))
794 AND ((tlinfo.unit_set_cd = x_unit_set_cd) OR ((tlinfo.unit_set_cd IS NULL) AND (X_unit_set_cd IS NULL)))
795 AND ((tlinfo.us_version_number = x_us_version_number) OR ((tlinfo.us_version_number IS NULL) AND (X_us_version_number IS NULL)))
796 AND ((tlinfo.uoo_id = x_uoo_id) OR ((tlinfo.uoo_id IS NULL) AND (X_uoo_id IS NULL)))
797 ) THEN
798 NULL;
799 ELSE
800 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
801 igs_ge_msg_stack.add;
802 app_exception.raise_exception;
803 END IF;
804
805 RETURN;
806
807 END lock_row;
808
809
810 PROCEDURE update_row (
811 x_rowid IN VARCHAR2,
812 x_comment_id IN NUMBER,
813 x_comment_type_code IN VARCHAR2,
814 x_comment_txt IN VARCHAR2,
815 x_person_id IN NUMBER,
816 x_course_cd IN VARCHAR2,
817 x_course_type IN VARCHAR2,
818 x_award_cd IN VARCHAR2,
819 x_load_cal_type IN VARCHAR2,
820 x_load_ci_sequence_number IN NUMBER,
821 x_unit_set_cd IN VARCHAR2,
822 x_us_version_number IN NUMBER,
823 x_uoo_id IN NUMBER,
824 x_mode IN VARCHAR2
825 ) AS
826 /*
827 || Created By : [email protected]
828 || Created On : 22-SEP-2003
829 || Purpose : Handles the UPDATE DML logic for the table.
830 || Known limitations, enhancements or remarks :
831 || Change History :
832 || Who When What
833 || (reverse chronological order - newest change first)
834 */
835 x_last_update_date DATE ;
836 x_last_updated_by NUMBER;
837 x_last_update_login NUMBER;
838
839 BEGIN
840
841 x_last_update_date := SYSDATE;
842 IF (X_MODE = 'I') THEN
843 x_last_updated_by := 1;
844 x_last_update_login := 0;
845 ELSIF (x_mode = 'R') THEN
846 x_last_updated_by := fnd_global.user_id;
847 IF x_last_updated_by IS NULL THEN
848 x_last_updated_by := -1;
849 END IF;
850 x_last_update_login := fnd_global.login_id;
851 IF (x_last_update_login IS NULL) THEN
852 x_last_update_login := -1;
853 END IF;
854 ELSE
855 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
856 fnd_message.set_token ('ROUTINE', 'IGS_AS_STU_TRN_CMTS_PKG.UPDATE_ROW');
857 igs_ge_msg_stack.add;
858 app_exception.raise_exception;
859 END IF;
860
861 before_dml(
862 p_action => 'UPDATE',
863 x_rowid => x_rowid,
867 x_person_id => x_person_id,
864 x_comment_id => x_comment_id,
865 x_comment_type_code => x_comment_type_code,
866 x_comment_txt => x_comment_txt,
868 x_course_cd => x_course_cd,
869 x_course_type => x_course_type,
870 x_award_cd => x_award_cd,
871 x_load_cal_type => x_load_cal_type,
872 x_load_ci_sequence_number => x_load_ci_sequence_number,
873 x_unit_set_cd => x_unit_set_cd,
874 x_us_version_number => x_us_version_number,
875 x_uoo_id => x_uoo_id,
876 x_creation_date => x_last_update_date,
877 x_created_by => x_last_updated_by,
878 x_last_update_date => x_last_update_date,
879 x_last_updated_by => x_last_updated_by,
880 x_last_update_login => x_last_update_login
881 );
882
883 UPDATE igs_as_stu_trn_cmts
884 SET
885 comment_type_code = new_references.comment_type_code,
886 comment_txt = new_references.comment_txt,
887 person_id = new_references.person_id,
888 course_cd = new_references.course_cd,
889 course_type = new_references.course_type,
890 award_cd = new_references.award_cd,
891 load_cal_type = new_references.load_cal_type,
892 load_ci_sequence_number = new_references.load_ci_sequence_number,
893 unit_set_cd = new_references.unit_set_cd,
894 us_version_number = new_references.us_version_number,
895 uoo_id = new_references.uoo_id,
896 last_update_date = x_last_update_date,
897 last_updated_by = x_last_updated_by,
898 last_update_login = x_last_update_login
899 WHERE rowid = x_rowid;
900
901 IF (SQL%NOTFOUND) THEN
902 RAISE NO_DATA_FOUND;
903 END IF;
904
905 l_rowid := NULL;
906
907 END update_row;
908
909
910 PROCEDURE add_row (
911 x_rowid IN OUT NOCOPY VARCHAR2,
912 x_comment_id IN OUT NOCOPY NUMBER,
913 x_comment_type_code IN VARCHAR2,
914 x_comment_txt IN VARCHAR2,
915 x_person_id IN NUMBER,
916 x_course_cd IN VARCHAR2,
917 x_course_type IN VARCHAR2,
918 x_award_cd IN VARCHAR2,
919 x_load_cal_type IN VARCHAR2,
920 x_load_ci_sequence_number IN NUMBER,
921 x_unit_set_cd IN VARCHAR2,
922 x_us_version_number IN NUMBER,
923 x_uoo_id IN NUMBER,
924 x_mode IN VARCHAR2
925 ) AS
926 /*
927 || Created By : [email protected]
928 || Created On : 22-SEP-2003
929 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
930 || Known limitations, enhancements or remarks :
931 || Change History :
932 || Who When What
933 || (reverse chronological order - newest change first)
934 */
935 CURSOR c1 IS
936 SELECT rowid
937 FROM igs_as_stu_trn_cmts
938 WHERE comment_id = x_comment_id;
939
940 BEGIN
941
942 OPEN c1;
943 FETCH c1 INTO x_rowid;
944 IF (c1%NOTFOUND) THEN
945 CLOSE c1;
946
947 insert_row (
948 x_rowid,
949 x_comment_id,
950 x_comment_type_code,
951 x_comment_txt,
952 x_person_id,
953 x_course_cd,
954 x_course_type,
955 x_award_cd,
956 x_load_cal_type,
957 x_load_ci_sequence_number,
958 x_unit_set_cd,
959 x_us_version_number,
960 x_uoo_id,
961 x_mode
962 );
963 RETURN;
964 END IF;
965 CLOSE c1;
966
967 update_row (
968 x_rowid,
969 x_comment_id,
970 x_comment_type_code,
971 x_comment_txt,
972 x_person_id,
973 x_course_cd,
974 x_course_type,
975 x_award_cd,
976 x_load_cal_type,
977 x_load_ci_sequence_number,
978 x_unit_set_cd,
979 x_us_version_number,
980 x_uoo_id,
981 x_mode
982 );
983
984 END add_row;
985
986
987 PROCEDURE delete_row (
988 x_rowid IN VARCHAR2
989 ) AS
990 /*
991 || Created By : [email protected]
992 || Created On : 22-SEP-2003
993 || Purpose : Handles the DELETE DML logic for the table.
994 || Known limitations, enhancements or remarks :
995 || Change History :
996 || Who When What
997 || (reverse chronological order - newest change first)
998 */
999 BEGIN
1000
1001 before_dml (
1002 p_action => 'DELETE',
1003 x_rowid => x_rowid
1004 );
1005
1006 DELETE FROM igs_as_stu_trn_cmts
1007 WHERE rowid = x_rowid;
1008
1009 IF (SQL%NOTFOUND) THEN
1010 RAISE NO_DATA_FOUND;
1011 END IF;
1012
1013 l_rowid := NULL;
1014
1015 END delete_row;
1016
1017
1018 END igs_as_stu_trn_cmts_pkg;
[Home] [Help]