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_ST_SPA_CC_PKG

Source


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