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_EDIT_REPORT_PKG

Source


1 PACKAGE BODY igf_sl_edit_report_pkg AS
2 /* $Header: IGFLI20B.pls 115.5 2002/11/28 14:26:02 nsidana ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igf_sl_edit_report_all%ROWTYPE;
6   new_references igf_sl_edit_report_all%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2    DEFAULT NULL,
11     x_edtr_id                           IN     NUMBER      DEFAULT NULL,
12     x_loan_number                       IN     VARCHAR2    DEFAULT NULL,
13     x_orig_chg_code                     IN     VARCHAR2    DEFAULT NULL,
14     x_sl_error_type                     IN     VARCHAR2    DEFAULT NULL,
15     x_sl_error_code                     IN     VARCHAR2    DEFAULT NULL,
16     x_field_name                        IN     VARCHAR2    DEFAULT NULL,
17     x_field_value                       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 : prchandr
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_EDIT_REPORT_ALL
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.edtr_id                           := x_edtr_id;
58     new_references.loan_number                       := x_loan_number;
59     new_references.orig_chg_code                     := x_orig_chg_code;
60     new_references.sl_error_type                     := x_sl_error_type;
61     new_references.sl_error_code                     := x_sl_error_code;
62     new_references.field_name                        := x_field_name;
63     new_references.field_value                       := x_field_value;
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   FUNCTION get_pk_for_validation (
81     x_edtr_id                           IN     NUMBER
82   ) RETURN BOOLEAN AS
83   /*
84   ||  Created By : prchandr
85   ||  Created On : 02-NOV-2000
86   ||  Purpose : Validates the Primary Key of the table.
87   ||  Known limitations, enhancements or remarks :
88   ||  Change History :
89   ||  Who             When            What
90   ||  (reverse chronological order - newest change first)
91   */
92     CURSOR cur_rowid IS
93       SELECT   rowid
94       FROM     igf_sl_edit_report_all
95       WHERE    edtr_id = x_edtr_id
96       FOR UPDATE NOWAIT;
97 
98     lv_rowid cur_rowid%RowType;
99 
100   BEGIN
101 
102     OPEN cur_rowid;
103     FETCH cur_rowid INTO lv_rowid;
104     IF (cur_rowid%FOUND) THEN
105       CLOSE cur_rowid;
106       RETURN(TRUE);
107     ELSE
108       CLOSE cur_rowid;
109       RETURN(FALSE);
110     END IF;
111 
112   END get_pk_for_validation;
113 
114 
115   PROCEDURE before_dml (
116     p_action                            IN     VARCHAR2,
117     x_rowid                             IN     VARCHAR2    DEFAULT NULL,
118     x_edtr_id                           IN     NUMBER      DEFAULT NULL,
119     x_loan_number                       IN     VARCHAR2    DEFAULT NULL,
120     x_orig_chg_code                     IN     VARCHAR2    DEFAULT NULL,
121     x_sl_error_type                     IN     VARCHAR2    DEFAULT NULL,
122     x_sl_error_code                     IN     VARCHAR2    DEFAULT NULL,
123     x_field_name                        IN     VARCHAR2    DEFAULT NULL,
124     x_field_value                       IN     VARCHAR2    DEFAULT NULL,
125     x_creation_date                     IN     DATE        DEFAULT NULL,
126     x_created_by                        IN     NUMBER      DEFAULT NULL,
127     x_last_update_date                  IN     DATE        DEFAULT NULL,
128     x_last_updated_by                   IN     NUMBER      DEFAULT NULL,
129     x_last_update_login                 IN     NUMBER      DEFAULT NULL
130   ) AS
131   /*
132   ||  Created By : prchandr
133   ||  Created On : 02-NOV-2000
134   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
135   ||            Trigger Handlers for the table, before any DML operation.
136   ||  Known limitations, enhancements or remarks :
137   ||  Change History :
138   ||  Who             When            What
139   ||  (reverse chronological order - newest change first)
140   */
141   BEGIN
142 
143     set_column_values (
144       p_action,
145       x_rowid,
146       x_edtr_id,
147       x_loan_number,
148       x_orig_chg_code,
149       x_sl_error_type,
150       x_sl_error_code,
151       x_field_name,
152       x_field_value,
153       x_creation_date,
154       x_created_by,
155       x_last_update_date,
156       x_last_updated_by,
157       x_last_update_login
158     );
159 
160     IF (p_action = 'INSERT') THEN
161       -- Call all the procedures related to Before Insert.
162       IF ( get_pk_for_validation(
163              new_references.edtr_id
164            )
165          ) THEN
166         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
167         igs_ge_msg_stack.add;
168         app_exception.raise_exception;
169       END IF;
170     ELSIF (p_action = 'VALIDATE_INSERT') THEN
171       -- Call all the procedures related to Before Insert.
172       IF ( get_pk_for_validation (
173              new_references.edtr_id
174            )
175          ) THEN
176         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
177         igs_ge_msg_stack.add;
178         app_exception.raise_exception;
179       END IF;
180     END IF;
181 
182   END before_dml;
183 
184 
185   PROCEDURE insert_row (
186     x_rowid                             IN OUT NOCOPY VARCHAR2,
187     x_edtr_id                           IN OUT NOCOPY NUMBER,
188     x_loan_number                       IN     VARCHAR2,
189     x_orig_chg_code                     IN     VARCHAR2,
190     x_sl_error_type                     IN     VARCHAR2,
191     x_sl_error_code                     IN     VARCHAR2,
192     x_field_name                        IN     VARCHAR2,
193     x_field_value                       IN     VARCHAR2,
194     x_mode                              IN     VARCHAR2 DEFAULT 'R'
195   ) AS
196   /*
197   ||  Created By : prchandr
198   ||  Created On : 02-NOV-2000
199   ||  Purpose : Handles the INSERT DML logic for the table.
200   ||  Known limitations, enhancements or remarks :
201   ||  Change History :
202   ||  Who             When            What
203   ||  (reverse chronological order - newest change first)
204   */
205     CURSOR c IS
206       SELECT   rowid
207       FROM     igf_sl_edit_report_all
208       WHERE    edtr_id                           = x_edtr_id;
209 
210     x_last_update_date           DATE;
211     x_last_updated_by            NUMBER;
212     x_last_update_login          NUMBER;
213     x_request_id                 NUMBER;
214     x_program_id                 NUMBER;
215     x_program_application_id     NUMBER;
216     x_program_update_date        DATE;
217     l_org_id			 igf_sl_edit_report_all.org_id%TYPE DEFAULT igf_aw_gen.get_org_id;
218 
219   BEGIN
220 
221     x_last_update_date := SYSDATE;
222     IF (x_mode = 'I') THEN
223       x_last_updated_by := 1;
224       x_last_update_login := 0;
225     ELSIF (x_mode = 'R') THEN
226       x_last_updated_by := fnd_global.user_id;
227       IF (x_last_updated_by IS NULL) THEN
228         x_last_updated_by := -1;
229       END IF;
230       x_last_update_login := fnd_global.login_id;
231       IF (x_last_update_login IS NULL) THEN
232         x_last_update_login := -1;
233       END IF;
234       x_request_id             := fnd_global.conc_request_id;
235       x_program_id             := fnd_global.conc_program_id;
236       x_program_application_id := fnd_global.prog_appl_id;
237 
238       IF (x_request_id = -1) THEN
239         x_request_id             := NULL;
240         x_program_id             := NULL;
241         x_program_application_id := NULL;
242         x_program_update_date    := NULL;
243       ELSE
244         x_program_update_date    := SYSDATE;
245       END IF;
246     ELSE
247       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
248       igs_ge_msg_stack.add;
249       app_exception.raise_exception;
250     END IF;
251 
252     SELECT igf_sl_edit_report_s.nextval
253       INTO x_edtr_id
254       FROM dual;
255 
256     before_dml(
257       p_action                            => 'INSERT',
258       x_rowid                             => x_rowid,
259       x_edtr_id                           => x_edtr_id,
260       x_loan_number                       => x_loan_number,
261       x_orig_chg_code                     => x_orig_chg_code,
262       x_sl_error_type                     => x_sl_error_type,
263       x_sl_error_code                     => x_sl_error_code,
264       x_field_name                        => x_field_name,
265       x_field_value                       => x_field_value,
266       x_creation_date                     => x_last_update_date,
267       x_created_by                        => x_last_updated_by,
268       x_last_update_date                  => x_last_update_date,
269       x_last_updated_by                   => x_last_updated_by,
270       x_last_update_login                 => x_last_update_login
271     );
272 
273     INSERT INTO igf_sl_edit_report_all(
274       edtr_id,
275       loan_number,
276       orig_chg_code,
277       sl_error_type,
278       sl_error_code,
279       field_name,
280       field_value,
281       creation_date,
282       created_by,
283       last_update_date,
284       last_updated_by,
285       last_update_login,
286       request_id,
287       program_id,
288       program_application_id,
289       program_update_date,
290       org_id
291     ) VALUES (
292       new_references.edtr_id,
293       new_references.loan_number,
294       new_references.orig_chg_code,
295       new_references.sl_error_type,
296       new_references.sl_error_code,
297       new_references.field_name,
298       new_references.field_value,
299       x_last_update_date,
300       x_last_updated_by,
301       x_last_update_date,
302       x_last_updated_by,
303       x_last_update_login ,
304       x_request_id,
305       x_program_id,
306       x_program_application_id,
307       x_program_update_date,
308       l_org_id
309     );
310 
311     OPEN c;
312     FETCH c INTO x_rowid;
313     IF (c%NOTFOUND) THEN
314       CLOSE c;
315       RAISE NO_DATA_FOUND;
316     END IF;
317     CLOSE c;
318 
319   END insert_row;
320 
321 
322   PROCEDURE lock_row (
323     x_rowid                             IN     VARCHAR2,
324     x_edtr_id                           IN     NUMBER,
325     x_loan_number                       IN     VARCHAR2,
326     x_orig_chg_code                     IN     VARCHAR2,
327     x_sl_error_type                     IN     VARCHAR2,
328     x_sl_error_code                     IN     VARCHAR2,
329     x_field_name                        IN     VARCHAR2,
330     x_field_value                       IN     VARCHAR2
331   ) AS
332   /*
333   ||  Created By : prchandr
334   ||  Created On : 02-NOV-2000
335   ||  Purpose : Handles the LOCK mechanism for the table.
336   ||  Known limitations, enhancements or remarks :
337   ||  Change History :
338   ||  Who             When            What
339   ||  (reverse chronological order - newest change first)
340   */
341     CURSOR c1 IS
342       SELECT
343         loan_number,
344         orig_chg_code,
345         sl_error_type,
346         sl_error_code,
347         field_name,
348         field_value,
349         org_id
350       FROM  igf_sl_edit_report_all
351       WHERE rowid = x_rowid
352       FOR UPDATE NOWAIT;
353 
354     tlinfo c1%ROWTYPE;
355 
356   BEGIN
357 
358     OPEN c1;
359     FETCH c1 INTO tlinfo;
360     IF (c1%notfound) THEN
361       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
362       igs_ge_msg_stack.add;
363       CLOSE c1;
364       app_exception.raise_exception;
365       RETURN;
366     END IF;
367     CLOSE c1;
368 
369     IF (
370         (tlinfo.loan_number = x_loan_number)
371         AND (tlinfo.orig_chg_code = x_orig_chg_code)
372         AND (tlinfo.sl_error_type = x_sl_error_type)
373         AND (tlinfo.sl_error_code = x_sl_error_code)
374         AND ((tlinfo.field_name = x_field_name) OR ((tlinfo.field_name IS NULL) AND (X_field_name IS NULL)))
375         AND ((tlinfo.field_value = x_field_value) OR ((tlinfo.field_value IS NULL) AND (X_field_value IS NULL)))
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_edtr_id                           IN     NUMBER,
392     x_loan_number                       IN     VARCHAR2,
393     x_orig_chg_code                     IN     VARCHAR2,
394     x_sl_error_type                     IN     VARCHAR2,
395     x_sl_error_code                     IN     VARCHAR2,
396     x_field_name                        IN     VARCHAR2,
397     x_field_value                       IN     VARCHAR2,
398     x_mode                              IN     VARCHAR2 DEFAULT 'R'
399   ) AS
400   /*
401   ||  Created By : prchandr
402   ||  Created On : 02-NOV-2000
403   ||  Purpose : Handles the UPDATE DML logic for the table.
404   ||  Known limitations, enhancements or remarks :
405   ||  Change History :
406   ||  Who             When            What
407   ||  (reverse chronological order - newest change first)
408   */
409     x_last_update_date           DATE ;
410     x_last_updated_by            NUMBER;
411     x_last_update_login          NUMBER;
412     x_request_id                 NUMBER;
413     x_program_id                 NUMBER;
414     x_program_application_id     NUMBER;
415     x_program_update_date        DATE;
416 
417   BEGIN
418 
419     x_last_update_date := SYSDATE;
420     IF (X_MODE = 'I') THEN
421       x_last_updated_by := 1;
422       x_last_update_login := 0;
423     ELSIF (x_mode = 'R') THEN
424       x_last_updated_by := fnd_global.user_id;
425       IF x_last_updated_by IS NULL THEN
426         x_last_updated_by := -1;
427       END IF;
428       x_last_update_login := fnd_global.login_id;
429       IF (x_last_update_login IS NULL) THEN
430         x_last_update_login := -1;
431       END IF;
432     ELSE
433       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
434       igs_ge_msg_stack.add;
435       app_exception.raise_exception;
436     END IF;
437 
438     before_dml(
439       p_action                            => 'UPDATE',
440       x_rowid                             => x_rowid,
441       x_edtr_id                           => x_edtr_id,
442       x_loan_number                       => x_loan_number,
443       x_orig_chg_code                     => x_orig_chg_code,
444       x_sl_error_type                     => x_sl_error_type,
445       x_sl_error_code                     => x_sl_error_code,
446       x_field_name                        => x_field_name,
447       x_field_value                       => x_field_value,
448       x_creation_date                     => x_last_update_date,
449       x_created_by                        => x_last_updated_by,
450       x_last_update_date                  => x_last_update_date,
451       x_last_updated_by                   => x_last_updated_by,
452       x_last_update_login                 => x_last_update_login
453     );
454 
455     IF (x_mode = 'R') THEN
456       x_request_id := fnd_global.conc_request_id;
457       x_program_id := fnd_global.conc_program_id;
458       x_program_application_id := fnd_global.prog_appl_id;
459       IF (x_request_id =  -1) THEN
460         x_request_id := old_references.request_id;
461         x_program_id := old_references.program_id;
462         x_program_application_id := old_references.program_application_id;
463         x_program_update_date := old_references.program_update_date;
464       ELSE
465         x_program_update_date := SYSDATE;
466       END IF;
467     END IF;
468 
469     UPDATE igf_sl_edit_report_all
470       SET
471         loan_number                       = new_references.loan_number,
472         orig_chg_code                     = new_references.orig_chg_code,
473         sl_error_type                     = new_references.sl_error_type,
474         sl_error_code                     = new_references.sl_error_code,
475         field_name                        = new_references.field_name,
476         field_value                       = new_references.field_value,
477         last_update_date                  = x_last_update_date,
478         last_updated_by                   = x_last_updated_by,
479         last_update_login                 = x_last_update_login ,
480         request_id                        = x_request_id,
481         program_id                        = x_program_id,
482         program_application_id            = x_program_application_id,
483         program_update_date               = x_program_update_date
484       WHERE rowid = x_rowid;
485 
486     IF (SQL%NOTFOUND) THEN
487       RAISE NO_DATA_FOUND;
488     END IF;
489 
490   END update_row;
491 
492 
493   PROCEDURE add_row (
494     x_rowid                             IN OUT NOCOPY VARCHAR2,
495     x_edtr_id                           IN OUT NOCOPY NUMBER,
496     x_loan_number                       IN     VARCHAR2,
497     x_orig_chg_code                     IN     VARCHAR2,
498     x_sl_error_type                     IN     VARCHAR2,
499     x_sl_error_code                     IN     VARCHAR2,
500     x_field_name                        IN     VARCHAR2,
501     x_field_value                       IN     VARCHAR2,
502     x_mode                              IN     VARCHAR2 DEFAULT 'R'
503   ) AS
504   /*
505   ||  Created By : prchandr
506   ||  Created On : 02-NOV-2000
507   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
508   ||  Known limitations, enhancements or remarks :
509   ||  Change History :
510   ||  Who             When            What
511   ||  (reverse chronological order - newest change first)
512   */
513     CURSOR c1 IS
514       SELECT   rowid
515       FROM     igf_sl_edit_report_all
516       WHERE    edtr_id                           = x_edtr_id;
517 
518   BEGIN
519 
520     OPEN c1;
521     FETCH c1 INTO x_rowid;
522     IF (c1%NOTFOUND) THEN
523       CLOSE c1;
524 
525       insert_row (
526         x_rowid,
527         x_edtr_id,
528         x_loan_number,
529         x_orig_chg_code,
530         x_sl_error_type,
531         x_sl_error_code,
532         x_field_name,
533         x_field_value,
534         x_mode
535       );
536       RETURN;
537     END IF;
538     CLOSE c1;
539 
540     update_row (
541       x_rowid,
542       x_edtr_id,
543       x_loan_number,
544       x_orig_chg_code,
545       x_sl_error_type,
546       x_sl_error_code,
547       x_field_name,
548       x_field_value,
549       x_mode
550     );
551 
552   END add_row;
553 
554 
555   PROCEDURE delete_row (
556     x_rowid IN VARCHAR2
557   ) AS
558   /*
559   ||  Created By : prchandr
560   ||  Created On : 02-NOV-2000
561   ||  Purpose : Handles the DELETE DML logic for the table.
562   ||  Known limitations, enhancements or remarks :
563   ||  Change History :
564   ||  Who             When            What
565   ||  (reverse chronological order - newest change first)
566   */
567   BEGIN
568 
569     before_dml (
570       p_action => 'DELETE',
571       x_rowid => x_rowid
572     );
573 
574     DELETE FROM igf_sl_edit_report_all
575     WHERE rowid = x_rowid;
576 
577     IF (SQL%NOTFOUND) THEN
578       RAISE NO_DATA_FOUND;
579     END IF;
580 
581   END delete_row;
582 
583 
584 END igf_sl_edit_report_pkg;