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.IGS_UC_MAP_OFF_RESP_PKG

Source


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