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_INST_CONTS_PKG

Source


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