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_HE_OU_CC_PKG

Source


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