eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IGS_AS_SUA_SES_ATTS_PKG

Source


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