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