[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rowid,
batch_num batch_num ,
TRIM(ci_alternate_code) ci_alternate_code ,
TRIM(person_number) person_number ,
TRIM(award_number_txt) award_number_txt ,
TRIM(loan_number_txt) loan_number_txt,
TRIM(import_status_type) import_status_type ,
loan_seq_num loan_seq_num ,
TRUNC(loan_per_begin_date) loan_per_begin_date ,
TRUNC(loan_per_end_date) loan_per_end_date ,
TRIM(loan_status_code) loan_status_code ,
TRUNC(loan_status_date) loan_status_date ,
TRIM(active_flag) active_flag ,
TRUNC(active_date) active_date ,
TRIM(borr_person_number) borr_person_number ,
TRIM(grade_level_code) grade_level_code ,
TRUNC(orig_acknowledgement_date) orig_acknowledgement_date ,
TRUNC(orig_batch_date) orig_batch_date ,
TRIM(orig_send_batch_id_txt) orig_send_batch_id_txt ,
TRIM(pnote_status_code) pnote_status_code ,
TRIM(pnote_batch_seq_num_txt) pnote_batch_seq_num_txt ,
TRIM(pnote_id_txt) pnote_id_txt ,
TRIM(pnote_print_ind_code) pnote_print_ind_code ,
pnote_accept_amt pnote_accept_amt ,
TRUNC(pnote_accept_date) pnote_accept_date ,
TRIM(unsub_elig_for_depnt_code) unsub_elig_for_depnt_code ,
TRIM(unsub_elig_for_heal_code) unsub_elig_for_heal_code ,
TRIM(loan_chg_status) loan_chg_status ,
TRUNC(loan_chg_status_date) loan_chg_status_date ,
TRIM(pnote_status_type) pnote_status_type ,
TRIM(pnote_indicator_code) pnote_indicator_code ,
TRUNC(mpn_acknowledgement_date) mpn_acknowledgement_date ,
TRIM(mpn_reject_code) mpn_reject_code ,
orig_fee_perct_num orig_fee_perct_num ,
TRUNC(credit_decision_date) credit_decision_date ,
TRIM(credit_override_code) credit_override_code ,
endorser_amount endorser_amount,
TRIM(cr_desc_batch_id_txt) cr_desc_batch_id_txt,
TRIM(orig_reject_code) orig_reject_code ,
TRIM(disclosure_print_ind_code) disclosure_print_ind_code ,
TRIM(s_default_status_code) s_default_status_code ,
TRUNC(sch_cert_date) sch_cert_date ,
TRIM(p_default_status_code) p_default_status_code ,
loan_approved_amt loan_approved_amt ,
TRIM(import_record_type) import_record_type,
transaction_num transaction_num,
TRIM(atd_entity_id_txt) atd_entity_id_txt,
TRIM(rep_entity_id_txt) rep_entity_id_txt,
credit_status credit_status
FROM igf_sl_li_dlor_ints dlint
WHERE dlint.batch_num = cp_batch_id
AND dlint.ci_alternate_code = cp_alternate_code
AND (dlint.import_status_type = p_import_status_type_1 OR dlint.import_status_type = p_import_status_type_2);
SELECT rowid,
TRIM(ci_alternate_code) ci_alternate_code ,
TRIM(person_number) person_number ,
TRIM(award_number_txt) award_number_txt,
disbursement_num disbursement_num ,
disbursement_seq_num disbursement_seq_num,
TRIM(loan_number_txt) loan_number_txt,
TRUNC(disbursement_date) disbursement_date,
gross_disbursement_amt gross_disbursement_amt,
TRIM(booking_batch_id_txt) booking_batch_id_txt,
TRUNC(booked_date) booked_date,
TRIM(disbursement_batch_id_txt) disbursement_batch_id_txt,
TRIM(disbursement_activity_code) disbursement_activity_code ,
TRIM(disbursement_activity_st_txt) disbursement_activity_st_txt,
loc_disbursement_gross_amt loc_disbursement_gross_amt,
loc_fee_1_amt loc_fee_1_amt ,
loc_disbursement_net_amt loc_disbursement_net_amt,
servicer_refund_amt servicer_refund_amt ,
loc_int_rebate_amt loc_int_rebate_amt ,
loc_net_booked_loan_amt loc_net_booked_loan_amt ,
TRUNC(acknowledgement_date) acknowledgement_date,
TRIM(school_code_txt) school_code_txt,
TRIM(confirmation_flag) confirmation_flag ,
interest_rebate_amt interest_rebate_amt ,
TRIM(user_identifier_txt) user_identifier_txt,
TRUNC(disbursement_activity_date) disbursement_activity_date
FROM igf_sl_li_dldb_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
ORDER BY disbursement_num ,disbursement_seq_num ;
SELECT TRIM(loan_number_txt) loan_number_txt,
TRIM(change_code) change_code ,
TRIM(send_batch_id_txt) send_batch_id_txt,
TRIM(resp_batch_id_txt) resp_batch_id_txt,
TRIM(reject_code) reject_code,
TRIM(new_value_txt) new_value_txt,
TRIM(loan_ident_err_code) loan_ident_err_code
FROM igf_sl_li_chg_ints slchg
WHERE slchg.loan_number_txt = p_loan_number ;
p_delete_flag IN VARCHAR2)
IS
/*
|| Created By : rasahoo
|| Created On : 07-July-2003
|| Purpose : Logs all the Input Parameters
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
-- cursor to get batch desc for the batch id from igf_ap_li_bat_ints
CURSOR c_batch_desc(cp_batch_num igf_aw_li_coa_ints.batch_num%TYPE ) IS
SELECT batch_desc, batch_type
FROM igf_ap_li_bat_ints
WHERE batch_num = cp_batch_num ;
l_delete_flag_prmpt VARCHAR2(80);
l_delete_flag_prmpt := fnd_message.get ;
l_yes_no := igf_ap_gen.get_lookup_meaning('IGF_AP_YES_NO',p_delete_flag);
FND_FILE.PUT_LINE( FND_FILE.LOG, RPAD( l_delete_flag_prmpt, 40) || ' : '|| l_yes_no ) ;
SELECT 'X'
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 (END_DATE, SYSDATE)
AND HZ.STATUS = 'A'
AND OLI.ORG_ALTERNATE_ID = l_value
AND system_id_type = 'DL_SCH_CD';
SELECT disb_gross_amt
FROM igf_db_awd_disb_dtl_all
WHERE award_id = cp_award_id
AND disb_num = cp_disb_num
AND disb_seq_num = cp_disb_seq_num;
p_igf_sl_msg_table.DELETE;
SELECT accepted_amt
FROM igf_aw_award
WHERE award_id = p_award_id;
p_igf_sl_msg_table.DELETE;
PROCEDURE loans_insert_row(p_interface IN c_interface%ROWTYPE,
p_award_id IN NUMBER)
AS
/*
|| Created By : rasahoo
|| Created On : 03-June-2003
|| Purpose : Inserts legacy data into loans Table .
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
ln_rowid ROWID;
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 => p_interface.loan_chg_status,
x_loan_chg_status_date => p_interface.loan_chg_status_date,
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 => NULL
);
fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.LOANS_INSERT_ROW');
END loans_insert_row;
PROCEDURE loans_orig_insert_row(p_interface IN c_interface%ROWTYPE)
AS
/*
|| Created By : rasahoo
|| Created On : 03-June-2003
|| Purpose : Inserts legacy data into loans origination Table .
|| Change History :
|| Who When What
-----------------------------------------------------------------------------------
bkkumar 06-oct-2003 Bug 3104228 FA 122 Loans Enhancements
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.
-----------------------------------------------------------------------------------
|| 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
|| (reverse chronological order - newest change first)
*/
ln_rowid ROWID;
igf_sl_lor_pkg.insert_row (
x_mode => 'R',
x_rowid => ln_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 => l_orig_status,
X_orig_batch_id => p_interface.orig_send_batch_id_txt,
X_orig_batch_date => p_interface.orig_batch_date,
X_chg_batch_id => NULL,
X_orig_ack_date => p_interface.orig_acknowledgement_date,
X_credit_override => p_interface.credit_override_code,
X_credit_decision_date => p_interface.credit_decision_date,
X_req_serial_loan_code => NULL,
X_act_serial_loan_code => NULL,
X_pnote_delivery_code => NULL,
X_pnote_status => p_interface.pnote_status_code,
x_pnote_status_date => p_interface.pnote_accept_date,
x_pnote_id => p_interface.pnote_id_txt,
x_pnote_print_ind => p_interface.pnote_print_ind_code,
x_pnote_accept_amt => p_interface.pnote_accept_amt,
X_pnote_accept_date => p_interface.pnote_accept_date,
X_unsub_elig_for_heal => p_interface.unsub_elig_for_heal_code,
x_disclosure_print_ind => p_interface.disclosure_print_ind_code,
x_orig_fee_perct => p_interface.orig_fee_perct_num,
x_borw_confirm_ind => NULL,
X_borw_interest_ind => NULL,
X_borw_outstd_loan_code => NULL,
X_unsub_elig_for_depnt => p_interface.unsub_elig_for_depnt_code,
X_guarantee_amt => NULL,
X_guarantee_date => NULL,
X_guarnt_amt_redn_code => NULL,
X_guarnt_status_code => NULL,
X_guarnt_status_date => NULL,
X_lend_apprv_denied_code => NULL,
X_lend_apprv_denied_date => NULL,
X_lend_status_code => NULL,
X_lend_status_date => NULL,
X_guarnt_adj_ind => NULL,
X_grade_level_code => p_interface.grade_level_code,
X_enrollment_code => NULL,
X_anticip_compl_date => NULL,
X_borw_lender_id => NULL,
X_duns_borw_lender_id => NULL,
X_guarantor_id => NULL,
X_duns_guarnt_id => NULL,
X_prc_type_code => NULL,
X_cl_seq_number => NULL,
X_last_resort_lender => NULL,
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 => NULL,
X_cl_loan_type => NULL,
X_cl_rec_status => NULL,
X_cl_rec_status_last_update => NULL,
X_alt_prog_type_code => NULL,
X_alt_appl_ver_code => NULL,
X_mpn_confirm_code => NULL,
X_resp_to_orig_code => NULL,
X_appl_loan_phase_code => NULL,
X_appl_loan_phase_code_chg => NULL,
X_appl_send_error_codes => NULL,
X_tot_outstd_stafford => NULL,
X_tot_outstd_plus => NULL,
X_alt_borw_tot_debt => NULL,
X_act_interest_rate => NULL,
X_service_type_code => NULL,
X_rev_notice_of_guarnt => NULL,
X_sch_refund_amt => NULL,
X_sch_refund_date => NULL,
X_uniq_layout_vend_code => NULL,
X_uniq_layout_ident_code => NULL,
X_p_person_id => l_b_person_id,
X_p_ssn_chg_date => NULL,
X_p_dob_chg_date => NULL,
X_p_permt_addr_chg_date => NULL,
X_p_default_status => p_interface.p_default_status_code,
X_p_signature_code => NULL,
X_p_signature_date => NULL,
X_s_ssn_chg_date => NULL,
X_s_dob_chg_date => NULL,
X_s_permt_addr_chg_date => NULL,
X_s_local_addr_chg_date => NULL,
X_s_default_status => p_interface.s_default_status_code,
X_s_signature_code => NULL,
X_pnote_batch_id => p_interface.pnote_batch_seq_num_txt ,
X_pnote_ack_date => p_interface.mpn_acknowledgement_date,
X_pnote_mpn_ind => p_interface.pnote_indicator_code,
X_elec_mpn_ind => p_interface.pnote_status_type,
X_borr_sign_ind => NULL,
X_stud_sign_ind => NULL,
X_borr_credit_auth_code => NULL,
x_relationship_cd => NULL,
x_interest_rebate_percent_num => NULL,
x_cps_trans_num => p_interface.transaction_num,
x_atd_entity_id_txt => p_interface.atd_entity_id_txt,
x_rep_entity_id_txt => p_interface.rep_entity_id_txt,
x_crdt_decision_status => p_interface.credit_status,
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 => NULL,
x_alt_approved_amt => NULL,
x_flp_approved_amt => NULL,
x_fls_approved_amt => NULL,
x_flu_approved_amt => NULL,
x_guarantor_use_txt => NULL,
x_lender_use_txt => NULL,
x_loan_app_form_code => NULL,
x_reinstatement_amt => NULL,
x_requested_loan_amt => NULL,
x_school_use_txt => NULL,
x_deferment_request_code => NULL,
x_eft_authorization_code => NULL,
x_actual_record_type_code => NULL,
x_override_grade_level_code => NULL,
x_b_alien_reg_num_txt => NULL,
x_esign_src_typ_cd => NULL,
x_acad_begin_date => NULL,
x_acad_end_date => NULL);
fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.LOANS_ORIG_INSERT_ROW');
END loans_orig_insert_row;
PROCEDURE loans_orig_loc_insert_row(p_interface IN c_interface%ROWTYPE,
p_award_id IN NUMBER,
p_base_id IN NUMBER,
p_fed_fund IN VARCHAR2)
AS
/*
|| Created By : rasahoo
|| Created On : 03-June-2003
|| Purpose : Inserts legacy data into loans origination loc Table .
|| Change History :
|| Who When What
-----------------------------------------------------------------------------------
pssahni 28-Oct-2004 Bug 3416863 FA149 COD-XML
Added columns x_award_id, x_base_id, x_document_id_txt,
x_loan_key_num, x_interest_rebate_percent_num, x_fin_award_year,
x_cps_trans_num, x_atd_entity_id_txt, x_rep_entity_id_txt,
x_source_entity_id_txt, x_pymt_servicer_amt, x_pymt_servicer_date,
x_book_loan_amt, x_book_loan_amt_date, x_s_chg_birth_date,
x_s_chg_ssn, x_s_chg_last_name, x_b_chg_birth_date, x_b_chg_ssn,
x_b_chg_last_name, x_note_message, x_full_resp_code, x_s_permt_county,
x_b_permt_county, x_s_permt_country, x_b_permt_country, x_crdt_decision_status
-----------------------------------------------------------------------------------
bkkumar 06-oct-2003 Bug 3104228 FA 122 Loans Enhancements
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 from igf_sl_lor _loc
|| veramach 16-SEP-2003 FA 122 loan enhancements
|| 1. c_loan_dtls does not select borrower information from igf_sl_lor_dtls_v
|| 2. igf_sl_gen.get_person_details is now used to get borrower information
|| (reverse chronological order - newest change first)
*/
CURSOR c_award_amt IS
SELECT offered_amt,
accepted_amt
FROM igf_aw_award_all
WHERE award_id = p_award_id;
SELECT loans.row_id,
loans.loan_id,
lor.s_default_status,
lor.p_default_status,
lor.p_person_id,
fabase.person_id student_id
FROM igf_sl_loans loans,
igf_sl_lor 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.base_id = fabase.base_id
AND isir.payment_isir = 'Y'
AND isir.system_record_type = 'ORIGINAL'
AND fabase.person_id = cp_person_id;
igf_sl_lor_loc_pkg.insert_row (
x_mode => 'R',
x_rowid => ln_row_id,
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_award_amt.offered_amt ,
x_loan_amt_accepted => l_award_amt.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,
x_acad_yr_end_date => NULL,
x_loan_status => p_interface.loan_status_code,
x_loan_status_date => p_interface.loan_status_date,
x_loan_chg_status => p_interface.loan_chg_status,
x_loan_chg_status_date => p_interface.loan_chg_status_date,
x_req_serial_loan_code => NULL,
x_act_serial_loan_code => NULL,
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,
x_orig_batch_id => p_interface.orig_send_batch_id_txt,
x_orig_batch_date => p_interface.orig_batch_date,
x_chg_batch_id => NULL,
x_orig_ack_date => p_interface.orig_acknowledgement_date,
x_credit_override => p_interface.credit_override_code,
x_credit_decision_date => p_interface.credit_decision_date,
x_pnote_delivery_code => NULL,
x_pnote_status => p_interface.pnote_status_code,
x_pnote_status_date => NULL,
x_pnote_id => p_interface.pnote_id_txt,
x_pnote_print_ind => p_interface.pnote_print_ind_code,
x_pnote_accept_amt => p_interface.pnote_accept_amt,
x_pnote_accept_date => p_interface.pnote_accept_date ,
x_p_signature_code => NULL,
x_p_signature_date => NULL,
x_s_signature_code => NULL,
x_unsub_elig_for_heal => p_interface.unsub_elig_for_heal_code,
x_disclosure_print_ind => p_interface.disclosure_print_ind_code,
x_orig_fee_perct => p_interface.orig_fee_perct_num,
x_borw_confirm_ind => NULL,
x_borw_interest_ind => NULL,
x_unsub_elig_for_depnt => p_interface.unsub_elig_for_depnt_code,
x_guarantee_amt => NULL,
x_guarantee_date => NULL,
x_guarnt_adj_ind => NULL,
x_guarnt_amt_redn_code => NULL,
x_guarnt_status_code => NULL,
x_guarnt_status_date => NULL,
x_lend_apprv_denied_code => NULL,
x_lend_apprv_denied_date => NULL,
x_lend_status_code => NULL,
x_lend_status_date => NULL,
x_grade_level_code => p_interface.grade_level_code,
x_enrollment_code => NULL,
x_anticip_compl_date => NULL,
x_borw_lender_id => NULL,
x_duns_borw_lender_id => NULL,
x_guarantor_id => NULL,
x_duns_guarnt_id => NULL,
x_prc_type_code => NULL,
x_rec_type_ind => NULL,
x_cl_loan_type => NULL,
x_cl_seq_number => NULL,
x_last_resort_lender => NULL,
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_cl_rec_status => NULL,
x_cl_rec_status_last_update => NULL,
x_alt_prog_type_code => NULL,
x_alt_appl_ver_code => NULL,
x_borw_outstd_loan_code => NULL,
x_mpn_confirm_code => NULL,
x_resp_to_orig_code => NULL,
x_appl_loan_phase_code => NULL,
x_appl_loan_phase_code_chg => NULL,
x_tot_outstd_stafford => NULL,
x_tot_outstd_plus => NULL,
x_alt_borw_tot_debt => NULL,
x_act_interest_rate => NULL,
x_service_type_code => NULL,
x_rev_notice_of_guarnt => NULL,
x_sch_refund_amt => NULL,
x_sch_refund_date => NULL,
x_uniq_layout_vend_code => NULL,
x_uniq_layout_ident_code => NULL,
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,
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,
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,
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 => NULL,
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,
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,
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,
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,
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_code,
x_s_citizenship_status => lv_s_citizenship_status,
x_s_alien_reg_num => lv_alien_reg_num,
x_s_foreign_postal_code => NULL,
x_pnote_batch_id => p_interface.pnote_batch_seq_num_txt,
x_pnote_ack_date => p_interface.mpn_acknowledgement_date,
x_pnote_mpn_ind => p_interface.pnote_indicator_code,
x_award_id => p_award_id,
x_base_id => p_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 => p_interface.transaction_num,
x_atd_entity_id_txt => p_interface.atd_entity_id_txt,
x_rep_entity_id_txt => p_interface.rep_entity_id_txt,
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,
x_external_loan_id_txt => NULL,
x_alt_approved_amt => NULL,
x_flp_approved_amt => NULL,
x_fls_approved_amt => NULL,
x_flu_approved_amt => NULL,
x_guarantor_use_txt => NULL,
x_lender_use_txt => NULL,
x_loan_app_form_code => NULL,
x_reinstatement_amt => NULL,
x_requested_loan_amt => NULL,
x_school_use_txt => NULL,
x_deferment_request_code => NULL,
x_eft_authorization_code => NULL,
x_actual_record_type_code => NULL,
x_alt_borrower_ind_flag => NULL,
x_borower_credit_authoriz_flag => NULL,
x_borower_electronic_sign_flag => NULL,
x_cost_of_attendance_amt => NULL,
x_established_fin_aid_amount => NULL,
x_expect_family_contribute_amt => NULL,
x_mpn_type_flag => p_interface.pnote_status_type,
x_school_id_txt => NULL,
x_student_electronic_sign_flag => NULL,
x_esign_src_typ_cd => NULL);
fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.LOANS_ORIG_LOC_INSERT_ROW');
END loans_orig_loc_insert_row;
PROCEDURE lor_resp_insert_row(p_interface IN c_interface%ROWTYPE)
AS
/*
|| Created By : rasahoo
|| Created On : 03-June-2003
|| Purpose : Inserts legacy data into loans origination response Table .
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
ln_rowid ROWID;
igf_sl_dl_lor_resp_pkg.insert_row (
x_mode => 'R',
x_rowid => ln_rowid,
x_lor_resp_num => ln_lor_resp_num,
x_dbth_id => ln_dbth_id,
x_orig_batch_id => p_interface.orig_send_batch_id_txt,
x_loan_number => p_interface.loan_number_txt ,
x_orig_ack_date => p_interface.orig_acknowledgement_date ,
x_orig_status_flag => p_interface.loan_status_code ,
x_orig_reject_reasons => p_interface.orig_reject_code,
x_pnote_status => p_interface.pnote_status_code,
x_pnote_id => p_interface.pnote_id_txt,
x_pnote_accept_amt => p_interface.pnote_accept_amt,
x_loan_amount_accepted => p_interface.loan_approved_amt,
x_status => 'Y',
x_elec_mpn_ind => p_interface.pnote_status_type
);
fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.LOR_RESP_INSERT_ROW');
END lor_resp_insert_row;
PROCEDURE lor_crresp_insert_row(p_interface IN c_interface%ROWTYPE)
AS
/*
|| Created By : rasahoo
|| Created On : 03-June-2003
|| Purpose : Inserts legacy data into igf_sl_dl_lor_crresp Table .
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
ln_rowid ROWID;
igf_sl_dl_lor_crresp_pkg.insert_row (
x_mode => 'R',
x_rowid => ln_rowid,
X_lor_resp_num => ln_lor_resp_num,
X_dbth_id => ln_dbth_id,
X_loan_number => p_interface.loan_number_txt,
X_credit_override => p_interface.credit_override_code,
X_credit_decision_date => p_interface.credit_decision_date,
X_status => 'Y' ,
x_endorser_amount => p_interface.pnote_accept_amt,
x_mpn_status => p_interface.pnote_status_code,
x_mpn_id => p_interface.pnote_id_txt,
x_mpn_type => p_interface.pnote_status_type,
x_mpn_indicator => p_interface.pnote_indicator_code
);
fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.LOR_CRRESP_INSERT_ROW');
END lor_crresp_insert_row;
PROCEDURE pnote_resp_insert_row (p_interface IN c_interface%ROWTYPE)
AS
/*
|| Created By : rasahoo
|| Created On : 03-June-2003
|| Purpose : Inserts legacy data into igf_sl_dl_pnote_resp Table .
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
ln_rowid ROWID;
igf_sl_dl_pnote_resp_pkg.insert_row (
x_rowid => ln_rowid,
x_dlpnr_id => ln_dlpnr_id,
x_dbth_id => ln_dbth_id,
x_pnote_ack_date => p_interface.pnote_accept_date,
x_pnote_batch_id => p_interface.pnote_id_txt,
x_loan_number => p_interface.loan_number_txt,
x_pnote_status => p_interface.pnote_status_code,
x_pnote_rej_codes => p_interface.orig_reject_code,
x_mpn_ind => p_interface.pnote_indicator_code,
x_pnote_accept_amt => p_interface.pnote_accept_amt,
x_status => 'Y',
x_mode => 'R',
x_elec_mpn_ind => p_interface.pnote_status_type);
fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.PNOTE_RESP_INSERT_ROW');
END pnote_resp_insert_row;
PROCEDURE pnote_insert_row(p_interface IN c_interface%ROWTYPE,
p_award_id IN NUMBER)
AS
/*
|| Created By : rasahoo
|| Created On : 03-June-2003
|| Purpose : Inserts legacy data into igf_sl_dl_pnote_p_p_all Table .
|| Change History :
|| Who When What
|| veramach 16-SEP-2003 FA 122 loan enhancements
|| 1. c_loan_dtls does not select borrower information from igf_sl_lor_dtls_v
|| 2. igf_sl_gen.get_person_details is now used to get borrower information
|| (reverse chronological order - newest change first)
*/
CURSOR c_award_amt IS
SELECT offered_amt,
accepted_amt
FROM igf_aw_award_all
WHERE award_id = p_award_id;
SELECT loans.row_id,
loans.loan_id,
lor.s_default_status,
lor.p_default_status,
lor.p_person_id,
fabase.person_id student_id
FROM igf_sl_loans loans,
igf_sl_lor 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.base_id = fabase.base_id
AND isir.payment_isir = 'Y'
AND isir.system_record_type = 'ORIGINAL'
AND fabase.person_id = cp_person_id;
igf_sl_dl_pnote_p_p_pkg.insert_row(
x_mode => 'R',
x_rowid => ln_rowid,
x_pnpp_id => ln_pnpp_id,
x_batch_seq_num => ln_dbth_id ,
x_loan_id => ln_loan_id,
x_loan_number => p_interface.loan_number_txt ,
x_loan_amt_offered => l_award_amt.offered_amt,
x_loan_amt_accepted => l_award_amt.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_person_id => loan_rec.student_id,
x_s_ssn => SUBSTR(student_dtl_rec.p_ssn,1,9),
x_s_first_name => student_dtl_rec.p_first_name,
x_s_last_name => student_dtl_rec.p_last_name,
x_s_middle_name => student_dtl_rec.p_middle_name,
x_s_date_of_birth => student_dtl_rec.p_date_of_birth,
x_s_citizenship_status => loan_rec.s_default_status,
x_s_alien_reg_number => lv_alien_reg_num,
x_s_license_num => lv_s_license_number,
x_s_license_state => lv_s_license_state,
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_province => NULL,
x_s_permt_county => NULL,
x_s_permt_country => NULL,
x_s_permt_zip => student_dtl_rec.p_permt_zip,
x_s_email_addr => student_dtl_rec.p_email_addr,
x_s_phone => lv_s_permt_phone,
x_p_person_id => loan_rec.p_person_id,
x_p_ssn => SUBSTR(parent_dtl_rec.p_ssn,1,9),
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_date_of_birth => parent_dtl_rec.p_date_of_birth,
x_p_citizenship_status => parent_dtl_rec.p_citizenship_status,
x_p_alien_reg_num => parent_dtl_rec.p_alien_reg_num,
x_p_license_num => parent_dtl_rec.p_license_num,
x_p_license_state => parent_dtl_rec.p_license_state,
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_province => NULL,
x_p_permt_county => NULL,
x_p_permt_country => NULL,
x_p_permt_zip => parent_dtl_rec.p_permt_zip,
x_p_email_addr => parent_dtl_rec.p_email_addr,
x_p_phone => lv_p_permt_phone,
x_status => 'Y'
);
fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.PNOTE_INSERT_ROW');
END pnote_insert_row;
PROCEDURE disb_resp_insert_row(p_disb_interface c_disb_interface%ROWTYPE)
AS
/*
|| Created By : rasahoo
|| Created On : 03-June-2003
|| Purpose : Inserts legacy data into igf_db_dl_disb_resp_all Table .
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
ln_rowid ROWID;
igf_db_dl_disb_resp_pkg.insert_row (
x_mode => 'R',
x_rowid => ln_rowid,
x_ddrp_id => ln_ddrp_id,
x_dbth_id => ln_dbth_id,
x_loan_number => p_disb_interface.loan_number_txt,
x_disb_num => p_disb_interface.disbursement_num,
x_disb_activity => p_disb_interface.disbursement_activity_code,
x_transaction_date => p_disb_interface.disbursement_date,
x_disb_seq_num => p_disb_interface.disbursement_seq_num ,
x_disb_gross_amt => p_disb_interface.gross_disbursement_amt,
x_fee_1 => p_disb_interface.loc_fee_1_amt,
x_disb_net_amt => p_disb_interface.gross_disbursement_amt - p_disb_interface.loc_fee_1_amt + p_disb_interface.loc_int_rebate_amt,
x_int_rebate_amt => p_disb_interface.loc_int_rebate_amt,
x_user_ident => p_disb_interface.user_identifier_txt,
x_disb_batch_id => p_disb_interface.disbursement_batch_id_txt,
x_school_id => p_disb_interface.school_code_txt,
x_sch_code_status => NULL,
x_loan_num_status => NULL,
x_disb_num_status => NULL,
x_disb_activity_status => p_disb_interface.disbursement_activity_st_txt,
x_trans_date_status => NULL,
x_disb_seq_num_status => NULL,
x_loc_disb_gross_amt => p_disb_interface.loc_disbursement_gross_amt,
x_loc_fee_1 => p_disb_interface.loc_fee_1_amt,
x_loc_disb_net_amt => p_disb_interface.loc_disbursement_net_amt,
x_servicer_refund_amt => p_disb_interface.servicer_refund_amt,
x_loc_int_rebate_amt => p_disb_interface.loc_int_rebate_amt,
x_loc_net_booked_loan => p_disb_interface.loc_net_booked_loan_amt,
x_ack_date => p_disb_interface.acknowledgement_date,
x_affirm_flag => p_disb_interface.confirmation_flag,
x_status => 'N'
);
fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.DISB_RESP_INSERT_ROW');
END disb_resp_insert_row;
PROCEDURE db_awd_disb_update_row(l_disb_interface IN c_disb_interface%ROWTYPE,
p_award_id IN NUMBER)
AS
/*
|| Created By : rasahoo
|| Created On : 03-June-2003
|| Purpose : Updates legacy data into igf_db_awd_disb_dtl Table .
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
ln_rowid ROWID;
SELECT ROWID,adisb.*
FROM IGF_DB_AWD_DISB_DTL_ALL adisb
WHERE adisb.award_id = p_award_id
AND adisb.disb_num = cp_disb_num
AND adisb.disb_seq_num = cp_disbursement_seq_num;
igf_db_awd_disb_dtl_pkg.update_row( x_rowid => l_rec_disb_dtl.rowid,
x_award_id => l_rec_disb_dtl.award_id,
x_disb_num => l_rec_disb_dtl.disb_num,
x_disb_seq_num => l_rec_disb_dtl.disb_seq_num,
x_disb_gross_amt => l_disb_interface.gross_disbursement_amt,
x_fee_1 => l_disb_interface.loc_fee_1_amt,
x_fee_2 => l_rec_disb_dtl.fee_2,
x_disb_net_amt => l_disb_interface.loc_disbursement_net_amt,
x_disb_adj_amt => l_rec_disb_dtl.disb_adj_amt,
x_disb_date => l_disb_interface.disbursement_date,
x_fee_paid_1 => l_rec_disb_dtl.fee_paid_1,
x_fee_paid_2 => l_rec_disb_dtl.fee_paid_2,
x_disb_activity => l_disb_interface.disbursement_activity_code,
x_disb_batch_id => l_disb_interface.disbursement_batch_id_txt,
x_disb_ack_date => l_disb_interface.acknowledgement_date,
x_booking_batch_id => l_disb_interface.booking_batch_id_txt,
x_booked_date => l_disb_interface.booked_date,
x_disb_status => l_disb_status,
x_disb_status_date => l_disb_interface.disbursement_activity_date,
x_sf_status => l_rec_disb_dtl.sf_status,
x_sf_status_date => l_rec_disb_dtl.sf_status_date,
x_sf_invoice_num => l_rec_disb_dtl.sf_invoice_num,
x_sf_credit_id => l_rec_disb_dtl.sf_credit_id,
x_spnsr_credit_id => l_rec_disb_dtl.spnsr_credit_id,
x_spnsr_charge_id => l_rec_disb_dtl.spnsr_charge_id,
x_error_desc => l_rec_disb_dtl.error_desc,
x_mode => 'R' ,
x_notification_date => l_rec_disb_dtl.notification_date,
x_interest_rebate_amt => l_rec_disb_dtl.interest_rebate_amt,
x_ld_cal_type => l_rec_disb_dtl.ld_cal_type,
x_ld_sequence_number => l_rec_disb_dtl.ld_sequence_number
);
fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.DB_AWD_DISB_UPDATE_ROW');
END db_awd_disb_update_row;
PROCEDURE dl_chg_send_insert_row (p_chg_interface c_chg_interface%ROWTYPE )
AS
/*
|| Created By : rasahoo
|| Created On : 03-June-2003
|| Purpose : Inserts legacy data into igf_sl_dl_chg_send Table .
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
ln_rowid ROWID;
igf_sl_dl_chg_send_pkg.insert_row (
x_mode => 'R',
x_rowid => ln_rowid,
X_chg_num => ln_chg_num,
X_dbth_id => ln_dbth_id,
X_loan_number => p_chg_interface.LOAN_NUMBER_TXT,
X_chg_code => p_chg_interface.CHANGE_CODE,
X_new_value => p_chg_interface.NEW_VALUE_TXT,
X_status => 'S'
);
fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.DL_CHG_SEND_INSERT_ROW');
END dl_chg_send_insert_row;
PROCEDURE dl_chg_resp_insert_row (p_chg_interface c_chg_interface%ROWTYPE )
AS
/*
|| Created By : rasahoo
|| Created On : 03-June-2003
|| Purpose : Inserts legacy data into igf_sl_dl_chg_resp Table .
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
ln_rowid ROWID;
igf_sl_dl_chg_resp_pkg.insert_row (
x_mode => 'R',
x_rowid => ln_rowid,
X_resp_num => ln_resp_num,
X_dbth_id => ln_dbth_id,
X_batch_id => p_chg_interface.SEND_BATCH_ID_TXT,
X_loan_number => p_chg_interface.LOAN_NUMBER_TXT,
X_chg_code => p_chg_interface.CHANGE_CODE,
X_reject_code => p_chg_interface.REJECT_CODE,
X_new_value => p_chg_interface.NEW_VALUE_TXT,
X_loan_ident_err_code => p_chg_interface.LOAN_IDENT_ERR_CODE,
X_status => 'N'
);
fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.DL_CHG_RESP_INSERT_ROW');
END dl_chg_resp_insert_row;
PROCEDURE dl_batch_insert_row(p_interface IN c_interface%ROWTYPE)
AS
/*
|| Created By : rasahoo
|| Created On : 03-June-2003
|| Purpose : Inserts legacy data into igf_sl_dl_batch Table .
|| Change History :
|| Who When What
|| bvisvana 24-Aug-2006 Bug 5478287 - Extending batch creation for 2006 and 2007
|| (reverse chronological order - newest change first)
*/
ln_rowid ROWID;
SELECT message_class
FROM igf_sl_dl_file_type
WHERE batch_type = p_batch_type
AND cycle_year = p_cycle_year
AND message_class LIKE p_message_class;
igf_sl_dl_batch_pkg.insert_row (
x_mode => 'R',
x_rowid => ln_rowid,
x_dbth_id => ln_dbth_id,
x_batch_id => p_interface.ORIG_SEND_BATCH_ID_TXT,
x_message_class => l_message_class.message_class,
x_bth_creation_date => TO_DATE(l_dt_btch_created,'YYYYMMDD'),
x_batch_rej_code => NULL,
x_end_date => NULL,
x_batch_type => l_batch_type,
x_send_resp => 'R',
x_status => 'N');
fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.DL_BATCH_INSERT_ROW');
END dl_batch_insert_row;
PROCEDURE delete_context_records(p_loan_id igf_sl_loans_all.loan_id%TYPE,
p_loan_num igf_sl_loans_all.loan_number%TYPE,
p_rowid ROWID)
AS
/*
|| Created By : rasahoo
|| Created On : 03-June-2003
|| Purpose :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
CURSOR c_lor(cp_loan_id NUMBER)
IS
SELECT rowid, origination_id
FROM igf_sl_lor_all
WHERE loan_id = cp_loan_id;
SELECT ROWID
FROM igf_sl_pnote_stat_h
WHERE loan_id = cp_loan_id;
SELECT ROWID
FROM igf_sl_dl_pnote_p_p_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_db_dl_disb_resp_all
WHERE loan_number = cp_loan_number;
SELECT rowid,dbth_id
FROM igf_sl_dl_lor_crresp_all
WHERE loan_number = cp_loan_number;
SELECT rowid,dbth_id
FROM igf_sl_dl_lor_resp_all
WHERE loan_number = cp_loan_number;
SELECT rowid,dbth_id,dlpnr_id
FROM igf_sl_dl_pnote_resp_all
WHERE loan_number = cp_loan_number;
SELECT ROWID
FROM igf_sl_dl_chg_send
WHERE loan_number = p_loan_number;
SELECT ROWID
FROM igf_sl_dl_chg_resp_all
WHERE loan_number = p_loan_number;
SELECT ROWID
FROM igf_sl_dl_pdet_resp
WHERE dlpnr_id = cp_dlpnr_id;
igf_sl_dl_pnote_p_p_pkg.delete_row(X_ROWID => l_pnote.rowid);
igf_sl_pnote_stat_h_pkg.delete_row(X_ROWID => l_pnote_hist.rowid);
igf_sl_lor_loc_pkg.delete_row(X_ROWID => l_lor_loc.rowid);
igf_sl_lor_pkg.delete_row(X_ROWID => l_lor.rowid);
igf_db_dl_disb_resp_pkg.delete_row(X_ROWID => l_disb_resp.rowid);
igf_sl_dl_lor_crresp_pkg.delete_row(X_ROWID => l_lor_crresp.rowid);
igf_sl_dl_lor_resp_pkg.delete_row(X_ROWID => l_lor_resp.rowid);
igf_sl_dl_pdet_resp_pkg.delete_row(l_pdet_resp.rowid);
igf_sl_dl_pnote_resp_pkg.delete_row(X_ROWID => l_pnote_resp.rowid);
igf_sl_dl_chg_send_pkg.delete_row(X_ROWID => l_dl_chg_send.rowid);
igf_sl_dl_chg_resp_pkg.delete_row(X_ROWID => l_dl_chg_resp.rowid);
igf_sl_loans_pkg.delete_row(X_ROWID => p_rowid);
fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_dl_li_imp_pkg.delete_context_records.exception','Exception:'||SQLERRM);
fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.DELETE_CONTEXT_RECORDS');
END delete_context_records;
PROCEDURE insert_context_records(l_interface c_interface%ROWTYPE,
l_award_id igf_aw_award_all.award_id%TYPE,
l_base_id NUMBER,
l_loan_num igf_sl_loans_all.loan_number%TYPE)
AS
/*
|| Created By : rasahoo
|| Created On : 03-June-2003
|| Purpose :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
l_chg_interface c_chg_interface%ROWTYPE;
loans_insert_row(l_interface,l_award_id);
loans_orig_insert_row(l_interface);
loans_orig_loc_insert_row(l_interface,l_award_id,l_base_id,lv_fed_fund_code);
dl_batch_insert_row(l_interface);
dl_chg_send_insert_row (l_chg_interface );
dl_chg_resp_insert_row (l_chg_interface);
pnote_resp_insert_row (l_interface);
lor_crresp_insert_row(l_interface);
lor_resp_insert_row(l_interface);
fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.INSERT_CONTEXT_RECORDS');
END insert_context_records;
PROCEDURE insert_context_disb_records( p_disb_interface c_disb_interface%ROWTYPE,
p_award_id igf_aw_award_all.award_id%TYPE
)
AS
/*
|| Created By : rasahoo
|| Created On : 03-June-2003
|| Purpose :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
BEGIN
IF p_disb_interface.acknowledgement_date IS NOT NULL THEN
-- insert into disbursement table
disb_resp_insert_row(p_disb_interface);
db_awd_disb_update_row(p_disb_interface ,p_award_id);
fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.INSERT_CONTEXT_DISB_RECORDS');
END insert_context_disb_records;
p_delete_flag IN VARCHAR2
)
IS
/*
|| Created By : RASAHOO
|| Created On : 07-July-2003
|| Purpose : This procedure is 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 alternate_code
FROM igs_ca_inst
WHERE cal_type = cp_ci_cal_type
AND sequence_number = cp_ci_sequence_number ;
SELECT batch_year
FROM igf_ap_batch_aw_map
WHERE ci_cal_type = cp_cal_type
AND ci_sequence_number = cp_seq_num;
SELECT batch_year,
award_year_status_code status,
sys_award_year
FROM igf_ap_batch_aw_map
WHERE ci_cal_type = cp_cal_type
AND ci_sequence_number = cp_seq_number;
SELECT awd.award_id,awd.award_status
FROM igf_aw_award_all awd
WHERE awd.base_id = cp_base_id
AND awd.award_number_txt = cp_award_number;
SELECT 1
FROM igf_ap_isir_matched isir
WHERE isir.base_id = cp_base_id
AND isir.active_isir = p_active;
SELECT
fc.fed_fund_code,
fc.sys_fund_type
FROM
igf_aw_award aw,
igf_aw_fund_mast fm,
igf_aw_fund_cat fc
WHERE
aw.award_id = cp_award_id and
fm.fund_id = aw.fund_id and
fc.fund_code = fm.fund_code;
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_db_awd_disb_dtl_all adisb
WHERE adisb.award_id = cp_award_id
AND adisb.disb_num = cp_disb_num
AND adisb.disb_seq_num = cp_disb_seq_num;
SELECT
rowid,
loan_id ,
loan_number,
legacy_record_flag
FROM igf_sl_loans_all
WHERE award_id = cp_award_id;
SELECT
rowid,
award_id
FROM igf_sl_loans_all
WHERE loan_number = cp_loan_num ;
SELECT rowid
FROM igf_sl_alt_borw_all
WHERE loan_id = cp_loan_id;
SELECT orig_fee_perct_stafford,
orig_fee_perct_plus
FROM igf_sl_dl_setup
WHERE ci_cal_type = p_ci_cal_type
AND ci_sequence_number = p_ci_sequence_number;
SELECT disb_num,
disb_seq_num
FROM igf_db_awd_disb_dtl_all adisb
WHERE adisb.award_id = cp_award_id
AND (disb_num,disb_seq_num) NOT IN
(
SELECT disbursement_num,disbursement_seq_num
FROM igf_sl_li_dldb_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 PARTY_ID
FROM hz_parties
WHERE party_number = cp_person_number and party_type = p_party_type;
SELECT atd.atd_entity_id_txt, rep.rep_entity_id_txt
FROM igf_gr_attend_pell atd, igf_gr_report_pell rep
WHERE atd.rcampus_id = rep.rcampus_id
AND atd.atd_entity_id_txt = p_atd_entity_id_txt
AND rep.rep_entity_id_txt = p_rep_entity_id_txt;
SELECT isir_id
FROM igf_ap_isir_matched_all
WHERE TO_NUMBER(transaction_num) = p_transaction_num
AND base_id = p_base_id;
log_input_params( p_batch_id, l_alternate_code , p_delete_flag);
delete_context_records(lv_loan_id,lv_loan_num,lv_rowid);
l_debug_str := l_debug_str || ' deleted all context records for import record type U ';
insert_context_records(l_interface,l_award_id,lv_base_id,lv_loan_num);
insert_context_disb_records(l_disb_interface,l_award_id);
l_debug_str := l_debug_str || ' inserted all context records for import record type U ';
ELSE --update flag check 'U'
OPEN c_chk_loan_exist(l_award_id);
insert_context_records(l_interface,l_award_id,lv_base_id,lv_loan_num);
insert_context_disb_records( l_disb_interface,l_award_id);
l_debug_str := l_debug_str || ' inserted all context records for import record type not equal U ';
l_debug_str := l_debug_str || 'Before update of interface table : status E ';
UPDATE igf_sl_li_dlor_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;
IF p_delete_flag = 'Y' THEN
DELETE
FROM igf_sl_li_chg_ints slchg
WHERE slchg.loan_number_txt = l_interface.loan_number_txt;
DELETE
FROM igf_sl_li_dldb_ints
WHERE ci_alternate_code = l_disb_interface.ci_alternate_code
AND person_number = l_disb_interface.person_number
AND award_number_txt = l_disb_interface.award_number_txt
AND loan_number_txt = l_disb_interface.loan_number_txt;
DELETE
FROM igf_sl_li_dlor_ints
WHERE ROWID = l_interface.ROWID;
l_debug_str := l_debug_str || ' Before update of interface table : status I ';
UPDATE igf_sl_li_dlor_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;
l_debug_str := l_debug_str || ' After update of interface table : status I ';