1 PACKAGE BODY igs_fi_prc_fin_lt_chg AS
2 /* $Header: IGSFI69B.pls 120.3 2006/02/13 02:31:08 sapanigr noship $ */
3 ------------------------------------------------------------------
4 --Created by :Sarakshi , Oracle IDC
5 --Date created:05-Dec-2001
6 --
7 --Purpose: Package Body contains code for procedures/Functions defined in
8 -- package specification . Also body includes Functions/Procedures
9 -- private to it .
10 --
11 --Known limitations/enhancements and/or remarks:
12 --
13 --Change History:
14 --Who When What
15 --sapanigr 13-Feb-2006 Bug 5018036. Modified cursor in procedure calc_fin_lt_charge for R12 repository perf tuning.
16 --svuppala 04-AUG-2005 Enh 3392095 - Tution Waivers build
17 -- Impact of Charges API version Number change
18 -- Modified igs_fi_charges_api_pvt.create_charge - version 2.0 and x_waiver_amount
19 --pathipat 26-Apr-2004 Bug 3578249 - Modified calc_fin_lt_charge()
20 --vvutukur 20-Jan-2004 Bug#3348787.Modified calc_fin_lt_charge.
21 --vvutukur 20-Sep-2003 Enh#3045007.Payment Plans Build. Changes specific to Payment Plans TD.
22 --vvutukur 30-Jul-2003 Bug#3069929.Modified calc_red_balance.
23 --pathipat 24-Jun-2003 Bug: 3018104 - Impact of changes to person id group views
24 -- Replaced igs_pe_persid_group_v with igs_pe_persid_group in validate_persid_grp()
25 --shtatiko 22-MAR-2003 Enh# 2831569, Modified calc_fin_lt_charge
26 --vvutukur 12-Feb-2003 Bug#2731357.Modified calc_fin_lt_charge and lookup_desc.
27 --shtatiko 10-JAN-2003 Bug# 2731350, modiifed calc_fin_lt_charge
28 --pathipat 08-Jan-2003 Bug: 2690024 - modified calc_red_balance
29 --shtatiko 17-DEC-2002 Enh# 2584741, introduced l_adb_bal1 for accommodating NOCOPY issue
30 --vvutukur 17-DEC-2002 Enh# 2584741, Changed as per Deposits TD.
31 --vvutukur 24-Nov-2002 Enh#2584986.Added p_d_gl_date parameter to calc_fin_lt_charge procedure.
32 -- Modifications done in procedures calculate_charge,call_charges_api.
33 --shtatiko 08-OCT-2002 Bug# 2562745, Changed calc_fin_lt_charge, calculate_charge,
34 -- det_payable_balance and calc_red_balance.
35 -- Check corresponding procedures for exact changes.
36 --shtatiko 23-SEP-2002 Bug# 2564643, Removed References to Sub Account Id in all places.
37 --vchappid 03-Jun-2002 Bug# 2325427, Customized messages are shown to the user when there is no charge to
38 -- create.
39 --SYKRISHN 30-APR-2002 Bug 2348883 - Function validate_ftci - Cursor cur_val
40 -- modified to compare with fee structure status (system ststus)
41 --vchappid 19-Apr-2002 Bug#2313147, Date comparisions are done after eliminating time component,
42 -- implemented in all places where ever time component is not present
43 -- Billing Cutoff date comparision with the Charge Creation Date is Corrected
44 --SYKRISHN -5-APR-02 Enh Bug : 2293676 - Introduction of functionality of planned credits -
45 -- According to SFCR018 Build - Introduced the function get_planned_credits_ind
46 -- and associated code.
47 --sarakshi 27-Feb-2002 bug:2238362, changed the view igs_pe_person_v to igs_fi_parties_v and used the
48 -- function igs_fi_gen_007.validate_person to validate person, removed validate
49 -- person_id local function.
50 -------------------------------------------------------------------
51
52 -- Global cursor, added NVL condition as part of Enh# 2562745.
53 CURSOR cur_plan(cp_plan_name igs_fi_fin_lt_plan.plan_name%TYPE) IS
54 SELECT *
55 FROM igs_fi_fin_lt_plan
56 WHERE plan_name= cp_plan_name
57 AND NVL(closed_ind,'N')='N';
58
59
60 --Declaration of a ref cursor type.
61 TYPE cur_ref IS REF CURSOR;
62
63 --Added these valriables as part of Enh# 2562745. These are passed as OUT NOCOPY variables in call to
64 --finp_get_balance_rule.
65
66 g_balance_rule_id igs_fi_balance_rules.balance_rule_id%TYPE;
67 g_last_conversion_date igs_fi_balance_rules.last_conversion_date%TYPE;
68 g_version_number igs_fi_balance_rules.version_number%TYPE;
69
70 l_validation_exp exception;
71 g_v_flat_amt igs_fi_fin_lt_plan.accrual_type%TYPE := 'FLAT_AMOUNT';
72 g_v_flat_rate igs_fi_fin_lt_plan.accrual_type%TYPE := 'FLAT_RATE';
73 g_v_cutoff_dt igs_fi_fin_lt_plan.accrual_type%TYPE := 'AVG_DLY_BAL_CUTOFF_DT';
74 g_v_due_dt igs_fi_fin_lt_plan.accrual_type%TYPE := 'AVG_DLY_BAL_DUE_DT';
75 g_v_finance igs_fi_fin_lt_plan.plan_type%TYPE := 'INTEREST';
76 g_v_late igs_fi_fin_lt_plan.plan_type%TYPE := 'LATE';
77 g_d_sysdate DATE := TRUNC(SYSDATE);
78 g_v_hor_line VARCHAR2(100) := RPAD('-',77,'-');
79
80
81 -- Changes due to SFCR018
82 ---Local package function to get the value of planned credits indicator
83 FUNCTION get_planned_credits_ind
84 RETURN VARCHAR2
85 IS
86 /***************************************************************
87 Created By : SYkrishn
88 Date Created By : APR/05/2002
89 Purpose : Gets the value of planned credits indicator from IGS_FI_CONTROL_ALL.
90 Known Limitations,Enhancements or Remarks:
91 Change History :
92 Who When What
93
94 ***************************************************************/
95
96 l_v_planned_credits_ind igs_fi_control_all.planned_credits_ind%TYPE := NULL;
97 l_v_pln_cr_message fnd_new_messages.message_name%TYPE := NULL;
98
99 BEGIN
100 --Call the genric function to get the value
101 l_v_planned_credits_ind := igs_fi_gen_001.finp_get_planned_credits_ind(l_v_pln_cr_message);
102
103 IF l_v_pln_cr_message IS NOT NULL THEN
104 --Log error message and raise exception
105 fnd_message.set_name('IGS',l_v_pln_cr_message);
106 fnd_file.put_line(fnd_file.log,fnd_message.get());
107 fnd_file.put_line(fnd_file.log,' ');
108 RAISE l_validation_exp;
109 END IF;
110
111 RETURN l_v_planned_credits_ind;
112 END get_planned_credits_ind;
113 -- Changes due to SFCR018
114
115 FUNCTION validate_persid_grp(p_persid_grp_id IN igs_pe_persid_group.group_id%TYPE)
116 RETURN BOOLEAN AS
117 /***********************************************************************************************
118
119 Created By : Sarakshi
120 Date Created By: 05-Dec-2001
121 Purpose : Validates person Id Group
122
123 Known limitations,enhancements,remarks:
124 Change History
125 Who When What
126 vvutukur 25-Sep-2003 Enh#3045007.Payment Plans Build.modified cursor cur_val to validate
127 the dynamic person id group also.igs_pe_persid_group will have only
128 static person id groups.
129 pathipat 24-Jun-2003 Bug: 3018104 - Impact of changes to person id group views
130 Replaced igs_pe_persid_group_v with igs_pe_persid_group
131 vchappid 19-Apr-2002 Bug#2313147, Date comparisions are done after eliminating time component
132 ********************************************************************************************** */
133
134 CURSOR cur_val IS
135 SELECT 'X'
136 FROM igs_pe_persid_group_all
137 WHERE group_id = p_persid_grp_id
138 AND closed_ind = 'N'
139 AND TRUNC(create_dt) <= g_d_sysdate;
140 l_temp VARCHAR2(1);
141 BEGIN
142 OPEN cur_val;
143 FETCH cur_val INTO l_temp;
144 IF cur_val%FOUND THEN
145 CLOSE cur_val;
146 RETURN TRUE;
147 ELSE
148 CLOSE cur_val;
149 RETURN FALSE;
150 END IF;
151 END validate_persid_grp;
152
153 FUNCTION validate_plan_name(p_plan_name IN igs_fi_fin_lt_plan.plan_name%TYPE)
154 RETURN BOOLEAN AS
155 /***********************************************************************************************
156
157 Created By : Sarakshi
158 Date Created By: 05-Dec-2001
159 Purpose : Validates Plan Name
160
161 Known limitations,enhancements,remarks:
162 Change History
163 Who When What
164
165 ********************************************************************************************** */
166
167 CURSOR cur_val IS
168 SELECT 'X'
169 FROM igs_fi_fin_lt_plan
170 WHERE plan_name = p_plan_name
171 AND closed_ind = 'N';
172 l_temp VARCHAR2(1);
173 BEGIN
174 OPEN cur_val;
175 FETCH cur_val INTO l_temp;
176 IF cur_val%FOUND THEN
177 CLOSE cur_val;
178 RETURN TRUE;
179 ELSE
180 CLOSE cur_val;
181 RETURN FALSE;
182 END IF;
183 END validate_plan_name;
184
185 FUNCTION validate_ftci(p_cur_plan cur_plan%ROWTYPE,
186 p_cal_type IN igs_fi_f_typ_ca_inst_all.fee_cal_type%TYPE,
187 p_sequence_number IN igs_fi_f_typ_ca_inst_all.fee_ci_sequence_number%TYPE)
188 RETURN BOOLEAN AS
189 /***********************************************************************************************
190
191 Created By : Sarakshi
192 Date Created By: 05-Dec-2001
193 Purpose : Validates ftci
194
195 Known limitations,enhancements,remarks:
196 Change History
197 Who When What
198 SYkrishn 30/APR/2002 Modified the cursor cur_val to compare fee type ci status
199 with system status of Fee Structure status Bug 2348883
200 Sarakshi 15-Jan-2002 Modifies the cursor cur_val to remove subaccount_id check ,bug:2175865
201 ********************************************************************************************** */
202 CURSOR cur_val(cp_fee_type igs_fi_f_typ_ca_inst_all.fee_type%TYPE,
203 cp_fee_cal_type igs_fi_f_typ_ca_inst_all.fee_cal_type%TYPE,
204 cp_fee_ci_sequence_number igs_fi_f_typ_ca_inst_all.fee_ci_sequence_number%TYPE) IS
205 SELECT 'X'
206 FROM igs_fi_f_typ_ca_inst_all fcc,
207 -- bug 5018036: Changed igs_fi_f_typ_ca_inst_lkp_v to igs_fi_f_typ_ca_inst_all
208 igs_fi_fee_str_stat fss
209 WHERE fcc.fee_type = cp_fee_type
210 AND fcc.fee_cal_type = cp_fee_cal_type
211 AND fcc.fee_ci_sequence_number = cp_fee_ci_sequence_number
212 AND fcc.fee_type_ci_status = fss.fee_structure_status
213 AND fss.s_fee_structure_status = 'ACTIVE';
214 l_temp VARCHAR2(1);
215
216 BEGIN
217 OPEN cur_val(p_cur_plan.fee_type,p_cal_type,p_sequence_number);
218 FETCH cur_val INTO l_temp;
219 IF cur_val%FOUND THEN
220 CLOSE cur_val;
221 RETURN TRUE;
222 ELSE
223 CLOSE cur_val;
224 RETURN FALSE;
225 END IF;
226
227 END validate_ftci;
228
229 PROCEDURE log_person(p_person_id igs_pe_person_v.person_id%TYPE ) IS
230 /***********************************************************************************************
231
232 Created By : Sarakshi
233 Date Created By: 05-Dec-2001
234 Purpose : Logging person number and name
235
236 Known limitations,enhancements,remarks:
237 Change History
238 Who When What
239
240 ********************************************************************************************** */
241 CURSOR cur_person IS
242 SELECT person_number,full_name
243 --bug:2238362, changed the view igs_pe_person_v to igs_fi_parties_v
244 FROM igs_fi_parties_v
245 WHERE person_id= p_person_id;
246 l_cur_person cur_person%ROWTYPE;
247
248 BEGIN
249 OPEN cur_person;
250 FETCH cur_person INTO l_cur_person;
251 CLOSE cur_person;
252
253 fnd_file.put_line(fnd_file.log,g_v_hor_line);
254 fnd_message.set_name('IGS','IGS_FI_PROC_PERSON');
255 fnd_message.set_token('NUMBER',l_cur_person.person_number);
256 fnd_message.set_token('NAME',l_cur_person.full_name) ;
257 fnd_file.put_line(fnd_file.log,fnd_message.get);
258 END log_person;
259
260 PROCEDURE log_amount(p_amount igs_fi_balances.standard_balance%TYPE, p_msg_name VARCHAR2 ) IS
261 /***********************************************************************************************
262
263 Created By : Sarakshi
264 Date Created By: 05-Dec-2001
265 Purpose : To log the amount calculated
266
267 Known limitations,enhancements,remarks:
268 Change History
269 Who When What
270
271 ********************************************************************************************** */
272
273 BEGIN
274 fnd_message.set_name('IGS',p_msg_name);
275 fnd_message.set_token('AMOUNT',TO_CHAR(p_amount));
276 fnd_file.put_line(fnd_file.log,fnd_message.get);
277 END log_amount;
278
279 FUNCTION lookup_desc( p_type IN VARCHAR2 ,
280 p_code IN VARCHAR2 )
281 RETURN VARCHAR2 IS
282 /***********************************************************************************************
283
284 Created By : Sarakshi
285 Date Created By: 05-Dec-2001
286 Purpose : To fetch the meaning of a corresponding lookup code of a lookup type
287
288 Known limitations,enhancements,remarks:
289 Change History
290 Who When What
291 vvutukur 27-Feb-2003 Enh#2731357.Removed cursor which fetches the meaning of the lookup code and its usage.
292 Instead, used generic function which returns the same.
293 ********************************************************************************************** */
294
295 BEGIN
296
297 IF p_code IS NULL THEN
298 RETURN NULL;
299 ELSE
300 RETURN igs_fi_gen_gl.get_lkp_meaning(p_v_lookup_type => p_type,
301 p_v_lookup_code => p_code);
302 END IF ;
303
304 END lookup_desc;
305
306 PROCEDURE log_messages ( p_msg_name VARCHAR2 ,
307 p_msg_val VARCHAR2
308 ) IS
309 /***********************************************************************************************
310
311 Created By : Sarakshi
312 Date Created By: 05-Dec-2001
313 Purpose : To log the parameter and other important information obtained from plans table
314
315 Known limitations,enhancements,remarks:
316 Change History
317 Who When What
318
319 ********************************************************************************************** */
320 BEGIN
321 fnd_message.set_name('IGS','IGS_FI_CAL_BALANCES_LOG');
322 fnd_message.set_token('PARAMETER_NAME',p_msg_name);
323 fnd_message.set_token('PARAMETER_VAL' ,p_msg_val) ;
324 fnd_file.put_line(fnd_file.log,fnd_message.get);
325 END log_messages ;
326
327
328
329 PROCEDURE log_plan_info(p_cur_plan cur_plan%ROWTYPE, p_batch_cutoff_dt DATE,p_batch_due_date DATE,
330 p_cal_type igs_fi_f_typ_ca_inst_lkp_v.fee_cal_type%TYPE ,
331 p_sequence_number igs_fi_f_typ_ca_inst_lkp_v.fee_ci_sequence_number%TYPE,
332 p_batch_due_dt DATE )
333 AS
334 /***********************************************************************************************
335
336 Created By : Sarakshi
337 Date Created By: 05-Dec-2001
338 Purpose : To log the parameter and other important information obtained from plans table
339
340 Known limitations,enhancements,remarks:
341 Change History
342 Who When What
343 vvutukur 20-Sep-2003 Enh#3045007.Payment Plans Build. Changes specific to Payment Plans TD.
344 vchappid 03-Jun-02 Bug 2325427, Added all the plan details in the Log file
345 ********************************************************************************************** */
346
347 BEGIN
348
349 fnd_file.put_line(fnd_file.log,' ');
350 log_messages(lookup_desc('IGS_FI_LOCKBOX','PLAN_TYPE'),lookup_desc('IGS_FI_PLAN_TYPE',p_cur_plan.plan_type));
351 log_messages(lookup_desc('IGS_FI_LOCKBOX','BALANCE_TYPE'),
352 lookup_desc('IGS_FI_BALANCE_TYPE',p_cur_plan.balance_type));
353 log_messages(lookup_desc('IGS_FI_LOCKBOX','ACCRUAL_TYPE'),
354 lookup_desc('IGS_FI_ACCRUAL_TYPE',p_cur_plan.accrual_type));
355 log_messages(lookup_desc('IGS_FI_LOCKBOX','FEE_TYPE'),p_cur_plan.fee_type);
356
357 fnd_message.set_name('IGS','IGS_FI_OFFSET_DAYS');
358 fnd_message.set_token('DAYS',p_cur_plan.offset_days);
359 fnd_file.put_line(fnd_file.log,fnd_message.get);
360 fnd_file.put_line(fnd_file.log,'');
361
362 fnd_message.set_name('IGS','IGS_FI_CHARGE_RATE');
363 fnd_message.set_token('RATE',p_cur_plan.chg_rate);
364 fnd_file.put_line(fnd_file.log,fnd_message.get);
365 fnd_file.put_line(fnd_file.log,'');
366
367 fnd_message.set_name('IGS','IGS_FI_FLAT_AMOUNT');
368 fnd_message.set_token('AMT',p_cur_plan.flat_amount);
369 fnd_file.put_line(fnd_file.log,fnd_message.get);
370 fnd_file.put_line(fnd_file.log,'');
371
372 fnd_message.set_name('IGS','IGS_FI_MIN_BAL_AMOUNT');
373 fnd_message.set_token('BAL_AMT',p_cur_plan.min_balance_amount);
374 fnd_file.put_line(fnd_file.log,fnd_message.get);
375 fnd_file.put_line(fnd_file.log,'');
376
377 fnd_message.set_name('IGS','IGS_FI_MIN_CHG_AMOUNT');
378 fnd_message.set_token('CHG_AMT',p_cur_plan.min_charge_amount);
379 fnd_file.put_line(fnd_file.log,fnd_message.get);
380 fnd_file.put_line(fnd_file.log,'');
381
382 fnd_message.set_name('IGS','IGS_FI_MAX_CHG_AMOUNT');
383 fnd_message.set_token('CHG_AMT',p_cur_plan.max_charge_amount);
384 fnd_file.put_line(fnd_file.log,fnd_message.get);
385 fnd_file.put_line(fnd_file.log,'');
386
387 fnd_message.set_name('IGS','IGS_FI_MIN_AMT_NO_CHG');
388 fnd_message.set_token('CHG_AMT',p_cur_plan.min_charge_amount_no_charge);
389 fnd_file.put_line(fnd_file.log,fnd_message.get);
390
391 fnd_file.put_line(fnd_file.log,' ');
392 END log_plan_info;
393
394 PROCEDURE calc_red_balance (
395 p_person_id IN igs_pe_person.person_id%TYPE,
396 p_bal_start_dt IN DATE,
397 p_bal_end_dt IN DATE,
398 p_bal_type IN igs_fi_balance_rules.balance_name%TYPE,
399 p_open_bal IN NUMBER,
400 p_red_bal OUT NOCOPY NUMBER) IS
401 -------------------------------------------------------------------------------
402 -- Created By : Nishikant, Oracle IDC.
403 -- Date Created On : 11-DEC-2001
404 -- Purpose: The Procedure takes the period of time and balance at the start of
405 -- the period for a person as input. Then it processes all credits in
406 -- the given period and applies them to the opening balance to
407 -- calculate the Reduced Balance.
408 -- Change History
409 -- Who When What
410 -- vvutukur 30-Jul-2003 Bug#3069929.Modified cursor c_credits.
411 -- pathipat 08-Jan-2003 Bug: 2690024 - Removed exception section to prevent masking
412 -- Called check_Exclusion_rules and finp_get_total_planned_credits
413 -- in begin-end blocks to handle exceptions
414 -- shtatiko 08-OCT-2002 Removed cursor c_bal_type and its references in code. Enh# 2562745.
415 -- Added balance_rule_id parameter to check_exclusion_rules.
416 -- shtatiko 23-SEP-2002 Removal of subaccount_id from the parameter list and usage of the-- same in the code.
417 -- SYKRISHN 5/apr/2002 Changes due to SFCr018 - 2293676 -- Include planned credits
418 -- if the indicator is set as Y while deriving the reduced balance p_red_bal
419 -- (reverse chronological order - newest change first)
420 -------------------------------------------------------------------------------
421 CURSOR c_credits IS
422 SELECT credit_id, amount, effective_date
423 FROM igs_fi_credits cdt,
424 igs_fi_cr_types cty
425 WHERE TRUNC(effective_date)
426 BETWEEN TRUNC(p_bal_start_dt) AND TRUNC(p_bal_end_dt)
427 AND party_id = p_person_id
428 AND status = 'CLEARED'
429 AND cty.credit_type_id = cdt.credit_type_id
430 AND cty.credit_class NOT IN ('ENRDEPOSIT','OTHDEPOSIT');
431
432 -- An issue was found with the above cursor during the build of SFCR018 - the format mask for date was wrongly used
433 -- as DDMMYYYY - It was changed to YYYYMMDD
434 l_credit_amount NUMBER := 0;
435 l_person_id VARCHAR2(1);
436 l_bal_type VARCHAR2(1);
437 l_credits c_credits%ROWTYPE;
438 l_message_name fnd_new_messages.message_name%TYPE;
439 l_return_stat BOOLEAN;
440
441 -- Changes due to SFCR018
442 -- Call the local function to get the value of planned_credits_ind
443 l_v_pln_cr_ind igs_fi_control_all.planned_credits_ind%TYPE := get_planned_credits_ind;
444 l_v_pln_cr_message fnd_new_messages.message_name%TYPE := NULL;
445 l_n_planned_credit igs_fi_credits.amount%TYPE := 0;
446 -- Changes due to SFCR018
447
448 l_b_success_flag BOOLEAN; -- For bug 2690024
449
450 BEGIN
451
452 -- The below parameters are mandatory so they cannot be NULL
453 IF (p_person_id IS NULL OR
454 p_bal_start_dt IS NULL OR
455 p_bal_end_dt IS NULL OR
456 p_bal_type IS NULL OR
457 p_open_bal IS NULL ) THEN
458 fnd_message.set_name('IGS', 'IGS_GE_INVALID_VALUE');
459 igs_ge_msg_stack.add;
460 app_exception.raise_exception;
461 END IF;
462
463 -- The Person_id passed as parameter to the procedure is validated
464 IF igs_fi_gen_007.validate_person(p_person_id) = 'N' THEN
465 fnd_message.set_name('IGS', 'IGS_GE_INVALID_VALUE');
466 igs_ge_msg_stack.add;
467 app_exception.raise_exception;
468 END IF;
469
470 -- The parameter Balance Start Date should less than Balance End Date
471 IF p_bal_start_dt > p_bal_end_dt THEN
472 fnd_message.set_name('IGS', 'IGS_GE_INVALID_VALUE');
473 igs_ge_msg_stack.add;
474 app_exception.raise_exception;
475 END IF;
476
477 -- Select all Credits from the credits table for the person where the Effective
478 -- Date of the Credits falls in the input Period, which means between Balance
479 -- Start Date and Balance End Date.
480 FOR l_credits IN c_credits
481 LOOP
482
483 l_message_name := NULL;
484 l_return_stat := FALSE;
485
486 -- Calling Check Exclusion Rules procedure to check whether the credit found is
487 -- excluded or not.
488 BEGIN
489 --Added balance rule id parameter to check_exclusion_rules as part of Enh#2562745.
490 l_return_stat := igs_fi_prc_balances.check_exclusion_rules (
491 p_balance_type => p_bal_type,
492 p_balance_date => l_credits.effective_date,
493 p_source_type => 'CREDIT',
494 p_source_id => l_credits.credit_id,
495 p_message_name => l_message_name,
496 p_balance_rule_id => g_balance_rule_id );
497
498 -- The exception part is written here to handle if any exception raised in
499 -- the check Exclusion Rules procedure due to the above call.
500 EXCEPTION
501 WHEN OTHERS THEN
502 -- (pathipat) for bug 2690024 - removed NULL (masking of exception),
503 -- instead set flag to TRUE
504 l_return_stat := TRUE;
505 END;
506
507 -- If the Return value from the above call is False then the Credit is not
508 -- excluded. Then it sums up the credit amount found for the credit with
509 -- the previously found credit amounts, if any found earlier.
510
511 -- (pathipat) for bug 2690024 - brought the following code out of the begin-end
512 -- section.
513 IF NOT l_return_stat AND l_message_name IS NULL THEN
514 l_credit_amount := l_credit_amount + l_credits.amount;
515 END IF;
516
517 END LOOP;
518
519 -- Here the Reduced Balance is calculated by substracting the whole Credit Amount
520 -- from the Opening Balance. If no Credit Amount found then Opening Balance will
521 -- be the Reduced Balance.
522 p_red_bal := p_open_bal - l_credit_amount;
523
524 -- (pathipat) bug 2690024 - Called finp_get_total_planned_credits within a begin-end block
525 -- to handle exception without masking it.
526 l_b_success_flag := TRUE;
527
528 BEGIN
529 -- Changes due to SFCR018 - To include planned credits also when the indicator is set as 'Y'
530 IF l_v_pln_cr_ind = 'Y' THEN
531 --Call the generic function to get the total planned credits for the params passed.
532 l_n_planned_credit := igs_fi_gen_001.finp_get_total_planned_credits(
533 p_person_id => p_person_id,
534 p_start_date => p_bal_start_dt,
535 p_end_date => p_bal_end_dt,
536 p_message_name => l_v_pln_cr_message);
537 IF l_v_pln_cr_message IS NOT NULL THEN
538 l_b_success_flag := FALSE;
539 ELSE
540 l_b_success_flag := TRUE;
541 END IF;
542 END IF;
543
544 EXCEPTION
545 WHEN OTHERS THEN
546 l_b_success_flag := FALSE;
547
548 END;
549
550 IF l_b_success_flag = FALSE THEN
551 fnd_message.set_name('IGS',l_v_pln_cr_message);
552 fnd_file.put_line(fnd_file.log,fnd_message.get());
553 fnd_file.put_line(FND_FILE.LOG,' ');
554 ELSE
555 -- When no errors reduce the balance with the sum of planned credits.
556 p_red_bal := p_red_bal - NVL(l_n_planned_credit,0);
557 END IF;
558 -- Changes due to SFCR018 - To include planned credits also when the indicator is set as 'Y'
559
560
561 END calc_red_balance;
562
563 PROCEDURE call_charges_api(p_cur_plan IN cur_plan%ROWTYPE,
564 p_person_id IN igs_pe_person_v.person_id%TYPE,
565 p_charge_amount IN igs_fi_balances.standard_balance%TYPE,
566 p_chg_crtn_dt IN DATE,
567 p_cal_type IN igs_fi_f_typ_ca_inst_v.fee_cal_type%TYPE,
568 p_sequence_number IN igs_fi_f_typ_ca_inst_v.fee_ci_sequence_number%TYPE,
569 p_d_gl_date IN igs_fi_credits_all.gl_date%TYPE,
570 p_n_std_plan_id IN igs_fi_pp_std_attrs.student_plan_id%TYPE,
571 p_d_inst_due_date IN igs_fi_pp_instlmnts.due_date%TYPE,
572 p_b_chrg_flag OUT NOCOPY BOOLEAN
573 )AS
574 /***********************************************************************************************
575
576 Created By : Sarakshi
577 Date Created By: 05-Dec-2001
578 Purpose : To create charges.
579
580 Known limitations,enhancements,remarks:
581 Change History
582 Who When What
583 svuppala 04-AUG-2005 Enh 3392095 - Tution Waivers build
584 Impact of Charges API version Number change
585 Modified igs_fi_charges_api_pvt.create_charge - version 2.0 and x_waiver_amount
586 vvutukur 20-Sep-2003 Enh#3045007.Payment Plans Build. Changes specific to Payment Plans TD.
587 vvutukur 24-Nov-2002 Enh#2584986.Added p_d_gl_date parameter and passed the same to the call to charges api.
588 Removed references to igs_fi_cur as the same has been obsoleted and captured the local
589 currency from System Options Form to be passed to charges api.
590 vchappid 19-APR-2002 Bug#2313147, Amount in the invoice lines table was passed as NULL, Corrected to
591 Charge Amount that is calculated
592 ********************************************************************************************** */
593
594
595 l_chg_rec igs_fi_charges_api_pvt.Header_Rec_Type;
596 l_chg_line_tbl igs_fi_charges_api_pvt.Line_Tbl_Type;
597 l_line_tbl igs_fi_charges_api_pvt.Line_Id_Tbl_Type;
598 l_invoice_id igs_fi_inv_int.Invoice_Id%TYPE;
599 l_ret_status VARCHAR2(1);
600 l_msg_count NUMBER(5);
601 l_msg_data VARCHAR2(2000);
602 l_var NUMBER(5) := 0;
603 l_count NUMBER(5);
604 l_msg VARCHAR2(2000);
605
606 CURSOR cur_desc(cp_fee_type igs_fi_fee_type.fee_type%TYPE) IS
607 SELECT description
608 FROM igs_fi_fee_type
609 WHERE fee_type = cp_fee_type;
610
611 l_cur_desc igs_fi_inv_int.invoice_desc%TYPE;
612
613 l_v_currency igs_fi_control_all.currency_cd%TYPE;
614 l_v_curr_desc fnd_currencies_tl.name%TYPE;
615 l_v_message_name fnd_new_messages.message_name%TYPE;
616 l_v_tran_type igs_lookup_values.meaning%TYPE := lookup_desc('TRANSACTION_TYPE',p_cur_plan.plan_type);
617 l_v_pmt_plan igs_lookup_values.meaning%TYPE := lookup_desc('IGS_FI_LOCKBOX','PAYMENT_PLAN');
618 l_v_instl_due igs_lookup_values.meaning%TYPE := lookup_desc('IGS_FI_LOCKBOX','INSTALLMENT_DUE');
619
620 l_n_waiver_amount NUMBER;
621 BEGIN
622
623 p_b_chrg_flag := TRUE;
624
625 IF p_n_std_plan_id IS NULL THEN
626 --fetching the fee type description
627 OPEN cur_desc(p_cur_plan.fee_type);
628 FETCH cur_desc INTO l_cur_desc;
629 CLOSE cur_desc;
630 ELSE
631 IF p_cur_plan.plan_type = g_v_finance THEN
632 l_cur_desc := l_v_tran_type||' '||l_v_pmt_plan;
633 ELSIF p_cur_plan.plan_type = g_v_late THEN
634 l_cur_desc := l_v_tran_type||' '||l_v_instl_due||' '||TO_CHAR(p_d_inst_due_date,'Month DD, YYYY');
635 END IF;
636 END IF;
637
638 --Capture the default currency that is set up in System Options Form.
639 igs_fi_gen_gl.finp_get_cur( p_v_currency_cd => l_v_currency,
640 p_v_curr_desc => l_v_curr_desc,
641 p_v_message_name => l_v_message_name
642 );
643 IF l_v_message_name IS NOT NULL THEN
644 fnd_message.set_name('IGS',l_v_message_name);
645 fnd_msg_pub.add;
646 p_b_chrg_flag := FALSE;
647 RAISE fnd_api.g_exc_error;
648 END IF;
649
650 l_chg_rec.p_person_id := p_person_id;
651 l_chg_rec.p_fee_type := p_cur_plan.fee_type;
652 l_chg_rec.p_fee_cat := NULL;
653 l_chg_rec.p_fee_cal_type := p_cal_type;
654 l_chg_rec.p_fee_ci_sequence_number := p_sequence_number;
655 l_chg_rec.p_course_cd := NULL;
656 l_chg_rec.p_attendance_type := NULL;
657 l_chg_rec.p_attendance_mode := NULL;
658 l_chg_rec.p_invoice_amount := p_charge_amount;
659 l_chg_rec.p_invoice_creation_date := p_chg_crtn_dt;
660 l_chg_rec.p_invoice_desc := l_cur_desc;
661 l_chg_rec.p_transaction_type := p_cur_plan.plan_type;
662 l_chg_rec.p_currency_cd := l_v_currency;
663 l_chg_rec.p_exchange_rate := 1;
664 l_chg_rec.p_effective_date := p_chg_crtn_dt;
665 l_chg_rec.p_waiver_flag := NULL;
666 l_chg_rec.p_waiver_reason := NULL;
667 l_chg_rec.p_source_transaction_id := NULL;
668
669
670 l_chg_line_tbl(1).p_s_chg_method_type := NULL;
671 l_chg_line_tbl(1).p_description := l_cur_desc;
672 l_chg_line_tbl(1).p_chg_elements := NULL;
673 l_chg_line_tbl(1).p_amount := p_charge_amount;
674 l_chg_line_tbl(1).p_unit_attempt_status := NULL;
675 l_chg_line_tbl(1).p_eftsu := NULL;
676 l_chg_line_tbl(1).p_credit_points := NULL;
677 l_chg_line_tbl(1).p_org_unit_cd := NULL;
678 l_chg_line_tbl(1).p_attribute_category := NULL;
679 l_chg_line_tbl(1).p_attribute1 := NULL;
680 l_chg_line_tbl(1).p_attribute2 := NULL;
681 l_chg_line_tbl(1).p_attribute3 := NULL;
682 l_chg_line_tbl(1).p_attribute4 := NULL;
683 l_chg_line_tbl(1).p_attribute5 := NULL;
684 l_chg_line_tbl(1).p_attribute6 := NULL;
685 l_chg_line_tbl(1).p_attribute7 := NULL;
686 l_chg_line_tbl(1).p_attribute8 := NULL;
687 l_chg_line_tbl(1).p_attribute9 := NULL;
688 l_chg_line_tbl(1).p_attribute10 := NULL;
689 l_chg_line_tbl(1).p_attribute11 := NULL;
690 l_chg_line_tbl(1).p_attribute12 := NULL;
691 l_chg_line_tbl(1).p_attribute13 := NULL;
692 l_chg_line_tbl(1).p_attribute14 := NULL;
693 l_chg_line_tbl(1).p_attribute15 := NULL;
694 l_chg_line_tbl(1).p_attribute16 := NULL;
695 l_chg_line_tbl(1).p_attribute17 := NULL;
696 l_chg_line_tbl(1).p_attribute18 := NULL;
697 l_chg_line_tbl(1).p_attribute19 := NULL;
698 l_chg_line_tbl(1).p_attribute20 := NULL;
699 l_chg_line_tbl(1).p_location_cd := NULL;
700 l_chg_line_tbl(1).p_uoo_id := NULL;
701 l_chg_line_tbl(1).p_d_gl_date := p_d_gl_date;
702
703
704 igs_fi_charges_api_pvt.Create_Charge(p_api_version => 2.0,
705 p_init_msg_list => 'T',
706 p_commit => 'F',
707 p_validation_level => NULL,
708 p_header_rec => l_chg_rec,
709 p_line_tbl => l_chg_line_tbl,
710 x_invoice_id => l_invoice_id,
711 x_line_id_tbl => l_line_tbl,
712 x_return_status => l_ret_status,
713 x_msg_count => l_msg_count,
714 x_msg_data => l_msg_data,
715 x_waiver_amount => l_n_waiver_amount);
716
717 IF l_ret_status <> 'S' THEN
718 IF l_msg_count = 1 THEN
719 fnd_message.set_encoded(l_msg_data);
720 fnd_file.put_line(fnd_file.log,fnd_message.get);
721 ELSE
722 FOR l_count IN 1 .. l_msg_count LOOP
723 l_msg := fnd_msg_pub.get(p_msg_index => l_count, p_encoded => 'T');
724 fnd_message.set_encoded(l_msg);
725 fnd_file.put_line(fnd_file.log,fnd_message.get);
726 END LOOP;
727 END IF;
728 p_b_chrg_flag := FALSE;
729 END IF;
730 END call_charges_api;
731
732
733 PROCEDURE log_dtls_create_charge(p_cur_plan IN cur_plan%ROWTYPE,
734 p_n_person_id IN PLS_INTEGER,
735 p_n_std_plan_id IN PLS_INTEGER,
736 p_b_grt_min_bal_amt IN BOOLEAN,
737 p_n_charge_amount IN NUMBER,
738 p_d_chg_crtn_dt IN DATE,
739 p_v_cal_type IN VARCHAR2,
740 p_n_sequence_number IN PLS_INTEGER,
741 p_d_gl_date IN DATE,
742 p_d_inst_due_date IN DATE,
743 p_v_test_flag IN VARCHAR2,
744 p_b_chrg_flag OUT NOCOPY BOOLEAN
745 ) AS
746 /***********************************************************************************************
747
748 Created By : vvutukur, Oracle India
749 Date Created By: 17-Sep-2003
750 Purpose : To log the amount calculated and amount to be charged. Also
751 this procedure calls another local procedure to create the charge.
752 This procedure is being created as part of the modifications for
753 Payment Plans Build.(Enh#3045007).
754
755 Known limitations,enhancements,remarks:
756 Change History
757 Who When What
758 ************************************************************************************************/
759
760 CURSOR cur_bill(cp_n_person_id igs_fi_parties_v.person_id%TYPE) IS
761 SELECT cut_off_date
762 FROM igs_fi_bill
763 WHERE person_id = cp_n_person_id
764 ORDER BY bill_date DESC;
765
766 l_cur_bill cur_bill%ROWTYPE;
767 l_n_min_chg_amt_no_chg igs_fi_fin_lt_plan.payment_plan_minchgamt_nochg%TYPE;
768 l_n_min_chrg_amt igs_fi_fin_lt_plan.payment_plan_min_charge_amt%TYPE;
769 l_n_max_chrg_amt igs_fi_fin_lt_plan.payment_plan_max_charge_amt%TYPE;
770 l_v_accrual_type igs_fi_fin_lt_plan.accrual_type%TYPE;
771 l_n_charge_amount NUMBER := 0;
772 l_b_chrg_flag BOOLEAN := TRUE;
773
774 BEGIN
775
776 l_v_accrual_type := p_cur_plan.accrual_type;
777 l_n_min_chg_amt_no_chg := p_cur_plan.min_charge_amount_no_charge;
778 l_n_min_chrg_amt := p_cur_plan.min_charge_amount;
779 l_n_max_chrg_amt := p_cur_plan.max_charge_amount;
780
781 IF (p_n_std_plan_id IS NOT NULL AND p_cur_plan.payment_plan_accrl_type_code IS NOT NULL) THEN
782 l_v_accrual_type := NVL(p_cur_plan.payment_plan_accrl_type_code,l_v_accrual_type);
783 l_n_max_chrg_amt := NVL(p_cur_plan.payment_plan_max_charge_amt,l_n_max_chrg_amt);
784 l_n_min_chrg_amt := NVL(p_cur_plan.payment_plan_min_charge_amt,l_n_min_chrg_amt);
785 l_n_min_chg_amt_no_chg := NVL(p_cur_plan.payment_plan_minchgamt_nochg,l_n_min_chg_amt_no_chg);
786 END IF;
787
788 l_n_charge_amount := NVL(p_n_charge_amount,0);
789
790 --Logging the calculated amount
791 log_amount(l_n_charge_amount,'IGS_FI_CALC_AMNT');
792
793 p_b_chrg_flag := TRUE;
794
795 IF (l_n_charge_amount <= 0 AND p_b_grt_min_bal_amt) THEN
796 fnd_file.put_line(fnd_file.log,fnd_message.get_string('IGS','IGS_FI_NO_CHG_APP'));
797 p_b_chrg_flag := FALSE;
798 RETURN;
799 END IF;
800
801 --Incase the flag p_b_grt_min_bal_amt is FALSE, no need to proceed for creating a charge.
802 IF p_cur_plan.plan_type = g_v_late AND p_b_grt_min_bal_amt = FALSE THEN
803 fnd_file.put_line(fnd_file.log,fnd_message.get_string('IGS','IGS_FI_NO_CHG_APP_MIN_BAL'));
804 p_b_chrg_flag := FALSE;
805 RETURN;
806 END IF;
807
808 --Validate the charge amount against Min Charge Amount, Max Charge Amount, Min Charge Amount No charge Amount
809 --only when the plan type is not late and accrual type is not Flat Amount.
810 --Because only in that particular case only, these three fields will not have values.
811 IF (NOT((p_cur_plan.plan_type = g_v_late) AND (l_v_accrual_type = g_v_flat_amt))) THEN
812 IF l_n_charge_amount < NVL(l_n_min_chg_amt_no_chg,l_n_charge_amount) THEN
813 fnd_file.put_line(fnd_file.log,fnd_message.get_string('IGS','IGS_FI_NO_CHG_APP_MIN_CHG'));
814 p_b_chrg_flag := FALSE;
815 RETURN;
816 END IF;
817
818 --If the charge amount is less than min charge amount, then amount to be charged = min charge amount.
819 IF l_n_charge_amount < NVL(l_n_min_chrg_amt,l_n_charge_amount) THEN
820 l_n_charge_amount := l_n_min_chrg_amt;
821 END IF;
822
823 --If the charge amount is greater than max charge amount, then amount to be charged = max charge amount.
824 IF l_n_charge_amount > NVL(l_n_max_chrg_amt,l_n_charge_amount) THEN
825 l_n_charge_amount := l_n_max_chrg_amt;
826 END IF;
827 END IF;
828
829 --Logging the amount to be charged
830 log_amount(l_n_charge_amount,'IGS_FI_CHG_AMNT');
831
832 --If the student is not on an active payment, then only the following validation
833 --related with charge creation date and latest bill generated cutoff date should happen.
834 IF p_n_std_plan_id IS NULL THEN
835 --validate input charge creation date with ths latest bill generated cutoff date
836 OPEN cur_bill(p_n_person_id);
837 FETCH cur_bill INTO l_cur_bill;
838 IF cur_bill%FOUND THEN
839 IF p_d_chg_crtn_dt < l_cur_bill.cut_off_date THEN
840 fnd_file.put_line(fnd_file.log,fnd_message.get_string('IGS','IGS_FI_CHG_BILL'));
841 p_b_chrg_flag := FALSE;
842 CLOSE cur_bill;
843 RETURN;
844 END IF;
845 END IF;
846 CLOSE cur_bill;
847 END IF;
848
849 --If it is not a test run then creating charges, i.e p_test_flag='N'
850 IF p_v_test_flag = 'N' THEN
851 call_charges_api(p_cur_plan => p_cur_plan,
852 p_person_id => p_n_person_id,
853 p_charge_amount => l_n_charge_amount,
854 p_chg_crtn_dt => p_d_chg_crtn_dt,
855 p_cal_type => p_v_cal_type,
856 p_sequence_number => p_n_sequence_number,
857 p_d_gl_date => p_d_gl_date,
858 p_n_std_plan_id => p_n_std_plan_id,
859 p_d_inst_due_date => p_d_inst_due_date,
860 p_b_chrg_flag => l_b_chrg_flag
861 );
862 --If charges api returns any error and the charge is not created,l_b_chrg_flag will be FALSE.
863 --otherwise, l_b_chrg_flag will be TRUE.
864 p_b_chrg_flag := l_b_chrg_flag;
865 END IF;
866 END log_dtls_create_charge;
867
868 FUNCTION get_pp_cul_dly_bal(p_d_bal_start_dt IN DATE,
869 p_n_open_bal IN NUMBER,
870 p_n_std_plan_id IN PLS_INTEGER) RETURN NUMBER AS
871 /***********************************************************************************************
872
873 Created By : vvutukur, Oracle India.
874 Date Created By: 18-Sep-2003
875 Purpose : To calculate the cumulative daily balance.
876
877 Known limitations,enhancements,remarks:
878 Change History
879 Who When What
880 ********************************************************************************************** */
881 CURSOR cur_get_inst_dtls(cp_n_std_plan_id NUMBER,
882 cp_d_bal_start_dt DATE
883 ) IS
884 SELECT due_amt, due_date, (g_d_sysdate -(TRUNC(due_date)-1)) num_days
885 FROM igs_fi_pp_instlmnts
886 WHERE student_plan_id = cp_n_std_plan_id
887 AND TRUNC(due_date) BETWEEN TRUNC(cp_d_bal_start_dt) AND g_d_sysdate
888 AND due_amt > 0
889 ORDER BY due_date;
890
891 l_cur_get_inst_dtls cur_get_inst_dtls%ROWTYPE;
892 l_n_cul_bal NUMBER;
893
894 BEGIN
895
896 l_n_cul_bal := p_n_open_bal * ((g_d_sysdate - p_d_bal_start_dt) + 1);
897
898 OPEN cur_get_inst_dtls(p_n_std_plan_id,p_d_bal_start_dt);
899 LOOP
900 FETCH cur_get_inst_dtls INTO l_cur_get_inst_dtls;
901 EXIT WHEN cur_get_inst_dtls%NOTFOUND;
902 l_n_cul_bal := l_n_cul_bal + NVL(l_cur_get_inst_dtls.due_amt,0) * NVL(l_cur_get_inst_dtls.num_days,0);
903 END LOOP;
904 CLOSE cur_get_inst_dtls;
905 RETURN l_n_cul_bal;
906
907 END get_pp_cul_dly_bal;
908
909
910 FUNCTION det_payable_balance(p_person_id IN igs_fi_balances.party_id%TYPE,
911 p_cur_plan IN cur_plan%ROWTYPE,
912 p_batch_cutoff_dt IN DATE)
913 RETURN NUMBER AS
914 /***********************************************************************************************
915
916 Created By : Sarakshi
917 Date Created By: 05-Dec-2001
918 Purpose : Obtain the payable balance for a person .
919
920 Known limitations,enhancements,remarks:
921 Change History
922 Who When What
923 shtatiko 08-OCT-2002 Changed cur_get_bal so that it includes only information related to
924 FEE balance type.
925 ********************************************************************************************** */
926 CURSOR cur_get_bal IS
927 SELECT fee_balance amount
928 FROM igs_fi_balances
929 WHERE party_id = p_person_id
930 AND balance_date <= p_batch_cutoff_dt
931 AND fee_balance IS NOT NULL
932 ORDER BY balance_date DESC;
933
934 l_cur_get_bal cur_get_bal%ROWTYPE;
935
936 BEGIN
937 OPEN cur_get_bal;
938 FETCH cur_get_bal INTO l_cur_get_bal;
939 IF cur_get_bal%NOTFOUND THEN
940 CLOSE cur_get_bal;
941 RETURN 0;
942 ELSE
943 CLOSE cur_get_bal;
944 RETURN NVL(l_cur_get_bal.amount,0);
945 END IF;
946 END det_payable_balance;
947
948 PROCEDURE calculate_late_fee_charge(p_overdue_bal IN igs_fi_balances.standard_balance%TYPE,
949 p_cur_plan IN cur_plan%ROWTYPE,
950 p_n_person_id IN PLS_INTEGER,
951 p_n_std_plan_id IN PLS_INTEGER,
952 p_d_chg_crtn_dt IN DATE,
953 p_v_fee_cal_type IN VARCHAR2,
954 p_n_fee_sequence_number IN PLS_INTEGER,
955 p_d_gl_date IN DATE,
956 p_v_test_flag IN VARCHAR2
957 ) AS
958 /***********************************************************************************************
959
960 Created By : Sarakshi
961 Date Created By: 05-Dec-2001
962 Purpose : To obtain the amount for a late fee.
963
964 Known limitations,enhancements,remarks:
965 Change History
966 Who When What
967 vvutukur 17-Sep-2003 Enh#3045007.Payment Plans Build. Changes specific to Payment Plans TD.
968 vchappid 04-Jun-2002 Bug# 2325427, for logging different messages when the overdue balance
969 is greater than minimum balance amount
970 ********************************************************************************************** */
971
972 CURSOR cur_get_instal_details(cp_n_person_id NUMBER,
973 cp_n_std_plan_id NUMBER,
974 cp_v_penalty_flag VARCHAR2,
975 cp_d_due_date DATE) IS
976 SELECT pp.rowid,pp.*
977 FROM igs_fi_pp_instlmnts pp
978 WHERE pp.student_plan_id = cp_n_std_plan_id
979 AND TRUNC(pp.due_date) <= cp_d_due_date
980 AND pp.due_amt > 0
981 AND NVL(pp.penalty_flag,'N') = cp_v_penalty_flag;
982
983 l_b_grt_min_bal_amt BOOLEAN;
984 l_n_charge_amount NUMBER := 0;
985 l_d_effective_dt DATE;
986 l_n_chg_rate igs_fi_fin_lt_plan.payment_plan_chg_rate%TYPE;
987 l_b_chrg_flag BOOLEAN;
988 l_b_inst_exists BOOLEAN := FALSE;
989
990 BEGIN
991
992 IF p_n_std_plan_id IS NULL THEN --if the person is NOT on an active payment plan..
993
994 IF p_overdue_bal >= NVL(p_cur_plan.min_balance_amount,0) THEN
995 l_b_grt_min_bal_amt := TRUE;
996 IF p_cur_plan.accrual_type = g_v_flat_amt THEN
997 l_n_charge_amount := NVL(p_cur_plan.flat_amount,0);
998 ELSIF p_cur_plan.accrual_type = g_v_flat_rate THEN
999 l_n_charge_amount := ROUND((NVL(p_cur_plan.chg_rate,0) * p_overdue_bal)/100,2);
1000 END IF;
1001 ELSE
1002 -- overdue balance is less than the minimum balance amount
1003 l_b_grt_min_bal_amt := FALSE;
1004 END IF;
1005
1006 --invoke the local procedure for generating the log and creating charge.
1007 log_dtls_create_charge(p_cur_plan => p_cur_plan,
1008 p_n_person_id => p_n_person_id,
1009 p_n_std_plan_id => NULL,
1010 p_b_grt_min_bal_amt => l_b_grt_min_bal_amt,
1011 p_n_charge_amount => l_n_charge_amount,
1012 p_d_chg_crtn_dt => p_d_chg_crtn_dt,
1013 p_v_cal_type => p_v_fee_cal_type,
1014 p_n_sequence_number => p_n_fee_sequence_number,
1015 p_d_gl_date => p_d_gl_date,
1016 p_d_inst_due_date => NULL,
1017 p_v_test_flag => p_v_test_flag,
1018 p_b_chrg_flag => l_b_chrg_flag
1019 );
1020
1021 ELSE --if the person is on an active payment plan.
1022
1023 --Get the effective date to get the installment details.
1024 IF p_cur_plan.offset_days IS NOT NULL THEN
1025 l_d_effective_dt := g_d_sysdate - p_cur_plan.offset_days;
1026 ELSE
1027 l_d_effective_dt := g_d_sysdate;
1028 END IF;
1029
1030 --Check if the balance as on the determination date is greater than zero,
1031 --otherwise log error message.
1032 IF NVL(igs_fi_gen_008.get_plan_balance(p_n_std_plan_id,l_d_effective_dt),0) <= 0 THEN
1033 fnd_message.set_name('IGS','IGS_FI_NO_CHG_APP_DUE_DT');
1034 fnd_file.put_line(fnd_file.log,fnd_message.get);
1035 RETURN;
1036 END IF;
1037
1038 --Get the charge rate.If the payment plan charge rate is not defined at, get the default charge rate.
1039 l_n_chg_rate := NVL(NVL(p_cur_plan.payment_plan_chg_rate,p_cur_plan.chg_rate),0);
1040
1041 l_b_inst_exists := FALSE;
1042
1043 --for each installment due a charge needs to be created
1044 FOR rec_cur_get_inst_details IN cur_get_instal_details(p_n_person_id,p_n_std_plan_id,'N',l_d_effective_dt) LOOP
1045
1046 l_b_inst_exists := TRUE;
1047
1048 --If the installment due amount is greater than the minimum balance amount, then..
1049 IF (rec_cur_get_inst_details.due_amt > NVL(NVL(p_cur_plan.payment_plan_min_balance_amt,p_cur_plan.min_balance_amount),0)) THEN
1050
1051 l_b_grt_min_bal_amt := TRUE;
1052
1053 --Get the charge amount depending on the accrual type(If not defined at Payment Plan, get from Default).
1054 IF (NVL(p_cur_plan.payment_plan_accrl_type_code,p_cur_plan.accrual_type))= g_v_flat_amt THEN
1055 l_n_charge_amount := NVL(NVL(p_cur_plan.payment_plan_flat_amt,p_cur_plan.flat_amount),0);
1056 ELSIF (NVL(p_cur_plan.payment_plan_accrl_type_code, p_cur_plan.accrual_type)) = g_v_flat_rate THEN
1057 l_n_charge_amount := ROUND((l_n_chg_rate * rec_cur_get_inst_details.due_amt)/100,2);
1058 END IF;
1059 ELSE
1060 l_b_grt_min_bal_amt := FALSE;
1061 END IF;
1062
1063 l_b_chrg_flag := FALSE;
1064
1065 --invoke the local procedure for generating the log and creating charge.
1066 log_dtls_create_charge( p_cur_plan => p_cur_plan,
1067 p_n_person_id => p_n_person_id,
1068 p_n_std_plan_id => p_n_std_plan_id,
1069 p_b_grt_min_bal_amt => l_b_grt_min_bal_amt,
1070 p_n_charge_amount => l_n_charge_amount,
1071 p_d_chg_crtn_dt => p_d_chg_crtn_dt,
1072 p_v_cal_type => p_v_fee_cal_type,
1073 p_n_sequence_number => p_n_fee_sequence_number,
1074 p_d_gl_date => p_d_gl_date,
1075 p_d_inst_due_date => rec_cur_get_inst_details.due_date,
1076 p_v_test_flag => p_v_test_flag,
1077 p_b_chrg_flag => l_b_chrg_flag
1078 );
1079 --Once the log is generated and charge is created, if the process is invoked with test run parameter as 'No',
1080 --then the penalty flag of the installment record should be updated to 'Y'.
1081 IF p_v_test_flag = 'N' AND l_b_chrg_flag THEN
1082 BEGIN
1083 igs_fi_pp_instlmnts_pkg.update_row(
1084 x_rowid => rec_cur_get_inst_details.rowid,
1085 x_installment_id => rec_cur_get_inst_details.installment_id,
1086 x_student_plan_id => rec_cur_get_inst_details.student_plan_id,
1087 x_installment_line_num => rec_cur_get_inst_details.installment_line_num,
1088 x_due_day => rec_cur_get_inst_details.due_day,
1089 x_due_month_code => rec_cur_get_inst_details.due_month_code,
1090 x_due_year => rec_cur_get_inst_details.due_year,
1091 x_due_date => rec_cur_get_inst_details.due_date,
1092 x_installment_amt => rec_cur_get_inst_details.installment_amt,
1093 x_due_amt => rec_cur_get_inst_details.due_amt,
1094 x_penalty_flag => 'Y',
1095 x_mode => 'R'
1096 );
1097 EXCEPTION
1098 WHEN OTHERS THEN
1099 fnd_file.put_line(fnd_file.log,SQLERRM);
1100 END;
1101 END IF;
1102 END LOOP;
1103
1104 --If there are no installments considered for Late charge Calculation, log message.
1105 IF NOT l_b_inst_exists THEN
1106 fnd_message.set_name('IGS','IGS_FI_PP_LT_NO_RECS');
1107 fnd_file.put_line(fnd_file.log,fnd_message.get);
1108 END IF;
1109 END IF;
1110
1111 END calculate_late_fee_charge;
1112
1113
1114
1115
1116 PROCEDURE calculate_finance_charge( p_cur_plan IN cur_plan%ROWTYPE,
1117 p_payable_bal IN igs_fi_balances.standard_balance%TYPE,
1118 p_overdue_bal IN igs_fi_balances.standard_balance%TYPE,
1119 p_batch_cutoff_dt IN DATE,
1120 p_batch_due_dt IN DATE,
1121 p_person_id IN igs_pe_person_v.person_id%TYPE,
1122 p_n_std_plan_id IN PLS_INTEGER,
1123 p_d_chg_crtn_dt IN DATE,
1124 p_v_fee_cal_type IN VARCHAR2,
1125 p_n_fee_sequence_number IN PLS_INTEGER,
1126 p_d_gl_date IN DATE,
1127 p_v_test_flag IN VARCHAR2
1128 ) AS
1129 /***********************************************************************************************
1130
1131 Created By : Sarakshi
1132 Date Created By: 05-Dec-2001
1133 Purpose : To obtain the amount for finance charge.
1134
1135 Known limitations,enhancements,remarks:
1136 Change History
1137 Who When What
1138 vvutukur 20-Sep-2003 Enh#3045007.Payment Plans Build. Changes specific to Payment Plans TD.
1139 shtatiko 17-DEC-2002 Enh# 2584741, Introduced l_adb_bal1
1140 vchappid 19-Apr-2002 Bug#2313147, Date comparisions are done after eliminating time component
1141 ********************************************************************************************** */
1142 l_overdue_bal igs_fi_balances.standard_balance%TYPE;
1143 l_adb_start_dt DATE;
1144 l_adb_bal igs_fi_balances.standard_balance%TYPE;
1145 l_adb_total_bal igs_fi_balances.standard_balance%TYPE := 0;
1146 l_adb_track_dt DATE;
1147 l_adb_bal1 igs_fi_balances.standard_balance%TYPE := 0;
1148
1149 l_n_charge_amount NUMBER := 0;
1150 l_n_charge_rate NUMBER := 0;
1151 l_d_start_dt DATE;
1152 l_d_eff_date DATE;
1153
1154 l_b_chrg_flag BOOLEAN;
1155
1156 BEGIN
1157
1158 l_n_charge_rate := NVL(p_cur_plan.chg_rate,0);
1159
1160 --if the person is NOT on an Active Payment Plan..
1161 IF p_n_std_plan_id IS NULL THEN
1162 --calculate ADB bal and ADB start date depending upon accrual type
1163 IF p_cur_plan.accrual_type = g_v_cutoff_dt THEN
1164 l_adb_bal := p_payable_bal;
1165 l_adb_start_dt := p_batch_cutoff_dt + 1;
1166 ELSIF p_cur_plan.accrual_type = g_v_due_dt THEN
1167 IF p_cur_plan.offset_days > 0 THEN
1168 calc_red_balance(p_person_id => p_person_id,
1169 p_bal_start_dt => p_batch_cutoff_dt + 1,
1170 p_bal_end_dt => p_batch_due_dt,
1171 p_bal_type => p_cur_plan.balance_type,
1172 p_open_bal => p_payable_bal,
1173 p_red_bal => l_overdue_bal
1174 );
1175 l_adb_bal := l_overdue_bal;
1176 ELSE
1177 l_adb_bal := p_overdue_bal;
1178 END IF;
1179 l_adb_start_dt := p_batch_due_dt + 1;
1180 END IF;
1181
1182 l_adb_total_bal := 0;
1183 l_adb_track_dt := l_adb_start_dt;
1184
1185 WHILE l_adb_track_dt <= g_d_sysdate LOOP
1186 -- Introduced dummy variable l_adb_bal1 to hold the output of the procedure.
1187 -- If l_adb_bal is also passed as OUT variable, then it will be made NULL.
1188 -- This has been done as part of Enh# 2584741, Deposits by shtatiko.
1189 calc_red_balance(p_person_id => p_person_id,
1190 p_bal_start_dt => l_adb_track_dt,
1191 p_bal_end_dt => l_adb_track_dt,
1192 p_bal_type => p_cur_plan.balance_type,
1193 p_open_bal => l_adb_bal,
1194 p_red_bal => l_adb_bal1
1195 );
1196 l_adb_bal := l_adb_bal1;
1197 EXIT WHEN l_adb_bal <= 0;
1198 l_adb_total_bal := l_adb_total_bal + l_adb_bal;
1199 l_adb_track_dt := l_adb_track_dt + 1;
1200 END LOOP;
1201
1202 ELSE --if the person is on an Active Payment Plan..
1203
1204 --Check if offset days is provided, if provided then..
1205 IF p_cur_plan.offset_days IS NOT NULL THEN
1206 --Deduct offset days from the system date and assign the date to the local variable.
1207 l_d_eff_date := g_d_sysdate - p_cur_plan.offset_days;
1208 ELSE
1209 --if offset days is not provided, then assign system date to the local variable.
1210 l_d_eff_date := g_d_sysdate;
1211 END IF;
1212
1213 --If the outstanding balance as on the above calculated start date is less than or equal to 0,
1214 --then log the error message and return from this procedure.
1215 IF NVL(igs_fi_gen_008.get_plan_balance(p_n_std_plan_id,l_d_eff_date),0) <= 0 THEN
1216 fnd_message.set_name('IGS','IGS_FI_NO_CHG_APP_DUE_DT');
1217 fnd_file.put_line(fnd_file.log,fnd_message.get);
1218 RETURN;
1219 END IF;
1220
1221 IF (NVL(p_cur_plan.payment_plan_accrl_type_code,p_cur_plan.accrual_type) = g_v_cutoff_dt) THEN
1222 l_d_start_dt := p_batch_cutoff_dt +1;
1223 ELSIF (NVL(p_cur_plan.payment_plan_accrl_type_code,p_cur_plan.accrual_type) = g_v_due_dt) THEN
1224 l_d_start_dt := p_batch_due_dt+1;
1225 END IF;
1226
1227 l_adb_total_bal := get_pp_cul_dly_bal(p_d_bal_start_dt => l_d_start_dt,
1228 p_n_open_bal => p_payable_bal,
1229 p_n_std_plan_id => p_n_std_plan_id
1230 );
1231
1232 IF p_cur_plan.payment_plan_chg_rate IS NOT NULL THEN
1233 l_n_charge_rate := p_cur_plan.payment_plan_chg_rate;
1234 END IF;
1235 END IF;
1236
1237 l_n_charge_amount := ROUND((l_n_charge_rate * l_adb_total_bal)/100,2);
1238
1239 --invoke the local procedure for creating a charge and generating the log.
1240 log_dtls_create_charge(p_cur_plan => p_cur_plan,
1241 p_n_person_id => p_person_id,
1242 p_n_std_plan_id => p_n_std_plan_id,
1243 p_b_grt_min_bal_amt => TRUE,
1244 p_n_charge_amount => l_n_charge_amount,
1245 p_d_chg_crtn_dt => p_d_chg_crtn_dt,
1246 p_v_cal_type => p_v_fee_cal_type,
1247 p_n_sequence_number => p_n_fee_sequence_number,
1248 p_d_gl_date => p_d_gl_date,
1249 p_d_inst_due_date => NULL,
1250 p_v_test_flag => p_v_test_flag,
1251 p_b_chrg_flag => l_b_chrg_flag
1252 );
1253
1254 END calculate_finance_charge;
1255
1256
1257 PROCEDURE calculate_charge(
1258 p_person_id IN igs_pe_person_v.person_id%TYPE ,
1259 p_batch_cutoff_dt IN DATE,
1260 p_batch_due_dt IN DATE,
1261 p_chg_crtn_dt IN DATE ,
1262 p_test_flag IN VARCHAR2,
1263 p_cur_plan IN cur_plan%ROWTYPE,
1264 p_cal_type IN igs_fi_f_typ_ca_inst_v.fee_cal_type%TYPE,
1265 p_sequence_number IN igs_fi_f_typ_ca_inst_v.fee_ci_sequence_number%TYPE,
1266 p_d_gl_date IN igs_fi_credits_all.gl_date%TYPE,
1267 p_n_std_plan_id IN PLS_INTEGER
1268 ) AS
1269 /***********************************************************************************************
1270
1271 Created By : Sarakshi
1272 Date Created By: 05-Dec-2001
1273 Purpose : To calculate the charge for a particular person
1274
1275 Known limitations,enhancements,remarks:
1276 Change History
1277 Who When What
1278 vvutukur 20-Sep-2003 Enh#3045007.Payment Plans Build. Changes specific to Payment Plans TD.
1279 vvutukur 24-Nov-2002 Enh#2584986.Added p_d_gl_date parameter and passed the same to the call to call_charges_api.
1280 shtatiko 08-Oct-2002 Bug# 2562745, Added call to calculate_balance
1281 vchappid 03-Jun-2002 Bug# 2325427, Customized messages are shown to the user when there is no charge to create
1282 vchappid 19-Apr-2002 Bug#2313147 Billing Cutoff date comparision with the Charge Creation Date is Corrected
1283 SYKRISHN 05-APR-2002 - SFCR018 Build - Planned Credits- 2293676 - to include planned credits also while deriving payable balance.!
1284 ********************************************************************************************** */
1285
1286 l_offset_date DATE;
1287 l_payable_bal igs_fi_balances.standard_balance%TYPE;
1288 l_overdue_bal igs_fi_balances.standard_balance%TYPE;
1289
1290 l_flg_cont BOOLEAN := TRUE;
1291
1292 l_grt_min_bal_amt BOOLEAN := TRUE;
1293
1294 -- Changes due to SFCR018
1295 -- Call the local function to get the value of planned_credits_ind
1296 l_v_pln_cr_ind igs_fi_control_all.planned_credits_ind%TYPE := get_planned_credits_ind;
1297 l_v_pln_cr_message fnd_new_messages.message_name%TYPE := NULL;
1298 l_n_planned_credit igs_fi_credits.amount%TYPE := 0;
1299 --changes due to Enh#2562745.
1300 l_message_name fnd_new_messages.message_name%TYPE;
1301 l_balance_amount igs_fi_balances.standard_balance%TYPE; --This variable ignored in the following call.
1302 l_d_eff_date DATE;
1303 l_d_inst_due_date DATE;
1304
1305 BEGIN
1306 --if the person is NOT on an Active Payment Plan..
1307 IF p_n_std_plan_id IS NULL THEN
1308 --call calculate_balance procedure. This call is added as part of Enh#2562745.
1309 igs_fi_prc_balances.calculate_balance ( p_person_id => p_person_id,
1310 p_balance_type => 'FEE',
1311 p_balance_date => p_batch_cutoff_dt,
1312 p_action => 'ASONBALDATE',
1313 p_balance_rule_id => g_balance_rule_id,
1314 p_balance_amount => l_balance_amount,
1315 p_message_name => l_message_name );
1316 --If any message is returned, log that and error out.
1317 IF l_message_name IS NOT NULL THEN
1318 fnd_file.new_line(fnd_file.log);
1319 fnd_message.set_name('IGS',l_message_name);
1320 fnd_file.put_line(fnd_file.log,fnd_message.get());
1321 RAISE l_validation_exp;
1322 END IF;
1323
1324 --Get the payable balance
1325 l_payable_bal:= det_payable_balance(p_person_id ,p_cur_plan, p_batch_cutoff_dt );
1326
1327 -- Changes due to SFCR018 - To include planned credits also when the indicator is set as 'Y'
1328 IF l_v_pln_cr_ind = 'Y' THEN
1329 --Call the generic function to get the total planned credits for the params passed.
1330 l_n_planned_credit := igs_fi_gen_001.finp_get_total_planned_credits(
1331 p_person_id => p_person_id,
1332 p_start_date => NULL,
1333 p_end_date => p_batch_cutoff_dt,
1334 p_message_name => l_v_pln_cr_message);
1335 IF l_v_pln_cr_message IS NOT NULL THEN
1336 fnd_message.set_name('IGS',l_v_pln_cr_message);
1337 fnd_file.put_line(fnd_file.log,fnd_message.get());
1338 fnd_file.put_line(fnd_file.log,' ');
1339 ELSE
1340 -- When no errors reduce the payable balance with the sum of planned credits.
1341 l_payable_bal := l_payable_bal - NVL(l_n_planned_credit,0);
1342 END IF;
1343 END IF;
1344 -- Changes due to SFCR018 - To include planned credits also when the indicator is set as 'Y'
1345
1346 -- Balance as on the Batch Cutoff Date is less than or equal to Zero
1347 IF l_payable_bal <= 0 THEN
1348 fnd_file.put_line(fnd_file.log,fnd_message.get_string('IGS','IGS_FI_NO_CHG_APP_BT_CUT_OFF'));
1349 RETURN;
1350 END IF;
1351
1352 --Determining the overdue balance as on the offset date
1353 l_offset_date := p_batch_due_dt + NVL(p_cur_plan.offset_days,0);
1354 calc_red_balance(p_person_id => p_person_id,
1355 p_bal_start_dt => p_batch_cutoff_dt + 1,
1356 p_bal_end_dt => l_offset_date,
1357 p_bal_type => p_cur_plan.balance_type,
1358 p_open_bal => l_payable_bal,
1359 p_red_bal => l_overdue_bal
1360 );
1361 -- Overdue Balance as of the offset date is less than or equal to zero
1362 IF l_overdue_bal <= 0 THEN
1363 fnd_file.put_line(fnd_file.log,fnd_message.get_string('IGS','IGS_FI_NO_CHG_APP_DUE_DT'));
1364 RETURN;
1365 END IF;
1366
1367 ELSE --if the person is on an Active Payment Plan..
1368
1369 --Log the message that the Student is on an Active Payment Plan.
1370 fnd_message.set_name('IGS','IGS_FI_PP_PRSN_ON_PP');
1371 fnd_file.put_line(fnd_file.log,fnd_message.get);
1372 fnd_file.new_line(fnd_file.log);
1373
1374 --If the plan type is 'Finance' then..
1375 IF p_cur_plan.plan_type = g_v_finance THEN
1376
1377 --If the payment plan accrual type is Average Daily Balance Cutoff Date..
1378 IF NVL(p_cur_plan.payment_plan_accrl_type_code,p_cur_plan.accrual_type) = g_v_cutoff_dt THEN
1379 --Assign batch cutoff date to the the local variable.
1380 l_d_eff_date := p_batch_cutoff_dt;
1381 --If the payment plan accrual type is Average Daily Balance Cutoff Date..
1382 ELSIF NVL(p_cur_plan.payment_plan_accrl_type_code,p_cur_plan.accrual_type) = g_v_due_dt THEN
1383 --Assign batch due date to the the local variable.
1384 l_d_eff_date := p_batch_due_dt;
1385 END IF;
1386
1387 --Get the Installment Balance passing the local variable l_d_eff_date value to p_d_effective_date parameter.
1388 --and student active plan id.
1389 l_payable_bal := igs_fi_gen_008.get_plan_balance(p_n_act_plan_id => p_n_std_plan_id,
1390 p_d_effective_date => l_d_eff_date
1391 );
1392 IF l_payable_bal <= 0 THEN
1393 --Log different messages in the log file depending on accrual type.
1394 IF NVL(p_cur_plan.payment_plan_accrl_type_code,p_cur_plan.accrual_type) = g_v_cutoff_dt THEN
1395 fnd_file.put_line(fnd_file.log,fnd_message.get_string('IGS','IGS_FI_NO_CHG_APP_BT_CUT_OFF'));
1396 RETURN;
1397 ELSIF NVL(p_cur_plan.payment_plan_accrl_type_code,p_cur_plan.accrual_type) = g_v_due_dt THEN
1398 fnd_file.put_line(fnd_file.log,fnd_message.get_string('IGS','IGS_FI_PP_INST_NO_AMT_DUE'));
1399 RETURN;
1400 END IF;
1401 END IF;
1402
1403 --If the Student is on an Active Payment Plan, assign the Installment Balance calculated as above,
1404 --to overdue balance.
1405 l_overdue_bal := l_payable_bal;
1406 END IF;
1407 END IF;
1408
1409 --Proceed with the processing
1410 IF p_cur_plan.plan_type = g_v_late THEN
1411 --Call the below procedure to calculate late fee charge(s).
1412 calculate_late_fee_charge(p_overdue_bal => l_overdue_bal,
1413 p_cur_plan => p_cur_plan,
1414 p_n_person_id => p_person_id,
1415 p_n_std_plan_id => p_n_std_plan_id,
1416 p_d_chg_crtn_dt => p_chg_crtn_dt,
1417 p_v_fee_cal_type => p_cal_type,
1418 p_n_fee_sequence_number => p_sequence_number,
1419 p_d_gl_date => p_d_gl_date,
1420 p_v_test_flag => p_test_flag
1421 );
1422 ELSIF p_cur_plan.plan_type = g_v_finance THEN
1423 --Call the below procedure to calculate finance charge.
1424 calculate_finance_charge( p_cur_plan => p_cur_plan,
1425 p_payable_bal => l_payable_bal,
1426 p_overdue_bal => l_overdue_bal,
1427 p_batch_cutoff_dt => p_batch_cutoff_dt,
1428 p_batch_due_dt => p_batch_due_dt,
1429 p_person_id => p_person_id,
1430 p_n_std_plan_id => p_n_std_plan_id,
1431 p_d_chg_crtn_dt => p_chg_crtn_dt,
1432 p_v_fee_cal_type => p_cal_type,
1433 p_n_fee_sequence_number => p_sequence_number,
1434 p_d_gl_date => p_d_gl_date,
1435 p_v_test_flag => p_test_flag
1436 );
1437 END IF;
1438
1439 END calculate_charge;
1440
1441
1442 PROCEDURE calc_fin_lt_charge(
1443 errbuf OUT NOCOPY VARCHAR2,
1444 retcode OUT NOCOPY NUMBER,
1445 p_person_id IN igs_pe_person.person_id%TYPE,
1446 p_pers_id_grp_id IN igs_pe_persid_group.group_id%TYPE,
1447 p_plan_name IN igs_fi_fin_lt_plan.plan_name%TYPE ,
1448 p_batch_cutoff_dt IN VARCHAR2,
1449 p_batch_due_dt IN VARCHAR2,
1450 p_fee_period IN VARCHAR2,
1451 p_chg_crtn_dt IN VARCHAR2,
1452 p_test_flag IN VARCHAR2,
1453 p_d_gl_date IN VARCHAR2
1454 ) AS
1455 /***********************************************************************************************
1456
1457 Created By : Sarakshi
1458 Date Created By: 05-Dec-2001
1459 Purpose : The main current program procedure, which validates all parameters and computes
1460 charges one person after another
1461
1462 Known limitations,enhancements,remarks:
1463 Change History
1464 Who When What
1465 sapanigr 13-Feb-2006 Bug 5018036 - Modified cur_fee to reference base table igs_fi_f_typ_ca_inst_all and igs_ca_inst ci.
1466 It was earlier using igs_fi_f_typ_ca_inst_lkp_v.
1467 pmarada 01-sep-04 bug 3687308 - Enforcing user to pass either person id or personid group
1468 pathipat 26-Apr-2004 Bug 3578249 - Modified cursor cur_yes_no - Replaced fnd_lookup_values
1469 with igs_lookup_values, changed lookup_type to YES_NO from SYS_YES_NO
1470 Changed code related to p_test_flag to check for Y and N instead of 1 and 2 respectively.
1471 vvutukur 20-Jan-2004 Bug#3348787.Modified cursor cur_yes_no.
1472 vvutukur 17-Sep-2003 Enh#3045007.Payment Plans Build. Changes specific to Payment Plans TD.
1473 shtatiko 22-APR-2003 Enh# 2831569, Added check for Manage Accounts System Option.
1474 vvutukur 12-Feb-2003 Bug#2731357.As p_chg_crtn_dt parameter is made mandatory,displayed error message
1475 if it is null.
1476 shtatiko 10-JAN-2003 Bug# 2731350, used user defined exception instead of using generic exception
1477 (app_exception.raise_exception)
1478 vvutukur 24-Nov-2002 Enh#2584986.Added p_d_gl_date parameter to calc_fin_lt_charge procedure and
1479 corresponding validations.Modified the calls to procedure calculate_charge to include
1480 p_d_gl_date parameter also.
1481 shtatiko 08-OCT-2002 Bug#2562745, check for Holds Balance Process running is added.
1482 vchappid 19-Apr-2002 Bug#2313147, Date comparisions are done after eliminating time component
1483 ********************************************************************************************** */
1484
1485 --Ref cursor variable.
1486 l_cur_ref cur_ref;
1487
1488 l_v_person_number igs_fi_parties_v.person_number%TYPE := NULL;
1489
1490 CURSOR cur_fee(cp_fee_type igs_fi_f_typ_ca_inst_all.fee_type%TYPE,
1491 cp_fee_cal_type igs_fi_f_typ_ca_inst_all.fee_cal_type%TYPE,
1492 cp_fee_ci_sequence_number igs_fi_f_typ_ca_inst_all.fee_ci_sequence_number%TYPE) IS
1493 SELECT ci.start_dt start_dt,ci.end_dt end_dt
1494 FROM igs_fi_f_typ_ca_inst_all ftci, igs_ca_inst ci
1495 WHERE ftci.fee_type=cp_fee_type
1496 AND ftci.fee_cal_type=cp_fee_cal_type
1497 AND ftci.fee_ci_sequence_number=cp_fee_ci_sequence_number
1498 AND ci.cal_type = ftci.fee_cal_type
1499 AND ci.sequence_number = ftci.fee_ci_sequence_number;
1500 l_cur_fee cur_fee%ROWTYPE;
1501
1502 CURSOR cur_yes_no IS
1503 SELECT meaning
1504 FROM igs_lookup_values
1505 WHERE lookup_type = 'YES_NO'
1506 AND lookup_code = p_test_flag;
1507
1508 l_cur_yes_no cur_yes_no%ROWTYPE;
1509
1510 l_cur_plan cur_plan%ROWTYPE;
1511 l_offset_date DATE;
1512 l_batch_cutoff_dt DATE;
1513 l_batch_due_dt DATE;
1514 l_chg_crtn_dt DATE;
1515 l_cal_type igs_fi_f_typ_ca_inst_lkp_v.fee_cal_type%TYPE;
1516 l_sequence_number igs_fi_f_typ_ca_inst_lkp_v.fee_ci_sequence_number%TYPE;
1517 l_record_count NUMBER :=0;
1518 --Added the following as part of Enh# 2562745.
1519 l_conv_process_run_ind igs_fi_control.conv_process_run_ind%TYPE;
1520 l_message_name fnd_new_messages.message_name%TYPE;
1521
1522 l_v_closing_status gl_period_statuses.closing_status%TYPE;
1523 l_d_gl_date igs_fi_credits_all.gl_date%TYPE;
1524 l_v_message_name fnd_new_messages.message_name%TYPE;
1525 l_v_manage_accounts igs_fi_control.manage_accounts%TYPE;
1526
1527 l_n_person_id igs_fi_parties_v.person_id%TYPE;
1528 l_v_sql VARCHAR2(32767);
1529 l_v_status VARCHAR2(1);
1530 l_n_act_plan_id igs_fi_pp_std_attrs.student_plan_id%TYPE;
1531 l_v_act_plan_name igs_fi_pp_std_attrs.payment_plan_name%TYPE;
1532
1533 BEGIN
1534
1535 --Create a Savepoint for Rollback.
1536 SAVEPOINT s_calc_fin_lt_charge;
1537
1538 IGS_GE_GEN_003.set_org_id(NULL) ; -- sets the orgid
1539 retcode := 0 ; -- initialises the out NOCOPY parameter to 0
1540
1541 -- Check the value of Manage Accounts System Option value.
1542 -- If its NULL or OTHER then this process should error out by logging message.
1543 igs_fi_com_rec_interface.chk_manage_account ( p_v_manage_acc => l_v_manage_accounts,
1544 p_v_message_name => l_v_message_name );
1545 IF l_v_manage_accounts IS NULL OR l_v_manage_accounts = 'OTHER' THEN
1546 fnd_message.set_name ( 'IGS', l_v_message_name );
1547 fnd_file.put_line ( fnd_file.LOG, fnd_message.get );
1548 RAISE l_validation_exp;
1549 END IF;
1550
1551 --Check whether Holds Balance Conversion Process is running or not. If yes, Error out.
1552
1553 igs_fi_gen_007.finp_get_conv_prc_run_ind ( p_n_conv_process_run_ind => l_conv_process_run_ind,
1554 p_v_message_name => l_message_name );
1555 IF ((l_conv_process_run_ind IS NOT NULL) AND (l_conv_process_run_ind = 1)) THEN
1556 fnd_file.new_line(fnd_file.log);
1557 fnd_message.set_name('IGS','IGS_FI_REASS_BAL_PRC_RUN');
1558 fnd_file.put_line(fnd_file.log,fnd_message.get());
1559 RAISE l_validation_exp;
1560 ELSIF ((l_message_name IS NOT NULL) AND (l_conv_process_run_ind IS NULL)) THEN
1561 fnd_file.new_line(fnd_file.log);
1562 fnd_message.set_name('IGS',l_message_name);
1563 fnd_file.put_line(fnd_file.log,fnd_message.get());
1564 RAISE l_validation_exp;
1565 END IF;
1566
1567 --Logging the parameters
1568 IF p_person_id IS NOT NULL THEN
1569 l_v_person_number := igs_fi_gen_008.get_party_number(p_n_party_id => p_person_id);
1570 END IF;
1571
1572 OPEN cur_yes_no ;
1573 FETCH cur_yes_no INTO l_cur_yes_no;
1574 CLOSE cur_yes_no;
1575
1576 --Getting the plan information in the record l_cur_plan
1577 OPEN cur_plan(p_plan_name);
1578 FETCH cur_plan INTO l_cur_plan;
1579 CLOSE cur_plan;
1580
1581 --Getting cal type and sequence number
1582 l_cal_type :=RTRIM(SUBSTR(p_fee_period ,1,10));
1583 l_sequence_number := TO_NUMBER(RTRIM(SUBSTR(p_fee_period,12)));
1584
1585 --Converting the date parameter
1586 l_batch_cutoff_dt := igs_ge_date.igsdate(p_batch_cutoff_dt);
1587 l_batch_due_dt := igs_ge_date.igsdate(p_batch_due_dt);
1588
1589 --Conversion of p_d_gl_date from VARCHAR2 data type to DATE data type.
1590 l_d_gl_date := igs_ge_date.igsdate(p_d_gl_date);
1591
1592 --Conversion of p_chg_crtn_dt from VARCHAR2 datatype to DATE data type.
1593 l_chg_crtn_dt := igs_ge_date.igsdate(p_chg_crtn_dt);
1594
1595 OPEN cur_fee(l_cur_plan.fee_type,l_cal_type,l_sequence_number);
1596 FETCH cur_fee INTO l_cur_fee;
1597 CLOSE cur_fee;
1598
1599 fnd_message.set_name('IGS','IGS_FI_ANC_LOG_PARM');
1600 fnd_file.put_line(fnd_file.log,fnd_message.get||':');
1601 fnd_file.new_line(fnd_file.log);
1602
1603 log_messages(lookup_desc('IGS_FI_LOCKBOX','PARTY'),l_v_person_number);
1604 log_messages(lookup_desc('IGS_FI_LOCKBOX','PERSON_GROUP'),TO_CHAR(p_pers_id_grp_id));
1605 log_messages(lookup_desc('IGS_FI_LOCKBOX','PLAN_NAME'),p_plan_name);
1606 log_messages(lookup_desc('IGS_FI_LOCKBOX','BATCH_CUTOFF_DT'),TO_CHAR(l_batch_cutoff_dt,'DD-MM-YYYY'));
1607 log_messages(lookup_desc('IGS_FI_LOCKBOX','BATCH_DUE_DT'),TO_CHAR(l_batch_due_dt,'DD-MM-YYYY'));
1608 log_messages(lookup_desc('IGS_FI_LOCKBOX','FEE_PERIOD'),
1609 TO_CHAR(l_cur_fee.start_dt,'DD-MM-YYYY')||' '||TO_CHAR(l_cur_fee.end_dt,'DD-MM-YYYY'));
1610 log_messages(lookup_desc('IGS_FI_LOCKBOX','CHG_CREATION_DT'),TO_CHAR(l_chg_crtn_dt,'DD-MM-YYYY'));
1611 log_messages(lookup_desc('IGS_FI_LOCKBOX','TEST_MODE'),l_cur_yes_no.meaning);
1612 log_messages(lookup_desc('IGS_FI_LOCKBOX','GL_DATE'),l_d_gl_date);
1613
1614 fnd_file.put_line(fnd_file.log,g_v_hor_line);
1615
1616 -- In following validations removed call to generic, app_exception.raise_exception
1617 -- and added user defined exception, l_validation_exp. As we are not putting message onto
1618 -- stack, used fnd_file.put_line to log messages. This has been done by shtatiko as part
1619 -- of Bug# 2731350
1620 --Validating if all the mandatory parameter are passed
1621 IF ((p_plan_name IS NULL) OR (p_batch_cutoff_dt IS NULL) OR (p_batch_due_dt IS NULL)
1622 OR (p_fee_period IS NULL) OR (p_test_flag IS NULL) OR (p_chg_crtn_dt IS NULL) ) THEN
1623 fnd_message.set_name('IGS','IGS_FI_PARAMETER_NULL');
1624 fnd_file.put_line ( fnd_file.log, fnd_message.get );
1625 RAISE l_validation_exp;
1626 END IF;
1627
1628
1629 --Validating person Id and person id group cannot be present at a same time
1630 IF p_person_id IS NOT NULL AND p_pers_id_grp_id IS NOT NULL THEN
1631 fnd_message.set_name('IGS','IGS_FI_PRS_OR_PRSIDGRP');
1632 fnd_file.put_line ( fnd_file.log, fnd_message.get );
1633 RAISE l_validation_exp;
1634 END IF;
1635
1636 -- Either person Id or person id group should be passed as a parameter
1637 IF p_person_id IS NULL AND p_pers_id_grp_id IS NULL THEN
1638 fnd_message.set_name('IGS','IGS_FI_PRS_PRSIDGRP_NULL');
1639 fnd_file.put_line ( fnd_file.log, fnd_message.get );
1640 RAISE l_validation_exp;
1641 END IF;
1642
1643 --Validating person id group
1644 IF p_pers_id_grp_id IS NOT NULL THEN
1645 IF NOT validate_persid_grp(p_pers_id_grp_id) THEN
1646 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
1647 fnd_file.put_line ( fnd_file.log, fnd_message.get );
1648 RAISE l_validation_exp;
1649 END IF;
1650 END IF;
1651
1652 --Validating person id
1653 IF p_person_id IS NOT NULL THEN
1654 IF igs_fi_gen_007.validate_person(p_person_id)= 'N' THEN
1655 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
1656 fnd_file.put_line ( fnd_file.log, fnd_message.get );
1657 RAISE l_validation_exp;
1658 END IF;
1659 END IF;
1660
1661 --Validating Plan Name
1662 IF NOT validate_plan_name(p_plan_name) THEN
1663 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
1664 fnd_file.put_line ( fnd_file.log, fnd_message.get );
1665 RAISE l_validation_exp;
1666 END IF;
1667
1668 --Validating cut off date with due date
1669 IF l_batch_cutoff_dt >= l_batch_due_dt THEN
1670 fnd_message.set_name('IGS','IGS_FI_CTDT_DUEDT');
1671 fnd_file.put_line ( fnd_file.log, fnd_message.get );
1672 RAISE l_validation_exp;
1673 END IF;
1674
1675 --Validating charge creation date with cut off date
1676 IF l_batch_cutoff_dt >= l_chg_crtn_dt THEN
1677 fnd_message.set_name('IGS','IGS_FI_CTDT_CHG_DT');
1678 fnd_file.put_line ( fnd_file.log, fnd_message.get );
1679 RAISE l_validation_exp;
1680 END IF;
1681
1682
1683 --Validating FTCI
1684 IF NOT validate_ftci(l_cur_plan,l_cal_type,l_sequence_number) THEN
1685 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
1686 fnd_file.put_line ( fnd_file.log, fnd_message.get );
1687 RAISE l_validation_exp;
1688 END IF;
1689
1690 --GL date parameter is mandatory, hence if passed as null, error out NOCOPY of the concurrent job.
1691 IF p_d_gl_date IS NULL THEN
1692 fnd_message.set_name('IGS','IGS_GE_INSUFFICIENT_PARAMETER');
1693 fnd_file.put_line(fnd_file.log, fnd_message.get );
1694 RAISE l_validation_exp;
1695 END IF;
1696
1697 --Validate the GL Date.
1698 igs_fi_gen_gl.get_period_status_for_date(p_d_date => l_d_gl_date,
1699 p_v_closing_status => l_v_closing_status,
1700 p_v_message_name => l_v_message_name
1701 );
1702 IF l_v_message_name IS NOT NULL THEN
1703 fnd_message.set_name('IGS',l_v_message_name);
1704 fnd_file.put_line(fnd_file.log,fnd_message.get);
1705 RAISE l_validation_exp;
1706 END IF;
1707
1708 --Error out NOCOPY the concurrent process if the GL Date is not a valid one.
1709 IF l_v_closing_status IN ('C','N','W') THEN
1710 fnd_message.set_name('IGS','IGS_FI_INVALID_GL_DATE');
1711 fnd_message.set_token('GL_DATE',l_d_gl_date);
1712 fnd_file.put_line(fnd_file.log,fnd_message.get);
1713 RAISE l_validation_exp;
1714 END IF;
1715
1716 --Logging the plan information
1717 log_plan_info(l_cur_plan,l_batch_cutoff_dt,l_batch_due_dt,l_cal_type,l_sequence_number,l_batch_due_dt);
1718
1719 --Validating offset date >= sysdate
1720 l_offset_date := l_batch_due_dt + NVL(l_cur_plan.offset_days,0);
1721 IF l_offset_date >= g_d_sysdate THEN
1722 fnd_file.put_line(fnd_file.log,fnd_message.get_string('IGS','IGS_FI_DUDT_OFFST'));
1723 ELSE
1724 --Find the latest balance rule id for the balance type FEE.Return values last_conversion_date and
1725 --version number are ignored by passing null.
1726
1727 --Added this call to finp_get_balance_rule as part of Bug#2562745. g_balance_rule_id is used
1728 --in calculate_charge. This is done here because this should be called only once irrespopective of
1729 --number of persons.
1730
1731 igs_fi_gen_007.finp_get_balance_rule ( p_v_balance_type => 'FEE',
1732 p_v_action => 'MAX',
1733 p_n_balance_rule_id => g_balance_rule_id,
1734 p_d_last_conversion_date => g_last_conversion_date,
1735 p_n_version_number => g_version_number );
1736
1737 --Proceed with the processing
1738 --when person id is provided
1739 IF p_person_id IS NOT NULL THEN
1740 log_person(p_person_id);
1741 l_record_count := l_record_count + 1;
1742
1743 --Get the Student's Active Payment Plan Details.
1744 igs_fi_gen_008.get_plan_details(p_n_person_id => p_person_id,
1745 p_n_act_plan_id => l_n_act_plan_id,
1746 p_v_act_plan_name => l_v_act_plan_name
1747 );
1748
1749 --Call the local procedure which does the processing for creation of Finanace/Late Charge.
1750 calculate_charge( p_person_id => p_person_id,
1751 p_batch_cutoff_dt => l_batch_cutoff_dt,
1752 p_batch_due_dt => l_batch_due_dt,
1753 p_chg_crtn_dt => l_chg_crtn_dt,
1754 p_test_flag => p_test_flag,
1755 p_cur_plan => l_cur_plan,
1756 p_cal_type => l_cal_type,
1757 p_sequence_number => l_sequence_number,
1758 p_d_gl_date => l_d_gl_date,
1759 p_n_std_plan_id => l_n_act_plan_id
1760 );
1761
1762 --when person group id is provided
1763 ELSIF p_pers_id_grp_id IS NOT NULL THEN
1764 --For the Person Group passed as input to the process, identify all the Persons that are members of this group
1765 --using generic function.
1766 l_v_sql := igs_pe_dynamic_persid_group.igs_get_dynamic_sql(p_groupid => p_pers_id_grp_id,
1767 p_status => l_v_status
1768 );
1769 --If the sql returned is invalid.. then,
1770 IF l_v_status <> 'S' THEN
1771 --Log the error message and stop processing.
1772 fnd_message.set_name('IGF','IGF_AP_INVALID_QUERY');
1773 fnd_file.put_line(fnd_file.log,fnd_message.get);
1774 fnd_file.put_line(fnd_file.log,l_v_sql);
1775 retcode := 2;
1776 RETURN;
1777 END IF;
1778
1779 --Execute the sql statement using ref cursor.
1780 OPEN l_cur_ref FOR l_v_sql;
1781 LOOP
1782 --Capture the person id into a local variable l_n_person_id.
1783 FETCH l_cur_ref INTO l_n_person_id;
1784 EXIT WHEN l_cur_ref%NOTFOUND;
1785 log_person(l_n_person_id);
1786 l_record_count := l_record_count + 1;
1787
1788 l_n_act_plan_id := NULL;
1789 l_v_act_plan_name := NULL;
1790
1791 --Get the Student's Active Payment Plan Details.
1792 igs_fi_gen_008.get_plan_details(p_n_person_id => l_n_person_id,
1793 p_n_act_plan_id => l_n_act_plan_id,
1794 p_v_act_plan_name => l_v_act_plan_name
1795 );
1796
1797 --Call the local procedure which does the processing for creation of Finanace/Late Charge.
1798 calculate_charge( p_person_id => l_n_person_id,
1799 p_batch_cutoff_dt => l_batch_cutoff_dt,
1800 p_batch_due_dt => l_batch_due_dt,
1801 p_chg_crtn_dt => l_chg_crtn_dt,
1802 p_test_flag => p_test_flag,
1803 p_cur_plan => l_cur_plan,
1804 p_cal_type => l_cal_type,
1805 p_sequence_number => l_sequence_number,
1806 p_d_gl_date => l_d_gl_date,
1807 p_n_std_plan_id => l_n_act_plan_id
1808 );
1809 END LOOP;
1810 CLOSE l_cur_ref;
1811 END IF; -- End if for personid or personid group not null
1812 END IF;
1813
1814 -- Rollback to the savepoint created if process is in test run mode.
1815 IF ( p_test_flag = 'Y' ) THEN -- i.e., If its in TEST mode.
1816 ROLLBACK TO s_calc_fin_lt_charge;
1817 END IF;
1818
1819 fnd_file.put_line(fnd_file.log,g_v_hor_line);
1820 fnd_file.put_line(fnd_file.log,fnd_message.get_string('IGS','IGS_GE_TOTAL_REC_PROCESSED')||TO_CHAR(l_record_count));
1821 fnd_file.put_line(fnd_file.log,g_v_hor_line);
1822 fnd_file.new_line(fnd_file.log);
1823
1824
1825 EXCEPTION
1826 WHEN l_validation_exp THEN
1827 retcode := 2;
1828 WHEN OTHERS THEN
1829 retcode := 2;
1830 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION') || ' : ' || SQLERRM;
1831 igs_ge_msg_stack.conc_exception_hndl;
1832 END calc_fin_lt_charge;
1833
1834 END igs_fi_prc_fin_lt_chg;
[Home] [Help]