[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_c_update_disb_dtls varchar2(10);
SELECT clrp1.* FROM igf_sl_cl_resp_r1 clrp1
WHERE clrp1.cbth_id = p_cbth_id
AND clrp1.resp_record_status = p_rec_status
ORDER BY clrp1.clrp1_id
FOR UPDATE OF resp_record_status NOWAIT;
PROCEDURE insert_into_resp1(p_loan_number igf_sl_loans_all.loan_number%TYPE,
p_resp_record_status igf_sl_cl_resp_r1_all.resp_record_status%TYPE,
p_rec_type_ind igf_sl_cl_resp_r1_all.rec_type_ind%TYPE);
PROCEDURE update_lor(p_clrp1_id igf_sl_cl_resp_r1_all.clrp1_id%TYPE,
p_loan_number igf_sl_loans_all.loan_number%TYPE,
p_rejected_rec BOOLEAN);
PROCEDURE insert_into_resp_r4(p_clrp1_id igf_sl_cl_resp_r1_all.clrp1_id%TYPE,
p_r4_record igf_sl_load_file_t.record_data%TYPE);
PROCEDURE insert_into_resp_r2(p_r2_record IN igf_sl_cl_resp_r2_dtls%ROWTYPE);
PROCEDURE insert_into_resp_r3(p_r3_record IN igf_sl_cl_resp_r3_dtls%ROWTYPE);
PROCEDURE insert_into_resp_r7(p_r7_record IN igf_sl_cl_resp_r7_dtls%ROWTYPE);
PROCEDURE insert_into_resp_r6(p_r6_record IN igf_sl_clchrs_dtls%ROWTYPE);
ugummall 21-OCT-2003 Removed one clause(selection based on school_id) from
cur_Hrecord cursor.
mesriniv 05-APR-2001 Changed the occurrences of field fao_id
to ope_id
***************************************************************/
l_row_id VARCHAR2(25);
SELECT * FROM igf_sl_load_file_t
WHERE file_type = p_file_type
AND LTRIM(RTRIM(SUBSTR(record_data,1,2))) = p_record_data
AND lort_id = 1
ORDER By lort_id;
SELECT 'x' FROM igf_sl_load_file_t
WHERE lort_id = (SELECT MAX(lort_id) FROM igf_sl_load_file_t
WHERE file_type = p_file_type)
AND file_type = p_file_type
AND SUBSTR(record_data,1,2) = p_record_data ;
SELECT clbatch.*, clbatch.ROWID row_id
FROM igf_sl_cl_batch_all clbatch
WHERE RTRIM(batch_id) = g_loaded_batch_id
AND file_creation_date = l_file_creation_date
AND file_ident_code = g_loaded_file_ident_code
AND recipient_id = g_loaded_recipient_id
AND NVL(recip_non_ed_brc_id,'*') = NVL(g_loaded_recip_non_ed_brc_id,'*')
AND source_id = g_file_source_id
AND NVL(source_non_ed_brc_id,'*') = NVL(g_file_source_non_ed_brc_id,'*')
AND send_resp = p_send_resp;
igf_sl_cl_batch_pkg.insert_row (
x_mode => 'R',
x_rowid => l_row_id,
x_cbth_id => g_cbth_id, -- generated by sequence
x_batch_id => g_loaded_batch_id,
x_file_creation_date => l_file_creation_date,
x_file_trans_date => l_file_trans_date,
x_file_ident_code => g_loaded_file_ident_code,
x_recipient_id => g_loaded_recipient_id,
x_recip_non_ed_brc_id => g_loaded_recip_non_ed_brc_id,
x_source_id => g_file_source_id,
x_source_non_ed_brc_id => g_file_source_non_ed_brc_id,
x_send_resp => 'R',
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
);
igf_sl_cl_batch_pkg.update_row (
x_mode => 'R',
x_rowid => l_row_id,
x_cbth_id => l_n_cbth_id,
x_batch_id => g_loaded_batch_id,
x_file_creation_date => l_file_creation_date,
x_file_trans_date => l_file_trans_date,
x_file_ident_code => g_loaded_file_ident_code,
x_recipient_id => g_loaded_recipient_id,
x_recip_non_ed_brc_id => g_loaded_recip_non_ed_brc_id,
x_source_id => g_file_source_id,
x_source_non_ed_brc_id => g_file_source_non_ed_brc_id,
x_send_resp => 'R',
x_record_count_num => rec_cur_batch.record_count_num ,
x_total_net_disb_amt => rec_cur_batch.total_net_disb_amt ,
x_total_net_eft_amt => rec_cur_batch.total_net_eft_amt ,
x_total_net_non_eft_amt => rec_cur_batch.total_net_non_eft_amt ,
x_total_reissue_amt => rec_cur_batch.total_reissue_amt ,
x_total_cancel_amt => rec_cur_batch.total_cancel_amt ,
x_total_deficit_amt => rec_cur_batch.total_deficit_amt ,
x_total_net_cancel_amt => rec_cur_batch.total_net_cancel_amt ,
x_total_net_out_cancel_amt => rec_cur_batch.total_net_out_cancel_amt
);
will insert the format file(@1 and @8) data into
the tables IGF_SL_CL_RESP_R1 and IGF_SL_CL_RESP_R8
tables correspondingly.
Known Limitations,Enhancements or Remarks
Change History :
Who When What
***************************************************************/
l_loan_active igf_sl_loans_all.active%TYPE;
SELECT * FROM igf_sl_load_file_t
WHERE SUBSTR(record_data,1,2)= p_record_data
AND file_type= p_file_type
ORDER BY lort_id;
SELECT active
,loan_id
,award_id
FROM igf_sl_loans_all
WHERE NVL(external_loan_id_txt,loan_number) = l_loan_number;
SELECT lor.rec_type_ind
FROM igf_sl_lor_all lor,
igf_sl_loans_all loan
WHERE loan.loan_id = lor.loan_id AND
NVL(loan.external_loan_id_txt,loan.loan_number) = cp_v_loan_number;
SELECT fmast.ci_cal_type ci_cal_type,
fmast.ci_sequence_number ci_sequence_number,
ci.alternate_code ci_alternate_code,
ci.start_dt ci_start_dt,
ci.end_dt ci_end_dt,
fcat.fed_fund_code fed_fund_code,
fcat.sys_fund_type sys_fund_type,
facon.person_id person_id,
pe.party_number person_number,
pit.api_person_id ssn,
pe.person_last_name || ',' || pe.person_first_name full_name,
awd.accepted_amt accepted_amt
FROM igf_aw_award_all awd,
igf_aw_fund_mast_all fmast,
igf_aw_fund_cat_all fcat,
igs_ca_inst ci,
igf_ap_fa_base_rec facon,
(SELECT api_person_id,
pe_person_id
FROM igs_pe_person_id_typ pit_2,
igs_pe_alt_pers_id api_2
WHERE api_2.person_id_type = pit_2.person_id_type
AND pit_2.s_person_id_type = 'SSN'
AND SYSDATE BETWEEN api_2.start_dt AND NVL (api_2.end_dt,SYSDATE)) pit,
hz_parties pe
WHERE fmast.fund_code = fcat.fund_code
AND fmast.ci_cal_type = ci.cal_type
AND fmast.ci_sequence_number = ci.sequence_number
AND awd.base_id = facon.base_id
AND pe.party_id = facon.person_id
AND pe.party_id = pit.pe_person_id(+)
AND fmast.fund_id = awd.fund_id
AND awd.award_id = cp_n_award_id;
insert_into_resp1(l_loan_number, l_resp_record_status, l_v_rec_type_ind);
insert_into_resp1(l_loan_number, l_resp_record_status, l_v_rec_type_ind);
CLOSE cur_1records; -- Finish inserting all the @1 and @8 Records into Resp1 and Resp2 Tables correspondingly.
/* Inserts Data into Resp1 ,Resp4 and Resp8 Tables*/
PROCEDURE insert_into_resp1(p_loan_number igf_sl_loans_all.loan_number%TYPE,
p_resp_record_status igf_sl_cl_resp_r1_all.resp_record_status%TYPE,
p_rec_type_ind igf_sl_cl_resp_r1_all.rec_type_ind%TYPE)
AS
/***************************************************************
Created By : mesriniv
Date Created By : 2000/12/07
Purpose : To Insert File data into IGF_SL_CL_RESP_R1
and IGF_SL_CL_RESP_R8 tables
Known Limitations,Enhancements or Remarks
Change History :
Who When What
masehgal 17-Feb-2002 # 2216956 FACR007
Added borr_sign_ind , Stud_sign_ind to TBH call for insert
***************************************************************/
l_ind NUMBER;
SELECT * FROM igf_sl_load_file_t
WHERE file_type = p_file_type
AND lort_id > Format_1_rec.lort_id;
log_to_fnd(p_v_module => 'insert_into_resp_r1',
p_v_string => 'Checking fields common to RELEASE-4 AND RELEASE-5 for loan number ='||p_loan_number
);
log_to_fnd(p_v_module => 'insert_into_resp_r1',
p_v_string => 'Seggrgating fields common to RELEASE-4'
);
rec_cl_resp_r1.cl_rec_status_last_update := fnd_date.string_to_date(SUBSTR(Format_1_rec.record_data,4,8),'YYYYMMDD');
log_to_fnd(p_v_module => 'insert_into_resp_r1',
p_v_string => 'Seggregating fields common to RELEASE-5'
);
rec_cl_resp_r1.cl_rec_status_last_update := NULL;
gv_debug_str := 'INSERT_INTO_RESP1 - 1' ||' ';
log_to_fnd(p_v_module => 'insert_into_resp_r1',
p_v_string => 'Invoking igf_sl_cl_resp_r1_pkg.insert_row'
);
igf_sl_cl_resp_r1_pkg.insert_row (
x_mode => 'R' ,
x_rowid => l_row_id ,
x_clrp1_id => l_clrp1_id ,
x_cbth_id => g_cbth_id ,
x_rec_code => rec_cl_resp_r1.rec_code ,
x_rec_type_ind => rec_cl_resp_r1.rec_type_ind ,
x_b_last_name => rec_cl_resp_r1.b_last_name ,
x_b_first_name => rec_cl_resp_r1.b_first_name ,
x_b_middle_name => rec_cl_resp_r1.b_middle_name ,
x_b_ssn => rec_cl_resp_r1.b_ssn ,
x_b_permt_addr1 => rec_cl_resp_r1.b_permt_addr1 ,
x_b_permt_addr2 => rec_cl_resp_r1.b_permt_addr2 ,
x_b_permt_city => rec_cl_resp_r1.b_permt_city ,
x_b_permt_state => rec_cl_resp_r1.b_permt_state ,
x_b_permt_zip => rec_cl_resp_r1.b_permt_zip ,
x_b_permt_zip_suffix => rec_cl_resp_r1.b_permt_zip_suffix ,
x_b_permt_phone => rec_cl_resp_r1.b_permt_phone ,
x_b_date_of_birth => rec_cl_resp_r1.b_date_of_birth ,
x_cl_loan_type => rec_cl_resp_r1.cl_loan_type ,
x_req_loan_amt => rec_cl_resp_r1.req_loan_amt ,
x_defer_req_code => rec_cl_resp_r1.defer_req_code ,
x_borw_interest_ind => rec_cl_resp_r1.borw_interest_ind ,
x_eft_auth_code => rec_cl_resp_r1.eft_auth_code ,
x_b_signature_code => rec_cl_resp_r1.b_signature_code ,
x_b_signature_date => rec_cl_resp_r1.b_signature_date ,
x_loan_number => rec_cl_resp_r1.loan_number ,
x_cl_seq_number => rec_cl_resp_r1.cl_seq_number ,
x_b_citizenship_status => rec_cl_resp_r1.b_citizenship_status ,
x_b_state_of_legal_res => rec_cl_resp_r1.b_state_of_legal_res ,
x_b_legal_res_date => rec_cl_resp_r1.b_legal_res_date ,
x_b_default_status => rec_cl_resp_r1.b_default_status ,
x_b_outstd_loan_code => rec_cl_resp_r1.b_outstd_loan_code ,
x_b_indicator_code => rec_cl_resp_r1.b_indicator_code ,
x_s_last_name => rec_cl_resp_r1.s_last_name ,
x_s_first_name => rec_cl_resp_r1.s_first_name ,
x_s_middle_name => rec_cl_resp_r1.s_middle_name ,
x_s_ssn => rec_cl_resp_r1.s_ssn ,
x_s_date_of_birth => rec_cl_resp_r1.s_date_of_birth ,
x_s_citizenship_status => rec_cl_resp_r1.s_citizenship_status ,
x_s_default_code => rec_cl_resp_r1.s_default_code ,
x_s_signature_code => rec_cl_resp_r1.s_signature_code ,
x_school_id => rec_cl_resp_r1.school_id ,
x_loan_per_begin_date => rec_cl_resp_r1.loan_per_begin_date ,
x_loan_per_end_date => rec_cl_resp_r1.loan_per_end_date ,
x_grade_level_code => rec_cl_resp_r1.grade_level_code ,
x_enrollment_code => rec_cl_resp_r1.enrollment_code ,
x_anticip_compl_date => rec_cl_resp_r1.anticip_compl_date ,
x_coa_amt => rec_cl_resp_r1.coa_amt ,
x_efc_amt => rec_cl_resp_r1.efc_amt ,
x_est_fa_amt => rec_cl_resp_r1.est_fa_amt ,
x_fls_cert_amt => rec_cl_resp_r1.fls_cert_amt ,
x_flu_cert_amt => rec_cl_resp_r1.flu_cert_amt ,
x_flp_cert_amt => rec_cl_resp_r1.flp_cert_amt ,
x_sch_cert_date => rec_cl_resp_r1.sch_cert_date ,
x_alt_cert_amt => rec_cl_resp_r1.alt_cert_amt ,
x_alt_appl_ver_code => rec_cl_resp_r1.alt_appl_ver_code ,
x_duns_school_id => rec_cl_resp_r1.duns_school_id ,
x_lender_id => rec_cl_resp_r1.lender_id ,
x_fls_approved_amt => rec_cl_resp_r1.fls_approved_amt ,
x_flu_approved_amt => rec_cl_resp_r1.flu_approved_amt ,
x_flp_approved_amt => rec_cl_resp_r1.flp_approved_amt ,
x_alt_approved_amt => rec_cl_resp_r1.alt_approved_amt ,
x_duns_lender_id => rec_cl_resp_r1.duns_lender_id ,
x_guarantor_id => rec_cl_resp_r1.guarantor_id ,
x_fed_appl_form_code => rec_cl_resp_r1.fed_appl_form_code ,
x_duns_guarnt_id => rec_cl_resp_r1.duns_guarnt_id ,
x_lend_blkt_guarnt_ind => rec_cl_resp_r1.lend_blkt_guarnt_ind ,
x_lend_blkt_guarnt_appr_date => rec_cl_resp_r1.lend_blkt_guarnt_appr_date ,
x_guarnt_adj_ind => rec_cl_resp_r1.guarnt_adj_ind ,
x_guarantee_date => rec_cl_resp_r1.guarantee_date ,
x_guarantee_amt => rec_cl_resp_r1.guarantee_amt ,
x_req_serial_loan_code => rec_cl_resp_r1.req_serial_loan_code ,
x_borw_confirm_ind => rec_cl_resp_r1.borw_confirm_ind ,
x_b_license_state => rec_cl_resp_r1.b_license_state ,
x_b_license_number => rec_cl_resp_r1.b_license_number ,
x_b_ref_code => rec_cl_resp_r1.b_ref_code ,
x_pnote_delivery_code => rec_cl_resp_r1.pnote_delivery_code ,
x_b_foreign_postal_code => rec_cl_resp_r1.b_foreign_postal_code ,
x_lend_non_ed_brc_id => rec_cl_resp_r1.lend_non_ed_brc_id ,
x_last_resort_lender => rec_cl_resp_r1.last_resort_lender ,
x_resp_to_orig_code => rec_cl_resp_r1.resp_to_orig_code ,
x_err_mesg_1 => rec_cl_resp_r1.err_mesg_1 ,
x_err_mesg_2 => rec_cl_resp_r1.err_mesg_2 ,
x_err_mesg_3 => rec_cl_resp_r1.err_mesg_3 ,
x_err_mesg_4 => rec_cl_resp_r1.err_mesg_4 ,
x_err_mesg_5 => rec_cl_resp_r1.err_mesg_5 ,
x_guarnt_amt_redn_code => rec_cl_resp_r1.guarnt_amt_redn_code ,
x_tot_outstd_stafford => rec_cl_resp_r1.tot_outstd_stafford ,
x_tot_outstd_plus => rec_cl_resp_r1.tot_outstd_plus ,
x_b_permt_addr_chg_date => rec_cl_resp_r1.b_permt_addr_chg_date ,
x_alt_prog_type_code => rec_cl_resp_r1.alt_prog_type_code ,
x_alt_borw_tot_debt => rec_cl_resp_r1.alt_borw_tot_debt ,
x_act_interest_rate => rec_cl_resp_r1.act_interest_rate ,
x_prc_type_code => rec_cl_resp_r1.prc_type_code ,
x_service_type_code => rec_cl_resp_r1.service_type_code ,
x_rev_notice_of_guarnt => rec_cl_resp_r1.rev_notice_of_guarnt ,
x_sch_refund_amt => rec_cl_resp_r1.sch_refund_amt ,
x_sch_refund_date => rec_cl_resp_r1.sch_refund_date ,
x_guarnt_status_code => rec_cl_resp_r1.guarnt_status_code ,
x_lender_status_code => rec_cl_resp_r1.lender_status_code ,
x_pnote_status_code => rec_cl_resp_r1.pnote_status_code ,
x_credit_status_code => rec_cl_resp_r1.credit_status_code ,
x_guarnt_status_date => rec_cl_resp_r1.guarnt_status_date ,
x_lender_status_date => rec_cl_resp_r1.lender_status_date ,
x_pnote_status_date => rec_cl_resp_r1.pnote_status_date ,
x_credit_status_date => rec_cl_resp_r1.credit_status_date ,
x_act_serial_loan_code => rec_cl_resp_r1.act_serial_loan_code ,
x_amt_avail_for_reinst => rec_cl_resp_r1.amt_avail_for_reinst ,
x_sch_non_ed_brc_id => rec_cl_resp_r1.sch_non_ed_brc_id ,
x_uniq_layout_vend_code => rec_cl_resp_r1.uniq_layout_vend_code ,
x_uniq_layout_ident_code => rec_cl_resp_r1.uniq_layout_ident_code ,
x_resp_record_status => rec_cl_resp_r1.resp_record_status ,
x_borr_sign_ind => rec_cl_resp_r1.borr_sign_ind ,
x_stud_sign_ind => rec_cl_resp_r1.stud_sign_ind ,
x_borr_credit_auth_code => rec_cl_resp_r1.borr_credit_auth_code ,
x_mpn_confirm_ind => rec_cl_resp_r1.mpn_confirm_ind ,
x_lender_use_txt => rec_cl_resp_r1.lender_use_txt ,
x_guarantor_use_txt => rec_cl_resp_r1.guarantor_use_txt ,
x_appl_loan_phase_code => rec_cl_resp_r1.appl_loan_phase_code ,
x_appl_loan_phase_code_chg => rec_cl_resp_r1.appl_loan_phase_code_chg ,
x_cl_rec_status => rec_cl_resp_r1.cl_rec_status ,
x_cl_rec_status_last_update => rec_cl_resp_r1.cl_rec_status_last_update ,
x_lend_apprv_denied_code => rec_cl_resp_r1.lend_apprv_denied_code ,
x_lend_apprv_denied_date => rec_cl_resp_r1.lend_apprv_denied_date ,
x_cl_version_code => rec_cl_resp_r1.cl_version_code ,
x_school_use_txt => rec_cl_resp_r1.school_use_txt ,
x_b_alien_reg_num_txt => rec_cl_resp_r1.b_alien_reg_num_txt ,
x_esign_src_typ_cd => rec_cl_resp_r1.esign_src_typ_cd
);
gv_debug_str := gv_debug_str || 'INSERT_INTO_RESP1 - 2' ||' ';
gv_debug_str := gv_debug_str || 'INSERT_INTO_RESP1 - 3' ||' ';
gv_debug_str := gv_debug_str || 'INSERT_INTO_RESP1 - 4' || ' ';
gv_debug_str := gv_debug_str || 'INSERT_INTO_RESP1 - 5' || ' ';
gv_debug_str := gv_debug_str || 'INSERT_INTO_RESP1 - 6' || ' ';
gv_debug_str := gv_debug_str || 'INSERT_INTO_RESP1 - 7' || ' ';
gv_debug_str := gv_debug_str || 'INSERT_INTO_RESP1 - 8' || ' ';
gv_debug_str := gv_debug_str || 'INSERT_INTO_RESP1 - 9' || ' ';
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig_ack.insert_into_resp1.debug',gv_debug_str);
insert_into_resp_r4(l_clrp1_id,
other_rec.record_data);
gv_debug_str := gv_debug_str || 'INSERT_INTO_RESP1 - 10' || ' ';
gv_debug_str := gv_debug_str || 'INSERT_INTO_RESP1 - 11' || ' ';
gv_debug_str := gv_debug_str || 'INSERT_INTO_RESP1 - 12' || ' ';
gv_debug_str := gv_debug_str || 'INSERT_INTO_RESP1 - 13' || ' ';
igf_sl_cl_resp_r8_pkg.insert_row (
x_mode => 'R',
x_rowid => l_row_id,
x_clrp1_id => l_clrp1_id,
x_clrp8_id => i,
x_disb_date => r8_tab(i).disb_date,
x_disb_gross_amt => r8_tab(i).disb_gross_amt,
x_orig_fee => r8_tab(i).orig_fee,
x_guarantee_fee => r8_tab(i).guarantee_fee,
x_net_disb_amt => r8_tab(i).net_disb_amt,
x_disb_hold_rel_ind => r8_tab(i).disb_hold_rel_ind,
x_disb_status => r8_tab(i).disb_status,
x_guarnt_fee_paid => r8_tab(i).guarnt_fee_paid,
x_orig_fee_paid => r8_tab(i).orig_fee_paid,
x_resp_record_status => 'N',
x_layout_owner_code_txt => r8_tab(i).layout_owner_code_txt,
x_layout_version_code_txt=> r8_tab(i).layout_version_code_txt,
x_record_code_txt => r8_tab(i).record_code_txt
-- x_direct_to_borr_flag => NVL(r8_tab(i).direct_to_borr_flag,'N')
);
gv_debug_str := gv_debug_str || 'INSERT_INTO_RESP1 - 14' || ' ';
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig_ack.insert_into_resp1.debug',gv_debug_str);
r8_tab.delete;
log_to_fnd(p_v_module => 'insert_into_resp_r1',
p_v_string => 'looping thru v_tab_resp_r2'
);
insert_into_resp_r2(
p_r2_record => v_tab_resp_r2(l_n_ctr_r2)
);
v_tab_resp_r2.DELETE;
log_to_fnd(p_v_module => 'insert_into_resp_r1',
p_v_string => 'looping thru v_tab_resp_r3'
);
insert_into_resp_r3(
p_r3_record => v_tab_resp_r3(l_n_ctr_r3)
);
v_tab_resp_r3.DELETE;
log_to_fnd(p_v_module => 'insert_into_resp_r1',
p_v_string => 'looping thru v_tab_resp_r7'
);
insert_into_resp_r7(
p_r7_record => v_tab_resp_r7(l_n_ctr_r7)
);
v_tab_resp_r7.DELETE;
log_to_fnd(p_v_module => 'insert_into_resp_r1',
p_v_string => 'looping thru v_tab_clchrs_dtls'
);
insert_into_resp_r6(
p_r6_record => v_tab_clchrs_dtls(l_n_ctr_r6)
);
v_tab_clchrs_dtls.DELETE;
fnd_message.set_token('NAME','IGF_SL_CL_ACK.INSERT_INTO_RESP1');
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_cl_orig_ack.insert_into_resp1.exception',gv_debug_str||' '|| SQLERRM);
END insert_into_resp1;
p_c_update_disb_dtls IN VARCHAR2
)
AS
/***************************************************************
Created By : mesriniv
Date Created By : 2000/12/07
Purpose : To process all the @1 Records
Known Limitations,Enhancements or Remarks
Change History :
Who When What
masehgal 17-Feb-2002 # 2216956 FACR007
Added Elec_mpn_ind , Borrow_sign_ind in igf_sl_cl_resp_r1
***************************************************************/
l_msg NUMBER;
SELECT rpad(field_desc,50)||sl_error_desc reject_desc FROM igf_sl_edit_report_v
WHERE loan_number = loaded_1rec.loan_number
AND orig_chg_code = p_chg_code
ORDER BY edtr_id;
g_c_update_disb_dtls := p_c_update_disb_dtls ;
igf_sl_edit.delete_edit(l_loan_number, 'R');
err_mesg_array.delete;
igf_sl_edit.insert_edit(loaded_1rec.loan_number,
'R',
'IGF_SL_CL_ERROR',
err_mesg_array(l_index),'','');
update_lor(loaded_1rec.clrp1_id,l_loan_number,lb_rejected_rec);
igf_sl_cl_resp_r1_pkg.update_row (
X_Mode => 'R',
x_rowid => loaded_1rec.row_id,
x_clrp1_id => loaded_1rec.clrp1_id,
x_cbth_id => loaded_1rec.cbth_id,
x_rec_code => loaded_1rec.rec_code,
x_rec_type_ind => loaded_1rec.rec_type_ind,
x_b_last_name => loaded_1rec.b_last_name,
x_b_first_name => loaded_1rec.b_first_name,
x_b_middle_name => loaded_1rec.b_middle_name,
x_b_ssn => loaded_1rec.b_ssn,
x_b_permt_addr1 => loaded_1rec.b_permt_addr1,
x_b_permt_addr2 => loaded_1rec.b_permt_addr2,
x_b_permt_city => loaded_1rec.b_permt_city,
x_b_permt_state => loaded_1rec.b_permt_state,
x_b_permt_zip => loaded_1rec.b_permt_zip,
x_b_permt_zip_suffix => loaded_1rec.b_permt_zip_suffix,
x_b_permt_phone => loaded_1rec.b_permt_phone,
x_b_date_of_birth => loaded_1rec.b_date_of_birth,
x_cl_loan_type => loaded_1rec.cl_loan_type,
x_req_loan_amt => loaded_1rec.req_loan_amt,
x_defer_req_code => loaded_1rec.defer_req_code,
x_borw_interest_ind => loaded_1rec.borw_interest_ind,
x_eft_auth_code => loaded_1rec.eft_auth_code,
x_b_signature_code => loaded_1rec.b_signature_code,
x_b_signature_date => loaded_1rec.b_signature_date,
x_loan_number => loaded_1rec.loan_number,
x_cl_seq_number => loaded_1rec.cl_seq_number,
x_b_citizenship_status => loaded_1rec.b_citizenship_status,
x_b_state_of_legal_res => loaded_1rec.b_state_of_legal_res,
x_b_legal_res_date => loaded_1rec.b_legal_res_date,
x_b_default_status => loaded_1rec.b_default_status,
x_b_outstd_loan_code => loaded_1rec.b_outstd_loan_code,
x_b_indicator_code => loaded_1rec.b_indicator_code,
x_s_last_name => loaded_1rec.s_last_name,
x_s_first_name => loaded_1rec.s_first_name,
x_s_middle_name => loaded_1rec.s_middle_name,
x_s_ssn => loaded_1rec.s_ssn,
x_s_date_of_birth => loaded_1rec.s_date_of_birth,
x_s_citizenship_status => loaded_1rec.s_citizenship_status,
x_s_default_code => loaded_1rec.s_default_code,
x_s_signature_code => loaded_1rec.s_signature_code,
x_school_id => loaded_1rec.school_id,
x_loan_per_begin_date => loaded_1rec.loan_per_begin_date,
x_loan_per_end_date => loaded_1rec.loan_per_end_date,
x_grade_level_code => loaded_1rec.grade_level_code,
x_enrollment_code => loaded_1rec.enrollment_code,
x_anticip_compl_date => loaded_1rec.anticip_compl_date,
x_coa_amt => loaded_1rec.coa_amt,
x_efc_amt => loaded_1rec.efc_amt,
x_est_fa_amt => loaded_1rec.est_fa_amt,
x_fls_cert_amt => loaded_1rec.fls_cert_amt,
x_flu_cert_amt => loaded_1rec.flu_cert_amt,
x_flp_cert_amt => loaded_1rec.flp_cert_amt,
x_sch_cert_date => loaded_1rec.sch_cert_date,
x_alt_cert_amt => loaded_1rec.alt_cert_amt,
x_alt_appl_ver_code => loaded_1rec.alt_appl_ver_code,
x_duns_school_id => loaded_1rec.duns_school_id,
x_lender_id => loaded_1rec.lender_id,
x_fls_approved_amt => loaded_1rec.fls_approved_amt,
x_flu_approved_amt => loaded_1rec.flu_approved_amt,
x_flp_approved_amt => loaded_1rec.flp_approved_amt,
x_alt_approved_amt => loaded_1rec.alt_approved_amt,
x_duns_lender_id => loaded_1rec.duns_lender_id,
x_guarantor_id => loaded_1rec.guarantor_id,
x_fed_appl_form_code => loaded_1rec.fed_appl_form_code,
x_duns_guarnt_id => loaded_1rec.duns_guarnt_id,
x_lend_blkt_guarnt_ind => loaded_1rec.lend_blkt_guarnt_ind,
x_lend_blkt_guarnt_appr_date => loaded_1rec.lend_blkt_guarnt_appr_date,
x_guarnt_adj_ind => loaded_1rec.guarnt_adj_ind,
x_guarantee_date => loaded_1rec.guarantee_date,
x_guarantee_amt => loaded_1rec.guarantee_amt,
x_req_serial_loan_code => loaded_1rec.req_serial_loan_code,
x_borw_confirm_ind => loaded_1rec.borw_confirm_ind,
x_b_license_state => loaded_1rec.b_license_state,
x_b_license_number => loaded_1rec.b_license_number,
x_b_ref_code => loaded_1rec.b_ref_code,
x_pnote_delivery_code => loaded_1rec.pnote_delivery_code,
x_b_foreign_postal_code => loaded_1rec.b_foreign_postal_code,
x_lend_non_ed_brc_id => loaded_1rec.lend_non_ed_brc_id,
x_last_resort_lender => loaded_1rec.last_resort_lender,
x_resp_to_orig_code => loaded_1rec.resp_to_orig_code,
x_err_mesg_1 => loaded_1rec.err_mesg_1,
x_err_mesg_2 => loaded_1rec.err_mesg_2,
x_err_mesg_3 => loaded_1rec.err_mesg_3,
x_err_mesg_4 => loaded_1rec.err_mesg_4,
x_err_mesg_5 => loaded_1rec.err_mesg_5,
x_guarnt_amt_redn_code => loaded_1rec.guarnt_amt_redn_code,
x_tot_outstd_stafford => loaded_1rec.tot_outstd_stafford,
x_tot_outstd_plus => loaded_1rec.tot_outstd_plus,
x_b_permt_addr_chg_date => loaded_1rec.b_permt_addr_chg_date,
x_alt_prog_type_code => loaded_1rec.alt_prog_type_code,
x_alt_borw_tot_debt => loaded_1rec.alt_borw_tot_debt,
x_act_interest_rate => loaded_1rec.act_interest_rate,
x_prc_type_code => loaded_1rec.prc_type_code,
x_service_type_code => loaded_1rec.service_type_code,
x_rev_notice_of_guarnt => loaded_1rec.rev_notice_of_guarnt,
x_sch_refund_amt => loaded_1rec.sch_refund_amt,
x_sch_refund_date => loaded_1rec.sch_refund_date,
x_guarnt_status_code => loaded_1rec.guarnt_status_code,
x_lender_status_code => loaded_1rec.lender_status_code,
x_pnote_status_code => loaded_1rec.pnote_status_code,
x_credit_status_code => loaded_1rec.credit_status_code,
x_guarnt_status_date => loaded_1rec.guarnt_status_date,
x_lender_status_date => loaded_1rec.lender_status_date,
x_pnote_status_date => loaded_1rec.pnote_status_date,
x_credit_status_date => loaded_1rec.credit_status_date,
x_act_serial_loan_code => loaded_1rec.act_serial_loan_code,
x_amt_avail_for_reinst => loaded_1rec.amt_avail_for_reinst,
x_sch_non_ed_brc_id => loaded_1rec.sch_non_ed_brc_id,
x_uniq_layout_vend_code => loaded_1rec.uniq_layout_vend_code,
x_uniq_layout_ident_code => loaded_1rec.uniq_layout_ident_code,
x_resp_record_status => 'Y',
x_stud_sign_ind => loaded_1rec.stud_sign_ind,
x_borr_credit_auth_code => loaded_1rec.borr_credit_auth_code,
x_borr_sign_ind => loaded_1rec.borr_sign_ind,
x_mpn_confirm_ind => loaded_1rec.mpn_confirm_ind,
x_lender_use_txt => loaded_1rec.lender_use_txt,
x_guarantor_use_txt => loaded_1rec.guarantor_use_txt,
x_appl_loan_phase_code => loaded_1rec.appl_loan_phase_code,
x_appl_loan_phase_code_chg => loaded_1rec.appl_loan_phase_code_chg,
x_cl_rec_status => loaded_1rec.cl_rec_status,
x_cl_rec_status_last_update => loaded_1rec.cl_rec_status_last_update,
x_lend_apprv_denied_code => loaded_1rec.lend_apprv_denied_code,
x_lend_apprv_denied_date => loaded_1rec.lend_apprv_denied_date,
x_cl_version_code => loaded_1rec.cl_version_code,
x_school_use_txt => loaded_1rec.school_use_txt ,
x_b_alien_reg_num_txt => loaded_1rec.b_alien_reg_num_txt,
x_esign_src_typ_cd => loaded_1rec.esign_src_typ_cd
);
igf_sl_gen.update_cl_chg_status(loaded_1rec.loan_number);
PROCEDURE update_lor(p_clrp1_id igf_sl_cl_resp_r1_all.clrp1_id%TYPE,
p_loan_number igf_sl_loans_all.loan_number%TYPE,
p_rejected_rec BOOLEAN)
AS
/***************************************************************
Created By : mesriniv
Date Created By : 2000/12/07
Purpose : To update the IGF_SL_LOR table
based on validations.
Known Limitations,Enhancements or Remarks
Change History :
Who When What
bvisvana 21-Sep-2005 Bug # 4168692 - IGF_SL_CL_INV_COMB_RT_RC changed to IGF_SL_CL_INV_COMB_PT_RC
ridas 17-Sep-2004 Bug #3691153: Query optimized by using the table igf_sl_cl_recipient
instead of the view igf_sl_cl_recipient_v
bkkumar 02-04-04 FACR116 - The lender related comparison not required when "ALT" Loan
bkkumar 08-oct-2003 Bug 3104228
a) Impact of adding the relationship_cd
in igf_sl_lor_all table and obsoleting
BORW_LENDER_ID, DUNS_BORW_LENDER_ID,
GUARANTOR_ID, DUNS_GUARNT_ID,
LENDER_ID, DUNS_LENDER_ID
LEND_NON_ED_BRC_ID, RECIPIENT_ID
RECIPIENT_TYPE,DUNS_RECIP_ID
RECIP_NON_ED_BRC_ID columns.
b) The DUNS_BORW_LENDER_ID
DUNS_GUARNT_ID
DUNS_LENDER_ID
DUNS_RECIP_ID columns are osboleted from the
igf_sl_lor_loc_all table.
bkkumar 18-sep-2003 Bug # 3104228 FA 122 Loan Enhancements
In update_lor procedure changed the condition that
loan status should be accepted if
guarantee status is 20 or 40.
agairola 15-Mar-2002 Modified the IGF_SL_LOANS_PKG update row call
for Borrower Determination as part of Refunds DLD - 2144600
masehgal 17-Feb-2002 # 2216956 FACR007
Added Elec_mpn_ind , Borrow_sign_ind
***************************************************************/
l_row_id VARCHAR2(25);
SKIP_UPDATE_LOANS EXCEPTION;
SELECT *
FROM igf_sl_lor_v
WHERE loan_id IN (SELECT loan_id FROM igf_sl_loans_all
WHERE NVL(external_loan_id_txt,loan_number) = p_loan_number);
SELECT igf_sl_loans.* FROM igf_sl_loans
WHERE NVL(external_loan_id_txt,loan_number)= p_loan_number FOR UPDATE OF loan_status NOWAIT;
SELECT
lender_id,
lend_non_ed_brc_id,
guarantor_id,
recipient_id,
recip_non_ed_brc_id,
enabled,
relationship_cd
FROM
igf_sl_cl_recipient
WHERE
lender_id = lv_lender_id AND
guarantor_id = lv_guarant_id AND
recipient_id = lv_recipient_id AND
NVL(lend_non_ed_brc_id,'*') = NVL(lv_lend_non_ed_id,'*') AND
NVL(recip_non_ed_brc_id,'*') = NVL(lv_recip_non_ed_id,'*');
SELECT
relationship_cd
FROM
igf_sl_cl_setup_all
WHERE relationship_cd = p_rel_code
AND ci_cal_type = p_cal_type
AND ci_sequence_number = p_seq_number;
SELECT lar.loan_number
,lar.loan_status
,lar.loan_chg_status
FROM igf_sl_loans_all lar
WHERE NVL(external_loan_id_txt,loan_number) = cp_v_loan_number;
gv_debug_str := 'UPDATE_LOR - 1' || ' ';
RAISE SKIP_UPDATE_LOANS;
RAISE SKIP_UPDATE_LOANS;
RAISE SKIP_UPDATE_LOANS;
gv_debug_str := gv_debug_str || 'UPDATE_LOR - 2' || ' ';
gv_debug_str := gv_debug_str || 'UPDATE_LOR - 3' || ' ';
gv_debug_str := gv_debug_str || 'UPDATE_LOR - 4' || ' ';
gv_debug_str := gv_debug_str || 'UPDATE_LOR - 6' || ' ';
gv_debug_str := gv_debug_str || 'UPDATE_LOR - 7' || ' ';
gv_debug_str := gv_debug_str || 'UPDATE_LOR - 8' || ' ';
gv_debug_str := gv_debug_str || 'UPDATE_LOR - 12' || ' ';
gv_debug_str := gv_debug_str || 'UPDATE_LOR - 10' || ' ';
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig_ack.update_lor.debug',gv_debug_str);
gv_debug_str := gv_debug_str || 'UPDATE_LOR - 11' || ' ';
gv_debug_str := gv_debug_str || 'UPDATE_LOR - 12' || ' ';
gv_debug_str := gv_debug_str || 'UPDATE_LOR - 13' || ' ';
log_to_fnd(p_v_module => 'UPDATE_LOR',
p_v_string => ' loan status <> S'
);
RAISE skip_update_loans;
log_to_fnd(p_v_module => 'UPDATE_LOR',
p_v_string => ' loan status <> S and lor rec_type_ind NOT IN (A,C,T)'
);
RAISE skip_update_loans;
log_to_fnd(p_v_module => 'UPDATE_LOR',
p_v_string => ' Response Record type = X'
);
RAISE skip_update_loans;
log_to_fnd(p_v_module => 'UPDATE_LOR',
p_v_string => ' R Record cannot be uploaded into the system. '||
' Loan status is not Accepted and Loan change status is not sent'
);
RAISE skip_update_loans;
log_to_fnd(p_v_module => 'UPDATE_LOR',
p_v_string => ' loan status <> S'
);
RAISE skip_update_loans;
log_to_fnd(p_v_module => 'UPDATE_LOR',
p_v_string => ' loan status <> S and lor rec_type_ind NOT IN (A,C,T)'
);
RAISE skip_update_loans;
log_to_fnd(p_v_module => 'UPDATE_LOR',
p_v_string => ' send record type not in A and C'
);
RAISE skip_update_loans;
log_to_fnd(p_v_module => 'UPDATE_LOR',
p_v_string => ' loan status <> S'
);
RAISE skip_update_loans;
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 => 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_req_serial_loan_code => tbh_rec.req_serial_loan_code ,
x_act_serial_loan_code => loaded_1rec.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_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 => loaded_1rec.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 => loaded_1rec.guarantee_amt ,
x_guarantee_date => loaded_1rec.guarantee_date ,
x_guarnt_amt_redn_code => loaded_1rec.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 => loaded_1rec.lend_apprv_denied_code ,
x_lend_apprv_denied_date => loaded_1rec.lend_apprv_denied_date ,
x_lend_status_code => tbh_rec.lend_status_code ,
x_lend_status_date => tbh_rec.lend_status_date ,
x_guarnt_adj_ind => loaded_1rec.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 => loaded_1rec.lender_id ,
x_duns_borw_lender_id => NULL ,
x_guarantor_id => loaded_1rec.guarantor_id ,
x_duns_guarnt_id => NULL ,
x_prc_type_code => tbh_rec.prc_type_code ,
x_cl_seq_number => loaded_1rec.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 => loaded_1rec.lend_non_ed_brc_id ,
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 => tbh_rec.cl_rec_status ,
x_cl_rec_status_last_update => loaded_1rec.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_mpn_confirm_code => loaded_1rec.mpn_confirm_ind ,
x_resp_to_orig_code => tbh_rec.resp_to_orig_code ,
x_appl_loan_phase_code => loaded_1rec.appl_loan_phase_code ,
x_appl_loan_phase_code_chg => loaded_1rec.appl_loan_phase_code_chg ,
x_appl_send_error_codes => tbh_rec.appl_send_error_codes ,
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 => loaded_1rec.act_interest_rate ,
x_service_type_code => loaded_1rec.service_type_code ,
x_rev_notice_of_guarnt => loaded_1rec.rev_notice_of_guarnt ,
x_sch_refund_amt => loaded_1rec.sch_refund_amt ,
x_sch_refund_date => loaded_1rec.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 => tbh_rec.p_ssn_chg_date ,
x_p_dob_chg_date => tbh_rec.p_dob_chg_date ,
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 => loaded_1rec.b_signature_code ,
x_p_signature_date => loaded_1rec.b_signature_date ,
x_s_ssn_chg_date => tbh_rec.s_ssn_chg_date ,
x_s_dob_chg_date => tbh_rec.s_dob_chg_date ,
x_s_permt_addr_chg_date => tbh_rec.s_permt_addr_chg_date ,
x_s_local_addr_chg_date => tbh_rec.s_local_addr_chg_date ,
x_s_default_status => tbh_rec.s_default_status ,
x_s_signature_code => lv_s_signature_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_elec_mpn_ind => tbh_rec.elec_mpn_ind ,
x_borr_sign_ind => loaded_1rec.borr_sign_ind ,
x_stud_sign_ind => lv_stud_sign_ind ,
x_borr_credit_auth_code => loaded_1rec.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_external_loan_id_txt => tbh_rec.external_loan_id_txt ,
x_deferment_request_code => lv_defer_req_code ,
x_eft_authorization_code => loaded_1rec.eft_auth_code ,
x_requested_loan_amt => tbh_rec.requested_loan_amt ,
x_actual_record_type_code => tbh_rec.actual_record_type_code ,
x_reinstatement_amt => loaded_1rec.amt_avail_for_reinst ,
x_school_use_txt => loaded_1rec.school_use_txt ,
x_lender_use_txt => loaded_1rec.lender_use_txt ,
x_guarantor_use_txt => loaded_1rec.guarantor_use_txt ,
x_fls_approved_amt => loaded_1rec.fls_approved_amt ,
x_flu_approved_amt => loaded_1rec.flu_approved_amt ,
x_flp_approved_amt => loaded_1rec.flp_approved_amt ,
x_alt_approved_amt => loaded_1rec.alt_approved_amt ,
x_loan_app_form_code => loaded_1rec.fed_appl_form_code ,
x_override_grade_level_code => tbh_rec.override_grade_level_code ,
x_acad_begin_date => tbh_rec.acad_begin_date ,
x_acad_end_date => tbh_rec.acad_end_date ,
x_b_alien_reg_num_txt => loaded_1rec.b_alien_reg_num_txt ,
x_esign_src_typ_cd => NVL(tbh_rec.esign_src_typ_cd,loaded_1rec.esign_src_typ_cd)
);
gv_debug_str := gv_debug_str || 'UPDATE_LOR - 14' || ' ';
SELECT lor_loc.*
FROM igf_sl_lor_loc lor_loc
WHERE loan_id = tbh_rec.loan_id FOR UPDATE OF loan_status NOWAIT;
gv_debug_str := gv_debug_str || 'UPDATE_LOR-15' ||' ';
igf_sl_lor_loc_pkg.update_row (
X_Mode => 'R' ,
x_rowid => lorloc_rec.row_id ,
x_loan_id => lorloc_rec.loan_id ,
x_origination_id => lorloc_rec.origination_id ,
x_loan_number => lorloc_rec.loan_number ,
x_loan_type => lorloc_rec.loan_type ,
x_loan_amt_offered => lorloc_rec.loan_amt_offered ,
x_loan_amt_accepted => lorloc_rec.loan_amt_accepted ,
x_loan_per_begin_date => lorloc_rec.loan_per_begin_date ,
x_loan_per_end_date => lorloc_rec.loan_per_end_date ,
x_acad_yr_begin_date => lorloc_rec.acad_yr_begin_date ,
x_acad_yr_end_date => lorloc_rec.acad_yr_end_date ,
x_loan_status => lorloc_rec.loan_status ,
x_loan_status_date => lorloc_rec.loan_status_date ,
x_loan_chg_status => lorloc_rec.loan_chg_status ,
x_loan_chg_status_date => lorloc_rec.loan_chg_status_date ,
x_req_serial_loan_code => lorloc_rec.req_serial_loan_code ,
x_act_serial_loan_code => loaded_1rec.act_serial_loan_code ,
x_active => lorloc_rec.active ,
x_active_date => lorloc_rec.active_date ,
x_sch_cert_date => lorloc_rec.sch_cert_date ,
x_orig_status_flag => lorloc_rec.orig_status_flag ,
x_orig_batch_id => lorloc_rec.orig_batch_id ,
x_orig_batch_date => lorloc_rec.orig_batch_date ,
x_chg_batch_id => lorloc_rec.chg_batch_id ,
x_orig_ack_date => lorloc_rec.orig_ack_date ,
x_credit_override => loaded_1rec.credit_status_code ,
x_credit_decision_date => loaded_1rec.credit_status_date ,
x_pnote_delivery_code => lorloc_rec.pnote_delivery_code ,
x_pnote_status => loaded_1rec.pnote_status_code ,
x_pnote_status_date => loaded_1rec.pnote_status_date ,
x_pnote_id => lorloc_rec.pnote_id ,
x_pnote_print_ind => lorloc_rec.pnote_print_ind ,
x_pnote_accept_amt => lorloc_rec.pnote_accept_amt ,
x_pnote_accept_date => lorloc_rec.pnote_accept_date ,
x_p_signature_code => loaded_1rec.b_signature_code ,
x_p_signature_date => loaded_1rec.b_signature_date ,
x_s_signature_code => loaded_1rec.s_signature_code ,
x_unsub_elig_for_heal => lorloc_rec.unsub_elig_for_heal ,
x_disclosure_print_ind => lorloc_rec.disclosure_print_ind ,
x_orig_fee_perct => lorloc_rec.orig_fee_perct ,
x_borw_confirm_ind => loaded_1rec.borw_confirm_ind ,
x_borw_interest_ind => lorloc_rec.borw_interest_ind ,
x_unsub_elig_for_depnt => lorloc_rec.unsub_elig_for_depnt ,
x_guarantee_amt => loaded_1rec.guarantee_amt ,
x_guarantee_date => loaded_1rec.guarantee_date ,
x_guarnt_adj_ind => loaded_1rec.guarnt_adj_ind ,
x_guarnt_amt_redn_code => loaded_1rec.guarnt_amt_redn_code ,
x_guarnt_status_code => loaded_1rec.guarnt_status_code ,
x_guarnt_status_date => loaded_1rec.guarnt_status_date ,
x_lend_apprv_denied_code => loaded_1rec.lend_apprv_denied_code ,
x_lend_apprv_denied_date => loaded_1rec.lend_apprv_denied_date ,
x_lend_status_code => loaded_1rec.lender_status_code ,
x_lend_status_date => loaded_1rec.lender_status_date ,
x_grade_level_code => lorloc_rec.grade_level_code ,
x_enrollment_code => lorloc_rec.enrollment_code ,
x_anticip_compl_date => lorloc_rec.anticip_compl_date ,
x_borw_lender_id => loaded_1rec.lender_id ,
x_duns_borw_lender_id => NULL ,
x_guarantor_id => loaded_1rec.guarantor_id ,
x_duns_guarnt_id => NULL ,
x_prc_type_code => lorloc_rec.prc_type_code ,
x_rec_type_ind => lorloc_rec.rec_type_ind ,
x_cl_loan_type => lorloc_rec.cl_loan_type ,
x_cl_seq_number => loaded_1rec.cl_seq_number ,
x_last_resort_lender => lorloc_rec.last_resort_lender ,
x_lender_id => loaded_1rec.lender_id ,
x_duns_lender_id => NULL ,
x_lend_non_ed_brc_id => loaded_1rec.lend_non_ed_brc_id ,
x_recipient_id => lorloc_rec.recipient_id ,
x_recipient_type => lorloc_rec.recipient_type ,
x_duns_recip_id => NULL ,
x_recip_non_ed_brc_id => lorloc_rec.recip_non_ed_brc_id ,
x_cl_rec_status => tbh_rec.cl_rec_status ,
x_cl_rec_status_last_update => loaded_1rec.cl_rec_status_last_update ,
x_alt_prog_type_code => lorloc_rec.alt_prog_type_code ,
x_alt_appl_ver_code => lorloc_rec.alt_appl_ver_code ,
x_borw_outstd_loan_code => lorloc_rec.borw_outstd_loan_code ,
x_mpn_confirm_code => loaded_1rec.mpn_confirm_ind ,
x_resp_to_orig_code => lorloc_rec.resp_to_orig_code ,
x_appl_loan_phase_code => loaded_1rec.appl_loan_phase_code ,
x_appl_loan_phase_code_chg => loaded_1rec.appl_loan_phase_code_chg ,
x_tot_outstd_stafford => lorloc_rec.tot_outstd_stafford ,
x_tot_outstd_plus => lorloc_rec.tot_outstd_plus ,
x_alt_borw_tot_debt => lorloc_rec.alt_borw_tot_debt ,
x_act_interest_rate => loaded_1rec.act_interest_rate ,
x_service_type_code => loaded_1rec.service_type_code ,
x_rev_notice_of_guarnt => loaded_1rec.rev_notice_of_guarnt ,
x_sch_refund_amt => loaded_1rec.sch_refund_amt ,
x_sch_refund_date => loaded_1rec.sch_refund_date ,
x_uniq_layout_vend_code => lorloc_rec.uniq_layout_vend_code ,
x_uniq_layout_ident_code => lorloc_rec.uniq_layout_ident_code ,
x_p_person_id => lorloc_rec.p_person_id ,
x_p_ssn => lorloc_rec.p_ssn ,
x_p_ssn_chg_date => lorloc_rec.p_ssn_chg_date ,
x_p_last_name => lorloc_rec.p_last_name ,
x_p_first_name => lorloc_rec.p_first_name ,
x_p_middle_name => lorloc_rec.p_middle_name ,
x_p_permt_addr1 => lorloc_rec.p_permt_addr1 ,
x_p_permt_addr2 => lorloc_rec.p_permt_addr2 ,
x_p_permt_city => lorloc_rec.p_permt_city ,
x_p_permt_state => lorloc_rec.p_permt_state ,
x_p_permt_zip => lorloc_rec.p_permt_zip ,
x_p_permt_addr_chg_date => lorloc_rec.p_permt_addr_chg_date ,
x_p_permt_phone => lorloc_rec.p_permt_phone ,
x_p_email_addr => lorloc_rec.p_email_addr ,
x_p_date_of_birth => lorloc_rec.p_date_of_birth ,
x_p_dob_chg_date => lorloc_rec.p_dob_chg_date ,
x_p_license_num => lorloc_rec.p_license_num ,
x_p_license_state => lorloc_rec.p_license_state ,
x_p_citizenship_status => lorloc_rec.p_citizenship_status ,
x_p_alien_reg_num => lorloc_rec.p_alien_reg_num ,
x_p_default_status => lorloc_rec.p_default_status ,
x_p_foreign_postal_code => lorloc_rec.p_foreign_postal_code ,
x_p_state_of_legal_res => lorloc_rec.p_state_of_legal_res ,
x_p_legal_res_date => lorloc_rec.p_legal_res_date ,
x_s_ssn => lorloc_rec.s_ssn ,
x_s_ssn_chg_date => lorloc_rec.s_ssn_chg_date ,
x_s_last_name => lorloc_rec.s_last_name ,
x_s_first_name => lorloc_rec.s_first_name ,
x_s_middle_name => lorloc_rec.s_middle_name ,
x_s_permt_addr1 => lorloc_rec.s_permt_addr1 ,
x_s_permt_addr2 => lorloc_rec.s_permt_addr2 ,
x_s_permt_city => lorloc_rec.s_permt_city ,
x_s_permt_state => lorloc_rec.s_permt_state ,
x_s_permt_zip => lorloc_rec.s_permt_zip ,
x_s_permt_addr_chg_date => lorloc_rec.s_permt_addr_chg_date ,
x_s_permt_phone => lorloc_rec.s_permt_phone ,
x_s_local_addr1 => lorloc_rec.s_local_addr1 ,
x_s_local_addr2 => lorloc_rec.s_local_addr2 ,
x_s_local_city => lorloc_rec.s_local_city ,
x_s_local_state => lorloc_rec.s_local_state ,
x_s_local_zip => lorloc_rec.s_local_zip ,
x_s_local_addr_chg_date => lorloc_rec.s_local_addr_chg_date ,
x_s_email_addr => lorloc_rec.s_email_addr ,
x_s_date_of_birth => lorloc_rec.s_date_of_birth ,
x_s_dob_chg_date => lorloc_rec.s_dob_chg_date ,
x_s_license_num => lorloc_rec.s_license_num ,
x_s_license_state => lorloc_rec.s_license_state ,
x_s_depncy_status => lorloc_rec.s_depncy_status ,
x_s_default_status => lorloc_rec.s_default_status ,
x_s_citizenship_status => lorloc_rec.s_citizenship_status ,
x_s_alien_reg_num => lorloc_rec.s_alien_reg_num ,
x_s_foreign_postal_code => lorloc_rec.s_foreign_postal_code ,
x_pnote_batch_id => lorloc_rec.pnote_batch_id ,
x_pnote_ack_date => lorloc_rec.pnote_ack_date ,
x_pnote_mpn_ind => lorloc_rec.pnote_mpn_ind ,
x_award_id => lorloc_rec.award_id ,
x_base_id => lorloc_rec.base_id ,
x_document_id_txt => lorloc_rec.document_id_txt ,
x_loan_key_num => lorloc_rec.loan_key_num ,
x_interest_rebate_percent_num => lorloc_rec.interest_rebate_percent_num ,
x_fin_award_year => lorloc_rec.fin_award_year ,
x_cps_trans_num => lorloc_rec.cps_trans_num ,
x_atd_entity_id_txt => lorloc_rec.atd_entity_id_txt ,
x_rep_entity_id_txt => lorloc_rec.rep_entity_id_txt ,
x_source_entity_id_txt => lorloc_rec.source_entity_id_txt ,
x_pymt_servicer_amt => lorloc_rec.pymt_servicer_amt ,
x_pymt_servicer_date => lorloc_rec.pymt_servicer_date ,
x_book_loan_amt => lorloc_rec.book_loan_amt ,
x_book_loan_amt_date => lorloc_rec.book_loan_amt_date ,
x_s_chg_birth_date => lorloc_rec.s_chg_birth_date ,
x_s_chg_ssn => lorloc_rec.s_chg_ssn ,
x_s_chg_last_name => lorloc_rec.s_chg_last_name ,
x_b_chg_birth_date => lorloc_rec.b_chg_birth_date ,
x_b_chg_ssn => lorloc_rec.b_chg_ssn ,
x_b_chg_last_name => lorloc_rec.b_chg_last_name ,
x_note_message => lorloc_rec.note_message ,
x_full_resp_code => lorloc_rec.full_resp_code ,
x_s_permt_county => lorloc_rec.s_permt_county ,
x_b_permt_county => lorloc_rec.b_permt_county ,
x_s_permt_country => lorloc_rec.s_permt_country ,
x_b_permt_country => lorloc_rec.b_permt_country ,
x_crdt_decision_status => lorloc_rec.crdt_decision_status ,
x_external_loan_id_txt => lorloc_rec.external_loan_id_txt ,
x_deferment_request_code => loaded_1rec.defer_req_code ,
x_eft_authorization_code => loaded_1rec.eft_auth_code ,
x_requested_loan_amt => lorloc_rec.requested_loan_amt ,
x_actual_record_type_code => tbh_rec.actual_record_type_code ,
x_reinstatement_amt => loaded_1rec.amt_avail_for_reinst ,
x_school_use_txt => loaded_1rec.school_use_txt ,
x_lender_use_txt => loaded_1rec.lender_use_txt ,
x_guarantor_use_txt => loaded_1rec.guarantor_use_txt ,
x_fls_approved_amt => loaded_1rec.fls_approved_amt ,
x_flu_approved_amt => loaded_1rec.flu_approved_amt ,
x_flp_approved_amt => loaded_1rec.flp_approved_amt ,
x_alt_approved_amt => loaded_1rec.alt_approved_amt ,
x_loan_app_form_code => loaded_1rec.fed_appl_form_code ,
x_alt_borrower_ind_flag => lorloc_rec.alt_borrower_ind_flag ,
x_school_id_txt => lorloc_rec.school_id_txt ,
x_cost_of_attendance_amt => lorloc_rec.cost_of_attendance_amt ,
x_expect_family_contribute_amt => lorloc_rec.expect_family_contribute_amt ,
x_established_fin_aid_amount => lorloc_rec.established_fin_aid_amount ,
x_borower_electronic_sign_flag => loaded_1rec.borr_sign_ind ,
x_student_electronic_sign_flag => loaded_1rec.stud_sign_ind ,
x_borower_credit_authoriz_flag => loaded_1rec.borr_credit_auth_code ,
x_mpn_type_flag => lorloc_rec.mpn_type_flag ,
x_esign_src_typ_cd => loaded_1rec.esign_src_typ_cd
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig_ack.update_lor.debug',gv_debug_str||' loaded_1rec.prc_type_code ' || loaded_1rec.prc_type_code);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig_ack.update_lor.debug',gv_debug_str||' tbh_rec.guarnt_status_code ' || tbh_rec.guarnt_status_code);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig_ack.update_lor.debug',gv_debug_str||' tbh_rec.lend_status_code ' || tbh_rec.lend_status_code);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig_ack.update_lor.debug',gv_debug_str||' tbh_rec.pnote_status ' || tbh_rec.pnote_status);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig_ack.update_lor.debug',gv_debug_str||' tbh_rec.credit_override ' || tbh_rec.crdt_decision_status );
gv_debug_str := gv_debug_str || 'UPDATE_LOR-16' || ' ';
log_to_fnd(p_v_module => 'update_lor',
p_v_string => ' Loan is Accepted'
);
gv_debug_str := gv_debug_str || 'UPDATE_LOR-17' || ' ';
log_to_fnd(p_v_module => 'update_lor',
p_v_string => ' Loan is Rejected'
);
gv_debug_str := gv_debug_str || 'UPDATE_LOR-18' || ' ';
log_to_fnd(p_v_module => 'update_lor',
p_v_string => ' Loan is Accepted'
);
log_to_fnd(p_v_module => 'update_lor',
p_v_string => ' Loan is Rejected'
);
log_to_fnd(p_v_module => 'update_lor',
p_v_string => ' Loan is Terminated'
);
log_to_fnd(p_v_module => 'update_lor',
p_v_string => ' Loan is Accepted'
);
log_to_fnd(p_v_module => 'update_lor',
p_v_string => ' Loan is Accepted'
);
log_to_fnd(p_v_module => 'update_lor',
p_v_string => ' Loan is Rejected'
);
log_to_fnd(p_v_module => 'update_lor',
p_v_string => ' Loan is rejected'
);
log_to_fnd(p_v_module => 'update_lor',
p_v_string => ' Loan is Terminated'
);
gv_debug_str := gv_debug_str || 'UPDATE_LOR-19' || ' ';
igf_sl_loans_pkg.update_row (
X_Mode => 'R',
x_rowid => loan_rec.row_id,
x_loan_id => loan_rec.loan_id,
x_award_id => loan_rec.award_id,
x_seq_num => loan_rec.seq_num,
x_loan_number => loan_rec.loan_number,
x_loan_per_begin_date => loan_rec.loan_per_begin_date,
x_loan_per_end_date => loan_rec.loan_per_end_date,
x_loan_status => l_loan_status,
x_loan_status_date => TRUNC(SYSDATE),
x_loan_chg_status => loan_rec.loan_chg_status,
x_loan_chg_status_date => loan_rec.loan_chg_status_date,
x_active => loan_rec.active,
x_active_date => loan_rec.active_date,
x_borw_detrm_code => loan_rec.borw_detrm_code,
x_legacy_record_flag => NULL,
x_external_loan_id_txt => loan_rec.external_loan_id_txt
);
gv_debug_str := gv_debug_str || 'UPDATE_LOR-20' || ' ';
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig_ack.update_lor.debug',gv_debug_str);
WHEN SKIP_UPDATE_LOANS THEN
NULL;
fnd_message.set_token('NAME','IGF_SL_CL_ACK.UPDATE_LOR');
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_cl_orig_ack.update_lor.exception',gv_debug_str||' '|| SQLERRM);
END update_lor;
SELECT award_id, NVL(COUNT(disb_num),0) FROM igf_aw_awd_disb
WHERE award_id = (SELECT award_id FROM igf_sl_loans
WHERE NVL(external_loan_id_txt,loan_number) = p_loan_number)
GROUP BY award_id;
SELECT NVL(COUNT(resp8.clrp8_id),0) FROM igf_sl_cl_resp_r8_all resp8
WHERE clrp1_id = loaded_1rec.clrp1_id
AND resp8.resp_record_status = p_rec_status;
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 clrp8_id, disb_gross_amt FROM igf_sl_cl_resp_r8_all clrp8
WHERE clrp1_id = loaded_1rec.clrp1_id
)
UNION ALL
(SELECT clrp8_id, disb_gross_amt FROM igf_sl_cl_resp_r8_all clrp8
WHERE clrp1_id = loaded_1rec.clrp1_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_cl_resp_r8
WHERE clrp1_id = loaded_1rec.clrp1_id
AND resp_record_status = p_rec_status
ORDER By clrp8_id;
SELECT * FROM igf_aw_awd_disb
WHERE award_id = l_award_id
ORDER BY disb_num;
PROCEDURE update_resp8_rec_status(p_clrp1_id igf_sl_cl_resp_r8_all.clrp1_id%TYPE,
p_clrp8_id igf_sl_cl_resp_r8_all.clrp8_id%TYPE,
p_resp_record_status igf_sl_cl_resp_r8_all.resp_record_status%TYPE)
AS
/***************************************************************
Created By : mesriniv
Date Created By : 2000/12/07
Purpose : To Update the Record Status of the Response8
Records as Processed
Known Limitations,Enhancements or Remarks
Change History :
-- Who When What
-- mnade 21-Jan-2005 Bug - 4136563 - Disbursement update problems.
-- The disbursements in @8 will be updated based on the flag and
-- award disbursement changes status.
***************************************************************/
--Select Response8 Records for Updation
CURSOR cur_resp_r8
IS
SELECT *
FROM igf_sl_cl_resp_r8
WHERE clrp1_id = p_clrp1_id
AND clrp8_id = NVL(p_clrp8_id, clrp8_id)
AND resp_record_status = 'N';
gv_debug_str := 'UPDATE_RESP8_REC_STATUS-1' || ' targetStatus - '|| l_resp_record_status || ' ';
gv_debug_str := gv_debug_str || 'UPDATE_RESP8_REC_STATUS-2' || ' ';
igf_sl_cl_resp_r8_pkg.update_row (
X_Mode => 'R',
x_rowid => resp_r8_rec.row_id,
x_clrp1_id => resp_r8_rec.clrp1_id,
x_clrp8_id => resp_r8_rec.clrp8_id,
x_disb_date => resp_r8_rec.disb_date,
x_disb_gross_amt => resp_r8_rec.disb_gross_amt,
x_orig_fee => resp_r8_rec.orig_fee,
x_guarantee_fee => resp_r8_rec.guarantee_fee,
x_net_disb_amt => resp_r8_rec.net_disb_amt,
x_disb_hold_rel_ind => resp_r8_rec.disb_hold_rel_ind,
x_disb_status => resp_r8_rec.disb_status,
x_guarnt_fee_paid => resp_r8_rec.guarnt_fee_paid,
x_orig_fee_paid => resp_r8_rec.orig_fee_paid,
x_resp_record_status => l_resp_record_status,
x_layout_owner_code_txt => resp_r8_rec.layout_owner_code_txt,
x_layout_version_code_txt => resp_r8_rec.layout_version_code_txt,
x_record_code_txt => resp_r8_rec.record_code_txt
-- x_direct_to_borr_flag => resp_r8_rec.direct_to_borr_flag
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_orig_ack.update_resp8_rec_status.debug',gv_debug_str);
END update_resp8_rec_status;
SELECT clrp8.* FROM igf_sl_cl_resp_r8 clrp8
WHERE clrp1_id = loaded_1rec.clrp1_id
AND resp_record_status = p_rec_status;
SELECT adisb.* FROM igf_aw_awd_disb adisb
WHERE award_id = p_award_id AND disb_num = p_disb_num
FOR UPDATE OF manual_hold_ind NOWAIT;
Update the disb_accepted amt with the disb_gross_amt from response
***************************************************************/
AS
CURSOR cur_loc_disb (p_rec_status igf_sl_cl_resp_r8_all.resp_record_status%TYPE)
IS
SELECT clrp8.* FROM igf_sl_cl_resp_r8 clrp8
WHERE clrp1_id = loaded_1rec.clrp1_id
AND resp_record_status = p_rec_status;
SELECT adisb.* FROM igf_aw_awd_disb adisb
WHERE award_id = p_award_id AND disb_num = p_disb_num
FOR UPDATE OF manual_hold_ind NOWAIT;
IF g_c_update_disb_dtls = 'Y' THEN -- Update Flag Check
fnd_message.set_name('IGF','IGF_SL_CL_UPD_DISB_DTLS');
igf_aw_awd_disb_pkg.update_row (
x_Mode => 'R',
x_rowid => tbh_rec.row_id,
x_award_id => tbh_rec.award_id,
x_disb_num => tbh_rec.disb_num,
x_tp_cal_type => tbh_rec.tp_cal_type,
x_tp_sequence_number => tbh_rec.tp_sequence_number,
x_disb_gross_amt => loc_disb.disb_gross_amt,
x_fee_1 => loc_disb.orig_fee,
x_fee_2 => loc_disb.guarantee_fee,
x_disb_net_amt => loc_disb.net_disb_amt,
x_disb_date => loc_disb.disb_date,
x_trans_type => tbh_rec.trans_type,
x_elig_status => tbh_rec.elig_status,
x_elig_status_date => tbh_rec.elig_status_date,
x_affirm_flag => tbh_rec.affirm_flag,
x_hold_rel_ind => loc_disb.disb_hold_rel_ind,
x_manual_hold_ind => tbh_rec.manual_hold_ind,
x_disb_status => loc_disb.disb_status,
x_disb_status_date => TRUNC(SYSDATE),
x_late_disb_ind => tbh_rec.late_disb_ind,
x_fund_dist_mthd => tbh_rec.fund_dist_mthd,
x_prev_reported_ind => tbh_rec.prev_reported_ind,
x_fund_release_date => tbh_rec.fund_release_date,
x_fund_status => tbh_rec.fund_status,
x_fund_status_date => tbh_rec.fund_status_date,
x_fee_paid_1 => loc_disb.orig_fee_paid,
x_fee_paid_2 => loc_disb.guarnt_fee_paid,
x_cheque_number => tbh_rec.cheque_number,
x_ld_cal_type => tbh_rec.ld_cal_type,
x_ld_sequence_number => tbh_rec.ld_sequence_number,
x_disb_accepted_amt => loc_disb.disb_gross_amt, -- tbh_rec.disb_accepted_amt, -- bvisvana.SBCC Bug # 4575843
x_disb_paid_amt => tbh_rec.disb_paid_amt,
x_rvsn_id => tbh_rec.rvsn_id,
x_int_rebate_amt => tbh_rec.int_rebate_amt,
x_force_disb => tbh_rec.force_disb,
x_min_credit_pts => tbh_rec.min_credit_pts,
x_disb_exp_dt => tbh_rec.disb_exp_dt,
x_verf_enfr_dt => tbh_rec.verf_enfr_dt,
x_fee_class => tbh_rec.fee_class,
x_show_on_bill => tbh_rec.show_on_bill,
x_attendance_type_code => tbh_rec.attendance_type_code,
x_base_attendance_type_code => tbh_rec.base_attendance_type_code,
x_payment_prd_st_date => tbh_rec.payment_prd_st_date,
x_change_type_code => tbh_rec.change_type_code,
x_fund_return_mthd_code => tbh_rec.fund_return_mthd_code
-- x_direct_to_borr_flag => loc_disb.direct_to_borr_flag
);
ELSE -- Else Update Flag Check
l_resp_record_status := 'D';
END IF; -- End Update Flag Check
update_resp8_rec_status(loc_disb.clrp1_id,
loc_disb.clrp8_id,
l_resp_record_status);
SELECT adisb.* FROM igf_aw_awd_disb adisb
WHERE award_id = p_award_id and
trans_type = p_trans_type ;
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 = cp_release_flag;
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 => 'CL',
x_hold_type => 'SYSTEM',
x_hold_date => TRUNC(sysdate),
x_release_flag => 'N',
x_release_reason => NULL,
x_release_date => NULL
);
update_resp8_rec_status(loaded_1rec.clrp1_id, NULL, 'N');
PROCEDURE insert_into_resp_r4(p_clrp1_id igf_sl_cl_resp_r1_all.clrp1_id%TYPE,
p_r4_record igf_sl_load_file_t.record_data%TYPE)
/***************************************************************
Created By : mesriniv
Date Created By : 2001/05/13
Purpose : To Insert File data into IGF_SL_CL_RESP_R4
ENH Bug No.:1769051
Bug Desc :Development of Loans Processing for Nov 2001.
Known Limitations,Enhancements or Remarks
Change History :
Who When What
***************************************************************/
AS
l_rowid VARCHAR2(25) DEFAULT NULL;
BEGIN --for the procedure insert_into_resp_r4
gv_debug_str := 'INSERT_INTO_RESP_R4 - 1' || ' ';
igf_sl_cl_resp_r4_pkg.insert_row (
x_mode => 'R',
x_rowid => l_rowid,
x_clrp1_id => p_clrp1_id, --Corresponding id already inserted for @1 Record
x_loan_number => l_loan_number,
x_fed_stafford_loan_debt => TO_NUMBER (LTRIM(RTRIM(SUBSTR(p_r4_record,9,5)))),
x_fed_sls_debt => TO_NUMBER (LTRIM(RTRIM(SUBSTR(p_r4_record,14,5)))),
x_heal_debt => TO_NUMBER (LTRIM(RTRIM(SUBSTR(p_r4_record,19,6)))),
x_perkins_debt => TO_NUMBER (LTRIM(RTRIM(SUBSTR(p_r4_record,25,5)))),
x_other_debt => TO_NUMBER (LTRIM(RTRIM(SUBSTR(p_r4_record,30,6)))),
x_crdt_undr_difft_name => LTRIM(RTRIM(SUBSTR(p_r4_record,43,1))),
x_borw_gross_annual_sal => TO_NUMBER (LTRIM(RTRIM(SUBSTR(p_r4_record,384,7)))),
x_borw_other_income => TO_NUMBER (LTRIM(RTRIM(SUBSTR(p_r4_record,391,7)))),
x_student_major => LTRIM(RTRIM(SUBSTR(p_r4_record,454,15))),
x_int_rate_opt => LTRIM(RTRIM(SUBSTR(p_r4_record,571,1))),
x_repayment_opt_code => LTRIM(RTRIM(SUBSTR(p_r4_record,572,1))),
x_stud_mth_housing_pymt => rec_cl_resp_r4.stud_mth_housing_pymt ,
x_stud_mth_crdtcard_pymt => rec_cl_resp_r4.stud_mth_crdtcard_pymt ,
x_stud_mth_auto_pymt => rec_cl_resp_r4.stud_mth_auto_pymt ,
x_stud_mth_ed_loan_pymt => rec_cl_resp_r4.stud_mth_ed_loan_pymt ,
x_stud_mth_other_pymt => rec_cl_resp_r4.stud_mth_other_pymt,
x_cosnr_1_last_name => LTRIM(RTRIM(SUBSTR(p_r4_record,44,35))),
x_cosnr_1_first_name => LTRIM(RTRIM(SUBSTR(p_r4_record,79,12))),
x_cosnr_1_middle_name => LTRIM(RTRIM(SUBSTR(p_r4_record,91,1))),
x_cosnr_1_ssn => LTRIM(RTRIM(SUBSTR(p_r4_record,92,9))),
x_cosnr_1_citizenship => LTRIM(RTRIM(SUBSTR(p_r4_record,101,1))),
x_cosnr_1_addr_line1 => LTRIM(RTRIM(SUBSTR(p_r4_record,102,30))),
x_cosnr_1_addr_line2 => LTRIM(RTRIM(SUBSTR(p_r4_record,132,30))),
x_cosnr_1_city => LTRIM(RTRIM(SUBSTR(p_r4_record,162,24))),
x_cosnr_1_state => LTRIM(RTRIM(SUBSTR(p_r4_record,192,2))),
x_cosnr_1_zip => LTRIM(RTRIM(SUBSTR(p_r4_record,194,5))),
x_cosnr_1_zip_suffix => LTRIM(RTRIM(SUBSTR(p_r4_record,199,4))),
x_cosnr_1_phone => LTRIM(RTRIM(SUBSTR(p_r4_record,203,10))),
x_cosnr_1_sig_code => LTRIM(RTRIM(SUBSTR(p_r4_record,213,1))),
x_cosnr_1_gross_anl_sal => TO_NUMBER (LTRIM(RTRIM(SUBSTR(p_r4_record,398,7)))),
x_cosnr_1_other_income => TO_NUMBER (LTRIM(RTRIM(SUBSTR(p_r4_record,405,7)))),
x_cosnr_1_forn_postal_code => LTRIM(RTRIM(SUBSTR(p_r4_record,426,14))),
x_cosnr_1_forn_phone_prefix => rec_cl_resp_r4.cosnr_1_forn_phone_prefix,
--MN 21-Jan-2005 - Using REPLACE to remove DOB containing 0s.
x_cosnr_1_dob => TO_DATE(REPLACE(LTRIM(RTRIM(SUBSTR(p_r4_record,469,8))), '00000000', NULL),'YYYYMMDD'),
x_cosnr_1_license_state => LTRIM(RTRIM(SUBSTR(p_r4_record,477,2))),
x_cosnr_1_license_num => LTRIM(RTRIM(SUBSTR(p_r4_record,479,20))),
x_cosnr_1_relationship_to => LTRIM(RTRIM(SUBSTR(p_r4_record,559,1))),
x_cosnr_1_years_at_addr => LTRIM(RTRIM(SUBSTR(p_r4_record,563,2))),
x_cosnr_1_mth_housing_pymt => rec_cl_resp_r4.cosnr_1_mth_housing_pymt,
x_cosnr_1_mth_crdtcard_pymt => rec_cl_resp_r4.cosnr_1_mth_crdtcard_pymt,
x_cosnr_1_mth_auto_pymt => rec_cl_resp_r4.cosnr_1_mth_auto_pymt,
x_cosnr_1_mth_ed_loan_pymt => rec_cl_resp_r4.cosnr_1_mth_ed_loan_pymt,
x_cosnr_1_mth_other_pymt => rec_cl_resp_r4.cosnr_1_mth_other_pymt,
x_cosnr_1_crdt_auth_code => rec_cl_resp_r4.cosnr_1_crdt_auth_code,
x_cosnr_2_last_name => LTRIM(RTRIM(SUBSTR(p_r4_record,214,35))),
x_cosnr_2_first_name => LTRIM(RTRIM(SUBSTR(p_r4_record,249,12))),
x_cosnr_2_middle_name => LTRIM(RTRIM(SUBSTR(p_r4_record,261,1))),
x_cosnr_2_ssn => LTRIM(RTRIM(SUBSTR(p_r4_record,262,9))),
x_cosnr_2_citizenship => LTRIM(RTRIM(SUBSTR(p_r4_record,271,1))),
x_cosnr_2_addr_line1 => LTRIM(RTRIM(SUBSTR(p_r4_record,272,30))),
x_cosnr_2_addr_line2 => LTRIM(RTRIM(SUBSTR(p_r4_record,302,30))),
x_cosnr_2_city => LTRIM(RTRIM(SUBSTR(p_r4_record,332,24))),
x_cosnr_2_state => LTRIM(RTRIM(SUBSTR(p_r4_record,362,2))),
x_cosnr_2_zip => LTRIM(RTRIM(SUBSTR(p_r4_record,364,5))),
x_cosnr_2_zip_suffix => LTRIM(RTRIM(SUBSTR(p_r4_record,369,4))),
x_cosnr_2_phone => LTRIM(RTRIM(SUBSTR(p_r4_record,373,10))),
x_cosnr_2_sig_code => LTRIM(RTRIM(SUBSTR(p_r4_record,383,1))),
x_cosnr_2_gross_anl_sal => TO_NUMBER (LTRIM(RTRIM(SUBSTR(p_r4_record,412,7)))),
x_cosnr_2_other_income => TO_NUMBER (LTRIM(RTRIM(SUBSTR(p_r4_record,419,7)))),
x_cosnr_2_forn_postal_code => LTRIM(RTRIM(SUBSTR(p_r4_record,440,14))),
x_cosnr_2_forn_phone_prefix => rec_cl_resp_r4.cosnr_2_forn_phone_prefix,
--MN 21-Jan-2005 - Using REPLACE to remove DOB containing 0s.
x_cosnr_2_dob => TO_DATE(REPLACE(LTRIM(RTRIM(SUBSTR(p_r4_record,499,8))), '00000000', NULL),'YYYYMMDD'),
x_cosnr_2_license_state => LTRIM(RTRIM(SUBSTR(p_r4_record,507,2))) ,
x_cosnr_2_license_num => LTRIM(RTRIM(SUBSTR(p_r4_record,509,20))),
x_cosnr_2_relationship_to => LTRIM(RTRIM(SUBSTR(p_r4_record,565,1))),
x_cosnr_2_years_at_addr => LTRIM(RTRIM(SUBSTR(p_r4_record,569,2))),
x_cosnr_2_mth_housing_pymt => rec_cl_resp_r4.cosnr_2_mth_housing_pymt,
x_cosnr_2_mth_crdtcard_pymt => rec_cl_resp_r4.cosnr_2_mth_crdtcard_pymt,
x_cosnr_2_mth_auto_pymt => rec_cl_resp_r4.cosnr_2_mth_auto_pymt,
x_cosnr_2_mth_ed_loan_pymt => rec_cl_resp_r4.cosnr_2_mth_ed_loan_pymt,
x_cosnr_2_mth_other_pymt => rec_cl_resp_r4.cosnr_2_mth_other_pymt,
x_cosnr_2_crdt_auth_code => rec_cl_resp_r4.cosnr_2_crdt_auth_code,
x_other_loan_amt => TO_NUMBER (LTRIM(RTRIM(SUBSTR(p_r4_record,36,7)))),
x_alt_layout_owner_code_txt => LTRIM(RTRIM(SUBSTR(p_r4_record,3,4))),
x_alt_layout_identi_code_txt => LTRIM(RTRIM(SUBSTR(p_r4_record,7,2))),
x_student_school_phone_txt => LTRIM(RTRIM(SUBSTR(p_r4_record,549,10))),
x_first_csgnr_elec_sign_flag => rec_cl_resp_r4.first_csgnr_elec_sign_flag,
x_second_csgnr_elec_sign_flag => rec_cl_resp_r4.second_csgnr_elec_sign_flag
);
gv_debug_str := 'INSERT_INTO_RESP_R4 - 2' || ' ';
fnd_message.set_token('NAME','IGF_SL_CL_ACK.INSERT_INTO_RESP_R4');
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_cl_orig_ack.insert_into_resp_r4.exception',gv_debug_str||' '|| SQLERRM);
END insert_into_resp_r4;
SELECT borw.*
FROM igf_sl_alt_borw borw
WHERE loan_id=p_loan_id;
SELECT resp4.*
FROM igf_sl_cl_resp_r4 resp4
WHERE clrp1_id= p_clrp1_id;
data_array.DELETE;
SELECT 'OSS' person_rec_type --source of ssn from OSS
,hz.party_number person_number
,hz.party_id person_id
,api_person_id_uf prsn_ssn --unformatted ssn value
,hz.person_first_name firstname
,hz.person_last_name lastname
FROM igs_pe_alt_pers_id api
,igs_pe_person_id_typ pit
,hz_parties hz
WHERE api.person_id_type = pit.person_id_type
AND pit.s_person_id_type = 'SSN'
AND hz.party_id = api.pe_person_id
AND (SYSDATE) BETWEEN api.start_dt AND NVL (api.end_dt, SYSDATE)
AND api.api_person_id_uf = cp_v_ssn
UNION
SELECT 'HRMS' person_rec_type --source of ssn from HRMS
,hz.party_number person_number
,ppf.party_id person_id
,igf_gr_gen.get_ssn_digits(ppf.national_identifier) prsn_ssn
,hz.person_first_name firstname
,hz.person_last_name lastname
FROM per_all_people_f ppf
,per_business_groups_perf pbg
,per_person_types ppt
,hz_parties hz
,hz_person_profiles hp
WHERE igs_en_gen_001.check_hrms_installed = 'Y'
AND pbg.legislation_code = 'US'
AND ppt.system_person_type = 'EMP'
AND ppt.person_type_id = ppf.person_type_id
AND pbg.business_group_id = ppf.business_group_id
AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppf.party_id = hz.party_id
AND hp.effective_end_date IS NULL
AND igf_ap_matching_process_pkg.remove_spl_chr(ppf.national_identifier) = cp_v_ssn;
v_tab_firstname_typ.DELETE;
v_tab_lastname_typ.DELETE;
v_tab_prsn_ssn_typ.DELETE;
v_tab_personid_typ.DELETE;
v_tab_personnum_typ.DELETE;
v_tab_rec_typ.DELETE;
SELECT person_id_type
FROM igs_pe_person_id_typ
WHERE s_person_id_type = cp_v_pers_id_type ;
p_v_string => ' Invoking igs_pe_person_pkg.insert_row '
);
igs_pe_person_pkg.insert_row
(
x_msg_count => l_n_msg_count,
x_msg_data => l_v_msg_data,
x_return_status => l_v_return_status,
x_rowid => l_v_rowid,
x_person_id => l_n_person_id,
x_person_number => l_v_person_number,
x_surname => INITCAP(l_v_person_last_name),
x_middle_name => l_v_person_middle_name,
x_given_names => INITCAP(l_v_person_first_name),
x_sex => NULL,
x_title => NULL,
x_staff_member_ind => NULL,
x_deceased_ind => NULL,
x_suffix => NULL,
x_pre_name_adjunct => NULL,
x_archive_exclusion_ind => NULL,
x_archive_dt => NULL,
x_purge_exclusion_ind => NULL,
x_purge_dt => NULL,
x_deceased_date => NULL,
x_proof_of_ins => NULL,
x_proof_of_immu => NULL,
x_birth_dt => l_d_date_of_birth,
x_salutation => NULL,
x_oracle_username => NULL,
x_preferred_given_name => NULL,
x_email_addr => NULL,
x_level_of_qual_id => NULL,
x_military_service_reg => NULL,
x_veteran => NULL,
x_hz_parties_ovn => l_n_location_ovn,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL,
x_person_id_type => l_v_person_id_type,
x_api_person_id => igf_ap_matching_process_pkg.format_ssn(l_v_ssn)/*,
x_attribute21 => NULL,
x_attribute22 => NULL,
x_attribute23 => NULL,
x_attribute24 => NULL*/
);
l_d_last_update_date DATE;
l_d_last_update_date := NULL;
igs_pe_person_addr_pkg.insert_row
(
p_action => 'R',
p_rowid => l_v_rowid,
p_location_id => l_n_location_id,
p_start_dt => NULL,
p_end_dt => NULL,
p_country => 'US',
p_address_style => NULL,
p_addr_line_1 => INITCAP( l_v_permt_addr1),
p_addr_line_2 => INITCAP( l_v_permt_addr2),
p_addr_line_3 => NULL,
p_addr_line_4 => NULL,
p_date_last_verified => NULL,
p_correspondence => NULL,
p_city => INITCAP(l_v_permt_city),
p_state => l_v_permt_state,
p_province => NULL,
p_county => NULL,
p_postal_code => l_v_postal_code,
p_address_lines_phonetic => NULL,
p_delivery_point_code => NULL,
p_other_details_1 => NULL,
p_other_details_2 => NULL,
p_other_details_3 => NULL,
l_return_status => l_v_return_status,
l_msg_data => l_v_msg_data,
p_party_id => l_n_person_id,
p_party_site_id => l_n_party_site_id,
p_party_type => NULL,
p_last_update_date => l_d_last_update_date,
p_party_site_ovn => l_n_party_site_ovn,
p_location_ovn => l_n_location_ovn,
p_status => 'A'
);
l_d_last_update_date := NULL;
p_action => 'INSERT',
p_rowid => l_v_rowid,
p_party_site_use_id => l_n_party_site_use_id,
p_party_site_id => l_n_party_site_id,
p_site_use_type => l_v_party_site_use,
p_status => 'A',
p_return_status => l_v_return_status ,
p_msg_data => l_v_msg_data,
p_last_update_date => l_d_last_update_date,
p_site_use_last_update_date => l_d_site_use_last_upd_dt,
p_profile_last_update_date => l_d_profile_last_upd_dt,
p_hz_party_site_use_ovn => l_n_party_site_ovn
);
l_last_update_date DATE ;
l_last_update_date := NULL;
p_action => 'INSERT',
p_rowid => l_rowid,
p_status => 'A',
p_owner_table_name => 'HZ_PARTIES',
p_owner_table_id => p_n_person_id,
p_primary_flag => 'N',
p_phone_country_code => NULL,
p_phone_area_code => l_area_code,
p_phone_number => l_phone_num,
p_phone_extension => NULL,
p_phone_line_type => 'GEN',
p_return_status => l_return_status,
p_msg_data => l_msg_data,
p_last_update_date => l_last_update_date,
p_contact_point_id => l_contact_point_id,
p_contact_point_ovn => l_contact_point_ovn,
p_attribute_category => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute16 => NULL,
p_attribute17 => NULL,
p_attribute18 => NULL,
p_attribute19 => NULL,
p_attribute20 => NULL
);
SELECT relationship_type
FROM hz_relationships
WHERE subject_id = p_subject_id
AND object_id = p_object_id;
l_d_last_update_date DATE;
igs_pe_relationships_pkg.creatupdate_party_relationship(
p_action => 'INSERT',
p_subject_id => p_n_borrower_id,
p_object_id => p_n_student_id,
p_party_relationship_type => 'PARENT/CHILD',
p_relationship_code => 'PARENT_OF',
p_comments => NULL,
p_start_date => SYSDATE,
p_end_date => NULL,
p_last_update_date => l_d_last_update_date,
p_return_status => l_v_return_status,
p_msg_count => l_n_msg_count,
p_msg_data => l_v_msg_data,
p_party_relationship_id => l_n_relationship_id,
p_party_id => l_n_party_id,
p_party_number => l_v_party_number,
p_object_version_number => l_n_location_ovn
);
p_v_string => ' igs_pe_relationships_pkg.creatupdate_party_relationship failed '
);
p_v_string => ' igs_pe_relationships_pkg.creatupdate_party_relationship successful. '
);
SELECT igf_sl_cl_scr_seq.NEXTVAL
FROM DUAL;
l_wf_parameter_list_t.DELETE;
SELECT igf_sl_cl_scr_seq.NEXTVAL -- MN 12-Jan-2005 - As the WorkFlow in use is common between
-- Amt Diff and SCR Diff using same sequence and removing igf_sl_cl_gamt_seq
FROM DUAL;
l_wf_parameter_list_t.DELETE;
PROCEDURE insert_into_resp_r2(p_r2_record IN igf_sl_cl_resp_r2_dtls%ROWTYPE) AS
------------------------------------------------------------------
--Created by : Sanil Madathil, Oracle IDC
--Date created: 07 November 2004
--
-- Purpose:
-- Invoked : from within insert_into_resp1 procedure
-- Function : private procedure which would insert into resp r2 table
--
-- Parameters :
--
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
------------------------------------------------------------------
l_v_rowid ROWID;
log_to_fnd(p_v_module => 'insert_into_resp_r2',
p_v_string => 'invoking igf_sl_cl_resp_r2_dtls_pkg.insert_row for clrp1_id ='||l_n_clrp1_id
);
igf_sl_cl_resp_r2_dtls_pkg.insert_row(
x_rowid => l_v_rowid ,
x_clresp2_id => l_n_clresp2_id ,
x_clrp1_id => l_n_clrp1_id ,
x_record_code_txt => rec_cl_resp_r2.record_code_txt,
x_uniq_layout_vend_code => rec_cl_resp_r2.uniq_layout_vend_code,
x_uniq_layout_ident_code => rec_cl_resp_r2.uniq_layout_ident_code,
x_filler_txt => rec_cl_resp_r2.filler_txt,
x_mode => 'R'
);
END insert_into_resp_r2;
PROCEDURE insert_into_resp_r3(p_r3_record IN igf_sl_cl_resp_r3_dtls%ROWTYPE) AS
------------------------------------------------------------------
--Created by : Sanil Madathil, Oracle IDC
--Date created: 07 November 2004
--
-- Purpose:
-- Invoked : from within insert_into_resp1 procedure
-- Function : private procedure which would insert into resp r3 table
--
-- Parameters :
--
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
------------------------------------------------------------------
l_v_rowid ROWID;
log_to_fnd(p_v_module => 'insert_into_resp_r3',
p_v_string => 'invoking igf_sl_cl_resp_r3_dtls_pkg.insert_row for clrp1_id ='||l_n_clrp1_id
);
igf_sl_cl_resp_r3_dtls_pkg.insert_row(
x_rowid => l_v_rowid,
x_clresp3_id => l_n_clresp3_id,
x_clrp1_id => l_n_clrp1_id,
x_record_code_txt => rec_cl_resp_r3.record_code_txt,
x_message_1_text => rec_cl_resp_r3.message_1_text,
x_message_2_text => rec_cl_resp_r3.message_2_text,
x_message_3_text => rec_cl_resp_r3.message_3_text,
x_message_4_text => rec_cl_resp_r3.message_4_text,
x_message_5_text => rec_cl_resp_r3.message_5_text,
x_mode => 'R'
);
END insert_into_resp_r3;
PROCEDURE insert_into_resp_r7(p_r7_record IN igf_sl_cl_resp_r7_dtls%ROWTYPE) AS
------------------------------------------------------------------
--Created by : Sanil Madathil, Oracle IDC
--Date created: 07 November 2004
--
-- Purpose:
-- Invoked : from within insert_into_resp1 procedure
-- Function : private procedure which would insert into resp r7 table
--
-- Parameters :
--
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
------------------------------------------------------------------
l_v_rowid ROWID;
log_to_fnd(p_v_module => 'insert_into_resp_r7',
p_v_string => 'invoking igf_sl_cl_resp_r3_dtls_pkg.insert_row for clrp1_id ='||l_n_clrp1_id
);
igf_sl_cl_resp_r7_dtls_pkg.insert_row(
x_rowid => l_v_rowid,
x_clresp7_id => l_n_clresp7_id,
x_clrp1_id => l_n_clrp1_id,
x_record_code_txt => rec_cl_resp_r7.record_code_txt,
x_layout_owner_code_txt => rec_cl_resp_r7.layout_owner_code_txt,
x_layout_identifier_code_txt => rec_cl_resp_r7.layout_identifier_code_txt,
x_email_txt => rec_cl_resp_r7.email_txt,
x_valid_email_flag => rec_cl_resp_r7.valid_email_flag,
x_email_effective_date => rec_cl_resp_r7.email_effective_date,
x_borrower_temp_add_line_1_txt => rec_cl_resp_r7.borrower_temp_add_line_1_txt,
x_borrower_temp_add_line_2_txt => rec_cl_resp_r7.borrower_temp_add_line_2_txt,
x_borrower_temp_add_city_txt => rec_cl_resp_r7.borrower_temp_add_city_txt,
x_borrower_temp_add_state_txt => rec_cl_resp_r7.borrower_temp_add_state_txt,
x_borrower_temp_add_zip_num => rec_cl_resp_r7.borrower_temp_add_zip_num,
x_borr_temp_add_zip_xtn_num => rec_cl_resp_r7.borrower_temp_add_zip_xtn_num,
x_borr_forgn_postal_code_txt => rec_cl_resp_r7.borrower_forgn_postal_code_txt,
x_mode => 'R'
);
END insert_into_resp_r7;
PROCEDURE insert_into_resp_r6(p_r6_record IN igf_sl_clchrs_dtls%ROWTYPE )AS
------------------------------------------------------------------
--Created by : Sanil Madathil, Oracle IDC
--Date created: 07 November 2004
--
-- Purpose:
-- Invoked : from within insert_into_resp1 procedure
-- Function : private procedure which would insert into igf_sl_clchrs_dtls table
--
-- Parameters :
--
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
------------------------------------------------------------------
l_v_rowid ROWID;
log_to_fnd(p_v_module => 'insert_into_resp_r6',
p_v_string => 'invoking igf_sl_clchrs_dtls_pkg.insert_row for clrp1_id ='||l_n_clrp1_id
);
igf_sl_clchrs_dtls_pkg.insert_row (
x_rowid => l_v_rowid,
x_clchgrsp_id => l_n_clchgrsp_id,
x_clrp1_id => l_n_clrp1_id,
x_record_code => rec_cl_resp_r6.record_code,
x_send_record_txt => rec_cl_resp_r6.send_record_txt ,
x_error_message_1_code => rec_cl_resp_r6.error_message_1_code ,
x_error_message_2_code => rec_cl_resp_r6.error_message_2_code ,
x_error_message_3_code => rec_cl_resp_r6.error_message_3_code ,
x_error_message_4_code => rec_cl_resp_r6.error_message_4_code ,
x_error_message_5_code => rec_cl_resp_r6.error_message_5_code ,
x_record_process_code => rec_cl_resp_r6.record_process_code,
x_mode => 'R'
);
END insert_into_resp_r6;
SELECT chdt.*, chdt.ROWID row_id
FROM igf_sl_clchsn_dtls chdt
WHERE loan_number_txt = cp_v_loan_number
AND chdt.status_code = 'S';
SELECT chrsdtls.*
FROM igf_sl_clchrs_dtls chrsdtls
WHERE chrsdtls.clrp1_id = cp_n_clrp1_id
AND send_record_txt = TRIM(cp_v_send_record_txt);
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 => l_v_status_code ,
x_status_date => rec_c_sl_clchsn_dtls.status_date ,
x_response_status_code => l_v_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 => rec_c_sl_clchsn_dtls.send_record_txt ,
x_mode => 'R'
);