[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
veramach 23-SEP-2003 Bug 3104228: Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
chg_batch_id,appl_send_error_codes from igf_sl_lor
Purpose : Bug :- 2426609 SSN Format Incorrect in Output File
Who When What
mesriniv 21-jun-2002 While inserting Student SSN/Parent SSN
formatting and substr of 9 chars is done.
Know limitations, enhancements or remarks
Change History:
Who When What
adhawan 20-nov-2002 gscc fix removing the default
2669341
adhawan 20-feb-2002 added elec_mpn_ind,
2216956 borr_sign_ind,stud_sign_ind,
borr_credit_auth_code in the call to update row of igf_sl_lor tbh
changed the parameter in the process_manifest to receive base_id instead of student id and then
deriving the student id value
(reverse chronological order - newest change first)
*****************************************************************/
PROCEDURE process_manifest(
ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY NUMBER,
p_award_year IN VARCHAR2,
p_loan_catg IN igf_lookups_view.lookup_code%TYPE,
p_base_id IN igf_aw_award.base_id%TYPE, -- modified to receive base_id instead of student id
p_loan_number IN igf_sl_loans_v.loan_number%TYPE,
p_org_id IN NUMBER
)
AS
/*****************************************************************
Created By : rboddu
Date Created On : 2001/05/14
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
akomurav 28-Feb-2006 Build FA161 and FA162.
TBH Impact change done in igf_sl_lor_pkg.update_row().
veramach 23-SEP-2003 Bug 3104228: Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
chg_batch_id,appl_send_error_codes from igf_sl_lor
(reverse chronological order - newest change first)
*****************************************************************/
/* Local Variable Declaration */
l_ci_cal_type igf_sl_dl_setup.ci_cal_type%TYPE;
SELECT pnote_print_ind, ci_alternate_code
FROM igf_sl_dl_setup_v
WHERE ci_cal_type = l_cal
AND ci_sequence_number = l_seq;
SELECT lor.student_id,
lor.p_person_id,
lor.loan_id,
lor.loan_number,
per.api_person_id,
per.given_names,
per.surname,
per.middle_name
FROM igf_sl_lor_v lor,
igf_ap_person_v per
WHERE lor.pnote_status = 'S' /* Check for pnote_status 'Signed' */
AND lor.fed_fund_code IN (SELECT DISTINCT lookup_code
FROM igf_lookups_view
WHERE lookup_type = DECODE(p_loan_catg,'DL_STAFFORD','IGF_SL_DL_STAFFORD','DL_PLUS','IGF_SL_DL_PLUS')
)
AND lor.ci_cal_type = l_cal /* Check for given Award Year */
AND lor.ci_sequence_number = l_seq
AND lor.student_id = NVL(cp_person_id, lor.student_id)
AND lor.loan_number = NVL(p_loan_number, lor.loan_number)
AND per.person_id = DECODE(p_loan_catg,'DL_STAFFORD',lor.student_id,lor.p_person_id);
SELECT igf_sl_lor.*
FROM igf_sl_lor
WHERE loan_id = lor_loan_id FOR UPDATE OF igf_sl_lor.pnote_status NOWAIT;
select person_id from igf_ap_fa_base_rec
where base_id = p_base_id and person_id IS NOT NULL;
/* For each 'Signed' Loan record in the current Batch, insert corresponding Student or Parent information into Manifest table
Depending on whetner the Loan type is 'STAFFORD' or 'PLUS', respectively. This set of records is identified by a single Batch Sequence Number.*/
/* insert the student(parent) details into igf_sl_manifest table with status as N (Not manifested) */
FOR r_lor_signed IN c_lor_signed(l_ci_cal_type, l_ci_sequence_number,l_person_id)
LOOP
BEGIN
IF l_batch_seq_num IS NULL THEN
/* Get the unique sequence number to be assigned to the entire batch of records */
SELECT igf_sl_dl_pnote_bth_s.nextval INTO l_batch_seq_num FROM DUAL;
igf_sl_dl_manifest_pkg.insert_row(
x_rowid => l_row_id,
x_pnmn_id => l_id,
x_batch_seq_num => l_batch_seq_num,
x_loan_id => r_lor_signed.loan_id,
x_loan_number => r_lor_signed.loan_number,
x_b_ssn => SUBSTR(igf_ap_matching_process_pkg.remove_spl_chr(r_lor_signed.api_person_id),1,9),
x_b_first_name => r_lor_signed.given_names,
x_b_last_name => r_lor_signed.surname,
x_b_middle_name => r_lor_signed.middle_name,
x_status => 'N',
x_mode => 'R'
);
igf_sl_lor_pkg.update_row (
X_mode => 'R',
x_rowid => r_dl_lor_rec.row_id,
x_origination_id => r_dl_lor_rec.origination_id,
x_loan_id => r_dl_lor_rec.loan_id,
x_sch_cert_date => r_dl_lor_rec.sch_cert_date,
x_orig_status_flag => r_dl_lor_rec.orig_status_flag,
x_orig_batch_id => r_dl_lor_rec.orig_batch_id,
x_orig_batch_date => r_dl_lor_rec.orig_batch_date,
x_chg_batch_id => NULL,
x_orig_ack_date => r_dl_lor_rec.orig_ack_date,
x_credit_override => r_dl_lor_rec.credit_override,
x_credit_decision_date => r_dl_lor_rec.credit_decision_date,
x_req_serial_loan_code => r_dl_lor_rec.req_serial_loan_code,
x_act_serial_loan_code => r_dl_lor_rec.act_serial_loan_code,
x_pnote_delivery_code => r_dl_lor_rec.pnote_delivery_code,
x_pnote_status => 'P',
x_pnote_status_date => TRUNC(SYSDATE),
x_pnote_id => r_dl_lor_rec.pnote_id,
x_pnote_print_ind => r_dl_lor_rec.pnote_print_ind,
x_pnote_accept_amt => r_dl_lor_rec.pnote_accept_amt,
x_pnote_accept_date => r_dl_lor_rec.pnote_accept_date,
x_unsub_elig_for_heal => r_dl_lor_rec.unsub_elig_for_heal,
x_disclosure_print_ind => r_dl_lor_rec.disclosure_print_ind,
x_orig_fee_perct => r_dl_lor_rec.orig_fee_perct,
x_borw_confirm_ind => r_dl_lor_rec.borw_confirm_ind,
x_borw_interest_ind => r_dl_lor_rec.borw_interest_ind,
x_borw_outstd_loan_code => r_dl_lor_rec.borw_outstd_loan_code,
x_unsub_elig_for_depnt => r_dl_lor_rec.unsub_elig_for_depnt,
x_guarantee_amt => r_dl_lor_rec.guarantee_amt,
x_guarantee_date => r_dl_lor_rec.guarantee_date,
x_guarnt_amt_redn_code => r_dl_lor_rec.guarnt_amt_redn_code,
x_guarnt_status_code => r_dl_lor_rec.guarnt_status_code,
x_guarnt_status_date => r_dl_lor_rec.guarnt_status_date,
x_lend_apprv_denied_code => NULL,
x_lend_apprv_denied_date => NULL,
x_lend_status_code => r_dl_lor_rec.lend_status_code,
x_lend_status_date => r_dl_lor_rec.lend_status_date,
x_guarnt_adj_ind => r_dl_lor_rec.guarnt_adj_ind,
x_grade_level_code => r_dl_lor_rec.grade_level_code,
x_enrollment_code => r_dl_lor_rec.enrollment_code,
x_anticip_compl_date => r_dl_lor_rec.anticip_compl_date,
x_borw_lender_id => NULL,
x_duns_borw_lender_id => NULL,
x_guarantor_id => NULL,
x_duns_guarnt_id => NULL,
x_prc_type_code => r_dl_lor_rec.prc_type_code,
x_cl_seq_number => r_dl_lor_rec.cl_seq_number,
x_last_resort_lender => r_dl_lor_rec.last_resort_lender,
x_lender_id => NULL,
x_duns_lender_id => NULL,
x_lend_non_ed_brc_id => NULL,
x_recipient_id => NULL,
x_recipient_type => NULL,
x_duns_recip_id => NULL,
x_recip_non_ed_brc_id => NULL,
x_rec_type_ind => r_dl_lor_rec.rec_type_ind,
x_cl_loan_type => r_dl_lor_rec.cl_loan_type,
x_cl_rec_status => NULL,
x_cl_rec_status_last_update => NULL,
x_alt_prog_type_code => r_dl_lor_rec.alt_prog_type_code,
x_alt_appl_ver_code => r_dl_lor_rec.alt_appl_ver_code,
x_mpn_confirm_code => NULL,
x_resp_to_orig_code => r_dl_lor_rec.resp_to_orig_code,
x_appl_loan_phase_code => NULL,
x_appl_loan_phase_code_chg => NULL,
x_appl_send_error_codes => NULL,
x_tot_outstd_stafford => r_dl_lor_rec.tot_outstd_stafford,
x_tot_outstd_plus => r_dl_lor_rec.tot_outstd_plus,
x_alt_borw_tot_debt => r_dl_lor_rec.alt_borw_tot_debt,
x_act_interest_rate => r_dl_lor_rec.act_interest_rate,
x_service_type_code => r_dl_lor_rec.service_type_code,
x_rev_notice_of_guarnt => r_dl_lor_rec.rev_notice_of_guarnt,
x_sch_refund_amt => r_dl_lor_rec.sch_refund_amt,
x_sch_refund_date => r_dl_lor_rec.sch_refund_date,
x_uniq_layout_vend_code => r_dl_lor_rec.uniq_layout_vend_code,
x_uniq_layout_ident_code => r_dl_lor_rec.uniq_layout_ident_code,
x_p_person_id => r_dl_lor_rec.p_person_id,
x_p_ssn_chg_date => NULL,
x_p_dob_chg_date => NULL,
x_p_permt_addr_chg_date => r_dl_lor_rec.p_permt_addr_chg_date,
x_p_default_status => r_dl_lor_rec.p_default_status,
x_p_signature_code => r_dl_lor_rec.p_signature_code,
x_p_signature_date => r_dl_lor_rec.p_signature_date,
x_s_ssn_chg_date => NULL,
x_s_dob_chg_date => NULL,
x_s_permt_addr_chg_date => r_dl_lor_rec.s_permt_addr_chg_date,
x_s_local_addr_chg_date => NULL,
x_s_default_status => r_dl_lor_rec.s_default_status,
x_s_signature_code => r_dl_lor_rec.s_signature_code,
x_pnote_batch_id => r_dl_lor_rec.pnote_batch_id,
x_pnote_ack_date => r_dl_lor_rec.pnote_ack_date,
x_pnote_mpn_ind => r_dl_lor_rec.pnote_mpn_ind ,
x_elec_mpn_ind => r_dl_lor_rec.elec_mpn_ind, -- added as part of 2216956
x_borr_sign_ind => r_dl_lor_rec.borr_sign_ind,
x_stud_sign_ind => r_dl_lor_rec.stud_sign_ind,
x_borr_credit_auth_code => r_dl_lor_rec.borr_credit_auth_code, -- added as part of 2216956
x_relationship_cd => r_dl_lor_rec.relationship_cd,
x_interest_rebate_percent_num => r_dl_lor_rec.interest_rebate_percent_num,
x_cps_trans_num => r_dl_lor_rec.cps_trans_num,
x_atd_entity_id_txt => r_dl_lor_rec.atd_entity_id_txt ,
x_rep_entity_id_txt => r_dl_lor_rec.rep_entity_id_txt,
x_crdt_decision_status => r_dl_lor_rec.crdt_decision_status,
x_note_message => r_dl_lor_rec.note_message,
x_book_loan_amt => r_dl_lor_rec.book_loan_amt ,
x_book_loan_amt_date => r_dl_lor_rec.book_loan_amt_date,
x_pymt_servicer_amt => r_dl_lor_rec.pymt_servicer_amt,
x_pymt_servicer_date => r_dl_lor_rec.pymt_servicer_date,
x_requested_loan_amt => r_dl_lor_rec.requested_loan_amt,
x_eft_authorization_code => r_dl_lor_rec.eft_authorization_code,
x_external_loan_id_txt => r_dl_lor_rec.external_loan_id_txt,
x_deferment_request_code => r_dl_lor_rec.deferment_request_code ,
x_actual_record_type_code => r_dl_lor_rec.actual_record_type_code,
x_reinstatement_amt => r_dl_lor_rec.reinstatement_amt,
x_school_use_txt => r_dl_lor_rec.school_use_txt,
x_lender_use_txt => r_dl_lor_rec.lender_use_txt,
x_guarantor_use_txt => r_dl_lor_rec.guarantor_use_txt,
x_fls_approved_amt => r_dl_lor_rec.fls_approved_amt,
x_flu_approved_amt => r_dl_lor_rec.flu_approved_amt,
x_flp_approved_amt => r_dl_lor_rec.flp_approved_amt,
x_alt_approved_amt => r_dl_lor_rec.alt_approved_amt,
x_loan_app_form_code => r_dl_lor_rec.loan_app_form_code,
x_override_grade_level_code => r_dl_lor_rec.override_grade_level_code,
x_b_alien_reg_num_txt => r_dl_lor_rec.b_alien_reg_num_txt,
x_esign_src_typ_cd => r_dl_lor_rec.esign_src_typ_cd,
x_acad_begin_date => r_dl_lor_rec.acad_begin_date,
x_acad_end_date => r_dl_lor_rec.acad_end_date
);
END LOOP; -- End of r_dl_lor_rec loop (i.e) end of update lor rec loop