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_PS_ACCTS_PKG

Source


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