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.IGP_AC_ACCOUNTS_PKG

Source


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