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_BILL_P_PLANS_PKG

Source


1 PACKAGE BODY igs_fi_bill_p_plans_pkg AS
2 /* $Header: IGSSIE3B.pls 115.0 2003/08/26 07:20:01 smvk noship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references igs_fi_bill_p_plans%ROWTYPE;
6   new_references igs_fi_bill_p_plans%ROWTYPE;
7 
8   PROCEDURE set_column_values (
9     p_action                            IN     VARCHAR2,
10     x_rowid                             IN     VARCHAR2,
11     x_student_plan_id                   IN     NUMBER,
12     x_bill_id                           IN     NUMBER,
13     x_plan_start_date                   IN     DATE,
14     x_plan_end_date                     IN     DATE,
15     x_creation_date                     IN     DATE,
16     x_created_by                        IN     NUMBER,
17     x_last_update_date                  IN     DATE,
18     x_last_updated_by                   IN     NUMBER,
19     x_last_update_login                 IN     NUMBER
20   ) AS
21   /*
22   ||  Created By : smvk
23   ||  Created On : 24-AUG-2003
24   ||  Purpose : Initialises the Old and New references for the columns of the table.
25   ||  Known limitations, enhancements or remarks :
26   ||  Change History :
27   ||  Who             When            What
28   ||  (reverse chronological order - newest change first)
29   */
30 
31     CURSOR cur_old_ref_values IS
32       SELECT   *
33       FROM     igs_fi_bill_p_plans
34       WHERE    rowid = x_rowid;
35 
36   BEGIN
37 
38     l_rowid := x_rowid;
39 
40     -- Code for setting the Old and New Reference Values.
41     -- Populate Old Values.
42     OPEN cur_old_ref_values;
43     FETCH cur_old_ref_values INTO old_references;
44     IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
45       CLOSE cur_old_ref_values;
46       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
47       igs_ge_msg_stack.add;
48       app_exception.raise_exception;
49       RETURN;
50     END IF;
51     CLOSE cur_old_ref_values;
52 
53     -- Populate New Values.
54     new_references.student_plan_id                   := x_student_plan_id;
55     new_references.bill_id                           := x_bill_id;
56     new_references.plan_start_date                   := x_plan_start_date;
57     new_references.plan_end_date                     := x_plan_end_date;
58 
59     IF (p_action = 'UPDATE') THEN
60       new_references.creation_date                   := old_references.creation_date;
61       new_references.created_by                      := old_references.created_by;
62     ELSE
63       new_references.creation_date                   := x_creation_date;
64       new_references.created_by                      := x_created_by;
65     END IF;
66 
67     new_references.last_update_date                  := x_last_update_date;
68     new_references.last_updated_by                   := x_last_updated_by;
69     new_references.last_update_login                 := x_last_update_login;
70 
71   END set_column_values;
72 
73 
74   PROCEDURE check_parent_existance AS
75   /*
76   ||  Created By : smvk
77   ||  Created On : 24-AUG-2003
78   ||  Purpose : Checks for the existance of Parent records.
79   ||  Known limitations, enhancements or remarks :
80   ||  Change History :
81   ||  Who             When            What
82   ||  (reverse chronological order - newest change first)
83   */
84   BEGIN
85 
86     IF (((old_references.student_plan_id = new_references.student_plan_id)) OR
87         ((new_references.student_plan_id IS NULL))) THEN
88       NULL;
89     ELSIF NOT igs_fi_pp_std_attrs_pkg.get_pk_for_validation (
90                 new_references.student_plan_id
91               ) THEN
92       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
93       igs_ge_msg_stack.add;
94       app_exception.raise_exception;
95     END IF;
96 
97     IF (((old_references.bill_id = new_references.bill_id)) OR
98         ((new_references.bill_id IS NULL))) THEN
99       NULL;
100     ELSIF NOT igs_fi_bill_pkg.get_pk_for_validation (
101                 new_references.bill_id
102               ) THEN
103       fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
104       igs_ge_msg_stack.add;
105       app_exception.raise_exception;
106     END IF;
107 
108   END check_parent_existance;
109 
110   FUNCTION get_pk_for_validation (
111     x_student_plan_id                   IN     NUMBER,
112     x_bill_id                           IN     NUMBER
113   ) RETURN BOOLEAN AS
114   /*
115   ||  Created By : smvk
116   ||  Created On : 24-AUG-2003
117   ||  Purpose : Validates the Primary Key of the table.
118   ||  Known limitations, enhancements or remarks :
119   ||  Change History :
120   ||  Who             When            What
121   ||  (reverse chronological order - newest change first)
122   */
123     CURSOR cur_rowid IS
124       SELECT   rowid
125       FROM     igs_fi_bill_p_plans
126       WHERE    student_plan_id = x_student_plan_id
127       AND      bill_id = x_bill_id;
128 
129     lv_rowid cur_rowid%RowType;
130 
131   BEGIN
132 
133     OPEN cur_rowid;
134     FETCH cur_rowid INTO lv_rowid;
135     IF (cur_rowid%FOUND) THEN
136       CLOSE cur_rowid;
137       RETURN(TRUE);
138     ELSE
139       CLOSE cur_rowid;
140       RETURN(FALSE);
141     END IF;
142 
143   END get_pk_for_validation;
144 
145 
146   PROCEDURE get_fk_igs_fi_pp_std_attrs (
147     x_student_plan_id                   IN     NUMBER
148   ) AS
149   /*
150   ||  Created By : smvk
151   ||  Created On : 24-AUG-2003
152   ||  Purpose : Validates the Foreign Keys for the table.
153   ||  Known limitations, enhancements or remarks :
154   ||  Change History :
155   ||  Who             When            What
156   ||  (reverse chronological order - newest change first)
157   */
158     CURSOR cur_rowid IS
159       SELECT   rowid
160       FROM     igs_fi_bill_p_plans
161       WHERE   ((student_plan_id = x_student_plan_id));
162 
163     lv_rowid cur_rowid%RowType;
164 
165   BEGIN
166 
167     OPEN cur_rowid;
168     FETCH cur_rowid INTO lv_rowid;
169     IF (cur_rowid%FOUND) THEN
170       CLOSE cur_rowid;
171       fnd_message.set_name ('IGS', 'IGS_FI_BPP_PPSA_FK');
172       igs_ge_msg_stack.add;
173       app_exception.raise_exception;
174       RETURN;
175     END IF;
176     CLOSE cur_rowid;
177 
178   END get_fk_igs_fi_pp_std_attrs;
179 
180   PROCEDURE before_dml (
181     p_action                            IN     VARCHAR2,
182     x_rowid                             IN     VARCHAR2,
183     x_student_plan_id                   IN     NUMBER,
184     x_bill_id                           IN     NUMBER,
185     x_plan_start_date                   IN     DATE,
186     x_plan_end_date                     IN     DATE,
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 : smvk
195   ||  Created On : 24-AUG-2003
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_student_plan_id,
209       x_bill_id,
210       x_plan_start_date,
211       x_plan_end_date,
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.student_plan_id,
223              new_references.bill_id
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.student_plan_id,
238              new_references.bill_id
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_student_plan_id                   IN     NUMBER,
253     x_bill_id                           IN     NUMBER,
254     x_plan_start_date                   IN     DATE,
255     x_plan_end_date                     IN     DATE,
256     x_mode                              IN     VARCHAR2
257   ) AS
258   /*
259   ||  Created By : smvk
260   ||  Created On : 24-AUG-2003
261   ||  Purpose : Handles the INSERT DML logic for the table.
262   ||  Known limitations, enhancements or remarks :
263   ||  Change History :
264   ||  Who             When            What
265   ||  (reverse chronological order - newest change first)
266   */
267 
268     x_last_update_date           DATE;
269     x_last_updated_by            NUMBER;
270     x_last_update_login          NUMBER;
271     x_request_id                 NUMBER;
272     x_program_id                 NUMBER;
273     x_program_application_id     NUMBER;
274     x_program_update_date        DATE;
275 
276   BEGIN
277 
278     x_last_update_date := SYSDATE;
279     IF (x_mode = 'I') THEN
280       x_last_updated_by := 1;
281       x_last_update_login := 0;
282     ELSIF (x_mode = 'R') THEN
283       x_last_updated_by := fnd_global.user_id;
284       IF (x_last_updated_by IS NULL) THEN
285         x_last_updated_by := -1;
286       END IF;
287       x_last_update_login := fnd_global.login_id;
288       IF (x_last_update_login IS NULL) THEN
289         x_last_update_login := -1;
290       END IF;
291       x_request_id             := fnd_global.conc_request_id;
292       x_program_id             := fnd_global.conc_program_id;
293       x_program_application_id := fnd_global.prog_appl_id;
294 
295       IF (x_request_id = -1) THEN
296         x_request_id             := NULL;
297         x_program_id             := NULL;
298         x_program_application_id := NULL;
299         x_program_update_date    := NULL;
300       ELSE
301         x_program_update_date    := SYSDATE;
302       END IF;
303     ELSE
304       fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
305       fnd_message.set_token ('ROUTINE', 'IGS_FI_BILL_P_PLANS_PKG.INSERT_ROW');
306       igs_ge_msg_stack.add;
307       app_exception.raise_exception;
308     END IF;
309 
310     before_dml(
311       p_action                            => 'INSERT',
312       x_rowid                             => x_rowid,
313       x_student_plan_id                   => x_student_plan_id,
314       x_bill_id                           => x_bill_id,
315       x_plan_start_date                   => x_plan_start_date,
316       x_plan_end_date                     => x_plan_end_date,
317       x_creation_date                     => x_last_update_date,
318       x_created_by                        => x_last_updated_by,
319       x_last_update_date                  => x_last_update_date,
320       x_last_updated_by                   => x_last_updated_by,
321       x_last_update_login                 => x_last_update_login
322     );
323 
324     INSERT INTO igs_fi_bill_p_plans (
325       student_plan_id,
326       bill_id,
327       plan_start_date,
328       plan_end_date,
329       creation_date,
330       created_by,
331       last_update_date,
332       last_updated_by,
333       last_update_login,
334       request_id,
335       program_id,
336       program_application_id,
337       program_update_date
338     ) VALUES (
339       new_references.student_plan_id,
340       new_references.bill_id,
341       new_references.plan_start_date,
342       new_references.plan_end_date,
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       x_request_id,
349       x_program_id,
350       x_program_application_id,
351       x_program_update_date
352     ) RETURNING ROWID INTO x_rowid;
353 
354   END insert_row;
355 
356 
357   PROCEDURE lock_row (
358     x_rowid                             IN     VARCHAR2,
359     x_student_plan_id                   IN     NUMBER,
360     x_bill_id                           IN     NUMBER,
361     x_plan_start_date                   IN     DATE,
362     x_plan_end_date                     IN     DATE
363   ) AS
364   /*
365   ||  Created By : smvk
366   ||  Created On : 24-AUG-2003
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_start_date,
376         plan_end_date
377       FROM  igs_fi_bill_p_plans
378       WHERE rowid = x_rowid
379       FOR UPDATE NOWAIT;
380 
381     tlinfo c1%ROWTYPE;
382 
383   BEGIN
384 
385     OPEN c1;
386     FETCH c1 INTO tlinfo;
387     IF (c1%notfound) THEN
388       fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
389       igs_ge_msg_stack.add;
390       CLOSE c1;
391       app_exception.raise_exception;
392       RETURN;
393     END IF;
394     CLOSE c1;
395 
396     IF (
397         (tlinfo.plan_start_date = x_plan_start_date)
398         AND (tlinfo.plan_end_date = x_plan_end_date)
399        ) THEN
400       NULL;
401     ELSE
402       fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
403       igs_ge_msg_stack.add;
404       app_exception.raise_exception;
405     END IF;
406 
407     RETURN;
408 
409   END lock_row;
410 
411 
412   PROCEDURE update_row (
413     x_rowid                             IN     VARCHAR2,
414     x_student_plan_id                   IN     NUMBER,
415     x_bill_id                           IN     NUMBER,
416     x_plan_start_date                   IN     DATE,
417     x_plan_end_date                     IN     DATE,
418     x_mode                              IN     VARCHAR2
419   ) AS
420   /*
421   ||  Created By : smvk
422   ||  Created On : 24-AUG-2003
423   ||  Purpose : Handles the UPDATE DML logic for the table.
424   ||  Known limitations, enhancements or remarks :
425   ||  Change History :
426   ||  Who             When            What
427   ||  (reverse chronological order - newest change first)
428   */
429     x_last_update_date           DATE ;
430     x_last_updated_by            NUMBER;
431     x_last_update_login          NUMBER;
432     x_request_id                 NUMBER;
433     x_program_id                 NUMBER;
434     x_program_application_id     NUMBER;
435     x_program_update_date        DATE;
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       fnd_message.set_token ('ROUTINE', 'IGS_FI_BILL_P_PLANS_PKG.UPDATE_ROW');
455       igs_ge_msg_stack.add;
456       app_exception.raise_exception;
457     END IF;
458 
459     before_dml(
460       p_action                            => 'UPDATE',
461       x_rowid                             => x_rowid,
462       x_student_plan_id                   => x_student_plan_id,
463       x_bill_id                           => x_bill_id,
464       x_plan_start_date                   => x_plan_start_date,
465       x_plan_end_date                     => x_plan_end_date,
466       x_creation_date                     => x_last_update_date,
467       x_created_by                        => x_last_updated_by,
468       x_last_update_date                  => x_last_update_date,
469       x_last_updated_by                   => x_last_updated_by,
470       x_last_update_login                 => x_last_update_login
471     );
472 
473     IF (x_mode = 'R') THEN
474       x_request_id := fnd_global.conc_request_id;
475       x_program_id := fnd_global.conc_program_id;
476       x_program_application_id := fnd_global.prog_appl_id;
477       IF (x_request_id =  -1) THEN
478         x_request_id := old_references.request_id;
479         x_program_id := old_references.program_id;
480         x_program_application_id := old_references.program_application_id;
481         x_program_update_date := old_references.program_update_date;
482       ELSE
483         x_program_update_date := SYSDATE;
484       END IF;
485     END IF;
486 
487     UPDATE igs_fi_bill_p_plans
488       SET
489         plan_start_date                   = new_references.plan_start_date,
490         plan_end_date                     = new_references.plan_end_date,
491         last_update_date                  = x_last_update_date,
492         last_updated_by                   = x_last_updated_by,
493         last_update_login                 = x_last_update_login ,
494         request_id                        = x_request_id,
495         program_id                        = x_program_id,
496         program_application_id            = x_program_application_id,
497         program_update_date               = x_program_update_date
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_student_plan_id                   IN     NUMBER,
510     x_bill_id                           IN     NUMBER,
511     x_plan_start_date                   IN     DATE,
512     x_plan_end_date                     IN     DATE,
513     x_mode                              IN     VARCHAR2
514   ) AS
515   /*
516   ||  Created By : smvk
517   ||  Created On : 24-AUG-2003
518   ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
519   ||  Known limitations, enhancements or remarks :
520   ||  Change History :
521   ||  Who             When            What
522   ||  (reverse chronological order - newest change first)
523   */
524     CURSOR c1 IS
525       SELECT   rowid
526       FROM     igs_fi_bill_p_plans
527       WHERE    student_plan_id                   = x_student_plan_id
528       AND      bill_id                           = x_bill_id;
529 
530   BEGIN
531 
532     OPEN c1;
533     FETCH c1 INTO x_rowid;
534     IF (c1%NOTFOUND) THEN
535       CLOSE c1;
536 
537       insert_row (
538         x_rowid,
539         x_student_plan_id,
540         x_bill_id,
541         x_plan_start_date,
542         x_plan_end_date,
543         x_mode
544       );
545       RETURN;
546     END IF;
547     CLOSE c1;
548 
549     update_row (
550       x_rowid,
551       x_student_plan_id,
552       x_bill_id,
553       x_plan_start_date,
554       x_plan_end_date,
555       x_mode
556     );
557 
558   END add_row;
559 
560 END igs_fi_bill_p_plans_pkg;