1 PACKAGE BODY igf_aw_gen_002 AS
2 /* $Header: IGFAW10B.pls 120.3 2006/08/04 07:39:04 veramach ship $ */
3
4 --------------------------------------------------------------------------------------
5 --veramach Oct 2004 FA 152/FA 137 - Changes to wrappers to include Award Period setup
6 -- veramach 24-Aug-2004 FA 145 Obsoleted pell_efc_range
7 -- veramach 08-Apr-2004 bug 3547237
8 -- Obsoleted get_fed_efc. Replaced references with igf_aw_packng_subfns.get_fed_efc
9 -- sjalasut 10 Dec, 2003 FA132 Changes. commented the code for get_sectionii_stdnt
10 -- get_sectionvi_fund, get_sectionvi_stdnt. DID NOT remove
11 -- from the spec as the IGF_AW_FISAP_SECTION_II_V and
12 -- IGF_AW_FISAP_SECTION_VI_V refer them. only commented the
13 -- package body and all these functions return 0
14 -- rasahoo 25-Nov-2003 FA 128 ISIR update. Changed the ereference to paid_efc in
15 -- all cursors and added the decode logic based on award_fmly_contribution_type
16 -- veramach 07-OCT-2003 FA 124
17 -- Chaged cursor resource_cur in get_resource_need
18 -- cdcruz 01-Oct-2003 FA121 - Verification Worksheet changes
19 -- new parameter added to compare_isirs procedure
20 -- rasahoo 02-Sep-2003 FA-114(Obsoletion of FA base record History)
21 -- Removed the join with igf_ap_fa_base_h from appropriate cursors
22 -- cdcruz 17-Mar-2003 Bug 2807235
23 -- Proc comp_fields changed insert_row call if new correction
24 -- else update_row so that same field can be corrected more
25 -- than once
26 --------------------------------------------------------------------------------------
27 -- sjadhav 03-Mar-2003 Bug 2781382
28 -- removed nvl in get_fed_efc
29 --------------------------------------------------------------------------------------
30 FUNCTION get_sectionii_stdnt (p_depend_stat IN igf_lookups_view.lookup_code%TYPE,
31 p_class_standing IN igf_lookups_view.lookup_code%TYPE,
32 p_ci_cal_type IN igs_ca_inst.cal_type%TYPE,
33 p_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE,
34 p_minvalue IN igf_aw_fi_inc_level.minvalue%TYPE,
35 p_maxvalue IN igf_aw_fi_inc_level.maxvalue%TYPE,
36 p_efc IN VARCHAR2)
37 RETURN NUMBER
38 IS
39
40 ------------------------------------------------------------------------------------
41 --Created by : ssawhney ( Oracle IDC)
42 --Date created: 2001/10/23
43 --Purpose: The function would be used to calculate the Student Count for a category
44 --and Income Range. This function is used in the view IGF_AW_FISAP SECTIONII_V
45 --and would retrieve the student count for a specific category which is An Award Year,
46 -- Class Standing, Dependency Status and FISAP Section, combination.
47 --Class Standing is matched with either "bach deg by date" or "deg beyond bach"
48 --
49 --Known limitations/enhancements and/or remarks:
50 --Change History:
51 --Who When What
52 --cdcruz 05-feb-03 Bug# 2758804 FACR105
53 -- all cursors ref changed to pick active isisr
54 --CDCRUZ 06-NOV-02 New procedure get_fed_efc
55 -- added as part of Bug 2613546 FA105/FA108
56 --CDCRUZ 09-Dec-02 procedure get_fed_efc
57 -- modified so that it picks efc from ISIR part of Bug 2676394 FACR107
58 --CDCRUZ 16-Dec-02 Bug# 2691811
59 -- Simulated awards should not be considered for FA Base Summation
60 -- Cursor in get_resource_need proc modified
61
62 -------------------------------------------------------------------------------------
63 -- return -1 if incorrect values passed for CLASS STANDING
64 -- return -2 if incorrect values passed for AUTO_ZERO_EFC
65 -- return -3 if any Unhandled Exception is Raised.
66
67 --This cursor is used for class standing 1 or 2 and AutoZERO EFC is false.
68 --It would retrieve the count of students for the Undergraduate with degree
69 --and Undergraduate without degree.
70
71 /*CURSOR c_count_ungrad_std (
72 cp_depend_stat igf_lookups_view.lookup_code%TYPE,
73 cp_class_standing igf_lookups_view.lookup_code%TYPE,
74 cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
75 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE)
76 IS
77 SELECT COUNT (isir.base_id)
78 FROM igf_ap_isir_matched isir,
79 igf_ap_fa_base_rec fa
80 WHERE isir.dependency_status=cp_depend_stat
81 AND NVL(isir.auto_zero_efc,'N') <> 'Y'
82 AND isir.active_isir ='Y'
83 AND isir.citizenship_status='1'
84 AND fa.ci_cal_type =cp_ci_cal_type
85 AND fa.ci_sequence_number =cp_ci_sequence_number
86 AND fa.base_id=isir.base_id
87 AND DECODE(fa.award_fmly_contribution_type, 2, isir.secondary_efc, isir.primary_efc) IS NOT NULL
88 AND EXISTS ( SELECT base.base_id
89 FROM igf_ap_fa_base_rec_all base,igs_ps_ver_all pv,igs_en_stdnt_ps_att att
90 WHERE base.base_id = isir.base_id
91 AND base.person_id = att.person_id
92 AND att.key_program = 'Y'
93 AND att.course_cd = pv.course_cd
94 AND att.version_number = pv.version_number
95 AND (pv.state_financial_aid='Y' OR pv.institutional_financial_aid='Y' OR pv.federal_financial_aid ='Y' )
96 AND igf_ap_gen_001.get_enrl_program_type(isir.base_id) IN ( SELECT cl.program_type
97 FROM igf_aw_career_map cl
98 WHERE cl.class_standing = cp_class_standing ));
99
100
101 --This cursor is for class standing is 3 and AutoZERO EFC is false.
102 --It would retrieve the data for the students whose career level is Graduate.
103
104 CURSOR c_count_grad_std ( cp_depend_stat igf_lookups_view.lookup_code%TYPE,
105 cp_class_standing igf_lookups_view.lookup_code%TYPE,
106 cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
107 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
108 cp_minvalue igf_aw_fi_inc_level.minvalue%TYPE,
109 cp_maxvalue igf_aw_fi_inc_level.maxvalue%TYPE,
110 cp_efc VARCHAR2 )
111 IS
112 SELECT COUNT (isir.base_id)
113 FROM igf_ap_isir_matched isir,
114 igf_ap_fa_base_rec fa
115 WHERE isir.dependency_status=cp_depend_stat
116 AND NVL(isir.auto_zero_efc,'N') <> 'Y'
117 AND isir.active_isir ='Y'
118 AND isir.citizenship_status='1'
119 AND fa.ci_cal_type =cp_ci_cal_type
120 AND fa.ci_sequence_number =cp_ci_sequence_number
121 AND fa.base_id=isir.base_id
122 AND DECODE(fa.award_fmly_contribution_type, 2, isir.secondary_efc, isir.primary_efc) IS NOT NULL
123 AND EXISTS
124 (SELECT base.base_id
125 FROM igf_ap_fa_base_rec_all base,igs_ps_ver_all pv,igs_en_stdnt_ps_att att
126 WHERE base.base_id = isir.base_id
127 AND base.person_id = att.person_id
128 AND att.key_program = 'Y'
129 AND att.course_cd = pv.course_cd
130 AND att.version_number = pv.version_number
131 AND (pv.state_financial_aid='Y' OR pv.institutional_financial_aid='Y' OR pv.federal_financial_aid ='Y' )
132 AND igf_ap_gen_001.get_enrl_program_type(isir.base_id) IN
133 (SELECT cl.program_type
134 FROM igf_aw_career_map cl
135 WHERE cl.class_standing =cp_class_standing ));
136
137 --This cursor is used for class standing 1 or 2 and AutoZERO EFC is TRUE(Y).
138 --It would retrieve the count of students for the Undergraduate with degree
139 --and Undergraduate without degree
140
141 CURSOR c_count_ungrad_std_efc (
142 cp_depend_stat igf_lookups_view.lookup_code%TYPE,
143 cp_class_standing igf_lookups_view.lookup_code%TYPE,
144 cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
145 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
146 cp_minvalue igf_aw_fi_inc_level.minvalue%TYPE,
147 cp_maxvalue igf_aw_fi_inc_level.maxvalue%TYPE,
148 cp_efc VARCHAR2)
149 IS
150 SELECT COUNT (isir.base_id)
151 FROM igf_ap_isir_matched isir,
152 igf_ap_fa_base_rec fa
153 WHERE isir.dependency_status=cp_depend_stat
154 AND isir.auto_zero_efc = 'Y'
155 AND isir.active_isir ='Y'
156 AND isir.citizenship_status='1'
157 AND fa.ci_cal_type =cp_ci_cal_type
158 AND fa.ci_sequence_number =cp_ci_sequence_number
159 AND fa.base_id=isir.base_id
160 AND DECODE(fa.award_fmly_contribution_type, 2, isir.secondary_efc, isir.primary_efc) IS NOT NULL
161 AND EXISTS
162 (
163 SELECT base.base_id
164 FROM igf_ap_fa_base_rec_all base,
165 igs_ps_ver_all pv,
166 igs_en_stdnt_ps_att att
167 WHERE base.base_id = isir.base_id
168 AND base.person_id = att.person_id
169 AND att.key_program = 'Y'
170 AND att.course_cd = pv.course_cd
171 AND att.version_number = pv.version_number
172 AND (pv.state_financial_aid='Y' OR pv.institutional_financial_aid='Y' OR pv.federal_financial_aid ='Y' )
173 AND igf_ap_gen_001.get_enrl_program_type(isir.base_id) IN
174 (SELECT cl.program_type
175 FROM igf_aw_career_map cl
176 WHERE cl.class_standing =cp_class_standing ));
177 --This cursor is for class standing is 3 and AutoZERO EFC is true (Y).
178 --It would retrieve the data for the students whose career level is Graduate.
179
180 CURSOR c_count_grad_std_efc ( cp_depend_stat igf_lookups_view.lookup_code%TYPE,
181 cp_class_standing igf_lookups_view.lookup_code%TYPE,
182 cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
183 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
184 cp_minvalue igf_aw_fi_inc_level.minvalue%TYPE,
185 cp_maxvalue igf_aw_fi_inc_level.maxvalue%TYPE,
186 cp_efc VARCHAR2)
187 IS
188 SELECT COUNT (isir.base_id)
189 FROM igf_ap_isir_matched isir,
190 igf_ap_fa_base_rec fa
191 WHERE isir.dependency_status=cp_depend_stat
192 AND isir.auto_zero_efc = 'Y'
193 AND isir.active_isir ='Y'
194 AND isir.citizenship_status='1'
195 AND fa.ci_cal_type =cp_ci_cal_type
196 AND fa.ci_sequence_number =cp_ci_sequence_number
197 AND fa.base_id=isir.base_id
198 AND DECODE(fa.award_fmly_contribution_type, 2, isir.secondary_efc, isir.primary_efc) IS NOT NULL
199 AND EXISTS
200 (
201 SELECT base.base_id
202 FROM igf_ap_fa_base_rec_all base,igs_ps_ver_all pv,igs_en_stdnt_ps_att att
203 WHERE base.base_id = isir.base_id
204 AND base.person_id = att.person_id
205 AND att.key_program = 'Y'
206 AND att.course_cd = pv.course_cd
207 AND att.version_number = pv.version_number
208 AND (pv.state_financial_aid='Y' OR pv.institutional_financial_aid='Y' OR pv.federal_financial_aid ='Y' )
209 AND igf_ap_gen_001.get_enrl_program_type(isir.base_id) IN
210 (SELECT cl.program_type
211 FROM igf_aw_career_map cl
212 WHERE cl.class_standing =cp_class_standing ));
213
214 */
215
216 l_std_cnt igf_aw_fisap_ii_h.student_count%TYPE DEFAULT 0;
217 l_class_standing igf_lookups_view.lookup_code%TYPE;
218 l_efc VARCHAR2(10);
219
220 BEGIN
221 -- code commented as part of Fa132. function only returns 0 since this function is being used
222 -- in igf_aw_fisap_section_ii_v and igf_aw_fisap_section_vi_v
223 /*
224 -- copy values of parameters into local variables.
225
226 l_class_standing := p_class_standing;
227 l_efc := p_efc;
228 l_std_cnt := 0; -- initialise the count;
229
230 -- check if the minimum required parameters are passed or not
231 -- return -1 if incorrect values passed for CLASS STANDING
232 -- return -2 if incorrect values passed for AUTO_ZERO_EFC
233
234 IF l_class_standing IS NULL OR
235 l_class_standing NOT IN ('1','2','3') THEN
236 l_std_cnt := -1;
237 RETURN (l_std_cnt);
238 END IF;
239
240 IF l_efc IS NULL OR
241 l_efc NOT IN ('TRUE','FALSE') THEN
242 l_std_cnt := -2;
243 RETURN (l_std_cnt);
244 END IF;
245
246 -- open each cursor to get the count of students.
247 -- the opening of the cursor will depend on the passed p_class_standing and p_efc
248 -- parameters.
249
250 -- open the first cursor, the condition will be, CLASS_STANDING should be
251 -- 1 or 2 and EFC FALSE
252
253 IF l_class_standing IN ('1','2') AND
254 l_efc = 'FALSE' THEN
255 OPEN c_count_ungrad_std(
256 p_depend_stat,
257 p_class_standing ,
258 p_ci_cal_type ,
259 p_ci_sequence_number );
260
261 -- l_std_cnt:=0; -- re initalise the count
262 FETCH c_count_ungrad_std INTO l_std_cnt;
263 IF c_count_ungrad_std%NOTFOUND THEN
264 l_std_cnt:=0;
265 RETURN (l_std_cnt);
266 ELSIF c_count_ungrad_std%FOUND THEN
267 RETURN (l_std_cnt);
268 END IF;
269
270 CLOSE c_count_ungrad_std;
271 -- open the second cursor, the condition will be, CLASS_STANDING should be 3
272 -- and EFC FALSE
273
274 ELSIF
275 l_class_standing ='3' AND
276 l_efc = 'FALSE' THEN
277 OPEN c_count_grad_std(
278 p_depend_stat,
279 p_class_standing ,
280 p_ci_cal_type ,
281 p_ci_sequence_number ,
282 p_minvalue ,
283 p_maxvaluE ,
284 p_efc);
285
286 FETCH c_count_grad_std INTO l_std_cnt;
287 IF c_count_grad_std%NOTFOUND THEN
288 l_std_cnt:=0;
289 RETURN (l_std_cnt);
290 ELSIF c_count_grad_std%FOUND THEN
291 RETURN (l_std_cnt);
292 END IF;
293 CLOSE c_count_grad_std;
294 -- open the third cursor, the condition will be, CLASS_STANDING should be
295 -- 1 or 2 and EFC TRUE
296
297 ELSIF
298 l_class_standing IN ('1','2') AND
299 l_efc = 'TRUE' THEN
300 OPEN c_count_ungrad_std_efc(
301 p_depend_stat,
302 p_class_standing ,
303 p_ci_cal_type ,
304 p_ci_sequence_number ,
305 p_minvalue ,
306 p_maxvaluE ,
307 p_efc);
308
309 FETCH c_count_ungrad_std_efc INTO l_std_cnt;
310 IF c_count_ungrad_std_efc%NOTFOUND THEN
311 l_std_cnt:=0;
312 RETURN (l_std_cnt);
313 ELSIF c_count_ungrad_std_efc%FOUND THEN
314 RETURN (l_std_cnt);
315 END IF;
316 CLOSE c_count_ungrad_std_efc;
317 -- open the fourth cursor, the condition will be, CLASS_STANDING should be 3
318 -- and EFC TRUE
319
320 ELSIF
321 l_class_standing ='3' AND
322 l_efc = 'TRUE' THEN
323 OPEN c_count_grad_std_efc(
324 p_depend_stat,
325 p_class_standing ,
326 p_ci_cal_type ,
327 p_ci_sequence_number ,
328 p_minvalue ,
329 p_maxvaluE ,
330 p_efc);
331
332 FETCH c_count_grad_std_efc INTO l_std_cnt;
333 IF c_count_grad_std_efc%NOTFOUND THEN
334 l_std_cnt:=0;
335 RETURN (l_std_cnt);
336 ELSIF c_count_grad_std_efc%FOUND THEN
337 RETURN (l_std_cnt);
338 END IF;
339 CLOSE c_count_grad_std_efc;
340 END IF;*/
341 RETURN 0;
342
343 EXCEPTION
344 WHEN OTHERS THEN
345 /*IF c_count_grad_std_efc%ISOPEN THEN
346 CLOSE c_count_grad_std_efc;
347 END IF;
348 IF c_count_grad_std%ISOPEN THEN
349 CLOSE c_count_grad_std;
350 END IF;
351 IF c_count_ungrad_std_efc%ISOPEN THEN
352 CLOSE c_count_ungrad_std_efc;
353 END IF;
354 IF c_count_ungrad_std%ISOPEN THEN
355 CLOSE c_count_ungrad_std;
356 END IF;*/
357 RETURN (-3); -- returning -3 means Unhandled exception raised
358
359 END get_sectionii_stdnt;
360
361
362 FUNCTION get_sectionvi_fund ( p_rec_type IN igf_aw_fisap_vi_h.rec_type%TYPE,
363 p_fund_type IN igf_aw_award_v.fed_fund_code%TYPE,
364 p_depend_stat IN igf_lookups_view.lookup_code%TYPE,
365 p_class_standing IN igf_lookups_view.lookup_code%TYPE,
366 p_ci_cal_type IN igs_ca_inst.cal_type%TYPE,
367 p_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE,
368 p_minvalue IN igf_aw_fi_inc_level.minvalue%TYPE,
369 p_maxvalue IN igf_aw_fi_inc_level.maxvalue%TYPE )
370 RETURN NUMBER
371 IS
372
373 ------------------------------------------------------------------------------------
374 --Created by : ssawhney ( Oracle IDC)
375 --Date created: 2001/10/23
376 --Purpose: The function would be used to calculate the Total Fund Amount for a category
377 --and Income Range. This function is used in the view IGF_AW_FISAP SECTIONVI_V
378 --and would retrieve the sum total for a specific category which is An Award Year,
379 --Class Standing, Dependency Status and FISAP Section, combination.
380 --Class Standing will be 4 for SectionVI records, so we will not check explicitly
381 --
382 --Known limitations/enhancements and/or remarks:
383 --Change History:
384 --Who When What
385 -------------------------------------------------------------------------------------
386
387 -- return -1 if incorrect values passed for CLASS STANDING
388 -- return -2 if incorrect values passed for AUTO_ZERO_EFC
389 -- return -3 if any Unhandled Exception is Raised.
390 /*
391 CURSOR c_non_proff_fund_count (
392 cp_rec_type igf_aw_fisap_vi_h.rec_type%TYPE,
393 cp_fund_type igf_aw_award_v.fed_fund_code%TYPE,
394 cp_depend_stat igf_lookups_view.lookup_code%TYPE,
395 cp_class_standing igf_lookups_view.lookup_code%TYPE,
396 cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
397 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
398 cp_minvalue igf_aw_fi_inc_level.minvalue%TYPE,
399 cp_maxvalue igf_aw_fi_inc_level.maxvalue%TYPE )
400 IS
401 SELECT NVL(SUM(awd.paid_amt),0)
402 FROM igf_ap_isir_matched isir,
403 igf_aw_award_v awd,
404 igf_ap_fa_base_rec fa
405 WHERE awd.base_id =isir.base_id
406 AND fa.base_id =isir.base_id
407 AND isir.dependency_status =p_depend_stat
408 AND awd.fed_fund_code =cp_fund_type
409 AND awd.ci_cal_type =cp_ci_cal_type
410 AND awd.ci_sequence_number =cp_ci_sequence_number
411 AND NVL(isir.auto_zero_efc,'N') <> 'Y'
412 AND isir.active_isir ='Y'
413 AND isir.citizenship_status='1'
414 AND DECODE(fa.award_fmly_contribution_type, 2, isir.secondary_efc, isir.primary_efc) IS NOT NULL
415 AND awd.award_status IN ('ACCEPTED','OFFERED')
416 AND igf_ap_gen_001.get_derived_attend_type(awd.base_id) ='FT'
417 AND igf_ap_gen_001.get_enrl_program_type(awd.base_id) IN
418 (SELECT cl.program_type FROM igf_aw_career_map cl
419 WHERE cl.class_standing IN ('1','2') )
420 AND isir.fti BETWEEN cp_minvalue AND cp_maxvalue
421 AND cp_rec_type ='NON_PROFESSIONAL';
422
423
424 CURSOR c_proff_fund_count ( cp_rec_type igf_aw_fisap_vi_h.rec_type%TYPE,
425 cp_fund_type igf_aw_award_v.fed_fund_code%TYPE,
426 cp_depend_stat igf_lookups_view.lookup_code%TYPE,
427 cp_class_standing igf_lookups_view.lookup_code%TYPE,
428 cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
429 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
430 cp_minvalue igf_aw_fi_inc_level.minvalue%TYPE,
431 cp_maxvalue igf_aw_fi_inc_level.maxvalue%TYPE )
432 IS
433 SELECT NVL(SUM(awd.paid_amt),0)
434 FROM igf_ap_isir_matched isir,
435 igf_aw_award_v awd,
436 igf_ap_fa_base_rec fa
437 WHERE awd.base_id =isir.base_id
438 AND fa.base_id =isir.base_id
439 AND isir.dependency_status =p_depend_stat
440 AND awd.fed_fund_code =cp_fund_type
441 AND awd.ci_cal_type =cp_ci_cal_type
442 AND awd.ci_sequence_number =cp_ci_sequence_number
443 AND NVL(isir.auto_zero_efc,'N') <> 'Y'
444 AND isir.active_isir ='Y'
445 AND isir.citizenship_status='1'
446 AND DECODE(fa.award_fmly_contribution_type, 2, isir.secondary_efc, isir.primary_efc) IS NOT NULL
447 AND awd.award_status IN ('ACCEPTED','OFFERED')
448 AND igf_ap_gen_001.get_derived_attend_type(awd.base_id) ='FT'
449 AND igf_ap_gen_001.get_enrl_program_type(awd.base_id) IN
450 (SELECT cl.program_type
451 FROM igf_aw_career_map cl
452 WHERE cl.class_standing ='3' )
453 AND cp_rec_type ='PROFESSIONAL';
454
455 CURSOR c_less_ft_fund_count (
456 cp_rec_type igf_aw_fisap_vi_h.rec_type%TYPE,
457 cp_fund_type igf_aw_award_v.fed_fund_code%TYPE,
458 cp_depend_stat igf_lookups_view.lookup_code%TYPE,
459 cp_class_standing igf_lookups_view.lookup_code%TYPE,
460 cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
461 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
462 cp_minvalue igf_aw_fi_inc_level.minvalue%TYPE,
463 cp_maxvalue igf_aw_fi_inc_level.maxvalue%TYPE )
464 IS
465 SELECT NVL(SUM(awd.paid_amt),0)
466 FROM igf_ap_isir_matched isir,
467 igf_aw_award_v awd,
468 igf_ap_fa_base_rec fa
469 WHERE awd.base_id =isir.base_id
470 AND fa.base_id =isir.base_id
471 AND isir.dependency_status IN ('I','D')
472 AND awd.fed_fund_code =cp_fund_type
473 AND awd.ci_cal_type =cp_ci_cal_type
474 AND awd.ci_sequence_number =cp_ci_sequence_number
475 AND awd.award_status IN ('ACCEPTED','OFFERED')
476 AND NVL(isir.auto_zero_efc,'N') <> 'Y'
477 AND isir.active_isir ='Y'
478 AND isir.citizenship_status='1'
479 AND DECODE(fa.award_fmly_contribution_type, 2, isir.secondary_efc, isir.primary_efc) IS NOT NULL
480 AND NVL(igf_ap_gen_001.get_derived_attend_type(awd.base_id),'N') <>'FT'
481 AND igf_ap_gen_001.get_enrl_program_type(awd.base_id) IN
482 (SELECT cl.program_type
483 FROM igf_aw_career_map cl
484 WHERE cl.class_standing IN ('1','2','3') )
485 AND cp_rec_type ='LESS_THAN_FULL_TIME';
486
487 CURSOR c_auto_efc_fund_count (
488 cp_rec_type igf_aw_fisap_vi_h.rec_type%TYPE,
489 cp_fund_type igf_aw_award_v.fed_fund_code%TYPE,
490 cp_depend_stat igf_lookups_view.lookup_code%TYPE,
491 cp_class_standing igf_lookups_view.lookup_code%TYPE,
492 cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
493 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
494 cp_minvalue igf_aw_fi_inc_level.minvalue%TYPE,
495 cp_maxvalue igf_aw_fi_inc_level.maxvalue%TYPE )
496 IS
497 SELECT NVL(SUM(awd.paid_amt),0)
498 FROM igf_ap_isir_matched isir,
499 igf_aw_award_v awd,
500 igf_ap_fa_base_rec fa
501 WHERE awd.base_id =isir.base_id
502 AND fa.base_id =isir.base_id
503 AND isir.dependency_status IN ('I','D')
504 AND awd.ci_cal_type =cp_ci_cal_type
505 AND awd.ci_sequence_number =cp_ci_sequence_number
506 AND awd.fed_fund_code =cp_fund_type
507 AND awd.award_status IN ('ACCEPTED','OFFERED')
508 AND isir.auto_zero_efc = 'Y'
509 AND isir.active_isir ='Y'
510 AND isir.citizenship_status='1'
511 AND DECODE(fa.award_fmly_contribution_type, 2, isir.secondary_efc, isir.primary_efc) IS NOT NULL
512 AND igf_ap_gen_001.get_derived_attend_type(awd.base_id) = 'FT'
513 AND igf_ap_gen_001.get_enrl_program_type(awd.base_id) IN
514 (SELECT cl.program_type FROM igf_aw_career_map cl
515 WHERE cl.class_standing IN ('1','2','3') )
516 AND cp_rec_type ='AUTO_ZERO';
517 */
518
519 l_fnd_cnt igf_aw_fisap_vi_h.fseog_fund_total%TYPE DEFAULT 0;
520 -- fund total is same data type for
521 -- all the 3 funds.
522
523 l_rec_type igf_aw_fisap_vi_h.rec_type%TYPE;
524 l_fund_type igf_aw_award_v.fed_fund_code%TYPE;
525
526 BEGIN
527 -- code commented as part of Fa132. function only returns 0 since this function is being used
528 -- in igf_aw_fisap_section_ii_v and igf_aw_fisap_section_vi_v
529 /*
530
531 -- copy values of parameters into local variables.
532
533 l_rec_type := p_rec_type;
534 l_fund_type := p_fund_type;
535 l_fnd_cnt :=0; -- initialise the count
536
537 -- check if the minimum required parameters are passed or not
538 -- return -1 if incorrect values passed for REC_TYPE
539 -- return -2 if incorrect values passed for FUND_TYPE
540
541 IF l_rec_type IS NULL OR
542 l_rec_type NOT IN ('AUTO_ZERO','LESS_THAN_FULL_TIME',
543 'PROFESSIONAL','NON_PROFESSIONAL') THEN
544 l_fnd_cnt := -1;
545 RETURN (l_fnd_cnt);
546 END IF;
547
548 IF l_fund_type IS NULL OR
549 l_fund_type NOT IN ('UNDUPL','FSEOG','FWS','PRK') THEN
550 l_fnd_cnt := -2;
551 RETURN (l_fnd_cnt);
552 END IF;
553
554 -- For class standing 4 (ie profession and graduate) and if the Fund is FSEOG then return
555 -- Null/0, this is handled in the form.
556
557 -- open each cursor to get the fund total for students.
558 -- the opening of the cursor will depend on the passed p_rec_type and p_fund_type
559 -- parameters.
560
561 -- open first cursor, count of all students having any of the funds in
562 -- FWS, PRK,FSEOG and un duplicate records, and non professional
563
564 IF l_rec_type ='NON_PROFESSIONAL' AND
565 l_fund_type <> 'UNDUPL' THEN
566 OPEN c_non_proff_fund_count(
567 p_rec_type,
568 p_fund_type,
569 p_depend_stat,
570 p_class_standing ,
571 p_ci_cal_type ,
572 p_ci_sequence_number ,
573 p_minvalue ,
574 p_maxvalue
575 );
576 FETCH c_non_proff_fund_count INTO l_fnd_cnt;
577 IF c_non_proff_fund_count%NOTFOUND THEN
578 l_fnd_cnt:=0;
579 RETURN (l_fnd_cnt);
580 ELSIF c_non_proff_fund_count%FOUND THEN
581 RETURN (l_fnd_cnt);
582 END IF;
583 CLOSE c_non_proff_fund_count;
584
585 -- open second cursor, count of all students having any of the funds in
586 -- FWS, PRK,FSEOG and un duplicate records, and professional
587
588 ELSIF
589 l_rec_type ='PROFESSIONAL' AND
590 l_fund_type <> 'UNDUPL' THEN
591 OPEN c_proff_fund_count(
592 p_rec_type,
593 p_fund_type,
594 p_depend_stat,
595 p_class_standing ,
596 p_ci_cal_type ,
597 p_ci_sequence_number ,
598 p_minvalue ,
599 p_maxvalue
600 );
601 FETCH c_proff_fund_count INTO l_fnd_cnt;
602 IF c_proff_fund_count%NOTFOUND THEN
603 l_fnd_cnt:=0;
604 RETURN (l_fnd_cnt);
605 ELSIF c_proff_fund_count%FOUND THEN
606 RETURN (l_fnd_cnt);
607 END IF;
608 CLOSE c_proff_fund_count;
609
610 -- open third cursor, count of all students having any of the funds in
611 -- FWS, PRK,FSEOG and un duplicate records, and less than full time
612
613 ELSIF
614 l_rec_type ='LESS_THAN_FULL_TIME' AND
615 l_fund_type <> 'UNDUPL' THEN
616 OPEN c_less_ft_fund_count(
617 p_rec_type,
618 p_fund_type,
619 p_depend_stat,
620 p_class_standing ,
621 p_ci_cal_type ,
622 p_ci_sequence_number ,
623 p_minvalue ,
624 p_maxvalue
625 );
626 FETCH c_less_ft_fund_count INTO l_fnd_cnt;
627 IF c_less_ft_fund_count%NOTFOUND THEN
628 l_fnd_cnt:=0;
629 RETURN (l_fnd_cnt);
630 ELSIF c_less_ft_fund_count%FOUND THEN
631 RETURN (l_fnd_cnt);
632 END IF;
633 CLOSE c_less_ft_fund_count;
634
635 -- open fourth cursor, count of all students having any of the funds in
636 -- FWS, PRK,FSEOG and un duplicate records, and auto zero
637
638 ELSIF
639 l_rec_type ='AUTO_ZERO' AND
640 l_fund_type <> 'UNDUPL' THEN
641 OPEN c_auto_efc_fund_count(
642 p_rec_type,
643 p_fund_type,
644 p_depend_stat,
645 p_class_standing ,
646 p_ci_cal_type ,
647 p_ci_sequence_number ,
648 p_minvalue ,
649 p_maxvalue
650 );
651 FETCH c_auto_efc_fund_count INTO l_fnd_cnt;
652 IF c_auto_efc_fund_count%NOTFOUND THEN
653 l_fnd_cnt:=0;
654 RETURN (l_fnd_cnt);
655 ELSIF c_auto_efc_fund_count%FOUND THEN
656 RETURN (l_fnd_cnt);
657 END IF;
658 CLOSE c_auto_efc_fund_count;
659
660 END IF;*/
661 RETURN 0;
662
663 EXCEPTION
664 WHEN OTHERS THEN
665 /* IF c_non_proff_fund_count%ISOPEN THEN
666 CLOSE c_non_proff_fund_count;
667 END IF;
668 IF c_proff_fund_count%ISOPEN THEN
669 CLOSE c_proff_fund_count;
670 END IF;
671 IF c_less_ft_fund_count%ISOPEN THEN
672 CLOSE c_less_ft_fund_count;
673 END IF;
674 IF c_auto_efc_fund_count%ISOPEN THEN
675 CLOSE c_auto_efc_fund_count;
676 END IF;*/
677 RETURN (-3); -- returning -3 means Unhandled exception raised
678
679 END get_sectionvi_fund;
680
681
682 FUNCTION get_sectionvi_stdnt (p_rec_type IN igf_aw_fisap_vi_h.rec_type%TYPE,
683 p_fund_type IN igf_aw_award_v.fed_fund_code%TYPE,
684 p_depend_stat IN igf_lookups_view.lookup_code%TYPE,
685 p_class_standing IN igf_lookups_view.lookup_code%TYPE,
686 p_ci_cal_type IN igs_ca_inst.cal_type%TYPE,
687 p_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE,
688 p_minvalue IN igf_aw_fi_inc_level.minvalue%TYPE,
689 p_maxvalue IN igf_aw_fi_inc_level.maxvalue%TYPE )
690 RETURN NUMBER
691 IS
692
693 ------------------------------------------------------------------------------------
694 --Created by : ssawhney ( Oracle IDC)
695 --Date created: 2001/10/23
696 --Purpose: The function would be used to calculate the Total Count of Students
697 --for a category and Income Range. This function is used in the view
698 --IGF_AW_FISAP SECTIONVI_V
699 --and would retrieve the student count for a specific category which is An Award Year,
700 --Class Standing, Dependency Status and FISAP Section, combination.
701 --
702 --Known limitations/enhancements and/or remarks:
703 --Change History:
704 --Who When What
705 -------------------------------------------------------------------------------------
706 -- return -1 if incorrect values passed for REC_TYPE
707 -- return -2 if incorrect values passed for FUND_TYPE
708 -- return -3 if any Unhandled Exception is Raised.
709
710 -- This cursor would check for the fund types "FWS, PRK, FSEOG"
711 -- and class Standing as 4 ( 4 combination of 1 and 2) and REC_TYPE as "NON_PROFESSIONAL"
712 /*
713 CURSOR c_non_proff_std_count (cp_rec_type igf_aw_fisap_vi_h.rec_type%TYPE,
714 cp_fund_type igf_aw_award_v.fed_fund_code%TYPE,
715 cp_depend_stat igf_lookups_view.lookup_code%TYPE,
716 cp_class_standing igf_lookups_view.lookup_code%TYPE,
717 cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
718 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
719 cp_minvalue igf_aw_fi_inc_level.minvalue%TYPE,
720 cp_maxvalue igf_aw_fi_inc_level.maxvalue%TYPE )
721 IS
722 SELECT COUNT (isir.base_id )
723 FROM igf_ap_isir_matched isir,
724 igf_aw_award_v awd,
725 igf_ap_fa_base_rec fa
726 WHERE isir.dependency_status =cp_depend_stat
727 AND fa.base_id =isir.base_id
728 AND awd.base_id = isir.base_id
729 AND NVL(isir.auto_zero_efc,'N') <> 'Y'
730 AND awd.fed_fund_code = cp_fund_type
731 AND awd.ci_cal_type =cp_ci_cal_type
732 AND awd.ci_sequence_number =cp_ci_sequence_number
733 AND awd.award_status IN ('ACCEPTED','OFFERED')
734 AND isir.active_isir ='Y'
735 AND isir.citizenship_status='1'
736 AND DECODE(fa.award_fmly_contribution_type, 2, isir.secondary_efc, isir.primary_efc) IS NOT NULL
737 AND igf_ap_gen_001.get_derived_attend_type(awd.base_id) ='FT'
738 AND igf_ap_gen_001.get_enrl_program_type(awd.base_id) IN
739 (SELECT cl.program_type
740 FROM igf_aw_career_map cl
741 WHERE cl.class_standing IN ('1','2') )
742 AND isir.fti between cp_minvalue and cp_maxvalue
743 AND cp_rec_type ='NON_PROFESSIONAL';
744
745
746 -- This cursor will get for dependency Status " I" and the
747 -- p_rec_type ='PROFESSIONAL' and the fund_type 'FWS' or 'PRK'
748 -- there is no FSEOG fund for Profession cat.
749
750 CURSOR c_proff_std_count (cp_rec_type igf_aw_fisap_vi_h.rec_type%TYPE,
751 cp_fund_type igf_aw_award_v.fed_fund_code%TYPE,
752 cp_depend_stat igf_lookups_view.lookup_code%TYPE,
753 cp_class_standing igf_lookups_view.lookup_code%TYPE,
754 cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
755 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
756 cp_minvalue igf_aw_fi_inc_level.minvalue%TYPE,
757 cp_maxvalue igf_aw_fi_inc_level.maxvalue%TYPE )
758 IS
759 SELECT COUNT (isir.base_id )
760 FROM igf_ap_isir_matched isir,
761 igf_aw_award_v awd,
762 igf_ap_fa_base_rec fa
763 WHERE isir.dependency_status =cp_depend_stat
764 AND awd.base_id= isir.base_id
765 AND fa.base_id = isir.base_id
766 AND awd.fed_fund_code = cp_fund_type
767 AND awd.ci_cal_type = cp_ci_cal_type
768 AND awd.ci_sequence_number =cp_ci_sequence_number
769 AND awd.award_status IN ('ACCEPTED','OFFERED')
770 AND NVL(isir.auto_zero_efc,'N') <> 'Y'
771 AND isir.active_isir ='Y'
772 AND isir.citizenship_status='1'
773 AND DECODE(fa.award_fmly_contribution_type, 2, isir.secondary_efc, isir.primary_efc) IS NOT NULL
774 AND igf_ap_gen_001.get_derived_attend_type(awd.base_id) ='FT'
775 AND igf_ap_gen_001.get_enrl_program_type(awd.base_id) IN
776 (SELECT cl.program_type
777 FROM igf_aw_career_map cl
778 WHERE cl.class_standing ='3' )
779 AND cp_rec_type ='PROFESSIONAL';
780
781
782 --For total Less than full Time students
783 --The dependency status passed to this "B", valid fed_fund_code and not 'UNDUPL'
784 --and class standing passed would be GRAD-UGRAD
785 CURSOR c_less_ft_std_count (cp_rec_type igf_aw_fisap_vi_h.rec_type%TYPE,
786 cp_fund_type igf_aw_award_v.fed_fund_code%TYPE,
787 cp_depend_stat igf_lookups_view.lookup_code%TYPE,
788 cp_class_standing igf_lookups_view.lookup_code%TYPE,
789 cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
790 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
791 cp_minvalue igf_aw_fi_inc_level.minvalue%TYPE,
792 cp_maxvalue igf_aw_fi_inc_level.maxvalue%TYPE )
793 IS
794 SELECT COUNT (isir.base_id )
795 FROM igf_ap_isir_matched isir,
796 igf_aw_award_v awd,
797 igf_ap_fa_base_rec fa
798 WHERE isir.dependency_status IN ('I','D')
799 AND awd.base_id = isir.base_id
800 AND fa.base_id =isir.base_id
801 AND NVL(isir.auto_zero_efc,'N') <> 'Y'
802 AND awd.fed_fund_code = cp_fund_type
803 AND awd.ci_cal_type =cp_ci_cal_type
804 AND awd.ci_sequence_number =cp_ci_sequence_number
805 AND awd.award_status IN ('ACCEPTED','OFFERED')
806 AND isir.active_isir ='Y'
807 AND isir.citizenship_status='1'
808 AND DECODE(fa.award_fmly_contribution_type, 2, isir.secondary_efc, isir.primary_efc) IS NOT NULL
809 AND NVL(igf_ap_gen_001.get_derived_attend_type(awd.base_id),'N') <> 'FT'
810 AND igf_ap_gen_001.get_enrl_program_type(awd.base_id) IN
811 (SELECT cl.program_type
812 FROM igf_aw_career_map cl
813 WHERE cl.class_standing IN ('1','2','3') )
814 AND p_rec_type ='LESS_THAN_FULL_TIME';
815
816 --The dependency status passed to this "B", valid
817 --FED_FUND_CODE and not 'UNDUPL' and class standing passed would be GRAD-UGRAD
818
819 CURSOR c_auto_efc_std_count ( cp_rec_type igf_aw_fisap_vi_h.rec_type%TYPE,
820 cp_fund_type igf_aw_award_v.fed_fund_code%TYPE,
821 cp_depend_stat igf_lookups_view.lookup_code%TYPE,
822 cp_class_standing igf_lookups_view.lookup_code%TYPE,
823 cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
824 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
825 cp_minvalue igf_aw_fi_inc_level.minvalue%TYPE,
826 cp_maxvalue igf_aw_fi_inc_level.maxvalue%TYPE )
827 IS
828 SELECT COUNT ( isir.base_id )
829 FROM igf_ap_isir_matched isir,
830 igf_aw_award_v awd,
831 igf_ap_fa_base_rec fa
832 WHERE isir.dependency_status IN ('I','D')
833 AND awd.base_id = isir.base_id
834 AND fa.base_id =isir.base_id
835 AND awd.fed_fund_code = cp_fund_type
836 AND awd.ci_cal_type =cp_ci_cal_type
837 AND awd.ci_sequence_number =cp_ci_sequence_number
838 AND awd.award_status IN ('ACCEPTED','OFFERED')
839 AND isir.auto_zero_efc = 'Y'
840 AND isir.active_isir ='Y'
841 AND isir.citizenship_status='1'
842 AND DECODE(fa.award_fmly_contribution_type, 2, isir.secondary_efc, isir.primary_efc) IS NOT NULL
843 AND igf_ap_gen_001.get_derived_attend_type(awd.base_id) = 'FT'
844 AND igf_ap_gen_001.get_enrl_program_type(awd.base_id) IN
845 (SELECT cl.program_type
846 FROM igf_aw_career_map cl
847 WHERE cl.class_standing IN ('1','2','3') )
848 AND cp_rec_type ='AUTO_ZERO';
849 -- This cursor would check for the fund types "FWS, PRK, FSEOG"
850 -- and class Standing as 4 and REC_TYPE as "NON_PROFESSIONAL" and Unduplicate student
851
852 CURSOR c_non_proff_und_std_count (cp_rec_type igf_aw_fisap_vi_h.rec_type%TYPE,
853 cp_fund_type igf_aw_award_v.fed_fund_code%TYPE,
854 cp_depend_stat igf_lookups_view.lookup_code%TYPE,
855 cp_class_standing igf_lookups_view.lookup_code%TYPE,
856 cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
857 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
858 cp_minvalue igf_aw_fi_inc_level.minvalue%TYPE,
859 cp_maxvalue igf_aw_fi_inc_level.maxvalue%TYPE )
860 IS
861 SELECT COUNT ( DISTINCT (isir.base_id ) )
862 FROM igf_ap_isir_matched isir,
863 igf_aw_award_v awd,
864 igf_ap_fa_base_rec fa
865 WHERE isir.dependency_status = cp_depend_stat
866 AND awd.base_id = isir.base_id
867 AND fa.base_id =isir.base_id
868 AND awd.fed_fund_code IN ('FWS','FSEOG','PRK')
869 AND awd.ci_cal_type =cp_ci_cal_type
870 AND awd.ci_sequence_number =cp_ci_sequence_number
871 AND awd.award_status IN ('ACCEPTED','OFFERED')
872 AND NVL(isir.auto_zero_efc,'N') <> 'Y'
873 AND isir.active_isir ='Y'
874 AND isir.citizenship_status='1'
875 AND DECODE(fa.award_fmly_contribution_type, 2, isir.secondary_efc, isir.primary_efc) IS NOT NULL
876 AND igf_ap_gen_001.get_derived_attend_type(awd.base_id) ='FT'
877 AND igf_ap_gen_001.get_enrl_program_type(awd.base_id) IN
878 (SELECT cl.program_type
879 FROM igf_aw_career_map cl
880 WHERE cl.class_standing IN ('1','2') )
881 AND isir.fti between cp_minvalue and cp_maxvalue
882 AND cp_rec_type ='NON_PROFESSIONAL';
883
884
885 -- This cursor will get for dependency Status " I" and the
886 -- p_rec_type ='PROFESSIONAL' and the fund_type 'FWS' or 'PRK' and Unduplicate
887 -- there is no FSEOG fund for Profession cat.
888
889 CURSOR c_proff_und_std_count (cp_rec_type igf_aw_fisap_vi_h.rec_type%TYPE,
890 cp_fund_type igf_aw_award_v.fed_fund_code%TYPE,
891 cp_depend_stat igf_lookups_view.lookup_code%TYPE,
892 cp_class_standing igf_lookups_view.lookup_code%TYPE,
893 cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
894 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
895 cp_minvalue igf_aw_fi_inc_level.minvalue%TYPE,
896 cp_maxvalue igf_aw_fi_inc_level.maxvalue%TYPE ) IS
897 SELECT COUNT (DISTINCT (isir.base_id ) )
898 FROM igf_ap_isir_matched isir,
899 igf_aw_award_v awd,
900 igf_ap_fa_base_rec fa
901 WHERE isir.dependency_status =cp_depend_stat
902 AND awd.base_id = isir.base_id
903 AND fa.base_id =isir.base_id
904 AND awd.fed_fund_code IN ('FWS','FSEOG','PRK')
905 AND awd.ci_cal_type =cp_ci_cal_type
906 AND awd.ci_sequence_number =cp_ci_sequence_number
907 AND awd.award_status IN ('ACCEPTED','OFFERED')
908 AND NVL(isir.auto_zero_efc,'N') <> 'Y'
909 AND isir.active_isir ='Y'
910 AND isir.citizenship_status='1'
911 AND DECODE(fa.award_fmly_contribution_type, 2, isir.secondary_efc, isir.primary_efc) IS NOT NULL
912 AND igf_ap_gen_001.get_derived_attend_type(awd.base_id) ='FT'
913 AND igf_ap_gen_001.get_enrl_program_type(awd.base_id) IN
914 (SELECT program_type
915 FROM igf_aw_career_map cl
916 WHERE cl.class_standing ='3' )
917 AND cp_rec_type ='PROFESSIONAL';
918
919
920 --For total Less than full Time students
921 --The dependency status passed to this "B", valid fed_fund_code and 'UNDUPL'
922 --and class standing passed would be GRAD-UGRAD
923
924 CURSOR c_less_ft_und_std_count (cp_rec_type igf_aw_fisap_vi_h.rec_type%TYPE,
925 cp_fund_type igf_aw_award_v.fed_fund_code%TYPE,
926 cp_depend_stat igf_lookups_view.lookup_code%TYPE,
927 cp_class_standing igf_lookups_view.lookup_code%TYPE,
928 cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
929 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
930 cp_minvalue igf_aw_fi_inc_level.minvalue%TYPE,
931 cp_maxvalue igf_aw_fi_inc_level.maxvalue%TYPE )
932 IS
933 SELECT COUNT (DISTINCT(isir.base_id ))
934 FROM igf_ap_isir_matched isir,
935 igf_aw_award_v awd,
936 igf_ap_fa_base_rec fa
937 WHERE isir.dependency_status IN ('I','D')
938 AND awd.base_id = isir.base_id
939 AND fa.base_id = isir.base_id
940 AND awd.fed_fund_code IN ('FWS','FSEOG','PRK')
941 AND awd.ci_cal_type =cp_ci_cal_type
942 AND awd.ci_sequence_number =cp_ci_sequence_number
943 AND awd.award_status IN ('ACCEPTED','OFFERED')
944 AND NVL(isir.auto_zero_efc,'N') <> 'Y'
945 AND isir.active_isir ='Y'
946 AND isir.citizenship_status='1'
947 AND DECODE(fa.award_fmly_contribution_type, 2, isir.secondary_efc, isir.primary_efc) IS NOT NULL
948 AND NVL(igf_ap_gen_001.get_derived_attend_type(awd.base_id),'N') <> 'FT'
949 AND igf_ap_gen_001.get_enrl_program_type(awd.base_id) IN
950 (SELECT cl.program_type
951 FROM igf_aw_career_map cl
952 WHERE cl.class_standing IN ('1','2','3') )
953 AND cp_rec_type ='LESS_THAN_FULL_TIME';
954
955
956 --The dependency status passed to this "B", valid
957 --FED_FUND_CODE and 'UNDUPL' and class standing passed would be GRAD-UGRAD
958
959 CURSOR c_auto_efc_und_std_count ( cp_rec_type igf_aw_fisap_vi_h.rec_type%TYPE,
960 cp_fund_type igf_aw_award_v.fed_fund_code%TYPE,
961 cp_depend_stat igf_lookups_view.lookup_code%TYPE,
962 cp_class_standing igf_lookups_view.lookup_code%TYPE,
963 cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
964 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
965 cp_minvalue igf_aw_fi_inc_level.minvalue%TYPE,
966 cp_maxvalue igf_aw_fi_inc_level.maxvalue%TYPE )
967 IS
968 SELECT COUNT (DISTINCT(isir.base_id ))
969 FROM igf_ap_isir_matched isir,
970 igf_aw_award_v awd,
971 igf_ap_fa_base_rec fa
972 WHERE isir.dependency_status IN ('I','D')
973 AND awd.fed_fund_code IN ('FWS','FSEOG','PRK')
974 AND awd.ci_cal_type =cp_ci_cal_type
975 AND awd.ci_sequence_number =cp_ci_sequence_number
976 AND awd.award_status IN ('ACCEPTED','OFFERED')
977 AND awd.base_id = isir.base_id
978 AND fa.base_id =isir.base_id
979 AND isir.auto_zero_efc = 'Y'
980 AND isir.active_isir ='Y'
981 AND isir.citizenship_status='1'
982 AND DECODE(fa.award_fmly_contribution_type, 2, isir.secondary_efc, isir.primary_efc) IS NOT NULL
983 AND igf_ap_gen_001.get_derived_attend_type(awd.base_id) = 'FT'
984 AND igf_ap_gen_001.get_enrl_program_type(awd.base_id) IN
985 (SELECT cl.program_type
986 FROM igf_aw_career_map cl
987 WHERE cl.class_standing IN ('1','2','3') )
988 AND cp_rec_type ='AUTO_ZERO';
989 */
990 l_std_cnt igf_aw_fisap_ii_h.student_count%TYPE DEFAULT 0;
991 l_rec_type igf_aw_fisap_vi_h.rec_type%TYPE;
992 l_fund_type igf_aw_award_v.fed_fund_code%TYPE;
993
994 BEGIN
995 -- code commented as part of Fa132. function only returns 0 since this function is being used
996 -- in igf_aw_fisap_section_ii_v and igf_aw_fisap_section_vi_v
997 /*
998
999 -- copy values of parameters into local variables.
1000
1001 l_rec_type := p_rec_type;
1002 l_fund_type := p_fund_type;
1003
1004 -- check if the minimum required parameters are passed or not
1005 -- return -1 if incorrect values passed for REC_TYPE
1006 -- return -2 if incorrect values passed for FUND_TYPE
1007
1008 IF l_rec_type IS NULL OR
1009 l_rec_type NOT IN ('AUTO_ZERO','LESS_THAN_FULL_TIME',
1010 'PROFESSIONAL','NON_PROFESSIONAL') THEN
1011 l_std_cnt := -1;
1012 RETURN (l_std_cnt);
1013 END IF;
1014
1015 IF l_fund_type IS NULL OR
1016 l_fund_type NOT IN ('UNDUPL','FSEOG','FWS','PRK') THEN
1017 l_std_cnt := -2;
1018 RETURN (l_std_cnt);
1019 END IF;
1020
1021 -- open each cursor to get the count of students.
1022 -- the opening of the cursor will depend on the passed p_rec_type and p_fund_type
1023 -- parameters.
1024
1025 -- open first cursor, count of all students having any of the funds in
1026 -- FWS, PRK,FSEOG and un duplicate records, and non professional
1027
1028 IF l_rec_type ='NON_PROFESSIONAL' AND
1029 l_fund_type <> 'UNDUPL' THEN
1030 OPEN c_non_proff_std_count(
1031 p_rec_type,
1032 p_fund_type,
1033 p_depend_stat,
1034 p_class_standing ,
1035 p_ci_cal_type ,
1036 p_ci_sequence_number ,
1037 p_minvalue ,
1038 p_maxvalue
1039 );
1040 FETCH c_non_proff_std_count INTO l_std_cnt;
1041 IF c_non_proff_std_count%NOTFOUND THEN
1042 l_std_cnt:=0;
1043 RETURN (l_std_cnt);
1044 ELSIF c_non_proff_std_count%FOUND THEN
1045 RETURN (l_std_cnt);
1046 END IF;
1047 CLOSE c_non_proff_std_count;
1048
1049 -- count for UNduplicated records now.
1050
1051 ELSIF
1052 l_rec_type ='NON_PROFESSIONAL' AND
1053 l_fund_type = 'UNDUPL' THEN
1054 OPEN c_non_proff_und_std_count(
1055 p_rec_type,
1056 p_fund_type,
1057 p_depend_stat,
1058 p_class_standing ,
1059 p_ci_cal_type ,
1060 p_ci_sequence_number ,
1061 p_minvalue ,
1062 p_maxvalue
1063 );
1064 FETCH c_non_proff_und_std_count INTO l_std_cnt;
1065 IF c_non_proff_und_std_count%NOTFOUND THEN
1066 l_std_cnt:=0;
1067 RETURN (l_std_cnt);
1068 ELSIF c_non_proff_und_std_count%FOUND THEN
1069 RETURN (l_std_cnt);
1070 END IF;
1071 CLOSE c_non_proff_und_std_count;
1072
1073 -- open second cursor, count of all students having any of the funds in
1074 -- FWS, PRK,FSEOG and un duplicate records, and professional
1075
1076 ELSIF
1077 l_rec_type ='PROFESSIONAL' AND
1078 l_fund_type <> 'UNDUPL' THEN
1079 OPEN c_proff_std_count(
1080 p_rec_type,
1081 p_fund_type,
1082 p_depend_stat,
1083 p_class_standing ,
1084 p_ci_cal_type ,
1085 p_ci_sequence_number ,
1086 p_minvalue ,
1087 p_maxvalue
1088 );
1089 FETCH c_proff_std_count INTO l_std_cnt;
1090 IF c_proff_std_count%NOTFOUND THEN
1091 l_std_cnt:=0;
1092 RETURN (l_std_cnt);
1093 ELSIF c_proff_std_count%FOUND THEN
1094 RETURN (l_std_cnt);
1095 END IF;
1096 CLOSE c_proff_std_count;
1097
1098 -- now count for Undulicated records
1099 ELSIF
1100 l_rec_type ='PROFESSIONAL' AND
1101 l_fund_type = 'UNDUPL' THEN
1102 OPEN c_proff_und_std_count(
1103 p_rec_type,
1104 p_fund_type,
1105 p_depend_stat,
1106 p_class_standing ,
1107 p_ci_cal_type ,
1108 p_ci_sequence_number ,
1109 p_minvalue ,
1110 p_maxvalue
1111 );
1112 FETCH c_proff_und_std_count INTO l_std_cnt;
1113 IF c_proff_und_std_count%NOTFOUND THEN
1114 l_std_cnt:=0;
1115 RETURN (l_std_cnt);
1116 ELSIF c_proff_und_std_count%FOUND THEN
1117 RETURN (l_std_cnt);
1118 END IF;
1119 CLOSE c_proff_und_std_count;
1120
1121 -- open third cursor, count of all students having any of the funds in
1122 -- FWS, PRK,FSEOG and un duplicate records, and Not FULL TIME
1123
1124 ELSIF
1125 l_rec_type ='LESS_THAN_FULL_TIME' AND
1126 l_fund_type <> 'UNDUPL' THEN
1127 OPEN c_less_ft_std_count(
1128 p_rec_type,
1129 p_fund_type,
1130 p_depend_stat,
1131 p_class_standing ,
1132 p_ci_cal_type ,
1133 p_ci_sequence_number ,
1134 p_minvalue ,
1135 p_maxvalue
1136 );
1137 FETCH c_less_ft_std_count INTO l_std_cnt;
1138 IF c_less_ft_std_count%NOTFOUND THEN
1139 l_std_cnt:=0;
1140 RETURN (l_std_cnt);
1141 ELSIF c_less_ft_std_count%FOUND THEN
1142 RETURN (l_std_cnt);
1143 END IF;
1144 CLOSE c_less_ft_std_count;
1145
1146 -- now open for Unduplicated record.
1147
1148 ELSIF
1149 l_rec_type ='LESS_THAN_FULL_TIME' AND
1150 l_fund_type = 'UNDUPL' THEN
1151 OPEN c_less_ft_und_std_count(
1152 p_rec_type,
1153 p_fund_type,
1154 p_depend_stat,
1155 p_class_standing ,
1156 p_ci_cal_type ,
1157 p_ci_sequence_number ,
1158 p_minvalue ,
1159 p_maxvalue
1160 );
1161 FETCH c_less_ft_und_std_count INTO l_std_cnt;
1162 IF c_less_ft_und_std_count%NOTFOUND THEN
1163 l_std_cnt:=0;
1164 RETURN (l_std_cnt);
1165 ELSIF c_less_ft_und_std_count%FOUND THEN
1166 RETURN (l_std_cnt);
1167 END IF;
1168 CLOSE c_less_ft_und_std_count;
1169
1170 -- open fourth cursor, count of all students having any of the funds in
1171 -- FWS, PRK,FSEOG and un duplicate records, and AUTO ZERO EFC
1172
1173 ELSIF
1174 l_rec_type ='AUTO_ZERO' AND
1175 l_fund_type <> 'UNDUPL' THEN
1176 OPEN c_auto_efc_std_count(
1177 p_rec_type,
1178 p_fund_type,
1179 p_depend_stat,
1180 p_class_standing ,
1181 p_ci_cal_type ,
1182 p_ci_sequence_number ,
1183 p_minvalue ,
1184 p_maxvalue
1185 );
1186 FETCH c_auto_efc_std_count INTO l_std_cnt;
1187 IF c_auto_efc_std_count%NOTFOUND THEN
1188 l_std_cnt:=0;
1189 RETURN (l_std_cnt);
1190 ELSIF c_auto_efc_std_count%FOUND THEN
1191 RETURN (l_std_cnt);
1192 END IF;
1193 CLOSE c_auto_efc_std_count;
1194
1195 -- now count for unduplicate recs.
1196
1197 ELSIF
1198 l_rec_type ='AUTO_ZERO' AND
1199 l_fund_type = 'UNDUPL' THEN
1200 OPEN c_auto_efc_und_std_count(
1201 p_rec_type,
1202 p_fund_type,
1203 p_depend_stat,
1204 p_class_standing ,
1205 p_ci_cal_type ,
1206 p_ci_sequence_number ,
1207 p_minvalue ,
1208 p_maxvalue
1209 );
1210 FETCH c_auto_efc_und_std_count INTO l_std_cnt;
1211 IF c_auto_efc_und_std_count%NOTFOUND THEN
1212 l_std_cnt:=0;
1213 RETURN (l_std_cnt);
1214 ELSIF c_auto_efc_und_std_count%FOUND THEN
1215 RETURN (l_std_cnt);
1216 END IF;
1217 CLOSE c_auto_efc_und_std_count;
1218 END IF;
1219 */
1220 RETURN 0;
1221 EXCEPTION
1222 WHEN OTHERS THEN
1223 /*IF c_auto_efc_und_std_count%ISOPEN THEN
1224 CLOSE c_auto_efc_und_std_count;
1225 END IF;
1226 IF c_auto_efc_std_count%ISOPEN THEN
1227 CLOSE c_auto_efc_std_count;
1228 END IF;
1229 IF c_less_ft_und_std_count%ISOPEN THEN
1230 CLOSE c_less_ft_und_std_count;
1231 END IF;
1232 IF c_less_ft_std_count%ISOPEN THEN
1233 CLOSE c_less_ft_std_count;
1234 END IF;
1235 IF c_proff_und_std_count%ISOPEN THEN
1236 CLOSE c_proff_und_std_count;
1237 END IF;
1238 IF c_proff_std_count%ISOPEN THEN
1239 CLOSE c_proff_std_count;
1240 END IF;
1241 IF c_less_ft_std_count%ISOPEN THEN
1242 CLOSE c_less_ft_std_count;
1243 END IF;
1244 IF c_non_proff_std_count %ISOPEN THEN
1245 CLOSE c_non_proff_std_count ;
1246 END IF;*/
1247
1248 RETURN (-3); -- returning -3 means Unhandled exception raised
1249
1250 END get_sectionvi_stdnt;
1251
1252
1253 --Procedure for Comparing ISIR Applications
1254 PROCEDURE compare_isirs(
1255 p_isir_id igf_ap_isir_matched_all.isir_id%TYPE,
1256 p_corr_isir_id igf_ap_isir_matched_all.isir_id%TYPE,
1257 p_cal_type igf_ap_fa_base_rec_all.ci_cal_type%TYPE,
1258 p_seq_num igf_ap_fa_base_rec_all.ci_sequence_number%TYPE,
1259 p_corr_status igf_ap_isir_corr.correction_status%TYPE
1260 ) AS
1261
1262 /***************************************************************
1263 Created By : mesriniv
1264 Date Created By : 2001/20/12
1265 Purpose : This Procedure is added for Comparison of ISIR Fields.
1266 This procedure is being called in the IGFAP003.pld for
1267 Save as Correction ISIR
1268 Known Limitations,Enhancements or Remarks
1269
1270 Change History :
1271 Enh Bug 2142666 EFC Build 2002 Jul
1272 Who When What
1273 brajendr 28-Nov-2002 Modified the parameters. Removed the Row Type and changed to ISIR ID
1274 ***************************************************************/
1275
1276 -- Get ISIR details
1277 CURSOR c_get_org_isir( cp_isir_id igf_ap_isir_matched_all.isir_id%TYPE ) IS
1278 SELECT *
1279 FROM igf_ap_isir_matched
1280 WHERE isir_id = cp_isir_id;
1281
1282 p_isir_corr c_get_org_isir%ROWTYPE;
1283 p_isir_pay c_get_org_isir%ROWTYPE;
1284 -- Get system award details
1285 CURSOR c_sys_awd_yr(
1286 cp_cal_type igf_ap_fa_base_rec_all.ci_cal_type%TYPE,
1287 cp_seq_num igf_ap_fa_base_rec_all.ci_sequence_number%TYPE
1288 ) IS
1289 SELECT sys_award_year
1290 FROM igf_ap_batch_aw_map
1291 WHERE ci_cal_type = cp_cal_type
1292 AND ci_sequence_number = cp_seq_num;
1293
1294 lc_sys_awd_yr c_sys_awd_yr%ROWTYPE;
1295
1296
1297 PROCEDURE comp_fields(
1298 p_field_corr IN VARCHAR2,
1299 p_field_pay IN VARCHAR2,
1300 p_field_name IN VARCHAR2
1301 ) AS
1302
1303 -- ## Cursor to get the changed code values
1304 -- cursor modifed as part of FACR 113 SAR Updates
1305 CURSOR c_chg_code ( cp_cal_type igf_ap_batch_aw_map.ci_cal_type%TYPE,
1306 cp_seq_num igf_ap_batch_aw_map.ci_sequence_number%TYPE ) IS
1307 SELECT sar.sar_field_number
1308 FROM igf_ap_batch_aw_map map,
1309 Igf_fc_sar_cd_mst sar
1310 WHERE map.ci_cal_type = cp_cal_type
1311 AND map.ci_sequence_number = cp_seq_num
1312 AND sar.sys_award_year = map.sys_award_year
1313 AND sar.sar_field_name = p_field_name ;
1314
1315
1316 CURSOR chk_corr_exists(v_isir_id NUMBER,
1317 v_cal_type VARCHAR2,
1318 v_seq_num NUMBER,
1319 v_sar_fld VARCHAR2
1320 ) IS
1321 SELECT row_id , isirc_id,correction_status
1322 FROM igf_ap_isir_corr
1323 WHERE isir_id = v_isir_id
1324 AND ci_cal_type = v_cal_type
1325 AND ci_sequence_number = v_seq_num
1326 AND sar_field_number = v_sar_fld ;
1327
1328
1329 lc_chg_code igf_lookups_view.lookup_code%TYPE;
1330 l_rowid VARCHAR2(25);
1331 l_isirc_id igf_ap_isir_corr.isirc_id%TYPE;
1332 p_batch_id igf_ap_isir_corr.batch_id%TYPE;
1333 lv_corr_status VARCHAR2(30);
1334
1335 l_chk_corr_exists chk_corr_exists%rowtype;
1336
1337 BEGIN
1338
1339 --Compare the Fields and insert if they have diff values
1340 IF (p_field_corr IS NULL AND p_field_pay IS NULL) OR
1341 (p_field_corr = p_field_pay) THEN
1342 NULL;
1343 ELSE
1344
1345 OPEN c_chg_code (p_cal_type, p_seq_num);
1346 FETCH c_chg_code INTO lc_chg_code;
1347 IF c_chg_code%NOTFOUND THEN
1348 CLOSE c_chg_code;
1349 --Raise an error as No such Field Defined
1350 fnd_message.set_name('IGF','IGF_AP_NO_SUCH_CHNG_CODE');
1351 fnd_message.set_token('CHANGE_CODE',p_field_name);
1352 igs_ge_msg_stack.add;
1353 app_exception.raise_exception;
1354 END IF;
1355
1356 BEGIN
1357 --Insert this Change Code and Its changed value for tracking
1358
1359 OPEN chk_corr_exists(p_isir_pay.isir_id,p_cal_type,p_seq_num,lc_chg_code);
1360 FETCH chk_corr_exists INTO l_chk_corr_exists;
1361 CLOSE chk_corr_exists;
1362
1363 IF l_chk_corr_exists.isirc_id IS NULL THEN
1364
1365 igf_ap_isir_corr_pkg.insert_row(
1366 X_ROWID => l_rowid,
1367 X_ISIRC_ID => l_isirc_id,
1368 X_ISIR_ID => p_isir_pay.isir_id,
1369 X_CI_SEQUENCE_NUMBER => p_seq_num,
1370 X_CI_CAL_TYPE => p_cal_type,
1371 X_SAR_FIELD_NUMBER => lc_chg_code,
1372 X_ORIGINAL_VALUE => p_field_pay,
1373 X_BATCH_ID => NULL,
1374 X_CORRECTED_VALUE => p_field_corr,
1375 X_CORRECTION_STATUS => p_corr_status,
1376 X_MODE => 'R'
1377 );
1378 ELSE
1379 l_rowid := l_chk_corr_exists.row_id ;
1380
1381 IF l_chk_corr_exists.correction_status = 'HOLD' THEN
1382 lv_corr_status := 'HOLD' ;
1383 ELSE
1384 lv_corr_status := p_corr_status ;
1385 END IF;
1386 igf_ap_isir_corr_pkg.update_row(
1387 X_ROWID => l_rowid,
1388 X_ISIRC_ID => l_chk_corr_exists.isirc_id,
1389 X_ISIR_ID => p_isir_pay.isir_id,
1390 X_CI_SEQUENCE_NUMBER => p_seq_num,
1391 X_CI_CAL_TYPE => p_cal_type,
1392 X_SAR_FIELD_NUMBER => lc_chg_code,
1393 X_ORIGINAL_VALUE => p_field_pay,
1394 X_BATCH_ID => NULL,
1395 X_CORRECTED_VALUE => p_field_corr,
1396 X_CORRECTION_STATUS => lv_corr_status,
1397 X_MODE => 'R'
1398 );
1399
1400 END IF;
1401 EXCEPTION
1402 WHEN OTHERS THEN
1403 NULL;
1404 END;
1405 CLOSE c_chg_code; -- ## Close the Cursor
1406
1407 END IF;
1408
1409 EXCEPTION
1410 WHEN OTHERS THEN
1411 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1412 FND_MESSAGE.SET_TOKEN('NAME','igf_ap_compare_fields');
1413 igs_ge_msg_stack.add;
1414 APP_EXCEPTION.RAISE_EXCEPTION;
1415 END comp_fields;
1416
1417 BEGIN
1418 -- Get record record of Original ISIR
1419 OPEN c_get_org_isir(p_isir_id);
1420 FETCH c_get_org_isir INTO p_isir_pay;
1421 CLOSE c_get_org_isir;
1422
1423 -- Get record record of Correction ISIR
1424 OPEN c_get_org_isir(p_corr_isir_id);
1425 FETCH c_get_org_isir INTO p_isir_corr;
1426 CLOSE c_get_org_isir;
1427
1428 -- Compare all the fields which are registered as Lookup Codes and which can change
1429 comp_fields( p_isir_corr.LAST_NAME,p_isir_pay.LAST_NAME,'LAST_NAME');
1430 comp_fields( p_isir_corr.PHONE_NUMBER,p_isir_pay.PHONE_NUMBER,'PHONE_NUMBER') ;
1431 comp_fields( p_isir_corr.PREPARER_SSN,p_isir_pay.PREPARER_SSN,'PREPARER_SSN');
1432 comp_fields( p_isir_corr.PREPARER_EMP_ID_NUMBER,p_isir_pay.PREPARER_EMP_ID_NUMBER,'PREPARER_EMP_ID_NUMBER');
1433 comp_fields( p_isir_corr.PREPARER_SIGN,p_isir_pay.PREPARER_SIGN,'PREPARER_SIGN');
1434
1435 -- bug 3277173
1436 comp_fields( p_isir_corr.DATE_OF_BIRTH,p_isir_pay.DATE_OF_BIRTH,'DATE_OF_BIRTH');
1437
1438 comp_fields( p_isir_corr.DEPENDENCY_OVERRIDE_IND,p_isir_pay.DEPENDENCY_OVERRIDE_IND,'DEPENDENCY_OVERRIDE_IND');
1439 comp_fields( p_isir_corr.FAA_ADJUSTMENT,p_isir_pay.FAA_ADJUSTMENT,'FAA_ADJUSTMENT');
1440 comp_fields( p_isir_corr.DRIVER_LICENSE_NUMBER,p_isir_pay.DRIVER_LICENSE_NUMBER,'DRIVER_LICENSE_NUMBER');
1441
1442 comp_fields( p_isir_corr.DRN,p_isir_pay.DRN,'DRN');
1443 comp_fields( p_isir_corr.DRIVER_LICENSE_STATE,p_isir_pay.DRIVER_LICENSE_STATE,'DRIVER_LICENSE_STATE');
1444 comp_fields( p_isir_corr.CITIZENSHIP_STATUS,p_isir_pay.CITIZENSHIP_STATUS,'CITIZENSHIP_STATUS');
1445
1446 comp_fields( p_isir_corr.ALIEN_REG_NUMBER,p_isir_pay.ALIEN_REG_NUMBER,'ALIEN_REG_NUMBER');
1447 comp_fields( p_isir_corr.S_MARITAL_STATUS,p_isir_pay.S_MARITAL_STATUS,'S_MARITAL_STATUS');
1448 comp_fields( p_isir_corr.S_MARITAL_STATUS_DATE,p_isir_pay.S_MARITAL_STATUS_DATE,'S_MARITAL_STATUS_DATE');
1449 comp_fields( p_isir_corr.SUMM_ENRL_STATUS,p_isir_pay.SUMM_ENRL_STATUS,'SUMM_ENRL_STATUS');
1450 comp_fields( p_isir_corr.FALL_ENRL_STATUS,p_isir_pay.FALL_ENRL_STATUS,'FALL_ENRL_STATUS');
1451
1452 comp_fields( p_isir_corr.WINTER_ENRL_STATUS,p_isir_pay.WINTER_ENRL_STATUS,'WINTER_ENRL_STATUS');
1453 comp_fields( p_isir_corr.FIRST_NAME,p_isir_pay.FIRST_NAME,'FIRST_NAME');
1454 comp_fields( p_isir_corr.SPRING_ENRL_STATUS,p_isir_pay.SPRING_ENRL_STATUS,'SPRING_ENRL_STATUS');
1455 comp_fields( p_isir_corr.SUMM2_ENRL_STATUS,p_isir_pay.SUMM2_ENRL_STATUS,'SUMM2_ENRL_STATUS');
1456 comp_fields( p_isir_corr.FATHERS_HIGHEST_EDU_LEVEL,p_isir_pay.FATHERS_HIGHEST_EDU_LEVEL,'FATHERS_HIGHEST_EDU_LEVEL');
1457
1458 comp_fields( p_isir_corr.MOTHERS_HIGHEST_EDU_LEVEL,p_isir_pay.MOTHERS_HIGHEST_EDU_LEVEL,'MOTHERS_HIGHEST_EDU_LEVEL');
1459 comp_fields( p_isir_corr.S_STATE_LEGAL_RESIDENCE,p_isir_pay.S_STATE_LEGAL_RESIDENCE,'S_STATE_LEGAL_RESIDENCE');
1460 comp_fields( p_isir_corr.LEGAL_RESIDENCE_BEFORE_DATE,p_isir_pay.LEGAL_RESIDENCE_BEFORE_DATE,'LEGAL_RESIDENCE_BEFORE_DATE') ;
1461 comp_fields( p_isir_corr.S_LEGAL_RESD_DATE,p_isir_pay.S_LEGAL_RESD_DATE,'S_LEGAL_RESD_DATE');
1462 comp_fields( p_isir_corr.SS_R_U_MALE,p_isir_pay.SS_R_U_MALE,'SS_R_U_MALE') ;
1463
1464 comp_fields( p_isir_corr.SELECTIVE_SERVICE_REG,p_isir_pay.SELECTIVE_SERVICE_REG,'SELECTIVE_SERVICE_REG');
1465 comp_fields( p_isir_corr.DEGREE_CERTIFICATION,p_isir_pay.DEGREE_CERTIFICATION,'DEGREE_CERTIFICATION') ;
1466 comp_fields( p_isir_corr.MIDDLE_INITIAL,p_isir_pay.MIDDLE_INITIAL,'MIDDLE_INITIAL');
1467 comp_fields( p_isir_corr.GRADE_LEVEL_IN_COLLEGE,p_isir_pay.GRADE_LEVEL_IN_COLLEGE,'GRADE_LEVEL_IN_COLLEGE') ;
1468 comp_fields( p_isir_corr.HIGH_SCHOOL_DIPLOMA_GED,p_isir_pay.HIGH_SCHOOL_DIPLOMA_GED,'HIGH_SCHOOL_DIPLOMA_GED');
1469
1470 comp_fields( p_isir_corr.FIRST_BACHELOR_DEG_BY_DATE,p_isir_pay.FIRST_BACHELOR_DEG_BY_DATE,'FIRST_BACHELOR_DEG_BY_DATE');
1471 comp_fields( p_isir_corr.INTEREST_IN_LOAN,p_isir_pay.INTEREST_IN_LOAN,'INTEREST_IN_LOAN');
1472 comp_fields( p_isir_corr.INTEREST_IN_STUD_EMPLOYMENT,p_isir_pay.INTEREST_IN_STUD_EMPLOYMENT,'INTEREST_IN_STUD_EMPLOYMENT');
1473 comp_fields( p_isir_corr.DRUG_OFFENCE_CONVICTION,p_isir_pay.DRUG_OFFENCE_CONVICTION,'DRUG_OFFENCE_CONVICTION');
1474 comp_fields( p_isir_corr.S_TAX_RETURN_STATUS,p_isir_pay.S_TAX_RETURN_STATUS,'S_TAX_RETURN_STATUS');
1475
1476 comp_fields( p_isir_corr.S_TYPE_TAX_RETURN,p_isir_pay.S_TYPE_TAX_RETURN,'S_TYPE_TAX_RETURN') ;
1477 comp_fields( p_isir_corr.S_ELIG_1040EZ,p_isir_pay.S_ELIG_1040EZ,'S_ELIG_1040EZ');
1478 comp_fields( p_isir_corr.S_ADJUSTED_GROSS_INCOME,p_isir_pay.S_ADJUSTED_GROSS_INCOME,'S_ADJUSTED_GROSS_INCOME');
1479 comp_fields( p_isir_corr.PERM_MAIL_ADD,p_isir_pay.PERM_MAIL_ADD,'PERM_MAIL_ADD');
1480 comp_fields( p_isir_corr.S_FED_TAXES_PAID,p_isir_pay.S_FED_TAXES_PAID,'S_FED_TAXES_PAID');
1481
1482 comp_fields( p_isir_corr.S_EXEMPTIONS,p_isir_pay.S_EXEMPTIONS,'S_EXEMPTIONS');
1483 comp_fields( p_isir_corr.S_INCOME_FROM_WORK,p_isir_pay.S_INCOME_FROM_WORK,'S_INCOME_FROM_WORK') ;
1484 comp_fields( p_isir_corr.SPOUSE_INCOME_FROM_WORK,p_isir_pay.SPOUSE_INCOME_FROM_WORK,'SPOUSE_INCOME_FROM_WORK');
1485 comp_fields( p_isir_corr.S_TOA_AMT_FROM_WSA,p_isir_pay.S_TOA_AMT_FROM_WSA,'S_TOA_AMT_FROM_WSA') ;
1486 comp_fields( p_isir_corr.S_TOA_AMT_FROM_WSB,p_isir_pay.S_TOA_AMT_FROM_WSB,'S_TOA_AMT_FROM_WSB');
1487
1488 comp_fields( p_isir_corr.S_TOA_AMT_FROM_WSC,p_isir_pay.S_TOA_AMT_FROM_WSC,'S_TOA_AMT_FROM_WSC');
1489 comp_fields( p_isir_corr.S_INVESTMENT_NETWORTH,p_isir_pay.S_INVESTMENT_NETWORTH,'S_INVESTMENT_NETWORTH');
1490 comp_fields( p_isir_corr.S_BUSI_FARM_NETWORTH,p_isir_pay.S_BUSI_FARM_NETWORTH,'S_BUSI_FARM_NETWORTH');
1491 comp_fields( p_isir_corr.S_CASH_SAVINGS,p_isir_pay.S_CASH_SAVINGS,'S_CASH_SAVINGS');
1492 comp_fields( p_isir_corr.PERM_CITY,p_isir_pay.PERM_CITY,'PERM_CITY');
1493
1494 comp_fields( p_isir_corr.VA_MONTHS,p_isir_pay.VA_MONTHS,'VA_MONTHS');
1495 comp_fields( p_isir_corr.VA_AMOUNT,p_isir_pay.VA_AMOUNT,'VA_AMOUNT');
1496 comp_fields( p_isir_corr.STUD_DOB_BEFORE_DATE,p_isir_pay.STUD_DOB_BEFORE_DATE,'STUD_DOB_BEFORE_DATE');
1497 comp_fields( p_isir_corr.DEG_BEYOND_BACHELOR,p_isir_pay.DEG_BEYOND_BACHELOR,'DEG_BEYOND_BACHELOR');
1498 comp_fields( p_isir_corr.S_MARRIED,p_isir_pay.S_MARRIED,'S_MARRIED');
1499
1500 comp_fields( p_isir_corr.S_HAVE_CHILDREN,p_isir_pay.S_HAVE_CHILDREN,'S_HAVE_CHILDREN');
1501 comp_fields( p_isir_corr.LEGAL_DEPENDENTS,p_isir_pay.LEGAL_DEPENDENTS,'LEGAL_DEPENDENTS');
1502 comp_fields( p_isir_corr.ORPHAN_WARD_OF_COURT,p_isir_pay.ORPHAN_WARD_OF_COURT,'ORPHAN_WARD_OF_COURT');
1503 comp_fields( p_isir_corr.S_VETERAN,p_isir_pay.S_VETERAN,'S_VETERAN');
1504 comp_fields( p_isir_corr.P_MARITAL_STATUS,p_isir_pay.P_MARITAL_STATUS,'P_MARITAL_STATUS');
1505 -- added parent_marital_status_date as part of nov 03 bug 3273581
1506 comp_fields( p_isir_corr.PARENT_MARITAL_STATUS_DATE,p_isir_pay.PARENT_MARITAL_STATUS_DATE,'PARENT_MARITAL_STATUS_DATE');
1507
1508 comp_fields( p_isir_corr.PERM_STATE,p_isir_pay.PERM_STATE,'PERM_STATE');
1509 comp_fields( p_isir_corr.FATHER_SSN,p_isir_pay.FATHER_SSN,'FATHER_SSN');
1510 comp_fields( p_isir_corr.F_LAST_NAME,p_isir_pay.F_LAST_NAME,'F_LAST_NAME');
1511 comp_fields( p_isir_corr.MOTHER_SSN,p_isir_pay.MOTHER_SSN,'MOTHER_SSN');
1512 comp_fields( p_isir_corr.M_LAST_NAME,p_isir_pay.M_LAST_NAME,'M_LAST_NAME');
1513
1514 comp_fields( p_isir_corr.P_NUM_FAMILY_MEMBER,p_isir_pay.P_NUM_FAMILY_MEMBER,'P_NUM_FAMILY_MEMBER');
1515 comp_fields( p_isir_corr.P_NUM_IN_COLLEGE,p_isir_pay.P_NUM_IN_COLLEGE,'P_NUM_IN_COLLEGE');
1516 comp_fields( p_isir_corr.P_STATE_LEGAL_RESIDENCE,p_isir_pay.P_STATE_LEGAL_RESIDENCE,'P_STATE_LEGAL_RESIDENCE');
1517 comp_fields( p_isir_corr.P_STATE_LEGAL_RES_BEFORE_DT,p_isir_pay.P_STATE_LEGAL_RES_BEFORE_DT,'P_STATE_LEGAL_RES_BEFORE_DT');
1518 comp_fields( p_isir_corr.P_LEGAL_RES_DATE,p_isir_pay.P_LEGAL_RES_DATE,'P_LEGAL_RES_DATE');
1519
1520 comp_fields( p_isir_corr.PERM_ZIP_CODE,p_isir_pay.PERM_ZIP_CODE,'PERM_ZIP_CODE');
1521 comp_fields( p_isir_corr.P_TAX_RETURN_STATUS,p_isir_pay.P_TAX_RETURN_STATUS,'P_TAX_RETURN_STATUS');
1522 comp_fields( p_isir_corr.P_TYPE_TAX_RETURN,p_isir_pay.P_TYPE_TAX_RETURN,'P_TYPE_TAX_RETURN');
1523 comp_fields( p_isir_corr.P_ELIG_1040AEZ,p_isir_pay.P_ELIG_1040AEZ,'P_ELIG_1040AEZ');
1524
1525 comp_fields( p_isir_corr.P_ADJUSTED_GROSS_INCOME,p_isir_pay.P_ADJUSTED_GROSS_INCOME,'P_ADJUSTED_GROSS_INCOME');
1526 comp_fields( p_isir_corr.P_TAXES_PAID,p_isir_pay.P_TAXES_PAID,'P_TAXES_PAID');
1527 comp_fields( p_isir_corr.P_EXEMPTIONS,p_isir_pay.P_EXEMPTIONS,'P_EXEMPTIONS');
1528 comp_fields( p_isir_corr.F_INCOME_WORK,p_isir_pay.F_INCOME_WORK,'F_INCOME_WORK');
1529 comp_fields( p_isir_corr.M_INCOME_WORK,p_isir_pay.M_INCOME_WORK,'M_INCOME_WORK');
1530
1531 comp_fields( p_isir_corr.P_INCOME_WSA,p_isir_pay.P_INCOME_WSA,'P_INCOME_WSA');
1532 comp_fields( p_isir_corr.P_INCOME_WSB,p_isir_pay.P_INCOME_WSB,'P_INCOME_WSB');
1533 comp_fields( p_isir_corr.CURRENT_SSN,p_isir_pay.CURRENT_SSN,'CURRENT_SSN');
1534 comp_fields( p_isir_corr.P_INCOME_WSC,p_isir_pay.P_INCOME_WSC,'P_INCOME_WSC');
1535 comp_fields( p_isir_corr.P_INVESTMENT_NETWORTH,p_isir_pay.P_INVESTMENT_NETWORTH,'P_INVESTMENT_NETWORTH');
1536
1537 comp_fields( p_isir_corr.P_BUSINESS_NETWORTH,p_isir_pay.P_BUSINESS_NETWORTH,'P_BUSINESS_NETWORTH');
1538 comp_fields( p_isir_corr.P_CASH_SAVING,p_isir_pay.P_CASH_SAVING,'P_CASH_SAVING') ;
1539 comp_fields( p_isir_corr.S_NUM_FAMILY_MEMBERS,p_isir_pay.S_NUM_FAMILY_MEMBERS,'S_NUM_FAMILY_MEMBERS') ;
1540 -- bug 3277173
1541 comp_fields( p_isir_corr.S_NUM_IN_COLLEGE,p_isir_pay.S_NUM_IN_COLLEGE,'S_NUM_IN_COLLEGE');
1542 -- bug 3277173
1543 comp_fields( p_isir_corr.FIRST_COLLEGE,p_isir_pay.FIRST_COLLEGE,'FIRST_COLLEGE');
1544 comp_fields( p_isir_corr.FIRST_HOUSE_PLAN,p_isir_pay.FIRST_HOUSE_PLAN,'FIRST_HOUSE_PLAN');
1545 comp_fields( p_isir_corr.SECOND_COLLEGE,p_isir_pay.SECOND_COLLEGE,'SECOND_COLLEGE');
1546 comp_fields( p_isir_corr.SECOND_HOUSE_PLAN,p_isir_pay.SECOND_HOUSE_PLAN,'SECOND_HOUSE_PLAN');
1547 comp_fields( p_isir_corr.THIRD_COLLEGE ,p_isir_pay.THIRD_COLLEGE ,'THIRD_COLLEGE');
1548 comp_fields( p_isir_corr.THIRD_HOUSE_PLAN,p_isir_pay.THIRD_HOUSE_PLAN,'THIRD_HOUSE_PLAN');
1549 comp_fields( p_isir_corr.FOURTH_COLLEGE ,p_isir_pay.FOURTH_COLLEGE ,'FOURTH_COLLEGE');
1550 comp_fields( p_isir_corr.FOURTH_HOUSE_PLAN,p_isir_pay.FOURTH_HOUSE_PLAN,'FOURTH_HOUSE_PLAN');
1551 comp_fields( p_isir_corr.FIFTH_COLLEGE ,p_isir_pay.FIFTH_COLLEGE ,'FIFTH_COLLEGE');
1552 comp_fields( p_isir_corr.FIFTH_HOUSE_PLAN,p_isir_pay.FIFTH_HOUSE_PLAN,'FIFTH_HOUSE_PLAN');
1553 comp_fields( p_isir_corr.SIXTH_COLLEGE ,p_isir_pay.SIXTH_COLLEGE ,'SIXTH_COLLEGE');
1554 comp_fields( p_isir_corr.SIXTH_HOUSE_PLAN,p_isir_pay.SIXTH_HOUSE_PLAN,'SIXTH_HOUSE_PLAN');
1555 -- bug 3277173
1556 comp_fields( p_isir_corr.SIGNED_BY,p_isir_pay.SIGNED_BY,'SIGNED_BY');
1557 comp_fields( p_isir_corr.PREPARER_SIGN ,p_isir_pay.PREPARER_SIGN ,'PREPARER_SIGN');
1558
1559 lc_sys_awd_yr := NULL;
1560 OPEN c_sys_awd_yr(p_cal_type, p_seq_num);
1561 FETCH c_sys_awd_yr INTO lc_sys_awd_yr;
1562 CLOSE c_sys_awd_yr;
1563 IF lc_sys_awd_yr.sys_award_year IN ('0203','0304') THEN
1564 comp_fields( p_isir_corr.FAA_FEDRAL_SCHL_CODE,p_isir_pay.FAA_FEDRAL_SCHL_CODE,'FAA_FEDRAL_SCHL_CODE');
1565 comp_fields( p_isir_corr.TRANSACTION_RECEIPT_DATE,p_isir_pay.TRANSACTION_RECEIPT_DATE,'TRANSACTION_RECEIPT_DATE');
1566 comp_fields( p_isir_corr.AGE_OLDER_PARENT,p_isir_pay.AGE_OLDER_PARENT,'AGE_OLDER_PARENT');
1567 comp_fields( p_isir_corr.EARLY_ANALYSIS_FLAG,p_isir_pay.EARLY_ANALYSIS_FLAG,'EARLY_ANALYSIS_FLAG');
1568 comp_fields( p_isir_corr.DATE_APP_COMPLETED,p_isir_pay.DATE_APP_COMPLETED,'DATE_APP_COMPLETED');
1569 ELSIF lc_sys_awd_yr.sys_award_year IN ('0405', '0506', '0607') THEN
1570 comp_fields( p_isir_corr.father_first_name_initial_txt,p_isir_pay.father_first_name_initial_txt,'FATHER_FIRST_NAME_INITIAL_TXT');
1571 comp_fields( p_isir_corr.father_step_father_birth_date,p_isir_pay.father_step_father_birth_date,'FATHER_STEP_FATHER_BIRTH_DATE');
1572 comp_fields( p_isir_corr.mother_first_name_initial_txt,p_isir_pay.mother_first_name_initial_txt,'MOTHER_FIRST_NAME_INITIAL_TXT');
1573 comp_fields( p_isir_corr.mother_step_mother_birth_date,p_isir_pay.mother_step_mother_birth_date,'MOTHER_STEP_MOTHER_BIRTH_DATE');
1574 comp_fields( p_isir_corr.parents_email_address_txt,p_isir_pay.parents_email_address_txt,'PARENTS_EMAIL_ADDRESS_TXT');
1575 END IF;
1576
1577 EXCEPTION
1578 WHEN OTHERS THEN
1579 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1580 fnd_message.set_token('NAME','igf_ap_compare_isirs');
1581 igs_ge_msg_stack.add;
1582 app_exception.raise_exception;
1583
1584 END compare_isirs;
1585
1586
1587 PROCEDURE get_resource_need
1588 (
1589 p_base_id IN igf_ap_fa_base_rec.base_id%TYPE,
1590 p_resource_f OUT NOCOPY NUMBER,
1591 p_resource_i OUT NOCOPY NUMBER,
1592 p_unmet_need_f OUT NOCOPY NUMBER,
1593 p_unmet_need_i OUT NOCOPY NUMBER,
1594 p_resource_f_fc OUT NOCOPY NUMBER,
1595 p_resource_i_fc OUT NOCOPY NUMBER,
1596 p_awd_prd_code IN igf_aw_awd_prd_term.award_prd_cd%TYPE DEFAULT NULL,
1597 p_calc_for_subz_loan IN VARCHAR2 DEFAULT 'N'
1598 )
1599 AS
1600 /*
1601 || Created By : [email protected]
1602 || Created On : 08-JAN-2001
1603 || Purpose : Bug No: 2154941. This procedure takes the base_id (Student and Award Year) as in parameter. It passes out NOCOPY the
1604 || Federal and Institutional resources for that base id, and Federal and institutional unmet
1605 || need/Overaward for that base_id.
1606 || Known limitations, enhancements or remarks :
1607 || Change History :
1608 || Who When What
1609 || veramach 12-Oct-2004 FA 152 - Added p_awd_prd_code to the signature
1610 || bkkumar 12-May-2004 Bug 3620500 Added the code to correctly include the sponsorship awards while
1611 || calculating total awards.
1612 || veramach 07-OCT-2003 FA 124
1613 || cursor resource_cur modified -it does not select resource_fm_i
1614 || cdcruz 16-Dec-2002 Bug # 2691811
1615 || Modified the cursor resource_cur , Award Status filter added
1616 || cdcruz 26-oct-2002 Bug Id 2613546
1617 || Cursor resource_cur modified to include Familty Contribution calc as well
1618 || adhawan 25-oct-2002 Bug Id 2613546
1619 || brajendr 08-May-2002 Bug # 2348285
1620 || Modified the cursor resource_cur to have award_status condition
1621 || (reverse chronological order - newest change first)
1622 ||
1623 || Variables description -
1624 || p_resource_f - SUM of all awards the Student has received
1625 || p_resource_i - SUM of all Institutional awards the Student has received
1626 || p_resource_f_fc - SUM of all Replace_EFC awards the Student has received
1627 */
1628 -- Cursor to determine the Total Awarded Amount for Federal and Institutional Methodology.
1629 -- As part of the Bug 3620500 The resource_f will include all the awards irrespective of the methodology
1630 -- and the replace_fc flad is applicable only to the federal methodology funds.
1631 CURSOR resource_cur IS
1632 SELECT NVL(SUM(NVL(disb.disb_gross_amt,0)),0) resource_f,
1633 NVL(SUM(DECODE(fm.fm_fc_methd,'INSTITUTIONAL',NVL(disb.disb_gross_amt,0),0)),0) resource_i,
1634 NVL(SUM(DECODE(fm.replace_fc,'Y',NVL(disb.disb_gross_amt,0),0)),0) resource_fm_f
1635 FROM igf_aw_awd_disb_all disb,
1636 igf_aw_award_all awd,
1637 igf_aw_fund_mast_all fm,
1638 igf_aw_fund_cat_all fcat,
1639 ( SELECT base_id, ld_cal_type, ld_sequence_number
1640 FROM igf_aw_coa_itm_terms
1641 WHERE base_id = p_base_id
1642 GROUP BY base_id,ld_cal_type,ld_sequence_number
1643 ) coa
1644 WHERE awd.fund_id = fm.fund_id
1645 AND awd.award_id = disb.award_id
1646 AND fm.fund_code = fcat.fund_code
1647 AND awd.base_id = p_base_id
1648 AND disb.ld_cal_type = coa.ld_cal_type
1649 AND disb.ld_sequence_number = coa.ld_sequence_number
1650 AND awd.base_id = coa.base_id
1651 AND disb.trans_type <> 'C'
1652 AND awd.award_status IN ('OFFERED','ACCEPTED')
1653 AND (
1654 (p_calc_for_subz_loan = 'Y' AND fcat.fed_fund_code NOT IN ('VA30','AMERICORPS')) OR
1655 (p_calc_for_subz_loan = 'N')
1656 );
1657 resource_rec resource_cur%ROWTYPE;
1658
1659 CURSOR resource_cur_awd IS
1660 SELECT NVL(SUM(NVL(disb.disb_gross_amt, 0)), 0) resource_f,
1661 NVL(SUM(DECODE(fm.fm_fc_methd,'INSTITUTIONAL', NVL(disb.disb_gross_amt, 0),0)),0) resource_i,
1662 NVL(SUM(DECODE(fm.replace_fc,'Y', NVL(disb.disb_gross_amt, 0),0)), 0) resource_fm_f
1663 FROM igf_aw_awd_disb_all disb,
1664 igf_aw_award_all awd,
1665 igf_aw_fund_mast_all fm,
1666 igf_aw_fund_cat_all fcat,
1667 igf_ap_fa_base_rec_all fa,
1668 igf_aw_awd_prd_term aprd,
1669 (SELECT base_id,
1670 ld_cal_type,
1671 ld_sequence_number
1672 FROM igf_aw_coa_itm_terms
1673 WHERE base_id = p_base_id
1674 GROUP BY base_id, ld_cal_type, ld_sequence_number) coa
1675 WHERE awd.fund_id = fm.fund_id
1676 AND awd.award_id = disb.award_id
1677 AND fm.fund_code = fcat.fund_code
1678 AND awd.base_id = p_base_id
1679 AND disb.ld_cal_type = coa.ld_cal_type
1680 AND disb.ld_sequence_number = coa.ld_sequence_number
1681 AND awd.base_id = coa.base_id
1682 AND awd.base_id = fa.base_id
1683 AND fa.ci_cal_type = aprd.ci_cal_type
1684 AND fa.ci_sequence_number = aprd.ci_sequence_number
1685 AND disb.ld_cal_type = aprd.ld_cal_type
1686 AND disb.ld_sequence_number = aprd.ld_sequence_number
1687 AND aprd.award_prd_cd = p_awd_prd_code
1688 AND disb.trans_type <> 'C'
1689 AND awd.award_status IN('OFFERED','ACCEPTED')
1690 AND (
1691 (p_calc_for_subz_loan = 'Y' AND fcat.fed_fund_code NOT IN ('VA30','AMERICORPS')) OR
1692 (p_calc_for_subz_loan = 'N')
1693 );
1694
1695 l_coa igf_aw_coa_items.amount%TYPE;
1696
1697 ln_efc_f NUMBER;
1698 ln_efc_i NUMBER;
1699 ln_award_from_efc_meeting_need NUMBER;
1700
1701 BEGIN
1702
1703 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1704 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_002.get_resource_need.debug','Parameter List - START');
1705 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_002.get_resource_need.debug','p_base_id: ' ||p_base_id);
1706 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_002.get_resource_need.debug','p_awd_prd_code: ' ||p_awd_prd_code);
1707 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_002.get_resource_need.debug','p_calc_for_subz_loan: ' ||p_calc_for_subz_loan);
1708 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_002.get_resource_need.debug','Parameter List - END');
1709 END IF;
1710
1711 IF p_awd_prd_code IS NULL THEN
1712 -- AP not available
1713 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1714 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_002.get_resource_need.debug','Awarding Period NOT available. Considering all awards for the Student in the Awd Yr as resource');
1715 END IF;
1716
1717 OPEN resource_cur;
1718 FETCH resource_cur INTO resource_rec;
1719 CLOSE resource_cur;
1720 ELSE
1721 -- AP available
1722 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1723 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_002.get_resource_need.debug','Awarding Period is available. Considering awards ONLY in Awarding Period ' ||p_awd_prd_code|| ' for the Student as resource');
1724 END IF;
1725
1726 OPEN resource_cur_awd;
1727 FETCH resource_cur_awd INTO resource_rec;
1728 CLOSE resource_cur_awd;
1729 END IF;
1730
1731 l_coa := igf_aw_coa_gen.coa_amount(p_base_id,p_awd_prd_code);
1732
1733 ln_efc_f := NVL(igf_aw_gen_004.efc_f(p_base_id,p_awd_prd_code),0);
1734 ln_efc_i := NVL(igf_aw_gen_004.efc_i(p_base_id,p_awd_prd_code),0);
1735
1736 -- The p_resource_f will contain all the awards from FEDERAL, INSTITUTIONAL Methodology including the
1737 -- Sponsorships Funds.
1738 p_resource_f := resource_rec.resource_f;
1739
1740 p_resource_i := resource_rec.resource_i;
1741 p_unmet_need_i := NVL(l_coa,0) - ln_efc_i - NVL(p_resource_f,0);
1742 p_resource_i_fc := NULL;
1743
1744 -- If the replace efc awards are more than the Fed EFC then reduce the resource_fm_f to the FED EFC.
1745 IF NVL(resource_rec.resource_fm_f,0) > ln_efc_f THEN
1746 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1747 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_002.get_resource_need.debug','Reducing p_resource_f_fc to EFC, bcoz the Replace_FC awds got by the student is > EFC');
1748 END IF;
1749
1750 p_resource_f_fc := ln_efc_f;
1751 ELSE
1752 p_resource_f_fc := resource_rec.resource_fm_f;
1753 END IF;
1754 -- Here the unmet need is adjusted with the replace FC funds.
1755 p_unmet_need_f := NVL(l_coa,0) - ln_efc_f - NVL(p_resource_f,0) + NVL(p_resource_f_fc,0);
1756
1757 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1758 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_002.get_resource_need.debug','l_coa: ' ||l_coa);
1759 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_002.get_resource_need.debug','ln_efc_f: ' ||ln_efc_f|| ', ln_efc_i: ' ||ln_efc_i);
1760 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_002.get_resource_need.debug','p_resource_f: ' ||p_resource_f|| ', p_resource_i: ' ||p_resource_i);
1761 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_002.get_resource_need.debug','p_resource_f_fc: ' ||p_resource_f_fc|| ', p_resource_i_fc: ' ||p_resource_i_fc);
1762 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen_002.get_resource_need.debug','p_unmet_need_f: ' ||p_unmet_need_f|| ', p_unmet_need_i: ' ||p_unmet_need_i);
1763 END IF;
1764
1765 END get_resource_need;
1766
1767 END igf_aw_gen_002;
[Home] [Help]