[Home] [Help]
PACKAGE BODY: APPS.IGF_AP_LG_VER_IMP
Source
1 PACKAGE BODY igf_ap_lg_ver_imp AS
2 /* $Header: IGFAP38B.pls 120.2 2006/01/17 02:37:07 tsailaja noship $ */
3
4
5 g_log_tab_index NUMBER := 0;
6
7 TYPE log_record IS RECORD ( person_number VARCHAR2(30),
8 message_text VARCHAR2(500));
9
10 -- The PL/SQL table for storing the log messages
11 TYPE LogTab IS TABLE OF log_record index by binary_integer;
12
13 g_log_tab LogTab;
14
15
16 -- global cursor for fa base rec
17 CURSOR c_fabase ( p_person_number hz_parties.party_number%TYPE ) IS
18 SELECT fabase.*
19 FROM igf_ap_fa_base_rec fabase,
20 hz_parties hz
21 WHERE hz.party_id = fabase.person_id
22 AND hz.party_number = p_person_number
23 AND fabase.ci_cal_type = g_ci_cal_type
24 AND fabase.ci_sequence_number = g_ci_sequence_number ;
25 g_fabase c_fabase%ROWTYPE ;
26
27
28 PROCEDURE log_input_params( p_batch_num IN igf_aw_li_coa_ints.batch_num%TYPE ,
29 p_alternate_code IN igs_ca_inst.alternate_code%TYPE ,
30 p_delete_flag IN VARCHAR2 ) IS
31 /*
32 || Created By : masehgal
33 || Created On : 28-May-2003
34 || Purpose : Logs all the Input Parameters
35 || Known limitations, enhancements or remarks :
36 || Change History :
37 || Who When What
38 || (reverse chronological order - newest change first)
39 */
40
41 -- cursor to get batch desc for the batch id from igf_ap_li_bat_ints
42 CURSOR c_batch_desc(cp_batch_num igf_aw_li_coa_ints.batch_num%TYPE ) IS
43 SELECT batch_desc, batch_type
44 FROM igf_ap_li_bat_ints
45 WHERE batch_num = cp_batch_num ;
46
47 l_lkup_type VARCHAR2(60) ;
48 l_lkup_code VARCHAR2(60) ;
49 l_batch_desc igf_ap_li_bat_ints.batch_desc%TYPE ;
50 l_batch_type igf_ap_li_bat_ints.batch_type%TYPE ;
51 l_batch_id igf_ap_li_bat_ints.batch_type%TYPE ;
52 l_yes_no igf_lookups_view.meaning%TYPE ;
53 l_award_year_pmpt igf_lookups_view.meaning%TYPE ;
54 l_params_pass_prmpt igf_lookups_view.meaning%TYPE ;
55 l_person_number_prmpt igf_lookups_view.meaning%TYPE ;
56 l_batch_num_prmpt igf_lookups_view.meaning%TYPE ;
57 l_error igf_lookups_view.meaning%TYPE ;
58
59 BEGIN -- begin log parameters
60
61 -- get the batch description
62 OPEN c_batch_desc( p_batch_num) ;
63 FETCH c_batch_desc INTO l_batch_desc, l_batch_type ;
64 CLOSE c_batch_desc ;
65
66 l_error := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','ERROR');
67 l_person_number_prmpt := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','PERSON_NUMBER');
68 l_batch_num_prmpt := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','BATCH_ID');
69 l_award_year_pmpt := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','AWARD_YEAR');
70 l_yes_no := igf_ap_gen.get_lookup_meaning('IGF_AP_YES_NO',p_delete_flag);
71 l_params_pass_prmpt := igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','PARAMETER_PASS');
72
73 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
74 FND_FILE.PUT_LINE( FND_FILE.LOG, '-------------------------------------------------------------');
75 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
76
77 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ') ;
78 FND_FILE.PUT_LINE( FND_FILE.LOG, l_params_pass_prmpt) ; --Parameters Passed
79 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ') ;
80
81 FND_FILE.PUT_LINE( FND_FILE.LOG, RPAD( l_award_year_pmpt, 40) || ' : '|| p_alternate_code ) ;
82
83 FND_FILE.PUT_LINE( FND_FILE.LOG, RPAD( l_batch_num_prmpt, 40) || ' : '|| TO_CHAR(p_batch_num) || '-' || l_batch_desc ) ;
84
85 FND_FILE.PUT_LINE( FND_FILE.LOG, RPAD( FND_MESSAGE.GET_STRING('IGS','IGS_GE_ASK_DEL_REC'), 40) || ' : '|| l_yes_no ) ;
86 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
87 FND_FILE.PUT_LINE( FND_FILE.LOG, '-------------------------------------------------------------');
88 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
89
90 EXCEPTION
91 WHEN OTHERS THEN
92 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
93 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_lg_ver_imp.log_input_params.exception','Unhandled Exception :'||SQLERRM);
94 END IF;
95 END log_input_params ;
96
97
98 PROCEDURE print_log_process( p_person_number IN VARCHAR2,
99 p_error IN VARCHAR2 ) IS
100 /*
101 || Created By : masehgal
102 || Created On : 01-Jun-2003
103 || Purpose : This process gets the records from the pl/sql table and print in the log file
104 || Known limitations, enhancements or remarks :
105 || Change History :
106 || Who When What
107 || (reverse chronological order - newest change first)
108 */
109
110 l_count NUMBER(5) := g_log_tab.COUNT;
111 l_old_person VARCHAR2(30) := '*******';
112
113 BEGIN
114
115 FOR i IN 1..l_count LOOP
116 IF l_old_person <> g_log_tab(i).person_number THEN
117 fnd_file.put_line(fnd_file.log,'-----------------------------------------------------------------------------');
118 fnd_file.put_line(fnd_file.log,p_person_number || ' : ' || g_log_tab(i).person_number);
119 END IF;
120 fnd_file.put_line(fnd_file.log,g_log_tab(i).message_text);
121 l_old_person := g_log_tab(i).person_number;
122 END LOOP;
123
124 EXCEPTION
125 WHEN OTHERS THEN
126 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
127 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_lg_ver_imp.print_log_process.exception','Unhandled Exception :'||SQLERRM);
128 END IF;
129
130 END print_log_process;
131
132
133 PROCEDURE check_dup_ver ( p_sar_num IN igf_ap_inst_ver_item_all.isir_map_col%TYPE,
134 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
135 p_dup_ver OUT NOCOPY BOOLEAN ) IS
136 /*
137 || Created By : masehgal
138 || Created On : 28-May-2003
139 || Purpose : check duplication of Ver Item
140 || Known limitations, enhancements or remarks :
141 || Change History :
142 || Who When What
143 || (reverse chronological order - newest change first)
144 */
145
146 CURSOR chk_dup ( cp_base_id igf_ap_isir_matched.base_id%TYPE,
147 cp_sar_num igf_ap_inst_ver_item_all.isir_map_col%TYPE ) IS
148 SELECT 1
149 FROM igf_ap_inst_ver_item_all
150 WHERE base_id = cp_base_id
151 AND isir_map_col = cp_sar_num ;
152 l_count NUMBER ;
153
154 BEGIN
155 OPEN chk_dup ( p_base_id, p_sar_num) ;
156 FETCH chk_dup INTO l_count ;
157 IF chk_dup%NOTFOUND THEN
158 p_dup_ver := FALSE ;
159 ELSE
160 p_dup_ver := TRUE ;
161 END IF ;
162 CLOSE chk_dup ;
163
164 EXCEPTION
165 WHEN OTHERS THEN
166 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
167 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_lg_ver_imp.check_dup_ver.exception','Unhandled Exception :'||SQLERRM);
168 END IF;
169 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
170 fnd_message.set_token('NAME','IGF_AP_LG_VER_IMP.CHECK_DUP_VER');
171 igs_ge_msg_stack.add;
172 app_exception.raise_exception;
173
174 END check_dup_ver ;
175
176
177 PROCEDURE chk_corr_items (p_sar_num IN igf_ap_inst_ver_item_all.isir_map_col%TYPE,
178 p_pay_isir_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
179 p_corr_exist OUT NOCOPY BOOLEAN ) IS
180 /*
181 || Created By : masehgal
182 || Created On : 28-May-2003
183 || Purpose : check correction items presence
184 || Known limitations, enhancements or remarks :
185 || Change History :
186 || Who When What
187 || (reverse chronological order - newest change first)
188 */
189
190 CURSOR cur_corr_items ( cp_pay_isir_id igf_ap_isir_corr.isir_id%TYPE,
191 cp_sar_num igf_ap_isir_corr.sar_field_number%TYPE ) IS
192 SELECT 1
193 FROM igf_ap_isir_corr
194 WHERE isir_id = cp_pay_isir_id
195 AND sar_field_number = cp_sar_num ;
196 l_count NUMBER ;
197
198 BEGIN
199 OPEN cur_corr_items ( p_pay_isir_id, p_sar_num) ;
200 FETCH cur_corr_items INTO l_count ;
201 IF cur_corr_items%NOTFOUND THEN
202 p_corr_exist := FALSE ;
203 ELSE
204 p_corr_exist := TRUE ;
205 END IF ;
206 CLOSE cur_corr_items ;
207 EXCEPTION
208 WHEN OTHERS THEN
209 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
210 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_lg_ver_imp.chk_corr_items.exception','Unhandled Exception :'||SQLERRM);
211 END IF;
212 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
213 fnd_message.set_token('NAME','IGF_AP_LG_VER_IMP.CHK_CORR_ITEMS');
214 igs_ge_msg_stack.add;
215 app_exception.raise_exception;
216
217 END chk_corr_items ;
218
219
220 PROCEDURE delete_ver_items ( p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
221 p_sar_num IN igf_ap_inst_ver_item_all.isir_map_col%TYPE ) IS
222 /*
223 || Created By : masehgal
224 || Created On : 28-May-2003
225 || Purpose : deletion of Ver Items
226 || Known limitations, enhancements or remarks :
227 || Change History :
228 || Who When What
229 || (reverse chronological order - newest change first)
230 */
231
232 CURSOR del_ver_items( cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
233 cp_sar_num igf_ap_inst_ver_item_all.isir_map_col%TYPE ) IS
234 SELECT rowid
235 FROM igf_ap_inst_ver_item_all
236 WHERE base_id = cp_base_id
237 AND isir_map_col = cp_sar_num ;
238 lv_rowid del_ver_items%ROWTYPE;
239
240 BEGIN
241 FOR lv_rowid IN del_ver_items ( p_base_id, p_sar_num )
242 LOOP
243 igf_ap_inst_ver_item_pkg.delete_row( x_rowid => lv_rowid.rowid);
244 END LOOP;
245
246 EXCEPTION
247 WHEN OTHERS THEN
248 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
249 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_lg_ver_imp.delete_ver_items.exception','Unhandled Exception :'||SQLERRM);
250 END IF;
251 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
252 fnd_message.set_token('NAME','IGF_AP_LG_VER_IMP.DELETE_VER_TERMS');
253 igs_ge_msg_stack.add;
254 app_exception.raise_exception;
255
256 END delete_ver_items ;
257
258
259
260 PROCEDURE upd_fed_verif_status ( p_fed_verif_status igf_ap_fa_base_rec_all.fed_verif_status%TYPE ) IS
261 /*
262 || Created By : masehgal
263 || Created On : 28-May-2003
264 || Purpose : updation of verification status
265 || Known limitations, enhancements or remarks :
266 || Change History :
267 || Who When What
268 || rasahoo 17-NOV-2003 FA 128 - ISIR update 2004-05
269 || added new parameter award_fmly_contribution_type to
270 || igf_ap_fa_base_rec_pkg.update_row
271 || ugummall 26-SEP-2003 FA 126 - Multiple FA Offices.
272 || added new parameter assoc_org_num to TBH call of
273 || igf_ap_fa_base_rec_pkg w.r.t. FA 126
274 ||
275 || (reverse chronological order - newest change first)
276 */
277
278 BEGIN
279 -- get base rec values
280
281 -- update fa base rec with new verif status ...
282 igf_ap_fa_base_rec_pkg.update_row(
283 x_rowid => g_fabase.row_id,
284 x_base_id => g_fabase.base_id,
285 x_ci_cal_type => g_fabase.ci_cal_type,
286 x_person_id => g_fabase.person_id,
287 x_ci_sequence_number => g_fabase.ci_sequence_number,
288 x_org_id => g_fabase.org_id,
289 x_coa_pending => g_fabase.coa_pending,
290 x_verification_process_run => g_fabase.verification_process_run,
291 x_inst_verif_status_date => g_fabase.inst_verif_status_date,
292 x_manual_verif_flag => g_fabase.manual_verif_flag,
293 x_fed_verif_status => p_fed_verif_status,
294 x_fed_verif_status_date => TRUNC(SYSDATE),
295 x_inst_verif_status => g_fabase.inst_verif_status,
296 x_nslds_eligible => g_fabase.nslds_eligible,
297 x_ede_correction_batch_id => g_fabase.ede_correction_batch_id,
298 x_fa_process_status_date => g_fabase.fa_process_status_date,
299 x_isir_corr_status => g_fabase.isir_corr_status,
300 x_isir_corr_status_date => g_fabase.isir_corr_status_date,
301 x_isir_status => g_fabase.isir_status,
302 x_isir_status_date => g_fabase.isir_status_date,
303 x_coa_code_f => g_fabase.coa_code_f,
304 x_coa_code_i => g_fabase.coa_code_i,
305 x_coa_f => g_fabase.coa_f,
306 x_coa_i => g_fabase.coa_i,
307 x_disbursement_hold => g_fabase.disbursement_hold,
308 x_fa_process_status => g_fabase.fa_process_status,
309 x_notification_status => g_fabase.notification_status,
310 x_notification_status_date => g_fabase.notification_status_date,
311 x_packaging_status => g_fabase.packaging_status,
312 x_packaging_status_date => g_fabase.packaging_status_date,
313 x_total_package_accepted => g_fabase.total_package_accepted,
314 x_total_package_offered => g_fabase.total_package_offered,
315 x_admstruct_id => g_fabase.admstruct_id,
316 x_admsegment_1 => g_fabase.admsegment_1,
317 x_admsegment_2 => g_fabase.admsegment_2,
318 x_admsegment_3 => g_fabase.admsegment_3,
319 x_admsegment_4 => g_fabase.admsegment_4,
320 x_admsegment_5 => g_fabase.admsegment_5,
321 x_admsegment_6 => g_fabase.admsegment_6,
322 x_admsegment_7 => g_fabase.admsegment_7,
323 x_admsegment_8 => g_fabase.admsegment_8,
324 x_admsegment_9 => g_fabase.admsegment_9,
325 x_admsegment_10 => g_fabase.admsegment_10,
326 x_admsegment_11 => g_fabase.admsegment_11,
327 x_admsegment_12 => g_fabase.admsegment_12,
328 x_admsegment_13 => g_fabase.admsegment_13,
329 x_admsegment_14 => g_fabase.admsegment_14,
330 x_admsegment_15 => g_fabase.admsegment_15,
331 x_admsegment_16 => g_fabase.admsegment_16,
332 x_admsegment_17 => g_fabase.admsegment_17,
333 x_admsegment_18 => g_fabase.admsegment_18,
334 x_admsegment_19 => g_fabase.admsegment_19,
335 x_admsegment_20 => g_fabase.admsegment_20,
336 x_packstruct_id => g_fabase.packstruct_id,
337 x_packsegment_1 => g_fabase.packsegment_1,
338 x_packsegment_2 => g_fabase.packsegment_2,
339 x_packsegment_3 => g_fabase.packsegment_3,
340 x_packsegment_4 => g_fabase.packsegment_4,
341 x_packsegment_5 => g_fabase.packsegment_5,
342 x_packsegment_6 => g_fabase.packsegment_6,
343 x_packsegment_7 => g_fabase.packsegment_7,
344 x_packsegment_8 => g_fabase.packsegment_8,
345 x_packsegment_9 => g_fabase.packsegment_9,
346 x_packsegment_10 => g_fabase.packsegment_10,
347 x_packsegment_11 => g_fabase.packsegment_11,
348 x_packsegment_12 => g_fabase.packsegment_12,
349 x_packsegment_13 => g_fabase.packsegment_13,
350 x_packsegment_14 => g_fabase.packsegment_14,
351 x_packsegment_15 => g_fabase.packsegment_15,
352 x_packsegment_16 => g_fabase.packsegment_16,
353 x_packsegment_17 => g_fabase.packsegment_17,
354 x_packsegment_18 => g_fabase.packsegment_18,
355 x_packsegment_19 => g_fabase.packsegment_19,
356 x_packsegment_20 => g_fabase.packsegment_20,
357 x_miscstruct_id => g_fabase.miscstruct_id,
358 x_miscsegment_1 => g_fabase.miscsegment_1,
359 x_miscsegment_2 => g_fabase.miscsegment_2,
360 x_miscsegment_3 => g_fabase.miscsegment_3,
361 x_miscsegment_4 => g_fabase.miscsegment_4,
362 x_miscsegment_5 => g_fabase.miscsegment_5,
363 x_miscsegment_6 => g_fabase.miscsegment_6,
364 x_miscsegment_7 => g_fabase.miscsegment_7,
365 x_miscsegment_8 => g_fabase.miscsegment_8,
366 x_miscsegment_9 => g_fabase.miscsegment_9,
367 x_miscsegment_10 => g_fabase.miscsegment_10,
368 x_miscsegment_11 => g_fabase.miscsegment_11,
369 x_miscsegment_12 => g_fabase.miscsegment_12,
370 x_miscsegment_13 => g_fabase.miscsegment_13,
371 x_miscsegment_14 => g_fabase.miscsegment_14,
372 x_miscsegment_15 => g_fabase.miscsegment_15,
373 x_miscsegment_16 => g_fabase.miscsegment_16,
374 x_miscsegment_17 => g_fabase.miscsegment_17,
375 x_miscsegment_18 => g_fabase.miscsegment_18,
376 x_miscsegment_19 => g_fabase.miscsegment_19,
377 x_miscsegment_20 => g_fabase.miscsegment_20,
378 x_prof_judgement_flg => g_fabase.prof_judgement_flg,
379 x_nslds_data_override_flg => g_fabase.nslds_data_override_flg ,
380 x_target_group => g_fabase.target_group,
381 x_coa_fixed => g_fabase.coa_fixed,
382 x_coa_pell => g_fabase.coa_pell,
383 x_profile_status => g_fabase.profile_status,
384 x_profile_status_date => g_fabase.profile_status_date,
385 x_profile_fc => g_fabase.profile_fc,
386 x_tolerance_amount => g_fabase.tolerance_amount,
387 x_manual_disb_hold => g_fabase.manual_disb_hold,
388 x_mode => 'R',
389 x_pell_alt_expense => g_fabase.pell_alt_expense,
390 x_assoc_org_num => g_fabase.assoc_org_num,
391 x_award_fmly_contribution_type => g_fabase.award_fmly_contribution_type,
392 x_isir_locked_by => g_fabase.isir_locked_by,
393 x_adnl_unsub_loan_elig_flag => g_fabase.adnl_unsub_loan_elig_flag,
394 x_lock_awd_flag => g_fabase.lock_awd_flag,
395 x_lock_coa_flag => g_fabase.lock_coa_flag
396
397 );
398
399
400 EXCEPTION
401 WHEN OTHERS THEN
402 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
403 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_lg_ver_imp.upd_fed_verif_status.exception','Unhandled Exception :'||SQLERRM);
404 END IF;
405 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
406 fnd_message.set_token('NAME','IGF_AP_LG_VER_IMP.UPD_FED_VERIF_STATUS');
407 igs_ge_msg_stack.add;
408 app_exception.raise_exception;
409
410 END upd_fed_verif_status ;
411
412
413 PROCEDURE main ( errbuf OUT NOCOPY VARCHAR2,
414 retcode OUT NOCOPY NUMBER,
415 p_award_year IN VARCHAR2,
416 p_batch_num IN VARCHAR2,
417 p_delete_flag IN VARCHAR2 ) IS
418 /*
419 || Created By : masehgal
420 || Created On : 28-May-2003
421 || Purpose : Main - called from submitted request
422 || Known limitations, enhancements or remarks :
423 || Change History :
424 || Who When What
425 || (reverse chronological order - newest change first)
426 || tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
427 || rasahoo 17-Oct-2003 #3085558 FA121 Added parameter use_blank_flag in
428 || igf_ap_inst_ver_item_pkg.insert_row
429 */
430
431 l_prof_set VARCHAR2(1) ;
432 g_terminate_process BOOLEAN := FALSE ;
433 g_skip_person BOOLEAN := FALSE ;
434 g_skip_record BOOLEAN := FALSE ;
435 g_award_year_status igf_ap_batch_aw_map.award_year_status_code%TYPE ;
436 g_sys_award_year igf_ap_batch_aw_map.sys_award_year%TYPE ;
437 l_alternate_code igs_ca_inst.alternate_code%TYPE ;
438 l_rec_processed NUMBER;
439 l_rec_imported NUMBER;
440 l_last_person_number igf_ap_li_vern_ints.person_number%TYPE ;
441 l_fa_base_id igf_ap_fa_base_rec.base_id%TYPE;
442 l_person_id igf_ap_fa_base_rec.person_id%TYPE;
443 l_dup_item_found BOOLEAN;
444 l_pay_isir_id igf_ap_isir_matched.isir_id%TYPE ;
445 l_act_isir_id igf_ap_isir_matched.isir_id%TYPE ;
446 l_error igf_lookups_view.meaning%TYPE ;
447 l_person_number igf_lookups_view.meaning%TYPE ;
448 l_token VARCHAR2(60) ;
449 l_corr_exist BOOLEAN ;
450 l_fed_verif_status igf_ap_fa_base_rec_all.fed_verif_status%TYPE ;
451 l_new_fed_verif_status igf_ap_fa_base_rec_all.fed_verif_status%TYPE ;
452 l_orig_value VARCHAR2(1000);
453 l_doc_value VARCHAR2(1000);
454 lv_rowid ROWID ;
455 lv_stmt VARCHAR2(2000);
456 l_diff_flag BOOLEAN ;
457 l_doc_null_flag BOOLEAN ;
458 lv_cur PLS_INTEGER;
459 lv_rows INTEGER;
460 l_per_item_count NUMBER ;
461 l_batch_valid VARCHAR2(1) ;
462
463
464 -- cursor to get sys award year and award year status
465 CURSOR c_get_stat IS
466 SELECT award_year_status_code, sys_award_year
467 FROM igf_ap_batch_aw_map map
468 WHERE map.ci_cal_type = g_ci_cal_type
469 AND map.ci_sequence_number = g_ci_sequence_number ;
470
471 -- cursor to get persons for import
472 CURSOR c_get_persons ( cp_alternate_code igf_ap_li_vern_ints.ci_alternate_code%TYPE,
473 cp_batch_num igf_ap_li_vern_ints.batch_num%TYPE ) IS
474 SELECT *
475 FROM igf_ap_li_vern_ints
476 WHERE ci_alternate_code = cp_alternate_code
477 AND batch_num = cp_batch_num
478 AND import_status_type IN ('R','U')
479 ORDER BY person_number ;
480
481 person_rec c_get_persons%ROWTYPE ;
482
483 -- cursor to get alternate code for award year
484 CURSOR c_alternate_code( cp_ci_cal_type igs_ca_inst.cal_type%TYPE ,
485 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE ) IS
486 SELECT alternate_code
487 FROM igs_ca_inst
488 WHERE cal_type = cp_ci_cal_type
489 AND sequence_number = cp_ci_sequence_number ;
490
491 -- check SAR Field Number done
492 CURSOR c_get_sar_num ( cp_sys_award_year igf_fc_sar_cd_mst.sys_award_year%TYPE,
493 cp_sar_name igf_fc_sar_cd_mst.sar_field_name%TYPE) IS
494 SELECT sar_field_number
495 FROM igf_fc_sar_cd_mst
496 WHERE sys_award_year = cp_sys_award_year
497 AND sar_field_name = cp_sar_name ;
498 l_sar_num igf_fc_sar_cd_mst.sar_field_number%TYPE;
499
500
501 -- get active and payment ISIR
502 CURSOR c_act_pay_isir ( cp_base_id igf_ap_isir_matched.base_id%TYPE ) IS
503 SELECT isir_id, active_isir, payment_isir
504 FROM igf_ap_isir_matched
505 WHERE base_id = cp_base_id
506 AND (active_isir = 'Y' OR payment_isir = 'Y' ) ;
507 act_pay_isir_rec c_act_pay_isir%ROWTYPE ;
508
509
510 BEGIN -- of MAIN
511 igf_aw_gen.set_org_id(NULL);
512 retcode := 0;
513 l_prof_set := 'N' ;
514 /****************************************
515 -- Check if the following profiles are set
516 *****************************************/
517 l_prof_set := igf_ap_gen.check_profile ;
518
519 IF l_prof_set = 'Y' THEN
520 l_error := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','ERROR');
521 l_person_number := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','PERSON_NUMBER');
522
523 -- profiles properly set ....... proceed
524 -- Get the Award Year Calender Type and the Sequence Number
525 g_ci_cal_type := RTRIM(SUBSTR(p_award_year,1,10));
526 g_ci_sequence_number := TO_NUMBER(RTRIM(SUBSTR(p_award_year,11)));
527
528 -- Get the Award Year Alternate Code
529 OPEN c_alternate_code( g_ci_cal_type, g_ci_sequence_number ) ;
530 FETCH c_alternate_code INTO l_alternate_code ;
531 CLOSE c_alternate_code ;
532
533 -- Log input params
534 log_input_params( p_batch_num, l_alternate_code , p_delete_flag);
535 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
536 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_lg_ver_imp.main.debug','Completed input parameters logging in Procedure main');
537 END IF;
538
539 -- Get Award Year Status
540 OPEN c_get_stat ;
541 FETCH c_get_stat INTO g_award_year_status, g_sys_award_year ;
542 -- check validity of award year
543 IF c_get_stat%NOTFOUND THEN
544 -- Award Year setup tampered .... Log a message
545 FND_MESSAGE.SET_NAME('IGF','IGF_AP_AWD_YR_NOT_FOUND');
546 FND_MESSAGE.SET_TOKEN('P_AWARD_YEAR', l_alternate_code);
547 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
548 g_terminate_process := TRUE ;
549 ELSE
550 -- Award year exists but is it Open/Legacy Details .... check
551 IF g_award_year_status NOT IN ('O','LD') THEN
552 FND_MESSAGE.SET_NAME('IGF','IGF_AP_LG_INVALID_STAT');
553 FND_MESSAGE.SET_TOKEN('AWARD_STATUS', g_award_year_status);
554 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
555 g_terminate_process := TRUE ;
556 END IF ; -- awd ye open or legacy detail chk
557 END IF ; -- award year invalid check
558 CLOSE c_get_stat ;
559
560 -- check validity of batch
561 l_batch_valid := igf_ap_gen.check_batch ( p_batch_num, 'VERIF') ;
562 IF NVL(l_batch_valid,'N') <> 'Y' THEN
563 FND_MESSAGE.SET_NAME('IGF','IGF_GR_BATCH_DOES_NOT_EXIST');
564 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
565 g_terminate_process := TRUE ;
566 END IF;
567
568 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
569 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_lg_ver_imp.main.debug','Completed batch validations in Procedure main');
570 END IF;
571
572
573 /***********************************************************************
574 Person Level checks
575 l_rec_processed flag to monitor the number of records in the batch
576 submitted for processing
577 l_last_person_number Holds the last processed Person Number
578 ***********************************************************************/
579
580 -- check for terminate flag
581 IF NOT g_terminate_process THEN
582 l_last_person_number := NULL ;
583 l_rec_processed := 0 ;
584 l_per_item_count := 0 ;
585 l_rec_imported := 0 ;
586
587 -- Select persons from interface table
588 FOR person_rec IN c_get_persons (l_alternate_code, p_batch_num)
589 LOOP
590 -- validate each person
591 l_rec_processed := l_rec_processed + 1 ;
592 g_skip_record := FALSE ;
593
594 -- check if this person has been processed before ....
595 -- if yes, then skip the person related validations re-check
596 IF person_rec.person_number <> NVL(l_last_person_number,'*') THEN
597 -- have to update the fa base rec for the previous person ....
598 IF l_last_person_number IS NOT NULL THEN
599 -- only for legacy details
600 IF g_award_year_status = 'LD' THEN
601 OPEN c_fabase(l_last_person_number) ;
602 FETCH c_fabase INTO g_fabase ;
603 CLOSE c_fabase ;
604
605 l_fed_verif_status := g_fabase.fed_verif_status ;
606
607 IF l_per_item_count > 0 THEN
608 IF l_fed_verif_status IN ('SELECTED','NOTSELECTED') THEN
609 IF l_doc_null_flag THEN
610 l_new_fed_verif_status := 'WITHOUTDOC' ;
611 FND_MESSAGE.SET_NAME('IGF','IGF_AP_VER_STAT_WITHOUT_DOC');
612 g_log_tab_index := g_log_tab_index + 1;
613 g_log_tab(g_log_tab_index).person_number := l_last_person_number ;
614 g_log_tab(g_log_tab_index).message_text := RPAD(null,12) || fnd_message.get;
615 END IF ;
616 IF NOT l_doc_null_flag AND NOT l_diff_flag THEN
617 l_new_fed_verif_status := 'ACCURATE' ;
618 FND_MESSAGE.SET_NAME('IGF','IGF_AP_VER_STAT_ACCURATE');
619 g_log_tab_index := g_log_tab_index + 1;
620 g_log_tab(g_log_tab_index).person_number := l_last_person_number;
621 g_log_tab(g_log_tab_index).message_text := RPAD(null,12) || fnd_message.get;
622 END IF ;
623 IF NOT l_doc_null_flag AND l_diff_flag THEN
624 l_new_fed_verif_status := 'SELECTED' ;
625 FND_MESSAGE.SET_NAME('IGF','IGF_AP_VER_STAT_SELECTED');
626 g_log_tab_index := g_log_tab_index + 1;
627 g_log_tab(g_log_tab_index).person_number := l_last_person_number;
628 g_log_tab(g_log_tab_index).message_text := RPAD(null,12) || fnd_message.get;
629 END IF ;
630
631 upd_fed_verif_status ( l_new_fed_verif_status) ;
632 -- commit after updating the fa base rec
633 COMMIT ;
634 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
635 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_lg_ver_imp.main.debug','Federal Verification Status updated in Procedure main');
636 END IF;
637
638 END IF ;
639 END IF ;
640 END IF ;
641 END IF ;
642
643 -- new person ..
644 -- set skip flag for the new person to FALSE
645 g_skip_person := FALSE ;
646 l_diff_flag := FALSE ;
647 l_doc_null_flag := FALSE ;
648 l_per_item_count := 0 ;
649
650 -- call procedure to check person existence and fa base rec existence
651 igf_ap_gen.check_person ( person_rec.person_number, g_ci_cal_type, g_ci_sequence_number,
652 l_person_id, l_fa_base_id) ;
653
654 IF l_person_id IS NULL THEN
655 FND_MESSAGE.SET_NAME('IGF','IGF_AP_PE_NOT_EXIST');
656 g_log_tab_index := g_log_tab_index + 1;
657 g_log_tab(g_log_tab_index).person_number := person_rec.person_number;
658 g_log_tab(g_log_tab_index).message_text := RPAD(l_error,12) || fnd_message.get;
659 g_skip_person := TRUE ;
660 ELSIF l_fa_base_id IS NULL THEN
661 FND_MESSAGE.SET_NAME('IGF','IGF_AP_FABASE_NOT_FOUND');
662 g_log_tab_index := g_log_tab_index + 1;
663 g_log_tab(g_log_tab_index).person_number := person_rec.person_number;
664 g_log_tab(g_log_tab_index).message_text := RPAD(l_error,12) || fnd_message.get;
665 g_skip_person := TRUE ;
666 ELSE
667 g_skip_person := FALSE ;
668 END IF ; -- person existence check
669
670 IF l_person_id IS NOT NULL THEN
671 -- check for Active and Payment ISIR for the person ...
672 l_act_isir_id := NULL ;
673 l_pay_isir_id := NULL ;
674 FOR act_pay_isir_rec IN c_act_pay_isir ( l_fa_base_id )
675 LOOP
676 EXIT WHEN c_act_pay_isir%NOTFOUND ;
677 IF act_pay_isir_rec.active_isir = 'Y' THEN
678 l_act_isir_id := act_pay_isir_rec.isir_id ;
679 END IF ;
680 IF act_pay_isir_rec.payment_isir = 'Y' THEN
681 l_pay_isir_id := act_pay_isir_rec.isir_id ;
682 END IF ;
683 END LOOP ;
684 IF l_act_isir_id IS NULL THEN
685 FND_MESSAGE.SET_NAME('IGF','IGF_AP_ACT_ISIR_NOT_FOUND');
686 g_log_tab_index := g_log_tab_index + 1;
687 g_log_tab(g_log_tab_index).person_number := person_rec.person_number;
688 g_log_tab(g_log_tab_index).message_text := RPAD(l_error,12) || fnd_message.get;
689 g_skip_person := TRUE ;
690 END IF ;
691 IF l_pay_isir_id IS NULL THEN
692 FND_MESSAGE.SET_NAME('IGF','IGF_AP_NO_PAY_ISIR');
693 g_log_tab_index := g_log_tab_index + 1;
694 g_log_tab(g_log_tab_index).person_number := person_rec.person_number;
695 g_log_tab(g_log_tab_index).message_text := RPAD(l_error,12) || fnd_message.get;
696 g_skip_person := TRUE ;
697 END IF ;
698 END IF ;
699 END IF ; -- person already processed check
700
701 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
702 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_lg_ver_imp.main.debug','Completed person validations in Procedure main');
703 END IF;
704
705
706 /* End Of Person level Check */
707 /**************************************************
708 Ver Item Level checks
709 ***************************************************/
710
711 -- Check for person skip flag
712 IF g_skip_person THEN
713 -- person skip flag set....
714 -- if flag set then the person related records aer to be marked as error records and skipped
715 -- update all person records to error status
716 UPDATE igf_ap_li_vern_ints
717 SET import_status_type = 'E'
718 WHERE batch_num = p_batch_num
719 AND person_number = person_rec.person_number ;
720 --COMMIT ;
721 ELSE -- person not to b skipped
722 -- Item level validations ...
723 l_token := person_rec.sar_field_label_code || ' VERINT_ID - ' || TO_CHAR(person_rec.verint_id) ;
724 FND_MESSAGE.SET_NAME('IGF','IGF_AP_PROC_ITM');
725 FND_MESSAGE.SET_TOKEN('ITEM', l_token );
726 g_log_tab_index := g_log_tab_index + 1;
727 g_log_tab(g_log_tab_index).person_number := person_rec.person_number;
728 g_log_tab(g_log_tab_index).message_text := RPAD(null,12) || fnd_message.get;
729
730 -- ver item valid
731 OPEN c_get_sar_num ( g_sys_award_year, person_rec.sar_field_label_code);
732 FETCH c_get_sar_num INTO l_sar_num ;
733 -- if no Ver Item found
734 IF c_get_sar_num%NOTFOUND THEN
735 l_sar_num := NULL ;
736 FND_MESSAGE.SET_NAME('IGF','IGF_AP_INVALID_VERN_ITM');
737 FND_MESSAGE.SET_TOKEN('ITEM', person_rec.sar_field_label_code);
738 g_log_tab_index := g_log_tab_index + 1;
739 g_log_tab(g_log_tab_index).person_number := person_rec.person_number;
740 g_log_tab(g_log_tab_index).message_text := RPAD(l_error,12) || fnd_message.get;
741 g_skip_record := TRUE ;
742 END IF ;
743 CLOSE c_get_sar_num;
744
745 -- ver item duplicate
746 IF l_sar_num is NOT NULL THEN
747 check_dup_ver ( l_sar_num, l_fa_base_id, l_dup_item_found) ;
748 IF l_dup_item_found AND NVL(person_rec.import_record_type,'A') <> 'U' THEN
749 -- log a message for duplicate
750 FND_MESSAGE.SET_NAME('IGF','IGF_AP_VER_ITM_EXIST');
751 FND_MESSAGE.SET_TOKEN('ITEM', person_rec.sar_field_label_code);
752 g_log_tab_index := g_log_tab_index + 1;
753 g_log_tab(g_log_tab_index).person_number := person_rec.person_number;
754 g_log_tab(g_log_tab_index).message_text := RPAD(l_error,12) || fnd_message.get;
755 g_skip_record := TRUE ;
756 END IF ;
757 IF (NOT l_dup_item_found) AND NVL(person_rec.import_record_type,'A') = 'U' THEN
758 -- log a message for duplicate
759 FND_MESSAGE.SET_NAME('IGF','IGF_AP_ORIG_REC_NOT_FOUND');
760 g_log_tab_index := g_log_tab_index + 1;
761 g_log_tab(g_log_tab_index).person_number := person_rec.person_number;
762 g_log_tab(g_log_tab_index).message_text := RPAD(l_error,12) ||fnd_message.get;
763 g_skip_record := TRUE ;
764 END IF ;
765
766 IF l_dup_item_found THEN
767 -- check if corrections have been created for the ver item
768 l_corr_exist := FALSE ;
769 chk_corr_items ( l_pay_isir_id , l_sar_num , l_corr_exist) ;
770 IF l_corr_exist THEN
771 FND_MESSAGE.SET_NAME('IGF','IGF_AP_VER_CORR_GEN');
772 g_log_tab_index := g_log_tab_index + 1;
773 g_log_tab(g_log_tab_index).person_number := person_rec.person_number;
774 g_log_tab(g_log_tab_index).message_text := RPAD(l_error,12) ||fnd_message.get;
775 g_skip_record := TRUE ;
776 END IF ;
777 END IF ; -- end of dup found ...
778 END IF ; -- end of sar null chk
779 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
780 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_lg_ver_imp.main.debug','Completed record validations in Procedure main');
781 END IF;
782
783
784
785 -- all record validations done ...
786 -- now check for skip record flag
787 IF g_skip_record THEN
788 UPDATE igf_ap_li_vern_ints
789 SET import_status_type = 'E'
790 WHERE verint_id = person_rec.verint_id ;
791 COMMIT ;
792 ELSE
793 -- add records
794 IF NVL(person_rec.import_record_type,'A') = 'U' THEN
795 -- record exists
796 -- has to be deleted
797 delete_ver_items ( l_fa_base_id, l_sar_num );
798 END IF; --
799 -- Now add records
800 l_per_item_count := l_per_item_count + 1 ;
801 l_rec_imported := l_rec_imported + 1 ;
802 igf_ap_inst_ver_item_pkg.insert_row (
803 x_rowid => lv_rowid ,
804 x_base_id => l_fa_base_id ,
805 x_udf_vern_item_seq_num => NULL ,
806 x_item_value => TRIM(person_rec.sar_field_value_txt) ,
807 x_waive_flag => NULL ,
808 x_incl_in_tolerance => NULL ,
809 x_isir_map_col => l_sar_num ,
810 x_legacy_record_flag => 'Y' ,
811 x_use_blank_flag => NULL,
812 x_mode => 'R'
813 );
814 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
815 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_lg_ver_imp.main.debug','Inserted Verification record in Procedure main');
816 END IF;
817
818 -- now update the record status
819 IF p_delete_flag = 'Y' THEN
820 DELETE FROM igf_ap_li_vern_ints
821 WHERE verint_id = person_rec.verint_id ;
822 COMMIT ;
823 ELSE
824 UPDATE igf_ap_li_vern_ints
825 SET import_status_type = 'I'
826 WHERE verint_id = person_rec.verint_id ;
827 COMMIT ;
828 END IF ;
829
830 IF g_award_year_status = 'LD' THEN
831 -- get the existing value for the item ....
832 IF person_rec.sar_field_label_code IS NOT NULL THEN
833 lv_cur := DBMS_SQL.OPEN_CURSOR;
834 lv_stmt := 'SELECT ' || person_rec.sar_field_label_code || ' FROM igf_ap_isir_matched WHERE isir_id = :l_isir_id' ;
835
836 DBMS_SQL.PARSE(lv_cur,lv_stmt,2);
837 DBMS_SQL.BIND_VARIABLE(lv_cur, 'l_isir_id', TO_CHAR(l_pay_isir_id));
838
839 DBMS_SQL.DEFINE_COLUMN(lv_cur,1,l_orig_value,30);
840 lv_rows := DBMS_SQL.EXECUTE_AND_FETCH(lv_cur);
841 DBMS_SQL.COLUMN_VALUE(lv_cur,1,l_orig_value);
842 DBMS_SQL.CLOSE_CURSOR(lv_cur);
843 END IF;
844 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
845 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_lg_ver_imp.main.debug','Obtained isir column value in Procedure main');
846 END IF;
847
848
849 -- check the original value with the new doc value
850 IF (l_orig_value = person_rec.sar_field_value_txt) OR
851 (l_orig_value is NULL and person_rec.sar_field_value_txt is NULL) THEN
852 NULL;
853 ELSE
854 l_diff_flag := TRUE ;
855 END IF ;
856
857 IF person_rec.sar_field_value_txt IS NULL THEN
858 l_doc_null_flag := TRUE ;
859 END IF ;
860 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
861 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_lg_ver_imp.main.debug','Set difference and null value flags in Procedure main');
862 END IF;
863
864
865 END IF ; -- award year check ...
866 END IF ; -- skip record check
867
868 END IF ; -- person skip flag check
869
870 -- Reset the Last Person Processed
871 l_last_person_number := person_rec.person_number;
872 END LOOP ; -- person selection loop
873
874 -- now update fa base record for the last person processed ...
875 IF g_award_year_status = 'LD' THEN
876 IF l_per_item_count > 0 THEN
877 OPEN c_fabase(l_last_person_number) ;
878 FETCH c_fabase INTO g_fabase ;
879 CLOSE c_fabase ;
880
881 l_fed_verif_status := g_fabase.fed_verif_status ;
882
883 IF l_fed_verif_status IN ('SELECTED','NOTSELECTED') THEN
884 IF l_doc_null_flag THEN
885 l_new_fed_verif_status := 'WITHOUTDOC' ;
886 FND_MESSAGE.SET_NAME('IGF','IGF_AP_VER_STAT_WITHOUT_DOC');
887 g_log_tab_index := g_log_tab_index + 1;
888 g_log_tab(g_log_tab_index).person_number := l_last_person_number ;
889 g_log_tab(g_log_tab_index).message_text := RPAD(null,12) || fnd_message.get;
890 END IF ;
891 IF NOT l_doc_null_flag AND NOT l_diff_flag THEN
892 l_new_fed_verif_status := 'ACCURATE' ;
893 FND_MESSAGE.SET_NAME('IGF','IGF_AP_VER_STAT_ACCURATE');
894 g_log_tab_index := g_log_tab_index + 1;
895 g_log_tab(g_log_tab_index).person_number := l_last_person_number;
896 g_log_tab(g_log_tab_index).message_text := RPAD(null,12) || fnd_message.get;
897 END IF ;
898 IF NOT l_doc_null_flag AND l_diff_flag THEN
899 l_new_fed_verif_status := 'SELECTED' ;
900 FND_MESSAGE.SET_NAME('IGF','IGF_AP_VER_STAT_SELECTED');
901 g_log_tab_index := g_log_tab_index + 1;
902 g_log_tab(g_log_tab_index).person_number := l_last_person_number;
903 g_log_tab(g_log_tab_index).message_text := RPAD(null,12) || fnd_message.get;
904 END IF ;
905
906 upd_fed_verif_status ( l_new_fed_verif_status) ;
907 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
908 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_lg_ver_imp.main.debug','Federal Verification Status updated for last person in Procedure main');
909 END IF;
910
911 END IF ; -- verification status check ...
912 END IF ; -- counter check ....
913 END IF ; -- award year check ...
914
915 IF l_rec_processed = 0 THEN
916 FND_MESSAGE.SET_NAME('IGF','IGF_AP_AWDYR_STAT_NOT_EXISTS');
917 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
918 ELSE
919 -- CALL THE PRINT LOG PROCESS
920 print_log_process(l_person_number,l_error);
921 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING('IGS','IGS_GE_TOTAL_REC_PROCESSED'),50)|| TO_CHAR(l_rec_processed) );
922 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING('IGS','IGS_GE_TOTAL_REC_FAILED'),50)|| TO_CHAR(l_rec_processed - l_rec_imported));
923 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(FND_MESSAGE.GET_STRING('IGS','IGS_AD_SUCC_IMP_OFR_RESP_REC'),50)|| TO_CHAR(l_rec_imported));
924
925 IF l_rec_imported = 0 THEN
926 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ');
927 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, '-------------------------------------------------------------');
928 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, ' ');
929 FND_MESSAGE.SET_NAME('IGS','IGS_EN_NO_DATA_IMP' );
930 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,FND_MESSAGE.GET);
931 END IF ;
932 END IF ;
933
934 END IF ; -- terminate flag check
935
936 ELSE -- profile check
937 -- error message
938 -- terminate the process .. no further processing
939 FND_MESSAGE.SET_NAME('IGF','IGF_AP_LGCY_PROC_NOT_RUN');
940 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
941 END IF ; -- profile check ends
942
943 EXCEPTION
944 WHEN OTHERS THEN
945 ROLLBACK;
946 RETCODE := 2 ;
947 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
948 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_lg_ver_imp.main.exception','Unhandled Exception :'||SQLERRM);
949 END IF;
950 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP') ;
951 FND_MESSAGE.SET_TOKEN('NAME','IGF_AP_LG_VER_IMP.MAIN') ;
952 errbuf := FND_MESSAGE.GET ;
953 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL ;
954 END main ;
955
956 END igf_ap_lg_ver_imp ;