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_SL_DL_CHG_FLD_PKG

Source


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