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_AP_TD_INST_REQRS_PKG

Source


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