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_FI_F_TYPE_ACCTS_PKG

Source


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