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_OR_INST_STATS_PKG

Source


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