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_REPKG_PRTY_PKG

Source


1 PACKAGE BODY igf_aw_repkg_prty_pkg AS
2 /* $Header: IGFWI66B.pls 120.0 2005/06/01 15:52:03 appldev noship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igf_aw_repkg_prty%ROWTYPE;
6   new_references igf_aw_repkg_prty%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2,
11     x_fund_order_num                    IN     NUMBER,
12     x_sys_fund_type_code                IN     VARCHAR2,
13     x_fund_source_code                  IN     VARCHAR2,
14     x_sys_fund_code                     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 : 06-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_repkg_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.fund_order_num                    := x_fund_order_num;
55     new_references.sys_fund_type_code                := x_sys_fund_type_code;
56     new_references.fund_source_code                  := x_fund_source_code;
57     new_references.sys_fund_code                     := x_sys_fund_code;
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 : 06-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.sys_fund_type_code,
88            new_references.fund_source_code,
89            new_references.sys_fund_code
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_fund_order_num                    IN     NUMBER
102   ) RETURN BOOLEAN AS
103   /*
104   ||  Created By : ridas
105   ||  Created On : 06-OCT-2004
106   ||  Purpose : Validates the Primary Key of the table.
107   ||  Known limitations, enhancements or remarks :
108   ||  Change History :
109   ||  Who             When            What
110   ||  (reverse chronological order - newest change first)
111   */
112     CURSOR cur_rowid IS
113       SELECT   rowid
114       FROM     igf_aw_repkg_prty
115       WHERE    fund_order_num = x_fund_order_num
116       FOR UPDATE NOWAIT;
117 
118     lv_rowid cur_rowid%RowType;
119 
120   BEGIN
121 
122     OPEN cur_rowid;
123     FETCH cur_rowid INTO lv_rowid;
124     IF (cur_rowid%FOUND) THEN
125       CLOSE cur_rowid;
126       RETURN(TRUE);
127     ELSE
128       CLOSE cur_rowid;
129       RETURN(FALSE);
130     END IF;
131 
132   END get_pk_for_validation;
133 
134 
135   FUNCTION get_uk_for_validation (
136     x_sys_fund_type_code                IN     VARCHAR2,
137     x_fund_source_code                  IN     VARCHAR2,
138     x_sys_fund_code                     IN     VARCHAR2
139   ) RETURN BOOLEAN AS
140   /*
141   ||  Created By : ridas
142   ||  Created On : 06-OCT-2004
143   ||  Purpose : Validates the Unique Keys of 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     igf_aw_repkg_prty
152       WHERE    sys_fund_type_code = x_sys_fund_type_code
153       AND      fund_source_code = x_fund_source_code
154       AND      sys_fund_code = x_sys_fund_code
155       AND      ((l_rowid IS NULL) OR (rowid <> l_rowid));
156 
157     lv_rowid cur_rowid%RowType;
158 
159   BEGIN
160 
161     OPEN cur_rowid;
162     FETCH cur_rowid INTO lv_rowid;
163     IF (cur_rowid%FOUND) THEN
164       CLOSE cur_rowid;
165         RETURN (true);
166         ELSE
167        CLOSE cur_rowid;
168       RETURN(FALSE);
169     END IF;
170 
171   END get_uk_for_validation ;
172 
173 
174   PROCEDURE before_dml (
175     p_action                            IN     VARCHAR2,
176     x_rowid                             IN     VARCHAR2,
177     x_fund_order_num                    IN     NUMBER,
178     x_sys_fund_type_code                IN     VARCHAR2,
179     x_fund_source_code                  IN     VARCHAR2,
180     x_sys_fund_code                     IN     VARCHAR2,
181     x_creation_date                     IN     DATE,
182     x_created_by                        IN     NUMBER,
183     x_last_update_date                  IN     DATE,
184     x_last_updated_by                   IN     NUMBER,
185     x_last_update_login                 IN     NUMBER
186   ) AS
187   /*
188   ||  Created By : ridas
189   ||  Created On : 06-OCT-2004
190   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
191   ||            Trigger Handlers for the table, before any DML operation.
192   ||  Known limitations, enhancements or remarks :
193   ||  Change History :
194   ||  Who             When            What
195   ||  (reverse chronological order - newest change first)
196   */
197   BEGIN
198 
199     set_column_values (
200       p_action,
201       x_rowid,
202       x_fund_order_num,
203       x_sys_fund_type_code,
204       x_fund_source_code,
205       x_sys_fund_code,
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.fund_order_num
217            )
218          ) THEN
219         fnd_message.set_name('IGF','IGF_AW_PRTY_EXIST');
220         igs_ge_msg_stack.add;
221         app_exception.raise_exception;
222       END IF;
223       check_uniqueness;
224     ELSIF (p_action = 'UPDATE') THEN
225       -- Call all the procedures related to Before Update.
226       check_uniqueness;
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.fund_order_num
231            )
232          ) THEN
233         fnd_message.set_name('IGF','IGF_AW_PRTY_EXIST');
234         igs_ge_msg_stack.add;
235         app_exception.raise_exception;
236       END IF;
237       check_uniqueness;
238     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
239       check_uniqueness;
240     END IF;
241 
242   END before_dml;
243 
244 
245   PROCEDURE insert_row (
246     x_rowid                             IN OUT NOCOPY VARCHAR2,
247     x_fund_order_num                    IN OUT NOCOPY NUMBER,
248     x_sys_fund_type_code                IN     VARCHAR2,
249     x_fund_source_code                  IN     VARCHAR2,
250     x_sys_fund_code                     IN     VARCHAR2,
251     x_mode                              IN     VARCHAR2
252   ) AS
253   /*
254   ||  Created By : ridas
255   ||  Created On : 06-OCT-2004
256   ||  Purpose : Handles the INSERT DML logic for the table.
257   ||  Known limitations, enhancements or remarks :
258   ||  Change History :
259   ||  Who             When            What
260   ||  (reverse chronological order - newest change first)
261   */
262 
263     x_last_update_date           DATE;
264     x_last_updated_by            NUMBER;
265     x_last_update_login          NUMBER;
266 
267   BEGIN
268 
269     x_last_update_date := SYSDATE;
270     IF (x_mode = 'I') THEN
271       x_last_updated_by := 1;
272       x_last_update_login := 0;
273     ELSIF (x_mode = 'R') THEN
274       x_last_updated_by := fnd_global.user_id;
275       IF (x_last_updated_by IS NULL) THEN
276         x_last_updated_by := -1;
277       END IF;
278       x_last_update_login := fnd_global.login_id;
279       IF (x_last_update_login IS NULL) THEN
280         x_last_update_login := -1;
281       END IF;
282     ELSE
283       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
284       fnd_message.set_token ('ROUTINE', 'IGF_AW_REPKG_PRTY_PKG.INSERT_ROW');
285       igs_ge_msg_stack.add;
286       app_exception.raise_exception;
287     END IF;
288 
289     --x_fund_order_num := NULL;
290 
291     before_dml(
292       p_action                            => 'INSERT',
293       x_rowid                             => x_rowid,
294       x_fund_order_num                    => x_fund_order_num,
295       x_sys_fund_type_code                => x_sys_fund_type_code,
296       x_fund_source_code                  => x_fund_source_code,
297       x_sys_fund_code                     => x_sys_fund_code,
298       x_creation_date                     => x_last_update_date,
299       x_created_by                        => x_last_updated_by,
300       x_last_update_date                  => x_last_update_date,
301       x_last_updated_by                   => x_last_updated_by,
302       x_last_update_login                 => x_last_update_login
303     );
304 
305     INSERT INTO igf_aw_repkg_prty (
306       fund_order_num,
307       sys_fund_type_code,
308       fund_source_code,
309       sys_fund_code,
310       creation_date,
311       created_by,
312       last_update_date,
313       last_updated_by,
314       last_update_login
315     ) VALUES (
316       new_references.fund_order_num,
317       new_references.sys_fund_type_code,
318       new_references.fund_source_code,
319       new_references.sys_fund_code,
320       x_last_update_date,
321       x_last_updated_by,
322       x_last_update_date,
323       x_last_updated_by,
324       x_last_update_login
325     ) RETURNING ROWID, fund_order_num INTO x_rowid, x_fund_order_num;
326 
327   END insert_row;
328 
329 
330   PROCEDURE lock_row (
331     x_rowid                             IN     VARCHAR2,
332     x_fund_order_num                    IN     NUMBER,
333     x_sys_fund_type_code                IN     VARCHAR2,
334     x_fund_source_code                  IN     VARCHAR2,
335     x_sys_fund_code                     IN     VARCHAR2
336   ) AS
337   /*
338   ||  Created By : ridas
339   ||  Created On : 06-OCT-2004
340   ||  Purpose : Handles the LOCK mechanism for the table.
341   ||  Known limitations, enhancements or remarks :
342   ||  Change History :
343   ||  Who             When            What
344   ||  (reverse chronological order - newest change first)
345   */
346     CURSOR c1 IS
347       SELECT
348         sys_fund_type_code,
349         fund_source_code,
350         sys_fund_code
351       FROM  igf_aw_repkg_prty
352       WHERE rowid = x_rowid
353       FOR UPDATE NOWAIT;
354 
355     tlinfo c1%ROWTYPE;
356 
357   BEGIN
358 
359     OPEN c1;
360     FETCH c1 INTO tlinfo;
361     IF (c1%notfound) THEN
362       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
363       igs_ge_msg_stack.add;
364       CLOSE c1;
365       app_exception.raise_exception;
366       RETURN;
367     END IF;
368     CLOSE c1;
369 
373         AND (tlinfo.sys_fund_code = x_sys_fund_code)
370     IF (
371         (tlinfo.sys_fund_type_code = x_sys_fund_type_code)
372         AND (tlinfo.fund_source_code = x_fund_source_code)
374        ) THEN
375       NULL;
376     ELSE
377       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
378       igs_ge_msg_stack.add;
379       app_exception.raise_exception;
380     END IF;
381 
382     RETURN;
383 
384   END lock_row;
385 
386 
387   PROCEDURE update_row (
388     x_rowid                             IN     VARCHAR2,
389     x_fund_order_num                    IN     NUMBER,
390     x_sys_fund_type_code                IN     VARCHAR2,
391     x_fund_source_code                  IN     VARCHAR2,
392     x_sys_fund_code                     IN     VARCHAR2,
393     x_mode                              IN     VARCHAR2
394   ) AS
395   /*
396   ||  Created By : ridas
397   ||  Created On : 06-OCT-2004
398   ||  Purpose : Handles the UPDATE DML logic for the table.
399   ||  Known limitations, enhancements or remarks :
400   ||  Change History :
401   ||  Who             When            What
402   ||  (reverse chronological order - newest change first)
403   */
404     x_last_update_date           DATE ;
405     x_last_updated_by            NUMBER;
406     x_last_update_login          NUMBER;
407 
408   BEGIN
409 
410     x_last_update_date := SYSDATE;
411     IF (X_MODE = 'I') THEN
412       x_last_updated_by := 1;
413       x_last_update_login := 0;
414     ELSIF (x_mode = 'R') THEN
415       x_last_updated_by := fnd_global.user_id;
416       IF x_last_updated_by IS NULL THEN
417         x_last_updated_by := -1;
418       END IF;
419       x_last_update_login := fnd_global.login_id;
420       IF (x_last_update_login IS NULL) THEN
421         x_last_update_login := -1;
422       END IF;
423     ELSE
424       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
425       fnd_message.set_token ('ROUTINE', 'IGF_AW_REPKG_PRTY_PKG.UPDATE_ROW');
426       igs_ge_msg_stack.add;
427       app_exception.raise_exception;
428     END IF;
429 
430     before_dml(
431       p_action                            => 'UPDATE',
432       x_rowid                             => x_rowid,
433       x_fund_order_num                    => x_fund_order_num,
434       x_sys_fund_type_code                => x_sys_fund_type_code,
435       x_fund_source_code                  => x_fund_source_code,
436       x_sys_fund_code                     => x_sys_fund_code,
437       x_creation_date                     => x_last_update_date,
438       x_created_by                        => x_last_updated_by,
439       x_last_update_date                  => x_last_update_date,
440       x_last_updated_by                   => x_last_updated_by,
441       x_last_update_login                 => x_last_update_login
442     );
443 
444     UPDATE igf_aw_repkg_prty
445       SET
446         fund_order_num                    = new_references.fund_order_num,
447         sys_fund_type_code                = new_references.sys_fund_type_code,
448         fund_source_code                  = new_references.fund_source_code,
449         sys_fund_code                     = new_references.sys_fund_code,
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_fund_order_num                    IN OUT NOCOPY NUMBER,
465     x_sys_fund_type_code                IN     VARCHAR2,
466     x_fund_source_code                  IN     VARCHAR2,
467     x_sys_fund_code                     IN     VARCHAR2,
468     x_mode                              IN     VARCHAR2
469   ) AS
470   /*
471   ||  Created By : ridas
472   ||  Created On : 06-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_repkg_prty
482       WHERE    fund_order_num                    = x_fund_order_num;
483 
484   BEGIN
485 
486     OPEN c1;
487     FETCH c1 INTO x_rowid;
488     IF (c1%NOTFOUND) THEN
489       CLOSE c1;
490 
491       insert_row (
492         x_rowid,
493         x_fund_order_num,
494         x_sys_fund_type_code,
495         x_fund_source_code,
496         x_sys_fund_code,
497         x_mode
498       );
499       RETURN;
500     END IF;
501     CLOSE c1;
502 
503     update_row (
504       x_rowid,
505       x_fund_order_num,
506       x_sys_fund_type_code,
507       x_fund_source_code,
508       x_sys_fund_code,
509       x_mode
510     );
511 
512   END add_row;
513 
514 
515   PROCEDURE delete_row (
516     x_rowid IN VARCHAR2
517   ) AS
518   /*
519   ||  Created By : ridas
520   ||  Created On : 06-OCT-2004
521   ||  Purpose : Handles the DELETE DML logic for the table.
522   ||  Known limitations, enhancements or remarks :
523   ||  Change History :
524   ||  Who             When            What
525   ||  (reverse chronological order - newest change first)
526   */
527   BEGIN
528 
529     before_dml (
530       p_action => 'DELETE',
531       x_rowid => x_rowid
532     );
533 
534     DELETE FROM igf_aw_repkg_prty
535     WHERE rowid = x_rowid;
536 
537     IF (SQL%NOTFOUND) THEN
538       RAISE NO_DATA_FOUND;
539     END IF;
540 
541   END delete_row;
542 
543 
544 END igf_aw_repkg_prty_pkg;