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_UNT_OU_CC_PKG

Source


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