1 PACKAGE BODY igs_fi_prc_holds AS
2 /* $Header: IGSFI67B.pls 120.6 2006/05/15 23:01:32 sapanigr ship $ */
3
4 /***************************************************************
5 Created By : bayadav
6 Date Created By : 29-Nov-2001
7 Purpose : Having procedures related to batch application of holds on person/person group/all people
8 AND release of holds on person/person group/all people
9 Known Limitations,Enhancements or Remarks:
10 Change History :
11 Who When What
12 sapanigr 03-May-2006 Enh#3924836 Precision Issue. Modified validate_holds.
13 sapanigr 10-Oct-2005 Bug 3049184 - Modified proc finp_release_holds_main and function validate_holds to change log file
14 for Process Release of Finance Holds
15 svuppala 17-AUG-2005 Bug 4557933 - Unable to remove holds that were put on with concurrent manager
16 Validate_holds : Passing values to AUTHORISING_PERSON_ID and AUTH_RESP_ID from SWS Holds API
17 while calling insert_row of IGS_PE_PERS_ENCUMB_PKG
18 pmarada 26-jul-2004 Bug 3792800, Added code to bypass the holds apply validation in finp_apply_holds procedure
19 pathipat 12-Aug-2003 Enh 3076768 - Automatic Release of Holds
20 Added procedure finp_auto_release_holds(), modified validate_holds() and all its call-outs
21 Modified validate_param - removed calls to igs_pe_gen_001.get_hold_auth.
22 pathipat 23-Jun-2003 Bug: 3018104 - Impact of changes in person id group views
23 Replaced all occurrences of igs_pe_persid_group_v and igs_pe_prsid_grp_mem_v
24 with igs_pe_persid_group and igs_pe_prsid_grp_mem respectively
25 pathipat 05-May-2003 Enh 2831569 - Commercial Receivables Build
26 Modified finp_apply_holds() and finp_release_holds_main() - Added check for manage_accounts
27 vvutukur 05-Mar-2003 Bug#2824994.Modified procedure finp_apply_holds,function validate_holds(used in releasing holds),holds_balance.
28 pathipat 25-Feb-2003 Enh:2747341 - Additional Security for Holds build
29 Modifications according to FI206_TD_SWS_Additional_Security_for_Holds_s1a.doc
30 Modified cursor c_person - selected from igs_pe_person_base_v instead of igs_pe_person
31 Changed declaration of local variable person_name appropriately.
32 ssawhney 17-feb-2003 Bug : 2758856 : Added the parameter x_external_reference in the call to IGS_PE_PERS_ENCUMB_PKG.UPDATE_ROW
33
34 SYKRISHn 03-JAN-2002 Bug 2684895 --Procedure finp_apply_holds and finp_release_holds_main
35 Logging Person Group Cd instead of person group id.
36 SYKRISHN 31DEC2002 Bug 2676524 - Procedure finp_apply_holds
37 Derived the person number for the parameter p_auth_person_id
38 to display in the concurrent log file instead of the P_auth_person_id which
39 was logged earlier.
40 smadathi 20-dec-2002 Enh. Bug 2566615. Removed the references of obsoleted table IGS_FI_HOLD_PLN_LNS and
41 incorporated changes suggested as per FICR102 TD. Removed lookup_desc function
42 agairola 03-Dec-2002 Bug No: 2584741 As part of the Deposits Build, modified the cursor c_credit_amount to exclude
43 credits of Credit Class Enrolment Deposit and Other Deposit
44 pathipat 04-OCT-2002 Enh Bug:2562745 -- Reassess Balances build
45 1. Added check in finp_apply_holds() and in finp_release_holds_main() to check if
46 holds conversion process is running before continuing with further processing
47 2. Added check in validate_param() to check if active balance rule has been defined for the
48 balance type of HOLDS.
49 3. Also added in the same function, check if the process start date is not later than the
50 last_conversion_date of the balance rule when the hold plan name is at 'Account' level.
51 4. In validate_holds(), removed insertion of balance_amount into the igs_fi_person_holds
52 table as the column is being obsoleted. Added cursor c_bal_amount to obtain holds balance
53 from igs_fi_balances (in place of balance amount from igs_fi_person_holds)
54 5. Added parameter balance_rule_id in calls to check_exclusion_rules()
55 6. Removed igs_ge_date.igsdate(p_process_start_date) and replaced with just
56 p_process_Start_date. similarly for process_end_date also.
57 pkpatel 30-SEP-2002 Bug No: 2600842
58 Added the parameter auth_resp_id in the call to the procedures of TBH igs_pe_pers_encumb_pkg
59 vchappid 07-Jun-2002 Bug 2392486#, Calculation of the holds balance incase the Holds plan is at subaccount
60 is corrected , Holds Balance should be added only when the balance record is found in
61 the balances table
62 SYkrishn 30/APR/2002 in function validate_param
63 Changes in curor c_fee_type to compare ci ststu with system fee structure ststua
64 Bug 2348883
65 SYkrishn 03-APR-2002 Changes according to Build 2293676 - Planned Credits Functionality introduced.
66 vvutukur 28-02-2002 Modified the cursor c_person by selecting from igs_pe_person
67 instead of igs_fi_parties_v.for bug:2238362(reverting back the earlier fix).
68 vvutukur 27-02-2002 Modified cursor c_person by selecting from igs_fi_parties_v
69 instead of igs_pe_person for bug:2238362.
70 ***************************************************************/
71
72 --Skip exception used to skip a record FROM the cursor based on the condition
73 skip EXCEPTION;
74
75 --cursor to SELECT person information for the passed person id in order to display in the log
76 CURSOR c_person(l_person_id igs_pe_person.person_id%TYPE) IS
77 SELECT person_number,
78 full_name
79 FROM igs_pe_person_base_v
80 WHERE person_id = l_person_id;
81
82 --cursor variable
83 l_person_rec c_person%ROWTYPE;
84
85 -- package variables - OUT NOCOPY variables from finp_get_balance_rule()
86 l_balance_rule_id igs_fi_balance_rules.balance_rule_id%TYPE;
87
88 -- Flag to indicate that a hold for a person has been skipped due to
89 -- some validation failure to continue with the next hold
90 -- g_b_hold_skipped = TRUE even if one hold for a person is skipped
91 g_b_hold_skipped BOOLEAN := FALSE;
92
93 --added following 6 global variables as part of bug#2824994.
94 g_v_person_number CONSTANT igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_HOLDS','PERSON_NUMBER');
95 g_v_person_name CONSTANT igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_HOLDS','PERSON_NAME');
96 g_v_hold_plan CONSTANT igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_HOLDS','HOLD_PLAN');
97 g_n_holds_bal CONSTANT igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_HOLDS','HOLDS_BALANCE');
98 g_n_overdue_bal CONSTANT igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_HOLDS','HOLDS_OVERDUE_BALANCE');
99 g_v_hold_type CONSTANT igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_HOLDS','HOLD_TYPE');
100 g_n_person_id hz_parties.party_id%TYPE;
101 g_n_resp_id fnd_responsibility.responsibility_id%TYPE;
102
103 -- Changes due to SFCR018
104 ---Local package function to get the value of planned credits indicator
105
106 FUNCTION get_planned_credits_ind
107 RETURN VARCHAR2
108 IS
109 /***************************************************************
110 Created By : SYkrishn
111 Date Created By : APR/03/2002
112 Purpose : Gets the value of planned credits indicator from IGS_FI_CONTROL_ALL.
113 Known Limitations,Enhancements or Remarks:
114 Change History :
115 Who When What
116
117 ***************************************************************/
118
119 l_v_planned_credits_ind igs_fi_control_all.planned_credits_ind%TYPE := NULL;
120 l_v_pln_cr_message fnd_new_messages.message_name%TYPE := NULL;
121
122 BEGIN
123 --Call the genric function to get the value
124 l_v_planned_credits_ind := igs_fi_gen_001.finp_get_planned_credits_ind(l_v_pln_cr_message);
125
126 IF l_v_pln_cr_message IS NOT NULL THEN
127 --Log error message and raise exception
128 fnd_message.set_name('IGS',l_v_pln_cr_message);
129 fnd_file.put_line(fnd_file.log,fnd_message.get());
130 fnd_file.put_line(fnd_file.log,' ');
131 app_exception.raise_exception;
132 END IF;
133
134 RETURN l_v_planned_credits_ind;
135
136 END get_planned_credits_ind;
137 -- Changes due to SFCR018
138
139 --FUNCTION to calculate holds balance amount
140
141 FUNCTION holds_balance( p_person_id IN igs_pe_person_v.person_id%TYPE ,
142 p_person_number IN igs_pe_person_v.person_number%TYPE ,
143 p_hold_plan_name IN igs_fi_hold_plan.hold_plan_name%Type,
144 P_fee_cal_type IN igs_fi_inv_int.fee_cal_type%TYPE,
145 P_fee_ci_sequence_number IN igs_fi_inv_int.fee_ci_sequence_number%TYPE,
146 p_process_end_date IN igs_fi_person_holds.process_end_dT%TYPE ,
147 p_process_start_date IN igs_fi_person_holds.process_start_dT%TYPE ,
148 P_test_run IN VARCHAR2 ,
149 P_hold_type OUT NOCOPY igs_fi_hold_plan.hold_type%Type,
150 P_hold_plan_level OUT NOCOPY igs_fi_hold_plan.hold_plan_level%TYPE,
151 P_holds_charges OUT NOCOPY igs_fi_inv_int.invoice_amount%TYPE,
152 P_holds_final_balance OUT NOCOPY igs_fi_credits.amount%TYPE,
153 p_offset_days OUT NOCOPY igs_fi_hold_plan.offset_days%TYPE,
154 p_n_student_plan_id OUT NOCOPY igs_fi_pp_std_attrs.student_plan_id%TYPE,
155 p_d_last_inst_due_date OUT NOCOPY igs_fi_pp_instlmnts.due_date%TYPE)
156 RETURN BOOLEAN
157 IS
158 /***************************************************************
159 Created By : bayadav
160 Date Created By : 29-Nov-2001
161 Purpose : to calculate holds balance amount
162 Known Limitations,Enhancements or Remarks:
163 Change History :
164 Who When What
165 shtatiko 17-OCT-2003 Bug# 3192641, Added code to consider charges which are waived when calculating holds_balance.
166 smadathi 28-Aug-2003 Enh Bug 3045007. Added new parameters p_n_student_plan_id, p_d_last_inst_due_date. Modified p_offset_days
167 to OUT. Modified the cursor c_hold_plan.
168 vvutukur 25-Mar-2003 Bug#2824994.Person Number,Person Name also logged if holds are would not be applied for a person.
169 smadathi 20-dec-2002 Enh. Bug 2566615. Removed the references of obsoleted table IGS_FI_HOLD_PLN_LNS and
170 incorporated changes suggested as per FICR102 TD
171 agairola 03-Dec-2002 Bug No: 2584741 As part of the Deposits Build, modified the cursor c_credit_amount to exclude
172 credits of Credit Class Enrolment Deposit and Other Deposit
173 sarakshi 23-sep-2002 Enh#2564643,removed teh reference of subaccount from this function as mentioned in the TD
174 vchappid 07-Jun-2002 Bug 2392486#, Calculation of the holds balance incase the Holds plan is at subaccount
175 is corrected , Balance should be added only when the balance record is found in the balances table
176 SYkrishn 07-APR-2002 Introduced planned credits functionality
177 as per SFCR018 DLD- 2293676 - For sub account level hold plans if the
178 include planned credits is setup then planned credits are also considered along with actual
179 credits to derive the holds balance overdue.
180 ***************************************************************/
181 --to SELECT hold_plan_level related information
182 CURSOR c_hold_plan IS
183 SELECT hold_plan_level,
184 hold_type,
185 threshold_amount,
186 threshold_percent,
187 fee_type,
188 offset_days ,
189 payment_plan_threshold_amt ,
190 payment_plan_threshold_pcent
191 FROM igs_fi_hold_plan
192 WHERE hold_plan_name = p_hold_plan_name;
193
194 --to SELECT holds_balance for the passed party_id AND date range
195 CURSOR c_holds_balance IS
196 SELECT holds_balance
197 FROM igs_fi_balances
198 WHERE party_id = p_person_id
199 AND holds_balance IS NOT NULL
200 AND TRUNC(balance_date) <= TRUNC(p_process_start_date)
201 ORDER BY balance_date desc;
202
203 --to SELECT credit_amount for the passed date range
204 CURSOR c_credit_amnt(l_payment_due_date DATE) IS
205 SELECT crd.credit_id,
206 crd.amount,
207 crd.effective_date
208 FROM igs_fi_credits crd,
209 igs_fi_cr_types crt
210 WHERE TRUNC(crd.effective_date) between (TRUNC(p_process_start_date) + 1) AND TRUNC(l_payment_due_date)
211 AND crd.status = 'CLEARED'
212 AND crd.party_id = p_person_id
213 AND crd.credit_type_id = crt.credit_type_id
214 AND crt.credit_class NOT IN ('ENRDEPOSIT','OTHDEPOSIT')
215 ORDER BY crd.credit_id ;
216
217
218 --to SELECT invoice amount or invoice amount due based on the l_SELECT_facto value passed
219 CURSOR c_invoice_amnt(l_fee_type igs_fi_fee_type_all.fee_type%TYPE ) IS
220 SELECT invoice_id,
221 invoice_amount amount,
222 invoice_amount_due ,
223 invoice_creation_date
224 FROM igs_fi_inv_int inv
225 WHERE fee_type = l_fee_type
226 AND fee_cal_type = p_fee_cal_type
227 AND fee_ci_sequence_number = p_fee_ci_sequence_number
228 AND TRUNC(invoice_creation_date) <= TRUNC(p_process_start_date)
229 AND person_id = p_person_id
230 AND NOT EXISTS (SELECT 'X'
231 FROM igs_fi_inv_wav_det
232 WHERE invoice_id = inv.invoice_id
233 AND balance_type = 'HOLDS'
234 AND (
235 (
236 TRUNC(end_dt) IS NOT NULL AND
237 TRUNC(p_process_start_date) BETWEEN TRUNC(start_dt) AND TRUNC(end_dt)
238 )
239 OR
240 (TRUNC(p_process_start_date) >= TRUNC(start_dt) AND TRUNC(end_dt) is null)
241 )
242 );
243
244 CURSOR c_igs_fi_pp_instlmnts ( cp_n_student_plan_id igs_fi_pp_std_attrs.student_plan_id%TYPE,
245 cp_d_d_pay_det_date DATE
246 ) IS
247 SELECT SUM(installment_amt) installment_amt,
248 SUM(due_amt) due_amt,
249 MAX(due_date) due_date
250 FROM igs_fi_pp_instlmnts
251 WHERE student_plan_id = cp_n_student_plan_id
252 AND due_date <= cp_d_d_pay_det_date;
253
254 rec_c_igs_fi_pp_instlmnts c_igs_fi_pp_instlmnts%ROWTYPE;
255
256 -- Cursor to calculate the total charge amount waived when a plan level is "Balance"
257 -- Added as part of Bug# 3192641
258 CURSOR c_waive_amount( cp_n_person_id NUMBER, cp_d_process_start_date DATE) IS
259 SELECT NVL(SUM(chg.invoice_amount), 0)
260 FROM igs_fi_inv_int_all chg,
261 igs_fi_inv_wav_det wav
262 WHERE person_id = cp_n_person_id
263 AND chg.invoice_id = wav.invoice_id
264 AND wav.balance_type = 'HOLDS'
265 AND cp_d_process_start_date BETWEEN TRUNC(start_dt) AND NVL(TRUNC(end_dt), cp_d_process_start_date);
266 l_n_waive_amount NUMBER;
267
268 --declaration of loacl variables
269 l_hold_plan_rec c_hold_plan%ROWTYPE ;
270 l_holds_balance_rec c_holds_balance%ROWTYPE;
271 l_credit_amnt_rec c_credit_amnt%ROWTYPE;
272 l_invoice_amnt_rec c_invoice_amnt%ROWTYPE;
273 l_message_name fnd_new_messages.message_name%TYPE := NULL;
274 l_holds_balance igs_fi_balances.holds_balance%TYPE :=0;
275 l_payment_due_date igs_fi_person_holds.process_start_dT%TYPE;
276 l_credit_subac igs_fi_credits.amount%TYPE :=0;
277 l_tot_credits igs_fi_credits.amount%TYPE :=0;
278 l_charges igs_fi_inv_int.invoice_amount%TYPE :=0;
279 l_final_balance igs_fi_credits.amount%TYPE :=0;
280 l_ratio igs_fi_hold_plan.threshold_percent%TYPE;
281
282 -- Changes due to SFCR018
283 -- Call the local function to get the value of planned_credits_ind
284 l_v_pln_cr_ind igs_fi_control_all.planned_credits_ind%TYPE := get_planned_credits_ind;
285 l_v_pln_cr_message fnd_new_messages.message_name%TYPE :=NULL;
286 l_n_planned_credit igs_fi_credits.amount%TYPE :=0;
287 -- Changes due to SFCR018
288
289 l_n_threshold_amount igs_fi_hold_plan.threshold_amount%TYPE ;
290 l_n_threshold_percent igs_fi_hold_plan.threshold_percent%TYPE ;
291 l_n_act_plan_id igs_fi_pp_std_attrs.student_plan_id%TYPE ;
292 l_v_act_plan_name igs_fi_pp_templates.payment_plan_name%TYPE;
293 l_d_pay_det_date DATE;
294
295 BEGIN
296
297 --To fetch the header details associated with the hold plan name
298
299 OPEN c_hold_plan ;
300 FETCH c_hold_plan INTO l_hold_plan_rec;
301 --Calculate the payment due date
302 IF l_hold_plan_rec.offset_days IS NOT NULL THEN
303 l_payment_due_date := p_process_end_date + NVL(l_hold_plan_rec.offset_days,0);
304 ELSE
305 l_payment_due_date := p_process_end_date ;
306 END IF;
307
308 p_hold_type := l_hold_plan_rec.hold_type;
309 p_hold_plan_level := l_hold_plan_rec.hold_plan_level;
310 p_offset_days := l_hold_plan_rec.offset_days;
311 l_n_threshold_amount := l_hold_plan_rec.threshold_amount;
312 l_n_threshold_percent := l_hold_plan_rec.threshold_percent;
313
314 --Validate the fetched Hold type for closed indicator
315
316 --1
317 l_message_name := NULL;
318 IF NOT igs_en_val_etde.enrp_val_et_closed(l_hold_plan_rec.hold_type,l_message_name)
319 AND (l_message_name = 'IGS_EN_ENCUMB_TYPE_CLOSED') THEN
320 fnd_message.set_name('IGS','IGS_EN_ENCUMB_TYPE_CLOSED');
321 fnd_file.put_line(fnd_file.log,fnd_message.get());
322 fnd_file.put_line(fnd_file.log,' ');
323 CLOSE c_hold_plan;
324 RETURN FALSE;
325 END IF;
326 --1
327 --To find the level at which the hold plan is defined
328 --2
329 IF l_hold_plan_rec.hold_plan_level = 'S' THEN
330
331 -- check if the Person is on an Active Payment Plan.
332 -- if the the Person is on an Active Payment Plan , hold application will be evaluated
333 -- based on the payment plan installments.
334 -- Invoke the Generic procedure
335 igs_fi_gen_008.get_plan_details (p_n_person_id => p_person_id,
336 p_n_act_plan_id => l_n_act_plan_id,
337 p_v_act_plan_name => l_v_act_plan_name
338 );
339
340 IF l_v_act_plan_name IS NOT NULL THEN
341
342 l_d_pay_det_date := TRUNC(SYSDATE) - NVL(l_hold_plan_rec.offset_days,0);
343
344 -- compute the sum of the installment amount and the due amount for the person
345
346 OPEN c_igs_fi_pp_instlmnts( cp_n_student_plan_id => l_n_act_plan_id ,
347 cp_d_d_pay_det_date => l_d_pay_det_date
348 );
349 FETCH c_igs_fi_pp_instlmnts INTO rec_c_igs_fi_pp_instlmnts;
350 CLOSE c_igs_fi_pp_instlmnts;
351
352 p_holds_charges := NVL(rec_c_igs_fi_pp_instlmnts.installment_amt,0);
353 p_holds_final_balance := NVL(rec_c_igs_fi_pp_instlmnts.due_amt,0);
354 p_n_student_plan_id := l_n_act_plan_id;
355 p_d_last_inst_due_date := rec_c_igs_fi_pp_instlmnts.due_date;
356
357 -- if payment plan threshold amount is provided, assign the
358 -- the c_hold_plan select value to the variable
359 IF l_hold_plan_rec.payment_plan_threshold_amt IS NOT NULL THEN
360 l_n_threshold_amount := NVL(l_hold_plan_rec.payment_plan_threshold_amt,0);
361 l_n_threshold_percent := NULL;
362 END IF;
363
364 -- if payment plan threshold percent is provided, assign the
365 -- the c_hold_plan select value to the variable
366 IF l_hold_plan_rec.payment_plan_threshold_pcent IS NOT NULL THEN
367 l_n_threshold_percent := NVL(l_hold_plan_rec.payment_plan_threshold_pcent,0);
368 l_n_threshold_amount := NULL;
369 END IF;
370
371 ELSIF l_v_act_plan_name IS NULL THEN
372
373 --Open cursor to get the latest HOLDS balances as on the process start date
374 OPEN c_holds_balance;
375 FETCH c_holds_balance INTO l_holds_balance_rec;
376 --Use a local variable to add up this latest outstanding holds balance(only the latest)
377 -- Add to the Local Holds balance variable only when the balance record is found in the igs_fi_balances table for the party_id
378 IF c_holds_balance%FOUND THEN
379 l_holds_balance := NVL(l_holds_balance_rec.holds_balance,0) + NVL(l_holds_balance,0);
380 END IF;
381 CLOSE c_holds_balance;
382
383 -- Added following logic as part of fix for Bug# 3192641
384 -- Check if any charges are waived as of process_start_date.
385 -- If yes, get the total amount waived.
386 OPEN c_waive_amount(p_person_id, TRUNC(p_process_start_date));
387 FETCH c_waive_amount INTO l_n_waive_amount;
388 CLOSE c_waive_amount;
389
390 -- Subtract waive amount from Holds Balance
391 l_holds_balance := NVL(l_holds_balance, 0) - l_n_waive_amount;
392
393 --Get the non- excluded payments/credits of person_id in context for the date range of p_process_start_date +1 to the payment_due_date.
394 OPEN c_credit_amnt(l_payment_due_date);
395 LOOP
396 FETCH c_credit_amnt INTO l_credit_amnt_rec;
397 --To be tested when completed as apart of alte fee finance charges
398 EXIT WHEN c_credit_amnt%NOTFOUND;
399 l_message_name := NULL ;
400 --invoke the common funtion for exclusion rules .If this function returns 1 then do not include this amount for the charges.
401 --5 TO MAKE IT NOT l_boolean
402 IF NOT igs_fi_prc_balances.check_exclusion_rules (p_balance_type => 'HOLDS',
403 p_source_type => 'CREDIT',
404 p_balance_date => TRUNC(l_credit_amnt_rec.effective_date),
405 p_source_id => l_credit_amnt_rec.credit_id ,
406 p_balance_rule_id => l_balance_rule_id,
407 p_message_name => l_message_name) THEN
408 IF l_message_name IS NULL THEN
409 --A local variable to sum up the non-excluded credits of the person
410 l_credit_subac := l_credit_subac + NVL(l_credit_amnt_rec.amount,0) ;
411 ELSIF l_message_name IS NOT NULL THEN
412 --Log the eror message in the log when message is returned with FALSE
413 IF p_test_run = 'Y' THEN
414 fnd_message.set_name('IGS',l_message_name);
415 fnd_file.put_line(fnd_file.log,fnd_message.get());
416 fnd_file.put_line(fnd_file.log,' ');
417 END IF;
418 END IF;
419 END IF;
420 --5
421 END LOOP;
422 CLOSE c_credit_amnt;
423 --local variable at the end of this loop to sum up the total non-excluded credits defined for the hold plan
424 l_tot_credits := l_tot_credits + l_credit_subac;
425 --4
426 -- Changes due to SFCR018 - To include planned credits also when the indicator is set as 'Y'
427 IF l_v_pln_cr_ind = 'Y' THEN
428 --Call the generic function to get the total planned credits for the params passed.
429 l_n_planned_credit := igs_fi_gen_001.finp_get_total_planned_credits(
430 p_person_id => p_person_id,
431 p_start_date => NULL,
432 p_end_date => l_payment_due_date,
433 p_message_name => l_v_pln_cr_message);
434 IF l_v_pln_cr_message IS NOT NULL THEN
435 fnd_message.set_name('IGS',l_v_pln_cr_message);
436 fnd_file.put_line(fnd_file.log,fnd_message.get());
437 fnd_file.put_line(fnd_file.log,' ');
438 RETURN FALSE;
439 END IF;
440 -- When no errors sum up the planned credits also with the actual credits
441 l_tot_credits := l_tot_credits + NVL(l_n_planned_credit,0);
442 END IF;
443 -- Changes due to SFCR018 - To include planned credits also when the indicator is set as 'Y'
444
445 --If this amount is Zero i.e holds balance is zero then we need not proceed further
446 IF l_holds_balance = 0 THEN
447 IF p_test_run = 'Y' THEN
448 fnd_file.put_line(fnd_file.log,g_v_person_number ||' : '||p_person_number);
449 fnd_file.put_line(fnd_file.log,g_v_person_name ||' : '||l_person_rec.full_name);
450 fnd_message.set_name('IGS','IGS_FI_NO_BALANCE');
451 fnd_message.set_token('PERSON',p_person_number);
452 fnd_message.set_token('PROCESS_START_DT',p_process_start_date);
453 fnd_file.put_line(fnd_file.log,fnd_message.get());
454 END IF;
455 RETURN FALSE;
456 END IF;
457 l_final_balance := l_holds_balance - l_tot_credits ;
458 --2
459 P_holds_charges := l_holds_balance;
460 P_holds_final_balance := l_final_balance;
461 END IF; /* end of if l_v_act_plan_name condition */
462 ELSIF l_hold_plan_rec.hold_plan_level = 'F' THEN
463 -- for a fee type hold plan level, fee type is mandatory
464 -- if no fee type if found attached to fee type hold plan level
465 -- an error is raised
466 IF l_hold_plan_rec.fee_type IS NULL THEN
467 fnd_message.set_name('IGS','IGS_FI_HLD_CRITERIA_NT_DEFIND');
468 fnd_file.put_line(fnd_file.log,fnd_message.get());
469 RETURN FALSE;
470 END IF;
471
472 --Get the charges records for the person FROM the charges table IGS_FI_INV_INT as on the p_process_start_date by getting the fee types for the passed hold plan
473 OPEN c_invoice_amnt(l_hold_plan_rec.fee_type);
474 LOOP
475 FETCH c_invoice_amnt INTO l_invoice_amnt_rec;
476
477 EXIT WHEN c_invoice_amnt%NOTFOUND;
478 --invoke the common funtion for exclusion rules .If this function returns 1 then do not include this amount for the charges.
479 l_message_name := NULL;
480 IF NOT igs_fi_prc_balances.check_exclusion_rules(p_balance_type => 'HOLDS',
481 p_source_type => 'CHARGE',
482 p_balance_date => TRUNC(l_invoice_amnt_rec.invoice_creation_date),
483 p_source_id => l_invoice_amnt_rec.Invoice_id ,
484 p_balance_rule_id => l_balance_rule_id,
485 p_message_name => l_message_name) THEN
486
487 IF l_message_name IS NULL THEN
488 --A local variable to sum up the non-excluded charges of the person for the invoice id.
489 l_charges := NVL(l_charges,0) + NVL(l_invoice_amnt_rec.amount,0) ;
490 l_final_balance := NVL(l_final_balance,0) + NVL(l_invoice_amnt_rec.invoice_amount_due,0) ;
491 ELSIF l_message_name IS NOT NULL THEN
492 --Log the eror message in the log when message is returned with FALSE
493 IF p_test_run = 'Y' THEN
494 fnd_message.set_name('IGS',l_message_name);
495 fnd_file.put_line(fnd_file.log,fnd_message.get());
496 fnd_file.put_line(fnd_file.log,' ');
497 END IF;
498 END IF;
499 END IF;
500 END LOOP;
501 CLOSE c_invoice_amnt;
502
503 --If this amount is Zero i.e charges sum for all the fee types is zero then we need not proceed further
504 IF NVL(l_charges,0) = 0 THEN
505 IF p_test_run = 'Y' THEN
506 fnd_file.put_line(fnd_file.log,g_v_person_number ||' : '||p_person_number);
507 fnd_file.put_line(fnd_file.log,g_v_person_name ||' : '||l_person_rec.full_name);
508 fnd_message.set_name('IGS','IGS_FI_NO_BALANCE');
509 fnd_message.set_token('PERSON',p_person_number);
510 fnd_message.set_token('PROCESS_START_DT',p_process_start_date);
511 fnd_file.put_line(fnd_file.log,fnd_message.get());
512 END IF;
513 RETURN FALSE;
514 END IF;
515 -- the non-excluded invoice amount and invoice amount_due has been assigned to OUT parameters
516 P_holds_charges := l_charges;
517 P_holds_final_balance := l_final_balance;
518 p_n_student_plan_id := NULL;
519 p_d_last_inst_due_date := NULL;
520 END IF;
521 --2
522
523 --Common Steps values to determine whether holds are applicable or not for both the cases(fee type level AND Account level / student level)
524
525
526 --Check if threshold_amount is specified
527 --10
528 IF l_n_threshold_amount IS NOT NULL THEN
529 --If the final balance is greater THEN the threshold_amount defined then mark the paritcular person applicable for placing the particular hold type
530 --11
531
532 IF p_holds_final_balance > l_n_threshold_amount THEN
533
534 RETURN TRUE;
535 ELSE
536 --11
537 RETURN FALSE;
538 END IF;
539 --11
540 ELSIF l_n_threshold_percent IS NOT NULL THEN
541 --10
542
543 l_ratio := (p_holds_final_balance/ P_holds_charges) *100;
544
545 --If ratio is greater THEN the threshold_percent then mark the particular person applicable for placing the particular hold type
546 --12
547 IF l_ratio > l_n_threshold_percent THEN
548 RETURN TRUE;
549 ELSE
550 --12
551 RETURN FALSE;
552 --12
553 END IF;
554 --10
555 ELSIF (l_n_threshold_percent IS NULL AND l_n_threshold_amount IS NULL) THEN
556 --If neither threshold_percent nor threshold_amount specified then DO NOT consider the person /hold plan to be placed under hold.
557
558 RETURN FALSE;
559 END IF;
560 --10
561 CLOSE c_hold_plan;
562
563
564 EXCEPTION
565 WHEN OTHERS THEN
566 ROLLBACK;
567 RAISE;
568 END holds_balance;
569
570
571 --To validate holds to be applied
572
573 FUNCTION validate_holds(p_person_id IN igs_pe_person_v.person_id%TYPE ,
574 p_person_number IN igs_pe_person_v.person_number%TYPE ,
575 p_hold_start_date IN igs_fi_person_holds.hold_start_dt%type,
576 p_hold_type IN igs_fi_hold_plan.hold_type%TYPE,
577 p_hold_plan_name IN igs_fi_hold_plan.hold_plan_name%TYPE,
578 p_hold_plan_level IN igs_fi_hold_plan.hold_plan_level%TYPE,
579 p_process_start_dt IN igs_fi_person_holds.process_start_dT%TYPE ,
580 p_process_end_dt IN igs_fi_person_holds.process_end_dT%TYPE ,
581 p_offset_days IN NUMBER,
582 p_holds_charges IN igs_fi_inv_int.invoice_amount%TYPE,
583 p_holds_final_balance IN igs_fi_credits.amount%TYPE,
584 p_fee_cal_type IN igs_fi_inv_int.fee_cal_type%TYPE,
585 p_fee_ci_sequence_number IN igs_fi_inv_int.fee_ci_sequence_number%TYPE,
586 p_test_run IN VARCHAR2,
587 p_n_student_plan_id IN igs_fi_pp_std_attrs.student_plan_id%TYPE,
588 p_d_last_inst_due_date IN igs_fi_pp_instlmnts.due_date%TYPE
589 )
590 RETURN BOOLEAN
591 IS
592 /***************************************************************
593 Created By : bayadav
594 Date Created By : 29-Nov-2001
595 Purpose : To validate holds to be applied
596 Known Limitations,Enhancements or Remarks:
597 Change History :
598 Who When What
599 sapanigr 04-May-2006 Enh#3924836 Precision Issue. Amount values being inserted into igs_fi_person_holds
600 are now rounded off to currency precision
601 svuppala 17-AUG-2005 Bug 4557933 - Unable to remove holds that were put on with concurrent manager
602 Passing global values to AUTHORISING_PERSON_ID and AUTH_RESP_ID from SWS Holds API
603 while calling insert_row of IGS_PE_PERS_ENCUMB_PKG
604 smadathi 28-Aug-2003 Enh Bug 3045007. Added 2 new IN parameter - p_n_student_plan_id and
605 p_d_last_inst_due_date
606 pathipat 12-Aug-2003 Enh 3076768 - Automatic Release of Holds
607 Added param x_release_credit_id to TBH calls of igs_fi_person_holds
608 pathipat 25-Feb-2003 Enh:2747341 - Additional Security for Holds build
609 Removed parameter p_auth_person_id. Passed Null to authorising_person_id
610 in the call to igs_pe_pers_encumb_pkg.insert_row
611 ssawhney 17-feb-2003 Bug : 2758856 : Added the parameter x_external_reference in the call to IGS_PE_PERS_ENCUMB_PKG.UPDATE_ROW
612 pathipat 04-OCT-2002 Enh Bug: 2562745 -- obsoleted column balance_amount from igs_fi_person_holds
613 Removed column balance_amount from call to igs_fi_person_holds_pkg.insert_row()
614 pkpatel 04-OCT-2002 Bug No: 2600842
615 Added the parameter auth_resp_id in the call to TBH igs_pe_pers_encumb_pkg
616
617 ***************************************************************/
618
619 --to check if reocrd exist in IGS_PE_PERS_ENCUMB for the passed start date
620 CURSOR c_hold_exist
621 IS
622 SELECT *
623 FROM igs_pe_pers_encumb
624 WHERE person_id = p_person_id
625 AND encumbrance_type = p_hold_type
626 AND TRUNC(start_dt) = TRUNC(p_hold_start_date);
627
628 --local varaibles declaration
629 l_hold_exist_rec c_hold_exist%ROWTYPE;
630 l_message_name fnd_new_messages.message_name%TYPE :=NULL;
631 l_message_string fnd_new_messages.message_text%TYPE;
632 l_rowid VARCHAR2(25) :=NULL;
633
634
635 BEGIN
636
637 --Validation to check if the person has the same hold type already active
638 OPEN c_hold_exist;
639 FETCH c_hold_exist INTO l_hold_exist_rec;
640 IF c_hold_exist%FOUND THEN
641 IF p_test_run = 'Y' THEN
642 fnd_message.set_name('IGS','IGS_FI_HOLD_NOT_VALID');
643 fnd_file.put_line(fnd_file.log,fnd_message.get());
644 fnd_file.put_line(fnd_file.log,' ');
645 END IF;
646 CLOSE c_hold_exist;
647 RETURN FALSE;
648 END IF;
649 CLOSE c_hold_exist;
650 -- Validation to perform check to see if new hold type will cause level conflicts with existing hold types.
651 l_message_name := NULL;
652 IF NOT (igs_en_val_pen.enrp_val_prsn_encmb(p_person_id,p_hold_type,TRUNC(p_hold_start_date), Null,l_message_name))
653 AND
654 (l_message_name IN ('IGS_EN_ENCUMB_TYPE_NOTAPPLIED','IGS_EN_ENCUMBTYPE_DIFF_LVLS','IGS_EN_ENCUMBTYPE_INV_COMBI','IGS_EN_ENCUMBTYPE_PRG_INVALID') ) THEN
655 IF p_test_run = 'Y' THEN
656 fnd_message.set_name('IGS','IGS_EN_ENCUMBTYPE_DIFF_LVLS');
657 fnd_file.put_line(fnd_file.log,fnd_message.get());
658 fnd_file.put_line(fnd_file.log,' ');
659 END IF;
660 RETURN FALSE;
661 END IF;
662
663 IF p_test_run ='N' THEN
664 l_rowid := NULL;
665
666 -- If test_tun value is 'N'
667 -- Insert into the person holds table for the person AND the hold type along with the hold start date.
668 igs_pe_pers_encumb_pkg.insert_row (
669 X_Mode => 'R',
670 X_RowId => l_rowid,
671 X_Person_Id => p_person_id,
672 X_Encumbrance_Type => p_hold_type,
673 X_CAL_TYPE => null,
674 X_SEQUENCE_NUMBER => null,
675 X_Start_Dt => p_hold_start_date,
676 X_Expiry_Dt => null,
677 X_Authorising_Person_Id => g_n_person_id,
678 X_Comments => null,
679 X_Spo_Course_Cd => null,
680 X_Spo_Sequence_Number => null,
681 x_auth_resp_id => g_n_resp_id,
682 x_external_reference => null); -- should always be null when passed from internal system
683
684 --Check if the hold type has effects that require any active enrolments of the person to be discontinued by making a call to the below function
685 l_message_name := NULL;
686
687 IF NOT igs_en_val_pen.finp_val_encmb_eff (p_person_id,
688 p_hold_type,
689 TRUNC(SYSDATE),
690 NULL,
691 l_message_name)
692 AND l_message_name IN ('IGS_FI_ENCUMB_NOTAPPLIED_RVK',
693 'IGS_FI_ENCUMB_NOTAPPLIED_EXC',
694 'IGS_EN_PERS_ENRL_COURSE',
695 'IGS_EN_CANT_APPLY_ENCUM_EFFEC',
696 'IGS_EN_DISCON_STUD_ENRL',
697 'IGS_EN_PERS_ENRL_EXCL_COURSE') THEN
698 fnd_message.set_name('IGS','IGS_FI_ACTIVE_ENRLLM_DISCONT');
699 fnd_message.set_token('HOLD_TYPE',p_hold_type);
700 fnd_message.set_token('PERSON',p_person_number);
701 fnd_file.put_line(fnd_file.log,fnd_message.get());
702 fnd_file.put_line(fnd_file.log,' ');
703
704 --TO rollback the previous insert
705 ROLLBACK;
706 RETURN FALSE;
707 END IF;
708 --To populate the default hold effects associated with the hold type placed.
709 l_message_name := NULL;
710 igs_en_gen_009.enrp_ins_dflt_effect (p_person_id,
711 p_hold_type,
712 TRUNC(p_hold_start_date),
713 null,
714 null,
715 l_message_name,
716 l_message_string);
717 IF l_message_name IS NOT NULL THEN
718 fnd_message.set_name('IGS','IGS_FI_DEFAULT_HOLD_EFFECTS');
719 fnd_message.set_token('HOLD_TYPE',p_hold_type);
720 fnd_file.put_line(fnd_file.log,fnd_message.get());
721 fnd_file.put_line(fnd_file.log,' ');
722 --TO rollback the previous insert
723 ROLLBACK;
724 RETURN FALSE;
725 END IF;
726 IF p_hold_plan_level ='S' THEN
727 l_rowid := NULL;
728 --To insert the required data elements as shown below to the intermediate table IGS_FI_PERSON_HOLDS
729 --Call to igs_fi_gen_gl.get_formatted_amount formats amount by rounding off to currency precision
730 igs_fi_person_holds_pkg.insert_row(
731 x_Mode => 'R',
732 x_RowId => l_rowid,
733 x_person_id => p_person_id,
734 x_hold_plan_name => p_hold_plan_name ,
735 x_hold_type => p_hold_type ,
736 x_hold_start_dt => p_hold_start_date,
737 x_process_start_dt => p_process_start_dt,
738 x_process_end_dt => p_process_end_dt,
739 x_offset_days => p_offset_days,
740 x_past_due_amount => igs_fi_gen_gl.get_formatted_amount(P_holds_final_balance),
741 x_fee_cal_type => NULL,
742 x_fee_ci_sequence_number => NULL,
743 x_fee_type_invoice_amount => NULL,
744 x_release_credit_id => NULL,
745 x_student_plan_id => p_n_student_plan_id,
746 x_last_instlmnt_due_date => p_d_last_inst_due_date
747 );
748
749
750 ELSIF p_hold_plan_level ='F' THEN
751 l_rowid := NULL;
752 -- Call to igs_fi_gen_gl.get_formatted_amount formats amounts by rounding off to currency precision
753 igs_fi_person_holds_pkg.insert_row(
754 x_Mode => 'R',
755 x_Rowid => l_rowid,
756 x_person_id => p_person_id,
757 x_hold_plan_name => p_hold_plan_name ,
758 x_hold_type => p_hold_type ,
759 x_hold_start_dt => p_hold_start_date,
760 x_process_start_dt => p_process_start_dt,
761 x_process_end_dt => p_process_start_dt,
762 x_offset_days => NULL,
763 x_past_due_amount => igs_fi_gen_gl.get_formatted_amount(P_holds_final_balance),
764 x_fee_cal_type => P_fee_cal_type ,
765 x_fee_ci_sequence_number => P_fee_ci_sequence_number ,
766 x_fee_type_invoice_amount => igs_fi_gen_gl.get_formatted_amount(p_holds_charges),
767 x_release_credit_id => NULL,
768 x_student_plan_id => p_n_student_plan_id,
769 x_last_instlmnt_due_date => p_d_last_inst_due_date
770 );
771
772 END IF;
773 --TO commit the data for this person as from now the processing for next person will start.
774 COMMIT;
775 END IF;
776
777 --TO return TRUE back to the main procedure if all the validations get passed
778 RETURN TRUE;
779 EXCEPTION
780 WHEN OTHERS THEN
781 ROLLBACK;
782 RAISE;
783 END validate_holds;
784
785 --Function to validate passed parameter values
786
787
788 FUNCTION validate_param(p_person_id IN igs_pe_person_v.person_id%TYPE ,
789 p_person_id_group IN igs_pe_persid_group_v.group_id%TYPE ,
790 P_process_start_date IN igs_fi_person_holds.process_start_dT%TYPE ,
791 P_process_end_date IN igs_fi_person_holds.process_end_dT%TYPE ,
792 P_hold_plan_name IN igs_fi_hold_plan.hold_plan_name%Type,
793 P_fee_cal_type IN igs_fi_inv_int.fee_cal_type%TYPE,
794 P_fee_ci_sequence_number IN igs_fi_inv_int.fee_ci_sequence_number%TYPE
795 )
796 RETURN BOOLEAN
797 IS
798 /***************************************************************
799 Created By : bayadav
800 Date Created By : 2001/04/25
801 Purpose : To validate the input parameters
802 Known Limitations,Enhancements or Remarks:
803 Change History :
804 Who When What
805 smadathi 28-Aug-2003 Enh Bug 3045007. Removed the parameter P_OFFSET_DAYS.
806 pathipat 12-Aug-2003 Enh 3076768 - Automatic Release of Holds
807 Removed calls to igs_pe_gen_001.get_hold_auth -- Removed validation for staff member
808 pathipat 23-Jun-2003 Bug: 3018104 - Impact of changes in person id group views
809 Modified cursor c_person_group_id - replaced igs_pe_persid_group_v
810 with igs_pe_persid_group
811 pathipat 25-Feb-2003 Enh:2747341 - Additional Security for Holds build
812 Removed parameter p_auth_person_id, added call to igs_pe_gen_001.get_hold_auth
813 pathipat 04-OCT-2002 Enh Bug:2562745
814 1. Added check to see if balance rule is defined for 'HOLDS', else
815 stop further processing.
816 2. Also, when the hold plan is determined at 'ACcount' level, then the
817 process_start_dt cannot be before the last_conversion_date from igs_fi_balance_rules
818 3. In check between process_start_date and process_end_date, it was made > in place of
819 >= .
820 sykrishn 30/APR/2002 c_fee_type cursor changed to compare with fee structure system status
821 bug 2348883
822
823 ***************************************************************/
824
825 -- to get the person group id related info
826 CURSOR c_person_group_id IS
827 SELECT group_id
828 FROM igs_pe_persid_group
829 WHERE group_id = p_person_id_group
830 AND TRUNC(create_dt) <= TRUNC(SYSDATE)
831 AND closed_ind = 'N';
832
833 --to get hold_plan_name related info
834 CURSOR c_hold_plan_name IS
835 SELECT hold_plan_name,
836 hold_plan_level,
837 offset_days
838 FROM igs_fi_hold_plan
839 WHERE hold_plan_name = p_hold_plan_name
840 AND closed_ind = 'N';
841
842 --to get fee related info based on the prameters value passed
843 CURSOR c_fee_type IS
844 SELECT fcc.fee_cal_type
845 FROM igs_fi_f_typ_ca_inst fcc,
846 igs_fi_fee_str_stat fss
847 WHERE fcc.fee_type_ci_status = fss.fee_structure_status
848 AND fss.s_fee_structure_status = 'ACTIVE'
849 AND fcc.fee_cal_type = p_fee_cal_type
850 AND fcc.fee_ci_sequence_number = p_fee_ci_sequence_number ;
851
852 --declaration of local variables
853
854 l_person_group_id_rec c_person_group_id%ROWTYPE;
855 l_hold_plan_name_rec c_hold_plan_name%ROWTYPE;
856 l_fee_type_rec c_fee_type%ROWTYPE;
857
858 l_last_conv_dt igs_fi_balance_rules.last_conversion_date%TYPE;
859 l_version_number igs_fi_balance_rules.version_number%TYPE;
860
861 BEGIN
862 --Check for mandatory parameters
863 IF p_process_start_date IS NULL THEN
864 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
865 fnd_message.set_token('PARAMETER','P_PROCESS_START_DATE');
866 fnd_file.put_line(fnd_file.log,' ');
867 RETURN FALSE;
868 ELSIF p_hold_plan_name IS NULL THEN
869 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
870 fnd_message.set_token('PARAMETER','P_HOLD_PLAN_NAME');
871 fnd_file.put_line(fnd_file.log,' ');
872 RETURN FALSE;
873 END IF;
874
875 igs_fi_gen_007.finp_get_balance_rule ( p_v_balance_type => 'HOLDS',
876 p_v_action => 'ACTIVE',
877 p_n_balance_rule_id => l_balance_rule_id,
878 p_d_last_conversion_date => l_last_conv_dt,
879 p_n_version_number => l_version_number );
880
881 IF l_version_number = 0 THEN
882 -- Exit if balance rule is not defined
883 fnd_message.set_name('IGS','IGS_FI_BR_CANNOT_APP_HLDS');
884 fnd_file.put_line(fnd_file.log,fnd_message.get());
885 fnd_file.put_line(fnd_file.log,' ');
886 RETURN FALSE;
887 END IF;
888
889 IF (p_person_id IS NOT NULL AND p_person_id_group IS NOT NULL) THEN
890 -- Exit if the value of both the parameters p_person_id AND p_person_id_group are not NUll
891 fnd_message.set_name('IGS','IGS_FI_NO_PERS_PGRP');
892 fnd_file.put_line(fnd_file.log,fnd_message.get());
893 fnd_file.put_line(fnd_file.log,' ');
894 RETURN FALSE;
895 ELSIF p_person_id IS NOT NULL AND p_person_id_group IS NULL THEN
896 -- Exit if the passed person_id is not valid
897 OPEN c_person(p_person_id);
898 FETCH c_person INTO l_person_rec;
899 IF c_person%NOTFOUND THEN
900 fnd_message.set_name('IGS','IGS_FI_INVALID_PERSON_ID');
901 fnd_file.put_line(fnd_file.log,fnd_message.get());
902 fnd_file.put_line(fnd_file.log,' ');
903 CLOSE c_person;
904 RETURN FALSE;
905 END IF;
906 CLOSE c_person;
907 ELSIF p_person_id IS NULL AND p_person_id_group IS NOT NULL THEN
908 -- Exit if the passed person group id is not valid
909 OPEN c_person_group_id;
910 FETCH c_person_group_id INTO l_person_group_id_rec;
911 IF c_person_group_id%NOTFOUND THEN
912 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
913 fnd_message.set_token('PARAMETER','P_PERSON_ID_GROUP');
914 fnd_file.put_line(fnd_file.log,' ');
915 CLOSE c_person_group_id;
916 RETURN FALSE;
917 END IF;
918 CLOSE c_person_group_id;
919 END IF;
920
921 --To check for p_process_start_date validity
922 IF TRUNC(p_process_start_date) > TRUNC(SYSDATE) THEN
923 fnd_message.set_name('IGS','IGS_FI_PR_ST_DT');
924 fnd_file.put_line(fnd_file.log,fnd_message.get());
925 fnd_file.put_line(fnd_file.log,' ');
926 RETURN FALSE;
927
928 -- If p_process_end_date is not NULL then (process_end_date +offset days) should not be greater THEN
929 --sysdate
930 ELSIF p_process_end_date IS NOT NULL THEN
931 OPEN c_hold_plan_name;
932 FETCH c_hold_plan_name INTO l_hold_plan_name_rec;
933 CLOSE c_hold_plan_name;
934 IF (TRUNC(p_process_end_date) + NVL(l_hold_plan_name_rec.offset_days,0)) > TRUNC(SYSDATE) THEN
935 fnd_message.set_name('IGS','IGS_FI_PR_EN_OFF_DT');
936 fnd_file.put_line(fnd_file.log,fnd_message.get());
937 fnd_file.put_line(fnd_file.log,' ');
938 RETURN FALSE;
939 END IF;
940
941 --If p_process_end_date is not NULL then process_end_date should not be less than process_start_date
942 IF TRUNC(p_process_start_date) > TRUNC(p_process_end_date) THEN
943 fnd_message.set_name('IGS','IGS_FI_PR_EN_DT');
944 fnd_file.put_line(fnd_file.log,fnd_message.get());
945 fnd_file.put_line(fnd_file.log,' ');
946 RETURN FALSE;
947 END IF;
948 END IF;
949
950 --To check for hold plan name validity
951 OPEN c_hold_plan_name;
952 FETCH c_hold_plan_name INTO l_hold_plan_name_rec;
953 IF c_hold_plan_name%NOTFOUND THEN
954 fnd_message.set_name('IGS','IGS_FI_INVALID_HP');
955 fnd_file.put_line(fnd_file.log,fnd_message.get());
956 fnd_file.put_line(fnd_file.log,' ');
957 CLOSE c_hold_plan_name;
958 RETURN FALSE;
959 END IF;
960 CLOSE c_hold_plan_name;
961
962 --To find the level at which the hold plan is defined
963 --2
964 IF l_hold_plan_name_rec.hold_plan_level = 'S' THEN
965 -- If hold plan is defined at Account level, then process_start_Date cannot be before the
966 -- last_conversion_date of the latest active balance rule for 'HOLDS' type
967 IF (TRUNC(p_process_start_date) < TRUNC(l_last_conv_dt)) THEN
968 fnd_message.set_name('IGS','IGS_FI_FINAPP_HLDS_DT');
969 fnd_message.set_token('DATE1',p_process_start_date);
970 fnd_message.set_token('DATE2',l_last_conv_dt);
971 fnd_file.put_line(fnd_file.log,fnd_message.get());
972 fnd_file.put_line(fnd_file.log,' ');
973 RETURN FALSE;
974 END IF;
975
976 --If the hold plan is defined at Account/student level then the parameter p_process_end_date is mandatory
977 IF p_process_end_date IS NULL THEN
978 fnd_message.set_name('IGS','IGS_FI_PROCESS_ENDDT_NULL');
979 fnd_file.put_line(fnd_file.log,fnd_message.get());
980 fnd_file.put_line(fnd_file.log,' ');
981 RETURN FALSE;
982 END IF;
983 ELSIF l_hold_plan_name_rec.hold_plan_level = 'F' THEN
984 --To check if the mandatory parameters p_fee_cal_type AND p_fee_ci_sequence_number are specified
985 --if the hold_plan_level SELECTed above is 'F'.
986 --6
987 IF p_fee_cal_type IS NULL AND p_fee_ci_sequence_number IS NULL THEN
988 fnd_message.set_name('IGS','IGS_FI_FEE_PERIOD_NOT_NULL');
989 fnd_file.put_line(fnd_file.log,fnd_message.get());
990 fnd_file.put_line(fnd_file.log,' ');
991 RETURN FALSE;
992 --6
993 END IF;
994 END IF;
995
996 --To check for fee_cal_type validity if specified
997 IF p_fee_cal_type IS NOT NULL AND p_fee_ci_sequence_number IS NOT NULL THEN
998 OPEN c_fee_type;
999 FETCH c_fee_type INTO l_fee_type_rec;
1000 IF c_fee_type%NOTFOUND THEN
1001 fnd_message.set_name('IGS','IGS_FI_FTCI_NOTFOUND');
1002 fnd_file.put_line(fnd_file.log,fnd_message.get());
1003 fnd_file.put_line(fnd_file.log,' ');
1004 CLOSE c_fee_type;
1005 RETURN FALSE;
1006 END IF;
1007 CLOSE c_fee_type;
1008 END IF;
1009
1010 --IF all the validations get passed then RETURN TRUE BACK to the main procedure
1011 RETURN TRUE;
1012
1013 EXCEPTION
1014 WHEN OTHERS THEN
1015 ROLLBACK;
1016 RAISE;
1017 END validate_param;
1018
1019
1020
1021 PROCEDURE finp_apply_holds(errbuf OUT NOCOPY VARCHAR2,
1022 retcode OUT NOCOPY NUMBER,
1023 p_person_id IN igs_pe_person_v.person_id%TYPE ,
1024 p_person_id_group IN igs_pe_persid_group_v.group_id%TYPE ,
1025 P_process_start_date IN VARCHAR2 ,
1026 P_process_end_date IN VARCHAR2 ,
1027 P_hold_plan_name IN igs_fi_hold_plan.hold_plan_name%Type,
1028 P_fee_period IN VARCHAR2,
1029 P_test_run IN VARCHAR2 )
1030
1031 IS
1032
1033 /***************************************************************
1034 Created By : bayadav
1035 Date Created By : 29-Nov-2001
1036 Purpose : To carry out NOCOPY the functionality of application of holds for the person.
1037 Known Limitations,Enhancements or Remarks:
1038 Change History :
1039 Who When What
1040 svuppala 17-AUG-2005 Bug 4557933 - Unable to remove holds that were put on with concurrent manager
1041 Validate_holds : getting global values for AUTHORISING_PERSON_ID and AUTH_RESP_ID from SWS Holds API
1042 pmarada 26-jul-2004 Bug 3792800, Added code to bypass the holds apply validation
1043 smadathi 28-Aug-2003 Enh Bug 3045007. Removed the parameter P_OFFSET_DAYS from
1044 procedure finp_apply_holds and all the references of P_OFFSET_DAYS
1045 pathipat 23-Jun-2003 Bug: 3018104 - Impact of changes in person id group views
1046 Modified cursor c_person_group - replaced igs_pe_prsid_grp_mem_v
1047 with igs_pe_prsid_grp_mem
1048 pathipat 05-May-2003 Enh 2831569 - Commercial Receivables Build
1049 Added check for manage_accounts - call to igs_fi_com_rec_interface.check_manage_acc()
1050 vvutukur 25-Mar-2003 Enh#2824994.Modified code such that log file output is not shown in horizontal tabular format, instead it
1051 it is shown in linear/logging format.Removed the code using l_person_ind as it is redundant since it is used
1052 earlier to print header in tabular format only once when the person is processed for the first time.
1053 Also used global variables to log the details.
1054 pathipat 25-Feb-2003 Enh:2747341 - Additional Security for Holds build
1055 Removed parameter p_auth_person_id and related code
1056 Removed p_auth_person_id in calls to functions validate_holds and validate_param
1057 SYKRISHn 03-JAN-2002 Bug 2684895 --Procedure finp_apply_holds
1058 Logging Person Group Cd instead of person group id.
1059 used igs_fi_gen_005.finp_get_prsid_grp_code
1060 SYKRISHN 31DEC2002 Bug 2676524 - Procedure finp_apply_holds
1061 Derived the person number for the parameter p_auth_person_id
1062 to display in the concurrent log file instead of the P_auth_person_id which
1063 was logged earlier.
1064
1065 pathipat 04-OCT-2002 Enh Bug:2562745, added check that if the holds conversion process
1066 is running, then application of holds cannot take place.
1067 ***************************************************************/
1068 --Cursor to SELECT dstinct persons FROM igs_pe_prsid_grp_mem_v if person group id is passsed
1069 CURSOR c_person_group IS
1070 SELECT person_id
1071 FROM igs_pe_prsid_grp_mem
1072 WHERE (TRUNC(end_date) IS NULL OR TRUNC(end_date) >= TRUNC(SYSDATE))
1073 AND group_id = p_person_id_group;
1074 --Cursor to SELECT distinct persons FROM igs_fi_inv_int if person id AND person group id are passsed as NULL
1075 CURSOR c_person_inv_int IS
1076 SELECT DISTINCT person_id
1077 FROM igs_fi_inv_int;
1078
1079 --Declare variables to store the values of cursors decalred above.
1080 l_person_group_rec c_person_group%ROWTYPE;
1081 l_person_inv_int_rec c_person_inv_int%ROWTYPE;
1082 l_fee_cal_type igs_fi_inv_int.fee_cal_type%TYPE;
1083 l_fee_ci_sequence_number igs_fi_inv_int.fee_ci_sequence_number%TYPE;
1084 l_person_number igs_pe_person.person_number%TYPE :=NULL;
1085 l_person_name igs_pe_person_base_v.full_name%TYPE :=NULL;
1086 l_message_name fnd_new_messages.message_name%TYPE :=NULL;
1087 l_message_name_1 fnd_new_messages.message_name%TYPE :=NULL;
1088 l_hold_type igs_fi_hold_plan.hold_type%TYPE;
1089 l_hold_plan_level igs_fi_hold_plan.hold_plan_level%TYPE;
1090 l_hold_plan_name igs_fi_hold_plan.hold_plan_name%TYPE;
1091 l_holds_charges igs_fi_inv_int.invoice_amount%TYPE := 0;
1092 l_holds_final_balance igs_fi_credits.amount%TYPE := 0;
1093 l_count PLS_INTEGER := 0;
1094 l_msg_str_0 VARCHAR2(1000) :=NULL;
1095 l_msg_str_1 VARCHAR2(1000) :=NULL;
1096 l_process_start_date igs_fi_person_holds.process_start_dT%TYPE;
1097 l_process_end_date igs_fi_person_holds.process_end_dT%TYPE;
1098
1099 l_process_run_ind igs_fi_control_all.conv_process_run_ind%TYPE;
1100
1101 l_v_manage_acc igs_fi_control_all.manage_accounts%TYPE := NULL;
1102 l_v_message_name fnd_new_messages.message_name%TYPE :=NULL;
1103 l_n_offset_days igs_fi_hold_plan.offset_days%TYPE :=0;
1104 l_n_student_plan_id igs_fi_pp_std_attrs.student_plan_id%TYPE;
1105 l_d_last_inst_due_date igs_fi_pp_instlmnts.due_date%TYPE;
1106 l_n_fnd_user_id fnd_user.user_id%TYPE;
1107 l_v_person_number hz_parties.party_number%TYPE;
1108 l_v_person_name hz_person_profiles.person_name%TYPE;
1109 l_v_msg_name FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
1110
1111
1112 BEGIN
1113
1114 --TO set the org id
1115 igs_ge_gen_003.set_org_id(null);
1116 retcode:= 0;
1117 --initialise the global variables
1118 g_n_resp_id := NULL;
1119 g_n_person_id := NULL;
1120 -- Initializing the hold validation global parameter to N, This global variable defined in IGSNI18S.pls
1121 -- with default value Y, If the value is Y then TBH validates the holds for person. But as per bug
1122 -- we have to bypass the apply holds validation, hence we are initializing to N. pmarada, bug 3792800
1123 igs_pe_gen_001.g_hold_validation := 'N';
1124
1125 IF p_fee_period IS NOT NULL THEN
1126 l_fee_cal_type := RTRIM(SUBSTR(p_fee_period, 102, 10));
1127 l_fee_ci_sequence_number := TO_NUMBER(LTRIM(SUBSTR(p_fee_period, 113,8)));
1128 END IF;
1129
1130 --To get the value of passed date parameters in canonical format
1131 IF p_process_start_date IS NOT NULL THEN
1132 l_process_start_date := TRUNC(igs_ge_date.igsdate(p_process_start_date));
1133 END IF;
1134
1135 IF p_process_end_date IS NOT NULL THEN
1136 l_process_end_date := TRUNC(igs_ge_date.igsdate(p_process_end_date));
1137 END IF;
1138
1139 --To get the passed person id if not NULL corresponding person number
1140 IF p_person_id IS NOT NULL THEN
1141 OPEN c_person(p_person_id);
1142 FETCH c_person INTO l_person_rec;
1143 l_person_number := l_person_rec.person_number;
1144 l_person_name := l_person_rec.full_name;
1145 CLOSE c_person;
1146 END IF;
1147
1148 --TO print the passed parameter values at the starting of log
1149 fnd_message.set_name('IGS','IGS_FI_APPLY_HOLD_PARAM');
1150 fnd_message.set_token('PERSON_NUMBER', l_person_number);
1151 fnd_message.set_token('PERSON_GROUP',igs_fi_gen_005.finp_get_prsid_grp_code(p_person_id_group));
1152 fnd_message.set_token('PROCESS_START_DATE',p_process_start_date);
1153 fnd_message.set_token('PROCESS_END_DATE',p_process_end_date);
1154 fnd_message.set_token('HOLD_PLAN_NAME',p_hold_plan_name);
1155 fnd_message.set_token('FEE_PERIOD',p_fee_period);
1156 fnd_message.set_token('TEST_RUN',p_test_run);
1157 fnd_file.put_line(fnd_file.log,fnd_message.get());
1158 fnd_file.put_line(fnd_file.log,' ');
1159
1160 -- Obtain the value of manage_accounts in the System Options form
1161 -- If it is null or 'OTHER', then this process is not available, so error out.
1162 igs_fi_com_rec_interface.chk_manage_account( p_v_manage_acc => l_v_manage_acc,
1163 p_v_message_name => l_v_message_name
1164 );
1165 IF (l_v_manage_acc = 'OTHER') OR (l_v_manage_acc IS NULL) THEN
1166 fnd_message.set_name('IGS',l_v_message_name);
1167 fnd_file.put_line(fnd_file.log,fnd_message.get());
1168 fnd_file.new_line(fnd_file.log);
1169 retcode := 2; -- error out
1170 RETURN;
1171 END IF;
1172
1173 -- If the holds conversion process is running (conv_process_run_ind of the control_all table
1174 -- will be 1 if the process is running), then error out. Else continue.
1175 igs_fi_gen_007.finp_get_conv_prc_run_ind( p_n_conv_process_run_ind => l_process_run_ind,
1176 p_v_message_name => l_message_name_1) ;
1177
1178 IF l_message_name_1 IS NOT NULL THEN
1179 fnd_message.set_name('IGS',l_message_name_1);
1180 fnd_file.put_line(fnd_file.log,fnd_message.get());
1181 fnd_file.new_line(fnd_file.log);
1182 retcode := 2; -- error out
1183 RETURN;
1184 END IF;
1185
1186 IF l_process_run_ind = 1 THEN
1187 -- Stop further processing as the holds conversion process is running
1188 fnd_message.set_name('IGS','IGS_FI_REASS_BAL_PRC_RUN');
1189 fnd_file.put_line(fnd_file.log,fnd_message.get());
1190 fnd_file.put_line(fnd_file.log,' ');
1191 retcode := 2; -- error out
1192 RETURN;
1193 END IF;
1194
1195 --function to validate the input parameters .In this if any of the validation fails appropriate errors are logged
1196 --proceessing does not take palce further.
1197 --1
1198 IF NOT validate_param(p_person_id ,
1199 p_person_id_group ,
1200 l_process_start_date ,
1201 l_process_end_date ,
1202 p_hold_plan_name ,
1203 l_fee_cal_type ,
1204 l_fee_ci_sequence_number
1205 ) THEN
1206 retcode := 2;
1207 RETURN;
1208 --1
1209 ELSE
1210 l_n_fnd_user_id := FND_GLOBAL.USER_ID;
1211 g_n_resp_id := FND_GLOBAL.RESP_ID;
1212 --derive the attributes - Authorising_Person_Id
1213 igs_pe_gen_001.get_hold_auth(l_n_fnd_user_id,
1214 g_n_person_id,
1215 l_v_person_number,
1216 l_v_person_name,
1217 l_v_msg_name);
1218
1219 IF l_v_msg_name IS NOT NULL THEN
1220 FND_MESSAGE.SET_NAME('IGS',l_v_msg_name);
1221 fnd_file.put_line(fnd_file.log,fnd_message.get());
1222 fnd_file.put_line(fnd_file.log,' ');
1223 retcode := 2;
1224 RETURN;
1225 END IF;
1226
1227 --To check only for 1 condition as for 0 the further execution has not to be done AND error message has been logged earlier in validate_param
1228 --2
1229 IF p_person_id IS NOT NULL THEN
1230 --3
1231
1232 IF NOT holds_balance ( p_person_id ,
1233 l_person_number,
1234 p_hold_plan_name ,
1235 l_fee_cal_type ,
1236 l_fee_ci_sequence_number ,
1237 l_process_end_date ,
1238 l_process_start_date ,
1239 p_test_run,
1240 l_hold_type ,
1241 l_hold_plan_level,
1242 l_holds_charges ,
1243 l_holds_final_balance,
1244 l_n_offset_days,
1245 l_n_student_plan_id,
1246 l_d_last_inst_due_date
1247 ) THEN
1248 fnd_message.set_name('IGS','IGS_FI_HOLD_NOT_APPLY');
1249 fnd_message.set_token('PERSON',l_person_number);
1250 fnd_file.put_line(fnd_file.log,fnd_message.get());
1251 fnd_file.put_line(fnd_file.log,' ');
1252 RETURN;
1253 ELSE
1254
1255 --Call a private function for validating the values to apply holds for the person,toinsert data into holds table AND to print
1256 --the output.
1257 IF NOT validate_holds( p_person_id,
1258 l_person_number,
1259 TRUNC(SYSDATE),
1260 l_hold_type,
1261 p_hold_plan_name,
1262 l_hold_plan_level,
1263 l_process_start_date,
1264 l_process_end_date,
1265 l_n_offset_days,
1266 l_holds_charges,
1267 l_holds_final_balance,
1268 l_fee_cal_type ,
1269 l_fee_ci_sequence_number ,
1270 p_test_run,
1271 l_n_student_plan_id,
1272 l_d_last_inst_due_date
1273 ) THEN
1274 fnd_message.set_name('IGS','IGS_FI_HOLD_NOT_APPLY');
1275 fnd_message.set_token('PERSON', l_person_number);
1276 fnd_file.put_line(fnd_file.log,fnd_message.get());
1277 fnd_file.put_line(fnd_file.log,' ');
1278 RETURN;
1279 ELSE
1280
1281 --count of persons on whom holds is applied
1282 l_count := l_count +1;
1283 --To print the log file for both the cases of test_run parameter value
1284 IF p_test_run = 'Y' AND l_count = 1 THEN
1285 fnd_message.set_name('IGS','IGS_FI_HOLDS_APPLY');
1286 fnd_file.put_line(fnd_file.log,fnd_message.get());
1287 fnd_file.put_line(fnd_file.log,' ');
1288 ELSIF p_test_run = 'N' AND l_count = 1 THEN
1289 fnd_message.set_name('IGS','IGS_FI_HLDS_HV_APP');
1290 fnd_file.put_line(fnd_file.log,fnd_message.get());
1291 fnd_file.put_line(fnd_file.log,' ');
1292 END IF;
1293
1294 fnd_file.put_line(fnd_file.log,g_v_person_number ||' : '||l_person_number);
1295 fnd_file.put_line(fnd_file.log,g_v_person_name ||' : '||l_person_name);
1296 fnd_file.put_line(fnd_file.log,g_n_holds_bal ||' : '||TO_CHAR(l_holds_charges));
1297 fnd_file.put_line(fnd_file.log,g_n_overdue_bal ||' : '||TO_CHAR(l_holds_final_balance));
1298 fnd_file.put_line(fnd_file.log,g_v_hold_type ||' : '||l_hold_type);
1299 fnd_file.put_line(fnd_file.log,' ');
1300 END IF;
1301 --3
1302 END IF;
1303 --2
1304 ELSIF p_person_id_group IS NOT NULL THEN
1305 OPEN c_person_group;
1306 LOOP
1307 FETCH c_person_group INTO l_person_group_rec;
1308 EXIT WHEN c_person_group%NOTFOUND;
1309 BEGIN
1310 OPEN c_person(l_person_group_rec.person_id);
1311 FETCH c_person INTO l_person_rec;
1312 CLOSE c_person;
1313 --4
1314 IF NOT holds_balance (l_person_group_rec.person_id ,
1315 l_person_rec.person_number ,
1316 p_hold_plan_name ,
1317 l_fee_cal_type ,
1318 l_fee_ci_sequence_number ,
1319 l_process_end_date ,
1320 l_process_start_date ,
1321 p_test_run,
1322 l_hold_type ,
1323 l_hold_plan_level,
1324 l_holds_charges ,
1325 l_holds_final_balance,
1326 l_n_offset_days ,
1327 l_n_student_plan_id,
1328 l_d_last_inst_due_date
1329 ) THEN
1330 RAISE skip;
1331 --4
1332 ELSE
1333 IF NOT validate_holds(l_person_group_rec.person_id ,
1334 l_person_rec.person_number,
1335 TRUNC(SYSDATE),
1336 l_hold_type ,
1337 p_hold_plan_name,
1338 l_hold_plan_level ,
1339 l_process_start_date,
1340 l_process_end_date ,
1341 l_n_offset_days ,
1342 l_holds_charges ,
1343 l_holds_final_balance,
1344 l_fee_cal_type ,
1345 l_fee_ci_sequence_number ,
1346 p_test_run,
1347 l_n_student_plan_id,
1348 l_d_last_inst_due_date
1349 ) THEN
1350 RAISE skip;
1351 ELSE
1352 --count of persons on whom holds is applied
1353 l_count := l_count +1;
1354
1355 --To print the log file for both the cases of test_run parameter value
1356 IF p_test_run = 'Y' AND l_count = 1 THEN
1357 fnd_message.set_name('IGS','IGS_FI_HOLDS_APPLY');
1358 fnd_file.put_line(fnd_file.log,fnd_message.get());
1359 fnd_file.put_line(fnd_file.log,' ');
1360 ELSIF p_test_run = 'N' AND l_count = 1 THEN
1361 fnd_message.set_name('IGS','IGS_FI_HLDS_HV_APP');
1362 fnd_file.put_line(fnd_file.log,fnd_message.get());
1363 fnd_file.put_line(fnd_file.log,' ');
1364 END IF;
1365
1366 fnd_file.put_line(fnd_file.log,g_v_person_number ||' : '||l_person_rec.person_number);
1367 fnd_file.put_line(fnd_file.log,g_v_person_name ||' : '||l_person_rec.full_name);
1368 fnd_file.put_line(fnd_file.log,g_n_holds_bal ||' : '||TO_CHAR(l_holds_charges));
1369 fnd_file.put_line(fnd_file.log,g_n_overdue_bal ||' : '||TO_CHAR(l_holds_final_balance));
1370 fnd_file.put_line(fnd_file.log,g_v_hold_type ||' : '||l_hold_type);
1371 fnd_file.put_line(fnd_file.log,' ');
1372 END IF;
1373 --4
1374 END IF;
1375
1376 EXCEPTION
1377 WHEN skip THEN
1378 IF c_person%ISOPEN THEN
1379 CLOSE c_person;
1380 END IF;
1381
1382 fnd_message.set_name('IGS','IGS_FI_HOLD_NOT_APPLY');
1383 fnd_message.set_token('PERSON',l_person_rec.person_number);
1384 fnd_file.put_line(fnd_file.log,fnd_message.get());
1385 fnd_file.put_line(fnd_file.log,' ');
1386 WHEN OTHERS THEN
1387 ROLLBACK;
1388 fnd_file.put_line(fnd_file.log,sqlerrm);
1389 fnd_file.put_line(fnd_file.log,' ');
1390 END;
1391 END LOOP;
1392 CLOSE c_person_group;
1393 --2
1394 ELSE
1395 --Both person_id AND person_group_code are NULL
1396 OPEN c_person_inv_int ;
1397 LOOP
1398 FETCH c_person_inv_int INTO l_person_inv_int_rec;
1399 EXIT WHEN c_person_inv_int%NOTFOUND;
1400 BEGIN
1401 OPEN c_person(l_person_inv_int_rec.person_id);
1402 FETCH c_person INTO l_person_rec;
1403 CLOSE c_person;
1404 --5
1405 IF NOT holds_balance (l_person_inv_int_rec.person_id ,
1406 l_person_rec.person_number,
1407 p_hold_plan_name ,
1408 l_fee_cal_type ,
1409 l_fee_ci_sequence_number ,
1410 l_process_end_date ,
1411 l_process_start_date ,
1412 p_test_run,
1413 l_hold_type ,
1414 l_hold_plan_level,
1415 l_holds_charges ,
1416 l_holds_final_balance,
1417 l_n_offset_days,
1418 l_n_student_plan_id,
1419 l_d_last_inst_due_date
1420 ) THEN
1421 RAISE skip;
1422 --5
1423 ELSE
1424 IF NOT validate_holds(l_person_inv_int_rec.person_id ,
1425 l_person_rec.person_number,
1426 TRUNC(SYSDATE),
1427 l_hold_type ,
1428 p_hold_plan_name,
1429 l_hold_plan_level ,
1430 l_process_start_date,
1431 l_process_end_date ,
1432 l_n_offset_days,
1433 l_holds_charges ,
1434 l_holds_final_balance,
1435 l_fee_cal_type ,
1436 l_fee_ci_sequence_number ,
1437 p_test_run,
1438 l_n_student_plan_id,
1439 l_d_last_inst_due_date
1440 ) THEN
1441 RAISE skip;
1442 ELSE
1443 --count of persons on whom holds is applied
1444 l_count := l_count +1;
1445
1446 --To print the log file for both the cases of test_run parameter value
1447 IF p_test_run = 'Y' AND l_count = 1 THEN
1448 fnd_message.set_name('IGS','IGS_FI_HOLDS_APPLY');
1449 fnd_file.put_line(fnd_file.log,fnd_message.get());
1450 fnd_file.put_line(fnd_file.log,' ');
1451 ELSIF p_test_run = 'N' AND l_count = 1 THEN
1452 fnd_message.set_name('IGS','IGS_FI_HLDS_HV_APP');
1453 fnd_file.put_line(fnd_file.log,fnd_message.get());
1454 fnd_file.put_line(fnd_file.log,' ');
1455 END IF;
1456
1457 fnd_file.put_line(fnd_file.log,g_v_person_number ||' : '||l_person_rec.person_number);
1458 fnd_file.put_line(fnd_file.log,g_v_person_name ||' : '||l_person_rec.full_name);
1459 fnd_file.put_line(fnd_file.log,g_n_holds_bal ||' : '||TO_CHAR(l_holds_charges));
1460 fnd_file.put_line(fnd_file.log,g_n_overdue_bal ||' : '||TO_CHAR(l_holds_final_balance));
1461 fnd_file.put_line(fnd_file.log,g_v_hold_type ||' : '||l_hold_type);
1462 fnd_file.put_line(fnd_file.log,' ');
1463 END IF;
1464 --5
1465 END IF;
1466
1467 EXCEPTION
1468 WHEN skip THEN
1469 fnd_message.set_name('IGS','IGS_FI_HOLD_NOT_APPLY');
1470 fnd_message.set_token('PERSON',l_person_rec.person_number);
1471 fnd_file.put_line(fnd_file.log,fnd_message.get());
1472 fnd_file.put_line(fnd_file.log,' ');
1473
1474 IF c_person%ISOPEN THEN
1475 CLOSE c_person;
1476 END IF;
1477 WHEN OTHERS THEN
1478 ROLLBACK;
1479 fnd_file.put_line(fnd_file.log,sqlerrm);
1480 fnd_file.put_line(fnd_file.log,' ');
1481 END;
1482 END LOOP;
1483 CLOSE c_person_inv_int;
1484 --2
1485 END IF;
1486 --1
1487 END IF;
1488
1489 --to display the count of persons on whom holds is applied
1490 IF p_test_run = 'N' THEN
1491 fnd_file.put_line(fnd_file.log,' ');
1492 fnd_message.set_name('IGS','IGS_FI_TOTAL_HOLDS_APPLY');
1493 fnd_message.set_token('COUNT',l_count);
1494 fnd_file.put_line(fnd_file.log,fnd_message.get());
1495 END IF;
1496
1497
1498 -- It is possible to do a test run which does not change
1499 -- data but performs all the assessment processing.
1500 IF (p_test_run = 'N') THEN
1501 COMMIT;
1502 ELSE
1503 ROLLBACK;
1504 END IF;
1505 -- Hold validation is global variable, For bypassing the hold validation we initialized to N
1506 -- at the starting of the process, after completes the process re-initializing back default value Y.
1507 igs_pe_gen_001.g_hold_validation := 'Y';
1508
1509 EXCEPTION
1510 WHEN OTHERS THEN
1511 ROLLBACK;
1512 retcode := 2;
1513 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION')||SUBSTR(sqlerrm,1,40);
1514 igs_ge_msg_stack.add;
1515 igs_ge_msg_stack.conc_exception_hndl;
1516 END finp_apply_holds;
1517
1518
1519 FUNCTION validate_param(p_person_id IN igs_pe_person_v.person_id%TYPE,
1520 p_person_id_group IN igs_pe_persid_group_v.group_id%TYPE,
1521 P_hold_plan_name IN igs_fi_hold_plan.hold_plan_name%Type,
1522 P_message_name OUT NOCOPY fnd_new_messages.message_name%TYPE)
1523 RETURN BOOLEAN
1524 IS
1525 /***************************************************************
1526 Created By : bayadav
1527 Date Created By : 2001/04/25
1528 Purpose : To validate the input parameters
1529 Known Limitations,Enhancements or Remarks:
1530 Change History :
1531 Who When What
1532 pathipat 12-Aug-2003 Enh 3076768 - Automatic Release of Holds
1533 Removed calls to igs_pe_gen_001.get_hold_auth
1534 pathipat 23-Jun-2003 Bug: 3018104 - Impact of changes in person id group views
1535 Modified cursor c_person_group_id - replaced igs_pe_persid_group_v
1536 with igs_pe_persid_group
1537 pathipat 25-Feb-2003 Enh:2747341 - - Additional Security for Holds build
1538 Added validation for hold authorizer
1539 ***************************************************************/
1540 CURSOR c_person_group_id
1541 IS
1542 SELECT group_id
1543 FROM igs_pe_persid_group
1544 WHERE group_id = p_person_id_group
1545 AND TRUNC(create_dt) <= TRUNC(SYSDATE)
1546 AND closed_ind = 'N';
1547
1548 CURSOR c_hold_plan_name
1549 IS
1550 SELECT hold_plan_name
1551 FROM igs_fi_hold_plan
1552 WHERE hold_plan_name = p_hold_plan_name;
1553
1554 l_person_group_id_rec c_person_group_id%ROWTYPE;
1555 l_hold_plan_name_rec c_hold_plan_name%ROWTYPE;
1556
1557 BEGIN
1558
1559 IF (p_person_id IS NOT NULL AND p_person_id_group IS NOT NULL) THEN
1560
1561 -- Exit if the value of both the parameters p_person_id and p_person_id_group are not NULL
1562 fnd_message.set_name('IGS','IGS_FI_NO_PERS_PGRP');
1563 p_message_name := 'IGS_FI_NO_PERS_PGRP';
1564 fnd_file.put_line(fnd_file.log,fnd_message.get());
1565 fnd_file.put_line(fnd_file.log,' ');
1566 RETURN FALSE;
1567 ELSIF ( p_person_id IS NOT NULL AND p_person_id_group IS NULL) THEN
1568
1569 -- Exit if the passed person_id is not valid
1570
1571 OPEN c_person(p_person_id);
1572 FETCH c_person INTO l_person_rec;
1573 IF c_person%NOTFOUND THEN
1574 fnd_message.set_name('IGS','IGS_FI_INVALID_PERSON_ID');
1575 p_message_name := 'IGS_FI_INVALID_PERSON_ID';
1576 fnd_file.put_line(fnd_file.log,fnd_message.get());
1577 fnd_file.put_line(fnd_file.log,' ');
1578 CLOSE c_person;
1579 RETURN FALSE;
1580 END IF;
1581 CLOSE c_person;
1582 ELSIF (p_person_id IS NULL AND p_person_id_group IS NOT NULL) THEN
1583
1584 -- Exit if the passed person group id is not valid
1585 OPEN c_person_group_id;
1586 FETCH c_person_group_id INTO l_person_group_id_rec;
1587 IF c_person_group_id%NOTFOUND THEN
1588 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
1589 fnd_message.set_token('PARAMETER','P_PERSON_ID_GROUP');
1590 p_message_name := 'IGS_GE_INVALID_VALUE';
1591 fnd_file.put_line(fnd_file.log,fnd_message.get());
1592 fnd_file.put_line(fnd_file.log,' ');
1593 CLOSE c_person_group_id;
1594 RETURN FALSE;
1595 END IF;
1596 CLOSE c_person_group_id;
1597 END IF;
1598 --To check for hold plan name validity
1599 IF p_hold_plan_name IS NOT NULL THEN
1600 OPEN c_hold_plan_name;
1601 FETCH c_hold_plan_name INTO l_hold_plan_name_rec;
1602 IF c_hold_plan_name%NOTFOUND THEN
1603 fnd_message.set_name('IGS','IGS_FI_INVALID_HP');
1604 p_message_name := 'IGS_FI_INVALID_HP';
1605 fnd_file.put_line(fnd_file.log,fnd_message.get());
1606 fnd_file.put_line(fnd_file.log,' ');
1607 CLOSE c_hold_plan_name;
1608 RETURN FALSE;
1609 END IF;
1610 CLOSE c_hold_plan_name;
1611 END IF;
1612
1613 --IF all the validations get passed then return TRUE BACK to the main procedure
1614 p_message_name := NULL;
1615 RETURN TRUE;
1616
1617 EXCEPTION
1618
1619 WHEN OTHERS THEN
1620 p_message_name := 'IGS_GE_UNHANDLED_EXCEPTION';
1621 ROLLBACK;
1622 RAISE;
1623 END validate_param;
1624
1625
1626
1627 FUNCTION validate_holds( p_person_id IN igs_pe_person.person_id%TYPE,
1628 P_hold_plan_name IN igs_fi_hold_plan.hold_plan_name%TYPE,
1629 P_test_run IN VARCHAR2 ,
1630 P_release OUT NOCOPY PLS_INTEGER,
1631 P_message_name OUT NOCOPY fnd_new_messages.message_name%TYPE,
1632 p_release_credit_id IN igs_fi_person_holds.release_credit_id%TYPE := NULL,
1633 p_hold_plan_level IN igs_fi_hold_plan.hold_plan_level%TYPE := NULL
1634 ) RETURN BOOLEAN IS
1635 /***************************************************************
1636 Created By : bayadav
1637 Date Created By : 29-Nov-2001
1638 Purpose : To find out the hold types to be relaesed for the passed person id
1639 Known Limitations,Enhancements or Remarks:
1640 Change History :
1641 Who When What
1642 sapanigr 10-Oct-2005 Bug 3049184. New message in log file added for case when Holds have not been released as balance amount is greater than the threshold amount
1643 shtatiko 17-OCT-2003 Bug# 3192641, Added code to consider charges that are waived when calculating Holds Balance.
1644 smadathi 28-Aug-2003 Enh Bug 3045007.
1645 pathipat 12-Aug-2003 Enh 3076768 - Auto Release of Holds. Modification as per TD
1646 Added two IN params - p_release_credit_id and p_hold_plan_level
1647 Modified cursor c_hold_type - added join with igs_fi_hold_plan, removed trunc for start and end dates
1648 Removed cursor c_hold_plan, modified TBH calls to igs_fi_person_holds
1649 vvutukur 05-Mar-2003 Enh#2824994.Modified code such that log file output is not shown in horizontal tabular format, instead it
1650 it is shown in linear/logging format.Also used global variables to log the details instead of calling generic function
1651 everytime to get the lookup meaning.
1652 pathipat 26-Feb-2003 Enh:2747341 - Additional Security for Holds
1653 Replaced call to igs_pe_pers_encumb_pkg.update_row with call to igs_pe_gen_001.release_hold
1654 ssawhney 17-feb-2003 Bug : 2758856 : Added the parameter x_external_reference in the call to IGS_PE_PERS_ENCUMB_PKG.UPDATE_ROW
1655
1656 smadathi 20-dec-2002 Enh. Bug 2566615. Removed the references of obsoleted table IGS_FI_HOLD_PLN_LNS and
1657 incorporated changes suggested as per FICR102 TD
1658 agairola 03-Dec-2002 Bug No: 2584741 As part of the Deposits Build, modified the cursor c_credit_amount to exclude
1659 credits of Credit Class Enrolment Deposit and Other Deposit
1660 pathipat 04-OCT-2002 Enh Bug: 2562745 -- Removed selection of balance_amount from cursor c_hold_type
1661 Added cursor c_bal_amount to obtain holds balance amount from igs_fi_balances
1662 Changed type of p_release and l_release_ind to PLS_INTEGER instead of NUMBER
1663 pkpatel 04-OCT-2002 Bug No: 2600842
1664 Added the parameter auth_resp_id in the call to TBH igs_pe_pers_encumb_pkg
1665 sarakshi 23-sep-2002 Enh#2564643,removed the reference of subaccount from this function as mentioned in the TD
1666 sykrishn 07-APR-2002 Introduced planned credits functionality as per SFCR018 DLD
1667 2293676 - Planned Credits are also considered for release of holds along with
1668 the actual credits . (suba account level holds plan)
1669 ***************************************************************/
1670
1671 CURSOR c_hold_type IS
1672 SELECT a.person_id person_id,
1673 a.encumbrance_type encumbrance_type,
1674 a.start_dt start_dt,
1675 c.rowid row_id,
1676 a.comments comments,
1677 a.expiry_dt expiry_dt,
1678 a.authorising_person_id authorising_person_id,
1679 a.spo_course_cd spo_course_cd,
1680 a.spo_sequence_number spo_sequence_number,
1681 a.cal_type cal_type,
1682 a.sequence_number sequence_number ,
1683 c.hold_plan_name hold_plan_name,
1684 c.process_start_dt process_start_dt ,
1685 c.fee_type_invoice_amount fee_type_invoice_amount,
1686 c.fee_ci_sequence_number fee_ci_sequence_number,
1687 c.fee_cal_type fee_cal_type,
1688 c.hold_type hold_type,
1689 a.auth_resp_id auth_resp_id,
1690 a.external_reference external_reference,
1691 c.hold_start_dt hold_start_dt,
1692 c.process_end_dt process_end_dt,
1693 c.offset_days offset_days,
1694 c.past_due_amount past_due_amount,
1695 hplan.hold_plan_level hold_plan_level,
1696 hplan.threshold_amount threshold_amount,
1697 hplan.threshold_percent threshold_percent,
1698 hplan.payment_plan_threshold_amt payment_plan_threshold_amt ,
1699 hplan.payment_plan_threshold_pcent payment_plan_threshold_pcent,
1700 c.student_plan_id student_plan_id,
1701 c.last_instlmnt_due_date last_instlmnt_due_date
1702 FROM igs_pe_pers_encumb a,
1703 igs_fi_person_holds c,
1704 igs_fi_hold_plan hplan
1705 WHERE (a.person_id = p_person_id OR p_person_id IS NULL)
1706 AND a.start_dt <= TRUNC(SYSDATE)
1707 AND (a.expiry_dt IS NULL OR TRUNC(SYSDATE) < a.expiry_dt )
1708 AND c.hold_plan_name = hplan.hold_plan_name
1709 AND c.person_id = a.person_id
1710 AND c.hold_start_dt = a.start_dt
1711 AND c.hold_type = a.encumbrance_type
1712 AND (c.hold_plan_name = p_hold_plan_name OR p_hold_plan_name is null)
1713 AND (hplan.hold_plan_level = p_hold_plan_level OR p_hold_plan_level IS NULL);
1714
1715 CURSOR c_credit_amount(l_process_start_date igs_fi_person_holds.process_start_dt%TYPE ) IS
1716 SELECT crd.credit_id,
1717 crd.amount,
1718 crd.effective_date
1719 FROM igs_fi_credits crd,
1720 igs_fi_cr_types crt
1721 WHERE TRUNC(crd.effective_date) between
1722 (TRUNC(l_process_start_date) + 1 )and TRUNC(SYSDATE)
1723 AND crd.status = 'CLEARED'
1724 AND crd.party_id = p_person_id
1725 AND crd.credit_type_id = crt.credit_type_id
1726 AND crt.credit_class NOT IN ('ENRDEPOSIT','OTHDEPOSIT')
1727 ORDER BY crd.credit_id;
1728
1729 CURSOR c_igs_fi_hold_plan(cp_c_hold_plan_name igs_fi_hold_plan.hold_plan_name%TYPE) IS
1730 SELECT fee_type
1731 FROM igs_fi_hold_plan fhpl
1732 WHERE fhpl.hold_plan_name = cp_c_hold_plan_name;
1733
1734 l_c_fee_type igs_fi_fee_type_all.fee_type%TYPE;
1735
1736 CURSOR c_total_balance(l_fee_cal_type igs_fi_person_holds.fee_cal_type%TYPE,
1737 l_fee_ci_sequence_number igs_fi_person_holds.fee_ci_sequence_number%TYPE,
1738 l_process_start_dt igs_fi_person_holds.process_start_dt%TYPE,
1739 cp_c_fee_type igs_fi_fee_type_all.fee_type%TYPE
1740 ) IS
1741 SELECT invoice_id,
1742 invoice_amount_due,
1743 invoice_creation_date,
1744 fee_type
1745 FROM igs_fi_inv_int inv
1746 WHERE fee_type = cp_c_fee_type
1747 AND fee_cal_type = l_fee_cal_type
1748 AND fee_ci_sequence_number = l_fee_ci_sequence_number
1749 AND TRUNC(invoice_creation_date) <= TRUNC(l_process_start_dt)
1750 AND person_id = p_person_id
1751 AND NVL(invoice_amount_due,0) > 0
1752 AND NOT EXISTS ( SELECT 'X'
1753 FROM igs_fi_inv_wav_det
1754 WHERE invoice_id = inv.invoice_id
1755 AND balance_type = 'HOLDS'
1756 AND ( (TRUNC(end_dt) IS NOT NULL AND TRUNC(SYSDATE) BETWEEN TRUNC(start_dt) AND TRUNC(end_dt))
1757 OR
1758 (TRUNC(SYSDATE) >= TRUNC(start_dt) AND TRUNC(end_dt) IS NULL)
1759 )
1760 );
1761
1762 -- Get the balance amount from igs_fi_balances instead of from igs_fi_person_holds
1763 -- where the column balance_amount is getting obsoleted.
1764 CURSOR c_bal_amount (cp_person_id igs_pe_pers_encumb.person_id%TYPE,
1765 cp_process_start_dt igs_fi_person_holds.process_start_dt%TYPE) IS
1766 SELECT holds_balance
1767 FROM igs_fi_balances
1768 WHERE party_id = cp_person_id
1769 AND holds_balance IS NOT NULL
1770 AND TRUNC(balance_date) <= TRUNC(cp_process_start_dt)
1771 ORDER BY balance_date DESC;
1772
1773 CURSOR c_igs_fi_pp_instlmnts ( cp_n_student_plan_id igs_fi_pp_std_attrs.student_plan_id%TYPE,
1774 cp_d_inst_due_date igs_fi_pp_instlmnts.due_date%TYPE
1775 ) IS
1776 SELECT SUM(installment_amt) installment_amt,
1777 SUM(due_amt) due_amt
1778 FROM igs_fi_pp_instlmnts
1779 WHERE student_plan_id = cp_n_student_plan_id
1780 AND due_date <= cp_d_inst_due_date;
1781
1782 rec_c_igs_fi_pp_instlmnts c_igs_fi_pp_instlmnts%ROWTYPE;
1783
1784 -- Cursor to get the total amount of waived charges
1785 -- Added as part of Bug# 3192641
1786 CURSOR c_waive_amount( cp_n_person_id NUMBER, cp_d_process_start_date DATE) IS
1787 SELECT NVL(SUM(chg.invoice_amount), 0)
1788 FROM igs_fi_inv_int_all chg,
1789 igs_fi_inv_wav_det wav
1790 WHERE person_id = cp_n_person_id
1791 AND chg.invoice_id = wav.invoice_id
1792 AND wav.balance_type = 'HOLDS'
1793 AND cp_d_process_start_date BETWEEN TRUNC(start_dt) AND NVL(TRUNC(end_dt), cp_d_process_start_date);
1794 l_n_waive_amount NUMBER;
1795
1796
1797 l_hold_type_rec c_hold_type%ROWTYPE;
1798 l_credit_amount_rec c_credit_amount%ROWTYPE;
1799 l_total_balance_rec c_total_balance%ROWTYPE;
1800 l_hold_plan_name igs_fi_hold_plan.hold_plan_name%TYPE;
1801 l_message_name fnd_new_messages.message_name%TYPE := NULL;
1802 l_tot_amnt_all_subact igs_fi_credits.amount%TYPE := 0;
1803 l_non_ex_amnt_each_sc igs_fi_credits.amount%TYPE := 0;
1804 l_total_charges igs_fi_balances.holds_balance%TYPE := 0.0;
1805 l_final_balance_amnt igs_fi_balances.holds_balance%TYPE := 0.0;
1806 l_release_hold VARCHAR2(2) :='Y';
1807 l_msg_str_0 VARCHAR2(1000) :=NULL;
1808 l_msg_str_1 VARCHAR2(1000) :=NULL;
1809 l_release_ind PLS_INTEGER := 0;
1810 l_srl_no NUMBER := 0;
1811 l_tot_hold_type_rec NUMBER := 0;
1812 l_count PLS_INTEGER := 0;
1813
1814 l_hold_bal_amt igs_fi_balances.holds_balance%TYPE := 0.0;
1815
1816 -- Changes due to SFCR018
1817 l_v_pln_cr_ind igs_fi_control_all.planned_credits_ind%TYPE := get_planned_credits_ind;
1818 l_v_pln_cr_message fnd_new_messages.message_name%TYPE :=NULL;
1819 l_n_planned_credit igs_fi_credits.amount%TYPE;
1820 -- Changes due to SFCR018
1821
1822 l_c_message_name fnd_new_messages.message_name%TYPE := NULL;
1823 l_n_threshold_amount igs_fi_hold_plan.threshold_amount%TYPE ;
1824 l_n_threshold_percent igs_fi_hold_plan.threshold_percent%TYPE ;
1825 --Boolean variable added for Bug 3049184
1826 l_b_ret_amt_grt_msg_flag BOOLEAN ;
1827 BEGIN
1828 l_b_ret_amt_grt_msg_flag := FALSE;
1829 p_message_name := NULL;
1830 p_release := 0;
1831
1832 --to check get the active (open ended) hold types which are of hold category 'ADMIN'
1833 OPEN c_hold_type;
1834 LOOP
1835 FETCH c_hold_type INTO l_hold_type_rec;
1836 --1
1837 IF c_hold_type%NOTFOUND AND c_hold_type%ROWCOUNT = 0 THEN
1838 IF p_test_run = 'Y' THEN
1839 fnd_message.set_name('IGS','IGS_FI_NO_ACT_HOLDS');
1840 fnd_file.put_line(fnd_file.log,fnd_message.get());
1841 END IF;
1842 p_message_name := 'IGS_FI_NO_ACT_HOLDS';
1843 CLOSE c_hold_type;
1844 RETURN FALSE;
1845 ELSE
1846 --1
1847 BEGIN
1848 EXIT WHEN c_hold_type%NOTFOUND;
1849 --to find out if the particular hold type was applied by the student finance holds apply process.
1850 --2
1851 IF p_hold_plan_name IS NULL THEN
1852 --to set the hold_name parameter value if it is passed as NULL
1853 l_hold_plan_name := l_hold_type_rec.hold_plan_name;
1854 --2
1855 ELSE
1856 l_hold_plan_name := p_hold_plan_name;
1857 END IF;
1858 --Get the corresponding threshold amount, threshold percent, level for the particular hold plan
1859 --3
1860 -- Assign the default threshold_amount and threshold_percent to variables
1861 l_n_threshold_amount := l_hold_type_rec.threshold_amount;
1862 l_n_threshold_percent := l_hold_type_rec.threshold_percent;
1863
1864 IF l_hold_type_rec.hold_plan_level = 'S' THEN
1865 --Processing logic if hold_level = 'S'
1866 -- balance_amount is obtained from igs_fi_balances, not person_holds
1867 IF l_hold_type_rec.student_plan_id IS NOT NULL THEN
1868
1869 -- if payment plan threshold amount is provided, assign the
1870 -- the c_hold_type select value to the variable
1871 IF l_hold_type_rec.payment_plan_threshold_amt IS NOT NULL THEN
1872 l_n_threshold_amount := l_hold_type_rec.payment_plan_threshold_amt;
1873 l_n_threshold_percent := NULL;
1874 END IF;
1875 -- if payment plan threshold percent is provided, assign the
1876 -- the c_hold_type select value to the variable
1877 IF l_hold_type_rec.payment_plan_threshold_pcent IS NOT NULL THEN
1878 l_n_threshold_percent := l_hold_type_rec.payment_plan_threshold_pcent;
1879 l_n_threshold_amount := NULL;
1880 END IF;
1881 OPEN c_igs_fi_pp_instlmnts(l_hold_type_rec.student_plan_id,
1882 l_hold_type_rec.last_instlmnt_due_date
1883 );
1884 FETCH c_igs_fi_pp_instlmnts INTO rec_c_igs_fi_pp_instlmnts;
1885 CLOSE c_igs_fi_pp_instlmnts;
1886 l_total_charges := rec_c_igs_fi_pp_instlmnts.installment_amt;
1887 l_final_balance_amnt := rec_c_igs_fi_pp_instlmnts.due_amt;
1888 ELSE
1889
1890 OPEN c_bal_amount(l_hold_type_rec.person_id, l_hold_type_rec.process_start_dt);
1891 FETCH c_bal_amount INTO l_hold_bal_amt;
1892 l_total_charges := NVL(l_hold_bal_amt,0.0);
1893 CLOSE c_bal_amount;
1894 -- Get the total waived amount.
1895 OPEN c_waive_amount( p_person_id, TRUNC(l_hold_type_rec.process_start_dt));
1896 FETCH c_waive_amount INTO l_n_waive_amount;
1897 CLOSE c_waive_amount;
1898
1899 l_total_charges := l_total_charges - l_n_waive_amount;
1900
1901 --loop across the table IGS_FI_CREDITS to get the amount for the person_id with the effective_date between the date range
1902 OPEN c_credit_amount(l_hold_type_rec.process_start_dt);
1903 LOOP
1904 FETCH c_credit_amount INTO l_credit_amount_rec;
1905 EXIT WHEN c_credit_amount%NOTFOUND;
1906
1907 -- Each of the above credit record is checked for exclusion rules by calling the common function for exclusion
1908 -- Use a local variable to sum up the non-excluded credits of the person
1909 --5
1910 l_message_name := NULL;
1911 IF NOT igs_fi_prc_balances.check_exclusion_rules (p_balance_type => 'HOLDS',
1912 P_source_type => 'CREDIT',
1913 P_balance_date => TRUNC(l_credit_amount_rec.effective_date),
1914 P_source_id => l_credit_amount_rec.credit_id,
1915 p_balance_rule_id => l_balance_rule_id,
1916 p_message_name => l_message_name) THEN
1917 IF l_message_name IS NULL THEN
1918 --A local variable to sum up the non-excluded credits of the person for the credit id.
1919 l_non_ex_amnt_each_sc:= l_non_ex_amnt_each_sc + NVL(l_credit_amount_rec.amount,0);
1920 ELSIF l_message_name IS NOT NULL THEN
1921 --Log the eror message in the log when message is returned with FALSE
1922 IF p_test_run = 'Y' THEN
1923 fnd_message.set_name('IGS',l_message_name);
1924 fnd_file.put_line(fnd_file.log,fnd_message.get());
1925 fnd_file.put_line(fnd_file.log,' ');
1926 END IF;
1927 END IF;
1928 END IF;
1929 --5
1930 END LOOP;
1931 CLOSE c_credit_amount;
1932 --4
1933 --another local variable at the end of this loop across the IGS_FI_CREDITS to sum up the total non-excluded credits defined for the hold plan.
1934 l_tot_amnt_all_subact := l_tot_amnt_all_subact + l_non_ex_amnt_each_sc;
1935
1936 -- Changes due to SFCR018 - To include planned credits also when the indicator is set as 'Y'
1937 IF l_v_pln_cr_ind = 'Y' THEN
1938 --Call the generic function to get the total planned credits for the params passed.
1939 l_n_planned_credit := igs_fi_gen_001.finp_get_total_planned_credits(
1940 p_person_id => p_person_id,
1941 p_start_date => NULL,
1942 p_end_date => SYSDATE,
1943 p_message_name => l_v_pln_cr_message);
1944 IF l_v_pln_cr_message IS NOT NULL THEN
1945 -- Log messages in the log file only if invoked from concurrent process
1946 -- Messages should not be logged if called for Automatic release of holds
1947 IF p_release_credit_id IS NULL THEN
1948 fnd_message.set_name('IGS',l_v_pln_cr_message);
1949 fnd_file.put_line(fnd_file.log,fnd_message.get());
1950 fnd_file.put_line(fnd_file.log,' ');
1951 END IF;
1952 RAISE skip;
1953 END IF;
1954 -- When no errors sum up the planned credits also with the actual credits
1955 l_tot_amnt_all_subact := l_tot_amnt_all_subact + NVL(l_n_planned_credit,0);
1956 END IF;
1957 -- Changes due to SFCR018 - To include planned credits also when the indicator is set as 'Y'
1958
1959
1960 --to calculate the final balance that will be used for validating whether the particular hold type placed on the person can be released or not
1961 l_final_balance_amnt := l_total_charges - l_tot_amnt_all_subact ;
1962 --3
1963 END IF;
1964 ELSIF l_hold_type_rec.hold_plan_level = 'F' THEN
1965
1966 OPEN c_igs_fi_hold_plan(l_hold_plan_name);
1967 FETCH c_igs_fi_hold_plan INTO l_c_fee_type;
1968 CLOSE c_igs_fi_hold_plan;
1969 --fetched fee_type_invoice_amount that stores the value of charges as on the process_start_date when the Holds application process ran and placed a hold on the student.
1970 l_total_charges := l_hold_type_rec.fee_type_invoice_amount ;
1971 --get the outstanding balance for the particular student for the fee types defined in the hold plan in the fee period from the intermediate table IGS_FI_PERSON_HOLDS
1972 OPEN c_total_balance(l_hold_type_rec.fee_cal_type ,
1973 l_hold_type_rec.fee_ci_sequence_number,
1974 l_hold_type_rec.process_start_dt,
1975 l_c_fee_type
1976 );
1977 LOOP
1978 FETCH c_total_balance INTO l_total_balance_rec;
1979 EXIT when c_total_balance%NOTFOUND ;
1980 --6
1981 l_message_name := NULL;
1982 IF NOT igs_fi_prc_balances.check_exclusion_rules ( p_balance_type => 'HOLDS',
1983 P_source_type => 'CHARGE',
1984 P_balance_date => TRUNC(l_total_balance_rec.invoice_creation_date),
1985 P_source_id => l_total_balance_rec.invoice_id ,
1986 p_balance_rule_id => l_balance_rule_id,
1987 P_message_name => l_message_name) THEN
1988
1989 IF l_message_name IS NULL THEN
1990 --A local variable to sum up the non-excluded charges of the person for the invoiceid.
1991 l_final_balance_amnt := NVL(l_final_balance_amnt,0) + NVL(l_total_balance_rec.invoice_amount_due,0);
1992 ELSIF l_message_name IS NOT NULL THEN
1993 --Log the eror message in the log when message is returned with FALSE
1994 IF p_test_run = 'Y' THEN
1995 fnd_message.set_name('IGS',l_message_name);
1996 fnd_file.put_line(fnd_file.log,fnd_message.get());
1997 fnd_file.put_line(fnd_file.log,' ');
1998 END IF;
1999 END IF;
2000
2001 END IF;
2002 --6
2003 END LOOP;
2004 CLOSE c_total_balance;
2005 --3
2006 END IF;
2007 -- logic to see if the particular hold type can be released for the person.
2008 --When threshold_amount is specified
2009 IF l_n_threshold_amount IS NOT NULL THEN
2010 --7
2011 IF l_final_balance_amnt <= l_n_threshold_amount THEN
2012 -- If the threshold amount is less THEN equal to the threshold_percent then mark the particular hold_type as releasable for that person.
2013 l_release_hold := 'Y';
2014 ELSE
2015 l_release_hold := 'N';
2016 --Code added for Bug 3049184
2017 l_b_ret_amt_grt_msg_flag := TRUE;
2018 END IF;
2019 --7
2020 ELSIF l_n_threshold_percent IS NOT NULL THEN
2021 --When threshold percent is specified
2022 --8
2023 IF ((l_final_balance_amnt /l_total_charges)*100) <= l_n_threshold_percent THEN
2024 -- If this ratio R is less THEN equal to the threshold_percent then mark the particular hold_type as releasable for that person.
2025 l_release_hold := 'Y';
2026 ELSE
2027 l_release_hold := 'N';
2028 --Code added for Bug 3049184
2029 l_b_ret_amt_grt_msg_flag := TRUE;
2030 END IF;
2031 --8
2032 END IF;
2033 --9
2034 IF l_release_hold = 'Y' AND p_test_run = 'Y' THEN
2035 l_release_ind := NVL(l_release_ind,0) + 1;
2036 -- Assigning of l_release_ind to out parameter p_release
2037 -- was added as part of FICR102 Build.
2038 p_release := l_release_ind ;
2039 IF l_release_ind = 1 THEN
2040 fnd_message.set_name('IGS','IGS_FI_HOLDS_RLS');
2041 fnd_file.put_line(fnd_file.log,fnd_message.get());
2042 fnd_file.put_line(fnd_file.log,' ');
2043 END IF;
2044 OPEN c_person(p_person_id);
2045 FETCH c_person INTO l_person_rec;
2046 --To print indvidual person logs repeated for each record
2047 fnd_file.put_line(fnd_file.log,g_v_person_number ||' : '||l_person_rec.person_number);
2048 fnd_file.put_line(fnd_file.log,g_v_person_name ||' : '||l_person_rec.full_name);
2049 fnd_file.put_line(fnd_file.log,g_v_hold_plan ||' : '||l_hold_type_rec.hold_plan_name);
2050 fnd_file.put_line(fnd_file.log,g_n_holds_bal ||' : '||TO_CHAR(l_total_charges));
2051 fnd_file.put_line(fnd_file.log,g_n_overdue_bal ||' : '||TO_CHAR(l_final_balance_amnt));
2052 fnd_file.put_line(fnd_file.log,g_v_hold_type ||' : '||l_hold_type_rec.hold_type);
2053 fnd_file.put_line(fnd_file.log,' ');
2054 CLOSE c_person;
2055
2056 ELSIF l_release_hold = 'Y' AND p_test_run = 'N' THEN
2057
2058 --Validate expiry date is not less THEN current date
2059 --10
2060 l_message_name := NULL ;
2061 IF NOT igs_en_val_pce.enrp_val_encmb_dt (TRUNC(SYSDATE),l_message_name) THEN
2062 --If this function returns false then log error,skip this record and move to next hold type
2063 -- Log messages in the log file only if invoked from concurrent process
2064 -- Messages should not be logged if called for Automatic release of holds
2065 IF p_release_credit_id IS NULL THEN
2066 fnd_message.set_name('IGS','IGS_EN_DT_NOT_LT_CURR_DT');
2067 fnd_file.put_line(fnd_file.log,fnd_message.get());
2068 fnd_file.put_line(fnd_file.log,' ');
2069 END IF;
2070
2071 --To set the message for the form
2072 p_message_name := 'IGS_FI_HOLD_NOT_RELSD';
2073 RAISE skip;
2074 END IF;
2075 --10
2076 --Validate expiry date is greater THEN start date.
2077 --11
2078 l_message_name := NULL ;
2079 IF NOT IGS_EN_VAL_PCE.enrp_val_strt_exp_dt (TRUNC(l_hold_type_rec.start_dt),TRUNC(SYSDATE),l_message_name) THEN
2080 --If this function returns false then log error,skip this record and move to next hold type
2081 -- Log messages in the log file only if invoked from concurrent process
2082 -- Messages should not be logged if called for Automatic release of holds
2083 IF p_release_credit_id IS NULL THEN
2084 fnd_message.set_name('IGS','IGS_EN_EXPDT_GE_STDT');
2085 fnd_file.put_line(fnd_file.log,fnd_message.get());
2086 fnd_file.put_line(fnd_file.log,' ');
2087 END IF;
2088
2089 --To set the message for the form
2090 p_message_name := 'IGS_FI_HOLD_NOT_RELSD';
2091 RAISE skip;
2092 END IF;
2093 --11
2094
2095 -- Call procedure to validate if person logged in as user has authority to release the hold
2096 -- Part of Auto Release of Holds build - Passed null to resp_id and fnd_user_id, 'X' to override_resp
2097 -- Validation for Staff should not happen for releasing holds
2098 BEGIN
2099 igs_pe_gen_001.release_hold( p_resp_id => NULL,
2100 p_fnd_user_id => NULL,
2101 p_person_id => l_hold_type_rec.person_id,
2102 p_encumbrance_type => l_hold_type_rec.encumbrance_type,
2103 p_start_dt => l_hold_type_rec.start_dt,
2104 p_expiry_dt => TRUNC(SYSDATE),
2105 p_override_resp => 'X',
2106 p_message_name => l_c_message_name
2107 );
2108 -- The above procedure does not return any message_name but the OUT parameter is
2109 -- still specified. So the following piece of code is present in case the out parameter
2110 -- is used in future, does not hold any purpose as of now.
2111 IF l_c_message_name IS NOT NULL THEN
2112 -- Log messages in the log file only if invoked from concurrent process
2113 -- Messages should not be logged if called for Automatic release of holds
2114 IF p_release_credit_id IS NULL THEN
2115 fnd_message.set_name('IGS',l_c_message_name);
2116 fnd_file.put_line(fnd_file.log,fnd_message.get());
2117 fnd_file.put_line(fnd_file.log,' ');
2118 END IF;
2119 app_exception.raise_exception;
2120 END IF;
2121
2122 -- Update the value of Release Credit Id in the Holds table if releasing of hold was successful
2123 -- Added as part of Automatic Release of Holds build
2124 -- The Credit Id which caused the hold to be released will be the release_credit_id
2125 igs_fi_person_holds_pkg.update_row ( x_rowid => l_hold_type_rec.row_id,
2126 x_person_id => l_hold_type_rec.person_id,
2127 x_hold_plan_name => l_hold_type_rec.hold_plan_name,
2128 x_hold_type => l_hold_type_rec.hold_type,
2129 x_hold_start_dt => l_hold_type_rec.hold_start_dt,
2130 x_process_start_dt => l_hold_type_rec.process_start_dt,
2131 x_process_end_dt => l_hold_type_rec.process_end_dt,
2132 x_offset_days => l_hold_type_rec.offset_days,
2133 x_past_due_amount => l_hold_type_rec.past_due_amount,
2134 x_fee_cal_type => l_hold_type_rec.fee_cal_type,
2135 x_fee_ci_sequence_number => l_hold_type_rec.fee_ci_sequence_number,
2136 x_fee_type_invoice_amount => l_hold_type_rec.fee_type_invoice_amount,
2137 x_mode => 'R',
2138 x_release_credit_id => p_release_credit_id ,
2139 x_student_plan_id => l_hold_type_rec.student_plan_id,
2140 x_last_instlmnt_due_date => l_hold_type_rec.last_instlmnt_due_date
2141 );
2142
2143
2144 EXCEPTION
2145 -- The procedure called above always raises an exception when some validation fails.
2146 -- We continue with the next hold for the person in case an exception occurs.
2147 WHEN OTHERS THEN
2148 -- Even if one hold is skipped, the process should end with a Warning status
2149 -- g_b_hold_skipped is made TRUE if a hold is skipped, and process continues for next hold
2150 g_b_hold_skipped := TRUE;
2151
2152 -- Log messages in the log file only if invoked from concurrent process
2153 -- Messages should not be logged if called for Automatic release of holds
2154 IF p_release_credit_id IS NULL THEN
2155 fnd_file.put_line(fnd_file.log,fnd_message.get());
2156 fnd_file.put_line(fnd_file.log,' ');
2157 END IF;
2158 RAISE skip;
2159 END;
2160
2161 l_release_ind := l_release_ind + 1;
2162 p_release := l_release_ind ;
2163 IF l_release_ind = 1 THEN
2164 -- Log messages in the log file only if invoked from concurrent process
2165 -- Messages should not be logged if called for Automatic release of holds
2166 IF p_release_credit_id IS NULL THEN
2167 fnd_message.set_name('IGS','IGS_FI_HLDS_HV_RLS');
2168 fnd_file.put_line(fnd_file.log,fnd_message.get());
2169 fnd_file.put_line(fnd_file.log,' ');
2170 END IF;
2171 END IF;
2172 OPEN c_person(p_person_id);
2173 FETCH c_person INTO l_person_rec;
2174
2175 -- Log messages in the log file only if invoked from concurrent process
2176 -- Messages should not be logged if called for Automatic release of holds
2177 IF p_release_credit_id IS NULL THEN
2178 --To print indvidual person logs repeated for each record
2179 fnd_file.put_line(fnd_file.log,g_v_person_number ||' : '||l_person_rec.person_number);
2180 fnd_file.put_line(fnd_file.log,g_v_person_name ||' : '||l_person_rec.full_name);
2181 fnd_file.put_line(fnd_file.log,g_v_hold_plan ||' : '||l_hold_type_rec.hold_plan_name);
2182 fnd_file.put_line(fnd_file.log,g_n_holds_bal ||' : '||TO_CHAR(l_total_charges));
2183 fnd_file.put_line(fnd_file.log,g_n_overdue_bal ||' : '||TO_CHAR(l_final_balance_amnt));
2184 fnd_file.put_line(fnd_file.log,g_v_hold_type ||' : '||l_hold_type_rec.hold_type);
2185 fnd_file.put_line(fnd_file.log,' ');
2186 END IF;
2187 CLOSE c_person;
2188
2189 END IF;
2190 --9
2191 EXCEPTION
2192 WHEN skip THEN
2193 Null;
2194 WHEN OTHERS THEN
2195 ROLLBACK;
2196 p_message_name := 'IGS_GE_UNHANDLED_EXCEPTION';
2197 RAISE;
2198 END;
2199 END IF;
2200 --1
2201 END LOOP;
2202 CLOSE c_hold_type;
2203
2204 --Code added for Bug 3049184
2205 IF l_b_ret_amt_grt_msg_flag=TRUE AND p_test_run='Y' THEN
2206 fnd_message.set_name('IGS','IGS_FI_BAL_AMT_GT_THRS_AMT');
2207 fnd_file.put_line(fnd_file.log,fnd_message.get());
2208 END IF;
2209
2210 RETURN TRUE;
2211 EXCEPTION
2212 WHEN OTHERS THEN
2213 p_message_name := 'IGS_GE_UNHANDLED_EXCEPTION';
2214 RAISE;
2215 END validate_holds;
2216
2217
2218 PROCEDURE finp_release_holds_main(p_person_id IN igs_pe_person.person_id%TYPE ,
2219 p_person_id_group IN igs_pe_persid_group_v.group_id%TYPE ,
2220 P_hold_plan_name IN Igs_fi_hold_plan.hold_plan_name%TYPE ,
2221 P_test_run IN VARCHAR2 ,
2222 P_message_name OUT NOCOPY fnd_new_messages.message_name%TYPE)
2223 IS
2224 /***************************************************************
2225 Created By : bayadav
2226 Date Created By : 29-Nov-2001
2227 Purpose : To will carry out the release of holds applied on a person / all persons /
2228 group of students by student finance
2229 Known Limitations,Enhancements or Remarks:
2230 Change History :
2231 Who When What
2232 sapanigr 10-Oct-2005 Bug 3049184 - Modified log file layout.
2233 pathipat 18-Aug-2003 Enh 3076768 - Automatic Release of Holds
2234 Modified cursor c_person_hold to remove DISTINCT clause
2235 pathipat 23-Jun-2003 Bug: 3018104 - Impact of changes in person id group views
2236 Modified cursor c_person_group - replaced igs_pe_prsid_grp_mem_v
2237 with igs_pe_prsid_grp_mem
2238 pathipat 05-May-2003 Enh 2831569 - Commercial Receivables Build
2239 Added check for manage_accounts - call to igs_fi_com_rec_interface.check_manage_acc()
2240 pathipat 26-Feb-2003 Enh 2747341 - Additional Security for Holds
2241 Added code to keep count of total number of records fetched apart from
2242 count of records processed (code involving l_n_total_count)
2243 SYKRISHn 03-JAN-2002 Bug 2684895 --Procedure finp_apply_holds
2244 Logging Person Group Cd instead of person group id.
2245 used igs_fi_gen_005.finp_get_prsid_grp_code
2246 pathipat 04-OCT-2002 Enh Bug: 2562745, added check that if the holds conversion process is running
2247 then the holds release process cannot happen simultaneously.
2248 ***************************************************************/
2249
2250 --As this procedure is being called from forms and as concurrent process also So
2251 --while calling this procedure from forms proper exception handeling in PLL is neccessary .For concurrent process
2252 --exception handeling has been done in wrapper procedure.
2253
2254 CURSOR c_person_group IS
2255 SELECT person_id
2256 FROM igs_pe_prsid_grp_mem
2257 WHERE (TRUNC(end_date) IS NULL OR TRUNC(end_date) >= TRUNC(SYSDATE))
2258 AND group_id = p_person_id_group;
2259
2260 CURSOR c_person_hold IS
2261 SELECT person_id, person_number
2262 FROM igs_pe_person_base_v per
2263 WHERE EXISTS ( SELECT '1'
2264 FROM igs_fi_person_holds hold
2265 WHERE hold.person_id = per.person_id);
2266
2267 l_person_group_rec c_person_group%ROWTYPE;
2268 l_person_hold_rec c_person_hold%ROWTYPE;
2269 l_person_number igs_pe_person.person_number%TYPE :=NULL;
2270 l_start_date igs_pe_pers_encumb.start_dt%TYPE;
2271 l_passed_hold_type igs_fi_person_holds.hold_type%TYPE ;
2272 l_hold_plan_name igs_fi_hold_plan.hold_plan_name%TYPE :=NULL;
2273 l_message_name fnd_new_messages.message_name%TYPE :=NULL;
2274 l_message_name_1 fnd_new_messages.message_name%TYPE :=NULL;
2275 l_release_hold VARCHAR2(10) :='Y';
2276 l_release NUMBER :=0;
2277 l_msg_str_0 VARCHAR2(1000);
2278 l_msg_str_1 VARCHAR2(1000);
2279 l_count PLS_INTEGER :=0;
2280
2281 l_process_run_ind igs_fi_control_all.conv_process_run_ind%TYPE;
2282 l_last_conv_dt igs_fi_balance_rules.last_conversion_date%TYPE;
2283 l_version_number igs_fi_balance_rules.version_number%TYPE;
2284
2285 l_n_total_count PLS_INTEGER := 0;
2286
2287 l_v_manage_acc igs_fi_control_all.manage_accounts%TYPE := NULL;
2288 l_v_message_name fnd_new_messages.message_name%TYPE := NULL;
2289 l_v_line_sepr VARCHAR2(1000) := '+-----------------------------------------------------------------------+';
2290
2291 BEGIN
2292 p_message_name := NULL;
2293 l_hold_plan_name := p_hold_plan_name;
2294 --To get the passed person id if not NULL corresponding person number
2295 IF p_person_id IS NOT NULL THEN
2296 OPEN c_person(p_person_id);
2297 FETCH c_person INTO l_person_rec;
2298 l_person_number := l_person_rec.person_number;
2299 CLOSE c_person;
2300 END IF;
2301
2302 --TO display passed parameter values in the log
2303 fnd_message.set_name('IGS','IGS_FI_RELEASE_HOLD_PARAM');
2304
2305 fnd_message.set_token('PERSON_NUMBER',l_person_number);
2306 fnd_message.set_token('PERSON_GROUP_ID',igs_fi_gen_005.finp_get_prsid_grp_code(p_person_id_group));
2307 fnd_message.set_token('HOLD_PLAN_NAME',p_hold_plan_name);
2308 fnd_message.set_token('TEST_RUN',p_test_run);
2309
2310 fnd_file.put_line(fnd_file.log,fnd_message.get());
2311 fnd_file.put_line(fnd_file.log,' ');
2312
2313 -- Obtain the value of manage_accounts in the System Options form
2314 -- If it is null or 'OTHER', then this process is not available, so error out.
2315 igs_fi_com_rec_interface.chk_manage_account( p_v_manage_acc => l_v_manage_acc,
2316 p_v_message_name => l_v_message_name
2317 );
2318 IF (l_v_manage_acc = 'OTHER') OR (l_v_manage_acc IS NULL) THEN
2319 fnd_message.set_name('IGS',l_v_message_name);
2320 fnd_file.put_line(fnd_file.log,fnd_message.get());
2321 fnd_file.new_line(fnd_file.log);
2322 p_message_name := l_v_message_name;
2323 RETURN;
2324 END IF;
2325
2326 -- if the holds conversion process is running (conv_process_run_ind of the control_all table
2327 -- will be 1 if the process is running), then error out. Else continue.
2328 igs_fi_gen_007.finp_get_conv_prc_run_ind( p_n_conv_process_run_ind => l_process_run_ind,
2329 p_v_message_name => l_message_name_1) ;
2330
2331 IF l_message_name_1 IS NOT NULL THEN
2332 fnd_message.set_name('IGS',l_message_name_1);
2333 fnd_file.put_line(fnd_file.log,fnd_message.get());
2334 fnd_file.put_line(fnd_file.log,' ');
2335 -- pass message name to calling proc
2336 p_message_name := l_message_name_1;
2337 RETURN;
2338 END IF;
2339
2340 IF l_process_run_ind = 1 THEN
2341 -- Stop further processing as the conversion process is running
2342 fnd_message.set_name('IGS','IGS_FI_REASS_BAL_PRC_RUN');
2343 fnd_file.put_line(fnd_file.log,fnd_message.get());
2344 fnd_file.put_line(fnd_file.log,' ');
2345 -- pass message name to calling proc
2346 p_message_name := 'IGS_FI_REASS_BAL_PRC_RUN';
2347 RETURN;
2348 END IF;
2349
2350 igs_fi_gen_007.finp_get_balance_rule ( p_v_balance_type => 'HOLDS',
2351 p_v_action => 'ACTIVE',
2352 p_n_balance_rule_id => l_balance_rule_id,
2353 p_d_last_conversion_date => l_last_conv_dt,
2354 p_n_version_number => l_version_number);
2355
2356 --function to validate the input parameters .In this if any of the validation fails appropriate errors are logged
2357 --proceessing does not take palce further.
2358 --To check only for TRUE condition as for FALSE the further execution has not to be done and error message has been logged earlier in validate_param
2359 --1
2360 l_message_name := NULL ;
2361 IF validate_param( p_person_id,
2362 p_person_id_group,
2363 p_hold_plan_name,
2364 l_message_name) THEN
2365 --2
2366 IF p_person_id IS NOT NULL THEN
2367 l_message_name := NULL;
2368 --Code added for Bug 3049184
2369 fnd_file.put_line(fnd_file.log,l_v_line_sepr);
2370 fnd_message.set_name('IGS','IGS_FI_END_DATE');
2371 fnd_message.set_token('END_DATE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_HOLDS','PERSON_NUMBER')||': '|| l_person_number);
2372 fnd_file.put_line(fnd_file.log,fnd_message.get);
2373
2374 --Function to check whether holds has to be released
2375 IF NOT validate_holds(p_person_id,
2376 l_hold_plan_name,
2377 P_test_run,
2378 l_release,
2379 l_message_name,
2380 NULL,
2381 NULL) THEN
2382 --TO display the message in form
2383 IF l_release = 0 AND l_message_name IS NULL THEN
2384 p_message_name := 'IGS_FI_HOLD_NOT_RELSD';
2385 ELSE
2386 p_message_name := l_message_name;
2387 END IF;
2388 fnd_message.set_name('IGS','IGS_FI_HLD_PERSON_NOT_RELSD');
2389 fnd_message.set_token('PERSON',l_person_number);
2390 fnd_file.put_line(fnd_file.log,fnd_message.get());
2391 RETURN;
2392 ELSE
2393 --This is to display the correct message back to the form
2394 IF l_release > 0 AND l_message_name IS NOT NULL THEN
2395 p_message_name := l_message_name;
2396 ELSIF l_release > 0 AND l_message_name IS NULL THEN
2397 p_message_name := 'IGS_FI_HOLD_RELSD';
2398 ELSIF l_release = 0 AND l_message_name IS NULL THEN
2399 p_message_name := 'IGS_FI_HOLD_NOT_RELSD';
2400 END IF;
2401 --To set the indicator to count the number of persons on whom hold has been released
2402 IF l_release > 0 THEN
2403 l_count := l_count +1;
2404 ELSIF l_release = 0 THEN
2405 IF p_test_run = 'Y' THEN
2406 fnd_message.set_name('IGS','IGS_FI_HLD_PERSON_NOT_RELSD');
2407 fnd_message.set_token('PERSON',l_person_number);
2408 fnd_file.put_line(fnd_file.log,fnd_message.get());
2409 END IF;
2410 END IF;
2411 --Code added for Bug 3049184
2412 fnd_file.put_line(fnd_file.log,l_v_line_sepr);
2413 END IF;
2414 --2
2415 ELSIF p_person_id_group IS NOT NULL THEN
2416 fnd_file.put_line(fnd_file.log,l_v_line_sepr);
2417 OPEN c_person_group;
2418 LOOP
2419 FETCH c_person_group INTO l_person_group_rec;
2420 -- To count the total records fetched
2421 l_n_total_count := c_person_group%ROWCOUNT;
2422 BEGIN
2423 EXIT WHEN c_person_group%NOTFOUND;
2424 --Call a private function for validating the values to apply holds for the person,
2425 --to insert data into holds table and to print the output.
2426 OPEN c_person(l_person_group_rec.person_id);
2427 FETCH c_person INTO l_person_rec;
2428 l_person_number := l_person_rec.person_number;
2429 CLOSE c_person;
2430 --Code added for Bug 3049184
2431 fnd_message.set_name('IGS','IGS_FI_END_DATE');
2432 fnd_message.set_token('END_DATE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_HOLDS','PERSON_NUMBER')||': '|| l_person_number);
2433 fnd_file.put_line(fnd_file.log,fnd_message.get);
2434
2435 IF NOT validate_holds(l_person_group_rec.person_id,
2436 l_hold_plan_name,
2437 P_test_run,
2438 l_release,
2439 l_message_name,
2440 NULL,
2441 NULL) THEN
2442 IF p_test_run = 'Y' THEN
2443 fnd_message.set_name('IGS','IGS_FI_HLD_PERSON_NOT_RELSD');
2444 fnd_message.set_token('PERSON',l_person_number);
2445 fnd_file.put_line(fnd_file.log,fnd_message.get());
2446 END IF;
2447 RAISE skip;
2448 ELSE
2449 IF l_release > 0 THEN
2450 l_count := l_count +1;
2451 ELSIF l_release = 0 THEN
2452 IF p_test_run = 'Y' THEN
2453 fnd_message.set_name('IGS','IGS_FI_HLD_PERSON_NOT_RELSD');
2454 fnd_message.set_token('PERSON',l_person_number);
2455 fnd_file.put_line(fnd_file.log,fnd_message.get());
2456 END IF;
2457 END IF;
2458 END IF; --4
2459 EXCEPTION
2460 WHEN skip THEN
2461 NULL;
2462 WHEN OTHERS THEN
2463 p_message_name := 'IGS_GE_UNHANDLED_EXCEPTION';
2464 RAISE;
2465 END;
2466 --Code added for Bug 3049184
2467 fnd_file.put_line(fnd_file.log,l_v_line_sepr);
2468 END LOOP;
2469 CLOSE c_person_group;
2470 --2
2471 ELSE
2472 --Both person_id and person_group_code are NULL
2473 fnd_file.put_line(fnd_file.log,l_v_line_sepr);
2474 OPEN c_person_hold ;
2475 LOOP
2476 FETCH c_person_hold INTO l_person_hold_rec;
2477 -- To count the total records fetched
2478 l_n_total_count := c_person_hold%ROWCOUNT;
2479 BEGIN
2480 EXIT WHEN c_person_hold%NOTFOUND;
2481 fnd_message.set_name('IGS','IGS_FI_END_DATE');
2482 fnd_message.set_token('END_DATE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_HOLDS','PERSON_NUMBER')||': '|| l_person_hold_rec.person_number);
2483 fnd_file.put_line(fnd_file.log,fnd_message.get);
2484 IF NOT validate_holds(l_person_hold_rec.person_id,
2485 l_hold_plan_name,
2486 p_test_run,
2487 l_release,
2488 l_message_name,
2489 NULL,
2490 NULL) THEN
2491 IF p_test_run = 'Y' THEN
2492 fnd_message.set_name('IGS','IGS_FI_HLD_PERSON_NOT_RELSD');
2493 fnd_message.set_token('PERSON',l_person_hold_rec.person_number);
2494 fnd_file.put_line(fnd_file.log,fnd_message.get());
2495 END IF;
2496 RAISE skip;
2497 ELSE
2498 IF l_release > 0 THEN
2499 l_count := l_count +1;
2500 ELSIF l_release = 0 THEN
2501 IF p_test_run = 'Y' THEN
2502 fnd_message.set_name('IGS','IGS_FI_HLD_PERSON_NOT_RELSD');
2503 fnd_message.set_token('PERSON',l_person_hold_rec.person_number);
2504 fnd_file.put_line(fnd_file.log,fnd_message.get());
2505 END IF;
2506 END IF;
2507 END IF; --4
2508 EXCEPTION
2509 WHEN skip THEN
2510 NULL;
2511 WHEN OTHERS THEN
2512 p_message_name := 'IGS_GE_UNHANDLED_EXCEPTION';
2513 RAISE;
2514 END;
2515 fnd_file.put_line(fnd_file.log,l_v_line_sepr);
2516 END LOOP;
2517 CLOSE c_person_hold;
2518 --2
2519 END IF;
2520 --1
2521 ELSE
2522 -- If validate_param fails, return to calling proc with error message.
2523 p_message_name := l_message_name;
2524 RETURN;
2525 END IF;
2526
2527
2528 --to display the count of persons on whom holds is released
2529 IF p_test_run = 'N' THEN
2530 --TO display the message for no active holds if due to some invalid hold plan name or person group passed,
2531 --the further execution of process does not take place for any person
2532 IF l_count =0 AND l_message_name = 'IGS_FI_NO_ACT_HOLDS' THEN
2533 fnd_message.set_name('IGS','IGS_FI_NO_ACT_HOLDS');
2534 fnd_file.put_line(fnd_file.log,fnd_message.get());
2535 END IF;
2536 fnd_file.put_line(fnd_file.log,' ');
2537
2538 -- If holds have not been released for some students, log message
2539 -- indicating that the release of holds failed for certain students.
2540 IF (l_n_total_count > l_count) OR (g_b_hold_skipped = TRUE) THEN
2541 p_message_name := 'IGS_FI_FEW_HOLDS_REL_ERR';
2542 fnd_message.set_name('IGS','IGS_FI_FEW_HOLDS_REL_ERR');
2543 -- If only one person was processed, the above message need not be
2544 -- shown in the log file.
2545 IF l_n_total_count > 1 THEN
2546 fnd_file.put_line(fnd_file.log,fnd_message.get());
2547 fnd_file.put_line(fnd_file.log,' ');
2548 END IF;
2549 END IF;
2550
2551 fnd_message.set_name('IGS','IGS_FI_TOTAL_HOLDS_RELEASE');
2552 fnd_message.set_token('COUNT',l_count);
2553 fnd_file.put_line(fnd_file.log,fnd_message.get());
2554 END IF;
2555
2556 --To commit the data based on the test run parameter value passed to it
2557 IF (p_test_run = 'N') THEN
2558 COMMIT;
2559 ELSE
2560 ROLLBACK;
2561 END IF;
2562
2563 EXCEPTION
2564 WHEN OTHERS THEN
2565 p_message_name := 'IGS_GE_UNHANDLED_EXCEPTION';
2566 RAISE;
2567 END finp_release_holds_main;
2568
2569
2570 PROCEDURE finp_release_holds( errbuf OUT NOCOPY VARCHAR2,
2571 retcode OUT NOCOPY NUMBER,
2572 p_person_id IN igs_pe_person.person_id%TYPE ,
2573 p_person_id_group IN igs_pe_persid_group_v.group_id%TYPE ,
2574 p_hold_plan_name IN Igs_fi_hold_plan.hold_plan_name%Type,
2575 p_test_run IN VARCHAR2)
2576
2577 IS
2578 /***************************************************************
2579 Created By : bayadav
2580 Date Created By : 29-Nov-2001
2581 Purpose : A wrapper procedure around the main process of release of holds from concurrent manager.
2582 Known Limitations,Enhancements or Remarks:
2583 Change History :
2584 Who When What
2585 pathipat 05-May-2003 Enh 2831569 - Commercial Receivables Build
2586 Added check for messages IGS_FI_MANAGE_ACC_NULL and IGS_FI_MANAGE_ACC_OTH
2587 pathipat 26-Feb-2003 Enh 2747341 - Additional Security for Holds
2588 Added code related to messages IGS_PE_HOLD_AUTH_CR, IGS_GE_NOT_STAFF_MEMBER
2589 and IGS_FI_FEW_HOLDS_REL_ERR
2590 ***************************************************************/
2591 l_message_name fnd_new_messages.message_name%TYPE :=NULL;
2592 l_error NUMBER :=0;
2593
2594 BEGIN
2595 --To set the org id
2596 igs_ge_gen_003.set_org_id(NULL);
2597 retcode := 0;
2598 l_message_name := NULL;
2599
2600
2601 --Invoke the Main Process Release of hol
2602 finp_release_holds_main (p_person_id,
2603 p_person_id_group,
2604 P_hold_plan_name,
2605 P_test_run,
2606 l_message_name
2607 );
2608 IF l_message_name in ('IGS_FI_NO_PERS_PGRP',
2609 'IGS_FI_INVALID_PERSON_ID',
2610 'IGS_GE_INVALID_VALUE',
2611 'IGS_FI_INVALID_HP',
2612 'IGS_FI_SYSTEM_OPT_SETUP',
2613 'IGS_FI_REASS_BAL_PRC_RUN',
2614 'IGS_PE_HOLD_AUTH_CR',
2615 'IGS_GE_NOT_STAFF_MEMBER',
2616 'IGS_FI_MANAGE_ACC_NULL',
2617 'IGS_FI_MANAGE_ACC_OTH') THEN
2618 ROLLBACK;
2619 retcode :=2;
2620 RETURN;
2621 END IF;
2622
2623 -- If release of holds failed for certain students, then end the process
2624 -- with a warning.
2625 IF l_message_name = 'IGS_FI_FEW_HOLDS_REL_ERR' THEN
2626 retcode := 1;
2627 RETURN;
2628 END IF;
2629
2630 EXCEPTION
2631 WHEN OTHERS THEN
2632 ROLLBACK;
2633 retcode := 2;
2634 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION')||SUBSTR(sqlerrm,1,40);
2635 igs_ge_msg_stack.add;
2636 igs_ge_msg_stack.conc_exception_hndl;
2637 END finp_release_holds;
2638
2639
2640 PROCEDURE finp_auto_release_holds ( p_person_id IN NUMBER,
2641 p_hold_plan_level IN VARCHAR2,
2642 p_release_credit_id IN NUMBER,
2643 p_run_application IN VARCHAR2,
2644 p_message_name OUT NOCOPY VARCHAR2
2645 ) IS
2646 /***************************************************************
2647 Created By : Priya Athipatla
2648 Date Created By : 12-Aug-2003
2649 Purpose : Wrapper procedure invoked to release holds
2650 automatically when a credit is created. Invoked from Credits API or
2651 from SS pages. Not invoked from concurrent process.
2652 Created as part of Automatic Release of Holds build Enh 3076768
2653 Known Limitations,Enhancements or Remarks:
2654 Change History :
2655 Who When What
2656 ***************************************************************/
2657
2658 -- Cursor to check if the student has any active Balance level
2659 -- or Fee Type level.
2660 CURSOR cur_active_holds(cp_person_id igs_fi_person_holds.person_id%TYPE,
2661 cp_hold_plan_level igs_fi_hold_plan.hold_plan_level%TYPE) IS
2662 SELECT 'X'
2663 FROM igs_pe_pers_encumb pe_encmb,
2664 igs_fi_person_holds fi_holds,
2665 igs_fi_hold_plan hplan
2666 WHERE pe_encmb.person_id = p_person_id
2667 AND pe_encmb.start_dt <= TRUNC(SYSDATE)
2668 AND (pe_encmb.expiry_dt IS NULL OR TRUNC(SYSDATE) < pe_encmb.expiry_dt)
2669 AND fi_holds.person_id = pe_encmb.person_id
2670 AND fi_holds.hold_start_dt = pe_encmb.start_dt
2671 AND fi_holds.hold_type = pe_encmb.encumbrance_type
2672 AND fi_holds.hold_plan_name = hplan.hold_plan_name
2673 AND hplan.hold_plan_level = p_hold_plan_level
2674 AND ROWNUM < 2;
2675
2676 CURSOR cur_credit_number(cp_release_credit_id igs_fi_person_holds.release_credit_id%TYPE) IS
2677 SELECT credit_number
2678 FROM igs_fi_credits_all
2679 WHERE credit_id = cp_release_credit_id;
2680
2681 l_rec_active_holds cur_active_holds%ROWTYPE;
2682 l_v_credit_number igs_fi_credits_all.credit_number%TYPE := NULL;
2683 l_d_last_conv_dt igs_fi_balance_rules.last_conversion_date%TYPE := NULL;
2684 l_n_version_number igs_fi_balance_rules.version_number%TYPE := NULL;
2685 l_n_release PLS_INTEGER := 0;
2686 l_v_message_name fnd_new_messages.message_name%TYPE := NULL;
2687
2688 BEGIN
2689
2690 OPEN cur_active_holds(p_person_id, p_hold_plan_level);
2691 FETCH cur_active_holds INTO l_rec_active_holds;
2692 IF cur_active_holds%NOTFOUND THEN
2693 CLOSE cur_active_holds;
2694 RETURN;
2695 END IF;
2696 CLOSE cur_active_holds;
2697
2698 -- Obtain the latest active balance rule
2699 igs_fi_gen_007.finp_get_balance_rule ( p_v_balance_type => 'HOLDS',
2700 p_v_action => 'ACTIVE',
2701 p_n_balance_rule_id => l_balance_rule_id, -- Package level OUT variable
2702 p_d_last_conversion_date => l_d_last_conv_dt,
2703 p_n_version_number => l_n_version_number);
2704
2705 -- Hold Plan Level = Fee Type
2706 IF (p_hold_plan_level = 'F') THEN
2707 BEGIN -- Block to call Mass Application and Release holds
2708 SAVEPOINT before_transaction;
2709
2710 IF (p_run_application = 'Y') THEN
2711 -- Invoke Mass Application process
2712 OPEN cur_credit_number(p_release_credit_id);
2713 FETCH cur_credit_number INTO l_v_credit_number;
2714 CLOSE cur_credit_number;
2715
2716 igs_fi_prc_appl.mass_apply( p_person_id => p_person_id,
2717 p_person_id_grp => NULL,
2718 p_credit_number => l_v_credit_number,
2719 p_credit_type_id => NULL,
2720 p_credit_date_low => NULL,
2721 p_credit_date_high => NULL,
2722 p_d_gl_date => TRUNC(SYSDATE)
2723 );
2724 END IF; -- End of check for run_application
2725
2726 -- Validate the holds, release if applicable
2727 -- Also updates release_credit_id column if holds were released
2728 IF NOT validate_holds( p_person_id => p_person_id,
2729 p_hold_plan_name => NULL,
2730 p_test_run => 'N',
2731 p_release => l_n_release,
2732 p_message_name => l_v_message_name,
2733 p_release_credit_id => p_release_credit_id,
2734 p_hold_plan_level => p_hold_plan_level) THEN
2735 ROLLBACK TO before_transaction;
2736 -- Set the message name to the out variable of validate_holds
2737 p_message_name := l_v_message_name;
2738 RETURN;
2739 END IF;
2740
2741 EXCEPTION
2742 WHEN OTHERS THEN
2743 ROLLBACK TO before_transaction;
2744 -- Set the message name to a generic error message
2745 p_message_name := 'IGS_FI_NO_AUTO_HOLD_REL';
2746 RETURN;
2747 END; -- End of Block to call Mass Application and release holds
2748
2749 -- Hold Plan Level = Balance
2750 ELSIF (p_hold_plan_level = 'S') THEN
2751 -- Block to validate and release holds
2752 BEGIN
2753 SAVEPOINT before_release;
2754
2755 -- Validate the holds, release if applicable
2756 -- Also updates release_credit_id column if holds were released
2757 IF NOT validate_holds( p_person_id => p_person_id,
2758 p_hold_plan_name => NULL,
2759 p_test_run => 'N',
2760 p_release => l_n_release, -- OUT parameter
2761 p_message_name => l_v_message_name, -- OUT parameter
2762 p_release_credit_id => p_release_credit_id,
2763 p_hold_plan_level => p_hold_plan_level) THEN
2764 ROLLBACK TO before_release;
2765 -- Set the message name to the out variable of validate_holds
2766 p_message_name := l_v_message_name;
2767 RETURN;
2768 END IF;
2769 EXCEPTION
2770 WHEN OTHERS THEN
2771 ROLLBACK TO before_release;
2772 -- Set the message name to a generic error message
2773 p_message_name := 'IGS_FI_NO_AUTO_HOLD_REL';
2774 RETURN;
2775 END; -- End of block to validate and release holds
2776
2777 END IF; -- End of check for hold_plan_level
2778
2779 END finp_auto_release_holds;
2780
2781 END igs_fi_prc_holds;
[Home] [Help]