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_AS_SUA_REF_CDS_PKG

Source


1 PACKAGE BODY igs_as_sua_ref_cds_pkg AS
2 /* $Header: IGSDI84B.pls 120.1 2005/09/16 15:33:56 appldev noship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_as_sua_ref_cds%ROWTYPE;
6   new_references igs_as_sua_ref_cds%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2,
11     x_suar_id                           IN     NUMBER,
12     x_person_id                         IN     NUMBER,
13     x_course_cd                         IN     VARCHAR2,
14     x_uoo_id                            IN     NUMBER,
15     x_reference_code_id                 IN     NUMBER,
16     x_reference_cd_type                 IN     VARCHAR2,
17     x_reference_cd                      IN     VARCHAR2,
18     x_applied_course_cd                 IN     VARCHAR2,
19     x_deleted_date                      IN     DATE,
20     x_creation_date                     IN     DATE,
21     x_created_by                        IN     NUMBER,
22     x_last_update_date                  IN     DATE,
23     x_last_updated_by                   IN     NUMBER,
24     x_last_update_login                 IN     NUMBER
25   ) AS
26   /*
27   ||  Created By :
28   ||  Created On : 01-JUL-2005
29   ||  Purpose : Initialises the Old and New references for the columns of the table.
30   ||  Known limitations, enhancements or remarks :
31   ||  Change History :
32   ||  Who             When            What
33   ||  (reverse chronological order - newest change first)
34   */
35 
36     CURSOR cur_old_ref_values IS
37       SELECT   *
38       FROM     igs_as_sua_ref_cds
39       WHERE    rowid = x_rowid;
40 
41   BEGIN
42 
43     l_rowid := x_rowid;
44 
45     -- Code for setting the Old and New Reference Values.
46     -- Populate Old Values.
47     OPEN cur_old_ref_values;
48     FETCH cur_old_ref_values INTO old_references;
49     IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
50       CLOSE cur_old_ref_values;
51       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
52       igs_ge_msg_stack.add;
53       app_exception.raise_exception;
54       RETURN;
55     END IF;
56     CLOSE cur_old_ref_values;
57 
58     -- Populate New Values.
59     new_references.suar_id                           := x_suar_id;
60     new_references.person_id                         := x_person_id;
61     new_references.course_cd                         := x_course_cd;
62     new_references.uoo_id                            := x_uoo_id;
63     new_references.reference_code_id                 := x_reference_code_id;
64     new_references.reference_cd_type                 := x_reference_cd_type;
65     new_references.reference_cd                      := x_reference_cd;
66     new_references.applied_course_cd                 := x_applied_course_cd;
67     new_references.deleted_date                      := x_deleted_date;
68 
69     IF (p_action = 'UPDATE') THEN
70       new_references.creation_date                   := old_references.creation_date;
71       new_references.created_by                      := old_references.created_by;
72     ELSE
73       new_references.creation_date                   := x_creation_date;
74       new_references.created_by                      := x_created_by;
75     END IF;
76 
77     new_references.last_update_date                  := x_last_update_date;
78     new_references.last_updated_by                   := x_last_updated_by;
79     new_references.last_update_login                 := x_last_update_login;
80 
81   END set_column_values;
82 
83 
84   PROCEDURE check_uniqueness AS
85   /*
86   ||  Created By :
87   ||  Created On : 01-JUL-2005
88   ||  Purpose : Handles the Unique Constraint logic defined for the columns.
89   ||  Known limitations, enhancements or remarks :
90   ||  Change History :
91   ||  Who             When            What
92   ||  (reverse chronological order - newest change first)
93   */
94   BEGIN
95 
96     IF ( get_uk_for_validation (
97            new_references.person_id,
98            new_references.course_cd,
99            new_references.uoo_id,
100            new_references.reference_code_id,
101            new_references.applied_course_cd,
102            new_references.deleted_date
103          )
104        ) THEN
105       fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
106       igs_ge_msg_stack.add;
107       app_exception.raise_exception;
108     END IF;
109 
110   END check_uniqueness;
111 
112 
113   FUNCTION get_pk_for_validation (
114     x_suar_id                           IN     NUMBER
115   ) RETURN BOOLEAN AS
116   /*
117   ||  Created By :
118   ||  Created On : 01-JUL-2005
119   ||  Purpose : Validates the Primary Key of the table.
120   ||  Known limitations, enhancements or remarks :
121   ||  Change History :
122   ||  Who             When            What
123   ||  (reverse chronological order - newest change first)
124   */
125     CURSOR cur_rowid IS
126       SELECT   rowid
127       FROM     igs_as_sua_ref_cds
128       WHERE    suar_id = x_suar_id
129       FOR UPDATE NOWAIT;
130 
131     lv_rowid cur_rowid%RowType;
132 
133   BEGIN
134 
135     OPEN cur_rowid;
136     FETCH cur_rowid INTO lv_rowid;
137     IF (cur_rowid%FOUND) THEN
138       CLOSE cur_rowid;
139       RETURN(TRUE);
140     ELSE
141       CLOSE cur_rowid;
142       RETURN(FALSE);
143     END IF;
144 
145   END get_pk_for_validation;
146 
147 
148   FUNCTION get_uk_for_validation (
149     x_person_id                         IN     NUMBER,
150     x_course_cd                         IN     VARCHAR2,
151     x_uoo_id                            IN     NUMBER,
152     x_reference_code_id                 IN     NUMBER,
153     x_applied_course_cd                 IN     VARCHAR2,
154     x_deleted_date                      IN     DATE
155   ) RETURN BOOLEAN AS
156   /*
157   ||  Created By :
158   ||  Created On : 01-JUL-2005
159   ||  Purpose : Validates the Unique Keys of the table.
160   ||  Known limitations, enhancements or remarks :
161   ||  Change History :
162   ||  Who             When            What
163   ||  (reverse chronological order - newest change first)
164   */
165     CURSOR cur_rowid IS
166       SELECT   rowid
167       FROM     igs_as_sua_ref_cds
168       WHERE    person_id = x_person_id
169       AND      course_cd = x_course_cd
170       AND      uoo_id = x_uoo_id
171       AND      reference_code_id = x_reference_code_id
172       AND      applied_course_cd = x_applied_course_cd
173       AND      ((deleted_date = x_deleted_date) OR (deleted_date IS NULL AND x_deleted_date IS NULL))
174       AND      ((l_rowid IS NULL) OR (rowid <> l_rowid));
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         RETURN (true);
185         ELSE
186        CLOSE cur_rowid;
187       RETURN(FALSE);
188     END IF;
189 
190   END get_uk_for_validation ;
191 
192 
193   PROCEDURE before_dml (
194     p_action                            IN     VARCHAR2,
195     x_rowid                             IN     VARCHAR2,
196     x_suar_id                           IN     NUMBER,
197     x_person_id                         IN     NUMBER,
198     x_course_cd                         IN     VARCHAR2,
199     x_uoo_id                            IN     NUMBER,
200     x_reference_code_id                 IN     NUMBER,
201     x_reference_cd_type                 IN     VARCHAR2,
202     x_reference_cd                      IN     VARCHAR2,
203     x_applied_course_cd                 IN     VARCHAR2,
204     x_deleted_date                      IN     DATE,
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 :
213   ||  Created On : 01-JUL-2005
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_suar_id,
227       x_person_id,
228       x_course_cd,
229       x_uoo_id,
230       x_reference_code_id,
231       x_reference_cd_type,
232       x_reference_cd,
233       x_applied_course_cd,
234       x_deleted_date,
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.suar_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     ELSIF (p_action = 'UPDATE') THEN
254       -- Call all the procedures related to Before Update.
255       check_uniqueness;
256     ELSIF (p_action = 'VALIDATE_INSERT') THEN
257       -- Call all the procedures related to Before Insert.
258       IF ( get_pk_for_validation (
259              new_references.suar_id
260            )
261          ) THEN
262         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
263         igs_ge_msg_stack.add;
264         app_exception.raise_exception;
265       END IF;
266       check_uniqueness;
267     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
268       check_uniqueness;
269     END IF;
270 
271   END before_dml;
272 
273 
274   PROCEDURE insert_row (
275     x_rowid                             IN OUT NOCOPY VARCHAR2,
276     x_suar_id                           IN OUT NOCOPY NUMBER,
277     x_person_id                         IN     NUMBER,
278     x_course_cd                         IN     VARCHAR2,
279     x_uoo_id                            IN     NUMBER,
280     x_reference_code_id                 IN     NUMBER,
281     x_reference_cd_type                 IN     VARCHAR2,
282     x_reference_cd                      IN     VARCHAR2,
283     x_applied_course_cd                 IN     VARCHAR2,
284     x_deleted_date                      IN     DATE,
285     x_mode                              IN     VARCHAR2
286   ) AS
287   /*
288   ||  Created By :
289   ||  Created On : 01-JUL-2005
290   ||  Purpose : Handles the INSERT DML logic for the table.
291   ||  Known limitations, enhancements or remarks :
292   ||  Change History :
293   ||  Who             When            What
294   ||  (reverse chronological order - newest change first)
295   */
296 
297     x_last_update_date           DATE;
298     x_last_updated_by            NUMBER;
299     x_last_update_login          NUMBER;
300 
301   BEGIN
302 
303     x_last_update_date := SYSDATE;
304     IF (x_mode = 'I') THEN
305       x_last_updated_by := 1;
306       x_last_update_login := 0;
307     ELSIF (x_mode = 'R') THEN
308       x_last_updated_by := fnd_global.user_id;
309       IF (x_last_updated_by IS NULL) THEN
310         x_last_updated_by := -1;
311       END IF;
312       x_last_update_login := fnd_global.login_id;
313       IF (x_last_update_login IS NULL) THEN
314         x_last_update_login := -1;
315       END IF;
316     ELSE
317       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
318       fnd_message.set_token ('ROUTINE', 'IGS_AS_SUA_REF_CDS_PKG.INSERT_ROW');
319       igs_ge_msg_stack.add;
320       app_exception.raise_exception;
321     END IF;
322 
323     x_suar_id := NULL;
324 
325     before_dml(
326       p_action                            => 'INSERT',
327       x_rowid                             => x_rowid,
328       x_suar_id                           => x_suar_id,
329       x_person_id                         => x_person_id,
330       x_course_cd                         => x_course_cd,
331       x_uoo_id                            => x_uoo_id,
332       x_reference_code_id                 => x_reference_code_id,
333       x_reference_cd_type                 => x_reference_cd_type,
334       x_reference_cd                      => x_reference_cd,
335       x_applied_course_cd                 => x_applied_course_cd,
336       x_deleted_date                      => x_deleted_date,
337       x_creation_date                     => x_last_update_date,
338       x_created_by                        => x_last_updated_by,
339       x_last_update_date                  => x_last_update_date,
340       x_last_updated_by                   => x_last_updated_by,
341       x_last_update_login                 => x_last_update_login
342     );
343 
344     INSERT INTO igs_as_sua_ref_cds (
345       suar_id,
346       person_id,
347       course_cd,
348       uoo_id,
349       reference_code_id,
350       reference_cd_type,
351       reference_cd,
352       applied_course_cd,
353       deleted_date,
354       creation_date,
355       created_by,
356       last_update_date,
357       last_updated_by,
358       last_update_login
359     ) VALUES (
360       igs_as_sua_ref_cds_s.NEXTVAL,
361       new_references.person_id,
362       new_references.course_cd,
363       new_references.uoo_id,
364       new_references.reference_code_id,
365       new_references.reference_cd_type,
366       new_references.reference_cd,
367       new_references.applied_course_cd,
368       new_references.deleted_date,
369       x_last_update_date,
370       x_last_updated_by,
371       x_last_update_date,
372       x_last_updated_by,
373       x_last_update_login
374     ) RETURNING ROWID, suar_id INTO x_rowid, x_suar_id;
375 
376   END insert_row;
377 
378 
379   PROCEDURE lock_row (
380     x_rowid                             IN     VARCHAR2,
381     x_suar_id                           IN     NUMBER,
382     x_person_id                         IN     NUMBER,
383     x_course_cd                         IN     VARCHAR2,
387     x_reference_cd                      IN     VARCHAR2,
384     x_uoo_id                            IN     NUMBER,
385     x_reference_code_id                 IN     NUMBER,
386     x_reference_cd_type                 IN     VARCHAR2,
388     x_applied_course_cd                 IN     VARCHAR2,
389     x_deleted_date                      IN     DATE
390   ) AS
391   /*
392   ||  Created By :
393   ||  Created On : 01-JUL-2005
394   ||  Purpose : Handles the LOCK mechanism for the table.
395   ||  Known limitations, enhancements or remarks :
396   ||  Change History :
397   ||  Who             When            What
398   ||  (reverse chronological order - newest change first)
399   */
400     CURSOR c1 IS
401       SELECT
402         person_id,
403         course_cd,
404         uoo_id,
405         reference_code_id,
406         reference_cd_type,
407         reference_cd,
408         applied_course_cd,
409         deleted_date
410       FROM  igs_as_sua_ref_cds
411       WHERE rowid = x_rowid
412       FOR UPDATE NOWAIT;
413 
414     tlinfo c1%ROWTYPE;
415 
416   BEGIN
417 
418     OPEN c1;
419     FETCH c1 INTO tlinfo;
420     IF (c1%notfound) THEN
421       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
422       igs_ge_msg_stack.add;
423       CLOSE c1;
424       app_exception.raise_exception;
425       RETURN;
426     END IF;
427     CLOSE c1;
428 
429     IF (
430         (tlinfo.person_id = x_person_id)
431         AND (tlinfo.course_cd = x_course_cd)
432         AND (tlinfo.uoo_id = x_uoo_id)
433         AND (tlinfo.reference_code_id = x_reference_code_id)
434         AND (tlinfo.reference_cd_type = x_reference_cd_type)
435         AND (tlinfo.reference_cd = x_reference_cd)
436         AND (tlinfo.applied_course_cd = x_applied_course_cd)
437         AND ((tlinfo.deleted_date = x_deleted_date) OR ((tlinfo.deleted_date IS NULL) AND (X_deleted_date IS NULL)))
438        ) THEN
439       NULL;
440     ELSE
441       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
442       igs_ge_msg_stack.add;
443       app_exception.raise_exception;
444     END IF;
445 
446     RETURN;
447 
448   END lock_row;
449 
450 
451   PROCEDURE update_row (
452     x_rowid                             IN     VARCHAR2,
453     x_suar_id                           IN     NUMBER,
454     x_person_id                         IN     NUMBER,
455     x_course_cd                         IN     VARCHAR2,
456     x_uoo_id                            IN     NUMBER,
457     x_reference_code_id                 IN     NUMBER,
458     x_reference_cd_type                 IN     VARCHAR2,
459     x_reference_cd                      IN     VARCHAR2,
460     x_applied_course_cd                 IN     VARCHAR2,
461     x_deleted_date                      IN     DATE,
462     x_mode                              IN     VARCHAR2
463   ) AS
464   /*
465   ||  Created By :
466   ||  Created On : 01-JUL-2005
467   ||  Purpose : Handles the UPDATE DML logic for the table.
468   ||  Known limitations, enhancements or remarks :
469   ||  Change History :
470   ||  Who             When            What
471   ||  (reverse chronological order - newest change first)
472   */
473     x_last_update_date           DATE ;
474     x_last_updated_by            NUMBER;
475     x_last_update_login          NUMBER;
476 
477   BEGIN
478 
479     x_last_update_date := SYSDATE;
480     IF (X_MODE = 'I') THEN
481       x_last_updated_by := 1;
482       x_last_update_login := 0;
483     ELSIF (x_mode = 'R') THEN
484       x_last_updated_by := fnd_global.user_id;
485       IF x_last_updated_by IS NULL THEN
486         x_last_updated_by := -1;
487       END IF;
488       x_last_update_login := fnd_global.login_id;
489       IF (x_last_update_login IS NULL) THEN
490         x_last_update_login := -1;
491       END IF;
492     ELSE
493       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
494       fnd_message.set_token ('ROUTINE', 'IGS_AS_SUA_REF_CDS_PKG.UPDATE_ROW');
495       igs_ge_msg_stack.add;
496       app_exception.raise_exception;
497     END IF;
498 
499     before_dml(
500       p_action                            => 'UPDATE',
501       x_rowid                             => x_rowid,
502       x_suar_id                           => x_suar_id,
503       x_person_id                         => x_person_id,
504       x_course_cd                         => x_course_cd,
505       x_uoo_id                            => x_uoo_id,
506       x_reference_code_id                 => x_reference_code_id,
507       x_reference_cd_type                 => x_reference_cd_type,
508       x_reference_cd                      => x_reference_cd,
509       x_applied_course_cd                 => x_applied_course_cd,
510       x_deleted_date                      => x_deleted_date,
511       x_creation_date                     => x_last_update_date,
512       x_created_by                        => x_last_updated_by,
513       x_last_update_date                  => x_last_update_date,
514       x_last_updated_by                   => x_last_updated_by,
515       x_last_update_login                 => x_last_update_login
516     );
517 
518     UPDATE igs_as_sua_ref_cds
519       SET
520         person_id                         = new_references.person_id,
521         course_cd                         = new_references.course_cd,
522         uoo_id                            = new_references.uoo_id,
523         reference_code_id                 = new_references.reference_code_id,
524         reference_cd_type                 = new_references.reference_cd_type,
525         reference_cd                      = new_references.reference_cd,
526         applied_course_cd                 = new_references.applied_course_cd,
527         deleted_date                      = new_references.deleted_date,
528         last_update_date                  = x_last_update_date,
529         last_updated_by                   = x_last_updated_by,
530         last_update_login                 = x_last_update_login
531       WHERE rowid = x_rowid;
532 
533     IF (SQL%NOTFOUND) THEN
534       RAISE NO_DATA_FOUND;
535     END IF;
536 
537   END update_row;
538 
539 
540   PROCEDURE add_row (
541     x_rowid                             IN OUT NOCOPY VARCHAR2,
542     x_suar_id                           IN OUT NOCOPY NUMBER,
543     x_person_id                         IN     NUMBER,
544     x_course_cd                         IN     VARCHAR2,
545     x_uoo_id                            IN     NUMBER,
546     x_reference_code_id                 IN     NUMBER,
547     x_reference_cd_type                 IN     VARCHAR2,
548     x_reference_cd                      IN     VARCHAR2,
549     x_applied_course_cd                 IN     VARCHAR2,
550     x_deleted_date                      IN     DATE,
551     x_mode                              IN     VARCHAR2
552   ) AS
553   /*
554   ||  Created By :
555   ||  Created On : 01-JUL-2005
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_as_sua_ref_cds
565       WHERE    suar_id                           = x_suar_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_suar_id,
577         x_person_id,
578         x_course_cd,
579         x_uoo_id,
580         x_reference_code_id,
581         x_reference_cd_type,
582         x_reference_cd,
583         x_applied_course_cd,
584         x_deleted_date,
585         x_mode
586       );
587       RETURN;
588     END IF;
589     CLOSE c1;
590 
591     update_row (
592       x_rowid,
593       x_suar_id,
594       x_person_id,
595       x_course_cd,
596       x_uoo_id,
597       x_reference_code_id,
598       x_reference_cd_type,
599       x_reference_cd,
600       x_applied_course_cd,
601       x_deleted_date,
602       x_mode
603     );
604 
605   END add_row;
606 
607 
608   PROCEDURE delete_row (
609     x_rowid IN VARCHAR2
610   ) AS
611   /*
612   ||  Created By :
613   ||  Created On : 01-JUL-2005
614   ||  Purpose : Handles the DELETE DML logic for the table.
615   ||  Known limitations, enhancements or remarks :
616   ||  Change History :
617   ||  Who             When            What
618   ||  (reverse chronological order - newest change first)
619   */
620   BEGIN
621 
622     before_dml (
623       p_action => 'DELETE',
624       x_rowid => x_rowid
625     );
626 
627     DELETE FROM igs_as_sua_ref_cds
628     WHERE rowid = x_rowid;
629 
630     IF (SQL%NOTFOUND) THEN
631       RAISE NO_DATA_FOUND;
632     END IF;
633 
634   END delete_row;
635 
636  PROCEDURE Get_UFK_Igs_As_Sua_Ref_Cds (
637     x_reference_cd_type IN VARCHAR2,
638     x_reference_cd IN VARCHAR2
639     ) AS
640 
641   /*************************************************************
642   Created By : swaghmar
643   Date Created By : 11-July-2005
644   Purpose :
645   Know limitations, enhancements or remarks
646   Change History
647   Who             When            What
648 
649   (reverse chronological order - newest change first)
650   ***************************************************************/
651 
652     CURSOR cur_rowid IS
653       SELECT   rowid
654       FROM     igs_as_sua_ref_cds
655       WHERE    reference_cd_type = x_reference_cd_type
656       AND reference_cd = x_reference_cd;
657 
658     lv_rowid cur_rowid%RowType;
659 
660   BEGIN
661 
662     Open cur_rowid;
663     Fetch cur_rowid INTO lv_rowid;
664     IF (cur_rowid%FOUND) THEN
665       Close cur_rowid;
666       Fnd_Message.Set_Name ('IGS', 'IGS_AS_SUA_REF_FK');
667       IGS_GE_MSG_STACK.ADD;
668       App_Exception.Raise_Exception;
669       Return;
670     END IF;
671     Close cur_rowid;
672 
673   END Get_UFK_Igs_As_Sua_Ref_Cds;
674 
675 
676 END igs_as_sua_ref_cds_pkg;