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_FI_1098T_BATCHS_PKG

Source


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