[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE proc_update_loan_rec(p_loan_rec igf_sl_loans%ROWTYPE);
PROCEDURE proc_update_clchsn_dtls_rec(p_v_loan_number IN igf_sl_loans.loan_number%TYPE ,
p_v_change_record_typ IN igf_sl_clchsn_dtls.change_record_type_txt%TYPE ,
p_n_disb_num IN igf_aw_awd_disb_all.disb_num%TYPE ,
p_v_send_record_txt IN igf_sl_clchsn_dtls.send_record_txt%TYPE
);
SELECT fmast.description fund_desc
,fmast.ci_cal_type
,fmast.ci_sequence_number
FROM igf_aw_fund_mast_all fmast
WHERE fmast.fund_id = cp_n_fund_id;
SELECT lar.loan_number
,lar.award_id
FROM igf_sl_loans_all lar
WHERE lar.loan_id = cp_n_loan_id;
SELECT pig.group_cd
FROM igs_pe_persid_group_all pig
WHERE pig.group_id = cp_n_person_id_grp;
SELECT lkups.meaning
FROM igf_aw_lookups_view lkups
WHERE lkups.lookup_type = cp_v_lookup_type
AND lkups.lookup_code = cp_v_lookup_code
AND lkups.cal_type = cp_v_cal_type
AND lkups.sequence_number = cp_n_sequence_number
AND lkups.enabled_flag = 'Y'
ORDER BY lookup_code;
SELECT alternate_identifier
,system_id_type
FROM igf_sl_school_codes_v
WHERE alternate_identifier = cp_v_school_id
ORDER BY alternate_identifier;
SELECT meaning
FROM igf_ap_school_opeid_v
WHERE alternate_identifier = cp_v_school_id;
SELECT 'x'
FROM igf_sl_school_codes_v
WHERE system_id_type = cp_v_non_ed_branch
AND alternate_identifier = cp_v_alt_identifier
ORDER BY alternate_identifier;
SELECT DISTINCT lor.relationship_cd
FROM igf_sl_lor_all lor
,igf_sl_loans_all loans
,igf_aw_award_all awd
,igf_aw_fund_mast_all fmast
,igf_sl_cl_recipient recip
WHERE loans.loan_id = lor.loan_id
AND loans.loan_chg_status = cp_c_loan_chg_status
AND awd.award_id = loans.award_id
AND fmast.fund_id = awd.fund_id
AND fmast.ci_cal_type = cp_v_cal_type
AND fmast.ci_sequence_number = cp_n_sequence_number
AND SUBSTR(loans.loan_number,1, 6) = SUBSTR(cp_v_school_id,1,6)
AND recip.relationship_cd = lor.relationship_cd;
SELECT base_id
FROM igf_ap_fa_base_rec_all fabase
WHERE fabase.person_id = cp_n_person_id
AND fabase.ci_cal_type = cp_v_cal_type
AND fabase.ci_sequence_number = cp_n_sequence_number;
SELECT person_number
FROM igs_pe_person_base_v
WHERE person_id = cp_n_person_id;
SELECT COUNT(awd.disb_num) tot_disb
FROM igf_aw_awd_disb_all awd
WHERE awd.award_id = cp_n_award_id
GROUP BY awd.award_id
HAVING COUNT(awd.disb_num) > 4;
SELECT chdt.ROWID row_id
,chdt.*
FROM igf_sl_clchsn_dtls chdt
WHERE chdt.loan_number_txt = cp_v_loan_number
AND chdt.status_code ='R';
SELECT loans.row_id
,loans.loan_id
,loans.award_id
,loans.seq_num
,loans.loan_number
,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
,loans.borw_detrm_code
,loans.legacy_record_flag
,loans.external_loan_id_txt
,lor.anticip_compl_date
,lor.sch_cert_date
,lor.prc_type_code
,lor.cl_rec_status
,lor.relationship_cd
,awd.base_id
,fmast.ci_cal_type
,fmast.ci_sequence_number
,fmast.fund_id
,fmast.fund_code
,fmast.discontinue_fund
,fcat.fed_fund_code
,clset.cl_version
FROM igf_sl_lor_all lor
,igf_sl_loans loans
,igf_aw_award_all awd
,igf_aw_fund_mast_all fmast
,igf_aw_fund_cat_all fcat
,igf_sl_cl_setup_all clset
WHERE ((lor.loan_id = cp_n_loan_id) OR cp_n_loan_id IS NULL)
AND loans.loan_id = lor.loan_id
AND awd.award_id = loans.award_id
AND ((awd.base_id = cp_n_base_id) OR cp_n_base_id IS NULL)
AND ((awd.fund_id = cp_n_fund_id) OR cp_n_fund_id IS NULL)
AND fmast.fund_id = awd.fund_id
AND fmast.ci_cal_type = cp_v_cal_type
AND fmast.ci_sequence_number = cp_n_sequence_number
AND fcat.fund_code = fmast.fund_code
AND clset.ci_cal_type = fmast.ci_cal_type
AND clset.ci_sequence_number = fmast.ci_sequence_number
AND clset.relationship_cd = lor.relationship_cd
AND loans.loan_chg_status = 'G'
AND EXISTS (SELECT '1'
FROM igf_ap_fa_base_rec_all fabase
WHERE fabase.base_id = awd.base_id
AND fabase.ci_cal_type = fmast.ci_cal_type
AND fabase.ci_sequence_number = fmast.ci_sequence_number
);
p_v_string => ' Invoking igf_sl_clchsn_dtls_pkg.update_row to update the status to Not Ready to Send'
);
igf_sl_clchsn_dtls_pkg.update_row
(
x_rowid => rec_c_sl_clchsn_dtls.row_id ,
x_clchgsnd_id => rec_c_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'N' ,
x_status_date => rec_c_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_sl_clchsn_dtls.old_date ,
x_new_date => rec_c_sl_clchsn_dtls.new_date ,
x_old_amt => rec_c_sl_clchsn_dtls.old_amt ,
x_new_amt => rec_c_sl_clchsn_dtls.new_amt ,
x_disbursement_number => rec_c_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => rec_c_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => l_v_message_text ,
x_send_record_txt => rec_c_sl_clchsn_dtls.send_record_txt
);
p_v_string => ' Updated the status of change send record to Not Ready to Send'
);
proc_update_loan_rec(p_loan_rec => rec_sl_loans);
p_v_string => ' update row of loans table to update the status to Not ready to Sent for : ' ||rec_c_lor_lar.loan_number
);
proc_update_loan_rec(p_loan_rec => rec_sl_loans);
PROCEDURE proc_update_loan_rec(p_loan_rec igf_sl_loans%ROWTYPE) AS
------------------------------------------------------------------
--Created by : Sanil Madathil, Oracle IDC
--Date created: 25 October 2004
--
-- Purpose:
-- Invoked : from within identify_clchsn_dtls procedure
-- Function : Private procedure to update the loans table
-- to 'V' or 'N'
-- Parameters : p_loan_rec : IN parameter. Required.
--
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
------------------------------------------------------------------
l_loan_rec igf_sl_loans%ROWTYPE;
log_to_fnd(p_v_module => 'proc_update_loan_rec',
p_v_string => ' Entered Procedure proc_update_loan_rec: The input parameters are '||
' p_loan_rec.loan_id : ' ||p_loan_rec.loan_id ||
' p_loan_rec.loan_chg_status : ' ||p_loan_rec.loan_chg_status
);
igf_sl_loans_pkg.update_row (
x_rowid => l_loan_rec.row_id ,
x_loan_id => l_loan_rec.loan_id ,
x_award_id => l_loan_rec.award_id ,
x_seq_num => l_loan_rec.seq_num ,
x_loan_number => l_loan_rec.loan_number ,
x_loan_per_begin_date => l_loan_rec.loan_per_begin_date ,
x_loan_per_end_date => l_loan_rec.loan_per_end_date ,
x_loan_status => l_loan_rec.loan_status ,
x_loan_status_date => l_loan_rec.loan_status_date ,
x_loan_chg_status => l_loan_rec.loan_chg_status ,
x_loan_chg_status_date => l_loan_rec.loan_chg_status_date ,
x_active => l_loan_rec.active ,
x_active_date => l_loan_rec.active_date ,
x_borw_detrm_code => l_loan_rec.borw_detrm_code ,
x_mode => 'R' ,
x_legacy_record_flag => l_loan_rec.legacy_record_flag ,
x_external_loan_id_txt => l_loan_rec.external_loan_id_txt
);
log_to_fnd(p_v_module => 'proc_update_loan_rec',
p_v_string => ' Updated the loan record successfully ' ||
' l_loan_rec.loan_id : ' ||l_loan_rec.loan_id ||
' l_loan_rec.loan_chg_status : ' ||l_loan_rec.loan_chg_status
);
END proc_update_loan_rec;
SELECT recip.rcpt_id
,recip.lender_id
,recip.lend_non_ed_brc_id
,recip.guarantor_id
,recip.recipient_id
,recip.recipient_type
,recip.recip_non_ed_brc_id
,loans.loan_id
,loans.award_id
,loans.seq_num
,loans.loan_number
,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
,loans.borw_detrm_code
,loans.legacy_record_flag
,loans.external_loan_id_txt
,lor.origination_id
,lor.sch_cert_date
,lor.orig_status_flag
,lor.orig_batch_id
,lor.orig_batch_date
,lor.chg_batch_id
,lor.orig_ack_date
,lor.credit_override
,lor.credit_decision_date
,lor.req_serial_loan_code
,lor.act_serial_loan_code
,lor.pnote_delivery_code
,lor.pnote_status
,lor.pnote_status_date
,lor.pnote_id
,lor.pnote_print_ind
,lor.pnote_accept_amt
,lor.pnote_accept_date
,lor.unsub_elig_for_heal
,lor.disclosure_print_ind
,lor.orig_fee_perct
,lor.borw_confirm_ind
,lor.borw_interest_ind
,lor.borw_outstd_loan_code
,lor.unsub_elig_for_depnt
,lor.guarantee_amt
,lor.guarantee_date
,lor.guarnt_amt_redn_code
,lor.guarnt_status_code
,lor.guarnt_status_date
,lor.lend_apprv_denied_code
,lor.lend_apprv_denied_date
,lor.lend_status_code
,lor.lend_status_date
,lor.guarnt_adj_ind
,lor.grade_level_code
,lor.enrollment_code
,lor.anticip_compl_date
,lor.borw_lender_id
,lor.duns_borw_lender_id
,lor.duns_guarnt_id
,lor.prc_type_code
,lor.cl_seq_number
,lor.last_resort_lender
,lor.duns_lender_id
,lor.duns_recip_id
,lor.rec_type_ind
,lor.cl_loan_type
,lor.cl_rec_status
,lor.cl_rec_status_last_update
,lor.alt_prog_type_code
,lor.alt_appl_ver_code
,lor.mpn_confirm_code
,lor.resp_to_orig_code
,lor.appl_loan_phase_code
,lor.appl_loan_phase_code_chg
,lor.appl_send_error_codes
,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
,DECODE(fcat.fed_fund_code,'FLP',lor.p_person_id,
'FLS',fabase.person_id,
'FLU',fabase.person_id,
'GPLUSFL',fabase.person_id,
'ALT',DECODE(SIGN(lor.p_person_id-fabase.person_id),0, fabase.person_id,lor.p_person_id)) borrower_id
,lor.p_ssn_chg_date
,lor.p_dob_chg_date
,lor.p_permt_addr_chg_date
,lor.p_default_status
,lor.p_signature_code
,lor.p_signature_date
,lor.s_ssn_chg_date
,lor.s_dob_chg_date
,lor.s_permt_addr_chg_date
,lor.s_local_addr_chg_date
,lor.s_default_status
,lor.s_signature_code
,lor.pnote_batch_id
,lor.pnote_ack_date
,lor.pnote_mpn_ind
,lor.elec_mpn_ind
,lor.borr_sign_ind
,lor.stud_sign_ind
,lor.borr_credit_auth_code
,lor.relationship_cd
,lor.cps_trans_num
,lor.crdt_decision_status
,lor.note_message
,lor.book_loan_amt
,lor.book_loan_amt_date
,lor.pymt_servicer_amt
,lor.pymt_servicer_date
,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
,awd.base_id
,awd.accepted_amt loan_amt_accepted
,awd.award_date
,fmast.fund_code
,fcat.fed_fund_code
,fabase.person_id student_id
,TRUNC(fabase.coa_f) coa_f
FROM igf_sl_lor_all lor
,igf_sl_loans_all loans
,igf_aw_award_all awd
,igf_aw_fund_mast_all fmast
,igf_aw_fund_cat_all fcat
,igf_sl_cl_recipient recip
,igf_ap_fa_base_rec_all fabase
WHERE lor.relationship_cd = cp_v_relationship_cd
AND ((lor.loan_id = cp_n_loan_id) OR cp_n_loan_id IS NULL)
AND loans.loan_id = lor.loan_id
AND loans.loan_chg_status = cp_c_loan_chg_status
AND SUBSTR(loans.loan_number,1, 6) = SUBSTR(cp_v_school_id,1,6)
AND awd.award_id = loans.award_id
AND ((awd.base_id = cp_n_base_id) OR cp_n_base_id IS NULL)
AND ((awd.fund_id = cp_n_fund_id) OR cp_n_fund_id IS NULL)
AND fmast.fund_id = awd.fund_id
AND fmast.ci_cal_type = cp_v_cal_type
AND fmast.ci_sequence_number = cp_n_sequence_number
AND fcat.fund_code = fmast.fund_code
AND recip.relationship_cd = lor.relationship_cd
AND fabase.base_id = awd.base_id
AND fabase.ci_cal_type = fmast.ci_cal_type
AND fabase.ci_sequence_number = fmast.ci_sequence_number
ORDER BY borrower_id;
SELECT meaning
FROM igf_lookups_view
WHERE lookup_type = 'IGF_AP_SCHOOL_OPEID'
AND lookup_code = cp_v_school_id;
SELECT recipient_id
,recipient_type
,recip_non_ed_brc_id
FROM igf_sl_cl_recipient
WHERE relationship_cd = cp_v_relationship_cd;
SELECT lender_id
,description
FROM igf_sl_lender
WHERE lender_id = cp_n_recipient_id
AND enabled = 'Y';
SELECT guarantor_id
,description
FROM igf_sl_guarantor
WHERE guarantor_id = cp_n_recipient_id
AND enabled = 'Y';
SELECT servicer_id
,description
FROM igf_sl_servicer
WHERE servicer_id = cp_n_recipient_id
AND enabled = 'Y';
SELECT chdt.ROWID row_id
,chdt.*
FROM igf_sl_clchsn_dtls chdt
WHERE chdt.loan_number_txt = cp_v_loan_number
AND chdt.status_code ='R'
ORDER BY change_record_type_txt,disbursement_number;
SELECT adisb.disb_date
,NVL(adisb.disb_accepted_amt,0) disb_accepted_amt
,adisb.hold_rel_ind
,adisb.disb_num
FROM igf_aw_awd_disb_all adisb
WHERE adisb.award_id = cp_n_award_id
ORDER BY adisb.disb_num;
SELECT adisb.disb_date
,NVL(adisb.disb_accepted_amt,0) disb_accepted_amt
,adisb.hold_rel_ind
,adisb.disb_num
,adisb.fee_1
,adisb.fee_2
,adisb.fee_paid_1
,adisb.fee_paid_2
FROM igf_aw_awd_disb_all adisb
WHERE adisb.award_id = cp_n_award_id
AND adisb.disb_num = cp_n_disb_num
ORDER BY adisb.disb_num;
SELECT TRUNC(SUM(NVL(NVL(awd.accepted_amt,awd.offered_amt),0))) etsimated_fin
FROM igf_aw_award_all awd
WHERE awd.base_id = cp_n_base_id
AND awd.award_id <> cp_n_award_id
AND (awd.award_status IN (cp_awd_status_1,cp_awd_status_2));
SELECT lorloc.*, lorloc.ROWID row_id
FROM igf_sl_lor_loc_all lorloc
WHERE loan_id = cp_n_loan_id
AND origination_id = cp_n_origination_id;
p_v_string => ' Inserting into igf_sl_cl_batch table '
);
igf_sl_cl_batch_pkg.insert_row (
x_mode => 'R' ,
x_rowid => l_v_rowid ,
x_cbth_id => l_n_cbth_id ,
x_batch_id => l_v_batch_id ,
x_file_creation_date => TRUNC(SYSDATE) ,
x_file_trans_date => TRUNC(SYSDATE) ,
x_file_ident_code => RPAD(l_v_file_ident_code,5,' '),
x_recipient_id => l_n_recipient_id ,
x_recip_non_ed_brc_id => l_v_recip_non_edbrc_id ,
x_source_id => l_v_school_id ,
x_source_non_ed_brc_id => l_v_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
);
p_v_string => ' Inserted successfully into igf_sl_cl_batch table '
);
p_v_string => ' Invoking proc_update_clchsn_dtls_rec to update the igf_sl_clchsn_dtls table ' ||
' with the send record text and to move status code to Sent for change send @1-07 record'
);
proc_update_clchsn_dtls_rec(
p_v_loan_number => rec_c_recip_dtls.loan_number ,
p_v_change_record_typ => '07' ,
p_n_disb_num => NULL ,
p_v_send_record_txt => SUBSTR(l_v_chg_01_7_rec,3,(INSTR(l_v_chg_01_7_rec,'*')-3))
);
p_v_string => ' Successfully Invoked proc_update_clchsn_dtls_rec to update the igf_sl_clchsn_dtls table ' ||
' for change send @1-07 record '
);
p_v_string => ' Invoking proc_update_clchsn_dtls_rec to update the igf_sl_clchsn_dtls table ' ||
' with the send record text and to move status code to Sent for change send @1-08 record'
);
proc_update_clchsn_dtls_rec(
p_v_loan_number => rec_c_recip_dtls.loan_number ,
p_v_change_record_typ => '08' ,
p_n_disb_num => NULL ,
p_v_send_record_txt => SUBSTR(l_v_chg_01_8_rec,3,(INSTR(l_v_chg_01_8_rec,'*')-3))
);
p_v_string => ' Successfully Invoked proc_update_clchsn_dtls_rec to update the igf_sl_clchsn_dtls table ' ||
' for change send @1-08 record '
);
p_v_string => ' Invoking proc_update_clchsn_dtls_rec to update the igf_sl_clchsn_dtls table ' ||
' with the send record text and to move status code to Sent for change send @1-09 record'
);
proc_update_clchsn_dtls_rec(
p_v_loan_number => rec_c_recip_dtls.loan_number ,
p_v_change_record_typ => '09' ,
p_n_disb_num => v_tab_rec_09_dtl(l_n_ctr_09).disb_number ,
p_v_send_record_txt => SUBSTR(l_v_chg_01_9_rec,3,(INSTR(l_v_chg_01_9_rec,'*')-3))
);
p_v_string => ' Successfully Invoked proc_update_clchsn_dtls_rec to update the igf_sl_clchsn_dtls table ' ||
' for change send @1-09 record '
);
v_tab_rec_09_dtl.DELETE;
p_v_string => ' Invoking proc_update_clchsn_dtls_rec to update the igf_sl_clchsn_dtls table ' ||
' with the send record text and to move status code to Sent for change send @1-10 record'
);
proc_update_clchsn_dtls_rec(
p_v_loan_number => rec_c_recip_dtls.loan_number ,
p_v_change_record_typ => '10' ,
p_n_disb_num => v_tab_rec_10_dtl(l_n_ctr_10).disb_number ,
p_v_send_record_txt => SUBSTR(l_v_chg_01_10_rec,3,(INSTR(l_v_chg_01_10_rec,'*')-3))
);
p_v_string => ' Successfully Invoked proc_update_clchsn_dtls_rec to update the igf_sl_clchsn_dtls table ' ||
' for change send @1-10 record '
);
v_tab_rec_10_dtl.DELETE;
p_v_string => ' Invoking proc_update_clchsn_dtls_rec to update the igf_sl_clchsn_dtls table ' ||
' with the send record text and to move status code to Sent for change send @1-24 record'
);
proc_update_clchsn_dtls_rec(
p_v_loan_number => rec_c_recip_dtls.loan_number ,
p_v_change_record_typ => '24' ,
p_n_disb_num => NULL ,
p_v_send_record_txt => SUBSTR(l_v_chg_01_24_rec,3,(INSTR(l_v_chg_01_24_rec,'*')-3))
);
p_v_string => ' Successfully Invoked proc_update_clchsn_dtls_rec to update the igf_sl_clchsn_dtls table ' ||
' for change send @1-24 record '
);
p_v_string => 'invoking igf_sl_gen.update_cl_chg_status for loan number ='||rec_c_recip_dtls.loan_number
);
igf_sl_gen.update_cl_chg_status(p_v_loan_number => rec_c_recip_dtls.loan_number);
p_v_string => ' Call out to igf_sl_gen.update_cl_chg_status successful for loan number ='||rec_c_recip_dtls.loan_number
);
p_v_string => ' invoking igf_sl_lor_loc_pkg.update_row for ' ||
' loan id = ' ||rec_c_recip_dtls.loan_id ||
' Origination id = ' ||rec_c_sl_lor_loc.origination_id
);
igf_sl_lor_loc_pkg.update_row (
x_rowid => rec_c_sl_lor_loc.row_id ,
x_loan_id => rec_c_sl_lor_loc.loan_id ,
x_origination_id => rec_c_sl_lor_loc.origination_id ,
x_loan_number => rec_c_sl_lor_loc.loan_number ,
x_loan_type => rec_c_sl_lor_loc.loan_type ,
x_loan_amt_offered => rec_c_sl_lor_loc.loan_amt_offered ,
x_loan_amt_accepted => rec_c_sl_lor_loc.loan_amt_accepted ,
x_loan_per_begin_date => rec_c_sl_lor_loc.loan_per_begin_date ,
x_loan_per_end_date => rec_c_sl_lor_loc.loan_per_end_date ,
x_acad_yr_begin_date => rec_c_sl_lor_loc.acad_yr_begin_date ,
x_acad_yr_end_date => rec_c_sl_lor_loc.acad_yr_end_date ,
x_loan_status => rec_c_sl_lor_loc.loan_status ,
x_loan_status_date => rec_c_sl_lor_loc.loan_status_date ,
x_loan_chg_status => 'S' ,
x_loan_chg_status_date => TRUNC(SYSDATE) ,
x_req_serial_loan_code => rec_c_sl_lor_loc.req_serial_loan_code ,
x_act_serial_loan_code => rec_c_sl_lor_loc.act_serial_loan_code ,
x_active => rec_c_sl_lor_loc.active ,
x_active_date => rec_c_sl_lor_loc.active_date ,
x_sch_cert_date => rec_c_sl_lor_loc.sch_cert_date ,
x_orig_status_flag => rec_c_sl_lor_loc.orig_status_flag ,
x_orig_batch_id => rec_c_sl_lor_loc.orig_batch_id ,
x_orig_batch_date => rec_c_sl_lor_loc.orig_batch_date ,
x_chg_batch_id => rec_c_sl_lor_loc.chg_batch_id ,
x_orig_ack_date => rec_c_sl_lor_loc.orig_ack_date ,
x_credit_override => rec_c_sl_lor_loc.credit_override ,
x_credit_decision_date => rec_c_sl_lor_loc.credit_decision_date ,
x_pnote_delivery_code => rec_c_sl_lor_loc.pnote_delivery_code ,
x_pnote_status => rec_c_sl_lor_loc.pnote_status ,
x_pnote_status_date => rec_c_sl_lor_loc.pnote_status_date ,
x_pnote_id => rec_c_sl_lor_loc.pnote_id ,
x_pnote_print_ind => rec_c_sl_lor_loc.pnote_print_ind ,
x_pnote_accept_amt => rec_c_sl_lor_loc.pnote_accept_amt ,
x_pnote_accept_date => rec_c_sl_lor_loc.pnote_accept_date ,
x_p_signature_code => rec_c_sl_lor_loc.p_signature_code ,
x_p_signature_date => rec_c_sl_lor_loc.p_signature_date ,
x_s_signature_code => rec_c_sl_lor_loc.s_signature_code ,
x_unsub_elig_for_heal => rec_c_sl_lor_loc.unsub_elig_for_heal ,
x_disclosure_print_ind => rec_c_sl_lor_loc.disclosure_print_ind ,
x_orig_fee_perct => rec_c_sl_lor_loc.orig_fee_perct ,
x_borw_confirm_ind => rec_c_sl_lor_loc.borw_confirm_ind ,
x_borw_interest_ind => rec_c_sl_lor_loc.borw_interest_ind ,
x_unsub_elig_for_depnt => rec_c_sl_lor_loc.unsub_elig_for_depnt ,
x_guarantee_amt => rec_c_sl_lor_loc.guarantee_amt ,
x_guarantee_date => rec_c_sl_lor_loc.guarantee_date ,
x_guarnt_adj_ind => rec_c_sl_lor_loc.guarnt_adj_ind ,
x_guarnt_amt_redn_code => rec_c_sl_lor_loc.guarnt_amt_redn_code ,
x_guarnt_status_code => rec_c_sl_lor_loc.guarnt_status_code ,
x_guarnt_status_date => rec_c_sl_lor_loc.guarnt_status_date ,
x_lend_apprv_denied_code => rec_c_sl_lor_loc.lend_apprv_denied_code ,
x_lend_apprv_denied_date => rec_c_sl_lor_loc.lend_apprv_denied_date ,
x_lend_status_code => rec_c_sl_lor_loc.lend_status_code ,
x_lend_status_date => rec_c_sl_lor_loc.lend_status_date ,
x_grade_level_code => rec_c_sl_lor_loc.grade_level_code ,
x_enrollment_code => rec_c_sl_lor_loc.enrollment_code ,
x_anticip_compl_date => rec_c_sl_lor_loc.anticip_compl_date ,
x_borw_lender_id => rec_c_sl_lor_loc.borw_lender_id ,
x_duns_borw_lender_id => rec_c_sl_lor_loc.duns_borw_lender_id ,
x_guarantor_id => rec_c_sl_lor_loc.guarantor_id ,
x_duns_guarnt_id => rec_c_sl_lor_loc.duns_guarnt_id ,
x_prc_type_code => rec_c_sl_lor_loc.prc_type_code ,
x_rec_type_ind => rec_c_sl_lor_loc.rec_type_ind ,
x_cl_loan_type => rec_c_sl_lor_loc.cl_loan_type ,
x_cl_seq_number => rec_c_sl_lor_loc.cl_seq_number ,
x_last_resort_lender => rec_c_sl_lor_loc.last_resort_lender ,
x_lender_id => rec_c_sl_lor_loc.lender_id ,
x_duns_lender_id => rec_c_sl_lor_loc.duns_lender_id ,
x_lend_non_ed_brc_id => rec_c_sl_lor_loc.lend_non_ed_brc_id ,
x_recipient_id => rec_c_sl_lor_loc.recipient_id ,
x_recipient_type => rec_c_sl_lor_loc.recipient_type ,
x_duns_recip_id => rec_c_sl_lor_loc.duns_recip_id ,
x_recip_non_ed_brc_id => rec_c_sl_lor_loc.recip_non_ed_brc_id ,
x_cl_rec_status => rec_c_sl_lor_loc.cl_rec_status ,
x_cl_rec_status_last_update => rec_c_sl_lor_loc.cl_rec_status_last_update ,
x_alt_prog_type_code => rec_c_sl_lor_loc.alt_prog_type_code ,
x_alt_appl_ver_code => rec_c_sl_lor_loc.alt_appl_ver_code ,
x_borw_outstd_loan_code => rec_c_sl_lor_loc.borw_outstd_loan_code ,
x_mpn_confirm_code => rec_c_sl_lor_loc.mpn_confirm_code ,
x_resp_to_orig_code => rec_c_sl_lor_loc.resp_to_orig_code ,
x_appl_loan_phase_code => rec_c_sl_lor_loc.appl_loan_phase_code ,
x_appl_loan_phase_code_chg => rec_c_sl_lor_loc.appl_loan_phase_code_chg ,
x_tot_outstd_stafford => rec_c_sl_lor_loc.tot_outstd_stafford ,
x_tot_outstd_plus => rec_c_sl_lor_loc.tot_outstd_plus ,
x_alt_borw_tot_debt => rec_c_sl_lor_loc.alt_borw_tot_debt ,
x_act_interest_rate => rec_c_sl_lor_loc.act_interest_rate ,
x_service_type_code => rec_c_sl_lor_loc.service_type_code ,
x_rev_notice_of_guarnt => rec_c_sl_lor_loc.rev_notice_of_guarnt ,
x_sch_refund_amt => rec_c_sl_lor_loc.sch_refund_amt ,
x_sch_refund_date => rec_c_sl_lor_loc.sch_refund_date ,
x_uniq_layout_vend_code => rec_c_sl_lor_loc.uniq_layout_vend_code ,
x_uniq_layout_ident_code => rec_c_sl_lor_loc.uniq_layout_ident_code ,
x_p_person_id => rec_c_sl_lor_loc.p_person_id ,
x_p_ssn => rec_c_sl_lor_loc.p_ssn ,
x_p_ssn_chg_date => rec_c_sl_lor_loc.p_ssn_chg_date ,
x_p_last_name => rec_c_sl_lor_loc.p_last_name ,
x_p_first_name => rec_c_sl_lor_loc.p_first_name ,
x_p_middle_name => rec_c_sl_lor_loc.p_middle_name ,
x_p_permt_addr1 => rec_c_sl_lor_loc.p_permt_addr1 ,
x_p_permt_addr2 => rec_c_sl_lor_loc.p_permt_addr2 ,
x_p_permt_city => rec_c_sl_lor_loc.p_permt_city ,
x_p_permt_state => rec_c_sl_lor_loc.p_permt_state ,
x_p_permt_zip => rec_c_sl_lor_loc.p_permt_zip ,
x_p_permt_addr_chg_date => rec_c_sl_lor_loc.p_permt_addr_chg_date ,
x_p_permt_phone => rec_c_sl_lor_loc.p_permt_phone ,
x_p_email_addr => rec_c_sl_lor_loc.p_email_addr ,
x_p_date_of_birth => rec_c_sl_lor_loc.p_date_of_birth ,
x_p_dob_chg_date => rec_c_sl_lor_loc.p_dob_chg_date ,
x_p_license_num => rec_c_sl_lor_loc.p_license_num ,
x_p_license_state => rec_c_sl_lor_loc.p_license_state ,
x_p_citizenship_status => rec_c_sl_lor_loc.p_citizenship_status ,
x_p_alien_reg_num => rec_c_sl_lor_loc.p_alien_reg_num ,
x_p_default_status => rec_c_sl_lor_loc.p_default_status ,
x_p_foreign_postal_code => rec_c_sl_lor_loc.p_foreign_postal_code ,
x_p_state_of_legal_res => rec_c_sl_lor_loc.p_state_of_legal_res ,
x_p_legal_res_date => rec_c_sl_lor_loc.p_legal_res_date ,
x_s_ssn => rec_c_sl_lor_loc.s_ssn ,
x_s_ssn_chg_date => rec_c_sl_lor_loc.s_ssn_chg_date ,
x_s_last_name => rec_c_sl_lor_loc.s_last_name ,
x_s_first_name => rec_c_sl_lor_loc.s_first_name ,
x_s_middle_name => rec_c_sl_lor_loc.s_middle_name ,
x_s_permt_addr1 => rec_c_sl_lor_loc.s_permt_addr1 ,
x_s_permt_addr2 => rec_c_sl_lor_loc.s_permt_addr2 ,
x_s_permt_city => rec_c_sl_lor_loc.s_permt_city ,
x_s_permt_state => rec_c_sl_lor_loc.s_permt_state ,
x_s_permt_zip => rec_c_sl_lor_loc.s_permt_zip ,
x_s_permt_addr_chg_date => rec_c_sl_lor_loc.s_permt_addr_chg_date ,
x_s_permt_phone => rec_c_sl_lor_loc.s_permt_phone ,
x_s_local_addr1 => rec_c_sl_lor_loc.s_local_addr1 ,
x_s_local_addr2 => rec_c_sl_lor_loc.s_local_addr2 ,
x_s_local_city => rec_c_sl_lor_loc.s_local_city ,
x_s_local_state => rec_c_sl_lor_loc.s_local_state ,
x_s_local_zip => rec_c_sl_lor_loc.s_local_zip ,
x_s_local_addr_chg_date => rec_c_sl_lor_loc.s_local_addr_chg_date ,
x_s_email_addr => rec_c_sl_lor_loc.s_email_addr ,
x_s_date_of_birth => rec_c_sl_lor_loc.s_date_of_birth ,
x_s_dob_chg_date => rec_c_sl_lor_loc.s_dob_chg_date ,
x_s_license_num => rec_c_sl_lor_loc.s_license_num ,
x_s_license_state => rec_c_sl_lor_loc.s_license_state ,
x_s_depncy_status => rec_c_sl_lor_loc.s_depncy_status ,
x_s_default_status => rec_c_sl_lor_loc.s_default_status ,
x_s_citizenship_status => rec_c_sl_lor_loc.s_citizenship_status ,
x_s_alien_reg_num => rec_c_sl_lor_loc.s_alien_reg_num ,
x_s_foreign_postal_code => rec_c_sl_lor_loc.s_foreign_postal_code ,
x_mode => 'R' ,
x_pnote_batch_id => rec_c_sl_lor_loc.pnote_batch_id ,
x_pnote_ack_date => rec_c_sl_lor_loc.pnote_ack_date ,
x_pnote_mpn_ind => rec_c_sl_lor_loc.pnote_mpn_ind ,
x_award_id => rec_c_sl_lor_loc.award_id ,
x_base_id => rec_c_sl_lor_loc.base_id ,
x_document_id_txt => rec_c_sl_lor_loc.document_id_txt ,
x_loan_key_num => rec_c_sl_lor_loc.loan_key_num ,
x_interest_rebate_percent_num => rec_c_sl_lor_loc.interest_rebate_percent_num ,
x_fin_award_year => rec_c_sl_lor_loc.fin_award_year ,
x_cps_trans_num => rec_c_sl_lor_loc.cps_trans_num ,
x_atd_entity_id_txt => rec_c_sl_lor_loc.atd_entity_id_txt ,
x_rep_entity_id_txt => rec_c_sl_lor_loc.rep_entity_id_txt ,
x_source_entity_id_txt => rec_c_sl_lor_loc.source_entity_id_txt ,
x_pymt_servicer_amt => rec_c_sl_lor_loc.pymt_servicer_amt ,
x_pymt_servicer_date => rec_c_sl_lor_loc.pymt_servicer_date ,
x_book_loan_amt => rec_c_sl_lor_loc.book_loan_amt ,
x_book_loan_amt_date => rec_c_sl_lor_loc.book_loan_amt_date ,
x_s_chg_birth_date => rec_c_sl_lor_loc.s_chg_birth_date ,
x_s_chg_ssn => rec_c_sl_lor_loc.s_chg_ssn ,
x_s_chg_last_name => rec_c_sl_lor_loc.s_chg_last_name ,
x_b_chg_birth_date => rec_c_sl_lor_loc.b_chg_birth_date ,
x_b_chg_ssn => rec_c_sl_lor_loc.b_chg_ssn ,
x_b_chg_last_name => rec_c_sl_lor_loc.b_chg_last_name ,
x_note_message => rec_c_sl_lor_loc.note_message ,
x_full_resp_code => rec_c_sl_lor_loc.full_resp_code ,
x_s_permt_county => rec_c_sl_lor_loc.s_permt_county ,
x_b_permt_county => rec_c_sl_lor_loc.b_permt_county ,
x_s_permt_country => rec_c_sl_lor_loc.s_permt_country ,
x_b_permt_country => rec_c_sl_lor_loc.b_permt_country ,
x_crdt_decision_status => rec_c_sl_lor_loc.crdt_decision_status ,
x_external_loan_id_txt => rec_c_sl_lor_loc.external_loan_id_txt ,
x_deferment_request_code => rec_c_sl_lor_loc.deferment_request_code ,
x_eft_authorization_code => rec_c_sl_lor_loc.eft_authorization_code ,
x_requested_loan_amt => rec_c_sl_lor_loc.requested_loan_amt ,
x_actual_record_type_code => rec_c_sl_lor_loc.actual_record_type_code ,
x_reinstatement_amt => rec_c_sl_lor_loc.reinstatement_amt ,
x_lender_use_txt => rec_c_sl_lor_loc.lender_use_txt ,
x_guarantor_use_txt => rec_c_sl_lor_loc.guarantor_use_txt ,
x_fls_approved_amt => rec_c_sl_lor_loc.fls_approved_amt ,
x_flu_approved_amt => rec_c_sl_lor_loc.flu_approved_amt ,
x_flp_approved_amt => rec_c_sl_lor_loc.flp_approved_amt ,
x_alt_approved_amt => rec_c_sl_lor_loc.alt_approved_amt ,
x_loan_app_form_code => rec_c_sl_lor_loc.loan_app_form_code ,
x_alt_borrower_ind_flag => rec_c_sl_lor_loc.alt_borrower_ind_flag ,
x_school_id_txt => rec_c_sl_lor_loc.school_id_txt ,
x_cost_of_attendance_amt => rec_c_sl_lor_loc.cost_of_attendance_amt ,
x_established_fin_aid_amount => rec_c_sl_lor_loc.established_fin_aid_amount ,
x_student_electronic_sign_flag => rec_c_sl_lor_loc.student_electronic_sign_flag ,
x_mpn_type_flag => rec_c_sl_lor_loc.mpn_type_flag ,
x_school_use_txt => rec_c_sl_lor_loc.school_use_txt ,
x_expect_family_contribute_amt => rec_c_sl_lor_loc.expect_family_contribute_amt ,
x_borower_electronic_sign_flag => rec_c_sl_lor_loc.borower_electronic_sign_flag ,
x_borower_credit_authoriz_flag => rec_c_sl_lor_loc.borower_credit_authoriz_flag ,
x_esign_src_typ_cd => rec_c_sl_lor_loc.esign_src_typ_cd
);
p_v_string => ' Call out to igf_sl_lor_loc_pkg.update_row for ' ||
' loan id = ' ||rec_c_sl_lor_loc.loan_id ||
' Origination id = ' ||rec_c_sl_lor_loc.origination_id ||
' Successful. Updated the change status to Sent '
);
PROCEDURE proc_update_clchsn_dtls_rec(p_v_loan_number IN igf_sl_loans.loan_number%TYPE ,
p_v_change_record_typ IN igf_sl_clchsn_dtls.change_record_type_txt%TYPE ,
p_n_disb_num IN igf_aw_awd_disb_all.disb_num%TYPE ,
p_v_send_record_txt IN igf_sl_clchsn_dtls.send_record_txt%TYPE
) AS
------------------------------------------------------------------
--Created by : Sanil Madathil, Oracle IDC
--Date created: 16 November 2004
--
-- Purpose:
-- Invoked : from sub_create_file procedure
-- Function :
--
-- Parameters : p_v_loan_number : IN parameter. Required.
-- p_v_change_record_typ : IN parameter. Required.
-- p_n_disb_num : IN parameter. Required.
-- p_v_send_record_txt : IN parameter. Required.
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
------------------------------------------------------------------
CURSOR c_sl_clchsn_dtls (cp_v_loan_number IN igf_sl_loans_all.loan_number%TYPE ,
cp_v_change_record_typ IN igf_sl_clchsn_dtls.change_record_type_txt%TYPE ,
cp_n_disb_num IN igf_aw_awd_disb_all.disb_num%TYPE
) IS
SELECT chdt.ROWID row_id
,chdt.*
FROM igf_sl_clchsn_dtls chdt
WHERE chdt.loan_number_txt = cp_v_loan_number
AND chdt.change_record_type_txt = cp_v_change_record_typ
AND ((chdt.disbursement_number = cp_n_disb_num) OR (cp_n_disb_num IS NULL))
AND chdt.status_code ='R'
ORDER BY loan_number_txt,change_record_type_txt,disbursement_number;
log_to_fnd(p_v_module => 'proc_update_clchsn_dtls_rec',
p_v_string => ' Entered Procedure proc_update_clchsn_dtls_rec: The input parameters are '||
' p_v_loan_number : ' ||p_v_loan_number ||
' p_v_change_record_typ : ' ||p_v_change_record_typ ||
' p_n_disb_num : ' ||p_n_disb_num ||
' p_v_send_record_txt : ' ||p_v_send_record_txt
);
log_to_fnd(p_v_module => 'proc_update_clchsn_dtls_rec',
p_v_string => 'invoking igf_sl_clchsn_dtls_pkg.update_row for change send id ='||rec_c_sl_clchsn_dtls.clchgsnd_id
);
igf_sl_clchsn_dtls_pkg.update_row (
x_rowid => rec_c_sl_clchsn_dtls.row_id ,
x_clchgsnd_id => rec_c_sl_clchsn_dtls.clchgsnd_id ,
x_award_id => rec_c_sl_clchsn_dtls.award_id ,
x_loan_number_txt => rec_c_sl_clchsn_dtls.loan_number_txt ,
x_cl_version_code => rec_c_sl_clchsn_dtls.cl_version_code ,
x_change_field_code => rec_c_sl_clchsn_dtls.change_field_code ,
x_change_record_type_txt => rec_c_sl_clchsn_dtls.change_record_type_txt ,
x_change_code_txt => rec_c_sl_clchsn_dtls.change_code_txt ,
x_status_code => 'S' ,
x_status_date => rec_c_sl_clchsn_dtls.status_date ,
x_response_status_code => rec_c_sl_clchsn_dtls.response_status_code ,
x_old_value_txt => rec_c_sl_clchsn_dtls.old_value_txt ,
x_new_value_txt => rec_c_sl_clchsn_dtls.new_value_txt ,
x_old_date => rec_c_sl_clchsn_dtls.old_date ,
x_new_date => rec_c_sl_clchsn_dtls.new_date ,
x_old_amt => rec_c_sl_clchsn_dtls.old_amt ,
x_new_amt => rec_c_sl_clchsn_dtls.new_amt ,
x_disbursement_number => rec_c_sl_clchsn_dtls.disbursement_number ,
x_disbursement_date => rec_c_sl_clchsn_dtls.disbursement_date ,
x_change_issue_code => rec_c_sl_clchsn_dtls.change_issue_code ,
x_disbursement_cancel_date => rec_c_sl_clchsn_dtls.disbursement_cancel_date ,
x_disbursement_cancel_amt => rec_c_sl_clchsn_dtls.disbursement_cancel_amt ,
x_disbursement_revised_amt => rec_c_sl_clchsn_dtls.disbursement_revised_amt ,
x_disbursement_revised_date => rec_c_sl_clchsn_dtls.disbursement_revised_date ,
x_disbursement_reissue_code => rec_c_sl_clchsn_dtls.disbursement_reissue_code ,
x_disbursement_reinst_code => rec_c_sl_clchsn_dtls.disbursement_reinst_code ,
x_disbursement_return_amt => rec_c_sl_clchsn_dtls.disbursement_return_amt ,
x_disbursement_return_date => rec_c_sl_clchsn_dtls.disbursement_return_date ,
x_disbursement_return_code => rec_c_sl_clchsn_dtls.disbursement_return_code ,
x_post_with_disb_return_amt => rec_c_sl_clchsn_dtls.post_with_disb_return_amt ,
x_post_with_disb_return_date => rec_c_sl_clchsn_dtls.post_with_disb_return_date ,
x_post_with_disb_return_code => rec_c_sl_clchsn_dtls.post_with_disb_return_code ,
x_prev_with_disb_return_amt => rec_c_sl_clchsn_dtls.prev_with_disb_return_amt ,
x_prev_with_disb_return_date => rec_c_sl_clchsn_dtls.prev_with_disb_return_date ,
x_school_use_txt => rec_c_sl_clchsn_dtls.school_use_txt ,
x_lender_use_txt => rec_c_sl_clchsn_dtls.lender_use_txt ,
x_guarantor_use_txt => rec_c_sl_clchsn_dtls.guarantor_use_txt ,
x_validation_edit_txt => rec_c_sl_clchsn_dtls.validation_edit_txt ,
x_send_record_txt => l_v_send_record_txt ,
x_mode => 'R'
);
log_to_fnd(p_v_module => 'proc_update_clchsn_dtls_rec',
p_v_string => 'Call out to igf_sl_clchsn_dtls_pkg.update_row successful for change send id ='||rec_c_sl_clchsn_dtls.clchgsnd_id
);
END proc_update_clchsn_dtls_rec;