[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
TBH Impact change in igf_sl_lor_loc_pkg.update_row()
Bug No:2332668 Desc:LOAN ORIGINATION PROCESS NOT RUNNING SUCCESSFULLY.
Who When What
mesriniv 23-APR-2002 Added code to display the Parameters Passed
Bug :- 2255281
Desc:- DL Version Change
Who When What
mesriniv 20-MAR-2002 Added Code to SKIP LOAN when an error occurs
Who When What
agairola 19-Mar-2002 Modifed the update row call of the IGF_SL_LOANS_PKG to include
Borrower determination as part of Refunds DLD - 2144600
(reverse chronological order - newest change first)
***************************************************************/
lv_cal_type igs_ca_inst.cal_type%TYPE; -- ## Used for the award year ##
SELECT
loans.row_id,
loans.loan_id,
loans.loan_number,
loans.award_id,
awd.accepted_amt loan_amt_accepted,
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.grade_level_code,
lor.unsub_elig_for_heal,
lor.disclosure_print_ind,
lor.unsub_elig_for_depnt,
lor.pnote_batch_id,
lor.pnote_ack_date,
lor.pnote_mpn_ind,
fabase.base_id,
fabase.person_id student_id,
awd.accepted_amt
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
WHERE
fabase.ci_cal_type = p_cal_type AND
fabase.ci_sequence_number = p_seq_num AND
fabase.base_id = awd.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_id = lor.loan_id AND
loans.loan_status = p_loan_status AND
loans.loan_chg_status = p_loan_chg_status AND
loans.active = p_active AND
substr(loans.loan_number, 13, 6) = p_school_code;
SELECT igf_sl_lor_loc.*
FROM igf_sl_lor_loc
WHERE loan_id = p_loan_id;
SELECT isir.dependency_status
FROM igf_ap_fa_base_rec fabase,igf_ap_isir_matched isir
WHERE isir.isir_id = fabase.payment_isir_id
AND fabase.base_id = l_base_id;
SELECT disb.* FROM igf_aw_awd_disb disb
WHERE disb.award_id = p_award_id
ORDER BY disb.disb_num;
SELECT disb.row_id row_id, disb.disb_num, disb.disb_gross_amt, disb.disb_date
FROM igf_sl_awd_disb_loc disb
WHERE award_id = p_award_id
ORDER BY disb.disb_num FOR UPDATE OF disb.disb_gross_amt NOWAIT;
SELECT meaning FROM igf_lookups_view
WHERE lookup_type='IGF_SL_DL_LOAN_CATG' AND lookup_code=p_dl_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','PARAMETER_PASS') AND enabled_flag = 'Y';
SELECT meaning
FROM igs_lookups_view
WHERE lookup_type = 'OR_SYSTEM_ID_TYPE'
AND lookup_code = 'DL_SCH_CD'
AND enabled_flag = 'Y';
SELECT ca.alternate_code FROM igs_ca_inst ca
WHERE ca.cal_type =lv_cal_type
AND ca.sequence_number = lv_cal_seq_num;
SELECT chg_code FROM igf_sl_dl_chg_fld
WHERE fld_name =p_field_name
AND loan_catg= p_dl_loan_catg
AND dl_version =lv_dl_version;
igf_sl_dl_chg_send_pkg.insert_row (
x_mode => 'R',
x_rowid => l_rowid,
X_chg_num => l_chg_num,
X_dbth_id => lv_dbth_id,
X_loan_number => lv_loan_number,
X_chg_code => lc_chg_code.chg_code,
X_new_value => p_field_lor,
X_status => 'S'
);
2. Inserts the Batch ID details in igf_sl_dl_batch
3. For the specified version, Opens a REF CURSOR, having
header file Specs.
Since the Batch-Type and File-Specifications are same for STAFFORD
and PLUS, we are passing Loan_catg='DL' and File-Type='DL_CHG_SEND'
to DLHeader_Cur(). (Maintaining only 1 record in igf_sl_dl_file_type Seed table)
*************************************************************************/
igf_sl_dl_record.DLHeader_cur(lv_dl_version, 'DL',
lv_cal_type, lv_cal_seq_num, 'DL_CHG_SEND', p_school_code,
lv_dbth_id, lv_batch_id, lv_mesg_class, Header_Rec);
igf_sl_lor_loc_pkg.update_row (
X_Mode => 'R',
x_rowid => lc_lor_loc.row_id,
x_loan_id => lc_lor_loc.loan_id,
x_origination_id => lc_lor_loc.origination_id,
x_loan_number => lc_lor_loc.loan_number,
x_loan_type => lc_lor_loc.loan_type,
x_loan_amt_offered => lc_lor_loc.loan_amt_offered,
x_loan_amt_accepted => lc_lor.loan_amt_accepted,
x_loan_per_begin_date => lc_lor.loan_per_begin_date,
x_loan_per_end_date => lc_lor.loan_per_end_date,
X_acad_yr_begin_date => lc_lor_loc.acad_yr_begin_date,
X_acad_yr_end_date => lc_lor_loc.acad_yr_end_date,
x_loan_status => lc_lor_loc.loan_status,
x_loan_status_date => lc_lor_loc.loan_status_date,
x_loan_chg_status => lc_lor_loc.loan_chg_status,
x_loan_chg_status_date => lc_lor_loc.loan_chg_status_date,
x_req_serial_loan_code => lc_lor_loc.req_serial_loan_code,
x_act_serial_loan_code => lc_lor_loc.act_serial_loan_code,
x_active => lc_lor_loc.active,
x_active_date => lc_lor_loc.active_date,
x_sch_cert_date => lc_lor_loc.sch_cert_date,
x_orig_status_flag => lc_lor_loc.orig_status_flag,
x_orig_batch_id => lc_lor_loc.orig_batch_id,
x_orig_batch_date => lc_lor_loc.orig_batch_date,
x_chg_batch_id => NULL,
x_orig_ack_date => lc_lor_loc.orig_ack_date,
x_credit_override => lc_lor_loc.credit_override,
x_credit_decision_date => lc_lor_loc.credit_decision_date,
x_pnote_delivery_code => lc_lor_loc.pnote_delivery_code,
x_pnote_status => lc_lor_loc.pnote_status,
x_pnote_status_date => lc_lor_loc.pnote_status_date,
x_pnote_id => lc_lor_loc.pnote_id,
x_pnote_print_ind => lc_lor.pnote_print_ind,
x_pnote_accept_amt => lc_lor_loc.pnote_accept_amt,
x_pnote_accept_date => lc_lor_loc.pnote_accept_date,
x_p_signature_code => lc_lor_loc.p_signature_code,
x_p_signature_date => lc_lor_loc.p_signature_date,
x_s_signature_code => lc_lor_loc.s_signature_code,
x_unsub_elig_for_heal => lc_lor.unsub_elig_for_heal,
x_disclosure_print_ind => lc_lor.disclosure_print_ind,
x_orig_fee_perct => lc_lor.orig_fee_perct,
x_borw_confirm_ind => lc_lor_loc.borw_confirm_ind,
x_borw_interest_ind => lc_lor_loc.borw_interest_ind,
x_unsub_elig_for_depnt => lc_lor.unsub_elig_for_depnt,
x_guarantee_amt => lc_lor_loc.guarantee_amt,
x_guarantee_date => lc_lor_loc.guarantee_date,
x_guarnt_adj_ind => lc_lor_loc.guarnt_adj_ind,
x_guarnt_amt_redn_code => lc_lor_loc.guarnt_amt_redn_code,
x_guarnt_status_code => lc_lor_loc.guarnt_status_code,
x_guarnt_status_date => lc_lor_loc.guarnt_status_date,
x_lend_apprv_denied_code => NULL,
x_lend_apprv_denied_date => NULL,
x_lend_status_code => lc_lor_loc.lend_status_code,
x_lend_status_date => lc_lor_loc.lend_status_date,
x_grade_level_code => lc_lor.grade_level_code,
x_enrollment_code => lc_lor_loc.enrollment_code,
x_anticip_compl_date => lc_lor_loc.anticip_compl_date,
x_borw_lender_id => lc_lor_loc.borw_lender_id,
x_duns_borw_lender_id => NULL,
x_guarantor_id => lc_lor_loc.guarantor_id,
x_duns_guarnt_id => NULL,
x_prc_type_code => lc_lor_loc.prc_type_code,
x_rec_type_ind => lc_lor_loc.rec_type_ind,
x_cl_loan_type => lc_lor_loc.cl_loan_type,
x_cl_seq_number => lc_lor_loc.cl_seq_number,
x_last_resort_lender => lc_lor_loc.last_resort_lender,
x_lender_id => lc_lor_loc.lender_id,
x_duns_lender_id => NULL,
x_lend_non_ed_brc_id => lc_lor_loc.lend_non_ed_brc_id,
x_recipient_id => lc_lor_loc.recipient_id,
x_recipient_type => lc_lor_loc.recipient_type,
x_duns_recip_id => NULL,
x_recip_non_ed_brc_id => lc_lor_loc.recip_non_ed_brc_id,
x_cl_rec_status => NULL,
x_cl_rec_status_last_update => NULL,
x_alt_prog_type_code => lc_lor_loc.alt_prog_type_code,
x_alt_appl_ver_code => lc_lor_loc.alt_appl_ver_code,
x_borw_outstd_loan_code => lc_lor_loc.borw_outstd_loan_code,
x_mpn_confirm_code => NULL,
x_resp_to_orig_code => lc_lor_loc.resp_to_orig_code,
x_appl_loan_phase_code => NULL,
x_appl_loan_phase_code_chg => NULL,
x_tot_outstd_stafford => lc_lor_loc.tot_outstd_stafford,
x_tot_outstd_plus => lc_lor_loc.tot_outstd_plus,
x_alt_borw_tot_debt => lc_lor_loc.alt_borw_tot_debt,
x_act_interest_rate => lc_lor_loc.act_interest_rate,
x_service_type_code => lc_lor_loc.service_type_code,
x_rev_notice_of_guarnt => lc_lor_loc.rev_notice_of_guarnt,
x_sch_refund_amt => lc_lor_loc.sch_refund_amt,
x_sch_refund_date => lc_lor_loc.sch_refund_date,
x_uniq_layout_vend_code => lc_lor_loc.uniq_layout_vend_code,
x_uniq_layout_ident_code => lc_lor_loc.uniq_layout_ident_code,
x_p_person_id => lc_lor_loc.p_person_id,
x_p_ssn => parent_dtl_rec.p_ssn,
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 => lc_lor_loc.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 => parent_dtl_rec.p_citizenship_status,
x_p_alien_reg_num => parent_dtl_rec.p_alien_reg_num,
x_p_default_status => lc_lor.p_default_status,
x_p_foreign_postal_code => NULL,
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 => student_dtl_rec.p_ssn,
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 => lc_lor_loc.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 => l_driver_license_number,
x_s_license_state => l_driver_license_state,
x_s_depncy_status => l_dependency_status,
x_s_default_status => lc_lor.s_default_status,
x_s_citizenship_status => l_citizenship_status,
x_s_alien_reg_num => l_alien_reg_number,
x_s_foreign_postal_code => NULL,
x_pnote_batch_id => lc_lor.pnote_batch_id,
x_pnote_ack_date => lc_lor.pnote_ack_date,
x_pnote_mpn_ind => lc_lor.pnote_mpn_ind ,
x_award_id => lc_lor_loc.award_id,
x_base_id => lc_lor_loc.base_id,
x_document_id_txt => lc_lor_loc.document_id_txt,
x_loan_key_num => lc_lor_loc.loan_key_num,
x_interest_rebate_percent_num => lc_lor_loc.interest_rebate_percent_num,
x_fin_award_year => lc_lor_loc.fin_award_year,
x_cps_trans_num => lc_lor_loc.cps_trans_num,
x_atd_entity_id_txt => lc_lor_loc.atd_entity_id_txt,
x_rep_entity_id_txt => lc_lor_loc.rep_entity_id_txt,
x_source_entity_id_txt => lc_lor_loc.source_entity_id_txt,
x_pymt_servicer_amt => lc_lor_loc.pymt_servicer_amt,
x_pymt_servicer_date => lc_lor_loc.pymt_servicer_date,
x_book_loan_amt => lc_lor_loc.book_loan_amt,
x_book_loan_amt_date => lc_lor_loc.book_loan_amt_date,
x_s_chg_birth_date => lc_lor_loc.s_chg_birth_date,
x_s_chg_ssn => lc_lor_loc.s_chg_ssn,
x_s_chg_last_name => lc_lor_loc.s_chg_last_name,
x_b_chg_birth_date => lc_lor_loc.b_chg_birth_date,
x_b_chg_ssn => lc_lor_loc.b_chg_ssn,
x_b_chg_last_name => lc_lor_loc.b_chg_last_name,
x_note_message => lc_lor_loc.note_message,
x_full_resp_code => lc_lor_loc.full_resp_code,
x_s_permt_county => lc_lor_loc.s_permt_county,
x_b_permt_county => lc_lor_loc.b_permt_county,
x_s_permt_country => lc_lor_loc.s_permt_country,
x_b_permt_country => lc_lor_loc.b_permt_country,
x_crdt_decision_status => lc_lor_loc.crdt_decision_status,
x_external_loan_id_txt => lc_lor_loc.external_loan_id_txt,
x_deferment_request_code => lc_lor_loc.deferment_request_code,
x_eft_authorization_code => lc_lor_loc.eft_authorization_code,
x_requested_loan_amt => lc_lor_loc.requested_loan_amt,
x_actual_record_type_code => lc_lor_loc.actual_record_type_code,
x_reinstatement_amt => lc_lor_loc.reinstatement_amt,
x_lender_use_txt => lc_lor_loc.lender_use_txt,
x_guarantor_use_txt => lc_lor_loc.guarantor_use_txt,
x_fls_approved_amt => lc_lor_loc.fls_approved_amt,
x_flu_approved_amt => lc_lor_loc.flu_approved_amt,
x_flp_approved_amt => lc_lor_loc.flp_approved_amt,
x_alt_approved_amt => lc_lor_loc.alt_approved_amt,
x_loan_app_form_code => lc_lor_loc.loan_app_form_code,
x_alt_borrower_ind_flag => lc_lor_loc.alt_borrower_ind_flag,
x_school_id_txt => lc_lor_loc.school_id_txt,
x_cost_of_attendance_amt => lc_lor_loc.cost_of_attendance_amt,
x_established_fin_aid_amount => lc_lor_loc.established_fin_aid_amount,
x_student_electronic_sign_flag => lc_lor_loc.student_electronic_sign_flag,
x_mpn_type_flag => lc_lor_loc.mpn_type_flag,
x_school_use_txt => lc_lor_loc.school_use_txt,
x_expect_family_contribute_amt => lc_lor_loc.expect_family_contribute_amt,
x_borower_electronic_sign_flag => lc_lor_loc.borower_electronic_sign_flag,
x_borower_credit_authoriz_flag => lc_lor_loc.borower_credit_authoriz_flag ,
x_esign_src_typ_cd => lc_lor_loc.esign_src_typ_cd
);
igf_sl_awd_disb_loc_pkg.delete_row (lcur_disb_loc.row_id);
igf_sl_awd_disb_loc_pkg.insert_row (
x_mode => 'R',
x_rowid => l_rowid,
X_award_id => lcur_award.award_id,
X_disb_num => lcur_award.disb_num,
X_disb_gross_amt => lcur_award.disb_accepted_amt,
X_fee_1 => lcur_award.fee_1,
X_fee_2 => lcur_award.fee_1,
X_disb_net_amt => lcur_award.disb_net_amt,
X_disb_date => lcur_award.disb_date,
X_hold_rel_ind => lcur_award.hold_rel_ind,
X_fee_paid_1 => lcur_award.fee_paid_1,
X_fee_paid_2 => lcur_award.fee_paid_2
);
SELECT igf_sl_loans.* FROM igf_sl_loans
WHERE loan_id =lc_lor.loan_id;
igf_sl_loans_pkg.update_row (
x_Mode => 'R',
x_rowid => tbh_rec.row_id,
x_loan_id => lc_lor.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 => tbh_rec.loan_status,
x_loan_status_date => tbh_rec.loan_status_date,
x_loan_chg_status => 'S', -- ## Change the loan change status as send
x_loan_chg_status_date => TRUNC(SYSDATE), -- ## Change the loan change date as sysdate
x_active => tbh_rec.active,
x_active_date => tbh_rec.active_date,
x_borw_detrm_code => tbh_rec.borw_detrm_code,
x_external_loan_id_txt => tbh_rec.external_loan_id_txt
);
SELECT * from igf_sl_dl_chg_send
WHERE dbth_id = p_dl_dbth_id
ORDER BY loan_number, chg_num;