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_CIT_LD_OVRD_PKG

Source


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