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

Source


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