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_FI_TP_RET_SCHD_PKG

Source


1 PACKAGE BODY igs_fi_tp_ret_schd_pkg AS
2 /* $Header: IGSSIE7B.pls 120.0 2005/06/02 04:20:06 appldev noship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_fi_tp_ret_schd%ROWTYPE;
6   new_references igs_fi_tp_ret_schd%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2,
11     x_ftci_teach_retention_id           IN     NUMBER,
12     x_teach_cal_type                    IN     VARCHAR2,
13     x_teach_ci_sequence_number          IN     NUMBER,
14     x_fee_cal_type                      IN     VARCHAR2,
15     x_fee_ci_sequence_number            IN     NUMBER,
16     x_fee_type                          IN     VARCHAR2,
17     x_dt_alias                          IN     VARCHAR2,
18     x_dai_sequence_number               IN     NUMBER,
19     x_ret_percentage                    IN     NUMBER,
20     x_ret_amount                        IN     NUMBER,
21     x_creation_date                     IN     DATE,
22     x_created_by                        IN     NUMBER,
23     x_last_update_date                  IN     DATE,
24     x_last_updated_by                   IN     NUMBER,
25     x_last_update_login                 IN     NUMBER
26   ) AS
27   /*
28   ||  Created By : [email protected]
29   ||  Created On : 02-SEP-2004
30   ||  Purpose : Initialises the Old and New references for the columns of the table.
31   ||  Known limitations, enhancements or remarks :
32   ||  Change History :
33   ||  Who             When            What
34   ||  (reverse chronological order - newest change first)
35   */
36 
37     CURSOR cur_old_ref_values IS
38       SELECT   *
39       FROM     igs_fi_tp_ret_schd
40       WHERE    rowid = x_rowid;
41 
42   BEGIN
43 
44     l_rowid := x_rowid;
45 
46     -- Code for setting the Old and New Reference Values.
47     -- Populate Old Values.
48     OPEN cur_old_ref_values;
49     FETCH cur_old_ref_values INTO old_references;
50     IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
51       CLOSE cur_old_ref_values;
52       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
53       igs_ge_msg_stack.add;
54       app_exception.raise_exception;
55       RETURN;
56     END IF;
57     CLOSE cur_old_ref_values;
58 
59     -- Populate New Values.
60     new_references.ftci_teach_retention_id           := x_ftci_teach_retention_id;
61     new_references.teach_cal_type                    := x_teach_cal_type;
62     new_references.teach_ci_sequence_number          := x_teach_ci_sequence_number;
63     new_references.fee_cal_type                      := x_fee_cal_type;
64     new_references.fee_ci_sequence_number            := x_fee_ci_sequence_number;
65     new_references.fee_type                          := x_fee_type;
66     new_references.dt_alias                          := x_dt_alias;
67     new_references.dai_sequence_number               := x_dai_sequence_number;
68     new_references.ret_percentage                    := x_ret_percentage;
69     new_references.ret_amount                        := x_ret_amount;
70 
71     IF (p_action = 'UPDATE') THEN
72       new_references.creation_date                   := old_references.creation_date;
73       new_references.created_by                      := old_references.created_by;
74     ELSE
75       new_references.creation_date                   := x_creation_date;
76       new_references.created_by                      := x_created_by;
77     END IF;
78 
79     new_references.last_update_date                  := x_last_update_date;
80     new_references.last_updated_by                   := x_last_updated_by;
81     new_references.last_update_login                 := x_last_update_login;
82 
83   END set_column_values;
84 
85 
86   PROCEDURE check_uniqueness AS
87   /*
88   ||  Created By : [email protected]
89   ||  Created On : 02-SEP-2004
90   ||  Purpose : Handles the Unique Constraint logic defined for the columns.
91   ||  Known limitations, enhancements or remarks :
92   ||  Change History :
93   ||  Who             When            What
94   ||  (reverse chronological order - newest change first)
95   */
96   BEGIN
97 
98     IF ( get_uk_for_validation (
99            new_references.teach_cal_type,
100            new_references.teach_ci_sequence_number,
101            new_references.fee_cal_type,
102            new_references.fee_ci_sequence_number,
103            new_references.fee_type,
104            new_references.dt_alias,
105            new_references.dai_sequence_number
106          )
107        ) THEN
108       fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
109       igs_ge_msg_stack.add;
110       app_exception.raise_exception;
111     END IF;
112 
113   END check_uniqueness;
114 
115   PROCEDURE check_parent_existance AS
116   /*
117   ||  Created By : [email protected]
118   ||  Created On : 13-SEP-2004
119   ||  Purpose : Checks for the existance of Parent records.
120   ||  Known limitations, enhancements or remarks :
121   ||  Change History :
122   ||  Who             When            What
123   ||  (reverse chronological order - newest change first)
124   */
125   BEGIN
126      IF ((old_references.fee_type = new_references.fee_type) AND
127          (old_references.fee_cal_type = new_references.fee_cal_type) AND
128 	 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number)
129 	)
130 	OR
131 	((new_references.fee_type IS NULL) OR
132 	 (new_references.fee_cal_type IS NULL) OR
133 	 (new_references.fee_ci_sequence_number IS NULL)) THEN
134 
135 	NULL;
136      ELSIF NOT igs_fi_f_typ_ca_inst_pkg.get_pk_for_validation (
137 		 x_fee_type => new_references.fee_type,
138 		 x_fee_cal_type => new_references.fee_cal_type,
139 		 x_fee_ci_sequence_number => new_references.fee_ci_sequence_number
140 		)  THEN
141         fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
142         igs_ge_msg_stack.add;
143         app_exception.raise_exception;
144      END IF;
145 
146      IF ((old_references.dt_alias = new_references.dt_alias) AND
147          (old_references.dai_sequence_number = new_references.dai_sequence_number) AND
148 	 (old_references.teach_cal_type = new_references.teach_cal_type) AND
149 	 (old_references.teach_ci_sequence_number = new_references.teach_ci_sequence_number)
150 	) OR
151 	((new_references.dt_alias IS NULL) OR
152 	 (new_references.dai_sequence_number IS NULL) OR
153 	 (new_references.teach_cal_type IS NULL) OR
154 	 (new_references.teach_ci_sequence_number IS NULL)) THEN
155 
156 	NULL;
157      ELSIF NOT igs_ca_da_inst_pkg.get_pk_for_validation (
158 			x_dt_alias => new_references.dt_alias,
159 			x_sequence_number => new_references.dai_sequence_number,
160 			x_cal_type => new_references.teach_cal_type,
161 			x_ci_sequence_number => new_references.teach_ci_sequence_number) THEN
162         fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
163         igs_ge_msg_stack.add;
164         app_exception.raise_exception;
165      END IF;
166 
167   END check_parent_existance;
168 
169   FUNCTION get_pk_for_validation (
170     x_ftci_teach_retention_id           IN     NUMBER
171   ) RETURN BOOLEAN AS
172   /*
173   ||  Created By : [email protected]
174   ||  Created On : 02-SEP-2004
175   ||  Purpose : Validates the Primary Key of the table.
176   ||  Known limitations, enhancements or remarks :
177   ||  Change History :
178   ||  Who             When            What
179   ||  (reverse chronological order - newest change first)
180   */
181     CURSOR cur_rowid IS
182       SELECT   rowid
183       FROM     igs_fi_tp_ret_schd
184       WHERE    ftci_teach_retention_id = x_ftci_teach_retention_id
185       FOR UPDATE NOWAIT;
186 
187     lv_rowid cur_rowid%RowType;
188 
189   BEGIN
190 
191     OPEN cur_rowid;
192     FETCH cur_rowid INTO lv_rowid;
193     IF (cur_rowid%FOUND) THEN
194       CLOSE cur_rowid;
195       RETURN(TRUE);
196     ELSE
197       CLOSE cur_rowid;
198       RETURN(FALSE);
199     END IF;
200 
201   END get_pk_for_validation;
202 
203 
204   FUNCTION get_uk_for_validation (
205     x_teach_cal_type                    IN     VARCHAR2,
206     x_teach_ci_sequence_number          IN     NUMBER,
207     x_fee_cal_type                      IN     VARCHAR2,
208     x_fee_ci_sequence_number            IN     NUMBER,
209     x_fee_type                          IN     VARCHAR2,
210     x_dt_alias                          IN     VARCHAR2,
211     x_dai_sequence_number               IN     NUMBER
212   ) RETURN BOOLEAN AS
213   /*
214   ||  Created By : [email protected]
215   ||  Created On : 02-SEP-2004
216   ||  Purpose : Validates the Unique Keys of the table.
217   ||  Known limitations, enhancements or remarks :
218   ||  Change History :
219   ||  Who             When            What
220   ||  (reverse chronological order - newest change first)
221   */
222     CURSOR cur_rowid IS
223       SELECT   rowid
224       FROM     igs_fi_tp_ret_schd
225       WHERE    teach_cal_type = x_teach_cal_type
226       AND      teach_ci_sequence_number = x_teach_ci_sequence_number
227       AND      ((fee_cal_type = x_fee_cal_type) OR (fee_cal_type IS NULL AND x_fee_cal_type IS NULL))
228       AND      ((fee_ci_sequence_number = x_fee_ci_sequence_number) OR (fee_ci_sequence_number IS NULL AND x_fee_ci_sequence_number IS NULL))
229       AND      ((fee_type = x_fee_type) OR (fee_type IS NULL AND x_fee_type IS NULL))
230       AND      dt_alias = x_dt_alias
231       AND      dai_sequence_number = x_dai_sequence_number
232       AND      ((l_rowid IS NULL) OR (rowid <> l_rowid));
233 
234     lv_rowid cur_rowid%RowType;
235 
236   BEGIN
237 
238     OPEN cur_rowid;
239     FETCH cur_rowid INTO lv_rowid;
240     IF (cur_rowid%FOUND) THEN
241       CLOSE cur_rowid;
242         RETURN (true);
243         ELSE
244        CLOSE cur_rowid;
245       RETURN(FALSE);
246     END IF;
247 
248   END get_uk_for_validation ;
249 
250   PROCEDURE BeforeRowInsertUpdateDelete1(
251     p_inserting IN BOOLEAN DEFAULT FALSE,
252     p_updating IN BOOLEAN DEFAULT FALSE,
253     p_deleting IN BOOLEAN DEFAULT FALSE
254     ) AS
255   /*
256   ||  Created By : [email protected]
257   ||  Created On : 20-SEP-2004
258   ||  Purpose :
259   ||  Known limitations, enhancements or remarks :
260   ||  Change History :
261   ||  Who             When            What
262   ||  (reverse chronological order - newest change first)
263   */
264   BEGIN
265       IF (p_inserting OR p_updating) THEN
266           IF (new_references.ret_percentage IS NULL AND new_references.ret_amount IS NULL) THEN
267               fnd_message.set_name('IGS','IGS_FI_RETAMT_OR_PER_MAND');
268 	      igs_ge_msg_stack.add;
269               App_Exception.Raise_Exception;
270 	  ELSIF (new_references.ret_percentage IS NOT NULL AND new_references.ret_amount IS NOT NULL) THEN
271               fnd_message.set_name('IGS','IGS_FI_ONE_RETAMT_OR_RETPREC');
272 	      igs_ge_msg_stack.add;
273               App_Exception.Raise_Exception;
274 	  END IF;
275       END IF;
276   END BeforeRowInsertUpdateDelete1;
277 
278 
279   PROCEDURE before_dml (
280     p_action                            IN     VARCHAR2,
281     x_rowid                             IN     VARCHAR2,
282     x_ftci_teach_retention_id           IN     NUMBER,
283     x_teach_cal_type                    IN     VARCHAR2,
284     x_teach_ci_sequence_number          IN     NUMBER,
285     x_fee_cal_type                      IN     VARCHAR2,
286     x_fee_ci_sequence_number            IN     NUMBER,
287     x_fee_type                          IN     VARCHAR2,
288     x_dt_alias                          IN     VARCHAR2,
289     x_dai_sequence_number               IN     NUMBER,
290     x_ret_percentage                    IN     NUMBER,
291     x_ret_amount                        IN     NUMBER,
292     x_creation_date                     IN     DATE,
293     x_created_by                        IN     NUMBER,
294     x_last_update_date                  IN     DATE,
295     x_last_updated_by                   IN     NUMBER,
296     x_last_update_login                 IN     NUMBER
297   ) AS
298   /*
299   ||  Created By : [email protected]
300   ||  Created On : 02-SEP-2004
301   ||  Purpose : Initialises the columns, Checks Constraints, Calls the
302   ||            Trigger Handlers for the table, before any DML operation.
303   ||  Known limitations, enhancements or remarks :
304   ||  Change History :
305   ||  Who             When            What
306   ||  (reverse chronological order - newest change first)
307   */
308   BEGIN
309 
310     set_column_values (
311       p_action,
312       x_rowid,
313       x_ftci_teach_retention_id,
314       x_teach_cal_type,
315       x_teach_ci_sequence_number,
316       x_fee_cal_type,
317       x_fee_ci_sequence_number,
318       x_fee_type,
319       x_dt_alias,
320       x_dai_sequence_number,
321       x_ret_percentage,
322       x_ret_amount,
323       x_creation_date,
324       x_created_by,
325       x_last_update_date,
326       x_last_updated_by,
327       x_last_update_login
328     );
329 
330     IF (p_action = 'INSERT') THEN
331       -- Call all the procedures related to Before Insert.
332       BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
333       IF ( get_pk_for_validation(
334              new_references.ftci_teach_retention_id
335            )
336          ) THEN
337         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
338         igs_ge_msg_stack.add;
339         app_exception.raise_exception;
340       END IF;
341       check_uniqueness;
342       Check_Constraints;
343       check_parent_existance;
344     ELSIF (p_action = 'UPDATE') THEN
345       -- Call all the procedures related to Before Update.
346       BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
347       check_uniqueness;
348       Check_Constraints;
349       check_parent_existance;
350     ELSIF (p_action = 'VALIDATE_INSERT') THEN
351       -- Call all the procedures related to Before Insert.
352       IF ( get_pk_for_validation (
353              new_references.ftci_teach_retention_id
354            )
355          ) THEN
356         fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
357         igs_ge_msg_stack.add;
358         app_exception.raise_exception;
359       END IF;
360       check_uniqueness;
361     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
362       check_uniqueness;
363     END IF;
364     l_rowid := NULL;
365   END before_dml;
366 
367 
368   PROCEDURE insert_row (
369     x_rowid                             IN OUT NOCOPY VARCHAR2,
370     x_ftci_teach_retention_id           IN OUT NOCOPY NUMBER,
371     x_teach_cal_type                    IN     VARCHAR2,
372     x_teach_ci_sequence_number          IN     NUMBER,
373     x_fee_cal_type                      IN     VARCHAR2,
374     x_fee_ci_sequence_number            IN     NUMBER,
375     x_fee_type                          IN     VARCHAR2,
376     x_dt_alias                          IN     VARCHAR2,
377     x_dai_sequence_number               IN     NUMBER,
378     x_ret_percentage                    IN     NUMBER,
379     x_ret_amount                        IN     NUMBER,
380     x_mode                              IN     VARCHAR2
381   ) AS
382   /*
383   ||  Created By : [email protected]
384   ||  Created On : 02-SEP-2004
385   ||  Purpose : Handles the INSERT DML logic for the table.
386   ||  Known limitations, enhancements or remarks :
387   ||  Change History :
388   ||  Who             When            What
389   ||  (reverse chronological order - newest change first)
390   */
391 
392     x_last_update_date           DATE;
393     x_last_updated_by            NUMBER;
394     x_last_update_login          NUMBER;
395     x_request_id                 NUMBER;
396     x_program_id                 NUMBER;
397     x_program_application_id     NUMBER;
398     x_program_update_date        DATE;
399 
400   BEGIN
401 
402     x_last_update_date := SYSDATE;
403     IF (x_mode = 'I') THEN
404       x_last_updated_by := 1;
405       x_last_update_login := 0;
406     ELSIF (x_mode = 'R') THEN
407       x_last_updated_by := fnd_global.user_id;
408       IF (x_last_updated_by IS NULL) THEN
409         x_last_updated_by := -1;
410       END IF;
411       x_last_update_login := fnd_global.login_id;
412       IF (x_last_update_login IS NULL) THEN
413         x_last_update_login := -1;
414       END IF;
415       x_request_id             := fnd_global.conc_request_id;
416       x_program_id             := fnd_global.conc_program_id;
417       x_program_application_id := fnd_global.prog_appl_id;
418 
419       IF (x_request_id = -1) THEN
420         x_request_id             := NULL;
421         x_program_id             := NULL;
422         x_program_application_id := NULL;
423         x_program_update_date    := NULL;
424       ELSE
425         x_program_update_date    := SYSDATE;
426       END IF;
427     ELSE
428       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
429       fnd_message.set_token ('ROUTINE', 'IGS_FI_TP_RET_SCHD_PKG.INSERT_ROW');
430       igs_ge_msg_stack.add;
431       app_exception.raise_exception;
432     END IF;
433 
434     x_ftci_teach_retention_id := NULL;
435 
436     before_dml(
437       p_action                            => 'INSERT',
438       x_rowid                             => x_rowid,
439       x_ftci_teach_retention_id           => x_ftci_teach_retention_id,
440       x_teach_cal_type                    => x_teach_cal_type,
441       x_teach_ci_sequence_number          => x_teach_ci_sequence_number,
442       x_fee_cal_type                      => x_fee_cal_type,
443       x_fee_ci_sequence_number            => x_fee_ci_sequence_number,
444       x_fee_type                          => x_fee_type,
445       x_dt_alias                          => x_dt_alias,
446       x_dai_sequence_number               => x_dai_sequence_number,
447       x_ret_percentage                    => x_ret_percentage,
448       x_ret_amount                        => x_ret_amount,
449       x_creation_date                     => x_last_update_date,
450       x_created_by                        => x_last_updated_by,
451       x_last_update_date                  => x_last_update_date,
452       x_last_updated_by                   => x_last_updated_by,
453       x_last_update_login                 => x_last_update_login
454     );
455 
456     INSERT INTO igs_fi_tp_ret_schd (
457       ftci_teach_retention_id,
458       teach_cal_type,
459       teach_ci_sequence_number,
460       fee_cal_type,
461       fee_ci_sequence_number,
462       fee_type,
463       dt_alias,
464       dai_sequence_number,
465       ret_percentage,
466       ret_amount,
467       creation_date,
468       created_by,
469       last_update_date,
470       last_updated_by,
471       last_update_login,
472       request_id,
473       program_id,
474       program_application_id,
475       program_update_date
476     ) VALUES (
477       igs_fi_tp_ret_schd_s.NEXTVAL,
478       new_references.teach_cal_type,
479       new_references.teach_ci_sequence_number,
480       new_references.fee_cal_type,
481       new_references.fee_ci_sequence_number,
482       new_references.fee_type,
483       new_references.dt_alias,
484       new_references.dai_sequence_number,
485       new_references.ret_percentage,
486       new_references.ret_amount,
487       x_last_update_date,
488       x_last_updated_by,
489       x_last_update_date,
490       x_last_updated_by,
491       x_last_update_login,
492       x_request_id,
493       x_program_id,
494       x_program_application_id,
495       x_program_update_date
496     ) RETURNING ROWID, ftci_teach_retention_id INTO x_rowid, x_ftci_teach_retention_id;
497 
498   END insert_row;
499 
500 
501   PROCEDURE lock_row (
502     x_rowid                             IN     VARCHAR2,
503     x_ftci_teach_retention_id           IN     NUMBER,
504     x_teach_cal_type                    IN     VARCHAR2,
505     x_teach_ci_sequence_number          IN     NUMBER,
506     x_fee_cal_type                      IN     VARCHAR2,
507     x_fee_ci_sequence_number            IN     NUMBER,
508     x_fee_type                          IN     VARCHAR2,
509     x_dt_alias                          IN     VARCHAR2,
510     x_dai_sequence_number               IN     NUMBER,
511     x_ret_percentage                    IN     NUMBER,
512     x_ret_amount                        IN     NUMBER
513   ) AS
514   /*
515   ||  Created By : [email protected]
516   ||  Created On : 02-SEP-2004
517   ||  Purpose : Handles the LOCK mechanism for 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
525         teach_cal_type,
526         teach_ci_sequence_number,
527         fee_cal_type,
528         fee_ci_sequence_number,
529         fee_type,
530         dt_alias,
531         dai_sequence_number,
532         ret_percentage,
533         ret_amount
534       FROM  igs_fi_tp_ret_schd
535       WHERE rowid = x_rowid
536       FOR UPDATE NOWAIT;
537 
538     tlinfo c1%ROWTYPE;
539 
540   BEGIN
541 
542     OPEN c1;
543     FETCH c1 INTO tlinfo;
544     IF (c1%notfound) THEN
545       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
546       igs_ge_msg_stack.add;
547       CLOSE c1;
548       app_exception.raise_exception;
549       RETURN;
550     END IF;
551     CLOSE c1;
552 
553     IF (
554         (tlinfo.teach_cal_type = x_teach_cal_type)
555         AND (tlinfo.teach_ci_sequence_number = x_teach_ci_sequence_number)
556         AND ((tlinfo.fee_cal_type = x_fee_cal_type) OR ((tlinfo.fee_cal_type IS NULL) AND (X_fee_cal_type IS NULL)))
557         AND ((tlinfo.fee_ci_sequence_number = x_fee_ci_sequence_number) OR ((tlinfo.fee_ci_sequence_number IS NULL) AND (X_fee_ci_sequence_number IS NULL)))
558         AND ((tlinfo.fee_type = x_fee_type) OR ((tlinfo.fee_type IS NULL) AND (X_fee_type IS NULL)))
559         AND (tlinfo.dt_alias = x_dt_alias)
560         AND (tlinfo.dai_sequence_number = x_dai_sequence_number)
561         AND ((tlinfo.ret_percentage = x_ret_percentage) OR ((tlinfo.ret_percentage IS NULL) AND (X_ret_percentage IS NULL)))
562         AND ((tlinfo.ret_amount = x_ret_amount) OR ((tlinfo.ret_amount IS NULL) AND (X_ret_amount IS NULL)))
563        ) THEN
564       NULL;
565     ELSE
566       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
567       igs_ge_msg_stack.add;
568       app_exception.raise_exception;
569     END IF;
570 
571     RETURN;
572 
573   END lock_row;
574 
575 
576   PROCEDURE update_row (
577     x_rowid                             IN     VARCHAR2,
578     x_ftci_teach_retention_id           IN     NUMBER,
579     x_teach_cal_type                    IN     VARCHAR2,
580     x_teach_ci_sequence_number          IN     NUMBER,
581     x_fee_cal_type                      IN     VARCHAR2,
582     x_fee_ci_sequence_number            IN     NUMBER,
583     x_fee_type                          IN     VARCHAR2,
584     x_dt_alias                          IN     VARCHAR2,
585     x_dai_sequence_number               IN     NUMBER,
586     x_ret_percentage                    IN     NUMBER,
587     x_ret_amount                        IN     NUMBER,
588     x_mode                              IN     VARCHAR2
589   ) AS
590   /*
591   ||  Created By : [email protected]
592   ||  Created On : 02-SEP-2004
593   ||  Purpose : Handles the UPDATE DML logic for the table.
594   ||  Known limitations, enhancements or remarks :
595   ||  Change History :
596   ||  Who             When            What
597   ||  (reverse chronological order - newest change first)
598   */
599     x_last_update_date           DATE ;
600     x_last_updated_by            NUMBER;
601     x_last_update_login          NUMBER;
602     x_request_id                 NUMBER;
603     x_program_id                 NUMBER;
604     x_program_application_id     NUMBER;
605     x_program_update_date        DATE;
606   BEGIN
607 
608     x_last_update_date := SYSDATE;
609     IF (X_MODE = 'I') THEN
610       x_last_updated_by := 1;
611       x_last_update_login := 0;
612     ELSIF (x_mode = 'R') THEN
613       x_last_updated_by := fnd_global.user_id;
614       IF x_last_updated_by IS NULL THEN
615         x_last_updated_by := -1;
616       END IF;
617       x_last_update_login := fnd_global.login_id;
618       IF (x_last_update_login IS NULL) THEN
619         x_last_update_login := -1;
620       END IF;
621     ELSE
622       fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
623       fnd_message.set_token ('ROUTINE', 'IGS_FI_TP_RET_SCHD_PKG.UPDATE_ROW');
624       igs_ge_msg_stack.add;
625       app_exception.raise_exception;
626     END IF;
627 
628     before_dml(
629       p_action                            => 'UPDATE',
630       x_rowid                             => x_rowid,
631       x_ftci_teach_retention_id           => x_ftci_teach_retention_id,
632       x_teach_cal_type                    => x_teach_cal_type,
633       x_teach_ci_sequence_number          => x_teach_ci_sequence_number,
634       x_fee_cal_type                      => x_fee_cal_type,
635       x_fee_ci_sequence_number            => x_fee_ci_sequence_number,
636       x_fee_type                          => x_fee_type,
637       x_dt_alias                          => x_dt_alias,
638       x_dai_sequence_number               => x_dai_sequence_number,
639       x_ret_percentage                    => x_ret_percentage,
640       x_ret_amount                        => x_ret_amount,
641       x_creation_date                     => x_last_update_date,
642       x_created_by                        => x_last_updated_by,
643       x_last_update_date                  => x_last_update_date,
644       x_last_updated_by                   => x_last_updated_by,
645       x_last_update_login                 => x_last_update_login
646     );
647 
648     IF (x_mode = 'R') THEN
649       x_request_id := fnd_global.conc_request_id;
650       x_program_id := fnd_global.conc_program_id;
651       x_program_application_id := fnd_global.prog_appl_id;
652       IF (x_request_id =  -1) THEN
653         x_request_id := old_references.request_id;
654         x_program_id := old_references.program_id;
655         x_program_application_id := old_references.program_application_id;
656         x_program_update_date := old_references.program_update_date;
657       ELSE
658         x_program_update_date := SYSDATE;
659       END IF;
660     END IF;
661 
662     UPDATE igs_fi_tp_ret_schd
663       SET
664         teach_cal_type                    = new_references.teach_cal_type,
665         teach_ci_sequence_number          = new_references.teach_ci_sequence_number,
666         fee_cal_type                      = new_references.fee_cal_type,
667         fee_ci_sequence_number            = new_references.fee_ci_sequence_number,
668         fee_type                          = new_references.fee_type,
669         dt_alias                          = new_references.dt_alias,
670         dai_sequence_number               = new_references.dai_sequence_number,
671         ret_percentage                    = new_references.ret_percentage,
672         ret_amount                        = new_references.ret_amount,
673         last_update_date                  = x_last_update_date,
674         last_updated_by                   = x_last_updated_by,
675         last_update_login                 = x_last_update_login,
676         request_id                        = x_request_id,
677         program_id                        = x_program_id,
678         program_application_id            = x_program_application_id,
679         program_update_date               = x_program_update_date
680       WHERE rowid = x_rowid;
681 
682     IF (SQL%NOTFOUND) THEN
683       RAISE NO_DATA_FOUND;
684     END IF;
685 
686   END update_row;
687 
688 
689   PROCEDURE add_row (
690     x_rowid                             IN OUT NOCOPY VARCHAR2,
691     x_ftci_teach_retention_id           IN OUT NOCOPY NUMBER,
692     x_teach_cal_type                    IN     VARCHAR2,
693     x_teach_ci_sequence_number          IN     NUMBER,
694     x_fee_cal_type                      IN     VARCHAR2,
695     x_fee_ci_sequence_number            IN     NUMBER,
696     x_fee_type                          IN     VARCHAR2,
697     x_dt_alias                          IN     VARCHAR2,
698     x_dai_sequence_number               IN     NUMBER,
699     x_ret_percentage                    IN     NUMBER,
700     x_ret_amount                        IN     NUMBER,
701     x_mode                              IN     VARCHAR2
702   ) AS
703   /*
704   ||  Created By : [email protected]
705   ||  Created On : 02-SEP-2004
706   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
707   ||  Known limitations, enhancements or remarks :
708   ||  Change History :
709   ||  Who             When            What
710   ||  (reverse chronological order - newest change first)
711   */
712     CURSOR c1 IS
713       SELECT   rowid
714       FROM     igs_fi_tp_ret_schd
715       WHERE    ftci_teach_retention_id           = x_ftci_teach_retention_id;
716 
717   BEGIN
718 
719     OPEN c1;
720     FETCH c1 INTO x_rowid;
721     IF (c1%NOTFOUND) THEN
722       CLOSE c1;
723 
724       insert_row (
725         x_rowid,
726         x_ftci_teach_retention_id,
727         x_teach_cal_type,
728         x_teach_ci_sequence_number,
729         x_fee_cal_type,
730         x_fee_ci_sequence_number,
731         x_fee_type,
732         x_dt_alias,
733         x_dai_sequence_number,
734         x_ret_percentage,
735         x_ret_amount,
736         x_mode
737       );
738       RETURN;
739     END IF;
740     CLOSE c1;
741 
742     update_row (
743       x_rowid,
744       x_ftci_teach_retention_id,
745       x_teach_cal_type,
746       x_teach_ci_sequence_number,
747       x_fee_cal_type,
748       x_fee_ci_sequence_number,
749       x_fee_type,
750       x_dt_alias,
751       x_dai_sequence_number,
752       x_ret_percentage,
753       x_ret_amount,
754       x_mode
755     );
756 
757   END add_row;
758 
759   PROCEDURE get_fk_igs_ca_da_inst (
760     x_dt_alias                          IN     VARCHAR2,
761     x_dai_sequence_number               IN     NUMBER,
762     x_teach_cal_type                    IN     VARCHAR2,
763     x_teach_ci_sequence_number          IN     NUMBER
764   ) AS
765     /*
766   ||  Created By : rmaddipa
767   ||  Created On : 02-SEP-04
768   ||  Purpose : Validates the Foreign Keys for the table.
769   ||  Known limitations, enhancements or remarks :
770   ||  Change History :
771   ||  Who             When            What
772   ||  (reverse chronological order - newest change first)
773   */
774     CURSOR cur_rowid IS
775       SELECT   rowid
776       FROM     igs_fi_tp_ret_schd
777       WHERE   ((dt_alias = x_dt_alias ) AND
778                (dai_sequence_number = x_dai_sequence_number) AND
779 	       (teach_cal_type = x_teach_cal_type) AND
780 	       (teach_ci_sequence_number = x_teach_ci_sequence_number));
781 
782     lv_rowid cur_rowid%RowType;
783 
784   BEGIN
785     OPEN cur_rowid;
786     FETCH cur_rowid INTO lv_rowid;
787     IF (cur_rowid%FOUND) THEN
788       CLOSE cur_rowid;
789       fnd_message.set_name ('IGS', 'IGS_FI_TPRS_DAI_FK');
790       igs_ge_msg_stack.add;
791       app_exception.raise_exception;
792       RETURN;
793     END IF;
794     CLOSE cur_rowid;
795   END get_fk_igs_ca_da_inst;
796 
797 
798   PROCEDURE delete_row (
799     x_rowid IN VARCHAR2
800   ) AS
801   /*
802   ||  Created By : [email protected]
803   ||  Created On : 08-SEP-2004
804   ||  Purpose : Handles the DELETE DML logic for the table.
805   ||  Known limitations, enhancements or remarks :
806   ||  Change History :
807   ||  Who             When            What
808   ||  (reverse chronological order - newest change first)
809   */
810   BEGIN
811 
812     before_dml (
813       p_action => 'DELETE',
814       x_rowid => x_rowid
815     );
816 
817     DELETE FROM igs_fi_tp_ret_schd
818     WHERE rowid = x_rowid;
819 
820     IF (SQL%NOTFOUND) THEN
821       RAISE NO_DATA_FOUND;
822     END IF;
823 
824   END delete_row;
825 
826 
827   PROCEDURE Check_Constraints (
828    	 Column_Name	IN	VARCHAR2	DEFAULT NULL,
829 	 Column_Value 	IN	VARCHAR2	DEFAULT NULL
830   ) AS
831   /*
832   ||  Created By : [email protected]
833   ||  Created On : 20-SEP-2004
834   ||  Purpose :
835   ||  Known limitations, enhancements or remarks :
836   ||  Change History :
837   ||  Who             When            What
838   ||  (reverse chronological order - newest change first)
839   */
840 
841   BEGIN
842     IF Column_Name is NULL THEN
843        	NULL;
844     ELSIF Upper(Column_Name) = 'RET_PERCENTAGE' THEN
845         new_references.ret_percentage := igs_ge_number.to_num (column_value);
846     ELSIF Upper(Column_Name) = 'RET_AMOUNT' THEN
847         new_references.ret_amount := igs_ge_number.to_num (column_value);
848     END IF;
849 
850     IF (Upper(Column_Name) = 'RET_PERCENTAGE' OR Column_Name IS NULL) THEN
851         IF (new_references.ret_percentage <= 0 OR new_references.ret_percentage > 100) THEN
852 	     fnd_message.set_name('IGS','IGS_FI_RET_PERCENT_GT_ZERO');
853 	     igs_ge_msg_stack.add;
854              App_Exception.Raise_Exception;
855         END IF;
856     END IF;
857 
858     IF (Upper(Column_Name) = 'RET_AMOUNT' OR Column_Name IS NULL) THEN
859         IF (new_references.ret_amount <= 0) THEN
860              fnd_message.set_name('IGS','IGS_FI_RET_AMT_GT_ZERO');
861 	     igs_ge_msg_stack.add;
862              App_Exception.Raise_Exception;
863         END IF;
864     END IF;
865 
866   END Check_Constraints;
867 END igs_fi_tp_ret_schd_pkg;