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_SERVIC_PLAN_PKG

Source


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