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_AD_LOC_ACCTS_PKG

Source


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