[Home] [Help]
PACKAGE BODY: APPS.IGF_GR_LI_IMPORT
Source
1 PACKAGE BODY igf_gr_li_import AS
2 /* $Header: IGFGR10B.pls 120.3 2006/01/17 02:45:55 tsailaja ship $ */
3 /*=======================================================================+
4 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | |
8 | DESCRIPTION |
9 | PL/SQL spec for package: IGF_GR_LI_IMPORT |
10 | |
11 | NOTES |
12 | Legacy Pell Origiantion and Disbursement Import Process. Imports the|
13 | Pell Originaton records and its disbursement records from the legacy|
14 | systems to IGS. Validates all the lookup values, checks for foreign |
15 | key refferencing to the awards table, also populates the Pell Batch |
16 | table. |
17 | |
18 | Supports import of Pell Origination and Disbursments records in |
19 | update mode provided key information is not changed. |
20 | |
21 | HISTORY |
22 | psssahni 29-Oct-2004 Bug 3416863 |
23 | Added validation to run the process for |
24 | awards having Ready to Send status only in |
25 | case of COD-XML processing. |
26 | Added validation to check the combination of|
27 | attending and reporting pell id |
28 | svuppala 14-Oct-2004 Bug # 3416936 |
29 | Modified TBH call to addeded field |
30 | Eligible for Additional Unsubsidized Loans |
31 | |
32 | ugummall 20-Apr-2004 Bug 3558751. Added lookup enabled_flag |
33 | check for all the cursors using lookups. |
34 | veramach 10-Dec-2003 Removed cursor c_pell_setup and related |
35 | code |
36 | rasahoo 17-NOV-2003 FA 128 - ISIR update 2004-05 |
37 | added new parameter |
38 | award_fmly_contribution_type to |
39 | igf_ap_fa_base_rec_pkg.update_row |
40 | Changed the cursor c_isir_details |
41 | brajendr 04-Jul-2002 Bug # 2991359 Creation of file |
42 | bkkumar 13-Aug-2003 Bug# 3089841 Added one transaction_num |
43 | validation and changed the c_isir_details |
44 | cursor. |
45 | nsidana 10/31/2003 Multiple FA offices build : Added new fn |
46 | to derive the REP PELL ID. |
47 | gvarapra 14-sep-2004 FA138 - ISIR Enhancements |
48 | Changed arguments in call to |
49 | IGF_AP_FA_BASE_RECORD_PKG. |
50 *=======================================================================*/
51
52 -- Get the details of Pell Origination Interface records
53 CURSOR c_pell_orig_int(
54 cp_batch_num igf_aw_li_pell_ints.batch_num%TYPE,
55 cp_ci_alternate_code igf_aw_li_pell_ints.ci_alternate_code%TYPE,
56 cp_orig_status_code igf_aw_li_pell_ints.orig_status_code%TYPE
57 ) IS
58 SELECT ROWID row_id,
59 batch_num batch_num,
60 TRIM(ci_alternate_code) ci_alternate_code,
61 TRIM(person_number) person_number,
62 TRIM(award_number_txt) award_number_txt,
63 TRIM(origination_id_txt) origination_id_txt,
64 TRIM(import_status_type) import_status_type,
65 TRIM(orig_send_batch_id_txt) orig_send_batch_id_txt,
66 TRIM(transaction_num_txt) transaction_num_txt,
67 efc_amt efc_amt,
68 TRIM(verification_status_code) verification_status_code,
69 secondary_efc_amt secondary_efc_amt,
70 TRIM(secondary_efc_code) secondary_efc_code,
71 pell_award_amt pell_award_amt,
72 TRIM(enrollment_status_flag) enrollment_status_flag,
73 enrollment_date enrollment_date,
74 pell_coa_amt pell_coa_amt,
75 TRIM(academic_calendar_cd) academic_calendar_cd,
76 TRIM(payment_method_code) payment_method_code,
77 TRIM(incrcd_fed_pell_rcp_code) incrcd_fed_pell_rcp_code,
78 TRIM(attending_campus_cd ) attending_campus_cd,
79 TRIM(orig_status_code) orig_status_code,
80 orig_status_date orig_status_date,
81 TRIM(orig_ed_use_flags_txt) orig_ed_use_flags_txt,
82 ft_sch_pell_amt ft_sch_pell_amt,
83 prev_accpt_efc_amt prev_accpt_efc_amt,
84 TRIM(prev_accpt_tran_num_txt) prev_accpt_tran_num_txt,
85 TRIM(prev_accpt_sec_efc_cd) prev_accpt_sec_efc_cd,
86 prev_accpt_coa_amt prev_accpt_coa_amt,
87 TRIM(orig_reject_codes_txt) orig_reject_codes_txt,
88 wk_inst_time_calc_pymt_num wk_inst_time_calc_pymt_num,
89 wk_int_time_prg_def_yr_num wk_int_time_prg_def_yr_num,
90 cr_clk_hrs_prds_sch_yr_num cr_clk_hrs_prds_sch_yr_num,
91 cr_clk_hrs_acad_yr_num cr_clk_hrs_acad_yr_num,
92 TRIM(inst_cross_ref_cd) inst_cross_ref_cd,
93 TRIM(low_tution_fee_cd) low_tution_fee_cd,
94 pending_amt pending_amt,
95 rfms_process_date rfms_process_date,
96 rfms_ack_date rfms_ack_date,
97 TRIM(import_record_type) import_record_type,
98 TRIM(ope_cd) ope_cd,
99 pell_alt_exp_amt pell_alt_exp_amt,
100 atd_entity_id_txt atd_entity_id_txt,
101 rep_entity_id_txt rep_entity_id_txt
102 FROM igf_aw_li_pell_ints pell
103 WHERE batch_num = cp_batch_num
104 AND TRIM(ci_alternate_code) = cp_ci_alternate_code
105 AND TRIM(import_status_type) IN ('U','R')
106 AND NVL (TRIM (orig_status_code), 'x') = NVL (cp_orig_status_code, NVL (TRIM
107 (orig_status_code), 'x'))
108 ORDER BY ci_alternate_code, person_number, award_number_txt;
109
110 -- Get the details of Pell Origination Interface records
111 CURSOR c_pell_disb_int(
112 cp_ci_alternate_code igf_aw_li_pdb_ints.ci_alternate_code%TYPE,
113 cp_person_number igf_aw_li_pdb_ints.person_number%TYPE,
114 cp_award_number_txt igf_aw_li_pdb_ints.award_number_txt%TYPE,
115 cp_origination_id_txt igf_aw_li_pdb_ints.origination_id_txt%TYPE
116 ) IS
117 SELECT ROWID row_id,
118 TRIM(ci_alternate_code) ci_alternate_code,
119 TRIM(person_number) person_number,
120 TRIM(award_number_txt) award_number_txt,
121 TRIM(origination_id_txt) origination_id_txt,
122 disbursement_num disbursement_num,
123 TRIM(disb_ack_act_flag) disb_ack_act_flag,
124 disb_status_date disb_status_date,
125 accpt_disb_date accpt_disb_date,
126 disb_accpt_amt disb_accpt_amt,
127 TRIM(disbursement_sign_flag) disbursement_sign_flag,
128 disb_ytd_amt disb_ytd_amt,
129 pymt_prd_start_date pymt_prd_start_date,
130 accpt_pymt_prd_start_date accpt_pymt_prd_start_date,
131 TRIM(edit_codes_txt) edit_codes_txt,
132 TRIM(disburse_batch_id_txt) disburse_batch_id_txt,
133 disburse_batch_process_date disburse_batch_process_date,
134 disburse_batch_ack_date disburse_batch_ack_date,
135 TRIM(ed_use_flags) ed_use_flags
136 FROM igf_aw_li_pdb_ints
137 WHERE TRIM(ci_alternate_code) = cp_ci_alternate_code
138 AND TRIM(person_number) = cp_person_number
139 AND TRIM(award_number_txt) = cp_award_number_txt
140 AND TRIM(origination_id_txt) = cp_origination_id_txt
141 ORDER BY ci_alternate_code, person_number, award_number_txt, origination_id_txt, disbursement_num;
142
143 -- Get the ISIR details for the context Person(base_id)
144 -- Bug# 3089841 Removed the payment ISIR check
145 CURSOR c_isir_details(
146 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
147 cp_transaction_num igf_ap_isir_matched_all.transaction_num%TYPE
148 ) IS
149 SELECT i.original_ssn,
150 RPAD(i.orig_name_id,2,' ') orig_name_id,
151 i.date_of_birth,
152 i.last_name,
153 i.first_name,
154 i.middle_initial,
155 i.current_ssn,
156 DECODE(f.award_fmly_contribution_type,
157 '2',i.secondary_efc,
158 i.primary_efc) paid_efc
159 FROM igf_ap_isir_matched_all i,
160 igf_ap_fa_base_rec_all f
161 WHERE i.base_id = cp_base_id
162 AND f.base_id = i.base_id
163 AND i.system_record_type = 'ORIGINAL'
164 AND TO_NUMBER(i.transaction_num) = TO_NUMBER(cp_transaction_num);
165
166 -- Declaration of global paramters
167 g_award_id igf_aw_award_all.award_id%TYPE;
168 g_person_id igf_ap_fa_base_rec_all.person_id%TYPE;
169 g_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
170 g_cal_type igf_ap_fa_base_rec_all.ci_cal_type%TYPE;
171 g_seq_number igf_ap_fa_base_rec_all.ci_sequence_number%TYPE;
172 g_sys_award_year igf_ap_batch_aw_map_all.sys_award_year%TYPE;
173 g_awd_yr_status_cd igf_ap_batch_aw_map_all.award_year_status_code%TYPE;
174 g_debug_runtime_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
175 g_disb_pad_str VARCHAR2(10) := ' ';
176 g_tot_rec_processed NUMBER :=0 ;
177 g_tot_rec_imp_warning NUMBER :=0 ;
178 g_tot_rec_imp_error NUMBER :=0 ;
179 g_tot_rec_imp_successful NUMBER :=0 ;
180 g_delete_flag VARCHAR2(1);
181 g_reporting_pell_id VARCHAR2(30):=NULL; -- Multiple FA offices. Global variable to hold the derived Rep Pell ID.
182 g_attending_pell_cd VARCHAR2(30):=NULL; -- Multiple FA offices. Global variable to hold the derived Rep Pell ID.
183 g_atd_entity_id_txt VARCHAR2(30):=NULL;
184
185 PROCEDURE log_parameters(
186 p_alternate_code VARCHAR2,
187 p_batch_number NUMBER,
188 p_del_ind VARCHAR2
189 ) IS
190 /*
191 || Created By : brajendr
192 || Created On : 10-Jul-2003
193 || Purpose : This process log the parameters in the log file
194 || Known limitations, enhancements or remarks :
195 || Change History :
196 || Who When What
197 || (reverse chronological order - newest change first)
198 */
199
200 -- Get the values from the lookups
201 CURSOR c_get_parameters IS
202 SELECT lkups.meaning, lkups.lookup_code
203 FROM igf_lookups_view lkups
204 WHERE lkups.lookup_type = 'IGF_GE_PARAMETERS'
205 AND lkups.lookup_code IN ('AWARD_YEAR','BATCH_NUMBER','DELETE_FLAG','PARAMETER_PASS')
206 AND lkups.enabled_flag = 'Y' ;
207
208 parameter_rec c_get_parameters%ROWTYPE;
209
210 l_award_year_pmpt igf_lookups_view.meaning%TYPE;
211 l_batch_number_pmpt igf_lookups_view.meaning%TYPE;
212 l_delete_flag_pmpt igf_lookups_view.meaning%TYPE;
213 l_para_pass igf_lookups_view.meaning%TYPE;
214
215 BEGIN
216
217 -- Set all the Prompts for the Input Parameters
218 OPEN c_get_parameters;
219 LOOP
220 FETCH c_get_parameters INTO parameter_rec;
221 EXIT WHEN c_get_parameters%NOTFOUND;
222
223 IF (parameter_rec.lookup_code ='AWARD_YEAR') THEN
224 l_award_year_pmpt := TRIM(parameter_rec.meaning);
225
226 ELSIF (parameter_rec.lookup_code ='BATCH_NUMBER') THEN
227 l_batch_number_pmpt := TRIM(parameter_rec.meaning);
228
229 ELSIF (parameter_rec.lookup_code ='DELETE_FLAG') THEN
230 l_delete_flag_pmpt := TRIM(parameter_rec.meaning);
231
232 ELSIF (parameter_rec.lookup_code ='PARAMETER_PASS') THEN
233 l_para_pass := TRIM(parameter_rec.meaning);
234
235 END IF;
236
237 END LOOP;
238 CLOSE c_get_parameters;
239
240
241 fnd_file.put_line(fnd_file.log, ' ');
242 fnd_file.put_line(fnd_file.log, l_para_pass); --------------Parameters Passed--------------
243 fnd_file.put_line(fnd_file.log, ' ');
244
245 fnd_file.put_line(fnd_file.log, RPAD(l_award_year_pmpt,40) || ' : '|| p_alternate_code);
246 fnd_file.put_line(fnd_file.log, RPAD(l_batch_number_pmpt,40) || ' : '|| p_batch_number);
247 fnd_file.put_line(fnd_file.log, RPAD(l_delete_flag_pmpt,40) || ' : '|| igf_ap_gen.get_lookup_meaning('IGF_AP_YES_NO', p_del_ind));
248
249 fnd_file.put_line(fnd_file.log, ' ');
250 fnd_file.put_line(fnd_file.log,RPAD('-',55,'-'));
251
252 EXCEPTION
253 WHEN OTHERS THEN
254 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
255 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'igf.plsql.igf_gr_li_import.log_parameters.exception', SQLERRM );
256 END IF;
257 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
258 fnd_message.set_token('NAME','IGF_GR_LI_IMPORT.LOG_PARAMETERS');
259 igs_ge_msg_stack.add;
260
261 END log_parameters;
262
263
264 FUNCTION derive_rep_pell_id(p_pell_orig_int c_pell_orig_int%ROWTYPE) RETURN VARCHAR2
265 /*
266 || Created By : nsidana
267 || Created On : 11/4/2003
268 || Purpose : This fn derives the reporting pell ID for an Pell interface record based on OPE CD / Attend campus ID / Base ID.
269 || Known limitations, enhancements or remarks :
270 || Change History :
271 || Who When What
272 || (reverse chronological order - newest change first)
273 */
274 AS
275 l_rep_pell VARCHAR2(30);
276 l_debug_str fnd_log_messages.message_text%TYPE := 'IGFGR10B.pls Function derive_rep_pell_id() :: ';
277
278
279 BEGIN
280 IF (p_pell_orig_int.ope_cd IS NOT NULL)
281 THEN
282 l_rep_pell := igf_gr_gen.get_rep_pell_from_ope(g_cal_type,g_seq_number,p_pell_orig_int.ope_cd);
283 IF (l_rep_pell IS NULL)
284 THEN
285 IF (p_pell_orig_int.attending_campus_cd IS NOT NULL)
286 THEN
287 l_rep_pell := igf_gr_gen.get_rep_pell_from_att(g_cal_type,g_seq_number,p_pell_orig_int.attending_campus_cd);
288 IF (l_rep_pell IS NULL)
289 THEN
290 l_rep_pell := igf_gr_gen.get_rep_pell_from_base(g_cal_type,g_seq_number,g_base_id);
291 IF (l_rep_pell IS NOT NULL)
292 THEN
293 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
294 l_debug_str := l_debug_str || ' Derived reporting pell Id from Base ID '|| l_rep_pell;
295 END IF;
296 RETURN l_rep_pell;
297 ELSE
298 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
299 l_debug_str := l_debug_str || ' Unable to derive the rep_pell ID from ope_cd, attending campus ID and base ID.';
300 END IF;
301 RETURN null;
302 END IF;
303 ELSE
304 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
305 l_debug_str := l_debug_str || ' Derived reporting pell Id from Attending campus ID '||l_rep_pell;
306 END IF;
307 RETURN l_rep_pell;
308 END IF;
309 ELSE
310 l_rep_pell := igf_gr_gen.get_rep_pell_from_base(g_cal_type,g_seq_number,g_base_id);
311 IF (l_rep_pell IS NOT NULL)
312 THEN
313 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
314 l_debug_str := l_debug_str || ' Derived Reporting Pell Id from the base ID '||l_rep_pell;
315 END IF;
316 RETURN l_rep_pell;
317 ELSE
318 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
319 l_debug_str := l_debug_str || ' Unable to derive the rep_pell ID from ope_cd, attending campus ID and base ID.';
320 END IF;
321 RETURN null;
322 END IF;
323 END IF;
324 ELSE
325 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
326 l_debug_str := l_debug_str || 'Derived reporting pell Id from the OPE CD '||l_rep_pell;
327 END IF;
328 RETURN l_rep_pell;
329 END IF;
330 ELSE
331 -- ope_cd is null...
332 IF (p_pell_orig_int.attending_campus_cd IS NOT NULL)
333 THEN
334 l_rep_pell := igf_gr_gen.get_rep_pell_from_att(g_cal_type,g_seq_number,p_pell_orig_int.attending_campus_cd);
335 IF (l_rep_pell IS NULL)
336 THEN
337 l_rep_pell := igf_gr_gen.get_rep_pell_from_base(g_cal_type,g_seq_number,g_base_id);
338 IF (l_rep_pell IS NOT NULL)
339 THEN
340 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
341 l_debug_str := l_debug_str || 'Derived repoting pell ID from Base ID' || l_rep_pell;
342 END IF;
343 RETURN l_rep_pell;
344 ELSE
345 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
346 l_debug_str := l_debug_str || ' Unable to derive the rep_pell ID from ope_cd, attending campus ID and base ID.';
347 END IF;
348 RETURN null;
349 END IF;
350 ELSE
351 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
352 l_debug_str := l_debug_str || 'Derived reporting pell ID from the Attend campus ID '||l_rep_pell;
353 END IF;
354 RETURN l_rep_pell;
355 END IF;
356 ELSE
357 l_rep_pell := igf_gr_gen.get_rep_pell_from_base(g_cal_type,g_seq_number,g_base_id);
358 IF (l_rep_pell IS NOT NULL)
359 THEN
360 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
361 l_debug_str := l_debug_str || 'Derived repoting pell ID from Base Id '||l_rep_pell;
362 END IF;
363 RETURN l_rep_pell;
364 ELSE
365 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
366 l_debug_str := l_debug_str || ' Unable to derive the rep_pell ID from ope_cd, attending campus ID and base ID.';
367 END IF;
368 RETURN null;
369 END IF;
370 END IF;
371 END IF;
372 END derive_rep_pell_id;
373
374
375 FUNCTION update_fa_base_data(
376 p_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
377 p_coa_pell igf_ap_fa_base_rec_all.coa_pell%TYPE,
378 p_pell_alt_expense igf_ap_fa_base_rec_all.pell_alt_expense%TYPE
379 ) RETURN VARCHAR2 AS
380
381 /*
382 || Created By : brajendr
383 || Created On : 10-Jul-2003
384 || Purpose : Updates the FA Base record with the Pel COA and Pell Alternate expenses
385 || Known limitations, enhancements or remarks :
386 || Change History :
387 || Who When What
388 || (reverse chronological order - newest change first)
389 ||
390 || rasahoo 17-NOV-2003 FA 128 - ISIR update 2004-05
391 || added new parameter award_fmly_contribution_type to
392 || igf_ap_fa_base_rec_pkg.update_row
393 || ugummall 25-SEP-2003 FA 126 - Multiple FA Offices
394 || added new parameter assoc_org_num to
395 || igf_ap_fa_base_rec_pkg.update_row call
396 */
397
398 -- Get base record deails
399 CURSOR c_get_base_rec_dtls(
400 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
401 ) IS
402 SELECT ROWID row_id, base.*
403 FROM igf_ap_fa_base_rec_all base
404 WHERE base.base_id = cp_base_id;
405
406 lc_get_base_rec_dtls c_get_base_rec_dtls%ROWTYPE;
407
408 l_debug_str fnd_log_messages.message_text%TYPE := 'IGFGR10B.pls Procedure update_fa_base_data :: ';
409
410 BEGIN
411
412 -- Get the details of the Base record to update the PELL COA and PELL ALT Expenses
413 lc_get_base_rec_dtls := NULL;
414 OPEN c_get_base_rec_dtls(p_base_id);
415 FETCH c_get_base_rec_dtls INTO lc_get_base_rec_dtls;
416 CLOSE c_get_base_rec_dtls;
417
418 IF (lc_get_base_rec_dtls.base_id IS NULL) THEN
419 RETURN 'E';
420 END IF;
421
422
423 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
424 l_debug_str := l_debug_str || ' Before updating FA Base : Pell COA ' || p_coa_pell || ' Pell Alt Exp : '|| p_pell_alt_expense;
425 END IF;
426
427 -- update the fa base record
428 igf_ap_fa_base_rec_pkg.update_row(
429 x_Mode => 'R' ,
430 x_rowid => lc_get_base_rec_dtls.row_id ,
431 x_base_id => lc_get_base_rec_dtls.base_id ,
432 x_ci_cal_type => lc_get_base_rec_dtls.ci_cal_type ,
433 x_person_id => lc_get_base_rec_dtls.person_id ,
434 x_ci_sequence_number => lc_get_base_rec_dtls.ci_sequence_number ,
435 x_org_id => lc_get_base_rec_dtls.org_id ,
436 x_coa_pending => lc_get_base_rec_dtls.coa_pending ,
437 x_verification_process_run => lc_get_base_rec_dtls.verification_process_run ,
438 x_inst_verif_status_date => lc_get_base_rec_dtls.inst_verif_status_date ,
439 x_manual_verif_flag => lc_get_base_rec_dtls.manual_verif_flag ,
440 x_fed_verif_status => lc_get_base_rec_dtls.fed_verif_status ,
441 x_fed_verif_status_date => lc_get_base_rec_dtls.fed_verif_status_date ,
442 x_inst_verif_status => lc_get_base_rec_dtls.inst_verif_status ,
443 x_nslds_eligible => lc_get_base_rec_dtls.nslds_eligible ,
444 x_ede_correction_batch_id => lc_get_base_rec_dtls.ede_correction_batch_id ,
445 x_fa_process_status_date => lc_get_base_rec_dtls.fa_process_status_date ,
446 x_isir_corr_status => lc_get_base_rec_dtls.isir_corr_status ,
447 x_isir_corr_status_date => lc_get_base_rec_dtls.isir_corr_status_date ,
448 x_isir_status => lc_get_base_rec_dtls.isir_status ,
449 x_isir_status_date => lc_get_base_rec_dtls.isir_status_date ,
450 x_coa_code_f => lc_get_base_rec_dtls.coa_code_f ,
451 x_coa_code_i => lc_get_base_rec_dtls.coa_code_i ,
452 x_coa_f => lc_get_base_rec_dtls.coa_f ,
453 x_coa_i => lc_get_base_rec_dtls.coa_i ,
454 x_disbursement_hold => lc_get_base_rec_dtls.disbursement_hold ,
455 x_fa_process_status => lc_get_base_rec_dtls.fa_process_status ,
456 x_notification_status => lc_get_base_rec_dtls.notification_status ,
457 x_notification_status_date => lc_get_base_rec_dtls.notification_status_date ,
458 x_packaging_status => lc_get_base_rec_dtls.packaging_status,
459 x_packaging_status_date => lc_get_base_rec_dtls.packaging_status_date,
460 x_total_package_accepted => lc_get_base_rec_dtls.total_package_accepted ,
461 x_total_package_offered => lc_get_base_rec_dtls.total_package_offered ,
462 x_admstruct_id => lc_get_base_rec_dtls.admstruct_id ,
463 x_admsegment_1 => lc_get_base_rec_dtls.admsegment_1 ,
464 x_admsegment_2 => lc_get_base_rec_dtls.admsegment_2 ,
465 x_admsegment_3 => lc_get_base_rec_dtls.admsegment_3 ,
466 x_admsegment_4 => lc_get_base_rec_dtls.admsegment_4 ,
467 x_admsegment_5 => lc_get_base_rec_dtls.admsegment_5 ,
468 x_admsegment_6 => lc_get_base_rec_dtls.admsegment_6 ,
469 x_admsegment_7 => lc_get_base_rec_dtls.admsegment_7 ,
470 x_admsegment_8 => lc_get_base_rec_dtls.admsegment_8 ,
471 x_admsegment_9 => lc_get_base_rec_dtls.admsegment_9 ,
472 x_admsegment_10 => lc_get_base_rec_dtls.admsegment_10 ,
473 x_admsegment_11 => lc_get_base_rec_dtls.admsegment_11 ,
474 x_admsegment_12 => lc_get_base_rec_dtls.admsegment_12 ,
475 x_admsegment_13 => lc_get_base_rec_dtls.admsegment_13 ,
476 x_admsegment_14 => lc_get_base_rec_dtls.admsegment_14 ,
477 x_admsegment_15 => lc_get_base_rec_dtls.admsegment_15 ,
478 x_admsegment_16 => lc_get_base_rec_dtls.admsegment_16 ,
479 x_admsegment_17 => lc_get_base_rec_dtls.admsegment_17 ,
480 x_admsegment_18 => lc_get_base_rec_dtls.admsegment_18 ,
481 x_admsegment_19 => lc_get_base_rec_dtls.admsegment_19 ,
482 x_admsegment_20 => lc_get_base_rec_dtls.admsegment_20 ,
483 x_packstruct_id => lc_get_base_rec_dtls.packstruct_id ,
484 x_packsegment_1 => lc_get_base_rec_dtls.packsegment_1 ,
485 x_packsegment_2 => lc_get_base_rec_dtls.packsegment_2 ,
486 x_packsegment_3 => lc_get_base_rec_dtls.packsegment_3 ,
487 x_packsegment_4 => lc_get_base_rec_dtls.packsegment_4 ,
488 x_packsegment_5 => lc_get_base_rec_dtls.packsegment_5 ,
489 x_packsegment_6 => lc_get_base_rec_dtls.packsegment_6 ,
490 x_packsegment_7 => lc_get_base_rec_dtls.packsegment_7 ,
491 x_packsegment_8 => lc_get_base_rec_dtls.packsegment_8 ,
492 x_packsegment_9 => lc_get_base_rec_dtls.packsegment_9 ,
493 x_packsegment_10 => lc_get_base_rec_dtls.packsegment_10 ,
494 x_packsegment_11 => lc_get_base_rec_dtls.packsegment_11 ,
495 x_packsegment_12 => lc_get_base_rec_dtls.packsegment_12 ,
496 x_packsegment_13 => lc_get_base_rec_dtls.packsegment_13 ,
497 x_packsegment_14 => lc_get_base_rec_dtls.packsegment_14 ,
498 x_packsegment_15 => lc_get_base_rec_dtls.packsegment_15 ,
499 x_packsegment_16 => lc_get_base_rec_dtls.packsegment_16 ,
500 x_packsegment_17 => lc_get_base_rec_dtls.packsegment_17 ,
501 x_packsegment_18 => lc_get_base_rec_dtls.packsegment_18 ,
502 x_packsegment_19 => lc_get_base_rec_dtls.packsegment_19 ,
503 x_packsegment_20 => lc_get_base_rec_dtls.packsegment_20 ,
504 x_miscstruct_id => lc_get_base_rec_dtls.miscstruct_id ,
505 x_miscsegment_1 => lc_get_base_rec_dtls.miscsegment_1 ,
506 x_miscsegment_2 => lc_get_base_rec_dtls.miscsegment_2 ,
507 x_miscsegment_3 => lc_get_base_rec_dtls.miscsegment_3 ,
508 x_miscsegment_4 => lc_get_base_rec_dtls.miscsegment_4 ,
509 x_miscsegment_5 => lc_get_base_rec_dtls.miscsegment_5 ,
510 x_miscsegment_6 => lc_get_base_rec_dtls.miscsegment_6 ,
511 x_miscsegment_7 => lc_get_base_rec_dtls.miscsegment_7 ,
512 x_miscsegment_8 => lc_get_base_rec_dtls.miscsegment_8 ,
513 x_miscsegment_9 => lc_get_base_rec_dtls.miscsegment_9 ,
514 x_miscsegment_10 => lc_get_base_rec_dtls.miscsegment_10 ,
515 x_miscsegment_11 => lc_get_base_rec_dtls.miscsegment_11 ,
516 x_miscsegment_12 => lc_get_base_rec_dtls.miscsegment_12 ,
517 x_miscsegment_13 => lc_get_base_rec_dtls.miscsegment_13 ,
518 x_miscsegment_14 => lc_get_base_rec_dtls.miscsegment_14 ,
519 x_miscsegment_15 => lc_get_base_rec_dtls.miscsegment_15 ,
520 x_miscsegment_16 => lc_get_base_rec_dtls.miscsegment_16 ,
521 x_miscsegment_17 => lc_get_base_rec_dtls.miscsegment_17 ,
522 x_miscsegment_18 => lc_get_base_rec_dtls.miscsegment_18 ,
523 x_miscsegment_19 => lc_get_base_rec_dtls.miscsegment_19 ,
524 x_miscsegment_20 => lc_get_base_rec_dtls.miscsegment_20 ,
525 x_prof_judgement_flg => lc_get_base_rec_dtls.prof_judgement_flg ,
526 x_nslds_data_override_flg => lc_get_base_rec_dtls.nslds_data_override_flg ,
527 x_target_group => lc_get_base_rec_dtls.target_group ,
528 x_coa_fixed => lc_get_base_rec_dtls.coa_fixed ,
529 x_coa_pell => p_coa_pell ,
530 x_profile_status => lc_get_base_rec_dtls.profile_status ,
531 x_profile_status_date => lc_get_base_rec_dtls.profile_status_date ,
532 x_profile_fc => lc_get_base_rec_dtls.profile_fc ,
533 x_manual_disb_hold => lc_get_base_rec_dtls.manual_disb_hold,
534 x_pell_alt_expense => p_pell_alt_expense,
535 x_assoc_org_num => lc_get_base_rec_dtls.assoc_org_num,
536 x_award_fmly_contribution_type => lc_get_base_rec_dtls.award_fmly_contribution_type,
537 x_isir_locked_by => lc_get_base_rec_dtls.isir_locked_by,
538 x_adnl_unsub_loan_elig_flag => lc_get_base_rec_dtls.adnl_unsub_loan_elig_flag,
539 x_lock_awd_flag => lc_get_base_rec_dtls.lock_awd_flag,
540 x_lock_coa_flag => lc_get_base_rec_dtls.lock_coa_flag
541
542 );
543
544 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
545 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_gr_li_import.update_fa_base_data.debug', l_debug_str || ' Sucssfully updated ');
546 l_debug_str := NULL;
547 END IF;
548
549 RETURN 'S';
550
551 EXCEPTION
552 WHEN OTHERS THEN
553
554 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
555 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'igf.plsql.igf_gr_li_import.update_fa_base_data.exception', l_debug_str || SQLERRM );
556 END IF;
557 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
558 fnd_message.set_token('NAME','IGF_GR_LI_IMPORT.UPDATE_FA_BASE_DATA');
559 igs_ge_msg_stack.add;
560 RETURN 'E';
561
562 END update_fa_base_data;
563
564
565 FUNCTION delete_existing_pell_rec(
566 p_origination_id igf_gr_rfms_all.origination_id%TYPE,
567 p_cal_type igf_gr_rfms_all.ci_cal_type%TYPE,
568 p_seq_number igf_gr_rfms_all.ci_sequence_number%TYPE
569 ) RETURN VARCHAR2 AS
570
571 /*
572 || Created By : brajendr
573 || Created On : 10-Jul-2003
574 || Purpose : Deletes the exitsing pell legacy records from the production tables
575 || Known limitations, enhancements or remarks :
576 || Change History :
577 || Who When What
578 || (reverse chronological order - newest change first)
579 */
580
581 -- Check whether is there any pell origination record present for the context information
582 CURSOR c_chk_pell_orig(
583 cp_origination_id igf_gr_rfms_all.origination_id%TYPE,
584 cp_cal_type igf_gr_rfms_all.ci_cal_type%TYPE,
585 cp_seq_number igf_gr_rfms_all.ci_sequence_number%TYPE
586 ) IS
587 SELECT ROWID row_id, rfms.rfmb_id
588 FROM igf_gr_rfms_all rfms
589 WHERE rfms.origination_id = cp_origination_id
590 AND rfms.ci_cal_type = cp_cal_type
591 AND rfms.ci_sequence_number = cp_seq_number
592 AND NVL(rfms.legacy_record_flag,'N') = 'Y';
593
594 lc_chk_pell_orig c_chk_pell_orig%ROWTYPE;
595
596 -- Get the details of
597 CURSOR c_get_pell_disb(
598 cp_origination_id igf_gr_rfms_all.origination_id%TYPE
599 ) IS
600 SELECT ROWID row_id, pdb.rfmd_id, pdb.rfmb_id
601 FROM igf_gr_rfms_disb_all pdb
602 WHERE pdb.origination_id = cp_origination_id;
603
604 l_return_val VARCHAR2(1) := 'E';
605 l_debug_str fnd_log_messages.message_text%TYPE := 'IGFGR10B.pls Procedure delete_existing_pell_rec :: ';
606
607 BEGIN
608
609 -- check whether Pell Origination record is present in the system with the legacy flag is set to 'Y'
610 lc_chk_pell_orig := NULL;
611 OPEN c_chk_pell_orig( p_origination_id, p_cal_type, p_seq_number);
612 FETCH c_chk_pell_orig INTO lc_chk_pell_orig;
613 CLOSE c_chk_pell_orig;
614
615 IF (lc_chk_pell_orig.row_id IS NOT NULL) THEN
616
617 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
618 l_debug_str := l_debug_str || ' Pell Orig : ' || p_origination_id || ', Looping Pell disb ';
619 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_gr_li_import.delete_existing_pell_rec.debug', l_debug_str);
620 l_debug_str := NULL;
621 END IF;
622
623 BEGIN
624
625 -- Loop for all Pell Disbursement records for the given Pell record
626 FOR lc_get_pell_disb IN c_get_pell_disb(p_origination_id) LOOP
627
628 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
629 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_gr_li_import.delete_existing_pell_rec.debug', ' Deleting Pell Disb : ' || lc_get_pell_disb.rfmd_id);
630 END IF;
631
632 igf_gr_rfms_disb_pkg.delete_row(lc_get_pell_disb.row_id);
633 END LOOP;
634
635 -- Delete Pell Origination Record
636 igf_gr_rfms_pkg.delete_row(lc_chk_pell_orig.row_id);
637
638 -- After complete deletion set return status as successful
639 l_return_val := 'S';
640
641 EXCEPTION
642 WHEN OTHERS THEN
643 l_return_val := 'E';
644 END;
645
646 END IF;
647
648 RETURN l_return_val;
649
650 EXCEPTION
651 WHEN OTHERS THEN
652
653 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
654 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'igf.plsql.igf_gr_li_import.delete_existing_pell_rec.exception', l_debug_str || SQLERRM );
655 END IF;
656 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
657 fnd_message.set_token('NAME','IGF_GR_LI_IMPORT.DELETE_EXISTING_PELL_REC');
658 igs_ge_msg_stack.add;
659 RETURN 'E';
660
661 END delete_existing_pell_rec;
662
663
664 FUNCTION create_pell_disb_batch(
665 p_pell_disb_int c_pell_disb_int%ROWTYPE,
666 p_ope_cd igf_aw_li_pell_ints.ope_cd%TYPE
667 ) RETURN NUMBER AS
668 /*
669 || Created By : brajendr
670 || Created On : 19-Jun-2003
671 || Purpose : Creates the Pell Disbursement Batch record in the batch table
672 || Known limitations, enhancements or remarks :
673 || Change History :
674 || Who When What
675 || (reverse chronological order - newest change first)
676 */
677
678 -- Get the details of duplicate batch id
679 CURSOR c_chk_dup_batch_dtls(
680 cp_batch_id igf_gr_rfms_batch_all.batch_id%TYPE
681 ) IS
682 SELECT pb.rfmb_id
683 FROM igf_gr_rfms_batch_all pb
684 WHERE pb.batch_id = cp_batch_id;
685
686 lc_chk_dup_batch_dtls c_chk_dup_batch_dtls%ROWTYPE;
687
688 l_row_id ROWID;
689 l_rfmb_id igf_gr_rfms_batch_all.rfmb_id%TYPE;
690 l_batch_id igf_gr_rfms_batch_all.batch_id%TYPE;
691 l_rfms_ack_batch_id igf_gr_rfms_batch_all.rfms_ack_batch_id%TYPE;
692 l_data_rec_length igf_gr_rfms_batch_all.data_rec_length%TYPE;
693
694 BEGIN
695
696 -- Check if duplicate batch records exists in the system, if present then return the existing batch id
697 OPEN c_chk_dup_batch_dtls(p_pell_disb_int.disburse_batch_id_txt);
698 FETCH c_chk_dup_batch_dtls INTO lc_chk_dup_batch_dtls;
699 CLOSE c_chk_dup_batch_dtls;
700
701 IF (lc_chk_dup_batch_dtls.rfmb_id IS NOT NULL) THEN
702 RETURN lc_chk_dup_batch_dtls.rfmb_id;
703 END IF;
704
705
706 -- Ack Batch id is needed only if ack data is not null.
707 IF (p_pell_disb_int.disburse_batch_ack_date IS NOT NULL) THEN
708 l_rfms_ack_batch_id := p_pell_disb_int.disburse_batch_id_txt;
709 ELSE
710 l_rfms_ack_batch_id := NULL;
711 END IF;
712
713
714 -- Create Pell Batch Record
715 l_row_id := NULL;
716 l_rfmb_id := -1;
717 igf_gr_rfms_batch_pkg.insert_row(
718 x_rowid => l_row_id,
719 x_rfmb_id => l_rfmb_id,
720 x_batch_id => p_pell_disb_int.disburse_batch_id_txt,
721 x_data_rec_length => 100,
722 x_ope_id => p_ope_cd,
723 x_software_providor => NULL,
724 x_rfms_process_dt => p_pell_disb_int.disburse_batch_process_date,
725 x_rfms_ack_dt => p_pell_disb_int.disburse_batch_ack_date,
726 x_rfms_ack_batch_id => l_rfms_ack_batch_id,
727 x_reject_reason => NULL,
728 x_mode => 'R'
729 );
730
731 RETURN l_rfmb_id;
732
733 EXCEPTION
734 WHEN OTHERS THEN
735 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
736 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'igf.plsql.igf_gr_li_import.create_pell_disb_batch.exception', SQLERRM );
737 END IF;
738 fnd_message.set_name('IGF','IGF_GR_LI_BDINSRT_FAIL');
739 fnd_message.set_token('BATCH_NUM',p_pell_disb_int.disburse_batch_id_txt);
740 fnd_message.set_token('ORIG_ID',p_pell_disb_int.origination_id_txt);
741 fnd_message.set_token('DISB_NUM',p_pell_disb_int.disbursement_num);
742 igs_ge_msg_stack.add;
743 RETURN -1;
744
745 END create_pell_disb_batch;
746
747
748 FUNCTION create_pell_disb(
749 p_pell_disb_int c_pell_disb_int%ROWTYPE,
750 p_rfmb_id igf_gr_rfms_batch_all.rfmb_id%TYPE,
751 p_disb_date igf_aw_awd_disb_all.disb_date%TYPE,
752 p_disb_amt igf_aw_awd_disb_all.disb_net_amt%TYPE
753 ) RETURN BOOLEAN AS
754 /*
755 || Created By : brajendr
756 || Created On : 19-Jun-2003
757 || Purpose : Creats the Pell Disbursement records for context Pell Origination record
758 || Known limitations, enhancements or remarks :
759 || Change History :
760 || Who When What
761 || (reverse chronological order - newest change first)
762 */
763
764 l_row_id ROWID;
765 l_rfmd_id igf_gr_rfms_disb.rfmd_id%TYPE;
766 l_db_cr_flag igf_gr_rfms_disb.db_cr_flag%TYPE;
767
768 BEGIN
769
770 -- Set the dibursement Sign Flag based on the Disb Accepted amount
771 IF (p_pell_disb_int.disb_accpt_amt >= 0) THEN
772 l_db_cr_flag := 'P';
773 ELSE
774 l_db_cr_flag := 'N';
775 END IF;
776
777
778 -- Create Pell Origination Record in the production table
779 l_row_id := NULL;
780 l_rfmd_id := NULL;
781 igf_gr_rfms_disb_pkg.insert_row(
782 x_mode => 'R',
783 x_rowid => l_row_id,
784 x_rfmd_id => l_rfmd_id,
785 x_origination_id => p_pell_disb_int.origination_id_txt,
786 x_disb_ref_num => p_pell_disb_int.disbursement_num,
787 x_disb_dt => p_disb_date,
788 x_disb_amt => NVL(p_pell_disb_int.disb_accpt_amt,p_disb_amt), -- If the disb ack status is R or N then, get the amt from awd disb
789 x_db_cr_flag => l_db_cr_flag,
790 x_disb_ack_act_status => p_pell_disb_int.disb_ack_act_flag,
791 x_disb_status_dt => p_pell_disb_int.disb_status_date,
792 x_accpt_disb_dt => p_pell_disb_int.accpt_disb_date,
793 x_disb_accpt_amt => p_pell_disb_int.disb_accpt_amt,
794 x_accpt_db_cr_flag => p_pell_disb_int.disbursement_sign_flag,
795 x_disb_ytd_amt => p_pell_disb_int.disb_ytd_amt,
796 x_pymt_prd_start_dt => p_pell_disb_int.pymt_prd_start_date,
797 x_accpt_pymt_prd_start_dt => p_pell_disb_int.accpt_pymt_prd_start_date,
798 x_edit_code => p_pell_disb_int.edit_codes_txt,
799 x_rfmb_id => p_rfmb_id,
800 x_ed_use_flags => p_pell_disb_int.ed_use_flags
801 );
802
803 UPDATE igf_aw_li_pdb_ints
804 SET last_updated_by = fnd_global.user_id,
805 last_update_date = SYSDATE,
806 last_update_login = fnd_global.login_id
807 WHERE ci_alternate_code = p_pell_disb_int.ci_alternate_code
808 AND person_number = p_pell_disb_int.person_number
809 AND award_number_txt = p_pell_disb_int.award_number_txt
810 AND origination_id_txt = p_pell_disb_int.origination_id_txt
811 AND disbursement_num = p_pell_disb_int.disbursement_num;
812
813 RETURN TRUE;
814
815 EXCEPTION
816 WHEN OTHERS THEN
817 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
818 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'igf.plsql.igf_gr_li_import.create_pell_disb.exception', SQLERRM );
819 END IF;
820 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
821 fnd_message.set_token('NAME','IGF_GR_LI_IMPORT.CREATE_PELL_DISB');
822 igs_ge_msg_stack.add;
823 RETURN FALSE;
824
825 END create_pell_disb;
826
827
828 FUNCTION import_pell_disb(
829 p_pell_disb_int c_pell_disb_int%ROWTYPE,
830 p_ope_cd igf_aw_li_pell_ints.ope_cd%TYPE
831 ) RETURN VARCHAR2 AS
832 /*
833 || Created By : brajendr
834 || Created On : 19-Jun-2003
835 || Purpose : Import Pell Disbursement records from the legacy interface table to the production tables
836 || Known limitations, enhancements or remarks :
837 || Change History :
838 || Who When What
839 || (reverse chronological order - newest change first)
840 */
841
842 -- Get the Award details for the Context pell
843 CURSOR c_awd_disb(
844 cp_award_id igf_aw_award_all.award_id%TYPE,
845 cp_disb_num igf_aw_awd_disb_all.disb_num%TYPE
846 ) IS
847 SELECT disb.disb_date, disb.disb_gross_amt, disb.disb_net_amt
848 FROM igf_aw_awd_disb_all disb
849 WHERE disb.award_id = cp_award_id
850 AND disb.disb_num = cp_disb_num
851 AND disb.trans_type = 'A';
852
853 lc_awd_disb c_awd_disb%ROWTYPE;
854
855 l_rfmb_id igf_gr_rfms_batch_all.rfmb_id%TYPE;
856 l_debug_str fnd_log_messages.message_text%TYPE := 'IGFGR10B.pls Function import_pell_disb :: ';
857 l_disb_import_status VARCHAR2(1) := 'I';
858 l_disb_batch_id VARCHAR2(30);
859
860 BEGIN
861
862 -- Check whether the disbursement record is present in the Award disbursement table.
863 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
864 l_debug_str := l_debug_str || ', Checking Awd disb with Disb Num : ' || p_pell_disb_int.disbursement_num;
865 END IF;
866
867 OPEN c_awd_disb(g_award_id, p_pell_disb_int.disbursement_num);
868 FETCH c_awd_disb INTO lc_awd_disb;
869 IF (c_awd_disb%NOTFOUND) THEN
870 fnd_message.set_name('IGF','IGF_GR_LI_DNUM_INVALID');
871 fnd_file.put_line(fnd_file.log, g_disb_pad_str || fnd_message.get);
872 CLOSE c_awd_disb;
873 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
874 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_gr_li_import.import_pell_disb.debug', l_debug_str);
875 l_debug_str := NULL;
876 END IF;
877 l_disb_import_status := 'E';
878 RETURN l_disb_import_status;
879 END IF;
880 CLOSE c_awd_disb;
881
882
883 -- Validate Acknowledgement Status
884 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
885 l_debug_str := l_debug_str || ', Acknowledgement Status : ' || p_pell_disb_int.disb_ack_act_flag;
886 END IF;
887
888 IF (p_pell_disb_int.disb_ack_act_flag IS NOT NULL) AND
889 ( (igf_ap_gen.get_aw_lookup_meaning('IGF_GR_ORIG_STATUS', p_pell_disb_int.disb_ack_act_flag, g_sys_award_year) IS NULL) OR
890 (p_pell_disb_int.disb_ack_act_flag IN ('S','D')) )
891 THEN
892
893 fnd_message.set_name('IGF','IGF_AP_INV_FLD_VAL');
894 fnd_message.set_token('FIELD', 'DISB_ACK_ACT_FLAG');
895 fnd_file.put_line(fnd_file.log, g_disb_pad_str || fnd_message.get);
896 l_disb_import_status := 'E';
897 END IF;
898
899
900 -- Validate Disbursement Batch ID
901 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
902 l_debug_str := l_debug_str || ', Disbursement Batch ID : ' || p_pell_disb_int.disburse_batch_id_txt;
903 END IF;
904
905 l_disb_batch_id := '#D' || -- Batch Code
906 igf_gr_gen.get_cycle_year(g_cal_type, g_seq_number) || -- Cycle year
907 g_reporting_pell_id; --pell_setup.rep_pell_id; -- Reporting PELL ID
908
909 IF ( SUBSTR(p_pell_disb_int.disburse_batch_id_txt, 0, 12) <> l_disb_batch_id ) OR
910 ( (p_pell_disb_int.disb_ack_act_flag IN ('R','N')) AND
911 (p_pell_disb_int.disburse_batch_id_txt IS NOT NULL) )
912 THEN
913 fnd_message.set_name('IGF','IGF_GR_LI_DBTH_INVALID');
914 fnd_message.set_token('DISB_BATCH_ID',p_pell_disb_int.disburse_batch_id_txt);
915 fnd_file.put_line(fnd_file.log, g_disb_pad_str || fnd_message.get);
916 l_disb_import_status := 'E';
917 END IF;
918
919
920 -- Validate Action Code status date
921 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
922 l_debug_str := l_debug_str || ', Action Code status date : ' || p_pell_disb_int.disb_status_date;
923 END IF;
924
925 IF ( (p_pell_disb_int.disb_ack_act_flag IN ('N','R')) AND
926 (p_pell_disb_int.disb_status_date IS NOT NULL) )
927 THEN
928 fnd_message.set_name('IGF','IGF_GR_LI_DBSD_INVALID_1');
929 fnd_message.set_token('DISB_STAT_DT',p_pell_disb_int.disb_status_date);
930 fnd_file.put_line(fnd_file.log, g_disb_pad_str || fnd_message.get);
931 l_disb_import_status := 'E';
932 ELSIF ( (p_pell_disb_int.disb_ack_act_flag IN ('A','C','E')) AND
933 (p_pell_disb_int.disb_status_date IS NULL) )
934 THEN
935 fnd_message.set_name('IGF','IGF_GR_LI_DBSD_INVALID_2');
936 fnd_file.put_line(fnd_file.log, g_disb_pad_str || fnd_message.get);
937 l_disb_import_status := 'E';
938 END IF;
939
940
941 -- Validate Process Date
942 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
943 l_debug_str := l_debug_str || ', Process Date : ' || p_pell_disb_int.disburse_batch_process_date;
944 END IF;
945
946 IF ( (p_pell_disb_int.disb_ack_act_flag IN ('N','R')) AND
947 (p_pell_disb_int.disburse_batch_process_date IS NOT NULL) )
948 THEN
949 fnd_message.set_name('IGF','IGF_GR_LI_PRDT_INVALID_1');
950 fnd_message.set_token('PROC_DATE',p_pell_disb_int.disburse_batch_process_date);
951 fnd_file.put_line(fnd_file.log, g_disb_pad_str || fnd_message.get);
952 l_disb_import_status := 'E';
953 ELSIF ( (p_pell_disb_int.disb_ack_act_flag IN ('A','C','E')) AND
954 (p_pell_disb_int.disburse_batch_process_date IS NULL) )
955 THEN
956 fnd_message.set_name('IGF','IGF_GR_LI_PRDT_INVALID_2');
957 fnd_file.put_line(fnd_file.log, g_disb_pad_str || fnd_message.get);
958 l_disb_import_status := 'E';
959 END IF;
960
961
962 -- Validate Payment Period start date
963 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
964 l_debug_str := l_debug_str || ', Payment Period Start Date : ' || p_pell_disb_int.pymt_prd_start_date;
965 END IF;
966 IF ( (p_pell_disb_int.disb_ack_act_flag IN ('N','R')) AND
967 (p_pell_disb_int.pymt_prd_start_date IS NOT NULL) )
968 THEN
969 fnd_message.set_name('IGF','IGF_GR_LI_PMST_INVALID');
970 fnd_message.set_token('PM_STDT',p_pell_disb_int.pymt_prd_start_date);
971 fnd_file.put_line(fnd_file.log, g_disb_pad_str || fnd_message.get);
972 l_disb_import_status := 'E';
973 END IF;
974
975
976 -- Validate Accepted Disbursement Date
977 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
978 l_debug_str := l_debug_str || ', Accepted Disbursement Date : ' || p_pell_disb_int.accpt_disb_date;
979 END IF;
980 IF ( (p_pell_disb_int.disb_ack_act_flag IN ('N','R')) AND
981 (p_pell_disb_int.accpt_disb_date IS NOT NULL) )
982 THEN
983 fnd_message.set_name('IGF','IGF_GR_LI_ADBDT_INVALID_1');
984 fnd_message.set_token('ACC_DISB_DT',p_pell_disb_int.accpt_disb_date);
985 fnd_file.put_line(fnd_file.log, g_disb_pad_str || fnd_message.get);
986 l_disb_import_status := 'E';
987 ELSIF ( (p_pell_disb_int.disb_ack_act_flag IN ('A','C','E')) AND
988 (p_pell_disb_int.accpt_disb_date IS NULL) )
989 THEN
990 fnd_message.set_name('IGF','IGF_GR_LI_ADBDT_INVALID_2');
991 fnd_file.put_line(fnd_file.log, g_disb_pad_str || fnd_message.get);
992 l_disb_import_status := 'E';
993 END IF;
994
995
996 -- Validate Accepted Disbursement Amount
997 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
998 l_debug_str := l_debug_str || ', Accepted Disbursement Amount : ' || p_pell_disb_int.disb_accpt_amt;
999 END IF;
1000 IF ( (p_pell_disb_int.disb_ack_act_flag IN ('N','R')) AND
1001 (p_pell_disb_int.disb_accpt_amt IS NOT NULL) )
1002 THEN
1003 fnd_message.set_name('IGF','IGF_GR_LI_DBAC_AMT_INVALID_1');
1004 fnd_message.set_token('DISB_AC_AMT',p_pell_disb_int.disb_accpt_amt);
1005 fnd_file.put_line(fnd_file.log, g_disb_pad_str || fnd_message.get);
1006 l_disb_import_status := 'E';
1007 ELSIF (p_pell_disb_int.disb_ack_act_flag IN ('A','C','E')) THEN
1008
1009 -- Disb Acpt Amt is mandatory if ack status is Accepted, Corrected or Rejected
1010 IF p_pell_disb_int.disb_accpt_amt IS NULL THEN
1011 fnd_message.set_name('IGF','IGF_GR_LI_DBAC_AMT_INVALID_2');
1012 fnd_file.put_line(fnd_file.log, g_disb_pad_str || fnd_message.get);
1013 l_disb_import_status := 'E';
1014 -- Validate Disbursement Amount against the Disb table
1015 ELSIF p_pell_disb_int.disb_accpt_amt <> lc_awd_disb.disb_net_amt THEN
1016 fnd_message.set_name('IGF','IGF_GR_LI_DAMT_INVALID');
1017 fnd_message.set_token('INT_AMT',p_pell_disb_int.disb_accpt_amt);
1018 fnd_message.set_token('SYS_AMT',lc_awd_disb.disb_net_amt);
1019 fnd_file.put_line(fnd_file.log, g_disb_pad_str || fnd_message.get);
1020
1021 END IF;
1022
1023 END IF;
1024
1025
1026 -- Validate Accepted Disbursement Amount Sign Indicator
1027 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1028 l_debug_str := l_debug_str || ', Accepted Disbursement Amount Sign Indicator : ' || p_pell_disb_int.disbursement_sign_flag;
1029 l_debug_str := l_debug_str || ', Accepted Disbursment Amount : ' || p_pell_disb_int.disb_accpt_amt;
1030 END IF;
1031 IF ( (p_pell_disb_int.disb_ack_act_flag IN ('N','R')) AND
1032 (p_pell_disb_int.disbursement_sign_flag IS NOT NULL) )
1033
1034 THEN
1035 fnd_message.set_name('IGF','IGF_GR_LI_ADBCR_INVALID_1');
1036 fnd_message.set_token('ACC_DBCR_FLAG',p_pell_disb_int.disbursement_sign_flag);
1037 fnd_file.put_line(fnd_file.log, g_disb_pad_str || fnd_message.get);
1038 l_disb_import_status := 'E';
1039
1040 ELSIF ( (p_pell_disb_int.disb_ack_act_flag IN ('A','C','E')) AND
1041 (p_pell_disb_int.disbursement_sign_flag IS NULL) )
1042 THEN
1043 fnd_message.set_name('IGF','IGF_GR_LI_ADBCR_INVALID_2');
1044 fnd_message.set_token('ACC_DBCR_FLAG',p_pell_disb_int.disbursement_sign_flag);
1045 fnd_file.put_line(fnd_file.log, g_disb_pad_str || fnd_message.get);
1046 l_disb_import_status := 'E';
1047
1048 END IF;
1049
1050 -- Validate Accepted Disbursment Amount
1051 -- Flag should be 'P' if the disb accepted amount is positive else 'N'
1052 IF ( (p_pell_disb_int.disbursement_sign_flag IS NOT NULL) AND
1053 (p_pell_disb_int.disb_ack_act_flag IN ('A','C','E')) )
1054 THEN
1055
1056 IF (p_pell_disb_int.disbursement_sign_flag = 'P' AND NVL(p_pell_disb_int.disb_accpt_amt,1) <= 0 ) OR
1057 (p_pell_disb_int.disbursement_sign_flag = 'N' AND NVL(p_pell_disb_int.disb_accpt_amt,-1) > 0 ) OR
1058 (p_pell_disb_int.disbursement_sign_flag NOT IN ('P','N'))
1059 THEN
1060 fnd_message.set_name('IGF','IGF_GR_LI_ADBCR_INVALID_3');
1061 fnd_message.set_token('ACC_DBCR_FLAG',p_pell_disb_int.disbursement_sign_flag);
1062 fnd_file.put_line(fnd_file.log, g_disb_pad_str || fnd_message.get);
1063 l_disb_import_status := 'E';
1064 END IF;
1065 END IF;
1066
1067 -- Validate Accepted Payment Period start date
1068 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1069 l_debug_str := l_debug_str || ', Accepted Payment Period start date : ' || p_pell_disb_int.accpt_pymt_prd_start_date;
1070 END IF;
1071 IF ( (p_pell_disb_int.disb_ack_act_flag IN ('N','R')) AND
1072 (p_pell_disb_int.accpt_pymt_prd_start_date IS NOT NULL) )
1073 THEN
1074 fnd_message.set_name('IGF','IGF_GR_LI_APMST_INVALID');
1075 fnd_message.set_token('ACC_PM_STDT',p_pell_disb_int.accpt_pymt_prd_start_date);
1076 fnd_file.put_line(fnd_file.log, g_disb_pad_str || fnd_message.get);
1077 l_disb_import_status := 'E';
1078 END IF;
1079
1080
1081 -- Validate Disbursement Year to Date Amount
1082 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1083 l_debug_str := l_debug_str || ', Disbursement Year to Date Amount : ' || p_pell_disb_int.disb_ytd_amt;
1084 END IF;
1085 IF ( (p_pell_disb_int.disb_ack_act_flag IN ('N','R')) AND
1086 (p_pell_disb_int.disb_ytd_amt IS NOT NULL) )
1087 THEN
1088 fnd_message.set_name('IGF','IGF_GR_LI_DYTD_INVALID_1');
1089 fnd_message.set_token('YTD_AMT',p_pell_disb_int.disb_ytd_amt);
1090 fnd_file.put_line(fnd_file.log, g_disb_pad_str || fnd_message.get);
1091 l_disb_import_status := 'E';
1092
1093 ELSIF ( (p_pell_disb_int.disb_ack_act_flag IN ('A','C','E')) AND
1094 (p_pell_disb_int.disb_ytd_amt IS NULL) )
1095 THEN
1096 fnd_message.set_name('IGF','IGF_GR_LI_DYTD_INVALID_2');
1097 fnd_file.put_line(fnd_file.log, g_disb_pad_str || fnd_message.get);
1098 l_disb_import_status := 'E';
1099
1100 END IF;
1101
1102
1103 -- Validate Edit Code
1104 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1105 l_debug_str := l_debug_str || ', Edit Code : ' || p_pell_disb_int.edit_codes_txt;
1106 END IF;
1107 IF ( (p_pell_disb_int.disb_ack_act_flag IN ('N','R')) AND
1108 (p_pell_disb_int.edit_codes_txt IS NOT NULL) )
1109 THEN
1110 fnd_message.set_name('IGF','IGF_GR_LI_REJCD_INVALID');
1111 fnd_message.set_token('REJ_CODES',p_pell_disb_int.edit_codes_txt);
1112 fnd_file.put_line(fnd_file.log, g_disb_pad_str || fnd_message.get);
1113 l_disb_import_status := 'E';
1114 END IF;
1115
1116
1117 -- Validate ED Use Flags
1118 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1119 l_debug_str := l_debug_str || ', ED Use Flags : ' || p_pell_disb_int.ed_use_flags;
1120 END IF;
1121 IF ( (p_pell_disb_int.disb_ack_act_flag IN ('N','R')) AND
1122 (p_pell_disb_int.ed_use_flags IS NOT NULL) )
1123 THEN
1124 fnd_message.set_name('IGF','IGF_GR_LI_DEDF_INVALID');
1125 fnd_message.set_token('DISB_ED_FLAGS',p_pell_disb_int.ed_use_flags);
1126 fnd_file.put_line(fnd_file.log, g_disb_pad_str || fnd_message.get);
1127 l_disb_import_status := 'E';
1128 END IF;
1129
1130 -- Completed all validations for Pell Disbursements
1131 IF l_disb_import_status <> 'E' THEN
1132
1133 -- Create the Disbursement batch record if Pell Disb in Ack status
1134 l_rfmb_id := NULL;
1135 IF ( (p_pell_disb_int.disb_ack_act_flag IS NOT NULL) AND p_pell_disb_int.disb_ack_act_flag NOT IN ('R','N')) THEN
1136
1137 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1138 l_debug_str := l_debug_str || ' :: Creating Pell Batch';
1139 END IF;
1140 l_rfmb_id := create_pell_disb_batch(p_pell_disb_int, p_ope_cd);
1141
1142 -- Create Pell Disbursement only if the Pell Batch is created
1143 IF l_rfmb_id = -1 THEN
1144
1145 l_disb_import_status := 'E';
1146 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1147 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_aw_li_import.import_pell_disb.debug', l_debug_str || ' : Pell Disb Batch not created' || ' Ret status : '|| l_disb_import_status);
1148 l_debug_str := NULL;
1149 END IF;
1150
1151 RETURN l_disb_import_status;
1152 END IF;
1153 END IF;
1154
1155 ELSE
1156 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1157 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_aw_li_import.import_pell_disb.debug', l_debug_str || ' : Pell Disb Batch not created' || ' Ret status : '|| l_disb_import_status);
1158 l_debug_str := NULL;
1159 END IF;
1160 RETURN l_disb_import_status;
1161 END IF;
1162
1163 -- Create Pell Disbursement
1164 IF l_disb_import_status <> 'E' AND (NOT (igf_sl_dl_validation.check_full_participant (g_cal_type, g_seq_number,'PELL'))) THEN
1165 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1166 l_debug_str := l_debug_str || ' :: Creating Pell Disbursement';
1167 END IF;
1168 IF NOT create_pell_disb(p_pell_disb_int, l_rfmb_id, lc_awd_disb.disb_date, lc_awd_disb.disb_net_amt) THEN
1169
1170 l_disb_import_status := 'E';
1171 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1172 l_debug_str := l_debug_str || ' :: Pell Disbursement not created Ret status : '|| l_disb_import_status;
1173 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_gr_li_import.import_pell_disb.debug', l_debug_str );
1174 l_debug_str := NULL;
1175 END IF;
1176 RETURN l_disb_import_status;
1177 END IF;
1178 END IF;
1179
1180 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1181 l_debug_str := l_debug_str || ' :: IMPORT_PELL_DISB Successful :: Ret status : '|| l_disb_import_status;
1182 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_gr_li_import.import_pell_disb.debug', l_debug_str );
1183 l_debug_str := NULL;
1184 END IF;
1185 RETURN l_disb_import_status;
1186
1187 EXCEPTION
1188 WHEN OTHERS THEN
1189 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
1190 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'igf.plsql.igf_gr_li_import.import_pell_disb.exception', l_debug_str || SQLERRM );
1191 END IF;
1192 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1193 fnd_message.set_token('NAME','IGF_GR_LI_IMPORT.IMPORT_PELL_DISB');
1194 igs_ge_msg_stack.add;
1195 RETURN 'E';
1196
1197 END import_pell_disb;
1198
1199
1200 FUNCTION create_pell_orig_batch(
1201 p_pell_orig_int c_pell_orig_int%ROWTYPE
1202 ) RETURN NUMBER AS
1203 /*
1204 || Created By : brajendr
1205 || Created On : 19-Jun-2003
1206 || Purpose : Creates the Pell origination batch records for the legacy pell record
1207 || Known limitations, enhancements or remarks :
1208 || Change History :
1209 || Who When What
1210 || (reverse chronological order - newest change first)
1211 */
1212
1213 -- Get the details of duplicate batch id
1214 CURSOR c_chk_dup_batch_dtls(
1215 cp_batch_id igf_gr_rfms_batch_all.batch_id%TYPE
1216 ) IS
1217 SELECT pb.rfmb_id
1218 FROM igf_gr_rfms_batch_all pb
1219 WHERE pb.batch_id = cp_batch_id;
1220
1221 lc_chk_dup_batch_dtls c_chk_dup_batch_dtls%ROWTYPE;
1222
1223 l_row_id ROWID;
1224 l_rfmb_id igf_gr_rfms_batch_all.rfmb_id%TYPE;
1225 l_batch_id igf_gr_rfms_batch_all.batch_id%TYPE;
1226 l_rfms_ack_batch_id igf_gr_rfms_batch_all.rfms_ack_batch_id%TYPE;
1227 l_data_rec_length igf_gr_rfms_batch_all.data_rec_length%TYPE;
1228
1229 BEGIN
1230
1231 -- Check if duplicate batch records exists in the system, if present then return the existing batch id
1232 OPEN c_chk_dup_batch_dtls(p_pell_orig_int.orig_send_batch_id_txt);
1233 FETCH c_chk_dup_batch_dtls INTO lc_chk_dup_batch_dtls;
1234 CLOSE c_chk_dup_batch_dtls;
1235
1236 IF lc_chk_dup_batch_dtls.rfmb_id IS NOT NULL THEN
1237 RETURN lc_chk_dup_batch_dtls.rfmb_id;
1238 END IF;
1239
1240
1241 -- Ack Batch id is needed only if ack data is not null.
1242 IF p_pell_orig_int.rfms_ack_date IS NOT NULL THEN
1243 l_rfms_ack_batch_id := p_pell_orig_int.orig_send_batch_id_txt;
1244 ELSE
1245 l_rfms_ack_batch_id := NULL;
1246 END IF;
1247
1248
1249 -- Create Pell Batch Record
1250 l_row_id := NULL;
1251 l_rfmb_id := -1;
1252 igf_gr_rfms_batch_pkg.insert_row(
1253 x_rowid => l_row_id,
1254 x_rfmb_id => l_rfmb_id,
1255 x_batch_id => p_pell_orig_int.orig_send_batch_id_txt,
1256 x_data_rec_length => 300,
1257 x_ope_id => p_pell_orig_int.ope_cd,
1258 x_software_providor => NULL,
1259 x_rfms_process_dt => p_pell_orig_int.rfms_process_date,
1260 x_rfms_ack_dt => p_pell_orig_int.rfms_ack_date,
1261 x_rfms_ack_batch_id => l_rfms_ack_batch_id,
1262 x_reject_reason => NULL,
1263 x_mode => 'R'
1264 );
1265
1266 RETURN l_rfmb_id;
1267
1268 EXCEPTION
1269 WHEN OTHERS THEN
1270 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
1271 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'igf.plsql.igf_gr_li_import.create_pell_orig_batch.exception', SQLERRM );
1272 END IF;
1273 fnd_message.set_name('IGF','IGF_GR_LI_BINSERT_FAIL');
1274 fnd_message.set_token('BATCH_NUM',p_pell_orig_int.orig_send_batch_id_txt);
1275 igs_ge_msg_stack.add;
1276 RETURN -1;
1277
1278 END create_pell_orig_batch;
1279
1280
1281 FUNCTION create_pell_orig(
1282 p_pell_orig_int c_pell_orig_int%ROWTYPE,
1283 p_isir_details c_isir_details%ROWTYPE,
1284 p_rfmb_id igf_gr_rfms_batch_all.rfmb_id%TYPE
1285 ) RETURN BOOLEAN AS
1286 /*
1287 || Created By : brajendr
1288 || Created On : 19-Jun-2003
1289 || Purpose : Creates Pell Origination record for the legacy interface record
1290 || Known limitations, enhancements or remarks :
1291 || Change History :
1292 || Who When What
1293 || (reverse chronological order - newest change first)
1294 */
1295
1296 CURSOR c_pymt_prds_n_date(
1297 cp_award_id igf_aw_award_all.award_id%TYPE
1298 ) IS
1299 SELECT adisb1.disb_num, adisb2.disb_date
1300 FROM igf_aw_awd_disb adisb1, igf_aw_awd_disb adisb2
1301 WHERE adisb1.award_id = cp_award_id
1302 AND adisb1.disb_num IN ( SELECT MAX(adisb11.disb_num)
1303 FROM igf_aw_awd_disb adisb11
1304 WHERE adisb11.award_id = adisb1.award_id
1305 )
1306 AND adisb1.award_id = adisb2.award_id
1307 AND adisb2.disb_num IN ( SELECT MIN(adisb11.disb_num)
1308 FROM igf_aw_awd_disb adisb11
1309 WHERE adisb11.award_id = adisb2.award_id
1310 );
1311
1312 lc_pymt_prds_n_date c_pymt_prds_n_date%ROWTYPE;
1313
1314 l_row_id ROWID;
1315 l_debug_str fnd_log_messages.message_text%TYPE := 'IGFGR10B.pls Procedure create_pell_orig :: ';
1316
1317 BEGIN
1318
1319 -- Get the Total Payment periods and First disbursement date
1320 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1321 l_debug_str := l_debug_str || ', Fetching Disb dates for Award ID : ' || g_award_id;
1322 END IF;
1323 OPEN c_pymt_prds_n_date(g_award_id);
1324 FETCH c_pymt_prds_n_date INTO lc_pymt_prds_n_date;
1325 CLOSE c_pymt_prds_n_date;
1326
1327 -- Create Pell Origination Record in the production table
1328 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1329 l_debug_str := l_debug_str || ', Creating RFMS record :: Total Disb Num : ' || lc_pymt_prds_n_date.disb_num;
1330 l_debug_str := l_debug_str || ', Disb Date : ' || lc_pymt_prds_n_date.disb_date;
1331 END IF;
1332
1333 l_row_id := NULL;
1334
1335 igf_gr_rfms_pkg.insert_row(
1336 x_rowid => l_row_id,
1337 x_origination_id => p_pell_orig_int.origination_id_txt,
1338 x_ci_cal_type => g_cal_type,
1339 x_ci_sequence_number => g_seq_number,
1340 x_base_id => g_base_id,
1341 x_award_id => g_award_id,
1342 x_rfmb_id => p_rfmb_id,
1343 x_sys_orig_ssn => p_isir_details.original_ssn,
1344 x_sys_orig_name_cd => p_isir_details.orig_name_id,
1345 x_transaction_num => p_pell_orig_int.transaction_num_txt,
1346 x_efc => p_pell_orig_int.efc_amt,
1347 x_ver_status_code => p_pell_orig_int.verification_status_code,
1348 x_secondary_efc => p_pell_orig_int.secondary_efc_amt,
1349 x_secondary_efc_cd => p_pell_orig_int.secondary_efc_code,
1350 x_pell_amount => p_pell_orig_int.pell_award_amt,
1351 x_pell_profile => NULL,
1352 x_enrollment_status => p_pell_orig_int.enrollment_status_flag,
1353 x_enrollment_dt => p_pell_orig_int.enrollment_date,
1354 x_coa_amount => p_pell_orig_int.pell_coa_amt,
1355 x_academic_calendar => p_pell_orig_int.academic_calendar_cd,
1356 x_payment_method => p_pell_orig_int.payment_method_code,
1357 x_total_pymt_prds => lc_pymt_prds_n_date.disb_num,
1358 x_incrcd_fed_pell_rcp_cd => p_pell_orig_int.incrcd_fed_pell_rcp_code,
1359 x_attending_campus_id => g_attending_pell_cd,
1360 x_est_disb_dt1 => lc_pymt_prds_n_date.disb_date,
1361 x_orig_action_code => p_pell_orig_int.orig_status_code,
1362 x_orig_status_dt => p_pell_orig_int.orig_status_date,
1363 x_orig_ed_use_flags => p_pell_orig_int.orig_ed_use_flags_txt,
1364 x_ft_pell_amount => p_pell_orig_int.ft_sch_pell_amt,
1365 x_prev_accpt_efc => p_pell_orig_int.prev_accpt_efc_amt,
1366 x_prev_accpt_tran_no => p_pell_orig_int.prev_accpt_tran_num_txt,
1367 x_prev_accpt_sec_efc_cd => p_pell_orig_int.prev_accpt_sec_efc_cd,
1368 x_prev_accpt_coa => p_pell_orig_int.prev_accpt_coa_amt,
1369 x_orig_reject_code => p_pell_orig_int.orig_reject_codes_txt,
1370 x_wk_inst_time_calc_pymt => p_pell_orig_int.wk_inst_time_calc_pymt_num,
1371 x_wk_int_time_prg_def_yr => p_pell_orig_int.wk_int_time_prg_def_yr_num,
1372 x_cr_clk_hrs_prds_sch_yr => p_pell_orig_int.cr_clk_hrs_prds_sch_yr_num,
1373 x_cr_clk_hrs_acad_yr => p_pell_orig_int.cr_clk_hrs_acad_yr_num,
1374 x_inst_cross_ref_cd => p_pell_orig_int.inst_cross_ref_cd,
1375 x_low_tution_fee => p_pell_orig_int.low_tution_fee_cd,
1376 x_rec_source => 'B',
1377 x_pending_amount => p_pell_orig_int.pending_amt,
1378 x_birth_dt => p_isir_details.date_of_birth,
1379 x_last_name => p_isir_details.last_name,
1380 x_first_name => p_isir_details.first_name,
1381 x_middle_name => p_isir_details.middle_initial,
1382 x_current_ssn => p_isir_details.current_ssn,
1383 x_legacy_record_flag => 'Y',
1384 x_mode => 'R',
1385 x_reporting_pell_cd => g_reporting_pell_id,
1386 x_rep_entity_id_txt => p_pell_orig_int.rep_entity_id_txt,
1387 x_atd_entity_id_txt => p_pell_orig_int.atd_entity_id_txt,
1388 x_note_message => NULL,
1389 x_full_resp_code => NULL,
1390 x_document_id_txt => NULL
1391 );
1392
1393 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1394 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_gr_li_import.create_pell_orig.debug', l_debug_str || ', Created Pell Origination record Orig ID :' || p_pell_orig_int.origination_id_txt);
1395 l_debug_str := NULL;
1396 END IF;
1397
1398 RETURN TRUE;
1399
1400 EXCEPTION
1401 WHEN OTHERS THEN
1402 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
1403 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'igf.plsql.igf_gr_li_import.create_pell_orig.exception', SQLERRM );
1404 END IF;
1405 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1406 fnd_message.set_token('NAME','IGF_GR_LI_IMPORT.CREATE_PELL_ORIG');
1407 igs_ge_msg_stack.add;
1408 RETURN FALSE;
1409
1410 END create_pell_orig;
1411
1412
1413 FUNCTION import_pell_orig(
1414 p_pell_orig_int c_pell_orig_int%ROWTYPE
1415 ) RETURN VARCHAR2 AS
1416 /*
1417 || Created By : brajendr
1418 || Created On : 19-Jun-2003
1419 || Purpose : Import pell origination records from the legacy interface table to the production tables
1420 || Known limitations, enhancements or remarks :
1421 || Change History :
1422 || Who When What
1423 || (reverse chronological order - newest change first)
1424 || rasahoo 16-Feb-2004 Bug # 3441605 Changed the cursor "cur_get_attendance_type_code"
1425 || Now it will select "Base_attendance_type_code" instead of "attendance_type_code"
1426 || Removed cursor "c_get_enrl_status" as it is no longer used.
1427 || ugummall 05-DEC-2003 Bug 3252832. FA 131 - COD Updates
1428 || Changed the logic for validating the enrollment status w.r.t. FA 131.
1429 || Added the cursor cur_get_attendance_type_code for above validation.
1430 || Removed the validation for Scheduled Pell Award.
1431 || rasahoo 02-Sep-2003 changed the cursor C_GET_ENRL_STATUS.
1432 || Removed the join with IGF_AP_FA_BASE_H
1433 || and got the DERIVED_ATTEND_TYPE from IGF_AP_GEN_001
1434 || and changed the data type of l_enrl_status from igf_ap_fa_base_h.derived_attend_type%TYPE
1435 || as part of FA-114(Obsoletion of FA base record History)
1436 ||
1437 */
1438
1439 -- Get the Award details for the refference award number if present
1440 CURSOR c_awd_details(
1441 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
1442 cp_award_number igf_aw_award_all.award_number_txt%TYPE
1443 ) IS
1444 SELECT awd.award_id, awd.offered_amt, awd.accepted_amt, awd.fund_id
1445 FROM igf_aw_award_all awd
1446 WHERE awd.base_id = cp_base_id
1447 AND awd.award_number_txt IS NOT NULL
1448 AND awd.award_number_txt = cp_award_number
1449 AND awd.award_status <> 'SIMULATED';
1450
1451 lc_awd_details c_awd_details%ROWTYPE;
1452
1453
1454 -- Get the details of RFMS for the context award id
1455 CURSOR c_chk_rfms_awd(
1456 cp_award_id igf_aw_award_all.award_id%TYPE
1457 ) IS
1458 SELECT rfms.origination_id
1459 FROM igf_gr_rfms_all rfms
1460 WHERE rfms.award_id = cp_award_id;
1461
1462 lc_chk_rfms_awd c_chk_rfms_awd%ROWTYPE;
1463
1464 CURSOR c_get_remain_disb(
1465 cp_award_id igf_aw_award_all.award_id%TYPE
1466 ) IS
1467 SELECT ad.disb_num, ad.disb_date, ad.disb_accepted_amt
1468 FROM igf_aw_awd_disb_all ad
1469 WHERE ad.award_id = cp_award_id
1470 AND NOT EXISTS ( SELECT pdisb.disb_ref_num
1471 FROM igf_gr_rfms_all porig, igf_gr_rfms_disb_all pdisb
1472 WHERE porig.origination_id = pdisb.origination_id
1473 AND ad.award_id = porig.award_id
1474 AND ad.disb_num = pdisb.disb_ref_num);
1475
1476 lc_isir_details c_isir_details%ROWTYPE;
1477 l_pell_import_status igf_aw_li_pell_ints.import_status_type%TYPE := 'I';
1478 l_enrl_status igs_en_stdnt_ps_att_all.derived_att_type%TYPE;
1479 l_origination_id igf_aw_li_pell_ints.origination_id_txt%TYPE;
1480 l_rfmb_id igf_gr_rfms_batch_all.rfmb_id%TYPE;
1481 l_db_cr_flag igf_gr_rfms_disb.db_cr_flag%TYPE;
1482 l_rfmd_id igf_gr_rfms_disb.rfmd_id%TYPE;
1483 l_disb_num_prmpt igf_lookups_view.meaning%TYPE;
1484 l_processing igf_lookups_view.meaning%TYPE;
1485 l_debug_str fnd_log_messages.message_text%TYPE := 'IGFGR10B.pls Procedure import_pell_orig (1) :: ';
1486 l_error VARCHAR2(30);
1487 l_orig_batch_id VARCHAR2(30);
1488 l_pell_mat VARCHAR2(10);
1489 l_pell_disb_full_status VARCHAR2(1);
1490 l_pell_disb_indv_status VARCHAR2(1);
1491 l_derived_attend_type VARCHAR2(30);
1492 l_office_cd VARCHAR2(30);
1493 l_ret_status VARCHAR2(1);
1494 l_msg_data VARCHAR2(30);
1495 ln_rem_disb_cnt NUMBER := 0;
1496 l_temp_aid NUMBER;
1497 l_row_id ROWID;
1498
1499 -- Get the Base attendance type code for context Award Id
1500 CURSOR cur_get_attendance_type_code(cp_award_id igf_aw_award_all.award_id%TYPE) IS
1501 SELECT base_attendance_type_code
1502 FROM igf_aw_awd_disb_all
1503 WHERE award_id = cp_award_id
1504 GROUP BY base_attendance_type_code;
1505
1506 rec_get_attendance_type_code cur_get_attendance_type_code%ROWTYPE;
1507
1508 BEGIN
1509
1510 -- Start validation of Pell record.
1511 SAVEPOINT SP_PELL;
1512
1513 -- Validate the Award Number in the production table
1514 g_award_id := NULL;
1515 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1516 l_debug_str := l_debug_str || ', Fetching production table award details with Awd Number : '|| p_pell_orig_int.award_number_txt;
1517 END IF;
1518
1519 OPEN c_awd_details(g_base_id, p_pell_orig_int.award_number_txt);
1520 FETCH c_awd_details INTO lc_awd_details;
1521 IF c_awd_details%NOTFOUND THEN
1522 fnd_message.set_name('IGF','IGF_GR_LI_AWD_INVALID');
1523 fnd_file.put_line(fnd_file.log, fnd_message.get);
1524
1525 CLOSE c_awd_details;
1526 ROLLBACK TO SP_PELL;
1527 l_pell_import_status := 'E';
1528 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1529 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_gr_li_import.import_pell_orig.debug',
1530 l_debug_str || 'Award ref not present in production table :'|| l_pell_import_status);
1531 END IF;
1532 RETURN l_pell_import_status;
1533
1534 END IF;
1535 CLOSE c_awd_details;
1536
1537 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1538 l_debug_str := l_debug_str || ' Award ID : ' || lc_awd_details.award_id;
1539 END IF;
1540
1541 g_award_id := lc_awd_details.award_id;
1542
1543
1544 -- Validate if award id is already present in the GR RFMS table
1545 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1546 l_debug_str := l_debug_str || ', Dup Award ID chk : ';
1547 END IF;
1548 OPEN c_chk_rfms_awd(g_award_id);
1549 FETCH c_chk_rfms_awd INTO lc_chk_rfms_awd;
1550 CLOSE c_chk_rfms_awd;
1551
1552 IF lc_chk_rfms_awd.origination_id IS NOT NULL THEN
1553 ROLLBACK TO SP_PELL;
1554 fnd_message.set_name('IGF','IGF_GR_LI_OINSERT_DUP_AWID');
1555 fnd_message.set_token('SYS_ORIG_ID',lc_chk_rfms_awd.origination_id);
1556 fnd_message.set_token('AWARD_ID',g_award_id);
1557 fnd_file.put_line(fnd_file.log, fnd_message.get);
1558 l_pell_import_status := 'E';
1559 RETURN l_pell_import_status;
1560 END IF;
1561
1562
1563 -- Validate Transaction Number
1564 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1565 l_debug_str := l_debug_str || ', Transaction Number : ' || p_pell_orig_int.transaction_num_txt;
1566 END IF;
1567 -- Bug# 3089841 Added the check for the transaction_num to be NULL
1568 IF p_pell_orig_int.transaction_num_txt IS NULL OR TO_NUMBER(p_pell_orig_int.transaction_num_txt) < 1 OR TO_NUMBER(p_pell_orig_int.transaction_num_txt) > 99 THEN
1569 fnd_message.set_name('IGF','IGF_GR_LI_TRNM_INVALID');
1570 fnd_message.set_token('TRNM',p_pell_orig_int.transaction_num_txt);
1571 fnd_file.put_line(fnd_file.log, fnd_message.get);
1572 l_pell_import_status := 'E';
1573 END IF;
1574
1575
1576 -- Fetch the ISIR Details of the new person,
1577 -- IF ISIR details are not present then log an error message proceed with new person
1578 OPEN c_isir_details(g_base_id, p_pell_orig_int.transaction_num_txt);
1579 FETCH c_isir_details INTO lc_isir_details;
1580 IF c_isir_details%NOTFOUND THEN
1581
1582 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1583 l_debug_str := l_debug_str || ', ISIR Details not found : ';
1584 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_gr_li_import.import_pell_orig.debug', l_debug_str);
1585 l_debug_str := NULL;
1586 END IF;
1587 -- Bug# 3089841 Changed the message
1588 fnd_message.set_name('IGF','IGF_GR_ISIR_NOT_FOUND');
1589 fnd_message.set_token('STUD',NULL);
1590 fnd_file.put_line(fnd_file.log, fnd_message.get);
1591 CLOSE c_isir_details;
1592 RETURN 'E';
1593 END IF;
1594 CLOSE c_isir_details;
1595
1596
1597 -- Validate Origination Status
1598 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1599 l_debug_str := l_debug_str || ', Origination Status : ' || p_pell_orig_int.orig_status_code;
1600 END IF;
1601 IF (p_pell_orig_int.orig_status_code IS NOT NULL) AND
1602 (igf_ap_gen.get_aw_lookup_meaning('IGF_GR_ORIG_STATUS', p_pell_orig_int.orig_status_code, g_sys_award_year) IS NULL OR
1603 p_pell_orig_int.orig_status_code IN ('S','D') )
1604 THEN
1605 fnd_message.set_name('IGF','IGF_GR_LI_OSTS_INVALID');
1606 fnd_message.set_token('ORIG_STATUS',p_pell_orig_int.orig_status_code);
1607 fnd_file.put_line(fnd_file.log, fnd_message.get);
1608 l_pell_import_status := 'E';
1609 END IF;
1610
1611 -- nsidana 11/4/2003 FA126 Multiple FA offices.
1612 -- Validation for the attend_campus_id in the pell interface table.
1613 -- This validation needs to be done only in case of phase-in participant
1614
1615 IF (NOT (igf_sl_dl_validation.check_full_participant (g_cal_type, g_seq_number,'PELL'))) THEN
1616
1617 g_attending_pell_cd := NULL;
1618
1619 IF(p_pell_orig_int.attending_campus_cd IS NOT NULL)
1620 THEN
1621 -- Derive the attending campus ID for the student and validate it against the values in the interface table. They shud match, else error.
1622 igf_sl_gen.get_stu_fao_code(g_base_id,'PELL_ID',l_office_cd,l_ret_status,l_msg_data);
1623 IF(l_ret_status = 'E')THEN
1624 -- return status is error then l_msg_data would have message name. Put it on the Stack and raise error.
1625 fnd_message.set_name('IGF',l_msg_data);
1626 fnd_file.put_line(fnd_file.log,fnd_message.get);
1627 l_pell_import_status := 'E';
1628 ELSE -- return status indicating success here
1629 IF((l_office_cd IS NOT NULL) AND (l_office_cd <> p_pell_orig_int.attending_campus_cd))
1630 THEN
1631 -- Raise error.
1632 fnd_message.set_name('IGF','IGF_AP_INV_FLD_VAL');
1633 fnd_message.set_token('FIELD','ATTENDING_CAMPUS_CD');
1634 fnd_file.put_line(fnd_file.log, fnd_message.get);
1635 l_pell_import_status := 'E';
1636 ELSE
1637 g_attending_pell_cd := TRIM(p_pell_orig_int.attending_campus_cd);
1638 END IF;
1639 END IF;
1640 ELSE -- Attend campus ID is null.
1641
1642 igf_sl_gen.get_stu_fao_code(g_base_id,'PELL_ID',l_office_cd, l_ret_status,l_msg_data);
1643 IF(l_ret_status = 'E')
1644 THEN
1645 fnd_message.set_name('IGF',l_msg_data);
1646 fnd_file.put_line(fnd_file.log,fnd_message.get);
1647 l_pell_import_status := 'E';
1648 ELSIF(l_ret_status = 'S')
1649 THEN
1650 g_attending_pell_cd := l_office_cd;
1651 END IF;
1652 END IF;
1653
1654 -- Derive the reporting Pell ID by making a call to the local fn.
1655
1656 g_reporting_pell_id:=null;
1657 g_reporting_pell_id:=derive_rep_pell_id(p_pell_orig_int);
1658 IF (g_reporting_pell_id IS NULL) THEN
1659 fnd_message.set_name( 'IGF', 'IGF_GR_NOREP_PELL');
1660 fnd_message.set_token('STU_NUMBER',p_pell_orig_int.person_number);
1661 fnd_file.put_line(fnd_file.log,fnd_message.get);
1662 l_pell_import_status := 'E';
1663 END IF;
1664
1665 END IF;
1666
1667
1668
1669 -- Validate Origination ID
1670 -- Passing Attending Pell value to generate_orgination_id function instead of reporting pell id
1671 -- Use attending entity id if the award year is COD-XML processing
1672 l_origination_id := NULL;
1673 l_error := NULL;
1674
1675 IF igf_sl_dl_validation.check_full_participant (g_cal_type, g_seq_number,'PELL') THEN
1676 igf_gr_pell.generate_origination_id(
1677 g_base_id,
1678 g_atd_entity_id_txt,
1679 l_origination_id,
1680 l_error
1681 );
1682 ELSE
1683 igf_gr_pell.generate_origination_id(
1684 g_base_id,
1685 g_attending_pell_cd,
1686 l_origination_id,
1687 l_error
1688 );
1689
1690 END IF;
1691
1692
1693
1694 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1695 l_debug_str := l_debug_str || ', Origination ID : ' || p_pell_orig_int.origination_id_txt;
1696 l_debug_str := l_debug_str || ', l_origination_id : ' || l_origination_id;
1697 END IF;
1698
1699 IF l_origination_id IS NULL OR
1700 p_pell_orig_int.origination_id_txt <> l_origination_id OR
1701 l_error IS NOT NULL
1702 THEN
1703 fnd_message.set_name('IGF','IGF_GR_LI_ORIG_INVALID');
1704 fnd_message.set_token('ORIG_ID',p_pell_orig_int.origination_id_txt);
1705 fnd_file.put_line(fnd_file.log, fnd_message.get);
1706 l_pell_import_status := 'E';
1707 END IF;
1708
1709
1710 -- Validate Pell Origianation Batch ID
1711 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1712 l_debug_str := l_debug_str || ', Origianation Batch ID : ' || p_pell_orig_int.orig_send_batch_id_txt;
1713 END IF;
1714
1715 l_orig_batch_id := '#O' || -- Batch Code
1716 igf_gr_gen.get_cycle_year(g_cal_type, g_seq_number) || -- Cycle year
1717 g_reporting_pell_id; --pell_setup.rep_pell_id; -- Reporting PELL ID
1718
1719 IF ( SUBSTR(p_pell_orig_int.orig_send_batch_id_txt, 0, 12) <> l_orig_batch_id ) OR
1720 ( (p_pell_orig_int.orig_status_code IN ('R','N')) AND
1721 (p_pell_orig_int.orig_send_batch_id_txt IS NOT NULL) )
1722 THEN
1723 fnd_message.set_name('IGF','IGF_GR_LI_OBID_INVALID');
1724 fnd_message.set_token('BATCH_ID',p_pell_orig_int.orig_send_batch_id_txt);
1725 fnd_file.put_line(fnd_file.log, fnd_message.get);
1726 l_pell_import_status := 'E';
1727 END IF;
1728
1729
1730 -- Validate Pell Cost of Attendance
1731 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1732 l_debug_str := l_debug_str || ', Pell COA : ' || p_pell_orig_int.pell_coa_amt;
1733 END IF;
1734
1735 IF (p_pell_orig_int.pell_coa_amt < 0) OR
1736 (p_pell_orig_int.pell_coa_amt > 9999999)
1737 THEN
1738 fnd_message.set_name('IGF','IGF_GR_LI_COA_INVALID');
1739 fnd_message.set_token('PELL_COA',p_pell_orig_int.pell_coa_amt);
1740 fnd_file.put_line(fnd_file.log, fnd_message.get);
1741 l_pell_import_status := 'E';
1742 END IF;
1743
1744
1745 -- Validate Pell Alternate Expenses
1746 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1747 l_debug_str := l_debug_str || ', Pell Alternate Expenses : ' || p_pell_orig_int.pell_alt_exp_amt;
1748 END IF;
1749
1750 IF (p_pell_orig_int.pell_alt_exp_amt < 0) OR
1751 (p_pell_orig_int.pell_alt_exp_amt > 9999999)
1752 THEN
1753 fnd_message.set_name('IGF','IGF_GR_LI_ALTEXP_INVALID');
1754 fnd_message.set_token('PELL_ALTEXP',p_pell_orig_int.pell_alt_exp_amt);
1755 fnd_file.put_line(fnd_file.log, fnd_message.get);
1756 l_pell_import_status := 'E';
1757 END IF;
1758
1759
1760 -- Validate Enrollment Status
1761 -- p_pell_orig_int.enrollment_status_flag NOT IN ('1','2','3','4','5') AND
1762 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1763 l_debug_str := l_debug_str || ', Enrollment Status : ' || p_pell_orig_int.enrollment_status_flag;
1764 END IF;
1765
1766 IF (p_pell_orig_int.enrollment_status_flag IS NOT NULL) AND
1767 (igf_ap_gen.get_aw_lookup_meaning('IGF_AP_ENRL_STAT',p_pell_orig_int.enrollment_status_flag, g_sys_award_year) IS NULL )
1768 THEN
1769 fnd_message.set_name('IGF','IGF_AP_INV_FLD_VAL');
1770 fnd_message.set_token('FIELD', 'ENROLLMENT_STATUS_FLAG');
1771 fnd_file.put_line(fnd_file.log, fnd_message.get);
1772 l_pell_import_status := 'E';
1773 END IF;
1774
1775 -- Validate Enrollment status with the system enrollment status
1776 -- FA 131. Prepare l_enrl_status variable to validate with orig record's value.
1777 OPEN cur_get_attendance_type_code(g_award_id);
1778 FETCH cur_get_attendance_type_code INTO rec_get_attendance_type_code;
1779
1780 IF (cur_get_attendance_type_code%NOTFOUND) THEN
1781 l_enrl_status := NULL;
1782 ELSE
1783 IF (cur_get_attendance_type_code%ROWCOUNT > 1) THEN
1784 l_enrl_status := '5'; -- 5 for Pell Attendance "Others"
1785 ELSIF (rec_get_attendance_type_code.base_attendance_type_code IS NULL) THEN
1786 -- cursor returned 1 row. And attendance_type_code is null
1787 l_enrl_status := '5'; -- 5 for Pell Attendance "Others"
1788 ELSE
1789 -- cursor returned 1 row. And attendance_type_code is not null
1790 l_enrl_status := rec_get_attendance_type_code.base_attendance_type_code;
1791 END IF;
1792 END IF;
1793 CLOSE cur_get_attendance_type_code;
1794 -- End FA 131
1795
1796 IF l_enrl_status IS NULL OR
1797 p_pell_orig_int.enrollment_status_flag <> l_enrl_status
1798 THEN
1799 fnd_message.set_name('IGF','IGF_GR_LI_ENRL_MISMTCH');
1800 fnd_message.set_token('SYS_ENRL_CODE', l_enrl_status );
1801 fnd_message.set_token('ENRL_CODE',p_pell_orig_int.enrollment_status_flag);
1802 fnd_file.put_line(fnd_file.log, fnd_message.get);
1803 IF l_pell_import_status <> 'E' THEN
1804 l_pell_import_status := 'W';
1805 END IF;
1806 END IF;
1807 -- End of Validate Enrollment Status
1808
1809
1810 -- Validate Pell EFC Amount for valid values
1811 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1812 l_debug_str := l_debug_str || ', EFC Amount : ' || p_pell_orig_int.efc_amt;
1813 END IF;
1814
1815 IF (p_pell_orig_int.efc_amt < 0) OR
1816 (p_pell_orig_int.efc_amt > 9999999)
1817 THEN
1818 fnd_message.set_name('IGF','IGF_GR_LI_EFC_INVALID');
1819 fnd_message.set_token('EFC',p_pell_orig_int.efc_amt);
1820 fnd_file.put_line(fnd_file.log, fnd_message.get);
1821 l_pell_import_status := 'E';
1822 END IF;
1823
1824
1825 -- Check Pell Amount with the ISIR Pell Amount
1826 IF (g_awd_yr_status_cd = 'O') AND
1827 (lc_isir_details.paid_efc <> p_pell_orig_int.efc_amt)
1828 THEN
1829
1830 fnd_message.set_name('IGF','IGF_GR_LI_EFC_MISMTCH');
1831 fnd_message.set_token('ISIR_EFC',lc_isir_details.paid_efc);
1832 fnd_message.set_token('EFC',p_pell_orig_int.efc_amt);
1833 fnd_file.put_line(fnd_file.log, fnd_message.get);
1834 IF l_pell_import_status <> 'E' THEN
1835 l_pell_import_status := 'W';
1836 END IF;
1837
1838 END IF;
1839
1840
1841 -- Validate Low Tution Fee Code
1842 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1843 l_debug_str := l_debug_str || ', Low Tution Fee Code : ' || p_pell_orig_int.low_tution_fee_cd;
1844 END IF;
1845
1846 IF (p_pell_orig_int.low_tution_fee_cd IS NOT NULL) AND
1847 (p_pell_orig_int.low_tution_fee_cd NOT IN ('1','2','3','4'))
1848 THEN
1849 fnd_message.set_name('IGF','IGF_AP_INV_FLD_VAL');
1850 fnd_message.set_token('FIELD', 'LOW_TUTION_FEE_CD');
1851 fnd_file.put_line(fnd_file.log, fnd_message.get);
1852 l_pell_import_status := 'E';
1853 END IF;
1854
1855
1856 -- Removed the following validation. FA 131.
1857 -- Validate Scheduled Pell Award for OPEN Award Year and Origination status should be 'Ready' or 'Not Ready'
1858
1859 -- Validate Pell Award
1860 IF p_pell_orig_int.pell_award_amt <> lc_awd_details.accepted_amt THEN
1861 fnd_message.set_name('IGF','IGF_GR_LI_PELL_AWD_INVALID');
1862 fnd_message.set_token('PELL_LMT', p_pell_orig_int.pell_award_amt);
1863 fnd_message.set_token('AWD_AMT', lc_awd_details.accepted_amt);
1864 fnd_file.put_line(fnd_file.log, fnd_message.get);
1865 l_pell_import_status := 'E';
1866
1867 END IF;
1868
1869
1870 -- Validate Payment Method
1871 -- p_pell_orig_int.payment_method_code NOT IN ('1','2','3','4','5')
1872 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1873 l_debug_str := l_debug_str || ', Payment Method : ' || p_pell_orig_int.payment_method_code;
1874 END IF;
1875
1876 IF igf_ap_gen.get_aw_lookup_meaning('IGF_GR_PAYMENT_METHOD', p_pell_orig_int.payment_method_code, g_sys_award_year) IS NULL THEN
1877 fnd_message.set_name('IGF','IGF_AP_INV_FLD_VAL');
1878 fnd_message.set_token('FIELD', 'PAYMENT_METHOD_CODE');
1879 fnd_file.put_line(fnd_file.log, fnd_message.get);
1880 l_pell_import_status := 'E';
1881 END IF;
1882
1883
1884 -- Validate Payment Weeks
1885 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1886 l_debug_str := l_debug_str || ', Payment Weeks : ' || p_pell_orig_int.wk_inst_time_calc_pymt_num;
1887 END IF;
1888
1889 IF p_pell_orig_int.payment_method_code = '1' AND
1890 p_pell_orig_int.wk_inst_time_calc_pymt_num IS NOT NULL
1891 THEN
1892 fnd_message.set_name('IGF','IGF_GR_LI_PMWK_INVALID');
1893 fnd_message.set_token('PYMT_WKS', p_pell_orig_int.wk_inst_time_calc_pymt_num);
1894 fnd_file.put_line(fnd_file.log, fnd_message.get);
1895 l_pell_import_status := 'E';
1896
1897 ELSIF p_pell_orig_int.payment_method_code IN ('2','3','4','5') AND
1898 p_pell_orig_int.wk_inst_time_calc_pymt_num IS NULL
1899 THEN
1900 fnd_message.set_name('IGF','IGF_GR_LI_PMWK_INVALID_1');
1901 fnd_file.put_line(fnd_file.log, fnd_message.get);
1902 l_pell_import_status := 'E';
1903
1904 END IF;
1905
1906
1907 -- Validate Academic Weeks
1908 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1909 l_debug_str := l_debug_str || ', Academic Weeks : ' || p_pell_orig_int.wk_int_time_prg_def_yr_num;
1910 END IF;
1911
1912 IF ( p_pell_orig_int.payment_method_code = '1' AND
1913 p_pell_orig_int.wk_int_time_prg_def_yr_num IS NOT NULL
1914 )
1915 OR
1916 (
1917 p_pell_orig_int.payment_method_code IN ('2','3','4','5') AND
1918 p_pell_orig_int.wk_int_time_prg_def_yr_num IS NULL
1919 )
1920 THEN
1921 fnd_message.set_name('IGF','IGF_AP_INV_FLD_VAL');
1922 fnd_message.set_token('FIELD', 'WK_INT_TIME_PRG_DEF_YR_NUM');
1923 fnd_file.put_line(fnd_file.log, fnd_message.get);
1924 l_pell_import_status := 'E';
1925 END IF;
1926
1927
1928 -- Validate Academic Calendar
1929 -- p_pell_orig_int.academic_calendar_cd NOT IN ('1','2','3','4','5','6')
1930 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1931 l_debug_str := l_debug_str || ', Academic Calendar : ' || p_pell_orig_int.academic_calendar_cd;
1932 END IF;
1933
1934 IF igf_ap_gen.get_aw_lookup_meaning('IGF_GR_ACAD_CAL', p_pell_orig_int.academic_calendar_cd, g_sys_award_year) IS NULL THEN
1935 fnd_message.set_name('IGF','IGF_AP_INV_FLD_VAL');
1936 fnd_message.set_token('FIELD', 'ACADEMIC_CALENDAR_CD');
1937 fnd_file.put_line(fnd_file.log, fnd_message.get);
1938 l_pell_import_status := 'E';
1939 END IF;
1940
1941
1942 -- Validate Expected Hours
1943 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1944 l_debug_str := l_debug_str || ', Expected Hours : ' || p_pell_orig_int.cr_clk_hrs_prds_sch_yr_num;
1945 END IF;
1946
1947 IF ( p_pell_orig_int.academic_calendar_cd IN ('1','2','3','4') AND p_pell_orig_int.cr_clk_hrs_prds_sch_yr_num IS NOT NULL ) OR
1948 ( p_pell_orig_int.academic_calendar_cd IN ('5','6') AND p_pell_orig_int.cr_clk_hrs_prds_sch_yr_num IS NULL )
1949 THEN
1950 fnd_message.set_name('IGF','IGF_GR_LI_EXHR_INVALID');
1951 fnd_message.set_token('EXP_HRS',p_pell_orig_int.cr_clk_hrs_prds_sch_yr_num);
1952 fnd_file.put_line(fnd_file.log, fnd_message.get);
1953 l_pell_import_status := 'E';
1954 END IF;
1955
1956
1957 -- Validate Academic hours
1958 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1959 l_debug_str := l_debug_str || ', Academic Hours : ' || p_pell_orig_int.cr_clk_hrs_acad_yr_num;
1960 END IF;
1961
1962 IF ( p_pell_orig_int.academic_calendar_cd IN ('1','2','3','4') AND p_pell_orig_int.cr_clk_hrs_acad_yr_num IS NOT NULL )OR
1963 ( p_pell_orig_int.academic_calendar_cd IN ('5','6') AND p_pell_orig_int.cr_clk_hrs_acad_yr_num IS NULL )
1964 THEN
1965 fnd_message.set_name('IGF','IGF_AP_INV_FLD_VAL');
1966 fnd_message.set_token('FIELD', 'CR_CLK_HRS_ACAD_YR_NUM');
1967 fnd_file.put_line(fnd_file.log, fnd_message.get);
1968 l_pell_import_status := 'E';
1969 END IF;
1970
1971
1972 -- Validate Verification Status
1973 -- ( g_sys_award_year = '0203' AND p_pell_orig_int.verification_status_code NOT IN ('W','V') ) OR
1974 -- ( g_sys_award_year = '0304' AND p_pell_orig_int.verification_status_code NOT IN ('W','V','S') )
1975 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1976 l_debug_str := l_debug_str || ', Verification Status : ' || p_pell_orig_int.verification_status_code;
1977 END IF;
1978
1979 IF (p_pell_orig_int.verification_status_code IS NOT NULL) AND
1980 (igf_ap_gen.get_aw_lookup_meaning('IGF_GR_VER_STAT_CD', p_pell_orig_int.verification_status_code, g_sys_award_year) IS NULL )
1981 THEN
1982 fnd_message.set_name('IGF','IGF_AP_INV_FLD_VAL');
1983 fnd_message.set_token('FIELD', 'VERIFICATION_STATUS_CODE');
1984 fnd_file.put_line(fnd_file.log, fnd_message.get);
1985 l_pell_import_status := 'E';
1986 END IF;
1987
1988
1989 -- Validate Incarcerated Code
1990 -- IF p_pell_orig_int.incrcd_fed_pell_rcp_code NOT IN ('Y','N') THEN
1991 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
1992 l_debug_str := l_debug_str || ', Incarcerated Code : ' || p_pell_orig_int.incrcd_fed_pell_rcp_code;
1993 END IF;
1994
1995 IF (p_pell_orig_int.incrcd_fed_pell_rcp_code IS NOT NULL) AND
1996 (igf_ap_gen.get_aw_lookup_meaning('IGF_GR_STU_INCARCE_STATUS', p_pell_orig_int.incrcd_fed_pell_rcp_code, g_sys_award_year) IS NULL )
1997 THEN
1998 fnd_message.set_name('IGF','IGF_AP_INV_FLD_VAL');
1999 fnd_message.set_token('FIELD', 'INCRCD_FED_PELL_RCP_CODE');
2000 fnd_file.put_line(fnd_file.log, fnd_message.get);
2001 l_pell_import_status := 'E';
2002 END IF;
2003
2004
2005 -- Validate Secondary EFC Code
2006 -- IF p_pell_orig_int.secondary_efc_code NOT IN ('O','S') THEN
2007 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2008 l_debug_str := l_debug_str || ', Secondary EFC Code : ' || p_pell_orig_int.secondary_efc_code;
2009 END IF;
2010
2011 IF (p_pell_orig_int.secondary_efc_code IS NOT NULL) AND
2012 (igf_ap_gen.get_aw_lookup_meaning('IGF_GR_SEC_EFC_CD', p_pell_orig_int.secondary_efc_code, g_sys_award_year) IS NULL )
2013 THEN
2014 fnd_message.set_name('IGF','IGF_AP_INV_FLD_VAL');
2015 fnd_message.set_token('FIELD', 'SECONDARY_EFC_CODE');
2016 fnd_file.put_line(fnd_file.log, fnd_message.get);
2017 l_pell_import_status := 'E';
2018 END IF;
2019
2020 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2021 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_gr_li_import.import_pell_orig.debug', l_debug_str);
2022 l_debug_str := 'IGFGR10B.pls Procedure import_pell_orig (2) :: ';
2023 END IF;
2024
2025
2026 -- Validate Previous Transaction Number
2027 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2028 l_debug_str := l_debug_str || ', Previous Transaction Number : ' || p_pell_orig_int.prev_accpt_tran_num_txt;
2029 END IF;
2030
2031 IF (p_pell_orig_int.prev_accpt_tran_num_txt IS NOT NULL) AND
2032 (TO_NUMBER(p_pell_orig_int.prev_accpt_tran_num_txt) < 1 OR TO_NUMBER(p_pell_orig_int.prev_accpt_tran_num_txt) > 99 )
2033 THEN
2034 fnd_message.set_name('IGF','IGF_GR_LI_PRNT_INVALID_1');
2035 fnd_message.set_token('PR_TNUM',p_pell_orig_int.prev_accpt_tran_num_txt);
2036 fnd_file.put_line(fnd_file.log, fnd_message.get);
2037 l_pell_import_status := 'E';
2038
2039 ELSIF ( (p_pell_orig_int.orig_status_code IN ('N','R','A','C')) AND
2040 (p_pell_orig_int.prev_accpt_tran_num_txt IS NOT NULL) )
2041 THEN
2042 fnd_message.set_name('IGF','IGF_GR_LI_PRNT_INVALID_2');
2043 fnd_message.set_token('PR_TNUM',p_pell_orig_int.prev_accpt_tran_num_txt);
2044 fnd_file.put_line(fnd_file.log, fnd_message.get);
2045 l_pell_import_status := 'E';
2046
2047 END IF;
2048
2049
2050 -- Validate Previous EFC
2051 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2052 l_debug_str := l_debug_str || ', Previous EFC : ' || p_pell_orig_int.prev_accpt_efc_amt;
2053 END IF;
2054
2055 IF (p_pell_orig_int.prev_accpt_efc_amt < 0) OR
2056 (p_pell_orig_int.prev_accpt_efc_amt > 9999999)
2057 THEN
2058 fnd_message.set_name('IGF','IGF_GR_LI_PSEF_INVALID_1');
2059 fnd_message.set_token('PR_SEC_EFC',p_pell_orig_int.prev_accpt_efc_amt);
2060 fnd_file.put_line(fnd_file.log, fnd_message.get);
2061 l_pell_import_status := 'E';
2062
2063 ELSIF ( (p_pell_orig_int.orig_status_code IN ('N','R','A','C')) AND
2064 (p_pell_orig_int.prev_accpt_efc_amt IS NOT NULL) )
2065 THEN
2066 fnd_message.set_name('IGF','IGF_GR_LI_PSEF_INVALID_2');
2067 fnd_message.set_token('PR_SEC_EFC',p_pell_orig_int.prev_accpt_efc_amt);
2068 fnd_file.put_line(fnd_file.log, fnd_message.get);
2069 l_pell_import_status := 'E';
2070
2071 END IF;
2072
2073
2074 -- Validate Previous Secondary EFC
2075 -- IF p_pell_orig_int.prev_accpt_sec_efc_cd NOT IN ('O','S') THEN
2076 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2077 l_debug_str := l_debug_str || ', Previous Secondary EFC : ' || p_pell_orig_int.prev_accpt_sec_efc_cd;
2078 END IF;
2079
2080 IF (p_pell_orig_int.prev_accpt_sec_efc_cd IS NOT NULL) AND
2081 (igf_ap_gen.get_aw_lookup_meaning('IGF_GR_SEC_EFC_CD', p_pell_orig_int.prev_accpt_sec_efc_cd, g_sys_award_year) IS NULL )
2082 THEN
2083 fnd_message.set_name('IGF','IGF_AP_INV_FLD_VAL');
2084 fnd_message.set_token('FIELD', 'PREV_ACCPT_SEC_EFC_CD');
2085 fnd_file.put_line(fnd_file.log, fnd_message.get);
2086 l_pell_import_status := 'E';
2087
2088 ELSIF ( (p_pell_orig_int.orig_status_code IN ('N','R','A','C')) AND
2089 (p_pell_orig_int.prev_accpt_sec_efc_cd IS NOT NULL) )
2090 THEN
2091 fnd_message.set_name('IGF','IGF_GR_LI_PSEFCD_INVALID_2');
2092 fnd_message.set_token('PR_SEC_EFC',p_pell_orig_int.prev_accpt_sec_efc_cd);
2093 fnd_file.put_line(fnd_file.log, fnd_message.get);
2094 l_pell_import_status := 'E';
2095
2096 END IF;
2097
2098
2099 -- Validate Previous COA
2100 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2101 l_debug_str := l_debug_str || ', Previous COA : ' || p_pell_orig_int.prev_accpt_coa_amt;
2102 END IF;
2103
2104 IF p_pell_orig_int.prev_accpt_coa_amt < 0 OR p_pell_orig_int.prev_accpt_coa_amt < 9999999 THEN
2105 fnd_message.set_name('IGF','IGF_GR_LI_PCOA_INVALID_1');
2106 fnd_message.set_token('PR_COA',p_pell_orig_int.prev_accpt_coa_amt);
2107 fnd_file.put_line(fnd_file.log, fnd_message.get);
2108 l_pell_import_status := 'E';
2109
2110 ELSIF ( (p_pell_orig_int.orig_status_code IN ('N','R','A','C')) AND
2111 (p_pell_orig_int.prev_accpt_coa_amt IS NOT NULL) )
2112 THEN
2113 fnd_message.set_name('IGF','IGF_GR_LI_PCOA_INVALID_2');
2114 fnd_message.set_token('PR_COA',p_pell_orig_int.prev_accpt_coa_amt);
2115 fnd_file.put_line(fnd_file.log, fnd_message.get);
2116 l_pell_import_status := 'E';
2117
2118 END IF;
2119
2120
2121 -- Validate Process Date
2122 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2123 l_debug_str := l_debug_str || ', Previous Date : ' || p_pell_orig_int.rfms_process_date;
2124 END IF;
2125
2126 IF ( (p_pell_orig_int.orig_status_code IN ('A','C','E')) AND
2127 (p_pell_orig_int.rfms_process_date IS NULL) )
2128 THEN
2129 fnd_message.set_name('IGF','IGF_GR_LI_OPRDT_INVALID_1');
2130 fnd_file.put_line(fnd_file.log, fnd_message.get);
2131 l_pell_import_status := 'E';
2132
2133 ELSIF ( (p_pell_orig_int.orig_status_code IN ('A','C','E')) AND
2134 (TRUNC(p_pell_orig_int.rfms_process_date) > TRUNC(sysdate)) )
2135 THEN
2136 fnd_message.set_name('IGF','IGF_GR_LI_OPRDT_INVALID_2');
2137 fnd_message.set_token('PROC_DATE',p_pell_orig_int.rfms_process_date);
2138 fnd_file.put_line(fnd_file.log, fnd_message.get);
2139 l_pell_import_status := 'E';
2140
2141 ELSIF ( (p_pell_orig_int.orig_status_code IN ('N','R')) AND
2142 (p_pell_orig_int.rfms_process_date IS NOT NULL) )
2143 THEN
2144 fnd_message.set_name('IGF','IGF_GR_LI_OPRDT_INVALID_3');
2145 fnd_message.set_token('PROC_DATE',p_pell_orig_int.rfms_process_date);
2146 fnd_file.put_line(fnd_file.log, fnd_message.get);
2147 l_pell_import_status := 'E';
2148
2149 END IF;
2150
2151
2152 -- Validate ED Use Flags
2153 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2154 l_debug_str := l_debug_str || ', ED Use Flags : ' || p_pell_orig_int.orig_ed_use_flags_txt;
2155 END IF;
2156
2157 IF ( (p_pell_orig_int.orig_status_code IN ('R','N')) AND
2158 (p_pell_orig_int.orig_ed_use_flags_txt IS NOT NULL) )
2159 THEN
2160 fnd_message.set_name('IGF','IGF_GR_LI_OEDFL_INVALID_2');
2161 fnd_message.set_token('ED_FLAG',p_pell_orig_int.orig_ed_use_flags_txt);
2162 fnd_file.put_line(fnd_file.log, fnd_message.get);
2163 l_pell_import_status := 'E';
2164 END IF;
2165
2166
2167 -- Validate Warning Codes
2168 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2169 l_debug_str := l_debug_str || ', Warning Codes : ' || p_pell_orig_int.orig_reject_codes_txt;
2170 END IF;
2171
2172 IF ( (p_pell_orig_int.orig_status_code IN ('R','N')) AND
2173 (p_pell_orig_int.orig_reject_codes_txt IS NOT NULL) )
2174 THEN
2175 fnd_message.set_name('IGF','IGF_GR_LI_OEDCD_INVALID_2');
2176 fnd_message.set_token('EDIT_CODE',p_pell_orig_int.orig_reject_codes_txt);
2177 fnd_file.put_line(fnd_file.log, fnd_message.get);
2178 l_pell_import_status := 'E';
2179 END IF;
2180
2181
2182 -- If the validations are passed thru, then created the PELL Batch record and Pell Origination (If error should not import records)
2183 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2184 l_debug_str := l_debug_str || ' :: After all Validations : Import status : ' || l_pell_import_status;
2185 END IF;
2186
2187 IF l_pell_import_status <> 'E' THEN
2188
2189 -- Create Pell Batch record (Do not create batch records for ready status and not ready statuses)
2190 l_rfmb_id := NULL;
2191 IF ( (p_pell_orig_int.orig_status_code IS NOT NULL) AND
2192 (p_pell_orig_int.orig_status_code NOT IN ('R','N')) )
2193 THEN
2194
2195 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2196 l_debug_str := l_debug_str || ' :: Creating Pell Orig Batch : ';
2197 END IF;
2198 l_rfmb_id := create_pell_orig_batch(p_pell_orig_int);
2199
2200 IF l_rfmb_id = -1 THEN
2201 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2202 l_debug_str := l_debug_str || ' : Pell Orig Batch not created';
2203 END IF;
2204 l_pell_import_status := 'E';
2205 ROLLBACK TO SP_PELL;
2206 RETURN l_pell_import_status;
2207 END IF;
2208
2209 END IF;
2210
2211 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2212 l_debug_str := l_debug_str || ' Pell Orig Batch ID : '|| l_rfmb_id || ' :: Creating Pell Orig rec : ';
2213 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_gr_li_import.import_pell_orig.debug', l_debug_str);
2214 l_debug_str := NULL;
2215 END IF;
2216
2217 -- Create Pell Record
2218 IF NOT create_pell_orig(p_pell_orig_int, lc_isir_details, l_rfmb_id) THEN
2219 fnd_message.set_name('IGF','IGF_GR_LI_OINSERT_FAIL');
2220 fnd_message.set_token('ORIG_ID',p_pell_orig_int.origination_id_txt);
2221 fnd_file.put_line(fnd_file.log, fnd_message.get);
2222 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2223 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_gr_li_import.import_pell_orig.debug', ' : Pell Orig rec not created');
2224 END IF;
2225 l_pell_import_status := 'E';
2226 ROLLBACK TO SP_PELL;
2227 RETURN l_pell_import_status;
2228 END IF;
2229
2230 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2231 l_debug_str := ' :: Creating Pell Disbursements : ';
2232 END IF;
2233
2234 l_disb_num_prmpt := igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','DISBURSEMENT_NUMBER');
2235 l_processing := igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','PROCESSING');
2236 l_pell_disb_full_status := 'I';
2237 l_pell_disb_indv_status := 'I';
2238
2239 -- Validate and Import Pell Disbursement
2240 FOR lc_pell_disb_int IN c_pell_disb_int(p_pell_orig_int.ci_alternate_code, p_pell_orig_int.person_number, p_pell_orig_int.award_number_txt, p_pell_orig_int.origination_id_txt ) LOOP
2241
2242 -- Log context information of the Pell Disbursment
2243 fnd_file.put_line(fnd_file.log, ' ');
2244 fnd_file.put_line(fnd_file.log, g_disb_pad_str || l_processing ||' '|| l_disb_num_prmpt ||' : '|| lc_pell_disb_int.disbursement_num);
2245
2246
2247 -- Import Pell Disbursement
2248 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2249 l_debug_str := ' Disb num : ' || lc_pell_disb_int.disbursement_num;
2250 END IF;
2251
2252 l_pell_disb_indv_status := 'E';
2253 l_pell_disb_indv_status := import_pell_disb(lc_pell_disb_int, p_pell_orig_int.ope_cd);
2254
2255 -- Update the full disbursement status
2256 IF(l_pell_disb_indv_status = 'I' AND (l_pell_disb_full_status NOT IN ('W','E')))THEN
2257 l_pell_disb_full_status := l_pell_disb_indv_status;
2258
2259 ELSIF(l_pell_disb_indv_status = 'W' AND l_pell_disb_full_status <> 'E')THEN
2260 l_pell_disb_full_status := l_pell_disb_indv_status;
2261
2262 ELSIF(l_pell_disb_indv_status = 'E')THEN
2263 l_pell_disb_full_status := l_pell_disb_indv_status;
2264
2265 -- Log an error message that Pell Import is unsuccessful
2266 fnd_message.set_name('IGF','IGF_GR_LI_DINSERT_FAIL');
2267 fnd_message.set_token('ORIG_ID',lc_pell_disb_int.origination_id_txt);
2268 fnd_message.set_token('DISB_NUM',lc_pell_disb_int.disbursement_num);
2269 fnd_file.put_line(fnd_file.log, g_disb_pad_str || fnd_message.get);
2270 END IF;
2271
2272 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2273 l_debug_str := l_debug_str || ', Disb Status : ' || l_pell_disb_indv_status;
2274 l_debug_str := l_debug_str || ', l_pell_disb_full_status : '|| l_pell_disb_full_status ||', l_pell_disb_indv_status : '|| l_pell_disb_indv_status;
2275 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_gr_li_import.import_pell_orig.debug', l_debug_str);
2276 l_debug_str := NULL;
2277 END IF;
2278
2279 END LOOP; -- End of creation of Disbursments
2280
2281
2282 -- Update the Pell status based on the disb full status
2283 IF(l_pell_disb_full_status = 'I' AND (l_pell_import_status NOT IN ('W','E')))THEN
2284 l_pell_import_status := l_pell_disb_full_status;
2285 ELSIF(l_pell_disb_full_status = 'W' AND l_pell_import_status <> 'E')THEN
2286 l_pell_import_status := l_pell_disb_full_status;
2287 ELSIF(l_pell_disb_full_status = 'E')THEN
2288 l_pell_import_status := l_pell_disb_full_status;
2289 END IF;
2290
2291
2292 -- If imported with errors then rollback to the Pell and return with the status 'E'
2293 IF l_pell_import_status = 'E' THEN
2294
2295 ROLLBACK TO SP_PELL;
2296 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2297 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_gr_li_import.import_pell_orig.debug', l_debug_str || ' End of Pell Orig Import, return status : '|| l_pell_import_status);
2298 l_debug_str := NULL;
2299 END IF;
2300
2301 RETURN l_pell_import_status;
2302
2303 END IF;
2304
2305
2306 -- Import the remaining disbursements only if Pell Disb imported successfully from Int table
2307 IF l_pell_import_status <> 'E' THEN
2308
2309 -- Create the remaining disbursements which are present in the Awd Disb table and not present in the Pell Disb table
2310 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2311 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_gr_li_import.import_pell_orig.debug','Processing Remaining disb present in the Production Awd Disb : ');
2312 END IF;
2313
2314 ln_rem_disb_cnt := 0;
2315 FOR lc_get_remain_disb IN c_get_remain_disb(g_award_id) LOOP
2316
2317 BEGIN
2318
2319 IF ln_rem_disb_cnt = 0 THEN
2320 -- Log a message for processing remaining disbursements.
2321 fnd_message.set_name('IGF','IGF_GR_LI_REMG_DISB');
2322 fnd_file.put_line(fnd_file.log, g_disb_pad_str || fnd_message.get);
2323 END IF;
2324 ln_rem_disb_cnt := ln_rem_disb_cnt + 1;
2325
2326
2327 -- Print the log information
2328 fnd_file.put_line(fnd_file.log, ' ');
2329 fnd_file.put_line(fnd_file.log, g_disb_pad_str || l_processing ||' '|| l_disb_num_prmpt ||' : '|| lc_get_remain_disb.disb_num);
2330
2331 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2332
2333 l_debug_str := l_debug_str || ' Remaining Disb Num : '|| lc_get_remain_disb.disb_num;
2334 END IF;
2335
2336 -- Set the dibursement Sign Flag based on the Disb Accepted amount
2337 IF lc_get_remain_disb.disb_accepted_amt >= 0 THEN
2338 l_db_cr_flag := 'P';
2339 ELSE
2340 l_db_cr_flag := 'N';
2341 END IF;
2342
2343
2344 -- Create Pell Origination Record in the production table
2345 l_row_id := NULL;
2346 l_rfmd_id := NULL;
2347 igf_gr_rfms_disb_pkg.insert_row(
2348 x_mode => 'R',
2349 x_rowid => l_row_id,
2350 x_rfmd_id => l_rfmd_id,
2351 x_origination_id => p_pell_orig_int.origination_id_txt,
2352 x_disb_ref_num => lc_get_remain_disb.disb_num,
2353 x_disb_dt => lc_get_remain_disb.disb_date,
2354 x_disb_amt => lc_get_remain_disb.disb_accepted_amt,
2355 x_db_cr_flag => l_db_cr_flag,
2356 x_disb_ack_act_status => 'R',
2357 x_disb_status_dt => TRUNC(SYSDATE),
2358 x_accpt_disb_dt => NULL,
2359 x_disb_accpt_amt => NULL,
2360 x_accpt_db_cr_flag => NULL,
2361 x_disb_ytd_amt => NULL,
2362 x_pymt_prd_start_dt => NULL,
2363 x_accpt_pymt_prd_start_dt => NULL,
2364 x_edit_code => NULL,
2365 x_rfmb_id => NULL,
2366 x_ed_use_flags => NULL
2367 );
2368 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2369 l_debug_str := l_debug_str || ', Created Disb successful rfmd_id: '|| l_rfmd_id;
2370 END IF;
2371
2372 EXCEPTION
2373 WHEN OTHERS THEN
2374 fnd_message.set_name('IGF','IGF_GR_LI_REM_DINS_FAIL');
2375 fnd_message.set_token('ORIG_ID',p_pell_orig_int.origination_id_txt);
2376 fnd_message.set_token('DISB_NUM',lc_get_remain_disb.disb_num);
2377 fnd_file.put_line(fnd_file.log, g_disb_pad_str || fnd_message.get);
2378 END;
2379
2380 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2381 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_gr_li_import.import_pell_orig.debug', l_debug_str);
2382 l_debug_str := NULL;
2383 END IF;
2384
2385 END LOOP; -- End of remaining disbs
2386
2387 END IF; -- End of remaining disb
2388
2389 END IF; -- End of Error status <> 'E' to Import Pell Disb
2390
2391 -- Return to the main routine
2392 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2393 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_gr_li_import.import_pell_orig.debug', l_debug_str || ' End of Pell Orig Import, return status : '|| l_pell_import_status);
2394 l_debug_str := NULL;
2395 END IF;
2396
2397 RETURN l_pell_import_status;
2398
2399 EXCEPTION
2400
2401 WHEN OTHERS THEN
2402 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
2403 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'igf.plsql.igf_gr_li_import.import_pell_orig.exception', l_debug_str || SQLERRM );
2404 END IF;
2405 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2406 fnd_message.set_token('NAME','IGF_GR_LI_IMPORT.IMPORT_PELL_ORIG');
2407 igs_ge_msg_stack.add;
2408
2409 END import_pell_orig;
2410
2411 FUNCTION chk_atd_rep( p_atd_entity_id_txt VARCHAR2, p_rep_entity_id_txt VARCHAR2)
2412 RETURN BOOLEAN
2413 IS
2414 /*
2415 || Created By : pssahni
2416 || Created On : 29-Oct-2004
2417 || Purpose : To validate the combination of attending and reporting pell ids
2418 || Known limitations, enhancements or remarks :
2419 || Change History :
2420 || Who When What
2421 || (reverse chronological order - newest change first)
2422 */
2423
2424 CURSOR c_chk_atd_rep_comb (p_atd_entity_id_txt VARCHAR2, p_rep_entity_id_txt VARCHAR2)
2425 IS
2426 SELECT atd.atd_entity_id_txt, rep.rep_entity_id_txt
2427 FROM igf_gr_report_pell rep, igf_gr_attend_pell atd
2428 WHERE rep.rcampus_id = atd.rcampus_id
2429 AND atd.atd_entity_id_txt = p_atd_entity_id_txt
2430 AND rep.rep_entity_id_txt = p_rep_entity_id_txt;
2431
2432 chk_atd_rep_comb_rec c_chk_atd_rep_comb%ROWTYPE;
2433
2434
2435 l_office_cd igs_or_org_alt_ids.org_alternate_id_type%TYPE;
2436 l_ret_status VARCHAR2(1);
2437 l_msg_data VARCHAR2(30);
2438 BEGIN
2439 IF (p_atd_entity_id_txt IS NULL)OR (p_rep_entity_id_txt IS NULL) THEN
2440 -- If anyone is null then raise error
2441 fnd_message.set_name('IGF','IGF_SL_ATD_REP_PELL_NOT_CORR');
2442 fnd_file.put_line(fnd_file.log, fnd_message.get);
2443 RETURN FALSE;
2444 ELSE
2445 -- Check if their combination is valid
2446
2447 OPEN c_chk_atd_rep_comb (p_atd_entity_id_txt, p_rep_entity_id_txt );
2448 FETCH c_chk_atd_rep_comb INTO chk_atd_rep_comb_rec;
2449
2450 IF c_chk_atd_rep_comb%NOTFOUND THEN
2451 fnd_message.set_name('IGF','IGF_SL_ATD_REP_PELL_NOT_CORR');
2452 fnd_file.put_line(fnd_file.log, fnd_message.get);
2453 RETURN FALSE;
2454 END IF;
2455
2456 CLOSE c_chk_atd_rep_comb;
2457
2458 END IF;
2459 g_atd_entity_id_txt := p_atd_entity_id_txt;
2460 RETURN TRUE;
2461
2462 END chk_atd_rep;
2463
2464
2465 PROCEDURE main(
2466 errbuf OUT NOCOPY VARCHAR2,
2467 retcode OUT NOCOPY NUMBER,
2468 p_award_year IN VARCHAR2,
2469 p_batch_num IN NUMBER,
2470 p_delete_flag IN VARCHAR2
2471 ) AS
2472 /*
2473 || Created By : brajendr
2474 || Created On : 18-Jun-2003
2475 || Purpose : Main process imports the Pell data from the Legacy Pell interface table
2476 || Known limitations, enhancements or remarks :
2477 || Change History :
2478 || Who When What
2479 || tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
2480 || bvisvana 07-July-2005 Bug # 4008991 - IGF_GR_BATCH_DOES_NOT_EXIST replaced by IGF_SL_GR_BATCH_DOES_NO_EXIST
2481 || (reverse chronological order - newest change first)
2482 */
2483
2484 -- Cursor to fetch alternate code for the given Cal Type and Sequence Number
2485 CURSOR c_get_alternate_code(
2486 cp_cal_type igs_ca_inst_all.cal_type%TYPE,
2487 cp_seq_number igs_ca_inst_all.sequence_number%TYPE
2488 ) IS
2489 SELECT ca.alternate_code
2490 FROM igs_ca_inst_all ca
2491 WHERE ca.cal_type = cp_cal_type
2492 AND ca.sequence_number = cp_seq_number;
2493
2494 lc_get_alternate_code c_get_alternate_code%ROWTYPE;
2495
2496 -- cursor to verify if the cal_type and seq_number are present in the system award year
2497 CURSOR c_sys_awd_yr_dtls(
2498 cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
2499 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE
2500 ) IS
2501 SELECT bam.alternate_code, bam.award_year_status_code, bam.sys_award_year
2502 FROM igf_ap_batch_aw_map_v bam
2503 WHERE bam.ci_cal_type = cp_ci_cal_type
2504 AND bam.ci_sequence_number = cp_ci_sequence_number;
2505
2506 lc_sys_awd_yr_dtls c_sys_awd_yr_dtls%ROWTYPE;
2507
2508 -- Get the details of
2509 CURSOR c_check_setups(
2510 cp_ci_cal_type igs_ca_inst.cal_type%TYPE,
2511 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE
2512 ) IS
2513 SELECT 'x'
2514 FROM DUAL
2515 WHERE EXISTS(
2516 SELECT 'x'
2517 FROM igf_gr_reg_amts reg, igf_ap_batch_aw_map_all batch
2518 WHERE batch.ci_cal_type = cp_ci_cal_type
2519 AND batch.ci_sequence_number = cp_ci_sequence_number
2520 AND batch.sys_award_year = reg.sys_awd_yr
2521 )
2522 AND EXISTS(
2523 SELECT 'X'
2524 FROM igf_gr_alt_amts alt, igf_ap_batch_aw_map_all batch
2525 WHERE batch.ci_cal_type = cp_ci_cal_type
2526 AND batch.ci_sequence_number = cp_ci_sequence_number
2527 AND alt.sys_awd_yr = batch.sys_award_year
2528 )
2529 AND EXISTS(
2530 SELECT 'X'
2531 FROM igf_gr_tuition_fee_codes tfee, igf_ap_batch_aw_map_all batch
2532 WHERE batch.ci_cal_type = cp_ci_cal_type
2533 AND batch.ci_sequence_number = cp_ci_sequence_number
2534 AND batch.sys_award_year = tfee.sys_awd_yr
2535 )
2536 AND EXISTS(
2537 SELECT 'X'
2538 FROM igf_ap_attend_map_all atm
2539 WHERE atm.cal_type IS NOT NULL
2540 AND atm.sequence_number IS NOT NULL
2541 AND atm.cal_type = cp_ci_cal_type
2542 AND atm.sequence_number = cp_ci_sequence_number);
2543
2544 lc_pell_orig_int c_pell_orig_int%ROWTYPE;
2545 lc_pell_disb_int c_pell_disb_int%ROWTYPE;
2546 l_prev_person_number hz_parties.party_number%TYPE;
2547 l_processing igf_lookups_view.meaning%TYPE;
2548 l_person_number igf_lookups_view.meaning%TYPE;
2549 l_award_number igf_lookups_view.meaning%TYPE;
2550 l_debug_str fnd_log_messages.message_text%TYPE := 'IGFGR10B.pls Procedure main :: ';
2551 l_pell_import_status VARCHAR2(1);
2552 l_chk_batch VARCHAR2(1);
2553 l_chk_setups VARCHAR2(1);
2554 l_chk_profile VARCHAR2(1);
2555 l_delete_status VARCHAR2(1);
2556 l_fabase_ret_status VARCHAR2(1);
2557 SKIP_RECORD EXCEPTION;
2558
2559
2560
2561
2562 BEGIN
2563 igf_aw_gen.set_org_id(NULL);
2564 -- Initialize the global variables
2565 g_cal_type := TRIM(SUBSTR(p_award_year,1,10));
2566 g_seq_number := TO_NUMBER(SUBSTR(p_award_year,11));
2567 g_delete_flag := p_delete_flag;
2568 errbuf := NULL;
2569 retcode := 0;
2570
2571 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2572 l_debug_str := l_debug_str || ', Cal Type : ' || g_cal_type;
2573 l_debug_str := l_debug_str || ', Sequence Num : ' || g_seq_number;
2574
2575 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_gr_li_import.main.debug', ' ---- Request ID : '||fnd_global.conc_request_id
2576 ||' Conc Prgm Id : '||fnd_global.conc_program_id
2577 ||' Pgm appl Id : '||fnd_global.prog_appl_id
2578 ||' ----');
2579 END IF;
2580
2581 OPEN c_get_alternate_code(g_cal_type,g_seq_number);
2582 FETCH c_get_alternate_code INTO lc_get_alternate_code;
2583 CLOSE c_get_alternate_code;
2584
2585 -- Log the input paramters.
2586 log_parameters( lc_get_alternate_code.alternate_code, p_batch_num, p_delete_flag );
2587
2588
2589 -- Verify whether the school is configured for US School and uses Financial Aid.
2590 -- If not participating then log an error message
2591 l_chk_profile := igf_ap_gen.check_profile();
2592 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2593 l_debug_str := l_debug_str || ', Checking Profile value : ' || l_chk_profile;
2594 END IF;
2595
2596 IF NVL(l_chk_profile, 'N') <> 'Y' THEN
2597 fnd_message.set_name('IGF','IGF_AP_LGCY_PROC_NOT_RUN');
2598 fnd_file.put_line(fnd_file.log, fnd_message.get);
2599 RETURN;
2600 END IF;
2601
2602 -- Validate the Batch Details
2603 l_chk_batch := 'Y';
2604 l_chk_batch := igf_ap_gen.check_batch(p_batch_num, 'GRANTS');
2605 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2606 l_debug_str := l_debug_str || ', Checking Batch : ' || l_chk_batch;
2607 END IF;
2608
2609 IF l_chk_batch = 'N' THEN
2610 fnd_message.set_name('IGF','IGF_SL_GR_BATCH_DOES_NO_EXIST');
2611 fnd_message.set_token('BATCH_ID',p_batch_num);
2612 fnd_file.put_line(fnd_file.log, fnd_message.get);
2613 RETURN;
2614 END IF;
2615
2616 -- Get the alternate code for the calendar details
2617 OPEN c_sys_awd_yr_dtls(g_cal_type, g_seq_number);
2618 FETCH c_sys_awd_yr_dtls INTO lc_sys_awd_yr_dtls;
2619 IF c_sys_awd_yr_dtls%NOTFOUND THEN
2620 CLOSE c_sys_awd_yr_dtls;
2621 FND_MESSAGE.SET_NAME('IGF','IGF_AP_AWD_YR_NOT_FOUND');
2622 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
2623 retcode := 2;
2624 RETURN;
2625 END IF;
2626 CLOSE c_sys_awd_yr_dtls;
2627
2628 -- Validate Alternate code
2629 -- Check whether the import is for OPEN or Legacy Details award year or not.
2630 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2631 l_debug_str := l_debug_str || ', Validating Alternate Code Awd Yr Cd : ' || lc_sys_awd_yr_dtls.award_year_status_code;
2632 l_debug_str := l_debug_str || ', Sys Awd Yr : ' || lc_sys_awd_yr_dtls.sys_award_year;
2633 END IF;
2634
2635 IF lc_sys_awd_yr_dtls.award_year_status_code IS NULL OR lc_sys_awd_yr_dtls.award_year_status_code NOT IN ('O','LD') THEN
2636 fnd_message.set_name('IGF','IGF_AP_LG_INVALID_STAT');
2637 fnd_message.set_token('AWARD_STATUS',igf_ap_gen.get_aw_lookup_meaning('IGF_AWARD_YEAR_STATUS', lc_sys_awd_yr_dtls.award_year_status_code, lc_sys_awd_yr_dtls.sys_award_year));
2638 fnd_file.put_line(fnd_file.log, fnd_message.get);
2639 RETURN;
2640 END IF;
2641
2642 -- Set the System Award Year and Award Year status code
2643 g_sys_award_year := lc_sys_awd_yr_dtls.sys_award_year;
2644 g_awd_yr_status_cd := lc_sys_awd_yr_dtls.award_year_status_code;
2645
2646 fnd_file.put_line(fnd_file.log,RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','AWARD_YR_STATUS'),40)|| ' : '|| igf_ap_gen.get_aw_lookup_meaning('IGF_AWARD_YEAR_STATUS',g_awd_yr_status_cd, g_sys_award_year));
2647 fnd_file.put_line(fnd_file.log,RPAD('-',55,'-'));
2648
2649
2650 -- Check whether all the setups are valid for the context award year in the system.
2651 OPEN c_check_setups(g_cal_type, g_seq_number);
2652 FETCH c_check_setups INTO l_chk_setups;
2653 CLOSE c_check_setups;
2654
2655 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2656 l_debug_str := l_debug_str || ', Validating Other Pell Setups : ' || l_chk_setups;
2657 END IF;
2658
2659 IF NVL(l_chk_setups, 'N') <> 'x' THEN
2660 fnd_message.set_name('IGF','IGF_GR_LI_AWD_YR_INVALID_4');
2661 fnd_file.put_line(fnd_file.log, fnd_message.get);
2662 END IF;
2663
2664 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2665 l_debug_str := l_debug_str || ', Starting Pell Origination import : ';
2666 END IF;
2667
2668
2669 -- If it is COD-XML award year then take awards with status Ready to Send only
2670 IF igf_sl_dl_validation.check_full_participant (g_cal_type, g_seq_number,'PELL') THEN
2671 -- open the cursor with origination status code as "R" meaning "Ready to Send".
2672 OPEN c_pell_orig_int(p_batch_num, lc_sys_awd_yr_dtls.alternate_code,'R');
2673 ELSE
2674 -- open the cursor with origination status code as NULL so that it picks all records.
2675 OPEN c_pell_orig_int(p_batch_num, lc_sys_awd_yr_dtls.alternate_code,NULL);
2676 END IF;
2677
2678 -- Loop each record in the Pell Interface table
2679 FETCH c_pell_orig_int INTO lc_pell_orig_int;
2680
2681 -- If Pell records are not present in the interface table for the given batch number and award year
2682 -- log an error message and exit process
2683 IF c_pell_orig_int%NOTFOUND THEN
2684 CLOSE c_pell_orig_int;
2685
2686 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2687 l_debug_str := l_debug_str || ', Pell int records not found :: ';
2688 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_gr_li_import.main.debug', l_debug_str);
2689 l_debug_str := NULL;
2690 END IF;
2691 fnd_message.set_name('IGF','IGF_GR_LI_NO_RECORDS');
2692 fnd_file.put_line(fnd_file.log, fnd_message.get);
2693 RETURN;
2694
2695 ELSE
2696
2697 -- Cache the temporary variables
2698 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2699 l_debug_str := l_debug_str || ', Pell int records are present :: ';
2700 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_gr_li_import.main.debug', l_debug_str);
2701 l_debug_str := NULL;
2702 END IF;
2703
2704 l_processing := igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','PROCESSING');
2705 l_person_number := igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','PERSON_NUMBER');
2706 l_award_number := igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','AWARD_NUMBER');
2707 l_prev_person_number := NULL;
2708
2709 -- Process all Interface Pell Origination records.
2710 LOOP
2711
2712 -- Loop is necessary to transfer the control from middle of the loop to next record
2713 BEGIN
2714
2715 SAVEPOINT SP_MAIN_PELL;
2716
2717 fnd_file.put_line(fnd_file.log, ' ');
2718 fnd_file.put_line(fnd_file.log, ' ');
2719 g_tot_rec_processed := g_tot_rec_processed + 1;
2720 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2721 l_debug_str := ' Processing Record : ' || g_tot_rec_processed ||', Person Number : ' || lc_pell_orig_int.person_number;
2722 END IF;
2723
2724 -- Check attending and reporting entity IDs in case of COD-XML processing
2725 -- Attending campus code should be NULL
2726 IF igf_sl_dl_validation.check_full_participant (g_cal_type, g_seq_number,'PELL') THEN
2727
2728 IF (NOT chk_atd_rep(lc_pell_orig_int.atd_entity_id_txt, lc_pell_orig_int.rep_entity_id_txt)) THEN
2729 -- Skip Record
2730 fnd_message.set_name('IGF','IGF_AW_LI_SKIPPING_AWD');
2731 fnd_file.put_line(fnd_file.log, fnd_message.get);
2732 g_tot_rec_imp_error := g_tot_rec_imp_error + 1;
2733 RAISE SKIP_RECORD;
2734 END IF;
2735 IF lc_pell_orig_int.attending_campus_cd IS NOT NULL THEN
2736 fnd_message.set_name('IGF','IGF_GR_FULL_NO_ATD_PELL_ID');
2737 fnd_message.set_name('IGF','IGF_AW_LI_SKIPPING_AWD');
2738 fnd_file.put_line(fnd_file.log, fnd_message.get);
2739 fnd_file.put_line(fnd_file.log, fnd_message.get);
2740 g_tot_rec_imp_error := g_tot_rec_imp_error + 1;
2741 RAISE SKIP_RECORD;
2742 END IF;
2743
2744 END IF;
2745
2746 -- Perform Person existance and Base Record existance in the system
2747 -- Do not perform the checks for the same person once again
2748 IF lc_pell_orig_int.person_number <> l_prev_person_number OR l_prev_person_number IS NULL THEN
2749
2750 l_prev_person_number := lc_pell_orig_int.person_number;
2751
2752 -- Log context Information in the log file
2753 fnd_file.put_line(fnd_file.log,RPAD('-',50,'-'));
2754 fnd_file.put_line(fnd_file.log, RPAD(l_processing ||' '|| l_person_number, 30) ||' : '|| lc_pell_orig_int.person_number);
2755 fnd_file.put_line(fnd_file.log,RPAD('-',50,'-'));
2756 fnd_file.put_line(fnd_file.log, RPAD(l_processing ||' '|| l_award_number, 30) ||' : '|| lc_pell_orig_int.award_number_txt);
2757
2758 -- Validate Person Number
2759 g_person_id := NULL;
2760 g_base_id := NULL;
2761 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2762 l_debug_str := l_debug_str || ', Checking Person Existance ';
2763 END IF;
2764
2765 igf_ap_gen.check_person(
2766 lc_pell_orig_int.person_number,
2767 g_cal_type,
2768 g_seq_number,
2769 g_person_id,
2770 g_base_id
2771 );
2772 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2773 l_debug_str := l_debug_str || ', Person ID : ' || g_person_id;
2774 l_debug_str := l_debug_str || ', Base ID : ' || g_base_id;
2775 END IF;
2776
2777 -- If person does not exits, log a message and exit the loop
2778 IF g_person_id IS NULL AND g_base_id IS NULL THEN
2779 fnd_message.set_name('IGF','IGF_AW_LI_PERSON_NOT_FND');
2780 fnd_file.put_line(fnd_file.log,fnd_message.get);
2781 l_prev_person_number := NULL;
2782
2783 fnd_message.set_name('IGF','IGF_AW_LI_SKIPPING_AWD');
2784 fnd_file.put_line(fnd_file.log, fnd_message.get);
2785 g_tot_rec_imp_error := g_tot_rec_imp_error + 1;
2786
2787 UPDATE igf_aw_li_pell_ints
2788 SET import_status_type = 'E',
2789 last_updated_by = fnd_global.user_id,
2790 last_update_date = SYSDATE,
2791 last_update_login = fnd_global.login_id,
2792 request_id = fnd_global.conc_request_id,
2793 program_id = fnd_global.conc_program_id,
2794 program_application_id = fnd_global.prog_appl_id,
2795 program_update_date = SYSDATE
2796 WHERE batch_num = lc_pell_orig_int.batch_num
2797 AND ci_alternate_code = lc_pell_orig_int.ci_alternate_code
2798 AND person_number = lc_pell_orig_int.person_number
2799 AND award_number_txt = lc_pell_orig_int.award_number_txt
2800 AND origination_id_txt = lc_pell_orig_int.origination_id_txt;
2801
2802 RAISE SKIP_RECORD;
2803
2804
2805 -- If FA Base record does not exits then log a message and exit the loop
2806 ELSIF g_base_id IS NULL THEN
2807
2808 fnd_message.set_name('IGF','IGF_AP_FABASE_NOT_FOUND');
2809 fnd_file.put_line(fnd_file.log,fnd_message.get);
2810 l_prev_person_number := NULL;
2811
2812 fnd_message.set_name('IGF','IGF_AW_LI_SKIPPING_AWD');
2813 fnd_file.put_line(fnd_file.log, fnd_message.get);
2814 g_tot_rec_imp_error := g_tot_rec_imp_error + 1;
2815
2816 UPDATE igf_aw_li_pell_ints
2817 SET import_status_type = 'E',
2818 last_updated_by = fnd_global.user_id,
2819 last_update_date = SYSDATE,
2820 last_update_login = fnd_global.login_id,
2821 request_id = fnd_global.conc_request_id,
2822 program_id = fnd_global.conc_program_id,
2823 program_application_id = fnd_global.prog_appl_id,
2824 program_update_date = SYSDATE
2825 WHERE batch_num = lc_pell_orig_int.batch_num
2826 AND ci_alternate_code = lc_pell_orig_int.ci_alternate_code
2827 AND person_number = lc_pell_orig_int.person_number
2828 AND award_number_txt = lc_pell_orig_int.award_number_txt
2829 AND origination_id_txt = lc_pell_orig_int.origination_id_txt;
2830
2831 RAISE SKIP_RECORD;
2832 END IF;
2833
2834 ELSE
2835 fnd_file.put_line(fnd_file.log, RPAD(l_processing ||' '|| l_award_number, 30) ||' : '|| lc_pell_orig_int.award_number_txt);
2836
2837 END IF;
2838
2839
2840 -- If the Pell record is being imported in Update mode, then delete the existing PELL record then import interface record
2841 IF lc_pell_orig_int.import_record_type = 'U' THEN
2842 l_delete_status := 'E';
2843 l_delete_status := delete_existing_pell_rec(
2844 lc_pell_orig_int.origination_id_txt,
2845 g_cal_type,
2846 g_seq_number
2847 );
2848
2849 -- IF legacy Pell records are not found OR Errors in Pell records deletion, log an error message and rollback to prev. save point
2850 IF l_delete_status = 'E' THEN
2851 ROLLBACK TO SP_MAIN_PELL;
2852 fnd_message.set_name('IGF','IGF_GR_LI_UPDATE_FAIL');
2853 fnd_file.put_line(fnd_file.log, fnd_message.get);
2854
2855 UPDATE igf_aw_li_pell_ints
2856 SET import_status_type = 'E',
2857 last_updated_by = fnd_global.user_id,
2858 last_update_date = SYSDATE,
2859 last_update_login = fnd_global.login_id,
2860 request_id = fnd_global.conc_request_id,
2861 program_id = fnd_global.conc_program_id,
2862 program_application_id = fnd_global.prog_appl_id,
2863 program_update_date = SYSDATE
2864 WHERE batch_num = lc_pell_orig_int.batch_num
2865 AND ci_alternate_code = lc_pell_orig_int.ci_alternate_code
2866 AND person_number = lc_pell_orig_int.person_number
2867 AND award_number_txt = lc_pell_orig_int.award_number_txt
2868 AND origination_id_txt = lc_pell_orig_int.origination_id_txt;
2869
2870 COMMIT;
2871
2872 fnd_message.set_name('IGF','IGF_AW_LI_SKIPPING_AWD');
2873 fnd_file.put_line(fnd_file.log, fnd_message.get);
2874 g_tot_rec_imp_error := g_tot_rec_imp_error + 1;
2875 RAISE SKIP_RECORD;
2876 END IF;
2877
2878 -- If NULL or in Insert mode, do the import
2879 ELSIF NVL(lc_pell_orig_int.import_record_type,'I') = 'I' THEN
2880 NULL;
2881 -- for all other values error out
2882 ELSE
2883
2884 ROLLBACK TO SP_MAIN_PELL;
2885 fnd_message.set_name('IGF','IGF_AW_LI_INVLD_IMP_REC_TY');
2886 fnd_file.put_line(fnd_file.log, fnd_message.get);
2887
2888 fnd_message.set_name('IGF','IGF_AW_LI_SKIPPING_AWD');
2889 fnd_file.put_line(fnd_file.log, fnd_message.get);
2890 g_tot_rec_imp_error := g_tot_rec_imp_error + 1;
2891
2892 UPDATE igf_aw_li_pell_ints
2893 SET import_status_type = 'E',
2894 last_updated_by = fnd_global.user_id,
2895 last_update_date = SYSDATE,
2896 last_update_login = fnd_global.login_id,
2897 request_id = fnd_global.conc_request_id,
2898 program_id = fnd_global.conc_program_id,
2899 program_application_id = fnd_global.prog_appl_id,
2900 program_update_date = SYSDATE
2901 WHERE batch_num = lc_pell_orig_int.batch_num
2902 AND ci_alternate_code = lc_pell_orig_int.ci_alternate_code
2903 AND person_number = lc_pell_orig_int.person_number
2904 AND award_number_txt = lc_pell_orig_int.award_number_txt
2905 AND origination_id_txt = lc_pell_orig_int.origination_id_txt;
2906
2907 RAISE SKIP_RECORD;
2908 END IF;
2909
2910
2911 -- Update Pell amounts at the FA Base rec level
2912 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2913 l_debug_str := l_debug_str || ' :: Updating FA Base Record ';
2914 END IF;
2915
2916 l_fabase_ret_status := NULL;
2917 l_fabase_ret_status := update_fa_base_data( g_base_id, lc_pell_orig_int.pell_coa_amt, lc_pell_orig_int.pell_alt_exp_amt);
2918 IF l_fabase_ret_status = 'E' THEN
2919 fnd_message.set_name('IGF','IGF_GR_LI_FAIL_UPD_COA');
2920 fnd_message.set_token('PELL_COA',lc_pell_orig_int.pell_coa_amt);
2921 fnd_message.set_token('PELL_ALT_EXP',lc_pell_orig_int.pell_alt_exp_amt);
2922 fnd_file.put_line(fnd_file.log, fnd_message.get);
2923
2924 IF l_pell_import_status <> 'E' THEN
2925 l_pell_import_status := 'W';
2926 END IF;
2927 END IF;
2928
2929 -- Import Pell Origination Record
2930 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2931 l_debug_str := l_debug_str || ' : Import Pell Origination : ';
2932 END IF;
2933
2934 l_pell_import_status := NULL;
2935 l_pell_import_status := import_pell_orig(lc_pell_orig_int);
2936
2937 -- Update the records imported count
2938 IF l_pell_import_status = 'I' THEN
2939 fnd_message.set_name('IGF','IGF_GR_LI_IMP_SUCCES');
2940 fnd_message.set_token('ORIG_ID',lc_pell_orig_int.origination_id_txt);
2941 fnd_file.put_line(fnd_file.log, fnd_message.get);
2942 g_tot_rec_imp_successful := g_tot_rec_imp_successful + 1;
2943
2944 -- Commit the transaction after successful import of Pell record.
2945 COMMIT;
2946
2947 ELSIF l_pell_import_status = 'E' THEN
2948 fnd_message.set_name('IGF','IGF_AW_LI_SKIPPING_AWD');
2949 fnd_file.put_line(fnd_file.log, fnd_message.get);
2950 g_tot_rec_imp_error := g_tot_rec_imp_error + 1;
2951
2952 -- Rollback the intermediate transactions
2953 ROLLBACK TO SP_MAIN_PELL;
2954
2955 ELSE
2956 g_tot_rec_imp_warning := g_tot_rec_imp_warning + 1;
2957
2958 END IF;
2959
2960 -- Update the return status of the record.
2961 -- Delete the interface recrodss if the falg is set. (Do not delete int records if imported with warnings)
2962 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2963 l_debug_str := l_debug_str || ' : Import status : ' || l_pell_import_status;
2964 END IF;
2965
2966 IF l_pell_import_status = 'I' AND g_delete_flag = 'Y' THEN
2967
2968 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2969 l_debug_str := l_debug_str || ' : Deleting Pell Int rec : ';
2970 END IF;
2971
2972 DELETE igf_aw_li_pell_ints
2973 WHERE batch_num = lc_pell_orig_int.batch_num
2974 AND ci_alternate_code = lc_pell_orig_int.ci_alternate_code
2975 AND person_number = lc_pell_orig_int.person_number
2976 AND award_number_txt = lc_pell_orig_int.award_number_txt
2977 AND origination_id_txt = lc_pell_orig_int.origination_id_txt;
2978
2979 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2980 l_debug_str := l_debug_str || ', Deleting Interface Pell Disb recs';
2981 END IF;
2982
2983 DELETE igf_aw_li_pdb_ints
2984 WHERE ci_alternate_code = lc_pell_orig_int.ci_alternate_code
2985 AND person_number = lc_pell_orig_int.person_number
2986 AND award_number_txt = lc_pell_orig_int.award_number_txt
2987 AND origination_id_txt = lc_pell_orig_int.origination_id_txt;
2988
2989 ELSE
2990 -- Update the interface table as 'I' if imported successfully
2991 -- Update the interface table as 'W' if imported with warnings
2992 -- Update the interface table as 'E' if not imported and errors are present
2993 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
2994 l_debug_str := l_debug_str || ' : Updating Pell Int rec : ';
2995 END IF;
2996
2997 UPDATE igf_aw_li_pell_ints
2998 SET import_status_type = l_pell_import_status,
2999 last_updated_by = fnd_global.user_id,
3000 last_update_date = SYSDATE,
3001 last_update_login = fnd_global.login_id,
3002 request_id = fnd_global.conc_request_id,
3003 program_id = fnd_global.conc_program_id,
3004 program_application_id = fnd_global.prog_appl_id,
3005 program_update_date = SYSDATE
3006 WHERE batch_num = lc_pell_orig_int.batch_num
3007 AND ci_alternate_code = lc_pell_orig_int.ci_alternate_code
3008 AND person_number = lc_pell_orig_int.person_number
3009 AND award_number_txt = lc_pell_orig_int.award_number_txt
3010 AND origination_id_txt = lc_pell_orig_int.origination_id_txt;
3011
3012 END IF;
3013
3014 -- This comment is necessary to commit the import_status_type of the corresponding record
3015 COMMIT;
3016
3017 EXCEPTION
3018 WHEN SKIP_RECORD THEN
3019 NULL;
3020
3021 WHEN OTHERS THEN
3022 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
3023 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'igf.plsql.igf_gr_li_import.main.internal Begin', l_debug_str || SQLERRM );
3024 END IF;
3025 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
3026 fnd_message.set_token('NAME','IGF_GR_LI_IMPORT.MAIN_SUB_BEGIN');
3027 igs_ge_msg_stack.add;
3028 app_exception.raise_exception;
3029 END; -- End of local begin
3030
3031
3032 -- Fetch the next record, if no more records then exit the code
3033 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
3034 l_debug_str := l_debug_str || ' : Fetch next record : ';
3035 l_debug_str := l_debug_str || ' : Fetch next record : ';
3036 END IF;
3037
3038 lc_pell_orig_int := NULL;
3039 FETCH c_pell_orig_int INTO lc_pell_orig_int;
3040 EXIT WHEN c_pell_orig_int%NOTFOUND;
3041
3042 IF (FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level) THEN
3043 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_gr_li_import.main.debug', l_debug_str);
3044 l_debug_str := NULL;
3045 END IF;
3046 l_debug_str := NULL;
3047
3048 END LOOP; -- End of all Pell Interface records
3049
3050 END IF; -- End of Pell Cursor
3051
3052 -- If cursor is still open then close it
3053 IF c_pell_orig_int%ISOPEN THEN
3054 CLOSE c_pell_orig_int;
3055 END IF;
3056
3057 fnd_file.put_line(fnd_file.log, ' ');
3058
3059 -- Print the statistics in the OUT file
3060 fnd_file.put_line(fnd_file.output,' ' );
3061 fnd_file.put_line(fnd_file.output, RPAD('-',50,'-'));
3062 fnd_file.put_line(fnd_file.output,' ' );
3063 fnd_file.put_line(fnd_file.output, RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','RECORDS_PROCESSED'), 40) || ' : ' || g_tot_rec_processed);
3064 fnd_file.put_line(fnd_file.output, RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','RECORDS_SUCCESSFUL'), 40) || ' : ' || g_tot_rec_imp_successful);
3065 fnd_file.put_line(fnd_file.output, RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','RECORDS_WARN'), 40) || ' : ' || g_tot_rec_imp_warning);
3066 fnd_file.put_line(fnd_file.output, RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','RECORDS_REJECTED'), 40) || ' : ' || g_tot_rec_imp_error);
3067 fnd_file.put_line(fnd_file.output,' ' );
3068 fnd_file.put_line(fnd_file.output, RPAD('-',50,'-'));
3069 fnd_file.put_line(fnd_file.output,' ' );
3070
3071 fnd_file.put_line(fnd_file.log,' ' );
3072 fnd_file.put_line(fnd_file.log, RPAD('-',50,'-'));
3073 fnd_file.put_line(fnd_file.log,' ' );
3074 fnd_file.put_line(fnd_file.log, RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','RECORDS_PROCESSED'), 40) || ' : ' || g_tot_rec_processed);
3075 fnd_file.put_line(fnd_file.log, RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','RECORDS_SUCCESSFUL'), 40) || ' : ' || g_tot_rec_imp_successful);
3076 fnd_file.put_line(fnd_file.log, RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','RECORDS_WARN'), 40) || ' : ' || g_tot_rec_imp_warning);
3077 fnd_file.put_line(fnd_file.log, RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','RECORDS_REJECTED'), 40) || ' : ' || g_tot_rec_imp_error);
3078 fnd_file.put_line(fnd_file.log,' ' );
3079 fnd_file.put_line(fnd_file.log, RPAD('-',50,'-'));
3080 fnd_file.put_line(fnd_file.log,' ' );
3081
3082
3083 EXCEPTION
3084 WHEN others THEN
3085 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
3086 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'igf.plsql.igf_gr_li_import.main.exception', l_debug_str || SQLERRM );
3087 END IF;
3088 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
3089 fnd_message.set_token('NAME','IGF_GR_LI_IMPORT.MAIN');
3090 errbuf := fnd_message.get;
3091 igs_ge_msg_stack.conc_exception_hndl;
3092
3093 END main;
3094
3095
3096
3097 END igf_gr_li_import;