[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT substr(record_data, 23, 23) batch_id,
substr(record_data, 15, 8) message_class,
substr(record_data, 46, 16) bth_creation_date,
substr(record_data, 60, 2) batch_rej_code,
substr(record_data, 23, 2) batch_type
FROM igf_sl_load_file_t
WHERE lort_id = 1
AND record_data like 'DL HEADER%'
AND file_type = 'DL_CHG_ACK';
SELECT lort_id last_lort_id,
substr(record_data,15,7) number_rec,
substr(record_data,22,5) accept_rec,
substr(record_data,27,5) reject_rec,
substr(record_data,32,5) pending_rec
FROM igf_sl_load_file_t
WHERE lort_id = (select max(lort_id) FROM igf_sl_load_file_t)
AND record_data like 'DL TRAILER%'
AND file_type = 'DL_CHG_ACK';
SELECT 'x' FROM igf_sl_dl_batch
WHERE batch_id = l_batch_id
AND message_class = l_message_class
AND to_char(bth_creation_date,'YYYYMMDDHH24MISS') = l_bth_creation_date
AND batch_type = l_batch_type;
SELECT ci_cal_type, ci_sequence_number
FROM igf_sl_dl_setup
WHERE dl_version = p_dl_version ;
igf_sl_dl_batch_pkg.insert_row (
x_mode => 'R',
x_rowid => l_rowid,
X_dbth_id => l_dbth_id,
X_batch_id => l_rec_batch_id,
X_message_class => l_rec_message_class,
X_bth_creation_date => to_date(l_rec_bth_creation_date,'YYYYMMDDHH24MISS'),
X_batch_rej_code => l_rec_batch_rej_code,
X_end_date => NULL,
X_batch_type => l_rec_batch_type,
X_send_resp => 'R',
X_status => 'N'
);
SELECT record_data FROM igf_sl_load_file_t
WHERE lort_id between 2 AND (l_last_lort_id-1)
AND file_type = 'DL_CHG_ACK';
igf_sl_dl_chg_resp_pkg.insert_row (
x_mode => 'R',
x_rowid => l_rowid,
X_resp_num => l_lor_resp_num,
X_dbth_id => l_dbth_id,
X_batch_id => RTRIM(substr(orec.record_data, 590,23) ),
X_loan_number => RTRIM(substr(orec.record_data, 1,21) ),
X_chg_code => RTRIM(substr(orec.record_data, 24+(56*(l_incr-1)), 4) ),
X_reject_code => RTRIM(substr(orec.record_data, 78+(56*(l_incr-1)), 2) ),
X_new_value => RTRIM(substr(orec.record_data, 28+(56*(l_incr-1)), 50) ),
X_loan_ident_err_code => RTRIM(substr(orec.record_data, 22,2)),
X_status => 'N'
);
SELECT igf_sl_dl_batch.* FROM igf_sl_dl_batch
WHERE dbth_id = l_dbth_id
AND status = 'N';
SELECT loan_number,
NVL(SUM(DECODE(loan_ident_err_code,NULL,0,1)),0) loan_err_count,
NVL(SUM(DECODE(reject_code,NULL,1,0)),0) accept_count,
NVL(SUM(DECODE(reject_code,NULL,0,1)),0) reject_count
FROM igf_sl_dl_chg_resp
WHERE dbth_id = l_dbth_id
AND status = 'N'
GROUP BY loan_number;
SELECT fcat.fed_fund_code
FROM igf_sl_loans_all loan,
igf_aw_award_all awd,
igf_aw_fund_mast_all fmast,
igf_aw_fund_cat_all fcat
WHERE
loan.award_id = awd.award_id AND
awd.fund_id = fmast.fund_id AND
fcat.fund_code = fmast.fund_code AND
loan.loan_number = p_loan_number;
SELECT resp.new_value
FROM igf_sl_dl_chg_resp resp,igf_sl_dl_chg_fld chgfld
WHERE loan_number =p_loan_number
AND dbth_id =p_dbth_id
AND status ='N'
AND resp.chg_code = chgfld.chg_code
AND chgfld.dl_version = g_dl_version
AND chgfld.fld_name ='LOAN_AMT_ACCEPTED'
AND chgfld.loan_catg =p_loan_catg;
SELECT DISTINCT rchg.loan_ident_err_code
FROM igf_sl_dl_chg_resp rchg
WHERE rchg.dbth_id = l_dbth_id
AND rchg.loan_number = l_loan_number
AND rchg.loan_ident_err_code IS NOT NULL
AND rchg.status = 'N';
SELECT fld_name, cchgv.description fld_desc, rchg.reject_code, rchg.new_value
FROM igf_sl_dl_chg_resp rchg,
igf_sl_dl_chg_fld_v cchgv
WHERE rchg.dbth_id = l_dbth_id
AND rchg.loan_number = l_loan_number
AND rchg.reject_code IS NOT NULL
AND rchg.status = 'N'
AND rchg.chg_code = cchgv.chg_code
AND cchgv.dl_version = g_dl_version;
igf_sl_edit.delete_edit(resp_rec.loan_number, 'H');
igf_sl_edit.insert_edit(resp_rec.loan_number,
'H', -- EDITS from Change Response
'IGF_SL_DL_CHG_REJ_CODES',
lrec.loan_ident_err_code,
NULL,
NULL);
igf_sl_edit.insert_edit(resp_rec.loan_number,
'H', -- EDITS from Change Response
'IGF_SL_DL_CHG_REJ_CODES',
rrec.reject_code,
rrec.fld_name,
rrec.new_value);
igf_sl_dl_batch_pkg.update_row (
X_Mode => 'R',
x_rowid => dbth_rec.row_id,
x_dbth_id => dbth_rec.dbth_id,
x_batch_id => dbth_rec.batch_id,
x_message_class => dbth_rec.message_class,
x_bth_creation_date => dbth_rec.bth_creation_date,
x_batch_rej_code => dbth_rec.batch_rej_code,
x_end_date => dbth_rec.end_date,
x_batch_type => dbth_rec.batch_type,
x_send_resp => dbth_rec.send_resp,
x_status => 'Y'
);
SELECT igf_sl_dl_chg_resp.* FROM igf_sl_dl_chg_resp
WHERE dbth_id = p_dbth_id
AND loan_number = p_loan_number;
igf_sl_dl_chg_resp_pkg.update_row (
X_Mode => 'R',
x_rowid => tbh_rec.row_id,
x_resp_num => tbh_rec.resp_num,
x_dbth_id => tbh_rec.dbth_id,
x_batch_id => tbh_rec.batch_id,
x_loan_number => tbh_rec.loan_number,
x_chg_code => tbh_rec.chg_code,
x_reject_code => tbh_rec.reject_code,
x_new_value => tbh_rec.new_value,
x_loan_ident_err_code => tbh_rec.loan_ident_err_code,
x_status => tbh_rec.status
);
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
(reverse chronological order - newest change first)
***************************************************************/
l_row_id VARCHAR2(25);
SELECT igf_sl_loans.* FROM igf_sl_loans
WHERE loan_number = p_loan_number
FOR UPDATE OF loan_id 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 => tbh_rec.loan_status,
x_loan_status_date => tbh_rec.loan_status_date,
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
);
SELECT igf_sl_lor_loc.* FROM igf_sl_lor_loc
WHERE loan_id = p_loan_id;
igf_sl_lor_loc_pkg.update_row (
X_Mode => 'R',
x_rowid => tbh_rec.row_id,
x_loan_id => tbh_rec.loan_id,
x_origination_id => tbh_rec.origination_id,
x_loan_number => tbh_rec.loan_number,
x_loan_type => tbh_rec.loan_type,
x_loan_amt_offered => tbh_rec.loan_amt_offered,
x_loan_amt_accepted => tbh_rec.loan_amt_accepted,
x_loan_per_begin_date => tbh_rec.loan_per_begin_date,
x_loan_per_end_date => tbh_rec.loan_per_end_date,
x_acad_yr_begin_date => tbh_rec.acad_yr_begin_date,
x_acad_yr_end_date => tbh_rec.acad_yr_end_date,
x_loan_status => tbh_rec.loan_status,
x_loan_status_date => tbh_rec.loan_status_date,
x_loan_chg_status => tbh_rec.loan_chg_status,
x_loan_chg_status_date => tbh_rec.loan_chg_status_date,
x_req_serial_loan_code => tbh_rec.req_serial_loan_code,
x_act_serial_loan_code => tbh_rec.act_serial_loan_code,
x_active => tbh_rec.active,
x_active_date => tbh_rec.active_date,
x_sch_cert_date => tbh_rec.sch_cert_date,
x_orig_status_flag => tbh_rec.orig_status_flag,
x_orig_batch_id => tbh_rec.orig_batch_id,
x_orig_batch_date => tbh_rec.orig_batch_date,
x_chg_batch_id => tbh_rec.chg_batch_id,
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_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_p_signature_code => tbh_rec.p_signature_code,
x_p_signature_date => tbh_rec.p_signature_date,
x_s_signature_code => tbh_rec.s_signature_code,
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_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_adj_ind => tbh_rec.guarnt_adj_ind,
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 => tbh_rec.lend_apprv_denied_code,
x_lend_apprv_denied_date => tbh_rec.lend_apprv_denied_date,
x_lend_status_code => tbh_rec.lend_status_code,
x_lend_status_date => tbh_rec.lend_status_date,
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 => tbh_rec.borw_lender_id,
x_duns_borw_lender_id => NULL,
x_guarantor_id => tbh_rec.guarantor_id,
x_duns_guarnt_id => NULL,
x_prc_type_code => tbh_rec.prc_type_code,
x_rec_type_ind => tbh_rec.rec_type_ind,
x_cl_loan_type => tbh_rec.cl_loan_type,
x_cl_seq_number => tbh_rec.cl_seq_number,
x_last_resort_lender => tbh_rec.last_resort_lender,
x_lender_id => tbh_rec.lender_id,
x_duns_lender_id => NULL,
x_lend_non_ed_brc_id => tbh_rec.lend_non_ed_brc_id,
x_recipient_id => tbh_rec.recipient_id,
x_recipient_type => tbh_rec.recipient_type,
x_duns_recip_id => NULL,
x_recip_non_ed_brc_id => tbh_rec.recip_non_ed_brc_id,
x_cl_rec_status => tbh_rec.cl_rec_status,
x_cl_rec_status_last_update => tbh_rec.cl_rec_status_last_update,
x_alt_prog_type_code => tbh_rec.alt_prog_type_code,
x_alt_appl_ver_code => tbh_rec.alt_appl_ver_code,
x_borw_outstd_loan_code => tbh_rec.borw_outstd_loan_code,
x_mpn_confirm_code => tbh_rec.mpn_confirm_code,
x_resp_to_orig_code => tbh_rec.resp_to_orig_code,
x_appl_loan_phase_code => tbh_rec.appl_loan_phase_code,
x_appl_loan_phase_code_chg => tbh_rec.appl_loan_phase_code_chg,
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 => tbh_rec.p_ssn,
x_p_ssn_chg_date => tbh_rec.p_ssn_chg_date,
x_p_last_name => tbh_rec.p_last_name,
x_p_first_name => tbh_rec.p_first_name,
x_p_middle_name => tbh_rec.p_middle_name,
x_p_permt_addr1 => tbh_rec.p_permt_addr1,
x_p_permt_addr2 => tbh_rec.p_permt_addr2,
x_p_permt_city => tbh_rec.p_permt_city,
x_p_permt_state => tbh_rec.p_permt_state,
x_p_permt_zip => tbh_rec.p_permt_zip,
x_p_permt_addr_chg_date => tbh_rec.p_permt_addr_chg_date,
x_p_permt_phone => tbh_rec.p_permt_phone,
x_p_email_addr => tbh_rec.p_email_addr,
x_p_date_of_birth => tbh_rec.p_date_of_birth,
x_p_dob_chg_date => tbh_rec.p_dob_chg_date,
x_p_license_num => tbh_rec.p_license_num,
x_p_license_state => tbh_rec.p_license_state,
x_p_citizenship_status => tbh_rec.p_citizenship_status,
x_p_alien_reg_num => tbh_rec.p_alien_reg_num,
x_p_default_status => tbh_rec.p_default_status,
x_p_foreign_postal_code => tbh_rec.p_foreign_postal_code,
x_p_state_of_legal_res => tbh_rec.p_state_of_legal_res,
x_p_legal_res_date => tbh_rec.p_legal_res_date,
x_s_ssn => tbh_rec.s_ssn,
x_s_ssn_chg_date => tbh_rec.s_ssn_chg_date,
x_s_last_name => tbh_rec.s_last_name,
x_s_first_name => tbh_rec.s_first_name,
x_s_middle_name => tbh_rec.s_middle_name,
x_s_permt_addr1 => tbh_rec.s_permt_addr1,
x_s_permt_addr2 => tbh_rec.s_permt_addr2,
x_s_permt_city => tbh_rec.s_permt_city,
x_s_permt_state => tbh_rec.s_permt_state,
x_s_permt_zip => tbh_rec.s_permt_zip,
x_s_permt_addr_chg_date => tbh_rec.s_permt_addr_chg_date,
x_s_permt_phone => tbh_rec.s_permt_phone,
x_s_local_addr1 => tbh_rec.s_local_addr1,
x_s_local_addr2 => tbh_rec.s_local_addr2,
x_s_local_city => tbh_rec.s_local_city,
x_s_local_state => tbh_rec.s_local_state,
x_s_local_zip => tbh_rec.s_local_zip,
x_s_local_addr_chg_date => tbh_rec.s_local_addr_chg_date,
x_s_email_addr => tbh_rec.s_email_addr,
x_s_date_of_birth => tbh_rec.s_date_of_birth,
x_s_dob_chg_date => tbh_rec.s_dob_chg_date,
x_s_license_num => tbh_rec.s_license_num,
x_s_license_state => tbh_rec.s_license_state,
x_s_depncy_status => tbh_rec.s_depncy_status,
x_s_default_status => tbh_rec.s_default_status,
x_s_citizenship_status => tbh_rec.s_citizenship_status,
x_s_alien_reg_num => tbh_rec.s_alien_reg_num,
x_s_foreign_postal_code => tbh_rec.s_foreign_postal_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_award_id => tbh_rec.award_id,
x_base_id => tbh_rec.base_id,
x_document_id_txt => tbh_rec.document_id_txt,
x_loan_key_num => tbh_rec.loan_key_num,
x_interest_rebate_percent_num => tbh_rec.interest_rebate_percent_num,
x_fin_award_year => tbh_rec.fin_award_year,
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_source_entity_id_txt => tbh_rec.source_entity_id_txt,
x_pymt_servicer_amt => tbh_rec.pymt_servicer_amt,
x_pymt_servicer_date => tbh_rec.pymt_servicer_date,
x_book_loan_amt => tbh_rec.book_loan_amt,
x_book_loan_amt_date => tbh_rec.book_loan_amt_date,
x_s_chg_birth_date => tbh_rec.s_chg_birth_date,
x_s_chg_ssn => tbh_rec.s_chg_ssn,
x_s_chg_last_name => tbh_rec.s_chg_last_name,
x_b_chg_birth_date => tbh_rec.b_chg_birth_date,
x_b_chg_ssn => tbh_rec.b_chg_ssn,
x_b_chg_last_name => tbh_rec.b_chg_last_name,
x_note_message => tbh_rec.note_message,
x_full_resp_code => tbh_rec.full_resp_code,
x_s_permt_county => tbh_rec.s_permt_county,
x_b_permt_county => tbh_rec.b_permt_county,
x_s_permt_country => tbh_rec.s_permt_country,
x_b_permt_country => tbh_rec.b_permt_country,
x_crdt_decision_status => tbh_rec.crdt_decision_status,
x_external_loan_id_txt => tbh_rec.external_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_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_alt_borrower_ind_flag => tbh_rec.alt_borrower_ind_flag,
x_school_id_txt => tbh_rec.school_id_txt,
x_cost_of_attendance_amt => tbh_rec.cost_of_attendance_amt,
x_established_fin_aid_amount => tbh_rec.established_fin_aid_amount,
x_student_electronic_sign_flag => tbh_rec.student_electronic_sign_flag,
x_mpn_type_flag => tbh_rec.mpn_type_flag,
x_school_use_txt => tbh_rec.school_use_txt,
x_expect_family_contribute_amt => tbh_rec.expect_family_contribute_amt,
x_borower_electronic_sign_flag => tbh_rec.borower_electronic_sign_flag,
x_borower_credit_authoriz_flag => tbh_rec.borower_credit_authoriz_flag ,
x_esign_src_typ_cd => tbh_rec.esign_src_typ_cd
);