[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
veramach 23-SEP-2003 Bug 3104228: 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
(reverse chronological order - newest change first)
***************************************************************/
PROCEDURE send_notif(
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT NOCOPY VARCHAR2 )
AS
/*************************************************************
Created By : viramali
Date Created On : 2001/05/15
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
smvk 21-Feb-2003 Bug # 2758823. Work flow event is triggered with proper lookupcode / meaning
(reverse chronological order - newest change first)
***************************************************************/
l_batchid igf_sl_dl_batch.dbth_id%TYPE;
SELECT loan_number,
full_name,
s_ssn,
person_number
FROM igf_sl_loans_v
WHERE loan_id = l_loanid ;
SELECT igf_sl_dl_pnote_resp.pnote_rej_codes
FROM igf_sl_dl_pnote_resp
WHERE dbth_id = l_batchid
AND loan_number = (SELECT loan_number
FROM igf_sl_loans
WHERE loan_id = l_loanid);
SELECT lookup_code, meaning
FROM igf_lookups_view
WHERE lookup_type = 'IGF_SL_PNOTE_REJ_CODES'
AND lookup_code IN (LTRIM(RTRIM(SUBSTR(l_reject_code, 1,2))),
LTRIM(RTRIM(SUBSTR(l_reject_code, 3,2))),
LTRIM(RTRIM(SUBSTR(l_reject_code, 5,2))),
LTRIM(RTRIM(SUBSTR(l_reject_code, 7,2))),
LTRIM(RTRIM(SUBSTR(l_reject_code, 9,2))));
veramach 23-SEP-2003 Bug 3104228: 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
(reverse chronological order - newest change first)
***************************************************************/
l_loanid igf_sl_loans_v.loan_id%TYPE;
SELECT * FROM igf_sl_lor
WHERE loan_id = l_loanid
AND pnote_status = 'R'
FOR UPDATE OF igf_sl_lor.pnote_status NOWAIT;
igf_sl_lor_pkg.update_row( x_mode => 'R',
x_rowid => l_tbh_rec.row_id,
x_origination_id => l_tbh_rec.origination_id,
x_loan_id => l_tbh_rec.loan_id,
x_sch_cert_date => l_tbh_rec.sch_cert_date,
x_orig_status_flag => l_tbh_rec.orig_status_flag,
x_orig_batch_id => l_tbh_rec.orig_batch_id,
x_orig_batch_date => l_tbh_rec.orig_batch_date,
x_chg_batch_id => NULL,
x_orig_ack_date => l_tbh_rec.orig_ack_date,
x_credit_override => l_tbh_rec.credit_override,
x_credit_decision_date => l_tbh_rec.credit_decision_date,
x_req_serial_loan_code => l_tbh_rec.req_serial_loan_code,
x_act_serial_loan_code => l_tbh_rec.act_serial_loan_code,
x_pnote_delivery_code => l_tbh_rec.pnote_delivery_code,
x_pnote_status => 'S',
x_pnote_status_date => TRUNC(SYSDATE),
x_pnote_id => l_tbh_rec.pnote_id,
x_pnote_print_ind => l_tbh_rec.pnote_print_ind,
x_pnote_accept_amt => l_tbh_rec.pnote_accept_amt,
x_pnote_accept_date => l_tbh_rec.pnote_accept_date,
x_pnote_batch_id => l_tbh_rec.pnote_batch_id,
x_pnote_ack_date => l_tbh_rec.pnote_ack_date,
x_pnote_mpn_ind => l_tbh_rec.pnote_mpn_ind,
x_unsub_elig_for_heal => l_tbh_rec.unsub_elig_for_heal,
x_disclosure_print_ind => l_tbh_rec.disclosure_print_ind,
x_orig_fee_perct => l_tbh_rec.orig_fee_perct,
x_borw_confirm_ind => l_tbh_rec.borw_confirm_ind,
x_borw_interest_ind => l_tbh_rec.borw_interest_ind,
x_borw_outstd_loan_code => l_tbh_rec.borw_outstd_loan_code,
x_unsub_elig_for_depnt => l_tbh_rec.unsub_elig_for_depnt,
x_guarantee_amt => l_tbh_rec.guarantee_amt,
x_guarantee_date => l_tbh_rec.guarantee_date,
x_guarnt_amt_redn_code => l_tbh_rec.guarnt_amt_redn_code,
x_guarnt_status_code => l_tbh_rec.guarnt_status_code,
x_guarnt_status_date => l_tbh_rec.guarnt_status_date,
x_lend_apprv_denied_code => NULL,
x_lend_apprv_denied_date => NULL,
x_lend_status_code => l_tbh_rec.lend_status_code,
x_lend_status_date => l_tbh_rec.lend_status_date,
x_guarnt_adj_ind => l_tbh_rec.guarnt_adj_ind,
x_grade_level_code => l_tbh_rec.grade_level_code,
x_enrollment_code => l_tbh_rec.enrollment_code,
x_anticip_compl_date => l_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 => l_tbh_rec.prc_type_code,
x_cl_seq_number => l_tbh_rec.cl_seq_number,
x_last_resort_lender => l_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 => l_tbh_rec.rec_type_ind,
x_cl_loan_type => l_tbh_rec.cl_loan_type,
x_cl_rec_status => NULL,
x_cl_rec_status_last_update => NULL,
x_alt_prog_type_code => l_tbh_rec.alt_prog_type_code,
x_alt_appl_ver_code => l_tbh_rec.alt_appl_ver_code,
x_mpn_confirm_code => NULL,
x_resp_to_orig_code => l_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 => l_tbh_rec.tot_outstd_stafford,
x_tot_outstd_plus => l_tbh_rec.tot_outstd_plus,
x_alt_borw_tot_debt => l_tbh_rec.alt_borw_tot_debt,
x_act_interest_rate => l_tbh_rec.act_interest_rate,
x_service_type_code => l_tbh_rec.service_type_code,
x_rev_notice_of_guarnt => l_tbh_rec.rev_notice_of_guarnt,
x_sch_refund_amt => l_tbh_rec.sch_refund_amt,
x_sch_refund_date => l_tbh_rec.sch_refund_date,
x_uniq_layout_vend_code => l_tbh_rec.uniq_layout_vend_code,
x_uniq_layout_ident_code => l_tbh_rec.uniq_layout_ident_code,
x_p_person_id => l_tbh_rec.p_person_id,
x_p_ssn_chg_date => NULL,
x_p_dob_chg_date => NULL,
x_p_permt_addr_chg_date => l_tbh_rec.p_permt_addr_chg_date,
x_p_default_status => l_tbh_rec.p_default_status,
x_p_signature_code => l_tbh_rec.p_signature_code,
x_p_signature_date => l_tbh_rec.p_signature_date,
x_s_ssn_chg_date => NULL,
x_s_dob_chg_date => NULL,
x_s_permt_addr_chg_date => l_tbh_rec.s_permt_addr_chg_date,
x_s_local_addr_chg_date => NULL,
x_s_default_status => l_tbh_rec.s_default_status,
x_s_signature_code => l_tbh_rec.s_signature_code,
x_elec_mpn_ind => l_tbh_rec.elec_mpn_ind,
x_borr_sign_ind => l_tbh_rec.borr_sign_ind,
x_stud_sign_ind => l_tbh_rec.stud_sign_ind,
x_borr_credit_auth_code => l_tbh_rec.borr_credit_auth_code,
x_relationship_cd => l_tbh_rec.relationship_cd,
x_interest_rebate_percent_num => l_tbh_rec.interest_rebate_percent_num,
x_cps_trans_num => l_tbh_rec.cps_trans_num,
x_atd_entity_id_txt => l_tbh_rec.atd_entity_id_txt ,
x_rep_entity_id_txt => l_tbh_rec.rep_entity_id_txt,
x_crdt_decision_status => l_tbh_rec.crdt_decision_status,
x_note_message => l_tbh_rec.note_message,
x_book_loan_amt => l_tbh_rec.book_loan_amt ,
x_book_loan_amt_date => l_tbh_rec.book_loan_amt_date,
x_pymt_servicer_amt => l_tbh_rec.pymt_servicer_amt,
x_pymt_servicer_date => l_tbh_rec.pymt_servicer_date,
x_requested_loan_amt => l_tbh_rec.requested_loan_amt,
x_eft_authorization_code => l_tbh_rec.eft_authorization_code,
x_external_loan_id_txt => l_tbh_rec.external_loan_id_txt,
x_deferment_request_code => l_tbh_rec.deferment_request_code ,
x_actual_record_type_code => l_tbh_rec.actual_record_type_code,
x_reinstatement_amt => l_tbh_rec.reinstatement_amt,
x_school_use_txt => l_tbh_rec.school_use_txt,
x_lender_use_txt => l_tbh_rec.lender_use_txt,
x_guarantor_use_txt => l_tbh_rec.guarantor_use_txt,
x_fls_approved_amt => l_tbh_rec.fls_approved_amt,
x_flu_approved_amt => l_tbh_rec.flu_approved_amt,
x_flp_approved_amt => l_tbh_rec.flp_approved_amt,
x_alt_approved_amt => l_tbh_rec.alt_approved_amt,
x_loan_app_form_code => l_tbh_rec.loan_app_form_code,
x_override_grade_level_code => l_tbh_rec.override_grade_level_code,
x_b_alien_reg_num_txt => l_tbh_rec.b_alien_reg_num_txt,
x_esign_src_typ_cd => l_tbh_rec.esign_src_typ_cd,
x_acad_begin_date => l_tbh_rec.acad_begin_date,
x_acad_end_date => l_tbh_rec.acad_end_date
);
veramach 23-SEP-2003 Bug 3104228: 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
(reverse chronological order - newest change first)
***************************************************************/
l_loanid igf_sl_loans_v.loan_id%TYPE;
SELECT * FROM igf_sl_lor
WHERE loan_id = l_loanid
AND pnote_status = 'R'
FOR UPDATE OF igf_sl_lor.pnote_status NOWAIT;
igf_sl_lor_pkg.update_row(
x_mode => 'R',
x_rowid => l_tbh_rec.row_id,
x_origination_id => l_tbh_rec.origination_id,
x_loan_id => l_tbh_rec.loan_id,
x_sch_cert_date => l_tbh_rec.sch_cert_date,
x_orig_status_flag => l_tbh_rec.orig_status_flag,
x_orig_batch_id => l_tbh_rec.orig_batch_id,
x_orig_batch_date => l_tbh_rec.orig_batch_date,
x_chg_batch_id => NULL,
x_orig_ack_date => l_tbh_rec.orig_ack_date,
x_credit_override => l_tbh_rec.credit_override,
x_credit_decision_date => l_tbh_rec.credit_decision_date,
x_req_serial_loan_code => l_tbh_rec.req_serial_loan_code,
x_act_serial_loan_code => l_tbh_rec.act_serial_loan_code,
x_pnote_delivery_code => l_tbh_rec.pnote_delivery_code,
x_pnote_status => 'G',
x_pnote_status_date => TRUNC(SYSDATE),
x_pnote_id => l_tbh_rec.pnote_id,
x_pnote_print_ind => l_tbh_rec.pnote_print_ind,
x_pnote_accept_amt => l_tbh_rec.pnote_accept_amt,
x_pnote_accept_date => l_tbh_rec.pnote_accept_date,
x_pnote_batch_id => l_tbh_rec.pnote_batch_id,
x_pnote_ack_date => l_tbh_rec.pnote_ack_date,
x_pnote_mpn_ind => l_tbh_rec.pnote_mpn_ind,
x_unsub_elig_for_heal => l_tbh_rec.unsub_elig_for_heal,
x_disclosure_print_ind => l_tbh_rec.disclosure_print_ind,
x_orig_fee_perct => l_tbh_rec.orig_fee_perct,
x_borw_confirm_ind => l_tbh_rec.borw_confirm_ind,
x_borw_interest_ind => l_tbh_rec.borw_interest_ind,
x_borw_outstd_loan_code => l_tbh_rec.borw_outstd_loan_code,
x_unsub_elig_for_depnt => l_tbh_rec.unsub_elig_for_depnt,
x_guarantee_amt => l_tbh_rec.guarantee_amt,
x_guarantee_date => l_tbh_rec.guarantee_date,
x_guarnt_amt_redn_code => l_tbh_rec.guarnt_amt_redn_code,
x_guarnt_status_code => l_tbh_rec.guarnt_status_code,
x_guarnt_status_date => l_tbh_rec.guarnt_status_date,
x_lend_apprv_denied_code => NULL,
x_lend_apprv_denied_date => NULL,
x_lend_status_code => l_tbh_rec.lend_status_code,
x_lend_status_date => l_tbh_rec.lend_status_date,
x_guarnt_adj_ind => l_tbh_rec.guarnt_adj_ind,
x_grade_level_code => l_tbh_rec.grade_level_code,
x_enrollment_code => l_tbh_rec.enrollment_code,
x_anticip_compl_date => l_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 => l_tbh_rec.prc_type_code,
x_cl_seq_number => l_tbh_rec.cl_seq_number,
x_last_resort_lender => l_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 => l_tbh_rec.rec_type_ind,
x_cl_loan_type => l_tbh_rec.cl_loan_type,
x_cl_rec_status => NULL,
x_cl_rec_status_last_update => NULL,
x_alt_prog_type_code => l_tbh_rec.alt_prog_type_code,
x_alt_appl_ver_code => l_tbh_rec.alt_appl_ver_code,
x_mpn_confirm_code => NULL,
x_resp_to_orig_code => l_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 => l_tbh_rec.tot_outstd_stafford,
x_tot_outstd_plus => l_tbh_rec.tot_outstd_plus,
x_alt_borw_tot_debt => l_tbh_rec.alt_borw_tot_debt,
x_act_interest_rate => l_tbh_rec.act_interest_rate,
x_service_type_code => l_tbh_rec.service_type_code,
x_rev_notice_of_guarnt => l_tbh_rec.rev_notice_of_guarnt,
x_sch_refund_amt => l_tbh_rec.sch_refund_amt,
x_sch_refund_date => l_tbh_rec.sch_refund_date,
x_uniq_layout_vend_code => l_tbh_rec.uniq_layout_vend_code,
x_uniq_layout_ident_code => l_tbh_rec.uniq_layout_ident_code,
x_p_person_id => l_tbh_rec.p_person_id,
x_p_ssn_chg_date => NULL,
x_p_dob_chg_date => NULL,
x_p_permt_addr_chg_date => l_tbh_rec.p_permt_addr_chg_date,
x_p_default_status => l_tbh_rec.p_default_status,
x_p_signature_code => l_tbh_rec.p_signature_code,
x_p_signature_date => l_tbh_rec.p_signature_date,
x_s_ssn_chg_date => NULL,
x_s_dob_chg_date => NULL,
x_s_permt_addr_chg_date => l_tbh_rec.s_permt_addr_chg_date,
x_s_local_addr_chg_date => NULL,
x_s_default_status => l_tbh_rec.s_default_status,
x_s_signature_code => l_tbh_rec.s_signature_code,
x_elec_mpn_ind => l_tbh_rec.elec_mpn_ind,
x_borr_sign_ind => l_tbh_rec.borr_sign_ind,
x_stud_sign_ind => l_tbh_rec.stud_sign_ind,
x_borr_credit_auth_code => l_tbh_rec.borr_credit_auth_code,
x_relationship_cd => l_tbh_rec.relationship_cd,
x_interest_rebate_percent_num => l_tbh_rec.interest_rebate_percent_num,
x_cps_trans_num => l_tbh_rec.cps_trans_num,
x_atd_entity_id_txt => l_tbh_rec.atd_entity_id_txt ,
x_rep_entity_id_txt => l_tbh_rec.rep_entity_id_txt,
x_crdt_decision_status => l_tbh_rec.crdt_decision_status,
x_note_message => l_tbh_rec.note_message,
x_book_loan_amt => l_tbh_rec.book_loan_amt ,
x_book_loan_amt_date => l_tbh_rec.book_loan_amt_date,
x_pymt_servicer_amt => l_tbh_rec.pymt_servicer_amt,
x_pymt_servicer_date => l_tbh_rec.pymt_servicer_date,
x_requested_loan_amt => l_tbh_rec.requested_loan_amt,
x_eft_authorization_code => l_tbh_rec.eft_authorization_code,
x_external_loan_id_txt => l_tbh_rec.external_loan_id_txt,
x_deferment_request_code => l_tbh_rec.deferment_request_code ,
x_actual_record_type_code => l_tbh_rec.actual_record_type_code,
x_reinstatement_amt => l_tbh_rec.reinstatement_amt,
x_school_use_txt => l_tbh_rec.school_use_txt,
x_lender_use_txt => l_tbh_rec.lender_use_txt,
x_guarantor_use_txt => l_tbh_rec.guarantor_use_txt,
x_fls_approved_amt => l_tbh_rec.fls_approved_amt,
x_flu_approved_amt => l_tbh_rec.flu_approved_amt,
x_flp_approved_amt => l_tbh_rec.flp_approved_amt,
x_alt_approved_amt => l_tbh_rec.alt_approved_amt,
x_loan_app_form_code => l_tbh_rec.loan_app_form_code,
x_override_grade_level_code => l_tbh_rec.override_grade_level_code,
x_b_alien_reg_num_txt => l_tbh_rec.b_alien_reg_num_txt,
x_esign_src_typ_cd => l_tbh_rec.esign_src_typ_cd,
x_acad_begin_date => l_tbh_rec.acad_begin_date,
x_acad_end_date => l_tbh_rec.acad_end_date
);