[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_PRC_WAIVERS
Source
1 PACKAGE BODY igs_fi_prc_waivers AS
2 /* $Header: IGSFI93B.pls 120.9 2006/05/04 07:40:13 abshriva noship $ */
3
4 /******************************************************************
5 Created By : Amit Gairola
6 Date Created By : 10-Aug-2005
7 Purpose : Package for the waiver processing
8
9 Known limitations,enhancements,remarks:
10 Change History
11 Who When What
12 abshriva 4-May-2006 Bug 5178077: Introduced igs_ge_gen_003.set_org_id in process_waivers
13 svuppala 24-Sep-2005 Bug# 4624875 Waiver Adjustment Charge Is Not Created (Manual Reversal Of Charge)
14 In Function validate_params, Changed the cursor cur_wav_pgm and the
15 conditional check for waiver category "COMP_RULE" was removed.
16
17 ***************************************************************** */
18
19 g_v_alternatecode igs_ca_inst.alternate_code%TYPE;
20
21 g_v_seperator CONSTANT VARCHAR2(1) := '-';
22
23 -- Procedure for enabling statement level logging
24 PROCEDURE log_to_fnd (
25 p_v_module IN VARCHAR2,
26 p_v_string IN VARCHAR2
27 );
28
29 PROCEDURE proc_manual_waiver_adj(p_n_person_id NUMBER,
30 p_v_fee_type VARCHAR2,
31 p_v_fee_cal_type VARCHAR2,
32 p_n_fee_ci_seq_number NUMBER,
33 p_v_currency_cd VARCHAR2,
34 p_d_gl_date DATE,
35 p_v_process_mode VARCHAR2,
36 x_return_status OUT NOCOPY VARCHAR2
37 );
38
39
40 FUNCTION validate_params(p_v_fee_cal_type igs_ca_inst.cal_type%TYPE,
41 p_n_fee_ci_seq igs_ca_inst.sequence_number%TYPE,
42 p_v_fee_type igs_fi_fee_type.fee_type%TYPE,
43 p_v_waiver_name igs_fi_waiver_pgms.waiver_name%TYPE) RETURN BOOLEAN AS
44
45 /******************************************************************
46 Created By : Amit Gairola
47 Date Created By : 10-Aug-2005
48 Purpose : Procedure for validating parameters
49
50 Known limitations,enhancements,remarks:
51 Change History
52 Who When What
53 svuppala 24-Sep-2005 Bug# 4624875 Waiver Adjustment Charge Is Not Created (Manual Reversal Of Charge )
54 Changed the cursor cur_wav_pgm and the conditional check for waiver category
55 "COMP_RULE" was removed.
56
57 ***************************************************************** */
58
59 CURSOR cur_wav_pgm(cp_v_fee_cal_type igs_ca_inst.cal_type%TYPE,
60 cp_n_fee_ci_seq igs_ca_inst.sequence_number%TYPE,
61 cp_v_fee_type igs_fi_fee_type.fee_type%TYPE,
62 cp_v_waiver_name igs_fi_waiver_pgms.waiver_name%TYPE) IS
63 SELECT waiver_name
64 FROM igs_fi_waiver_pgms
65 WHERE target_fee_type = cp_v_fee_type
66 AND fee_cal_type = cp_v_fee_cal_type
67 AND fee_ci_sequence_number = cp_n_fee_ci_seq
68 AND ((waiver_name = cp_v_waiver_name) OR (cp_v_waiver_name IS NULL));
69
70 l_v_var VARCHAR2(1);
71 l_b_rec_found BOOLEAN;
72 l_b_ret_val BOOLEAN;
73 l_v_waiver_name igs_fi_waiver_pgms.waiver_name%TYPE;
74
75 BEGIN
76 l_b_rec_found := FALSE;
77 l_b_ret_val := TRUE;
78
79 OPEN cur_wav_pgm(p_v_fee_cal_type,
80 p_n_fee_ci_seq,
81 p_v_fee_type,
82 p_v_waiver_name);
83
84 FETCH cur_wav_pgm INTO l_v_waiver_name;
85 -- If the cursor select returns atleast one row, setting the
86 --boolean flag l_b_rec_found to TRUE
87 IF cur_wav_pgm%FOUND THEN
88 l_b_rec_found := TRUE;
89 END IF;
90 CLOSE cur_wav_pgm;
91
92 IF NOT l_b_rec_found THEN
93 IF p_v_waiver_name IS NULL THEN
94 fnd_message.set_name('IGS',
95 'IGS_FI_WAV_PGM_NO_REC_FOUND');
96 fnd_message.set_token('FEE_ALT_CD',
97 igs_ca_gen_001.calp_get_alt_cd(p_v_fee_cal_type,p_n_fee_ci_seq));
98 fnd_message.set_token('FEE_TYPE',
99 p_v_fee_type);
100 ELSE
101 fnd_message.set_name('IGS',
102 'IGS_FI_WAV_FEE_CAL_INST');
103 END IF;
104 fnd_msg_pub.add;
105 l_b_ret_val := FALSE;
106 END IF;
107
108 RETURN l_b_ret_val;
109 END validate_params;
110
111 FUNCTION get_calc_old_waiver_amt(p_n_person_id NUMBER,
112 p_v_fee_cal_type VARCHAR2,
113 p_n_fee_ci_seq_number NUMBER,
114 p_v_waiver_name VARCHAR2) RETURN NUMBER AS
115 /******************************************************************
116 Created By : Amit Gairola
117 Date Created By : 10-Aug-2005
118 Purpose : Procedure for deriving the old waiver amount
119
120 Known limitations,enhancements,remarks:
121 Change History
122 Who When What
123
124 ***************************************************************** */
125 CURSOR cur_wav_crd(cp_n_person_id igs_fi_credits_all.party_id%TYPE,
126 cp_v_fee_cal_type igs_fi_credits_all.fee_cal_type%TYPE,
127 cp_n_fee_ci_seq igs_fi_credits_all.fee_ci_sequence_number%TYPE,
128 cp_v_waiver_name igs_fi_credits_all.waiver_name%TYPE) IS
129 SELECT crd.amount,
130 crd.credit_id
131 FROM igs_fi_credits_all crd
132 WHERE crd.party_id = cp_n_person_id
133 AND crd.fee_cal_type = cp_v_fee_cal_type
134 AND crd.fee_ci_sequence_number = cp_n_fee_ci_seq
135 AND crd.waiver_name = cp_v_waiver_name;
136
137 CURSOR cur_chg_appl(cp_n_credit_id igs_fi_credits_all.credit_id%TYPE,
138 cp_v_fee_cal_type igs_fi_credits_all.fee_cal_type%TYPE,
139 cp_n_fee_ci_seq igs_fi_credits_all.fee_ci_sequence_number%TYPE,
140 cp_v_waiver_name igs_fi_credits_all.waiver_name%TYPE) IS
141 SELECT SUM(appl.amount_applied) amt_appl
142 FROM igs_fi_applications appl,
143 igs_fi_inv_int_all inv
144 WHERE appl.credit_id = cp_n_credit_id
145 AND appl.invoice_id = inv.invoice_id
146 AND inv.fee_cal_type = cp_v_fee_cal_type
147 AND inv.fee_ci_sequence_number = cp_n_fee_ci_seq
148 AND inv.waiver_name = cp_v_waiver_name
149 AND appl.application_type = 'APP'
150 AND inv.transaction_type = 'WAIVER_ADJ';
151
152
153
154 l_n_old_wav_amt NUMBER;
155 BEGIN
156 log_to_fnd(p_v_module => 'get_calc_old_waiver_amt',
157 p_v_string => ' Entered Function get_calc_old_waiver_amt: The input parameters are '||
158 ' p_n_person_id : ' ||p_n_person_id ||
159 ' p_v_fee_cal_type : ' ||p_v_fee_cal_type ||
160 ' p_n_fee_ci_seq_number : ' ||p_n_fee_ci_seq_number ||
161 ' p_v_waiver_name : ' ||p_v_waiver_name
162 );
163 l_n_old_wav_amt := 0;
164 FOR l_rec_crd IN cur_wav_crd(p_n_person_id,
165 p_v_fee_cal_type,
166 p_n_fee_ci_seq_number,
167 p_v_waiver_name) LOOP
168 l_n_old_wav_amt := l_n_old_wav_amt + NVL(l_rec_crd.amount,0);
169
170 FOR l_rec_chg IN cur_chg_appl(l_rec_crd.credit_id,
171 p_v_fee_cal_type,
172 p_n_fee_ci_seq_number,
173 p_v_waiver_name) LOOP
174 l_n_old_wav_amt := l_n_old_wav_amt - NVL(l_rec_chg.amt_appl,0);
175 END LOOP;
176 END LOOP;
177 log_to_fnd(p_v_module => 'get_calc_old_waiver_amt',
178 p_v_string => ' Function get_calc_old_waiver_amt returning '||
179 ' old Waiver Amount : ' ||l_n_old_wav_amt
180 );
181 RETURN l_n_old_wav_amt;
182 END get_calc_old_waiver_amt;
183
184 PROCEDURE call_fee_calc(p_n_person_id igs_fi_inv_int_all.person_id%TYPE,
185 p_v_fee_type igs_fi_inv_int_all.fee_type%TYPE,
186 p_v_fee_cal_type igs_fi_inv_int_all.fee_cal_type%TYPE,
187 p_n_fee_ci_seq igs_fi_inv_int_all.fee_ci_sequence_number%TYPE,
188 p_d_gl_date DATE,
189 p_v_real_time_flag VARCHAR2,
190 p_v_process_mode VARCHAR2,
191 p_v_career VARCHAR2,
192 x_wav_amount OUT NOCOPY NUMBER,
193 x_ret_status OUT NOCOPY VARCHAR2) AS
194 /******************************************************************
195 Created By : Amit Gairola
196 Date Created By : 10-Aug-2005
197 Purpose : Procedure for calling Fee Calc
198
199 Known limitations,enhancements,remarks:
200 Change History
201 Who When What
202
203 ***************************************************************** */
204 l_n_waiver_amount igs_fi_credits_all.amount%TYPE;
205 l_d_date DATE;
206 l_b_bool BOOLEAN;
207 l_v_message_name VARCHAR2(2000);
208 BEGIN
209 log_to_fnd(p_v_module => 'call_fee_calc',
210 p_v_string => ' Entered Procedure call_fee_calc: The input parameters are '||
211 ' p_n_person_id : ' ||p_n_person_id ||
212 ' p_v_fee_type : ' ||p_v_fee_type ||
213 ' p_v_fee_cal_type : ' ||p_v_fee_cal_type ||
214 ' p_n_fee_ci_seq_number : ' ||p_n_fee_ci_seq ||
215 ' p_d_gl_date : ' ||p_d_gl_date ||
216 ' p_v_real_time_flag : ' ||p_v_real_time_flag ||
217 ' p_v_process_mode : ' ||p_v_process_mode ||
218 ' p_v_career : ' ||p_v_career
219 );
220 l_b_bool := igs_fi_prc_fee_ass.finp_ins_enr_fee_ass(p_effective_dt => SYSDATE,
221 p_person_id => p_n_person_id,
222 p_course_cd => null,
223 p_fee_category => null,
224 p_fee_cal_type => p_v_fee_cal_type,
225 p_fee_ci_sequence_num => p_n_fee_ci_seq,
226 p_fee_type => p_v_fee_type,
227 p_trace_on => 'N',
228 p_test_run => 'Y',
229 p_creation_dt => l_d_date,
230 p_message_name => l_v_message_name,
231 p_process_mode => p_v_process_mode,
232 p_c_career => p_v_career,
233 p_d_gl_date => p_d_gl_date,
234 p_v_wav_calc_flag => 'Y',
235 p_n_waiver_amount => l_n_waiver_amount);
236
237
238 IF NOT l_b_bool THEN
239 x_wav_amount := 0;
240 x_ret_status := 'E';
241 ELSE
242 x_wav_amount := l_n_waiver_amount;
243 x_ret_status := 'S';
244 END IF;
245 log_to_fnd(p_v_module => 'call_fee_calc',
246 p_v_string => ' igs_fi_prc_fee_ass.finp_ins_enr_fee_ass returned '||
247 ' waiver amount : ' ||l_n_waiver_amount
248 );
249 EXCEPTION
250 WHEN OTHERS THEN
251 log_to_fnd(p_v_module => 'call_fee_calc',
252 p_v_string => ' Procedure call_fee_calc raised unhandled exception '||SQLERRM
253 );
254 x_wav_amount := 0;
255 x_ret_status := 'E';
256 RAISE;
257 END call_fee_calc;
258
259 FUNCTION get_wav_amt(p_n_person_id igs_fi_inv_int_all.person_id%TYPE,
260 p_v_fee_type igs_fi_inv_int_all.fee_type%TYPE,
261 p_v_fee_cal_type igs_fi_inv_int_all.fee_cal_type%TYPE,
262 p_n_fee_ci_seq igs_fi_inv_int_all.fee_ci_sequence_number%TYPE,
263 p_v_waiver_name igs_fi_inv_int_all.waiver_name%TYPE) RETURN NUMBER AS
264 /******************************************************************
265 Created By : Amit Gairola
266 Date Created By : 10-Aug-2005
267 Purpose : Procedure for Waiver Amount
268
269 Known limitations,enhancements,remarks:
270 Change History
271 Who When What
272 smadathi 08-Nov-2005 Bug 4634950. Modified the cursor cur_prewav
273 to include filter criteria
274 ***************************************************************** */
275
276 CURSOR cur_inv(cp_n_person_id igs_fi_inv_int_all.person_id%TYPE,
277 cp_v_fee_type igs_fi_inv_int_all.fee_type%TYPE,
278 cp_v_fee_cal_type igs_fi_inv_int_all.fee_cal_type%TYPE,
279 cp_n_fee_ci_seq igs_fi_inv_int_all.fee_ci_sequence_number%TYPE) IS
280 SELECT inv.invoice_amount,
281 inv.invoice_id
282 FROM igs_fi_inv_int_all inv
283 WHERE inv.person_id = cp_n_person_id
284 AND inv.fee_type = cp_v_fee_type
285 AND inv.fee_cal_type = cp_v_fee_cal_type
286 AND inv.fee_ci_sequence_number = cp_n_fee_ci_seq
287 AND inv.transaction_type <> 'RETENTION'
288 ORDER BY inv.invoice_id;
289
290 CURSOR cur_chgadj(cp_n_invoice_id igs_fi_inv_int_all.invoice_id%TYPE) IS
291 SELECT SUM(appl.amount_applied) amt_appl
292 FROM igs_fi_applications appl,
293 igs_fi_credits_all crd,
294 igs_fi_cr_types crt
295 WHERE appl.invoice_id = cp_n_invoice_id
296 AND appl.credit_id = crd.credit_id
297 AND crd.credit_type_id = crt.credit_type_id
298 AND crt.credit_class = 'CHGADJ';
299
300 CURSOR cur_prewav(cp_v_fee_cal_type igs_fi_inv_int_all.fee_cal_type%TYPE,
301 cp_n_fee_ci_seq igs_fi_inv_int_all.fee_ci_sequence_number%TYPE,
302 cp_v_waiver_name igs_fi_wav_pr_preqs.sub_waiver_name%TYPE) IS
303 SELECT fwpp.sup_waiver_name
304 FROM igs_fi_wav_pr_preqs fwpp
305 WHERE fwpp.fee_cal_type = cp_v_fee_cal_type
306 AND fwpp.fee_ci_sequence_number = cp_n_fee_ci_seq
307 AND fwpp.sub_waiver_name = cp_v_waiver_name;
308
309 CURSOR cur_prewav_appl(cp_n_invoice_id igs_fi_inv_int_all.invoice_id%TYPE,
310 cp_v_waiver_name igs_fi_inv_int_all.waiver_name%TYPE,
311 cp_v_fee_cal_type igs_fi_inv_int_all.fee_cal_type%TYPE,
312 cp_n_fee_ci_seq igs_fi_inv_int_all.fee_ci_sequence_number%TYPE) IS
313 SELECT appl.amount_applied,
314 appl.application_id,
315 appl.credit_id
316 FROM igs_fi_applications appl,
317 igs_fi_credits_all crd,
318 igs_fi_cr_types_all crt
319 WHERE appl.invoice_id = cp_n_invoice_id
320 AND appl.credit_id = crd.credit_id
321 AND appl.application_type = 'APP'
322 AND crd.credit_type_id = crt.credit_type_id
323 AND crt.credit_class = 'WAIVER'
324 AND crd.fee_cal_type = cp_v_fee_cal_type
325 AND crd.fee_ci_sequence_number = cp_n_fee_ci_seq
326 AND crd.waiver_name = cp_v_waiver_name
327 ORDER BY appl.application_id;
328
329 CURSOR cur_wav_adj(cp_n_credit_id igs_fi_credits_all.credit_id%TYPE,
330 cp_v_fee_cal_type igs_fi_credits_all.fee_cal_type%TYPE,
331 cp_n_fee_ci_seq igs_fi_credits_all.fee_ci_sequence_number%TYPE,
332 cp_n_invoice_id igs_fi_inv_int.invoice_id%TYPE) IS
333 SELECT igs_fi_gen_007.get_sum_appl_amnt(application_id) amt_appl
334 FROM igs_fi_applications appl,
335 igs_fi_inv_int_all inv
336 WHERE appl.invoice_id = inv.invoice_id
337 AND appl.credit_id = cp_n_credit_id
338 AND inv.fee_cal_type = cp_v_fee_cal_type
339 AND inv.fee_ci_sequence_number = cp_n_fee_ci_seq
340 AND inv.transaction_type = 'WAIVER_ADJ'
341 AND appl.application_type = 'APP'
342 AND EXISTS (SELECT 'x'
343 FROM igs_fi_credits_all crd1,
344 igs_fi_applications appl1
345 WHERE appl1.credit_id = crd1.credit_id
346 AND crd1.credit_id = cp_n_credit_id
347 AND appl1.invoice_id = cp_n_invoice_id
348 AND appl1.application_type = 'UNAPP'
349 AND appl1.amount_applied = - appl.amount_applied);
350
351 l_n_inv_amt NUMBER;
352 l_n_chgadj_amt NUMBER;
353 l_n_prereq_wavcrd_amt NUMBER;
354 l_n_prereq_wavadj_amt NUMBER;
355 l_n_wav_app_amt NUMBER;
356
357 l_n_wav_amt NUMBER;
358 BEGIN
359 log_to_fnd(p_v_module => 'get_wav_amt',
360 p_v_string => ' Entered FUNCTION get_wav_amt: The input parameters are '||
361 ' p_n_person_id : ' ||p_n_person_id ||
362 ' p_v_fee_type : ' ||p_v_fee_type ||
363 ' p_v_fee_cal_type : ' ||p_v_fee_cal_type ||
364 ' p_n_fee_ci_seq_number : ' ||p_n_fee_ci_seq ||
365 ' p_v_waiver_name : ' ||p_v_waiver_name
366 );
367 -- Loop across all the non-retention charges
368 l_n_inv_amt := 0;
369
370 FOR l_rec_inv IN cur_inv(p_n_person_id,
371 p_v_fee_type,
372 p_v_fee_cal_type,
373 p_n_fee_ci_seq) LOOP
374
375 l_n_inv_amt := NVL(l_n_inv_amt,0) +
376 NVL(l_rec_inv.invoice_amount,0);
377
378 -- Identify the negative charge adjustments for the Charge
379 FOR l_rec_chgadj IN cur_chgadj(l_rec_inv.invoice_id) LOOP
380 l_n_inv_amt := NVL(l_n_inv_amt,0) -
381 NVL(l_rec_chgadj.amt_appl,0);
382 END LOOP;
383
384 l_n_wav_amt := 0;
385
386 -- Loop across the Superior waiver programs for the Fee Period and the waiver
387 -- passed as input
388 FOR l_rec_prewav IN cur_prewav(p_v_fee_cal_type,
389 p_n_fee_ci_seq,
390 p_v_waiver_name) LOOP
391
392 -- Loop across the application records for the charge in context for the
393 -- Superior Waiver
394 FOR l_rec_prewav_appl IN cur_prewav_appl(cp_n_invoice_id => l_rec_inv.invoice_id,
395 cp_v_waiver_name => l_rec_prewav.sup_waiver_name,
396 cp_v_fee_cal_type => p_v_fee_cal_type,
397 cp_n_fee_ci_seq => p_n_fee_ci_seq
398 ) LOOP
399 l_n_wav_amt := NVL(l_n_wav_amt,0) +
400 NVL(l_rec_prewav_appl.amount_applied,0);
401
402 -- Subtract the waiver adjustment amount
403 FOR l_rec_wavchg IN cur_wav_adj(l_rec_prewav_appl.credit_id,
404 p_v_fee_cal_type,
405 p_n_fee_ci_seq,
406 l_rec_inv.invoice_id) LOOP
407 l_n_wav_amt := NVL(l_n_wav_amt,0) -
408 NVL(l_rec_wavchg.amt_appl,0);
409 END LOOP;
410 END LOOP;
411 END LOOP;
412
413 l_n_inv_amt := NVL(l_n_inv_amt,0) -
414 NVL(l_n_wav_amt,0);
415 END LOOP;
416 log_to_fnd(p_v_module => 'call_fee_calc',
417 p_v_string => ' FUNCTION get_wav_amt returning '||
418 ' Calculated Waiver Amount : ' ||l_n_inv_amt
419 );
420 RETURN l_n_inv_amt;
421 END get_wav_amt;
422
423 PROCEDURE get_calc_new_waiver_amt(p_n_person_id NUMBER,
424 p_v_fee_cal_type VARCHAR2,
425 p_n_fee_ci_seq_number NUMBER,
426 p_v_waiver_name VARCHAR2,
427 p_v_target_fee_type igs_fi_fee_type.fee_type%TYPE,
428 p_v_rule_fee_type igs_fi_fee_type.fee_type%TYPE,
429 p_v_wav_criteria_code VARCHAR2,
430 p_n_wav_per_alloc NUMBER,
431 p_d_gl_date DATE,
432 p_v_real_time_flag VARCHAR2,
433 p_v_process_mode VARCHAR2,
434 p_v_career VARCHAR2,
435 x_return_wav_amt OUT NOCOPY NUMBER,
436 x_return_status OUT NOCOPY VARCHAR2) AS
437 /******************************************************************
438 Created By : Amit Gairola
439 Date Created By : 10-Aug-2005
440 Purpose : Procedure for New Waiver Amount
441
442 Known limitations,enhancements,remarks:
443 Change History
444 Who When What
445
446 ***************************************************************** */
447
448 l_n_inv_amt NUMBER;
449 l_n_chgadj_amt NUMBER;
450 l_n_prereq_wavcrd_amt NUMBER;
451 l_n_prereq_wavadj_amt NUMBER;
452 l_n_wav_app_amt NUMBER;
453 BEGIN
454
455 log_to_fnd(p_v_module => 'get_calc_new_waiver_amt',
456 p_v_string => ' Entered Procedure get_calc_new_waiver_amt: The input parameters are '||
457 ' p_n_person_id : ' ||p_n_person_id ||
458 ' p_v_target_fee_type : ' ||p_v_target_fee_type ||
459 ' p_v_fee_cal_type : ' ||p_v_fee_cal_type ||
460 ' p_n_fee_ci_seq_number : ' ||p_n_fee_ci_seq_number ||
461 ' p_v_rule_fee_type : ' ||p_v_rule_fee_type ||
462 ' p_v_waiver_name : ' ||p_v_waiver_name ||
463 ' p_v_wav_criteria_code : ' ||p_v_wav_criteria_code ||
464 ' p_n_wav_per_alloc : ' ||p_n_wav_per_alloc ||
465 ' p_v_real_time_flag : ' ||p_v_real_time_flag ||
466 ' p_v_process_mode : ' ||p_v_process_mode ||
467 ' p_v_career : ' ||p_v_career
468 );
469
470 -- If Waiver Criteria Code is passed as Null, return S and 0 amt
471 IF p_v_wav_criteria_code IS NULL THEN
472 log_to_fnd(p_v_module => 'get_calc_new_waiver_amt',
473 p_v_string => ' Null Waiver Criteria passed '
474 );
475 x_return_wav_amt := 0;
476 x_return_status := 'S';
477 END IF;
478
479 -- IF waiver Criteria is Fee Balance
480 IF p_v_wav_criteria_code = 'FEE_BALANCE' THEN
481
482 -- Calculate the Waiver Amount
483 x_return_wav_amt := get_wav_amt(p_n_person_id => p_n_person_id,
484 p_v_fee_type => p_v_rule_fee_type,
485 p_v_fee_cal_type => p_v_fee_cal_type,
486 p_n_fee_ci_seq => p_n_fee_ci_seq_number,
487 p_v_waiver_name => p_v_waiver_name);
488 log_to_fnd(p_v_module => 'get_calc_new_waiver_amt',
489 p_v_string => ' get_wav_amt returned the calculated waiver amount '||x_return_wav_amt
490 );
491 x_return_status := 'S';
492 ELSIF p_v_wav_criteria_code = 'COMPUTE_AMOUNT' THEN
493
494 -- If real time flag is set to Y and rule fee type is same as target fee type
495 IF p_v_real_time_flag = 'Y' THEN
496 IF p_v_rule_fee_type = p_v_target_fee_type THEN
497 x_return_wav_amt := get_wav_amt(p_n_person_id => p_n_person_id,
498 p_v_fee_type => p_v_rule_fee_type,
499 p_v_fee_cal_type => p_v_fee_cal_type,
500 p_n_fee_ci_seq => p_n_fee_ci_seq_number,
501 p_v_waiver_name => p_v_waiver_name);
502 log_to_fnd(p_v_module => 'get_calc_new_waiver_amt',
503 p_v_string => ' get_wav_amt returned the calculated waiver amount '||x_return_wav_amt
504 );
505 x_return_status := 'S';
506 ELSE
507
508 -- Call Fee Calc
509 call_fee_calc(p_n_person_id => p_n_person_id,
510 p_v_fee_type => p_v_rule_fee_type,
511 p_v_fee_cal_type => p_v_fee_cal_type,
512 p_n_fee_ci_seq => p_n_fee_ci_seq_number,
513 p_d_gl_date => p_d_gl_date,
514 p_v_real_time_flag => p_v_real_time_flag,
515 p_v_process_mode => p_v_process_mode,
516 p_v_career => p_v_career,
517 x_wav_amount => x_return_wav_amt,
518 x_ret_status => x_return_status);
519 IF x_return_status <> 'S' THEN
520 log_to_fnd(p_v_module => 'get_calc_new_waiver_amt',
521 p_v_string => ' Procedure call_fee_calc errored out'
522 );
523 x_return_status := 'E';
524 RETURN;
525 END IF;
526 END IF;
527 ELSE
528
529 -- Call Fee Calc
530 call_fee_calc(p_n_person_id => p_n_person_id,
531 p_v_fee_type => p_v_rule_fee_type,
532 p_v_fee_cal_type => p_v_fee_cal_type,
533 p_n_fee_ci_seq => p_n_fee_ci_seq_number,
534 p_d_gl_date => p_d_gl_date,
535 p_v_real_time_flag => p_v_real_time_flag,
536 p_v_process_mode => p_v_process_mode,
537 p_v_career => p_v_career,
538 x_wav_amount => x_return_wav_amt,
539 x_ret_status => x_return_status);
540 IF x_return_status <> 'S' THEN
541 log_to_fnd(p_v_module => 'get_calc_new_waiver_amt',
542 p_v_string => ' Procedure call_fee_calc errored out'
543 );
544 x_return_status := 'E';
545 RETURN;
546 END IF;
547 END IF;
548 END IF;
549 x_return_wav_amt := (NVL(x_return_wav_amt,0)*NVL(p_n_wav_per_alloc,0))/100;
550 log_to_fnd(p_v_module => 'get_calc_new_waiver_amt',
551 p_v_string => ' Procedure get_calc_new_waiver_amt returning '||
552 ' Calculated new waiver amount : ' ||x_return_wav_amt
553 );
554 END get_calc_new_waiver_amt;
555
556
557
558 PROCEDURE create_waivers(p_n_person_id NUMBER,
559 p_v_fee_type VARCHAR2,
560 p_v_fee_cal_type VARCHAR2,
561 p_n_fee_ci_seq_number NUMBER,
562 p_v_waiver_name VARCHAR2,
563 p_v_currency_cd VARCHAR2,
564 p_d_gl_date DATE,
565 p_v_real_time_flag VARCHAR2,
566 p_v_process_mode VARCHAR2,
567 p_v_career VARCHAR2,
568 p_b_init_msg_list BOOLEAN,
569 p_validation_level NUMBER,
570 p_v_raise_wf_event VARCHAR2,
571 x_waiver_amount OUT NOCOPY NUMBER,
572 x_return_status OUT NOCOPY VARCHAR2,
573 x_msg_count OUT NOCOPY NUMBER,
574 x_msg_data OUT NOCOPY VARCHAR2) AS
575 /******************************************************************
576 Created By : Amit Gairola
577 Date Created By : 10-Aug-2005
578 Purpose : Procedure for creating waivers
579
580 Known limitations,enhancements,remarks:
581 Change History
582 Who When What
583 agairola 27-Oct-2005 Bug 4704177: Enhancement for Tuition Waiver
584 CCR. Added check for the Error Account = 'Y'
585
586 ***************************************************************** */
587
588 CURSOR cur_wav_pgm(cp_v_fee_cal_type igs_ca_inst.cal_type%TYPE,
589 cp_n_fee_ci_seq igs_ca_inst.sequence_number%TYPE,
590 cp_v_fee_type igs_fi_fee_type.fee_type%TYPE,
591 cp_v_waiver_name igs_fi_waiver_pgms.waiver_name%TYPE) IS
592 SELECT fwp.*
593 FROM igs_fi_waiver_pgms fwp
594 WHERE fwp.target_fee_type = cp_v_fee_type
595 AND fwp.fee_cal_type = cp_v_fee_cal_type
596 AND fwp.fee_ci_sequence_number = cp_n_fee_ci_seq
597 AND ((fwp.waiver_name = cp_v_waiver_name) OR (cp_v_waiver_name IS NULL))
598 ORDER BY fwp.creation_date;
599
600 CURSOR cur_wav_std_pgm(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
601 cp_v_fee_cal_type igs_ca_inst.cal_type%TYPE,
602 cp_n_fee_ci_seq igs_ca_inst.sequence_number%TYPE,
603 cp_v_waiver_name igs_fi_waiver_pgms.waiver_name%TYPE) IS
604 SELECT assignment_status_code
605 FROM igs_fi_wav_std_pgms
606 WHERE person_id = cp_n_person_id
607 AND fee_cal_type = cp_v_fee_cal_type
608 AND fee_ci_sequence_number = cp_n_fee_ci_seq
609 AND waiver_name = cp_v_waiver_name;
610
611 CURSOR cur_ctrl IS
612 SELECT NVL(waiver_notify_finaid_flag,'N') waiver_notify_finaid_flag
613 FROM igs_fi_control;
614
615 l_v_manage_acc igs_fi_control_all.manage_accounts%TYPE;
616 l_v_message_name VARCHAR2(2000);
617 l_v_ret_status VARCHAR2(1);
618
619 l_n_old_wav_amnt igs_fi_credits_all.amount%TYPE;
620 l_n_new_wav_amnt igs_fi_credits_all.amount%TYPE;
621
622 l_b_wavpgm BOOLEAN;
623 l_b_chr_err_account NUMBER;
624 l_v_var VARCHAR2(1);
625
626 l_n_comp_wav_amnt igs_fi_credits_all.amount%TYPE;
627 l_n_eligible_amnt igs_fi_credits_all.amount%TYPE;
628 l_n_diff_wav_amnt igs_fi_credits_all.amount%TYPE;
629 l_n_credit_id igs_fi_credits_all.credit_id%TYPE;
630 l_n_invoice_id igs_fi_inv_int_all.invoice_id%TYPE;
631
632 l_v_finaid_wvr_flag igs_fi_control.waiver_notify_finaid_flag%TYPE;
633 l_n_tot_wav_amnt igs_fi_credits_all.amount%TYPE;
634 l_v_assgn_stat_code igs_fi_wav_std_pgms.assignment_status_code%TYPE;
635
636 l_n_prc_cnt NUMBER;
637 BEGIN
638
639 -- Create a savepoint
640 SAVEPOINT create_waivers_sp;
641 IF p_b_init_msg_list THEN
642 fnd_msg_pub.initialize;
643 END IF;
644
645 x_return_status := 'S';
646
647 log_to_fnd(p_v_module => 'create_waivers',
648 p_v_string => ' Entered Procedure create_waivers: The input parameters are '||
649 ' p_n_person_id : ' ||p_n_person_id ||
650 ' p_v_fee_type : ' ||p_v_fee_type ||
651 ' p_v_fee_cal_type : ' ||p_v_fee_cal_type ||
652 ' p_n_fee_ci_seq_number : ' ||p_n_fee_ci_seq_number ||
653 ' p_v_waiver_name : ' ||p_v_waiver_name ||
654 ' p_v_currency_cd : ' ||p_v_currency_cd ||
655 ' p_d_gl_date : ' ||p_d_gl_date ||
656 ' p_v_real_time_flag : ' ||p_v_real_time_flag ||
657 ' p_v_process_mode : ' ||p_v_process_mode ||
658 ' p_v_career : ' ||p_v_career ||
659 ' p_v_raise_wf_event : ' ||p_v_raise_wf_event
660 );
661 -- Check for Manage Accounts
662 igs_fi_com_rec_interface.chk_manage_account(p_v_manage_acc => l_v_manage_acc,
663 p_v_message_name => l_v_message_name);
664
665 IF l_v_manage_acc IS NULL OR l_v_manage_acc = 'OTHER' THEN
666 x_return_status := 'E';
667 fnd_message.set_name('IGS',
668 l_v_message_name);
669 fnd_msg_pub.add;
670 RAISE fnd_api.g_exc_error;
671 END IF;
672
673 -- Validate input parameters
674
675 -- If any of the input parameters is Null, then return status as 'E'
676 IF p_n_person_id IS NULL OR
677 p_v_fee_type IS NULL OR
678 p_v_fee_cal_type IS NULL OR
679 p_n_fee_ci_seq_number IS NULL OR
680 p_v_currency_cd IS NULL OR
681 p_d_gl_date IS NULL OR
682 p_v_raise_wf_event IS NULL
683 THEN
684 fnd_message.set_name('IGS','IGS_FI_PARAMETER_NULL');
685 fnd_msg_pub.add;
686 RAISE fnd_api.g_exc_error;
687 END IF;
688
689 IF p_validation_level = 100 THEN
690 IF NOT validate_params(p_v_fee_cal_type => p_v_fee_cal_type,
691 p_n_fee_ci_seq => p_n_fee_ci_seq_number,
692 p_v_fee_type => p_v_fee_type,
693 p_v_waiver_name => p_v_waiver_name) THEN
694 RAISE fnd_api.g_exc_error;
695 END IF;
696 END IF;
697
698 -- call the procedure for manual waiver adjustment
699 proc_manual_waiver_adj(p_n_person_id => p_n_person_id,
700 p_v_fee_type => p_v_fee_type,
701 p_v_fee_cal_type => p_v_fee_cal_type,
702 p_n_fee_ci_seq_number => p_n_fee_ci_seq_number,
703 p_v_currency_cd => p_v_currency_cd,
704 p_d_gl_date => p_d_gl_date,
705 p_v_process_mode => p_v_process_mode,
706 x_return_status => l_v_ret_status);
707
708 IF l_v_ret_status = 'E' THEN
709 log_to_fnd(p_v_module => 'create_waivers',
710 p_v_string => ' proc_manual_waiver_adj errored out'
711 );
712 fnd_message.set_name('IGS',
713 'IGS_FI_WAV_NO_TRANS_CREATED');
714 fnd_msg_pub.add;
715 RAISE fnd_api.g_exc_error;
716 END IF;
717
718 -- Get the Financial Aid Waiver Flag from System Options
719 OPEN cur_ctrl;
720 FETCH cur_ctrl INTO l_v_finaid_wvr_flag;
721 CLOSE cur_ctrl;
722
723 -- Loop across the waiver programs
724 FOR l_rec_wavpgm IN cur_wav_pgm(p_v_fee_cal_type,
725 p_n_fee_ci_seq_number,
726 p_v_fee_type,
727 p_v_waiver_name) LOOP
728 log_to_fnd(p_v_module => 'create_waivers',
729 p_v_string => ' Processing waiver program '||l_rec_wavpgm.waiver_name ||
730 ' Fee Calendar '||p_v_fee_cal_type || ' '||p_n_fee_ci_seq_number
731 );
732 l_n_old_wav_amnt := 0;
733 l_n_new_wav_amnt := 0;
734 l_n_comp_wav_amnt := 0;
735 l_n_credit_id := NULL;
736 l_n_invoice_id := NULL;
737 l_n_prc_cnt := 0;
738 l_b_wavpgm := FALSE;
739
740 -- Check if the Student has the Waiver associated
741 OPEN cur_wav_std_pgm(p_n_person_id,
742 p_v_fee_cal_type,
743 p_n_fee_ci_seq_number,
744 l_rec_wavpgm.waiver_name);
745 FETCH cur_wav_std_pgm INTO l_v_assgn_stat_code;
746 IF cur_wav_std_pgm%FOUND THEN
747 l_b_wavpgm := TRUE;
748 END IF;
749 CLOSE cur_wav_std_pgm;
750
751 IF (l_b_wavpgm) THEN
752 log_to_fnd(p_v_module => 'create_waivers',
753 p_v_string => ' Waiver Assignments found for ' ||
754 ' Student : '||p_n_person_id ||
755 ' Assignment Status : '||l_v_assgn_stat_code ||
756 ' Fee Calendar Type : '||p_v_fee_cal_type ||
757 ' Fee Sequence Num : '||p_n_fee_ci_seq_number ||
758 ' Waiver program : '||l_rec_wavpgm.waiver_name
759 );
760 IF (l_rec_wavpgm.waiver_status_code = 'ACTIVE') AND
761 (l_v_assgn_stat_code = 'ACTIVE') THEN
762 -- Check if the Charge transactions for the Person, Fee Type and Fee Period combination
763 -- have the Error Account flag set.
764 l_b_chr_err_account := igs_fi_wav_utils_002.check_chg_error_account (
765 p_n_person_id => p_n_person_id,
766 p_v_fee_type => p_v_fee_type,
767 p_v_fee_cal_type => p_v_fee_cal_type,
768 p_n_fee_ci_seq_number => p_n_fee_ci_seq_number
769 );
770 IF (l_b_chr_err_account=1) THEN
771 log_to_fnd(p_v_module => 'create_waivers',
772 p_v_string => 'Charges with Error Account exist. Waivers cannot be created');
773 fnd_message.set_name('IGS','IGS_FI_WAV_CHG_ERR');
774 fnd_message.set_token('PERSON',igs_fi_gen_008.get_party_number(p_n_party_id => p_n_person_id));
775 fnd_message.set_token('FEE_TYPE',p_v_fee_type);
776 fnd_message.set_token('FEE_PERIOD',
777 igs_ca_gen_001.calp_get_alt_cd(p_cal_type => p_v_fee_cal_type,
778 p_sequence_number => p_n_fee_ci_seq_number)
779 );
780 fnd_msg_pub.add;
781 x_waiver_amount := 0;
782 RAISE fnd_api.g_exc_error;
783 END IF;
784 IF p_v_raise_wf_event = 'Y' THEN
785 -- Raise the Student Assignment Event
786 log_to_fnd(p_v_module => 'create_waivers',
787 p_v_string => ' Invoking raise_stdntwavassign_event'
788 );
789 igs_fi_wav_dtls_wf.raise_stdntwavassign_event(p_n_person_id => p_n_person_id,
790 p_v_waiver_name => l_rec_wavpgm.waiver_name,
791 p_c_fee_cal_type => p_v_fee_cal_type,
792 p_n_fee_ci_seq_number => p_n_fee_ci_seq_number);
793 END IF;
794
795 -- Get the new waiver amount
796 get_calc_new_waiver_amt(p_n_person_id => p_n_person_id,
797 p_v_fee_cal_type => p_v_fee_cal_type,
798 p_n_fee_ci_seq_number => p_n_fee_ci_seq_number,
799 p_v_waiver_name => l_rec_wavpgm.waiver_name,
800 p_v_target_fee_type => l_rec_wavpgm.target_fee_type,
801 p_v_rule_fee_type => l_rec_wavpgm.rule_fee_type,
802 p_v_wav_criteria_code => l_rec_wavpgm.waiver_criteria_code,
803 p_n_wav_per_alloc => l_rec_wavpgm.waiver_percent_alloc,
804 p_d_gl_date => p_d_gl_date,
805 p_v_real_time_flag => p_v_real_time_flag,
806 p_v_process_mode => p_v_process_mode,
807 p_v_career => p_v_career,
808 x_return_wav_amt => l_n_comp_wav_amnt,
809 x_return_status => x_return_status);
810 IF x_return_status = 'E' THEN
811 log_to_fnd(p_v_module => 'create_waivers',
812 p_v_string => ' get_calc_new_waiver_amt errored out'
813 );
814 fnd_message.set_name('IGS',
815 'IGS_FI_WAV_NO_TRANS_CREATED');
816 fnd_msg_pub.add;
817 x_waiver_amount := 0;
818 RAISE fnd_api.g_exc_error;
819 END IF;
820
821 -- Calculate eligible waiver amount
822 igs_fi_wav_utils_001.get_eligible_waiver_amt(p_n_person_id => p_n_person_id,
823 p_v_fee_cal_type => p_v_fee_cal_type,
824 p_n_fee_ci_seq_number => p_n_fee_ci_seq_number,
825 p_v_waiver_name => l_rec_wavpgm.waiver_name,
826 p_v_target_fee_type => l_rec_wavpgm.target_fee_type,
827 p_v_waiver_method_code => l_rec_wavpgm.waiver_method_code,
828 p_v_waiver_mode_code => null,
829 p_n_source_invoice_id => null,
830 x_return_status => x_return_status,
831 x_eligible_amount => l_n_eligible_amnt);
832 IF x_return_status = 'E' THEN
833 log_to_fnd(p_v_module => 'create_waivers',
834 p_v_string => ' igs_fi_wav_utils_001.get_eligible_waiver_amt errored out'
835 );
836 fnd_message.set_name('IGS',
837 'IGS_FI_WAV_NO_TRANS_CREATED');
838 fnd_msg_pub.add;
839 x_waiver_amount := 0;
840 RAISE fnd_api.g_exc_error;
841 END IF;
842 log_to_fnd(p_v_module => 'create_waivers',
843 p_v_string => ' igs_fi_wav_utils_001.get_eligible_waiver_amt returned'||
844 ' Eligible amount : '||l_n_eligible_amnt
845 );
846
847 l_n_new_wav_amnt := l_n_comp_wav_amnt;
848
849 IF l_n_comp_wav_amnt > l_n_eligible_amnt THEN
850 l_n_new_wav_amnt := l_n_eligible_amnt;
851 END IF;
852 END IF;
853 log_to_fnd(p_v_module => 'create_waivers',
854 p_v_string => ' New Waiver Amount : '||l_n_new_wav_amnt
855 );
856 -- Calculate Old waiver amount
857 l_n_old_wav_amnt := get_calc_old_waiver_amt(p_n_person_id => p_n_person_id,
858 p_v_fee_cal_type => p_v_fee_cal_type,
859 p_n_fee_ci_seq_number => p_n_fee_ci_seq_number,
860 p_v_waiver_name => l_rec_wavpgm.waiver_name);
861
862 -- Calculate the difference
863 l_n_diff_wav_amnt := NVL(l_n_new_wav_amnt,0) -
864 NVL(l_n_old_wav_amnt,0);
865
866 log_to_fnd(p_v_module => 'create_waivers',
867 p_v_string => ' Difference in Waiver Amount : '||l_n_diff_wav_amnt
868 );
869
870 -- If the diff > 0 then call Credits API
871 IF l_n_diff_wav_amnt > 0 THEN
872 igs_fi_wav_utils_002.call_credits_api(p_n_person_id => p_n_person_id,
873 p_v_fee_cal_type => p_v_fee_cal_type,
874 p_n_fee_ci_seq_number => p_n_fee_ci_seq_number,
875 p_v_waiver_name => l_rec_wavpgm.waiver_name,
876 p_n_credit_type_id => l_rec_wavpgm.credit_type_id,
877 p_v_currency_cd => p_v_currency_cd,
878 p_n_waiver_amt => l_n_diff_wav_amnt,
879 p_d_gl_date => p_d_gl_date,
880 p_n_credit_id => l_n_credit_id,
881 x_return_status => x_return_status);
882 IF x_return_status <> 'S' THEN
883 log_to_fnd(p_v_module => 'create_waivers',
884 p_v_string => ' igs_fi_wav_utils_002.call_credits_api errored out'
885 );
886 fnd_message.set_name('IGS',
887 'IGS_FI_WAV_NO_TRANS_CREATED');
888 fnd_msg_pub.add;
889 x_waiver_amount := 0;
890 RAISE fnd_api.g_exc_error;
891 END IF;
892 log_to_fnd(p_v_module => 'create_waivers',
893 p_v_string => ' The call to igs_fi_wav_utils_002.call_credits_api ' ||
894 ' returned waiver credit id : ' ||l_n_credit_id
895 );
896 ELSIF (l_n_diff_wav_amnt < 0) THEN
897 -- Else call Charges API
898 igs_fi_wav_utils_002.call_charges_api(p_n_person_id => p_n_person_id,
899 p_v_fee_cal_type => p_v_fee_cal_type,
900 p_n_fee_ci_seq_number => p_n_fee_ci_seq_number,
901 p_v_waiver_name => l_rec_wavpgm.waiver_name,
902 p_v_adj_fee_type => l_rec_wavpgm.adjustment_fee_type,
903 p_v_currency_cd => p_v_currency_cd,
904 p_n_waiver_amt => l_n_diff_wav_amnt,
905 p_d_gl_date => p_d_gl_date,
906 p_n_invoice_id => l_n_invoice_id,
907 x_return_status => x_return_status);
908 IF x_return_status <> 'S' THEN
909 log_to_fnd(p_v_module => 'create_waivers',
910 p_v_string => ' igs_fi_wav_utils_002.call_charges_api errored out'
911 );
912 fnd_message.set_name('IGS',
913 'IGS_FI_WAV_NO_TRANS_CREATED');
914 fnd_msg_pub.add;
915 x_waiver_amount := 0;
916 RAISE fnd_api.g_exc_error;
917 END IF;
918 log_to_fnd(p_v_module => 'create_waivers',
919 p_v_string => ' The call to igs_fi_wav_utils_002.call_charges_api ' ||
920 ' returned waiver Adjustment id : ' ||l_n_invoice_id
921 );
922 END IF;
923 IF NVL(l_n_diff_wav_amnt,0) <> 0 THEN
924 -- Apply waivers
925 igs_fi_wav_utils_001.apply_waivers(p_n_person_id => p_n_person_id,
926 p_v_fee_cal_type => p_v_fee_cal_type,
927 p_n_fee_ci_seq_number => p_n_fee_ci_seq_number,
928 p_v_waiver_name => l_rec_wavpgm.waiver_name,
929 p_v_target_fee_type => l_rec_wavpgm.target_fee_type,
930 p_v_adj_fee_type => l_rec_wavpgm.adjustment_fee_type,
931 p_v_waiver_method_code => l_rec_wavpgm.waiver_method_code,
932 p_v_waiver_mode_code => l_rec_wavpgm.waiver_mode_code,
933 p_n_source_credit_id => l_n_credit_id,
934 p_n_source_invoice_id => l_n_invoice_id,
935 p_v_currency_cd => p_v_currency_cd,
936 p_d_gl_date => p_d_gl_date,
937 x_return_status => x_return_status);
938
939 IF x_return_status <> 'S' THEN
940 log_to_fnd(p_v_module => 'create_waivers',
941 p_v_string => ' igs_fi_wav_utils_001.apply_waivers errored out'
942 );
943 fnd_message.set_name('IGS',
944 'IGS_FI_WAV_NO_TRANS_CREATED');
945 fnd_msg_pub.add;
946 x_waiver_amount := 0;
947 RAISE fnd_api.g_exc_error;
948 END IF;
949 END IF;
950 -- If the flag is 'Y', raise the business event
951 IF l_v_finaid_wvr_flag = 'Y' AND p_v_raise_wf_event = 'Y' THEN
952 log_to_fnd(p_v_module => 'create_waivers',
953 p_v_string => ' Invoking raise_wavtrandtlstofa_event'
954 );
955 igs_fi_wav_dtls_wf.raise_wavtrandtlstofa_event(p_n_person_id => p_n_person_id,
956 p_v_waiver_name => l_rec_wavpgm.waiver_name,
957 p_c_fee_cal_type => p_v_fee_cal_type,
958 p_n_fee_ci_seq_number => p_n_fee_ci_seq_number,
959 p_n_waiver_amount => l_n_diff_wav_amnt);
960
961 END IF;
962
963 l_n_tot_wav_amnt := NVL(l_n_tot_wav_amnt,0) +
964 l_n_diff_wav_amnt;
965 END IF;
966 END LOOP;
967 log_to_fnd(p_v_module => 'create_waivers',
968 p_v_string => ' The total Waiver Amount : '||l_n_tot_wav_amnt
969 );
970 x_waiver_amount := l_n_tot_wav_amnt;
971
972 EXCEPTION
973 WHEN fnd_api.g_exc_error THEN
974 ROLLBACK TO create_waivers_sp;
975 x_return_status := fnd_api.g_ret_sts_error;
976 x_waiver_amount := 0;
977 fnd_msg_pub.count_and_get( p_count => x_msg_count,
978 p_data => x_msg_data);
979
980 WHEN fnd_api.g_exc_unexpected_error THEN
981 ROLLBACK TO create_waivers_sp;
982 x_return_status := fnd_api.g_ret_sts_unexp_error;
983 x_waiver_amount := 0;
984 fnd_msg_pub.count_and_get( p_count => x_msg_count,
985 p_data => x_msg_data);
986
987 WHEN OTHERS THEN
988 ROLLBACK TO create_waivers_sp;
989 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
990 fnd_msg_pub.add_exc_msg(
991 p_pkg_name => 'igs.plsql.igs_fi_prc_waivers',
992 p_procedure_name => 'create_waivers'
993 );
994 END IF;
995 x_return_status := fnd_api.g_ret_sts_unexp_error;
996 x_waiver_amount := 0;
997 fnd_msg_pub.count_and_get( p_count => x_msg_count,
998 p_data => x_msg_data);
999 END create_waivers;
1000
1001 PROCEDURE proc_manual_waiver_adj(p_n_person_id NUMBER,
1002 p_v_fee_type VARCHAR2,
1003 p_v_fee_cal_type VARCHAR2,
1004 p_n_fee_ci_seq_number NUMBER,
1005 p_v_currency_cd VARCHAR2,
1006 p_d_gl_date DATE,
1007 p_v_process_mode VARCHAR2,
1008 x_return_status OUT NOCOPY VARCHAR2) AS
1009 /******************************************************************
1010 Created By : Amit Gairola
1011 Date Created By : 10-Aug-2005
1012 Purpose : Procedure for manual waiver adjustments
1013
1014 Known limitations,enhancements,remarks:
1015 Change History
1016 Who When What
1017
1018 ***************************************************************** */
1019
1020 CURSOR cur_wav_pgms(cp_v_fee_type igs_fi_fee_type.fee_type%TYPE,
1021 cp_v_fee_cal_type igs_fi_inv_int_all.fee_cal_type%TYPE,
1022 cp_n_fee_ci_seq igs_fi_inv_int_all.fee_ci_sequence_number%TYPE) IS
1023 SELECT fwp.fee_cal_type,
1024 fwp.fee_ci_sequence_number,
1025 fwp.waiver_name,
1026 fwp.waiver_method_code,
1027 fwp.waiver_status_code,
1028 fwp.credit_type_id,
1029 fwp.target_fee_type,
1030 fwp.waiver_mode_code,
1031 fwp.adjustment_fee_type
1032 FROM igs_fi_waiver_pgms fwp
1033 WHERE fwp.target_fee_type = cp_v_fee_type
1034 AND fwp.fee_cal_type = cp_v_fee_cal_type
1035 AND fwp.fee_ci_sequence_number = cp_n_fee_ci_seq
1036 AND fwp.waiver_method_code = 'MANUAL';
1037
1038 CURSOR cur_crd(cp_n_person_id igs_fi_credits_all.party_id%TYPE,
1039 cp_v_waiver_name igs_fi_credits_all.waiver_name%TYPE,
1040 cp_v_fee_cal_type igs_fi_credits_all.fee_cal_type%TYPE,
1041 cp_n_fee_ci_seq igs_fi_credits_all.fee_ci_sequence_number%TYPE) IS
1042 SELECT credit_id,
1043 amount,
1044 unapplied_amount
1045 FROM igs_fi_credits_all
1046 WHERE party_id = cp_n_person_id
1047 AND fee_cal_type = cp_v_fee_cal_type
1048 AND fee_ci_sequence_number = cp_n_fee_ci_seq
1049 AND waiver_name = cp_v_waiver_name
1050 AND unapplied_amount > 0;
1051
1052 CURSOR cur_app(cp_n_credit_id igs_fi_credits_all.credit_id%TYPE) IS
1053 SELECT invoice_id
1054 FROM igs_fi_applications
1055 WHERE credit_id = cp_n_credit_id
1056 AND application_type = 'UNAPP'
1057 ORDER BY application_id;
1058
1059 l_b_chr_err_account NUMBER;
1060 BEGIN
1061 log_to_fnd(p_v_module => 'proc_manual_waiver_adj',
1062 p_v_string => ' Entered Procedure proc_manual_waiver_adj: The input parameters are '||
1063 ' p_n_person_id : ' ||p_n_person_id ||
1064 ' p_v_fee_type : ' ||p_v_fee_type ||
1065 ' p_v_fee_cal_type : ' ||p_v_fee_cal_type ||
1066 ' p_n_fee_ci_seq_number : ' ||p_n_fee_ci_seq_number ||
1067 ' p_v_currency_cd : ' ||p_v_currency_cd ||
1068 ' p_d_gl_date : ' ||p_d_gl_date ||
1069 ' p_v_process_mode : ' ||p_v_process_mode
1070 );
1071 x_return_status := 'S';
1072
1073 -- If any of the input parameters is Null, then return status as 'E'
1074 IF p_n_person_id IS NULL OR
1075 p_v_fee_type IS NULL OR
1076 p_v_fee_cal_type IS NULL OR
1077 p_n_fee_ci_seq_number IS NULL OR
1078 p_v_currency_cd IS NULL OR
1079 p_d_gl_date IS NULL THEN
1080
1081 x_return_status := 'E';
1082 RETURN;
1083 END IF;
1084
1085 -- Loop across the waiver programs
1086 FOR l_rec_wav_pgms IN cur_wav_pgms(p_v_fee_type,
1087 p_v_fee_cal_type,
1088 p_n_fee_ci_seq_number) LOOP
1089 -- Loop across the Credits
1090 FOR l_rec_crd IN cur_crd(p_n_person_id,
1091 l_rec_wav_pgms.waiver_name,
1092 p_v_fee_cal_type,
1093 p_n_fee_ci_seq_number) LOOP
1094
1095 -- If the waiver mode code is Charge Level, then apply waivers
1096 IF l_rec_wav_pgms.waiver_mode_code = 'CHARGE_LEVEL' THEN
1097 FOR l_rec_app IN cur_app(l_rec_crd.credit_id) LOOP
1098 igs_fi_wav_utils_001.apply_waivers(p_n_person_id => p_n_person_id,
1099 p_v_fee_cal_type => p_v_fee_cal_type,
1100 p_n_fee_ci_seq_number => p_n_fee_ci_seq_number,
1101 p_v_waiver_name => l_rec_wav_pgms.waiver_name,
1102 p_v_target_fee_type => l_rec_wav_pgms.target_fee_type,
1103 p_v_adj_fee_type => l_rec_wav_pgms.adjustment_fee_type,
1104 p_v_waiver_method_code => l_rec_wav_pgms.waiver_method_code,
1105 p_v_waiver_mode_code => l_rec_wav_pgms.waiver_mode_code,
1106 p_n_source_credit_id => l_rec_crd.credit_id,
1107 p_n_source_invoice_id => l_rec_app.invoice_id,
1108 p_v_currency_cd => p_v_currency_cd,
1109 p_d_gl_date => p_d_gl_date,
1110 x_return_status => x_return_status);
1111 IF x_return_status <> 'S' THEN
1112 log_to_fnd(p_v_module => 'proc_manual_waiver_adj',
1113 p_v_string => ' Procedure igs_fi_wav_utils_001.apply_waivers errored out'
1114 );
1115 x_return_status := 'E';
1116 RETURN;
1117 END IF;
1118 END LOOP;
1119 ELSE
1120 -- Check if the Charge transactions for the Person, Fee Type and Fee Period combination
1121 -- have the Error Account flag set.
1122 l_b_chr_err_account := igs_fi_wav_utils_002.check_chg_error_account (
1123 p_n_person_id => p_n_person_id,
1124 p_v_fee_type => l_rec_wav_pgms.target_fee_type,
1125 p_v_fee_cal_type => p_v_fee_cal_type,
1126 p_n_fee_ci_seq_number => p_n_fee_ci_seq_number
1127 );
1128 IF (l_b_chr_err_account=1) THEN
1129 log_to_fnd(p_v_module => 'create_waivers',
1130 p_v_string => 'Charges with Error Account exist. Waivers cannot be created');
1131 x_return_status := 'E';
1132 RETURN;
1133 END IF;
1134 igs_fi_wav_utils_001.apply_waivers(p_n_person_id => p_n_person_id,
1135 p_v_fee_cal_type => p_v_fee_cal_type,
1136 p_n_fee_ci_seq_number => p_n_fee_ci_seq_number,
1137 p_v_waiver_name => l_rec_wav_pgms.waiver_name,
1138 p_v_target_fee_type => l_rec_wav_pgms.target_fee_type,
1139 p_v_adj_fee_type => l_rec_wav_pgms.adjustment_fee_type,
1140 p_v_waiver_method_code => l_rec_wav_pgms.waiver_method_code,
1141 p_v_waiver_mode_code => l_rec_wav_pgms.waiver_mode_code,
1142 p_n_source_credit_id => l_rec_crd.credit_id,
1143 p_n_source_invoice_id => null,
1144 p_v_currency_cd => p_v_currency_cd,
1145 p_d_gl_date => p_d_gl_date,
1146 x_return_status => x_return_status);
1147 IF x_return_status <> 'S' THEN
1148 log_to_fnd(p_v_module => 'proc_manual_waiver_adj',
1149 p_v_string => ' Procedure igs_fi_wav_utils_001.apply_waivers errored out'
1150 );
1151 x_return_status := 'E';
1152 RETURN;
1153 END IF;
1154 END IF;
1155 END LOOP;
1156 END LOOP;
1157
1158 END proc_manual_waiver_adj;
1159
1160 PROCEDURE log_to_fnd (
1161 p_v_module IN VARCHAR2,
1162 p_v_string IN VARCHAR2
1163 ) AS
1164 ------------------------------------------------------------------
1165 --Created by : Sanil Madathil, Oracle IDC
1166 --Date created: 31 August 2005
1167 --
1168 -- Purpose:
1169 -- Invoked : from within API
1170 -- Function : Private procedure for logging all the statement level
1171 -- messages
1172 -- Parameters : p_v_module : IN parameter. Required.
1173 -- p_v_string : IN parameter. Required.
1174 --
1175 --
1176 --Known limitations/enhancements and/or remarks:
1177 --
1178 --Change History:
1179 --Who When What
1180 ------------------------------------------------------------------
1181 BEGIN
1182
1183 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1184 fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_fi_prc_waivers.'||p_v_module, p_v_string);
1185 END IF;
1186 END log_to_fnd;
1187
1188 PROCEDURE validate_params( p_n_person_id IN hz_parties.party_id%TYPE,
1189 p_n_person_grp_id IN igs_pe_persid_group.group_id%TYPE,
1190 p_v_fee_cal IN VARCHAR2,
1191 p_v_waiver_name IN IGS_FI_WAIVER_PGMS.WAIVER_NAME%TYPE,
1192 p_v_fee_type IN IGS_FI_WAIVER_PGMS.TARGET_FEE_TYPE%TYPE,
1193 p_d_gl_date IN VARCHAR2,
1194 p_v_fee_cal_type OUT NOCOPY igs_ca_inst.cal_type%TYPE,
1195 p_n_fee_ci_sequence_number OUT NOCOPY igs_ca_inst.sequence_number%TYPE,
1196 p_v_ld_cal_type OUT NOCOPY igs_ca_inst.cal_type%TYPE,
1197 p_n_ld_ci_sequence_number OUT NOCOPY igs_ca_inst.sequence_number%TYPE,
1198 p_val_status OUT NOCOPY BOOLEAN) IS
1199 ------------------------------------------------------------------
1200 --Created by :Gurpreet Singh , Oracle India (in)
1201 --Date created: 08-Aug, 2005
1202 --
1203 --Purpose: To Validate the input parameters and log messages to the
1204 -- Log File.
1205 --
1206 --
1207 --Known limitations/enhancements and/or remarks:
1208 --
1209 --Change History:
1210 --Who When What
1211 -------------------------------------------------------------------
1212 CURSOR cur_chk_per_grp_id(cp_n_person_grp_id IGS_PE_PERSID_GROUP_V.group_id%TYPE)
1213 IS
1214 SELECT 'X'
1215 FROM igs_pe_persid_group_v
1216 WHERE group_id = cp_n_person_grp_id;
1217
1218 CURSOR cur_chk_waiver_name(cp_v_fee_cal_type IGS_FI_WAIVER_PGMS.FEE_CAL_TYPE%TYPE,
1219 cp_n_fee_ci_seq_nbr IGS_FI_WAIVER_PGMS.FEE_CI_SEQUENCE_NUMBER%TYPE,
1220 cp_v_waiver_name IGS_FI_WAIVER_PGMS.WAIVER_NAME%TYPE)
1221 IS
1222 SELECT fwp.fee_cal_type,
1223 fwp.fee_ci_sequence_number,
1224 fwp.waiver_name,
1225 fwp.waiver_method_code,
1226 fwp.target_fee_type
1227 FROM igs_fi_waiver_pgms fwp
1228 WHERE fwp.fee_cal_type = cp_v_fee_cal_type
1229 AND fwp.fee_ci_sequence_number = cp_n_fee_ci_seq_nbr
1230 AND fwp.waiver_name = cp_v_waiver_name;
1231
1232 CURSOR cur_chk_fee_type(cp_v_fee_cal_type IGS_FI_WAIVER_PGMS.FEE_CAL_TYPE%TYPE,
1233 cp_n_fee_ci_seq_nbr IGS_FI_WAIVER_PGMS.FEE_CI_SEQUENCE_NUMBER%TYPE,
1234 cp_v_target_fee_type IGS_FI_WAIVER_PGMS.TARGET_FEE_TYPE%TYPE)
1235 IS
1236 SELECT 'X'
1237 FROM igs_fi_waiver_pgms fwp
1238 WHERE fwp.fee_cal_type = cp_v_fee_cal_type
1239 AND fwp.fee_ci_sequence_number = cp_n_fee_ci_seq_nbr
1240 AND fwp.target_fee_type = cp_v_target_fee_type;
1241
1242 l_rec_cur_chk_waiver_name cur_chk_waiver_name%ROWTYPE;
1243
1244 l_v_valid_person VARCHAR2(1);
1245 l_v_dummy VARCHAR2(1);
1246 l_b_parameter_val_status BOOLEAN;
1247
1248 l_v_ld_cal_type igs_ca_inst.cal_type%TYPE;
1249 l_n_ld_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
1250 l_v_message_name fnd_new_messages.message_name%TYPE;
1251
1252 l_b_return_stat BOOLEAN;
1253 l_v_closing_status igs_fi_gl_periods_v.closing_status%TYPE;
1254
1255 l_v_fee_cal_type igs_ca_inst.cal_type%TYPE;
1256 l_n_fee_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
1257
1258 BEGIN
1259 l_b_parameter_val_status := TRUE;
1260
1261 --validate person id
1262 IF p_n_person_id IS NOT NULL THEN
1263 l_v_valid_person := igs_fi_gen_007.validate_person(p_person_id => p_n_person_id);
1264 IF l_v_valid_person = 'N' THEN
1265 fnd_message.set_name('IGS','IGS_FI_INVALID_PERSON');
1266 fnd_file.put_line(fnd_file.log,fnd_message.get);
1267 l_b_parameter_val_status := FALSE;
1268 END IF;
1269 END IF;
1270
1271 --validate person grp id
1272 IF p_n_person_grp_id IS NOT NULL THEN
1273 OPEN cur_chk_per_grp_id(p_n_person_grp_id);
1274 FETCH cur_chk_per_grp_id into l_v_dummy;
1275 IF cur_chk_per_grp_id%NOTFOUND THEN
1276 CLOSE cur_chk_per_grp_id;
1277 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
1278 fnd_message.set_token('PARAMETER',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PERSON_GROUP')||': '||p_n_person_id);
1279 fnd_file.put_line(fnd_file.log,fnd_message.get);
1280 l_b_parameter_val_status := FALSE;
1281 ELSE
1282 CLOSE cur_chk_per_grp_id;
1283 END IF;
1284 END IF;
1285
1286 --person number and person grp id both cannot be provided.
1287 IF p_n_person_id IS NOT NULL AND p_n_person_grp_id IS NOT NULL THEN
1288 fnd_message.set_name('IGS','IGS_FI_NO_PERS_PGRP');
1289 fnd_file.put_line(fnd_file.log,fnd_message.get);
1290 l_b_parameter_val_status := FALSE;
1291 END IF;
1292
1293 IF p_v_fee_cal IS NOT NULL THEN
1294 l_v_fee_cal_type := RTRIM(SUBSTR(p_v_fee_cal,1,10));
1295 l_n_fee_ci_sequence_number := TO_NUMBER(RTRIM(SUBSTR(p_v_fee_cal,12,8)));
1296
1297 IF igs_fi_crdapi_util.validate_cal_inst( p_v_cal_type => l_v_fee_cal_type,
1298 p_n_ci_sequence_number => l_n_fee_ci_sequence_number,
1299 p_v_s_cal_cat => 'FEE') THEN
1300 -- Call To The Procedure To Check Whether The Fee Calendar Instance Has
1301 -- One To One Relation With Load Calendar Instance
1302
1303 igs_fi_crdapi_util.validate_fci_lci_reln(p_v_fee_cal_type => l_v_fee_cal_type,
1304 p_n_fee_ci_sequence_number => l_n_fee_ci_sequence_number,
1305 p_v_ld_cal_type => l_v_ld_cal_type ,
1306 p_n_ld_ci_sequence_number => l_n_ld_ci_sequence_number ,
1307 p_v_message_name => l_v_message_name ,
1308 p_b_return_stat =>l_b_return_stat);
1309 IF NOT l_b_return_stat THEN
1310 fnd_message.set_name('IGS',l_v_message_name);
1311 fnd_file.put_line(fnd_file.log,fnd_message.get);
1312 l_b_parameter_val_status := FALSE;
1313 END IF;
1314 ELSE
1315 -- The Message 'Invalid Fee Period Parameters Passed To The Process.' Is Logged If
1316 -- The Function Returns False.
1317 fnd_message.set_name('IGS','IGS_FI_FCI_NOTFOUND');
1318 fnd_file.put_line(fnd_file.log,fnd_message.get);
1319 l_b_parameter_val_status := FALSE;
1320 END IF;
1321 END IF;
1322
1323 --validate waiver name
1324 IF p_v_waiver_name IS NOT NULL THEN
1325 IF p_v_fee_cal IS NOT NULL THEN
1326 OPEN cur_chk_waiver_name(l_v_fee_cal_type, l_n_fee_ci_sequence_number, p_v_waiver_name);
1327 FETCH cur_chk_waiver_name INTO l_rec_cur_chk_waiver_name;
1328 IF cur_chk_waiver_name%NOTFOUND THEN
1329 FND_MESSAGE.SET_NAME('IGS','IGS_FI_WPRG_FTYP_NOT_EXIST');
1330 fnd_file.put_line(fnd_file.log,fnd_message.get);
1331 l_b_parameter_val_status := FALSE;
1332 END IF;
1333 CLOSE cur_chk_waiver_name;
1334
1335 IF l_rec_cur_chk_waiver_name.WAIVER_METHOD_CODE <> 'COMP_RULE' THEN
1336 FND_MESSAGE.SET_NAME('IGS','IGS_FI_WAV_CAT_INVALID');
1337 fnd_file.put_line(fnd_file.log,fnd_message.get);
1338 l_b_parameter_val_status := FALSE;
1339 END IF;
1340 END IF;
1341
1342 IF p_v_fee_type IS NOT NULL THEN
1343 IF l_rec_cur_chk_waiver_name.TARGET_FEE_TYPE <> p_v_fee_type THEN
1344 FND_MESSAGE.SET_NAME('IGS','IGS_FI_WAV_FEE_CAL_INST');
1345 fnd_file.put_line(fnd_file.log,fnd_message.get);
1346 l_b_parameter_val_status := FALSE;
1347 END IF;
1348 END IF;
1349
1350 END IF;
1351
1352 --validate fee type
1353 IF p_v_fee_type IS NOT NULL THEN
1354 IF p_v_fee_cal IS NOT NULL THEN
1355 OPEN cur_chk_fee_type(l_v_fee_cal_type, l_n_fee_ci_sequence_number, p_v_fee_type);
1356 FETCH cur_chk_fee_type INTO l_v_dummy;
1357 IF cur_chk_fee_type%NOTFOUND THEN
1358 FND_MESSAGE.SET_NAME('IGS','IGS_FI_WAV_FEE_CAL_INST');
1359 fnd_file.put_line(fnd_file.log,fnd_message.get);
1360 l_b_parameter_val_status := FALSE;
1361 END IF;
1362
1363 END IF;
1364 END IF;
1365
1366 -- To Validate The Parameter Gl Date
1367 IF p_d_gl_date IS NULL THEN
1368 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INSUFFICIENT_PARAMETER');
1369 fnd_file.put_line(fnd_file.log,fnd_message.get);
1370 l_b_parameter_val_status := FALSE;
1371 ELSE
1372 igs_fi_gen_gl.get_period_status_for_date(p_d_date => igs_ge_date.igsdate(p_d_gl_date),
1373 p_v_closing_status => l_v_closing_status,
1374 p_v_message_name => l_v_message_name);
1375 IF l_v_message_name IS NOT NULL THEN
1376 fnd_message.set_name('IGS',l_v_message_name);
1377 fnd_file.put_line(fnd_file.log,fnd_message.get);
1378 l_b_parameter_val_status := FALSE;
1379 END IF;
1380
1381 IF l_v_closing_status NOT IN ('O','F') THEN
1382 fnd_message.set_name('IGS','IGS_FI_INVALID_GL_DATE');
1383 fnd_message.set_token('GL_DATE',p_d_gl_date);
1384 fnd_file.put_line(fnd_file.log,fnd_message.get);
1385 l_b_parameter_val_status := FALSE;
1386 END IF;
1387
1388 END IF;
1389
1390 --Assign The Values To The Out Parameters.
1391 p_v_fee_cal_type := l_v_fee_cal_type;
1392 p_n_fee_ci_sequence_number :=l_n_fee_ci_sequence_number;
1393 p_v_ld_cal_type := l_v_ld_cal_type;
1394 p_n_ld_ci_sequence_number := l_n_ld_ci_sequence_number;
1395 p_val_status := l_b_parameter_val_status;
1396
1397 END validate_params;
1398
1399 PROCEDURE log_result(p_n_person_id IN hz_parties.party_id%TYPE,
1400 p_v_waiver_name IN IGS_FI_WAIVER_PGMS.WAIVER_NAME%TYPE,
1401 p_v_fee_type IN IGS_FI_WAIVER_PGMS.TARGET_FEE_TYPE%TYPE,
1402 p_n_waiver_amount IN NUMBER,
1403 p_v_status IN VARCHAR2) IS
1404 ------------------------------------------------------------------
1405 --Created by :Gurpreet Singh , Oracle India (in)
1406 --Date created: 08-Aug, 2005
1407 --
1408 --Purpose: To log Failure or Success of process waivers to the Concurrent Log file.
1409 --
1410 --
1411 --Known limitations/enhancements and/or remarks:
1412 --
1413 --Change History:
1414 --Who When What
1415 -------------------------------------------------------------------
1416 BEGIN
1417 --Logging Person Number.
1418 fnd_message.set_name('IGS','IGS_FI_END_DATE');
1419 fnd_message.set_token('END_DATE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PERSON')||': '||igs_fi_gen_008.get_party_number(p_n_person_id));
1420 fnd_file.put_line(fnd_file.log,fnd_message.get);
1421
1422 --Logging Waiver Program Name.
1423 fnd_message.set_name('IGS','IGS_FI_END_DATE');
1424 fnd_message.set_token('END_DATE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','WAIVER_NAME')||': '||p_v_waiver_name);
1425 fnd_file.put_line(fnd_file.log,fnd_message.get);
1426
1427 --Logging Fee Type
1428 fnd_message.set_name('IGS','IGS_FI_END_DATE');
1429 fnd_message.set_token('END_DATE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FEE_TYPE')||': '||p_v_fee_type);
1430 fnd_file.put_line(fnd_file.log,fnd_message.get);
1431
1432 IF p_v_status = 'SUCCESS' THEN
1433 --Logging Waiver Amount
1434 fnd_message.set_name('IGS','IGS_FI_END_DATE');
1435 fnd_message.set_token('END_DATE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','WAIVER_AMOUNT')||': '||p_n_waiver_amount);
1436 fnd_file.put_line(fnd_file.log,fnd_message.get);
1437 END IF;
1438
1439 --Logging Status
1440 fnd_message.set_name('IGS','IGS_FI_END_DATE');
1441 fnd_message.set_token('END_DATE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','STATUS')||': '||igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',p_v_status));
1442 fnd_file.put_line(fnd_file.log,fnd_message.get);
1443
1444 END log_result;
1445
1446 PROCEDURE log_in_parameters(p_n_person_id IN hz_parties.party_id%TYPE,
1447 p_n_person_grp_id IN igs_pe_persid_group.group_id%TYPE,
1448 p_v_fee_cal IN VARCHAR2,
1449 p_v_waiver_name IN IGS_FI_WAIVER_PGMS.WAIVER_NAME%TYPE,
1450 p_v_fee_type IN IGS_FI_WAIVER_PGMS.TARGET_FEE_TYPE%TYPE,
1451 p_d_gl_date IN VARCHAR2) IS
1452 ------------------------------------------------------------------
1453 --Created by :Gurpreet Singh , Oracle India (in)
1454 --Date created: 08-Aug, 2005
1455 --
1456 --Purpose: To log Inbound Parameters to the Log File.
1457 --
1458 --
1459 --Known limitations/enhancements and/or remarks:
1460 --
1461 --Change History:
1462 --Who When What
1463 -------------------------------------------------------------------
1464 --Cursor For Getting The Alternate Code For The Fee Period.
1465 CURSOR c_get_alt_code(cp_v_cal_type igs_ca_inst.cal_type%TYPE,
1466 cp_n_sequence_number igs_ca_inst.sequence_number%TYPE) IS
1467 SELECT alternate_code
1468 FROM igs_ca_inst_all
1469 WHERE cal_type = cp_v_cal_type
1470 AND sequence_number = cp_n_sequence_number;
1471
1472 l_c_alt_code_desc c_get_alt_code%ROWTYPE;
1473
1474 --Cursor For Getting The Group Code For The Group Id
1475 CURSOR c_get_person_grp (c_group_id igs_pe_persid_group_v.group_id%TYPE) IS
1476 SELECT group_cd
1477 FROM igs_pe_persid_group_v
1478 WHERE group_id = c_group_id;
1479
1480 l_c_get_person_grp c_get_person_grp%ROWTYPE;
1481
1482 BEGIN
1483
1484 --Logging of all the Parameter to the Log File.
1485
1486 --Logging Fee Period.
1487 OPEN c_get_alt_code(cp_v_cal_type => RTRIM(SUBSTR(p_v_fee_cal,1,10)),
1488 cp_n_sequence_number => TO_NUMBER(RTRIM(SUBSTR(p_v_fee_cal,12,8))));
1489
1490 FETCH c_get_alt_code INTO l_c_alt_code_desc;
1491 CLOSE c_get_alt_code;
1492 g_v_alternatecode := l_c_alt_code_desc.alternate_code;
1493 fnd_message.set_name('IGS','IGS_FI_END_DATE');
1494 fnd_message.set_token('END_DATE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FEE_PERIOD')||': '||l_c_alt_code_desc.alternate_code);
1495 fnd_file.put_line(fnd_file.log,fnd_message.get);
1496
1497 --Logging Person Number.
1498 fnd_message.set_name('IGS','IGS_FI_END_DATE');
1499 fnd_message.set_token('END_DATE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PERSON')||': '||igs_fi_gen_008.get_party_number(p_n_person_id));
1500 fnd_file.put_line(fnd_file.log,fnd_message.get);
1501
1502 --Logging Person Group.
1503 OPEN c_get_person_grp(p_n_person_grp_id);
1504 FETCH c_get_person_grp INTO l_c_get_person_grp;
1505 CLOSE c_get_person_grp;
1506 fnd_message.set_name('IGS','IGS_FI_END_DATE');
1507 fnd_message.set_token('END_DATE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PERSON_GROUP')||': '||l_c_get_person_grp.group_cd);
1508 fnd_file.put_line(fnd_file.log,fnd_message.get);
1509
1510 --Logging Waiver Program Name.
1511 fnd_message.set_name('IGS','IGS_FI_END_DATE');
1512 fnd_message.set_token('END_DATE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','WAIVER_NAME')||': '||p_v_waiver_name);
1513 fnd_file.put_line(fnd_file.log,fnd_message.get);
1514
1515 --Logging Fee Type
1516 fnd_message.set_name('IGS','IGS_FI_END_DATE');
1517 fnd_message.set_token('END_DATE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FEE_TYPE')||': '||p_v_fee_type);
1518 fnd_file.put_line(fnd_file.log,fnd_message.get);
1519
1520 --Logging GL Date.
1521 fnd_message.set_name('IGS','IGS_FI_END_DATE');
1522 fnd_message.set_token('END_DATE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','GL_DATE')||': '||p_d_gl_date);
1523 fnd_file.put_line(fnd_file.log,fnd_message.get);
1524
1525 END log_in_parameters;
1526
1527
1528 PROCEDURE process_waivers( errbuf OUT NOCOPY VARCHAR2,
1529 retcode OUT NOCOPY NUMBER,
1530 p_person_id IN hz_parties.party_id%TYPE,
1531 p_person_grp_id IN igs_pe_persid_group.group_id%TYPE,
1532 p_fee_cal IN VARCHAR2,
1533 p_waiver_name IN IGS_FI_WAIVER_PGMS.WAIVER_NAME%TYPE,
1534 p_fee_type IN IGS_FI_WAIVER_PGMS.TARGET_FEE_TYPE%TYPE,
1535 p_gl_date IN VARCHAR2 ) IS
1536 ------------------------------------------------------------------
1537 --Created by :Gurpreet Singh, Oracle India (in)
1538 --Date created: 08-Aug-2005
1539 --
1540 --Purpose: - Invoked to create the waiver transactions on the student's account
1541 --
1542 --
1543 --Known limitations/enhancements and/or remarks:
1544 --
1545 --Change History:
1546 --Who When What
1547 --abshriva 4-May-2006 Bug 5178077: Introduced igs_ge_gen_003.set_org_id
1548 -------------------------------------------------------------------
1549 TYPE person_grp_ref_cur_type IS REF CURSOR;
1550 c_ref_person_grp person_grp_ref_cur_type;
1551
1552 l_n_person_id hz_parties.party_id%TYPE;
1553 l_dynamic_sql VARCHAR2(32767);
1554 l_v_status VARCHAR2(1);
1555 l_v_manage_acc igs_fi_control_all.manage_accounts%TYPE;
1556 l_v_message_name fnd_new_messages.message_name%TYPE;
1557
1558 -- Out Parameters from the Process_Waivers procedure.
1559 l_b_recs_found BOOLEAN;
1560 l_v_return_status VARCHAR2(1);
1561 l_b_validate_parm_status BOOLEAN;
1562 l_v_fee_cal_type igs_ca_inst.cal_type%TYPE;
1563 l_n_fee_ci_seq_number igs_ca_inst.sequence_number%TYPE;
1564 l_v_load_cal_type igs_ca_inst.cal_type%TYPE;
1565 l_n_load_ci_seq_number igs_ca_inst.sequence_number%TYPE;
1566
1567 l_conv_process_run_ind igs_fi_control.conv_process_run_ind%TYPE;
1568 l_n_balance_rule_id igs_fi_balance_rules.balance_rule_id%TYPE;
1569 l_d_last_conversion_date igs_fi_balance_rules.last_conversion_date%TYPE;
1570 l_n_version_number igs_fi_balance_rules.version_number%TYPE;
1571 l_msg VARCHAR2(2000);
1572
1573 l_v_process_mode VARCHAR2(6);
1574 l_n_waiver_amount IGS_FI_CREDITS_ALL.amount%TYPE;
1575
1576 l_v_msg_data VARCHAR2(2000);
1577 l_n_msg_count NUMBER;
1578 l_org_id VARCHAR2(15);
1579 e_resource_busy EXCEPTION;
1580 PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
1581
1582 --Cursor For Getting The Group Code For The Group Id
1583 CURSOR c_get_person_grp (c_group_id igs_pe_persid_group_v.group_id%TYPE) IS
1584 SELECT group_cd
1585 FROM igs_pe_persid_group_v
1586 WHERE group_id = c_group_id;
1587 l_c_get_person_grp c_get_person_grp%ROWTYPE;
1588
1589 --Cursor For Getting The Alternate Code For The Fee Period.
1590 CURSOR c_get_alt_code(cp_v_cal_type igs_ca_inst.cal_type%TYPE,
1591 cp_n_sequence_number igs_ca_inst.sequence_number%TYPE) IS
1592 SELECT alternate_code
1593 FROM igs_ca_inst_all
1594 WHERE cal_type = cp_v_cal_type
1595 AND sequence_number = cp_n_sequence_number;
1596
1597 l_c_alt_code_desc c_get_alt_code%ROWTYPE;
1598
1599 CURSOR cur_get_currency_cd
1600 IS
1601 SELECT currency_cd
1602 FROM IGS_FI_CONTROL;
1603 l_currency_cd IGS_FI_CONTROL.currency_cd%TYPE;
1604
1605 CURSOR cur_get_stdnt_wvr_assgn(cp_v_fee_cal_type IGS_FI_WAIVER_PGMS.FEE_CAL_TYPE%TYPE,
1606 cp_n_fee_ci_seq_nbr IGS_FI_WAIVER_PGMS.FEE_CI_SEQUENCE_NUMBER%TYPE,
1607 cp_v_waiver_name IGS_FI_WAIVER_PGMS.WAIVER_NAME%TYPE,
1608 cp_n_person_id hz_parties.party_id%TYPE,
1609 cp_v_fee_type IGS_FI_WAIVER_PGMS.TARGET_FEE_TYPE%TYPE)
1610 IS
1611 SELECT FWSP.WAIVER_NAME,
1612 FWSP.PERSON_ID,
1613 FWP.TARGET_FEE_TYPE
1614 FROM IGS_FI_WAV_STD_PGMS FWSP,
1615 IGS_FI_WAIVER_PGMS FWP
1616 WHERE FWSP.FEE_CAL_TYPE = cp_v_fee_cal_type
1617 AND FWSP.FEE_CI_SEQUENCE_NUMBER = cp_n_fee_ci_seq_nbr
1618 AND (cp_v_waiver_name IS NULL OR FWSP.WAIVER_NAME = cp_v_waiver_name)
1619 AND (FWSP.PERSON_ID = cp_n_person_id OR cp_n_person_id IS NULL)
1620 AND FWSP.FEE_CAL_TYPE = FWP.FEE_CAL_TYPE
1621 AND FWSP.FEE_CI_SEQUENCE_NUMBER = FWP.FEE_CI_SEQUENCE_NUMBER
1622 AND FWSP.WAIVER_NAME = FWP.WAIVER_NAME
1623 AND (cp_v_fee_type IS NULL OR FWP.TARGET_FEE_TYPE = cp_v_fee_type);
1624
1625 l_rec_c_get_stdnt_wvr_assgn cur_get_stdnt_wvr_assgn%ROWTYPE;
1626
1627 CURSOR cur_get_sys_fee_type(cp_v_target_fee_type IGS_FI_WAIVER_PGMS.TARGET_FEE_TYPE%TYPE)
1628 IS
1629 SELECT FT.S_FEE_TYPE
1630 FROM IGS_FI_FEE_TYPE_ALL FT
1631 WHERE FT.FEE_TYPE = cp_v_target_fee_type;
1632
1633 l_cur_v_target_fee_type cur_get_sys_fee_type%ROWTYPE;
1634 l_b_flag BOOLEAN;
1635
1636 BEGIN
1637 BEGIN
1638 l_org_id := NULL;
1639 igs_ge_gen_003.set_org_id(l_org_id);
1640 EXCEPTION
1641 WHEN OTHERS THEN
1642 fnd_file.put_line (fnd_file.log, fnd_message.get);
1643 retcode:=2;
1644 RETURN;
1645 END;
1646
1647 SAVEPOINT sp_process_waivers;
1648 retcode := 0;
1649 errbuf := NULL;
1650 l_b_flag := FALSE;
1651
1652 -- Log the inbound parameters in the log file using statement level logging
1653 -- Invoke the local procedure to log all the Inbound Parameters in concurrent log File
1654 log_in_parameters(
1655 p_n_person_id => p_person_id,
1656 p_n_person_grp_id => p_person_grp_id,
1657 p_v_fee_cal => p_fee_cal,
1658 p_v_waiver_name => p_waiver_name,
1659 p_v_fee_type => p_fee_type,
1660 p_d_gl_date => igs_ge_date.igsdate(p_gl_date));
1661
1662 --Checking System option value for Manage Accounts.
1663 igs_fi_com_rec_interface.chk_manage_account(p_v_manage_acc => l_v_manage_acc,
1664 p_v_message_name => l_v_message_name);
1665
1666 IF l_v_manage_acc IS NULL OR l_v_manage_acc = 'OTHER' THEN
1667 fnd_file.new_line(fnd_file.log);
1668 fnd_message.set_name('IGS',l_v_message_name);
1669 fnd_file.put_line(fnd_file.log,fnd_message.get);
1670 retcode := 2;
1671 RETURN;
1672 END IF;
1673
1674 -- To Validate The Parameters And Log The Message.
1675 validate_params(p_n_person_id => p_person_id,
1676 p_n_person_grp_id => p_person_grp_id,
1677 p_v_fee_cal => p_fee_cal,
1678 p_v_waiver_name => p_waiver_name,
1679 p_v_fee_type => p_fee_type,
1680 p_d_gl_date => p_gl_date,
1681 p_v_fee_cal_type => l_v_fee_cal_type,
1682 p_n_fee_ci_sequence_number => l_n_fee_ci_seq_number,
1683 p_v_ld_cal_type => l_v_load_cal_type,
1684 p_n_ld_ci_sequence_number => l_n_load_ci_seq_number,
1685 p_val_status => l_b_validate_parm_status);
1686
1687 -- If any of the Parameter validations are failed, return with Error Status
1688 IF NOT l_b_validate_parm_status THEN
1689 retcode :=2;
1690 RETURN;
1691 END IF;
1692
1693 --Check whether Holds Balance Conversion Process is running or not. If yes, Error out.
1694 igs_fi_gen_007.finp_get_conv_prc_run_ind ( p_n_conv_process_run_ind => l_conv_process_run_ind,
1695 p_v_message_name => l_v_message_name );
1696
1697 IF ((l_conv_process_run_ind IS NOT NULL) AND (l_conv_process_run_ind = 1)) THEN
1698 fnd_file.new_line(fnd_file.log);
1699 fnd_message.set_name('IGS','IGS_FI_REASS_BAL_PRC_RUN');
1700 fnd_file.put_line(fnd_file.log,fnd_message.get());
1701 retcode := 2;
1702 RETURN;
1703 END IF;
1704
1705 IF ((l_v_message_name IS NOT NULL) AND (l_conv_process_run_ind IS NULL)) THEN
1706 fnd_file.new_line(fnd_file.log);
1707 fnd_message.set_name('IGS',l_v_message_name);
1708 fnd_file.put_line(fnd_file.log,fnd_message.get());
1709 retcode := 2;
1710 RETURN;
1711 END IF;
1712
1713 --Verify if active balance rule for holds balance type has been setup in the balance rules form.
1714 igs_fi_gen_007.finp_get_balance_rule ( p_v_balance_type => 'HOLDS',
1715 p_v_action => 'ACTIVE',
1716 p_n_balance_rule_id => l_n_balance_rule_id,
1717 p_d_last_conversion_date => l_d_last_conversion_date,
1718 p_n_version_number => l_n_version_number );
1719
1720 IF l_n_version_number = 0 THEN
1721
1722 --no active balance rule exists
1723 fnd_file.new_line(fnd_file.log);
1724 fnd_message.set_name('IGS','IGS_FI_CANNOT_CRT_TXN');
1725 fnd_file.put_line(fnd_file.log,fnd_message.get());
1726 retcode := 2;
1727 RETURN;
1728 END IF;
1729
1730 OPEN cur_get_currency_cd;
1731 FETCH cur_get_currency_cd INTO l_currency_cd;
1732 CLOSE cur_get_currency_cd;
1733
1734 --Process is run for a group of students
1735 IF p_person_grp_id IS NOT NULL AND p_person_id IS NULL THEN
1736
1737 l_dynamic_sql := igs_pe_dynamic_persid_group.igs_get_dynamic_sql(p_person_grp_id,l_v_status );
1738 IF l_v_status <> 'S' THEN
1739
1740 --Log the error message and stop the processing.
1741 fnd_message.set_name('IGF','IGF_AP_INVALID_QUERY');
1742 fnd_file.put_line(fnd_file.log,fnd_message.get);
1743 retcode := 2;
1744 RETURN;
1745 END IF;
1746
1747 OPEN c_ref_person_grp FOR l_dynamic_sql;
1748 -- Looping Across All The Valid Person Ids In The Group.
1749 LOOP
1750 FETCH c_ref_person_grp INTO l_n_person_id;
1751 EXIT WHEN c_ref_person_grp%NOTFOUND;
1752
1753 --get all the student waiver assignments
1754 FOR l_rec_c_get_stdnt_wvr_assgn IN cur_get_stdnt_wvr_assgn(l_v_fee_cal_type,
1755 l_n_fee_ci_seq_number,
1756 p_waiver_name,
1757 l_n_person_id,
1758 p_fee_type)
1759 LOOP
1760 --Get the system fee type associated to the target fee type.
1761 OPEN cur_get_sys_fee_type(l_rec_c_get_stdnt_wvr_assgn.target_fee_type);
1762 FETCH cur_get_sys_fee_type INTO l_cur_v_target_fee_type;
1763 CLOSE cur_get_sys_fee_type;
1764 IF l_cur_v_target_fee_type.s_fee_type IN ('TUTNFEE','AUDIT','OTHER') THEN
1765 l_v_process_mode := 'ACTUAL';
1766 ELSE
1767 l_v_process_mode := NULL;
1768 END IF;
1769 --create waivers
1770 igs_fi_prc_waivers.create_waivers(p_n_person_id => l_rec_c_get_stdnt_wvr_assgn.person_id,
1771 p_v_fee_type => l_rec_c_get_stdnt_wvr_assgn.target_fee_type,
1772 p_v_fee_cal_type => l_v_fee_cal_type,
1773 p_n_fee_ci_seq_number => l_n_fee_ci_seq_number,
1774 p_v_waiver_name => l_rec_c_get_stdnt_wvr_assgn.waiver_name,
1775 p_v_currency_cd => l_currency_cd,
1776 p_d_gl_date => igs_ge_date.igsdate(p_gl_date),
1777 p_b_init_msg_list => TRUE,
1778 p_validation_level => 0,
1779 p_v_real_time_flag => 'N',
1780 p_v_process_mode => l_v_process_mode,
1781 p_v_career => NULL,
1782 p_v_raise_wf_event => 'Y',
1783 x_waiver_amount => l_n_waiver_amount,
1784 x_return_status => l_v_return_status,
1785 x_msg_count => l_n_msg_count,
1786 x_msg_data => l_v_msg_data);
1787 IF l_v_return_status <> 'S' THEN
1788 fnd_file.new_line(fnd_file.log,2);
1789 --log the failure in the log file for a person in this cycle of group of persons
1790 log_result(p_n_person_id => l_rec_c_get_stdnt_wvr_assgn.person_id,
1791 p_v_waiver_name => l_rec_c_get_stdnt_wvr_assgn.waiver_name,
1792 p_v_fee_type => l_rec_c_get_stdnt_wvr_assgn.target_fee_type,
1793 p_n_waiver_amount => NULL,
1794 p_v_status => 'FAIL');
1795
1796 IF l_n_msg_count = 1 THEN
1797 fnd_message.set_encoded(l_v_msg_data);
1798 fnd_file.put_line(fnd_file.log,fnd_message.get);
1799 ELSE
1800 FOR l_count IN 1 .. l_n_msg_count LOOP
1801 l_msg := fnd_msg_pub.get(p_msg_index => l_count, p_encoded => 'T');
1802 fnd_message.set_encoded(l_msg);
1803 fnd_file.put_line(fnd_file.log,fnd_message.get);
1804 END LOOP;
1805 END IF;
1806 ELSIF l_v_return_status = 'S' THEN
1807 fnd_file.new_line(fnd_file.log,2);
1808 --log the success in the log file for a person in this cycle of group of persons
1809 log_result(p_n_person_id => l_rec_c_get_stdnt_wvr_assgn.person_id,
1810 p_v_waiver_name => l_rec_c_get_stdnt_wvr_assgn.waiver_name,
1811 p_v_fee_type => l_rec_c_get_stdnt_wvr_assgn.target_fee_type,
1812 p_n_waiver_amount => l_n_waiver_amount,
1813 p_v_status => 'SUCCESS');
1814 END IF;
1815
1816 l_b_flag := TRUE;
1817
1818 END LOOP;--end loop of stdnt assignments
1819 END LOOP;--end loop of person grp
1820
1821 END IF;
1822
1823 --Process is run for a particular person or both person grp and person details are not provided.
1824 IF (p_person_id IS NOT NULL) OR (p_person_id IS NULL AND p_person_grp_id IS NULL) THEN
1825
1826 --Get waiver assignemnts.
1827 FOR l_rec_c_get_stdnt_wvr_assgn IN cur_get_stdnt_wvr_assgn(l_v_fee_cal_type,
1828 l_n_fee_ci_seq_number,
1829 p_waiver_name,
1830 p_person_id,
1831 p_fee_type)
1832 LOOP
1833 --Check system fee type associated with target fee type
1834 OPEN cur_get_sys_fee_type(l_rec_c_get_stdnt_wvr_assgn.target_fee_type);
1835 FETCH cur_get_sys_fee_type INTO l_cur_v_target_fee_type;
1836 CLOSE cur_get_sys_fee_type;
1837
1838 IF l_cur_v_target_fee_type.s_fee_type IN ('TUTNFEE','AUDIT','OTHER') THEN
1839 l_v_process_mode := 'ACTUAL';
1840 ELSE
1841 l_v_process_mode := NULL;
1842 END IF;
1843 --calling create waivers.
1844
1845 igs_fi_prc_waivers.create_waivers(p_n_person_id => l_rec_c_get_stdnt_wvr_assgn.person_id,
1846 p_v_fee_type => l_rec_c_get_stdnt_wvr_assgn.target_fee_type,
1847 p_v_fee_cal_type => l_v_fee_cal_type,
1848 p_n_fee_ci_seq_number => l_n_fee_ci_seq_number,
1849 p_v_waiver_name => l_rec_c_get_stdnt_wvr_assgn.waiver_name,
1850 p_v_currency_cd => l_currency_cd,
1851 p_d_gl_date => igs_ge_date.igsdate(p_gl_date),
1852 p_b_init_msg_list => TRUE,
1853 p_validation_level => 0,
1854 p_v_real_time_flag => 'N',
1855 p_v_process_mode => l_v_process_mode,
1856 p_v_career => NULL,
1857 p_v_raise_wf_event => 'Y',
1858 x_waiver_amount => l_n_waiver_amount,
1859 x_return_status => l_v_return_status,
1860 x_msg_count => l_n_msg_count,
1861 x_msg_data => l_v_msg_data);
1862
1863 IF l_v_return_status <> 'S' THEN
1864 fnd_file.new_line(fnd_file.log,2);
1865 --log the failure in the log file.
1866 log_result(p_n_person_id => l_rec_c_get_stdnt_wvr_assgn.person_id,
1867 p_v_waiver_name => l_rec_c_get_stdnt_wvr_assgn.waiver_name,
1868 p_v_fee_type => l_rec_c_get_stdnt_wvr_assgn.target_fee_type,
1869 p_n_waiver_amount => NULL,
1870 p_v_status => 'FAIL');
1871
1872 IF l_n_msg_count = 1 THEN
1873 fnd_message.set_encoded(l_v_msg_data);
1874 fnd_file.put_line(fnd_file.log,fnd_message.get);
1875 ELSE
1876 FOR l_count IN 1 .. l_n_msg_count LOOP
1877 l_msg := fnd_msg_pub.get(p_msg_index => l_count, p_encoded => 'T');
1878 fnd_message.set_encoded(l_msg);
1879 fnd_file.put_line(fnd_file.log,fnd_message.get);
1880 END LOOP;
1881 END IF;
1882 ELSIF l_v_return_status = 'S' THEN
1883 fnd_file.new_line(fnd_file.log,2);
1884 --log the success in the log file
1885 log_result(p_n_person_id => l_rec_c_get_stdnt_wvr_assgn.person_id,
1886 p_v_waiver_name => l_rec_c_get_stdnt_wvr_assgn.waiver_name,
1887 p_v_fee_type => l_rec_c_get_stdnt_wvr_assgn.target_fee_type,
1888 p_n_waiver_amount => l_n_waiver_amount,
1889 p_v_status => 'SUCCESS');
1890 END IF;
1891
1892 l_b_flag := TRUE;
1893
1894 END LOOP;
1895 END IF;
1896
1897 IF NOT l_b_flag THEN
1898 fnd_file.new_line(fnd_file.log);
1899 fnd_message.set_name('IGS','IGS_GE_NO_DATA_FOUND');
1900 fnd_file.put_line(fnd_file.log,fnd_message.get);
1901 END IF;
1902
1903 EXCEPTION
1904 WHEN OTHERS THEN
1905 ROLLBACK TO sp_process_waivers;
1906 retcode := 2;
1907 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION') || ' : ' || SQLERRM;
1908 igs_ge_msg_stack.conc_exception_hndl;
1909
1910 END process_waivers;
1911 END igs_fi_prc_waivers;