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_PR_UL_MARK_DTL_PKG

Source


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