[Home] [Help]
PACKAGE BODY: APPS.IGF_AP_ISIR_GEN_PKG
Source
1 PACKAGE BODY igf_ap_isir_gen_pkg AS
2 /* $Header: IGFAP47B.pls 120.5 2006/02/23 01:38:51 rajagupt noship $ */
3 ------------------------------------------------------------------
4 --Created by : ugummall, Oracle India
5 --Date created: 04-AUG-2004
6 --
7 --Purpose: Generic routines used in self-service pages and ISIR Import Process.
8 --
9 --
10 --Known limitations/enhancements and/or remarks:
11 --
12 --Change History:
13 --Who When What
14 -- cdcruz 16-Dec-2004 Bug 4068284 Function chk_pell_orig cursor if condition corrected
15 --brajendr 02-Nov-2004 Bug 3031287 FA152 and FA137 COA Updates and Repackaging
16 -- Added procedure upd_ant_data_awd_prc_status for Updating Anticipated Data and Award Prcoess status
17 -------------------------------------------------------------------
18
19 PROCEDURE attach_isir( cp_si_id IN igf_ap_isir_ints_all.si_id%TYPE ,
20 cp_batch_year IN igf_ap_isir_ints_all.batch_year_num%TYPE,
21 cp_message_out OUT NOCOPY VARCHAR2)
22 AS
23 /*
24 || Created By : rasahoo
25 || Created On : 13-OCT-2004
26 || Purpose :Attch an ISIR to an existing person
27 || Known limitations, enhancements or remarks :
28 || Change History :
29 || Who When What
30 || (reverse chronological order - newest change first)
31 */
32 errbuf VARCHAR2(2000);
33 retcode NUMBER;
34
35 BEGIN
36 igf_ap_matching_process_pkg.main(errbuf => errbuf, --OUT NOCOPY VARCHAR2,
37 retcode => retcode, -- OUT NOCOPY NUMBER,
38 p_force_add => NULL, --IN VARCHAR2,
39 p_create_inquiry => NULL, --IN VARCHAR2,
40 p_adm_source_type => NULL, --IN VARCHAR2,
41 p_batch_year => cp_batch_year, --IN VARCHAR2,
42 p_match_code => NULL, --IN VARCHAR2,
43 p_del_int => NULL, --IN VARCHAR2,
44 p_parent_req_id => NULL, --IN VARCHAR2, -- when called as sub request
45 p_sub_req_num => NULL, --IN VARCHAR2, -- when called as sub request
46 p_si_id => cp_si_id, --IN VARCHAR2 -- when called for single si id
47 p_upd_ant_val => 'Y'
48 );
49
50 EXCEPTION
51 WHEN OTHERS THEN
52 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
53 fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.attach_isir');
54 cp_message_out:= fnd_message.get;
55 END attach_isir;
56
57 FUNCTION can_unlock_isir(
58 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
59 p_user_id IN fnd_user.user_id%TYPE
60 )
61 RETURN VARCHAR2 IS
62 /*
63 || Created By : ugummall
64 || Created On : 04-AUG-2004
65 || Purpose : Returns 'Y' if the user can unlock the ISIR
66 || otherwise returns 'N'
67 || Known limitations, enhancements or remarks :
68 || Change History :
69 || Who When What
70 || (reverse chronological order - newest change first)
71 */
72
73 -- Cursor to check wether the user locked the specified ISIR or not.
74 CURSOR cur_is_user_locked_isir( cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
75 cp_user_id fnd_user.user_id%TYPE
76 ) IS
77 SELECT 'X'
78 FROM IGF_AP_FA_BASE_REC_ALL fabase
79 WHERE fabase.base_id = cp_base_id
80 AND (fabase.isir_locked_by IS NULL OR fabase.isir_locked_by = cp_user_id);
81
82 -- Cursor to check wether the user is system administrator or not.
83 CURSOR cur_is_user_sysadmin( cp_user_id fnd_user.user_id%TYPE ) IS
84 SELECT 'X'
85 FROM FND_USER_RESP_GROUPS userresp,
86 FND_RESPONSIBILITY resp
87 WHERE resp.responsibility_id = userresp.responsibility_id
88 AND resp.responsibility_key = 'SYSTEM_ADMINISTRATOR'
89 AND userresp.user_id = cp_user_id;
90
91 lv_dummy VARCHAR2(1);
92
93 BEGIN
94
95 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
96 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.can_unlock_isir.debug','starting can_unlock_isir with p_base_id : ' || p_base_id ||' p_user_id : '|| p_user_id);
97 END IF;
98
99 -- if base id or user id is null then return 'N'
100 IF (p_base_id IS NULL OR p_user_id IS NULL) THEN
101 RETURN 'N';
102 END IF;
103
104 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
105 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.can_unlock_isir.debug','Before opening cursor cur_is_user_locked_isir ');
106 END IF;
107
108 -- check if the ISIR is locked by the user
109 lv_dummy := null;
110 OPEN cur_is_user_locked_isir(p_base_id, p_user_id);
111 FETCH cur_is_user_locked_isir INTO lv_dummy;
112 CLOSE cur_is_user_locked_isir;
113
114 IF (lv_dummy = 'X') THEN
115 RETURN 'Y';
116 END IF;
117
118 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
119 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.can_unlock_isir.debug','Before opening cursor cur_is_user_sysadmin ');
120 END IF;
121
122 -- ISIR is not locked by user. Check if user is system administrator
123 lv_dummy := null;
124 OPEN cur_is_user_sysadmin(p_user_id);
125 FETCH cur_is_user_sysadmin INTO lv_dummy;
126 CLOSE cur_is_user_sysadmin;
127
128 IF (lv_dummy = 'X') THEN
129 RETURN 'Y';
130 END IF;
131
132 -- Neither user locked the ISIR, nor is system administrator.
133 -- That means user has no privileges.
134 RETURN 'N';
135
136 EXCEPTION
137 WHEN OTHERS THEN
138 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
139 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.can_unlock_isir.exception','The exception is : ' || SQLERRM );
140 END IF;
141 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
142 fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.can_unlock_isir');
143 fnd_file.put_line(fnd_file.log,fnd_message.get);
144 igs_ge_msg_stack.add;
145 RETURN 'N';
146
147 END can_unlock_isir;
148
149
150 FUNCTION update_lock_status (
151 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
152 p_user_id IN fnd_user.user_id%TYPE
153 )
154 RETURN VARCHAR2 IS
155 /*
156 || Created By : ugummall
157 || Created On : 04-AUG-2004
158 || Purpose : To lock/unlock the ISIR. Returns 'Y' upon successfull update,
159 || returns 'N' otherwise
160 || Known limitations, enhancements or remarks :
161 || Change History :
162 || Who When What
163 || (reverse chronological order - newest change first)
164 */
165
166 -- Cursor to get fa base record
167 CURSOR cur_fa_base( cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE ) IS
168 SELECT fabase.*
169 FROM IGF_AP_FA_BASE_REC fabase
170 WHERE fabase.base_id = cp_base_id;
171
172 rec_fa_base cur_fa_base%ROWTYPE;
173
174
175 BEGIN
176
177 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
178 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.update_lock_status.debug','starting update_lock_status with p_base_id : ' || p_base_id ||' p_user_id : '|| p_user_id);
179 END IF;
180
181 -- if p_base_id is null, return 'N'
182 IF (p_base_id IS NULL) THEN
183 RETURN 'N';
184 END IF;
185
186 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
187 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.update_lock_status.debug','Before opening cursor cur_fa_base ');
188 END IF;
189
190 OPEN cur_fa_base(p_base_id);
191 FETCH cur_fa_base INTO rec_fa_base;
192 IF cur_fa_base%NOTFOUND THEN
193 CLOSE cur_fa_base;
194 RETURN 'N';
195 END IF;
196 CLOSE cur_fa_base;
197
198 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
199 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.update_lock_status.debug','Calling igf_ap_fa_base_rec_pkg.update_row for baseid : '||rec_fa_base.base_id);
200 END IF;
201
202 -- if p_user_id is null, unlock the ISIR., update fa base record with ISIR lock status (ISIR_LOCKED_BY ) to null
203 -- If NOT NULL, update fa base record with ISIR lock status (ISIR_LOCKED_BY) to p_user_id
204 igf_ap_fa_base_rec_pkg.update_row(
205 x_Mode => 'R' ,
206 x_rowid => rec_fa_base.row_id ,
207 x_base_id => rec_fa_base.base_id ,
208 x_ci_cal_type => rec_fa_base.ci_cal_type ,
209 x_person_id => rec_fa_base.person_id ,
210 x_ci_sequence_number => rec_fa_base.ci_sequence_number ,
211 x_org_id => rec_fa_base.org_id ,
212 x_coa_pending => rec_fa_base.coa_pending ,
213 x_verification_process_run => rec_fa_base.verification_process_run ,
214 x_inst_verif_status_date => rec_fa_base.inst_verif_status_date ,
215 x_manual_verif_flag => rec_fa_base.manual_verif_flag ,
216 x_fed_verif_status => rec_fa_base.fed_verif_status ,
217 x_fed_verif_status_date => rec_fa_base.fed_verif_status_date ,
218 x_inst_verif_status => rec_fa_base.inst_verif_status ,
219 x_nslds_eligible => rec_fa_base.nslds_eligible ,
220 x_ede_correction_batch_id => rec_fa_base.ede_correction_batch_id ,
221 x_fa_process_status_date => rec_fa_base.fa_process_status_date ,
222 x_isir_corr_status => rec_fa_base.isir_corr_status ,
223 x_isir_corr_status_date => rec_fa_base.isir_corr_status_date ,
224 x_isir_status => rec_fa_base.isir_status ,
225 x_isir_status_date => rec_fa_base.isir_status_date,
226 x_coa_code_f => rec_fa_base.coa_code_f ,
227 x_coa_code_i => rec_fa_base.coa_code_i ,
228 x_coa_f => rec_fa_base.coa_f ,
229 x_coa_i => rec_fa_base.coa_i ,
230 x_disbursement_hold => rec_fa_base.disbursement_hold ,
231 x_fa_process_status => rec_fa_base.fa_process_status ,
232 x_notification_status => rec_fa_base.notification_status ,
233 x_notification_status_date => rec_fa_base.notification_status_date ,
234 x_packaging_status => rec_fa_base.packaging_status ,
235 x_packaging_status_date => rec_fa_base.packaging_status_date ,
236 x_total_package_accepted => rec_fa_base.total_package_accepted ,
240 x_admsegment_2 => rec_fa_base.admsegment_2 ,
237 x_total_package_offered => rec_fa_base.total_package_offered ,
238 x_admstruct_id => rec_fa_base.admstruct_id ,
239 x_admsegment_1 => rec_fa_base.admsegment_1 ,
241 x_admsegment_3 => rec_fa_base.admsegment_3 ,
242 x_admsegment_4 => rec_fa_base.admsegment_4 ,
243 x_admsegment_5 => rec_fa_base.admsegment_5 ,
244 x_admsegment_6 => rec_fa_base.admsegment_6 ,
245 x_admsegment_7 => rec_fa_base.admsegment_7 ,
246 x_admsegment_8 => rec_fa_base.admsegment_8 ,
247 x_admsegment_9 => rec_fa_base.admsegment_9 ,
248 x_admsegment_10 => rec_fa_base.admsegment_10 ,
249 x_admsegment_11 => rec_fa_base.admsegment_11 ,
250 x_admsegment_12 => rec_fa_base.admsegment_12 ,
251 x_admsegment_13 => rec_fa_base.admsegment_13 ,
252 x_admsegment_14 => rec_fa_base.admsegment_14 ,
253 x_admsegment_15 => rec_fa_base.admsegment_15 ,
254 x_admsegment_16 => rec_fa_base.admsegment_16 ,
255 x_admsegment_17 => rec_fa_base.admsegment_17 ,
256 x_admsegment_18 => rec_fa_base.admsegment_18 ,
257 x_admsegment_19 => rec_fa_base.admsegment_19 ,
258 x_admsegment_20 => rec_fa_base.admsegment_20 ,
259 x_packstruct_id => rec_fa_base.packstruct_id ,
260 x_packsegment_1 => rec_fa_base.packsegment_1 ,
261 x_packsegment_2 => rec_fa_base.packsegment_2 ,
262 x_packsegment_3 => rec_fa_base.packsegment_3 ,
263 x_packsegment_4 => rec_fa_base.packsegment_4 ,
264 x_packsegment_5 => rec_fa_base.packsegment_5 ,
265 x_packsegment_6 => rec_fa_base.packsegment_6 ,
266 x_packsegment_7 => rec_fa_base.packsegment_7 ,
267 x_packsegment_8 => rec_fa_base.packsegment_8 ,
268 x_packsegment_9 => rec_fa_base.packsegment_9 ,
269 x_packsegment_10 => rec_fa_base.packsegment_10 ,
270 x_packsegment_11 => rec_fa_base.packsegment_11 ,
271 x_packsegment_12 => rec_fa_base.packsegment_12 ,
272 x_packsegment_13 => rec_fa_base.packsegment_13 ,
273 x_packsegment_14 => rec_fa_base.packsegment_14 ,
274 x_packsegment_15 => rec_fa_base.packsegment_15 ,
275 x_packsegment_16 => rec_fa_base.packsegment_16 ,
276 x_packsegment_17 => rec_fa_base.packsegment_17 ,
277 x_packsegment_18 => rec_fa_base.packsegment_18 ,
278 x_packsegment_19 => rec_fa_base.packsegment_19 ,
279 x_packsegment_20 => rec_fa_base.packsegment_20 ,
280 x_miscstruct_id => rec_fa_base.miscstruct_id ,
281 x_miscsegment_1 => rec_fa_base.miscsegment_1 ,
282 x_miscsegment_2 => rec_fa_base.miscsegment_2 ,
283 x_miscsegment_3 => rec_fa_base.miscsegment_3 ,
284 x_miscsegment_4 => rec_fa_base.miscsegment_4 ,
285 x_miscsegment_5 => rec_fa_base.miscsegment_5 ,
286 x_miscsegment_6 => rec_fa_base.miscsegment_6 ,
287 x_miscsegment_7 => rec_fa_base.miscsegment_7 ,
288 x_miscsegment_8 => rec_fa_base.miscsegment_8 ,
289 x_miscsegment_9 => rec_fa_base.miscsegment_9 ,
290 x_miscsegment_10 => rec_fa_base.miscsegment_10 ,
291 x_miscsegment_11 => rec_fa_base.miscsegment_11 ,
292 x_miscsegment_12 => rec_fa_base.miscsegment_12 ,
293 x_miscsegment_13 => rec_fa_base.miscsegment_13 ,
294 x_miscsegment_14 => rec_fa_base.miscsegment_14 ,
295 x_miscsegment_15 => rec_fa_base.miscsegment_15 ,
296 x_miscsegment_16 => rec_fa_base.miscsegment_16 ,
297 x_miscsegment_17 => rec_fa_base.miscsegment_17 ,
301 x_prof_judgement_flg => rec_fa_base.prof_judgement_flg ,
298 x_miscsegment_18 => rec_fa_base.miscsegment_18 ,
299 x_miscsegment_19 => rec_fa_base.miscsegment_19 ,
300 x_miscsegment_20 => rec_fa_base.miscsegment_20 ,
302 x_nslds_data_override_flg => rec_fa_base.nslds_data_override_flg ,
303 x_target_group => rec_fa_base.target_group ,
304 x_coa_fixed => rec_fa_base.coa_fixed ,
305 x_coa_pell => rec_fa_base.coa_pell ,
306 x_profile_status => rec_fa_base.profile_status ,
307 x_profile_status_date => rec_fa_base.profile_status_date ,
308 x_profile_fc => rec_fa_base.profile_fc ,
309 x_tolerance_amount => rec_fa_base.tolerance_amount ,
310 x_manual_disb_hold => rec_fa_base.manual_disb_hold ,
311 x_pell_alt_expense => rec_fa_base.pell_alt_expense,
312 x_assoc_org_num => rec_fa_base.assoc_org_num,
313 x_award_fmly_contribution_type => rec_fa_base.award_fmly_contribution_type,
314 x_isir_locked_by => p_user_id,
315 x_adnl_unsub_loan_elig_flag => rec_fa_base.adnl_unsub_loan_elig_flag,
316 x_lock_awd_flag => rec_fa_base.lock_awd_flag,
317 x_lock_coa_flag => rec_fa_base.lock_coa_flag
318
319 );
320 RETURN 'Y';
321
322 END update_lock_status;
323
324
325 FUNCTION is_awarding_pymnt_isir_exists(
326 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE
327 )
328 RETURN VARCHAR2 IS
329 /*
330 || Created By : ugummall
331 || Created On : 04-AUG-2004
332 || Purpose : Returns 'Y' if student has any outstanding corrections with status 'BATCHED' or 'READY'
333 || otherwise returns 'N'
334 || Known limitations, enhancements or remarks :
335 || Change History :
336 || Who When What
337 || (reverse chronological order - newest change first)
338 */
339
340 -- Cursor to check wether the student has any outstanding corrections with status 'BATCHED' or 'READY'
341 CURSOR cur_isir( cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE ) IS
342 SELECT 'X'
343 FROM igf_ap_isir_matched_all
344 WHERE base_id = p_base_id
345 AND system_record_type IN ('ORIGINAL','CORRECTION')
346 AND (NVL(payment_isir,'N') = 'Y' OR NVL(active_isir,'N') = 'Y')
347 AND ROWNUM < 2;
348
349 lv_dummy VARCHAR2(1);
350
351 BEGIN
352
353 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
354 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.is_awarding_pymnt_isir_exists.debug','starting is_awarding_pymnt_isir_exists with p_base_id : ' || p_base_id );
355 END IF;
356
357 -- validate the input parameters
358 IF (p_base_id IS NULL) THEN
359 RETURN 'N';
360 END IF;
361
362 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
363 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.is_awarding_pymnt_isir_exists.debug','before opening cursor cur_isir ');
364 END IF;
365
366 -- check wether the student is having any outstanding corrections or not.
367 lv_dummy := NULL;
368 OPEN cur_isir(p_base_id);
369 FETCH cur_isir INTO lv_dummy;
370 CLOSE cur_isir;
371
372 IF (lv_dummy = 'X') THEN
373 RETURN 'Y';
374 END IF;
375
376 RETURN 'N';
377
378 EXCEPTION
379 WHEN OTHERS THEN
380 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
381 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.is_awarding_pymnt_isir_exists.exception','The exception is : ' || SQLERRM );
382 END IF;
383 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
384 fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.is_awarding_pymnt_isir_exists');
385 fnd_file.put_line(fnd_file.log,fnd_message.get);
386 igs_ge_msg_stack.add;
387 RETURN 'N';
388
389 END is_awarding_pymnt_isir_exists;
390
391
392 FUNCTION are_corrections_exists (
393 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE
394 )
395 RETURN VARCHAR2 IS
396 /*
397 || Created By : ugummall
398 || Created On : 04-AUG-2004
399 || Purpose : Returns 'Y' if student has any outstanding corrections with status 'BATCHED'
400 || otherwise returns 'N'
401 || Known limitations, enhancements or remarks :
402 || Change History :
403 || Who When What
404 || (reverse chronological order - newest change first)
405 */
406
407 -- Cursor to check wether the student has any outstanding corrections with status 'BATCHED'
408 CURSOR cur_corrections( cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE ) IS
409 SELECT 'X'
410 FROM IGF_AP_ISIR_CORR_ALL ic
411 WHERE ic.correction_status = 'BATCHED'
412 AND ic.isir_id IN ( SELECT im.isir_id
413 FROM IGF_AP_ISIR_MATCHED_ALL im
414 WHERE im.base_id = cp_base_id );
415
416 lv_dummy VARCHAR2(1);
417
418 BEGIN
419
420 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
424 -- validate the input parameters
421 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.are_corrections_exists.debug','starting are_corrections_exists with p_base_id : ' || p_base_id);
422 END IF;
423
425 IF (p_base_id IS NULL) THEN
426 RETURN 'N';
427 END IF;
428
429 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
430 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.are_corrections_exists.debug','Before opening cursor cur_corrections ');
431 END IF;
432
433 -- check wether the student is having any outstanding corrections or not.
434 lv_dummy := NULL;
435 OPEN cur_corrections(p_base_id);
436 FETCH cur_corrections INTO lv_dummy;
437 CLOSE cur_corrections;
438
439 IF (lv_dummy = 'X') THEN
440 RETURN 'Y';
441 END IF;
442
443 RETURN 'N';
444
445 EXCEPTION
446 WHEN OTHERS THEN
447 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
448 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.are_corrections_exists.exception','The exception is : ' || SQLERRM );
449 END IF;
450 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
451 fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.are_corrections_exists');
452 fnd_file.put_line(fnd_file.log,fnd_message.get);
453 igs_ge_msg_stack.add;
454 RETURN 'N';
455
456 END are_corrections_exists;
457
458
459 PROCEDURE delete_isir_validations(
460 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
461 p_isir_id IN igf_ap_isir_matched_all.isir_id%TYPE,
462 x_msg_count OUT NOCOPY NUMBER,
463 x_msg_data OUT NOCOPY VARCHAR2,
464 x_return_status OUT NOCOPY VARCHAR2
465 ) IS
466 /*
467 || Created By : ugummall
468 || Created On : 04-AUG-2004
469 || Purpose : Returns 'A' if student has Non-Simulated awards
470 || Returns 'C' if correction ISIR exists
471 || If no non-simulated awards, no correction ISIR then deletes ISIR
472 || and returns 'Y' upon successful deletion. Otherwise returns 'N'
473 || Known limitations, enhancements or remarks :
474 || Change History :
475 || Who When What
476 || (reverse chronological order - newest change first)
477 */
478
479 -- Cursor to check wether the student has any non-simulated awards.
480 CURSOR cur_stud_non_simulated_awards( cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE ) IS
481 SELECT 'X'
482 FROM IGF_AW_AWARD_ALL awards
483 WHERE awards.base_id = cp_base_id
484 AND awards.award_status <> 'SIMULATED';
485
486 -- Cursor to check if internal created correction ISIR exists for the student or not.
487 CURSOR cur_stud_correction_isir( cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE ) IS
488 SELECT 'X'
489 FROM IGF_AP_ISIR_MATCHED_ALL im
490 WHERE im.base_id = cp_base_id
491 AND im.system_record_type = 'CORRECTION';
492
493 lv_dummy VARCHAR2(1);
494 RETURN_EXP EXCEPTION;
495 l_err_msg VARCHAR2(2000);
496
497 BEGIN
498
499 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
500 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_isir_validations.debug','starting delete_isir_validations with p_base_id : ' || p_base_id ||' p_isir_id : '|| p_isir_id);
501 END IF;
502
503 SAVEPOINT IGFAP47_DELETE_ISIR_VAL;
504 FND_MSG_PUB.initialize;
505 x_return_status := FND_API.G_RET_STS_SUCCESS;
506
507 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
508 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_isir_validations.debug','Before opening cursor cur_stud_non_simulated_awards ');
509 END IF;
510
511 -- check if non-simulated awards exists for the student.
512 lv_dummy := null;
513 OPEN cur_stud_non_simulated_awards(p_base_id);
514 FETCH cur_stud_non_simulated_awards INTO lv_dummy;
515 CLOSE cur_stud_non_simulated_awards;
516
517 IF (lv_dummy = 'X') THEN
518 x_return_status := 'A';
519 RAISE RETURN_EXP;
520 END IF;
521
522 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
523 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_isir_validations.debug','Before opening cursor cur_stud_correction_isir ');
524 END IF;
525
526 -- check if internal created correction ISIR exists for the student.
527 lv_dummy := null;
528 OPEN cur_stud_correction_isir(p_base_id);
529 FETCH cur_stud_correction_isir INTO lv_dummy;
530 CLOSE cur_stud_correction_isir;
531
532 IF (lv_dummy = 'X') THEN
533 x_return_status := 'C';
534 RAISE RETURN_EXP;
535 END IF;
536
537 x_return_status := 'S';
538 fnd_msg_pub.count_and_get(
539 p_encoded => fnd_api.g_false,
540 p_count => x_msg_count,
541 p_data => x_msg_data
542 );
543
544 EXCEPTION
545 WHEN RETURN_EXP THEN
546 fnd_msg_pub.count_and_get(
547 p_encoded => fnd_api.g_false,
548 p_count => x_msg_count,
549 p_data => x_msg_data
550 );
551
552 WHEN OTHERS THEN
553
557 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.delete_isir.exception','The exception is : ' || SQLERRM );
554 ROLLBACK TO IGFAP47_DELETE_ISIR_VAL;
555
556 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
558 END IF;
559 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
560 fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.delete_isir');
561
562 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
563 l_err_msg := fnd_message.get||SQLERRM;
564 fnd_msg_pub.count_and_get(
565 p_encoded => fnd_api.g_false,
566 p_count => x_msg_count,
567 p_data => l_err_msg --x_msg_data
568 );
569 END delete_isir_validations;
570
571
572 PROCEDURE delete_isir(
573 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
574 p_isir_id IN igf_ap_isir_matched_all.isir_id%TYPE,
575 x_msg_count OUT NOCOPY NUMBER,
576 x_msg_data OUT NOCOPY VARCHAR2,
577 x_return_status OUT NOCOPY VARCHAR2
578 ) IS
579 /*
580 || Created By : ugummall
581 || Created On : 04-AUG-2004
582 || Purpose : Returns 'A' if student has Non-Simulated awards
583 || Returns 'C' if correction ISIR exists
584 || If no non-simulated awards, no correction ISIR then deletes ISIR
585 || and returns 'Y' upon successful deletion. Otherwise returns 'N'
586 || Known limitations, enhancements or remarks :
587 || Change History :
588 || Who When What
589 || (reverse chronological order - newest change first)
590 */
591
592 -- Cursor to get rowid of the ISIR to be deleted.
593 CURSOR cur_chk_isir_in_ints(
594 cp_batch_year igf_ap_isir_matched_all.batch_year%TYPE,
595 cp_transaction_num igf_ap_isir_matched_all.transaction_num%TYPE,
596 cp_original_ssn igf_ap_isir_matched_all.original_ssn%TYPE,
597 cp_orig_name_id igf_ap_isir_matched_all.orig_name_id%TYPE
598 ) IS
599 SELECT ROWID row_id, si_id
600 FROM igf_ap_isir_ints i
601 WHERE batch_year_num = cp_batch_year
602 AND transaction_num_txt = cp_transaction_num
603 AND original_ssn_txt = cp_original_ssn
604 AND orig_name_id_txt = cp_orig_name_id;
605
606 rec_chk_isir_in_ints cur_chk_isir_in_ints%ROWTYPE;
607
608 -- Cursor to get rowid of the ISIR to be deleted.
609 CURSOR cur_get_rowid_nslds( cp_isir_id igf_ap_isir_matched_all.isir_id%TYPE ) IS
610 SELECT ROWID row_id
611 FROM igf_ap_nslds_data_all n
612 WHERE n.isir_id = cp_isir_id;
613
614 rec_get_rowid_nslds cur_get_rowid_nslds%ROWTYPE;
615
616 -- Cursor to get rowid of the ISIR to be deleted.
617 CURSOR cur_get_rowid_isir( cp_isir_id igf_ap_isir_matched_all.isir_id%TYPE ) IS
618 SELECT ROWID row_id, batch_year, transaction_num, original_ssn, orig_name_id, base_id, system_record_type
619 FROM igf_ap_isir_matched_all im
620 WHERE im.isir_id = cp_isir_id;
621
622 rec_get_rowid_isir cur_get_rowid_isir%ROWTYPE;
623
624 CURSOR cur_isir_int ( cp_isir_id igf_ap_isir_matched_all.isir_id%TYPE ) IS
625 SELECT i.batch_year, i.transaction_num, i.current_ssn, i.ssn_name_change, i.original_ssn, i.orig_name_id, i.last_name, i.first_name, i.middle_initial,
626 i.perm_mail_add, i.perm_city, i.perm_state, i.perm_zip_code, i.date_of_birth, i.phone_number, i.driver_license_number, i.driver_license_state,
627 i.citizenship_status, i.alien_reg_number, i.s_marital_status, i.s_marital_status_date, i.summ_enrl_status, i.fall_enrl_status, i.winter_enrl_status,
628 i.spring_enrl_status, i.summ2_enrl_status, i.fathers_highest_edu_level, i.mothers_highest_edu_level, i.s_state_legal_residence, i.legal_residence_before_date,
629 i.s_legal_resd_date, i.ss_r_u_male, i.selective_service_reg, i.degree_certification, i.grade_level_in_college, i.high_school_diploma_ged,
630 i.first_bachelor_deg_by_date, i.interest_in_loan, i.interest_in_stud_employment, i.drug_offence_conviction, i.s_tax_return_status, i.s_type_tax_return,
631 i.s_elig_1040ez, i.s_adjusted_gross_income, i.s_fed_taxes_paid, i.s_exemptions, i.s_income_from_work, i.spouse_income_from_work, i.s_toa_amt_from_wsa,
632 i.s_toa_amt_from_wsb, i.s_toa_amt_from_wsc, i.s_investment_networth, i.s_busi_farm_networth, i.s_cash_savings, i.va_months, i.va_amount, i.stud_dob_before_date,
633 i.deg_beyond_bachelor, i.s_married, i.s_have_children, i.legal_dependents, i.orphan_ward_of_court, i.s_veteran, i.p_marital_status, i.father_ssn, i.f_last_name,
634 i.mother_ssn, i.m_last_name, i.p_num_family_member, i.p_num_in_college, i.p_state_legal_residence, i.p_state_legal_res_before_dt, i.p_legal_res_date,
635 i.age_older_parent, i.p_tax_return_status, i.p_type_tax_return, i.p_elig_1040aez, i.p_adjusted_gross_income, i.p_taxes_paid, i.p_exemptions, i.f_income_work,
636 i.m_income_work, i.p_income_wsa, i.p_income_wsb, i.p_income_wsc, i.p_investment_networth, i.p_business_networth, i.p_cash_saving, i.s_num_family_members,
637 i.s_num_in_college, i.first_college, i.first_house_plan, i.second_college, i.second_house_plan, i.third_college, i.third_house_plan, i.fourth_college,
638 i.fourth_house_plan, i.fifth_college, i.fifth_house_plan, i.sixth_college, i.sixth_house_plan, i.date_app_completed, i.signed_by, i.preparer_ssn,
639 i.preparer_emp_id_number, i.preparer_sign, i.transaction_receipt_date, i.dependency_override_ind, i.faa_fedral_schl_code, i.faa_adjustment, i.input_record_type,
640 i.serial_number, i.batch_number, i.early_analysis_flag, i.app_entry_source_code, i.eti_destination_code, i.reject_override_b, i.reject_override_n,
644 i.efc_change_flag, i.sarc_flag, i.simplified_need_test, i.reject_reason_codes, i.select_service_match_flag, i.select_service_reg_flag, i.ins_match_flag,
641 i.reject_override_w, i.assum_override_1, i.assum_override_2, i.assum_override_3, i.assum_override_4, i.assum_override_5, i.assum_override_6,
642 i.dependency_status, i.s_email_address, i.nslds_reason_code, i.app_receipt_date, i.processed_rec_type, i.hist_correction_for_tran_id,
643 i.system_generated_indicator, i.dup_request_indicator, i.source_of_correction, i.p_cal_tax_status, i.s_cal_tax_status, i.graduate_flag, i.auto_zero_efc,
645 i.ins_verification_number, i.sec_ins_match_flag, i.sec_ins_ver_number, i.ssn_match_flag, i.ssa_citizenship_flag, i.ssn_date_of_death, i.nslds_match_flag,
646 i.va_match_flag, i.prisoner_match, i.verification_flag, i.subsequent_app_flag, i.app_source_site_code, i.tran_source_site_code, i.drn, i.tran_process_date,
647 i.computer_batch_number, i.correction_flags, i.highlight_flags, i.paid_efc, i.primary_efc, i.secondary_efc, i.fed_pell_grant_efc_type, i.primary_efc_type,
648 i.sec_efc_type, i.primary_alternate_month_1, i.primary_alternate_month_2, i.primary_alternate_month_3, i.primary_alternate_month_4, i.primary_alternate_month_5,
649 i.primary_alternate_month_6, i.primary_alternate_month_7, i.primary_alternate_month_8, i.primary_alternate_month_10, i.primary_alternate_month_11,
650 i.primary_alternate_month_12, i.sec_alternate_month_1, i.sec_alternate_month_2, i.sec_alternate_month_3, i.sec_alternate_month_4, i.sec_alternate_month_5,
651 i.sec_alternate_month_6, i.sec_alternate_month_7, i.sec_alternate_month_8, i.sec_alternate_month_10, i.sec_alternate_month_11, i.sec_alternate_month_12,
652 i.total_income, i.allow_total_income, i.state_tax_allow, i.employment_allow, i.income_protection_allow, i.available_income, i.contribution_from_ai,
653 i.discretionary_networth, i.efc_networth, i.asset_protect_allow, i.parents_cont_from_assets, i.adjusted_available_income, i.total_student_contribution,
654 i.total_parent_contribution, i.parents_contribution, i.student_total_income, i.sati, i.sic, i.sdnw, i.sca, i.fti, i.secti, i.secati, i.secstx, i.secea,
655 i.secipa, i.secai, i.seccai, i.secdnw, i.secnw, i.secapa, i.secpca, i.secaai, i.sectsc, i.sectpc, i.secpc, i.secsti, i.secsic, i.secsati, i.secsdnw, i.secsca,
656 i.secfti, i.a_citizenship, i.a_student_marital_status, i.a_student_agi, i.a_s_us_tax_paid, i.a_s_income_work, i.a_spouse_income_work, i.a_s_total_wsc,
657 i.a_date_of_birth, i.a_student_married, i.a_have_children, i.a_s_have_dependents, i.a_va_status, i.a_s_num_in_family, i.a_s_num_in_college,
658 i.a_p_marital_status, i.a_father_ssn, i.a_mother_ssn, i.a_parents_num_family, i.a_parents_num_college, i.a_parents_agi, i.a_p_us_tax_paid, i.a_f_work_income,
659 i.a_m_work_income, i.a_p_total_wsc, i.comment_codes, i.sar_ack_comm_code, i.pell_grant_elig_flag, i.reprocess_reason_code, i.duplicate_date,
660 i.isir_transaction_type, i.fedral_schl_code_indicator, i.multi_school_code_flags, i.dup_ssn_indicator, i.verif_track_flag, i.fafsa_data_verify_flags,
661 i.reject_override_a, i.reject_override_c, i.parent_marital_status_date, i.father_first_name_initial_txt, i.father_step_father_birth_date,
662 i.mother_first_name_initial_txt, i.mother_step_mother_birth_date, i.parents_email_address_txt, i.address_change_type, i.cps_pushed_isir_flag,
663 i.electronic_transaction_type, i.sar_c_change_type, i.father_ssn_match_type, i.mother_ssn_match_type, i.reject_override_g_flag, i.dhs_verification_num_txt,
664 i.data_file_name_txt, n.nslds_transaction_num, n.nslds_database_results_f, n.nslds_f, n.nslds_pell_overpay_f, n.nslds_pell_overpay_contact,
665 n.nslds_seog_overpay_f, n.nslds_seog_overpay_contact, n.nslds_perkins_overpay_f, n.nslds_perkins_overpay_cntct, n.nslds_defaulted_loan_f,
666 n.nslds_dischged_loan_chng_f, n.nslds_satis_repay_f, n.nslds_act_bankruptcy_f, n.nslds_agg_subsz_out_prin_bal, n.nslds_agg_unsbz_out_prin_bal,
667 n.nslds_agg_comb_out_prin_bal, n.nslds_agg_cons_out_prin_bal, n.nslds_agg_subsz_pend_dismt, n.nslds_agg_unsbz_pend_dismt, n.nslds_agg_comb_pend_dismt,
668 n.nslds_agg_subsz_total, n.nslds_agg_unsbz_total, n.nslds_agg_comb_total, n.nslds_agg_consd_total, n.nslds_perkins_out_bal, n.nslds_perkins_cur_yr_dismnt,
669 n.nslds_default_loan_chng_f, n.nslds_discharged_loan_f,
670 n.nslds_satis_repay_chng_f, n.nslds_act_bnkrupt_chng_f, n.nslds_overpay_chng_f, n.nslds_agg_loan_chng_f, n.nslds_perkins_loan_chng_f, n.nslds_pell_paymnt_chng_f,
671 n.nslds_addtnl_pell_f, n.nslds_addtnl_loan_f, n.direct_loan_mas_prom_nt_f, n.nslds_pell_seq_num_1, n.nslds_pell_verify_f_1, n.nslds_pell_efc_1,
672 n.nslds_pell_school_code_1, n.nslds_pell_transcn_num_1, n.nslds_pell_last_updt_dt_1, n.nslds_pell_scheduled_amt_1, n.nslds_pell_amt_paid_todt_1,
673 n.nslds_pell_remng_amt_1, n.nslds_pell_pc_schd_awd_us_1, n.nslds_pell_award_amt_1, n.nslds_pell_seq_num_2, n.nslds_pell_verify_f_2, n.nslds_pell_efc_2,
674 n.nslds_pell_school_code_2, n.nslds_pell_transcn_num_2, n.nslds_pell_last_updt_dt_2, n.nslds_pell_scheduled_amt_2, n.nslds_pell_amt_paid_todt_2,
675 n.nslds_pell_remng_amt_2, n.nslds_pell_pc_schd_awd_us_2, n.nslds_pell_award_amt_2, n.nslds_pell_seq_num_3, n.nslds_pell_verify_f_3, n.nslds_pell_efc_3,
676 n.nslds_pell_school_code_3, n.nslds_pell_transcn_num_3, n.nslds_pell_last_updt_dt_3, n.nslds_pell_scheduled_amt_3, n.nslds_pell_amt_paid_todt_3,
677 n.nslds_pell_remng_amt_3, n.nslds_pell_pc_schd_awd_us_3, n.nslds_pell_award_amt_3, n.nslds_loan_seq_num_1, n.nslds_loan_type_code_1, n.nslds_loan_chng_f_1,
678 n.nslds_loan_prog_code_1, n.nslds_loan_net_amnt_1, n.nslds_loan_cur_st_code_1, n.nslds_loan_cur_st_date_1, n.nslds_loan_agg_pr_bal_1,
679 n.nslds_loan_out_pr_bal_dt_1, n.nslds_loan_begin_dt_1, n.nslds_loan_end_dt_1, n.nslds_loan_ga_code_1, n.nslds_loan_cont_type_1, n.nslds_loan_schol_code_1,
680 n.nslds_loan_cont_code_1, n.nslds_loan_grade_lvl_1, n.nslds_loan_xtr_unsbz_ln_f_1, n.nslds_loan_capital_int_f_1, n.nslds_loan_seq_num_2,
681 n.nslds_loan_type_code_2, n.nslds_loan_chng_f_2, n.nslds_loan_prog_code_2, n.nslds_loan_net_amnt_2, n.nslds_loan_cur_st_code_2, n.nslds_loan_cur_st_date_2,
682 n.nslds_loan_agg_pr_bal_2, n.nslds_loan_out_pr_bal_dt_2, n.nslds_loan_begin_dt_2, n.nslds_loan_end_dt_2, n.nslds_loan_ga_code_2, n.nslds_loan_cont_type_2,
686 n.nslds_loan_cont_type_3, n.nslds_loan_schol_code_3, n.nslds_loan_cont_code_3, n.nslds_loan_grade_lvl_3, n.nslds_loan_xtr_unsbz_ln_f_3,
683 n.nslds_loan_schol_code_2, n.nslds_loan_cont_code_2, n.nslds_loan_grade_lvl_2, n.nslds_loan_xtr_unsbz_ln_f_2, n.nslds_loan_capital_int_f_2,
684 n.nslds_loan_seq_num_3, n.nslds_loan_type_code_3, n.nslds_loan_chng_f_3, n.nslds_loan_prog_code_3, n.nslds_loan_net_amnt_3, n.nslds_loan_cur_st_code_3,
685 n.nslds_loan_cur_st_date_3, n.nslds_loan_agg_pr_bal_3, n.nslds_loan_out_pr_bal_dt_3, n.nslds_loan_begin_dt_3, n.nslds_loan_end_dt_3, n.nslds_loan_ga_code_3,
687 n.nslds_loan_capital_int_f_3, n.nslds_loan_seq_num_4, n.nslds_loan_type_code_4, n.nslds_loan_chng_f_4, n.nslds_loan_prog_code_4, n.nslds_loan_net_amnt_4,
688 n.nslds_loan_cur_st_code_4, n.nslds_loan_cur_st_date_4, n.nslds_loan_agg_pr_bal_4, n.nslds_loan_out_pr_bal_dt_4, n.nslds_loan_begin_dt_4,
689 n.nslds_loan_end_dt_4, n.nslds_loan_ga_code_4, n.nslds_loan_cont_type_4, n.nslds_loan_schol_code_4, n.nslds_loan_cont_code_4, n.nslds_loan_grade_lvl_4,
690 n.nslds_loan_xtr_unsbz_ln_f_4, n.nslds_loan_capital_int_f_4, n.nslds_loan_seq_num_5, n.nslds_loan_type_code_5, n.nslds_loan_chng_f_5, n.nslds_loan_prog_code_5,
691 n.nslds_loan_net_amnt_5, n.nslds_loan_cur_st_code_5, n.nslds_loan_cur_st_date_5, n.nslds_loan_agg_pr_bal_5, n.nslds_loan_out_pr_bal_dt_5,
692 n.nslds_loan_begin_dt_5, n.nslds_loan_end_dt_5, n.nslds_loan_ga_code_5, n.nslds_loan_cont_type_5, n.nslds_loan_schol_code_5, n.nslds_loan_cont_code_5,
693 n.nslds_loan_grade_lvl_5, n.nslds_loan_xtr_unsbz_ln_f_5, n.nslds_loan_capital_int_f_5, n.nslds_loan_seq_num_6, n.nslds_loan_type_code_6, n.nslds_loan_chng_f_6,
694 n.nslds_loan_prog_code_6, n.nslds_loan_net_amnt_6, n.nslds_loan_cur_st_code_6, n.nslds_loan_cur_st_date_6, n.nslds_loan_agg_pr_bal_6,
695 n.nslds_loan_out_pr_bal_dt_6, n.nslds_loan_begin_dt_6, n.nslds_loan_end_dt_6, n.nslds_loan_ga_code_6, n.nslds_loan_cont_type_6, n.nslds_loan_schol_code_6,
696 n.nslds_loan_cont_code_6, n.nslds_loan_grade_lvl_6, n.nslds_loan_xtr_unsbz_ln_f_6, n.nslds_loan_capital_int_f_6, n.nslds_loan_last_d_amt_1,
697 n.nslds_loan_last_d_date_1, n.nslds_loan_last_d_amt_2, n.nslds_loan_last_d_date_2, n.nslds_loan_last_d_amt_3, n.nslds_loan_last_d_date_3,
698 n.nslds_loan_last_d_amt_4, n.nslds_loan_last_d_date_4, n.nslds_loan_last_d_amt_5, n.nslds_loan_last_d_date_5, n.nslds_loan_last_d_amt_6,
699 n.nslds_loan_last_d_date_6, n.dlp_master_prom_note_flag, n.subsidized_loan_limit_type, n.combined_loan_limit_type,
700 i.system_record_type
701 FROM igf_ap_isir_matched_all i, igf_ap_nslds_data_all n
702 WHERE i.isir_id = n.isir_id(+)
703 AND i.isir_id = cp_isir_id;
704
705 cur_isir_int_rec cur_isir_int%ROWTYPE;
706 CURSOR get_isir(cp_base_id NUMBER)
707 IS
708 SELECT 'X'
709 FROM IGF_AP_ISIR_MATCHED_ALL
710 WHERE base_id = cp_base_id;
711
712 l_isir VARCHAR2(1);
713
714 CURSOR todo_items_for_isir_cur(cp_base_id NUMBER) IS
715 SELECT ii.rowid,
716 ii.*
717 FROM igf_ap_td_item_mst im, igf_ap_td_item_inst ii
718 WHERE ii.base_id = cp_base_id
719 AND im.todo_number = ii.item_sequence_number
720 AND im.system_todo_type_code = 'ISIR'; -- for ISIR type only
721
722 -- Get
723 CURSOR max_seq IS
724 SELECT max(si_id) max_si_id
725 FROM igf_ap_isir_ints_all;
726
727
728 todo_items_for_isir_rec todo_items_for_isir_cur%ROWTYPE;
729
730 max_seq_rec max_seq%ROWTYPE;
731
732 lv_dummy VARCHAR2(1);
733 l_err_msg VARCHAR2(2000);
734 l_isir_data_from_mtch_tbl BOOLEAN;
735 RETURN_EXP EXCEPTION;
736
737 BEGIN
738
739 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
740 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_isir.debug','starting delete_isir with p_base_id : ' || p_base_id ||' p_isir_id : '|| p_isir_id);
741 END IF;
742
743
744 SAVEPOINT IGFAP47_DELETE_ISIR;
745 FND_MSG_PUB.initialize;
746 x_return_status := FND_API.G_RET_STS_SUCCESS;
747 l_isir_data_from_mtch_tbl := FALSE;
748
749 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
750 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_isir.debug','Before opening cursor cur_get_rowid_isir ');
751 END IF;
752
753 -- Check whether the ISIR is still present in the ISIR Interface table, If so, change the status to UNMATCHED and delete the ISIR AND NSLDS
754 -- If not present then Create the ISIR record from the ISIR and NSLDS data and delete the ISIR and NSLDS data from the matched tables.
755 rec_get_rowid_isir := null;
756 OPEN cur_get_rowid_isir(p_isir_id);
757 FETCH cur_get_rowid_isir INTO rec_get_rowid_isir;
758 CLOSE cur_get_rowid_isir;
759
760 -- If the selected record is a Simulation ISIR, then dele the Simulated ISIR from the Matched table.
761 IF rec_get_rowid_isir.system_record_type = 'SIMULATION' THEN
762 igf_ap_isir_matched_pkg.delete_row(rec_get_rowid_isir.row_id);
763 RAISE RETURN_EXP ;
764
765 ELSE
766 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
767 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_isir.debug','Before opening cursor cur_chk_isir_in_ints ');
768 END IF;
769
770 rec_chk_isir_in_ints := NULL;
771 OPEN cur_chk_isir_in_ints( rec_get_rowid_isir.batch_year, rec_get_rowid_isir.transaction_num, rec_get_rowid_isir.original_ssn, rec_get_rowid_isir.orig_name_id );
772 FETCH cur_chk_isir_in_ints INTO rec_chk_isir_in_ints;
773 CLOSE cur_chk_isir_in_ints;
774
775 -- IF the record is present in the ISIR Interface table, then update the status.
776 IF rec_chk_isir_in_ints.si_id IS NOT NULL THEN
777
778
779 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
783 UPDATE igf_ap_isir_ints_all
780 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_isir.debug','Before calling UPDATE igf_ap_isir_ints_all for si_id : '|| rec_chk_isir_in_ints.si_id);
781 END IF;
782
784 SET record_status = 'UNMATCHED',
785 last_updated_by = fnd_global.user_id,
786 last_update_date = TRUNC(SYSDATE),
787 last_update_login = fnd_global.login_id
788 WHERE si_id = rec_chk_isir_in_ints.si_id;
789
790 l_isir_data_from_mtch_tbl := TRUE;
791
792 ELSE
793
794 -- If the ISIR data is not present in the ISIR Interface table, then insert the data from the ISIR Matched table and NSLDS data table.
795 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
796 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_isir.debug','Before opening cursor cur_isir_int and insert row into igf_ap_isir_ints_all');
797 END IF;
798
799
800 OPEN cur_isir_int(p_isir_id);
801 FETCH cur_isir_int INTO cur_isir_int_rec;
802 CLOSE cur_isir_int;
803
804 OPEN max_seq;
805 FETCH max_seq INTO max_seq_rec;
806 CLOSE max_seq;
807
808 INSERT INTO igf_ap_isir_ints_all (
809 si_id,
810 batch_year_num,
811 transaction_num_txt,
812 current_ssn_txt,
813 ssn_name_change_type,
814 original_ssn_txt,
815 orig_name_id_txt,
816 last_name,
817 first_name,
818 middle_initial_txt,
819 perm_mail_address_txt,
820 perm_city_txt,
821 perm_state_txt,
822 perm_zip_cd,
823 birth_date,
824 phone_number_txt,
825 driver_license_number_txt,
826 driver_license_state_txt,
827 citizenship_status_type,
828 alien_reg_number_txt,
829 s_marital_status_type,
830 s_marital_status_date,
831 summ_enrl_status_type,
832 fall_enrl_status_type,
833 winter_enrl_status_type,
834 spring_enrl_status_type,
835 summ2_enrl_status_type,
836 fathers_highst_edu_lvl_type,
837 mothers_highst_edu_lvl_type,
838 s_state_legal_residence,
839 legal_res_before_year_flag,
840 s_legal_resd_date,
841 ss_r_u_male_flag,
842 selective_service_reg_flag,
843 degree_certification_type,
844 grade_level_in_college_type,
845 high_schl_diploma_ged_flag,
846 first_bachlr_deg_year_flag,
847 interest_in_loan_flag,
848 interest_in_stu_employ_flag,
849 drug_offence_convict_type,
850 s_tax_return_status_type,
851 s_type_tax_return_type,
852 s_elig_1040ez_type,
853 s_adjusted_gross_income_amt,
854 s_fed_taxes_paid_amt,
855 s_exemptions_amt,
856 s_income_from_work_amt,
857 spouse_income_from_work_amt,
858 s_total_from_wsa_amt,
859 s_total_from_wsb_amt,
860 s_total_from_wsc_amt,
861 s_investment_networth_amt,
862 s_busi_farm_networth_amt,
863 s_cash_savings_amt,
864 va_months_num,
865 va_amt,
866 stud_dob_before_year_flag,
867 deg_beyond_bachelor_flag,
868 s_married_flag,
869 s_have_children_flag,
870 legal_dependents_flag,
871 orphan_ward_of_court_flag,
872 s_veteran_flag,
873 p_marital_status_type,
874 father_ssn_txt,
875 f_last_name,
876 mother_ssn_txt,
877 m_last_name,
878 p_family_members_num,
879 p_in_college_num,
880 p_state_legal_residence_txt,
881 p_legal_res_before_dt_flag,
882 p_legal_res_date,
883 age_older_parent_num,
884 p_tax_return_status_type,
885 p_type_tax_return_type,
886 p_elig_1040aez_type,
887 p_adjusted_gross_income_amt,
888 p_taxes_paid_amt,
889 p_exemptions_amt,
890 f_income_work_amt,
891 m_income_work_amt,
892 p_income_wsa_amt,
893 p_income_wsb_amt,
894 p_income_wsc_amt,
895 p_investment_networth_amt,
896 p_business_networth_amt,
897 p_cash_saving_amt,
898 s_family_members_num,
899 s_in_college_num,
900 first_college_cd,
901 first_house_plan_type,
902 second_college_cd,
903 second_house_plan_type,
904 third_college_cd,
905 third_house_plan_type,
906 fourth_college_cd,
907 fourth_house_plan_type,
908 fifth_college_cd,
909 fifth_house_plan_type,
910 sixth_college_cd,
911 sixth_house_plan_type,
912 app_completed_date,
913 signed_by_type,
914 preparer_ssn_txt,
915 preparer_emp_id_number_txt,
919 faa_fedral_schl_cd,
916 preparer_sign_flag,
917 transaction_receipt_date,
918 dependency_override_type,
920 faa_adjustment_type,
921 input_record_type,
922 serial_num,
923 batch_number_txt,
924 early_analysis_flag,
925 app_entry_source_type,
926 eti_destination_cd,
927 reject_override_b_flag,
928 reject_override_n_flag,
929 reject_override_w_flag,
930 assum_override_1_flag,
931 assum_override_2_flag,
932 assum_override_3_flag,
933 assum_override_4_flag,
934 assum_override_5_flag,
935 assum_override_6_flag,
936 dependency_status_type,
937 s_email_address_txt,
938 nslds_reason_cd,
939 app_receipt_date,
940 processed_rec_type,
941 hist_corr_for_tran_num,
942 sys_generated_indicator_type,
943 dup_request_indicator_type,
944 source_of_correction_type,
945 p_cal_tax_status_type,
946 s_cal_tax_status_type,
947 graduate_flag,
948 auto_zero_efc_flag,
949 efc_change_flag,
950 sarc_flag,
951 simplified_need_test_flag,
952 reject_reason_codes_txt,
953 select_service_match_type,
954 select_service_reg_type,
955 ins_match_flag,
956 ins_verification_num,
957 sec_ins_match_type,
958 sec_ins_ver_num,
959 ssn_match_type,
960 ssa_citizenship_type,
961 ssn_death_date,
962 nslds_match_type,
963 va_match_type,
964 prisoner_match_flag,
965 verification_flag,
966 subsequent_app_flag,
967 app_source_site_cd,
968 tran_source_site_cd,
969 drn_num,
970 tran_process_date,
971 computer_batch_num,
972 correction_flags_txt,
973 highlight_flags_txt,
974 paid_efc_amt,
975 primary_efc_amt,
976 secondary_efc_amt,
977 fed_pell_grant_efc_type,
978 primary_efc_type,
979 sec_efc_type,
980 primary_alt_month_1_amt,
981 primary_alt_month_2_amt,
982 primary_alt_month_3_amt,
983 primary_alt_month_4_amt,
984 primary_alt_month_5_amt,
985 primary_alt_month_6_amt,
986 primary_alt_month_7_amt,
987 primary_alt_month_8_amt,
988 primary_alt_month_10_amt,
989 primary_alt_month_11_amt,
990 primary_alt_month_12_amt,
991 sec_alternate_month_1_amt,
992 sec_alternate_month_2_amt,
993 sec_alternate_month_3_amt,
994 sec_alternate_month_4_amt,
995 sec_alternate_month_5_amt,
996 sec_alternate_month_6_amt,
997 sec_alternate_month_7_amt,
998 sec_alternate_month_8_amt,
999 sec_alternate_month_10_amt,
1000 sec_alternate_month_11_amt,
1001 sec_alternate_month_12_amt,
1002 total_income_amt,
1003 allow_total_income_amt,
1004 state_tax_allow_amt,
1008 contribution_from_ai_amt,
1005 employment_allow_amt,
1006 income_protection_allow_amt,
1007 available_income_amt,
1009 discretionary_networth_amt,
1010 efc_networth_amt,
1011 asset_protect_allow_amt,
1012 parents_cont_from_assets_amt,
1013 adjusted_avail_income_amt,
1014 total_student_contrib_amt,
1015 total_parent_contrib_amt,
1016 parents_contribution_amt,
1017 student_total_income_amt,
1018 sati_amt,
1019 sic_amt,
1020 sdnw_amt,
1021 sca_amt,
1022 fti_amt,
1023 secti_amt,
1024 secati_amt,
1025 secstx_amt,
1026 secea_amt,
1027 secipa_amt,
1028 secai_amt,
1029 seccai_amt,
1030 secdnw_amt,
1031 secnw_amt,
1032 secapa_amt,
1033 SECPCA_AMT,
1034 secaai_amt,
1035 sectsc_amt,
1036 sectpc_amt,
1037 secpc_amt,
1038 secsti_amt,
1039 secsic_amt,
1040 secsati_amt,
1041 secsdnw_amt,
1042 secsca_amt,
1043 secfti_amt,
1044 a_citizenship_flag,
1045 a_studnt_marital_status_flag,
1046 a_student_agi_amt,
1047 a_s_us_tax_paid_amt,
1048 a_s_income_work_amt,
1049 a_spouse_income_work_amt,
1050 a_s_total_wsc_amt,
1051 a_date_of_birth_flag,
1052 a_student_married_flag,
1053 a_have_children_flag,
1054 a_s_have_dependents_flag,
1055 a_va_status_flag,
1056 a_s_in_family_num,
1057 a_s_in_college_num,
1058 a_p_marital_status_flag,
1059 a_father_ssn_txt,
1060 a_mother_ssn_txt,
1061 a_parents_family_num,
1062 a_parents_college_num,
1063 a_parents_agi_amt,
1064 a_p_us_tax_paid_amt,
1065 a_f_work_income_amt,
1066 a_m_work_income_amt,
1067 a_p_total_wsc_amt,
1068 comment_codes_txt,
1069 sar_ack_comm_codes_txt,
1070 pell_grant_elig_flag,
1071 reprocess_reason_cd,
1072 duplicate_date,
1073 isir_transaction_type,
1074 fedral_schl_type,
1075 multi_school_cd_flags_txt,
1076 dup_ssn_indicator_flag,
1077 verif_track_type,
1078 fafsa_data_verification_txt,
1079 reject_override_a_flag,
1080 reject_override_c_flag,
1081 parent_marital_status_date,
1082 fathr_first_name_initial_txt,
1083 fathr_step_father_birth_date,
1084 mothr_first_name_initial_txt,
1085 mothr_step_mother_birth_date,
1086 parents_email_address_txt,
1087 address_change_type,
1088 cps_pushed_isir_flag,
1089 electronic_transaction_type,
1090 sar_c_change_type,
1091 father_ssn_match_type,
1092 mother_ssn_match_type,
1093 reject_override_g_flag,
1094 dhs_verification_num_txt,
1095 data_file_name_txt,
1096 nslds_transaction_num,
1097 nslds_database_results_type,
1098 nslds_flag,
1099 nslds_pell_overpay_type,
1100 nslds_pell_overpay_cont_txt,
1101 nslds_seog_overpay_type,
1102 nslds_seog_overpay_cont_txt,
1103 nslds_perkins_overpay_type,
1104 nslds_perk_ovrpay_cntct_txt,
1105 nslds_defaulted_loan_flag,
1106 nslds_dischgd_loan_chng_flag,
1107 nslds_satis_repay_flag,
1108 nslds_act_bankruptcy_flag,
1109 nslds_agg_subsz_out_pbal_amt,
1110 nslds_agg_unsbz_out_pbal_amt,
1111 nslds_agg_comb_out_pbal_amt,
1112 nslds_agg_cons_out_pbal_amt,
1113 nslds_agg_subsz_pnd_disb_amt,
1114 nslds_agg_unsbz_pnd_disb_amt,
1115 nslds_agg_comb_pend_disb_amt,
1116 nslds_agg_subsz_total_amt,
1117 nslds_agg_unsbz_total_amt,
1118 nslds_agg_comb_total_amt,
1119 nslds_agg_consd_total_amt,
1120 nslds_perkins_out_bal_amt,
1121 nslds_perkin_cur_yr_disb_amt,
1122 nslds_default_loan_chng_flag,
1123 nslds_discharged_loan_type,
1124 nslds_satis_repay_chng_flag,
1125 nslds_act_bnkrupt_chng_flag,
1126 nslds_overpay_chng_flag,
1127 nslds_agg_loan_chng_flag,
1128 nslds_perkins_loan_chng_flag,
1129 nslds_pell_paymnt_chng_flag,
1130 nslds_addtnl_pell_flag,
1131 nslds_addtnl_loan_flag,
1135 nslds_pell_1_efc_amt,
1132 direct_loan_mas_prom_nt_type,
1133 nslds_pell_1_seq_num,
1134 nslds_pell_1_verify_f_txt,
1136 nslds_pell_1_school_num,
1137 nslds_pell_1_transcn_num,
1138 nslds_pell_1_last_updt_date,
1139 nslds_pell_1_scheduled_amt,
1140 nslds_pell_1_paid_todt_amt,
1141 nslds_pell_1_remng_amt,
1142 nslds_pell_1_pc_scwd_use_amt,
1143 nslds_pell_1_award_amt,
1144 nslds_pell_2_seq_num,
1145 nslds_pell_2_verify_f_txt,
1146 nslds_pell_2_efc_amt,
1147 nslds_pell_2_school_num,
1148 nslds_pell_2_transcn_num,
1149 nslds_pell_2_last_updt_date,
1150 nslds_pell_2_scheduled_amt,
1151 nslds_pell_2_paid_todt_amt,
1152 nslds_pell_2_remng_amt,
1153 nslds_pell_2_pc_scwd_use_amt,
1154 nslds_pell_2_award_amt,
1155 nslds_pell_3_seq_num,
1156 nslds_pell_3_verify_f_txt,
1157 nslds_pell_3_efc_amt,
1158 nslds_pell_3_school_num,
1159 nslds_pell_3_transcn_num,
1160 nslds_pell_3_last_updt_date,
1161 nslds_pell_3_scheduled_amt,
1162 nslds_pell_3_paid_todt_amt,
1163 nslds_pell_3_remng_amt,
1164 nslds_pell_3_pc_scwd_use_amt,
1165 nslds_pell_3_award_amt,
1166 nslds_loan_1_seq_num,
1167 nslds_loan_1_type,
1168 nslds_loan_1_chng_flag,
1169 nslds_loan_1_prog_cd,
1170 nslds_loan_1_net_amt,
1171 nslds_loan_1_cur_st_cd,
1172 nslds_loan_1_cur_st_date,
1173 nslds_loan_1_agg_pr_bal_amt,
1174 nslds_loan_1_out_pr_bal_date,
1175 nslds_loan_1_begin_date,
1176 nslds_loan_1_end_date,
1177 nslds_loan_1_ga_cd,
1178 nslds_loan_1_cont_type,
1179 nslds_loan_1_schol_cd,
1180 nslds_loan_1_cont_cd,
1181 nslds_loan_1_grade_lvl_txt,
1182 nslds_loan_1_x_unsbz_ln_type,
1183 nslds_loan_1_captal_int_flag,
1184 nslds_loan_2_seq_num,
1185 nslds_loan_2_type,
1186 nslds_loan_2_chng_flag,
1187 nslds_loan_2_prog_cd,
1188 nslds_loan_2_net_amt,
1189 nslds_loan_2_cur_st_cd,
1190 nslds_loan_2_cur_st_date,
1191 nslds_loan_2_agg_pr_bal_amt,
1192 nslds_loan_2_out_pr_bal_date,
1193 nslds_loan_2_begin_date,
1194 nslds_loan_2_end_date,
1195 nslds_loan_2_ga_cd,
1196 nslds_loan_2_cont_type,
1197 nslds_loan_2_schol_cd,
1198 nslds_loan_2_cont_cd,
1199 nslds_loan_2_grade_lvl_txt,
1200 nslds_loan_2_x_unsbz_ln_type,
1201 nslds_loan_2_captal_int_flag,
1202 nslds_loan_3_seq_num,
1203 nslds_loan_3_type,
1204 nslds_loan_3_chng_flag,
1205 nslds_loan_3_prog_cd,
1206 nslds_loan_3_net_amt,
1207 nslds_loan_3_cur_st_cd,
1208 nslds_loan_3_cur_st_date,
1209 nslds_loan_3_agg_pr_bal_amt,
1210 nslds_loan_3_out_pr_bal_date,
1211 nslds_loan_3_begin_date,
1212 nslds_loan_3_end_date,
1213 nslds_loan_3_ga_cd,
1214 nslds_loan_3_cont_type,
1215 nslds_loan_3_schol_cd,
1216 nslds_loan_3_cont_cd,
1217 nslds_loan_3_grade_lvl_txt,
1218 nslds_loan_3_x_unsbz_ln_type,
1219 nslds_loan_3_captal_int_flag,
1220 nslds_loan_4_seq_num,
1221 nslds_loan_4_type,
1222 nslds_loan_4_chng_flag,
1223 nslds_loan_4_prog_cd,
1224 nslds_loan_4_net_amt,
1225 nslds_loan_4_cur_st_cd,
1226 nslds_loan_4_cur_st_date,
1227 nslds_loan_4_agg_pr_bal_amt,
1228 nslds_loan_4_out_pr_bal_date,
1229 nslds_loan_4_begin_date,
1230 nslds_loan_4_end_date,
1231 nslds_loan_4_ga_cd,
1232 nslds_loan_4_cont_type,
1233 nslds_loan_4_schol_cd,
1234 nslds_loan_4_cont_cd,
1235 nslds_loan_4_grade_lvl_txt,
1236 nslds_loan_4_x_unsbz_ln_type,
1237 nslds_loan_4_captal_int_flag,
1238 nslds_loan_5_seq_num,
1239 nslds_loan_5_type,
1240 nslds_loan_5_chng_flag,
1241 nslds_loan_5_prog_cd,
1242 nslds_loan_5_net_amt,
1243 nslds_loan_5_cur_st_cd,
1244 nslds_loan_5_cur_st_date,
1245 nslds_loan_5_agg_pr_bal_amt,
1246 nslds_loan_5_out_pr_bal_date,
1247 nslds_loan_5_begin_date,
1248 nslds_loan_5_end_date,
1249 nslds_loan_5_ga_cd,
1250 nslds_loan_5_cont_type,
1251 nslds_loan_5_schol_cd,
1252 nslds_loan_5_cont_cd,
1253 nslds_loan_5_grade_lvl_txt,
1254 nslds_loan_5_x_unsbz_ln_type,
1255 nslds_loan_5_captal_int_flag,
1259 nslds_loan_6_prog_cd,
1256 nslds_loan_6_seq_num,
1257 nslds_loan_6_type,
1258 nslds_loan_6_chng_flag,
1260 nslds_loan_6_net_amt,
1261 nslds_loan_6_cur_st_cd,
1262 nslds_loan_6_cur_st_date,
1263 nslds_loan_6_agg_pr_bal_amt,
1264 nslds_loan_6_out_pr_bal_date,
1265 nslds_loan_6_begin_date,
1266 nslds_loan_6_end_date,
1267 nslds_loan_6_ga_cd,
1268 nslds_loan_6_cont_type,
1269 nslds_loan_6_schol_cd,
1270 nslds_loan_6_cont_cd,
1271 nslds_loan_6_grade_lvl_txt,
1272 nslds_loan_6_x_unsbz_ln_type,
1273 nslds_loan_6_captal_int_flag,
1274 nslds_loan_1_last_disb_amt,
1275 nslds_loan_1_last_disb_date,
1276 nslds_loan_2_last_disb_amt,
1277 nslds_loan_2_last_disb_date,
1278 nslds_loan_3_last_disb_amt,
1279 nslds_loan_3_last_disb_date,
1280 nslds_loan_4_last_disb_amt,
1281 nslds_loan_4_last_disb_date,
1282 nslds_loan_5_last_disb_amt,
1283 nslds_loan_5_last_disb_date,
1284 nslds_loan_6_last_disb_amt,
1285 nslds_loan_6_last_disb_date,
1286 dlp_master_prom_note_type,
1287 subsidized_loan_limit_type,
1288 combined_loan_limit_type,
1289 record_status,
1290 creation_date,
1291 created_by,
1292 last_updated_by,
1293 last_update_login,
1294 last_update_date
1295 ) VALUES (
1296 max_seq_rec.max_si_id + 1,
1297 cur_isir_int_rec.batch_year,
1298 cur_isir_int_rec.transaction_num,
1299 cur_isir_int_rec.current_ssn,
1300 cur_isir_int_rec.ssn_name_change,
1301 cur_isir_int_rec.original_ssn,
1302 cur_isir_int_rec.orig_name_id,
1303 cur_isir_int_rec.last_name,
1304 cur_isir_int_rec.first_name,
1305 cur_isir_int_rec.middle_initial,
1306 cur_isir_int_rec.perm_mail_add,
1307 cur_isir_int_rec.perm_city,
1308 cur_isir_int_rec.perm_state,
1309 cur_isir_int_rec.perm_zip_code,
1310 cur_isir_int_rec.date_of_birth,
1311 cur_isir_int_rec.phone_number,
1312 cur_isir_int_rec.driver_license_number,
1313 cur_isir_int_rec.driver_license_state,
1314 cur_isir_int_rec.citizenship_status,
1315 cur_isir_int_rec.alien_reg_number,
1316 cur_isir_int_rec.s_marital_status,
1317 cur_isir_int_rec.s_marital_status_date,
1318 cur_isir_int_rec.summ_enrl_status,
1319 cur_isir_int_rec.fall_enrl_status,
1320 cur_isir_int_rec.winter_enrl_status,
1321 cur_isir_int_rec.spring_enrl_status,
1322 cur_isir_int_rec.summ2_enrl_status,
1323 cur_isir_int_rec.fathers_highest_edu_level,
1324 cur_isir_int_rec.mothers_highest_edu_level,
1325 cur_isir_int_rec.s_state_legal_residence,
1326 cur_isir_int_rec.legal_residence_before_date,
1327 cur_isir_int_rec.s_legal_resd_date,
1328 cur_isir_int_rec.ss_r_u_male,
1329 cur_isir_int_rec.selective_service_reg,
1330 cur_isir_int_rec.degree_certification,
1331 cur_isir_int_rec.grade_level_in_college,
1332 cur_isir_int_rec.high_school_diploma_ged,
1333 cur_isir_int_rec.first_bachelor_deg_by_date,
1334 cur_isir_int_rec.interest_in_loan,
1335 cur_isir_int_rec.interest_in_stud_employment,
1336 cur_isir_int_rec.drug_offence_conviction,
1337 cur_isir_int_rec.s_tax_return_status,
1338 cur_isir_int_rec.s_type_tax_return,
1339 cur_isir_int_rec.s_elig_1040ez,
1340 cur_isir_int_rec.s_adjusted_gross_income,
1341 cur_isir_int_rec.s_fed_taxes_paid,
1342 cur_isir_int_rec.s_exemptions,
1343 cur_isir_int_rec.s_income_from_work,
1344 cur_isir_int_rec.spouse_income_from_work,
1345 cur_isir_int_rec.s_toa_amt_from_wsa,
1346 cur_isir_int_rec.s_toa_amt_from_wsb,
1347 cur_isir_int_rec.s_toa_amt_from_wsc,
1348 cur_isir_int_rec.s_investment_networth,
1349 cur_isir_int_rec.s_busi_farm_networth,
1350 cur_isir_int_rec.s_cash_savings,
1351 cur_isir_int_rec.va_months,
1352 cur_isir_int_rec.va_amount,
1353 cur_isir_int_rec.stud_dob_before_date,
1354 cur_isir_int_rec.deg_beyond_bachelor,
1355 cur_isir_int_rec.s_married,
1356 cur_isir_int_rec.s_have_children,
1357 cur_isir_int_rec.legal_dependents,
1358 cur_isir_int_rec.orphan_ward_of_court,
1359 cur_isir_int_rec.s_veteran,
1360 cur_isir_int_rec.p_marital_status,
1361 cur_isir_int_rec.father_ssn,
1362 cur_isir_int_rec.f_last_name,
1363 cur_isir_int_rec.mother_ssn,
1364 cur_isir_int_rec.m_last_name,
1365 cur_isir_int_rec.p_num_family_member,
1366 cur_isir_int_rec.p_num_in_college,
1367 cur_isir_int_rec.p_state_legal_residence,
1368 cur_isir_int_rec.p_state_legal_res_before_dt,
1369 cur_isir_int_rec.p_legal_res_date,
1370 cur_isir_int_rec.age_older_parent,
1374 cur_isir_int_rec.p_adjusted_gross_income,
1371 cur_isir_int_rec.p_tax_return_status,
1372 cur_isir_int_rec.p_type_tax_return,
1373 cur_isir_int_rec.p_elig_1040aez,
1375 cur_isir_int_rec.p_taxes_paid,
1376 cur_isir_int_rec.p_exemptions,
1377 cur_isir_int_rec.f_income_work,
1378 cur_isir_int_rec.m_income_work,
1379 cur_isir_int_rec.p_income_wsa,
1380 cur_isir_int_rec.p_income_wsb,
1381 cur_isir_int_rec.p_income_wsc,
1382 cur_isir_int_rec.p_investment_networth,
1383 cur_isir_int_rec.p_business_networth,
1384 cur_isir_int_rec.p_cash_saving,
1385 cur_isir_int_rec.s_num_family_members,
1386 cur_isir_int_rec.s_num_in_college,
1387 cur_isir_int_rec.first_college,
1388 cur_isir_int_rec.first_house_plan,
1389 cur_isir_int_rec.second_college,
1390 cur_isir_int_rec.second_house_plan,
1391 cur_isir_int_rec.third_college,
1392 cur_isir_int_rec.third_house_plan,
1393 cur_isir_int_rec.fourth_college,
1394 cur_isir_int_rec.fourth_house_plan,
1395 cur_isir_int_rec.fifth_college,
1396 cur_isir_int_rec.fifth_house_plan,
1397 cur_isir_int_rec.sixth_college,
1398 cur_isir_int_rec.sixth_house_plan,
1399 cur_isir_int_rec.date_app_completed,
1400 cur_isir_int_rec.signed_by,
1401 cur_isir_int_rec.preparer_ssn,
1402 cur_isir_int_rec.preparer_emp_id_number,
1403 cur_isir_int_rec.preparer_sign,
1404 cur_isir_int_rec.transaction_receipt_date,
1405 cur_isir_int_rec.dependency_override_ind,
1406 cur_isir_int_rec.faa_fedral_schl_code,
1407 cur_isir_int_rec.faa_adjustment,
1408 cur_isir_int_rec.input_record_type,
1409 cur_isir_int_rec.serial_number,
1410 cur_isir_int_rec.batch_number,
1411 cur_isir_int_rec.early_analysis_flag,
1412 cur_isir_int_rec.app_entry_source_code,
1413 cur_isir_int_rec.eti_destination_code,
1414 cur_isir_int_rec.reject_override_b,
1415 cur_isir_int_rec.reject_override_n,
1416 cur_isir_int_rec.reject_override_w,
1417 cur_isir_int_rec.assum_override_1,
1418 cur_isir_int_rec.assum_override_2,
1419 cur_isir_int_rec.assum_override_3,
1420 cur_isir_int_rec.assum_override_4,
1421 cur_isir_int_rec.assum_override_5,
1422 cur_isir_int_rec.assum_override_6,
1423 cur_isir_int_rec.dependency_status,
1424 cur_isir_int_rec.s_email_address,
1425 cur_isir_int_rec.nslds_reason_code,
1426 cur_isir_int_rec.app_receipt_date,
1427 cur_isir_int_rec.processed_rec_type,
1428 cur_isir_int_rec.hist_correction_for_tran_id,
1429 cur_isir_int_rec.system_generated_indicator,
1430 cur_isir_int_rec.dup_request_indicator,
1431 cur_isir_int_rec.source_of_correction,
1432 cur_isir_int_rec.p_cal_tax_status,
1433 cur_isir_int_rec.s_cal_tax_status,
1434 cur_isir_int_rec.graduate_flag,
1435 cur_isir_int_rec.auto_zero_efc,
1436 cur_isir_int_rec.efc_change_flag,
1437 cur_isir_int_rec.sarc_flag,
1438 cur_isir_int_rec.simplified_need_test,
1439 cur_isir_int_rec.reject_reason_codes,
1440 cur_isir_int_rec.select_service_match_flag,
1441 cur_isir_int_rec.select_service_reg_flag,
1442 cur_isir_int_rec.ins_match_flag,
1443 cur_isir_int_rec.ins_verification_number,
1444 cur_isir_int_rec.sec_ins_match_flag,
1445 cur_isir_int_rec.sec_ins_ver_number,
1446 cur_isir_int_rec.ssn_match_flag,
1447 cur_isir_int_rec.ssa_citizenship_flag,
1448 cur_isir_int_rec.ssn_date_of_death,
1449 cur_isir_int_rec.nslds_match_flag,
1450 cur_isir_int_rec.va_match_flag,
1451 cur_isir_int_rec.prisoner_match,
1452 cur_isir_int_rec.verification_flag,
1453 cur_isir_int_rec.subsequent_app_flag,
1454 cur_isir_int_rec.app_source_site_code,
1455 cur_isir_int_rec.tran_source_site_code,
1456 cur_isir_int_rec.drn,
1457 cur_isir_int_rec.tran_process_date,
1458 cur_isir_int_rec.computer_batch_number,
1459 cur_isir_int_rec.correction_flags,
1460 cur_isir_int_rec.highlight_flags,
1461 cur_isir_int_rec.paid_efc,
1462 cur_isir_int_rec.primary_efc,
1463 cur_isir_int_rec.secondary_efc,
1464 cur_isir_int_rec.fed_pell_grant_efc_type,
1465 cur_isir_int_rec.primary_efc_type,
1466 cur_isir_int_rec.sec_efc_type,
1467 cur_isir_int_rec.primary_alternate_month_1,
1468 cur_isir_int_rec.primary_alternate_month_2,
1469 cur_isir_int_rec.primary_alternate_month_3,
1470 cur_isir_int_rec.primary_alternate_month_4,
1471 cur_isir_int_rec.primary_alternate_month_5,
1472 cur_isir_int_rec.primary_alternate_month_6,
1473 cur_isir_int_rec.primary_alternate_month_7,
1474 cur_isir_int_rec.primary_alternate_month_8,
1475 cur_isir_int_rec.primary_alternate_month_10,
1476 cur_isir_int_rec.primary_alternate_month_11,
1477 cur_isir_int_rec.primary_alternate_month_12,
1478 cur_isir_int_rec.sec_alternate_month_1,
1482 cur_isir_int_rec.sec_alternate_month_5,
1479 cur_isir_int_rec.sec_alternate_month_2,
1480 cur_isir_int_rec.sec_alternate_month_3,
1481 cur_isir_int_rec.sec_alternate_month_4,
1483 cur_isir_int_rec.sec_alternate_month_6,
1484 cur_isir_int_rec.sec_alternate_month_7,
1485 cur_isir_int_rec.sec_alternate_month_8,
1486 cur_isir_int_rec.sec_alternate_month_10,
1487 cur_isir_int_rec.sec_alternate_month_11,
1488 cur_isir_int_rec.sec_alternate_month_12,
1489 cur_isir_int_rec.total_income,
1490 cur_isir_int_rec.allow_total_income,
1491 cur_isir_int_rec.state_tax_allow,
1492 cur_isir_int_rec.employment_allow,
1493 cur_isir_int_rec.income_protection_allow,
1494 cur_isir_int_rec.available_income,
1495 cur_isir_int_rec.contribution_from_ai,
1496 cur_isir_int_rec.discretionary_networth,
1497 cur_isir_int_rec.efc_networth,
1498 cur_isir_int_rec.asset_protect_allow,
1499 cur_isir_int_rec.parents_cont_from_assets,
1500 cur_isir_int_rec.adjusted_available_income,
1501 cur_isir_int_rec.total_student_contribution,
1502 cur_isir_int_rec.total_parent_contribution,
1503 cur_isir_int_rec.parents_contribution,
1504 cur_isir_int_rec.student_total_income,
1505 cur_isir_int_rec.sati,
1506 cur_isir_int_rec.sic,
1507 cur_isir_int_rec.sdnw,
1508 cur_isir_int_rec.sca,
1509 cur_isir_int_rec.fti,
1510 cur_isir_int_rec.secti,
1511 cur_isir_int_rec.secati,
1512 cur_isir_int_rec.secstx,
1513 cur_isir_int_rec.secea,
1514 cur_isir_int_rec.secipa,
1515 cur_isir_int_rec.secai,
1516 cur_isir_int_rec.seccai,
1517 cur_isir_int_rec.secdnw,
1518 cur_isir_int_rec.secnw,
1519 cur_isir_int_rec.secapa,
1520 cur_isir_int_rec.secpca,
1521 cur_isir_int_rec.secaai,
1522 cur_isir_int_rec.sectsc,
1523 cur_isir_int_rec.sectpc,
1524 cur_isir_int_rec.secpc,
1525 cur_isir_int_rec.secsti,
1526 cur_isir_int_rec.secsic,
1527 cur_isir_int_rec.secsati,
1528 cur_isir_int_rec.secsdnw,
1529 cur_isir_int_rec.secsca,
1530 cur_isir_int_rec.secfti,
1531 cur_isir_int_rec.a_citizenship,
1532 cur_isir_int_rec.a_student_marital_status,
1533 cur_isir_int_rec.a_student_agi,
1534 cur_isir_int_rec.a_s_us_tax_paid,
1535 cur_isir_int_rec.a_s_income_work,
1536 cur_isir_int_rec.a_spouse_income_work,
1537 cur_isir_int_rec.a_s_total_wsc,
1538 cur_isir_int_rec.a_date_of_birth,
1539 cur_isir_int_rec.a_student_married,
1540 cur_isir_int_rec.a_have_children,
1541 cur_isir_int_rec.a_s_have_dependents,
1542 cur_isir_int_rec.a_va_status,
1543 cur_isir_int_rec.a_s_num_in_family,
1544 cur_isir_int_rec.a_s_num_in_college,
1545 cur_isir_int_rec.a_p_marital_status,
1546 cur_isir_int_rec.a_father_ssn,
1547 cur_isir_int_rec.a_mother_ssn,
1548 cur_isir_int_rec.a_parents_num_family,
1549 cur_isir_int_rec.a_parents_num_college,
1550 cur_isir_int_rec.a_parents_agi,
1551 cur_isir_int_rec.a_p_us_tax_paid,
1552 cur_isir_int_rec.a_f_work_income,
1553 cur_isir_int_rec.a_m_work_income,
1554 cur_isir_int_rec.a_p_total_wsc,
1555 cur_isir_int_rec.comment_codes,
1556 cur_isir_int_rec.sar_ack_comm_code,
1557 cur_isir_int_rec.pell_grant_elig_flag,
1558 cur_isir_int_rec.reprocess_reason_code,
1559 cur_isir_int_rec.duplicate_date,
1560 cur_isir_int_rec.isir_transaction_type,
1561 cur_isir_int_rec.fedral_schl_code_indicator,
1562 cur_isir_int_rec.multi_school_code_flags,
1563 cur_isir_int_rec.dup_ssn_indicator,
1564 cur_isir_int_rec.verif_track_flag,
1565 cur_isir_int_rec.fafsa_data_verify_flags,
1566 cur_isir_int_rec.reject_override_a,
1567 cur_isir_int_rec.reject_override_c,
1568 cur_isir_int_rec.parent_marital_status_date,
1569 cur_isir_int_rec.father_first_name_initial_txt,
1570 cur_isir_int_rec.father_step_father_birth_date,
1571 cur_isir_int_rec.mother_first_name_initial_txt,
1572 cur_isir_int_rec.mother_step_mother_birth_date,
1573 cur_isir_int_rec.parents_email_address_txt,
1574 cur_isir_int_rec.address_change_type,
1575 cur_isir_int_rec.cps_pushed_isir_flag,
1576 cur_isir_int_rec.electronic_transaction_type,
1577 cur_isir_int_rec.sar_c_change_type,
1578 cur_isir_int_rec.father_ssn_match_type,
1579 cur_isir_int_rec.mother_ssn_match_type,
1580 cur_isir_int_rec.reject_override_g_flag,
1581 cur_isir_int_rec.dhs_verification_num_txt,
1582 cur_isir_int_rec.data_file_name_txt,
1583 cur_isir_int_rec.nslds_transaction_num,
1584 cur_isir_int_rec.nslds_database_results_f,
1585 cur_isir_int_rec.nslds_f,
1586 cur_isir_int_rec.nslds_pell_overpay_f,
1587 cur_isir_int_rec.nslds_pell_overpay_contact,
1588 cur_isir_int_rec.nslds_seog_overpay_f,
1592 cur_isir_int_rec.nslds_defaulted_loan_f,
1589 cur_isir_int_rec.nslds_seog_overpay_contact,
1590 cur_isir_int_rec.nslds_perkins_overpay_f,
1591 cur_isir_int_rec.nslds_perkins_overpay_cntct,
1593 cur_isir_int_rec.nslds_dischged_loan_chng_f,
1594 cur_isir_int_rec.nslds_satis_repay_f,
1595 cur_isir_int_rec.nslds_act_bankruptcy_f,
1596 cur_isir_int_rec.nslds_agg_subsz_out_prin_bal,
1597 cur_isir_int_rec.nslds_agg_unsbz_out_prin_bal,
1598 cur_isir_int_rec.nslds_agg_comb_out_prin_bal,
1599 cur_isir_int_rec.nslds_agg_cons_out_prin_bal,
1600 cur_isir_int_rec.nslds_agg_subsz_pend_dismt,
1601 cur_isir_int_rec.nslds_agg_unsbz_pend_dismt,
1602 cur_isir_int_rec.nslds_agg_comb_pend_dismt,
1603 cur_isir_int_rec.nslds_agg_subsz_total,
1604 cur_isir_int_rec.nslds_agg_unsbz_total,
1605 cur_isir_int_rec.nslds_agg_comb_total,
1606 cur_isir_int_rec.nslds_agg_consd_total,
1607 cur_isir_int_rec.nslds_perkins_out_bal,
1608 cur_isir_int_rec.nslds_perkins_cur_yr_dismnt,
1609 cur_isir_int_rec.nslds_default_loan_chng_f,
1610 cur_isir_int_rec.nslds_discharged_loan_f,
1611 cur_isir_int_rec.nslds_satis_repay_chng_f,
1612 cur_isir_int_rec.nslds_act_bnkrupt_chng_f,
1613 cur_isir_int_rec.nslds_overpay_chng_f,
1614 cur_isir_int_rec.nslds_agg_loan_chng_f,
1615 cur_isir_int_rec.nslds_perkins_loan_chng_f,
1616 cur_isir_int_rec.nslds_pell_paymnt_chng_f,
1617 cur_isir_int_rec.nslds_addtnl_pell_f,
1618 cur_isir_int_rec.nslds_addtnl_loan_f,
1619 cur_isir_int_rec.direct_loan_mas_prom_nt_f,
1620 cur_isir_int_rec.nslds_pell_seq_num_1,
1621 cur_isir_int_rec.nslds_pell_verify_f_1,
1622 cur_isir_int_rec.nslds_pell_efc_1,
1623 cur_isir_int_rec.nslds_pell_school_code_1,
1624 cur_isir_int_rec.nslds_pell_transcn_num_1,
1625 cur_isir_int_rec.nslds_pell_last_updt_dt_1,
1626 cur_isir_int_rec.nslds_pell_scheduled_amt_1,
1627 cur_isir_int_rec.nslds_pell_amt_paid_todt_1,
1628 cur_isir_int_rec.nslds_pell_remng_amt_1,
1629 cur_isir_int_rec.nslds_pell_pc_schd_awd_us_1,
1630 cur_isir_int_rec.nslds_pell_award_amt_1,
1631 cur_isir_int_rec.nslds_pell_seq_num_2,
1632 cur_isir_int_rec.nslds_pell_verify_f_2,
1633 cur_isir_int_rec.nslds_pell_efc_2,
1634 cur_isir_int_rec.nslds_pell_school_code_2,
1635 cur_isir_int_rec.nslds_pell_transcn_num_2,
1636 cur_isir_int_rec.nslds_pell_last_updt_dt_2,
1637 cur_isir_int_rec.nslds_pell_scheduled_amt_2,
1638 cur_isir_int_rec.nslds_pell_amt_paid_todt_2,
1639 cur_isir_int_rec.nslds_pell_remng_amt_2,
1640 cur_isir_int_rec.nslds_pell_pc_schd_awd_us_2,
1641 cur_isir_int_rec.nslds_pell_award_amt_2,
1642 cur_isir_int_rec.nslds_pell_seq_num_3,
1643 cur_isir_int_rec.nslds_pell_verify_f_3,
1644 cur_isir_int_rec.nslds_pell_efc_3,
1645 cur_isir_int_rec.nslds_pell_school_code_3,
1646 cur_isir_int_rec.nslds_pell_transcn_num_3,
1647 cur_isir_int_rec.nslds_pell_last_updt_dt_3,
1648 cur_isir_int_rec.nslds_pell_scheduled_amt_3,
1649 cur_isir_int_rec.nslds_pell_amt_paid_todt_3,
1650 cur_isir_int_rec.nslds_pell_remng_amt_3,
1651 cur_isir_int_rec.nslds_pell_pc_schd_awd_us_3,
1652 cur_isir_int_rec.nslds_pell_award_amt_3,
1653 cur_isir_int_rec.nslds_loan_seq_num_1,
1654 cur_isir_int_rec.nslds_loan_type_code_1,
1655 cur_isir_int_rec.nslds_loan_chng_f_1,
1656 cur_isir_int_rec.nslds_loan_prog_code_1,
1657 cur_isir_int_rec.nslds_loan_net_amnt_1,
1658 cur_isir_int_rec.nslds_loan_cur_st_code_1,
1659 cur_isir_int_rec.nslds_loan_cur_st_date_1,
1660 cur_isir_int_rec.nslds_loan_agg_pr_bal_1,
1661 cur_isir_int_rec.nslds_loan_out_pr_bal_dt_1,
1662 cur_isir_int_rec.nslds_loan_begin_dt_1,
1663 cur_isir_int_rec.nslds_loan_end_dt_1,
1664 cur_isir_int_rec.nslds_loan_ga_code_1,
1665 cur_isir_int_rec.nslds_loan_cont_type_1,
1666 cur_isir_int_rec.nslds_loan_schol_code_1,
1667 cur_isir_int_rec.nslds_loan_cont_code_1,
1668 cur_isir_int_rec.nslds_loan_grade_lvl_1,
1669 cur_isir_int_rec.nslds_loan_xtr_unsbz_ln_f_1,
1670 cur_isir_int_rec.nslds_loan_capital_int_f_1,
1671 cur_isir_int_rec.nslds_loan_seq_num_2,
1672 cur_isir_int_rec.nslds_loan_type_code_2,
1673 cur_isir_int_rec.nslds_loan_chng_f_2,
1674 cur_isir_int_rec.nslds_loan_prog_code_2,
1675 cur_isir_int_rec.nslds_loan_net_amnt_2,
1676 cur_isir_int_rec.nslds_loan_cur_st_code_2,
1677 cur_isir_int_rec.nslds_loan_cur_st_date_2,
1678 cur_isir_int_rec.nslds_loan_agg_pr_bal_2,
1679 cur_isir_int_rec.nslds_loan_out_pr_bal_dt_2,
1680 cur_isir_int_rec.nslds_loan_begin_dt_2,
1681 cur_isir_int_rec.nslds_loan_end_dt_2,
1682 cur_isir_int_rec.nslds_loan_ga_code_2,
1683 cur_isir_int_rec.nslds_loan_cont_type_2,
1684 cur_isir_int_rec.nslds_loan_schol_code_2,
1685 cur_isir_int_rec.nslds_loan_cont_code_2,
1686 cur_isir_int_rec.nslds_loan_grade_lvl_2,
1687 cur_isir_int_rec.nslds_loan_xtr_unsbz_ln_f_2,
1691 cur_isir_int_rec.nslds_loan_chng_f_3,
1688 cur_isir_int_rec.nslds_loan_capital_int_f_2,
1689 cur_isir_int_rec.nslds_loan_seq_num_3,
1690 cur_isir_int_rec.nslds_loan_type_code_3,
1692 cur_isir_int_rec.nslds_loan_prog_code_3,
1693 cur_isir_int_rec.nslds_loan_net_amnt_3,
1694 cur_isir_int_rec.nslds_loan_cur_st_code_3,
1695 cur_isir_int_rec.nslds_loan_cur_st_date_3,
1696 cur_isir_int_rec.nslds_loan_agg_pr_bal_3,
1697 cur_isir_int_rec.nslds_loan_out_pr_bal_dt_3,
1698 cur_isir_int_rec.nslds_loan_begin_dt_3,
1699 cur_isir_int_rec.nslds_loan_end_dt_3,
1700 cur_isir_int_rec.nslds_loan_ga_code_3,
1701 cur_isir_int_rec.nslds_loan_cont_type_3,
1702 cur_isir_int_rec.nslds_loan_schol_code_3,
1703 cur_isir_int_rec.nslds_loan_cont_code_3,
1704 cur_isir_int_rec.nslds_loan_grade_lvl_3,
1705 cur_isir_int_rec.nslds_loan_xtr_unsbz_ln_f_3,
1706 cur_isir_int_rec.nslds_loan_capital_int_f_3,
1707 cur_isir_int_rec.nslds_loan_seq_num_4,
1708 cur_isir_int_rec.nslds_loan_type_code_4,
1709 cur_isir_int_rec.nslds_loan_chng_f_4,
1710 cur_isir_int_rec.nslds_loan_prog_code_4,
1711 cur_isir_int_rec.nslds_loan_net_amnt_4,
1712 cur_isir_int_rec.nslds_loan_cur_st_code_4,
1713 cur_isir_int_rec.nslds_loan_cur_st_date_4,
1714 cur_isir_int_rec.nslds_loan_agg_pr_bal_4,
1715 cur_isir_int_rec.nslds_loan_out_pr_bal_dt_4,
1716 cur_isir_int_rec.nslds_loan_begin_dt_4,
1717 cur_isir_int_rec.nslds_loan_end_dt_4,
1718 cur_isir_int_rec.nslds_loan_ga_code_4,
1719 cur_isir_int_rec.nslds_loan_cont_type_4,
1720 cur_isir_int_rec.nslds_loan_schol_code_4,
1721 cur_isir_int_rec.nslds_loan_cont_code_4,
1722 cur_isir_int_rec.nslds_loan_grade_lvl_4,
1723 cur_isir_int_rec.nslds_loan_xtr_unsbz_ln_f_4,
1724 cur_isir_int_rec.nslds_loan_capital_int_f_4,
1725 cur_isir_int_rec.nslds_loan_seq_num_5,
1726 cur_isir_int_rec.nslds_loan_type_code_5,
1727 cur_isir_int_rec.nslds_loan_chng_f_5,
1728 cur_isir_int_rec.nslds_loan_prog_code_5,
1729 cur_isir_int_rec.nslds_loan_net_amnt_5,
1730 cur_isir_int_rec.nslds_loan_cur_st_code_5,
1731 cur_isir_int_rec.nslds_loan_cur_st_date_5,
1732 cur_isir_int_rec.nslds_loan_agg_pr_bal_5,
1733 cur_isir_int_rec.nslds_loan_out_pr_bal_dt_5,
1734 cur_isir_int_rec.nslds_loan_begin_dt_5,
1735 cur_isir_int_rec.nslds_loan_end_dt_5,
1736 cur_isir_int_rec.nslds_loan_ga_code_5,
1737 cur_isir_int_rec.nslds_loan_cont_type_5,
1738 cur_isir_int_rec.nslds_loan_schol_code_5,
1739 cur_isir_int_rec.nslds_loan_cont_code_5,
1740 cur_isir_int_rec.nslds_loan_grade_lvl_5,
1741 cur_isir_int_rec.nslds_loan_xtr_unsbz_ln_f_5,
1742 cur_isir_int_rec.nslds_loan_capital_int_f_5,
1743 cur_isir_int_rec.nslds_loan_seq_num_6,
1744 cur_isir_int_rec.nslds_loan_type_code_6,
1745 cur_isir_int_rec.nslds_loan_chng_f_6,
1746 cur_isir_int_rec.nslds_loan_prog_code_6,
1747 cur_isir_int_rec.nslds_loan_net_amnt_6,
1748 cur_isir_int_rec.nslds_loan_cur_st_code_6,
1749 cur_isir_int_rec.nslds_loan_cur_st_date_6,
1750 cur_isir_int_rec.nslds_loan_agg_pr_bal_6,
1751 cur_isir_int_rec.nslds_loan_out_pr_bal_dt_6,
1752 cur_isir_int_rec.nslds_loan_begin_dt_6,
1753 cur_isir_int_rec.nslds_loan_end_dt_6,
1754 cur_isir_int_rec.nslds_loan_ga_code_6,
1755 cur_isir_int_rec.nslds_loan_cont_type_6,
1756 cur_isir_int_rec.nslds_loan_schol_code_6,
1757 cur_isir_int_rec.nslds_loan_cont_code_6,
1758 cur_isir_int_rec.nslds_loan_grade_lvl_6,
1759 cur_isir_int_rec.nslds_loan_xtr_unsbz_ln_f_6,
1760 cur_isir_int_rec.nslds_loan_capital_int_f_6,
1761 cur_isir_int_rec.nslds_loan_last_d_amt_1,
1762 cur_isir_int_rec.nslds_loan_last_d_date_1,
1763 cur_isir_int_rec.nslds_loan_last_d_amt_2,
1764 cur_isir_int_rec.nslds_loan_last_d_date_2,
1765 cur_isir_int_rec.nslds_loan_last_d_amt_3,
1766 cur_isir_int_rec.nslds_loan_last_d_date_3,
1767 cur_isir_int_rec.nslds_loan_last_d_amt_4,
1768 cur_isir_int_rec.nslds_loan_last_d_date_4,
1769 cur_isir_int_rec.nslds_loan_last_d_amt_5,
1770 cur_isir_int_rec.nslds_loan_last_d_date_5,
1771 cur_isir_int_rec.nslds_loan_last_d_amt_6,
1772 cur_isir_int_rec.nslds_loan_last_d_date_6,
1773 cur_isir_int_rec.dlp_master_prom_note_flag,
1774 cur_isir_int_rec.subsidized_loan_limit_type,
1775 cur_isir_int_rec.combined_loan_limit_type,
1776 'UNMATCHED',
1777 SYSDATE,
1778 fnd_global.user_id,
1779 fnd_global.user_id,
1780 fnd_global.login_id,
1781 SYSDATE
1782 );
1783
1784 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1785 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_isir.debug','After insert row into igf_ap_isir_ints ');
1786 END IF;
1787 l_isir_data_from_mtch_tbl := TRUE;
1788
1789 END IF;
1790
1791 END IF;
1792
1793 IF l_isir_data_from_mtch_tbl THEN
1794
1798
1795 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1796 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_isir.debug','Before opening the cursor cur_get_rowid_nslds ');
1797 END IF;
1799 -- Delete the NSLDS Data
1800 rec_get_rowid_nslds := null;
1801 OPEN cur_get_rowid_nslds(p_isir_id);
1802 FETCH cur_get_rowid_nslds INTO rec_get_rowid_nslds;
1803 CLOSE cur_get_rowid_nslds;
1804
1805
1806 IF (rec_get_rowid_nslds.row_id IS NOT NULL) THEN
1807 igf_ap_nslds_data_pkg.delete_row(rec_get_rowid_nslds.row_id);
1808 END IF; -- NSLDS
1809
1810 -- Delete the ISIR Data
1811 IF (rec_get_rowid_isir.row_id IS NOT NULL) THEN
1812 igf_ap_isir_matched_pkg.delete_row(rec_get_rowid_isir.row_id);
1813
1814 -- Processing TODO Items. If the ISIR delete is the only ISIR present in system,
1815 --Update the status of ISIR todo Item to "REQUESTED"
1816 OPEN get_isir(p_base_id);
1817 FETCH get_isir INTO l_isir;
1818 IF get_isir%NOTFOUND THEN
1819 CLOSE get_isir;
1820
1821 -- update the status to Requested
1822 FOR todo_items_for_isir_rec IN todo_items_for_isir_cur(p_base_id) LOOP
1823 igf_ap_td_item_inst_pkg.update_row (
1824 x_rowid => todo_items_for_isir_rec.rowid ,
1825 x_base_id => p_base_id ,
1826 x_item_sequence_number => todo_items_for_isir_rec.item_sequence_number,
1827 x_status => 'REQ' ,
1828 x_status_date => todo_items_for_isir_rec.status_date ,
1829 x_add_date => todo_items_for_isir_rec.add_date ,
1830 x_corsp_date => todo_items_for_isir_rec.corsp_date ,
1831 x_corsp_count => todo_items_for_isir_rec.corsp_count ,
1832 x_inactive_flag => todo_items_for_isir_rec.inactive_flag ,
1833 x_freq_attempt => todo_items_for_isir_rec.freq_attempt ,
1834 x_max_attempt => todo_items_for_isir_rec.max_attempt ,
1835 x_required_for_application => todo_items_for_isir_rec.required_for_application,
1836 x_mode => 'R' ,
1837 x_legacy_record_flag => todo_items_for_isir_rec.legacy_record_flag,
1838 x_clprl_id => todo_items_for_isir_rec.clprl_id
1839 );
1840 END LOOP;
1841 ELSE
1842 CLOSE get_isir;
1843 END IF;
1844 END IF; -- ISIR
1845
1846
1847 END IF; -- l_isir_data_from_mtch_tbl
1848
1849
1850 x_return_status := 'S';
1851 fnd_msg_pub.count_and_get(
1852 p_encoded => fnd_api.g_false,
1853 p_count => x_msg_count,
1854 p_data => x_msg_data
1855 );
1856
1857 EXCEPTION
1858
1859 WHEN RETURN_EXP THEN
1860 x_return_status := 'S';
1861 fnd_msg_pub.count_and_get(
1862 p_encoded => fnd_api.g_false,
1863 p_count => x_msg_count,
1864 p_data => x_msg_data
1865 );
1866
1867 WHEN OTHERS THEN
1868
1869 ROLLBACK TO IGFAP47_DELETE_ISIR;
1870
1871 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1872 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.delete_isir.exception','The exception is : ' || SQLERRM );
1873 END IF;
1874 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1875 fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.delete_isir');
1876
1877 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1878 l_err_msg := fnd_message.get||SQLERRM;
1879 fnd_msg_pub.count_and_get(
1880 p_encoded => fnd_api.g_false,
1881 p_count => x_msg_count,
1882 p_data => l_err_msg --x_msg_data
1883 );
1884
1885 END delete_isir;
1886
1887
1888
1889 PROCEDURE delete_person_match (
1890 p_si_id IN NUMBER
1891 ) IS
1892 /*
1893 || Created By : ugummall
1894 || Created On : 05-Aug-2004
1895 || Purpose : This Procedure does the following tasks.
1896 || 1.
1897 || 2.
1898 || 3.
1899 || 4.
1900 || 5.
1901 || Known limitations, enhancements or remarks :
1902 || Change History :
1903 || Who When What
1904 */
1905
1906 -- Cursor to fetch apm_id from person match table of an isir interface record.
1907 CURSOR cur_get_person_match ( cp_si_id igf_ap_person_match_all.si_id%TYPE) IS
1908 SELECT ROWID row_id, apm_id
1909 FROM IGF_AP_PERSON_MATCH_ALL permatch
1910 WHERE permatch.si_id = cp_si_id;
1911
1912 -- Cursor to fetch rowids of child records (match detail records) of person match record.
1913 CURSOR cur_get_match_detail ( cp_apm_id igf_ap_person_match_all.apm_id%TYPE) IS
1914 SELECT ROWID row_id
1915 FROM IGF_AP_MATCH_DETAILS matchdtls
1916 WHERE matchdtls.apm_id = cp_apm_id;
1917
1918 rec_get_person_match cur_get_person_match%ROWTYPE;
1922
1919 rec_get_match_detail cur_get_match_detail%ROWTYPE;
1920
1921 BEGIN
1923 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1924 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_person_match.debug','starting delete_person_match with p_si_id : ' || p_si_id);
1925 END IF;
1926
1927 -- Get APM_ID from SI_ID
1928 rec_get_person_match := null;
1929 OPEN cur_get_person_match(p_si_id);
1930 FETCH cur_get_person_match INTO rec_get_person_match;
1931 CLOSE cur_get_person_match;
1932
1933 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1934 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_person_match.debug','Before for-loop rec_get_match_detail ');
1935 END IF;
1936
1937 -- Delete match detail records (child records)
1938 FOR rec_get_match_detail IN cur_get_match_detail(rec_get_person_match.apm_id) LOOP
1939 igf_ap_match_details_pkg.delete_row(rec_get_match_detail.row_id);
1940 END LOOP;
1941
1942 -- Delete person match record (parent record)
1943 igf_ap_person_match_pkg.delete_row(rec_get_person_match.row_id);
1944
1945 EXCEPTION
1946 WHEN OTHERS THEN
1947 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1948 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.delete_person_match.exception','The exception is : ' || SQLERRM );
1949 END IF;
1950 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1951 fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.delete_person_match');
1952 fnd_file.put_line(fnd_file.log,fnd_message.get);
1953 igs_ge_msg_stack.add;
1954 END delete_person_match;
1955
1956
1957 PROCEDURE delete_interface_record (
1958 p_si_id IN NUMBER,
1959 lv_status OUT NOCOPY VARCHAR2
1960 ) IS
1961 /*
1962 || Created By : ugummall
1963 || Created On : 05-Aug-2004
1964 || Purpose : This Procedure does the following tasks.
1965 || 1. Deletes the record in ISIR interface table.
1966 || 2. Deletes the corresponding match detail records.
1967 || 3. Deletes the corresponding record in person match table.
1968 || Known limitations, enhancements or remarks :
1969 || Change History :
1970 || Who When What
1971 */
1972
1973 -- Cursor to get rowid of the ISIR interface record.
1974 CURSOR cur_get_rowid_interface ( cp_si_id igf_ap_isir_intrface.si_id%TYPE) IS
1975 SELECT ROWID row_id
1976 FROM IGF_AP_ISIR_INTS_ALL intface
1977 WHERE intface.si_id = cp_si_id;
1978
1979 rec_get_rowid_interface cur_get_rowid_interface%ROWTYPE;
1980
1981 BEGIN
1982
1983 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1984 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_interface_record.debug','starting delete_interface_record with p_si_id : ' || p_si_id);
1985 END IF;
1986
1987 -- get row id of the ISIR interface record to be deleted.
1988 OPEN cur_get_rowid_interface(p_si_id);
1989 FETCH cur_get_rowid_interface INTO rec_get_rowid_interface;
1990 IF (cur_get_rowid_interface%NOTFOUND) THEN
1991 CLOSE cur_get_rowid_interface;
1992 lv_status := 'E';
1993 RETURN;
1994 END IF;
1995 CLOSE cur_get_rowid_interface;
1996
1997 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1998 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_interface_record.debug','Before deleting record from isir_ints table and person match table');
1999 END IF;
2000
2001 -- delete the interface record.
2002 --igf_ap_isir_intrface_pkg.delete_row(rec_get_rowid_interface.row_id);
2003 DELETE IGF_AP_ISIR_INTS_ALL WHERE si_id = p_si_id;
2004
2005 -- delete person match record and match details records
2006 delete_person_match(p_si_id => p_si_id);
2007
2008 lv_status := 'S';
2009
2010 EXCEPTION
2011 WHEN OTHERS THEN
2012 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2013 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.delete_interface_person_match.exception','The exception is : ' || SQLERRM );
2014 END IF;
2015 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2016 fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.delete_interface_person_match');
2017 fnd_file.put_line(fnd_file.log,fnd_message.get);
2018 igs_ge_msg_stack.add;
2019 lv_status := 'E';
2020 END delete_interface_record;
2021
2022
2023 PROCEDURE delete_int_records (
2024 p_si_ids IN VARCHAR2
2025 ) IS
2026 /*
2027 || Created By : ugummall
2028 || Created On : 05-Aug-2004
2029 || Purpose : This Procedure does the following tasks.
2030 || 1. Deletes the record in ISIR interface table.
2031 || 2. Deletes the corresponding match detail records.
2032 || 3. Deletes the corresponding record in person match table.
2033 || Known limitations, enhancements or remarks :
2034 || Change History :
2035 || Who When What
2036 */
2037
2038 l_del_si_id VARCHAR2(10);
2039 l_si_id VARCHAR2(10);
2040 l_si_ids VARCHAR2(1000);
2041 lv_status VARCHAR2(2);
2042
2043 BEGIN
2044
2045 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2046 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_int_records.debug','starting delete_int_records with p_si_ids : ' || p_si_ids);
2047 END IF;
2048
2049 l_si_ids := p_si_ids;
2050
2054 l_si_id := TRIM(SUBSTR(l_si_ids, 1, INSTR(l_si_ids, '*') - 1));
2051 LOOP
2052
2053 l_si_ids := TRIM(SUBSTR(l_si_ids, INSTR(l_si_ids, '*') + 1, LENGTH(l_si_ids)));
2055 l_si_ids := TRIM(SUBSTR(l_si_ids, INSTR(l_si_ids, ',') + 1, LENGTH(l_si_ids)));
2056
2057 IF (l_si_id IS NULL) THEN
2058 l_del_si_id := l_si_ids;
2059 ELSE
2060 l_del_si_id := l_si_id;
2061 END IF;
2062
2063 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2064 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_int_records.debug','Calling delete_interface_record with the l_del_si_id '|| l_del_si_id);
2065 END IF;
2066
2067 delete_interface_record( p_si_id => l_del_si_id, lv_status => lv_status);
2068
2069 IF (l_si_id IS NULL) THEN
2070 EXIT; -- exit from loop.
2071 END IF;
2072
2073 END LOOP;
2074
2075 END delete_int_records;
2076
2077
2078 PROCEDURE is_isir_exists (
2079 p_si_id IN NUMBER,
2080 p_batch_year IN NUMBER,
2081 p_status OUT NOCOPY VARCHAR2
2082 ) IS
2083 /*
2084 || Created By : rasahoo
2085 || Created On :
2086 || Purpose : This Procedure does the following tasks.
2087 || 1. Checks whether student has any ISIR present in ISIR Matched table
2088 || with the primary attributes of the student ISIR
2089 || If it finds then returns Y else return N
2090 || Known limitations, enhancements or remarks :
2091 || Change History :
2092 || Who When What
2093 */
2094
2095 CURSOR cur_isir_exists (cp_si_id NUMBER, cp_batch_year NUMBER) IS
2096 SELECT 1
2097 FROM igf_ap_isir_matched iim,
2098 igf_ap_isir_ints_all isir
2099 WHERE iim.original_ssn = isir.original_ssn_txt
2100 AND iim.orig_name_id = isir.orig_name_id_txt
2101 AND iim.system_record_type = 'ORIGINAL'
2102 AND isir.si_id = cp_si_id
2103 AND iim.batch_year = cp_batch_year;
2104
2105 rec_isir_exists cur_isir_exists%ROWTYPE;
2106
2107 BEGIN
2108
2109 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2110 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.is_isir_exists.debug','starting is_isir_exists with p_si_id : ' || p_si_id ||' p_batch_year : '|| p_batch_year);
2111 END IF;
2112
2113 -- Checks whether student has any ISIR present in ISIR Matched table with the primary attributes of the student ISIR
2114 OPEN cur_isir_exists(p_si_id,p_batch_year);
2115 FETCH cur_isir_exists INTO rec_isir_exists;
2116 IF (cur_isir_exists%NOTFOUND) THEN
2117 p_status := 'N';
2118 CLOSE cur_isir_exists;
2119 ELSE
2120 p_status := 'Y';
2121 CLOSE cur_isir_exists;
2122 END IF;
2123
2124 END is_isir_exists;
2125
2126
2127 FUNCTION is_awards_exists (
2128 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE
2129 ) RETURN VARCHAR2 IS
2130 /*
2131 || Created By : rasahoo
2132 || Created On :
2133 || Purpose : This Procedure does the following tasks.
2134 || 1. check if the student has already some awards then return 'A'
2135 || 2. check if non-simulated awards exists for the student.
2136 || Known limitations, enhancements or remarks :
2137 || Change History :
2138 || Who When What
2139 */
2140
2141 -- Cursor to check wether the student has any non-simulated awards.
2142 CURSOR cur_stud_non_simulated_awards( cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE ) IS
2143 SELECT 'X'
2144 FROM IGF_AW_AWARD_ALL awards
2145 WHERE awards.base_id = cp_base_id
2146 AND awards.award_status <> 'SIMULATED';
2147
2148 lv_dummy VARCHAR2(1);
2149
2150 BEGIN
2151
2152
2153 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2154 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.is_awards_exists.debug','starting is_awards_exists with p_base_id : ' || p_base_id);
2155 END IF;
2156
2157 -- validate the input parameters
2158 IF p_base_id IS NULL THEN
2159 RETURN 'N';
2160 END IF;
2161
2162 -- check if the student has already some awards then return 'A'
2163 -- check if non-simulated awards exists for the student.
2164 lv_dummy := null;
2165 OPEN cur_stud_non_simulated_awards(p_base_id);
2166 FETCH cur_stud_non_simulated_awards INTO lv_dummy;
2167 CLOSE cur_stud_non_simulated_awards;
2168 IF (lv_dummy = 'X') THEN
2169 RETURN 'A';
2170 END IF;
2171
2172 RETURN 'N';
2173
2174 EXCEPTION
2175 WHEN OTHERS THEN
2176 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2177 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.is_awards_exists.exception','The exception is : ' || SQLERRM );
2178 END IF;
2179 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2180 fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.is_awards_exists');
2181 fnd_file.put_line(fnd_file.log,fnd_message.get);
2182 igs_ge_msg_stack.add;
2183 RETURN 'N';
2184 END is_awards_exists;
2185
2186
2187 FUNCTION chk_pell_orig (
2188 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
2189 p_isir_id IN igf_ap_isir_matched_all.isir_id%TYPE
2190 ) RETURN VARCHAR2 IS
2191 /*
2192 || Created By : rasahoo
2196 || current Payment ISIR transaction number If not same, it returns 'P' else return 'N'
2193 || Created On :
2194 || Purpose : This Procedure does the following tasks.
2195 || 1. Checks whether the Pell Origination transaction number is same as the
2197 || Known limitations, enhancements or remarks :
2198 || Change History :
2199 || Who When What
2200 */
2201
2202 -- Cursor to check wether the student has any non-simulated awards.
2203 CURSOR cur_pell_orig_chk(
2204 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
2205 cp_isir_id igf_ap_isir_matched_all.isir_id%TYPE
2206 ) IS
2207 SELECT
2208 isir.transaction_num isir_trans,
2209 rfms.transaction_num rfms_trans
2210 FROM igf_ap_isir_matched_all isir, igf_gr_rfms_all rfms
2211 WHERE isir.base_id = rfms.base_id
2212 AND isir.base_id = cp_base_id
2213 AND isir.isir_id = cp_isir_id ;
2214
2215 lv_pell_orig_rec cur_pell_orig_chk%ROWTYPE;
2216
2217 BEGIN
2218
2219 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2220 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.chk_pell_orig.debug','starting chk_pell_orig with p_base_id : ' || p_base_id ||' p_isir_id : '|| p_isir_id);
2221 END IF;
2222
2223 -- validate the input parameters
2224 IF (p_base_id IS NULL OR p_isir_id IS NULL) THEN
2225 RETURN 'N';
2226 END IF;
2227
2228 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2229 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.chk_pell_orig.debug','Before calling cur_pell_orig_chk ');
2230 END IF;
2231
2232 -- if the PELL origination isir is different from the selected ISIR then return 'P'
2233 lv_pell_orig_rec := null;
2234 OPEN cur_pell_orig_chk(p_base_id, p_isir_id);
2235 FETCH cur_pell_orig_chk INTO lv_pell_orig_rec;
2236 IF cur_pell_orig_chk%FOUND THEN
2237 CLOSE cur_pell_orig_chk;
2238
2239 -- Raise the message only if the RFMS Transaction Number is different
2240 -- from the current Payment ISIR Transaction Number.
2241 IF lv_pell_orig_rec.isir_trans <> NVL(lv_pell_orig_rec.rfms_trans,-1) THEN
2242 RETURN 'P';
2243 ELSE
2244 RETURN 'N';
2245 END IF;
2246 ELSE
2247 CLOSE cur_pell_orig_chk;
2248 RETURN 'N';
2249 END IF;
2250
2251 EXCEPTION
2252 WHEN OTHERS THEN
2253 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2254 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.chk_pell_orig.exception','The exception is : ' || SQLERRM );
2255 END IF;
2256 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2257 fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.chk_pell_orig');
2258 fnd_file.put_line(fnd_file.log,fnd_message.get);
2259 igs_ge_msg_stack.add;
2260 RETURN 'N';
2261 END chk_pell_orig;
2262
2263
2264 FUNCTION make_awarding_isir (
2265 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
2266 p_isir_id IN igf_ap_isir_matched_all.isir_id%TYPE
2267 ) RETURN VARCHAR2 IS
2268 /*
2269 || Created By : rasahoo
2270 || Created On :
2271 || Purpose : This Procedure does the following tasks.
2272 || 1. Marks the given ISIR ID as both Awarding and Payment ISIR
2273 || 2. Un-Marks all other ISIRs for the given baseid as NON-Payment and NON-Awarding.
2274 || Known limitations, enhancements or remarks :
2275 || Change History :
2276 || Who When What
2277 */
2278
2279 -- Get all the ISIRs of the given person for marking it as Awarding and Payment
2280 CURSOR cur_isir_matched (
2281 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
2282 ) IS
2283 SELECT *
2284 FROM igf_ap_isir_matched m
2285 WHERE m.base_id = cp_base_id;
2286
2287 lv_dummy VARCHAR2(1);
2288
2289 BEGIN
2290
2291 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2292 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.make_awarding_isir.debug','starting make_awarding_isir with p_base_id : ' || p_base_id ||' p_isir_id : '|| p_isir_id);
2293 END IF;
2294
2295 -- validate the input parameters
2296 IF (p_base_id IS NULL OR p_isir_id IS NULL) THEN
2297 RETURN 'N';
2298 END IF;
2299
2300 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2301 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.make_awarding_isir.debug','Looping the cursor cur_isir_matched');
2302 END IF;
2303
2304 -- if no errors, then make the current ISIR as both Awarding and Payment
2305 -- then set status to N for the existing payment ISIR and Awarding ISIRs.
2306 FOR cur_isir_matched_rec IN cur_isir_matched(p_base_id) LOOP
2307
2308 IF cur_isir_matched_rec.isir_id = p_isir_id THEN
2309 cur_isir_matched_rec.payment_isir := 'Y';
2310 cur_isir_matched_rec.active_isir := 'Y';
2311 ELSE
2312 cur_isir_matched_rec.payment_isir := 'N';
2313 cur_isir_matched_rec.active_isir := 'N';
2314 END IF;
2315
2316 -- Call the Update_row of using rowtype present in the TBH
2317 igf_ap_isir_matched_pkg.update_row_rectype(cur_isir_matched_rec);
2318
2319 END LOOP;
2320
2321 RETURN 'Y';
2322
2323
2324 EXCEPTION
2325 WHEN OTHERS THEN
2326 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2330 fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.make_awarding_isir');
2327 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.make_awarding_isir.exception','The exception is : ' || SQLERRM );
2328 END IF;
2329 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2331 fnd_file.put_line(fnd_file.log,fnd_message.get);
2332 igs_ge_msg_stack.add;
2333 RETURN 'N';
2334 END make_awarding_isir;
2335
2336
2337 FUNCTION get_isir_message_class (
2338 p_message_class IN igf_ap_isir_matched_all.message_class_txt%TYPE
2339 ) RETURN VARCHAR2 IS
2340 /*
2341 || Created By : brajendr
2342 || Created On : 04-AUG-2004
2343 || Purpose : Returns ISIR Type meaning for the given Message Class
2344 || If the message class is NULL, then it will return NULL else return the ISIR Type description.
2345 || Known limitations, enhancements or remarks :
2346 || Change History :
2347 || Who When What
2348 || (reverse chronological order - newest change first)
2349 */
2350
2351 -- Get
2352 CURSOR get_message_class(
2353 cp_message_class igf_ap_isir_matched_all.message_class_txt%TYPE
2354 ) IS
2355 SELECT tlkp.meaning isir_type_desc, tlkp.lookup_code isir_type
2356 FROM igf_lookups_view mlkp, igf_lookups_view tlkp
2357 WHERE mlkp.enabled_flag = 'Y'
2358 AND tlkp.enabled_flag = 'Y'
2359 AND mlkp.lookup_type = 'IGF_AP_ISIR_MESSAGE_CLASS'
2360 AND tlkp.lookup_type = 'IGF_AP_ISIR_TYPE'
2361 AND mlkp.tag = tlkp.lookup_code
2362 AND mlkp.lookup_code = cp_message_class;
2363
2364 get_message_class_rec get_message_class%ROWTYPE;
2365
2366 BEGIN
2367
2368 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2369 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.get_isir_message_class.debug','starting get_isir_message_class with p_message_class : ' || p_message_class);
2370 END IF;
2371
2372 -- if the message class is NULL then return NULL
2373 -- else get the ISIR type using the tag provided at the lookup code.
2374 IF p_message_class IS NULL THEN
2375 RETURN NULL;
2376 END IF;
2377
2378 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2379 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.get_isir_message_class.debug','Before opening the cursor get_message_class');
2380 END IF;
2381
2382 get_message_class_rec := NULL;
2383 OPEN get_message_class(p_message_class);
2384 FETCH get_message_class INTO get_message_class_rec;
2385 CLOSE get_message_class;
2386
2387 RETURN get_message_class_rec.isir_type_desc;
2388
2389 END get_isir_message_class;
2390
2391
2392 PROCEDURE upd_ant_data_awd_prc_status(
2393 p_old_active_isir_id IN igf_ap_isir_matched_all.isir_id%TYPE,
2394 p_new_active_isir_id IN igf_ap_isir_matched_all.isir_id%TYPE,
2395 p_upd_ant_val IN VARCHAR2,
2396 p_anticip_status OUT NOCOPY VARCHAR2,
2397 p_awd_prc_status OUT NOCOPY VARCHAR2
2398 ) AS
2399 ------------------------------------------------------------------
2400 --Created by : brajendr, Oracle India
2401 --Date created:
2402 --
2403 --Purpose:
2404 --
2405 --
2406 --Known limitations/enhancements and/or remarks:
2407 --
2408 --Change History:
2409 --Who When What
2410 -------------------------------------------------------------------
2411
2412 -- Get the ISIR details of the student.
2413 CURSOR cur_isir_data( cp_isir_id igf_ap_isir_matched_all.isir_id%TYPE ) IS
2414 SELECT isir_id, base_id, batch_year, summ_enrl_status, grade_level_in_college, first_house_plan, second_house_plan, third_house_plan,
2415 fourth_house_plan, fifth_house_plan, sixth_house_plan, fedral_schl_code_indicator, primary_efc, primary_alternate_month_1,
2416 primary_alternate_month_2, primary_alternate_month_3, primary_alternate_month_4, primary_alternate_month_5, primary_alternate_month_6,
2417 primary_alternate_month_7, primary_alternate_month_8, primary_alternate_month_10, primary_alternate_month_11, primary_alternate_month_12
2418 FROM igf_ap_isir_matched_all
2419 WHERE isir_id = cp_isir_id;
2420
2421 new_active_isir cur_isir_data%ROWTYPE;
2422 old_active_isir cur_isir_data%ROWTYPE;
2423
2424 -- Get the anticipated data for the student.
2425 CURSOR c_ant_data(
2426 cp_base_id igf_ap_fa_ant_data.base_id%TYPE
2427 ) IS
2428 SELECT rowid row_id, a.*
2429 FROM igf_ap_fa_ant_data a
2430 WHERE base_id = cp_base_id;
2431
2432 lc_ant_data c_ant_data%ROWTYPE;
2433
2434 -- Derive the attendance type fromt he setup with the ISIR data.
2435 CURSOR c_attendance_type(
2436 cp_batch_year igf_ap_isir_matched_all.batch_year%TYPE,
2437 cp_summ_enrl_status igf_ap_isir_matched_all.summ_enrl_status%TYPE
2438 ) IS
2439 SELECT atm.attendance_type, atm.ap_att_code
2440 FROM igf_ap_attend_map_v atm, igf_ap_batch_aw_map_all bam
2441 WHERE atm.cal_type = bam.ci_cal_type
2442 AND atm.sequence_number = bam.ci_sequence_number
2443 AND bam.batch_year = cp_batch_year
2444 AND atm.ap_att_code = cp_summ_enrl_status;
2445
2446 lc_attendance_type c_attendance_type%ROWTYPE;
2447
2448 -- Derive the class standing from the ISIR Grade level.
2452 WHERE ap_std_code = cp_grade_level_in_college;
2449 CURSOR c_class_standing( cp_grade_level_in_college igf_ap_isir_matched_all.grade_level_in_college%TYPE ) IS
2450 SELECT class_standing, ap_std_code
2451 FROM igf_ap_class_std_map_v
2453
2454 lc_class_standing c_class_standing%ROWTYPE;
2455
2456 -- Derive the Attendace mode based on the housing code
2457 CURSOR c_attendance_mode(
2458 cp_batch_year igf_ap_isir_matched_all.batch_year%TYPE,
2459 cp_first_house_plan igf_ap_isir_matched_all.first_house_plan%TYPE,
2460 cp_second_house_plan igf_ap_isir_matched_all.second_house_plan%TYPE,
2461 cp_third_house_plan igf_ap_isir_matched_all.third_house_plan%TYPE,
2462 cp_fourth_house_plan igf_ap_isir_matched_all.fourth_house_plan%TYPE,
2463 cp_fifth_house_plan igf_ap_isir_matched_all.fifth_house_plan%TYPE,
2464 cp_sixth_house_plan igf_ap_isir_matched_all.sixth_house_plan%TYPE,
2465 cp_fedral_schl_code_indicator igf_ap_isir_matched_all.fedral_schl_code_indicator%TYPE
2466 ) IS
2467 SELECT housing_stat_code, ap_house_plan_code
2468 FROM igf_ap_housing_map hm, igf_ap_batch_aw_map_all bam
2469 WHERE hm.ci_cal_type = bam.ci_cal_type
2470 AND hm.ci_sequence_number = bam.ci_sequence_number
2471 AND bam.batch_year = cp_batch_year
2472 AND hm.AP_HOUSE_PLAN_CODE = DECODE(cp_fedral_schl_code_indicator, 1, cp_first_house_plan,
2473 2, cp_second_house_plan,
2474 3, cp_third_house_plan,
2475 4, cp_fourth_house_plan,
2476 5, cp_fifth_house_plan,
2477 6, cp_sixth_house_plan,
2478 '**');
2479
2480 lc_attendance_mode c_attendance_mode%ROWTYPE;
2481
2482 BEGIN
2483
2484 IF p_new_active_isir_id IS NULL THEN
2485
2486 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2487 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status.debug','One of the input parameter is null');
2488 END IF;
2489
2490 p_anticip_status := 'ERROR';
2491 p_awd_prc_status := 'ERROR';
2492 RETURN;
2493 END IF;
2494
2495 -- Get New Active ISIR details
2496 new_active_isir := NULL;
2497 OPEN cur_isir_data(p_new_active_isir_id);
2498 FETCH cur_isir_data INTO new_active_isir;
2499 CLOSE cur_isir_data;
2500
2501 IF p_upd_ant_val = 'Y' THEN
2502
2503 -- Update the Anticipated data
2504 lc_ant_data := NULL;
2505 OPEN c_ant_data(new_active_isir.base_id);
2506 FETCH c_ant_data INTO lc_ant_data;
2507
2508 -- If there is a difference in the anticipated data then
2509 IF c_ant_data%FOUND THEN
2510 CLOSE c_ant_data;
2511
2512 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2513 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status.debug','Anticipated data was found, hence updating');
2514 END IF;
2515
2516 -- Derive the Attendance Type value and compare the the anticipated value.
2517 lc_attendance_type := NULL;
2518 OPEN c_attendance_type(new_active_isir.batch_year, new_active_isir.summ_enrl_status);
2519 FETCH c_attendance_type INTO lc_attendance_type;
2520 IF c_attendance_type%NOTFOUND THEN
2521 fnd_message.set_name('IGF','IGF_AW_ATTEND_TYPE_NOT_DEF');
2522 fnd_message.set_token('STATUS',new_active_isir.summ_enrl_status);
2523 fnd_file.put_line(fnd_file.log,fnd_message.get);
2524 END IF;
2525 CLOSE c_attendance_type;
2526
2527 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2528 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status.debug','Derived the attendance Type : '||lc_attendance_type.attendance_type);
2529 END IF;
2530
2531 -- Get the Class standing using the ISIR grde level
2532 lc_class_standing := NULL;
2533 OPEN c_class_standing(new_active_isir.grade_level_in_college);
2534 FETCH c_class_standing INTO lc_class_standing;
2535 IF c_class_standing%NOTFOUND THEN
2536 fnd_message.set_name('IGF','IGF_AW_CLASS_STANDING_NOT_DEF');
2537 fnd_message.set_token('GD_LEVEL',new_active_isir.grade_level_in_college);
2538 fnd_file.put_line(fnd_file.log,fnd_message.get);
2539 END IF;
2540 CLOSE c_class_standing;
2541
2542 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2543 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status.debug','Derived the Class standing : '||lc_class_standing.class_standing);
2544 END IF;
2545
2546 -- Get the Attendance Mode based on the housing plan codes and federal school code.
2547 lc_attendance_mode := NULL;
2548 OPEN c_attendance_mode(
2549 new_active_isir.batch_year,
2550 new_active_isir.first_house_plan,
2551 new_active_isir.second_house_plan,
2552 new_active_isir.third_house_plan,
2553 new_active_isir.fourth_house_plan,
2554 new_active_isir.fifth_house_plan,
2555 new_active_isir.sixth_house_plan,
2559 IF c_attendance_mode%FOUND THEN
2556 new_active_isir.fedral_schl_code_indicator
2557 );
2558 FETCH c_attendance_mode INTO lc_attendance_mode;
2560 lc_ant_data.housing_status_code := lc_attendance_mode.housing_stat_code;
2561 ELSE
2562 fnd_message.set_name('IGF','IGF_AW_ATTEND_MODE_NOT_DEF');
2563 fnd_message.set_token('H_PLAN',new_active_isir.fedral_schl_code_indicator);
2564 fnd_file.put_line(fnd_file.log,fnd_message.get);
2565 END IF;
2566 CLOSE c_attendance_mode;
2567
2568 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2569 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status.debug','Derived the housing status : '||lc_attendance_mode.housing_stat_code);
2570 END IF;
2571
2572 IF lc_class_standing.class_standing IS NOT NULL OR
2573 lc_attendance_type.attendance_type IS NOT NULL OR
2574 lc_attendance_mode.housing_stat_code IS NOT NULL THEN
2575
2576 -- Update the anticipated data with the derived values for all anticipated records of a student.
2577 FOR lfc_ant_data IN c_ant_data(new_active_isir.base_id) LOOP
2578
2579 IF lc_class_standing.class_standing IS NOT NULL THEN
2580 lfc_ant_data.class_standing := lc_class_standing.class_standing;
2581 END IF;
2582
2583 IF lc_attendance_type.attendance_type IS NOT NULL THEN
2584 lfc_ant_data.attendance_type := lc_attendance_type.attendance_type;
2585 END IF;
2586
2587 IF lc_attendance_mode.housing_stat_code IS NOT NULL THEN
2588 lfc_ant_data.housing_status_code := lc_attendance_mode.housing_stat_code;
2589 END IF;
2590
2591 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2592 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status.debug','Updating Anticipated data for base_id : '|| lfc_ant_data.base_id);
2593 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status.debug',' ld_cal_type : '|| lfc_ant_data.ld_cal_type ||' Ld_seq_num : '||lfc_ant_data.ld_sequence_number);
2594 END IF;
2595
2596 igf_ap_fa_ant_data_pkg.update_row(
2597 x_rowid => lfc_ant_data.row_id,
2598 x_base_id => lfc_ant_data.base_id,
2599 x_ld_cal_type => lfc_ant_data.ld_cal_type,
2600 x_ld_sequence_number => lfc_ant_data.ld_sequence_number,
2601 x_org_unit_cd => lfc_ant_data.org_unit_cd,
2602 x_program_type => lfc_ant_data.program_type,
2603 x_program_location_cd => lfc_ant_data.program_location_cd,
2604 x_program_cd => lfc_ant_data.program_cd,
2605 x_class_standing => lfc_ant_data.class_standing,
2606 x_residency_status_code => lfc_ant_data.residency_status_code,
2607 x_housing_status_code => lfc_ant_data.housing_status_code,
2608 x_attendance_type => lfc_ant_data.attendance_type,
2609 x_attendance_mode => lfc_ant_data.attendance_mode,
2610 x_months_enrolled_num => lfc_ant_data.months_enrolled_num,
2611 x_credit_points_num => lfc_ant_data.credit_points_num,
2612 x_mode => 'R'
2613 );
2614 END LOOP;
2615
2616 p_anticip_status := 'SUCCESS';
2617 fnd_message.set_name('IGF','IGF_AW_ANTICIP_DATA_UPDATED');
2618 fnd_file.put_line(fnd_file.log,fnd_message.get);
2619
2620 END IF;
2621
2622 ELSE
2623 p_anticip_status := 'NO_DATA';
2624 CLOSE c_ant_data;
2625 END IF;
2626
2627 END IF; -- p_upd_ant_val
2628
2629 -- Check for the EFC values for everymonth and if not same then update the award process status of the awards
2630 IF p_old_active_isir_id IS NOT NULL THEN
2631
2632 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2633 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status.debug','Old Active ISIR is present : '||p_old_active_isir_id);
2634 END IF;
2635
2636 old_active_isir := NULL;
2637 OPEN cur_isir_data(p_old_active_isir_id);
2638 FETCH cur_isir_data INTO old_active_isir;
2639 CLOSE cur_isir_data;
2640
2641 IF ( NVL(old_active_isir.primary_efc,-1) <> NVL(new_active_isir.primary_efc,-1) OR
2642 NVL(old_active_isir.primary_alternate_month_1,-1) <> NVL(new_active_isir.primary_alternate_month_1,-1) OR
2643 NVL(old_active_isir.primary_alternate_month_2,-1) <> NVL(new_active_isir.primary_alternate_month_2,-1) OR
2644 NVL(old_active_isir.primary_alternate_month_3,-1) <> NVL(new_active_isir.primary_alternate_month_3,-1) OR
2645 NVL(old_active_isir.primary_alternate_month_4,-1) <> NVL(new_active_isir.primary_alternate_month_4,-1) OR
2646 NVL(old_active_isir.primary_alternate_month_5,-1) <> NVL(new_active_isir.primary_alternate_month_5,-1) OR
2647 NVL(old_active_isir.primary_alternate_month_6,-1) <> NVL(new_active_isir.primary_alternate_month_6,-1) OR
2648 NVL(old_active_isir.primary_alternate_month_7,-1) <> NVL(new_active_isir.primary_alternate_month_7,-1) OR
2649 NVL(old_active_isir.primary_alternate_month_8,-1) <> NVL(new_active_isir.primary_alternate_month_8,-1) OR
2650 NVL(old_active_isir.primary_alternate_month_10,-1) <> NVL(new_active_isir.primary_alternate_month_10,-1) OR
2651 NVL(old_active_isir.primary_alternate_month_11,-1) <> NVL(new_active_isir.primary_alternate_month_11,-1) OR
2652 NVL(old_active_isir.primary_alternate_month_12,-1) <> NVL(new_active_isir.primary_alternate_month_12,-1)
2653 ) THEN
2654
2655 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2656 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status.debug','EFC values were different, hence upadate the Awd process status');
2657 END IF;
2658
2659 p_awd_prc_status := NULL;
2660 p_awd_prc_status := igf_aw_coa_gen.set_awd_proc_status( p_base_id => new_active_isir.base_id,
2661 p_award_prd_code => NULL);
2662
2663 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2664 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status.debug','after updating Awd process status : '||p_awd_prc_status);
2665 END IF;
2666
2667 IF p_awd_prc_status IS NOT NULL THEN
2668 fnd_message.set_name('IGF','IGF_AW_AWD_PRC_STATUS_CHNGED');
2669 fnd_message.set_token('STATUS',igf_aw_gen.lookup_desc('IGF_AW_AWD_PROC_STAT',p_awd_prc_status));
2670 fnd_file.put_line(fnd_file.log,fnd_message.get);
2671 END IF;
2672
2673 END IF;
2674 END IF;
2675
2676 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2677 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status.debug',' -- Successfull returning with p_anticip_status : '||p_anticip_status ||' p_awd_prc_status : '||p_awd_prc_status);
2678 END IF;
2679
2680 RETURN;
2681
2682 EXCEPTION
2683 WHEN others THEN
2684 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2685 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status.exception','The exception is : ' || SQLERRM );
2686 END IF;
2687 p_anticip_status := 'ERROR';
2688 p_awd_prc_status := 'ERROR';
2689 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2690 fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status'||SQLERRM);
2691 fnd_file.put_line(fnd_file.log,fnd_message.get);
2692 igs_ge_msg_stack.add;
2693 END upd_ant_data_awd_prc_status;
2694
2695 END igf_ap_isir_gen_pkg;