1 PACKAGE BODY igf_aw_gen_005 AS
2 /* $Header: IGFAW14B.pls 120.2 2005/07/11 08:44:44 appldev ship $ */
3
4 /*======================================================================+
5 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA|
6 | All rights reserved. |
7 +=======================================================================+
8 | |
9 | DESCRIPTION |
10 | PL/SQL spec for package: IGF_AW_GEN_005 |
11 | |
12 | NOTES |
13 | Holds all the generic Routines |
14 | |
15 | HISTORY |
16 | Who When What |
17 | veramach Oct 2004 FA 152/FA 137 - Changes to wrappers to|
18 | bring in the awarding period setup |
19 | bkkumar 4-DEC-2003 FA 131 Bug# 3252832 |
20 | TBH impact of the igf_aw_award |
21 | Added columns LOCK_AWARD_FLAG, |
22 | APP_TRANS_NUM_TXT |
23 | veramach 1-NOV-2003 FA 125 Multipl Distribution Methods |
24 | Added procedures update_plan, |
25 | update_dist_plan,delete_plan, |
26 | check_plan_code |
27 | brajendr 08-Jan-2003 Bug # 2710314 |
28 | Added a Function validate_student_efc |
29 | for checking the validity of EFC |
30 | |
31 | brajendr 31-Dec-2002 Bug # 2721995 |
32 | Added an extra condition in first if |
33 | condition. ( fund_code IS NULL ) |
34 | |
35 *======================================================================*/
36
37 PROCEDURE update_plan(
38 p_adplans_id IN igf_aw_awd_dist_plans.adplans_id%TYPE,
39 p_method_code IN VARCHAR2,
40 p_result OUT NOCOPY VARCHAR2
41 ) AS
42 ------------------------------------------------------------------
43 --Created by : veramach, Oracle India
44 --Date created: 1-NOV-2003
45 --
46 --Purpose:Update a distribution plan's distribution percentages
47 --
48 --
49 --Known limitations/enhancements and/or remarks:
50 --
51 --Change History:
52 --Who When What
53 -------------------------------------------------------------------
54 -- Get all terms associated with the plan ID
55 CURSOR c_terms(
56 cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE
57 ) IS
58 SELECT rowid row_id,terms.*
59 FROM igf_aw_dp_terms terms
60 WHERE adplans_id = cp_adplans_id
61 AND ld_perct_num IS NOT NULL;
62
63 BEGIN
64
65 p_result := NULL;
66
67 IF p_method_code <> 'M' THEN
68
69 FOR terms_rec IN c_terms(p_adplans_id) LOOP
70 igf_aw_dp_terms_pkg.update_row(
71 x_rowid => terms_rec.row_id,
72 x_adterms_id => terms_rec.adterms_id,
73 x_adplans_id => terms_rec.adplans_id,
74 x_ld_cal_type => terms_rec.ld_cal_type,
75 x_ld_sequence_number => terms_rec.ld_sequence_number,
76 x_ld_perct_num => NULL,
77 x_mode => 'R'
78 );
79 p_result := 'REQUERY';
80 END LOOP;
81 END IF;
82 EXCEPTION
83 WHEN OTHERS THEN
84 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
85 fnd_message.set_token('NAME','IGF_AW_GEN_005.UPDATE_PLAN');
86 igs_ge_msg_stack.add;
87 app_exception.raise_exception;
88 END update_plan;
89
90 PROCEDURE update_dist_plan(
91 p_award_id igf_aw_award.award_id%TYPE
92 ) AS
93 ------------------------------------------------------------------
94 --Created by : veramach, Oracle India
95 --Date created: 1-NOV-2003
96 --
97 --Purpose:To update an award's distribution plan with NULL when disbursements are changed manually
98 --
99 --
100 --Known limitations/enhancements and/or remarks:
101 --
102 --Change History:
103 --Who When What
104 --bvisvana 11-Jul-2005 TBH impact for notification status code,notification status date and publish in ss flag
105 -------------------------------------------------------------------
106 -- Get award details
107 CURSOR c_award(
108 cp_award_id igf_aw_award.award_id%TYPE
109 ) IS
110 SELECT *
111 FROM igf_aw_award
112 WHERE award_id = cp_award_id;
113
114 l_award c_award%ROWTYPE;
115
116 BEGIN
117 IF p_award_id IS NOT NULL THEN
118 OPEN c_award(p_award_id);
119 FETCH c_award INTO l_award;
120 IF c_award%FOUND THEN
121 igf_aw_award_pkg.update_row(
122 x_rowid => l_award.row_id,
123 x_award_id => l_award.award_id,
124 x_fund_id => l_award.fund_id,
125 x_base_id => l_award.base_id,
126 x_offered_amt => l_award.offered_amt,
127 x_accepted_amt => l_award.accepted_amt,
128 x_paid_amt => l_award.paid_amt,
129 x_packaging_type => l_award.packaging_type,
130 x_batch_id => l_award.batch_id,
131 x_manual_update => l_award.manual_update,
132 x_rules_override => l_award.rules_override,
133 x_award_date => l_award.award_date,
134 x_award_status => l_award.award_status,
135 x_attribute_category => l_award.attribute_category,
136 x_attribute1 => l_award.attribute1,
137 x_attribute2 => l_award.attribute2,
138 x_attribute3 => l_award.attribute3,
139 x_attribute4 => l_award.attribute4,
140 x_attribute5 => l_award.attribute5,
141 x_attribute6 => l_award.attribute6,
142 x_attribute7 => l_award.attribute7,
143 x_attribute8 => l_award.attribute8,
144 x_attribute9 => l_award.attribute9,
145 x_attribute10 => l_award.attribute10,
146 x_attribute11 => l_award.attribute11,
147 x_attribute12 => l_award.attribute12,
148 x_attribute13 => l_award.attribute13,
149 x_attribute14 => l_award.attribute14,
150 x_attribute15 => l_award.attribute15,
151 x_attribute16 => l_award.attribute16,
152 x_attribute17 => l_award.attribute17,
153 x_attribute18 => l_award.attribute18,
154 x_attribute19 => l_award.attribute19,
155 x_attribute20 => l_award.attribute20,
156 x_rvsn_id => l_award.rvsn_id,
157 x_alt_pell_schedule => l_award.alt_pell_schedule,
158 x_mode => 'R',
159 x_award_number_txt => l_award.award_number_txt,
160 x_legacy_record_flag => l_award.legacy_record_flag,
161 x_adplans_id => NULL,
162 x_lock_award_flag => l_award.lock_award_flag,
163 x_app_trans_num_txt => l_award.app_trans_num_txt,
164 x_awd_proc_status_code => l_award.awd_proc_status_code,
165 x_notification_status_code => l_award.notification_status_code,
166 x_notification_status_date => l_award.notification_status_date,
167 x_publish_in_ss_flag => l_award.publish_in_ss_flag
168 );
169 END IF;
170 CLOSE c_award;
171 END IF;
172 EXCEPTION
173 WHEN OTHERS THEN
174 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
175 fnd_message.set_token('NAME','IGF_AW_GEN_005.UPDATE_DIST_PLAN');
176 igs_ge_msg_stack.add;
177 app_exception.raise_exception;
178 END update_dist_plan;
179
180 PROCEDURE check_plan_code(
181 p_adplans_id IN igf_aw_awd_dist_plans.adplans_id%TYPE,
182 p_result OUT NOCOPY VARCHAR2
183 ) AS
184 ------------------------------------------------------------------
185 --Created by : veramach, Oracle India
186 --Date created: 1-NOV-2003
187 --
188 --Purpose: Check if a distribution plan is associated with any award,award group or formula group
189 --
190 --
191 --Known limitations/enhancements and/or remarks:
192 --
193 --Change History:
194 --Who When What
195 -------------------------------------------------------------------
196
197 -- check if a distribution plan is associated with a award group
198 CURSOR c_check_agrp(
199 cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE
200 ) IS
201 SELECT adplans_id
202 FROM igf_aw_target_grp
203 WHERE adplans_id = cp_adplans_id;
204
205 -- check if a distribution plan is associated with a target group
206 CURSOR c_check_frml(
207 cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE
208 ) IS
209 SELECT adplans_id
210 FROM igf_aw_awd_frml_det
211 WHERE adplans_id = cp_adplans_id;
212
213 -- check if a distribution plan is associated with a award
214 CURSOR c_check_awd(
215 cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE
216 ) IS
217 SELECT adplans_id
218 FROM igf_aw_award
219 WHERE adplans_id = cp_adplans_id;
220
221 l_check_agrp c_check_agrp%ROWTYPE;
222 l_check_frml c_check_frml%ROWTYPE;
223 l_check_awd c_check_awd%ROWTYPE;
224
225 BEGIN
226
227 p_result := NULL;
228
229 OPEN c_check_agrp(p_adplans_id);
230 FETCH c_check_agrp INTO l_check_agrp;
231
232 IF c_check_agrp%FOUND THEN
233 p_result := 'AGRP';
234 ELSE
235 OPEN c_check_frml(p_adplans_id);
236 FETCH c_check_frml INTO l_check_frml;
237
238 IF c_check_frml%FOUND THEN
239 p_result := 'AGRP';
240 END IF;
241 END IF;
242
243 OPEN c_check_awd(p_adplans_id);
244 FETCH c_check_awd INTO l_check_awd;
245
246 IF c_check_awd%FOUND THEN
247 p_result := 'AWARD';
248 END IF;
249
250 IF c_check_agrp%ISOPEN THEN
251 CLOSE c_check_agrp;
252 END IF;
253
254 IF c_check_frml%ISOPEN THEN
255 CLOSE c_check_frml;
256 END IF;
257
258 IF c_check_awd%ISOPEN THEN
259 CLOSE c_check_awd;
260 END IF;
261
262 EXCEPTION
263 WHEN OTHERS THEN
264 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
265 fnd_message.set_token('NAME','IGF_AW_GEN_005.CHECK_PLAN_CODE');
266 igs_ge_msg_stack.add;
267 app_exception.raise_exception;
268 END check_plan_code;
269
270 PROCEDURE delete_plan(
271 p_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
272 p_adterms_id igf_aw_dp_terms.adterms_id%TYPE
273 ) AS
274 ------------------------------------------------------------------
275 --Created by : veramach, Oracle India
276 --Date created: 1-NOV-2003
277 --
278 --Purpose: To delete terms and teaching periods attahced to a distribution plan
279 --
280 --
281 --Known limitations/enhancements and/or remarks:
282 --
283 --Change History:
284 --Who When What
285 -------------------------------------------------------------------
286 -- Get all terms attached to a distribution plan
287 CURSOR c_terms(
288 cp_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
289 cp_adterms_id igf_aw_dp_terms.adterms_id%TYPE
290 ) IS
291 SELECT rowid row_id,terms.adterms_id adterms_id
292 FROM igf_aw_dp_terms terms
293 WHERE adplans_id = cp_adplans_id
294 AND adterms_id = NVL(cp_adterms_id,adterms_id);
295
296 -- Get all teaching periods attahced with a term
297 CURSOR c_teach_periods(
298 cp_adterms_id igf_aw_dp_terms.adterms_id%TYPE
299 ) IS
300 SELECT rowid row_id
301 FROM igf_aw_dp_teach_prds
302 WHERE adterms_id = cp_adterms_id;
303
304
305 BEGIN
306
307 FOR l_terms_rec IN c_terms(p_adplans_id,p_adterms_id) LOOP
308 FOR l_teaching_periods_rec IN c_teach_periods(l_terms_rec.adterms_id) LOOP
309 igf_aw_dp_teach_prds_pkg.delete_row(x_rowid => l_teaching_periods_rec.row_id);
310 END LOOP;
311 IF p_adterms_id IS NULL THEN
312 igf_aw_dp_terms_pkg.delete_row(x_rowid => l_terms_rec.row_id);
313 END IF;
314 END LOOP;
315
316 EXCEPTION
317 WHEN OTHERS THEN
318 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
319 fnd_message.set_token('NAME','IGF_AW_GEN_005.DELETE_PLAN');
320 igs_ge_msg_stack.add;
321 app_exception.raise_exception;
322 END delete_plan;
323
324 FUNCTION get_stud_hold_effect(
325 p_orig IN VARCHAR2,
326 p_person_id IN igf_ap_fa_base_rec_all.person_id%TYPE,
327 p_fund_code IN igf_aw_fund_mast_all.fund_code%TYPE,
328 p_date IN DATE
329 ) RETURN VARCHAR2 IS
330
331 /*
332 || Created By : brajendr
333 || Created On : 7-Nov-2002
334 || Purpose : Bug # 2613536
335 || Known limitations, enhancements or remarks :
336 || Change History :
337 || Who When What
338 || brajendr 31-Dec-2002 Bug # 2721995
339 || Added an extra condition in first if condition. ( fund_code IS NULL )
340 || gmaheswa 25-Aug-2004 Bug 3609966 removed check for closed indicator in c_get_enc_effect cursor.
341 || (reverse chronological order - newest change first)
342 */
343
344 -- Check whether the fund is present in Persons Exclusiions list
345 CURSOR c_chk_fund(
346 cp_person_id igf_ap_fa_base_rec_all.person_id%TYPE,
347 cp_fund_code igf_aw_fund_mast_all.fund_code%TYPE,
348 cp_encb_type igs_pe_persenc_effct.encumbrance_type%TYPE,
349 cp_effect_type VARCHAR2,
350 cp_date DATE
351 ) IS
352 SELECT 'x'
353 FROM dual
354 WHERE EXISTS ( SELECT 1
355 FROM igs_pe_fund_excl
356 WHERE fund_code = cp_fund_code
357 AND person_id = cp_person_id
358 AND encumbrance_type = cp_encb_type
359 AND s_encmb_effect_type = cp_effect_type
360 AND NVL(cp_date,TRUNC(sysdate)) BETWEEN TRUNC(pfe_start_dt) AND NVL(TRUNC(expiry_dt),TRUNC(sysdate))
361 );
362
363
364 -- Check whether Are there any Holds present for the person
365 CURSOR c_get_enc_effect(
366 cp_person_id igf_ap_fa_base_rec_all.person_id%TYPE,
367 cp_date DATE
368 ) IS
369 SELECT eff.encumbrance_type encb_type, eff.s_encmb_effect_type effect_type
370 FROM igs_fi_encmb_type typ,
371 igs_pe_pers_encumb enc,
372 igs_pe_persenc_effct eff
373 WHERE typ.s_encumbrance_cat = 'ACADEMIC'
374 AND typ.encumbrance_type = enc.encumbrance_type
375 AND NVL(cp_date, TRUNC(sysdate)) BETWEEN TRUNC(eff.pee_start_dt) AND NVL(TRUNC(eff.expiry_dt), TRUNC(sysdate))
376 AND enc.person_id = cp_person_id
377 AND enc.encumbrance_type = eff.encumbrance_type
378 AND eff.person_id = enc.person_id
379 AND eff.s_encmb_effect_type IN ('EX_AWD', 'EX_SP_AWD', 'EX_DISB', 'EX_SP_DISB');
380
381 lv_valid_fund VARCHAR2(1);
382
383 BEGIN
384
385 lv_valid_fund := NULL;
386
387 -- Return 'F' for invalid parameters
388 IF p_orig NOT IN ( 'A', 'D') THEN
389 RETURN 'F';
390 END IF;
391
392
393 -- Get all Financial Aid Holds at the student level
394 FOR c_get_enc_effect_rec IN c_get_enc_effect(p_person_id, p_date) LOOP
395
396 -- If called from Packaging and Person has "All Awards Hold" then return 'F'
397 IF p_orig = 'A' AND c_get_enc_effect_rec.effect_type = 'EX_AWD' AND p_fund_code IS NULL THEN
398 RETURN 'F';
399
400 -- If called from Packaging and Person has "Hold Specific Fund" then return 'F'
401 ELSIF p_orig = 'A' AND c_get_enc_effect_rec.effect_type = 'EX_SP_AWD' AND p_fund_code IS NOT NULL THEN
402 OPEN c_chk_fund( p_person_id, p_fund_code, c_get_enc_effect_rec.encb_type, 'EX_SP_AWD', p_date);
403 FETCH c_chk_fund INTO lv_valid_fund;
404 CLOSE c_chk_fund;
405
406 IF lv_valid_fund = 'x' THEN
407 RETURN 'F';
408 END IF;
409
410 -- If called from Disbursement and Person has "All Disbursement Hold" then return 'F'
411 ELSIF p_orig = 'D' AND c_get_enc_effect_rec.effect_type = 'EX_DISB' THEN
412 RETURN 'F';
413
414 -- If called from Disbursement and Person has "Hold Specific Fund Disbursement" then return 'F'
415 ELSIF p_orig = 'D' AND c_get_enc_effect_rec.effect_type = 'EX_SP_DISB' AND p_fund_code IS NOT NULL THEN
416 OPEN c_chk_fund( p_person_id, p_fund_code, c_get_enc_effect_rec.encb_type, 'EX_SP_DISB', p_date);
417 FETCH c_chk_fund INTO lv_valid_fund;
418 CLOSE c_chk_fund;
419
420 IF lv_valid_fund = 'x' THEN
421 RETURN 'F';
422 END IF;
423
424 END IF;
425
426 END LOOP;
427
428 RETURN 'S';
429 EXCEPTION
430 WHEN others THEN
431 RETURN 'F';
432 END get_stud_hold_effect;
433
434
435 FUNCTION validate_student_efc(
436 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
437 p_awd_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE
438 ) RETURN VARCHAR2 AS
439 /*
440 || Created By : brajendr
441 || Created On : 08-Jan-2003
442 || Purpose : This function checks whether the student has got the valid EFC at the ISIR Record or not.
443 || Returns 'T' if the EFC is calculated.
444 || Returns 'F' if the EFC is not calculated. ( NULL at ISIR RECORD )
445 || Known limitations, enhancements or remarks :
446 || Change History :
447 || Who When What
448 || (reverse chronological order - newest change first)
449 || veramach 16-Apr-2004 bug 3547237
450 || Enforced a check that if auto_zero_efc is set to 'Y' in the active_isir,
451 || then the EFC shown on the Summary tab must always be zero
452 || rasahoo 27-Nov-2003 FA 128 Isir Update
453 || Changed the Cursor c_chk_valid_efc as part of paid efc impact
454 */
455 l_efc NUMBER;
456 BEGIN
457 l_efc := igf_aw_gen_004.efc_f(p_base_id,p_awd_prd_code);
458 IF l_efc IS NOT NULL THEN
459 RETURN 'T';
460 ELSIF l_efc IS NULL THEN
461 RETURN 'F';
462 END IF;
463
464 END validate_student_efc;
465
466
467 END igf_aw_gen_005;
[Home] [Help]