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_EXT_RUN_EXCP_PKG

Source


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