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_PDET_RESP_PKG

Source


1 PACKAGE BODY igf_sl_dl_pdet_resp_pkg AS
2 /* $Header: IGFLI27B.pls 115.4 2002/11/28 14:27:40 nsidana noship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igf_sl_dl_pdet_resp%ROWTYPE;
6   new_references igf_sl_dl_pdet_resp%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2    DEFAULT NULL,
11     x_dlpnr_id                          IN     NUMBER      DEFAULT NULL,
12     x_dlpdr_id                          IN     NUMBER      DEFAULT NULL,
13     x_disb_gross_amt                    IN     NUMBER      DEFAULT NULL,
14     x_creation_date                     IN     DATE        DEFAULT NULL,
15     x_created_by                        IN     NUMBER      DEFAULT NULL,
16     x_last_update_date                  IN     DATE        DEFAULT NULL,
17     x_last_updated_by                   IN     NUMBER      DEFAULT NULL,
18     x_last_update_login                 IN     NUMBER      DEFAULT NULL
19   ) AS
20   /*
21   ||  Created By : viramali
22   ||  Created On : 10-MAY-2001
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     IGF_SL_DL_PDET_RESP
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.dlpnr_id                          := x_dlpnr_id;
54     new_references.dlpdr_id                          := x_dlpdr_id;
55     new_references.disb_gross_amt                    := x_disb_gross_amt;
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_parent_existance AS
73   /*
74   ||  Created By : viramali
75   ||  Created On : 10-MAY-2001
76   ||  Purpose : Checks for the existance of Parent records.
77   ||  Known limitations, enhancements or remarks :
78   ||  Change History :
79   ||  Who             When            What
80   ||  (reverse chronological order - newest change first)
81   */
82   BEGIN
83 
84     IF (((old_references.dlpnr_id = new_references.dlpnr_id)) OR
85         ((new_references.dlpnr_id IS NULL))) THEN
86       NULL;
87     ELSIF NOT igf_sl_dl_pnote_resp_pkg.get_pk_for_validation (
88                 new_references.dlpnr_id
89               ) THEN
90       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
91       igs_ge_msg_stack.add;
92       app_exception.raise_exception;
93     END IF;
94 
95   END check_parent_existance;
96 
97 
98   FUNCTION get_pk_for_validation (
99     x_dlpnr_id                          IN     NUMBER,
100     x_dlpdr_id                          IN     NUMBER
101   ) RETURN BOOLEAN AS
102   /*
103   ||  Created By : viramali
104   ||  Created On : 10-MAY-2001
105   ||  Purpose : Validates the Primary Key of the table.
106   ||  Known limitations, enhancements or remarks :
107   ||  Change History :
108   ||  Who             When            What
109   ||  (reverse chronological order - newest change first)
110   */
111     CURSOR cur_rowid IS
112       SELECT   rowid
113       FROM     igf_sl_dl_pdet_resp
114       WHERE    dlpnr_id = x_dlpnr_id
115       AND      dlpdr_id = x_dlpdr_id
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   PROCEDURE get_fk_igf_sl_dl_pnote_resp (
136     x_dlpnr_id                          IN     NUMBER
137   ) AS
138   /*
139   ||  Created By : viramali
140   ||  Created On : 10-MAY-2001
141   ||  Purpose : Validates the Foreign Keys for the table.
142   ||  Known limitations, enhancements or remarks :
143   ||  Change History :
144   ||  Who             When            What
145   ||  (reverse chronological order - newest change first)
146   */
147     CURSOR cur_rowid IS
148       SELECT   rowid
149       FROM     igf_sl_dl_pdet_resp
150       WHERE   ((dlpnr_id = x_dlpnr_id));
151 
152     lv_rowid cur_rowid%RowType;
153 
154   BEGIN
155 
156     OPEN cur_rowid;
157     FETCH cur_rowid INTO lv_rowid;
158     IF (cur_rowid%FOUND) THEN
159       CLOSE cur_rowid;
160       fnd_message.set_name ('IGF', 'IGF_SL_DLPDR_DLPNR_FK');
161       igs_ge_msg_stack.add;
162       app_exception.raise_exception;
163       RETURN;
164     END IF;
165     CLOSE cur_rowid;
166 
167   END get_fk_igf_sl_dl_pnote_resp;
168 
169 
170   PROCEDURE before_dml (
171     p_action                            IN     VARCHAR2,
172     x_rowid                             IN     VARCHAR2    DEFAULT NULL,
173     x_dlpnr_id                          IN     NUMBER      DEFAULT NULL,
174     x_dlpdr_id                          IN     NUMBER      DEFAULT NULL,
175     x_disb_gross_amt                    IN     NUMBER      DEFAULT NULL,
176     x_creation_date                     IN     DATE        DEFAULT NULL,
177     x_created_by                        IN     NUMBER      DEFAULT NULL,
178     x_last_update_date                  IN     DATE        DEFAULT NULL,
179     x_last_updated_by                   IN     NUMBER      DEFAULT NULL,
180     x_last_update_login                 IN     NUMBER      DEFAULT NULL
181   ) AS
182   /*
183   ||  Created By : viramali
184   ||  Created On : 10-MAY-2001
185   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
186   ||            Trigger Handlers for the table, before any DML operation.
187   ||  Known limitations, enhancements or remarks :
188   ||  Change History :
189   ||  Who             When            What
190   ||  (reverse chronological order - newest change first)
191   */
192   BEGIN
193 
194     set_column_values (
195       p_action,
196       x_rowid,
197       x_dlpnr_id,
198       x_dlpdr_id,
199       x_disb_gross_amt,
200       x_creation_date,
201       x_created_by,
202       x_last_update_date,
203       x_last_updated_by,
204       x_last_update_login
205     );
206 
207     IF (p_action = 'INSERT') THEN
208       -- Call all the procedures related to Before Insert.
209       IF ( get_pk_for_validation(
210              new_references.dlpnr_id,
211              new_references.dlpdr_id
212            )
213          ) THEN
214         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
215         igs_ge_msg_stack.add;
216         app_exception.raise_exception;
217       END IF;
218       check_parent_existance;
219     ELSIF (p_action = 'UPDATE') THEN
220       -- Call all the procedures related to Before Update.
221       check_parent_existance;
222     ELSIF (p_action = 'VALIDATE_INSERT') THEN
223       -- Call all the procedures related to Before Insert.
224       IF ( get_pk_for_validation (
225              new_references.dlpnr_id,
226              new_references.dlpdr_id
227            )
228          ) THEN
229         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
230         igs_ge_msg_stack.add;
231         app_exception.raise_exception;
232       END IF;
233     END IF;
234 
235   END before_dml;
236 
237 
238   PROCEDURE insert_row (
239     x_rowid                             IN OUT NOCOPY VARCHAR2,
240     x_dlpnr_id                          IN     NUMBER,
241     x_dlpdr_id                          IN     NUMBER,
242     x_disb_gross_amt                    IN     NUMBER,
243     x_mode                              IN     VARCHAR2 DEFAULT 'R'
244   ) AS
245   /*
246   ||  Created By : viramali
247   ||  Created On : 10-MAY-2001
248   ||  Purpose : Handles the INSERT DML logic for the table.
249   ||  Known limitations, enhancements or remarks :
250   ||  Change History :
251   ||  Who             When            What
252   ||  (reverse chronological order - newest change first)
253   */
254     CURSOR c IS
255       SELECT   rowid
256       FROM     igf_sl_dl_pdet_resp
257       WHERE    dlpnr_id                          = x_dlpnr_id
258       AND      dlpdr_id                          = x_dlpdr_id;
259 
260     x_last_update_date           DATE;
261     x_last_updated_by            NUMBER;
262     x_last_update_login          NUMBER;
263     x_request_id                 NUMBER;
264     x_program_id                 NUMBER;
265     x_program_application_id     NUMBER;
266     x_program_update_date        DATE;
267 
268   BEGIN
269 
270     x_last_update_date := SYSDATE;
271     IF (x_mode = 'I') THEN
272       x_last_updated_by := 1;
273       x_last_update_login := 0;
274     ELSIF (x_mode = 'R') THEN
275       x_last_updated_by := fnd_global.user_id;
276       IF (x_last_updated_by IS NULL) THEN
277         x_last_updated_by := -1;
278       END IF;
279       x_last_update_login := fnd_global.login_id;
280       IF (x_last_update_login IS NULL) THEN
281         x_last_update_login := -1;
282       END IF;
283       x_request_id             := fnd_global.conc_request_id;
284       x_program_id             := fnd_global.conc_program_id;
285       x_program_application_id := fnd_global.prog_appl_id;
286 
287       IF (x_request_id = -1) THEN
288         x_request_id             := NULL;
289         x_program_id             := NULL;
293         x_program_update_date    := SYSDATE;
290         x_program_application_id := NULL;
291         x_program_update_date    := NULL;
292       ELSE
294       END IF;
295     ELSE
296       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
297       igs_ge_msg_stack.add;
298       app_exception.raise_exception;
299     END IF;
300 
301     before_dml(
302       p_action                            => 'INSERT',
303       x_rowid                             => x_rowid,
304       x_dlpnr_id                          => x_dlpnr_id,
305       x_dlpdr_id                          => x_dlpdr_id,
306       x_disb_gross_amt                    => x_disb_gross_amt,
307       x_creation_date                     => x_last_update_date,
308       x_created_by                        => x_last_updated_by,
309       x_last_update_date                  => x_last_update_date,
310       x_last_updated_by                   => x_last_updated_by,
311       x_last_update_login                 => x_last_update_login
312     );
313 
314     INSERT INTO igf_sl_dl_pdet_resp (
315       dlpnr_id,
316       dlpdr_id,
317       disb_gross_amt,
318       creation_date,
319       created_by,
320       last_update_date,
321       last_updated_by,
322       last_update_login,
323       request_id,
324       program_id,
325       program_application_id,
326       program_update_date
327     ) VALUES (
328       new_references.dlpnr_id,
329       new_references.dlpdr_id,
330       new_references.disb_gross_amt,
331       x_last_update_date,
332       x_last_updated_by,
333       x_last_update_date,
334       x_last_updated_by,
335       x_last_update_login ,
336       x_request_id,
337       x_program_id,
338       x_program_application_id,
339       x_program_update_date
340     );
341 
342     OPEN c;
343     FETCH c INTO x_rowid;
344     IF (c%NOTFOUND) THEN
345       CLOSE c;
346       RAISE NO_DATA_FOUND;
347     END IF;
348     CLOSE c;
349 
350   END insert_row;
351 
352 
353   PROCEDURE lock_row (
354     x_rowid                             IN     VARCHAR2,
355     x_dlpnr_id                          IN     NUMBER,
356     x_dlpdr_id                          IN     NUMBER,
357     x_disb_gross_amt                    IN     NUMBER
358   ) AS
359   /*
360   ||  Created By : viramali
361   ||  Created On : 10-MAY-2001
362   ||  Purpose : Handles the LOCK mechanism for the table.
363   ||  Known limitations, enhancements or remarks :
364   ||  Change History :
365   ||  Who             When            What
366   ||  (reverse chronological order - newest change first)
367   */
368     CURSOR c1 IS
369       SELECT
370         disb_gross_amt
371       FROM  igf_sl_dl_pdet_resp
372       WHERE rowid = x_rowid
373       FOR UPDATE NOWAIT;
374 
375     tlinfo c1%ROWTYPE;
376 
377   BEGIN
378 
379     OPEN c1;
380     FETCH c1 INTO tlinfo;
381     IF (c1%notfound) THEN
385       app_exception.raise_exception;
382       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
383       igs_ge_msg_stack.add;
384       CLOSE c1;
386       RETURN;
387     END IF;
388     CLOSE c1;
389 
390     IF (
391         ((tlinfo.disb_gross_amt = x_disb_gross_amt) OR ((tlinfo.disb_gross_amt IS NULL) AND (X_disb_gross_amt IS NULL)))
392        ) THEN
393       NULL;
394     ELSE
395       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
396       igs_ge_msg_stack.add;
397       app_exception.raise_exception;
398     END IF;
399 
400     RETURN;
401 
402   END lock_row;
403 
404 
405   PROCEDURE update_row (
406     x_rowid                             IN     VARCHAR2,
407     x_dlpnr_id                          IN     NUMBER,
408     x_dlpdr_id                          IN     NUMBER,
409     x_disb_gross_amt                    IN     NUMBER,
410     x_mode                              IN     VARCHAR2 DEFAULT 'R'
411   ) AS
412   /*
413   ||  Created By : viramali
414   ||  Created On : 10-MAY-2001
415   ||  Purpose : Handles the UPDATE DML logic for the table.
416   ||  Known limitations, enhancements or remarks :
417   ||  Change History :
418   ||  Who             When            What
419   ||  (reverse chronological order - newest change first)
420   */
421     x_last_update_date           DATE ;
422     x_last_updated_by            NUMBER;
423     x_last_update_login          NUMBER;
424     x_request_id                 NUMBER;
425     x_program_id                 NUMBER;
426     x_program_application_id     NUMBER;
427     x_program_update_date        DATE;
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       igs_ge_msg_stack.add;
447       app_exception.raise_exception;
448     END IF;
449 
450     before_dml(
451       p_action                            => 'UPDATE',
452       x_rowid                             => x_rowid,
453       x_dlpnr_id                          => x_dlpnr_id,
454       x_dlpdr_id                          => x_dlpdr_id,
455       x_disb_gross_amt                    => x_disb_gross_amt,
456       x_creation_date                     => x_last_update_date,
457       x_created_by                        => x_last_updated_by,
458       x_last_update_date                  => x_last_update_date,
459       x_last_updated_by                   => x_last_updated_by,
460       x_last_update_login                 => x_last_update_login
461     );
462 
463     IF (x_mode = 'R') THEN
464       x_request_id := fnd_global.conc_request_id;
465       x_program_id := fnd_global.conc_program_id;
466       x_program_application_id := fnd_global.prog_appl_id;
467       IF (x_request_id =  -1) THEN
468         x_request_id := old_references.request_id;
469         x_program_id := old_references.program_id;
470         x_program_application_id := old_references.program_application_id;
471         x_program_update_date := old_references.program_update_date;
472       ELSE
473         x_program_update_date := SYSDATE;
474       END IF;
475     END IF;
476 
477     UPDATE igf_sl_dl_pdet_resp
478       SET
479         disb_gross_amt                    = new_references.disb_gross_amt,
480         last_update_date                  = x_last_update_date,
481         last_updated_by                   = x_last_updated_by,
482         last_update_login                 = x_last_update_login ,
483         request_id                        = x_request_id,
484         program_id                        = x_program_id,
485         program_application_id            = x_program_application_id,
486         program_update_date               = x_program_update_date
487       WHERE rowid = x_rowid;
488 
489     IF (SQL%NOTFOUND) THEN
490       RAISE NO_DATA_FOUND;
491     END IF;
492 
493   END update_row;
494 
495 
496   PROCEDURE add_row (
497     x_rowid                             IN OUT NOCOPY VARCHAR2,
498     x_dlpnr_id                          IN     NUMBER,
499     x_dlpdr_id                          IN     NUMBER,
500     x_disb_gross_amt                    IN     NUMBER,
501     x_mode                              IN     VARCHAR2 DEFAULT 'R'
502   ) AS
503   /*
504   ||  Created By : viramali
505   ||  Created On : 10-MAY-2001
506   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
507   ||  Known limitations, enhancements or remarks :
508   ||  Change History :
509   ||  Who             When            What
510   ||  (reverse chronological order - newest change first)
511   */
512     CURSOR c1 IS
513       SELECT   rowid
514       FROM     igf_sl_dl_pdet_resp
515       WHERE    dlpnr_id                          = x_dlpnr_id
516       AND      dlpdr_id                          = x_dlpdr_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_dlpnr_id,
528         x_dlpdr_id,
529         x_disb_gross_amt,
530         x_mode
531       );
532       RETURN;
533     END IF;
534     CLOSE c1;
535 
536     update_row (
537       x_rowid,
538       x_dlpnr_id,
539       x_dlpdr_id,
540       x_disb_gross_amt,
541       x_mode
542     );
543 
544   END add_row;
545 
546 
547   PROCEDURE delete_row (
548     x_rowid IN VARCHAR2
549   ) AS
550   /*
551   ||  Created By : viramali
552   ||  Created On : 10-MAY-2001
553   ||  Purpose : Handles the DELETE DML logic for the table.
554   ||  Known limitations, enhancements or remarks :
555   ||  Change History :
556   ||  Who             When            What
557   ||  (reverse chronological order - newest change first)
558   */
559   BEGIN
560 
561     before_dml (
562       p_action => 'DELETE',
563       x_rowid => x_rowid
564     );
565 
566     DELETE FROM igf_sl_dl_pdet_resp
567     WHERE rowid = x_rowid;
568 
569     IF (SQL%NOTFOUND) THEN
570       RAISE NO_DATA_FOUND;
571     END IF;
572 
573   END delete_row;
574 
575 
576 END igf_sl_dl_pdet_resp_pkg;