[Home] [Help]
PACKAGE BODY: APPS.IGF_AP_PROCESS_CORRECTIONS
Source
1 PACKAGE BODY igf_ap_process_corrections AS
2 /* $Header: IGFAP02B.pls 120.8 2006/04/17 00:09:56 hkodali ship $ */
3
4 /*
5 || Created By : Sridhar
6 || Created On : 25-NOV-2000
7 || Purpose : Package creates a flat file with header,corrected records and trailer
8 || to be sent to CPS. After the file is created igf_ap_isir_corr,
9 || igf_ap_fa_base_rec tables are updated to change the correction_status.
10 || Known limitations, enhancements or remarks :
11 || Change History :
12 || Who When What
13 || bkkumar 07-May-2004 Bug 3598933 Added the fnd logging messages
14 || veramach 29-Apr-2004 bug 3598067
15 || Changed gv_Trans_Data_Source_Or_Type's value to '1C' rather than 'IC'
16 || ugummall 31-OCT-2003 Bug 3102439. FA 126 - Multiple FA Offices.
17 || 1. Added 5 new parameters to prepare_file.
18 || 2. Removed cursors get_school and dest_num_cur and their references.
19 || 3. Modified cursor match_isirs to select only those records whose
20 || associated org unit's federal school code match with passed in code.
21 || cdcruz 17-Sep-2003 # 3085558 FA121-Verification Worksheet.
22 || HOld check added
23 || masehgal 25-Sep-2002 FA 104 -To Do Enhancements
24 || Added manual_disb_hold in update of Fa Base Rec
25 || (reverse chronological order - newest change first)
26 */
27
28
29 gn_isir_id igf_ap_isir_corr.isir_id%TYPE;
30 gv_s_email igf_ap_isir_matched.s_email_address%TYPE;
31 gv_corr_rec VARCHAR2(1000);
32 gv_trailer VARCHAR2(1000);
33 gv_header VARCHAR2(1000);
34 gn_cnt NUMBER;
35 gn_std_cnt NUMBER DEFAULT 0;
36 gb_nwisr_flg BOOLEAN;
37 gv_ori_ssn VARCHAR2(9);
38 gv_ori_name VARCHAR2(2);
39 gv_trn_num VARCHAR2(2);
40
41 g_fed_school_code VARCHAR2(6);
42 gv_datarec_len NUMBER DEFAULT 580;
43 gv_batchnum VARCHAR2(23);
44 gv_first_name VARCHAR2(12);
45 gv_last_name VARCHAR2(16);
46 gv_person_number VARCHAR2(30);
47 gn_baseid NUMBER(15);
48 gv_cal_type VARCHAR2(10);
49 gn_sequence_number NUMBER;
50 gv_reject_override_3_flag VARCHAR2(1);
51 gv_reject_override_12_flag VARCHAR2(1);
52 gv_reject_override_j_flag VARCHAR2(1);
53 gv_reject_override_k_flag VARCHAR2(1);
54 gv_reject_override_a VARCHAR2(1);
55 gv_reject_override_b VARCHAR2(1);
56 gv_reject_override_c VARCHAR2(1);
57 gv_reject_override_g_flag VARCHAR2(1);
58 gv_reject_override_n VARCHAR2(1);
59 gv_reject_override_w VARCHAR2(1);
60 gv_assum_override_1 VARCHAR2(1);
61 gv_assum_override_2 VARCHAR2(1);
62 gv_assum_override_3 VARCHAR2(1);
63 gv_assum_override_4 VARCHAR2(1);
64 gv_assum_override_5 VARCHAR2(1);
65 gv_assum_override_6 VARCHAR2(1);
66 gv_date VARCHAR2(8);
67 gv_datetime VARCHAR2(6);
68 gv_dest_num VARCHAR2(10);
69 gv_batch_year VARCHAR2(4);
70 gv_Trans_Data_Source_Or_Type VARCHAR2(2) := '1C';
71
72
73 CURSOR corrs ( cp_corr_status VARCHAR2 ) IS
74 SELECT isir_id, sar_field_number, original_value, corrected_value
75 FROM igf_ap_isir_corr
76 WHERE correction_status = cp_corr_status
77 AND isir_id = gn_isir_id;
78
79 CURSOR corr_hold IS
80 SELECT 'x'
81 FROM igf_ap_isir_corr
82 WHERE
83 correction_status = 'HOLD' AND
84 isir_id = gn_isir_id and
85 rownum = 1;
86
87 -- Bug 4403807 - removed the condition isir.payment_isir = 'Y'
88 CURSOR match_isirs (p_base_id NUMBER) IS
89 SELECT isirm.isir_id, isirm.s_email_address, isirm.transaction_num, isirm.original_ssn,
90 isirm.orig_name_id, isirm.first_name, isirm.last_name, isirm.base_id, PE.party_number person_number
91 FROM igf_ap_isir_matched_all isirm,
92 igf_ap_fa_base_rec_all far,
93 hz_parties pe
94 WHERE isirm.base_id = far.base_id
95 AND far.person_id = pe.party_Id
96 AND isirm.system_record_type = 'ORIGINAL'
97 AND isirm.base_id = NVL(p_base_id, isirm.base_id)
98 AND far.ci_cal_type = gv_cal_type
99 AND far.ci_sequence_number = gn_sequence_number
100 AND isirm.isir_id IN (SELECT DISTINCT c.isir_id
101 FROM igf_ap_isir_corr_all c
102 WHERE c.correction_status = 'READY' );
103
104
105 CURSOR cur_corr_isir (gn_baseid NUMBER ,
106 cp_rec_type VARCHAR2) IS
107 SELECT
108 reject_override_3_flag,
109 reject_override_12_flag,
110 reject_override_a,
111 reject_override_b,
112 reject_override_c,
113 reject_override_g_flag,
114 reject_override_j_flag,
115 reject_override_k_flag,
116 reject_override_n,
117 reject_override_w,
118 assum_override_1,
119 assum_override_2,
120 assum_override_3,
121 assum_override_4,
122 assum_override_5,
123 assum_override_6
124 FROM igf_ap_isir_matched
125 WHERE base_id = gn_baseid
126 AND system_record_type = cp_rec_type ;
127
128 corr_isir_rec cur_corr_isir%ROWTYPE ;
129
130 -- Cursor get_school here is removed as p_school_code passed in parameter is being used.
131
132 lc_corr_cur corrs%ROWTYPE;
133
134 lc_corr_hold corr_hold%ROWTYPE;
135
136 -- Cursor dest_num_cur here is removed as eti_dest_num passed in parameter is being used.
137
138 CURSOR batch_yr_cur IS
139 SELECT batch_year
140 FROM igf_ap_batch_aw_map
141 WHERE ci_cal_type = gv_cal_type
142 AND ci_sequence_number = gn_sequence_number;
143
144 -- masehgal # 2885882 added in FACR113 SAR Updates
145 CURSOR cur_pay_isir (gn_baseid NUMBER) IS
146 SELECT
147 reject_override_3_flag,
148 reject_override_12_flag,
149 reject_override_a,
150 reject_override_b,
151 reject_override_c,
152 reject_override_g_flag,
153 reject_override_j_flag,
154 reject_override_k_flag,
155 reject_override_n,
156 reject_override_w,
157 assum_override_1,
158 assum_override_2,
159 assum_override_3,
160 assum_override_4,
161 assum_override_5,
162 assum_override_6
163 FROM igf_ap_isir_matched_all
164 WHERE base_id = gn_baseid
165 AND system_record_type = 'ORIGINAL'
166 AND payment_isir = 'Y';
167
168 pay_isir_rec cur_pay_isir%ROWTYPE ;
169
170
171 FUNCTION blanks(num_spaces IN NUMBER)
172 RETURN VARCHAR2
173 IS
174 /*
175 || Created By : Sridhar
176 || Created On : 25-NOV-2000
177 || Purpose : For right padding the variables to make their length
178 || fit to the field size in record.
179 || Known limitations, enhancements or remarks :
180 || Change History :
181 || Who When What
182 || (reverse chronological order - newest change first)
183 */
184
185 l_chr VARCHAR2(2000);
186
187 BEGIN
188
189 -- RETURN(RPAD(fnd_global.local_chr(0),num_spaces,fnd_global.local_chr(0)));
190 RETURN(RPAD(' ',num_spaces,' '));
191
192 EXCEPTION
193
194 WHEN OTHERS THEN
195 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
196 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_process_corrections.blanks.exception','The exception is : ' || SQLERRM );
197 END IF;
198 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
199 fnd_message.set_token('NAME','IGF_AP_PROCESS_CORRECTIONS.BLANKS');
200 fnd_file.put_line(fnd_file.log,SQLERRM);
201 igs_ge_msg_stack.add;
202 app_exception.raise_exception;
203
204 END blanks;
205
206 PROCEDURE create_header
207 IS
208 /*
209 || Created By : Sridhar
210 || Created On : 25-NOV-2000
211 || Purpose : Creates the mandatory header record for a batch of corrected records
212 || and calls write_file to write this header into the file.
213 || Known limitations, enhancements or remarks :
214 || Change History :
215 || Who When What
216 || ugummall 31-OCT-2003 Bug 3102439. FA 126 - Multiple FA Offices.
217 || gv_dest_num and g_fed_school_code are populated in main prepare_file
218 || procedure. Thus cursors get_school and dest_num_cur are removed.
219 || (reverse chronological order - newest change first)
220 */
221
222
223 BEGIN
224
225 --
226 -- Get ETI Destination Code from Setup
227 --
228
229 -- gv_dest_num is populated with passed in parameter eti_dest_num in prepare_file(main concurrent) procedure
230
231 --
232 -- Give message if the ETI Destination Code is invalid. Continue with the process
233 --
234
235 IF LENGTH(NVL(gv_dest_num,'TGXXXXX')) <> 7 THEN
236 fnd_message.set_name('IGF','IGF_AP_INVALID_DEST_CODE');
237 fnd_file.put_line(fnd_file.log, fnd_message.get);
238 gv_dest_num := 'TGXXXXX';
239 END IF;
240
241 gv_date := TO_CHAR(SYSDATE,'YYYYMMDD');
242 gv_datetime := TO_CHAR(SYSDATE,'HHMISS');
243
244
245 IF g_fed_school_code IS NULL THEN
246 g_fed_school_code := g_fed_school_code || blanks(6);
247 ELSE
248 g_fed_school_code := g_fed_school_code || blanks(6-LENGTH(g_fed_school_code));
249 END IF;
250 -- total
251 gv_batchnum := '#C' || -- 2
252 gv_batch_year || -- 3
253 g_fed_school_code || -- 9
254 gv_date || -- 17
255 gv_datetime; -- 23
256
257 --
258 -- Header record format is picked up from the File formats
259 -- Provided by the US Education department web site
260 --
261
262 IF gv_batch_year = '3' THEN
263 -- total
264 gv_header := 'CPS' || -- 3
265 blanks(1) || -- 4
266 'HEADER' || -- 10
267 blanks(2) || -- 12
268 LPAD(TO_CHAR(gv_datarec_len),4,0) || -- 16
269 'H' || -- 17
270 gv_dest_num || -- 24
271 blanks(2) || -- 26
272 gv_date || -- 34
273 gv_datetime || -- 40
274 blanks(12) || -- 52
275 '0203' || -- 56
276 gv_batchnum || -- 79
277 blanks(3) || -- 82
278 blanks(2) || -- 84
279 blanks(1) || -- 85
280 blanks(gv_datarec_len - 85); -- 580
281
282 ELSIF gv_batch_year = '4' THEN
283 -- total
284 gv_header := 'CPS' || -- 3
285 blanks(1) || -- 4
286 'HEADER' || -- 10
287 blanks(2) || -- 12
288 LPAD(TO_CHAR(gv_datarec_len),4,0) || -- 16
289 'H' || -- 17
290 gv_dest_num || -- 24
291 blanks(2) || -- 26
292 gv_date || -- 34
293 gv_datetime || -- 40
294 blanks(12) || -- 52
295 '0304' || -- 56
296 gv_batchnum || -- 79
297 blanks(3) || -- 82
298 blanks(2) || -- 84
299 blanks(1) || -- 85
300 blanks(gv_datarec_len - 85); -- 580
301 END IF;
302
303 EXCEPTION
304 WHEN OTHERS THEN
305 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
306 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_process_corrections.create_header.exception','The exception is : ' || SQLERRM );
307 END IF;
308 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
309 fnd_message.set_token('NAME','IGF_AP_PROCESS_CORRECTIONS.CREATE_HEADER');
310 fnd_file.put_line(fnd_file.log,SQLERRM);
311 igs_ge_msg_stack.add;
312 app_exception.raise_exception;
313
314 END create_header;
315
316 PROCEDURE create_trailer
317 IS
318 /*
319 || Created By : Sridhar
320 || Created On : 25-NOV-2000
321 || Purpose : Creates the mandatory trailer record for a batch of corrected records
322 || and calls write_file to write this trailer into the file.
323 || Known limitations, enhancements or remarks :
324 || Change History :
325 || Who When What
326 || (reverse chronological order - newest change first)
327 */
328
329
330 BEGIN
331
332 --
333 -- Trailer record format is picked up from the file formats
334 -- provided by the US Education department
335 --
336
337 IF gv_batch_year = '3' THEN
338 -- total
339 gv_trailer := 'CPS' || -- 3
340 blanks(1) || -- 4
341 'TRAILER' || -- 11
342 blanks(1) || -- 12
343 LPAD(TO_CHAR(gv_datarec_len),4,'0') || -- 16
344 'H' || -- 17
345 gv_dest_num || -- 24
346 gv_date || -- 32
347 gv_datetime || -- 38
348 blanks(14) || -- 52
349 '0203' || -- 56
350 LPAD(TO_CHAR(gn_std_cnt),5,'0') || -- 61
351 gv_batchnum || -- 84
352 blanks(1) || -- 85
353 blanks(2) || -- 87
354 blanks(7) || -- 94
355 blanks(7) || -- 101
356 blanks(7) || -- 108
357 blanks(1) || -- 109
358 blanks(gv_datarec_len - 109); -- 580
359
360 ELSIF gv_batch_year = '4' THEN
361 -- total
362 gv_trailer := 'CPS' || -- 3
363 blanks(1) || -- 4
364 'TRAILER' || -- 11
365 blanks(1) || -- 12
366 LPAD(TO_CHAR(gv_datarec_len),4,'0')|| -- 16
367 'H' || -- 17
368 gv_dest_num || -- 24
369 gv_date || -- 32
370 gv_datetime || -- 38
371 blanks(7) || -- 45
372 '0304' || -- 49
373 LPAD(TO_CHAR(gn_std_cnt),5,'0') || -- 54
374 gv_batchnum || -- 77
375 blanks(1) || -- 78
376 blanks(2) || -- 80
377 blanks(7) || -- 87
378 blanks(7) || -- 94
379 blanks(7) || -- 101
380 blanks(7) || -- 108
381 blanks(1) || -- 109
382 blanks(gv_datarec_len - 109); -- 580
383 END IF;
384
385 EXCEPTION
386 WHEN OTHERS THEN
387 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
388 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_process_corrections.create_trailer.exception','The exception is : ' || SQLERRM );
389 END IF;
390 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
391 fnd_message.set_token('NAME','IGF_AP_PROCESS_CORRECTIONS.CREATE_TRAILER');
392 fnd_file.put_line(fnd_file.log,SQLERRM);
393 igs_ge_msg_stack.add;
394 app_exception.raise_exception;
395
396 END create_trailer;
397
398
399 PROCEDURE format_fields(vsarno IN OUT NOCOPY VARCHAR2,
400 vnewval IN OUT NOCOPY VARCHAR2)
401 IS
402 /*
403 || Created By : Sridhar
404 || Created On : 25-NOV-2000
405 || Purpose : Formats the signed numeric and ordinary numeric fields.
406 || Known limitations, enhancements or remarks :
407 || Change History :
408 || Who When What
409 || museshad 28-Oct-2005 Bug 4690726
410 || masehgal 14-May-2003 # 2885882 FACR113 SAR Updates
411 || Precessing based on SAR Names rather than SAR Numbers
412 || (reverse chronological order - newest change first)
413 */
414
415
416 CURSOR cur_get_sar_name (cp_base_id igf_ap_fa_base_rec.base_id%TYPE ,
417 l_sar_number igf_fc_sar_cd_mst.sar_field_number%TYPE ) IS
418 SELECT sar.sar_field_name
419 FROM igf_ap_batch_aw_map map,
420 igf_ap_fa_base_rec_all fabase ,
421 igf_fc_sar_cd_mst sar
422 WHERE fabase.base_id = cp_base_id
423 AND map.ci_cal_type = fabase.ci_cal_type
424 AND map.ci_sequence_number = fabase.ci_sequence_number
425 AND sar.sys_award_year = map.sys_award_year
426 AND sar.sar_field_number = l_sar_number ;
427
428 v_last_digit VARCHAR2(2) ;
429 L_sar_column_name VARCHAR2(30) ;
430
431 BEGIN
432
433 OPEN cur_get_sar_name ( gn_baseid, vsarno ) ;
434 FETCH cur_get_sar_name INTO l_sar_column_name ;
435 CLOSE cur_get_sar_name ;
436
437 IF RTRIM(vnewval) IS NULL THEN
438 vnewval := '*';
439 ELSE
440
441 /* These fields are signed numeric fields. The format in which they need
442 to be sent to CPS is available in file formats. */
443
444 IF L_sar_column_name IN ('S_ADJUSTED_GROSS_INCOME' ,
445 'S_INCOME_FROM_WORK' ,
446 'SPOUSE_INCOME_FROM_WORK',
447 'P_ADJUSTED_GROSS_INCOME',
448 'F_INCOME_WORK',
449 'M_INCOME_WORK') THEN
450 IF TO_NUMBER( vnewval) > 0 THEN
451 v_last_digit := TO_NUMBER (SUBSTR (RTRIM (vnewval), LENGTH (RTRIM (vnewval)),1)) ;
452 IF v_last_digit = 0 THEN
453 vnewval := SUBSTR ( RTRIM (vnewval), 1, LENGTH ( RTRIM(vnewval))-1) || '{' ;
454 ELSIF v_last_digit = 1 THEN
455 vnewval := SUBSTR ( RTRIM (vnewval), 1, LENGTH ( RTRIM(vnewval))-1) || 'A' ;
456 ELSIF v_last_digit = 2 THEN
457 vnewval := SUBSTR ( RTRIM (vnewval), 1, LENGTH ( RTRIM(vnewval))-1) || 'B' ;
458 ELSIF v_last_digit = 3 THEN
459 vnewval := SUBSTR ( RTRIM (vnewval), 1, LENGTH ( RTRIM(vnewval))-1) || 'C' ;
460 ELSIF v_last_digit = 4 THEN
461 vnewval := SUBSTR ( RTRIM (vnewval), 1, LENGTH ( RTRIM(vnewval))-1) || 'D' ;
462 ELSIF v_last_digit = 5 THEN
463 vnewval := SUBSTR ( RTRIM (vnewval), 1, LENGTH ( RTRIM(vnewval))-1) || 'E' ;
464 ELSIF v_last_digit = 6 THEN
465 vnewval := SUBSTR ( RTRIM (vnewval), 1, LENGTH ( RTRIM(vnewval))-1) || 'F' ;
466 ELSIF v_last_digit = 7 THEN
467 vnewval := SUBSTR ( RTRIM (vnewval), 1, LENGTH ( RTRIM(vnewval))-1) || 'G' ;
468 ELSIF v_last_digit = 8 THEN
469 vnewval := SUBSTR ( RTRIM (vnewval), 1, LENGTH ( RTRIM(vnewval))-1) || 'H' ;
470 ELSIF v_last_digit = 9 THEN
471 vnewval := SUBSTR ( RTRIM (vnewval), 1, LENGTH ( RTRIM(vnewval))-1) || 'I' ;
472 END IF ;
473 ELSIF TO_NUMBER( vnewval) < 0 THEN
474 v_last_digit := TO_NUMBER (SUBSTR (RTRIM (vnewval), LENGTH (RTRIM (vnewval)),1)) ;
475 IF v_last_digit = 0 THEN
476 vnewval := SUBSTR ( RTRIM (vnewval), 2, LENGTH ( RTRIM(vnewval))-2) || '}' ;
477 ELSIF v_last_digit = 1 THEN
478 vnewval := SUBSTR ( RTRIM (vnewval), 2, LENGTH ( RTRIM(vnewval))-2) || 'J' ;
479 ELSIF v_last_digit = 2 THEN
480 vnewval := SUBSTR ( RTRIM (vnewval), 2, LENGTH ( RTRIM(vnewval))-2) || 'K' ;
481 ELSIF v_last_digit = 3 THEN
482 vnewval := SUBSTR ( RTRIM (vnewval), 2, LENGTH ( RTRIM(vnewval))-2) || 'L' ;
483 ELSIF v_last_digit = 4 THEN
484 vnewval := SUBSTR ( RTRIM (vnewval), 2, LENGTH ( RTRIM(vnewval))-2) || 'M' ;
485 ELSIF v_last_digit = 5 THEN
486 vnewval := SUBSTR ( RTRIM (vnewval), 2, LENGTH ( RTRIM(vnewval))-2) || 'N' ;
487 ELSIF v_last_digit = 6 THEN
488 vnewval := SUBSTR ( RTRIM (vnewval), 2, LENGTH ( RTRIM(vnewval))-2) || 'O' ;
489 ELSIF v_last_digit = 7 THEN
490 vnewval := SUBSTR ( RTRIM (vnewval), 2, LENGTH ( RTRIM(vnewval))-2) || 'P' ;
491 ELSIF v_last_digit = 8 THEN
492 vnewval := SUBSTR ( RTRIM (vnewval), 2, LENGTH ( RTRIM(vnewval))-2) || 'Q' ;
493 ELSIF v_last_digit = 9 THEN
494 vnewval := SUBSTR ( RTRIM (vnewval), 2, LENGTH ( RTRIM(vnewval))-2) || 'R' ;
495 END IF ;
496 ELSIF TO_NUMBER( vnewval) = 0 THEN
497 vnewval := '{' ;
498 END IF ;
499 vnewval := LPAD(vnewval,6,'0');
500
501 ELSIF l_sar_column_name IN ('S_EXEMPTIONS',
502 'VA_MONTHS',
503 'P_NUM_FAMILY_MEMBER',
504 'P_EXEMPTIONS',
505 'S_NUM_FAMILY_MEMBERS' ) THEN
506 vnewval := LPAD(vnewval, 2, '0') ;
507
508 ELSIF l_sar_column_name = 'VA_AMOUNT' THEN
509 vnewval := LPAD(vnewval, 4, '0') ;
510
511 ELSIF l_sar_column_name IN ('PERM_ZIP_CODE',
512 'S_FED_TAXES_PAID',
513 'S_TOA_AMT_FROM_WSA',
514 'S_TOA_AMT_FROM_WSB',
515 'S_TOA_AMT_FROM_WSC',
516 'P_INCOME_WSA',
517 'P_INCOME_WSB',
518 'P_INCOME_WSC') THEN
519 vnewval := LPAD(vnewval, 5, '0') ;
520
521 ELSIF l_sar_column_name IN ('S_INVESTMENT_NETWORTH',
522 'S_BUSI_FARM_NETWORTH',
523 'S_CASH_SAVINGS',
524 'P_TAXES_PAID',
525 'P_INVESTMENT_NETWORTH',
526 'P_BUSINESS_NETWORTH',
527 'P_CASH_SAVING') THEN
528 vnewval := LPAD(vnewval, 6, '0') ;
529
530 ELSIF l_sar_column_name IN ('CURRENT_SSN',
531 'FATHER_SSN',
532 'MOTHER_SSN',
533 'PREPARER_SSN',
534 'PREPARER_EMP_ID_NUMBER') THEN
535 vnewval := LPAD(vnewval, 9, '0') ;
536
537 ELSIF l_sar_column_name = 'PHONE_NUMBER' THEN
538 vnewval := LPAD(vnewval, 10, '0') ;
539
540 ELSIF l_sar_column_name IN ('DATE_OF_BIRTH',
541 'TRANSACTION_RECEIPT_DATE',
542 'DATE_APP_COMPLETED',
543 'FATHER_STEP_FATHER_BIRTH_DATE',
544 'MOTHER_STEP_MOTHER_BIRTH_DATE') THEN
545 vnewval := TO_CHAR(fnd_date.chardate_to_date(vnewval),'YYYYMMDD');
546 vnewval := LPAD(vnewval,8,'0') ;
547
548 -- masehgal 17-Jun-2003 # 2986938 Corrections File formatting Bug
549 ELSIF l_sar_column_name IN ('S_MARITAL_STATUS_DATE',
550 'PARENT_MARITAL_STATUS_DATE',
551 'P_LEGAL_RES_DATE',
552 'S_LEGAL_RESD_DATE') THEN
553 vnewval := TO_CHAR(fnd_date.chardate_to_date(vnewval),'YYYYMM');
554 vnewval := LPAD(vnewval,6,'0');
555
556 END IF;
557 END IF;
558 vnewval := vnewval || blanks(35 - length(vnewval));
559
560 EXCEPTION
561
562 WHEN OTHERS THEN
563 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
564 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_process_corrections.format_fields.exception','The exception is : ' || SQLERRM );
565 END IF;
566 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
567 fnd_message.set_token('NAME','IGF_AP_PROCESS_CORRECTIONS.FORMAT_FIELDS'|| ' ' || L_sar_column_name || ' ' || vnewval );
568 fnd_file.put_line(fnd_file.log,SQLERRM);
569 igs_ge_msg_stack.add;
570 app_exception.raise_exception;
571
572 END format_fields;
573
574 PROCEDURE start_record
575 IS
576 /*
577 || Created By : Sridhar
578 || Created On : 25-NOV-2000
579 || Purpose : Creates a new record to be written into the flat file.
580 || Known limitations, enhancements or remarks :
581 || Change History :
582 || Who When What
583 || (reverse chronological order - newest change first)
584 */
585
586
587
588 BEGIN
589
590 gv_ori_ssn := LPAD(gv_ori_ssn,9,'0');
591 gv_trn_num := LPAD(gv_trn_num,2,'0');
592 gv_corr_rec := gv_batch_year ||
593 gv_ori_ssn ||
594 gv_ori_name ||
595 gv_trn_num;
596
597 EXCEPTION
598
599 WHEN OTHERS THEN
600 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
601 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_process_corrections.start_record.exception','The exception is : ' || SQLERRM );
602 END IF;
603 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
604 fnd_message.set_token('NAME','IGF_AP_PROCESS_CORRECTIONS.START_RECORD');
605 fnd_file.put_line(fnd_file.log,SQLERRM);
606 igs_ge_msg_stack.add;
607 app_exception.raise_exception;
608
609 END start_record;
610
611 PROCEDURE fill_string(sar_no IN NUMBER,new_val IN VARCHAR2)
612 IS
613 /*
614 || Created By : Sridhar
615 || Created On : 25-NOV-2000
616 || Purpose : Calls format_fields procedure to format the string
617 || Known limitations, enhancements or remarks :
618 || Change History :
619 || Who When What
620 || (reverse chronological order - newest change first)
621 */
622
623 v_sarno VARCHAR2(3);
624 v_newval VARCHAR2(35);
625 BEGIN
626
627 v_sarno := sar_no;
628 v_newval := new_val;
629 v_sarno := LPAD(v_sarno,3,'0');
630
631 format_fields(v_sarno,v_newval);
632
633 gv_corr_rec := gv_corr_rec ||
634 v_sarno ||
635 v_newval;
636 EXCEPTION
637
638 WHEN OTHERS THEN
639 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
640 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_process_corrections.fill_string.exception','The exception is : ' || SQLERRM );
641 END IF;
642 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
643 fnd_message.set_token('NAME','IGF_AP_PROCESS_CORRECTIONS.FILL_STRING');
644 fnd_file.put_line(fnd_file.log,SQLERRM);
645 igs_ge_msg_stack.add;
646 app_exception.raise_exception;
647
648 END fill_string;
649
650 PROCEDURE compare_individual_override
651 AS
652 /*
653 || Created By : rasahoo
654 || Created On :
655 || Purpose :
656 || Known limitations, enhancements or remarks :
657 || Change History :
658 || Who When What
659 || ridas 05-Apr-2006 Bug #5064614. Added NVL function to
660 || avoid NULL value in the correction file.
661 || (reverse chronological order - newest change first)
662 */
663
664 BEGIN
665
666 gv_reject_override_3_flag := NVL(corr_isir_rec.reject_override_3_flag,blanks(1));
667
668 gv_reject_override_12_flag := NVL(corr_isir_rec.reject_override_12_flag,blanks(1));
669
670 gv_reject_override_a := NVL(corr_isir_rec.reject_override_a,blanks(1));
671
672 gv_reject_override_b := NVL(corr_isir_rec.reject_override_b,blanks(1));
673
674 gv_reject_override_c := NVL(corr_isir_rec.reject_override_c,blanks(1));
675
676 gv_reject_override_g_flag := NVL(corr_isir_rec.reject_override_g_flag,blanks(1));
677
678 gv_reject_override_j_flag := NVL(corr_isir_rec.reject_override_j_flag,blanks(1));
679
680 gv_reject_override_k_flag := NVL(corr_isir_rec.reject_override_k_flag,blanks(1));
681
682 gv_reject_override_n := NVL(corr_isir_rec.reject_override_n,blanks(1));
683
684 gv_reject_override_w := NVL(corr_isir_rec.reject_override_w,blanks(1));
685
686 gv_assum_override_1 := NVL(corr_isir_rec.assum_override_1,blanks(1));
687
688 gv_assum_override_2 := NVL(corr_isir_rec.assum_override_2,blanks(1));
689
690 gv_assum_override_3 := NVL(corr_isir_rec.assum_override_3,blanks(1));
691
692 gv_assum_override_4 := NVL(corr_isir_rec.assum_override_4,blanks(1));
693
694 gv_assum_override_5 := NVL(corr_isir_rec.assum_override_5,blanks(1));
695
696 gv_assum_override_6 := NVL(corr_isir_rec.assum_override_6,blanks(1));
697
698 EXCEPTION
699 WHEN others THEN
700 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
701 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_process_corrections.compare_individual_override.exception','The exception is : ' || SQLERRM );
702 END IF;
703 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
704 fnd_message.set_token('NAME','IGF_AP_PROCESS_CORRECTIONS.COMPARE_INDIVIDUAL_OVERRIDE' );
705 fnd_file.put_line(fnd_file.log,SQLERRM);
706 igs_ge_msg_stack.add;
707 app_exception.raise_exception;
708
709 END compare_individual_override;
710
711 PROCEDURE write_file(str_type VARCHAR2)
712 IS
713 /*
714 || Created By : Sridhar
715 || Created On : 25-NOV-2000
716 || Purpose : Uses Fnd_file utitlity to write header,data and trailer records
717 || into the flat file.Header and trailer records are written into
718 || file as they are constructed. But data records are formated here
719 || to fill in the last fields.
720 || Known limitations, enhancements or remarks :
721 || Change History :
722 || Who When What
723 || (reverse chronological order - newest change first)
724 */
725
726 ln_sno NUMBER;
727 lv_sno VARCHAR2(5);
728 lv_trn_dt VARCHAR2(8);
729 lv_email VARCHAR2(50);
730 lv_rowid VARCHAR2(30);
731 ln_ict_id igf_ap_isircor_tmp.ict_id%TYPE;
732 l_rec_type VARCHAR2(30) ;
733
734 BEGIN
735
736 IF str_type = 'DATA' THEN
737
738 IF LENGTH(LTRIM(RTRIM(gv_corr_rec))) < 470 THEN
739 gv_corr_rec := RPAD(gv_corr_rec,470,' ');
740 END IF;
741
742 IF gv_s_email IS NULL THEN
743 lv_email := lv_email||blanks(50);
744 ELSE
745 lv_email := LTRIM(RTRIM(gv_s_email));
746 lv_email := lv_email||blanks(50-LENGTH(lv_email));
747 END IF;
748
749 -- transaction date
750 lv_trn_dt := TO_CHAR(SYSDATE,'YYYYMMDD');
751 lv_sno := LPAD(TO_CHAR(gn_std_cnt),5,'0');
752
753 /*
754 Get the reject override and assumption override fields from the Correction ISIR.
755 These fields can be changed through the modify ISIR page.
756 */
757 -- masehgal FACR113 SAR Updates - removed overrides from 0203 format file
758 IF SUBSTR(gv_corr_rec,1,1) = '3' THEN
759 gv_corr_rec := gv_corr_rec||g_fed_school_code; -- 470 - 476
760 gv_corr_rec := gv_corr_rec || -- 476
761 lv_email || -- 526
762 blanks (9) || -- 535
763 blanks(17) || -- 552
764 lv_trn_dt || -- 560
765 blanks(14) || -- 574
766 lv_sno || -- 579
767 'H'; -- 580
768
769
770 -- for 0304
771 -- masehgal FACR113 SAR Updates
772 ELSIF SUBSTR(gv_corr_rec,1,1) = '4' THEN
773 -- get the correction isir assumption/reject override values
774 l_rec_type := 'CORRECTION' ;
775 OPEN cur_corr_isir (gn_baseid , l_rec_type );
776 FETCH cur_corr_isir INTO corr_isir_rec ;
777 CLOSE cur_corr_isir;
778
779 -- get the payment isir assumption/reject override values
780 OPEN cur_pay_isir (gn_baseid);
781 FETCH cur_pay_isir INTO pay_isir_rec ;
782 CLOSE cur_pay_isir;
783
784 -- compare individual overrides
785 compare_individual_override;
786 gv_corr_rec := gv_corr_rec||g_fed_school_code; -- 471 - 476
787 -- append in the correction record -- total
788 gv_corr_rec := gv_corr_rec || -- 476
789 lv_email || -- 526
790 gv_reject_override_a || -- 527
791 gv_reject_override_b || -- 528
792 gv_reject_override_c || -- 529
793 gv_reject_override_n || -- 530
794 gv_reject_override_w || -- 531
795 gv_assum_override_1 || -- 532
796 gv_assum_override_2 || -- 533
797 gv_assum_override_3 || -- 534
798 gv_assum_override_4 || -- 535
799 gv_assum_override_5 || -- 536
800 gv_assum_override_6 || -- 537
801 blanks(17) || -- 554
802 lv_trn_dt || -- 562
803 blanks(12) || -- 574
804 lv_sno || -- 579
805 'H'; -- 580
806 ELSIF SUBSTR(gv_corr_rec,1,1) = '5' THEN
807 -- get the correction isir assumption/reject override values
808 l_rec_type := 'CORRECTION' ;
809
810 OPEN cur_corr_isir (gn_baseid , l_rec_type );
811 FETCH cur_corr_isir INTO corr_isir_rec ;
812 CLOSE cur_corr_isir;
813
814 -- get the payment isir assumption/reject override values
815 OPEN cur_pay_isir (gn_baseid);
816 FETCH cur_pay_isir INTO pay_isir_rec ;
817 CLOSE cur_pay_isir;
818
819 -- compare individual overrides
820 compare_individual_override;
821 gv_corr_rec := gv_corr_rec || -- 470
822 lv_email || -- 520
823 gv_Trans_Data_Source_Or_Type || -- 522
824 lv_trn_dt || -- 530
825 gv_assum_override_1 || -- 531
826 gv_assum_override_2 || -- 532
827 gv_assum_override_3 || -- 533
828 gv_assum_override_4 || -- 534
829 gv_assum_override_5 || -- 535
830 gv_assum_override_6 || -- 536
831 gv_reject_override_a || -- 537
832 gv_reject_override_b || -- 538
833 gv_reject_override_c || -- 539
834 gv_reject_override_g_flag || -- 540
835 gv_reject_override_n || -- 541
836 gv_reject_override_w || -- 542
837 g_fed_school_code || -- 548
838 blanks(32); -- 580
839
840 ELSIF SUBSTR(gv_corr_rec,1,1) = '6' THEN
841 -- get the correction isir assumption/reject override values
842 l_rec_type := 'CORRECTION' ;
843
844 OPEN cur_corr_isir (gn_baseid , l_rec_type );
845 FETCH cur_corr_isir INTO corr_isir_rec ;
846 CLOSE cur_corr_isir;
847
848 -- get the payment isir assumption/reject override values
849 OPEN cur_pay_isir (gn_baseid);
850 FETCH cur_pay_isir INTO pay_isir_rec ;
851 CLOSE cur_pay_isir;
852
853 -- compare individual overrides
854 compare_individual_override;
855 gv_corr_rec := gv_corr_rec || -- 470
856 lv_email || -- 520
857 gv_Trans_Data_Source_Or_Type || -- 522
858 lv_trn_dt || -- 530
859 gv_assum_override_1 || -- 531
860 gv_assum_override_2 || -- 532
861 gv_assum_override_3 || -- 533
862 gv_assum_override_4 || -- 534
863 gv_assum_override_5 || -- 535
864 gv_assum_override_6 || -- 536
865 gv_reject_override_3_flag || -- 537
866 gv_reject_override_12_flag || -- 538
867 gv_reject_override_a || -- 539
868 gv_reject_override_b || -- 540
869 gv_reject_override_c || -- 541
870 blanks(1) || -- 542
871 gv_reject_override_g_flag || -- 543
872 gv_reject_override_j_flag || -- 544
873 gv_reject_override_k_flag || -- 545
874 gv_reject_override_n || -- 546
875 blanks(1) || -- 547
876 gv_reject_override_w || -- 548
877 g_fed_school_code || -- 554
878 blanks(26); -- 580
879
880
881 ELSIF SUBSTR(gv_corr_rec,1,1) = '7' THEN
882 -- get the correction isir assumption/reject override values
883 l_rec_type := 'CORRECTION' ;
884
885 OPEN cur_corr_isir (gn_baseid , l_rec_type );
886 FETCH cur_corr_isir INTO corr_isir_rec ;
887 CLOSE cur_corr_isir;
888
889 -- get the payment isir assumption/reject override values
890 OPEN cur_pay_isir (gn_baseid);
891 FETCH cur_pay_isir INTO pay_isir_rec ;
892 CLOSE cur_pay_isir;
893
894 -- compare individual overrides
895 compare_individual_override;
896 gv_corr_rec := gv_corr_rec || -- 470
897 lv_email || -- 520
898 gv_Trans_Data_Source_Or_Type || -- 522
899 lv_trn_dt || -- 530
900 gv_assum_override_1 || -- 531
901 gv_assum_override_2 || -- 532
902 gv_assum_override_3 || -- 533
903 gv_assum_override_4 || -- 534
904 gv_assum_override_5 || -- 535
905 gv_assum_override_6 || -- 536
906 gv_reject_override_3_flag || -- 537
907 gv_reject_override_12_flag || -- 538
908 gv_reject_override_a || -- 539
909 gv_reject_override_b || -- 540
910 gv_reject_override_c || -- 541
911 blanks(1) || -- 542
912 gv_reject_override_g_flag || -- 543
913 gv_reject_override_j_flag || -- 544
914 gv_reject_override_k_flag || -- 545
915 gv_reject_override_n || -- 546
916 blanks(1) || -- 547
917 gv_reject_override_w || -- 548
918 g_fed_school_code || -- 554
919 blanks(26); -- 580
920
921 END IF;
922
923 fnd_file.put_line(fnd_file.output,gv_corr_rec);
924
925 ELSIF str_type = 'HEADER' THEN
926 create_header;
927
928 fnd_file.put_line(fnd_file.output,gv_header);
929
930
931 ELSIF str_type = 'TRAILER' THEN
932 create_trailer;
933
934 fnd_file.put_line(fnd_file.output,gv_trailer);
935
936 END IF;
937
938 EXCEPTION
939
940 WHEN fnd_file.utl_file_error THEN
941 fnd_message.set_name('IGF','IGF_GE_ERROR_OPEN_FILE');
942 igs_ge_msg_stack.add;
943 app_exception.raise_exception;
944
945 WHEN OTHERS THEN
946 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
947 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_process_corrections.write_file.exception','The exception is : ' || SQLERRM );
948 END IF;
949 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
950 fnd_message.set_token('NAME','IGF_AP_PROCESS_CORRECTIONS.WRITE_FILE');
951 fnd_file.put_line(fnd_file.log,SQLERRM);
952 igs_ge_msg_stack.add;
953 app_exception.raise_exception;
954
955 END write_file;
956
957 PROCEDURE update_corr
958 IS
959 /*
960 || Created By : Sridhar
961 || Created On : 25-NOV-2000
962 || Purpose : Updates the status of the records written into output file to batched
963 || in IGF_AP_ISIR_CORR and IGF_AP_FA_BASE_REC tables.
964 || Known limitations, enhancements or remarks :
965 || Change History :
966 || Who When What
967 || ugummall 26-SEP-2003 FA 126 - Multiple FA Offices.
968 || added new parameter assoc_org_num to TBH call
969 || igf_ap_fa_base_rec_pkg.update_row w.r.t. FA 126
970 ||
971 || masehgal 11-Nov-2002 FA 101 - SAP Obsoletion
972 || removed packaging hold
973 || masehgal 25-Sep-2002 FA 104 -To Do Enhancements
974 || Added manual_disb_hold in update of Fa Base Rec
975 || rbezawad 22-Jun-2001 igf_ap_fa_base_rec_pkg.update_row call modified by
976 || passing gv_batchnum to parameter x_ede_correction_batch_id
977 || w.r.t. Bug ID: 1821811
978 || (reverse chronological order - newest change first)
979 */
980 CURSOR corr_rec ( cp_corr_stat VARCHAR2) IS
981 SELECT corr.*
982 FROM igf_ap_isir_corr corr
983 WHERE isir_id = gn_isir_id
984 AND correction_status = cp_corr_stat ;
985
986 CURSOR get_baserec IS
987 SELECT f.*
988 FROM igf_ap_fa_base_rec f
989 WHERE base_id = gn_baseid;
990
991 pn_rec corr_rec%ROWTYPE;
992 base_rec get_baserec%ROWTYPE;
993 corr_stat igf_ap_isir_corr.correction_status%TYPE;
994 lv_curdate DATE;
995 l_corr_stat VARCHAR2(30) ;
996
997 BEGIN
998
999 corr_stat := 'BATCHED';
1000 l_corr_stat := 'READY';
1001
1002 OPEN corr_rec ( l_corr_stat );
1003 LOOP
1004
1005 FETCH corr_rec INTO pn_rec;
1006 EXIT WHEN corr_rec%NOTFOUND;
1007 IF NOT igf_ap_isir_corr_pkg.get_uk_for_validation (pn_rec.isir_id,
1008 pn_rec.sar_field_number,
1009 corr_stat)
1010 THEN
1011
1012 igf_ap_isir_corr_pkg.update_row(
1013 x_mode => 'R',
1014 x_rowid => pn_rec.row_id,
1015 x_isirc_id => pn_rec.isirc_id,
1016 x_isir_id => pn_rec.isir_id,
1017 x_ci_sequence_number => pn_rec.ci_sequence_number,
1018 x_ci_cal_type => pn_rec.ci_cal_type,
1019 x_sar_field_number => pn_rec.sar_field_number,
1020 x_original_value => pn_rec.original_value,
1021 x_batch_id => gv_batchnum,
1022 x_corrected_value => pn_rec.corrected_value,
1023 x_correction_status => 'BATCHED'
1024 );
1025 END IF;
1026
1027 END LOOP;
1028 CLOSE corr_rec;
1029
1030 lv_curdate := TRUNC(SYSDATE);
1031
1032 OPEN get_baserec;
1033 FETCH get_baserec INTO base_rec;
1034
1035 IF get_baserec%NOTFOUND THEN
1036 NULL;
1037 ELSE
1038 igf_ap_fa_base_rec_pkg.update_row(
1039 x_mode => 'R',
1040 x_rowid => base_rec.row_id,
1041 x_base_id => base_rec.base_id,
1042 x_ci_cal_type => base_rec.ci_cal_type,
1043 x_person_id => base_rec.person_id,
1044 x_ci_sequence_number => base_rec.ci_sequence_number,
1045 x_org_id => base_rec.org_id,
1046 x_coa_pending => base_rec.coa_pending,
1047 x_verification_process_run => base_rec.verification_process_run,
1048 x_inst_verif_status_date => base_rec.inst_verif_status_date,
1049 x_manual_verif_flag => base_rec.manual_verif_flag,
1050 x_fed_verif_status => 'CORRSENT' ,
1051 x_fed_verif_status_date => base_rec.fed_verif_status_date,
1052 x_inst_verif_status => base_rec.inst_verif_status,
1053 x_nslds_eligible => base_rec.nslds_eligible,
1054 x_ede_correction_batch_id => gv_batchnum, --Modified by rbezawad on 22-Jun-2001 w.r.t. Bug ID: 1821811
1055 x_fa_process_status_date => base_rec.fa_process_status_date,
1056 x_isir_corr_status => corr_stat,
1057 x_isir_corr_status_date => lv_curdate,
1058 x_isir_status => base_rec.isir_status,
1059 x_isir_status_date => base_rec.isir_status_date,
1060 x_coa_code_f => base_rec.coa_code_f,
1061 x_coa_code_i => base_rec.coa_code_i,
1062 x_coa_f => base_rec.coa_f,
1063 x_coa_i => base_rec.coa_i,
1064 x_disbursement_hold => base_rec.disbursement_hold,
1065 x_fa_process_status => base_rec.fa_process_status,
1066 x_notification_status => base_rec.notification_status,
1067 x_notification_status_date => base_rec.notification_status_date,
1068 x_packaging_status => base_rec.packaging_status,
1069 x_packaging_status_date => base_rec.packaging_status_date,
1070 x_total_package_accepted => base_rec.total_package_accepted,
1071 x_total_package_offered => base_rec.total_package_offered,
1072 x_admstruct_id => base_rec.admstruct_id,
1073 x_admsegment_1 => base_rec.admsegment_1,
1074 x_admsegment_2 => base_rec.admsegment_2,
1075 x_admsegment_3 => base_rec.admsegment_3,
1076 x_admsegment_4 => base_rec.admsegment_4,
1077 x_admsegment_5 => base_rec.admsegment_5,
1078 x_admsegment_6 => base_rec.admsegment_6,
1079 x_admsegment_7 => base_rec.admsegment_7,
1080 x_admsegment_8 => base_rec.admsegment_8,
1081 x_admsegment_9 => base_rec.admsegment_9,
1082 x_admsegment_10 => base_rec.admsegment_10,
1083 x_admsegment_11 => base_rec.admsegment_11,
1084 x_admsegment_12 => base_rec.admsegment_12,
1085 x_admsegment_13 => base_rec.admsegment_13,
1086 x_admsegment_14 => base_rec.admsegment_14,
1087 x_admsegment_15 => base_rec.admsegment_15,
1088 x_admsegment_16 => base_rec.admsegment_16,
1089 x_admsegment_17 => base_rec.admsegment_17,
1090 x_admsegment_18 => base_rec.admsegment_18,
1091 x_admsegment_19 => base_rec.admsegment_19,
1092 x_admsegment_20 => base_rec.admsegment_20,
1093 x_packstruct_id => base_rec.packstruct_id,
1094 x_packsegment_1 => base_rec.packsegment_1,
1095 x_packsegment_2 => base_rec.packsegment_2,
1096 x_packsegment_3 => base_rec.packsegment_3,
1097 x_packsegment_4 => base_rec.packsegment_4,
1098 x_packsegment_5 => base_rec.packsegment_5,
1099 x_packsegment_6 => base_rec.packsegment_6,
1100 x_packsegment_7 => base_rec.packsegment_7,
1101 x_packsegment_8 => base_rec.packsegment_8,
1102 x_packsegment_9 => base_rec.packsegment_9,
1103 x_packsegment_10 => base_rec.packsegment_10,
1104 x_packsegment_11 => base_rec.packsegment_11,
1105 x_packsegment_12 => base_rec.packsegment_12,
1106 x_packsegment_13 => base_rec.packsegment_13,
1107 x_packsegment_14 => base_rec.packsegment_14,
1108 x_packsegment_15 => base_rec.packsegment_15,
1109 x_packsegment_16 => base_rec.packsegment_16,
1110 x_packsegment_17 => base_rec.packsegment_17,
1111 x_packsegment_18 => base_rec.packsegment_18,
1112 x_packsegment_19 => base_rec.packsegment_19,
1113 x_packsegment_20 => base_rec.packsegment_20,
1114 x_miscstruct_id => base_rec.miscstruct_id,
1115 x_miscsegment_1 => base_rec.miscsegment_1,
1116 x_miscsegment_2 => base_rec.miscsegment_2,
1117 x_miscsegment_3 => base_rec.miscsegment_3,
1118 x_miscsegment_4 => base_rec.miscsegment_4,
1119 x_miscsegment_5 => base_rec.miscsegment_5,
1120 x_miscsegment_6 => base_rec.miscsegment_6,
1121 x_miscsegment_7 => base_rec.miscsegment_7,
1122 x_miscsegment_8 => base_rec.miscsegment_8,
1123 x_miscsegment_9 => base_rec.miscsegment_9,
1124 x_miscsegment_10 => base_rec.miscsegment_10,
1125 x_miscsegment_11 => base_rec.miscsegment_11,
1126 x_miscsegment_12 => base_rec.miscsegment_12,
1127 x_miscsegment_13 => base_rec.miscsegment_13,
1128 x_miscsegment_14 => base_rec.miscsegment_14,
1129 x_miscsegment_15 => base_rec.miscsegment_15,
1130 x_miscsegment_16 => base_rec.miscsegment_16,
1131 x_miscsegment_17 => base_rec.miscsegment_17,
1132 x_miscsegment_18 => base_rec.miscsegment_18,
1133 x_miscsegment_19 => base_rec.miscsegment_19,
1134 x_miscsegment_20 => base_rec.miscsegment_20,
1135 x_prof_judgement_flg => base_rec.prof_judgement_flg,
1136 x_nslds_data_override_flg => base_rec.nslds_data_override_flg,
1137 x_target_group => base_rec.target_group,
1138 x_coa_fixed => base_rec.coa_fixed,
1139 x_profile_status => base_rec.profile_status,
1140 x_profile_status_date => base_rec.profile_status_date,
1141 x_profile_fc => base_rec.profile_fc,
1142 x_coa_pell => base_rec.coa_pell,
1143 x_tolerance_amount => base_rec.tolerance_amount,
1144 x_manual_disb_hold => base_rec.manual_disb_hold,
1145 x_pell_alt_expense => base_rec.pell_alt_expense,
1146 x_assoc_org_num => base_rec.assoc_org_num,
1147 x_award_fmly_contribution_type => base_rec.award_fmly_contribution_type,
1148 x_isir_locked_by => base_rec.isir_locked_by,
1149 x_adnl_unsub_loan_elig_flag => base_rec.adnl_unsub_loan_elig_flag,
1150 x_lock_awd_flag => base_rec.lock_awd_flag,
1151 x_lock_coa_flag => base_rec.lock_coa_flag
1152
1153 );
1154 END IF;
1155 CLOSE get_baserec;
1156
1157 EXCEPTION
1158 WHEN OTHERS THEN
1159 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1160 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_process_corrections.update_corr.exception','The exception is : ' || SQLERRM );
1161 END IF;
1162 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1163 fnd_message.set_token('NAME','IGF_AP_PROCESS_CORRECTIONS.UPDATE_CORR');
1164 fnd_file.put_line(fnd_file.log,SQLERRM);
1165 igs_ge_msg_stack.add;
1166 app_exception.raise_exception;
1167
1168 END update_corr;
1169
1170 PROCEDURE prepare_file( errbuf OUT NOCOPY VARCHAR2,
1171 retcode OUT NOCOPY NUMBER,
1172 p_award_year IN VARCHAR2,
1173 p_base_id IN NUMBER,
1174 school_type IN VARCHAR2,
1175 p_school_code IN VARCHAR2,
1176 eti_dest_code IN VARCHAR2,
1177 eti_dest_num IN VARCHAR2
1178 )
1179 IS
1180 /*
1181 || Created By : Sridhar
1182 || Created On : 25-NOV-2000
1183 || Purpose : Fetches the Corrected values and formats them into strings
1184 || as per EDE standards and writes them into files.This is the
1185 || main program called from Conccurrent manager.
1186 || Known limitations, enhancements or remarks :
1187 || Change History :
1188 || Who When What
1189 || tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
1190 || bkkumar 07-May-2004 Bug 3598933 Added the validation for the school code to be 6 characters.
1191 || ugummall 31-OCT-2003 Bug 3102439. FA 126 - Multiple FA Offices.
1192 || 1. Added 5 new parameters namely
1193 || p_base_id,school_type,p_school_code,eti_dest_code,eti_dest_num
1194 || 2. gv_dest_num populated with new passed in parameter eti_dest_num
1195 || g_fed_school_code populated with new passed in parameter p_school_code
1196 || 3. Processed only those records whose baseid's associated org unit's
1197 || federal school code matched with passed in federal school code p_school_code.
1198 || (reverse chronological order - newest change first)
1199 */
1200
1201
1202 -- Bug 4403807 - removed the condition isir.payment_isir = 'Y'
1203 CURSOR get_cnt ( cp_corr_stat VARCHAR2) IS
1204 SELECT COUNT(*)
1205 FROM igf_ap_isir_corr corr,
1206 igf_ap_isir_matched isir
1207 WHERE corr.correction_status = cp_corr_stat
1208 AND corr.isir_id = isir.isir_id
1209 AND isir.system_record_type = 'ORIGINAL'
1210 AND isir.batch_year = gv_batch_year ;
1211
1212 CURSOR c_get_corr_isir(cp_base_id NUMBER) IS
1213 SELECT s_email_address
1214 FROM igf_ap_isir_matched_all
1215 WHERE system_record_type='CORRECTION'
1216 AND base_id = cp_base_id;
1217
1218 l_get_corr_isir c_get_corr_isir%ROWTYPE;
1219
1220 l_corr_stat VARCHAR2(30) ;
1221 lv_corr_cnt NUMBER DEFAULT 0;
1222 l_corr_status VARCHAR2(30) ;
1223 x_fed_school_code igs_or_org_alt_ids.org_alternate_id%TYPE;
1224 x_return_status VARCHAR2(1);
1225 x_msg_data fnd_new_messages.message_name%TYPE;
1226 lv_write_header_data VARCHAR2(1) DEFAULT 'Y';
1227 l_process_corr_flag VARCHAR2(1);
1228
1229 BEGIN
1230 retcode := 0;
1231 errbuf := NULL;
1232 igf_aw_gen.set_org_id(NULL);
1233 gv_cal_type := RTRIM(SUBSTR(p_award_year,1,10));
1234 gn_sequence_number := TO_NUMBER(RTRIM(SUBSTR(p_award_year,11)));
1235
1236 IF p_school_code IS NOT NULL THEN
1237 IF SUBSTR(p_school_code,1,1) NOT IN ('0','B','E','G')
1238 OR LENGTH(p_school_code) <> 6 THEN -- the school code entered is invalid
1239 fnd_message.set_name('IGF','IGF_AP_INVALID_FED_SCH');
1240 fnd_file.put_line(fnd_file.log, fnd_message.get);
1241 RETURN;
1242 END IF;
1243 END IF;
1244
1245 gv_dest_num := eti_dest_num;
1246 g_fed_school_code := p_school_code;
1247
1248 /* Get Batch Year Information to get the batch year mapping */
1249
1250 OPEN batch_yr_cur;
1251 FETCH batch_yr_cur INTO gv_batch_year;
1252
1253 IF batch_yr_cur%NOTFOUND THEN
1254 gv_batch_year := '****';
1255 fnd_message.set_name('IGF','IGF_AP_INVALID_BATCH_YR');
1256 fnd_file.put_line(fnd_file.log, fnd_message.get);
1257 END IF;
1258 CLOSE batch_yr_cur;
1259
1260 IF ((gv_batch_year = '5') OR (gv_batch_year = '6') OR (gv_batch_year= '7')) THEN
1261 IF gv_dest_num IS NOT NULL THEN
1262 fnd_message.set_name('IGF','IGF_AP_ETI_DESTNUM_NOT_BLANK');
1263 fnd_file.put_line(fnd_file.log, fnd_message.get);
1264 RETURN;
1265 END IF;
1266 ELSIF gv_dest_num IS NULL THEN
1267 fnd_message.set_name('IGF','IGF_AP_ETI_DESTNUM_BLANK');
1268 fnd_file.put_line(fnd_file.log, fnd_message.get);
1269 RETURN;
1270 END IF;
1271
1272 l_corr_stat := 'READY' ;
1273 OPEN get_cnt ( l_corr_stat );
1274 FETCH get_cnt INTO lv_corr_cnt;
1275 CLOSE get_cnt;
1276
1277 IF lv_corr_cnt > 0 THEN /* if 1*/
1278 OPEN match_isirs(p_base_id);
1279 LOOP /* loop 1*/
1280
1281 gn_isir_id := NULL;
1282 gv_s_email := NULL;
1283 gv_trn_num := NULL;
1284 gv_ori_ssn := NULL;
1285 gv_ori_name := NULL;
1286 gv_first_name := NULL;
1287 gv_last_name := NULL;
1288 gn_baseid := NULL;
1289 gv_person_number := NULL;
1290 l_process_corr_flag := 'Y';
1291
1292 FETCH match_isirs INTO gn_isir_id,gv_s_email,gv_trn_num,gv_ori_ssn,gv_ori_name,
1293 gv_first_name,gv_last_name,gn_baseid,gv_person_number;
1294 EXIT WHEN match_isirs%NOTFOUND;
1295 --
1296 -- Log Message indicating Person Record Processed.
1297 --
1298 fnd_file.new_line(fnd_file.log,1);
1299 fnd_message.set_name('IGF','IGF_AP_PROCESSING_STUDENT');
1300 fnd_message.set_token('PERSON_NAME',gv_first_name||' '||gv_last_name);
1301 fnd_message.set_token('PERSON_NUMBER',gv_person_number);
1302 fnd_file.put_line(fnd_file.log,fnd_message.get);
1303
1304 x_return_status := NULL;
1305 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1306 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_process_corrections.prepare_file.debug','The gn_baseid passed to get_stu_fao_code : ' || gn_baseid );
1307 END IF;
1308
1309
1310 -- Get baseid's (match_isirs's gn_baseid) associated org unit's federal school code.
1311 igf_sl_gen.get_stu_fao_code(gn_baseid, 'FED_SCH_CD', x_fed_school_code, x_return_status, x_msg_data);
1312
1313 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1314 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_process_corrections.prepare_file.debug','The x_fed_school_code returned : ' || x_fed_school_code );
1315 END IF;
1316
1317 /* -- Commenting the code for future refference
1318 IF (x_return_status = 'E') AND (NVL(x_msg_data,'X') = 'IGF_AP_STU_FED_SCH_CD_NFND') THEN
1319 -- skip this record and do not create the corrections and log the message.
1320 FND_MESSAGE.SET_NAME('IGF', x_msg_data);
1321 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
1322 l_process_corr_flag := 'N'; -- Do not process Corrections
1323
1324 ELSIF (x_return_status = 'E') AND (NVL(x_msg_data,'X') <> 'IGF_AP_STU_FED_SCH_CD_NFND') THEN
1325 -- do not skip this record and create the corrections using the school code supplied as parameter and log the message.
1326 FND_MESSAGE.SET_NAME('IGF', x_msg_data);
1327 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
1328 x_fed_school_code := p_school_code;
1329 l_process_corr_flag := 'Y'; -- Process Corrections
1330
1331 ELSIF (x_return_status = 'S') AND (NVL(x_fed_school_code,'X') <> p_school_code) THEN
1332 -- skip this record and do not create the corrections with the supplied school code and log the message.
1333 FND_MESSAGE.SET_NAME('IGF', 'IGF_AP_FEDSCH_NOT_CONTEXT');
1334 FND_MESSAGE.SET_TOKEN('PERSON_NUM',gv_person_number);
1335 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
1336 l_process_corr_flag := 'N'; -- Do not process Corrections
1337
1338 ELSIF (x_return_status = 'S') AND (x_fed_school_code = p_school_code) THEN
1339 l_process_corr_flag := 'Y'; -- Process Corrections
1340
1341 END IF;
1342 */
1343
1344 IF (x_return_status = 'E') THEN
1345 -- skip this record and do not create the corrections and log the message.
1346 FND_MESSAGE.SET_NAME('IGF', x_msg_data);
1347 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
1348 l_process_corr_flag := 'N'; -- Do not process Corrections
1349
1350 ELSIF (NVL(x_fed_school_code,'X') <> p_school_code) THEN
1351 -- skip this record and do not create the corrections with the supplied school code and log the message.
1352 FND_MESSAGE.SET_NAME('IGF', 'IGF_AP_FEDSCH_NOT_CONTEXT');
1353 FND_MESSAGE.SET_TOKEN('PERSON_NUM',gv_person_number);
1354 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
1355 l_process_corr_flag := 'N'; -- Do not process Corrections
1356
1357 ELSE
1358 l_process_corr_flag := 'Y'; -- Process Corrections
1359 END IF;
1360
1361 IF (l_process_corr_flag = 'Y') THEN -- Process Corrections
1362 -- Get the corrected student's email address from the correction isir
1363 OPEN c_get_corr_isir(gn_baseid);
1364 FETCH c_get_corr_isir INTO l_get_corr_isir;
1365 gv_s_email := l_get_corr_isir.s_email_address;
1366 CLOSE c_get_corr_isir;
1367
1368 OPEN corr_hold;
1369 FETCH corr_hold INTO lc_corr_hold;
1370 IF corr_hold%FOUND THEN -- If Hold Exists check
1371 fnd_message.set_name('IGF','IGF_AP_CORR_HOLD_EXIST');
1372 fnd_message.set_token('PERSON_NUMBER',gv_person_number);
1373 fnd_file.put_line(fnd_file.log,fnd_message.get);
1374 ELSE -- No Hold so proceed with corrections
1375 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1376 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_process_corrections.prepare_file.debug','Before calling fill_string ');
1377 END IF;
1378 gn_std_cnt := gn_std_cnt + 1;
1379 start_record;
1380 l_corr_status := 'READY' ;
1381 OPEN corrs ( l_corr_status ) ;
1382 LOOP
1383 FETCH corrs INTO lc_corr_cur;
1384 EXIT WHEN corrs%NOTFOUND;
1385 gn_cnt := corrs%ROWCOUNT;
1386 IF MOD(gn_cnt,12) = 0 THEN
1387 fill_string(lc_corr_cur.sar_field_number, lc_corr_cur.corrected_value);
1388 -- write header record only if there are corrections to send.
1389 IF(lv_write_header_data = 'Y')THEN
1390 -- write header only if the batch year is 3 or 4
1391 IF gv_batch_year = '3' OR gv_batch_year = '4' THEN
1392 write_file('HEADER');
1393 lv_write_header_data := 'N';
1394 END IF;
1395 END IF;
1396 write_file('DATA');
1397 start_record;
1398 ELSE
1399 fill_string(lc_corr_cur.sar_field_number, lc_corr_cur.corrected_value);
1400 END IF;
1401 END LOOP;
1402
1403 IF MOD(gn_cnt,12) <> 0 THEN
1404 IF(lv_write_header_data = 'Y')THEN
1405 -- write header only if the batch year is 3 or 4
1406 IF gv_batch_year = '3' OR gv_batch_year = '4' THEN
1407 write_file('HEADER');
1408 lv_write_header_data := 'N';
1409 END IF;
1410 END IF;
1411 write_file('DATA');
1412 END IF;
1413
1414 update_corr;
1415 CLOSE corrs;
1416 END IF; -- If Hold Exists check
1417 CLOSE corr_hold;
1418 END IF; -- End process correction check
1419
1420
1421 END LOOP; /* end of loop 1*/
1422 CLOSE match_isirs;
1423 IF(lv_write_header_data = 'N')THEN
1424 -- write trailer only if the batch year is 3 or 4
1425 IF gv_batch_year = '3' OR gv_batch_year = '4' THEN
1426 write_file('TRAILER');
1427 END IF;
1428 END IF;
1429 COMMIT;
1430 ELSE
1431 fnd_message.set_name ('IGF','IGF_AP_NO_ISIR_FOR_BATCH');
1432 fnd_file.put_line(fnd_file.log, fnd_message.get);
1433 END IF; /* end of if 1*/
1434 EXCEPTION
1435 WHEN OTHERS THEN
1436 ROLLBACK;
1437 retcode := 2;
1438 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1439 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_process_corrections.prepare_file.exception','The exception is : ' || SQLERRM );
1440 END IF;
1441 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1442 errbuf := fnd_message.get;
1443 fnd_file.put_line(fnd_file.log,SQLERRM);
1444 igs_ge_msg_stack.add;
1445 END prepare_file;
1446
1447 END igf_ap_process_corrections;