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_LB_REC_TYPES_PKG

Source


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