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_USEC_SESSNS_PKG

Source


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