[Home] [Help]
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:
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
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
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;
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;
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: --
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: --
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))
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: --
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: --
594:
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
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
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
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
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
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
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:
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;
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:
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
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);
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:
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: /*
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
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;
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:
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);
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;
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
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';
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;
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);
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;
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';
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:
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
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: /***************************************************************
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;
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';
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)
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
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
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:
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:
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;
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
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;
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:
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:
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
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.
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
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:
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
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
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
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:
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
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;
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:
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
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;
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;
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:
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
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:
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
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;
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';
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');
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
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';
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
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
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';
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');
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;
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:
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
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
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
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;
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
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);
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';
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;
1526: --
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
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
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:
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
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;
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:
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:
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,
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';
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');
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:
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;
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;
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:
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:
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:
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
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.
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
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:
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
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;
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:
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
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';
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
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
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:
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
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
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.
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
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;
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:
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;
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
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
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.
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');
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);
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
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
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;
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:
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
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;
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;
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:
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:
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
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');
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);
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');
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);
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:
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)
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: --
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
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: --
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: --
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
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;
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;
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:
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:
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: --
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
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
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;
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';
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');
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
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
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
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
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:
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
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';
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');
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';
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');
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
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;
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
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';
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');
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;
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;
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:
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
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;
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:
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
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
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;
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;
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:
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
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;
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
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';
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');
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;
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
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';
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');
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;
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;
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:
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
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;
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:
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
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);
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;
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
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';
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');
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
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) -
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;
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
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';
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');
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;
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
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';
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');
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;
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:
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
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) -
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:
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,
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,
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
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;
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;
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;
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:
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
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:
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;
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
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';
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');
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;
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
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
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
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';
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;
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:
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;
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
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
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
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
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:
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
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
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
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
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
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
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
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;
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:
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
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
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;
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:
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
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
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;
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:
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
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
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;
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:
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:
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
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:
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
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.
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
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';
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');
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');
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:
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
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';
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';
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');
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
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
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';
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:
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;
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
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';
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');
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;
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;
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:
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
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:
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
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:
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,
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,
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;
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
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
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;
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
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:
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:
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,
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:
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:
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
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;
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:
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;
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:
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
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;
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;
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;
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;
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:
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
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');
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);
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;
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:
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');
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);
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
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;
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
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
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
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
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
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);
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);
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;
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);
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:
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;
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);
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;