[Home] [Help]
PACKAGE BODY: APPS.IGS_AZ_ADVISING_RELS_PKG
Source
1 PACKAGE BODY IGS_AZ_ADVISING_RELS_PKG AS
2 /* $Header: IGSHI04B.pls 115.2 2003/06/16 09:56:50 anilk noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_AZ_ADVISING_RELS%ROWTYPE;
6 new_references IGS_AZ_ADVISING_RELS%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_group_advising_rel_id IN NUMBER,
12 x_group_name IN VARCHAR2,
13 x_group_advisor_id IN NUMBER,
14 x_group_student_id IN NUMBER,
15 x_START_DATE IN DATE,
16 x_END_DATE IN DATE,
17 x_creation_date IN DATE,
18 x_created_by IN NUMBER,
19 x_last_update_date IN DATE,
20 x_last_updated_by IN NUMBER,
21 x_last_update_login IN NUMBER
22 ) AS
23 /*
24 || Created By : [email protected]
25 || Created On : 15-MAY-2003
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_AZ_ADVISING_RELS
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.group_advising_rel_id := x_group_advising_rel_id;
57 new_references.group_name := x_group_name;
58 new_references.group_advisor_id := x_group_advisor_id;
59 new_references.group_student_id := x_group_student_id;
60 new_references.START_DATE := TRUNC(x_START_DATE);
61 new_references.END_DATE := TRUNC(x_END_DATE);
62
63 IF (p_action = 'UPDATE') THEN
64 new_references.creation_date := old_references.creation_date;
65 new_references.created_by := old_references.created_by;
66 ELSE
67 new_references.creation_date := x_creation_date;
68 new_references.created_by := x_created_by;
69 END IF;
70
71 new_references.last_update_date := x_last_update_date;
72 new_references.last_updated_by := x_last_updated_by;
73 new_references.last_update_login := x_last_update_login;
74
75 END set_column_values;
76
77
78 PROCEDURE check_uniqueness AS
79 /*
80 || Created By : [email protected]
81 || Created On : 15-MAY-2003
82 || Purpose : Handles the Unique Constraint logic defined for the columns.
83 || Known limitations, enhancements or remarks :
84 || Change History :
85 || Who When What
86 || (reverse chronological order - newest change first)
87 */
88 BEGIN
89
90 IF ( get_uk_for_validation (
91 new_references.group_name,
92 new_references.group_advisor_id,
93 new_references.group_student_id
94 )
95 ) THEN
96 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
97 igs_ge_msg_stack.add;
98 app_exception.raise_exception;
99 END IF;
100
101 END check_uniqueness;
102
103
104 PROCEDURE check_parent_existance AS
105 /*
106 || Created By : [email protected]
107 || Created On : 15-MAY-2003
108 || Purpose : Checks for the existance of Parent records.
109 || Known limitations, enhancements or remarks :
110 || Change History :
111 || Who When What
112 || (reverse chronological order - newest change first)
113 */
114 BEGIN
115
116 IF (((old_references.group_name = new_references.group_name)) OR
117 ((new_references.group_name IS NULL))) THEN
118 NULL;
119 ELSIF NOT IGS_AZ_GROUPS_pkg.get_pk_for_validation (
120 new_references.group_name
121 ) THEN
122 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
123 igs_ge_msg_stack.add;
124 app_exception.raise_exception;
125 END IF;
126
127 IF (((old_references.group_student_id = new_references.group_student_id)) OR
128 ((new_references.group_student_id IS NULL))) THEN
129 NULL;
130 ELSIF NOT IGS_AZ_STUDENTS_pkg.get_pk_for_validation (
131 new_references.group_student_id
132 ) 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 IF (((old_references.group_advisor_id = new_references.group_advisor_id)) OR
139 ((new_references.group_advisor_id IS NULL))) THEN
140 NULL;
141 ELSIF NOT IGS_AZ_ADVISORS_pkg.get_pk_for_validation (
142 new_references.group_advisor_id
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 END check_parent_existance;
150
151
152 FUNCTION get_pk_for_validation (
153 x_group_advising_rel_id IN NUMBER
154 ) RETURN BOOLEAN AS
155 /*
156 || Created By : [email protected]
157 || Created On : 15-MAY-2003
158 || Purpose : Validates the Primary Key of the table.
159 || Known limitations, enhancements or remarks :
160 || Change History :
161 || Who When What
162 || (reverse chronological order - newest change first)
163 */
164 CURSOR cur_rowid IS
165 SELECT rowid
166 FROM IGS_AZ_ADVISING_RELS
167 WHERE group_advising_rel_id = x_group_advising_rel_id
168 FOR UPDATE NOWAIT;
169
170 lv_rowid cur_rowid%RowType;
171
172 BEGIN
173
174 OPEN cur_rowid;
175 FETCH cur_rowid INTO lv_rowid;
176 IF (cur_rowid%FOUND) THEN
177 CLOSE cur_rowid;
178 RETURN(TRUE);
179 ELSE
180 CLOSE cur_rowid;
181 RETURN(FALSE);
182 END IF;
183
184 END get_pk_for_validation;
185
186
187 FUNCTION get_uk_for_validation (
188 x_group_name IN VARCHAR2,
189 x_group_advisor_id IN NUMBER,
190 x_group_student_id IN NUMBER
191 ) RETURN BOOLEAN AS
192 /*
193 || Created By : [email protected]
194 || Created On : 15-MAY-2003
195 || Purpose : Validates the Unique Keys of the table.
196 || Known limitations, enhancements or remarks :
197 || Change History :
198 || Who When What
199 || (reverse chronological order - newest change first)
200 */
201 CURSOR cur_rowid IS
202 SELECT rowid
203 FROM IGS_AZ_ADVISING_RELS
204 WHERE group_name = x_group_name
205 AND group_advisor_id = x_group_advisor_id
206 AND group_student_id = x_group_student_id
207 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
208
209 lv_rowid cur_rowid%RowType;
210
211 BEGIN
212
213 OPEN cur_rowid;
214 FETCH cur_rowid INTO lv_rowid;
215 IF (cur_rowid%FOUND) THEN
216 CLOSE cur_rowid;
217 RETURN (true);
218 ELSE
219 CLOSE cur_rowid;
220 RETURN(FALSE);
221 END IF;
222
223 END get_uk_for_validation ;
224
225
226 PROCEDURE get_fk_IGS_AZ_GROUPS (
227 x_group_name IN VARCHAR2
228 ) AS
229 /*
230 || Created By : [email protected]
231 || Created On : 15-MAY-2003
232 || Purpose : Validates the Foreign Keys for the table.
233 || Known limitations, enhancements or remarks :
234 || Change History :
235 || Who When What
236 || (reverse chronological order - newest change first)
237 */
238 CURSOR cur_rowid IS
239 SELECT rowid
240 FROM IGS_AZ_ADVISING_RELS
241 WHERE ((group_name = x_group_name));
242
243 lv_rowid cur_rowid%RowType;
244
245 BEGIN
246
247 OPEN cur_rowid;
248 FETCH cur_rowid INTO lv_rowid;
249 IF (cur_rowid%FOUND) THEN
250 CLOSE cur_rowid;
251 fnd_message.set_name ('IGS', 'IGS_FOREIGN_KEY_REFERENCE');
252 igs_ge_msg_stack.add;
253 app_exception.raise_exception;
254 RETURN;
255 END IF;
256 CLOSE cur_rowid;
257
258 END get_fk_IGS_AZ_GROUPS;
259
260
261 PROCEDURE get_fk_IGS_AZ_STUDENTS (
262 x_group_student_id IN NUMBER
263 ) AS
264 /*
265 || Created By : [email protected]
266 || Created On : 15-MAY-2003
267 || Purpose : Validates the Foreign Keys for the table.
268 || Known limitations, enhancements or remarks :
269 || Change History :
270 || Who When What
271 || (reverse chronological order - newest change first)
272 */
273 CURSOR cur_rowid IS
274 SELECT rowid
275 FROM IGS_AZ_ADVISING_RELS
276 WHERE ((group_student_id = x_group_student_id));
277
278 lv_rowid cur_rowid%RowType;
279
280 BEGIN
281
282 OPEN cur_rowid;
283 FETCH cur_rowid INTO lv_rowid;
284 IF (cur_rowid%FOUND) THEN
285 CLOSE cur_rowid;
286 fnd_message.set_name ('IGS', 'IGS_FOREIGN_KEY_REFERENCE');
287 igs_ge_msg_stack.add;
288 app_exception.raise_exception;
289 RETURN;
290 END IF;
291 CLOSE cur_rowid;
292
293 END get_fk_IGS_AZ_STUDENTS;
294
295
296 PROCEDURE get_fk_IGS_AZ_ADVISORS (
297 x_group_advisor_id IN NUMBER
298 ) AS
299 /*
300 || Created By : [email protected]
301 || Created On : 15-MAY-2003
302 || Purpose : Validates the Foreign Keys for the table.
303 || Known limitations, enhancements or remarks :
304 || Change History :
305 || Who When What
306 || (reverse chronological order - newest change first)
307 */
308 CURSOR cur_rowid IS
309 SELECT rowid
310 FROM IGS_AZ_ADVISING_RELS
311 WHERE ((group_advisor_id = x_group_advisor_id));
312
313 lv_rowid cur_rowid%RowType;
314
315 BEGIN
316
317 OPEN cur_rowid;
318 FETCH cur_rowid INTO lv_rowid;
319 IF (cur_rowid%FOUND) THEN
320 CLOSE cur_rowid;
321 fnd_message.set_name ('IGS', 'IGS_FOREIGN_KEY_REFERENCE');
322 igs_ge_msg_stack.add;
323 app_exception.raise_exception;
324 RETURN;
325 END IF;
326 CLOSE cur_rowid;
327
328 END get_fk_IGS_AZ_ADVISORS;
329
330
331 PROCEDURE before_dml (
332 p_action IN VARCHAR2,
333 x_rowid IN VARCHAR2,
334 x_group_advising_rel_id IN NUMBER,
335 x_group_name IN VARCHAR2,
336 x_group_advisor_id IN NUMBER,
337 x_group_student_id IN NUMBER,
338 x_START_DATE IN DATE,
339 x_END_DATE IN DATE,
340 x_creation_date IN DATE,
341 x_created_by IN NUMBER,
342 x_last_update_date IN DATE,
343 x_last_updated_by IN NUMBER,
344 x_last_update_login IN NUMBER
345 ) AS
346 /*
347 || Created By : [email protected]
348 || Created On : 15-MAY-2003
349 || Purpose : Initialises the columns, Checks Constraints, Calls the
350 || Trigger Handlers for the table, before any DML operation.
351 || Known limitations, enhancements or remarks :
352 || Change History :
353 || Who When What
354 || (reverse chronological order - newest change first)
355 */
356 BEGIN
357
358 set_column_values (
359 p_action,
360 x_rowid,
361 x_group_advising_rel_id,
362 x_group_name,
363 x_group_advisor_id,
364 x_group_student_id,
365 x_START_DATE,
366 x_END_DATE,
367 x_creation_date,
368 x_created_by,
369 x_last_update_date,
370 x_last_updated_by,
371 x_last_update_login
372 );
373
374 IF (p_action = 'INSERT') THEN
375 -- Call all the procedures related to Before Insert.
376 IF ( get_pk_for_validation(
377 new_references.group_advising_rel_id
378 )
379 ) THEN
380 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
381 igs_ge_msg_stack.add;
382 app_exception.raise_exception;
383 END IF;
384 check_uniqueness;
385 check_parent_existance;
386 ELSIF (p_action = 'UPDATE') THEN
387 -- Call all the procedures related to Before Update.
388 check_uniqueness;
389 check_parent_existance;
390 ELSIF (p_action = 'VALIDATE_INSERT') THEN
391 -- Call all the procedures related to Before Insert.
392 IF ( get_pk_for_validation (
393 new_references.group_advising_rel_id
394 )
395 ) THEN
396 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
397 igs_ge_msg_stack.add;
398 app_exception.raise_exception;
399 END IF;
400 check_uniqueness;
401 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
402 check_uniqueness;
403 END IF;
404
405 END before_dml;
406
407
408 PROCEDURE insert_row (
409 x_rowid IN OUT NOCOPY VARCHAR2,
410 x_group_advising_rel_id IN OUT NOCOPY NUMBER,
411 x_group_name IN VARCHAR2,
412 x_group_advisor_id IN NUMBER,
413 x_group_student_id IN NUMBER,
414 x_START_DATE IN DATE,
415 x_END_DATE IN DATE,
416 x_mode IN VARCHAR2,
417 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
418 X_MSG_DATA OUT NOCOPY VARCHAR2,
419 X_MSG_COUNT OUT NOCOPY NUMBER
420 ) AS
421 /*
422 || Created By : [email protected]
423 || Created On : 15-MAY-2003
427 || Who When What
424 || Purpose : Handles the INSERT DML logic for the table.
425 || Known limitations, enhancements or remarks :
426 || Change History :
428 || (reverse chronological order - newest change first)
429 */
430
431 x_last_update_date DATE;
432 x_last_updated_by NUMBER;
433 x_last_update_login NUMBER;
434
435 BEGIN
436 FND_MSG_PUB.initialize;
437 x_last_update_date := SYSDATE;
438 IF (x_mode = 'I') THEN
439 x_last_updated_by := 1;
440 x_last_update_login := 0;
441 ELSIF (x_mode = 'R') THEN
442 x_last_updated_by := fnd_global.user_id;
443 IF (x_last_updated_by IS NULL) THEN
444 x_last_updated_by := -1;
445 END IF;
446 x_last_update_login := fnd_global.login_id;
447 IF (x_last_update_login IS NULL) THEN
448 x_last_update_login := -1;
449 END IF;
450 ELSE
451 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
452 fnd_message.set_token ('ROUTINE', 'IGS_AZ_ADVISING_RELS_PKG.INSERT_ROW');
453 igs_ge_msg_stack.add;
454 app_exception.raise_exception;
455 END IF;
456
457 x_group_advising_rel_id := NULL;
458
459 before_dml(
460 p_action => 'INSERT',
461 x_rowid => x_rowid,
462 x_group_advising_rel_id => x_group_advising_rel_id,
463 x_group_name => x_group_name,
464 x_group_advisor_id => x_group_advisor_id,
465 x_group_student_id => x_group_student_id,
466 x_START_DATE => x_START_DATE,
467 x_END_DATE => x_END_DATE,
468 x_creation_date => x_last_update_date,
469 x_created_by => x_last_updated_by,
470 x_last_update_date => x_last_update_date,
471 x_last_updated_by => x_last_updated_by,
472 x_last_update_login => x_last_update_login
473 );
474
475 INSERT INTO IGS_AZ_ADVISING_RELS (
476 group_advising_rel_id,
477 group_name,
478 group_advisor_id,
479 group_student_id,
480 START_DATE,
481 END_DATE,
482 creation_date,
483 created_by,
484 last_update_date,
485 last_updated_by,
486 last_update_login
487 ) VALUES (
488 IGS_AZ_ADVISING_RELS_s.NEXTVAL,
489 new_references.group_name,
490 new_references.group_advisor_id,
491 new_references.group_student_id,
492 TRUNC(new_references.START_DATE),
493 TRUNC(new_references.END_DATE),
494 x_last_update_date,
495 x_last_updated_by,
496 x_last_update_date,
497 x_last_updated_by,
498 x_last_update_login
499 ) RETURNING ROWID, group_advising_rel_id INTO x_rowid, x_group_advising_rel_id;
500 -- Standard call to get message count and if count is 1, get message
501 -- info.
502 FND_MSG_PUB.Count_And_Get(
503 p_encoded => FND_API.G_FALSE,
504 p_count => x_MSG_COUNT,
505 p_data => X_MSG_DATA);
506 EXCEPTION
507 WHEN FND_API.G_EXC_ERROR THEN
508 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
509 FND_MSG_PUB.Count_And_Get(
510 p_encoded => FND_API.G_FALSE,
511 p_count => x_MSG_COUNT,
512 p_data => X_MSG_DATA);
513 RETURN;
514 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
515 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
516 FND_MSG_PUB.Count_And_Get(
517 p_encoded => FND_API.G_FALSE,
518 p_count => x_MSG_COUNT,
519 p_data => X_MSG_DATA);
520 RETURN;
521 WHEN OTHERS THEN
522 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
523 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
524 FND_MESSAGE.SET_TOKEN('NAME','Insert_Row : '||SQLERRM);
525 FND_MSG_PUB.ADD;
526 FND_MSG_PUB.Count_And_Get(
527 p_encoded => FND_API.G_FALSE,
528 p_count => x_MSG_COUNT,
529 p_data => X_MSG_DATA);
530 RETURN;
531
532 END insert_row;
533
534
535 PROCEDURE lock_row (
536 x_rowid IN VARCHAR2,
537 x_group_advising_rel_id IN NUMBER,
538 x_group_name IN VARCHAR2,
539 x_group_advisor_id IN NUMBER,
540 x_group_student_id IN NUMBER,
541 x_START_DATE IN DATE,
542 x_END_DATE IN DATE,
543 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
544 X_MSG_DATA OUT NOCOPY VARCHAR2,
545 X_MSG_COUNT OUT NOCOPY NUMBER
546 ) AS
547 /*
548 || Created By : [email protected]
549 || Created On : 15-MAY-2003
550 || Purpose : Handles the LOCK mechanism for the table.
551 || Known limitations, enhancements or remarks :
552 || Change History :
553 || Who When What
554 || (reverse chronological order - newest change first)
555 */
556 CURSOR c1 IS
557 SELECT
558 group_name,
559 group_advisor_id,
560 group_student_id,
561 START_DATE,
562 END_DATE
563 FROM IGS_AZ_ADVISING_RELS
564 WHERE rowid = x_rowid
568
565 FOR UPDATE NOWAIT;
566
567 tlinfo c1%ROWTYPE;
569 BEGIN
570 FND_MSG_PUB.initialize;
571 OPEN c1;
572 FETCH c1 INTO tlinfo;
573 IF (c1%notfound) THEN
574 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
575 igs_ge_msg_stack.add;
576 CLOSE c1;
577 app_exception.raise_exception;
578 RETURN;
579 END IF;
580 CLOSE c1;
581
582 IF (
583 (tlinfo.group_name = x_group_name)
584 AND (tlinfo.group_advisor_id = x_group_advisor_id)
585 AND (tlinfo.group_student_id = x_group_student_id)
586 AND ((TRUNC(tlinfo.START_DATE) = TRUNC(x_START_DATE)) OR ((tlinfo.START_DATE IS NULL) AND (X_START_DATE IS NULL)))
587 AND ((TRUNC(tlinfo.END_DATE) = TRUNC(x_END_DATE)) OR ((tlinfo.END_DATE IS NULL) AND (X_END_DATE IS NULL)))
588 ) THEN
589 NULL;
590 ELSE
591 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
592 igs_ge_msg_stack.add;
593 app_exception.raise_exception;
594 END IF;
595 -- Initialize API return status to success.
596 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
597 -- Standard call to get message count and if count is 1, get message
598 -- info.
599 FND_MSG_PUB.Count_And_Get(
600 p_encoded => FND_API.G_FALSE,
601 p_count => x_MSG_COUNT,
602 p_data => X_MSG_DATA);
603 RETURN;
604 EXCEPTION
605 WHEN FND_API.G_EXC_ERROR THEN
606 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
607 FND_MSG_PUB.Count_And_Get(
608 p_encoded => FND_API.G_FALSE,
609 p_count => x_MSG_COUNT,
610 p_data => X_MSG_DATA);
611 RETURN;
612 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
613 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
614 FND_MSG_PUB.Count_And_Get(
615 p_encoded => FND_API.G_FALSE,
616 p_count => x_MSG_COUNT,
617 p_data => X_MSG_DATA);
618 RETURN;
619 WHEN OTHERS THEN
620 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
621 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
622 FND_MESSAGE.SET_TOKEN('NAME','Insert_Row : '||SQLERRM);
623 FND_MSG_PUB.ADD;
624 FND_MSG_PUB.Count_And_Get(
625 p_encoded => FND_API.G_FALSE,
626 p_count => x_MSG_COUNT,
627 p_data => X_MSG_DATA);
628 RETURN;
629
630 END lock_row;
631
632
633 PROCEDURE update_row (
634 x_rowid IN VARCHAR2,
635 x_group_advising_rel_id IN NUMBER,
636 x_group_name IN VARCHAR2,
637 x_group_advisor_id IN NUMBER,
638 x_group_student_id IN NUMBER,
639 x_START_DATE IN DATE,
640 x_END_DATE IN DATE,
641 x_mode IN VARCHAR2,
642 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
643 X_MSG_DATA OUT NOCOPY VARCHAR2,
644 X_MSG_COUNT OUT NOCOPY NUMBER
645 ) AS
646 /*
647 || Created By : [email protected]
648 || Created On : 15-MAY-2003
649 || Purpose : Handles the UPDATE DML logic for the table.
650 || Known limitations, enhancements or remarks :
651 || Change History :
652 || Who When What
653 || (reverse chronological order - newest change first)
654 */
655 x_last_update_date DATE ;
656 x_last_updated_by NUMBER;
657 x_last_update_login NUMBER;
658
659
660 BEGIN
661 FND_MSG_PUB.initialize;
662 x_last_update_date := SYSDATE;
663 IF (X_MODE = 'I') THEN
664 x_last_updated_by := 1;
665 x_last_update_login := 0;
666 ELSIF (x_mode = 'R') THEN
667 x_last_updated_by := fnd_global.user_id;
668 IF x_last_updated_by IS NULL THEN
669 x_last_updated_by := -1;
670 END IF;
671 x_last_update_login := fnd_global.login_id;
672 IF (x_last_update_login IS NULL) THEN
673 x_last_update_login := -1;
674 END IF;
675 ELSE
676 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
677 fnd_message.set_token ('ROUTINE', 'IGS_AZ_ADVISING_RELS_PKG.UPDATE_ROW');
678 igs_ge_msg_stack.add;
679 app_exception.raise_exception;
680 END IF;
681
682 -- x_group_advising_rel_id := NULL;
683
684 before_dml(
685 p_action => 'UPDATE',
686 x_rowid => x_rowid,
687 x_group_advising_rel_id => X_group_advising_rel_id,
688 x_group_name => x_group_name,
689 x_group_advisor_id => x_group_advisor_id,
690 x_group_student_id => x_group_student_id,
691 x_START_DATE => x_START_DATE,
692 x_END_DATE => x_END_DATE,
693 x_creation_date => x_last_update_date,
694 x_created_by => x_last_updated_by,
695 x_last_update_date => x_last_update_date,
696 x_last_updated_by => x_last_updated_by,
697 x_last_update_login => x_last_update_login
698 );
699
700 --
701 -- The logic that if the user has de selected a record from being matched, the the row should be deleted from the database.
702 -- Normally when a record is created for adding the suggested matches, it will be created with start date as null and if..
703 -- and the null start date records will be displayed to the users for suggested matches. If the user sdeselects them
704 -- frombeing accepted then delete the record. these may be again created with null start date when job is run.
705
706 --IF new_references.START_DATE IS NULL THEN
707 -- DELETE_ROW(x_rowid, X_RETURN_STATUS, X_MSG_DATA, X_MSG_COUNT);
708 --ELSE
709
710 UPDATE IGS_AZ_ADVISING_RELS
711 SET
712 group_name = new_references.group_name,
713 group_advisor_id = new_references.group_advisor_id,
714 group_student_id = new_references.group_student_id,
715 START_DATE = TRUNC(new_references.START_DATE),
716 END_DATE = TRUNC(new_references.END_DATE),
717 last_update_date = x_last_update_date,
718 last_updated_by = x_last_updated_by,
719 last_update_login = x_last_update_login
720 WHERE rowid = x_rowid;
721 --END IF;
722
723 IF (SQL%NOTFOUND) THEN
724 RAISE NO_DATA_FOUND;
725 END IF;
726 -- Initialize API return status to success.
727 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
728 -- Standard call to get message count and if count is 1, get message
729 -- info.
730 FND_MSG_PUB.Count_And_Get(
731 p_encoded => FND_API.G_FALSE,
732 p_count => x_MSG_COUNT,
733 p_data => X_MSG_DATA);
734 EXCEPTION
735 WHEN FND_API.G_EXC_ERROR THEN
736 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
737 FND_MSG_PUB.Count_And_Get(
738 p_encoded => FND_API.G_FALSE,
739 p_count => x_MSG_COUNT,
740 p_data => X_MSG_DATA);
741 RETURN;
742 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
743 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
744 FND_MSG_PUB.Count_And_Get(
745 p_encoded => FND_API.G_FALSE,
746 p_count => x_MSG_COUNT,
747 p_data => X_MSG_DATA);
748 RETURN;
749 WHEN OTHERS THEN
750 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
751 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
752 FND_MESSAGE.SET_TOKEN('NAME','Update_Row : '||SQLERRM);
753 FND_MSG_PUB.ADD;
754 FND_MSG_PUB.Count_And_Get(
755 p_encoded => FND_API.G_FALSE,
756 p_count => x_MSG_COUNT,
757 p_data => X_MSG_DATA);
758 RETURN;
759 END update_row;
760
761
762 PROCEDURE add_row (
763 x_rowid IN OUT NOCOPY VARCHAR2,
764 x_group_advising_rel_id IN OUT NOCOPY NUMBER,
765 x_group_name IN VARCHAR2,
766 x_group_advisor_id IN NUMBER,
767 x_group_student_id IN NUMBER,
768 x_START_DATE IN DATE,
769 x_END_DATE IN DATE,
770 x_mode IN VARCHAR2
771 ) AS
772 /*
773 || Created By : [email protected]
774 || Created On : 15-MAY-2003
775 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
776 || Known limitations, enhancements or remarks :
777 || Change History :
778 || Who When What
779 || (reverse chronological order - newest change first)
780 */
781 L_RETURN_STATUS VARCHAR2(10);
782 L_MSG_DATA VARCHAR2(2000);
783 L_MSG_COUNT NUMBER(10);
784
785 CURSOR c1 IS
786 SELECT rowid
787 FROM IGS_AZ_ADVISING_RELS
788 WHERE group_advising_rel_id = x_group_advising_rel_id;
789
790 BEGIN
791
792 OPEN c1;
793 FETCH c1 INTO x_rowid;
794 IF (c1%NOTFOUND) THEN
795 CLOSE c1;
796
797 insert_row (
798 x_rowid,
799 x_group_advising_rel_id,
800 x_group_name,
801 x_group_advisor_id,
802 x_group_student_id,
803 x_START_DATE,
804 x_END_DATE,
805 x_mode,
806 L_RETURN_STATUS,
807 L_MSG_DATA,
808 L_MSG_COUNT
809
810 );
811 RETURN;
812 END IF;
813 CLOSE c1;
814
815 update_row (
816 x_rowid,
817 x_group_advising_rel_id,
818 x_group_name,
819 x_group_advisor_id,
820 x_group_student_id,
821 x_START_DATE,
822 x_END_DATE,
823 x_mode,
824 L_RETURN_STATUS,
825 L_MSG_DATA,
826 L_MSG_COUNT
827 );
828
829 END add_row;
830
831
832 PROCEDURE delete_row (
833 x_rowid IN VARCHAR2,
834 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
835 X_MSG_DATA OUT NOCOPY VARCHAR2,
836 X_MSG_COUNT OUT NOCOPY NUMBER
837 ) AS
838 /*
839 || Created By : [email protected]
840 || Created On : 15-MAY-2003
841 || Purpose : Handles the DELETE DML logic for the table.
842 || Known limitations, enhancements or remarks :
843 || Change History :
844 || Who When What
845 || (reverse chronological order - newest change first)
846 */
847 BEGIN
848 FND_MSG_PUB.initialize;
849 before_dml (
850 p_action => 'DELETE',
851 x_rowid => x_rowid
852 );
853
854 DELETE FROM IGS_AZ_ADVISING_RELS
855 WHERE rowid = x_rowid;
856
857 IF (SQL%NOTFOUND) THEN
858 RAISE NO_DATA_FOUND;
859 END IF;
860 -- Initialize API return status to success.
861 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
862 -- Standard call to get message count and if count is 1, get message
863 -- info.
864 FND_MSG_PUB.Count_And_Get(
865 p_encoded => FND_API.G_FALSE,
866 p_count => x_MSG_COUNT,
867 p_data => X_MSG_DATA);
868 EXCEPTION
869 WHEN FND_API.G_EXC_ERROR THEN
870 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
871 FND_MSG_PUB.Count_And_Get(
872 p_encoded => FND_API.G_FALSE,
873 p_count => x_MSG_COUNT,
874 p_data => X_MSG_DATA);
875 RETURN;
876 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
877 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
878 FND_MSG_PUB.Count_And_Get(
879 p_encoded => FND_API.G_FALSE,
880 p_count => x_MSG_COUNT,
881 p_data => X_MSG_DATA);
882 RETURN;
883 WHEN OTHERS THEN
884 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
885 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
886 FND_MESSAGE.SET_TOKEN('NAME','Insert_Row : '||SQLERRM);
887 FND_MSG_PUB.ADD;
888 FND_MSG_PUB.Count_And_Get(
889 p_encoded => FND_API.G_FALSE,
890 p_count => x_MSG_COUNT,
891 p_data => X_MSG_DATA);
892 RETURN;
893 END delete_row;
894
895
896 END IGS_AZ_ADVISING_RELS_pkg;