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_ACC_CLASSES_PKG

Source


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