[Home] [Help]
PACKAGE BODY: APPS.IGF_AP_ISIR_IMPORT_PKG
Source
1 PACKAGE BODY IGF_AP_ISIR_IMPORT_PKG AS
2 /* $Header: IGFAP01B.pls 120.2 2006/02/10 02:47:08 bvisvana noship $ */
3
4 g_ISIR_rec igf_ap_ISIR_matched%ROWTYPE;
5 g_base_rec igf_ap_fa_base_rec%ROWTYPE;
6 g_paid_efc igf_ap_fa_base_rec.efc_f%TYPE;
7 g_pell_grant_elig_flag igf_ap_fa_base_rec_all.pell_eligible%TYPE;
8 g_nslds_match_flag igf_ap_fa_base_rec.nslds_eligible%TYPE;
9 g_verification_flag igf_ap_ISIR_matched.verification_flag%TYPE;
10 g_ISIR_id igf_ap_ISIR_matched.ISIR_id%TYPE;
11 g_base_id igf_ap_fa_base_rec.base_id%TYPE;
12 g_fed_verif_status igf_ap_fa_base_rec_all.fed_verif_status%TYPE;
13 g_msg_body VARCHAR2(4000) := NULL;
14 g_transaction_num CHAR(13);
15 g_cnt NUMBER := 1;
16 l_document VARCHAR2(4000);
17 l_document_type VARCHAR2(4000);
18
19 -- added by rgangara as part of FA138 enh
20 g_batch_year igf_ap_batch_aw_map.batch_year%TYPE;
21 g_match_code igf_ap_record_match_all.match_code%TYPE;
22 g_rec_status igf_ap_isir_ints_all.record_status%TYPE;
23 g_rec_type igf_ap_isir_ints_all.processed_rec_type%TYPE;
24 g_message_Class igf_ap_isir_ints_all.data_file_name_txt%TYPE;
25 g_school_code igf_ap_isir_ints_all.first_college_cd%TYPE;
26 g_del_int VARCHAR2(1);
27 g_force_add VARCHAR2(1);
28 g_create_inquiry VARCHAR2(1);
29 g_adm_source_type VARCHAR2(30);
30
31 g_where VARCHAR2(32000);
32 g_total_recs_fetched NUMBER;
33
34 -- define a PL/SQL table
35 TYPE T_int_si_id IS TABLE OF igf_ap_isir_ints_all.si_id%TYPE;
36 TYPE T_int_batch_yr IS TABLE OF igf_ap_isir_ints_all.batch_year_num%TYPE;
37 TYPE T_int_orig_ssn IS TABLE OF igf_ap_isir_ints_all.original_ssn_txt%TYPE;
38 TYPE T_int_orig_id IS TABLE OF igf_ap_isir_ints_all.orig_name_id_txt%TYPE;
39
40 TYPE T_int_prnt_req_id IS TABLE OF igf_ap_isir_ints_all.orig_name_id_txt%TYPE;
41 TYPE T_int_sub_req_num IS TABLE OF igf_ap_isir_ints_all.orig_name_id_txt%TYPE;
42 -- define global variables of corresp type and initialize
43 g_si_id_tab T_int_si_id := T_int_si_id();
44 g_batch_year_num_tab T_int_batch_yr := T_int_batch_yr();
45 g_original_ssn_txt_tab T_int_orig_ssn := T_int_orig_ssn();
46 g_orig_name_id_txt_tab T_int_orig_id := T_int_orig_id();
47 g_parent_req_id_tab T_int_prnt_req_id := T_int_prnt_req_id();
48 g_sub_req_num_tab T_int_sub_req_num := T_int_sub_req_num();
49
50
51 PROCEDURE log_debug_message(m VARCHAR2)
52 IS
53 -- for debug message logging
54 --g_debug_seq NUMBER:=0; --- #R1 Remove after debugging
55 BEGIN
56 --fnd_file.put_line(fnd_file.log, m);
57 --g_debug_seq := g_debug_seq + 1;
58 --INSERT INTO RAN_DEBUG values (g_debug_seq,m);
59 NULL;
60 END;
61
62 FUNCTION get_msg_class( p_isir_type IN VARCHAR2)
63 RETURN VARCHAR2
64 /*
65 || Created By : rasahoo
66 || Created On : 22-NOV-2004
67 || Purpose : Returns the message class
68 || Known limitations, enhancements or remarks :
69 || Change History :
70 || Who When What
71 || (reverse chronological order - newest change first)
72 */
73 IS
74
75 CURSOR c_msg_class( p_lookup_type VARCHAR2, p_lookup_code VARCHAR2 ) IS
76 SELECT LOOKUP_CODE
77 FROM igf_lookups_view
78 WHERE lookup_type = p_lookup_type
79 AND tag = p_lookup_code
80 AND enabled_flag = 'Y';
81
82 l_msg_class c_msg_class%ROWTYPE;
83
84 ret_val VARCHAR2(100);
85 BEGIN
86 ret_val := NULL;
87 OPEN c_msg_class('IGF_AP_ISIR_MESSAGE_CLASS',p_isir_type);
88 LOOP
89 FETCH c_msg_class INTO l_msg_class;
90 EXIT WHEN c_msg_class%NOTFOUND;
91 IF ret_val IS NOT NULL THEN
92 ret_val := ret_val || ',';
93 END IF;
94 ret_val := ret_val||''''||l_msg_class.lookup_code || '''';
95 END LOOP;
96 CLOSE c_msg_class;
97 return ret_val;
98 END get_msg_class;
99 ------------------------------------------------------------------------------------
100 -- Function to check whether coreection is initiated from the this school or not.
101 ------------------------------------------------------------------------------------
102 FUNCTION l_is_cor_from_same_school(p_ISIR_id NUMBER)
103 RETURN BOOLEAN
104 IS
105 /*
106 || Created By : brajendr
107 || Created On : 08-NOV-2000
108 || Purpose : Checks whether the ISIR Correction is intiated from the same school.
109 || Known limitations, enhancements or remarks :
110 || Change History :
111 || Who When What
112 || ugummall 27-OCT-2003 Bug 3102439. FA 126 - Multiple FA Offices.
113 || removed cursor cur_fed_code and its references.
114 || Added new cursor cur_get_base_id.
115 || (reverse chronological order - newest change first)
116 */
117
118 -- Get the base_id as well as 6 school codes for p_isir_id.
119 CURSOR cur_get_base_id IS
120 SELECT base_id,
121 first_college,
122 second_college,
123 third_college,
124 fourth_college,
125 fifth_college,
126 sixth_college
127 FROM igf_ap_isir_matched
128 WHERE isir_id = p_ISIR_id;
129 l_get_base_id_rec cur_get_base_id%ROWTYPE;
130
131 x_fed_sch_cd igs_or_org_alt_ids.org_alternate_id%TYPE;
132 x_return_status VARCHAR2(1);
133 x_msg_data VARCHAR2(30);
134
135 BEGIN
136
137 -- FA 126.
138 OPEN cur_get_base_id;
139 FETCH cur_get_base_id INTO l_get_base_id_rec;
140 CLOSE cur_get_base_id;
141
142 -- Derive Federal School Code.
143 igf_sl_gen.get_stu_fao_code(l_get_base_id_rec.base_id, 'FED_SCH_CD', x_fed_sch_cd, x_return_status, x_msg_data);
144 IF (x_return_status = 'E') THEN
145 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
146 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'igf.plsql.igf_ap_isir_import_pkg.l_is_cor_from_same_school.debug','x_msg_data : ' || x_msg_data);
147 END IF;
148 RETURN FALSE;
149 ELSE
150 -- write debug message with federal school code.
151 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
152 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'igf.plsql.igf_ap_isir_import_pkg.l_is_cor_from_same_school.debug','x_fed_sch_cd : ' || x_fed_sch_cd);
153 END IF;
154 -- check wether federal school code matches with any of 6 codes
155 IF ( x_fed_sch_cd = l_get_base_id_rec.first_college OR
156 x_fed_sch_cd = l_get_base_id_rec.second_college OR
157 x_fed_sch_cd = l_get_base_id_rec.third_college OR
158 x_fed_sch_cd = l_get_base_id_rec.fourth_college OR
159 x_fed_sch_cd = l_get_base_id_rec.fifth_college OR
160 x_fed_sch_cd = l_get_base_id_rec.sixth_college
161 ) THEN
162 RETURN TRUE;
163 ELSE
164 RETURN FALSE;
165 END IF;
166 END IF;
167
168 EXCEPTION
169 WHEN others THEN
170 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
171 fnd_message.set_token('NAME','IGF_AP_ISIR_IMPORT_PKG.L_IS_COR_FROM_SAME_SCHOOL');
172 fnd_file.put_line(fnd_file.log,SQLERRM);
173 igs_ge_msg_stack.add;
174 app_exception.raise_exception;
175
176 END l_is_cor_from_same_school;
177
178
179 PROCEDURE main_import_process ( errbuf OUT NOCOPY VARCHAR2,
180 retcode OUT NOCOPY NUMBER,
181 p_org_id IN NUMBER,
182 p_award_year IN VARCHAR2,
183 p_force_add IN VARCHAR2,
184 p_create_inquiry IN VARCHAR2,
185 p_adm_source_type IN VARCHAR2,
186 p_match_code IN VARCHAR2,
187 p_rec_type IN VARCHAR2,
188 p_rec_status IN VARCHAR2,
189 p_message_class IN VARCHAR2,
190 p_school_type IN VARCHAR2,
191 p_school_code IN VARCHAR2,
192 p_del_int IN VARCHAR2,
193 p_spawn_process IN VARCHAR2,
194 p_upd_ant_val IN VARCHAR2
195 )
196
197 IS
198
199 /*
200 || Created By : rgangara
201 || Created On : 06-AUG-2004
202 || Purpose : Main process which in turn calls the Matching process by passing either SI_ID or a PL/sQL table.
203 || Known limitations, enhancements or remarks :
204 || Change History :
205 || (reverse chronological order - newest change first)
206
207 || Who When What
208 ||
209 */
210
211 CURSOR c_batch(cp_cal_type VARCHAR2,
212 cp_seq_number NUMBER) IS
213 SELECT batch_year
214 FROM igf_ap_batch_aw_map_all
215 WHERE ci_cal_type = cp_cal_type
216 AND ci_sequence_number = cp_seq_number;
217
218 l_batch c_batch%ROWTYPE;
219 l_sql VARCHAR2(32000);
220 l_add_and VARCHAR2(1);
221 ln_total_rec NUMBER := 0;
222
223 l_cal_type igf_ap_fa_base_rec_all.ci_cal_type%TYPE ;
224 l_seq_number igf_ap_fa_base_rec_all.ci_sequence_number%TYPE;
225 l_batch_year igf_ap_batch_aw_map_all.batch_year%TYPE;
226
227 g_parent_req_number NUMBER;
228 ------------------------------------------------------------
229 -- Begin of Local new Procedures created for FA138 build - rgangara.
230 ------------------------------------------------------------
231 PROCEDURE launch_sub_request(p_sub_req_number NUMBER,
232 p_sub_req_rec_cnt NUMBER)
233 IS
234 /*
235 || Created By : rgangara
236 || Created On : 28-JUL-2004
237 || Purpose : For records distribution and launching spawned/parallel processes.
238 || Known limitations, enhancements or remarks :
239 || Change History :
240 || Who When What
241 || (reverse chronological order - newest change first)
242 */
243 l_request_id NUMBER;
244 l_recs_to_process NUMBER; -- No. of records to process
245
246 BEGIN
247
248 l_request_id := Fnd_Request.Submit_Request
249 ('IGF',
250 'IGFAPJ30',
251 'ISIR Internal Spawned Import Process',
252 NULL,
253 FALSE,
254 p_force_add,
255 p_create_inquiry,
256 p_adm_source_type,
257 g_batch_year,
258 p_match_code,
259 p_del_int,
260 g_parent_req_number,
261 p_sub_req_number,
262 NULL,
263 p_upd_ant_val,
264 CHR(0),
265 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
266 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
267 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
268 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
269 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
270 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
271 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
272 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
273 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
274 );
275
276
277 IF l_request_id > 0 THEN
278 -- successfully submitted then log message
279 fnd_file.put_line( fnd_file.LOG ,' ');
280 fnd_message.set_name('IGS','IGF_AP_SPAWN_REQ_SUBMIT');
281 fnd_message.set_token('REQUEST_ID', l_request_id);
282 fnd_message.set_token('SPAWN_ID', p_sub_req_number);
283 fnd_message.set_token('TOTAL_RECS', p_sub_req_rec_cnt);
284 fnd_file.put_line(fnd_file.log,fnd_message.get);
285 ELSE
286 -- if error then log message
287 fnd_message.set_name('IGS','IGF_AP_FAIL_SBMT_SPAWN_PROC');
288 fnd_message.set_token('SPAWN_ID', p_sub_req_number);
289 fnd_file.put_line( fnd_file.LOG ,fnd_message.get);
290 END IF;
291
292 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
293 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_import_pkg.spawn_processes.debug','Launched spawned request ' || p_sub_req_number || ' Request ID : ' || l_request_id);
294 END IF;
295
296 EXCEPTION
297 WHEN OTHERS THEN
298 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
299 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_import_pkg.launch_sub_request.exception','The exception is : ' || SQLERRM );
300 END IF;
301
302 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
303 fnd_message.set_token('NAME','IGF_AP_ISIR_IMPORT_PKG.LAUNCH_SUB_REQUEST');
304 fnd_file.put_line(fnd_file.log,fnd_message.get);
305 igs_ge_msg_stack.add;
306 RETURN; -- continue processing for the next sub request.
307 END launch_sub_request;
308
309
310 PROCEDURE validate_parameters(errbuf OUT NOCOPY VARCHAR2, Retcode OUT NOCOPY NUMBER)
311 IS
312 /*
313 || Created By : rgangara
314 || Created On : 28-JUL-2004
315 || Purpose : Validates all the input parameters which are copied to global variables.
316 || Known limitations, enhancements or remarks :
317 || Change History :
318 || Who When What
319 || (reverse chronological order - newest change first)
320 */
321
322 CURSOR cur_batch_aw_map(cp_batch_yr NUMBER) IS
323 SELECT 'Y'
324 FROM igf_ap_batch_aw_map
325 WHERE batch_year = cp_batch_yr;
326
327 CURSOR cur_lookups(cp_lkup_type igf_lookups_view.lookup_type%TYPE,
328 cp_lkup_code igf_lookups_view.lookup_code%TYPE) IS
329 SELECT 'Y'
330 FROM igf_lookups_view
331 WHERE lookup_type = cp_lkup_type
332 AND lookup_code = cp_lkup_code
333 AND enabled_flag = 'Y';
334
335 CURSOR cur_school_cd(cp_school_cd VARCHAR2) IS
336 SELECT
337 'Y'
338 FROM
339 hz_parties hz,
340 igs_or_org_alt_ids oli,
341 igs_or_org_alt_idtyp olt
342 WHERE oli.org_structure_id = hz.party_number
343 AND oli.org_alternate_id_type = olt.org_alternate_id_type
344 AND SYSDATE BETWEEN oli.start_date AND nvl (end_date, SYSDATE)
345 AND hz.status = 'A'
346 AND olt.system_id_type = 'FED_SCH_CD'
347 AND oli.org_alternate_id = cp_school_cd ;
348
349 CURSOR cur_match_set(cp_match_code igf_ap_record_match_all.match_code%TYPE) IS
350 SELECT 'Y'
351 FROM igf_ap_record_match
352 WHERE match_code = cp_match_code
353 AND enabled_flag = 'Y';
354
355 l_valid_found VARCHAR2(1);
356
357 BEGIN
358 -----------------------------------------------------------------------------
359 -- PARAMETER VALIDATIONS
360 -----------------------------------------------------------------------------
361
362 -- Batch Year Validation (Mandatory parameter)
363 OPEN cur_batch_aw_map(g_batch_year) ;
364 FETCH cur_batch_aw_map INTO l_valid_found ;
365
366 IF cur_batch_aw_map%NOTFOUND THEN
367 CLOSE cur_batch_aw_map ;
368 fnd_message.set_name('IGF','IGF_AP_BATCH_YEAR_NOT_FOUND');
369 errbuf := fnd_message.get;
370 igs_ge_msg_stack.add;
371 retcode := 2;
372 RETURN;
373 END IF ;
374 CLOSE cur_batch_aw_map ;
375
376 log_debug_message(' Record type validation... ' || g_rec_type);
377 -- Record Type validation
378 IF g_rec_type IS NOT NULL THEN
379 l_valid_found := 'N';
380 OPEN cur_lookups ('IGF_AP_ISIR_REC_TYPE', g_rec_type);
381 FETCH cur_lookups INTO l_valid_found;
382
383 IF cur_lookups%NOTFOUND THEN
384 CLOSE cur_lookups;
385 fnd_message.set_name('IGF','IGF_AP_INVALID_PARAMETER');
386 fnd_message.set_token('PARAM_TYPE', 'RECORD TYPE');
387 errbuf := fnd_message.get;
388 igs_ge_msg_stack.add;
389 retcode := 2;
390 RETURN;
391 END IF ;
392 CLOSE cur_lookups;
393 END IF;
394
395
396 log_debug_message(' Record status validation... ' || g_rec_status);
397 -- Record Status validation
398 IF g_rec_status IS NOT NULL THEN
399 l_valid_found := 'N';
400 OPEN cur_lookups ('IGF_AP_ISIR_STATUS', g_rec_status);
401 FETCH cur_lookups INTO l_valid_found;
402
403 IF cur_lookups%NOTFOUND THEN
404 CLOSE cur_lookups;
405 fnd_message.set_name('IGF','IGF_AP_INVALID_PARAMETER');
406 fnd_message.set_token('PARAM_TYPE', 'RECORD STATUS');
407 errbuf := fnd_message.get;
408 igs_ge_msg_stack.add;
409 retcode := 2;
410 RETURN;
411 END IF ;
412 CLOSE cur_lookups;
413 END IF;
414
415 log_debug_message(' Message class validation... ' || g_message_class);
416 -- Message Class validation
417 IF g_message_class IS NOT NULL THEN
418 l_valid_found := 'N';
419 OPEN cur_lookups ('IGF_AP_ISIR_TYPE', g_message_class);
420 FETCH cur_lookups INTO l_valid_found;
421
422 IF cur_lookups%NOTFOUND THEN
423 CLOSE cur_lookups;
424 fnd_message.set_name('IGF','IGF_AP_INVALID_PARAMETER');
425 fnd_message.set_token('PARAM_TYPE', 'MESSAGE CLASS');
426 errbuf := fnd_message.get;
427 igs_ge_msg_stack.add;
428 retcode := 2;
429 RETURN;
430 END IF ;
431 CLOSE cur_lookups;
432 END IF;
433
434
435 log_debug_message(' Schoold code validation... ' || g_school_code);
436 -- School Code validation
437 IF g_school_code IS NOT NULL THEN
438 l_valid_found := 'N';
439 OPEN cur_school_cd (g_school_code);
440 FETCH cur_school_cd INTO l_valid_found;
441
442 IF cur_school_cd%NOTFOUND THEN
443 CLOSE cur_school_cd;
444 fnd_message.set_name('IGF','IGF_AP_INVALID_PARAMETER');
445 fnd_message.set_token('PARAM_TYPE', 'SCHOOL CODE');
446 errbuf := fnd_message.get;
447 igs_ge_msg_stack.add;
448 retcode := 2;
449 RETURN;
450 END IF ;
451 CLOSE cur_school_cd;
452 END IF;
453
454
455 log_debug_message(' Match Code validation... ' || g_match_code);
456 -- Match Code validation (Mandatory parameter)
457 l_valid_found := 'N';
458 OPEN cur_match_set(g_match_code);
459 FETCH cur_match_set INTO l_valid_found;
460
461 IF cur_match_set%NOTFOUND THEN
462 CLOSE cur_match_set;
463 fnd_message.set_name('IGF','IGF_AP_INVALID_PARAMETER');
464 fnd_message.set_token('PARAM_TYPE', 'MATCH SET CODE');
465 errbuf := fnd_message.get;
466 igs_ge_msg_stack.add;
467 retcode := 2;
468 RETURN;
469 END IF ;
470 CLOSE cur_match_set;
471
472 log_debug_message(' All validations successful... ');
473
474 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
475 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_import_pkg.validate_parameters.debug', 'Successfully Completed validate_parameters procedure.');
476 END IF;
477
478 EXCEPTION
479 WHEN OTHERS THEN
480 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
481 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_import_pkg.validate_parameters.exception','The exception is : ' || SQLERRM );
482 END IF;
483
484 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
485 fnd_message.set_token('NAME','IGF_AP_ISIR_IMPORT_PKG.VALIDATE_PARAMETERS');
486 fnd_file.put_line(fnd_file.log,fnd_message.get);
487 fnd_file.put_line(fnd_file.log, SQLERRM);
488 igs_ge_msg_stack.add;
489 app_exception.raise_exception;
490 END validate_parameters;
491
492
493
494 PROCEDURE build_selection_criteria
495 IS
496 /*
497 || Created By : rgangara
498 || Created On : 28-JUL-2004
499 || Purpose : Builds the dynamic record selection criteria based on parameters.
500 || Known limitations, enhancements or remarks :
501 || Change History :
502 || Who When What
503 || (reverse chronological order - newest change first)
504 */
505
506 BEGIN
507
508 log_debug_message(' Beginning Building record Selection criteria ');
509
510 -- adding record status filtering
511 IF g_rec_status IS NOT NULL THEN
512
513 IF g_rec_status = 'N' THEN -- New
514 g_where := g_where || ' AND record_status = ' || '''NEW''';
515
516 ELSIF g_rec_status = 'R' THEN -- Review
517 g_where := g_where || ' AND record_status = ' || '''REVIEW''';
518
519 ELSIF g_rec_status = 'U' THEN -- Unmatched
520 g_where := g_where || ' AND record_status = ' || '''UNMATCHED''';
521
522 ELSIF g_rec_status = 'NR' THEN -- New and Review
523 g_where := g_where || ' AND record_status IN (' || '''REVIEW''' || ',' || '''NEW''' || ')';
524
525 ELSIF g_rec_status = 'NU' THEN -- New and Unmatched
526 g_where := g_where || ' AND record_status IN (' || '''NEW''' || ',' || '''UNMATCHED''' || ')';
527
528 ELSIF g_rec_status = 'RU' THEN -- Review and Unmatched
529 g_where := g_where || ' AND record_status IN (' || '''REVIEW''' || ',' || '''UNMATCHED''' || ')';
530
531 END IF;
532
533 ELSE -- g_rec_status
534 -- no rec status filtering given hence process all except matched records.
535 g_where := g_where || ' AND record_status IN (' || '''REVIEW''' || ',' || '''UNMATCHED''' || ',' || '''NEW''' || ')';
536 END IF; -- g_rec_status
537
538
539 log_debug_message(' Record type filtering... ');
540 -- adding Record Type filtering
541 IF g_rec_type IS NOT NULL THEN
542
543 IF g_rec_type = 'O' THEN -- Original ISIR records
544 g_where := g_where || ' AND (processed_rec_type IS NULL OR processed_rec_type NOT IN (''C'',''H''))';
545 ELSE
546 g_where := g_where || ' AND processed_rec_type IN (''C'',''H'')';
547 END IF;
548 END IF;
549
550
551
552 -- adding Message Class filtering RAMMOHAN chk whether function call is efficient or direct derivation.
553 IF g_message_class IS NOT NULL THEN
554 -- g_where := g_where || ' AND ' || '''' || g_message_class || '''' || ' = DECODE(INSTR(data_file_name_txt, ''.''), 0, data_file_name_txt, SUBSTR(data_file_name_txt, 1, INSTR(data_file_name_txt, ''.'')-1))';
555 -- g_where := g_where || ' AND ' || '''' || g_message_class || '''' || ' = igf_ap_matching_process_pkg.get_msg_class_from_filename(data_file_name_txt) ';
556 g_where := g_where || ' AND ' || ' igf_ap_matching_process_pkg.get_msg_class_from_filename(data_file_name_txt) IN (' || get_msg_class(g_message_class) || ')';
557 END IF;
558
559
560 -- adding School Code filtering
561 IF g_school_code IS NOT NULL THEN
562 g_where := g_where || ' AND ' || '''' || g_school_code || '''' || ' IN (first_college_cd, second_college_cd, third_college_cd, fourth_college_cd, fifth_college_cd, sixth_college_cd) ';
563 END IF;
564
565 -- fnd_file.put_line(fnd_file.LOG, ' Dynamic Where Clause : ' || g_where);
566 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
567 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_import_pkg.build_selection_criteria.debug', 'Successfully Completed build_selection_criteria procedure.');
568 END IF;
569
570 EXCEPTION
571 WHEN OTHERS THEN
572 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
573 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_import_pkg.build_selection_criteria.exception','The exception is : ' || SQLERRM );
574 END IF;
575
576 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
577 fnd_message.set_token('NAME','IGF_AP_ISIR_IMPORT_PKG.BUILD_SELECTION_CRITERIA');
578 fnd_file.put_line(fnd_file.log,fnd_message.get);
579 fnd_file.put_line(fnd_file.log, SQLERRM);
580 igs_ge_msg_stack.add;
581 app_exception.raise_exception;
582 END build_selection_criteria;
583
584
585 PROCEDURE query_isir_records
586 IS
587 /*
588 || Created By : rgangara
589 || Created On : 28-JUL-2004
590 || Purpose : fetches records from the ISIR interface table for processing.
591 || Known limitations, enhancements or remarks :
592 || Change History :
593 || Who When What
594 || (reverse chronological order - newest change first)
595 */
596
597 -- define a REF Cursor for fetching data using the query created
598 TYPE isir_int_ref_cur IS REF CURSOR;
599 isir_int_cur isir_int_ref_cur;
600
601 i NUMBER := 1;
602
603 BEGIN
604
605 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
606 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_import_pkg.query_isir_records.debug', 'Beginning procedure query_isir_records');
607 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_import_pkg.query_isir_records.debug', 'Querying SQL Query : ' || g_where);
608 END IF;
609
610 i := 1;
611 -- Execute the query and get the records into temp table.
612 OPEN isir_int_cur FOR g_where;
613
614 -- create one row in table type variables
615 g_si_id_tab.extend;
616 g_batch_year_num_tab.extend;
617 g_original_ssn_txt_tab.extend;
618 g_orig_name_id_txt_tab.extend;
619
620
621 FETCH isir_int_cur INTO g_si_id_tab(i), g_batch_year_num_tab(i), g_original_ssn_txt_tab(i), g_orig_name_id_txt_tab(i);
622
623 -- BULK COLLECT option is not supported hence used loop. Once supported the loop can be removed and the above fetch modified
624 -- to populate directly to variables without subscripts.
625 WHILE isir_int_cur%FOUND LOOP
626 -- extend the tables
627 g_si_id_tab.extend;
628 g_batch_year_num_tab.extend;
629 g_original_ssn_txt_tab.extend;
630 g_orig_name_id_txt_tab.extend;
631 i := i + 1;
632 FETCH isir_int_cur INTO g_si_id_tab(i), g_batch_year_num_tab(i), g_original_ssn_txt_tab(i), g_orig_name_id_txt_tab(i);
633 END LOOP;
634 CLOSE isir_int_cur ;
635 i := i - 1; -- since the counter would be incremented by 1 extra iteration.
636
637 -- get the count of No. of ISIR records for processing.
638 g_total_recs_fetched := g_si_id_tab.COUNT;
639 log_debug_message('Populated temporary PL/SQL table. Records : ' || g_total_recs_fetched || '. Time : ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
640
641 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
642 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_import_pkg.query_isir_records.debug', 'No. of records fetched for processing : ' || g_total_recs_fetched);
643 END IF;
644
645 EXCEPTION
646 WHEN OTHERS THEN
647 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
648 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_import_pkg.query_isir_records.exception','The exception is : ' || SQLERRM );
649 END IF;
650 log_debug_message('EXCEPTION : ' || SQLERRM);
651
652 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
653 fnd_message.set_token('NAME','IGF_AP_ISIR_IMPORT_PKG.QUERY_ISIR_RECORDS');
654 fnd_file.put_line(fnd_file.log,fnd_message.get);
655 fnd_file.put_line(fnd_file.log, SQLERRM);
656 igs_ge_msg_stack.add;
657 app_exception.raise_exception;
658 END query_isir_records;
659
660
661 PROCEDURE spawn_processes(p_spawn_process NUMBER)
662 IS
663 /*
664 || Created By : rgangara
665 || Created On : 28-JUL-2004
666 || Purpose : For records distribution and launching spawned/parallel processes.
667 || Known limitations, enhancements or remarks :
668 || Change History :
669 || Who When What
670 || (reverse chronological order - newest change first)
671 */
672
673 l_recs_per_process NUMBER; -- No. of records to process per Spawned process
674 l_remaining_recs NUMBER; -- No. of records remaining to be processed
675 l_from_rec NUMBER; -- Holds the starting record position for the current sub request.
676 l_to_rec NUMBER; -- Holds the last record position for the current sub request.
677 l_current_sub_req_recs NUMBER; -- Holds the total No. of records to be processed for the current sub request.
678
679 BEGIN
680
681 log_debug_message(' Beginning process spawning....');
682 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
683 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_import_pkg.spawn_processes.debug', 'No. of processes to spawn : ' || p_spawn_process);
684 END IF;
685
686 -- get the No. of records per process
687 l_recs_per_process := CEIL(g_total_recs_fetched/p_spawn_process);
688
689 l_remaining_recs := g_total_recs_fetched;
690 l_from_rec := 1; -- initialize the rec pointer
691 l_to_rec := 0;
692
693 -- loop thru as many times as the No. of spawns needed
694 FOR i IN 1..p_spawn_process LOOP
695
696 l_current_sub_req_recs := 0; -- initialize for each sub request
697
698 -- identify the Total No. of recs to process for the current sub request
699 IF l_remaining_recs < l_recs_per_process THEN
700 l_current_sub_req_recs := l_remaining_recs; -- if remaining recs for processing is less, process only remaining recs
701 ELSE
702 l_current_sub_req_recs := l_recs_per_process; -- Process recs as derived by the recs per process
703 END IF;
704
705 -- derive the last record to be processed for the current sub request.
706 l_to_rec := l_from_rec + l_current_sub_req_recs - 1;
707
708 ----------------------------------------------------------------------------
709 -- At this point No. of Recs to process for the current sub request have been determined.
710 -- Now check if the next record after the last record to process (for this subrequest) is for the same person.
711 -- If so they have to be included in the current sub request since as per the policy
712 -- ISIRs for the same person should be processed by the same sub request.
713 ----------------------------------------------------------------------------
714
715 IF g_si_id_tab.EXISTS(l_to_rec + 1) THEN -- check whether it is the last record.
716
717 -- i.e. check whether the immeidate next record belongs to the same person.
718 -- If so till they are same, add them to the current sub request.
719 WHILE g_original_ssn_txt_tab(l_to_rec + 1) = g_original_ssn_txt_tab(l_to_rec) AND
720 g_orig_name_id_txt_tab(l_to_rec + 1) = g_orig_name_id_txt_tab(l_to_rec) AND
721 g_batch_year_num_tab(l_to_rec + 1) = g_batch_year_num_tab(l_to_rec)
722 LOOP
723
724 -- update tracking variables.
725 l_to_rec := l_to_rec + 1;
726 l_current_sub_req_recs := l_current_sub_req_recs + 1;
727
728 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
729 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_import_pkg.spawn_processes.debug', 'Adding SI_ID ' || g_si_id_tab(l_to_rec) || ' to Sub Request No. ' || i);
730 END IF;
731 log_debug_message(' Next record belongs to same person!!!!. Including in the current sub reqeust itself. SI_ID ' || g_si_id_tab(l_to_rec));
732 END LOOP;
733 END IF; -- g_proc_recs_tab
734
735 log_debug_message('TOTAL RECORDS for Sub Request No. ' || i || ' is ' || l_current_sub_req_recs);
736 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
737 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_import_pkg.spawn_processes.debug', 'Total records for Sub Request No. ' || i || ' is ' || l_current_sub_req_recs);
738 END IF;
739
740 ----------------------------------------------------------------------------
741 -- At this point Final Recs to be processed for the current sub request have been included.
742 ----------------------------------------------------------------------------
743 -- Now update the interface table for the identified recs with the sub request no.
744 FORALL k IN l_from_rec..l_to_rec -- No. of recs to process from the current rec pointer.
745 UPDATE igf_ap_isir_ints
746 SET parent_req_id = g_parent_req_number,
747 sub_req_num = i
748 WHERE si_id = g_si_id_tab(k);
749
750
751 COMMIT; -- commit the parent request id and sub request number updates to Interface table.
752
753 -- increment counters to process from the next rec for the next sub request.
754 l_remaining_recs := l_remaining_recs - l_current_sub_req_recs; -- No. of recs still to be processed
755 l_from_rec := l_to_rec + 1; -- increment the from rec pointer to point to the next record
756
757 -- Launch Sub request
758 launch_sub_request(p_sub_req_number => i, p_sub_req_rec_cnt => l_current_sub_req_recs);
759
760
761 IF l_remaining_recs <= 0 THEN
762 -- No. more recs exists for processing. Hence exit the loop. No need to fire remaining spawn processes
763 EXIT;
764 END IF;
765
766 END LOOP; -- i
767
768 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
769 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_import_pkg.spawn_processes.debug', 'Completed updating Spawning Processes details.... ');
770 END IF;
771 log_debug_message(' Spawning Process completed successfully. Exitting.....at.. ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
772
773 EXCEPTION
774 WHEN OTHERS THEN
775 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
776 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_import_pkg.spawn_processes.exception','The exception is : ' || SQLERRM );
777 END IF;
778 log_debug_message(' EXCEPTION in spawn_processes : ' || SQLERRM);
779
780 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
781 fnd_message.set_token('NAME','IGF_AP_ISIR_IMPORT_PKG.SPAWN_PROCESSES');
782 fnd_file.put_line(fnd_file.log,fnd_message.get);
783 igs_ge_msg_stack.add;
784 app_exception.raise_exception;
785 END spawn_processes;
786
787
788 ------------------------------------------------------------
789 -- End of Local new Procedures created for FA138 build - rgangara.
790 ------------------------------------------------------------
791
792 BEGIN
793
794 igf_aw_gen.set_org_id(p_org_id);
795
796 log_debug_message(' Beginning Main process at ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
797
798 -- print input parameters
799 fnd_file.put_line(fnd_file.log, '-----------------------------------------------------------------------------------------');
800 fnd_message.set_name('IGF', 'IGF_AP_AWD_YR');
801 fnd_message.set_token('AWD_YEAR', p_award_year);
802 fnd_file.put_line(fnd_file.log, fnd_message.get);
803
804 fnd_message.set_name('IGF', 'IGF_AP_CREATE_PRSN_NO_MATCH');
805 fnd_message.set_token('CREATE_PRSN', p_force_add);
806 fnd_file.put_line(fnd_file.log, fnd_message.get);
807
808 fnd_message.set_name('IGF', 'IGF_AP_CREATE_ADM_INQ');
809 fnd_message.set_token('CREATE_INQ', p_create_inquiry);
810 fnd_file.put_line(fnd_file.log, fnd_message.get);
811
812 fnd_message.set_name('IGF', 'IGF_AP_ADM_INQ_MTHD');
813 fnd_message.set_token('INQ_METHOD', p_adm_source_type);
814 fnd_file.put_line(fnd_file.log, fnd_message.get);
815
816 fnd_message.set_name('IGF', 'IGF_AP_MATCH_CODE');
817 fnd_message.set_token('MATCH_CODE', p_match_code);
818 fnd_file.put_line(fnd_file.log, fnd_message.get);
819
820 fnd_message.set_name('IGF', 'IGF_AP_REC_TYPE');
821 fnd_message.set_token('REC_TYPE', p_rec_type);
822 fnd_file.put_line(fnd_file.log, fnd_message.get);
823
824 fnd_message.set_name('IGF', 'IGF_AP_REC_STAT');
825 fnd_message.set_token('REC_STATUS', p_rec_status);
826 fnd_file.put_line(fnd_file.log, fnd_message.get);
827
828 fnd_message.set_name('IGF', 'IGF_AP_MSG_CLASS');
829 fnd_message.set_token('MSG_CLASS', p_message_class);
830 fnd_file.put_line(fnd_file.log, fnd_message.get);
831
832 fnd_message.set_name('IGF', 'IGF_AP_SCHOOL_CD');
833 fnd_message.set_token('SCHOOL_CD', p_school_code);
834 fnd_file.put_line(fnd_file.log, fnd_message.get);
835
836 fnd_message.set_name('IGF', 'IGF_AP_SPAWN_REQ');
837 fnd_message.set_token('SPAWN_CNT', p_spawn_process);
838 fnd_file.put_line(fnd_file.log, fnd_message.get);
839
840 fnd_message.set_name('IGF', 'IGF_AP_DEL_INT_RECORD');
841 fnd_message.set_token('DEL_FLAG', p_del_int);
842 fnd_file.put_line(fnd_file.log, fnd_message.get);
843
844 fnd_message.set_name('IGF', 'IGF_AP_UPD_ANT_DATA');
845 fnd_message.set_token('UPD_ANT', p_upd_ant_val);
846 fnd_file.put_line(fnd_file.log, fnd_message.get);
847
848 fnd_file.put_line(fnd_file.log, '-----------------------------------------------------------------------------------------');
849
850 errbuf := NULL;
851 retcode := 0;
852 l_cal_type := LTRIM(RTRIM(SUBSTR(p_award_year,1,10)));
853 l_seq_number := TO_NUMBER(SUBSTR(p_award_year,11));
854
855 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
856 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_import_pkg.main_import_process.debug','Beginning Main process. Before gathering Statistics: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
857 END IF;
858
859 -- gather Statistics RAMMOHAN commented for testing
860 log_debug_message(' Starting to gather statistics. ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
861 fnd_stats.gather_table_stats(ownname => 'IGF', tabname => 'IGF_AP_ISIR_INTS_ALL' , cascade => TRUE);
862 fnd_stats.gather_table_stats(ownname => 'IGF', tabname => 'IGF_AP_ISIR_MATCHED_ALL' , cascade => TRUE);
863 log_debug_message(' End of Statistics gathering. ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
864
865 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
866 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_import_pkg.main_import_process.debug','After gathering Statistics: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
867 END IF;
868
869
870 -- Copying the parameter values to the gobal variable.
871 g_where := NULL;
872 g_force_add := NVL(p_force_add, 'N');
873 g_create_inquiry := NVL(p_create_inquiry,'N');
874 g_adm_source_type := p_adm_source_type;
875 g_match_code := p_match_code;
876 g_rec_status := p_rec_status ;
877 g_rec_type := p_rec_type ;
878 g_message_class := p_message_class ;
879 g_school_code := p_school_code ;
880 g_del_int := NVL(p_del_int, 'N');
881 g_parent_req_number := fnd_global.conc_request_id; -- get the current request id as this would be the parent request id for the sub requests.
882
883 OPEN c_batch(l_cal_type,l_seq_number) ;
884 FETCH c_batch INTO l_batch;
885 CLOSE c_batch;
886
887 IF l_batch.batch_year IS NULL THEN
888 fnd_message.set_name('IGF','IGF_AP_BATCH_YEAR_NOT_FOUND');
889 errbuf := fnd_message.get;
890 fnd_file.put_line(fnd_file.log, errbuf);
891 RETCODE := 2;
892 RETURN;
893 END IF;
894 g_batch_year := l_batch.batch_year;
895
896
897 -- Initialize the variable with Basic SQL statement to which the dynamic where clause can be appended later.
898 l_sql := 'SELECT si_id, batch_year_num, original_ssn_txt, orig_name_id_txt FROM igf_ap_isir_ints WHERE batch_year_num = ' || g_batch_year ;
899
900 IF LTRIM(RTRIM(p_create_inquiry)) = 'Y' AND LTRIM(RTRIM(p_adm_source_type)) IS NULL THEN
901 fnd_message.set_name('IGF', 'IGF_AP_SOURCE_TYPE_REQ');
902 errbuf := fnd_message.get;
903 fnd_file.put_line(fnd_file.log,errbuf);
904 retcode := 2;
905 RETURN;
906 END IF;
907
908 -- call procedure to validate parameters
909 validate_parameters(retcode => retcode, errbuf => errbuf);
910
911 IF retcode <> 0 THEN -- i.e. some parameter validation failed.
912 RETURN;
913 END IF;
914
915 -- call procedure to build ISIR record selection criteria based on given parameters
916 build_selection_criteria;
917
918 -- build the complete SQL statement
919 g_where := l_sql || g_where || ' ORDER BY original_ssn_txt, orig_name_id_txt, batch_year_num ' ;
920
921 log_debug_message(' FINAL QUERY : ' || g_where );
922 -- call procedure to get interface records for processing
923 query_isir_records;
924
925 -- Check whether any records found for processing for the query
926 IF g_total_recs_fetched = 0 THEN
927 fnd_message.set_name ('IGF','IGF_AP_MATCHING_REC_NT_FND');
928 errbuf := fnd_message.get;
929 fnd_file.put_line(fnd_file.log, errbuf);
930 retcode := 1;
931 RETURN;
932 END IF;
933
934 -- spawn processes only if the No. of recs to process is > No. of processes
935 IF NVL(p_spawn_process,1) > 1 AND g_total_recs_fetched > NVL(p_spawn_process,1) THEN
936 -- records distribution and launching spawned/parallel processes
937 spawn_processes(NVL(p_spawn_process,1));
938
939 ELSE
940 log_debug_message('Processing as a Single Request.');
941 -- Single request hence update the identified recs with the sub request no.
942 FORALL k IN 1..g_total_recs_fetched
943 UPDATE igf_ap_isir_ints
944 SET parent_req_id = g_parent_req_number,
945 sub_req_num = 1
946 WHERE si_id = g_si_id_tab(k);
947
948
949 COMMIT; -- commit the parent request id and sub request number updates to Interface table.
950
951 log_debug_message('Launching Sub Request as a Single Request.');
952 -- Launch Sub request
953 launch_sub_request(p_sub_req_number => 1, p_sub_req_rec_cnt => g_total_recs_fetched);
954 END IF;
955
956 log_debug_message('Successfully completed the Request at : ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS')) ;
957 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
958 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_import_pkg.main_import_process.debug','Successfully Completed the process at: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
959 END IF;
960
961 EXCEPTION
962 WHEN others THEN
963 ROLLBACK;
964 retcode := 2;
965 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
966 fnd_message.set_token('NAME','IGF_AP_ISIR_IMPORT_PKG.MAIN_IMPORT_PROCESS');
967 fnd_file.put_line(fnd_file.log,SQLERRM);
968 errbuf := fnd_message.get;
969 igs_ge_msg_stack.conc_exception_hndl;
970 END main_import_process;
971
972
973 PROCEDURE update_matched_ISIR (p_ISIR_id igf_ap_ISIR_matched_all.ISIR_id%TYPE,
974 p_system_record_type igf_ap_ISIR_matched_all.system_record_type%TYPE,
975 p_payment_ISIR igf_ap_ISIR_matched_all.payment_ISIR%TYPE,
976 p_active_ISIR igf_ap_ISIR_matched_all.active_ISIR%TYPE)
977 IS
978 /*
979 || Created By : brajendr
980 || Created On : 08-NOV-2000
981 || Purpose : Process which inserts comment codes of the student.
982 || Known limitations, enhancements or remarks :
983 || Change History :
984 || Who When What
985 || smvk 11-Feb-2003 Bug # 2758812. Added the procedure call igf_gr_gen.update_current_ssn.
986 || masehgal 15-Feb-2002 # 2216956 FACR007
987 || Added Verif_track_flag
988 || (reverse chronological order - newest change first)
989 */
990
991 CURSOR cur_upd_ISIR IS
992 SELECT ism.*
993 FROM igf_ap_ISIR_matched ism
994 WHERE ism.ISIR_id = p_ISIR_id;
995
996
997 cur_ISIR_rec cur_upd_ISIR%ROWTYPE;
998 p_c_message VARCHAR2(30);
999 l_msg_class igf_ap_isir_matched.message_class_txt%TYPE;
1000
1001 BEGIN
1002 IF p_system_record_type = 'ORIGINAL' THEN
1003 FOR cur_ISIR_rec IN cur_upd_ISIR LOOP
1004
1005 -- get message class from data file name
1006 l_msg_class := igf_ap_matching_process_pkg.get_msg_class_from_filename(cur_ISIR_rec.data_file_name_txt);
1007
1008 igf_ap_ISIR_matched_pkg.update_row (x_Mode => 'R',
1009 x_rowid => cur_ISIR_rec.row_id,
1010 x_ISIR_id => cur_ISIR_rec.ISIR_id,
1011 x_base_id => cur_ISIR_rec.base_id,
1012 x_batch_year => cur_ISIR_rec.batch_year,
1013 x_transaction_num => cur_ISIR_rec.transaction_num,
1014 x_current_ssn => cur_ISIR_rec.current_ssn,
1015 x_ssn_name_change => cur_ISIR_rec.ssn_name_change,
1016 x_original_ssn => cur_ISIR_rec.original_ssn,
1017 x_orig_name_id => cur_ISIR_rec.orig_name_id,
1018 x_last_name => cur_ISIR_rec.last_name,
1019 x_first_name => cur_ISIR_rec.first_name,
1020 x_middle_initial => cur_ISIR_rec.middle_initial,
1021 x_perm_mail_add => cur_ISIR_rec.perm_mail_add,
1022 x_perm_city => cur_ISIR_rec.perm_city,
1023 x_perm_state => cur_ISIR_rec.perm_state,
1024 x_perm_zip_code => cur_ISIR_rec.perm_zip_code,
1025 x_date_of_birth => cur_ISIR_rec.date_of_birth,
1026 x_phone_number => cur_ISIR_rec.phone_number,
1027 x_driver_license_number => cur_ISIR_rec.driver_license_number,
1028 x_driver_license_state => cur_ISIR_rec.driver_license_state,
1029 x_citizenship_status => cur_ISIR_rec.citizenship_status,
1030 x_alien_reg_number => cur_ISIR_rec.alien_reg_number,
1031 x_s_marital_status => cur_ISIR_rec.s_marital_status,
1032 x_s_marital_status_date => cur_ISIR_rec.s_marital_status_date,
1033 x_summ_enrl_status => cur_ISIR_rec.summ_enrl_status,
1034 x_fall_enrl_status => cur_ISIR_rec.fall_enrl_status,
1035 x_winter_enrl_status => cur_ISIR_rec.winter_enrl_status,
1036 x_spring_enrl_status => cur_ISIR_rec.spring_enrl_status,
1037 x_summ2_enrl_status => cur_ISIR_rec.summ2_enrl_status,
1038 x_fathers_highest_edu_level => cur_ISIR_rec.fathers_highest_edu_level,
1039 x_mothers_highest_edu_level => cur_ISIR_rec.mothers_highest_edu_level,
1040 x_s_state_legal_residence => cur_ISIR_rec.s_state_legal_residence,
1041 x_legal_residence_before_date => cur_ISIR_rec.legal_residence_before_date,
1042 x_s_legal_resd_date => cur_ISIR_rec.s_legal_resd_date,
1043 x_ss_r_u_male => cur_ISIR_rec.ss_r_u_male,
1044 x_selective_service_reg => cur_ISIR_rec.selective_service_reg,
1045 x_degree_certification => cur_ISIR_rec.degree_certification,
1046 x_grade_level_in_college => cur_ISIR_rec.grade_level_in_college,
1047 x_high_school_diploma_ged => cur_ISIR_rec.high_school_diploma_ged,
1048 x_first_bachelor_deg_by_date => cur_ISIR_rec.first_bachelor_deg_by_date,
1049 x_interest_in_loan => cur_ISIR_rec.interest_in_loan,
1050 x_interest_in_stud_employment => cur_ISIR_rec.interest_in_stud_employment,
1051 x_drug_offence_conviction => cur_ISIR_rec.drug_offence_conviction,
1052 x_s_tax_return_status => cur_ISIR_rec.s_tax_return_status,
1053 x_s_type_tax_return => cur_ISIR_rec.s_type_tax_return,
1054 x_s_elig_1040ez => cur_ISIR_rec.s_elig_1040ez,
1055 x_s_adjusted_gross_income => cur_ISIR_rec.s_adjusted_gross_income,
1056 x_s_fed_taxes_paid => cur_ISIR_rec.s_fed_taxes_paid,
1057 x_s_exemptions => cur_ISIR_rec.s_exemptions,
1058 x_s_income_from_work => cur_ISIR_rec.s_income_from_work,
1059 x_spouse_income_from_work => cur_ISIR_rec.spouse_income_from_work,
1060 x_s_toa_amt_from_wsa => cur_ISIR_rec.s_toa_amt_from_wsa,
1061 x_s_toa_amt_from_wsb => cur_ISIR_rec.s_toa_amt_from_wsb,
1062 x_s_toa_amt_from_wsc => cur_ISIR_rec.s_toa_amt_from_wsc,
1063 x_s_investment_networth => cur_ISIR_rec.s_investment_networth,
1064 x_s_busi_farm_networth => cur_ISIR_rec.s_busi_farm_networth,
1065 x_s_cash_savings => cur_ISIR_rec.s_cash_savings,
1066 x_va_months => cur_ISIR_rec.va_months,
1067 x_va_amount => cur_ISIR_rec.va_amount,
1068 x_stud_dob_before_date => cur_ISIR_rec.stud_dob_before_date,
1069 x_deg_beyond_bachelor => cur_ISIR_rec.deg_beyond_bachelor,
1070 x_s_married => cur_ISIR_rec.s_married,
1071 x_s_have_children => cur_ISIR_rec.s_have_children,
1072 x_legal_dependents => cur_ISIR_rec.legal_dependents,
1073 x_orphan_ward_of_court => cur_ISIR_rec.orphan_ward_of_court,
1074 x_s_veteran => cur_ISIR_rec.s_veteran,
1075 x_p_marital_status => cur_ISIR_rec.p_marital_status,
1076 x_father_ssn => cur_ISIR_rec.father_ssn,
1077 x_f_last_name => cur_ISIR_rec.f_last_name,
1078 x_mother_ssn => cur_ISIR_rec.mother_ssn,
1079 x_m_last_name => cur_ISIR_rec.m_last_name,
1080 x_p_num_family_member => cur_ISIR_rec.p_num_family_member,
1081 x_p_num_in_college => cur_ISIR_rec.p_num_in_college,
1082 x_p_state_legal_residence => cur_ISIR_rec.p_state_legal_residence,
1083 x_p_state_legal_res_before_dt => cur_ISIR_rec.p_state_legal_res_before_dt,
1084 x_p_legal_res_date => cur_ISIR_rec.p_legal_res_date,
1085 x_age_older_parent => cur_ISIR_rec.age_older_parent,
1086 x_p_tax_return_status => cur_ISIR_rec.p_tax_return_status,
1087 x_p_type_tax_return => cur_ISIR_rec.p_type_tax_return,
1088 x_p_elig_1040aez => cur_ISIR_rec.p_elig_1040aez,
1089 x_p_adjusted_gross_income => cur_ISIR_rec.p_adjusted_gross_income,
1090 x_p_taxes_paid => cur_ISIR_rec.p_taxes_paid,
1091 x_p_exemptions => cur_ISIR_rec.p_exemptions,
1092 x_f_income_work => cur_ISIR_rec.f_income_work,
1093 x_m_income_work => cur_ISIR_rec.m_income_work,
1094 x_p_income_wsa => cur_ISIR_rec.p_income_wsa,
1095 x_p_income_wsb => cur_ISIR_rec.p_income_wsb,
1096 x_p_income_wsc => cur_ISIR_rec.p_income_wsc,
1097 x_p_investment_networth => cur_ISIR_rec.p_investment_networth,
1098 x_p_business_networth => cur_ISIR_rec.p_business_networth,
1099 x_p_cash_saving => cur_ISIR_rec.p_cash_saving,
1100 x_s_num_family_members => cur_ISIR_rec.s_num_family_members,
1101 x_s_num_in_college => cur_ISIR_rec.s_num_in_college,
1102 x_first_college => cur_ISIR_rec.first_college,
1103 x_first_house_plan => cur_ISIR_rec.first_house_plan,
1104 x_second_college => cur_ISIR_rec.second_college,
1105 x_second_house_plan => cur_ISIR_rec.second_house_plan,
1106 x_third_college => cur_ISIR_rec.third_college,
1107 x_third_house_plan => cur_ISIR_rec.third_house_plan,
1108 x_fourth_college => cur_ISIR_rec.fourth_college,
1109 x_fourth_house_plan => cur_ISIR_rec.fourth_house_plan,
1110 x_fifth_college => cur_ISIR_rec.fifth_college,
1111 x_fifth_house_plan => cur_ISIR_rec.fifth_house_plan,
1112 x_sixth_college => cur_ISIR_rec.sixth_college,
1113 x_sixth_house_plan => cur_ISIR_rec.sixth_house_plan,
1114 x_date_app_completed => cur_ISIR_rec.date_app_completed,
1115 x_signed_by => cur_ISIR_rec.signed_by,
1116 x_preparer_ssn => cur_ISIR_rec.preparer_ssn,
1117 x_preparer_emp_id_number => cur_ISIR_rec.preparer_emp_id_number,
1118 x_preparer_sign => cur_ISIR_rec.preparer_sign,
1119 x_transaction_receipt_date => cur_ISIR_rec.transaction_receipt_date,
1120 x_dependency_override_ind => cur_ISIR_rec.dependency_override_ind,
1121 x_faa_fedral_schl_code => cur_ISIR_rec.faa_fedral_schl_code,
1122 x_faa_adjustment => cur_ISIR_rec.faa_adjustment,
1123 x_input_record_type => cur_ISIR_rec.input_record_type,
1124 x_serial_number => cur_ISIR_rec.serial_number,
1125 x_batch_number => cur_ISIR_rec.batch_number,
1126 x_early_analysis_flag => cur_ISIR_rec.early_analysis_flag,
1127 x_app_entry_source_code => cur_ISIR_rec.app_entry_source_code,
1128 x_eti_destination_code => cur_ISIR_rec.eti_destination_code,
1129 x_reject_override_b => cur_ISIR_rec.reject_override_b,
1130 x_reject_override_n => cur_ISIR_rec.reject_override_n,
1131 x_reject_override_w => cur_ISIR_rec.reject_override_w,
1132 x_assum_override_1 => cur_ISIR_rec.assum_override_1,
1133 x_assum_override_2 => cur_ISIR_rec.assum_override_2,
1134 x_assum_override_3 => cur_ISIR_rec.assum_override_3,
1135 x_assum_override_4 => cur_ISIR_rec.assum_override_4,
1136 x_assum_override_5 => cur_ISIR_rec.assum_override_5,
1137 x_assum_override_6 => cur_ISIR_rec.assum_override_6,
1138 x_dependency_status => cur_ISIR_rec.dependency_status,
1139 x_s_email_address => cur_ISIR_rec.s_email_address,
1140 x_nslds_reason_code => cur_ISIR_rec.nslds_reason_code,
1141 x_app_receipt_date => cur_ISIR_rec.app_receipt_date,
1142 x_processed_rec_type => cur_ISIR_rec.processed_rec_type,
1143 x_hist_correction_for_tran_id => cur_ISIR_rec.hist_correction_for_tran_id,
1144 x_system_generated_indicator => cur_ISIR_rec.system_generated_indicator,
1145 x_dup_request_indicator => cur_ISIR_rec.dup_request_indicator,
1146 x_source_of_correction => cur_ISIR_rec.source_of_correction,
1147 x_p_cal_tax_status => cur_ISIR_rec.p_cal_tax_status,
1148 x_s_cal_tax_status => cur_ISIR_rec.s_cal_tax_status,
1149 x_graduate_flag => cur_ISIR_rec.graduate_flag,
1150 x_auto_zero_efc => cur_ISIR_rec.auto_zero_efc,
1151 x_efc_change_flag => cur_ISIR_rec.efc_change_flag,
1152 x_sarc_flag => cur_ISIR_rec.sarc_flag,
1153 x_simplified_need_test => cur_ISIR_rec.simplified_need_test,
1154 x_reject_reason_codes => cur_ISIR_rec.reject_reason_codes,
1155 x_select_service_match_flag => cur_ISIR_rec.select_service_match_flag,
1156 x_select_service_reg_flag => cur_ISIR_rec.select_service_reg_flag,
1157 x_ins_match_flag => cur_ISIR_rec.ins_match_flag,
1158 x_ins_verification_number => NULL,
1159 x_sec_ins_match_flag => cur_ISIR_rec.sec_ins_match_flag,
1160 x_sec_ins_ver_number => cur_ISIR_rec.sec_ins_ver_number,
1161 x_ssn_match_flag => cur_ISIR_rec.ssn_match_flag,
1162 x_ssa_citizenship_flag => cur_ISIR_rec.ssa_citizenship_flag,
1163 x_ssn_date_of_death => cur_ISIR_rec.ssn_date_of_death,
1164 x_nslds_match_flag => cur_ISIR_rec.nslds_match_flag,
1165 x_va_match_flag => cur_ISIR_rec.va_match_flag,
1166 x_prisoner_match => cur_ISIR_rec.prisoner_match,
1167 x_verification_flag => cur_ISIR_rec.verification_flag,
1168 x_subsequent_app_flag => cur_ISIR_rec.subsequent_app_flag,
1169 x_app_source_site_code => cur_ISIR_rec.app_source_site_code,
1170 x_tran_source_site_code => cur_ISIR_rec.tran_source_site_code,
1171 x_drn => cur_ISIR_rec.drn,
1172 x_tran_process_date => cur_ISIR_rec.tran_process_date,
1173 x_computer_batch_number => cur_ISIR_rec.computer_batch_number,
1174 x_correction_flags => cur_ISIR_rec.correction_flags,
1175 x_highlight_flags => cur_ISIR_rec.highlight_flags,
1176 x_paid_efc => NULL,
1177 x_primary_efc => cur_ISIR_rec.primary_efc,
1178 x_secondary_efc => cur_ISIR_rec.secondary_efc,
1179 x_fed_pell_grant_efc_type => NULL,
1180 x_primary_efc_type => cur_ISIR_rec.primary_efc_type,
1181 x_sec_efc_type => cur_ISIR_rec.sec_efc_type,
1182 x_primary_alternate_month_1 => cur_ISIR_rec.primary_alternate_month_1,
1183 x_primary_alternate_month_2 => cur_ISIR_rec.primary_alternate_month_2,
1184 x_primary_alternate_month_3 => cur_ISIR_rec.primary_alternate_month_3,
1185 x_primary_alternate_month_4 => cur_ISIR_rec.primary_alternate_month_4,
1186 x_primary_alternate_month_5 => cur_ISIR_rec.primary_alternate_month_5,
1187 x_primary_alternate_month_6 => cur_ISIR_rec.primary_alternate_month_6,
1188 x_primary_alternate_month_7 => cur_ISIR_rec.primary_alternate_month_7,
1189 x_primary_alternate_month_8 => cur_ISIR_rec.primary_alternate_month_8,
1190 x_primary_alternate_month_10 => cur_ISIR_rec.primary_alternate_month_10,
1191 x_primary_alternate_month_11 => cur_ISIR_rec.primary_alternate_month_11,
1192 x_primary_alternate_month_12 => cur_ISIR_rec.primary_alternate_month_12,
1193 x_sec_alternate_month_1 => cur_ISIR_rec.sec_alternate_month_1,
1194 x_sec_alternate_month_2 => cur_ISIR_rec.sec_alternate_month_2,
1195 x_sec_alternate_month_3 => cur_ISIR_rec.sec_alternate_month_3,
1196 x_sec_alternate_month_4 => cur_ISIR_rec.sec_alternate_month_4,
1197 x_sec_alternate_month_5 => cur_ISIR_rec.sec_alternate_month_5,
1198 x_sec_alternate_month_6 => cur_ISIR_rec.sec_alternate_month_6,
1199 x_sec_alternate_month_7 => cur_ISIR_rec.sec_alternate_month_7,
1200 x_sec_alternate_month_8 => cur_ISIR_rec.sec_alternate_month_8,
1201 x_sec_alternate_month_10 => cur_ISIR_rec.sec_alternate_month_10,
1202 x_sec_alternate_month_11 => cur_ISIR_rec.sec_alternate_month_11,
1203 x_sec_alternate_month_12 => cur_ISIR_rec.sec_alternate_month_12,
1204 x_total_income => cur_ISIR_rec.total_income,
1205 x_allow_total_income => cur_ISIR_rec.allow_total_income,
1206 x_state_tax_allow => cur_ISIR_rec.state_tax_allow,
1207 x_employment_allow => cur_ISIR_rec.employment_allow,
1208 x_income_protection_allow => cur_ISIR_rec.income_protection_allow,
1209 x_available_income => cur_ISIR_rec.available_income,
1210 x_contribution_from_ai => cur_ISIR_rec.contribution_from_ai,
1211 x_discretionary_networth => cur_ISIR_rec.discretionary_networth,
1212 x_efc_networth => cur_ISIR_rec.efc_networth,
1213 x_asset_protect_allow => cur_ISIR_rec.asset_protect_allow,
1214 x_parents_cont_from_assets => cur_ISIR_rec.parents_cont_from_assets,
1215 x_adjusted_available_income => cur_ISIR_rec.adjusted_available_income,
1216 x_total_student_contribution => cur_ISIR_rec.total_student_contribution,
1217 x_total_parent_contribution => cur_ISIR_rec.total_parent_contribution,
1218 x_parents_contribution => cur_ISIR_rec.parents_contribution,
1219 x_student_total_income => cur_ISIR_rec.student_total_income,
1220 x_sati => cur_ISIR_rec.sati,
1221 x_sic => cur_ISIR_rec.sic,
1222 x_sdnw => cur_ISIR_rec.sdnw,
1223 x_sca => cur_ISIR_rec.sca,
1224 x_fti => cur_ISIR_rec.fti,
1225 x_secti => cur_ISIR_rec.secti,
1226 x_secati => cur_ISIR_rec.secati,
1227 x_secstx => cur_ISIR_rec.secstx,
1228 x_secea => cur_ISIR_rec.secea,
1229 x_secipa => cur_ISIR_rec.secipa,
1230 x_secai => cur_ISIR_rec.secai,
1231 x_seccai => cur_ISIR_rec.seccai,
1232 x_secdnw => cur_ISIR_rec.secdnw,
1233 x_secnw => cur_ISIR_rec.secnw,
1234 x_secapa => cur_ISIR_rec.secapa,
1235 x_secpca => cur_ISIR_rec.secpca,
1236 x_secaai => cur_ISIR_rec.secaai,
1237 x_sectsc => cur_ISIR_rec.sectsc,
1238 x_sectpc => cur_ISIR_rec.sectpc,
1239 x_secpc => cur_ISIR_rec.secpc,
1240 x_secsti => cur_ISIR_rec.secsti,
1241 x_secsic => cur_ISIR_rec.secsic,
1242 x_secsati => cur_ISIR_rec.secsati,
1243 x_secsdnw => cur_ISIR_rec.secsdnw,
1244 x_secsca => cur_ISIR_rec.secsca,
1245 x_secfti => cur_ISIR_rec.secfti,
1246 x_a_citizenship => cur_ISIR_rec.a_citizenship,
1247 x_a_student_marital_status => cur_ISIR_rec.a_student_marital_status,
1248 x_a_student_agi => cur_ISIR_rec.a_student_agi,
1249 x_a_s_us_tax_paid => cur_ISIR_rec.a_s_us_tax_paid,
1250 x_a_s_income_work => cur_ISIR_rec.a_s_income_work,
1251 x_a_spouse_income_work => cur_ISIR_rec.a_spouse_income_work,
1252 x_a_s_total_wsc => cur_ISIR_rec.a_s_total_wsc,
1253 x_a_date_of_birth => cur_ISIR_rec.a_date_of_birth,
1254 x_a_student_married => cur_ISIR_rec.a_student_married,
1255 x_a_have_children => cur_ISIR_rec.a_have_children,
1256 x_a_s_have_dependents => cur_ISIR_rec.a_s_have_dependents,
1257 x_a_va_status => cur_ISIR_rec.a_va_status,
1258 x_a_s_num_in_family => cur_ISIR_rec.a_s_num_in_family,
1259 x_a_s_num_in_college => cur_ISIR_rec.a_s_num_in_college,
1260 x_a_p_marital_status => cur_ISIR_rec.a_p_marital_status,
1261 x_a_father_ssn => cur_ISIR_rec.a_father_ssn,
1262 x_a_mother_ssn => cur_ISIR_rec.a_mother_ssn,
1263 x_a_parents_num_family => cur_ISIR_rec.a_parents_num_family,
1264 x_a_parents_num_college => cur_ISIR_rec.a_parents_num_college,
1265 x_a_parents_agi => cur_ISIR_rec.a_parents_agi,
1266 x_a_p_us_tax_paid => cur_ISIR_rec.a_p_us_tax_paid,
1267 x_a_f_work_income => cur_ISIR_rec.a_f_work_income,
1268 x_a_m_work_income => cur_ISIR_rec.a_m_work_income,
1269 x_a_p_total_wsc => cur_ISIR_rec.a_p_total_wsc,
1270 x_comment_codes => cur_ISIR_rec.comment_codes,
1271 x_sar_ack_comm_code => cur_ISIR_rec.sar_ack_comm_code,
1272 x_pell_grant_elig_flag => cur_ISIR_rec.pell_grant_elig_flag,
1273 x_reprocess_reason_code => cur_ISIR_rec.reprocess_reason_code,
1274 x_duplicate_date => cur_ISIR_rec.duplicate_date,
1275 x_ISIR_transaction_type => cur_ISIR_rec.ISIR_transaction_type,
1276 x_fedral_schl_code_indicator => cur_ISIR_rec.fedral_schl_code_indicator,
1277 x_multi_school_code_flags => cur_ISIR_rec.multi_school_code_flags,
1278 x_dup_ssn_indicator => cur_ISIR_rec.dup_ssn_indicator,
1279 x_payment_ISIR => p_payment_ISIR,
1280 x_receipt_status => 'PROCESSED',
1281 x_system_record_type => p_system_record_type,
1282 x_ISIR_receipt_completed => 'Y' ,
1283 x_verif_track_flag => cur_ISIR_rec.verif_track_flag,
1284 x_active_ISIR => NVL(p_active_ISIR,cur_ISIR_rec.active_ISIR),
1285 x_fafsa_data_verify_flags => cur_ISIR_rec.fafsa_data_verify_flags,
1286 x_reject_override_a => cur_ISIR_rec.reject_override_a,
1287 x_reject_override_c => cur_ISIR_rec.reject_override_c,
1288 x_parent_marital_status_date => cur_ISIR_rec.parent_marital_status_date,
1289 x_legacy_record_flag => NULL,
1290 x_father_first_name_initial => cur_ISIR_rec.father_first_name_initial_txt,
1291 x_father_step_father_birth_dt => cur_ISIR_rec.father_step_father_birth_date,
1292 x_mother_first_name_initial => cur_ISIR_rec.mother_first_name_initial_txt,
1293 x_mother_step_mother_birth_dt => cur_ISIR_rec.mother_step_mother_birth_date,
1294 x_parents_email_address_txt => cur_ISIR_rec.parents_email_address_txt,
1295 x_address_change_type => cur_ISIR_rec.address_change_type,
1296 x_cps_pushed_isir_flag => cur_ISIR_rec.cps_pushed_isir_flag,
1297 x_electronic_transaction_type => cur_ISIR_rec.electronic_transaction_type,
1298 x_sar_c_change_type => cur_ISIR_rec.sar_c_change_type,
1299 x_father_ssn_match_type => cur_ISIR_rec.father_ssn_match_type,
1300 x_mother_ssn_match_type => cur_ISIR_rec.mother_ssn_match_type,
1301 x_reject_override_g_flag => cur_ISIR_rec.reject_override_g_flag,
1302 x_dhs_verification_num_txt => cur_ISIR_rec.dhs_verification_num_txt,
1303 x_data_file_name_txt => cur_ISIR_rec.data_file_name_txt,
1304 x_message_class_txt => l_msg_class,
1305 x_reject_override_3_flag => cur_ISIR_rec.reject_override_3_flag,
1306 x_reject_override_12_flag => cur_ISIR_rec.reject_override_12_flag,
1307 x_reject_override_j_flag => cur_ISIR_rec.reject_override_j_flag,
1308 x_reject_override_k_flag => cur_ISIR_rec.reject_override_k_flag,
1309 x_rejected_status_change_flag => cur_ISIR_rec.rejected_status_change_flag,
1310 x_verification_selection_flag => cur_ISIR_rec.verification_selection_flag
1311 );
1312
1313 igf_gr_gen.update_current_ssn(cur_ISIR_rec.base_id,cur_ISIR_rec.current_ssn,p_c_message);
1314
1315 IF p_c_message = 'IGF_GR_UPDT_SSN_FAIL' THEN
1316 fnd_message.set_name ('IGF',p_c_message);
1317 fnd_file.put_line(fnd_file.log,fnd_message.get);
1318 END IF;
1319 END LOOP;
1320 END IF;
1321
1322 EXCEPTION
1323 WHEN others THEN
1324 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1325 fnd_message.set_token('NAME','IGF_AP_ISIR_IMPORT_PKG.UPDATE_MATCHED_ISIR');
1326 fnd_file.put_line(fnd_file.log,SQLERRM);
1327 igs_ge_msg_stack.add;
1328 app_exception.raise_exception;
1329 END update_matched_ISIR;
1330
1331 PROCEDURE update_fabase (p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1332 p_ISIR_corr_status igf_ap_fa_base_rec_all.ISIR_corr_status%TYPE,
1333 p_ISIR_corr_status_date igf_ap_fa_base_rec_all.ISIR_corr_status_date%TYPE)
1334 IS
1335 /*
1336 || Created By : brajendr
1337 || Created On : 08-NOV-2000
1338 || Purpose : Process which inserts comment codes of the student.
1339 || Known limitations, enhancements or remarks :
1340 || Change History :
1341 || Who When What
1342 || masehgal 11-Nov-2002 FA 101 - SAP Obsoletion
1343 || removed packaging hold
1344 || masehgal 25-Sep-2002 FA 104 - To Do Enhancements
1345 || Added manual_disb_hold in update of Fa Base Rec
1346 || (reverse chronological order - newest change first)
1347 */
1348
1349 CURSOR cur_upd_base (p_base_id igf_ap_fa_base_rec_all.base_id%TYPE) IS
1350 SELECT fab.*
1351 FROM igf_ap_fa_base_rec fab
1352 WHERE fab.base_id = p_base_id;
1353
1354 cur_fbr_rec cur_upd_base%ROWTYPE;
1355 l_fed_verif_stat igf_ap_fa_base_rec.fed_verif_status%TYPE;
1356 l_fed_verif_date igf_ap_fa_base_rec.fed_verif_status_date%TYPE := TRUNC(SYSDATE);
1357
1358 BEGIN
1359
1360 FOR cur_fbr_rec IN cur_upd_base (p_base_id) LOOP
1361
1362 IF g_fed_verif_status = 'REPROCESSED' THEN
1363
1364 cur_fbr_rec.fed_verif_status := g_fed_verif_status ;
1365 g_fed_verif_status := NULL;
1366
1367 ELSIF cur_fbr_rec.fed_verif_status IN ('CORRSENT','NOTVERIFIED', 'NOTSELECTED')
1368 OR cur_fbr_rec.fed_verif_status IS NULL THEN
1369
1370 IF LTRIM(RTRIM(g_verification_flag)) = 'Y' THEN
1371 cur_fbr_rec.fed_verif_status := 'SELECTED';
1372 ELSE
1373 cur_fbr_rec.fed_verif_status := 'NOTSELECTED';
1374 END IF;
1375
1376 END IF;
1377
1378 igf_ap_fa_base_rec_pkg.update_row (x_Mode => 'R',
1379 x_rowid => cur_fbr_rec.row_id,
1380 x_base_id => cur_fbr_rec.base_id,
1381 x_ci_cal_type => cur_fbr_rec.ci_cal_type,
1382 x_person_id => cur_fbr_rec.person_id,
1383 x_ci_sequence_number => cur_fbr_rec.ci_sequence_number,
1384 x_org_id => cur_fbr_rec.org_id,
1385 x_coa_pending => cur_fbr_rec.coa_pending,
1386 x_verification_process_run => cur_fbr_rec.verification_process_run,
1387 x_inst_verif_status_date => cur_fbr_rec.inst_verif_status_date,
1388 x_manual_verif_flag => cur_fbr_rec.manual_verif_flag,
1389 x_fed_verif_status => cur_fbr_rec.fed_verif_status,
1390 x_fed_verif_status_date => l_fed_verif_date,
1391 x_inst_verif_status => cur_fbr_rec.inst_verif_status,
1392 x_nslds_eligible => g_nslds_match_flag,
1393 x_ede_correction_batch_id => cur_fbr_rec.ede_correction_batch_id,
1394 x_fa_process_status_date => TRUNC(SYSDATE),
1395 x_ISIR_corr_status => p_ISIR_corr_status,
1396 x_ISIR_corr_status_date => p_ISIR_corr_status_date,
1397 x_ISIR_status => 'Received-Valid',
1398 x_ISIR_status_date => TRUNC(SYSDATE),
1399 x_coa_code_f => cur_fbr_rec.coa_code_f,
1400 x_coa_code_i => cur_fbr_rec.coa_code_i,
1401 x_coa_f => cur_fbr_rec.coa_f,
1402 x_coa_i => cur_fbr_rec.coa_i,
1403 x_disbursement_hold => cur_fbr_rec.disbursement_hold,
1404 x_fa_process_status => cur_fbr_rec.fa_process_status,
1405 x_notification_status => cur_fbr_rec.notification_status,
1406 x_notification_status_date => cur_fbr_rec.notification_status_date,
1407 x_packaging_status => cur_fbr_rec.packaging_status,
1408 x_packaging_status_date => cur_fbr_rec.packaging_status_date,
1409 x_total_package_accepted => cur_fbr_rec.total_package_accepted,
1410 x_total_package_offered => cur_fbr_rec.total_package_offered,
1411 x_admstruct_id => cur_fbr_rec.admstruct_id,
1412 x_admsegment_1 => cur_fbr_rec.admsegment_1,
1413 x_admsegment_2 => cur_fbr_rec.admsegment_2,
1414 x_admsegment_3 => cur_fbr_rec.admsegment_3,
1415 x_admsegment_4 => cur_fbr_rec.admsegment_4,
1416 x_admsegment_5 => cur_fbr_rec.admsegment_5,
1417 x_admsegment_6 => cur_fbr_rec.admsegment_6,
1418 x_admsegment_7 => cur_fbr_rec.admsegment_7,
1419 x_admsegment_8 => cur_fbr_rec.admsegment_8,
1420 x_admsegment_9 => cur_fbr_rec.admsegment_9,
1421 x_admsegment_10 => cur_fbr_rec.admsegment_10,
1422 x_admsegment_11 => cur_fbr_rec.admsegment_11,
1423 x_admsegment_12 => cur_fbr_rec.admsegment_12,
1424 x_admsegment_13 => cur_fbr_rec.admsegment_13,
1425 x_admsegment_14 => cur_fbr_rec.admsegment_14,
1426 x_admsegment_15 => cur_fbr_rec.admsegment_15,
1427 x_admsegment_16 => cur_fbr_rec.admsegment_16,
1428 x_admsegment_17 => cur_fbr_rec.admsegment_17,
1429 x_admsegment_18 => cur_fbr_rec.admsegment_18,
1430 x_admsegment_19 => cur_fbr_rec.admsegment_19,
1431 x_admsegment_20 => cur_fbr_rec.admsegment_20,
1432 x_packstruct_id => cur_fbr_rec.packstruct_id,
1433 x_packsegment_1 => cur_fbr_rec.packsegment_1,
1434 x_packsegment_2 => cur_fbr_rec.packsegment_2,
1435 x_packsegment_3 => cur_fbr_rec.packsegment_3,
1436 x_packsegment_4 => cur_fbr_rec.packsegment_4,
1437 x_packsegment_5 => cur_fbr_rec.packsegment_5,
1438 x_packsegment_6 => cur_fbr_rec.packsegment_6,
1439 x_packsegment_7 => cur_fbr_rec.packsegment_7,
1440 x_packsegment_8 => cur_fbr_rec.packsegment_8,
1441 x_packsegment_9 => cur_fbr_rec.packsegment_9,
1442 x_packsegment_10 => cur_fbr_rec.packsegment_10,
1443 x_packsegment_11 => cur_fbr_rec.packsegment_11,
1444 x_packsegment_12 => cur_fbr_rec.packsegment_12,
1445 x_packsegment_13 => cur_fbr_rec.packsegment_13,
1446 x_packsegment_14 => cur_fbr_rec.packsegment_14,
1447 x_packsegment_15 => cur_fbr_rec.packsegment_15,
1448 x_packsegment_16 => cur_fbr_rec.packsegment_16,
1449 x_packsegment_17 => cur_fbr_rec.packsegment_17,
1450 x_packsegment_18 => cur_fbr_rec.packsegment_18,
1451 x_packsegment_19 => cur_fbr_rec.packsegment_19,
1452 x_packsegment_20 => cur_fbr_rec.packsegment_20,
1453 x_miscstruct_id => cur_fbr_rec.miscstruct_id,
1454 x_miscsegment_1 => cur_fbr_rec.miscsegment_1,
1455 x_miscsegment_2 => cur_fbr_rec.miscsegment_2,
1456 x_miscsegment_3 => cur_fbr_rec.miscsegment_3,
1457 x_miscsegment_4 => cur_fbr_rec.miscsegment_4,
1458 x_miscsegment_5 => cur_fbr_rec.miscsegment_5,
1459 x_miscsegment_6 => cur_fbr_rec.miscsegment_6,
1460 x_miscsegment_7 => cur_fbr_rec.miscsegment_7,
1461 x_miscsegment_8 => cur_fbr_rec.miscsegment_8,
1462 x_miscsegment_9 => cur_fbr_rec.miscsegment_9,
1463 x_miscsegment_10 => cur_fbr_rec.miscsegment_10,
1464 x_miscsegment_11 => cur_fbr_rec.miscsegment_11,
1465 x_miscsegment_12 => cur_fbr_rec.miscsegment_12,
1466 x_miscsegment_13 => cur_fbr_rec.miscsegment_13,
1467 x_miscsegment_14 => cur_fbr_rec.miscsegment_14,
1468 x_miscsegment_15 => cur_fbr_rec.miscsegment_15,
1469 x_miscsegment_16 => cur_fbr_rec.miscsegment_16,
1470 x_miscsegment_17 => cur_fbr_rec.miscsegment_17,
1471 x_miscsegment_18 => cur_fbr_rec.miscsegment_18,
1472 x_miscsegment_19 => cur_fbr_rec.miscsegment_19,
1473 x_miscsegment_20 => cur_fbr_rec.miscsegment_20,
1474 x_prof_judgement_flg => cur_fbr_rec.prof_judgement_flg,
1475 x_nslds_data_override_flg => cur_fbr_rec.nslds_data_override_flg,
1476 x_target_group => cur_fbr_rec.target_group,
1477 x_coa_fixed => cur_fbr_rec.coa_fixed,
1478 x_profile_status => cur_fbr_rec.profile_status,
1479 x_profile_status_date => cur_fbr_rec.profile_status_date,
1480 x_profile_fc => cur_fbr_rec.profile_fc,
1481 x_coa_pell => cur_fbr_rec.coa_pell,
1482 x_manual_disb_hold => cur_fbr_rec.manual_disb_hold,
1483 x_pell_alt_expense => cur_fbr_rec.pell_alt_expense,
1484 x_assoc_org_num => cur_fbr_rec.assoc_org_num,
1485 x_award_fmly_contribution_type => cur_fbr_rec.award_fmly_contribution_type,
1486 x_packaging_hold => cur_fbr_rec.packaging_hold,
1487 x_isir_locked_by => cur_fbr_rec.isir_locked_by,
1488 x_adnl_unsub_loan_elig_flag => cur_fbr_rec.adnl_unsub_loan_elig_flag,
1489 x_lock_awd_flag => cur_fbr_rec.lock_awd_flag,
1490 x_lock_coa_flag => cur_fbr_rec.lock_coa_flag
1491
1492 );
1493 END LOOP;
1494 EXCEPTION
1495
1496 WHEN others THEN
1497 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1498 fnd_message.set_token('NAME','IGF_AP_ISIR_IMPORT_PKG.UPDATE_FABASE');
1499 fnd_file.put_line(fnd_file.log,SQLERRM);
1500 igs_ge_msg_stack.add;
1501 app_exception.raise_exception;
1502
1503 END update_fabase;
1504
1505 PROCEDURE update_ISIR_corr (p_ISIRc_id igf_ap_ISIR_corr_all.ISIRc_id%TYPE,
1506 p_correction_status igf_ap_ISIR_corr_all.correction_status%TYPE )
1507 IS
1508 /*
1509 || Created By : skoppula
1510 || Created On : 03-JUL-2001
1511 || Purpose : Procedure updates the correction status of the ISIR corrections
1512 || Known limitations, enhancements or remarks :
1513 || Change History :
1514 || Who When What
1515 || (reverse chronological order - newest change first)
1516 */
1517
1518 l_correction_status igf_ap_ISIR_corr.correction_status%TYPE;
1519 CURSOR cur_corr IS
1520 SELECT *
1521 FROM igf_ap_ISIR_corr
1522 WHERE ISIRc_id = p_ISIRc_id;
1523
1524 cur_ISIR_corr cur_corr%ROWTYPE;
1525
1526 BEGIN
1527
1528 FOR cur_ISIR_corr IN cur_corr LOOP
1529
1530 igf_ap_ISIR_corr_pkg.update_row (
1531 x_rowid => cur_ISIR_corr.row_id,
1532 x_ISIRc_id => cur_ISIR_corr.ISIRc_id,
1533 x_ISIR_id => cur_ISIR_corr.ISIR_id,
1534 x_ci_sequence_number => cur_ISIR_corr.ci_sequence_number,
1535 x_ci_cal_type => cur_ISIR_corr.ci_cal_type,
1536 x_sar_field_number => cur_ISIR_corr.sar_field_number,
1537 x_original_value => cur_ISIR_corr.original_value,
1538 x_batch_id => cur_ISIR_corr.batch_id,
1539 x_corrected_value => cur_ISIR_corr.corrected_value,
1540 x_correction_status => NVL(p_correction_status,cur_ISIR_corr.correction_status),
1541 x_mode => 'R');
1542 END LOOP;
1543
1544 EXCEPTION
1545 WHEN others THEN
1546 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1547 fnd_message.set_token('NAME','IGF_AP_ISIR_IMPORT_PKG.UPDATE_ISIR_CORR');
1548 fnd_file.put_line(fnd_file.log,SQLERRM);
1549 igs_ge_msg_stack.add;
1550 app_exception.raise_exception;
1551 END update_ISIR_corr;
1552
1553 PROCEDURE validate_corrections (p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1554 p_ISIR_id igf_ap_ISIR_matched.ISIR_id%TYPE)
1555 IS
1556
1557 CURSOR cur_ISIR (p_base_id igf_ap_fa_base_rec_all.base_id%TYPE) IS
1558 SELECT ISIR_id
1559 FROM igf_ap_ISIR_matched
1560 WHERE base_id = p_base_id;
1561
1562 CURSOR cur_isir_corr (cp_base_id igf_ap_fa_base_rec.base_id%TYPE ,
1563 l_isir_id igf_ap_isir_matched.isir_id%TYPE,
1564 cp_corr_stat VARCHAR2,
1565 cp_lkup_type VARCHAR2 ) IS
1566 SELECT corr.ISIR_id,
1567 corr.ISIRc_id,
1568 corr.sar_field_number,
1569 sar.sar_field_name column_name,
1570 corr.corrected_value,
1571 corr.correction_status ,
1572 lkup.meaning meaning
1573 FROM igf_ap_batch_aw_map map,
1574 igf_ap_fa_base_rec_all fabase,
1575 igf_ap_ISIR_corr corr,
1576 Igf_fc_sar_cd_mst sar ,
1577 igf_lookups_view lkup
1578 WHERE fabase.base_id = cp_base_id
1579 AND map.ci_cal_type = fabase.ci_cal_type
1580 AND map.ci_sequence_number = fabase.ci_sequence_number
1581 AND sar.sys_award_year = map.sys_award_year
1582 AND corr.isir_id = l_isir_id
1583 AND corr.correction_status <> cp_corr_stat
1584 AND sar.sar_field_number = corr.sar_field_number
1585 AND lkup.lookup_type = cp_lkup_type
1586 AND lkup.lookup_code = sar.sar_field_name ;
1587
1588 l_correction_value VARCHAR2(255);
1589 l_new_value VARCHAR2(255);
1590 lv_cur PLS_INTEGER ;
1591 lv_retval igf_ap_ISIR_corr.original_value%TYPE;
1592 lv_stmt VARCHAR2(2000);
1593 lv_rows integer;
1594 lv_column_name VARCHAR2(30);
1595 lv_column_meaning igf_lookups_view.meaning%TYPE ;
1596 ln_count_corr NUMBER := 99999;
1597 l_corr_stat VARCHAR2(30) ;
1598 ln_isir_id igf_ap_isir_matched.isir_id%TYPE;
1599 l_lkup_type VARCHAR2(60);
1600
1601 BEGIN
1602
1603 FOR rec_ISIR IN cur_ISIR (p_base_id) LOOP
1604
1605 ln_count_corr := 0;
1606 l_corr_stat := 'ACKNOWLEDGED' ;
1607 l_lkup_type := 'IGF_AP_SAR_FIELD_MAP' ;
1608 FOR rec_ISIR_corr IN cur_ISIR_corr ( p_base_id, rec_ISIR.ISIR_id, l_corr_stat, l_lkup_type) LOOP
1609
1610 -- The Correction Value that will be sent to CPS.
1611 l_correction_value := rec_ISIR_corr.corrected_value;
1612 -- The Values received from CPS for that Column
1613 lv_column_name := rec_ISIR_corr.column_name;
1614 lv_column_meaning := rec_ISIR_corr.meaning;
1615 ln_isir_id := TO_CHAR (p_isir_id) ;
1616
1617 lv_cur := DBMS_SQL.OPEN_CURSOR;
1618 lv_stmt := 'SELECT '||lv_column_name ||' FROM igf_ap_ISIR_matched where ISIR_id = :l_isir_id ' ;
1619 DBMS_SQL.PARSE(lv_cur,lv_stmt,6);
1620 DBMS_SQL.BIND_VARIABLE(lv_cur, 'l_isir_id', ln_isir_id);
1621
1622 DBMS_SQL.DEFINE_COLUMN(lv_cur,1,lv_retval,30);
1623 lv_rows := DBMS_SQL.EXECUTE_AND_FETCH(lv_cur);
1624 DBMS_SQL.COLUMN_VALUE(lv_cur,1,lv_retval);
1625 DBMS_SQL.CLOSE_CURSOR(lv_cur);
1626
1627 --
1628 -- Compare the values and if the value send for correction is same as the value present in the ISIR then
1629 -- Mark the record as ACKNOWLEDGED.
1630 --
1631 IF LTRIM(RTRIM(UPPER(NVL(lv_retval,'##')))) = LTRIM(RTRIM(UPPER(NVL(l_correction_value,'##')))) THEN
1632 IF NOT igf_ap_ISIR_corr_pkg.get_uk_for_validation ( x_ISIR_id => rec_ISIR_corr.ISIR_id,
1633 x_sar_field_number => rec_ISIR_corr.sar_field_number,
1634 x_correction_status => 'ACKNOWLEDGED') THEN
1635 update_ISIR_corr (rec_ISIR_corr.ISIRc_id, 'ACKNOWLEDGED');
1636 fnd_message.set_name('IGF','IGF_AP_ISIR_CORR_ACK');
1637 fnd_message.set_token('FIELD', lv_column_meaning);
1638 fnd_file.put_line(fnd_file.log,fnd_message.get);
1639 END IF;
1640
1641 ELSE
1642 IF NOT igf_ap_ISIR_corr_pkg.get_uk_for_validation ( x_ISIR_id => rec_ISIR_corr.ISIR_id,
1643 x_sar_field_number => rec_ISIR_corr.sar_field_number,
1644 x_correction_status => 'READY') THEN
1645 update_ISIR_corr (rec_ISIR_corr.ISIRc_id, 'READY');
1646 ln_count_corr := ln_count_corr + 1;
1647 fnd_message.set_name('IGF','IGF_AP_ISIR_CORR_READY');
1648 fnd_message.set_token('FIELD', lv_column_meaning);
1649 fnd_file.put_line(fnd_file.log,fnd_message.get);
1650 END IF;
1651 END IF;
1652 END LOOP;
1653
1654 --
1655 -- If all the ISIR corrections values match with the value in Current ISIR then update the Verification Status to REPROCESSED.
1656 --
1657 IF ln_count_corr = 0 THEN
1658 g_fed_verif_status := 'REPROCESSED';
1659 update_fabase(g_base_rec.base_id,g_base_rec.ISIR_corr_status,g_base_rec.ISIR_corr_status_date);
1660 update_matched_ISIR(g_ISIR_rec.ISIR_id,'ORIGINAL','Y','Y');
1661 -- set payment ISIR as active ISIR.
1662 END IF;
1663 END LOOP;
1664
1665 EXCEPTION
1666 WHEN others THEN
1667 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1668 fnd_message.set_token('NAME','IGF_AP_ISIR_IMPORT_PKG.VALIDATE_CORRECTIONS');
1669 fnd_file.put_line(fnd_file.log,SQLERRM);
1670 igs_ge_msg_stack.add;
1671 app_exception.raise_exception;
1672 END validate_corrections;
1673
1674 PROCEDURE prepare_message
1675 IS
1676 /*
1677 || Created By : skoppula
1678 || Created On : 04-JUL-2001
1679 || Purpose : To create the Correction ISIR
1680 || Known limitations, enhancements or remarks :
1681 || Change History :
1682 || Who When What
1683 || masehgal 19-Mar-2002 # 2167635 Added column ow_id
1684 || (reverse chronological order - newest change first)
1685 */
1686 l_given_names CHAR(301);
1687 l_person_number CHAR(30);
1688 l_rowid VARCHAR2(30);
1689 l_ow_id NUMBER;
1690 CURSOR cur_get_name
1691 IS
1692 SELECT given_names,
1693 person_number
1694 FROM igf_ap_fa_con_v
1695 WHERE base_id = g_base_id;
1696
1697 BEGIN
1698
1699 OPEN cur_get_name;
1700 FETCH cur_get_name INTO l_given_names,l_person_number;
1701 igf_ap_outcorr_wf_pkg.insert_row (
1702 x_rowid => l_rowid,
1703 x_person_number => l_person_number,
1704 x_given_names => l_given_names,
1705 x_transaction_number => g_transaction_num,
1706 x_item_key => 'NEW',
1707 x_ow_id => l_ow_id,
1708 x_mode => 'R');
1709
1710 CLOSE cur_get_name;
1711
1712 EXCEPTION
1713 WHEN others THEN
1714 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1715 fnd_message.set_token('NAME','IGF_AP_ISIR_IMPORT_PKG.PREPARE_MESSAGE');
1716 fnd_file.put_line(fnd_file.log,SQLERRM);
1717 igs_ge_msg_stack.add;
1718 app_exception.raise_exception;
1719 END prepare_message;
1720
1721
1722 PROCEDURE create_message( document_id IN VARCHAR2,
1723 display_type IN VARCHAR2,
1724 document IN OUT NOCOPY VARCHAR2,
1725 document_type IN OUT NOCOPY VARCHAR2)
1726 IS
1727 /*
1728 || Created By : skoppula
1729 || Created On : 04-JUL-2001
1730 || Purpose : To create the Correction ISIR
1731 || Known limitations, enhancements or remarks :
1732 || Change History :
1733 || Who When What
1734 || masehgal 19-Mar-2002 # 2167635 Added column ow_id
1735 || (reverse chronological order - newest change first)
1736 */
1737 l_item_type VARCHAR2(200);
1738 l_item_key VARCHAR2(300);
1739 l_interim_str VARCHAR2(500);
1740 l_cnt NUMBER ;
1741 l_ow_id igf_ap_outcorr_wf.ow_id%type;
1742 l_given_names igf_ap_outcorr_wf.given_names%TYPE;
1743
1744 CURSOR cur_get_name
1745 IS
1746 SELECT given_names,
1747 person_number
1748 FROM igf_ap_fa_con_v
1749 WHERE base_id = g_base_id;
1750
1751 CURSOR cur_upd_key
1752 IS
1753 SELECT wf.*,
1754 wf.rowid row_id
1755 FROM igf_ap_outcorr_wf wf
1756 WHERE item_key = 'NEW';
1757
1758 CURSOR cur_get_data
1759 IS
1760 SELECT *
1761 FROM igf_ap_outcorr_wf
1762 WHERE item_key = l_item_key;
1763
1764 l_msg_body cur_get_data%ROWTYPE;
1765 l_upd_key cur_upd_key%ROWTYPE;
1766
1767 BEGIN
1768
1769 l_item_key := LTRIM(RTRIM(SUBSTR(document_id,INSTR(document_id,':',1) +1)));
1770
1771 OPEN cur_upd_key;
1772
1773 LOOP
1774 FETCH cur_upd_key INTO l_upd_key;
1775 EXIT WHEN cur_upd_key%NOTFOUND;
1776 IGF_AP_OUTCORR_WF_PKG.update_row(
1777 x_rowid => l_upd_key.row_id,
1778 x_person_number => l_upd_key.person_number,
1779 x_given_names => l_upd_key.given_names,
1780 x_transaction_number => l_upd_key.transaction_number,
1781 x_item_key => l_item_key,
1782 x_ow_id => l_upd_key.ow_id,
1783 x_mode => 'R'
1784 );
1785
1786 END LOOP;
1787 CLOSE cur_upd_key;
1788 OPEN cur_get_data;
1789 FETCH cur_get_data INTO l_msg_body;
1790
1791 IF cur_get_data%NOTFOUND THEN
1792 l_cnt := 0;
1793 CLOSE cur_get_data;
1794 ELSE
1795 l_cnt := 1;
1796 CLOSE cur_get_data;
1797 END IF;
1798
1799 IF l_cnt = 0 THEN
1800
1801 fnd_message.set_name ( 'IGF','IGF_AP_NO_DATA_FOUND');
1802 document := fnd_message.get;
1803 ELSE
1804
1805 OPEN cur_get_data;
1806 LOOP
1807 FETCH cur_get_data INTO l_msg_body;
1808 EXIT WHEN cur_get_data%NOTFOUND;
1809 l_given_names := SUBSTR(l_msg_body.given_names,1,LENGTH(LTRIM(RTRIM(l_msg_body.given_names))));
1810 document := document||l_msg_body.person_number||fnd_global.tab||
1811 l_given_names||fnd_global.tab||l_msg_body.transaction_number;
1812 document := document||fnd_global.newline;
1813 END LOOP;
1814 CLOSE cur_get_data;
1815
1816 END IF;
1817 --delete from igf_ap_outcorr_wf;
1818
1819 IF display_type = 'text/plain' THEN
1820 document_type := 'text/plain';
1821 RETURN;
1822 ELSE
1823 document_type := 'text/plain';
1824 RETURN;
1825 END IF;
1826
1827 EXCEPTION
1828 WHEN others THEN
1829 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1830 fnd_message.set_token('NAME','IGF_AP_ISIR_IMPORT_PKG.CREATE_MESSAGE');
1831 fnd_file.put_line(fnd_file.log,SQLERRM);
1832 igs_ge_msg_stack.add;
1833 app_exception.raise_exception;
1834 END create_message;
1835
1836
1837 PROCEDURE outside_corrections(itemtype IN VARCHAR2,
1838 itemkey IN VARCHAR2,
1839 actid IN NUMBER,
1840 funcmode IN VARCHAR2,
1841 resultout OUT NOCOPY VARCHAR2)
1842 IS
1843 /*
1844 || Created By : skoppula
1845 || Created On : 20-FEB-2001
1846 || Purpose:Checks whether Fabase record exists
1847 || Known limitations, enhancements or remarks :
1848 || Change History :
1849 || Who When What
1850 || (reverse chronological order - newest change first)
1851 */
1852
1853 document VARCHAR2(1000);
1854 document_type VARCHAR2(1000);
1855 l_user VARCHAR2(80);
1856
1857 BEGIN
1858 IF funcmode='RUN' THEN
1859 l_user := fnd_global.user_name;
1860 l_user := LTRIM(RTRIM(l_user));
1861
1862 wf_engine.setitemattrtext(itemtype,
1863 itemkey,
1864 'VUSER',
1865 l_user);
1866
1867 wf_engine.setitemattrtext(itemtype,
1868 itemkey,
1869 'VMSGBODY',
1870 g_msg_body);
1871
1872 wf_engine.setitemattrtext(itemtype,
1873 itemkey,
1874 'MSGDOC','PLSQL:IGF_AP_ISIR_IMPORT_PKG.CREATE_MESSAGE/'||itemtype||':'||itemkey);
1875 resultout := 'COMPLETE';
1876 END IF;
1877
1878 EXCEPTION
1879 WHEN OTHERS THEN
1880 wf_core.context('IGF_AP_NOTIFY_CHANGE_WF','FABASE_EXISTS',itemtype,itemkey,TO_CHAR(actid),funcmode);
1881 END outside_corrections;
1882
1883 PROCEDURE send_message
1884 IS
1885 /*
1886 || Created By : skoppula
1887 || Created On : 04-JUL-2001
1888 || Purpose : To create the Correction ISIR
1889 || Known limitations, enhancements or remarks :
1890 || Change History :
1891 || Who When What
1892 || (reverse chronological order - newest change first)
1893 */
1894 CURSOR cur_get_seq
1895 IS
1896 SELECT
1897 igf_ap_corr_wf_s.NEXTVAL
1898 FROM DUAL;
1899
1900 l_item_key NUMBER;
1901
1902 BEGIN
1903 OPEN cur_get_seq;
1904 FETCH cur_get_seq INTO l_item_key;
1905 CLOSE cur_get_seq;
1906 wf_engine.createprocess('OUTCORR',l_item_key,'NOTIFY');
1907 wf_engine.startprocess('OUTCORR',l_item_key);
1908 EXCEPTION
1909 WHEN others THEN
1910 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1911 fnd_message.set_token('NAME','IGF_AP_ISIR_IMPORT_PKG.SEND_MESSAGE');
1912 fnd_file.put_line(fnd_file.log,SQLERRM);
1913 igs_ge_msg_stack.add;
1914 app_exception.raise_exception;
1915 END send_message;
1916
1917 END IGF_AP_ISIR_IMPORT_PKG;