[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_update_mode_required BOOLEAN := FALSE;
Purpose : To Check if the Column value is NULL and then insert a record
into the Edit Report Table specifying that the Column Value
is Recommended or Strongly Recommended.
Known Limitations,Enhancements or Remarks
Change History :
Who When What
***************************************************************/
lv_complete BOOLEAN := TRUE;
SELECT DISTINCT status from igf_sl_reqd_fields lrf
WHERE lrf.loan_type = p_loan_catg
AND lrf.field_name = p_field_name
AND lrf.spec_version = p_cl_version
-- AND lrf.transaction_type = '@1'
AND lrf.transaction_type IN ('@1','@4') -- To Include @4 Record for borrower and Cosigner details
AND lrf.prc_type_code = p_prc_type_code;
igf_sl_edit.insert_edit(p_loan_number, 'V', lv_err_type, lv_data_reqd, p_field_name, p_field_value);
igf_sl_edit.insert_edit(p_loan_number, 'V', lv_err_type, lv_data_recomm, p_field_name, p_field_value);
changed cursor cur_lor_details_recs not to select student/borrower information.
This is derived from igf_sl_gen.get_person_details
Bug No: 2400487 Desc : FFELP Validation and Origination Issues.
Who When What
rajagupt 10-Apr-2006 Bug 5006587 . Removed validations (set_complete_status()) for the following field names
For PLUS -> S_DEFAULT_STATUS,BORW_INTEREST_IND,P_STATE_OF_LEGAL_RES,S_SIGNATURE_CODE,BORW_OUTSTD_LOAN_CODE
museshad 05-Oct-2005 Bug 4116399.
Added Stafford loan limit validation.
bvisvana 25-Aug-2005 Bug 4127532. Removed the Validation of checking default status of borrower and student.
For ALT loans, the loan should be sent irrespective of the default status of the borrower or the student.
museshad 11-Aug-2005 Bug 4103922.
Disbursement Hold/Release indicator validation was incorrect.
The variable 'ln_disb_amt_count' was used in the place of
'ln_disb_ind_count'. Corrected this.
smadathi 29-oct-2004 Bug 3416936. Added new business logic as part of
CL4 changes
ridas 17-SEP-04 Bug #3691137: Query optimized by using the table igf_sl_cl_recipient
instead of the view igf_sl_cl_recipient_v
bkkumar 10-apr-04 FACR116 - Added the validation for ALT_LOAN_CODE
mesriniv 8-jun-2002 Commented Check for P_SIGNATURE_CODE for CL_STAFFORD
Commented Check for LOAN_SEQ_NUMBER for all types of Loans
Added Check for S_SIGNATURE_CODE for CL_STAFFORD.
Bug Id : 1769051 - Developement for Nov 2001 release
Who When What
avenkatr 09-MAY-2001 1. Added validations for 'Alternate' Loans
Bug Id : 1720677 Desc : Mapping of school id in the CommonLine Setup
to ope_id of FinancialAid Office Setup.
Who When What
mesriniv 05-APR-2001 Changed the occurrences of field fao_id
to ope_id
***************************************************************/
lv_loan_status igf_sl_loans_all.loan_status%TYPE;
SELECT loans.row_id,
loans.loan_id,
loans.loan_number,
loans.award_id,
awd.accepted_amt loan_amt_accepted,
lor.requested_loan_amt,
loans.loan_per_begin_date,
loans.loan_per_end_date,
lor.orig_fee_perct,
lor.pnote_print_ind,
lor.s_default_status,
lor.p_default_status,
lor.p_person_id,
lor.sch_cert_date,
lor.prc_type_code,
lor.anticip_compl_date,
lor.cl_loan_type,
lor.borw_interest_ind,
lor.grade_level_code,
lor.enrollment_code,
lor.req_serial_loan_code,
lor.pnote_delivery_code,
lor.s_signature_code,
lor.p_signature_code,
lor.borw_outstd_loan_code,
lor.cl_seq_number,
lor.rec_type_ind,
lor.p_signature_date,
lor.borr_sign_ind,
lor.stud_sign_ind,
lor.borr_credit_auth_code,
lor.origination_id,
lor.relationship_cd,
lor.eft_authorization_code,
lor.b_alien_reg_num_txt, -- FA 161 - Bug # 5006583
lor.deferment_request_code,
lor.loan_app_form_code,
recip.lender_id,
recip.guarantor_id,
recip.lend_non_ed_brc_id,
recip.lender_id borw_lender_id,
fabase.base_id,
fabase.person_id student_id,
awd.accepted_amt,
fcat.fed_fund_code,
fcat.alt_loan_code,
TRUNC(fabase.coa_f) coa_f,
loans.external_loan_id_txt
FROM igf_sl_loans loans,
igf_sl_lor lor,
igf_aw_award awd,
igf_aw_fund_mast fmast,
igf_aw_fund_cat fcat,
igf_ap_fa_base_rec fabase,
igf_sl_cl_recipient recip
WHERE fabase.ci_cal_type = p_cal_type
AND fabase.ci_sequence_number = p_seq_number
AND fabase.base_id = awd.base_id
AND fabase.base_id = NVL(p_base_id, fabase.base_id)
AND awd.fund_id = fmast.fund_id
AND fmast.fund_code = fcat.fund_code
AND (fcat.fed_fund_code = p_fed_fund_1 OR fcat.fed_fund_code = p_fed_fund_2)
AND loans.award_id = awd.award_id
AND loans.loan_number = NVL(p_loan_number,loans.loan_number)
AND loans.loan_id = lor.loan_id
AND (loans.loan_status = p_status_1 OR loans.loan_status = p_status_2 OR loans.loan_status = p_status_3)
AND loans.active = p_active
AND lor.relationship_cd = recip.relationship_cd
AND substr(loans.loan_number, 1, 6) = NVL(substr(p_school_id, 1, 6), substr(loans.loan_number, 1, 6));
SELECT isir.dependency_status
FROM igf_ap_isir_matched isir
WHERE isir.payment_isir = 'Y'
AND isir.system_record_type = 'ORIGINAL'
AND isir.base_id=lor_rec_temp.base_id;
SELECT COUNT(award_id)
FROM igf_aw_awd_disb awdb
WHERE award_id = lor_rec_temp.award_id
AND disb_accepted_amt <= 0;
SELECT COUNT(award_id)
FROM igf_aw_awd_disb awdb
WHERE award_id=lor_rec_temp.award_id
AND hold_rel_ind IS NULL;
SELECT COUNT(award_id)
FROM igf_aw_awd_disb awdb
WHERE award_id=lor_rec_temp.award_id
AND disb_date IS NULL;
SELECT COUNT(awd.disb_num) tot_disb
FROM igf_aw_awd_disb_all awd
WHERE awd.award_id = cp_n_award_id
GROUP BY awd.award_id
HAVING COUNT(awd.disb_num) > 4;
SELECT
pct.restatus_code
FROM igs_pe_eit_restatus_v pct
WHERE pct.person_id = cp_person_id AND
SYSDATE BETWEEN start_date AND NVL(end_date,SYSDATE);
SELECT trim(fa_citi_stat_code) fa_citi_stat_code FROM igf_sl_pe_citi_map
WHERE pe_citi_stat_code = cp_citizenship_status
AND ci_sequence_number = cp_sequence_number
AND ci_cal_type = cp_cal_type;
agairola 15-Mar-2002 Modified the IGF_SL_LOANS_PKG update row call
as part of Refunds DLD
museshad 05-May-2005 Bug# 4346258
Added the parameter 'base_id' in the call to the
function get_cl_version(). The signature of
this function has been changed so that it takes
into account any overriding CL version for a
specific Organization Unit in FFELP Setup override.
***************************************************************/
BEGIN
IF p_complete = FALSE THEN
lv_complete := FALSE;
SELECT NVL (SUM (NVL (disb.disb_gross_amt, 0)), 0) resource_f,
NVL (SUM (DECODE (fm.replace_fc,
'Y', NVL (disb.disb_gross_amt, 0),
0
)),
0) resource_fm_f
FROM igf_aw_awd_disb_all disb,
igf_aw_award_all awd,
igf_aw_fund_mast_all fm,
(SELECT base_id,
ld_cal_type,
ld_sequence_number
FROM igf_aw_coa_itm_terms
WHERE base_id = p_base_id
GROUP BY base_id, ld_cal_type, ld_sequence_number) coa
WHERE awd.fund_id = fm.fund_id
AND awd.award_id = disb.award_id
AND awd.base_id = p_base_id
AND disb.ld_cal_type = coa.ld_cal_type
AND disb.ld_sequence_number = coa.ld_sequence_number
AND awd.base_id = coa.base_id
AND disb.trans_type <> 'C'
AND awd.award_status IN ('OFFERED', 'ACCEPTED');
SELECT
borw.borw_gross_annual_sal borw_gross_annual_sal,
borw.borw_other_income borw_other_income,
borw.cs1_gross_annual_sal_num csgnr1_gross_annual_sal,
borw.cs1_other_income_amt csgnr1_other_income,
borw.cs2_gross_annual_sal_num csgnr2_gross_annual_sal,
borw.cs2_other_income_amt csgnr2_other_income,
borw.student_major student_major,
lor.prc_type_code prc_type_code,
loans.loan_number loan_number
FROM
igf_sl_loans loans,
igf_sl_lor lor,
igf_sl_alt_borw borw
WHERE
loans.loan_number = p_loan_number AND
lor.prc_type_code = p_prc_type_code AND
loans.loan_id = lor.loan_id AND
lor.loan_id = borw.loan_id;
SELECT adplans_id
FROM igf_aw_award_all
WHERE award_id = cp_award_id;
igf_sl_edit.delete_edit(lor_rec.loan_number,'V');
igf_sl_edit.insert_edit(lor_rec.loan_number,'V','IGF_SL_ERR_CODES','EXT_LOAN_NUM_CHK',NULL,NULL);
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '078', 'SCH_CERT_DATE',
lor_rec.sch_cert_date);
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '046', 'FILE_TRANS_DATE', NULL);
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '053', 'ANTICIP_COMPL_DATE',
lor_rec.anticip_compl_date);
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '405', 'FED_APPL_FORM_CODE', NULL);
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '502', 'DEFER_REQ_CODE', NULL);
g_update_mode_required := TRUE;
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'IS_DEFAULTER', 'S_DEFAULT_STATUS', NULL);
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '034', 'S_SSN', NULL);
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '405', 'FED_APPL_FORM_CODE', NULL);
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '502', 'DEFER_REQ_CODE', NULL);
g_update_mode_required := TRUE;
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '502', 'BORW_INTEREST_IND', NULL);
g_update_mode_required := TRUE;
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '502', 'P_DEFAULT_STATUS', NULL);
g_update_mode_required := TRUE;
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '502', 'S_DEFAULT_STATUS', NULL);
g_update_mode_required := TRUE;
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '502', 'BORW_OUTSTD_LOAN_CODE', NULL);
g_update_mode_required := TRUE;
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '502', 'S_SIGNATURE_CODE', NULL);
g_update_mode_required := TRUE;
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '502', 'S_ESIGN_IND_CODE', NULL);
g_update_mode_required := TRUE;
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'NO_FA_CITI_MAP_CD', 'S_CITIZENSHIP_STATUS', NULL);
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'NO_FA_CITI_MAP_CD', 'P_CITIZENSHIP_STATUS', NULL);
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '034', 'S_SSN', NULL);
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '001', 'P_SSN', NULL);
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'NO_FA_CITI_MAP_CD', 'S_CITIZENSHIP_STATUS', NULL);
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '016', 'P_CITIZENSHIP_STATUS', NULL);
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'NO_FA_CITI_MAP_CD', 'P_CITIZENSHIP_STATUS', NULL);
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '016', 'P_CITIZENSHIP_STATUS', NULL);
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '405', 'FED_APPL_FORM_CODE', NULL);
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '034', 'S_SSN', NULL);
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '001', 'P_SSN', NULL);
igf_sl_edit.insert_edit(lor_rec.loan_number,'V','IGF_SL_ERR_CODES','FOUR_DISB_CHK',NULL,NULL);
igf_sl_edit.insert_edit(lor_rec.loan_number,'V','IGF_SL_ERR_CODES','COA_EFC_CHK',NULL,NULL);
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'DEPNCY_NO_EFC_CALC', 'S_DEPNCY_STATUS', NULL);
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'LESS_THAN_ZERO', 'LOAN_AMT_ACCEPTED', NULL);
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'DISB_MORE_THAN_ZERO', NULL, NULL);
igf_sl_edit.insert_edit(lor_rec.loan_number,'V','IGF_SL_ERR_CODES','HOLD_REL_NOT_NULL',NULL,NULL);
igf_sl_edit.insert_edit(lor_rec.loan_number,'V','IGF_SL_ERR_CODES','DISB_DATE_NOT_NULL',NULL,NULL);
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '014', 'REQUESTED_LOAN_AMT', NULL);
igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', lv_lookup_code, NULL, NULL);
SELECT igf_sl_loans.* FROM igf_sl_loans
WHERE loan_id = lor_rec.loan_id FOR UPDATE OF igf_sl_loans.loan_status NOWAIT;
igf_sl_loans_pkg.update_row (
X_Mode => 'R',
x_rowid => tbh_rec.row_id,
x_loan_id => tbh_rec.loan_id,
x_award_id => tbh_rec.award_id,
x_seq_num => tbh_rec.seq_num,
x_loan_number => tbh_rec.loan_number,
x_loan_per_begin_date => tbh_rec.loan_per_begin_date,
x_loan_per_end_date => tbh_rec.loan_per_end_date,
x_loan_status => lv_loan_status,
x_loan_status_date => TRUNC(SYSDATE),
x_loan_chg_status => tbh_rec.loan_chg_status,
x_loan_chg_status_date => tbh_rec.loan_chg_status_date,
x_active => tbh_rec.active,
x_active_date => tbh_rec.active_date,
x_borw_detrm_code => tbh_rec.borw_detrm_code,
x_legacy_record_flag => NULL,
x_external_loan_id_txt => tbh_rec.external_loan_id_txt
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_validation.cl_lar_validate.debug', 'Updated tbh_rec.loan_id:' || tbh_rec.loan_id);
SELECT RPAD(field_desc,70)||sl_error_desc reject_desc FROM igf_sl_edit_report_v
WHERE loan_number = lor_rec.loan_number
AND orig_chg_code = 'V';
IF g_update_mode_required THEN
fnd_file.new_line(fnd_file.log,2);