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_REQD_FIELDS_PKG

Source


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