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_OR_UNIT_ACCTS_PKG

Source


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