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_DTLS_PKG

Source


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