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_ACCT_CD_PKG

Source


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