1 PACKAGE BODY igs_fi_prc_refunds AS
2 /* $Header: IGSFI65B.pls 120.6 2006/06/27 14:20:31 skharida noship $ */
3 /* **********************************************************************************************
4
5 Created By : Amit Gairola
6 Date Created By: 11-Mar-2002
7 Purpose : This package contains the procedures for processing the Refunds for the
8 Excess Unapplied Credits
9 Known limitations,enhancements,remarks:
10 Change History
11 Who When What
12 skharida 26-Jun-2006 Bug# 5208136 - Modified pocess_plus and process_batch procedure,
13 removed the obsoleted columns from the IGS_FI_REFUNDS_PKG
14 abshriva 9-JUN-2006 Bug 5076169 Modified procedure process_batch
15 sapanigr 03-May-2006 Enh#3924836 Precision Issue. Modified process_batch and process_plus.
16 sapanigr 24-Feb-2006 Bug 5018036 - Removed cur_pnum in process_batch for R12 repository performance tuning
17 sapanigr 14-Feb-2006 Bug 5018036. Cursors modified in procedure process_batch for R12 repository performance tuning
18 smadathi 25-AUG-2003 Enh. Bug 3045007. Modified process_batch
19 pathipat 24-Apr-2003 Enh 2831569 - Commercial Receivables build
20 Modified process_batch() and process_plus() - Added call to chk_manage_account()
21 smadathi 21-Feb-2002 Enh. Bug 2747329. Modified Process_batch and process_plus procedures.
22 Added public cursor c_refund_cr_glccid
23 shtatiko 28-JAN-2003 Bug# 2734316, modified process_plus procedure
24 smadathi 15-Jan-2003 Bug 2722096. Modified process_plus procedure.
25 agairola 23-Dec-02 Bug 2718029: Modified the code in Process_batch to remove the app_exception
26 .raise_exception for parameter validation and replaced it by setting retcode=2
27 and RETURN
28 vvutukur 19-Nov-2002 Enh#2584986.Modified the procedures process_plus,process_batch.
29 shtatiko 24-Sep-2002 Bug# 2564643, Removed references and instances of Sub Account Id.
30 vvutukur 28-Jun-2002 Modified process_batch.Modified IGS_FI_TOL_LIM_DEFIED message token from REF_AMNT to
31 REF_AMOUNT.bug#2427999.
32 vchappid 13-Jun-02 Bug#2411529, Incorrectly used message name has been modified
33 agairola 11-Jun-02 Bug No: 2408221 modified Process_Plus
34 agairola 15-May-02 Modified the process_batch procedure for bug 2373855
35 rnirwani 13-Sep-2004 changed cursor cur_inst to not consider logically deleted records Bug# 3885804
36 ********************************************************************************************** */
37
38 g_ind_yes CONSTANT VARCHAR2(1) := 'Y';
39 g_ind_no CONSTANT VARCHAR2(1) := 'N';
40 g_cleared CONSTANT VARCHAR2(30) := 'CLEARED';
41 g_borrower CONSTANT VARCHAR2(30) := 'BORROWER';
42 g_student CONSTANT VARCHAR2(30) := 'STUDENT';
43 g_on_acc CONSTANT VARCHAR2(30) := 'ON_ACCOUNT';
44 g_null CONSTANT VARCHAR2(10) := NULL;
45 g_todo CONSTANT VARCHAR2(30) := 'TODO';
46 g_app CONSTANT VARCHAR2(30) := 'APP';
47 g_sponsor CONSTANT VARCHAR2(30) := 'SPONSOR';
48 g_intermit CONSTANT VARCHAR2(30) := 'INTERMIT';
49 g_dlp CONSTANT VARCHAR2(10) := 'DLP';
50 g_flp CONSTANT VARCHAR2(10) := 'FLP';
51 g_msg_lkp CONSTANT VARCHAR2(30) := 'IGS_FI_LOCKBOX';
52 g_check_add_drop CONSTANT VARCHAR2(30) := 'CHECK_ADD_DROP';
53 g_yes_no CONSTANT VARCHAR2(30) := 'YES_NO';
54 g_gl_date CONSTANT VARCHAR2(30) := 'GL_DATE';
55
56 g_amnt_high igs_fi_refund_setup.amount_high%TYPE;
57 g_amnt_low igs_fi_refund_setup.amount_low%TYPE;
58 e_resource_busy EXCEPTION;
59 PRAGMA EXCEPTION_INIT(e_resource_busy,-0054);
60
61 -- PL/SQL record for the Credit Information
62 TYPE crdrec IS RECORD(credit_id IGS_FI_CREDITS.Credit_Id%TYPE,
63 credit_class IGS_FI_CR_TYPES.Credit_Class%TYPE,
64 unapplied_amount IGS_FI_CREDITS.Unapplied_Amount%TYPE,
65 fee_type IGS_FI_FEE_TYPE.Fee_Type%TYPE,
66 fee_cal_type IGS_CA_INST.Cal_Type%TYPE,
67 fee_ci_sequence_number IGS_CA_INST.Sequence_Number%TYPE,
68 credit_number IGS_FI_CREDITS.Credit_Number%TYPE);
69
70 -- PL/SQL table for storing the credit records which are to be processed for
71 -- refunds
72 TYPE crdtab IS TABLE OF crdrec
73 INDEX BY BINARY_INTEGER;
74
75 refund_calc crdtab;
76
77 l_rfnd_cntr NUMBER(15);
78
79 -- PL/SQL record for the distinct Sub Account and FTCI combination
80 TYPE rfndfee IS RECORD(fee_type IGS_FI_FEE_TYPE.Fee_Type%TYPE,
81 fee_cal_type IGS_CA_INST.Cal_Type%TYPE,
82 fee_ci_sequence_number IGS_CA_INST.Sequence_Number%TYPE);
83
84 -- PL/SQL table for storing the distinct fee periods to be refunded
85 TYPE rfndtab IS TABLE OF rfndfee
86 INDEX BY BINARY_INTEGER;
87
88 refund_fee_prd rfndtab;
89 l_trm_cntr NUMBER(15);
90
91 CURSOR c_refund_cr_glccid IS
92 SELECT refund_cr_gl_ccid
93 FROM igs_fi_control;
94
95
96 FUNCTION get_meaning(p_lookup_type IN VARCHAR2,
97 p_lookup_code IN VARCHAR2) RETURN VARCHAR2 AS
98
99 /***********************************************************************************************
100
101 Created By : Amit Gairola
102 Date Created By: 11-Mar-2002
103 Purpose : This function will fetch the meaning for the lookup type and code
104 Known limitations,enhancements,remarks:
105 Change History
106 Who When What
107
108 ********************************************************************************************** */
109
110 -- Select the meaning from the Lookups table for the Lookup Type and the Lookup Code
111 --
112 CURSOR cur_lkp(cp_lookup_type VARCHAR2,
113 cp_lookup_code VARCHAR2) IS
114 SELECT meaning
115 FROM igs_lookups_view
116 WHERE lookup_type = cp_lookup_type
117 AND lookup_code = cp_lookup_code;
118
119 l_meaning igs_lookups_view.meaning%TYPE;
120 BEGIN
121 -- Fetch the meaning from the Lookups table
122 OPEN cur_lkp(p_lookup_type,
123 p_lookup_code);
124 FETCH cur_lkp INTO l_meaning;
125 CLOSE cur_lkp;
126
127 RETURN l_meaning;
128 END get_meaning;
129
130 PROCEDURE log_message(p_lookup_type IN VARCHAR2,
131 p_lookup_code IN VARCHAR2,
132 p_value IN VARCHAR2,
133 p_level IN NUMBER) AS
134
135 /***********************************************************************************************
136
137 Created By : Amit Gairola
138 Date Created By: 11-Mar-2002
139 Purpose : This procedure will log the message in the log file of the concurrent manager
140 Known limitations,enhancements,remarks:
141 Change History
142 Who When What
143
144 ********************************************************************************************** */
145
146 l_meaning igs_lookups_view.meaning%TYPE;
147 l_message varchar2(2000);
148 BEGIN
149
150 l_meaning := get_meaning(p_lookup_type,
151 p_lookup_code);
152
153 -- Based on the value of the level passed as input to the procedure
154 -- prepare the Log file line
155 l_message := lpad(l_meaning,length(l_meaning)+p_level*3,' ')||':'||p_value;
156
157 -- Update the concurrent manager's log file
158 fnd_file.put_line(fnd_file.log,
159 l_message);
160 END log_message;
161
162 FUNCTION val_rfnd_lim(p_refund_amnt igs_fi_refunds.refund_amount%TYPE) RETURN BOOLEAN AS
163 /***********************************************************************************************
164
165 Created By : Amit Gairola
166 Date Created By: 11-Mar-2002
167 Purpose : This function will validate if the Refund Amount is greater or lesser than the
168 tolerance limits
169 Known limitations,enhancements,remarks:
170 Change History
171 Who When What
172
173 ********************************************************************************************** */
174
175 -- Select the High and the Low Limits from the refund setup table
176 CURSOR cur_rfnd_limits IS
177 SELECT amount_high,
178 amount_low
179 FROM igs_fi_refund_setup
180 WHERE TRUNC(sysdate) BETWEEN TRUNC(START_DATE) AND TRUNC(NVL(END_DATE,sysdate));
181
182 l_return BOOLEAN;
183 BEGIN
184 g_amnt_high := NULL;
185 g_amnt_low := NULL;
186
187 -- Fetch the Upper and the Lower limit from the Refunds Setup table into the global
188 -- variables for the Upper and Lower limits
189 OPEN cur_rfnd_limits;
190 FETCH cur_rfnd_limits INTO g_amnt_high,
191 g_amnt_low;
192 CLOSE cur_rfnd_limits;
193
194 l_return := TRUE;
195
196 -- If the upper limit fetched by the cursor is not null and the refund amount is
197 -- higher than the Upper limit, the function should return false
198 IF ((g_amnt_high IS NOT NULL) AND (p_refund_amnt > g_amnt_high)) THEN
199 l_return := FALSE;
200 END IF;
201
202 -- If the lowe limit fetched by the cursor is not null and the refund amount is
203 -- less than the lower limit, the function should return false
204 IF ((g_amnt_low IS NOT NULL) AND (p_refund_amnt < g_amnt_low)) THEN
205 l_return := FALSE;
206 END IF;
207
208 RETURN l_return;
209 END val_rfnd_lim;
210
211 PROCEDURE process_plus(p_credit_id IN NUMBER,
212 p_borrower_id IN NUMBER,
213 p_err_message OUT NOCOPY VARCHAR2,
214 p_status OUT NOCOPY BOOLEAN,
215 p_d_gl_date IN DATE
216 ) AS
217
218 /***********************************************************************************************
219
220 Created By : Amit Gairola
221 Date Created By: 11-Mar-2002
222 Purpose : This procedure is to be called from the Financial Aid Integration process.
223 This procedure processes the refunds for the PLUS loan.
224 Known limitations,enhancements,remarks:
225 Change History
226 Who When What
227 skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns from the table IGS_FI_REFUNDS
228 sapanigr 03-May-2006 Enh#3924836 Precision Issue. Amount values being inserted into igs_fi_refunds
229 is now rounded off to currency precision
230 pathipat 24-Apr-2003 Enh 2831569 - Commercial Receivables build
231 Added check for manage_accounts - call to chk_manage_account()
232 smadathi 21-Feb-2002 Enh. Bug 2747329. Added validation for refund_cr_gl_ccid
233 shtatiko 28-JAN-2003 Bug# 2734316, changed message name to IGS_FI_PARAMETER_NULL from IGS_FI_INVALID_PARM
234 smadathi 15-Jan-2003 Bug 2722096. Incorporated logging of error message raised by the mass_apply
235 process call.
236 vvutukur 19-Nov-2002 Enh#2584986.Code added to validate the mandatory newly added IN parameter p_d_gl_date.
237 Passed this p_d_gl_date parameter to the calls to i)igs_fi_prc_appl.mass_apply,
238 ii)igs_fi_refunds_pkg.insert_row iii)igs_fi_gen_007.create_application.
239 shtatiko 24-Sep-2002 Bug No: 2564643 Removed references to Subaccount_id.
240 agairola 13-Jun-2002 Bug No: 2408221 For the Application Hierarchy, added the check for effective
241 start and end date
242 agairola 11-Jun-2002 Bug No: 2408221 added the validation for Application Hierarchy
243
244 ********************************************************************************************** */
245
246 -- Cursor for validating the credits
247 CURSOR cur_val_crd(cp_credit_id IN NUMBER,
248 cp_status IN VARCHAR2) IS
249 SELECT crd.party_id,
250 crd.credit_type_id,
251 crd.effective_date
252 FROM igs_fi_credits crd,
253 igs_fi_cr_types crt
254 WHERE crd.credit_id = cp_credit_id
255 AND crd.credit_type_id = crt.credit_type_id
256 AND crd.status = cp_status
257 AND NVL(crt.refund_allowed,g_ind_no) = g_ind_yes;
258
259 -- Cursor for selecting the credit information from the Credits table based on whether the refund is
260 -- allowed for the Credit Id passed as input to the procedure
261 CURSOR cur_crd(cp_credit_id IN NUMBER,
262 cp_status IN VARCHAR2) IS
263 SELECT CRD.PARTY_ID,
264 CRD.UNAPPLIED_AMOUNT,
265 CRD.FEE_CAL_TYPE,
266 CRD.FEE_CI_SEQUENCE_NUMBER
267 FROM igs_fi_credits crd
268 WHERE crd.credit_id = p_credit_id
269 AND crd.status = cp_status
270 FOR UPDATE NOWAIT;
271
272 -- Cursor for fetching the Application Hierarchy for the Credit Type
273 CURSOR cur_appl_hier(cp_credit_type_id igs_fi_cr_types.credit_type_id%TYPE,
274 cp_effective_date igs_fi_credits.effective_date%TYPE) IS
275 SELECT 'x'
276 FROM igs_fi_a_hierarchies
277 WHERE credit_type_id = cp_credit_type_id
278 AND cp_effective_date BETWEEN effective_start_date AND NVL(effective_end_date,cp_effective_date);
279
280 -- Variables of data type as the column of a table
281 l_fee_type igs_fi_fee_type.fee_type%TYPE;
282 l_fee_cal_type igs_ca_inst.cal_type%TYPE;
283 l_fee_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
284 l_dr_gl_ccid igs_fi_refunds.dr_gl_ccid%TYPE;
285 l_cr_gl_ccid igs_fi_refunds.cr_gl_ccid%TYPE;
286 l_dr_account_cd igs_fi_refunds.dr_account_cd%TYPE;
287 l_cr_account_cd igs_fi_refunds.cr_account_cd%TYPE;
288 l_rowid igs_fi_refunds_v.row_id%TYPE;
289 l_refund_id igs_fi_refunds.refund_id%TYPE;
290 l_invoice_id igs_fi_inv_int.invoice_id%TYPE;
291 l_dr_ccid igs_fi_refunds.dr_gl_ccid%TYPE;
292 l_cr_ccid igs_fi_refunds.cr_gl_ccid%TYPE;
293 l_dr_acc_cd igs_fi_refunds.dr_account_cd%TYPE;
294 l_cr_acc_cd igs_fi_refunds.cr_account_cd%TYPE;
295 l_inv_amt_due igs_fi_inv_int.invoice_amount_due%TYPE;
296 l_unapp_amount igs_fi_credits.unapplied_amount%TYPE;
297 l_application_id igs_fi_applications.application_id%TYPE;
298 l_payee_id igs_fi_parties_v.person_id%TYPE;
299 l_reason fnd_new_messages.message_text%TYPE;
300 l_party_id igs_fi_parties_v.person_id%TYPE;
301 l_determination igs_lookups_view.lookup_code%TYPE;
302 l_credit_type_id igs_fi_cr_types.credit_type_id%TYPE;
303 l_effective_date igs_fi_credits.effective_date%TYPE;
304
305 -- Varchar2 variables
306 l_err_msg VARCHAR2(2000);
307 l_var VARCHAR2(1);
308
309 -- Boolean variables
310 l_status BOOLEAN;
311 l_exception BOOLEAN;
312
313 -- Variable of Cursor Rowtype
314 l_crd_rec cur_crd%ROWTYPE;
315 l_v_message_name fnd_new_messages.message_name%TYPE;
316 l_v_closing_status gl_period_statuses.closing_status%TYPE;
317
318 --- Cursor variable for publec cursor c_refund_cr_glccid
319 rec_c_refund_cr_glccid c_refund_cr_glccid%ROWTYPE;
320
321 l_v_manage_acc igs_fi_control_all.manage_accounts%TYPE := NULL;
322
323 BEGIN
324
325 -- Obtain the value of manage_accounts in the System Options form
326 -- If it is null or 'OTHER', then this process is not available, so error out.
327 igs_fi_com_rec_interface.chk_manage_account( p_v_manage_acc => l_v_manage_acc,
328 p_v_message_name => l_v_message_name
329 );
330 IF (l_v_manage_acc = 'OTHER') OR (l_v_manage_acc IS NULL) THEN
331 p_status := FALSE;
332 p_err_message := l_v_message_name;
333 RETURN;
334 END IF;
335
336 SAVEPOINT RFND_PRC_PLUS;
337
338 OPEN c_refund_cr_glccid;
339 FETCH c_refund_cr_glccid INTO rec_c_refund_cr_glccid;
340 CLOSE c_refund_cr_glccid;
341
342 -- if refund_cr_gl_ccid is set in System Options form, check if refund_cr_gl_ccid passed is of type Liability.
343 IF rec_c_refund_cr_glccid.refund_cr_gl_ccid IS NOT NULL THEN
344 -- if the function to check validity of Liability Account returns false
345 -- return the status to flase and set the error message
346 IF NOT (igs_fi_gen_apint.chk_liability_acc(p_n_ccid => rec_c_refund_cr_glccid.refund_cr_gl_ccid)) THEN
347 p_status := FALSE;
348 p_err_message := 'IGS_FI_INV_ACC_LIABL';
349 RETURN;
350 END IF;
351 END IF;
352
353 -- If the Credit Id is NULL or GL Date is NULL then this is an error condition
354 -- and the procedure should return an error message. As these are manadatory
355 -- parameters.
356
357 IF p_credit_id IS NULL OR p_d_gl_date IS NULL THEN
358 p_status := FALSE;
359 -- Changed the message name to IGS_FI_PARAMTER_NULL from IGS_FI_INVALID_PARM
360 p_err_message := 'IGS_FI_PARAMETER_NULL';
361 RETURN;
362 END IF;
363
364 igs_fi_gen_gl.get_period_status_for_date(p_d_date => p_d_gl_date,
365 p_v_closing_status => l_v_closing_status,
366 p_v_message_name => l_v_message_name
367 );
368 IF l_v_message_name IS NOT NULL THEN
369 p_status := FALSE;
370 p_err_message := l_v_message_name;
371 RETURN;
372 END IF;
373
374 IF l_v_closing_status NOT IN ('O','F') THEN
375 p_status := FALSE;
376 p_err_message := 'IGS_FI_INVALID_GL_DATE';
377 RETURN;
378 END IF;
379
380 OPEN cur_val_crd(p_credit_id,
381 g_cleared);
382 FETCH cur_val_crd INTO l_party_id,
383 l_credit_type_id,
384 l_effective_date;
385 IF cur_val_crd%NOTFOUND THEN
386 p_status := FALSE;
387 p_err_message := 'IGS_FI_RFND_NOT_ALWD';
388 CLOSE cur_val_crd;
389 RETURN;
390 END IF;
391 CLOSE cur_val_crd;
392
393
394 -- Call the procedure for getting the Borrower Determination
395 IGS_FI_GEN_REFUNDS.Get_Borw_Det(p_credit_id => p_credit_id,
396 p_determination => l_determination,
397 p_err_message => l_err_msg,
398 p_status => l_status);
399 -- If the procedure for Borrower Determination returns false, then
400 -- the Plus loans process should exit
401 IF NOT l_status THEN
402 p_err_message := l_err_msg;
403 p_status := l_status;
404 RETURN;
405 END IF;
406
407
408 -- If the determination is Borrower, then the Payee should be the Borrower Id
409 -- passed as input to the procedure
410 IF l_determination = g_borrower THEN
411 l_payee_id := p_borrower_id;
412
413 -- Else if the determination is Student, then the Payee should be the Party Id
414 -- of the credit record
415 ELSIF l_determination = g_student THEN
416 l_payee_id := l_party_id;
417 ELSIF l_determination = g_on_acc THEN
418
419 -- Elsif the Determination is On Account, then the procedure should return without processing
420 -- any refunds. The borrower has determined that the excess credit should be left on the student
421 -- account
422 p_err_message := 'IGS_FI_RFND_ON_ACC';
423 p_status := TRUE;
424 RETURN;
425 END IF;
426
427 -- If there are no application hierarchies defined for the Credit Type
428 -- then no refunds should be allowed
429 OPEN cur_appl_hier(l_credit_type_id,
430 l_effective_date);
431 FETCH cur_appl_hier INTO l_var;
432 IF cur_appl_hier%NOTFOUND THEN
433 CLOSE cur_appl_hier;
434 p_status := FALSE;
435 p_err_message := 'IGS_FI_NO_APPL_HIER_REFUND';
436 RETURN;
437 END IF;
438 CLOSE cur_appl_hier;
439
440 BEGIN
441
442 -- Call the procedure for the Mass Application of all the credits and the charges for the
443 -- person
444 -- Added logging of the error message raised by the procedure Mass Apply
445
446 fnd_message.set_name('IGS','IGS_FI_RFND_MASS_APP_START');
447 fnd_file.put_line(fnd_file.log,fnd_message.get);
448 fnd_file.new_line(fnd_file.log,1);
449
450 igs_fi_prc_appl.mass_apply(p_person_id => l_party_id,
451 p_person_id_grp => g_null,
452 p_credit_number => g_null,
453 p_credit_type_id => g_null,
454 p_credit_date_low => g_null,
455 p_credit_date_high => g_null,
456 p_d_gl_date => p_d_gl_date);
457 fnd_file.new_line(fnd_file.log,1);
458 fnd_message.set_name('IGS','IGS_FI_RFND_MASS_APP_END');
459 fnd_file.put_line(fnd_file.log,fnd_message.get);
460 fnd_file.new_line(fnd_file.log,1);
461 EXCEPTION
462 WHEN OTHERS THEN
463 ROLLBACK TO RFND_PRC_PLUS;
464 fnd_file.put_line(fnd_file.log,fnd_message.get);
465 l_exception := TRUE;
466 END;
467
468 -- If there is an error, then the procedure should exit with the status as FALSE
469 IF l_exception THEN
470 p_err_message := 'IGS_FI_APPL_ERR';
471 p_status := FALSE;
472 RETURN;
473 END IF;
474
475
476 -- Select the credit information from the credits table
477 OPEN cur_crd(p_credit_id,
478 g_cleared);
479 FETCH cur_crd INTO l_crd_rec;
480
481 -- If the cursor does not return any records then this is an error condition
482 -- and the procedure should exit with an error message
483 IF cur_crd%NOTFOUND THEN
484 p_status := FALSE;
485 p_err_message := 'IGS_FI_RFND_NOT_ALWD';
486 CLOSE cur_crd;
487 RETURN;
488 END IF;
489 CLOSE cur_crd;
490
491
492 -- If the Unapplied Amount of the credit record is less than 0
493 IF l_crd_rec.unapplied_amount <= 0 THEN
494 p_status := TRUE;
495 RETURN;
496 END IF;
497
498 l_fee_cal_type := l_crd_rec.fee_cal_type;
499 l_fee_ci_sequence_number := l_crd_rec.fee_ci_sequence_number;
500
501 -- Call the procedure for derivation of the FTCI for the Credit Record
502 igs_fi_gen_refunds.get_fee_prd(p_fee_type => l_fee_type,
503 p_fee_cal_type => l_fee_cal_type,
504 p_fee_ci_sequence_number => l_fee_ci_sequence_number,
505 p_status => l_status);
506
507 -- If the status returned by the Get Fee Period is False, then the procedure should exit
508 IF NOT l_status THEN
509 p_status := l_status;
510 p_err_message := 'IGS_FI_REFUND_FTCI';
511 RETURN;
512 END IF;
513
514 -- Call the procedure for determining the Refund Accounts
515 igs_fi_gen_refunds.get_refund_acc(p_dr_gl_ccid => l_dr_gl_ccid,
516 p_cr_gl_ccid => l_cr_gl_ccid,
517 p_dr_account_cd => l_dr_account_cd,
518 p_cr_account_cd => l_cr_account_cd,
519 p_err_message => l_err_msg,
520 p_status => l_status);
521
522 -- If the status returned by the Get Refund Account procedure is False, then the
523 -- procedure should exit
524 IF NOT l_status THEN
525 p_status := l_status;
526 p_err_message := l_err_msg;
527 RETURN;
528 END IF;
529
530 -- Get the reason for the creation of refund transaction from the message
531 fnd_message.set_name('IGS',
532 'IGS_FI_REFUND_REASON_PLUS');
533 l_reason := fnd_message.get;
534
535 SAVEPOINT REFUNDS;
536
537 l_rowid := NULL;
538 l_refund_id := NULL;
539 l_invoice_id := NULL;
540
541 -- Call the Refunds table TBH for creating a record in the
542 -- refunds table
543 -- Call to igs_fi_gen_gl.get_formatted_amount formats refund_amount by rounding off to currency precision
544 igs_fi_refunds_pkg.insert_row(X_ROWID => l_rowid,
545 X_REFUND_ID => l_refund_id,
546 X_VOUCHER_DATE => TRUNC(sysdate),
547 X_PERSON_ID => l_crd_rec.party_id,
548 X_PAY_PERSON_ID => l_payee_id,
549 X_DR_GL_CCID => l_dr_gl_ccid,
550 X_CR_GL_CCID => l_cr_gl_ccid,
551 X_DR_ACCOUNT_CD => l_dr_account_cd,
552 X_CR_ACCOUNT_CD => l_cr_account_cd,
553 X_REFUND_AMOUNT => igs_fi_gen_gl.get_formatted_amount(l_crd_rec.unapplied_amount),
554 X_FEE_TYPE => l_fee_type,
555 X_FEE_CAL_TYPE => l_fee_cal_type,
556 X_FEE_CI_SEQUENCE_NUMBER => l_fee_ci_sequence_number,
557 X_SOURCE_REFUND_ID => g_null,
558 X_INVOICE_ID => l_invoice_id,
559 X_TRANSFER_STATUS => g_todo,
560 X_REVERSAL_IND => g_ind_no,
561 X_REASON => l_reason,
562 X_ATTRIBUTE_CATEGORY => g_null,
563 X_ATTRIBUTE1 => g_null,
564 X_ATTRIBUTE2 => g_null,
565 X_ATTRIBUTE3 => g_null,
566 X_ATTRIBUTE4 => g_null,
567 X_ATTRIBUTE5 => g_null,
568 X_ATTRIBUTE6 => g_null,
569 X_ATTRIBUTE7 => g_null,
570 X_ATTRIBUTE8 => g_null,
571 X_ATTRIBUTE9 => g_null,
572 X_ATTRIBUTE10 => g_null,
573 X_ATTRIBUTE11 => g_null,
574 X_ATTRIBUTE12 => g_null,
575 X_ATTRIBUTE13 => g_null,
576 X_ATTRIBUTE14 => g_null,
577 X_ATTRIBUTE15 => g_null,
578 X_ATTRIBUTE16 => g_null,
579 X_ATTRIBUTE17 => g_null,
580 X_ATTRIBUTE18 => g_null,
581 X_ATTRIBUTE19 => g_null,
582 X_ATTRIBUTE20 => g_null,
583 X_GL_DATE => p_d_gl_date,
584 X_REVERSAL_GL_DATE => NULL);
585
586 -- Call the Applications procedure for applying the Refund Charge to the Credit being refunded
587 l_application_id := NULL;
588 igs_fi_gen_007.create_application(p_credit_id => p_credit_id,
589 p_invoice_id => l_invoice_id,
590 p_amount_apply => l_crd_rec.unapplied_amount,
591 p_appl_type => g_app,
592 p_appl_hierarchy_id => g_null,
593 p_application_id => l_application_id,
594 p_validation => g_ind_yes,
595 p_dr_gl_ccid => l_dr_ccid,
596 p_cr_gl_ccid => l_cr_ccid,
597 p_dr_account_cd => l_dr_acc_cd,
598 p_cr_account_cd => l_cr_acc_cd,
599 p_unapp_amount => l_unapp_amount,
600 p_inv_amt_due => l_inv_amt_due,
601 p_err_msg => l_err_msg,
602 p_status => l_status,
603 p_d_gl_date => p_d_gl_date);
604
605 -- If the Applications procedure gives an error message, then the procedure should exit
606 IF NOT l_status THEN
607 p_err_message := l_err_msg;
608 p_status := l_status;
609 ROLLBACK TO REFUNDS;
610 RETURN;
611 END IF;
612
613 -- If the Invoice Amount Due or the Unapplied Amount of the Credit Transaction as returned by
614 -- the Applications API is more than 0, then this is an error and the procedure should exit
615 IF ((l_inv_amt_due > 0) OR (l_unapp_amount > 0)) THEN
616 p_err_message := 'IGS_FI_REFUND_APPL_ERR';
617 p_status := FALSE;
618 ROLLBACK TO REFUNDS;
619 RETURN;
620 END IF;
621
622 p_err_message := NULL;
623 p_status := TRUE;
624 EXCEPTION
625 WHEN e_resource_busy THEN
626 p_err_message := 'IGS_FI_RFND_REC_LOCK';
627 p_status := FALSE;
628 ROLLBACK TO RFND_PRC_PLUS;
629 END process_plus;
630
631 PROCEDURE process_batch(errbuf OUT NOCOPY VARCHAR2,
632 retcode OUT NOCOPY NUMBER,
633 p_person_id IN NUMBER,
634 p_person_id_grp IN NUMBER,
635 p_add_drop IN VARCHAR2,
636 p_test_run IN VARCHAR2,
637 p_d_gl_date IN VARCHAR2) AS
638 /***********************************************************************************************
639
640 Created By : Amit Gairola
641 Date Created By: 10-Mar-2002
642 Purpose : This procedure is used for batch processing.
643 Known limitations,enhancements,remarks:
644 Change History
645 Who When What
646 skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns from the table IGS_FI_REFUNDS
647 abshriva 9-Jun-2006 Bug:5076169 Modified code to log message in log file in case student is not having Refund Allowed checked Credits.
648 sapanigr 03-May-2006 Enh#3924836 Precision Issue. Amount values being inserted into igs_fi_refunds
649 is now rounded off to currency precision
650 pmarada 19-jul-04 Bug 3775620, Logging credit number in log file, if there is no fee period for the refund fee type.
651 smadathi 25-AUG-2003 Enh. Bug 3045007. Added business logic to check if the Person for which the Refunds is being processed
652 is on an Active Payment Plan
653 pathipat 24-Apr-2003 Enh 2831569 - Commercial Receivables build
654 Added check for manage_accounts - call to chk_manage_account()
655 smadathi 21-Feb-2002 Bug 2747329. Cursor select cur_pers was modified to replace table igs_pe_persid_group reference to
656 igs_pe_all_persid_group_v. Added validation for refund_cr_gl_ccid.
657 agairola 23-Dec-02 Bug 2718029: Modified the code in Process_batch to remove the app_exception
658 .raise_exception for parameter validation and replaced it by setting retcode=2
659 and RETURN
660 vvutukur 19-Nov-2002 Enh#2584986.Added mandatory IN parameter p_d_gl_date.Code added to validate GL Date.
661 Modified the calls to igs_fi_refunds_pkg.insert_row and igs_fi_gen_007.create_application to include new parameter p_d_gl_date also. Modified IGS_GE_UNHANDLED_EXCEP message to
662 IGS_GE_UNHANDLED_EXCEPTION.
663 vvutukur 28-Jun-2002 modified IGS_FI_TOL_LIM_DEFIED message token from REF_AMNT to
664 REF_AMOUNT.bug#2427999.
665 sarakshi 14-Jun-02 bug#2400617,the check of refunds not be processed if student is studying
666 at another instution or the student is inactive should be there irrespective
667 of the financial aid student or not.
668 vchappid 13-Jun-02 Bug#2411529, Incorrectly used message name has been modified
669 agairola 15-May-02 For the bug 2373855, following modifications were done
670 1. Removed the CLOSE cur_spnsr statement from inside IF Cur_spnsr%FOUND
671 as it was already happening outside the %FOUND statements
672 2. Added a new variable l_hld_exsts as the process was displaying that
673 Holds exist even though there were no holds because the variable
674 l_process_party was set to FALSE. Hence added the additional check.
675
676 ********************************************************************************************** */
677
678 -- Cursor for validating the person id
679 CURSOR cur_val_party(cp_party_id IN igs_fi_parties_v.person_id%TYPE) IS
680 SELECT person_number
681 FROM igs_fi_parties_v pe
682 WHERE pe.person_id = cp_party_id;
683
684 -- Cursor for validating the Person Id Group
685 CURSOR cur_pers(cp_person_id_grp igs_pe_persid_group.group_id%TYPE) IS
686 SELECT group_cd
687 FROM igs_pe_all_persid_group_v
688 WHERE group_id=cp_person_id_grp
689 AND closed_ind <> g_ind_yes;
690
691 -- Cursor for getting the Person Ids for which the Refunds have to be
692 -- processed
693 -- Bug 5018036 - Cursor cur_party broken into cur_party and cur_party_all.
694 CURSOR cur_party(cp_person_id igs_fi_parties_v.person_id%TYPE) IS
695 SELECT party_id person_id
696 FROM hz_parties
697 WHERE party_id = cp_person_id;
698
699 -- Cursor for getting the Person Ids for which the Refunds have to be
700 -- processed if no person id given
701 CURSOR cur_party_all IS
702 SELECT DISTINCT crd.party_id person_id
703 FROM igs_fi_credits crd,
704 igs_fi_cr_types crt
705 WHERE crd.credit_type_id = crt.credit_type_id
706 AND crd.status = g_cleared
707 AND NVL(crt.refund_allowed,g_ind_no) = g_ind_yes
708 AND crd.unapplied_amount > 0;
709
710 -- Cursor for validating whether the Person being processed is not a sponsor
711 CURSOR cur_spnsr(cp_person_id igs_fi_parties_v.person_id%TYPE) IS
712 SELECT 'x'
713 FROM igf_aw_fund_mast fund,
714 igf_aw_fund_cat fcat
715 WHERE fund.fund_code = fcat.fund_code
716 AND fcat.sys_fund_type = g_sponsor
717 AND fund.party_id = cp_person_id;
718
719 -- Cursor for getting the credit records for the Person Id
720 CURSOR cur_crd(cp_person_id igs_fi_parties_v.person_id%TYPE,
721 cp_status igs_fi_credits.status%TYPE
722 ) IS
723 SELECT crd.*, crt.credit_class
724 FROM igs_fi_credits crd,
725 igs_fi_cr_types crt,
726 igs_fi_cr_activities cra
727 WHERE crd.credit_type_id = crt.credit_type_id
728 AND crd.status = cp_status
729 AND crd.party_id = cp_person_id
730 AND NVL(crt.refund_allowed,g_ind_no) = g_ind_yes
731 AND crt.credit_class <> 'SPNSP'
732 AND crd.unapplied_amount > 0
733 AND cra.credit_id = crd.credit_id;
734
735 -- Cursor for validating if any program attempt of the person is in INTERMIT state and the
736 -- intermission type has the study at another institution flag set
737 CURSOR cur_inst(cp_person_id igs_fi_parties_v.person_id%TYPE,
738 cp_crs_stat igs_en_stdnt_ps_att.course_attempt_status%TYPE) IS
739 SELECT 'x'
740 FROM igs_en_stdnt_ps_att spa,
741 igs_en_intm_types intm,
742 igs_en_stdnt_ps_intm spi
743 WHERE spa.person_id = cp_person_id
744 AND spa.person_id = spi.person_id
745 AND spa.course_cd = spi.course_cd
746 AND spa.course_attempt_status = cp_crs_stat
747 AND spi.intermission_type = intm.intermission_type
748 AND spi.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY')
749 AND intm.study_antr_inst_ind = g_ind_yes;
750
751 -- Cursor for validating if there is any program attempt of the person in
752 -- status INACTIVE or ENROLLED. This validation is required to validate if the Person
753 -- is active in the system
754 CURSOR cur_crs(cp_person_id igs_fi_parties_v.person_id%TYPE) IS
755 SELECT 'x'
756 FROM igs_en_stdnt_ps_att
757 WHERE person_id = cp_person_id
758 AND course_attempt_status IN ('INACTIVE','ENROLLED');
759
760 -- Cursor for getting the Fee Calendar Instance details.
761 CURSOR cur_cal(cp_cal_type VARCHAR2,
762 cp_seq_num NUMBER) IS
763 SELECT cal_type||' '||to_char(start_dt)||' '||to_char(end_dt) fee_prd
764 FROM igs_ca_inst
765 WHERE cal_type = cp_cal_type
766 AND sequence_number = cp_seq_num;
767
768 -- Cursor for getting the Invoice Number based on the Invoice Id
769 CURSOR cur_inv(cp_invoice_id NUMBER) IS
770 SELECT invoice_number
771 FROM igs_fi_inv_int
772 WHERE invoice_id = cp_invoice_id;
773
774
775 -- Variables of the database column type
776 l_dr_gl_ccid igs_fi_refunds.dr_gl_ccid%TYPE;
777 l_cr_gl_ccid igs_fi_refunds.cr_gl_ccid%TYPE;
778 l_dr_account_cd igs_fi_refunds.dr_account_cd%TYPE;
779 l_cr_account_cd igs_fi_refunds.cr_account_cd%TYPE;
780 l_rowid igs_fi_refunds_v.row_id%TYPE;
781 l_refund_id igs_fi_refunds.refund_id%TYPE;
782 l_invoice_id igs_fi_inv_int.invoice_id%TYPE;
783 l_dr_ccid igs_fi_refunds.dr_gl_ccid%TYPE;
784 l_cr_ccid igs_fi_refunds.cr_gl_ccid%TYPE;
785 l_dr_acc_cd igs_fi_refunds.dr_account_cd%TYPE;
786 l_cr_acc_cd igs_fi_refunds.cr_account_cd%TYPE;
787 l_inv_amt_due igs_fi_inv_int.invoice_amount_due%TYPE;
788 l_unapp_amount igs_fi_credits.unapplied_amount%TYPE;
789 l_application_id igs_fi_applications.application_id%TYPE;
790 l_payee_id igs_fi_parties_v.person_id%TYPE;
791 l_reason fnd_new_messages.message_text%TYPE;
792 l_err_msg fnd_new_messages.message_name%TYPE;
793 l_determination igs_lookups_view.lookup_code%TYPE;
794 l_fee_type igs_fi_fee_type.fee_type%TYPE;
795 l_fee_cal_type igs_ca_inst.cal_type%TYPE;
796 l_fee_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
797 l_rfnd_amnt igs_fi_refunds.refund_amount%TYPE;
798 l_party_number igs_fi_parties_v.person_number%TYPE;
799 l_invoice_number igs_fi_inv_int.invoice_number%TYPE;
800 l_group_cd igs_pe_all_persid_group_v.group_cd%TYPE;
801 l_v_message_name fnd_new_messages.message_name%TYPE;
802 l_v_closing_status gl_period_statuses.closing_status%TYPE;
803 l_d_gl_date igs_fi_credits_all.gl_date%TYPE;
804 l_n_party_id hz_parties.party_id%TYPE;
805 l_v_manage_acc igs_fi_control_all.manage_accounts%TYPE := NULL;
806
807 -- Variables holding number type data
808 l_n_cntr NUMBER(15) := 0;
809 l_cntr NUMBER(15);
810 l_rfndcntr NUMBER(15);
811 l_trm_cntr NUMBER(15);
812 l_loop NUMBER(15);
813 l_msg_count NUMBER(15);
814
815 -- Variables holding Varchar2 type data
816 l_var VARCHAR2(1);
817 l_msg_text VARCHAR2(2000);
818 l_v_sql_stmnt VARCHAR2(32767) ;
819 l_v_status VARCHAR2(32767);
820
821 -- Variables of type BOOLEAN
822 l_rec_match BOOLEAN := FALSE;
823 l_party_process BOOLEAN := FALSE;
824 l_status BOOLEAN;
825 l_rec_found BOOLEAN;
826 l_fa_received BOOLEAN;
827 l_process_flag BOOLEAN;
828 l_exception_flag BOOLEAN;
829 l_val_parameters BOOLEAN := TRUE;
830 l_hld_exsts BOOLEAN := FALSE;
831
832 -- Rowtype variables
833 l_fee_prd cur_cal%ROWTYPE;
834
835 -- cursor variable for cursor c_refund_cr_glccid
836 rec_c_refund_cr_glccid c_refund_cr_glccid%ROWTYPE;
837
838 TYPE r_c_grp_cur IS REF CURSOR ;
839 TYPE tab_party_rec IS TABLE OF hz_parties.party_id%TYPE INDEX BY BINARY_INTEGER;
840 c_ref_grp_cur r_c_grp_cur;
841 v_tab_party_rec tab_party_rec ;
842
843 l_n_act_plan_id igs_fi_pp_std_attrs.student_plan_id%TYPE;
844 l_v_act_plan_name igs_fi_pp_templates.payment_plan_name%TYPE;
845 lv_group_type igs_pe_persid_group_v.group_type%TYPE;
846
847 BEGIN
848 igs_ge_gen_003.set_org_id(g_null);
849
850 retcode := 0;
851 errbuf := NULL;
852
853 -- Obtain the value of manage_accounts in the System Options form
854 -- If it is null or 'OTHER', then this process is not available, so error out.
855 igs_fi_com_rec_interface.chk_manage_account( p_v_manage_acc => l_v_manage_acc,
856 p_v_message_name => l_v_message_name
857 );
858 IF (l_v_manage_acc = 'OTHER') OR (l_v_manage_acc IS NULL) THEN
859 fnd_message.set_name('IGS',l_v_message_name);
860 fnd_file.put_line(fnd_file.log,fnd_message.get());
861 fnd_file.new_line(fnd_file.log);
862 retcode := 2;
863 RETURN;
864 END IF;
865
866 SAVEPOINT RFND_PRC_BATCH;
867
868 l_val_parameters := TRUE;
869
870 -- Initialize the PL/SQL tables
871 refund_calc.delete;
872 refund_fee_prd.delete;
873
874 -- Log the Input parameters
875 fnd_message.set_name('IGS','IGS_FI_ANC_LOG_PARM');
876 fnd_file.put_line(fnd_file.log,fnd_message.get);
877
878 l_var := NULL;
879
880 -- If the person id is not null, then validate the Person id
881 OPEN cur_val_party(p_person_id);
882 FETCH cur_val_party INTO l_party_number;
883 log_message(g_msg_lkp,
884 'PERSON',
885 l_party_number,
886 1);
887 IF (cur_val_party%NOTFOUND AND p_person_id IS NOT NULL) THEN
888 fnd_message.set_name('IGS','IGS_FI_INVALID_PERSON_ID');
889 fnd_file.put_line(fnd_file.log,fnd_message.get);
890 l_val_parameters := FALSE;
891 END IF;
892 CLOSE cur_val_party;
893
894 l_var := NULL;
895
896 -- If the Person id Group is not null, then validate the Person Id Group
897 OPEN cur_pers(p_person_id_grp);
898 FETCH cur_pers INTO l_group_cd;
899 log_message(g_msg_lkp,
900 'PERSON_GROUP',
901 l_group_cd,
902 1);
903
904 IF (cur_pers%NOTFOUND AND p_person_id_grp IS NOT NULL) THEN
905
906 fnd_message.set_name('IGS','IGS_FI_INVPERS_ID_GRP');
907 fnd_file.put_line(fnd_file.log,fnd_message.get);
908 l_val_parameters := FALSE;
909
910 END IF;
911 CLOSE cur_pers;
912
913 --GL Date parameter is mandatory to this concurrent job, hence it is passed as null, error out NOCOPY the job.
914 IF p_d_gl_date IS NULL THEN
915 FND_MESSAGE.SET_NAME('IGS','IGS_FI_PARAMETER_NULL');
916 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
917 l_val_parameters := FALSE;
918 END IF;
919
920 --Convert the parameter p_d_gl_date from VARCHAR2 to DATE datatype.
921 l_d_gl_date := IGS_GE_DATE.IGSDATE(p_d_gl_date);
922
923 log_message(g_msg_lkp,
924 'VAL_ADD_DROP',
925 get_meaning(g_check_add_drop,p_add_drop),
926 1);
927
928 log_message(g_msg_lkp,
929 'GL_DATE',
930 l_d_gl_date,
931 1);
932
933 log_message(g_msg_lkp,
934 'TEST_MODE',
935 get_meaning(g_yes_no,p_test_run),
936 1);
937
938
939 -- If the Person Id and the Person id Group are passed, then the process should
940 -- error out NOCOPY
941 -- IGS_FI_PRS_PRSIDGRP_NULL message replaced by IGS_FI_NO_PERS_PGRP
942 IF p_person_id IS NOT NULL AND p_person_id_grp IS NOT NULL THEN
943 fnd_message.set_name('IGS','IGS_FI_NO_PERS_PGRP');
944 fnd_file.put_line(fnd_file.log,fnd_message.get);
945 l_val_parameters := FALSE;
946 END IF;
947
948 --Validate the GL Date.
949 igs_fi_gen_gl.get_period_status_for_date(p_d_date => l_d_gl_date,
950 p_v_closing_status => l_v_closing_status,
951 p_v_message_name => l_v_message_name
952 );
953 IF l_v_message_name IS NOT NULL THEN
954 FND_MESSAGE.SET_NAME('IGS',l_v_message_name);
955 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
956 l_val_parameters := FALSE;
957 END IF;
958
959 --Error out NOCOPY the concurrent process if the GL Date is not a valid one.
960 IF l_v_closing_status NOT IN ('O','F') THEN
961 FND_MESSAGE.SET_NAME('IGS','IGS_FI_INVALID_GL_DATE');
962 FND_MESSAGE.SET_TOKEN('GL_DATE',l_d_gl_date);
963 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
964 l_val_parameters := FALSE;
965 END IF;
966
967 OPEN c_refund_cr_glccid;
968 FETCH c_refund_cr_glccid INTO rec_c_refund_cr_glccid;
969 CLOSE c_refund_cr_glccid;
970
971 IF rec_c_refund_cr_glccid.refund_cr_gl_ccid IS NOT NULL THEN
972 -- if the function to check validity of Liability Account CCID combination returns false
973 -- log the message and errors out
974 IF NOT (igs_fi_gen_apint.chk_liability_acc(p_n_ccid => rec_c_refund_cr_glccid.refund_cr_gl_ccid)) THEN
975 fnd_message.set_name('IGS','IGS_FI_INV_ACC_LIABL');
976 fnd_file.put_line(fnd_file.log,fnd_message.get);
977 l_val_parameters := FALSE;
978 END IF;
979 END IF;
980
981
982 fnd_file.new_line(fnd_file.log,2);
983
984 IF NOT l_val_parameters THEN
985 retcode := 2;
986 RETURN;
987 END IF;
988
989 -- Derive the Refunds Accounting
990 igs_fi_gen_refunds.get_refund_acc(p_dr_gl_ccid => l_dr_gl_ccid,
991 p_cr_gl_ccid => l_cr_gl_ccid,
992 p_dr_account_cd => l_dr_account_cd,
993 p_cr_account_cd => l_cr_account_cd,
994 p_err_message => l_err_msg,
995 p_status => l_status);
996
997 -- If the Refunds Accounting procedure returns an error, then
998 -- the process should log this in the log file of the concurrent manager
999 IF NOT l_status THEN
1000 fnd_message.set_name('IGS',l_err_msg);
1001 fnd_file.put_line(fnd_file.log,fnd_message.get);
1002 app_exception.raise_exception;
1003 END IF;
1004
1005 IF p_person_id_grp IS NOT NULL THEN
1006 --Bug #5021084
1007 l_v_sql_stmnt := igf_ap_ss_pkg.get_pid(p_person_id_grp, l_v_status ,lv_group_type);
1008
1009 --Bug #5021084. Passing Group ID if the group type is STATIC.
1010 IF lv_group_type = 'STATIC' THEN
1011 OPEN c_ref_grp_cur FOR l_v_sql_stmnt USING p_person_id_grp;
1012 ELSIF lv_group_type = 'DYNAMIC' THEN
1013 OPEN c_ref_grp_cur FOR l_v_sql_stmnt;
1014 END IF;
1015
1016 LOOP
1017 FETCH c_ref_grp_cur INTO l_n_party_id;
1018 EXIT WHEN c_ref_grp_cur%NOTFOUND;
1019 v_tab_party_rec(l_n_cntr) := l_n_party_id;
1020 l_n_cntr := NVL(l_n_cntr,0) +1;
1021 END LOOP;
1022 CLOSE c_ref_grp_cur;
1023 -- If person_id passed then open cur_party and if no ID info given
1024 -- then open cur_party_all to process all records with extra credits
1025 ELSIF p_person_id IS NOT NULL THEN
1026 OPEN cur_party(p_person_id);
1027 FETCH cur_party BULK COLLECT INTO v_tab_party_rec;
1028 CLOSE cur_party;
1029 ELSE
1030 OPEN cur_party_all;
1031 FETCH cur_party_all BULK COLLECT INTO v_tab_party_rec;
1032 CLOSE cur_party_all;
1033 END IF;
1034
1035 l_n_cntr := v_tab_party_rec.FIRST;
1036 IF v_tab_party_rec.COUNT > 0 THEN
1037 -- Loop across all the Person ids identified for processing for Refunds
1038 FOR l_n_cntr IN v_tab_party_rec.FIRST..v_tab_party_rec.LAST
1039 LOOP
1040 l_n_party_id := v_tab_party_rec(l_n_cntr);
1041 -- Fetch the Party Number for the Person Id being passed as input
1042 l_party_number := igs_fi_gen_008.get_party_number(l_n_party_id);
1043
1044 log_message(g_msg_lkp,
1045 'PERSON',
1046 l_party_number,
1047 1);
1048
1049 l_party_process := TRUE;
1050
1051 -- Validate if the Party exists as a sponsor in the system. If the party is a sponsor
1052 -- then no refunds should be done for the party and the process should proceed for
1053 -- refunds for the next person
1054 OPEN cur_spnsr(l_n_party_id);
1055 FETCH cur_spnsr INTO l_var;
1056 IF cur_spnsr%FOUND THEN
1057 fnd_message.set_name('IGS','IGS_FI_RFND_NOT_SPNSR');
1058 fnd_file.put_line(fnd_file.log,fnd_message.get);
1059 l_party_process := FALSE;
1060 END IF;
1061 CLOSE cur_spnsr;
1062
1063
1064 --bug#2400617,the check of refunds not be processed if student is studyng at another instution or the
1065 --student is inactive should be there irrespective of the financial aid student or not.
1066
1067 -- If the Refunds have to be processed as per the validations earlier, in case of Financial Aid
1068 -- received for the period, then automatic refunds should not be done in the following cases
1069 -- 1. Student is studying at another institution
1070 -- 2. Student is inactive in the system i.e. if any program attempt status of the student
1071 -- is INACTIVE or ENROLLED, then the student is active in the system
1072
1073 IF l_party_process THEN
1074
1075 -- Validate if the Student is studying at another institution. If yes, then no automatic refunds
1076 -- have to be processed for the student
1077 OPEN cur_inst(l_n_party_id,
1078 g_intermit);
1079 FETCH cur_inst INTO l_var;
1080 IF cur_inst%FOUND THEN
1081 fnd_message.set_name('IGS','IGS_FI_PRG_INTERMIT');
1082 fnd_file.new_line(fnd_file.log,1);
1083 fnd_file.put_line(fnd_file.log,fnd_message.get);
1084 fnd_file.new_line(fnd_file.log,1);
1085 l_party_process:= FALSE;
1086 END IF;
1087 CLOSE cur_inst;
1088
1089 -- If the earlier validation is passed, i.e. the l_process_flag is not set to FALSE
1090 IF l_party_process THEN
1091
1092 -- validate if any program attempt of the student is INACTIVE or ENROLLED
1093 OPEN cur_crs(l_n_party_id);
1094 FETCH cur_crs INTO l_var;
1095
1096 -- If there are none, then refunds should not be processed for the student
1097 IF cur_crs%NOTFOUND THEN
1098 fnd_message.set_name('IGS','IGS_FI_STDNT_NOT_ACTIVE');
1099 fnd_file.new_line(fnd_file.log,1);
1100 fnd_file.put_line(fnd_file.log,fnd_message.get);
1101 fnd_file.new_line(fnd_file.log,1);
1102 l_party_process:= FALSE;
1103 END IF;
1104 CLOSE cur_crs;
1105 END IF;
1106 END IF;
1107
1108
1109 -- validate if the Person has any holds of STOPREFUNDS
1110 IF igs_fi_gen_refunds.get_rfnd_hold(p_person_id => l_n_party_id) THEN
1111 l_hld_exsts := TRUE;
1112 l_party_process := FALSE;
1113 END IF;
1114
1115 l_rfnd_cntr := 0;
1116 l_trm_cntr := 0;
1117
1118 refund_calc.DELETE;
1119 refund_fee_prd.DELETE;
1120
1121 -- If the refunds have to be processed for the person, then
1122 IF l_party_process THEN
1123
1124 -- check if the Person for which the Refunds is being processed is on an Active Payment Plan.
1125 -- If the person is on an active payment plan, then a warning needs to be logged in the log file.
1126 -- The process will continue after logging this warning message
1127 -- Invoke the Generic procedure
1128 igs_fi_gen_008.get_plan_details (p_n_person_id => l_n_party_id,
1129 p_n_act_plan_id => l_n_act_plan_id,
1130 p_v_act_plan_name => l_v_act_plan_name
1131 );
1132
1133 IF l_v_act_plan_name IS NOT NULL THEN
1134 fnd_message.set_name('IGS','IGS_FI_PP_RFND_WARN');
1135 fnd_file.new_line(fnd_file.log,1);
1136 fnd_file.put_line(fnd_file.log,fnd_message.get);
1137 fnd_file.new_line(fnd_file.log,1);
1138 retcode := 1;
1139 END IF;
1140
1141 l_rec_found := FALSE;
1142
1143 -- Fetch all the cleared credit records for the Person Id being processed
1144 FOR reccrd IN cur_crd(l_n_party_id,
1145 g_cleared) LOOP
1146 l_rec_found := TRUE;
1147
1148 l_cntr := 0;
1149
1150 -- If the credit source of the Credit Record is DLP or FLP, then
1151 IF ((reccrd.credit_source IS NOT NULL) AND (reccrd.credit_source IN (g_dlp,g_flp))) THEN
1152
1153 l_err_msg := NULL;
1154 l_status := TRUE;
1155 l_determination := NULL;
1156
1157 -- This means that the Credit Record being processed is a PLUS loan which should not have been
1158 -- selected because if any refunds were to be done for this credit record, then it should have already
1159 -- been done in the Plus loans process. However, if there is a borrower determination for the
1160 -- credit to be in Student Account, then nothing needs to be done for this credit. Else
1161 -- the process should log this as error in the concurrent manager log file
1162
1163 -- Determine the Borrower's determination for the PLUS loan
1164 igs_fi_gen_refunds.get_borw_det(p_credit_id => reccrd.credit_id,
1165 p_determination => l_determination,
1166 p_err_message => l_err_msg,
1167 p_status => l_status);
1168
1169 -- If the Borrower's Determination process results in an error, then
1170 -- this error message should be logged in the log file of the concurrent manager.
1171 IF NOT l_status THEN
1172 fnd_message.set_name('IGS',
1173 l_err_msg);
1174 fnd_file.put_line(fnd_file.log,
1175 fnd_message.get);
1176 ELSE
1177
1178 -- if the borrower determination procedure returns success, then validate the determination
1179 -- if the borrower determination is ON ACCOUNT, then log this in the log file of the
1180 -- concurrent manager and no refunds need to be processed for this
1181 IF l_determination = g_on_acc THEN
1182 fnd_message.set_name('IGS',
1183 'IGS_FI_RFND_CRD_ACC');
1184 fnd_message.set_token('CRD_NUM',
1185 reccrd.credit_number);
1186 fnd_file.put_line(fnd_file.log,
1187 fnd_message.get);
1188 ELSE
1189
1190 -- else this credit record has had errors when the PLUS loan process tried to process it.
1191 -- Hence, this should be logged in the log file of the concurrent manager
1192 fnd_message.set_name('IGS',
1193 'IGS_FI_RFND_CRD_PLUS');
1194 fnd_message.set_token('CREDIT_NUMBER',
1195 reccrd.credit_number);
1196 fnd_file.put_line(fnd_file.log,
1197 fnd_message.get);
1198 END IF;
1199 END IF;
1200 ELSE
1201 l_fee_cal_type := reccrd.fee_cal_type;
1202 l_fee_ci_sequence_number := reccrd.fee_ci_sequence_number;
1203 l_fee_type := NULL;
1204 l_status := TRUE;
1205
1206 OPEN cur_cal(l_fee_cal_type,
1207 l_fee_ci_sequence_number);
1208 FETCH cur_cal INTO l_fee_prd;
1209 CLOSE cur_cal;
1210
1211 -- Determine the Fee Type, Fee Calendar Instance for the Sub Account
1212 igs_fi_gen_refunds.get_fee_prd(p_fee_type => l_fee_type,
1213 p_fee_cal_type => l_fee_cal_type,
1214 p_fee_ci_sequence_number => l_fee_ci_sequence_number,
1215 p_status => l_status);
1216
1217 -- If the procedure returns with an error, then this should be logged in the log file
1218 -- of the concurrent manager
1219 IF NOT l_status THEN
1220 -- Log the processing Credit number in log file, bug 3775620
1221 log_message(g_msg_lkp,
1222 'CREDIT_NUMBER',
1223 reccrd.credit_number,
1224 1);
1225 fnd_message.set_name('IGS','IGS_FI_REFUND_FTCI');
1226 fnd_file.put_line(fnd_file.log,fnd_message.get);
1227 l_fee_prd := NULL;
1228 fnd_file.put_line(fnd_file.log,fnd_message.get);
1229 ELSE
1230
1231 -- Else this should be added to the PL/SQL table for the Refunds processing
1232 l_rfnd_cntr := l_rfnd_cntr + 1;
1233 refund_calc(l_rfnd_cntr).fee_type := l_fee_type;
1234 refund_calc(l_rfnd_cntr).fee_cal_type := l_fee_cal_type;
1235 refund_calc(l_rfnd_cntr).fee_ci_sequence_number := l_fee_ci_sequence_number;
1236 refund_calc(l_rfnd_cntr).credit_class := reccrd.credit_class;
1237 refund_calc(l_rfnd_cntr).unapplied_amount := reccrd.unapplied_amount;
1238 refund_calc(l_rfnd_cntr).credit_id := reccrd.credit_id;
1239 refund_calc(l_rfnd_cntr).credit_number := reccrd.credit_number;
1240 END IF;
1241 END IF;
1242 END LOOP;
1243
1244 refund_fee_prd.DELETE;
1245
1246 l_trm_cntr := 0;
1247
1248 -- if there are any records in the Refunds PL/SQL table, then get the distinct Fee Calendar Instance,
1249 -- from the PL/SQL table. These distinct values are the Fee Calendar Instances for which
1250 -- the refunds need to be processed
1251 IF refund_calc.COUNT > 0 THEN
1252
1253 -- Loop across all the credits being identified for refunds
1254 -- The following code determines the distinct combination of the Sub Account, Fee Type, Fee Cal Type
1255 -- and Fee Ci Sequence Number and puts all such distinct combinations into another PL/SQL table
1256 FOR l_loop IN refund_calc.FIRST..refund_calc.LAST LOOP
1257
1258 -- If it is the first record being processed, then add the Sub Account, Fee Type, Fee Cal Type and
1259 -- Fee CI Sequence Number to the Refunds Fee Periods PL/SQL table
1260 IF refund_fee_prd.COUNT = 0 THEN
1261 l_trm_cntr := l_trm_cntr + 1;
1262 refund_fee_prd(l_trm_cntr).fee_type := refund_calc(l_loop).fee_type;
1263 refund_fee_prd(l_trm_cntr).fee_cal_type := refund_calc(l_loop).fee_cal_type;
1264 refund_fee_prd(l_trm_cntr).fee_ci_sequence_number :=
1265 refund_calc(l_loop).fee_ci_sequence_number;
1266 ELSE
1267
1268 -- Else if there are any records in the Refunds Fee Periods PL/SQL table, then the code identifies whether
1269 -- the combination already exists in the PL/SQL table or not. If it exists, then no need to do anything
1270 -- else the combination needs to be added in the PL/SQL table for Refunds Fee Periods
1271 IF refund_fee_prd.COUNT > 0 THEN
1272 l_rec_match := FALSE;
1273 FOR l_cntr IN refund_fee_prd.FIRST..refund_fee_prd.LAST LOOP
1274 IF ((refund_fee_prd(l_cntr).fee_type = refund_calc(l_loop).fee_type) AND
1275 (refund_fee_prd(l_cntr).fee_cal_type = refund_calc(l_loop).fee_cal_type) AND
1276 (refund_fee_prd(l_cntr).fee_ci_sequence_number =
1277 refund_calc(l_loop).fee_ci_sequence_number)) THEN
1278 l_rec_match := TRUE;
1279 END IF;
1280 END LOOP;
1281
1282 IF NOT l_rec_match THEN
1283 l_trm_cntr := l_trm_cntr + 1;
1284 refund_fee_prd(l_trm_cntr).fee_type := refund_calc(l_loop).fee_type;
1285 refund_fee_prd(l_trm_cntr).fee_cal_type := refund_calc(l_loop).fee_cal_type;
1286 refund_fee_prd(l_trm_cntr).fee_ci_sequence_number :=
1287 refund_calc(l_loop).fee_ci_sequence_number;
1288 END IF;
1289 END IF;
1290 END IF;
1291 END LOOP;
1292 END IF;
1293
1294 -- If there are some records in the Refunds Fee Periods then
1295 IF refund_fee_prd.COUNT > 0 THEN
1296
1297 -- Loop across all such records in the Refunds Fee Periods table
1298 FOR l_loop IN refund_fee_prd.FIRST..refund_fee_prd.LAST LOOP
1299 l_fee_type := refund_fee_prd(l_loop).fee_type;
1300 l_fee_cal_type := refund_fee_prd(l_loop).fee_cal_type;
1301 l_fee_ci_sequence_number := refund_fee_prd(l_loop).fee_ci_sequence_number;
1302
1303 l_fa_received := FALSE;
1304 l_cntr := 0;
1305 l_rfnd_amnt := 0;
1306
1307 OPEN cur_cal(l_fee_cal_type,
1308 l_fee_ci_sequence_number);
1309 FETCH cur_cal INTO l_fee_prd;
1310 CLOSE cur_cal;
1311
1312 -- For each of the combination of Sub Account and FTCI in the Refunds Fee Periods table,
1313 -- loop across all the records in the Refunds Calculation PL/SQL table which match the combination
1314 FOR l_cntr IN refund_calc.FIRST..refund_calc.LAST LOOP
1315 IF ((refund_calc(l_cntr).fee_type = l_fee_type) AND
1316 (refund_calc(l_cntr).fee_cal_type = l_fee_cal_type) AND
1317 (refund_calc(l_cntr).fee_ci_sequence_number = l_fee_ci_sequence_number)) THEN
1318
1319 -- Calculate the total amount to be refunded by summing up the total unapplied amount of the credits
1320 l_rfnd_amnt := NVL(l_rfnd_amnt,0)+
1321 NVL(refund_calc(l_cntr).unapplied_amount,0);
1322
1323 END IF;
1324 END LOOP;
1325 -- If any of the credits in the Refunds Calculation PL/SQL table has a Financial Aid or Sponsorship
1326 -- Credit Class, then set the flag for the Financial Aid received for the Fee Period
1327 FOR l_cntr IN refund_calc.FIRST..refund_calc.LAST LOOP
1328 IF ((refund_calc(l_cntr).fee_cal_type = l_fee_cal_type) AND
1329 (refund_calc(l_cntr).fee_ci_sequence_number =l_fee_ci_sequence_number)) THEN
1330
1331 -- If any of the credits in the Refunds Calculation PL/SQL table has a Financial Aid or Sponsorship
1332 -- Credit Class, then set the flag for the Financial Aid received for the Fee Period
1333 IF refund_calc(l_cntr).credit_class in ('EXTFA','INTFA','SPNSP') THEN
1334 l_fa_received := TRUE;
1335 END IF;
1336 END IF;
1337 END LOOP;
1338
1339
1340 l_process_flag := TRUE;
1341
1342 IF l_rfnd_amnt <= 0 THEN
1343 l_process_flag := FALSE;
1344 END IF;
1345
1346
1347
1348 -- If the Financial Aid is Received then validate if the Add Drop period is specified
1349 IF (l_fa_received AND l_process_flag) THEN
1350
1351 -- If the Add Drop parameter is not null and the add drop parameter value is ALL or FINAID, then
1352 IF ((p_add_drop IS NOT NULL) AND (p_add_drop IN ('ALL','FINAID'))) THEN
1353
1354 -- Call the function for validating the Add Drop for the Fee Period
1355 IF IGS_FI_GEN_REFUNDS.Val_Add_Drop(l_fee_cal_type,
1356 l_fee_ci_sequence_number) THEN
1357
1358 -- If the function returns true, then this fee period should not be processed for refunds as it is still
1359 -- in the Add Drop period
1360 fnd_message.set_name('IGS',
1361 'IGS_FI_ADD_DROP');
1362 fnd_message.set_token('FEE_PRD',
1363 l_fee_prd.fee_prd);
1364 fnd_file.put_line(fnd_file.log,
1365 fnd_message.get);
1366 l_process_flag := FALSE;
1367 END IF;
1368 END IF;
1369 ELSIF ((l_process_flag) AND (l_fa_received = FALSE)) THEN
1370
1371 -- Else if the Financial Aid is not received in the Fee Period, then validate if
1372 -- the Add Drop period is there for the Fee Calendar Instance
1373 IF ((p_add_drop IS NOT NULL) AND (p_add_drop = 'ALL')) THEN
1374 IF IGS_FI_GEN_REFUNDS.Val_Add_Drop(l_fee_cal_type,
1375 l_fee_ci_sequence_number) THEN
1376 fnd_message.set_name('IGS',
1377 'IGS_FI_ADD_DROP');
1378 fnd_message.set_token('FEE_PRD',
1379 l_fee_prd.fee_prd);
1380 fnd_file.put_line(fnd_file.log,
1381 fnd_message.get);
1382
1383 -- If the Add Drop period is valid, then set the l_process_flag to FALSE
1384 -- Based on this l_process_flag, the refunds are processed
1385 l_process_flag := FALSE;
1386 END IF;
1387 END IF;
1388 END IF;
1389
1390 -- If the l_process_flag is set to TRUE, then
1391 IF l_process_flag THEN
1392
1393 -- Validate if the Refund Amount is within the Tolerance limits if setup
1394 -- call the function val_rfnd_lim to validate if the refund amount is within the Tolerance
1395 -- limits. If the refund amount is not within the Tolerance limits then refunds should not be
1396 -- processed and the log file of the concurrent manager should be updated appropriately
1397 IF NOT val_rfnd_lim(l_rfnd_amnt) THEN
1398 fnd_message.set_name('IGS',
1399 'IGS_FI_TOL_LIM_DEFIED');
1400 fnd_message.set_token('REF_AMOUNT',
1401 To_Char(l_rfnd_amnt));
1402 fnd_message.set_token('HIGH_LIM',
1403 To_Char(g_amnt_high));
1404 fnd_message.set_token('LOW_LIM',
1405 To_Char(g_amnt_low));
1406 fnd_file.new_line(fnd_file.log,
1407 1);
1408 fnd_file.put_line(fnd_file.log,
1409 fnd_message.get);
1410 fnd_file.new_line(fnd_file.log,
1411 1);
1412 ELSE
1413
1414 -- Call the Refunds table handler for creating a record in the Refunds table
1415 l_rowid := g_null;
1416 l_refund_id := g_null;
1417 l_invoice_id := g_null;
1418 fnd_message.set_name('IGS',
1419 'IGS_FI_REFUND_REASON_BATCH');
1420 l_reason := fnd_message.get;
1421
1422 SAVEPOINT REFUNDS;
1423 BEGIN
1424 l_exception_flag := FALSE;
1425
1426 -- Call to igs_fi_gen_gl.get_formatted_amount formats refund_amount by rounding off to currency precision
1427 l_rfnd_amnt := igs_fi_gen_gl.get_formatted_amount(l_rfnd_amnt);
1428
1429 igs_fi_refunds_pkg.insert_row(X_ROWID => l_rowid,
1430 X_REFUND_ID => l_refund_id,
1431 X_VOUCHER_DATE => TRUNC(sysdate),
1432 X_PERSON_ID => l_n_party_id,
1433 X_PAY_PERSON_ID => l_n_party_id,
1434 X_DR_GL_CCID => l_dr_gl_ccid,
1435 X_CR_GL_CCID => l_cr_gl_ccid,
1436 X_DR_ACCOUNT_CD => l_dr_account_cd,
1437 X_CR_ACCOUNT_CD => l_cr_account_cd,
1438 X_REFUND_AMOUNT => l_rfnd_amnt,
1439 X_FEE_TYPE => l_fee_type,
1440 X_FEE_CAL_TYPE => l_fee_cal_type,
1441 X_FEE_CI_SEQUENCE_NUMBER => l_fee_ci_sequence_number,
1442 X_SOURCE_REFUND_ID => g_null,
1443 X_INVOICE_ID => l_invoice_id,
1444 X_TRANSFER_STATUS => g_todo,
1445 X_REVERSAL_IND => g_ind_no,
1446 X_REASON => l_reason,
1447 X_ATTRIBUTE_CATEGORY => g_null,
1448 X_ATTRIBUTE1 => g_null,
1449 X_ATTRIBUTE2 => g_null,
1450 X_ATTRIBUTE3 => g_null,
1451 X_ATTRIBUTE4 => g_null,
1452 X_ATTRIBUTE5 => g_null,
1453 X_ATTRIBUTE6 => g_null,
1454 X_ATTRIBUTE7 => g_null,
1455 X_ATTRIBUTE8 => g_null,
1456 X_ATTRIBUTE9 => g_null,
1457 X_ATTRIBUTE10 => g_null,
1458 X_ATTRIBUTE11 => g_null,
1459 X_ATTRIBUTE12 => g_null,
1460 X_ATTRIBUTE13 => g_null,
1461 X_ATTRIBUTE14 => g_null,
1462 X_ATTRIBUTE15 => g_null,
1463 X_ATTRIBUTE16 => g_null,
1464 X_ATTRIBUTE17 => g_null,
1465 X_ATTRIBUTE18 => g_null,
1466 X_ATTRIBUTE19 => g_null,
1467 X_ATTRIBUTE20 => g_null,
1468 X_GL_DATE => l_d_gl_date,
1469 X_REVERSAL_GL_DATE => NULL);
1470
1471 -- Log the Refund details in the concurrent manager log file
1472 log_message(g_msg_lkp,
1473 'REFUND_ID',
1474 l_refund_id,
1475 2);
1476 log_message(g_msg_lkp,
1477 'FEE_TYPE',
1478 l_fee_type,
1479 2);
1480 log_message(g_msg_lkp,
1481 'FEE_CAL_TYPE',
1482 l_fee_cal_type,
1483 2);
1484 log_message(g_msg_lkp,
1485 'FEE_PERIOD',
1486 l_fee_prd.fee_prd,
1487 2);
1488 log_message(g_msg_lkp,
1489 'AMOUNT',
1490 l_rfnd_amnt,
1491 2);
1492 log_message(g_msg_lkp,
1493 'PAYEE',
1494 l_party_number,
1495 2);
1496 log_message(g_msg_lkp,
1497 'VOUCHER_DATE',
1498 to_char(sysdate),
1499 2);
1500
1501 -- Fetch the Invoice Number for the Invoice created
1502 OPEN cur_inv(l_invoice_id);
1503 FETCH cur_inv INTO l_invoice_number;
1504 CLOSE cur_inv;
1505
1506 log_message(g_msg_lkp,
1507 'INVOICE_NUMBER',
1508 l_invoice_number,
1509 2);
1510
1511 -- Loop across all the identified credit records for the FTCI and the Sub Account
1512 FOR l_cntr IN refund_calc.FIRST..refund_calc.LAST LOOP
1513 IF ((refund_calc(l_cntr).fee_type = l_fee_type) AND
1514 (refund_calc(l_cntr).fee_cal_type = l_fee_cal_type) AND
1515 (refund_calc(l_cntr).fee_ci_sequence_number = l_fee_ci_sequence_number)) THEN
1516
1517 log_message(g_msg_lkp,
1518 'CREDIT_NUMBER',
1519 refund_calc(l_cntr).credit_number,
1520 3);
1521 log_message(g_msg_lkp,
1522 'UNAPPLIED_AMOUNT',
1523 refund_calc(l_cntr).unapplied_amount,
1524 3);
1525
1526 -- Apply the Credit records to the Invoice created by calling the
1527 -- Applications general procedure
1528 l_application_id := NULL;
1529 igs_fi_gen_007.create_application(p_credit_id =>
1530 refund_calc(l_cntr).credit_id,
1531 p_invoice_id => l_invoice_id,
1532 p_amount_apply =>
1533 refund_calc(l_cntr).unapplied_amount,
1534 p_appl_type => g_app,
1535 p_appl_hierarchy_id => g_null,
1536 p_application_id => l_application_id,
1537 p_validation => g_ind_yes,
1538 p_dr_gl_ccid => l_dr_ccid,
1539 p_cr_gl_ccid => l_cr_ccid,
1540 p_dr_account_cd => l_dr_acc_cd,
1541 p_cr_account_cd => l_cr_acc_cd,
1542 p_unapp_amount => l_unapp_amount,
1543 p_inv_amt_due => l_inv_amt_due,
1544 p_err_msg => l_err_msg,
1545 p_status => l_status,
1546 p_d_gl_date => l_d_gl_date);
1547
1548 -- If the Applications procedure returns an error message, then this should be logged to the log file
1549 -- of the concurrent manager
1550 IF NOT l_status THEN
1551 fnd_message.set_name('IGS',
1552 l_err_msg);
1553 l_msg_text := fnd_message.get;
1554 fnd_file.put_line(fnd_file.log,
1555 lpad(l_msg_text,length(l_msg_text)+6,' '));
1556 app_exception.raise_exception;
1557 END IF;
1558 l_unapp_amount := NULL;
1559 l_inv_amt_due := NULL;
1560 l_dr_acc_cd := NULL;
1561 l_cr_acc_cd := NULL;
1562 l_dr_ccid := NULL;
1563 l_cr_ccid := NULL;
1564
1565 END IF;
1566 END LOOP;
1567 fnd_file.new_line(fnd_file.log);
1568 EXCEPTION
1569 WHEN OTHERS THEN
1570
1571 -- If an error happens, then log the error message in the log file and rollback to
1572 -- the save point of Refunds
1573 fnd_file.put_line(fnd_file.log,
1574 sqlerrm);
1575 ROLLBACK TO REFUNDS;
1576 l_msg_count := FND_MSG_PUB.Count_Msg;
1577 FOR l_msg_cntr IN 1..l_msg_count LOOP
1578 fnd_file.put_line(fnd_file.log,
1579 fnd_msg_pub.get(p_msg_index => l_msg_cntr,
1580 p_encoded => 'T'));
1581 END LOOP;
1582 l_exception_flag := TRUE;
1583 END;
1584
1585 -- If an exception happens, then log the message in the log file of the
1586 -- Concurrent Manager
1587 IF l_exception_flag THEN
1588 fnd_message.set_name('IGS',
1589 'IGS_FI_REFUND_ERR');
1590 fnd_file.put_line(fnd_file.log,
1591 fnd_message.get||sqlerrm);
1592 END IF;
1593 END IF;
1594 END IF;
1595 END LOOP;
1596 END IF;
1597
1598 -- If no credits have been found for processing then
1599 -- log the message in the log file of the concurrent manager
1600 IF (NOT l_party_process) THEN
1601 fnd_message.set_name('IGS','IGS_FI_RFND_PRC_NULL');
1602 fnd_file.put_line(fnd_file.log, fnd_message.get);
1603 END IF;
1604
1605 IF (NOT l_rec_found) THEN
1606 fnd_message.set_name('IGS','IGS_FI_NO_RFND_EXCESS_AMT');
1607 fnd_file.put_line(fnd_file.log, fnd_message.get);
1608 END IF;
1609 ELSE
1610
1611 -- This means that the holds of STOPREFUNDS exist for the person and no automatic refund
1612 -- processing needs to be done for the Person
1613 IF l_hld_exsts THEN
1614 fnd_message.set_name('IGS',
1615 'IGS_FI_RFND_HLD_EXSTS');
1616 fnd_message.set_token('PARTY_NUM',
1617 l_party_number);
1618 fnd_file.put_line(fnd_file.log,
1619 fnd_message.get);
1620 END IF;
1621 END IF;
1622 END LOOP;
1623 END IF;
1624 -- If the Process is run in Test Run Mode, then the process should rollback
1625 IF p_test_run = 'N' THEN
1626 COMMIT WORK;
1627 ELSE
1628 ROLLBACK TO RFND_PRC_BATCH;
1629 END IF;
1630
1631 EXCEPTION
1632 WHEN e_resource_busy THEN
1633 fnd_message.set_name('IGS',
1634 'IGS_FI_RFND_REC_LOCK');
1635 fnd_file.put_line(fnd_file.log,
1636 fnd_message.get);
1637 ROLLBACK TO RFND_PRC_BATCH;
1638 retcode := 2;
1639 WHEN OTHERS THEN
1640 retcode := 2;
1641 errbuf := 'IGS_GE_UNHANDLED_EXCEPTION';
1642 ROLLBACK TO RFND_PRC_BATCH;
1643 fnd_message.set_name('IGS',
1644 'IGS_GE_UNHANDLED_EXCEPTION');
1645 fnd_file.put_line(fnd_file.log,
1646 fnd_message.get);
1647 fnd_file.put_line(fnd_file.log,
1648 sqlerrm);
1649 igs_ge_msg_stack.conc_exception_hndl;
1650 END process_batch;
1651
1652 END igs_fi_prc_refunds;
[Home] [Help]