[Home] [Help]
PACKAGE BODY: APPS.IGF_AP_BATCH_VER_PRC_PKG
Source
1 PACKAGE BODY IGF_AP_BATCH_VER_PRC_PKG AS
2 /* $Header: IGFAP08B.pls 120.3 2006/03/07 23:25:33 veramach ship $ */
3
4 /*=======================================================================+
5 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA|
6 | All rights reserved. |
7 +=======================================================================+
8 | |
9 | DESCRIPTION |
10 | PL/SQL body for package: IGF_AP_BATCH_VER_PRC_PKG |
11 | |
12 | NOTES |
13 | This process does the auto verification for student records |
14 | The Verification Request Process generates the request for documents |
15 | from the student. This process will take all the info submited by the |
16 | student and see if the information present in ISIR matches with the |
17 | info presented by the student. |
18 | |
19 | |
20 | HISTORY |
21 | |
22 | who when what
23 | svuppala 14-OCt-04 Bug # 3416936 Modified TBH call to addeded field |
24 | Eligible for Additional Unsubsidized Loans |
25 | |
26 | veramach 28-Jan-2004 Bug # 3405556 Obsoleted ins_verification_number.Added dhs_verification_num_txt
27 | bkkumar 15-Dec-2003 Bug# 3240804 Removed the code to update the |
28 | fed_verif_Status to CORRSENT |
29 | bkkumar 10-Dec-2003 Bug# 3240804 Added update_fed_verif_status procedure
30 | |
31 | brajendr 17-NOV-2003 FA 128 - ISIR update 2004-05 |
32 | Corrected the issue for creating correction ISIR
33 | |
34 | rasahoo 17-NOV-2003 FA 128 - ISIR update 2004-05 |
35 | added parameter award_fmly_contribution_type |
36 | to TBH call igf_ap_fa_base_rec_pkg |
37 | Added TBH impact of igf_ap_isir_matched |
38 | |
39 | cdcruz 26-SEP-2003 FA 121 - Verification Worksheet. |
40 | Changes per the TD have been incoporated |
41 | EFC Computation is added while creating |
42 | correction records |
43 | ugummall 26-SEP-2003 FA 126 - Multiple FA Offices. |
44 | added new parameter assoc_org_num to TBH call |
45 | igf_ap_fa_base_rec_pkg.update_row w.r.t. FA126|
46 | |
47 | bkkumar 27-Aug-2003 Bug# 3071157 Added explicit date format mask |
48 | to the to_date() function. |
49 | gmuralid 10-03-2003 BUG# 2829487 .In procedure update process |
50 | status, inactive flag check in cursor cur_todo|
51 | was modified. |
52 | |
53 | brajendr 03-Mar-2003 Bug # 2822497 |
54 | Added the validations for Current SSN when |
55 | Pell Origination is Alread Sent |
56 | |
57 | rasingh 4-Feb-2003 Build FACR105 EFC Enhancements. |
58 | Correction ISIR updated with the values of |
59 | correction items. If correction ISIR not |
60 | present, then it is created. |
61 | |
62 | rasingh 25-Nov-02 Build:2613576 FACR107/FA113/FA103 Related Build. |
63 | All Verification Status Removed except: Accurate and|
64 | Correction Sent |
65 | |
66 | Bug 2637505 - DEV LINE Fix of Bugs on MNT Line. |
67 | Please look in the bug for details of the fix. |
68 | update_fa_status - pv_verification_status passed to |
69 | update of igf_ap_fa_base_rec. |
70 | l_chk_dup_corr - Another cursor added to check if any record is already
71 | present with the new correction status evaluated. |
72 | main: Logic to derive verification status modified. |
73 | Bug 2606001,2613546 |
74 | sjadhav Oct.28.2002 |
75 | Added l_pell_mat to igf_gr_pell.pell_calc routine |
76 | Added x_pell_alt_expense in igf_ap_fa_base_rec_pkg |
77 | Modified get_gr_ver_code to read mapping from igf_aw_int_ext_map table|
78 |-----------------------------------------------------------------------|
79 | 12-Jun-2002 Bug ID: 2402371/ 2403886. |
80 |The initialization of cursor variable dbms_sql.open_cursor removed from|
81 |declaration section and moved to main body as it was failing when more |
82 |cursor was required to be open more than once. |
83 |EXECUTE IMMEDIATE Directly used instead of DBMS_SQL |
84 | Bug ID : 1818617 |
85 | who when what |
86 |-----------------------------------------------------------------------
87 | bkkumar 02-July-2003 Bug 3004841 |
88 | Modified function l_chk_dup_corr |
89 | rasahoo 11-June-2003 bug# 2858504 added parameter |
90 | x_legacy_record_flag to insert row |
91 | cdcruz 03-mar-2003 Bug # 2824774. Active ISIR not getting set |
92 | smvk 11-feb-2003 Bug # 2758812. Modified procedure |
93 | get_gr_ver_code. |
94 | rasingh 27-Sept-2002 Build 2590748 To Do Enhancements |
95 | sjadhav 24-jul-2001 added parameter p_get_recent_info |
96 | rasingh 9-May-2002 Verification Status 'CALCULATED' added.
97 | Logic added to compare the aid based on Pell Matix. |
98 | 15-May-01 Rakesh Bug ID: 1776927 and 1776735 : |
99 | Changes: There was an error in the way in which the count was passes |
100 | thru the loop. This was fixed. |
101 | 15-May01 Rakesh Bug ID: 1779453 |
102 | Changes: Base_id and Award Year added to the input parameter list. |
103 | 03-July-01 kkillams Bug ID: 17794114 |
104 | Changes: Creation of correction records based on group tolerance |
105 | amount and updation of wavie flag |
106 |-----------------------------------------------------------------------|
107 |-----------------------------------------------------------------------|
108 | 24-Oct-2001 skoppula BugID :2061146
109 | Process of creating correction records is changed.If a correction
110 | exists is "NotReady" status for an ISIR against a SAR field then the
111 | corrected value is updated.Else if records exist in other states a
112 | correction record with "NotReady" status is created |
113 | |
114 | gvarapra 14-sep-2004 FA138 - ISIR Enhancements |
115 | Added arguments in call to |
116 | IGF_AP_ISIR_MATCHED_PKG. |
117 | Added arguments in call to |
118 | IGF_AP_FA_BASE_RECORD_PKG. |
119 *=======================================================================*/
120
121 l_new_corr_status igf_ap_isir_corr.correction_status%TYPE;
122 ln_corr_count NUMBER;
123 ln_no_corr_count NUMBER;
124 ln_tot_no_corr_count NUMBER;
125 ln_tot_corr_count NUMBER;
126 g_disb_hold VARCHAR2(1);
127
128 FUNCTION l_chk_dup_corr(
129 pn_isir_id NUMBER,
130 pn_sar_field_number NUMBER,
131 pn_corrected_value VARCHAR2,
132 pv_status OUT NOCOPY VARCHAR2
133 )
134 RETURN BOOLEAN IS
135
136 -- bkkumar 02-07-2003 Bug 3004841 Here the check incorporated is that if the corrected
137 -- value is the same as already present in the record with status "batched" then do not create the
138 -- record again in the "pending" status.
139
140 CURSOR cur_dup_rec (pn_isir_id NUMBER, pn_sar_field_number NUMBER) IS
141 SELECT NVL(correction_status,'NOTREADY') correction_status,
142 corrected_value
143 FROM igf_ap_isir_corr
144 WHERE isir_id = pn_isir_id
145 AND sar_field_number = pn_sar_field_number;
146
147 l_cur_dup_rec cur_dup_rec%ROWTYPE;
148
149 CURSOR cur_find_corr (pn_isir_id NUMBER, pn_sar_field_number NUMBER, pv_status VARCHAR2) IS
150 SELECT count(*)
151 FROM igf_ap_isir_corr
152 WHERE isir_id = pn_isir_id
153 AND sar_field_number = pn_sar_field_number
154 AND correction_status = pv_status;
155
156 l_corr_status igf_ap_isir_corr.correction_status%TYPE := NULL;
157 l_insert BOOLEAN := FALSE;
158 l_exists_flag BOOLEAN := FALSE; -- this flag is set if the corrected value is same as the already present value.
159 l_count NUMBER;
160
161 BEGIN
162
163 /*
164 This function will check if there is already correction present which has not been batched. If yes then the correction
165 record is updated instead of creating a new correction record.
166 Also, if the correction is present with status 'BATCHED', then new corrections are created with status 'PENDING'
167 */
168 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
169 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.l_chk_dup_corr.debug','opening cursor with cur_dup_rec > pn_isir_id/pn_sar_field_number'||pn_isir_id ||'/'||pn_sar_field_number);
170 END IF;
171
172 OPEN cur_dup_rec (pn_isir_id ,pn_sar_field_number);
173 LOOP
174
175 FETCH cur_dup_rec INTO l_cur_dup_rec;
176 l_corr_status := l_cur_dup_rec.correction_status;
177
178 IF cur_dup_rec%NOTFOUND THEN
179 IF l_corr_status IS NULL THEN
180 l_new_corr_status := 'READY' ;
181 l_insert := TRUE;
182 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
183 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.l_chk_dup_corr.debug','New Correction status is READY');
184 END IF;
185 END IF;
186 EXIT;
187 END IF;
188
189 IF l_corr_status IN ( 'NOTREADY', 'READY','PENDING','HOLD') THEN
190 l_insert := FALSE;
191 l_new_corr_status := l_corr_status;
192 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
193 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.l_chk_dup_corr.debug','New Correction status is '||l_corr_status);
194 END IF;
195
196 ELSIF l_corr_status = 'BATCHED' THEN
197 IF l_cur_dup_rec.corrected_value = pn_corrected_value THEN
198 l_exists_flag := TRUE;
199 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
200 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.l_chk_dup_corr.debug','value is the same as already present in the record with status batched');
201 END IF;
202
203 ELSE
204 l_new_corr_status := 'PENDING';
205 l_insert:= TRUE;
206 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
207 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.l_chk_dup_corr.debug','value is not same as already present in the record with status batched and now status changed to pending ');
208 END IF;
209 END IF;
210 END IF;
211
212 END LOOP;
213 CLOSE cur_dup_rec;
214
215 IF l_exists_flag THEN
216 pv_status := '';
217 RETURN FALSE;
218 END IF;
219
220 OPEN cur_find_corr ( pn_isir_id ,pn_sar_field_number, l_new_corr_status);
221 FETCH cur_find_corr INTO l_count;
222
223 IF NOT l_insert AND l_count = 1 THEN
224 pv_status := 'UPDATE';
225 RETURN TRUE;
226 ELSIF l_insert AND l_count = 0 THEN
227 pv_status := 'INSERT';
228 RETURN TRUE;
229 ELSE
230 RETURN FALSE;
231 END IF;
232 CLOSE cur_find_corr;
233
234 EXCEPTION
235 WHEN OTHERS THEN
236 CLOSE cur_dup_rec;
237 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
238 fnd_message.set_token('NAME','IGF_AP_BATCH_VER_PRC.L_CHK_DUP_CORR '||SQLERRM);
239 igs_ge_msg_stack.add;
240 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
241 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.l_chk_dup_corr.debug',SQLERRM);
242 END IF;
243 RETURN FALSE;
244 END l_chk_dup_corr;
245
246
247 PROCEDURE l_update_base_rec(
248 pn_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
249 pv_status igf_ap_fa_base_rec_all.fed_verif_status%TYPE ,
250 pv_tol NUMBER,
251 pv_fa_process_status igf_ap_fa_base_rec_all.fa_process_status%TYPE
252 ) IS
253
254 CURSOR cur_fa_base (pn_base_id NUMBER) IS
255 SELECT fbr.*
256 FROM igf_ap_fa_base_rec fbr
257 WHERE base_id = pn_base_id FOR UPDATE NOWAIT;
258
259 BEGIN
260
261 FOR cur_fbr_rec IN cur_fa_base (pn_base_id) LOOP
262
263 -- Update necessary fields in FA_Base_Record table.
264 igf_ap_fa_base_rec_pkg.update_row (
265 x_Mode => 'R',
266 x_rowid => cur_fbr_rec.row_id,
267 x_base_id => cur_fbr_rec.base_id,
268 x_ci_cal_type => cur_fbr_rec.ci_cal_type,
269 x_person_id => cur_fbr_rec.person_id,
270 x_ci_sequence_number => cur_fbr_rec.ci_sequence_number,
271 x_org_id => cur_fbr_rec.org_id,
272 x_coa_pending => cur_fbr_rec.coa_pending,
273 x_verification_process_run => cur_fbr_rec.verification_process_run,
274 x_inst_verif_status_date => cur_fbr_rec.inst_verif_status_date,
275 x_manual_verif_flag => cur_fbr_rec.manual_verif_flag,
276 x_fed_verif_status => NVL(pv_status,cur_fbr_rec.fed_verif_status),
277 x_fed_verif_status_date => TRUNC(SYSDATE),
278 x_inst_verif_status => cur_fbr_rec.inst_verif_status,
279 x_nslds_eligible => cur_fbr_rec.nslds_eligible,
280 x_ede_correction_batch_id => cur_fbr_rec.ede_correction_batch_id,
281 x_fa_process_status_date => TRUNC(SYSDATE) ,
285 x_isir_status_date => cur_fbr_rec.isir_status_date,
282 x_isir_corr_status => cur_fbr_rec.isir_corr_status,
283 x_isir_corr_status_date => cur_fbr_rec.isir_corr_status_date,
284 x_isir_status => cur_fbr_rec.isir_status,
286 x_coa_code_f => cur_fbr_rec.coa_code_f,
287 x_coa_code_i => cur_fbr_rec.coa_code_i,
288 x_coa_f => cur_fbr_rec.coa_f,
289 x_coa_i => cur_fbr_rec.coa_i,
290 x_disbursement_hold => cur_fbr_rec.disbursement_hold,
291 x_fa_process_status => NVL(pv_fa_process_status,cur_fbr_rec.fa_process_status),
292 x_notification_status => cur_fbr_rec.notification_status,
293 x_notification_status_date => cur_fbr_rec.notification_status_date,
294 x_packaging_status => cur_fbr_rec.packaging_status,
295 x_packaging_status_date => cur_fbr_rec.packaging_status_date,
296 x_total_package_accepted => cur_fbr_rec.total_package_accepted,
297 x_total_package_offered => cur_fbr_rec.total_package_offered,
298 x_admstruct_id => cur_fbr_rec.admstruct_id,
299 x_admsegment_1 => cur_fbr_rec.admsegment_1,
300 x_admsegment_2 => cur_fbr_rec.admsegment_2,
301 x_admsegment_3 => cur_fbr_rec.admsegment_3,
302 x_admsegment_4 => cur_fbr_rec.admsegment_4,
303 x_admsegment_5 => cur_fbr_rec.admsegment_5,
304 x_admsegment_6 => cur_fbr_rec.admsegment_6,
305 x_admsegment_7 => cur_fbr_rec.admsegment_7,
306 x_admsegment_8 => cur_fbr_rec.admsegment_8,
307 x_admsegment_9 => cur_fbr_rec.admsegment_9,
308 x_admsegment_10 => cur_fbr_rec.admsegment_10,
309 x_admsegment_11 => cur_fbr_rec.admsegment_11,
310 x_admsegment_12 => cur_fbr_rec.admsegment_12,
311 x_admsegment_13 => cur_fbr_rec.admsegment_13,
312 x_admsegment_14 => cur_fbr_rec.admsegment_14,
313 x_admsegment_15 => cur_fbr_rec.admsegment_15,
314 x_admsegment_16 => cur_fbr_rec.admsegment_16,
315 x_admsegment_17 => cur_fbr_rec.admsegment_17,
316 x_admsegment_18 => cur_fbr_rec.admsegment_18,
317 x_admsegment_19 => cur_fbr_rec.admsegment_19,
318 x_admsegment_20 => cur_fbr_rec.admsegment_20,
319 x_packstruct_id => cur_fbr_rec.packstruct_id,
320 x_packsegment_1 => cur_fbr_rec.packsegment_1,
321 x_packsegment_2 => cur_fbr_rec.packsegment_2,
322 x_packsegment_3 => cur_fbr_rec.packsegment_3,
323 x_packsegment_4 => cur_fbr_rec.packsegment_4,
324 x_packsegment_5 => cur_fbr_rec.packsegment_5,
325 x_packsegment_6 => cur_fbr_rec.packsegment_6,
326 x_packsegment_7 => cur_fbr_rec.packsegment_7,
327 x_packsegment_8 => cur_fbr_rec.packsegment_8,
328 x_packsegment_9 => cur_fbr_rec.packsegment_9,
329 x_packsegment_10 => cur_fbr_rec.packsegment_10,
330 x_packsegment_11 => cur_fbr_rec.packsegment_11,
331 x_packsegment_12 => cur_fbr_rec.packsegment_12,
332 x_packsegment_13 => cur_fbr_rec.packsegment_13,
333 x_packsegment_14 => cur_fbr_rec.packsegment_14,
334 x_packsegment_15 => cur_fbr_rec.packsegment_15,
335 x_packsegment_16 => cur_fbr_rec.packsegment_16,
336 x_packsegment_17 => cur_fbr_rec.packsegment_17,
340 x_miscstruct_id => cur_fbr_rec.miscstruct_id,
337 x_packsegment_18 => cur_fbr_rec.packsegment_18,
338 x_packsegment_19 => cur_fbr_rec.packsegment_19,
339 x_packsegment_20 => cur_fbr_rec.packsegment_20,
341 x_miscsegment_1 => cur_fbr_rec.miscsegment_1,
342 x_miscsegment_2 => cur_fbr_rec.miscsegment_2,
343 x_miscsegment_3 => cur_fbr_rec.miscsegment_3,
344 x_miscsegment_4 => cur_fbr_rec.miscsegment_4,
345 x_miscsegment_5 => cur_fbr_rec.miscsegment_5,
346 x_miscsegment_6 => cur_fbr_rec.miscsegment_6,
347 x_miscsegment_7 => cur_fbr_rec.miscsegment_7,
348 x_miscsegment_8 => cur_fbr_rec.miscsegment_8,
349 x_miscsegment_9 => cur_fbr_rec.miscsegment_9,
350 x_miscsegment_10 => cur_fbr_rec.miscsegment_10,
351 x_miscsegment_11 => cur_fbr_rec.miscsegment_11,
352 x_miscsegment_12 => cur_fbr_rec.miscsegment_12,
353 x_miscsegment_13 => cur_fbr_rec.miscsegment_13,
354 x_miscsegment_14 => cur_fbr_rec.miscsegment_14,
355 x_miscsegment_15 => cur_fbr_rec.miscsegment_15,
356 x_miscsegment_16 => cur_fbr_rec.miscsegment_16,
357 x_miscsegment_17 => cur_fbr_rec.miscsegment_17,
358 x_miscsegment_18 => cur_fbr_rec.miscsegment_18,
359 x_miscsegment_19 => cur_fbr_rec.miscsegment_19,
360 x_miscsegment_20 => cur_fbr_rec.miscsegment_20,
361 x_prof_judgement_flg => cur_fbr_rec.prof_judgement_flg,
362 x_nslds_data_override_flg => cur_fbr_rec.nslds_data_override_flg,
363 x_target_group => cur_fbr_rec.target_group,
364 x_coa_fixed => cur_fbr_rec.coa_fixed,
365 x_tolerance_amount => NVL(pv_tol,cur_fbr_rec.tolerance_amount),
366 x_profile_status => cur_fbr_rec.profile_status,
367 x_profile_status_date => cur_fbr_rec.profile_status_date,
368 x_profile_fc => cur_fbr_rec.profile_fc,
369 x_coa_pell => cur_fbr_rec.coa_pell,
370 x_manual_disb_hold => g_disb_hold,
371 x_pell_alt_expense => cur_fbr_rec.pell_alt_expense,
372 x_assoc_org_num => cur_fbr_rec.assoc_org_num,
373 x_award_fmly_contribution_type => cur_fbr_rec.award_fmly_contribution_type,
374 x_isir_locked_by => cur_fbr_rec.isir_locked_by,
375 x_adnl_unsub_loan_elig_flag => cur_fbr_rec.adnl_unsub_loan_elig_flag,
376 x_lock_awd_flag => cur_fbr_rec.lock_awd_flag,
377 x_lock_coa_flag => cur_fbr_rec.lock_coa_flag
378 );
379 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
380 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.l_update_base_rec.debug','igf_ap_fa_base_rec_pkg.update_row successfull');
381 END IF;
382 END LOOP;
383
384 EXCEPTION
385 WHEN OTHERS THEN
386 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
387 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_BATCH_VER_PRC.L_UPDATE_BASE_REC '||SQLERRM);
388 IGS_GE_MSG_STACK.ADD;
389 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
390 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.l_update_base_rec.debug',SQLERRM);
391 END IF;
392 APP_EXCEPTION.RAISE_EXCEPTION;
393 END l_update_base_rec;
394
395
396 PROCEDURE check_data_type(p_colname VARCHAR2, p_datatype OUT NOCOPY VARCHAR2) IS
397 /*
398 || Created By : Rakesh
399 || Created On : 22-Mar-2002
400 || Purpose : For right padding the variables to make their length
401 || fit to the field size in record.
402 || Known limitations, enhancements or remarks :
403 || Change History :
404 || Who When What
405 || (reverse chronological order - newest change first)
406 */
407
408 v_datatype VARCHAR2(106);
412 SELECT data_type
409 v_date_num_flg VARCHAR2(1);
410
411 CURSOR get_data_type ( cp_tablename VARCHAR2 )IS
413 FROM user_tab_columns
414 WHERE column_name=p_colname
415 AND table_name = cp_tablename ;
416
417 l_tablename VARCHAR2(60) ;
418
419 BEGIN
420
421 l_tablename := 'IGF_AP_ISIR_MATCHED';
422 OPEN get_data_type ( l_tablename) ;
423 FETCH get_data_type INTO v_datatype;
424 CLOSE get_data_type;
425
426 IF v_datatype = 'NUMBER' THEN
427 p_datatype := 'N';
428 ELSIF v_datatype = 'DATE' THEN
429 p_datatype := 'D';
430 ELSE
431 p_datatype := 'C';
432 END IF;
433
434 EXCEPTION
435 WHEN OTHERS THEN
436 p_datatype := 'C';
437 END check_data_type;
438
439 PROCEDURE create_correction_isir(
440 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
441 pn_isir_id OUT NOCOPY NUMBER
442 ) IS
443 /*
444 || Created By : skoppula
445 || Created On : 04-JUL-2001
446 || Purpose : To create the Correction ISIR
447 || Known limitations, enhancements or remarks :
448 || Change History :
449 || Who When What
450 || masehgal 15-Feb-2002 # 2216956 FACR007
451 || Added Verif_track_flag
452 || (reverse chronological order - newest change first)
453 */
454
455 CURSOR cur_isir_original (cp_base_id NUMBER) IS
456 SELECT im.*
457 FROM igf_ap_isir_matched im
458 WHERE im.payment_isir = 'Y'
459 AND im.system_record_type = 'ORIGINAL'
460 AND im.base_id = cp_base_id;
461
462 lv_rowid VARCHAR2(30);
463
464 BEGIN
465
466 -- INsert Record in IGF_AP_ISIR_Matched table
467 FOR cur_ori_isir_rec IN cur_isir_original(p_base_id) LOOP
468
469 igf_ap_isir_matched_pkg.insert_row(
470 x_Mode => 'R',
471 x_rowid => lv_rowid,
472 x_isir_id => pn_isir_id,
473 x_base_id => cur_ori_isir_rec.base_id,
474 x_batch_year => cur_ori_isir_rec.batch_year,
475 x_transaction_num => cur_ori_isir_rec.transaction_num,
476 x_current_ssn => cur_ori_isir_rec.current_ssn,
477 x_ssn_name_change => cur_ori_isir_rec.ssn_name_change,
478 x_original_ssn => cur_ori_isir_rec.original_ssn,
479 x_orig_name_id => cur_ori_isir_rec.orig_name_id,
480 x_last_name => cur_ori_isir_rec.last_name,
481 x_first_name => cur_ori_isir_rec.first_name,
482 x_middle_initial => cur_ori_isir_rec.middle_initial,
483 x_perm_mail_add => cur_ori_isir_rec.perm_mail_add,
484 x_perm_city => cur_ori_isir_rec.perm_city,
485 x_perm_state => cur_ori_isir_rec.perm_state,
486 x_perm_zip_code => cur_ori_isir_rec.perm_zip_code,
487 x_date_of_birth => cur_ori_isir_rec.date_of_birth,
488 x_phone_number => cur_ori_isir_rec.phone_number,
489 x_driver_license_number => cur_ori_isir_rec.driver_license_number,
490 x_driver_license_state => cur_ori_isir_rec.driver_license_state,
491 x_citizenship_status => cur_ori_isir_rec.citizenship_status,
492 x_alien_reg_number => cur_ori_isir_rec.alien_reg_number,
493 x_s_marital_status => cur_ori_isir_rec.s_marital_status,
494 x_s_marital_status_date => cur_ori_isir_rec.s_marital_status_date,
495 x_summ_enrl_status => cur_ori_isir_rec.summ_enrl_status,
496 x_fall_enrl_status => cur_ori_isir_rec.fall_enrl_status,
497 x_winter_enrl_status => cur_ori_isir_rec.winter_enrl_status,
498 x_spring_enrl_status => cur_ori_isir_rec.spring_enrl_status,
499 x_summ2_enrl_status => cur_ori_isir_rec.summ2_enrl_status,
500 x_fathers_highest_edu_level => cur_ori_isir_rec.fathers_highest_edu_level,
501 x_mothers_highest_edu_level => cur_ori_isir_rec.mothers_highest_edu_level,
502 x_s_state_legal_residence => cur_ori_isir_rec.s_state_legal_residence,
503 x_legal_residence_before_date => cur_ori_isir_rec.legal_residence_before_date,
504 x_s_legal_resd_date => cur_ori_isir_rec.s_legal_resd_date,
505 x_ss_r_u_male => cur_ori_isir_rec.ss_r_u_male,
509 x_high_school_diploma_ged => cur_ori_isir_rec.high_school_diploma_ged,
506 x_selective_service_reg => cur_ori_isir_rec.selective_service_reg,
507 x_degree_certification => cur_ori_isir_rec.degree_certification,
508 x_grade_level_in_college => cur_ori_isir_rec.grade_level_in_college,
510 x_first_bachelor_deg_by_date => cur_ori_isir_rec.first_bachelor_deg_by_date,
511 x_interest_in_loan => cur_ori_isir_rec.interest_in_loan,
512 x_interest_in_stud_employment => cur_ori_isir_rec.interest_in_stud_employment,
513 x_drug_offence_conviction => cur_ori_isir_rec.drug_offence_conviction,
514 x_s_tax_return_status => cur_ori_isir_rec.s_tax_return_status,
515 x_s_type_tax_return => cur_ori_isir_rec.s_type_tax_return,
516 x_s_elig_1040ez => cur_ori_isir_rec.s_elig_1040ez,
517 x_s_adjusted_gross_income => cur_ori_isir_rec.s_adjusted_gross_income,
518 x_s_fed_taxes_paid => cur_ori_isir_rec.s_fed_taxes_paid,
519 x_s_exemptions => cur_ori_isir_rec.s_exemptions,
520 x_s_income_from_work => cur_ori_isir_rec.s_income_from_work,
521 x_spouse_income_from_work => cur_ori_isir_rec.spouse_income_from_work,
522 x_s_toa_amt_from_wsa => cur_ori_isir_rec.s_toa_amt_from_wsa,
523 x_s_toa_amt_from_wsb => cur_ori_isir_rec.s_toa_amt_from_wsb,
524 x_s_toa_amt_from_wsc => cur_ori_isir_rec.s_toa_amt_from_wsc,
525 x_s_investment_networth => cur_ori_isir_rec.s_investment_networth,
526 x_s_busi_farm_networth => cur_ori_isir_rec.s_busi_farm_networth,
527 x_s_cash_savings => cur_ori_isir_rec.s_cash_savings,
528 x_va_months => cur_ori_isir_rec.va_months,
529 x_va_amount => cur_ori_isir_rec.va_amount,
530 x_stud_dob_before_date => cur_ori_isir_rec.stud_dob_before_date,
531 x_deg_beyond_bachelor => cur_ori_isir_rec.deg_beyond_bachelor,
532 x_s_married => cur_ori_isir_rec.s_married,
533 x_s_have_children => cur_ori_isir_rec.s_have_children,
534 x_legal_dependents => cur_ori_isir_rec.legal_dependents,
535 x_orphan_ward_of_court => cur_ori_isir_rec.orphan_ward_of_court,
536 x_s_veteran => cur_ori_isir_rec.s_veteran,
537 x_p_marital_status => cur_ori_isir_rec.p_marital_status,
538 x_father_ssn => cur_ori_isir_rec.father_ssn,
539 x_f_last_name => cur_ori_isir_rec.f_last_name,
540 x_mother_ssn => cur_ori_isir_rec.mother_ssn,
541 x_m_last_name => cur_ori_isir_rec.m_last_name,
542 x_p_num_family_member => cur_ori_isir_rec.p_num_family_member,
543 x_p_num_in_college => cur_ori_isir_rec.p_num_in_college,
544 x_p_state_legal_residence => cur_ori_isir_rec.p_state_legal_residence,
545 x_p_state_legal_res_before_dt => cur_ori_isir_rec.p_state_legal_res_before_dt,
546 x_p_legal_res_date => cur_ori_isir_rec.p_legal_res_date,
547 x_age_older_parent => cur_ori_isir_rec.age_older_parent,
548 x_p_tax_return_status => cur_ori_isir_rec.p_tax_return_status,
549 x_p_type_tax_return => cur_ori_isir_rec.p_type_tax_return,
550 x_p_elig_1040aez => cur_ori_isir_rec.p_elig_1040aez,
551 x_p_adjusted_gross_income => cur_ori_isir_rec.p_adjusted_gross_income,
552 x_p_taxes_paid => cur_ori_isir_rec.p_taxes_paid,
553 x_p_exemptions => cur_ori_isir_rec.p_exemptions,
554 x_f_income_work => cur_ori_isir_rec.f_income_work,
555 x_m_income_work => cur_ori_isir_rec.m_income_work,
556 x_p_income_wsa => cur_ori_isir_rec.p_income_wsa,
557 x_p_income_wsb => cur_ori_isir_rec.p_income_wsb,
558 x_p_income_wsc => cur_ori_isir_rec.p_income_wsc,
559 x_p_investment_networth => cur_ori_isir_rec.p_investment_networth,
560 x_p_business_networth => cur_ori_isir_rec.p_business_networth,
561 x_p_cash_saving => cur_ori_isir_rec.p_cash_saving,
562 x_s_num_family_members => cur_ori_isir_rec.s_num_family_members,
566 x_second_college => cur_ori_isir_rec.second_college,
563 x_s_num_in_college => cur_ori_isir_rec.s_num_in_college,
564 x_first_college => cur_ori_isir_rec.first_college,
565 x_first_house_plan => cur_ori_isir_rec.first_house_plan,
567 x_second_house_plan => cur_ori_isir_rec.second_house_plan,
568 x_third_college => cur_ori_isir_rec.third_college,
569 x_third_house_plan => cur_ori_isir_rec.third_house_plan,
570 x_fourth_college => cur_ori_isir_rec.fourth_college,
571 x_fourth_house_plan => cur_ori_isir_rec.fourth_house_plan,
572 x_fifth_college => cur_ori_isir_rec.fifth_college,
573 x_fifth_house_plan => cur_ori_isir_rec.fifth_house_plan,
574 x_sixth_college => cur_ori_isir_rec.sixth_college,
575 x_sixth_house_plan => cur_ori_isir_rec.sixth_house_plan,
576 x_date_app_completed => cur_ori_isir_rec.date_app_completed,
577 x_signed_by => cur_ori_isir_rec.signed_by,
578 x_preparer_ssn => cur_ori_isir_rec.preparer_ssn,
579 x_preparer_emp_id_number => cur_ori_isir_rec.preparer_emp_id_number,
580 x_preparer_sign => cur_ori_isir_rec.preparer_sign,
581 x_transaction_receipt_date => cur_ori_isir_rec.transaction_receipt_date,
582 x_dependency_override_ind => cur_ori_isir_rec.dependency_override_ind,
583 x_faa_fedral_schl_code => cur_ori_isir_rec.faa_fedral_schl_code,
584 x_faa_adjustment => cur_ori_isir_rec.faa_adjustment,
585 x_input_record_type => cur_ori_isir_rec.input_record_type,
586 x_serial_number => cur_ori_isir_rec.serial_number,
587 x_batch_number => cur_ori_isir_rec.batch_number,
588 x_early_analysis_flag => cur_ori_isir_rec.early_analysis_flag,
589 x_app_entry_source_code => cur_ori_isir_rec.app_entry_source_code,
590 x_eti_destination_code => cur_ori_isir_rec.eti_destination_code,
591 x_reject_override_b => cur_ori_isir_rec.reject_override_b,
592 x_reject_override_n => cur_ori_isir_rec.reject_override_n,
593 x_reject_override_w => cur_ori_isir_rec.reject_override_w,
594 x_assum_override_1 => cur_ori_isir_rec.assum_override_1,
595 x_assum_override_2 => cur_ori_isir_rec.assum_override_2,
596 x_assum_override_3 => cur_ori_isir_rec.assum_override_3,
597 x_assum_override_4 => cur_ori_isir_rec.assum_override_4,
598 x_assum_override_5 => cur_ori_isir_rec.assum_override_5,
599 x_assum_override_6 => cur_ori_isir_rec.assum_override_6,
600 x_dependency_status => cur_ori_isir_rec.dependency_status,
601 x_s_email_address => cur_ori_isir_rec.s_email_address,
602 x_nslds_reason_code => cur_ori_isir_rec.nslds_reason_code,
603 x_app_receipt_date => cur_ori_isir_rec.app_receipt_date,
604 x_processed_rec_type => cur_ori_isir_rec.processed_rec_type,
605 x_hist_correction_for_tran_id => cur_ori_isir_rec.hist_correction_for_tran_id,
606 x_system_generated_indicator => cur_ori_isir_rec.system_generated_indicator,
607 x_dup_request_indicator => cur_ori_isir_rec.dup_request_indicator,
608 x_source_of_correction => cur_ori_isir_rec.source_of_correction,
609 x_p_cal_tax_status => cur_ori_isir_rec.p_cal_tax_status,
610 x_s_cal_tax_status => cur_ori_isir_rec.s_cal_tax_status,
611 x_graduate_flag => cur_ori_isir_rec.graduate_flag,
612 x_auto_zero_efc => cur_ori_isir_rec.auto_zero_efc,
613 x_efc_change_flag => cur_ori_isir_rec.efc_change_flag,
614 x_sarc_flag => cur_ori_isir_rec.sarc_flag,
615 x_simplified_need_test => cur_ori_isir_rec.simplified_need_test,
616 x_reject_reason_codes => cur_ori_isir_rec.reject_reason_codes,
617 x_select_service_match_flag => cur_ori_isir_rec.select_service_match_flag,
618 x_select_service_reg_flag => cur_ori_isir_rec.select_service_reg_flag,
619 x_ins_match_flag => cur_ori_isir_rec.ins_match_flag,
620 x_ins_verification_number => NULL,
621 x_sec_ins_match_flag => cur_ori_isir_rec.sec_ins_match_flag,
625 x_ssn_date_of_death => cur_ori_isir_rec.ssn_date_of_death,
622 x_sec_ins_ver_number => cur_ori_isir_rec.sec_ins_ver_number,
623 x_ssn_match_flag => cur_ori_isir_rec.ssn_match_flag,
624 x_ssa_citizenship_flag => cur_ori_isir_rec.ssa_citizenship_flag,
626 x_nslds_match_flag => cur_ori_isir_rec.nslds_match_flag,
627 x_va_match_flag => cur_ori_isir_rec.va_match_flag,
628 x_prisoner_match => cur_ori_isir_rec.prisoner_match,
629 x_verification_flag => cur_ori_isir_rec.verification_flag,
630 x_subsequent_app_flag => cur_ori_isir_rec.subsequent_app_flag,
631 x_app_source_site_code => cur_ori_isir_rec.app_source_site_code,
632 x_tran_source_site_code => cur_ori_isir_rec.tran_source_site_code,
633 x_drn => cur_ori_isir_rec.drn,
634 x_tran_process_date => cur_ori_isir_rec.tran_process_date,
635 x_computer_batch_number => cur_ori_isir_rec.computer_batch_number,
636 x_correction_flags => cur_ori_isir_rec.correction_flags,
637 x_highlight_flags => cur_ori_isir_rec.highlight_flags,
638 x_paid_efc => cur_ori_isir_rec.paid_efc,
639 x_primary_efc => cur_ori_isir_rec.primary_efc,
640 x_secondary_efc => cur_ori_isir_rec.secondary_efc,
641 x_fed_pell_grant_efc_type => cur_ori_isir_rec.fed_pell_grant_efc_type,
642 x_primary_efc_type => cur_ori_isir_rec.primary_efc_type,
643 x_sec_efc_type => cur_ori_isir_rec.sec_efc_type,
644 x_primary_alternate_month_1 => cur_ori_isir_rec.primary_alternate_month_1,
645 x_primary_alternate_month_2 => cur_ori_isir_rec.primary_alternate_month_2,
646 x_primary_alternate_month_3 => cur_ori_isir_rec.primary_alternate_month_3,
647 x_primary_alternate_month_4 => cur_ori_isir_rec.primary_alternate_month_4,
648 x_primary_alternate_month_5 => cur_ori_isir_rec.primary_alternate_month_5,
649 x_primary_alternate_month_6 => cur_ori_isir_rec.primary_alternate_month_6,
650 x_primary_alternate_month_7 => cur_ori_isir_rec.primary_alternate_month_7,
651 x_primary_alternate_month_8 => cur_ori_isir_rec.primary_alternate_month_8,
652 x_primary_alternate_month_10 => cur_ori_isir_rec.primary_alternate_month_10,
653 x_primary_alternate_month_11 => cur_ori_isir_rec.primary_alternate_month_11,
654 x_primary_alternate_month_12 => cur_ori_isir_rec.primary_alternate_month_12,
655 x_sec_alternate_month_1 => cur_ori_isir_rec.sec_alternate_month_1,
656 x_sec_alternate_month_2 => cur_ori_isir_rec.sec_alternate_month_2,
657 x_sec_alternate_month_3 => cur_ori_isir_rec.sec_alternate_month_3,
658 x_sec_alternate_month_4 => cur_ori_isir_rec.sec_alternate_month_4,
659 x_sec_alternate_month_5 => cur_ori_isir_rec.sec_alternate_month_5,
660 x_sec_alternate_month_6 => cur_ori_isir_rec.sec_alternate_month_6,
661 x_sec_alternate_month_7 => cur_ori_isir_rec.sec_alternate_month_7,
662 x_sec_alternate_month_8 => cur_ori_isir_rec.sec_alternate_month_8,
663 x_sec_alternate_month_10 => cur_ori_isir_rec.sec_alternate_month_10,
664 x_sec_alternate_month_11 => cur_ori_isir_rec.sec_alternate_month_11,
665 x_sec_alternate_month_12 => cur_ori_isir_rec.sec_alternate_month_12,
666 x_total_income => cur_ori_isir_rec.total_income,
667 x_allow_total_income => cur_ori_isir_rec.allow_total_income,
668 x_state_tax_allow => cur_ori_isir_rec.state_tax_allow,
669 x_employment_allow => cur_ori_isir_rec.employment_allow,
670 x_income_protection_allow => cur_ori_isir_rec.income_protection_allow,
671 x_available_income => cur_ori_isir_rec.available_income,
672 x_contribution_from_ai => cur_ori_isir_rec.contribution_from_ai,
673 x_discretionary_networth => cur_ori_isir_rec.discretionary_networth,
674 x_efc_networth => cur_ori_isir_rec.efc_networth,
675 x_asset_protect_allow => cur_ori_isir_rec.asset_protect_allow,
676 x_parents_cont_from_assets => cur_ori_isir_rec.parents_cont_from_assets,
677 x_adjusted_available_income => cur_ori_isir_rec.adjusted_available_income,
678 x_total_student_contribution => cur_ori_isir_rec.total_student_contribution,
682 x_sati => cur_ori_isir_rec.sati,
679 x_total_parent_contribution => cur_ori_isir_rec.total_parent_contribution,
680 x_parents_contribution => cur_ori_isir_rec.parents_contribution,
681 x_student_total_income => cur_ori_isir_rec.student_total_income,
683 x_sic => cur_ori_isir_rec.sic,
684 x_sdnw => cur_ori_isir_rec.sdnw,
685 x_sca => cur_ori_isir_rec.sca,
686 x_fti => cur_ori_isir_rec.fti,
687 x_secti => cur_ori_isir_rec.secti,
688 x_secati => cur_ori_isir_rec.secati,
689 x_secstx => cur_ori_isir_rec.secstx,
690 x_secea => cur_ori_isir_rec.secea,
691 x_secipa => cur_ori_isir_rec.secipa,
692 x_secai => cur_ori_isir_rec.secai,
693 x_seccai => cur_ori_isir_rec.seccai,
694 x_secdnw => cur_ori_isir_rec.secdnw,
695 x_secnw => cur_ori_isir_rec.secnw,
696 x_secapa => cur_ori_isir_rec.secapa,
697 x_secpca => cur_ori_isir_rec.secpca,
698 x_secaai => cur_ori_isir_rec.secaai,
699 x_sectsc => cur_ori_isir_rec.sectsc,
700 x_sectpc => cur_ori_isir_rec.sectpc,
701 x_secpc => cur_ori_isir_rec.secpc,
702 x_secsti => cur_ori_isir_rec.secsti,
703 x_secsic => cur_ori_isir_rec.secsic,
704 x_secsati => cur_ori_isir_rec.secsati,
705 x_secsdnw => cur_ori_isir_rec.secsdnw,
706 x_secsca => cur_ori_isir_rec.secsca,
707 x_secfti => cur_ori_isir_rec.secfti,
708 x_a_citizenship => cur_ori_isir_rec.a_citizenship,
709 x_a_student_marital_status => cur_ori_isir_rec.a_student_marital_status,
710 x_a_student_agi => cur_ori_isir_rec.a_student_agi,
711 x_a_s_us_tax_paid => cur_ori_isir_rec.a_s_us_tax_paid,
712 x_a_s_income_work => cur_ori_isir_rec.a_s_income_work,
713 x_a_spouse_income_work => cur_ori_isir_rec.a_spouse_income_work,
714 x_a_s_total_wsc => cur_ori_isir_rec.a_s_total_wsc,
715 x_a_date_of_birth => cur_ori_isir_rec.a_date_of_birth,
716 x_a_student_married => cur_ori_isir_rec.a_student_married,
717 x_a_have_children => cur_ori_isir_rec.a_have_children,
718 x_a_s_have_dependents => cur_ori_isir_rec.a_s_have_dependents,
719 x_a_va_status => cur_ori_isir_rec.a_va_status,
720 x_a_s_num_in_family => cur_ori_isir_rec.a_s_num_in_family,
721 x_a_s_num_in_college => cur_ori_isir_rec.a_s_num_in_college,
722 x_a_p_marital_status => cur_ori_isir_rec.a_p_marital_status,
723 x_a_father_ssn => cur_ori_isir_rec.a_father_ssn,
724 x_a_mother_ssn => cur_ori_isir_rec.a_mother_ssn,
725 x_a_parents_num_family => cur_ori_isir_rec.a_parents_num_family,
726 x_a_parents_num_college => cur_ori_isir_rec.a_parents_num_college,
727 x_a_parents_agi => cur_ori_isir_rec.a_parents_agi,
728 x_a_p_us_tax_paid => cur_ori_isir_rec.a_p_us_tax_paid,
729 x_a_f_work_income => cur_ori_isir_rec.a_f_work_income,
730 x_a_m_work_income => cur_ori_isir_rec.a_m_work_income,
731 x_a_p_total_wsc => cur_ori_isir_rec.a_p_total_wsc,
732 x_comment_codes => cur_ori_isir_rec.comment_codes,
733 x_sar_ack_comm_code => cur_ori_isir_rec.sar_ack_comm_code,
734 x_pell_grant_elig_flag => cur_ori_isir_rec.pell_grant_elig_flag,
735 x_reprocess_reason_code => cur_ori_isir_rec.reprocess_reason_code,
736 x_duplicate_date => cur_ori_isir_rec.duplicate_date,
737 x_isir_transaction_type => 'C',
738 x_fedral_schl_code_indicator => cur_ori_isir_rec.fedral_schl_code_indicator,
739 x_multi_school_code_flags => cur_ori_isir_rec.multi_school_code_flags,
743 x_active_isir => 'Y',
740 x_dup_ssn_indicator => cur_ori_isir_rec.dup_ssn_indicator,
741 x_system_record_type => 'CORRECTION',
742 x_payment_isir => 'N',
744 x_receipt_status => 'MATCHED',
745 x_isir_receipt_completed => 'Y' ,
746 x_verif_track_flag => cur_ori_isir_rec.verif_track_flag,
747 x_legacy_record_flag => NULL,
748 x_father_first_name_initial => cur_ori_isir_rec.father_first_name_initial_txt,
749 x_father_step_father_birth_dt => cur_ori_isir_rec.father_step_father_birth_date,
750 x_mother_first_name_initial => cur_ori_isir_rec.mother_first_name_initial_txt,
751 x_mother_step_mother_birth_dt => cur_ori_isir_rec.mother_step_mother_birth_date,
752 x_parents_email_address_txt => cur_ori_isir_rec.parents_email_address_txt,
753 x_address_change_type => cur_ori_isir_rec.address_change_type,
754 x_cps_pushed_isir_flag => cur_ori_isir_rec.cps_pushed_isir_flag,
755 x_electronic_transaction_type => cur_ori_isir_rec.electronic_transaction_type,
756 x_sar_c_change_type => cur_ori_isir_rec.sar_c_change_type,
757 x_father_ssn_match_type => cur_ori_isir_rec.father_ssn_match_type,
758 x_mother_ssn_match_type => cur_ori_isir_rec.mother_ssn_match_type,
759 x_reject_override_g_flag => cur_ori_isir_rec.reject_override_g_flag,
760 x_dhs_verification_num_txt => cur_ori_isir_rec.dhs_verification_num_txt,
761 x_data_file_name_txt => cur_ori_isir_rec.data_file_name_txt ,
762 x_message_class_txt => NULL,
763 x_reject_override_3_flag => cur_ori_isir_rec.reject_override_3_flag,
764 x_reject_override_12_flag => cur_ori_isir_rec.reject_override_12_flag,
765 x_reject_override_j_flag => cur_ori_isir_rec.reject_override_j_flag,
766 x_reject_override_k_flag => cur_ori_isir_rec.reject_override_k_flag,
767 x_rejected_status_change_flag => cur_ori_isir_rec.rejected_status_change_flag,
768 x_verification_selection_flag => cur_ori_isir_rec.verification_selection_flag
769 );
770 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
771 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.l_update_base_rec.debug','igf_ap_isir_matched_pkg.insert_row successfull New ISIR ID: '||pn_isir_id);
772 END IF;
773 END LOOP;
774
775 EXCEPTION
776 WHEN others THEN
777 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
778 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_BATCH_VER_PRC.CREATE_CORRECTION_ISIR '||SQLERRM);
779 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
780 IGS_GE_MSG_STACK.ADD;
781 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
782 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.create_correction_isir.debug',SQLERRM);
783 END IF;
784 APP_EXCEPTION.RAISE_EXCEPTION;
785
786 END create_correction_isir;
787
788
789 PROCEDURE update_correction_isir (
790 pn_base_id igf_ap_fa_base_rec.base_id%TYPE ,
791 p_isir_rec IGF_AP_ISIR_MATCHED%ROWTYPE ,
792 p_update_ssn VARCHAR2
793 ) IS
794 /*
795 || Created By : rasingh
796 || Created On : 07-May-2002
797 || Purpose :
798 || The procedure will validate if the correctiosn will cause any chage in the EFC, If there is change then
799 || This procedure creates correction record if that record is not presant. Else FA Process Status is Updated to
800 || CALCULATED.
801 || Change History :
802 || Who When What
803 || (reverse chronological order - newest change first)
804 || brajendr 03-Mar-2003 Bug # 2822497
805 || Added the validations for Current SSN when
806 || Pell Origination is Alread Sent
807 */
808
809
810 l_message VARCHAR2(100);
811 BEGIN
812
813 IF p_update_ssn = 'Y' THEN
814 -- SSN for the student has changed so update the SSN
815 l_message := NULL;
816
817 igf_gr_gen.update_current_ssn(
818 p_isir_rec.base_id,
819 p_isir_rec.current_ssn,
820 l_message
821 );
822
823 IF l_message = 'IGF_GR_UPDT_SSN_FAIL' THEN
824 fnd_message.set_name('IGF','IGF_GR_UPDT_SSN_FAIL');
825 fnd_file.put_line(fnd_file.log,fnd_message.get);
826 END IF;
827
828 END IF;
829
830 igf_ap_isir_matched_pkg.update_row(
831 x_Mode => 'R',
832 x_rowid => p_isir_rec.row_id,
836 x_transaction_num => p_isir_rec.transaction_num,
833 x_isir_id => p_isir_rec.isir_id,
834 x_base_id => p_isir_rec.base_id,
835 x_batch_year => p_isir_rec.batch_year,
837 x_current_ssn => p_isir_rec.current_ssn,
838 x_ssn_name_change => p_isir_rec.ssn_name_change,
839 x_original_ssn => p_isir_rec.original_ssn,
840 x_orig_name_id => p_isir_rec.orig_name_id,
841 x_last_name => p_isir_rec.last_name,
842 x_first_name => p_isir_rec.first_name,
843 x_middle_initial => p_isir_rec.middle_initial,
844 x_perm_mail_add => p_isir_rec.perm_mail_add,
845 x_perm_city => p_isir_rec.perm_city,
846 x_perm_state => p_isir_rec.perm_state,
847 x_perm_zip_code => p_isir_rec.perm_zip_code,
848 x_date_of_birth => p_isir_rec.date_of_birth,
849 x_phone_number => p_isir_rec.phone_number,
850 x_driver_license_number => p_isir_rec.driver_license_number,
851 x_driver_license_state => p_isir_rec.driver_license_state,
852 x_citizenship_status => p_isir_rec.citizenship_status,
853 x_alien_reg_number => p_isir_rec.alien_reg_number,
854 x_s_marital_status => p_isir_rec.s_marital_status,
855 x_s_marital_status_date => p_isir_rec.s_marital_status_date,
856 x_summ_enrl_status => p_isir_rec.summ_enrl_status,
857 x_fall_enrl_status => p_isir_rec.fall_enrl_status,
858 x_winter_enrl_status => p_isir_rec.winter_enrl_status,
859 x_spring_enrl_status => p_isir_rec.spring_enrl_status,
860 x_summ2_enrl_status => p_isir_rec.summ2_enrl_status,
861 x_fathers_highest_edu_level => p_isir_rec.fathers_highest_edu_level,
862 x_mothers_highest_edu_level => p_isir_rec.mothers_highest_edu_level,
863 x_s_state_legal_residence => p_isir_rec.s_state_legal_residence,
864 x_legal_residence_before_date => p_isir_rec.legal_residence_before_date,
865 x_s_legal_resd_date => p_isir_rec.s_legal_resd_date,
866 x_ss_r_u_male => p_isir_rec.ss_r_u_male,
867 x_selective_service_reg => p_isir_rec.selective_service_reg,
868 x_degree_certification => p_isir_rec.degree_certification,
869 x_grade_level_in_college => p_isir_rec.grade_level_in_college,
870 x_high_school_diploma_ged => p_isir_rec.high_school_diploma_ged,
871 x_first_bachelor_deg_by_date => p_isir_rec.first_bachelor_deg_by_date,
872 x_interest_in_loan => p_isir_rec.interest_in_loan,
873 x_interest_in_stud_employment => p_isir_rec.interest_in_stud_employment,
874 x_drug_offence_conviction => p_isir_rec.drug_offence_conviction,
875 x_s_tax_return_status => p_isir_rec.s_tax_return_status,
876 x_s_type_tax_return => p_isir_rec.s_type_tax_return,
877 x_s_elig_1040ez => p_isir_rec.s_elig_1040ez,
878 x_s_adjusted_gross_income => p_isir_rec.s_adjusted_gross_income,
879 x_s_fed_taxes_paid => p_isir_rec.s_fed_taxes_paid,
880 x_s_exemptions => p_isir_rec.s_exemptions,
881 x_s_income_from_work => p_isir_rec.s_income_from_work,
882 x_spouse_income_from_work => p_isir_rec.spouse_income_from_work,
883 x_s_toa_amt_from_wsa => p_isir_rec.s_toa_amt_from_wsa,
884 x_s_toa_amt_from_wsb => p_isir_rec.s_toa_amt_from_wsb,
885 x_s_toa_amt_from_wsc => p_isir_rec.s_toa_amt_from_wsc,
886 x_s_investment_networth => p_isir_rec.s_investment_networth,
887 x_s_busi_farm_networth => p_isir_rec.s_busi_farm_networth,
888 x_s_cash_savings => p_isir_rec.s_cash_savings,
889 x_va_months => p_isir_rec.va_months,
890 x_va_amount => p_isir_rec.va_amount,
891 x_stud_dob_before_date => p_isir_rec.stud_dob_before_date,
892 x_deg_beyond_bachelor => p_isir_rec.deg_beyond_bachelor,
893 x_s_married => p_isir_rec.s_married,
894 x_s_have_children => p_isir_rec.s_have_children,
895 x_legal_dependents => p_isir_rec.legal_dependents,
896 x_orphan_ward_of_court => p_isir_rec.orphan_ward_of_court,
897 x_s_veteran => p_isir_rec.s_veteran,
898 x_p_marital_status => p_isir_rec.p_marital_status,
899 x_father_ssn => p_isir_rec.father_ssn,
900 x_f_last_name => p_isir_rec.f_last_name,
901 x_mother_ssn => p_isir_rec.mother_ssn,
902 x_m_last_name => p_isir_rec.m_last_name,
903 x_p_num_family_member => p_isir_rec.p_num_family_member,
904 x_p_num_in_college => p_isir_rec.p_num_in_college,
905 x_p_state_legal_residence => p_isir_rec.p_state_legal_residence,
906 x_p_state_legal_res_before_dt => p_isir_rec.p_state_legal_res_before_dt,
910 x_p_type_tax_return => p_isir_rec.p_type_tax_return,
907 x_p_legal_res_date => p_isir_rec.p_legal_res_date,
908 x_age_older_parent => p_isir_rec.age_older_parent,
909 x_p_tax_return_status => p_isir_rec.p_tax_return_status,
911 x_p_elig_1040aez => p_isir_rec.p_elig_1040aez,
912 x_p_adjusted_gross_income => p_isir_rec.p_adjusted_gross_income,
913 x_p_taxes_paid => p_isir_rec.p_taxes_paid,
914 x_p_exemptions => p_isir_rec.p_exemptions,
915 x_f_income_work => p_isir_rec.f_income_work,
916 x_m_income_work => p_isir_rec.m_income_work,
917 x_p_income_wsa => p_isir_rec.p_income_wsa,
918 x_p_income_wsb => p_isir_rec.p_income_wsb,
919 x_p_income_wsc => p_isir_rec.p_income_wsc,
920 x_p_investment_networth => p_isir_rec.p_investment_networth,
921 x_p_business_networth => p_isir_rec.p_business_networth,
922 x_p_cash_saving => p_isir_rec.p_cash_saving,
923 x_s_num_family_members => p_isir_rec.s_num_family_members,
924 x_s_num_in_college => p_isir_rec.s_num_in_college,
925 x_first_college => p_isir_rec.first_college,
926 x_first_house_plan => p_isir_rec.first_house_plan,
927 x_second_college => p_isir_rec.second_college,
928 x_second_house_plan => p_isir_rec.second_house_plan,
929 x_third_college => p_isir_rec.third_college,
930 x_third_house_plan => p_isir_rec.third_house_plan,
931 x_fourth_college => p_isir_rec.fourth_college,
932 x_fourth_house_plan => p_isir_rec.fourth_house_plan,
933 x_fifth_college => p_isir_rec.fifth_college,
934 x_fifth_house_plan => p_isir_rec.fifth_house_plan,
935 x_sixth_college => p_isir_rec.sixth_college,
936 x_sixth_house_plan => p_isir_rec.sixth_house_plan,
937 x_date_app_completed => p_isir_rec.date_app_completed,
938 x_signed_by => p_isir_rec.signed_by,
939 x_preparer_ssn => p_isir_rec.preparer_ssn,
940 x_preparer_emp_id_number => p_isir_rec.preparer_emp_id_number,
941 x_preparer_sign => p_isir_rec.preparer_sign,
942 x_transaction_receipt_date => p_isir_rec.transaction_receipt_date,
943 x_dependency_override_ind => p_isir_rec.dependency_override_ind,
944 x_faa_fedral_schl_code => p_isir_rec.faa_fedral_schl_code,
945 x_faa_adjustment => p_isir_rec.faa_adjustment,
946 x_input_record_type => p_isir_rec.input_record_type,
947 x_serial_number => p_isir_rec.serial_number,
948 x_batch_number => p_isir_rec.batch_number,
949 x_early_analysis_flag => p_isir_rec.early_analysis_flag,
950 x_app_entry_source_code => p_isir_rec.app_entry_source_code,
951 x_eti_destination_code => p_isir_rec.eti_destination_code,
952 x_reject_override_b => p_isir_rec.reject_override_b,
953 x_reject_override_n => p_isir_rec.reject_override_n,
954 x_reject_override_w => p_isir_rec.reject_override_w,
955 x_assum_override_1 => p_isir_rec.assum_override_1,
956 x_assum_override_2 => p_isir_rec.assum_override_2,
957 x_assum_override_3 => p_isir_rec.assum_override_3,
958 x_assum_override_4 => p_isir_rec.assum_override_4,
959 x_assum_override_5 => p_isir_rec.assum_override_5,
960 x_assum_override_6 => p_isir_rec.assum_override_6,
961 x_dependency_status => p_isir_rec.dependency_status,
962 x_s_email_address => p_isir_rec.s_email_address,
963 x_nslds_reason_code => p_isir_rec.nslds_reason_code,
964 x_app_receipt_date => p_isir_rec.app_receipt_date,
965 x_processed_rec_type => p_isir_rec.processed_rec_type,
966 x_hist_correction_for_tran_id => p_isir_rec.hist_correction_for_tran_id,
967 x_system_generated_indicator => p_isir_rec.system_generated_indicator,
968 x_dup_request_indicator => p_isir_rec.dup_request_indicator,
969 x_source_of_correction => p_isir_rec.source_of_correction,
970 x_p_cal_tax_status => p_isir_rec.p_cal_tax_status,
971 x_s_cal_tax_status => p_isir_rec.s_cal_tax_status,
972 x_graduate_flag => p_isir_rec.graduate_flag,
973 x_auto_zero_efc => p_isir_rec.auto_zero_efc,
974 x_efc_change_flag => p_isir_rec.efc_change_flag,
975 x_sarc_flag => p_isir_rec.sarc_flag,
976 x_simplified_need_test => p_isir_rec.simplified_need_test,
977 x_reject_reason_codes => p_isir_rec.reject_reason_codes,
978 x_select_service_match_flag => p_isir_rec.select_service_match_flag,
979 x_select_service_reg_flag => p_isir_rec.select_service_reg_flag,
980 x_ins_match_flag => p_isir_rec.ins_match_flag,
981 x_ins_verification_number => NULL,
982 x_sec_ins_match_flag => p_isir_rec.sec_ins_match_flag,
986 x_ssn_date_of_death => p_isir_rec.ssn_date_of_death,
983 x_sec_ins_ver_number => p_isir_rec.sec_ins_ver_number,
984 x_ssn_match_flag => p_isir_rec.ssn_match_flag,
985 x_ssa_citizenship_flag => p_isir_rec.ssa_citizenship_flag,
987 x_nslds_match_flag => p_isir_rec.nslds_match_flag,
988 x_va_match_flag => p_isir_rec.va_match_flag,
989 x_prisoner_match => p_isir_rec.prisoner_match,
990 x_verification_flag => p_isir_rec.verification_flag,
991 x_subsequent_app_flag => p_isir_rec.subsequent_app_flag,
992 x_app_source_site_code => p_isir_rec.app_source_site_code,
993 x_tran_source_site_code => p_isir_rec.tran_source_site_code,
994 x_drn => p_isir_rec.drn,
995 x_tran_process_date => p_isir_rec.tran_process_date,
996 x_computer_batch_number => p_isir_rec.computer_batch_number,
997 x_correction_flags => p_isir_rec.correction_flags,
998 x_highlight_flags => p_isir_rec.highlight_flags,
999 x_paid_efc => p_isir_rec.paid_efc,
1000 x_primary_efc => p_isir_rec.primary_efc,
1001 x_secondary_efc => p_isir_rec.secondary_efc,
1002 x_fed_pell_grant_efc_type => p_isir_rec.fed_pell_grant_efc_type,
1003 x_primary_efc_type => p_isir_rec.primary_efc_type,
1004 x_sec_efc_type => p_isir_rec.sec_efc_type,
1005 x_primary_alternate_month_1 => p_isir_rec.primary_alternate_month_1,
1006 x_primary_alternate_month_2 => p_isir_rec.primary_alternate_month_2,
1007 x_primary_alternate_month_3 => p_isir_rec.primary_alternate_month_3,
1008 x_primary_alternate_month_4 => p_isir_rec.primary_alternate_month_4,
1009 x_primary_alternate_month_5 => p_isir_rec.primary_alternate_month_5,
1010 x_primary_alternate_month_6 => p_isir_rec.primary_alternate_month_6,
1011 x_primary_alternate_month_7 => p_isir_rec.primary_alternate_month_7,
1012 x_primary_alternate_month_8 => p_isir_rec.primary_alternate_month_8,
1013 x_primary_alternate_month_10 => p_isir_rec.primary_alternate_month_10,
1014 x_primary_alternate_month_11 => p_isir_rec.primary_alternate_month_11,
1015 x_primary_alternate_month_12 => p_isir_rec.primary_alternate_month_12,
1016 x_sec_alternate_month_1 => p_isir_rec.sec_alternate_month_1,
1017 x_sec_alternate_month_2 => p_isir_rec.sec_alternate_month_2,
1018 x_sec_alternate_month_3 => p_isir_rec.sec_alternate_month_3,
1019 x_sec_alternate_month_4 => p_isir_rec.sec_alternate_month_4,
1020 x_sec_alternate_month_5 => p_isir_rec.sec_alternate_month_5,
1021 x_sec_alternate_month_6 => p_isir_rec.sec_alternate_month_6,
1022 x_sec_alternate_month_7 => p_isir_rec.sec_alternate_month_7,
1023 x_sec_alternate_month_8 => p_isir_rec.sec_alternate_month_8,
1024 x_sec_alternate_month_10 => p_isir_rec.sec_alternate_month_10,
1025 x_sec_alternate_month_11 => p_isir_rec.sec_alternate_month_11,
1026 x_sec_alternate_month_12 => p_isir_rec.sec_alternate_month_12,
1027 x_total_income => p_isir_rec.total_income,
1028 x_allow_total_income => p_isir_rec.allow_total_income,
1029 x_state_tax_allow => p_isir_rec.state_tax_allow,
1030 x_employment_allow => p_isir_rec.employment_allow,
1031 x_income_protection_allow => p_isir_rec.income_protection_allow,
1032 x_available_income => p_isir_rec.available_income,
1033 x_contribution_from_ai => p_isir_rec.contribution_from_ai,
1034 x_discretionary_networth => p_isir_rec.discretionary_networth,
1035 x_efc_networth => p_isir_rec.efc_networth,
1036 x_asset_protect_allow => p_isir_rec.asset_protect_allow,
1037 x_parents_cont_from_assets => p_isir_rec.parents_cont_from_assets,
1038 x_adjusted_available_income => p_isir_rec.adjusted_available_income,
1039 x_total_student_contribution => p_isir_rec.total_student_contribution,
1040 x_total_parent_contribution => p_isir_rec.total_parent_contribution,
1041 x_parents_contribution => p_isir_rec.parents_contribution,
1042 x_student_total_income => p_isir_rec.student_total_income,
1043 x_sati => p_isir_rec.sati,
1044 x_sic => p_isir_rec.sic,
1045 x_sdnw => p_isir_rec.sdnw,
1046 x_sca => p_isir_rec.sca,
1047 x_fti => p_isir_rec.fti,
1048 x_secti => p_isir_rec.secti,
1049 x_secati => p_isir_rec.secati,
1050 x_secstx => p_isir_rec.secstx,
1051 x_secea => p_isir_rec.secea,
1052 x_secipa => p_isir_rec.secipa,
1053 x_secai => p_isir_rec.secai,
1054 x_seccai => p_isir_rec.seccai,
1055 x_secdnw => p_isir_rec.secdnw,
1056 x_secnw => p_isir_rec.secnw,
1057 x_secapa => p_isir_rec.secapa,
1058 x_secpca => p_isir_rec.secpca,
1062 x_secpc => p_isir_rec.secpc,
1059 x_secaai => p_isir_rec.secaai,
1060 x_sectsc => p_isir_rec.sectsc,
1061 x_sectpc => p_isir_rec.sectpc,
1063 x_secsti => p_isir_rec.secsti,
1064 x_secsic => p_isir_rec.secsic,
1065 x_secsati => p_isir_rec.secsati,
1066 x_secsdnw => p_isir_rec.secsdnw,
1067 x_secsca => p_isir_rec.secsca,
1068 x_secfti => p_isir_rec.secfti,
1069 x_a_citizenship => p_isir_rec.a_citizenship,
1070 x_a_student_marital_status => p_isir_rec.a_student_marital_status,
1071 x_a_student_agi => p_isir_rec.a_student_agi,
1072 x_a_s_us_tax_paid => p_isir_rec.a_s_us_tax_paid,
1073 x_a_s_income_work => p_isir_rec.a_s_income_work,
1074 x_a_spouse_income_work => p_isir_rec.a_spouse_income_work,
1075 x_a_s_total_wsc => p_isir_rec.a_s_total_wsc,
1076 x_a_date_of_birth => p_isir_rec.a_date_of_birth,
1077 x_a_student_married => p_isir_rec.a_student_married,
1078 x_a_have_children => p_isir_rec.a_have_children,
1079 x_a_s_have_dependents => p_isir_rec.a_s_have_dependents,
1080 x_a_va_status => p_isir_rec.a_va_status,
1081 x_a_s_num_in_family => p_isir_rec.a_s_num_in_family,
1082 x_a_s_num_in_college => p_isir_rec.a_s_num_in_college,
1083 x_a_p_marital_status => p_isir_rec.a_p_marital_status,
1084 x_a_father_ssn => p_isir_rec.a_father_ssn,
1085 x_a_mother_ssn => p_isir_rec.a_mother_ssn,
1086 x_a_parents_num_family => p_isir_rec.a_parents_num_family,
1087 x_a_parents_num_college => p_isir_rec.a_parents_num_college,
1088 x_a_parents_agi => p_isir_rec.a_parents_agi,
1089 x_a_p_us_tax_paid => p_isir_rec.a_p_us_tax_paid,
1090 x_a_f_work_income => p_isir_rec.a_f_work_income,
1091 x_a_m_work_income => p_isir_rec.a_m_work_income,
1092 x_a_p_total_wsc => p_isir_rec.a_p_total_wsc,
1093 x_comment_codes => p_isir_rec.comment_codes,
1094 x_sar_ack_comm_code => p_isir_rec.sar_ack_comm_code,
1095 x_pell_grant_elig_flag => p_isir_rec.pell_grant_elig_flag,
1096 x_reprocess_reason_code => p_isir_rec.reprocess_reason_code,
1097 x_duplicate_date => p_isir_rec.duplicate_date,
1098 x_isir_transaction_type => p_isir_rec.isir_transaction_type,
1099 x_fedral_schl_code_indicator => p_isir_rec.fedral_schl_code_indicator,
1100 x_multi_school_code_flags => p_isir_rec.multi_school_code_flags,
1101 x_dup_ssn_indicator => p_isir_rec.dup_ssn_indicator,
1102 x_system_record_type => p_isir_rec.system_record_type,
1103 x_payment_isir => p_isir_rec.payment_isir,
1104 x_receipt_status => p_isir_rec.receipt_status,
1105 x_isir_receipt_completed => p_isir_rec.isir_receipt_completed,
1106 x_active_isir => p_isir_rec.active_isir,
1107 x_fafsa_data_verify_flags => p_isir_rec.fafsa_data_verify_flags,
1108 x_reject_override_a => p_isir_rec.reject_override_a,
1109 x_reject_override_c => p_isir_rec.reject_override_c,
1110 x_parent_marital_status_date => p_isir_rec.parent_marital_status_date,
1111 x_legacy_record_flag => p_isir_rec.legacy_record_flag,
1112 x_verif_track_flag => p_isir_rec.verif_track_flag,
1113 x_father_first_name_initial => p_isir_rec.father_first_name_initial_txt,
1114 x_father_step_father_birth_dt => p_isir_rec.father_step_father_birth_date,
1115 x_mother_first_name_initial => p_isir_rec.mother_first_name_initial_txt,
1116 x_mother_step_mother_birth_dt => p_isir_rec.mother_step_mother_birth_date,
1117 x_parents_email_address_txt => p_isir_rec.parents_email_address_txt,
1118 x_address_change_type => p_isir_rec.address_change_type,
1119 x_cps_pushed_isir_flag => p_isir_rec.cps_pushed_isir_flag,
1120 x_electronic_transaction_type => p_isir_rec.electronic_transaction_type,
1121 x_sar_c_change_type => p_isir_rec.sar_c_change_type,
1122 x_father_ssn_match_type => p_isir_rec.father_ssn_match_type,
1123 x_mother_ssn_match_type => p_isir_rec.mother_ssn_match_type,
1124 x_reject_override_g_flag => p_isir_rec.reject_override_g_flag,
1125 x_dhs_verification_num_txt => p_isir_rec.dhs_verification_num_txt,
1126 x_data_file_name_txt => p_isir_rec.data_file_name_txt ,
1127 x_message_class_txt => p_isir_rec.message_class_txt,
1128 x_reject_override_3_flag => p_isir_rec.reject_override_3_flag,
1129 x_reject_override_12_flag => p_isir_rec.reject_override_12_flag,
1130 x_reject_override_j_flag => p_isir_rec.reject_override_j_flag,
1131 x_reject_override_k_flag => p_isir_rec.reject_override_k_flag,
1132 x_rejected_status_change_flag => p_isir_rec.rejected_status_change_flag,
1133 x_verification_selection_flag => p_isir_rec.verification_selection_flag
1134 );
1138
1135 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1136 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.update_correction_isir.debug','igf_ap_isir_matched_pkg.update_row successfull');
1137 END IF;
1139 EXCEPTION
1140 WHEN OTHERS THEN
1141 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1142 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_BATCH_VER_PRC.UPDATE_CORRECTION_ISIR '||SQLERRM);
1143 IGS_GE_MSG_STACK.ADD;
1144 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1145 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.update_correction_isir.debug',SQLERRM);
1146 END IF;
1147 APP_EXCEPTION.RAISE_EXCEPTION;
1148 END update_correction_isir;
1149
1150
1151 FUNCTION l_incomplete(p_base_id igf_ap_fa_base_rec_all.base_id%TYPE ) RETURN BOOLEAN IS
1152
1153 /*
1154 || Created By : rasingh
1155 || Created On : 06-July-2001
1156 || Purpose : This function checks the all verification item values, if any item having null values
1157 || than it sets the incomplete status and returns true or false.
1158 || Change History :
1159 || Who When What
1160 || (reverse chronological order - newest change first)
1161 */
1162
1163 CURSOR cur_get_ver_data (pn_base_id NUMBER) IS
1164 SELECT COUNT(ivi.isir_map_col)
1165 FROM igf_ap_inst_ver_item ivi
1166 WHERE ivi.base_id = pn_base_id
1167 AND ivi.waive_flag <> 'Y'
1168 AND ( ivi.item_value IS NULL AND NVL(USE_BLANK_FLAG,'N') <> 'Y' )
1169 AND rownum = 1;
1170
1171 lv_flag NUMBER := 0;
1172 lv_ver_status igf_ap_fa_base_rec_all.fed_verif_status%TYPE;
1173 lb_return_status BOOLEAN;
1174
1175 BEGIN
1176
1177 OPEN cur_get_ver_data (p_base_id);
1178 FETCH cur_get_ver_data INTO lv_flag;
1179 CLOSE cur_get_ver_data;
1180
1181 IF lv_flag > 0 THEN
1182 FND_MESSAGE.SET_NAME('IGF','IGF_AP_INCOMPLETE_DOC_VAL');
1183 FND_FILE.PUT_LINE(FND_FILE.LOG,fnd_message.get);
1184 lb_return_status := TRUE;
1185 ELSE
1186 lb_return_status := FALSE;
1187 END IF;
1188
1189 RETURN lb_return_status;
1190
1191 EXCEPTION
1192 WHEN OTHERS THEN
1193 IF cur_get_ver_data%ISOPEN THEN
1194 CLOSE cur_get_ver_data;
1195 END IF;
1196 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1197 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_BATCH_VER_PRC.l_incomplete '||SQLERRM);
1198 IGS_GE_MSG_STACK.ADD;
1199 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1200 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.l_incomplete.debug',SQLERRM);
1201 END IF;
1202 APP_EXCEPTION.RAISE_EXCEPTION;
1203 END l_incomplete;
1204
1205
1206 PROCEDURE l_create_corr_record(
1207 pn_base_id igf_ap_fa_base_rec.base_id%TYPE ,
1208 pn_isir_id igf_ap_isir_matched.isir_id%TYPE ,
1209 pv_isir_map_column igf_ap_inst_ver_item.isir_map_col%TYPE,
1210 pv_cal_type igs_ca_inst.cal_type%TYPE,
1211 pn_sequence_number igs_ca_inst.sequence_number%TYPE,
1212 pv_retval VARCHAR2,
1213 pv_item_value igf_ap_inst_ver_item.item_value%TYPE,
1214 pv_mode VARCHAR2
1215 ) IS
1216
1217 lv_rowid VARCHAR2(30);
1218 ln_isirc_id igf_ap_isir_corr.isirc_id%TYPE;
1219
1220 CURSOR upd_corr_cur IS
1221 SELECT corr.*,corr.rowid
1222 FROM igf_ap_isir_corr corr
1223 WHERE isir_id = pn_isir_id
1224 AND sar_field_number = pv_isir_map_column;
1225
1226 BEGIN
1227
1228 IF pv_mode = 'INSERT' THEN
1229
1230 igf_ap_isir_corr_pkg.insert_row
1231 (
1232 X_ROWID => lv_rowid,
1233 X_ISIRC_ID => ln_isirc_id,
1234 X_ISIR_ID => pn_isir_id,
1235 X_CI_SEQUENCE_NUMBER => pn_sequence_number,
1236 X_CI_CAL_TYPE => pv_cal_type,
1237 X_SAR_FIELD_NUMBER => pv_isir_map_column,
1238 X_ORIGINAL_VALUE => pv_retval,
1239 X_BATCH_ID => NULL,
1240 X_CORRECTED_VALUE => pv_item_value,
1241 X_CORRECTION_STATUS => NVL(l_new_corr_status,'READY'),
1242 X_MODE => 'R'
1243 );
1244 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1245 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.l_create_corr_record.debug','igf_ap_isir_corr_pkg.insert_row successfull with values ');
1246 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.l_create_corr_record.debug','Values : ln_isirc_id/pn_isir_id/pn_sequence_number : '||ln_isirc_id||' / '||pn_isir_id||' / '||pn_sequence_number);
1247 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.l_create_corr_record.debug','Values : pv_cal_type/pv_isir_map_column/pv_retval/pv_item_value'||pv_cal_type||'/'||pv_isir_map_column||'/'||pv_retval||'/'||pv_item_value);
1248 END IF;
1249 ELSIF pv_mode = 'UPDATE' THEN
1250 FOR corr_upd_rec IN upd_Corr_cur LOOP
1251 igf_ap_isir_corr_pkg.update_row
1252 (
1253 X_ROWID => corr_upd_rec.rowid,
1254 X_ISIRC_ID => corr_upd_rec.isirc_id,
1255 X_ISIR_ID => corr_upd_rec.isir_id,
1259 X_ORIGINAL_VALUE => pv_retval,
1256 X_CI_SEQUENCE_NUMBER => corr_upd_rec.ci_sequence_number,
1257 X_CI_CAL_TYPE => corr_upd_rec.ci_cal_type,
1258 X_SAR_FIELD_NUMBER => corr_upd_rec.sar_field_number,
1260 X_BATCH_ID => NULL,
1261 X_CORRECTED_VALUE => pv_item_value,
1262 X_CORRECTION_STATUS => l_new_corr_status,
1263 X_MODE => 'R'
1264 );
1265 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1266 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.l_create_corr_record.debug','igf_ap_isir_corr_pkg.update_row successfull');
1267 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.l_create_corr_record.debug','Values : corr_upd_rec.rowid / corr_upd_rec.isirc_id : '||corr_upd_rec.rowid||' / '||corr_upd_rec.isirc_id);
1268 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.l_create_corr_record.debug','Values : corr_upd_rec.isir_id / corr_upd_rec.ci_sequence_number : '||corr_upd_rec.isir_id||' / '||corr_upd_rec.ci_sequence_number);
1269 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.l_create_corr_record.debug','Values : corr_upd_rec.ci_cal_type / corr_upd_rec.sar_field_number : '||corr_upd_rec.ci_cal_type||' / '||corr_upd_rec.sar_field_number);
1270 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.l_create_corr_record.debug','Values : pv_retval / pv_item_value / l_new_corr_status : '||pv_retval / pv_item_value||' / '||l_new_corr_status);
1271 END IF;
1272 END LOOP;
1273 END IF;
1274 EXCEPTION
1275 WHEN OTHERS THEN
1276 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1277 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_BATCH_VER_PRC.L_CREATE_CORR_RECORD '||SQLERRM);
1278 IGS_GE_MSG_STACK.ADD;
1279 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1280 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.l_create_corr_record.debug',SQLERRM);
1281 END IF;
1282 RETURN;
1283 END l_create_corr_record;
1284
1285
1286 PROCEDURE l_validate_data ( pn_base_id IN igf_ap_fa_base_rec.base_id%TYPE,
1287 pv_isir_id IN igf_ap_isir_matched.isir_id%TYPE,
1288 pv_item_value IN igf_ap_inst_ver_item.item_value%TYPE,
1289 pv_column_name IN igf_fc_sar_cd_mst.sar_field_name%TYPE, --gets the SAR Field name
1290 pv_isir_map_column IN igf_ap_inst_ver_item.isir_map_col%TYPE,
1291 pv_ci_cal_type IN igf_ap_fa_base_rec.ci_cal_type%TYPE,
1292 pv_ci_sequence_number IN igf_ap_fa_base_rec.ci_sequence_number%TYPE ,
1293 pv_update_ssn IN OUT NOCOPY VARCHAR2
1294 ) IS
1295 /*
1296 || Created By : rasingh
1297 || Created On : 05-Dec-2000
1298 || Purpose : This procedure validates verification value to ISIR record value and
1299 || creates the correction record if those values are not same.
1300 || Change History :
1301 || Who When What
1302 || masehgal 14-May-2003 # 2885882 FACR113 SAR Updates
1303 || changed cursors
1304 || (reverse chronological order - newest change first)
1305 */
1306
1307 lv_cur PLS_INTEGER;
1308 lv_retval igf_ap_isir_corr.original_value%TYPE;
1309 lv_stmt VARCHAR2(2000);
1310 lv_rows integer;
1311 ln_isir_id igf_ap_isir_matched.isir_id%TYPE := pv_isir_id;
1312 lv_cal_type igs_ca_inst.cal_type%TYPE := pv_ci_cal_type;
1313 ln_sequence_number igs_ca_inst.sequence_number%TYPE := pv_ci_sequence_number;
1314 lv_item_value igf_ap_inst_ver_item.item_value%TYPE;
1315 l_mode VARCHAR2(30);
1316
1317 CURSOR cur_get_sar_field_desc (pv_column_name igf_fc_sar_cd_mst.sar_field_name%TYPE,
1318 pv_lookup_type VARCHAR2) IS
1319 SELECT lkup.meaning
1320 FROM igf_lookups_view lkup
1321 WHERE lkup.lookup_type = pv_lookup_type
1322 AND lkup.lookup_code = pv_column_name
1323 AND lkup.enabled_flag = 'Y' ;
1324
1325 l_column_name igf_fc_sar_cd_mst.sar_field_name%TYPE ;
1326 l_lookup_type VARCHAR2(60) ;
1327 l_sar_col_desc igf_lookups_view.meaning%TYPE ;
1328
1329 BEGIN
1330
1331 IF pv_column_name <> 'S_EMAIL_ADDRESS' THEN
1332
1333 lv_item_value := pv_item_value;
1334
1335 -- Get the isir_id of the latest ISIR for the student
1336 IF pv_isir_id IS NULL THEN
1337 fnd_message.set_name('IGF','IGF_AP_NO_ISIR_RECS_EXIST');
1338 fnd_file.put_line(fnd_file.log,fnd_message.get);
1339 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1340 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.l_create_corr_record.debug','No ISIR record exists');
1341 END IF;
1342 ELSE
1343
1344 IF pv_column_name IS NOT NULL THEN
1345 lv_cur := DBMS_SQL.OPEN_CURSOR;
1346 lv_stmt := 'SELECT '||pv_column_name ||' FROM igf_ap_isir_matched where isir_id = '||to_char(pv_isir_id);
1347
1348 DBMS_SQL.PARSE(lv_cur,lv_stmt,2);
1349 DBMS_SQL.DEFINE_COLUMN(lv_cur,1,lv_retval,30);
1350 lv_rows := DBMS_SQL.EXECUTE_AND_FETCH(lv_cur);
1351 DBMS_SQL.COLUMN_VALUE(lv_cur,1,lv_retval);
1355 -- Processing for String Values.
1352 DBMS_SQL.CLOSE_CURSOR(lv_cur);
1353 END IF;
1354
1356 IF UPPER(LTRIM(RTRIM(NVL(lv_retval,'#')))) <> UPPER(LTRIM(RTRIM(NVL(lv_item_value,'#')))) THEN
1357 ln_corr_count := ln_corr_count + 1;
1358 IF l_chk_dup_corr (ln_isir_id,TO_NUMBER(pv_isir_map_column),lv_item_value,l_mode) THEN
1359 l_lookup_type := 'IGF_AP_SAR_FIELD_MAP';
1360 OPEN cur_get_sar_field_desc ( pv_column_name , l_lookup_type) ;
1361 FETCH cur_get_sar_field_desc INTO l_sar_col_desc ;
1362 CLOSE cur_get_sar_field_desc ;
1363
1364 fnd_message.set_name('IGF','IGF_AP_CORR_REC_CREATED');
1365 fnd_message.set_token ('ITEM',l_sar_col_desc );
1366 fnd_file.put_line(fnd_file.log, ' ' || fnd_message.get);
1367 l_create_corr_record ( pn_base_id,ln_isir_id,pv_isir_map_column,lv_cal_type, ln_sequence_number, lv_retval,lv_item_value,l_mode);
1368
1369 -- Update the Correction ISIR with the correction Value
1370 EXECUTE IMMEDIATE 'BEGIN igf_ap_batch_ver_prc_pkg.lp_isir_rec.'
1371 || pv_column_name || ' := ' || '''' || lv_item_value || '''' || ' ; END;' ;
1372
1373 -- Check if the SSN is getting updated
1374 IF pv_column_name = 'CURRENT_SSN' THEN
1375 pv_update_ssn := 'Y' ;
1376 END IF;
1377 END IF;
1378 END IF;
1379
1380 END IF; -- End of ISIR IS NULL
1381 END IF;
1382
1383 EXCEPTION
1384 WHEN OTHERS THEN
1385 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1386 fnd_message.set_token('NAME','IGF_AP_BATCH_VER_PRC.L_VALIDATE_DATA '||SQLERRM);
1387 igs_ge_msg_stack.add;
1388 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1389 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.l_validate_data.debug',SQLERRM);
1390 END IF;
1391 RETURN;
1392 END l_validate_data;
1393
1394
1395 PROCEDURE main (
1396 errbuf OUT NOCOPY VARCHAR2,
1397 retcode OUT NOCOPY NUMBER,
1398 p_award_year VARCHAR2,
1399 p_org_id IN NUMBER
1400 ) IS
1401 /**********************************************************
1402 Created By :
1403 Date Created By :
1404 Purpose :
1405 Know limitations, enhancements or remarks
1406
1407 Change History
1408 Who When What
1409 bkkumar 05-Dec-2003 Bug# 3240804 Removed the base_id parameter.
1410 Enforced the validation of when student has
1411 any correction items = Batched (or Verification
1412 Process Status = Corrections Sent) then no
1413 correction items can be created
1414 masehgal 14-May-2003 # 2885882 FACR113 SAR Updates
1415 changed cursor cur_get_ver_data, call to validate_data
1416 smvk 04-Feb-2003 Removed the status 'INCOMPLETE' from the cursor cur_selected_rec.
1417 As per Enh Bug # 2758812.
1418 (reverse chronological order - newest change first)
1419 ***************************************************************/
1420
1421 CURSOR cur_selected_rec (lv_ci_cal_type VARCHAR2,
1422 lv_ci_sequence_number NUMBER) IS
1423 SELECT far.base_id,
1424 far.ci_cal_type,
1425 far.ci_sequence_number,
1426 far.person_id,
1427 pe.party_number person_number,
1428 pe.person_first_name given_names,
1429 pe.person_last_name surname,
1430 im.isir_id
1431 FROM igf_ap_fa_base_rec_all far,
1432 igf_ap_isir_matched_all im,
1433 hz_parties pe
1434 WHERE far.person_id = pe.party_id
1435 AND far.ci_cal_type = NVL(lv_ci_cal_type,far.ci_cal_type)
1436 AND far.ci_sequence_number = NVL(lv_ci_sequence_number, far.ci_sequence_number)
1437 AND far.fed_verif_status IN ('SELECTED', 'NOTVERIFIED')
1438 AND far.base_id = im.base_id
1439 AND im.system_record_type = 'ORIGINAL'
1440 AND im.payment_isir = 'Y';
1441
1442
1443 CURSOR cur_chk_batched ( cp_base_id igf_ap_isir_matched_all.base_id%TYPE,
1444 cp_corr_status igf_ap_isir_corr_all.correction_status%TYPE
1445 )
1446 IS
1447 SELECT isir.base_id
1448 FROM igf_ap_isir_matched_all isir,
1449 igf_ap_isir_corr_all corr
1450 WHERE isir.base_id = cp_base_id
1451 AND isir.isir_id = corr.isir_id
1452 AND NVL(corr.correction_status,'X') = cp_corr_status;
1453
1454 l_cur_chk_batched cur_chk_batched%ROWTYPE;
1455
1456 CURSOR cur_get_ver_data ( pn_base_id NUMBER ) IS
1457 SELECT ivi.base_id, ivi.udf_vern_item_seq_num, ivi.item_value, ivi.isir_map_col,
1458 ivi.incl_in_tolerance, sar.sar_field_name column_name
1459 FROM igf_ap_batch_aw_map map,
1460 igf_ap_fa_base_rec_all fabase,
1461 igf_ap_inst_ver_item ivi,
1462 Igf_fc_sar_cd_mst sar
1463 WHERE fabase.base_id = pn_base_id
1464 AND map.ci_cal_type = fabase.ci_cal_type
1465 AND map.ci_sequence_number = fabase.ci_sequence_number
1466 AND ivi.base_id = pn_base_id
1467 AND sar.sys_award_year = map.sys_award_year
1471 (ivi.item_value IS NULL AND NVL(USE_BLANK_FLAG,'N') = 'Y' )
1468 AND sar.sar_field_number = ivi.isir_map_col
1469 AND ivi.waive_flag <> 'Y'
1470 AND ( (ivi.item_value IS NOT NULL) OR
1472 );
1473
1474 CURSOR cur_get_ver_item_count(
1475 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
1476 ) IS
1477 SELECT ivi.base_id
1478 FROM igf_ap_inst_ver_item ivi
1479 WHERE ivi.base_id = cp_base_id
1480 AND ivi.waive_flag <> 'Y'
1481 AND ( (ivi.item_value IS NOT NULL) OR
1482 (ivi.item_value IS NULL AND NVL(USE_BLANK_FLAG,'N') = 'Y' )
1483 )
1484 AND rownum = 1;
1485
1486 lc_get_ver_item_count cur_get_ver_item_count%ROWTYPE;
1487
1488 CURSOR c_correction_isir ( cp_base_id igf_ap_fa_base_rec.base_id%TYPE) IS
1489 SELECT *
1490 FROM igf_ap_isir_matched
1491 WHERE BASE_ID = cp_base_id
1492 AND SYSTEM_RECORD_TYPE = 'CORRECTION';
1493
1494 CURSOR c_get_sys_year(
1495 cp_cal_type VARCHAR2,
1496 cp_sequence_number NUMBER
1497 ) IS
1498 SELECT sys_award_year
1499 FROM igf_ap_batch_aw_map
1500 WHERE ci_cal_type = cp_cal_type
1501 AND ci_sequence_number = cp_sequence_number ;
1502
1503 lv_get_sys_year c_get_sys_year%ROWTYPE;
1504
1505 rec_selected cur_selected_rec%ROWTYPE ;
1506 rec_get_ver_data cur_get_ver_data%ROWTYPE ;
1507 lv_ci_cal_type IGS_CA_INST.CAL_TYPE%TYPE;
1508 lv_ci_sequence_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE;
1509 lv_ver_status VARCHAR2(30);
1510 ln_tot_count NUMBER(15);
1511 lv_efc_isir_rec igf_ap_isir_matched%ROWTYPE;
1512 p_efc_ret_status VARCHAR2(30);
1513 p_msg_count NUMBER;
1514 p_msg_text VARCHAR2(2000);
1515 lv_update_ssn VARCHAR2(1);
1516 SKIP_STUDENT EXCEPTION;
1517 ln_corr_isir_id igf_ap_isir_matched_all.isir_id%TYPE;
1518
1519
1520 BEGIN
1521
1522 igf_aw_gen.set_org_id(p_org_id);
1523 lv_ci_cal_type := RTRIM(SUBSTR(p_award_year,1,10));
1524 lv_ci_sequence_number := TO_NUMBER(RTRIM(SUBSTR(p_award_year,11)));
1525
1526 -- Retrieve the System Award Year
1527 OPEN c_get_sys_year(lv_ci_cal_type,lv_ci_sequence_number);
1528 FETCH c_get_sys_year INTO lv_get_sys_year;
1529 CLOSE c_get_sys_year ;
1530
1531 -- Initialize Count variables:
1532 ln_tot_count := 0;
1533 ln_tot_no_corr_count := 0;
1534 ln_tot_corr_count := 0;
1535
1536 -- Start the process by getting all the students for which
1537 -- the verification process is initiated.
1538 FOR rec_selected IN cur_selected_rec(lv_ci_cal_type, lv_ci_sequence_number) LOOP
1539
1540 fnd_message.set_name('IGF','IGF_AP_PROCESSING_STUDENT');
1541 fnd_message.set_token ('PERSON_NAME',rec_selected.given_names ||' '||rec_selected.surname);
1542 fnd_message.set_token ('PERSON_NUMBER',rec_selected.person_number);
1543 fnd_file.put_line(fnd_file.log,fnd_message.get);
1544 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1545 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.main.debug','Procssing student '||rec_selected.given_names ||' '||rec_selected.surname );
1546 END IF;
1547 ln_tot_count := ln_tot_count + 1;
1548
1549 l_cur_chk_batched := NULL;
1550 OPEN cur_chk_batched(rec_selected.base_id,'BATCHED');
1551 FETCH cur_chk_batched INTO l_cur_chk_batched;
1552 CLOSE cur_chk_batched;
1553
1554 -- Run this process only for those records for which the item value is present. l_incomplete will return TRUE if all the verification items have some value.
1555 IF( l_cur_chk_batched.base_id IS NULL AND (NOT l_incomplete(rec_selected.base_id)) ) THEN
1556
1557 ln_corr_count := 0;
1558 ln_no_corr_count := 0;
1559
1560 -- Load the current Active Correction ISIR for the Student
1561 -- Initialize the pkg rowtype to Null , Note this is used for dynamic update of correction ISIR.
1562 BEGIN
1563
1564 SAVEPOINT IGFAP08B_SP;
1565
1566 -- If Verifications Items are not present for the student, then log a message and skip the student.
1567 -- If Verification items are present then proceed further
1568 OPEN cur_get_ver_item_count(rec_selected.base_id);
1569 FETCH cur_get_ver_item_count INTO lc_get_ver_item_count;
1570 IF cur_get_ver_item_count%NOTFOUND THEN
1571 CLOSE cur_get_ver_item_count;
1572 fnd_message.set_name('IGF','IGF_AP_STDNT_SKIP_NO_VERIF');
1573 fnd_file.put_line(fnd_file.log,fnd_message.get);
1574 fnd_file.new_line(fnd_file.log,2);
1575 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1576 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.main.debug','No verification items present , Therefore skipping the student');
1577 END IF;
1578 RAISE SKIP_STUDENT;
1579 ELSE
1580 CLOSE cur_get_ver_item_count;
1581 END IF;
1582
1583 -- If Correction ISIR is already present, then update the Correction ISIR with the Verificaiton items
1584 -- else create the Correction ISIR and then update the Correction ISIR with the Verificaiton Items
1585 igf_ap_batch_ver_prc_pkg.lp_isir_rec := NULL;
1586 OPEN c_correction_isir(rec_selected.base_id);
1590
1587 FETCH c_correction_isir INTO igf_ap_batch_ver_prc_pkg.lp_isir_rec;
1588 IF c_correction_isir%FOUND THEN
1589 CLOSE c_correction_isir ;
1591 ELSIF c_correction_isir%NOTFOUND THEN
1592 CLOSE c_correction_isir;
1593
1594 -- Create the CORRECTION ISIR
1595 ln_corr_isir_id := NULL;
1596 create_correction_isir( rec_selected.base_id, ln_corr_isir_id );
1597 IF ln_corr_isir_id IS NULL THEN
1598 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1599 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.main.debug','Could not create correction record, Therefore skipping the student');
1600 END IF;
1601 RAISE SKIP_STUDENT;
1602 END IF;
1603
1604 -- Fetch the Correction ISIR and add the Verifiaction items
1605 igf_ap_batch_ver_prc_pkg.lp_isir_rec := NULL;
1606 OPEN c_correction_isir(rec_selected.base_id);
1607 FETCH c_correction_isir INTO igf_ap_batch_ver_prc_pkg.lp_isir_rec;
1608 CLOSE c_correction_isir ;
1609
1610 END IF;
1611
1612 --
1613 -- For the selected student, start processing the Verification Items.
1614 --
1615 lv_update_ssn := 'N' ;
1616
1617 FOR rec_get_ver_data IN cur_get_ver_data (rec_selected.base_id) LOOP
1618 l_validate_data ( pn_base_id => rec_get_ver_data.base_id,
1619 pv_isir_id => rec_selected.isir_id,
1620 pv_item_value => rec_get_ver_data.item_value,
1621 pv_column_name => rec_get_ver_data.column_name, -- we are now passing the SAR Field name as in the Seed Table
1622 pv_isir_map_column => rec_get_ver_data.isir_map_col,
1623 pv_ci_cal_type => rec_selected.ci_cal_type,
1624 pv_ci_sequence_number => rec_selected.ci_sequence_number,
1625 pv_update_ssn => lv_update_ssn
1626 );
1627 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1628 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.main.debug','l_validate_data sucessfull');
1629 END IF;
1630 END LOOP;
1631
1632 -- Compute EFC Against the updated correction ISIR
1633 lv_efc_isir_rec := igf_ap_batch_ver_prc_pkg.lp_isir_rec ;
1634
1635 FND_MSG_PUB.Initialize ;
1636
1637 -- Call EFC Calculation with Ignore Warnings set to 'N'
1638 igf_ap_efc_calc.calculate_efc(
1639 p_isir_rec => lv_efc_isir_rec,
1640 p_ignore_warnings => 'N',
1641 p_sys_batch_yr => lv_get_sys_year.sys_award_year,
1642 p_return_status => p_efc_ret_status
1643 );
1644 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1645 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.main.debug','igf_ap_efc_calc.calculate_efc sucessfull');
1646 END IF;
1647
1648 IF p_efc_ret_status = 'W' THEN
1649 -- Computation stopped with Warnings.
1650 -- So re-submit with Ignore Warnings
1651 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1652 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.main.debug','EFC Computation stopped with Warnings.So re-submit with Ignore Warnings');
1653 END IF;
1654
1655 igf_ap_efc_calc.calculate_efc(
1656 p_isir_rec => lv_efc_isir_rec,
1657 p_ignore_warnings => 'Y',
1658 p_sys_batch_yr => lv_get_sys_year.sys_award_year,
1659 p_return_status => p_efc_ret_status
1660 );
1661 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1662 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.main.debug','igf_ap_efc_calc.calculate_efc sucessfull');
1663 END IF;
1664 END IF;
1665
1666 -- Now check the final return value of p_efc_ret_status
1667 IF p_efc_ret_status = 'S' THEN
1668
1669 IF ln_corr_count = 0 THEN
1670
1671 lv_ver_status := 'ACCURATE';
1672
1673 -- Update FA Process Status in FA Base Record.
1674 igf_ap_batch_ver_prc_pkg.update_process_status (rec_selected.base_id,lv_ver_status);
1675 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1676 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.main.debug','Updated FA Process Status in FA Base Record');
1677 END IF;
1678 ln_tot_no_corr_count := ln_tot_no_corr_count+1;
1679 fnd_message.set_name ('IGF','IGF_AP_VER_STAT_ACCURATE');
1680 fnd_file.put_line(fnd_file.log,fnd_message.get);
1681
1682 ELSIF ln_corr_count <> 0 THEN
1683
1684 -- Update the Correction ISIR with Correction Items Values in Federal Verification Worksheet.
1685 -- Note since we have loaded all the corrections into lv_efc_isir_rec Just pass this rowtype variable
1689 p_update_ssn => lv_update_ssn );
1686 -- For Update of Correction ISIR.
1687 update_correction_isir (pn_base_id => rec_selected.base_id,
1688 p_isir_rec => lv_efc_isir_rec,
1690 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1691 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.main.debug','Correction Records were created for the student and Verification Process Status set to Corrections Sent');
1692 END IF;
1693 ln_tot_corr_count := ln_tot_corr_count + 1;
1694 END IF;
1695
1696 ELSE
1697 -- Efc failed due to errors.
1698 -- PRINT ALL ERRORS
1699 p_msg_count := FND_MSG_PUB.Count_Msg();
1700
1701 IF NVL(p_msg_count,0) > 0 THEN
1702 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1703 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.main.debug','EFC computation for the context student has failed.');
1704 END IF;
1705 FND_MESSAGE.SET_NAME('IGF','IGF_AP_EFC_FAIL');
1706 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1707
1708 FOR i in 1..p_msg_count LOOP
1709 p_msg_text := fnd_msg_pub.get(i,'F');
1710 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ' || TO_CHAR(i) || '.' || p_msg_text);
1711 END LOOP;
1712 END IF;
1713 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1714 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.main.debug','Since EFC could not be computed with given correction items, No correction item has been created for this Student.');
1715 END IF;
1716 FND_MESSAGE.SET_NAME('IGF','IGF_AP_CORR_SKIP');
1717 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1718 ROLLBACK TO IGFAP08B_SP;
1719
1720 END IF;
1721
1722 fnd_file.put_line(fnd_file.log,RPAD('-',100,'-'));
1723
1724 EXCEPTION
1725 WHEN SKIP_STUDENT THEN
1726 NULL;
1727
1728 WHEN OTHERS THEN
1729 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1730 fnd_message.set_token('NAME','IGF_AP_BATCH_VER_PRC.MAIN-INNER '||SQLERRM);
1731 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1732 FND_MESSAGE.SET_NAME('IGF','IGF_AP_CORR_SKIP');
1733 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1734 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1735 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.main-inner.debug',SQLERRM);
1736 END IF;
1737 ROLLBACK TO IGFAP08B_SP;
1738 END;
1739 ELSE
1740 IF l_cur_chk_batched.base_id IS NOT NULL THEN
1741 fnd_message.set_name('IGF','IGF_AP_COR_BATCH_EXIST');
1742 fnd_file.put_line(fnd_file.log,fnd_message.get);
1743 END IF;
1744 fnd_file.put_line(fnd_file.log,RPAD('-',100,'-'));
1745
1746 END IF;
1747
1748 END LOOP;
1749
1750 IF ln_tot_count = 0 THEN
1751 fnd_message.set_name('IGF','IGF_AP_MATCHING_REC_NT_FND');
1752 fnd_file.put_line(fnd_file.log,fnd_message.get);
1753 END IF;
1754
1755 fnd_message.set_name('IGF','IGF_AP_TOTAL_RECS');
1756 fnd_message.set_token('COUNT',TO_CHAR(ln_tot_count));
1757 fnd_file.put_line(fnd_file.output,fnd_message.get);
1758
1759 fnd_message.set_name('IGF','IGF_AP_COMPLETED_RECS');
1760 fnd_message.set_token('COUNT',TO_CHAR(ln_tot_count-ln_tot_corr_count));
1761 fnd_file.put_line(fnd_file.output,fnd_message.get);
1762
1763 fnd_message.set_name('IGF','IGF_AP_CORR_RECS');
1764 fnd_message.set_token('COUNT',TO_CHAR(ln_tot_corr_count));
1765 fnd_file.put_line(fnd_file.output,fnd_message.get);
1766
1767 retcode := 0;
1768 EXCEPTION
1769 WHEN others THEN
1770 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1771 fnd_message.set_token('NAME','IGF_AP_BATCH_VER_PRC.main');
1772 igs_ge_msg_stack.add;
1773 igs_ge_msg_stack.conc_exception_hndl;
1774 fnd_file.put_line(fnd_file.log,'ERROR: '||SQLERRM);
1775 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1776 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.main.debug',SQLERRM);
1777 END IF;
1778 app_exception.raise_exception;
1779 END main;
1780
1781
1782 PROCEDURE update_process_status(
1783 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1784 p_fed_verif_status igf_ap_fa_base_rec_all.fed_verif_status%TYPE
1785 ) IS
1786
1787 CURSOR cur_todo(
1788 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1789 p_person_id igf_ap_fa_base_rec_all.person_id%TYPE
1790 ) IS
1791 SELECT 1
1792 FROM igf_ap_td_item_inst_all tdinst,
1793 igf_ap_td_item_mst_all tdmst
1794 WHERE tdinst.base_id = p_base_id
1795 AND tdinst.status IN ('INC','REQ','REC')
1796 AND tdinst.required_for_application = 'Y'
1797 AND NVL(tdinst.inactive_flag,'N') <> 'Y'
1798 AND tdinst.item_sequence_number = tdmst.todo_number
1799 AND tdmst.career_item = 'N'
1800 AND ROWNUM < 2
1801 UNION
1802 SELECT 1
1803 FROM igf_ap_td_item_inst_all tdinst,
1804 igf_ap_td_item_mst_all tdmst,
1805 igf_ap_fa_base_rec_all fa
1806 WHERE tdinst.base_id = fa.base_id
1807 AND tdinst.status IN ('INC','REQ','REC')
1808 AND tdinst.required_for_application = 'Y'
1809 AND NVL(tdinst.inactive_flag,'N') <> 'Y'
1810 AND tdinst.item_sequence_number = tdmst.todo_number
1811 AND tdmst.career_item = 'Y'
1812 AND fa.person_id = p_person_id
1813 AND ROWNUM < 2;
1814
1815 CURSOR cur_ver_status ( p_base_id igf_ap_fa_base_rec_all.base_id%TYPE) IS
1816 SELECT fed_verif_status, NVL(fa_process_status,'RECEIVED') fa_process_status
1817 FROM igf_ap_fa_base_rec
1818 WHERE base_id = p_base_id;
1819
1820 ln_count_open_items NUMBER;
1821 l_person_id hz_parties.party_id%TYPE;
1822 lv_fed_verif_status igf_ap_fa_base_rec_all.fed_verif_status%TYPE;
1823 lv_fa_process_status igf_ap_fa_base_rec_all.fa_process_status%TYPE;
1824 ln_auto_na_complete VARCHAR2(80);
1825
1826 -- Get person_id
1827 CURSOR c_person_id(
1828 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
1829 ) IS
1830 SELECT person_id
1831 FROM igf_ap_fa_base_rec_all
1832 WHERE base_id = cp_base_id;
1833
1834 BEGIN
1835
1839 OPEN c_person_id(p_base_id);
1836 -- Initialise the global
1837 g_disb_hold := 'N';
1838
1840 FETCH c_person_id INTO l_person_id;
1841 CLOSE c_person_id;
1842
1843 fnd_profile.get('IGF_AP_MANUAL_REVIEW_APPL',ln_auto_na_complete);
1844 ln_auto_na_complete := NVL(ln_auto_na_complete,'N');
1845
1846 OPEN cur_ver_status ( p_base_id);
1847 FETCH cur_ver_status INTO lv_fed_verif_status,lv_fa_process_status;
1848 IF cur_ver_status%NOTFOUND THEN
1849 CLOSE cur_ver_status;
1850 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1851 FND_MESSAGE.SET_TOKEN('NAME','FA BASE DOES NOT EXIST');
1852 fnd_file.put_line(fnd_file.log,fnd_message.get);
1853 APP_EXCEPTION.RAISE_EXCEPTION;
1854 END IF;
1855 CLOSE cur_ver_status;
1856
1857 OPEN cur_todo ( p_base_id,l_person_id);
1858 FETCH cur_todo INTO ln_count_open_items;
1859 IF cur_todo%NOTFOUND THEN
1860 ln_count_open_items := 0;
1861 ELSE
1862 ln_count_open_items := 1;
1863 END IF;
1864 CLOSE cur_todo;
1865
1866 IF lv_fa_process_status = 'RECEIVED' AND ln_count_open_items = 0 THEN
1867 IF ln_auto_na_complete = 'Y' THEN
1868 lv_fa_process_status := 'MANUAL_REVIEW';
1869 ELSE
1870 lv_fa_process_status := 'COMPLETE';
1871 END IF;
1872
1873 ELSIF ln_count_open_items > 0 THEN
1874 lv_fa_process_status := 'RECEIVED';
1875
1876 END IF;
1877
1878 IF p_fed_verif_status = 'REPROCESSED' AND lv_fed_verif_status <> 'REPROCESSED' THEN
1879 g_disb_hold := 'Y';
1880 END IF;
1881
1882 l_update_base_rec (p_base_id,p_fed_verif_status,NULL,lv_fa_process_status);
1883
1884 EXCEPTION
1885 WHEN OTHERS THEN
1886 fnd_file.put_line(fnd_file.log,SQLERRM);
1887 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1888 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_BATCH_VER_PRC.UPDATE_PROCESS_STATUS');
1889 IGS_GE_MSG_STACK.ADD;
1890 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1891 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.update_process_status.debug',SQLERRM);
1892 END IF;
1893 APP_EXCEPTION.RAISE_EXCEPTION;
1894
1895 END update_process_status;
1896
1897 PROCEDURE update_fed_verif_status ( p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1898 p_fed_verif_status igf_ap_fa_base_rec_all.fed_verif_status%TYPE
1899 ) IS
1900 /**********************************************************
1901 Created By : bkkumar
1902 Date Created By : 10-Dec-2003
1903
1904 Purpose : Bug# 3240804 Update the federal verification status
1905 if the verification status is in TERMINAL status.
1906
1907 Know limitations, enhancements or remarks
1908 Change History
1909 Who When What
1910 (reverse chronological order - newest change first)
1911 ***************************************************************/
1912 CURSOR get_fed_verif_status (cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
1913 IS
1914 SELECT fed_verif_status
1915 FROM igf_ap_fa_base_rec_all
1916 WHERE base_id = cp_base_id;
1917
1918 l_fed_verif_status igf_ap_fa_base_rec_all.fed_verif_status%TYPE;
1919
1920 BEGIN
1921 l_fed_verif_status := NULL;
1922
1923 OPEN get_fed_verif_status(p_base_id);
1924 FETCH get_fed_verif_status INTO l_fed_verif_status;
1925 CLOSE get_fed_verif_status;
1926
1927 -- If the fed_verif_Status is not in terminal status then only update the status
1928 IF NVL(l_fed_verif_status,'X') NOT IN ('CORRSENT','SELECTED','WITHOUTDOC') THEN
1929 update_process_status(p_base_id , p_fed_verif_status);
1930 ELSE
1931 update_process_status(p_base_id , NULL);
1932 END IF;
1933
1934 EXCEPTION
1935 WHEN OTHERS THEN
1936 fnd_file.put_line(fnd_file.log,SQLERRM);
1937 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1938 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_BATCH_VER_PRC.UPDATE_FED_VERIF_STATUS');
1939 IGS_GE_MSG_STACK.ADD;
1940 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1941 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.update_fed_verif_status.debug',SQLERRM);
1942 END IF;
1943 APP_EXCEPTION.RAISE_EXCEPTION;
1944
1945 END update_fed_verif_status;
1946
1947 FUNCTION get_gr_ver_code(
1948 pv_fed_verif_status igf_lookups_view.lookup_code%TYPE,
1949 p_cal_type igf_ap_batch_aw_map_all.ci_cal_type%TYPE,
1950 p_sequence_number igf_ap_batch_aw_map_all.ci_sequence_number%TYPE
1951 ) RETURN VARCHAR2 IS
1952
1953 -- Read Sys Award Year
1954 CURSOR cur_get_sys_awd(
1955 p_cal_type igf_ap_batch_aw_map_all.ci_cal_type%TYPE,
1956 p_sequence_number igf_ap_batch_aw_map_all.ci_sequence_number%TYPE
1957 ) IS
1958 SELECT sys_award_year
1959 FROM igf_ap_batch_aw_map
1960 WHERE ci_cal_type = p_cal_type
1961 AND ci_sequence_number = p_sequence_number;
1962
1963 lv_sys_award_year igf_aw_int_ext_map.sys_award_year%TYPE;
1964
1965 -- Read mapping from igf_aw_int_ext_map table
1966 CURSOR cur_get_int_ext(
1967 p_fed_verif_status igf_lookups_view.lookup_code%TYPE,
1968 p_sys_award_year igf_aw_int_ext_map.sys_award_year%TYPE ,
1969 cp_int_lkup_type VARCHAR2 ,
1970 cp_ext_lkup_type VARCHAR2
1971 ) IS
1972 SELECT ext_lookup_code
1973 FROM igf_aw_int_ext_map
1974 WHERE int_lookup_type = cp_int_lkup_type
1975 AND ext_lookup_type = cp_ext_lkup_type
1976 AND int_lookup_code = p_fed_verif_status
1977 AND SYS_AWARD_YEAR = p_sys_award_year;
1978
1979 lv_gr_verif_status VARCHAR2(30);
1980 l_int_lkup_type VARCHAR2(30);
1981 l_ext_lkup_type VARCHAR2(30);
1982
1983 BEGIN
1984
1985 OPEN cur_get_sys_awd(p_cal_type,p_sequence_number);
1986 FETCH cur_get_sys_awd INTO lv_sys_award_year;
1987 CLOSE cur_get_sys_awd;
1988
1989 l_int_lkup_type := 'IGF_FED_VERIFY_STATUS';
1990 l_ext_lkup_type := 'IGF_GR_VER_STAT_CD' ;
1991 OPEN cur_get_int_ext(pv_fed_verif_status,lv_sys_award_year, l_int_lkup_type, l_ext_lkup_type );
1992 FETCH cur_get_int_ext INTO lv_gr_verif_status;
1993 CLOSE cur_get_int_ext;
1994
1995 RETURN lv_gr_verif_status;
1996
1997 EXCEPTION
1998 WHEN OTHERS THEN
1999
2000 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2001 fnd_message.set_token('NAME','IGF_AP_BATCH_VER_PRC.GET_GR_VER_CODE');
2002 igs_ge_msg_stack.add;
2003 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2004 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_batch_ver_prc_pkg.get_gr_ver_code.debug',SQLERRM);
2005 END IF;
2006 app_exception.raise_exception;
2007
2008 END get_gr_ver_code;
2009
2010
2011 END igf_ap_batch_ver_prc_pkg;