[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_resp_edit(p_dlpnr_id igf_sl_dl_pnote_resp_all.dlpnr_id%TYPE,
p_status igf_sl_dl_pnote_resp_all.status%TYPE);
SELECT RTRIM(SUBSTR(record_data, 23, 23)) batch_id,
RTRIM(SUBSTR(record_data, 15, 8)) message_class,
RTRIM(SUBSTR(record_data, 46, 16)) bth_creation_date,
RTRIM(SUBSTR(record_data, 60, 2)) batch_rej_code,
RTRIM(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_PNOTE_ACK';
SELECT lort_id last_lort_id,
RTRIM(SUBSTR(record_data,15,7)) number_rec,
RTRIM(SUBSTR(record_data,22,5)) accept_rec,
RTRIM(SUBSTR(record_data,27,5)) reject_rec,
RTRIM(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_PNOTE_ACK';
SELECT ci_cal_type, ci_sequence_number
FROM igf_sl_dl_setup
WHERE dl_version = cp_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 => 'Y'
);
SELECT record_data
FROM igf_sl_load_file_t
WHERE lort_id between 2 AND (l_last_lort_id-1)
AND file_type = 'DL_PNOTE_ACK';
igf_sl_dl_pnote_resp_pkg.insert_row (
x_rowid => lv_rowid,
x_dlpnr_id => l_dlpnr_id,
x_dbth_id => l_dbth_id,
x_pnote_ack_date => TO_DATE(RTRIM(SUBSTR(orec.record_data, 1,8)),'YYYYMMDD'),
x_pnote_batch_id => RTRIM(SUBSTR(orec.record_data, 9,23)),
x_loan_number => RTRIM(SUBSTR(orec.record_data, 32,21)),
x_pnote_status => RTRIM(SUBSTR(orec.record_data, 53,1)),
x_pnote_rej_codes => RTRIM(SUBSTR(orec.record_data, 54,10)),
x_mpn_ind => RTRIM(SUBSTR(orec.record_data, 164,21)),
x_pnote_accept_amt => LTRIM(RTRIM(SUBSTR(orec.record_data, 185,5))),
x_elec_mpn_ind => l_c_elec_mpn_ind,
x_status => 'N',
x_mode => 'R'
);
igf_sl_dl_pdet_resp_pkg.insert_row (
x_mode => 'R',
x_rowid => lc_rowid,
x_dlpnr_id => l_dlpnr_id,
x_dlpdr_id => i + 1,
x_disb_gross_amt => TO_NUMBER(RTRIM(SUBSTR(orec.record_data, 64 + (i * 5),5))));
igf_sl_dl_pnote_resp_pkg.insert_row (
x_rowid => lv_rowid,
x_dlpnr_id => l_dlpnr_id,
x_dbth_id => l_dbth_id,
x_pnote_ack_date => TO_DATE(RTRIM(SUBSTR(orec.record_data, 1,8)),'YYYYMMDD'),
x_pnote_batch_id => RTRIM(SUBSTR(orec.record_data, 9,23)),
x_loan_number => RTRIM(SUBSTR(orec.record_data, 32,21)),
x_pnote_status => RTRIM(SUBSTR(orec.record_data, 53,1)),
x_pnote_rej_codes => RTRIM(SUBSTR(orec.record_data, 54,10)),
x_mpn_ind => RTRIM(SUBSTR(orec.record_data, 164,21)),
x_pnote_accept_amt => NULL,
x_elec_mpn_ind => l_c_elec_mpn_ind,
x_status => 'N',
x_mode => 'R'
);
l_rec_updated VARCHAR2(10);
SELECT igf_sl_dl_batch.* FROM igf_sl_dl_batch
WHERE dbth_id = l_dbth_id;
SELECT igf_sl_dl_pnote_resp.* FROM igf_sl_dl_pnote_resp
WHERE dbth_id = l_dbth_id
AND status = 'N';
SELECT fed_fund_code, count(*) countcol
FROM igf_sl_loans_v loans
WHERE loan_number=p_loan_number
GROUP BY fed_fund_code;
SELECT loan_id FROM
igf_sl_loans
WHERE trim(loan_number)=trim(p_loan_number);
SELECT igf_sl_wf_process_s.NEXTVAL
FROM DUAL;
Rec_no_update EXCEPTION;
FOR dbth_rec IN cur_batch LOOP -- ## Outer Loop for selecting from Batch Table
FOR resp_rec IN cur_pnote_resp(l_dbth_id) LOOP -- ## Selects records from PNOTE_ERSP table
BEGIN
g_log_start_flag := FALSE;
l_rec_updated := 'N';
update_resp_edit(resp_rec.dlpnr_id,'I'); -- ## Set the status as Invalid Loan
SELECT lookup_code, meaning FROM igf_lookups_view
WHERE lookup_type = 'IGF_SL_PNOTE_REJ_CODES'
AND lookup_code IN (LTRIM(RTRIM(SUBSTR(resp_rec.pnote_rej_codes, 1,2))),
LTRIM(RTRIM(SUBSTR(resp_rec.pnote_rej_codes, 3,2))),
LTRIM(RTRIM(SUBSTR(resp_rec.pnote_rej_codes, 5,2))),
LTRIM(RTRIM(SUBSTR(resp_rec.pnote_rej_codes, 7,2))),
LTRIM(RTRIM(SUBSTR(resp_rec.pnote_rej_codes, 9,2))));
igf_sl_edit.delete_edit(resp_rec.loan_number, 'P');
igf_sl_edit.insert_edit(resp_rec.loan_number, 'P', 'IGF_SL_PNOTE_REJ_CODES',
rrec.lookup_code, '', '');
update_resp_edit(resp_rec.dlpnr_id,'I');
SELECT igf_sl_lor.* FROM igf_sl_lor
WHERE loan_id = (SELECT loan_id FROM igf_sl_loans lar
WHERE loan_number = resp_rec.loan_number)
FOR UPDATE NOWAIT ;
update_resp_edit(resp_rec.dlpnr_id,'U');
RAISE Rec_no_update;
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 => 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 => 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_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_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 => NULL,
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 => tbh_rec.p_permt_addr_chg_date,
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 => tbh_rec.s_permt_addr_chg_date,
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_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_requested_loan_amt => tbh_rec.requested_loan_amt,
x_eft_authorization_code => tbh_rec.eft_authorization_code,
x_external_loan_id_txt => tbh_rec.external_loan_id_txt,
x_deferment_request_code => tbh_rec.deferment_request_code ,
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
);
SELECT NVL(loan_amt_offered,loan_amt_accepted) loan_amt FROM
igf_sl_loans_v WHERE
loan_number = TRIM(p_loan_number);
update_resp_edit(resp_rec.dlpnr_id,'Y');
WHEN Rec_no_update THEN
NULL;
PROCEDURE update_resp_edit(p_dlpnr_id igf_sl_dl_pnote_resp_all.dlpnr_id%TYPE,p_status igf_sl_dl_pnote_resp_all.status%TYPE) IS
CURSOR c_tbh_cur IS
SELECT resp.* FROM igf_sl_dl_pnote_resp resp
WHERE dlpnr_id = p_dlpnr_id
FOR UPDATE NOWAIT;
Purpose : Procedure to Update the PNOTE_RESP table
Know limitations, enhancements or remarks
Change History
Who When What
masehgal 19-Feb-2002 # 2216956 FACR007
Added Elec_mpn_ind
(reverse chronological order - newest change first)
***************************************************************/
BEGIN
FOR tbh_cur IN c_tbh_cur LOOP
igf_sl_dl_pnote_resp_pkg.update_row (
x_mode => 'R',
x_rowid => tbh_cur.row_id,
x_dlpnr_id => tbh_cur.dlpnr_id,
x_dbth_id => tbh_cur.dbth_id,
x_pnote_ack_date => tbh_cur.pnote_ack_date,
x_pnote_batch_id => tbh_cur.pnote_batch_id,
x_loan_number => tbh_cur.loan_number,
x_pnote_status => tbh_cur.pnote_status,
x_pnote_rej_codes => tbh_cur.pnote_rej_codes,
x_mpn_ind => tbh_cur.mpn_ind,
x_pnote_accept_amt => tbh_cur.pnote_accept_amt,
x_elec_mpn_ind => tbh_cur.elec_mpn_ind,
x_status => p_status
);
END update_resp_edit;
SELECT award_id, NVL(COUNT(disb_num),0) FROM igf_aw_awd_disb
WHERE award_id = (SELECT award_id FROM igf_sl_loans
WHERE loan_number = p_loan_number)
GROUP BY award_id;
SELECT NVL(COUNT(resp.dlpdr_id),0) FROM igf_sl_dl_pdet_resp resp
WHERE dlpnr_id = loaded_1rec.dlpnr_id;
SELECT disb_num, disb_gross_amt FROM
((
SELECT disb_num, NVL(disb_accepted_amt,0) disb_gross_amt FROM igf_aw_awd_disb adisb
WHERE award_id = l_award_id
MINUS
SELECT dlpdr_id, disb_gross_amt FROM igf_sl_dl_pdet_resp resp
WHERE dlpnr_id = loaded_1rec.dlpnr_id
)
UNION
(SELECT dlpdr_id, disb_gross_amt FROM igf_sl_dl_pdet_resp resp
WHERE dlpnr_id = loaded_1rec.dlpnr_id
MINUS
SELECT disb_num, NVL(disb_accepted_amt,0) disb_gross_amt FROM igf_aw_awd_disb adisb
WHERE award_id = l_award_id
)
);
SELECT * FROM igf_sl_dl_pdet_resp resp
WHERE dlpnr_id = loaded_1rec.dlpnr_id
ORDER By dlpnr_id;
SELECT * FROM igf_aw_awd_disb
WHERE award_id = l_award_id
ORDER BY disb_num;
SELECT adisb.* FROM igf_aw_awd_disb adisb
WHERE award_id = p_award_id and
trans_type = 'P';
SELECT COUNT(row_id)
FROM igf_db_disb_holds
WHERE award_id = cp_award_id
AND disb_num = cp_disb_num
AND hold = cp_hold
AND release_flag ='N';
igf_db_disb_holds_pkg.insert_row (
x_mode => 'R',
x_rowid => l_rowid,
x_hold_id => l_hold_id,
x_award_id => tbh_rec.award_id,
x_disb_num => tbh_rec.disb_num,
x_hold => 'DL_PROM',
x_hold_type => 'SYSTEM',
x_hold_date => TRUNC(sysdate),
x_release_flag => 'N',
x_release_reason => NULL,
x_release_date => NULL
);