[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
rcpt.recipient_id recipient_id,
rcpt.recipient_type recipient_type,
lnd.description recip_description,
rcpt.recip_non_ed_brc_id recip_non_ed_brc_id
FROM
igf_sl_cl_recipient rcpt,
igf_sl_lender lnd
WHERE
rcpt.recipient_id = lnd.lender_id AND
rcpt.recipient_type = 'LND' AND
rcpt.relationship_cd = p_relationship_cd
UNION ALL
SELECT
rcpt.recipient_id recipient_id,
rcpt.recipient_type recipient_type,
guarn.description recip_description,
rcpt.recip_non_ed_brc_id recip_non_ed_brc_id
FROM
igf_sl_cl_recipient rcpt,
igf_sl_guarantor guarn
WHERE
rcpt.recipient_id = guarn.guarantor_id AND
rcpt.recipient_type = 'GUARN' AND
rcpt.relationship_cd = p_relationship_cd
UNION ALL
SELECT
rcpt.recipient_id recipient_id,
rcpt.recipient_type recipient_type,
srvc.description recip_description,
rcpt.recip_non_ed_brc_id recip_non_ed_brc_id
FROM
igf_sl_cl_recipient rcpt,
igf_sl_servicer srvc
WHERE
rcpt.recipient_id = srvc.servicer_id AND
rcpt.recipient_type = 'SRVC' AND
rcpt.relationship_cd = p_relationship_cd;
SELECT loans.ROWID row_id,
loans.loan_id,
loans.loan_number,
loans.award_id,
awd.offered_amt loan_amt_offered,
awd.accepted_amt loan_amt_accepted,
loans.loan_per_begin_date,
loans.loan_per_end_date,
loans.loan_status,
loans.loan_status_date,
loans.loan_chg_status,
loans.loan_chg_status_date,
loans.active,
loans.active_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.act_serial_loan_code,
lor.orig_status_flag,
lor.orig_batch_id,
lor.orig_batch_date,
lor.orig_ack_date,
lor.credit_override,
lor.credit_decision_date,
lor.pnote_status,
lor.pnote_status_date,
lor.pnote_id,
lor.pnote_accept_amt,
lor.pnote_accept_date,
lor.borw_confirm_ind,
lor.unsub_elig_for_heal,
lor.disclosure_print_ind,
lor.unsub_elig_for_depnt,
lor.guarantee_amt,
lor.guarantee_date,
lor.guarnt_adj_ind,
lor.guarnt_amt_redn_code,
lor.guarnt_status_code,
lor.guarnt_status_date,
lor.lend_status_code,
lor.lend_status_date,
lor.last_resort_lender,
lor.alt_prog_type_code,
lor.alt_appl_ver_code,
lor.resp_to_orig_code,
lor.tot_outstd_stafford,
lor.tot_outstd_plus,
lor.alt_borw_tot_debt,
lor.act_interest_rate,
lor.service_type_code,
lor.rev_notice_of_guarnt,
lor.sch_refund_amt,
lor.sch_refund_date,
lor.uniq_layout_vend_code,
lor.uniq_layout_ident_code,
lor.pnote_batch_id,
lor.pnote_ack_date,
lor.pnote_mpn_ind,
recip.lender_id,
recip.guarantor_id,
recip.recipient_id,
recip.lend_non_ed_brc_id,
recip.recip_non_ed_brc_id,
recip.recipient_type,
fabase.base_id,
fabase.person_id student_id,
awd.accepted_amt,
fcat.fed_fund_code,
fabase.ci_cal_type,
fabase.ci_sequence_number,
fcat.alt_rel_code,
fcat.alt_loan_code,
lor.note_message,
lor.book_loan_amt_date,
lor.book_loan_amt,
lor.pymt_servicer_date,
lor.pymt_servicer_amt,
lor.rep_entity_id_txt,
lor.cps_trans_num,
lor.atd_entity_id_txt,
lor.s_dob_chg_date,
lor.p_dob_chg_date,
lor.crdt_decision_status,
lor.interest_rebate_percent_num,
lor.external_loan_id_txt,
lor.deferment_request_code,
lor.eft_authorization_code,
lor.requested_loan_amt,
lor.actual_record_type_code,
lor.reinstatement_amt,
lor.school_use_txt,
lor.lender_use_txt,
lor.guarantor_use_txt,
lor.fls_approved_amt,
lor.flu_approved_amt,
lor.flp_approved_amt,
lor.alt_approved_amt,
lor.loan_app_form_code,
lor.override_grade_level_code,
recip.relationship_cd,
lor.b_alien_reg_num_txt, -- fa 161 - bug # 5006583
lor.esign_src_typ_cd
FROM igf_sl_loans_all loans,
igf_sl_lor_all lor,
igf_aw_award_all awd,
igf_aw_fund_mast_all fmast,
igf_aw_fund_cat_all fcat,
igf_ap_fa_base_rec_all 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_loan_status
AND loans.active = p_active
AND SUBSTR(loans.loan_number,1,6) = SUBSTR(p_school_id,1,6)
AND lor.relationship_cd = recip.relationship_cd
AND lor.relationship_cd = NVL (p_relationship_cd, lor.relationship_cd);
2. Changed the cursor cur_recip_dts to select only those
records related to p_school_id, and only those related
to p_base_id if it is not null.
mesriniv 23-APR-2002 Added code to display the Parameters Passed
***************************************************************************************/
lv_ci_cal_type igs_ca_inst.cal_type%TYPE;
SELECT DISTINCT lor.relationship_cd
FROM igf_ap_fa_base_rec fabase,
igf_sl_loans lar,
igf_sl_lor lor,
igf_aw_award awd,
igf_aw_fund_mast fund,
igf_aw_fund_cat fcat,
igf_sl_cl_recipient recip
WHERE lar.loan_id = lor.loan_id
AND lar.award_id = awd.award_id
AND awd.base_id = fabase.base_id
AND awd.base_id = NVL(p_base_id, awd.base_id)
AND awd.fund_id = fund.fund_id
AND fund.fund_code = fcat.fund_code
AND fabase.ci_cal_type = lv_ci_cal_type
AND fabase.ci_sequence_number = lv_ci_sequence_number
AND lar.loan_status = p_loan_status
AND lar.active = p_active
AND recip.recipient_id = NVL(p_recipient_id,recip.recipient_id)
AND lor.relationship_cd = recip.relationship_cd
AND lar.loan_number LIKE DECODE(p_loan_number,NULL,'%',p_loan_number)
AND substr(lar.loan_number, 1, 6) = substr(p_school_id,1,6)
AND fcat.fed_fund_code IN ( SELECT DISTINCT lookup_code
FROM igf_lookups_view
WHERE lookup_type = p_lookup_type
AND enabled_flag = p_enabled_flag)
ORDER BY
lor.relationship_cd;
SELECT meaning
FROM igf_lookups_view
WHERE lookup_type = 'IGF_SL_CL_LOAN_CATG'
AND lookup_code = p_loan_catg
AND enabled_flag = 'Y'
UNION ALL
SELECT meaning
FROM igf_lookups_view
WHERE lookup_type = 'IGF_GE_PARAMETERS'
AND lookup_code IN ('AWARD_YEAR','LOAN_CATG','LOAN_ID','PARAMETER_PASS')
AND enabled_flag = 'Y';
SELECT ca.alternate_code
FROM igs_ca_inst ca
WHERE ca.cal_type = lv_ci_cal_type
AND ca.sequence_number = lv_ci_sequence_number;
PROCEDURE insert_lor_loc_rec (
p_v_school_id IN VARCHAR2,
p_n_coa IN igf_ap_fa_base_rec_all.coa_f%TYPE,
p_n_efc IN igf_ap_fa_base_rec_all.efc_f%TYPE,
p_n_est_fin IN igf_aw_award_all.accepted_amt%TYPE,
p_c_alt_borr_ind_flag IN VARCHAR2
) AS
/***************************************************************
Created By : mesriniv
Date Created By : 2000/11/17
Purpose : To insert transaction records into the igf_sl_lor_loc table
Known Limitations,Enhancements or Remarks
Change History :
Who When What
veramach 23-SEP-2003 Bug 3104228:
1. Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
chg_batch_id,appl_send_error_codes from igf_sl_lor
2. Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
chg_batch_id from igf_sl_lor_loc
***************************************************************/
lv_row_id ROWID;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.insert_lor_loc_rec.debug', 'Before inserting into igf_sl_lor_loc' );
igf_sl_lor_loc_pkg.insert_row (
x_mode => 'R',
x_rowid => lv_row_id,
x_loan_id => loan_rec.loan_id,
x_origination_id => loan_rec.origination_id,
x_loan_number => loan_rec.loan_number,
x_loan_type => loan_rec.cl_loan_type,
x_loan_amt_offered => loan_rec.loan_amt_offered,
x_loan_amt_accepted => loan_rec.loan_amt_accepted,
x_loan_per_begin_date => loan_rec.loan_per_begin_date,
x_loan_per_end_date => loan_rec.loan_per_end_date,
x_acad_yr_begin_date => NULL , --ld_acad_yr_begin_date,
x_acad_yr_end_date => NULL , --ld_acad_yr_end_date,
x_loan_status => loan_rec.loan_status,
x_loan_status_date => loan_rec.loan_status_date,
x_loan_chg_status => loan_rec.loan_chg_status,
x_loan_chg_status_date => loan_rec.loan_chg_status_date,
x_req_serial_loan_code => loan_rec.req_serial_loan_code,
x_act_serial_loan_code => loan_rec.act_serial_loan_code,
x_active => loan_rec.active,
x_active_date => loan_rec.active_date,
x_sch_cert_date => loan_rec.sch_cert_date,
x_orig_status_flag => loan_rec.orig_status_flag,
x_orig_batch_id => loan_rec.orig_batch_id,
x_orig_batch_date => loan_rec.orig_batch_date,
x_chg_batch_id => NULL,
x_orig_ack_date => loan_rec.orig_ack_date,
x_credit_override => loan_rec.credit_override,
x_credit_decision_date => loan_rec.credit_decision_date,
x_pnote_delivery_code => loan_rec.pnote_delivery_code,
x_pnote_status => loan_rec.pnote_status ,
x_pnote_status_date => loan_rec.pnote_status_date,
x_pnote_id => loan_rec.pnote_id,
x_pnote_print_ind => loan_rec.pnote_print_ind,
x_pnote_accept_amt => loan_rec.pnote_accept_amt,
x_pnote_accept_date => loan_rec.pnote_accept_date,
x_p_signature_code => loan_rec.p_signature_code,
x_p_signature_date => loan_rec.p_signature_date,
x_s_signature_code => loan_rec.s_signature_code,
x_unsub_elig_for_heal => loan_rec.unsub_elig_for_heal,
x_disclosure_print_ind => loan_rec.disclosure_print_ind,
x_orig_fee_perct => loan_rec.orig_fee_perct,
x_borw_confirm_ind => loan_rec.borw_confirm_ind,
x_borw_interest_ind => loan_rec.borw_interest_ind,
x_unsub_elig_for_depnt => loan_rec.unsub_elig_for_depnt,
x_guarantee_amt => loan_rec.guarantee_amt,
x_guarantee_date => loan_rec.guarantee_date,
x_guarnt_adj_ind => loan_rec.guarnt_adj_ind,
x_guarnt_amt_redn_code => loan_rec.guarnt_amt_redn_code,
x_guarnt_status_code => loan_rec.guarnt_status_code,
x_guarnt_status_date => loan_rec.guarnt_status_date,
x_lend_apprv_denied_code => NULL,
x_lend_apprv_denied_date => NULL,
x_lend_status_code => loan_rec.lend_status_code,
x_lend_status_date => loan_rec.lend_status_date,
x_grade_level_code => loan_rec.grade_level_code,
x_enrollment_code => loan_rec.enrollment_code,
x_anticip_compl_date => loan_rec.anticip_compl_date,
x_borw_lender_id => loan_rec.lender_id,
x_duns_borw_lender_id => NULL,
x_guarantor_id => loan_rec.guarantor_id,
x_duns_guarnt_id => NULL,
x_prc_type_code => loan_rec.prc_type_code,
x_rec_type_ind => loan_rec.rec_type_ind,
x_cl_loan_type => loan_rec.cl_loan_type,
x_cl_seq_number => loan_rec.cl_seq_number,
x_last_resort_lender => loan_rec.last_resort_lender,
x_lender_id => loan_rec.lender_id,
x_duns_lender_id => NULL,
x_lend_non_ed_brc_id => loan_rec.lend_non_ed_brc_id,
x_recipient_id => loan_rec.recipient_id,
x_recipient_type => loan_rec.recipient_type,
x_duns_recip_id => NULL,
x_recip_non_ed_brc_id => loan_rec.recip_non_ed_brc_id,
x_cl_rec_status => NULL,
x_cl_rec_status_last_update => NULL,
x_alt_prog_type_code => loan_rec.alt_prog_type_code,
x_alt_appl_ver_code => loan_rec.alt_appl_ver_code,
x_borw_outstd_loan_code => loan_rec.borw_outstd_loan_code,
x_mpn_confirm_code => NULL,
x_resp_to_orig_code => loan_rec.resp_to_orig_code,
x_appl_loan_phase_code => NULL,
x_appl_loan_phase_code_chg => NULL,
x_tot_outstd_stafford => loan_rec.tot_outstd_stafford,
x_tot_outstd_plus => loan_rec.tot_outstd_plus,
x_alt_borw_tot_debt => loan_rec.alt_borw_tot_debt,
x_act_interest_rate => loan_rec.act_interest_rate,
x_service_type_code => loan_rec.service_type_code,
x_rev_notice_of_guarnt => loan_rec.rev_notice_of_guarnt,
x_sch_refund_amt => loan_rec.sch_refund_amt,
x_sch_refund_date => loan_rec.sch_refund_date,
x_uniq_layout_vend_code => loan_rec.uniq_layout_vend_code,
x_uniq_layout_ident_code => loan_rec.uniq_layout_ident_code,
x_p_person_id => loan_rec.p_person_id,
x_p_ssn => SUBSTR(parent_dtl_rec.p_ssn,1,9),
x_p_ssn_chg_date => NULL,
x_p_last_name => parent_dtl_rec.p_last_name,
x_p_first_name => parent_dtl_rec.p_first_name,
x_p_middle_name => parent_dtl_rec.p_middle_name,
x_p_permt_addr1 => parent_dtl_rec.p_permt_addr1,
x_p_permt_addr2 => parent_dtl_rec.p_permt_addr2,
x_p_permt_city => parent_dtl_rec.p_permt_city,
x_p_permt_state => parent_dtl_rec.p_permt_state,
x_p_permt_zip => parent_dtl_rec.p_permt_zip,
x_p_permt_addr_chg_date => NULL,
x_p_permt_phone => lv_p_permt_phone,
x_p_email_addr => parent_dtl_rec.p_email_addr,
x_p_date_of_birth => parent_dtl_rec.p_date_of_birth,
x_p_dob_chg_date => NULL,
x_p_license_num => parent_dtl_rec.p_license_num,
x_p_license_state => parent_dtl_rec.p_license_state,
x_p_citizenship_status => lv_p_citizenship_status,
x_p_alien_reg_num => parent_dtl_rec.p_alien_reg_num,
x_p_default_status => loan_rec.p_default_status,
x_p_foreign_postal_code => lv_p_foreign_postal_code,
x_p_state_of_legal_res => parent_dtl_rec.p_state_of_legal_res,
x_p_legal_res_date => parent_dtl_rec.p_legal_res_date,
x_s_ssn => SUBSTR(student_dtl_rec.p_ssn,1,9),
x_s_ssn_chg_date => NULL,
x_s_last_name => student_dtl_rec.p_last_name,
x_s_first_name => student_dtl_rec.p_first_name,
x_s_middle_name => student_dtl_rec.p_middle_name,
x_s_permt_addr1 => student_dtl_rec.p_permt_addr1,
x_s_permt_addr2 => student_dtl_rec.p_permt_addr2,
x_s_permt_city => student_dtl_rec.p_permt_city,
x_s_permt_state => student_dtl_rec.p_permt_state,
x_s_permt_zip => student_dtl_rec.p_permt_zip,
x_s_permt_addr_chg_date => NULL,
x_s_permt_phone => lv_s_permt_phone,
x_s_local_addr1 => student_dtl_rec.p_local_addr1,
x_s_local_addr2 => student_dtl_rec.p_local_addr2,
x_s_local_city => student_dtl_rec.p_local_city,
x_s_local_state => student_dtl_rec.p_local_state,
x_s_local_zip => student_dtl_rec.p_local_zip,
x_s_local_addr_chg_date => NULL,
x_s_email_addr => student_dtl_rec.p_email_addr,
x_s_date_of_birth => student_dtl_rec.p_date_of_birth,
x_s_dob_chg_date => NULL,
x_s_license_num => lv_s_license_number,
x_s_license_state => lv_s_license_state,
x_s_depncy_status => lv_dependency_status,
x_s_default_status => loan_rec.s_default_status,
x_s_citizenship_status => lv_s_citizenship_status,
x_s_alien_reg_num => lv_alien_reg_num,
x_s_foreign_postal_code => lv_s_foreign_postal_code,
x_pnote_batch_id => loan_rec.pnote_batch_id,
x_pnote_ack_date => loan_rec.pnote_ack_date,
x_pnote_mpn_ind => loan_rec.pnote_mpn_ind,
x_award_id => loan_rec.award_id ,
x_base_id => loan_rec.base_id ,
x_document_id_txt => NULL ,
x_loan_key_num => NULL ,
x_interest_rebate_percent_num => loan_rec.interest_rebate_percent_num ,
x_fin_award_year => NULL ,
x_cps_trans_num => loan_rec.cps_trans_num ,
x_atd_entity_id_txt => loan_rec.atd_entity_id_txt ,
x_rep_entity_id_txt => loan_rec.rep_entity_id_txt ,
x_source_entity_id_txt => NULL ,
x_pymt_servicer_amt => loan_rec.pymt_servicer_amt ,
x_pymt_servicer_date => loan_rec.pymt_servicer_date ,
x_book_loan_amt => loan_rec.book_loan_amt ,
x_book_loan_amt_date => loan_rec.book_loan_amt_date ,
x_s_chg_birth_date => loan_rec.s_dob_chg_date ,
x_s_chg_ssn => NULL ,
x_s_chg_last_name => NULL ,
x_b_chg_birth_date => loan_rec.p_dob_chg_date ,
x_b_chg_ssn => NULL ,
x_b_chg_last_name => NULL ,
x_note_message => loan_rec.note_message ,
x_full_resp_code => NULL ,
x_s_permt_county => student_dtl_rec.p_county ,
x_b_permt_county => parent_dtl_rec.p_county ,
x_s_permt_country => student_dtl_rec.p_country ,
x_b_permt_country => parent_dtl_rec.p_country ,
x_crdt_decision_status => loan_rec.crdt_decision_status ,
x_external_loan_id_txt => loan_rec.external_loan_id_txt ,
x_deferment_request_code => loan_rec.deferment_request_code ,
x_eft_authorization_code => loan_rec.eft_authorization_code ,
x_requested_loan_amt => loan_rec.requested_loan_amt ,
x_actual_record_type_code => loan_rec.actual_record_type_code ,
x_reinstatement_amt => loan_rec.reinstatement_amt ,
x_school_use_txt => loan_rec.school_use_txt ,
x_lender_use_txt => loan_rec.lender_use_txt ,
x_guarantor_use_txt => loan_rec.guarantor_use_txt ,
x_fls_approved_amt => loan_rec.fls_approved_amt ,
x_flu_approved_amt => loan_rec.flu_approved_amt ,
x_flp_approved_amt => loan_rec.flp_approved_amt ,
x_alt_approved_amt => loan_rec.alt_approved_amt ,
x_loan_app_form_code => loan_rec.loan_app_form_code ,
x_alt_borrower_ind_flag => p_c_alt_borr_ind_flag ,
x_school_id_txt => p_v_school_id ,
x_cost_of_attendance_amt => p_n_coa ,
x_expect_family_contribute_amt => p_n_efc ,
x_established_fin_aid_amount => p_n_est_fin ,
x_borower_electronic_sign_flag => loan_rec.borr_sign_ind ,
x_student_electronic_sign_flag => loan_rec.stud_sign_ind ,
x_borower_credit_authoriz_flag => loan_rec.borr_credit_auth_code ,
x_mpn_type_flag => NULL ,
x_esign_src_typ_cd => loan_rec.esign_src_typ_cd
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.insert_lor_loc_rec.debug', 'Insertion into igf_sl_lor_loc succeeded' );
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_cl_orig.insert_lor_loc_rec.exception', 'Lock row failed' );
fnd_message.set_token('NAME','IGF_SL_CL_ORIG.INSERT_LOR_LOC_REC');
fnd_log.string(fnd_log.level_exception, 'igf.plsql.igf_sl_cl_orig.insert_lor_loc_rec.exception',SQLERRM);
END insert_lor_loc_rec;
PROCEDURE update_orig_batch_id(p_origination_id igf_sl_lor.origination_id%TYPE,
p_batch_id igf_sl_lor.orig_batch_id%TYPE)
IS
/***************************************************************
Created By : mesriniv
Date Created By : 2000/11/17
Purpose : To update igf_sl_lor table with origination batch id
Known Limitations,Enhancements or Remarks
Change History :
Who When What
veramach 23-SEP-2003 Bug 3104228:
1. Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
chg_batch_id,appl_send_error_codes from igf_sl_lor
2. Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
chg_batch_id from igf_sl_lor_loc
masehgal 17-Feb-2002 # 2216956 , FACR007
Added Elec_mpn_indicator , Borrow_sign_ind
***************************************************************/
lv_row_id ROWID;
SELECT igf_sl_lor.*
FROM igf_sl_lor
WHERE origination_id = p_origination_id FOR UPDATE OF sch_cert_date NOWAIT;
SELECT external_loan_id_txt
FROM igf_sl_loans_all
WHERE loan_id = cp_n_loan_id;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.update_batch_orig_id.debug', 'Before inserting into igf_sl_lor, loan_id:'||tbh_rec.loan_id);
igf_sl_lor_pkg.update_row (
x_Mode => 'R',
x_rowid => tbh_rec.row_id,
x_origination_id => tbh_rec.origination_id,
x_loan_id => tbh_rec.loan_id,
x_sch_cert_date => l_sch_date,
x_orig_status_flag => tbh_rec.orig_status_flag,
x_orig_batch_id => p_batch_id,
x_orig_batch_date => TRUNC(SYSDATE),
x_chg_batch_id => NULL,
x_orig_ack_date => tbh_rec.orig_ack_date,
x_credit_override => tbh_rec.credit_override,
x_credit_decision_date => tbh_rec.credit_decision_date,
x_req_serial_loan_code => tbh_rec.req_serial_loan_code,
x_act_serial_loan_code => tbh_rec.act_serial_loan_code,
x_pnote_delivery_code => tbh_rec.pnote_delivery_code,
x_pnote_status => tbh_rec.pnote_status,
x_pnote_status_date => tbh_rec.pnote_status_date,
x_pnote_id => tbh_rec.pnote_id,
x_pnote_print_ind => tbh_rec.pnote_print_ind,
x_pnote_accept_amt => tbh_rec.pnote_accept_amt,
x_pnote_accept_date => tbh_rec.pnote_accept_date,
x_unsub_elig_for_heal => tbh_rec.unsub_elig_for_heal,
x_disclosure_print_ind => tbh_rec.disclosure_print_ind,
x_orig_fee_perct => tbh_rec.orig_fee_perct,
x_borw_confirm_ind => tbh_rec.borw_confirm_ind,
x_borw_interest_ind => tbh_rec.borw_interest_ind,
x_borw_outstd_loan_code => tbh_rec.borw_outstd_loan_code,
x_unsub_elig_for_depnt => tbh_rec.unsub_elig_for_depnt,
x_guarantee_amt => tbh_rec.guarantee_amt,
x_guarantee_date => tbh_rec.guarantee_date,
x_guarnt_amt_redn_code => tbh_rec.guarnt_amt_redn_code,
x_guarnt_status_code => tbh_rec.guarnt_status_code,
x_guarnt_status_date => tbh_rec.guarnt_status_date,
x_lend_apprv_denied_code => NULL,
x_lend_apprv_denied_date => NULL,
x_lend_status_code => tbh_rec.lend_status_code,
x_lend_status_date => tbh_rec.lend_status_date,
x_guarnt_adj_ind => tbh_rec.guarnt_adj_ind,
x_grade_level_code => tbh_rec.grade_level_code,
x_enrollment_code => tbh_rec.enrollment_code,
x_anticip_compl_date => tbh_rec.anticip_compl_date,
x_borw_lender_id => NULL,
x_duns_borw_lender_id => NULL,
x_guarantor_id => NULL,
x_duns_guarnt_id => NULL,
x_prc_type_code => tbh_rec.prc_type_code,
x_cl_seq_number => tbh_rec.cl_seq_number,
x_last_resort_lender => tbh_rec.last_resort_lender,
x_lender_id => NULL,
x_duns_lender_id => NULL,
x_lend_non_ed_brc_id => tbh_rec.lend_non_ed_brc_id,
x_recipient_id => NULL,
x_recipient_type => NULL,
x_duns_recip_id => NULL,
x_recip_non_ed_brc_id => NULL,
x_rec_type_ind => tbh_rec.rec_type_ind,
x_cl_loan_type => tbh_rec.cl_loan_type,
x_cl_rec_status => NULL,
x_cl_rec_status_last_update => NULL,
x_alt_prog_type_code => tbh_rec.alt_prog_type_code,
x_alt_appl_ver_code => tbh_rec.alt_appl_ver_code,
x_mpn_confirm_code => NULL,
x_resp_to_orig_code => tbh_rec.resp_to_orig_code,
x_appl_loan_phase_code => NULL,
x_appl_loan_phase_code_chg => NULL,
x_appl_send_error_codes => NULL,
x_tot_outstd_stafford => tbh_rec.tot_outstd_stafford,
x_tot_outstd_plus => tbh_rec.tot_outstd_plus,
x_alt_borw_tot_debt => tbh_rec.alt_borw_tot_debt,
x_act_interest_rate => tbh_rec.act_interest_rate,
x_service_type_code => tbh_rec.service_type_code,
x_rev_notice_of_guarnt => tbh_rec.rev_notice_of_guarnt,
x_sch_refund_amt => tbh_rec.sch_refund_amt,
x_sch_refund_date => tbh_rec.sch_refund_date,
x_uniq_layout_vend_code => tbh_rec.uniq_layout_vend_code,
x_uniq_layout_ident_code => tbh_rec.uniq_layout_ident_code,
x_p_person_id => tbh_rec.p_person_id,
x_p_ssn_chg_date => NULL,
x_p_dob_chg_date => NULL,
x_p_permt_addr_chg_date => NULL,
x_p_default_status => tbh_rec.p_default_status,
x_p_signature_code => tbh_rec.p_signature_code,
x_p_signature_date => tbh_rec.p_signature_date,
x_s_ssn_chg_date => NULL,
x_s_dob_chg_date => NULL,
x_s_permt_addr_chg_date => NULL,
x_s_local_addr_chg_date => NULL,
x_s_default_status => tbh_rec.s_default_status,
x_s_signature_code => tbh_rec.s_signature_code,
x_pnote_batch_id => tbh_rec.pnote_batch_id,
x_pnote_ack_date => tbh_rec.pnote_ack_date,
x_pnote_mpn_ind => tbh_rec.pnote_mpn_ind,
x_elec_mpn_ind => tbh_rec.elec_mpn_ind,
x_borr_sign_ind => tbh_rec.borr_sign_ind,
x_stud_sign_ind => tbh_rec.stud_sign_ind,
x_borr_credit_auth_code => tbh_rec.borr_credit_auth_code,
x_relationship_cd => tbh_rec.relationship_cd ,
x_interest_rebate_percent_num => tbh_rec.interest_rebate_percent_num ,
x_cps_trans_num => tbh_rec.cps_trans_num ,
x_atd_entity_id_txt => tbh_rec.atd_entity_id_txt ,
x_rep_entity_id_txt => tbh_rec.rep_entity_id_txt ,
x_crdt_decision_status => tbh_rec.crdt_decision_status ,
x_note_message => tbh_rec.note_message ,
x_book_loan_amt => tbh_rec.book_loan_amt ,
x_book_loan_amt_date => tbh_rec.book_loan_amt_date ,
x_pymt_servicer_amt => tbh_rec.pymt_servicer_amt ,
x_pymt_servicer_date => tbh_rec.pymt_servicer_date ,
x_external_loan_id_txt => l_v_ext_loan_id_txt ,
x_deferment_request_code => tbh_rec.deferment_request_code ,
x_eft_authorization_code => tbh_rec.eft_authorization_code ,
x_requested_loan_amt => tbh_rec.requested_loan_amt ,
x_actual_record_type_code => tbh_rec.actual_record_type_code ,
x_reinstatement_amt => tbh_rec.reinstatement_amt ,
x_school_use_txt => tbh_rec.school_use_txt ,
x_lender_use_txt => tbh_rec.lender_use_txt ,
x_guarantor_use_txt => tbh_rec.guarantor_use_txt ,
x_fls_approved_amt => tbh_rec.fls_approved_amt ,
x_flu_approved_amt => tbh_rec.flu_approved_amt ,
x_flp_approved_amt => tbh_rec.flp_approved_amt ,
x_alt_approved_amt => tbh_rec.alt_approved_amt ,
x_loan_app_form_code => tbh_rec.loan_app_form_code ,
x_override_grade_level_code => tbh_rec.override_grade_level_code ,
x_b_alien_reg_num_txt => tbh_rec.b_alien_reg_num_txt ,
x_esign_src_typ_cd => tbh_rec.esign_src_typ_cd ,
x_acad_begin_date => tbh_rec.acad_begin_date ,
x_acad_end_date => tbh_rec.acad_end_date
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.update_orig_batch_id.debug', 'Insertion into igf_sl_lor succeeded, loan_id:'||tbh_rec.loan_id);
fnd_log.string(fnd_log.level_exception, 'igf.plsql.igf_sl_cl_orig.update_orig_batch_id.exception','Record Lock Exception');
fnd_log.string(fnd_log.level_exception, 'igf.plsql.igf_sl_cl_orig.update_orig_batch_id.exception',SQLERRM);
fnd_message.set_token('NAME','IGF_SL_CL_ORIG.UPDATE_ORIG_BATCH_ID');
END update_orig_batch_id;
agairola 15-Mar-2002 Modified the Update Row call of the IGF_SL_LOANS_PKG
to include the Borrower Determination as part of Refunds
DLD - 2144600
mesriniv 05-APR-2001 Changed the occurrences of field fao_id
to ope_id
***************************************************************/
no_loan_data EXCEPTION;
SELECT eft_authorization
FROM igf_sl_cl_setup
WHERE ci_cal_type = p_cal_type
AND ci_sequence_number = p_seq_number
AND relationship_cd = p_rel_code
AND NVL(party_id,-100) = NVL(p_party_id,-100);
SELECT isir.dependency_status
FROM igf_ap_fa_base_rec fabase, igf_ap_isir_matched isir
WHERE isir.base_id = fabase.base_id
AND fabase.person_id = loan_rec.student_id
AND isir.payment_isir = 'Y'
AND isir.system_record_type = 'ORIGINAL';
SELECT meaning
FROM igf_lookups_view
WHERE lookup_type = 'IGF_AP_SCHOOL_OPEID'
AND lookup_code = p_school_id;
SELECT disb_date,
NVL(disb_accepted_amt,0) disb_accepted_amt,
hold_rel_ind, direct_to_borr_flag
FROM igf_aw_awd_disb
WHERE award_id = loan_rec.award_id
ORDER
BY disb_num;
SELECT TRUNC(coa_f)
FROM igf_ap_fa_base_rec
WHERE base_id = loan_rec.base_id;
SELECT TRUNC(SUM(NVL(NVL(accepted_amt,offered_amt),0))) etsimated_fin
FROM igf_aw_award
WHERE base_id = loan_rec.base_id
AND award_id <> loan_rec.award_id
AND (award_status = p_award_status_1 OR award_status = p_award_status_2);
SELECT LPAD(SUM(NVL(fed_stafford_loan_debt,0) + NVL(fed_sls_debt,0) +
NVL(heal_debt,0) + NVL(perkins_debt,0) +
NVL(other_debt,0)),7,0) alt_loan_debt
FROM igf_sl_alt_borw,
igf_sl_loans_v loanv
WHERE igf_sl_alt_borw.loan_id = loanv.loan_id
AND loanv.student_id = loan_rec.student_id
AND loanv.ci_cal_type <> loan_rec.ci_cal_type
AND loanv.ci_sequence_number <> loan_rec.ci_sequence_number;
SELECT alt.*
FROM igf_sl_alt_borw alt
WHERE loan_id=loan_rec.loan_id;
SELECT COUNT(disb_num)
FROM igf_aw_awd_disb
WHERE award_id = p_award_id;
SELECT lor.*
FROM igf_sl_lor_all lor
WHERE loan_id = cp_n_loan_id;
SELECT
pct.restatus_code restatus_code
FROM igs_lookup_values lkup,
igs_pe_eit_restatus_v pct
WHERE lkup.lookup_type = 'PE_CITI_STATUS'
AND trim(lkup.lookup_code) = trim(pct.restatus_code)
AND 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;
igf_sl_cl_batch_pkg.insert_row (
x_mode => 'R',
x_rowid => lv_row_id,
x_cbth_id => lv_cbth_id,
x_batch_id => lv_batch_id,
x_file_creation_date => TRUNC(SYSDATE),
x_file_trans_date => TRUNC(SYSDATE),
x_file_ident_code => RPAD(lv_file_ident_code,5),
x_recipient_id => recip_desc_rec.recipient_id,
x_recip_non_ed_brc_id => recip_desc_rec.recip_non_ed_brc_id,
x_source_id => p_school_id,
x_source_non_ed_brc_id => sch_non_ed_branch,
x_send_resp => 'S',
x_record_count_num => NULL ,
x_total_net_disb_amt => NULL ,
x_total_net_eft_amt => NULL ,
x_total_net_non_eft_amt => NULL ,
x_total_reissue_amt => NULL ,
x_total_cancel_amt => NULL ,
x_total_deficit_amt => NULL ,
x_total_net_cancel_amt => NULL ,
x_total_net_out_cancel_amt => NULL
);
SELECT row_id row_id
FROM igf_sl_lor_loc
WHERE loan_id = loan_rec.loan_id FOR UPDATE OF loan_status NOWAIT;
igf_sl_lor_loc_pkg.delete_row (tbh_rec.row_id);
insert_lor_loc_rec (
p_v_school_id => p_school_id ,
p_n_coa => l_coa,
p_n_efc => l_efc,
p_n_est_fin => l_est_fin,
p_c_alt_borr_ind_flag => l_borw_ind_code
);
SELECT row_id row_id
FROM igf_sl_awd_disb_loc
WHERE award_id = loan_rec.award_id;
igf_sl_awd_disb_loc_pkg.delete_row (tbh_rec.row_id);
SELECT *
FROM igf_aw_awd_disb
WHERE award_id = loan_rec.award_id;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'Calling igf_sl_awd_disb_loc_pkg.insert_row');
igf_sl_awd_disb_loc_pkg.insert_row (
x_Mode => 'R',
x_rowid => lv_row_id,
x_award_id => tbh_rec.award_id,
x_disb_num => tbh_rec.disb_num,
x_disb_gross_amt => tbh_rec.disb_accepted_amt,
x_fee_1 => tbh_rec.fee_1,
x_fee_2 => tbh_rec.fee_2,
x_disb_net_amt => tbh_rec.disb_net_amt,
x_disb_date => tbh_rec.disb_date,
x_hold_rel_ind => tbh_rec.hold_rel_ind,
x_fee_paid_1 => tbh_rec.fee_paid_1,
x_fee_paid_2 => tbh_rec.fee_paid_2
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'igf_sl_awd_disb_loc_pkg.insert_row succeeded');
SELECT igf_sl_loans.*
FROM igf_sl_loans
WHERE loan_id = loan_rec.loan_id FOR UPDATE OF igf_sl_loans.loan_status NOWAIT;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig.sub_cl_originate.debug', 'Calling igf_sl_loans_pkg.update_row');
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 => 'S',
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_orig.sub_cl_originate.debug', 'igf_sl_loans_pkg.update_row succeeded');
Update_orig_batch_id(loan_rec.origination_id, lv_batch_id);