1 PACKAGE BODY igs_fi_prc_appl AS
2 /* $Header: IGSFI56B.pls 120.7 2006/05/25 10:02:57 akandreg ship $ */
3
4 /******************************************************************
5 Created By : Vinay Chappidi
6 Date Created By : 25-Apr-2001
7 Purpose : This function is used when the user has not passed
8 any low date, returns the min effective date from the
9 credits table
10
11 Known limitations,
12 enhancements,
13 remarks :
14 Change History
15 Who When What
16 akandreg 17-May-2006 Bug 5134636 - Modifications for locking the records
17 sapanigr 24-Feb-2006 Bug#5018036 - Cursor cur_person changed in mass_apply procedure (R12 SQL Repository tuning)
18 sapanigr 12-Feb-2006 Bug#5018036 - Modified cursor cur_fund_auth in mass_apply procedure. (R12 SQL Repository tuning)
19 pathipat 04-Nov-2005 Bug 4634950 - Modified CUR_CREDITS in procedure mass_apply
20 sapanigr 20-Sep-2005 Enh#4228665. Modified CUR_CREDITS, CUR_HIERARCHIES and
21 code logic in mass_apply() function.
22 svuppala 07-JUL-2005 Enh 3392095 - Tution Waivers build
23 Modified CUR_CREDITS in mass_apply() function.
24 pathipat 23-Apr-2003 Enh 2831569 - Commercial Receivables build
25 Modified mass_apply() - added call to chk_manage_Account()
26 sarakshi 06-Mar-2003 Bug#2767522,added validation related to credit dates in mass_apply procedure
27 pathipat 21-Jan-2003 Bug: 2686680 - Modified mass_apply - changed error message logged
28 smadathi 9-Jan-2003 Bug 2722096. Modified procedures mass_apply and mass_application.
29 vvutukur 13-Dec-2002 Enh#2584741.Deposits Build. Modifications done in eff_max_date,eff_min_date,mass_apply.
30 smadathi 20-NOV-2002 Enh. Bug 2584986. Added new parameter GL Date to procedure mass_application
31 and mass_apply.Private function lookup_desc removed and usage replaced by
32 igs_fi_gen_gl.get_lkp_meaning function.
33 schodava 19-Sep-2002 Enh # 2564643 - Subaccount Removal
34 Modified procedures mass_application, mass_apply
35 agairola 23-May-2002 Added the procedure get_cal_details for the bug 2378182. Modified
36 the code in the procedure mass_apply
37 agairola 21-May-2002 Modified the procedure mass_apply for bug 2377976
38 vvutukur 26-apr-2002 Changed the case of fnd_file.put_line to lower and
39 used fnd_file.new_line instead of writing a null string by fnd_file.put_line.
40 bug#2326163.
41 vvutukur 22-APR-2002 Modified as part of bug#2326163 not to show ids in log file but
42 numbers and names.
43 smadathi 28-Feb-2002 Bug. 2238413. modified mass _application procedure.
44 sarakshi 27-Feb-2002 bug:2238362, changed the view igs_pe_person_v to igs_fi_parties_v
45 masehgal 17-Jan-2002 ENH # 2170429
46 Obsoletion of SPONSOR_CD from UPDATE_ROW Call to IGS_FI_INV_INT Tablehandler
47 msrinivi 13 aug,2001 bug 1882122: Take only ch_lns with err_acc ='N'
48 sykrishn 28-JAN changes to mass application - sfcr020 - 2191470
49 ******************************************************************/
50
51 --global variable to know whether Oracle Financials is Yes Or No in System Options form.
52 g_rec_installed igs_fi_control.rec_installed%TYPE;
53 e_lock_exception EXCEPTION;
54 PRAGMA EXCEPTION_INIT(e_lock_exception, -0054);
55 g_v_lock VARCHAR2(1);
56
57
58 FUNCTION eff_min_date RETURN DATE IS
59 /******************************************************************
60 Created By : Vinay Chappidi
61 Date Created By : 25-Apr-2001
62 Purpose : This function is used when the user has not passed
63 any low date, returns the min effective date from the
64 credits table
65
66 Known limitations,
67 enhancements,
68 remarks :
69 Change History
70 Who When What
71 vvutukur 13-Dec-2002 Enh#2584741.Modified cursor cur_min_date to exclude deposit transactions from Credits Table.
72 ******************************************************************/
73
74 CURSOR cur_min_date
75 IS
76 SELECT MIN(effective_date) min_date
77 FROM igs_fi_credits a,
78 igs_fi_cr_types b
79 WHERE a.credit_type_id = b.credit_type_id
80 AND b.credit_class NOT IN ('ENRDEPOSIT','OTHDEPOSIT');
81
82 l_min_date cur_min_date%ROWTYPE;
83
84 BEGIN
85 OPEN cur_min_date;
86 FETCH cur_min_date INTO l_min_date;
87 CLOSE cur_min_date;
88
89 RETURN(l_min_date.min_date);
90 END eff_min_date;
91
92
93 FUNCTION eff_max_date RETURN DATE IS
94 /******************************************************************
95 Created By : Vinay Chappidi
96 Date Created By : 25-Apr-2001
97 Purpose : This function is used when the user has not passed
98 any high date, returns the max effective date from the
99 credits table
100
101 Known limitations,
102 enhancements,
103 remarks :
104 Change History
105 Who When What
106 vvutukur 13-Dec-2002 Enh#2584741.Modified cursor cur_max_date to exclude deposit transactions
107 from Credits Table.
108 ******************************************************************/
109
110 CURSOR cur_max_date
111 IS
112 SELECT MAX(effective_date) max_date
113 FROM igs_fi_credits a,
114 igs_fi_cr_types b
115 WHERE a.credit_type_id = b.credit_type_id
116 AND b.credit_class NOT IN ('ENRDEPOSIT','OTHDEPOSIT');
117
118 l_max_date cur_max_date%ROWTYPE;
119
120 BEGIN
121 OPEN cur_max_date;
122 FETCH cur_max_date INTO l_max_date;
123 CLOSE cur_max_date;
124
125 RETURN(l_max_date.max_date);
126 END eff_max_date;
127
128 PROCEDURE apply_and_log_messages (
129 p_invoice_id IN igs_fi_inv_int_all.invoice_id%TYPE,
130 p_credit_id IN igs_fi_credits.credit_id%TYPE,
131 p_fee_type IN igs_fi_inv_int_all.fee_type%TYPE,
132 p_fee_cal_type IN igs_fi_inv_int_all.fee_cal_type%TYPE,
133 p_unapplied_amount IN OUT NOCOPY igs_fi_credits.unapplied_amount%TYPE,
134 p_orig_invoice_amount_due IN igs_fi_inv_int_all.invoice_amount_due%TYPE,
135 p_invoice_amount_due IN OUT NOCOPY igs_fi_inv_int_all.invoice_amount_due%TYPE,
136 p_amount_applied IN igs_fi_applications.amount_applied%TYPE,
137 p_appl_hierarchy_id IN igs_fi_applications.appl_hierarchy_id%TYPE,
138 P_counter IN OUT NOCOPY NUMBER ,
139 p_d_gl_date IN DATE
140 ) IS
141 /******************************************************************
142 Created By : sykrishn
143 Date Created By : 24-JAN-2001
144 Purpose : This procedure invokes the generic application process and logs appropriate messages
145 Known limitations,
146 enhancements,
147 remarks :Local Procedure
148 Change History
149 Who When What
150 akandreg 17-May-2006 Bug 5134636: Removed commit for each application record
151 smadathi 20-NOV-2002 Enh. Bug 2584986. Added new parameter GL Date to procedure. Added cursors
152 c_igs_fi_invln_int and c_igs_fi_credits to obtain the charge and credit
153 GL Dates.
154 vvutukur 26-apr-2002 fnd_file.put_line is changed to lower case and fnd_file.new_line is used
155 instead of writing null string by fnd_file.put_line.bug#2326163.
156 vvutukur 22-apr-2002 Removed the code showing log horizontally. Modified to show numbers and
157 names in the log file instead of ids. bug#2326163.
158 ****************************************************************/
159
160 --added by vvutukur for bug 2326163
161 CURSOR cur_invoice_number(cp_invoice_id igs_fi_inv_int.invoice_number%TYPE) IS
162 SELECT invoice_number
163 FROM igs_fi_inv_int
164 WHERE invoice_id = cp_invoice_id;
165
166 CURSOR c_igs_fi_invln_int(cp_n_invoice_id igs_fi_inv_int.invoice_id%TYPE) IS
167 SELECT gl_date
168 FROM igs_fi_invln_int
169 WHERE invoice_id = cp_n_invoice_id;
170
171 CURSOR c_igs_fi_credits(cp_n_credit_id igs_fi_credits.credit_id%TYPE) IS
172 SELECT gl_date
173 FROM igs_fi_credits
174 WHERE credit_id = cp_n_credit_id;
175
176 rec_c_igs_fi_invln_int c_igs_fi_invln_int%ROWTYPE;
177 rec_c_igs_fi_credits c_igs_fi_credits%ROWTYPE;
178
179 l_invoice_number igs_fi_inv_int.invoice_number%TYPE;
180 l_dr_ccid_str VARCHAR2(230);
181 l_cr_ccid_str VARCHAR2(230);
182
183 l_msg_str_2 VARCHAR2(2000);
184 l_msg_str_3 VARCHAR2(2000);
185 l_v_app_error_message fnd_new_messages.message_name%TYPE;
186 l_n_application_id igs_fi_applications.application_id%TYPE;
187 l_v_dr_account_cd igs_fi_applications.dr_account_cd%TYPE;
188 l_v_cr_account_cd igs_fi_applications.cr_account_cd%TYPE;
189 l_n_cr_gl_ccid igs_fi_applications.cr_gl_code_ccid%TYPE;
190 l_n_dr_gl_ccid igs_fi_applications.dr_gl_code_ccid%TYPE;
191 l_b_status BOOLEAN := FALSE ;
192 l_dummy_unapplied_amount igs_fi_credits.unapplied_amount%TYPE;
193 l_dummy_invoice_amount_due igs_fi_inv_int_all.invoice_amount_due%TYPE;
194
195 BEGIN
196
197 --added by vvutukur for bug#2326163
198 OPEN cur_invoice_number(p_invoice_id);
199 FETCH cur_invoice_number INTO l_invoice_number;
200 CLOSE cur_invoice_number;
201
202 /* This procedure invokes the generic application process and logs appropriate messages */
203
204 /* Call the generic applications procedure to create applications */
205 -- The call has been modified as part of enh. Bug 2584986 to pass p_validation
206 -- parameter value as 'Y'
207 igs_fi_gen_007.create_application(
208 p_application_id =>l_n_application_id, --IN OUT NOCOPY param
209 p_credit_id =>p_credit_id,
210 p_invoice_id =>p_invoice_id,
211 p_amount_apply =>p_amount_applied,
212 p_appl_type =>'APP',
213 p_appl_hierarchy_id => p_appl_hierarchy_id,
214 p_validation =>'Y',
215 p_unapp_amount => p_unapplied_amount,
216 p_inv_amt_due => p_invoice_amount_due,
217 p_dr_gl_ccid => l_n_dr_gl_ccid,
218 p_cr_gl_ccid => l_n_cr_gl_ccid,
219 p_dr_account_cd => l_v_dr_account_cd,
220 p_cr_account_cd => l_v_cr_account_cd,
221 p_err_msg => l_v_app_error_message,
222 p_status => l_b_status,
223 p_d_gl_date => TRUNC(p_d_gl_date)
224 );
225 IF l_b_status THEN
226 /*Success **/
227 -- if the generic applications procedure to create application returns the message IGS_FI_CHG_CRD_GL_DATE
228 -- log this message and proceed.
229 IF l_v_app_error_message = 'IGS_FI_CHG_CRD_GL_DATE' THEN
230 OPEN c_igs_fi_invln_int(p_invoice_id);
231 FETCH c_igs_fi_invln_int INTO rec_c_igs_fi_invln_int;
232 CLOSE c_igs_fi_invln_int;
233
234 OPEN c_igs_fi_credits(p_credit_id);
235 FETCH c_igs_fi_credits INTO rec_c_igs_fi_credits;
236 CLOSE c_igs_fi_credits;
237
238 fnd_message.set_name('IGS',l_v_app_error_message);
239 fnd_message.set_token('GL_DATE',p_d_gl_date);
240 fnd_message.set_token('CHG_GL_DATE',rec_c_igs_fi_invln_int.gl_date);
241 fnd_message.set_token('CRD_GL_DATE',rec_c_igs_fi_credits.gl_date);
242 fnd_file.put_line(fnd_file.log,fnd_message.Get);
243 END IF;
244
245 P_counter := NVL(P_counter,0) + 1;
246 /** logging Invoice table details **/
247
248 --added by vvutukur for bug# 2326163
249 --populate the debit and credit accounts into local variables.
250 IF g_rec_installed = 'Y' THEN
251 l_dr_ccid_str := igs_fi_gen_007.get_ccid_concat(l_n_dr_gl_ccid);
252 l_cr_ccid_str := igs_fi_gen_007.get_ccid_concat(l_n_cr_gl_ccid);
253 ELSE
254 l_dr_ccid_str := l_v_dr_account_cd;
255 l_cr_ccid_str := l_v_cr_account_cd;
256 END IF;
257
258 --Show log for invoice and application details.
259 fnd_file.put_line(fnd_file.log,' '||igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','INVOICE_NUMBER')||' : '||l_invoice_number);
260 fnd_file.put_line(fnd_file.log,' '||igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FEE_TYPE')||' : '||p_fee_type);
261 fnd_file.put_line(fnd_file.log,' '||igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FEE_CAL_TYPE')||' : '||p_fee_cal_type);
262 fnd_file.put_line(fnd_file.log,' '||igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','INVOICE_AMOUNT_DUE')||' : '||TO_CHAR(p_orig_invoice_amount_due));
263 fnd_file.put_line(fnd_file.log,' '||igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','INVOICE_AMOUNT_APPLIED')||' : '||NVL(TO_CHAR(p_invoice_amount_due),0));
264
265 fnd_file.put_line(fnd_file.log,' '||igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','AMOUNT_APPLIED')||' : '||NVL(TO_CHAR(p_amount_applied),0));
266 fnd_file.put_line(fnd_file.log,' '||igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','DR_ACCOUNT')||' : '||l_dr_ccid_str);
267 fnd_file.put_line(fnd_file.log,' '||igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','CR_ACCOUNT')||' : '||l_cr_ccid_str);
268 fnd_file.new_line(fnd_file.log);
269
270 ELSE
271 /*Failure*/
272 fnd_message.Set_Name('IGS',l_v_app_error_message);
273 fnd_file.put_line(fnd_file.log, fnd_message.Get);
274 fnd_file.put_line(fnd_file.log,' ');
275 END IF;
276
277 EXCEPTION
278 WHEN OTHERS THEN
279 fnd_message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
280 fnd_file.put_line(fnd_file.log, fnd_message.Get);
281 RETURN;
282 END apply_and_log_messages;
283
284 PROCEDURE get_cal_details(p_fee_cal_type igs_ca_inst.cal_type%TYPE,
285 p_fee_seq igs_ca_inst.sequence_number%TYPE,
286 p_end_dt OUT NOCOPY igs_ca_inst.end_dt%TYPE,
287 p_message OUT NOCOPY fnd_new_messages.message_name%TYPE,
288 p_status OUT NOCOPY BOOLEAN) AS
289 /******************************************************************
290 Created By : agairola
291 Date Created By : 23-May-2002
292 Purpose : This procedure fetches the End Date of the Load Calendar for the
293 Fee Calendar passed as input.
294 Known limitations,
295 enhancements,
296 remarks :Local Procedure
297 Change History
298 Who When What
299
300 ****************************************************************/
301
302 CURSOR cur_cal(cp_cal_type igs_ca_inst.cal_type%TYPE,
303 cp_seq_num igs_ca_inst.sequence_number%TYPE) IS
304 SELECT end_dt
305 FROM igs_ca_inst
306 WHERE cal_type = cp_cal_type
307 AND sequence_number = cp_seq_num;
308
309 l_load_cal_type igs_ca_inst.cal_type%TYPE;
310 l_load_seq igs_ca_inst.sequence_number%TYPE;
311 l_message_name fnd_new_messages.message_name%TYPE;
312 l_end_dt igs_ca_inst.end_dt%TYPE;
313 BEGIN
314 p_status := TRUE;
315
316 -- Call the API for getting the Load Calendar from the Fee Calendar
317 -- If the status is returned to FALSE, then make the p_status as FALSE
318 IF NOT igs_fi_gen_001.finp_get_lfci_reln(p_cal_type => p_fee_cal_type,
319 p_ci_sequence_number => p_fee_seq,
320 p_cal_category => 'FEE',
321 p_ret_cal_type => l_load_cal_type,
322 p_ret_ci_sequence_number => l_load_seq,
323 p_message_name => l_message_name) THEN
324 p_status := FALSE;
325 ELSE
326
327 -- Else get the End Date of the Load Calendar
328 OPEN cur_cal(l_load_cal_type,
329 l_load_seq);
330 FETCH cur_cal INTO l_end_dt;
331 IF cur_cal%NOTFOUND THEN
332 p_status := FALSE;
333 END IF;
334 CLOSE cur_cal;
335 END IF;
336
337 IF NOT p_status THEN
338 p_message := l_message_name;
339 p_end_dt := NULL;
340 ELSE
341 p_message := NULL;
342 p_end_dt := l_end_dt;
343 END IF;
344 END get_cal_details;
345
346 PROCEDURE mass_application ( errbuf OUT NOCOPY VARCHAR2,
347 retcode OUT NOCOPY NUMBER,
348 p_org_id NUMBER,
349 p_person_id igs_fi_inv_int_all.person_id%TYPE,
350 p_person_id_grp igs_pe_prsid_grp_mem_all.group_id%TYPE,
351 p_credit_number igs_fi_credits_all.credit_number%TYPE,
352 p_credit_type_id igs_fi_credits_all.credit_type_id%TYPE,
353 p_credit_date_low VARCHAR2,
354 p_credit_date_high VARCHAR2,
355 p_d_gl_date VARCHAR2
356 ) IS
357 /******************************************************************
358 Created By : Vinay Chappidi
359 Date Created : 25-Apr-2001
360 Purpose : This procedure will apply all the unapplied amount
361 that is found in the credits table into the invoice
362 tables for the given person id, subaccount id and fee type
363 Records will be filtered depending on the parameters
364 that are passed into the procedure.
365
366 Known limitations,
367 enhancements,
368 remarks :
369 Change History
370 Who When What
371 akandreg 17-May-2006 Bug 5134636: Added logic for setting the retcode
372 sarakshi 05-Feb-2003 bug#2767532,modifed the outermost exception
373 smadathi 9-Jan-2003 Bug 2722096. All the exceptions raised by the mass_apply procedure
374 is masked and handled these in the user defined exception.
375 smadathi 20-NOV-2002 Enh. Bug 2584986. Added new parameter GL Date to procedure mass_application
376 schodava 19-Sep-2002 Enh # 2564643 - Subaccount Removal
377 Removed all references to subaccount.
378 vvutukur 22-apr-2002 Populated rec_installed value into g_rec_installed for bug#2326163
379 smadathi 28-Feb-2002 Bug. 2238413. The call to igs_fi_gen_005.finp_get_receivables_inst and
380 logging of message is removed since this check has been moved
381 to CREATE_APPLICATION procedure.
382 vchappid 05-OCT-2001 As per Enh#2030448, Call to calculate balances process has been removed,
383 Balance_Flag column reference has been removed from updation of Credits,
384 Charges TBH's
385 Account GL codes assigned depending on the Accounting Method in case of
386 CR_GL_CODE_CCID, DR_GL_CODE_CCID
387
388 sykrishn 26-JAN-2002 Enhancements due to SFCR020 - 2191470 -
389 ******************************************************************/
390 l_d_gl_date igs_fi_applications.gl_date%TYPE ;
391 l_err_exception EXCEPTION;
392 BEGIN
393
394 retcode :=0;
395 -- Set the Org Id
396 igs_ge_gen_003.set_org_id(p_org_id);
397
398 --Populate the rec_installed value into g_rec_installed to know whether Oracle Financials is
399 --set to Yes or No in System Options Form.
400 g_rec_installed := igs_fi_gen_005.finp_get_receivables_inst;
401 l_d_gl_date := TRUNC(igs_ge_date.igsdate(p_d_gl_date)) ;
402
403 -- the exceptions raised in the mass_apply is masked in the block so that
404 -- the same can be captured and raised in the the user defined excpetion handler.
405 -- in case of any un handled exception raised in mass apply procedure, this will not be
406 -- captured in the user defined exception handler, but will be captured in the
407 -- when others handler
408 BEGIN
409 g_v_lock := 'N';
410 mass_apply(p_person_id => p_person_id,
411 p_person_id_grp => p_person_id_grp,
412 p_credit_number => p_credit_number,
413 p_credit_type_id => p_credit_type_id,
414 p_credit_date_low => p_credit_date_low,
415 p_credit_date_high => p_credit_date_high,
416 p_d_gl_date => l_d_gl_date
417 );
418 IF g_v_lock = 'Y' THEN
419 retcode := 1;
420 END IF;
421 COMMIT;
422
423
424 EXCEPTION
425 WHEN OTHERS THEN
426 IF IGS_GE_MSG_STACK.COUNT_MSG <> 0 THEN
427 fnd_file.put_line(fnd_file.log,fnd_message.get);
428 RAISE l_err_exception;
429 ELSE
430 RAISE;
431 END IF;
432 END ;
433
434 EXCEPTION
435 WHEN l_err_exception THEN
436 retcode := 2;
437 WHEN OTHERS THEN
438 retcode := 2;
439 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
440 errbuf := fnd_message.get;
441 fnd_file.put_line(fnd_file.log,sqlerrm);
442 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
443 END mass_application;
444
445 PROCEDURE mass_apply(p_person_id igs_fi_inv_int_all.person_id%TYPE,
446 p_person_id_grp igs_pe_prsid_grp_mem_all.group_id%TYPE,
447 p_credit_number igs_fi_credits_all.credit_number%TYPE,
448 p_credit_type_id igs_fi_credits_all.credit_type_id%TYPE,
449 p_credit_date_low VARCHAR2,
450 p_credit_date_high VARCHAR2,
451 p_d_gl_date DATE
452 ) IS
453 /******************************************************************
454 Created By : Vinay Chappidi
455 Date Created : 25-Apr-2001
456 Purpose :
457
458 Known limitations,
459 enhancements,
460 remarks :
461 Change History
462 Who When What
463 akandreg 17-May-2006 Bug 5134636: Added code logic for locking and trapping the exception
464 sapanigr 24-Feb-2006 Bug#5018036 - Cursor cur_person broken into three separate cursors: cur_person_group_id, cur_person_id, cur_all_person_id.
465 to resolve non mergable view.
466 sapanigr 12-Feb-2006 Bug#5018036 - Cursor cur_fund_auth now uses base tables directly instead of igs_fi_parties_v. (R12 SQL Repository tuning)
467 pathipat 04-Nov-2005 Bug 4634950 - Modified CUR_CREDITS - removed unwanted join with igs_fi_cr_types_all
468 sapanigr 20-Sep-2005 Enh#4228665. Modified CUR_CREDITS to select APPL_HIERARCHY_ID also from table IGS_FI_CR_TYPES_ALL
469 Modified CUR_HIERARCHIES.
470 Modified code logic to pass APPL_HIERARCHY_ID from CUR_CREDITS to CUR_HIERARCHIES
471 svuppala 07-JUL-2005 Enh 3392095 - Tution Waivers build
472 Modified CUR_CREDITS -- exclude credit class of 'Waiver'.
473 pathipat 23-Apr-2003 Enh 2831569 - Commercial Receivables build
474 Added validation for manage_account - call to chk_manage_account()
475 sarakshi 06-Mar-2003 Bug#2767522,added validation for checking credit date low cannot be greater than credit date high
476 pathipat 21-Jan-2003 Bug: 2686680 - When person_id and person_id grp are both specified
477 changed the error message logged from IGS_FI_PRS_OR_PRSIDGRP to IGS_FI_NO_PERS_PGRP
478 smadathi 9-Jan-2003 Bug 2722096. Removed the logging of person group id. Instead
479 used call to igs_fi_gen_005.finp_get_prsid_grp_code to
480 log person group code. Logging of the error message for invalid
481 parameters passed have been removed and same has been taken cared in mass_application procedure.
482 Moreover, call to igs_ge_msg_stack.add has been incorporated wherever explicit handling for
483 invalid parameter values have been done.
484 vvutukur 13-Dec-2002 Enh#2584741.Modified cursor cur_credits to exclude deposit transactions while selecting
485 rows from Credits Table.
486 smadathi 20-NOV-2002 Enh. Bug 2584986. Added new parameter GL Date to procedure mass_apply
487 schodava 19-Sep-2002 Enh # 2564643 - Subaccount Removal
488 Removed all references to subaccount.
489 agairola 23-May-2002 Modified the code for the bug 2378182
490 1. For the Charges being selected for the Fee Periods other than that of
491 the Fee Period of the Credit Record, the End Date of the Load Calendar
492 for the Fee Period of the Charge record should be less than or equal to
493 the End Date of the Load Calendar of the Credit Record's Fee Period.
494 agairola 21-May-2002 Modified the cursor cur_charge_not_fp to include the OR condition
495 instead of the AND condition for the Fee Period- Bug No 2377976
496 vvutukur 22-apr-2002 Modified code to show numbers and names in the log file instead of ids.
497 bug#2326163.(added cursors cur_subaccount_name,cur_credit_type).
498 ******************************************************************/
499
500 -- cursor for getting the person_id when person group id is passed
501 CURSOR cur_person_group_id(cp_person_grp igs_pe_prsid_grp_mem_all.group_id%TYPE)
502 IS
503 SELECT person_id
504 FROM igs_pe_prsid_grp_mem
505 WHERE group_id = cp_person_grp
506 ORDER BY 1;
507
508 -- cursor for getting the person_id when person id is passed
509 CURSOR cur_person_id(cp_person_id hz_parties.party_id%TYPE)
510 IS
511 SELECT party_id
512 FROM hz_parties
513 WHERE party_id = cp_person_id
514 ORDER BY 1;
515
516 -- cursor for getting the person_id when neither of person id or
517 -- person grp id is passed
518 CURSOR cur_all_person_id
519 IS
520 SELECT DISTINCT party_id
521 FROM igs_fi_credits
522 WHERE unapplied_amount>0
523 ORDER BY 1;
524
525 -- cursor for getting all the positive unapplied amount depending on the
526 -- parameters passed by the user --
527 -- Those credits that have CHGADJ as credit type with credit class
528 CURSOR cur_credits( cp_person_id igs_fi_inv_int_all.person_id%TYPE,
529 cp_credit_date_low DATE,
530 cp_credit_date_high DATE)
531 IS
532 SELECT cr.rowid, cr.*,crt.credit_type_name,crt.appl_hierarchy_id
533 FROM igs_fi_credits cr,igs_fi_cr_types crt
534 WHERE
535 (cr.party_id = cp_person_id OR (cp_person_id IS NULL)) AND
536 (cr.credit_number = p_credit_number OR (p_credit_number IS NULL)) AND
537 (cr.credit_type_id = p_credit_type_id OR (p_credit_type_id IS NULL)) AND
538 (TRUNC(cr.effective_date) BETWEEN TRUNC(cp_credit_date_low) AND TRUNC(cp_credit_date_high)) AND
539 cr.unapplied_amount > 0 AND
540 cr.credit_type_id = crt.credit_type_id AND
541 crt.credit_class NOT IN ('ENRDEPOSIT','OTHDEPOSIT','CHGADJ','WAIVER')
542 ORDER BY party_id, effective_date
543 FOR UPDATE NOWAIT;
544
545 -- cursor for getting the application hierarchy id for the given credit type id
546 --changed this cursor to sort by version number to get the latest record.
547 CURSOR cur_hierarchies(cp_appl_hierarchy_id igs_fi_a_hierarchies.credit_type_id%TYPE)
548 IS
549 SELECT appl_hierarchy_id, hierarchy_name, effective_start_date, effective_end_date
550 FROM igs_fi_a_hierarchies
551 WHERE appl_hierarchy_id = cp_appl_hierarchy_id
552 ORDER by version_number desc;
553
554 -- cursor for getting all the fee types ordered based on the rule sequence identified
555 -- for the application hierarchy id
556 CURSOR cur_app_rules(cp_appl_hierarchy_id igs_fi_app_rules.appl_hierarchy_id%TYPE)
557 IS
558 SELECT rule_sequence, fee_type, enabled_flag
559 FROM igs_fi_app_rules_v
560 WHERE appl_hierarchy_id = cp_appl_hierarchy_id AND
561 enabled_flag = 'Y'
562 ORDER BY rule_sequence;
563
564 -- cursor for getting all the fee types ordered based on the rule sequence identified
565 -- for the application hierarchy id in the Additional Authorized tab
566 CURSOR cur_adl_app_rules(cp_appl_hierarchy_id igs_fi_app_rules.appl_hierarchy_id%TYPE)
567 IS
568 SELECT rule_sequence, fee_type, enabled_flag
569 FROM igs_fi_adl_app_rul_v
570 WHERE appl_hierarchy_id = cp_appl_hierarchy_id AND
571 enabled_flag = 'Y'
572 ORDER BY rule_sequence;
573
574 -- cursor for getting all the charge records that can be applied for the
575 -- various enabled fee types for a person for the fee period (which do not have error account)
576 CURSOR cur_charges(cp_fee_type IN igs_fi_inv_int_all.fee_type%TYPE,
577 cp_person_id IN igs_fi_inv_int_all.person_id%TYPE,
578 cp_fee_cal_type IN igs_fi_inv_int_all.fee_cal_type%TYPE,
579 cp_fee_ci_sequence_number IN igs_fi_inv_int_all.fee_ci_sequence_number%TYPE)
580 IS
581 SELECT rowid, inv.*
582 FROM igs_fi_inv_int inv
583 WHERE person_id = cp_person_id AND
584 fee_type = cp_fee_type AND
585 fee_cal_type = cp_fee_cal_type AND
586 fee_ci_sequence_number = cp_fee_ci_sequence_number AND
587 invoice_amount_due > 0
588 AND NOT EXISTS (SELECT 'X'
589 FROM igs_fi_invln_int
590 WHERE invoice_id = inv.invoice_id
591 AND NVL(error_account,'N') = 'Y')
592 ORDER BY invoice_creation_date
593 FOR UPDATE NOWAIT;
594
595 -- cursor for getting all the charges records that can be applied for
596 -- various enabled fee types for a person NOT In the fee period (which do not have error account)
597
598 CURSOR cur_charges_not_fp(cp_fee_type IN igs_fi_inv_int_all.fee_type%TYPE,
599 cp_person_id IN igs_fi_inv_int_all.person_id%TYPE,
600 cp_fee_cal_type IN igs_fi_inv_int_all.fee_cal_type%TYPE,
601 cp_fee_ci_sequence_number IN igs_fi_inv_int_all.fee_ci_sequence_number%TYPE)
602 IS
603 SELECT rowid, inv.*
604 FROM igs_fi_inv_int inv
605 WHERE person_id = cp_person_id AND
606 fee_type = cp_fee_type AND
607 (fee_cal_type <> cp_fee_cal_type OR
608 fee_ci_sequence_number <> cp_fee_ci_sequence_number) AND
609 invoice_amount_due > 0
610 AND NOT EXISTS (SELECT 'X'
611 FROM igs_fi_invln_int
612 WHERE invoice_id = inv.invoice_id
613 AND NVL(error_account,'N') = 'Y')
614 ORDER BY invoice_creation_date
615 FOR UPDATE NOWAIT;
616
617
618 -- cursor for getting all the charges records that can be applied
619 -- for various enabled fee types for a person across all the fee periods (which do not have error account)
620
621 CURSOR cur_charges_normal (cp_fee_type IN igs_fi_inv_int_all.fee_type%TYPE,
622 cp_person_id IN igs_fi_inv_int_all.person_id%TYPE )
623 IS
624 SELECT rowid, inv.*
625 FROM igs_fi_inv_int inv
626 WHERE person_id = cp_person_id AND
627 fee_type = cp_fee_type AND
628 invoice_amount_due > 0
629 AND NOT EXISTS (SELECT 'X'
630 FROM igs_fi_invln_int
631 WHERE invoice_id = inv.invoice_id
632 AND NVL(error_account,'N') = 'Y')
633 ORDER BY invoice_creation_date
634 FOR UPDATE NOWAIT;
635
636
637 /* cursor for getting the title IV ind for the credit type id */
638 CURSOR cur_title4_ind (cp_credit_type_id IN igs_fi_cr_types.credit_type_id%TYPE)
639 IS
640 SELECT title4_type_ind
641 FROM igs_fi_cr_types
642 WHERE credit_type_id = cp_credit_type_id;
643
644 /* cursor for getting the funds authorization for the person_id */
645 CURSOR cur_fund_auth (cp_person_id IN igs_pe_person_v.person_id%TYPE)
646 IS
647 SELECT pd.fund_authorization, p.party_number
648 --bug:2238362, changed the view igs_pe_person_v to igs_fi_parties_v
649 --bug:5018036, replaced igs_fi_person_v by base tables
650 FROM hz_parties p, igs_pe_hz_parties pd
651 WHERE p.party_id = cp_person_id
652 AND p.party_id = pd.party_id;
653
654 --added by vvutukur
655
656 CURSOR cur_credit_type (cp_credit_type_id igs_fi_cr_types.credit_type_id%TYPE) IS
657 SELECT credit_type_name
658 FROM igs_fi_cr_types
659 WHERE credit_type_id = cp_credit_type_id;
660
661 TYPE tab_party_rec IS TABLE OF hz_parties.party_id%TYPE INDEX BY BINARY_INTEGER;
662 v_tab_party_rec tab_party_rec ;
663 l_person_id hz_parties.party_id%TYPE;
664
665 l_person_number igs_fi_parties_v.person_number%TYPE;
666 l_credit_type_name igs_fi_cr_types.credit_type_name%TYPE;
667
668 -- cursor rowtype variables
669 l_credits cur_credits%ROWTYPE;
670 l_hierarchies cur_hierarchies%ROWTYPE;
671 l_app_rules cur_app_rules%ROWTYPE;
672 l_charges cur_charges%ROWTYPE;
673 l_title4_ind cur_title4_ind%ROWTYPE;
674 l_fund_auth cur_fund_auth%ROWTYPE;
675 l_adl_app_rules cur_adl_app_rules%ROWTYPE;
676 l_charges_not_fp cur_charges_not_fp%ROWTYPE;
677 l_charges_normal cur_charges_normal%ROWTYPE;
678 -- table type variables
679 l_charges_amount igs_fi_inv_int_all.invoice_amount_due%TYPE;
680 l_credits_amount igs_fi_credits_all.unapplied_amount%TYPE;
681 l_invoice_amount_due igs_fi_inv_int_all.invoice_amount_due%TYPE;
682 l_credits_unapplied_amount igs_fi_credits_all.unapplied_amount%TYPE;
683 l_applications_rowid VARCHAR2(25); -- OUT NOCOPY parameter from the applications table Insert TBH
684 l_rec_installed VARCHAR2(1); -- variable captures the value returned from the
685 -- function which checks AR is installed or not
686 l_credit_date_low DATE; -- VARCHAR2 datatypes will be made DATE types
687 l_credit_date_high DATE;
688 -- Boolean Flags
689 l_b_f_period_missing BOOLEAN := FALSE;
690 -- Message Variables
691 l_msg_str_0 VARCHAR2(2000);
692 l_msg_str_1 VARCHAR2(2000);
693 l_n_charges_app_count NUMBER := 0;
694
695 /** Below code is Addition due to SFCr020 - 2191470 */
696 l_v_fund_auth_profile VARCHAR2(1) := NVL(FND_PROFILE.VALUE('IGS_FI_FUND_AUTH'),'N');
697 /** Profile value taken as N if not Set - hence used NVL */
698
699 l_process_credit BOOLEAN;
700 l_cur_cal_end_dt igs_ca_inst.end_dt%TYPE;
701 l_fee_cal_end_dt igs_ca_inst.end_dt%TYPE;
702 l_status BOOLEAN;
703 l_message_name fnd_new_messages.message_name%TYPE;
704 l_c_closing_status gl_period_statuses.closing_status%TYPE;
705
706 l_v_manage_acc igs_fi_control_all.manage_accounts%TYPE := NULL;
707 l_v_message_name fnd_new_messages.message_name%TYPE := NULL;
708
709 BEGIN
710
711 --added by vvutukur for bug#2326163
712
713 OPEN cur_credit_type(p_credit_type_id);
714 FETCH cur_credit_type INTO l_credit_type_name;
715 CLOSE cur_credit_type;
716
717 /* Logging of Parameters of the procedure */
718
719 /* Get the party number*/
720 OPEN cur_fund_auth(p_person_id);
721 FETCH cur_fund_auth INTO l_fund_auth;
722 CLOSE cur_fund_auth;
723
724 fnd_message.set_name('IGS','IGS_FI_GEN_PARAMETER');
725 fnd_message.set_token('PARM_TYPE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PARTY'));
726 fnd_message.set_token('PARM_VALUE', l_fund_auth.party_number);
727 fnd_file.put_line(fnd_file.log, fnd_message.Get);
728
729 fnd_message.set_name('IGS','IGS_FI_GEN_PARAMETER');
730 fnd_message.set_token('PARM_TYPE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PERSON_GROUP'));
731 fnd_message.set_token('PARM_VALUE',igs_fi_gen_005.finp_get_prsid_grp_code(p_person_id_grp));
732 fnd_file.put_line(fnd_file.log, fnd_message.Get);
733
734 fnd_message.set_name('IGS','IGS_FI_GEN_PARAMETER');
735 fnd_message.set_token('PARM_TYPE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','CREDIT_NUMBER'));
736 fnd_message.set_token('PARM_VALUE', p_credit_number);
737 fnd_file.put_line(fnd_file.log, fnd_message.Get);
738
739 fnd_message.set_name('IGS','IGS_FI_GEN_PARAMETER');
740 fnd_message.set_token('PARM_TYPE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'CREDIT_TYPE_NAME'));
741 fnd_message.set_token('PARM_VALUE', l_credit_type_name);
742 fnd_file.put_line(fnd_file.log, fnd_message.Get);
743
744 fnd_message.set_name('IGS','IGS_FI_GEN_PARAMETER');
745 fnd_message.set_token('PARM_TYPE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'CREDIT_DATE_LOW'));
746 fnd_message.set_token('PARM_VALUE', p_credit_date_low);
747 fnd_file.put_line(fnd_file.log, fnd_message.Get);
748
749 fnd_message.set_name('IGS','IGS_FI_GEN_PARAMETER');
750 fnd_message.set_token('PARM_TYPE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','CREDIT_DATE_HIGH'));
751 fnd_message.set_token('PARM_VALUE', p_credit_date_high);
752 fnd_file.put_line(fnd_file.log, fnd_message.Get);
753
754 fnd_message.set_name('IGS','IGS_FI_GEN_PARAMETER');
755 fnd_message.set_token('PARM_TYPE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','GL_DATE'));
756 fnd_message.set_token('PARM_VALUE', p_d_gl_date);
757 fnd_file.put_line(fnd_file.log, fnd_message.Get);
758
759
760 -- Obtain the value of manage_accounts in the System Options form
761 -- If it is null or 'OTHER', then this process is not available, so error out.
762 igs_fi_com_rec_interface.chk_manage_account( p_v_manage_acc => l_v_manage_acc,
763 p_v_message_name => l_v_message_name
764 );
765 IF (l_v_manage_acc = 'OTHER') OR (l_v_manage_acc IS NULL) THEN
766 fnd_message.set_name('IGS',l_v_message_name);
767 igs_ge_msg_stack.add;
768 app_exception.raise_exception;
769 END IF;
770
771 -- validate whether GL Date is provided by the user. If the user has not
772 -- provided the GL Date parameter error out NOCOPY of the process
773
774 IF p_d_gl_date IS NULL THEN
775 fnd_message.set_name('IGS','IGS_GE_INSUFFICIENT_PARAMETER');
776 igs_ge_msg_stack.add;
777 app_exception.raise_exception;
778 END IF;
779
780 -- Check if both person id and person group id is passed as parameters
781 -- if both are passed then error is logged in the log file and returned
782 IF (p_person_id IS NOT NULL AND p_person_id_grp IS NOT NULL) THEN
783 fnd_message.Set_Name('IGS','IGS_FI_NO_PERS_PGRP');
784 igs_ge_msg_stack.add;
785 app_exception.raise_exception;
786 END IF;
787
788 -- validate the GL Date parameter passed to the process
789 -- This procedure returns the derived Closing status of the period in which the passed GL Date belongs to
790 igs_fi_gen_gl.get_period_status_for_date (p_d_date => p_d_gl_date,
791 p_v_closing_status => l_c_closing_status,
792 p_v_message_name => l_message_name
793 );
794 -- if the derived closing status is other than 'F' or 'O' , log the error and error out NOCOPY of the process
795 IF l_c_closing_status IN ('C','N','W')
796 THEN
797 fnd_message.set_name('IGS','IGS_FI_INVALID_GL_DATE');
798 fnd_message.set_token('GL_DATE',p_d_gl_date);
799 igs_ge_msg_stack.add;
800 app_exception.raise_exception;
801 END IF;
802 IF l_c_closing_status IS NULL AND l_message_name IS NOT NULL
803 THEN
804 fnd_message.set_name('IGS',l_message_name);
805 igs_ge_msg_stack.add;
806 app_exception.raise_exception;
807 END IF;
808
809 -- Set the values of high and low dates when
810 IF p_credit_date_low IS NOT NULL THEN
811 l_credit_date_low := igs_ge_date.igsdate(p_credit_date_low);
812 ELSE
813 l_credit_date_low := eff_min_date();
814 END IF;
815
816 IF p_credit_date_high IS NOT NULL THEN
817 l_credit_date_high := igs_ge_date.igsdate(p_credit_date_high);
818 ELSE
819 l_credit_date_high := eff_max_date();
820 END IF;
821
822 --If both credit date high and credit date low are specified then high date should be greater than or equal to low date
823 IF p_credit_date_low IS NOT NULL AND p_credit_date_high IS NOT NULL THEN
824 IF TRUNC(l_credit_date_low) > TRUNC(l_credit_date_high) THEN
825 fnd_message.Set_Name('IGS','IGS_FI_TRAN_LOW_HIGH');
826 fnd_message.set_token('DATE_LOW',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','CREDIT_DATE_LOW'));
827 fnd_message.set_token('DATE_HIGH',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','CREDIT_DATE_HIGH'));
828 igs_ge_msg_stack.add;
829 app_exception.raise_exception;
830 END IF;
831 END IF;
832
833 /* Open person cursors to loop across the persons in context */
834
835 IF p_person_id_grp IS NOT NULL THEN
836 OPEN cur_person_group_id(p_person_id_grp);
837 FETCH cur_person_group_id BULK COLLECT INTO v_tab_party_rec;
838 CLOSE cur_person_group_id;
839 ELSIF p_person_id IS NOT NULL THEN
840 OPEN cur_person_id(p_person_id);
841 FETCH cur_person_id BULK COLLECT INTO v_tab_party_rec;
842 CLOSE cur_person_id;
843 ELSE
844 OPEN cur_all_person_id;
845 FETCH cur_all_person_id BULK COLLECT INTO v_tab_party_rec;
846 CLOSE cur_all_person_id;
847 END IF;
848
849 IF v_tab_party_rec.COUNT > 0 THEN
850 -- Loop across all the Person ids identified for processing for Refunds
851 FOR l_n_cntr IN v_tab_party_rec.FIRST..v_tab_party_rec.LAST
852 LOOP
853 l_person_id := v_tab_party_rec(l_n_cntr);
854 BEGIN
855 SAVEPOINT APPL_SP1;
856 /* Get the value of funds authorization for the person l_person_id */
857 OPEN cur_fund_auth(l_person_id);
858 FETCH cur_fund_auth INTO l_fund_auth;
859 CLOSE cur_fund_auth;
860
861 -- Get all the unapplied credits that are to be applied depending on the parameters the user entered
862 OPEN cur_credits(l_person_id,l_credit_date_low,l_credit_date_high);
863 LOOP
864 FETCH cur_credits INTO l_credits;
865 EXIT WHEN cur_credits%NOTFOUND;
866
867 l_b_f_period_missing := FALSE;
868 l_n_charges_app_count := 0;
869
870
871 /* Get title 4 ind of the credit record */
872 OPEN cur_title4_ind(l_credits.credit_type_id);
873 FETCH cur_title4_ind INTO l_title4_ind;
874 CLOSE cur_title4_ind;
875
876 l_msg_str_0 := '----------------------------------------------------------------------------------------';
877
878 fnd_file.put_line(fnd_file.log,l_msg_str_0);
879 fnd_file.new_line(fnd_file.log);
880
881 fnd_file.put_line(fnd_file.log,igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PARTY')||' : '||l_fund_auth.party_number);
882 fnd_file.put_line(fnd_file.log,igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','CREDIT_NUMBER')||' : '||l_credits.credit_number);
883 fnd_file.put_line(fnd_file.log,igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','CREDIT_TYPE_NAME')||' : '||l_credits.credit_type_name);
884 fnd_file.put_line(fnd_file.log,igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','UNAPPLIED_AMOUNT')||' : '||TO_CHAR(l_credits.unapplied_amount));
885 fnd_file.put_line(fnd_file.log,igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','TITLE4_IND')||' : '||NVL(l_title4_ind.title4_type_ind,'N'));
886 fnd_file.put_line(fnd_file.log,igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FUND_AUTH')||' : '||NVL(l_fund_auth.fund_authorization,'N'));
887 fnd_file.put_line(fnd_file.log,igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FEE_PERIOD')||' : '||l_credits.fee_cal_type||' '||TO_CHAR(l_credits.fee_ci_sequence_number));
888 fnd_file.new_line(fnd_file.log);
889
890 -- Capture the credits unapplied amount before the application to charges begin
891 l_credits_unapplied_amount := l_credits.unapplied_amount;
892
893 l_process_credit := TRUE;
894
895 -- If the Fee Period fields of the Credit Record are not null then
896 -- derive the End Date of the Load Calendar for the Fee Period.
897 IF ((l_credits.fee_cal_type IS NOT NULL) AND
898 (l_credits.fee_ci_sequence_number IS NOT NULL)) THEN
899 get_cal_details(p_fee_cal_type => l_credits.fee_cal_type,
900 p_fee_seq => l_credits.fee_ci_sequence_number,
901 p_end_dt => l_cur_cal_end_dt,
902 p_message => l_message_name,
903 p_status => l_status);
904 IF NOT l_status THEN
905 fnd_message.set_name('IGS',l_message_name);
906 fnd_file.put_line(fnd_file.log,
907 fnd_message.get);
908 l_process_credit := FALSE;
909 END IF;
910 END IF;
911
912 -- If the Credit has to be processed.
913 IF l_process_credit THEN
914
915 /* Get the latest (version Number) unique application hierarchy id from the application hierarchy table
916 for the credit_type_id */
917 OPEN cur_hierarchies(l_credits.appl_hierarchy_id);
918 FETCH cur_hierarchies INTO l_hierarchies;
919 IF cur_hierarchies%NOTFOUND THEN
920 fnd_message.set_name('IGS','IGS_FI_NO_APPL_HIER');
921 fnd_message.set_token('CREDIT_ID',l_credits.credit_number);
922 fnd_file.put_line(fnd_file.log,fnd_message.Get);
923 fnd_file.new_line(fnd_file.log);
924 END IF;
925 CLOSE cur_hierarchies;
926
927 l_msg_str_1 :=igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','APPL_HIER_NAME') || l_hierarchies.hierarchy_name;
928 fnd_file.put_line(fnd_file.log,l_msg_str_1);
929 fnd_file.new_line(fnd_file.log);
930
931 /* Check if the IGS_FI_FUND_AUTH profile is set to Y */
932 IF l_v_fund_auth_profile = 'Y' THEN
933 /* Check if the credit is title 4 */
934 IF NVL(l_title4_ind.title4_type_ind,'N') = 'Y' THEN
935 /* For title 4 if credits fee period is null log error */
936 IF (l_credits.fee_cal_type IS NULL OR l_credits.fee_ci_sequence_number IS NULL) THEN
937 l_b_f_period_missing := TRUE;
938 fnd_message.set_name('IGS','IGS_FI_FEEPERIOD_NULL');
939 fnd_message.set_token('CREDIT_ID', l_credits.credit_number );
940 fnd_file.put_line(fnd_file.log,' '||fnd_message.Get);
941 fnd_file.new_line(fnd_file.log);
942 END IF;
943 /* Proceed only if the fee period is present for Title IV Credits */
944 IF NOT l_b_f_period_missing THEN
945 /* Check if the person has funds authorization */
946 IF NVL(l_fund_auth.fund_authorization,'N') = 'Y' THEN
947 /** This section is for profile Y , title4 Y and fund auth Y **/
948 fnd_file.put_line(fnd_file.log,' '||igs_fi_gen_gl.get_lkp_meaning('IGS_FI_RULE_TYPE','ALLOW'));
949 OPEN cur_app_rules(l_hierarchies.appl_hierarchy_id); -- All Allowed rules
950 LOOP
951 FETCH cur_app_rules INTO l_app_rules;
952 EXIT WHEN ( cur_app_rules%NOTFOUND OR l_credits_unapplied_amount = 0);
953 l_msg_str_1 := ' '||igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FEE_TYPES') || l_app_rules.fee_type ||','|| igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','RULE_SEQ') ||to_char( l_app_rules.rule_sequence);
954 fnd_file.put_line(fnd_file.log,l_msg_str_1);
955
956 -- Get the records from the charges table for a fee_type, person id and fee period
957 l_charges_amount := 0;
958 OPEN cur_charges(l_app_rules.fee_type,l_credits.party_id,l_credits.fee_cal_type,l_credits.fee_ci_sequence_number);
959 LOOP
960 FETCH cur_charges INTO l_charges;
961 EXIT WHEN (cur_charges%NOTFOUND OR l_credits_unapplied_amount = 0);
962 IF (l_credits_unapplied_amount >= l_charges.invoice_amount_due) THEN
963 l_charges_amount := l_charges.invoice_amount_due;
964 l_invoice_amount_due := 0;
965 l_credits_unapplied_amount := l_credits_unapplied_amount - l_charges.invoice_amount_due;
966 ELSE
967 l_charges_amount := l_credits_unapplied_amount;
968 l_invoice_amount_due := l_charges.invoice_amount_due - l_credits_unapplied_amount;
969 l_credits_unapplied_amount := 0;
970 END IF;
971 /* If an eligible charge is found then invoke application procedure */
972 apply_and_log_messages(
973 p_invoice_id => l_charges.invoice_id,
974 p_credit_id => l_credits.credit_id,
975 p_fee_type => l_app_rules.fee_type,
976 p_fee_cal_type => l_charges.fee_cal_type,
977 p_unapplied_amount=> l_credits_unapplied_amount,
978 p_orig_invoice_amount_due => l_charges.invoice_amount_due,
979 p_invoice_amount_due => l_invoice_amount_due,
980 p_amount_applied => l_charges_amount,
981 p_appl_hierarchy_id => l_hierarchies.appl_hierarchy_id,
982 P_counter => l_n_charges_app_count,
983 p_d_gl_date => p_d_gl_date
984 );
985 END LOOP; -- Endloop for Charges for each app rule
986 CLOSE cur_charges;
987 END LOOP; -- Endloop for appl rules in Allowable
988 CLOSE cur_app_rules;
989
990 /**** If local varible unapplied amount is still left then proceed to pick up from Additional Authorized****/
991 IF l_credits_unapplied_amount > 0 THEN
992 fnd_file.put_line(fnd_file.log,igs_fi_gen_gl.get_lkp_meaning('IGS_FI_RULE_TYPE','ADDITION'));
993 OPEN cur_adl_app_rules(l_hierarchies.appl_hierarchy_id);
994 LOOP
995 FETCH cur_adl_app_rules INTO l_adl_app_rules;
996 EXIT WHEN ( cur_adl_app_rules%NOTFOUND OR l_credits_unapplied_amount = 0);
997
998 l_msg_str_1 := ' '||igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FEE_TYPES') || l_adl_app_rules.fee_type ||
999 ','|| igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','RULE_SEQ') ||to_char( l_adl_app_rules.rule_sequence);
1000 fnd_file.put_line(fnd_file.log,l_msg_str_1);
1001
1002 -- Get the records from the charges table for a fee_type, person id and fee period
1003 l_charges_amount := 0;
1004 OPEN cur_charges(l_adl_app_rules.fee_type,l_credits.party_id,l_credits.fee_cal_type,l_credits.fee_ci_sequence_number);
1005 LOOP
1006 FETCH cur_charges INTO l_charges;
1007 EXIT WHEN (cur_charges%NOTFOUND OR l_credits_unapplied_amount = 0);
1008 IF (l_credits_unapplied_amount >= l_charges.invoice_amount_due) THEN
1009 l_charges_amount := l_charges.invoice_amount_due;
1010 l_invoice_amount_due := 0;
1011 l_credits_unapplied_amount := l_credits_unapplied_amount - l_charges.invoice_amount_due;
1012 ELSE
1013 l_charges_amount := l_credits_unapplied_amount;
1014 l_invoice_amount_due := l_charges.invoice_amount_due - l_credits_unapplied_amount;
1015 l_credits_unapplied_amount := 0;
1016 END IF;
1017
1018 /* If an eligible charge is found then invoke application procedure */
1019
1020 apply_and_log_messages(
1021 p_invoice_id => l_charges.invoice_id,
1022 p_credit_id => l_credits.credit_id,
1023 p_fee_type => l_adl_app_rules.fee_type,
1024 p_fee_cal_type => l_charges.fee_cal_type,
1025 p_unapplied_amount => l_credits_unapplied_amount,
1026 p_orig_invoice_amount_due => l_charges.invoice_amount_due,
1027 p_invoice_amount_due => l_invoice_amount_due,
1028 p_amount_applied => l_charges_amount,
1029 p_appl_hierarchy_id => l_hierarchies.appl_hierarchy_id,
1030 P_counter => l_n_charges_app_count,
1031 p_d_gl_date => p_d_gl_date
1032 );
1033 END LOOP; -- Endloop for Charges for each adl app rule
1034 CLOSE cur_charges;
1035 END LOOP; -- Endloop for appl rules in Additional tab
1036 CLOSE cur_adl_app_rules;
1037 END IF; -- Unapplied amount
1038
1039 /**** If unapplied amount is still left then proceed to pick up from Allowed tab again****/
1040 IF l_credits_unapplied_amount > 0 THEN
1041 OPEN cur_app_rules(l_hierarchies.appl_hierarchy_id); -- All Allowed rules
1042 LOOP
1043 FETCH cur_app_rules INTO l_app_rules;
1044 EXIT WHEN ( cur_app_rules%NOTFOUND OR l_credits_unapplied_amount = 0);
1045
1046 -- Get the records from the charges table for a fee_type, person id and NOT IN fee period
1047 l_charges_amount := 0;
1048 OPEN cur_charges_not_fp(l_app_rules.fee_type,l_credits.party_id,l_credits.fee_cal_type,l_credits.fee_ci_sequence_number);
1049 LOOP
1050 FETCH cur_charges_not_fp INTO l_charges_not_fp;
1051 EXIT WHEN (cur_charges_not_fp%NOTFOUND OR l_credits_unapplied_amount = 0);
1052 l_status := TRUE;
1053 l_message_name := NULL;
1054 l_fee_cal_end_dt := NULL;
1055 get_cal_details(p_fee_cal_type => l_charges_not_fp.fee_cal_type,
1056 p_fee_seq => l_charges_not_fp.fee_ci_sequence_number,
1057 p_end_dt => l_fee_cal_end_dt,
1058 p_message => l_message_name,
1059 p_status => l_status);
1060
1061 IF (TRUNC(l_fee_cal_end_dt) <= TRUNC(l_cur_cal_end_dt) AND l_status) THEN
1062
1063 IF (l_credits_unapplied_amount >= l_charges_not_fp.invoice_amount_due) THEN
1064 l_charges_amount := l_charges_not_fp.invoice_amount_due;
1065 l_invoice_amount_due := 0;
1066 l_credits_unapplied_amount := l_credits_unapplied_amount - l_charges_not_fp.invoice_amount_due;
1067 ELSE
1068 l_charges_amount := l_credits_unapplied_amount;
1069 l_invoice_amount_due := l_charges_not_fp.invoice_amount_due - l_credits_unapplied_amount;
1070 l_credits_unapplied_amount := 0;
1071 END IF;
1072 /* If an eligible charge is found then invoke application procedure */
1073 apply_and_log_messages(
1074 p_invoice_id => l_charges_not_fp.invoice_id,
1075 p_credit_id => l_credits.credit_id,
1076 p_fee_type => l_app_rules.fee_type,
1077 p_fee_cal_type => l_charges_not_fp.fee_cal_type,
1078 p_unapplied_amount => l_credits_unapplied_amount,
1079 p_orig_invoice_amount_due => l_charges_not_fp.invoice_amount_due,
1080 p_invoice_amount_due => l_invoice_amount_due,
1081 p_amount_applied => l_charges_amount,
1082 p_appl_hierarchy_id => l_hierarchies.appl_hierarchy_id,
1083 P_counter => l_n_charges_app_count,
1084 p_d_gl_date => p_d_gl_date
1085 );
1086 END IF; -- End if for End Dates
1087 END LOOP; -- Endloop for Charges for each adl app rule
1088 CLOSE cur_charges_not_fp;
1089 END LOOP; -- Endloop for appl rules in Additional tab
1090 CLOSE cur_app_rules;
1091 END IF; -- Unapplied amount
1092
1093 /**** If unapplied amount is still left then proceed to pick up from Allowed tab again****/
1094 IF l_credits_unapplied_amount > 0 THEN
1095 OPEN cur_adl_app_rules(l_hierarchies.appl_hierarchy_id);
1096 LOOP
1097 FETCH cur_adl_app_rules INTO l_adl_app_rules;
1098 EXIT WHEN ( cur_adl_app_rules%NOTFOUND OR l_credits_unapplied_amount = 0);
1099
1100 -- Get the records from the charges table for a fee_type, person id and NOT IN fee period
1101 l_charges_amount := 0;
1102 OPEN cur_charges_not_fp(l_adl_app_rules.fee_type,l_credits.party_id,l_credits.fee_cal_type,l_credits.fee_ci_sequence_number);
1103 LOOP
1104 FETCH cur_charges_not_fp INTO l_charges_not_fp;
1105 EXIT WHEN (cur_charges_not_fp%NOTFOUND OR l_credits_unapplied_amount = 0);
1106 l_status := TRUE;
1107 l_message_name := NULL;
1108 l_fee_cal_end_dt := NULL;
1109
1110 get_cal_details(p_fee_cal_type => l_charges_not_fp.fee_cal_type,
1111 p_fee_seq => l_charges_not_fp.fee_ci_sequence_number,
1112 p_end_dt => l_fee_cal_end_dt,
1113 p_message => l_message_name,
1114 p_status => l_status);
1115
1116 IF (TRUNC(l_fee_cal_end_dt) <= TRUNC(l_cur_cal_end_dt) AND l_status) THEN
1117 IF (l_credits_unapplied_amount >= l_charges_not_fp.invoice_amount_due) THEN
1118 l_charges_amount := l_charges_not_fp.invoice_amount_due;
1119 l_invoice_amount_due := 0;
1120 l_credits_unapplied_amount := l_credits_unapplied_amount - l_charges_not_fp.invoice_amount_due;
1121 ELSE
1122 l_charges_amount := l_credits_unapplied_amount;
1123 l_invoice_amount_due := l_charges_not_fp.invoice_amount_due - l_credits_unapplied_amount;
1124 l_credits_unapplied_amount := 0;
1125 END IF;
1126
1127 /* If an eligible charge is found then invoke application procedure */
1128 apply_and_log_messages(
1129 p_invoice_id => l_charges_not_fp.invoice_id,
1130 p_credit_id => l_credits.credit_id,
1131 p_fee_type => l_adl_app_rules.fee_type,
1132 p_fee_cal_type => l_charges_not_fp.fee_cal_type,
1133 p_unapplied_amount => l_credits_unapplied_amount,
1134 p_orig_invoice_amount_due => l_charges_not_fp.invoice_amount_due,
1135 p_invoice_amount_due => l_invoice_amount_due,
1136 p_amount_applied => l_charges_amount,
1137 p_appl_hierarchy_id => l_hierarchies.appl_hierarchy_id,
1138 P_counter => l_n_charges_app_count,
1139 p_d_gl_date => p_d_gl_date
1140 );
1141 END IF; -- End if for the End Date check
1142 END LOOP; -- Endloop for Charges for each adl app rule
1143 CLOSE cur_charges_not_fp;
1144 END LOOP; -- Endloop for appl rules in Additional tab
1145 CLOSE cur_adl_app_rules;
1146 END IF; -- Unapplied amount
1147 ELSIF NVL(l_fund_auth.fund_authorization,'N') = 'N' THEN
1148 /** This section is for profile Y , title4 Y and fund auth N **/
1149 fnd_file.put_line(fnd_file.log,' '||igs_fi_gen_gl.get_lkp_meaning('IGS_FI_RULE_TYPE','ALLOW'));
1150 OPEN cur_app_rules(l_hierarchies.appl_hierarchy_id);
1151 LOOP
1152 FETCH cur_app_rules INTO l_app_rules;
1153 EXIT WHEN ( cur_app_rules%NOTFOUND OR l_credits_unapplied_amount = 0);
1154
1155 l_msg_str_1 := ' '||igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FEE_TYPES') || l_app_rules.fee_type
1156 ||','|| igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','RULE_SEQ') ||to_char( l_app_rules.rule_sequence);
1157 fnd_file.put_line(fnd_file.log,l_msg_str_1);
1158 -- Get the records from the charges table for a fee_type, person id for the fee period only
1159 l_charges_amount := 0;
1160 OPEN cur_charges(l_app_rules.fee_type,l_credits.party_id,l_credits.fee_cal_type,l_credits.fee_ci_sequence_number);
1161 LOOP
1162 FETCH cur_charges INTO l_charges;
1163 EXIT WHEN (cur_charges%NOTFOUND OR l_credits_unapplied_amount = 0);
1164 IF (l_credits_unapplied_amount >= l_charges.invoice_amount_due) THEN
1165 l_charges_amount := l_charges.invoice_amount_due;
1166 l_invoice_amount_due := 0;
1167 l_credits_unapplied_amount := l_credits_unapplied_amount - l_charges.invoice_amount_due;
1168 ELSE
1169 l_charges_amount := l_credits_unapplied_amount;
1170 l_invoice_amount_due := l_charges.invoice_amount_due - l_credits_unapplied_amount;
1171 l_credits_unapplied_amount := 0;
1172 END IF;
1173 /* If an eligible charge is found then invoke application procedure */
1174 apply_and_log_messages(
1175 p_invoice_id => l_charges.invoice_id,
1176 p_credit_id => l_credits.credit_id,
1177 p_fee_type => l_app_rules.fee_type,
1178 p_fee_cal_type => l_charges.fee_cal_type,
1179 p_unapplied_amount => l_credits_unapplied_amount,
1180 p_orig_invoice_amount_due => l_charges.invoice_amount_due,
1181 p_invoice_amount_due => l_invoice_amount_due,
1182 p_amount_applied => l_charges_amount,
1183 p_appl_hierarchy_id => l_hierarchies.appl_hierarchy_id,
1184 P_counter => l_n_charges_app_count,
1185 p_d_gl_date => p_d_gl_date
1186 );
1187 END LOOP; -- Endloop for Charges for each app rule
1188 CLOSE cur_charges;
1189 END LOOP; -- Endloop for appl rules in Allowable
1190 CLOSE cur_app_rules;
1191 END IF; -- For fund Auth
1192 END IF; -- For Fee Period Present
1193 END IF; --For title4 = 'Y'
1194 END IF; --For Profile = 'Y'
1195 IF (l_v_fund_auth_profile = 'N' OR NVL(l_title4_ind.title4_type_ind,'N') = 'N') THEN
1196 fnd_file.put_line(fnd_file.log,' '||igs_fi_gen_gl.get_lkp_meaning('IGS_FI_RULE_TYPE','ALLOW'));
1197 OPEN cur_app_rules(l_hierarchies.appl_hierarchy_id); -- All Allowed rules
1198 LOOP
1199 FETCH cur_app_rules INTO l_app_rules;
1200 EXIT WHEN ( cur_app_rules%NOTFOUND OR l_credits_unapplied_amount = 0);
1201
1202 l_msg_str_1 := ' '||igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FEE_TYPES') || l_app_rules.fee_type ||','|| igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','RULE_SEQ') ||to_char( l_app_rules.rule_sequence);
1203 fnd_file.put_line(fnd_file.log,l_msg_str_1);
1204
1205 -- Get the records from the charges table for a fee_type, person id w/o considering fee period at all - Fund Authorization also need not considered
1206 l_charges_amount := 0;
1207 OPEN cur_charges_normal(l_app_rules.fee_type,l_credits.party_id);
1208 LOOP
1209 FETCH cur_charges_normal INTO l_charges_normal;
1210 EXIT WHEN (cur_charges_normal%NOTFOUND OR l_credits_unapplied_amount = 0);
1211 IF (l_credits_unapplied_amount >= l_charges_normal.invoice_amount_due) THEN
1212 l_charges_amount := l_charges_normal.invoice_amount_due;
1213 l_invoice_amount_due := 0;
1214 l_credits_unapplied_amount := l_credits_unapplied_amount - l_charges_normal.invoice_amount_due;
1215 ELSE
1216 l_charges_amount := l_credits_unapplied_amount;
1217 l_invoice_amount_due := l_charges_normal.invoice_amount_due - l_credits_unapplied_amount;
1218 l_credits_unapplied_amount := 0;
1219 END IF;
1220
1221 /* If an eligible charge is found then invoke application procedure */
1222 apply_and_log_messages(
1223 p_invoice_id => l_charges_normal.invoice_id,
1224 p_credit_id => l_credits.credit_id,
1225 p_fee_type => l_app_rules.fee_type,
1226 p_fee_cal_type => l_charges_normal.fee_cal_type,
1227 p_unapplied_amount => l_credits_unapplied_amount,
1228 p_orig_invoice_amount_due => l_charges_normal.invoice_amount_due,
1229 p_invoice_amount_due => l_invoice_amount_due,
1230 p_amount_applied => l_charges_amount,
1231 p_appl_hierarchy_id => l_hierarchies.appl_hierarchy_id,
1232 P_counter => l_n_charges_app_count,
1233 p_d_gl_date => p_d_gl_date
1234 );
1235 END LOOP; -- Endloop for Charges for each app rule
1236 CLOSE cur_charges_normal;
1237 END LOOP; -- Endloop for appl rules in Allowable
1238 CLOSE cur_app_rules;
1239 END IF; --For Profile = 'Y' and title = N
1240
1241 /* log message for " no eligible charge for application if the counter l_n_charges_app_count is Zero - no charges were found" */
1242 IF l_n_charges_app_count = 0 THEN
1243 fnd_message.set_name('IGS','IGS_FI_NO_ELIG_CHGAPP');
1244 fnd_message.set_token('PERSON_ID', l_fund_auth.party_number);
1245 fnd_message.set_token('CREDIT_ID', l_credits.credit_number);
1246 fnd_file.put_line(fnd_file.log,' '||fnd_message.Get);
1247 fnd_file.new_line(fnd_file.log);
1248 END IF;
1249 END IF; -- For the l_process_credit
1250 END LOOP;
1251 CLOSE cur_credits;
1252 EXCEPTION
1253 WHEN e_lock_exception THEN
1254 ROLLBACK TO APPL_SP1;
1255 g_v_lock := 'Y';
1256 fnd_file.put_line(fnd_file.log,igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PARTY')||' : '||l_fund_auth.party_number);
1257 fnd_message.set_name('IGS',
1258 'IGS_FI_APPL_PRC_RUN');
1259 fnd_file.put_line(fnd_file.log, fnd_message.get);
1260 END;
1261 END LOOP;
1262 END IF; -- End if of IF checking for v_tab_party_rec.COUNT > 0
1263 END mass_apply;
1264
1265 -- end of package body
1266 END igs_fi_prc_appl;
[Home] [Help]