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.IGF_AW_FN_RPKG_PRTY_PKG

Source


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