[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_REFUNDS_PKG
Source
1 PACKAGE BODY igs_fi_refunds_pkg AS
2 /* $Header: IGSSIB4B.pls 120.3 2006/06/27 14:18:40 skharida ship $ */
3
4 /*
5 || Created By : Vinay
6 || Created On : 26-FEB-2002
7 || Purpose :
8 || Known limitations, enhancements or remarks :
9 || Change History :
10 || Who When What
11 || agairola 29-Aug-2005 Enh 3392095: Tuition Waiver Build, impact of waiver_name in charges table
12 || svuppala 04-AUG-2005 Enh 3392095 - Tution Waivers build
13 || Impact of Charges API version Number change
14 || Modified igs_fi_charges_api_pvt.create_charge - version 2.0 and x_waiver_amount
15 || pmarada 26-May-2005 Enh#3020586- added tax year code column as per 1098-t reporting build
16 || agairola 06-JUN-2002 for bug 2392776 - added update_invoice and modified beforerowinsert
17 || agairola 14-May-2002 Changed the procedure beforerowinsert for bug 2365356
18 || agairola 14-May-2002 Changed the call to the Charges API for bugs 2372163,2372122
19 || (reverse chronological order - newest change first)
20 */
21 -- Bug: 2144600
22 -- What: Created TBH
23 -- Who: vchappid
24 -- When: 25-Feb-2002
25
26 l_rowid VARCHAR2(25);
27 old_references igs_fi_refunds%ROWTYPE;
28 new_references igs_fi_refunds%ROWTYPE;
29
30 -- Initial Declaration of the procedure
31 PROCEDURE beforerowinsert;
32
33 PROCEDURE update_invoice(p_invoice_id igs_fi_inv_int.invoice_id%TYPE) AS
34 /*
35 || Created By : Amit.Gairola
36 || Created On : 06-JUN-2002
37 || Purpose : Updates the Charge record in case of reversal. Updates the Reversal Flag and the reversal reason
38 || for bug 2392776
39 || Known limitations, enhancements or remarks :
40 || Change History :
41 || Who When What
42 || (reverse chronological order - newest change first)
43 || skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns from the table IGS_FI_INV_INT_ALL and IGS_FI_REFUNDS
44 || smadathi 01-Nov-2002 Enh. Bug 2584986. Modified igs_fi_inv_int_pkg.update_row
45 || to add new column REVERSAL_GL_DATE
46 || vvutukur 18-Sep-2002 Enh#2564643.Removed references to subaccount_id.ie., from the call to
47 || igs_fi_inv_int_pkg.update_row.
48 */
49
50 CURSOR cur_inv(cp_invoice_id igs_fi_inv_int.invoice_id%TYPE) IS
51 SELECT inv.*
52 FROM igs_fi_inv_int inv
53 WHERE inv.invoice_id = cp_invoice_id;
54
55 BEGIN
56 IF new_references.source_refund_id IS NOT NULL THEN
57 FOR recinv IN cur_inv(p_invoice_id) LOOP
58 igs_fi_inv_int_pkg.update_row (
59 x_rowid => recinv.row_id,
60 x_invoice_id => recinv.invoice_id,
61 x_person_id => recinv.person_id,
62 x_fee_type => recinv.fee_type,
63 x_fee_cat => recinv.fee_cat,
64 x_fee_cal_type => recinv.fee_cal_type,
65 x_fee_ci_sequence_number => recinv.fee_ci_sequence_number,
66 x_course_cd => recinv.course_cd,
67 x_attendance_mode => recinv.attendance_mode,
68 x_attendance_type => recinv.attendance_type,
69 x_invoice_amount_due => recinv.invoice_amount_due,
70 x_invoice_creation_date => recinv.invoice_creation_date,
71 x_invoice_desc => recinv.invoice_desc,
72 x_transaction_type => recinv.transaction_type,
73 x_currency_cd => recinv.currency_cd,
74 x_status => recinv.status,
75 x_attribute_category => recinv.attribute_category,
76 x_attribute1 => recinv.attribute1,
77 x_attribute2 => recinv.attribute2,
78 x_attribute3 => recinv.attribute3,
79 x_attribute4 => recinv.attribute4,
80 x_attribute5 => recinv.attribute5,
81 x_attribute6 => recinv.attribute6,
82 x_attribute7 => recinv.attribute7,
83 x_attribute8 => recinv.attribute8,
84 x_attribute9 => recinv.attribute9,
85 x_attribute10 => recinv.attribute10,
86 x_invoice_amount => recinv.invoice_amount,
87 x_bill_id => recinv.bill_id,
88 x_bill_number => recinv.bill_number,
89 x_bill_date => recinv.bill_date,
90 x_waiver_flag => 'Y',
91 x_waiver_reason => 'MANUAL_REFUND',
92 x_effective_date => recinv.effective_date,
93 x_invoice_number => recinv.invoice_number,
94 x_exchange_rate => recinv.exchange_rate,
95 x_bill_payment_due_date => recinv.bill_payment_due_date,
96 x_optional_fee_flag => recinv.optional_fee_flag,
97 x_mode => 'R',
98 x_reversal_gl_date => new_references.gl_date,
99 x_tax_year_code => recinv.tax_year_code,
100 x_waiver_name => recinv.waiver_name
101 );
102
103 END LOOP;
104 END IF;
105 END update_invoice;
106
107 PROCEDURE set_column_values (
108 p_action IN VARCHAR2,
109 x_rowid IN VARCHAR2,
110 x_refund_id IN NUMBER ,
111 x_voucher_date IN DATE ,
112 x_person_id IN NUMBER ,
113 x_pay_person_id IN NUMBER ,
114 x_dr_gl_ccid IN NUMBER ,
115 x_cr_gl_ccid IN NUMBER ,
116 x_dr_account_cd IN VARCHAR2,
117 x_cr_account_cd IN VARCHAR2,
118 x_refund_amount IN NUMBER ,
119 x_fee_type IN VARCHAR2,
120 x_fee_cal_type IN VARCHAR2,
121 x_fee_ci_sequence_number IN NUMBER ,
122 x_source_refund_id IN NUMBER ,
123 x_invoice_id IN NUMBER ,
124 x_transfer_status IN VARCHAR2,
125 x_reversal_ind IN VARCHAR2,
126 x_reason IN VARCHAR2,
127 x_attribute_category IN VARCHAR2,
128 x_attribute1 IN VARCHAR2,
129 x_attribute2 IN VARCHAR2,
130 x_attribute3 IN VARCHAR2,
131 x_attribute4 IN VARCHAR2,
132 x_attribute5 IN VARCHAR2,
133 x_attribute6 IN VARCHAR2,
134 x_attribute7 IN VARCHAR2,
135 x_attribute8 IN VARCHAR2,
136 x_attribute9 IN VARCHAR2,
137 x_attribute10 IN VARCHAR2,
138 x_attribute11 IN VARCHAR2,
139 x_attribute12 IN VARCHAR2,
140 x_attribute13 IN VARCHAR2,
141 x_attribute14 IN VARCHAR2,
142 x_attribute15 IN VARCHAR2,
143 x_attribute16 IN VARCHAR2,
144 x_attribute17 IN VARCHAR2,
145 x_attribute18 IN VARCHAR2,
146 x_attribute19 IN VARCHAR2,
147 x_attribute20 IN VARCHAR2,
148 x_creation_date IN DATE ,
149 x_created_by IN NUMBER ,
150 x_last_update_date IN DATE ,
151 x_last_updated_by IN NUMBER ,
152 x_last_update_login IN NUMBER ,
153 x_gl_date IN DATE ,
154 x_reversal_gl_date IN DATE
155 ) AS
156 /*******************************
157 who when what
158 smadathi 06-Nov-2002 Enh. Bug 2584986. Added new column GL_DATE,REVERSAL_GL_DATE
159 Bug: 2144600
160 What: Created TBH
161 Who: vchappid
162 When: 25-Feb-2002
163 skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns of the table IGS_FI_REFUNDS
164 vvutukur 23-Sep-2002 removed default clause from parameters as gscc fix.
165 **********************************/
166
167 CURSOR cur_old_ref_values IS
168 SELECT *
169 FROM igs_fi_refunds
170 WHERE rowid = x_rowid;
171
172 BEGIN
173
174 l_rowid := x_rowid;
175
176 -- Code for setting the Old and New Reference Values.
177 -- Populate Old Values.
178 OPEN cur_old_ref_values;
179 FETCH cur_old_ref_values INTO old_references;
180 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
181 CLOSE cur_old_ref_values;
182 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
183 igs_ge_msg_stack.add;
184 app_exception.raise_exception;
185 RETURN;
186 END IF;
187 CLOSE cur_old_ref_values;
188
189 -- Populate New Values.
190 new_references.refund_id := x_refund_id;
191 new_references.voucher_date := x_voucher_date;
192 new_references.person_id := x_person_id;
193 new_references.pay_person_id := x_pay_person_id;
194 new_references.dr_gl_ccid := x_dr_gl_ccid;
195 new_references.cr_gl_ccid := x_cr_gl_ccid;
196 new_references.dr_account_cd := x_dr_account_cd;
197 new_references.cr_account_cd := x_cr_account_cd;
198 new_references.refund_amount := x_refund_amount;
199 new_references.fee_type := x_fee_type;
200 new_references.fee_cal_type := x_fee_cal_type;
201 new_references.fee_ci_sequence_number := x_fee_ci_sequence_number;
202 new_references.source_refund_id := x_source_refund_id;
203 new_references.invoice_id := x_invoice_id;
204 new_references.transfer_status := x_transfer_status;
205 new_references.reversal_ind := x_reversal_ind;
206 new_references.reason := x_reason;
207 new_references.attribute_category := x_attribute_category;
208 new_references.attribute1 := x_attribute1;
209 new_references.attribute2 := x_attribute2;
210 new_references.attribute3 := x_attribute3;
211 new_references.attribute4 := x_attribute4;
212 new_references.attribute5 := x_attribute5;
213 new_references.attribute6 := x_attribute6;
214 new_references.attribute7 := x_attribute7;
215 new_references.attribute8 := x_attribute8;
216 new_references.attribute9 := x_attribute9;
217 new_references.attribute10 := x_attribute10;
218 new_references.attribute11 := x_attribute11;
219 new_references.attribute12 := x_attribute12;
220 new_references.attribute13 := x_attribute13;
221 new_references.attribute14 := x_attribute14;
222 new_references.attribute15 := x_attribute15;
223 new_references.attribute16 := x_attribute16;
224 new_references.attribute17 := x_attribute17;
225 new_references.attribute18 := x_attribute18;
226 new_references.attribute19 := x_attribute19;
227 new_references.attribute20 := x_attribute20;
228 new_references.gl_date := TRUNC(x_gl_date);
229 new_references.reversal_gl_date := TRUNC(x_reversal_gl_date);
230
231 IF (p_action = 'UPDATE') THEN
232 new_references.creation_date := old_references.creation_date;
233 new_references.created_by := old_references.created_by;
234 ELSE
235 new_references.creation_date := x_creation_date;
236 new_references.created_by := x_created_by;
237 END IF;
238
239 new_references.last_update_date := x_last_update_date;
240 new_references.last_updated_by := x_last_updated_by;
241 new_references.last_update_login := x_last_update_login;
242
243 END set_column_values;
244
245
246 PROCEDURE check_uniqueness AS
247 /*
248 || Created By :
249 || Created On : 26-FEB-2002
250 || Purpose : Handles the Unique Constraint logic defined for the columns.
251 || Known limitations, enhancements or remarks :
252 || Change History :
253 || Who When What
254 || (reverse chronological order - newest change first)
255 */
256 BEGIN
257
258 IF ( get_uk_for_validation (
259 new_references.person_id,
260 new_references.pay_person_id,
261 new_references.fee_type,
262 new_references.fee_cal_type,
263 new_references.fee_ci_sequence_number,
264 new_references.reversal_ind,
265 new_references.invoice_id
266 )
267 ) THEN
268 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
269 igs_ge_msg_stack.add;
270 app_exception.raise_exception;
271 END IF;
272
273 END check_uniqueness;
274
275
276 PROCEDURE check_parent_existance AS
277 /*
278 || Created By : vchappid
279 || Created On : 26-FEB-2002
280 || Purpose : Checks for the existance of Parent records.
281 || Known limitations, enhancements or remarks :
282 || Change History :
283 || Who When What
284 || (reverse chronological order - newest change first)
285 */
286 CURSOR cur_rowid (cp_party_id hz_parties.party_id%TYPE) IS
287 SELECT rowid
288 FROM hz_parties
289 WHERE party_id = cp_party_id
290 FOR UPDATE NOWAIT;
291
292 lv_rowid cur_rowid%RowType;
293
294 BEGIN
295
296 IF (((old_references.person_id = new_references.person_id)) OR
297 ((new_references.person_id IS NULL))) THEN
298 NULL;
299 ELSE
300 OPEN cur_rowid(new_references.person_id);
301 FETCH cur_rowid INTO lv_rowid;
302 IF (cur_rowid%FOUND) THEN
303 CLOSE cur_rowid;
304 ELSE
305 CLOSE cur_rowid;
306 fnd_message.set_name ('FND','FORM_RECORD_DELETED');
307 igs_ge_msg_stack.add;
308 app_exception.raise_exception;
309 END IF;
310 END IF;
311
312 IF (((old_references.pay_person_id = new_references.pay_person_id)) OR
313 ((new_references.pay_person_id IS NULL))) THEN
314 NULL;
315 ELSE
316 OPEN cur_rowid(new_references.pay_person_id);
317 FETCH cur_rowid INTO lv_rowid;
318 IF (cur_rowid%FOUND) THEN
319 CLOSE cur_rowid;
320 ELSE
321 CLOSE cur_rowid;
322 fnd_message.set_name ('FND','FORM_RECORD_DELETED');
323 igs_ge_msg_stack.add;
324 app_exception.raise_exception;
325 END IF;
326 END IF;
327
328 IF (((old_references.transfer_status = new_references.transfer_status)) OR
329 ((new_references.transfer_status IS NULL))) THEN
330 NULL;
331 ELSIF NOT igs_lookups_view_pkg.get_pk_for_validation('REFUND_TRANSFER_STATUS',
332 new_references.transfer_status)THEN
333 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
334 IGS_GE_MSG_STACK.ADD;
335 App_Exception.Raise_Exception;
336 END IF;
337
338 IF (((old_references.dr_account_cd = new_references.dr_account_cd)) OR
339 ((new_references.dr_account_cd IS NULL))) THEN
340 NULL;
341 ELSIF NOT igs_fi_acc_pkg.get_pk_for_validation (
342 new_references.dr_account_cd
343 ) THEN
344 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
345 igs_ge_msg_stack.add;
346 app_exception.raise_exception;
347 END IF;
348
349 IF (((old_references.cr_account_cd = new_references.cr_account_cd)) OR
350 ((new_references.cr_account_cd IS NULL))) THEN
351 NULL;
352 ELSIF NOT igs_fi_acc_pkg.get_pk_for_validation (
353 new_references.cr_account_cd
354 ) THEN
355 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
356 igs_ge_msg_stack.add;
357 app_exception.raise_exception;
358 END IF;
359
360 IF (((old_references.fee_cal_type = new_references.fee_cal_type) AND
361 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number) AND
362 (old_references.fee_type = new_references.fee_type)) OR
363 ((new_references.fee_cal_type IS NULL) OR
364 (new_references.fee_ci_sequence_number IS NULL) OR
365 (new_references.fee_type IS NULL))) THEN
366 NULL;
367 ELSIF NOT igs_fi_f_typ_ca_inst_pkg.get_pk_for_validation ( new_references.fee_type,
368 new_references.fee_cal_type,
369 new_references.fee_ci_sequence_number ) THEN
370 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
371 igs_ge_msg_stack.add;
372 app_exception.raise_exception;
373 END IF;
374
375 IF (((old_references.source_refund_id = new_references.source_refund_id)) OR
376 ((new_references.source_refund_id IS NULL))) THEN
377 NULL;
378 ELSIF NOT igs_fi_refunds_pkg.get_pk_for_validation (
379 new_references.source_refund_id
380 ) THEN
381 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
382 igs_ge_msg_stack.add;
383 app_exception.raise_exception;
384 END IF;
385
386 IF (((old_references.invoice_id = new_references.invoice_id)) OR
387 ((new_references.invoice_id IS NULL))) THEN
388 NULL;
389 ELSIF NOT igs_fi_inv_int_pkg.get_pk_for_validation (
390 new_references.invoice_id
391 ) THEN
392 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
393 igs_ge_msg_stack.add;
394 app_exception.raise_exception;
395 END IF;
396
397 END check_parent_existance;
398
399
400 PROCEDURE check_child_existance IS
401 /*
402 || Created By :
403 || Created On : 26-FEB-2002
404 || Purpose : Checks for the existance of Child records.
405 || Known limitations, enhancements or remarks :
406 || Change History :
407 || Who When What
408 || (reverse chronological order - newest change first)
409 */
410 BEGIN
411
412 igs_fi_refunds_pkg.get_fk_igs_fi_refunds (
413 old_references.refund_id
414 );
415
416 igs_fi_refund_int_pkg.get_fk_igs_fi_refunds (
417 old_references.refund_id
418 );
419
420 END check_child_existance;
421
422
423 FUNCTION get_pk_for_validation (
424 x_refund_id IN NUMBER
425 ) RETURN BOOLEAN AS
426 /*
427 || Created By : 2144600
428 || Created On : 26-FEB-2002
429 || Purpose : Validates the Primary Key of the table.
430 || Known limitations, enhancements or remarks :
431 || Change History :
432 || Who When What
433 || (reverse chronological order - newest change first)
434 */
435 CURSOR cur_rowid IS
436 SELECT rowid
437 FROM igs_fi_refunds
438 WHERE refund_id = x_refund_id
439 FOR UPDATE NOWAIT;
440
441 lv_rowid cur_rowid%RowType;
442
443 BEGIN
444
445 OPEN cur_rowid;
446 FETCH cur_rowid INTO lv_rowid;
447 IF (cur_rowid%FOUND) THEN
448 CLOSE cur_rowid;
449 RETURN(TRUE);
450 ELSE
451 CLOSE cur_rowid;
452 RETURN(FALSE);
453 END IF;
454
455 END get_pk_for_validation;
456
457
458 FUNCTION get_uk_for_validation (
459 x_person_id IN NUMBER,
460 x_pay_person_id IN NUMBER,
461 x_fee_type IN VARCHAR2,
462 x_fee_cal_type IN VARCHAR2,
463 x_fee_ci_sequence_number IN NUMBER,
464 x_reversal_ind IN VARCHAR2,
465 x_invoice_id IN NUMBER
466 ) RETURN BOOLEAN AS
467 /*
468 || Created By : vchappid
469 || Created On : 26-FEB-2002
470 || Purpose : Validates the Unique Keys of the table.
471 || Known limitations, enhancements or remarks :
472 || Change History :
473 || Who When What
474 || (reverse chronological order - newest change first)
475 */
476 CURSOR cur_rowid IS
477 SELECT rowid
478 FROM igs_fi_refunds
479 WHERE person_id = x_person_id
480 AND pay_person_id = x_pay_person_id
481 AND fee_type = x_fee_type
482 AND fee_cal_type = x_fee_cal_type
483 AND fee_ci_sequence_number = x_fee_ci_sequence_number
484 AND ((invoice_id = x_invoice_id) OR (invoice_id IS NULL AND x_invoice_id IS NULL))
485 AND ((reversal_ind = x_reversal_ind) OR (reversal_ind IS NULL AND x_reversal_ind IS NULL))
486 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
487
488 lv_rowid cur_rowid%RowType;
489
490 BEGIN
491
492 OPEN cur_rowid;
493 FETCH cur_rowid INTO lv_rowid;
494 IF (cur_rowid%FOUND) THEN
495 CLOSE cur_rowid;
496 RETURN (true);
497 ELSE
498 CLOSE cur_rowid;
499 RETURN(FALSE);
500 END IF;
501
502 END get_uk_for_validation ;
503
504
505 PROCEDURE get_fk_igs_fi_refunds (
506 x_refund_id IN NUMBER
507 ) AS
508 /*
509 || Created By : vchappid
510 || Created On : 26-FEB-2002
511 || Purpose : Validates the Foreign Keys for the table.
512 || Known limitations, enhancements or remarks :
513 || Change History :
514 || Who When What
515 || (reverse chronological order - newest change first)
516 */
517 CURSOR cur_rowid IS
518 SELECT rowid
519 FROM igs_fi_refunds
520 WHERE ((source_refund_id = x_refund_id));
521
522 lv_rowid cur_rowid%RowType;
523
524 BEGIN
525
526 OPEN cur_rowid;
527 FETCH cur_rowid INTO lv_rowid;
528 IF (cur_rowid%FOUND) THEN
529 CLOSE cur_rowid;
530 fnd_message.set_name ('IGS', 'IGS_FI_RFND_RFND_FK');
531 igs_ge_msg_stack.add;
532 app_exception.raise_exception;
533 RETURN;
534 END IF;
535 CLOSE cur_rowid;
536
537 END get_fk_igs_fi_refunds;
538
539
540 PROCEDURE get_fk_igs_fi_inv_int (
541 x_invoice_id IN NUMBER
542 ) AS
543 /*
544 || Created By : vchappid
545 || Created On : 26-FEB-2002
546 || Purpose : Validates the Foreign Keys for the table.
547 || Known limitations, enhancements or remarks :
548 || Change History :
549 || Who When What
550 || (reverse chronological order - newest change first)
551 || skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns of the table IGS_FI_REFUNDS
552 */
553 CURSOR cur_rowid IS
554 SELECT rowid
555 FROM igs_fi_refunds
556 WHERE ((invoice_id = x_invoice_id));
557
558 lv_rowid cur_rowid%RowType;
559
560 BEGIN
561
562 OPEN cur_rowid;
563 FETCH cur_rowid INTO lv_rowid;
564 IF (cur_rowid%FOUND) THEN
565 CLOSE cur_rowid;
566 fnd_message.set_name ('IGS', 'IGS_FI_INV_RFND_FK');
567 igs_ge_msg_stack.add;
568 app_exception.raise_exception;
569 RETURN;
570 END IF;
571 CLOSE cur_rowid;
572
573 END get_fk_igs_fi_inv_int;
574
575
576 PROCEDURE before_dml (
577 p_action IN VARCHAR2,
578 x_rowid IN VARCHAR2,
579 x_refund_id IN NUMBER ,
580 x_voucher_date IN DATE ,
581 x_person_id IN NUMBER ,
582 x_pay_person_id IN NUMBER ,
583 x_dr_gl_ccid IN NUMBER ,
584 x_cr_gl_ccid IN NUMBER ,
585 x_dr_account_cd IN VARCHAR2,
586 x_cr_account_cd IN VARCHAR2,
587 x_refund_amount IN NUMBER ,
588 x_fee_type IN VARCHAR2,
589 x_fee_cal_type IN VARCHAR2,
590 x_fee_ci_sequence_number IN NUMBER ,
591 x_source_refund_id IN NUMBER ,
592 x_invoice_id IN NUMBER ,
593 x_transfer_status IN VARCHAR2,
594 x_reversal_ind IN VARCHAR2,
595 x_reason IN VARCHAR2,
596 x_attribute_category IN VARCHAR2,
597 x_attribute1 IN VARCHAR2,
598 x_attribute2 IN VARCHAR2,
599 x_attribute3 IN VARCHAR2,
600 x_attribute4 IN VARCHAR2,
601 x_attribute5 IN VARCHAR2,
602 x_attribute6 IN VARCHAR2,
603 x_attribute7 IN VARCHAR2,
604 x_attribute8 IN VARCHAR2,
605 x_attribute9 IN VARCHAR2,
606 x_attribute10 IN VARCHAR2,
607 x_attribute11 IN VARCHAR2,
608 x_attribute12 IN VARCHAR2,
609 x_attribute13 IN VARCHAR2,
610 x_attribute14 IN VARCHAR2,
611 x_attribute15 IN VARCHAR2,
612 x_attribute16 IN VARCHAR2,
613 x_attribute17 IN VARCHAR2,
614 x_attribute18 IN VARCHAR2,
615 x_attribute19 IN VARCHAR2,
616 x_attribute20 IN VARCHAR2,
617 x_creation_date IN DATE ,
618 x_created_by IN NUMBER ,
619 x_last_update_date IN DATE ,
620 x_last_updated_by IN NUMBER ,
621 x_last_update_login IN NUMBER ,
622 x_gl_date IN DATE ,
623 x_reversal_gl_date IN DATE
624 ) AS
625 /*
626 || Created By : vchappid
627 || Created On : 26-FEB-2002
628 || Purpose : Initialises the columns, Checks Constraints, Calls the
629 || Trigger Handlers for the table, before any DML operation.
630 || Known limitations, enhancements or remarks :
631 || Change History :
632 || Who When What
633 || (reverse chronological order - newest change first)
634 || skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns of the table IGS_FI_REFUNDS
635 || smadathi 06-Nov-2002 Enh. Bug 2584986. Added new column GL_DATE,REVERSAL_GL_DATE
636 || vvutukur 23-Sep-2002 Enh#2564643.Removed default clause as gscc fix.
637 */
638 BEGIN
639 set_column_values (
640 p_action,
641 x_rowid,
642 x_refund_id,
643 x_voucher_date,
644 x_person_id,
645 x_pay_person_id,
646 x_dr_gl_ccid,
647 x_cr_gl_ccid,
648 x_dr_account_cd,
649 x_cr_account_cd,
650 x_refund_amount,
651 x_fee_type,
652 x_fee_cal_type,
653 x_fee_ci_sequence_number,
654 x_source_refund_id,
655 x_invoice_id,
656 x_transfer_status,
657 x_reversal_ind,
658 x_reason,
659 x_attribute_category,
660 x_attribute1,
661 x_attribute2,
662 x_attribute3,
663 x_attribute4,
664 x_attribute5,
665 x_attribute6,
666 x_attribute7,
667 x_attribute8,
668 x_attribute9,
669 x_attribute10,
670 x_attribute11,
671 x_attribute12,
672 x_attribute13,
673 x_attribute14,
674 x_attribute15,
675 x_attribute16,
676 x_attribute17,
677 x_attribute18,
678 x_attribute19,
679 x_attribute20,
680 x_creation_date,
681 x_created_by,
682 x_last_update_date,
683 x_last_updated_by,
684 x_last_update_login ,
685 x_gl_date,
686 x_reversal_gl_date
687 );
688
689 IF (p_action = 'INSERT') THEN
690 -- Call all the procedures related to Before Insert.
691 IF ( get_pk_for_validation(
692 new_references.refund_id
693 )
694 ) THEN
695 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
696 igs_ge_msg_stack.add;
697 app_exception.raise_exception;
698 END IF;
699 check_uniqueness;
700 check_parent_existance;
701 beforerowinsert;
702 ELSIF (p_action = 'UPDATE') THEN
703 -- Call all the procedures related to Before Update.
704 check_uniqueness;
705 check_parent_existance;
706 ELSIF (p_action = 'DELETE') THEN
707 -- Call all the procedures related to Before Delete.
708 check_child_existance;
709 ELSIF (p_action = 'VALIDATE_INSERT') THEN
710 -- Call all the procedures related to Before Insert.
711 IF ( get_pk_for_validation (
712 new_references.refund_id
713 )
714 ) THEN
715 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
716 igs_ge_msg_stack.add;
717 app_exception.raise_exception;
718 END IF;
719 check_uniqueness;
720 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
721 check_uniqueness;
722 ELSIF (p_action = 'VALIDATE_DELETE') THEN
723 check_child_existance;
724 END IF;
725
726 END before_dml;
727
728
729 PROCEDURE insert_row (
730 x_rowid IN OUT NOCOPY VARCHAR2,
731 x_refund_id IN OUT NOCOPY NUMBER,
732 x_voucher_date IN DATE,
733 x_person_id IN NUMBER,
734 x_pay_person_id IN NUMBER,
735 x_dr_gl_ccid IN NUMBER,
736 x_cr_gl_ccid IN NUMBER,
737 x_dr_account_cd IN VARCHAR2,
738 x_cr_account_cd IN VARCHAR2,
739 x_refund_amount IN NUMBER,
740 x_fee_type IN VARCHAR2,
741 x_fee_cal_type IN VARCHAR2,
742 x_fee_ci_sequence_number IN NUMBER,
743 x_source_refund_id IN NUMBER,
744 x_invoice_id IN OUT NOCOPY NUMBER,
745 x_transfer_status IN VARCHAR2,
746 x_reversal_ind IN VARCHAR2,
747 x_reason IN VARCHAR2,
748 x_attribute_category IN VARCHAR2,
749 x_attribute1 IN VARCHAR2,
750 x_attribute2 IN VARCHAR2,
751 x_attribute3 IN VARCHAR2,
752 x_attribute4 IN VARCHAR2,
753 x_attribute5 IN VARCHAR2,
754 x_attribute6 IN VARCHAR2,
755 x_attribute7 IN VARCHAR2,
756 x_attribute8 IN VARCHAR2,
757 x_attribute9 IN VARCHAR2,
758 x_attribute10 IN VARCHAR2,
759 x_attribute11 IN VARCHAR2,
760 x_attribute12 IN VARCHAR2,
761 x_attribute13 IN VARCHAR2,
762 x_attribute14 IN VARCHAR2,
763 x_attribute15 IN VARCHAR2,
764 x_attribute16 IN VARCHAR2,
765 x_attribute17 IN VARCHAR2,
766 x_attribute18 IN VARCHAR2,
767 x_attribute19 IN VARCHAR2,
768 x_attribute20 IN VARCHAR2,
769 x_mode IN VARCHAR2,
770 x_gl_date IN DATE ,
771 x_reversal_gl_date IN DATE
772 ) AS
773 /*
774 || Created By : vchappid
775 || Created On : 26-FEB-2002
776 || Purpose : Handles the INSERT DML logic for the table.
777 || Known limitations, enhancements or remarks :
778 || Change History :
779 || Who When What
780 || (reverse chronological order - newest change first)
781 || skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns of the table IGS_FI_REFUNDS
782 || smadathi 06-Nov-2002 Enh. Bug 2584986. Added new column GL_DATE,REVERSAL_GL_DATE
783 || vvutukur 23-Sep-2002 Enh#2564643.Removed default clause from parameters as gscc fix.
784 */
785 CURSOR c IS
786 SELECT rowid
787 FROM igs_fi_refunds
788 WHERE refund_id = x_refund_id;
789
790 x_last_update_date DATE;
791 x_last_updated_by NUMBER;
792 x_last_update_login NUMBER;
793 x_request_id NUMBER;
794 x_program_id NUMBER;
795 x_program_application_id NUMBER;
796 x_program_update_date DATE;
797
798 BEGIN
799
800 x_last_update_date := SYSDATE;
801 IF (x_mode = 'I') THEN
802 x_last_updated_by := 1;
803 x_last_update_login := 0;
804 ELSIF (x_mode = 'R') THEN
805 x_last_updated_by := fnd_global.user_id;
806 IF (x_last_updated_by IS NULL) THEN
807 x_last_updated_by := -1;
808 END IF;
809 x_last_update_login := fnd_global.login_id;
810 IF (x_last_update_login IS NULL) THEN
811 x_last_update_login := -1;
812 END IF;
813 x_request_id := fnd_global.conc_request_id;
814 x_program_id := fnd_global.conc_program_id;
815 x_program_application_id := fnd_global.prog_appl_id;
816
817 IF (x_request_id = -1) THEN
818 x_request_id := NULL;
819 x_program_id := NULL;
820 x_program_application_id := NULL;
821 x_program_update_date := NULL;
822 ELSE
823 x_program_update_date := SYSDATE;
824 END IF;
825 ELSE
826 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
827 igs_ge_msg_stack.add;
828 app_exception.raise_exception;
829 END IF;
830
831 SELECT igs_fi_refunds_s.NEXTVAL
832 INTO x_refund_id
833 FROM dual;
834
835 before_dml(
836 p_action => 'INSERT',
837 x_rowid => x_rowid,
838 x_refund_id => x_refund_id,
839 x_voucher_date => x_voucher_date,
840 x_person_id => x_person_id,
841 x_pay_person_id => x_pay_person_id,
842 x_dr_gl_ccid => x_dr_gl_ccid,
843 x_cr_gl_ccid => x_cr_gl_ccid,
844 x_dr_account_cd => x_dr_account_cd,
845 x_cr_account_cd => x_cr_account_cd,
846 x_refund_amount => x_refund_amount,
847 x_fee_type => x_fee_type,
848 x_fee_cal_type => x_fee_cal_type,
849 x_fee_ci_sequence_number => x_fee_ci_sequence_number,
850 x_source_refund_id => x_source_refund_id,
851 x_invoice_id => x_invoice_id,
852 x_transfer_status => x_transfer_status,
853 x_reversal_ind => x_reversal_ind,
854 x_reason => x_reason,
855 x_attribute_category => x_attribute_category,
856 x_attribute1 => x_attribute1,
857 x_attribute2 => x_attribute2,
858 x_attribute3 => x_attribute3,
859 x_attribute4 => x_attribute4,
860 x_attribute5 => x_attribute5,
861 x_attribute6 => x_attribute6,
862 x_attribute7 => x_attribute7,
863 x_attribute8 => x_attribute8,
864 x_attribute9 => x_attribute9,
865 x_attribute10 => x_attribute10,
866 x_attribute11 => x_attribute11,
867 x_attribute12 => x_attribute12,
868 x_attribute13 => x_attribute13,
869 x_attribute14 => x_attribute14,
870 x_attribute15 => x_attribute15,
871 x_attribute16 => x_attribute16,
872 x_attribute17 => x_attribute17,
873 x_attribute18 => x_attribute18,
874 x_attribute19 => x_attribute19,
875 x_attribute20 => x_attribute20,
876 x_creation_date => x_last_update_date,
877 x_created_by => x_last_updated_by,
878 x_last_update_date => x_last_update_date,
879 x_last_updated_by => x_last_updated_by,
880 x_last_update_login => x_last_update_login ,
881 x_gl_date => x_gl_date,
882 x_reversal_gl_date => x_reversal_gl_date
883 );
884
885 INSERT INTO igs_fi_refunds (
886 refund_id,
887 voucher_date,
888 person_id,
889 pay_person_id,
890 dr_gl_ccid,
891 cr_gl_ccid,
892 dr_account_cd,
893 cr_account_cd,
894 refund_amount,
895 fee_type,
896 fee_cal_type,
897 fee_ci_sequence_number,
898 source_refund_id,
899 invoice_id,
900 transfer_status,
901 reversal_ind,
902 reason,
903 attribute_category,
904 attribute1,
905 attribute2,
906 attribute3,
907 attribute4,
908 attribute5,
909 attribute6,
910 attribute7,
911 attribute8,
912 attribute9,
913 attribute10,
914 attribute11,
915 attribute12,
916 attribute13,
917 attribute14,
918 attribute15,
919 attribute16,
920 attribute17,
921 attribute18,
922 attribute19,
923 attribute20,
924 creation_date,
925 created_by,
926 last_update_date,
927 last_updated_by,
928 last_update_login,
929 request_id,
930 program_id,
931 program_application_id,
932 program_update_date ,
933 gl_date,
934 reversal_gl_date
935 ) VALUES (
936 new_references.refund_id,
937 new_references.voucher_date,
938 new_references.person_id,
939 new_references.pay_person_id,
940 new_references.dr_gl_ccid,
941 new_references.cr_gl_ccid,
942 new_references.dr_account_cd,
943 new_references.cr_account_cd,
944 new_references.refund_amount,
945 new_references.fee_type,
946 new_references.fee_cal_type,
947 new_references.fee_ci_sequence_number,
948 new_references.source_refund_id,
949 new_references.invoice_id,
950 new_references.transfer_status,
951 new_references.reversal_ind,
952 new_references.reason,
953 new_references.attribute_category,
954 new_references.attribute1,
955 new_references.attribute2,
956 new_references.attribute3,
957 new_references.attribute4,
958 new_references.attribute5,
959 new_references.attribute6,
960 new_references.attribute7,
961 new_references.attribute8,
962 new_references.attribute9,
963 new_references.attribute10,
964 new_references.attribute11,
965 new_references.attribute12,
966 new_references.attribute13,
967 new_references.attribute14,
968 new_references.attribute15,
969 new_references.attribute16,
970 new_references.attribute17,
971 new_references.attribute18,
972 new_references.attribute19,
973 new_references.attribute20,
974 x_last_update_date,
975 x_last_updated_by,
976 x_last_update_date,
977 x_last_updated_by,
978 x_last_update_login ,
979 x_request_id,
980 x_program_id,
981 x_program_application_id,
982 x_program_update_date ,
983 new_references.gl_date,
984 new_references.reversal_gl_date
985 );
986
987 OPEN c;
988 FETCH c INTO x_rowid;
989 IF (c%NOTFOUND) THEN
990 CLOSE c;
991 RAISE NO_DATA_FOUND;
992 END IF;
993 CLOSE c;
994 x_invoice_id := new_references.invoice_id;
995 END insert_row;
996
997
998 PROCEDURE lock_row (
999 x_rowid IN VARCHAR2,
1000 x_refund_id IN NUMBER,
1001 x_voucher_date IN DATE,
1002 x_person_id IN NUMBER,
1003 x_pay_person_id IN NUMBER,
1004 x_dr_gl_ccid IN NUMBER,
1005 x_cr_gl_ccid IN NUMBER,
1006 x_dr_account_cd IN VARCHAR2,
1007 x_cr_account_cd IN VARCHAR2,
1008 x_refund_amount IN NUMBER,
1009 x_fee_type IN VARCHAR2,
1010 x_fee_cal_type IN VARCHAR2,
1011 x_fee_ci_sequence_number IN NUMBER,
1012 x_source_refund_id IN NUMBER,
1013 x_invoice_id IN NUMBER,
1014 x_transfer_status IN VARCHAR2,
1015 x_reversal_ind IN VARCHAR2,
1016 x_reason IN VARCHAR2,
1017 x_attribute_category IN VARCHAR2,
1018 x_attribute1 IN VARCHAR2,
1019 x_attribute2 IN VARCHAR2,
1020 x_attribute3 IN VARCHAR2,
1021 x_attribute4 IN VARCHAR2,
1022 x_attribute5 IN VARCHAR2,
1023 x_attribute6 IN VARCHAR2,
1024 x_attribute7 IN VARCHAR2,
1025 x_attribute8 IN VARCHAR2,
1026 x_attribute9 IN VARCHAR2,
1027 x_attribute10 IN VARCHAR2,
1028 x_attribute11 IN VARCHAR2,
1029 x_attribute12 IN VARCHAR2,
1030 x_attribute13 IN VARCHAR2,
1031 x_attribute14 IN VARCHAR2,
1032 x_attribute15 IN VARCHAR2,
1033 x_attribute16 IN VARCHAR2,
1034 x_attribute17 IN VARCHAR2,
1035 x_attribute18 IN VARCHAR2,
1036 x_attribute19 IN VARCHAR2,
1037 x_attribute20 IN VARCHAR2,
1038 x_gl_date IN DATE,
1039 x_reversal_gl_date IN DATE
1040 ) AS
1041 /*
1042 || Created By : vchappid
1043 || Created On : 26-FEB-2002
1044 || Purpose : Handles the LOCK mechanism for the table.
1045 || Known limitations, enhancements or remarks :
1046 || Change History :
1047 || Who When What
1048 || skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns of the table IGS_FI_REFUNDS
1049 || smadathi 06-Nov-2002 Enh. Bug 2584986. Added new column GL_DATE,REVERSAL_GL_DATE
1050 || (reverse chronological order - newest change first)
1051 */
1052 CURSOR c1 IS
1053 SELECT
1054 voucher_date,
1055 person_id,
1056 pay_person_id,
1057 dr_gl_ccid,
1058 cr_gl_ccid,
1059 dr_account_cd,
1060 cr_account_cd,
1061 refund_amount,
1062 fee_type,
1063 fee_cal_type,
1064 fee_ci_sequence_number,
1065 source_refund_id,
1066 invoice_id,
1067 transfer_status,
1068 reversal_ind,
1069 reason,
1070 attribute_category,
1071 attribute1,
1072 attribute2,
1073 attribute3,
1074 attribute4,
1075 attribute5,
1076 attribute6,
1077 attribute7,
1078 attribute8,
1079 attribute9,
1080 attribute10,
1081 attribute11,
1082 attribute12,
1083 attribute13,
1084 attribute14,
1085 attribute15,
1086 attribute16,
1087 attribute17,
1088 attribute18,
1089 attribute19,
1090 attribute20,
1091 gl_date,
1092 reversal_gl_date
1093 FROM igs_fi_refunds
1094 WHERE rowid = x_rowid
1095 FOR UPDATE NOWAIT;
1096
1097 tlinfo c1%ROWTYPE;
1098
1099 BEGIN
1100
1101 OPEN c1;
1102 FETCH c1 INTO tlinfo;
1103 IF (c1%notfound) THEN
1104 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1105 igs_ge_msg_stack.add;
1106 CLOSE c1;
1107 app_exception.raise_exception;
1108 RETURN;
1109 END IF;
1110 CLOSE c1;
1111
1112 IF (
1113 (tlinfo.voucher_date = x_voucher_date)
1114 AND (tlinfo.person_id = x_person_id)
1115 AND (tlinfo.pay_person_id = x_pay_person_id)
1116 AND ((tlinfo.dr_gl_ccid = x_dr_gl_ccid) OR ((tlinfo.dr_gl_ccid IS NULL) AND (X_dr_gl_ccid IS NULL)))
1117 AND ((tlinfo.cr_gl_ccid = x_cr_gl_ccid) OR ((tlinfo.cr_gl_ccid IS NULL) AND (X_cr_gl_ccid IS NULL)))
1118 AND ((tlinfo.dr_account_cd = x_dr_account_cd) OR ((tlinfo.dr_account_cd IS NULL) AND (X_dr_account_cd IS NULL)))
1119 AND ((tlinfo.cr_account_cd = x_cr_account_cd) OR ((tlinfo.cr_account_cd IS NULL) AND (X_cr_account_cd IS NULL)))
1120 AND (tlinfo.refund_amount = x_refund_amount)
1121 AND (tlinfo.fee_type = x_fee_type)
1122 AND (tlinfo.fee_cal_type = x_fee_cal_type)
1123 AND (tlinfo.fee_ci_sequence_number = x_fee_ci_sequence_number)
1124 AND ((tlinfo.source_refund_id = x_source_refund_id) OR ((tlinfo.source_refund_id IS NULL) AND (X_source_refund_id IS NULL)))
1125 AND ((tlinfo.invoice_id = x_invoice_id) OR ((tlinfo.invoice_id IS NULL) AND (X_invoice_id IS NULL)))
1126 AND ((tlinfo.transfer_status = x_transfer_status) OR ((tlinfo.transfer_status IS NULL) AND (X_transfer_status IS NULL)))
1127 AND ((tlinfo.reversal_ind = x_reversal_ind) OR ((tlinfo.reversal_ind IS NULL) AND (X_reversal_ind IS NULL)))
1128 AND ((tlinfo.reason = x_reason) OR ((tlinfo.reason IS NULL) AND (X_reason IS NULL)))
1129 AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (X_attribute_category IS NULL)))
1130 AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (X_attribute1 IS NULL)))
1131 AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (X_attribute2 IS NULL)))
1132 AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (X_attribute3 IS NULL)))
1133 AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (X_attribute4 IS NULL)))
1134 AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (X_attribute5 IS NULL)))
1135 AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (X_attribute6 IS NULL)))
1136 AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (X_attribute7 IS NULL)))
1137 AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (X_attribute8 IS NULL)))
1138 AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (X_attribute9 IS NULL)))
1139 AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (X_attribute10 IS NULL)))
1140 AND ((tlinfo.attribute11 = x_attribute11) OR ((tlinfo.attribute11 IS NULL) AND (X_attribute11 IS NULL)))
1141 AND ((tlinfo.attribute12 = x_attribute12) OR ((tlinfo.attribute12 IS NULL) AND (X_attribute12 IS NULL)))
1142 AND ((tlinfo.attribute13 = x_attribute13) OR ((tlinfo.attribute13 IS NULL) AND (X_attribute13 IS NULL)))
1143 AND ((tlinfo.attribute14 = x_attribute14) OR ((tlinfo.attribute14 IS NULL) AND (X_attribute14 IS NULL)))
1144 AND ((tlinfo.attribute15 = x_attribute15) OR ((tlinfo.attribute15 IS NULL) AND (X_attribute15 IS NULL)))
1145 AND ((tlinfo.attribute16 = x_attribute16) OR ((tlinfo.attribute16 IS NULL) AND (X_attribute16 IS NULL)))
1146 AND ((tlinfo.attribute17 = x_attribute17) OR ((tlinfo.attribute17 IS NULL) AND (X_attribute17 IS NULL)))
1147 AND ((tlinfo.attribute18 = x_attribute18) OR ((tlinfo.attribute18 IS NULL) AND (X_attribute18 IS NULL)))
1148 AND ((tlinfo.attribute19 = x_attribute19) OR ((tlinfo.attribute19 IS NULL) AND (X_attribute19 IS NULL)))
1149 AND ((tlinfo.attribute20 = x_attribute20) OR ((tlinfo.attribute20 IS NULL) AND (X_attribute20 IS NULL)))
1150 AND ((TRUNC(tlinfo.gl_date) = TRUNC(x_gl_date)) OR ((tlinfo.gl_date IS NULL) AND (X_gl_date IS NULL)))
1151 AND ((TRUNC(tlinfo.reversal_gl_date) = TRUNC(x_reversal_gl_date)) OR ((tlinfo.reversal_gl_date IS NULL) AND (X_reversal_gl_date IS NULL)))
1152 ) THEN
1153 NULL;
1154 ELSE
1155 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1156 igs_ge_msg_stack.add;
1157 app_exception.raise_exception;
1158 END IF;
1159
1160 RETURN;
1161
1162 END lock_row;
1163
1164
1165 PROCEDURE update_row (
1166 x_rowid IN VARCHAR2,
1167 x_refund_id IN NUMBER,
1168 x_voucher_date IN DATE,
1169 x_person_id IN NUMBER,
1170 x_pay_person_id IN NUMBER,
1171 x_dr_gl_ccid IN NUMBER,
1172 x_cr_gl_ccid IN NUMBER,
1173 x_dr_account_cd IN VARCHAR2,
1174 x_cr_account_cd IN VARCHAR2,
1175 x_refund_amount IN NUMBER,
1176 x_fee_type IN VARCHAR2,
1177 x_fee_cal_type IN VARCHAR2,
1178 x_fee_ci_sequence_number IN NUMBER,
1179 x_source_refund_id IN NUMBER,
1180 x_invoice_id IN NUMBER,
1181 x_transfer_status IN VARCHAR2,
1182 x_reversal_ind IN VARCHAR2,
1183 x_reason IN VARCHAR2,
1184 x_attribute_category IN VARCHAR2,
1185 x_attribute1 IN VARCHAR2,
1186 x_attribute2 IN VARCHAR2,
1187 x_attribute3 IN VARCHAR2,
1188 x_attribute4 IN VARCHAR2,
1189 x_attribute5 IN VARCHAR2,
1190 x_attribute6 IN VARCHAR2,
1191 x_attribute7 IN VARCHAR2,
1192 x_attribute8 IN VARCHAR2,
1193 x_attribute9 IN VARCHAR2,
1194 x_attribute10 IN VARCHAR2,
1195 x_attribute11 IN VARCHAR2,
1196 x_attribute12 IN VARCHAR2,
1197 x_attribute13 IN VARCHAR2,
1198 x_attribute14 IN VARCHAR2,
1199 x_attribute15 IN VARCHAR2,
1200 x_attribute16 IN VARCHAR2,
1201 x_attribute17 IN VARCHAR2,
1202 x_attribute18 IN VARCHAR2,
1203 x_attribute19 IN VARCHAR2,
1204 x_attribute20 IN VARCHAR2,
1205 x_mode IN VARCHAR2,
1206 x_gl_date IN DATE,
1207 x_reversal_gl_date IN DATE
1208 ) AS
1209 /*
1210 || Created By :
1211 || Created On : 26-FEB-2002
1212 || Purpose : Handles the UPDATE DML logic for the table.
1213 || Known limitations, enhancements or remarks :
1214 || Change History :
1215 || Who When What
1216 || (reverse chronological order - newest change first)
1217 || skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns of the table IGS_FI_REFUNDS
1218 || smadathi 06-Nov-2002 Enh. Bug 2584986. Added new column GL_DATE,REVERSAL_GL_DATE
1219 || vvutukur 23-Sep-2002 Enh#2564643.Removed DEFAULT clause from parameters as gscc fix.
1220 */
1221 x_last_update_date DATE ;
1222 x_last_updated_by NUMBER;
1223 x_last_update_login NUMBER;
1224 x_request_id NUMBER;
1225 x_program_id NUMBER;
1226 x_program_application_id NUMBER;
1227 x_program_update_date DATE;
1228
1229 BEGIN
1230
1231 x_last_update_date := SYSDATE;
1232 IF (X_MODE = 'I') THEN
1233 x_last_updated_by := 1;
1234 x_last_update_login := 0;
1235 ELSIF (x_mode = 'R') THEN
1236 x_last_updated_by := fnd_global.user_id;
1237 IF x_last_updated_by IS NULL THEN
1238 x_last_updated_by := -1;
1239 END IF;
1240 x_last_update_login := fnd_global.login_id;
1241 IF (x_last_update_login IS NULL) THEN
1242 x_last_update_login := -1;
1243 END IF;
1244 ELSE
1245 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1246 igs_ge_msg_stack.add;
1247 app_exception.raise_exception;
1248 END IF;
1249
1250 before_dml(
1251 p_action => 'UPDATE',
1252 x_rowid => x_rowid,
1253 x_refund_id => x_refund_id,
1254 x_voucher_date => x_voucher_date,
1255 x_person_id => x_person_id,
1256 x_pay_person_id => x_pay_person_id,
1257 x_dr_gl_ccid => x_dr_gl_ccid,
1258 x_cr_gl_ccid => x_cr_gl_ccid,
1259 x_dr_account_cd => x_dr_account_cd,
1260 x_cr_account_cd => x_cr_account_cd,
1261 x_refund_amount => x_refund_amount,
1262 x_fee_type => x_fee_type,
1263 x_fee_cal_type => x_fee_cal_type,
1264 x_fee_ci_sequence_number => x_fee_ci_sequence_number,
1265 x_source_refund_id => x_source_refund_id,
1266 x_invoice_id => x_invoice_id,
1267 x_transfer_status => x_transfer_status,
1268 x_reversal_ind => x_reversal_ind,
1269 x_reason => x_reason,
1270 x_attribute_category => x_attribute_category,
1271 x_attribute1 => x_attribute1,
1272 x_attribute2 => x_attribute2,
1273 x_attribute3 => x_attribute3,
1274 x_attribute4 => x_attribute4,
1275 x_attribute5 => x_attribute5,
1276 x_attribute6 => x_attribute6,
1277 x_attribute7 => x_attribute7,
1278 x_attribute8 => x_attribute8,
1279 x_attribute9 => x_attribute9,
1280 x_attribute10 => x_attribute10,
1281 x_attribute11 => x_attribute11,
1282 x_attribute12 => x_attribute12,
1283 x_attribute13 => x_attribute13,
1284 x_attribute14 => x_attribute14,
1285 x_attribute15 => x_attribute15,
1286 x_attribute16 => x_attribute16,
1287 x_attribute17 => x_attribute17,
1288 x_attribute18 => x_attribute18,
1289 x_attribute19 => x_attribute19,
1290 x_attribute20 => x_attribute20,
1291 x_creation_date => x_last_update_date,
1292 x_created_by => x_last_updated_by,
1293 x_last_update_date => x_last_update_date,
1294 x_last_updated_by => x_last_updated_by,
1295 x_last_update_login => x_last_update_login,
1296 x_gl_date => x_gl_date,
1297 x_reversal_gl_date => x_reversal_gl_date
1298 );
1299
1300 IF (x_mode = 'R') THEN
1301 x_request_id := fnd_global.conc_request_id;
1302 x_program_id := fnd_global.conc_program_id;
1303 x_program_application_id := fnd_global.prog_appl_id;
1304 IF (x_request_id = -1) THEN
1305 x_request_id := old_references.request_id;
1306 x_program_id := old_references.program_id;
1307 x_program_application_id := old_references.program_application_id;
1308 x_program_update_date := old_references.program_update_date;
1309 ELSE
1310 x_program_update_date := SYSDATE;
1311 END IF;
1312 END IF;
1313
1314 UPDATE igs_fi_refunds
1315 SET
1316 voucher_date = new_references.voucher_date,
1317 person_id = new_references.person_id,
1318 pay_person_id = new_references.pay_person_id,
1319 dr_gl_ccid = new_references.dr_gl_ccid,
1320 cr_gl_ccid = new_references.cr_gl_ccid,
1321 dr_account_cd = new_references.dr_account_cd,
1322 cr_account_cd = new_references.cr_account_cd,
1323 refund_amount = new_references.refund_amount,
1324 fee_type = new_references.fee_type,
1325 fee_cal_type = new_references.fee_cal_type,
1326 fee_ci_sequence_number = new_references.fee_ci_sequence_number,
1327 source_refund_id = new_references.source_refund_id,
1328 invoice_id = new_references.invoice_id,
1329 transfer_status = new_references.transfer_status,
1330 reversal_ind = new_references.reversal_ind,
1331 reason = new_references.reason,
1332 attribute_category = new_references.attribute_category,
1333 attribute1 = new_references.attribute1,
1334 attribute2 = new_references.attribute2,
1335 attribute3 = new_references.attribute3,
1336 attribute4 = new_references.attribute4,
1337 attribute5 = new_references.attribute5,
1338 attribute6 = new_references.attribute6,
1339 attribute7 = new_references.attribute7,
1340 attribute8 = new_references.attribute8,
1341 attribute9 = new_references.attribute9,
1342 attribute10 = new_references.attribute10,
1343 attribute11 = new_references.attribute11,
1344 attribute12 = new_references.attribute12,
1345 attribute13 = new_references.attribute13,
1346 attribute14 = new_references.attribute14,
1347 attribute15 = new_references.attribute15,
1348 attribute16 = new_references.attribute16,
1349 attribute17 = new_references.attribute17,
1350 attribute18 = new_references.attribute18,
1351 attribute19 = new_references.attribute19,
1352 attribute20 = new_references.attribute20,
1353 last_update_date = x_last_update_date,
1354 last_updated_by = x_last_updated_by,
1355 last_update_login = x_last_update_login ,
1356 request_id = x_request_id,
1357 program_id = x_program_id,
1358 program_application_id = x_program_application_id,
1359 program_update_date = x_program_update_date ,
1360 gl_date = new_references.gl_date,
1361 reversal_gl_date = new_references.reversal_gl_date
1362 WHERE rowid = x_rowid;
1363
1364 IF (SQL%NOTFOUND) THEN
1365 RAISE NO_DATA_FOUND;
1366 END IF;
1367
1368 END update_row;
1369
1370
1371 PROCEDURE add_row (
1372 x_rowid IN OUT NOCOPY VARCHAR2,
1373 x_refund_id IN OUT NOCOPY NUMBER,
1374 x_voucher_date IN DATE,
1375 x_person_id IN NUMBER,
1376 x_pay_person_id IN NUMBER,
1377 x_dr_gl_ccid IN NUMBER,
1378 x_cr_gl_ccid IN NUMBER,
1379 x_dr_account_cd IN VARCHAR2,
1380 x_cr_account_cd IN VARCHAR2,
1381 x_refund_amount IN NUMBER,
1382 x_fee_type IN VARCHAR2,
1383 x_fee_cal_type IN VARCHAR2,
1384 x_fee_ci_sequence_number IN NUMBER,
1385 x_source_refund_id IN NUMBER,
1386 x_invoice_id IN OUT NOCOPY NUMBER,
1387 x_transfer_status IN VARCHAR2,
1388 x_reversal_ind IN VARCHAR2,
1389 x_reason IN VARCHAR2,
1390 x_attribute_category IN VARCHAR2,
1391 x_attribute1 IN VARCHAR2,
1392 x_attribute2 IN VARCHAR2,
1393 x_attribute3 IN VARCHAR2,
1394 x_attribute4 IN VARCHAR2,
1395 x_attribute5 IN VARCHAR2,
1396 x_attribute6 IN VARCHAR2,
1397 x_attribute7 IN VARCHAR2,
1398 x_attribute8 IN VARCHAR2,
1399 x_attribute9 IN VARCHAR2,
1400 x_attribute10 IN VARCHAR2,
1401 x_attribute11 IN VARCHAR2,
1402 x_attribute12 IN VARCHAR2,
1403 x_attribute13 IN VARCHAR2,
1404 x_attribute14 IN VARCHAR2,
1405 x_attribute15 IN VARCHAR2,
1406 x_attribute16 IN VARCHAR2,
1407 x_attribute17 IN VARCHAR2,
1408 x_attribute18 IN VARCHAR2,
1409 x_attribute19 IN VARCHAR2,
1410 x_attribute20 IN VARCHAR2,
1411 x_mode IN VARCHAR2,
1412 x_gl_date IN DATE,
1413 x_reversal_gl_date IN DATE
1414 ) AS
1415 /*
1416 || Created By : vchappid
1417 || Created On : 26-FEB-2002
1418 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1419 || Known limitations, enhancements or remarks :
1420 || Change History :
1421 || Who When What
1422 || (reverse chronological order - newest change first)
1423 || skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns of the table IGS_FI_REFUNDS
1424 || smadathi 06-Nov-2002 Enh. Bug 2584986. Added new column GL_DATE,REVERSAL_GL_DATE
1425 || vvutukur 23-Sep-2002 Enh#2564643.Removed DEFAULT clause from parameters as gscc fix.
1426 */
1427 CURSOR c1 IS
1428 SELECT rowid
1429 FROM igs_fi_refunds
1430 WHERE refund_id = x_refund_id;
1431
1432 BEGIN
1433
1434 OPEN c1;
1435 FETCH c1 INTO x_rowid;
1436 IF (c1%NOTFOUND) THEN
1437 CLOSE c1;
1438
1439 insert_row (
1440 x_rowid,
1441 x_refund_id,
1442 x_voucher_date,
1443 x_person_id,
1444 x_pay_person_id,
1445 x_dr_gl_ccid,
1446 x_cr_gl_ccid,
1447 x_dr_account_cd,
1448 x_cr_account_cd,
1449 x_refund_amount,
1450 x_fee_type,
1451 x_fee_cal_type,
1452 x_fee_ci_sequence_number,
1453 x_source_refund_id,
1454 x_invoice_id,
1455 x_transfer_status,
1456 x_reversal_ind,
1457 x_reason,
1458 x_attribute_category,
1459 x_attribute1,
1460 x_attribute2,
1461 x_attribute3,
1462 x_attribute4,
1463 x_attribute5,
1464 x_attribute6,
1465 x_attribute7,
1466 x_attribute8,
1467 x_attribute9,
1468 x_attribute10,
1469 x_attribute11,
1470 x_attribute12,
1471 x_attribute13,
1472 x_attribute14,
1473 x_attribute15,
1474 x_attribute16,
1475 x_attribute17,
1476 x_attribute18,
1477 x_attribute19,
1478 x_attribute20,
1479 x_mode,
1480 x_gl_date,
1481 x_reversal_gl_date
1482 );
1483 RETURN;
1484 END IF;
1485 CLOSE c1;
1486
1487 update_row (
1488 x_rowid,
1489 x_refund_id,
1490 x_voucher_date,
1491 x_person_id,
1492 x_pay_person_id,
1493 x_dr_gl_ccid,
1494 x_cr_gl_ccid,
1495 x_dr_account_cd,
1496 x_cr_account_cd,
1497 x_refund_amount,
1498 x_fee_type,
1499 x_fee_cal_type,
1500 x_fee_ci_sequence_number,
1501 x_source_refund_id,
1502 x_invoice_id,
1503 x_transfer_status,
1504 x_reversal_ind,
1505 x_reason,
1506 x_attribute_category,
1507 x_attribute1,
1508 x_attribute2,
1509 x_attribute3,
1510 x_attribute4,
1511 x_attribute5,
1512 x_attribute6,
1513 x_attribute7,
1514 x_attribute8,
1515 x_attribute9,
1516 x_attribute10,
1517 x_attribute11,
1518 x_attribute12,
1519 x_attribute13,
1520 x_attribute14,
1521 x_attribute15,
1522 x_attribute16,
1523 x_attribute17,
1524 x_attribute18,
1525 x_attribute19,
1526 x_attribute20,
1527 x_mode ,
1528 x_gl_date,
1529 x_reversal_gl_date
1530 );
1531
1532 END add_row;
1533
1534
1535 PROCEDURE delete_row (
1536 x_rowid IN VARCHAR2
1537 ) AS
1538 /*
1539 || Created By : vchappid
1540 || Created On : 26-FEB-2002
1541 || Purpose : Handles the DELETE DML logic for the table.
1542 || Known limitations, enhancements or remarks :
1543 || Change History :
1544 || Who When What
1545 || (reverse chronological order - newest change first)
1546 */
1547 BEGIN
1548
1549 before_dml (
1550 p_action => 'DELETE',
1551 x_rowid => x_rowid
1552 );
1553
1554 DELETE FROM igs_fi_refunds
1555 WHERE rowid = x_rowid;
1556
1557 IF (SQL%NOTFOUND) THEN
1558 RAISE NO_DATA_FOUND;
1559 END IF;
1560
1561 END delete_row;
1562
1563 PROCEDURE beforerowinsert
1564 AS
1565 /*
1566 || Created By : vchappid
1567 || Created On : 26-FEB-2002
1568 || Purpose : Before a record is created in the igs_fi_refunds table, a charge record has to be created
1569 || call to charges api is made.
1570 || Known limitations, enhancements or remarks :
1571 || Change History :
1572 || Who When What
1573 || svuppala 04-AUG-2005 Enh 3392095 - Tution Waivers build
1574 || Impact of Charges API version Number change
1575 || Modified igs_fi_charges_api_pvt.create_charge - version 2.0 and x_waiver_amount
1576 || vvutukur 24-Dec-2002 Bug#2713315.Used FND_MESSAGE.SET_ENCODED to encode the token value, if charges api
1577 || returned some error message.
1578 || smadathi 06-Nov-2002 Enh. Bug 2584986. Modified charges API call to Add new parameter GL_DATE
1579 || Cursor CUR_CURRENCY declaration and its usage removed and replaced by
1580 || call to igs_fi_gen_gl.finp_get_cur
1581 || vvutukur 18-Sep-2002 Enh#2564643.Removed references to subaccount_id from cur_subaccount
1582 || cursor,p_header_rec record type parameter of call to charges api.
1583 || Renamed the cursor cur_subaccount to cur_ft_desc as this cursor
1584 || no longer refers to subaccount_id.Modified at its usage also.
1585 || agairola 06-Jun-2002 for bug 2392776 - added the call to the Updateinvoice procedure
1586 || agairola 14-May-2002 For bug 2365356, if the Invoice Id is null, then copy the new_references.invoiceid
1587 || agairola 14-May-2002 Following modifications were done for the bugs 2372163,2372122
1588 || 1. Modified the cursor cur_subaccount to fetch the Fee Type Description
1589 || 2. The Fee Type description is passed to Charges API
1590 || 3. The Effective Date is passed as the Voucher Date
1591 || (reverse chronological order - newest change first)
1592 */
1593 -- Variables added for the Charges API call
1594 l_header_rec igs_fi_charges_api_pvt.header_rec_type;
1595 l_line_tbl igs_fi_charges_api_pvt.line_tbl_type;
1596 l_line_id_tbl igs_fi_charges_api_pvt.line_id_tbl_type;
1597 l_invoice_id igs_fi_inv_int.invoice_id%TYPE;
1598 l_description igs_fi_fee_type.description%TYPE;
1599 l_return_status VARCHAR2(1);
1600 l_msg_count NUMBER(3);
1601 l_msg_data VARCHAR2(2000);
1602 l_msg VARCHAR2(2000);
1603 l_currency_cd igs_fi_control_all.currency_cd%TYPE;
1604 l_currency_desc fnd_currencies_tl.name%TYPE;
1605
1606 -- Cursor for Fetching the description of the Fee Type in context
1607 CURSOR cur_ft_desc(cp_fee_type igs_fi_fee_type.fee_type%TYPE) IS
1608 SELECT description
1609 FROM igs_fi_fee_type
1610 WHERE fee_type = cp_fee_type;
1611
1612 -- Getting the Charge Method and GL Code/ CCID's associated for a fee type
1613 CURSOR cur_chg_mthd(cp_fee_type igs_fi_f_typ_ca_inst.fee_type%TYPE,
1614 cp_fee_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
1615 cp_fee_ci_sequence_number igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE) IS
1616 SELECT s_chg_method_type,
1617 rec_gl_ccid,
1618 rec_account_cd
1619 FROM igs_fi_f_typ_ca_inst
1620 WHERE fee_type = cp_fee_type
1621 AND fee_cal_type = cp_fee_cal_type
1622 AND fee_ci_sequence_number = cp_fee_ci_sequence_number;
1623
1624 l_cur_chg_mthd cur_chg_mthd%ROWTYPE;
1625
1626 l_override_dr_rec_ccid igs_fi_f_typ_ca_inst.rec_gl_ccid%TYPE;
1627 l_override_dr_account_cd igs_fi_f_typ_ca_inst.rec_account_cd%TYPE;
1628 l_override_cr_rev_ccid igs_fi_f_typ_ca_inst.rec_gl_ccid%TYPE;
1629 l_override_cr_account_cd igs_fi_f_typ_ca_inst.rec_account_cd%TYPE;
1630
1631 l_rec_installed igs_fi_control_all.rec_installed%TYPE;
1632 l_message_name fnd_new_messages.message_name%TYPE;
1633
1634 l_n_waiver_amount NUMBER;
1635
1636 BEGIN
1637
1638 -- The procedure igs_fi_gen_gl.finp_get_cur returns Currency code and currency description
1639 -- If no set up has been done in the System Options form, the procedure returns error message
1640
1641 igs_fi_gen_gl.finp_get_cur( p_v_currency_cd => l_currency_cd ,
1642 p_v_curr_desc => l_currency_desc ,
1643 p_v_message_name => l_message_name
1644 );
1645
1646 IF l_currency_cd IS NULL AND l_message_name IS NOT NULL
1647 THEN
1648 fnd_message.set_name('IGS',l_message_name);
1649 igs_ge_msg_stack.add;
1650 app_exception.raise_exception;
1651 END IF;
1652
1653 -- Fetch the Fee Type description
1654 OPEN cur_ft_desc(new_references.fee_type);
1655 FETCH cur_ft_desc INTO l_description;
1656 CLOSE cur_ft_desc;
1657
1658 OPEN cur_chg_mthd(new_references.fee_type,new_references.fee_cal_type,new_references.fee_ci_sequence_number);
1659 FETCH cur_chg_mthd INTO l_cur_chg_mthd;
1660 CLOSE cur_chg_mthd;
1661
1662 -- Checking which Account Receivables is installed at the client side and insert into corresponding columns accordingly
1663 l_rec_installed := igs_fi_gen_005.finp_get_receivables_inst;
1664
1665 IF (NVL(l_rec_installed,'N') ='Y') THEN
1666 l_override_dr_rec_ccid := l_cur_chg_mthd.rec_gl_ccid;
1667 l_override_dr_account_cd := NULL;
1668 l_override_cr_rev_ccid := new_references.dr_gl_ccid;
1669 l_override_cr_account_cd := NULL;
1670 ELSE
1671 l_override_dr_rec_ccid := NULL;
1672 l_override_dr_account_cd := l_cur_chg_mthd.rec_account_cd;
1673 l_override_cr_rev_ccid := NULL;
1674 l_override_cr_account_cd := new_references.dr_account_cd;
1675 END IF;
1676
1677 -- assign the relevant parameters to the charges API Header Record
1678 l_header_rec.p_person_id := new_references.person_id;
1679 l_header_rec.p_fee_type := new_references.fee_type;
1680 l_header_rec.p_fee_cat := NULL;
1681 l_header_rec.p_fee_cal_type := new_references.fee_cal_type;
1682 l_header_rec.p_fee_ci_sequence_number := new_references.fee_ci_sequence_number;
1683 l_header_rec.p_course_cd := NULL;
1684 l_header_rec.p_attendance_type := NULL;
1685 l_header_rec.p_attendance_mode := NULL;
1686 l_header_rec.p_invoice_amount := new_references.refund_amount;
1687 l_header_rec.p_invoice_creation_date := new_references.voucher_date;
1688 l_header_rec.p_effective_date := new_references.voucher_date;
1689 l_header_rec.p_invoice_desc := l_description;
1690 l_header_rec.p_transaction_type := 'REFUND';
1691 l_header_rec.p_currency_cd := l_currency_cd;
1692 l_header_rec.p_exchange_rate := 1;
1693 l_header_rec.p_waiver_flag := NULL;
1694 l_header_rec.p_waiver_reason := NULL;
1695 l_header_rec.p_source_transaction_id := new_references.invoice_id;
1696
1697 -- assign the relevant parameters to the charges API Detail Record
1698 l_line_tbl(1).p_description := l_description;
1699 l_line_tbl(1).p_amount := new_references.refund_amount;
1700 l_line_tbl(1).p_chg_elements := 1;
1701 l_line_tbl(1).p_uoo_id := NULL;
1702 l_line_tbl(1).p_eftsu := NULL;
1703 l_line_tbl(1).p_credit_points := 1;
1704 l_line_tbl(1).p_org_unit_cd := NULL;
1705 l_line_tbl(1).p_attribute_category := NULL;
1706 l_line_tbl(1).p_override_dr_rec_ccid := l_override_dr_rec_ccid;
1707 l_line_tbl(1).p_override_cr_rev_ccid := l_override_cr_rev_ccid;
1708 l_line_tbl(1).p_override_dr_rec_account_cd := l_override_dr_account_cd;
1709 l_line_tbl(1).p_override_cr_rev_account_cd := l_override_cr_account_cd;
1710 l_line_tbl(1).p_attribute1 := NULL;
1711 l_line_tbl(1).p_attribute2 := NULL;
1712 l_line_tbl(1).p_attribute3 := NULL;
1713 l_line_tbl(1).p_attribute4 := NULL;
1714 l_line_tbl(1).p_attribute5 := NULL;
1715 l_line_tbl(1).p_attribute6 := NULL;
1716 l_line_tbl(1).p_attribute7 := NULL;
1717 l_line_tbl(1).p_attribute8 := NULL;
1718 l_line_tbl(1).p_attribute9 := NULL;
1719 l_line_tbl(1).p_attribute10 := NULL;
1720 l_line_tbl(1).p_attribute11 := NULL;
1721 l_line_tbl(1).p_attribute12 := NULL;
1722 l_line_tbl(1).p_attribute13 := NULL;
1723 l_line_tbl(1).p_attribute14 := NULL;
1724 l_line_tbl(1).p_attribute15 := NULL;
1725 l_line_tbl(1).p_attribute16 := NULL;
1726 l_line_tbl(1).p_attribute17 := NULL;
1727 l_line_tbl(1).p_attribute18 := NULL;
1728 l_line_tbl(1).p_attribute19 := NULL;
1729 l_line_tbl(1).p_attribute20 := NULL;
1730 l_line_tbl(1).p_d_gl_date := new_references.gl_date;
1731
1732 igs_fi_charges_api_pvt.create_charge(p_api_version => 2.0,
1733 p_init_msg_list => 'F',
1734 p_commit => 'F',
1735 p_header_rec => l_header_rec,
1736 p_line_tbl => l_line_tbl,
1737 x_line_id_tbl => l_line_id_tbl,
1738 x_invoice_id => l_invoice_id,
1739 x_return_status => l_return_status,
1740 x_msg_count => l_msg_count,
1741 x_msg_data => l_msg_data,
1742 x_waiver_amount => l_n_waiver_amount);
1743
1744 IF l_return_status <> 'S' THEN
1745 IF l_msg_count = 1 THEN
1746 FND_MESSAGE.SET_ENCODED(l_msg_data);
1747 l_msg := fnd_message.get;
1748 FND_MESSAGE.Set_Name('IGS',
1749 'IGS_FI_ERR_TXT');
1750 FND_MESSAGE.Set_Token('TEXT',
1751 l_msg);
1752 IGS_GE_MSG_STACK.Add;
1753 APP_EXCEPTION.RAISE_EXCEPTION;
1754 ELSE
1755 FOR l_count IN 1 .. l_msg_count LOOP
1756 l_msg := l_msg||FND_MSG_PUB.GET (p_msg_index => l_count, p_encoded => 'T');
1757 END LOOP;
1758 FND_MESSAGE.Set_Name('IGS',
1759 'IGS_FI_ERR_TXT');
1760 FND_MESSAGE.Set_Token('TEXT',
1761 l_msg);
1762 APP_EXCEPTION.RAISE_EXCEPTION;
1763 END IF;
1764 ELSE
1765 new_references.invoice_id := NVL(l_invoice_id,new_references.invoice_id);
1766
1767 -- Update the Invoice Record by calling the Update Invoice procedure
1768 update_invoice(new_references.invoice_id);
1769 END IF;
1770 END beforerowinsert;
1771 END igs_fi_refunds_pkg;