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_R2_DTLS_PKG

Source


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