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