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.
[Home] [Help]
PACKAGE BODY: APPS.IGS_HE_CODE_ASS_VAL_PKG

Source


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