[Home] [Help]
PACKAGE BODY: APPS.IGF_AW_MANAGE_AWD
Source
1 PACKAGE BODY igf_aw_manage_awd AS
2 /* $Header: IGFAW19B.pls 120.6 2006/02/08 23:42:35 ridas noship $ */
3
4 ------------------------------------------------------------------------------
5 -- Who When What
6 --------------------------------------------------------------------------------
7
8
9 PROCEDURE process_award(p_award_id IN igf_aw_award.award_id%TYPE,
10 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
11 p_run_mode IN VARCHAR2,
12 p_awd_proc_status IN VARCHAR2
13 ) IS
14 ------------------------------------------------------------------
15 --Created by : ridas, Oracle India
16 --Date created: 12-OCT-2004
17 --
18 --Purpose:
19 --
20 --
21 --Known limitations/enhancements and/or remarks:
22 --
23 --Change History:
24 --Who When What
25 --veramach 31-May-2005 FA 140 Student SS Enhancements
26 -- Added logic to publish/unpublish awards from student self-service
27 --
28 --ridas 14-Sep-2005 Bug #4103343. Added a new message to print in the log file when the
29 -- Award Process Status gets update
30 -------------------------------------------------------------------
31
32 --
33 -- This cursor is opened for udpating awards based on run mode
34 --
35 CURSOR c_awd_mode(c_award_id igf_aw_award.award_id%TYPE,
36 c_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
37 c_run_mode VARCHAR2
38 ) IS
39 SELECT
40 awd.ROWID row_id,awd.*,fmast.fund_code
41 FROM
42 igf_aw_award_all awd,
43 igf_aw_fund_mast_all fmast
44 WHERE
45 awd.award_id = c_award_id AND
46 awd.base_id = c_base_id AND
47 fmast.fund_id = awd.fund_id AND
48 DECODE(c_run_mode,
49 'L',NVL(awd.lock_award_flag,'N'),
50 'U',NVL(awd.lock_award_flag,'N'),
51 'PB',NVL(awd.publish_in_ss_flag,'N'),
52 'UP',NVL(awd.publish_in_ss_flag,'N')
53 ) <> DECODE(c_run_mode,
54 'L','Y',
55 'U','N',
56 'PB','Y',
57 'UP','N'
58 )
59 FOR UPDATE OF awd.lock_award_flag,awd.awd_proc_status_code,awd.publish_in_ss_flag NOWAIT;
60
61
62 --
63 -- This cursor is opened for udpating awards
64 --
65 CURSOR c_awd(c_award_id igf_aw_award.award_id%TYPE,c_base_id igf_ap_fa_base_rec_all.base_id%TYPE) IS
66 SELECT
67 awd.ROWID row_id,awd.*,fmast.fund_code
68 FROM
69 igf_aw_award_all awd,
70 igf_aw_fund_mast_all fmast
71 WHERE
72 awd.award_id = c_award_id AND
73 awd.base_id = c_base_id AND
74 fmast.fund_id = awd.fund_id
75 FOR UPDATE OF awd.lock_award_flag,
76 awd.awd_proc_status_code,
77 awd.publish_in_ss_flag NOWAIT;
78
79 lc_awd c_awd%ROWTYPE;
80
81 lv_lock_award_flag igf_aw_award_all.lock_award_flag%TYPE;
82 lv_publish_in_ss_flag igf_aw_award_all.publish_in_ss_flag%TYPE;
83 lv_awd_proc_status_code igf_aw_award_all.awd_proc_status_code%TYPE;
84
85 BEGIN
86 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
87 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_manage_awd.process_award.debug','Starting process_award with award_id:'||p_award_id);
88 END IF;
89
90 --open cursor based on run mode
91 IF p_run_mode IN ('L','U','PB','UP') THEN
92 OPEN c_awd_mode(p_award_id,p_base_id,p_run_mode);
93 FETCH c_awd_mode INTO lc_awd;
94
95 IF c_awd_mode%NOTFOUND THEN
96 CLOSE c_awd_mode;
97 RETURN;
98 END IF;
99 CLOSE c_awd_mode;
100 ELSE
101 OPEN c_awd(p_award_id,p_base_id);
102 FETCH c_awd INTO lc_awd;
103
104 IF c_awd%NOTFOUND THEN
105 CLOSE c_awd;
106 RETURN;
107 END IF;
108 CLOSE c_awd;
109 END IF;
110
111
112 IF p_run_mode = 'L' THEN
113 lv_lock_award_flag := 'Y';
114 lv_publish_in_ss_flag := lc_awd.publish_in_ss_flag;
115 lv_awd_proc_status_code := lc_awd.awd_proc_status_code;
116
117 fnd_message.set_name('IGF','IGF_AW_LOCK_STUD');
118 fnd_message.set_token('FUND',lc_awd.fund_code);
119 fnd_message.set_token('AWARD',p_award_id);
120 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
121
122 ELSIF p_run_mode = 'U' THEN
123 lv_lock_award_flag := 'N';
124 lv_publish_in_ss_flag := lc_awd.publish_in_ss_flag;
125 lv_awd_proc_status_code := lc_awd.awd_proc_status_code;
126
127 fnd_message.set_name('IGF','IGF_AW_UNLOCK_STUD');
128 fnd_message.set_token('FUND',lc_awd.fund_code);
129 fnd_message.set_token('AWARD',p_award_id);
130 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
131
132 ELSIF p_run_mode = 'PB' THEN
133 lv_lock_award_flag := lc_awd.lock_award_flag;
134 lv_publish_in_ss_flag := 'Y';
135 lv_awd_proc_status_code := lc_awd.awd_proc_status_code;
136
137 fnd_message.set_name('IGF','IGF_AW_AWD_PUBLISH');
138 fnd_message.set_token('FUNDCODE',lc_awd.fund_code);
139 fnd_message.set_token('AWDID',p_award_id);
140 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
141
142 ELSIF p_run_mode = 'UP' THEN
143 lv_lock_award_flag := lc_awd.lock_award_flag;
144 lv_publish_in_ss_flag := 'N';
145 lv_awd_proc_status_code := lc_awd.awd_proc_status_code;
146
147 fnd_message.set_name('IGF','IGF_AW_AWD_UNPUBLISH');
148 fnd_message.set_token('FUNDCODE',lc_awd.fund_code);
149 fnd_message.set_token('AWDID',p_award_id);
150 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
151
152 ELSIF p_run_mode = 'S' THEN
153 lv_lock_award_flag := lc_awd.lock_award_flag;
154 lv_publish_in_ss_flag := lc_awd.publish_in_ss_flag;
155 lv_awd_proc_status_code := p_awd_proc_status;
156 END IF;
157
158 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
159 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_manage_awd.process_award.debug','Updating igf_aw_award_all with lock='||lv_lock_award_flag||' and process_status='||lv_awd_proc_status_code);
160 END IF;
161
162 igf_aw_award_pkg.update_row (
163 x_mode => 'R',
164 x_rowid => lc_awd.ROW_ID,
165 x_award_id => lc_awd.AWARD_ID,
166 x_fund_id => lc_awd.FUND_ID,
167 x_base_id => lc_awd.BASE_ID,
168 x_offered_amt => lc_awd.OFFERED_AMT,
169 x_accepted_amt => lc_awd.ACCEPTED_AMT,
170 x_paid_amt => lc_awd.PAID_AMT,
171 x_packaging_type => lc_awd.PACKAGING_TYPE,
172 x_batch_id => lc_awd.BATCH_ID,
173 x_manual_update => lc_awd.MANUAL_UPDATE,
174 x_rules_override => lc_awd.RULES_OVERRIDE,
175 x_award_date => lc_awd.AWARD_DATE,
176 x_award_status => lc_awd.AWARD_STATUS,
177 x_attribute_category => lc_awd.ATTRIBUTE_CATEGORY,
178 x_attribute1 => lc_awd.ATTRIBUTE1,
179 x_attribute2 => lc_awd.ATTRIBUTE2,
180 x_attribute3 => lc_awd.ATTRIBUTE3,
181 x_attribute4 => lc_awd.ATTRIBUTE4,
182 x_attribute5 => lc_awd.ATTRIBUTE5,
183 x_attribute6 => lc_awd.ATTRIBUTE6,
184 x_attribute7 => lc_awd.ATTRIBUTE7,
185 x_attribute8 => lc_awd.ATTRIBUTE8,
186 x_attribute9 => lc_awd.ATTRIBUTE9,
187 x_attribute10 => lc_awd.ATTRIBUTE10,
188 x_attribute11 => lc_awd.ATTRIBUTE11,
189 x_attribute12 => lc_awd.ATTRIBUTE12,
190 x_attribute13 => lc_awd.ATTRIBUTE13,
191 x_attribute14 => lc_awd.ATTRIBUTE14,
192 x_attribute15 => lc_awd.ATTRIBUTE15,
193 x_attribute16 => lc_awd.ATTRIBUTE16,
194 x_attribute17 => lc_awd.ATTRIBUTE17,
195 x_attribute18 => lc_awd.ATTRIBUTE18,
196 x_attribute19 => lc_awd.ATTRIBUTE19,
197 x_attribute20 => lc_awd.ATTRIBUTE20,
198 x_rvsn_id => lc_awd.RVSN_ID ,
199 x_alt_pell_schedule => lc_awd.ALT_PELL_SCHEDULE,
200 x_award_number_txt => lc_awd.AWARD_NUMBER_TXT,
201 x_legacy_record_flag => lc_awd.legacy_record_flag,
202 x_adplans_id => lc_awd.adplans_id,
203 x_lock_award_flag => lv_lock_award_flag,
204 x_app_trans_num_txt => lc_awd.app_trans_num_txt,
205 x_awd_proc_status_code => lv_awd_proc_status_code,
206 x_notification_status_code => lc_awd.notification_status_code,
207 x_notification_status_date => lc_awd.notification_status_date,
208 x_publish_in_ss_flag => lv_publish_in_ss_flag
209 );
210
211 IF p_run_mode = 'S' THEN
212 fnd_message.set_name('IGF','IGF_AW_AWD_PROC');
213 fnd_message.set_token('PROC_STATUS',p_awd_proc_status);
214 fnd_message.set_token('AWARD',p_award_id);
215 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
216 END IF;
217
218 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
219 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_manage_awd.process_award.debug','End of Update');
220 END IF;
221
222 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
223 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_manage_awd.process_award.debug','process_award is done');
224 END IF;
225
226 EXCEPTION
227 WHEN OTHERS THEN
228 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
229 fnd_message.set_token('NAME','IGF_AW_MANAGE_AWD.PROCESS_AWARD :' || SQLERRM);
230 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
231 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_manage_awd.process_award.exception','sql error:'||SQLERRM);
232 END IF;
233 igs_ge_msg_stack.conc_exception_hndl;
234 app_exception.raise_exception;
235
236 END process_award;
237
238
239 --Procedure to Lock/Unlock Award at the Student Level
240 PROCEDURE lock_unlock_stud(p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
241 p_run_mode IN VARCHAR2
242 ) IS
243 ------------------------------------------------------------------
244 --Created by : ridas, Oracle India
245 --Date created: 12-OCT-2004
246 --
247 --Purpose:
248 --
249 --
250 --Known limitations/enhancements and/or remarks:
251 --
252 --Change History:
253 --Who When What
254 -------------------------------------------------------------------
255
256
257 --This cursor is to fetch person details
258 CURSOR cur_upd_base (c_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
259 c_run_mode VARCHAR2) IS
260 SELECT fab.*
261 FROM igf_ap_fa_base_rec fab
262 WHERE fab.base_id = c_base_id
263 AND NVL(lock_awd_flag,'N') <> DECODE(c_run_mode,'L','Y','N')
264 FOR UPDATE OF lock_coa_flag NOWAIT;
265
266 cur_fbr_rec cur_upd_base%ROWTYPE;
267
268 lv_lock_award_flag igf_aw_award_all.lock_award_flag%TYPE;
269
270 BEGIN
271
272 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
273 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_manage_awd.lock_unlock_stud.debug','Starting lock_unlock_stud with base_id:'||p_base_id);
274 END IF;
275
276 OPEN cur_upd_base(p_base_id,p_run_mode);
277 FETCH cur_upd_base INTO cur_fbr_rec;
278
279 IF cur_upd_base%NOTFOUND THEN
280 CLOSE cur_upd_base;
281 RETURN;
282 END IF;
283 CLOSE cur_upd_base;
284
285 IF p_run_mode = 'L' THEN
286 lv_lock_award_flag := 'Y';
287 fnd_message.set_name('IGF','IGF_AW_LOCK_STUD_LVL');
288 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
289
290 ELSIF p_run_mode = 'U' THEN
291 lv_lock_award_flag := 'N';
292 fnd_message.set_name('IGF','IGF_AW_UNLOCK_STUD_LVL');
293 fnd_file.put_line(fnd_file.log,RPAD(' ',5)||fnd_message.get);
294 END IF;
295
296 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
297 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_manage_awd.lock_unlock_stud.debug','Updating igf_ap_fa_base_rec_all with lock='||lv_lock_award_flag);
298 END IF;
299
300 igf_ap_fa_base_rec_pkg.update_row
301 (x_Mode => 'R',
302 x_rowid => cur_fbr_rec.row_id,
303 x_base_id => cur_fbr_rec.base_id,
304 x_ci_cal_type => cur_fbr_rec.ci_cal_type,
305 x_person_id => cur_fbr_rec.person_id,
306 x_ci_sequence_number => cur_fbr_rec.ci_sequence_number,
307 x_org_id => cur_fbr_rec.org_id,
308 x_coa_pending => cur_fbr_rec.coa_pending,
309 x_verification_process_run => cur_fbr_rec.verification_process_run,
310 x_inst_verif_status_date => cur_fbr_rec.inst_verif_status_date,
311 x_manual_verif_flag => cur_fbr_rec.manual_verif_flag,
312 x_fed_verif_status => cur_fbr_rec.fed_verif_status,
313 x_fed_verif_status_date => cur_fbr_rec.fed_verif_status_date,
314 x_inst_verif_status => cur_fbr_rec.inst_verif_status,
315 x_nslds_eligible => cur_fbr_rec.nslds_eligible,
316 x_ede_correction_batch_id => cur_fbr_rec.ede_correction_batch_id,
317 x_fa_process_status_date => cur_fbr_rec.fa_process_status_date,
318 x_ISIR_corr_status => cur_fbr_rec.ISIR_corr_status,
319 x_ISIR_corr_status_date => cur_fbr_rec.ISIR_corr_status_date,
320 x_ISIR_status => cur_fbr_rec.ISIR_status,
321 x_ISIR_status_date => cur_fbr_rec.ISIR_status_date,
322 x_coa_code_f => cur_fbr_rec.coa_code_f,
323 x_coa_code_i => cur_fbr_rec.coa_code_i,
324 x_coa_f => cur_fbr_rec.coa_f,
325 x_coa_i => cur_fbr_rec.coa_i,
326 x_disbursement_hold => cur_fbr_rec.disbursement_hold,
327 x_fa_process_status => cur_fbr_rec.fa_process_status,
328 x_notification_status => cur_fbr_rec.notification_status,
329 x_notification_status_date => cur_fbr_rec.notification_status_date,
330 x_packaging_status => cur_fbr_rec.packaging_status,
331 x_packaging_status_date => cur_fbr_rec.packaging_status_date,
332 x_total_package_accepted => cur_fbr_rec.total_package_accepted,
333 x_total_package_offered => cur_fbr_rec.total_package_offered,
334 x_admstruct_id => cur_fbr_rec.admstruct_id,
335 x_admsegment_1 => cur_fbr_rec.admsegment_1,
336 x_admsegment_2 => cur_fbr_rec.admsegment_2,
337 x_admsegment_3 => cur_fbr_rec.admsegment_3,
338 x_admsegment_4 => cur_fbr_rec.admsegment_4,
339 x_admsegment_5 => cur_fbr_rec.admsegment_5,
340 x_admsegment_6 => cur_fbr_rec.admsegment_6,
341 x_admsegment_7 => cur_fbr_rec.admsegment_7,
342 x_admsegment_8 => cur_fbr_rec.admsegment_8,
343 x_admsegment_9 => cur_fbr_rec.admsegment_9,
344 x_admsegment_10 => cur_fbr_rec.admsegment_10,
345 x_admsegment_11 => cur_fbr_rec.admsegment_11,
346 x_admsegment_12 => cur_fbr_rec.admsegment_12,
347 x_admsegment_13 => cur_fbr_rec.admsegment_13,
348 x_admsegment_14 => cur_fbr_rec.admsegment_14,
349 x_admsegment_15 => cur_fbr_rec.admsegment_15,
350 x_admsegment_16 => cur_fbr_rec.admsegment_16,
351 x_admsegment_17 => cur_fbr_rec.admsegment_17,
352 x_admsegment_18 => cur_fbr_rec.admsegment_18,
353 x_admsegment_19 => cur_fbr_rec.admsegment_19,
354 x_admsegment_20 => cur_fbr_rec.admsegment_20,
355 x_packstruct_id => cur_fbr_rec.packstruct_id,
356 x_packsegment_1 => cur_fbr_rec.packsegment_1,
357 x_packsegment_2 => cur_fbr_rec.packsegment_2,
358 x_packsegment_3 => cur_fbr_rec.packsegment_3,
359 x_packsegment_4 => cur_fbr_rec.packsegment_4,
360 x_packsegment_5 => cur_fbr_rec.packsegment_5,
361 x_packsegment_6 => cur_fbr_rec.packsegment_6,
362 x_packsegment_7 => cur_fbr_rec.packsegment_7,
363 x_packsegment_8 => cur_fbr_rec.packsegment_8,
364 x_packsegment_9 => cur_fbr_rec.packsegment_9,
365 x_packsegment_10 => cur_fbr_rec.packsegment_10,
366 x_packsegment_11 => cur_fbr_rec.packsegment_11,
367 x_packsegment_12 => cur_fbr_rec.packsegment_12,
368 x_packsegment_13 => cur_fbr_rec.packsegment_13,
369 x_packsegment_14 => cur_fbr_rec.packsegment_14,
370 x_packsegment_15 => cur_fbr_rec.packsegment_15,
371 x_packsegment_16 => cur_fbr_rec.packsegment_16,
372 x_packsegment_17 => cur_fbr_rec.packsegment_17,
373 x_packsegment_18 => cur_fbr_rec.packsegment_18,
374 x_packsegment_19 => cur_fbr_rec.packsegment_19,
375 x_packsegment_20 => cur_fbr_rec.packsegment_20,
376 x_miscstruct_id => cur_fbr_rec.miscstruct_id,
377 x_miscsegment_1 => cur_fbr_rec.miscsegment_1,
378 x_miscsegment_2 => cur_fbr_rec.miscsegment_2,
379 x_miscsegment_3 => cur_fbr_rec.miscsegment_3,
380 x_miscsegment_4 => cur_fbr_rec.miscsegment_4,
381 x_miscsegment_5 => cur_fbr_rec.miscsegment_5,
382 x_miscsegment_6 => cur_fbr_rec.miscsegment_6,
383 x_miscsegment_7 => cur_fbr_rec.miscsegment_7,
384 x_miscsegment_8 => cur_fbr_rec.miscsegment_8,
385 x_miscsegment_9 => cur_fbr_rec.miscsegment_9,
386 x_miscsegment_10 => cur_fbr_rec.miscsegment_10,
387 x_miscsegment_11 => cur_fbr_rec.miscsegment_11,
388 x_miscsegment_12 => cur_fbr_rec.miscsegment_12,
389 x_miscsegment_13 => cur_fbr_rec.miscsegment_13,
390 x_miscsegment_14 => cur_fbr_rec.miscsegment_14,
391 x_miscsegment_15 => cur_fbr_rec.miscsegment_15,
392 x_miscsegment_16 => cur_fbr_rec.miscsegment_16,
393 x_miscsegment_17 => cur_fbr_rec.miscsegment_17,
394 x_miscsegment_18 => cur_fbr_rec.miscsegment_18,
395 x_miscsegment_19 => cur_fbr_rec.miscsegment_19,
396 x_miscsegment_20 => cur_fbr_rec.miscsegment_20,
397 x_prof_judgement_flg => cur_fbr_rec.prof_judgement_flg,
398 x_nslds_data_override_flg => cur_fbr_rec.nslds_data_override_flg,
399 x_target_group => cur_fbr_rec.target_group,
400 x_coa_fixed => cur_fbr_rec.coa_fixed,
401 x_profile_status => cur_fbr_rec.profile_status,
402 x_profile_status_date => cur_fbr_rec.profile_status_date,
403 x_profile_fc => cur_fbr_rec.profile_fc,
404 x_coa_pell => cur_fbr_rec.coa_pell,
405 x_manual_disb_hold => cur_fbr_rec.manual_disb_hold,
406 x_pell_alt_expense => cur_fbr_rec.pell_alt_expense,
407 x_assoc_org_num => cur_fbr_rec.assoc_org_num,
408 x_award_fmly_contribution_type => cur_fbr_rec.award_fmly_contribution_type,
409 x_packaging_hold => cur_fbr_rec.packaging_hold,
410 x_isir_locked_by => cur_fbr_rec.isir_locked_by ,
411 x_adnl_unsub_loan_elig_flag => cur_fbr_rec.adnl_unsub_loan_elig_flag,
412 x_lock_awd_flag => lv_lock_award_flag,
413 x_lock_coa_flag => cur_fbr_rec.lock_coa_flag
414 );
415
416 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
417 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_manage_awd.lock_unlock_stud.debug','End of Update');
418 END IF;
419
420 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
421 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_manage_awd.lock_unlock_stud.debug','lock_unlock_stud is done');
422 END IF;
423
424 EXCEPTION
425 WHEN OTHERS THEN
426 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
427 fnd_message.set_token('NAME','IGF_AW_MANAGE_AWD.LOCK_UNLOCK_STUD :' || SQLERRM);
428 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
429 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_manage_awd.lock_unlock_stud.exception','sql error:'||SQLERRM);
430 END IF;
431 igs_ge_msg_stack.conc_exception_hndl;
432 app_exception.raise_exception;
433
434 END lock_unlock_stud;
435
436
437
438 -- This procedure is the callable from concurrent manager
439 PROCEDURE run(
440 errbuf OUT NOCOPY VARCHAR2,
441 retcode OUT NOCOPY NUMBER,
442 p_award_year IN VARCHAR2,
443 p_award_period IN igf_aw_award_prd.award_prd_cd%TYPE,
444 p_run_type IN VARCHAR2,
445 p_pid_group IN igs_pe_prsid_grp_mem_all.group_id%TYPE,
446 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
447 p_run_mode IN VARCHAR2,
448 p_awd_proc_status IN VARCHAR2,
449 p_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE
450 ) IS
451 --------------------------------------------------------------------------------
452 -- this procedure is called from concurrent manager.
453 -- if the parameters passed are not correct then procedure exits
454 -- giving reasons for errors.
455 -- Created by : ridas, Oracle India
456 -- Date created: 12-OCT-2004
457
458 -- Change History:
459 -- Who When What
460 -- ridas 08-Feb-2006 Bug #5021084. Added new parameter 'lv_group_type' in
461 -- call to igf_ap_ss_pkg.get_pid
462 -- tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
463 -- veramach 31-May-2005 FA 140 Student SS Enhancements
464 -- Added logic to log parameters only if they are not null
465 --------------------------------------------------------------------------------
466
467 param_exception EXCEPTION;
468
469 -- Variables for the dynamic person id group
470 lv_status VARCHAR2(1);
471 lv_sql_stmt VARCHAR(32767);
472 lv_group_type igs_pe_persid_group_v.group_type%TYPE;
473
474 TYPE CpregrpCurTyp IS REF CURSOR ;
475 cur_per_grp CpregrpCurTyp ;
476
477 TYPE CpergrpTyp IS RECORD(
478 person_id igf_ap_fa_base_rec_all.person_id%TYPE,
479 person_number igs_pe_person_base_v.person_number%TYPE
480 );
481 per_grp_rec CpergrpTyp ;
482
483
484 --Cursor below retrieves all the students belonging to a given AWARD YEAR
485 CURSOR c_per_awd_yr(
486 c_ci_cal_type igf_ap_fa_base_rec_all.ci_cal_type%TYPE,
487 c_ci_sequence_number igf_ap_fa_base_rec_all.ci_sequence_number%TYPE
488 ) IS
489 SELECT fa.base_id
490 FROM igf_ap_fa_base_rec_all fa
491 WHERE fa.ci_cal_type = c_ci_cal_type
492 AND fa.ci_sequence_number = c_ci_sequence_number
493 ORDER BY fa.base_id;
494
495
496 --Cursor below retrieves the group code for the given group id
497 CURSOR c_group_code(
498 c_grp_id igs_pe_prsid_grp_mem_all.group_id%TYPE
499 ) IS
500 SELECT group_cd
501 FROM igs_pe_persid_group_all
502 WHERE group_id = c_grp_id;
503
504 l_grp_cd c_group_code%ROWTYPE;
505
506
507 --Curson below retrieves the fund code for the given fund id
508 CURSOR c_fund_code(
509 c_fund_id igf_aw_fund_mast_all.fund_id%TYPE
510 ) IS
511 SELECT fund_code
512 FROM igf_aw_fund_mast_all
513 WHERE fund_id = c_fund_id;
514
515 l_fund_code c_fund_code%ROWTYPE;
516
517
518 --Cursor to filter out those awards that fall within the awarding period
519 CURSOR c_awards(
520 c_ci_cal_type igf_aw_award_prd.ci_cal_type%TYPE,
521 c_ci_sequence_number igf_aw_award_prd.ci_sequence_number%TYPE,
522 c_award_prd_cd igf_aw_award_prd.award_prd_cd%TYPE,
523 c_base_id igf_ap_fa_base_rec_all.base_id%TYPE ,
524 c_fund_id igf_aw_fund_mast_all.fund_id%TYPE
525 ) IS
526 SELECT awd.award_id
527 FROM igf_aw_award_all awd
528 WHERE awd.base_id = c_base_id AND
529 awd.fund_id = NVL(c_fund_id, awd.fund_id) AND
530 NOT EXISTS
531 (SELECT disb.ld_cal_type,
532 disb.ld_sequence_number
533 FROM igf_aw_awd_disb_all disb
534 WHERE disb.award_id = awd.award_id
535 MINUS
536 SELECT ld_cal_type,
537 ld_sequence_number
538 FROM igf_aw_awd_prd_term apt
539 WHERE apt.ci_cal_type = c_ci_cal_type AND
540 apt.ci_sequence_number = c_ci_sequence_number AND
541 apt.award_prd_cd = c_award_prd_cd
542 );
543
544
545 --Cursor to fetch person no based on person id
546 CURSOR c_person_no (c_person_id hz_parties.party_id%TYPE)
547 IS
548 SELECT party_number
549 FROM hz_parties
550 WHERE party_id = c_person_id;
551
552 l_person_no c_person_no%ROWTYPE;
553
554
555 --Cursor to retrieve all awards for the award year
556 CURSOR c_award_yr(
557 c_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
558 c_fund_id igf_aw_fund_mast_all.fund_id%TYPE
559 ) IS
560 SELECT awd.award_id
561 FROM igf_aw_award_all awd
562 WHERE awd.base_id = c_base_id AND
563 awd.fund_id = NVL(c_fund_id, awd.fund_id);
564
565
566 lv_run_type VARCHAR2(100);
567 lv_ci_cal_type igs_ca_inst_all.cal_type%TYPE;
568 ln_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE;
569 ln_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
570 lv_err_msg fnd_new_messages.message_name%TYPE;
571
572
573 BEGIN
574 igf_aw_gen.set_org_id(NULL);
575 retcode := 0;
576 errbuf := NULL;
577 lv_ci_cal_type := LTRIM(RTRIM(SUBSTR(p_award_year,1,10)));
578 ln_ci_sequence_number := TO_NUMBER(SUBSTR(p_award_year,11));
579 lv_status := 'S'; /*Defaulted to 'S' and the function will return 'F' in case of failure */
580
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_manage_awd.run.debug','p_award_year:'||p_award_year);
584 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_manage_awd.run.debug','p_award_period:'||p_award_period);
585 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_manage_awd.run.debug','p_run_type:'||p_run_type);
586 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_manage_awd.run.debug','p_pid_group:'||p_pid_group);
587 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_manage_awd.run.debug','p_base_id:'||p_base_id);
588 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_manage_awd.run.debug','p_run_mode:'||p_run_mode);
589 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_manage_awd.run.debug','p_awd_proc_status:'||p_awd_proc_status);
590 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_manage_awd.run.debug','p_fund_id:'||p_fund_id);
591 END IF;
592
593 fnd_file.new_line(fnd_file.log,1);
594
595 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PARAMETER_PASS'));
596 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','AWARD_YEAR'),40) ||': '|| igf_gr_gen.get_alt_code(lv_ci_cal_type,ln_ci_sequence_number));
597 IF p_award_period IS NOT NULL THEN
598 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','AWARD_PERIOD'),40)||': '||p_award_period );
599 END IF;
600
601 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','RUN_TYPE'),40)||': '||p_run_type );
602
603 IF p_pid_group IS NOT NULL THEN
604 OPEN c_group_code(p_pid_group);
605 FETCH c_group_code INTO l_grp_cd;
606 CLOSE c_group_code;
607
608 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PERSON_ID_GROUP'),40) ||': '|| l_grp_cd.group_cd);
609 END IF;
610 IF p_base_id IS NOT NULL THEN
611 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PERSON_NUMBER'),40) ||': '|| igf_gr_gen.get_per_num(p_base_id));
612 END If;
613 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','RUN_MODE'),40) ||': '||igf_aw_gen.lookup_desc('IGF_AW_LOCK_MODE',p_run_mode));
614 IF p_awd_proc_status IS NOT NULL THEN
615 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','AWARD_PROC_STATUS'),40) ||': '|| igf_aw_gen.lookup_desc('IGF_AW_AWD_PROC_STAT',p_awd_proc_status));
616 END IF;
617
618 IF p_fund_id IS NOT NULL THEN
619 OPEN c_fund_code(p_fund_id);
620 FETCH c_fund_code INTO l_fund_code;
621 CLOSE c_fund_code;
622
623 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','FUND_CODE'),40) ||': '|| l_fund_code.fund_code);
624 END IF;
625
626
627 IF (p_award_year IS NULL) OR (p_run_type IS NULL) OR (p_run_mode IS NULL) THEN
628 RAISE param_exception;
629
630 ELSIF lv_ci_cal_type IS NULL OR ln_ci_sequence_number IS NULL THEN
631 RAISE param_exception;
632
633 ELSIF (p_pid_group IS NOT NULL) AND (p_base_id IS NOT NULL) THEN
634 RAISE param_exception;
635
636 --If person selection is for all persons in the Person ID Group and
637 --Person ID Group is NULL then log error with exception
638 ELSIF p_run_type = 'P' AND p_pid_group IS NULL THEN
639 fnd_message.set_name('IGF','IGF_AW_COA_PARAM_EX_P');
640 fnd_file.put_line(fnd_file.log,fnd_message.get);
641 RAISE param_exception;
642
643 --If person selection is for a single person and
644 --Base ID is NULL then log error with exception
645 ELSIF p_run_type = 'S' AND p_base_id IS NULL THEN
646 fnd_message.set_name('IGF','IGF_AW_COA_PARAM_EX_S');
647 fnd_file.put_line(fnd_file.log,fnd_message.get);
648 RAISE param_exception;
649
650 --If the Run Mode is 'Set Award Process Status' and
651 --Fund ID is NOT NULL then log error with exception
652 ELSIF p_run_mode = 'S' AND p_fund_id IS NOT NULL THEN
653 fnd_message.set_name('IGF','IGF_AW_MANG_AW_FUND_ERR');
654 fnd_file.put_line(fnd_file.log,fnd_message.get);
655 RAISE param_exception;
656 END IF;
657
658 fnd_file.put_line(fnd_file.log,'-------------------------------------------------------');
659
660 --COMPUTATION ONLY IF PERSON NUMBER IS PRESENT
661 IF p_run_type = 'S' AND (p_pid_group IS NULL) AND (p_base_id IS NOT NULL) THEN
662
663 fnd_file.new_line(fnd_file.log,1);
664 fnd_message.set_name('IGF','IGF_AW_PROC_STUD');
665 fnd_message.set_token('STDNT',igf_gr_gen.get_per_num(p_base_id));
666 fnd_file.put_line(fnd_file.log,fnd_message.get);
667
668 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
669 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_manage_awd.run.debug','Starting Run_Type=S with base_id:'||p_base_id);
670 END IF;
671
672 IF p_award_period IS NOT NULL THEN
673 FOR l_awards IN c_awards(lv_ci_cal_type,ln_ci_sequence_number,p_award_period,p_base_id,p_fund_id)
674 LOOP
675 process_award(l_awards.award_id,
676 p_base_id,
677 p_run_mode,
678 p_awd_proc_status
679 );
680
681 END LOOP; -- end of cursor c_awards
682 ELSE
683 FOR l_award_year IN c_award_yr(p_base_id,p_fund_id)
684 LOOP
685 process_award(l_award_year.award_id,
686 p_base_id,
687 p_run_mode,
688 p_awd_proc_status
689 );
690 END LOOP; -- end of cursor c_award_year
691
692 -- update lock/unlock award at the student level
693 IF (p_award_period IS NULL AND p_fund_id IS NULL AND p_run_mode IN ('L','U')) THEN
694 lock_unlock_stud(p_base_id,p_run_mode);
695 END IF;
696 END IF;
697
698 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
699 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_manage_awd.run.debug','Run_Type=S done');
700 END IF;
701
702 --COMPUTATION FOR AWARD YEAR ONLY
703 ELSIF p_run_type = 'Y' AND (p_pid_group IS NULL) AND (p_base_id IS NULL) THEN
704 FOR l_per_awd_rec IN c_per_awd_yr(lv_ci_cal_type,ln_ci_sequence_number)
705 LOOP
706 fnd_file.new_line(fnd_file.log,1);
707 fnd_message.set_name('IGF','IGF_AW_PROC_STUD');
708 fnd_message.set_token('STDNT',igf_gr_gen.get_per_num(l_per_awd_rec.base_id));
709 fnd_file.put_line(fnd_file.log,fnd_message.get);
710
711 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
712 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_manage_awd.run.debug','Starting Run_Type=Y with base_id:'||l_per_awd_rec.base_id);
713 END IF;
714
715 IF p_award_period IS NOT NULL THEN
716 FOR l_awards IN c_awards(lv_ci_cal_type,ln_ci_sequence_number,p_award_period,l_per_awd_rec.base_id,p_fund_id)
717 LOOP
718 process_award(l_awards.award_id,
719 l_per_awd_rec.base_id,
720 p_run_mode,
721 p_awd_proc_status
722 );
723
724 END LOOP; -- end of cursor c_awards
725 ELSE
726 FOR l_award_year IN c_award_yr(l_per_awd_rec.base_id,p_fund_id)
727 LOOP
728 process_award(l_award_year.award_id,
729 l_per_awd_rec.base_id,
730 p_run_mode,
731 p_awd_proc_status
732 );
733 END LOOP; -- end of cursor c_award_year
734
735 -- update lock/unlock award at the student level
736 IF (p_award_period IS NULL AND p_fund_id IS NULL AND p_run_mode IN ('L','U')) THEN
737 lock_unlock_stud(l_per_awd_rec.base_id,p_run_mode);
738 END IF;
739 END IF;
740 END LOOP;
741
742 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
743 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_manage_awd.run.debug','Run_Type=Y done');
744 END IF;
745
746 --COMPUTATION FOR ALL PERSONS IN THE PERSON ID GROUP
747 ELSIF (p_run_type = 'P' AND p_pid_group IS NOT NULL) THEN
748 --Bug #5021084
749 lv_sql_stmt := igf_ap_ss_pkg.get_pid(p_pid_group,lv_status,lv_group_type);
750
751 --Bug #5021084. Passing Group ID if the group type is STATIC.
752 IF lv_group_type = 'STATIC' THEN
753 OPEN cur_per_grp FOR
754 'SELECT person_id,
755 person_number
756 FROM igs_pe_person_base_v
757 WHERE person_id IN ('||lv_sql_stmt||') ' USING p_pid_group;
758 ELSIF lv_group_type = 'DYNAMIC' THEN
759 OPEN cur_per_grp FOR
760 'SELECT person_id,
761 person_number
762 FROM igs_pe_person_base_v
763 WHERE person_id IN ('||lv_sql_stmt||')';
764 END IF;
765
766 FETCH cur_per_grp INTO per_grp_rec;
767
768 IF (cur_per_grp%NOTFOUND) THEN
769 fnd_message.set_name('IGF','IGF_DB_NO_PER_GRP');
770 fnd_file.put_line(fnd_file.log,fnd_message.get);
771 ELSE
772 LOOP
773 -- check if person has a fa base record
774 ln_base_id := NULL;
775 lv_err_msg := NULL;
776
777 igf_gr_gen.get_base_id(
778 lv_ci_cal_type,
779 ln_ci_sequence_number,
780 per_grp_rec.person_id,
781 ln_base_id,
782 lv_err_msg
783 );
784
785 IF lv_err_msg = 'NULL' THEN
786 fnd_file.new_line(fnd_file.log,1);
787 fnd_message.set_name('IGF','IGF_AW_PROC_STUD');
788 fnd_message.set_token('STDNT',igf_gr_gen.get_per_num(ln_base_id));
789 fnd_file.put_line(fnd_file.log,fnd_message.get);
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_manage_awd.run.debug','Starting Run_Type=P with base_id:'||ln_base_id);
793 END IF;
794
795 IF p_award_period IS NOT NULL THEN
796 FOR l_awards IN c_awards(lv_ci_cal_type,ln_ci_sequence_number,p_award_period,ln_base_id,p_fund_id)
797 LOOP
798 process_award(l_awards.award_id,
799 ln_base_id,
800 p_run_mode,
801 p_awd_proc_status
802 );
803
804 END LOOP; -- end of cursor c_awards
805 ELSE
806 FOR l_award_year IN c_award_yr(ln_base_id,p_fund_id)
807 LOOP
808 process_award(l_award_year.award_id,
809 ln_base_id,
810 p_run_mode,
811 p_awd_proc_status
812 );
813 END LOOP; -- end of cursor c_award_year
814
815 -- update lock/unlock award at the student level
816 IF (p_award_period IS NULL AND p_fund_id IS NULL AND p_run_mode IN ('L','U')) THEN
817 lock_unlock_stud(ln_base_id,p_run_mode);
818 END IF;
819 END IF;
820
821 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
822 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_manage_awd.run.debug','Run_Type=P done');
823 END IF;
824
825 ELSE
826 OPEN c_person_no(per_grp_rec.person_id);
827 FETCH c_person_no INTO l_person_no;
828 CLOSE c_person_no;
829
830 fnd_message.set_name('IGF','IGF_AP_NO_BASEREC');
831 fnd_message.set_token('STUD',l_person_no.party_number);
832 fnd_file.new_line(fnd_file.log,1);
833 fnd_file.put_line(fnd_file.log,fnd_message.get);
834 END IF;
835
836 FETCH cur_per_grp INTO per_grp_rec;
837 EXIT WHEN cur_per_grp%NOTFOUND;
838 END LOOP;
839 CLOSE cur_per_grp;
840
841 END IF; -- end of IF (cur_per_grp%NOTFOUND)
842
843 END IF;
844
845 fnd_file.new_line(fnd_file.log,1);
846 fnd_file.put_line(fnd_file.log,'-------------------------------------------------------');
847
848
849 COMMIT;
850
851 EXCEPTION
852 WHEN param_exception THEN
853 retcode:=2;
854 fnd_message.set_name('IGF','IGF_AW_PARAM_ERR');
855 igs_ge_msg_stack.add;
856 errbuf := fnd_message.get;
857
858 WHEN app_exception.record_lock_exception THEN
859 ROLLBACK;
860 retcode:=2;
861 fnd_message.set_name('IGF','IGF_GE_LOCK_ERROR');
862 igs_ge_msg_stack.add;
863 errbuf := fnd_message.get;
864
865 WHEN OTHERS THEN
866 ROLLBACK;
867 retcode:=2;
868 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
869 igs_ge_msg_stack.add;
870 errbuf := fnd_message.get || SQLERRM;
871 END run;
872
873 END igf_aw_manage_awd;