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_BALANCES_HST_PKG

Source


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