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_EN_PSV_TERM_IT_PKG

Source


1 PACKAGE BODY igs_en_psv_term_it_pkg AS
2 /* $Header: IGSEI78B.pls 120.0 2005/06/01 18:06:37 appldev noship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_en_psv_term_it%ROWTYPE;
6   new_references igs_en_psv_term_it%ROWTYPE;
7 
8 
9 PROCEDURE Check_Parent_Existance AS
10   /*----------------------------------------------------------------------------
11   ||  Created By : vkarthik
12   ||  Created On : 30-Mar-2004
13   ||  Purpose :
14   ||  Known limitations, enhancements or remarks :
15   ||  Change History :
16   ||  Who             When            What
17   ||
18   ----------------------------------------------------------------------------*/
19   BEGIN
20      IF (((old_references.cal_type = new_references.cal_type) AND
21          (old_references.sequence_number = new_references.sequence_number)) OR
22         ((new_references.cal_type IS NULL) OR
23          (new_references.sequence_number IS NULL))) THEN
24         NULL;
25      ELSE
26         IF NOT IGS_CA_INST_PKG.Get_PK_For_Validation (
27                 new_references.cal_type,
28                 new_references.sequence_number
29                 ) THEN
30                 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
31                 IGS_GE_MSG_STACK.ADD;
32                 App_Exception.Raise_Exception;
33         END IF;
34     END IF;
35 
36     IF (((old_references.course_cd = new_references.course_cd) AND
37          (old_references.version_number = new_references.version_number)) OR
38         ((new_references.course_cd IS NULL) OR
39          (new_references.version_number IS NULL))) THEN
40         NULL;
41      ELSE
42         IF NOT IGS_PS_VER_PKG.Get_PK_For_Validation (
43                 new_references.course_cd,
44                 new_references.version_number
45                 ) THEN
46                 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
47                 IGS_GE_MSG_STACK.ADD;
48                 App_Exception.Raise_Exception;
49         END IF;
50     END IF;
51 
52   END Check_Parent_Existance;
53 
54 
55   PROCEDURE set_column_values (
56     p_action                            IN     VARCHAR2,
57     x_rowid                             IN     VARCHAR2,
58     x_cal_type                          IN     VARCHAR2,
59     x_sequence_number                   IN     NUMBER,
60     x_course_cd                         IN     VARCHAR2,
61     x_version_number                    IN     NUMBER,
62     x_term_instruction_time             IN     NUMBER,
63     x_creation_date                     IN     DATE,
64     x_created_by                        IN     NUMBER,
65     x_last_update_date                  IN     DATE,
66     x_last_updated_by                   IN     NUMBER,
67     x_last_update_login                 IN     NUMBER
68   ) AS
69   /*
70   ||  Created By : [email protected]
71   ||  Created On : 01-MAR-2004
72   ||  Purpose : Initialises the Old and New references for the columns of the table.
73   ||  Known limitations, enhancements or remarks :
74   ||  Change History :
75   ||  Who             When            What
76   ||  (reverse chronological order - newest change first)
77   */
78 
79     CURSOR cur_old_ref_values IS
80       SELECT   *
81       FROM     igs_en_psv_term_it
82       WHERE    rowid = x_rowid;
83 
84   BEGIN
85 
86     l_rowid := x_rowid;
87 
88     -- Code for setting the Old and New Reference Values.
89     -- Populate Old Values.
90     OPEN cur_old_ref_values;
91     FETCH cur_old_ref_values INTO old_references;
92     IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
93       CLOSE cur_old_ref_values;
94       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
95       igs_ge_msg_stack.add;
96       app_exception.raise_exception;
97       RETURN;
98     END IF;
99     CLOSE cur_old_ref_values;
100 
101     -- Populate New Values.
102     new_references.cal_type                          := x_cal_type;
103     new_references.sequence_number                   := x_sequence_number;
104     new_references.course_cd                         := x_course_cd;
105     new_references.version_number                    := x_version_number;
106     new_references.term_instruction_time             := x_term_instruction_time;
107 
108     IF (p_action = 'UPDATE') THEN
109       new_references.creation_date                   := old_references.creation_date;
110       new_references.created_by                      := old_references.created_by;
111     ELSE
112       new_references.creation_date                   := x_creation_date;
113       new_references.created_by                      := x_created_by;
114     END IF;
115 
116     new_references.last_update_date                  := x_last_update_date;
117     new_references.last_updated_by                   := x_last_updated_by;
118     new_references.last_update_login                 := x_last_update_login;
119 
120   END set_column_values;
121 
122 
123   PROCEDURE GET_FK_IGS_CA_INST (
124     X_CAL_TYPE IN VARCHAR2,
125     X_SEQUENCE_NUMBER IN NUMBER
126     ) AS
127   /*
128   ||  Created By : vkarthik
129   ||  Created On : 10-MAR-2004
130   ||  Purpose : Validates the Foreign Key of the table.
131   ||  Known limitations, enhancements or remarks :
132   ||  Change History :
133   ||  Who             When            What
134   ||  (reverse chronological order - newest change first)
135   */
136     CURSOR cur_rowid IS
137       SELECT ROWID
138       FROM   igs_en_psv_term_it
139       WHERE  CAL_TYPE   = X_CAL_TYPE  AND
140              SEQUENCE_NUMBER = X_SEQUENCE_NUMBER ;
141     lv_rowid cur_rowid%ROWTYPE;
142 
143   BEGIN
144     OPEN cur_rowid;
145     FETCH cur_rowid INTO lv_rowid;
146     IF (cur_rowid%FOUND) THEN
147       Fnd_Message.Set_Name ('IGS', 'IGS_EN_PITO_CI_FK');
148       Igs_Ge_Msg_Stack.ADD;
149       CLOSE cur_rowid;
150       App_Exception.Raise_Exception;
151       RETURN;
152     END IF;
153     CLOSE cur_rowid;
154   END GET_FK_IGS_CA_INST;
155 
156   PROCEDURE GET_FK_IGS_PS_VER (
157     x_course_cd IN VARCHAR2,
158     x_version_number IN NUMBER
159     ) AS
160   /*
161   ||  Created By : vkarthik
162   ||  Created On : 10-MAR-2004
163   ||  Purpose : Validates the Foreign Key of the table.
164   ||  Known limitations, enhancements or remarks :
165   ||  Change History :
166   ||  Who             When            What
167   ||  (reverse chronological order - newest change first)
168   */
169     CURSOR cur_rowid IS
170       SELECT   ROWID
171       FROM     igs_en_psv_term_it
172       WHERE    course_cd = x_course_cd
173       AND      version_number = x_version_number ;
174     lv_rowid cur_rowid%ROWTYPE;
175   BEGIN
176     OPEN cur_rowid;
177     FETCH cur_rowid INTO lv_rowid;
178     IF (cur_rowid%FOUND) THEN
179       CLOSE cur_rowid;
180       Fnd_Message.Set_Name ('IGS', 'IGS_EN_PITO_CRV_FK');
181       IGS_GE_MSG_STACK.ADD;
182       App_Exception.Raise_Exception;
183       RETURN;
184     END IF;
185   CLOSE cur_rowid;
186   END GET_FK_IGS_PS_VER;
187 
188   FUNCTION get_pk_for_validation (
189     x_cal_type                          IN     VARCHAR2,
190     x_sequence_number                   IN     NUMBER,
191     x_course_cd                         IN     VARCHAR2,
192     x_version_number                    IN     NUMBER
193   ) RETURN BOOLEAN AS
194   /*
195   ||  Created By : [email protected]
196   ||  Created On : 01-MAR-2004
197   ||  Purpose : Validates the Primary Key of the table.
198   ||  Known limitations, enhancements or remarks :
199   ||  Change History :
200   ||  Who             When            What
201   ||  (reverse chronological order - newest change first)
202   */
203     CURSOR cur_rowid IS
204       SELECT   rowid
205       FROM     igs_en_psv_term_it
206       WHERE    cal_type = x_cal_type
207       AND      sequence_number = x_sequence_number
208       AND      course_cd = x_course_cd
209       AND      version_number = x_version_number
210       FOR UPDATE NOWAIT;
211 
212     lv_rowid cur_rowid%RowType;
213 
214   BEGIN
215 
216     OPEN cur_rowid;
217     FETCH cur_rowid INTO lv_rowid;
218     IF (cur_rowid%FOUND) THEN
219       CLOSE cur_rowid;
220       RETURN(TRUE);
221     ELSE
222       CLOSE cur_rowid;
223       RETURN(FALSE);
224     END IF;
225 
226   END get_pk_for_validation;
227 
228 
229   PROCEDURE before_dml (
230     p_action                            IN     VARCHAR2,
231     x_rowid                             IN     VARCHAR2,
232     x_cal_type                          IN     VARCHAR2,
233     x_sequence_number                   IN     NUMBER,
234     x_course_cd                         IN     VARCHAR2,
235     x_version_number                    IN     NUMBER,
236     x_term_instruction_time             IN     NUMBER,
237     x_creation_date                     IN     DATE,
238     x_created_by                        IN     NUMBER,
239     x_last_update_date                  IN     DATE,
240     x_last_updated_by                   IN     NUMBER,
241     x_last_update_login                 IN     NUMBER
242   ) AS
243   /*
244   ||  Created By : [email protected]
245   ||  Created On : 01-MAR-2004
246   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
247   ||            Trigger Handlers for the table, before any DML operation.
248   ||  Known limitations, enhancements or remarks :
249   ||  Change History :
250   ||  Who             When            What
251   ||  (reverse chronological order - newest change first)
252   */
253   BEGIN
254 
255     set_column_values (
256       p_action,
257       x_rowid,
258       x_cal_type,
259       x_sequence_number,
260       x_course_cd,
261       x_version_number,
262       x_term_instruction_time,
263       x_creation_date,
264       x_created_by,
265       x_last_update_date,
266       x_last_updated_by,
267       x_last_update_login
268     );
269 
270     IF (p_action = 'INSERT') THEN
271       -- Call all the procedures related to Before Insert.
272       IF ( get_pk_for_validation(
273              new_references.cal_type,
274              new_references.sequence_number,
275              new_references.course_cd,
276              new_references.version_number
277            )
278          ) THEN
279         fnd_message.set_name('IGS','IGS_EN_PRG_IT_EXISTS');
280         igs_ge_msg_stack.add;
281         app_exception.raise_exception;
282       END IF;
283       Check_parent_existance;
284     ELSIF (p_action = 'VALIDATE_INSERT') THEN
285       -- Call all the procedures related to Before Insert.
286       IF ( get_pk_for_validation (
287              new_references.cal_type,
288              new_references.sequence_number,
289              new_references.course_cd,
290              new_references.version_number
291            )
292          ) THEN
293         fnd_message.set_name('IGS','IGS_EN_PRG_IT_EXISTS');
294         igs_ge_msg_stack.add;
295         app_exception.raise_exception;
296       END IF;
297     END IF;
298 
299   END before_dml;
300 
301 
302   PROCEDURE insert_row (
303     x_rowid                             IN OUT NOCOPY VARCHAR2,
304     x_cal_type                          IN     VARCHAR2,
305     x_sequence_number                   IN     NUMBER,
306     x_course_cd                         IN     VARCHAR2,
307     x_version_number                    IN     NUMBER,
308     x_term_instruction_time             IN     NUMBER,
309     x_mode                              IN     VARCHAR2
310   ) AS
311   /*
312   ||  Created By : [email protected]
313   ||  Created On : 01-MAR-2004
314   ||  Purpose : Handles the INSERT DML logic for the table.
315   ||  Known limitations, enhancements or remarks :
316   ||  Change History :
317   ||  Who             When            What
318   ||  (reverse chronological order - newest change first)
319   */
320 
321     x_last_update_date           DATE;
322     x_last_updated_by            NUMBER;
323     x_last_update_login          NUMBER;
324 
325   BEGIN
326 
327     x_last_update_date := SYSDATE;
328     IF (x_mode = 'I') THEN
329       x_last_updated_by := 1;
330       x_last_update_login := 0;
331     ELSIF (x_mode = 'R') THEN
332       x_last_updated_by := fnd_global.user_id;
333       IF (x_last_updated_by IS NULL) THEN
334         x_last_updated_by := -1;
335       END IF;
336       x_last_update_login := fnd_global.login_id;
337       IF (x_last_update_login IS NULL) THEN
338         x_last_update_login := -1;
339       END IF;
340     ELSE
341       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
342       fnd_message.set_token ('ROUTINE', 'IGS_EN_PSV_TERM_IT_PKG.INSERT_ROW');
343       igs_ge_msg_stack.add;
344       app_exception.raise_exception;
345     END IF;
346 
347     before_dml(
348       p_action                            => 'INSERT',
349       x_rowid                             => x_rowid,
350       x_cal_type                          => x_cal_type,
351       x_sequence_number                   => x_sequence_number,
352       x_course_cd                         => x_course_cd,
353       x_version_number                    => x_version_number,
354       x_term_instruction_time             => x_term_instruction_time,
355       x_creation_date                     => x_last_update_date,
356       x_created_by                        => x_last_updated_by,
357       x_last_update_date                  => x_last_update_date,
358       x_last_updated_by                   => x_last_updated_by,
359       x_last_update_login                 => x_last_update_login
360     );
361 
362     INSERT INTO igs_en_psv_term_it (
363       cal_type,
364       sequence_number,
365       course_cd,
366       version_number,
367       term_instruction_time,
368       creation_date,
369       created_by,
370       last_update_date,
371       last_updated_by,
372       last_update_login
373     ) VALUES (
374       new_references.cal_type,
375       new_references.sequence_number,
376       new_references.course_cd,
377       new_references.version_number,
378       new_references.term_instruction_time,
379       x_last_update_date,
380       x_last_updated_by,
381       x_last_update_date,
382       x_last_updated_by,
383       x_last_update_login
384     ) RETURNING ROWID INTO x_rowid;
385 
386   END insert_row;
387 
388 
389   PROCEDURE lock_row (
390     x_rowid                             IN     VARCHAR2,
391     x_cal_type                          IN     VARCHAR2,
392     x_sequence_number                   IN     NUMBER,
393     x_course_cd                         IN     VARCHAR2,
394     x_version_number                    IN     NUMBER,
395     x_term_instruction_time             IN     NUMBER
396   ) AS
397   /*
398   ||  Created By : [email protected]
399   ||  Created On : 01-MAR-2004
400   ||  Purpose : Handles the LOCK mechanism for the table.
401   ||  Known limitations, enhancements or remarks :
402   ||  Change History :
403   ||  Who             When            What
404   ||  (reverse chronological order - newest change first)
405   */
406     CURSOR c1 IS
407       SELECT
408         term_instruction_time
409       FROM  igs_en_psv_term_it
410       WHERE rowid = x_rowid
411       FOR UPDATE NOWAIT;
412 
413     tlinfo c1%ROWTYPE;
414 
415   BEGIN
416 
417     OPEN c1;
418     FETCH c1 INTO tlinfo;
419     IF (c1%notfound) THEN
420       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
421       igs_ge_msg_stack.add;
422       CLOSE c1;
423       app_exception.raise_exception;
424       RETURN;
425     END IF;
426     CLOSE c1;
427 
428     IF (
429         (tlinfo.term_instruction_time = x_term_instruction_time)
430        ) THEN
431       NULL;
432     ELSE
433       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
434       igs_ge_msg_stack.add;
435       app_exception.raise_exception;
436     END IF;
437 
438     RETURN;
439 
440   END lock_row;
441 
442 
443   PROCEDURE update_row (
444     x_rowid                             IN     VARCHAR2,
445     x_cal_type                          IN     VARCHAR2,
446     x_sequence_number                   IN     NUMBER,
447     x_course_cd                         IN     VARCHAR2,
448     x_version_number                    IN     NUMBER,
449     x_term_instruction_time             IN     NUMBER,
450     x_mode                              IN     VARCHAR2
451   ) AS
452   /*
453   ||  Created By : [email protected]
454   ||  Created On : 01-MAR-2004
455   ||  Purpose : Handles the UPDATE DML logic for the table.
456   ||  Known limitations, enhancements or remarks :
457   ||  Change History :
458   ||  Who             When            What
459   ||  (reverse chronological order - newest change first)
460   */
461     x_last_update_date           DATE ;
462     x_last_updated_by            NUMBER;
463     x_last_update_login          NUMBER;
464 
465   BEGIN
466 
467     x_last_update_date := SYSDATE;
468     IF (X_MODE = 'I') THEN
469       x_last_updated_by := 1;
470       x_last_update_login := 0;
471     ELSIF (x_mode = 'R') THEN
472       x_last_updated_by := fnd_global.user_id;
473       IF x_last_updated_by IS NULL THEN
474         x_last_updated_by := -1;
475       END IF;
476       x_last_update_login := fnd_global.login_id;
477       IF (x_last_update_login IS NULL) THEN
478         x_last_update_login := -1;
479       END IF;
480     ELSE
481       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
482       fnd_message.set_token ('ROUTINE', 'IGS_EN_PSV_TERM_IT_PKG.UPDATE_ROW');
483       igs_ge_msg_stack.add;
484       app_exception.raise_exception;
485     END IF;
486 
487     before_dml(
488       p_action                            => 'UPDATE',
489       x_rowid                             => x_rowid,
490       x_cal_type                          => x_cal_type,
491       x_sequence_number                   => x_sequence_number,
492       x_course_cd                         => x_course_cd,
493       x_version_number                    => x_version_number,
494       x_term_instruction_time             => x_term_instruction_time,
495       x_creation_date                     => x_last_update_date,
496       x_created_by                        => x_last_updated_by,
497       x_last_update_date                  => x_last_update_date,
498       x_last_updated_by                   => x_last_updated_by,
499       x_last_update_login                 => x_last_update_login
500     );
501 
502     UPDATE igs_en_psv_term_it
503       SET
504         term_instruction_time             = new_references.term_instruction_time,
505         last_update_date                  = x_last_update_date,
506         last_updated_by                   = x_last_updated_by,
507         last_update_login                 = x_last_update_login
508       WHERE rowid = x_rowid;
509 
510     IF (SQL%NOTFOUND) THEN
511       RAISE NO_DATA_FOUND;
512     END IF;
513 
514   END update_row;
515 
516 
517   PROCEDURE add_row (
518     x_rowid                             IN OUT NOCOPY VARCHAR2,
519     x_cal_type                          IN     VARCHAR2,
520     x_sequence_number                   IN     NUMBER,
521     x_course_cd                         IN     VARCHAR2,
522     x_version_number                    IN     NUMBER,
523     x_term_instruction_time             IN     NUMBER,
524     x_mode                              IN     VARCHAR2
525   ) AS
526   /*
527   ||  Created By : [email protected]
528   ||  Created On : 01-MAR-2004
529   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
530   ||  Known limitations, enhancements or remarks :
531   ||  Change History :
532   ||  Who             When            What
533   ||  (reverse chronological order - newest change first)
534   */
535     CURSOR c1 IS
536       SELECT   rowid
537       FROM     igs_en_psv_term_it
538       WHERE    cal_type                          = x_cal_type
539       AND      sequence_number                   = x_sequence_number
540       AND      course_cd                         = x_course_cd
541       AND      version_number                    = x_version_number;
542 
543   BEGIN
544 
545     OPEN c1;
546     FETCH c1 INTO x_rowid;
547     IF (c1%NOTFOUND) THEN
548       CLOSE c1;
549 
550       insert_row (
551         x_rowid,
552         x_cal_type,
553         x_sequence_number,
554         x_course_cd,
555         x_version_number,
556         x_term_instruction_time,
557         x_mode
558       );
559       RETURN;
560     END IF;
561     CLOSE c1;
562 
563     update_row (
564       x_rowid,
565       x_cal_type,
566       x_sequence_number,
567       x_course_cd,
568       x_version_number,
569       x_term_instruction_time,
570       x_mode
571     );
572 
573   END add_row;
574 
575 
576   PROCEDURE delete_row (
577     x_rowid IN VARCHAR2
578   ) AS
579   /*
580   ||  Created By : [email protected]
581   ||  Created On : 01-MAR-2004
582   ||  Purpose : Handles the DELETE DML logic for the table.
583   ||  Known limitations, enhancements or remarks :
584   ||  Change History :
585   ||  Who             When            What
586   ||  (reverse chronological order - newest change first)
587   */
588   BEGIN
589 
590     before_dml (
591       p_action => 'DELETE',
592       x_rowid => x_rowid
593     );
594 
595     DELETE FROM igs_en_psv_term_it
596     WHERE rowid = x_rowid;
597 
598     IF (SQL%NOTFOUND) THEN
599       RAISE NO_DATA_FOUND;
600     END IF;
601 
602   END delete_row;
603 
604 
605 END igs_en_psv_term_it_pkg;