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_POOUS_OU_CC_PKG

Source


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