[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
ROWID,
TRUNC(clint.loan_per_begin_date) loan_per_begin_date,
TRUNC(clint.loan_per_end_date) loan_per_end_date,
TRUNC(clint.loan_status_date) loan_status_date,
TRUNC(clint.active_date) active_date,
TRUNC(clint.anticip_compl_date) anticip_compl_date,
TRUNC(clint.b_permt_addr_chg_date) b_permt_addr_chg_date,
TRUNC(clint.b_signature_date) b_signature_date,
TRUNC(clint.credit_status_date) credit_status_date,
TRUNC(clint.guarantee_date) guarantee_date,
TRUNC(clint.guarnt_status_date) guarnt_status_date,
TRUNC(clint.lend_status_date) lend_status_date,
TRUNC(clint.lend_blkt_guarnt_appr_date) lend_blkt_guarnt_appr_date,
TRUNC(clint.orig_ack_date) orig_ack_date,
TRUNC(clint.orig_batch_date) orig_batch_date,
TRUNC(clint.pnote_status_date) pnote_status_date,
TRUNC(clint.sch_cert_date) sch_cert_date,
TRUNC(clint.sch_refund_date) sch_refund_date,
TRUNC(clint.file_creation_date) file_creation_date,
TRUNC(clint.file_trans_date) file_trans_date,
clint.batch_num,
clint.loan_seq_num,
clint.act_interest_rate_num,
clint.alt_appl_ver_code_num,
clint.alt_borw_tot_stu_loan_debt_amt,
clint.borw_gross_annual_sal_amt,
clint.borw_other_income_amt,
clint.cl_seq_num,
clint.coa_amt,
clint.efc_amt,
clint.est_fa_amt,
clint.fed_sls_debt_amt,
clint.fed_stafford_loan_debt_amt,
clint.flp_approved_amt,
clint.flp_cert_amt,
clint.fls_approved_amt,
clint.fls_cert_amt,
clint.flu_approved_amt,
clint.flu_cert_amt,
clint.guarantee_amt,
clint.heal_debt_amt,
clint.other_debt_amt,
clint.perkins_debt_amt,
clint.req_loan_amt,
clint.sch_refund_amt,
clint.stud_mth_auto_pymt_amt,
clint.stud_mth_crdtcard_pymt_amt,
clint.stud_mth_ed_loan_pymt_amt,
clint.stud_mth_housing_pymt_amt,
clint.stud_mth_other_pymt_amt,
clint.tot_outstd_plus_amt,
clint.tot_outstd_stafford_amt,
clint.alt_cert_amt,
clint.alt_approved_amt,
clint.reinst_avail_amt,
TRIM(clint.ci_alternate_code) ci_alternate_code,
TRIM(clint.person_number) person_number,
TRIM(clint.award_number_txt) award_number_txt,
TRIM(clint.loan_number_txt) loan_number_txt,
TRIM(clint.import_status_type) import_status_type,
TRIM(clint.loan_status_code) loan_status_code,
TRIM(clint.active_flag) active_flag,
TRIM(clint.act_serial_loan_code) act_serial_loan_code,
TRIM(clint.alt_prog_type_cd) alt_prog_type_cd,
TRIM(clint.borr_person_number) borr_person_number,
TRIM(clint.b_default_status_flag) b_default_status_flag,
TRIM(clint.b_foreign_postal_cd) b_foreign_postal_cd,
TRIM(clint.b_stu_indicator_flag) b_stu_indicator_flag,
TRIM(clint.b_reference_flag) b_reference_flag,
TRIM(clint.b_signature_flag) b_signature_flag,
TRIM(clint.borr_credit_auth_flag) borr_credit_auth_flag,
TRIM(clint.borr_sign_flag) borr_sign_flag,
TRIM(clint.borw_confirm_flag) borw_confirm_flag,
TRIM(clint.borw_interest_flag) borw_interest_flag,
TRIM(clint.borw_outstd_loan_flag) borw_outstd_loan_flag,
TRIM(clint.crdt_undr_difft_name_flag) crdt_undr_difft_name_flag,
TRIM(clint.credit_status_code) credit_status_code,
TRIM(clint.eft_auth_flag) eft_auth_flag,
TRIM(clint.enrollment_code) enrollment_code,
TRIM(clint.err_mesg_1_cd) err_mesg_1_cd,
TRIM(clint.err_mesg_2_cd) err_mesg_2_cd,
TRIM(clint.err_mesg_3_cd) err_mesg_3_cd,
TRIM(clint.err_mesg_4_cd) err_mesg_4_cd,
TRIM(clint.err_mesg_5_cd) err_mesg_5_cd,
TRIM(clint.fed_appl_form_type) fed_appl_form_type,
TRIM(clint.grade_level_code) grade_level_code,
TRIM(clint.guarnt_adj_flag) guarnt_adj_flag,
TRIM(clint.guarnt_amt_redn_code) guarnt_amt_redn_code,
TRIM(clint.guarnt_status_code) guarnt_status_code,
TRIM(clint.int_rate_opt_code) int_rate_opt_code,
TRIM(clint.last_resort_lender_flag) last_resort_lender_flag,
TRIM(clint.lend_status_code) lend_status_code,
TRIM(clint.lend_blkt_guarnt_flag) lend_blkt_guarnt_flag,
TRIM(clint.orig_ack_batch_id_txt) orig_ack_batch_id_txt,
TRIM(clint.orig_send_batch_id_txt) orig_send_batch_id_txt,
TRIM(clint.pnote_delivery_code) pnote_delivery_code,
TRIM(clint.pnote_status_code) pnote_status_code,
TRIM(clint.prc_type_code) prc_type_code,
TRIM(clint.record_code) record_code,
TRIM(clint.repayment_opt_code) repayment_opt_code,
TRIM(clint.req_serial_loan_code) req_serial_loan_code,
TRIM(clint.resp_to_orig_flag) resp_to_orig_flag,
TRIM(clint.rev_notice_of_guarnt_code) rev_notice_of_guarnt_code,
TRIM(clint.s_default_status_flag) s_default_status_flag,
TRIM(clint.s_signature_flag) s_signature_flag,
TRIM(clint.sch_non_ed_brc_id_txt) sch_non_ed_brc_id_txt,
TRIM(clint.service_type_code) service_type_code,
TRIM(clint.stud_sign_flag) stud_sign_flag,
TRIM(clint.student_major_txt) student_major_txt,
TRIM(clint.uniq_layout_ident_code) uniq_layout_ident_code,
TRIM(clint.uniq_layout_vend_code) uniq_layout_vend_code,
TRIM(clint.orig_batch_id_txt) orig_batch_id_txt,
TRIM(clint.defer_req_flag) defer_req_flag,
TRIM(clint.b_license_state_code) b_license_state_code,
TRIM(clint.b_license_number_txt) b_license_number_txt,
TRIM(clint.send_resp_code) send_resp_code,
TRIM(clint.source_id_txt) source_id_txt,
TRIM(clint.source_non_ed_brc_id_txt) source_non_ed_brc_id_txt,
TRIM(clint.import_record_type) import_record_type,
TRIM(clint.relationship_cd) relationship_cd, -- FA 122 Loans Enhancements,
TRIM(clint.actual_record_type_code) actual_record_type_code,
TRIM(clint.lend_apprv_denied_code) lend_apprv_denied_code,
TRIM(clint.lend_apprv_denied_date) lend_apprv_denied_date,
TRIM(clint.cl_rec_status) cl_rec_status,
TRIM(clint.appl_loan_phase_code) appl_loan_phase_code,
TRIM(clint.mpn_confirm_code) mpn_confirm_code,
TRIM(clint.appl_loan_phase_code_chg) appl_loan_phase_code_chg,
TRIM(clint.external_loan_id_txt) external_loan_id_txt,
TRUNC(clint.other_loan_amt) other_loan_amt,
TRIM(clint.guarantor_use_txt) guarantor_use_txt,
TRIM(clint.lender_use_txt) lender_use_txt,
TRIM(clint.school_use_txt) school_use_txt,
TRIM(cl_rec_status_last_update) cl_rec_status_last_update,
TRIM(clint.cs1_lname) cs1_lname,
TRIM(clint.cs1_fname) cs1_fname,
TRIM(clint.cs1_mi_txt) cs1_mi_txt,
TRIM(clint.cs1_ssn_txt) cs1_ssn_txt,
TRIM(clint.cs1_citizenship_status) cs1_citizenship_status,
TRIM(clint.cs1_address_line_1_txt) cs1_address_line_1_txt,
TRIM(clint.cs1_address_line_2_txt) cs1_address_line_2_txt,
TRIM(clint.cs1_city_txt) cs1_city_txt,
TRIM(clint.cs1_state_txt) cs1_state_txt,
TRIM(clint.cs1_zip_txt) cs1_zip_txt,
TRIM(clint.cs1_zip_suffix_txt) cs1_zip_suffix_txt,
TRIM(clint.cs1_telephone_number_txt) cs1_telephone_number_txt,
TRIM(clint.cs1_signature_code_txt) cs1_signature_code_txt,
TRIM(clint.cs2_lname) cs2_lname,
TRIM(clint.cs2_fname) cs2_fname,
TRIM(clint.cs2_mi_txt) cs2_mi_txt,
TRIM(clint.cs2_ssn_txt) cs2_ssn_txt,
TRIM(clint.cs2_citizenship_status) cs2_citizenship_status,
TRIM(clint.cs2_address_line_1_txt) cs2_address_line_1_txt,
TRIM(clint.cs2_address_line_2_txt) cs2_address_line_2_txt,
TRIM(clint.cs2_city_txt) cs2_city_txt,
TRIM(clint.cs2_state_txt) cs2_state_txt,
TRIM(clint.cs2_zip_txt) cs2_zip_txt,
TRIM(clint.cs2_zip_suffix_txt) cs2_zip_suffix_txt,
TRIM(clint.cs2_telephone_number_txt) cs2_telephone_number_txt,
TRIM(clint.cs2_signature_code_txt) cs2_signature_code_txt,
TRIM(clint.cs1_credit_auth_code_txt) cs1_credit_auth_code_txt,
TRUNC(clint.cs1_birth_date) cs1_birth_date,
TRIM(clint.cs1_drv_license_num_txt) cs1_drv_license_num_txt,
TRIM(clint.cs1_drv_license_state_txt) cs1_drv_license_state_txt,
SUBSTR(clint.cs1_elect_sig_ind_code_txt, 1, 1) cs1_elect_sig_ind_code_txt, -- Since the elect sig can come in as " " for N, treating it the same way
TRIM(clint.cs1_frgn_postal_code_txt) cs1_frgn_postal_code_txt,
TRIM(clint.cs1_frgn_tel_num_prefix_txt) cs1_frgn_tel_num_prefix_txt,
TRUNC(clint.cs1_gross_annual_sal_num) cs1_gross_annual_sal_num,
TRIM(clint.cs1_mthl_auto_pay_txt) cs1_mthl_auto_pay_txt,
TRIM(clint.cs1_mthl_cc_pay_txt) cs1_mthl_cc_pay_txt,
TRIM(clint.cs1_mthl_edu_loan_pay_txt) cs1_mthl_edu_loan_pay_txt,
TRIM(clint.cs1_mthl_housing_pay_txt) cs1_mthl_housing_pay_txt,
TRIM(clint.cs1_mthl_other_pay_txt) cs1_mthl_other_pay_txt,
TRUNC(clint.cs1_other_income_amt) cs1_other_income_amt,
TRIM(clint.cs1_rel_to_student_flag) cs1_rel_to_student_flag,
TRIM(clint.cs1_suffix_txt) cs1_suffix_txt,
TRUNC(clint.cs1_years_at_address_txt) cs1_years_at_address_txt,
TRIM(clint.cs2_credit_auth_code_txt) cs2_credit_auth_code_txt,
TRUNC(clint.cs2_birth_date) cs2_birth_date,
TRIM(clint.cs2_drv_license_num_txt) cs2_drv_license_num_txt,
TRIM(clint.cs2_drv_license_state_txt) cs2_drv_license_state_txt,
SUBSTR(clint.cs2_elect_sig_ind_code_txt, 1, 1) cs2_elect_sig_ind_code_txt, -- Since the elect sig can come in as " " for N, treating it the same way
TRIM(clint.cs2_frgn_postal_code_txt) cs2_frgn_postal_code_txt,
TRIM(clint.cs2_frgn_tel_num_prefix_txt) cs2_frgn_tel_num_prefix_txt,
TRUNC(clint.cs2_gross_annual_sal_num) cs2_gross_annual_sal_num,
TRIM(clint.cs2_mthl_auto_pay_txt) cs2_mthl_auto_pay_txt,
TRIM(clint.cs2_mthl_cc_pay_txt) cs2_mthl_cc_pay_txt,
TRIM(clint.cs2_mthl_edu_loan_pay_txt) cs2_mthl_edu_loan_pay_txt,
TRIM(clint.cs2_mthl_housing_pay_txt) cs2_mthl_housing_pay_txt,
TRIM(clint.cs2_mthl_other_pay_txt) cs2_mthl_other_pay_txt,
TRUNC(clint.cs2_other_income_amt) cs2_other_income_amt,
TRIM(clint.cs2_rel_to_student_flag) cs2_rel_to_student_flag,
TRIM(clint.cs2_suffix_txt) cs2_suffix_txt,
TRUNC(clint.cs2_years_at_address_txt) cs2_years_at_address_txt,
TRIM(clint.esign_src_typ_cd) esign_src_typ_cd -- FA 161 - CL 4
FROM
igf_sl_li_orig_ints clint
WHERE
clint.batch_num = cp_batch_id AND
clint.ci_alternate_code = cp_alternate_code AND
(clint.import_status_type = p_import_status_type_1 OR clint.import_status_type = p_import_status_type_2)
ORDER BY clint.person_number;
SELECT
TRUNC(dlint.disbursement_date) disbursement_date,
TRUNC(dlint.fund_release_date) fund_release_date,
TRUNC(dlint.guarantee_date) guarantee_date,
TRUNC(dlint.pnote_status_date) pnote_status_date,
TRUNC(dlint.disbursement_status_date) disbursement_status_date,
TRUNC(dlint.fund_status_date) fund_status_date,
TRUNC(dlint.file_creation_date) file_creation_date,
TRUNC(dlint.file_trans_date) file_trans_date,
dlint.disbursement_num,
dlint.sch_disbursement_num,
dlint.guarantee_amt,
dlint.gross_disbursement_amt,
dlint.origination_fee_amt,
dlint.guarantee_fee_amt,
dlint.guarantee_fees_paid_amt,
dlint.net_cancel_amt,
dlint.origination_fees_paid_amt,
dlint.netted_cancel_amt,
dlint.outstd_cancel_amt,
TRIM(dlint.ci_alternate_code) ci_alternate_code,
TRIM(dlint.person_number) person_number,
TRIM(dlint.award_number_txt) award_number_txt,
TRIM(dlint.loan_number_txt) loan_number_txt,
TRIM(dlint.record_type) record_type,
TRIM(dlint.school_use_txt) school_use_txt,
TRIM(dlint.lender_use_txt) lender_use_txt,
TRIM(dlint.guarantor_use_txt) guarantor_use_txt,
TRIM(dlint.fund_dist_mthd_type) fund_dist_mthd_type,
TRIM(dlint.check_number_txt) check_number_txt,
TRIM(dlint.late_disbursement_flag) late_disbursement_flag,
TRIM(dlint.prev_reported_flag) prev_reported_flag,
TRIM(dlint.err_mesg_1_cd) err_mesg_1_cd,
TRIM(dlint.err_mesg_2_cd) err_mesg_2_cd,
TRIM(dlint.err_mesg_3_cd) err_mesg_3_cd,
TRIM(dlint.err_mesg_4_cd) err_mesg_4_cd,
TRIM(dlint.err_mesg_5_cd) err_mesg_5_cd,
TRIM(dlint.disbursement_hld_release_flag) disbursement_hld_release_flag,
TRIM(dlint.pnote_code) pnote_code,
TRIM(dlint.disbursement_status_code) disbursement_status_code,
TRIM(dlint.fund_status_code) fund_status_code,
TRIM(dlint.lender_name) lender_name,
TRIM(dlint.roster_batch_id_txt) roster_batch_id,
TRIM(dlint.recipient_id_txt) recipient_id_txt,
TRIM(dlint.recipient_non_ed_brc_id_txt) recipient_non_ed_brc_id_txt,
TRIM(dlint.source_id_txt) source_id_txt,
TRIM(dlint.source_non_ed_brc_id_txt) source_non_ed_brc_id_txt,
TRIM(dlint.send_resp_code) send_resp_code,
TRIM(dlint.direct_to_borr_flag) direct_to_borr_flag
FROM
igf_sl_li_org_disb_ints dlint
WHERE
dlint.ci_alternate_code = cp_alternate_code AND
dlint.person_number = cp_person_number AND
dlint.award_number_txt = cp_award_number_txt AND
dlint.loan_number_txt = cp_loan_number;
SELECT awd.award_id,
awd.offered_amt,
awd.accepted_amt,
fcat.fed_fund_code,
fcat.fund_code
FROM igf_aw_award_all awd,
igf_aw_fund_mast_all fmast,
igf_aw_fund_cat_all fcat
WHERE awd.base_id = cp_base_id AND
awd.award_number_txt = cp_award_number AND
awd.fund_id = fmast.fund_id AND
fmast.fund_code = fcat.fund_code;
SELECT meaning, lookup_code
FROM igf_lookups_view
WHERE lookup_type = 'IGF_GE_PARAMETERS'
AND lookup_code IN ('AWARD_YEAR',
'BATCH_NUMBER',
'DELETE_FLAG',
'PARAMETER_PASS',
'PROCESSING',
'LOAN_DISB',
'LOAN_RECORD',
'AWARD_YR_STATUS',
'PERSON_NUMBER');
ELSIF parameter_rec.lookup_code ='DELETE_FLAG' THEN
g_del_flag := TRIM(parameter_rec.meaning);
SELECT 1
FROM hz_parties hz,
igs_or_org_alt_ids oli,
igs_or_org_alt_idtyp olt
WHERE oli.org_structure_id = hz.party_number
AND oli.org_alternate_id_type = olt.org_alternate_id_type
AND SYSDATE BETWEEN oli.start_date AND NVL(oli.end_date, SYSDATE)
AND hz.status = 'A'
AND oli.org_alternate_id = cp_source_or_branch_id
AND system_id_type = cp_source_type;
SELECT cl_version
FROM igf_sl_cl_setup_all
WHERE ci_cal_type = p_cal_type
AND ci_sequence_number = p_seq_num
AND relationship_cd= p_rel_code;
SELECT
count(*)
FROM
igf_sl_li_org_disb_ints
WHERE
ci_alternate_code = cp_alternate_code AND
person_number = cp_person_number AND
award_number_txt = cp_award_number_txt AND
loan_number_txt = cp_loan_number;
SELECT
1
FROM
igf_sl_lender
WHERE
lender_id = cp_lender_id;
SELECT
1
FROM
igf_sl_guarantor
WHERE
guarantor_id = cp_guarnt_id;
SELECT 'X'
FROM hz_relationships pr,
igs_pe_hz_parties pe,
hz_parties br,
hz_parties st
WHERE
br.party_number = cp_b_person_number
AND st.party_number = cp_person_number
AND pr.subject_id = st.party_id
AND pr.object_id = br.party_id
AND st.party_id = pe.party_id;
SELECT '1'
FROM
igf_ap_class_std_map
WHERE
cl_std_code = p_grd_lvl AND
ppt_id IN
(
SELECT ppt_id
FROM igf_ap_pr_prg_type
WHERE sequence_number = p_seq_number AND
cal_type = p_cal_type
);
SELECT '1'
FROM
igf_ap_attend_map_v
WHERE
cl_att_code = p_enrl_code AND
sequence_number = p_seq_number AND
cal_type = p_cal_type;
SELECT relationship_cd
FROM
igf_sl_cl_setup
WHERE
ci_cal_type = p_cal_type AND
ci_sequence_number = p_seq_number AND
NVL(relationship_cd,'*') = p_rel_code;
SELECT alternate_code
FROM igs_ca_inst
WHERE cal_type = cp_cal_type
AND sequence_number = cp_seq_number;
SELECT 1
FROM hz_parties hz,
igs_or_org_alt_ids oli,
igs_or_org_alt_idtyp olt
WHERE oli.org_structure_id = hz.party_number
AND oli.org_alternate_id_type = olt.org_alternate_id_type
AND SYSDATE BETWEEN oli.start_date AND NVL(oli.end_date, SYSDATE)
AND hz.status = 'A'
AND oli.org_alternate_id = cp_sch_non_ed_brc_id
AND system_id_type = cp_source_type;
SELECT fund_code
FROM igf_aw_fund_cat_all
WHERE NVL(alt_loan_code,'*') = cp_alt_loan_code
AND NVL(alt_rel_code,'*') = cp_alt_rel_code
AND fund_code = cp_fund_code;
SELECT lookup_code
FROM igf_aw_lookups_view
WHERE
lookup_type = 'IGF_SL_ALT_CS_US_CT_ST_CODE'
AND cal_type = p_cal_type
AND sequence_number = p_seq_number
AND enabled_flag = 'Y'
AND lookup_code = cp_lookup_code;
SELECT lookup_code
FROM igf_aw_lookups_view
WHERE
lookup_type = 'IGF_AP_STATE_CODES'
AND cal_type = p_cal_type
AND sequence_number = p_seq_number
AND enabled_flag = 'Y'
AND lookup_code NOT IN ('BL', 'CN', 'MX', 'OT')
AND lookup_code = cp_lookup_code;
SELECT lookup_code
FROM igf_aw_lookups_view
WHERE
lookup_type = 'YES_NO'
AND cal_type = p_cal_type
AND sequence_number = p_seq_number
AND enabled_flag = 'Y'
AND lookup_code = cp_lookup_code;
SELECT lookup_code
FROM igf_aw_lookups_view
WHERE
lookup_type = 'IGF_SL_ALT_CS_STUDENT_RELATION'
AND cal_type = p_cal_type
AND sequence_number = p_seq_number
AND enabled_flag = 'Y'
AND lookup_code = cp_lookup_code;
SELECT disb_accepted_amt disb_gross_amt
FROM igf_aw_awd_disb_all
WHERE award_id = cp_award_id
AND disb_num = cp_disb_num;
g_igf_sl_msg_table.DELETE;
PROCEDURE delete_records ( p_rowid ROWID,
p_loan_id NUMBER,
p_loan_number VARCHAR2)
IS
l_award_id igf_aw_award_all.award_id%TYPE;
SELECT
rowid,
origination_id
FROM igf_sl_lor_all
WHERE loan_id = cp_loan_id;
SELECT rowid
FROM igf_sl_lor_loc_all
WHERE origination_id = cp_orig_id;
SELECT rowid
FROM igf_sl_alt_borw_all
WHERE loan_id = cp_loan_id;
SELECT rowid
FROM igf_sl_awd_disb_loc_all
WHERE award_id = cp_award_id;
SELECT rowid,clrp1_id
FROM igf_sl_cl_resp_r1_all
WHERE loan_number = cp_loan_number;
SELECT rowid
FROM igf_sl_cl_resp_r4_all
WHERE clrp1_id = cp_rp1_id;
SELECT rowid
FROM igf_sl_cl_resp_r8_all
WHERE clrp1_id = cp_rp1_id;
SELECT rowid
FROM igf_db_cl_disb_resp_all
WHERE loan_number = cp_loan_number;
SELECT
rowid
FROM igf_sl_pnote_stat_h
WHERE loan_id = cp_loan_id;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.delete_record.debug','DELETE_RECORDS: Before c_pnote_hist OPEN');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.delete_records.debug','DELETE_RECORDS: c_pnote_hist ROWID ' || l_pnote_hist.rowid);
igf_sl_pnote_stat_h_pkg.delete_row(l_pnote_hist.rowid);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.delete_record.debug','l_lor_rowid.rowid:'||l_lor_rowid);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.delete_record.debug','l_loc_rowid.rowid:'||l_loc_rowid);
igf_sl_lor_loc_pkg.delete_row(l_loc_rowid);
igf_sl_lor_pkg.delete_row(l_lor_rowid);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.delete_record.debug','l_lor_rowid.rowid:'||l_lor_rowid);
igf_sl_lor_pkg.delete_row(l_lor_rowid);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_li_imp_pkg.delete_records.debug','l_alt_borw ROWID '|| l_alt_borw.rowid);
igf_sl_alt_borw_pkg.delete_row(x_ROWID => l_alt_borw.rowid);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_li_imp_pkg.delete_records.debug','l_disb_loc ROWID ' || l_disb_loc.rowid);
igf_sl_awd_disb_loc_pkg.delete_row(x_ROWID => l_disb_loc.rowid);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.delete_record.debug','l_resp_r4 ROWID ' || l_resp_r4.rowid);
igf_sl_cl_resp_r4_pkg.delete_row(l_resp_r4.rowid);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.delete_record.debug','l_resp_r8 ROWID ' || l_resp_r8.rowid);
igf_sl_cl_resp_r8_pkg.delete_row(x_ROWID => l_resp_r8.rowid);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.delete_record.debug','l_resp_r1 ROWID ' || l_resp_r1.rowid);
igf_sl_cl_resp_r1_pkg.delete_row(x_ROWID => l_resp_r1_rowid);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.delete_record.debug','l_disb_resp ROWID ' || l_disb_resp.rowid);
igf_db_cl_disb_resp_pkg.delete_row(x_ROWID => l_disb_resp.rowid);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.delete_record.debug','igf_sl_loans ROWID ' || p_rowid);
igf_sl_loans_pkg.delete_row(p_rowid);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.delete_record.debug','Deletion Complete');
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_cl_imp_pkg.delete_records.exception','Exception ' || SQLERRM);
fnd_message.set_token('NAME','IGF_SL_CL_LI_IMP_PKG.DELETE_RECORDS');
END delete_records;
PROCEDURE insert_records(p_interface IN c_interface%ROWTYPE,
p_award_id IN NUMBER,
p_fed_fund IN VARCHAR2,
p_student_person_id IN NUMBER)
AS
--
-- Created By : gmuralid
-- Created On : 24-JUN-2003
-- Purpose : This procedure is used to perform the dml operation on every table
-- Known limitations, enhancements or remarks :
-- Change History :
-- Who When What
-- (reverse chronological order - newest change first)
-- bkkumar 07-oct-2003 Bug 3104228 FA 122 Loans Enhancements
-- a) Impact of obsoleting GUARANTOR_ID_TXT,
-- LENDER_ID_TXT,LEND_NON_ED_BRC_ID_TXT,RECIPIENT_ID_TXT,
-- RECIPIENT_TYPE,RECIPIENT_NON_ED_BRC_ID_TXT from the
-- interface table and also adding a new column relationship_cd
-- b) 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.
-- c) 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.
-- veramach 23-SEP-2003 Bug 3104228:
-- 1. Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
-- cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
-- p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
-- chg_batch_id,appl_send_error_codes from igf_sl_lor
-- 2. Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
-- cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
-- p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
-- chg_batch_id from igf_sl_lor_loc
-- veramach 16-SEP-2003 FA 122 loan enhancements
-- Changed c_loan_dtls cursor.it does not select borrower information.igf_sl_gen.get_person_details is used for this
ln_rowid ROWID;
SELECT base_id,
ci_cal_type,
ci_sequence_number
FROM igf_aw_award_v
WHERE award_id = cp_award_id;
SELECT est_orig_fee_perct
FROM igf_sl_cl_setup
WHERE ci_cal_type = cp_cal_type
AND ci_sequence_number = cp_sequence_number
AND cp_rel_code = NVL(relationship_cd,'*');
SELECT loans.row_id,
loans.loan_id,
lor.s_default_status,
lor.p_default_status,
lor.p_person_id,
lor.recipient_id,
lor.lender_id,
lor.guarantor_id,
lor.lend_non_ed_brc_id,
lor.recip_non_ed_brc_id,
lor.recipient_type,
fabase.person_id student_id
FROM igf_sl_loans loans,
igf_sl_lor_v lor,
igf_aw_award awd,
igf_ap_fa_base_rec fabase
WHERE fabase.base_id = awd.base_id
AND loans.award_id = awd.award_id
AND loans.loan_id = lor.loan_id
AND loans.loan_id = p_loan_id;
SELECT isir.dependency_status
FROM igf_ap_fa_base_rec fabase,
igf_ap_isir_matched isir
WHERE isir.payment_isir = 'Y'
AND isir.system_record_type = 'ORIGINAL'
AND isir.base_id = fabase.base_id
AND fabase.person_id = cp_person_id;
SELECT
ROWID,
adisb.*
FROM
igf_aw_awd_disb_all adisb
WHERE
adisb.award_id = p_award_id AND
adisb.disb_num = cp_disb_num;
SELECT cbth_id
FROM igf_sl_cl_batch_all
WHERE batch_id = cp_orig_ack_batch_id_txt;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_record.debug','Before insert into loans IGF_SL_LOANS_ALL');
igf_sl_loans_pkg.insert_row (
x_mode => 'R',
x_rowid => ln_rowid,
x_loan_id => ln_loan_id,
x_award_id => p_award_id,
x_seq_num => p_interface.loan_seq_num,
x_loan_number => p_interface.loan_number_txt,
x_loan_per_begin_date => p_interface.loan_per_begin_date,
x_loan_per_end_date => p_interface.loan_per_end_date,
x_loan_status => p_interface.loan_status_code,
x_loan_status_date => p_interface.loan_status_date,
x_loan_chg_status => NULL,
x_loan_chg_status_date => NULL,
x_active => p_interface.active_flag,
x_active_date => p_interface.active_date,
x_borw_detrm_code => NULL,
x_legacy_record_flag => 'Y',
x_external_loan_id_txt => p_interface.external_loan_id_txt
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_records.debug','After Insert into IGF_SL_LOANS LOAN_ID ' || ln_loan_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_records.debug','Before insert into IGF_SL_LOR');
igf_sl_lor_pkg.insert_row (
x_mode => 'R',
x_rowid => lor_rowid,
x_origination_id => ln_origination_id,
x_loan_id => ln_loan_id,
x_sch_cert_date => p_interface.sch_cert_date,
x_orig_status_flag => NULL,--This is applicable for Direct Loans
x_orig_batch_id => p_interface.orig_batch_id_txt,
x_orig_batch_date => p_interface.orig_batch_date,
x_chg_batch_id => NULL,--This is applicable for Direct Loans
x_orig_ack_date => p_interface.orig_ack_date,
x_credit_override => NULL, -- FA 149 Credit status override changed to crdt_decision_status
x_credit_decision_date => p_interface.credit_status_date,
x_req_serial_loan_code => p_interface.req_serial_loan_code,
x_act_serial_loan_code => p_interface.act_serial_loan_code,
x_pnote_delivery_code => p_interface.pnote_delivery_code,
x_pnote_status => p_interface.pnote_status_code,
x_pnote_status_date => p_interface.pnote_status_date,
x_pnote_id => NULL,--This is applicable for Direct Loans
x_pnote_print_ind => NULL,--This is applicable for Direct Loans
x_pnote_accept_amt => NULL,--This is applicable for Direct Loans
x_pnote_accept_date => NULL,--This is applicable for Direct Loans
x_unsub_elig_for_heal => NULL,--This is applicable for Direct Loans
x_disclosure_print_ind => NULL,--This is applicable for Direct Loans
x_orig_fee_perct => l_setup.est_orig_fee_perct,
x_borw_confirm_ind => p_interface.borw_confirm_flag,
x_borw_interest_ind => p_interface.borw_interest_flag,
x_borw_outstd_loan_code => p_interface.borw_outstd_loan_flag,
x_unsub_elig_for_depnt => NULL,--This is applicable for Direct Loans
x_guarantee_amt => p_interface.guarantee_amt,
x_guarantee_date => p_interface.guarantee_date,
x_guarnt_amt_redn_code => p_interface.guarnt_amt_redn_code,
x_guarnt_status_code => p_interface.guarnt_status_code,
x_guarnt_status_date => p_interface.guarnt_status_date,
x_lend_apprv_denied_code => p_interface.lend_apprv_denied_code,
x_lend_apprv_denied_date => p_interface.lend_apprv_denied_date,
x_lend_status_code => p_interface.lend_status_code,
x_lend_status_date => p_interface.lend_status_date,
x_guarnt_adj_ind => p_interface.guarnt_adj_flag,
x_grade_level_code => p_interface.grade_level_code,
x_enrollment_code => p_interface.enrollment_code,
x_anticip_compl_date => p_interface.anticip_compl_date,
x_borw_lender_id => NULL,
x_duns_borw_lender_id => NULL,
x_guarantor_id => NULL,
x_duns_guarnt_id => NULL,
x_prc_type_code => p_interface.prc_type_code,
x_cl_seq_number => p_interface.cl_seq_num,
x_last_resort_lender => p_interface.last_resort_lender_flag,
x_lender_id => NULL,
x_duns_lender_id => NULL,
x_lend_non_ed_brc_id => NULL,
x_recipient_id => NULL,
x_recipient_type => NULL,
x_duns_recip_id => NULL,
x_recip_non_ed_brc_id => NULL,
x_rec_type_ind => p_interface.record_code,
x_cl_loan_type => lv_cl_loan_type,
x_cl_rec_status => p_interface.cl_rec_status,
x_cl_rec_status_last_update => p_interface.cl_rec_status_last_update,
x_alt_prog_type_code => p_interface.alt_prog_type_cd,
x_alt_appl_ver_code => p_interface.alt_appl_ver_code_num,
x_mpn_confirm_code => p_interface.mpn_confirm_code,-- May be Obsolete field
x_resp_to_orig_code => p_interface.resp_to_orig_flag,
x_appl_loan_phase_code => p_interface.appl_loan_phase_code,
x_appl_loan_phase_code_chg => p_interface.appl_loan_phase_code_chg,
x_appl_send_error_codes => NULL,-- May be Obsolete field
x_tot_outstd_stafford => p_interface.tot_outstd_stafford_amt,
x_tot_outstd_plus => p_interface.tot_outstd_plus_amt,
x_alt_borw_tot_debt => p_interface.alt_borw_tot_stu_loan_debt_amt,
x_act_interest_rate => p_interface.act_interest_rate_num,
x_service_type_code => p_interface.service_type_code,
x_rev_notice_of_guarnt => p_interface.rev_notice_of_guarnt_code,
x_sch_refund_amt => p_interface.sch_refund_amt,
x_sch_refund_date => p_interface.sch_refund_date,
x_uniq_layout_vend_code => p_interface.uniq_layout_vend_code,
x_uniq_layout_ident_code => p_interface.uniq_layout_ident_code,
x_p_person_id => g_p_person_id,
x_p_ssn_chg_date => NULL,--Change Field for Direct Loan
x_p_dob_chg_date => NULL,--Change Field for Direct Loan
x_p_permt_addr_chg_date => NULL,--Change Field for Direct Loan
x_p_default_status => l_p_default_status,
x_p_signature_code => p_interface.b_signature_flag,
x_p_signature_date => p_interface.b_signature_date,
x_s_ssn_chg_date => NULL,--Change Field for Direct Loan
x_s_dob_chg_date => NULL,--Change Field for Direct Loan
x_s_permt_addr_chg_date => NULL,--Change Field for Direct Loan
x_s_local_addr_chg_date => NULL,--Change Field for Direct Loan
x_s_default_status => p_interface.s_default_status_flag,
x_s_signature_code => p_interface.s_signature_flag,
x_pnote_batch_id => NULL,--This is applicable for Direct Loans
x_pnote_ack_date => NULL,--This is applicable for Direct Loans
x_pnote_mpn_ind => NULL,--This is applicable for Direct Loans
x_elec_mpn_ind => NULL,--This is applicable for Direct Loans
x_borr_sign_ind => p_interface.borr_sign_flag ,
x_stud_sign_ind => p_interface.stud_sign_flag,
x_borr_credit_auth_code => p_interface.borr_credit_auth_flag,
x_relationship_cd => p_interface.relationship_cd,
x_interest_rebate_percent_num => NULL,
x_cps_trans_num => NULL,
x_atd_entity_id_txt => NULL,
x_rep_entity_id_txt => NULL,
x_crdt_decision_status => p_interface.credit_status_code,
x_note_message => NULL,
x_book_loan_amt => NULL,
x_book_loan_amt_date => NULL,
x_pymt_servicer_amt => NULL,
x_pymt_servicer_date => NULL,
x_external_loan_id_txt => p_interface.external_loan_id_txt,
x_alt_approved_amt => p_interface.alt_approved_amt,
x_flp_approved_amt => p_interface.flp_approved_amt,
x_fls_approved_amt => p_interface.fls_approved_amt,
x_flu_approved_amt => p_interface.flu_approved_amt,
x_guarantor_use_txt => p_interface.guarantor_use_txt,
x_lender_use_txt => p_interface.lender_use_txt,
x_loan_app_form_code => p_interface.fed_appl_form_type,
x_reinstatement_amt => p_interface.reinst_avail_amt,
x_requested_loan_amt => p_interface.req_loan_amt,
x_school_use_txt => p_interface.school_use_txt,
x_deferment_request_code => p_interface.defer_req_flag,
x_eft_authorization_code => p_interface.eft_auth_flag,
x_actual_record_type_code => p_interface.actual_record_type_code,
x_override_grade_level_code => NULL,
x_acad_begin_date => NULL,
x_acad_end_date => NULL,
x_b_alien_reg_num_txt => NULL,
x_esign_src_typ_cd => p_interface.esign_src_typ_cd
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_records.debug','After insert into IGF_SL_LOR ln_origination_id ' || ln_origination_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_records.debug','Before insert into IGF_SL_ALT_BORW_ALL');
igf_sl_alt_borw_pkg.insert_row (
x_mode => 'R',
x_rowid => alt_rowid,
x_stud_mth_auto_pymt => p_interface.stud_mth_auto_pymt_amt,
x_stud_mth_ed_loan_pymt => p_interface.stud_mth_ed_loan_pymt_amt ,
x_stud_mth_other_pymt => p_interface.stud_mth_other_pymt_amt ,
x_stud_mth_crdtcard_pymt => p_interface.stud_mth_crdtcard_pymt_amt ,
x_repayment_opt_code => p_interface.repayment_opt_code,
x_stud_mth_housing_pymt => p_interface.stud_mth_housing_pymt_amt ,
x_albw_id => ln_albw_id,
x_loan_id => ln_loan_id,
x_fed_stafford_loan_debt => p_interface.fed_stafford_loan_debt_amt ,
x_fed_sls_debt => p_interface.fed_sls_debt_amt,
x_heal_debt => p_interface.heal_debt_amt,
x_perkins_debt => p_interface.perkins_debt_amt,
x_other_debt => p_interface.other_debt_amt,
x_crdt_undr_difft_name => p_interface.crdt_undr_difft_name_flag,
x_borw_gross_annual_sal => p_interface.borw_gross_annual_sal_amt,
x_borw_other_income => p_interface.borw_other_income_amt,
x_student_major => p_interface.student_major_txt,
x_int_rate_opt => p_interface.int_rate_opt_code,
x_other_loan_amt => p_interface.other_loan_amt,
x_cs1_lname => p_interface.cs1_lname,
x_cs1_fname => p_interface.cs1_fname,
x_cs1_mi_txt => p_interface.cs1_mi_txt,
x_cs1_ssn_txt => p_interface.cs1_ssn_txt,
x_cs1_citizenship_status => p_interface.cs1_citizenship_status,
x_cs1_address_line_1_txt => p_interface.cs1_address_line_1_txt,
x_cs1_address_line_2_txt => p_interface.cs1_address_line_2_txt,
x_cs1_city_txt => p_interface.cs1_city_txt,
x_cs1_state_txt => p_interface.cs1_state_txt,
x_cs1_zip_txt => p_interface.cs1_zip_txt,
x_cs1_zip_suffix_txt => p_interface.cs1_zip_suffix_txt,
x_cs1_telephone_number_txt => p_interface.cs1_telephone_number_txt,
x_cs1_signature_code_txt => p_interface.cs1_signature_code_txt,
x_cs2_lname => p_interface.cs2_lname,
x_cs2_fname => p_interface.cs2_fname,
x_cs2_mi_txt => p_interface.cs2_mi_txt,
x_cs2_ssn_txt => p_interface.cs2_ssn_txt,
x_cs2_citizenship_status => p_interface.cs2_citizenship_status,
x_cs2_address_line_1_txt => p_interface.cs2_address_line_1_txt,
x_cs2_address_line_2_txt => p_interface.cs2_address_line_2_txt,
x_cs2_city_txt => p_interface.cs2_city_txt,
x_cs2_state_txt => p_interface.cs2_state_txt,
x_cs2_zip_txt => p_interface.cs2_zip_txt,
x_cs2_zip_suffix_txt => p_interface.cs2_zip_suffix_txt,
x_cs2_telephone_number_txt => p_interface.cs2_telephone_number_txt,
x_cs2_signature_code_txt => p_interface.cs2_signature_code_txt,
x_cs1_credit_auth_code_txt => p_interface.cs1_credit_auth_code_txt,
x_cs1_birth_date => p_interface.cs1_birth_date,
x_cs1_drv_license_num_txt => p_interface.cs1_drv_license_num_txt,
x_cs1_drv_license_state_txt => p_interface.cs1_drv_license_state_txt,
x_cs1_elect_sig_ind_code_txt => p_interface.cs1_elect_sig_ind_code_txt,
x_cs1_frgn_postal_code_txt => p_interface.cs1_frgn_postal_code_txt,
x_cs1_frgn_tel_num_prefix_txt => p_interface.cs1_frgn_tel_num_prefix_txt,
x_cs1_gross_annual_sal_num => p_interface.cs1_gross_annual_sal_num,
x_cs1_mthl_auto_pay_txt => p_interface.cs1_mthl_auto_pay_txt,
x_cs1_mthl_cc_pay_txt => p_interface.cs1_mthl_cc_pay_txt,
x_cs1_mthl_edu_loan_pay_txt => p_interface.cs1_mthl_edu_loan_pay_txt,
x_cs1_mthl_housing_pay_txt => p_interface.cs1_mthl_housing_pay_txt,
x_cs1_mthl_other_pay_txt => p_interface.cs1_mthl_other_pay_txt,
x_cs1_other_income_amt => p_interface.cs1_other_income_amt,
x_cs1_rel_to_student_flag => p_interface.cs1_rel_to_student_flag,
x_cs1_suffix_txt => p_interface.cs1_suffix_txt,
x_cs1_years_at_address_txt => p_interface.cs1_years_at_address_txt,
x_cs2_credit_auth_code_txt => p_interface.cs2_credit_auth_code_txt,
x_cs2_birth_date => p_interface.cs2_birth_date,
x_cs2_drv_license_num_txt => p_interface.cs2_drv_license_num_txt,
x_cs2_drv_license_state_txt => p_interface.cs2_drv_license_state_txt,
x_cs2_elect_sig_ind_code_txt => p_interface.cs2_elect_sig_ind_code_txt,
x_cs2_frgn_postal_code_txt => p_interface.cs2_frgn_postal_code_txt,
x_cs2_frgn_tel_num_prefix_txt => p_interface.cs2_frgn_tel_num_prefix_txt,
x_cs2_gross_annual_sal_num => p_interface.cs2_gross_annual_sal_num,
x_cs2_mthl_auto_pay_txt => p_interface.cs2_mthl_auto_pay_txt,
x_cs2_mthl_cc_pay_txt => p_interface.cs2_mthl_cc_pay_txt,
x_cs2_mthl_edu_loan_pay_txt => p_interface.cs2_mthl_edu_loan_pay_txt,
x_cs2_mthl_housing_pay_txt => p_interface.cs2_mthl_housing_pay_txt,
x_cs2_mthl_other_pay_txt => p_interface.cs2_mthl_other_pay_txt,
x_cs2_other_income_amt => p_interface.cs2_other_income_amt,
x_cs2_rel_to_student_flag => p_interface.cs2_rel_to_student_flag,
x_cs2_suffix_txt => p_interface.cs2_suffix_txt,
x_cs2_years_at_address_txt => p_interface.cs2_years_at_address_txt
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_records.debug','After insert into IGF_SL_ALT_BORW_ALL albw_id ' || ln_albw_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_record.debug','Before insert into IGF_SL_LOR_LOC_ALL');
igf_sl_lor_loc_pkg.insert_row (
x_rowid => loc_rowid,
x_loan_id => ln_loan_id,
x_origination_id => ln_origination_id,
x_loan_number => p_interface.loan_number_txt,
x_loan_type => p_fed_fund,
x_loan_amt_offered => l_get_award.offered_amt,
x_loan_amt_accepted => l_get_award.accepted_amt,
x_loan_per_begin_date => p_interface.loan_per_begin_date,
x_loan_per_end_date => p_interface.loan_per_end_date,
x_acad_yr_begin_date => NULL,--This is applicable for Direct Loans
x_acad_yr_end_date => NULL,--This is applicable for Direct Loans
x_loan_status => p_interface.loan_status_code,
x_loan_status_date => p_interface.loan_status_date,
x_loan_chg_status => NULL,--This is applicable for Direct Loans
x_loan_chg_status_date => NULL,--This is applicable for Direct Loans
x_req_serial_loan_code => p_interface.req_serial_loan_code,
x_act_serial_loan_code => p_interface.act_serial_loan_code,
x_active => p_interface.active_flag,
x_active_date => p_interface.active_date,
x_sch_cert_date => p_interface.sch_cert_date,
x_orig_status_flag => NULL,--This is applicable for Direct Loans
x_orig_batch_id => p_interface.orig_batch_id_txt,
x_orig_batch_date => p_interface.orig_batch_date,
x_chg_batch_id => NULL,--This is applicable for Direct Loans
x_orig_ack_date => p_interface.orig_ack_date,
x_credit_override => NULL,
x_credit_decision_date => p_interface.credit_status_date,
x_pnote_delivery_code => p_interface.pnote_delivery_code,
x_pnote_status => p_interface.pnote_status_code,
x_pnote_status_date => p_interface.pnote_status_date,
x_pnote_id => NULL,--This is applicable for Direct Loans
x_pnote_print_ind => NULL,--This is applicable for Direct Loans
x_pnote_accept_amt => NULL,--This is applicable for Direct Loans
x_pnote_accept_date => NULL,--This is applicable for Direct Loans
x_p_signature_code => p_interface.b_signature_flag,
x_p_signature_date => p_interface.b_signature_date,
x_s_signature_code => p_interface.s_signature_flag,
x_unsub_elig_for_heal => NULL,--This is applicable for Direct Loans
x_disclosure_print_ind => NULL,--This is applicable for Direct Loans
x_orig_fee_perct => l_setup.est_orig_fee_perct,
x_borw_confirm_ind => p_interface.borw_confirm_flag,
x_borw_interest_ind => p_interface.borw_interest_flag ,
x_unsub_elig_for_depnt => NULL,--This is applicable for Direct Loans
x_guarantee_amt => p_interface.guarantee_amt,
x_guarantee_date => p_interface.guarantee_date,
x_guarnt_adj_ind => p_interface.guarnt_adj_flag,
x_guarnt_amt_redn_code => p_interface.guarnt_amt_redn_code,
x_guarnt_status_code => p_interface.guarnt_status_code,
x_guarnt_status_date => p_interface.guarnt_status_date,
x_lend_apprv_denied_code => p_interface.lend_apprv_denied_code,
x_lend_apprv_denied_date => p_interface.lend_apprv_denied_date,
x_lend_status_code => p_interface.lend_status_code,
x_lend_status_date => p_interface.lend_status_date,
x_grade_level_code => p_interface.grade_level_code,
x_enrollment_code => p_interface.enrollment_code,
x_anticip_compl_date => p_interface.anticip_compl_date,
x_borw_lender_id => loan_rec.lender_id,
x_duns_borw_lender_id => NULL,
x_guarantor_id => loan_rec.guarantor_id,
x_duns_guarnt_id => NULL,
x_prc_type_code => p_interface.prc_type_code,
x_rec_type_ind => p_interface.record_code,
x_cl_loan_type => lv_cl_loan_type,
x_cl_seq_number => p_interface.cl_seq_num,
x_last_resort_lender => p_interface.last_resort_lender_flag,
x_lender_id => loan_rec.lender_id,
x_duns_lender_id => NULL,
x_lend_non_ed_brc_id => loan_rec.lend_non_ed_brc_id,
x_recipient_id => loan_rec.recipient_id,
x_recipient_type => loan_rec.recipient_type,
x_duns_recip_id => NULL,
x_recip_non_ed_brc_id => loan_rec.recip_non_ed_brc_id,
x_cl_rec_status => p_interface.cl_rec_status,
x_cl_rec_status_last_update => p_interface.cl_rec_status_last_update,
x_alt_prog_type_code => p_interface.alt_prog_type_cd,
x_alt_appl_ver_code => p_interface.alt_appl_ver_code_num,
x_borw_outstd_loan_code => p_interface.borw_outstd_loan_flag,
x_mpn_confirm_code => p_interface.mpn_confirm_code,
x_resp_to_orig_code => p_interface.resp_to_orig_flag,
x_appl_loan_phase_code => p_interface.appl_loan_phase_code,
x_appl_loan_phase_code_chg => p_interface.appl_loan_phase_code_chg,
x_tot_outstd_stafford => p_interface.tot_outstd_stafford_amt,
x_tot_outstd_plus => p_interface.tot_outstd_plus_amt,
x_alt_borw_tot_debt => p_interface.alt_borw_tot_stu_loan_debt_amt,
x_act_interest_rate => p_interface.act_interest_rate_num,
x_service_type_code => p_interface.service_type_code,
x_rev_notice_of_guarnt => p_interface.rev_notice_of_guarnt_code,
x_sch_refund_amt => p_interface.sch_refund_amt,
x_sch_refund_date => p_interface.sch_refund_date,
x_uniq_layout_vend_code => p_interface.uniq_layout_vend_code,
x_uniq_layout_ident_code => p_interface.uniq_layout_ident_code,
x_p_person_id => loan_rec.p_person_id,
x_p_ssn => SUBSTR(parent_dtl_rec.p_ssn,1,9),
x_p_ssn_chg_date => NULL,-- Change field
x_p_last_name => parent_dtl_rec.p_last_name,
x_p_first_name => parent_dtl_rec.p_first_name,
x_p_middle_name => parent_dtl_rec.p_middle_name,
x_p_permt_addr1 => parent_dtl_rec.p_permt_addr1,
x_p_permt_addr2 => parent_dtl_rec.p_permt_addr2,
x_p_permt_city => parent_dtl_rec.p_permt_city,
x_p_permt_state => parent_dtl_rec.p_permt_state,
x_p_permt_zip => parent_dtl_rec.p_permt_zip,
x_p_permt_addr_chg_date => NULL,-- Change field
x_p_permt_phone => lv_p_permt_phone,
x_p_email_addr => parent_dtl_rec.p_email_addr,
x_p_date_of_birth => parent_dtl_rec.p_date_of_birth,
x_p_dob_chg_date => NULL,-- Change field
x_p_license_num => parent_dtl_rec.p_license_num,
x_p_license_state => parent_dtl_rec.p_license_state,
x_p_citizenship_status => parent_dtl_rec.p_citizenship_status,
x_p_alien_reg_num => parent_dtl_rec.p_alien_reg_num,
x_p_default_status => loan_rec.p_default_status,
x_p_foreign_postal_code => p_interface.b_foreign_postal_cd,
x_p_state_of_legal_res => parent_dtl_rec.p_state_of_legal_res,
x_p_legal_res_date => parent_dtl_rec.p_legal_res_date,
x_s_ssn => SUBSTR(student_dtl_rec.p_ssn,1,9),
x_s_ssn_chg_date => NULL,-- Change field
x_s_last_name => student_dtl_rec.p_last_name,
x_s_first_name => student_dtl_rec.p_first_name,
x_s_middle_name => student_dtl_rec.p_middle_name,
x_s_permt_addr1 => student_dtl_rec.p_permt_addr1,
x_s_permt_addr2 => student_dtl_rec.p_permt_addr2,
x_s_permt_city => student_dtl_rec.p_permt_city,
x_s_permt_state => student_dtl_rec.p_permt_state,
x_s_permt_zip => student_dtl_rec.p_permt_zip,
x_s_permt_addr_chg_date => NULL,-- Change field
x_s_permt_phone => lv_s_permt_phone,
x_s_local_addr1 => student_dtl_rec.p_local_addr1,
x_s_local_addr2 => student_dtl_rec.p_local_addr2,
x_s_local_city => student_dtl_rec.p_local_city,
x_s_local_state => student_dtl_rec.p_local_state,
x_s_local_zip => student_dtl_rec.p_local_zip,
x_s_local_addr_chg_date => NULL,-- Change field
x_s_email_addr => student_dtl_rec.p_email_addr,
x_s_date_of_birth => student_dtl_rec.p_date_of_birth,
x_s_dob_chg_date => NULL,-- Change field
x_s_license_num => lv_s_license_number,
x_s_license_state => lv_s_license_state,
x_s_depncy_status => lv_dependency_status,
x_s_default_status => p_interface.s_default_status_flag,
x_s_citizenship_status => lv_s_citizenship_status,
x_s_alien_reg_num => lv_alien_reg_num,
x_s_foreign_postal_code => p_interface.b_foreign_postal_cd,
x_mode => 'R',
x_pnote_batch_id => NULL,
x_pnote_ack_date => NULL,
x_pnote_mpn_ind => NULL,
x_award_id => p_award_id,
x_base_id => l_get_base_id.base_id,
x_document_id_txt => NULL,
x_loan_key_num => NULL,
x_interest_rebate_percent_num=> NULL,
x_fin_award_year => NULL,
x_cps_trans_num => NULL,
x_atd_entity_id_txt => NULL,
x_rep_entity_id_txt => NULL,
x_source_entity_id_txt => NULL,
x_pymt_servicer_amt => NULL,
x_pymt_servicer_date => NULL,
x_book_loan_amt => NULL,
x_book_loan_amt_date => NULL,
x_s_chg_birth_date => NULL,
x_s_chg_ssn => NULL,
x_s_chg_last_name => NULL,
x_b_chg_birth_date => NULL,
x_b_chg_ssn => NULL,
x_b_chg_last_name => NULL,
x_note_message => NULL,
x_full_resp_code => NULL,
x_s_permt_county => NULL,
x_b_permt_county => NULL,
x_s_permt_country => NULL,
x_b_permt_country => NULL,
x_crdt_decision_status => p_interface.credit_status_code,
x_actual_record_type_code => p_interface.actual_record_type_code,
x_alt_approved_amt => p_interface.alt_approved_amt,
x_alt_borrower_ind_flag => p_interface.b_stu_indicator_flag,
x_borower_credit_authoriz_flag => p_interface.borr_credit_auth_flag,
x_borower_electronic_sign_flag => p_interface.borr_sign_flag,
x_cost_of_attendance_amt => p_interface.coa_amt,
x_deferment_request_code => p_interface.defer_req_flag,
x_eft_authorization_code => p_interface.eft_auth_flag,
x_established_fin_aid_amount => p_interface.est_fa_amt,
x_expect_family_contribute_amt => p_interface.efc_amt,
x_external_loan_id_txt => p_interface.external_loan_id_txt,
x_flp_approved_amt => p_interface.flp_approved_amt,
x_fls_approved_amt => p_interface.fls_approved_amt,
x_flu_approved_amt => p_interface.flu_approved_amt,
x_guarantor_use_txt => p_interface.guarantor_use_txt,
x_lender_use_txt => p_interface.lender_use_txt,
x_loan_app_form_code => p_interface.fed_appl_form_type,
x_mpn_type_flag => NULL,
x_reinstatement_amt => p_interface.reinst_avail_amt,
x_requested_loan_amt => p_interface.req_loan_amt,
x_school_id_txt => SUBSTR(p_interface.loan_number_txt,1,8),
x_school_use_txt => p_interface.school_use_txt,
x_student_electronic_sign_flag => p_interface.stud_sign_flag,
x_esign_src_typ_cd => p_interface.esign_src_typ_cd
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_record.debug','After insert into IGF_SL_LOR_LOC_ALL');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_records.debug','Before insert into IGF_SL_CL_BATCH_ALL - I');
igf_sl_cl_batch_pkg.insert_row (
x_rowid => clb_rowid,
x_cbth_id => ln_cbth_id,
x_batch_id => p_interface.orig_send_batch_id_txt,
x_file_creation_date => p_interface.file_creation_date,
x_file_trans_date => p_interface.file_trans_date ,
x_file_ident_code => 'A005P',
x_recipient_id => NULL,
x_recip_non_ed_brc_id => NULL,
x_source_id => p_interface.source_id_txt,
x_source_non_ed_brc_id => p_interface.source_non_ed_brc_id_txt,
x_send_resp => p_interface.send_resp_code,
x_mode => '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);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_records.debug','After insert into IGF_SL_CL_BATCH_ALL - I ln_cbth_id ' || ln_cbth_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_record.debug','Before insert into IGF_SL_CL_BATCH_ALL');
igf_sl_cl_batch_pkg.insert_row (
x_rowid => clb_rowid,
x_cbth_id => ln_cbth_id,
x_batch_id => p_interface.orig_ack_batch_id_txt ,
x_file_creation_date => p_interface.file_creation_date,
x_file_trans_date => p_interface.file_trans_date ,
x_file_ident_code => 'R005P',
x_recipient_id => NULL,
x_recip_non_ed_brc_id => NULL,
x_source_id => p_interface.source_id_txt,
x_source_non_ed_brc_id => p_interface.source_non_ed_brc_id_txt,
x_send_resp => p_interface.send_resp_code,
x_mode => '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);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_records.debug','Before insert into IGF_SL_CL_BATCH_ALL ln_cbth_id ' || ln_cbth_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_records.debug','Before insert into IGF_SL_CL_RESP_R1');
igf_sl_cl_resp_r1_pkg.insert_row (
x_mode => 'R',
x_rowid => l_rl_row_id,
x_clrp1_id => l_clrp1_id, -- generated by sequence and value returned
x_cbth_id => ln_cbth_id,
x_rec_code => '@1',
x_rec_type_ind => p_interface.record_code,
x_b_last_name => l_b_last_name,
x_b_first_name => l_b_first_name,
x_b_middle_name => l_b_middle_name,
x_b_ssn => TO_NUMBER(l_b_ssn),
x_b_permt_addr1 => l_b_permt_addr1,
x_b_permt_addr2 => l_b_permt_addr2,
x_b_permt_city => l_b_permt_city,
x_b_permt_state => l_b_permt_state,
x_b_permt_zip => l_b_permt_zip,
x_b_permt_zip_suffix => l_b_permt_zip_suffix,
x_b_permt_phone => l_b_permt_phone,
x_b_date_of_birth => l_b_dob,
x_cl_loan_type => lv_cl_loan_type,
x_req_loan_amt => p_interface.req_loan_amt,
x_defer_req_code => p_interface.defer_req_flag,
x_borw_interest_ind => p_interface.borw_interest_flag,
x_eft_auth_code => p_interface.eft_auth_flag,
x_b_signature_code => l_b_signature_code,
x_b_signature_date => l_b_signature_date,
x_loan_number => p_interface.loan_number_txt,
x_cl_seq_number => p_interface.cl_seq_num,
x_borr_credit_auth_code => p_interface.borr_credit_auth_flag,
x_b_citizenship_status => l_b_citizenship_status,
x_b_state_of_legal_res => l_b_state_of_legal_res,
x_b_legal_res_date => l_b_legal_res_date,
x_b_default_status => l_b_default_status,
x_b_outstd_loan_code => p_interface.borw_outstd_loan_flag,
x_b_indicator_code => p_interface.b_stu_indicator_flag,
x_s_last_name => student_dtl_rec.p_last_name,
x_s_first_name => student_dtl_rec.p_first_name,
x_s_middle_name => student_dtl_rec.p_middle_name,
x_s_ssn => TO_NUMBER(SUBSTR(student_dtl_rec.p_ssn,1,9)),
x_s_date_of_birth => student_dtl_rec.p_date_of_birth,
x_s_citizenship_status => lv_s_citizenship_status,
x_s_default_code => p_interface.s_default_status_flag,
x_s_signature_code => p_interface.s_signature_flag,
x_school_id => SUBSTR(p_interface.loan_number_txt,1,8),
x_loan_per_begin_date => p_interface.loan_per_begin_date,
x_loan_per_end_date => p_interface.loan_per_end_date,
x_grade_level_code => p_interface.grade_level_code,
x_borr_sign_ind => p_interface.borr_sign_flag,
x_enrollment_code => p_interface.enrollment_code,
x_anticip_compl_date => p_interface.anticip_compl_date,
x_coa_amt => p_interface.coa_amt,
x_efc_amt => p_interface.efc_amt,
x_est_fa_amt => p_interface.est_fa_amt,
x_fls_cert_amt => p_interface.fls_cert_amt,
x_flu_cert_amt => p_interface.flu_cert_amt,
x_flp_cert_amt => p_interface.flp_cert_amt,
x_sch_cert_date => p_interface.sch_cert_date,
x_alt_cert_amt => p_interface.alt_cert_amt,
x_alt_appl_ver_code => p_interface.alt_appl_ver_code_num,
x_duns_school_id => NULL,
x_lender_id => NULL,
x_fls_approved_amt => p_interface.fls_approved_amt,
x_flu_approved_amt => p_interface.flu_approved_amt,
x_flp_approved_amt => p_interface.flp_approved_amt,
x_alt_approved_amt => p_interface.alt_approved_amt,
x_duns_lender_id => NULL,
x_guarantor_id => NULL,
x_fed_appl_form_code => p_interface.fed_appl_form_type,
x_duns_guarnt_id => NULL,
x_lend_blkt_guarnt_ind => p_interface.lend_blkt_guarnt_flag,
x_lend_blkt_guarnt_appr_date => p_interface.lend_blkt_guarnt_appr_date,
x_guarnt_adj_ind => p_interface.guarnt_adj_flag,
x_guarantee_date => p_interface.guarantee_date,
x_guarantee_amt => p_interface.guarantee_amt,
x_req_serial_loan_code => p_interface.req_serial_loan_code,
x_borw_confirm_ind => p_interface.borw_confirm_flag,
x_b_license_state => l_b_license_state,
x_b_license_number => l_b_license_number,
x_b_ref_code => p_interface.b_reference_flag,
x_pnote_delivery_code => p_interface.pnote_delivery_code,
x_b_foreign_postal_code => p_interface.b_foreign_postal_cd,
x_stud_sign_ind => p_interface.stud_sign_flag,
x_lend_non_ed_brc_id => NULL,
x_last_resort_lender => p_interface.last_resort_lender_flag,
x_resp_to_orig_code => p_interface.resp_to_orig_flag,
x_err_mesg_1 => p_interface.err_mesg_1_cd,
x_err_mesg_2 => p_interface.err_mesg_2_cd,
x_err_mesg_3 => p_interface.err_mesg_3_cd,
x_err_mesg_4 => p_interface.err_mesg_4_cd,
x_err_mesg_5 => p_interface.err_mesg_5_cd,
x_guarnt_amt_redn_code => p_interface.guarnt_amt_redn_code,
x_tot_outstd_stafford => p_interface.tot_outstd_stafford_amt,
x_tot_outstd_plus => p_interface.tot_outstd_plus_amt,
x_b_permt_addr_chg_date => p_interface.b_permt_addr_chg_date,
x_alt_prog_type_code => p_interface.alt_prog_type_cd,
x_alt_borw_tot_debt => p_interface.alt_borw_tot_stu_loan_debt_amt,
x_act_interest_rate => p_interface.act_interest_rate_num,
x_prc_type_code => p_interface.prc_type_code,
x_service_type_code => p_interface.service_type_code,
x_rev_notice_of_guarnt => p_interface.rev_notice_of_guarnt_code,
x_sch_refund_amt => p_interface.sch_refund_amt,
x_sch_refund_date => p_interface.sch_refund_date,
x_guarnt_status_code => p_interface.guarnt_status_code,
x_lender_status_code => p_interface.lend_status_code,
x_pnote_status_code => p_interface.pnote_status_code,
x_credit_status_code => p_interface.credit_status_code,
x_guarnt_status_date => p_interface.guarnt_status_date,
x_lender_status_date => p_interface.lend_status_date,
x_pnote_status_date => p_interface.pnote_status_date,
x_credit_status_date => p_interface.credit_status_date,
x_act_serial_loan_code => p_interface.act_serial_loan_code,
x_amt_avail_for_reinst => p_interface.reinst_avail_amt,
x_sch_non_ed_brc_id => p_interface.source_non_ed_brc_id_txt,
x_uniq_layout_vend_code => p_interface.uniq_layout_vend_code,
x_uniq_layout_ident_code => p_interface.uniq_layout_ident_code,
x_resp_record_status => 'Y',
x_appl_loan_phase_code => p_interface.appl_loan_phase_code,
x_appl_loan_phase_code_chg => p_interface.appl_loan_phase_code_chg,
x_cl_rec_status => p_interface.cl_rec_status,
x_cl_rec_status_last_update => p_interface.cl_rec_status_last_update,
x_cl_version_code => g_rel_version,
x_guarantor_use_txt => p_interface.guarantor_use_txt,
x_lend_apprv_denied_code => p_interface.lend_apprv_denied_code,
x_lend_apprv_denied_date => p_interface.lend_apprv_denied_date,
x_lender_use_txt => p_interface.lender_use_txt,
x_mpn_confirm_ind => p_interface.mpn_confirm_code,
x_school_use_txt => p_interface.school_use_txt,
x_b_alien_reg_num_txt => NULL,
x_esign_src_typ_cd => p_interface.esign_src_typ_cd
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_records.debug','After insert into IGF_SL_CL_RESP_R1 l_clrp1_id ' || l_clrp1_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_records.debug','Before insert into IGF_SL_CL_RESP_R4');
igf_sl_cl_resp_r4_pkg.insert_row (
x_mode => 'R',
x_rowid => l_r4_row_id,
x_clrp1_id => l_clrp1_id,
x_loan_number => p_interface.loan_number_txt,
x_fed_stafford_loan_debt => p_interface.fed_stafford_loan_debt_amt,
x_fed_sls_debt => p_interface.fed_sls_debt_amt ,
x_heal_debt => p_interface.heal_debt_amt ,
x_perkins_debt => p_interface.perkins_debt_amt ,
x_other_debt => p_interface.other_debt_amt ,
x_crdt_undr_difft_name => p_interface.crdt_undr_difft_name_flag,
x_borw_gross_annual_sal => p_interface.borw_gross_annual_sal_amt,
x_borw_other_income => p_interface.borw_other_income_amt ,
x_student_major => p_interface.student_major_txt ,
x_int_rate_opt => p_interface.int_rate_opt_code,
x_repayment_opt_code => p_interface.repayment_opt_code,
x_stud_mth_housing_pymt => p_interface.stud_mth_housing_pymt_amt ,
x_stud_mth_crdtcard_pymt => p_interface.stud_mth_crdtcard_pymt_amt ,
x_stud_mth_auto_pymt => p_interface.stud_mth_auto_pymt_amt ,
x_stud_mth_ed_loan_pymt => p_interface.stud_mth_ed_loan_pymt_amt ,
x_stud_mth_other_pymt => p_interface.stud_mth_other_pymt_amt ,
x_cosnr_1_last_name => NULL,
x_cosnr_1_first_name => NULL,
x_cosnr_1_middle_name => NULL,
x_cosnr_1_ssn => NULL,
x_cosnr_1_citizenship => NULL,
x_cosnr_1_addr_line1 => NULL,
x_cosnr_1_addr_line2 => NULL,
x_cosnr_1_city => NULL,
x_cosnr_1_state => NULL,
x_cosnr_1_zip => NULL,
x_cosnr_1_zip_suffix => NULL,
x_cosnr_1_phone => NULL,
x_cosnr_1_sig_code => NULL,
x_cosnr_1_gross_anl_sal => NULL,
x_cosnr_1_other_income => NULL,
x_cosnr_1_forn_postal_code => NULL,
x_cosnr_1_forn_phone_prefix => NULL,
x_cosnr_1_dob => NULL,
x_cosnr_1_license_state => NULL,
x_cosnr_1_license_num => NULL,
x_cosnr_1_relationship_to => NULL,
x_cosnr_1_years_at_addr => NULL,
x_cosnr_1_mth_housing_pymt => NULL,
x_cosnr_1_mth_crdtcard_pymt => NULL,
x_cosnr_1_mth_auto_pymt => NULL,
x_cosnr_1_mth_ed_loan_pymt => NULL,
x_cosnr_1_mth_other_pymt => NULL,
x_cosnr_1_crdt_auth_code => NULL,
x_cosnr_2_last_name => NULL,
x_cosnr_2_first_name => NULL,
x_cosnr_2_middle_name => NULL,
x_cosnr_2_ssn => NULL,
x_cosnr_2_citizenship => NULL,
x_cosnr_2_addr_line1 => NULL,
x_cosnr_2_addr_line2 => NULL,
x_cosnr_2_city => NULL,
x_cosnr_2_state => NULL,
x_cosnr_2_zip => NULL,
x_cosnr_2_zip_suffix => NULL,
x_cosnr_2_phone => NULL,
x_cosnr_2_sig_code => NULL,
x_cosnr_2_gross_anl_sal => NULL,
x_cosnr_2_other_income => NULL,
x_cosnr_2_forn_postal_code => NULL,
x_cosnr_2_forn_phone_prefix => NULL,
x_cosnr_2_dob => NULL,
x_cosnr_2_license_state => NULL,
x_cosnr_2_license_num => NULL,
x_cosnr_2_relationship_to => NULL,
x_cosnr_2_years_at_addr => NULL,
x_cosnr_2_mth_housing_pymt => NULL,
x_cosnr_2_mth_crdtcard_pymt => NULL,
x_cosnr_2_mth_auto_pymt => NULL,
x_cosnr_2_mth_ed_loan_pymt => NULL,
x_cosnr_2_mth_other_pymt => NULL,
x_cosnr_2_crdt_auth_code => NULL,
x_other_loan_amt => p_interface.other_loan_amt,
x_alt_layout_owner_code_txt => NULL,
x_alt_layout_identi_code_txt => NULL,
x_student_school_phone_txt => NULL,
x_first_csgnr_elec_sign_flag => NULL,
x_second_csgnr_elec_sign_flag => NULL
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_records.debug','Before insert into IGF_SL_CL_RESP_R4 l_clrp1_id ' || l_clrp1_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_records.debug','Before insert into IGF_SL_CL_RESP_R8');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_records.debug','Before Update IGF_AW_AWD_DISB');
igf_aw_awd_disb_pkg.update_row (
x_mode => 'R',
x_rowid => l_disb_det.rowid,
x_award_id => l_disb_det.award_id,
x_disb_num => l_disb_det.disb_num,
x_tp_cal_type => l_disb_det.tp_cal_type,
x_tp_sequence_number => l_disb_det.tp_sequence_number ,
x_disb_gross_amt => l_disb_det.disb_gross_amt,
x_fee_1 => l_disb_interface.origination_fee_amt,
x_fee_2 => l_disb_interface.guarantee_fee_amt,
x_disb_net_amt => lv_disb_net_amt,
x_disb_date => l_disb_det.disb_date,
x_trans_type => l_disb_det.trans_type,
x_elig_status => l_disb_det.elig_status,
x_elig_status_date => l_disb_det.elig_status_date,
x_affirm_flag => l_disb_det.affirm_flag,
x_hold_rel_ind => l_disb_interface.disbursement_hld_release_flag,
x_manual_hold_ind => l_disb_det.manual_hold_ind,
x_disb_status => l_disb_interface.disbursement_status_code,
x_disb_status_date => l_disb_interface.disbursement_status_date,
x_late_disb_ind => l_disb_interface.late_disbursement_flag,
x_fund_dist_mthd => l_disb_interface.fund_dist_mthd_type,
x_prev_reported_ind => l_disb_interface.prev_reported_flag,
x_fund_release_date => l_disb_interface.fund_release_date,
x_fund_status => l_disb_interface.fund_status_code,
x_fund_status_date => l_disb_interface.fund_status_date,
x_fee_paid_1 => l_disb_interface.origination_fees_paid_amt,
x_fee_paid_2 => l_disb_interface.guarantee_fees_paid_amt,
x_cheque_number => l_disb_interface.check_number_txt,
x_ld_cal_type => l_disb_det.ld_cal_type,
x_ld_sequence_number => l_disb_det.ld_sequence_number,
x_disb_accepted_amt => l_disb_det.disb_accepted_amt,
x_disb_paid_amt => l_disb_det.disb_paid_amt,
x_rvsn_id => l_disb_det.rvsn_id,
x_int_rebate_amt => l_disb_det.int_rebate_amt,
x_force_disb => l_disb_det.force_disb,
x_min_credit_pts => l_disb_det.min_credit_pts,
x_disb_exp_dt => l_disb_det.disb_exp_dt,
x_verf_enfr_dt => l_disb_det.verf_enfr_dt,
x_fee_class => l_disb_det.fee_class,
x_show_on_bill => l_disb_det.show_on_bill,
x_attendance_type_code => l_disb_det.attendance_type_code,
x_base_attendance_type_code => l_disb_det.base_attendance_type_code,
x_change_type_code => l_disb_det.change_type_code,
x_fund_return_mthd_code => l_disb_det.fund_return_mthd_code,
x_payment_prd_st_date => l_disb_det.payment_prd_st_date,
x_direct_to_borr_flag => l_disb_interface.direct_to_borr_flag
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_records.debug','Before insert into IGF_SL_CL_RESP_R8 - LOOP');
igf_sl_cl_resp_r8_pkg.insert_row (
x_mode => 'R',
x_rowid => l_r8_row_id,
x_clrp1_id => l_clrp1_id,
x_clrp8_id => i,
x_disb_date => l_disb_interface.disbursement_date,
x_disb_gross_amt => l_disb_interface.gross_disbursement_amt,
x_orig_fee => l_disb_interface.origination_fee_amt,
x_guarantee_fee => l_disb_interface.guarantee_fee_amt,
x_net_disb_amt => lv_disb_net_amt,
x_disb_hold_rel_ind => l_disb_interface.disbursement_hld_release_flag,
x_disb_status => l_disb_interface.disbursement_status_code,
x_guarnt_fee_paid => l_disb_interface.guarantee_fees_paid_amt,
x_orig_fee_paid => l_disb_interface.origination_fees_paid_amt,
x_resp_record_status => 'Y',
x_layout_owner_code_txt => NULL,
x_layout_version_code_txt => NULL,
x_record_code_txt => NULL,
x_direct_to_borr_flag => l_disb_interface.direct_to_borr_flag);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_records.debug','Before insert into IGF_SL_CL_RESP_R8 - LOOP l_clrp1_id ' || l_clrp1_id || ' i ' || i);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_records.debug','Before insert into IGF_SL_CL_BATCH');
igf_sl_cl_batch_pkg.insert_row (
x_rowid => clb_rowid,
x_cbth_id => ln_cbth_id,
x_batch_id => l_disb_interface.roster_batch_id,
x_file_creation_date => l_disb_interface.file_creation_date,
x_file_trans_date => l_disb_interface.file_trans_date ,
x_file_ident_code => 'E005P',
x_recipient_id => l_disb_interface.recipient_id_txt,
x_recip_non_ed_brc_id => l_disb_interface.recipient_non_ed_brc_id_txt,
x_source_id => l_disb_interface.source_id_txt,
x_source_non_ed_brc_id => l_disb_interface.source_non_ed_brc_id_txt,
x_send_resp => l_disb_interface.send_resp_code,
x_mode => '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);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_records.debug','After insert into IGF_SL_CL_BATCH ln_cbth_id ' || ln_cbth_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_records.debug','Before insert into IGF_DB_CL_DISB_RESP');
igf_db_cl_disb_resp_pkg.insert_row (
x_mode => 'R',
x_rowid => l_rost_rowid,
x_cdbr_id => l_cdbr_id,
x_cbth_id => ln_cbth_id,
x_record_type => l_disb_interface.record_type,
x_loan_number => l_disb_interface.loan_number_txt,
x_cl_seq_number => p_interface.cl_seq_num,
x_b_last_name => l_b_last_name,
x_b_first_name => l_b_first_name,
x_b_middle_name => l_b_middle_name,
x_b_ssn => l_b_ssn,
x_b_addr_line_1 => l_b_permt_addr1,
x_b_addr_line_2 => l_b_permt_addr2,
x_b_city => l_b_permt_city,
x_b_state => l_b_permt_state,
x_b_zip => l_b_permt_zip,
x_b_zip_suffix => NULL,
x_b_addr_chg_date => NULL,
x_eft_auth_code => p_interface.eft_auth_flag,
x_s_last_name => student_dtl_rec.p_last_name,
x_s_first_name => student_dtl_rec.p_first_name,
x_s_middle_initial => student_dtl_rec.p_middle_name,
x_s_ssn => SUBSTR(student_dtl_rec.p_ssn,1,9),
x_school_id => SUBSTR(p_interface.loan_number_txt,1,8),
x_school_use => l_disb_interface.school_use_txt,
x_loan_per_start_date => p_interface.loan_per_begin_date ,
x_loan_per_end_date => p_interface.loan_per_end_date ,
x_cl_loan_type => lv_cl_loan_type,
x_alt_prog_type_code => p_interface.alt_prog_type_cd ,
x_lender_id => NULL,
x_lend_non_ed_brc_id => NULL,
x_lender_use => l_disb_interface.lender_use_txt,
x_borw_confirm_ind => p_interface.borw_confirm_flag,
x_tot_sched_disb => l_disb_interface.sch_disbursement_num,
x_fund_release_date => l_disb_interface.fund_release_date,
x_disb_num => l_disb_interface.disbursement_num,
x_guarantor_id => NULL,
x_guarantor_use => l_disb_interface.guarantor_use_txt,
x_guarantee_date => l_disb_interface.guarantee_date,
x_guarantee_amt => l_disb_interface.guarantee_amt,
x_gross_disb_amt => l_disb_interface.gross_disbursement_amt,
x_fee_1 => l_disb_interface.origination_fee_amt,
x_fee_2 => l_disb_interface.guarantee_fee_amt,
x_net_disb_amt => lv_disb_net_amt,
x_fund_dist_mthd => l_disb_interface.fund_dist_mthd_type,
x_check_number => l_disb_interface.check_number_txt,
x_late_disb_ind => l_disb_interface.late_disbursement_flag,
x_prev_reported_ind => l_disb_interface.prev_reported_flag,
x_err_code1 => l_disb_interface.err_mesg_1_cd,
x_err_code2 => l_disb_interface.err_mesg_2_cd,
x_err_code3 => l_disb_interface.err_mesg_3_cd,
x_err_code4 => l_disb_interface.err_mesg_4_cd,
x_err_code5 => l_disb_interface.err_mesg_5_cd,
x_fee_paid_2 => l_disb_interface.guarantee_fees_paid_amt,
x_lender_name => l_disb_interface.lender_name,
x_net_cancel_amt => l_disb_interface.net_cancel_amt,
x_duns_lender_id => NULL,
x_duns_guarnt_id => NULL,
x_hold_rel_ind => l_disb_interface.disbursement_hld_release_flag,
x_pnote_code => SUBSTR(l_disb_interface.pnote_code,1,2),
x_pnote_status_date => l_disb_interface.pnote_status_date ,
x_fee_paid_1 => l_disb_interface.origination_fees_paid_amt,
x_netted_cancel_amt => l_disb_interface.netted_cancel_amt,
x_outstd_cancel_amt => l_disb_interface.outstd_cancel_amt,
x_sch_non_ed_brc_id => p_interface.source_non_ed_brc_id_txt,
x_status => 'Y',
x_esign_src_typ_cd => NULL,
x_direct_to_borr_flag => l_disb_interface.direct_to_borr_flag);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_records.debug','Before Update IGF_AW_AWD_DISB');
igf_aw_awd_disb_pkg.update_row (
x_mode => 'R',
x_rowid => l_disb_det.rowid,
x_award_id => l_disb_det.award_id,
x_disb_num => l_disb_det.disb_num,
x_tp_cal_type => l_disb_det.tp_cal_type,
x_tp_sequence_number => l_disb_det.tp_sequence_number ,
x_disb_gross_amt => l_disb_det.disb_gross_amt,
x_fee_1 => l_disb_interface.origination_fee_amt,
x_fee_2 => l_disb_interface.guarantee_fee_amt,
x_disb_net_amt => lv_disb_net_amt,
x_disb_date => l_disb_det.disb_date,
x_trans_type => l_disb_det.trans_type,
x_elig_status => l_disb_det.elig_status,
x_elig_status_date => l_disb_det.elig_status_date,
x_affirm_flag => l_disb_det.affirm_flag,
x_hold_rel_ind => l_disb_interface.disbursement_hld_release_flag,
x_manual_hold_ind => l_disb_det.manual_hold_ind,
x_disb_status => l_disb_interface.disbursement_status_code,
x_disb_status_date => l_disb_interface.disbursement_status_date,
x_late_disb_ind => l_disb_interface.late_disbursement_flag,
x_fund_dist_mthd => l_disb_interface.fund_dist_mthd_type,
x_prev_reported_ind => l_disb_interface.prev_reported_flag,
x_fund_release_date => l_disb_interface.fund_release_date,
x_fund_status => l_disb_interface.fund_status_code,
x_fund_status_date => l_disb_interface.fund_status_date,
x_fee_paid_1 => l_disb_interface.origination_fees_paid_amt,
x_fee_paid_2 => l_disb_interface.guarantee_fees_paid_amt,
x_cheque_number => l_disb_interface.check_number_txt,
x_ld_cal_type => l_disb_det.ld_cal_type,
x_ld_sequence_number => l_disb_det.ld_sequence_number,
x_disb_accepted_amt => l_disb_det.disb_accepted_amt,
x_disb_paid_amt => l_disb_det.disb_paid_amt,
x_rvsn_id => l_disb_det.rvsn_id,
x_int_rebate_amt => l_disb_det.int_rebate_amt,
x_force_disb => l_disb_det.force_disb,
x_min_credit_pts => l_disb_det.min_credit_pts,
x_disb_exp_dt => l_disb_det.disb_exp_dt,
x_verf_enfr_dt => l_disb_det.verf_enfr_dt,
x_fee_class => l_disb_det.fee_class,
x_show_on_bill => l_disb_det.show_on_bill,
x_attendance_type_code => l_disb_det.attendance_type_code,
x_base_attendance_type_code => l_disb_det.base_attendance_type_code,
x_change_type_code => l_disb_det.change_type_code,
x_fund_return_mthd_code => l_disb_det.fund_return_mthd_code,
x_payment_prd_st_date => l_disb_det.payment_prd_st_date,
x_direct_to_borr_flag => l_disb_interface.direct_to_borr_flag
);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_records.debug','After insert into IGF_SL_CL_RESP_R8 - LOOP');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_records.debug','Before insert into IGF_SL_AWD_DISB_LOC');
SELECT *
FROM igf_sl_awd_disb_loc
WHERE award_id = p_award_id;
SELECT *
FROM igf_aw_awd_disb
WHERE award_id = p_award_id;
igf_sl_awd_disb_loc_pkg.delete_row (tbh_rec.row_id);
igf_sl_awd_disb_loc_pkg.insert_row (
x_mode => 'R',
x_rowid => lv_row_id,
x_award_id => tbh_rec.award_id,
x_disb_num => tbh_rec.disb_num,
x_disb_gross_amt => tbh_rec.disb_accepted_amt,
x_fee_1 => tbh_rec.fee_1,
x_fee_2 => tbh_rec.fee_2,
x_disb_net_amt => tbh_rec.disb_net_amt,
x_disb_date => tbh_rec.disb_date,
x_hold_rel_ind => tbh_rec.hold_rel_ind,
x_fee_paid_1 => tbh_rec.fee_paid_1,
x_fee_paid_2 => tbh_rec.fee_paid_2);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_records.debug',' After insert into IGF_SL_AWD_DISB_LOC');
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.insert_records.debug','DML Complete');
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_cl_imp_pkg.insert_records.exception','Exception ' || SQLERRM);
fnd_message.set_token('NAME','IGF_SL_CL_LI_IMP_PKG.INSERT_RECORDS');
END insert_records;
p_delete_flag IN VARCHAR2
)
IS
--
-- Created By : gmuralid
-- Created On : 24-JUN-2003
-- Purpose : This procedure is the main procedure invoked via concurrent program to import legacy data.
-- Known limitations, enhancements or remarks :
-- Change History :
-- Who When What
-- tsailaja 15/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
SELECT batch_desc
FROM igf_ap_li_bat_ints
WHERE batch_num = cp_batch_num;
SELECT alternate_code
FROM igs_ca_inst
WHERE cal_type = cp_cal_type
AND sequence_number = cp_seq_number;
SELECT batch_year,
award_year_status_code,
sys_award_year
FROM igf_ap_batch_aw_map_v
WHERE ci_cal_type = cp_cal_type
AND ci_sequence_number = cp_seq_number;
SELECT 1
FROM igf_ap_isir_matched isir
WHERE isir.base_id = cp_base_id
AND isir.active_isir = 'Y';
SELECT
rowid row_id,
loan_id,
award_id,
loan_number,
legacy_record_flag
FROM
igf_sl_loans_all
WHERE
award_id = cp_award_id;
SELECT
rowid row_id,
loan_id,
award_id,
loan_number,
legacy_record_flag
FROM
igf_sl_loans_all
WHERE
loan_number = cp_loan_number;
SELECT 1
FROM igf_aw_awd_disb_all adisb
WHERE adisb.award_id = cp_award_id
AND adisb.disb_num = cp_disb_num;
log_parameters( l_get_alternate_code.alternate_code,p_batch_id,igf_ap_gen.get_lookup_meaning('IGF_AP_YES_NO',p_delete_flag));
delete_records(l_chk_loan_exist.row_id,
l_chk_loan_exist.loan_id,
l_chk_loan_exist.loan_number);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.run.debug','Record to be updated deleted successfully');
insert_records(l_interface,
l_award_id,
l_fed_fund_cd,
lv_person_id);
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.run.debug','Before update of interface table : status E');
UPDATE
igf_sl_li_orig_ints
SET
import_status_type = 'E',
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id,
request_id = fnd_global.conc_request_id,
program_id = fnd_global.conc_program_id,
program_application_id = fnd_global.prog_appl_id,
program_update_date = SYSDATE
WHERE
ROWID = l_interface.ROWID;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.run.debug','After update of interface table : status E');
IF p_delete_flag = 'Y' THEN
IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.run.debug','Before deleting disb interface table record');
DELETE
FROM
igf_sl_li_org_disb_ints
WHERE
ci_alternate_code = l_interface.ci_alternate_code AND
person_number = l_interface.person_number AND
award_number_txt = l_interface.award_number_txt;
DELETE
FROM
igf_sl_li_orig_ints
WHERE
ROWID = l_interface.ROWID;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.run.debug','Before update of interface table : status I');
UPDATE
igf_sl_li_orig_ints
SET
import_status_type = 'I',
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id,
request_id = fnd_global.conc_request_id,
program_id = fnd_global.conc_program_id,
program_application_id = fnd_global.prog_appl_id,
program_update_date = SYSDATE
WHERE
ROWID = l_interface.ROWID;
fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_imp_pkg.run.debug','After update of interface table : status I');