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_PS_UNIT_ACCTS_PKG

Source


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