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_UC_OFFER_CONDS_PKG

Source


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