1 PACKAGE BODY igs_fi_wav_utils_001 AS
2 /* $Header: IGSFI95B.pls 120.2 2005/11/07 16:08:58 appldev noship $ */
3
4 /******************************************************************
5 Created By : Anji Yedubati
6 Date Created By : 05-JUL-2005
7 Purpose : Waiver Utility Package for the generic routines,
8 get_eligible_waiver_amt and apply_waivers, which are
9 required for waiver processing
10 Created as part of FI234 - Tuition Waivers enh. Bug # 3392095
11
12 Known limitations,enhancements,remarks:
13
14 Change History :
15 WHO WHEN WHAT
16 pathipat 16-Aug-2005 Enh 3392095 - Tuition Waivers Enh
17 Added procedure create_ss_waiver_transactions
18 ***************************************************************** */
19
20 --
21 -- Global Variables Declaration
22 --
23 g_v_app VARCHAR2(3) := 'APP';
24 g_v_unapp VARCHAR2(5) := 'UNAPP';
25
26 --
27 -- Private Procedures Declaration
28 --
29
30 -- Procedure for logging messages at the Statement Level
31 PROCEDURE log_to_fnd (p_v_module IN VARCHAR2,
32 p_v_string IN VARCHAR2);
33
34 -- Procedure to validate the parameters passed to get Eligible Waiver Amount
35 PROCEDURE validate_elg_wavamt_params(
36 p_n_person_id IN igs_pe_person_base_v.person_id%TYPE,
37 p_v_fee_cal_type IN igs_fi_waiver_pgms.fee_cal_type%TYPE,
38 p_n_fee_ci_seq_number IN igs_fi_waiver_pgms.fee_ci_sequence_number%TYPE,
39 p_v_waiver_name IN igs_fi_waiver_pgms.waiver_name%TYPE,
40 p_v_target_fee_type IN igs_fi_waiver_pgms.target_fee_type%TYPE,
41 p_v_waiver_method_code IN igs_fi_waiver_pgms.waiver_method_code%TYPE,
42 p_v_waiver_mode_code IN igs_fi_waiver_pgms.waiver_mode_code%TYPE,
43 p_n_source_invoice_id IN igs_fi_inv_int_all.invoice_id%TYPE,
44 x_return_status OUT NOCOPY VARCHAR2);
45
46 -- Procedure to validate the parameters passed to Apply Waivers procedure
47 PROCEDURE validate_applywav_params(
48 p_n_person_id IN igs_pe_person_base_v.person_id%TYPE,
49 p_v_fee_cal_type IN igs_fi_waiver_pgms.fee_cal_type%TYPE,
50 p_n_fee_ci_seq_number IN igs_fi_waiver_pgms.fee_ci_sequence_number%TYPE,
51 p_v_waiver_name IN igs_fi_waiver_pgms.waiver_name%TYPE,
52 p_v_target_fee_type IN igs_fi_waiver_pgms.target_fee_type%TYPE,
53 p_v_adj_fee_type IN igs_fi_waiver_pgms.adjustment_fee_type%TYPE,
54 p_v_waiver_method_code IN igs_fi_waiver_pgms.waiver_method_code%TYPE,
55 p_v_waiver_mode_code IN igs_fi_waiver_pgms.waiver_mode_code%TYPE,
56 p_n_source_credit_id IN igs_fi_credits_all.credit_id%TYPE,
57 p_n_source_invoice_id IN igs_fi_inv_int_all.invoice_id%TYPE,
58 p_v_currency_cd IN igs_fi_inv_int_all.currency_cd%TYPE,
59 p_d_gl_date IN igs_fi_invln_int.gl_date%TYPE,
60 x_return_status OUT NOCOPY VARCHAR2);
61
62 -- Procedure to Apply the Waiver Credit against the Waiver Adjustment Charges
63 PROCEDURE process_wavadj_charges(
64 p_n_person_id IN igs_pe_person_base_v.person_id%TYPE,
65 p_v_fee_cal_type IN igs_fi_waiver_pgms.fee_cal_type%TYPE,
66 p_n_fee_ci_seq_number IN igs_fi_waiver_pgms.fee_ci_sequence_number%TYPE,
67 p_v_adj_fee_type IN igs_fi_waiver_pgms.adjustment_fee_type%TYPE,
68 p_v_waiver_name IN igs_fi_waiver_pgms.waiver_name%TYPE,
69 p_n_credit_id IN igs_fi_credits_all.credit_id%TYPE,
70 p_d_gl_date IN igs_fi_invln_int.gl_date%TYPE,
71 p_n_credit_amount IN OUT NOCOPY NUMBER,
72 x_return_status OUT NOCOPY VARCHAR2);
73
74 -- Procedure to Apply the balance Waiver Credit against the charges in the
75 -- student account except the Rentension Charges
76 PROCEDURE process_stdnt_charges(
77 p_n_invoice_id IN igs_fi_inv_int_all.invoice_id%TYPE,
78 p_n_person_id IN igs_pe_person_base_v.person_id%TYPE,
79 p_v_fee_cal_type IN igs_fi_waiver_pgms.fee_cal_type%TYPE,
80 p_n_fee_ci_seq_number IN igs_fi_waiver_pgms.fee_ci_sequence_number%TYPE,
81 p_v_target_fee_type IN igs_fi_waiver_pgms.target_fee_type%TYPE,
82 p_n_credit_id IN igs_fi_credits_all.credit_id%TYPE,
83 p_d_gl_date IN igs_fi_invln_int.gl_date%TYPE,
84 p_n_credit_amount IN OUT NOCOPY NUMBER,
85 x_return_status OUT NOCOPY VARCHAR2);
86
87 -- Procedure to Un apply existing applications for credits other than
88 -- Negative Charge Adjustmnet, Waiver, Enrollment Deposit and Other Depost types
89 -- and apply the unapplied charges against the balance Waiver Credit
90 PROCEDURE adjust_stdnt_charges(
91 p_n_invoice_id IN igs_fi_inv_int_all.invoice_id%TYPE,
92 p_n_credit_id IN igs_fi_credits_all.credit_id%TYPE,
93 p_d_gl_date IN igs_fi_invln_int.gl_date%TYPE,
94 p_n_credit_amount IN OUT NOCOPY NUMBER,
95 x_return_status OUT NOCOPY VARCHAR2);
96
97 -- Procedure to create the Waiver Adjustment Charge and
98 -- apply the charge against the waiver credit
99 PROCEDURE create_wavadj_charge(
100 p_n_person_id IN igs_pe_person_base_v.person_id%TYPE,
101 p_v_fee_cal_type IN igs_fi_waiver_pgms.fee_cal_type%TYPE,
102 p_n_fee_ci_seq_number IN igs_fi_waiver_pgms.fee_ci_sequence_number%TYPE,
103 p_v_waiver_name IN igs_fi_waiver_pgms.waiver_name%TYPE,
104 p_v_adj_fee_type IN igs_fi_waiver_pgms.adjustment_fee_type%TYPE,
105 p_n_credit_id IN igs_fi_credits_all.credit_id%TYPE,
106 p_v_currency_cd IN igs_fi_inv_int_all.currency_cd%TYPE,
107 p_n_waiver_amt IN igs_fi_inv_int_all.invoice_amount%TYPE,
108 p_d_gl_date IN igs_fi_invln_int.gl_date%TYPE,
109 x_return_status OUT NOCOPY VARCHAR2);
110
111 -- Procedure to apply the Waiver Adjustment Charges for a Waiver Credit,
112 -- which has some positive amount due exists
113 PROCEDURE process_due_wavadj_charges(
114 p_n_source_credit_id IN igs_fi_credits_all.credit_id%TYPE,
115 p_n_person_id IN igs_pe_person_base_v.person_id%TYPE,
116 p_v_fee_cal_type IN igs_fi_waiver_pgms.fee_cal_type%TYPE,
117 p_n_fee_ci_seq_number IN igs_fi_waiver_pgms.fee_ci_sequence_number%TYPE,
118 p_v_waiver_name IN igs_fi_waiver_pgms.waiver_name%TYPE,
119 p_v_adj_fee_type IN igs_fi_waiver_pgms.adjustment_fee_type%TYPE,
120 p_d_gl_date IN igs_fi_invln_int.gl_date%TYPE,
121 x_return_status OUT NOCOPY VARCHAR2);
122
123 --
124 -- Public Procedures
125 --
126
127 PROCEDURE get_eligible_waiver_amt(
128 p_n_person_id IN igs_pe_person_base_v.person_id%TYPE,
129 p_v_fee_cal_type IN igs_fi_waiver_pgms.fee_cal_type%TYPE,
130 p_n_fee_ci_seq_number IN igs_fi_waiver_pgms.fee_ci_sequence_number%TYPE,
131 p_v_waiver_name IN igs_fi_waiver_pgms.waiver_name%TYPE,
132 p_v_target_fee_type IN igs_fi_waiver_pgms.target_fee_type%TYPE,
133 p_v_waiver_method_code IN igs_fi_waiver_pgms.waiver_method_code%TYPE,
134 p_v_waiver_mode_code IN igs_fi_waiver_pgms.waiver_mode_code%TYPE,
135 p_n_source_invoice_id IN igs_fi_inv_int_all.invoice_id%TYPE,
136 x_return_status OUT NOCOPY VARCHAR2,
137 x_eligible_amount OUT NOCOPY NUMBER ) AS
138 /******************************************************************
139 Created By : Anji Yedubati
140 Date Created By : 11-JUL-2005
141 Purpose : To calculate the Eligible amount that can be actually waived for the
142 Student, Fee Calendar, Waiver Program and Target Fee Type combination.
143 Created as part of Tuition Waivers Enhancment Bug # 3392095
144
145 Known limitations,enhancements,remarks:
146
147 Change History :
148 WHO WHEN WHAT
149 AYEDUBAT 22-AUG-05 Fixed the review comments on the new object
150 ***************************************************************** */
151
152 -- Fecth the Charges in the Student account except Rentension Charges
153 -- for a combination of Person, Fee Type, Fee Period or based on Invoice ID
154 CURSOR stdnt_charges_cur (
155 cp_n_person_id igs_fi_inv_int_all.person_id%TYPE,
156 cp_v_target_fee_type igs_fi_inv_int_all.fee_type%TYPE,
157 cp_v_fee_cal_type igs_fi_inv_int_all.fee_cal_type%TYPE,
158 cp_n_fee_ci_seq_num igs_fi_inv_int_all.fee_ci_sequence_number%TYPE,
159 cp_n_invoice_id igs_fi_inv_int_all.invoice_id%TYPE) IS
160 SELECT inv.invoice_id, inv.invoice_amount
161 FROM igs_fi_inv_int_all inv
162 WHERE inv.person_id = cp_n_person_id
163 AND inv.fee_type = cp_v_target_fee_type
164 AND inv.fee_cal_type = cp_v_fee_cal_type
165 AND inv.fee_ci_sequence_number = cp_n_fee_ci_seq_num
166 AND inv.transaction_type <> 'RETENTION'
167 AND (cp_n_invoice_id IS NULL OR inv.invoice_id = cp_n_invoice_id)
168 ORDER BY inv.invoice_id;
169
170 -- Fecth the Negative Charge Adjustment application details for a Charge
171 CURSOR chgadj_amt_cur (cp_n_invoice_id igs_fi_applications.invoice_id%TYPE) IS
172 SELECT appl.amount_applied
173 FROM igs_fi_applications appl,
174 igs_fi_credits_all crd,
175 igs_fi_cr_types_all cr
176 WHERE appl.invoice_id = cp_n_invoice_id
177 AND appl.credit_id = crd.credit_id
178 AND appl.application_type = 'APP'
179 AND crd.credit_type_id = cr.credit_type_id
180 AND cr.credit_class = 'CHGADJ';
181
182 -- Fecth the Waiver Credit Application records applied to the charges having same target fee type
183 -- and fee calendar and other than the waiver program passed as in parameter to the procedure
184 CURSOR wav_applications_cur(
185 cp_n_person_id igs_fi_inv_int_all.person_id%TYPE,
186 cp_v_target_fee_type igs_fi_inv_int_all.fee_type%TYPE,
187 cp_v_fee_cal_type igs_fi_credits_all.fee_cal_type%TYPE,
188 cp_n_fee_ci_seq_num igs_fi_credits_all.fee_ci_sequence_number%TYPE,
189 cp_n_invoice_id igs_fi_applications.invoice_id%TYPE,
190 cp_v_waiver_name igs_fi_credits_all.waiver_name%TYPE) IS
191 SELECT
192 appl.amount_applied,
193 appl.application_id,
194 appl.credit_id,
195 appl.invoice_id
196 FROM igs_fi_inv_int_all inv,
197 igs_fi_applications appl,
198 igs_fi_credits_all crd,
199 igs_fi_cr_types cr
200 WHERE inv.person_id = cp_n_person_id
201 AND inv.fee_type = cp_v_target_fee_type
202 AND inv.fee_cal_type = cp_v_fee_cal_type
203 AND inv.fee_ci_sequence_number = cp_n_fee_ci_seq_num
204 AND (cp_n_invoice_id IS NULL OR inv.invoice_id = cp_n_invoice_id)
205 AND appl.invoice_id = inv.invoice_id
206 AND appl.credit_id = crd.credit_id
207 AND appl.application_type = 'APP'
208 AND crd.fee_cal_type = cp_v_fee_cal_type
209 AND crd.fee_ci_sequence_number = cp_n_fee_ci_seq_num
210 AND (cp_v_waiver_name IS NULL OR crd.waiver_name <> cp_v_waiver_name)
211 AND crd.status = 'CLEARED'
212 AND crd.credit_type_id = cr.credit_type_id
213 AND cr.credit_class = 'WAIVER'
214 ORDER BY appl.application_id;
215
216 -- Fetch Application records for a given waiver credit
217 -- against which waiver adjustment charge applied
218 CURSOR wavadj_app_cur(cp_n_credit_id igs_fi_applications.credit_id%TYPE,
219 cp_v_fee_cal_type igs_fi_inv_int_all.fee_cal_type%TYPE,
220 cp_n_fee_ci_seq_num igs_fi_inv_int_all.fee_ci_sequence_number%TYPE,
221 cp_n_invoice_id igs_fi_inv_int_all.invoice_id%TYPE) IS
222 SELECT appl.amount_applied
223 FROM igs_fi_applications appl,
224 igs_fi_inv_int_all inv
225 WHERE appl.credit_id = cp_n_credit_id
226 AND appl.invoice_id = inv.invoice_id
227 AND appl.application_type = 'APP'
228 AND inv.fee_cal_type = cp_v_fee_cal_type
229 AND inv.fee_ci_sequence_number = cp_n_fee_ci_seq_num
230 AND inv.transaction_type = 'WAIVER_ADJ'
231 AND EXISTS( SELECT '1'
232 FROM igs_fi_credits_all crd,
233 igs_fi_applications unappl
234 WHERE crd.credit_id = appl.credit_id
235 AND unappl.credit_id = crd.credit_id
236 AND unappl.invoice_id = cp_n_invoice_id
237 AND unappl.application_type = 'UNAPP'
238 AND unappl.amount_applied = - appl.amount_applied );
239
240 l_n_chgadj_amt IGS_FI_APPLICATIONS.amount_applied%TYPE;
241 l_n_wavadj_amt IGS_FI_APPLICATIONS.amount_applied%TYPE;
242 l_n_net_chg_amount IGS_FI_APPLICATIONS.amount_applied%TYPE;
243 l_n_net_waiver_amt IGS_FI_APPLICATIONS.amount_applied%TYPE;
244
245 BEGIN
246
247 -- Invoke the procedure to validate the Inbound Parameters
248 validate_elg_wavamt_params(
249 p_n_person_id,
250 p_v_fee_cal_type,
251 p_n_fee_ci_seq_number,
252 p_v_waiver_name,
253 p_v_target_fee_type,
254 p_v_waiver_method_code,
255 p_v_waiver_mode_code,
256 p_n_source_invoice_id,
257 x_return_status);
258
259 -- If the Parameter validation is failed then return the calling procedure with error status
260 IF x_return_status = 'E' THEN
261 log_to_fnd('get_eligible_waiver_amt','validate_elg_wavamt_params procedure is Failed');
262 x_eligible_amount := NULL;
263 RETURN;
264 END IF;
265
266 log_to_fnd('get_eligible_waiver_amt','validate_elg_wavamt_params procedure is Successfull');
267
268 -- Fetch all the Student Charges except Retention Charges and calculate the Net Charge Amount.
269 -- Net Charge Amount = Invoice Amount - Negative Charge Adjustment Amount
270 l_n_net_chg_amount := 0;
271 FOR stdnt_charges_rec IN stdnt_charges_cur (p_n_person_id,p_v_target_fee_type,p_v_fee_cal_type,
272 p_n_fee_ci_seq_number, p_n_source_invoice_id) LOOP
273
274 log_to_fnd('get_eligible_waiver_amt','Processing the Invoice ID: '||stdnt_charges_rec.invoice_id);
275
276 -- Loop through the negative charge adjustment application records for the context
277 -- Charge transaction and cumulate the Amount Applied
278 l_n_chgadj_amt := 0;
279 FOR chgadj_amt_rec IN chgadj_amt_cur (stdnt_charges_rec.invoice_id) LOOP
280 l_n_chgadj_amt := l_n_chgadj_amt + chgadj_amt_rec.amount_applied;
281 END LOOP;
282
283 log_to_fnd('get_eligible_waiver_amt','Negative Charge Adjustment amount for the Invoice = '||l_n_chgadj_amt);
284
285 -- Calculate the difference between the Invoice Amount and
286 -- negative charge adjustment amount applied for the context Charge record
287 l_n_net_chg_amount := l_n_net_chg_amount + (stdnt_charges_rec.invoice_amount - l_n_chgadj_amt);
288
289 END LOOP;
290
291 log_to_fnd('get_eligible_waiver_amt','Net Charge Amount = '||l_n_net_chg_amount);
292
293 -- Loop through the Waiver Credit Application records other than the Waiver Program
294 -- passed as inbound parameter against which Waiver Adjustment charge is applied
295 -- Net Waiver Amount = Waiver Amount applied - Waiver Adjustment Amount applied
296 l_n_net_waiver_amt := 0;
297 FOR wav_applications_rec IN wav_applications_cur (p_n_person_id,p_v_target_fee_type,p_v_fee_cal_type,
298 p_n_fee_ci_seq_number,p_n_source_invoice_id,p_v_waiver_name) LOOP
299
300 log_to_fnd('get_eligible_waiver_amt','Processing the Waiver Application ID: '||wav_applications_rec.application_id);
301
302 -- Loop through the Waiver Adjustment Charges for the Context Waiver Application
303 -- and cumulate the Amount Applied
304 l_n_wavadj_amt := 0;
305 FOR wavadj_app_rec IN wavadj_app_cur (wav_applications_rec.credit_id, p_v_fee_cal_type,
306 p_n_fee_ci_seq_number, wav_applications_rec.invoice_id) LOOP
307 l_n_wavadj_amt := l_n_wavadj_amt + wavadj_app_rec.amount_applied;
308 END LOOP;
309
310 log_to_fnd('get_eligible_waiver_amt','Waiver Adjustment Charge amount for the Application = '||l_n_wavadj_amt);
311
312 -- Calculate the difference between waiver amount applied and
313 -- Waiver Adjustment amount applied for the context Waiver Application
314 l_n_net_waiver_amt := l_n_net_waiver_amt + (wav_applications_rec.amount_applied - l_n_wavadj_amt);
315
316 END LOOP;
317
318 log_to_fnd('get_eligible_waiver_amt','Net Waiver Amount = '||l_n_net_waiver_amt);
319
320 -- Calculate the Eligible Waiver Amount as the difference between
321 -- net Charge Amount (l_n_net_chg_amount) and net Waiver Amount(l_n_net_waiver_amt)
322 x_eligible_amount := l_n_net_chg_amount - l_n_net_waiver_amt;
323
324 -- Return the Success Status
325 x_return_status := 'S';
326
327 log_to_fnd('get_eligible_waiver_amt','Eligible Waiver Amount = '||x_eligible_amount);
328
329 EXCEPTION
330 WHEN OTHERS THEN
331
332 -- Set the values to the OUT variables
333 x_eligible_amount := NULL;
334 x_return_status := 'E';
335
336 -- Log the SQLERRM message
337 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
338 fnd_log.string(fnd_log.level_exception,'igs.plsql.igs_fi_wav_utils_001.get_eligible_waiver_amt.exception','sqlerrm ' || SQLERRM);
339 END IF;
340
341 END get_eligible_waiver_amt;
342
343
344 PROCEDURE apply_waivers(
345 p_n_person_id IN igs_pe_person_base_v.person_id%TYPE,
346 p_v_fee_cal_type IN igs_fi_waiver_pgms.fee_cal_type%TYPE,
347 p_n_fee_ci_seq_number IN igs_fi_waiver_pgms.fee_ci_sequence_number%TYPE,
348 p_v_waiver_name IN igs_fi_waiver_pgms.waiver_name%TYPE,
349 p_v_target_fee_type IN igs_fi_waiver_pgms.target_fee_type%TYPE,
350 p_v_adj_fee_type IN igs_fi_waiver_pgms.adjustment_fee_type%TYPE,
351 p_v_waiver_method_code IN igs_fi_waiver_pgms.waiver_method_code%TYPE,
352 p_v_waiver_mode_code IN igs_fi_waiver_pgms.waiver_mode_code%TYPE,
353 p_n_source_credit_id IN igs_fi_credits_all.credit_id%TYPE,
354 p_n_source_invoice_id IN igs_fi_inv_int_all.invoice_id%TYPE,
355 p_v_currency_cd IN igs_fi_inv_int_all.currency_cd%TYPE,
356 p_d_gl_date IN igs_fi_invln_int.gl_date%TYPE,
357 x_return_status OUT NOCOPY VARCHAR2 ) AS
358 /******************************************************************
359 Created By : Anji Yedubati
360 Date Created By : 11-JUL-2005
361 Purpose : Procedure to apply the Waiver Credit against the Student Charges
362 Created as part of Tuition Waivers Enhancment Bug # 3392095
363
364 Known limitations,enhancements,remarks:
365
366 Change History :
367 WHO WHEN WHAT
368 ***************************************************************** */
369
370 -- Declare used defined exception to raise when apply waiver credit fails
371 apply_wav_fail EXCEPTION;
372
373 -- Fetch the credit details for a Credit Id
374 CURSOR chglvl_waiver_crdits_cur(cp_credit_id igs_fi_credits_all.credit_id%TYPE) IS
375 SELECT crd.credit_id,
376 crd.unapplied_amount
377 FROM igs_fi_credits_all crd
378 WHERE crd.credit_id = cp_credit_id
379 AND NVL(crd.unapplied_amount, 0) > 0;
380
381 -- Fetch waiver credit records for a combination of Person, Fee Period and Waiver Name
382 CURSOR feelvl_waiver_crdits_cur(
383 cp_credit_id igs_fi_credits_all.credit_id%TYPE,
384 cp_n_person_id igs_fi_credits_all.party_id%TYPE,
385 cp_v_fee_cal_type igs_fi_credits_all.fee_cal_type%TYPE,
386 cp_n_fee_ci_seq_num igs_fi_credits_all.fee_ci_sequence_number%TYPE,
387 cp_v_waiver_name igs_fi_credits_all.waiver_name%TYPE) IS
388 SELECT crd.credit_id,
389 crd.unapplied_amount
390 FROM igs_fi_credits_all crd
391 WHERE (cp_credit_id IS NULL OR crd.credit_id = cp_credit_id)
392 AND crd.party_id = cp_n_person_id
393 AND crd.fee_cal_type = cp_v_fee_cal_type
394 AND crd.fee_ci_sequence_number = cp_n_fee_ci_seq_num
395 AND crd.waiver_name = cp_v_waiver_name
396 AND NVL(crd.unapplied_amount, 0) > 0 ;
397
398 -- Fetch the change records other than Re
399 -- for a combination of Person, Fee Type and Fee Period
400 CURSOR stnt_charges_cur(
401 cp_n_person_id igs_fi_inv_int_all.person_id%TYPE,
402 cp_v_target_fee_type igs_fi_inv_int_all.fee_type%TYPE,
403 cp_v_fee_cal_type igs_fi_credits_all.fee_cal_type%TYPE,
404 cp_n_fee_ci_seq_num igs_fi_credits_all.fee_ci_sequence_number%TYPE) IS
405 SELECT inv.invoice_id,
406 inv.invoice_amount_due
407 FROM igs_fi_inv_int_all inv,
408 igs_fi_invln_int_all invln
409 WHERE inv.person_id = cp_n_person_id
410 AND inv.fee_type = cp_v_target_fee_type
411 AND inv.fee_cal_type = cp_v_fee_cal_type
412 AND inv.fee_ci_sequence_number = cp_n_fee_ci_seq_num
413 AND inv.transaction_type NOT IN ('RETENTION','WAIVER_ADJ')
414 AND invln.invoice_id = inv.invoice_id
415 AND NVL(invln.error_account,'N') = 'N'
416 ORDER BY inv.invoice_id;
417
418 l_v_return_status VARCHAR2(1);
419 l_n_credit_amount IGS_FI_CREDITS_ALL.unapplied_amount%TYPE;
420
421 BEGIN
422
423 -- Create the Save Point
424 SAVEPOINT apply_waivers_sp;
425
426 -- Log the inbound parameters in the log file using statement level logging
427 log_to_fnd('apply_waivers','Inbound Parameters to the procedure');
428 log_to_fnd('apply_waivers','p_n_person_id='||p_n_person_id);
429 log_to_fnd('apply_waivers','p_v_fee_cal_type='||p_v_fee_cal_type);
430 log_to_fnd('apply_waivers','p_n_fee_ci_seq_number='||p_n_fee_ci_seq_number);
431 log_to_fnd('apply_waivers','p_v_waiver_name='||p_v_waiver_name);
432 log_to_fnd('apply_waivers','p_v_target_fee_type='||p_v_target_fee_type);
433 log_to_fnd('apply_waivers','p_v_adj_fee_type='||p_v_adj_fee_type);
434 log_to_fnd('apply_waivers','p_v_waiver_method_code='||p_v_waiver_method_code);
435 log_to_fnd('apply_waivers','p_v_waiver_mode_code='||p_v_waiver_mode_code);
436 log_to_fnd('apply_waivers','p_n_source_credit_id='||p_n_source_credit_id);
437 log_to_fnd('apply_waivers','p_n_source_invoice_id='||p_n_source_invoice_id);
438
439 -- Initialize the Local Variables
440 x_return_status := 'S';
441 l_n_credit_amount := 0;
442
443 --
444 -- Validate the Inbound Parameters
445 --
446 validate_applywav_params(
447 p_n_person_id,
448 p_v_fee_cal_type,
449 p_n_fee_ci_seq_number,
450 p_v_waiver_name,
451 p_v_target_fee_type,
452 p_v_adj_fee_type,
453 p_v_waiver_method_code,
454 p_v_waiver_mode_code,
455 p_n_source_credit_id,
456 p_n_source_invoice_id,
457 p_v_currency_cd,
458 p_d_gl_date,
459 x_return_status);
460
461 -- If the Parameter validations are failed, then Retunr to calling procedure with Error Status
462 IF x_return_status = 'E' THEN
463 log_to_fnd('apply_waivers','validate_applywav_params procedure is Failed' );
464 RETURN;
465 END IF;
466 log_to_fnd('apply_waivers','validate_applywav_params procedure is Successfull' );
467
468 --
469 -- Apply Waivers processing is based on the Mode of Waiver
470 -- If Mode of Waiver is Charge Level, apply the Source Waiver Credit against Source Charge
471 -- If Mode of Waiver is Charge Level, apply all the Student Waiver Credits
472 --
473 -- Mode of Waiver is Charge Level
474 --
475 IF (p_v_waiver_mode_code = 'CHARGE_LEVEL') THEN
476
477 log_to_fnd('apply_waivers','Charge Level Processing');
478
479 -- Fetch the details of the Waiver Credit record, p_n_source_credit_id
480 FOR chglvl_waiver_crdits_rec IN chglvl_waiver_crdits_cur(p_n_source_credit_id) LOOP
481
482 -- Initlaize the Waiver Credit Amount local variable with the Unapplied Amount
483 l_n_credit_amount := chglvl_waiver_crdits_rec.unapplied_amount;
484 l_v_return_status := NULL;
485
486 log_to_fnd('apply_waivers','Processing Credit ID: '||chglvl_waiver_crdits_rec.credit_id||' Credit Amount='||l_n_credit_amount);
487
488 -- Invoke the local procedure to apply the source Waiver Credit
489 -- againt Waiver Adjustment Charge records
490 process_wavadj_charges(
491 p_n_person_id => p_n_person_id,
492 p_v_fee_cal_type => p_v_fee_cal_type,
493 p_n_fee_ci_seq_number => p_n_fee_ci_seq_number,
494 p_v_adj_fee_type => p_v_adj_fee_type,
495 p_v_waiver_name => p_v_waiver_name,
496 p_n_credit_id => chglvl_waiver_crdits_rec.credit_id,
497 p_d_gl_date => p_d_gl_date,
498 p_n_credit_amount => l_n_credit_amount,
499 x_return_status => l_v_return_status );
500
501 -- If the procedure is returned with Error status then exit the processing
502 -- with Error Status, otherwise continue with the processing
503 IF l_v_return_status = 'E' THEN
504 log_to_fnd('apply_waivers','Procedure, process_wavadj_charges is failed');
505 RAISE apply_wav_fail;
506 END IF;
507 log_to_fnd('apply_waivers','Procedure, process_wavadj_charges completed successfully. Balance Credit Amount='||l_n_credit_amount);
508
509 -- If the balance Waiver Credit Amount is greater then 0
510 IF l_n_credit_amount > 0 THEN
511
512 -- Invoke the local procedure to apply the source Waiver Credit againt the source charge
513 process_stdnt_charges(
514 p_n_invoice_id => p_n_source_invoice_id,
515 p_n_person_id => p_n_person_id,
516 p_v_fee_cal_type => p_v_fee_cal_type,
517 p_n_fee_ci_seq_number => p_n_fee_ci_seq_number,
518 p_v_target_fee_type => p_v_target_fee_type,
519 p_n_credit_id => chglvl_waiver_crdits_rec.credit_id,
520 p_d_gl_date => p_d_gl_date,
521 p_n_credit_amount => l_n_credit_amount,
522 x_return_status => l_v_return_status );
523
524 -- If the procedure is returned with Error status then exit the processing
525 -- with Error Status, otherwise continue with the processing
526 IF l_v_return_status = 'E' THEN
527 log_to_fnd('apply_waivers','Procedure, process_stdnt_charges is failed');
528 RAISE apply_wav_fail;
529 END IF;
530 log_to_fnd('apply_waivers','Procedure, process_stdnt_charges is completed successfully. Balance Credit Amount='||l_n_credit_amount);
531
532 END IF;
533
534 -- If the balance Waiver Credit Amount is greater then 0
535 IF l_n_credit_amount > 0 THEN
536
537 -- Invoke the local procedure to un apply the application of source charge, if any
538 -- and then apply the same charge againt the source Waiver Credit
539 adjust_stdnt_charges(
540 p_n_invoice_id => p_n_source_invoice_id,
541 p_n_credit_id => chglvl_waiver_crdits_rec.credit_id,
542 p_d_gl_date => p_d_gl_date,
543 p_n_credit_amount => l_n_credit_amount,
544 x_return_status => l_v_return_status );
545
546 -- If the procedure is returned with Error status then exit the processing
547 -- with Error Status, otherwise continue with the processing
548 IF l_v_return_status = 'E' THEN
549 log_to_fnd('apply_waivers','Procedure, adjust_stdnt_charges is failed');
550 RAISE apply_wav_fail;
551 END IF;
552 log_to_fnd('apply_waivers','Procedure, adjust_stdnt_charges completed successfully. Balance Credit Amount='||l_n_credit_amount);
553
554 END IF;
555
556 -- If the balance Waiver Credit Amount is greater then 0
557 IF l_n_credit_amount > 0 THEN
558
559 -- Invoke the local procedure to create Waiver Adjustment Charge for the
560 -- balance credit amount and apply the against the source Waiver Credit
561 create_wavadj_charge(
562 p_n_person_id => p_n_person_id,
563 p_v_fee_cal_type => p_v_fee_cal_type,
564 p_n_fee_ci_seq_number => p_n_fee_ci_seq_number,
565 p_v_waiver_name => p_v_waiver_name,
566 p_n_credit_id => chglvl_waiver_crdits_rec.credit_id,
567 p_v_adj_fee_type => p_v_adj_fee_type,
568 p_v_currency_cd => p_v_currency_cd,
569 p_n_waiver_amt => l_n_credit_amount,
570 p_d_gl_date => p_d_gl_date,
571 x_return_status => l_v_return_status );
572
573 -- If the procedure is returned with Error status then exit the processing
574 -- with Error Status, otherwise continue with the processing
575 IF l_v_return_status = 'E' THEN
576 log_to_fnd('apply_waivers','Procedure, create_wavadj_charge is failed');
577 RAISE apply_wav_fail;
578 END IF;
579 log_to_fnd('apply_waivers','Procedure, create_wavadj_charge is successfull');
580 l_n_credit_amount := 0;
581
582 END IF;
583
584 END LOOP; -- End of Processing the Waiver Credit Record
585
586 --
587 -- Mode of Waiver is Fee Level
588 --
589 ELSIF (p_v_waiver_mode_code = 'FEE_LEVEL' OR p_v_waiver_method_code = 'COMP_RULE') THEN
590
591 -- Log the message to indicate Fee Level processing or Computation Rule based processing
592 IF p_v_waiver_mode_code = 'FEE_LEVEL' THEN
593 log_to_fnd('apply_waivers','Fee Level Processing');
594 ELSE
595 log_to_fnd('apply_waivers','Computation Rule based Processing');
596 END IF;
597
598 -- Fetch the Waiver Credit details either for the Inbound Waiver Credit record, p_n_source_credit_id or
599 -- for a combination of Person, Fee Period and Waiver Name
600 FOR feelvl_waiver_crdits_rec IN feelvl_waiver_crdits_cur(p_n_source_credit_id,p_n_person_id,
601 p_v_fee_cal_type,p_n_fee_ci_seq_number, p_v_waiver_name ) LOOP
602
603 -- Initlaize the Waiver Credit Amount local variable with the Unapplied Amount
604 l_n_credit_amount := feelvl_waiver_crdits_rec.unapplied_amount;
605 l_v_return_status := NULL;
606
607 log_to_fnd('apply_waivers','Processing Credit ID: '||feelvl_waiver_crdits_rec.credit_id||' Credit Amount='||l_n_credit_amount);
608
609 -- Invoke the local procedure to apply the context Waiver Credit
610 -- againt Waiver Adjustment Charge records
611 process_wavadj_charges(
612 p_n_person_id => p_n_person_id,
613 p_v_fee_cal_type => p_v_fee_cal_type,
614 p_n_fee_ci_seq_number => p_n_fee_ci_seq_number,
615 p_v_adj_fee_type => p_v_adj_fee_type,
616 p_v_waiver_name => p_v_waiver_name,
617 p_n_credit_id => feelvl_waiver_crdits_rec.credit_id,
618 p_d_gl_date => p_d_gl_date,
619 p_n_credit_amount => l_n_credit_amount,
620 x_return_status => l_v_return_status);
621
622 -- If the procedure is returned with Error status then exit the processing
623 -- with Error Status, otherwise continue with the processing
624 IF l_v_return_status = 'E' THEN
625 log_to_fnd('apply_waivers','Procedure, process_wavadj_charges is failed');
626 RAISE apply_wav_fail;
627 END IF;
628 log_to_fnd('apply_waivers','Procedure, process_wavadj_charges is completed successfully. Balance Credit Amount='||l_n_credit_amount);
629
630 -- If the balance Waiver Credit Amount is greater then 0
631 IF l_n_credit_amount > 0 THEN
632
633 -- Invoke the local procedure to apply the context Waiver Credit
634 -- againt Student Charges except the Retension Charges
635 process_stdnt_charges(
636 p_n_invoice_id => NULL,
637 p_n_person_id => p_n_person_id,
638 p_v_fee_cal_type => p_v_fee_cal_type,
639 p_n_fee_ci_seq_number => p_n_fee_ci_seq_number,
640 p_v_target_fee_type => p_v_target_fee_type,
641 p_n_credit_id => feelvl_waiver_crdits_rec.credit_id,
642 p_d_gl_date => p_d_gl_date,
643 p_n_credit_amount => l_n_credit_amount,
644 x_return_status => l_v_return_status );
645
646 -- If the procedure is returned with Error status then exit the processing
647 -- with Error Status, otherwise continue with the processing
648 IF l_v_return_status = 'E' THEN
649 log_to_fnd('apply_waivers','Procedure, process_stdnt_charges is failed');
650 RAISE apply_wav_fail;
651 END IF;
652 log_to_fnd('apply_waivers','Procedure, process_stdnt_charges is completed successfully. Balance Credit Amount='||l_n_credit_amount);
653
654 END IF;
655
656 -- If the balance Waiver Credit Amount is greater then 0
657 IF l_n_credit_amount > 0 THEN
658
659 FOR stnt_charges_rec IN stnt_charges_cur(p_n_person_id, p_v_target_fee_type,
660 p_v_fee_cal_type, p_n_fee_ci_seq_number) LOOP
661
662 log_to_fnd('apply_waivers','Processing the Invoice ID:'||stnt_charges_rec.invoice_id);
663
664 -- Invoke the local procedure to un apply the applications aginst which the conext charge is already applied
665 -- and then apply the same charge againt the source Waiver Credit
666 adjust_stdnt_charges(
667 p_n_invoice_id => stnt_charges_rec.invoice_id,
668 p_n_credit_id => feelvl_waiver_crdits_rec.credit_id,
669 p_d_gl_date => p_d_gl_date,
670 p_n_credit_amount => l_n_credit_amount,
671 x_return_status => l_v_return_status );
672
673 -- If the procedure is returned with Error status then exit the processing
674 -- with Error Status, otherwise continue with the processing
675 IF l_v_return_status = 'E' THEN
676 log_to_fnd('apply_waivers','Procedure adjust_stdnt_charges is failed');
677 RAISE apply_wav_fail;
678 END IF;
679
680 log_to_fnd('apply_waivers','Procedure adjust_stdnt_charges is completed successfully. Balance Credit Amount='||l_n_credit_amount);
681 -- If the credit amount is less than or equal to 0 then exit the loop
682 IF l_n_credit_amount <= 0 THEN
683 EXIT;
684 END IF;
685
686 END LOOP;
687
688 END IF;
689
690 -- If the balance Waiver Credit Amount is greater then 0
691 IF l_n_credit_amount > 0 THEN
692
693 -- Invoke the local procedure to create Waiver Adjustment Charge for the
694 -- balance credit amount and apply the against the context Waiver Credit
695 create_wavadj_charge(
696 p_n_person_id => p_n_person_id,
697 p_v_fee_cal_type => p_v_fee_cal_type,
698 p_n_fee_ci_seq_number => p_n_fee_ci_seq_number,
699 p_v_waiver_name => p_v_waiver_name,
700 p_n_credit_id => feelvl_waiver_crdits_rec.credit_id,
701 p_v_adj_fee_type => p_v_adj_fee_type,
702 p_v_currency_cd => p_v_currency_cd,
703 p_n_waiver_amt => l_n_credit_amount,
704 p_d_gl_date => p_d_gl_date,
705 x_return_status => l_v_return_status );
706
707 -- If the procedure is returned with Error status then exit the processing
708 -- with Error Status, otherwise continue with the processing
709 IF l_v_return_status = 'E' THEN
710 log_to_fnd('apply_waivers','Procedure create_wavadj_charge is failed');
711 RAISE apply_wav_fail;
712 END IF;
713 log_to_fnd('apply_waivers','Procedure create_wavadj_charge is completed successfully');
714 l_n_credit_amount := 0;
715
716 END IF;
717
718 END LOOP; -- End of Processing the current Waiver Credit Record
719
720 END IF; -- End of Fee Level or Computation Rule processing
721
722 log_to_fnd('apply_waivers','Calling the local Procedure process_due_wavadj_charges');
723 -- Invoke the local procedure to apply Waiver Adjustment Charges, if any Waiver Credits
724 -- with un applied amount. If still Waiver Adjustment Charge to apply is pending then
725 -- un apply the waiver credits which are applied against charges other than Waiver and
726 -- apply against the Waiver Adjustment Charge
727 process_due_wavadj_charges(
728 p_n_source_credit_id => p_n_source_credit_id,
729 p_n_person_id => p_n_person_id,
730 p_v_fee_cal_type => p_v_fee_cal_type,
731 p_n_fee_ci_seq_number => p_n_fee_ci_seq_number,
732 p_v_waiver_name => p_v_waiver_name,
733 p_v_adj_fee_type => p_v_adj_fee_type,
734 p_d_gl_date => p_d_gl_date,
735 x_return_status => l_v_return_status);
736
737 -- If the procedure is returned with Error status then exit the processing
738 -- with Error Status, otherwise continue with the processing
739 IF l_v_return_status = 'E' THEN
740 log_to_fnd('apply_waivers','Procedure process_due_wavadj_charges is failed');
741 RAISE apply_wav_fail;
742 END IF;
743 log_to_fnd('apply_waivers','Procedure process_due_wavadj_charges is completed successfully');
744
745 EXCEPTION
746
747 WHEN apply_wav_fail THEN
748 ROLLBACK TO apply_waivers_sp;
749 x_return_status := 'E';
750
751 WHEN OTHERS THEN
752 ROLLBACK TO apply_waivers_sp;
753 x_return_status := 'E';
754
755 -- Log the SQLERRM message
756 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
757 fnd_log.string(fnd_log.level_exception,'igs.plsql.igs_fi_wav_utils_001.apply_waivers.exception','sqlerrm ' || SQLERRM);
758 END IF;
759
760 END apply_waivers;
761
762
763 --
764 -- Local Procedures Definition
765 --
766
767 PROCEDURE validate_elg_wavamt_params(
768 p_n_person_id IN igs_pe_person_base_v.person_id%TYPE,
769 p_v_fee_cal_type IN igs_fi_waiver_pgms.fee_cal_type%TYPE,
770 p_n_fee_ci_seq_number IN igs_fi_waiver_pgms.fee_ci_sequence_number%TYPE,
771 p_v_waiver_name IN igs_fi_waiver_pgms.waiver_name%TYPE,
772 p_v_target_fee_type IN igs_fi_waiver_pgms.target_fee_type%TYPE,
773 p_v_waiver_method_code IN igs_fi_waiver_pgms.waiver_method_code%TYPE,
774 p_v_waiver_mode_code IN igs_fi_waiver_pgms.waiver_mode_code%TYPE,
775 p_n_source_invoice_id IN igs_fi_inv_int_all.invoice_id%TYPE,
776 x_return_status OUT NOCOPY VARCHAR2) AS
777 /******************************************************************
778 Created By : Anji Yedubati
779 Date Created By : 19-JUL-2005
780 Purpose : This Procedure is called from get_eligible_waiver_amt to
781 validate the inbound parameters
782 Known limitations,enhancements,remarks:
783
784 Change History :
785 WHO WHEN WHAT
786 ***************************************************************** */
787 BEGIN
788
789 -- Initialize the OUT Parameter to Sucess
790 x_return_status := 'S';
791
792 -- Check all the mandatory parameters are passed to the procedure
793 IF p_n_person_id IS NULL OR p_v_fee_cal_type IS NULL OR p_n_fee_ci_seq_number IS NULL OR
794 p_v_waiver_name IS NULL OR p_v_target_fee_type IS NULL THEN
795
796 log_to_fnd('validate_elg_wavamt_params','Mandatory Parameters are Null');
797 x_return_status := 'E';
798
799 END IF;
800
801 -- Waiver Creation Method should be Manual or Computation Rule
802 IF p_v_waiver_method_code IS NULL OR
803 p_v_waiver_method_code NOT IN ('MANUAL','COMP_RULE') THEN
804
805 log_to_fnd('validate_elg_wavamt_params','p_v_waiver_method_code should be passed as MANUAL or COMP_RULE');
806 x_return_status := 'E';
807
808 -- If Waiver Creation Method is Computaion Rule
809 ELSIF p_v_waiver_method_code = 'COMP_RULE' THEN
810 IF p_v_waiver_mode_code IS NOT NULL THEN
811 log_to_fnd('validate_elg_wavamt_params','p_v_waiver_mode_code IS NOT NULL for Computation Waiver Method');
812 x_return_status := 'E';
813 END IF;
814
815 -- If Waiver Creation Method is Manual
816 ELSIF p_v_waiver_method_code = 'MANUAL' THEN
817
818 -- Waiver Mode should be Charge Level or Fee Level
819 IF p_v_waiver_mode_code IS NULL OR
820 p_v_waiver_mode_code NOT IN ('CHARGE_LEVEL','FEE_LEVEL') THEN
821
822 log_to_fnd('validate_elg_wavamt_params','p_v_waiver_mode_code should be passed as CHARGE_LEVEL or FEE_LEVEL');
823 x_return_status := 'E';
824
825 -- If Waiver Mode should be Charge Level then Source Invoice ID should be passed
826 ELSIF p_v_waiver_mode_code = 'CHARGE_LEVEL' THEN
827
828 IF p_n_source_invoice_id IS NULL THEN
829 log_to_fnd('validate_elg_wavamt_params','p_n_source_invoice_id IS NULL for Charge Level Waiver Mode');
830 x_return_status := 'E';
831 END IF;
832
833 -- Waiver Mode is Fee Level then Source Invoice ID should not be passed
834 ELSIF p_v_waiver_mode_code = 'FEE_LEVEL' THEN
835
836 IF p_n_source_invoice_id IS NOT NULL THEN
837 log_to_fnd('validate_elg_wavamt_params','p_n_source_invoice_id IS NOT NULL for Fee Level Waiver Mode');
838 x_return_status := 'E';
839 END IF;
840
841 END IF;
842
843 END IF;
844
845 END validate_elg_wavamt_params;
846
847 PROCEDURE validate_applywav_params(
848 p_n_person_id IN igs_pe_person_base_v.person_id%TYPE,
849 p_v_fee_cal_type IN igs_fi_waiver_pgms.fee_cal_type%TYPE,
850 p_n_fee_ci_seq_number IN igs_fi_waiver_pgms.fee_ci_sequence_number%TYPE,
851 p_v_waiver_name IN igs_fi_waiver_pgms.waiver_name%TYPE,
852 p_v_target_fee_type IN igs_fi_waiver_pgms.target_fee_type%TYPE,
853 p_v_adj_fee_type IN igs_fi_waiver_pgms.adjustment_fee_type%TYPE,
854 p_v_waiver_method_code IN igs_fi_waiver_pgms.waiver_method_code%TYPE,
855 p_v_waiver_mode_code IN igs_fi_waiver_pgms.waiver_mode_code%TYPE,
856 p_n_source_credit_id IN igs_fi_credits_all.credit_id%TYPE,
857 p_n_source_invoice_id IN igs_fi_inv_int_all.invoice_id%TYPE,
858 p_v_currency_cd IN igs_fi_inv_int_all.currency_cd%TYPE,
859 p_d_gl_date IN igs_fi_invln_int.gl_date%TYPE,
860 x_return_status OUT NOCOPY VARCHAR2) AS
861 /******************************************************************
862 Created By : Anji Yedubati
863 Date Created By : 19-JUL-2005
864 Purpose : This Procedure is called from apply_waivers to
865 validate the inbound parameters
866 Known limitations,enhancements,remarks:
867
868 Change History :
869 WHO WHEN WHAT
870 ***************************************************************** */
871 BEGIN
872
873 -- Initialize the OUT Parameter to Sucess
874 x_return_status := 'S';
875
876 -- Check all the mandatory parameters are passed to the procedure
877 IF p_n_person_id IS NULL OR p_v_fee_cal_type IS NULL OR p_n_fee_ci_seq_number IS NULL OR p_v_waiver_name IS NULL OR
878 p_v_target_fee_type IS NULL OR p_v_adj_fee_type IS NULL OR p_v_currency_cd IS NULL OR p_d_gl_date IS NULL THEN
879
880 log_to_fnd('validate_applywav_params','Mandatory Parameters are Null');
881 x_return_status := 'E';
882
883 END IF;
884
885 -- Waiver Creation Method should be Manual or Computation Rule
886 IF p_v_waiver_method_code IS NULL OR
887 p_v_waiver_method_code NOT IN ('MANUAL','COMP_RULE') THEN
888
889 log_to_fnd('validate_applywav_params','p_v_waiver_method_code should be passed as MANUAL or COMP_RULE');
890 x_return_status := 'E';
891
892 -- If Waiver Creation Method is Computaion Rule
893 ELSIF p_v_waiver_method_code = 'COMP_RULE' THEN
894 IF p_v_waiver_mode_code IS NOT NULL THEN
895 log_to_fnd('validate_applywav_params','p_v_waiver_mode_code IS NOT NULL for Computation Waiver Method');
896 x_return_status := 'E';
897 END IF;
898
899 -- If Waiver Creation Method is Manual
900 ELSIF p_v_waiver_method_code = 'MANUAL' THEN
901
902 -- Waiver Mode should be Charge Level or Fee Level
903 IF p_v_waiver_mode_code IS NULL OR
904 p_v_waiver_mode_code NOT IN ('CHARGE_LEVEL','FEE_LEVEL') THEN
905
906 log_to_fnd('validate_applywav_params','p_v_waiver_mode_code should be passed as CHARGE_LEVEL or FEE_LEVEL');
907 x_return_status := 'E';
908
909 -- If Waiver Mode should be Charge Level then Source Invoice ID should be passed
910 ELSIF p_v_waiver_mode_code = 'CHARGE_LEVEL' THEN
911
912 IF p_n_source_credit_id IS NULL OR p_n_source_invoice_id IS NULL THEN
913 log_to_fnd('validate_applywav_params','p_n_source_credit_id IS NULL or p_n_source_invoice_id IS NULL for Charge Level Waiver Mode');
914 x_return_status := 'E';
915
916 END IF;
917
918 -- Waiver Mode is Fee Level then Source Invoice ID should not be passed
919 ELSIF p_v_waiver_mode_code = 'FEE_LEVEL' THEN
920
921 IF p_n_source_credit_id IS NULL OR p_n_source_invoice_id IS NOT NULL THEN
922 log_to_fnd('validate_applywav_params','p_n_source_credit_id IS NULL or p_n_source_invoice_id IS NOT NULL for Fee Level Waiver Mode');
923 x_return_status := 'E';
924
925 END IF;
926
927 END IF;
928
929 END IF;
930
931 END validate_applywav_params;
932
933 PROCEDURE process_wavadj_charges(
934 p_n_person_id IN igs_pe_person_base_v.person_id%TYPE,
935 p_v_fee_cal_type IN igs_fi_waiver_pgms.fee_cal_type%TYPE,
936 p_n_fee_ci_seq_number IN igs_fi_waiver_pgms.fee_ci_sequence_number%TYPE,
937 p_v_adj_fee_type IN igs_fi_waiver_pgms.adjustment_fee_type%TYPE,
938 p_v_waiver_name IN igs_fi_waiver_pgms.waiver_name%TYPE,
939 p_n_credit_id IN igs_fi_credits_all.credit_id%TYPE,
940 p_d_gl_date IN igs_fi_invln_int.gl_date%TYPE,
941 p_n_credit_amount IN OUT NOCOPY NUMBER,
942 x_return_status OUT NOCOPY VARCHAR2) AS
943 /******************************************************************
944 Created By : Anji Yedubati
945 Date Created By : 19-JUL-2005
946 Purpose : This Procedure is called from apply_waivers to
947 apply the waiver credit againt waiver adjustment charges.
948 Known limitations,enhancements,remarks:
949
950 Change History :
951 WHO WHEN WHAT
952 ***************************************************************** */
953
954 -- Fecth the Waiver adjustment charges having some positive amount due exists
955 -- for the combination of Person, Adjsutment Fee Type, Fee Period and Waiver Name
956 CURSOR waiver_adj_charges_cur(
957 cp_n_person_id igs_fi_inv_int_all.person_id%TYPE,
958 cp_v_adj_fee_type igs_fi_inv_int_all.fee_type%TYPE,
959 cp_v_fee_cal_type igs_fi_credits_all.fee_cal_type%TYPE,
960 cp_n_fee_ci_seq_num igs_fi_credits_all.fee_ci_sequence_number%TYPE,
961 cp_v_waiver_name igs_fi_credits_all.waiver_name%TYPE) IS
962 SELECT inv.invoice_id,
963 inv.invoice_amount_due
964 FROM igs_fi_inv_int_all inv,
965 igs_fi_invln_int_all invln
966 WHERE inv.person_id = cp_n_person_id
967 AND inv.fee_type = cp_v_adj_fee_type
968 AND inv.fee_cal_type = cp_v_fee_cal_type
969 AND inv.fee_ci_sequence_number = cp_n_fee_ci_seq_num
970 AND inv.waiver_name = cp_v_waiver_name
971 AND NVL(inv.invoice_amount_due, 0) > 0
972 AND invln.invoice_id = inv.invoice_id
973 AND NVL(invln.error_account,'N') = 'N'
974 ORDER BY inv.invoice_id;
975
976 l_n_app_id igs_fi_applications.application_id%TYPE;
977 l_n_amount_apply igs_fi_applications.amount_applied%TYPE;
978 l_n_dr_gl_ccid igs_fi_applications.dr_gl_code_ccid%TYPE;
979 l_n_cr_gl_ccid igs_fi_applications.cr_gl_code_ccid%TYPE;
980 l_v_dr_acc_cd igs_fi_applications.dr_account_cd%TYPE;
981 l_v_cr_acc_cd igs_fi_applications.cr_account_cd%TYPE;
982 l_n_unapp_amount igs_fi_credits.unapplied_amount%TYPE;
983 l_n_inv_amt_due igs_fi_inv_int.invoice_amount_due%TYPE;
984 l_v_err_msg VARCHAR2(2000);
985 l_b_status BOOLEAN;
986
987 BEGIN
988
989 x_return_status := 'S';
990
991 -- Loop through the Waiver Adjustment charge records having some positive amount due exists
992 -- for the combination of Person, Adjsutment Fee Type, Fee Period and Waiver Name
993 FOR waiver_adj_charges_rec IN waiver_adj_charges_cur(p_n_person_id,p_v_adj_fee_type,
994 p_v_fee_cal_type,p_n_fee_ci_seq_number,p_v_waiver_name) LOOP
995
996 log_to_fnd('process_wavadj_charges','Processing the Invoice ID: '||waiver_adj_charges_rec.invoice_id);
997
998 -- Calculate the amount to be applied to the Waiver Adjustment charge
999 IF waiver_adj_charges_rec.invoice_amount_due >= p_n_credit_amount THEN
1000 l_n_amount_apply := p_n_credit_amount;
1001 ELSE
1002 l_n_amount_apply := waiver_adj_charges_rec.invoice_amount_due;
1003 END IF;
1004
1005 -- Invoke the application procedure to create an application
1006 l_n_app_id := NULL;
1007 l_v_err_msg := NULL;
1008 igs_fi_gen_007.create_application(
1009 p_application_id => l_n_app_id,
1010 p_credit_id => p_n_credit_id,
1011 p_invoice_id => waiver_adj_charges_rec.invoice_id,
1012 p_amount_apply => l_n_amount_apply,
1013 p_appl_type => g_v_app,
1014 p_appl_hierarchy_id => NULL,
1015 p_validation => 'Y',
1016 p_dr_gl_ccid => l_n_dr_gl_ccid,
1017 p_cr_gl_ccid => l_n_cr_gl_ccid,
1018 p_dr_account_cd => l_v_dr_acc_cd,
1019 p_cr_account_cd => l_v_cr_acc_cd,
1020 p_unapp_amount => l_n_unapp_amount,
1021 p_inv_amt_due => l_n_inv_amt_due,
1022 p_err_msg => l_v_err_msg,
1023 p_status => l_b_status,
1024 p_d_gl_date => p_d_gl_date);
1025
1026 -- Check the Staus of the Waiver Credit Application
1027 -- If the status of the application is true
1028 IF l_b_status THEN
1029 log_to_fnd(p_v_module => 'process_wavadj_charges',
1030 p_v_string => 'Application record created. Application ID= '||l_n_app_id);
1031
1032 -- Deduct the Waiver credit amount to the extent of applied amount(l_n_amount_apply)
1033 p_n_credit_amount := p_n_credit_amount - l_n_amount_apply;
1034
1035 -- If the balance Waiver credit is zero then return to apply_waivers procedure with success status
1036 IF p_n_credit_amount = 0 THEN
1037 x_return_status := 'S';
1038 RETURN;
1039 END IF;
1040
1041 -- If the status of the application is false
1042 ELSE
1043 -- Log the message and return to the apply_waivers Procedure
1044 log_to_fnd(p_v_module => 'process_wavadj_charges',
1045 p_v_string => 'Error: '||fnd_message.get_string('IGS', l_v_err_msg));
1046
1047 -- Return to apply_waivers procedure with Error status
1048 x_return_status := 'E';
1049 RETURN;
1050
1051 END IF;
1052
1053 END LOOP;
1054
1055 EXCEPTION
1056 WHEN OTHERS THEN
1057
1058 -- Log the SQLERRM message and return to the apply_waivers Procedure
1059 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1060 fnd_log.string(fnd_log.level_exception,'igs.plsql.igs_fi_wav_utils_001.process_wavadj_charges.exception','sqlerrm ' || SQLERRM);
1061 END IF;
1062
1063 x_return_status := 'E';
1064
1065 END process_wavadj_charges;
1066
1067 PROCEDURE process_stdnt_charges(
1068 p_n_invoice_id IN igs_fi_inv_int_all.invoice_id%TYPE,
1069 p_n_person_id IN igs_pe_person_base_v.person_id%TYPE,
1070 p_v_fee_cal_type IN igs_fi_waiver_pgms.fee_cal_type%TYPE,
1071 p_n_fee_ci_seq_number IN igs_fi_waiver_pgms.fee_ci_sequence_number%TYPE,
1072 p_v_target_fee_type IN igs_fi_waiver_pgms.target_fee_type%TYPE,
1073 p_n_credit_id IN igs_fi_credits_all.credit_id%TYPE,
1074 p_d_gl_date IN igs_fi_invln_int.gl_date%TYPE,
1075 p_n_credit_amount IN OUT NOCOPY NUMBER,
1076 x_return_status OUT NOCOPY VARCHAR2) AS
1077 /******************************************************************
1078 Created By : Anji Yedubati
1079 Date Created By : 19-JUL-2005
1080 Purpose : This Procedure is called from apply_waivers to
1081 apply the waiver credit againt charges in the
1082 student account exception Retension chanrges
1083 Known limitations,enhancements,remarks:
1084
1085 Change History :
1086 WHO WHEN WHAT
1087 ***************************************************************** */
1088
1089 -- Fetch the Charges except Retention Charges having some positive amount due exists
1090 -- for a combination of Person, Fee Type and Fee Period or source invoice id alone
1091 CURSOR stnt_charges_cur(
1092 cp_n_invoice_id igs_fi_inv_int_all.invoice_id%TYPE,
1093 cp_n_person_id igs_fi_inv_int_all.person_id%TYPE,
1094 cp_v_target_fee_type igs_fi_inv_int_all.fee_type%TYPE,
1095 cp_v_fee_cal_type igs_fi_credits_all.fee_cal_type%TYPE,
1096 cp_n_fee_ci_seq_num igs_fi_credits_all.fee_ci_sequence_number%TYPE) IS
1097 SELECT inv.invoice_id,
1098 inv.invoice_amount_due
1099 FROM igs_fi_inv_int_all inv,
1100 igs_fi_invln_int_all invln
1101 WHERE (inv.invoice_id = cp_n_invoice_id OR cp_n_invoice_id IS NULL)
1102 AND inv.person_id = cp_n_person_id
1103 AND inv.fee_type = cp_v_target_fee_type
1104 AND inv.fee_cal_type = cp_v_fee_cal_type
1105 AND inv.fee_ci_sequence_number = cp_n_fee_ci_seq_num
1106 AND NVL (inv.invoice_amount_due, 0) > 0
1107 AND inv.transaction_type <> 'RETENTION'
1108 AND invln.invoice_id = inv.invoice_id
1109 AND NVL(invln.error_account,'N') = 'N'
1110 ORDER BY inv.invoice_id;
1111
1112 l_n_app_id igs_fi_applications.application_id%TYPE;
1113 l_n_amount_apply igs_fi_applications.amount_applied%TYPE;
1114 l_n_dr_gl_ccid igs_fi_applications.dr_gl_code_ccid%TYPE;
1115 l_n_cr_gl_ccid igs_fi_applications.cr_gl_code_ccid%TYPE;
1116 l_v_dr_acc_cd igs_fi_applications.dr_account_cd%TYPE;
1117 l_v_cr_acc_cd igs_fi_applications.cr_account_cd%TYPE;
1118 l_n_unapp_amount igs_fi_credits.unapplied_amount%TYPE;
1119 l_n_inv_amt_due igs_fi_inv_int.invoice_amount_due%TYPE;
1120 l_v_err_msg VARCHAR2(2000);
1121 l_b_status BOOLEAN;
1122
1123 BEGIN
1124
1125 x_return_status := 'S';
1126
1127 -- Loop through the charges except Retention charges having some positive amount due exists
1128 -- for a combination of Person, Fee Type and Fee Period or source invoice id alone
1129 FOR stnt_charges_rec IN stnt_charges_cur(p_n_invoice_id, p_n_person_id,
1130 p_v_target_fee_type, p_v_fee_cal_type, p_n_fee_ci_seq_number) LOOP
1131
1132 log_to_fnd('process_stdnt_charges','Processing the Invoice ID: '||stnt_charges_rec.invoice_id);
1133
1134 -- Calculate the amount to be applied against the context charge
1135 IF stnt_charges_rec.invoice_amount_due >= p_n_credit_amount THEN
1136 l_n_amount_apply := p_n_credit_amount;
1137 ELSE
1138 l_n_amount_apply := stnt_charges_rec.invoice_amount_due;
1139 END IF;
1140
1141 -- Invoke the application procedure to create an application
1142 l_n_app_id := NULL;
1143 l_v_err_msg := NULL;
1144 igs_fi_gen_007.create_application(
1145 p_application_id => l_n_app_id,
1146 p_credit_id => p_n_credit_id,
1147 p_invoice_id => stnt_charges_rec.invoice_id,
1148 p_amount_apply => l_n_amount_apply,
1149 p_appl_type => g_v_app,
1150 p_appl_hierarchy_id => NULL,
1151 p_validation => 'Y',
1152 p_dr_gl_ccid => l_n_dr_gl_ccid,
1153 p_cr_gl_ccid => l_n_cr_gl_ccid,
1154 p_dr_account_cd => l_v_dr_acc_cd,
1155 p_cr_account_cd => l_v_cr_acc_cd,
1156 p_unapp_amount => l_n_unapp_amount,
1157 p_inv_amt_due => l_n_inv_amt_due,
1158 p_err_msg => l_v_err_msg,
1159 p_status => l_b_status,
1160 p_d_gl_date => p_d_gl_date);
1161
1162 -- Check the Staus of the Waiver Credit application
1163 -- If the status of the application is true
1164 IF l_b_status THEN
1165 log_to_fnd(p_v_module => 'process_stdnt_charges',
1166 p_v_string => 'Application record created. Application ID= '||l_n_app_id);
1167
1168 -- Deduct the Waiver credit amount to the extent of applied amount(l_n_amount_apply)
1169 p_n_credit_amount := p_n_credit_amount - l_n_amount_apply;
1170
1171 -- If the balance Waiver credit is zero then return to apply_waivers with success status
1172 IF p_n_credit_amount = 0 THEN
1173 x_return_status := 'S';
1174 RETURN;
1175 END IF;
1176
1177 -- If the status of the application is false
1178 ELSE
1179 -- Log the message and return to the apply_waivers procedure
1180 log_to_fnd(p_v_module => 'process_stdnt_charges',
1181 p_v_string => 'Error: '||fnd_message.get_string('IGS', l_v_err_msg));
1182
1183 x_return_status := 'E';
1184 RETURN;
1185
1186 END IF;
1187
1188 END LOOP;
1189
1190 EXCEPTION
1191 WHEN OTHERS THEN
1192
1193 -- Log the SQLERRM message and return to the apply_waivers procedure
1194 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1195 fnd_log.string(fnd_log.level_exception,'igs.plsql.igs_fi_wav_utils_001.process_stdnt_charges.exception','sqlerrm ' || SQLERRM);
1196 END IF;
1197
1198 -- Return the Error Status
1199 x_return_status := 'E';
1200
1201 END process_stdnt_charges;
1202
1203 PROCEDURE adjust_stdnt_charges(
1204 p_n_invoice_id IN igs_fi_inv_int_all.invoice_id%TYPE,
1205 p_n_credit_id IN igs_fi_credits_all.credit_id%TYPE,
1206 p_d_gl_date IN igs_fi_invln_int.gl_date%TYPE,
1207 p_n_credit_amount IN OUT NOCOPY NUMBER,
1208 x_return_status OUT NOCOPY VARCHAR2) AS
1209 /******************************************************************
1210 Created By : Anji Yedubati
1211 Date Created By : 21-JUL-2005
1212 Purpose : This procedure is called from apply_waivers to
1213 un apply the applications applied against waiver,
1214 charge adjustment, enrollment deposit and other deposits
1215 and apply against the waiver credit
1216 Known limitations,enhancements,remarks:
1217
1218 Change History :
1219 WHO WHEN WHAT
1220 AYEDUBAT 03-NOV-2005 Changed the cursor, wav_app_cur to fetch the amount_applied using the
1221 function call, igs_fi_gen_007.get_sum_appl_amnt for bug# 4634950
1222 ***************************************************************** */
1223
1224 -- Fetch the application records other than negative charge adjustment credit,
1225 -- enrollment deposit and other deposits and waiver credit for a charge transaction
1226 CURSOR wav_app_cur (
1227 cp_n_invoice_id igs_fi_applications.invoice_id%TYPE,
1228 cp_cst_app igs_fi_applications.application_type%TYPE) IS
1229 SELECT igs_fi_gen_007.get_sum_appl_amnt(appl.application_id) amount_applied,
1230 appl.application_id,
1231 appl.credit_id,
1232 appl.invoice_id
1233 FROM igs_fi_applications appl,
1234 igs_fi_credits_all crd,
1235 igs_fi_cr_types cr
1236 WHERE appl.invoice_id = cp_n_invoice_id
1237 AND appl.credit_id = crd.credit_id
1238 AND appl.application_type = cp_cst_app
1239 AND crd.credit_type_id = cr.credit_type_id
1240 AND cr.credit_class NOT IN ('CHGADJ','WAIVER','ENRDEPOSIT','OTHDEPOSIT')
1241 AND NOT EXISTS(
1242 SELECT 'X'
1243 FROM igs_fi_applications appl2
1244 WHERE appl2.application_type = 'UNAPP'
1245 AND appl2.link_application_id = appl.application_id
1246 AND appl2.amount_applied = - appl.amount_applied)
1247 ORDER BY appl.application_id;
1248
1249 l_n_app_id igs_fi_applications.application_id%TYPE;
1250 l_n_amount_apply igs_fi_applications.amount_applied%TYPE;
1251 l_n_dr_gl_ccid igs_fi_applications.dr_gl_code_ccid%TYPE;
1252 l_n_cr_gl_ccid igs_fi_applications.cr_gl_code_ccid%TYPE;
1253 l_v_dr_acc_cd igs_fi_applications.dr_account_cd%TYPE;
1254 l_v_cr_acc_cd igs_fi_applications.cr_account_cd%TYPE;
1255 l_n_unapp_amount igs_fi_credits.unapplied_amount%TYPE;
1256 l_n_inv_amt_due igs_fi_inv_int.invoice_amount_due%TYPE;
1257 l_v_err_msg VARCHAR2(2000);
1258 l_b_status BOOLEAN;
1259
1260 BEGIN
1261
1262 x_return_status := 'S';
1263
1264 -- Loop through the application records other than negative charge adjustment credit,
1265 -- enrollment deposit and other deposits and waiver credit for a charge transaction
1266 FOR wav_app_rec IN wav_app_cur(p_n_invoice_id, g_v_app) LOOP
1267
1268 log_to_fnd('adjust_stdnt_charges','Processing the Application ID: '||wav_app_rec.application_id);
1269
1270 -- Calculate the amount to be un Applied against the context application
1271 IF wav_app_rec.amount_applied >= p_n_credit_amount THEN
1272 l_n_amount_apply := p_n_credit_amount;
1273 ELSE
1274 l_n_amount_apply := wav_app_rec.amount_applied;
1275 END IF;
1276
1277 -- Invoke the application procedure to un apply the context application
1278 l_n_app_id := wav_app_rec.application_id;
1279 l_v_err_msg := NULL;
1280 igs_fi_gen_007.create_application(
1281 p_application_id => l_n_app_id,
1282 p_credit_id => wav_app_rec.credit_id,
1283 p_invoice_id => wav_app_rec.invoice_id,
1284 p_amount_apply => l_n_amount_apply,
1285 p_appl_type => g_v_unapp,
1286 p_appl_hierarchy_id => NULL,
1287 p_validation => 'Y',
1288 p_dr_gl_ccid => l_n_dr_gl_ccid,
1289 p_cr_gl_ccid => l_n_cr_gl_ccid,
1290 p_dr_account_cd => l_v_dr_acc_cd,
1291 p_cr_account_cd => l_v_cr_acc_cd,
1292 p_unapp_amount => l_n_unapp_amount,
1293 p_inv_amt_due => l_n_inv_amt_due,
1294 p_err_msg => l_v_err_msg,
1295 p_status => l_b_status,
1296 p_d_gl_date => p_d_gl_date);
1297
1298 -- Check the staus of the Un Application
1299 -- If the status of the un application is true
1300 IF l_b_status THEN
1301 log_to_fnd(p_v_module => 'adjust_stdnt_charges',
1302 p_v_string => 'Un Application record created. Application ID= '||l_n_app_id);
1303
1304 -- Invoke the application procedure to craete the application
1305 l_n_app_id := NULL;
1306 l_v_err_msg := NULL;
1307 igs_fi_gen_007.create_application(
1308 p_application_id => l_n_app_id,
1309 p_credit_id => p_n_credit_id,
1310 p_invoice_id => wav_app_rec.invoice_id,
1311 p_amount_apply => l_n_amount_apply,
1312 p_appl_type => g_v_app,
1313 p_appl_hierarchy_id => NULL,
1314 p_validation => 'Y',
1315 p_dr_gl_ccid => l_n_dr_gl_ccid,
1316 p_cr_gl_ccid => l_n_cr_gl_ccid,
1317 p_dr_account_cd => l_v_dr_acc_cd,
1318 p_cr_account_cd => l_v_cr_acc_cd,
1319 p_unapp_amount => l_n_unapp_amount,
1320 p_inv_amt_due => l_n_inv_amt_due,
1321 p_err_msg => l_v_err_msg,
1322 p_status => l_b_status,
1323 p_d_gl_date => p_d_gl_date);
1324
1325 -- If the status of the application is true
1326 IF l_b_status THEN
1327 log_to_fnd(p_v_module => 'adjust_stdnt_charges',
1328 p_v_string => 'Application record created. Application ID= '||l_n_app_id);
1329
1330 -- Deduct the Waiver credit amount to the extent of applied amount(l_n_amount_apply)
1331 p_n_credit_amount := p_n_credit_amount - l_n_amount_apply;
1332
1333 -- If the balance Waiver credit is zero then return to apply_waivers with success status
1334 IF p_n_credit_amount = 0 THEN
1335 x_return_status := 'S';
1336 RETURN;
1337 END IF;
1338
1339 -- If the status of the application is false
1340 ELSE
1341
1342 -- Log the message and return to the apply_waivers Procedure
1343 log_to_fnd(p_v_module => 'adjust_stdnt_charges',
1344 p_v_string => 'Error: '||fnd_message.get_string('IGS', l_v_err_msg));
1345
1346 x_return_status := 'E';
1347 RETURN;
1348
1349 END IF;
1350
1351 -- If the status of the un application is false
1352 ELSE
1353
1354 -- Log the message and return to the apply_waivers Procedure
1355 log_to_fnd(p_v_module => 'adjust_stdnt_charges',
1356 p_v_string => 'Error: '||fnd_message.get_string('IGS', l_v_err_msg) );
1357
1358 x_return_status := 'E';
1359 RETURN;
1360
1361 END IF;
1362
1363 END LOOP;
1364
1365 EXCEPTION
1366 WHEN OTHERS THEN
1367
1368 -- Log the SQLERRM message and return to the apply_waivers Procedure
1369 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1370 fnd_log.string(fnd_log.level_exception,'igs.plsql.igs_fi_wav_utils_001.adjust_stdnt_charges.exception','sqlerrm ' || SQLERRM);
1371 END IF;
1372
1373 -- Return the Error Status
1374 x_return_status := 'E';
1375
1376 END adjust_stdnt_charges;
1377
1378 PROCEDURE create_wavadj_charge(
1379 p_n_person_id IN igs_pe_person_base_v.person_id%TYPE,
1380 p_v_fee_cal_type IN igs_fi_waiver_pgms.fee_cal_type%TYPE,
1381 p_n_fee_ci_seq_number IN igs_fi_waiver_pgms.fee_ci_sequence_number%TYPE,
1382 p_v_waiver_name IN igs_fi_waiver_pgms.waiver_name%TYPE,
1383 p_v_adj_fee_type IN igs_fi_waiver_pgms.adjustment_fee_type%TYPE,
1384 p_n_credit_id IN igs_fi_credits_all.credit_id%TYPE,
1385 p_v_currency_cd IN igs_fi_inv_int_all.currency_cd%TYPE,
1386 p_n_waiver_amt IN igs_fi_inv_int_all.invoice_amount%TYPE,
1387 p_d_gl_date IN igs_fi_invln_int.gl_date%TYPE,
1388 x_return_status OUT NOCOPY VARCHAR2) AS
1389 /******************************************************************
1390 Created By : Anji Yedubati
1391 Date Created By : 21-JUL-2005
1392 Purpose : This procedure is called from apply_waivers to create the
1393 waiver adjustment charge for the balance waiver credit and apply
1394 against the waiver adjustment charge
1395 Known limitations,enhancements,remarks:
1396
1397 Change History :
1398 WHO WHEN WHAT
1399 ***************************************************************** */
1400
1401 l_n_invoice_id igs_fi_inv_int_all.invoice_id%TYPE;
1402 l_b_status BOOLEAN;
1403
1404 l_n_app_id igs_fi_applications.application_id%TYPE;
1405 l_n_dr_gl_ccid igs_fi_applications.dr_gl_code_ccid%TYPE;
1406 l_n_cr_gl_ccid igs_fi_applications.cr_gl_code_ccid%TYPE;
1407 l_v_dr_acc_cd igs_fi_applications.dr_account_cd%TYPE;
1408 l_v_cr_acc_cd igs_fi_applications.cr_account_cd%TYPE;
1409 l_n_unapp_amount igs_fi_credits.unapplied_amount%TYPE;
1410 l_n_inv_amt_due igs_fi_inv_int.invoice_amount_due%TYPE;
1411 l_v_err_msg VARCHAR2(2000);
1412
1413 BEGIN
1414
1415 x_return_status := 'S';
1416
1417 log_to_fnd('create_wavadj_charge','Before calling the procedure igs_fi_wav_utils_002.call_charges_api');
1418
1419 -- Invoke the Charges API to create Waiver Adjustment Charge
1420 l_n_invoice_id := NULL;
1421 igs_fi_wav_utils_002.call_charges_api(
1422 p_n_person_id => p_n_person_id,
1423 p_v_fee_cal_type => p_v_fee_cal_type,
1424 p_n_fee_ci_seq_number => p_n_fee_ci_seq_number,
1425 p_v_waiver_name => p_v_waiver_name,
1426 p_v_adj_fee_type => p_v_adj_fee_type,
1427 p_v_currency_cd => p_v_currency_cd,
1428 p_n_waiver_amt => p_n_waiver_amt,
1429 p_d_gl_date => p_d_gl_date,
1430 p_n_invoice_id => l_n_invoice_id,
1431 x_return_status => x_return_status);
1432
1433 IF x_return_status = 'S' THEN
1434
1435 log_to_fnd('create_wavadj_charge','Charge created successfully. Invoice ID= '||l_n_invoice_id);
1436
1437 -- Invoke the application procedure to apply the Waiver Adjustment Charge
1438 -- against the Waiver Credit record in entirity
1439 l_n_app_id := NULL;
1440 l_v_err_msg := NULL;
1441 igs_fi_gen_007.create_application(
1442 p_application_id => l_n_app_id,
1443 p_credit_id => p_n_credit_id,
1444 p_invoice_id => l_n_invoice_id,
1445 p_amount_apply => p_n_waiver_amt,
1446 p_appl_type => g_v_app,
1447 p_appl_hierarchy_id => NULL,
1448 p_validation => 'Y',
1449 p_dr_gl_ccid => l_n_dr_gl_ccid,
1450 p_cr_gl_ccid => l_n_cr_gl_ccid,
1451 p_dr_account_cd => l_v_dr_acc_cd,
1452 p_cr_account_cd => l_v_cr_acc_cd,
1453 p_unapp_amount => l_n_unapp_amount,
1454 p_inv_amt_due => l_n_inv_amt_due,
1455 p_err_msg => l_v_err_msg,
1456 p_status => l_b_status,
1457 p_d_gl_date => p_d_gl_date);
1458
1459 -- Check the Staus of the Waiver Credit Application
1460 IF NOT l_b_status THEN
1461 -- Log the message and set the Error Status
1462 log_to_fnd(p_v_module => 'create_wavadj_charge',
1463 p_v_string => 'Error: '||fnd_message.get_string('IGS', l_v_err_msg) );
1464 x_return_status := 'E';
1465
1466 ELSE
1467 log_to_fnd('create_wavadj_charge','Application record created. Application ID= '||l_n_app_id);
1468 END IF;
1469
1470 ELSE
1471
1472 -- Log the message and set the Error Status
1473 log_to_fnd(p_v_module => 'create_wavadj_charge',
1474 p_v_string => 'Error: '||fnd_message.get_string('IGS', l_v_err_msg));
1475
1476 END IF;
1477
1478 EXCEPTION
1479 WHEN OTHERS THEN
1480 -- Log the SQLERRM message and return the Error status
1481 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1482 fnd_log.string(fnd_log.level_exception,'igs.plsql.igs_fi_wav_utils_001.create_wavadj_charge.exception','sqlerrm ' || SQLERRM);
1483 END IF;
1484
1485 -- Return the Error Status
1486 x_return_status := 'E';
1487
1488 END create_wavadj_charge;
1489
1490 PROCEDURE process_due_wavadj_charges(
1491 p_n_source_credit_id IN igs_fi_credits_all.credit_id%TYPE,
1492 p_n_person_id IN igs_pe_person_base_v.person_id%TYPE,
1493 p_v_fee_cal_type IN igs_fi_waiver_pgms.fee_cal_type%TYPE,
1494 p_n_fee_ci_seq_number IN igs_fi_waiver_pgms.fee_ci_sequence_number%TYPE,
1495 p_v_waiver_name IN igs_fi_waiver_pgms.waiver_name%TYPE,
1496 p_v_adj_fee_type IN igs_fi_waiver_pgms.adjustment_fee_type%TYPE,
1497 p_d_gl_date IN igs_fi_invln_int.gl_date%TYPE,
1498 x_return_status OUT NOCOPY VARCHAR2) AS
1499 /******************************************************************
1500 Created By : Anji Yedubati
1501 Date Created By : 21-JUL-2005
1502 Purpose : This procedure is called from apply_waivers to apply the
1503 waiver adjustment charges, if any to the waiver credits
1504 Known limitations,enhancements,remarks:
1505
1506 Change History :
1507 WHO WHEN WHAT
1508 AYEDUBAT 03-NOV-2005 Changed the cursor, appls_otherthan_wavadj_cur to fetch the amount_applied
1509 using the function call, igs_fi_gen_007.get_sum_appl_amnt.
1510 Added the Not exist clause to the cursor,appls_otherthan_wavadj_cur to restrict
1511 the records which are already un applied for bug# 4634950
1512 ***************************************************************** */
1513
1514 -- Fetch the Waiver charge adjustment records having some positive amount due exists
1515 -- for the combination of Person, Fee Type, Fee Period and Waiver name
1516 CURSOR waiver_adj_charges_cur(
1517 cp_n_person_id igs_fi_inv_int_all.person_id%TYPE,
1518 cp_v_adj_fee_type igs_fi_inv_int_all.fee_type%TYPE,
1519 cp_v_fee_cal_type igs_fi_credits_all.fee_cal_type%TYPE,
1520 cp_n_fee_ci_seq_num igs_fi_credits_all.fee_ci_sequence_number%TYPE,
1521 cp_v_waiver_name igs_fi_credits_all.waiver_name%TYPE) IS
1522 SELECT
1523 inv.invoice_id,
1524 inv.invoice_amount,
1525 inv.invoice_amount_due
1526 FROM igs_fi_inv_int_all inv,
1527 igs_fi_invln_int_all invln
1528 WHERE inv.person_id = cp_n_person_id
1529 AND inv.fee_type = cp_v_adj_fee_type
1530 AND inv.fee_cal_type = cp_v_fee_cal_type
1531 AND inv.fee_ci_sequence_number = cp_n_fee_ci_seq_num
1532 AND inv.waiver_name = cp_v_waiver_name
1533 AND NVL (inv.invoice_amount_due, 0) > 0
1534 AND invln.invoice_id = inv.invoice_id
1535 AND NVL(invln.error_account,'N') = 'N'
1536 ORDER BY inv.invoice_id;
1537
1538 -- Fetch the waiver credit records having some positive amount due exists
1539 -- for the combination of Person, Fee Period and Waiver Name
1540 CURSOR stdnt_waiver_crdits_cur(
1541 cp_n_credit_id igs_fi_credits_all.credit_id%TYPE,
1542 cp_n_person_id igs_fi_credits_all.party_id%TYPE,
1543 cp_v_fee_cal_type igs_fi_credits_all.fee_cal_type%TYPE,
1544 cp_n_fee_ci_seq_num igs_fi_credits_all.fee_ci_sequence_number%TYPE,
1545 cp_v_waiver_name igs_fi_credits_all.waiver_name%TYPE) IS
1546 SELECT
1547 crd.credit_id,
1548 crd.unapplied_amount
1549 FROM igs_fi_credits_all crd
1550 WHERE (cp_n_credit_id IS NULL OR crd.credit_id = cp_n_credit_id)
1551 AND crd.party_id = cp_n_person_id
1552 AND crd.fee_cal_type = cp_v_fee_cal_type
1553 AND crd.fee_ci_sequence_number = cp_n_fee_ci_seq_num
1554 AND crd.waiver_name = cp_v_waiver_name
1555 AND NVL(crd.unapplied_amount, 0) > 0
1556 ORDER BY credit_id;
1557
1558 -- Fetch the waiver credit records which are fully applied
1559 -- for the combination of Person, Fee Period and Waiver Name
1560 CURSOR stdnt_fullapplied_wavcr_cur(
1561 cp_n_credit_id igs_fi_credits_all.credit_id%TYPE,
1562 cp_n_person_id igs_fi_credits_all.party_id%TYPE,
1563 cp_v_fee_cal_type igs_fi_credits_all.fee_cal_type%TYPE,
1564 cp_n_fee_ci_seq_num igs_fi_credits_all.fee_ci_sequence_number%TYPE,
1565 cp_v_waiver_name igs_fi_credits_all.waiver_name%TYPE) IS
1566 SELECT
1567 crd.credit_id,
1568 crd.unapplied_amount
1569 FROM igs_fi_credits_all crd
1570 WHERE (cp_n_credit_id IS NULL OR crd.credit_id = cp_n_credit_id)
1571 AND crd.party_id = cp_n_person_id
1572 AND crd.fee_cal_type = cp_v_fee_cal_type
1573 AND crd.fee_ci_sequence_number = cp_n_fee_ci_seq_num
1574 AND crd.waiver_name = cp_v_waiver_name
1575 ORDER BY credit_id;
1576
1577 -- Fetch the application records exist for a waiver credit transaction
1578 -- excluding the applications between the waiver credit and waiver adjustment charge
1579 CURSOR appls_otherthan_wavadj_cur(
1580 cp_n_credit_id igs_fi_credits_all.credit_id%TYPE) IS
1581 SELECT
1582 igs_fi_gen_007.get_sum_appl_amnt(appl.application_id) amount_applied,
1583 appl.application_id,
1584 appl.credit_id,
1585 appl.invoice_id
1586 FROM igs_fi_applications appl,
1587 igs_fi_inv_int_all inv
1588 WHERE appl.credit_id = cp_n_credit_id
1589 AND appl.invoice_id = inv.invoice_id
1590 AND appl.application_type = 'APP'
1591 AND inv.transaction_type <> 'WAIVER_ADJ'
1592 AND NOT EXISTS (
1593 SELECT 'X'
1594 FROM igs_fi_applications appl2
1595 WHERE appl2.application_type = 'UNAPP'
1596 AND appl2.link_application_id = appl.application_id
1597 AND appl2.amount_applied = - appl.amount_applied)
1598 ORDER BY appl.application_id;
1599
1600 l_n_invoice_amount igs_fi_inv_int_all.invoice_amount%TYPE;
1601 l_n_amount_apply NUMBER;
1602
1603 l_n_app_id igs_fi_applications.application_id%TYPE;
1604 l_n_dr_gl_ccid igs_fi_applications.dr_gl_code_ccid%TYPE;
1605 l_n_cr_gl_ccid igs_fi_applications.cr_gl_code_ccid%TYPE;
1606 l_v_dr_acc_cd igs_fi_applications.dr_account_cd%TYPE;
1607 l_v_cr_acc_cd igs_fi_applications.cr_account_cd%TYPE;
1608 l_n_unapp_amount igs_fi_credits.unapplied_amount%TYPE;
1609 l_n_inv_amt_due igs_fi_inv_int.invoice_amount_due%TYPE;
1610 l_v_err_msg VARCHAR2(2000);
1611 l_b_status BOOLEAN;
1612
1613 BEGIN
1614
1615 -- Initialize the Variables
1616 l_n_invoice_amount := 0;
1617 x_return_status := 'S';
1618
1619 -- Loop through the Waiver charge adjustment records having some positive amount due exists
1620 -- for the combination of Person, Fee Type, Fee Period and Waiver name
1621 FOR waiver_adj_charges_rec IN waiver_adj_charges_cur(p_n_person_id,p_v_adj_fee_type,
1622 p_v_fee_cal_type,p_n_fee_ci_seq_number,p_v_waiver_name) LOOP
1623
1624 log_to_fnd(p_v_module => 'process_due_wavadj_charges',
1625 p_v_string => 'Processing waiver_adj_charges_rec Invoice ID: '||waiver_adj_charges_rec.invoice_id);
1626
1627 -- Intialize the Invoice Amount Due in a local variable and process the following logic
1628 -- until Invoice Amount Due is 0 or all the records have been processed successfully
1629 l_n_invoice_amount := waiver_adj_charges_rec.invoice_amount_due;
1630
1631 -- Loop through the waiver credit records having some positive amount due exists
1632 -- for the combination of Person, Fee Period and Waiver Name details
1633 <<waiver_crdits_loop>>
1634 FOR stdnt_waiver_crdits_rec IN stdnt_waiver_crdits_cur (p_n_source_credit_id,p_n_person_id, p_v_fee_cal_type,
1635 p_n_fee_ci_seq_number, p_v_waiver_name) LOOP
1636
1637 log_to_fnd(p_v_module => 'process_due_wavadj_charges',
1638 p_v_string => 'Processing stdnt_waiver_crdits_rec Credit ID= '||stdnt_waiver_crdits_rec.credit_id);
1639
1640 -- Calculate the Amount to be applied
1641 -- If the Unapplied amount on the context waiver credit record is greater than or
1642 -- equal to Invoice Amount(l_n_invoice_amount), apply an amount equal to Invoice Amount
1643 -- Otherwise , apply an amount equal to Unapplied amount on the context waiver credit record
1644 IF stdnt_waiver_crdits_rec.unapplied_amount >= l_n_invoice_amount THEN
1645 l_n_amount_apply := l_n_invoice_amount;
1646 ELSE
1647 l_n_amount_apply := stdnt_waiver_crdits_rec.unapplied_amount;
1648 END IF;
1649
1650 --Invoke the application procedure to create Application
1651 l_n_app_id := NULL;
1652 l_v_err_msg := NULL;
1653 igs_fi_gen_007.create_application(
1654 p_application_id => l_n_app_id,
1655 p_credit_id => stdnt_waiver_crdits_rec.credit_id,
1656 p_invoice_id => waiver_adj_charges_rec.invoice_id,
1657 p_amount_apply => l_n_amount_apply,
1658 p_appl_type => g_v_app,
1659 p_appl_hierarchy_id => NULL,
1660 p_validation => 'Y',
1661 p_dr_gl_ccid => l_n_dr_gl_ccid,
1662 p_cr_gl_ccid => l_n_cr_gl_ccid,
1663 p_dr_account_cd => l_v_dr_acc_cd,
1664 p_cr_account_cd => l_v_cr_acc_cd,
1665 p_unapp_amount => l_n_unapp_amount,
1666 p_inv_amt_due => l_n_inv_amt_due,
1667 p_err_msg => l_v_err_msg,
1668 p_status => l_b_status,
1669 p_d_gl_date => p_d_gl_date);
1670
1671 IF l_b_status THEN
1672
1673 log_to_fnd(p_v_module => 'process_due_wavadj_charges',
1674 p_v_string => 'Application record created. Application ID= '||l_n_app_id);
1675
1676 -- Deduct the Invoice Amount to the extent applied (l_n_amount_apply)
1677 l_n_invoice_amount := l_n_invoice_amount - l_n_amount_apply;
1678 IF l_n_invoice_amount = 0 THEN
1679 EXIT waiver_crdits_loop;
1680 END IF;
1681
1682 ELSE
1683
1684 -- Log the message and return to the calling Procedure
1685 log_to_fnd( p_v_module => 'process_due_wavadj_charges',
1686 p_v_string => 'Error: '||fnd_message.get_string('IGS', l_v_err_msg));
1687 x_return_status := 'E';
1688 RETURN;
1689 END IF;
1690
1691 END LOOP waiver_crdits_loop;
1692
1693 -- If Invoice Amount is lee than or euqal to 0 then Return from the Procedure
1694 IF l_n_invoice_amount > 0 THEN
1695
1696 -- Loop through the waiver credit records which are fully applied
1697 -- for the combination of Person, Fee Period and Waiver Name details
1698 <<fullapplied_wavcr_loop>>
1699 FOR stdnt_fullapplied_wavcr_rec IN stdnt_fullapplied_wavcr_cur(p_n_source_credit_id,p_n_person_id,
1700 p_v_fee_cal_type,p_n_fee_ci_seq_number, p_v_waiver_name ) LOOP
1701
1702 log_to_fnd(p_v_module => 'process_due_wavadj_charges',
1703 p_v_string => 'Processing stdnt_fullapplied_wavcr_rec Credit ID= '||stdnt_fullapplied_wavcr_rec.credit_id);
1704
1705 -- Loop through the application records exists for the context waiver credit transaction
1706 -- excluding the applications between the waiver credit and waiver adjustment charge
1707 FOR appls_otherthan_wavadj_rec IN appls_otherthan_wavadj_cur(stdnt_fullapplied_wavcr_rec.credit_id) LOOP
1708
1709 -- Calculate the Amount to be applied for Unapplication and Application
1710 IF appls_otherthan_wavadj_rec.amount_applied >= l_n_invoice_amount THEN
1711 l_n_amount_apply := l_n_invoice_amount;
1712 ELSE
1713 l_n_amount_apply := appls_otherthan_wavadj_rec.amount_applied;
1714 END IF;
1715
1716 -- Invoke the application procedure to Un apply the context application
1717 l_v_err_msg := NULL;
1718 l_n_app_id := appls_otherthan_wavadj_rec.application_id;
1719 igs_fi_gen_007.create_application(
1720 p_application_id => l_n_app_id,
1721 p_credit_id => appls_otherthan_wavadj_rec.credit_id,
1722 p_invoice_id => appls_otherthan_wavadj_rec.invoice_id,
1723 p_amount_apply => l_n_amount_apply,
1724 p_appl_type => g_v_unapp,
1725 p_appl_hierarchy_id => NULL,
1726 p_validation => 'Y',
1727 p_dr_gl_ccid => l_n_dr_gl_ccid,
1728 p_cr_gl_ccid => l_n_cr_gl_ccid,
1729 p_dr_account_cd => l_v_dr_acc_cd,
1730 p_cr_account_cd => l_v_cr_acc_cd,
1731 p_unapp_amount => l_n_unapp_amount,
1732 p_inv_amt_due => l_n_inv_amt_due,
1733 p_err_msg => l_v_err_msg,
1734 p_status => l_b_status,
1735 p_d_gl_date => p_d_gl_date);
1736
1737 -- Check the Staus of the Unapplication
1738 -- If Application failed, then return to the calling procedure
1739 IF NOT l_b_status THEN
1740
1741 -- Log the message and return to the calling Procedure
1742 log_to_fnd(p_v_module => 'process_due_wavadj_charges',
1743 p_v_string => 'Error: '||fnd_message.get_string('IGS', l_v_err_msg) );
1744 -- Return Error Status
1745 x_return_status := 'E';
1746 RETURN;
1747
1748 END IF;
1749
1750 log_to_fnd(p_v_module => 'process_due_wavadj_charges',
1751 p_v_string => 'Un application record created. Application ID= '||l_n_app_id);
1752
1753
1754 -- Invoke the procedure to apply the context application credit record
1755 -- against the Waiver Adjustment Charge
1756 l_n_app_id := NULL;
1757 l_v_err_msg := NULL;
1758 igs_fi_gen_007.create_application(
1759 p_application_id => l_n_app_id,
1760 p_credit_id => appls_otherthan_wavadj_rec.credit_id,
1761 p_invoice_id => waiver_adj_charges_rec.invoice_id,
1762 p_amount_apply => l_n_amount_apply,
1763 p_appl_type => g_v_app,
1764 p_appl_hierarchy_id => NULL,
1765 p_validation => 'Y',
1766 p_dr_gl_ccid => l_n_dr_gl_ccid,
1767 p_cr_gl_ccid => l_n_cr_gl_ccid,
1768 p_dr_account_cd => l_v_dr_acc_cd,
1769 p_cr_account_cd => l_v_cr_acc_cd,
1770 p_unapp_amount => l_n_unapp_amount,
1771 p_inv_amt_due => l_n_inv_amt_due,
1772 p_err_msg => l_v_err_msg,
1773 p_status => l_b_status,
1774 p_d_gl_date => p_d_gl_date);
1775
1776 IF l_b_status THEN
1777 log_to_fnd(p_v_module => 'process_due_wavadj_charges',
1778 p_v_string => 'Application record created. Application ID= '||l_n_app_id);
1779
1780 -- Deduct the Invoice Amount to the extent applied (l_n_amount_apply)
1781 l_n_invoice_amount := l_n_invoice_amount - l_n_amount_apply;
1782 IF l_n_invoice_amount = 0 THEN
1783 EXIT fullapplied_wavcr_loop;
1784 END IF;
1785
1786 ELSE
1787 -- Log the message and return to the calling Procedure
1788 log_to_fnd(p_v_module => 'process_due_wavadj_charges',
1789 p_v_string => 'Error: '||fnd_message.get_string('IGS', l_v_err_msg));
1790 -- Return Error Status
1791 x_return_status := 'E';
1792 RETURN;
1793
1794 END IF;
1795
1796 END LOOP;
1797
1798 END LOOP fullapplied_wavcr_loop;
1799
1800 END IF; -- End of Invoice Amount > 0 check
1801
1802 END LOOP;
1803
1804 EXCEPTION
1805
1806 WHEN OTHERS THEN
1807 -- Log the SQLERRM message
1808 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1809 fnd_log.string(fnd_log.level_exception,'igs.plsql.igs_fi_wav_utils_001.process_due_wavadj_charges.exception','sqlerrm ' || SQLERRM);
1810 END IF;
1811
1812 -- Return the Error Status
1813 x_return_status := 'E';
1814
1815 END process_due_wavadj_charges;
1816
1817 PROCEDURE log_to_fnd (p_v_module IN VARCHAR2,
1818 p_v_string IN VARCHAR2) IS
1819 /******************************************************************
1820 Created By : Anji Yedubati
1821 Date Created By : 21-JUL-2005
1822 Purpose : Procedure to log messages for Statement Level logging
1823 Known limitations,enhancements,remarks:
1824
1825 Change History :
1826 WHO WHEN WHAT
1827 ***************************************************************** */
1828
1829 BEGIN
1830
1831 -- If current Logging Level is less than or equal to Statement Level, then log the message
1832 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1833 fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_fi_wav_utils_001.'||p_v_module, p_v_string);
1834 END IF;
1835
1836 END log_to_fnd;
1837
1838
1839 PROCEDURE create_ss_waiver_transactions(
1840 p_n_person_id IN igs_pe_person_base_v.person_id%TYPE,
1841 p_v_fee_cal_type IN igs_fi_waiver_pgms.fee_cal_type%TYPE,
1842 p_n_fee_ci_seq_number IN igs_fi_waiver_pgms.fee_ci_sequence_number%TYPE,
1843 p_v_waiver_name IN igs_fi_waiver_pgms.waiver_name%TYPE,
1844 p_v_target_fee_type IN igs_fi_waiver_pgms.target_fee_type%TYPE,
1845 p_v_adj_fee_type IN igs_fi_waiver_pgms.adjustment_fee_type%TYPE,
1846 p_n_credit_type_id IN igs_fi_waiver_pgms.credit_type_id%TYPE,
1847 p_v_waiver_method_code IN igs_fi_waiver_pgms.waiver_method_code%TYPE,
1848 p_v_waiver_mode_code IN igs_fi_waiver_pgms.waiver_mode_code%TYPE,
1849 p_n_source_invoice_id IN igs_fi_inv_int_all.invoice_id%TYPE,
1850 p_n_waiver_amount IN igs_fi_credits_all.amount%TYPE,
1851 p_v_currency_cd IN igs_fi_inv_int_all.currency_cd%TYPE,
1852 p_d_gl_date IN igs_fi_invln_int_all.gl_date%TYPE,
1853 x_return_status OUT NOCOPY VARCHAR2) IS
1854 /******************************************************************
1855 Created By : Priya Athipatla
1856 Date Created By : 16-Aug-2005
1857 Purpose : Wrapper procedure invoked from SS to create waiver
1858 transacations.
1859 Known limitations,enhancements,remarks:
1860
1861 Change History :
1862 WHO WHEN WHAT
1863 ***************************************************************** */
1864
1865 -- Cursor to fetch the value of waiver_notify_finaid_flag from igs_fi_control_all
1866 CURSOR cur_notify_flag IS
1867 SELECT waiver_notify_finaid_flag
1868 FROM igs_fi_control_all;
1869
1870 -- Local variables
1871 l_n_credit_id igs_fi_credits_all.credit_id%TYPE;
1872 l_v_return_status VARCHAR2(1);
1873 l_v_notify_fa_flag igs_fi_control_all.waiver_notify_finaid_flag%TYPE;
1874
1875 BEGIN
1876
1877 l_v_return_status := 'S';
1878
1879 log_to_fnd(p_v_module => 'create_ss_waiver_transactions',
1880 p_v_string => ' Invoking igs_fi_wav_utils_002.call_credits_api for given Person, Waiver and Fee Period');
1881
1882 -- Invoke Credits API for the parameters in the context
1883 igs_fi_wav_utils_002.call_credits_api(p_n_person_id => p_n_person_id,
1884 p_v_fee_cal_type => p_v_fee_cal_type,
1885 p_n_fee_ci_seq_number => p_n_fee_ci_seq_number,
1886 p_v_waiver_name => p_v_waiver_name,
1887 p_n_credit_type_id => p_n_credit_type_id,
1888 p_v_currency_cd => p_v_currency_cd,
1889 p_n_waiver_amt => p_n_waiver_amount,
1890 p_d_gl_date => p_d_gl_date,
1891 p_n_credit_id => l_n_credit_id,
1892 x_return_status => l_v_return_status);
1893 -- If the above call returns E, then rollback and return from the procedure
1894 IF (l_v_return_status = 'E') THEN
1895 log_to_fnd(p_v_module => 'create_ss_waiver_transactions',
1896 p_v_string => ' Error: Error from igs_fi_wav_utils_002.call_credits_api, returning E after rollback');
1897 ROLLBACK;
1898 x_return_status := 'E';
1899 RETURN;
1900 END IF;
1901
1902 log_to_fnd(p_v_module => 'create_ss_waiver_transactions',
1903 p_v_string => ' Invoking igs_fi_wav_utils_001.apply_waivers for given Person, Waiver and Fee Period');
1904
1905 -- Invoke procedure to Apply the Waivers.
1906 igs_fi_wav_utils_001.apply_waivers(p_n_person_id => p_n_person_id,
1907 p_v_fee_cal_type => p_v_fee_cal_type,
1908 p_n_fee_ci_seq_number => p_n_fee_ci_seq_number,
1909 p_v_waiver_name => p_v_waiver_name,
1910 p_v_target_fee_type => p_v_target_fee_type,
1911 p_v_adj_fee_type => p_v_adj_fee_type,
1912 p_v_waiver_method_code => p_v_waiver_method_code,
1913 p_v_waiver_mode_code => p_v_waiver_mode_code,
1914 p_n_source_credit_id => l_n_credit_id,
1915 p_n_source_invoice_id => p_n_source_invoice_id,
1916 p_v_currency_cd => p_v_currency_cd,
1917 p_d_gl_date => p_d_gl_date,
1918 x_return_status => l_v_return_status);
1919 -- If the above call returns E, then rollback and return from the procedure
1920 IF (l_v_return_status = 'E') THEN
1921 log_to_fnd(p_v_module => 'create_ss_waiver_transactions',
1922 p_v_string => ' Error: Error from igs_fi_wav_utils_001.apply_waivers, returning E after rollback');
1923 ROLLBACK;
1924 x_return_status := 'E';
1925 RETURN;
1926 END IF;
1927
1928 -- Fetch the value of 'Generate Notification to Financial Aid' (WAIVER_NOTIFY_FINAID_FLAG in IGS_FI_CONTROL_ALL)
1929 -- If this value is 'Y', then invoke procedure to raise a Workflow event
1930 OPEN cur_notify_flag;
1931 FETCH cur_notify_flag INTO l_v_notify_fa_flag;
1932 IF (cur_notify_flag%NOTFOUND) THEN
1933 -- If no data was found in IGS_FI_CONTROL_ALL, rollback and return E
1934 log_to_fnd(p_v_module => 'create_ss_waiver_transactions',
1935 p_v_string => ' Error: Error in fetching WAIVER_NOTIFY_FINAID_FLAG from IGS_FI_CONTROL_ALL, returning E after rollback');
1936 CLOSE cur_notify_flag;
1937 ROLLBACK;
1938 x_return_status := 'E';
1939 RETURN;
1940 END IF;
1941 CLOSE cur_notify_flag;
1942
1943 log_to_fnd(p_v_module => 'create_ss_waiver_transactions',
1944 p_v_string => ' Fetched WAIVER_NOTIFY_FINAID_FLAG from IGS_FI_CONTROL_ALL, Value is: '||l_v_notify_fa_flag);
1945
1946 -- If 'Generate Notification to Financial Aid' is Yes, raise Workflow Event.
1947 IF NVL(l_v_notify_fa_flag,'N') = 'Y' THEN
1948 log_to_fnd(p_v_module => 'create_ss_waiver_transactions',
1949 p_v_string => ' Invoking igs_fi_wav_dtls_wf.raise_wavtrandtlstofa_event for given Person, Waiver and Fee Period');
1950 -- Invoke procedure to raise WF event
1951 igs_fi_wav_dtls_wf.raise_wavtrandtlstofa_event(p_n_person_id => p_n_person_id,
1952 p_v_waiver_name => p_v_waiver_name,
1953 p_c_fee_cal_type => p_v_fee_cal_type,
1954 p_n_fee_ci_seq_number => p_n_fee_ci_seq_number,
1955 p_n_waiver_amount => p_n_waiver_amount);
1956 END IF;
1957
1958 EXCEPTION
1959 WHEN OTHERS THEN
1960 -- Log the SQLERRM message
1961 log_to_fnd(p_v_module => 'create_ss_waiver_transactions.exception',
1962 p_v_string => ' Error: Unhandled Exception, returning E after rollback. SQLERRM: '||SQLERRM);
1963 ROLLBACK;
1964 -- Return the Error Status
1965 x_return_status := 'E';
1966
1967 END create_ss_waiver_transactions;
1968
1969 END igs_fi_wav_utils_001;
[Home] [Help]