1 PACKAGE BODY igf_aw_packng_subfns AS
2 /* $Header: IGFAW09B.pls 120.10 2006/08/04 07:38:08 veramach ship $ */
3
4 /* ------------------------------------------------------------------
5 || Created By : avenkatr
6 || Created On : 20-JUN-2001
7 || Purpose :
8 || Known limitations, enhancements or remarks :
9 || skoppula
10 || The functionality of this procedure is changed to recieve the loan
11 || amounts for the student and then compare them against the Federal
12 || Stafford loan limits setup through IGFAW021.fmb
13 || museshad 15-Jun-2005 Build# FA157 - Bug# 4382371.
14 || Added the parameters - l_awd_period, l_called_from.
15 || As of now, these parameters (defaultable) will get passed only
16 || when called from the Packaging process (igf_aw_packaging).
17 || Passed these paramaters to get_class_stnd()
18 || to get the class standing data from anticipated data, if
19 || actual data is not available.
20 || sjadhav 09-Nov-2004 corrected garde level override comparsion
21 || veramach 11-Oct-2004 Obsoleted get_coa_months,stud_elig_chk
22 || veramach 11-NOV-2003 FA 125 Multiple distribution methods
23 || 1.Changed function signature of get_class_stnd to take adplans_id instead of fund_id
24 || 2.Award start date is chosen based on distribution plan setup instead of fund setup
25 ||
26 || veramach 07-OCT-2003 FA 124
27 || 1.Removed the procedure get_im_efc
28 || 2.Added the function is_over_award_occured
29 || brajendr 20-Dec-2002 Bug # 2706197
30 || Modifed the logic for get class standing process, removed the return statement.
31 ||
32 || brajendr 24-Oct-2002 FA105 / FA108 Builds
33 || Modified the references of Obsoleted Views
34 --------------------------------------------------------------------*/
35
36 PROCEDURE check_loan_limits( l_base_id IN NUMBER,
37 fund_type IN VARCHAR2,
38 l_award_id IN NUMBER,
39 l_adplans_id IN NUMBER,
40 l_aid IN OUT NOCOPY NUMBER,
41 l_std_loan_tab IN std_loan_tab DEFAULT NULL,
42 p_msg_name OUT NOCOPY VARCHAR2,
43 l_awd_period IN igf_aw_awd_prd_term.award_prd_cd%TYPE DEFAULT NULL,
44 l_called_from IN VARCHAR2 DEFAULT 'NON-PACKAGING',
45 p_chk_aggr_limit IN VARCHAR2 DEFAULT 'Y'
46 ) IS
47 /*
48 || Created By : avenkatr
49 || Created On : 20-JUN-2001
50 || Purpose :
51 || Known limitations, enhancements or remarks :
52 || Change History :
53 || Who When What
54 || (reverse chronological order - newest change first)
55 || bkkumar 14-Jan-04 Bug# 3360702
56 || Added the check for the l_aid to be negative and then returning with the
57 || appropriate message.
58 || cdcruz 05-feb-03 Bug# 2758804
59 || cursor c_get_status ref changed to pick active isisr
60 */
61
62 lv_class_standing igs_pr_css_class_std_v.class_standing%TYPE;
63 lv_course_type igs_ps_ver_all.course_type%TYPE;
64
65 CURSOR c_fabase_det IS
66 SELECT *
67 FROM igf_ap_fa_base_rec_all
68 WHERE base_id = l_base_id;
69
70 l_fabase_det c_fabase_det%ROWTYPE;
71
72 CURSOR c_get_status IS
73 SELECT dependency_status
74 FROM igf_ap_isir_matched_all
75 WHERE base_id = l_base_id AND
76 payment_isir = 'Y' AND
77 system_record_type = 'ORIGINAL';
78
79 l_get_status c_get_status%ROWTYPE;
80
81 CURSOR c_loan_limit_FFELP ( x_depend_stat igf_ap_isir_matched.dependency_status%TYPE,
82 x_fl_grad_lvl VARCHAR2,
83 x_ci_cal_type Varchar,
84 x_ci_sequence_number Number) IS
85 SELECT subs_annual_lt, tot_annual_lt, subs_aggr_lt, tot_aggr_lt
86 FROM igf_aw_loan_limit_all
87 WHERE ci_cal_type = x_ci_cal_type AND
88 ci_sequence_number = x_ci_sequence_number AND
89 depend_stat = x_depend_stat AND
90 ffelp_grade_level = x_fl_grad_lvl;
91
92
93 CURSOR c_loan_limit_DL( x_depend_stat igf_ap_isir_matched.dependency_status%TYPE,
94 x_ci_cal_type Varchar,
95 x_ci_sequence_number Number,
96 x_dl_grad_lvl VARCHAR2 ) IS
97 SELECT subs_annual_lt, tot_annual_lt, subs_aggr_lt, tot_aggr_lt
98 FROM igf_aw_loan_limit_all
99 WHERE ci_cal_type = x_ci_cal_type AND
100 ci_sequence_number = x_ci_sequence_number AND
101 depend_stat = x_depend_stat AND
102 dl_grade_level = x_dl_grad_lvl;
103
104 l_loan_limit c_loan_limit_DL%ROWTYPE;
105
106 -- Cursor to fetch the nslds loan aggregates and the transaction process
107 -- date from the isir matced table
108 CURSOR c_nslds_loans IS SELECT
109 NVL(nslds.nslds_agg_subsz_out_prin_bal,0) aggr_subs_loan,
110 NVL(nslds.nslds_agg_comb_out_prin_bal,0) aggr_total_loan,
111 isir.tran_process_date
112 FROM igf_ap_isir_matched_all isir,igf_ap_nslds_data nslds
113 WHERE nslds.base_id = l_base_id
114 AND isir.isir_id = nslds.isir_id
115 ORDER BY TO_NUMBER(nslds.transaction_num_txt) DESC;
116
117 l_nslds_loan_rec c_nslds_loans%ROWTYPE;
118
119 -- Cursor to pick up all those awards which have not yet gone into
120 -- the NSLDS database
121
122 CURSOR c_get_addtnl_awd(
123 cp_awd_date igf_aw_award.award_date%TYPE
124 ) IS
125 SELECT SUM (NVL (awd.offered_amt, 0)) loan_amt,
126 SUM (DECODE (
127 fcat.fed_fund_code,
128 'FLS', NVL (awd.offered_amt, 0),
129 'DLS', NVL (awd.offered_amt, 0),
130 0
131 )) subs_loan_amt
132 FROM igf_aw_award_all awd,
133 igf_aw_fund_mast fm,
134 igf_aw_fund_cat_all fcat,
135 igf_ap_fa_base_rec_all fa
136 WHERE fa.person_id IN (SELECT person_id
137 FROM igf_ap_fa_base_rec_all
138 WHERE base_id = l_base_id)
139 AND awd.base_id = fa.base_id
140 AND awd.fund_id = fm.fund_id
141 AND ( cp_awd_date IS NULL
142 OR TRUNC (awd.award_date) >= TRUNC (cp_awd_date))
143 AND awd.award_status IN ('OFFERED', 'ACCEPTED')
144 AND fcat.fund_code = fm.fund_code
145 AND fcat.fed_fund_code IN ('DLS', 'DLU', 'FLS', 'FLU');
146 l_addtnl_awd_rec c_get_addtnl_awd%ROWTYPE;
147
148 -- Cursor to check annual loan limits
149 CURSOR c_get_anl_awd IS
150 SELECT SUM(NVL(awd.offered_amt,0)) loan_amt,
151 SUM(DECODE(fcat.fed_fund_code,'FLS',
152 NVL(awd.offered_amt,0),'DLS',NVL(awd.offered_amt,0),0)) subs_loan_amt
153 FROM igf_aw_award_all awd,
154 igf_aw_fund_mast_all fm,
155 igf_aw_fund_cat_all fcat
156 WHERE awd.base_id = l_base_id
157 AND awd.fund_id = fm.fund_id
158 AND awd.award_status IN ('OFFERED','ACCEPTED')
159 AND fcat.fund_code = fm.fund_code
160 AND fcat.fed_fund_code IN ('DLS','DLU','FLS','FLU');
161
162 -- Cursor to determine the start date for the award
163
164 --
165 -- cursor to read grade level override
166 -- FA 134 Build, Loan Level Grade Level Override Impact
167 -- use this value if not null for CommonLine Checks
168 CURSOR cur_grade_overide(p_award_id NUMBER) IS
169 SELECT override_grade_level_code
170 FROM igf_sl_lor_all lor,
171 igf_sl_loans_all loan
172 WHERE loan.loan_id = lor.loan_id
173 AND loan.award_id = p_award_id;
174
175 grade_overide_rec cur_grade_overide%ROWTYPE;
176 lv_grd_lvl VARCHAR2(30);
177
178 l_anl_awd_rec c_get_anl_awd%ROWTYPE;
179 l_yr_loan_amt NUMBER := 0; -- Current year loan amount
180 l_aggr_loan_amt NUMBER := 0; -- Aggregate loan amount
181 l_yr_subs_amt NUMBER := 0; -- Current year subs. loan amount
182 l_aggr_subs_amt NUMBER := 0; -- Aggregate Subsidized loan amount
183 l_nsl_nc_ln_amt NUMBER := 0; -- This var. is used in the std_loan table to fetch
184 l_nsl_nc_sub_amt NUMBER := 0;
185 l_dl_std_code igf_ap_class_std_map.dl_std_code%TYPE;
186 l_cl_std_code igf_ap_class_std_map.cl_std_code%TYPE;
187 l_aggr_unsb_total NUMBER;
188 l_anul_unsub_total NUMBER;
189
190 BEGIN
191
192 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
193 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Parameter List - START');
194 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','In Param :l_base_id '|| l_base_id );
195 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','In Param :fund_type '|| fund_type );
196 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','In Param :l_award_id '|| l_award_id );
197 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','In Param :l_aid '|| l_aid );
198 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','In Param :l_adplans_id '|| l_adplans_id );
199 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','In Param :l_awd_period '|| l_awd_period );
200 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','In Param :l_called_from '|| l_called_from );
201 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','In Param :p_chk_aggr_limit '|| p_chk_aggr_limit );
202 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Parameter List - END');
203 END IF;
204
205 p_msg_name := NULL;
206
207 OPEN c_fabase_det;
208 FETCH c_fabase_det INTO l_fabase_det;
209 CLOSE c_fabase_det;
210
211 OPEN cur_grade_overide(l_award_id);
212 FETCH cur_grade_overide INTO grade_overide_rec;
213 CLOSE cur_grade_overide;
214
215 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
216 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','grade_overide '|| grade_overide_rec.override_grade_level_code);
217 END IF;
218 --
219 -- If there is not grade level override record
220 -- or if grade level override is null then derive class
221 -- standing
222 --
223 IF grade_overide_rec.override_grade_level_code IS NULL THEN
224 lv_class_standing := get_class_stnd(l_base_id,
225 l_fabase_det.person_id,
226 l_adplans_id,
227 l_award_id,
228 lv_course_type,
229 l_awd_period,
230 l_called_from);
231 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
232 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','lv_class_standing:'||lv_class_standing);
233 END IF;
234 IF lv_class_standing IS NULL THEN
235 p_msg_name := 'IGF_AW_CLS_STD_NOT_FND';
236 l_aid := 0;
237 RETURN;
238 END IF;
239 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
240 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','calling dl cl std code with parameters - base_id: ' ||l_base_id|| ', class standing: ' ||lv_class_standing|| ', course type: ' ||lv_course_type);
241 END IF;
242
243 igf_sl_lar_creation.get_dl_cl_std_code(l_base_id,
244 lv_class_standing,
245 lv_course_type,
246 l_dl_std_code,
247 l_cl_std_code);
248 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
249 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','after dl cl std code');
250 END IF;
251
252 IF l_dl_std_code IS NULL AND l_cl_std_code IS NULL THEN
253 p_msg_name := 'IGF_AW_CLSSTD_MAP_NOT_FND';
254 l_aid := 0;
255 RETURN;
256 END IF;
257 END IF;
258
259 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
260 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','l_dl_std_code: '||l_dl_std_code||' l_cl_std_code: '||l_cl_std_code);
261 END IF;
262
263 OPEN c_get_status ;
264 FETCH c_get_status INTO l_get_status;
265 IF c_get_status%NOTFOUND THEN
266 CLOSE c_get_status;
267 p_msg_name := 'IGF_AW_DEP_STAT_NOT_FND';
268 l_aid := 0;
269 RETURN;
270 END IF;
271 CLOSE c_get_status;
272
273 -- Fetching the aggregate loans (Subsidized and combined)
274
275 OPEN c_nslds_loans;
276 FETCH c_nslds_loans INTO l_nslds_loan_rec;
277 IF c_nslds_loans%NOTFOUND THEN
278 fnd_message.set_name('IGF','IGF_AW_NSLDS_NOT_FND');
279 igs_ge_msg_stack.add;
280 END IF;
281 CLOSE c_nslds_loans;
282
283 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
284 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','l_nslds_loan_rec.aggr_subs_loan: '||l_nslds_loan_rec.aggr_subs_loan);
285 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','l_nslds_loan_rec.aggr_total_loan: '||l_nslds_loan_rec.aggr_total_loan);
286 END IF;
287
288 IF l_std_loan_tab IS NOT NULL AND l_std_loan_tab.COUNT > 0 THEN -- check whether the table parameter is passed with rows
289
290 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
291 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','l_std_loan_tab.COUNT:'||l_std_loan_tab.COUNT);
292 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Inside the pl-sql tab IF');
293 END IF;
294
295
296 FOR i IN 1..l_std_loan_tab.COUNT LOOP
297
298 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
299 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Inside the pl-sql tab loop');
300 END IF;
301
302 l_yr_loan_amt := NVL(l_yr_loan_amt,0) + NVL(l_std_loan_tab(i).award_amount,0);
303
304 -- Log
305 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
306 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','added, to l_yr_loan_amt,award:'||NVL(l_std_loan_tab(i).award_amount,0)||' from:'||l_std_loan_tab(i).fund_code);
307 END IF;
308
309 IF l_std_loan_tab(i).award_date >= l_nslds_loan_rec.tran_process_date THEN
310 l_nsl_nc_ln_amt := NVL(l_nsl_nc_ln_amt,0) + NVL(l_std_loan_tab(i).award_amount,0);
311
312 -- Log
313 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
314 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','added, to l_nsl_nc_ln_amt,award:'||NVL(l_std_loan_tab(i).award_amount,0)||' from:'||l_std_loan_tab(i).fund_code);
315 END IF;
316 END IF;
317
318 IF l_std_loan_tab(i).fed_fund_code IN ('FLS','DLS') THEN
319 l_yr_subs_amt := NVL(l_yr_subs_amt,0) + NVL(l_std_loan_tab(i).award_amount,0);
320 -- Log
321 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
322 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','added, to l_yr_subs_amt,award:'||NVL(l_std_loan_tab(i).award_amount,0)||' from:'||l_std_loan_tab(i).fund_code);
323 END IF;
324
325 IF l_std_loan_tab(i).award_date >= l_nslds_loan_rec.tran_process_date THEN
326 l_nsl_nc_sub_amt := NVL(l_nsl_nc_sub_amt,0) + NVL(l_std_loan_tab(i).award_amount,0);
327
328 -- Log
329 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
330 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','added, to l_nsl_nc_sub_amt,award:'||NVL(l_std_loan_tab(i).award_amount,0)||' from:'||l_std_loan_tab(i).fund_code);
331 END IF;
332 END IF;
333 END IF;
334 END LOOP; -- End for loop of student table
335
336 ELSE
337
338 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
339 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Inside the pl-sql tab ELSE');
340 END IF;
341
342 OPEN c_get_anl_awd;
343 FETCH c_get_anl_awd INTO l_anl_awd_rec;
344 CLOSE c_get_anl_awd;
345
346 l_yr_loan_amt := NVL(l_anl_awd_rec.loan_amt,0);
347 l_yr_subs_amt := NVL(l_anl_awd_rec.subs_loan_amt,0);
348
349 OPEN c_get_addtnl_awd(l_nslds_loan_rec.tran_process_date);
350 FETCH c_get_addtnl_awd INTO l_addtnl_awd_rec;
351 CLOSE c_get_addtnl_awd;
352
353 l_nsl_nc_ln_amt := NVL(l_addtnl_awd_rec.loan_amt,0);
354 l_nsl_nc_sub_amt := NVL(l_addtnl_awd_rec.subs_loan_amt,0);
355
356 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
357 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','l_yr_loan_amt:'||l_yr_loan_amt);
358 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','l_yr_subs_amt:'||l_yr_subs_amt);
359 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','l_nsl_nc_ln_amt:'||l_nsl_nc_ln_amt);
360 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','l_nsl_nc_sub_amt:'||l_nsl_nc_sub_amt);
361 END IF;
362
363 END IF; --End of table parameter check
364
365 l_aggr_loan_amt := NVL(l_nslds_loan_rec.aggr_total_loan,0) + NVL(l_nsl_nc_ln_amt,0);
366 l_aggr_subs_amt := NVL(l_nslds_loan_rec.aggr_subs_loan,0) + NVL(l_nsl_nc_sub_amt,0);
367 -- here the unsub aggregate and subsidized amounts are calculated to be used ahead.
368 l_aggr_unsb_total := l_aggr_loan_amt - l_aggr_subs_amt;
369 l_anul_unsub_total := l_yr_loan_amt - l_yr_subs_amt;
370
371 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
372 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','l_aggr_loan_amt:'||l_aggr_loan_amt);
373 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','l_aggr_subs_amt:'||l_aggr_subs_amt);
374 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','l_aggr_unsb_total:'||l_aggr_unsb_total);
375 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','l_anul_unsub_total:'||l_anul_unsub_total);
376 END IF;
377
378 /* Added by svuppala as part of bug: 3416936 */
379 IF (l_fabase_det.adnl_unsub_loan_elig_flag ='Y' AND l_get_status.dependency_status ='D') THEN
380 l_get_status.dependency_status := 'I';
381 END IF;
382
383 IF grade_overide_rec.override_grade_level_code IS NOT NULL THEN
384 IF fund_type IN ('DLP','DLS','DLU') THEN
385 l_dl_std_code := grade_overide_rec.override_grade_level_code;
386 END IF;
387 IF fund_type IN ('FLP','FLS','FLU','ALT') THEN
388 l_cl_std_code := grade_overide_rec.override_grade_level_code;
389 END IF;
390 END IF;
391
392 IF fund_type IN ('DLP','DLS','DLU') THEN
393 OPEN c_loan_limit_dl(l_get_status.dependency_status,l_fabase_det.ci_cal_type,l_fabase_det.ci_sequence_number,l_dl_std_code);
394 FETCH c_loan_limit_dl INTO l_loan_limit;
395 END IF;
396
397 IF fund_type IN ('FLP','FLS','FLU','ALT') THEN
398 OPEN c_loan_limit_ffelp(l_get_status.dependency_status,l_cl_std_code,l_fabase_det.ci_cal_type,l_fabase_det.ci_sequence_number);
399 FETCH c_loan_limit_ffelp INTO l_loan_limit;
400 END IF;
401
402 IF (fund_type IN ('DLP','DLS','DLU') AND c_loan_limit_dl%FOUND)
403 OR (fund_type IN ('FLP','FLS','FLU','ALT') AND c_loan_limit_ffelp%FOUND) THEN
404
405 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
406 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Stafford loan limits(as in setup) for fund '||fund_type||':');
407 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','l_loan_limit.tot_aggr_lt:'||l_loan_limit.tot_aggr_lt);
408 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','l_loan_limit.tot_annual_lt:'||l_loan_limit.tot_annual_lt);
409 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','l_loan_limit.subs_aggr_lt:'||l_loan_limit.subs_aggr_lt);
410 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','l_loan_limit.subs_annual_lt:'||l_loan_limit.subs_annual_lt);
411 END IF;
412
413 -- Subs+Unsubs Aggr limit check
414 /* Check if loan is within aggregate loan limits and adjust the amount accordingly */
415 IF p_chk_aggr_limit = 'Y' THEN
416
417 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
418 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Total Aggr limit check - START');
419 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Total Aggr already received by student= ' ||NVL(l_aggr_loan_amt,0));
420 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Permissible Total Aggr(as in setup)= ' ||NVL(l_loan_limit.tot_aggr_lt,0));
421 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','l_aid(current loan amt)= ' ||NVL(l_aid,0));
422 END IF;
423
424 IF (( NVL(l_aggr_loan_amt,0) + NVL(l_aid,0)) > NVL(l_loan_limit.tot_aggr_lt,0) ) THEN
425 l_aid := NVL(l_loan_limit.tot_aggr_lt,0) - NVL(l_aggr_loan_amt,0);
426 END IF;
427
428 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
429 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Aggr l_aid(after adjustment): '||l_aid );
430 END IF;
431
432 -- if the l_aid is less than 0 it means that already the limits have been exhausted so just set the
433 -- appropriate the message and return.
434 IF l_aid < 0 THEN
435 p_msg_name := 'IGF_AW_AGGR_LMT_ERR';
436 IF c_loan_limit_dl%ISOPEN THEN
437 CLOSE c_loan_limit_dl;
438 END IF;
439 IF c_loan_limit_ffelp%ISOPEN THEN
440 CLOSE c_loan_limit_ffelp;
441 END IF;
442
443 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
444 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Total Aggr limit check FAILED with IGF_AW_AGGR_LMT_ERR');
445 END IF;
446
447 RETURN;
448 END IF;
449
450 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
451 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Total Aggr limit check PASSED');
452 END IF;
453 END IF; -- <<p_chk_aggr_limit>>
454
455 -- Subs+Unsubs Annual limit check
456 /* Check if loan is within annual loan limits and adjust the amount accordingly*/
457 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
458 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Total Annual limit check - START');
459 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Total Annual already received by student= ' ||NVL(l_yr_loan_amt,0));
460 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Permissible Total Annual(as in setup)= ' ||NVL(l_loan_limit.tot_annual_lt,0));
461 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','l_aid(current loan amt)= ' ||NVL(l_aid,0));
462 END IF;
463
464 IF (( NVL(l_yr_loan_amt,0) + NVL(l_aid,0)) > NVL(l_loan_limit.tot_annual_lt,0) ) THEN
465 l_aid := NVL(l_loan_limit.tot_annual_lt,0) - NVL(l_yr_loan_amt,0);
466 END IF;
467
468 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
469 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Annual l_aid(after adjustment) : '||l_aid );
470 END IF;
471
472 IF l_aid < 0 THEN
473 p_msg_name := 'IGF_AW_ANNUAL_LMT_ERR';
474
475 IF c_loan_limit_dl%ISOPEN THEN
476 CLOSE c_loan_limit_dl;
477 END IF;
478
479 IF c_loan_limit_ffelp%ISOPEN THEN
480 CLOSE c_loan_limit_ffelp;
481 END IF;
482
483 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
484 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Total Annual limit check FAILED with IGF_AW_ANNUAL_LMT_ERR');
485 END IF;
486
487 RETURN;
488 END IF;
489
490 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
491 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Total Annual limit check PASSED');
492 END IF;
493
494 /* Check if loan is a subsidized loan and then check whether annual and aggregate
495 subisidzed loan limits are met for the student and adjust the aid accordingly */
496 IF fund_type IN('DLS','FLS') THEN
497 -- Subsidized Aggr limit check
498 IF p_chk_aggr_limit = 'Y' THEN
499 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
500 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Subs Aggr limit check - START');
501 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Subs Aggr already received by student= ' ||NVL(l_aggr_subs_amt,0));
502 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Permissible Subs Aggr (as in setup)= ' ||NVL(l_loan_limit.subs_aggr_lt,0));
503 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','l_aid(current loan amt)= ' ||NVL(l_aid,0));
504 END IF;
505
506 IF (( NVL(l_aggr_subs_amt,0) + NVL(l_aid,0)) > NVL(l_loan_limit.subs_aggr_lt,0)) THEN
507
508 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
509 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','l_aggr_subs_amt + aid > l_loan_limit.subs_aggr_lt');
510 END IF;
511
512 l_aid := NVL(l_loan_limit.subs_aggr_lt,0) - NVL(l_aggr_subs_amt,0);
513 END IF;
514
515 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
516 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Subs Aggr l_aid(after adjustment) : '||l_aid );
517 END IF;
518
519 IF l_aid < 0 THEN
520 p_msg_name := 'IGF_AW_SUB_AGGR_LMT_ERR';
521 IF c_loan_limit_dl%ISOPEN THEN
522 CLOSE c_loan_limit_dl;
523 END IF;
524 IF c_loan_limit_ffelp%ISOPEN THEN
525 CLOSE c_loan_limit_ffelp;
526 END IF;
527
528 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
529 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Subs Aggr limit check FAILED with IGF_AW_SUB_AGGR_LMT_ERR');
530 END IF;
531
532 RETURN;
533 END IF;
534
535 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
536 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Subs Aggr limit check PASSED');
537 END IF;
538 END IF; -- <<p_chk_aggr_limit>>
539
540 -- Subsidized Annual limit check
541 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
542 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Subs Annual limit check - START');
543 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Subs Annual already received by student= ' ||NVL(l_yr_subs_amt,0));
544 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Permissible Subs Annual (as in setup)= ' ||NVL(l_loan_limit.subs_annual_lt,0));
545 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','l_aid(current loan amt)= ' ||NVL(l_aid,0));
546 END IF;
547
548 IF (( NVL(l_yr_subs_amt,0) + NVL(l_aid,0)) > NVL(l_loan_limit.subs_annual_lt,0)) THEN
549
550 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
551 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','l_yr_subs_amt + aid > l_loan_limit.subs_annual_lt');
552 END IF;
553
554 l_aid := NVL(l_loan_limit.subs_annual_lt,0) - NVL(l_yr_subs_amt,0);
555 END IF;
556
557 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
558 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Subs Annual l_aid(after adjustment) : '||l_aid );
559 END IF;
560
561 IF l_aid < 0 THEN
562 p_msg_name := 'IGF_AW_SUB_LMT_ERR';
563 IF c_loan_limit_dl%ISOPEN THEN
564 CLOSE c_loan_limit_dl;
565 END IF;
566 IF c_loan_limit_ffelp%ISOPEN THEN
567 CLOSE c_loan_limit_ffelp;
568 END IF;
569
570 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
571 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Subs Annual limit check FAILED with IGF_AW_SUB_LMT_ERR');
572 END IF;
573
574 RETURN;
575 END IF;
576
577 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
578 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Subs Annual limit check PASSED');
579 END IF;
580 END IF; -- << fund_type IN('DLS','FLS') >>
581
582 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
583 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','l_aid:'||l_aid);
584 END IF;
585
586 ELSIF (fund_type IN ('DLP','DLS','DLU') AND c_loan_limit_dl%NOTFOUND)
587 OR (fund_type IN ('FLP','FLS','FLU','ALT') AND c_loan_limit_ffelp%NOTFOUND) THEN
588 p_msg_name := 'IGF_AW_LOAN_LMT_NOT_FND';
589 l_aid := 0;
590
591 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
592 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.check_loan_limits.debug','Stafford loan limits not defined, msg is IGF_AW_LOAN_LMT_NOT_FND');
593 END IF;
594 END IF;
595
596 IF c_loan_limit_dl%ISOPEN THEN
597 CLOSE c_loan_limit_dl;
598 END IF;
599 IF c_loan_limit_ffelp%ISOPEN THEN
600 CLOSE c_loan_limit_ffelp;
601 END IF;
602
603 IF l_aid < 0 THEN
604 l_aid := 0;
605 END IF;
606 -- Commonline Loans
607 EXCEPTION
608 WHEN OTHERS THEN
609
610 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
611 FND_MESSAGE.SET_TOKEN('NAME','igf_aw_packng_subfns.check_loan_limits');
612 IGS_GE_MSG_STACK.ADD;
613 APP_EXCEPTION.RAISE_EXCEPTION ;
614
615 END check_loan_limits;
616
617 PROCEDURE get_fed_efc(
618 l_base_id IN NUMBER,
619 l_awd_prd_code IN igf_aw_awd_prd_term.award_prd_cd%TYPE,
620 l_efc_f OUT NOCOPY NUMBER,
621 l_pell_efc OUT NOCOPY NUMBER,
622 l_efc_ay OUT NOCOPY NUMBER
623 ) IS
624 /*
625 || Created By : avenkatr
626 || Created On : 07-JUN-2001
627 || Purpose :
628 || Known limitations, enhancements or remarks :
629 || Change History :
630 || Who When What
631 || veramach 08-Apr-2004 bug 3547237
632 || Enforced a check that if auto_zero_efc is set to 'Y' in the active_isir,
633 || then the EFC must be zero
634 || brajendr 03-Dec-2003 FA 128 Isir Update
635 || Modified the logic for deriving the PELL EFC
636 ||
637 || rasahoo 27-Nov-2003 FA 128 Isir Update
638 || Changed the query string 'qry_str'
639 ||
640 || cdcruz 05-feb-03 Bug# 2758804 FACR105
641 || cursor c_isir_id ref changed to pick active isisr
642 */
643
644 CURSOR c_isir_id ( x_base_id igf_ap_fa_base_rec.base_id%TYPE ) IS
645 SELECT isir_id,
646 primary_efc,
647 NVL(auto_zero_efc,'N') auto_zero_efc
648 FROM igf_ap_isir_matched
649 WHERE base_id = x_base_id
650 AND active_isir = 'Y';
651
652 l_isir_id c_isir_id%ROWTYPE;
653
654 CURSOR c_pell_efc ( cp_base_id igf_ap_fa_base_rec.base_id%TYPE ) IS
655 SELECT DECODE(fa.award_fmly_contribution_type, 2, isir.secondary_efc, isir.primary_efc) pell_efc
656 FROM igf_ap_fa_base_rec_all fa,
657 igf_ap_isir_matched_all isir
658 WHERE fa.base_id = isir.base_id
659 AND fa.base_id = cp_base_id
660 AND isir.active_isir = 'Y';
661
662 lc_pell_efc c_pell_efc%ROWTYPE;
663
664 -- Get the details of
665 CURSOR get_awd_fmly_contrib_type(cp_base_id igf_ap_fa_base_rec.base_id%TYPE) IS
666 SELECT award_fmly_contribution_type
667 FROM igf_ap_fa_base_rec_all
668 WHERE base_id = cp_base_id;
669
670 lv_awd_fmly_contrib_type igf_ap_fa_base_rec.award_fmly_contribution_type%TYPE;
671
672 cur_rec NUMBER;
673 rows NUMBER;
674 qry_str VARCHAR2(200);
675
676 l_coa_months NUMBER;
677
678 CURSOR c_efc(
679 cp_isir_id igf_ap_isir_matched_all.isir_id%TYPE,
680 cp_months_num NUMBER,
681 cp_awd_fmly_contrib_type igf_ap_fa_base_rec_all.award_fmly_contribution_type%TYPE
682 ) IS
683 SELECT DECODE (
684 cp_awd_fmly_contrib_type,
685 2, DECODE (
686 cp_months_num,
687 1, isir.sec_alternate_month_1,
688 2, isir.sec_alternate_month_2,
689 3, isir.sec_alternate_month_3,
690 4, isir.sec_alternate_month_4,
691 5, isir.sec_alternate_month_5,
692 6, isir.sec_alternate_month_6,
693 7, isir.sec_alternate_month_7,
694 8, isir.sec_alternate_month_8,
695 9, isir.secondary_efc,
696 10, isir.sec_alternate_month_10,
697 11, isir.sec_alternate_month_11,
698 12, isir.sec_alternate_month_12
699 ),
700 DECODE (
701 cp_months_num,
702 1, isir.primary_alternate_month_1,
703 2, isir.primary_alternate_month_2,
704 3, isir.primary_alternate_month_3,
705 4, isir.primary_alternate_month_4,
706 5, isir.primary_alternate_month_5,
707 6, isir.primary_alternate_month_6,
708 7, isir.primary_alternate_month_7,
709 8, isir.primary_alternate_month_8,
710 9, isir.primary_efc,
711 10, isir.primary_alternate_month_10,
712 11, isir.primary_alternate_month_11,
713 12, isir.primary_alternate_month_12
714 )
715 ) efc
716 FROM igf_ap_isir_matched isir
717 WHERE isir.isir_id = cp_isir_id;
718
719 BEGIN
720
721 OPEN c_isir_id( l_base_id );
722 FETCH c_isir_id INTO l_isir_id ;
723
724 IF c_isir_id%NOTFOUND THEN
725 l_efc_f := NULL;
726 l_pell_efc := NULL;
727 l_efc_ay := NULL;
728
729 ELSE
730
731 l_efc_f := NULL;
732 l_pell_efc := NULL;
733 l_efc_ay := NULL;
734 --
735 -- Derive the NON-PELL EFC
736 --
737
738 /*
739 If primary_efc is zero and auto_zero_efc='Y', then EFC for all months should be zero
740 */
741 IF l_isir_id.primary_efc = 0 AND NVL(l_isir_id.auto_zero_efc,'*') = 'Y' THEN
742 l_efc_f := 0;
743 l_pell_efc := 0;
744 l_efc_ay := 0;
745 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
746 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.get_fed_efc.debug','since auto_zero_efc=Y, returning zero');
747 END IF;
748 RETURN;
749 END IF;
750
751 -- Get the Awarding Family contribution type to corresponsing months EFC
752 lv_awd_fmly_contrib_type := NULL;
753 OPEN get_awd_fmly_contrib_type(l_base_id);
754 FETCH get_awd_fmly_contrib_type INTO lv_awd_fmly_contrib_type;
755 CLOSE get_awd_fmly_contrib_type;
756
757 l_coa_months := igf_aw_coa_gen.coa_duration(l_base_id,l_awd_prd_code);
758 IF l_coa_months > 12 OR l_coa_months < 0 THEN
759 l_coa_months := 12;
760 END IF;
761
762 IF l_coa_months IS NULL OR l_coa_months = 0 THEN
763 l_efc_f := NULL;
764 l_efc_ay := NULL;
765 ELSE
766 -- If the EFC Type is set to SECONDARY, then use Seconday months, else use Primary months
767 OPEN c_efc(l_isir_id.isir_id,l_coa_months,lv_awd_fmly_contrib_type);
768 FETCH c_efc INTO l_efc_ay;
769 CLOSE c_efc;
770 END IF;
771 --
772 -- Derive the PELL EFC
773 --
774 lc_pell_efc := NULL;
775 OPEN c_pell_efc( l_base_id );
776 FETCH c_pell_efc INTO lc_pell_efc;
777 IF c_pell_efc%FOUND THEN
778 l_pell_efc := lc_pell_efc.pell_efc;
779 END IF;
780 CLOSE c_pell_efc;
781
782 END IF;
783 CLOSE c_isir_id ;
784
785 IF l_awd_prd_code IS NOT NULL THEN
786 l_efc_f := igf_aw_gen_004.efc_f(l_base_id,l_awd_prd_code);
787 ELSE
788 l_efc_f := NULL;
789 END IF;
790
791 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
792 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.get_fed_efc.debug','l_efc_f: '||l_efc_f||' l_pell_efc: '||l_pell_efc);
793 END IF;
794
795 EXCEPTION
796 WHEN OTHERS THEN
797 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
798 FND_MESSAGE.SET_TOKEN('NAME','IGF_AW_PACKNG_SUBFNS.GET_FED_EFC '||SQLERRM);
799 IGS_GE_MSG_STACK.ADD;
800 l_efc_f := NULL;
801 l_pell_efc := NULL;
802 l_efc_ay := NULL;
803 END get_fed_efc;
804
805
806 FUNCTION get_class_stnd(
807 p_base_id IN igf_ap_fa_base_rec.base_id%TYPE,
808 p_person_id IN igf_ap_fa_base_rec.person_id%TYPE,
809 p_adplans_id IN NUMBER,
810 p_award_id IN igf_aw_award_all.award_id%TYPE,
811 p_course_type OUT NOCOPY igs_ps_ver_all.course_type%TYPE,
812 p_awd_period IN igf_aw_awd_prd_term.award_prd_cd%TYPE DEFAULT NULL,
813 p_called_from IN VARCHAR2 DEFAULT 'NON-PACKAGING'
814 ) RETURN CHAR IS
815
816 /*
817 || Created By : skoppula
818 || Created On : 29-MAY-2002
819 || Purpose : This function checks whether the minimum start date of the load calendar
820 || that is attached to the fund falls with in the current term. If it falls
821 || then it returns the actual class standig, otherwise it returns the class
822 || standing as on the minimum start date of the load calendar
823 || that is attached to the fund
824 || Known limitations, enhancements or remarks :
825 || Change History :
826 || Who When What
827 || (reverse chronological order - newest change first)
828 || museshad 21-Oct-2005 Added the check to ensure that p_award_id is not null when
829 || deriving Class Standing.
830 || museshad 16-Sep-2005 Bug 4604393
831 || Modified the logic for deriving predictive/actual Class Standing
832 || museshad 02-Jun-2005 Build# FA157 - Bug# 4382371.
833 || Use anticipated data for Class Standing and Program type if actual
834 || data is not available, when called from Packaging
835 || concurent process.
836 || Added the parameters - p_awd_period, p_called_from in the signature
837 || bkkumar 14-Jan-04 Bug# 3360702
838 || Added one new award_id parameter and changed the fund_id parameter to adplans_id
839 ||
840 || veramach 11-NOV-2003 FA 125 Multiple distribution methods
841 || 1.Changed function signature to take adplans_id instead of fund_id
842 || 2.Award start date is chosen based on distribution plan setup instead of fund setup
843 || rasahoo 01-09-2003 Removed cursor C_GET_PROG and all its references.
844 || called genric API to get the values of coloumns used in cursor C_GET_PROG
845 ||
846 || brajendr 20-Dec-2002 Bug # 2706197
847 || Modifed the logic for get class standing process, removed the return statement.
848 */
849
850 -- Cursor that fetches the key program from fa base h
851
852 l_acad_cal_type igs_ca_inst.cal_type%TYPE;
853 l_acad_seq_num igs_ca_inst.sequence_number%TYPE;
854 l_message VARCHAR2(4000);
855
856 -- Cursor that fetched the current enrolled term for
857 -- student
858 CURSOR c_enrl_dtl_cur IS SELECT
859 ci.cal_type enrl_load_cal_type,
860 ci.sequence_number enrl_load_seq_num ,
861 ci.alternate_code enrolled_term ,
862 TRUNC(ci.start_dt) enrolled_start_dt,
863 TRUNC(ci.end_dt) enrolled_end_dt
864 FROM
865 igs_ca_inst ci,
866 igs_ca_type cty
867 WHERE cty.s_cal_cat = 'LOAD'
868 AND cty.cal_type = ci.cal_type
869 AND (ci.cal_type, ci.sequence_number)
870 IN
871 (
872 SELECT sup_cal_type,
873 sup_ci_sequence_number
874 FROM igs_ca_inst_rel
875 WHERE sub_cal_type = l_acad_cal_type
876 AND sub_ci_sequence_number = l_acad_seq_num
877 UNION
878 SELECT sub_cal_type,
879 sub_ci_sequence_number
880 FROM igs_ca_inst_rel
881 WHERE sup_cal_type = l_acad_cal_type
882 AND sup_ci_sequence_number = l_acad_seq_num
883 )
884 AND
885 TRUNC(SYSDATE) BETWEEN TRUNC(ci.start_dt) AND TRUNC(ci.end_dt)
886 ORDER BY ci.start_dt;
887
888 l_enrl_dt_rec c_enrl_dtl_cur%ROWTYPE;
889
890 CURSOR c_get_awd_dt(
891 cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE
892 ) IS
893 SELECT MIN(terms.start_date) start_dt
894 FROM igf_aw_dp_terms_v terms
895 WHERE terms.adplans_id = cp_adplans_id;
896
897 CURSOR c_get_min_date(
898 cp_award_id igf_aw_award_all.award_id%TYPE
899 )IS
900 SELECT igf_aw_packaging.get_term_start_date(awd.base_id, disb.ld_cal_type, disb.ld_sequence_number) start_dt
901 FROM igf_aw_awd_disb_v disb,
902 igf_aw_award_all awd
903 WHERE disb.award_id = cp_award_id
904 AND disb.award_id = awd.award_id
905 ORDER BY igf_aw_packaging.get_term_start_date(awd.base_id, disb.ld_cal_type, disb.ld_sequence_number);
906
907 -- museshad (Bug# 4604393)
908 -- Get the start date of the earliest term in the (COA + DP) matching terms
909 CURSOR c_get_ear_term_st_date(
910 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
911 cp_adplans_id NUMBER
912 )
913 IS
914 SELECT igf_aw_packaging.get_term_start_date(cp_base_id, dp_terms.ld_cal_type, dp_terms.ld_sequence_number) ear_term_start_date
915 FROM
916 igf_aw_coa_term_tot_v coa_terms,
917 igf_aw_dp_terms dp_terms,
918 igf_aw_awd_dist_plans adplans
919 WHERE
920 dp_terms.ld_cal_type = coa_terms.ld_cal_type
921 AND dp_terms.ld_sequence_number = coa_terms.ld_sequence_number
922 AND dp_terms.adplans_id = adplans.adplans_id
923 AND coa_terms.base_id = cp_base_id
924 AND dp_terms.adplans_id = cp_adplans_id
925 ORDER BY igf_aw_packaging.get_term_start_date(cp_base_id, dp_terms.ld_cal_type, dp_terms.ld_sequence_number) ASC;
926
927 -- Get the start date of the earliest term in the DP matching terms
928 CURSOR c_get_ear_term_st_date_dp(
929 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
930 cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE
931 ) IS
932 SELECT igf_aw_packaging.get_term_start_date(cp_base_id, dp_terms.ld_cal_type, dp_terms.ld_sequence_number) ear_term_start_date
933 FROM igf_aw_dp_terms dp_terms,
934 igf_aw_awd_dist_plans adplans
935 WHERE dp_terms.adplans_id = adplans.adplans_id
936 AND dp_terms.adplans_id = cp_adplans_id
937 ORDER BY igf_aw_packaging.get_term_start_date(cp_base_id, dp_terms.ld_cal_type, dp_terms.ld_sequence_number) ASC;
938
939 -- Gets start date of the eariest term in the award
940 CURSOR c_get_ear_term_st_date_awd(
941 cp_award_id igf_aw_award_all.award_id%TYPE
942 ) IS
943 SELECT igf_aw_packaging.get_term_start_date(awd.base_id, disb.ld_cal_type, disb.ld_sequence_number) ear_term_start_date
944 FROM igf_aw_awd_disb_all disb,
945 igf_aw_award_all awd
946 WHERE awd.award_id = cp_award_id
947 AND awd.award_id = disb.award_id
948 ORDER BY igf_aw_packaging.get_term_start_date(awd.base_id, disb.ld_cal_type, disb.ld_sequence_number) ASC;
949
950 l_ear_term_start_date DATE;
951 l_pred_flag VARCHAR2(1);
952 -- museshad (Bug# 4604393)
953
954 l_awd_dt_rec c_get_awd_dt%ROWTYPE;
955 l_class_standing igs_pr_css_class_std_v.class_standing%TYPE := NULL;
956 l_course_cd VARCHAR2(10);
957 l_ver_number NUMBER;
958
959 -- Getting anticipated Class standing
960 -- Scans all the terms (starting from the earliest) in the awarding period
961 -- for a valid anticipated class standing. The first term that has a valid anticipated
962 -- class standing data is taken into consideration. The ROWNUM predicate is
963 -- added to avoid scanning other terms in the awarding period.
964 CURSOR c_get_ant_class_stnd(
965 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
966 cp_awd_per igf_aw_awd_prd_term.award_prd_cd%TYPE
967 )
968 IS
969 SELECT ant_data.class_standing class_standing,
970 ant_data.ld_cal_type load_cal_type,
971 ant_data.ld_sequence_number load_seq_num
972 FROM
973 igf_aw_awd_prd_term awd_per,
974 igs_ca_inst_all cal_inst,
975 igf_ap_fa_ant_data ant_data,
976 igf_ap_fa_base_rec_all fabase
977 WHERE
978 awd_per.ld_cal_type = cal_inst.cal_type AND
979 awd_per.ld_sequence_number = cal_inst.sequence_number AND
980 ant_data.ld_cal_type = awd_per.ld_cal_type AND
981 ant_data.ld_sequence_number = awd_per.ld_sequence_number AND
982 awd_per.ci_cal_type = fabase.ci_cal_type AND
983 awd_per.ci_sequence_number = fabase.ci_sequence_number AND
984 fabase.base_id = cp_base_id AND
985 awd_per.award_prd_cd = cp_awd_per AND
986 ant_data.base_id = cp_base_id AND
987 ant_data.class_standing IS NOT NULL
988 ORDER BY igf_aw_packaging.get_term_start_date(cp_base_id, awd_per.ld_cal_type, awd_per.ld_sequence_number) ASC;
989
990 l_get_ant_class_stnd_rec c_get_ant_class_stnd%ROWTYPE;
991
992 -- Getting anticipated Prog type
993 -- Scans all the terms (starting from the earliest) in the awarding period
994 -- for a valid anticipated Prog type. The first term that has a valid anticipated
995 -- Prog type data is taken into consideration. The ROWNUM predicate is
996 -- added to avoid scanning other terms in the awarding period.
997 CURSOR c_get_ant_prog_type(
998 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
999 cp_awd_per igf_aw_awd_prd_term.award_prd_cd%TYPE
1000 )
1001 IS
1002 SELECT ant_data.program_type prog_type,
1003 ant_data.ld_cal_type load_cal_type,
1004 ant_data.ld_sequence_number load_seq_num
1005 FROM
1006 igf_aw_awd_prd_term awd_per,
1007 igs_ca_inst_all cal_inst,
1008 igf_ap_fa_ant_data ant_data,
1009 igf_ap_fa_base_rec_all fabase
1010 WHERE
1011 awd_per.ld_cal_type = cal_inst.cal_type AND
1012 awd_per.ld_sequence_number = cal_inst.sequence_number AND
1013 ant_data.ld_cal_type = awd_per.ld_cal_type AND
1014 ant_data.ld_sequence_number = awd_per.ld_sequence_number AND
1015 awd_per.ci_cal_type = fabase.ci_cal_type AND
1016 awd_per.ci_sequence_number = fabase.ci_sequence_number AND
1017 fabase.base_id = cp_base_id AND
1018 awd_per.award_prd_cd = cp_awd_per AND
1019 ant_data.base_id = cp_base_id AND
1020 ant_data.program_type IS NOT NULL
1021 ORDER BY igf_aw_packaging.get_term_start_date(cp_base_id, awd_per.ld_cal_type, awd_per.ld_sequence_number) ASC;
1022
1023 l_get_ant_prog_type_rec c_get_ant_prog_type%ROWTYPE;
1024
1025 -- Get the award year Calendar details
1026 CURSOR c_get_cal_det(cp_base_id igf_ap_fa_base_rec.base_id%TYPE)
1027 IS
1028 SELECT ci_cal_type, ci_sequence_number
1029 FROM igf_ap_fa_base_rec
1030 WHERE base_id = cp_base_id;
1031
1032 l_get_cal_det_rec c_get_cal_det%ROWTYPE;
1033 l_ld_cal_type igs_ca_inst.cal_type%TYPE;
1034 l_ld_seq_num igs_ca_inst.sequence_number%TYPE;
1035
1036 BEGIN
1037
1038 -- Call generic API get_key_program to get the cource code
1039 igf_ap_gen_001.get_key_program(p_base_id,l_course_cd,l_ver_number);
1040 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1041 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.get_class_stnd.debug',
1042 'key_program_course_cd:'||l_course_cd||'key_program_version_number'||l_ver_number);
1043 END IF;
1044 igs_en_gen_015.get_academic_cal( p_person_id,l_course_cd,l_acad_cal_type,l_acad_seq_num,l_message);
1045 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1046 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.get_class_stnd.debug',
1047 'l_acad_cal_type:'||l_acad_cal_type||'l_acad_seq_num'||l_acad_seq_num||'l_message'||l_message);
1048 END IF;
1049
1050
1051 OPEN c_enrl_dtl_cur;
1052 FETCH c_enrl_dtl_cur INTO l_enrl_dt_Rec;
1053 CLOSE c_enrl_dtl_cur;
1054
1055 -- if the adplans_id is null use the award_id parameter otherwise adplans_id
1056 IF p_adplans_id IS NULL THEN
1057 OPEN c_get_min_date(p_award_id);
1058 FETCH c_get_min_date INTO l_awd_dt_rec;
1059 IF c_get_min_date%NOTFOUND THEN
1060 CLOSE c_get_min_date;
1061 RETURN l_class_standing;
1062 END IF;
1063 CLOSE c_get_min_date;
1064
1065 ELSE
1066 OPEN c_get_awd_dt(p_adplans_id);
1067 FETCH c_get_awd_dt INTO l_awd_dt_rec;
1068 IF c_get_awd_dt%NOTFOUND THEN
1069 CLOSE c_get_awd_dt;
1070 RETURN l_class_standing;
1071 END IF;
1072 CLOSE c_get_awd_dt;
1073 END IF;
1074
1075 -- museshad (Bug# 4604393)
1076 -- Derive Class Standing
1077 l_ear_term_start_date := NULL;
1078 IF igf_aw_gen_003.check_coa(p_base_id,p_awd_period) THEN
1079 OPEN c_get_ear_term_st_date(p_base_id, p_adplans_id);
1080 FETCH c_get_ear_term_st_date INTO l_ear_term_start_date;
1081 CLOSE c_get_ear_term_st_date;
1082 ELSE
1083 OPEN c_get_ear_term_st_date_dp(p_base_id, p_adplans_id);
1084 FETCH c_get_ear_term_st_date_dp INTO l_ear_term_start_date;
1085 CLOSE c_get_ear_term_st_date_dp;
1086 END IF;
1087
1088 IF l_ear_term_start_date IS NOT NULL THEN
1089
1090 IF l_ear_term_start_date > TRUNC(SYSDATE) THEN
1091 -- Predictive Class Standing
1092 l_pred_flag := 'Y';
1093
1094 -- Log message
1095 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1096 fnd_log.string(fnd_log.level_statement,
1097 'igf.plsql.igf_aw_packng_subfns.get_class_stnd.debug',
1098 'Computing PREDICTIVE class standing for date ' || TO_CHAR(l_ear_term_start_date, 'DD-MON-YYYY'));
1099 END IF;
1100 ELSE
1101 -- Actual Class Standing
1102 l_pred_flag := 'N';
1103
1104 -- Log message
1105 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1106 fnd_log.string(fnd_log.level_statement,
1107 'igf.plsql.igf_aw_packng_subfns.get_class_stnd.debug',
1108 'Computing ACTUAL class standing for date ' || TO_CHAR(l_ear_term_start_date, 'DD-MON-YYYY'));
1109 END IF;
1110 END IF;
1111
1112 -- Get Class Standing
1113 l_class_standing := igs_pr_get_class_std.get_class_standing(
1114 p_person_id => p_person_id,
1115 p_course_cd => l_course_cd,
1116 p_predictive_ind => l_pred_flag,
1117 p_effective_dt => l_ear_term_start_date,
1118 p_load_cal_type => NULL,
1119 p_load_ci_sequence_number => NULL
1120 );
1121
1122 -- Log message
1123 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1124 fnd_log.string(fnd_log.level_statement,
1125 'igf.plsql.igf_aw_packng_subfns.get_class_stnd.debug',
1126 'Class Standing= ' || l_class_standing);
1127 END IF;
1128 ELSIF p_award_id IS NOT NULL THEN
1129 /*
1130 If terms cannot be found from adplans_id, use award_id
1131 */
1132 OPEN c_get_ear_term_st_date_awd(p_award_id);
1133 FETCH c_get_ear_term_st_date_awd INTO l_ear_term_start_date;
1134 CLOSE c_get_ear_term_st_date_awd;
1135
1136 IF l_ear_term_start_date IS NOT NULL AND l_ear_term_start_date > TRUNC(SYSDATE) THEN
1137 l_pred_flag := 'Y';
1138 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1139 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.get_class_stnd.debug','computing predictive class standing for'||TO_CHAR(l_ear_term_start_date));
1140 END IF;
1141 ELSE
1142 l_pred_flag := 'N';
1143 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1144 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_packng_subfns.get_class_stnd.debug','computing actual class standing for'||TO_CHAR(l_ear_term_start_date));
1145 END IF;
1146 END IF;
1147
1148 -- Get Class Standing
1149 l_class_standing := igs_pr_get_class_std.get_class_standing(
1150 p_person_id => p_person_id,
1151 p_course_cd => l_course_cd,
1152 p_predictive_ind => l_pred_flag,
1153 p_effective_dt => l_ear_term_start_date,
1154 p_load_cal_type => NULL,
1155 p_load_ci_sequence_number => NULL
1156 );
1157 END IF;
1158 -- museshad (Bug# 4604393)
1159
1160
1161
1162 -- Call generic API get_enrl_program_type to get Program Type
1163 p_course_type := igf_ap_gen_001.get_enrl_program_type(p_base_id);
1164
1165 -- If actual Prog type is not available, then get it from anticipated data
1166 -- We will get anticipated Prog type when -
1167 -- 1. Enrollment (Actual) Prog type data is not available
1168 -- Note: We don't look into Admissions for Prog type, bcoz Admissions does not provide
1169 -- this information.
1170 -- 2. Profile option permits to consider anticipated data
1171 -- 3. Call is from Packaging concurent process
1172 IF (p_course_type IS NULL) AND (igf_aw_coa_gen.canUseAnticipVal) AND (p_called_from = 'PACKAGING') THEN
1173
1174 -- Anticipated data is defined at the term level. But the Packaging concurrent process
1175 -- works at the awarding period level. We will scan each term (starting from the earliest)
1176 -- in the awarding period and get its anticipated data. If Prog type is found for a
1177 -- term, we will not consider the remaining terms.
1178
1179 OPEN c_get_ant_prog_type(
1180 cp_base_id => p_base_id,
1181 cp_awd_per => p_awd_period
1182 );
1183 FETCH c_get_ant_prog_type INTO l_get_ant_prog_type_rec;
1184
1185 IF (c_get_ant_prog_type%FOUND) THEN
1186 -- Found anticipated Prog type
1187 p_course_type := l_get_ant_prog_type_rec.prog_type;
1188
1189 -- Log message
1190 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1191 fnd_log.string(fnd_log.level_statement,
1192 'igf.plsql.igf_aw_packng_subfns.get_class_stnd.debug',
1193 'Actual Prog type not available, but found anticipated Prog type.');
1194 fnd_log.string(fnd_log.level_statement,
1195 'igf.plsql.igf_aw_packng_subfns.get_class_stnd.debug',
1196 'Base Id: ' ||NVL(p_base_id,'')|| '. Anticipated Prog type=' ||p_course_type|| ', Term cal type=' ||l_get_ant_prog_type_rec.load_cal_type|| ', Term sequence number=' ||l_get_ant_prog_type_rec.load_seq_num);
1197 END IF;
1198 ELSE
1199 -- Anticipated Prog type is not defined in
1200 -- any of the terms in the awarding period.
1201 p_course_type := NULL;
1202
1203 -- Log message
1204 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1205 fnd_log.string(fnd_log.level_statement,
1206 'igf.plsql.igf_aw_packng_subfns.get_class_stnd.debug',
1207 'Both actual and anticipated Prog type is not available for - ' ||'Base Id: ' ||NVL(p_base_id,'')|| ' for any of the terms in the awarding period: ' ||p_awd_period);
1208 END IF;
1209 END IF; -- End (c_get_ant_prog_type%FOUND)
1210
1211 CLOSE c_get_ant_prog_type;
1212 END IF;
1213
1214 -- If actual Class Standing is not available, then get it from anticipated data
1215 -- We will get anticipated Class Standing when -
1216 -- 1. Enrollment (Actual) Class Standing data is not available
1217 -- Note: We don't look into Admissions for Class Standing, bcoz Admissions does not provide
1218 -- this information.
1219 -- 2. Profile option permits to consider anticipated data
1220 -- 3. Call is from Packaging concurent process
1221 IF (l_class_standing IS NULL) AND (igf_aw_coa_gen.canUseAnticipVal) AND (p_called_from = 'PACKAGING') THEN
1222
1223 -- Anticipated data is defined at the term level. But the Packaging concurrent process
1224 -- works at the awarding period level. We will scan each term (starting from the earliest)
1225 -- in the awarding period and get its anticipated data. If class standing is found for a
1226 -- term, we will not consider the remaining terms.
1227
1228 OPEN c_get_ant_class_stnd(
1229 cp_base_id => p_base_id,
1230 cp_awd_per => p_awd_period
1231 );
1232 FETCH c_get_ant_class_stnd INTO l_get_ant_class_stnd_rec;
1233
1234 IF (c_get_ant_class_stnd%FOUND) THEN
1235 -- Found anticipated Class Standing
1236 l_class_standing := l_get_ant_class_stnd_rec.class_standing;
1237
1238 -- Log message
1239 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1240 fnd_log.string(fnd_log.level_statement,
1241 'igf.plsql.igf_aw_packng_subfns.get_class_stnd.debug',
1242 'Actual class standing data not available, but found anticipated class standing.');
1243 fnd_log.string(fnd_log.level_statement,
1244 'igf.plsql.igf_aw_packng_subfns.get_class_stnd.debug',
1245 'Base Id: ' ||NVL(p_base_id,'')|| '. Anticipated class standing =' ||l_class_standing|| ', Term cal type=' ||l_get_ant_class_stnd_rec.load_cal_type|| ', Term sequence number=' ||l_get_ant_class_stnd_rec.load_seq_num);
1246 END IF;
1247
1248 CLOSE c_get_ant_class_stnd;
1249 RETURN l_class_standing;
1250 END IF;
1251 CLOSE c_get_ant_class_stnd;
1252
1253 -- Anticipated class standing is not defined in any of the terms in the
1254 -- awarding period.
1255 l_class_standing := NULL;
1256
1257 -- Log message
1258 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1259 fnd_log.string(fnd_log.level_statement,
1260 'igf.plsql.igf_aw_packng_subfns.get_class_stnd.debug',
1261 'Both actual and anticipated class standing is not available for - ' ||'Base Id: ' ||NVL(p_base_id,'')|| ' for any of the terms in the awarding period: ' ||p_awd_period);
1262 END IF;
1263 END IF; -- End of (l_class_standing IS NULL) AND (igf_aw_coa_gen.canUseAnticipVal) AND (p_called_from = 'PACKAGING')
1264
1265 RETURN l_class_standing;
1266 END get_class_stnd;
1267
1268 FUNCTION is_over_award_occured(
1269 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1270 p_mthd_type VARCHAR2 ,
1271 p_awd_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE
1272 ) RETURN BOOLEAN AS
1273 ------------------------------------------------------------------
1274 --Created by : veramach, Oracle India
1275 --Date created: 07-OCT-2003
1276 --
1277 --Purpose: To check if over award occurs for a person
1278 --
1279 --
1280 --Known limitations/enhancements and/or remarks:
1281 --
1282 --Change History:
1283 --Who When What
1284 -------------------------------------------------------------------
1285
1286 l_unmetneed NUMBER;
1287 l_award NUMBER;
1288
1289 BEGIN
1290 l_unmetneed := NULL;
1291 l_award := NULL;
1292
1293 l_unmetneed := igf_aw_gen_004.unmetneed_f(p_base_id,p_awd_prd_code);
1294 l_award := igf_aw_coa_gen.award_amount(p_base_id,p_awd_prd_code);
1295
1296 IF NVL(p_mthd_type,'ISIR') = 'ISIR' THEN
1297 IF NVL(l_award,0) <> 0 AND l_unmetneed < 0 THEN
1298 RETURN TRUE;
1299 ELSE
1300 RETURN FALSE;
1301 END IF;
1302 ELSIF NVL(p_mthd_type,'ISIR') = 'PROFILE' THEN
1303 IF NVL(l_award,0) <> 0 AND l_unmetneed < 0 THEN
1304 RETURN TRUE;
1305 ELSE
1306 RETURN FALSE;
1307 END IF;
1308 END IF;
1309
1310 END is_over_award_occured;
1311
1312 END igf_aw_packng_subfns;
[Home] [Help]