[Home] [Help]
PACKAGE BODY: APPS.IGS_AZ_GROUPS_PKG
Source
1 PACKAGE BODY igs_az_groups_pkg AS
2 /* $Header: IGSHI01B.pls 115.6 2003/10/30 13:29:08 rghosh noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_az_groups%ROWTYPE;
6 new_references igs_az_groups%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_group_name IN VARCHAR2,
12 x_group_desc IN VARCHAR2,
13 x_advising_code IN VARCHAR2,
14 x_resp_org_unit_cd IN VARCHAR2,
15 x_resp_person_id IN NUMBER,
16 x_location_cd IN VARCHAR2,
17 x_delivery_method_code IN VARCHAR2,
18 x_advisor_group_id IN NUMBER,
19 x_student_group_id IN NUMBER,
20 x_default_advisor_load_num IN NUMBER,
21 x_mandatory_flag IN VARCHAR2,
22 x_advising_sessions_num IN NUMBER,
23 x_advising_hold_type IN VARCHAR2,
24 x_closed_flag IN VARCHAR2,
25 x_comments_txt IN VARCHAR2,
26 x_auto_refresh_flag IN VARCHAR2,
27 x_last_auto_refresh_date IN DATE,
28 x_auto_stdnt_add_flag IN VARCHAR2,
29 x_auto_stdnt_remove_flag IN VARCHAR2,
30 x_auto_advisor_add_flag IN VARCHAR2,
31 x_auto_advisor_remove_flag IN VARCHAR2,
32 x_auto_match_flag IN VARCHAR2,
33 x_auto_apply_hold_flag IN VARCHAR2,
34 x_attribute_category IN VARCHAR2,
35 x_attribute1 IN VARCHAR2,
36 x_attribute2 IN VARCHAR2,
37 x_attribute3 IN VARCHAR2,
38 x_attribute4 IN VARCHAR2,
39 x_attribute5 IN VARCHAR2,
40 x_attribute6 IN VARCHAR2,
41 x_attribute7 IN VARCHAR2,
42 x_attribute8 IN VARCHAR2,
43 x_attribute9 IN VARCHAR2,
44 x_attribute10 IN VARCHAR2,
45 x_attribute11 IN VARCHAR2,
46 x_attribute12 IN VARCHAR2,
47 x_attribute13 IN VARCHAR2,
48 x_attribute14 IN VARCHAR2,
49 x_attribute15 IN VARCHAR2,
50 x_attribute16 IN VARCHAR2,
51 x_attribute17 IN VARCHAR2,
52 x_attribute18 IN VARCHAR2,
53 x_attribute19 IN VARCHAR2,
54 x_attribute20 IN VARCHAR2,
55 x_creation_date IN DATE,
56 x_created_by IN NUMBER,
57 x_last_update_date IN DATE,
58 x_last_updated_by IN NUMBER,
59 x_last_update_login IN NUMBER
60 ) AS
61 /*
62 || Created By : [email protected]
63 || Created On : 15-MAY-2003
64 || Purpose : Initialises the Old and New references for the columns of the table.
65 || Known limitations, enhancements or remarks :
66 || Change History :
67 || Who When What
68 || (reverse chronological order - newest change first)
69 */
70
71 CURSOR cur_old_ref_values IS
72 SELECT *
73 FROM igs_az_groups
74 WHERE rowid = x_rowid;
75
76 BEGIN
77
78 l_rowid := x_rowid;
79
80 -- Code for setting the Old and New Reference Values.
81 -- Populate Old Values.
82 OPEN cur_old_ref_values;
83 FETCH cur_old_ref_values INTO old_references;
84 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
85 CLOSE cur_old_ref_values;
86 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
87 igs_ge_msg_stack.add;
88 app_exception.raise_exception;
89 RETURN;
90 END IF;
91 CLOSE cur_old_ref_values;
92
93 -- Populate New Values.
94 new_references.group_name := x_group_name;
95 new_references.group_desc := x_group_desc;
96 new_references.advising_code := x_advising_code;
97 new_references.resp_org_unit_cd := x_resp_org_unit_cd;
98 new_references.resp_person_id := x_resp_person_id;
99 new_references.location_cd := x_location_cd;
100 new_references.delivery_method_code := x_delivery_method_code;
101 new_references.advisor_group_id := x_advisor_group_id;
102 new_references.student_group_id := x_student_group_id;
103 new_references.default_advisor_load_num := x_default_advisor_load_num;
104 new_references.mandatory_flag := x_mandatory_flag;
105 new_references.advising_sessions_num := x_advising_sessions_num;
106 new_references.advising_hold_type := x_advising_hold_type;
107 new_references.closed_flag := x_closed_flag;
108 new_references.comments_txt := x_comments_txt;
109 new_references.auto_refresh_flag := x_auto_refresh_flag;
110 new_references.last_auto_refresh_date := TRUNC(x_last_auto_refresh_date);
111 new_references.auto_stdnt_add_flag := x_auto_stdnt_add_flag;
112 new_references.auto_stdnt_remove_flag := x_auto_stdnt_remove_flag;
113 new_references.auto_advisor_add_flag := x_auto_advisor_add_flag;
114 new_references.auto_advisor_remove_flag := x_auto_advisor_remove_flag;
115 new_references.auto_match_flag := x_auto_match_flag;
116 new_references.auto_apply_hold_flag := x_auto_apply_hold_flag;
117 new_references.attribute_category := x_attribute_category;
118 new_references.attribute1 := x_attribute1;
119 new_references.attribute2 := x_attribute2;
120 new_references.attribute3 := x_attribute3;
121 new_references.attribute4 := x_attribute4;
122 new_references.attribute5 := x_attribute5;
123 new_references.attribute6 := x_attribute6;
124 new_references.attribute7 := x_attribute7;
125 new_references.attribute8 := x_attribute8;
126 new_references.attribute9 := x_attribute9;
127 new_references.attribute10 := x_attribute10;
128 new_references.attribute11 := x_attribute11;
129 new_references.attribute12 := x_attribute12;
130 new_references.attribute13 := x_attribute13;
131 new_references.attribute14 := x_attribute14;
132 new_references.attribute15 := x_attribute15;
133 new_references.attribute16 := x_attribute16;
134 new_references.attribute17 := x_attribute17;
135 new_references.attribute18 := x_attribute18;
136 new_references.attribute19 := x_attribute19;
137 new_references.attribute20 := x_attribute20;
138
139 IF (p_action = 'UPDATE') THEN
140 new_references.creation_date := old_references.creation_date;
141 new_references.created_by := old_references.created_by;
142 ELSE
143 new_references.creation_date := x_creation_date;
144 new_references.created_by := x_created_by;
145 END IF;
146
147 new_references.last_update_date := x_last_update_date;
148 new_references.last_updated_by := x_last_updated_by;
149 new_references.last_update_login := x_last_update_login;
150
151 END set_column_values;
152
153
154 PROCEDURE check_parent_existance AS
155 /*
156 || Created By : [email protected]
157 || Created On : 15-MAY-2003
158 || Purpose : Checks for the existance of Parent records.
159 || Known limitations, enhancements or remarks :
160 || Change History :
161 || Who When What
162 || (reverse chronological order - newest change first)
163 */
164 BEGIN
165
166 IF (((old_references.location_cd = new_references.location_cd)) OR
167 ((new_references.location_cd IS NULL))) THEN
168 NULL;
169 ELSIF NOT igs_ad_location_pkg.get_pk_for_validation (
170 new_references.location_cd,
171 'N'
172 ) THEN
173 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
174 igs_ge_msg_stack.add;
175 app_exception.raise_exception;
176 END IF;
177
178 IF (((old_references.advisor_group_id = new_references.advisor_group_id)) OR
179 ((new_references.advisor_group_id IS NULL))) THEN
180 NULL;
181 ELSIF NOT igs_pe_persid_group_pkg.get_pk_for_validation (
182 new_references.advisor_group_id
183 ) THEN
184 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
185 igs_ge_msg_stack.add;
186 app_exception.raise_exception;
187 END IF;
188
189 IF (((old_references.student_group_id = new_references.student_group_id)) OR
190 ((new_references.student_group_id IS NULL))) THEN
191 NULL;
192 ELSIF NOT igs_pe_persid_group_pkg.get_pk_for_validation (
193 new_references.student_group_id
194 ) THEN
195 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
196 igs_ge_msg_stack.add;
197 app_exception.raise_exception;
198 END IF;
199
200 IF (((old_references.advising_hold_type = new_references.advising_hold_type)) OR
201 ((new_references.advising_hold_type IS NULL))) THEN
202 NULL;
203 ELSIF NOT igs_fi_encmb_type_pkg.get_pk_for_validation (
204 new_references.advising_hold_type
205 ) THEN
206 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
207 igs_ge_msg_stack.add;
208 app_exception.raise_exception;
209 END IF;
210
211 END check_parent_existance;
212
213
214 PROCEDURE check_child_existance AS
215 /*
216 || Created By : [email protected]
217 || Created On : 15-MAY-2003
218 || Purpose : Checks for the existance of Child records.
219 || Known limitations, enhancements or remarks :
220 || Change History :
221 || Who When What
222 || (reverse chronological order - newest change first)
223 */
224 BEGIN
225
226 IGS_AZ_ADVISING_RELS_pkg.get_fk_igs_az_groups (
227 old_references.group_name
228 );
229
230 IGS_AZ_ADVISORS_pkg.get_fk_igs_az_groups (
231 old_references.group_name
232 );
233
234 IGS_AZ_STUDENTS_pkg.get_fk_igs_az_groups (
235 old_references.group_name
236 );
237
238 END check_child_existance;
239
240
241 FUNCTION get_pk_for_validation (
242 x_group_name IN VARCHAR2
243 ) RETURN BOOLEAN AS
244 /*
245 || Created By : [email protected]
246 || Created On : 15-MAY-2003
247 || Purpose : Validates the Primary Key of the table.
248 || Known limitations, enhancements or remarks :
249 || Change History :
250 || Who When What
251 || (reverse chronological order - newest change first)
252 */
253 CURSOR cur_rowid IS
254 SELECT rowid
255 FROM igs_az_groups
256 WHERE group_name = x_group_name;
257
258 lv_rowid cur_rowid%RowType;
259
260 BEGIN
261
262 OPEN cur_rowid;
263 FETCH cur_rowid INTO lv_rowid;
264 IF (cur_rowid%FOUND) THEN
265 CLOSE cur_rowid;
266 RETURN(TRUE);
267 ELSE
268 CLOSE cur_rowid;
269 RETURN(FALSE);
270 END IF;
271
272 END get_pk_for_validation;
273
274
275 PROCEDURE get_fk_igs_ad_location (
276 x_location_cd IN VARCHAR2
277 ) AS
278 /*
279 || Created By : [email protected]
280 || Created On : 15-MAY-2003
281 || Purpose : Validates the Foreign Keys for the table.
282 || Known limitations, enhancements or remarks :
283 || Change History :
284 || Who When What
285 || (reverse chronological order - newest change first)
286 */
287 CURSOR cur_rowid IS
288 SELECT rowid
289 FROM igs_az_groups
290 WHERE ((location_cd = x_location_cd));
291
292 lv_rowid cur_rowid%RowType;
293
294 BEGIN
295
296 OPEN cur_rowid;
297 FETCH cur_rowid INTO lv_rowid;
298 IF (cur_rowid%FOUND) THEN
299 CLOSE cur_rowid;
300 fnd_message.set_name ('IGS', 'IGS_AZ_GROUPS_LOC_FK');
301 igs_ge_msg_stack.add;
302 app_exception.raise_exception;
303 RETURN;
304 END IF;
305 CLOSE cur_rowid;
306
307 END get_fk_igs_ad_location;
308
309
310 PROCEDURE get_fk_igs_pe_persid_group (
311 x_group_id IN NUMBER
312 ) AS
313 /*
314 || Created By : [email protected]
315 || Created On : 15-MAY-2003
316 || Purpose : Validates the Foreign Keys for the table.
317 || Known limitations, enhancements or remarks :
318 || Change History :
319 || Who When What
320 || (reverse chronological order - newest change first)
321 */
322 CURSOR cur_rowid IS
323 SELECT rowid
324 FROM igs_az_groups
325 WHERE ((advisor_group_id = x_group_id))
326 OR ((student_group_id = x_group_id));
327
328 lv_rowid cur_rowid%RowType;
329
330 BEGIN
331
332 OPEN cur_rowid;
333 FETCH cur_rowid INTO lv_rowid;
334 IF (cur_rowid%FOUND) THEN
335 CLOSE cur_rowid;
336 fnd_message.set_name ('IGS', 'IGS_AZ_GROUPS_PERS_FK');
337 igs_ge_msg_stack.add;
338 app_exception.raise_exception;
339 RETURN;
340 END IF;
341 CLOSE cur_rowid;
342
343 END get_fk_igs_pe_persid_group;
344
345
346 PROCEDURE get_fk_igs_fi_encmb_type (
347 x_encumbrance_type IN VARCHAR2
348 ) AS
349 /*
350 || Created By : [email protected]
351 || Created On : 15-MAY-2003
352 || Purpose : Validates the Foreign Keys for the table.
353 || Known limitations, enhancements or remarks :
354 || Change History :
355 || Who When What
356 || (reverse chronological order - newest change first)
357 */
358 CURSOR cur_rowid IS
359 SELECT rowid
360 FROM igs_az_groups
361 WHERE ((advising_hold_type = x_encumbrance_type));
362
363 lv_rowid cur_rowid%RowType;
364
365 BEGIN
366
367 OPEN cur_rowid;
368 FETCH cur_rowid INTO lv_rowid;
369 IF (cur_rowid%FOUND) THEN
370 CLOSE cur_rowid;
371 fnd_message.set_name ('IGS', 'IGS_AZ_GROUPS_ENCMB_FK');
372 igs_ge_msg_stack.add;
373 app_exception.raise_exception;
374 RETURN;
375 END IF;
376 CLOSE cur_rowid;
377
378 END get_fk_igs_fi_encmb_type;
379
380
381 PROCEDURE before_dml (
382 p_action IN VARCHAR2,
383 x_rowid IN VARCHAR2,
384 x_group_name IN VARCHAR2,
385 x_group_desc IN VARCHAR2,
386 x_advising_code IN VARCHAR2,
387 x_resp_org_unit_cd IN VARCHAR2,
388 x_resp_person_id IN NUMBER,
389 x_location_cd IN VARCHAR2,
390 x_delivery_method_code IN VARCHAR2,
391 x_advisor_group_id IN NUMBER,
392 x_student_group_id IN NUMBER,
393 x_default_advisor_load_num IN NUMBER,
394 x_mandatory_flag IN VARCHAR2,
395 x_advising_sessions_num IN NUMBER,
396 x_advising_hold_type IN VARCHAR2,
397 x_closed_flag IN VARCHAR2,
398 x_comments_txt IN VARCHAR2,
399 x_auto_refresh_flag IN VARCHAR2,
400 x_last_auto_refresh_date IN DATE,
401 x_auto_stdnt_add_flag IN VARCHAR2,
402 x_auto_stdnt_remove_flag IN VARCHAR2,
403 x_auto_advisor_add_flag IN VARCHAR2,
404 x_auto_advisor_remove_flag IN VARCHAR2,
405 x_auto_match_flag IN VARCHAR2,
406 x_auto_apply_hold_flag IN VARCHAR2,
407 x_attribute_category IN VARCHAR2,
408 x_attribute1 IN VARCHAR2,
409 x_attribute2 IN VARCHAR2,
410 x_attribute3 IN VARCHAR2,
411 x_attribute4 IN VARCHAR2,
412 x_attribute5 IN VARCHAR2,
413 x_attribute6 IN VARCHAR2,
414 x_attribute7 IN VARCHAR2,
415 x_attribute8 IN VARCHAR2,
416 x_attribute9 IN VARCHAR2,
417 x_attribute10 IN VARCHAR2,
418 x_attribute11 IN VARCHAR2,
419 x_attribute12 IN VARCHAR2,
420 x_attribute13 IN VARCHAR2,
421 x_attribute14 IN VARCHAR2,
422 x_attribute15 IN VARCHAR2,
423 x_attribute16 IN VARCHAR2,
424 x_attribute17 IN VARCHAR2,
425 x_attribute18 IN VARCHAR2,
426 x_attribute19 IN VARCHAR2,
427 x_attribute20 IN VARCHAR2,
428 x_creation_date IN DATE,
429 x_created_by IN NUMBER,
430 x_last_update_date IN DATE,
431 x_last_updated_by IN NUMBER,
432 x_last_update_login IN NUMBER
433 ) AS
434 /*
435 || Created By : [email protected]
436 || Created On : 15-MAY-2003
437 || Purpose : Initialises the columns, Checks Constraints, Calls the
438 || Trigger Handlers for the table, before any DML operation.
439 || Known limitations, enhancements or remarks :
440 || Change History :
441 || Who When What
442 || (reverse chronological order - newest change first)
443 */
444 BEGIN
445
446 set_column_values (
447 p_action,
448 x_rowid,
449 x_group_name,
450 x_group_desc,
451 x_advising_code,
452 x_resp_org_unit_cd,
453 x_resp_person_id,
454 x_location_cd,
455 x_delivery_method_code,
456 x_advisor_group_id,
457 x_student_group_id,
458 x_default_advisor_load_num,
459 x_mandatory_flag,
460 x_advising_sessions_num,
461 x_advising_hold_type,
462 x_closed_flag,
463 x_comments_txt,
464 x_auto_refresh_flag,
465 x_last_auto_refresh_date,
466 x_auto_stdnt_add_flag,
467 x_auto_stdnt_remove_flag,
468 x_auto_advisor_add_flag,
469 x_auto_advisor_remove_flag,
470 x_auto_match_flag,
471 x_auto_apply_hold_flag,
472 x_attribute_category,
473 x_attribute1,
474 x_attribute2,
475 x_attribute3,
476 x_attribute4,
477 x_attribute5,
478 x_attribute6,
479 x_attribute7,
480 x_attribute8,
481 x_attribute9,
482 x_attribute10,
483 x_attribute11,
484 x_attribute12,
485 x_attribute13,
486 x_attribute14,
487 x_attribute15,
488 x_attribute16,
489 x_attribute17,
490 x_attribute18,
491 x_attribute19,
492 x_attribute20,
493 x_creation_date,
494 x_created_by,
495 x_last_update_date,
496 x_last_updated_by,
497 x_last_update_login
498 );
499
500 IF (p_action = 'INSERT') THEN
501 -- Call all the procedures related to Before Insert.
502 IF ( get_pk_for_validation(
503 new_references.group_name
504 )
505 ) THEN
506 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
507 igs_ge_msg_stack.add;
508 app_exception.raise_exception;
509 END IF;
510 check_parent_existance;
511 ELSIF (p_action = 'UPDATE') THEN
512 -- Call all the procedures related to Before Update.
513 check_parent_existance;
514 ELSIF (p_action = 'DELETE') THEN
515 -- Call all the procedures related to Before Delete.
516 check_child_existance;
517 ELSIF (p_action = 'VALIDATE_INSERT') THEN
518 -- Call all the procedures related to Before Insert.
519 IF ( get_pk_for_validation (
520 new_references.group_name
521 )
522 ) THEN
523 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
524 igs_ge_msg_stack.add;
525 app_exception.raise_exception;
526 END IF;
527 ELSIF (p_action = 'VALIDATE_DELETE') THEN
528 check_child_existance;
529 END IF;
530
531 END before_dml;
532
533
534 PROCEDURE insert_row (
535 x_rowid IN OUT NOCOPY VARCHAR2,
536 x_group_name IN OUT NOCOPY VARCHAR2,
537 x_group_desc IN VARCHAR2,
538 x_advising_code IN VARCHAR2,
539 x_resp_org_unit_cd IN VARCHAR2,
540 x_resp_person_id IN NUMBER,
541 x_location_cd IN VARCHAR2,
542 x_delivery_method_code IN VARCHAR2,
543 x_advisor_group_id IN NUMBER,
544 x_student_group_id IN NUMBER,
545 x_default_advisor_load_num IN NUMBER,
546 x_mandatory_flag IN VARCHAR2,
547 x_advising_sessions_num IN NUMBER,
548 x_advising_hold_type IN VARCHAR2,
549 x_closed_flag IN VARCHAR2,
550 x_comments_txt IN VARCHAR2,
551 x_auto_refresh_flag IN VARCHAR2,
552 x_last_auto_refresh_date IN DATE,
553 x_auto_stdnt_add_flag IN VARCHAR2,
554 x_auto_stdnt_remove_flag IN VARCHAR2,
555 x_auto_advisor_add_flag IN VARCHAR2,
556 x_auto_advisor_remove_flag IN VARCHAR2,
557 x_auto_match_flag IN VARCHAR2,
558 x_auto_apply_hold_flag IN VARCHAR2,
559 x_attribute_category IN VARCHAR2,
560 x_attribute1 IN VARCHAR2,
561 x_attribute2 IN VARCHAR2,
562 x_attribute3 IN VARCHAR2,
563 x_attribute4 IN VARCHAR2,
564 x_attribute5 IN VARCHAR2,
565 x_attribute6 IN VARCHAR2,
566 x_attribute7 IN VARCHAR2,
567 x_attribute8 IN VARCHAR2,
568 x_attribute9 IN VARCHAR2,
569 x_attribute10 IN VARCHAR2,
570 x_attribute11 IN VARCHAR2,
571 x_attribute12 IN VARCHAR2,
572 x_attribute13 IN VARCHAR2,
573 x_attribute14 IN VARCHAR2,
574 x_attribute15 IN VARCHAR2,
575 x_attribute16 IN VARCHAR2,
576 x_attribute17 IN VARCHAR2,
577 x_attribute18 IN VARCHAR2,
578 x_attribute19 IN VARCHAR2,
579 x_attribute20 IN VARCHAR2,
580 x_mode IN VARCHAR2,
581 x_return_status OUT NOCOPY VARCHAR2,
582 x_msg_data OUT NOCOPY VARCHAR2,
583 x_msg_count OUT NOCOPY NUMBER
584 ) AS
585 /*
586 || Created By : [email protected]
587 || Created On : 15-MAY-2003
588 || Purpose : Handles the INSERT DML logic for the table.
589 || Known limitations, enhancements or remarks :
590 || Change History :
591 || Who When What
592 || (reverse chronological order - newest change first)
593 */
594
595 x_last_update_date DATE;
596 x_last_updated_by NUMBER;
597 x_last_update_login NUMBER;
598
599 BEGIN
600 FND_MSG_PUB.initialize;
601 x_last_update_date := SYSDATE;
602 IF (x_mode = 'I') THEN
603 x_last_updated_by := 1;
604 x_last_update_login := 0;
605 ELSIF (x_mode = 'R') THEN
606 x_last_updated_by := fnd_global.user_id;
607 IF (x_last_updated_by IS NULL) THEN
608 x_last_updated_by := -1;
609 END IF;
610 x_last_update_login := fnd_global.login_id;
611 IF (x_last_update_login IS NULL) THEN
612 x_last_update_login := -1;
613 END IF;
614 ELSE
615 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
616 fnd_message.set_token ('ROUTINE', 'IGS_AZ_GROUPS_PKG.INSERT_ROW');
617 igs_ge_msg_stack.add;
618 app_exception.raise_exception;
619 END IF;
620
621 before_dml(
622 p_action => 'INSERT',
623 x_rowid => x_rowid,
624 x_group_name => x_group_name,
625 x_group_desc => x_group_desc,
626 x_advising_code => x_advising_code,
627 x_resp_org_unit_cd => x_resp_org_unit_cd,
628 x_resp_person_id => x_resp_person_id,
629 x_location_cd => x_location_cd,
630 x_delivery_method_code => x_delivery_method_code,
631 x_advisor_group_id => x_advisor_group_id,
632 x_student_group_id => x_student_group_id,
633 x_default_advisor_load_num => x_default_advisor_load_num,
634 x_mandatory_flag => x_mandatory_flag,
635 x_advising_sessions_num => x_advising_sessions_num,
636 x_advising_hold_type => x_advising_hold_type,
637 x_closed_flag => x_closed_flag,
638 x_comments_txt => x_comments_txt,
639 x_auto_refresh_flag => x_auto_refresh_flag,
640 x_last_auto_refresh_date => x_last_auto_refresh_date,
641 x_auto_stdnt_add_flag => x_auto_stdnt_add_flag,
642 x_auto_stdnt_remove_flag => x_auto_stdnt_remove_flag,
643 x_auto_advisor_add_flag => x_auto_advisor_add_flag,
644 x_auto_advisor_remove_flag => x_auto_advisor_remove_flag,
645 x_auto_match_flag => x_auto_match_flag,
646 x_auto_apply_hold_flag => x_auto_apply_hold_flag,
647 x_attribute_category => x_attribute_category,
648 x_attribute1 => x_attribute1,
649 x_attribute2 => x_attribute2,
650 x_attribute3 => x_attribute3,
651 x_attribute4 => x_attribute4,
652 x_attribute5 => x_attribute5,
653 x_attribute6 => x_attribute6,
654 x_attribute7 => x_attribute7,
655 x_attribute8 => x_attribute8,
656 x_attribute9 => x_attribute9,
657 x_attribute10 => x_attribute10,
658 x_attribute11 => x_attribute11,
659 x_attribute12 => x_attribute12,
660 x_attribute13 => x_attribute13,
661 x_attribute14 => x_attribute14,
662 x_attribute15 => x_attribute15,
663 x_attribute16 => x_attribute16,
664 x_attribute17 => x_attribute17,
665 x_attribute18 => x_attribute18,
666 x_attribute19 => x_attribute19,
667 x_attribute20 => x_attribute20,
668 x_creation_date => x_last_update_date,
669 x_created_by => x_last_updated_by,
670 x_last_update_date => x_last_update_date,
671 x_last_updated_by => x_last_updated_by,
672 x_last_update_login => x_last_update_login
673 );
674
675 INSERT INTO igs_az_groups (
676 group_name,
677 group_desc,
678 advising_code,
679 resp_org_unit_cd,
680 resp_person_id,
681 location_cd,
682 delivery_method_code,
683 advisor_group_id,
684 student_group_id,
685 default_advisor_load_num,
686 mandatory_flag,
687 advising_sessions_num,
688 advising_hold_type,
689 closed_flag,
690 comments_txt,
691 auto_refresh_flag,
692 last_auto_refresh_date,
693 auto_stdnt_add_flag,
694 auto_stdnt_remove_flag,
695 auto_advisor_add_flag,
696 auto_advisor_remove_flag,
697 auto_match_flag,
698 auto_apply_hold_flag,
699 attribute_category,
700 attribute1,
701 attribute2,
702 attribute3,
703 attribute4,
704 attribute5,
705 attribute6,
706 attribute7,
707 attribute8,
708 attribute9,
709 attribute10,
710 attribute11,
711 attribute12,
712 attribute13,
713 attribute14,
714 attribute15,
715 attribute16,
716 attribute17,
717 attribute18,
718 attribute19,
719 attribute20,
720 creation_date,
721 created_by,
722 last_update_date,
723 last_updated_by,
724 last_update_login
725 ) VALUES (
726 UPPER(new_references.group_name),
727 new_references.group_desc,
728 new_references.advising_code,
729 new_references.resp_org_unit_cd,
730 new_references.resp_person_id,
731 new_references.location_cd,
732 new_references.delivery_method_code,
733 new_references.advisor_group_id,
734 new_references.student_group_id,
735 new_references.default_advisor_load_num,
736 new_references.mandatory_flag,
737 new_references.advising_sessions_num,
738 new_references.advising_hold_type,
739 new_references.closed_flag,
740 new_references.comments_txt,
741 new_references.auto_refresh_flag,
742 new_references.last_auto_refresh_date,
743 new_references.auto_stdnt_add_flag,
744 new_references.auto_stdnt_remove_flag,
745 new_references.auto_advisor_add_flag,
746 new_references.auto_advisor_remove_flag,
747 new_references.auto_match_flag,
748 new_references.auto_apply_hold_flag,
749 new_references.attribute_category,
750 new_references.attribute1,
751 new_references.attribute2,
752 new_references.attribute3,
753 new_references.attribute4,
754 new_references.attribute5,
755 new_references.attribute6,
756 new_references.attribute7,
757 new_references.attribute8,
758 new_references.attribute9,
759 new_references.attribute10,
760 new_references.attribute11,
761 new_references.attribute12,
762 new_references.attribute13,
763 new_references.attribute14,
764 new_references.attribute15,
765 new_references.attribute16,
766 new_references.attribute17,
767 new_references.attribute18,
768 new_references.attribute19,
769 new_references.attribute20,
770 x_last_update_date,
771 x_last_updated_by,
772 x_last_update_date,
773 x_last_updated_by,
774 x_last_update_login
775 ) RETURNING ROWID, group_name INTO x_rowid, x_group_name;
776 -- Initialize API return status to success.
777 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
778 -- Standard call to get message count and if count is 1, get message
779 -- info.
780 FND_MSG_PUB.Count_And_Get(
781 p_encoded => FND_API.G_FALSE,
782 p_count => x_MSG_COUNT,
783 p_data => X_MSG_DATA);
784
785 EXCEPTION
786 WHEN FND_API.G_EXC_ERROR THEN
787 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
788 FND_MSG_PUB.Count_And_Get(
789 p_encoded => FND_API.G_FALSE,
790 p_count => x_MSG_COUNT,
791 p_data => X_MSG_DATA);
792 RETURN;
793 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
794 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
795 FND_MSG_PUB.Count_And_Get(
796 p_encoded => FND_API.G_FALSE,
797 p_count => x_MSG_COUNT,
798 p_data => X_MSG_DATA);
799 RETURN;
800 WHEN OTHERS THEN
801 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
802 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
803 FND_MESSAGE.SET_TOKEN('NAME','Insert_Row : '||SQLERRM);
804 FND_MSG_PUB.ADD;
805 FND_MSG_PUB.Count_And_Get(
806 p_encoded => FND_API.G_FALSE,
807 p_count => x_MSG_COUNT,
808 p_data => X_MSG_DATA);
809 RETURN;
810
811 END insert_row;
812
813
814 Procedure lock_row (
815 x_rowid IN VARCHAR2,
816 x_group_name IN VARCHAR2,
817 x_group_desc IN VARCHAR2,
818 x_advising_code IN VARCHAR2,
819 x_resp_org_unit_cd IN VARCHAR2,
820 x_resp_person_id IN NUMBER,
821 x_location_cd IN VARCHAR2,
822 x_delivery_method_code IN VARCHAR2,
823 x_advisor_group_id IN NUMBER,
824 x_student_group_id IN NUMBER,
825 x_default_advisor_load_num IN NUMBER,
826 x_mandatory_flag IN VARCHAR2,
827 x_advising_sessions_num IN NUMBER,
828 x_advising_hold_type IN VARCHAR2,
829 x_closed_flag IN VARCHAR2,
830 x_comments_txt IN VARCHAR2,
831 x_auto_refresh_flag IN VARCHAR2,
832 x_last_auto_refresh_date IN DATE,
833 x_auto_stdnt_add_flag IN VARCHAR2,
834 x_auto_stdnt_remove_flag IN VARCHAR2,
835 x_auto_advisor_add_flag IN VARCHAR2,
836 x_auto_advisor_remove_flag IN VARCHAR2,
837 x_auto_match_flag IN VARCHAR2,
838 x_auto_apply_hold_flag IN VARCHAR2,
839 x_attribute_category IN VARCHAR2,
840 x_attribute1 IN VARCHAR2,
841 x_attribute2 IN VARCHAR2,
842 x_attribute3 IN VARCHAR2,
843 x_attribute4 IN VARCHAR2,
844 x_attribute5 IN VARCHAR2,
845 x_attribute6 IN VARCHAR2,
846 x_attribute7 IN VARCHAR2,
847 x_attribute8 IN VARCHAR2,
848 x_attribute9 IN VARCHAR2,
849 x_attribute10 IN VARCHAR2,
850 x_attribute11 IN VARCHAR2,
851 x_attribute12 IN VARCHAR2,
852 x_attribute13 IN VARCHAR2,
853 x_attribute14 IN VARCHAR2,
854 x_attribute15 IN VARCHAR2,
855 x_attribute16 IN VARCHAR2,
856 x_attribute17 IN VARCHAR2,
857 x_attribute18 IN VARCHAR2,
858 x_attribute19 IN VARCHAR2,
859 x_attribute20 IN VARCHAR2,
860 x_return_status OUT NOCOPY VARCHAR2,
861 x_msg_data OUT NOCOPY VARCHAR2,
862 x_msg_count OUT NOCOPY NUMBER
863 ) AS
864 /*
865 || Created By : [email protected]
866 || Created On : 15-MAY-2003
867 || Purpose : Handles the LOCK mechanism for the table.
868 || Known limitations, enhancements or remarks :
869 || Change History :
870 || Who When What
871 || (reverse chronological order - newest change first)
872 */
873 CURSOR c1 IS
874 SELECT
875 group_desc,
876 advising_code,
877 resp_org_unit_cd,
878 resp_person_id,
879 location_cd,
880 delivery_method_code,
881 advisor_group_id,
882 student_group_id,
883 default_advisor_load_num,
884 mandatory_flag,
885 advising_sessions_num,
886 advising_hold_type,
887 closed_flag,
888 comments_txt,
889 auto_refresh_flag,
890 last_auto_refresh_date,
891 auto_stdnt_add_flag,
892 auto_stdnt_remove_flag,
893 auto_advisor_add_flag,
894 auto_advisor_remove_flag,
895 auto_match_flag,
896 auto_apply_hold_flag,
897 attribute_category,
898 attribute1,
899 attribute2,
900 attribute3,
901 attribute4,
902 attribute5,
903 attribute6,
904 attribute7,
905 attribute8,
906 attribute9,
907 attribute10,
908 attribute11,
909 attribute12,
910 attribute13,
911 attribute14,
912 attribute15,
913 attribute16,
914 attribute17,
915 attribute18,
916 attribute19,
917 attribute20
918 FROM igs_az_groups
919 WHERE rowid = x_rowid
920 FOR UPDATE NOWAIT;
921
922 tlinfo c1%ROWTYPE;
923
924 BEGIN
925 FND_MSG_PUB.initialize;
926 OPEN c1;
927 FETCH c1 INTO tlinfo;
928 IF (c1%notfound) THEN
929 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
930 igs_ge_msg_stack.add;
931 CLOSE c1;
932 app_exception.raise_exception;
933 RETURN;
934 END IF;
935 CLOSE c1;
936
937 IF (
938 (tlinfo.group_desc = x_group_desc)
939 AND (tlinfo.advising_code = x_advising_code OR ((tlinfo.advising_code IS NULL) AND (x_advising_code IS NULL)))
940 AND (tlinfo.resp_org_unit_cd = x_resp_org_unit_cd)
941 AND ((tlinfo.resp_person_id = x_resp_person_id) OR ((tlinfo.resp_person_id IS NULL) AND (X_resp_person_id IS NULL)))
942 AND ((tlinfo.location_cd = x_location_cd) OR ((tlinfo.location_cd IS NULL) AND (X_location_cd IS NULL)))
943 AND (tlinfo.delivery_method_code = x_delivery_method_code)
944 AND ((tlinfo.advisor_group_id = x_advisor_group_id) OR ((tlinfo.advisor_group_id IS NULL) AND (X_advisor_group_id IS NULL)))
945 AND ((tlinfo.student_group_id = x_student_group_id) OR ((tlinfo.student_group_id IS NULL) AND (X_student_group_id IS NULL)))
946 AND ((tlinfo.default_advisor_load_num = x_default_advisor_load_num) OR ((tlinfo.default_advisor_load_num IS NULL) AND (x_default_advisor_load_num IS NULL)))
947 AND (tlinfo.mandatory_flag = x_mandatory_flag)
948 AND ((tlinfo.advising_sessions_num = x_advising_sessions_num) OR ((tlinfo.advising_sessions_num IS NULL) AND (x_advising_sessions_num IS NULL)))
949 AND ((tlinfo.advising_hold_type = x_advising_hold_type) OR ((tlinfo.advising_hold_type IS NULL) AND (X_advising_hold_type IS NULL)))
950 AND (tlinfo.closed_flag = x_closed_flag)
951 AND ((tlinfo.comments_txt = x_comments_txt) OR ((tlinfo.comments_txt IS NULL) AND (x_comments_txt IS NULL)))
952 AND (tlinfo.auto_refresh_flag = x_auto_refresh_flag)
953 AND ((trunc(tlinfo.last_auto_refresh_date) = trunc(x_last_auto_refresh_date)) OR ((tlinfo.last_auto_refresh_date IS NULL) AND (x_last_auto_refresh_date IS NULL)))
954 AND (tlinfo.auto_stdnt_add_flag = x_auto_stdnt_add_flag)
955 AND (tlinfo.auto_stdnt_remove_flag = x_auto_stdnt_remove_flag)
956 AND (tlinfo.auto_advisor_add_flag = x_auto_advisor_add_flag)
957 AND (tlinfo.auto_advisor_remove_flag = x_auto_advisor_remove_flag)
958 AND (tlinfo.auto_match_flag = x_auto_match_flag)
959 AND (tlinfo.auto_apply_hold_flag = x_auto_apply_hold_flag)
960 AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (X_attribute_category IS NULL)))
961 AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (X_attribute1 IS NULL)))
962 AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (X_attribute2 IS NULL)))
963 AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (X_attribute3 IS NULL)))
964 AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (X_attribute4 IS NULL)))
965 AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (X_attribute5 IS NULL)))
966 AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (X_attribute6 IS NULL)))
967 AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (X_attribute7 IS NULL)))
968 AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (X_attribute8 IS NULL)))
969 AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (X_attribute9 IS NULL)))
970 AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (X_attribute10 IS NULL)))
971 AND ((tlinfo.attribute11 = x_attribute11) OR ((tlinfo.attribute11 IS NULL) AND (X_attribute11 IS NULL)))
972 AND ((tlinfo.attribute12 = x_attribute12) OR ((tlinfo.attribute12 IS NULL) AND (X_attribute12 IS NULL)))
973 AND ((tlinfo.attribute13 = x_attribute13) OR ((tlinfo.attribute13 IS NULL) AND (X_attribute13 IS NULL)))
974 AND ((tlinfo.attribute14 = x_attribute14) OR ((tlinfo.attribute14 IS NULL) AND (X_attribute14 IS NULL)))
975 AND ((tlinfo.attribute15 = x_attribute15) OR ((tlinfo.attribute15 IS NULL) AND (X_attribute15 IS NULL)))
976 AND ((tlinfo.attribute16 = x_attribute16) OR ((tlinfo.attribute16 IS NULL) AND (X_attribute16 IS NULL)))
977 AND ((tlinfo.attribute17 = x_attribute17) OR ((tlinfo.attribute17 IS NULL) AND (X_attribute17 IS NULL)))
978 AND ((tlinfo.attribute18 = x_attribute18) OR ((tlinfo.attribute18 IS NULL) AND (X_attribute18 IS NULL)))
979 AND ((tlinfo.attribute19 = x_attribute19) OR ((tlinfo.attribute19 IS NULL) AND (X_attribute19 IS NULL)))
980 AND ((tlinfo.attribute20 = x_attribute20) OR ((tlinfo.attribute20 IS NULL) AND (X_attribute20 IS NULL)))
981 ) THEN
982 NULL;
983 ELSE
984 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
985 igs_ge_msg_stack.add;
986 RAISE FND_API.G_EXC_ERROR;
987 END IF;
988
989
990 -- Initialize API return status to success.
991 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
992 -- Standard call to get message count and if count is 1, get message
993 -- info.
994 FND_MSG_PUB.Count_And_Get(
995 p_encoded => FND_API.G_FALSE,
996 p_count => x_MSG_COUNT,
997 p_data => X_MSG_DATA);
998 RETURN;
999 EXCEPTION
1000 WHEN FND_API.G_EXC_ERROR THEN
1001 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1002 FND_MSG_PUB.Count_And_Get(
1003 p_encoded => FND_API.G_FALSE,
1004 p_count => x_MSG_COUNT,
1005 p_data => X_MSG_DATA);
1006 RETURN;
1007 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1008 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1009 FND_MSG_PUB.Count_And_Get(
1010 p_encoded => FND_API.G_FALSE,
1011 p_count => x_MSG_COUNT,
1012 p_data => X_MSG_DATA);
1013 RETURN;
1014 WHEN OTHERS THEN
1015 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1016 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1017 FND_MESSAGE.SET_TOKEN('NAME','Insert_Row : '||SQLERRM);
1018 FND_MSG_PUB.ADD;
1019 FND_MSG_PUB.Count_And_Get(
1020 p_encoded => FND_API.G_FALSE,
1021 p_count => x_MSG_COUNT,
1022 p_data => X_MSG_DATA);
1023 RETURN;
1024
1025
1026 END lock_row;
1027
1028
1029 PROCEDURE update_row (
1030 x_rowid IN VARCHAR2,
1031 x_group_name IN VARCHAR2,
1032 x_group_desc IN VARCHAR2,
1033 x_advising_code IN VARCHAR2,
1034 x_resp_org_unit_cd IN VARCHAR2,
1035 x_resp_person_id IN NUMBER,
1036 x_location_cd IN VARCHAR2,
1037 x_delivery_method_code IN VARCHAR2,
1038 x_advisor_group_id IN NUMBER,
1039 x_student_group_id IN NUMBER,
1040 x_default_advisor_load_num IN NUMBER,
1041 x_mandatory_flag IN VARCHAR2,
1042 x_advising_sessions_num IN NUMBER,
1043 x_advising_hold_type IN VARCHAR2,
1044 x_closed_flag IN VARCHAR2,
1045 x_comments_txt IN VARCHAR2,
1046 x_auto_refresh_flag IN VARCHAR2,
1047 x_last_auto_refresh_date IN DATE,
1048 x_auto_stdnt_add_flag IN VARCHAR2,
1049 x_auto_stdnt_remove_flag IN VARCHAR2,
1050 x_auto_advisor_add_flag IN VARCHAR2,
1051 x_auto_advisor_remove_flag IN VARCHAR2,
1052 x_auto_match_flag IN VARCHAR2,
1053 x_auto_apply_hold_flag IN VARCHAR2,
1054 x_attribute_category IN VARCHAR2,
1055 x_attribute1 IN VARCHAR2,
1056 x_attribute2 IN VARCHAR2,
1057 x_attribute3 IN VARCHAR2,
1058 x_attribute4 IN VARCHAR2,
1059 x_attribute5 IN VARCHAR2,
1060 x_attribute6 IN VARCHAR2,
1061 x_attribute7 IN VARCHAR2,
1062 x_attribute8 IN VARCHAR2,
1063 x_attribute9 IN VARCHAR2,
1064 x_attribute10 IN VARCHAR2,
1065 x_attribute11 IN VARCHAR2,
1066 x_attribute12 IN VARCHAR2,
1067 x_attribute13 IN VARCHAR2,
1068 x_attribute14 IN VARCHAR2,
1069 x_attribute15 IN VARCHAR2,
1070 x_attribute16 IN VARCHAR2,
1071 x_attribute17 IN VARCHAR2,
1072 x_attribute18 IN VARCHAR2,
1073 x_attribute19 IN VARCHAR2,
1074 x_attribute20 IN VARCHAR2,
1075 x_mode IN VARCHAR2,
1076 x_return_status OUT NOCOPY VARCHAR2,
1077 x_msg_data OUT NOCOPY VARCHAR2,
1078 x_msg_count OUT NOCOPY NUMBER
1079 ) AS
1080 /*
1081 || Created By : [email protected]
1082 || Created On : 15-MAY-2003
1083 || Purpose : Handles the UPDATE DML logic for the table.
1084 || Known limitations, enhancements or remarks :
1085 || Change History :
1086 || Who When What
1087 || (reverse chronological order - newest change first)
1088 */
1089 x_last_update_date DATE ;
1090 x_last_updated_by NUMBER;
1091 x_last_update_login NUMBER;
1092
1093 BEGIN
1094 FND_MSG_PUB.initialize;
1095 x_last_update_date := SYSDATE;
1096 IF (X_MODE = 'I') THEN
1097 x_last_updated_by := 1;
1098 x_last_update_login := 0;
1099 ELSIF (x_mode = 'R') THEN
1100 x_last_updated_by := fnd_global.user_id;
1101 IF x_last_updated_by IS NULL THEN
1102 x_last_updated_by := -1;
1103 END IF;
1104 x_last_update_login := fnd_global.login_id;
1105 IF (x_last_update_login IS NULL) THEN
1106 x_last_update_login := -1;
1107 END IF;
1108 ELSE
1109 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1110 fnd_message.set_token ('ROUTINE', 'IGS_AZ_GROUPS_PKG.UPDATE_ROW');
1111 igs_ge_msg_stack.add;
1112 app_exception.raise_exception;
1113 END IF;
1114
1115 before_dml(
1116 p_action => 'UPDATE',
1117 x_rowid => x_rowid,
1118 x_group_name => x_group_name,
1119 x_group_desc => x_group_desc,
1120 x_advising_code => x_advising_code,
1121 x_resp_org_unit_cd => x_resp_org_unit_cd,
1122 x_resp_person_id => x_resp_person_id,
1123 x_location_cd => x_location_cd,
1124 x_delivery_method_code => x_delivery_method_code,
1125 x_advisor_group_id => x_advisor_group_id,
1126 x_student_group_id => x_student_group_id,
1127 x_default_advisor_load_num => x_default_advisor_load_num,
1128 x_mandatory_flag => x_mandatory_flag,
1129 x_advising_sessions_num => x_advising_sessions_num,
1130 x_advising_hold_type => x_advising_hold_type,
1131 x_closed_flag => x_closed_flag,
1132 x_comments_txt => x_comments_txt,
1133 x_auto_refresh_flag => x_auto_refresh_flag,
1134 x_last_auto_refresh_date => x_last_auto_refresh_date,
1135 x_auto_stdnt_add_flag => x_auto_stdnt_add_flag,
1136 x_auto_stdnt_remove_flag => x_auto_stdnt_remove_flag,
1137 x_auto_advisor_add_flag => x_auto_advisor_add_flag,
1138 x_auto_advisor_remove_flag => x_auto_advisor_remove_flag,
1139 x_auto_match_flag => x_auto_match_flag,
1140 x_auto_apply_hold_flag => x_auto_apply_hold_flag,
1141 x_attribute_category => x_attribute_category,
1142 x_attribute1 => x_attribute1,
1143 x_attribute2 => x_attribute2,
1144 x_attribute3 => x_attribute3,
1145 x_attribute4 => x_attribute4,
1146 x_attribute5 => x_attribute5,
1147 x_attribute6 => x_attribute6,
1148 x_attribute7 => x_attribute7,
1149 x_attribute8 => x_attribute8,
1150 x_attribute9 => x_attribute9,
1151 x_attribute10 => x_attribute10,
1152 x_attribute11 => x_attribute11,
1153 x_attribute12 => x_attribute12,
1154 x_attribute13 => x_attribute13,
1155 x_attribute14 => x_attribute14,
1156 x_attribute15 => x_attribute15,
1157 x_attribute16 => x_attribute16,
1158 x_attribute17 => x_attribute17,
1159 x_attribute18 => x_attribute18,
1160 x_attribute19 => x_attribute19,
1161 x_attribute20 => x_attribute20,
1162 x_creation_date => x_last_update_date,
1163 x_created_by => x_last_updated_by,
1164 x_last_update_date => x_last_update_date,
1165 x_last_updated_by => x_last_updated_by,
1166 x_last_update_login => x_last_update_login
1167 );
1168
1169 UPDATE igs_az_groups
1170 SET
1171 group_desc = new_references.group_desc,
1172 advising_code = new_references.advising_code,
1173 resp_org_unit_cd = new_references.resp_org_unit_cd,
1174 resp_person_id = new_references.resp_person_id,
1175 location_cd = new_references.location_cd,
1176 delivery_method_code = new_references.delivery_method_code,
1177 advisor_group_id = new_references.advisor_group_id,
1178 student_group_id = new_references.student_group_id,
1179 default_advisor_load_num = new_references.default_advisor_load_num,
1180 mandatory_flag = new_references.mandatory_flag,
1181 advising_sessions_num = new_references.advising_sessions_num,
1182 advising_hold_type = new_references.advising_hold_type,
1183 closed_flag = new_references.closed_flag,
1184 comments_txt = new_references.comments_txt,
1185 auto_refresh_flag = new_references.auto_refresh_flag,
1186 last_auto_refresh_date = new_references.last_auto_refresh_date,
1187 auto_stdnt_add_flag = new_references.auto_stdnt_add_flag,
1188 auto_stdnt_remove_flag = new_references.auto_stdnt_remove_flag,
1189 auto_advisor_add_flag = new_references.auto_advisor_add_flag,
1190 auto_advisor_remove_flag = new_references.auto_advisor_remove_flag,
1191 auto_match_flag = new_references.auto_match_flag,
1192 auto_apply_hold_flag = new_references.auto_apply_hold_flag,
1193 attribute_category = new_references.attribute_category,
1194 attribute1 = new_references.attribute1,
1195 attribute2 = new_references.attribute2,
1196 attribute3 = new_references.attribute3,
1197 attribute4 = new_references.attribute4,
1198 attribute5 = new_references.attribute5,
1199 attribute6 = new_references.attribute6,
1200 attribute7 = new_references.attribute7,
1201 attribute8 = new_references.attribute8,
1202 attribute9 = new_references.attribute9,
1203 attribute10 = new_references.attribute10,
1204 attribute11 = new_references.attribute11,
1205 attribute12 = new_references.attribute12,
1206 attribute13 = new_references.attribute13,
1207 attribute14 = new_references.attribute14,
1208 attribute15 = new_references.attribute15,
1209 attribute16 = new_references.attribute16,
1210 attribute17 = new_references.attribute17,
1211 attribute18 = new_references.attribute18,
1212 attribute19 = new_references.attribute19,
1213 attribute20 = new_references.attribute20,
1214 last_update_date = x_last_update_date,
1215 last_updated_by = x_last_updated_by,
1216 last_update_login = x_last_update_login
1217 WHERE rowid = x_rowid;
1218
1219 IF (SQL%NOTFOUND) THEN
1220 RAISE NO_DATA_FOUND;
1221 END IF;
1222 -- Initialize API return status to success.
1223 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1224 -- Standard call to get message count and if count is 1, get message
1225 -- info.
1226 FND_MSG_PUB.Count_And_Get(
1227 p_encoded => FND_API.G_FALSE,
1228 p_count => x_MSG_COUNT,
1229 p_data => X_MSG_DATA);
1230
1231 EXCEPTION
1232 WHEN FND_API.G_EXC_ERROR THEN
1233 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1234 FND_MSG_PUB.Count_And_Get(
1235 p_encoded => FND_API.G_FALSE,
1236 p_count => x_MSG_COUNT,
1237 p_data => X_MSG_DATA);
1238 RETURN;
1239 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1240 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1241 FND_MSG_PUB.Count_And_Get(
1242 p_encoded => FND_API.G_FALSE,
1243 p_count => x_MSG_COUNT,
1244 p_data => X_MSG_DATA);
1245 RETURN;
1246 WHEN OTHERS THEN
1247 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1248 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1249 FND_MESSAGE.SET_TOKEN('NAME','Update_Row : '||SQLERRM);
1250 FND_MSG_PUB.ADD;
1251 FND_MSG_PUB.Count_And_Get(
1252 p_encoded => FND_API.G_FALSE,
1253 p_count => x_MSG_COUNT,
1254 p_data => X_MSG_DATA);
1255 RETURN;
1256 END update_row;
1257
1258
1259 PROCEDURE add_row (
1260 x_rowid IN OUT NOCOPY VARCHAR2,
1261 x_group_name IN OUT NOCOPY VARCHAR2,
1262 x_group_desc IN VARCHAR2,
1263 x_advising_code IN VARCHAR2,
1264 x_resp_org_unit_cd IN VARCHAR2,
1265 x_resp_person_id IN NUMBER,
1266 x_location_cd IN VARCHAR2,
1267 x_delivery_method_code IN VARCHAR2,
1268 x_advisor_group_id IN NUMBER,
1269 x_student_group_id IN NUMBER,
1270 x_default_advisor_load_num IN NUMBER,
1271 x_mandatory_flag IN VARCHAR2,
1272 x_advising_sessions_num IN NUMBER,
1273 x_advising_hold_type IN VARCHAR2,
1274 x_closed_flag IN VARCHAR2,
1275 x_comments_txt IN VARCHAR2,
1276 x_auto_refresh_flag IN VARCHAR2,
1277 x_last_auto_refresh_date IN DATE,
1278 x_auto_stdnt_add_flag IN VARCHAR2,
1279 x_auto_stdnt_remove_flag IN VARCHAR2,
1280 x_auto_advisor_add_flag IN VARCHAR2,
1281 x_auto_advisor_remove_flag IN VARCHAR2,
1282 x_auto_match_flag IN VARCHAR2,
1283 x_auto_apply_hold_flag IN VARCHAR2,
1284 x_attribute_category IN VARCHAR2,
1285 x_attribute1 IN VARCHAR2,
1286 x_attribute2 IN VARCHAR2,
1287 x_attribute3 IN VARCHAR2,
1288 x_attribute4 IN VARCHAR2,
1289 x_attribute5 IN VARCHAR2,
1290 x_attribute6 IN VARCHAR2,
1291 x_attribute7 IN VARCHAR2,
1292 x_attribute8 IN VARCHAR2,
1293 x_attribute9 IN VARCHAR2,
1294 x_attribute10 IN VARCHAR2,
1295 x_attribute11 IN VARCHAR2,
1296 x_attribute12 IN VARCHAR2,
1297 x_attribute13 IN VARCHAR2,
1298 x_attribute14 IN VARCHAR2,
1299 x_attribute15 IN VARCHAR2,
1300 x_attribute16 IN VARCHAR2,
1301 x_attribute17 IN VARCHAR2,
1302 x_attribute18 IN VARCHAR2,
1303 x_attribute19 IN VARCHAR2,
1304 x_attribute20 IN VARCHAR2,
1305 x_mode IN VARCHAR2
1306 ) AS
1307 /*
1308 || Created By : [email protected]
1309 || Created On : 15-MAY-2003
1310 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1311 || Known limitations, enhancements or remarks :
1312 || Change History :
1313 || Who When What
1314 || (reverse chronological order - newest change first)
1315 */
1316 CURSOR c1 IS
1317 SELECT rowid
1318 FROM igs_az_groups
1319 WHERE group_name = x_group_name;
1320 l_return_status VARCHAR2(10);
1321 l_msg_data VARCHAR2(2000);
1322 l_msg_count NUMBER(10);
1323 BEGIN
1324
1325 OPEN c1;
1326 FETCH c1 INTO x_rowid;
1327 IF (c1%NOTFOUND) THEN
1328 CLOSE c1;
1329
1330 insert_row (
1331 x_rowid,
1332 x_group_name,
1333 x_group_desc,
1334 x_advising_code,
1335 x_resp_org_unit_cd,
1336 x_resp_person_id,
1337 x_location_cd,
1338 x_delivery_method_code,
1339 x_advisor_group_id,
1340 x_student_group_id,
1341 x_default_advisor_load_num,
1342 x_mandatory_flag,
1343 x_advising_sessions_num,
1344 x_advising_hold_type,
1345 x_closed_flag,
1346 x_comments_txt,
1347 x_auto_refresh_flag,
1348 x_last_auto_refresh_date,
1349 x_auto_stdnt_add_flag,
1350 x_auto_stdnt_remove_flag,
1351 x_auto_advisor_add_flag,
1352 x_auto_advisor_remove_flag,
1353 x_auto_match_flag,
1354 x_auto_apply_hold_flag,
1355 x_attribute_category,
1356 x_attribute1,
1357 x_attribute2,
1358 x_attribute3,
1359 x_attribute4,
1360 x_attribute5,
1361 x_attribute6,
1362 x_attribute7,
1363 x_attribute8,
1364 x_attribute9,
1365 x_attribute10,
1366 x_attribute11,
1367 x_attribute12,
1368 x_attribute13,
1369 x_attribute14,
1370 x_attribute15,
1371 x_attribute16,
1372 x_attribute17,
1373 x_attribute18,
1374 x_attribute19,
1375 x_attribute20,
1376 x_mode,
1377 l_return_status,
1378 l_msg_data,
1379 l_msg_count
1380 );
1381 RETURN;
1382 END IF;
1383 CLOSE c1;
1384
1385 update_row (
1386 x_rowid,
1387 x_group_name,
1388 x_group_desc,
1389 x_advising_code,
1390 x_resp_org_unit_cd,
1391 x_resp_person_id,
1392 x_location_cd,
1393 x_delivery_method_code,
1394 x_advisor_group_id,
1395 x_student_group_id,
1396 x_default_advisor_load_num,
1397 x_mandatory_flag,
1398 x_advising_sessions_num,
1399 x_advising_hold_type,
1400 x_closed_flag,
1401 x_comments_txt,
1402 x_auto_refresh_flag,
1403 x_last_auto_refresh_date,
1404 x_auto_stdnt_add_flag,
1405 x_auto_stdnt_remove_flag,
1406 x_auto_advisor_add_flag,
1407 x_auto_advisor_remove_flag,
1408 x_auto_match_flag,
1409 x_auto_apply_hold_flag,
1410 x_attribute_category,
1411 x_attribute1,
1412 x_attribute2,
1413 x_attribute3,
1414 x_attribute4,
1415 x_attribute5,
1416 x_attribute6,
1417 x_attribute7,
1418 x_attribute8,
1419 x_attribute9,
1420 x_attribute10,
1421 x_attribute11,
1422 x_attribute12,
1423 x_attribute13,
1424 x_attribute14,
1425 x_attribute15,
1426 x_attribute16,
1427 x_attribute17,
1428 x_attribute18,
1429 x_attribute19,
1430 x_attribute20,
1431 x_mode,
1432 l_return_status,
1433 l_msg_data,
1434 l_msg_count
1435 );
1436
1437 END add_row;
1438
1439
1440 PROCEDURE delete_row (
1441 x_rowid IN VARCHAR2,
1442 x_return_status OUT NOCOPY VARCHAR2,
1443 x_msg_data OUT NOCOPY VARCHAR2,
1444 x_msg_count OUT NOCOPY NUMBER
1445 ) AS
1446 /*
1447 || Created By : [email protected]
1448 || Created On : 15-MAY-2003
1449 || Purpose : Handles the DELETE DML logic for the table.
1450 || Known limitations, enhancements or remarks :
1451 || Change History :
1452 || Who When What
1453 || (reverse chronological order - newest change first)
1454 */
1455 BEGIN
1456 FND_MSG_PUB.initialize;
1457 before_dml (
1458 p_action => 'DELETE',
1459 x_rowid => x_rowid
1460 );
1461
1462 DELETE FROM igs_az_groups
1463 WHERE rowid = x_rowid;
1464
1465 IF (SQL%NOTFOUND) THEN
1466 RAISE NO_DATA_FOUND;
1467 END IF;
1468 -- Initialize API return status to success.
1469 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1470 -- Standard call to get message count and if count is 1, get message
1471 -- info.
1472 FND_MSG_PUB.Count_And_Get(
1473 p_encoded => FND_API.G_FALSE,
1474 p_count => x_MSG_COUNT,
1475 p_data => X_MSG_DATA
1476 );
1477
1478 EXCEPTION
1479 WHEN FND_API.G_EXC_ERROR THEN
1480 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1481 FND_MSG_PUB.Count_And_Get(
1482 p_encoded => FND_API.G_FALSE,
1483 p_count => x_MSG_COUNT,
1484 p_data => X_MSG_DATA);
1485 RETURN;
1486 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1487 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1488 FND_MSG_PUB.Count_And_Get(
1489 p_encoded => FND_API.G_FALSE,
1490 p_count => x_MSG_COUNT,
1491 p_data => X_MSG_DATA);
1492 RETURN;
1493 WHEN OTHERS THEN
1494 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1495 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1496 FND_MESSAGE.SET_TOKEN('NAME','Insert_Row : '||SQLERRM);
1497 FND_MSG_PUB.ADD;
1498 FND_MSG_PUB.Count_And_Get(
1499 p_encoded => FND_API.G_FALSE,
1500 p_count => x_MSG_COUNT,
1501 p_data => X_MSG_DATA);
1502 RETURN;
1503 END delete_row;
1504
1505
1506 END igs_az_groups_pkg;