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_CL_RESP_R3_DTLS_PKG

Source


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