1 PACKAGE BODY igf_aw_li_import AS
2 /* $Header: IGFAW15B.pls 120.14 2006/09/08 13:55:06 akomurav ship $ */
3
4 /*=======================================================================+
5 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA|
6 | All rights reserved. |
7 +=======================================================================+
8 | |
9 | DESCRIPTION |
10 | PL/SQL body for package: IGF_AW_LI_IMPORT |
11 | |
12 | NOTES |
13 | |
14 |The Legacy - Award and Disbursement Data Import Process imports data |
15 |from the Legacy Award Data Interface Table, Legacy Disbursement Data |
16 |Interface Table, and Legacy Disbursement Activity Data Interface Table |
17 |into appropriate OSS Financial Aid tables. |
18 |This concurrent process performs specified business rules and |
19 |validations before data can be imported into the OSS Financial Aid |
20 |tables. Users will be notified via the concurrent request's log file |
21 |if the process has encountered errors. |
22 | |
23 | HISTORY |
24 | Who When What |
25 | |
26 | azmohamm 03-AUG-2006 FA 163, Added GPLUSFL fund code |
27 |
28 | ridas 29-Jul-2005 Bug #3536039. |
29 | Exception igf_se_gen_001.IGFSEGEN001 added in |
30 | procedure CREATE_AWARD_RECORD |
31 | |
32 | ridas 22-Jul-2005 Bug #4093072. If FEDERAL_FUND_CODE equal to |
33 | 'ALT'/'FLS'/'FLP'/'FLU', then call the function|
34 | igf_sl_award.get_cl_hold_rel_ind() which will |
35 | return the hold_release_indicator value. |
36 | If not assign FALSE as default value |
37 | |
38 | ridas 10-Jan-2005 Bug #3701698 if the Award Status='CANCELLED', |
39 | Accected amount should be zero. |
40 | |
41 | brajendr 04-Jan-2004 Bug 3701698 Loading Legacy awds with CANCEL st |
42 | Added the validations for validating the |
43 | -- Total offered/accepted amounts in disb |
44 | -- added check for disb_accepted_amt in dacts |
45 | |
46 | cdcruz 02-Dec-2004 Bug 3701698 Customer requirement was to be able|
47 | to upload Cancelled Awards via Legacy Import |
48 | |
49 | ridas 08-Nov-2004 Bug 3021287 If the profile_value = 'AWARDED' |
50 | then updating COA at the student level |
51 | |
52 | brajendr 12-Oct-2004 Bug 3732665 ISIR Enhacements |
53 | modified the payment isir reference |
54 | |
55 | veramach July 2004 FA 151 HR Integration (bug# 3709292) |
56 | Moved validations at the disbursement level |
57 | for FWS awards to award level. |
58 | also,creation of authorization is made at the |
59 | award level rather than at each disbursement |
60 | level |
61 | veramach 26-Feb-2004 bug 3466726 - Changed cursor c_get_att_type to |
62 | use lookup_code rather than description for |
63 | validating base_attendance_type_code |
64 | If this validation fails, message |
65 | IGF_AP_INV_FLD_VAL is displayed rather than |
66 | IGF_AW_LI_INVALID_ATT_TYPE |
67 | veramach 08-Dec-2003 FA 131 Build - made li_awd_rec parameter of |
68 | validate_awdyear_int_rec to IN OUT NOCOPY |
69 | sjadhav 4-Dec-2003 Limit logic for term comparision to Sponsor |
70 | Funds |
71 | veramach 04-Dec-2003 FA 131 COD Updates |
72 | Changed column names in legacy award/ |
73 | disbursement table to be in sync with the CS |
74 | nsidana 11/27/2003 FA131 COD updated for 2004-2005 build. |
75 | New cols added to legacy award and legacy award |
76 | disbursements table. Impact done here. |
77 | sjadhav 19-Nov-2003 Bug 3160568 FA 125 Build. Added run routine |
78 | Added validations for EXT Award Import |
79 | Added validations for attendance_type_code |
80 | veramach 1-NOV-2003 FA 125 Multiple Distr Methods |
81 | Changed calll to igf_aw_awd_disb_pkg.update_row|
82 | to reflect the addition of attendance_type_code|
83 | brajendr 08-Oct-2003 Bug # 3116511 - Update the Auth Date |
84 | |
85 | sjalasut June 16,2003 Created as Part of Fa118.2 Legacy Import Build |
86 | sjalasut August 4, 2003 Import Sponsorships and Federal Work Study |
87 | funds for FACR117, part of sep 03 patch |
88 | sjalasut Aug 12, 2003 Bug 3093913. Changed the message for validatio|
89 | n on transaction type. |
90 *=======================================================================*/
91
92 /***************************************************************
93 Created By : nsidana
94 Date Created By : 11/28/2003
95 Purpose : Legacy Awards Import package body.
96 Known Limitations,Enhancements or Remarks
97 Change History :
98 Who When What
99 nsidana 11/28/2003 FA131 COD updates build. Added validations on two new feilds
100 added to the legacy awards table and 1 feild in the legacy award
101 disbursements table.
102 ***************************************************************/
103
104
105 -- cursor to fetch records from the award legacy interface tables
106
107 CURSOR cur_legacy_award_int(p_alternate_code igs_ca_inst.alternate_code%TYPE,
108 p_batch_num igf_aw_li_awd_ints.batch_num%TYPE) IS
109 SELECT
110 awdint.batch_num,
111 trim(awdint.ci_alternate_code) ci_alternate_code,
112 trim(awdint.fund_code) fund_code,
113 trim(awdint.person_number) person_number,
114 trim(awdint.award_number_txt) award_number_txt,
115 awdint.import_status_type,
116 awdint.offered_amt,
117 awdint.accepted_amt,
118 trim(awdint.award_status_code) award_status_code,
119 awdint.award_date,
120 awdint.alt_pell_schedule_type,
121 awdint.import_record_type,
122 awdint.created_by,
123 awdint.creation_date,
124 awdint.last_updated_by,
125 awdint.last_update_date,
126 awdint.last_update_login,
127 awdint.request_id,
128 awdint.program_application_id,
129 awdint.program_id,
130 awdint.program_update_date,
131 awdint.lock_award_flag, --new col for FA131 COD updates build
132 awdint.app_trans_num_txt, --new col for FA131 COD updates build
133 awdint.authorization_date,
134 awdint.publish_in_ss_flag
135 FROM igf_aw_li_awd_ints awdint
136 WHERE awdint.batch_num = p_batch_num
137 AND awdint.ci_alternate_code = p_alternate_code
138 AND awdint.import_status_type IN ('U','R')
139 ORDER BY awdint.person_number, awdint.fund_code, awdint.award_number_txt;
140 l_out_person_id hz_parties.party_id%TYPE;
141 l_out_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
142
143 g_ci_cal_type igf_ap_batch_aw_map.ci_cal_type%TYPE;
144 g_ci_sequence_number igf_ap_batch_aw_map_all.ci_sequence_number%TYPE;
145
146 g_award_year_status_code igf_ap_batch_aw_map_all.award_year_status_code%TYPE;
147 g_sys_award_year igf_ap_batch_aw_map_all.sys_award_year%TYPE;
148 g_fund_code igf_aw_fund_mast_all.fund_code%TYPE;
149 g_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE;
150 g_fund_id igf_aw_fund_mast_all.fund_id%TYPE;
151 g_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
152 g_award_id igf_aw_award_all.award_id%TYPE;
153 g_disb_net_amount igf_aw_li_dact_ints.disb_net_amt%TYPE;
154 g_person_id hz_parties.party_id%TYPE;
155 g_processing_string VARCHAR2(1000);
156 g_debug_string fnd_log_messages.message_text%TYPE;
157 g_debug_runtime_level NUMBER;
158 g_entry_point VARCHAR2(30);
159
160 g_print_msg VARCHAR2(200);
161 -- bvisvana - Bug # 4635941 - Global variables
162 CURSOR c_get_awd_details(cp_award_id igf_aw_award_all.award_id%TYPE) IS
163 SELECT * FROM igf_aw_award_all
164 WHERE award_id = cp_award_id;
165
166 g_old_award_rec c_get_awd_details%ROWTYPE; -- Collects information of the old award (that is getting deleted)
167 g_new_award_rec c_get_awd_details%ROWTYPE; -- Collects information of the new award (that is getting created)
168 g_hist_cnt NUMBER;
169 g_update_mode BOOLEAN := FALSE; -- To Track whether running in update mode or not
170 TYPE g_old_award_hist_tab_type IS TABLE OF igf_aw_award_level_hist%ROWTYPE INDEX BY BINARY_INTEGER;
171 g_old_award_hist_col g_old_award_hist_tab_type; -- Holds the history of the award that is getting deleted when running in update_mode
172
173
174 PROCEDURE lock_std_coa (p_base_id igf_ap_fa_base_rec_all.base_id%TYPE
175 )
176 IS
177 -----------------------------------------------------------------------------
178 -- Created By : ridas
179 -- Created On : 05-Nov-2004
180 -- Purpose : to lock COA at the student level
181 -- Known limitations, enhancements or remarks :
182 -- Change History :
183 -- Who When What
184 --
185 -----------------------------------------------------------------------------
186
187 --Cursor to fetch person details
188 CURSOR c_get_fab (p_base_id igf_ap_fa_base_rec_all.base_id%TYPE
189 )
190 IS
191 SELECT fab.rowid row_id,
192 fab.*
193 FROM igf_ap_fa_base_rec_all fab
194 WHERE fab.base_id = p_base_id
195 AND NVL(fab.lock_coa_flag,'N') <> 'Y';
196
197 l_get_fab c_get_fab%ROWTYPE;
198
199
200 --Cursor to fetch item details
201 CURSOR c_items(
202 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
203 ) IS
204 SELECT items.rowid row_id,
205 items.*
206 FROM igf_aw_coa_items items
207 WHERE base_id = cp_base_id
208 AND NVL(lock_flag,'N') <> 'Y';
209
210
211 --Cursor to fetch term details
212 CURSOR c_terms(
213 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
214 cp_item_code igf_aw_item.item_code%TYPE
215 ) IS
216 SELECT terms.rowid row_id,
217 terms.*
218 FROM igf_aw_coa_itm_terms terms
219 WHERE base_id = cp_base_id
220 AND item_code = cp_item_code
221 AND NVL(lock_flag,'N') <> 'Y';
222
223
224 BEGIN
225
226 OPEN c_get_fab(p_base_id);
227 FETCH c_get_fab INTO l_get_fab;
228 CLOSE c_get_fab;
229
230 IF l_get_fab.base_id IS NOT NULL THEN
231
232 FOR l_items IN c_items(l_get_fab.base_id)
233 LOOP
234 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
235 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_li_import.lock_std_coa.debug','locking at the item level');
236 END IF;
237
238 igf_aw_coa_items_pkg.update_row(
239 x_rowid => l_items.row_id,
240 x_base_id => l_items.base_id,
241 x_item_code => l_items.item_code,
242 x_amount => l_items.amount,
243 x_pell_coa_amount => l_items.pell_coa_amount,
244 x_alt_pell_amount => l_items.alt_pell_amount,
245 x_fixed_cost => l_items.fixed_cost,
246 x_legacy_record_flag => l_items.legacy_record_flag,
247 x_mode => 'R',
248 x_lock_flag => 'Y'
249 );
250
251
252 FOR l_terms IN c_terms(l_get_fab.base_id,l_items.item_code)
253 LOOP
254 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
255 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_li_import.lock_std_coa.debug','locking at the term level');
256 END IF;
257
258 igf_aw_coa_itm_terms_pkg.update_row(
259 x_rowid => l_terms.row_id,
260 x_base_id => l_terms.base_id,
261 x_item_code => l_terms.item_code,
262 x_amount => l_terms.amount,
263 x_ld_cal_type => l_terms.ld_cal_type,
264 x_ld_sequence_number => l_terms.ld_sequence_number,
265 x_mode => 'R',
266 x_lock_flag => 'Y'
267 );
268
269 END LOOP;
270 END LOOP;
271
272
273 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
274 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_li_import.lock_std_coa.debug','l_get_fab.base_id IS NOT NULL');
275 END IF;
276
277 igf_ap_fa_base_rec_pkg.update_row
278 (x_Mode => 'R',
279 x_rowid => l_get_fab.row_id,
280 x_base_id => l_get_fab.base_id,
281 x_ci_cal_type => l_get_fab.ci_cal_type,
282 x_person_id => l_get_fab.person_id,
283 x_ci_sequence_number => l_get_fab.ci_sequence_number,
284 x_org_id => l_get_fab.org_id,
285 x_coa_pending => l_get_fab.coa_pending,
286 x_verification_process_run => l_get_fab.verification_process_run,
287 x_inst_verif_status_date => l_get_fab.inst_verif_status_date,
288 x_manual_verif_flag => l_get_fab.manual_verif_flag,
289 x_fed_verif_status => l_get_fab.fed_verif_status,
290 x_fed_verif_status_date => l_get_fab.fed_verif_status_date,
291 x_inst_verif_status => l_get_fab.inst_verif_status,
292 x_nslds_eligible => l_get_fab.nslds_eligible,
293 x_ede_correction_batch_id => l_get_fab.ede_correction_batch_id,
294 x_fa_process_status_date => l_get_fab.fa_process_status_date,
295 x_ISIR_corr_status => l_get_fab.ISIR_corr_status,
296 x_ISIR_corr_status_date => l_get_fab.ISIR_corr_status_date,
297 x_ISIR_status => l_get_fab.ISIR_status,
298 x_ISIR_status_date => l_get_fab.ISIR_status_date,
299 x_coa_code_f => l_get_fab.coa_code_f,
300 x_coa_code_i => l_get_fab.coa_code_i,
301 x_coa_f => l_get_fab.coa_f,
302 x_coa_i => l_get_fab.coa_i,
303 x_disbursement_hold => l_get_fab.disbursement_hold,
304 x_fa_process_status => l_get_fab.fa_process_status,
305 x_notification_status => l_get_fab.notification_status,
306 x_notification_status_date => l_get_fab.notification_status_date,
307 x_packaging_status => l_get_fab.packaging_status,
308 x_packaging_status_date => l_get_fab.packaging_status_date,
309 x_total_package_accepted => l_get_fab.total_package_accepted,
310 x_total_package_offered => l_get_fab.total_package_offered,
311 x_admstruct_id => l_get_fab.admstruct_id,
312 x_admsegment_1 => l_get_fab.admsegment_1,
313 x_admsegment_2 => l_get_fab.admsegment_2,
314 x_admsegment_3 => l_get_fab.admsegment_3,
315 x_admsegment_4 => l_get_fab.admsegment_4,
316 x_admsegment_5 => l_get_fab.admsegment_5,
317 x_admsegment_6 => l_get_fab.admsegment_6,
318 x_admsegment_7 => l_get_fab.admsegment_7,
319 x_admsegment_8 => l_get_fab.admsegment_8,
320 x_admsegment_9 => l_get_fab.admsegment_9,
321 x_admsegment_10 => l_get_fab.admsegment_10,
322 x_admsegment_11 => l_get_fab.admsegment_11,
323 x_admsegment_12 => l_get_fab.admsegment_12,
324 x_admsegment_13 => l_get_fab.admsegment_13,
325 x_admsegment_14 => l_get_fab.admsegment_14,
326 x_admsegment_15 => l_get_fab.admsegment_15,
327 x_admsegment_16 => l_get_fab.admsegment_16,
328 x_admsegment_17 => l_get_fab.admsegment_17,
329 x_admsegment_18 => l_get_fab.admsegment_18,
330 x_admsegment_19 => l_get_fab.admsegment_19,
331 x_admsegment_20 => l_get_fab.admsegment_20,
332 x_packstruct_id => l_get_fab.packstruct_id,
333 x_packsegment_1 => l_get_fab.packsegment_1,
334 x_packsegment_2 => l_get_fab.packsegment_2,
335 x_packsegment_3 => l_get_fab.packsegment_3,
336 x_packsegment_4 => l_get_fab.packsegment_4,
337 x_packsegment_5 => l_get_fab.packsegment_5,
338 x_packsegment_6 => l_get_fab.packsegment_6,
339 x_packsegment_7 => l_get_fab.packsegment_7,
340 x_packsegment_8 => l_get_fab.packsegment_8,
341 x_packsegment_9 => l_get_fab.packsegment_9,
342 x_packsegment_10 => l_get_fab.packsegment_10,
343 x_packsegment_11 => l_get_fab.packsegment_11,
344 x_packsegment_12 => l_get_fab.packsegment_12,
345 x_packsegment_13 => l_get_fab.packsegment_13,
346 x_packsegment_14 => l_get_fab.packsegment_14,
347 x_packsegment_15 => l_get_fab.packsegment_15,
348 x_packsegment_16 => l_get_fab.packsegment_16,
349 x_packsegment_17 => l_get_fab.packsegment_17,
350 x_packsegment_18 => l_get_fab.packsegment_18,
351 x_packsegment_19 => l_get_fab.packsegment_19,
352 x_packsegment_20 => l_get_fab.packsegment_20,
353 x_miscstruct_id => l_get_fab.miscstruct_id,
354 x_miscsegment_1 => l_get_fab.miscsegment_1,
355 x_miscsegment_2 => l_get_fab.miscsegment_2,
356 x_miscsegment_3 => l_get_fab.miscsegment_3,
357 x_miscsegment_4 => l_get_fab.miscsegment_4,
358 x_miscsegment_5 => l_get_fab.miscsegment_5,
359 x_miscsegment_6 => l_get_fab.miscsegment_6,
360 x_miscsegment_7 => l_get_fab.miscsegment_7,
361 x_miscsegment_8 => l_get_fab.miscsegment_8,
362 x_miscsegment_9 => l_get_fab.miscsegment_9,
363 x_miscsegment_10 => l_get_fab.miscsegment_10,
364 x_miscsegment_11 => l_get_fab.miscsegment_11,
365 x_miscsegment_12 => l_get_fab.miscsegment_12,
366 x_miscsegment_13 => l_get_fab.miscsegment_13,
367 x_miscsegment_14 => l_get_fab.miscsegment_14,
368 x_miscsegment_15 => l_get_fab.miscsegment_15,
369 x_miscsegment_16 => l_get_fab.miscsegment_16,
370 x_miscsegment_17 => l_get_fab.miscsegment_17,
371 x_miscsegment_18 => l_get_fab.miscsegment_18,
372 x_miscsegment_19 => l_get_fab.miscsegment_19,
373 x_miscsegment_20 => l_get_fab.miscsegment_20,
374 x_prof_judgement_flg => l_get_fab.prof_judgement_flg,
375 x_nslds_data_override_flg => l_get_fab.nslds_data_override_flg,
376 x_target_group => l_get_fab.target_group,
377 x_coa_fixed => l_get_fab.coa_fixed,
378 x_profile_status => l_get_fab.profile_status,
379 x_profile_status_date => l_get_fab.profile_status_date,
380 x_profile_fc => l_get_fab.profile_fc,
381 x_coa_pell => l_get_fab.coa_pell,
382 x_manual_disb_hold => l_get_fab.manual_disb_hold,
383 x_pell_alt_expense => l_get_fab.pell_alt_expense,
384 x_assoc_org_num => l_get_fab.assoc_org_num,
385 x_award_fmly_contribution_type => l_get_fab.award_fmly_contribution_type,
386 x_packaging_hold => l_get_fab.packaging_hold,
387 x_isir_locked_by => l_get_fab.isir_locked_by ,
388 x_adnl_unsub_loan_elig_flag => l_get_fab.adnl_unsub_loan_elig_flag,
389 x_lock_awd_flag => l_get_fab.lock_awd_flag,
390 x_lock_coa_flag => 'Y'
391 );
392 fnd_message.set_name('IGF','IGF_AW_STUD_COA_LOCK');
393 fnd_message.set_token('PERSON_NUM',igf_gr_gen.get_per_num (p_base_id));
394 g_print_msg := fnd_message.get;
395 END IF;
396
397 EXCEPTION
398 WHEN OTHERS THEN
399 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
400 fnd_message.set_token('NAME','igf_aw_li_import.lock_std_coa :' || SQLERRM);
401 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
402 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_aw_li_import.lock_std_coa.exception','sql error:'||SQLERRM);
403 END IF;
404 igs_ge_msg_stack.conc_exception_hndl;
405 app_exception.raise_exception;
406
407
408 END lock_std_coa;
409
410
411
412 PROCEDURE validate_awdyear_int_rec(li_awd_rec IN OUT NOCOPY igf_aw_li_awd_ints%ROWTYPE, l_return_value OUT NOCOPY VARCHAR2) IS
413
414 /***************************************************************
415 Created By : nsidana
416 Date Created By : 11/28/2003
417 Purpose : Validates the interface record.
418 Known Limitations,Enhancements or Remarks
419 Change History :
420 Who When What
421 veramach 08-Dec-2003 FA 131 Build - made li_awd_rec parameter of
422 validate_awdyear_int_rec to IN OUT NOCOPY
423 nsidana 11/28/2003 FA131 COD updates build. Added validations on two new feilds
424 added to the legacy awards table.
425 ***************************************************************/
426
427 -- validates the award year interface record
428 CURSOR cur_check_sys_awd (p_base_id igf_ap_fa_base_rec_all.base_id%TYPE) IS
429 SELECT award_id, legacy_record_flag
430 FROM igf_aw_award_all
431 WHERE base_id = p_base_id
432 AND award_number_txt = li_awd_rec.award_number_txt;
433 l_award_id igf_aw_award_all.award_id%TYPE;
434 l_legacy_record_flag igf_aw_award_all.legacy_record_flag%TYPE;
435
436 CURSOR cur_get_fund_code(p_fund_code igf_aw_li_awd_ints.fund_code%TYPE) IS
437 SELECT fmast.fund_code, fcat.fed_fund_code, fmast.fund_id
438 FROM igf_aw_fund_mast_all fmast,
439 igf_aw_fund_cat fcat
440 WHERE fmast.fund_code = fcat.fund_code
441 AND fmast.fund_code = p_fund_code
442 AND fmast.ci_cal_type = g_ci_cal_type
443 AND fmast.ci_sequence_number = g_ci_sequence_number
444 AND discontinue_fund = 'N';
445
446 CURSOR c_1_more_pell_fws IS
447 SELECT 'X' present_in_award
448 FROM igf_aw_award_all
449 WHERE base_id = g_base_id
450 AND fund_id = g_fund_id;
451 c_1_more_pell_fws_rec c_1_more_pell_fws%ROWTYPE;
452
453 CURSOR c_get_fa_hold_spnsr IS
454 SELECT 'X' exist_hold
455 FROM igf_aw_li_hold_ints
456 WHERE person_number = li_awd_rec.person_number
457 AND award_number_txt = li_awd_rec.award_number_txt
458 AND ci_alternate_code = li_awd_rec.ci_alternate_code;
459 c_get_fa_hold_spnsr_rec c_get_fa_hold_spnsr%ROWTYPE;
460
461 CURSOR c_get_spnsr_amt IS
462 SELECT fmast.max_yearly_amt
463 FROM igf_aw_fund_mast_all fmast, igf_aw_fund_cat_all fcat
464 WHERE fmast.fund_id = g_fund_id
465 AND fcat.fed_fund_code = g_fed_fund_code
466 AND fmast.ci_cal_type = g_ci_cal_type
467 AND fmast.ci_sequence_number = g_ci_sequence_number
468 AND fmast.fund_code = fcat.fund_code
469 AND fmast.discontinue_fund = 'N';
470 c_get_spnsr_amt_rec c_get_spnsr_amt%ROWTYPE;
471
472 l_return_status_awd VARCHAR2(1);
473 l_status_open_awd_yr VARCHAR2(1);
474 l_return_status_db VARCHAR2(1);
475
476 CURSOR c_get_person_ssn IS
477 SELECT api.api_person_id, api.person_id_type, api.start_dt, api.end_dt
478 FROM igs_pe_alt_pers_id api,
479 igf_ap_fa_base_rec_all fabase,
480 igs_pe_person_id_typ pid
481 WHERE fabase.person_id = api.pe_person_id
482 AND fabase.base_id = g_base_id
483 AND api.person_id_Type = pid.person_id_type
484 AND pid.s_person_id_type = 'SSN'
485 AND SYSDATE BETWEEN api.start_Dt AND NVL(api.end_dt,SYSDATE);
486 c_get_person_ssn_rec c_get_person_ssn%ROWTYPE;
487
488 PROCEDURE validate_open_award_year (li_awd_rec IN igf_aw_li_awd_ints%ROWTYPE,
489 l_status_open_awd_yr OUT NOCOPY VARCHAR2) IS
490 /***************************************************************
491 Created By : nsidana
492 Date Created By : 11/28/2003
493 Purpose : Validates the open award year.
494 Known Limitations,Enhancements or Remarks
495 Change History :
496 Who When What
497 nsidana 11/28/2003 FA131 COD updates build. Added validations on two new feilds
498 added to the legacy awards table.
499 ***************************************************************/
500 CURSOR c_get_pay_isir IS
501 SELECT isir_id payment_isir_id
502 FROM igf_ap_isir_matched_all
503 WHERE base_id = g_base_id
504 AND system_record_type = 'ORIGINAL'
505 AND payment_isir = 'Y';
506
507 l_payment_isir_id igf_ap_isir_matched_all.isir_id%TYPE;
508
509 CURSOR c_get_fund_source IS
510 SELECT fund_source FROM igf_aw_fund_cat_all WHERE fund_code = li_awd_rec.fund_code;
511 l_fund_source igf_aw_fund_cat_all.fund_source%TYPE;
512
513 CURSOR c_get_fund_amt IS
514 SELECT NVL(remaining_amt,0) remaining_amt, NVL(max_award_amt,0) max_award_amt,
515 max_yearly_amt, max_life_amt
516 FROM igf_aw_fund_mast_all
517 WHERE fund_id = g_fund_id;
518 c_get_fund_amt_rec c_get_fund_amt%ROWTYPE;
519
520 CURSOR c_std_max_yr_amt IS
521 SELECT NVL(SUM(NVL(awd.accepted_amt,awd.offered_amt)), 0) yr_total
522 FROM igf_aw_award_all awd
523 WHERE awd.fund_id = g_fund_id
524 AND awd.base_id = g_base_id;
525 c_std_max_yr_amt_rec c_std_max_yr_amt%ROWTYPE;
526
527 CURSOR c_std_max_lf_count IS
528 SELECT NVL(SUM( NVL(awd.accepted_amt,awd.offered_amt)), 0) lf_total
529 FROM igf_aw_award_all awd, igf_aw_fund_mast_all fund, igf_ap_fa_base_rec_all fabase
530 WHERE fund.fund_code = g_fund_code
531 AND fabase.person_id = g_person_id
532 AND awd.base_id = fabase.base_id
533 AND awd.fund_id = fund.fund_id;
534 c_std_max_lf_count_rec c_std_max_lf_count%ROWTYPE;
535
536 CURSOR c_get_todo_items IS
537 SELECT MST.item_code, mst.todo_number
538 FROM igf_ap_td_item_mst mst, igf_aw_fund_td_map fund
539 WHERE fund.fund_id = g_fund_id
540 AND mst.todo_number = fund.item_sequence_number
541 AND fund.item_sequence_number NOT IN (SELECT item_sequence_number
542 FROM IGF_AP_TD_ITEM_INST
543 WHERE base_id = g_base_id);
544
545 -- nsidana 11/27/2003 FA131 COD updates build.
546 -- Get the details of transaction number for the base ID and the APP_TRANS_ID present in the interface table.
547 CURSOR c_get_trans_num(cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,cp_app_trans_num_txt VARCHAR2) IS
548 SELECT transaction_num
549 FROM
550 igf_ap_isir_matched
551 WHERE
552 BASE_ID=cp_base_id AND
553 transaction_num=cp_app_trans_num_txt;
554 l_trans_num NUMBER;
555
556 BEGIN -- begin of validate_open_award_year
557
558 l_status_open_awd_yr :='S';
559 l_payment_isir_id := NULL; l_fund_source := NULL; c_get_fund_amt_rec := NULL;
560
561 OPEN c_get_pay_isir; FETCH c_get_pay_isir INTO l_payment_isir_id; CLOSE c_get_pay_isir;
562 OPEN c_get_fund_source; FETCH c_get_fund_source INTO l_fund_source; CLOSE c_get_fund_source;
563 OPEN c_get_fund_amt; FETCH c_get_fund_amt INTO c_get_fund_amt_rec; CLOSE c_get_fund_amt;
564
565 IF(FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level)THEN
566 g_debug_string := 'Fund ID'||g_fund_id||', Payment Isir'||l_payment_isir_id||',Fund Source'||l_fund_source||
567 ',Remaining Amt'||c_get_fund_amt_rec.remaining_amt||',Max Award Amount'||
568 c_get_fund_amt_rec.max_Award_amt||',Max Yearly Amt'||c_get_fund_amt_rec.max_yearly_amt||
569 ',Max Life Amount'||c_get_fund_amt_rec.max_life_amt;
570 END IF;
571
572 -- check if the student is having a valid isir when the context fund is FEDERAL fund. student should have
573 -- valid active isir and payment isir
574 IF(l_payment_isir_id IS NULL AND l_fund_source IS NOT NULL AND l_fund_source = 'FEDERAL') THEN
575 l_status_open_awd_yr :='E';
576 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_ISIR_NOT_PRESENT');
577 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
578 END IF;
579
580 --<nsidana 11/27/2003 FA131 COD updates>
581 IF ((l_fund_source = 'FEDERAL') and (upper(li_awd_rec.fund_code)='PELL') AND (li_awd_rec.app_trans_num_txt IS NOT NULL))
582 THEN
583 OPEN c_get_trans_num(g_base_id,li_awd_rec.app_trans_num_txt);
584 FETCH c_get_trans_num INTO l_trans_num;
585 CLOSE c_get_trans_num;
586 IF (l_trans_num IS NULL)
587 THEN
588 l_return_value :='E';
589 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_NO_ISIR_TRANS'); -- New message to be entered.
590 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
591 END IF;
592 END IF;
593 --</nsidana 11/27/2003 FA131 COD>
594
595 -- if the award amount > fund remaining amount
596 IF(li_awd_rec.offered_amt > c_get_fund_amt_rec.remaining_amt)THEN
597 l_status_open_awd_yr :='E';
598 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_AMT_GT_REM_AMT');
599 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
600 END IF;
601
602 -- award amount greater than the fund max amount
603 --akomurav 5478287
604 --if the max_award_amt is null do not do this validation
605
606 IF(c_get_fund_amt_rec.max_award_amt <> 0 AND li_awd_rec.offered_amt > c_get_fund_amt_rec.max_award_amt) THEN
607 l_status_open_awd_yr :='E';
608 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_AMT_GT_MAX_AMT');
609 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
610 END IF;
611
612 -- sum of total year award amount and legacy award amount greater than the maximum year amount
613 -- defined at the fund
614 c_std_max_yr_amt_rec := NULL;
615 OPEN c_std_max_yr_amt; FETCH c_std_max_yr_amt INTO c_std_max_yr_amt_rec; CLOSE c_std_max_yr_amt;
616 IF(FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level)THEN
617 g_debug_string := g_debug_string ||'Year Total'||c_std_max_yr_amt_rec.yr_total;
618 END IF;
619 IF((c_get_fund_amt_rec.max_yearly_amt IS NOT NULL) AND
620 ((c_std_max_yr_amt_rec.yr_total + li_awd_rec.offered_amt) > c_get_fund_amt_rec.max_yearly_amt))THEN
621 l_status_open_awd_yr :='E';
622 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_AMT_GT_YR_AMT');
623 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
624 END IF;
625
626 -- sum of total life award amount and legacy award amount greater than the maximum life amount defined
627 -- at the fund
628 c_std_max_lf_count_rec := NULL;
629 OPEN c_std_max_lf_count; FETCH c_std_max_lf_count INTO c_std_max_lf_count_rec; CLOSE c_std_max_lf_count;
630 IF(FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level)THEN
631 g_debug_string := g_debug_string ||'Life Total'||c_std_max_lf_count_rec.lf_total;
632 END IF;
633 IF((c_get_fund_amt_rec.max_life_amt IS NOT NULL) AND
634 ((c_std_max_lf_count_rec.lf_total + li_awd_rec.offered_amt) > c_get_fund_amt_rec.max_life_amt))THEN
635 l_status_open_awd_yr :='E';
636 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_AMT_GT_LIFE_AMT');
637 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
638 END IF;
639 -- disbursement validation is present at the disbursement level. 'coz the disb intf record is not
640 -- yet processed
641
642 -- Check To Do's are present at the Fund level and the same To Do's are not assigned to FA Base
643 FOR c_get_todo_items_rec IN c_get_todo_items LOOP
644 IF(l_status_open_awd_yr <> 'E')THEN
645 l_status_open_awd_yr := 'W';
646 END IF;
647 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_TODO_NT_FND');
648 FND_MESSAGE.SET_TOKEN('TODO',c_get_todo_items_rec.item_code);
649 FND_MESSAGE.SET_TOKEN('FUND',g_fund_code);
650 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
651 END LOOP;
652
653 IF(FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level)THEN
654 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_aw_li_import.validate_open_award_year.debug',g_debug_string);
655 g_debug_string := NULL;
656 END IF;
657 EXCEPTION WHEN OTHERS THEN
658 l_status_open_awd_yr :='E';
659 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
660 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
661 'igf.plsql.igf_aw_li_import.validate_open_award_year.exception',
662 SQLERRM );
663 END IF;
664 FND_MESSAGE.SET_NAME('IGF','IGF_GE_UNHANDLED_EXP');
665 FND_MESSAGE.SET_TOKEN('NAME','VALIDATE_OPEN_AWARD_YEAR : '||SQLERRM);
666 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
667 END validate_open_award_year;
668
669 /*
670 Created By : bvisvana
671 Created On : 24-May-2005
672 Purpose : Identifies whether the award attribute is changed or not.
673 Known limitations, enhancements or remarks :
674 Change History :
675 Who When What
676 -------------------------------------
677 -------------------------------------
678 (reverse chronological order - newest change first)
679 */
680
681 FUNCTION isChangeIn_AwardAttribute(p_award_atrr_code IN igf_aw_award_level_hist.award_attrib_code%TYPE)
682 RETURN BOOLEAN
683 AS
684 l_changed BOOLEAN := FALSE;
685 BEGIN
686 -- For offered amount change
687 IF (p_award_atrr_code = 'IGF_AW_AMOUNT_OFFERED') THEN
688 IF(NVL(g_old_award_rec.OFFERED_AMT,0) <> NVL(g_new_award_rec.OFFERED_AMT,0)) THEN
689 l_changed := TRUE;
690 END IF;
691 END IF;
692 -- For Accepted amount change
693 IF (p_award_atrr_code = 'IGF_AW_AMOUNT_ACCEPTED') THEN
694 IF (NVL(g_old_award_rec.ACCEPTED_AMT,0) <> NVL(g_new_award_rec.ACCEPTED_AMT,0)) THEN
695 l_changed := TRUE;
696 END IF;
697 END IF;
698 -- For Paid amount change
699 IF (p_award_atrr_code = 'IGF_AW_AMOUNT_PAID') THEN
700 IF (NVL(g_old_award_rec.PAID_AMT ,0) <> NVL(g_new_award_rec.PAID_AMT ,0)) THEN
701 l_changed := TRUE;
702 END IF;
703 END IF;
704 -- For Award Status change
705 IF (p_award_atrr_code = 'IGF_AW_AWARD_STATUS') THEN
706 IF (NVL(g_old_award_rec.AWARD_STATUS ,'*') <> NVL(g_new_award_rec.AWARD_STATUS,'*')) THEN
707 l_changed := TRUE;
708 END IF;
709 END IF;
710 -- For Award Distribution plan change
711 IF (p_award_atrr_code = 'IGF_AW_DIST_PLAN') THEN
712 IF (NVL(g_old_award_rec.ADPLANS_ID ,-1) <> NVL(g_new_award_rec.ADPLANS_ID,-1)) THEN
713 l_changed := TRUE;
714 END IF;
715 END IF;
716 -- For lock award change
717 IF (p_award_atrr_code = 'IGF_AW_LOCK_STATUS') THEN
718 IF (NVL(g_old_award_rec.LOCK_AWARD_FLAG ,'*') <> NVL(g_new_award_rec.LOCK_AWARD_FLAG,'*')) THEN
719 l_changed := TRUE;
720 END IF;
721 END IF;
722
723 RETURN l_changed;
724 END isChangeIn_AwardAttribute;
725
726 PROCEDURE create_new_award_hist_rec IS
727 /***************************************************************
728 Created By : bvisvana
729 Date Created By : 17-Oct-2005
730 Purpose : Bug # 4635941 - Since new award is created when runnig in update mode, there wud be some difference in
731 award attributes.This function will identify the differences in the old award (deleted) and new award (created).
732 For the difference in award attributes, it inserts a history record
733 Known Limitations,Enhancements or Remarks
734 Change History :
735 Who When What
736 ***************************************************************/
737 CURSOR c_lookup_attribute IS
738 SELECT lookup_code FROM igf_lookups_view
739 WHERE lookup_type = 'IGF_AW_AWARD_ATTRIBUTES';
740
741 l_award_hist_tran_id igf_aw_award_level_hist.award_hist_tran_id%TYPE;
742
743 l_award_atrr_code igf_aw_award_level_hist.award_attrib_code%TYPE;
744 l_awd_attr_changed BOOLEAN := FALSE;
745 l_row_id VARCHAR2(30) ;
746
747 BEGIN
748 -- Get the details of the newly created award
749 OPEN c_get_awd_details(cp_award_id => g_award_id);
750 FETCH c_get_awd_details INTO g_new_award_rec;
751 CLOSE c_get_awd_details;
752
753 IF g_old_award_rec.award_id IS NOT NULL AND g_new_award_rec.award_id IS NOT NULL THEN
754 -- Get the new transaction Id
755 SELECT igf_aw_award_level_hist_s.NEXTVAL INTO l_award_hist_tran_id from dual;
756 -- insert history record for the 6 attributes IF any change
757 l_row_id := null;
758 OPEN c_lookup_attribute;
759 LOOP
760 l_awd_attr_changed := FALSE;
761 FETCH c_lookup_attribute INTO l_award_atrr_code;
762 EXIT WHEN c_lookup_attribute%NOTFOUND;
763 l_awd_attr_changed := isChangeIn_AwardAttribute(l_award_atrr_code);
764 l_row_id := null;
765 /* If award attributes Change, then insert */
766 IF (l_awd_attr_changed) THEN
767 igf_aw_award_level_hist_pkg.insert_row
768 (
769 x_rowid => l_row_id,
770 x_award_id => g_new_award_rec.award_id,
771 x_award_hist_tran_id => l_award_hist_tran_id,
772 x_award_attrib_code => l_award_atrr_code,
773 x_award_change_source_code => 'CONCURRENT_PROCESS',
774 x_old_offered_amt => g_old_award_rec.offered_amt,
775 x_new_offered_amt => g_new_award_rec.offered_amt,
776 x_old_accepted_amt => g_old_award_rec.accepted_amt,
777 x_new_accepted_amt => g_new_award_rec.accepted_amt,
778 x_old_paid_amt => g_old_award_rec.paid_amt,
779 x_new_paid_amt => g_new_award_rec.paid_amt,
780 x_old_lock_award_flag => g_old_award_rec.lock_award_flag,
781 x_new_lock_award_flag => g_new_award_rec.lock_award_flag,
782 x_old_award_status_code => g_old_award_rec.award_status,
783 x_new_award_status_code => g_new_award_rec.award_status,
784 x_old_adplans_id => g_old_award_rec.adplans_id,
785 x_new_adplans_id => g_new_award_rec.adplans_id,
786 x_mode => 'R'
787 );
788 END IF;
789 END LOOP; -- End of Loop - 6 attribute comparison ends
790 CLOSE c_lookup_attribute;
791 END IF; -- End of records NOT NULL check
792
793 EXCEPTION
794 WHEN OTHERS THEN
795 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
796 fnd_message.set_token('NAME','igf_aw_li_import.create_new_award_hist_rec' || SQLERRM);
797 igs_ge_msg_stack.add;
798 app_exception.raise_exception;
799 END create_new_award_hist_rec;
800
801 PROCEDURE maintain_old_award_hist_rec IS
802 /***************************************************************
803 Created By : bvisvana
804 Date Created By : 17-Oct-2005
805 Purpose : Bug # 4635941 - Maintains the Award History details of the deleted award and assigns
806 those to the newly created award (g_award_id)
807 Known Limitations,Enhancements or Remarks
808 Change History :
809 Who When What
810 ***************************************************************/
811 BEGIN
812 FOR i IN g_old_award_hist_col.FIRST..g_old_award_hist_col.LAST LOOP
813 INSERT INTO igf_aw_award_level_hist (
814 award_id,
815 award_hist_tran_id,
816 award_attrib_code,
817 award_change_source_code,
818 old_offered_amt,
819 new_offered_amt,
820 old_accepted_amt,
821 new_accepted_amt,
822 old_paid_amt,
823 new_paid_amt,
824 old_lock_award_flag,
825 new_lock_award_flag,
826 old_award_status_code,
827 new_award_status_code,
828 old_adplans_id,
829 new_adplans_id,
830 created_by,
831 creation_date,
832 last_updated_by,
833 last_update_date,
834 last_update_login,
835 request_id,
836 program_id,
837 program_application_id,
838 program_update_date
839 ) VALUES (
840 g_award_id,
841 g_old_award_hist_col(i).award_hist_tran_id,
842 g_old_award_hist_col(i).award_attrib_code,
843 g_old_award_hist_col(i).award_change_source_code,
844 g_old_award_hist_col(i).old_offered_amt,
845 g_old_award_hist_col(i).new_offered_amt,
846 g_old_award_hist_col(i).old_accepted_amt,
847 g_old_award_hist_col(i).new_accepted_amt,
848 g_old_award_hist_col(i).old_paid_amt,
849 g_old_award_hist_col(i).new_paid_amt,
850 g_old_award_hist_col(i).old_lock_award_flag,
851 g_old_award_hist_col(i).new_lock_award_flag,
852 g_old_award_hist_col(i).old_award_status_code,
853 g_old_award_hist_col(i).new_award_status_code,
854 g_old_award_hist_col(i).old_adplans_id,
855 g_old_award_hist_col(i).new_adplans_id,
856 g_old_award_hist_col(i).created_by,
857 g_old_award_hist_col(i).creation_date,
858 g_old_award_hist_col(i).last_updated_by,
859 g_old_award_hist_col(i).last_update_date,
860 g_old_award_hist_col(i).last_update_login,
861 g_old_award_hist_col(i).request_id,
862 g_old_award_hist_col(i).program_id,
863 g_old_award_hist_col(i).program_application_id,
864 g_old_award_hist_col(i).program_update_date
865 );
866 END LOOP;
867 END maintain_old_award_hist_rec;
868
869 PROCEDURE create_award_record(li_awd_rec IN igf_aw_li_awd_ints%ROWTYPE, l_awd_ins_status OUT NOCOPY VARCHAR2) IS
870 /***************************************************************
871 Created By : nsidana
872 Date Created By : 11/28/2003
873 Purpose : Created awards in the system.
874 Known Limitations,Enhancements or Remarks
875 Change History :
876 Who When What
877 ***************************************************************/
878 l_awd_rowid VARCHAR2(25);
879 l_alt_pell_schedule igf_aw_award_all.alt_pell_schedule%TYPE;
880
881 CURSOR c_visa (cv_person_id hz_parties.party_id%TYPE ) IS
882 SELECT a.visa_type,
883 a.visa_category,
884 a.visa_number,
885 a.visa_expiry_date,
886 b.visit_start_date entry_date
887 FROM igs_pe_visa a,
888 igs_pe_visit_histry b
889 WHERE a.person_id = cv_person_id
890 AND NVL(a.visa_expiry_date,SYSDATE) >= SYSDATE
891 AND a.visa_id = b.visa_id
892 ORDER BY a.visa_expiry_date DESC;
893
894 visa_rec c_visa%ROWTYPE;
895
896 CURSOR c_hzp (cv_person_id hz_parties.party_id%TYPE ) IS
897 SELECT person_first_name,
898 person_last_name,
899 address1,
900 address2,
901 address3,
902 address4,
903 city,
904 state,
905 province,
906 county,
907 country
908 FROM hz_parties
909 WHERE party_id = cv_person_id;
910
911 hzp_rec c_hzp%ROWTYPE;
912 l_warning VARCHAR2(200);
913
914 BEGIN --begin of create_award_record
915
916 l_awd_rowid := NULL; g_award_id:= NULL;
917 IF(li_awd_rec.alt_pell_schedule_type = 'Y')THEN
918 l_alt_pell_schedule := 'A';
919 ELSIF(li_awd_rec.alt_pell_schedule_type IS NULL)THEN
920 l_alt_pell_schedule := 'R';
921 END IF;
922 igf_aw_award_pkg.insert_row(
923 x_rowid => l_awd_rowid,
924 x_award_id => g_award_id,
925 x_fund_id => g_fund_id,
926 x_base_id => g_base_id,
927 x_offered_amt => li_awd_rec.offered_amt,
928 x_accepted_amt => li_awd_rec.accepted_amt,
929 x_paid_amt => NULL,
930 x_packaging_type => NULL,
931 x_batch_id => NULL,
932 x_manual_update => NULL,
933 x_rules_override => NULL,
934 x_award_date => li_awd_rec.award_date,
935 x_award_status => li_awd_rec.award_status_code,
936 x_attribute_category => NULL,
937 x_attribute1 => NULL,
938 x_attribute2 => NULL,
939 x_attribute3 => NULL,
940 x_attribute4 => NULL,
941 x_attribute5 => NULL,
942 x_attribute6 => NULL,
943 x_attribute7 => NULL,
944 x_attribute8 => NULL,
945 x_attribute9 => NULL,
946 x_attribute10 => NULL,
947 x_attribute11 => NULL,
948 x_attribute12 => NULL,
949 x_attribute13 => NULL,
950 x_attribute14 => NULL,
951 x_attribute15 => NULL,
952 x_attribute16 => NULL,
953 x_attribute17 => NULL,
954 x_attribute18 => NULL,
955 x_attribute19 => NULL,
956 x_attribute20 => NULL,
957 x_rvsn_id => NULL,
958 x_alt_pell_schedule => l_alt_pell_schedule,
959 x_award_number_txt => li_awd_rec.award_number_txt,
960 x_legacy_record_flag => 'Y',
961 x_adplans_id => NULL,
962 x_lock_award_flag => li_awd_rec.lock_award_flag,
963 x_app_trans_num_txt => li_awd_rec.app_trans_num_txt,
964 x_awd_proc_status_code => NULL,
965 x_notification_status_code => 'R',
966 x_notification_status_date => TRUNC(SYSDATE),
967 x_publish_in_ss_flag => li_awd_rec.publish_in_ss_flag
968 );
969 IF(l_awd_rowid IS NULL AND g_award_id IS NULL) THEN
970 l_awd_ins_status := 'E';
971 ELSE
972 l_awd_ins_status := 'S';
973 -- bvisvana - Bug # 4635941 - START
974 IF g_update_mode AND g_old_award_hist_col.COUNT > 0 THEN
975 -- Maintain / preserve the old award history if running in UPDATE MODE and if some history exists
976 maintain_old_award_hist_rec;
977 END IF;
978 -- While running in update mode, there wud be some changes and hence new award history should be inserted
979 IF g_update_mode THEN
980 create_new_award_hist_rec;
981 -- Clear the mode and history collection
982 g_update_mode := FALSE;
983 g_old_award_hist_col.DELETE;
984 END IF;
985 -- bvisvana - Bug # 4635941 - END
986 END IF;
987
988 IF(g_fed_fund_code = 'FWS' AND li_awd_rec.authorization_date IS NOT NULL
989 AND TRUNC(li_awd_rec.authorization_date) <= TRUNC(SYSDATE) AND l_awd_ins_status <> 'E')THEN
990 BEGIN
991 OPEN c_hzp (g_person_id);
992 FETCH c_hzp INTO hzp_rec;
993 CLOSE c_hzp;
994 OPEN c_visa (g_person_id);
995 FETCH c_visa INTO visa_rec;
996
997 IF c_visa%NOTFOUND AND (hzp_rec.country IS NOT NULL AND hzp_rec.country <> 'US') THEN
998 fnd_message.set_name('FND','FND_MBOX_WARN_CONSTANT');
999 l_warning := fnd_message.get;
1000 fnd_message.set_name('IGF','IGF_SE_INVALID_SETUP');
1001 fnd_message.set_token('PLACE','VISA');
1002 fnd_file.put_line( fnd_file.log,' -- '||l_warning ||' : '|| fnd_message.get);
1003 CLOSE c_visa;
1004 IF(l_awd_ins_status <> 'E')THEN
1005 l_awd_ins_status := 'W';
1006 END IF;
1007
1008 ELSE
1009 CLOSE c_visa;
1010 END IF;
1011
1012 igf_se_gen_001.send_work_auth(
1013 g_base_id,
1014 g_person_id,
1015 g_fund_id,
1016 g_award_id,
1017 NULL,
1018 NULL,
1019 'LEGACY',
1020 li_awd_rec.authorization_date
1021 );
1022
1023 EXCEPTION
1024 WHEN igf_se_gen_001.IGFSEGEN001 THEN
1025 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
1026 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'igf.plsql.igf_aw_li_import.create_work_authorization.exception',SQLERRM);
1027 END IF;
1028 l_awd_ins_status := 'E';
1029 RETURN;
1030
1031 WHEN OTHERS THEN
1032 FND_MESSAGE.SET_NAME('IGF','IGF_GE_UNHANDLED_EXP');
1033 FND_MESSAGE.SET_TOKEN('NAME','CREATING WORK AUTHORIZATION : '||SQLERRM);
1034 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD(' ',3,' ')||'-- '||FND_MESSAGE.GET);
1035 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
1036 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'igf.plsql.igf_aw_li_import.create_work_authorization.exception',SQLERRM);
1037 END IF;
1038 l_awd_ins_status := 'E';
1039 RETURN;
1040 END;
1041 END IF;
1042
1043 EXCEPTION WHEN OTHERS THEN
1044 l_awd_ins_status := 'E';
1045 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
1046 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
1047 'igf.plsql.igf_aw_li_import.create_award_record.exception',
1048 SQLERRM );
1049 END IF;
1050 FND_MESSAGE.SET_NAME('IGF','IGF_GE_UNHANDLED_EXP');
1051 FND_MESSAGE.SET_TOKEN('NAME','CREATE_AWARD_RECORD : '||SQLERRM);
1052 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1053 END create_award_record;
1054
1055 PROCEDURE delete_award_and_child_records(p_award_id IN igf_aw_award_all.award_id%TYPE) IS
1056 /***************************************************************
1057 Created By : nsidana
1058 Date Created By : 11/28/2003
1059 Purpose : deletes awards and child records.
1060 Known Limitations,Enhancements or Remarks
1061 Change History :
1062 Who When What
1063 bvisvana 17-Oct-2005 Bug # 4635941 - Capturing the award history before deleting the award
1064 Capturing the award details before deleting the award
1065 ***************************************************************/
1066 CURSOR c_get_awd_row IS
1067 SELECT ROWID row_id
1068 FROM igf_aw_award_all
1069 WHERE award_id = p_award_id;
1070
1071 -- bvisvana - Bug # 4635941 - Added new cursors
1072 CURSOR c_get_awd_hist IS
1073 SELECT ROWID row_id
1074 FROM igf_aw_award_level_hist
1075 WHERE award_id = p_award_id;
1076
1077 CURSOR c_get_awd_hist_details IS
1078 SELECT * FROM igf_aw_award_level_hist
1079 WHERE award_id = p_award_id;
1080 c_award_hist_rec c_get_awd_hist_details%ROWTYPE;
1081
1082 CURSOR c_get_disb_row IS
1083 SELECT ROWID row_id, auth_id
1084 FROM igf_aw_awd_disb_all
1085 WHERE award_id = p_award_id;
1086
1087 CURSOR c_get_disb_dtl_row IS
1088 SELECT ROWID row_id
1089 FROM igf_db_awd_disb_dtl_all
1090 WHERE award_id = p_award_id;
1091
1092 CURSOR c_get_disb_holds_row IS
1093 SELECT ROWID row_id
1094 FROM igf_db_disb_holds_all
1095 WHERE award_id = p_award_id;
1096
1097 CURSOR c_get_auth_row (cp_auth_id igf_se_auth.auth_id%TYPE) IS
1098 SELECT ROWID row_id
1099 FROM igf_se_auth
1100 WHERE auth_id = cp_auth_id;
1101
1102 CURSOR c_get_disb_chg_dtls_row (p_award_id igf_aw_award_all.award_id%TYPE) IS
1103 SELECT ROWID row_id
1104 FROM igf_aw_db_chg_dtls
1105 WHERE award_id = p_award_id;
1106
1107 CURSOR c_get_cod_dtl_row (p_award_id igf_aw_award_all.award_id%TYPE) IS
1108 SELECT ROWID row_id
1109 FROM igf_aw_db_cod_dtls
1110 WHERE award_id = p_award_id;
1111
1112
1113 BEGIN --begin of delete_award_and_child_records
1114
1115 -- bvisvana - Bug # 4635941 - START
1116 -- While deleting the award, collect details of the award and also its history.
1117 g_hist_cnt := 0;
1118 OPEN c_get_awd_details(cp_award_id => p_award_id);
1119 FETCH c_get_awd_details INTO g_old_award_rec;
1120 CLOSE c_get_awd_details;
1121
1122 OPEN c_get_awd_hist_details;
1123 LOOP
1124 FETCH c_get_awd_hist_details INTO c_award_hist_rec;
1125 EXIT WHEN c_get_awd_hist_details%NOTFOUND;
1126 g_hist_cnt := g_hist_cnt + 1;
1127 g_old_award_hist_col(g_hist_cnt).award_id := c_award_hist_rec.award_id;
1128 g_old_award_hist_col(g_hist_cnt).award_hist_tran_id := c_award_hist_rec.award_hist_tran_id;
1129 g_old_award_hist_col(g_hist_cnt).award_attrib_code := c_award_hist_rec.award_attrib_code;
1130 g_old_award_hist_col(g_hist_cnt).award_change_source_code := c_award_hist_rec.award_change_source_code;
1131 g_old_award_hist_col(g_hist_cnt).old_offered_amt := c_award_hist_rec.old_offered_amt;
1132 g_old_award_hist_col(g_hist_cnt).new_offered_amt := c_award_hist_rec.new_offered_amt;
1133 g_old_award_hist_col(g_hist_cnt).old_accepted_amt := c_award_hist_rec.old_accepted_amt;
1134 g_old_award_hist_col(g_hist_cnt).new_accepted_amt := c_award_hist_rec.new_accepted_amt;
1135 g_old_award_hist_col(g_hist_cnt).old_paid_amt := c_award_hist_rec.old_paid_amt;
1136 g_old_award_hist_col(g_hist_cnt).new_paid_amt := c_award_hist_rec.new_paid_amt;
1137 g_old_award_hist_col(g_hist_cnt).old_lock_award_flag := c_award_hist_rec.old_lock_award_flag;
1138 g_old_award_hist_col(g_hist_cnt).new_lock_award_flag := c_award_hist_rec.new_lock_award_flag;
1139 g_old_award_hist_col(g_hist_cnt).old_award_status_code := c_award_hist_rec.old_award_status_code;
1140 g_old_award_hist_col(g_hist_cnt).new_award_status_code := c_award_hist_rec.new_award_status_code;
1141 g_old_award_hist_col(g_hist_cnt).old_adplans_id := c_award_hist_rec.old_adplans_id;
1142 g_old_award_hist_col(g_hist_cnt).new_adplans_id := c_award_hist_rec.new_adplans_id;
1143 g_old_award_hist_col(g_hist_cnt).created_by := c_award_hist_rec.created_by;
1144 g_old_award_hist_col(g_hist_cnt).creation_date := c_award_hist_rec.creation_date;
1145 g_old_award_hist_col(g_hist_cnt).last_updated_by := c_award_hist_rec.last_updated_by;
1146 g_old_award_hist_col(g_hist_cnt).last_update_date := c_award_hist_rec.last_update_date;
1147 g_old_award_hist_col(g_hist_cnt).last_update_login := c_award_hist_rec.last_update_login;
1148 g_old_award_hist_col(g_hist_cnt).request_id := c_award_hist_rec.request_id;
1149 g_old_award_hist_col(g_hist_cnt).program_application_id := c_award_hist_rec.program_application_id;
1150 g_old_award_hist_col(g_hist_cnt).program_id := c_award_hist_rec.program_id;
1151 g_old_award_hist_col(g_hist_cnt).program_update_date := c_award_hist_rec.program_update_date;
1152 END LOOP;
1153 CLOSE c_get_awd_hist_details;
1154 -- bvisvana - Bug # 4635941 - END
1155
1156 -- while deleting the record in IGF_AW_AWD_DISB_ALL delete also from IGF_AW_DB_CHG_DTLS
1157
1158 FOR c_get_disb_dtl_row_rec IN c_get_disb_dtl_row LOOP
1159 igf_db_awd_disb_dtl_pkg.delete_row(c_get_disb_dtl_row_rec.row_id);
1160 END LOOP;
1161
1162 FOR c_get_cod_dtl_row_rec IN c_get_cod_dtl_row (p_award_id) LOOP
1163 igf_aw_db_cod_dtls_pkg.delete_row(c_get_cod_dtl_row_rec.row_id);
1164 END LOOP;
1165
1166 FOR c_get_disb_chg_dtls_row_rec IN c_get_disb_chg_dtls_row(p_award_id) LOOP
1167 igf_aw_db_chg_dtls_pkg.delete_row(c_get_disb_chg_dtls_row_rec.row_id);
1168 END LOOP;
1169
1170 FOR c_get_disb_holds_row_rec IN c_get_disb_holds_row LOOP
1171 igf_db_disb_holds_pkg.delete_row(c_get_disb_holds_row_rec.row_id);
1172 END LOOP;
1173
1174 FOR c_get_disb_row_rec IN c_get_disb_row LOOP
1175 -- Delete the authorization record for the corresponsing Disb
1176 IF c_get_disb_row_rec.auth_id IS NOT NULL THEN
1177 FOR c_get_auth_row_rec IN c_get_auth_row(c_get_disb_row_rec.auth_id) LOOP
1178 igf_se_auth_pkg.delete_row(c_get_auth_row_rec.row_id);
1179 END LOOP;
1180 END IF;
1181 igf_aw_awd_disb_pkg.delete_row(c_get_disb_row_rec.row_id);
1182 END LOOP;
1183
1184 -- bvisvana - Bug # 4635941 - Delete the Award History
1185 FOR award_hist_rec IN c_get_awd_hist LOOP
1186 igf_aw_award_level_hist_pkg.delete_row(award_hist_rec.row_id);
1187 END LOOP;
1188
1189 FOR c_get_awd_row_rec IN c_get_awd_row LOOP
1190 igf_aw_award_pkg.delete_row(c_get_awd_row_rec.row_id);
1191 END LOOP;
1192
1193 END delete_award_and_child_records;
1194
1195 PROCEDURE upd_aw_rec_with_legacy_status(p_award_id IN igf_aw_award_all.award_id%TYPE) IS
1196 /***************************************************************
1197 Created By : nsidana
1198 Date Created By : 11/28/2003
1199 Purpose : updates awards records with legacy status.
1200 Known Limitations,Enhancements or Remarks
1201 Change History :
1202 Who When What
1203 ***************************************************************/
1204 CURSOR c_get_award IS
1205 SELECT ROWID row_id,awd.*
1206 FROM igf_aw_award_all awd
1207 WHERE award_id = p_award_id;
1208 c_get_award_rec c_get_award%ROWTYPE;
1209 BEGIN --begin of upd_aw_rec_with_legacy_status
1210
1211 OPEN c_get_award; FETCH c_get_award INTO c_get_award_rec; CLOSE c_get_award;
1212 igf_aw_award_pkg.update_row (
1213 x_rowid => c_get_award_rec.row_id,
1214 x_award_id => c_get_award_rec.award_id,
1215 x_fund_id => c_get_award_rec.fund_id,
1216 x_base_id => c_get_award_rec.base_id,
1217 x_offered_amt => c_get_award_rec.offered_amt,
1218 x_accepted_amt => c_get_award_rec.accepted_amt,
1219 x_paid_amt => c_get_award_rec.paid_amt,
1220 x_packaging_type => c_get_award_rec.packaging_type,
1221 x_batch_id => c_get_award_rec.batch_id,
1222 x_manual_update => c_get_award_rec.manual_update,
1223 x_rules_override => c_get_award_rec.rules_override,
1224 x_award_date => c_get_award_rec.award_date,
1225 x_award_status => c_get_award_rec.award_status,
1226 x_attribute_category => c_get_award_rec.attribute_category,
1227 x_attribute1 => c_get_award_rec.attribute1,
1228 x_attribute2 => c_get_award_rec.attribute2,
1229 x_attribute3 => c_get_award_rec.attribute3,
1230 x_attribute4 => c_get_award_rec.attribute4,
1231 x_attribute5 => c_get_award_rec.attribute5,
1232 x_attribute6 => c_get_award_rec.attribute6,
1233 x_attribute7 => c_get_award_rec.attribute7,
1234 x_attribute8 => c_get_award_rec.attribute8,
1235 x_attribute9 => c_get_award_rec.attribute9,
1236 x_attribute10 => c_get_award_rec.attribute10,
1237 x_attribute11 => c_get_award_rec.attribute11,
1238 x_attribute12 => c_get_award_rec.attribute12,
1239 x_attribute13 => c_get_award_rec.attribute13,
1240 x_attribute14 => c_get_award_rec.attribute14,
1241 x_attribute15 => c_get_award_rec.attribute15,
1242 x_attribute16 => c_get_award_rec.attribute16,
1243 x_attribute17 => c_get_award_rec.attribute17,
1244 x_attribute18 => c_get_award_rec.attribute18,
1245 x_attribute19 => c_get_award_rec.attribute19,
1246 x_attribute20 => c_get_award_rec.attribute20,
1247 x_rvsn_id => c_get_award_rec.rvsn_id,
1248 x_alt_pell_schedule => c_get_award_rec.alt_pell_schedule,
1249 x_award_number_txt => c_get_award_rec.award_number_txt,
1250 x_legacy_record_flag => 'Y', -- this is required as the award record is modified to have legacy as N by other processes
1251 x_adplans_id => c_get_award_rec.adplans_id,
1252 x_app_trans_num_txt => c_get_award_rec.app_trans_num_txt,
1253 x_lock_award_flag => c_get_award_rec.lock_award_flag,
1254 x_awd_proc_status_code => c_get_award_rec.awd_proc_status_code,
1255 x_notification_status_code => c_get_award_rec.notification_status_code,
1256 x_notification_status_date => c_get_award_rec.notification_status_date,
1257 x_publish_in_ss_flag => c_get_award_rec.publish_in_ss_flag
1258 );
1259
1260 END upd_aw_rec_with_legacy_status;
1261
1262 BEGIN -- begin of validate_awdyear_int_rec
1263
1264 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
1265 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'igf.plsql.igf_aw_li_import.validate_awdyear_int_rec.debug','start' );
1266 END IF;
1267
1268 -- initialize the return statuses, assuming all the validations are successful.
1269 l_return_value :='S'; l_status_open_awd_yr :='S'; l_return_status_db := 'S';
1270 -- initialize applicable global variables here.
1271 g_base_id := NULL; g_fund_code := NULL; g_fed_fund_code := NULL; g_fund_id := NULL;
1272 g_processing_string := igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','PROCESSING') || ' '||
1273 igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','AWARD_NUMBER')|| ' : '
1274 || li_awd_rec.award_number_txt ||' '||
1275 igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','FUND_CODE')
1276 || ' : '|| li_awd_rec.fund_code;
1277 FND_FILE.PUT_LINE(FND_FILE.LOG,g_processing_string);
1278
1279 IF(li_awd_rec.import_record_type = 'U' OR li_awd_rec.import_record_type IS NULL) THEN
1280 g_base_id := get_base_id_from_per_num(li_awd_rec.person_number, g_ci_cal_type, g_ci_sequence_number);
1281
1282 IF(g_base_id IS NOT NULL AND g_base_id <> -1) THEN
1283 l_award_id := NULL; l_legacy_record_flag := NULL;
1284 OPEN cur_check_sys_awd(g_base_id); FETCH cur_check_sys_awd INTO l_award_id, l_legacy_record_flag;
1285 IF(li_awd_rec.import_record_type = 'U' AND cur_check_sys_awd%NOTFOUND) THEN
1286 CLOSE cur_check_sys_awd;
1287 l_return_value :='E';
1288 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_UPD_FAIL_LINOT_FND');
1289 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1290 RETURN;
1291 ELSIF(li_awd_rec.import_record_type IS NULL AND cur_check_sys_awd%FOUND) THEN
1292 CLOSE cur_check_sys_awd;
1293 l_return_value :='E';
1294 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_AWD_ALREADY_PRSNT');
1295 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1296 RETURN;
1297 ELSIF((li_awd_rec.import_record_type = 'U' AND cur_check_sys_awd%FOUND) OR
1298 (li_awd_rec.import_record_type IS NULL AND cur_check_sys_awd%NOTFOUND)
1299 ) THEN
1300 CLOSE cur_check_sys_awd;
1301
1302 -- validate all the fields in the interface table. do not return unless all the fields are validated
1303
1304
1305 -- first validate if the award year status code is valid
1306 IF(li_awd_rec.award_status_code IN ('E_ACCEPTED','E_OFFERED','REVISED','SIMULATED','STOPPED')
1307 OR igf_ap_gen.get_aw_lookup_meaning('IGF_AWARD_STATUS',li_awd_rec.award_status_code, g_sys_award_year) IS NULL)THEN
1308 l_return_value :='E';
1309 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_INVALID_AWD_STATUS');
1310 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1311 END IF;
1312
1313 -- second, validate if the fund code in the interface table matches with that of the fund master
1314
1315 g_fund_code := NULL; g_fed_fund_code := NULL; g_fund_id := NULL;
1316 OPEN cur_get_fund_code(li_awd_rec.fund_code); FETCH cur_get_fund_code INTO g_fund_code, g_fed_fund_code, g_fund_id; CLOSE cur_get_fund_code;
1317 IF(FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level)THEN
1318 g_debug_string := g_debug_string || 'Fund Code'||g_fund_code||'Fed Fund Code'||g_fed_fund_code||'Fund Id'||g_fund_id;
1319 END IF;
1320 IF(g_fund_code IS NULL OR g_fund_id IS NULL)THEN
1321
1322 l_return_value :='E';
1323 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_FUND_CODE_NOT_FND');
1324 FND_MESSAGE.SET_TOKEN('FUND_CODE',li_awd_rec.fund_code);
1325 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1326 ELSE
1327
1328 SAVEPOINT st_def_del_award_records;
1329 -- delete production records only if legacy record is present in the system with legacy_record_flag = Y
1330 IF(l_award_id IS NOT NULL)THEN
1331 IF(NVL(l_legacy_record_flag,'N') = 'Y')THEN
1332
1333 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
1334 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'igf.plsql.igf_aw_li_import.validate_awdyear_int_rec.debug','calling DELETE with award id' || l_award_id );
1335 END IF;
1336 g_update_mode := TRUE; -- bvisvana - Bug # 4635941 - To track the update mode
1337 delete_award_and_child_records(l_award_id);
1338
1339 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
1340 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'igf.plsql.igf_aw_li_import.validate_awdyear_int_rec.debug','deleted records with award id' || l_award_id );
1341 END IF;
1342 ELSE
1343 l_return_value :='E';
1344 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_UPD_FAILED_NOT_LI'); --sjalasut, should this message b changed ?
1345 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1346 RETURN;
1347 END IF;
1348 END IF;
1349
1350 -- second, variant one. introduced in FACR117. Sponsorships cannot be imported for open award year.
1351 -- no further validations are required on this award if its a sponsorship and the award year is open
1352 IF(g_fed_fund_code = 'SPNSR' AND g_award_year_status_code = 'O')THEN
1353 l_return_value :='E';
1354 FND_MESSAGE.SET_NAME('IGF','IGF_SP_LI_NOT_IMP_4OPEN_AWDYR');
1355 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1356 RETURN;
1357 END IF;
1358
1359 --
1360 -- External Awards Import can only import EXT funds
1361 --
1362 IF g_entry_point = 'EXTERNAL' AND g_fed_fund_code <> 'EXT' THEN
1363 l_return_value :='E';
1364 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_FUND_CODE_FAIL');
1365 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1366 RETURN;
1367 END IF;
1368
1369 -- variant two. If the sponsorship has financial aid hold information, error out and do not proceed further with
1370 -- this award.
1371 IF(g_fed_fund_code = 'SPNSR')THEN
1372 c_get_fa_hold_spnsr_rec := NULL;
1373 OPEN c_get_fa_hold_spnsr;FETCH c_get_fa_hold_spnsr INTO c_get_fa_hold_spnsr_rec; CLOSE c_get_fa_hold_spnsr;
1374 IF(c_get_fa_hold_spnsr_rec.exist_hold IS NOT NULL)THEN
1375 l_return_value :='E';
1376 FND_MESSAGE.SET_NAME('IGF','IGF_SP_LI_HOLD_PRSNT');
1377 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1378 RETURN;
1379 END IF;
1380 -- for sponsorship awards, award status should always be accepted.
1381 IF(li_awd_rec.award_status_code <> 'ACCEPTED')THEN
1382 l_return_value :='E';
1383 FND_MESSAGE.SET_NAME('IGF','IGF_SP_LI_AWD_ACPT');
1384 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1385 END IF;
1386 END IF;
1387
1388 -- variant three. a student can have only one FWS award in an award year. this check should be done here to
1389 -- save further porcessing time.
1390 IF g_fed_fund_code = 'FWS' THEN
1391 -- Authorization Date should be NULL if the context award status is Offered.
1392 IF(li_awd_rec.award_status_code = 'OFFERED' AND li_awd_rec.authorization_date IS NOT NULL)THEN
1393 l_return_value := 'E';
1394 FND_MESSAGE.SET_NAME('IGF','IGF_SE_LI_AUTH_DT_BLNK_OFFRD');
1395 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1396 END IF;
1397
1398 -- Authorization Date should not be greater than sysdate
1399 IF(li_awd_rec.authorization_date IS NOT NULL AND TRUNC(li_awd_rec.authorization_date) > TRUNC(SYSDATE))THEN
1400 l_return_value := 'E';
1401 FND_MESSAGE.SET_NAME('IGF','IGF_SE_LI_DATE_GT_SYSDATE');
1402 FND_MESSAGE.SET_TOKEN('DATE','AUTHORIZATION_DATE');
1403 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1404 END IF;
1405
1406 -- SSN is mandatory for Federal Work Study Funds
1407 c_get_person_ssn_rec := NULL;
1408 OPEN c_get_person_ssn; FETCH c_get_person_ssn INTO c_get_person_ssn_rec; CLOSE c_get_person_ssn;
1409 IF(c_get_person_ssn_rec.api_person_id IS NULL)THEN
1410 l_return_value := 'E';
1411 FND_MESSAGE.SET_NAME('IGF', 'IGF_SE_LI_SSN_NOT_PRSNT');
1412 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1413 END IF;
1414 ELSE
1415
1416 -- authorization date should be blank for all the funds except Federal Work Study
1417 IF(li_awd_rec.authorization_date IS NOT NULL)THEN
1418 l_return_value := 'E';
1419 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_AWD_COL_BLNK');
1420 FND_MESSAGE.SET_TOKEN('COLUMN_NAME','AUTHORIZATION_DATE');
1421 FND_MESSAGE.SET_TOKEN('FUND_CODE',li_awd_rec.fund_code);
1422 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1423 END IF;
1424 END IF;
1425
1426 -- third, check if the offered amount > 0
1427 IF(li_awd_rec.offered_amt <= 0)THEN
1428 l_return_value :='E';
1429 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_AWD_OFRD_AMT_GT_0');
1430 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1431 ELSE -- interface offered amount should not match with the total sponsor amt for sponsorships.
1432 -- validation introduced in FACR117
1433
1434 IF(g_fed_fund_code = 'SPNSR')THEN
1435 c_get_spnsr_amt_rec := NULL;
1436 OPEN c_get_spnsr_amt; FETCH c_get_spnsr_amt INTO c_get_spnsr_amt_rec; CLOSE c_get_spnsr_amt;
1437 IF(c_get_spnsr_amt_rec.max_yearly_amt IS NOT NULL AND li_awd_rec.offered_amt <> c_get_spnsr_amt_rec.max_yearly_amt) THEN
1438 IF(l_return_value <> 'E')THEN
1439 l_return_value := 'W';
1440 END IF;
1441 FND_MESSAGE.SET_NAME('IGF','IGF_SP_LI_TOTAL_AMT_MISMTCH');
1442 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1443 END IF;
1444 END IF;
1445 END IF;
1446
1447 -- fourth, if the award status is accepted then
1448 IF(li_awd_rec.award_status_code = 'ACCEPTED')THEN
1449 IF(li_awd_rec.accepted_amt IS NULL OR li_awd_rec.accepted_amt <= 0)THEN
1450 l_return_value :='E';
1451 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_ACPT_AMT_GT_0_ACPT');
1452 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1453 END IF;
1454 IF(li_awd_rec.accepted_amt IS NOT NULL AND li_awd_rec.accepted_amt > li_awd_rec.offered_amt)THEN
1455 l_return_value :='E';
1456 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_ACPT_AMT_GT_OFRD_AMT');
1457 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1458 END IF;
1459 -- for sponsorships, the accepted amount and the offered amount should be equal.
1460 IF(li_awd_rec.accepted_amt IS NOT NULL AND g_fed_fund_code = 'SPNSR' AND li_awd_rec.accepted_amt <> li_awd_rec.offered_amt)THEN
1461 l_return_value :='E';
1462 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_EQ_ACPT_OFRD_AMT');
1463 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1464 END IF;
1465 ELSIF(li_awd_rec.award_status_code IN ('OFFERED','CANCELLED','DECLINED'))THEN
1466 --bug #3701698
1467 IF (li_awd_rec.award_status_code = 'CANCELLED' AND
1468 ( li_awd_rec.accepted_amt IS NULL OR li_awd_rec.accepted_amt <> 0 )
1469 ) THEN
1470 l_return_value :='E';
1471 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_ACPT_AMT_AWD_CNCL');
1472 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1473
1474 ELSIF(li_awd_rec.accepted_amt < 0)THEN
1475 l_return_value :='E';
1476 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_ACPT_AMT_NEG');
1477 FND_MESSAGE.SET_TOKEN('AWD_STATUS', li_awd_rec.award_status_code);
1478 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1479 END IF;
1480 END IF;
1481
1482 -- fifth, validate Pell Alternate Schedule indicator. for this we have to validate if the fund code is
1483 -- mapped to PELL fed fund code
1484 IF(g_fed_fund_code = 'PELL')THEN
1485 IF(li_awd_rec.alt_pell_schedule_type IS NOT NULL AND li_awd_rec.alt_pell_schedule_type <> 'Y') THEN
1486 l_return_value :='E';
1487 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_INVLD_PELL_ALT_CD');
1488 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1489 END IF;
1490 IF(li_awd_rec.import_record_type IS NULL)THEN
1491 -- student can have only one PELL fund code in the given award year. check if this guy has a PELL already
1492 -- since the interface record is marked for insert. the same curosor is used for FWS also, hence the name.
1493 c_1_more_pell_fws_rec := NULL;
1494 OPEN c_1_more_pell_fws; FETCH c_1_more_pell_fws INTO c_1_more_pell_fws_rec; CLOSE c_1_more_pell_fws;
1495 IF(c_1_more_pell_fws_rec.present_in_award IS NOT NULL)THEN
1496 l_return_value :='E';
1497 FND_MESSAGE.SET_NAME('IGF','IGF_AW_MORE_PELL_AWD');
1498 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1499 END IF;
1500 END IF;
1501 ELSE -- for all awards other than Pell, the pell alternate schedule type should be null
1502 IF(li_awd_rec.alt_pell_schedule_type IS NOT NULL) THEN
1503 l_return_value :='E';
1504 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_PELL_ALT_IND_BLNK');
1505 FND_MESSAGE.SET_TOKEN('FUND_CODE',li_awd_rec.fund_code);
1506 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1507 END IF;
1508 END IF;
1509 IF(FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level)THEN
1510 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'igf.plsql.igf_aw_li_import.validate_awdyear_int_rec.debug', g_debug_string);
1511 g_debug_string := NULL;
1512 END IF;
1513
1514 -- <nsidana 11/27/2003 FA131 COD updates build>
1515 -- Adding a check to see that the new col lock_award has only valid values i.e 'Y' or null.
1516 IF (li_awd_rec.lock_award_flag IS NOT NULL) THEN
1517 IF li_awd_rec.lock_award_flag NOT IN ('Y','N') THEN
1518 l_return_value :='E';
1519 fnd_message.set_name('IGF','IGF_AW_LI_INVD_LOCK_AWD_VAL');
1520 fnd_file.put_line(fnd_file.log,fnd_message.get);
1521 END IF;
1522 ELSE
1523 li_awd_rec.lock_award_flag := 'N';
1524 END IF;
1525
1526 -- </nsidana 11/27/2003 FA131 COD updates.>
1527
1528 IF li_awd_rec.publish_in_ss_flag IS NULL OR li_awd_rec.publish_in_ss_flag NOT IN ('Y','N') THEN
1529 l_return_value := 'E';
1530 fnd_message.set_name('IGF','IGF_AP_INV_FLD_VAL');
1531 fnd_message.set_token('FIELD','PUBLISH_IN_SS_FLAG');
1532 fnd_file.put_line(fnd_file.log,fnd_message.get);
1533 END IF;
1534 -- sixth, if the context award year is open and the return status is not E then perform open year validations
1535 IF(l_return_value IN ('S','W') AND g_award_year_status_code = 'O')THEN
1536
1537 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
1538 fnd_log.string(fnd_log.level_exception, 'igf.plsql.igf_aw_li_import.validate_awdyear_int_rec.debug','calling validate open awd' );
1539 END IF;
1540
1541 validate_open_award_year(li_awd_rec, l_status_open_awd_yr);
1542
1543 END IF;
1544 IF(l_return_value IN ('S','W') AND l_status_open_awd_yr IN ('S','W')) THEN
1545 BEGIN
1546
1547 -- insert into awards table
1548 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
1549 fnd_log.string(fnd_log.level_exception, 'igf.plsql.igf_aw_li_import.validate_awdyear_int_rec.debug','calling create awd rec' );
1550 END IF;
1551
1552 create_award_record(li_awd_rec, l_return_status_awd);
1553
1554 IF(l_return_status_awd = 'E')THEN
1555 l_return_value := 'E';
1556 ROLLBACK TO st_def_del_award_records;
1557 RETURN;
1558 END IF;
1559
1560 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
1561 fnd_log.string(fnd_log.level_exception, 'igf.plsql.igf_aw_li_import.validate_awdyear_int_rec.debug','calling validate_disb_int_rec' );
1562 END IF;
1563
1564 validate_disburs_int_rec(li_awd_rec, l_return_status_db);
1565
1566 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
1567 fnd_log.string(fnd_log.level_exception, 'igf.plsql.igf_aw_li_import.validate_awdyear_int_rec.debug','status returned = ' || l_return_status_db );
1568 END IF;
1569
1570 IF(l_return_status_db = 'E') THEN
1571 l_return_value := 'E';
1572 ROLLBACK TO st_def_del_award_records;
1573 RETURN;
1574 ELSE
1575
1576 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
1577 fnd_log.string(fnd_log.level_exception, 'igf.plsql.igf_aw_li_import.validate_awdyear_int_rec.debug','upd_awd_with_leg_stat' );
1578 END IF;
1579
1580 upd_aw_rec_with_legacy_status(g_award_id);
1581
1582 END IF;
1583 -- OR condition because the open award valiations are in the disbursement validation proc also
1584 IF(l_status_open_awd_yr = 'W' OR l_return_status_db = 'W')THEN
1585 l_return_value := 'W';
1586 ELSE
1587 l_return_value := 'S';
1588 END IF;
1589 EXCEPTION WHEN OTHERS THEN
1590
1591 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
1592 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
1593 'igf.plsql.igf_aw_li_import.create_award_validate_disb_section.exception',
1594 SQLERRM);
1595 END IF;
1596 ROLLBACK TO st_def_del_award_records;
1597 l_return_value := 'E';
1598 RETURN;
1599 END;
1600 ELSE
1601 ROLLBACK TO st_def_del_award_records;
1602 l_return_value:='E';
1603 RETURN;
1604 END IF; -- return status of l_return_value and l_status_open_awd_yr
1605 END IF; -- for fund_code is null or fund_id is null
1606 END IF; --end if of check import record type and sys award year found
1607 END IF; -- end if of base_id <> -1
1608 ELSE
1609 l_return_value :='E';
1610 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_INVLD_IMP_REC_TY');
1611 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1612 RETURN;
1613 END IF;
1614 EXCEPTION WHEN OTHERS THEN
1615 ROLLBACK TO st_def_del_award_records;
1616 l_return_value :='E';
1617 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
1618 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
1619 'igf.plsql.igf_aw_li_import.validate_awdyear_int_rec.exception',
1620 SQLERRM);
1621 END IF;
1622 FND_MESSAGE.SET_NAME('IGF','IGF_GE_UNHANDLED_EXP');
1623 FND_MESSAGE.SET_TOKEN('NAME','VALIDATE_AWDYEAR_INT_REC : '||SQLERRM);
1624 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
1625 END validate_awdyear_int_rec;
1626
1627
1628 PROCEDURE validate_disburs_int_rec(li_awd_rec IN igf_aw_li_awd_ints%ROWTYPE,
1629 p_return_status OUT NOCOPY VARCHAR2
1630 ) IS
1631
1632 /***************************************************************
1633 Created By : nsidana
1634 Date Created By : 11/28/2003
1635 Purpose : Validates legacy award disbursements interface record.
1636 Known Limitations,Enhancements or Remarks
1637 Change History :
1638 Who When What
1639 nsidana 11/28/2003 FA131 COD updates build. Added validations on 1 feild in the
1640 legacy award disbursements table.
1641 ***************************************************************/
1642 CURSOR cur_legacy_disb_int IS
1643 SELECT
1644 trim(disbint.ci_alternate_code) ci_alternate_code,
1645 trim(disbint.person_number) person_number,
1646 trim(disbint.award_number_txt) award_number_txt,
1647 disbint.disbursement_num,
1648 trim(disbint.ld_alternate_code) ld_alternate_code,
1649 trim(disbint.tp_alternate_code) tp_alternate_code,
1650 disbint.offered_amt,
1651 disbint.accepted_amt,
1652 disbint.fee_1_amt,
1653 disbint.fee_2_amt,
1654 disbint.disb_date,
1655 trim(disbint.trans_type_code) trans_type_code,
1656 disbint.elig_status_date,
1657 disbint.affirm_flag,
1658 disbint.int_rebate_amt,
1659 disbint.force_disb_flag,
1660 disbint.min_credit_pts_num,
1661 disbint.disb_exp_date,
1662 disbint.verf_enfr_date,
1663 disbint.planned_credit_flag,
1664 disbint.fee_paid_1_amt,
1665 disbint.fee_paid_2_amt,
1666 disbint.created_by,
1667 disbint.creation_date,
1668 disbint.last_updated_by,
1669 disbint.last_update_date,
1670 disbint.last_update_login,
1671 trim(fee_class_code) fee_class_code,
1672 authorization_date,
1673 TRIM(attendance_type_code) attendance_type_code,
1674 disbint.base_attendance_type_code -- new col added as part of FA131 COD updates build.
1675 FROM igf_aw_li_disb_ints disbint
1676 WHERE ci_alternate_code = li_awd_rec.ci_alternate_code
1677 AND person_number = li_awd_rec.person_number
1678 AND award_number_txt = li_awd_rec.award_number_txt
1679 ORDER BY disbursement_num;
1680
1681 CURSOR c_uniq_fee_class IS
1682 SELECT fee_class_code
1683 FROM igf_aw_li_disb_ints
1684 WHERE person_number = li_awd_rec.person_number
1685 AND award_number_txt = li_awd_rec.award_number_txt
1686 GROUP BY fee_class_code
1687 HAVING COUNT(fee_class_code) > 1;
1688 c_uniq_fee_class_rec c_uniq_fee_class%ROWTYPE;
1689
1690 CURSOR cur_legacy_disb_total IS
1691 SELECT SUM(offered_amt) total_offered_amt,
1692 SUM(accepted_amt) total_accepted_amt,
1693 COUNT(disbursement_num) number_of_disb,
1694 NVL(MAX(disbursement_num),0) max_disb_number,
1695 NVL(MIN(disbursement_num),0) min_disb_number
1696 FROM igf_aw_li_disb_ints
1697 WHERE ci_alternate_code = li_awd_rec.ci_alternate_code
1698 AND person_number = li_awd_rec.person_number
1699 AND award_number_txt = li_awd_rec.award_number_txt;
1700
1701 cur_legacy_disb_total_rec cur_legacy_disb_total%ROWTYPE;
1702
1703 CURSOR c_match_disb_term (p_cal_type igf_aw_fund_tp_all.tp_cal_type%TYPE,
1704 p_sequence_number igf_aw_fund_tp_all.tp_sequence_number%TYPE) IS
1705 SELECT fund_id, tp_cal_type, tp_sequence_number
1706 FROM igf_aw_fund_tp_all
1707 WHERE fund_id = g_fund_id
1708 AND tp_cal_type = p_cal_type
1709 AND tp_sequence_number = p_sequence_number;
1710 c_match_disb_term_rec c_match_disb_term%ROWTYPE;
1711
1712 CURSOR c_max_year_spnsr IS
1713 SELECT fmast.max_yearly_amt
1714 FROM igf_aw_fund_mast_all fmast,
1715 igf_aw_fund_cat_all fcat
1716 WHERE fcat.fund_code = fmast.fund_code
1717 AND fcat.fed_fund_code = g_fed_fund_code
1718 AND fmast.fund_id = g_fund_id
1719 AND fmast.discontinue_fund = 'N';
1720 c_max_year_spnsr_rec c_max_year_spnsr%ROWTYPE;
1721
1722 CURSOR c_get_sp_fee_class(p_fee_class_code igf_aw_li_disb_ints.fee_class_code%TYPE) IS
1723 SELECT fee_cls_id
1724 FROM igf_sp_fc_all
1725 WHERE fund_id = g_fund_id
1726 AND fee_class = p_fee_class_code;
1727 c_get_sp_fee_class_rec c_get_sp_fee_class%ROWTYPE;
1728
1729 CURSOR c_get_dact_fws(p_disbursement_num igf_aw_li_disb_ints.disbursement_num%TYPE) IS
1730 SELECT disb_activity_num
1731 FROM igf_aw_li_dact_ints
1732 WHERE ci_alternate_code = li_awd_rec.ci_alternate_code
1733 AND person_number = li_awd_rec.person_number
1734 AND award_number_txt = li_awd_rec.award_number_txt
1735 AND disbursement_num = p_disbursement_num;
1736 c_get_dact_fws_rec c_get_dact_fws%ROWTYPE;
1737
1738 CURSOR c_val_lookup_code(p_lookup_type igs_lookups_view.lookup_type%TYPE,
1739 p_lookup_code igs_lookups_view.lookup_code%TYPE) IS
1740 SELECT meaning FROM igs_lookups_view WHERE lookup_type = p_lookup_type AND
1741 lookup_code = p_lookup_code and enabled_flag = 'Y';
1742 c_val_lookup_code_rec c_val_lookup_code%ROWTYPE;
1743
1744 CURSOR c_get_disb_count IS
1745 SELECT count(*)
1746 FROM igf_aw_li_disb_ints disbint
1747 WHERE disbint.ci_alternate_code = li_awd_rec.ci_alternate_code
1748 AND disbint.person_number = li_awd_rec.person_number
1749 AND disbint.award_number_txt = li_awd_rec.award_number_txt
1750 ORDER BY disbursement_num;
1751 l_disb_rec_count PLS_INTEGER;
1752
1753 CURSOR c_get_uniq_ld_code IS
1754 SELECT count(distinct(ld_alternate_code))
1755 FROM igf_aw_li_disb_ints disbint
1756 WHERE ci_alternate_code = li_awd_rec.ci_alternate_code
1757 AND person_number = li_awd_rec.person_number
1758 AND award_number_txt = li_awd_rec.award_number_txt;
1759 l_uniq_ld_code PLS_INTEGER;
1760
1761 CURSOR cur_check_atd( cp_atd_code VARCHAR2) IS
1762 SELECT attendance_type
1763 FROM igs_en_atd_type
1764 WHERE attendance_type = cp_atd_code;
1765
1766 check_atd_rec cur_check_atd%ROWTYPE;
1767
1768 CURSOR cur_legacy_disb_fws_total IS
1769 SELECT COUNT (DISTINCT ld_alternate_code) terms,
1770 COUNT (tp_alternate_code) tp
1771 FROM igf_aw_li_disb_ints
1772 WHERE ci_alternate_code = li_awd_rec.ci_alternate_code
1773 AND person_number = li_awd_rec.person_number
1774 AND award_number_txt = li_awd_rec.award_number_txt;
1775
1776 cur_legacy_disb_total_fws_rec cur_legacy_disb_fws_total%ROWTYPE;
1777
1778 l_ctr PLS_INTEGER;
1779 l_ret_val BOOLEAN;
1780 l_var_cal_type igs_ca_inst.cal_type%TYPE;
1781 l_var_seq_number igs_ca_inst.sequence_number%TYPE;
1782 l_return_status_dh VARCHAR2(1);
1783 l_return_status_da VARCHAR2(1);
1784 l_disb_rec_ins_status VARCHAR2(1);
1785 l_net_amount igf_aw_awd_disb_all.disb_net_amt%TYPE;
1786
1787 l_padding_string VARCHAR2(100);
1788
1789 PROCEDURE create_disbursment_record(li_awd_rec IN igf_aw_li_awd_ints%ROWTYPE,
1790 cur_legacy_disb_int_rec IN igf_aw_li_disb_ints%ROWTYPE,
1791 l_disb_rec_ins_status OUT NOCOPY VARCHAR2
1792 ) IS
1793 /***************************************************************
1794 Created By : nsidana
1795 Date Created By : 11/28/2003
1796 Purpose : Creates disbursements records in the system.
1797 Known Limitations,Enhancements or Remarks
1798 Change History :
1799 Who When What
1800 nsidana 11/28/2003 FA131 COD updates build. Added validations on two new feilds
1801 added to the legacy awards table and 1 feild in the legacy award
1802 disbursements table.
1803 --veramach 1-NOV-2003 FA 125 Multiple Distr Methods
1804 -- Changed calll to igf_aw_awd_disb_pkg.update_row to reflect the addition of attendance_type_code
1805 --rasahoo 25/Aug/2003 #3101894 Called procedure send_work_auth with parameter 'LEGACY'
1806 ***************************************************************/
1807 l_rowid VARCHAR2(25);
1808
1809 CURSOR c_get_cal_typ_seq_num(p_alternate_code igs_ca_inst.alternate_code%TYPE) IS
1810 SELECT cal_type, sequence_number
1811 FROM igs_ca_inst
1812 WHERE alternate_code = p_alternate_code;
1813
1814 CURSOR cur_awd_disb( cp_row_id ROWID) IS
1815 SELECT rowid row_id, disb.*
1816 FROM igf_aw_awd_disb_all disb
1817 WHERE rowid = cp_row_id;
1818
1819 CURSOR c_get_fed_fund (cp_fund_code igf_aw_fund_cat_all.fund_code%TYPE) IS
1820 SELECT fed_fund_code
1821 FROM igf_aw_fund_cat_all
1822 WHERE fund_code = cp_fund_code;
1823
1824 l_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE;
1825 lc_awd_disb cur_awd_disb%ROWTYPE;
1826 c_tp_calseq_rec c_get_cal_typ_seq_num%ROWTYPE;
1827 c_ld_calseq_rec c_get_cal_typ_seq_num%ROWTYPE;
1828 l_warning VARCHAR2(200);
1829 l_trans_type VARCHAR2(1);
1830 l_cal_type igs_ca_inst_all.cal_type%TYPE;
1831 ln_sequence_number igs_ca_inst_all.sequence_number%TYPE;
1832 ln_base_id igf_ap_fa_base_rec_all.base_id%TYPE := NULL;
1833 l_hol_rel_ind VARCHAR2(30) := NULL;
1834
1835
1836 BEGIN
1837 l_rowid := NULL;
1838
1839 -- obtain the cal type sequence number for teaching period
1840 c_tp_calseq_rec := NULL;
1841 OPEN c_get_cal_typ_seq_num(cur_legacy_disb_int_rec.tp_alternate_code);
1842 FETCH c_get_cal_typ_seq_num INTO c_tp_calseq_rec;
1843 CLOSE c_get_cal_typ_seq_num;
1844
1845 -- obtain the cal type, sequence number for load calendar
1846 OPEN c_get_cal_typ_seq_num(cur_legacy_disb_int_rec.ld_alternate_code);
1847 FETCH c_get_cal_typ_seq_num INTO c_ld_calseq_rec;
1848 CLOSE c_get_cal_typ_seq_num;
1849
1850 IF li_awd_rec.award_status_code = 'CANCELLED' THEN
1851
1852 l_net_amount := 0 ;
1853 l_trans_type := 'C' ;
1854 ELSE
1855
1856 l_trans_type := cur_legacy_disb_int_rec.trans_type_code ;
1857 END IF;
1858
1859 -- get the hold_rel_ind flag if fed_fund_code in ('ALT', 'FLS', 'FLP', 'FLU')
1860 l_fed_fund_code := NULL;
1861
1862 -- cursor to fetch the federal fund code
1863 OPEN c_get_fed_fund(li_awd_rec.fund_code);
1864 FETCH c_get_fed_fund INTO l_fed_fund_code;
1865 CLOSE c_get_fed_fund;
1866
1867 IF l_fed_fund_code IN ('ALT', 'FLS', 'FLP', 'FLU') THEN
1868 -- get the cal type, sequence number for the award year
1869 OPEN c_get_cal_typ_seq_num(li_awd_rec.ci_alternate_code);
1870 FETCH c_get_cal_typ_seq_num INTO l_cal_type,ln_sequence_number;
1871 CLOSE c_get_cal_typ_seq_num;
1872
1873 ln_base_id := get_base_id_from_per_num(li_awd_rec.person_number, l_cal_type, ln_sequence_number);
1874
1875 l_hol_rel_ind := igf_sl_award.get_cl_hold_rel_ind(
1876 p_fed_fund_code => l_fed_fund_code,
1877 p_ci_cal_type => l_cal_type,
1878 p_ci_seq_num => ln_sequence_number,
1879 p_base_id => ln_base_id,
1880 p_alt_rel_code => igf_sl_award.get_alt_rel_code(li_awd_rec.fund_code)
1881 );
1882
1883 ELSE
1884 l_hol_rel_ind := 'FALSE';
1885 END IF;
1886
1887
1888 igf_aw_awd_disb_pkg.insert_row(
1889 x_rowid => l_rowid,
1890 x_award_id => g_award_id,
1891 x_disb_num => cur_legacy_disb_int_rec.disbursement_num,
1892 x_tp_cal_type => c_tp_calseq_rec.cal_type,
1893 x_tp_sequence_number=> c_tp_calseq_rec.sequence_number,
1894 x_disb_gross_amt => cur_legacy_disb_int_rec.offered_amt,
1895 x_fee_1 => cur_legacy_disb_int_rec.fee_1_amt,
1896 x_fee_2 => cur_legacy_disb_int_rec.fee_2_amt,
1897 x_disb_net_amt => l_net_amount,
1898 x_disb_date => cur_legacy_disb_int_rec.disb_date,
1899 x_trans_type => l_trans_type,
1900 x_elig_status => NULL,
1901 x_elig_status_date => cur_legacy_disb_int_rec.elig_status_date,
1902 x_affirm_flag => cur_legacy_disb_int_rec.affirm_flag,
1903 x_hold_rel_ind => l_hol_rel_ind,
1904 x_manual_hold_ind => NULL,
1905 x_disb_status => NULL,
1906 x_disb_status_date => NULL,
1907 x_late_disb_ind => NULL,
1908 x_fund_dist_mthd => NULL,
1909 x_prev_reported_ind => NULL,
1910 x_fund_release_date => NULL,
1911 x_fund_status => NULL,
1912 x_fund_status_date => NULL,
1913 x_fee_paid_1 => cur_legacy_disb_int_rec.fee_paid_1_amt,
1914 x_fee_paid_2 => cur_legacy_disb_int_rec.fee_paid_2_amt,
1915 x_cheque_number => NULL,
1916 x_ld_cal_type => c_ld_calseq_rec.cal_type,
1917 x_ld_sequence_number=> c_ld_calseq_rec.sequence_number,
1918 x_disb_accepted_amt => cur_legacy_disb_int_rec.accepted_amt,
1919 x_disb_paid_amt => NULL,
1920 x_rvsn_id => NULL,
1921 x_int_rebate_amt => cur_legacy_disb_int_rec.int_rebate_amt,
1922 x_force_disb => cur_legacy_disb_int_rec.force_disb_flag,
1923 x_min_credit_pts => cur_legacy_disb_int_rec.min_credit_pts_num,
1924 x_disb_exp_dt => cur_legacy_disb_int_rec.disb_exp_date,
1925 x_verf_enfr_dt => cur_legacy_disb_int_rec.verf_enfr_date,
1926 x_fee_class => cur_legacy_disb_int_rec.fee_class_code,
1927 x_show_on_bill => cur_legacy_disb_int_rec.planned_credit_flag,
1928 x_attendance_type_code => cur_legacy_disb_int_rec.attendance_type_code,
1929 x_base_attendance_type_code => cur_legacy_disb_int_rec.base_attendance_type_code,
1930 x_payment_prd_st_date => NULL,
1931 x_change_type_code => NULL,
1932 x_fund_return_mthd_code => NULL,
1933 x_direct_to_borr_flag => 'N'
1934 );
1935
1936 IF(l_rowid IS NOT NULL)THEN
1937 l_disb_rec_ins_status := 'S';
1938 ELSE
1939 l_disb_rec_ins_status := 'E';
1940 RETURN;
1941 END IF;
1942
1943 EXCEPTION WHEN OTHERS THEN
1944 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
1945 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'igf.plsql.igf_aw_li_import.create_disbursment_record.exception',SQLERRM);
1946 END IF;
1947 FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM);
1948 l_disb_rec_ins_status := 'E';
1949 END create_disbursment_record;
1950
1951 BEGIN -- validate_disburs_int_rec
1952
1953 p_return_status := 'S';
1954 l_padding_string := RPAD(' ',3,' ')||'-- ';
1955 -- open the cusrsor cur_legacy_disb_total and obtain the total values
1956 cur_legacy_disb_total_rec := NULL;
1957 OPEN cur_legacy_disb_total; FETCH cur_legacy_disb_total INTO cur_legacy_disb_total_rec; CLOSE cur_legacy_disb_total;
1958 g_processing_string := l_padding_string || igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','PROCESSING') || ' '||
1959 igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','DISBURSMNT');
1960 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1961 FND_FILE.PUT_LINE(FND_FILE.LOG,g_processing_string);
1962
1963 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1964 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_li_import.validate_disburs_int_rec.debug','no of disb'|| cur_legacy_disb_total_rec.number_of_disb || 'max no = ' ||cur_legacy_disb_total_rec.max_disb_number);
1965 END IF;
1966 -- if total number of disbursement = 0 then error
1967 IF(cur_legacy_disb_total_rec.number_of_disb = 0)THEN
1968 p_return_status := 'E';
1969 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_DISB_NOT_PRSNT');
1970 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
1971 RETURN;
1972 ELSIF(cur_legacy_disb_total_rec.max_disb_number > 99)THEN
1973 p_return_status := 'E';
1974 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_DISB_NUM_INVALID');
1975 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
1976 RETURN;
1977 END IF;
1978
1979 -- validate disbursement numbers for the award. the numbers must be sequential starting from 1
1980 l_ctr:=1;
1981 FOR cur_legacy_disb_int_rec IN cur_legacy_disb_int LOOP
1982 IF(l_ctr <> cur_legacy_disb_int_rec.disbursement_num) THEN
1983 p_return_status := 'E';
1984 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_DISB_NUM_ST_1_INR_1');
1985 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
1986 RETURN;
1987 END IF;
1988 l_ctr:=l_ctr+1;
1989 END LOOP;
1990
1991 -- sum of all offered amounts for all disb at the context awd = total offered amt.
1992 IF( (li_awd_rec.offered_amt IS NOT NULL AND cur_legacy_disb_total_rec.total_offered_amt IS NULL) OR
1993 (li_awd_rec.offered_amt IS NOT NULL AND li_awd_rec.offered_amt <> cur_legacy_disb_total_rec.total_offered_amt )
1994 ) THEN
1995 p_return_status := 'E';
1996 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_SUM_OFRD_AMT_NOT_EQL');
1997 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
1998 RETURN;
1999 END IF;
2000
2001
2002 -- If accepted amt is present at award and not present at disb then error
2003 -- sum of all accepted amounts for all dis at the context awd = total accepted amt
2004 IF( ( li_awd_rec.accepted_amt IS NOT NULL AND cur_legacy_disb_total_rec.total_accepted_amt IS NULL ) OR
2005 ( li_awd_rec.accepted_amt IS NOT NULL AND li_awd_rec.accepted_amt <> cur_legacy_disb_total_rec.total_accepted_amt)
2006 ) THEN
2007 p_return_status := 'E';
2008 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_SUM_ACPT_AMT_NOT_EQL');
2009 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2010 RETURN;
2011 END IF;
2012
2013 IF(g_fed_fund_code = 'SPNSR')THEN
2014 -- unique fee class validation for sponsorship awards
2015 c_uniq_fee_class_rec := NULL;
2016 OPEN c_uniq_fee_class; FETCH c_uniq_fee_class INTO c_uniq_fee_class_rec; CLOSE c_uniq_fee_class;
2017 IF(c_uniq_fee_class_rec.fee_class_code IS NOT NULL)THEN
2018 p_return_status := 'E';
2019 FND_MESSAGE.SET_NAME('IGF','IGF_SP_LI_DUP_FC_FND');
2020 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2021 END IF;
2022 -- Sponsorship Awards should be awarded under one Load Calendar for the given Award. Check if the Student has any
2023 -- sponsorhsips not in the same load calendar for a sponsorship Award in the disbursement interface.
2024 -- table. If the Student does have more than one LD then error out
2025 OPEN c_get_uniq_ld_code; FETCH c_get_uniq_ld_code INTO l_uniq_ld_code; CLOSE c_get_uniq_ld_code;
2026 IF(l_uniq_ld_code > 1)THEN
2027 p_return_status := 'E';
2028 FND_MESSAGE.SET_NAME('IGF','IGF_SP_LI_LD_CAL_NOT_SAME');
2029 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2030 END IF;
2031 END IF;
2032
2033 IF g_fed_fund_code = 'FWS' THEN
2034 --check if the award has more than 1 disbursement per term
2035 --if yes, error OUT
2036 OPEN cur_legacy_disb_fws_total;
2037 FETCH cur_legacy_disb_fws_total INTO cur_legacy_disb_total_fws_rec;
2038 CLOSE cur_legacy_disb_fws_total;
2039 IF cur_legacy_disb_total_fws_rec.terms <> cur_legacy_disb_total_fws_rec.tp THEN
2040 p_return_status := 'E';
2041 FND_MESSAGE.SET_NAME('IGF','IGF_SE_MAX_TP_SETUP');
2042 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2043 RETURN;
2044 END IF;
2045 END IF;
2046
2047 l_ctr := 0; -- this initialization is necessary for checking only one disb record for sponsorships
2048 FOR cur_legacy_disb_int_rec IN cur_legacy_disb_int LOOP
2049 l_ctr := l_ctr + 1;
2050 -- validate the term calendar
2051 g_processing_string := RPAD(' ',3,' ')||'-- '|| igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','PROCESSING') || ' '||
2052 igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','DISBURSEMENT_NUMBER');
2053 FND_FILE.PUT_LINE(FND_FILE.LOG,g_processing_string||': '||cur_legacy_disb_int_rec.disbursement_num);
2054 l_ret_val := igf_ap_gen.validate_cal_inst('LOAD',li_awd_rec.ci_alternate_code,
2055 cur_legacy_disb_int_rec.ld_alternate_code,
2056 l_var_cal_type,
2057 l_var_seq_number);
2058
2059 IF(FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level)THEN
2060 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_aw_li_import.validate_disburs_int_rec.debug',
2061 'LOAD validation ci_alternate_code'||li_awd_rec.ci_alternate_code||'ld_alternate_code:'||
2062 cur_legacy_disb_int_rec.ld_alternate_code||'l_var_cal_type'||l_var_cal_type||'l_var_seq_number'||l_var_seq_number
2063 );
2064 END IF;
2065
2066 IF(l_ret_val = FALSE AND l_var_cal_type IS NULL)THEN
2067 p_return_status := 'E';
2068 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_TERM_NOT_FND');
2069 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2070 END IF;
2071
2072 IF(l_ret_val = FALSE AND l_var_cal_type IS NOT NULL)THEN
2073 p_return_status := 'E';
2074 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_TERM_NOT_CHLD_AWD_YR');
2075 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2076 END IF;
2077
2078 -- error out if the award status is not accepted and accepted amount exists at the disbursement level
2079 IF ( li_awd_rec.award_status_code IN ('OFFERED','CANCELLED','DECLINED') AND
2080 cur_legacy_disb_int_rec.accepted_amt < 0
2081 )THEN
2082 p_return_status := 'E';
2083 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_ACPT_AMT_NEG');
2084 FND_MESSAGE.SET_TOKEN('AWD_STATUS', li_awd_rec.award_status_code);
2085 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
2086 END IF;
2087
2088 -- for cancelled awards acpt amt should be zero or null
2089 IF ( li_awd_rec.award_status_code ='CANCELLED' AND
2090 ( cur_legacy_disb_int_rec.accepted_amt IS NULL OR cur_legacy_disb_int_rec.accepted_amt <> 0 )
2091 ) THEN
2092 p_return_status := 'E';
2093 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_DB_ACPT_AMT_AWD_CNCL');
2094 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
2095 END IF;
2096
2097 -- federal work study validations introduced in FACR117.
2098 IF(g_fed_fund_code = 'FWS')THEN
2099 -- Work Study Funds should not have Legacy Disbursement Activity Records
2100 c_get_dact_fws_rec := NULL;
2101 OPEN c_get_dact_fws(cur_legacy_disb_int_rec.disbursement_num); FETCH c_get_dact_fws INTO c_get_dact_fws_rec;
2102 CLOSE c_get_dact_fws;
2103 IF(c_get_dact_fws_rec.disb_activity_num IS NOT NULL)THEN
2104 p_return_status := 'E';
2105 FND_MESSAGE.SET_NAME('IGF','IGF_SE_LI_DISB_ADJ_PRSNT');
2106 FND_MESSAGE.SET_TOKEN('FUND',li_awd_rec.fund_code);
2107 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2108 END IF;
2109 END IF;
2110
2111 -- sponsorship validations, introduced in FACR117 for sep 2003 release
2112 IF(g_fed_fund_code <> 'SPNSR')THEN
2113 -- fee class code should be null for non sponsorship awards. introduced in FACR117
2114 IF(cur_legacy_disb_int_rec.fee_class_code IS NOT NULL)THEN
2115 p_return_status := 'E';
2116 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_DISB_COL_BLNK');
2117 FND_MESSAGE.SET_TOKEN('COLUMN_NAME','FEE_CLASS_CODE');
2118 FND_MESSAGE.SET_TOKEN('FUND_CODE',li_awd_rec.fund_code);
2119 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2120 END IF;
2121 ELSIF(g_fed_fund_code = 'SPNSR')THEN
2122 -- When the sponsorship amount is present, only one disbursement record exist without any fee class code
2123 -- also for second disbursement onwards, fee class codes should be present.
2124 -- Secondly, when the spoosorship amount is not present, error if fee class code is blank.
2125 c_max_year_spnsr_rec := NULL; l_disb_rec_count := NULL;
2126 OPEN c_max_year_spnsr; FETCH c_max_year_spnsr INTO c_max_year_spnsr_rec; CLOSE c_max_year_spnsr;
2127 OPEN c_get_disb_count; FETCH c_get_disb_count INTO l_disb_rec_count; CLOSE c_get_disb_count;
2128 IF(c_max_year_spnsr_rec.max_yearly_amt IS NOT NULL AND cur_legacy_disb_int_rec.fee_class_code IS NULL AND l_disb_rec_count > 1)THEN
2129 p_return_status := 'E';
2130 FND_MESSAGE.SET_NAME('IGF','IGF_SP_LI_FC_NOT_PRSNT');
2131 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2132 ELSIF(c_max_year_spnsr_rec.max_yearly_amt IS NULL AND cur_legacy_disb_int_rec.fee_class_code IS NULL AND l_disb_rec_count >= 1)THEN
2133 p_return_status := 'E';
2134 FND_MESSAGE.SET_NAME('IGF','IGF_SP_LI_GT1_DB_FC_NULL');
2135 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2136 END IF;
2137 -- check if the fee class code is valid value for configured lookup. Test this only if the value is present in the
2138 -- fee class code. validation introduced in FACR117
2139 IF(cur_legacy_disb_int_rec.fee_class_code IS NOT NULL)THEN
2140 c_val_lookup_code_rec := NULL;
2141 OPEN c_val_lookup_code('FEE_CLASS',cur_legacy_disb_int_rec.fee_class_code); FETCH c_val_lookup_code INTO c_val_lookup_code_rec;
2142 CLOSE c_val_lookup_code;
2143 IF(c_val_lookup_code_rec.meaning IS NULL)THEN
2144 p_return_status := 'E';
2145 FND_MESSAGE.SET_NAME('IGF','IGF_AP_INV_FLD_VAL');
2146 FND_MESSAGE.SET_TOKEN('FIELD','FEE_CLASS_CODE');
2147 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2148 END IF;
2149 END IF;
2150
2151 -- validate if the fee class is present in the sponsor definition. if not present then raise a warning
2152 c_get_sp_fee_class_rec := NULL;
2153 IF(cur_legacy_disb_int_rec.fee_class_code IS NOT NULL)THEN
2154 OPEN c_get_sp_fee_class(cur_legacy_disb_int_rec.fee_class_code); FETCH c_get_sp_fee_class INTO c_get_sp_fee_class_rec;
2155 CLOSE c_get_sp_fee_class;
2156 IF(c_get_sp_fee_class_rec.fee_cls_id IS NULL)THEN
2157 IF(p_return_status <> 'E')THEN
2158 p_return_status := 'W';
2159 END IF;
2160 FND_MESSAGE.SET_NAME('IGF','IGF_SP_LI_FC_NOT_CHILD');
2161 FND_MESSAGE.SET_TOKEN('FEE_CLASS',cur_legacy_disb_int_rec.fee_class_code);
2162 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2163 END IF;
2164 END IF;
2165 END IF;
2166
2167 -- disbursement term matching validations for open award year only
2168 -- Do this validation only for SPNSR
2169 IF(g_award_year_status_code = 'O')THEN
2170 IF g_fed_fund_code = 'SPNSR' THEN
2171 c_match_disb_term_rec := NULL;
2172 OPEN c_match_disb_term(l_var_cal_type, l_var_seq_number); FETCH c_match_disb_term INTO c_match_disb_term_rec;
2173 CLOSE c_match_disb_term;
2174 IF(c_match_disb_term_rec.fund_id IS NULL) THEN
2175 IF(p_return_status <> 'E')THEN
2176 p_return_status := 'W';
2177 END IF;
2178 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_TERM_DISTRIBUTION');
2179 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2180 END IF;
2181 END IF;
2182
2183 -- validations as per review comments doc # 17 in the s1 version of the TD
2184 IF(g_fed_fund_code in ('DLP','FLP','GPLUSFL') AND cur_legacy_disb_total_rec.max_disb_number > 4) THEN
2185 p_return_status := 'E';
2186 FND_MESSAGE.SET_NAME('IGF','IGF_AW_PLUS_DISB');
2187 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2188 ELSIF(g_fed_fund_code in ('DLS','FLS','DLU','FLU') AND cur_legacy_disb_total_rec.max_disb_number > 20) THEN
2189 p_return_status := 'E';
2190 FND_MESSAGE.SET_NAME('IGF','IGF_AW_SUNS_DISB');
2191 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2192 ELSIF(g_fed_fund_code in ('PELL') AND cur_legacy_disb_total_rec.max_disb_number > 90) THEN
2193 p_return_status := 'E';
2194 FND_MESSAGE.SET_NAME('IGF','IGF_AW_PELL_DISB');
2195 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2196 END IF;
2197 END IF;
2198
2199 -- validate transacation type. valid values are ACTUAL(A), PLANNED(P), CANCELLED(C)
2200 IF(igf_ap_gen.get_aw_lookup_meaning('IGF_DB_TRANS_TYPE',cur_legacy_disb_int_rec.trans_type_code,g_sys_award_year)IS NULL)THEN
2201 p_return_status := 'E';
2202 FND_MESSAGE.SET_NAME('IGF','IGF_AP_INV_FLD_VAL');
2203 FND_MESSAGE.SET_TOKEN('FIELD','TRANS_TYPE_CODE');
2204 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2205 END IF;
2206 --
2207 -- External Awards Import can import only Planned Disbursements
2208 --
2209 IF g_entry_point = 'EXTERNAL' AND g_fed_fund_code = 'EXT' AND cur_legacy_disb_int_rec.trans_type_code <> 'P' THEN
2210 p_return_status :='E';
2211 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_TRANS_TYPE_FAIL');
2212 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2213 END IF;
2214
2215 --
2216 -- Check if the Attendance Type Code is setup in OSS or not
2217 --
2218 IF cur_legacy_disb_int_rec.attendance_type_code IS NOT NULL THEN
2219 OPEN cur_check_atd(cur_legacy_disb_int_rec.attendance_type_code);
2220 FETCH cur_check_atd INTO check_atd_rec;
2221 IF cur_check_atd%NOTFOUND THEN
2222 p_return_status :='E';
2223 FND_MESSAGE.SET_NAME('IGF','IGF_AP_INV_FLD_VAL');
2224 FND_MESSAGE.SET_TOKEN('FIELD','ATTENDANCE_TYPE_CODE');
2225 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2226 END IF;
2227 CLOSE cur_check_atd;
2228 END IF;
2229
2230 IF (
2231 g_fed_fund_code = 'PELL' AND
2232 igf_ap_gen.get_aw_lookup_meaning('IGF_GR_RFMS_ENROL_STAT',cur_legacy_disb_int_rec.base_attendance_type_code,g_sys_award_year) IS NULL)
2233 OR (
2234 g_fed_fund_code <> 'PELL' AND
2235 cur_legacy_disb_int_rec.base_attendance_type_code IS NOT NULL
2236 )
2237 THEN
2238 p_return_status :='E';
2239 FND_MESSAGE.SET_NAME('IGF','IGF_AP_INV_FLD_VAL');
2240 fnd_message.set_token('FIELD','BASE_ATTENDANCE_TYPE_CODE');
2241 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2242 END IF;
2243
2244 --
2245 -- Either Attendance Type Code, or Credit Points can be
2246 -- entered, not both
2247 --
2248 IF cur_legacy_disb_int_rec.attendance_type_code IS NOT NULL AND
2249 cur_legacy_disb_int_rec.min_credit_pts_num IS NOT NULL THEN
2250 p_return_status :='E';
2251 FND_MESSAGE.SET_NAME('IGF','IGF_AW_CRP_ATT_FAIL');
2252 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2253 END IF;
2254
2255 -- validate teaching calendar
2256 l_ret_val := FALSE; l_var_cal_type:=NULL; l_var_seq_number:=NULL;
2257 l_ret_val := igf_ap_gen.validate_cal_inst('TEACHING',cur_legacy_disb_int_rec.ld_alternate_code,
2258 cur_legacy_disb_int_rec.tp_alternate_code,
2259 l_var_cal_type,
2260 l_var_seq_number);
2261 IF(l_ret_val = FALSE AND l_var_cal_type IS NULL)THEN
2262 p_return_status := 'E';
2263 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_TEACH_CAL_NOT_FND');
2264 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2265 END IF;
2266 IF(l_ret_val = FALSE AND l_var_cal_type IS NULL)THEN
2267 p_return_status := 'E';
2268 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_TEACH_NOT_CHLD_LOAD');
2269 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2270 END IF;
2271
2272 -- validate offered amount. offered amount > 0 always
2273 IF(cur_legacy_disb_int_rec.offered_amt <= 0)THEN
2274 p_return_status := 'E';
2275 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_OFRD_AMT_GT_0');
2276 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2277 END IF;
2278
2279 -- validate eligibility status date. ELIG_STATUS_DATE < sysdate always
2280 IF(trunc(cur_legacy_disb_int_rec.elig_status_date) > trunc(sysdate))THEN
2281 p_return_status := 'E';
2282 FND_MESSAGE.SET_NAME('IGS','IGS_AD_DATE_SYSDATE');
2283 FND_MESSAGE.SET_TOKEN('NAME','ELIG_STATUS_DATE');
2284 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2285 END IF;
2286
2287 IF(li_awd_rec.award_status_code = 'ACCEPTED')THEN
2288 -- accepted amount should be present at disb level if present at award level. if null
2289 -- at disb level, log error message and continue with validations
2290 IF(li_awd_rec.accepted_amt IS NOT NULL) THEN
2291 IF(cur_legacy_disb_int_rec.accepted_amt IS NULL) THEN
2292 p_return_status := 'E';
2293 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_ACPT_REQ_AWD_ACPT');
2294 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2295 -- accepted amount should be > 0.
2296 ELSIF(cur_legacy_disb_int_rec.accepted_amt <= 0) THEN
2297 p_return_status := 'E';
2298 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_ACPT_AMT_GT_0');
2299 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2300 END IF;
2301 IF(cur_legacy_disb_int_rec.trans_type_code = 'C' AND cur_legacy_disb_int_rec.accepted_amt <> 0) THEN
2302 p_return_status := 'E';
2303 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_ACPT_AMT_EQ_0_TT_C');
2304 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2305 END IF;
2306 END IF;
2307 END IF; -- end award status is ACCEPTED
2308
2309 -- Interest Rebate Amount and Affirmation Flag should be NULL for non Direct Loans
2310 IF(g_fed_fund_code NOT IN ('DLP','DLS','DLU'))THEN
2311 IF(cur_legacy_disb_int_rec.int_rebate_amt IS NOT NULL)THEN
2312 p_return_status := 'E';
2313 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_DISB_COL_BLNK');
2314 FND_MESSAGE.SET_TOKEN('COLUMN_NAME','INT_REBATE_AMT');
2315 FND_MESSAGE.SET_TOKEN('FUND_CODE',li_awd_rec.fund_code);
2316 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2317 END IF;
2318 IF(cur_legacy_disb_int_rec.affirm_flag IS NOT NULL)THEN
2319 p_return_status := 'E';
2320 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_DISB_COL_BLNK');
2321 FND_MESSAGE.SET_TOKEN('COLUMN_NAME','AFFIRM_FLAG');
2322 FND_MESSAGE.SET_TOKEN('FUND_CODE',li_awd_rec.fund_code);
2323 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2324 END IF;
2325 END IF;
2326
2327 -- Origination Fee should be null for all loans except for Direct, FFELP and Alternative
2328 IF(g_fed_fund_code NOT IN ('DLP','DLS','DLU','FLS','FLP','FLU','ALT','GPLUSFL') AND cur_legacy_disb_int_rec.fee_1_amt IS NOT NULL)THEN
2329 p_return_status := 'E';
2330 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_DISB_COL_BLNK');
2331 FND_MESSAGE.SET_TOKEN('COLUMN_NAME','FEE_1_AMT');
2332 FND_MESSAGE.SET_TOKEN('FUND_CODE',li_awd_rec.fund_code);
2333 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2334 END IF;
2335
2336 -- validate if the Origination Fee is correct for Direct, FFELP and Alternative Loan
2337 IF(g_fed_fund_code IN ('DLP','DLS','DLU','FLS','FLP','FLU','ALT','GPLUSFL'))THEN
2338 IF(cur_legacy_disb_int_rec.fee_1_amt IS NOT NULL AND cur_legacy_disb_int_rec.fee_1_amt < 0)THEN
2339 p_return_status := 'E';
2340 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_DISB_COL_REQ');
2341 FND_MESSAGE.SET_TOKEN('COLUMN_NAME','FEE_1_AMT');
2342 FND_MESSAGE.SET_TOKEN('FUND_CODE',li_awd_rec.fund_code);
2343 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2344 END IF;
2345 END IF;
2346
2347 -- Guarantee Fee, Guarantee Fee Paid, Origination Fee Paid should be null for non FFELP and Alternative Loans
2348 IF(g_fed_fund_code NOT IN('FLS','FLP','FLU','ALT','GPLUSFL'))THEN
2349 IF(cur_legacy_disb_int_rec.fee_2_amt IS NOT NULL)THEN
2350 p_return_status := 'E';
2351 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_DISB_COL_BLNK');
2352 FND_MESSAGE.SET_TOKEN('COLUMN_NAME','FEE_2_AMT');
2353 FND_MESSAGE.SET_TOKEN('FUND_CODE',li_awd_rec.fund_code);
2354 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2355 END IF;
2356 IF(cur_legacy_disb_int_rec.fee_paid_1_amt IS NOT NULL)THEN
2357 p_return_status := 'E';
2358 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_DISB_COL_BLNK');
2359 FND_MESSAGE.SET_TOKEN('COLUMN_NAME','FEE_PAID_1_AMT');
2360 FND_MESSAGE.SET_TOKEN('FUND_CODE',li_awd_rec.fund_code);
2361 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2362 END IF;
2363 IF(cur_legacy_disb_int_rec.fee_paid_2_amt IS NOT NULL)THEN
2364 p_return_status := 'E';
2365 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_DISB_COL_BLNK');
2366 FND_MESSAGE.SET_TOKEN('COLUMN_NAME','FEE_PAID_2_AMT');
2367 FND_MESSAGE.SET_TOKEN('FUND_CODE',li_awd_rec.fund_code);
2368 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2369 END IF;
2370 END IF;
2371
2372 IF(g_fed_fund_code NOT IN('DLP','DLS','DLU') AND cur_legacy_disb_int_rec.affirm_flag IS NOT NULL)THEN
2373 p_return_status := 'E';
2374 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_DISB_COL_BLNK');
2375 FND_MESSAGE.SET_TOKEN('COLUMN_NAME','AFFIRM_FLAG');
2376 FND_MESSAGE.SET_TOKEN('FUND_CODE',li_awd_rec.fund_code);
2377 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2378 END IF;
2379
2380 -- net amount is always the accepted amount for all the awards except for Direct Loans and Common Line Loans
2381 l_net_amount := NVL(cur_legacy_disb_int_rec.accepted_amt,0);
2382 -- perform direct loan validations
2383
2384 IF(g_fed_fund_code IS NOT NULL AND g_fed_fund_code IN ('DLP','DLS','DLU'))THEN
2385 -- interest rebate amount is optional for Direct Loans. Validate if it is < 0
2386
2387 IF(cur_legacy_disb_int_rec.int_rebate_amt IS NOT NULL AND cur_legacy_disb_int_rec.int_rebate_amt < 0)THEN
2388 p_return_status := 'E';
2389 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_DISB_COL_REQ');
2390 FND_MESSAGE.SET_TOKEN('COLUMN_NAME','INT_REBATE_AMT');
2391 FND_MESSAGE.SET_TOKEN('FUND_CODE',li_awd_rec.fund_code);
2392 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2393 END IF;
2394 IF(cur_legacy_disb_int_rec.affirm_flag IS NOT NULL AND cur_legacy_disb_int_rec.affirm_flag NOT IN('Y','N'))THEN
2395 p_return_status := 'E';
2396 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_AFRM_FG_INVALID');
2397 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2398 END IF;
2399
2400 -- compute l_net_amount here
2401 l_net_amount := NVL(cur_legacy_disb_int_rec.accepted_amt,0) -
2402 NVL(cur_legacy_disb_int_rec.fee_1_amt,0) +
2403 NVL(cur_legacy_disb_int_rec.int_rebate_amt,0);
2404
2405 -- perform common line loan validations
2406 ELSIF(g_fed_fund_code IS NOT NULL AND g_fed_fund_code IN ('FLP','FLS','FLU','ALT','GPLUSFL')) THEN
2407
2408 IF(cur_legacy_disb_int_rec.fee_paid_1_amt IS NOT NULL AND cur_legacy_disb_int_rec.fee_paid_1_amt < 0)THEN
2409 p_return_status := 'E';
2410 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_DISB_COL_REQ');
2411 FND_MESSAGE.SET_TOKEN('COLUMN_NAME','FEE_PAID_1_AMT');
2412 FND_MESSAGE.SET_TOKEN('FUND_CODE',li_awd_rec.fund_code);
2413 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2414 END IF;
2415 IF(cur_legacy_disb_int_rec.fee_2_amt IS NOT NULL AND cur_legacy_disb_int_rec.fee_2_amt < 0)THEN
2416 p_return_status := 'E';
2417 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_DISB_COL_REQ');
2418 FND_MESSAGE.SET_TOKEN('COLUMN_NAME','FEE_2_AMT');
2419 FND_MESSAGE.SET_TOKEN('FUND_CODE',li_awd_rec.fund_code);
2420 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2421 END IF;
2422 IF(cur_legacy_disb_int_rec.fee_paid_2_amt IS NOT NULL AND cur_legacy_disb_int_rec.fee_paid_2_amt < 0)THEN
2423 p_return_status := 'E';
2424 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_DISB_COL_REQ');
2425 FND_MESSAGE.SET_TOKEN('COLUMN_NAME','FEE_PAID_2_AMT');
2426 FND_MESSAGE.SET_TOKEN('FUND_CODE',li_awd_rec.fund_code);
2427 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2428 END IF;
2429
2430 -- compute net amount for common line loans
2431 l_net_amount := NVL(cur_legacy_disb_int_rec.accepted_amt,0) -
2432 NVL(cur_legacy_disb_int_rec.fee_1_amt,0) -
2433 NVL(cur_legacy_disb_int_rec.fee_2_amt,0) +
2434 NVL(cur_legacy_disb_int_rec.fee_paid_1_amt,0) +
2435 NVL(cur_legacy_disb_int_rec.fee_paid_2_amt,0);
2436 END IF; -- end common line validations
2437 -- call to insert row of disb production table package. with the calculations of Net Amount for
2438 -- direct loans and common line loans
2439 -- call to insert disb record and further validations only if the return status is S or W
2440
2441 IF(p_return_status = 'S' OR p_return_status = 'W')THEN
2442 SAVEPOINT st_def_ins_disb_records;
2443
2444 create_disbursment_record(li_awd_rec,cur_legacy_disb_int_rec,l_disb_rec_ins_status);
2445
2446 IF(l_disb_rec_ins_status = 'S' OR l_disb_rec_ins_status = 'W')THEN
2447
2448 -- process disb holds records for context disb
2449 validate_disb_hold_int_rec(li_awd_rec,cur_legacy_disb_int_rec,l_return_status_dh);
2450 IF(l_return_status_dh = 'E')THEN
2451 ROLLBACK TO st_def_ins_disb_records;
2452 p_return_status := 'E';
2453 RETURN;
2454 END IF;
2455 ELSE
2456 ROLLBACK TO st_def_ins_disb_records;
2457 p_return_status := 'E';
2458 RETURN;
2459 END IF;
2460 -- process disb activity for context disb. process disb activity only if transaction type is ACTUAL or CANCELLED
2461 -- disbursement activity records are not processed for FWS funds.
2462
2463 IF(cur_legacy_disb_int_rec.trans_type_code IN ('A','C') AND p_return_status <> 'E' AND g_fed_fund_code <> 'FWS') THEN
2464 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2465 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_li_import.validate_disburs_int_rec.debug','calling validate_disb_act_int_rec');
2466 END IF;
2467
2468 validate_disb_act_int_rec(li_awd_rec,cur_legacy_disb_int_rec,l_return_status_da);
2469
2470 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2471 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_li_import.validate_disburs_int_rec.debug','done validate_disb_act_int_rec');
2472 END IF;
2473 IF(l_return_status_da = 'E')THEN
2474 ROLLBACK TO st_def_ins_disb_records;
2475 p_return_status := 'E';
2476 RETURN;
2477 END IF;
2478 END IF;
2479 END IF;
2480 END LOOP;
2481 EXCEPTION WHEN OTHERS THEN
2482 p_return_status := 'E';
2483 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
2484 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
2485 'igf.plsql.igf_aw_li_import.validate_disburs_int_rec.exception',
2486 SQLERRM);
2487 END IF;
2488 FND_MESSAGE.SET_NAME('IGF','IGF_GE_UNHANDLED_EXP');
2489 FND_MESSAGE.SET_TOKEN('NAME','VALIDATE_DISBURS_INT_REC : '||SQLERRM);
2490 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
2491 END validate_disburs_int_rec;
2492
2493 PROCEDURE validate_disb_act_int_rec(li_awd_rec igf_aw_li_awd_ints%ROWTYPE,
2494 li_awd_disb_rec igf_aw_li_disb_ints%ROWTYPE,
2495 p_return_status OUT NOCOPY VARCHAR2
2496 ) IS
2497 /***************************************************************
2498 Created By : nsidana
2499 Date Created By : 11/28/2003
2500 Purpose : Validates disbursements activity interface record.
2501 Known Limitations,Enhancements or Remarks
2502 Change History :
2503 Who When What
2504 pssahni 10/20/2004 The newly added fields in the table IGF_AW_LI_DACT_INTS and table IGF_DB_AWD_DISB_DTL.
2505 (ORIG_FEE_AMT, GUARNT_FEE_AMT, ORIG_FEE_PAID_AMT, GUARNT_FEE_PAID_AMT, INT_REBATE_AMT)
2506 are taken into account and data is inseted into IGF_AW_DB_CHG_DTLS table also
2507 Validation is done to check that the last activity always reflect the current disbursement information.
2508
2509 ***************************************************************/
2510 CURSOR cur_li_disb_act_int IS
2511 SELECT
2512 trim(disbact.ci_alternate_code) ci_alternate_code,
2513 trim(disbact.person_number) person_number,
2514 trim(disbact.award_number_txt) award_number_txt,
2515 disbact.disbursement_num,
2516 disbact.disb_activity_num,
2517 trim(disbact.disb_activity_type) disb_activity_type,
2518 disbact.disb_net_amt,
2519 disbact.disb_date,
2520 trim(disbact.sf_status_code) sf_status_code,
2521 disbact.sf_status_date,
2522 trim(disbact.sf_invoice_num_txt) sf_invoice_num_txt,
2523 trim(disbact.sf_credit_num_txt) sf_credit_num_txt,
2524 trim(disbact.disb_status_code) disb_status_code,
2525 disbact.notification_date,
2526 disbact.created_by,
2527 disbact.creation_date,
2528 disbact.last_updated_by,
2529 disbact.last_update_date,
2530 disbact.last_update_login,
2531 trim(disbact.spnsr_credit_num_txt) spnsr_credit_num_txt,
2532 trim(disbact.spnsr_charge_num_txt) spnsr_charge_num_txt,
2533 trim(disbact.origination_fee_amt ) origination_fee_amt ,
2534 trim(disbact.guarntee_fee_amt ) guarntee_fee_amt ,
2535 trim(disbact.origination_fee_paid_amt ) origination_fee_paid_amt ,
2536 trim(disbact.guarntee_fee_paid_amt ) guarntee_fee_paid_amt ,
2537 trim(disbact.interest_rebate_amt ) interest_rebate_amt ,
2538 trim(disbact.disbursement_accepted_amt ) disbursement_accepted_amt
2539 FROM igf_aw_li_dact_ints disbact
2540 WHERE ci_alternate_code = li_awd_disb_rec.ci_alternate_code
2541 AND person_number = li_awd_disb_rec.person_number
2542 AND award_number_txt = li_awd_disb_rec.award_number_txt
2543 AND disbursement_num = li_awd_disb_rec.disbursement_num
2544 ORDER BY disb_activity_num;
2545
2546 -- Get the last activity sequence number
2547 CURSOR cur_get_max_disb IS
2548 SELECT MAX(disb_activity_num) last_activity
2549 FROM igf_aw_li_dact_ints
2550 WHERE ci_alternate_code = li_awd_disb_rec.ci_alternate_code
2551 AND person_number = li_awd_disb_rec.person_number
2552 AND award_number_txt = li_awd_disb_rec.award_number_txt
2553 AND disbursement_num = li_awd_disb_rec.disbursement_num;
2554
2555 max_disb_num cur_get_max_disb%ROWTYPE;
2556
2557 CURSOR cur_sf_credit_num (p_credit_num igf_aw_li_dact_ints.sf_credit_num_txt%TYPE) IS
2558 SELECT credit_number, amount, credit_id
2559 FROM igs_fi_credits
2560 WHERE party_id = g_person_id
2561 AND credit_number = p_credit_num;
2562 cur_sf_credit_num_rec cur_sf_credit_num%ROWTYPE;
2563 l_trans_credit_id igs_fi_credits_all.credit_id%TYPE;
2564 l_trans_spnsr_credit_id igs_fi_credits_all.credit_id%TYPE;
2565
2566 CURSOR c_get_cal_typ_seq_num(p_alternate_code igs_ca_inst.alternate_code%TYPE) IS
2567 SELECT cal_type, sequence_number
2568 FROM igs_ca_inst
2569 WHERE alternate_code = p_alternate_code;
2570
2571 r_get_cal_typ_seq_num c_get_cal_typ_seq_num%ROWTYPE;
2572
2573
2574 CURSOR cur_sf_invoice_num(p_invoice_num igf_aw_li_dact_ints.sf_invoice_num_txt%TYPE) IS
2575 SELECT invoice_number, invoice_amount, invoice_id
2576 FROM igs_fi_inv_int
2577 WHERE person_id = g_person_id
2578 AND invoice_number = p_invoice_num;
2579 cur_sf_invoice_num_rec cur_sf_invoice_num%ROWTYPE;
2580 l_trans_invoice_id igs_fi_inv_int_all.invoice_id%TYPE;
2581 l_trans_spnsr_charge_id igs_fi_inv_int_all.invoice_id%TYPE;
2582
2583 --l_disbact_rec igf_aw_li_dact_ints%ROWTYPE;
2584 l_disbact_rec cur_li_disb_act_int%ROWTYPE;
2585 l_ctr PLS_INTEGER;
2586 l_adjust_amt igf_aw_li_dact_ints.disb_net_amt%TYPE;
2587 l_last_disb_net_amt igf_aw_li_dact_ints.disb_net_amt%TYPE;
2588 l_return_status_da VARCHAR2(1);
2589
2590 TYPE disb_net_amt_tab IS TABLE OF igf_aw_li_dact_ints.disb_net_amt%TYPE;
2591 disb_net_amt_table disb_net_amt_tab;
2592
2593 TYPE disb_date_tab IS TABLE OF igf_aw_li_dact_ints.disb_date%TYPE;
2594 disb_date_table disb_date_tab;
2595
2596 CURSOR c_get_all_disb_amt IS
2597 SELECT disbact.disb_net_amt, disbact.disb_date
2598 FROM igf_aw_li_dact_ints disbact
2599 WHERE ci_alternate_code = li_awd_disb_rec.ci_alternate_code
2600 AND person_number = li_awd_disb_rec.person_number
2601 AND award_number_txt = li_awd_disb_rec.award_number_txt
2602 AND disbursement_num = li_awd_disb_rec.disbursement_num
2603 ORDER BY disb_activity_num;
2604
2605
2606 -- Get the last disbursment sequence number activity
2607 CURSOR c_get_last_activity (p_disb_seq_num NUMBER)
2608 IS
2609 SELECT *
2610 FROM igf_aw_li_dact_ints
2611 WHERE ci_alternate_code = li_awd_disb_rec.ci_alternate_code
2612 AND person_number = li_awd_disb_rec.person_number
2613 AND award_number_txt = li_awd_disb_rec.award_number_txt
2614 AND disbursement_num = li_awd_disb_rec.disbursement_num
2615 AND disb_activity_num = p_disb_seq_num;
2616
2617 last_activity_rec c_get_last_activity%ROWTYPE;
2618
2619
2620 CURSOR cur_rowid ( p_award_id NUMBER, p_disb_num NUMBER, p_disb_seq_num NUMBER)
2621 IS
2622 SELECT ROWID row_id
2623 FROM igf_aw_db_chg_dtls
2624 WHERE award_id = p_award_id
2625 AND disb_num = p_disb_num
2626 AND disb_seq_num = p_disb_seq_num;
2627 lv_rowid cur_rowid%ROWTYPE;
2628
2629 l_rowid VARCHAR2(25);
2630 l_padding_string VARCHAR2(100);
2631
2632
2633 BEGIN -- begin of validate_disb_act_int_rec
2634 p_return_status := 'S';
2635 disb_net_amt_table := NULL; disb_date_table := NULL;
2636 l_padding_string := RPAD(' ',6,' ')||'-- ';
2637 l_disbact_rec := NULL;
2638
2639 OPEN cur_li_disb_act_int; FETCH cur_li_disb_act_int INTO l_disbact_rec;
2640 g_processing_string := l_padding_string || igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','PROCESSING') || ' '||
2641 igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','DISBURSEMENT_ACTIVITY');
2642 FND_FILE.PUT_LINE(FND_FILE.LOG,g_processing_string);
2643 -- if transaction type is Actual and disb records are not found.
2644
2645 IF(cur_li_disb_act_int%NOTFOUND AND li_awd_disb_rec.trans_type_code = 'A')THEN
2646 CLOSE cur_li_disb_act_int;
2647 p_return_status := 'E';
2648 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_DACT_NOT_FND');
2649 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2650 RETURN;
2651 -- if transaction type is cancelled and disb activity records are not found
2652 ELSIF(cur_li_disb_act_int%NOTFOUND AND li_awd_disb_rec.trans_type_code = 'C') THEN
2653 CLOSE cur_li_disb_act_int;
2654 RETURN;
2655 END IF;
2656
2657 IF cur_li_disb_act_int%ISOPEN THEN
2658 CLOSE cur_li_disb_act_int;
2659 END IF;
2660
2661 --
2662 l_ctr:=1;
2663 FOR cur_li_disb_act_int_rec IN cur_li_disb_act_int LOOP
2664 IF(l_ctr <> cur_li_disb_act_int_rec.disb_activity_num) THEN
2665 p_return_status := 'E';
2666 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_DB_SEQ_ST_1_INR_1');
2667 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2668 RETURN;
2669 END IF;
2670 l_ctr := l_ctr +1;
2671 END LOOP;
2672
2673
2674 IF(disb_net_amt_table IS NULL OR disb_date_table IS NULL)THEN -- this condition makes this if execute only once per call
2675
2676 -- fetch all the disb_net_amt into a PL/SQL Table. This is used in calculating the adjustment amount
2677 OPEN c_get_all_disb_amt;
2678 FETCH c_get_all_disb_amt BULK COLLECT INTO disb_net_amt_table,
2679 disb_date_table;
2680 CLOSE c_get_all_disb_amt;
2681
2682
2683 -- accepted amt in disb should be = to disb act last seq num disb_net_amt. step 15 in TD validation for disb acts
2684
2685 -- Find the last activity details
2686 OPEN cur_get_max_disb; FETCH cur_get_max_disb INTO max_disb_num ; CLOSE cur_get_max_disb;
2687 OPEN c_get_last_activity (max_disb_num.last_activity);
2688 FETCH c_get_last_activity INTO last_activity_rec;
2689 CLOSE c_get_last_activity;
2690
2691
2692 IF(( NVL(li_awd_disb_rec.accepted_amt,-1) <> NVL(disb_net_amt_table(disb_net_amt_table.COUNT),-1))
2693 OR (NVL(li_awd_disb_rec.fee_1_amt,-1) <> NVL(last_activity_rec.origination_fee_amt,-1) )
2694 OR (NVL(li_awd_disb_rec.fee_2_amt,-1) <> NVL(last_activity_rec.guarntee_fee_amt,-1) )
2695 OR (NVL(li_awd_disb_rec.fee_paid_1_amt,-1) <> NVL(last_activity_rec.origination_fee_paid_amt,-1) )
2696 OR (NVL(li_awd_disb_rec.fee_paid_2_amt,-1) <> NVL(last_activity_rec.guarntee_fee_paid_amt,-1) )
2697 OR (NVL(li_awd_disb_rec.int_rebate_amt,-1) <> NVL(last_activity_rec.interest_rebate_amt,-1) )
2698 )THEN
2699
2700
2701 p_return_status := 'E';
2702 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_ACPT_AMT_EQ_DISB_NET');
2703 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2704 END IF;
2705 END IF;
2706
2707 -- l_ctr has a different meaning here. used in calculating the disb_net_amt
2708 l_ctr := 0;
2709 FOR cur_li_disb_act_int_rec IN cur_li_disb_act_int LOOP
2710 l_ctr := l_ctr +1;
2711 g_processing_string := l_padding_string || igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','PROCESSING') || ' '||
2712 igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','DISBURSEMENT_ACTIVITY');
2713 FND_FILE.PUT_LINE(FND_FILE.LOG,g_processing_string||': '||cur_li_disb_act_int_rec.disb_activity_num);
2714 l_trans_invoice_id := NULL; l_trans_credit_id := NULL; l_trans_spnsr_credit_id := NULL; l_trans_spnsr_charge_id := NULL;
2715
2716 -- Disb Activity Table should not be processed for Disb Transaction Type is PLANNED
2717 IF(li_awd_disb_rec.trans_type_code = 'P' AND cur_li_disb_act_int_rec.person_number IS NOT NULL)THEN
2718 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_NO_PRC_ACTIVITY');
2719 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2720 RETURN;
2721 END IF;
2722
2723 -- sponsorship charge number and sponsorship activity number should be blank for non sponsorship awards
2724 -- introduced in FACR117
2725 IF(g_fed_fund_code <> 'SPNSR')THEN
2726 IF(cur_li_disb_act_int_rec.spnsr_credit_num_txt IS NOT NULL)THEN
2727 p_return_status := 'E';
2728 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_DISB_COL_BLNK');
2729 FND_MESSAGE.SET_TOKEN('COLUMN_NAME','SPNSR_CREDIT_NUM_TXT');
2730 FND_MESSAGE.SET_TOKEN('FUND_CODE',li_awd_rec.fund_code);
2731 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2732 END IF;
2733 IF(cur_li_disb_act_int_rec.spnsr_charge_num_txt IS NOT NULL)THEN
2734 p_return_status := 'E';
2735 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_DISB_COL_BLNK');
2736 FND_MESSAGE.SET_TOKEN('COLUMN_NAME','SPNSR_CHARGE_NUM_TXT');
2737 FND_MESSAGE.SET_TOKEN('FUND_CODE',li_awd_rec.fund_code);
2738 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2739 END IF;
2740 ELSIF(g_fed_fund_code = 'SPNSR')THEN
2741 IF(cur_li_disb_act_int_rec.sf_status_code IS NOT NULL AND cur_li_disb_act_int_rec.sf_status_code = 'P')THEN
2742 -- for posted transactions either both invoice number and sponsor credit number should be not null or both should be null.
2743 -- in any other cases, log an error
2744 IF ((cur_li_disb_act_int_rec.sf_invoice_num_txt IS NULL AND cur_li_disb_act_int_rec.spnsr_credit_num_txt IS NOT NULL) OR
2745 (cur_li_disb_act_int_rec.sf_invoice_num_txt IS NOT NULL AND cur_li_disb_act_int_rec.spnsr_credit_num_txt IS NULL))THEN
2746 p_return_status := 'E';
2747 FND_MESSAGE.SET_NAME('IGF','IGF_SP_LI_INVN_SPCRN_REQ');
2748 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2749 END IF;
2750 IF ((cur_li_disb_act_int_rec.sf_credit_num_txt IS NULL AND cur_li_disb_act_int_rec.spnsr_charge_num_txt IS NOT NULL) OR
2751 (cur_li_disb_act_int_rec.sf_credit_num_txt IS NOT NULL AND cur_li_disb_act_int_rec.spnsr_charge_num_txt IS NULL)) THEN
2752 p_return_status := 'E';
2753 FND_MESSAGE.SET_NAME('IGF','IGF_SP_LI_CRN_SPINVN_REQ');
2754 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2755 END IF;
2756 END IF;
2757 END IF;
2758
2759 -- These validations are necessary only from second Disb Act records
2760 IF(l_ctr > 1)THEN
2761 -- adjustment amount is calculated here.
2762 l_adjust_amt := disb_net_amt_table(l_ctr) - disb_net_amt_table(l_ctr-1);
2763 -- disb dates should be in ascending order
2764 IF(disb_date_table(l_ctr) < disb_date_table(l_ctr-1))THEN
2765 p_return_status := 'E';
2766 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_ADJ_DB_DT_INVALID');
2767 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2768 END IF;
2769 ELSIF(l_ctr = 1)THEN
2770 l_adjust_amt := 0;
2771 END IF;
2772
2773 -- disbursement date cannot be greater than sysdate
2774 IF(disb_date_table(l_ctr) > trunc(SYSDATE))THEN
2775 p_return_status := 'E';
2776 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_ADJ_DB_DT_GT_SYSDATE');
2777 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2778 END IF;
2779
2780 -- both sf_invoice_num_txt and sf_credit_num_txt should not be present at the disb activity level. error if such case
2781 IF(cur_li_disb_act_int_rec.sf_invoice_num_txt IS NOT NULL AND cur_li_disb_act_int_rec.sf_credit_num_txt IS NOT NULL)THEN
2782 p_return_status := 'E';
2783 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_CRINV_BOTH_PRSNT');
2784 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2785 END IF;
2786
2787 -- if either the invoice num txt or the credit_num_txt is present then sf_status_code is mandatory
2788 IF(cur_li_disb_act_int_rec.sf_invoice_num_txt IS NOT NULL OR cur_li_disb_act_int_rec.sf_credit_num_txt IS NOT NULL) THEN
2789 IF(cur_li_disb_act_int_rec.sf_status_code IS NULL)THEN
2790 p_return_status := 'E';
2791 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_SF_STATUS_BLNK');
2792 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2793 END IF;
2794 END IF;
2795
2796 -- Validate SF status code values
2797 IF(cur_li_disb_act_int_rec.sf_status_code IS NOT NULL AND cur_li_disb_act_int_rec.sf_status_code NOT IN ('P','R'))THEN
2798 p_return_status := 'E';
2799 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_SF_STATUS_INVALID');
2800 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2801 END IF;
2802
2803 -- first disb activity cannot have invoice number
2804 IF(cur_li_disb_act_int_rec.sf_invoice_num_txt IS NOT NULL AND l_ctr = 1)THEN
2805 p_return_status := 'E';
2806 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_INV_NUM_FIRST_DBIB');
2807 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2808 END IF;
2809
2810 -- credit number validation for posted transactions only. valid for open and legacy details award years
2811 IF(cur_li_disb_act_int_rec.sf_credit_num_txt IS NOT NULL AND cur_li_disb_act_int_rec.sf_status_code = 'P')THEN
2812 -- Fetch the credit number from the igs_fi_credits table
2813 cur_sf_credit_num_rec := NULL;
2814 OPEN cur_sf_credit_num(cur_li_disb_act_int_rec.sf_credit_num_txt); FETCH cur_sf_credit_num INTO cur_sf_credit_num_rec;
2815 CLOSE cur_sf_credit_num;
2816 l_trans_credit_id := cur_sf_credit_num_rec.credit_id;
2817 IF(FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level)THEN
2818 g_debug_string := 'SF Credit Num'||cur_sf_credit_num_rec.credit_number||'credit id'||l_trans_credit_id;
2819 END IF;
2820 IF(cur_sf_credit_num_rec.credit_number IS NULL)THEN
2821 p_return_status := 'E';
2822 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_CR_NUM_NOT_FND');
2823 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2824 END IF;
2825 -- credit amount validations for open award year only. the credit amount should match with the adjusted amount
2826 IF(g_award_year_status_code = 'O' AND cur_sf_credit_num_rec.amount IS NOT NULL)THEN
2827 IF((cur_sf_credit_num_rec.amount <> ABS(l_adjust_amt) AND l_ctr <> 1) OR
2828 (l_ctr=1 AND cur_sf_credit_num_rec.amount <> cur_li_disb_act_int_rec.disb_net_amt))THEN
2829 p_return_status := 'E';
2830 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_CR_AMT_NOT_MTCH');
2831 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2832 END IF;
2833 END IF;
2834 END IF;
2835
2836 -- invoice number validation for posted transactions only. valid for open and legacy details award years
2837 IF(cur_li_disb_act_int_rec.sf_invoice_num_txt IS NOT NULL AND cur_li_disb_act_int_rec.sf_status_code = 'P')THEN
2838 cur_sf_invoice_num_rec := NULL;
2839 OPEN cur_sf_invoice_num(cur_li_disb_act_int_rec.sf_invoice_num_txt); FETCH cur_sf_invoice_num INTO cur_sf_invoice_num_rec;
2840 CLOSE cur_sf_invoice_num;
2841 l_trans_invoice_id := cur_sf_invoice_num_rec.invoice_id;
2842 IF(FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level)THEN
2843 g_debug_string := g_debug_string ||'SF Invoice Num'||cur_sf_invoice_num_rec.invoice_number||'invoice id'||l_trans_invoice_id;
2844 END IF;
2845 IF(cur_sf_invoice_num_rec.invoice_number IS NULL)THEN
2846 p_return_status := 'E';
2847 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_INV_NUM_NOT_FND');
2848 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2849 END IF;
2850 -- invoice amount validations for open award year only. the invoice amount should match with the adjusted amount
2851 IF(g_award_year_status_code = 'O' AND cur_sf_invoice_num_rec.invoice_amount IS NOT NULL AND l_ctr <> 1)THEN
2852 IF(cur_sf_invoice_num_rec.invoice_amount <> ABS(l_adjust_amt))THEN
2853 p_return_status := 'E';
2854 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_INV_AMT_NOT_MTCH');
2855 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2856 END IF;
2857 END IF;
2858 END IF;
2859
2860 -- sponsor credit number validations for posted transactions only. applicable for open as well as legacy details award years
2861 IF(cur_li_disb_act_int_rec.spnsr_credit_num_txt IS NOT NULL AND cur_li_disb_act_int_rec.sf_status_code = 'P')THEN
2862 --Fetch the Sponsorship Credit number from the igs_fi_credits_table
2863 cur_sf_credit_num_rec := NULL;
2864 OPEN cur_sf_credit_num(cur_li_disb_act_int_rec.spnsr_credit_num_txt); FETCH cur_sf_credit_num INTO cur_sf_credit_num_rec;
2865 CLOSE cur_sf_credit_num;
2866 l_trans_spnsr_credit_id := cur_sf_credit_num_rec.credit_id;
2867 IF(FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level)THEN
2868 g_debug_string := 'SF sponsor credit num'||cur_sf_credit_num_rec.credit_number||'credit id is'||l_trans_spnsr_credit_id;
2869 END IF;
2870 IF(cur_sf_credit_num_rec.credit_number IS NULL)THEN
2871 p_return_status := 'E';
2872 FND_MESSAGE.SET_NAME('IGF','IGF_SP_LI_SPNSR_CRNUM_NOT_FND');
2873 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2874 END IF;
2875 -- credit amount validations for open award year only. the credit amount should match with the adjusted amount
2876 IF(g_award_year_status_code = 'O' AND cur_sf_credit_num_rec.amount IS NOT NULL)THEN
2877 IF((cur_sf_credit_num_rec.amount <> ABS(l_adjust_amt) AND l_ctr <> 1) OR
2878 (l_ctr=1 AND cur_sf_credit_num_rec.amount <> cur_li_disb_act_int_rec.disb_net_amt))THEN
2879 p_return_status := 'E';
2880 FND_MESSAGE.SET_NAME('IGF','IGF_SP_LI_CR_AMT_NOT_MTCH');
2881 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2882 END IF;
2883 END IF;
2884 END IF;
2885
2886 -- sponsor charge number validation for posted transactions only. valid for open and legacy details award years
2887 IF(cur_li_disb_act_int_rec.spnsr_charge_num_txt IS NOT NULL AND cur_li_disb_act_int_rec.sf_status_code = 'P')THEN
2888 cur_sf_invoice_num_rec := NULL;
2889 OPEN cur_sf_invoice_num(cur_li_disb_act_int_rec.spnsr_charge_num_txt); FETCH cur_sf_invoice_num INTO cur_sf_invoice_num_rec;
2890 CLOSE cur_sf_invoice_num;
2891 l_trans_spnsr_charge_id := cur_sf_invoice_num_rec.invoice_id;
2892 IF(FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level)THEN
2893 g_debug_string := g_debug_string ||'SF sponsor charge Num'||cur_sf_invoice_num_rec.invoice_number||'charge id'||l_trans_spnsr_charge_id;
2894 END IF;
2895 IF(cur_sf_invoice_num_rec.invoice_number IS NULL)THEN
2896 p_return_status := 'E';
2897 FND_MESSAGE.SET_NAME('IGF','IGF_SP_LI_SPNSR_INNUM_NOT_FND');
2898 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2899 END IF;
2900 -- sponsor charge amount validations for open award year only. the invoice amount should match with the adjusted amount
2901 IF(g_award_year_status_code = 'O' AND cur_sf_invoice_num_rec.invoice_amount IS NOT NULL AND l_ctr <> 1)THEN
2902 IF(cur_sf_invoice_num_rec.invoice_amount <> ABS(l_adjust_amt))THEN
2903 p_return_status := 'E';
2904 FND_MESSAGE.SET_NAME('IGF','IGF_SP_LI_INV_AMT_NOT_MTCH');
2905 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2906 END IF;
2907 END IF;
2908 END IF;
2909
2910 -- student finance status date
2911 IF(NVL(cur_li_disb_act_int_rec.sf_status_code,'X') = 'P' OR
2912 cur_li_disb_act_int_rec.sf_invoice_num_txt IS NOT NULL OR
2913 cur_li_disb_act_int_rec.sf_credit_num_txt IS NOT NULL)THEN
2914 IF(cur_li_disb_act_int_rec.sf_status_date IS NULL)THEN
2915 p_return_status := 'E';
2916 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_SF_STATUS_DT_BLNK');
2917 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2918 END IF;
2919 END IF;
2920
2921 IF(cur_li_disb_act_int_rec.sf_status_code IS NOT NULL AND cur_li_disb_act_int_rec.sf_status_code = 'R')THEN
2922 -- validation introduced in FACR117. when the sf_status is ready to send, the by products of the posting
2923 -- should be null. viz. the status date, invioce num, credit num, spnsr credit num, spnsr invoice num
2924 IF(cur_li_disb_act_int_rec.sf_status_date IS NOT NULL OR cur_li_disb_act_int_rec.sf_credit_num_txt IS NOT NULL OR
2925 cur_li_disb_act_int_rec.sf_invoice_num_txt IS NOT NULL OR cur_li_disb_act_int_rec.spnsr_credit_num_txt IS NOT NULL OR
2926 cur_li_disb_act_int_rec.spnsr_charge_num_txt IS NOT NULL)THEN
2927 p_return_status := 'E';
2928 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_SF_STATUS_DT_NTREQ');
2929 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2930 END IF;
2931 END IF;
2932
2933 IF((l_adjust_amt < 0 AND cur_li_disb_act_int_rec.sf_credit_num_txt IS NOT NULL) OR
2934 (l_adjust_amt >= 0 AND cur_li_disb_act_int_rec.sf_invoice_num_txt IS NOT NULL)
2935 ) THEN
2936 p_return_status := 'E';
2937 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_INC_CR_INV_NUM');
2938 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2939 END IF;
2940
2941 -- validate disbursement status and disbursement ctivity.
2942 IF(g_fed_fund_code IN ('DLP','DLS','DLU'))THEN
2943
2944 IF(cur_li_disb_act_int_rec.disb_status_code IS NULL)THEN
2945 p_return_status := 'E';
2946 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_INV_DISB_ACT');
2947 FND_MESSAGE.SET_TOKEN('FUND',li_awd_rec.fund_code);
2948 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2949 ELSIF(cur_li_disb_act_int_rec.disb_status_code NOT IN ('A','B','G','R'))THEN
2950 p_return_status := 'E';
2951 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_DISB_ACT_UPD');
2952 FND_MESSAGE.SET_TOKEN('COL_NAME', 'DISB_STATUS_CODE');
2953 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2954 END IF;
2955
2956 IF cur_li_disb_act_int_rec.disb_activity_type IS NULL THEN
2957 p_return_status := 'E';
2958 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_INVLD_ACTVTY_CD');
2959 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2960 ELSE
2961 IF(cur_li_disb_act_int_rec.disb_activity_type NOT IN ('D','A','Q'))THEN
2962 p_return_status := 'E';
2963 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_DISB_ACT_UPD');
2964 FND_MESSAGE.SET_TOKEN('COL_NAME','DISB_ACTIVITY_TYPE');
2965 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2966 END IF;
2967 -- the first disbact record should have the disbact type as D always
2968 IF(l_ctr = 1 AND cur_li_disb_act_int_rec.disb_activity_type <> 'D')THEN
2969 p_return_status := 'E';
2970 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_DISB_FIRST_ACT');
2971 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2972 END IF;
2973 END IF;
2974
2975
2976 ELSE
2977 -- disbursement status and activity should be null for non Direct Loans
2978 IF(cur_li_disb_act_int_rec.disb_status_code IS NOT NULL)THEN
2979 p_return_status := 'E';
2980 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_ACT_COL_BLNK');
2981 FND_MESSAGE.SET_TOKEN('COLUMN_NAME','DISB_STATUS_CODE');
2982 FND_MESSAGE.SET_TOKEN('FUND_CODE',li_awd_rec.fund_code);
2983 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2984 END IF;
2985 IF(cur_li_disb_act_int_rec.disb_activity_type IS NOT NULL)THEN
2986 p_return_status := 'E';
2987 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_ACT_COL_BLNK');
2988 FND_MESSAGE.SET_TOKEN('COLUMN_NAME','DISB_ACTIVITY_TYPE');
2989 FND_MESSAGE.SET_TOKEN('FUND_CODE',li_awd_rec.fund_code);
2990 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2991 END IF;
2992 END IF;
2993
2994 -- Disbusement Accepted amount is a mandatory amount
2995 IF cur_li_disb_act_int_rec.disbursement_accepted_amt IS NULL THEN
2996 p_return_status := 'E';
2997 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_ACPT_AMT_BLNK');
2998 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string || FND_MESSAGE.GET);
2999 END IF;
3000
3001
3002 -- insert row only if all the validations are successful
3003 -- check for cal of l_adjust_amt
3004
3005 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3006 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_li_import.validate_disb_act_int_rec.debug','inserting into igf_db_awd_disb_dtl table');
3007 END IF;
3008
3009 IF p_return_status <> 'E' THEN
3010 l_rowid := NULL;
3011
3012 OPEN c_get_cal_typ_seq_num(li_awd_disb_rec.ld_alternate_code);
3013 FETCH c_get_cal_typ_seq_num INTO r_get_cal_typ_seq_num;
3014 CLOSE c_get_cal_typ_seq_num;
3015
3016 igf_db_awd_disb_dtl_pkg.insert_row (
3017 x_rowid => l_rowid,
3018 x_award_id => g_award_id,
3019 x_disb_num => li_awd_disb_rec.disbursement_num,
3020 x_disb_seq_num => cur_li_disb_act_int_rec.disb_activity_num,
3021 x_disb_gross_amt => cur_li_disb_act_int_rec.disbursement_accepted_amt,
3022 x_fee_1 => cur_li_disb_act_int_rec.origination_fee_amt ,
3023 x_fee_2 => cur_li_disb_act_int_rec.guarntee_fee_amt ,
3024 x_disb_net_amt => cur_li_disb_act_int_rec.disb_net_amt,
3025 x_disb_adj_amt => l_adjust_amt ,
3026 x_disb_date => cur_li_disb_act_int_rec.disb_date,
3027 x_fee_paid_1 => cur_li_disb_act_int_rec.origination_fee_paid_amt,
3028 x_fee_paid_2 => cur_li_disb_act_int_rec.guarntee_fee_paid_amt ,
3029 x_disb_activity => cur_li_disb_act_int_rec.disb_activity_type,
3030 x_disb_batch_id => NULL,
3031 x_disb_ack_date => NULL,
3032 x_booking_batch_id => NULL,
3033 x_booked_date => NULL,
3034 x_disb_status => cur_li_disb_act_int_rec.disb_status_code,
3035 x_disb_status_date => NULL,
3036 x_sf_status => cur_li_disb_act_int_rec.sf_status_code,
3037 x_sf_status_date => cur_li_disb_act_int_rec.sf_status_date,
3038 x_sf_invoice_num => l_trans_invoice_id, -- dont get confused by the col name. this is invoice id only
3039 x_spnsr_credit_id => l_trans_spnsr_credit_id,
3040 x_spnsr_charge_id => l_trans_spnsr_charge_id,
3041 x_sf_credit_id => l_trans_credit_id,
3042 x_error_desc => NULL,
3043 x_mode => 'R',
3044 x_notification_date => cur_li_disb_act_int_rec.notification_date,
3045 x_interest_rebate_amt => cur_li_disb_act_int_rec.interest_rebate_amt,
3046 x_ld_cal_type => r_get_cal_typ_seq_num.cal_type,
3047 x_ld_sequence_number => r_get_cal_typ_seq_num.sequence_number
3048 );
3049
3050
3051 IF(l_rowid IS NULL)THEN
3052 p_return_status := 'E';
3053 RETURN;
3054 END IF;
3055
3056 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3057 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_li_import.validate_disb_act_int_rec.debug','inserting into igf_aw_db_chg_dtls table');
3058 END IF;
3059 -- insert into IGF_AW_DB_CHG_DTLS table also
3060
3061 -- First delete the record in the table if present
3062 OPEN cur_rowid (g_award_id, li_awd_disb_rec.disbursement_num, cur_li_disb_act_int_rec.disb_activity_num);
3063 FETCH cur_rowid INTO lv_rowid;
3064
3065 IF cur_rowid%FOUND THEN
3066 CLOSE cur_rowid;
3067 igf_aw_db_chg_dtls_pkg.delete_row(lv_rowid.row_id);
3068 ELSE
3069 CLOSE cur_rowid;
3070 END IF;
3071
3072
3073 l_rowid := NULL;
3074 igf_aw_db_chg_dtls_pkg.insert_row (
3075 x_rowid => l_rowid,
3076 x_award_id => g_award_id,
3077 x_disb_num => li_awd_disb_rec.disbursement_num,
3078 x_disb_seq_num => cur_li_disb_act_int_rec.disb_activity_num,
3079 x_disb_accepted_amt => cur_li_disb_act_int_rec.disbursement_accepted_amt,
3080 x_orig_fee_amt => cur_li_disb_act_int_rec.origination_fee_amt,
3081 x_disb_net_amt => cur_li_disb_act_int_rec.disb_net_amt,
3082 x_disb_date => cur_li_disb_act_int_rec.disb_date,
3083 x_disb_activity => cur_li_disb_act_int_rec.disb_activity_type,
3084 x_disb_status => cur_li_disb_act_int_rec.disb_status_code,
3085 x_disb_status_date => NULL,
3086 x_disb_rel_flag => NULL,
3087 x_first_disb_flag => NULL,
3088 x_interest_rebate_amt => cur_li_disb_act_int_rec.interest_rebate_amt ,
3089 x_disb_conf_flag => NULL,
3090 x_pymnt_prd_start_date => NULL,
3091 x_note_message => NULL,
3092 x_batch_id_txt => NULL,
3093 x_ack_date => NULL,
3094 x_booking_id_txt => NULL,
3095 x_booking_date => NULL,
3096 x_mode => 'R'
3097
3098 );
3099
3100 IF(l_rowid IS NULL)THEN
3101 p_return_status := 'E';
3102 RETURN;
3103 END IF;
3104
3105
3106 END IF; -- End if of p_return_status <> 'E'
3107
3108 IF(FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level)THEN
3109 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_aw_li_import.validate_disb_act_int_rec.debug',g_debug_string);
3110 g_debug_string := NULL;
3111 END IF;
3112 END LOOP;
3113
3114 EXCEPTION WHEN OTHERS THEN
3115 p_return_status := 'E';
3116 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
3117 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
3118 'igf.plsql.igf_aw_li_import.validate_disb_act_int_rec.exception',
3119 SQLERRM);
3120 END IF;
3121 FND_MESSAGE.SET_NAME('IGF','IGF_GE_UNHANDLED_EXP');
3122 FND_MESSAGE.SET_TOKEN('NAME','VALIDATE_DISB_ACT_INT_REC : '||SQLERRM);
3123 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
3124 END validate_disb_act_int_rec;
3125
3126 PROCEDURE validate_disb_hold_int_rec(li_awd_rec igf_aw_li_awd_ints%ROWTYPE,
3127 li_awd_disb_rec igf_aw_li_disb_ints%ROWTYPE,
3128 p_return_status OUT NOCOPY VARCHAR2
3129 ) IS
3130 /***************************************************************
3131 Created By : nsidana
3132 Date Created By : 11/28/2003
3133 Purpose : Validates disbursements holds interface record.
3134 Known Limitations,Enhancements or Remarks
3135 Change History :
3136 Who When What
3137 ***************************************************************/
3138 CURSOR cur_li_disb_hold_int IS
3139 SELECT
3140 trim(disbhold.ci_alternate_code) ci_alternate_code,
3141 trim(disbhold.person_number) person_number,
3142 trim(disbhold.award_number_txt) award_number_txt,
3143 disbhold.disbursement_num,
3144 trim(disbhold.hold_code) hold_code,
3145 disbhold.hold_date,
3146 disbhold.release_date,
3147 trim(disbhold.release_reason_txt) release_reason_txt,
3148 disbhold.created_by,
3149 disbhold.creation_date,
3150 disbhold.last_updated_by,
3151 disbhold.last_update_date,
3152 disbhold.last_update_login
3153 FROM igf_aw_li_hold_ints disbhold
3154 WHERE ci_alternate_code = li_awd_disb_rec.ci_alternate_code
3155 AND person_number = li_awd_disb_rec.person_number
3156 AND award_number_txt = li_awd_disb_rec.award_number_txt
3157 AND disbursement_num = li_awd_disb_rec.disbursement_num;
3158
3159 x_release_flag igf_db_disb_holds_all.release_flag%TYPE;
3160 l_rowid VARCHAR2(25);
3161 l_hold_id igf_db_disb_holds_All.hold_id%TYPE;
3162 l_release_flag igf_db_disb_holds_all.release_flag%TYPE;
3163
3164 l_padding_string VARCHAR2(100);
3165
3166 BEGIN
3167 p_return_status := 'S';
3168 l_padding_string := RPAD(' ',6,' ')||'-- ';
3169 g_processing_string := l_padding_string || igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','PROCESSING') || ' '||
3170 igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','DISBURSEMENT_HOLD');
3171 FOR cur_li_disb_hold_int_rec IN cur_li_disb_hold_int LOOP
3172 FND_FILE.PUT_LINE(FND_FILE.LOG,g_processing_string||': '||cur_li_disb_hold_int_rec.hold_code);
3173 -- validate hold code
3174 -- should be one of 'CL','DL','DL_PROM','ENROLMENT','MISC','OVERAWARD','PELL' unless some are removed for certain award years
3175 IF(igf_ap_gen.get_aw_lookup_meaning('IGF_DB_DISB_HOLDS',cur_li_disb_hold_int_rec.hold_code, g_sys_award_year)IS NULL)THEN
3176 p_return_status := 'E';
3177 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_INVAL_HOLD_CODE');
3178 FND_MESSAGE.SET_TOKEN('HOCDE',cur_li_disb_hold_int_rec.hold_code);
3179 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string ||FND_MESSAGE.GET);
3180 RETURN;
3181 END IF;
3182 IF(cur_li_disb_hold_int_rec.release_date IS NOT NULL AND cur_li_disb_hold_int_rec.hold_date IS NOT NULL)THEN
3183 IF(trunc(cur_li_disb_hold_int_rec.release_date) < trunc(cur_li_disb_hold_int_rec.hold_date))THEN
3184 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_INVAL_REL_DATE');
3185 FND_FILE.PUT_LINE(FND_FILE.LOG,l_padding_string ||FND_MESSAGE.GET);
3186 RETURN;
3187 END IF;
3188 END IF;
3189 IF(cur_li_disb_hold_int_rec.release_date IS NULL)THEN
3190 x_release_flag := 'N';
3191 ELSE
3192 x_release_flag := 'Y';
3193 END IF;
3194 IF(cur_li_disb_hold_int_rec.release_date IS NULL)THEN
3195 l_release_flag := 'N';
3196 ELSE
3197 l_release_flag := 'Y';
3198 END IF;
3199 l_rowid := NULL;l_hold_id:=NULL;
3200 igf_db_disb_holds_pkg.insert_row(
3201 x_rowid => l_rowid,
3202 x_hold_id => l_hold_id,
3203 x_award_id => g_award_id,
3204 x_disb_num => li_awd_disb_rec.disbursement_num,
3205 x_hold => cur_li_disb_hold_int_rec.hold_code,
3206 x_hold_date => cur_li_disb_hold_int_rec.hold_date,
3207 x_hold_type => 'SYSTEM',
3208 x_release_date => cur_li_disb_hold_int_rec.release_date,
3209 x_release_flag => l_release_flag,
3210 x_release_reason=> cur_li_disb_hold_int_rec.release_reason_txt
3211 );
3212 IF(l_hold_id IS NULL)THEN
3213 p_return_status := 'E';
3214 RETURN;
3215 END IF;
3216 END LOOP;
3217 EXCEPTION WHEN OTHERS THEN
3218 p_return_status := 'E';
3219 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
3220 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
3221 'igf.plsql.igf_aw_li_import.validate_disb_hold_int_rec.exception',
3222 SQLERRM);
3223 END IF;
3224 FND_MESSAGE.SET_NAME('IGF','IGF_GE_UNHANDLED_EXP');
3225 FND_MESSAGE.SET_TOKEN('NAME','VALIDATE_DISB_HOLD_INT_REC : '||SQLERRM);
3226 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
3227 END validate_disb_hold_int_rec;
3228
3229
3230 PROCEDURE main(errbuf OUT NOCOPY VARCHAR2,
3231 retcode OUT NOCOPY NUMBER,
3232 p_award_year IN VARCHAR2,
3233 p_batch_number IN NUMBER,
3234 p_delete_flag IN VARCHAR2
3235 ) IS
3236
3237 /***************************************************************
3238 Created By : nsidana
3239 Date Created By : 11/28/2003
3240 Purpose : Main procedure for the legacy awards and disbursements import process.
3241 Known Limitations,Enhancements or Remarks
3242 Change History :
3243 Who When What
3244 || tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
3245 ***************************************************************/
3246 l_cal_type igs_ca_inst.cal_type%TYPE;
3247 l_seq_number igs_ca_inst.sequence_number%TYPE;
3248
3249 l_var_cal_type igs_ca_inst.cal_type%TYPE;
3250 l_var_seq_number igs_ca_inst.sequence_number%TYPE;
3251 l_ret_value BOOLEAN;
3252
3253 l_success_records PLS_INTEGER;
3254 l_warning_records PLS_INTEGER;
3255 l_rejected_records PLS_INTEGER;
3256 l_total_records PLS_INTEGER;
3257 lv_profile_value VARCHAR2(30);
3258 lv_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
3259
3260
3261 -- cursor to get the calendar alternate code
3262 CURSOR cur_alt_code ( x_ci_cal_type igs_ca_inst.cal_type%TYPE,
3263 x_ci_sequence_number igs_ca_inst.sequence_number%TYPE) IS
3264 SELECT cal.alternate_code
3265 FROM igs_ca_inst cal
3266 WHERE cal.cal_type = x_ci_cal_type
3267 AND cal.sequence_number = x_ci_sequence_number;
3268 l_alternate_code igs_ca_inst.alternate_code%TYPE;
3269
3270 -- cursor to verify if the cal_type and seq_number are present in the system award year
3271 CURSOR cur_in_award_year_form(x_ci_cal_type igs_ca_inst.cal_type%TYPE,
3272 x_ci_sequence_number igs_ca_inst.sequence_number%TYPE) IS
3273 SELECT award_year_status_code, sys_award_year
3274 FROM igf_ap_batch_aw_map
3275 WHERE ci_cal_type = x_ci_cal_type
3276 AND ci_sequence_number = x_ci_sequence_number;
3277 l_award_year_status_code igf_ap_batch_aw_map_all.award_year_status_code%TYPE;
3278 l_sys_award_year igf_ap_batch_aw_map_all.sys_award_year%TYPE;
3279 l_return_status_awd VARCHAR2(1);
3280
3281 PROCEDURE del_or_upd_int_records(p_operation IN VARCHAR2,
3282 ctx_li_awd_rec IN igf_aw_li_awd_ints%ROWTYPE,
3283 p_status IN VARCHAR2) IS
3284 BEGIN
3285
3286 IF(p_operation = 'U')THEN
3287 UPDATE igf_aw_li_awd_ints
3288 SET import_status_type = p_status,
3289 last_updated_by = fnd_global.user_id,
3290 last_update_date = SYSDATE,
3291 last_update_login = fnd_global.login_id,
3292 request_id = fnd_global.conc_request_id,
3293 program_id = fnd_global.conc_program_id,
3294 program_application_id = fnd_global.prog_appl_id,
3295 program_update_date = SYSDATE
3296 WHERE ci_alternate_code = ctx_li_awd_rec.ci_alternate_code
3297 AND person_number = ctx_li_awd_rec.person_number
3298 AND award_number_txt = ctx_li_awd_rec.award_number_txt
3299 AND import_status_type = ctx_li_awd_rec.import_status_type;
3300 ELSIF(p_operation = 'D') THEN
3301 DELETE FROM igf_aw_li_dact_ints
3302 WHERE ci_alternate_code = ctx_li_awd_rec.ci_alternate_code
3303 AND person_number = ctx_li_awd_rec.person_number
3304 AND award_number_txt = ctx_li_awd_rec.award_number_txt;
3305
3306 DELETE FROM igf_aw_li_hold_ints
3307 WHERE ci_alternate_code = ctx_li_awd_rec.ci_alternate_code
3308 AND person_number = ctx_li_awd_rec.person_number
3309 AND award_number_txt = ctx_li_awd_rec.award_number_txt;
3310
3311 DELETE FROM igf_aw_li_disb_ints
3312 WHERE ci_alternate_code = ctx_li_awd_rec.ci_alternate_code
3313 AND person_number = ctx_li_awd_rec.person_number
3314 AND award_number_txt = ctx_li_awd_rec.award_number_txt;
3315
3316 DELETE FROM igf_aw_li_awd_ints
3317 WHERE ci_alternate_code = ctx_li_awd_rec.ci_alternate_code
3318 AND person_number = ctx_li_awd_rec.person_number
3319 AND award_number_txt = ctx_li_awd_rec.award_number_txt
3320 AND import_status_type = ctx_li_awd_rec.import_status_type;
3321 END IF;
3322 EXCEPTION WHEN OTHERS THEN
3323 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
3324 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
3325 'igf.plsql.igf_aw_li_import.del_or_upd_int_records.exception',
3326 'Unhandled Exception'||SQLERRM);
3327 END IF;
3328 FND_MESSAGE.SET_NAME('IGF','IGF_GE_UNHANDLED_EXP');
3329 FND_MESSAGE.SET_TOKEN('NAME','DEL_OR_UPD_INT_RECORDS : '||SQLERRM );
3330 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
3331 END del_or_upd_int_records;
3332
3333
3334 BEGIN
3335 igf_aw_gen.set_org_id(NULL);
3336
3337 -- begin of procedure main
3338 -- Obtain cal_type and sequence number from the Parameter p_award_year
3339 g_debug_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3340 l_cal_type := TRIM(SUBSTR(p_award_year,1,10));
3341 l_seq_number := TO_NUMBER(SUBSTR(p_award_year,11));
3342
3343 IF(FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level)THEN
3344 g_debug_string := 'REQUEST ID: '||fnd_global.conc_request_id;
3345 END IF;
3346
3347 -- copy the values into global variables for further processing
3348 g_ci_cal_type := l_cal_type; g_ci_sequence_number := l_seq_number;
3349
3350 -- <1> validate if the school is configured for us financial aid functionallity
3351 IF(igf_ap_gen.check_profile = 'N') THEN
3352 FND_MESSAGE.SET_NAME('IGF','IGF_AP_LGCY_PROC_NOT_RUN');
3353 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
3354 retcode := 2;
3355 RETURN;
3356 END IF;
3357
3358 -- obtain the alternate code corresponding to the passed cal_type and seq_number
3359 OPEN cur_alt_code(l_cal_type,l_seq_number); FETCH cur_alt_code INTO l_alternate_code;CLOSE cur_alt_code;
3360
3361 -- Record the parameters in the log
3362 FND_FILE.PUT_LINE(FND_FILE.LOG,igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','PARAMETER_PASS'));
3363 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','AWARD_YEAR'),25) || ' : '|| l_alternate_code);
3364 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','BATCH_NUMBER'),25) || ' : '|| p_batch_number);
3365 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','DELETE_FLAG'),25) || ' : '||
3366 igf_ap_gen.get_lookup_meaning('IGF_AP_YES_NO',p_delete_flag));
3367 FND_FILE.PUT_LINE(fnd_file.log,RPAD('-',55,'-'));
3368
3369 l_var_cal_type := l_cal_type;
3370 l_var_seq_number := l_seq_number;
3371 l_ret_value := igf_ap_gen.validate_cal_inst('AWARD',l_alternate_code,NULL,l_var_cal_type,l_var_seq_number);
3372
3373 -- <2> validate the existence of award year in the calendar system
3374 -- sjalasut, is this validation required ?
3375 IF(l_ret_value = FALSE AND l_var_cal_type IS NULL AND l_var_seq_number IS NULL) THEN
3376 FND_MESSAGE.SET_NAME('IGF','IGF_AP_AWD_YR_NOT_FOUND');
3377 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
3378 retcode := 2;
3379 RETURN;
3380 END IF;
3381
3382 -- <3> validate if the award year entered has been setup in the system award year form
3383 -- and the status if found is either open or legacy details
3384 OPEN cur_in_award_year_form(l_cal_type,l_seq_number); FETCH cur_in_award_year_form INTO l_award_year_status_code, l_sys_award_year;
3385 IF(cur_in_award_year_form%NOTFOUND)THEN
3386 CLOSE cur_in_award_year_form;
3387 FND_MESSAGE.SET_NAME('IGF','IGF_AP_AWD_YR_NOT_FOUND');
3388 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
3389 retcode := 2;
3390 RETURN;
3391 ELSE -- award year is present in the sys award year form. check for validity
3392 CLOSE cur_in_award_year_form;
3393 -- copy the award year status code and sys award year values into global variables.for further processing.
3394 g_award_year_status_code := l_award_year_status_code; g_sys_award_year := l_sys_award_year;
3395 IF(FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level)THEN
3396 g_debug_string := g_debug_string || 'Award year status code '||g_award_year_status_code||'Sys Award Year'||g_sys_award_year;
3397 END IF;
3398 IF(l_award_year_status_code NOT IN ('O','LD')) THEN
3399 FND_MESSAGE.SET_NAME('IGF','IGF_AP_LG_INVALID_STAT');
3400 FND_MESSAGE.SET_TOKEN('AWARD_STATUS',l_award_year_status_code);
3401 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
3402 retcode := 2;
3403 RETURN;
3404 END IF;
3405 END IF;
3406
3407 -- award year status code that is printed before starting the transaction
3408 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','AWARD_YR_STATUS'),25)|| ' : '||
3409 igf_ap_gen.get_lookup_meaning('IGF_AWARD_YEAR_STATUS',g_award_year_status_code));
3410
3411 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD('-',55,'-'));
3412
3413 -- final minute validation on batch number. if the config changes and the interface tables are not updated then error
3414 IF(igf_ap_gen.check_batch(p_batch_number,'AWD') = 'N')THEN
3415 FND_MESSAGE.SET_NAME('IGF','IGF_GR_BATCH_DOES_NOT_EXIST');
3416 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
3417 RETURN;
3418 END IF;
3419
3420 --To check whether the profile value is set to a value of 'When student is awarded' or not
3421 fnd_profile.get('IGF_AW_LOCK_COA',lv_profile_value);
3422
3423 lv_base_id := NULL;
3424 g_print_msg := NULL;
3425
3426 -- <4> now that the initial setup validations are passed, process each record of the interface table.
3427 l_success_records :=0; l_warning_records :=0; l_rejected_records:=0;
3428 FOR cur_legacy_award_int_rec IN cur_legacy_award_int(l_alternate_code, trim(p_batch_number)) LOOP
3429 IF g_print_msg IS NOT NULL AND lv_base_id<>l_out_base_id THEN
3430 fnd_file.put_line(fnd_file.log,g_print_msg);
3431 g_print_msg := NULL;
3432 END IF;
3433 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
3434
3435 l_out_person_id := NULL; l_out_base_id := NULL; g_award_id := NULL;
3436 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
3437 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD('-',40,'-'));
3438 FND_FILE.PUT_LINE(FND_FILE.LOG,igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','PROCESSING')||' '||
3439 igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','PERSON_NUMBER')||' '||cur_legacy_award_int_rec.person_number);
3440 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD('-',40,'-'));
3441 igf_ap_gen.check_person(cur_legacy_award_int_rec.person_number,
3442 l_cal_type,
3443 l_seq_number,
3444 l_out_person_id,
3445 l_out_base_id
3446 );
3447
3448 -- person id is null. update the interface record with status E
3449 IF(l_out_person_id IS NULL) THEN
3450 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_PERSON_NOT_FND');
3451 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
3452 l_rejected_records := l_rejected_records + 1;
3453 del_or_upd_int_records('U',cur_legacy_award_int_rec,'E');
3454 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_SKIPPING_AWD');
3455 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
3456 ELSE
3457 g_person_id := l_out_person_id;
3458 END IF;
3459
3460 -- base id is null but the person id is not null. error out updating the interface record with status E
3461 IF(l_out_base_id IS NULL AND l_out_person_id IS NOT NULL) THEN
3462 FND_MESSAGE.SET_NAME('IGF','IGF_AP_FABASE_NOT_FOUND');
3463 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
3464 l_rejected_records := l_rejected_records + 1;
3465 del_or_upd_int_records('U',cur_legacy_award_int_rec,'E');
3466 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_SKIPPING_AWD');
3467 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
3468 ELSIF(l_out_base_id IS NOT NULL AND l_out_person_id IS NOT NULL)THEN -- here both person id and base id are found
3469 -- write to the log and clear the string so that it can be used for other procedures being called.
3470 IF(FND_LOG.LEVEL_STATEMENT >= g_debug_runtime_level)THEN
3471 g_debug_string := g_debug_string || 'Base Id'||l_out_base_id||'Person Id'||l_out_person_id;
3472 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_aw_li_import.main.debug',g_debug_string);
3473 g_debug_string := NULL;
3474 END IF;
3475 -- call to validate award year interface record. this is the main fork. control goes around the
3476 -- world before comming back here.
3477
3478 validate_awdyear_int_rec(cur_legacy_award_int_rec, l_return_status_awd);
3479
3480 -- do some action based on the l_return_status_awd here.
3481 IF(l_return_status_awd IN ('S','W'))THEN
3482 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_IMP_SUCCESS');
3483 FND_MESSAGE.SET_TOKEN('AWARD_ID',g_award_id);
3484 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
3485 END IF;
3486 -- The Records did not error out so if delete flag is set, delete interface records
3487 IF(l_return_status_awd <> 'E' AND p_delete_flag ='Y')THEN
3488 del_or_upd_int_records('D',cur_legacy_award_int_rec,NULL); -- NULL here does not matter as the op is delete
3489 END IF;
3490 IF(l_return_status_awd = 'S') THEN
3491 l_success_records := l_success_records + 1;
3492 -- processing of award and its child records is successful. but the delete flag is not set
3493 IF(p_delete_flag = 'N')THEN
3494 del_or_upd_int_records('U',cur_legacy_award_int_rec,'I');
3495 END IF;
3496 ELSIF(l_return_status_awd = 'W')THEN
3497 l_warning_records := l_warning_records + 1;
3498 IF(p_delete_flag = 'N')THEN
3499 del_or_upd_int_records('U',cur_legacy_award_int_rec,'W');
3500 END IF;
3501 ELSIF(l_return_status_awd NOT IN ('S','W')) THEN
3502 l_rejected_records := l_rejected_records + 1;
3503 del_or_upd_int_records('U',cur_legacy_award_int_rec,'E');
3504 FND_MESSAGE.SET_NAME('IGF','IGF_AW_LI_SKIPPING_AWD');
3505 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
3506 END IF;
3507
3508 -- IF lv_profile_value = 'AWARDED' lock COA at the student level
3509 IF lv_profile_value = 'AWARDED' THEN
3510 IF (l_return_status_awd = 'S') OR (l_return_status_awd = 'W') THEN
3511 lock_std_coa(l_out_base_id);
3512 lv_base_id := l_out_base_id;
3513 END IF;
3514 END IF;
3515
3516 END IF; -- end if of if(l_out_base_id is null AND l_out_person_id IS NOT NULL)
3517 COMMIT;
3518 END LOOP;
3519
3520 -- print message if it is locked at the student level
3521 IF g_print_msg IS NOT NULL THEN
3522 fnd_file.put_line(fnd_file.log,g_print_msg);
3523 END IF;
3524
3525 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
3526 l_total_records := l_success_records + l_warning_records + l_rejected_records;
3527 -- if no records are available, then write no records to be processed in the log file and write the
3528 -- summary in the output file. else write the summary in both log and output files.
3529 IF(l_total_records = 0)THEN
3530 FND_MESSAGE.SET_NAME('IGS','IGS_FI_NO_RECORD_AVAILABLE');
3531 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
3532 -- write the summary into the out files.
3533 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-',55,'-'));
3534 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','RECORDS_PROCESSED'),40)||' : '||l_total_records);
3535 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','RECORDS_SUCCESSFUL'),40)||' : '||l_success_records);
3536 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','RECORDS_WARN'),40)||' : '||l_warning_records);
3537 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','RECORDS_REJECTED'),40)||' : '||l_rejected_records);
3538 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-',55,'-'));
3539 ELSE
3540 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
3541 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','RECORDS_PROCESSED'),40)||' : '||l_total_records);
3542 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','RECORDS_SUCCESSFUL'),40)||' : '||l_success_records);
3543 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','RECORDS_WARN'),40)||' : '||l_warning_records);
3544 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','RECORDS_REJECTED'),40)||' : '||l_rejected_records);
3545 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
3546
3547 -- write the summary into the out files.
3548 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-',55,'-'));
3549 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','RECORDS_PROCESSED'),40)||' : '||l_total_records);
3550 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','RECORDS_SUCCESSFUL'),40)||' : '||l_success_records);
3551 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','RECORDS_WARN'),40)||' : '||l_warning_records);
3552 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','RECORDS_REJECTED'),40)||' : '||l_rejected_records);
3553 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-',55,'-'));
3554 END IF;
3555 EXCEPTION WHEN OTHERS THEN
3556 ROLLBACK;
3557 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
3558 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'igf.plsql.igf_aw_li_import.main.exception',
3559 g_debug_string );
3560 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'igf.plsql.igf_aw_li_import.main.exception',
3561 'Unhandled Exception '||SQLERRM );
3562 END IF;
3563 FND_MESSAGE.SET_NAME('IGF','IGF_GE_UNHANDLED_EXP');
3564 FND_MESSAGE.SET_TOKEN('NAME','MAIN : '||SQLERRM);
3565 IGS_GE_MSG_STACK.ADD;
3566 retcode := 2;
3567 errbuf:= FND_MESSAGE.GET;
3568 FND_FILE.PUT_LINE(fnd_file.log,errbuf);
3569 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
3570 END main;
3571
3572 -------------------------------------------------------------------------------------------------------
3573 -- Generic Functions
3574 -------------------------------------------------------------------------------------------------------
3575 FUNCTION get_base_id_from_per_num (p_person_number hz_parties.party_number%TYPE,
3576 p_cal_type igs_ca_inst.cal_type%TYPE,
3577 p_sequence_number igs_ca_inst.sequence_number%TYPE
3578 ) RETURN NUMBER IS
3579 CURSOR cur_base_id IS
3580 SELECT base.base_id
3581 FROM igf_ap_fa_base_rec_all base,
3582 hz_parties hp
3583 WHERE
3584 base.ci_sequence_number = p_sequence_number
3585 AND base.ci_cal_type = p_cal_type
3586 AND base.person_id = hp.party_id
3587 AND hp.party_number = p_person_number;
3588
3589 l_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
3590
3591 BEGIN
3592 IF(p_person_number IS NOT NULL AND p_cal_type IS NOT NULL AND p_sequence_number IS NOT NULL)THEN
3593 OPEN cur_base_id; FETCH cur_base_id INTO l_base_id; CLOSE cur_base_id;
3594 IF l_base_id IS NOT NULL THEN
3595 RETURN l_base_id;
3596 ELSE
3597 RETURN -1;
3598 END IF;
3599 END IF;
3600 END get_base_id_from_per_num ;
3601
3602 PROCEDURE run( errbuf OUT NOCOPY VARCHAR2,
3603 retcode OUT NOCOPY NUMBER,
3604 p_award_year IN VARCHAR2,
3605 p_batch_number IN NUMBER,
3606 p_delete_flag IN VARCHAR2
3607 )
3608 IS
3609 /***************************************************************
3610 Created By : nsidana
3611 Date Created By : 11/28/2003
3612 Purpose :
3613 Known Limitations,Enhancements or Remarks
3614 Change History :
3615 Who When What
3616
3617 ***************************************************************/
3618
3619 lv_errbuf VARCHAR2(4000);
3620 ln_retcode NUMBER;
3621 BEGIN
3622 errbuf := NULL;
3623 retcode := 0;
3624
3625 --
3626 -- Assign global variable
3627 --
3628 g_entry_point := 'EXTERNAL';
3629
3630 --
3631 --Invoke the main routine
3632 --
3633 main(lv_errbuf,
3634 ln_retcode,
3635 p_award_year,
3636 p_batch_number,
3637 p_delete_flag);
3638
3639 EXCEPTION WHEN OTHERS THEN
3640 ROLLBACK;
3641 IF(FND_LOG.LEVEL_EXCEPTION >= g_debug_runtime_level)THEN
3642 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'igf.plsql.igf_aw_li_import.run.exception',
3643 g_debug_string );
3644 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'igf.plsql.igf_aw_li_import.run.exception',
3645 'Unhandled Exception '||SQLERRM );
3646 END IF;
3647 FND_MESSAGE.SET_NAME('IGF','IGF_GE_UNHANDLED_EXP');
3648 FND_MESSAGE.SET_TOKEN('NAME','RUN : '||SQLERRM);
3649 IGS_GE_MSG_STACK.ADD;
3650 retcode := 2;
3651 errbuf:= FND_MESSAGE.GET;
3652 FND_FILE.PUT_LINE(fnd_file.log,errbuf);
3653 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
3654
3655 END run;
3656
3657 END igf_aw_li_import;
[Home] [Help]