1 PACKAGE BODY igf_sl_award AS
2 /* $Header: IGFSL13B.pls 120.5 2006/08/10 16:16:24 museshad ship $ */
3
4 --
5 ----------------------------------------------------------------------------------------
6 -- Created By : venagara
7 -- Date Created On : 2000/12/12
8 -- Purpose :
9 -- Know limitations, enhancements or remarks
10 -- Change History
11 ----------------------------------------------------------------------------------------
12 -- Who When What
13 ----------------------------------------------------------------------------------------
14 -- museshad 10-Aug-2006 Bug 5337555. Build FA 163. TBH Impact.
15 ----------------------------------------------------------------------------------------
16 -- museshad 20-Sep-2005 Bug 3943742.
17 -- When the Preferred lender relationship code
18 -- has an override, it was not being considered.
19 -- Fixed this issue.
20 ----------------------------------------------------------------------------------------
21 -- museshad 06-May-2005 Bug# 4346258 Modified the entire logic in the function
22 -- 'get_loan_cl_version()' so that it arrives at the
23 -- correct CL version#
24 ----------------------------------------------------------------------------------------
25 -- mnade 8-Feb-2005 Bug 4127250 chk_disb_date call changed to pass the dates being set
26 -- for checking if that is covering all the disbursements.
27 ----------------------------------------------------------------------------------------
28 -- pssahni 20-Dec-2004 Bug #4059136 Allow DML operations if loan status is accepted
29 --------------------------------------------------------------------------------------------
30 -- ridas 14-Sep-2004 bug 3847105 - Log message in case the Loan is created using Default Lender setup
31 -- despite of Preferred Lender as the Preferred Lender is not setup for the Award Year.
32 ------------------------------------------------------------------------
33
34 -- veramach July 2004 FA 151 HR Integration (bug#3709292)
35 -- Impacts of obsoleting columns from igf_aw_awd_disb_all
36 ---------------------------------------------------------------------------------
37 -- sjadhav 18-Feb-2004 Modified get_loan_fee1 call so that
38 -- pick_setup is invoked only for FFELP/ALT
39 -- Loan calculations
40 ----------------------------------------------------------------------------------------
41 -- veramach 1-NOV-2003 FA 125 Multiple Distr Methods
42 -- Changed calll to igf_aw_awd_disb_pkg.update_row to
43 -- reflect the addition of attendance_type_code
44 ----------------------------------------------------------------------------------------
45 -- sjalasut 30 OCT 03 Uncommented the Code to get associated org
46 -- for the Person as part of FA126 Multiple FA
47 -- Office Build. Bug 3102439. Also added local
48 -- variable declarations that are out parameters
49 ----------------------------------------------------------------------------------------
50 -- sjadhav 8-Oct-2003 Bug 3104228 FA 122
51 -- added cursor find lender
52 -- corrected upd lock process
53 ----------------------------------------------------------------------------------------
54 -- bkkumar 07-oct-2003 Bug 3104228 Used the global variables g_rel_code,
55 -- g_party_id instead of calling pick_setup everytime.
56 -- Also removed the select_org procedure
57 ----------------------------------------------------------------------------------------
58 -- bkkumar 30-sep-2003 FA 122 Loan Enhancents
59 -- Added new function get_cl_auto_late_ind,
60 -- pick_setup and changed get_loan_fee1,get_loan_fee2,
61 -- get_cl_hold_rel_ind, recalc_fees
62 ----------------------------------------------------------------------------------------
63 --
64
65
66 l_dl_fee1_staf igf_sl_dl_setup.orig_fee_perct_stafford%TYPE;
67 l_dl_fee1_plus igf_sl_dl_setup.orig_fee_perct_plus%TYPE;
68 l_dl_int_rebate igf_sl_dl_setup.int_rebate%TYPE;
69
70
71 l_cl_fee1 igf_sl_cl_setup.est_orig_fee_perct%TYPE;
72 l_cl_alt_fee1 igf_sl_cl_setup.est_alt_orig_fee_perct%TYPE;
73
74 CURSOR c_dlsetup (p_ci_cal_type igs_ca_inst_all.cal_type%TYPE,
75 p_ci_seq_num igs_ca_inst_all.sequence_number%TYPE)IS
76 SELECT orig_fee_perct_stafford,
77 orig_fee_perct_plus,
78 int_rebate
79 FROM igf_sl_dl_setup
80 WHERE ci_cal_type = p_ci_cal_type
81 AND ci_sequence_number = p_ci_seq_num;
82
83 CURSOR c_get_fed_fund_code (
84 cp_award_id igf_sl_loans.award_id%TYPE
85 )
86 IS
87 SELECT fed_fund_code
88 FROM igf_aw_award awd,
89 igf_aw_fund_mast fundmast,
90 igf_aw_fund_cat fundcat
91 WHERE awd.award_id = cp_award_id
92 AND awd.fund_id = fundmast.fund_id
93 AND fundmast.fund_code = fundcat.fund_code;
94
95 FUNCTION get_cl_hold_rel_ind(p_fed_fund_code igf_aw_fund_cat.fed_fund_code%TYPE,
96 p_ci_cal_type igs_ca_inst.cal_type%TYPE,
97 p_ci_seq_num igs_ca_inst.sequence_number%TYPE,
98 p_base_id igf_aw_award_all.base_id%TYPE,
99 p_alt_rel_code igf_aw_fund_cat_all.alt_rel_code%TYPE)
100 RETURN VARCHAR2
101 AS
102 /*************************************************************
103 Created By : venagara
104 Date Created On : 2000/11/20
105 Purpose :
106 Know limitations, enhancements or remarks
107 Change History
108 Who When What
109 bkkumar 02-04-04 FACR116 - Added the paramter p_alt_rel_code
110 bkkumar 30-sep-2003 FA 122 Loan Enhancements
111 Added p_base_id and call to
112 Pick_setup
113 (reverse chronological order - newest change first)
114 ***************************************************************/
115 l_hold_rel_ind igf_sl_cl_setup.hold_rel_ind%TYPE;
116 l_rel_code igf_sl_cl_setup.relationship_cd%TYPE;
117 l_party_id igf_sl_cl_setup.party_id%TYPE;
118 l_person_id igf_sl_cl_pref_lenders.person_id%TYPE;
119 CURSOR c_clsetup(
120 cp_rel_code igf_sl_cl_setup.relationship_cd%TYPE,
121 cp_party_id igf_sl_cl_setup.party_id%TYPE
122 ) IS
123 SELECT hold_rel_ind FROM igf_sl_cl_setup
124 WHERE ci_cal_type = p_ci_cal_type
125 AND ci_sequence_number = p_ci_seq_num
126 AND NVL(relationship_cd,'*') = cp_rel_code
127 AND NVL(party_id,-1000) = NVL(cp_party_id,-1000);
128 BEGIN
129
130 l_rel_code := NULL;
131 l_party_id := NULL;
132 l_person_id := NULL;
133
134 -- pick the values from the setup base on the base_id
135 igf_sl_award.pick_setup(p_base_id,p_ci_cal_type,p_ci_seq_num,l_rel_code,l_person_id,l_party_id,p_alt_rel_code);
136 g_rel_code := l_rel_code;
137 g_party_id := l_party_id;
138 -- put debug log messages
139 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
140 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_award.get_cl_hold_rel_ind.debug','The value pick_setup returned rel_code: '||l_rel_code);
141 END IF;
142 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
143 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_award.get_cl_hold_rel_ind.debug','The value pick_setup returned party_id: '||l_party_id);
144 END IF;
145
146
147 IF igf_sl_gen.chk_cl_fed_fund_code(p_fed_fund_code) = 'TRUE' THEN
148 OPEN c_clsetup(g_rel_code,g_party_id);
149 FETCH c_clsetup INTO l_hold_rel_ind;
150 CLOSE c_clsetup;
151 END IF;
152
153 RETURN l_hold_rel_ind;
154
155 EXCEPTION WHEN OTHERS THEN
156 IF FND_LOG.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level THEN
157 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_award.get_cl_hold_rel_ind.exception',SQLERRM);
158 END IF;
159 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
160 fnd_message.set_token('NAME','IGF_SL_AWARD.GET_CL_HOLD_REL_IND');
161 igs_ge_msg_stack.conc_exception_hndl;
162 app_exception.raise_exception;
163
164 END get_cl_hold_rel_ind;
165
166 -- Created as part of FA 122 Loans Enhancements
167 FUNCTION get_cl_auto_late_ind(p_fed_fund_code igf_aw_fund_cat.fed_fund_code%TYPE,
168 p_ci_cal_type igs_ca_inst.cal_type%TYPE,
169 p_ci_seq_num igs_ca_inst.sequence_number%TYPE,
170 p_base_id igf_aw_award_all.base_id%TYPE,
171 p_alt_rel_code igf_aw_fund_cat_all.alt_rel_code%TYPE)
172 RETURN VARCHAR2
173 AS
174 /*************************************************************
175 Created By : bkkumar
176 Date Created On : 2003/09/30
177 Purpose :
178 Know limitations, enhancements or remarks
179 Change History
180 Who When What
181 bkkumar 02-04-04 FACR116 - Added the paramter p_alt_rel_code
182 bkkumar 30-sep-2003 FA 122 Loan Enhancements
183 To get the auto_late_disb_ind.
184
185 (reverse chronological order - newest change first)
186 ***************************************************************/
187
188 l_auto_late_disb_ind igf_sl_cl_setup.auto_late_disb_ind%TYPE;
189 l_rel_code igf_sl_cl_setup.relationship_cd%TYPE;
190 l_party_id igf_sl_cl_setup.party_id%TYPE;
191 l_person_id igf_sl_cl_pref_lenders.person_id%TYPE;
192 CURSOR c_clsetup(
193 cp_rel_code igf_sl_cl_setup.relationship_cd%TYPE,
194 cp_party_id igf_sl_cl_setup.party_id%TYPE
195 ) IS
196 SELECT auto_late_disb_ind
197 FROM igf_sl_cl_setup
198 WHERE ci_cal_type = p_ci_cal_type
199 AND ci_sequence_number = p_ci_seq_num
200 AND NVL(relationship_cd,'*') = cp_rel_code
201 AND NVL(party_id,-1000) = NVL(cp_party_id,-1000);
202 BEGIN
203
204 l_rel_code := NULL;
205 l_party_id := NULL;
206 l_person_id := NULL;
207
208 --
209 -- pick the values from the setup base on the base_id
210 --
211
212 igf_sl_award.pick_setup(p_base_id,p_ci_cal_type,p_ci_seq_num,l_rel_code,l_person_id,l_party_id,p_alt_rel_code);
213 g_rel_code := l_rel_code ;
214 g_party_id := l_party_id;
215 --
216 -- put debug log messages
217 --
218 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
219 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_award.get_cl_auto_late_ind.debug','The value pick_setup returned rel_code: '||l_rel_code);
220 END IF;
221 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
222 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_award.get_cl_auto_late_ind.debug','The value pick_setup returned party_id: '||l_party_id);
223 END IF;
224
225 IF igf_sl_gen.chk_cl_fed_fund_code(p_fed_fund_code) = 'TRUE' THEN
226 OPEN c_clsetup(g_rel_code,g_party_id);
227 FETCH c_clsetup INTO l_auto_late_disb_ind;
228 CLOSE c_clsetup;
229 END IF;
230
231 RETURN l_auto_late_disb_ind;
232
233 EXCEPTION WHEN OTHERS THEN
234 IF FND_LOG.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level THEN
235 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_award.get_cl_auto_late_ind.exception',SQLERRM);
236 END IF;
237 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
238 fnd_message.set_token('NAME','IGF_SL_AWARD.GET_CL_AUTO_LATE_IND');
239 igs_ge_msg_stack.conc_exception_hndl;
240 app_exception.raise_exception;
241
242 END get_cl_auto_late_ind;
243
244 FUNCTION get_loan_fee1(p_fed_fund_code igf_aw_fund_cat.fed_fund_code%TYPE,
245 p_ci_cal_type igs_ca_inst.cal_type%TYPE,
246 p_ci_seq_num igs_ca_inst.sequence_number%TYPE,
247 p_base_id igf_aw_award_all.base_id%TYPE,
248 p_rel_code VARCHAR2,
249 p_alt_rel_code igf_aw_fund_cat_all.alt_rel_code%TYPE)
250 RETURN NUMBER
251 AS
252 /*************************************************************
253 Created By : venagara
254 Date Created On : 2000/11/20
255 Purpose :
256 Know limitations, enhancements or remarks
257 Change History
258 Who When What
259 bkkumar 02-04-04 Added the paramter p_alt_rel_code
260 akonatha 08-MAY-2001 Added Functionality to check for alternate loans
261 bkkumar 30-sep-2003 FA 122 Loan Enhancements
262 Added p_base_id and call to
263 Pick_setup
264 (reverse chronological order - newest change first)
265 ***************************************************************/
266
267
268 CURSOR c_clsetup (
269 cp_rel_code igf_sl_cl_setup.relationship_cd%TYPE,
270 cp_party_id igf_sl_cl_setup.party_id%TYPE
271 )
272 IS
273 SELECT est_orig_fee_perct,
274 est_alt_orig_fee_perct
275 FROM igf_sl_cl_setup
276 WHERE ci_cal_type = p_ci_cal_type
277 AND ci_sequence_number = p_ci_seq_num
278 AND NVL(relationship_cd,'*') = cp_rel_code
279 AND NVL(party_id,-1000) = NVL(cp_party_id,-1000);
280
281 l_rel_code igf_sl_cl_setup.relationship_cd%TYPE;
282 l_party_id igf_sl_cl_setup.party_id%TYPE;
283 l_person_id igf_sl_cl_pref_lenders.person_id%TYPE;
284
285 BEGIN
286
287 l_dl_int_rebate := NULL;
288
289 IF igf_sl_gen.chk_dl_fed_fund_code(p_fed_fund_code) = 'TRUE' THEN
290
291 OPEN c_dlsetup(p_ci_cal_type,p_ci_seq_num);
292 FETCH c_dlsetup INTO l_dl_fee1_staf, l_dl_fee1_plus,l_dl_int_rebate;
293 CLOSE c_dlsetup;
294
295 l_dl_int_rebate := NVL(l_dl_int_rebate,0);
296
297 IF igf_sl_gen.chk_dl_stafford(p_fed_fund_code) = 'TRUE' THEN
298 RETURN NVL(l_dl_fee1_staf,0);
299 ELSIF igf_sl_gen.chk_dl_plus(p_fed_fund_code) = 'TRUE' THEN
300 RETURN NVL(l_dl_fee1_plus,0);
301 END IF;
302
303 ELSIF igf_sl_gen.chk_cl_alt(p_fed_fund_code) = 'TRUE' THEN
304
305 l_rel_code := NULL;
306 l_party_id := NULL;
307 l_person_id := NULL;
308 --
309 -- call this only if the p_rel_code param is NULL
310 --
311 IF p_rel_code IS NULL THEN
312 pick_setup(p_base_id,p_ci_cal_type,p_ci_seq_num,l_rel_code,l_person_id,l_party_id,p_alt_rel_code);
313 g_rel_code := l_rel_code;
314 ELSE
315 g_rel_code := p_rel_code;
316 END IF;
317
318 g_party_id := l_party_id;
319
320 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
324 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_award.get_loan_fee1.debug','The value pick_setup returned party_id: '||l_party_id);
321 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_award.get_loan_fee1.debug','The value pick_setup returned rel_code: '||l_rel_code);
322 END IF;
323 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
325 END IF;
326
327 OPEN c_clsetup(g_rel_code,g_party_id);
328 FETCH c_clsetup INTO l_cl_fee1,l_cl_alt_fee1;
329 CLOSE c_clsetup;
330
331 RETURN NVL(l_cl_alt_fee1,0);
332
333 ELSIF igf_sl_gen.chk_cl_fed_fund_code(p_fed_fund_code) = 'TRUE' THEN
334
335 l_rel_code := NULL;
336 l_party_id := NULL;
337 l_person_id := NULL;
338 --
339 -- call this only if the p_rel_code param is NULL
340 --
341 IF p_rel_code IS NULL THEN
342 pick_setup(p_base_id,p_ci_cal_type,p_ci_seq_num,l_rel_code,l_person_id,l_party_id,p_alt_rel_code);
343 g_rel_code := l_rel_code;
344 ELSE
345 g_rel_code := p_rel_code;
346 END IF;
347
348 g_party_id := l_party_id;
349
350 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
351 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_award.get_loan_fee1.debug','The value pick_setup returned rel_code: '||l_rel_code);
352 END IF;
353 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
354 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_award.get_loan_fee1.debug','The value pick_setup returned party_id: '||l_party_id);
355 END IF;
356
357 OPEN c_clsetup(g_rel_code,g_party_id);
358 FETCH c_clsetup INTO l_cl_fee1,l_cl_alt_fee1;
359 CLOSE c_clsetup;
360
361 RETURN NVL(l_cl_fee1,0);
362
363 END IF;
364
365 RETURN 0;
366 EXCEPTION WHEN OTHERS THEN
367 IF FND_LOG.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level THEN
368 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_award.get_loan_fee1.exception',SQLERRM);
369 END IF;
370 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
371 fnd_message.set_token('NAME','IGF_SL_AWARD.GET_LOAN_FEE1');
372 igs_ge_msg_stack.conc_exception_hndl;
373 app_exception.raise_exception;
374
375 END get_loan_fee1;
376
377
378
379 FUNCTION get_loan_fee2(p_fed_fund_code igf_aw_fund_cat.fed_fund_code%TYPE,
380 p_ci_cal_type igs_ca_inst.cal_type%TYPE,
381 p_ci_seq_num igs_ca_inst.sequence_number%TYPE,
382 p_base_id igf_aw_award_all.base_id%TYPE,
383 p_rel_code VARCHAR2,
384 p_alt_rel_code igf_aw_fund_cat_all.alt_rel_code%TYPE)
385 RETURN NUMBER
386 AS
387 /*************************************************************
388 Created By : venagara
389 Date Created On : 2000/11/20
390 Purpose :
391 Know limitations, enhancements or remarks
392 Change History
393 Who When What
394 bkkumar 02-04-04 Added the paramter p_alt_rel_code
395 akonatha 08-MAY-2001 Added Functionality to check for alternate loans
399 (reverse chronological order - newest change first)
396 bkkumar 30-sep-2003 FA 122 Loan Enhancements
397 Added p_base_id and call to
398 Pick_setup
400 ***************************************************************/
401
402 l_cl_fee2 igf_sl_cl_setup.est_guarnt_fee_perct%TYPE;
403 l_cl_alt_fee2 igf_sl_cl_setup.est_alt_orig_fee_perct%TYPE;
404
405 CURSOR c_clsetup (
406 cp_rel_code igf_sl_cl_setup.relationship_cd%TYPE,
407 cp_party_id igf_sl_cl_setup.party_id%TYPE
408 )
409 IS
410 SELECT est_guarnt_fee_perct,
411 est_alt_guarnt_fee_perct
412 FROM igf_sl_cl_setup
413 WHERE ci_cal_type = p_ci_cal_type
414 AND ci_sequence_number = p_ci_seq_num
415 AND NVL(relationship_cd,'*') = cp_rel_code
416 AND NVL(party_id,-1000) = NVL(cp_party_id,-1000);
417
418 l_rel_code igf_sl_cl_setup.relationship_cd%TYPE;
419 l_party_id igf_sl_cl_setup.party_id%TYPE;
420 l_person_id igf_sl_cl_pref_lenders.person_id%TYPE;
421
422 BEGIN
423
424 l_rel_code := NULL;
425 l_party_id := NULL;
426 l_person_id := NULL;
427
428 --
429 -- pick the values from the setup base on the base_id
430 --
431 --
432 -- call this only if the p_rel_code param is NULL
433 --
434
435 IF p_rel_code IS NULL THEN
436 pick_setup(p_base_id,p_ci_cal_type,p_ci_seq_num,l_rel_code,l_person_id,l_party_id,p_alt_rel_code);
437 g_rel_code := l_rel_code;
438 ELSE
439 g_rel_code := p_rel_code;
440 END IF;
441
442 g_party_id := l_party_id;
443 --
444 -- put debug log messages
445 --
446 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
447 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_award.get_loan_fee2.debug','The value pick_setup returned rel_code: '||l_rel_code);
448 END IF;
449 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
450 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_award.get_loan_fee2.debug','The value pick_setup returned party_id: '||l_party_id);
451 END IF;
452
453
454 IF igf_sl_gen.chk_cl_alt(p_fed_fund_code) = 'TRUE' THEN
455
456 OPEN c_clsetup(g_rel_code,g_party_id);
457 FETCH c_clsetup INTO l_cl_fee2,l_cl_alt_fee2;
458 CLOSE c_clsetup;
459 RETURN NVL(l_cl_alt_fee2,0);
460
461 ELSIF igf_sl_gen.chk_cl_fed_fund_code(p_fed_fund_code) = 'TRUE' THEN
462
463 OPEN c_clsetup(g_rel_code,g_party_id);
464 FETCH c_clsetup INTO l_cl_fee2,l_cl_alt_fee2;
465 CLOSE c_clsetup;
466 RETURN NVL(l_cl_fee2,0);
467 END IF;
468 RETURN 0;
469
470 EXCEPTION WHEN OTHERS THEN
471 IF FND_LOG.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level THEN
472 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_award.get_loan_fee2.exception',SQLERRM);
473 END IF;
477 app_exception.raise_exception;
474 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
475 fnd_message.set_token('NAME','IGF_SL_AWARD.GET_LOAN_FEE2');
476 igs_ge_msg_stack.conc_exception_hndl;
478 END get_loan_fee2;
479
480
481 FUNCTION chk_disb_date(p_award_id igf_sl_loans.award_id%TYPE,
482 p_loan_per_begin_date igf_sl_loans_all.loan_per_begin_date%TYPE,
483 p_loan_per_end_date igf_sl_loans_all.loan_per_end_date%TYPE
484 )
485 RETURN VARCHAR2 AS
486 /* -------------------------------------------------------------
487 || Created By :
488 || Created On :
489 || Purpose :
490 || Known limitations, enhancements or remarks :
491 || Change History :
492 || Who When What
493 || (reverse chronological order - newest change first)
494 || smadathi 14-OCT-2004 Bug 3416936.Changes as per TD
495 ------------------------------------------------------------------*/
496
497 CURSOR c_loans (cp_n_award_id igf_aw_award_all.award_id%TYPE,
498 cp_loan_per_begin_date igf_sl_loans_all.loan_per_begin_date%TYPE,
499 cp_loan_per_end_date igf_sl_loans_all.loan_per_end_date%TYPE) IS
500 SELECT disb.award_id
501 ,disb.disb_date
502 ,disb.disb_num
503 FROM igf_aw_awd_disb_all disb
504 ,igf_sl_loans_all loans
505 WHERE loans.award_id=disb.award_id
506 AND loans.award_id = cp_n_award_id
507 AND ( disb.disb_date < NVL(cp_loan_per_begin_date, loans.loan_per_begin_date)
508 OR disb.disb_date > NVL(cp_loan_per_end_date, loans.loan_per_end_date));
509
510 l_v_disb_num_desc igs_lookup_values.meaning%TYPE;
511 l_v_disb_date_desc igs_lookup_values.meaning%TYPE;
512 l_v_return_val VARCHAR2(4000);
513 BEGIN
514 l_v_return_val := NULL;
515 FOR rec_c_loans IN c_loans (cp_n_award_id => p_award_id,
516 cp_loan_per_begin_date => p_loan_per_begin_date,
517 cp_loan_per_end_date => p_loan_per_end_date)
518 LOOP
519 l_v_return_val := l_v_return_val ||l_v_disb_num_desc || ' '|| rec_c_loans.disb_num ||' ';
520 l_v_return_val := l_v_return_val ||l_v_disb_date_desc || ' '|| rec_c_loans.disb_date ;
521 END LOOP;
522
523 RETURN l_v_return_val;
524
525 EXCEPTION
526 WHEN OTHERS THEN
527 IF FND_LOG.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level THEN
528 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_award.chk_disb_date.exception',SQLERRM);
529 END IF;
530 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
531 fnd_message.set_token('NAME','IGF_SL_AWARD.CHK_DISB_DATE');
532 igs_ge_msg_stack.conc_exception_hndl;
533 app_exception.raise_exception;
534
535 END chk_disb_date;
536
537 -- FACR116 This function returns the alt_rel_code for the passed fund_Code.
538 FUNCTION get_alt_rel_code(p_fund_code igf_aw_fund_cat_all.fund_code%TYPE)
539 RETURN VARCHAR2
540 AS
541 l_alt_rel_code igf_aw_fund_cat_all.alt_rel_code%TYPE;
542
543 CURSOR c_rel_code (cp_fund_code igf_aw_fund_cat_all.fund_code%TYPE)
544 IS
545 SELECT alt_rel_code
546 FROM igf_aw_fund_cat_all
547 WHERE fund_code = cp_fund_code;
548
549 BEGIN
550
551 l_alt_rel_code := NULL;
552 OPEN c_rel_code(p_fund_code);
553 FETCH c_rel_code INTO l_alt_rel_code;
554 CLOSE c_rel_code;
555
556 RETURN l_alt_rel_code;
557 EXCEPTION WHEN OTHERS THEN
558 IF FND_LOG.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level THEN
559 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_award.get_alt_rel_code.exception',SQLERRM);
560 END IF;
561 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
562 fnd_message.set_token('NAME','IGF_SL_AWARD.get_alt_rel_code');
563 igs_ge_msg_stack.conc_exception_hndl;
564 app_exception.raise_exception;
565
566 END get_alt_rel_code;
567
568 FUNCTION chk_loan_upd_lock(p_award_id igf_sl_loans.award_id%TYPE)
569 RETURN VARCHAR2
570 AS
571 /* -------------------------------------------------------------
572 || Created By :
573 || Created On :
574 || Purpose :
575 || Known limitations, enhancements or remarks :
576 || Change History :
577 || Who When What
578 || (reverse chronological order - newest change first)
579 || smadathi 14-OCT-2004 Bug 3416936.Changes as per TD
580 ------------------------------------------------------------------*/
581 l_loan_id igf_sl_loans.loan_id%TYPE;
582 l_loan_status igf_sl_loans.loan_status%TYPE;
583 l_loan_chg_status igf_sl_loans.loan_chg_status%TYPE;
584
585 l_get_fed_fund_code c_get_fed_fund_code%ROWTYPE;
586
587 CURSOR c_loans (p_award_id NUMBER)IS
588 SELECT loan_id
589 ,loan_status
590 ,loan_chg_status
591 FROM igf_sl_loans
592 WHERE award_id = p_award_id;
593
594 l_n_cl_version igf_sl_cl_setup_all.cl_version%TYPE;
595
596 BEGIN
597
598 -- If Loan Application Record is Created, then Check whether the
599 -- Loan Status or Loan Change Status is SENT. If SENT, then should
600 -- not allow to update anything in Awards Table. So, returning
604 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_award.chk_loan_upd_lock.Award_id', p_award_id);
601 -- FALSE, saying do not allow update to awards.
602
603 IF FND_LOG.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level THEN
605 END IF;
606
607 OPEN c_loans(p_award_id);
608 FETCH c_loans INTO l_loan_id, l_loan_status, l_loan_chg_status;
609 CLOSE c_loans;
610
611 IF l_loan_id IS NOT NULL THEN
612
613 -- get the loan version for the input award id
614 l_n_cl_version := igf_sl_award.get_loan_cl_version(p_n_award_id => p_award_id);
615
616 -- FA 122 Loan Enhancements BKKUMAR 30-SEP-2003
617 -- first get the fund code to see if it is a DL Record or FFELP Loan Record
618 l_get_fed_fund_code := NULL;
619 OPEN c_get_fed_fund_code(p_award_id);
620 FETCH c_get_fed_fund_code INTO l_get_fed_fund_code;
621 CLOSE c_get_fed_fund_code;
622 IF igf_sl_gen.chk_dl_fed_fund_code(l_get_fed_fund_code.fed_fund_code) = 'TRUE' THEN
623 IF l_loan_status = 'S' OR NVL(l_loan_chg_status,'*') = 'S' THEN
624 RETURN 'TRUE';
625 END IF;
626 RETURN 'FALSE';
627 -- for FFELP loans
628 ELSIF igf_sl_gen.chk_cl_fed_fund_code(l_get_fed_fund_code.fed_fund_code) = 'TRUE' THEN
629 -- if the common line release version is 'RELEASE-4' and either of loan
630 -- status or loan change status in Sent or cancelled, NO DML operation should
631 -- be allowed
632 -- if the common line release version is 'RELEASE-5' and loan status
633 -- Sent or cancelled, NO DML operation should be allowed
634 -- Bug #4059136 Allow DML operations if loan status is accepted
635 IF (l_n_cl_version = 'RELEASE-5') THEN
636 IF (l_loan_status IN ('S','C')) THEN
637 RETURN 'TRUE';
638 END IF;
639 RETURN 'FALSE';
640 END IF;
641 IF (l_n_cl_version = 'RELEASE-4') THEN
642 IF (((l_loan_status = 'S') OR (NVL (l_loan_chg_status,'*') = 'S')) OR
643 ((l_loan_status = 'C') OR (NVL(l_loan_chg_status,'*') = 'C'))) THEN
644 RETURN 'TRUE';
645 END IF;
646 RETURN 'FALSE';
647 END IF;
648 END IF;
649 END IF;
650 RETURN 'FALSE';
651 EXCEPTION WHEN OTHERS THEN
652 IF FND_LOG.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level THEN
653 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_award.chk_loan_upd_lock.exception',SQLERRM);
654 END IF;
655 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
656 fnd_message.set_token('NAME','IGF_SL_AWARD.CHK_LOAN_UPD_LOCK');
657 igs_ge_msg_stack.conc_exception_hndl;
658 app_exception.raise_exception;
659
660 END chk_loan_upd_lock;
661
662 PROCEDURE get_loan_amts(p_ci_cal_type IN igs_ca_inst_all.cal_type%TYPE,
663 p_ci_seq_num IN igs_ca_inst_all.sequence_number%TYPE,
664 p_fed_fund_code IN igf_aw_fund_cat_all.fed_fund_code%TYPE,
665 p_gross_amt IN igf_aw_awd_disb_all.disb_gross_amt%TYPE,
666 p_rebate_amt OUT NOCOPY igf_aw_awd_disb_all.int_rebate_amt%TYPE,
667 p_loan_fee_amt OUT NOCOPY igf_aw_awd_disb_all.fee_1%TYPE,
668 p_net_amt OUT NOCOPY igf_aw_awd_disb_all.disb_net_amt%TYPE)
669 IS
670 -----------------------------------------------------------------------------------
671 --
672 -- sjadhav, Jan 23,2002
673 -- This procedure calculates loan fee amount, interest rebate amount
674 -- combined fee int rebate anount and disb net amonut for Direct Loans
675 -- This net amount does not include the Fee Paid
676 -----------------------------------------------------------------------------------
677
678
679 ln_comb_int_pct igf_sl_dl_setup_all.int_rebate%TYPE;
680 ln_comb_int_amt igf_aw_awd_disb_all.disb_net_amt%TYPE;
681
682 BEGIN
683
684 --
685 -- 1. Get Combined Fee/Int Reb Pctg and Amt
686 --
687
688 ln_comb_int_pct := get_loan_fee1 (p_fed_fund_code,p_ci_cal_type,p_ci_seq_num) / 100 -
689 l_dl_int_rebate / 100;
690
691 ln_comb_int_amt := TRUNC(ln_comb_int_pct * p_gross_amt);
692
693
694 --
695 -- 2.Get Net Disb Amount
696 --
697
698 p_net_amt := p_gross_amt - ln_comb_int_amt;
699
700
701 --
702 -- 3. Get Loan Fee Amount
706
703 --
704
705 p_loan_fee_amt := TRUNC (p_gross_amt * get_loan_fee1 (p_fed_fund_code,p_ci_cal_type,p_ci_seq_num) / 100 );
707 --
708 -- 4. Get Int Rebate Amount
709 --
710 p_rebate_amt := p_net_amt - ( p_gross_amt - p_loan_fee_amt);
711
712 EXCEPTION WHEN OTHERS THEN
713 IF FND_LOG.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level THEN
714 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_award.get_loan_amts.exception',SQLERRM);
715 END IF;
716 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
717 fnd_message.set_token('NAME','IGF_SL_AWARD.GET_LOAN_AMTS');
718 igs_ge_msg_stack.conc_exception_hndl;
719 app_exception.raise_exception;
720
721
722 END get_loan_amts;
723
724 PROCEDURE recalc_fees(
725 p_base_id IN igf_aw_award_all.base_id%TYPE,
726 p_cal_type IN igs_ca_inst_all.cal_type%TYPE,
727 p_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
728 p_rel_code IN igf_sl_cl_setup.relationship_cd%TYPE,
729 p_award_id IN igf_sl_loans.award_id%TYPE
730 )
731 IS
732 /*************************************************************
733 Created By : bkkumar
734 Date Created On : 05-Sep-2003
735 Purpose : FA 122 Loans Enhancements
736 Know limitations, enhancements or remarks
737 Change History
738 Who When What
739 museshad 20-Sep-2005 Bug 3943742.
740 When the Preferred lender relationship code
741 has an override, it was not being considered.
742 Fixed this by passing NULL (instead of the
743 derived rel code) for p_rel_code to get_loan_fee1()
744 and get_loan_fee2(). To get the override relationship
745 code details from setup, the party_id of the Org Unit
746 is needed. This does not get set if the relationship code
747 is passed to get_loan_fee1() and get_loan_fee2(). Both
748 get_loan_fee1() and get_loan_fee2() make an inherent
749 call to pick_setup() to arrive at the correct rel code
750 and party_id.
751 bkkumar 02-04-04 FACR116 Added the paramter to the pick_setup routine.
752 bkkumar 30-sep-2003 FA 122 Loans Enhancements
753 This is to recalculate teh fees
754 based on the setup choosen for the
755 student
756 veramach 1-NOV-2003 FA 125 Multiple Distr Methods
757 Changed calll to igf_aw_awd_disb_pkg.update_row to reflect the addition of attendance_type_code
758 (reverse chronological order - newest change first)
759 ***************************************************************/
760
761 l_rel_code igf_sl_cl_setup.relationship_cd%TYPE;
762 l_party_id igf_sl_cl_setup.party_id%TYPE;
763 l_person_id igf_sl_cl_pref_lenders.person_id%TYPE;
764
765 CURSOR cur_get_adisb (cp_award_id igf_sl_loans.award_id%TYPE)
766 IS
767 SELECT *
768 FROM igf_aw_awd_disb adisb
769 WHERE adisb.award_id = p_award_id;
770
771 CURSOR c_get_alt_code (cp_award_id igf_aw_award_all.award_id%TYPE)
772 IS
773 select
774 fcat.alt_rel_code alt_rel_code
775 from
776 igf_aw_award_all awd,
777 igf_aw_fund_mast_all fmast,
778 igf_aw_fund_cat_all fcat
779 where
780 awd.fund_id = fmast.fund_id
781 and fmast.fund_code = fcat.fund_code
782 and awd.award_id = cp_award_id ;
783
784
785 get_adisb_rec cur_get_adisb%ROWTYPE;
786 l_get_fed_fund_code c_get_fed_fund_code%ROWTYPE;
787 l_fee1 igf_aw_awd_disb.fee_1%TYPE;
788 l_fee2 igf_aw_awd_disb.fee_2%TYPE;
789 l_net_amt igf_aw_awd_disb.disb_net_amt%TYPE;
790 l_alt_rel_code igf_aw_fund_cat_all.alt_rel_code%TYPE;
791 BEGIN
792
793 l_rel_code := NULL;
794 l_party_id := NULL;
795 l_person_id := NULL;
796 l_alt_rel_code := NULL;
797
798 OPEN c_get_alt_code(p_award_id);
799 FETCH c_get_alt_code INTO l_alt_rel_code;
800 CLOSE c_get_alt_code;
801
802 -- pick the values from the setup base on the base_id
803 igf_sl_award.pick_setup(p_base_id,p_cal_type,p_sequence_number,l_rel_code,l_person_id,l_party_id,l_alt_rel_code);
804 IF l_rel_code = p_rel_code THEN
805 g_party_id := l_party_id;
806 ELSE
807 g_party_id := NULL;
808 END IF;
809 -- put debug log messages
810 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
811 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_award.recalc_fees.debug','The value pick_setup returned rel_code: '||l_rel_code);
812 END IF;
813 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
814 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_award.recalc_fees.debug','The value pick_setup returned party_id: '||l_party_id);
815 END IF;
816
820 OPEN c_get_fed_fund_code(p_award_id);
817 g_rel_code := p_rel_code;
818
819 l_get_fed_fund_code := NULL;
821 FETCH c_get_fed_fund_code INTO l_get_fed_fund_code;
822 CLOSE c_get_fed_fund_code;
823
824 -- museshad (Bug 3943742). Passed NULL for p_rel_code in both get_loan_fee1() and get_loan_fee2()
825 -- get the fee1 and fee2 for the setup
826 l_fee1 := igf_sl_award.get_loan_fee1(
827 p_fed_fund_code => l_get_fed_fund_code.fed_fund_code,
828 p_ci_cal_type => p_cal_type,
829 p_ci_seq_num => p_sequence_number,
830 p_base_id => p_base_id,
831 p_rel_code => NULL,
832 p_alt_rel_code => NULL
833 );
834
835 l_fee2 := igf_sl_award.get_loan_fee2(
836 p_fed_fund_code => l_get_fed_fund_code.fed_fund_code,
837 p_ci_cal_type => p_cal_type,
838 p_ci_seq_num => p_sequence_number,
839 p_base_id => p_base_id,
840 p_rel_code => NULL,
841 p_alt_rel_code => NULL
842 );
843
844 get_adisb_rec := NULL;
845
846
847 FOR get_adisb_rec IN cur_get_adisb(p_award_id) LOOP
848
849 -- first calculate the net amount
850 IF NVL(get_adisb_rec.disb_accepted_amt,0) = 0 THEN
851 l_net_amt := 0;
852 ELSE
853 l_net_amt := NVL(get_adisb_rec.disb_accepted_amt,0) - (l_fee1 * NVL(get_adisb_rec.disb_accepted_amt,0)) / 100
854 - (l_fee2 * NVL(get_adisb_rec.disb_accepted_amt,0)) / 100 + NVL(get_adisb_rec.fee_paid_1,0)
855 + NVL(get_adisb_rec.fee_paid_2,0);
856
857 END IF;
858 -- update the amounts and the fees as calculated above
859 igf_aw_awd_disb_pkg.update_row( x_rowid => get_adisb_rec.row_id ,
860 x_award_id => get_adisb_rec.award_id ,
861 x_disb_num => get_adisb_rec.disb_num ,
862 x_tp_cal_type => get_adisb_rec.tp_cal_type ,
863 x_tp_sequence_number => get_adisb_rec.tp_sequence_number ,
864 x_disb_gross_amt => get_adisb_rec.disb_gross_amt ,
865 x_fee_1 => (l_fee1 * NVL(get_adisb_rec.disb_accepted_amt,0)) / 100,
866 x_fee_2 => (l_fee2 * NVL(get_adisb_rec.disb_accepted_amt,0)) / 100,
867 x_disb_net_amt => l_net_amt,
868 x_disb_date => get_adisb_rec.disb_date ,
869 x_trans_type => get_adisb_rec.trans_type ,
870 x_elig_status => get_adisb_rec.elig_status ,
871 x_elig_status_date => get_adisb_rec.elig_status_date ,
872 x_affirm_flag => get_adisb_rec.affirm_flag ,
873 x_hold_rel_ind => get_adisb_rec.hold_rel_ind ,
874 x_manual_hold_ind => get_adisb_rec.manual_hold_ind ,
875 x_disb_status => get_adisb_rec.disb_status ,
876 x_disb_status_date => get_adisb_rec.disb_status_date ,
877 x_late_disb_ind => get_adisb_rec.late_disb_ind ,
878 x_fund_dist_mthd => get_adisb_rec.fund_dist_mthd ,
879 x_prev_reported_ind => get_adisb_rec.prev_reported_ind ,
880 x_fund_release_date => get_adisb_rec.fund_release_date ,
881 x_fund_status => get_adisb_rec.fund_status ,
882 x_fund_status_date => get_adisb_rec.fund_status_date ,
883 x_fee_paid_1 => get_adisb_rec.fee_paid_1 ,
884 x_fee_paid_2 => get_adisb_rec.fee_paid_2 ,
885 x_cheque_number => get_adisb_rec.cheque_number ,
886 x_ld_cal_type => get_adisb_rec.ld_cal_type ,
887 x_ld_sequence_number => get_adisb_rec.ld_sequence_number ,
888 x_disb_accepted_amt => get_adisb_rec.disb_accepted_amt ,
889 x_disb_paid_amt => get_adisb_rec.disb_paid_amt ,
890 x_rvsn_id => get_adisb_rec.rvsn_id ,
891 x_int_rebate_amt => get_adisb_rec.int_rebate_amt ,
892 x_force_disb => get_adisb_rec.force_disb ,
893 x_min_credit_pts => get_adisb_rec.min_credit_pts ,
894 x_disb_exp_dt => get_adisb_rec.disb_exp_dt ,
895 x_verf_enfr_dt => get_adisb_rec.verf_enfr_dt ,
896 x_fee_class => get_adisb_rec.fee_class ,
897 x_show_on_bill => get_adisb_rec.show_on_bill ,
901 x_payment_prd_st_date => get_adisb_rec.payment_prd_st_date,
898 x_mode => 'R',
899 x_attendance_type_code => get_adisb_rec.attendance_type_code,
900 x_base_attendance_type_code => get_adisb_rec.base_attendance_type_code,
902 x_change_type_code => get_adisb_rec.change_type_code,
903 x_fund_return_mthd_code => get_adisb_rec.fund_return_mthd_code,
904 x_direct_to_borr_flag => get_adisb_rec.direct_to_borr_flag
905 );
906
907 END LOOP;
908
909 EXCEPTION WHEN OTHERS THEN
910 IF FND_LOG.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level THEN
911 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_award.recalc_fees.exception',SQLERRM);
912 END IF;
913 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
914 fnd_message.set_token('NAME','IGF_SL_AWARD.RECALC_FEES');
915 igs_ge_msg_stack.conc_exception_hndl;
916 app_exception.raise_exception;
917
918 END recalc_fees;
919
920 PROCEDURE pick_setup(
921 p_base_id IN igf_aw_award_all.base_id%TYPE,
922 p_cal_type IN igs_ca_inst_all.cal_type%TYPE,
923 p_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
924 p_rel_code OUT NOCOPY igf_sl_cl_setup.relationship_cd%TYPE,
925 p_person_id OUT NOCOPY igf_sl_cl_pref_lenders.person_id%TYPE,
926 p_party_id OUT NOCOPY igf_sl_cl_setup.party_id%TYPE, -- this is used in FA 126
927 p_alt_rel_code IN igf_aw_fund_cat_all.alt_rel_code%TYPE )
928 IS
929 /*************************************************************
930 Created By : bkkumar
931 Date Created On : 05-Sep-2003
932 Purpose : FA 122 Loans Enhancements
933 Know limitations, enhancements or remarks
934 Change History
935 Who When What
936 bkkumar 02-Apr-04 FACR116 Added a new paramter p_alt_rel_code
937 which will change check for the lender set up for teh p_alt_rel_code
938 in case of teh 'ALT' loans instead of the preffered lender setup.
939 veramach 12-Nov-2003 Changes to c_chk_pref_lender cursor
940 bkkumar 30-sep-2003 FA 122 Loans Enhancemnts
941 This picks up the set up
942 applicable to the particular setup
943 based on teh preferred lender setup
944 (reverse chronological order - newest change first)
945 ***************************************************************/
946 CURSOR c_get_details (
947 cp_base_id igf_aw_award_all.base_id%TYPE
948 )
949 IS
950 SELECT person_id
951 FROM igf_ap_fa_base_rec_all
952 WHERE base_id = cp_base_id;
953
954 l_get_details c_get_details%ROWTYPE;
955
956
957 CURSOR c_chk_pref_lender (
958 cp_person_id igf_sl_cl_pref_lenders.person_id%TYPE
959 )
960 IS
961 SELECT relationship_cd
962 FROM igf_sl_cl_pref_lenders
963 WHERE person_id = cp_person_id
964 AND SYSDATE BETWEEN start_date AND NVL(end_date,SYSDATE);
965
966 l_chk_pref_lender c_chk_pref_lender%ROWTYPE;
967
968 CURSOR c_get_default_lender (
969 cp_cal_type igs_ca_inst_all.cal_type%TYPE,
970 cp_sequence_number igs_ca_inst_all.sequence_number%TYPE,
971 cp_default_flag igf_sl_cl_setup.default_flag%TYPE
972 )
973 IS
974 SELECT relationship_cd
975 FROM igf_sl_cl_setup
976 WHERE ci_cal_type = cp_cal_type
977 AND ci_sequence_number = cp_sequence_number
978 AND NVL(default_flag,'N') = cp_default_flag
979 AND party_id IS NULL;
980
981 l_get_default_lender c_get_default_lender%ROWTYPE;
982
983 CURSOR c_get_ovrd_lender (
984 cp_cal_type igs_ca_inst_all.cal_type%TYPE,
985 cp_sequence_number igs_ca_inst_all.sequence_number%TYPE,
986 cp_rel_code igf_sl_cl_setup.relationship_cd%TYPE,
987 cp_party_id igf_sl_cl_setup.party_id%TYPE
988 )
989 IS
990 SELECT relationship_cd
991 FROM igf_sl_cl_setup
992 WHERE ci_cal_type = cp_cal_type
993 AND ci_sequence_number = cp_sequence_number
997 l_get_ovrd_lender c_get_ovrd_lender%ROWTYPE;
994 AND cp_rel_code = NVL(relationship_cd,'*')
995 AND cp_party_id = NVL(party_id,-1000);
996
998
999 CURSOR c_get_ovrd_default_lender (
1000 cp_cal_type igs_ca_inst_all.cal_type%TYPE,
1001 cp_sequence_number igs_ca_inst_all.sequence_number%TYPE,
1002 cp_party_id igf_sl_cl_setup.party_id%TYPE,
1003 cp_default_flag igf_sl_cl_setup.default_flag%TYPE
1004 )
1005 IS
1006 SELECT relationship_cd
1007 FROM igf_sl_cl_setup
1008 WHERE ci_cal_type = cp_cal_type
1009 AND ci_sequence_number = cp_sequence_number
1010 AND NVL(default_flag,'N') = cp_default_flag
1011 AND NVL(party_id,-1000) = cp_party_id;
1012
1013 l_get_ovrd_default_lender c_get_ovrd_default_lender%ROWTYPE;
1014
1015
1016 CURSOR cur_find_lender (
1017 cp_cal_type igs_ca_inst_all.cal_type%TYPE,
1018 cp_sequence_number igs_ca_inst_all.sequence_number%TYPE,
1019 cp_rel_code igf_sl_cl_setup.relationship_cd%TYPE
1020 )
1021 IS
1022 SELECT relationship_cd
1023 FROM igf_sl_cl_setup
1024 WHERE ci_cal_type = cp_cal_type
1025 AND ci_sequence_number = cp_sequence_number
1026 AND cp_rel_code = relationship_cd
1027 AND party_id IS NULL;
1028
1029 find_lender_rec cur_find_lender%ROWTYPE;
1030
1031 lv_party_number hz_parties.party_number%TYPE;
1032 lv_module VARCHAR2(2);
1033 lv_return_status VARCHAR2(1);
1034 lv_msg_data fnd_new_messages.message_name%TYPE;
1035
1036 BEGIN
1037
1038 g_base_id := p_base_id;
1039 igf_sl_gen.get_associated_org(g_base_id, lv_party_number, g_party_id, lv_module, lv_return_status, lv_msg_data);
1040
1041 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1042 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_award.pick_setup.debug','The value select_org returned : '||g_party_id);
1043 END IF;
1044 --FACR116
1045 -- If the alt_rel_code passed is NOT NULL means it has to be done for the Alternative Loans
1046 IF p_alt_rel_code IS NOT NULL THEN
1047 g_rel_code := p_alt_rel_code;
1048 IF g_party_id IS NOT NULL THEN
1049
1050 l_get_ovrd_lender := NULL;
1051 OPEN c_get_ovrd_lender(p_cal_type,p_sequence_number,g_rel_code,g_party_id);
1052 FETCH c_get_ovrd_lender INTO l_get_ovrd_lender;
1053 CLOSE c_get_ovrd_lender;
1054
1055 IF l_get_ovrd_lender.relationship_cd IS NULL THEN
1056
1057 --
1058 -- This means there is no override for the party
1059 -- then check if the relationship code setup exists
1060 --
1061
1062 OPEN cur_find_lender(p_cal_type,p_sequence_number,g_rel_code);
1066 IF find_lender_rec.relationship_cd IS NULL THEN
1063 FETCH cur_find_lender INTO find_lender_rec;
1064 CLOSE cur_find_lender;
1065
1067 g_rel_code := NULL;
1068 END IF;
1069 g_party_id := NULL;
1070 END IF;
1071 ELSE -- party_id is NULL
1072 OPEN cur_find_lender(p_cal_type,p_sequence_number,g_rel_code);
1073 FETCH cur_find_lender INTO find_lender_rec;
1074 CLOSE cur_find_lender;
1075 IF find_lender_rec.relationship_cd IS NULL THEN
1076 g_rel_code := NULL;
1077 END IF;
1078
1079 END IF; -- party_id NOT NULL
1080
1081 ELSE -- the Loan in consideration is not a ALTERNATIVE loan
1082
1083 l_get_default_lender := NULL;
1084 OPEN c_get_default_lender(p_cal_type,p_sequence_number,'Y');
1085 FETCH c_get_default_lender INTO l_get_default_lender;
1086 CLOSE c_get_default_lender;
1087
1088 -- get the person id of this person from the igf_ap_fa_base_rec_all;
1089
1090 l_get_details := NULL;
1091 OPEN c_get_details(g_base_id);
1092 FETCH c_get_details INTO l_get_details;
1093 CLOSE c_get_details;
1094
1095 p_person_id := l_get_details.person_id;
1096
1097 l_chk_pref_lender := NULL;
1098 OPEN c_chk_pref_lender(p_person_id);
1099 FETCH c_chk_pref_lender INTO l_chk_pref_lender;
1100 CLOSE c_chk_pref_lender;
1101
1102 IF g_party_id IS NOT NULL THEN
1103 --
1104 -- this is implemented for FA126
1105 -- if the person has a preferred lender then assign g_rel_code to this value
1106 --
1107
1108 IF l_chk_pref_lender.relationship_cd IS NOT NULL THEN
1109
1110 g_rel_code := l_chk_pref_lender.relationship_cd;
1111
1112 l_get_ovrd_lender := NULL;
1113 OPEN c_get_ovrd_lender(p_cal_type,p_sequence_number,g_rel_code,g_party_id);
1114 FETCH c_get_ovrd_lender INTO l_get_ovrd_lender;
1115 CLOSE c_get_ovrd_lender;
1116
1117 IF l_get_ovrd_lender.relationship_cd IS NULL THEN
1118
1119 --
1120 -- This means there is no override for the party
1121 -- then check if the relationship code setup exists
1122 --
1123
1124 OPEN cur_find_lender(p_cal_type,p_sequence_number,g_rel_code);
1125 FETCH cur_find_lender INTO find_lender_rec;
1126 CLOSE cur_find_lender;
1127
1128 IF find_lender_rec.relationship_cd IS NULL THEN
1129 --bug #3847105 - Log message in case the Loan is created using Default Lender setup
1130 --despite of Preferred Lender as the Preferred Lender is not setup for the Award Year.
1131 fnd_message.set_name('IGF','IGF_SL_DFLT_LEN_LOAN');
1132 fnd_message.set_token('DEFAULT_LEN',l_get_default_lender.relationship_cd);
1133 fnd_message.set_token('PREFER_LEN',l_chk_pref_lender.relationship_cd);
1134 fnd_message.set_token('AWARD_YEAR',igf_gr_gen.get_alt_code(p_cal_type,p_sequence_number));
1135 fnd_file.put_line(fnd_file.log, fnd_message.get);
1136
1137 g_rel_code := l_get_default_lender.relationship_cd;
1138 END IF;
1139
1140 g_party_id := NULL;
1141
1142 END IF;
1143
1144 ELSE
1145 -- if it does not have a preferred lender check for default lender in override form
1146 l_get_ovrd_default_lender := NULL;
1147
1148 OPEN c_get_ovrd_default_lender(p_cal_type,p_sequence_number,g_party_id,'Y');
1149 FETCH c_get_ovrd_default_lender INTO l_get_ovrd_default_lender;
1150 CLOSE c_get_ovrd_default_lender;
1151
1152 IF l_get_ovrd_default_lender.relationship_cd IS NOT NULL THEN
1153
1154 g_rel_code := l_get_ovrd_default_lender.relationship_cd;
1155
1156 ELSE
1157 -- get the default setup
1158 g_rel_code := l_get_default_lender.relationship_cd;
1159 -- check for the override for this lender
1160 l_get_ovrd_lender := NULL;
1161 OPEN c_get_ovrd_lender(p_cal_type,p_sequence_number,g_rel_code,g_party_id);
1162 FETCH c_get_ovrd_lender INTO l_get_ovrd_lender;
1163 CLOSE c_get_ovrd_lender;
1164
1165 IF l_get_ovrd_lender.relationship_cd IS NULL THEN
1166 g_party_id := NULL;
1167 END IF;
1168
1169 END IF;
1170
1171 END IF;
1172
1173 ELSE
1174 --
1175 -- if the party_id is NULL then no organization linked to the student
1176 --
1177 IF l_chk_pref_lender.relationship_cd IS NOT NULL THEN
1178
1179 g_rel_code := l_chk_pref_lender.relationship_cd;
1180 --
1181 -- If there is no record in the setup table
1182 -- for this rel code then use the default lender
1183 -- for the award year
1184 --
1185 OPEN cur_find_lender(p_cal_type,p_sequence_number,g_rel_code);
1186 FETCH cur_find_lender INTO find_lender_rec;
1187 CLOSE cur_find_lender;
1188
1189 IF find_lender_rec.relationship_cd IS NULL THEN
1190 --bug #3847105 - Log message in case the Loan is created using Default Lender setup
1191 --despite of Preferred Lender as the Preferred Lender is not setup for the Award Year.
1192 fnd_message.set_name('IGF','IGF_SL_DFLT_LEN_LOAN');
1193 fnd_message.set_token('DEFAULT_LEN',l_get_default_lender.relationship_cd);
1197 fnd_file.put_line(fnd_file.log, fnd_message.get);
1194 fnd_message.set_token('PREFER_LEN',l_chk_pref_lender.relationship_cd);
1195 fnd_message.set_token('AWARD_YEAR',igf_gr_gen.get_alt_code(p_cal_type,p_sequence_number));
1196 g_rel_code := l_get_default_lender.relationship_cd;
1198 END IF;
1199
1200 ELSE
1201 g_rel_code := l_get_default_lender.relationship_cd;
1202 END IF;
1203
1204 END IF;
1205 END IF;
1206 p_rel_code := g_rel_code;
1207 p_party_id := g_party_id;
1208
1209 EXCEPTION WHEN OTHERS THEN
1210 IF FND_LOG.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level THEN
1211 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_award.pick_setup.exception',SQLERRM);
1212 END IF;
1213 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1214 fnd_message.set_token('NAME','IGF_SL_AWARD.PICK_SETUP');
1215 igs_ge_msg_stack.conc_exception_hndl;
1216 app_exception.raise_exception;
1217
1218 END pick_setup;
1219
1220 FUNCTION chk_chg_enable (p_n_award_id igf_aw_award_all.award_id%TYPE)
1221 RETURN BOOLEAN AS
1222 ------------------------------------------------------------------
1223 --Created by : Sanil Madathil, Oracle IDC
1224 --Date created: 14 October 2004
1225 --
1226 -- Purpose : Generic Function
1227 -- Invoked :
1228 -- Function :
1229 --
1230 -- Parameters : p_n_award_id : IN parameter. Required.
1231 --
1232 --
1233 --Known limitations/enhancements and/or remarks:
1234 --
1235 --Change History:
1236 --Who When What
1237 ------------------------------------------------------------------
1238 CURSOR c_chk_chg_enable (cp_n_award_id igf_aw_award_all.award_id%TYPE) IS
1239 SELECT loans.loan_number
1240 FROM igf_sl_lor_all lor
1241 ,igf_sl_loans_all loans
1242 ,igf_aw_award_all awd
1243 ,igf_aw_fund_mast_all fmast
1244 ,igf_sl_cl_setup_all clset
1245 WHERE loans.loan_id = lor.loan_id
1246 AND loans.award_id = cp_n_award_id
1247 AND lor.prc_type_code IN ('GO','GP')
1248 AND ( lor.guarnt_status_code = '40' OR lor.cl_rec_status IN ('B','G'))
1249 AND loans.loan_status = 'A'
1250 AND NVL (loans.loan_chg_status,'*') <> 'S'
1251 AND awd.award_id = loans.award_id
1252 AND fmast.fund_id = awd.fund_id
1253 AND fmast.ci_cal_type = clset.ci_cal_type
1254 AND fmast.ci_sequence_number = clset.ci_sequence_number
1255 AND lor.relationship_cd = clset.relationship_cd
1256 AND clset.cl_version = 'RELEASE-4';
1257
1258 l_v_loan_number igf_sl_loans_all.loan_number%TYPE;
1259 BEGIN
1260 OPEN c_chk_chg_enable (cp_n_award_id => p_n_award_id);
1261 FETCH c_chk_chg_enable INTO l_v_loan_number;
1262 IF c_chk_chg_enable%NOTFOUND THEN
1263 CLOSE c_chk_chg_enable;
1264 RETURN FALSE;
1265 END IF;
1266 CLOSE c_chk_chg_enable;
1267 RETURN TRUE;
1268 EXCEPTION
1269 WHEN OTHERS THEN
1270 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
1271 fnd_log.string(fnd_log.level_exception,'igf_sl_award.chk_chg_enable exception',SQLERRM);
1272 END IF;
1273 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1274 fnd_message.set_token('NAME','igf_sl_award.chk_chg_enable');
1275 igs_ge_msg_stack.add;
1276 app_exception.raise_exception;
1277 END chk_chg_enable;
1278
1279
1280 FUNCTION chk_add_new_disb (p_n_award_id igf_aw_award_all.award_id%TYPE)
1281 RETURN BOOLEAN AS
1282 ------------------------------------------------------------------
1283 --Created by : Sanil Madathil, Oracle IDC
1284 --Date created: 14 October 2004
1285 --
1286 -- Purpose : Generic Function
1287 -- Invoked :
1288 -- Function :
1289 --
1290 -- Parameters : p_n_award_id : IN parameter. Required.
1291 --
1292 --
1293 --Known limitations/enhancements and/or remarks:
1294 --
1295 --Change History:
1296 --Who When What
1297 ------------------------------------------------------------------
1298 CURSOR c_igf_sl_loans (cp_n_award_id igf_aw_award_all.award_id%TYPE) IS
1299 SELECT 'X'
1300 FROM igf_sl_loans_all lar
1301 WHERE lar.award_id = cp_n_award_id;
1302
1303 rec_c_igf_sl_loans c_igf_sl_loans%ROWTYPE;
1304
1305 CURSOR c_igf_aw_awd_disb (cp_n_award_id igf_aw_award_all.award_id%TYPE) IS
1306 SELECT adisb.disb_num
1307 FROM igf_aw_awd_disb_all adisb
1308 WHERE adisb.award_id=cp_n_award_id
1309 AND NVL(adisb.fund_status,'N') = 'N';
1310
1311 l_disb_num igf_aw_awd_disb_all.disb_num%TYPE;
1312 l_n_award_id igf_aw_award_all.award_id%TYPE;
1313 l_return_val BOOLEAN;
1314 BEGIN
1315 l_n_award_id := p_n_award_id;
1316 OPEN c_igf_sl_loans(cp_n_award_id => l_n_award_id);
1317 FETCH c_igf_sl_loans INTO rec_c_igf_sl_loans;
1318 -- check if loan record is created or not
1319 IF c_igf_sl_loans%NOTFOUND THEN
1320 CLOSE c_igf_sl_loans;
1321 RETURN TRUE;
1322 END IF;
1323 CLOSE c_igf_sl_loans;
1324 --if loan records exists, check if new disbursement can be added or not
1325 OPEN c_igf_aw_awd_disb(cp_n_award_id => l_n_award_id);
1326 FETCH c_igf_aw_awd_disb INTO l_disb_num;
1327 CLOSE c_igf_aw_awd_disb ;
1328
1329 l_return_val:= igf_sl_award.chk_chg_enable (p_n_award_id => l_n_award_id);
1330
1331 IF NOT (l_return_val) THEN
1332 RETURN TRUE;
1333 END IF;
1334
1335 IF (l_disb_num IS NULL) THEN
1336 RETURN FALSE;
1337 END IF;
1338 RETURN TRUE;
1339
1340
1341
1342 EXCEPTION
1343 WHEN OTHERS THEN
1344 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
1348 fnd_message.set_token('NAME','igf_sl_award.chk_add_new_disb');
1345 fnd_log.string(fnd_log.level_exception,'igf_sl_award.chk_add_new_disb exception',SQLERRM);
1346 END IF;
1347 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1349 igs_ge_msg_stack.add;
1350 app_exception.raise_exception;
1351 END chk_add_new_disb;
1352
1353
1354 FUNCTION chk_loan_increase (p_n_award_id igf_aw_award_all.award_id%TYPE)
1355 RETURN BOOLEAN AS
1356 ------------------------------------------------------------------
1357 --Created by : Sanil Madathil, Oracle IDC
1358 --Date created: 14 October 2004
1359 --
1360 -- Purpose : Generic Function
1361 -- Invoked :
1362 -- Function :
1363 --
1364 -- Parameters : p_n_award_id : IN parameter. Required.
1365 --
1366 --
1367 --Known limitations/enhancements and/or remarks:
1368 --
1369 --Change History:
1370 --Who When What
1371 ------------------------------------------------------------------
1372 CURSOR c_igf_sl_loans (cp_n_award_id igf_aw_award_all.award_id%TYPE) IS
1373 SELECT 'X'
1374 FROM igf_sl_loans_all lar
1375 WHERE lar.award_id = cp_n_award_id;
1376
1377 rec_c_igf_sl_loans c_igf_sl_loans%ROWTYPE;
1378
1379 CURSOR c_igf_aw_awd_disb (cp_n_award_id igf_aw_award_all.award_id%TYPE) IS
1380 SELECT adisb.disb_num
1381 FROM igf_aw_awd_disb_all adisb
1382 WHERE adisb.award_id=cp_n_award_id
1383 AND NVL(adisb.fund_status,'N') = 'N';
1384
1385 CURSOR c_chk_chg_enable (cp_n_award_id igf_aw_award_all.award_id%TYPE) IS
1386 SELECT loans.loan_number loan_number
1387 ,clset.cl_version cl_version
1388 ,loans.loan_status loan_status
1389 FROM igf_sl_lor_all lor
1390 ,igf_sl_loans_all loans
1391 ,igf_aw_award_all awd
1392 ,igf_aw_fund_mast_all fmast
1393 ,igf_sl_cl_setup_all clset
1394 WHERE loans.loan_id = lor.loan_id
1395 AND loans.award_id = cp_n_award_id
1396 AND lor.prc_type_code IN ('GO','GP')
1397 AND loans.loan_status <> 'S'
1398 AND NVL (loans.loan_chg_status,'*') <> 'S'
1399 AND awd.award_id = loans.award_id
1400 AND fmast.fund_id = awd.fund_id
1401 AND fmast.ci_cal_type = clset.ci_cal_type
1402 AND fmast.ci_sequence_number = clset.ci_sequence_number
1403 AND lor.relationship_cd = clset.relationship_cd ;
1404
1405 l_v_loan_number igf_sl_loans_all.loan_number%TYPE;
1406 l_n_cl_version igf_sl_cl_setup_all.cl_version%TYPE;
1407 l_v_loan_status igf_sl_loans_all.loan_status%TYPE;
1408 l_disb_num igf_aw_awd_disb_all.disb_num%TYPE;
1409 l_n_award_id igf_aw_award_all.award_id%TYPE;
1410
1411 BEGIN
1412 l_n_award_id := p_n_award_id;
1413 OPEN c_igf_sl_loans(cp_n_award_id => l_n_award_id);
1414 FETCH c_igf_sl_loans INTO rec_c_igf_sl_loans;
1415 -- check if loan record is created or not
1416 IF c_igf_sl_loans%NOTFOUND THEN
1417 CLOSE c_igf_sl_loans;
1418 RETURN TRUE;
1419 END IF;
1420 CLOSE c_igf_sl_loans;
1421 --if loan records exists, check if new disbursement can be added or not
1422 OPEN c_igf_aw_awd_disb(cp_n_award_id => l_n_award_id);
1423 FETCH c_igf_aw_awd_disb INTO l_disb_num;
1424 CLOSE c_igf_aw_awd_disb ;
1425
1426 OPEN c_chk_chg_enable (cp_n_award_id => l_n_award_id);
1427 FETCH c_chk_chg_enable INTO l_v_loan_number,l_n_cl_version,l_v_loan_status;
1428 IF c_chk_chg_enable%NOTFOUND THEN
1429 CLOSE c_chk_chg_enable;
1430 RETURN TRUE;
1431 END IF;
1432 CLOSE c_chk_chg_enable;
1433
1434 IF (l_n_cl_version = 'RELEASE-4')THEN
1435 IF l_disb_num IS NOT NULL THEN
1436 RETURN TRUE;
1437 END IF;
1438 RETURN FALSE;
1439 ELSIF (l_n_cl_version = 'RELEASE-5')THEN
1440 -- if loan status is accepted or sent
1441 IF l_v_loan_status IN ('A','S') THEN
1442 RETURN FALSE;
1443 END IF;
1444 RETURN TRUE;
1445 END IF;
1446
1447 EXCEPTION
1448 WHEN OTHERS THEN
1449 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
1450 fnd_log.string(fnd_log.level_exception,'igf_sl_award.chk_loan_increase exception',SQLERRM);
1451 END IF;
1452 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1453 fnd_message.set_token('NAME','igf_sl_award.chk_loan_increase');
1454 igs_ge_msg_stack.add;
1455 app_exception.raise_exception;
1456 END chk_loan_increase;
1457
1458 FUNCTION get_loan_cl_version (p_n_award_id igf_aw_award_all.award_id%TYPE)
1459 RETURN igf_sl_cl_setup_all.cl_version%TYPE AS
1460 ------------------------------------------------------------------
1461 --Created by : Sanil Madathil, Oracle IDC
1462 --Date created: 14 October 2004
1463 --
1464 -- Purpose : Generic Function
1465 -- Invoked :
1466 -- Function :
1467 --
1468 -- Parameters : p_n_award_id : IN parameter. Required.
1469 --
1470 --
1471 --Known limitations/enhancements and/or remarks:
1472 --
1473 --Change History:
1474 --Who When What
1475 --museshad 06-May-2005 Bug# 4346258
1476 -- Modified the entire logic in the function so that
1477 -- it arrives at the correct CL version# by
1478 -- taking into account any CL version# override
1479 -- for any particular Organization Unit setup in
1480 -- FFELP Setup override.
1481 ------------------------------------------------------------------
1482
1483 /* Cursor Variable */
1484 CURSOR c_get_loan_details(cp_n_award_id igf_aw_award_all.award_id%TYPE)
1485 IS
1486 SELECT
1487 awd.award_id,
1488 awd.base_id,
1489 fmast.ci_cal_type,
1490 fmast.ci_sequence_number,
1491 lor.relationship_cd
1492 FROM
1496 igf_aw_fund_mast_all fmast
1493 igf_sl_lor_all lor,
1494 igf_sl_loans_all loans,
1495 igf_aw_award_all awd,
1497 WHERE
1498 loans.loan_id = lor.loan_id AND
1499 awd.award_id = loans.award_id AND
1500 fmast.fund_id = awd.fund_id AND
1501 awd.award_id = cp_n_award_id;
1502
1503 /* Local Variables */
1504 l_n_cl_version igf_sl_cl_setup_all.cl_version%TYPE;
1505 l_n_award_id igf_aw_award_all.award_id%TYPE;
1506 l_n_base_id igf_aw_award_all.base_id%TYPE;
1507 l_v_ci_cal_type igf_aw_fund_mast_all.ci_cal_type%TYPE;
1508 l_n_ci_sequence_number igf_aw_fund_mast_all.ci_sequence_number%TYPE;
1509 l_v_relationship_cd igf_sl_lor_all.relationship_cd%TYPE;
1510
1511 BEGIN
1512 -- Get Base_Id and related details
1513 OPEN c_get_loan_details(cp_n_award_id => p_n_award_id);
1514 FETCH c_get_loan_details INTO l_n_award_id, l_n_base_id, l_v_ci_cal_type,
1515 l_n_ci_sequence_number, l_v_relationship_cd;
1516 CLOSE c_get_loan_details;
1517
1518 -- Get CL Version#
1519 l_n_cl_version := igf_sl_gen.get_cl_version(p_ci_cal_type => l_v_ci_cal_type,
1520 p_ci_seq_num => l_n_ci_sequence_number,
1521 p_relationship_cd => l_v_relationship_cd,
1522 p_base_id => l_n_base_id);
1523 RETURN l_n_cl_version;
1524
1525 EXCEPTION
1526 WHEN OTHERS THEN
1527 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
1528 fnd_log.string(fnd_log.level_exception,'igf_sl_award.get_loan_cl_version exception',SQLERRM);
1529 END IF;
1530 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1531 fnd_message.set_token('NAME','igf_sl_award.get_loan_cl_version');
1532 igs_ge_msg_stack.add;
1533 app_exception.raise_exception;
1534 END get_loan_cl_version;
1535
1536 FUNCTION chk_fund_st_chg ( p_n_award_id IN igf_aw_award_all.award_id%TYPE,
1537 p_n_disb_num IN igf_aw_awd_disb_all.disb_num%TYPE
1538 )
1539 RETURN BOOLEAN AS
1540 ------------------------------------------------------------------
1541 --Created by : Sanil Madathil, Oracle IDC
1542 --Date created: 14 October 2004
1543 --
1544 -- Purpose : Generic Function
1545 -- Invoked :
1546 -- Function :
1547 --
1548 -- Parameters : p_n_award_id : IN parameter. Required.
1549 -- p_n_disb_num : IN parameter. Required.
1550 --
1551 --Known limitations/enhancements and/or remarks:
1552 --
1553 --Change History:
1554 --Who When What
1555 ------------------------------------------------------------------
1556 CURSOR c_chk_fund_st_chg_1 (cp_n_award_id IN igf_aw_award_all.award_id%TYPE,
1557 cp_n_disb_num IN igf_aw_awd_disb_all.disb_num%TYPE) IS
1558 SELECT dbresp.disb_num
1559 FROM igf_db_cl_disb_resp dbresp
1560 ,igf_sl_loans_all loans
1561 ,igf_aw_award_all awd
1562 WHERE dbresp.disb_num = cp_n_disb_num
1563 AND loans.loan_number = dbresp.loan_number
1564 AND awd.award_id = loans.award_id
1565 AND awd.award_id = cp_n_award_id
1566 ORDER BY cdbr_id DESC;
1567
1568 CURSOR c_chk_fund_st_chg_2 (cp_n_award_id IN igf_aw_award_all.award_id%TYPE,
1569 cp_n_disb_num IN igf_aw_awd_disb_all.disb_num%TYPE) IS
1570 SELECT chg.disbursement_number
1571 FROM igf_sl_clchsn_dtls chg
1572 ,igf_sl_loans_all loans
1573 ,igf_aw_award_all awd
1574 WHERE chg.disbursement_number = cp_n_disb_num
1575 AND chg.change_record_type_txt = '10'
1576 AND chg.status_code <> ('D')
1577 AND chg.loan_number_txt = loans.loan_number
1578 AND loans.award_id = awd.award_id
1579 AND awd.award_id = cp_n_award_id ;
1580
1581 l_n_disb_num igf_aw_awd_disb_all.disb_num%TYPE;
1582
1583 BEGIN
1584
1585 -- Fund Status would determine if the Disbursement Change is to be considered as Pre or Post Disbursement,
1586 -- Fund Status once set to "Funded" cannot be updated if
1587 -- There are roster response present for the disbursement, or
1588 -- Post Disbursement Change Records are present in the Change Send table for this disbursement
1589 OPEN c_chk_fund_st_chg_1 (cp_n_award_id => p_n_award_id,
1590 cp_n_disb_num => p_n_disb_num
1591 );
1592 FETCH c_chk_fund_st_chg_1 INTO l_n_disb_num;
1593 IF c_chk_fund_st_chg_1%FOUND THEN
1594 CLOSE c_chk_fund_st_chg_1;
1595 RETURN FALSE;
1596 END IF;
1597 CLOSE c_chk_fund_st_chg_1;
1598 OPEN c_chk_fund_st_chg_2 (cp_n_award_id => p_n_award_id,
1599 cp_n_disb_num => p_n_disb_num
1600 );
1601 FETCH c_chk_fund_st_chg_2 INTO l_n_disb_num;
1602 IF c_chk_fund_st_chg_2%FOUND THEN
1603 CLOSE c_chk_fund_st_chg_2;
1604 RETURN FALSE;
1605 END IF;
1606 CLOSE c_chk_fund_st_chg_2 ;
1607 RETURN TRUE;
1608 EXCEPTION
1609 WHEN OTHERS THEN
1610 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
1611 fnd_log.string(fnd_log.level_exception,'igf_sl_award.chk_fund_st_chg exception',SQLERRM);
1612 END IF;
1613 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1614 fnd_message.set_token('NAME','igf_sl_award.chk_fund_st_chg');
1615 igs_ge_msg_stack.add;
1616 app_exception.raise_exception;
1617 END chk_fund_st_chg;
1618
1619 END igf_sl_award;
[Home] [Help]